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