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

Imports System.Data.SqlClient
Public Class Frm_Stock
    Dim cmd As New SqlCommand
    Dim conn As New SqlConnection
    Dim conn1 As New SqlConnection
    Dim dr As SqlDataReader
    Dim da As New SqlDataAdapter

    Dim ds As New DataSet
    Dim sql As String = ""
    Dim p() As String
    Private Sub Frm_Stock_Load(sender As Object, e As EventArgs) Handles MyBase.Load


        conn.ConnectionString = connstring
        conn.Open()

        '" Me.MdiParent = FrmMDI

        cmd.Connection = conn



        conn1.ConnectionString = connstring
        conn1.Open()



        sql = "select date as 日期 from rt_month_tm group by date order by date desc"
        cmd.CommandText = sql
        dr = cmd.ExecuteReader

        While dr.Read

            ListBox1.Items.Add(dr("日期"))

        End While
        dr.Close()

    End Sub

    Private Sub ListBox1_MouseClick(sender As Object, e As MouseEventArgs) Handles ListBox1.MouseClick

        txt_begin.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
        TextBox7.Text = ""



        p = Split(ListBox1.Items(ListBox1.SelectedIndex), "-")


        If Val(p(1)) <> 12 Then
            p(1) = p(1) + 1
        Else
            p(0) = Val(p(0)) + 1
            p(1) = 1
        End If



        sql = "SELECT cast(SUM(wet) as numeric(10,0)) AS 蓝皮重 FROM RT_MONTH_TM WHERE (date = '" & ListBox1.Items(ListBox1.SelectedIndex) & "')"  '---反毛期初
        cmd.CommandText = sql
        txt_begin.Text = cmd.ExecuteScalar.ToString


        sql = "SELECT cast(SUM(qty) / 5.8 + SUM(weight) / 2 as numeric(10,0)) AS weight FROM RT_MONTH_PU WHERE (date = '" & ListBox1.Items(ListBox1.SelectedIndex) & "')" '---PU期初
        cmd.CommandText = sql
        txt_begin_pu.Text = cmd.ExecuteScalar.ToString
        txt_begin_pu_b.Text = cmd.ExecuteScalar.ToString


        sql = "select card1 as 卡号,cast(sum(蓝皮重) as numeric(10,0)) as 蓝皮重 from(SELECT left(card,1) as card, case when card like 'B%' then 'B' else 'F' end as card1,wet as  蓝皮重 FROM RT_MONTH_TM  WHERE (date = '" & ListBox1.Items(ListBox1.SelectedIndex) & "')) as p group by card1"

        cmd.CommandText = sql
        dr = cmd.ExecuteReader
        While dr.Read

            If dr("卡号") = "B" Then

                txt_begin_b.Text = dr("蓝皮重")
            Else
                txt_begin_f.Text = dr("蓝皮重")
            End If

        End While
        dr.Close()




        sql = "SELECT SUM(TOTAL)/2 AS Expr1 FROM RT_RETAN_RECORD_D where year(date)= '" & p(0) & "' and month(date)='" & p(1) & "'" '---总下鼓
        cmd.CommandText = sql
        If cmd.ExecuteScalar Is Nothing Then
            TextBox2.Text = 0
        Else
            TextBox2.Text = cmd.ExecuteScalar.ToString

        End If



        sql = "SELECT SUM(TOTAL)/2 AS Expr1 FROM RT_RETAN_RECORD_D where type=N'打底' and year(date)= '" & p(0) & "' and month(date)='" & p(1) & "'"  '-----打底
        cmd.CommandText = sql

        If cmd.ExecuteScalar Is Nothing Then
            TextBox3.Text = 0
        Else
            TextBox3.Text = cmd.ExecuteScalar.ToString

        End If

        sql = "SELECT top(1) date FROM RT_RETAN_RECORD_D where type=N'打底' and year(date)= '" & p(0) & "' and month(date)='" & p(1) & "' order by date desc"  '-----打底日期
        cmd.CommandText = sql

        If cmd.ExecuteScalar Is Nothing Then
            TextBox7.Text = 0
        Else
            TextBox7.Text = cmd.ExecuteScalar.ToString

        End If



        sql = "select 卡号,sum(weight) as 蓝皮重 from (select case when card='B' then 'B' else 'F' end as 卡号,sum(weight) as weight from (SELECT left(card,1) as card,SUM(TOTAL)/2 AS weight FROM RT_RETAN_RECORD_D where type=N'打底' and year(date)= '" & p(0) & "' and month(date)='" & p(1) & "' group by left(card,1)) as p group by card) as q group by 卡号"
        cmd.CommandText = sql
        dr = cmd.ExecuteReader
        While dr.Read

            If dr("卡号") = "B" Then

                TextBox3_b.Text = dr("蓝皮重")
            Else
                TextBox3_f.Text = dr("蓝皮重")
            End If

        End While
        dr.Close()







        sql = "SELECT SUM(TOTAL)/2 AS Expr1 FROM RT_RETAN_RECORD_D where type=N'套染' and year(date)= '" & p(0) & "' and month(date)='" & p(1) & "'"   '-----套染
        cmd.CommandText = sql
        TextBox4.Text = cmd.ExecuteScalar.ToString

        sql = "SELECT SUM(TOTAL)/2 AS Expr1 FROM RT_RETAN_RECORD_D where type=N'回鼓' and  year(date)= '" & p(0) & "' and month(date)='" & p(1) & "'"   '----回鼓
        cmd.CommandText = sql
        TextBox5.Text = cmd.ExecuteScalar.ToString


        sql = "SELECT cast(SUM(sf)/5.8 as numeric(10,0)) AS Expr1 FROM RT_measure_daily where year(date)= '" & p(0) & "' and month(date)='" & p(1) & "'"   '---反毛量尺
        cmd.CommandText = sql
        TextBox6.Text = cmd.ExecuteScalar.ToString

        sql = "SELECT top(1) date FROM RT_measure_daily where year(date)= '" & p(0) & "' and month(date)='" & p(1) & "' order by date desc"   '---反毛量尺日期
        cmd.CommandText = sql
        If cmd.ExecuteScalar Is Nothing Then
            TextBox14.Text = 0
        Else
            TextBox14.Text = cmd.ExecuteScalar.ToString

        End If


        sql = "SELECT card as 卡号,sum(weight) as 蓝皮重 from (select case when left(card,1)='B' then 'B' else 'F' end as card,cast(SUM(sf)/5.8 as numeric(10,0)) AS weight FROM RT_measure_daily where year(date)= '" & p(0) & "' and month(date)='" & p(1) & "' group by left(card,1)) as p group by card"
        cmd.CommandText = sql
        dr = cmd.ExecuteReader
        While dr.Read

            If dr("卡号") = "B" Then

                TextBox6_b.Text = dr("蓝皮重")
            Else
                TextBox6_f.Text = dr("蓝皮重")
            End If

        End While
        dr.Close()



        sql = "SELECT cast(SUM(sf)/5.8 as numeric(10,0)) AS Expr1 FROM RT_PU_measure2 where  year(date)= '" & p(0) & "' and month(date)='" & p(1) & "'"     '----PU量尺
        cmd.CommandText = sql
        TextBox1.Text = cmd.ExecuteScalar.ToString

        sql = "SELECT top(1) date FROM RT_PU_measure2 where  year(date)= '" & p(0) & "' and month(date)='" & p(1) & "' order by date desc"     '----PU量尺日期
        cmd.CommandText = sql
        If cmd.ExecuteScalar Is Nothing Then
            TextBox10.Text = 0
        Else
            TextBox10.Text = cmd.ExecuteScalar.ToString

        End If



        TextBox1_b.Text = TextBox1.Text


        TextBox7.Text = Val(txt_begin.Text) + Val(txt_begin_pu.Text) + Val(TextBox3.Text) - Val(TextBox6.Text) - Val(TextBox1.Text)
        TextBox7_b.Text = Val(txt_begin_b.Text) + Val(txt_begin_pu_b.Text) + Val(TextBox3_b.Text) - Val(TextBox6_b.Text) - Val(TextBox1_b.Text)
        TextBox7_f.Text = Val(TextBox7.Text) - Val(TextBox7_b.Text)
    End Sub

    Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click


        sql = "drop table rt_crust_stock1"
        cmd.CommandText = sql
        cmd.ExecuteScalar()




        Dim pp() As String = Split(ListBox1.Items(ListBox1.SelectedIndex), "-")




        sql = "select * into rt_crust_stock1 from rt_month_tm where year(date)= '" & pp(0) & "' and month(date)='" & pp(1) & "'"   '---盘点表写入新表  ?????
        cmd.CommandText = sql
        cmd.ExecuteScalar()

        Dim cmd1 As New SqlCommand

        cmd1.Connection = conn1




        sql = "SELECT SERIAL, AREA, MONTH, TABLE1, TABLE2, DATE, CARD, s_card, CUST, COLOR, TOTAL/2 as total, CLASS, TYPE, DATE2, WEIGHT_TOTAL/2, GROUP_NAME, comment2, comment3, comment4 FROM RT_RETAN_RECORD_D where type=N'打底' and year(date)= '" & p(0) & "' and month(date)='" & p(1) & "' order by right(card,10) "  '-----加入打底
        cmd.CommandText = sql
        dr = cmd.ExecuteReader

        Dim M3 As Double = 0
        Dim m4 As Double = 0
        While dr.Read



            sql = "SELECT CARD FROM RT_CRUST_STOCK1 WHERE CARD='" & dr("CARD") & "'"
            cmd1.CommandText = sql

            Dim ANS2 As String = ""

            ANS2 = cmd1.ExecuteScalar

            If ANS2 = "" Then

                sql = "insert into rt_crust_stock1 (word,commentxx,serial,card,cust,color,weight) values(N'蓝皮',N'当月打底','" & dr("serial") & "','" & dr("card") & "',N'" & dr("cust") & "','" & dr("color") & "','" & dr("total") & "')"
                cmd1.CommandText = sql
                cmd1.ExecuteScalar()

                If (Microsoft.VisualBasic.Mid(dr("card"), 3, 1) = "P") Then

                    ListBox7.Items.Add(dr("card") & vbTab & dr("TOTAL"))
                    m4 = m4 + dr("TOTAL")

                Else
                    ListBox4.Items.Add(dr("card") & vbTab & dr("TOTAL"))
                    M3 = M3 + dr("TOTAL")
                End If





            End If

        End While
        dr.Close()

        TextBox21.Text = M3
        TextBox24.Text = m4

        sql = "SELECT CARD,sf FROM RT_MEASURE_DAILY WHERE (COMMENT1 LIKE N'%完%') and year(date)= '" & p(0) & "' and month(date)='" & p(1) & "' order by card"  '----把量完的在每日量尺记录中标注

        cmd.CommandText = sql
        dr = cmd.ExecuteReader


        Dim M5 As Double = 0
        While dr.Read


            sql = "update rt_measure_daily set finished='True' where right(card,10)='" & Microsoft.VisualBasic.Right(dr("card"), 10) & "'"
            cmd1.CommandText = sql
            cmd1.ExecuteNonQuery()

            'sql = "UPDATE RT_CRUST_STOCK SET FINISHED='TRUE' WHERE right(card,10)='" & Microsoft.VisualBasic.Right(dr("card"), 10) & "'"
            'cmd1.CommandText = sql
            'cmd1.ExecuteNonQuery()
            M5 = M5 + dr("SF")
        End While
        dr.Close()




        sql = "SELECT RIGHT(CARD,10) AS CARD,SUM(sf) AS SF FROM RT_MEASURE_DAILY WHERE finished='True' and year(date)= '" & p(0) & "' and month(date)='" & p(1) & "' GROUP BY RIGHT(CARD,10)  order by RIGHT(CARD,10)"  '----把量完的删除_對

        cmd.CommandText = sql
        dr = cmd.ExecuteReader

        Dim m1 As Double = 0
        Dim m2 As Double = 0
        Dim M6 As Double = 0
        Dim ANS1 As String = ""
        While dr.Read


            sql = "SELECT RIGHT(CARD,10) AS CARD FROM RT_CRUST_STOCK1 where right(card,10)='" & dr("card") & "'"
            cmd1.CommandText = sql

            ANS1 = cmd1.ExecuteScalar()

            sql = "UPDATE rt_crust_stock1 SET FINISHED='TRUE' where right(card,10)='" & dr("card") & "'"
            cmd1.CommandText = sql
            Dim ans As Integer = 0
            ans = cmd1.ExecuteNonQuery()

            If ans = 0 Then
                ListBox2.Items.Add(dr("card") & vbTab & Math.Round(dr("sf"), 1)) '----找不到卡号
                m1 = m1 + dr("sf") / 5.8
            Else
                ListBox3.Items.Add(dr("card") & vbTab & Math.Round(dr("sf"), 1))
                m2 = m2 + dr("sf") / 5.8
            End If



        End While
        dr.Close()




        TextBox9.Text = Math.Round(m1, 0)
        TextBox18.Text = Math.Round(m2, 0)



        sql = "SELECT CARD as card,sum(sf) as sf FROM rt_measure_daily WHERE finished is null and  year(date)= '" & p(0) & "' and month(date)='" & p(1) & "' group by card order by card"  '----未量完

        cmd.CommandText = sql
        dr = cmd.ExecuteReader

        m1 = 0
        m2 = 0

        While dr.Read


            sql = "update rt_crust_stock1 set measured='" & dr("sf") & "' where right(card,10)='" & Microsoft.VisualBasic.Right(dr("card"), 10) & "'"
            cmd1.CommandText = sql
            Dim ans As Integer = 0
            ans = cmd1.ExecuteNonQuery()

            If ans = 0 Then
                ListBox5.Items.Add(dr("card") & vbTab & Math.Round(dr("sf"), 1)) '----找不到卡号
                m1 = m1 + Math.Round(dr("sf") / 5.8, 1)
            Else
                ListBox6.Items.Add(dr("card") & vbTab & Math.Round(dr("sf"), 1))
                m2 = m2 + Math.Round(dr("sf") / 5.8, 1)
            End If

        End While
        dr.Close()


        TextBox22.Text = m1
        TextBox23.Text = m2


        sql = "update rt_crust_stock1 set measured=0 where measured is null"
        cmd1.CommandText = sql

        cmd1.ExecuteNonQuery()


        sql = "SELECT  rt_crust_stock1.serial, rt_crust_stock1.card, rt_crust_stock1.class, rt_crust_stock1.cust, rt_crust_stock1.color,                 " &
