|
Dim dt As DataTable
Dim cmd As new SQLCommand
cmd.CommandText = "Select Distinct {生产主表}.生产单号, {原材料出仓主表}.出仓日期, {原材料出仓主表}.基材型号, {原材料出仓主表}.基材规格, {原材料出仓主表}.厂商代号, {原材料出仓主表}.总件数, {原材料出仓主表}.总重量, {原材料出仓主表}.总金额, {生产主表}.生产类型, {生产入库明细表}.产品型号, {生产入库明细表}.产品规格"
cmd.CommandText &= " From ({原材料出仓主表} Inner JOIN {生产入库明细表} ON {生产入库明细表}.[生产单号] = {原材料出仓主表}.[生产单号]) Inner JOIN {生产主表} ON {生产主表}.[生产单号] = {原材料出仓主表}.[生产单号] where {生产主表}.已完成 = true and {生产主表}.已记账 = false"
dt = cmd.ExecuteReader
dt.DataCols.Add("客户代号", Gettype(String),255)
dt.DataCols.Add("产品金额", Gettype(Double))
dt.DataCols.Add("一等品出货重量", Gettype(Double))
dt.DataCols.Add("二等品出货重量", Gettype(Double))
dt.DataCols.Add("一等品库存", Gettype(Double))
dt.DataCols.Add("二等品库存", Gettype(Double))
dt.DataCols.Add("单边库存", Gettype(Double))
dt.DataCols.Add("成品率", Gettype(Double))
dt.DataCols.Add("一等品率", Gettype(Double))
dt.DataCols.Add("出货率", Gettype(Double))
Dim filter As String = ""
Dim ed As Date = e.Form.Controls("截止日期").Value
If ed <> Nothing Then
filter &= " and 入库时间 <= #" & ed & "# "
End If
For Each dr As DataRow In dt.DataRows
dr("客户代号") = DataTables("生产入库明细表").GetComboListString("客户代号", "生产单号 = '" & dr("生产单号") & "'").Replace("|", "/")
dr("产品金额") = DataTables("生产入库明细表").Compute("sum(产品金额)", "产品型号 = '" & dr("产品型号") & "' and 产品规格 = '" & dr("产品规格") & "' and 已出货 = true " & filter)
dr("一等品出货重量") = DataTables("生产入库明细表").Compute("sum(每箱净重)", "产品型号 = '" & dr("产品型号") & "' and 产品规格 = '" & dr("产品规格") & "' and 已出货 = true and 等级 = '一等品' " & filter)
dr("二等品出货重量") = DataTables("生产入库明细表").Compute("sum(每箱净重)","产品型号 = '" & dr("产品型号") & "' and 产品规格 = '" & dr("产品规格") & "' and 已出货 = true and 等级 = '二等品' " & filter)
dr("一等品库存") = DataTables("生产入库明细表").Compute("sum(每箱净重)","产品型号 = '" & dr("产品型号") & "' and 产品规格 = '" & dr("产品规格") & "' and 已出货 = false and 等级 = '一等品' " & filter)
dr("二等品库存") = DataTables("生产入库明细表").Compute("sum(每箱净重)","产品型号 = '" & dr("产品型号") & "' and 产品规格 = '" & dr("产品规格") & "' and 已出货 = false and 等级 = '二等品' " & filter)
dr("单边库存") = DataTables("生产入库明细表").Compute("sum(每箱净重)","产品型号 = '" & dr("产品型号") & "' and 产品规格 = '" & dr("产品规格") & "' and 已出货 = false and 等级 = '单边' " & filter)
dr("成品率") = (dr("一等品出货重量")+dr("二等品出货重量")+dr("一等品库存")+dr("二等品库存")+dr("单边库存"))/dr("总重量")
dr("一等品率") = (dr("一等品出货重量")+dr("一等品库存"))/dr("总重量")
dr("出货率") = (dr("一等品出货重量")+dr("二等品出货重量"))/dr("总重量")
DataTables("生产主表").ReplaceFor("已记账", True, "生产单号 = '" & dr("生产单号") & "' and 已完成 = true")
DataTables("生产主表").save
Next
e.Form.Controls("Table1").Table.Datasource = dt
|
加好友 
发短信
Post By：2014/9/30 19:33:00 [显示全部帖子]
