コード例 #1
0
ファイル: ProductItemDao.cs プロジェクト: lxh2014/gigade-net
        /// <summary>
        /// ATM匯款未付款數量查詢
        /// </summary>chaojie1124j 2015/12/16 10:05am
        /// <param name="erp_id"></param>
        /// <returns></returns>
        public int GetATMStock(ProductItemQuery query)
        {
            StringBuilder str = new StringBuilder();
            StringBuilder strCondi = new StringBuilder();
            try
            {
                str.Append(" SELECT IFNULL(sum(case item_mode when 0 then buy_num when 2 then buy_num*parent_num end),0)as 'buynum' from order_detail od LEFT JOIN product_item pi USING(item_id) ");
                strCondi.AppendFormat(" where 1=1 and od.detail_status=0 and  item_mode<>1 ");
                if (!string.IsNullOrEmpty(query.Erp_Id))
                {
                    strCondi.AppendFormat(" and  pi.erp_id={0} ", query.Erp_Id);
                }
                return Convert.ToInt32(_access.getDataTable(str.ToString() + strCondi.ToString()).Rows[0]["buynum"]);

            }
            catch (Exception ex)
            {
                throw new Exception("ProductItemDao-->GetATMStock-->" + ex.Message + str.ToString() + strCondi.ToString(), ex);
            }
        }
コード例 #2
0
ファイル: ProductItemDao.cs プロジェクト: lxh2014/gigade-net
 /// <summary>
 /// 通過ERP編號,查看商品信息,然後確定是否修改。
 /// chaojie1124j add by 2015/12/16 10:32am
 /// </summary>
 /// <param name="query"></param>
 /// <returns></returns>
 public List<ProductItemQuery> GetProdItemByERp(ProductItemQuery query)
 {
     StringBuilder str = new StringBuilder();
     StringBuilder strCondi = new StringBuilder();
     try
     {
          str.AppendFormat("SELECT p.product_id,p.product_name,pi.item_id,CONCAT(p.spec_title_1,' ',ps1.spec_name) as Spec_Name_1 ");
          str.AppendFormat(" ,CONCAT(p.spec_title_2,' ',ps2.spec_name) as Spec_Name_2,pi.item_stock ");
         str.AppendFormat("  from product_item pi ");
         str.AppendFormat(" left JOIN product p on p.product_id=pi.product_id ");
         str.AppendFormat(" left JOIN product_spec ps1 on ps1.spec_id=pi.spec_id_1 ");
         str.AppendFormat(" left JOIN product_spec ps2 on  ps2.spec_id=pi.spec_id_2 ");
         strCondi.Append(" where 1=1 and  (p.product_mode=2 or p.prepaid=1) ");
         if (!string.IsNullOrEmpty(query.Erp_Id))
         {
             strCondi.AppendFormat(" and  pi.erp_id={0} ", query.Erp_Id);
         }
         return _access.getDataTableForObj<ProductItemQuery>(str.ToString() + strCondi.ToString());
     }
     catch (Exception ex)
     {
         throw new Exception("ProductItemDao-->GetProdItemByERp-->" + ex.Message + str.ToString() + strCondi.ToString(), ex);
     }
 }
