Esempio n. 1
0
        /// <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);
            }
        }
Esempio n. 2
0
        /// <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);
            }
        }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
 /// <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);
     }
 }