Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Imports fatd_faturamento.BLL.Repositorio
Imports fatd_faturamento.ENTIDADE
Imports CrystalDecisions.CrystalReports.Engine

Friend Class frmRelConvenios
	Inherits System.Windows.Forms.Form

	Private dt, dt1, dt2, dt3, dt4 As DataTable
	Private objAtualizaBancoDados As New entidadeAtualizaBancoDados
	Private repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

	Private objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
	Private repositorioExecAtualizaBancoDados As New repositorioAtualizaBancoDados
	Private LImagem() As Byte
	Private sNomeEmpresa As String
	Private sEnderecoEmpresa As String
	Private sCepEmpresa As String
	Private sCidadeEmpresa As String
	Private sUFempresa As String
	Private sBairroEmpresa As String
	Private sFoneEmpresa As String
	Private sWebEmpresa As String
	Private bValidacao As Boolean
	Private dtBase As Date
	Private sCabec As String
	Private sCabec2 As String

	Private Sub chkReserva_CheckStateChanged(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles chkReserva.CheckStateChanged
		If Me.chkReserva.CheckState = System.Windows.Forms.CheckState.Checked Then
			Me.chkArrecadado.CheckState = System.Windows.Forms.CheckState.Checked
			Me.chkArrecadado.Enabled = False
			Me.chkArrecadar.CheckState = System.Windows.Forms.CheckState.Unchecked
			Me.chkArrecadar.Enabled = False
		Else
			Me.chkArrecadado.Enabled = True
			Me.chkArrecadar.Enabled = True
		End If
	End Sub

	Private Sub cmdEmitir_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdEmitir.Click
		'Dim fr As New FrmRelatorio
		Dim strsql As String
		Dim strCabec As String
		Dim strCabec2 As String
		Dim strCodConv As String
		Dim strNomConv As String


		Try
			dtBase = CDate(UltimoDiaMes(Month(DTPFinal.Value), Year(DTPFinal.Value)) & "/" & Month(DTPFinal.Value) & "/" & Year(DTPFinal.Value))

			If chkArrecadado.CheckState Or chkArrecadar.CheckState Then

				strCodConv = Mid(Me.cmbConv.Text, 1, 10)
				strNomConv = Mid(Me.cmbConv.Text, 14)

				Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
				Call LigDes(False)

				If chkArrecadado.CheckState Then
					'Arrecadado no período Informado
					strsql = "   AND f.datapag >= '" & VB6.Format(DTPInicial.Value, "yyyy-MM-dd") & "' AND f.datapag <= '" & VB6.Format(DTPFinal.Value, "yyyy-MM-dd") & "' "
					strsql = strsql & "   AND f.datapag <> '0000-00-00' "
					If Me.chkReserva.CheckState = System.Windows.Forms.CheckState.Unchecked Then
						strCabec = "Bx.Reserva (" & strNomConv & ")"
						sCabec = "Bx.Reserva (" & strNomConv & ")"
						strsql = strsql & "   AND f.flgst = 'P'"
					Else
						strCabec = "Arrecadação (" & strNomConv & ")"
						sCabec = "Arrecadação (" & strNomConv & ")"
						strsql = strsql & "   AND f.flgst = 'R'"
					End If
					strCabec2 = "Período de " & VB6.Format(DTPInicial.Value, "dd/mm/yyyy") & " a " & VB6.Format(DTPFinal.Value, "dd/mm/yyyy")
					Call RelConvenios(strsql, strCabec, 0, strCodConv, strCabec2, "A")
				End If

				If chkArrecadar.CheckState Then

					strsql = "   AND f.datavenc >= """ & VB6.Format(DTPInicial.Value, "yyyy-MM-dd") & """ AND f.datavenc <= """ & VB6.Format(DTPFinal.Value, "yyyy-MM-dd") & """ " & "   AND (datapag = ""0000-00-00"" or datapag is null) AND flgst NOT IN('C','R','P') "

					strCabec = "Saldo a Arrecadar (" & strNomConv & ") "
					sCabec2 = "Saldo a Arrecadar (" & strNomConv & ") "
					strCabec2 = "No dia " & VB6.Format(dtBase, "dd/mm/yyyy")
					Call RelConvenios(strsql, strCabec, 2, strCodConv, strCabec2, "C")
				End If

				Call sDadosEmpresa(LImagem, sNomeEmpresa, sEnderecoEmpresa, sCepEmpresa, sCidadeEmpresa, sUFempresa, sBairroEmpresa, sFoneEmpresa, sWebEmpresa)
				Call lPrint("rptRelConvenios")

				'fr.VS.EndDoc()
				'fr.Show()

				Call LigDes(True)

				Me.Cursor = System.Windows.Forms.Cursors.Default
			End If

		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: cmdEmitir_Click" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub
	
	Private Sub frmRelConvenios_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		DTPInicial.Value = Date.Now
		DTPFinal.Value = Date.Now
		Call encheComboConvenios()
		Call sCriaTabelaAuxiliar()
	End Sub
	
	Private Sub encheComboConvenios()
		Dim strsql As String
		'Dim rsConsulta As New ADODB.Recordset

		Try
			strsql = "SELECT * " & "FROM codigos " & "WHERE num LIKE '9030%' " & "AND num <> '9030000000' " & "ORDER BY num "
			objAtualizaBancoDados.QUERY_NAME = strsql
			dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

			If dt.Rows.Count > 0 Then
				For Each row As DataRow In dt.Rows
					Me.cmbConv.Items.Add(row.Item("Num") & " - " & row.Item("descri"))
				Next
			End If

			If Me.cmbConv.Items.Count > 0 Then
				Me.cmbConv.Text = VB6.GetItemString(Me.cmbConv, 0)
			End If

		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: encheComboConvenios" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub

	Private Sub RelConvenios(ByRef astrSql As String, ByRef astrCabec As String, ByRef aintTipo As Short, ByRef astrCodConv As String, ByRef astrCabec2 As String, ByVal sTipo As String)
		Dim strTemp As String
		Dim strTamCol As String
		'Dim rsConsulta As ADODB.Recordset
		'Dim rsUnidade As ADODB.Recordset
		Dim strZLC As String
		Dim strNome As String
		Dim strEnde As String
		Dim lstrGrTar As String
		Dim lngQtde As Integer
		Dim lngConsId As Integer
		Dim lngUnidId As Integer
		Dim dblValor As Double
		Dim lngTQtd As Integer
		Dim dblTVlr As Double
		Dim strsql As String
		Dim strSql3 As String


		Try

			strsql = "SELECT f.zona, f.livro, f.conta, f.numnf, f.mesano, f.datapag, f.itensserv, f.ta, f.cons_id, f.unid_id " & "  FROM nf f " & " WHERE itensserv like ""%" & astrCodConv & "%"""
			strSql3 = strsql & astrSql
			objAtualizaBancoDados.QUERY_NAME = strsql & astrSql
			dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

			If dt.Rows.Count > 0 Then
				Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, dt.Rows.Count, " " & VB.Left(astrCabec, InStr(1, astrCabec, "(") - 1))
				lngQtde = dt.Rows.Count
				For Each row As DataRow In dt.Rows
					dblValor = BuscaVlrItem(row.Item("itensserv"), astrCodConv)
					strZLC = row.Item("Zona") & "." & row.Item("Livro") & "." & row.Item("Conta")
					lstrGrTar = LCase(Mid(IIf(IsDBNull(row.Item("ta")), "01B", row.Item("ta")), 3, 1))
					lngConsId = IIf(IsDBNull(row.Item("cons_id")), 0, row.Item("cons_id"))
					lngUnidId = IIf(IsDBNull(row.Item("unid_id")), 0, row.Item("unid_id"))

					strsql = "SELECT nome, end " & "  FROM uc_faturamento WHERE cons_id = " & lngConsId & "   AND id = " & lngUnidId & "  LIMIT 1 "
					objAtualizaBancoDados.QUERY_NAME = strsql
					dt1 = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

					If dt1.Rows.Count > 0 Then
						For Each row1 As DataRow In dt1.Rows
							strNome = VB.Left(IIf(IsDBNull(row.Item("nome")), "NOME INDEFINIDO", row.Item("nome")), 50)
							strEnde = VB.Left(IIf(IsDBNull(row.Item("End")), "ENDERECO INDEFINIDO", row.Item("End")), 30)
						Next
					Else

						strsql = "SELECT nome, end " & "  FROM consum " & " WHERE consreg = " & lngConsId & "  LIMIT 1 "
						objAtualizaBancoDados.QUERY_NAME = strsql
						dt2 = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

						If dt2.Rows.Count > 0 Then
							For Each row2 As DataRow In dt2.Rows
								strNome = VB.Left(IIf(IsDBNull(row2.Item("nome")), "NOME INDEFINIDO", row2.Item("nome")), 50)
								strEnde = VB.Left(IIf(IsDBNull(row2.Item("End")), "ENDERECO INDEFINIDO", row2.Item("End")), 30)
							Next
						Else
							strNome = "NOME INDEFINIDO"
							strEnde = "ENDERECO INDEFINIDO"
						End If
					End If
					'If rsUnidade.State = 1 Then Call rsUnidade.Close()
					'dblValor = IIf(IsNull(rsConsulta!valor_item), 0, rsConsulta!valor_item)

					Dim lstrsql As String
					lstrsql = "INSERT INTO AUX_CONVENIOS SET "
					lstrsql = lstrsql & "TIPO = '" & sTipo & "' ,"
					lstrsql = lstrsql & "IDENTIFICACAO = '" & strZLC & "' ,"
					lstrsql = lstrsql & "NOME = '" & strNome & "' ,"
					lstrsql = lstrsql & "ENDERECO = '" & strEnde & "',"
					lstrsql = lstrsql & "VALOR = '" & Strings.Format(dblValor, "#,##0.00") & "'"
					objExecAtualizaBancoDados.QUERY_NAME = lstrsql
					repositorioAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

					'dblTVlr = dblTVlr + dblValor
					'strTemp = "|" & strZLC & "|" & strNome & "|" & strEnde & "|" & VB6.Format(dblValor, "standard")


					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1,  , " " & VB.Left(astrCabec, InStr(1, astrCabec, "(") - 1))
					System.Windows.Forms.Application.DoEvents()
				Next
			End If



		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: RelConvenios" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub frmRelConvenios_FormClosed(sender As Object, e As FormClosedEventArgs) Handles MyBase.FormClosed
		Try

			Dim lstrsql As String

			objExecAtualizaBancoDados.QUERY_NAME = "drop table if exists AUX_CONVENIOS"
			repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: frmRelConvenios_FormClosed" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub LigDes(ByRef abModo As Boolean)
		Try
			Label1(0).Enabled = abModo
			Label1(1).Enabled = abModo
			Label1(2).Enabled = abModo
			DTPInicial.Enabled = abModo
			DTPFinal.Enabled = abModo
			FramePeriodo.Enabled = abModo
			cmdEmitir.Enabled = abModo
			cmdFechar.Enabled = abModo
			Me.cmbConv.Enabled = abModo
			chkReserva.Enabled = abModo
			If Me.chkReserva.CheckState = System.Windows.Forms.CheckState.Checked Then
				Me.chkArrecadado.Enabled = False
				Me.chkArrecadar.Enabled = False
			Else
				Me.chkArrecadado.Enabled = abModo
				Me.chkArrecadar.Enabled = abModo
			End If
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: LigDes" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub cmdFechar_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdFechar.Click
		Me.Close()
	End Sub

	Private Sub lPrint(ByVal vNomeRPT As String)

		Dim dt As DataTable
		Dim dt_1 As DataTable
		Dim dt_2 As DataTable
		Dim dt_3 As DataTable
		Dim dc As DataColumn
		Dim ds As DataSet
		Dim lstrsql As String

		Dim bytBLOBData() As Byte

		Dim objEmpresa As New entidadeEmpresa
		Dim repositorioEmpresa As New repositorioEmpresa

		Dim vlist


		'Dim vlist

		'Dim objEmpresa As New entidadeEmpresa
		'Dim repositorioEmpresa As New repositorioEmpresa


		'vlist = repositorioEmpresa.Select(objEmpresa)

		'If vlist.Count <> 0 Then

		'	For Each objEmpresaFor As entidadeEmpresa In vlist

		'		bytBLOBData = objEmpresaFor.LOGOTIPO

		'	Next

		'End If

		Try
			Dim rpt As New ReportDocument()

			'rptConsumidorAVencer
			If vNomeRPT = "rptRelConvenios" Then


				dt_2 = New DataTable()
				dt_2.Columns.Add("IDENTIFICACAO", GetType(String))
				dt_2.Columns.Add("NOME", GetType(String))
				dt_2.Columns.Add("ENDERECO", GetType(String))
				dt_2.Columns.Add("VALOR", GetType(String))
				dt_2.Columns.Add("IMAGEM", GetType(Byte()))

				ds = New DataSet

				lstrsql = ""
				lstrsql = "SELECT * FROM AUX_CONVENIOS " 'WHERE nm_usuario = '" & My.Computer.Name & "'"
				objAtualizaBancoDados.QUERY_NAME = lstrsql
				dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

				If dt.Rows.Count > 0 Then
					For Each dr_1 As DataRow In dt.Rows

						dt_2.Rows.Add(dr_1("IDENTIFICACAO"),
										  dr_1("NOME"),
										  dr_1("ENDERECO"),
										  dr_1("VALOR"),
										  bytBLOBData)
					Next dr_1
				End If


				ds.Tables.Add(dt)
				ds.Tables(0).TableName = "REL_CONVENIOS"


				If ds.Tables(0).Rows.Count = 0 Then
					MessageBox.Show("Não há registros para o relatório escolhido! ", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information)
					Exit Sub
				Else

					rpt = New rptRelConvenios

					rpt.SetDataSource(ds)
					rpt.SetParameterValue("nomeempresa", sNomeEmpresa)
					rpt.SetParameterValue("enderecoempresa", sEnderecoEmpresa)
					rpt.SetParameterValue("cepempresa", sCepEmpresa)
					rpt.SetParameterValue("ufempresa", sUFempresa)
					rpt.SetParameterValue("cidadeempresa", sCidadeEmpresa)
					rpt.SetParameterValue("bairroempresa", sBairroEmpresa)
					rpt.SetParameterValue("foneempresa", sFoneEmpresa)
					rpt.SetParameterValue("websiteempresa", sWebEmpresa)
					rpt.SetParameterValue("Dia", "DIA: " & dtBase)
					rpt.SetParameterValue("TipoArrec", sCabec)
					rpt.SetParameterValue("TipoArrec2", sCabec2)
					'rpt.SetParameterValue("Tipo", IIf(cmbOpcao.Text = "Geral", "Geral", "Munícipio"))
					'rpt.SetParameterValue("MesAnoRef", Month(dtpMesAno.Value) & "/" & Year(dtpMesAno.Value))
					'rpt.SetParameterValue("NomeRel", "Grupo A - Período de: " & dtpIni.Value & " Até: " & dtpFim.Value)

					frmViewRelatorios.rpt = rpt

					frmViewRelatorios.CRViewer.Zoom((100))
					frmViewRelatorios.Text = "Relatório Convênios"
					frmViewRelatorios.ShowDialog()

				End If

			End If

			Me.Cursor = System.Windows.Forms.Cursors.Default

		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: lPrint" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub

	Private Sub sCriaTabelaAuxiliar()

		Try

			Dim lstrsql As String

			objExecAtualizaBancoDados.QUERY_NAME = "drop table if exists AUX_CONVENIOS"
			repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

			lstrsql = ""
			lstrsql = "create table AUX_CONVENIOS ("
			lstrsql = lstrsql & "TIPO VARCHAR(10),"
			lstrsql = lstrsql & "IDENTIFICACAO VARCHAR(20),"
			lstrsql = lstrsql & "NOME VARCHAR(50),"
			lstrsql = lstrsql & "ENDERECO VARCHAR(100),"
			lstrsql = lstrsql & "VALOR VARCHAR(20))"
			objExecAtualizaBancoDados.QUERY_NAME = lstrsql
			repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: sCriaTabelaAuxiliar" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub
End Class