public List<Model.Query.PromotionsAccumulateBonusQuery> Query(Model.Query.PromotionsAccumulateBonusQuery store, out int totalCount)
{
try
{
List<Model.Query.PromotionsAccumulateBonusQuery> stores = _proAccBonusDao.Query(store, out totalCount);
if (stores.Count > 0)
{
ParametersrcDao _pDa = new ParametersrcDao(conn);
List<Model.Parametersrc> alist = _pDa.QueryParametersrcByTypes("payment");
foreach (var item in stores)
{
string[] strids = item.payment_code.Split(',');
for (int i = 0; i < strids.Length; i++)
{
var clist = alist.Find(m => m.ParameterCode == strids[i].ToString());
if (clist != null)
{
item.payment_name += clist.parameterName + ",";
}
}
item.payment_name = item.payment_name.TrimEnd(',');
}
}
return stores;
}
catch (Exception ex)
{
throw new Exception("PromotionsAccumulateBonusMgr-->Query-->" + ex.Message, ex);
}
}
/// <summary>
/// 獲取滿額滿件免運的信息
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<PromotionsAmountFareQuery> Query(PromotionsAmountFareQuery query, out int totalCount)
{
try
{
List<PromotionsAmountFareQuery> _list = _promoFareDao.Query(query, out totalCount);
if (_list.Count > 0)
{
ParametersrcDao _parameterDao = new ParametersrcDao(connStr);
List<BLL.gigade.Model.Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_freight", "event_type", "device", "payment");
foreach (PromotionsAmountFareQuery item in _list)
{
var alist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == item.type.ToString());
var blist = parameterList.Find(m => m.ParameterType == "event_type" && m.ParameterCode == item.event_type.ToString());
var clist = parameterList.Find(m => m.ParameterType == "device" && m.ParameterCode == item.device.ToString());
if (alist != null)
{
item.typeName = alist.parameterName;
}
if (blist != null)
{
item.event_type_name = blist.parameterName;
}
if (clist != null)
{
item.deviceName = clist.parameterName;
}
if (!string.IsNullOrEmpty(item.payment_code))
{
string[] arryPayment = item.payment_code.Split(',');//將payment_code轉化為payment_name
for (int i = 0; i < arryPayment.Length; i++)
{
if (arryPayment[i] != "0")
{
var dlist = parameterList.Find(m => m.ParameterType == "payment" && m.ParameterCode == arryPayment[i].ToString());
if (dlist != null)
{
item.payment_name += dlist.parameterName + ",";
}
}
}
item.payment_name = item.payment_name.TrimEnd(',');
}
}
}
return _list;
}
catch (Exception ex)
{
throw new Exception("PromotionsAmountFareMgr-->Query-->" + ex.Message, ex);
}
}
public List<Model.Query.TicketQuery> GetTicketList(Model.Query.TicketQuery tqQuery, out int totalCount, string conditon)
{
StringBuilder sb = new StringBuilder();
StringBuilder strsql = new StringBuilder();
strsql.Append(@" SELECT COUNT(*)
FROM ticket AS Ticket
LEFT JOIN vendor AS Export ON Export.vendor_id = Ticket.export_id ");
//LEFT JOIN (SELECT * FROM t_parametersrc WHERE parameterType='Deliver_Store') as tp on tp.parameterCode=Ticket.delivery_store");
strsql.Append("WHERE 1=1 ");
sb.Append(@" SELECT Ticket.ticket_id, Ticket.delivery_store,Ticket.warehouse_status, Ticket.ticket_status, Ticket.created,Ticket.modified, Ticket.seized_status, Ticket.ship_status,
Ticket.Freight_status, Ticket.export_id, Ticket.freight_set, Export.vendor_name_simple,Ticket.delivery_store AS deliver_ys_type_id
FROM ticket AS Ticket
LEFT JOIN vendor AS Export ON Export.vendor_id = Ticket.export_id ");
//LEFT JOIN (SELECT * FROM t_parametersrc WHERE parameterType='Deliver_Store') as tp on tp.parameterCode=Ticket.delivery_store
sb.Append(" WHERE 1=1 ");
sb.AppendFormat(conditon.ToString());
sb.AppendFormat(" ORDER BY Ticket.ticket_id DESC ");
totalCount = 0;
try
{
if (tqQuery.IsPage)
{
System.Data.DataTable _dt = _access.getDataTable(strsql.ToString()+conditon.ToString());
if (_dt != null && Convert.ToInt32(_dt.Rows[0][0]) > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0][0]);
}
sb.AppendFormat(" limit {0},{1}", tqQuery.Start, tqQuery.Limit);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Deliver_Store");
List<TicketQuery> list = _access.getDataTableForObj<TicketQuery>(sb.ToString());
foreach (TicketQuery q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Deliver_Store" && m.ParameterCode == q.deliver_ys_type_id.ToString());
if (alist != null)
{
q.deliver_ys_type = alist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("TicketDao.GetTicketList-->" + ex.Message + sb.ToString(), ex);
}
}
public List<ProductOrderTemp> QuerySingle(ProductOrderTemp pot)
{
StringBuilder sb = new StringBuilder();
try
{
//LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType = 'Combo_Type') AS c ON c.parameterCode = p.combination
sb.AppendFormat(@"SELECT vb.brand_name,product_name,p.product_id,pi.item_id,p.combination,product_name AS combination_name, o.buy_num AS c_num,
FROM_UNIXTIME(p.product_start) AS product_start ,FROM_UNIXTIME(p.product_end) AS product_end,
CASE WHEN ignore_stock = 0 then '否' ELSE '是' END AS ignore_stock,
CASE WHEN shortage = 0 then '否' ELSE '是' END AS shortage FROM product p
INNER JOIN vendor_brand vb ON vb.brand_id = p.brand_id
INNER JOIN product_item pi ON pi.product_id = p.product_id
LEFT JOIN (
SELECT pi.item_id,CAST(SUM(od.buy_num) AS SIGNED) AS buy_num FROM product_item pi
INNER JOIN order_detail od ON pi.item_id = od.item_id
INNER JOIN order_slave os ON os.slave_id = od.slave_id
INNER JOIN order_master om ON om.order_id = os.order_id
WHERE od.detail_status= 4 AND order_createdate > UNIX_TIMESTAMP('{0}') AND od.item_mode = 0
GROUP BY pi.item_id
) o ON o.item_id = pi.item_id
WHERE p.product_status = 5 AND p.product_id >10000 AND p.brand_id IN({1}) AND p.combination = 1", pot.create_time.ToString("yyyy-MM-dd HH:MM:ss"), pot.brand_id);
//edit by zhuoqin0830w 2015/05/18
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type");
List<ProductOrderTemp> list = _dbAccess.getDataTableForObj<ProductOrderTemp>(sb.ToString());
foreach (ProductOrderTemp q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination.ToString());
if (alist != null)
{
q.combination_name = alist.parameterName;
}
else { q.combination_name = ""; }
}
return list;
//return _dbAccess.getDataTableForObj<ProductOrderTemp>(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductOrderTempDao-->QuerySingle" + ex.Message,ex); ;
}
}
/// <summary>
/// 查詢語句
/// </summary>
/// <param name="productId">商品id</param>
/// <returns>符合條件的集合</returns>
public List<ProdNameExtendCustom> Query(ProdNameExtendCustom pec,string ids)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT DISTINCT pe.rid, pm.product_id,pm.price_master_id,pm.product_name,pm.site_id,st.site_name,pm.user_level as level_name ,pe.product_prefix,pe.product_suffix,");
sb.Append(@"pm.user_level,pm.user_id,pe.event_start,pe.event_end,pe.kuser,pe.kdate,pe.flag,pu.`type`,pe.apply_id FROM price_master pm ");
sb.Append(" LEFT JOIN site st on st.site_id = pm.site_id ");
sb.Append(" LEFT JOIN product_extend pe ON pm.price_master_id = pe.price_master_id ");
//sb.Append(" LEFT JOIN (select parametername,parametercode from t_parametersrc where parametertype='userlevel') g on pm.user_level=g.parametercode");
sb.Append(" LEFT JOIN price_update_apply_history pu ON pu.apply_id = pe.apply_id AND (pu.`type`=2 OR pu.`type`=3)");
sb.Append(" WHERE 1=1 ");
if (ids != "")
{
sb.AppendFormat(" AND pm.product_id in ({0}) AND (pm.child_id = 0 OR pm.child_id =pm.product_id)", ids);
}
if (pec.Site_Id != 0)
{
sb.AppendFormat(" AND pm.site_id ={0} ", pec.Site_Id);
}
//edit by zhuoqin0830w 2015/05/18
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("UserLevel");
List<ProdNameExtendCustom> list = _access.getDataTableForObj<ProdNameExtendCustom>(sb.ToString());
foreach (ProdNameExtendCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.User_Level.ToString());
if (alist != null)
{
q.Level_Name = alist.parameterName;
}
}
return list;
//return _access.getDataTableForObj<ProdNameExtendCustom>(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("ProdNameExtendDao.Query-->" + ex.Message, ex);
}
}
public DataTable GetSecretInfoLog(SecretInfoLog query, out int totalCount)
{
try
{
DataTable _dt = _secretlogDao.GetSecretInfoLog(query, out totalCount);
ParametersrcDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("secret_type");
_dt.Columns.Add("type_name");
foreach (DataRow q in _dt.Rows)
{
var alist = parameterList.Find(m => m.ParameterType == "secret_type" && m.ParameterCode == q["type"].ToString());
if (alist != null)
{
q["type_name"] = alist.parameterName;
}
}
return _dt;
}
catch (Exception ex)
{
throw new Exception("SecretInfoLogMgr-->GetSecretInfoLog-->" + ex.Message, ex);
}
}
/// <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);
}
}
/// <summary>
/// 異常訂單列表
/// </summary>
/// <param name="query">查詢條件</param>
/// <returns></returns>
public DataTable ArrorOrderList(OrderMasterQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
try
{
sql.Append(@"SELECT om.order_id,om.order_status,FROM_UNIXTIME(om.order_createdate,'%Y/%m/%d') as order_createdate,od.detail_id,od.parent_id,od.pack_id,od.combined_mode,od.item_mode,count(od.detail_id) as cout ");
sqlCount.Append(@" from order_detail od LEFT JOIN order_slave os USING(slave_id) LEFT JOIN order_master om USING(order_id) ");
sqlCount.Append(" where od.item_mode=1 ");
//LEFT JOIN (select * from t_parametersrc where parameterType ='order_status') as os on os.parameterCode=om.order_status
//left join (select * from t_parametersrc where parameterType ='Combo_Type') as ct on ct.parameterCode=od.combined_mode
if (query.first_time != 0 && query.last_time != 0)
{
sqlCount.AppendFormat(" and om.order_createdate between '{0}' and '{1}' ", query.first_time, query.last_time);
}
sqlCount.AppendFormat(" GROUP BY om.order_id,od.parent_id,od.pack_id HAVING cout>1 ");
sql.Append(sqlCount.ToString());
totalCount = 0;
if (query.IsPage)
{
System.Data.DataTable _dt = _dbAccess.getDataTable(" select count(a.order_id) as totalCount from ( " + sql.ToString() + ")a");
if (_dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"].ToString());
}
sql.AppendFormat(" limit {0},{1};", query.Start, query.Limit);
}
DataTable _list = _dbAccess.getDataTable(sql.ToString());
if (_list.Rows.Count > 0)
{
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("order_status", "Combo_Type");
_list.Columns.Add("modeName");
_list.Columns.Add("remark");
foreach (DataRow dr in _list.Rows)
{
var alist = parameterList.Find(m => m.ParameterType == "order_status" && m.ParameterCode == dr["order_status"].ToString());
var blist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == dr["combined_mode"].ToString());
if (alist != null)
{
dr["remark"] = alist.remark;
}
if (blist != null)
{
dr["modeName"] = blist.parameterName;
}
}
}
return _list;
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->ArrorOrderList -->" + ex.Message + sql.ToString(), ex);
}
}
public string GetVendorBank(string code)
{
try
{
ParametersrcDao paraDao = new ParametersrcDao(connStr);
List<Parametersrc> list = paraDao.Query(new Parametersrc { ParameterCode = code, ParameterType = "BankBranchName" });
if (list != null && list.Count > 0)
{
return list[0].remark;
}
else
{
return "";
}
}
catch (Exception ex)
{
throw new Exception("VendorMgr-->GetVendorBank" + ex.Message, ex);
}
}
/// <summary>
/// 根據查詢條件匯出對應欄位
/// </summary>
/// <param name="query">查詢條件</param>
/// <param name="columns">對應欄位</param>
/// <returns></returns>
public MemoryStream ExportProductToExcel(Model.Query.QueryVerifyCondition query, int exportFlag, string cols, string fileName)
{
XDocument xml = XDocument.Load(fileName);//加载xml
Dictionary<string, string> columns = xml.Elements().Elements().ToDictionary(p => p.Attribute("key").Value, p => p.Value);//將xml轉換成Dictionary
query.IsPage = false;
string price_master_id = "";
switch (exportFlag)
{
case 1://庫存資料匯出(單一商品)
List<ProductItemCustom> productItems = _productDao.GetStockInfo(query);
return ExcelHelperXhf.ExportExcel(productItems, columns);//break;
case 2://商品資料匯出
case 3://商品價格匯出:權限控管
int total = 0;
List<QueryandVerifyCustom> items = _productDao.QueryByProSite(query, out total);
List<string> authority = exportFlag == 3 ? cols.Split(',').ToList() : null;
return ExcelHelperXhf.ExportExcel(items, columns, authority);
case 4:///子商品價格信息匯出
int sum = 0;
List<QueryandVerifyCustom> product = _productDao.QueryByProSite(query, out sum);///獲得根據條件查詢到的相關信息
foreach (var p in product)
{
price_master_id += p.price_master_id + ",";
}
if (price_master_id == "")///如果該字符串無值
{
return null;//return
}
price_master_id = price_master_id.Remove(price_master_id.Length - 1, 1);//否則刪去最後一個,
IPriceMasterImplMgr _priceMgr = new PriceMasterMgr(connectionStr);
List<PriceMasterCustom> listPirce = _priceMgr.GetExcelItemIdInfo(price_master_id);
IParametersrcImplDao _paramerDao = new ParametersrcDao(connectionStr);
List<Parametersrc> listParameter = _paramerDao.QueryParametersrcByTypes("price_status", "product_status");
foreach (PriceMasterCustom p in listPirce)
{
var listTemp = listParameter.Find(m => m.ParameterCode == p.price_status.ToString());
var listTemp2 = listParameter.Find(m => m.ParameterCode == p.product_status.ToString());
if (listTemp != null)
{
p.price_status_str = listTemp.parameterName;
}
if (listTemp2 != null)
{
p.product_status_str = listTemp2.parameterName;
}
}
return ExcelHelperXhf.ExportExcel(listPirce, columns);
case 5: //預購商品匯出
int totalYugou = 0;
List<QueryandVerifyCustom> itemsYugou = _productDao.QueryForPurchase_in_advance(query, out totalYugou);
DataTable dtnewYuGuo = new DataTable();//保存沒有過期的
DataTable dtoldYuGuo = new DataTable();//保存過期的
//產生列
dtnewYuGuo.Columns.Add("商品編號", typeof(String));
dtnewYuGuo.Columns.Add("商品名稱", typeof(String));
dtnewYuGuo.Columns.Add("品牌名稱", typeof(String));
dtnewYuGuo.Columns.Add("供應商名稱", typeof(String));
dtnewYuGuo.Columns.Add("商品類型", typeof(String));
dtnewYuGuo.Columns.Add("預購商品開始時間", typeof(String));
dtnewYuGuo.Columns.Add("預購商品結束時間", typeof(String));
dtnewYuGuo.Columns.Add("預計出貨時間", typeof(String));
dtnewYuGuo.Columns.Add("排程設定", typeof(String));
dtnewYuGuo.Columns.Add("庫存數", typeof(String));
dtnewYuGuo.Columns.Add("未出貨數量", typeof(String));
//複製
dtoldYuGuo = dtnewYuGuo.Clone();
foreach (QueryandVerifyCustom qcustom in itemsYugou)
{
string endtime = qcustom.purchase_in_advance_end_time;
if (DateTime.Parse(endtime == "" ? DateTime.Now.AddDays(1).ToString() : endtime) > DateTime.Now)
{
DataRow newrow = dtnewYuGuo.NewRow();
newrow[0] = qcustom.product_id;
newrow[1] = qcustom.product_name;
newrow[2] = qcustom.brand_name;
newrow[3] = qcustom.vendor_name_full;
newrow[4] = qcustom.combination;
newrow[5] = qcustom.purchase_in_advance_start_time;
newrow[6] = qcustom.purchase_in_advance_end_time;
newrow[7] = qcustom.expect_time_time;
newrow[8] = qcustom.schedule_name;
newrow[9] = qcustom.Item_Stock;
newrow[10] = qcustom.bnum;
dtnewYuGuo.Rows.Add(newrow);
}
else
{
DataRow oldrow = dtoldYuGuo.NewRow();
oldrow[0] = qcustom.product_id;
oldrow[1] = qcustom.product_name;
oldrow[2] = qcustom.brand_name;
oldrow[3] = qcustom.vendor_name_full;
oldrow[4] = qcustom.combination;
oldrow[5] = qcustom.purchase_in_advance_start_time;
oldrow[6] = qcustom.purchase_in_advance_end_time;
oldrow[7] = qcustom.expect_time_time;
oldrow[8] = qcustom.schedule_name;
oldrow[9] = qcustom.Item_Stock;
oldrow[10] = qcustom.bnum;
dtoldYuGuo.Rows.Add(oldrow);
}
}
List<string> NameList = new List<string>();
NameList.Add("有效期內的預購商品");
NameList.Add("過期的預購商品");
List<DataTable> Elist = new List<DataTable>();
Elist.Add(dtnewYuGuo);
Elist.Add(dtoldYuGuo);
List<bool> comName = new List<bool>();
comName.Add(true);
comName.Add(true);
return ExcelHelperXhf.ExportDTNoColumnsBySdy(Elist, NameList, comName);
default:
throw new Exception("unaccepted exportFlag!!!");
}
}
/// <summary>
/// 會計入賬匯出
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable OrderMasterExport(OrderMasterQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select oac.row_id, om.order_id,om.order_name,om.deduct_card_bonus,imr.invoice_date,'' as invoicedate,");
sql.AppendFormat(" SUM(imr.free_tax) as free_tax,SUM(imr.sales_amount) as sales_amount,SUM(imr.tax_amount) as tax_amount,SUM(imr.total_amount) imramount,");
sql.AppendFormat("om.money_cancel,om.money_return,om.delivery_name,om.order_amount,om.order_payment,'' as parameterName,om.order_createdate, '' as ordercreatedate,");
sql.AppendFormat("oac.account_collection_time,oac.account_collection_money,oac.poundage,oac.return_collection_time,oac.return_collection_money,oac.return_poundage,oac.remark,");
sql.AppendFormat(" oac.invoice_date_manual,oac.invoice_sale_manual,oac.invoice_tax_manual , ");
sql.AppendFormat(" '' as oacamount , '' as invoice_diff ");
sql.Append(" from order_master om ");
sql.Append(" left join order_account_collection oac on om.order_id = oac.order_id ");
sql.AppendFormat(" left join invoice_master_record imr on imr.order_id=om.order_id and invoice_attribute=1 ");
sql.AppendFormat(" where 1=1 ");
if (query.dateType == 1)
{
if (query.order_date_pay_startTime != DateTime.MinValue)
{
sql.AppendFormat(" and oac.account_collection_time>='{0}' ", query.order_date_pay_startTime.ToString("yyyy-MM-dd 00:00:00"));
}
if (query.order_date_pay_endTime != DateTime.MinValue)
{
sql.AppendFormat(" and oac.account_collection_time<='{0}' ", query.order_date_pay_endTime.ToString("yyyy-MM-dd 23:59:59"));
}
}
else if (query.dateType == 3)
{
if (query.first_time != 0)
{
sql.AppendFormat(" and om.order_createdate>='{0}' ", query.first_time);
}
if (query.last_time != 0)
{
sql.AppendFormat(" and om.order_createdate<='{0}' ", query.last_time);
}
}
else if (query.dateType == 4)
{
if (query.order_date_pay_startTime != DateTime.MinValue)
{
sql.AppendFormat(" and oac.return_collection_time>='{0}' ", query.order_date_pay_startTime.ToString("yyyy-MM-dd 00:00:00"));
}
if (query.order_date_pay_endTime != DateTime.MinValue)
{
sql.AppendFormat(" and oac.return_collection_time<='{0}' ", query.order_date_pay_endTime.ToString("yyyy-MM-dd 23:59:59"));
}
}
else if (query.dateType == 2)
{
if (query.first_time != 0)
{
sql.AppendFormat(" and imr.invoice_date>='{0}' ", query.first_time);
}
if (query.last_time != 0)
{
sql.AppendFormat(" and imr.invoice_date<='{0}' ", query.last_time);
}
}
else if (query.dateType == 5)
{
if (query.order_date_pay_startTime != DateTime.MinValue)
{
sql.AppendFormat(" and oac.invoice_date_manual>='{0}' ", query.order_date_pay_startTime.ToString("yyyy-MM-dd 00:00:00"));
}
if (query.order_date_pay_endTime != DateTime.MinValue)
{
sql.AppendFormat(" and oac.invoice_date_manual<='{0}' ", query.order_date_pay_endTime.ToString("yyyy-MM-dd 23:59:59"));
}
}
if (query.show_type == 1)
{
sql.AppendFormat(" and oac.row_id!='' ");
}
else if (query.show_type == 2)
{
sql.AppendFormat(" and ISNULL(oac.row_id) ");
}
if (query.invoice_type == 1)
{
sql.AppendFormat(" and (imr.invoice_id!='' or oac.invoice_date_manual!='' ) ");
}
else if (query.invoice_type == 2)
{
sql.AppendFormat(" and ISNULL(imr.invoice_id) and ISNULL(oac.invoice_date_manual) ");
}
if (query.Order_Id != 0)
{
sql.AppendFormat(" and om.order_id='{0}' ", query.Order_Id);
}
if (query.Order_Payment != 0)
{
sql.AppendFormat(" and om.order_payment='{0}' ", query.Order_Payment);
}
sql.AppendFormat(" GROUP BY om.order_id ");
sql.AppendFormat(" ORDER BY imr.invoice_date desc, om.order_id desc ");
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.GetAllKindType("payment");
DataTable dt = _dbAccess.getDataTable(sql.ToString());
Int64 order_creat = 0;
Int64 invoi_date = 0;
Int64 poun = 0;
Int64 coll = 0;
Int64 Rpoun = 0;
Int64 Rcoll = 0;
Int64 totalMoney = 0;
Int64 imramount = 0;
Int64 invoice = 0;
foreach (DataRow dr in dt.Rows)
{
var alist = parameterList.Find(m => m.ParameterCode == dr["order_payment"].ToString());
if (alist != null)
{
dr["parameterName"] = alist.parameterName;
}
invoi_date = 0;
if (Int64.TryParse(dr["invoice_date"].ToString(), out invoi_date))
{
dr["invoicedate"] = CommonFunction.GetNetTime(invoi_date).ToShortDateString();
}
order_creat = 0;
if (Int64.TryParse(dr["order_createdate"].ToString(), out order_creat))
{
dr["ordercreatedate"] = CommonFunction.GetNetTime(order_creat).ToShortDateString();
}
totalMoney = 0;
poun = 0;
if (!string.IsNullOrEmpty(dr["poundage"].ToString()))
{
if (Int64.TryParse(dr["poundage"].ToString(), out poun))
{
totalMoney += poun;
}
}
coll = 0;
if (!string.IsNullOrEmpty(dr["account_collection_money"].ToString()))
{
if (Int64.TryParse(dr["account_collection_money"].ToString(), out coll))
{
totalMoney += coll;
}
}
Rpoun = 0;
if (!string.IsNullOrEmpty(dr["return_poundage"].ToString()))
{
if (Int64.TryParse(dr["return_poundage"].ToString(), out Rpoun))
{
totalMoney += Rpoun;
}
}
Rcoll = 0;
if (!string.IsNullOrEmpty(dr["return_collection_money"].ToString()))
{
if (Int64.TryParse(dr["return_collection_money"].ToString(), out Rcoll))
{
totalMoney += Rcoll;
}
}
dr["oacamount"] = totalMoney;
imramount = 0;
invoice = 0;
if (!string.IsNullOrEmpty(dr["imramount"].ToString()))
{
if (Int64.TryParse(dr["imramount"].ToString(), out invoice))
{
imramount += Convert.ToInt64(dr["imramount"].ToString());
}
}
if (!string.IsNullOrEmpty(dr["invoice_sale_manual"].ToString()))
{
if (Int64.TryParse(dr["invoice_sale_manual"].ToString(), out invoice))
{
imramount += Convert.ToInt64(dr["invoice_sale_manual"].ToString());
}
}
if (!string.IsNullOrEmpty(dr["invoice_tax_manual"].ToString()))
{
if (Int64.TryParse(dr["invoice_tax_manual"].ToString(), out invoice))
{
imramount += Convert.ToInt64(dr["invoice_tax_manual"].ToString());
}
}
dr["imramount"] = imramount;
if (!string.IsNullOrEmpty(dr["oacamount"].ToString()) || !string.IsNullOrEmpty(dr["imramount"].ToString()) || !string.IsNullOrEmpty(dr["invoice_tax_manual"].ToString()) || !string.IsNullOrEmpty(dr["invoice_sale_manual"].ToString()))
{
dr["invoice_diff"] = totalMoney - imramount;//J=E-H
}
}
return dt;
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->OrderMasterExport -->" + ex.Message + sql.ToString(), ex);
}
}
public List<ElementDetailQuery> QueryAll(ElementDetailQuery query, out int totalCount)
{
try
{
query.Replace4MySQL();
StringBuilder TempCol = new StringBuilder("SELECT p.product_id,ap.element_type,ap.packet_status,p.product_name,p.product_status,ap.packet_name, bd.element_id,ap.element_type,");//c.parametername as element_type_name,
TempCol.Append(" bd.element_content,bd.packet_id,bd.element_name,bd.element_link_url,bd.element_link_mode,bd.element_remark,bd.element_sort,bd.element_status, bd.element_link_mode,");//d.parametername as element_linkmode,
TempCol.Append(" bd.element_start,bd.element_end,bd.element_createdate,bd.element_updatedate,bd.category_id,bd.category_name,bd.element_img_big ");
StringBuilder tempCount = new StringBuilder("select count(bd.element_id) as totalCount ");
StringBuilder mainSql = new StringBuilder(" FROM element_detail bd ");
mainSql.Append(" join area_packet ap on ap.packet_id=bd.packet_id");
mainSql.Append(" LEFT JOIN product p ON p.product_id = bd.element_content ");
// mainSql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_type') c on ap.element_type=c.parametercode");
// mainSql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_link_mode') d on bd.element_link_mode=d.parametercode");
StringBuilder condi = new StringBuilder();
condi.Append(" and element_status!=2 ");
if (query.packet_id != 0)
{
condi.AppendFormat(" and ap.packet_id={0} ", query.packet_id);
}
if (query.element_type != 0)
{
condi.AppendFormat(" and ap.element_type={0} ", query.element_type);
}
if (!string.IsNullOrEmpty(query.key))
{
condi.AppendFormat(" and ( bd.element_name like N'%{0}%' ", query.key);
condi.AppendFormat(" or bd.element_remark like N'%{0}%' ", query.key);
condi.AppendFormat(" or ap.packet_name like N'%{0}%' ", query.key);
condi.AppendFormat(" or bd.element_content like N'%{0}%') ", query.key);
}
if (!string.IsNullOrEmpty(query.searchcate))
{
condi.AppendFormat(" and bd.category_name like N'%{0}%' ", query.searchcate);
}
if (query.product_status != 0 && query.element_type == 3)
{
if (query.product_status == 5)
{
condi.Append(" and p.product_status=5 ");
}
else
{
condi.AppendFormat(" and p.product_status <> 5 ");
}
}
if (condi.Length > 0)
{
mainSql.Append(" where ");
mainSql.Append(condi.ToString().TrimStart().Remove(0, 3));
}
mainSql.AppendFormat(" order by bd.element_id desc ");
totalCount = 0;
if (query.IsPage)
{
sql.Append(tempCount.ToString() + mainSql.ToString());
System.Data.DataTable _dt = _access.getDataTable(sql.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
mainSql.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
IParametersrcImplDao _paradao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _paradao.QueryParametersrcByTypes("element_type", "element_link_mode");
List<ElementDetailQuery> list = _access.getDataTableForObj<ElementDetailQuery>(TempCol.ToString() + mainSql.ToString());
foreach (ElementDetailQuery q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "element_type" && m.ParameterCode == q.element_type.ToString());
var blist = parameterList.Find(m => m.ParameterType == "element_link_mode" && m.ParameterCode == q.element_link_mode.ToString());
if (alist != null)
{
q.element_type_name = alist.parameterName;
}
if (blist != null)
{
q.element_linkmode = blist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ElementDetailDao-->QueryAll-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// add by jiajun 2014/08/22
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<Model.Custom.QueryandVerifyCustom> QueryForStation(Model.Query.QueryVerifyCondition query, out int totalCount)
{
try
{
var regex = new Regex("^[0-9,]+$");
query.Replace4MySQL();
StringBuilder strCols = new StringBuilder("select a.product_id,b.brand_name,a.product_image,a.product_name,a.prod_sz,a.product_spec AS product_spec_id,");
strCols.Append("a.product_price_list,a.product_status as product_status_id,a.combination as combination_id,a.user_id ");
StringBuilder strTbls = new StringBuilder("from product a left join vendor_brand b on a.brand_id=b.brand_id ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_spec') d on a.product_spec=d.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') e on a.product_status=e.parametercode ");
StringBuilder strCondition = new StringBuilder("where 1=1 ");
if (query.brand_id != 0)
{
strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
}
if (!string.IsNullOrEmpty(query.cate_id))
{
strCondition.AppendFormat(" and a.cate_id in ('{0}')", GetStrbyCate_id(query.cate_id));
}
if (query.category_id != 0)
{//edit by hjiajun1211w 2014/08/08 父商品查詢
IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
string str = string.Empty;
GetAllCategory_id(category, query.category_id, ref str);
strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
}
if (query.combination != 0)
{
strCondition.AppendFormat(" and a.combination={0}", query.combination);
}
if (query.product_status != -1)
{
strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
}
if (query.freight != 0)
{
strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
}
if (query.mode != 0)
{
strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
}
if (query.tax_type != 0)
{
strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
}
//add by zhuoqin0830w 2015/03/11 已買斷商品的篩選功能
if (query.Prepaid != -1)
{
strCondition.AppendFormat(" and a.prepaid={0}", query.Prepaid);
}
if (!string.IsNullOrEmpty(query.date_type))
{
CheckCondition(query, "a", strCondition);
}
strCols.Append(",a.price_type AS price_type_id,a.product_freight_set AS product_freight_set_id,a.product_mode as product_mode_id,a.tax_type,a.product_sort,a.product_createdate,a.product_start,a.product_end ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') f on a.product_freight_set=f.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') g on a.product_mode=g.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='price_type') h on a.price_type=h.parametercode ");
//edit 2014/08/26
if (query.priceCondition == 2)
{
strTbls.Append("left join price_master l on a.product_id=l.product_id and l.site_id= 1 and l.user_level=1 and l.user_id=0 and (l.product_id=l.child_id or l.child_id=0) ");
strCondition.AppendFormat(" and ((a.combination not in(0,1) and a.price_type <>2) or (a.combination = 1 and l.same_price =1))");
}
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%')" : " and a.product_name like '%{0}%'", query.name_number);
}
string strCount = "select count(a.product_id) as totalCount " + strTbls.ToString() + strCondition.ToString();
System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
totalCount = 0;
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
strCondition.Append(" order by a.product_id desc ");
if (query.IsPage)
{
strCondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "product_freight", "product_mode", "Price_Type");
List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
foreach (QueryandVerifyCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_spec" && m.ParameterCode == q.product_spec_id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
var dlist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.product_freight_set_id.ToString());
var elist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == q.product_mode_id.ToString());
var flist = parameterList.Find(m => m.ParameterType == "Price_Type" && m.ParameterCode == q.price_type_id.ToString());
if (alist != null)
{
q.combination = alist.parameterName;
}
if (blist != null)
{
q.product_spec = blist.parameterName;
}
if (clist != null)
{
q.product_status = clist.parameterName;
}
if (dlist != null)
{
q.product_freight_set = dlist.parameterName;
}
if (elist != null)
{
q.product_mode = elist.parameterName;
}
if (flist != null)
{
q.price_type = flist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.QueryByProSite-->" + ex.Message, ex);
}
}
/// <summary>
/// 查詢符合條件的ShippintCarrior集合
/// </summary>
/// <param name="sc">查詢的條件</param>
/// <returns>符合條件的集合</returns>
public List<ShippingCarriorCustom> QueryAll(ShippingCarrior sc, out int totalCount)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT sc.rid,sc.delivery_store_id,sc.freight_big_area,sc.freight_type,sc.delivery_freight_set,sc.active,sc.charge_type,sc.shipping_fee,sc.return_fee,
sc.size_limitation,sc.length,sc.width,sc.height,sc.weight,sc.pod,sc.note,ds.store_name FROM shipping_carrior sc ");
sb.Append(@"
LEFT JOIN delivery_store ds ON ds.delivery_store_id = sc.delivery_store_id
");
sb.AppendFormat(" WHERE 1=1 ");
if (sc.Rid != 0)
{
sb.AppendFormat(" AND sc.Rid ={0}", sc.Rid);
}
if (sc.Delivery_store_id != 0)
{
sb.AppendFormat(" AND sc.delivery_store_id ={0}", sc.Delivery_store_id);
}
if (sc.Freight_big_area != 0)
{
sb.AppendFormat(" AND sc.freight_big_area ={0}", sc.Freight_big_area);
}
if (sc.Freight_type != 0)
{
sb.AppendFormat(" AND sc.freight_type ={0}", sc.Freight_type);
}
if (sc.Delivery_freight_set != 0)
{
sb.AppendFormat(" AND sc.delivery_freight_set", sc.Delivery_freight_set);
}
if (sc.Active != 0)
{
sb.AppendFormat(" AND sc.active", sc.Active);
}
totalCount = 0;
System.Data.DataTable _dt = _dbAccess.getDataTable("SELECT COUNT(Rid) AS totalCount from shipping_carrior ");
if (_dt != null)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(strConn);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("freight_big_area", "freight_type");
List<ShippingCarriorCustom> list = _dbAccess.getDataTableForObj<ShippingCarriorCustom>(sb.ToString());
foreach (ShippingCarriorCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "freight_big_area" && m.ParameterCode == q.Freight_big_area.ToString());
var blist = parameterList.Find(m => m.ParameterType == "freight_type" && m.ParameterCode == q.Freight_type.ToString());
if (alist != null)
{
q.Area_name = alist.parameterName;
}
if (blist != null)
{
q.Freight_type_Name = blist.parameterName;
}
}
return _dbAccess.getDataTableForObj<ShippingCarriorCustom>(sb.ToString());
}
//add by xiangwang0413w 2014/07/02
public List<Model.Custom.PriceMasterCustom> QuerySitePriceOption(uint proId, uint channelId)
{
try
{
//left join t_parametersrc c on a.user_level=c.parameterCode and parameterType='UserLevel'
string strSql = string.Format(@"select distinct a.price_master_id,concat(!isnull(e.price_master_id) ,b.site_name) as site_name,a.user_level,
a.user_level as user_level_name,a.user_id,d.user_email from price_master a
left join users d on a.user_id=d.user_id left join site b on a.site_id=b.site_id
left join product_item_map e on a.price_master_id=e.price_master_id and e.channel_id={0}
where a.product_id={1} order by e.price_master_id desc ;", channelId, proId);
//edit by zhuoqin0830w 2015/05/18
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("UserLevel");
List<PriceMasterCustom> list = _dbAccess.getDataTableForObj<Model.Custom.PriceMasterCustom>(strSql);
foreach (PriceMasterCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.user_level.ToString());
if (alist != null)
{
q.user_level_name = alist.parameterName;
}
}
return list;
//return _dbAccess.getDataTableForObj<Model.Custom.PriceMasterCustom>(strSql);
}
catch (Exception ex)
{
throw new Exception("QuerySitePriceOption(uint proId)-->" + ex.Message, ex);
}
}
/// <summary>
/// 根據商品ID獲得商品信息
/// productID:商品ID
/// add by wangwei0216w 2014/8/25
/// </summary>
public List<Model.Custom.QueryandVerifyCustom> GetProductInfoByID(string productIds)
{
StringBuilder sb = new StringBuilder(@"select a.product_image ,a.product_id ,b.brand_name,a.product_name ,a.prod_sz,a.combination AS combination_id,a.combination as combination_id,
a.price_type as price_type_id,a.product_status AS product_status_id,f.price ,f.cost ,f.event_price,f.event_cost,a.prepaid from product a
left join vendor_brand b on a.brand_id=b.brand_id
left join price_master f on f.product_id=a.product_id and f.site_id=1 and f.user_level=1 and f.user_id=0 and (f.product_id=f.child_id or child_id=0)");
sb.AppendFormat(" where a.product_id in({0})", productIds);
List<Model.Custom.QueryandVerifyCustom> list1 = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(sb.ToString());
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "Price_Type", "product_status");
foreach (QueryandVerifyCustom q in list1)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "Price_Type" && m.ParameterCode == q.price_type_id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
if (alist != null)
{
q.combination = alist.parameterName;
}
if (blist != null)
{
q.price_type = blist.parameterName;
}
if (clist != null)
{
q.product_status = clist.parameterName;
}
}
return list1;
}
/// <summary>
/// 類別商品修改中獲取所有商品信息
/// add by shuangshuang0420j 20141020 10:00
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<Model.Custom.ProductDetailsCustom> GetAllProList(Model.Query.ProductQuery query, out int totalCount)
{
StringBuilder stb = new StringBuilder();
StringBuilder stbCondi = new StringBuilder();
StringBuilder stbWhere = new StringBuilder();
query.Replace4MySQL();
try
{
stb.Append(@"select DISTINCT(a.product_id), a.brand_id,b.brand_name,a.product_name,a.product_image,");
stb.Append("a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.combination AS combination_id,");
stb.Append("a.product_keywords,a.product_status AS product_status_id, a.process_type AS process_type_id,a.combination as product_type,sale_status ");
stbCondi.Append(" from (select product_id,brand_id,product_name,product_freight_set,product_mode,process_type,product_type,product_keywords,sale_status,product_status,combination,product_image from product where product_id >= 10000 and product_status not in(0,20) ) a");//0:新建立商品 20：供應商新建商品
stbCondi.Append(" left join vendor_brand b on a.brand_id=b.brand_id ");
//stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') c on a.product_freight_set=c.parametercode");
//stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') d on a.product_mode=d.parametercode");
//stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='process_type') g on a.process_type=g.parametercode");
//stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_type') h on a.product_type=h.parametercode");
//stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='sale_status') i on a.sale_status=i.parametercode");
//stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') f on a.product_status = f.parametercode");
if (query.isjoincate)
{
stb.Append(",pcs.category_id,pc.category_name ");//在product_category_set中加入sort欄位控制該類別下商品在前台的排序 add by shuangshuang0420j 20141105 15:00
stbCondi.Append(" right join product_category_set pcs on pcs.product_id=a.product_id ");
stbCondi.Append(" left join product_category pc on pc.category_id=pcs.category_id");//查詢類別中商品時查詢該商品的類別
}
else
{
if (query.category_id != 0)//查詢所有商品時按類別查詢
{
stb.Append(",pcs.category_id,pc.category_name ");
stbCondi.Append(" left join product_category_set pcs on pcs.product_id=a.product_id ");
stbCondi.Append(" left join product_category pc on pc.category_id=pcs.category_id");
}
}
// stbCondi.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') e on a.combination=e.parametercode ");
// stbWhere.AppendFormat(" where a.product_status not in(0,20) ");//0:新建立商品 20：供應商新建商品
if (query.Product_Status != 0 && query.Product_Status != 20)
{
stbWhere.AppendFormat(" and a.product_status ='{0}' ", query.Product_Status);
}
if (query.category_id != 0)
{
stbWhere.AppendFormat(" and pcs.category_id='{0}'", query.category_id);
}
else if (!string.IsNullOrEmpty(query.categoryArry))
{
stbWhere.AppendFormat("and pcs.category_id in ({0})", query.categoryArry);
}
if (query.Product_Id != 0)
{
stbWhere.AppendFormat(" and a.product_id = '{0}'", query.Product_Id);
}
if (!string.IsNullOrEmpty(query.Product_Id_In))
{
stbWhere.AppendFormat(" and a.product_id in ({0}) ", query.Product_Id_In);
}
if (!string.IsNullOrEmpty(query.Product_Name))
{
stbWhere.AppendFormat(" and a.product_name like N'%{0}%'", query.Product_Name);
}
if (!string.IsNullOrEmpty(query.siteStr))
{
stbCondi.Append(" left join price_master pm on a.product_id=pm.product_id and(pm.product_id=pm.child_id or pm.child_id=0) ");
stbCondi.Append(" left join site s on pm.site_id=s.site_id ");
stbWhere.Append(" and s.site_id is not null ");
stbWhere.AppendFormat(" and s.site_id in ({0})", query.siteStr);
}
if (query.Brand_Id != 0)
{
stbWhere.AppendFormat(" and a.brand_id ='{0}'", query.Brand_Id);
}
else if (!string.IsNullOrEmpty(query.brandArry))
{
stbWhere.AppendFormat(" and a.brand_id in ({0})", query.brandArry);
}
if (stbWhere.Length != 0)
{
stbCondi.Append(" WHERE ");
stbCondi.Append(stbWhere.ToString().TrimStart().Remove(0, 3));
}
string strCount = "select count(a.product_id) as totalCount " + stbCondi.ToString();
System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
totalCount = 0;
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
stbCondi.Append(" order by a.product_id desc ");
if (query.IsPage)
{
stbCondi.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
stb.Append(stbCondi.ToString());
// return _dbAccess.getDataTableForObj<Model.Custom.ProductDetailsCustom>(stb.ToString());
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_freight", "product_mode", "process_type", "product_type", "sale_status", "product_status", "Combo_Type");
List<Model.Custom.ProductDetailsCustom> listStore = _dbAccess.getDataTableForObj<Model.Custom.ProductDetailsCustom>(stb.ToString());
foreach (ProductDetailsCustom list in listStore)
{
var alist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == list.product_freight_set_id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == list.product_mode_id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "process_type" && m.ParameterCode == list.process_type_id.ToString());
var dlist = parameterList.Find(m => m.ParameterType == "product_type" && m.ParameterCode == list.product_type.ToString());
var elist = parameterList.Find(m => m.ParameterType == "sale_status" && m.ParameterCode == list.sale_status.ToString());
var flist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == list.product_status_id.ToString());
var glist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == list.combination_id.ToString());
if (alist != null)
{
list.product_freight_set = alist.parameterName;
}
if (blist != null)
{
list.product_mode = blist.parameterName;
}
if (clist != null)
{
list.process_type = clist.parameterName;
}
if (dlist != null)
{
list.product_kind = dlist.parameterName;
}
if (elist != null)
{
list.sale_name = elist.parameterName;
}
if (flist != null)
{
list.product_status = flist.parameterName;
}
if (glist != null)
{
list.combination = glist.parameterName;
}
}
return listStore;
}
catch (Exception ex)
{
throw new Exception("ProductDao.GetAllProList-->" + ex.Message, ex);
}
}
//查詢商品詳細信息
public Model.Custom.ProductDetailsCustom ProductDetail(Model.Product query)
{
try
{
StringBuilder stb = new StringBuilder("select a.combination as product_type,b.brand_name,a.product_name,a.prod_sz,a.product_sort,a.product_vendor_code,a.product_start,a.product_end,a.expect_time,");
stb.Append("a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.tax_type,a.combination AS combination_id,page_content_1,page_content_2,page_content_3,product_buy_limit,");
stb.Append(@"product_keywords,a.fortune_quota,fortune_freight,a.product_status AS product_status_id,a.price_type,a.expect_msg,a.create_channel,a.process_type AS process_type_id, a.product_type AS product_kind_id ,a.sale_status,a.show_listprice,a.show_in_deliver,a.prepaid,k.course_name,j.course_id,a.safe_stock_amount,a.deliver_days,a.min_purchase_amount,a.extra_days,purchase_in_advance,purchase_in_advance_start,purchase_in_advance_end
from product a");// edit by zhuoqin0830w 增加5個欄位 ,p.safe_stock_amount,p.deliver_days,p.min_purchase_amount,p.extra_days
stb.Append(" left join vendor_brand b on a.brand_id=b.brand_id ");
//stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') c on a.product_freight_set=c.parametercode");
//stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') d on a.product_mode=d.parametercode");
//stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='process_type') g on a.process_type=g.parametercode");
//stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_type') h on a.product_type=h.parametercode");
//stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='sale_status') i on a.sale_status=i.parametercode");
//stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') f on a.product_status = f.parametercode");
//stb.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') e on a.combination=e.parametercode ");
stb.Append("left join course_product j on a.product_id=j.product_id left join course k on j.course_id=k.course_id ");
stb.Append(" where 1=1 ");
if (query.Product_Id != 0)
{
stb.AppendFormat(" and a.product_id={0}", query.Product_Id);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_freight", "product_mode", "process_type", "product_type", "sale_status", "product_status", "Combo_Type");
Model.Custom.ProductDetailsCustom list = _dbAccess.getSinggleObj<Model.Custom.ProductDetailsCustom>(stb.ToString());
var alist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == list.product_freight_set_id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == list.product_mode_id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "process_type" && m.ParameterCode == list.process_type_id.ToString());
var dlist = parameterList.Find(m => m.ParameterType == "product_type" && m.ParameterCode == list.product_kind_id.ToString());
var elist = parameterList.Find(m => m.ParameterType == "sale_status" && m.ParameterCode == list.sale_status.ToString());
var flist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == list.product_status_id.ToString());
var glist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == list.combination_id.ToString());
if (alist != null)
{
list.product_freight_set = alist.parameterName;
}
if (blist != null)
{
list.product_mode = blist.parameterName;
}
if (clist != null)
{
list.process_type = clist.parameterName;
}
if (dlist != null)
{
list.product_kind = dlist.parameterName;
}
if (elist != null)
{
list.sale_name = elist.parameterName;
}
if (flist != null)
{
list.product_status = flist.parameterName;
}
if (glist != null)
{
list.combination = glist.parameterName;
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.ProductDetail-->" + ex.Message, ex);
}
}
public List<Model.Custom.QueryandVerifyCustom> QueryByProSite(string priceMasterIds)
{
try
{
string sqlStr = string.Format(@"select f.price_master_id,a.product_id,b.brand_name,a.product_image,a.product_name,a.prod_sz,a.combination AS combination_id,
a.product_spec AS product_spec_id,a.product_price_list,a.product_status AS product_status_id,a.product_status as product_status_id,a.price_type as price_type_id,
a.combination as combination_id,a.user_id ,f.user_id as master_user_id,g.site_name,f.site_id,f.user_level as level,
f.price_status AS price_status_id,f.price,f.cost,f.event_price,f.event_cost,f.event_start,f.event_end,a.prepaid from product a left join vendor_brand b on a.brand_id=b.brand_id
left join price_master f on a.product_id=f.product_id and(f.product_id=f.child_id or f.child_id=0)
left join site g on f.site_id=g.site_id
where f.price_master_id in({0}) and f.site_id is not null order by a.product_id desc", priceMasterIds);
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "UserLevel", "price_status", "Price_Type");
List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(sqlStr);
foreach (QueryandVerifyCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_spec" && m.ParameterCode == q.product_spec_id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
var dlist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.level.ToString());
var elist = parameterList.Find(m => m.ParameterType == "price_status" && m.ParameterCode == q.price_status_id.ToString());
var flist = parameterList.Find(m => m.ParameterType == "Price_Type" && m.ParameterCode == q.price_type_id.ToString());
if (alist != null)
{
q.combination = alist.parameterName;
}
if (blist != null)
{
q.product_spec = blist.parameterName;
}
if (clist != null)
{
q.product_status = clist.parameterName;
}
if (dlist != null)
{
q.user_level = dlist.parameterName;
}
if (elist != null)
{
q.price_status = elist.parameterName;
}
if (flist != null)
{
q.price_type = flist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.QueryByProSite-->" + ex.Message, ex);
}
}
/// <summary>
/// 根據cate_id獲取相應的sql語句字段
/// </summary>
/// add by wangwei0216w 2014/10/9
/// <returns>sql語句</returns>
public string GetStrbyCate_id(string cate_id)
{
IParametersrcImplDao _parametersrcDao = new ParametersrcDao(connStr);
string str = "";
List<Parametersrc> paras = _parametersrcDao.Query(new Parametersrc() { ParameterType = "product_cate" });
var subParas = paras.FindAll(p => p.TopValue == cate_id);
if (subParas.Count == 0)
{
str += cate_id + "','";
}
else
{
subParas.ForEach(p => str += p.ParameterCode + "','");
}
return str.Remove(str.Length - 3, 3);
}
/// <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);
}
}
public List<VendorAccountCustom> VendorAccountDetailExport(VendorAccountDetailQuery query)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
_iupc = new IupcDao(connStr);
try
{
// sql.AppendFormat(" select p.tax_type, vad.slave_id,vad.vendor_id,vad.order_id,vad.creditcard_1_percent,vad.creditcard_3_percent,vad.sales_limit, ");
sql.AppendFormat(" select vad.slave_id,vad.vendor_id,vad.order_id,vad.sales_limit, ");
sql.AppendFormat(" vad.bonus_percent,vad.freight_low_limit,vad.freight_low_money,vad.freight_normal_limit,vad.freight_normal_money, ");
sql.AppendFormat("vad.freight_return_normal_money,vad.product_money,vad.product_cost,vad.money_creditcard_1,vad.money_creditcard_3,");
sql.AppendFormat("vad.freight_delivery_low,vad.freight_delivery_normal,vad.freight_return_low, ");
//sql.AppendFormat("vad.freight_return_normal,vad.account_amount,FROM_UNIXTIME (vad.account_date,'%Y/%m/%d') account_date,vad.gift,vad.deduction,vad.bag_check_money,vad.freight_return_low_money,imr.free_tax,imr.total_amount,imr.tax_amount, ");
sql.AppendFormat("vad.freight_return_normal,vad.account_amount,account_date,vad.gift,vad.deduction,vad.bag_check_money,vad.freight_return_low_money,imr.free_tax,imr.total_amount,imr.tax_amount, ");
sql.AppendFormat("od.detail_id,od.slave_id,od.item_id,od.item_vendor_id,od.item_mode,od.product_freight_set,od.product_mode,od.product_name,od.product_spec_name,od.single_cost,od.deduct_account,od.parent_id,");
//sql.AppendFormat("od.single_price,od.buy_num,od.event_cost,od.single_money,od.detail_status,FROM_UNIXTIME (os.slave_date_delivery,'%Y/%m/%d') slave_date_delivery,os.slave_date_close,om.order_payment,");
sql.AppendFormat("od.single_price,od.buy_num,od.parent_num,od.event_cost,od.single_money,od.detail_status, slave_date_delivery,os.slave_date_close,om.order_payment,");
sql.AppendFormat(" od.deduct_bonus,od.deduct_welfare,od.deduct_happygo_money, ");
//sql.AppendFormat(" FROM_UNIXTIME( om.order_createdate,'%Y/%m/%d') order_createdate,om.note_admin,od.bag_check_money as od_bag_check_money,tp.parameterName,tp1.parameterName as product_freight,tp2.remark order_status_name ");
sql.AppendFormat(" order_createdate,om.note_admin,od.bag_check_money as od_bag_check_money ");
sqlfrom.AppendFormat(" from order_detail od left join order_slave os on od.slave_id = os.slave_id ");
sqlfrom.AppendFormat("left join vendor_account_detail vad on vad.slave_id = od.slave_id and vad.vendor_id = od.item_vendor_id ");
sqlfrom.AppendFormat("left join order_master om on vad.order_id = om.order_id ");
//sqlfrom.AppendFormat("left join vendor v ON vad.vendor_id = v.vendor_id ");
// sqlfrom.AppendFormat("left join product_item pi ON pi.item_id = od.item_id ");
//sqlfrom.AppendFormat("left join product p ON p.product_id = pi.product_id ");
sqlfrom.AppendFormat("left join invoice_master_record imr on om.order_id=imr.order_id and imr.tax_type=1 and invoice_attribute=1 ");
// sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='payment') tp on om.order_payment = tp.parameterCode ");
//sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='product_freight') tp1 on od.product_freight_set = tp1.parameterCode ");
//sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='order_status') tp2 on od.detail_status = tp2.parameterCode ");
sqlfrom.AppendFormat(" where 1=1 and vad.vendor_id = {0} and vad.account_date >= {1} and vad.account_date <= {2} and od.detail_status <> 89 ", query.vendor_id, query.search_start_time, query.search_end_time);
sqlfrom.AppendFormat(" order by account_date asc, order_id asc,vad.slave_id ASC, od.item_mode asc ");
sql.AppendFormat(sqlfrom.ToString());
//return _access.getDataTable(sql.ToString());
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("payment", "product_freight", "order_status");
List<VendorAccountCustom> list = _access.getDataTableForObj<Model.Custom.VendorAccountCustom>(sql.ToString());
IProductItemImplDao _itemDao = new ProductItemDao(connStr);
IVendorImplDao _vendordao = new VendorDao(connStr);
foreach (VendorAccountCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "payment" && m.ParameterCode == q.Order_Payment.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.Product_Freight_Set.ToString());
var clist = parameterList.Find(m => m.ParameterType == "order_status" && m.ParameterCode == q.Detail_Status.ToString());
if (alist != null)
{
q.paymentname = alist.parameterName;
}
if (blist != null)
{
q.product_freight = blist.parameterName;
}
if (clist != null)
{
q.order_status_name = clist.remark;
}
Product p = _itemDao.GetTaxByItem(Convert.ToUInt32(q.Item_Id));
DataTable dt = _vendordao.GetVendorDetail(" and vendor_id=" + q.Item_Vendor_Id + " ");
if (p != null)
{
q.tax_type = p.Tax_Type;
}
if (dt.Rows.Count != 0)
{
q.creditcard_1_percent = Convert.ToUInt32(dt.Rows[0]["creditcard_1_percent"].ToString());
// q.creditcard_3_percent = Convert.ToUInt32(dt.Rows[0]["creditcard_3_percent"].ToString());
}
q.accountdate = Common.CommonFunction.GetNetTime(q.account_date);
q.slavedate_delivery = Common.CommonFunction.GetNetTime(q.slave_date_delivery);
q.ordercreatedate = Common.CommonFunction.GetNetTime(q.Order_Createdate);
q.upc_id = _iupc.Getupc(q.Item_Id.ToString(), "1");
}
return list;
}
catch (Exception ex)
{
throw new Exception("VendorAccountMonthDao-->VendorAccountDetailExport-->" + sql.ToString() + ex.Message, ex);
}
}
public string ImportVendorBank(DataTable dt)
{
try
{
string result = "";
ArrayList _list = new ArrayList();
string user_email = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_email;
ParametersrcDao _paraDao = new ParametersrcDao(connStr);
string faCode = ""; int topValue = 0;
foreach (DataRow dr in dt.Rows)
{
Regex reg = new Regex("^[0-9]{7}$");
if (reg.IsMatch(dr[0].ToString()))
{
string sumCode = dr[0].ToString().Substring(0, 3);
var blist = _paraDao.Query(new Parametersrc { ParameterType = "BankBranchName", ParameterCode = dr[0].ToString() });
if (blist == null || blist.Count == 0)//不存在則保存總行數據
{
//查看總行是否已存在
if (faCode != sumCode)
{
var alist = _paraDao.Query(new Parametersrc { ParameterType = "BankName", ParameterCode = sumCode });
if (alist == null || alist.Count == 0)//不存在則保存總行數據
{
Parametersrc para = new Parametersrc();
para.ParameterType = "BankName";
para.ParameterCode = sumCode;
para.parameterName = dr[1].ToString().Split('-')[0].ToString();
para.remark = para.parameterName;
para.Kdate = DateTime.Now;
para.Kuser = user_email;
para.Used = 1;
_list.Add(_paraDao.Save(para));
}
faCode = sumCode;
}
}
else
{
result += dr[0].ToString() + "有重複匯入,";
break;
}
}
else
{
result += dr[0].ToString() + ',';
}
}
if (string.IsNullOrEmpty(result))//匯入數據無異常
{
//執行保存總行信息的事務
if (_mysqlDao.ExcuteSqlsThrowException(_list))
{
_list.Clear();
foreach (DataRow dr in dt.Rows)
{
string sumCode = dr[0].ToString().Substring(0, 3);
//查看總行是否已存在
if (faCode != sumCode)
{
topValue = _paraDao.Query(new Parametersrc { ParameterCode = sumCode, ParameterType = "BankName" }).FirstOrDefault().Rowid;
faCode = sumCode;
}
Parametersrc paraBranch = new Parametersrc();
paraBranch.ParameterType = "BankBranchName";
paraBranch.ParameterCode = dr[0].ToString();
paraBranch.parameterName = dr[1].ToString().Split('-')[1].ToString();
paraBranch.remark = dr[1].ToString();
paraBranch.Kdate = DateTime.Now;
paraBranch.Kuser = user_email;
paraBranch.Used = 1;
paraBranch.TopValue = topValue.ToString();
_list.Add(_paraDao.Save(paraBranch));
}
_mysqlDao.ExcuteSqlsThrowException(_list);
}
}
return result.TrimEnd(',');
}
catch (MySqlException ex)
{
throw new Exception(ex.Number.ToString() + ":VendorMgr-->ImportVendorBank-->" + ex.Message, ex);
}
catch (Exception ex)
{
throw new Exception("VendorMgr-->ImportVendorBank" + ex.Message, ex);
}
}
public List<Model.Custom.QueryandVerifyCustom> QueryByProSite(Model.Query.QueryVerifyCondition query, out int totalCount)
{
try
{
var regex = new Regex("^[0-9,]+$");
query.Replace4MySQL();
StringBuilder strCols = new StringBuilder("select a.product_id,b.brand_name,a.product_image,a.prod_sz,a.combination AS combination_id,a.product_spec AS product_spec_id,");
strCols.Append("a.product_price_list,a.sale_status AS sale_status_id,v.vendor_name_full,v.vendor_name_simple,v.erp_id,a.product_status as product_status_id,a.user_id, a.create_channel,a.prepaid,a.bag_check_money,a.off_grade ");//添加 失格欄位 a.off_grade add by zhuoqin0830w 2015/06/30
//add by wangwei 2014/9/29 添加a.create_channel字段
strCols.Append(",a.purchase_in_advance_start,a.purchase_in_advance_end,a.expect_time,a.outofstock_days_stopselling,prr.create_time as 'outofstock_create_time' ");//添加預購商品開始時間 ,結束時間 guodong1130w 2015/9/16
StringBuilder strTbls = new StringBuilder("from product a left join vendor_brand b on a.brand_id=b.brand_id ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_spec') d on a.product_spec=d.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') e on a.product_status=e.parametercode ");
//strTbls.Append(" LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='sale_status') sa ON a.sale_status = sa.parametercode "); //add by wwei0216w 2015/02/05
//add by wwei 0216w 2015/5/18
strTbls.Append(" LEFT JOIN vendor v ON v.vendor_id = b.vendor_id "); //add by wwei0216w 2015/02/06
strTbls.Append(" LEFT JOIN product_remove_reason prr ON a.product_id = prr.product_id "); //add by wwei0216w 2015/02/06
StringBuilder strCondition = new StringBuilder("where 1=1 ");
if (query.brand_id != 0)
{
strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
}
//庫存分類 edit by xiangwang0413w 2014/11/24
if (query.StockStatus != 0)// 1.庫存為0還可販售 2.補貨中停止販售 3.庫存數<1
{
switch (query.StockStatus)
{
case 1://1.庫存為0還可販售
strCondition.Append(" and a.combination=1 and a.ignore_stock=1 ");
break;
case 2:
strCondition.Append(" and a.shortage=1");//2.補貨中停止販售
break;
case 3:
strCondition.Append(" and a.combination=1 and a.product_id in (select distinct product_id from product_item pi where item_stock <1 and pi.product_id=a.product_id)");//3.庫存數<1
break;
default:
throw new Exception("unaccepted StockStatus");
}
}
if (!string.IsNullOrEmpty(query.cate_id))
{
strCondition.AppendFormat(" and a.cate_id in ('{0}')", GetStrbyCate_id(query.cate_id));
}
if (query.category_id != 0)
{//edit by hjiajun1211w 2014/08/08 父商品查詢
IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
string str = string.Empty;
GetAllCategory_id(category, query.category_id, ref str);
strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
}
if (query.combination != 0)
{
if (query.combination == 1 && query.outofstock_days_stopselling > 0)// add by dongya 2015/10/22
{
strCondition.AppendFormat(" and a.combination={0} and a.outofstock_days_stopselling >= '{1}' ", query.combination, query.outofstock_days_stopselling);
}
else
{
strCondition.AppendFormat(" and a.combination={0}", query.combination);
}
}
if (query.product_status != -1)
{
strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
}
if (query.product_type != -1)
{
strCondition.AppendFormat(" and a.product_type={0}", query.product_type);
}
if (query.freight != 0)
{
strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
}
if (query.mode != 0)
{
strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
}
if (query.tax_type != 0)
{
strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
}
//add by zhuoqin0830w 2015/03/11 已買斷商品的篩選功能
if (query.Prepaid != -1)
{
strCondition.AppendFormat(" and a.prepaid={0}", query.Prepaid);
}
//add by zhuoqin0830w 2015/06/30 失格商品篩選
if (query.off_grade != 0)
{
strCondition.AppendFormat(" and a.off_grade={0} ", query.off_grade);
}
//add by guodong1130w 2015/09/17 預購商品
if (query.purchase_in_advance != 0)
{
strCondition.AppendFormat(" and a.purchase_in_advance={0} ", query.purchase_in_advance);
}
if (!string.IsNullOrEmpty(query.date_type))
{
CheckCondition(query, "a", strCondition);
}
if (query.price_check) //價格進階搜尋條件
{
strCols.Append(",f.product_name,f.price_master_id,f.user_id as master_user_id,g.site_name,f.site_id,f.user_level as level,f.price_status AS price_status_id,f.price,f.cost,f.event_price,f.event_cost,f.event_start,f.event_end,a.prod_classify ");//add by wwei0216w 添加f.cost:成本 //添加館別欄位 eidt by zhuoqin0830w 2015/03/05
strTbls.Append("left join price_master f on a.product_id=f.product_id and(f.product_id=f.child_id or f.child_id=0) ");
strTbls.Append("left join site g on f.site_id=g.site_id ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='userlevel') h on f.user_level=h.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='price_status') i on f.price_status=i.parametercode ");
strCondition.Append(" and f.site_id is not null ");
if (!query.IsPage) //edit by wangwei0216w 當價格條件被選中,並且不分頁,證明為.商品價格匯出 拼接以下條件
{
//strTbls.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') k on a.product_freight_set=k.parametercode ");
//strTbls.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') l on a.product_mode=l.parametercode ");
strCols.Append(" ,a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.tax_type,a.product_start,a.product_end ");
strCondition.Append(" and a.product_id > 10000");//匯出時不匯出大於10000的商品
}
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or f.product_name like '%{0}%' or a.product_id in(select distinct product_id from product_item where item_id in({0})))" : " and f.product_name like '%{0}%'", query.name_number);//add by zhuoqin0830w 2015/03/30 添加商品細項編號
}
if (query.site_id != 0)
{
strCondition.AppendFormat(" and f.site_id={0}", query.site_id);
}
if (query.user_level != 0)
{
strCondition.AppendFormat(" and f.user_level={0}", query.user_level);
}
if (query.price_status != 0)
{
strCondition.AppendFormat(" and f.price_status={0}", query.price_status);
}
}
else
{
strCols.Append(",a.product_name,a.price_type AS price_type_id,a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.tax_type,a.product_sort,a.product_createdate,a.product_start,a.product_end,a.prod_classify ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') f on a.product_freight_set=f.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') g on a.product_mode=g.parametercode ");
//strTbls.Append("left join (select parametercode,parametername from t_parametersrc where parametertype='price_type') h on a.price_type=h.parametercode ");
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%' or a.product_id in (select distinct product_id from product_item where item_id in({0})))" : " and a.product_name like '%{0}%'", query.name_number); //add by zhuoqin0830w 2015/03/30 添加商品細項編號
}
if (!query.IsPage)//匯出時不匯出大於10000的商品
{
//商品匯出添加商品上下架備註欄位 add by mingwei0727w 2015/09/25
strCols.Append(" ,psh.remark ");
//strTbls.AppendFormat(" LEFT JOIN (select product_id,remark from (select product_id,remark from product_status_history where type =1 order by create_time desc) as kp group by product_id) as psh on a.product_id = psh.product_id ");
strTbls.AppendFormat(" LEFT JOIN (select product_id,remark from product_status_history as pshman inner join ( select max(id) as mid from product_status_history where type = 1 and remark <> '' group by product_id ) as pshson on pshman.id=pshson.mid)as psh on a.product_id = psh.product_id ");
strCondition.Append(" and a.product_id > 10000");
}
}
//添加 按鈕選擇值的查詢條件 edit by zhuoqin0830w 2015/02/10
if (query.priceCondition == 2)
{
strTbls.Append("left join price_master l on a.product_id=l.product_id and l.site_id= 1 and l.user_level=1 and l.user_id=0 and (l.product_id=l.child_id or l.child_id=0) ");
strCondition.AppendFormat(" and ((a.combination not in(0,1) and a.price_type <>2) or (a.combination = 1 and l.same_price =1))");
}
string strCount = "select count(a.product_id) as totalCount " + strTbls.ToString() + strCondition.ToString();
System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
totalCount = 0;
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
if (query.price_check == false)
{
/*
*以下3行 add by wwei0216w 2015/8/13
*用於查詢商品所對應的子商品item_id
*/
strTbls.Append(" LEFT JOIN product_item pi ON a.product_id = pi.product_id ");
strCols.Append(" ,GROUP_CONCAT(pi.item_id) AS itemIds ");
strCondition.Append(" GROUP BY a.product_id ");
}
strCondition.Append(" order by a.product_id desc ");
if (query.IsPage)
{
strCondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "sale_status", "UserLevel", "price_status", "product_freight", "product_mode", "Price_Type");
List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
foreach (QueryandVerifyCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_spec" && m.ParameterCode == q.product_spec_id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
var dlist = parameterList.Find(m => m.ParameterType == "sale_status" && m.ParameterCode == q.sale_status_id.ToString());
var elist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.level.ToString());
var flist = parameterList.Find(m => m.ParameterType == "price_status" && m.ParameterCode == q.price_status_id.ToString());
var glist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.product_freight_set_id.ToString());
var hlist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == q.product_mode_id.ToString());
var jlist = parameterList.Find(m => m.ParameterType == "Price_Type" && m.ParameterCode == q.price_type_id.ToString());
if (alist != null)
{
q.combination = alist.parameterName;
}
if (blist != null)
{
q.product_spec = blist.parameterName;
}
if (clist != null)
{
q.product_status = clist.parameterName;
}
if (dlist != null)
{
q.sale_name = dlist.parameterName;
}
if (elist != null)
{
q.user_level = elist.parameterName;
}
if (flist != null)
{
q.price_status = flist.parameterName;
}
if (glist != null)
{
q.product_freight_set = glist.parameterName;
}
if (hlist != null)
{
q.product_mode = hlist.parameterName;
}
if (jlist != null)
{
q.price_type = jlist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.QueryByProSite-->" + ex.Message, ex);
}
}
/// <summary>
/// 獲取庫存資料信息
/// </summary>
/// <param name="query">判斷條件</param>
/// <param name="totalCount">總共記錄</param>
/// <returns>符合條件的集合</returns>
/// Add by wangwei0216w 2014/10/20
public List<Model.Custom.ProductItemCustom> GetStockInfo(Model.Query.QueryVerifyCondition query)
{
try
{
//add by zhuoqin0830w 2015/02/25 添加匯出庫存資料中規格一，二的狀態顯示和備註欄位
StringBuilder strCols = new StringBuilder("SELECT b.brand_name,p.product_id,item.item_id,item.erp_id,p.product_name,p.prod_sz,s1.spec_name as Spec_Name_1,s2.spec_name as Spec_Name_2,item.item_stock,p.prepaid,item.item_alarm,p.product_status AS Product_Status_Id,item.export_flag,s1.spec_status as Spec_Status1,s2.spec_status as Spec_Status2,item.remark,p.product_freight_set AS Freight_Set_Id,p.product_mode AS Product_Mode_Id");
//add by wangwei 2014/9/29 添加a.create_channel字段
StringBuilder strTbls = new StringBuilder(" FROM product p ");
strTbls.AppendFormat(" INNER JOIN product_item item ON item.product_id = p.product_id ");
strTbls.AppendFormat(" LEFT JOIN vendor_brand b ON p.brand_id=b.brand_id ");
//strTbls.Append("LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='product_status') e ON p.product_status=e.parametercode ");
//strTbls.Append("LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='product_freight') f ON p.product_freight_set=f.parametercode ");
//strTbls.Append("LEFT JOIN (SELECT parametercode,parametername FROM t_parametersrc WHERE parametertype='product_mode') g ON p.product_mode=g.parametercode ");
strTbls.Append("LEFT JOIN product_spec s1 on item.spec_id_1 = s1.spec_id ");
strTbls.Append("LEFT JOIN product_spec s2 on item.spec_id_2 = s2.spec_id ");
StringBuilder strCondition = new StringBuilder(" WHERE 1=1 AND p.product_id > 10000 ");
if (query.StockStatus != 0)// 1.庫存為0還可販售 2.補貨中停止販售 3.庫存數<1
{
switch (query.StockStatus)
{
case 1://1.庫存為0還可販售
strCondition.Append(" and p.combination=1 and p.ignore_stock=1 ");
break;
case 2:
strCondition.Append(" and p.shortage=1");//2.補貨中停止販售
break;
case 3:
strCondition.Append(" and p.combination=1 and p.product_id in (select distinct product_id from product_item pi where item_stock <1 and pi.product_id=p.product_id)");//3.庫存數<1 edit by wwei0216w 不存在a.product_id 改為p.product_id 2015/11/18
break;
default:
throw new Exception("unaccepted StockStatus");
}
}
if (query.combination != 0)
{
strCondition.AppendFormat(" AND p.combination={0}", query.combination);
}
if (query.brand_id != 0)
{
strCondition.AppendFormat(" AND p.brand_id={0}", query.brand_id);
}
if (!string.IsNullOrEmpty(query.cate_id))
{
strCondition.AppendFormat(" AND p.cate_id IN ('{0}')", GetStrbyCate_id(query.cate_id));
}
if (query.category_id != 0)
{
IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
string str = string.Empty;
GetAllCategory_id(category, query.category_id, ref str);
strTbls.AppendFormat(" INNER JOIN (SELECT DISTINCT product_id FROM product_category_set WHERE category_id IN({0})) j ON p.product_id=j.product_id ", str);
}
if (query.product_status != -1)
{
strCondition.AppendFormat(" AND p.product_status={0}", query.product_status);
}
if (query.freight != 0)
{
strCondition.AppendFormat(" AND p.product_freight_set={0}", query.freight);
}
if (query.mode != 0)
{
strCondition.AppendFormat(" AND p.product_mode={0}", query.mode);
}
if (query.tax_type != 0)
{
strCondition.AppendFormat(" AND p.tax_type={0}", query.tax_type);
}
//add by zhuoqin0830w 2015/06/30 失格商品篩選
if (query.off_grade != 0)
{
strCondition.AppendFormat(" AND p.off_grade={0} ", query.off_grade);
}
if (!string.IsNullOrEmpty(query.date_type))
{
CheckCondition(query, "p", strCondition);
}
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(new Regex("^[0-9,]+$").IsMatch(query.name_number) ? " and (p.product_id in ({0}) or p.product_name like '%{0}%' or p.product_id in (select distinct product_id from product_item where item_id in({0})))" : " and p.product_name like '%{0}%'", query.name_number);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("product_status", "product_freight", "product_mode");
List<Model.Custom.ProductItemCustom> list = _dbAccess.getDataTableForObj<Model.Custom.ProductItemCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
foreach (ProductItemCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.Product_Status_Id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.Freight_Set_Id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == q.Product_Mode_Id.ToString());
if (alist != null)
{
q.Product_Status = alist.parameterName;
}
if (blist != null)
{
q.Freight_Set = blist.parameterName;
}
if (clist != null)
{
q.Product_Mode = clist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.GetStoreInfo-->" + ex.Message, ex);
}
}
/// <summary>
/// 預購商品導出 guodong1130w 2015/09/17 添加
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<Model.Custom.QueryandVerifyCustom> QueryForPurchase_in_advance(Model.Query.QueryVerifyCondition query, out int totalCount)
{
try
{
var regex = new Regex("^[0-9,]+$");
query.Replace4MySQL();
StringBuilder strCols = new StringBuilder("select a.product_id,b.brand_name,v.vendor_name_full,a.combination AS combination_id");
strCols.Append(",a.purchase_in_advance_start,a.purchase_in_advance_end,a.expect_time,pi.item_stock,CAST(sum(odetail.buy_num) AS SIGNED) as bnum,sdule.schedule_name ");
StringBuilder strTbls = new StringBuilder("from product a left join vendor_brand b on a.brand_id=b.brand_id ");
strTbls.Append(" LEFT JOIN vendor v ON v.vendor_id = b.vendor_id ");
//連接查出未出貨數量
strTbls.Append(" LEFT JOIN product_item pi ON pi.product_id = a.product_id ");
strTbls.Append(@"LEFT JOIN (select ordetail.item_id,ordetail.buy_num,ordetail.detail_id from order_detail as ordetail
INNER JOIN deliver_detail as ddetail on ordetail.detail_id=ddetail.detail_id
and ddetail.delivery_status in (0,1,2,3)
and detail_status=2 and item_mode<>1
LEFT JOIN order_slave AS oslave on ordetail.slave_id=oslave.slave_id
INNER JOIN order_master AS omaster on omaster.order_id=oslave.order_id
WHERE ((order_payment=8 and money_collect_date=0) or money_collect_date<>0)) as odetail on odetail.item_id = pi.item_id ");
//關聯查出排成名稱
strTbls.Append(" LEFT JOIN schedule_relation srelation on srelation.relation_id=a.product_id and srelation.relation_table='product' ");
strTbls.Append(" LEFT JOIN schedule sdule on sdule.schedule_id=srelation.schedule_id ");
StringBuilder strCondition = new StringBuilder(" WHERE 1=1 ");
if (query.brand_id != 0)
{
strCondition.AppendFormat(" and a.brand_id={0}", query.brand_id);
}
//庫存分類 edit by xiangwang0413w 2014/11/24
if (query.StockStatus != 0)// 1.庫存為0還可販售 2.補貨中停止販售 3.庫存數<1
{
switch (query.StockStatus)
{
case 1://1.庫存為0還可販售
strCondition.Append(" and a.combination=1 and a.ignore_stock=1 ");
break;
case 2:
strCondition.Append(" and a.shortage=1");//2.補貨中停止販售
break;
case 3:
strCondition.Append(" and a.combination=1 and a.product_id in (select distinct product_id from product_item pi where item_stock <1 and pi.product_id=a.product_id)");//3.庫存數<1
break;
default:
throw new Exception("unaccepted StockStatus");
}
}
if (!string.IsNullOrEmpty(query.cate_id))
{
strCondition.AppendFormat(" and a.cate_id in ('{0}')", GetStrbyCate_id(query.cate_id));
}
if (query.category_id != 0)
{//父商品查詢
IProductCategoryImplDao pcDao = new ProductCategoryDao(connStr);
List<Model.ProductCategory> category = pcDao.QueryAll(new ProductCategory());
string str = string.Empty;
GetAllCategory_id(category, query.category_id, ref str);
strTbls.AppendFormat(" inner join (select distinct product_id from product_category_set where category_id in({0})) j on a.product_id=j.product_id ", str);
}
if (query.combination != 0)
{
strCondition.AppendFormat(" and a.combination={0}", query.combination);
}
if (query.product_status != -1)
{
strCondition.AppendFormat(" and a.product_status={0}", query.product_status);
}
if (query.product_type != -1)
{
strCondition.AppendFormat(" and a.product_type={0}", query.product_type);
}
if (query.freight != 0)
{
strCondition.AppendFormat(" and a.product_freight_set={0}", query.freight);
}
if (query.mode != 0)
{
strCondition.AppendFormat(" and a.product_mode={0}", query.mode);
}
if (query.tax_type != 0)
{
strCondition.AppendFormat(" and a.tax_type={0}", query.tax_type);
}
// 已買斷商品的篩選功能
if (query.Prepaid != -1)
{
strCondition.AppendFormat(" and a.prepaid={0}", query.Prepaid);
}
// 失格商品篩選
if (query.off_grade != 0)
{
strCondition.AppendFormat(" and a.off_grade={0} ", query.off_grade);
}
//預購商品
if (query.purchase_in_advance != 0)
{
strCondition.AppendFormat(" and a.purchase_in_advance={0} ", query.purchase_in_advance);
}
if (!string.IsNullOrEmpty(query.date_type))
{
CheckCondition(query, "a", strCondition);
}
strCols.Append(",a.product_name ");
if (!string.IsNullOrEmpty(query.name_number))
{
strCondition.AppendFormat(regex.IsMatch(query.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%' or a.product_id in (select distinct product_id from product_item where item_id in({0})))" : " and a.product_name like '%{0}%'", query.name_number); //add by zhuoqin0830w 2015/03/30 添加商品細項編號
}
if (!query.IsPage)//匯出時不匯出大於10000的商品
{
strCondition.Append(" and a.product_id > 10000");
}
//添加 按鈕選擇值的查詢條件
if (query.priceCondition == 2)
{
strTbls.Append("left join price_master l on a.product_id=l.product_id and l.site_id= 1 and l.user_level=1 and l.user_id=0 and (l.product_id=l.child_id or l.child_id=0) ");
strCondition.AppendFormat(" and ((a.combination not in(0,1) and a.price_type <>2) or (a.combination = 1 and l.same_price =1))");
}
string strCount = "select count(a.product_id) as totalCount " + strTbls.ToString() + strCondition.ToString();
System.Data.DataTable _dt = _dbAccess.getDataTable(strCount);
totalCount = 0;
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
strCols.Append(" ,GROUP_CONCAT(pi.item_id) AS itemIds ");
strCondition.Append(" GROUP BY a.product_id ");
strCondition.Append(" order by a.product_id desc ");
if (query.IsPage)
{
strCondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_spec", "product_status", "sale_status", "UserLevel", "price_status", "product_freight", "product_mode", "Price_Type");
List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(strCols.ToString() + strTbls.ToString() + strCondition.ToString());
foreach (QueryandVerifyCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
if (alist != null)
{
q.combination = alist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.QueryForPurchase_in_advance-->" + ex.Message, ex);
}
}
//價格審核列表查詢
public List<Model.Custom.QueryandVerifyCustom> QueryandVerify(Model.Query.QueryVerifyCondition qcCon, ref int total)
{
try
{
qcCon.Replace4MySQL();
StringBuilder condition = new StringBuilder();
StringBuilder group = new StringBuilder();
StringBuilder colName = new StringBuilder("select a.product_image,a.product_id,b.brand_name,e.product_name,a.prod_sz,a.combination AS combination_id,");
colName.Append("a.product_status AS product_status_id,f.site_name,h.user_email,e.price_status AS price_status_id,");
colName.Append("e.price,e.event_price,e.event_start,e.cost,e.event_cost,e.event_end,i.apply_time,k.user_username as apply_user,i.apply_user as user_id,e.price_master_id,e.apply_id"); //edit by wangwei0216w 功能: 2014/8/8在sql語句中添加了e.cost,e.event_cost 2014/8/8
colName.Append(",e.site_id,e.user_level as level,e.user_id as user");
//condition.Append(" from price_master e");
condition.Append(" from price_master_ts e"); //價格審核列表不再查詢price_master正式表，而是查詢price_master_ts表 edit by xiangwang0413w 2014/07/17
condition.Append(" left join product a on a.product_id=e.product_id");
condition.Append(" left join vendor_brand b on a.brand_id=b.brand_id ");
//condition.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode");
//condition.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='product_status') d on a.product_status=d.parametercode");
condition.Append(" left join site f on e.site_id=f.site_id");
//condition.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='userlevel') g on e.user_level=g.parametercode");
condition.Append(" left join users h on e.user_id = h.user_id ");
condition.Append(" right join price_update_apply i on e.apply_id = i.apply_id ");
condition.Append(" right join manage_user k on k.user_id = i.apply_user ");
//condition.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='price_status') j on e.price_status=j.parametercode ");
condition.Append(" where e.price_status=2 and (e.child_id=e.product_id or e.child_id=0) ");
if (qcCon.brand_id != 0)
{
condition.AppendFormat(" and a.brand_id={0}", qcCon.brand_id);
}
if (qcCon.site_id != 0)
{
condition.AppendFormat(" and e.site_id={0}", qcCon.site_id);
}
if (qcCon.user_level != 0)
{
condition.AppendFormat(" and e.user_level={0}", qcCon.user_level);
}
if (qcCon.combination != 0)
{
condition.AppendFormat(" and a.combination={0}", qcCon.combination);
}
if (qcCon.product_status != -1)
{
condition.AppendFormat(" and a.product_status={0}", qcCon.product_status);
}
if (!(qcCon.time_start == "" && qcCon.time_end == "") && !string.IsNullOrEmpty(qcCon.date_type))
{
switch (qcCon.date_type)
{
case "apply_time": CheckCondition(qcCon, "i", condition); break; //申請日期
case "product_start": CheckCondition(qcCon, "a", condition); break; //上架日期
case "product_end": CheckCondition(qcCon, "a", condition); break; //下架日期
default:
break;
}
}
if (!string.IsNullOrEmpty(qcCon.name_number))
{
//condition.AppendFormat(" and (a.product_name like '%{0}%' or a.product_id='{0}')", qcCon.name_number);
condition.AppendFormat(new Regex("^[0-9,]+$").IsMatch(qcCon.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%') " : " and a.product_name like '%{0}%'", qcCon.name_number);
}
condition.Append(" and a.product_id is not null");
group.Append(" group by a.product_image,a.product_id,b.brand_name,a.product_name, combination,");
group.Append("product_status,f.site_name,h.user_email,price_status,");
group.Append("e.price,e.event_price,e.event_start,e.event_end,i.apply_time,i.apply_user,e.price_master_id");
total = int.Parse(_dbAccess.getDataTable("select count(a.product_id) " + condition.ToString()).Rows[0][0].ToString());
group.Append(" order by a.product_id desc ");
group.AppendFormat(" limit {0},{1}", qcCon.Start, qcCon.Limit);
var a = colName.Append(condition).Append(group).ToString();
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("combo_type", "product_status", "userlevel", "price_status");
List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(a);
foreach (QueryandVerifyCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.level.ToString());
var dlist = parameterList.Find(m => m.ParameterType == "price_status" && m.ParameterCode == q.price_status_id.ToString());
if (alist != null)
{
q.combination = alist.parameterName;
}
if (blist != null)
{
q.product_status = blist.parameterName;
}
if (clist != null)
{
q.user_level = clist.parameterName;
}
if (dlist != null)
{
q.price_status = dlist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.QueryandVerify-->" + ex.Message, ex);
}
}
/// <summary>
/// 代/待審核列表查詢
/// </summary>
/// <param name="qcCon"></param>
/// <returns></returns>
public List<Model.Custom.QueryandVerifyCustom> verifyWaitQuery(Model.Query.QueryVerifyCondition qcCon, out int totalCount)
{
try
{
qcCon.Replace4MySQL();
StringBuilder stbCols = new StringBuilder("select distinct a.product_image,a.product_id,b.brand_name,a.product_name,a.prod_sz,a.user_id,u.user_username as user_name,a.combination AS combination_id,");
stbCols.Append("a.product_price_list,s.prev_status AS prev_status_id,a.product_status AS product_status_id,a.product_freight_set AS product_freight_set_id,a.product_mode AS product_mode_id,a.tax_type,s.apply_time,s.online_mode,a.product_createdate,a.product_start,a.product_end");
StringBuilder stbTabs = new StringBuilder(" from product a");
stbTabs.Append(" left join vendor_brand b on a.brand_id=b.brand_id ");
stbTabs.Append(" left join manage_user u on a.user_id = u.user_id");
stbTabs.Append(" left join product_status_apply s on a.product_id = s.product_id");
//stbTabs.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='combo_type') c on a.combination=c.parametercode");
//stbTabs.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') p on s.prev_status = p.parametercode");
//stbTabs.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_status') d on a.product_status = d.parametercode");
//stbTabs.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_freight') e on a.product_freight_set = e.parametercode");
//stbTabs.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='product_mode') f on a.product_mode = f.parametercode");add by wwei0216w 2015/5/18
StringBuilder stbCon = new StringBuilder(" where 1=1");
if (qcCon.brand_id != 0)
{
stbCon.AppendFormat(" and a.brand_id={0}", qcCon.brand_id);
}
if (!string.IsNullOrEmpty(qcCon.cate_id))
{
stbCon.AppendFormat(" and a.cate_id in ('{0}')", GetStrbyCate_id(qcCon.cate_id));
}
if (qcCon.category_id != 0)
{
stbTabs.Append(" left join product_category_set g on a.product_id = g.product_id");
stbCon.AppendFormat(" and g.category_id = {0}", qcCon.category_id);
}
if (qcCon.combination != 0)
{
stbCon.AppendFormat(" and a.combination= {0}", qcCon.combination);
}
if (qcCon.product_status != -1)
{
stbCon.AppendFormat(" and a.product_status = {0}", qcCon.product_status);
//當商品狀態為申請審核時,申請表中必須要有對應記錄
if (qcCon.product_status == 1)
{
stbCon.AppendFormat(" and s.apply_id <> ''");
}
}
if (qcCon.prev_status != -1)
{
stbCon.AppendFormat(" and s.prev_status= {0}", qcCon.prev_status);
}
if (!string.IsNullOrEmpty(qcCon.name_number))
{
//stbCon.AppendFormat(" and (a.product_name like '%{0}%' or a.product_id='{0}')", qcCon.name_number);
stbCon.AppendFormat(new Regex("^[0-9,]+$").IsMatch(qcCon.name_number) ? " and (a.product_id in ({0}) or a.product_name like '%{0}%') " : " and a.product_name like '%{0}%'", qcCon.name_number);
}
if (!string.IsNullOrEmpty(qcCon.date_type))
{
switch (qcCon.date_type)
{
case "product_createdate": CheckCondition(qcCon, "a", stbCon); break; //建立日期
case "apply_time": CheckCondition(qcCon, "s", stbCon); break; //申請日期
case "product_start": CheckCondition(qcCon, "a", stbCon); break; //上架日期
case "product_end": CheckCondition(qcCon, "a", stbCon); break; //下架日期
default:
break;
}
}
totalCount = 0;
System.Data.DataTable _dt = _dbAccess.getDataTable("select count(a.product_id) as totalCount" + stbTabs.ToString() + stbCon.ToString());
if (_dt != null)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
stbCon.Append(" order by product_id desc");
if (qcCon.IsPage)
{
stbCon.AppendFormat(" limit {0},{1}", qcCon.Start, qcCon.Limit);
}
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type", "product_status", "product_freight", "product_mode");
List<Model.Custom.QueryandVerifyCustom> list = _dbAccess.getDataTableForObj<Model.Custom.QueryandVerifyCustom>(stbCols.ToString() + stbTabs.ToString() + stbCon.ToString());
foreach (QueryandVerifyCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination_id.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.prev_status_id.ToString());
var clist = parameterList.Find(m => m.ParameterType == "product_status" && m.ParameterCode == q.product_status_id.ToString());
var dlist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.product_freight_set_id.ToString());
var elist = parameterList.Find(m => m.ParameterType == "product_mode" && m.ParameterCode == q.product_mode_id.ToString());
if (alist != null)
{
q.combination = alist.parameterName;
}
if (blist != null)
{
q.prev_status = blist.parameterName;
}
if (clist != null)
{
q.product_status = clist.parameterName;
}
if (dlist != null)
{
q.product_freight_set = dlist.parameterName;
}
if (elist != null)
{
q.product_mode = elist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ProductDao.verifyWaitQuery-->" + ex.Message, ex);
}
}
public List<Model.Custom.PriceMasterCustom> Query(Model.PriceMaster priceMaster)
{
priceMaster.Replace4MySQL();
try
{
StringBuilder strSql = new StringBuilder("select price_master_id,accumulated_bonus,product_id,site.site_id,site.site_name,price_master.user_level,price_master.user_level as user_level_name,price_master.user_id,product_name,bonus_percent,default_bonus_percent,");
strSql.Append("same_price,accumulated_bonus,event_start,event_end,price_status,price,event_price,cost,event_cost,child_id,price_master.user_id,user_email,price_master.price_status,price_master.price_status as status,bonus_percent_start,bonus_percent_end,max_price,max_event_price,valid_start,valid_end from price_master ");
strSql.Append(" left join site on site.site_id=price_master.site_id left join users on users.user_id=price_master.user_id where 1=1 ");
//strSql.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='userlevel') t on t.parametercode=price_master.user_level");
//strSql.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='price_status') s on s.parametercode=price_master.price_status where 1=1");
if (priceMaster.product_id != 0)
{
strSql.AppendFormat(" and product_id={0}", priceMaster.product_id);
}
if (priceMaster.user_id != 0)
{
strSql.AppendFormat(" and price_master.user_id={0}", priceMaster.user_id);
}
if (priceMaster.user_level != 0)
{
strSql.AppendFormat(" and price_master.user_level={0}", priceMaster.user_level);
}
if (priceMaster.site_id != 0)
{
strSql.AppendFormat(" and site.site_id={0}", priceMaster.site_id);
}
if (priceMaster.price_master_id != 0)
{
strSql.AppendFormat(" and price_master_id={0}", priceMaster.price_master_id);
}
else
{
strSql.AppendFormat(" and child_id={0}", priceMaster.child_id);
}
//edit by zhuoqin0830w 2015/05/18
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("userlevel", "price_status");
List<PriceMasterCustom> list = _dbAccess.getDataTableForObj<PriceMasterCustom>(strSql.ToString());
foreach (PriceMasterCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "UserLevel" && m.ParameterCode == q.user_level.ToString());
var blist = parameterList.Find(m => m.ParameterType == "price_status" && m.ParameterCode == q.price_status.ToString());
if (alist != null)
{
q.user_level_name = alist.parameterName;
}
if (blist != null)
{
q.status = blist.parameterName;
}
}
return list;
//return _dbAccess.getDataTableForObj<Model.Custom.PriceMasterCustom>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("PriceMasterDao.Query-->" + ex.Message, ex);
}
}
public DataTable ExportArrorOrderExcel(OrderMasterQuery query)
{
try
{
DataTable _list = _orderMasterDao.ExportArrorOrderExcel(query);
_list.Columns.Add("remark");
_list.Columns.Add("mode_name");
_list.Columns.Add("combined_mode_name");
if (_list.Rows.Count > 0)
{
ParametersrcDao _pDao = new ParametersrcDao(conn);
List<Parametersrc> parameterList = _pDao.QueryParametersrcByTypes("order_status");
foreach (DataRow item in _list.Rows)
{
var alist = parameterList.Find(m => m.ParameterCode == item["order_status"].ToString());
if (alist != null)
{
item["remark"] = alist.remark.ToString();
}
switch (item["item_mode"].ToString())
{
case "0":
item["mode_name"] = "單一商品";
break;
case "1":
item["mode_name"] = "父商品";
break;
case "2":
item["mode_name"] = "子商品";
break;
default:
item["mode_name"] = "";
break;
}
//0:一般 1:組合 2:子商品
switch (item["combined_mode"].ToString())
{
case "0":
item["combined_mode_name"] = "一般";
break;
case "1":
item["combined_mode_name"] = "組合";
break;
case "2":
item["combined_mode_name"] = "子商品";
break;
default:
item["combined_mode_name"] = "";
break;
}
}
}
return _list;
}
catch (Exception ex)
{
throw new Exception("OrderMasterMgr-->ExportArrorOrderExcel-->" + ex.Message, ex);
}
}