HF-MIS-SYS--LEEHSIEHJUI/WindowsApp1/02、皮料管理/Frm_WetBlue.vb

Imports System.Data.SqlClient
Imports OfficeOpenXml
Imports System.Net
Imports Microsoft.Office.Interop
Public Class Frm_WetBlue
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim my_year111 As Integer
    Dim my_month111 As Integer
    'Dim my_year As Integer
    Dim dt1 As New DataTable
    Dim sql As String
    Dim conn As New SqlConnection
    Dim xlApp As Microsoft.Office.Interop.Excel.Application
    Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim xlRange As Microsoft.Office.Interop.Excel.Range
    Dim PTCache As Microsoft.Office.Interop.Excel.PivotCache
    Dim PT As Microsoft.Office.Interop.Excel.PivotTable

    Private Sub Frm_WetBlue_Load(sender As Object, e As EventArgs) Handles MyBase.Load



        conn.ConnectionString = connstring
        conn.Open()
        cmd.Connection = conn


        sql = "SELECT upper(SUPPLIER) as 供应商,count(*) as 笔数 FROM    RT_WETBLUE WHERE  (PATINDEX('%[0-9]%', SUPPLIER) = 0) AND (SUPPLIER <> '') GROUP BY   SUPPLIER order by count(*) desc"  '----供应商笔数
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt1)

        dgv_supplier.DataSource = dt1

        dgv_supplier.Columns("供应商").Width = 70

        dgv_supplier.RowHeadersWidth = 10



        sql = "SELECT upper(source1) as 皮源,count(*) as 笔数 FROM  RT_WETBLUE where source1<>'' GROUP BY   source1 order by count(*) desc"  '----皮源笔数
        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_source2.DataSource = dt1

        dgv_source2.Columns("皮源").Width = 230
        dgv_source2.Columns("笔数").Width = 80

        dgv_source2.RowHeadersWidth = 10








        sql = "SELECT xno as 手册号,upper(SUPPLIER) as 供应商,source1 as 皮源, begin_weight as 重量,begin_price as 单价 , bao_in_date as 入库日期,che_in_date as 调拨日期,che_out_date as 领料日期,location as 存放地点 FROM  RT_WETBLUE WHERE che_out_date is null and source1<>''  order by begin_date " '------全部
        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_detail.DataSource = dt1

        dgv_detail.Columns("供应商").Width = 70
        dgv_detail.Columns("皮源").Width = 200
        dgv_detail.RowHeadersWidth = 10
        dgv_detail.Columns("单价").Width = 50
        dgv_detail.Columns("手册号").Width = 150
        dgv_detail.Columns("入库日期").Width = 110
        dgv_detail.Columns("调拨日期").Width = 110
        dgv_detail.Columns("领料日期").Width = 110
        dgv_detail.Columns("存放地点").Width = 150




        sql = "SELECT upper(SUPPLIER) as 供应商,source1 as 皮源, sum(begin_weight) as 重量,count(*) as 笔数 FROM  RT_WETBLUE WHERE che_out_date is null and source1<>''  group by supplier,source1 order by supplier  " '----皮源加总
        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_pending_source.DataSource = dt1


        dgv_pending_source.Columns("皮源").Width = 150
        dgv_pending_source.RowHeadersWidth = 10


        sql = "select cast(sum(begin_weight) as numeric(10,0)) as 重量 FROM  RT_WETBLUE WHERE che_out_date is null and source1<>'' "   '----重量汇总
        cmd.CommandText = sql

        ' TextBox3.Text = cmd.ExecuteScalar
        MaskedTextBox1.Text = cmd.ExecuteScalar
        MaskedTextBox1.Mask = "#,###,###"



        sql = "select sum(begin_weight) as 重量 FROM  RT_WETBLUE WHERE source1<>'' and begin_date is null  "   '----未进厂重量汇总
        cmd.CommandText = sql

        'TextBox4.Text = cmd.ExecuteScalar.ToString

        MaskedTextBox2.Text = cmd.ExecuteScalar.ToString
        MaskedTextBox2.Mask = "#,###,###"

        sql = "select cast(sum(begin_weight) as numeric(10,0)) as 重量 FROM  RT_WETBLUE WHERE bao_out_date is null and source1<>'' and begin_date is not null  "   '----重量汇总 保税仓
        cmd.CommandText = sql

        ' TextBox8.Text = cmd.ExecuteScalar

        MaskedTextBox3.Text = cmd.ExecuteScalar
        MaskedTextBox3.Mask = "#,###,###"

        sql = "select sum(begin_weight) as 重量 FROM  RT_WETBLUE WHERE che_out_date is null and che_in_date is not null and source1<>''  "   '----重量汇总 车间
        cmd.CommandText = sql

        'TextBox7.Text = cmd.ExecuteScalar
        MaskedTextBox4.Text = cmd.ExecuteScalar
        MaskedTextBox4.Mask = "#,###,###"




        sql = "select bao_in_date as 进厂日期,upper(SUPPLIER) as 供应商,source1 as 皮源,begin_price as 单价,begin_weight as 重量,location as 存放地点 from RT_WETBLUE where (SUPPLIER <> '') and BAO_IN_DATE is not null and che_out_date is null order by bao_in_date"  '---- 未领料

        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_pending.DataSource = dt1


        dgv_pending.Columns("皮源").Width = 200
        dgv_pending.RowHeadersWidth = 10







    End Sub

    Private Sub dgv_supplier_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgv_supplier.CellMouseClick  '---点选供应商


        sql = "SELECT xno as 手册号,upper(SUPPLIER) as 供应商,source1 as 皮源, begin_weight as 重量,begin_price as 单价 , bao_in_date as 入库日期,che_in_date as 调拨日期,che_out_date as 领料日期 FROM  RT_WETBLUE WHERE supplier=N'" & dgv_supplier.Rows(dgv_supplier.CurrentCell.RowIndex).Cells("供应商").Value & "' order by source1,begin_date desc "
        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_detail.DataSource = dt1


        'dgv_detail.Columns("手册号").Width = 180
        'dgv_detail.Columns("供应商").Width = 70
        'dgv_detail.Columns("皮源").Width = 200
        'dgv_detail.Columns("单价").Width = 50
        'dgv_detail.RowHeadersWidth = 10


        sql = "SELECT source1 as 皮源,count(*) as 笔数 FROM  RT_WETBLUE WHERE supplier=N'" & dgv_supplier.Rows(dgv_supplier.CurrentCell.RowIndex).Cells("供应商").Value & "'  group by source1 "
        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_source.DataSource = dt1


        dgv_source.Columns("皮源").Width = 150
        dgv_source.RowHeadersWidth = 10




    End Sub

    Private Sub dgv_source2_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgv_source2.CellMouseClick  '--点选皮源


        sql = "SELECT xno as 手册号,upper(SUPPLIER) as 供应商,source1 as 皮源, begin_weight as 重量,begin_price as 单价 , bao_in_date as 入库日期,che_in_date as 调拨日期,che_out_date as 领料日期,location as 存放地点 FROM  RT_WETBLUE WHERE source1=N'" & dgv_source2.Rows(dgv_source2.CurrentCell.RowIndex).Cells("皮源").Value & "' order by source1,begin_date desc "
        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_detail.DataSource = dt1


        'dgv_detail.Columns("手册号").Width = 110
        'dgv_detail.Columns("供应商").Width = 70
        'dgv_detail.Columns("皮源").Width = 150
        'dgv_detail.Columns("单价").Width = 50
        'dgv_detail.RowHeadersWidth = 10






        sql = "SELECT supplier as 供应商,count(*) as 笔数 FROM  RT_WETBLUE WHERE source1=N'" & dgv_source2.Rows(dgv_source2.CurrentCell.RowIndex).Cells("皮源").Value & "'  group by supplier "
        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_source.DataSource = dt1


        'dgv_source.Columns("供应商").Width = 70
        'dgv_source.RowHeadersWidth = 10









    End Sub

    Private Sub TextBox1_KeyUp(sender As Object, e As KeyEventArgs) Handles TextBox1.KeyUp  '---皮源搜索









        sql = "SELECT source1 as 皮源,count(*) as 笔数 FROM  RT_WETBLUE WHERE source1 like N'%" & TextBox1.Text & "%' and source1 <>''  group by source1 "
        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_source2.DataSource = dt1


        'dgv_source2.Columns("皮源").Width = 150
        'dgv_source2.RowHeadersWidth = 10






    End Sub

    Private Sub TextBox2_KeyUp(sender As Object, e As KeyEventArgs) Handles TextBox2.KeyUp '-----找重量



        sql = "SELECT supplier as 供应商,source1 as 皮源 FROM  RT_WETBLUE WHERE begin_weight ='" & TextBox2.Text & "'  "
        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_source2.DataSource = dt1


        dgv_source2.Columns("皮源").Width = 150
        dgv_source2.RowHeadersWidth = 10




    End Sub

    Private Sub dgv_pending_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgv_pending.CellContentClick

    End Sub

    Private Sub dgv_pending_source_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgv_pending_source.CellContentClick

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click  '未领料


        sql = "SELECT xno as 手册号,upper(SUPPLIER) as 供应商,source1 as 皮源, begin_weight as 重量,begin_price as 单价 , bao_in_date as 入库日期,che_in_date as 调拨日期,che_out_date as 领料日期,location as 存放地点 FROM  RT_WETBLUE WHERE che_out_date is null and begin_date is not null and source1<>''  order by begin_date " '------全部
        cmd.CommandText = sql
        da.SelectCommand = cmd
        dt1 = New DataTable
        da.Fill(dt1)

        dgv_detail.DataSource = dt1

        dgv_detail.Columns("供应商").Width = 70
        dgv_detail.Columns("皮源").Width = 200
        dgv_detail.RowHeadersWidth = 10
        dgv_detail.Columns("单价").Width = 50
        dgv_detail.Columns("手册号").Width = 150
        dgv_detail.Columns("入库日期").Width = 110
        dgv_detail.Columns("调拨日期").Width = 110
        dgv_detail.Columns("领料日期").Width = 110
        dgv_detail.Columns("存放地点").Width = 150

    End Sub
End Class