コード例 #3
0
        public HttpResponseBase GetInventoryQueryList()// yachoa1120j 2015-9-10 商品庫存查詢
        {

            string json = string.Empty;
            int totalcount = 0;
            try
            {
                ProductItemQuery query = new ProductItemQuery();
                query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
                query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
                productitemMgr = new ProductItemMgr(connectionString);
                if (!string.IsNullOrEmpty(Request.Params["vendor_name_full_OR_vendor_id"]))
                {
                    query.vendor_name_full_OR_vendor_id = Request.Params["vendor_name_full_OR_vendor_id"];//供应商名称/编号
                }
                if (!string.IsNullOrEmpty(Request.Params["product_id_OR_product_name"]))
                {
                    query.product_id_OR_product_name = Request.Params["product_id_OR_product_name"];//商品编号/名称/商品細項編號
                }
                if (!string.IsNullOrEmpty(Request.Params["brand_id_OR_brand_name"]))
                {
                    query.brand_id_OR_brand_name = Request.Params["brand_id_OR_brand_name"];//品牌编号/名称
                }
                if (!string.IsNullOrEmpty(Request.Params["product_status"]))
                {
                    query.product_status = Convert.ToUInt32(Request.Params["product_status"]);//商品状态
                }
                if (!string.IsNullOrEmpty(Request.Params["sale_status"]))
                {
                    query.sale_status = Convert.ToUInt32(Request.Params["sale_status"]);//商品販售狀態
                }
                if (!string.IsNullOrEmpty(Request.Params["item_stock_start"]))
                {
                    query.item_stock_start = Convert.ToInt32(Request.Params["item_stock_start"]);//库存数量--开始
                }
                if (!string.IsNullOrEmpty(Request.Params["item_stock_end"]))
                {
                    query.item_stock_end = Convert.ToInt32(Request.Params["item_stock_end"]);//库存数量--结束
                }
                if (!string.IsNullOrEmpty(Request.Params["ignore_stockRdo"]))
                {
                    query.ignore_stock = Convert.ToInt32(Request.Params["ignore_stockRdo"]);//庫存為0時是否還能販售
                }
                List<ProductItemQuery> list = productitemMgr.GetInventoryQueryList(query, out totalcount);
                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
                timeConverter.DateTimeFormat = "yyyy-MM-dd";
                json = "{success:true,totalCount:" + totalcount + ",data:" + JsonConvert.SerializeObject(list, Formatting.Indented, timeConverter) + "}";
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return Response;
        }
コード例 #4
0
        /// <summary>
        /// 匯出商品建議採購信息
        /// </summary>
        public void ReportSuggestPurchaseExcel()
        {
            _paraMgr = new ParameterMgr(mySqlConnectionString);
            Parametersrc p = new Parametersrc();
            List<Parametersrc> Paralist = new List<Parametersrc>();
          
            DataTable dt = new DataTable();
            ProductItemQuery query = new ProductItemQuery();
            query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
            query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
            query.stockScope = int.Parse(Request.Params["stockScope"] ?? "0");//所有庫存
            query.sumDays = int.Parse(Request.Params["sumDays"] ?? "90");   //總天數
            query.periodDays = int.Parse(Request.Params["periodDays"] ?? "7"); //周期天數
            query.prepaid = int.Parse(Request.Params["perpaid"] ?? "-1");/*是否買斷*/
            query.Is_pod = int.Parse(Request.Params["Is_pod"] ?? "0");/*是否已下單採購*/
            //query.vendor_name = Request.Params["vendor_name"] ?? "";/*供應商名稱*/
            if (!string.IsNullOrEmpty(Request.Params["sale_status"]) && Request.Params["sale_status"] != "null")
            {
                query.sale_status = uint.Parse(Request.Params["sale_status"]);/*販售狀態*/
            }
            else 
            {
                query.sale_status = 100;
            }
            if (!string.IsNullOrEmpty(Request.Params["serchType"]) && Request.Params["serchType"]!="null")
            {
                int serchType = int.Parse(Request.Params["serchType"] ?? "0");
                if (!string.IsNullOrEmpty(Request.Params["serchName"] ?? ""))
                {
                    switch (serchType)
                    {
                        case 1:
                            query.vendor_id = uint.Parse(Request.Params["serchName"].Trim());
                            break;
                        case 2:
                            query.vendor_name_full = Request.Params["serchName"];
                            break;
                        case 3:
                            query.vendor_name = Request.Params["serchName"];
                            break;
                        case 4:
                            query.Erp_Id = Request.Params["serchName"];
                            break;
                        default:
                            break;
                    }
                }
            }
            p.ParameterType = "Food_Articles";
            Paralist = _paraMgr.GetAllKindType(p.ParameterType);
            for (int i = 0; i < Paralist.Count; i++)/*要禁用的食品錧和用品館的商品*/
            {
                if (!string.IsNullOrEmpty(Paralist[i].ParameterCode))
                {
                    query.category_ID_IN += Paralist[i].ParameterCode + ",";
                }
            }
           
            query.category_ID_IN = query.category_ID_IN.TrimEnd(',');
           
            query.IsPage = false;
            DataTable dtExcel = new DataTable();
            try
            {

                int totalCount = 0;
                dt = productItemMgr.GetSuggestPurchaseInfo(query, out totalCount);

                //添加兩列用於存儲"平均平均量"與"建議採購量"
                dt.Columns.Add("averageCount", typeof(string));
                dt.Columns.Add("suggestPurchaseCount", typeof(string));
                dtExcel.Columns.Add("供應商編號", typeof(String));
                dtExcel.Columns.Add("供應商簡稱", typeof(String));
                dtExcel.Columns.Add("商品編號", typeof(String));
                dtExcel.Columns.Add("商品細項編號", typeof(String));
                dtExcel.Columns.Add("商品ERP編號", typeof(String));
                dtExcel.Columns.Add("商品名稱", typeof(String));
                dtExcel.Columns.Add("規格", typeof(String));
                //dtExcel.Columns.Add("規格二", typeof(String));
                dtExcel.Columns.Add("出貨方式", typeof(String));
                dtExcel.Columns.Add("是否買斷", typeof(String));
                dtExcel.Columns.Add("庫存量", typeof(String));
                dtExcel.Columns.Add("後台庫存量", typeof(String));
                dtExcel.Columns.Add("安全存量", typeof(String));
                dtExcel.Columns.Add("購買總數", typeof(String));
                dtExcel.Columns.Add("週期平均量", typeof(String));
                dtExcel.Columns.Add("建議採購量", typeof(String));
                dtExcel.Columns.Add("最小採購量", typeof(String));
                dtExcel.Columns.Add("未驗收數量", typeof(String));
                dtExcel.Columns.Add("供應商採購天數", typeof(String));
                dtExcel.Columns.Add("補貨通知人數", typeof(String));
                dtExcel.Columns.Add("售價(單價)", typeof(String));
                dtExcel.Columns.Add("成本(單價)", typeof(String));
                dtExcel.Columns.Add("商品狀態", typeof(String));
                dtExcel.Columns.Add("販售狀態", typeof(String));
                dtExcel.Columns.Add("料位編號", typeof(String));
                dtExcel.Columns.Add("製造日期", typeof(String));
                dtExcel.Columns.Add("有效日期", typeof(String));
                dtExcel.Columns.Add("保存期限", typeof(String));
                
                dtExcel.Columns.Add("有效期控管", typeof(String));
              

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow newRow = dtExcel.NewRow();
                    newRow[0] = Convert.ToInt64(dt.Rows[i]["vendor_id"]);
                    newRow[1] = dt.Rows[i]["vendor_name_simple"];
                    newRow[2] = dt.Rows[i]["product_id"];
                    newRow[3] = dt.Rows[i]["item_id"];
                    newRow[4] = " "+dt.Rows[i]["erp_id"];
                    newRow[5] = dt.Rows[i]["product_name"];
                    newRow[6] = dt.Rows[i]["spec_title_1"];
                   // newRow[7] = dt.Rows[i]["spec_title_2"].ToString() + dt.Rows[i]["spec_id_2"].ToString();
                    newRow[7] = dt.Rows[i]["product_mode_name"];
                    newRow[8] = dt.Rows[i]["prepaid"];
                    if (!string.IsNullOrEmpty(dt.Rows[i]["prepaid"].ToString()))
                    {
                        int prepaid = Convert.ToInt32(dt.Rows[i]["prepaid"]);
                        if (prepaid == 0)
                            newRow[8] = "否";
                        if (prepaid == 1)
                            newRow[8] = "是";
                    }
                    newRow[9] = dt.Rows[i]["item_stock"];
                    newRow[10] = dt.Rows[i]["iinvd_stock"];
                    newRow[11] = dt.Rows[i]["item_alarm"];
                    newRow[12] = dt.Rows[i]["sum_total"];

                    if (string.IsNullOrEmpty(dt.Rows[i]["sum_total"].ToString()))
                    {
                        newRow[13] = 0;
                        newRow[14] = 0;
                    }
                    else
                    {
                        double sum_total = 0;
                        int safe_stock_amount = 0;
                        int item_stock = 0;
                        int item_alarm = 0;
                        int procurement_days = 0;
                        if (double.TryParse(dt.Rows[i]["sum_total"].ToString(), out sum_total))
                        {
                            sum_total = Convert.ToDouble(dt.Rows[i]["sum_total"]);
                        }
                        if (int.TryParse(dt.Rows[i]["safe_stock_amount"].ToString(), out safe_stock_amount))
                        {
                            safe_stock_amount = Convert.ToInt32(dt.Rows[i]["safe_stock_amount"]);
                        }
                        if (int.TryParse(dt.Rows[i]["item_stock"].ToString(), out item_stock))
                        {
                            item_stock = Convert.ToInt32(dt.Rows[i]["item_stock"]);
                        }
                        if (int.TryParse(dt.Rows[i]["item_alarm"].ToString(), out item_alarm))
                        {
                            item_alarm = Convert.ToInt32(dt.Rows[i]["item_alarm"]);
                        }
                        if (int.TryParse(dt.Rows[i]["procurement_days"].ToString(), out procurement_days))
                        {
                            procurement_days = Convert.ToInt32(dt.Rows[i]["procurement_days"]);
                        }

                        //週期平均量
                        //newRow[11] = sum_total / query.sumDays * query.periodDays;
                        string averageCount = (sum_total / query.sumDays * query.periodDays).ToString();
                        if (averageCount.Contains('.') && averageCount.Substring(averageCount.IndexOf('.'), averageCount.Length - averageCount.IndexOf('.')).Length > 5)
                        {
                            newRow[13] = averageCount.Substring(0, averageCount.IndexOf('.') + 5);
                        }
                        else
                        {
                            newRow[13] = averageCount;
                        }

                        //當前庫存量-供應商的採購天數*平均銷售數量(最小值為1))<=安全存量時,就需要採購
                        if (item_stock - procurement_days * sum_total / query.sumDays * query.periodDays <= item_alarm)
                        {
                            //建議採購量:供應商的進貨天數*採購調整系數*近3個月的平均每周銷售數量(最小值為1)
                            //double suggestPurchaseTemp = procurement_days * safe_stock_amount * (sum_total / query.sumDays) * query.periodDays;
                            double suggestPurchaseTemp = (procurement_days + safe_stock_amount) * (sum_total / query.sumDays) * query.periodDays + ((item_alarm - item_stock) > 0 ? (item_alarm - item_stock) : 0);
                            //if (suggestPurchaseTemp <= 1)   //最小值為1
                            //{
                            //    newRow[13] = 1;
                            //}
                            if (suggestPurchaseTemp <= int.Parse(dt.Rows[i]["min_purchase_amount"].ToString()))   //最小值為1
                            {
                                 newRow[14] = dt.Rows[i]["min_purchase_amount"];
                            }
                            else
                            {
                                int suggestPurchase = Convert.ToInt32(suggestPurchaseTemp);
                                if (suggestPurchase < suggestPurchaseTemp)
                                {
                                    newRow[14] = Convert.ToInt32(suggestPurchaseTemp) + 1;
                                }
                                else
                                {
                                    newRow[14] = Convert.ToInt32(suggestPurchaseTemp);
                                }
                            }
                        }
                        else
                        {
                            newRow[14] = "暫不需採購";
                        }
                    }

                    newRow[15] = dt.Rows[i]["min_purchase_amount"];//最小採購量
                    newRow[16] = dt.Rows[i]["ipo_qty"];
                    newRow[17] = dt.Rows[i]["procurement_days"];
                    newRow[18] = dt.Rows[i]["NoticeGoods"];
                    newRow[19] = dt.Rows[i]["item_money"];
                    newRow[20] = dt.Rows[i]["item_cost"];
                    newRow[21] = dt.Rows[i]["product_status_string"];
                    newRow[22] = dt.Rows[i]["sale_name"];
                   // newRow[22] = dt.Rows[i]["create_datetime"];

                    newRow[23] = dt.Rows[i]["loc_id"];
                    newRow[24] = dt.Rows[i]["made_date"];
                    newRow[25] = dt.Rows[i]["cde_dt"];
                    newRow[26] = dt.Rows[i]["cde_dt_incr"];

                    newRow[27] = dt.Rows[i]["pwy_dte_ctl"];
                    dtExcel.Rows.Add(newRow);
                }
                if (dtExcel.Rows.Count > 0)
                {
                    string fileName = "商品建議採購量_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                    MemoryStream ms = ExcelHelperXhf.ExportDT(dtExcel, "商品建議採購量_" + DateTime.Now.ToString("yyyyMMddHHmmss"));
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
                    Response.BinaryWrite(ms.ToArray());
                }
                else
                {
                    Response.Write("匯出數據不存在");
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
            }
        }
コード例 #5
0
ファイル: ProductItemDao.cs プロジェクト: lxh2014/gigade-net
        public List<ProductItemQuery> GetInventoryQueryList(ProductItemQuery query, out int totalCount)// by yachao1120j 2015-9-10 商品库存查询
        {
            StringBuilder str = new StringBuilder();
            StringBuilder strcont = new StringBuilder();
            totalCount = 0;

            try
            {
                str.AppendFormat("SELECT p.product_id,p.product_name,pi.item_id,CONCAT(p.spec_title_1,' ',ps1.spec_name) as Spec_Name_1 ");
                str.AppendFormat(" ,CONCAT(p.spec_title_2,' ',ps2.spec_name) as Spec_Name_2 ,v.vendor_id,v.vendor_name_full, vb.brand_id ");
                str.Append(" ,vb.brand_name,tp1.parameterName as product_status_string,p.product_status,tp2.parameterName as sale_status_string,pi.item_stock,p.ignore_stock  ");
                strcont.AppendFormat("  from product_item pi ");
                strcont.AppendFormat(" left JOIN product p on p.product_id=pi.product_id ");
                strcont.AppendFormat(" INNER JOIN vendor_brand vb on vb.brand_id=p.brand_id ");
                strcont.AppendFormat(" INNER JOIN vendor v on v.vendor_id=vb.vendor_id ");
                strcont.AppendFormat(" left JOIN product_spec ps1 on ps1.spec_id=pi.spec_id_1 ");
                strcont.AppendFormat(" left JOIN product_spec ps2 on  ps2.spec_id=pi.spec_id_2 ");
                //strcont.AppendFormat(" inner JOIN t_parametersrc tp on tp.parameterCode=p.product_status and  tp.parameterType='product_status' ");
                strcont.AppendFormat(" inner JOIN (SELECT parameterName,parameterCode from t_parametersrc where parameterType='product_status')  tp1 on tp1.parameterCode=p.product_status  ");
                strcont.AppendFormat(" INNER JOIN (SELECT parameterName,parameterCode from t_parametersrc where parameterType='sale_status' )  tp2 on tp2.parameterCode=p.sale_status ");
                strcont.AppendFormat(" where 1=1 ");
                if (!string.IsNullOrEmpty(query.product_id_OR_product_name))//商品名稱或者商品編號或商品細項編號
                {
                    int ID = 0;
                    if (int.TryParse(query.product_id_OR_product_name, out ID))
                    {
                        if (query.product_id_OR_product_name.Length == 6)
                        {
                            strcont.AppendFormat("and pi.item_id='{0}'", query.product_id_OR_product_name);
                        }
                        else
                        {
                            strcont.AppendFormat("and p.product_id='{0}'",query.product_id_OR_product_name);
                        }
                        //strcont.AppendFormat(" and ( p.product_id = '{0}' or pi.item_id = '{1}') ", query.product_id_OR_product_name, query.product_id_OR_product_name);
                    }
                    else
                    {
                        strcont.AppendFormat(" and p.product_name LIKE '%{0}%'", query.product_id_OR_product_name);
                    }
                }
                if (!string.IsNullOrEmpty(query.vendor_name_full_OR_vendor_id))//供應商名稱或者供應商編號
                {
                    int ID = 0;
                    if (int.TryParse(query.vendor_name_full_OR_vendor_id, out ID))
                    {
                        strcont.AppendFormat(" and  v.vendor_id = '{0}' ", query.vendor_name_full_OR_vendor_id);
                    }
                    else
                    {
                        strcont.AppendFormat(" and v.vendor_name_full LIKE '%{0}%'", query.vendor_name_full_OR_vendor_id);
                    }
                }

                if (!string.IsNullOrEmpty(query.brand_id_OR_brand_name))//品牌名稱或者品牌編號
                {
                    int ID = 0;
                    if (int.TryParse(query.brand_id_OR_brand_name, out ID))
                    {
                        strcont.AppendFormat(" and vb.brand_id = '{0}'", query.brand_id_OR_brand_name);
                    }
                    else
                    {
                        strcont.AppendFormat(" and vb.brand_name LIKE '%{0}%'", query.brand_id_OR_brand_name);
                    }
                }
                if (query.product_status != 10)//商品狀態  10 代表全部
                {
                    strcont.AppendFormat("and p.product_status = '{0}'", query.product_status);
                }
                if (query.sale_status != 100)//  商品販售狀態  100 代表全部
                {
                    strcont.AppendFormat("and p.sale_status = '{0}'", query.sale_status);
                }
                if (query.item_stock_start <= query.item_stock_end)//库存数量开始--库存数量结束   
                {
                    strcont.AppendFormat("  and pi.item_stock >='{0}' and pi.item_stock <='{1}'  ", query.item_stock_start, query.item_stock_end);
                }

                strcont.AppendFormat("and p.ignore_stock = '{0}'", query.ignore_stock);//庫存為0時是否還能販售
                str.Append(strcont);

                if (query.IsPage)
                {
                    StringBuilder strpage = new StringBuilder();//  
                    StringBuilder strcontpage = new StringBuilder();
                    strpage.AppendFormat(" SELECT count(pi.item_id) as totalCount  ");
                    strpage.Append(strcont);
                    string sql = strpage.ToString();
                    DataTable _dt = _access.getDataTable(sql);
                    if (_dt.Rows.Count > 0)
                    {
                        totalCount = Convert.ToInt32(_dt.Rows[0][0]);
                        str.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
                    }
                }
                return _access.getDataTableForObj<ProductItemQuery>(str.ToString());// 獲取查詢記錄

            }
            catch (Exception ex)
            {
                throw new Exception("ProductItemDao-->GetInventoryQueryList-->" + ex.Message);
            }

        }
コード例 #6
0
ファイル: ReadLogMgr.cs プロジェクト: lxh2014/gigade-net
        public StringBuilder GetStockMessageFromFile(string fileName, ref DataTable _dtSucess, ref DataTable _dtFail, ref DataTable _dtIgnore, ref DataTable _dtErrorTable)
        {
            StringBuilder sbErrContent = new StringBuilder();
            DataTable dt = null;
            try
            {
                dt = ExcelHelperXhf.ImportExcel2003toDt(fileName);
            }
            catch (Exception)
            {

                try
                {
                    dt = ExcelHelperXhf.ImportExcel2007toDt(fileName);
                }
                catch (Exception)
                {
                    DataRow rows = _dtErrorTable.NewRow();
                    rows[0] = fileName;
                    rows[1] = "這個格式的Excel格式未知,不能讀取";
                    _dtErrorTable.Rows.Add(rows);
                    sbErrContent.AppendLine(fileName + "這個格式的Excel格式未知,不能讀取");
                    //Console.WriteLine("這個格式的Excel格式未知,不能讀取");
                }
            }
            _productItemMgr = new ProductItemMgr(mySqlConnectionString);
            foreach (DataRow r in dt.Rows)
            {

                ProductItemQuery model = new ProductItemQuery();
                model.Erp_Id = r["品號"].ToString();
                model.item_stock = Convert.ToInt32(r["可用量"].ToString());
                //查詢atm數量
                int atmNumber = _productItemMgr.GetATMStock(model);/*ATM的數量*/
                int useNumber = Convert.ToInt32(model.item_stock) - atmNumber;
                //int olditem_stock = Convert.ToInt32(model.item_stock);
                model.item_stock = useNumber;
                try
                {
                    List<ProductItemQuery> ItemList = new List<ProductItemQuery>();
                    ItemList = _productItemMgr.GetProdItemByERp(model);
                    if (ItemList.Count > 0 && ItemList[0].product_id != 0)
                    {

                        if (atmNumber > 0)
                        {//扣除ATM的
                            #region MyRegion
                            if (model.item_stock == ItemList[0].item_stock)
                            {
                                DataRow rows = _dtSucess.NewRow();
                                rows[0] = ItemList[0].product_id;
                                rows[1] = ItemList[0].item_id;
                                rows[2] = model.Erp_Id;
                                rows[3] = ItemList[0].product_name;
                                rows[4] = ItemList[0].Spec_Name_1 + ItemList[0].Spec_Name_2;
                                rows[5] = string.Format(" {0} ", model.item_stock);
                                _dtSucess.Rows.Add(rows);
                                sbErrContent.AppendLine(string.Format("erp_id為{0}的商品庫存變為{1}", model.Erp_Id, model.item_stock));
                            }
                            else
                            {

                                if (_productItemMgr.UpdateStockAsErpId(model) > 0)
                                {
                                    DataRow rows = _dtSucess.NewRow();
                                    rows[0] = ItemList[0].product_id;
                                    rows[1] = ItemList[0].item_id;
                                    rows[2] = model.Erp_Id;
                                    rows[3] = ItemList[0].product_name;
                                    rows[4] = ItemList[0].Spec_Name_1 + ItemList[0].Spec_Name_2;
                                    rows[5] = string.Format(" {0}扣除ATM庫存為{1} ", model.item_stock + atmNumber, model.item_stock);
                                    _dtSucess.Rows.Add(rows);
                                    sbErrContent.AppendLine(string.Format("erp_id為{0}的商品扣除ATM庫存{1}", model.Erp_Id, atmNumber));
                                }
                                else
                                {
                                    DataRow rows = _dtFail.NewRow();
                                    rows[0] = ItemList[0].product_id;
                                    rows[1] = ItemList[0].item_id;
                                    rows[2] = model.Erp_Id;
                                    rows[3] = ItemList[0].product_name;
                                    rows[4] = ItemList[0].Spec_Name_1 + ItemList[0].Spec_Name_2;
                                    rows[5] = string.Format(" 由{0}更新為{1}失敗! ", ItemList[0].item_stock, model.item_stock);
                                    _dtFail.Rows.Add(rows);
                                    sbErrContent.AppendLine(string.Format("erp_id為{0}的更新失敗", model.Erp_Id));
                                }
                            }
                            #endregion
                        }
                        else 
                        {//不扣除ATM的,可能需要更新,可能不需要更新
                            #region MyRegion
                            
                           
                            if (ItemList[0].item_stock == model.item_stock)//不需要更新的
                            {
                                DataRow rows = _dtSucess.NewRow();
                                rows[0] = ItemList[0].product_id;
                                rows[1] = ItemList[0].item_id;
                                rows[2] = model.Erp_Id;
                                rows[3] = ItemList[0].product_name;
                                rows[4] = ItemList[0].Spec_Name_1 + ItemList[0].Spec_Name_2;
                                rows[5] = string.Format(" {0} ", model.item_stock);
                                _dtSucess.Rows.Add(rows);
                                sbErrContent.AppendLine(string.Format("erp_id為{0}的商品庫存變為{1}", model.Erp_Id, model.item_stock));
                            }
                            else //需要更新的
                            {
                                if (_productItemMgr.UpdateStockAsErpId(model) > 0)
                                {
                                    DataRow rows = _dtSucess.NewRow();
                                    rows[0] = ItemList[0].product_id;
                                    rows[1] = ItemList[0].item_id;
                                    rows[2] = model.Erp_Id;
                                    rows[3] = ItemList[0].product_name;
                                    rows[4] = ItemList[0].Spec_Name_1 + ItemList[0].Spec_Name_2;
                                    rows[5] = string.Format(" 由{0}更新為{1}成功! ", ItemList[0].item_stock, model.item_stock);
                                    _dtSucess.Rows.Add(rows);
                                    sbErrContent.AppendLine(string.Format("erp_id為{0}的商品庫存變為{1}", model.Erp_Id, model.item_stock));
                                }
                                else
                                {
                                    DataRow rows = _dtFail.NewRow();
                                    rows[0] = ItemList[0].product_id;
                                    rows[1] = ItemList[0].item_id;
                                    rows[2] = model.Erp_Id;
                                    rows[3] = ItemList[0].product_name;
                                    rows[4] = ItemList[0].Spec_Name_1 + ItemList[0].Spec_Name_2;
                                    rows[5] = string.Format(" 由{0}更新為{1}失敗! ", ItemList[0].item_stock, model.item_stock);
                                    _dtFail.Rows.Add(rows);
                                    sbErrContent.AppendLine(string.Format("erp_id為{0}的更新失敗", model.Erp_Id));
                                }
                            }
                            #endregion
                        }


                        //if (_productItemMgr.UpdateStockAsErpId(model) > 0)
                        //{//更新成功的
                        //    if (atmNumber > 0)
                        //    {//扣除ATM的
                        //        DataRow rows = _dtSucess.NewRow();
                        //        rows[0] = ItemList[0].product_id;
                        //        rows[1] = ItemList[0].item_id;
                        //        rows[2] = model.Erp_Id;
                        //        rows[3] = ItemList[0].product_name;
                        //        rows[4] = ItemList[0].Spec_Name_1 + ItemList[0].Spec_Name_2;
                        //        rows[5] = string.Format(" {0}扣除ATM庫存為{1} ", model.item_stock + atmNumber, model.item_stock); 
                        //        _dtSucess.Rows.Add(rows);
                        //        sbErrContent.AppendLine(string.Format("erp_id為{0}的商品扣除ATM庫存{1}", model.Erp_Id, atmNumber));
                        //    }
                        //    else 
                        //    {
                        //        DataRow rows = _dtSucess.NewRow();
                        //        rows[0] = ItemList[0].product_id;
                        //        rows[1] = ItemList[0].item_id;
                        //        rows[2] = model.Erp_Id;
                        //        rows[3] = ItemList[0].product_name;
                        //        rows[4] = ItemList[0].Spec_Name_1 + ItemList[0].Spec_Name_2;
                        //        rows[5] = string.Format(" 由{0}更新為{1}成功! ", ItemList[0].item_stock, model.item_stock); 
                        //        _dtSucess.Rows.Add(rows);
                        //        sbErrContent.AppendLine(string.Format("erp_id為{0}的商品庫存變為{1}", model.Erp_Id, model.item_stock));
                        //    }
                        //}
                        //else 
                        //{//更新失敗的
                        //    DataRow rows = _dtFail.NewRow();
                        //    rows[0] = ItemList[0].product_id;
                        //    rows[1] = ItemList[0].item_id;
                        //    rows[2] = model.Erp_Id;
                        //    rows[3] = ItemList[0].product_name;
                        //    rows[4] = ItemList[0].Spec_Name_1 + ItemList[0].Spec_Name_2;
                        //    rows[5] = string.Format(" 由{0}更新為{1}失敗! ", ItemList[0].item_stock, model.item_stock);
                        //    _dtFail.Rows.Add(rows);
                        //    sbErrContent.AppendLine(string.Format("erp_id為{0}的更新失敗", model.Erp_Id));
                        //}
                    }
                    else 
                    {
                        DataRow rows = _dtIgnore.NewRow();
                        rows[0] = model.Erp_Id;
                        _dtIgnore.Rows.Add(rows);
                        sbErrContent.AppendLine(string.Format("erp_id為{0}的跳過", model.Erp_Id));
                    }
                    
                }
                catch (Exception ex)
                {
                    throw new Exception("CheckOrderAmount-->GetStockMessageFromFile-->" + ex.Message);
                }
            }

            return sbErrContent;
        }
コード例 #7
0
ファイル: ProductItemMgr.cs プロジェクト: lxh2014/gigade-net
        public List<ProductItemQuery> GetWaitLiaoWeiList(ProductItemQuery query, out int totalCount)// 等待料位報表
        {
            try
            {
                List<ProductItemQuery> store = new List<ProductItemQuery>();
                store = _productItemDao.GetWaitLiaoWeiList(query, out totalCount);
                foreach (var item in store)
                {
                    item.product_createdate_string = CommonFunction.GetNetTime(item.product_createdate).ToString("yyyy-MM-dd HH:mm:ss");
                    item.product_start_string = CommonFunction.GetNetTime(item.product_start).ToString("yyyy-MM-dd HH:mm:ss");
                    item.product_spec = item.Spec_Name_1;
                    item.product_spec += string.IsNullOrEmpty(item.Spec_Name_1.Trim()) ? item.Spec_Name_2 : (string.IsNullOrEmpty(item.Spec_Name_2.Trim()) ? "" : " / " + item.Spec_Name_2);
                    //商品類型
                    if (item.combination == 1)
                    {
                        item.combination_string = "單一商品";
                    }
                    if (item.combination == 2)
                    {
                        item.combination_string = "固定組合";
                    }
                    if (item.combination == 3)
                    {
                        item.combination_string = "任選組合";
                    }
                    if (item.combination == 4)
                    {
                        item.combination_string = "群組搭配";
                    }

                    //出貨方式
                    if (item.product_mode == 1)
                    {
                        item.product_mode_string = "自出";
                    }
                    if (item.product_mode == 2)
                    {
                        item.product_mode_string = "寄倉";
                    }
                    if (item.product_mode == 3)
                    {
                        item.product_mode_string = "調度";
                    }
                    //商品狀態
                    if (item.product_status == 0)
                    {
                        item.product_status_string = "新建立商品";
                    }
                    if (item.product_status == 1)
                    {
                        item.product_status_string = "申請審核";
                    }
                    if (item.product_status == 2)
                    {
                        item.product_status_string = "審核通過";
                    }
                    if (item.product_status == 5)
                    {
                        item.product_status_string = "上架";
                    }
                    //溫層
                    if (item.delivery_freight_set == 1)
                    {
                        item.product_freight_set_string = "常溫";
                    }
                    if (item.delivery_freight_set == 2)
                    {
                        item.product_freight_set_string = "冷凍";
                    }
                    //採購單單號
                    if (!string.IsNullOrEmpty(item.po_id))
                    {
                        item.po_id = " " + item.po_id;
                    }             
                }

                return store;

            }
            catch (Exception ex)
            {
                throw new Exception("ProductItemMgr->GetWaitLiaoWeiList" + ex.Message);
            }
        }
コード例 #8
0
ファイル: ProductItemDao.cs プロジェクト: lxh2014/gigade-net
 /***
  * chaojie1124j 2015/08/26添加GetNoticeGoods方法,實現商品建議採購量的補貨通知人數。
  *補貨通知人數 
  */
 public Dictionary<int, int> GetNoticeGoods(ProductItemQuery query)
 {
     StringBuilder sb = new StringBuilder();
     Dictionary<int, int> NoticeGoods = new Dictionary<int, int>();
     string startTime = DateTime.Now.AddDays(-query.sumDays).ToString("yyyy-MM-dd 00:00:00");
     try
     {
         sb.AppendFormat(" select count(user_id) as Count,item_id from  arrival_notice  where create_time>='{0}' group by item_id ;", CommonFunction.GetPHPTime(startTime));
         DataTable _dtGoods = _dbAccess.getDataTable(sb.ToString());
         foreach (DataRow item in _dtGoods.Rows)
         {
             NoticeGoods.Add(Convert.ToInt32(item["item_id"]), Convert.ToInt32(item["Count"]));
         }
         return NoticeGoods;
     }
     catch (Exception ex)
     {
         throw new Exception("ProductItemDao-->GetNoticeGoods" + ex.Message, ex);
     }
 }
コード例 #9
0
ファイル: ProductItemMgr.cs プロジェクト: lxh2014/gigade-net
 public List<ProductItemQuery> GetProductItemByID(ProductItemQuery query)
 {
     try
     {
         return productItemDao.GetProductItemByID(query);
     }
     catch (Exception ex)
     {
         throw new Exception("ProductItemMgr-->GetProductItemByID" + ex.Message, ex);
     }
 }
コード例 #10
0
ファイル: ProductItemMgr.cs プロジェクト: lxh2014/gigade-net
        public List<ProductItemQuery> GetInventoryQueryList(ProductItemQuery query, out int totalCount)
        {
            try
            {
                List<ProductItemQuery> store = new List<ProductItemQuery>();
                store = _productItemDao.GetInventoryQueryList(query, out totalCount);
                foreach (var item in store)
                {
                    item.product_spec = item.Spec_Name_1;
                    item.product_spec += string.IsNullOrEmpty(item.Spec_Name_1) ? item.Spec_Name_2 : (string.IsNullOrEmpty(item.Spec_Name_2) ? "" : " / " + item.Spec_Name_2);
                    if (item.ignore_stock == 0)
                    {
                        item.ignore_stock_string = "否";
                    }
                    if (item.ignore_stock == 1)
                    {
                        item.ignore_stock_string = "是";
                    }
                }

                return store;

            }
            catch (Exception ex)
            {
                throw new Exception("ProductItemMgr->GetInventoryQueryList" + ex.Message);
            }
        }
コード例 #11
0
ファイル: ProductItemMgr.cs プロジェクト: lxh2014/gigade-net
 /// <summary>
 /// 獲取商品建議採購量信息
 /// </summary>
 /// <param name="query">查詢條件</param>
 /// <returns>商品建議採購列表</returns>
 public DataTable GetSuggestPurchaseInfo(ProductItemQuery query,out int totalCount)
 {
     try
     {
         return productItemDao.GetSuggestPurchaseInfo(query,out totalCount );
     }
     catch (Exception ex)
     {
         throw new Exception("ProductItemMgr-->GetSuggestPurchaseInfo-->" + ex.Message,ex);
     }
 }
コード例 #12
0
        public HttpResponseBase GetProductName()
        {
            ProductItemQuery pItem = new ProductItemQuery();
            if (!string.IsNullOrEmpty(Request.Params["item_id"]))
            {
                pItem.Item_Id = uint.Parse(Request.Params["item_id"]);
            }


            productItemMgr = new ProductItemMgr(mySqlConnectionString);
            string jsonStr = string.Empty;

            try
            {
                List<ProductItemQuery> ItemList = new List<ProductItemQuery>();
                ItemList = productItemMgr.GetProductItemByID(pItem);
                if (ItemList.Count > 0)
                {
                    jsonStr = "{success:true,msg:\"[" + ItemList[0].Product_Id + "]" + ItemList[0].Remark +" "+ ItemList[0].Spec_Name_1 + ItemList[0].Spec_Name_2 + "\",product_id:\"" + ItemList[0].Product_Id + "\"}";
                }
                else
                {
                    jsonStr = "{success:true,msg:\"" + 100 + "\"}";
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                jsonStr = "{success:false}";
            }
            this.Response.Clear();
            this.Response.Write(jsonStr);
            this.Response.End();
            return this.Response;
        }
コード例 #13
0
        /// <summary>
        /// 根據條件獲取需要建議採購的商品信息
        /// </summary>
        /// <returns>商品信息列表</returns>
        public HttpResponseBase GetSuggestPurchase()
        {
            string json = string.Empty;

            DataTable dt = new DataTable();
            ProductItemQuery query = new ProductItemQuery();
            query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
            query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
            query.stockScope = int.Parse(Request.Params["stockScope"] ?? "0");//所有庫存
            query.sumDays = int.Parse(Request.Params["sumDays"] ?? "90");   //總天數
            query.periodDays = int.Parse(Request.Params["periodDays"] ?? "7"); //周期天數
            //     perpaid: Ext.getCmp('perpaid').getValue().perpaidValue,/*是否已下單採購*/
            //Is_pod: Ext.getCmp('Is_pod').getValue().Is_podValue,/*是否 買斷*/
            //vendor_name:Ext.getCmp('vendor_name').getValue(),/*供應商名稱*/
            query.prepaid = int.Parse(Request.Params["perpaid"] ?? "-1");/*是否買斷*/
            query.Is_pod = int.Parse(Request.Params["Is_pod"] ?? "0");/*是否已下單採購*/
            query.sale_status = uint.Parse(Request.Params["sale_status"] ?? "100");/*販售狀態*/
           // query.vendor_name = Request.Params["vendor_name"] ?? "";/*供應商名稱*/
            try
            {
                if(!string.IsNullOrEmpty(Request.Params["serchType"] ))
                {
                    int serchType=int.Parse(Request.Params["serchType"]);
                    if (!string.IsNullOrEmpty(Request.Params["serchName"].Trim()))
                    {
                        switch (serchType)
                        {
                            case 1:
                                query.vendor_id = uint.Parse(Request.Params["serchName"].Trim());
                                break;
                            case 2:
                                query.vendor_name_full = Request.Params["serchName"].Trim();
                                break;
                            case 3:
                                query.vendor_name = Request.Params["serchName"].Trim();
                                break;
                            case 4:
                                query.Erp_Id = Request.Params["serchName"].Trim();
                                break;
                            default:
                                break;
                        }
                    }
                }
                _paraMgr = new ParameterMgr(mySqlConnectionString);
                Parametersrc p = new Parametersrc();
                List<Parametersrc> list = new List<Parametersrc>();
                p.ParameterType = "Food_Articles";
                list = _paraMgr.GetAllKindType(p.ParameterType);
                for (int i = 0; i < list.Count; i++)/*要禁用的食品錧和用品館的商品*/
                {
                    if (!string.IsNullOrEmpty(list[i].ParameterCode))
                    {
                        query.category_ID_IN += list[i].ParameterCode + ",";
                    }
                }
                query.category_ID_IN = query.category_ID_IN.TrimEnd(',');
                int totalCount = 0;
                dt = productItemMgr.GetSuggestPurchaseInfo(query, out totalCount);
                if (dt.Rows.Count > 0)
                {
                    //添加兩列用於存儲"平均平均量"與"建議採購量"
                    dt.Columns.Add("averageCount", typeof(string));
                    dt.Columns.Add("suggestPurchaseCount", typeof(string));
                    //循環每一行數據計算"平均平均量"與"建議採購量"
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        double sum_total = 0;
                        int safe_stock_amount = 0;
                        int item_stock = 0;
                        int item_alarm = 0;
                        int procurement_days = 0;
                        if (double.TryParse(dt.Rows[i]["sum_total"].ToString(), out sum_total))
                        {
                            sum_total = Convert.ToDouble(dt.Rows[i]["sum_total"]);
                        }
                        if (int.TryParse(dt.Rows[i]["safe_stock_amount"].ToString(), out safe_stock_amount))
                        {
                            safe_stock_amount = Convert.ToInt32(dt.Rows[i]["safe_stock_amount"]);
                        }
                        if (int.TryParse(dt.Rows[i]["item_stock"].ToString(), out item_stock))
                        {
                            item_stock = Convert.ToInt32(dt.Rows[i]["item_stock"]);
                        }
                        if (int.TryParse(dt.Rows[i]["item_alarm"].ToString(), out item_alarm))
                        {
                            item_alarm = Convert.ToInt32(dt.Rows[i]["item_alarm"]);
                        }
                        if (int.TryParse(dt.Rows[i]["procurement_days"].ToString(), out procurement_days))
                        {
                            procurement_days = Convert.ToInt32(dt.Rows[i]["procurement_days"]);
                        }

                        if (string.IsNullOrEmpty(dt.Rows[i]["sum_total"].ToString()))
                        {
                            dt.Rows[i]["averageCount"] = 0;
                            dt.Rows[i]["suggestPurchaseCount"] = 0;
                        }
                        else
                        {
                            //週期平均量
                            string averageCount = (sum_total / query.sumDays * query.periodDays).ToString();
                            if (averageCount.Contains('.'))
                            {
                                if (averageCount.Substring(averageCount.IndexOf('.'), averageCount.Length - averageCount.IndexOf('.')).Length > 5)
                                {
                                    dt.Rows[i]["averageCount"] = averageCount.Substring(0, averageCount.IndexOf('.') + 5);
                                }
                                else
                                {
                                    dt.Rows[i]["averageCount"] = averageCount;
                                }
                            }
                            else
                            {
                                dt.Rows[i]["averageCount"] = averageCount;
                            }

                            //當前庫存量-供應商的採購天數*平均銷售數量(最小值為1))<=安全存量時,就需要採購
                            if (item_stock - procurement_days * sum_total / query.sumDays * query.periodDays <= item_alarm)
                            {
                                //建議採購量:供應商的進貨天數*採購調整系數*近3個月的平均每周銷售數量(最小值為1)
                                //(供應商採購天數+安全係數)*週期平均量+(安全存量-庫存)
                               //double suggestPurchaseTemp = procurement_days * safe_stock_amount * (sum_total / query.sumDays) * query.periodDays;
                                double suggestPurchaseTemp = (procurement_days + safe_stock_amount) * (sum_total / query.sumDays) * query.periodDays + ((item_alarm - item_stock) > 0 ? (item_alarm - item_stock): 0);
                                if (suggestPurchaseTemp <= int.Parse(dt.Rows[i]["min_purchase_amount"].ToString()))   //最小值為1
                                {
                                    dt.Rows[i]["suggestPurchaseCount"] = dt.Rows[i]["min_purchase_amount"];
                                }
                                else
                                {
                                    if (suggestPurchaseTemp.ToString().Contains('.'))
                                    {
                                        int suggestPurchase = Convert.ToInt32(suggestPurchaseTemp);
                                        if (suggestPurchase < suggestPurchaseTemp)
                                        {
                                            dt.Rows[i]["suggestPurchaseCount"] = Convert.ToInt32(suggestPurchaseTemp) + 1;
                                        }
                                        else 
                                        {
                                            dt.Rows[i]["suggestPurchaseCount"] = Convert.ToInt32(suggestPurchaseTemp);
                                        }
                                    }
                                    else
                                    {
                                        dt.Rows[i]["suggestPurchaseCount"] = Convert.ToInt32(suggestPurchaseTemp);
                                    }
                                }
                            }
                            else
                            {
                                dt.Rows[i]["suggestPurchaseCount"] = "暫不需採購";
                            }
                        }
                    }
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式     
                timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
                json = "{success:true,'msg':'user',totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dt, Formatting.Indented, timeConverter) + "}";//返回json數據
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,totalCount:0,data:[]}";
            }

            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
コード例 #14
0
ファイル: ProductItemDao.cs プロジェクト: lxh2014/gigade-net
 /// <summary>
 /// 更改商品庫存,通過ERP編號
 /// chaojie1124j add by 2015/12/16 10:32am
 /// </summary>
 /// <param name="query"></param>
 /// <returns></returns>
 public int UpdateStockAsErpId(ProductItemQuery query) 
 {
     StringBuilder str = new StringBuilder();
     try
     {
         str.AppendFormat(" update product_item set item_stock={0} where erp_id={1} ", query.item_stock, query.Erp_Id);
         return _access.execCommand(str.ToString());
     }
     catch (Exception ex)
     {
         throw new Exception("ProductItemDao-->GetProdItemByERp-->" + ex.Message + str.ToString() ,ex);
     }
 }
コード例 #15
0
ファイル: ProductItemMgr.cs プロジェクト: lxh2014/gigade-net
 public int GetATMStock(ProductItemQuery query) 
 {
     try
     {
         return _productItemDao.GetATMStock(query);
     }
     catch (Exception ex)
     {
        throw new Exception("ProductItemMgr->GetATMStock" + ex.Message);
     }
 }
コード例 #16
0
ファイル: ProductItemDao.cs プロジェクト: lxh2014/gigade-net
        /// <summary>
        /// 獲取商品建議採購量信息
        /// </summary>
        /// <param name="query">查詢條件</param>
        /// <returns>商品建議採購列表</returns>
        ///
        public DataTable GetSuggestPurchaseInfo(ProductItemQuery query, out int TotalCount)
        {
            DataTable _dt = new DataTable();
            StringBuilder sbSqlColumn = new StringBuilder();
            StringBuilder sbSqlTable = new StringBuilder();
            StringBuilder sbSqlCondition = new StringBuilder();
            StringBuilder sb = new StringBuilder();

            TotalCount = 0;
            string sumdate = DateTime.Now.AddDays(-query.sumDays).ToString("yyyy-MM-dd 00:00:00"); ;
            /*清理掉記錄表中已經補充貨物的記錄*/
            sb.Append("set sql_safe_updates = 0; delete from item_ipo_create_log where item_id in(select pi.item_id from (SELECT od.item_id,sum(od.buy_num*od.parent_num) as sum_total from order_master om LEFT JOIN order_slave os USING(order_id)LEFT JOIN order_detail od USING(slave_id)  ");
            sb.AppendFormat(" where FROM_UNIXTIME( om.order_createdate)>='{0}'  GROUP BY od.item_id) sum_biao ", sumdate);
            sb.Append(" left join  product_item pi on sum_biao.item_id=pi.item_id left join product p on p.product_id=pi.product_id  LEFT JOIN vendor_brand vb on vb.brand_id=p.brand_id LEFT JOIN vendor v on v.vendor_id=vb.vendor_id ");
            sb.AppendFormat(" where pi.item_stock-(v.procurement_days* sum_biao.sum_total/'{0}'*'{1}')>pi.item_alarm); set sql_safe_updates = 1; ", query.sumDays, query.periodDays);
            _dbAccess.execCommand(sb.ToString());

            #region MyRegion
            // sbSqlColumn.Append("  SELECT v.vendor_id,p.spec_title_2,p.spec_title_1,p.product_id,p.product_name,(select max(create_time) from item_ipo_create_log where item_id=pi.item_id ) as create_datetime, ");
            //sbSqlColumn.Append("  p.sale_status ,'' as sale_name,p.product_mode ,'' as product_mode_name, p.prepaid,pi.erp_id,pi.item_id,pi.item_stock, pi.item_alarm,p.safe_stock_amount,ip.item_money,ip.item_cost, ");
            //sbSqlColumn.Append(" sum( case item_mode when 0 then od.buy_num when  2 then od.buy_num*od.parent_num end ) as sum_total,subTtotal.iinvd_stock, p.min_purchase_amount, ");
            //sbSqlColumn.Append(" v.vendor_name_simple,v.procurement_days,p.product_status,'' as product_status_string,pi.spec_id_1 ,pi.spec_id_2,''as NoticeGoods ");
            //sbSqlTable.Append(" from product_item pi  ");
            //sbSqlTable.Append(" INNER JOIN product p on p.product_id=pi.product_id ");
            //sbSqlTable.Append(" JOIN order_detail od on od.item_id=pi.item_id and od.item_mode in (0,2) ");
            //sbSqlTable.Append(" INNER JOIN order_slave os on os.slave_id=od.slave_id ");
            //sbSqlTable.Append(" INNER JOIN order_master om on om.order_id=os.order_id ");
            //sbSqlTable.Append(" INNER JOIN vendor_brand vb on vb.brand_id=p.brand_id  ");
            //sbSqlTable.Append(" INNER JOIN vendor v on v.vendor_id=vb.vendor_id  ");
            //sbSqlTable.Append(" INNER JOIN item_price ip on ip.item_id=pi.item_id  ");
            //sbSqlTable.Append(" left join (select item_id,sum(prod_qty) as iinvd_stock  from iinvd where ista_id='A' GROUP BY item_id ) as subTtotal on subTtotal.item_id=pi.item_id  ");
            //sbSqlTable.Append(" LEFT JOIN item_ipo_create_log iicl on iicl.item_id=pi.item_id   ");
            //sbSqlTable.Append(" where 1=1 ");
            //sbSqlCondition.AppendFormat(" and p.product_id>10000  and ((p.prepaid=1) or (p.prepaid=0 and p.product_mode=2)) and FROM_UNIXTIME( om.order_createdate)>='{0}'  ", sumdate);
            //sbSqlCondition.Append(" and(  p.product_status=5 or( p.product_status <>5 and p.product_id in  ");
            //sbSqlCondition.Append(" (SELECT pc.product_id from product pc INNER JOIN product_combo pcm on pcm.child_id=pc.product_id  INNER JOIN product pm on pm.product_id=pcm.parent_id where pm.product_status =5)))  "); 
            #endregion

            sbSqlColumn.Append("select vendor_id,CONCAT_WS(':',spec_title_2,ps2.spec_name) as spec_title_2 ,CONCAT_WS(':',spec_title_1,ps1.spec_name) as spec_title_1,''as loc_id,'' as cde_dt,''as made_date,'' as pwy_dte_ctl,''as cde_dt_incr,v_product_onsale.product_id,v_product_onsale.product_name,(select max(create_time) from item_ipo_create_log where item_id=v_product_onsale.item_id ) as create_datetime, sale_status ,'' as sale_name,product_mode ,'' as product_mode_name, prepaid,erp_id,v_product_onsale.item_id,item_stock, item_alarm,safe_stock_amount,  ");
            sbSqlColumn.Append(" '' as item_money,'' as item_cost,sum_biao.sum_total, subTtotal.iinvd_stock,v_product_onsale.product_start,v_product_onsale.product_end, min_purchase_amount,vendor_name_simple,vendor_name_full, procurement_days,product_status,'' as product_status_string, ");
            sbSqlColumn.Append(" spec_id_1 ,spec_id_2,''as NoticeGoods,ipod.ipo_qty ");



            sbSqlTable.Append(" from v_product_onsale left join (SELECT  od.item_id, sum( case dt1.item_mode when 0 then dt1.buy_num when  2 then dt1.buy_num*dt1.parent_num end ) as sum_total from order_master om ");
            sbSqlTable.Append("INNER JOIN order_slave os USING(order_id)INNER JOIN order_detail od USING(slave_id)  ");
            sbSqlTable.AppendFormat(" left join order_detail dt1 on dt1.detail_id=od.detail_id and dt1.detail_status=4 where FROM_UNIXTIME( om.order_createdate)>='{0}' and od.item_mode in (0,2) GROUP BY od.item_id) sum_biao ", sumdate);
            sbSqlTable.Append(" on v_product_onsale.item_id=sum_biao.item_id ");
           /* sbSqlTable.Append(" from (SELECT  od.item_id,sum( case dt1.item_mode when 0 then dt1.buy_num when  2 then dt1.buy_num*dt1.parent_num end ) as sum_total from order_master om INNER JOIN order_slave os USING(order_id)INNER JOIN order_detail od USING(slave_id)  ");
            sbSqlTable.AppendFormat(" left join order_detail dt1 on dt1.detail_id=od.detail_id and dt1.detail_status=4 where FROM_UNIXTIME( om.order_createdate)>='{0}' and od.item_mode in (0,2) GROUP BY od.item_id) sum_biao ", sumdate);
            sbSqlTable.Append(" INNER JOIN  v_product_onsale  on v_product_onsale.item_id=sum_biao.item_id ");*/
            sbSqlTable.Append(" left join (select item_id,sum(prod_qty) as iinvd_stock  from iinvd where ista_id='A' GROUP BY item_id ) as subTtotal on subTtotal.item_id=v_product_onsale.item_id ");
            sbSqlTable.Append(" LEFT JOIN  (select sum(qty_ord)as ipo_qty,prod_id from ipod where plst_id='O' GROUP BY prod_id) as ipod on ipod.prod_id=v_product_onsale.item_id ");
            sbSqlTable.Append(" LEFT JOIN item_ipo_create_log iicl on iicl.item_id=v_product_onsale.item_id ");
           // sbSqlTable.Append(" INNER join price_master pm on pm.product_id=v_product_onsale.product_id and pm.site_id=1 ");//and ((prepaid=1) or (prepaid=0 and product_mode=2))
            
            sbSqlTable.Append(" left join product_spec ps1 on ps1.spec_id=v_product_onsale.spec_id_1 ");
            sbSqlTable.Append(" left join product_spec ps2 on ps2.spec_id=v_product_onsale.spec_id_2 ");
            sbSqlCondition.Append("  where 1=1 and ((prepaid=1) or (prepaid=0 and product_mode=2)) ");


            if (query.prepaid != -1)
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.prepaid='{0}' ", query.prepaid);
            }
            if (!string.IsNullOrEmpty(query.category_ID_IN))
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.product_id NOT in(select product_id from  product_category_set where category_id in({0}))", query.category_ID_IN);
            }
            if (query.vendor_id != 0)
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.vendor_id ='{0}' ", query.vendor_id);
            }
            if (!string.IsNullOrEmpty(query.vendor_name_full))
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.vendor_name_full like '%{0}%'  ", query.vendor_name_full);
            }
            if (!string.IsNullOrEmpty(query.vendor_name))
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.vendor_name_simple like '%{0}%' ", query.vendor_name);
            }
            if (!string.IsNullOrEmpty(query.Erp_Id))
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.erp_id = '{0}' ", query.Erp_Id);
            }
            if (query.sale_status!=100)
            {
                sbSqlCondition.AppendFormat(" and v_product_onsale.sale_status = '{0}' ", query.sale_status);
            }
            //if (!string.IsNullOrEmpty(query.vendor_name))
            //{
            //    sbSqlCondition.AppendFormat(" and (v.vendor_name_full like '%{0}%' or v.vendor_name_simple like '%{0}%') ", query.vendor_name);
            //}
            switch (query.Is_pod)
            {
                case 0:
                    //不管下單採購還是未下單採購
                    break;
                case 1://下單採購的,時間不為空
                    sbSqlCondition.Append(" and iicl.create_time IS NOT NULL ");
                    break;
                case 2:
                    //未下單採購的時間為空
                    sbSqlCondition.Append(" and iicl.create_time IS NULL ");
                    break;
                default:
                    break;
            }

            switch (query.stockScope)
            {
                case 0://所有庫存,不加條件的
                    //當(庫存數量-安全存量)<(供應商的進貨天數*近3個月的平均每周銷售數量(最小值為1))時,就需要採購
                  
                    break;
                case 1:
                    sbSqlCondition.Append(" and v_product_onsale.item_stock<=0 ");//庫存數量在0或者0以下
                   
                    break;
                //case 2:
                //    sbSqlCondition.Append(" and (pi.item_stock<=5 and pi.item_stock>0) ");//庫存數量在0到5之間
                //    break;
                //case 3:
                //    sbSqlCondition.Append(" and (pi.item_stock<=10 and pi.item_stock>5) ");//庫存數量在5到10之間
                //    break;
                case 2:
                    //sbSqlCondition.Append(" and (pi.item_stock<pi.item_alarm) ");//庫存數量小於安全存量
                    //當前庫存量-供應商的採購天數*平均銷售數量(最小值為1))<=安全存量時,就需要採購

                    sbSqlCondition.AppendFormat(" and v_product_onsale.item_stock-(v_product_onsale.procurement_days* IFNULL(sum_total,0)/'{0}'*'{1}')<=v_product_onsale.item_alarm", query.sumDays, query.periodDays);
                    break;
                default:
                    break;
            }
            sbSqlCondition.AppendFormat(" order by v_product_onsale.item_id desc ");
            try
            {

                if (query.IsPage)//
                {
                    DataTable dt = _dbAccess.getDataTable("select count(v_product_onsale.item_id) as totalCount  " + sbSqlTable.ToString() + sbSqlCondition.ToString());
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        TotalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
                    }

                    sbSqlCondition.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
                }
                Dictionary<int, int>NoticeGoods= GetNoticeGoods(query);
                IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
                IPriceMasterImplDao _priceMasterDao=new PriceMasterDao(connStr);
                IProductSpecImplDao _specDao = new ProductSpecDao(connStr);
                List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_mode", "sale_status");
                DataTable dtResult = _dbAccess.getDataTable(sbSqlColumn.ToString() + sbSqlTable.ToString() + sbSqlCondition.ToString());
                List<Parametersrc> parameterStatus = _parameterDao.QueryParametersrcByTypes("product_status");
                List<PriceMaster> pmster=new List<PriceMaster>();
                DataTable _dtloc=new DataTable();
                //select iin.cde_dt,iin.made_date,pe.pwy_dte_ctl,cde_dt_incr from iinvd iin left join product_ext pe on pe.item_id=iin.item_id
