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