public HttpResponseBase GetVendorProductSpec() { string json = string.Empty; DataTable _dt = new DataTable(); ProductQuery query = new ProductQuery(); int totalCount = 0; try { query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); query.Product_Id = Convert.ToUInt32(Request.Params["product_id"]); _IProductMgr = new ProductMgr(mySqlConnectionString); _dt = _IProductMgr.GetVendorProductSpec(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_dt, 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,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json.ToString()); this.Response.End(); return this.Response; }
public DataTable GetProductDetialText(ProductQuery p, string id, out string notFind) { string[] num = id.Split(','); DataTable _dt = new DataTable(); uint pro_id = 0; notFind = string.Empty; DataTable _result = new DataTable(); _result.Columns.Add("product_detail_text"); try { foreach (var item in num) { if (uint.TryParse(item.Trim(), out pro_id)) { p.Product_Id = pro_id; _dt = _productDao.GetProductDetialText(p); if (_dt != null && _dt.Rows.Count != 0) { if (!string.IsNullOrEmpty(_dt.Rows[0][0].ToString())) { DataRow _dr = _result.NewRow(); ; _dr[0] = _dt.Rows[0][0]; _result.Rows.Add(_dr); } else { notFind = item; return null; } } else { notFind = item; return null; } } continue; } } catch (Exception ex) { throw new Exception("ProductMgr.GetProductDetialText-->" + ex.Message, ex); } return _result; }
public DataTable GetProductList(ProductQuery p, out int totalCount) { try { return _productDao.GetProductList(p, out totalCount); } catch (Exception ex) { throw new Exception("ProductMgr.GetProductList-->" + ex.Message, ex); } }
public DataTable GetVendorProductSpec(ProductQuery query, out int totalCount) { try { return _productDao.GetVendorProductSpec(query, out totalCount); } catch (Exception ex) { throw new Exception("ProductMgr.GetVendorProductSpec-->" + ex.Message, ex); } }
public DataTable NoIlocReportList(ProductQuery query) { try { return _IplasDao.NoIlocReportList(query); } catch (Exception ex) { throw new Exception("IplasMgr-->NOIlocReportList-->" + ex.Message, ex); } }
public HttpResponseBase GetProductDetialText() { ProductQuery query = new ProductQuery(); string json = string.Empty; DataTable result = new DataTable(); string notFind = string.Empty; try { _productMgr = new ProductMgr(connectionString); if (!string.IsNullOrEmpty(Request.Params["product_IDS"])) { result = _productMgr.GetProductDetialText(query, Request.Params["product_IDS"], out notFind); if (result != null) { for (int i = 0; i < result.Rows.Count; i++) { result.Rows[i][0] = result.Rows[i][0].ToString().Replace("<br/>", "\n"); } json = "{success:true,data:" + JsonConvert.SerializeObject(result, Newtonsoft.Json.Formatting.Indented) + "}"; } else { json = "{success:false,data:" + JsonConvert.SerializeObject(notFind, Newtonsoft.Json.Formatting.Indented) + "}"; } } } 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,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase GetProductByCategorySet() { string resultStr = "{success:false}"; try { _productMgr = new ProductMgr(connectionString); ProductQuery query = new ProductQuery(); query.isjoincate = true; query.IsPage = true; query.Start = int.Parse(Request.Form["start"] ?? "0"); if (!string.IsNullOrEmpty(Request.Form["limit"])) { query.Limit = Convert.ToInt32(Request.Form["limit"].ToString()); } else { query.Limit = 500; } uint isTryUint = 0; if (uint.TryParse(Request.Params["status"].ToString(), out isTryUint)) { query.Product_Status = Convert.ToUInt32(Request.Params["status"].ToString()); } if (uint.TryParse(Request.Params["brand_id"].ToString(), out isTryUint)) { query.Brand_Id = Convert.ToUInt32(Request.Params["brand_id"].ToString()); } else { if (!string.IsNullOrEmpty(Request.Params["class_id"])) { VendorBrandSetMgr vbsMgr = new VendorBrandSetMgr(connectionString); VendorBrandSet vbs = new VendorBrandSet(); vbs.class_id = Convert.ToUInt32(Request.Form["class_id"]); List<VendorBrandSet> vbsList = vbsMgr.Query(vbs); foreach (VendorBrandSet item in vbsList) { query.brandArry += item.brand_id; query.brandArry += ","; } query.brandArry = query.brandArry.Substring(0, query.brandArry.Length - 1); } } if (!string.IsNullOrEmpty(Request.Params["keyCode"].ToString())) { string keyCode = Request.Params["keyCode"].ToString(); keyCode = keyCode.Replace(",", ","); keyCode = Regex.Replace(keyCode, @"\s+", ","); if (keyCode.Substring(keyCode.Length - 1).Equals(",")) { keyCode = keyCode.Substring(0, keyCode.Length - 1); } string[] ProductID = keyCode.Split(','); try { for (int i = 0; i < ProductID.Length; i++) { int ID = int.Parse(ProductID[i]); } query.Product_Id = 0; query.Product_Id_In = keyCode; } catch (Exception) { query.Product_Name = Request.Params["keyCode"].ToString(); } } //if (!string.IsNullOrEmpty(Request.Params["keyCode"].ToString())) //{ // if (uint.TryParse(Request.Params["keyCode"].ToString(), out isTryUint)) // { // query.Product_Id = Convert.ToUInt32(Request.Params["keyCode"].ToString()); // } // else // { // query.Product_Name = Request.Params["keyCode"].ToString(); // } //} if (string.IsNullOrEmpty(Request.Params["category_id"].ToString())) { if (uint.TryParse(Request.Params["comboFrontCage_hide"].ToString(), out isTryUint)) { query.category_id = Convert.ToUInt32(Request.Params["comboFrontCage_hide"].ToString()); } } else { if (uint.TryParse(Request.Params["category_id"].ToString(), out isTryUint)) { query.category_id = Convert.ToUInt32(Request.Params["category_id"].ToString()); } } int totalCount = 0; List<ProductDetailsCustom> prods = _productMgr.GetAllProList(query, out totalCount); resultStr = "{succes:true,totalCount:" + totalCount + ",item:" + JsonConvert.SerializeObject(prods) + "}"; } 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); resultStr = "{succes:false,totalCount:0,item:[]}"; } this.Response.Clear(); this.Response.Write(resultStr); this.Response.End(); return this.Response; }
/// <summary> /// 查詢主料位 裡面的商品 並且需要摘除主料位的商品信息 /// </summary> /// <param name="query">商品實體</param> /// <param name="totalCount">分頁顯示</param> /// <returns>list頁面</returns> public DataTable GetIlocReportList(ProductQuery query, out int totalCount) { StringBuilder sql = new StringBuilder(); totalCount = 0; try { sql.AppendFormat(@" SELECT loc.loc_id as loc_id, iplas.item_id, CONCAT(vb.brand_name,'-',p.product_name) as Product_Name,pi.product_id, p.brand_id, v.vendor_name_simple as vendor_name_simple, upc.upc_id as upc_id,p.product_mode,tp.parameterName, (select sum(prod_qty) from iinvd where iinvd.item_id=iplas.item_id) as product_qty,concat(IFNULL(ps1.spec_name,''),IFNULL(ps2.spec_name,'')) as prod_sz from iplas iplas LEFT JOIN product_ext pe on pe.item_id = iplas.item_id LEFT JOIN iloc loc on loc.loc_id=iplas.loc_id LEFT JOIN product_item pi on pi.item_id=iplas.item_id LEFT JOIN product_spec ps1 ON pi.spec_id_1 = ps1.spec_id LEFT JOIN product_spec ps2 ON pi.spec_id_2 = ps2.spec_id LEFT JOIN product p on p.product_id=pi.product_id LEFT JOIN t_parametersrc tp on tp.parameterCode=p.product_mode and tp.parameterType='product_mode' LEFT JOIN vendor_brand vb on vb.brand_id=p.brand_id LEFT JOIN vendor v on v.vendor_id=vb.vendor_id LEFT JOIN (SELECT upc_id,item_id from iupc GROUP BY item_id) upc on upc.item_id=iplas.item_id where pe.pend_del='Y' and loc.lcat_id='S' "); if (int.Parse(query.vendor_name_simple) != 0) { sql.AppendFormat(" and v.vendor_id='{0}' ", query.vendor_name_simple); } totalCount = 0; sql.AppendFormat(" ORDER BY loc_id asc "); return _dbAccess.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("IplasDao-->GetIlocReportList-->" + ex.Message + sql.ToString(), ex); } }
public DataTable GetVendorProductSpec(ProductQuery query, out int totalCount) { query.Replace4MySQL(); StringBuilder sb = new StringBuilder(); StringBuilder sbcount = new StringBuilder(); sb.AppendFormat(@"SELECT pii.product_id,pii.item_id,ps.spec_name as specone,pst.spec_name as spectwo,pii.item_stock,ps.spec_status as spec_status_one,pst.spec_status as spec_status_two,ps.spec_id as spec_id_one,pst.spec_id as spec_id_two FROM product_item pii LEFT JOIN product_spec ps on ps.spec_id=pii.spec_id_1 LEFT JOIN product_spec pst on pst.spec_id=pii.spec_id_2 WHERE pii.product_id='{0}' ORDER BY pii.item_id ASC ", query.Product_Id); sbcount.AppendFormat(@"SELECT count(1) as totalcounts FROM product_item pii LEFT JOIN product_spec ps on ps.spec_id=pii.spec_id_1 LEFT JOIN product_spec pst on pst.spec_id=pii.spec_id_2 WHERE pii.product_id='{0}' ", query.Product_Id); try { totalCount = 0; if (query.IsPage) { System.Data.DataTable _dt = _dbAccess.getDataTable(sbcount.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]); } sb.AppendFormat(" limit {0},{1}", query.Start, query.Limit); } return _dbAccess.getDataTable(sb.ToString()); } catch (Exception ex) { throw new Exception("ProductDao-->GetVendorProductSpec-->" + ex.Message + sb.ToString() + sbcount.ToString(), ex); } }
public DataTable GetVendorProductList(ProductQuery query, out int totalCount) { query.Replace4MySQL(); StringBuilder sb = new StringBuilder(); StringBuilder sbadi = new StringBuilder(); sb.Append(@"SELECT vdMsg.vendor_id,pt.product_id,pt.brand_id,pt.product_name,pt.product_status,pt.shortage,vdMsg.vendor_name_simple,vdMsg.brand_name,tpPm.parameterName as nProductStatus"); sbadi.AppendFormat(@" FROM product pt RIGHT JOIN (SELECT vd.vendor_name_full,vd.vendor_name_simple,vb.brand_name,vb.brand_name_simple,vd.vendor_id,vb.brand_id FROM vendor vd LEFT JOIN vendor_brand vb on vd.vendor_id =vb.vendor_id where vd.vendor_id='{0}') as vdMsg on pt.brand_id=vdMsg.brand_id LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType='product_status') as tpPm on pt.product_status=tpPm.parameterCode where pt.product_mode in(1,3) and pt.product_id>10000 ", query.Vendor_Id); if (!string.IsNullOrEmpty(query.searchcontent.Trim())) { sbadi.AppendFormat("and pt.product_id in ({0}) ", query.searchcontent.Trim()); } if (query.this_product_state != -1) { sbadi.AppendFormat("and pt.product_status = '{0}' ", query.this_product_state); } sbadi.Append(" ORDER BY pt.product_id ASC "); try { totalCount = 0; if (query.IsPage) { System.Data.DataTable _dt = _dbAccess.getDataTable("SELECT count(1) as totalcounts " + sbadi.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]); } sbadi.AppendFormat(" limit {0},{1}", query.Start, query.Limit); } sb.Append(sbadi.ToString()); return _dbAccess.getDataTable(sb.ToString()); } catch (Exception ex) { throw new Exception("ProductDao-->GetVendorProductList-->" + ex.Message + sb.ToString(), ex); } }
/// <summary> /// chaojie1124j add by 2015/10/26 實現下架狀態明細表 /// </summary> /// <param name="query"></param> /// <param name="TotalCount"></param> /// <returns></returns> public DataTable GetStatusListLowerShelf(ProductQuery query, out int TotalCount)//List<ProductQuery> { try { return productItemDao.GetStatusListLowerShelf(query, out TotalCount); } catch (Exception ex) { throw new Exception("ProductItemMgr-->GetStatusListLowerShelf-->" + ex.Message, ex); } }
public HttpResponseBase GetProductByCategorySet() { string resultStr = "{success:false}"; string str = string.Empty; try { _productMgr = new ProductMgr(mySqlConnectionString); ProductQuery query = new ProductQuery(); query.isjoincate = true; query.IsPage = true; query.Start = int.Parse(Request.Form["start"] ?? "0"); if (!string.IsNullOrEmpty(Request.Form["limit"])) { query.Limit = Convert.ToInt32(Request.Form["limit"].ToString()); } else { query.Limit = 500; } uint isTryUint = 0; if (uint.TryParse(Request.Params["status"].ToString(), out isTryUint)) { query.Product_Status = Convert.ToUInt32(Request.Params["status"].ToString()); } if (uint.TryParse(Request.Params["brand_id"].ToString(), out isTryUint)) { query.Brand_Id = Convert.ToUInt32(Request.Params["brand_id"].ToString()); } else { if (!string.IsNullOrEmpty(Request.Params["class_id"])) { VendorBrandSetMgr vbsMgr = new VendorBrandSetMgr(mySqlConnectionString); VendorBrandSet vbs = new VendorBrandSet(); vbs.class_id = Convert.ToUInt32(Request.Form["class_id"]); List<VendorBrandSet> vbsList = vbsMgr.Query(vbs); foreach (VendorBrandSet item in vbsList) { query.brandArry += item.brand_id; query.brandArry += ","; } query.brandArry = query.brandArry.Substring(0, query.brandArry.Length - 1); } } if (!string.IsNullOrEmpty(Request.Params["keyCode"].ToString())) { if (uint.TryParse(Request.Params["keyCode"].ToString(), out isTryUint)) { query.Product_Id = Convert.ToUInt32(Request.Params["keyCode"].ToString()); } else { query.Product_Name = Request.Params["keyCode"].ToString(); } } if (!string.IsNullOrEmpty(Request.Params["category_id"].ToString())) { if (uint.TryParse(Request.Params["category_id"].ToString(), out isTryUint)) { //判斷是否是父節點,若是則獲取所有的category_id prodCateMgr = new ProductCategoryMgr(mySqlConnectionString); List<ProductCategory> category = prodCateMgr.QueryAll(new ProductCategory()); GetAllCategory_id(category, Convert.ToUInt32(Request.Params["category_id"].ToString()), ref str); query.categoryArry = str; } } int totalCount = 0; List<ProductDetailsCustom> prods = _productMgr.GetAllProList(query, out totalCount); resultStr = "{succes:true,totalCount:" + totalCount + ",item:" + JsonConvert.SerializeObject(prods) + "}"; } 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); resultStr = "{succes:false,totalCount:0,item:[]}"; } this.Response.Clear(); this.Response.Write(resultStr); this.Response.End(); return this.Response; }
public void GetStatusListLowerShelfExcel() { ProductQuery query = new ProductQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); DataTable dt = new DataTable(); query.IsPage = false; DataTable dtExcel = new DataTable(); try { if (!string.IsNullOrEmpty(Request.Params["Short"])) { query.Shortage = int.Parse(Request.Params["Short"]); } int p = 0; if (!string.IsNullOrEmpty(Request.Params["searchcon"].Trim())) { if (int.TryParse(Request.Params["searchcon"].Trim(), out p)) { if (Request.Params["searchcon"].Trim().Length == 5) { query.Product_Id = uint.Parse(Request.Params["searchcon"].Trim()); } else if (Request.Params["searchcon"].Trim().Length == 6) { query.item_id = uint.Parse(Request.Params["searchcon"].Trim()); } else { query.Product_Name = Request.Params["searchcon"].Trim(); } } else { query.Product_Name = Request.Params["searchcon"].Trim(); } } if (!string.IsNullOrEmpty(Request.Params["startIloc"].Trim())) { query.loc_id = Request.Params["startIloc"].Trim(); } if (!string.IsNullOrEmpty(Request.Params["endIloc"].Trim())) { query.loc_id2 = Request.Params["endIloc"].Trim() + "Z"; } if (!string.IsNullOrEmpty(Request.Params["fright_set"])) { query.product_freight = int.Parse(Request.Params["fright_set"]); } int totalCount = 0; dt = productItemMgr.GetStatusListLowerShelf(query, out totalCount); //添加兩列用於存儲"平均平均量"與"建議採購量" 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] = dt.Rows[i]["product_id"]; newRow[1] = dt.Rows[i]["item_id"]; newRow[2] = dt.Rows[i]["product_name"]; newRow[3] = dt.Rows[i]["spec_title_1"]; newRow[4] = dt.Rows[i]["loc_id"]; newRow[5] = dt.Rows[i]["plas_loc_id"]; newRow[6] = dt.Rows[i]["made_date"]; newRow[7] = dt.Rows[i]["cde_dt"]; newRow[8] = dt.Rows[i]["item_stock"]; newRow[9] = dt.Rows[i]["prod_qty"]; if (!string.IsNullOrEmpty(dt.Rows[i]["product_freight"].ToString())) { int product_freight = Convert.ToInt32(dt.Rows[i]["product_freight"]); if (product_freight == 1) newRow[10] = "常溫"; if (product_freight == 2) newRow[10] = "冷凍"; } if (!string.IsNullOrEmpty(dt.Rows[i]["prepaid"].ToString())) { int prepaid = Convert.ToInt32(dt.Rows[i]["prepaid"]); if (prepaid == 0) newRow[11] = "否"; if (prepaid == 1) newRow[11] = "是"; } newRow[12] = dt.Rows[i]["product_status_string"]; if (!string.IsNullOrEmpty(dt.Rows[i]["shortage"].ToString())) { int shortage = Convert.ToInt32(dt.Rows[i]["shortage"]); if (shortage == 0) newRow[13] = "否"; if (shortage == 1) newRow[13] = "是"; } 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 HttpResponseBase GetStatusListLowerShelf() { string json = string.Empty; DataTable dt = new DataTable(); ProductQuery query = new ProductQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); int totalCount = 0; try { if (!string.IsNullOrEmpty(Request.Params["Short"])) { query.Shortage = int.Parse(Request.Params["Short"]); } int p = 0; if (!string.IsNullOrEmpty(Request.Params["searchcon"].Trim())) { if (int.TryParse(Request.Params["searchcon"].Trim(), out p)) { if (Request.Params["searchcon"].Trim().Length == 5) { query.Product_Id = uint.Parse(Request.Params["searchcon"].Trim()); } else if (Request.Params["searchcon"].Trim().Length == 6) { query.item_id = uint.Parse(Request.Params["searchcon"].Trim()); } else { query.Product_Name = Request.Params["searchcon"].Trim(); } } else { query.Product_Name = Request.Params["searchcon"].Trim(); } } if (!string.IsNullOrEmpty(Request.Params["startIloc"].Trim())) { query.loc_id = Request.Params["startIloc"].Trim(); } if (!string.IsNullOrEmpty(Request.Params["endIloc"].Trim())) { query.loc_id2 = Request.Params["endIloc"].Trim()+"Z"; } if (!string.IsNullOrEmpty(Request.Params["fright_set"])) { query.product_freight = int.Parse(Request.Params["fright_set"]); } DataTable _td = productItemMgr.GetStatusListLowerShelf(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_td, 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:true,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
/// <summary> /// chaojie1124j add by 2015/10/26 實現下架狀態明細表 /// </summary> /// <param name="query"></param> /// <param name="TotalCount"></param> /// <returns></returns> public DataTable GetStatusListLowerShelf(ProductQuery query, out int TotalCount) { StringBuilder sqlClumn = new StringBuilder(); StringBuilder sqlCondi = new StringBuilder(); StringBuilder sbSqlCondition = new StringBuilder(); TotalCount = 0; try { sqlClumn.Append(" select p.product_id,p.product_name,ip.loc_id,ii.item_id ,dfsm.delivery_freight_set as product_freight,p.product_status,pi.item_stock,ii.plas_loc_id,ii.made_date,ii.cde_dt,ii.prod_qty, "); sqlClumn.Append(" p.prepaid,p.shortage,'' as product_status_string ,p.spec_title_1,p.spec_title_2,pi.spec_id_1,pi.spec_id_2"); sqlCondi.Append(" from product_item pi "); sqlCondi.Append(" left join iplas ip on ip.item_id=pi.item_id "); sqlCondi.Append(" inner 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 "); sqlCondi.Append(" inner join product p on pi.product_id=p.product_id "); sqlCondi.Append(" inner join delivery_freight_set_mapping dfsm on dfsm.product_freight_set=p.product_freight_set "); sqlCondi.Append(" left join v_product_item_stopsale on pi.item_id=v_product_item_stopsale.item_id "); sqlCondi.Append(" left join iinvd ii on ii.item_id=pi.item_id and ii.ista_id='A' "); sbSqlCondition.Append(" where 1=1 "); sbSqlCondition.Append(" and p.product_id>10000 "); if (query.Shortage != 0) { sbSqlCondition.AppendFormat(" and ((pi.item_id=v_product_item_stopsale.item_id)or p.shortage='{0}' ) ", query.Shortage); } else { sbSqlCondition.AppendFormat(" and pi.item_id=v_product_item_stopsale.item_id "); } if (query.Product_Id != 0) { sbSqlCondition.AppendFormat(" and (p.product_id='{0}' or p.product_name like '%{0}%' ) ", query.Product_Id); } if (query.item_id != 0) { sbSqlCondition.AppendFormat(" and (pi.item_id='{0}' or p.product_name like '%{0}%' ) ", query.item_id); } if (!string.IsNullOrEmpty(query.Product_Name)) { sbSqlCondition.AppendFormat(" and p.product_name like '%{0}%' ", query.Product_Name); } if (query.product_freight != 0) { sbSqlCondition.AppendFormat(" and dfsm.delivery_freight_set='{0}' ", query.product_freight); } if (!string.IsNullOrEmpty(query.loc_id)) { sbSqlCondition.AppendFormat(" and ip.loc_id>='{0}' ", query.loc_id); } if (!string.IsNullOrEmpty(query.loc_id2)) { sbSqlCondition.AppendFormat(" and ip.loc_id<='{0}' ", query.loc_id2); } sbSqlCondition.Append(" order by ii.item_id desc "); if (query.IsPage) { DataTable dt = _dbAccess.getDataTable("select count(ii.row_id) as totalCount " + sqlCondi.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); } DataTable dtResult = _dbAccess.getDataTable(sqlClumn.ToString() + sqlCondi.ToString() + sbSqlCondition.ToString()); IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr); IProductSpecImplDao _specDao = new ProductSpecDao(connStr); List<Parametersrc> parameterStatus = _parameterDao.QueryParametersrcByTypes("product_status"); foreach (DataRow dr in dtResult.Rows) { var slist = parameterStatus.Find(m => m.ParameterType == "product_status" && m.ParameterCode == dr["Product_Status"].ToString());// dr["product_status"].ToString() 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_1"].ToString() + " " + dr["spec_title_2"]; } return dtResult; } catch (Exception ex) { throw new Exception("ProductItemDao-->GetStatusListLowerShelf-->" + ex.Message + "", ex); } }
/// <summary> /// 列表 /// </summary> /// <param name="p"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable GetProductList(ProductQuery p, out int totalCount) { p.Replace4MySQL(); StringBuilder sql = new StringBuilder(); StringBuilder sqlfield = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); totalCount = 0; try { sqlfield.AppendLine(@"SELECT v.vendor_id,v.vendor_name_full, p.product_id,p.product_name,p.prod_classify,REPLACE(product_detail_text,'<br/>','\n') as product_detail_text, "); sqlfield.AppendFormat(@" p.product_status,u.user_username as create_username ,p.detail_createdate,uu.user_username as update_username ,p.detail_updatedate,vb.brand_name "); sqlwhere.AppendLine(@" FROM product p "); sqlwhere.AppendLine(@" LEFT JOIN vendor_brand vb ON p.brand_id=vb.brand_id "); sqlwhere.AppendLine(@" LEFT JOIN vendor v ON vb.vendor_id=v.vendor_id "); sqlwhere.AppendLine(@" LEFT JOIN manage_user u ON u.user_id=p.detail_created "); sqlwhere.AppendLine(@" LEFT JOIN manage_user uu ON uu.user_id=p.detail_update "); // sqlwhere.AppendFormat(@" LEFT JOIN ( SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType='product_status') pa ON p.product_status=pa.parameterCode "); sqlwhere.AppendLine(@" WHERE p.product_id > 10000 "); if (!string.IsNullOrEmpty(p.pids)) { sqlwhere.AppendFormat(@" AND p.product_id IN ({0}) ", p.pids); } if (!string.IsNullOrEmpty(p.Product_Name)) { string[] pname = p.Product_Name.Split(','); sqlwhere.AppendFormat(@" AND ( "); for (int i = 0; i < pname.Length; i++) { if (i == 0) { sqlwhere.AppendFormat(@" p.product_name LIKE '%{0}%' ", pname[i]); } else { sqlwhere.AppendFormat(@" OR p.product_name LIKE '%{0}%' ", pname[i]); } } sqlwhere.AppendFormat(@" )"); } if (p.Vendor_Id != 0) { sqlwhere.AppendFormat(@" AND v.vendor_id='{0}' ", p.Vendor_Id); } if (p.Brand_Id != 0) { sqlwhere.AppendFormat(@" AND p.brand_id='{0}' ", p.Brand_Id); } if (p.Product_Status != 999) { sqlwhere.AppendFormat(@" AND p.product_status='{0}' ", p.Product_Status); } //狀態 未編輯 if (p.product_detail_text == "1") { sqlwhere.AppendFormat(@" AND product_detail_text IS NULL "); } else if (p.product_detail_text == "2")//已編輯 { sqlwhere.AppendFormat(@" AND product_detail_text IS NOT NULL "); } if (!string.IsNullOrEmpty(p.create_username)) { sqlwhere.AppendFormat(@" AND u.user_username like N'%{0}%'", p.create_username.Trim()); } if (p.time_start != DateTime.MinValue) { sqlwhere.AppendFormat(@" AND p.detail_createdate >= '{0}'", Common.CommonFunction.DateTimeToString(p.time_start)); } if (p.time_end != DateTime.MinValue) { sqlwhere.AppendFormat(@" AND p.detail_createdate <= '{0}'", Common.CommonFunction.DateTimeToString(p.time_end)); } if (p.Combination != 0) { sqlwhere.AppendFormat(@" AND p.combination = '{0}'", p.Combination); } if (p.vendor_status != 0) { sqlwhere.AppendFormat(@" AND v.vendor_status = '{0}'", p.vendor_status); } if (p.brand_status != 0) { sqlwhere.AppendFormat(@" AND vb.brand_status = '{0}'", p.brand_status); } sql.AppendFormat(sqlfield.ToString() + sqlwhere.ToString()); if (p.IsPage) { sql.AppendFormat(@" LIMIT {0},{1}; ", p.Start, p.Limit); DataTable dt = _dbAccess.getDataTable("SELECT p.product_id " + sqlwhere.ToString()); if (dt != null && dt.Rows.Count > 0) { totalCount = dt.Rows.Count; } } return _dbAccess.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("ProductDao-->GetProductList" + ex.Message + sql.ToString(), ex); } }
public void GetIlocReportList() { string json = string.Empty; string fileName = "主料位商品摘除報表_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; ProductQuery prod = new ProductQuery(); prod.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 prod.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 try { if (!string.IsNullOrEmpty(Request.Params["search"])) { prod.vendor_name_simple = Request.Params["search"]; } _IiplasMgr = new IplasMgr(mySqlConnectionString); int totalCount = 0; DataTable store = _IiplasMgr.GetIlocReportList(prod, out totalCount); DataTable dtHZ = new DataTable(); 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)); foreach (DataRow item in store.Rows) { DataRow dr = dtHZ.NewRow(); dr[0] = item["loc_id"]; dr[1] = item["product_id"]; dr[2] = item["item_id"]; dr[3] = item["Product_Name"]; dr[4] = item["prod_sz"]; dr[5] = item["vendor_name_simple"]; dr[6] = " " + item["upc_id"]; dr[7] = item["parameterName"]; dr[8] = item["product_qty"]; dtHZ.Rows.Add(dr); } MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "主料位商品摘除報表" + "_" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")); Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.BinaryWrite(ms.ToArray()); } 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,data:[]}"; } }
public DataTable NoIlocReportList(ProductQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendLine(@"select loc_id,product_item.product_id as Product_Id ,product_item.item_id,CONCAT(v.brand_name,'-',p.product_name) as Product_Name,'' as prod_sz,vendor.vendor_name_simple as vendor_name_simple,'' as upc_id,pe.inner_pack_len,pe.inner_pack_wid,pe.inner_pack_hgt from product_item "); //sql.AppendFormat(@" LEFT JOIN product_spec ps1 ON product_item.spec_id_1 = ps1.spec_id LEFT JOIN product_spec ps2 ON product_item.spec_id_2 = ps2.spec_id "); sql.AppendLine(@" LEFT join product p on product_item.product_id=p.product_id LEFT join vendor_brand v on p.brand_id=v.brand_id "); sql.AppendLine(@"left join vendor on v.vendor_id=vendor.vendor_id "); //sql.AppendLine(@" left join (SELECT upc_id,product_item from iupc GROUP BY item_id) iupc on product_item.item_id=iupc.item_id "); sql.AppendLine(@"left join iplas on iplas.item_id=product_item.item_id "); sql.AppendLine("left join product_ext pe on pe.item_id=product_item.item_id "); sql.AppendLine(@"where product_item.item_id not in( select item_id from iplas ) and p.product_mode=2 "); if (int.Parse(query.vendor_name_simple) != 0) { sql.AppendFormat(" and vendor.vendor_id='{0}'", query.vendor_name_simple); } sql.AppendFormat("order by vendor.vendor_id,product_item.product_id DESC LIMIT 65000 "); return _dbAccess.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("IplasDao-->NoIlocReportList-->" + ex.Message + sql.ToString(), ex); } }
public void NoIlocReportList() { string json = string.Empty; string fileName = "無主料位商品報表_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; ProductQuery prod = new ProductQuery(); prod.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 prod.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 try { if (!string.IsNullOrEmpty(Request.Params["search"])) { prod.vendor_name_simple = Request.Params["search"]; } DataTable dt = new DataTable(); _IiplasMgr = new IplasMgr(mySqlConnectionString); _IiupcMgr = new IupcMgr(mySqlConnectionString); dt = _IiplasMgr.NoIlocReportList(prod); DataTable dtHZ = new DataTable(); 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)); // pe.inner_pack_len,pe.inner_pack_wid,pe.inner_pack_hgt foreach (DataRow item in dt.Rows) { DataRow dr = dtHZ.NewRow(); dr[0] = item["loc_id"]; dr[1] = item["Product_Id"]; dr[2] = item["item_id"]; dr[3] = item["Product_Name"]; dr[4] = GetProductSpec(item["item_id"].ToString()); dr[5] = item["vendor_name_simple"]; if (string.IsNullOrEmpty(_IiupcMgr.Getupc(item["item_id"].ToString(), "0"))) { dr[6] = "未維護"; } else { dr[6] = " " + _IiupcMgr.Getupc(item["item_id"].ToString(), "0"); } if (string.IsNullOrEmpty(item["inner_pack_len"].ToString())) { dr[7] = "未維護"; } else { dr[7] = item["inner_pack_len"]; } if (string.IsNullOrEmpty(item["inner_pack_wid"].ToString())) { dr[8] = "未維護"; } else { dr[8] = item["inner_pack_wid"]; } if (string.IsNullOrEmpty(item["inner_pack_hgt"].ToString())) { dr[9] = "未維護"; } else { dr[9] = item["inner_pack_hgt"]; } dtHZ.Rows.Add(dr); } MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "無主料位商品報表(寄倉品)" + "_" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")); Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.BinaryWrite(ms.ToArray()); #region CSV //string[] colname = new string[dtHZ.Columns.Count]; //string filename = "NoIplas.csv"; //newExcelName = Server.MapPath(excelPath) + filename; //for (int i = 0; i < dtHZ.Columns.Count; i++) //{ // colname[i] = dtHZ.Columns[i].ColumnName; //} //if (System.IO.File.Exists(newExcelName)) //{ // System.IO.File.Delete(newExcelName); //} //CsvHelper.ExportDataTableToCsv(dtHZ, newExcelName, colname, true); //IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; #endregion } 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,data:[]}"; } }
/// <summary> /// 列表頁 /// </summary> /// <returns></returns> public HttpResponseBase GetProductList() { ProductQuery query = new ProductQuery(); string json = string.Empty; try { int totalCount = 0; query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 _productMgr = new ProductMgr(connectionString); if (!string.IsNullOrEmpty(Request.Params["vendor_id"])) { query.Vendor_Id = uint.Parse(Request.Params["vendor_id"]); } if (!string.IsNullOrEmpty(Request.Params["brand_id"])) { query.Brand_Id = uint.Parse(Request.Params["brand_id"]); } if (!string.IsNullOrEmpty(Request.Params["product_status"])) { query.Product_Status = uint.Parse(Request.Params["product_status"]); } else { query.Product_Status = 999;//未選擇商品狀態 } if (!string.IsNullOrEmpty(Request.Params["creater"])) { query.create_username = Request.Params["creater"]; } if (!string.IsNullOrEmpty(Request.Params["timestart"])) { query.time_start = Convert.ToDateTime(Request.Params["timestart"]); } if (!string.IsNullOrEmpty(Request.Params["timeend"])) { query.time_end = Convert.ToDateTime(Request.Params["timeend"]); } if (!string.IsNullOrEmpty(Request.Params["search_content"])) { //支持空格,中英文逗號隔開 string content = Regex.Replace(Request.Params["search_content"].Trim(), "(\\s+)|(,)|(\\,)", ","); string[] contents = content.Split(','); int pid = 0; for (int i = 0; i < contents.Length; i++) { if (!string.IsNullOrEmpty(contents[i].Trim())) { if (query.pids == "" && query.Product_Name == "") { if (int.TryParse(contents[i], out pid)) { query.pids += Regex.Replace(contents[i].Trim(), "(\\s+)|(,)|(\\,)", ","); } else { query.Product_Name += Regex.Replace(contents[i].Trim(), "(\\s+)|(,)|(\\,)", ","); } } else { if (int.TryParse(contents[i], out pid)) { query.pids += "," + Regex.Replace(contents[i].Trim(), "(\\s+)|(,)|(\\,)", ","); } else { query.Product_Name += "," + Regex.Replace(contents[i].Trim(), "(\\s+)|(,)|(\\,)", ","); } } } else { continue; } } } string status = Request.Params["status"]; //判斷是不是已編輯 if (status == "0") { query.product_detail_text = string.Empty; } else if (status == "1") { query.product_detail_text = "1"; } else if (status == "2") { query.product_detail_text = "2"; } if (!string.IsNullOrEmpty(Request.Params["combination"])) { // Convert.ToUInt32(Request.Params["combination"]) != 0 query.Combination = uint.Parse(Request.Params["combination"]); } #region 供應商狀態、品牌狀態 if (!string.IsNullOrEmpty(Request.Params["vendorState"])) { query.vendor_status = Convert.ToInt32(Request.Params["vendorState"]); } if (!string.IsNullOrEmpty(Request.Params["brandState"])) { query.brand_status = Convert.ToInt32(Request.Params["brandState"]); } #endregion DataTable dt = _productMgr.GetProductList(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; //listUser是准备转换的对象 json = "{success:true,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:true,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public uint GetBrandId(uint productid) { ProductQuery query = new ProductQuery(); try { query = _inspectionReport.GetBrandId(productid); return query.Brand_Id; } catch (Exception ex) { throw new Exception("InspectionReportDao-->GetBrandId-->" + ex.Message , ex); } }
/// <summary> /// 匯出 /// </summary> /// <returns></returns> public HttpResponseBase ProductDetailExport() { string json = "false"; ProductQuery query = new ProductQuery(); _productMgr = new ProductMgr(connectionString); if (!string.IsNullOrEmpty(Request.Params["vendor_id"])) { query.Vendor_Id = uint.Parse(Request.Params["vendor_id"]); } if (!string.IsNullOrEmpty(Request.Params["brand_id"])) { query.Brand_Id = uint.Parse(Request.Params["brand_id"]); } if (!string.IsNullOrEmpty(Request.Params["product_status"])) { query.Product_Status = uint.Parse(Request.Params["product_status"]); } else { query.Product_Status = 999;//未選擇商品狀態 } if (!string.IsNullOrEmpty(Request.Params["creater"])) { query.create_username = Request.Params["creater"]; } if (!string.IsNullOrEmpty(Request.Params["timestart"])) { query.time_start = Convert.ToDateTime(Request.Params["timestart"]); } if (!string.IsNullOrEmpty(Request.Params["timeend"])) { query.time_end = Convert.ToDateTime(Request.Params["timeend"]); } if (!string.IsNullOrEmpty(Request.Params["search_content"])) { //支持空格,中英文逗號隔開 string content = Regex.Replace(Request.Params["search_content"].Trim(), "(\\s+)|(,)|(\\,)", ","); string[] contents = content.Split(','); int pid = 0; for (int i = 0; i < contents.Length; i++) { if (!string.IsNullOrEmpty(contents[i].Trim())) { if (query.pids == "" && query.Product_Name == "") { if (int.TryParse(contents[i], out pid)) { query.pids += Regex.Replace(contents[i].Trim(), "(\\s+)|(,)|(\\,)", ","); } else { query.Product_Name += Regex.Replace(contents[i].Trim(), "(\\s+)|(,)|(\\,)", ","); } } else { if (int.TryParse(contents[i], out pid)) { query.pids += "," + Regex.Replace(contents[i].Trim(), "(\\s+)|(,)|(\\,)", ","); } else { query.Product_Name += "," + Regex.Replace(contents[i].Trim(), "(\\s+)|(,)|(\\,)", ","); } } } else { continue; } } } string status = Request.Params["status"]; //判斷是不是已編輯 if (status == "0") { query.product_detail_text = string.Empty; } else if (status == "1") { query.product_detail_text = "1"; } else if (status == "2") { query.product_detail_text = "2"; } if (!string.IsNullOrEmpty(Request.Params["combination"])) { query.Combination = uint.Parse(Request.Params["combination"]); } #region 供應商狀態、品牌狀態 if (!string.IsNullOrEmpty(Request.Params["vendorState"])) { query.vendor_status = Convert.ToInt32(Request.Params["vendorState"]); } if (!string.IsNullOrEmpty(Request.Params["brandState"])) { query.brand_status = Convert.ToInt32(Request.Params["brandState"]); } #endregion query.IsPage = false; DataTable dtHZ = new DataTable(); 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)); try { int totalCount = 0; DataTable dt = _productMgr.GetProductList(query, out totalCount); foreach (DataRow dr_v in dt.Rows) { DataRow dr = dtHZ.NewRow(); dr[0] = dr_v["vendor_id"].ToString(); dr[1] = dr_v["vendor_name_full"].ToString(); dr[2] = dr_v["brand_name"].ToString(); dr[3] = dr_v["product_id"].ToString(); dr[4] = dr_v["product_name"].ToString(); dr[5] = dr_v["create_username"].ToString(); dr[6] = dr_v["detail_createdate"].ToString(); dr[7] = dr_v["update_username"].ToString(); dr[8] = dr_v["detail_updatedate"].ToString(); dtHZ.Rows.Add(dr); } string[] colname = new string[dtHZ.Columns.Count]; string filename = "product_detail" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; ; newExcelName = Server.MapPath(excelPath_export) + filename; for (int i = 0; i < dtHZ.Columns.Count; i++) { colname[i] = dtHZ.Columns[i].ColumnName; } if (System.IO.File.Exists(newExcelName)) { System.IO.File.Delete(newExcelName); } ExcelHelperXhf.ExportDTtoExcel(dtHZ, "", newExcelName); //CsvHelper.ExportDataTableToCsv(dtHZ, newExcelName, colname, true); json = "{success:true,ExcelName:\'" + filename + "\'}"; } 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); } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public DataTable GetIlocReportList(ProductQuery query, out int totalCount) { try { return _IplasDao.GetIlocReportList(query, out totalCount); } catch (Exception ex) { throw new Exception("IplasMgr-->GetIlocReportList-->" + ex.Message, ex); } }