"                            rt_crust_stock1.weight, rt_crust_stock1.tech, rt_crust_stock1.date, rt_crust_stock1.commentXX, rt_crust_stock1.measured,    " &
"                            rt_crust_stock1.finished, change, RT_BG_CHANGE.FIRST, RT_BG_CHANGE.SECOND                                               " &
" fROM              rt_crust_stock1 LEFT OUTER JOIN                                                                                                   " &
"                            RT_BG_CHANGE ON RIGHT(rt_crust_stock1.card, 10) = RIGHT(RT_BG_CHANGE.CARD, 10)                                          " &
" WHERE          (RT_BG_CHANGE.SECOND Is Not NULL) And (LEN(RT_BG_CHANGE.FIRST) < 10) "
        cmd.CommandText = sql

        dr = cmd.ExecuteReader

        Dim a As Integer = 0
        While dr.Read

            sql = "update rt_crust_stock1 set change='True' where CARD='" & dr("CARD") & "'"  'B改F, F改b
            cmd1.CommandText = sql
            cmd1.ExecuteNonQuery()

            a = a + 1
        End While
        dr.Close()

        sql = "SELECT  CARD,cast(SUM(BAL) as numeric(10,0)) AS BAL FROM (SELECT CASE WHEN LEFT(card, 1) = 'b' THEN 'B' ELSE 'F' END AS CARD, CASE WHEN WORD = N'蓝皮' THEN WEIGHT - MEASURED / 5.8 ELSE WEIGHT / 0.6 - MEASURED / 5.8 END AS BAL, commentXX, measured, CHANGE FROM rt_crust_stock1 WHERE (finished IS NULL)) AS P GROUP BY CARD"  '期未BF
        Dim dt As New DataTable
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)

        DGV1.DataSource = dt


        DGV1.AllowUserToAddRows = False
        dgv2.AllowUserToAddRows = False
        dgv3.AllowUserToAddRows = False
        dgv4.AllowUserToAddRows = False


        sql = "SELECT cast(sum(bal) as numeric(10,0)) as SUM from (select CARD,SUM(BAL) AS BAL FROM (SELECT CASE WHEN LEFT(card, 1) = 'b' THEN 'B' ELSE 'F' END AS CARD, CASE WHEN WORD = N'蓝皮' THEN WEIGHT - MEASURED / 5.8 ELSE WEIGHT / 0.6 - MEASURED / 5.8 END AS BAL, commentXX, measured, CHANGE FROM rt_crust_stock1 WHERE (finished IS NULL)) AS P GROUP BY CARD) as p"
        Dim dt1 As New DataTable
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt1)
        dgv2.DataSource = dt1    '期未BF加总


        sql = "select Changed,cast(sum(bal) as numeric(10,0)) as BAL from (SELECT CARD,CASE WHEN WORD=N'蓝皮'THEN WEIGHT - MEASURED / 5.8 ELSE WEIGHT / 0.6 - MEASURED / 5.8 END AS BAL,CASE WHEN LEFT(CARD,1)='B' THEN 'F' ELSE 'B' END AS CHANGED FROM RT_CRUST_STOCK1 WHERE CHANGE=1) as p group by changed"
        Dim dt2 As New DataTable
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt2)
        dgv3.DataSource = dt2    'B改F




        Dim dt3 As New DataTable
        sql = "SELECT CARD, SUM(wet) AS Expr1 FROM (SELECT CASE WHEN LEFT(card, 1) = 'b' THEN 'B' ELSE 'F' END AS CARD, wet FROM RT_MONTH_TM WHERE (MONTH(date) = 12)) AS p GROUP BY   CARD"  '盘点表

        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt3)
        dgv4.DataSource = dt3





        MsgBox(a)


    End Sub

    Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click


        Dim conn1 As New SqlConnection
        conn1.ConnectionString = connstring
        conn1.Open()

        Dim cmd1 As New SqlCommand

        cmd1.Connection = conn1

        Dim dr1 As SqlDataReader












    End Sub

    Private Sub Button12_Click(sender As Object, e As EventArgs) Handles Button12.Click


        Dim S As String = ""

        For X As Integer = 0 To ListBox2.Items.Count - 1
            S = S & vbCrLf & ListBox2.Items(X)
        Next

        '  MsgBox(S)
        Clipboard.SetText(S)

    End Sub

    Private Sub Button13_Click(sender As Object, e As EventArgs) Handles Button13.Click
        Dim S As String = ""

        For X As Integer = 0 To ListBox3.Items.Count - 1
            S = S & vbCrLf & ListBox3.Items(X)
        Next

        '  MsgBox(S)
        Clipboard.SetText(S)

    End Sub

    Private Sub Button16_Click(sender As Object, e As EventArgs) Handles Button16.Click
        Dim S As String = ""

        For X As Integer = 0 To ListBox4.Items.Count - 1
            S = S & vbCrLf & ListBox4.Items(X)
        Next

        MsgBox(Len(S))
        Clipboard.SetText(S)

    End Sub

    Private Sub Button14_Click(sender As Object, e As EventArgs) Handles Button14.Click


        Dim S As String = ""

        For X As Integer = 0 To ListBox5.Items.Count - 1
            S = S & vbCrLf & ListBox5.Items(X)
        Next

        '  MsgBox(S)
        Clipboard.SetText(S)

    End Sub

    Private Sub Button15_Click(sender As Object, e As EventArgs) Handles Button15.Click


        Dim S As String = ""

        For X As Integer = 0 To ListBox6.Items.Count - 1
            S = S & vbCrLf & ListBox6.Items(X)
        Next

        '  MsgBox(S)
        Clipboard.SetText(S)

    End Sub
End Class