/// <summary>
/// 讀取Excel文件
/// </summary>
/// <param name="filePath"></param>
/// <param name="channel_id"></param>
/// <param name="fExtension"></param>
/// <param name="isHeaderError"></param>
/// <param name="errorPm"></param>
/// <returns></returns>
public List<ProductItemMapCustom> ReadFile(string filePath, int channel_id, string fExtension, ref bool isHeaderError, List<ProductItemMapCustom> errorPm)
{
try
{
NPOI4ExcelHelper fm = new NPOI4ExcelHelper(filePath);
DataTable dt = fm.SheetData();
if (dt == null) { return null; }
List<ProductItemMapCustom> result = new List<ProductItemMapCustom>();
Regex RegxProductId = new Regex("^\\d{5}$");
Regex RegxItemId = new Regex("^\\d{6}$");
Regex RegxMoney = new Regex("^\\d{1,9}$");
for (int i = 0, j = dt.Rows.Count; i < j; i++)
{
bool bl = true;
bool el = true;
bool isNull = false;
bool Pro_id = false;//記錄商品編號是否為空
bool Itm_id = false;//記錄商品細項編號是否為空
PriceMaster prm = new PriceMaster();
ProductItemMapCustom pm = new ProductItemMapCustom();
pm.channel_id = Convert.ToUInt32(channel_id);
for (int m = 0, n = dt.Columns.Count; m < n; m++)
{
string valStr = dt.Rows[i][m].ToString();
switch (dt.Columns[m].ToString().Trim())
{
case "商品編號(5碼)":
if (!string.IsNullOrEmpty(valStr))
{
uint _ProductId = 0;
uint.TryParse(valStr ?? "0", out _ProductId);
pm.product_id = _ProductId;
prm.product_id = _ProductId;
if (!RegxProductId.IsMatch(pm.product_id.ToString()))
{
bl = false;
}
}
else
{
Pro_id = true;
isNull = true;
break;
}
break;
case "商品細項編號(6碼)":
if (!string.IsNullOrEmpty(valStr))
{
if (valStr.Split(',').Length > 1)
{
for (int k = 0; k < valStr.Split(',').Length; k++)
{
if (!RegxItemId.IsMatch(valStr.Split(',')[k]))
{
bl = false;
}
}
pm.group_item_id = CommonFunction.Rank_ItemId(valStr);
}
else
{
uint _itemId = 0;
uint.TryParse(valStr ?? "0", out _itemId);
pm.item_id = _itemId;
pm.group_item_id = _itemId.ToString();
if (!RegxItemId.IsMatch(pm.item_id.ToString()))
{
bl = false;
}
}
}
else
{
Itm_id = true;
isNull = true;
break;
//bl = false;
};
break;
case "外站商品名稱":
if (!string.IsNullOrEmpty(valStr))
{
pm.product_name = valStr;
}
else
{
bl = false;
};
break;
case "外站商品編號":
if (!string.IsNullOrEmpty(valStr))
{
pm.channel_detail_id = valStr;
}
else
{
bl = false;
}; break;
case "外站商品成本":
if (!string.IsNullOrEmpty(valStr))
{
if (RegxMoney.IsMatch(valStr))
{
pm.product_cost = int.Parse(valStr);
}
else
{
bl = false;
}
}
else
{
bl = false;
}
; break;
case "外站商品售價":
if (!string.IsNullOrEmpty(valStr))
{
if (RegxMoney.IsMatch(valStr))
{
pm.product_price = int.Parse(valStr);
}
else
{
bl = false;
}
}
else
{
bl = false;
}
; break;
case "組合中之數量(0為照組合中之設定)":
if (!string.IsNullOrEmpty(valStr))
{
if (RegxMoney.IsMatch(valStr))
{
pm.set_num = uint.Parse(valStr);
}
else
{
bl = false;
}
}
else
{
bl = false;
} break;
case "user_email"://郵箱
if (!string.IsNullOrEmpty(valStr))
{
if (_callerMgr.Login(valStr) != null)
{
prm.user_id = uint.Parse(_callerMgr.Login(valStr).user_id.ToString());
}
}
break;
case "site_id"://站臺
if (!string.IsNullOrEmpty(valStr))
{
if (RegxMoney.IsMatch(valStr))
{
prm.site_id = uint.Parse(valStr);
}
else
{
bl = false;
}
}
else
{
bl = false;
}
break;
case "user_level"://站臺等級
if (!string.IsNullOrEmpty(valStr))
{
if (RegxMoney.IsMatch(valStr))
{
prm.user_level = uint.Parse(valStr);
}
else
{
bl = false;
}
}
else
{
bl = false;
}
break;
default:
el = false;
break;
}
//if (isNull)
//{
// break;
//}
}
//判斷Excel表頭格式
if (!el)
{
isHeaderError = true;
break;
}
else
{
isHeaderError = false;
}
//若單一商品可以無商品編號,若固定組合可以無商品細項編號。其餘類型出現為空數據,則此行記錄無效,不做處理
if (Itm_id)
{
if (_productItemMapDao.CombinationQuery(pm).FirstOrDefault() != null && _productItemMapDao.CombinationQuery(pm).FirstOrDefault().combination == 2)
{
List<ProductComboMap> pMc = _productItemMapDao.QueryItemId(pm.product_id);
if (pMc != null && pMc.Where(e => e.product_spec == 0).Count() == pMc.Count())
{
string strItem = "";
for (int Itemi = 0, Itemj = pMc.Count(); Itemi < Itemj; Itemi++)
{
if (Itemi > 0)
{
strItem += ",";
}
strItem += pMc[Itemi].item_id;
}
pm.group_item_id = CommonFunction.Rank_ItemId(strItem);
isNull = false;
}
}
}
//單一商品邏輯 add by hufeng0813w 2013/12/23
if (Pro_id)
{
ProductItem pIm = new ProductItem();
pIm.Item_Id = uint.Parse(pm.item_id.ToString());
//pm.product_id = _productItemDao.Query(pIm).FirstOrDefault().Product_Id;
ProductItem itemResult = _productItemDao.Query(pIm).FirstOrDefault();
if (itemResult != null)
{
prm.product_id = pm.product_id = itemResult.Product_Id;
ProductMapCustom mapcusResult = _productItemMapDao.CombinationQuery(pm).FirstOrDefault();
if (mapcusResult != null && mapcusResult.combination == 1)
{
isNull = false;
}
}
}
if (isNull)
{
continue;
}
//獲取price_master_id
if (prm.product_id != 0)
{
pm.price_master_id = _priceMasterMgr.QueryPriceMasterId(prm);
}
else
{
bl = false;
}
//判斷內容格式
if (!bl)
{
pm.msg = Resource.CoreMessage.GetResource("ERROR_FORMAT");
}
else
{
ProductMapCustom map = _productItemMapDao.CombinationQuery(pm).FirstOrDefault();
if (map != null)
{
uint Combination = map.combination;
if (Combination != 2)
{
ProductItem pi = new ProductItem();
pi.Item_Id = uint.Parse(pm.item_id.ToString());
if (_productItemMgr.Query(pi).Count == 0)
{
pm.msg = Resource.CoreMessage.GetResource("ITEMID_ID_NOT_EXISTS");
bl = false;
}
else
{
if (_productItemMapDao.Exist(pm) > 0)
{
pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
bl = false;
}
else
{
ProductItemMapCustom existItem = result.Where(m => m.item_id == pm.item_id).FirstOrDefault();
if (existItem != null)
{
pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
bl = false;
}
}
}
}
else
{
#region
for (int l = 0; l < pm.group_item_id.Split(',').Length; l++)
{
if (_productItemMapDao.Comb_Compare(pm.product_id, uint.Parse(pm.group_item_id.Split(',')[l])) == 0)
{
pm.msg = Resource.CoreMessage.GetResource("ITEMID_ID_NOT_COMBINATION");
bl = false;
}
else
{
#region
if (l == 0)
{
if (_productItemMapDao.Comb_Exist(pm) > 0)
{
pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
bl = false;
}
else
{
ProductItemMapCustom existItem = result.Where(m => m.group_item_id == pm.group_item_id).FirstOrDefault();
if (existItem != null)
{
pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
bl = false;
}
}
}
#endregion
}
}
#endregion
}
}
else
{
bl = false;
pm.msg = Resource.CoreMessage.GetResource("PRODUCT_NOT_EXIST");
}
}
if (!bl)
{
errorPm.Add(pm);
}
else
{
result.Add(pm);
}
}
return result; // _access.getObjByTable<ProductItemMap>(dt);
}
catch (Exception ex)
{
throw new Exception("ProductItemMapExcelMgr-->ReadFile-->" + ex.Message, ex);
}
}
//單一商品
public List<ProductItemMapCustom> SingleProductItemMapExc(PriceMasterCustom pmc)
{
IProductItemImplMgr _proItem = new ProductItemMgr(connectionString);
List<ProductItem> resultList = _proItem.Query(pmc);
List<ProductItemMapCustom> pimcs = new List<ProductItemMapCustom>();
int i=1;
foreach (var item in resultList)
{
var p = new ProductItemMapCustom();
p.channel_detail_id = pmc.product_id + ((resultList.Count > 1) ? ("-" + i) : "");//如果該商品只有一個子項,則不拼接序號
p.product_name = pmc.product_name + ((item.Spec_Name_1 == "" && item.Spec_Name_2 == "") ? "" : ("-" + item.Spec_Name_1 + item.Spec_Name_2));
p.product_id = pmc.product_id;
p.group_item_id = item.Item_Id.ToString() ;
p.product_price = pmc.price;
p.product_cost = pmc.cost;
p.site_id = pmc.site_id;
p.user_level = pmc.user_level;
p.user_id = pmc.user_id;
pimcs.Add(p);
i++;
}
return pimcs;
}
//組合商品
public List<ProductItemMapCustom> FixedComboProductItemMapExc(PriceMasterCustom pmc)
{
ProductItemMapMgr _mapMgr = new ProductItemMapMgr(connectionString);
List<ProductItemMapCustom> pimcs = new List<ProductItemMapCustom>();
List<ProductMapCustom> resultList = _ProductItemMapDao.CombinationQuery(
new ProductItemMapCustom { product_id = pmc.product_id }
);
if (resultList.Count() > 0)
{
List<List<ProductComboMap>> itemAll = new List<List<ProductComboMap>>();
for (int i = 0, j = resultList.Count(); i < j; i++)
{
List<ProductComboMap> itemList = _mapMgr.ProductComboItemQuery(resultList[i].child_id, (int)pmc.product_id);
if (itemList.Count > 0)//當子商品有規格時才可以建立對照
{
itemAll.Add(itemList);
}
}
if (itemAll.Count == 0) return pimcs;
string s = "";
getCombo(0, itemAll, "", ref s);
s = s.Remove(s.Length - 1);
string[] itemstr = s.Split('&');
for (int Si = 0, Sj = itemstr.Length; Si < Sj; Si++)
{
ProductItemMapCustom _productitemmap = new ProductItemMapCustom();
_productitemmap.channel_detail_id = pmc.product_id + ((Sj > 1) ? ("-" + (Si + 1)) : "");//如果組合種類為多種,則加序號
_productitemmap.product_name = pmc.product_name;
_productitemmap.product_id = pmc.product_id;
_productitemmap.group_item_id = itemstr[Si];
_productitemmap.product_price = pmc.price;
_productitemmap.product_cost = pmc.cost;
_productitemmap.site_id = pmc.site_id;
_productitemmap.user_level = pmc.user_level;
_productitemmap.user_id = pmc.user_id;
pimcs.Add(_productitemmap);
}
}
return pimcs;
}
/// <summary>
/// 匯出商品對照檔
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public MemoryStream ExportProductItemMap(QueryVerifyCondition query)
{
IProductImplMgr _prodMgr = new ProductMgr(connectionString);
List<ProductItemMapCustom> pims = new List<ProductItemMapCustom>();
List<Model.Custom.PriceMasterCustom> pmcs = _prodMgr.Query(query);
foreach (var pmc in pmcs)
{
switch (pmc.combination)
{
case 1://單一商品
var single = SingleProductItemMapExc(pmc);
pims.AddRange(single);
break;
case 2://固定組合
var fixedCombo = FixedComboProductItemMapExc(pmc);
pims.AddRange(fixedCombo);
break;
case 3:
case 4:
ProductItemMapCustom item = new ProductItemMapCustom();
item.channel_detail_id = Resource.CoreMessage.GetResource("MANUAL_OPERATING");
item.product_name = pmc.product_name;
item.product_id = pmc.product_id;
item.site_id = pmc.site_id;
item.user_level = pmc.user_level;
item.user_id = pmc.user_id;
pims.Add(item);
break;
default:
break;
}
}
//創建excel並填入數據
MemoryStream ms = new MemoryStream();
IWorkbook workBook = new HSSFWorkbook();
ISheet sheet = workBook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue(Resource.CoreMessage.GetResource("OUTSITE_PRODUCT_ID"));
headerRow.CreateCell(1).SetCellValue(Resource.CoreMessage.GetResource("OUTSITE_PRODUCT_NAME"));
headerRow.CreateCell(2).SetCellValue(Resource.CoreMessage.GetResource("PRODUCT_ID"));
headerRow.CreateCell(3).SetCellValue(Resource.CoreMessage.GetResource("ITEM_ID"));
headerRow.CreateCell(4).SetCellValue(Resource.CoreMessage.GetResource("COMBO_NUM_SET"));
headerRow.CreateCell(5).SetCellValue(Resource.CoreMessage.GetResource("OUTSITE_PRODUCT_PRICE"));
headerRow.CreateCell(6).SetCellValue("site_id");
headerRow.CreateCell(7).SetCellValue("user_level");
headerRow.CreateCell(8).SetCellValue("user_email");
int rowIndex=1;
foreach (var item in pims)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(item.channel_detail_id);
dataRow.CreateCell(1).SetCellValue(item.product_name);
dataRow.CreateCell(2).SetCellValue(item.product_id);
dataRow.CreateCell(3).SetCellValue(item.group_item_id);
dataRow.CreateCell(4).SetCellValue(0);//組合中之數量(0為照組合中之設定)
//dataRow.CreateCell(5).SetCellValue(item.product_cost);//外站商品成本
dataRow.CreateCell(5).SetCellValue(item.product_price);
dataRow.CreateCell(6).SetCellValue(item.site_id);
dataRow.CreateCell(7).SetCellValue(item.user_level);
dataRow.CreateCell(8).SetCellValue(item.user_id);
rowIndex++;
}
workBook.Write(ms);
ms.Flush();
ms.Position = 0;
workBook = null;
return ms;
}
public List<ProductMapCustom> CombinationQuery(ProductItemMapCustom p)
{
return _ProductItemMapDao.CombinationQuery(p);
}
/// <summary>
/// 根據itemmap表中的productid查找該商品是不是組合商品
/// </summary>
/// <param name="p">一條商品對照信息</param>
/// <returns></returns>
public List<ProductMapCustom> CombinationQuery(ProductItemMapCustom p)
{
StringBuilder sql = new StringBuilder("select p.combination,c.child_id,c.g_must_buy,c.pile_id,c.buy_limit,s.product_spec,p.product_name,p.prod_sz,m.cost,m.price,c.s_must_buy,p.price_type");
sql.AppendFormat(" from product p left join product_combo c on p.product_id = c.parent_id");
sql.AppendFormat(" left join product s on c.child_id = s.product_id");
//sql.AppendFormat(" left join price_master m on p.product_id=m.product_id and m.site_id=1 and m.user_level=1 and m.user_id=0 and m.child_id=p.product_id");
sql.AppendFormat(" left join price_master m on p.product_id=m.product_id and m.child_id=p.product_id and m.price_master_id={0}", p.price_master_id);
//edit by xiangwang0413w 根據price_master_id來查詢價格
sql.AppendFormat(" where p.product_id = {0} order by c.id", p.product_id);
return _accessMySql.getDataTableForObj<ProductMapCustom>(sql.ToString());
}
/// <summary>
/// 查詢商品對照是否存在(組合)
/// </summary>
/// <param name="p">一條商品信息</param>
/// <returns></returns>
public int Comb_Exist(ProductItemMapCustom p)
{
p.Replace4MySQL();
tempStr = string.Format("select count(rid) from product_item_map where channel_id={0} and product_id={1} and group_item_id='{2}'", p.channel_id, p.product_id, p.group_item_id);
if (p.channel_detail_id != "" && p.channel_detail_id != null)
{
tempStr += string.Format(" and channel_detail_id='{0}' ", p.channel_detail_id);
}
return int.Parse(_accessMySql.getDataTable(tempStr).Rows[0][0].ToString());
}
public void Save_Comb(ProductItemMapCustom p)
{
p.Replace4MySQL();
MySqlCommand Mycmd = new MySqlCommand(string.Format("insert into product_item_map (`channel_id`, `channel_detail_id`, `group_item_id`, `product_name`, `product_cost`, `product_price`,product_id ,price_master_id) values ({0},'{1}','{2}','{3}',{4},{5},{6},{7});select @@identity;", p.channel_id, p.channel_detail_id, p.group_item_id, p.product_name, p.product_cost, p.product_price, p.product_id, p.price_master_id), Mycon);
Mycon.Open();
int rid = int.Parse(Mycmd.ExecuteScalar().ToString());
Mycon.Close();
if (p.MapChild.Count > 0) //手動添加
{
for (int i = 0, j = p.MapChild.Count; i < j; i++)
{
tempStr = string.Format("insert into product_map_set(map_rid,item_id,set_num)values({0},{1},{2})", rid, p.MapChild[i].item_id, p.MapChild[i].set_num);
_accessMySql.execCommand(tempStr);
}
}
else //匯入對照
{
for (int i = 0; i < p.group_item_id.Split(',').Length; i++)
{
if (p.set_num == 0)
{
p.set_num = uint.Parse(_accessMySql.getDataTable(string.Format("select s_must_buy from product_combo where parent_id={0} and child_id =(select product_id from product_item where item_id={1})", p.product_id, p.group_item_id.Split(',')[i])).Rows[0][0].ToString());
}
tempStr = string.Format("insert into product_map_set(map_rid,item_id,set_num)values({0},{1},{2})", rid, uint.Parse(p.group_item_id.Split(',')[i]), p.set_num);
_accessMySql.execCommand(tempStr);
}
}
}