public Model.Custom.PriceMasterProductCustom Query(PriceMasterTemp priceMasterTemp)
{
priceMasterTemp.Replace4MySQL();
StringBuilder stb = new StringBuilder();
try
{
stb.Append("select a.price_type,a.product_price_list,b.product_name,b.default_bonus_percent,b.bonus_percent,a.bag_check_money,b.accumulated_bonus,b.bonus_percent_start,b.bonus_percent_end,");
stb.Append(" b.price,b.event_price,b.cost,b.event_cost,b.event_start,b.event_end,b.same_price,b.valid_start,b.valid_end,a.show_listprice,a.product_mode from product_temp a right join ");
stb.AppendFormat("price_master_temp b on a.writer_id=b.writer_id and a.combo_type=b.combo_type and a.product_id=b.product_id and a.combo_type={0} where a.writer_id={1}", priceMasterTemp.combo_type, priceMasterTemp.writer_Id);
stb.AppendFormat(" and b.product_id='{0}' and b.child_id='{1}'", priceMasterTemp.product_id, priceMasterTemp.child_id);
return _dbAccess.getSinggleObj<Model.Custom.PriceMasterProductCustom>(stb.ToString());
}
catch (Exception ex)
{
throw new Exception("PriceMasterTempDao.Query-->" + ex.Message + stb.ToString(), ex);
}
}
public string Save(PriceMasterTemp priceMaster)
{
priceMaster.Replace4MySQL();
StringBuilder strSql = new StringBuilder("");
try
{
strSql.Append("insert into price_master_temp(`writer_id`,`product_id`,`site_id`,`user_level`,`user_id`,`product_name`,`bonus_percent`,`default_bonus_percent`,");
strSql.Append("`same_price`,`event_start`,`event_end`,`price_status`,`price`,`event_price`,`child_id`,`combo_type`,`cost`,`event_cost`,`accumulated_bonus`,`bonus_percent_start`");
strSql.AppendFormat(",`bonus_percent_end`,`max_price`,`max_event_price`,`valid_start`,`valid_end`)values({0},'{1}',{2},{3}", priceMaster.writer_Id, string.IsNullOrEmpty(priceMaster.product_id) ? priceMaster.product_id.ToString() : priceMaster.product_id, priceMaster.site_id, priceMaster.user_level);
strSql.AppendFormat(",{0},'{1}',{2},{3},{4},", priceMaster.user_id, priceMaster.product_name, priceMaster.bonus_percent, priceMaster.default_bonus_percent, priceMaster.same_price);
strSql.AppendFormat("{0},{1},{2},{3},", priceMaster.event_start, priceMaster.event_end, priceMaster.price_status, priceMaster.price);
strSql.AppendFormat("{0},'{1}',{2},{3},{4},{5},", priceMaster.event_price, priceMaster.child_id, priceMaster.combo_type, priceMaster.cost, priceMaster.event_cost, priceMaster.accumulated_bonus);
strSql.AppendFormat("{0},{1},{2},{3},{4},{5});select @@identity;", priceMaster.bonus_percent_start, priceMaster.bonus_percent_end, priceMaster.max_price, priceMaster.max_event_price, priceMaster.valid_start, priceMaster.valid_end);
return strSql.ToString();
}
catch (Exception ex)
{
throw new Exception("PriceMasterTempDao.Save-->" + ex.Message + strSql.ToString(), ex);
}
}
public string Update(PriceMasterTemp priceMaster)
{
priceMaster.Replace4MySQL();
try
{
StringBuilder strSql = new StringBuilder("set sql_safe_updates=0;update price_master_temp set ");
strSql.AppendFormat(" writer_id={0},site_id={1},user_level={2},user_id={3},", priceMaster.writer_Id, priceMaster.site_id, priceMaster.user_level, priceMaster.user_id);
strSql.AppendFormat("product_name='{0}',bonus_percent={1},default_bonus_percent={2},", priceMaster.product_name, priceMaster.bonus_percent, priceMaster.default_bonus_percent);
strSql.AppendFormat("same_price={0},price_status={1},event_start={2},", priceMaster.same_price, priceMaster.price_status, priceMaster.event_start);
strSql.AppendFormat("event_end={0},price={1},event_price={2},accumulated_bonus = {3}", priceMaster.event_end, priceMaster.price, priceMaster.event_price, priceMaster.accumulated_bonus);
strSql.AppendFormat(",bonus_percent_start={0},bonus_percent_end={1}", priceMaster.bonus_percent_start, priceMaster.bonus_percent_end);
strSql.AppendFormat(",cost={0},event_cost={1},child_id={2},max_price={3} ", priceMaster.cost, priceMaster.event_cost, priceMaster.child_id, priceMaster.max_price);
strSql.AppendFormat(",max_event_price={0},valid_start={1},valid_end={2} ", priceMaster.max_event_price, priceMaster.valid_start, priceMaster.valid_end);
strSql.AppendFormat(" where writer_id={0} and combo_type={1} and product_id='{2}' and child_id='{3}';", priceMaster.writer_Id, priceMaster.combo_type, priceMaster.product_id, priceMaster.child_id);
strSql.AppendFormat("set sql_safe_updates = 1;");
return strSql.ToString();
}
catch (Exception ex)
{
throw new Exception("PriceMasterTempDao.Update-->" + ex.Message, ex);
}
}
public List<Model.Custom.PriceMasterCustom> QueryProdSiteByVendor(PriceMasterTemp priceMasterTemp)
{
priceMasterTemp.Replace4MySQL();
StringBuilder strSql = new StringBuilder("");
try
{
strSql.Append("select price_master_id,accumulated_bonus,product_id as vendor_product_id,site.site_id,site.site_name,pmt.user_level,t.parametername as user_level_name,pmt.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 ,pmt.user_id,user_email,s.parametername as status,bonus_percent_start,bonus_percent_end,max_price,max_event_price,valid_start,valid_end from price_master_temp pmt ");
strSql.Append(" left join site on site.site_id=pmt.site_id left join users on users.user_id=pmt.user_id ");
strSql.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='userlevel') t on t.parametercode=pmt.user_level");
strSql.Append(" left join (select parametercode,parametername from t_parametersrc where parametertype='price_status') s on s.parametercode=pmt.price_status where 1=1");
if (!string.IsNullOrEmpty(priceMasterTemp.product_id) && priceMasterTemp.product_id != "0")
{
strSql.AppendFormat(" and product_id='{0}'", priceMasterTemp.product_id);
}
if (priceMasterTemp.user_id != 0)
{
strSql.AppendFormat(" and pmt.user_id={0}", priceMasterTemp.user_id);
}
if (priceMasterTemp.user_level != 0)
{
strSql.AppendFormat(" and pmt.user_level={0}", priceMasterTemp.user_level);
}
if (priceMasterTemp.site_id != 0)
{
strSql.AppendFormat(" and site.site_id={0}", priceMasterTemp.site_id);
}
if (priceMasterTemp.price_master_id != 0)
{
strSql.AppendFormat(" and price_master_id={0}", priceMasterTemp.price_master_id);
}
if (!string.IsNullOrEmpty(priceMasterTemp.child_id) && priceMasterTemp.child_id != "0")
{
strSql.AppendFormat(" and child_id='{0}'", priceMasterTemp.child_id);
}
return _dbAccess.getDataTableForObj<Model.Custom.PriceMasterCustom>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("PriceMasterTempDao.QueryProdSiteByVendor-->" + ex.Message + strSql.ToString(), ex);
}
}
public string VendorSaveFromPriceMaster(PriceMasterTemp priceMasterTemp, string old_product_id)
{//20140905 供應商複製
priceMasterTemp.Replace4MySQL();
try
{
StringBuilder strSql = new StringBuilder("insert into price_master_temp(`writer_id`,`product_id`,`site_id`,`user_level`,`user_id`,`product_name`,`accumulated_bonus`");
strSql.Append(",`bonus_percent`,`default_bonus_percent`,`same_price`,`event_start`,`event_end`,`price_status`,`price`,`event_price`,`child_id`,`combo_type`,`cost`,`event_cost`,`bonus_percent_start`,`bonus_percent_end`,`max_price`,`max_event_price`,`valid_start`,`valid_end`) select ");
strSql.AppendFormat("{0} as writer_id,'{1}' as product_id,site_id,user_level,user_id,product_name,accumulated_bonus", priceMasterTemp.writer_Id, priceMasterTemp.product_id);
strSql.AppendFormat(",bonus_percent,default_bonus_percent,same_price,event_start,event_end,{0} as price_status,price,event_price, '{1}' as child_id", priceMasterTemp.price_status, priceMasterTemp.product_id);
strSql.AppendFormat(",{0} as combo_type,cost,event_cost,bonus_percent_start,bonus_percent_end,max_price,max_event_price,valid_start,valid_end ", priceMasterTemp.combo_type);
uint productid = 0;
if (uint.TryParse(old_product_id, out productid))
{
strSql.AppendFormat(" from price_master where product_id={0} ", old_product_id);
}
else
{
strSql.AppendFormat(" from price_master_temp where product_id='{0}' ", old_product_id);
}
strSql.Append(" and site_id=1 and user_level=1 and user_id=0 ;");
return strSql.ToString();
}
catch (Exception ex)
{
throw new Exception("PriceMasterTempDao.VendorSaveFromPriceMaster-->" + ex.Message, ex);
}
}
public string SaveFromPriceMaster(PriceMasterTemp priceMasterTemp)
{
priceMasterTemp.Replace4MySQL();
try
{
StringBuilder strSql = new StringBuilder("insert into price_master_temp(`writer_id`,`product_id`,`site_id`,`user_level`,`user_id`,`product_name`,`accumulated_bonus`");
strSql.Append(",`bonus_percent`,`default_bonus_percent`,`same_price`,`event_start`,`event_end`,`price_status`,`price`,`event_price`,`child_id`,`combo_type`,`cost`,`event_cost`,`bonus_percent_start`,`bonus_percent_end`,`max_price`,`max_event_price`,valid_start,valid_end) select ");
strSql.AppendFormat("{0} as writer_id,product_id,site_id,user_level,user_id,'' AS product_name,accumulated_bonus", priceMasterTemp.writer_Id);
strSql.AppendFormat(",bonus_percent,default_bonus_percent,same_price,event_start,event_end,{0} as price_status,price,event_price,child_id", priceMasterTemp.price_status);
strSql.AppendFormat(",{0} as combo_type,cost,event_cost,bonus_percent_start,bonus_percent_end,max_price,max_event_price,valid_start,valid_end from price_master where product_id='{1}' and site_id=1 and user_level=1 and user_id=0", priceMasterTemp.combo_type, priceMasterTemp.product_id);
return strSql.ToString();
///ediy by wwei0216w 2015/8/27
/*
修改代碼 '' AS product_name
* 修改原因:
* 商品名稱添加前後綴后,複製商品時,價格名稱部份會連前後綴一起複製,導致商品沒有前後綴的添加記錄,為避免該問題
* 複製商品時,價格名稱部份將不會再進行複製
*/
}
catch (Exception ex)
{
throw new Exception("PriceMasterTempDao.SaveFromPriceMaster-->" + ex.Message, ex);
}
}