//where 1=1 and iin.ista_id='A' order by cde_dt asc  
                foreach (DataRow dr in dtResult.Rows)
                {
                    if (string.IsNullOrEmpty(dr["ipo_qty"].ToString()))
                    {
                        dr["ipo_qty"] = 0; 
                    }

                    if (string.IsNullOrEmpty(dr["iinvd_stock"].ToString()))
                    {
                        dr["iinvd_stock"] = 0;
                    }
                    if (string.IsNullOrEmpty(dr["sum_total"].ToString()))
                    {
                        dr["sum_total"] = 0;
                    }
                    _dtloc = GettSuggestPurchaseIloc(dr["item_id"].ToString());
                    if (string.IsNullOrEmpty(_dtloc.Rows[0]["loc_id"].ToString()))//沒有主料位
                    {
                        if (dr["product_mode"].ToString() == "2")
                        {
                            dr["loc_id"] = "YY999999";
                        }
                        if (dr["product_mode"].ToString() == "3")
                        {
                            dr["loc_id"] = "ZZ999999";
                        }
                    }else
                    {
                        dr["loc_id"] = _dtloc.Rows[0]["loc_id"];
                    }
                    if (string.IsNullOrEmpty(_dtloc.Rows[0]["cde_dt"].ToString()))//沒有效期控管
                    {
                        dr["cde_dt"] = " ";
                        dr["made_date"] = " ";
                    }
                    else 
                    {
                        dr["cde_dt"] = _dtloc.Rows[0]["cde_dt"];
                        dr["made_date"] = _dtloc.Rows[0]["made_date"];
                    }
                    dr["pwy_dte_ctl"] = _dtloc.Rows[0]["pwy_dte_ctl"];
                    dr["cde_dt_incr"] = _dtloc.Rows[0]["cde_dt_incr"];

                    //計算商品的單價和商品的成本
                    pmster= _priceMasterDao.GetPriceMasterInfoByID2(dr["product_id"].ToString());
                    if (pmster.Count > 0)
                    {
                        dr["item_money"] = pmster[pmster.Count - 1].price;
                        dr["item_cost"] = pmster[pmster.Count - 1].cost;
                    }
                    else 
                    {
                        dr["item_money"] = 0;
                        dr["item_cost"] = 0;

                    }

                    dr["NoticeGoods"] = 0;
                    if (NoticeGoods.Keys.Contains(Convert.ToInt32(dr["item_id"])))
                    {
                        dr["NoticeGoods"] = NoticeGoods[Convert.ToInt32(dr["item_id"])];
                    }
                    var alist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == dr["product_mode"].ToString());
                    var dlist = parameterList.Find(m => m.ParameterType == "sale_status" && m.ParameterCode == dr["sale_status"].ToString());
                    var slist = parameterStatus.Find(m => m.ParameterType == "product_status" && m.ParameterCode == dr["product_status"].ToString());
                    if (alist != null)
                    {
                        dr["product_mode_name"] = alist.parameterName;
                    }
                    if (dlist != null)
                    {
                        dr["sale_name"] = dlist.parameterName;
                    }
                    if (slist != null)
                    {
                        dr["product_status_string"] = slist.parameterName;
                    }

                    //ProductSpec spec1 = _specDao.query(Convert.ToInt32(dr["spec_id_1"].ToString()));
                    //ProductSpec spec2 = _specDao.query(Convert.ToInt32(dr["spec_id_2"].ToString()));
                    //if (spec1 != null)
                    //{
                    //    dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString())?"":dr["spec_title_1"]+":"+spec1.spec_name;
                    //}
                    //if (spec2 != null)
                    //{
                    //    dr["spec_title_2"] = string.IsNullOrEmpty(dr["spec_title_2"].ToString()) ? "" : dr["spec_title_2"] +":"+ spec2.spec_name;
                    //}
                    dr["spec_title_1"] = string.IsNullOrEmpty(dr["spec_title_1"].ToString()) ? dr["spec_title_2"] : dr["spec_title_1"].ToString() + "  " + dr["spec_title_2"];
                }
                return dtResult;
            }
            catch (Exception ex)
            {
                throw new Exception("ProductItemDao-->GetSuggestPurchaseInfo-->" + ex.Message + sbSqlColumn.Append(sbSqlTable).Append(sbSqlCondition).ToString(), ex);
            }
        }
