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);
}
}
}