public static bool InsertUploadBarCodeLog(SqlConnection conn, UploadBarCodeLog log)
{
string sql = @"INSERT [Configuration].dbo.UploadBarCodeLog ( UploadFileName ,
UploadFileAddress ,
FailFileName ,
FailFileAddress ,
CreateTime ,
LastUpdateDataTime
)
VALUES ( @uploadFileName ,
@uploadFileAddress ,
@failFileName ,
@failFileAddress ,
GETDATE(),
GETDATE()
)";
var sqlParam = new[]
{
new SqlParameter("@uploadFileName", log.UploadFileName),
new SqlParameter("@uploadFileAddress", log.UploadFileAddress),
new SqlParameter("@failFileName", log.FailFileName),
new SqlParameter("@failFileAddress", log.FailFileAddress),
};
return(SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sqlParam) > 0 ? true : false);
}
public bool InsertUploadBarCodeLog(UploadBarCodeLog log)
{
try
{
return(handler.InsertUploadBarCodeLog(log));
}
catch (TuhuBizException)
{
throw;
}
catch (Exception ex)
{
var exception = new DownloadAppException(1, "InsertUploadBarCodeLog", ex);
Logger.Log(Level.Error, exception, "InsertUploadBarCodeLog");
throw ex;
}
}
public bool InsertUploadBarCodeLog(UploadBarCodeLog log)
{
Func <SqlConnection, bool> action = (connection) => DalTireSecurityCodeConfig.InsertUploadBarCodeLog(connection, log);
return(dbManager.Execute(action));
}
public ActionResult UploadBarCodeLogImport(HttpPostedFileBase fileBase)
{
HttpPostedFileBase file = Request.Files["files"];
#region 【参数初始化】
string uploadFileName = "";
string uploadFileAddress = "";
string failFileName = "";
string failFileAddress = "";
string ext = "";
string noFileName = "";
string uploadDomain = "/TireLog/BarCode";
string barCodeBatchNum = DateTime.Now.ToString("yyyyMMddHHmmss");
#endregion
#region 【导入文件预判断】
if (file == null || file.ContentLength <= 0)
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件不能为空" }));
}
var filesize = file.ContentLength; //获取上传文件的大小单位为字节byte
ext = System.IO.Path.GetExtension(file.FileName); //获取上传文件的扩展名
noFileName = System.IO.Path.GetFileNameWithoutExtension(file.FileName); //获取无扩展名的文件名
const string fileType = ".xls,.xlsx"; //定义上传文件的类型字符串
if (string.IsNullOrWhiteSpace(ext))
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件扩展名不能为空" }));
}
if (!fileType.Contains(ext))
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件类型不对，只能导入xls和xlsx格式的文件" }));
}
#endregion
#region 【源文件上传】
var stream = file.InputStream;
var buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
uploadFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + '_' + noFileName + ext;
var result = FileUploadService.UploadFile(buffer, ext, uploadFileName, uploadDomain);
if (!string.IsNullOrWhiteSpace(result))
{
uploadFileAddress = "https://img1.tuhu.org" + result;
}
else
{
return(RedirectToAction("UploadSecurityCodeLog", "TireSecurityCode", new { msg = "文件上传失败" }));
}
UploadBarCodeLog barLog = new UploadBarCodeLog
{
UploadFileName = uploadFileName,
UploadFileAddress = uploadFileAddress,
Operator = ThreadIdentity.Operator.Name
};
#endregion
#region 【检查数据缺失】
stream.Position = 0;
var excel = new Controls.ExcelHelper(stream, file.FileName);
var dt = excel.ExcelToDataTable("sheet1", true);
if (buffer.Length > 0 && (dt == null || dt.Rows == null || dt.Rows.Count < 1))
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" }));
}
StringBuilder failSB = new StringBuilder();
List <InputBarCode> congifList = new List <InputBarCode>();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (null == dt.Rows[i]["条码"] || string.IsNullOrWhiteSpace(dt.Rows[i]["条码"].ToString()) ||
null == dt.Rows[i]["防伪码"] || string.IsNullOrWhiteSpace(dt.Rows[i]["防伪码"].ToString()))
{
failSB.AppendLine("第" + (i + 2) + "行:条码或防伪码数据不完整.");
}
else
{
congifList.Add(new InputBarCode
{
BarCode = dt.Rows[i]["条码"].ToString(),
SecurityCode = dt.Rows[i]["防伪码"].ToString(),
BarCodeBatchNum = barCodeBatchNum
});
}
}
if (failSB.Length > 0)
{
byte[] arrayFail_Params = Encoding.GetEncoding("gb2312").GetBytes(failSB.ToString());
failFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + '_' + noFileName + "_Error" + ".txt";
result = FileUploadService.UploadFile(arrayFail_Params, ".txt", failFileName, uploadDomain);
if (!string.IsNullOrWhiteSpace(result))
{
failFileAddress = "https://img1.tuhu.org" + result;
}
else
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" }));
}
barLog.FailFileName = failFileName;
barLog.FailFileAddress = failFileAddress;
barLog.CreateTime = DateTime.Now;
barLog.LastUpdateDataTime = DateTime.Now;
var insertLog_Params = TireSecurityCodeConfigManager.InsertUploadBarCodeLog(barLog);
if (insertLog_Params)
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "数据有缺失,请检查错误日志,整理数据后重新上传." }));
}
else
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" }));
}
}
#endregion
#region 【检查数据重复】
var checkSCodeDup = congifList
.GroupBy(item => item.SecurityCode)
.Select(item => item.First())
.ToList <InputBarCode>().Count;
if (checkSCodeDup < congifList.Count)
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "防伪码数据有重复，请Excel排查后重新上传." }));
}
var checkBarCodeDup = congifList
.GroupBy(item => item.BarCode)
.Select(item => item.First())
.ToList <InputBarCode>().Count;
if (checkBarCodeDup < congifList.Count)
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "条码数据有重复，请Excel排查后重新上传." }));
}
#endregion
#region 【批量更新数据】
congifList = congifList
.GroupBy(item => item.SecurityCode)
.Select(item => item.First())
.ToList <InputBarCode>();
var updateBarCode = TireSecurityCodeConfigManager.InsertBarCodeConfig(congifList);
List <InputBarCode> errorInputList = new List <InputBarCode>();
switch (updateBarCode)
{
case -2:
errorInputList = TireSecurityCodeConfigManager.QueryInputBarCodeByError("ItemNotExist", congifList);
failSB.AppendLine("以下防伪码还没上传过,请检查数据.");
failSB.AppendLine("防伪码,条码");
foreach (var error in errorInputList)
{
failSB.AppendLine(error.SecurityCode + "," + error.BarCode);
}
byte[] arrayFail_ItemNotExist = Encoding.GetEncoding("gb2312").GetBytes(failSB.ToString());
failFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + '_' + noFileName + "_Error" + ".txt";
result = FileUploadService.UploadFile(arrayFail_ItemNotExist, ".txt", failFileName, uploadDomain);
if (!string.IsNullOrWhiteSpace(result))
{
failFileAddress = "https://img1.tuhu.org" + result;
}
else
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" }));
}
barLog.FailFileName = failFileName;
barLog.FailFileAddress = failFileAddress;
barLog.CreateTime = DateTime.Now;
barLog.LastUpdateDataTime = DateTime.Now;
var insertLog_ItemNotExist = TireSecurityCodeConfigManager.InsertUploadBarCodeLog(barLog);
if (insertLog_ItemNotExist)
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "数据里有不存在的防伪码,请检查错误日志,整理数据后重新上传." }));
}
else
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" }));
}
case -3:
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "本次上传数据均存在,不做更新." }));
case -4:
errorInputList = TireSecurityCodeConfigManager.QueryInputBarCodeByError("Difference", congifList);
failSB.AppendLine("以下防伪码或条码已经存在过,请删除已存在数据后重新上传.");
failSB.AppendLine("防伪码,条码");
foreach (var error in errorInputList)
{
failSB.AppendLine(error.SecurityCode + "," + error.BarCode);
}
byte[] arrayFail_Difference = Encoding.GetEncoding("gb2312").GetBytes(failSB.ToString());
failFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + '_' + noFileName + "_Error" + ".txt";
result = FileUploadService.UploadFile(arrayFail_Difference, ".txt", failFileName, uploadDomain);
if (!string.IsNullOrWhiteSpace(result))
{
failFileAddress = "https://img1.tuhu.org" + result;
}
else
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" }));
}
barLog.FailFileName = failFileName;
barLog.FailFileAddress = failFileAddress;
barLog.CreateTime = DateTime.Now;
barLog.LastUpdateDataTime = DateTime.Now;
var insertLog_Difference = TireSecurityCodeConfigManager.InsertUploadBarCodeLog(barLog);
if (insertLog_Difference)
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "部分数据已存在,请检查错误日志,整理数据后重新上传." }));
}
else
{
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" }));
}
case -1:
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "上传失败,刷新页面重试." }));
}
#endregion
#region 【记录上传文件日志】
barLog.FailFileName = failFileName;
barLog.FailFileAddress = failFileAddress;
barLog.CreateTime = DateTime.Now;
barLog.LastUpdateDataTime = DateTime.Now;
var insertLog = TireSecurityCodeConfigManager.InsertUploadBarCodeLog(barLog);
if (!insertLog)
{
TireSecurityCodeConfigManager.DeleleBarCodeByBatchNum(barCodeBatchNum);
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "文件上传失败" }));
}
#endregion
return(RedirectToAction("UploadBarCodeLog", "TireSecurityCode", new { msg = "导入成功" }));
}