コード例 #17
0
ファイル: ProductItemMgr.cs プロジェクト: lxh2014/gigade-net
 public List<ProductItemQuery> GetProdItemByERp(ProductItemQuery query)
 {
     try
     {
         return _productItemDao.GetProdItemByERp(query);
     }
     catch (Exception ex)
     {
         throw new Exception("ProductItemMgr->GetProdItemByERp" + ex.Message);
     }
    
 }
コード例 #18
0
ファイル: ProductItemDao.cs プロジェクト: lxh2014/gigade-net
        public List<ProductItemQuery> GetProductItemByID(ProductItemQuery query)
        {
            StringBuilder sb = new StringBuilder();
            try
            {
                sb.Append("SELECT p.product_id,p.product_name as Remark,CONCAT(p.spec_title_1,' ',ps1.spec_name) as Spec_Name_1,CONCAT(p.spec_title_2,' ',ps2.spec_name) as Spec_Name_2 ");
                sb.Append(" from product_item pi left join product p on p.product_id =pi.product_id left join product_spec ps1 on ps1.spec_id=pi.spec_id_1 left join product_spec ps2 on ps2.spec_id=pi.spec_id_2");
                sb.AppendFormat(" where 1=1 ");
                if (query.Item_Id != 0)
                {
                    sb.AppendFormat(" and pi.item_id ='{0}'", query.Item_Id);
                }
                return _dbAccess.getDataTableForObj<ProductItemQuery>(sb.ToString());

            }
            catch (Exception ex)
            {
                throw new Exception("ProductItemDao-->GetProductItemByID" + ex.Message, ex);
            }
        }
