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