public string Save(ProductCategorySet saveModel)
{
StringBuilder stb = new StringBuilder("insert into product_category_set (`product_id`,`category_id`,`brand_id`) ");
stb.AppendFormat(" values ({0},{1},{2});", saveModel.Product_Id, saveModel.Category_Id, saveModel.Brand_Id);
return stb.ToString();
}
public DataOperation(List<Model.MigrationDataSet> mds, MainForm form)
{
this.mds = mds;
this.connectionString = System.Configuration.ConfigurationSettings.AppSettings["MySqlConnectionString"];
prod = new Product();
pItem = new ProductItem();
pM = new PriceMaster();
iPrice = new ItemPrice();
categorySet = new ProductCategorySet();
pMap = new ProductMigrationMap();
_prodMgr = new ProductMgr(this.connectionString);
_priceMgr = new PriceMasterMgr(this.connectionString);
_prodItemMgr = new ProductItemMgr(this.connectionString);
_itemPriceMgr = new ItemPriceMgr(this.connectionString);
_vendorBrandMgr = new VendorBrandMgr(this.connectionString);
_vendorMgr = new VendorMgr(this.connectionString);
_pMap = new ProductMigrationMgr(this.connectionString);
_productCategorySetMgr = new ProductCategorySetMgr(this.connectionString);
_productNoticeSetMgr = new ProductNoticeSetMgr(this.connectionString);
_productTagSetMgr = new ProductTagSetMgr(this.connectionString);
_productPictureMgr = new ProductPictureMgr(this.connectionString);
_proStatusHistoryMgr = new ProductStatusHistoryMgr(connectionString);
_proSpecMgr = new ProductSpecMgr(connectionString);
_siteMgr = new SiteMgr(connectionString);
this.form = form;
}
public string Delete(ProductCategorySet delModel, string deStr = "0")
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"set sql_safe_updates = 0;delete from product_category_set where product_id = {0}", delModel.Product_Id);
if(deStr!="0")
{
sql.AppendFormat(" AND category_id in ({0})", deStr);
}
sql.Append("; set sql_safe_updates = 1;");
return sql.ToString();
}
public string Delete(ProductCategorySet delModel, string deStr = "0")
{
try
{
return _categorySetDao.Delete(delModel, deStr);
}
catch (Exception ex)
{
throw new Exception("ProductCategorySetMgr-->Delete-->" + ex.Message, ex);
}
}
public string Save(ProductCategorySet save)
{
try
{
return _categorySetDao.Save(save);
}
catch (Exception ex)
{
throw new Exception("ProductCategorySetMgr-->SingleCompareSave-->" + ex.Message, ex);
}
}
public List<ProductCategorySet> Query(ProductCategorySet queryModel)
{
try
{
return _categorySetDao.Query(queryModel);
}
catch (Exception ex)
{
throw new Exception("ProductCategorySetMgr-->Query(ProductCategorySet queryModel)-->" + ex.Message, ex);
}
}
public List<ProductCategorySet> Query(ProductCategorySet queryModel)
{
StringBuilder strSql = new StringBuilder("select product_id,category_id,brand_id from product_category_set where 1=1 ");
if (queryModel.Product_Id != 0)
{
strSql.AppendFormat(" and product_id = {0} ", queryModel.Product_Id);
}
if (queryModel.Category_Id != 0)
{
strSql.AppendFormat(" and category_id = {0} ", queryModel.Category_Id);
}
return _access.getDataTableForObj<ProductCategorySet>(strSql.ToString());
}
public string DelProdCateSetByCPID(ProductCategorySet delModel)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" set sql_safe_updates = 0;delete from product_category_set where product_id='{0}' and category_id='{1}';set sql_safe_updates = 1;", delModel.Product_Id, delModel.Category_Id);
return sb.ToString();
}
public int Insert(ProductCategorySet save)
{
try
{
return _categorySetDao.Insert(save);
}
catch (Exception ex)
{
throw new Exception("ProductCategorySetMgr-->Insert-->" + ex.Message, ex);
}
}
public HttpResponseBase SaveProductCategorySet()
{
string[] bids = Request.Params["brandids"].Split('|');
string categoryid = Request.Params["categoryid"];
string[] pids = Request.Params["productids"].Split('|');
string proIds = string.Empty;//保存已經存在于categorset中的product
string resultStr = "{success:false}";
try
{
for (int i = 0; i < bids.Length; i++)
{
if (bids[i].ToString() != "" && pids[i].ToString() != "")
{
ProductCategorySetMgr _categorySetMgr = new ProductCategorySetMgr(connectionString);
ProductCategorySet pcs = new ProductCategorySet();
pcs.Brand_Id = Convert.ToUInt32(bids[i]);
pcs.Category_Id = Convert.ToUInt32(categoryid);
pcs.Product_Id = Convert.ToUInt32(pids[i]);
List<ProductCategorySet> queryList = _categorySetMgr.Query(pcs);
if (queryList.Count == 0)//該類別下不存在該商品時才新增
{
_categorySetMgr.Insert(pcs);
}
else
{
proIds += queryList[0].Product_Id;
if (i != bids.Length - 1)
{
proIds += ",";
}
}
}
}
resultStr = "{success:true,\"proIds\":\"" + proIds + "\"}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
this.Response.Clear();
this.Response.Write(resultStr);
this.Response.End();
return this.Response;
}
public HttpResponseBase DeleteProductFromCategorySet()
{
string[] bids = Request.Params["brandids"].Split('|');
string categoryid = Request.Params["categoryid"];
string[] pids = Request.Params["productids"].Split('|');
string resultStr = "{success:false}";
try
{
for (int i = 0; i < bids.Length; i++)
{
if (bids[i].ToString() != "" && pids[i].ToString() != "")
{
ProductCategorySetMgr _categorySetMgr = new ProductCategorySetMgr(connectionString);
ProductCategorySet pcs = new ProductCategorySet();
pcs.Brand_Id = Convert.ToUInt32(bids[i]);
pcs.Category_Id = Convert.ToUInt32(categoryid);
pcs.Product_Id = Convert.ToUInt32(pids[i]);
try
{
_categorySetMgr.DeleteProductByModel(pcs);
}
catch (Exception)
{
}
}
}
resultStr = "{success:true}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
this.Response.Clear();
this.Response.Write(resultStr);
this.Response.End();
return this.Response;
}
//add by wwei0216w 2015/2/24
/// <summary>
/// 根據商品id修改品牌id
/// </summary>
/// <param name="pcs">一個ProductCategorySet對象</param>
/// <returns>將要執行的sql語句</returns>
public string UpdateBrandId(ProductCategorySet pcs)
{
try
{
return _categorySetDao.UpdateBrandId(pcs);
}
catch (Exception ex)
{
throw new Exception("ProductCategorySetMgr-->UpdateBrandId" + ex.Message, ex);
}
}
public string SaveFromOtherPro(ProductCategorySet saveModel)
{
StringBuilder stb = new StringBuilder("insert into product_category_set (`product_id`,`category_id`,`brand_id`) select {0} as product_id,category_id,brand_id");
stb.AppendFormat(" from product_category_set where product_id={0}", saveModel.Product_Id);
return stb.ToString();
}
public void PrepareData(List<CombinationExcel> source)
{
if (source != null)
{
source.ForEach(m => m.Validate());
var parents = source.Where(m => m.combination.Trim() != "299" && string.IsNullOrEmpty(m.msg));
_productMgr = new ProductMgr(strConn);
_productItemMgr = new ProductItemMgr(strConn);
_vendorMgr = new VendorMgr(strConn);
_vendorBrandMgr = new VendorBrandMgr(strConn);
_siteMgr = new SiteMgr(strConn);
_productMigrationMgr = new ProductMigrationMgr(strConn);
_productComboMgr = new ProductComboMgr(strConn);
_cateMgr = new ProductCategoryMgr(strConn);
_productCategorySetMgr = new ProductCategorySetMgr(strConn);
_productNoticeSetMgr = new ProductNoticeSetMgr(strConn);
_productTagSetMgr = new ProductTagSetMgr(strConn);
_productPictureMgr = new ProductPictureMgr(strConn);
_productStatusHistoryMgr = new ProductStatusHistoryMgr(strConn);
foreach (var parent in parents)
{
form.change(1);
uint product_id = uint.Parse(parent.product_id);
#region Product
ProductMigrationMap prodMigra = _productMigrationMgr.GetSingle(new ProductMigrationMap { temp_id = parent.is_exist.ToUpper().Trim().Equals("OLD") ? parent.product_id : parent.temp_id });
if (prodMigra != null)
{
parent.msg = "此記錄暫時編號(temp_id)已經存在;";
continue;
}
Product newPro = parent.is_exist.ToUpper().Trim() == "OLD" ? _productMgr.Query(new Product { Product_Id = product_id }).FirstOrDefault() : new Product();
if (newPro == null)
{
parent.msg = "商品不存在";
continue;
}
newPro.Product_Name = parent.product_name.Trim();
VendorBrand brand = _vendorBrandMgr.GetProductBrand(new VendorBrand { Brand_Name = parent.brand_name.Trim() });
if (brand == null)
{
parent.msg = "品牌不存在";
continue;
}
newPro.Brand_Id = brand.Brand_Id;
switch (parent.combination.Trim())
{
case "2":
newPro.Combination = 2; break;
case "3":
newPro.Combination = 3; break;
case "4":
newPro.Combination = 4; break;
default: break;
}
switch (parent.status.Trim().ToUpper())
{
case "SAME":
break;
case "ON":
newPro.Product_Status = 5; break;
case "OFF":
newPro.Product_Status = 6; break;
case "NEW":
newPro.Product_Status = 0; break;
default:
break;
}
newPro.Price_type = 1;
newPro.Product_Spec = 0;
newPro.Spec_Title_1 = string.Empty;
newPro.Spec_Title_2 = string.Empty;
newPro.Ignore_Stock = 0;// parent.ignore_stock.ToUpper().Trim() == "Y" ? 1 : 0;
newPro.Shortage = 0;// parent.shortage.ToUpper().Trim() == "Y" ? 1 : 0;
newPro.Cate_Id = parent.cate_id.Trim();
var vendor = _vendorMgr.GetSingle(new Vendor { vendor_id = brand.Vendor_Id });
if (vendor == null)
{
parent.msg = "供應商不存在";
continue;
}
newPro.user_id = vendor.product_manage;
if (!string.IsNullOrEmpty(parent.service_fee))
{
uint bag_check_money=0;
uint.TryParse(parent.service_fee,out bag_check_money);
newPro.Bag_Check_Money = bag_check_money;
}
#endregion
bool result = true;
ArrayList sqls = new ArrayList();
#region PriceMaster
Site site = _siteMgr.Query(new Site { Site_Name = parent.site.Trim() }).FirstOrDefault();
if (site == null)
{
parent.msg = "價格檔站臺不存在";
result = false;
break;
}
List<PriceMaster> priceMasters = new List<PriceMaster>();
//首先默認加入一筆吉甲地站臺
PriceMaster newPriceMaster = new PriceMaster { site_id = 1, user_level = 1, same_price = 1, accumulated_bonus = 1, default_bonus_percent = 1, bonus_percent = 1 };
newPriceMaster.product_name = newPro.Product_Name;
newPriceMaster.price_status = 1;
var item = _productItemMgr.Query(new ProductItem { Product_Id = newPro.Product_Id }).FirstOrDefault();
if (item == null)
{
parent.msg = "商品細項不存在";
continue;
}
#region 成本
int cost = 0;
if (!string.IsNullOrEmpty(parent.cost))
{
int.TryParse(parent.cost, out cost);
}
else
{
cost = Convert.ToInt32(item.Item_Cost);
}
newPriceMaster.cost = cost;
#endregion
#region 售價
int price = 0;
if (!string.IsNullOrEmpty(parent.price))
{
int.TryParse(parent.price, out price);
}
else
{
price = Convert.ToInt32(item.Item_Money);
}
newPriceMaster.price = price;
#endregion
newPriceMaster.event_price = Convert.ToInt32(item.Event_Item_Money);
newPriceMaster.event_start = item.Event_Product_Start;
newPriceMaster.event_end = item.Event_Product_End;
priceMasters.Add(newPriceMaster);
if (site.Site_Id != 1)
{
//如果站臺不是吉甲地就把吉甲地站臺的價格狀態改為4（下架）
priceMasters[0].price_status = 4;
//再加入非吉甲地站臺
newPriceMaster = new PriceMaster { site_id = site.Site_Id, user_level = 1, same_price = 1, accumulated_bonus = 1, default_bonus_percent = 1, bonus_percent = 1 };
newPriceMaster.product_name = newPro.Product_Name;
newPriceMaster.price_status = 1;
#region 成本
cost = 0;
if (!string.IsNullOrEmpty(parent.cost))
{
int.TryParse(parent.cost, out cost);
}
else
{
cost = Convert.ToInt32(item.Item_Cost);
}
newPriceMaster.cost = cost;
#endregion
#region 售價
price = 0;
if (!string.IsNullOrEmpty(parent.price))
{
int.TryParse(parent.price, out price);
}
else
{
price = Convert.ToInt32(item.Item_Money);
}
newPriceMaster.price = price;
#endregion
newPriceMaster.event_price = Convert.ToInt32(item.Event_Item_Money);
newPriceMaster.event_start = item.Event_Product_Start;
newPriceMaster.event_end = item.Event_Product_End;
priceMasters.Add(newPriceMaster);
}
#region 其他站臺價格
var child = source.Where(m =>
m.combination.Trim() == "299"
&& m.formula.ToUpper().Trim() == parent.temp_id.ToUpper().Trim()
&& string.IsNullOrEmpty(m.msg)
&& m.new_old.ToUpper().Trim() == "NEW"
&& m.formula.ToUpper().Trim().StartsWith("P"));
if (child != null)
{
foreach (var c in child)
{
item = _productItemMgr.Query(new ProductItem { Product_Id = uint.Parse(c.product_id) }).FirstOrDefault();
if (item == null)
{
c.msg = "價格檔商品細項不存在";
result = false;
break;
}
site = _siteMgr.Query(new Site { Site_Name = c.site.Trim() }).FirstOrDefault();
if (site == null)
{
c.msg = "站臺不存在";
result = false;
break;
}
if (priceMasters.Exists(m => m.site_id == site.Site_Id))
{
c.msg = "站臺重複";
result = false;
break;
}
newPriceMaster = new PriceMaster { user_level = 1, same_price = 1, accumulated_bonus = 1, default_bonus_percent = 1, bonus_percent = 1 };
newPriceMaster.product_name = c.product_name.Trim();
newPriceMaster.site_id = site.Site_Id;
newPriceMaster.price_status = 1;
#region 成本
cost = 0;
if (!string.IsNullOrEmpty(c.cost))
{
int.TryParse(c.cost, out cost);
}
else
{
cost = Convert.ToInt32(item.Item_Cost);
}
newPriceMaster.cost = cost;
#endregion
#region 售價
price = 0;
if (!string.IsNullOrEmpty(c.price))
{
int.TryParse(c.price, out price);
}
else
{
price = Convert.ToInt32(item.Item_Money);
}
newPriceMaster.price = price;
#endregion
newPriceMaster.event_price = Convert.ToInt32(item.Event_Item_Money);
newPriceMaster.event_start = item.Event_Product_Start;
newPriceMaster.event_end = item.Event_Product_End;
priceMasters.Add(newPriceMaster);
if (site.Site_Name.Trim().ToLower() == "chinatrust")
{
ArrayList category_set = CategorySet(c, brand.Brand_Id);
if (category_set == null)
{
result = false;
break;
}
else
{
sqls.AddRange(category_set);
}
}
}
if (!result) continue;
}
#endregion
#endregion
#region ProductCombo
string[] strIds = parent.formula.IndexOf(",") != -1 ? parent.formula.Trim().Split(',') : new string[] { parent.formula.Trim() };
string[] nums = parent.number.IndexOf(",") != -1 ? parent.number.Trim().Split(',') : new string[] { parent.number.Trim() };
List<Product> children = new List<Product>();
ProductCombo tmp;
for (int i = 0; i < strIds.Length; i++)
{
tmp = new ProductCombo();
if (parent.combination.Trim() != "2")
{
tmp.Buy_Limit = parent.buylimit.Trim().ToUpper() == "Y" ? 1 : 0;
}
else
{
tmp.S_Must_Buy = int.Parse(i >= nums.Length ? nums[nums.Length - 1] : nums[i]);
}
if (parent.combination.Trim() == "3")//為3任選時 數量為G_Must_Buy
{
tmp.G_Must_Buy = int.Parse(nums[0]);
}
switch (parent.new_old.Trim().ToUpper())
{
case "NEW":
var map = _productMigrationMgr.GetSingle(new ProductMigrationMap { temp_id = strIds[i] });
if (map == null)
{
parent.msg = "原料編號未找到對應";
break;
}
tmp.Child_Id = Convert.ToInt32(map.product_id);
break;
case "OLD":
tmp.Child_Id = int.Parse(strIds[i]);
break;
case "BOTH":
if (strIds[i].ToUpper().Trim().StartsWith("P"))
{
var tMap = _productMigrationMgr.GetSingle(new ProductMigrationMap { temp_id = strIds[i] });
if (tMap == null)
{
parent.msg = "原料編號未找到對應";
break;
}
tmp.Child_Id = Convert.ToInt32(tMap.product_id);
}
else
{
tmp.Child_Id = int.Parse(strIds[i]);
}
break;
}
if (tmp.Child_Id == 0)
{
result = false;
break;
}
else
{
Product tmpPro = _productMgr.Query(new Product { Product_Id = Convert.ToUInt32(tmp.Child_Id) }).FirstOrDefault();
if (tmpPro == null)
{
parent.msg = strIds[i] + "不存在";
result = false;
break;
}
if (tmpPro.Product_Status == 0 || tmpPro.Product_Status ==1)
{
parent.msg = strIds[i] + "商品状态不為新增、申請審核";
result = false;
break;
}
if (tmpPro.Combination != 0 && tmpPro.Combination != 1)
{
parent.msg = strIds[i] + "不是單一商品";
result = false;
break;
}
switch (newPro.Product_Freight_Set)
{
case 1:
case 3:
if (tmpPro.Product_Freight_Set != 1 && tmpPro.Product_Freight_Set != 3)
{
parent.msg = strIds[i] + "运费模式与主商品不匹配";
result = false;
}
break;
case 2:
case 4:
if (tmpPro.Product_Freight_Set != 2 && tmpPro.Product_Freight_Set != 4)
{
parent.msg = strIds[i] + "运费模式与主商品不匹配";
result = false;
}
break;
case 5:
case 6:
if (tmpPro.Product_Freight_Set != 5 && tmpPro.Product_Freight_Set != 6)
{
parent.msg = strIds[i] + "运费模式与主商品不匹配";
result = false;
}
break;
default:
break;
}
if (!result) break;
children.Add(tmpPro);
}
sqls.Add(_productComboMgr.Save(tmp));
}
if (!result) continue;
#endregion
#region product_category_set
if (!string.IsNullOrEmpty(parent.display))
{
ArrayList category_set = CategorySet(parent, brand.Brand_Id);
if (category_set == null)
continue;
else
{
foreach (var set in category_set)
{
if (!sqls.Contains(set))
{
sqls.Add(set);
}
}
}
}
else
{
sqls.Add(_productCategorySetMgr.SaveFromOtherPro(new ProductCategorySet { Product_Id = product_id }));
}
#endregion
#region product_migration_map
ProductMigrationMap pMap = new ProductMigrationMap();
if (parent.is_exist.ToUpper().Equals("OLD"))
{
pMap.temp_id = parent.product_id;
}
else if (parent.is_exist.ToUpper().Equals("NEW"))
{
pMap.temp_id = parent.temp_id;
}
sqls.Add(_productMigrationMgr.SaveNoPrid(pMap));
#endregion
#region notice tag picture
sqls.Add(_productNoticeSetMgr.SaveFromOtherPro(new ProductNoticeSet { product_id = product_id }));
sqls.Add(_productTagSetMgr.SaveFromOtherPro(new ProductTagSet { product_id = product_id }));
sqls.Add(_productPictureMgr.SaveFromOtherPro(new ProductPicture { product_id = Convert.ToInt32(product_id) }));
sqls.Add(_productStatusHistoryMgr.SaveNoProductId(new ProductStatusHistory { type = 7, product_status = Convert.ToInt32(newPro.Product_Status) }));
#endregion
string str = string.Empty;
if (_productMgr.ProductMigration(newPro, priceMasters, null, null, sqls, null))
{
str = "匯入成功";
}
else
{
str = "保存至數據庫失敗";
}
parent.msg = str;
source.FindAll(m =>
m.combination.Trim() == "299"
&& m.formula.ToUpper().Trim() == parent.temp_id.ToUpper().Trim()
&& string.IsNullOrEmpty(m.msg)
&& m.new_old.ToUpper().Trim() == "NEW"
&& m.formula.ToUpper().Trim().StartsWith("P")).ForEach(m => m.msg = str);
}
#region 其他站臺價格
var pChild = source.Where(m => m.combination.Trim() == "299" && m.new_old.ToUpper().Trim() == "OLD" && string.IsNullOrEmpty(m.msg));
if (pChild != null)
{
ProductItem item;
PriceMaster newPriceMaster;
Site site;
_priceMasterMgr = new PriceMasterMgr(strConn);
foreach (var c in pChild)
{
ProductMigrationMap prodMigra = _productMigrationMgr.GetSingle(new ProductMigrationMap { temp_id = c.formula.Trim() });
if (prodMigra == null)
{
c.msg = "原料編號對照不存在";
continue;
}
item = _productItemMgr.Query(new ProductItem { Product_Id = uint.Parse(c.product_id) }).FirstOrDefault();
if (item == null)
{
c.msg = "價格檔商品細項不存在";
continue;
}
site = _siteMgr.Query(new Site { Site_Name = c.site.Trim() }).FirstOrDefault();
if (site == null)
{
c.msg = "站臺不存在";
continue;
}
newPriceMaster = new PriceMaster { user_level = 1, same_price = 1, accumulated_bonus = 1, default_bonus_percent = 1, bonus_percent = 1 };
newPriceMaster.product_name = c.product_name.Trim();
newPriceMaster.product_id = prodMigra.product_id;
newPriceMaster.child_id = Convert.ToInt32(prodMigra.product_id);
newPriceMaster.site_id = site.Site_Id;
newPriceMaster.price_status = 1;
#region 成本
int cost = 0;
if (!string.IsNullOrEmpty(c.cost))
{
int.TryParse(c.cost, out cost);
}
else
{
cost = Convert.ToInt32(item.Item_Cost);
}
newPriceMaster.cost = cost;
#endregion
#region 售價
int price = 0;
if (!string.IsNullOrEmpty(c.price))
{
int.TryParse(c.price, out price);
}
else
{
price = Convert.ToInt32(item.Item_Money);
}
newPriceMaster.price = price;
#endregion
newPriceMaster.event_price = Convert.ToInt32(item.Event_Item_Money);
newPriceMaster.event_start = item.Event_Product_Start;
newPriceMaster.event_end = item.Event_Product_End;
ArrayList category_set = new ArrayList();
if (site.Site_Name.Trim().ToLower() == "chinatrust")
{
Product pro= _productMgr.Query(new Product { Product_Id = item.Product_Id }).FirstOrDefault();
if (pro == null)
{
c.msg = "商品不存在";
continue;
}
#region 更新該站臺商品的 product_category_set
bool result = true;
System.Text.RegularExpressions.Regex regx = new System.Text.RegularExpressions.Regex("^[0-9]*$");
string[] cateArray = c.display.Split(',');
foreach (string strcate in cateArray)
{
if (!string.IsNullOrEmpty(strcate))
{
if (regx.IsMatch(strcate))
{
ProductCategory query = _cateMgr.QueryAll(new ProductCategory { category_id = uint.Parse(strcate) }).FirstOrDefault();
if (query == null || strcate.Equals("0"))
{
c.msg = "display:" + strcate + " 不存在;";
result = false;
break;
}
else
{
ProductCategorySet category = new ProductCategorySet { Brand_Id = pro.Brand_Id, Category_Id = uint.Parse(strcate), Product_Id = prodMigra.product_id };
if (_productCategorySetMgr.Query(category).FirstOrDefault() == null)
{
category_set.Add(_productCategorySetMgr.Save(category));
}
else
{
c.msg = "display:" + strcate + " 已存在;";
result = false;
break;
}
}
}
else
{
c.msg = "display:" + strcate + " 格式不正確 ";
result = false;
break;
}
}
}
if (!result) continue;
#endregion
}
string str = string.Empty;
if (_priceMasterMgr.Save(newPriceMaster, null, category_set, ref str) > 0)
{
c.msg = "匯入成功";
}
else
{
c.msg = string.IsNullOrEmpty(str) ? "保存至數據庫失敗" : str;
}
}
}
#endregion
}
}
/// <summary>
/// 第二步保存和編輯數據 更新主表和product_category數據 處理product_category_set prod_promo prom_all prom_ticket
/// </summary>
/// <param name="model">query對象</param>
/// <param name="oldeventid">原來的event_type</param>
/// <returns>執行結果</returns>
public int Update(PromotionsAmountGiftQuery model, string oldeventid)
{
//實例dao對象
_ptDao = new PromoTicketDao(connStr);
_proCateDao = new ProductCategoryDao(connStr);
_prodCategSetDao = new ProductCategorySetDao(connStr);
_prodpromoDao = new ProdPromoDao(connStr);
int i = 0;
//聲明鏈接數據庫
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sbExSql = new StringBuilder();
try
{
model.Replace4MySQL();
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
//開啟事物
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
StringBuilder Sql = new StringBuilder();
mySqlCmd.CommandText = _prodpromoDao.DeleteProdProm(oldeventid);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//刪除ProdPromo
mySqlCmd.CommandText = _proAllDao.DelPromAll(oldeventid);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//刪除PromAll
//Sql.AppendFormat(_prodpromoDao.DeleteProdProm(model.event_id));
//處理表product_category_set 和 prod_promo
if (model.quanguan == 1)
{
//根據category_id刪除product_category_set表數據
Sql.Clear();
Sql.AppendFormat(_prodCategSetDao.DeleteProdCateSet(model.category_id));
////添加product_category_set數據
ProductCategorySet pcs = new ProductCategorySet();
pcs.Category_Id = model.category_id;
pcs.Product_Id = 999999;
pcs.Brand_Id = 0;
Sql.AppendFormat(_prodCategSetDao.SaveProdCategorySet(pcs));
mySqlCmd.CommandText = Sql.ToString();
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//添加全館商品到PromAll
Sql.Clear();
}
else//非全館
{
//刪除全館商品
ProductCategorySet qgSet = new ProductCategorySet();
qgSet.Category_Id = model.category_id;
qgSet.Product_Id = 999999;//全館商品刪除 id=999999
//根據category_id刪除product_category_set表數據
mySqlCmd.CommandText = _prodCategSetDao.DelProdCateSetByCPID(qgSet);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//刪除全館別商品999999
if (model.url_by == 1)//專區時 add by shuangshuang0420j 20150309 11：25 區分專區和非專區
{
// Sql.Append(_prodpromoDao.DeleteProdProm(oldeventid));//修改前先刪除已有的數據
//選擇brand_id 的時候處理prod_promo 和product_category_set
if (model.brand_id != 0)//當品牌不為空時講該品牌下的所有商品加入set表
{
Sql.Append(_prodCategSetDao.DeleteProdCateSet(model.category_id));
mySqlCmd.CommandText = Sql.ToString();
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
Sql.Clear();
QueryVerifyCondition query = new QueryVerifyCondition();
query.brand_id = Convert.ToUInt32(model.brand_id);
query.site_ids = model.site.ToString();
PromotionsMaintainDao _promoMainDao = new PromotionsMaintainDao(connStr);
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.QueryByProSite(query, out totalCount, 0);
List<uint> categorysetProduct = new List<uint>();
foreach (QueryandVerifyCustom qvcItem in qvcList)
{
if (categorysetProduct.Contains(qvcItem.product_id))
{
continue;
}
categorysetProduct.Add(qvcItem.product_id);
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Product_Id = qvcItem.product_id;
pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
pcsModel.Category_Id = model.category_id;
Sql.Append(_prodCategSetDao.SaveProdCategorySet(pcsModel));
}
if (!string.IsNullOrEmpty(Sql.ToString()))
{
mySqlCmd.CommandText = Sql.ToString();
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
Sql.Clear();
}
}
}
else
{ //非專區時
mySqlCmd.CommandText = _prodCategSetDao.DeleteProdCateSet(model.category_id);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
if (model.product_id != 0)
{
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Product_Id = Convert.ToUInt32(model.product_id);
pcsModel.Brand_Id = _prodDao.Query(new Product { Product_Id = pcsModel.Product_Id }).FirstOrDefault().Brand_Id;
pcsModel.Category_Id = model.category_id;
//Sql.Append(_prodCategSetDao.SaveProdCategorySet(pcsModel));
mySqlCmd.CommandText = _prodCategSetDao.SaveProdCategorySet(pcsModel);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//新增商品關係
}
else if (model.brand_id != 0)
{
QueryVerifyCondition query = new QueryVerifyCondition();
query.brand_id = Convert.ToUInt32(model.brand_id);
query.site_ids = model.site;
query.combination = 1;
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.QueryByProSite(query, out totalCount, 0);
foreach (QueryandVerifyCustom qvcItem in qvcList)
{
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Product_Id = qvcItem.product_id;
pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
pcsModel.Category_Id = model.category_id;
// arryList.Add(_prodCategSet.SaveProdCategorySet(pcsModel));
mySqlCmd.CommandText = _prodCategSetDao.SaveProdCategorySet(pcsModel);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//新增商品關係
}
}
else if (model.class_id != 0)
{
List<VendorBrand> brandIDs = _vendorBrandDao.GetClassBrandList(new VendorBrand { }, (uint)model.class_id);
foreach (VendorBrand item in brandIDs)
{
QueryVerifyCondition query = new QueryVerifyCondition();
query.brand_id = item.Brand_Id;
query.site_ids = model.site;
query.combination = 1;
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.QueryByProSite(query, out totalCount, 0);
foreach (QueryandVerifyCustom qvcItem in qvcList)
{
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Product_Id = qvcItem.product_id;
pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
pcsModel.Category_Id = model.category_id;
//arryList.Add(_prodCategSet.SaveProdCategorySet(pcsModel));
mySqlCmd.CommandText = _prodCategSetDao.SaveProdCategorySet(pcsModel);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//新增商品關係
}
}
}
}
}
//新增表promo_all數據
// Sql.AppendFormat(_proAllDao.DelPromAll(model.event_id));
Sql.Clear();
PromoAll pamodel = new PromoAll();
pamodel.event_id = model.event_id;
pamodel.product_id = model.product_id;
pamodel.class_id = model.class_id;
pamodel.brand_id = model.brand_id;
pamodel.event_type = model.event_type;
pamodel.category_id = Convert.ToInt32(model.category_id);
pamodel.startTime = model.startdate;
pamodel.end = model.enddate;
pamodel.status = model.status;
pamodel.kuser = model.kuser;
pamodel.kdate = model.created;
pamodel.muser = model.muser;
pamodel.mdate = model.modified;
Sql.AppendFormat(_proAllDao.SavePromAll(pamodel));
//更新product_category表數據
ProductCategory pcmodel = _proCateDao.GetModelById(Convert.ToUInt32(model.category_id));
pcmodel.category_id = Convert.ToUInt32(model.category_id);
pcmodel.banner_image = model.banner_image;
pcmodel.category_link_url = model.category_link_url;
pcmodel.category_display = Convert.ToUInt32(model.status);
Sql.AppendFormat(_proCateDao.UpdateProdCate(pcmodel));
//判斷送禮類型是否是機會,若是的話 修改編輯表promo_ticket
if (model.gift_type == 2)
{ //新增數據到promo_ticket
if (model.ticket_id == 0)
{
PromoTicket pt = new PromoTicket();
pt.ticket_name = model.name;//機會name
pt.event_id = model.event_id;
pt.event_type = model.event_type;
pt.active_now = model.active_now;
pt.use_start = model.use_start;
pt.use_end = model.use_end;
pt.kuser = model.kuser;
pt.kdate = model.created;
pt.muser = model.muser;
pt.mdate = model.modified;
pt.valid_interval = model.valid_interval;
pt.status = model.status;
model.ticket_id = _ptDao.Save(pt);
model.ticket_name = pt.ticket_name;
}
//更新表數據
else
{
PromoTicket pt = _ptDao.Query(model.ticket_id);
pt.kuser = model.kuser;
pt.kdate = model.created;
pt.muser = model.muser;
pt.mdate = model.modified;
pt.ticket_name = model.ticket_name;//機會name
pt.event_id = model.event_id;
pt.event_type = model.event_type;
pt.active_now = model.active_now;
pt.use_start = model.use_start;
pt.use_end = model.use_end;
pt.valid_interval = model.valid_interval;
pt.status = model.status;
Sql.AppendFormat(_ptDao.UpdateSql(pt));
}
}
else if (model.ticket_id != 0)
{
Sql.AppendFormat(_ptDao.DeleteSql(model.ticket_id));
}
if (model.gift_type == 3 || model.gift_type == 4)//當贈送購物金或抵用券時產生gift_id
{
PromotionsAmountGiftCustom promGiftCustom = new PromotionsAmountGiftCustom();
promGiftCustom.product_id = 5669;
promGiftCustom.spec_type = 1;
promGiftCustom.spec_name = model.name;
promGiftCustom.Item_Alarm = 5;
promGiftCustom.Item_Stock = 9999;
mySqlCmd.CommandText = SaveProSpec(promGiftCustom);
uint specId = Convert.ToUInt32(mySqlCmd.ExecuteScalar());
promGiftCustom.Spec_Id_1 = specId;
mySqlCmd.CommandText = SaveProItem(promGiftCustom);
model.gift_id = Convert.ToInt32(mySqlCmd.ExecuteScalar());
}
//更新主表數據
Sql.AppendFormat(UpdatePromoGift(model));
mySqlCmd.CommandText = Sql.ToString();
sbExSql.Append(mySqlCmd.CommandText);
i += mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{//事物回滾
mySqlCmd.Transaction.Rollback();
throw new Exception("PromotionsAmountGiftDao-->Update-->" + ex.Message + sbExSql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return i;
}
public string SaveFromOtherPro(ProductCategorySet saveModel)
{
return _categorySetDao.SaveFromOtherPro(saveModel);
}
//add by wwei0216w 2015/2/24
/// <summary>
/// 根據商品id修改品牌id
/// </summary>
/// <param name="pcs">一個ProductCategorySet對象</param>
/// <returns>將要執行的sql語句</returns>
public string UpdateBrandId(ProductCategorySet pcs)
{
StringBuilder sb = new StringBuilder();
try
{
sb.AppendFormat(@"SET sql_safe_updates = 0;UPDATE product_category_set SET brand_id={0} WHERE product_id ={1};SET sql_safe_updates = 1;",pcs.Brand_Id,pcs.Product_Id);
return sb.ToString();
}
catch (Exception ex)
{
throw new Exception("ProductCategorySetDao-->UpdateBrandId" + ex.Message,ex);
}
}
public string SaveNoPrid(ProductCategorySet save)
{
StringBuilder stb = new StringBuilder("insert into product_category_set (`product_id`,`category_id`,`brand_id`) values ({0},");
stb.AppendFormat("{0},{1});", save.Category_Id, save.Brand_Id);
return stb.ToString();
}
public int DeleteProductByModel(ProductCategorySet delModel)
{
try
{
return _categorySetDao.DeleteProductByModel(delModel);
}
catch (Exception ex)
{
throw new Exception("ProductCategorySetMgr-->DeleteProductByModel-->" + ex.Message, ex);
}
}
/// <summary>
/// 修改狀態
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateActive(PromotionsAmountFareQuery model)
{
int i = 0;
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sql = new StringBuilder();
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
sql.Append(_prodPromoDao.DeleteProdProm(model.event_id));//先刪除在新增當active=0時照樣刪除只有active=1時才新增
if (model.active)
{
ProductCategorySet querySet = new ProductCategorySet();
querySet.Category_Id = Convert.ToUInt32(model.category_id);
ProductCategorySetDao _setDao = new ProductCategorySetDao(connStr);
List<ProductCategorySet> lmodelSet = _setDao.Query(querySet);
foreach (ProductCategorySet item in lmodelSet)
{
ProdPromo ppmodel = new ProdPromo();
ppmodel.product_id = Convert.ToInt32(item.Product_Id);
ppmodel.event_id = model.event_id;
ppmodel.event_type = model.event_type;
ppmodel.event_desc = model.event_desc;
ppmodel.start = model.start;
ppmodel.end = model.end;
ppmodel.page_url = model.category_link_url;
if (model.group_id == 0 && model.condition_id == 0)
{
ppmodel.user_specified = 0;
}
else
{
ppmodel.user_specified = 1;
}
ppmodel.kuser = model.muser;
ppmodel.kdate = model.modified;
ppmodel.muser = model.muser;
ppmodel.mdate = model.modified;
ppmodel.status = model.status;
sql.Append(_prodPromoDao.SaveProdProm(ppmodel));
}
}
if (model.id != 0)
{
sql.Append(UpdatePromoFare(model));
PromotionsAmountReduce parModel = new PromotionsAmountReduce();
parModel.id = model.id;
parModel.name = model.name;
parModel.delivery_store = model.delivery_store;
parModel.group_id = model.group_id;
parModel.type = model.type;
parModel.amount = model.amount;
parModel.quantity = model.quantity;
parModel.start = model.start;
parModel.end = model.end;
parModel.created = model.created;
parModel.updatetime = model.modified;
parModel.active = model.active ? 1 : 0;
parModel.status = model.status;
sql.Append(_promAmountReduceDao.Update(parModel));
}
mySqlCmd.CommandText = sql.ToString();
i += mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("PromotionsAmountFareDao-->UpdateActive-->" + ex.Message + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return i;
}
public override bool Exec()
{
bool result = true;
PriceMaster priceMaster = new PriceMaster();
foreach (MigrationDataSet item in mds)
{
result = true;
form.change(1);
//如果价格档原料编号新旧为new,则从对照表取出formula对应的product_id.
//if (item.New_old.Equals("new"))
//{
ProductMigrationMap mapQuery = _pMap.GetSingle(new ProductMigrationMap { temp_id = item.Formula });
if (mapQuery != null)
{
item.Formula = mapQuery.product_id.ToString();
}
else
{
item.OutMessage = "formula對照不存在";
result = false;
continue;
}
//}
#region 原逻辑
//如果价格档中无价格,则取formula对应PriceMaster中站台为gigade的价格.
//if (string.IsNullOrEmpty(item.Price) || string.IsNullOrEmpty(item.Cost))
//{
// PriceMaster priceQuery = _priceMgr.PriceMasterQuery(new PriceMaster { product_id = uint.Parse(item.Formula), site_id = 1 }).FirstOrDefault();
// if (priceQuery != null)
// {
// item.Cost = priceQuery.cost.ToString();
// item.Price = priceQuery.price.ToString();
// }
// else
// {
// item.OutMessage = "price,cost不存在";
// result = false;
// continue;
// }
//}
#endregion
//获取product_id对应product_item中的价格
if (!item.Product_id.Equals("0"))
{
ProductItem itemQuery = _prodItemMgr.Query(new ProductItem { Product_Id = uint.Parse(item.Product_id) }).FirstOrDefault();
if (itemQuery != null)
{
item.Cost = itemQuery.Item_Cost.ToString();
item.Price = itemQuery.Item_Money.ToString();
}
else
{
item.OutMessage = "product_item中无product_id信息;";
result = false;
continue;
}
}
else
{
item.OutMessage = "product_id不存在;";
result = false;
continue;
}
//price_master
priceMaster.product_id = uint.Parse(item.Formula);
priceMaster.cost = int.Parse(item.Cost);
priceMaster.price = int.Parse(item.Price);
priceMaster.price_status = 1;
priceMaster.user_level = 1;
priceMaster.default_bonus_percent = 1;
priceMaster.bonus_percent = 1;
priceMaster.site_id = uint.Parse(item.Site);
priceMaster.user_id = 0;
priceMaster.product_name = item.Product_name;
priceMaster.same_price = 1;
priceMaster.child_id = 0;
//item_price
List<ItemPrice> ipList = new List<ItemPrice>();
List<ProductItem> queryList = _prodItemMgr.Query(new ProductItem { Product_Id = uint.Parse(item.Formula) });
foreach (ProductItem proItem in queryList)
{
iPrice = new ItemPrice();
iPrice.item_id = proItem.Item_Id;
iPrice.item_money = uint.Parse(item.Price);
iPrice.item_cost = uint.Parse(item.Cost);
ipList.Add(iPrice);
}
ArrayList others = new ArrayList();
Site site = _siteMgr.Query(new Site { Site_Id = uint.Parse(item.Site) }).FirstOrDefault();
if (site.Site_Name.Trim().ToLower() == "chinatrust")
{
Product pro = _prodMgr.Query(new Product { Product_Id = mapQuery.product_id }).FirstOrDefault();
if (pro == null)
{
item.OutMessage = "商品不存在";
continue;
}
#region 更新該站臺商品的 product_category_set
string[] cateArray = item.Display.Split(',');
foreach (string strcate in cateArray)
{
ProductCategorySet category = new ProductCategorySet { Brand_Id = pro.Brand_Id, Category_Id = uint.Parse(strcate), Product_Id = mapQuery.product_id };
if (_productCategorySetMgr.Query(category).FirstOrDefault() == null)
{
others.Add(_productCategorySetMgr.Save(category));
}
else
{
item.OutMessage = "display:" + strcate + " 已存在;";
result = false;
break;
}
}
if (!result) continue;
}
#endregion
//保存
string strError = "";
if (_priceMgr.Save(priceMaster, ipList, others, ref strError) <= 0)
{
result = false;
item.OutMessage = strError;
if (strError == "")
{
item.OutMessage = "匯入失敗";
}
}
else
{
item.OutMessage = "匯入成功";
}
}
return result;
}
public bool DeleteProductByModelArry(string bids, string cids, string pids)
{
try
{
string[] arrybids = bids.Split('|');
string[] arrycids = cids.Split('|');
string[] arrypids = pids.Split('|');
ArrayList sqls = new ArrayList();
for (int i = 0; i < arrybids.Length; i++)
{
if (arrybids[i].ToString() != "" && arrypids[i].ToString() != "")
{
ProductCategorySet pcs = new ProductCategorySet();
pcs.Brand_Id = Convert.ToUInt32(arrybids[i].ToString());
pcs.Category_Id = Convert.ToUInt32(arrycids[i].ToString());
pcs.Product_Id = Convert.ToUInt32(arrypids[i].ToString());
sqls.Add(_categorySetDao.DeleteProductByModelStr(pcs));
}
}
return _sqlDao.ExcuteSqls(sqls);
}
catch (Exception ex)
{
throw new Exception("ProductCategorySetMgr-->DeleteProductByModelArry-->" + ex.Message, ex);
}
}
public int Update(PromotionsAmountFareQuery model, string oldEventId)
{
int i = 0;
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sbExSql = new StringBuilder();
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
StringBuilder sqlf = new StringBuilder();
mySqlCmd.CommandText = _prodPromoDao.DeleteProdProm(model.event_id);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//刪除ProdPromo
mySqlCmd.CommandText = _promoAllDao.DelPromAll(model.event_id);
sbExSql.Append(_promoAllDao.DelPromAll(mySqlCmd.CommandText));
mySqlCmd.ExecuteNonQuery();//刪除PromAll
if (model.allClass == 0)
{
//刪除全館商品
ProductCategorySet qgSet = new ProductCategorySet();
qgSet.Category_Id = model.category_id;
qgSet.Product_Id = 999999;//全館商品刪除 id=999999
//根據category_id刪除product_category_set表數據
mySqlCmd.CommandText = _prodCateSetDao.DelProdCateSetByCPID(qgSet);
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();//刪除全館別商品999999
if (model.brand_id != 0)//當品牌不為空時講該品牌下的所有商品加入set表
{
sqlf.Append(_prodCateSetDao.DeleteProdCateSet(model.category_id));
mySqlCmd.CommandText = sqlf.ToString();
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
sqlf.Clear();
QueryVerifyCondition query = new QueryVerifyCondition();//根據條件從product表中獲取符合條件的商品并添加到set和prodprom表中
query.brand_id = Convert.ToUInt32(model.brand_id);//品牌
query.site_ids = model.site;
query.combination = 1;
PromotionsMaintainDao _promoMainDao = new PromotionsMaintainDao(connStr);
query.IsPage = false;
int totalCount = 0;
List<QueryandVerifyCustom> qvcList = _promoMainDao.GetProList(query, out totalCount);
List<uint> categorysetProduct = new List<uint>();
foreach (QueryandVerifyCustom qvcItem in qvcList)
{
if (categorysetProduct.Contains(qvcItem.product_id))
{
continue;
}
categorysetProduct.Add(qvcItem.product_id);
ProductCategorySet pcsModel = new ProductCategorySet();
pcsModel.Product_Id = qvcItem.product_id;
pcsModel.Brand_Id = Convert.ToUInt32(model.brand_id);
pcsModel.Category_Id = model.category_id;
//刪除已有的 新增異動的
sqlf.Append(_prodCateSetDao.SaveProdCategorySet(pcsModel));
}
}
if (!string.IsNullOrEmpty(sqlf.ToString()))
{
mySqlCmd.CommandText = sqlf.ToString();
sbExSql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
sqlf.Clear();
}
}
else//全館時
{
sqlf.Clear();
sqlf.AppendFormat(_prodCateSetDao.DeleteProdCateSet(model.category_id));
ProductCategorySet pcs = new ProductCategorySet();
pcs.Category_Id = model.category_id;
pcs.Product_Id = 999999;
pcs.Brand_Id = 0;
sqlf.Append(_prodCateSetDao.SaveProdCategorySet(pcs));
mySqlCmd.CommandText = sqlf.ToString();
sbExSql.Append(_promoAllDao.DeletePromAll(mySqlCmd.CommandText));
mySqlCmd.ExecuteNonQuery();
}
sqlf.Clear();
PromoAll pamodel = new PromoAll();
pamodel.event_id = model.event_id;
pamodel.event_type = model.event_type;
pamodel.category_id = Convert.ToInt32(model.category_id);
pamodel.startTime = model.start;
pamodel.end = model.end;
pamodel.status = model.status;
pamodel.kuser = model.kuser;
pamodel.kdate = model.created;
pamodel.muser = model.muser;
pamodel.mdate = model.modified;
pamodel.product_id = model.product_id;
pamodel.class_id = model.class_id;
pamodel.brand_id = model.brand_id;
sqlf.AppendFormat(_promoAllDao.SavePromAll(pamodel));
ProductCategoryDao _categoryDao = new ProductCategoryDao(connStr);
ProductCategory pcmodel = _categoryDao.GetModelById(Convert.ToUInt32(model.category_id));
pcmodel.category_id = Convert.ToUInt32(model.category_id);
pcmodel.category_name = model.name;
pcmodel.banner_image = model.banner_image;
pcmodel.category_link_url = model.category_link_url;
pcmodel.category_father_id = model.category_father_id;
pcmodel.category_updatedate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime(model.modified.ToString());
pcmodel.category_display = Convert.ToUInt32(model.status);
sqlf.AppendFormat(_cateDao.UpdateProdCate(pcmodel));
PromotionsAmountReduce parModel = new PromotionsAmountReduce();
parModel.id = model.id;
parModel.name = model.name;
parModel.delivery_store = model.delivery_store;
parModel.group_id = model.group_id;
parModel.type = model.type;
parModel.amount = model.amount;
parModel.quantity = model.quantity;
parModel.start = model.start;
parModel.end = model.end;
parModel.updatetime = model.modified;
parModel.created = model.created;
parModel.active = model.active ? 1 : 0;
parModel.status = model.status;
sqlf.AppendFormat(_promAmountReduceDao.Update(parModel));
mySqlCmd.CommandText = sqlf + UpdatePromoFare(model);
i += mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("PromotionsAmountFareDao-->Update-->sql:" + sbExSql.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return i;
}
/// <summary>
/// 遞歸得到分類節點
/// </summary>
/// <param name="catelist">父節點</param>
public void GetCategoryList(List<ProductCategory> categoryList, ref List<ProductCategoryCustom> catelist, List<ProductCategorySetTemp> TempresultList, List<ProductCategorySet> resultList)
{
foreach (ProductCategoryCustom item in catelist)
{
List<ProductCategoryCustom> childList = getCate(categoryList, item.id.ToString());
item.children = childList;
ProductCategorySet resultTemp = new ProductCategorySet();
if (TempresultList != null)
{
resultTemp = TempresultList.Where(m => m.Category_Id.ToString() == item.id).FirstOrDefault();
}
else if (resultList != null)
{//正式數據不為null時對類表賦值
resultTemp = resultList.Where(m => m.Category_Id.ToString() == item.id).FirstOrDefault();
}
if (resultTemp != null)
{
item.Checked = true;
}
if (childList.Count() > 0)
{
GetCategoryList(categoryList, ref childList, TempresultList, resultList);
}
}
}
/// <summary>
/// 刪除全館數據
/// </summary>
/// <returns></returns>
public HttpResponseBase DeleteAllClassProductByModel()
{
ProductCategorySetMgr _categorySetMgr = new ProductCategorySetMgr(connectionString);
string categoryid = Request.Params["categoryid"];
string resultStr = "{success:false}";
ProductCategorySet pcs = new ProductCategorySet();
pcs.Brand_Id = 0;
pcs.Category_Id = Convert.ToUInt32(categoryid);
pcs.Product_Id = 999999;
try
{
_categorySetMgr.DeleteProductByModel(pcs);
resultStr = "{success:true}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
this.Response.Clear();
this.Response.Write(resultStr);
this.Response.End();
return this.Response;
}
public string UpdateProdCategorySet(ProductCategorySet saveModel)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" set sql_safe_updates = 0;update product_category_set set product_id='{0}',brand_id='{1}' where category_id='{2}';set sql_safe_updates = 1;", saveModel.Product_Id, saveModel.Brand_Id, saveModel.Category_Id);
return sb.ToString();
}
public void VendorGetCategoryList(List<ProductCategory> categoryList, ref List<ProductCategoryCustom> catelist, List<ProductCategorySetTemp> TempresultList, List<ProductCategorySet> resultList)
{ //Add 20140912 jialei by 用於商品管理供應商申請審核
foreach (ProductCategoryCustom item in catelist)
{
List<ProductCategoryCustom> childList = getCate(categoryList, uint.Parse(item.id.ToString()));
item.children = childList;
ProductCategorySet resultTemp = new ProductCategorySet();
if (TempresultList != null)
{
resultTemp = TempresultList.Where(m => m.Category_Id.ToString() == item.id).FirstOrDefault();
}
else if (resultList != null)
{
resultTemp = resultList.Where(m => m.Category_Id.ToString() == item.id).FirstOrDefault();
}
if (resultTemp != null)
{
item.Checked = true;
}
if (childList.Count() > 0)
{
VendorGetCategoryList(categoryList, ref childList, TempresultList, resultList);
}
}
}
public string DeleteProductByModelStr(ProductCategorySet delModel)
{
string sql = string.Format("set sql_safe_updates = 0;delete from product_category_set where product_id = {0} and category_id = {1} and brand_id={2};set sql_safe_updates = 1;", delModel.Product_Id, delModel.Category_Id, delModel.Brand_Id);
try
{
return sql;
}
catch (Exception ex)
{
throw new Exception("ProductCategorySetDao-->DeleteProductByModelStr-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 移除幾件幾元或幾件幾折
/// </summary>
/// <returns></returns>
public HttpResponseBase Deletezhe()
{
string jsonStr = String.Empty;
PromotionsAmountDiscount query = new PromotionsAmountDiscount();
ProductCategory pmodel = new ProductCategory();
ProductCategorySet pmsmodel = new ProductCategorySet();
ProductCategorySet querypcs = new ProductCategorySet();
PromoDiscount querypd = new PromoDiscount();
PromoAll pamodel = new PromoAll();
ProdPromo ppmodel = new ProdPromo();
querypcs.Category_Id = pmodel.category_id;
List<ProductCategorySet> smodel = _produCateSetMgr.Query(querypcs);
if (!String.IsNullOrEmpty(Request.Params["rowid"]))
{
try
{
foreach (string rid in Request.Params["rowid"].ToString().Split('|'))//批次移除
{
if (!string.IsNullOrEmpty(rid))
{
//刪除peomotion_amount_discount
//根據id 獲取整個model
query = _promoAmountDiscountMgr.GetModelById(Convert.ToInt32(rid));
var eventid = CommonFunction.GetEventId(query.event_type, query.id.ToString());
if (_promoAmountDiscountMgr.Delete(query, eventid) > 0)
{
jsonStr = "{success:true}";
}
else
{
jsonStr = "{success:false}";
}
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
jsonStr = "{success:false}";
}
}
this.Response.Clear();
this.Response.Write(jsonStr);
this.Response.End();
return this.Response;
}
public string SaveNoPrid(ProductCategorySet save)
{
try
{
return _categorySetDao.SaveNoPrid(save);
}
catch (Exception ex)
{
throw new Exception("ProductCategorySetMgr-->SaveNoPrid-->" + ex.Message, ex);
}
}
