Option Strict Off
Option Explicit On
Imports fatd_faturamento.BLL.Repositorio
Imports fatd_faturamento.ENTIDADE
Imports CrystalDecisions.CrystalReports.Engine
Friend Class FrmRelRecebDiario
	Inherits System.Windows.Forms.Form
	'Private ifrmRelRecebDiario As FrmRelatorio
	Private istrTamCol As String
	Private ilngBanId As Integer
	Private objAtualizaBancoDados As New entidadeAtualizaBancoDados
	Private repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

	Private objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
	Private repositorioExecAtualizaBancoDados As New repositorioAtualizaBancoDados
	Private dt, dt1, dt2, dt3, dt4 As DataTable
	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 stipo As String
	Private Sub cmbBanco_TextChanged(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmbBanco.TextChanged
		ilngBanId = VB6.GetItemData(cmbBanco, cmbBanco.SelectedIndex)
	End Sub
	Private Sub cmbBanco_SelectedIndexChanged(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmbBanco.SelectedIndexChanged
		ilngBanId = VB6.GetItemData(cmbBanco, cmbBanco.SelectedIndex)
	End Sub

	Private Sub cmdImprimir_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdImprimir.Click
		Try
			Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
			Me.cmdImprimir.Enabled = False
			Me.cmdFechar.Enabled = False
			Me.dtpIni.Enabled = False
			Me.dtpFim.Enabled = False
			Me.cmbBanco.Enabled = False
			Me.cmbModelo.Enabled = False
			Call RelRecebDiarioDetalhado()
			Me.cmdImprimir.Enabled = True
			Me.cmdFechar.Enabled = True
			Me.dtpIni.Enabled = True
			Me.dtpFim.Enabled = True
			Me.cmbBanco.Enabled = True
			Me.cmbModelo.Enabled = True
			Me.Cursor = System.Windows.Forms.Cursors.Default

		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: cmdImprimir_Click" & "", "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
		Call Me.Close()
	End Sub
	
	Private Sub RelRecebDiarioDetalhado()
		Dim ldblTotVlr As Double
		Dim llngTotQtd As Integer
		Dim ldblValor As Double
		Dim ldblLiquido As Double
		Dim ldtePgto As Date
		Dim llngQtde As Integer
		Dim lstrZona As String
		Dim lstrLivro As String
		Dim lstrConta As String
		Dim lstrSql As String

		Try

			Me.lblInfo.Text = "Montando o Relatório..."
			Me.lblInfo.Refresh()

			lstrSql = ""
			lstrSql = lstrSql & "delete from AUX_RECEBIMENTO_DIA"
			objExecAtualizaBancoDados.QUERY_NAME = lstrSql
			repositorioAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

			lstrSql = ""
			lstrSql = lstrSql & " SELECT `datapag`, "
			If Me.cmbModelo.SelectedIndex = 1 Then
				lstrSql = lstrSql & "        `zona`, `livro`, `conta`, "
			End If
			lstrSql = lstrSql & "        SUM(`valortot`) `tot1`, "
			lstrSql = lstrSql & "        SUM(IF(IFNULL(`valorliq`, `valortot`) = 0, `valortot`, IFNULL(`valorliq`, `valortot`))) `tot2`, "
			lstrSql = lstrSql & "        COUNT(`numnf`) `qtde` "
			lstrSql = lstrSql & "  FROM nf "
			lstrSql = lstrSql & " WHERE datapag BETWEEN '" & VB6.Format(Me.dtpIni.Value, "yyyy-MM-dd") & "' AND '" & VB6.Format(dtpFim.Value, "yyyy-MM-dd") & "' "
			If ilngBanId <> 999 Then
				lstrSql = lstrSql & "   AND banarrec = " & ilngBanId & ""
			End If
			If cmbModelo.SelectedIndex = 1 Then
				lstrSql = lstrSql & " GROUP BY  `zona`, `livro`, `conta`, `datapag` "
				lstrSql = lstrSql & " ORDER BY `datapag` ASC, `zona` ASC, `livro` ASC, `conta` ASC "
			Else
				lstrSql = lstrSql & " GROUP BY `datapag` ASC "
			End If
			objAtualizaBancoDados.QUERY_NAME = lstrSql
			dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

			If dt.Rows.Count > 0 Then
				Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, dt.Rows.Count)
				ldblTotVlr = 0
				llngTotQtd = 0

				For Each row As DataRow In dt.Rows
					If Me.cmbModelo.SelectedIndex = 1 Then
						lstrZona = Trim(IIf(IsDBNull(row.Item("zona")), "", row.Item("zona")))
						lstrLivro = Trim(IIf(IsDBNull(row.Item("livro")), "", row.Item("livro")))
						lstrConta = Trim(IIf(IsDBNull(row.Item("conta")), "", row.Item("conta")))
						stipo = "Detalhado"
					Else
						lstrZona = "000"
						lstrLivro = "00"
						lstrConta = "000000"
						stipo = "Resumido"
					End If
					ldtePgto = CDate(IIf(IsDBNull(row.Item("datapag")), "1900-01-01", row.Item("datapag")))
					ldblValor = CDbl(IIf(IsDBNull(row.Item("tot1")), 0, row.Item("tot1")))
					ldblLiquido = CDbl(IIf(IsDBNull(row.Item("tot2")), 0, row.Item("tot2")))
					llngQtde = CInt(IIf(IsDBNull(row.Item("qtde")), 0, row.Item("qtde")))
					ldblTotVlr = ldblTotVlr + IIf(ldblLiquido = 0, ldblValor, ldblLiquido)
					llngTotQtd = llngTotQtd + llngQtde

					lstrSql = "Insert into AUX_RECEBIMENTO_DIA SET"
					lstrSql = lstrSql & " DATA = '" & ldtePgto & "' ,"
					lstrSql = lstrSql & " VALOR = '" & VB6.Format(ldblValor, "#,##0.00") & "',"
					lstrSql = lstrSql & " ZLC = '" & VB6.Format(lstrZona, "000") & "." & VB6.Format(lstrLivro, "00") & "." & VB6.Format(lstrConta, "000000") & "',"
					lstrSql = lstrSql & " QUANTIDADE = '" & llngQtde & "'"
					objExecAtualizaBancoDados.QUERY_NAME = lstrSql
					repositorioAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
				Next

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

				Me.lblInfo.Text = "Pronto!"
				Call Me.lblInfo.Refresh()
			Else
				Me.lblInfo.Text = "Não foi encontrado faturas no periodo "
				Call Me.lblInfo.Refresh()
			End If

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

	Private Sub dtpIni_ValueChanged(sender As Object, e As EventArgs) Handles dtpIni.ValueChanged
		Me.dtpFim.Value = UltimoDiaMes(Month(Me.dtpIni.Value), Year(dtpIni.Value)) & "/" & Month(Me.dtpIni.Value) & "/" & Year(Me.dtpIni.Value)
	End Sub

	Private Sub FrmRelRecebDiario_KeyPress(ByVal eventSender As System.Object, ByVal eventArgs As System.Windows.Forms.KeyPressEventArgs) Handles MyBase.KeyPress
		Try
			Dim KeyAscii As Short = Asc(eventArgs.KeyChar)
			If KeyAscii = System.Windows.Forms.Keys.Return Then
				TabEnter(KeyAscii, eventArgs)
			ElseIf KeyAscii = System.Windows.Forms.Keys.Escape Then
				Call cmdFechar_Click(cmdFechar, New System.EventArgs())
			End If
			eventArgs.KeyChar = Chr(KeyAscii)
			If KeyAscii = 0 Then
				eventArgs.Handled = True
			End If
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: FrmRelRecebDiario_KeyPress" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub FrmRelRecebDiario_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		Try
			Me.dtpIni.Value = Date.Now()
			Me.dtpFim.Value = CDate(UltimoDiaMes(Month(Me.dtpIni.Value), Year(Me.dtpIni.Value)) & "/" & VB6.Format(Me.dtpIni.Value, "mm/yyyy"))
			Me.cmbModelo.SelectedIndex = 0
			Call CarregaComboBancos()
			Call sCriaTabelaAuxiliares()
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: FrmRelRecebDiario_Load" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub
	Private Sub FrmRelRecebDiario_FormClosed(ByVal eventSender As System.Object, ByVal eventArgs As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
		Dim lstrsql As String

		Try

			lstrsql = ""
			lstrsql = lstrsql & "DROP TABLE IF EXISTS AUX_RECEBIMENTO_DIA"
			objExecAtualizaBancoDados.QUERY_NAME = lstrsql
			repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

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

	End Sub

	Private Sub CarregaComboBancos()
		Dim lstrSql As String
		Try

			With Me.cmbBanco
				Call .Items.Add("000 - TODOS")
				VB6.SetItemData(Me.cmbBanco, 0, 999)
			End With
			lstrSql = "SELECT `ban_id`, `nrbco`, `nmbco` FROM `bancos` WHERE `tpmovto` = 'N'  "
			objAtualizaBancoDados.QUERY_NAME = lstrSql
			dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

			If dt.Rows.Count > 0 Then
				For Each row As DataRow In dt.Rows
					Call Me.cmbBanco.Items.Add(IIf(IsDBNull(row.Item("nrbco")), "000", row.Item("nrbco")) & " - " & Trim(IIf(IsDBNull(row.Item("nmbco")), "Indefinido", row.Item("nmbco"))))
					VB6.SetItemData(Me.cmbBanco, Me.cmbBanco.Items.Count - 1, CInt(IIf(IsDBNull(row.Item("ban_id")), 0, row.Item("ban_id"))))
				Next
			End If

			With Me.cmbBanco
				.SelectedIndex = 0
				ilngBanId = VB6.GetItemData(Me.cmbBanco, .SelectedIndex)
			End With

		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: MontaTabela" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	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 = LImagem

		Dim objEmpresa As New entidadeEmpresa
		Dim repositorioEmpresa As New repositorioEmpresa

		Dim vlist

		Try
			Dim rpt As New ReportDocument()

			'rptRelRecebimentoDia
			If vNomeRPT = "rptRelRecebimentoDia" Then

				dt_2 = New DataTable()
				dt_2.Columns.Add("DATA", GetType(String))
				dt_2.Columns.Add("VALOR", GetType(String))
				dt_2.Columns.Add("ZLC", GetType(String))
				dt_2.Columns.Add("QUANTIDADE", GetType(String))
				dt_2.Columns.Add("NOME", GetType(String))
				dt_2.Columns.Add("TIPO", GetType(String))
				dt_2.Columns.Add("IMAGEM", GetType(Byte()))

				ds = New DataSet

				lstrsql = ""
				lstrsql = "SELECT * FROM AUX_RECEBIMENTO_DIA " '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("DATA"),
										  dr_1("VALOR"),
										  dr_1("ZLC"),
										  dr_1("QUANTIDADE"),
										  dr_1("NOME"),
										  dr_1("TIPO"),
										  bytBLOBData)
					Next dr_1
				End If

				ds.Tables.Add(dt_2)
				ds.Tables(0).TableName = "REL_RECEBIMENTOS_DIA"

				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 rptRelRecebimentoDia

					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("MesAnoRef", "Período: " & dtpIni.Value & " Até: " & dtpFim.Value)
					rpt.SetParameterValue("NomeRel", "Posição de Recebimento Por dia - " & stipo)

					frmViewRelatorios.rpt = rpt

					frmViewRelatorios.CRViewer.Zoom((100))
					frmViewRelatorios.Text = "Recebimento do dia"
					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 sCriaTabelaAuxiliares()

		Dim lstrsql As String

		Try

			lstrsql = ""
			lstrsql = lstrsql & "DROP TABLE IF EXISTS AUX_RECEBIMENTO_DIA"
			objExecAtualizaBancoDados.QUERY_NAME = lstrsql
			repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

			lstrsql = ""
			lstrsql = lstrsql & "create table IF NOT EXISTS AUX_RECEBIMENTO_DIA("
			lstrsql = lstrsql & "DATA VARCHAR(10),"
			lstrsql = lstrsql & "VALOR  VARCHAR(20),"
			lstrsql = lstrsql & "ZLC  VARCHAR(20),"
			lstrsql = lstrsql & "NOME  VARCHAR(50),"
			lstrsql = lstrsql & "TIPO  VARCHAR(30),"
			lstrsql = lstrsql & "QUANTIDADE VARCHAR(20))"
			objExecAtualizaBancoDados.QUERY_NAME = lstrsql
			repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

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

	End Sub

End Class