/// <summary>
/// 转换成产品信息列表
/// </summary>
/// <param name="excelPath"></param>
/// <returns></returns>
public static List <ProductImportDto> ConvertToProducts(string excelPath)
{
List <ProductImportDto> objs = new List <ProductImportDto>();
ExcelHelper helper = new ExcelHelper(excelPath);
var table = helper.ExcelToDataTable("Sheet1");
foreach (DataRow row in table.Rows)
{
string _sku = VariableHelper.SaferequestStr(row[7].ToString());
if (!string.IsNullOrEmpty(_sku))
{
var obj = new ProductImportDto
{
Name = VariableHelper.SaferequestStr(row[0].ToString()),
MatlGroup = VariableHelper.SaferequestStr(row[1].ToString()),
Description = VariableHelper.SaferequestStr(row[2].ToString()),
ProductName = VariableHelper.SaferequestStr(row[3].ToString()),
Material = VariableHelper.SaferequestStr(row[4].ToString()),
GdVal = VariableHelper.SaferequestStr(row[5].ToString()),
EAN = VariableHelper.SaferequestStr(row[6].ToString()),
SKU = VariableHelper.SaferequestStr(row[7].ToString()),
MarketPrice = VariableHelper.SaferequestDecimal(row[8].ToString()),
IsCommon = VariableHelper.SaferequestIntToBool(row[9].ToString()),
IsSet = VariableHelper.SaferequestIntToBool(row[10].ToString()),
IsGift = VariableHelper.SaferequestIntToBool(row[11].ToString()),
IsUsed = VariableHelper.SaferequestIntToBool(row[12].ToString()),
Result = true,
ResultMsg = string.Empty
};
objs.Add(obj);
}
}
return(objs);
}
/// <summary>
/// 保存产品信息
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static FuncResult SaveProducts(ProductImportDto item)
{
FuncResult _result = new FuncResult();
using (var db = new ebEntities())
{
StringBuilder sb = new StringBuilder();
try
{
if (item.IsCommon)
{
//只有sku不为空,Material长度为5位,GdVal长度为4位
if (!string.IsNullOrEmpty(item.SKU) && item.Material.Length == 5 && item.GdVal.Length == 4)
{
string _ProductId = item.Material + item.GdVal;
sb.AppendLine($"if exists(select * from Product where SKU = '{item.SKU}')");
sb.AppendLine("begin");
sb.AppendLine($"UPDATE Product set Name='{item.Name}',MatlGroup='{item.MatlGroup}',Description='{item.Description}',ProductName='{item.ProductName}',Material='{item.Material}',GdVal='{item.GdVal}',ProductId='{_ProductId}',EAN='{item.EAN}',MarketPrice={item.MarketPrice},IsDelete={VariableHelper.SaferequestBoolToInt(!item.IsUsed)},IsCommon={VariableHelper.SaferequestBoolToInt(item.IsCommon)},IsGift={VariableHelper.SaferequestBoolToInt(item.IsGift)},IsSet={VariableHelper.SaferequestBoolToInt(item.IsSet)},EditDate='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'");
sb.AppendLine($" WHERE SKU = '{item.SKU}';");
sb.AppendLine("end");
sb.AppendLine("else");
sb.AppendLine("begin");
sb.AppendLine("insert into Product(GroupDesc,Name,MatlGroup,Description,ProductName,Material,GdVal,ProductId,EAN,SupplyPrice,MarketPrice,SalesPrice,IsDelete,IsCommon,IsGift,IsSet,SKU,AddDate,EditDate)");
sb.AppendLine($"values('','{item.Name}','{item.MatlGroup}','{item.Description}','{item.ProductName}','{item.Material}','{item.GdVal}','{_ProductId}','{item.EAN}',0,{item.MarketPrice},0,{VariableHelper.SaferequestBoolToInt(!item.IsUsed)},{VariableHelper.SaferequestBoolToInt(item.IsCommon)},{VariableHelper.SaferequestBoolToInt(item.IsGift)},{VariableHelper.SaferequestBoolToInt(item.IsSet)},'{item.SKU}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')");
sb.AppendLine("end");
}
else
{
if (string.IsNullOrEmpty(item.SKU))
{
throw new Exception($"The SKU can not be empty!");
}
if (item.Material.Length != 5)
{
throw new Exception($"The length of Material must be 5!");
}
if (item.GdVal.Length != 4)
{
throw new Exception($"The length of GdVal must be 4!");
}
}
}
else
{
//只有sku不为空
if (!string.IsNullOrEmpty(item.SKU))
{
sb.AppendLine($"if exists(select * from Product where SKU = '{item.SKU}')");
sb.AppendLine("begin");
sb.AppendLine($"UPDATE Product set Name='{item.Name}',MatlGroup='{item.MatlGroup}',Description='{item.Description}',ProductName='{item.ProductName}',Material='{item.Material}',GdVal='{item.GdVal}',EAN='{item.EAN}',MarketPrice={item.MarketPrice},IsDelete={VariableHelper.SaferequestBoolToInt(!item.IsUsed)},IsCommon={VariableHelper.SaferequestBoolToInt(item.IsCommon)},IsGift={VariableHelper.SaferequestBoolToInt(item.IsGift)},IsSet={VariableHelper.SaferequestBoolToInt(item.IsSet)},EditDate='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'");
sb.AppendLine($" WHERE SKU = '{item.SKU}';");
sb.AppendLine("end");
sb.AppendLine("else");
sb.AppendLine("begin");
sb.AppendLine("insert into Product(GroupDesc,Name,MatlGroup,Description,ProductName,Material,GdVal,EAN,SupplyPrice,MarketPrice,SalesPrice,IsDelete,IsCommon,IsGift,IsSet,SKU,AddDate,EditDate)");
sb.AppendLine($"values('','{item.Name}','{item.MatlGroup}','{item.Description}','{item.ProductName}','{item.Material}','{item.GdVal}','{item.EAN}',0,{item.MarketPrice},0,{VariableHelper.SaferequestBoolToInt(!item.IsUsed)},{VariableHelper.SaferequestBoolToInt(item.IsCommon)},{VariableHelper.SaferequestBoolToInt(item.IsGift)},{VariableHelper.SaferequestBoolToInt(item.IsSet)},'{item.SKU}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')");
sb.AppendLine("end");
}
else
{
throw new Exception($"The SKU can not be empty!");
}
}
if (db.Database.ExecuteSqlCommand(sb.ToString()) > 0)
{
_result.Result = true;
_result.Message = string.Empty;
}
else
{
throw new Exception($"SKU:{item.SKU} save fail!");
}
}
catch (Exception ex)
{
_result.Result = false;
_result.Message = ex.Message;
}
}
return(_result);
}