コード例 #19
0
ファイル: ProductItemMgr.cs プロジェクト: lxh2014/gigade-net
 public int UpdateStockAsErpId(ProductItemQuery query)
 {
     try
     {
         return _productItemDao.UpdateStockAsErpId(query);
     }
     catch (Exception ex)
     {
         throw new Exception("ProductItemMgr->UpdateStockAsErpId" + ex.Message);
     }
 
 }
コード例 #20
0
ファイル: ProductItemDao.cs プロジェクト: lxh2014/gigade-net
        public List<ProductItemQuery> GetWaitLiaoWeiList(ProductItemQuery query, out int totalCount)// by yachao1120j 2015-10-20 等待料位報表
        {
            StringBuilder str = new StringBuilder();
            StringBuilder sqlCount = new StringBuilder();
            StringBuilder strcont = new StringBuilder();
            totalCount = 0;

            try
            {
                sqlCount.AppendFormat("SELECT count(pi.item_id) as totalCount ");
                str.AppendFormat("select pi.item_id,p.product_createdate,p.product_name,CONCAT(p.spec_title_1,' ',ps1.spec_name) as Spec_Name_1,CONCAT(p.spec_title_2,'',ps2.spec_name) as Spec_Name_2 ,p.combination,p.product_status,p.product_mode,dfsm.delivery_freight_set,p.product_start,tp2.parameterName as product_fenlei_dalei,tp1.parameterName as  product_fenlei_xiaolei,i.po_id  ");
                strcont.AppendFormat(" from  product_item pi ");
                strcont.AppendFormat(" inner join product p on p.product_id =pi.product_id ");
                strcont.AppendFormat(" inner join delivery_freight_set_mapping dfsm on dfsm.product_freight_set=p.product_freight_set ");
                strcont.AppendFormat(" LEFT JOIN ipod i on i.prod_id=pi.item_id ");
                strcont.AppendFormat(" INNER JOIN v_product_item_noloc v on v.item_id=pi.item_id ");
                strcont.AppendFormat(" left JOIN product_spec ps1 on ps1.spec_id=pi.spec_id_1 ");
                strcont.AppendFormat(" left JOIN product_spec ps2 on  ps2.spec_id=pi.spec_id_2 ");
                strcont.AppendFormat(" inner JOIN t_parametersrc  tp1 on tp1.parameterCode=p.cate_id  and tp1.parameterType='product_cate'  ");
                strcont.AppendFormat(" inner JOIN  t_parametersrc  tp2 on tp2.parameterCode=tp1.topValue  and tp2.parameterType='product_cate' ");
                strcont.AppendFormat(" where 1=1  and p.product_id>10000  ");

                if (query.product_mode != 100)//  出貨方式  100 代表全部
                {
                    strcont.AppendFormat(" and p.product_mode = '{0}' ", query.product_mode);
                }
                if (query.product_freight_set != 100)//溫層  100 代表全部
                {
                    strcont.AppendFormat(" and dfsm.delivery_freight_set = '{0}' ", query.product_freight_set);
                }
                strcont.AppendFormat("and p.product_status in (0,1,2,5) ");
                strcont.AppendFormat("  and p.product_createdate >='{0}' and p.product_createdate  <='{1}'  ", (query.start_time), (query.end_time));
                str.Append(strcont);
                if (query.IsPage)
                {
                    DataTable _dt = _access.getDataTable(sqlCount.ToString() + strcont.ToString());
                    if (_dt.Rows.Count > 0)
                    {
                        totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
                        str.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
                    }
                }
                return _access.getDataTableForObj<ProductItemQuery>(str.ToString());// 獲取查詢記錄

            }
            catch (Exception ex)
            {
                throw new Exception("ProductItemDao-->GetWaitLiaoWeiList-->" + ex.Message);
            }

        }
