/// <summary>
/// 导入Excel-轮胎PID
/// </summary>
/// <param name="model"></param>
/// <param name="list"></param>
/// <returns></returns>
public bool ImportTireActivityPid(TireActivityModel model, List <TireActivityPIDModel> list)
{
bool sucess = false;
int tireActivityID = AddTireActivity(model);
foreach (var item in list)
{
//检测Excel中的PID是否与己有计划的PID重复
var repeatList = GetTireActivityPidByPid(item.PID);
if (repeatList != null && repeatList.Count > 0)
{
foreach (var piditem in repeatList)
{
//重复的PID在被覆盖计划中删除
UpdateTireActivityPid(piditem.PKID, item.CreateBy);
//重复的PID所在计划的PID数量减去1
UpdateTireActivityPidNum(piditem.TireActivityID, item.CreateBy);
//如果重复的PID所在计划的PIDNum为0，则暂停该计划
var tireActivityModel = GetTireActivityModel(piditem.TireActivityID);
if (tireActivityModel != null && tireActivityModel.PIDNum == 0)
{
UpdateTireActivityStatus(piditem.TireActivityID, item.CreateBy);
}
}
}
item.TireActivityID = tireActivityID;
sucess = AddTireActivityPid(item) > 0;
}
return(sucess);
}
/// <summary>
/// 添加轮胎活动计划
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int AddTireActivity(TireActivityModel model)
{
try
{
return(DataAccess.DAO.TireActivity.DalTireActivity.AddTireActivity(model));
}
catch (Exception ex)
{
Logger.Error("AddTireActivity", ex);
throw ex;
}
}
public ActionResult AddTireActivityPid(TireActivityModel model, int notvalidCount, List <TireActivityPIDModel> list)
{
var success = TireActivityManager.ImportTireActivityPid(model, list);
if (success == true)
{
return(Json(new { code = 1, status = success, msg = "文件上传成功" }));
}
else
{
return(Json(new { code = 1, status = success, msg = "上传失败" }));
}
}
/// <summary>
/// 添加轮胎活动计划
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int AddTireActivity(TireActivityModel model)
{
var sql = @"INSERT INTO Activity.[dbo].[TireActivity]
([PlanNumber]
,[PlanName]
,[PlanDesc]
,[PIDNum]
,[Status]
,[UpdateID]
,[BeginDatetime]
,[EndDatetime]
,[CreateDatetime]
,[LastUpdateDateTime]
,[CreateBy]
,[LastUpdateBy]
)
VALUES ( @PlanNumber ,
@PlanName,
@PlanDesc,
@PIDNum,
@Status,
@UpdateID,
@BeginDatetime,
@EndDatetime,
GETDATE() ,
GETDATE(),
@CreateBy,
@LastUpdateBy
)
SELECT SCOPE_IDENTITY();";
using (var conn = new SqlConnection(ConnectionHelper.GetDecryptConn("Gungnir")))
{
var parameters = new[]
{
new SqlParameter("@PlanNumber", model.PlanNumber),
new SqlParameter("@PlanName", model.PlanName),
new SqlParameter("@PlanDesc", model.PlanDesc),
new SqlParameter("@PIDNum", model.PIDNum),
new SqlParameter("@Status", model.Status),
new SqlParameter("@UpdateID", model.UpdateID),
new SqlParameter("@BeginDatetime", model.BeginDatetime),
new SqlParameter("@EndDatetime", model.EndDatetime),
new SqlParameter("@CreateBy", model.CreateBy),
new SqlParameter("@LastUpdateBy", model.LastUpdateBy)
};
return(Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, parameters)));
}
}
public ActionResult ImportTireActivityPid(string planName, string planDesc, string beginDateTime, string endDateTime)
{
var list = new List <TireActivityPIDModel>();
#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文件" }));
}
var desc = Request["planName"].ToString();
#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的索引
for (int i = cellNum; i < titleRow.LastCellNum; i++)
{
if (titleRow.GetCell(i) != null)
{
if (getStringValueFunc(titleRow.GetCell(i)) == "PID")
{
pidNum = i;
}
}
}
var msgs = 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));
if (!string.IsNullOrEmpty(pid))
{
var item = new TireActivityPIDModel
{
PID = pid
};
var existsItem = list.FirstOrDefault(x =>
x.PID == item.PID
);
if (existsItem != null)
{
list.Remove(existsItem);
}
;
item.CreateBy = User.Identity.Name;
item.LastUpdateBy = User.Identity.Name;
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.GetValidTirePid(pidList).Select(x => x.PID).ToList(); //有效pid集合
var validMainPackageList = list.Where(x => validPidList.Contains(x.PID)).ToList(); //有效的轮胎活动PID
if (!validMainPackageList.Any())
{
return(Json(new { code = 1, status = false, msg = "轮胎PID全部无效!" }));
}
var tireActivityModel = new TireActivityModel();
tireActivityModel.PlanDesc = planDesc.Trim();
tireActivityModel.PlanName = planName.Trim();
tireActivityModel.BeginDatetime = DateTime.Parse(beginDateTime);
tireActivityModel.EndDatetime = DateTime.Parse(endDateTime);
tireActivityModel.PIDNum = validMainPackageList.Count;
var nowTime = DateTime.Now;
if (nowTime < tireActivityModel.BeginDatetime)
{
tireActivityModel.Status = 0;
}
if (tireActivityModel.BeginDatetime <= nowTime && nowTime <= tireActivityModel.EndDatetime)
{
tireActivityModel.Status = 1;
}
if (nowTime > tireActivityModel.EndDatetime)
{
tireActivityModel.Status = 2;
}
tireActivityModel.CreateBy = User.Identity.Name;
tireActivityModel.LastUpdateBy = User.Identity.Name;
int maxUpdateID = TireActivityManager.GetMaxTireActivityUpdateID();
tireActivityModel.UpdateID = maxUpdateID + 1;
tireActivityModel.PlanNumber = "计划" + (maxUpdateID + 2);
var notvalidCount = list.Where(x => !validPidList.Contains(x.PID)).ToList().Count;
#endregion
var repeatList = TireActivityManager.GetRepeatTirePids(validMainPackageList.Select(x => x.PID).ToList().ToList());
return(Json(new { code = 0, activityModel = tireActivityModel, list = validMainPackageList, repeatList = repeatList, notvalidCount = notvalidCount, validCount = validPidList.Count }));
}
