/// <summary>
/// get product wip info
/// <summary>
/// <param name=accname>accname</param>
/// <param name=productid>productid</param>
/// <param name=mod>model object of warehouseproductinout</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public BindingCollection <modProductWip> GetProductWip(string accname, bool tempflag, out string emsg)
{
try
{
BindingCollection <modProductWip> modellist = new BindingCollection <modProductWip>();
//Execute a query to read the categories
dalAccPeriodList dal = new dalAccPeriodList();
modAccPeriodList mod = dal.GetItem(accname, out emsg);
string sql = string.Format("select count(1) from warehouse_product_inout where inout_date='{0}'", mod.StartDate);
int cnt = Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
if (cnt == 0 && mod.Seq > 1)
{
BalanceWip(mod.Seq - 1, mod.StartDate, out emsg);
}
if (mod.LockFlag == 0 && mod.EndDate < DateTime.Today)
{
mod.EndDate = DateTime.Today;
}
string tempwhere = string.Empty;
if (tempflag)
{
tempwhere = "and b.product_type='临时' ";
}
string inoutdatewhere = "and a.inout_date >= '" + mod.StartDate + "' and a.inout_date <= '" + mod.EndDate + "' ";
sql = "select a.product_id,b.product_name,b.min_qty,b.max_qty,sum(a.size * a.start_qty) start_qty,sum(a.size * a.input_qty) input_qty,sum(a.size * a.output_qty) output_qty "
+ "from warehouse_product_inout a inner join product_list b on a.product_id=b.product_id where 1=1 " + inoutdatewhere + tempwhere
+ "group by a.product_id,b.product_name,b.min_qty,b.max_qty order by a.product_id";
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modProductWip model = new modProductWip();
model.ProductId = dalUtility.ConvertToString(rdr["product_id"]);
model.ProductName = dalUtility.ConvertToString(rdr["product_Name"]);
model.StartQty = dalUtility.ConvertToDecimal(rdr["start_qty"]);
model.InputQty = dalUtility.ConvertToDecimal(rdr["input_qty"]);
model.OutputQty = dalUtility.ConvertToDecimal(rdr["output_qty"]);
model.EndQty = model.StartQty + model.InputQty - model.OutputQty;
model.MinQty = dalUtility.ConvertToDecimal(rdr["min_qty"]);
model.MaxQty = dalUtility.ConvertToDecimal(rdr["max_qty"]);
modellist.Add(model);
}
}
emsg = null;
return(modellist);
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}
/// <summary>
/// get useless product
/// <summary>
/// <param name=out emsg>return error message</param>
///<returns>details of all productlist</returns>
public BindingCollection <modProductList> GetUselessProduct(string accname, out string emsg)
{
try
{
BindingCollection <modProductList> modellist = new BindingCollection <modProductList>();
//Execute a query to read the categories
int inoutdays = 30;
dalSysParameters dalpara = new dalSysParameters();
modSysParameters modpara = dalpara.GetItem("PRODUCT_CLEAR_DAYS", out emsg);
if (!string.IsNullOrEmpty(modpara.ParaValue) && Convert.ToInt32(modpara.ParaValue) >= inoutdays)
{
inoutdays = Convert.ToInt32(modpara.ParaValue);
}
dalAccPeriodList dalp = new dalAccPeriodList();
modAccPeriodList modp = dalp.GetItem(accname, out emsg);
string sql = string.Format(@"select product_id,product_name,product_type,barcode,size_flag,specify,brand,unit_no,remark,min_qty,max_qty,update_user,update_time
from product_list a
where status<>7 and update_time<=getdate()-7
and not exists(select '#' from acc_product_inout where acc_name='{0}' and product_id=a.product_id)
and not exists(select '#' from warehouse_product_inout where product_id=a.product_id and (inout_date>=getdate()-{1} or inout_date>='{2}'))
order by update_time" , accname, inoutdays, modp.StartDate);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modProductList model = new modProductList();
model.ProductId = dalUtility.ConvertToString(rdr["product_id"]);
model.ProductName = dalUtility.ConvertToString(rdr["product_name"]);
model.ProductType = dalUtility.ConvertToString(rdr["product_type"]);
model.Barcode = dalUtility.ConvertToString(rdr["barcode"]);
model.SizeFlag = dalUtility.ConvertToInt(rdr["size_flag"]);
model.Specify = dalUtility.ConvertToString(rdr["specify"]);
model.Brand = dalUtility.ConvertToString(rdr["brand"]);
model.UnitNo = dalUtility.ConvertToString(rdr["unit_no"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.MinQty = dalUtility.ConvertToDecimal(rdr["min_qty"]);
model.MaxQty = dalUtility.ConvertToDecimal(rdr["max_qty"]);
model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]);
model.UpdateTime = dalUtility.ConvertToDateTime(rdr["update_time"]);
modellist.Add(model);
}
}
emsg = null;
return(modellist);
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}
public bool BalanceWip(int accpreviousseq, DateTime startdate, out string emsg)
{
dalAccPeriodList dal = new dalAccPeriodList();
modAccPeriodList modprevious = dal.GetItem(accpreviousseq, out emsg);
string inoutdatewhere = "and a.inout_date >= '" + modprevious.StartDate + "' and a.inout_date <= '" + modprevious.EndDate + "' ";
string sql = "insert into warehouse_product_inout(warehouse_id,product_id,size,form_id,form_type,inv_no,start_date,start_qty,input_qty,output_qty,remark,update_user,update_time) "
+ "select a.warehouse_id,a.product_id,a.size,0 form_id,'上月结存','','" + startdate + "',sum(a.start_qty) start_qty,sum(a.input_qty) input_qty,sum(a.output_qty) output_qty,'查询结转','Auto',getdate() "
+ "from warehouse_product_inout a where 1=1 " + inoutdatewhere + "group by a.warehouse_id,a.product_id,a.size";
SqlHelper.ExecuteNonQuery(sql);
emsg = string.Empty;
return(true);
}
/// <summary>
/// get product wip info
/// <summary>
/// <param name=accname>accname</param>
/// <param name=productid>productid</param>
/// <param name=mod>model object of warehouseproductinout</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public modProductWip GetProductWipItem(string accname, string productid, out string emsg)
{
try
{
dalAccPeriodList dal = new dalAccPeriodList();
modAccPeriodList mod = dal.GetItem(accname, out emsg);
string inoutdatewhere = "and a.inout_date >= '" + mod.StartDate + "' and a.inout_date <= '" + mod.EndDate + "' ";
string sql = string.Format("select a.product_id,b.product_name,b.min_qty,b.max_qty,sum(a.size * a.start_qty) start_qty,sum(a.size * a.input_qty) input_qty,sum(a.size * a.output_qty) output_qty "
+ "from warehouse_product_inout a inner join product_list b on a.product_id=b.product_id where a.product_id='{0}' " + inoutdatewhere
+ "group by a.product_id,b.product_name,b.min_qty,b.max_qty", productid);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
if (rdr.Read())
{
modProductWip model = new modProductWip();
model.ProductId = dalUtility.ConvertToString(rdr["product_id"]);
model.ProductName = dalUtility.ConvertToString(rdr["product_Name"]);
model.StartQty = dalUtility.ConvertToDecimal(rdr["start_qty"]);
model.InputQty = dalUtility.ConvertToDecimal(rdr["input_qty"]);
model.OutputQty = dalUtility.ConvertToDecimal(rdr["output_qty"]);
model.EndQty = model.StartQty + model.InputQty - model.OutputQty;
model.MinQty = dalUtility.ConvertToDecimal(rdr["min_qty"]);
model.MaxQty = dalUtility.ConvertToDecimal(rdr["max_qty"]);
emsg = string.Empty;
return(model);
}
else
{
emsg = "No data found!";
return(null);
}
}
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}
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);
}
}
}