/// <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); } }
/// <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); } }
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; }
/// <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); } }
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); } }
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; }
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); } }
/*** * 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); } }
public List<ProductItemQuery> GetProductItemByID(ProductItemQuery query) { try { return productItemDao.GetProductItemByID(query); } catch (Exception ex) { throw new Exception("ProductItemMgr-->GetProductItemByID" + ex.Message, ex); } }
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); } }
/// <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); } }
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; }
/// <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; }
/// <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); } }
public int GetATMStock(ProductItemQuery query) { try { return _productItemDao.GetATMStock(query); } catch (Exception ex) { throw new Exception("ProductItemMgr->GetATMStock" + ex.Message); } }
/// <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); } }
public List<ProductItemQuery> GetProdItemByERp(ProductItemQuery query) { try { return _productItemDao.GetProdItemByERp(query); } catch (Exception ex) { throw new Exception("ProductItemMgr->GetProdItemByERp" + ex.Message); } }
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); } }
public int UpdateStockAsErpId(ProductItemQuery query) { try { return _productItemDao.UpdateStockAsErpId(query); } catch (Exception ex) { throw new Exception("ProductItemMgr->UpdateStockAsErpId" + ex.Message); } }
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); } }
// 等待料位報表 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; }
//根據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; }
//匯出 等待料位報表 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); } }
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); } }
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; }