Imports System.Data.SqlClient
Imports System.Runtime.InteropServices

Public Class FrmCobranza
#Region "Mov. Pantalla"
    <DllImport("user32.Dll", EntryPoint:="ReleaseCapture")>
    Private Shared Sub ReleaseCapture()

    End Sub

    <DllImport("user32.Dll", EntryPoint:="SendMessage")>
    Private Shared Sub SendMessage(hWnd As IntPtr, wMsg As Integer, wParam As Integer, lParam As Integer)

    End Sub

    Private Sub PanelBarraTitulo_MouseMove(sender As Object, e As MouseEventArgs) Handles PanelBarraTitulo.MouseMove
        ReleaseCapture()
        SendMessage(Me.Handle, &H112&, &HF012&, 0)
    End Sub

    Private Sub lblEmpresa_MouseMove(sender As Object, e As MouseEventArgs) Handles lblEmpresa.MouseMove
        ReleaseCapture()
        SendMessage(Me.Handle, &H112&, &HF012&, 0)
    End Sub

#End Region

    Private Sub btnCerrar_Click(sender As Object, e As EventArgs) Handles btnCerrar.Click
        Me.Close()
        CerrarFormulario()
    End Sub

    Private Sub FrmCobranza_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        GlobalVariables.varReporteConsulta = 0
        CargarDGV()
        LlenaBusqueda()


        Dim FechaHoy As Date = Date.Now
        Dim Fecha8 As Date = FechaHoy.AddDays(-8)
        Dim Fecha15 As Date = FechaHoy.AddDays(-15)
        Dim Fecha30 As Date = FechaHoy.AddDays(-30)

        Dim varFecHoy As String = FechaHoy.ToString("yyyy/MM/dd")
        Dim VarFec8 As String = Fecha8.ToString("yyyy/MM/dd")
        Dim VarFec15 As String = Fecha15.ToString("yyyy/MM/dd")
        Dim varFec30 As String = Fecha30.ToString("yyyy/MM/dd")

        RecD8.Text = DSum("Total", "Invoices", "Fecha >= '" & VarFec8 & "' and Fecha <= '" & varFecHoy & "' and pagado=0 and Moneda='Dollar USD'").ToString("c2")
        RecD15.Text = DSum("Total", "Invoices", "Fecha >= '" & VarFec15 & "' and Fecha <= '" & VarFec8 & "' and pagado=0 and Moneda='Dollar USD'").ToString("c2")
        RecD30.Text = DSum("Total", "Invoices", "Fecha >= '" & varFec30 & "' and Fecha <= '" & VarFec15 & "' and pagado=0 and Moneda='Dollar USD'").ToString("c2")
        RecD30Mas.Text = DSum("Total", "Invoices", "Fecha < '" & varFec30 & "' and pagado=0 and Moneda='Dollar USD'").ToString("c2")

        RecP8.Text = DSum("Total", "Invoices", "Fecha >= '" & VarFec8 & "' and Fecha <= '" & varFecHoy & "' and pagado=0 and Moneda='Pesos MXN'").ToString("c2")
        RecP15.Text = DSum("Total", "Invoices", "Fecha >= '" & VarFec15 & "' and Fecha <= '" & VarFec8 & "' and pagado=0 and Moneda='Pesos MXN'").ToString("c2")
        RecP30.Text = DSum("Total", "Invoices", "Fecha >= '" & varFec30 & "' and Fecha <= '" & VarFec15 & "' and pagado=0 and Moneda='Pesos MXN'").ToString("c2")
        RecP30Mas.Text = DSum("Total", "Invoices", "Fecha < '" & varFec30 & "' and pagado=0 and Moneda='Pesos MXN'").ToString("c2")

        TotalMxn.Text = DSum("Total", "Invoices", "pagado=0 and Moneda='Pesos MXN'").ToString("c2")
        TotalDls.Text = DSum("Total", "Invoices", "pagado=0 and Moneda='Dollar USD'").ToString("c2")
    End Sub
    Sub CargarDGV()
        Dim sql As String
        Dim cn As SqlConnection
        Dim cm As SqlCommand
        Dim da As SqlDataAdapter
        Dim ds As DataSet
        sql = "SELECT Id, Folio, Fecha, Cliente, Estatus, Moneda, Total, Pagado, FechaPag, RefPago, Aplico FROM Invoices where pagado=0 order by Id desc"
        cn = New SqlConnection(GlobalVariables.cstring)
        Try
            cn.Open()
            cm = New SqlCommand()
            cm.CommandText = sql
            cm.CommandType = CommandType.Text
            cm.Connection = cn
            da = New SqlDataAdapter(cm)
            ds = New DataSet()
            da.Fill(ds)
            Me.BindingSource1.DataSource = ds.Tables(0).DefaultView
            Me.BindingNavigator1.BindingSource = Me.BindingSource1
            DGVRegs.DataSource = Me.BindingSource1
            'DGVRegs.Columns(0).Visible = False
            DGVRegs.Columns.Item("Total").DefaultCellStyle.Format = "C2"
            DGVRegs.Columns.Item("Total").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            FormatoFilas()
            cn.Close()
            cm = Nothing
        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Information)
        End Try
    End Sub
    Sub FormatoFilas()
        With DGVRegs
            'CAMBIA LOS TITULOS DE LAS COLUMNAS a ingles
            .Columns(0).HeaderText = "Id. Reg."
            .Columns(1).HeaderText = "Folio"
            .Columns(2).HeaderText = "Fecha"
            .Columns(3).HeaderText = "Cliente"
            .Columns(4).HeaderText = "Estatus"
            .Columns(5).HeaderText = "Móneda"
            .Columns(6).HeaderText = "Total"
            .Columns(7).HeaderText = "Pagado"
            .Columns(8).HeaderText = "Fecha Pago"
            .Columns(9).HeaderText = "Ref. Pago"
            .Columns(10).HeaderText = "Aplicó"
        End With
    End Sub

    Sub LlenaBusqueda()
        Dim dt As DataTable = New DataTable("Tabla")

        dt.Columns.Add("Codigo")
        dt.Columns.Add("Descripcion")

        Dim dr As DataRow

        dr = dt.NewRow()
        dr("Codigo") = "Folio"
        dr("Descripcion") = "No. Folio"
        dt.Rows.Add(dr)

        dr = dt.NewRow()
        dr("Codigo") = "TipoReg"
        dr("Descripcion") = "Tipo Reg."
        dt.Rows.Add(dr)

        dr = dt.NewRow()
        dr("Codigo") = "Cliente"
        dr("Descripcion") = "Cliente"
        dt.Rows.Add(dr)

        dr = dt.NewRow()
        dr("Codigo") = "Estatus"
        dr("Descripcion") = "Estatus"
        dt.Rows.Add(dr)

        bsCriterio.ComboBox.DataSource = dt
        bsCriterio.ComboBox.ValueMember = "Codigo"
        bsCriterio.ComboBox.DisplayMember = "Descripcion"
    End Sub

    Private Sub bsFiltro_KeyDown(sender As Object, e As KeyEventArgs) Handles bsFiltro.KeyDown
        If e.KeyCode = Keys.Enter Then
            Dim CriterioBusqueda As String = ""
            Try

                If bsCriterio.ComboBox.Text = "No. Folio" Then
                    GlobalVariables.varCriterioConsulta = "Folio Like '%" & Me.bsFiltro.Text & "%' and Pagado=0"
                ElseIf bsCriterio.ComboBox.Text = "Tipo Reg." Then
                    GlobalVariables.varCriterioConsulta = "TipoReg like '%" & Me.bsFiltro.Text & "%' and Pagado=0"
                ElseIf bsCriterio.ComboBox.Text = "Cliente" Then
                    GlobalVariables.varCriterioConsulta = "Cliente like '%" & Me.bsFiltro.Text & "%' and Pagado=0"
                ElseIf bsCriterio.ComboBox.Text = "Estatus" Then
                    GlobalVariables.varCriterioConsulta = "Estatus like '%" & Me.bsFiltro.Text & "%' and Pagado=0"
                End If
                LlenaDatos()
                GlobalVariables.varReporteConsulta = 1
            Catch ex As Exception
                Exit Sub
            End Try
        End If
    End Sub
    Sub LlenaDatos()
        Dim sql As String
        Dim cn As SqlConnection
        Dim cm As SqlCommand
        Dim da As SqlDataAdapter
        Dim ds As DataSet
        sql = "SELECT Id, Folio, Fecha, Cliente, Estatus, Moneda, Total, Pagado, FechaPag, RefPago, Aplico FROM Invoices  where " & GlobalVariables.varCriterioConsulta & " and pagado=0 order by Id desc"
        cn = New SqlConnection(GlobalVariables.cstring)
        Try
            cn.Open()
            cm = New SqlCommand()
            cm.CommandText = sql
            cm.CommandType = CommandType.Text
            cm.Connection = cn
            da = New SqlDataAdapter(cm)
            ds = New DataSet()
            da.Fill(ds)
            Me.BindingSource1.DataSource = ds.Tables(0).DefaultView
            Me.BindingNavigator1.BindingSource = Me.BindingSource1
            DGVRegs.DataSource = Me.BindingSource1
            'DGVRegs.Columns(0).Visible = False
            DGVRegs.Columns.Item("Total").DefaultCellStyle.Format = "C2"
            DGVRegs.Columns.Item("Total").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            FormatoFilas()
            cn.Close()
            cm = Nothing
        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Information)
        End Try
    End Sub
    Private Sub DGVEntList_CellContentDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGVRegs.CellContentDoubleClick
        AbrirFormDatos()
    End Sub
    Private Sub btnEditar_Click(sender As Object, e As EventArgs) Handles btnEditar.Click
        AbrirFormDatos()
    End Sub

    Sub AbrirFormDatos()
        GlobalVariables.VarIDReg = DGVRegs.CurrentRow.Cells("Id").Value

        FrmAppPago.Folio.Text = DGVRegs.CurrentRow.Cells("Folio").Value
        FrmAppPago.Cliente.Text = DGVRegs.CurrentRow.Cells("Cliente").Value
        FrmAppPago.Total.Text = Convert.ToDouble(DGVRegs.CurrentRow.Cells("Total").Value).ToString("C2")

        FrmAppPago.FechaPag.Format = DateTimePickerFormat.Custom
        FrmAppPago.FechaPag.CustomFormat = " "
        FrmAppPago.Pagado.Checked = False
        FrmAppPago.RefPago.Text = ""
        GlobalVariables.VarOrigenPago = False
        FrmAppPago.ShowDialog()
    End Sub

    Private Sub btnVerTodos_Click(sender As Object, e As EventArgs) Handles btnVerTodos.Click
        Me.bsFiltro.Text = ""
        CargarDGV()
        GlobalVariables.varReporteConsulta = 0
    End Sub
    Private Sub btnImprimir_Click(sender As Object, e As EventArgs) Handles btnImprimir.Click
        GlobalVariables.VarOrigen = True
        If (Application.OpenForms("FrmRptInvoiceList") Is Nothing) Then
            FrmRptInvoiceList.lblEncabezado.Text = "Reporte: FACTURAS PENDIENTES DE PAGO"
            FrmRptInvoiceList.Text = "Reporte: FACTURAS PENDIENTES DE PAGO"
            FrmRptInvoiceList.ShowDialog()
        Else
            FrmRptInvoiceList.Close()
            FrmRptInvoiceList.lblEncabezado.Text = "Reporte: FACTURAS PENDIENTES DE PAGO"
            FrmRptInvoiceList.Text = "Reporte: FACTURAS PENDIENTES DE PAGO"
            FrmRptInvoiceList.ShowDialog()
        End If

    End Sub
End Class