コード例 #21
0
        // 等待料位報表
        public HttpResponseBase GetWaitLiaoWeiList()// createTime 2015/10/19 by yachao1120j
        {
            string json = string.Empty;
            int totalcount = 0;
            ProductItemQuery query = new ProductItemQuery();
            query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
            query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
            productitemMgr = new ProductItemMgr(mySqlConnectionString);

            if (!string.IsNullOrEmpty(Request.Params["product_mode"]))
            {
                query.product_mode = Convert.ToInt32(Request.Params["product_mode"]);//出貨方式
            }
            if (!string.IsNullOrEmpty(Request.Params["freight"]))
            {
                query.product_freight_set = Convert.ToUInt32(Request.Params["freight"]);//溫層
            }
            //if (!string.IsNullOrEmpty(Request.Params["product_status"]))
            //{
            //    query.product_status = Convert.ToUInt32(Request.Params["product_status"]);//商品状态
            //}
            if (!string.IsNullOrEmpty(Request.Params["start_time"]))//開始時間
            {
                //query.start_time = Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00");
                query.start_time = (int)CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd HH:mm:ss"));
            }
            if (!string.IsNullOrEmpty(Request.Params["end_time"]))//結束時間
            {
                //query.end_time = Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59");
                query.end_time = (int)CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd HH:mm:ss"));
            }
            List<ProductItemQuery> list = productitemMgr.GetWaitLiaoWeiList(query, out totalcount);
            IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
            timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
            json = "{success:true,totalCount:" + totalcount + ",data:" + JsonConvert.SerializeObject(list, Formatting.Indented, timeConverter) + "}";
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return Response;

        }
