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
|