HF-MIS-SYS--LEEHSIEHJUI/WindowsApp1/99、未分類/FrmCalChem.vb

Imports System.Data.SqlClient
Imports System.Reflection
Public Class FrmCalChem
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable
    Dim conn As New SqlConnection
    Dim sql As String
    Private Sub FrmCalChem_Load(sender As Object, e As EventArgs) Handles MyBase.Load



        Dim type As Type = dgv1.GetType()
        Dim pi As PropertyInfo = type.GetProperty("DoubleBuffered", System.Reflection.BindingFlags.Instance Or System.Reflection.BindingFlags.NonPublic)
        pi.SetValue(dgv1, True, Nothing)


        conn.ConnectionString = connstring
        conn.Open()



        cmd.Connection = conn

        sql = "SELECT 品名,单价,供应商,产地,年,分类,现场库存,可慕库存,保税库存,合计, [3] as [3月],[4] as [4月],[5] as [5月],[6] as [6月],[7]  as[7月], (select max(c) from (values([3]),([4]),([5]),([6]),([7])) as maxV(c)) as 最大用量,CASE WHEN (合计-[7]) IS NULL THEN 0 ELSE (合计-[7]) END  as 即时库存             " &
" FROM (SELECT          RT_SYS_CHEM_MAIN.name AS 品名, RT_SYS_CHEM_MAIN.price AS 单价, RT_SYS_CHEM_MAIN.supplier AS 供应商,    " &
"                              RT_SYS_CHEM_MAIN.origin AS 产地, RT_SYS_CHEM_MAIN.rcp AS 水场工艺, RT_SYS_CHEM_MAIN.BG AS 关务, " &
"                             CAST(SUM(RT_SYS_CHEM_OUT.WEIGHT) AS numeric(10, 0)) AS 领料, YEAR(RT_SYS_CHEM_OUT.DATE) AS 年,   " &
"                             MONTH(RT_SYS_CHEM_OUT.DATE) AS 月, RT_SYS_CHEM_MAIN.CLASS1 AS 分类,                              " &
"                              rt_chem_stock_month1.stock1 AS 现场库存,                                                        " &
"                             rt_chem_stock_month1.stock2 AS 可慕库存, rt_chem_stock_month1.stock3 AS 保税库存,                " &
"                             rt_chem_stock_month1.stock1 + rt_chem_stock_month1.stock2 + rt_chem_stock_month1.stock3 AS 合计 " &
" FROM              RT_SYS_CHEM_MAIN LEFT OUTER JOIN                                                                          " &
"                             RT_SYS_CHEM_OUT_DB ON RT_SYS_CHEM_MAIN.name = RT_SYS_CHEM_OUT_DB.name LEFT OUTER JOIN           " &
"                             RT_SYS_CHEM_IN_L ON RT_SYS_CHEM_MAIN.name = RT_SYS_CHEM_IN_L.NAME LEFT OUTER JOIN               " &
"                             RT_SYS_CHEM_IN_S ON RT_SYS_CHEM_MAIN.name = RT_SYS_CHEM_IN_S.name LEFT OUTER JOIN               " &
"                             RT_SYS_CHEM_IN_F ON RT_SYS_CHEM_MAIN.name = RT_SYS_CHEM_IN_F.name LEFT OUTER JOIN               " &
"                             rt_chem_stock_month1 ON RT_SYS_CHEM_MAIN.name = rt_chem_stock_month1.name LEFT OUTER JOIN       " &
"                             RT_SYS_CHEM_OUT ON RT_SYS_CHEM_MAIN.name = RT_SYS_CHEM_OUT.NAME                                 " &
" GROUP BY   RT_SYS_CHEM_MAIN.name, RT_SYS_CHEM_MAIN.supplier, RT_SYS_CHEM_MAIN.origin,                                       " &
"                             RT_SYS_CHEM_MAIN.rcp, RT_SYS_CHEM_MAIN.BG, YEAR(RT_SYS_CHEM_OUT.DATE),                          " &
"                             MONTH(RT_SYS_CHEM_OUT.DATE), RT_SYS_CHEM_MAIN.CLASS1, RT_SYS_CHEM_MAIN.CLASS2,                  " &
"                             RT_SYS_CHEM_MAIN.price, rt_chem_stock_month1.stock1, rt_chem_stock_month1.stock2,               " &
"                             rt_chem_stock_month1.stock3, RT_SYS_CHEM_IN_F.weight, RT_SYS_CHEM_IN_S.weight,                  " &
"                             RT_SYS_CHEM_IN_L.WEIGHT, RT_SYS_CHEM_OUT_DB.weight)                                             " &
" AS p pivot (sum(领料) for 月 in([3],[4],[5],[6],[7]) ) as q "

        sql = "SELECT 品名,单价,供应商,产地,年,分类,现场库存,可慕库存,保税库存,合计, [3] as [3月],[4] as [4月],[5] as [5月],[6] as [6月],[7]  as[7月],              " &