コード例 #22
0
        //根據upcid獲取item_id
        public HttpResponseBase GetItemidByUpcid()
        {
            string json = string.Empty;
            _IiupcMgr = new IupcMgr(mySqlConnectionString);
            _iproductitemMgr = new ProductItemMgr(mySqlConnectionString);
            Iupc m = new Iupc();
            DataTable dt = new DataTable();
            bool isUpc = true;
            try
            {
                //獲取條碼
                if (!string.IsNullOrEmpty(Request.Params["upc_id"]))
                {
                    m.upc_id = Request.Params["upc_id"].ToString().Trim();
                    //6位整數時先判斷輸入的是否為item_id
                    Regex reg = new Regex("^\\d{6}$");
                    if (reg.IsMatch(m.upc_id))
                    {
                        ProductItemQuery query = new ProductItemQuery();
                        query.Item_Id = Convert.ToUInt32(m.upc_id);
                        List<ProductItemQuery> store = _iproductitemMgr.GetProductItemByID(query);
                        if (store.Count == 0)
                        {
                            isUpc = true;
                        }
                        else if (store.Count > 1)
                        {
                            isUpc = true;
                        }
                        else
                        {
                            json = "{success:true,msg:1,itemid:" + query.Item_Id + "}";
                            isUpc = false;
                        }
                    }
                    if (isUpc)
                    {
                        dt = _IiupcMgr.upcid(m);
                        if (dt.Rows.Count == 0)
                        {
                            json = "{success:false,msg:0}";
                        }
                        else if (dt.Rows.Count > 1)
                        {
                            json = "{success:false,msg:2}";
                        }
                        else
                        {
                            json = "{success:true,msg:1,itemid:" + dt.Rows[0]["item_id"].ToString() + "}";
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
コード例 #23
0
        //匯出 等待料位報表
        public void ExportCSV() // createTime 2015/10/21 by yachao1120j
        {
            ProductItemQuery query = new ProductItemQuery();
            try
            {
                if (!string.IsNullOrEmpty(Request.Params["product_mode"]))
                {
                    query.product_mode = Convert.ToInt32(Request.Params["product_mode"]);//出貨方式
                }
                if (!string.IsNullOrEmpty(Request.Params["freight"]))
                {
                    query.product_freight_set = Convert.ToUInt32(Request.Params["freight"]);//溫層
                }
                //if (!string.IsNullOrEmpty(Request.Params["product_status"]))
                //{
                //    query.product_status = Convert.ToUInt32(Request.Params["product_status"]);//商品状态
                //}
                if (!string.IsNullOrEmpty(Request.Params["start_time"]))//開始時間
                {
                    //query.start_time = Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00");
                    query.start_time = (int)CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd HH:mm:ss"));
                }
                if (!string.IsNullOrEmpty(Request.Params["end_time"]))//結束時間
                {
                    //query.end_time = Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59");
                    query.end_time = (int)CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd HH:mm:ss"));
                }
                DataTable dtHZ = new DataTable();
                int totalcount = 0;
                query.IsPage = false;
                string newExcelName = string.Empty;
                dtHZ.Columns.Add("料位編號", typeof(String));
                dtHZ.Columns.Add("商品細項編號", typeof(String));
                dtHZ.Columns.Add("商品名稱", typeof(String));
                dtHZ.Columns.Add("商品規格", typeof(String));
                dtHZ.Columns.Add("商品類型", typeof(String));
                dtHZ.Columns.Add("分類--大類", typeof(String));
                dtHZ.Columns.Add("分類--小類", typeof(String));
                dtHZ.Columns.Add("商品狀態", typeof(String));
                dtHZ.Columns.Add("出貨方式", typeof(String));
                dtHZ.Columns.Add("溫層", typeof(String));
                dtHZ.Columns.Add("商品建立日期", typeof(String));
                dtHZ.Columns.Add("商品上架時間", typeof(String));
                dtHZ.Columns.Add("採購單單號", typeof(String));
                productitemMgr = new ProductItemMgr(mySqlConnectionString);
                List<ProductItemQuery> list = productitemMgr.GetWaitLiaoWeiList(query, out totalcount);
                if (list.Count > 0)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        DataRow dr = dtHZ.NewRow();
                        dr[0] = list[i].plas_id_string;
                        dr[1] = list[i].item_id;
                        dr[2] = list[i].product_name;
                        dr[3] = list[i].product_spec;
                        dr[4] = list[i].combination_string;
                        dr[5] = list[i].product_fenlei_dalei;
                        dr[6] = list[i].product_fenlei_xiaolei;
                        dr[7] = list[i].product_status_string;
                        dr[8] = list[i].product_mode_string;
                        dr[9] = list[i].product_freight_set_string;
                        dr[10] = list[i].product_createdate_string;
                        dr[11] = list[i].product_start_string;
                        dr[12] = list[i].po_id;
                        dtHZ.Rows.Add(dr);
                    }
                    string fileName = "等待料位報表匯出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                    MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "");
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
                    Response.BinaryWrite(ms.ToArray());
                }
                else
                {
                    Response.Clear();
                    this.Response.Write("無數據存在<br/>");
                }

            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);

            }
        }
コード例 #24
0
        public void ExportCSV()// yachao1120j 2015-9-10 匯出商品庫存查詢記錄
        {
            ProductItemQuery query = new ProductItemQuery();
            try
            {
                if (!string.IsNullOrEmpty(Request.Params["vendor_name_full_OR_vendor_id"]))
                {
                    query.vendor_name_full_OR_vendor_id = Request.Params["vendor_name_full_OR_vendor_id"];//供应商名称/编号
                }
                if (!string.IsNullOrEmpty(Request.Params["product_id_OR_product_name"]))
                {
                    query.product_id_OR_product_name = Request.Params["product_id_OR_product_name"];//商品编号/名称
                }
                if (!string.IsNullOrEmpty(Request.Params["brand_id_OR_brand_name"]))
                {
                    query.brand_id_OR_brand_name = Request.Params["brand_id_OR_brand_name"];//品牌编号/名称
                }
                if (!string.IsNullOrEmpty(Request.Params["product_status"]))
                {
                    query.product_status = Convert.ToUInt32(Request.Params["product_status"]);//商品状态
                }
                if (!string.IsNullOrEmpty(Request.Params["sale_status"]))
                {
                    query.sale_status = Convert.ToUInt32(Request.Params["sale_status"]);//商品販售狀態
                }
                if (!string.IsNullOrEmpty(Request.Params["item_stock_start"]) && Request.Params["item_stock_start"] != "null")
                {
                    query.item_stock_start = Convert.ToInt32(Request.Params["item_stock_start"]);//库存数量--开始
                }
                else 
                {
                    query.item_stock_start = 0;
                }
                if (!string.IsNullOrEmpty(Request.Params["item_stock_end"]) && Request.Params["item_stock_end"] != "null")
                {
                    query.item_stock_end = Convert.ToInt32(Request.Params["item_stock_end"]);//库存数量--结束
                }
                else
                {
                    query.item_stock_end = 0;
                }
                if (!string.IsNullOrEmpty(Request.Params["ignore_stockRdo"]))
                {
                    query.ignore_stock = Convert.ToInt32(Request.Params["ignore_stockRdo"]);//补货中停止贩售
                }
                DataTable dtHZ = new DataTable();
                int totalcount = 0;
                query.IsPage = false;
                string newExcelName = string.Empty;
                dtHZ.Columns.Add("供應商編號", typeof(String));
                dtHZ.Columns.Add("供應商名稱", typeof(String));
                dtHZ.Columns.Add("品牌編號", typeof(String));
                dtHZ.Columns.Add("品牌名稱", typeof(String));
                dtHZ.Columns.Add("商品編號", typeof(String));
                dtHZ.Columns.Add("商品名稱", typeof(String));
                dtHZ.Columns.Add("商品細項編號", typeof(String));
                dtHZ.Columns.Add("商品規格", typeof(String));
                dtHZ.Columns.Add("商品狀態", typeof(String));
                dtHZ.Columns.Add("商品販售狀態", typeof(String));
                dtHZ.Columns.Add("庫存為0時是否還能販售", typeof(String));
                dtHZ.Columns.Add("庫存數量", typeof(String));
                List<ProductItemQuery> list = new List<ProductItemQuery>();
                productitemMgr = new ProductItemMgr(connectionString);
                list = productitemMgr.GetInventoryQueryList(query, out totalcount);
                if (list.Count > 0)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        DataRow dr = dtHZ.NewRow();

                        dr[0] = list[i].vendor_id;
                        dr[1] = list[i].vendor_name_full;
                        dr[2] = list[i].brand_id;
                        dr[3] = list[i].brand_name;
                        dr[4] = list[i].Product_Id;
                        dr[5] = list[i].product_name;
                        dr[6] = list[i].Item_Id;
                        dr[7] = list[i].product_spec;
                        dr[8] = list[i].product_status_string;
                        dr[9] = list[i].sale_status_string;
                        dr[10] = list[i].ignore_stock_string;
                        dr[11] = list[i].Item_Stock;
                        dtHZ.Rows.Add(dr);
                    }
                    string fileName = "product_stock_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                    MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "");
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
                    Response.BinaryWrite(ms.ToArray());
                }
                else
                {
                    Response.Clear();
                    this.Response.Write("無數據存在<br/>");
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
            }


        }
コード例 #25
0
        public bool Start(string schedule_code)
        {
            bool result = false;

            try
            {
                if (string.IsNullOrEmpty(schedule_code))
                {
                    return result;
                }
                productItemMgr = new ProductItemMgr(mySqlConnectionString);
                _paraMgr = new ParameterMgr(mySqlConnectionString);
                ProductItemQuery query = new ProductItemQuery();
                ArrivalNotice arriva = new ArrivalNotice();
                MailModel mailModel = new MailModel();
                mailModel.MysqlConnectionString = mySqlConnectionString;
                string GroupCode = string.Empty;
                string MailTitle = string.Empty;
                string MailBody = string.Empty;
                string NOSuggestCountMsg = "今天沒有要採購的商品";//沒有要採購的商品提示
                string sumDays = "60";//採購總天數
                string periodDays = "1";//採購週期天數
                ScheduleServiceMgr _secheduleServiceMgr;

                //獲取該排程參數
                List<ScheduleConfigQuery> store_config = new List<ScheduleConfigQuery>();
                ScheduleConfigQuery query_config = new ScheduleConfigQuery();
                query_config.schedule_code = schedule_code;
                _secheduleServiceMgr = new ScheduleServiceMgr(mySqlConnectionString);
                store_config = _secheduleServiceMgr.GetScheduleConfig(query_config);
                #region FTP參數賦值
                foreach (ScheduleConfigQuery item in store_config)
                {
                    if (item.parameterCode.Equals("MailFromAddress"))
                    {
                        mailModel.MailFromAddress = item.value;
                    }
                    else if (item.parameterCode.Equals("MailHost"))
                    {
                        mailModel.MailHost = item.value;
                    }
                    else if (item.parameterCode.Equals("MailPort"))
                    {
                        mailModel.MailPort = item.value;
                    }
                    else if (item.parameterCode.Equals("MailFromUser"))
                    {
                        mailModel.MailFromUser = item.value;
                    }
                    else if (item.parameterCode.Equals("EmailPassWord"))
                    {
                        mailModel.MailFormPwd = item.value;
                    }
                    else if (item.parameterCode.Equals("GroupCode"))
                    {
                        GroupCode = item.value;
                    }
                    else if (item.parameterCode.Equals("MailTitle"))
                    {
                        MailTitle = item.value;
                    }
                    else if (item.parameterCode.Equals("MailTitle"))
                    {
                        MailTitle = item.value;
                    }
                    else if (item.parameterCode.Equals("MailBody"))
                    {
                        MailBody = item.value;
                    }
                    else if (item.parameterCode.Equals("sumDays"))
                    {
                        sumDays = item.value;
                    }
                    else if (item.parameterCode.Equals("periodDays"))
                    {
                        periodDays = item.value;
                    }
                    else if (item.parameterCode.Equals("NOSuggestCountMsg"))
                    {
                        NOSuggestCountMsg = item.value;
                    }
                }
                #endregion
                Parametersrc p = new Parametersrc();
                List<Parametersrc> list = new List<Parametersrc>();
                p.ParameterType = "Food_Articles";
                list = _paraMgr.GetAllKindType(p.ParameterType);
                for (int i = 0; i < list.Count; i++)/*要禁用的食品錧和用品館的商品*/
                {
                    if (!string.IsNullOrEmpty(list[i].ParameterCode))
                    {
                        query.category_ID_IN += list[i].ParameterCode + ",";
                    }
                }
                query.sumDays = int.Parse(sumDays);
                query.periodDays = int.Parse(periodDays);
                query.category_ID_IN = query.category_ID_IN.TrimEnd(',');
                query.sale_status = 100;
                query.Is_pod = 0;
                query.stockScope = 2;
                query.prepaid = -1;
                query.IsPage = false;
                int totalCount = 0;

                DataTable dt = productItemMgr.GetSuggestPurchaseInfo(query, out totalCount);
                MailHelper mail = new MailHelper(mailModel);
                if (dt.Rows.Count > 0)
                {
                    DataTable dtExcel = new DataTable();
                    dtExcel.Columns.Add("行號", typeof(String));
                    dtExcel.Columns.Add("供應商編號", typeof(String));
                    dtExcel.Columns.Add("供應商名稱", typeof(String));//
                    dtExcel.Columns.Add("商品編號", typeof(String));
                    dtExcel.Columns.Add("商品細項編號", typeof(String));
                    dtExcel.Columns.Add("商品ERP編號", typeof(String));
                    dtExcel.Columns.Add("商品名稱", typeof(String));
                    //dtExcel.Columns.Add("商品狀態", typeof(String));
                    //dtExcel.Columns.Add("販售狀態", typeof(String));
                    dtExcel.Columns.Add("規格", typeof(String));
                    // dtExcel.Columns.Add("規格二", typeof(String));
                    dtExcel.Columns.Add("庫存量", typeof(String));
                    dtExcel.Columns.Add("後台庫存量", typeof(String));
                    dtExcel.Columns.Add("安全存量", typeof(String));
                    dtExcel.Columns.Add("建議採購量", typeof(String));//ipo_qty
                    dtExcel.Columns.Add("未驗收數量", typeof(String));
                    dtExcel.Columns.Add("補貨通知人數", typeof(String));
                    dtExcel.Columns.Add("成本", typeof(String));
                    dtExcel.Columns.Add("價格", typeof(String));
                    dtExcel.Columns.Add("上架時間", typeof(String));
                    dtExcel.Columns.Add("下架時間", typeof(String));
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        #region
                        DataRow newRow = dtExcel.NewRow();
                        newRow[0] = i + 1;
                        newRow[1] = Convert.ToInt64(dt.Rows[i]["vendor_id"]);
                        newRow[2] = dt.Rows[i]["vendor_name_full"];
                        newRow[3] = dt.Rows[i]["product_id"];
                        newRow[4] = dt.Rows[i]["item_id"];
                        newRow[5] = dt.Rows[i]["erp_id"];
                        newRow[6] = dt.Rows[i]["product_name"];
                        //newRow[7] = dt.Rows[i]["product_status_string"];
                        //newRow[8] = dt.Rows[i]["sale_name"];
                        newRow[7] = dt.Rows[i]["spec_title_1"];

                        newRow[8] = dt.Rows[i]["item_stock"];
                        newRow[9] = dt.Rows[i]["iinvd_stock"];
                        newRow[10] = dt.Rows[i]["item_alarm"];
                        if (string.IsNullOrEmpty(dt.Rows[i]["sum_total"].ToString()))
                        {
                            newRow[11] = 0;
                        }
                        #region
                        else
                        {
                            double sum_total = 0;
                            int safe_stock_amount = 0;
                            int item_stock = 0;
                            int item_alarm = 0;
                            int procurement_days = 0;
                            if (double.TryParse(dt.Rows[i]["sum_total"].ToString(), out sum_total))
                            {
                                sum_total = Convert.ToDouble(dt.Rows[i]["sum_total"]);
                            }
                            if (int.TryParse(dt.Rows[i]["safe_stock_amount"].ToString(), out safe_stock_amount))
                            {
                                safe_stock_amount = Convert.ToInt32(dt.Rows[i]["safe_stock_amount"]);
                            }
                            if (int.TryParse(dt.Rows[i]["item_stock"].ToString(), out item_stock))
                            {
                                item_stock = Convert.ToInt32(dt.Rows[i]["item_stock"]);
                            }
                            if (int.TryParse(dt.Rows[i]["item_alarm"].ToString(), out item_alarm))
                            {
                                item_alarm = Convert.ToInt32(dt.Rows[i]["item_alarm"]);
                            }
                            if (int.TryParse(dt.Rows[i]["procurement_days"].ToString(), out procurement_days))
                            {
                                procurement_days = Convert.ToInt32(dt.Rows[i]["procurement_days"]);
                            }
                            if (item_stock - procurement_days * sum_total / query.sumDays * query.periodDays <= item_alarm)
                            {
                                //建議採購量:供應商的進貨天數*採購調整系數*近3個月的平均每周銷售數量(最小值為1)

                                double suggestPurchaseTemp = (procurement_days + safe_stock_amount) * (sum_total / query.sumDays) * query.periodDays + ((item_alarm - item_stock) > 0 ? (item_alarm - item_stock) : 0);

                                if (suggestPurchaseTemp <= int.Parse(dt.Rows[i]["min_purchase_amount"].ToString()))   //最小值為1
                                {
                                    newRow[11] = dt.Rows[i]["min_purchase_amount"];
                                }
                                else
                                {
                                    int suggestPurchase = Convert.ToInt32(suggestPurchaseTemp);
                                    if (suggestPurchase < suggestPurchaseTemp)
                                    {
                                        newRow[11] = Convert.ToInt32(suggestPurchaseTemp) + 1;
                                    }
                                    else
                                    {
                                        newRow[11] = Convert.ToInt32(suggestPurchaseTemp);
                                    }
                                }
                            }

                        }
                        #endregion
                        newRow[12] = dt.Rows[i]["ipo_qty"];
                        newRow[13] = dt.Rows[i]["NoticeGoods"];

                        newRow[14] = dt.Rows[i]["item_cost"];
                        newRow[15] = dt.Rows[i]["item_money"];
                        newRow[16] = string.IsNullOrEmpty(dt.Rows[i]["product_start"].ToString()) ? " " : DateTime.Parse(dt.Rows[i]["product_start"].ToString()).ToString("yyyy-MM-dd hh:mm:ss");
                        newRow[17] = string.IsNullOrEmpty(dt.Rows[i]["product_end"].ToString()) ? "" : DateTime.Parse(dt.Rows[i]["product_end"].ToString()).ToString("yyyy-MM-dd hh:mm:ss");
                        dtExcel.Rows.Add(newRow);
                        #endregion
                    }


                    string EmailContent = GetMail(dtExcel);
                    mail.SendToGroup(GroupCode, MailTitle, EmailContent, false, true);//發送郵件給群組
                }
                else
                {
                    mail.SendToGroup(GroupCode, MailTitle, NOSuggestCountMsg, false, true);//發送郵件給群組 
                }
                result = true;
            }
            catch (Exception ex)
            {
                throw new Exception("SendOrderInfoToBlackCatFTPMgr-->Start-->" + ex.Message);
            }
            return result;
        }