/// <summary>
/// 添加轮胎数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int AddTireActivityPid(TireActivityPIDModel model)
{
var sql = @"INSERT INTO Activity.[dbo].[TireActivityPID]
([TireActivityID]
,[PID]
,[Is_Deleted]
,[CreateDatetime]
,[LastUpdateDateTime]
,[CreateBy]
,[LastUpdateBy]
)
VALUES ( @TireActivityID ,
@PID,
@Is_Deleted,
GETDATE() ,
GETDATE(),
@CreateBy,
@LastUpdateBy
)
SELECT SCOPE_IDENTITY();";
using (var conn = new SqlConnection(ConnectionHelper.GetDecryptConn("Gungnir")))
{
var parameters = new[]
{
new SqlParameter("@TireActivityID", model.TireActivityID),
new SqlParameter("@PID", model.PID),
new SqlParameter("@Is_Deleted", model.Is_Deleted),
new SqlParameter("@CreateBy", model.CreateBy),
new SqlParameter("@LastUpdateBy", model.LastUpdateBy)
};
return(Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, parameters)));
}
}
/// <summary>
/// 添加轮胎数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int AddTireActivityPid(TireActivityPIDModel model)
{
try
{
return(DataAccess.DAO.TireActivity.DalTireActivity.AddTireActivityPid(model));
}
catch (Exception ex)
{
Logger.Error("AddTireActivityPid", ex);
throw ex;
}
}
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 }));
}