/// <summary>
/// 添加小保养套餐优惠价格数据
/// </summary>
/// <param name="model"></param>
/// <param name="maxUpdateID"></param>
/// <returns></returns>
public static int AddMaintenancePackageOnSale(MaintenancePackageOnSaleModel model, int maxUpdateID)
{
try
{
return(DataAccess.DAO.TireActivity.DalTireActivity.AddMaintenancePackageOnSale(model, maxUpdateID));
}
catch (Exception ex)
{
Logger.Error("AddMaintenancePackageOnSale", ex);
throw ex;
}
}
/// <summary>
/// 添加小保养套餐优惠价格数据
/// </summary>
/// <param name="model"></param>
/// <param name="maxUpdateID"></param>
/// <returns></returns>
public static int AddMaintenancePackageOnSale(MaintenancePackageOnSaleModel model, int maxUpdateID)
{
var sql = @"INSERT INTO Activity.[dbo].[MaintenancePackageOnSale]
([PID]
,[Price]
,[OnetirePrice]
,[TwotirePrice]
,[ThreetirePrice]
,[FourtirePrice]
,[UpdateID]
,[Status]
,[CreateBy]
,[LastUpdateBy]
,[CreateDatetime]
,[LastUpdateDateTime]
)
VALUES ( @PID ,
@Price,
@OnetirePrice,
@TwotirePrice,
@ThreetirePrice,
@FourtirePrice,
@UpdateID,
@Status,
@CreateBy,
@LastUpdateBy,
GETDATE() ,
GETDATE()
)
SELECT SCOPE_IDENTITY();";
using (var conn = new SqlConnection(ConnectionHelper.GetDecryptConn("Gungnir")))
{
var parameters = new[]
{
new SqlParameter("@PID", model.PID),
new SqlParameter("@Price", model.Price),
new SqlParameter("@OnetirePrice", model.OnetirePrice),
new SqlParameter("@TwotirePrice", model.TwotirePrice),
new SqlParameter("@ThreetirePrice", model.ThreetirePrice),
new SqlParameter("@FourtirePrice", model.FourtirePrice),
new SqlParameter("@UpdateID", maxUpdateID),
new SqlParameter("@Status", model.Status),
new SqlParameter("@CreateBy", model.CreateBy),
new SqlParameter("@LastUpdateBy", model.LastUpdateBy)
};
return(Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, parameters)));
}
}
public ActionResult ImportMaintenancePackage()
{
var list = new List <MaintenancePackageOnSaleModel>();
#region 验证文件
var files = Request.Files;
if (files == null || files.Count <= 0)
{
return(Json(new { code = 1, status = false, msg = "请先选择文件上传" }));
}
var file = files[0];
if (file.ContentType != "application/vnd.ms-excel" &&
file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
return(Json(new { code = 1, status = false, msg = "文件格式不正确, 请上传Excel文件" }));
}
#endregion
var stream = file.InputStream;
var buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
var workBook = new XSSFWorkbook(new MemoryStream(buffer));
var sheet = workBook.GetSheetAt(0);
#region 读取验证excel信息
Func <ICell, string> getStringValueFunc = cell =>
{
if (cell != null)
{
if (cell.CellType == CellType.Numeric)
{
return(DateUtil.IsCellDateFormatted(cell) ?
cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff") :
cell.NumericCellValue.ToString());
}
return(cell.StringCellValue?.Trim());
}
return(null);
};
var titleRow = sheet.GetRow(sheet.FirstRowNum);
var cellNum = titleRow.FirstCellNum;
int pidNum = 0; //小保养套餐PID的索引
int priceNum = 0; //原价索引
int oneTireNum = 0; //一条轮胎优惠价索引
int twoTireNum = 0; //二条轮胎优惠价索引
int threeTireNum = 0; //三条轮胎优惠价索引
int fourTireNum = 0; //四条轮胎优惠价索引
for (int i = cellNum; i < titleRow.LastCellNum; i++)
{
if (getStringValueFunc(titleRow.GetCell(i)) == "小保养套餐PID")
{
pidNum = i;
}
if (getStringValueFunc(titleRow.GetCell(i)) == "原价")
{
priceNum = i;
}
if (getStringValueFunc(titleRow.GetCell(i)) == "一条轮胎优惠价")
{
oneTireNum = i;
}
if (getStringValueFunc(titleRow.GetCell(i)) == "二条轮胎优惠价")
{
twoTireNum = i;
}
if (getStringValueFunc(titleRow.GetCell(i)) == "三条轮胎优惠价")
{
threeTireNum = i;
}
if (getStringValueFunc(titleRow.GetCell(i)) == "四条轮胎优惠价")
{
fourTireNum = i;
}
}
int invalidPriceCount = 0;
var msgs = new List <string>();
var allPidList = new List <string>();
for (var rowIndex = sheet.FirstRowNum + 1; rowIndex <= sheet.LastRowNum; rowIndex++)
{
var row = sheet.GetRow(rowIndex);
if (row != null)
{
var cellIndex = row.FirstCellNum;
var pid = getStringValueFunc(row.GetCell(pidNum));
allPidList.Add(pid);
if (string.IsNullOrWhiteSpace(pid))
{
continue;
}
Regex decimalPattern = new Regex("^[0-9]+(.[0-9]*)?$");
string priceString = getStringValueFunc(row.GetCell(priceNum));
string onePriceString = getStringValueFunc(row.GetCell(oneTireNum));
string twoPriceString = getStringValueFunc(row.GetCell(twoTireNum));
string threePriceString = getStringValueFunc(row.GetCell(threeTireNum));
string fourPriceString = getStringValueFunc(row.GetCell(fourTireNum));
if ((!string.IsNullOrWhiteSpace(priceString) && !decimalPattern.IsMatch(priceString)) || (!string.IsNullOrWhiteSpace(onePriceString) && !decimalPattern.IsMatch(onePriceString)) || (!string.IsNullOrWhiteSpace(twoPriceString) && !decimalPattern.IsMatch(twoPriceString)) ||
(!string.IsNullOrWhiteSpace(threePriceString) && !decimalPattern.IsMatch(threePriceString)) || (!string.IsNullOrWhiteSpace(fourPriceString) && !decimalPattern.IsMatch(fourPriceString)))
{
invalidPriceCount++;
continue;
}
decimal price = 0;
decimal oneTirePrice = 0;
decimal twoTirePrice = 0;
decimal threeTirePrice = 0;
decimal fourTirePrice = 0;
if (!string.IsNullOrWhiteSpace(priceString))
{
price = Convert.ToDecimal(priceString);
}
if (!string.IsNullOrWhiteSpace(onePriceString))
{
oneTirePrice = Convert.ToDecimal(onePriceString);
}
if (!string.IsNullOrWhiteSpace(twoPriceString))
{
twoTirePrice = Convert.ToDecimal(twoPriceString);
}
if (!string.IsNullOrWhiteSpace(threePriceString))
{
threeTirePrice = Convert.ToDecimal(threePriceString);
}
if (!string.IsNullOrWhiteSpace(fourPriceString))
{
fourTirePrice = Convert.ToDecimal(fourPriceString);
}
if (!string.IsNullOrEmpty(pid))
{
var item = new MaintenancePackageOnSaleModel
{
PID = pid,
Price = price,
OnetirePrice = oneTirePrice,
TwotirePrice = twoTirePrice,
ThreetirePrice = threeTirePrice,
FourtirePrice = fourTirePrice
};
int sameIndex = list.FindIndex(x => x.PID == item.PID);
if (sameIndex >= 0)
{
list[sameIndex].Status = 0;
}
item.CreateBy = User.Identity.Name;
item.LastUpdateBy = User.Identity.Name;
item.Status = 1;
list.Add(item);
}
}
}
if (!list.Any())
{
return(Json(new { code = 1, status = false, msg = "导入数据不能为空!" }));
}
#endregion
#region 小保养套餐PID验证
var pidList = list.Select(x => x.PID).ToList(); //excel中所有的pid集合
var validPidList = TireActivityManager.GetMaintenancePackagePidList(pidList).Select(x => x.PID).ToList(); //有效pid集合
var validMainPackageList = list.Where(x => validPidList.Contains(x.PID)).ToList(); //有效的小保养套餐数据
var allValidList = TireActivityManager.GetMaintenancePackagePidList(allPidList).Select(x => x.PID).ToList();
var invalidcount = allPidList.Where(x => !allValidList.Contains(x)).ToList().Count;//无效PID数量
if (!validMainPackageList.Any())
{
return(Json(new { code = 1, status = false, msg = "小保养套餐PID全部无效!" }));
}
#endregion
if (invalidcount == 0 && invalidPriceCount == 0)
{
return(AddMaintenancePackageOnSaleList(validMainPackageList));
}
else
{
return(Json(new { code = 0, data = validMainPackageList, invalidCount = invalidcount, invalidPriceCount = invalidPriceCount }));
}
}