" (select max(c) from (values([3]),([4]),([5]),([6]),([7])) as maxV(c)) as 最大用量,CASE WHEN (合计-[7]) IS NULL THEN 0 ELSE (合计-[7]) END  as 即时库存 FROM   " &
" (SELECT          RT_SYS_CHEM_MAIN.name AS 品名, RT_SYS_CHEM_MAIN.price AS 单价, RT_SYS_CHEM_MAIN.supplier AS 供应商,                                         " &
"                              RT_SYS_CHEM_MAIN.origin AS 产地, RT_SYS_CHEM_MAIN.rcp AS 水场工艺, RT_SYS_CHEM_MAIN.BG AS 关务,                                 " &
"                             CAST(SUM(RT_SYS_CHEM_OUT.WEIGHT) AS numeric(10, 0)) AS 领料, YEAR(RT_SYS_CHEM_OUT.DATE) AS 年,                                   " &
"                             MONTH(RT_SYS_CHEM_OUT.DATE) AS 月, RT_SYS_CHEM_MAIN.CLASS1 AS 分类,                                                              " &
"                             rt_chem_stock_month1.stock1 AS 现场库存, rt_chem_stock_month1.stock2 AS 可慕库存,                                                " &
"                             rt_chem_stock_month1.stock3 AS 保税库存,                                                                                         " &
"                             rt_chem_stock_month1.stock1 + rt_chem_stock_month1.stock2 + rt_chem_stock_month1.stock3 AS 合计                                 " &
" FROM              RT_SYS_CHEM_MAIN LEFT OUTER JOIN                                                                                                          " &
"                             rt_chem_stock_month1 ON RT_SYS_CHEM_MAIN.name = rt_chem_stock_month1.name LEFT OUTER JOIN                                       " &
"                             RT_SYS_CHEM_OUT ON RT_SYS_CHEM_MAIN.name = RT_SYS_CHEM_OUT.NAME                                                                 " &
" GROUP BY   RT_SYS_CHEM_MAIN.name, RT_SYS_CHEM_MAIN.supplier, RT_SYS_CHEM_MAIN.origin,                                                                       " &
"                             RT_SYS_CHEM_MAIN.rcp, RT_SYS_CHEM_MAIN.BG, YEAR(RT_SYS_CHEM_OUT.DATE),                                                          " &
"                             MONTH(RT_SYS_CHEM_OUT.DATE), RT_SYS_CHEM_MAIN.CLASS1, RT_SYS_CHEM_MAIN.CLASS2,                                                  " &
"                             RT_SYS_CHEM_MAIN.price, rt_chem_stock_month1.stock1, rt_chem_stock_month1.stock2,                                               " &
"                             rt_chem_stock_month1.stock3)  AS p                                                                                              " &
" pivot (sum(领料) for 月 in([3],[4],[5],[6],[7]) ) as q"


        cmd.CommandText = sql

        da.SelectCommand = cmd
        da.Fill(dt)
        dgv1.DataSource = dt



        dt.Columns.Add("外购入库")
        dt.Columns.Add("保税入库")
        dt.Columns.Add("可慕入库")
        dt.Columns.Add("调拨出库")

        dt.Columns.Add("余量(月)")
        dt.Columns.Add("前置时间")
        dt.Columns.Add("订货")



        dgv1.Columns(0).Width = 200
        dgv1.Columns(1).Width = 60
        dgv1.Columns(2).Width = 60
        dgv1.Columns(3).Width = 60
        dgv1.Columns(4).Width = 60
        dgv1.Columns(5).Width = 100
        dgv1.Columns(6).Width = 60
        dgv1.Columns(7).Width = 60
        dgv1.Columns(8).Width = 60
        dgv1.Columns(9).Width = 60
        dgv1.Columns(10).Width = 60
        dgv1.Columns(11).Width = 60
        dgv1.Columns(12).Width = 60
        dgv1.Columns(13).Width = 60
        dgv1.Columns(14).Width = 60
        dgv1.Columns(15).Width = 60
        dgv1.Columns(16).Width = 60
        dgv1.Columns(17).Width = 60
        dgv1.Columns(18).Width = 60
        dgv1.Columns(19).Width = 60
        dgv1.Columns(20).Width = 60
        dgv1.Columns(21).Width = 60
        dgv1.Columns(22).Width = 60




        Dim DR As SqlDataReader

        For x As Integer = 0 To dt.Rows.Count - 1
            Dim xx As Integer = 0
            Dim YY As Integer = 0



            sql = "SELECT CASE WHEN SUM(WEIGHT) IS NULL THEN 0 ELSE SUM(WEIGHT) END AS WEIGHT FROM RT_SYS_CHEM_IN_F WHERE NAME='" & dt.Rows(x).Item("品名") & "'"
            cmd.CommandText = sql
            DR = cmd.ExecuteReader
            If DR.Read Then
                dt.Rows(x).Item("外购入库") = DR("WEIGHT")
                dt.Rows(x).Item("即时库存") = dt.Rows(x).Item("即时库存") + dt.Rows(x).Item("外购入库")
            End If
            DR.Close()

            sql = "SELECT CASE WHEN SUM(WEIGHT) IS NULL THEN 0 ELSE SUM(WEIGHT) END AS WEIGHT FROM RT_SYS_CHEM_IN_S WHERE NAME='" & dt.Rows(x).Item("品名") & "' and month(date)=7"
            cmd.CommandText = sql
            DR = cmd.ExecuteReader
            If DR.Read Then
                dt.Rows(x).Item("保税入库") = DR("WEIGHT")
            End If
            DR.Close()

            sql = "SELECT CASE WHEN SUM(WEIGHT) IS NULL THEN 0 ELSE SUM(WEIGHT) END AS WEIGHT FROM RT_SYS_CHEM_IN_L WHERE NAME='" & dt.Rows(x).Item("品名") & "' and month(date)=7 "
            cmd.CommandText = sql
            DR = cmd.ExecuteReader
            If DR.Read Then
                dt.Rows(x).Item("可慕入库") = DR("WEIGHT")
            End If
            DR.Close()

            sql = "SELECT CASE WHEN SUM(WEIGHT) IS NULL THEN 0 ELSE SUM(WEIGHT) END AS WEIGHT FROM RT_SYS_CHEM_OUT_DB WHERE NAME='" & dt.Rows(x).Item("品名") & "' and month(date)=7"
            cmd.CommandText = sql
            DR = cmd.ExecuteReader
            If DR.Read Then
                dt.Rows(x).Item("调拨出库") = DR("WEIGHT")
                dt.Rows(x).Item("即时库存") = dt.Rows(x).Item("即时库存") - dt.Rows(x).Item("调拨出库")
            End If
            DR.Close()








            Select Case dt.Rows(x).Item("产地").ToString

                Case "台湾", "泰国", "新加坡"
                    dt.Rows(x).Item("前置时间") = 1

                    If dt.Rows(x).Item("最大用量").ToString = "" Then
                        xx = 0
                    Else
                        xx = dt.Rows(x).Item("最大用量")
                    End If

                    If dt.Rows(x).Item("即时库存").ToString = "" Then
                        YY = 0
                    Else
                        YY = dt.Rows(x).Item("即时库存")
                    End If

                    dt.Rows(x).Item("订货") = YY - xx * dt.Rows(x).Item("前置时间")
                    If xx <> 0 Then dt.Rows(x).Item("余量(月)") = Math.Round(YY / xx, 1)

                Case "美国", "荷兰", "西班牙", "德国", "义大利"
                    dt.Rows(x).Item("前置时间") = 3
                    dt.Rows(x).Item("订货") = 1

                    If dt.Rows(x).Item("最大用量").ToString = "" Then
                        xx = 0
                    Else
                        xx = dt.Rows(x).Item("最大用量")
                    End If

                    If dt.Rows(x).Item("即时库存").ToString = "" Then
                        YY = 0
                    Else
                        YY = dt.Rows(x).Item("即时库存")
                    End If

                    dt.Rows(x).Item("订货") = YY - xx * dt.Rows(x).Item("前置时间")
                    If xx <> 0 Then dt.Rows(x).Item("余量(月)") = Math.Round(YY / xx, 1)


                Case "山东", "上海", "厦门"
                    dt.Rows(x).Item("前置时间") = 0.5
                    dt.Rows(x).Item("订货") = 1

                    If dt.Rows(x).Item("最大用量").ToString = "" Then
                        xx = 0
                    Else
                        xx = dt.Rows(x).Item("最大用量")
                    End If

                    If dt.Rows(x).Item("即时库存").ToString = "" Then
                        YY = 0
                    Else
                        YY = dt.Rows(x).Item("即时库存")
                    End If

                    dt.Rows(x).Item("订货") = YY - xx * dt.Rows(x).Item("前置时间")
                    If xx <> 0 Then dt.Rows(x).Item("余量(月)") = Math.Round(YY / xx, 1)
            End Select








        Next




    End Sub
End Class