public bool RegenDailyReport(DateTime reportDate, out string emsg) { using (TransactionScope transaction = new TransactionScope()) //使用事务 { try { var dalPeriod = new dalAccPeriodList(); modAccPeriodList modPeriod = dalPeriod.GetDuringItem(reportDate, out emsg); if (modPeriod == null) { return(false); } string sql = "delete from warehouse_inout_daily_report where report_date=@report_date"; SqlParameter[] parm = { new SqlParameter("report_date", SqlDbType.DateTime) }; parm[0].Value = reportDate; int i = SqlHelper.ExecuteNonQuery(CommandType.Text, sql, parm); sql = "select a.product_id,a.size*(a.start_qty+a.input_qty-a.output_qty) as start_qty,0 end_qty,0 purchase_qty,0 sale_qty,0 as overflow_qty,0 loss_qty,0 production_output,0 production_input,0 transfer_output,0 transfer_input " + "from warehouse_product_inout a where a.inout_date between @first_date and @yesterday_date " + "union all select a.product_id,0 start_qty,a.size * (a.start_qty + a.input_qty - a.output_qty) as end_qty,0 purchase_qty,0 sale_qty,0 as overflow_qty,0 loss_qty,0 production_output,0 production_input,0 transfer_output,0 transfer_input " + "from warehouse_product_inout a where a.inout_date between @first_date and @today_date " + "union all select a.product_id,0 start_qty,0 end_qty,a.size*(a.input_qty-a.output_qty) purchase_qty,0 sale_qty,0 as overflow_qty,0 loss_qty,0 production_output,0 production_input,0 transfer_output,0 transfer_input " + "from warehouse_product_inout a where a.form_type in ('采购收货','采购退货') and a.inout_date = @inout_date " + "union all select a.product_id,0 start_qty,0 end_qty,0 purchase_qty,a.size*(a.output_qty-a.input_qty) sale_qty,0 as overflow_qty,0 loss_qty,0 production_output,0 production_input,0 transfer_output,0 transfer_input " + "from warehouse_product_inout a where a.form_type in ('送货单','退货单') and a.inout_date = @inout_date " + "union all select a.product_id,0 start_qty,0 end_qty,0 purchase_qty,0 sale_qty,a.size*a.input_qty as overflow_qty,a.size*a.output_qty as loss_qty,0 production_output,0 production_input,0 transfer_output,0 transfer_input " + "from warehouse_product_inout a where a.form_type in ('溢余入库','借入物入库','借出物入库','损耗出库','借入物出库','借出物出库') and a.inout_date = @inout_date " + "union all select a.product_id,0 start_qty,0 end_qty,0 purchase_qty,0 sale_qty,0 overflow_qty,0 loss_qty,a.size*a.input_qty production_output,a.size*a.output_qty production_input,0 transfer_output,0 transfer_input " + "from warehouse_product_inout a where a.form_type in ('外发单','内部单') and a.inout_date = @inout_date " + "union all select a.product_id,0 start_qty,0 end_qty,0 purchase_qty,0 sale_qty,0 overflow_qty,0 loss_qty,0 production_output,0 production_input,a.size*a.output_qty transfer_output,a.size*a.input_qty transfer_input " + "from warehouse_product_inout a where a.form_type in ('转仓入库','转仓出库') and a.inout_date = @inout_date"; sql = "insert into warehouse_inout_daily_report(report_date,product_id,start_qty,end_qty,purchase_qty,sale_qty,overflow_qty,loss_qty,production_output,production_input,transfer_output,transfer_input) " + "select @reportDate,product_id,sum(start_qty) start_qty,sum(end_qty) end_qty,sum(purchase_qty) purchase_qty,sum(sale_qty) sale_qty,sum(overflow_qty) overflow_qty,sum(loss_qty) loss_qty," + "sum(production_output) production_output,sum(production_input) production_input,sum(transfer_output) transfer_output,sum(transfer_input) transfer_input " + "from ( " + sql + ") t group by product_id"; SqlParameter[] parm2 = { new SqlParameter("reportDate", SqlDbType.DateTime), new SqlParameter("first_date", SqlDbType.DateTime), new SqlParameter("yesterday_date", SqlDbType.DateTime), new SqlParameter("today_date", SqlDbType.DateTime), new SqlParameter("inout_date", SqlDbType.DateTime) }; parm2[0].Value = reportDate; parm2[1].Value = modPeriod.StartDate; parm2[2].Value = reportDate.AddDays(-1); parm2[3].Value = reportDate; parm2[4].Value = reportDate; i = SqlHelper.ExecuteNonQuery(CommandType.Text, sql, parm2); transaction.Complete(); //就这句就可以了。 if (i > 0) { emsg = string.Empty; } else { emsg = "未产生任何数据!"; } return(true); } catch (Exception ex) { emsg = ex.Message; return(false); } } }