#region Excel批量导入
/// <summary>
/// Excel批量导入（form表单方式提交数据）
/// </summary>
/// <returns></returns>
[HttpPost]
public List<ReturnObject> UploadImportPrize()
{
List<ReturnObject> d = new List<ReturnObject>();
ReturnObject ret = new ReturnObject();
List<string> errors = new List<string>();
List<string> fails = new List<string>();
int Type = Convert.ToInt32(HttpContext.Current.Request["Type"]);
string completePath = "";
HttpFileCollection filelist = HttpContext.Current.Request.Files;
if (filelist != null && filelist.Count > 0)
{
for (int i = 0; i < filelist.Count; i++)
{
HttpPostedFile file = filelist[i];
String Tpath = "import/"; ;
string filename = DateTime.Now.ToString("HHmmss") +"-"+ file.FileName;
string FilePath = "C:\\" + Tpath + filename;
//这里应该获取当前项目路径地址，再在后面创建文件，如果按上面的注释掉的写法，在服务器上没有找到d盘，则会报错。
//string FilePath = System.Web.Hosting.HostingEnvironment.MapPath(@"~/") + filename;
string diPath = Path.GetDirectoryName(FilePath); //获取到当前目录的文件夹，没有就创建
if (!Directory.Exists(diPath)) { Directory.CreateDirectory(diPath); };
try
{
completePath = FilePath;
file.SaveAs(completePath); //生成一个文件目录，把上传的文件写入到目录中去，
d = ImportPrize(Type,completePath); //然后获取这个目录的文件，用DataTable进行读取，然后解析excel的每行数据，批量写入到数据库中
}
catch (Exception ex)
{
ret.msg = "上传文件写入失败：" + ex.Message;
ret.isOK = false;
ret.errorCode = 3;
d.Add(ret);
return d;
}
}
}
else
{
ret.msg = "上传的文件信息不存在！";
ret.isOK = false;
ret.errorCode = 3;
d.Add(ret);
return d;
}
return d;
}
/// <summary>
///
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private DataTable ReadExcelToTable(string path)
{
DataTable result = new DataTable();
Workbook workbook = new Workbook();
workbook.Open(path);
Cells cells = workbook.Worksheets[0].Cells;
result = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, false);
return result;
}
/// <summary>
/// 写入数据到数据库
/// </summary>
/// <param name="Type"></param>
/// <param name="completePath"></param>
/// <returns></returns>
[HttpGet]
public List<ReturnObject> ImportPrize(int Type, string completePath)
{
List<InPrize> list = new List<InPrize>();
List<ReturnObject> retlist = new List<ReturnObject>();
if (Type == 1)
{
var jinxing = DbHelperSQL.GetDataTable("SELECT Num from ProcessMain where StepState=1");
if (jinxing.Rows.Count > 0)
{
var bznum = jinxing.Rows[0]["Num"].ToString();
ReturnObject ret = new ReturnObject();
ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！";
ret.errorCode = 500;
retlist.Add(ret);
return retlist;
}
else
{
#region MyRegion
var a = ReadExcelToTable(completePath).Rows;
var msg = "";
var intStr = "";
var intStr1 = "";
var oil_zuoye = "";
var oil_jieshou = "";
var PhaseName = "";
var StepContent = "";
var str = "";
var RelatedValve = "";
var RelatedYb = "";
var cname = "";
var intStr9 = "";
var Manual = "";
var StepState = "";
for (var i = 2; i < a.Count; i++)
{
#region 错误信息判断
int intType1; intStr1 = a[i][0].ToString();
if (!int.TryParse(intStr1, out intType1))
{
ReturnObject ret = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！";
ret.isOK = false;
ret.msg = msg;
ret.errorCode = 500;
retlist.Add(ret);
}
int intType; intStr = a[i][1].ToString();
if (!int.TryParse(intStr, out intType))
{
ReturnObject ret2 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！";
ret2.isOK = false;
ret2.msg = msg;
ret2.errorCode = 500;
retlist.Add(ret2);
}
oil_zuoye = a[i][2].ToString();
if (string.IsNullOrWhiteSpace(oil_zuoye))
{
ReturnObject ret3 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！";
ret3.isOK = false;
ret3.msg = msg;
ret3.errorCode = 500;
retlist.Add(ret3);
}
oil_jieshou = a[i][3].ToString();
if (string.IsNullOrWhiteSpace(oil_jieshou))
{
ReturnObject ret4 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！";
ret4.isOK = false;
ret4.msg = msg;
ret4.errorCode = 500;
retlist.Add(ret4);
}
if (oil_zuoye == oil_jieshou)
{
ReturnObject ret21 = new ReturnObject();
msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！";
ret21.isOK = false;
ret21.msg = msg;
ret21.errorCode = 500;
retlist.Add(ret21);
}
PhaseName = a[i][4].ToString();
if (string.IsNullOrWhiteSpace(PhaseName))
{
ReturnObject ret5 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！";
ret5.isOK = false;
ret5.msg = msg;
ret5.errorCode = 500;
retlist.Add(ret5);
}
StepContent = a[i][5].ToString();
if (string.IsNullOrWhiteSpace(StepContent))
{
ReturnObject ret6 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！";
ret6.isOK = false;
ret6.msg = msg;
ret6.errorCode = 500;
retlist.Add(ret6);
}
str = "";
var RelatedJobs = a[i][6].ToString();
if (!string.IsNullOrWhiteSpace(RelatedJobs))
{
string[] id = RelatedJobs.Split(',');
for (int j = 0; j < id.Length; j++)
{
var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'");
if (tab.Rows.Count > 0)
{
var jobname = tab.Rows[0]["Job_Id"].ToString();
str += "" + jobname + ",";
}
else
{
ReturnObject ret7 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】";
ret7.isOK = false;
ret7.msg = msg;
ret7.errorCode = 500;
retlist.Add(ret7);
}
}
str = str.TrimEnd(',');
}
RelatedValve = a[i][7].ToString();
RelatedYb = a[i][8].ToString();
cname = "";
var Company = a[i][9].ToString();
if (!string.IsNullOrWhiteSpace(Company))
{
var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'");
if (tab.Rows.Count > 0)
{
cname = tab.Rows[0]["Company_Id"].ToString();
}
else
{
ReturnObject ret8 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！";
ret8.isOK = false;
ret8.msg = msg;
ret8.errorCode = 500;
retlist.Add(ret8);
}
}
int intType9; intStr9 = a[i][10].ToString();
if (!int.TryParse(intStr9, out intType9))
{
ReturnObject ret9 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！";
ret9.isOK = false;
ret9.msg = msg;
ret9.errorCode = 500;
retlist.Add(ret9);
}
int intType13; Manual = a[i][11].ToString();
if (!int.TryParse(Manual, out intType13))
{
ReturnObject ret10 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！";
ret10.isOK = false;
ret10.msg = msg;
ret10.errorCode = 500;
retlist.Add(ret10);
}
int intType14; StepState = a[i][12].ToString();
if (!int.TryParse(StepState, out intType14))
{
ReturnObject ret11 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！";
ret11.isOK = false;
ret11.msg = msg;
ret11.errorCode = 500;
retlist.Add(ret11);
}
//int intType15; Conten = a[i][13].ToString();
//if (!int.TryParse(Conten, out intType15))
//{
// ReturnObject ret12 = new ReturnObject();
// msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！";
// ret12.isOK = false;
// ret12.msg = msg;
// ret12.errorCode = 500;
// retlist.Add(ret12);
//}
#endregion
if (retlist.Count > 0)
{
return retlist;
}
else
{
list.Add(new InPrize()
{
ID = Guid.NewGuid().ToString(),
Type = Convert.ToInt32(intStr1),
Num = Convert.ToInt32(intStr),
JobOil = oil_zuoye,
RecepTionOil = oil_jieshou,
PhaseName = PhaseName,
StepContent = StepContent,
RelatedJobs = str,
RelatedValve = RelatedValve,
RelatedYb = RelatedYb,
Company = cname,
Confirmation = Convert.ToInt32(intStr9),
Manual = Convert.ToInt32(Manual),
StepState = Convert.ToInt32(StepState),
});
}
}
bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1);
if (isRepeat == true)
{
ReturnObject ret22 = new ReturnObject();
ret22.isOK = false;
ret22.msg = "步骤序号，有重复项请重新填写！";
ret22.errorCode = 500;
retlist.Add(ret22);
return retlist;
}
var CompanyName = a[2][9].ToString();
var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'");
cname = ta.Rows[0]["Company_Id"].ToString();
int tab22 = DbHelperSQL.GetDataTable("SELECT * from ProcessMain where Company='" + cname + "'").Rows.Count;
if (tab22 > 0)
{
var n4 = DbHelperSQL.ExecuteSql("DELETE from ProcessMain");
}
foreach (var n1 in list)
{
var sql = "INSERT INTO ProcessMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")";
var n2 = DbHelperSQL.ExecuteSql(sql);
}
return retlist;
#endregion
}
}
if (Type == 2)
{
var jinxing = DbHelperSQL.GetDataTable("SELECT Num from HairPortMain where StepState=1");
if (jinxing.Rows.Count > 0)
{
var bznum = jinxing.Rows[0]["Num"].ToString();
ReturnObject ret = new ReturnObject();
ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！";
ret.errorCode = 500;
retlist.Add(ret);
return retlist;
}
else
{
#region MyRegion
var a = ReadExcelToTable(completePath).Rows;
var msg = "";
var intStr = "";
var intStr1 = "";
var oil_zuoye = "";
var oil_jieshou = "";
var PhaseName = "";
var StepContent = "";
var str = "";
var RelatedValve = "";
var RelatedYb = "";
var cname = "";
var intStr9 = "";
var Manual = "";
var StepState = "";
for (var i = 2; i < a.Count; i++)
{
#region 错误信息判断
int intType1; intStr1 = a[i][0].ToString();
if (!int.TryParse(intStr1, out intType1))
{
ReturnObject ret = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！";
ret.isOK = false;
ret.msg = msg;
ret.errorCode = 500;
retlist.Add(ret);
}
int intType; intStr = a[i][1].ToString();
if (!int.TryParse(intStr, out intType))
{
ReturnObject ret2 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！";
ret2.isOK = false;
ret2.msg = msg;
ret2.errorCode = 500;
retlist.Add(ret2);
}
oil_zuoye = a[i][2].ToString();
if (string.IsNullOrWhiteSpace(oil_zuoye))
{
ReturnObject ret3 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！";
ret3.isOK = false;
ret3.msg = msg;
ret3.errorCode = 500;
retlist.Add(ret3);
}
oil_jieshou = a[i][3].ToString();
if (string.IsNullOrWhiteSpace(oil_jieshou))
{
ReturnObject ret4 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！";
ret4.isOK = false;
ret4.msg = msg;
ret4.errorCode = 500;
retlist.Add(ret4);
}
if (oil_zuoye == oil_jieshou)
{
ReturnObject ret21 = new ReturnObject();
msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！";
ret21.isOK = false;
ret21.msg = msg;
ret21.errorCode = 500;
retlist.Add(ret21);
}
PhaseName = a[i][4].ToString();
if (string.IsNullOrWhiteSpace(PhaseName))
{
ReturnObject ret5 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！";
ret5.isOK = false;
ret5.msg = msg;
ret5.errorCode = 500;
retlist.Add(ret5);
}
StepContent = a[i][5].ToString();
if (string.IsNullOrWhiteSpace(StepContent))
{
ReturnObject ret6 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！";
ret6.isOK = false;
ret6.msg = msg;
ret6.errorCode = 500;
retlist.Add(ret6);
}
str = "";
var RelatedJobs = a[i][6].ToString();
if (!string.IsNullOrWhiteSpace(RelatedJobs))
{
string[] id = RelatedJobs.Split(',');
for (int j = 0; j < id.Length; j++)
{
var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'");
if (tab.Rows.Count > 0)
{
var jobname = tab.Rows[0]["Job_Id"].ToString();
str += "" + jobname + ",";
}
else
{
ReturnObject ret7 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】";
ret7.isOK = false;
ret7.msg = msg;
ret7.errorCode = 500;
retlist.Add(ret7);
}
}
str = str.TrimEnd(',');
}
RelatedValve = a[i][7].ToString();
RelatedYb = a[i][8].ToString();
cname = "";
var Company = a[i][9].ToString();
if (!string.IsNullOrWhiteSpace(Company))
{
var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'");
if (tab.Rows.Count > 0)
{
cname = tab.Rows[0]["Company_Id"].ToString();
}
else
{
ReturnObject ret8 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！";
ret8.isOK = false;
ret8.msg = msg;
ret8.errorCode = 500;
retlist.Add(ret8);
}
}
int intType9; intStr9 = a[i][10].ToString();
if (!int.TryParse(intStr9, out intType9))
{
ReturnObject ret9 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！";
ret9.isOK = false;
ret9.msg = msg;
ret9.errorCode = 500;
retlist.Add(ret9);
}
int intType13; Manual = a[i][11].ToString();
if (!int.TryParse(Manual, out intType13))
{
ReturnObject ret10 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！";
ret10.isOK = false;
ret10.msg = msg;
ret10.errorCode = 500;
retlist.Add(ret10);
}
int intType14; StepState = a[i][12].ToString();
if (!int.TryParse(StepState, out intType14))
{
ReturnObject ret11 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！";
ret11.isOK = false;
ret11.msg = msg;
ret11.errorCode = 500;
retlist.Add(ret11);
}
//int intType15; Conten = a[i][13].ToString();
//if (!int.TryParse(Conten, out intType15))
//{
// ReturnObject ret12 = new ReturnObject();
// msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！";
// ret12.isOK = false;
// ret12.msg = msg;
// ret12.errorCode = 500;
// retlist.Add(ret12);
//}
#endregion
if (retlist.Count > 0)
{
return retlist;
}
else
{
list.Add(new InPrize()
{
ID = Guid.NewGuid().ToString(),
Type = Convert.ToInt32(intStr1),
Num = Convert.ToInt32(intStr),
JobOil = oil_zuoye,
RecepTionOil = oil_jieshou,
PhaseName = PhaseName,
StepContent = StepContent,
RelatedJobs = str,
RelatedValve = RelatedValve,
RelatedYb = RelatedYb,
Company = cname,
Confirmation = Convert.ToInt32(intStr9),
Manual = Convert.ToInt32(Manual),
StepState = Convert.ToInt32(StepState),
});
}
}
bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1);
if (isRepeat == true)
{
ReturnObject ret22 = new ReturnObject();
ret22.isOK = false;
ret22.msg = "步骤序号，有重复项请重新填写！";
ret22.errorCode = 500;
retlist.Add(ret22);
return retlist;
}
var CompanyName = a[2][9].ToString();
var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'");
cname = ta.Rows[0]["Company_Id"].ToString();
int tab22 = DbHelperSQL.GetDataTable("SELECT * from HairPortMain where Company='" + cname + "'").Rows.Count;
if (tab22 > 0)
{
var n4 = DbHelperSQL.ExecuteSql("DELETE from HairPortMain");
}
foreach (var n1 in list)
{
var sql = "INSERT INTO HairPortMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")";
var n2 = DbHelperSQL.ExecuteSql(sql);
}
return retlist;
#endregion
}
}
#region 3
if (Type == 3)
{
var jinxing = DbHelperSQL.GetDataTable("SELECT Num from ReceMain where StepState=1");
if (jinxing.Rows.Count > 0)
{
var bznum = jinxing.Rows[0]["Num"].ToString();
ReturnObject ret = new ReturnObject();
ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！";
ret.errorCode = 500;
retlist.Add(ret);
return retlist;
}
else
{
#region MyRegion
var a = ReadExcelToTable(completePath).Rows;
var msg = "";
var intStr = "";
var intStr1 = "";
var oil_zuoye = "";
var oil_jieshou = "";
var PhaseName = "";
var StepContent = "";
var str = "";
var RelatedValve = "";
var RelatedYb = "";
var cname = "";
var intStr9 = "";
var Manual = "";
var StepState = "";
for (var i = 2; i < a.Count; i++)
{
#region 错误信息判断
int intType1; intStr1 = a[i][0].ToString();
if (!int.TryParse(intStr1, out intType1))
{
ReturnObject ret = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！";
ret.isOK = false;
ret.msg = msg;
ret.errorCode = 500;
retlist.Add(ret);
}
int intType; intStr = a[i][1].ToString();
if (!int.TryParse(intStr, out intType))
{
ReturnObject ret2 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！";
ret2.isOK = false;
ret2.msg = msg;
ret2.errorCode = 500;
retlist.Add(ret2);
}
oil_zuoye = a[i][2].ToString();
if (string.IsNullOrWhiteSpace(oil_zuoye))
{
ReturnObject ret3 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！";
ret3.isOK = false;
ret3.msg = msg;
ret3.errorCode = 500;
retlist.Add(ret3);
}
oil_jieshou = a[i][3].ToString();
if (string.IsNullOrWhiteSpace(oil_jieshou))
{
ReturnObject ret4 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！";
ret4.isOK = false;
ret4.msg = msg;
ret4.errorCode = 500;
retlist.Add(ret4);
}
if (oil_zuoye == oil_jieshou)
{
ReturnObject ret21 = new ReturnObject();
msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！";
ret21.isOK = false;
ret21.msg = msg;
ret21.errorCode = 500;
retlist.Add(ret21);
}
PhaseName = a[i][4].ToString();
if (string.IsNullOrWhiteSpace(PhaseName))
{
ReturnObject ret5 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！";
ret5.isOK = false;
ret5.msg = msg;
ret5.errorCode = 500;
retlist.Add(ret5);
}
StepContent = a[i][5].ToString();
if (string.IsNullOrWhiteSpace(StepContent))
{
ReturnObject ret6 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！";
ret6.isOK = false;
ret6.msg = msg;
ret6.errorCode = 500;
retlist.Add(ret6);
}
str = "";
var RelatedJobs = a[i][6].ToString();
if (!string.IsNullOrWhiteSpace(RelatedJobs))
{
string[] id = RelatedJobs.Split(',');
for (int j = 0; j < id.Length; j++)
{
var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'");
if (tab.Rows.Count > 0)
{
var jobname = tab.Rows[0]["Job_Id"].ToString();
str += "" + jobname + ",";
}
else
{
ReturnObject ret7 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】";
ret7.isOK = false;
ret7.msg = msg;
ret7.errorCode = 500;
retlist.Add(ret7);
}
}
str = str.TrimEnd(',');
}
RelatedValve = a[i][7].ToString();
RelatedYb = a[i][8].ToString();
cname = "";
var Company = a[i][9].ToString();
if (!string.IsNullOrWhiteSpace(Company))
{
var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'");
if (tab.Rows.Count > 0)
{
cname = tab.Rows[0]["Company_Id"].ToString();
}
else
{
ReturnObject ret8 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！";
ret8.isOK = false;
ret8.msg = msg;
ret8.errorCode = 500;
retlist.Add(ret8);
}
}
int intType9; intStr9 = a[i][10].ToString();
if (!int.TryParse(intStr9, out intType9))
{
ReturnObject ret9 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！";
ret9.isOK = false;
ret9.msg = msg;
ret9.errorCode = 500;
retlist.Add(ret9);
}
int intType13; Manual = a[i][11].ToString();
if (!int.TryParse(Manual, out intType13))
{
ReturnObject ret10 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！";
ret10.isOK = false;
ret10.msg = msg;
ret10.errorCode = 500;
retlist.Add(ret10);
}
int intType14; StepState = a[i][12].ToString();
if (!int.TryParse(StepState, out intType14))
{
ReturnObject ret11 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！";
ret11.isOK = false;
ret11.msg = msg;
ret11.errorCode = 500;
retlist.Add(ret11);
}
//int intType15; Conten = a[i][13].ToString();
//if (!int.TryParse(Conten, out intType15))
//{
// ReturnObject ret12 = new ReturnObject();
// msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！";
// ret12.isOK = false;
// ret12.msg = msg;
// ret12.errorCode = 500;
// retlist.Add(ret12);
//}
#endregion
if (retlist.Count > 0)
{
return retlist;
}
else
{
list.Add(new InPrize()
{
ID = Guid.NewGuid().ToString(),
Type = Convert.ToInt32(intStr1),
Num = Convert.ToInt32(intStr),
JobOil = oil_zuoye,
RecepTionOil = oil_jieshou,
PhaseName = PhaseName,
StepContent = StepContent,
RelatedJobs = str,
RelatedValve = RelatedValve,
RelatedYb = RelatedYb,
Company = cname,
Confirmation = Convert.ToInt32(intStr9),
Manual = Convert.ToInt32(Manual),
StepState = Convert.ToInt32(StepState),
});
}
}
bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1);
if (isRepeat == true)
{
ReturnObject ret22 = new ReturnObject();
ret22.isOK = false;
ret22.msg = "步骤序号，有重复项请重新填写！";
ret22.errorCode = 500;
retlist.Add(ret22);
return retlist;
}
var CompanyName = a[2][9].ToString();
var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'");
cname = ta.Rows[0]["Company_Id"].ToString();
int tab22 = DbHelperSQL.GetDataTable("SELECT * from ReceMain where Company='" + cname + "'").Rows.Count;
if (tab22 > 0)
{
var n4 = DbHelperSQL.ExecuteSql("DELETE from ReceMain");
}
foreach (var n1 in list)
{
var sql = "INSERT INTO ReceMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")";
var n2 = DbHelperSQL.ExecuteSql(sql);
}
return retlist;
#endregion
}
}
#endregion
#region 4
if (Type == 4)
{
var jinxing = DbHelperSQL.GetDataTable("SELECT Num from TankMain where StepState=1");
if (jinxing.Rows.Count > 0)
{
var bznum = jinxing.Rows[0]["Num"].ToString();
ReturnObject ret = new ReturnObject();
ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！";
ret.errorCode = 500;
retlist.Add(ret);
return retlist;
}
else
{
#region MyRegion
var a = ReadExcelToTable(completePath).Rows;
var msg = "";
var intStr = "";
var intStr1 = "";
var oil_zuoye = "";
var oil_jieshou = "";
var PhaseName = "";
var StepContent = "";
var str = "";
var RelatedValve = "";
var RelatedYb = "";
var cname = "";
var intStr9 = "";
var Manual = "";
var StepState = "";
for (var i = 2; i < a.Count; i++)
{
#region 错误信息判断
int intType1; intStr1 = a[i][0].ToString();
if (!int.TryParse(intStr1, out intType1))
{
ReturnObject ret = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！";
ret.isOK = false;
ret.msg = msg;
ret.errorCode = 500;
retlist.Add(ret);
}
int intType; intStr = a[i][1].ToString();
if (!int.TryParse(intStr, out intType))
{
ReturnObject ret2 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！";
ret2.isOK = false;
ret2.msg = msg;
ret2.errorCode = 500;
retlist.Add(ret2);
}
oil_zuoye = a[i][2].ToString();
if (string.IsNullOrWhiteSpace(oil_zuoye))
{
ReturnObject ret3 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！";
ret3.isOK = false;
ret3.msg = msg;
ret3.errorCode = 500;
retlist.Add(ret3);
}
oil_jieshou = a[i][3].ToString();
if (string.IsNullOrWhiteSpace(oil_jieshou))
{
ReturnObject ret4 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！";
ret4.isOK = false;
ret4.msg = msg;
ret4.errorCode = 500;
retlist.Add(ret4);
}
if (oil_zuoye == oil_jieshou)
{
ReturnObject ret21 = new ReturnObject();
msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！";
ret21.isOK = false;
ret21.msg = msg;
ret21.errorCode = 500;
retlist.Add(ret21);
}
PhaseName = a[i][4].ToString();
if (string.IsNullOrWhiteSpace(PhaseName))
{
ReturnObject ret5 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！";
ret5.isOK = false;
ret5.msg = msg;
ret5.errorCode = 500;
retlist.Add(ret5);
}
StepContent = a[i][5].ToString();
if (string.IsNullOrWhiteSpace(StepContent))
{
ReturnObject ret6 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！";
ret6.isOK = false;
ret6.msg = msg;
ret6.errorCode = 500;
retlist.Add(ret6);
}
str = "";
var RelatedJobs = a[i][6].ToString();
if (!string.IsNullOrWhiteSpace(RelatedJobs))
{
string[] id = RelatedJobs.Split(',');
for (int j = 0; j < id.Length; j++)
{
var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'");
if (tab.Rows.Count > 0)
{
var jobname = tab.Rows[0]["Job_Id"].ToString();
str += "" + jobname + ",";
}
else
{
ReturnObject ret7 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】";
ret7.isOK = false;
ret7.msg = msg;
ret7.errorCode = 500;
retlist.Add(ret7);
}
}
str = str.TrimEnd(',');
}
RelatedValve = a[i][7].ToString();
RelatedYb = a[i][8].ToString();
cname = "";
var Company = a[i][9].ToString();
if (!string.IsNullOrWhiteSpace(Company))
{
var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'");
if (tab.Rows.Count > 0)
{
cname = tab.Rows[0]["Company_Id"].ToString();
}
else
{
ReturnObject ret8 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！";
ret8.isOK = false;
ret8.msg = msg;
ret8.errorCode = 500;
retlist.Add(ret8);
}
}
int intType9; intStr9 = a[i][10].ToString();
if (!int.TryParse(intStr9, out intType9))
{
ReturnObject ret9 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！";
ret9.isOK = false;
ret9.msg = msg;
ret9.errorCode = 500;
retlist.Add(ret9);
}
int intType13; Manual = a[i][11].ToString();
if (!int.TryParse(Manual, out intType13))
{
ReturnObject ret10 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！";
ret10.isOK = false;
ret10.msg = msg;
ret10.errorCode = 500;
retlist.Add(ret10);
}
int intType14; StepState = a[i][12].ToString();
if (!int.TryParse(StepState, out intType14))
{
ReturnObject ret11 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！";
ret11.isOK = false;
ret11.msg = msg;
ret11.errorCode = 500;
retlist.Add(ret11);
}
//int intType15; Conten = a[i][13].ToString();
//if (!int.TryParse(Conten, out intType15))
//{
// ReturnObject ret12 = new ReturnObject();
// msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！";
// ret12.isOK = false;
// ret12.msg = msg;
// ret12.errorCode = 500;
// retlist.Add(ret12);
//}
#endregion
if (retlist.Count > 0)
{
return retlist;
}
else
{
list.Add(new InPrize()
{
ID = Guid.NewGuid().ToString(),
Type = Convert.ToInt32(intStr1),
Num = Convert.ToInt32(intStr),
JobOil = oil_zuoye,
RecepTionOil = oil_jieshou,
PhaseName = PhaseName,
StepContent = StepContent,
RelatedJobs = str,
RelatedValve = RelatedValve,
RelatedYb = RelatedYb,
Company = cname,
Confirmation = Convert.ToInt32(intStr9),
Manual = Convert.ToInt32(Manual),
StepState = Convert.ToInt32(StepState),
});
}
}
bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1);
if (isRepeat == true)
{
ReturnObject ret22 = new ReturnObject();
ret22.isOK = false;
ret22.msg = "步骤序号，有重复项请重新填写！";
ret22.errorCode = 500;
retlist.Add(ret22);
return retlist;
}
var CompanyName = a[2][9].ToString();
var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'");
cname = ta.Rows[0]["Company_Id"].ToString();
int tab22 = DbHelperSQL.GetDataTable("SELECT * from TankMain where Company='" + cname + "'").Rows.Count;
if (tab22 > 0)
{
var n4 = DbHelperSQL.ExecuteSql("DELETE from TankMain");
}
foreach (var n1 in list)
{
var sql = "INSERT INTO TankMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")";
var n2 = DbHelperSQL.ExecuteSql(sql);
}
return retlist;
#endregion
}
}
#endregion
#region 5
if (Type == 5)
{
var jinxing = DbHelperSQL.GetDataTable("SELECT Num from BrIngMain where StepState=1");
if (jinxing.Rows.Count > 0)
{
var bznum = jinxing.Rows[0]["Num"].ToString();
ReturnObject ret = new ReturnObject();
ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！";
ret.errorCode = 500;
retlist.Add(ret);
return retlist;
}
else
{
#region MyRegion
var a = ReadExcelToTable(completePath).Rows;
var msg = "";
var intStr = "";
var intStr1 = "";
var oil_zuoye = "";
var oil_jieshou = "";
var PhaseName = "";
var StepContent = "";
var str = "";
var RelatedValve = "";
var RelatedYb = "";
var cname = "";
var intStr9 = "";
var Manual = "";
var StepState = "";
for (var i = 2; i < a.Count; i++)
{
#region 错误信息判断
int intType1; intStr1 = a[i][0].ToString();
if (!int.TryParse(intStr1, out intType1))
{
ReturnObject ret = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！";
ret.isOK = false;
ret.msg = msg;
ret.errorCode = 500;
retlist.Add(ret);
}
int intType; intStr = a[i][1].ToString();
if (!int.TryParse(intStr, out intType))
{
ReturnObject ret2 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！";
ret2.isOK = false;
ret2.msg = msg;
ret2.errorCode = 500;
retlist.Add(ret2);
}
oil_zuoye = a[i][2].ToString();
if (string.IsNullOrWhiteSpace(oil_zuoye))
{
ReturnObject ret3 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！";
ret3.isOK = false;
ret3.msg = msg;
ret3.errorCode = 500;
retlist.Add(ret3);
}
oil_jieshou = a[i][3].ToString();
if (string.IsNullOrWhiteSpace(oil_jieshou))
{
ReturnObject ret4 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！";
ret4.isOK = false;
ret4.msg = msg;
ret4.errorCode = 500;
retlist.Add(ret4);
}
if (oil_zuoye == oil_jieshou)
{
ReturnObject ret21 = new ReturnObject();
msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！";
ret21.isOK = false;
ret21.msg = msg;
ret21.errorCode = 500;
retlist.Add(ret21);
}
PhaseName = a[i][4].ToString();
if (string.IsNullOrWhiteSpace(PhaseName))
{
ReturnObject ret5 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！";
ret5.isOK = false;
ret5.msg = msg;
ret5.errorCode = 500;
retlist.Add(ret5);
}
StepContent = a[i][5].ToString();
if (string.IsNullOrWhiteSpace(StepContent))
{
ReturnObject ret6 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！";
ret6.isOK = false;
ret6.msg = msg;
ret6.errorCode = 500;
retlist.Add(ret6);
}
str = "";
var RelatedJobs = a[i][6].ToString();
if (!string.IsNullOrWhiteSpace(RelatedJobs))
{
string[] id = RelatedJobs.Split(',');
for (int j = 0; j < id.Length; j++)
{
var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'");
if (tab.Rows.Count > 0)
{
var jobname = tab.Rows[0]["Job_Id"].ToString();
str += "" + jobname + ",";
}
else
{
ReturnObject ret7 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】";
ret7.isOK = false;
ret7.msg = msg;
ret7.errorCode = 500;
retlist.Add(ret7);
}
}
str = str.TrimEnd(',');
}
RelatedValve = a[i][7].ToString();
RelatedYb = a[i][8].ToString();
cname = "";
var Company = a[i][9].ToString();
if (!string.IsNullOrWhiteSpace(Company))
{
var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'");
if (tab.Rows.Count > 0)
{
cname = tab.Rows[0]["Company_Id"].ToString();
}
else
{
ReturnObject ret8 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！";
ret8.isOK = false;
ret8.msg = msg;
ret8.errorCode = 500;
retlist.Add(ret8);
}
}
int intType9; intStr9 = a[i][10].ToString();
if (!int.TryParse(intStr9, out intType9))
{
ReturnObject ret9 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！";
ret9.isOK = false;
ret9.msg = msg;
ret9.errorCode = 500;
retlist.Add(ret9);
}
int intType13; Manual = a[i][11].ToString();
if (!int.TryParse(Manual, out intType13))
{
ReturnObject ret10 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！";
ret10.isOK = false;
ret10.msg = msg;
ret10.errorCode = 500;
retlist.Add(ret10);
}
int intType14; StepState = a[i][12].ToString();
if (!int.TryParse(StepState, out intType14))
{
ReturnObject ret11 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！";
ret11.isOK = false;
ret11.msg = msg;
ret11.errorCode = 500;
retlist.Add(ret11);
}
//int intType15; Conten = a[i][13].ToString();
//if (!int.TryParse(Conten, out intType15))
//{
// ReturnObject ret12 = new ReturnObject();
// msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！";
// ret12.isOK = false;
// ret12.msg = msg;
// ret12.errorCode = 500;
// retlist.Add(ret12);
//}
#endregion
if (retlist.Count > 0)
{
return retlist;
}
else
{
list.Add(new InPrize()
{
ID = Guid.NewGuid().ToString(),
Type = Convert.ToInt32(intStr1),
Num = Convert.ToInt32(intStr),
JobOil = oil_zuoye,
RecepTionOil = oil_jieshou,
PhaseName = PhaseName,
StepContent = StepContent,
RelatedJobs = str,
RelatedValve = RelatedValve,
RelatedYb = RelatedYb,
Company = cname,
Confirmation = Convert.ToInt32(intStr9),
Manual = Convert.ToInt32(Manual),
StepState = Convert.ToInt32(StepState),
});
}
}
bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1);
if (isRepeat == true)
{
ReturnObject ret22 = new ReturnObject();
ret22.isOK = false;
ret22.msg = "步骤序号，有重复项请重新填写！";
ret22.errorCode = 500;
retlist.Add(ret22);
return retlist;
}
var CompanyName = a[2][9].ToString();
var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'");
cname = ta.Rows[0]["Company_Id"].ToString();
int tab22 = DbHelperSQL.GetDataTable("SELECT * from BrIngMain where Company='" + cname + "'").Rows.Count;
if (tab22 > 0)
{
var n4 = DbHelperSQL.ExecuteSql("DELETE from BrIngMain");
}
foreach (var n1 in list)
{
var sql = "INSERT INTO BrIngMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")";
var n2 = DbHelperSQL.ExecuteSql(sql);
}
return retlist;
#endregion
}
}
#endregion
#region 6
if (Type == 6)
{
var jinxing = DbHelperSQL.GetDataTable("SELECT Num from RecyMain where StepState=1");
if (jinxing.Rows.Count > 0)
{
var bznum = jinxing.Rows[0]["Num"].ToString();
ReturnObject ret = new ReturnObject();
ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！";
ret.errorCode = 500;
retlist.Add(ret);
return retlist;
}
else
{
#region MyRegion
var a = ReadExcelToTable(completePath).Rows;
var msg = "";
var intStr = "";
var intStr1 = "";
var oil_zuoye = "";
var oil_jieshou = "";
var PhaseName = "";
var StepContent = "";
var str = "";
var RelatedValve = "";
var RelatedYb = "";
var cname = "";
var intStr9 = "";
var Manual = "";
var StepState = "";
for (var i = 2; i < a.Count; i++)
{
#region 错误信息判断
int intType1; intStr1 = a[i][0].ToString();
if (!int.TryParse(intStr1, out intType1))
{
ReturnObject ret = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！";
ret.isOK = false;
ret.msg = msg;
ret.errorCode = 500;
retlist.Add(ret);
}
int intType; intStr = a[i][1].ToString();
if (!int.TryParse(intStr, out intType))
{
ReturnObject ret2 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！";
ret2.isOK = false;
ret2.msg = msg;
ret2.errorCode = 500;
retlist.Add(ret2);
}
oil_zuoye = a[i][2].ToString();
if (string.IsNullOrWhiteSpace(oil_zuoye))
{
ReturnObject ret3 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！";
ret3.isOK = false;
ret3.msg = msg;
ret3.errorCode = 500;
retlist.Add(ret3);
}
oil_jieshou = a[i][3].ToString();
if (string.IsNullOrWhiteSpace(oil_jieshou))
{
ReturnObject ret4 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！";
ret4.isOK = false;
ret4.msg = msg;
ret4.errorCode = 500;
retlist.Add(ret4);
}
if (oil_zuoye == oil_jieshou)
{
ReturnObject ret21 = new ReturnObject();
msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！";
ret21.isOK = false;
ret21.msg = msg;
ret21.errorCode = 500;
retlist.Add(ret21);
}
PhaseName = a[i][4].ToString();
if (string.IsNullOrWhiteSpace(PhaseName))
{
ReturnObject ret5 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！";
ret5.isOK = false;
ret5.msg = msg;
ret5.errorCode = 500;
retlist.Add(ret5);
}
StepContent = a[i][5].ToString();
if (string.IsNullOrWhiteSpace(StepContent))
{
ReturnObject ret6 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！";
ret6.isOK = false;
ret6.msg = msg;
ret6.errorCode = 500;
retlist.Add(ret6);
}
str = "";
var RelatedJobs = a[i][6].ToString();
if (!string.IsNullOrWhiteSpace(RelatedJobs))
{
string[] id = RelatedJobs.Split(',');
for (int j = 0; j < id.Length; j++)
{
var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'");
if (tab.Rows.Count > 0)
{
var jobname = tab.Rows[0]["Job_Id"].ToString();
str += "" + jobname + ",";
}
else
{
ReturnObject ret7 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】";
ret7.isOK = false;
ret7.msg = msg;
ret7.errorCode = 500;
retlist.Add(ret7);
}
}
str = str.TrimEnd(',');
}
RelatedValve = a[i][7].ToString();
RelatedYb = a[i][8].ToString();
cname = "";
var Company = a[i][9].ToString();
if (!string.IsNullOrWhiteSpace(Company))
{
var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'");
if (tab.Rows.Count > 0)
{
cname = tab.Rows[0]["Company_Id"].ToString();
}
else
{
ReturnObject ret8 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！";
ret8.isOK = false;
ret8.msg = msg;
ret8.errorCode = 500;
retlist.Add(ret8);
}
}
int intType9; intStr9 = a[i][10].ToString();
if (!int.TryParse(intStr9, out intType9))
{
ReturnObject ret9 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！";
ret9.isOK = false;
ret9.msg = msg;
ret9.errorCode = 500;
retlist.Add(ret9);
}
int intType13; Manual = a[i][11].ToString();
if (!int.TryParse(Manual, out intType13))
{
ReturnObject ret10 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！";
ret10.isOK = false;
ret10.msg = msg;
ret10.errorCode = 500;
retlist.Add(ret10);
}
int intType14; StepState = a[i][12].ToString();
if (!int.TryParse(StepState, out intType14))
{
ReturnObject ret11 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！";
ret11.isOK = false;
ret11.msg = msg;
ret11.errorCode = 500;
retlist.Add(ret11);
}
//int intType15; Conten = a[i][13].ToString();
//if (!int.TryParse(Conten, out intType15))
//{
// ReturnObject ret12 = new ReturnObject();
// msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！";
// ret12.isOK = false;
// ret12.msg = msg;
// ret12.errorCode = 500;
// retlist.Add(ret12);
//}
#endregion
if (retlist.Count > 0)
{
return retlist;
}
else
{
list.Add(new InPrize()
{
ID = Guid.NewGuid().ToString(),
Type = Convert.ToInt32(intStr1),
Num = Convert.ToInt32(intStr),
JobOil = oil_zuoye,
RecepTionOil = oil_jieshou,
PhaseName = PhaseName,
StepContent = StepContent,
RelatedJobs = str,
RelatedValve = RelatedValve,
RelatedYb = RelatedYb,
Company = cname,
Confirmation = Convert.ToInt32(intStr9),
Manual = Convert.ToInt32(Manual),
StepState = Convert.ToInt32(StepState),
});
}
}
bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1);
if (isRepeat == true)
{
ReturnObject ret22 = new ReturnObject();
ret22.isOK = false;
ret22.msg = "步骤序号，有重复项请重新填写！";
ret22.errorCode = 500;
retlist.Add(ret22);
return retlist;
}
var CompanyName = a[2][9].ToString();
var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'");
cname = ta.Rows[0]["Company_Id"].ToString();
int tab22 = DbHelperSQL.GetDataTable("SELECT * from RecyMain where Company='" + cname + "'").Rows.Count;
if (tab22 > 0)
{
var n4 = DbHelperSQL.ExecuteSql("DELETE from RecyMain");
}
foreach (var n1 in list)
{
var sql = "INSERT INTO RecyMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")";
var n2 = DbHelperSQL.ExecuteSql(sql);
}
return retlist;
#endregion
}
}
#endregion
else
{
var jinxing = DbHelperSQL.GetDataTable("SELECT Num from AirPortMain where StepState=1");
if (jinxing.Rows.Count > 0)
{
var bznum = jinxing.Rows[0]["Num"].ToString();
ReturnObject ret = new ReturnObject();
ret.msg = "第" + bznum + "步骤正在进行中，Execl不能导入！";
ret.errorCode = 500;
retlist.Add(ret);
return retlist;
}
else
{
#region MyRegion
var a = ReadExcelToTable(completePath).Rows;
var msg = "";
var intStr = "";
var intStr1 = "";
var oil_zuoye = "";
var oil_jieshou = "";
var PhaseName = "";
var StepContent = "";
var str = "";
var RelatedValve = "";
var RelatedYb = "";
var cname = "";
var intStr9 = "";
var Manual = "";
var StepState = "";
for (var i = 2; i < a.Count; i++)
{
#region 错误信息判断
int intType1; intStr1 = a[i][0].ToString();
if (!int.TryParse(intStr1, out intType1))
{
ReturnObject ret = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【类型】应为整数类型数据！";
ret.isOK = false;
ret.msg = msg;
ret.errorCode = 500;
retlist.Add(ret);
}
int intType; intStr = a[i][1].ToString();
if (!int.TryParse(intStr, out intType))
{
ReturnObject ret2 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤序号】应为整数类型数据！";
ret2.isOK = false;
ret2.msg = msg;
ret2.errorCode = 500;
retlist.Add(ret2);
}
oil_zuoye = a[i][2].ToString();
if (string.IsNullOrWhiteSpace(oil_zuoye))
{
ReturnObject ret3 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【作业油罐】数据不能为空！";
ret3.isOK = false;
ret3.msg = msg;
ret3.errorCode = 500;
retlist.Add(ret3);
}
oil_jieshou = a[i][3].ToString();
if (string.IsNullOrWhiteSpace(oil_jieshou))
{
ReturnObject ret4 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【接收油罐】数据不能为空！";
ret4.isOK = false;
ret4.msg = msg;
ret4.errorCode = 500;
retlist.Add(ret4);
}
if (oil_zuoye == oil_jieshou)
{
ReturnObject ret21 = new ReturnObject();
msg = "【第" + (i + 1) + "行】【作业油罐】和【接收油罐】数据不能相同！";
ret21.isOK = false;
ret21.msg = msg;
ret21.errorCode = 500;
retlist.Add(ret21);
}
PhaseName = a[i][4].ToString();
if (string.IsNullOrWhiteSpace(PhaseName))
{
ReturnObject ret5 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【阶段名称】数据不能为空！";
ret5.isOK = false;
ret5.msg = msg;
ret5.errorCode = 500;
retlist.Add(ret5);
}
StepContent = a[i][5].ToString();
if (string.IsNullOrWhiteSpace(StepContent))
{
ReturnObject ret6 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤内容】数据不能为空！";
ret6.isOK = false;
ret6.msg = msg;
ret6.errorCode = 500;
retlist.Add(ret6);
}
str = "";
var RelatedJobs = a[i][6].ToString();
if (!string.IsNullOrWhiteSpace(RelatedJobs))
{
string[] id = RelatedJobs.Split(',');
for (int j = 0; j < id.Length; j++)
{
var tab = DbHelperSQL.GetDataTable("SELECT Job_Id,Job_Name from Sys_Job where Job_Name='" + id[j] + "'");
if (tab.Rows.Count > 0)
{
var jobname = tab.Rows[0]["Job_Id"].ToString();
str += "" + jobname + ",";
}
else
{
ReturnObject ret7 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【关联岗位】【" + id[j] + "岗位名称填写错误，请修改！】";
ret7.isOK = false;
ret7.msg = msg;
ret7.errorCode = 500;
retlist.Add(ret7);
}
}
str = str.TrimEnd(',');
}
RelatedValve = a[i][7].ToString();
RelatedYb = a[i][8].ToString();
cname = "";
var Company = a[i][9].ToString();
if (!string.IsNullOrWhiteSpace(Company))
{
var tab = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + Company + "'");
if (tab.Rows.Count > 0)
{
cname = tab.Rows[0]["Company_Id"].ToString();
}
else
{
ReturnObject ret8 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【所属单位】单位名称填写错误，请重新填写！";
ret8.isOK = false;
ret8.msg = msg;
ret8.errorCode = 500;
retlist.Add(ret8);
}
}
int intType9; intStr9 = a[i][10].ToString();
if (!int.TryParse(intStr9, out intType9))
{
ReturnObject ret9 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【是否双岗确认】应为整数类型数据！";
ret9.isOK = false;
ret9.msg = msg;
ret9.errorCode = 500;
retlist.Add(ret9);
}
int intType13; Manual = a[i][11].ToString();
if (!int.TryParse(Manual, out intType13))
{
ReturnObject ret10 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【手动/自动】应为整数类型数据！";
ret10.isOK = false;
ret10.msg = msg;
ret10.errorCode = 500;
retlist.Add(ret10);
}
int intType14; StepState = a[i][12].ToString();
if (!int.TryParse(StepState, out intType14))
{
ReturnObject ret11 = new ReturnObject();
msg = "格式错误，【第" + (i + 1) + "行】【步骤状态】应为整数类型数据！";
ret11.isOK = false;
ret11.msg = msg;
ret11.errorCode = 500;
retlist.Add(ret11);
}
//int intType15; Conten = a[i][13].ToString();
//if (!int.TryParse(Conten, out intType15))
//{
// ReturnObject ret12 = new ReturnObject();
// msg = "格式错误，【选项内容跳步】【第" + (i + 1) + "行】应为整数类型数据！";
// ret12.isOK = false;
// ret12.msg = msg;
// ret12.errorCode = 500;
// retlist.Add(ret12);
//}
#endregion
if (retlist.Count > 0)
{
return retlist;
}
else
{
list.Add(new InPrize()
{
ID = Guid.NewGuid().ToString(),
Type = Convert.ToInt32(intStr1),
Num = Convert.ToInt32(intStr),
JobOil = oil_zuoye,
RecepTionOil = oil_jieshou,
PhaseName = PhaseName,
StepContent = StepContent,
RelatedJobs = str,
RelatedValve = RelatedValve,
RelatedYb = RelatedYb,
Company = cname,
Confirmation = Convert.ToInt32(intStr9),
Manual = Convert.ToInt32(Manual),
StepState = Convert.ToInt32(StepState),
});
}
}
bool isRepeat = list.GroupBy(i => i.Num).Any(g => g.Count() > 1);
if (isRepeat == true)
{
ReturnObject ret22 = new ReturnObject();
ret22.isOK = false;
ret22.msg = "步骤序号，有重复项请重新填写！";
ret22.errorCode = 500;
retlist.Add(ret22);
return retlist;
}
var CompanyName = a[2][9].ToString();
var ta = DbHelperSQL.GetDataTable("SELECT Company_Id,Company_Name from Sys_Company where Company_Name='" + CompanyName + "'");
cname = ta.Rows[0]["Company_Id"].ToString();
int tab22 = DbHelperSQL.GetDataTable("SELECT * from AirPortMain where Company='" + cname + "'").Rows.Count;
if (tab22 > 0)
{
var n4 = DbHelperSQL.ExecuteSql("DELETE from AirPortMain");
}
foreach (var n1 in list)
{
var sql = "INSERT INTO AirPortMain ([ID] ,[Type] ,[Num] ,[JobOil] ,[RecepTionOil] ,[PhaseName] ,[StepContent] ,[RelatedJobs] ,[RelatedValve] ,[RelatedYb] ,[Company] ,[Confirmation] ,[Manual] ,[StepState] ) VALUES ('" + n1.ID + "'," + n1.Type + "," + n1.Num + ",'" + n1.JobOil + "','" + n1.RecepTionOil + "','" + n1.PhaseName + "','" + n1.StepContent + "','" + n1.RelatedJobs + "','" + n1.RelatedValve + "','" + n1.RelatedYb + "','" + n1.Company + "'," + n1.Confirmation + "," + n1.Manual + "," + n1.StepState + ")";
var n2 = DbHelperSQL.ExecuteSql(sql);
}
return retlist;
#endregion
}
}
}
public class ReturnObject
{
public bool isOK { get; set; }
public int errorCode { get; set; }
public string msg { get; set; }
}
public class InPrize
{
public string ID { get; set; }
public int Type { get; set; }
public int Num { get; set; }
public string JobOil { get; set; }
public string RecepTionOil { get; set; }
public string PhaseName { get; set; }
public string StepContent { get; set; }
public string RelatedJobs { get; set; }
public string RelatedValve { get; set; }
public string RelatedYb { get; set; }
public string Company { get; set; }
public int Confirmation { get; set; }
public string ConfirPerson { get; set; }
public string Twice { get; set; }
public string Reviewer { get; set; }
public int Manual { get; set; }
public int StepState { get; set; }
public string Batch { get; set; }
public string Images { get; set; }
public int Conten { get; set; }
}
public class HairPortMain
{
public string ID { get; set; }
public int Type { get; set; }
public int Num { get; set; }
public string JobOil { get; set; }
public string RecepTionOil { get; set; }
public string PhaseName { get; set; }
public string StepContent { get; set; }
public string RelatedJobs { get; set; }
public string RelatedValve { get; set; }
public string RelatedYb { get; set; }
public string Company { get; set; }
public int Confirmation { get; set; }
public string ConfirPerson { get; set; }
public string Twice { get; set; }
public string Reviewer { get; set; }
public int Manual { get; set; }
public int StepState { get; set; }
public string Batch { get; set; }
public string Images { get; set; }
public int Conten { get; set; }
}
#endregion
1.储油罐倒储油罐主表
|
HairPortMain |
|||
|
参数名称 |
参数描述 |
数据类型 |
备注 |
|
ID |
ID |
|
|
|
Type |
类型 |
Int |
1储油罐倒储油罐作业 |
|
Num |
步骤序号 |
int |
|
|
JobOil |
作业油罐 |
Nvarchar(50) |
油罐编号 |
|
RecepTionOil |
接收油罐 |
Nvarchar(50) |
油罐编号 |
|
PhaseName |
阶段名称 |
Nvarchar(50) |
|
|
StepContent |
步骤内容 |
Nvarchar(max) |
|
|
RelatedJobs |
关联岗位 |
Nvarchar(50) |
岗位信息表id |
|
RelatedValve |
关联阀门 |
Nvarchar(100) |
阀门编号 多个就用（,）拼接 |
|
RelatedYb |
关联油泵 |
Nvarchar(100) |
油泵编号 |
|
Company |
所属单位 |
Nvarchar(50) |
|
|
Confirmation |
是否双岗确认 |
int |
0否 1是 |
|
ConfirPerson |
确认人 |
Nvarchar(50) |
员工表id |
|
Twice |
二次确认人 |
Nvarchar(50) |
|
|
Reviewer |
复核人 |
Nvarchar(50) |
|
|
Manual |
手动/自动 |
Int |
0手动 1自动 2没有 |
|
StepState |
步骤状态 |
int |
0未开始 1进行中 2已结束 |
|
Batch |
流程批次 |
Nvarchar(50) |
|
|
JXTime |
流程进行时间 |
datetime |
|
|
Images |
图片 |
nvarchar(max) |
|
|
Conten |
选项内容 跳步 |
int |
1选项一 2选项二 3选项三 |
标签：批量,Company,ReturnObject,Excel,var,retlist,导入,ToString,msg From： https://www.cnblogs.com/canfengfeixue/p/16661855.html