using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Models;
|
|
namespace VueWebCoreApi.Tools
|
{
|
public class ImportExcelData
|
{
|
public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
|
|
#region【Excel模板上传验证】
|
|
#region【Excel模板上传验证,组织架构模板】
|
public static string Zerro(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
List<DataTable> excelTable = new List<DataTable>();
|
excelTable = ImportExcel.ExcelToTableList(FileCode);
|
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,岗位管理模板】
|
public static string One(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 4)
|
{
|
code = "301";
|
Message = "岗位清单模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*岗位编号(唯一)")
|
{
|
code = "301";
|
Message = "岗位清单模板：表头信息不符合规范,第1列应为{*岗位编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*岗位名称")
|
{
|
code = "301";
|
Message = "岗位清单模板：表头信息不符合规范,第2列应为{*岗位名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*状态")
|
{
|
code = "301";
|
Message = "岗位清单模板：表头信息不符合规范,第3列应为{*状态}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "描述")
|
{
|
code = "301";
|
Message = "岗位清单模板：表头信息不符合规范,第4列应为{描述}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,班组管理模板】
|
public static string Two(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 4)
|
{
|
code = "301";
|
Message = "班组清单模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*班组编号(唯一)")
|
{
|
code = "301";
|
Message = "班组清单模板：表头信息不符合规范,第1列应为{*班组编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*班组名称")
|
{
|
code = "301";
|
Message = "班组清单模板：表头信息不符合规范,第2列应为{*班组名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*状态")
|
{
|
code = "301";
|
Message = "班组清单模板：表头信息不符合规范,第3列应为{*状态}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "描述")
|
{
|
code = "301";
|
Message = "班组清单模板：表头信息不符合规范,第4列应为{描述}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,人员管理模板】
|
public static string Three(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 11)
|
{
|
code = "301";
|
Message = "用户清单模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*用户编号(唯一)")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第1列应为{*用户编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*用户姓名")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第2列应为{*用户姓名}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*密码")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第3列应为{*密码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "*薪资类型")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第4列应为{*薪资类型}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "*在职状态")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第5列应为{*在职状态}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[5].ColumnName != "手机号")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第6列应为{手机号}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[6].ColumnName != "邮箱")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第7列应为{邮箱}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[7].ColumnName != "*所属组织编码")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第8列应为{*所属组织编码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[8].ColumnName != "所属岗位编码")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第9列应为{所属岗位编码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[9].ColumnName != "所属角色编码")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第10列应为{所属角色编码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[10].ColumnName != "所属班组编码")
|
{
|
code = "301";
|
Message = "用户清单模板：表头信息不符合规范,第11列应为{所属班组编码}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,角色管单模板】
|
public static string Four(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 4)
|
{
|
code = "301";
|
Message = "角色清单模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*角色编号(唯一)")
|
{
|
code = "301";
|
Message = "角色清单模板：表头信息不符合规范,第1列应为{*角色编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*角色名称")
|
{
|
code = "301";
|
Message = "角色清单模板：表头信息不符合规范,第2列应为{*角色名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*状态")
|
{
|
code = "301";
|
Message = "角色清单模板：表头信息不符合规范,第3列应为{*状态}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "描述")
|
{
|
code = "301";
|
Message = "角色清单模板：表头信息不符合规范,第4列应为{描述}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,往来单位模板】
|
public static string Five(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 2)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 3)
|
{
|
code = "301";
|
Message = "往来单位类型模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns.Count != 7)
|
{
|
code = "301";
|
Message = "往来单位模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*往来单位类型编号(唯一)")
|
{
|
code = "301";
|
Message = "往来单位类型模板：表头信息不符合规范,第1列应为{*往来单位类型编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*往来单位类型名称")
|
{
|
code = "301";
|
Message = "往来单位类型模板：表头信息不符合规范,第2列应为{*往来单位类型名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "上级编码")
|
{
|
code = "301";
|
Message = "往来单位类型模板：表头信息不符合规范,第3列应为{上级编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "*往来单位编号(唯一)")
|
{
|
code = "301";
|
Message = "往来单位模板：表头信息不符合规范,第1列应为{*往来单位编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "*往来单位名称")
|
{
|
code = "301";
|
Message = "往来单位模板：表头信息不符合规范,第2列应为{*往来单位名称}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "*状态")
|
{
|
code = "301";
|
Message = "往来单位模板：表头信息不符合规范,第3列应为{*状态}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "*往来单位类型编码")
|
{
|
code = "301";
|
Message = "往来单位模板：表头信息不符合规范,第4列应为{*往来单位类型编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "联系人")
|
{
|
code = "301";
|
Message = "往来单位模板：表头信息不符合规范,第5列应为{联系人}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[5].ColumnName != "联系方式")
|
{
|
code = "301";
|
Message = "往来单位模板：表头信息不符合规范,第6列应为{联系人}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[6].ColumnName != "地址")
|
{
|
code = "301";
|
Message = "往来单位模板：表头信息不符合规范,第7列应为{地址}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,仓库设置模板】
|
public static string Six(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 5)
|
{
|
code = "301";
|
Message = "仓库设置模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*仓库编号(唯一)")
|
{
|
code = "301";
|
Message = "仓库设置模板：表头信息不符合规范,第1列应为{*仓库编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*仓库名称")
|
{
|
code = "301";
|
Message = "仓库设置模板：表头信息不符合规范,第2列应为{*仓库名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*状态")
|
{
|
code = "301";
|
Message = "仓库设置模板：表头信息不符合规范,第3列应为{*状态}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "*库位管理")
|
{
|
code = "301";
|
Message = "仓库设置模板：表头信息不符合规范,第4列应为{*库位管理}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "描述")
|
{
|
code = "301";
|
Message = "仓库设置模板：表头信息不符合规范,第5列应为{描述}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,库位设置模板】
|
public static string Seven(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 6)
|
{
|
code = "301";
|
Message = "库位设置模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*库位编号(唯一)")
|
{
|
code = "301";
|
Message = "库位设置模板：表头信息不符合规范,第1列应为{*库位编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*库位名称")
|
{
|
code = "301";
|
Message = "库位设置模板：表头信息不符合规范,第2列应为{*库位名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*所属仓库编码")
|
{
|
code = "301";
|
Message = "库位设置模板：表头信息不符合规范,第3列应为{*所属仓库编码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "上级库位编码")
|
{
|
code = "301";
|
Message = "库位设置模板：表头信息不符合规范,第4列应为{上级库位编码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "*状态")
|
{
|
code = "301";
|
Message = "库位设置模板：表头信息不符合规范,第5列应为{*状态}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[5].ColumnName != "描述")
|
{
|
code = "301";
|
Message = "库位设置模板：表头信息不符合规范,第6列应为{描述}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,存货档案模板】
|
public static string Eight(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 2)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 3)
|
{
|
code = "301";
|
Message = "存货分类模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns.Count != 13)
|
{
|
code = "301";
|
Message = "存货档案模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*存货分类编号(唯一)")
|
{
|
code = "301";
|
Message = "存货分类型模板：表头信息不符合规范,第1列应为{*存货分类编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*存货分类名称")
|
{
|
code = "301";
|
Message = "存货分类型模板：表头信息不符合规范,第2列应为{*存货分类名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "上级编码")
|
{
|
code = "301";
|
Message = "存货分类型模板：表头信息不符合规范,第3列应为{上级编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "*存货编号(唯一)")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第1列应为{*存货编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "*存货名称")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第2列应为{*存货名称}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "规格型号")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第3列应为{规格型号}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "*存货分类编码")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第4列应为{*存货分类编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "*状态")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第5列应为{*状态}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[5].ColumnName != "*计量方式")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第6列应为{*计量方式}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[6].ColumnName != "*计量单位/组名称")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第7列应为{*计量单位/组名称}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[7].ColumnName != "所属仓库编码")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第8列应为{所属仓库编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[8].ColumnName != "是否外购")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第9列应为{是否外购}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[9].ColumnName != "是否销售")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第10列应为{是否销售}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[10].ColumnName != "是否自制")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第11列应为{是否自制}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[11].ColumnName != "是否生产消耗")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第12列应为{是否生产消耗}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[12].ColumnName != "是否委外")
|
{
|
code = "301";
|
Message = "存货档案模板：表头信息不符合规范,第13列应为{是否委外}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,设备清单模板】
|
public static string Nine(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 2)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 3)
|
{
|
code = "301";
|
Message = "设备类型模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns.Count != 5)
|
{
|
code = "301";
|
Message = "设备清单模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*设备类型编号(唯一)")
|
{
|
code = "301";
|
Message = "设备类型模板：表头信息不符合规范,第1列应为{*设备类型编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*设备类型名称")
|
{
|
code = "301";
|
Message = "设备类型模板：表头信息不符合规范,第2列应为{*设备类型名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "描述")
|
{
|
code = "301";
|
Message = "设备类型模板：表头信息不符合规范,第3列应为{描述}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "*设备编号(唯一)")
|
{
|
code = "301";
|
Message = "设备清单模板：表头信息不符合规范,第1列应为{*设备编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "*设备名称")
|
{
|
code = "301";
|
Message = "设备清单模板：表头信息不符合规范,第2列应为{*设备名称}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "*所属组织编码")
|
{
|
code = "301";
|
Message = "设备清单模板：表头信息不符合规范,第3列应为{*所属组织编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "*设备类型编码")
|
{
|
code = "301";
|
Message = "设备清单模板：表头信息不符合规范,第4列应为{*设备类型编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "*状态")
|
{
|
code = "301";
|
Message = "设备清单模板：表头信息不符合规范,第5列应为{*状态}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,设备点检项目模板】
|
public static string Ten(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 5)
|
{
|
code = "301";
|
Message = "设备点检项目模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*点检项目编号(唯一)")
|
{
|
code = "301";
|
Message = "设备点检项目模板：表头信息不符合规范,第1列应为{*点检项目编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*点检项目名称")
|
{
|
code = "301";
|
Message = "设备点检项目模板：表头信息不符合规范,第2列应为{*点检项目名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*点检周期")
|
{
|
code = "301";
|
Message = "设备点检项目模板：表头信息不符合规范,第3列应为{*点检周期}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "*是否扫码")
|
{
|
code = "301";
|
Message = "设备点检项目模板：表头信息不符合规范,第3列应为{*是否扫码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "点检项目要求")
|
{
|
code = "301";
|
Message = "设备点检项目模板：表头信息不符合规范,第3列应为{点检项目要求}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,设备点检标准模板】
|
public static string Ten_one(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 2)
|
{
|
code = "300";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 4)
|
{
|
code = "300";
|
Message = "设备点检标准页签模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns.Count != 7)
|
{
|
code = "300";
|
Message = "设备点检部位页签模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*设备点检标准编号(唯一)")
|
{
|
code = "300";
|
Message = "设备点检标准页签模板：表头信息不符合规范,第1列应为{*设备点检标准编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*设备点检标准名称")
|
{
|
code = "300";
|
Message = "设备点检标准页签模板：表头信息不符合规范,第2列应为{*设备点检标准名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*点检管控")
|
{
|
code = "300";
|
Message = "设备点检标准页签模板：表头信息不符合规范,第3列应为{*点检管控}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "标准描述")
|
{
|
code = "300";
|
Message = "设备点检标准页签模板：表头信息不符合规范,第4列应为{标准描述}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "*设备点检标准编号(M)")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第1列应为{*设备点检标准编号(M)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "*点检项序号")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第2列应为{*点检项序号}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "*设备点检部位编号(唯一)")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第3列应为{*设备点检部位编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "*设备点检部位名称")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第4列应为{*设备点检部位名称}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "点检部位要求")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第5列应为{点检部位要求}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[5].ColumnName != "*选择扫码")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第6列应为{*选择扫码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[6].ColumnName != "*点检周期")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第7列应为{*点检周期}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,设备保养项目模板】
|
public static string Eleven(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 2)
|
{
|
code = "300";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 4)
|
{
|
code = "300";
|
Message = "设备保养项目模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*保养项目编号(唯一)")
|
{
|
code = "300";
|
Message = "设备保养项目模板不符合规范：表头信息不符合规范,第1列应为{*保养项目编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*保养项目名称")
|
{
|
code = "300";
|
Message = "设备保养项目模板不符合规范：表头信息不符合规范,第2列应为{*保养项目名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*是否扫码")
|
{
|
code = "300";
|
Message = "设备保养项目模板不符合规范：表头信息不符合规范,第3列应为{*是否扫码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "保养项目要求")
|
{
|
code = "300";
|
Message = "设备保养项目模板不符合规范：表头信息不符合规范,第4列应为{保养项目要求}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,设备保养标准模板】
|
public static string Eleven_one(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 2)
|
{
|
code = "300";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 4)
|
{
|
code = "300";
|
Message = "设备保养标准页签模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns.Count != 6)
|
{
|
code = "300";
|
Message = "设备保养部位页签模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*设备保养标准编号(唯一)")
|
{
|
code = "300";
|
Message = "设备保养标准页签模板：表头信息不符合规范,第1列应为{*设备保养标准编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*设备保养标准名称")
|
{
|
code = "300";
|
Message = "设备保养标准页签模板：表头信息不符合规范,第2列应为{*设备保养标准名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*保养周期")
|
{
|
code = "300";
|
Message = "设备保养标准页签模板：表头信息不符合规范,第3列应为{*保养周期}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "标准描述")
|
{
|
code = "300";
|
Message = "设备保养标准页签模板：表头信息不符合规范,第4列应为{标准描述}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "*设备保养标准编号(M)")
|
{
|
code = "300";
|
Message = "设备保养部位页签模板：表头信息不符合规范,第1列应为{*设备保养标准编号(M)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "*保养项序号")
|
{
|
code = "300";
|
Message = "设备保养部位页签模板：表头信息不符合规范,第2列应为{*保养项序号}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "*设备保养部位编号(唯一)")
|
{
|
code = "300";
|
Message = "设备保养部位页签模板：表头信息不符合规范,第3列应为{*设备保养部位编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "*设备保养部位名称")
|
{
|
code = "300";
|
Message = "设备保养部位页签模板：表头信息不符合规范,第4列应为{*设备保养部位名称}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "保养部位要求")
|
{
|
code = "300";
|
Message = "设备保养部位页签模板：表头信息不符合规范,第5列应为{保养部位要求}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[5].ColumnName != "*选择扫码")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第6列应为{*选择扫码}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,模具清单模板】
|
public static string Twelve(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 2)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
if (excelTable[1].Rows.Count > 0)
|
{
|
if (excelTable[1].Columns.Count != 2)
|
{
|
code = "301";
|
Message = "模具关联产品模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "模具编码")
|
{
|
code = "301";
|
Message = "模具关联产品模板：表头信息不符合规范,第1列应为{模具编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "产品编码")
|
{
|
code = "301";
|
Message = "模具关联产品模板：表头信息不符合规范,第2列应为{产品编码}";
|
return Message;
|
}
|
}
|
else if (excelTable[0].Columns.Count != 8)
|
{
|
code = "301";
|
Message = "模具清单模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*模具编号(唯一)")
|
{
|
code = "301";
|
Message = "模具清单模板：表头信息不符合规范,第1列应为{*模具编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*模具名称")
|
{
|
code = "301";
|
Message = "模具清单模板：表头信息不符合规范,第2列应为{*模具名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "规格型号")
|
{
|
code = "301";
|
Message = "模具清单模板：表头信息不符合规范,第3列应为{规格型号}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "*状态")
|
{
|
code = "301";
|
Message = "模具清单模板：表头信息不符合规范,第4列应为{*状态}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "*仓库编码")
|
{
|
code = "301";
|
Message = "模具清单模板：表头信息不符合规范,第5列应为{*仓库编码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[5].ColumnName != "*库位编码")
|
{
|
code = "301";
|
Message = "模具清单模板：表头信息不符合规范,第6列应为{*库位编码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[6].ColumnName != "预计寿命(次)")
|
{
|
code = "301";
|
Message = "模具清单模板：表头信息不符合规范,第7列应为{预计寿命(次)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[7].ColumnName != "剩余寿命(次)")
|
{
|
code = "301";
|
Message = "模具清单模板：表头信息不符合规范,第8列应为{剩余寿命(次)}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,模具点检项模板】
|
public static string Thirteen(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 3)
|
{
|
code = "301";
|
Message = "模具点检项目模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*点检项目编号(唯一)")
|
{
|
code = "301";
|
Message = "模具点检项目模板：表头信息不符合规范,第1列应为{*点检项目编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*点检项目名称")
|
{
|
code = "301";
|
Message = "模具点检项目模板：表头信息不符合规范,第2列应为{*点检项目名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "要求描述")
|
{
|
code = "301";
|
Message = "模具点检项目模板：表头信息不符合规范,第3列应为{要求描述}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,模具点检标准模板】
|
public static string Thirteen_one(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 2)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 3)
|
{
|
code = "301";
|
Message = "模具点检标准模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns.Count != 5)
|
{
|
code = "301";
|
Message = "关联模具点检部位模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*模具点检标准编号(唯一)")
|
{
|
code = "301";
|
Message = "模具点检标准模板：表头信息不符合规范,第1列应为{*模具点检标准编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*模具点检标准名称")
|
{
|
code = "301";
|
Message = "模具点检标准模板：表头信息不符合规范,第2列应为{*模具点检标准名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "标准描述")
|
{
|
code = "301";
|
Message = "模具点检标准模板：表头信息不符合规范,第3列应为{标准描述}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "*模具点检标准编号(M)")
|
{
|
code = "301";
|
Message = "关联模具点检部位模板：表头信息不符合规范,第1列应为{*模具点检标准编号(M)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "*点检项序号")
|
{
|
code = "301";
|
Message = "关联模具点检部位模板：表头信息不符合规范,第2列应为{*点检项序号}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "*模具点检项编号(唯一)")
|
{
|
code = "301";
|
Message = "关联模具点检部位模板：表头信息不符合规范,第3列应为{*模具点检项编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "*模具点检项名称")
|
{
|
code = "301";
|
Message = "关联模具点检部位模板：表头信息不符合规范,第4列应为{*模具点检项名称}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "点检项要求")
|
{
|
code = "301";
|
Message = "关联模具点检部位模板：表头信息不符合规范,第5列应为{点检项要求}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,模具保养项模板】
|
public static string Fourteen(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 3)
|
{
|
code = "301";
|
Message = "模具保养项目模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*保养项目编号(唯一)")
|
{
|
code = "301";
|
Message = "模具保养项目模板：表头信息不符合规范,第1列应为{*保养项目编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*保养项目名称")
|
{
|
code = "301";
|
Message = "模具保养项目模板：表头信息不符合规范,第2列应为{*保养项目名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "要求描述")
|
{
|
code = "301";
|
Message = "模具保养项目模板：表头信息不符合规范,第3列应为{要求描述}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,模具保养标准模板】
|
public static string Fourteen_one(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 2)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 3)
|
{
|
code = "301";
|
Message = "模具保养标准模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns.Count != 5)
|
{
|
code = "301";
|
Message = "关联模具保养部位模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*模具保养标准编号(唯一)")
|
{
|
code = "301";
|
Message = "模具保养标准模板：表头信息不符合规范,第1列应为{*模具保养标准编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*模具保养标准名称")
|
{
|
code = "301";
|
Message = "模具保养标准模板：表头信息不符合规范,第2列应为{*模具保养标准名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "标准描述")
|
{
|
code = "301";
|
Message = "模具保养标准模板：表头信息不符合规范,第3列应为{标准描述}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "*模具保养标准编号(M)")
|
{
|
code = "301";
|
Message = "关联模具保养部位模板：表头信息不符合规范,第1列应为{*模具保养标准编号(M)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "*保养项序号")
|
{
|
code = "301";
|
Message = "关联模具保养部位模板：表头信息不符合规范,第2列应为{*保养项序号}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "*模具保养项编号(唯一)")
|
{
|
code = "301";
|
Message = "关联模具保养部位模板：表头信息不符合规范,第3列应为{*模具保养项编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "*模具保养项名称")
|
{
|
code = "301";
|
Message = "关联模具保养部位模板：表头信息不符合规范,第4列应为{*模具保养项名称}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "保养项要求")
|
{
|
code = "301";
|
Message = "关联模具保养部位模板：表头信息不符合规范,第5列应为{保养项要求}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,工序定义模板】
|
public static string Fifteen(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 5)
|
{
|
code = "301";
|
Message = "工序设置模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*工序编号(唯一)")
|
{
|
code = "301";
|
Message = "工序设置模板：表头信息不符合规范,第1列应为{*工序编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*工序名称")
|
{
|
code = "301";
|
Message = "工序设置模板：表头信息不符合规范,第2列应为{*工序名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "*工序类型")
|
{
|
code = "301";
|
Message = "工序设置模板：表头信息不符合规范,第3列应为{*工序类型}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "*状态")
|
{
|
code = "301";
|
Message = "工序设置模板：表头信息不符合规范,第1列应为{*状态}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "描述")
|
{
|
code = "301";
|
Message = "工序设置模板：表头信息不符合规范,第2列应为{描述}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,工艺路线模板】
|
public static string Sixteen(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
List<DataTable> excelTable = new List<DataTable>();
|
excelTable = ImportExcel.ExcelToTableList(FileCode);
|
if (excelTable.Count != 2)
|
{
|
code = "300";
|
Message = "模板不符合规范,缺失的Sheet";
|
}
|
else if (excelTable[0].Columns.Count != 6)
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名字段数";
|
}
|
else if (excelTable[1].Columns.Count != 9)
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名字段数";
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "工艺路线(唯一)")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "物料编码")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "物料名称")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "生产周期（天）")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[5].ColumnName != "结束装箱")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "工艺路线")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "SEQ(排列顺序)")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "工序编码")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "工序名称")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[5].ColumnName != "工序检验")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[6].ColumnName != "初始节拍（秒）")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[7].ColumnName != "初始型腔数")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[8].ColumnName != "初始工价（元）")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,节拍工价模板】
|
public static string TwentyFour(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
List<DataTable> excelTable = new List<DataTable>();
|
excelTable = ImportExcel.ExcelToTableList(FileCode);
|
if (excelTable.Count != 2)
|
{
|
code = "300";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 9)
|
{
|
code = "300";
|
Message = "节拍工价模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "节拍工价模板不符合规范：表头信息不符合规范,第1列应为{序号}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "产品名称")
|
{
|
code = "300";
|
Message = "产品名称模板不符合规范：表头信息不符合规范,第2列应为{产品名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "工艺路线名称")
|
{
|
code = "300";
|
Message = "节拍工价模板不符合规范：表头信息不符合规范,第3列应为{工艺路线名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "工序名称")
|
{
|
code = "300";
|
Message = "节拍工价模板不符合规范：表头信息不符合规范,第4列应为{工序名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "设备名称")
|
{
|
code = "300";
|
Message = "节拍工价模板不符合规范：表头信息不符合规范,第4列应为{设备名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[5].ColumnName != "计件单价")
|
{
|
code = "300";
|
Message = "节拍工价模板不符合规范：表头信息不符合规范,第4列应为{计件单价}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[6].ColumnName != "设备节拍")
|
{
|
code = "300";
|
Message = "节拍工价模板不符合规范：表头信息不符合规范,第4列应为{设备节拍}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[7].ColumnName != "生产节拍")
|
{
|
code = "300";
|
Message = "节拍工价模板不符合规范：表头信息不符合规范,第4列应为{生产节拍}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[8].ColumnName != "型腔数")
|
{
|
code = "300";
|
Message = "节拍工价模板不符合规范：表头信息不符合规范,第4列应为{型腔数}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,质检标准模板】
|
public static string Seventeen(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
List<DataTable> excelTable = new List<DataTable>();
|
excelTable = ImportExcel.ExcelToTableList(FileCode);
|
if (excelTable.Count != 2)
|
{
|
code = "300";
|
Message = "模板不符合规范,缺失的Sheet";
|
}
|
else if (excelTable[0].Columns.Count != 8)
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名字段数";
|
}
|
else if (excelTable[1].Columns.Count != 9)
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名字段数";
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "物料编码")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "物料名称")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "检验类型")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "工艺路线")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[5].ColumnName != "工序编码")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[6].ColumnName != "工序名称")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[0].Columns[7].ColumnName != "流程管控")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "主表序列号")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "排列顺序")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "检验项目")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "重要度")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[5].ColumnName != "检验工具")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[6].ColumnName != "标准要求")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[7].ColumnName != "下限值")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable[1].Columns[8].ColumnName != "上限值")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,缺陷定义模板】
|
public static string Eighteen(List<DataTable> excelTable, out string code)
|
{
|
string Message = "";
|
code = "";
|
if (excelTable.Count != 1)
|
{
|
code = "301";
|
Message = "导入模板不符合规范,请检查sheet数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 3)
|
{
|
code = "301";
|
Message = "缺陷定义模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "*缺陷编号(唯一)")
|
{
|
code = "301";
|
Message = "缺陷定义模板：表头信息不符合规范,第1列应为{*缺陷编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "*缺陷名称")
|
{
|
code = "301";
|
Message = "缺陷定义模板：表头信息不符合规范,第2列应为{*缺陷名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "缺陷描述")
|
{
|
code = "301";
|
Message = "缺陷定义模板：表头信息不符合规范,第3列应为{缺陷描述}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,生产订单模板】
|
public static string Nineteen(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
DataTable excelTable = new DataTable();
|
excelTable = ImportExcel.ExcelToTable(FileCode);
|
if (excelTable.Columns.Count != 7)
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[1].ColumnName != "产品编码")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[2].ColumnName != "产品名称")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[3].ColumnName != "产品规格")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[4].ColumnName != "订单数量")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[5].ColumnName != "要求交付时间")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[6].ColumnName != "ERP订单号")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,生产订单模板】
|
public static string Twenty(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
DataTable excelTable = new DataTable();
|
excelTable = ImportExcel.ExcelToTable(FileCode);
|
if (excelTable.Columns.Count != 10)
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[1].ColumnName != "库位编码")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[2].ColumnName != "物料编码")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[3].ColumnName != "物料名称")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[4].ColumnName != "流程卡号")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[5].ColumnName != "容器编码")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[6].ColumnName != "入厂条码")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[7].ColumnName != "炉号批次")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[8].ColumnName != "供方名称")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[9].ColumnName != "当前库存")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,物料清单模板】
|
public static string TwentyOne(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
List<DataTable> excelTable = new List<DataTable>();
|
excelTable = ImportExcel.ExcelToTableList(FileCode);
|
if (excelTable.Count != 4)
|
{
|
code = "300";
|
Message = "导入模板不符合规范,请检查Sheet数";
|
}
|
else if (excelTable[0].Columns.Count != 6)
|
{
|
code = "300";
|
Message = "物料清单模板(母件信息)不符合规范,请检查列名字段数";
|
}
|
else if (excelTable[1].Columns.Count != 10)
|
{
|
code = "300";
|
Message = "物料清单模板(子件信息)不符合规范,请检查列名字段数";
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "物料清单模板(母件)：表头信息不符合规范,第1列应为{序号}";
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "母件编码")
|
{
|
code = "300";
|
Message = "物料清单模板(母件)：表头信息不符合规范,第2列应为{母件编码}";
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "母件名称")
|
{
|
code = "300";
|
Message = "物料清单模板(母件)：表头信息不符合规范,第3列应为{母件名称}";
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "基础数量")
|
{
|
code = "300";
|
Message = "物料清单模板(母件)：表头信息不符合规范,第4列应为{基础数量}";
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "启用状态")
|
{
|
code = "300";
|
Message = "物料清单模板(母件)：表头信息不符合规范,第5列应为{启用状态}";
|
}
|
else if (excelTable[0].Columns[5].ColumnName != "版本号")
|
{
|
code = "300";
|
Message = "物料清单模板(母件)：表头信息不符合规范,第6列应为{版本号}";
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "物料清单模板(子件)：表头信息不符合规范,第1列应为{序号}";
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "主表序列号")
|
{
|
code = "300";
|
Message = "物料清单模板(子件)：表头信息不符合规范,第2列应为{主表序列号}";
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "子件顺序号")
|
{
|
code = "300";
|
Message = "物料清单模板(子件)：表头信息不符合规范,第3列应为{子件顺序号}";
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "子件编码")
|
{
|
code = "300";
|
Message = "物料清单模板(子件)：表头信息不符合规范,第4列应为{子件编码}";
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "子件名称")
|
{
|
code = "300";
|
Message = "物料清单模板(子件)：表头信息不符合规范,第5列应为{子件名称}";
|
}
|
else if (excelTable[1].Columns[5].ColumnName != "发料仓库")
|
{
|
code = "300";
|
Message = "物料清单模板(子件)：表头信息不符合规范,第6列应为{发料仓库}";
|
}
|
else if (excelTable[1].Columns[6].ColumnName != "基本用量")
|
{
|
code = "300";
|
Message = "物料清单模板(子件)：表头信息不符合规范,第7列应为{基本用量}";
|
}
|
else if (excelTable[1].Columns[7].ColumnName != "损耗率(%)")
|
{
|
code = "300";
|
Message = "物料清单模板(子件)：表头信息不符合规范,第8列应为{损耗率(%)}";
|
}
|
else if (excelTable[1].Columns[8].ColumnName != "实际用量")
|
{
|
code = "300";
|
Message = "物料清单模板(子件)：表头信息不符合规范,第9列应为{实际用量}";
|
}
|
else if (excelTable[1].Columns[9].ColumnName != "属性")
|
{
|
code = "300";
|
Message = "物料清单模板(子件)：表头信息不符合规范,第10列应为{属性}";
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#endregion
|
|
#region【Excel上传数据验证】
|
#region【Excel上传数据验证,组织架构】
|
public static List<ExcelErro> ZerroData(string savePath, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,岗位管理】
|
public static List<ExcelErro> OneData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//岗位表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TPost where postcode=@postcode";
|
dynamicParams.Add("@postcode", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*岗位编号(唯一)}";
|
erro.ErrorCont = "岗位表:{*岗位编号(唯一)}字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TPost where postname=@postname";
|
dynamicParams.Add("@postname", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*岗位名称}";
|
erro.ErrorCont = "岗位表:{*岗位名称}字段" + excelTable[0].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,班组管理】
|
public static List<ExcelErro> TwoData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//班组表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TGroup where usergroupcode=@usergroupcode";
|
dynamicParams.Add("@usergroupcode", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*班组编号(唯一)}";
|
erro.ErrorCont = "班组表:{*班组编号(唯一)}字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TGroup where usergroupname=@usergroupname";
|
dynamicParams.Add("@usergroupname", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*班组名称}";
|
erro.ErrorCont = "班组表:{*班组名称}字段" + excelTable[0].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,人员管理】
|
public static List<ExcelErro> ThreeData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//用户表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TUser where usercode=@usercode";
|
dynamicParams.Add("@usercode", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = (k + 1).ToString();
|
erro.ErrorField = "*用户编码(唯一)";
|
erro.ErrorCont = "用户表:*用户编码(唯一)字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TUser where username=@username";
|
dynamicParams.Add("@username", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = (k + 1).ToString();
|
erro.ErrorField = "*用户名称";
|
erro.ErrorCont = "用户表:*用户名称字段" + excelTable[0].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][7].ToString().Trim() != null && excelTable[0].Rows[k][7].ToString().Trim() != "")
|
{
|
sql = @"select * from TOrganization where torg_code=@torg_code";
|
dynamicParams.Add("@torg_code", excelTable[0].Rows[k][7].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt == null || dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = (k + 1).ToString();
|
erro.ErrorField = "*组织编码";
|
erro.ErrorCont = "用户表:*组织编码字段" + excelTable[0].Rows[k][7].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][8].ToString().Trim() != null && excelTable[0].Rows[k][8].ToString().Trim() != "")
|
{
|
string[] postcode = Array.ConvertAll<string, string>(excelTable[0].Rows[k][8].ToString().Trim().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
sql = @"select postcode from TPost where postcode in @postcode";
|
dynamicParams.Add("@postcode", postcode);
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
string[] values = excelTable[0].Rows[k][8].ToString().Trim().Split(',');
|
var unmatchedValues = values.Except(dt.AsEnumerable().Select(row => row.Field<string>("postcode")));
|
foreach (var value in unmatchedValues)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = (k + 1).ToString();
|
erro.ErrorField = "{所属岗位编码}";
|
erro.ErrorCont = "用户表:{所属岗位编码}字段" + value + " 不是有效的岗位编码";
|
list.Add(erro);
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = (k + 1).ToString();
|
erro.ErrorField = "{所属岗位编码}";
|
erro.ErrorCont = "用户表:{所属岗位编码}字段" + excelTable[0].Rows[k][8].ToString().Trim() + " 不是有效的岗位编码";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][9].ToString().Trim() != null && excelTable[0].Rows[k][9].ToString().Trim() != "")
|
{
|
string[] rolecode = Array.ConvertAll<string, string>(excelTable[0].Rows[k][9].ToString().Trim().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
sql = @"select rolecode from TRole where rolecode in @rolecode";
|
dynamicParams.Add("@rolecode", rolecode);
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
string[] values = excelTable[0].Rows[k][9].ToString().Trim().Split(',');
|
var unmatchedValues = values.Except(dt.AsEnumerable().Select(row => row.Field<string>("rolecode")));
|
foreach (var value in unmatchedValues)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = (k + 1).ToString();
|
erro.ErrorField = "{所属角色编码}";
|
erro.ErrorCont = "用户表:{所属角色编码}字段" + value + " 不是有效的角色编码";
|
list.Add(erro);
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = (k + 1).ToString();
|
erro.ErrorField = "{所属角色编码}";
|
erro.ErrorCont = "用户表:{所属角色编码}字段" + excelTable[0].Rows[k][9].ToString().Trim() + " 不是有效的角色编码";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][10].ToString().Trim() != null && excelTable[0].Rows[k][10].ToString().Trim() != "")
|
{
|
string[] usergroupcode = Array.ConvertAll<string, string>(excelTable[0].Rows[k][10].ToString().Trim().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
sql = @"select usergroupcode from TGroup where usergroupcode in @usergroupcode";
|
dynamicParams.Add("@usergroupcode", usergroupcode);
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
string[] values = excelTable[0].Rows[k][10].ToString().Trim().Split(',');
|
var unmatchedValues = values.Except(dt.AsEnumerable().Select(row => row.Field<string>("usergroupcode")));
|
foreach (var value in unmatchedValues)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = (k + 1).ToString();
|
erro.ErrorField = "{所属班组编码}";
|
erro.ErrorCont = "用户表:{所属班组编码}字段" + value + " 不是有效的班组编码";
|
list.Add(erro);
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = (k + 1).ToString();
|
erro.ErrorField = "{所属班组编码}";
|
erro.ErrorCont = "用户表:{所属班组编码}字段" + excelTable[0].Rows[k][10].ToString().Trim() + " 不是有效的班组编码";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,角色管理】
|
public static List<ExcelErro> FourData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//角色表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TRole where rolecode=@rolecode";
|
dynamicParams.Add("@rolecode", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*角色编号(唯一)}";
|
erro.ErrorCont = "角色表:{*角色编号(唯一)}字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TRole where rolename=@rolename";
|
dynamicParams.Add("@rolename", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*角色名称}";
|
erro.ErrorCont = "角色表:{*角色名称}字段" + excelTable[0].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
//if (excelTable[0].Rows[k][3].ToString().Trim() != null && excelTable[0].Rows[k][3].ToString().Trim() != "")
|
//{
|
// if (excelTable[0].Rows[k][3].ToString().Trim() == "自定义")
|
// {
|
// if (excelTable[0].Rows[k][4].ToString().Trim() == null && excelTable[0].Rows[k][4].ToString().Trim() == "")
|
// {
|
// ExcelErro erro = new ExcelErro();
|
// erro.RoeNumber = "/";
|
// erro.ErrorField = "{数据权限}";
|
// erro.ErrorCont = "角色表:{数据范围}字段为：" + excelTable[1].Rows[k][3].ToString().Trim() + "时,{数据权限}字段不能为空";
|
// list.Add(erro);
|
// }
|
// else
|
// {
|
// //判断数据权限编码是否合理
|
// if (excelTable[0].Rows[k][4].ToString().Trim() != null && excelTable[0].Rows[k][4].ToString().Trim() != "")
|
// {
|
// string[] torgcode = Array.ConvertAll<string, string>(excelTable[0].Rows[k][4].ToString().Trim().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
// sql = @"select postcode from TOrganization where torg_code in @torgcode";
|
// dynamicParams.Add("@torgcode", torgcode);
|
// dt = DapperHelper.selectdata(sql, dynamicParams);
|
// if (dt != null && dt.Rows.Count > 0)
|
// {
|
// string[] values = excelTable[0].Rows[k][4].ToString().Trim().Split(',');
|
// var unmatchedValues = values.Except(dt.AsEnumerable().Select(row => row.Field<string>("torg_code")));
|
// foreach (var value in unmatchedValues)
|
// {
|
// ExcelErro erro = new ExcelErro();
|
// erro.RoeNumber = (k + 1).ToString();
|
// erro.ErrorField = "{数据权限}";
|
// erro.ErrorCont = "角色表:{数据权限}字段" + value + " 不是有效的组织编码";
|
// list.Add(erro);
|
// }
|
// }
|
// else
|
// {
|
// ExcelErro erro = new ExcelErro();
|
// erro.RoeNumber = (k + 1).ToString();
|
// erro.ErrorField = "{数据权限}";
|
// erro.ErrorCont = "角色表:{数据权限}字段" + excelTable[0].Rows[k][4].ToString().Trim() + " 不是有效的组织编码";
|
// list.Add(erro);
|
// }
|
// }
|
// }
|
// }
|
//}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,往来单位】
|
public static List<ExcelErro> FiveData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
var dynamicParams = new DynamicParameters();
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//往来单位类型表
|
for (int j = 0; j < excelTable[0].Rows.Count; j++)
|
{
|
if (excelTable[0].Rows[j][0].ToString().Trim() != null && excelTable[0].Rows[j][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TCustomerType where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[j][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*往来单位类型编号(唯一)}";
|
erro.ErrorCont = "往来单位类型表:{*往来单位类型编号(唯一)}字段" + excelTable[0].Rows[j][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[j][1].ToString().Trim() != null && excelTable[0].Rows[j][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TCustomerType where name=@name";
|
dynamicParams.Add("@name", excelTable[0].Rows[j][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*往来单位类型名称}";
|
erro.ErrorCont = "往来单位类型表:{*往来单位类型名称}字段" + excelTable[0].Rows[j][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
var invalidRows = excelTable[0].AsEnumerable().Where(row =>
|
row.Field<string>("上级编码") == row.Field<string>("*往来单位类型编号(唯一)") ||
|
(row.Field<string>("上级编码") != null && !excelTable[0].AsEnumerable().Any(r => r.Field<string>("*往来单位类型编号(唯一)") == row.Field<string>("上级编码")))
|
);
|
|
// 输出不合理的行
|
foreach (var row in invalidRows)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{上级编码}";
|
erro.ErrorCont = $"往来单位类型表:*往来单位类型编号(唯一)： {row.Field<string>("*往来单位类型编号(唯一)")} 的上级编码：{row.Field<string>("上级编码")}不合理";
|
list.Add(erro);
|
}
|
|
//往来单位表
|
for (int k = 0; k < excelTable[1].Rows.Count; k++)
|
{
|
if (excelTable[1].Rows[k][0].ToString().Trim() != null && excelTable[1].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TCustomer where code=@code";
|
dynamicParams.Add("@code", excelTable[1].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*往来单位编号(唯一)}";
|
erro.ErrorCont = "往来单位表:{*往来单位编号(唯一)}字段" + excelTable[1].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[1].Rows[k][1].ToString().Trim() != null && excelTable[1].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TCustomer where name=@name";
|
dynamicParams.Add("@name", excelTable[1].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*往来单位名称}";
|
erro.ErrorCont = "往来单位表:{*往来单位名称}字段" + excelTable[1].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//判断子表外键不存在于主表主键中的数据
|
var dt3 = from r in excelTable[1].AsEnumerable()
|
where !(
|
from rr in excelTable[0].AsEnumerable()
|
select rr.Field<string>("*往来单位类型编号(唯一)")
|
).Contains(r.Field<string>("*往来单位类型编码"))
|
select r;
|
List<DataRow> listRow = dt3.ToList();
|
if (listRow.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*往来单位类型编码}";
|
erro.ErrorCont = "往来单位表:{*往来单位类型编码}字段中有值在往来单位类型表:{*往来单位类型编号(唯一)}中不存在";
|
list.Add(erro);
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,仓库设置】
|
public static List<ExcelErro> SixData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//仓库表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TSecStck where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*仓库编号(唯一)}";
|
erro.ErrorCont = "仓库表:{*仓库编号(唯一)}字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TSecStck where name=@name";
|
dynamicParams.Add("@name", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*仓库名称}";
|
erro.ErrorCont = "仓库表:{*仓库名称}字段" + excelTable[1].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,库位设置】
|
public static List<ExcelErro> SevenData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//库位表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TSecLoca where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*库位编号(唯一)}";
|
erro.ErrorCont = "库位表:{*库位编号(唯一)}字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TSecLoca where name=@name";
|
dynamicParams.Add("@name", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*库位名称}";
|
erro.ErrorCont = "库位表:{*库位名称}字段" + excelTable[1].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][2].ToString().Trim() != null && excelTable[0].Rows[k][2].ToString().Trim() != "")
|
{
|
sql = @"select * from TSecStck where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[k][2].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*所属仓库编码}";
|
erro.ErrorCont = "库位表:{*所属仓库编码}字段" + excelTable[1].Rows[k][1].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
var invalidRows = excelTable[0].AsEnumerable().Where(row =>
|
row.Field<string>("上级库位编码") == row.Field<string>("*库位编号(唯一)") ||
|
(row.Field<string>("上级库位编码") != null && !excelTable[0].AsEnumerable().Any(r => r.Field<string>("*库位编号(唯一)") == row.Field<string>("上级库位编码")))
|
);
|
|
// 输出不合理的行
|
foreach (var row in invalidRows)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{上级库位编码}";
|
erro.ErrorCont = $"库位表:*库位编号(唯一)： {row.Field<string>("*库位编号(唯一)")} 的上级库位编码：{row.Field<string>("上级库位编码")}不合理";
|
list.Add(erro);
|
}
|
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,存货档案】
|
public static List<ExcelErro> EightData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
var dynamicParams = new DynamicParameters();
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//存货分类表
|
for (int j = 0; j < excelTable[0].Rows.Count; j++)
|
{
|
if (excelTable[0].Rows[j][0].ToString().Trim() != null && excelTable[0].Rows[j][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TMateriel_Class where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[j][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*存货分类编号(唯一)}";
|
erro.ErrorCont = "存货分类表:{*存货分类编号(唯一)}字段" + excelTable[0].Rows[j][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[j][1].ToString().Trim() != null && excelTable[0].Rows[j][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TMateriel_Class where name=@name";
|
dynamicParams.Add("@name", excelTable[0].Rows[j][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*存货分类名称}";
|
erro.ErrorCont = "存货分类表:{*存货分类名称}字段" + excelTable[0].Rows[j][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
var invalidRows = excelTable[0].AsEnumerable().Where(row =>
|
row.Field<string>("上级编码") == row.Field<string>("*存货分类编号(唯一)") ||
|
(row.Field<string>("上级编码") != null && !excelTable[0].AsEnumerable().Any(r => r.Field<string>("*存货分类编号(唯一)") == row.Field<string>("上级编码")))
|
);
|
|
// 输出不合理的行
|
foreach (var row in invalidRows)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{上级编码}";
|
erro.ErrorCont = $"存货类型表:*存货分类编号(唯一)： {row.Field<string>("**存货分类编号(唯一)")} 的上级编码：{row.Field<string>("上级编码")}不合理";
|
list.Add(erro);
|
}
|
|
//存货表
|
for (int k = 0; k < excelTable[1].Rows.Count; k++)
|
{
|
if (excelTable[1].Rows[k][0].ToString().Trim() != null && excelTable[1].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TMateriel_Info where partcode=@partcode";
|
dynamicParams.Add("@partcode", excelTable[1].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*存货编号(唯一)}";
|
erro.ErrorCont = "存货档案表:{*存货编号(唯一)}字段" + excelTable[1].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[1].Rows[k][1].ToString().Trim() != null && excelTable[1].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TMateriel_Info where partname=@partname";
|
dynamicParams.Add("@partname", excelTable[1].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*存货名称}";
|
erro.ErrorCont = "存货档案表:{*存货名称}字段" + excelTable[1].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[1].Rows[k][5].ToString().Trim() != null && excelTable[1].Rows[k][5].ToString().Trim() != "")
|
{
|
switch (excelTable[1].Rows[k][5].ToString().Trim())
|
{
|
case "S": //单计量
|
//判断计量单位是否为单计量
|
if (excelTable[1].Rows[k][6].ToString().Trim() != null && excelTable[1].Rows[k][6].ToString().Trim() != "")
|
{
|
sql = @"select * from TUnit where name=@name and isSingleUnit='1'";
|
dynamicParams.Add("@name", excelTable[1].Rows[k][6].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*计量单位/组名字}";
|
erro.ErrorCont = "存货档案表:{*计量单位/组名字}字段" + excelTable[1].Rows[k][6].ToString().Trim() + "不是有效的单计量单位";
|
list.Add(erro);
|
}
|
}
|
break;
|
case "M": //多计量
|
//判断计量单位是否为多计量
|
if (excelTable[1].Rows[k][6].ToString().Trim() != null && excelTable[1].Rows[k][6].ToString().Trim() != "")
|
{
|
sql = @"select * from TUnitGroup where name=@name";
|
dynamicParams.Add("@code", excelTable[1].Rows[k][6].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*计量单位/组名称}";
|
erro.ErrorCont = "存货档案表:{*计量单位/组名称}字段" + excelTable[1].Rows[k][6].ToString().Trim() + "不是有效的多计量单位组";
|
list.Add(erro);
|
}
|
}
|
break;
|
default:
|
break;
|
}
|
}
|
if (excelTable[1].Rows[k][7].ToString().Trim() != null && excelTable[1].Rows[k][7].ToString().Trim() != "")
|
{
|
sql = @"select * from TSecStck where code=@code";
|
dynamicParams.Add("@code", excelTable[1].Rows[k][7].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{所属仓库编码}";
|
erro.ErrorCont = "存货档案表:{所属仓库编码}字段" + excelTable[1].Rows[k][7].ToString().Trim() + "不是有效的仓库编码";
|
list.Add(erro);
|
}
|
}
|
}
|
//判断子表外键不存在于主表主键中的数据
|
var dt3 = from r in excelTable[1].AsEnumerable()
|
where !(
|
from rr in excelTable[0].AsEnumerable()
|
select rr.Field<string>("*存货分类编号(唯一)")
|
).Contains(r.Field<string>("*存货分类编码"))
|
select r;
|
List<DataRow> listRow = dt3.ToList();
|
if (listRow.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*存货分类编码}";
|
erro.ErrorCont = "存货档案表:{*存货分类编码}字段中有值在存货分类表:{*存货分类编号(唯一)}中不存在";
|
list.Add(erro);
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,设备清单】
|
public static List<ExcelErro> NineData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
var dynamicParams = new DynamicParameters();
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//设备类型表
|
for (int j = 0; j < excelTable[0].Rows.Count; j++)
|
{
|
if (excelTable[0].Rows[j][0].ToString().Trim() != null && excelTable[0].Rows[j][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpType where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[j][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备类型编号(唯一)}";
|
erro.ErrorCont = "设备类型表:{*设备类型编号(唯一)}字段" + excelTable[0].Rows[j][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[j][1].ToString().Trim() != null && excelTable[0].Rows[j][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpType where name=@name";
|
dynamicParams.Add("@name", excelTable[0].Rows[j][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备类型名称}";
|
erro.ErrorCont = "设备类型表:{*设备类型名称}字段" + excelTable[0].Rows[j][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
|
//设备表
|
for (int k = 0; k < excelTable[1].Rows.Count; k++)
|
{
|
if (excelTable[1].Rows[k][0].ToString().Trim() != null && excelTable[1].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpInfo where code=@code";
|
dynamicParams.Add("@code", excelTable[1].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备编号(唯一)}";
|
erro.ErrorCont = "设备表:{*设备编号(唯一)}字段" + excelTable[1].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[1].Rows[k][1].ToString().Trim() != null && excelTable[1].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpInfo where name=@name";
|
dynamicParams.Add("@name", excelTable[1].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备名称}";
|
erro.ErrorCont = "设备表:{*设备名称}字段" + excelTable[1].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[1].Rows[k][2].ToString().Trim() != null && excelTable[1].Rows[k][2].ToString().Trim() != "")
|
{
|
sql = @"select * from TOrganization where torg_code=@torg_code";
|
dynamicParams.Add("@torg_code", excelTable[1].Rows[k][2].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*所属组织编码}";
|
erro.ErrorCont = "设备表:{*所属组织编码}字段" + excelTable[1].Rows[k][2].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//判断子表外键不存在于主表主键中的数据
|
var dt3 = from r in excelTable[1].AsEnumerable()
|
where !(
|
from rr in excelTable[0].AsEnumerable()
|
select rr.Field<string>("*设备类型编号(唯一)")
|
).Contains(r.Field<string>("*设备类型编码"))
|
select r;
|
List<DataRow> listRow = dt3.ToList();
|
if (listRow.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备类型编码}";
|
erro.ErrorCont = "设备表:{*设备类型编码}字段中有值在设备类型表:{*设备类型编号(唯一)}中不存在";
|
list.Add(erro);
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,设备点检项目】
|
public static List<ExcelErro> TenData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
var dynamicParams = new DynamicParameters();
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//当前Excel数据中点检标准页签中,设备点检标准编号(唯一是否有重复)
|
DataTable distinct = excelTable[0].DefaultView.ToTable(true, "*点检项目编号(唯一)");
|
if (distinct.Rows.Count != excelTable[0].Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*点检项目编号(唯一)}";
|
erro.ErrorCont = "设备点检项目模板:{*点检项目编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
if (excelTable[0].Rows[i][0].ToString().Trim() != null && excelTable[0].Rows[i][0].ToString().Trim() != "")
|
{
|
sql = @"select code from TEqpchk_Item where code=@code";
|
|
dynamicParams.Add("@code", excelTable[0].Rows[i][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*点检项目编号(唯一)}";
|
erro.ErrorCont = "设备点检项目表:{*点检项目编号(唯一)}字段" + excelTable[0].Rows[i][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,设备点检标准】
|
public static List<ExcelErro> Ten_oneData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
var dynamicParams = new DynamicParameters();
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//当前Excel数据中点检标准页签中,设备点检标准编号(唯一是否有重复)
|
DataTable distinct = excelTable[0].DefaultView.ToTable(true, "*设备点检标准编号(唯一)");
|
if (distinct.Rows.Count != excelTable[0].Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备点检标准编号(唯一)}";
|
erro.ErrorCont = "设备点检标准页签模板:{*设备点检标准编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
//设备点检标准表是否已存在点检标准
|
for (int j = 0; j < excelTable[0].Rows.Count; j++)
|
{
|
if (excelTable[0].Rows[j][0].ToString().Trim() != null && excelTable[0].Rows[j][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpchk_Main where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[j][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备点检标准编号(唯一)}";
|
erro.ErrorCont = "设备点检标准表:{*设备点检标准编号(唯一)}字段" + excelTable[0].Rows[j][0].ToString().Trim() + "在数据表中已存在";
|
list.Add(erro);
|
}
|
//通过设备点检标准页签模板中的标准编码查询设备点检部位页签模板中对应的数据信息
|
var var_dtTable = excelTable[1].AsEnumerable().Where<DataRow>(W => W["*设备点检标准编号(M)"].ToString() == excelTable[0].Rows[j][0].ToString().Trim());
|
DataTable ds = var_dtTable.Any() ? var_dtTable.CopyToDataTable() : excelTable[1].Clone();
|
if (ds.Rows.Count > 0)
|
{
|
//判断点检标准查询点检部位数据是否有重复
|
DataTable distinct1 = ds.DefaultView.ToTable(true, "*设备点检部位编号(唯一)");
|
if (distinct1.Rows.Count != ds.Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备点检部位编号(唯一)}";
|
erro.ErrorCont = "设备点检部位页签模板:{*设备点检标准编号(M)}:" + excelTable[1].Rows[j][1].ToString().Trim() + " 对应{*设备点检部位编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备点检标准编号(唯一)}";
|
erro.ErrorCont = "设备点检标准模板sheet1中:{*设备点检标准编号(M)}:" + excelTable[1].Rows[j][0].ToString().Trim() + " 在设备点检标准模板sheet中{*设备点检标准编号(唯一)}列不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//当前Excel数据中点检部位页签中,设备点检部位编号(唯一)在设备点检部位表中是否存在
|
for (int k = 0; k < excelTable[1].Rows.Count; k++)
|
{
|
if (excelTable[1].Rows[k][2].ToString().Trim() != null && excelTable[1].Rows[k][2].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpchk_Item where code=@code";
|
dynamicParams.Add("@code", excelTable[1].Rows[k][2].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备点检部位编号(唯一)}";
|
erro.ErrorCont = "设备点检部位表:{*设备点检部位编号(唯一)}字段" + excelTable[1].Rows[k][2].ToString().Trim() + "在数据表中不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//判断子表外键不存在于主表主键中的数据
|
var dt3 = from r in excelTable[1].AsEnumerable()
|
where !(
|
from rr in excelTable[0].AsEnumerable()
|
select rr.Field<string>("*设备点检标准编号(唯一)")
|
).Contains(r.Field<string>("*设备点检标准编号(M)"))
|
select r;
|
List<DataRow> listRow = dt3.ToList();
|
if (listRow.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备点检标准编号(M)}";
|
erro.ErrorCont = "设备点检部位页签模板:{*设备点检标准编号(M)}字段中有值在设备点检标准页签模板:{*设备点检标准编号(唯一)}中不存在";
|
list.Add(erro);
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,设备保养项目】
|
public static List<ExcelErro> ElevenData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
var dynamicParams = new DynamicParameters();
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//当前Excel数据中点检标准页签中,设备点检标准编号(唯一是否有重复)
|
DataTable distinct = excelTable[0].DefaultView.ToTable(true, "*保养项目编号(唯一)");
|
if (distinct.Rows.Count != excelTable[0].Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*保养项目编号(唯一)}";
|
erro.ErrorCont = "设备保养项目页签模板:{*保养项目编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
if (excelTable[0].Rows[i][0].ToString().Trim() != null && excelTable[0].Rows[i][0].ToString().Trim() != "")
|
{
|
sql = @"select code from TEqpmai_Item where code=@code";
|
|
dynamicParams.Add("@code", excelTable[0].Rows[i][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*保养项目编号(唯一)}";
|
erro.ErrorCont = "设备保养项目表:{*点检保养编号(唯一)}字段" + excelTable[0].Rows[i][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,设备保养标准】
|
public static List<ExcelErro> Eleven_oneData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
var dynamicParams = new DynamicParameters();
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//当前Excel数据中保养标准页签中,设备保养标准编号(唯一是否有重复)
|
DataTable distinct = excelTable[0].DefaultView.ToTable(true, "*设备保养标准编号(唯一)");
|
if (distinct.Rows.Count != excelTable[0].Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备保养标准编号(唯一)}";
|
erro.ErrorCont = "设备保养标准页签模板:{*设备保养标准编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
//设备保养标准表是否已存在保养标准
|
for (int j = 0; j < excelTable[0].Rows.Count; j++)
|
{
|
if (excelTable[0].Rows[j][0].ToString().Trim() != null && excelTable[0].Rows[j][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpmai_Main where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[j][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备保养标准编号(唯一)}";
|
erro.ErrorCont = "设备保养标准表:{*设备保养标准编号(唯一)}字段" + excelTable[0].Rows[j][0].ToString().Trim() + "在数据表中已存在";
|
list.Add(erro);
|
}
|
//通过设备保养标准页签模板中的标准编码查询设备保养部位页签模板中对应的数据信息
|
var var_dtTable = excelTable[1].AsEnumerable().Where<DataRow>(W => W["*设备保养标准编号(M)"].ToString() == excelTable[0].Rows[j][0].ToString().Trim());
|
DataTable ds = var_dtTable.Any() ? var_dtTable.CopyToDataTable() : excelTable[0].Clone();
|
if (ds.Rows.Count > 0)
|
{
|
//判断保养标准查询保养部位数据是否有重复
|
DataTable distinct1 = ds.DefaultView.ToTable(true, "*设备保养部位编号(唯一)");
|
if (distinct1.Rows.Count != ds.Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备保养部位编号(唯一)}";
|
erro.ErrorCont = "设备保养部位页签模板:{*设备保养标准编号(M)}:" + excelTable[1].Rows[j][1].ToString().Trim() + " 对应{*设备保养部位编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备保养标准编号(唯一)}";
|
erro.ErrorCont = "设备保养标准模板sheet1中:{*设备保养标准编号(M)}:" + excelTable[1].Rows[j][0].ToString().Trim() + " 在设备保养标准模板sheet中{*设备保养标准编号(唯一)}列不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//当前Excel数据中点检部位页签中,设备保养部位编号(唯一)在设备保养部位表中是否存在
|
for (int k = 0; k < excelTable[1].Rows.Count; k++)
|
{
|
if (excelTable[1].Rows[k][2].ToString().Trim() != null && excelTable[1].Rows[k][2].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpmai_Item where code=@code";
|
dynamicParams.Add("@code", excelTable[1].Rows[k][2].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备保养部位编号(唯一)}";
|
erro.ErrorCont = "设备保养部位表:{*设备保养部位编号(唯一)}字段" + excelTable[1].Rows[k][2].ToString().Trim() + "在数据表中不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//判断子表外键不存在于主表主键中的数据
|
var dt3 = from r in excelTable[1].AsEnumerable()
|
where !(
|
from rr in excelTable[0].AsEnumerable()
|
select rr.Field<string>("*设备保养标准编号(唯一)")
|
).Contains(r.Field<string>("*设备保养标准编号(M)"))
|
select r;
|
List<DataRow> listRow = dt3.ToList();
|
if (listRow.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*设备保养标准编号(M)}";
|
erro.ErrorCont = "设备保养部位页签模板:{*设备保养标准编号(M)}字段中有值在设备保养标准页签模板:{*设备保养标准编号(唯一)}中不存在";
|
list.Add(erro);
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,模具清单】
|
public static List<ExcelErro> TwelveData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
var dynamicParams = new DynamicParameters();
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
|
//模具表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TMouldInfo where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具编号(唯一)}";
|
erro.ErrorCont = "模具表:{*模具编号(唯一)}字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TMouldInfo where name=@name";
|
dynamicParams.Add("@name", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具名称}";
|
erro.ErrorCont = "模具表:{*模具名称}字段" + excelTable[0].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
//判断仓库编码是否为空
|
if (excelTable[0].Rows[k][4].ToString().Trim() != null && excelTable[0].Rows[k][4].ToString().Trim() != "")
|
{
|
sql = @"select * from TSecStck where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[k][4].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*仓库编码}";
|
erro.ErrorCont = "模具表:{*仓库编码}字段" + excelTable[0].Rows[k][4].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
}
|
//判断库位编码是否为空
|
if (excelTable[0].Rows[k][5].ToString().Trim() != null && excelTable[0].Rows[k][5].ToString().Trim() != "")
|
{
|
//库位不为空时,判断仓库是否为空
|
if (excelTable[0].Rows[k][4].ToString().Trim() != null && excelTable[0].Rows[k][4].ToString().Trim() != "")
|
{
|
//判断库位是否存在
|
sql = @"select * from TSecLoca where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[k][5].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*库位编码}";
|
erro.ErrorCont = "模具表:{*库位编码}字段" + excelTable[0].Rows[k][5].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
else
|
{
|
//判断库位是否属于当前仓库
|
if (excelTable[0].Rows[k][4].ToString().Trim() != dt.Rows[0]["idwarehouse"].ToString())
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*库位编码}";
|
erro.ErrorCont = "模具表:{*库位编码}字段" + excelTable[0].Rows[k][5].ToString().Trim() + "与{*仓库编码}字段：" + excelTable[0].Rows[k][4].ToString().Trim() + "不匹配";
|
list.Add(erro);
|
}
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*库位编码}";
|
erro.ErrorCont = "模具表:{*库位编码}字段" + excelTable[0].Rows[k][5].ToString().Trim() + "对应的仓库字段不能为空";
|
list.Add(erro);
|
}
|
}
|
}
|
//模具关联产品表
|
for (int j = 0; j < excelTable[1].Rows.Count; j++)
|
{
|
if (excelTable[1].Rows[j][1].ToString().Trim() != null && excelTable[1].Rows[j][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TMateriel_Info where partcode=@partcode";
|
dynamicParams.Add("@partcode", excelTable[1].Rows[j][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{产品编码}";
|
erro.ErrorCont = "模具关联产品表:{产品编码}字段" + excelTable[1].Rows[j][1].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//判断模具关联产品表是否有数据
|
if (excelTable[1].Rows.Count > 0)
|
{
|
//判断子表外键不存在于主表主键中的数据
|
var dt3 = from r in excelTable[1].AsEnumerable()
|
where !(
|
from rr in excelTable[0].AsEnumerable()
|
select rr.Field<string>("*模具编号(唯一)").Trim()
|
).Contains(r.Field<string>("模具编码").Trim())
|
select r;
|
List<DataRow> listRow = dt3.ToList();
|
if (listRow.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{模具编码}";
|
erro.ErrorCont = "模具关联产品表:{模具编码}字段中有值在模具清单表:{*模具编号(唯一)}中不存在";
|
list.Add(erro);
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,模具点检项目】
|
public static List<ExcelErro> ThirteenData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//模具点检项目表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TMouldchk_Item where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*点检项目编号(唯一)}";
|
erro.ErrorCont = "模具点检项目:{*点检项目编号(唯一)}字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TMouldchk_Item where name=@name";
|
dynamicParams.Add("@name", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*点检项目名称}";
|
erro.ErrorCont = "模具点检项目表:{*点检项目名称}字段" + excelTable[0].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,模具点检标准】
|
public static List<ExcelErro> Thirteen_oneData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//模具点检标准表是否已存在点检标准
|
for (int j = 0; j < excelTable[0].Rows.Count; j++)
|
{
|
if (excelTable[0].Rows[j][0].ToString().Trim() != null && excelTable[0].Rows[j][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TMouldchk_Main where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[j][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具点检标准编号(唯一)}";
|
erro.ErrorCont = "模具点检标准表:{*模具点检标准编号(唯一)}字段" + excelTable[0].Rows[j][0].ToString().Trim() + "在数据表中已存在";
|
list.Add(erro);
|
}
|
//通过模具点检标准页签模板中的标准编码查询模具点检项页签模板中对应的数据信息
|
var var_dtTable = excelTable[1].AsEnumerable().Where<DataRow>(W => W["*模具点检标准编号(M)"].ToString().Trim() == excelTable[0].Rows[j][0].ToString().Trim());
|
DataTable ds = var_dtTable.Any() ? var_dtTable.CopyToDataTable() : excelTable[1].Clone();
|
if (ds.Rows.Count > 0)
|
{
|
//判断点检标准查询点检项数据是否有重复
|
DataTable distinct1 = ds.DefaultView.ToTable(true, "*模具点检项编号(唯一)");
|
if (distinct1.Rows.Count != ds.Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具点检项编号(唯一)}";
|
erro.ErrorCont = "模具点检项页签模板:{*模具点检标准编号(M)}:" + excelTable[1].Rows[j][0].ToString().Trim() + " 对应{*模具点检项编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具点检标准编号(唯一)}";
|
erro.ErrorCont = "模具点检标准模板sheet1中:{*模具点检标准编号(M)}:" + excelTable[1].Rows[j][0].ToString().Trim() + " 在模具点检标准模板sheet中{*模具点检标准编号(唯一)}列不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//当前Excel数据中点检项页签中,模具点检项编号(唯一)在模具点检部位表中是否存在
|
for (int k = 0; k < excelTable[1].Rows.Count; k++)
|
{
|
if (excelTable[1].Rows[k][2].ToString().Trim() != null && excelTable[1].Rows[k][2].ToString().Trim() != "")
|
{
|
sql = @"select * from TMouldchk_Item where code=@code and name=@name";
|
dynamicParams.Add("@code", excelTable[1].Rows[k][2].ToString().Trim());
|
dynamicParams.Add("@name", excelTable[1].Rows[k][3].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具点检项编号(唯一)}";
|
erro.ErrorCont = "模具点检部位表:{*模具点检项编号(唯一),*模具点检项名称}字段{" + excelTable[1].Rows[k][2].ToString().Trim() + "," + excelTable[1].Rows[k][3].ToString().Trim() + "}在模具点检项中不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//判断子表外键不存在于主表主键中的数据
|
var dt3 = from r in excelTable[1].AsEnumerable()
|
where !(
|
from rr in excelTable[0].AsEnumerable()
|
select rr.Field<string>("*模具点检标准编号(唯一)").Trim()
|
).Contains(r.Field<string>("*模具点检标准编号(M)").Trim())
|
select r;
|
List<DataRow> listRow = dt3.ToList();
|
if (listRow.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具点检标准编号(M)}";
|
erro.ErrorCont = "模具点检项页签模板:{*模具点检标准编号(M)}字段中有值在模具点检标准页签模板:{*模具点检标准编号(唯一)}中不存在";
|
list.Add(erro);
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,模具保养项目】
|
public static List<ExcelErro> FourteenData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//模具保养项目表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TMouldmai_Item where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*保养项目编号(唯一)}";
|
erro.ErrorCont = "模具保养项目:{*保养项目编号(唯一)}字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TMouldmai_Item where name=@name";
|
dynamicParams.Add("@name", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*保养项目名称}";
|
erro.ErrorCont = "模具保养项目表:{*保养项目名称}字段" + excelTable[0].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,模具保养标准】
|
public static List<ExcelErro> FourteenData_one(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//模具保养标准表是否已存在保养标准
|
for (int j = 0; j < excelTable[0].Rows.Count; j++)
|
{
|
if (excelTable[0].Rows[j][0].ToString().Trim() != null && excelTable[0].Rows[j][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TMouldmai_Main where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[j][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具保养标准编号(唯一)}";
|
erro.ErrorCont = "模具保养标准表:{*模具保养标准编号(唯一)}字段" + excelTable[0].Rows[j][0].ToString().Trim() + "在数据表中已存在";
|
list.Add(erro);
|
}
|
//通过模具保养标准页签模板中的标准编码查询模具保养项页签模板中对应的数据信息
|
var var_dtTable = excelTable[1].AsEnumerable().Where<DataRow>(W => W["*模具保养标准编号(M)"].ToString().Trim() == excelTable[0].Rows[j][0].ToString().Trim());
|
DataTable ds = var_dtTable.Any() ? var_dtTable.CopyToDataTable() : excelTable[1].Clone();
|
if (ds.Rows.Count > 0)
|
{
|
//判断保养标准查询保养项数据是否有重复
|
DataTable distinct1 = ds.DefaultView.ToTable(true, "*模具保养项编号(唯一)");
|
if (distinct1.Rows.Count != ds.Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具保养项编号(唯一)}";
|
erro.ErrorCont = "模具保养项页签模板:{*模具保养标准编号(M)}:" + excelTable[1].Rows[j][0].ToString().Trim() + " 对应{*模具保养项编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具保养标准编号(唯一)}";
|
erro.ErrorCont = "模具保养标准模板sheet1中:{*模具保养标准编号(M)}:" + excelTable[1].Rows[j][0].ToString().Trim() + " 在模具保养标准模板sheet中{*模具保养标准编号(唯一)}列不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//当前Excel数据中保养项页签中,模具保养项编号(唯一)在模具保养部位表中是否存在
|
for (int k = 0; k < excelTable[1].Rows.Count; k++)
|
{
|
if (excelTable[1].Rows[k][2].ToString().Trim() != null && excelTable[1].Rows[k][2].ToString().Trim() != "")
|
{
|
sql = @"select * from TMouldmai_Item where code=@code and name=@name";
|
dynamicParams.Add("@code", excelTable[1].Rows[k][2].ToString().Trim());
|
dynamicParams.Add("@name", excelTable[1].Rows[k][3].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具保养项编号(唯一)}";
|
erro.ErrorCont = "模具保养部位表:{*模具保养项编号(唯一),*模具保养项名称}字段{" + excelTable[1].Rows[k][2].ToString().Trim() + "," + excelTable[1].Rows[k][3].ToString().Trim() + "}在模具保养项中不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//判断子表外键不存在于主表主键中的数据
|
var dt3 = from r in excelTable[1].AsEnumerable()
|
where !(
|
from rr in excelTable[0].AsEnumerable()
|
select rr.Field<string>("*模具保养标准编号(唯一)").Trim()
|
).Contains(r.Field<string>("*模具保养标准编号(M)").Trim())
|
select r;
|
List<DataRow> listRow = dt3.ToList();
|
if (listRow.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*模具保养标准编号(M)}";
|
erro.ErrorCont = "模具保养项页签模板:{*模具保养标准编号(M)}字段中有值在模具保养标准页签模板:{*模具保养标准编号(唯一)}中不存在";
|
list.Add(erro);
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,工序定义】
|
public static List<ExcelErro> FifteenData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//工序表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TStep where stepcode=@stepcode";
|
dynamicParams.Add("@stepcode", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*工序编号(唯一)}";
|
erro.ErrorCont = "工序表:{*工序编号(唯一)}字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TStep where stepname=@stepname";
|
dynamicParams.Add("@stepname", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*工序名称}";
|
erro.ErrorCont = "工序表:{*工序名称}字段" + excelTable[0].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,工艺路线】
|
public static List<ExcelErro> SixteenData(string FileCode, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,节拍工价】
|
public static List<ExcelErro> TwentyFourData(string FileCode, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
var dynamicParams = new DynamicParameters();
|
List<ExcelErro> list = new List<ExcelErro>();
|
DataTable excelTable = new DataTable();
|
list = ImportExcel.ExcelToTableErro(FileCode); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
excelTable = ImportExcel.ExcelToTable(FileCode); //获取Excel数据
|
for (int i = 0; i < excelTable.Rows.Count; i++)
|
{
|
//产品是否存在
|
sql = @"select partname from TMateriel_Info
|
where partname=@partname";
|
dynamicParams.Add("@partname", excelTable.Rows[i][1].ToString().Trim());
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{产品名称}";
|
erro.ErrorCont = "存货档案:{产品名称}字段" + excelTable.Rows[i][1].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
//工艺路线是否存在
|
sql = @"select name from TFlw_Rout
|
where name=@routename";
|
dynamicParams.Add("@routename", excelTable.Rows[i][2].ToString().Trim());
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{工艺路线名称}";
|
erro.ErrorCont = "工艺路线:{工艺路线名称}字段" + excelTable.Rows[i][2].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
//工序是否存在
|
sql = @"select stepname from TStep
|
where stepname=@stepname";
|
dynamicParams.Add("@stepname", excelTable.Rows[i][3].ToString().Trim());
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data1.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{工序名称}";
|
erro.ErrorCont = "工序定义:{工序名称}字段" + excelTable.Rows[i][3].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
//设备是否存在
|
sql = @"select name from TEqpInfo
|
where name=@eqpname";
|
dynamicParams.Add("@eqpname", excelTable.Rows[i][4].ToString().Trim());
|
var data2 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data2.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{设备名称}";
|
erro.ErrorCont = "设备清单:{设备名称}字段" + excelTable.Rows[i][4].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
//判断产品是否关联工艺路线
|
sql = @"select M.partname,R.name from TMateriel_Route A
|
inner join TMateriel_Info M on A.materiel_code=M.partcode
|
inner join TFlw_Rout R on A.route_code=R.code
|
where M.partname=@partname and R.name=@routename";
|
dynamicParams.Add("@partname", excelTable.Rows[i][1].ToString().Trim());
|
dynamicParams.Add("@routename", excelTable.Rows[i][2].ToString().Trim());
|
var data3 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data3.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{产品名称/工艺路线名称}";
|
erro.ErrorCont = "产品关联工艺路线:{产品名称}字段:【" + excelTable.Rows[i][1].ToString().Trim() + "】对应工艺路线名称：【" + excelTable.Rows[i][2].ToString().Trim() + "】未关联";
|
list.Add(erro);
|
}
|
//判断工艺路线是否关联工序
|
sql = @"select R.name,S.stepname from TFlw_Rtdt A
|
inner join TFlw_Rout R on A.rout_code=R.code
|
inner join TStep S on A.step_code=S.stepcode
|
where R.name=@routename and S.stepname=@stepname";
|
dynamicParams.Add("@routename", excelTable.Rows[i][2].ToString().Trim());
|
dynamicParams.Add("@stepname", excelTable.Rows[i][3].ToString().Trim());
|
var data4 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data4.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{工艺路线名称/工序名称}";
|
erro.ErrorCont = "工艺路线关联工序:{工艺路线名称}字段:【" + excelTable.Rows[i][2].ToString().Trim() + "】对应工序名称：【" + excelTable.Rows[i][3].ToString().Trim() + "】未关联";
|
list.Add(erro);
|
}
|
//判断工序是否关联设备
|
sql = @"select E.name,S.stepname from TFlw_Rteqp A
|
inner join TStep S on A.step_code=S.stepcode
|
inner join TEqpInfo E on A.eqp_code=E.code
|
where S.stepname=@stepname and E.name=@eqpname";
|
dynamicParams.Add("@stepname", excelTable.Rows[i][3].ToString().Trim());
|
dynamicParams.Add("@eqpname", excelTable.Rows[i][4].ToString().Trim());
|
var data5 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data5.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{工序名称/设备名称}";
|
erro.ErrorCont = "工序关联设备:{工序名称}字段:【" + excelTable.Rows[i][3].ToString().Trim() + "】对应设备名称：【" + excelTable.Rows[i][4].ToString().Trim() + "】未关联";
|
list.Add(erro);
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable.Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,质检标准】
|
public static List<ExcelErro> SeventeenData(string FileCode, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,缺陷定义】
|
public static List<ExcelErro> EighteenData(List<DataTable> excelTable, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
list = ImportExcel.InportExcelToTableListErro(excelTable); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
//缺陷表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
|
{
|
sql = @"select * from TDefect where code=@code";
|
dynamicParams.Add("@code", excelTable[0].Rows[k][0].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*缺陷编号(唯一)}";
|
erro.ErrorCont = "缺陷:{*缺陷编号(唯一)}字段" + excelTable[0].Rows[k][0].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TDefect where name=@name";
|
dynamicParams.Add("@name", excelTable[0].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{*缺陷名称}";
|
erro.ErrorCont = "缺陷表:{*缺陷名称}字段" + excelTable[1].Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,生产订单】
|
public static List<ExcelErro> NineteenData(string FileCode, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,库存查询】
|
public static List<ExcelErro> TwentyData(string FileCode, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
DataTable dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
|
return list;
|
}
|
|
#region【获取库存的容器中是否同一个产品】
|
|
public static Boolean getSamePNofTote(String PN, String Tote_ID)
|
{
|
Boolean bRes = false;
|
String sql = "";
|
String stockPN = "";
|
DataTable dt = null;
|
|
if (String.IsNullOrEmpty(PN))
|
{
|
bRes = true;
|
}
|
else
|
{
|
sql = @"select part_number from mes_tk_wms_stock where tote_id='" + Tote_ID + "' and qty>0 ";
|
dt = DBHelper.GetTable(sql);
|
if (dt.Rows.Count > 0)
|
{
|
stockPN = dt.Rows[0]["part_number"].ToString();
|
if (PN == stockPN)
|
{
|
bRes = true;
|
}
|
}
|
else
|
{
|
//仓库中不存在此容器
|
bRes = true;
|
}
|
}
|
return bRes;
|
}
|
#endregion
|
#endregion
|
|
#region【Excel上传数据验证,物料清单】
|
public static List<ExcelErro> TwentyOneData(string FileCode, out string StuCode, out string message, out int count)
|
{
|
message = "";
|
StuCode = "";
|
count = 0;
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
DataTable dt0, dt;
|
List<ExcelErro> list = new List<ExcelErro>();
|
List<DataTable> excelTable = new List<DataTable>();
|
list = ImportExcel.ExcelToTableListErro(FileCode); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
excelTable = ImportExcel.ExcelToTableList(FileCode); //获取Excel数据
|
//Excel查询母件+版本号是否重复
|
DataTable distinct = excelTable[0].DefaultView.ToTable(true, new string[] { "母件编码", "版本号" });
|
if (distinct.Rows.Count != excelTable[0].Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{版本号}";
|
erro.ErrorCont = "Bom母件表:{母件编码}对应的{版本号}有重复";
|
list.Add(erro);
|
}
|
//判断子表外键不存在于主表主键中的数据
|
var dt3 = from r in excelTable[1].AsEnumerable()
|
where !(
|
from rr in excelTable[0].AsEnumerable()
|
select rr.Field<string>("序号")
|
).Contains(r.Field<string>("主表序列号"))
|
select r;
|
List<DataRow> listRow = dt3.ToList();
|
if (listRow.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{主表序列号}";
|
erro.ErrorCont = "子件信息:{主表序列号}字段中有值在母件信息:{序号}中不存在";
|
list.Add(erro);
|
}
|
//数据库查询母件+版本号是否存在
|
for (int j = 0; j < excelTable[0].Rows.Count; j++)
|
{
|
if (excelTable[0].Rows[j][1].ToString().Trim() != null && excelTable[0].Rows[j][1].ToString().Trim() != "")
|
{
|
//判断母件在存货中是否存在
|
sql = @"select * from TMateriel_Info where partcode=@partcode";
|
dynamicParams.Add("@partcode", excelTable[0].Rows[j][1].ToString().Trim());
|
dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{母件编码}";
|
erro.ErrorCont = "Bom母件表:{母件编码}字段" + excelTable[0].Rows[j][1].ToString().Trim() + "在存货档案中不存在";
|
list.Add(erro);
|
}
|
|
sql = @"select * from TBom_Main where materiel_code=@code and version=@version";
|
dynamicParams.Add("@code", excelTable[0].Rows[j][1].ToString().Trim());
|
dynamicParams.Add("@version", excelTable[0].Rows[j][5].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{母件编码}";
|
erro.ErrorCont = "Bom母件表:{母件编码}字段" + excelTable[0].Rows[j][1].ToString().Trim() + "对应的版本号:" + excelTable[0].Rows[j][5].ToString() + "已存在";
|
list.Add(erro);
|
}
|
//查询母件信息对应的子件信息
|
var var_dtTable = excelTable[1].AsEnumerable().Where<DataRow>(W => W["主表序列号"].ToString() == excelTable[0].Rows[j][0].ToString().Trim());
|
DataTable ds = var_dtTable.Any() ? var_dtTable.CopyToDataTable() : excelTable[1].Clone();
|
if (ds.Rows.Count > 0)
|
{
|
//判断同母件对应的子件是否重复
|
DataTable distinct1 = ds.DefaultView.ToTable(true, "子件编码");
|
if (distinct1.Rows.Count != ds.Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{子件编码}";
|
erro.ErrorCont = "物料清单对应子件信息页签模板:主表序列号：【" + excelTable[1].Rows[j]["主表序列号"].ToString() + "】对应{子件编码}有重复";
|
list.Add(erro);
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{主表序列号}";
|
erro.ErrorCont = "物料清单对应子件信息页签模板:{主表序列号}:" + excelTable[1].Rows[j][1].ToString().Trim() + " 在母件信息页签模板中不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//子件判断
|
for (int k = 0; k < excelTable[1].Rows.Count; k++)
|
{
|
if (excelTable[1].Rows[k][3].ToString().Trim() != null && excelTable[1].Rows[k][3].ToString().Trim() != "")
|
{
|
sql = @"select * from TMateriel_Info where partcode=@partcode";
|
dynamicParams.Add("@partcode", excelTable[1].Rows[k][3].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{子件编码}";
|
erro.ErrorCont = "物料清单子件信息:{子件编码}字段" + excelTable[1].Rows[k][3].ToString().Trim() + "在存货档案中不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
if (list.Count > 0)
|
{
|
int index = 0;
|
foreach (ExcelErro item in list)
|
{
|
index++;
|
item.Seq = index.ToString();
|
}
|
StuCode = "301";
|
message = "数据验证失败";
|
}
|
else
|
{
|
StuCode = "200";
|
message = "数据验证成功";
|
count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
#endregion
|
|
#region[Excel上传数据提交]
|
#region【Excel数据上传,组织架构】
|
public static string ZerroSubmit(string FileCode, string User, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
string ZZName = "", Enable = "", WageType = "";
|
DataTable dt;
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
List<DataTable> excelTable = new List<DataTable>();
|
excelTable = ImportExcel.ExcelToTableList(FileCode);
|
//导入角色类型
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
sql = @"insert into TRoleType(roletype_code,roletype_name,torg_code)
|
values(@roletype_code,@roletype_name,@torg_code)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
roletype_code = excelTable[0].Rows[k][2].ToString().Trim(),
|
roletype_name = excelTable[0].Rows[k][3].ToString().Trim(),
|
torg_code = excelTable[0].Rows[k][1].ToString().Trim()
|
}
|
});
|
}
|
//导入角色
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
sql = @"insert into TRole(role_code,role_name,roletype_code,description,lm_user,lm_date,torg_code)
|
values(@role_code,@role_name,@roletype_code,@description,@Operator,@CreateDate,@torg_code)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
role_code = excelTable[1].Rows[i][2].ToString().Trim(),
|
role_name = excelTable[1].Rows[i][3].ToString().Trim(),
|
roletype_code = excelTable[1].Rows[i][4].ToString().Trim(),
|
description = excelTable[1].Rows[i][5].ToString().Trim(),
|
Operator = User,
|
CreateDate = DateTime.Now.ToString(),
|
torg_code = excelTable[1].Rows[i][1].ToString().Trim()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,岗位管理】
|
public static string OneSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入岗位
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
sql = @"insert into TPost(postcode,postname,status,description,lm_user,lm_date)
|
values(@postcode,@postname,@status,@description,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
postcode = excelTable[0].Rows[i][0].ToString().Trim(),
|
postname = excelTable[0].Rows[i][1].ToString().Trim(),
|
status = excelTable[0].Rows[i][2].ToString().Trim(),
|
description = excelTable[0].Rows[i][3].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,分组管理】
|
public static string TwoSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入班组
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
sql = @"insert into TGroup(usergroupcode,usergroupname,status,description,lm_user,lm_date)
|
values(@usergroupcode,@usergroupname,@status,@description,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
usergroupcode = excelTable[0].Rows[i][0].ToString().Trim(),
|
usergroupname = excelTable[0].Rows[i][1].ToString().Trim(),
|
status = excelTable[0].Rows[i][2].ToString().Trim(),
|
description = excelTable[0].Rows[i][3].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,人员管理】
|
public static string ThreeSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
DataTable dt;
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入用户
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
//sql = @"select * from TUser where usercode=@usercode";
|
//dynamicParams.Add("@usercode", excelTable[1].Rows[i][1].ToString().Trim());
|
//dt = DapperHelper.selectdata(sql, dynamicParams);
|
//if (dt.Rows.Count > 0)
|
//{
|
// continue;
|
//}
|
string Type = "";
|
switch (excelTable[0].Rows[i][3].ToString().Trim())
|
{
|
case "计件":
|
Type = "S";
|
break;
|
case "计时":
|
Type = "T";
|
break;
|
case "固定":
|
Type = "M";
|
break;
|
default:
|
break;
|
}
|
sql = @"insert into TUser(usercode,username,password,payrate,status,mobile,email,storg_code,post_code,role_code,group_code,is_system_admin,lm_user,lm_date)
|
values(@usercode,@username,@password,@payrate,@status,@mobile,@email,@storg_code,@post_code,@role_code,@group_code,@is_system_admin,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
usercode = excelTable[0].Rows[i][0].ToString().Trim(),
|
username = excelTable[0].Rows[i][1].ToString().Trim(),
|
password = excelTable[0].Rows[i][2].ToString().Trim(),
|
payrate = Type,
|
status = excelTable[0].Rows[i][4].ToString().Trim(),
|
mobile = excelTable[0].Rows[i][5].ToString().Trim(),
|
email = excelTable[0].Rows[i][6].ToString().Trim(),
|
storg_code = excelTable[0].Rows[i][7].ToString().Trim(),
|
post_code = excelTable[0].Rows[i][8].ToString().Trim(),
|
role_code = excelTable[0].Rows[i][9].ToString().Trim(),
|
group_code = excelTable[0].Rows[i][10].ToString().Trim(),
|
is_system_admin = "N",
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,角色管理】
|
public static string FourSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "", datarange = "", datapermissions = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入角色
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
//if (excelTable[0].Rows[i][3].ToString().Trim() != null && excelTable[0].Rows[i][3].ToString().Trim() != "")
|
//{
|
// //数据范围
|
// switch (excelTable[0].Rows[i][3].ToString().Trim())
|
// {
|
// case "全部":
|
// datarange = "ALL";
|
// break;
|
// case "本级":
|
// datarange = "LEVEL";
|
// break;
|
// case "本人":
|
// datarange = "PERSON";
|
// break;
|
// case "自定义":
|
// datarange = "CUSTOM";
|
// datapermissions = excelTable[0].Rows[i][4].ToString().Trim();
|
// break;
|
// default:
|
// break;
|
// }
|
//}
|
|
sql = @"insert into TRole(rolecode,rolename,status,datarange,datapermissions,identifying,description,lm_user,lm_date)
|
values(@rolecode,@rolename,@status,@datarange,@datapermissions,@identifying,@description,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
rolecode = excelTable[0].Rows[i][0].ToString().Trim(),
|
rolename = excelTable[0].Rows[i][1].ToString().Trim(),
|
status = excelTable[0].Rows[i][2].ToString().Trim(),
|
datarange = "ALL",
|
datapermissions = datapermissions,
|
identifying = "2",
|
description = excelTable[0].Rows[i][3].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,往来单位】
|
public static string FiveSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入往来单位类型
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
sql = @"insert into TCustomerType(code,name,idparent,lm_user,lm_date,data_sources)
|
values(@code,@name,@idparent,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[k][0].ToString().Trim(),
|
name = excelTable[0].Rows[k][1].ToString().Trim(),
|
idparent = excelTable[0].Rows[k][2].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = "MES"
|
}
|
});
|
}
|
//导入往来单位
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
sql = @"insert into TCustomer(code,name,status,conttacts,conttphone,addr,customertype,lm_user,lm_date,data_sources)
|
values(@code,@name,@status,@conttacts,@conttphone,@addr,@customertype,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[1].Rows[i][0].ToString().Trim(),
|
name = excelTable[1].Rows[i][1].ToString().Trim(),
|
status = excelTable[1].Rows[i][2].ToString().Trim(),
|
customertype = excelTable[1].Rows[i][3].ToString().Trim(),
|
conttacts = excelTable[1].Rows[i][4].ToString().Trim(),
|
conttphone = excelTable[1].Rows[i][5].ToString().Trim(),
|
addr = excelTable[1].Rows[i][6].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = "MES"
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,仓库设置】
|
public static string SixSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入仓库
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
sql = @"insert into TSecStck(code,name,status,ishasPosition,description,lm_user,lm_date,data_sources)
|
values(@code,@name,@status,@ishasPosition,@description,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[i][0].ToString().Trim(),
|
name = excelTable[0].Rows[i][1].ToString().Trim(),
|
status = excelTable[0].Rows[i][2].ToString().Trim(),
|
ishasPosition = excelTable[0].Rows[i][3].ToString().Trim(),
|
description = excelTable[0].Rows[i][4].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = "MES"
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,库位设置】
|
public static string SevenSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入库位
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
string status = "";
|
if (excelTable[0].Rows[i][4].ToString().Trim() != null && excelTable[0].Rows[i][4].ToString().Trim() != "")
|
{
|
switch (excelTable[0].Rows[i][3].ToString().Trim())
|
{
|
case "正常":
|
status = "Y";
|
break;
|
case "停用":
|
status = "N";
|
break;
|
default:
|
break;
|
}
|
}
|
sql = @"insert into TSecLoca(code,name,idwarehouse,idparent,status,description,lm_user,lm_date,data_sources)
|
values(@code,@name,@idwarehouse,@idparent,@status,@description,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[i][0].ToString().Trim(),
|
name = excelTable[0].Rows[i][1].ToString().Trim(),
|
idwarehouse = excelTable[0].Rows[i][2].ToString().Trim(),
|
idparent = excelTable[0].Rows[i][3].ToString().Trim(),
|
status = status,
|
description = excelTable[0].Rows[i][5].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = "MES"
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,存货档案】
|
public static string EightSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入存货类型
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
sql = @"insert into TMateriel_Class(code,name,idparent,lm_user,lm_date,data_sources)
|
values(@code,@name,@idparent,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[k][0].ToString().Trim(),
|
name = excelTable[0].Rows[k][1].ToString().Trim(),
|
idparent = excelTable[0].Rows[k][2].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = "MES"
|
}
|
});
|
}
|
//导入存货档案
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
string idunitgroup = "", idunit = "", idnounit = "";
|
int isSingleUnit = 0;
|
if (excelTable[1].Rows[i][5].ToString().Trim() == "S")
|
{
|
isSingleUnit = 1;
|
sql = @"select * from TUnit where name=@name and isSingleUnit='1'";
|
dynamicParams.Add("@name", excelTable[1].Rows[i][6].ToString().Trim());
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
idunit = dt.Rows[0]["code"].ToString();
|
}
|
if (excelTable[1].Rows[i][5].ToString().Trim() == "M")
|
{
|
sql = @"select T.code,T.name,T.isMainUnit from TUnitGroup G
|
inner join TUnit T on G.code=T.idunitgroup
|
where G.name=@name";
|
dynamicParams.Add("@name", excelTable[1].Rows[i][6].ToString().Trim());
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
idunitgroup = dt.Rows[0]["groupcode"].ToString();
|
idunit = dt.AsEnumerable().Where(row => row.Field<string>("isMainUnit") == "1").Select(row => row.Field<string>("code")).FirstOrDefault();
|
idnounit = dt.AsEnumerable().Where(row => row.Field<string>("isMainUnit") == "0").Select(row => row.Field<string>("code")).FirstOrDefault();
|
}
|
|
sql = @"insert into TMateriel_Info(partcode,partname,partspec,idunit,idunitgroup,isSingleUnit,idSubUnitByReport,idUnitByStock,
|
idUnitByPurchase,idUnitBySale,idunitbymanufacture,idinventoryclass,isPurchase,isSale,isMadeSelf,isMaterial,isMadeRequest,
|
idwarehouse,status,lm_user,lm_date,data_sources)
|
values(@partcode,@partname,@partspec,@idunit,@idunitgroup,@isSingleUnit,@idSubUnitByReport,@idUnitByStock,
|
@idUnitByPurchase,@idUnitBySale,@idunitbymanufacture,@idinventoryclass,@isPurchase,@isSale,@isMadeSelf,@isMaterial,@isMadeRequest,
|
@idwarehouse,@status,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = excelTable[1].Rows[i][0].ToString().Trim(),
|
partname = excelTable[1].Rows[i][1].ToString().Trim(),
|
partspec = excelTable[1].Rows[i][2].ToString().Trim(),
|
idunit = idunit,
|
idunitgroup = idunitgroup,
|
isSingleUnit = isSingleUnit,
|
idSubUnitByReport = idnounit,
|
idUnitByStock = idunit,
|
idUnitByPurchase = idunit,
|
idUnitBySale = idunit,
|
idunitbymanufacture = idunit,
|
idinventoryclass = excelTable[1].Rows[i][3].ToString().Trim(),
|
isPurchase = excelTable[1].Rows[i][8].ToString().Trim(),
|
isSale = excelTable[1].Rows[i][9].ToString().Trim(),
|
isMadeSelf = excelTable[1].Rows[i][10].ToString().Trim(),
|
isMaterial = excelTable[1].Rows[i][11].ToString().Trim(),
|
isMadeRequest = excelTable[1].Rows[i][12].ToString().Trim(),
|
idwarehouse = excelTable[1].Rows[i][7].ToString().Trim(),
|
status = excelTable[1].Rows[i][4].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = "MES"
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,设备清单】
|
public static string NineSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入设备类型
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
sql = @"insert into TEqpType(code,name,lm_user,lm_date,remark)
|
values(@code,@name,@lm_user,@lm_date,@remark)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[k][0].ToString().Trim(),
|
name = excelTable[0].Rows[k][1].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
remark = excelTable[0].Rows[k][2].ToString().Trim()
|
}
|
});
|
}
|
//导入设备
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
sql = @"insert into TEqpInfo(code,name,torg_code,eqptype_code,operation_ration,enable,opc_uom,lm_user,lm_date)
|
values(@code,@name,@torg_code,@eqptype_code,@operation_ration,@enable,@opc_uom,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[1].Rows[i][0].ToString().Trim(),
|
name = excelTable[1].Rows[i][1].ToString().Trim(),
|
torg_code = excelTable[1].Rows[i][2].ToString().Trim(),
|
eqptype_code = excelTable[1].Rows[i][3].ToString().Trim(),
|
operation_ration = "100",
|
enable = excelTable[1].Rows[i][4].ToString().Trim(),
|
opc_uom = "台",
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,设备点检项目】
|
public static string TenSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入设备点检项目表
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
string Type = "", IsScan = "";
|
switch (excelTable[0].Rows[i][2].ToString().Trim())
|
{
|
case "年":
|
Type = "Y";
|
break;
|
case "季":
|
Type = "S";
|
break;
|
case "月":
|
Type = "M";
|
break;
|
case "周":
|
Type = "W";
|
break;
|
case "日":
|
Type = "D";
|
break;
|
default:
|
break;
|
}
|
switch (excelTable[0].Rows[i][3].ToString().Trim())
|
{
|
case "是":
|
IsScan = "Y";
|
break;
|
case "否":
|
IsScan = "N";
|
break;
|
default:
|
break;
|
}
|
sql = @"insert into TEqpchk_Item(code,name,description,isscan,cycle,lm_user,lm_date)
|
values(@code,@name,@descr,@isscan,@cycle,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[i][0].ToString().Trim(),
|
name = excelTable[0].Rows[i][1].ToString().Trim(),
|
descr = excelTable[0].Rows[i][4].ToString().Trim(),
|
isscan = IsScan,
|
cycle = Type,
|
Operator = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,设备点检标准】
|
public static string Ten_oneSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入设备点检标准主表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
sql = @"insert into TEqpchk_Main(code,name,description,iscontr,lm_user,lm_date)
|
values(@code,@name,@description,@iscontr,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[k][0].ToString().Trim(),
|
name = excelTable[0].Rows[k][1].ToString().Trim(),
|
iscontr = excelTable[0].Rows[k][2].ToString().Trim(),
|
description = excelTable[0].Rows[k][3].ToString().Trim(),
|
CreateDate = DateTime.Now.ToString(),
|
Operator = us.usercode
|
}
|
});
|
}
|
//导入设备点检标准部位子表
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
string Type = "";
|
switch (excelTable[1].Rows[i][6].ToString().Trim())
|
{
|
case "日":
|
Type = "D";
|
break;
|
default:
|
break;
|
}
|
sql = @"insert into TEqpchk_Deta(seq,code,name,eqpchk_main_code,chkdesc,isscan,cycle,lm_user,lm_date)
|
values(@seq,@code,@name,@eqpchk_main_code,@description,@isscan,@cycle,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
seq = excelTable[1].Rows[i][1].ToString().Trim(),
|
code = excelTable[1].Rows[i][2].ToString().Trim(),
|
name = excelTable[1].Rows[i][3].ToString().Trim(),
|
eqpchk_main_code = excelTable[1].Rows[i][0].ToString().Trim(),
|
description = excelTable[1].Rows[i][4].ToString().Trim(),
|
isscan = excelTable[1].Rows[i][5].ToString().Trim(),
|
cycle = Type,
|
Operator = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,设备保养项目】
|
public static string ElevenSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入设备点检项目表
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
string IsScan = "";
|
switch (excelTable[0].Rows[i][2].ToString().Trim())
|
{
|
case "是":
|
IsScan = "Y";
|
break;
|
case "否":
|
IsScan = "N";
|
break;
|
default:
|
break;
|
}
|
sql = @"insert into TEqpmai_Item(code,name,description,isscan,cycle,lm_user,lm_date)
|
values(@code,@name,@descr,@isscan,@cycle,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[i][0].ToString().Trim(),
|
name = excelTable[0].Rows[i][1].ToString().Trim(),
|
descr = excelTable[0].Rows[i][3].ToString().Trim(),
|
isscan = IsScan,
|
cycle = "",
|
Operator = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,设备保养标准】
|
public static string Eleven_oneSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入设备保养标准主表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
string Type = "";
|
switch (excelTable[0].Rows[k][2].ToString().Trim())
|
{
|
case "年":
|
Type = "Y";
|
break;
|
case "季":
|
Type = "S";
|
break;
|
case "月":
|
Type = "M";
|
break;
|
case "周":
|
Type = "W";
|
break;
|
default:
|
break;
|
}
|
sql = @"insert into TEqpmai_Main(code,name,description,main_cycle,lm_user,lm_date)
|
values(@code,@name,@description,@main_cycle,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[k][0].ToString().Trim(),
|
name = excelTable[0].Rows[k][1].ToString().Trim(),
|
main_cycle = Type,
|
description = excelTable[0].Rows[k][3].ToString().Trim(),
|
CreateDate = DateTime.Now.ToString(),
|
Operator = us.usercode
|
}
|
});
|
}
|
//导入设备保养标准部位子表
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
sql = @"insert into TEqpmai_Deta(seq,code,name,eapmai_code,chk_desc,isscan,lm_user,lm_date)
|
values(@seq,@code,@name,@eapmai_code,@description,@isscan,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
seq = excelTable[1].Rows[i][1].ToString().Trim(),
|
code = excelTable[1].Rows[i][2].ToString().Trim(),
|
name = excelTable[1].Rows[i][3].ToString().Trim(),
|
eapmai_code = excelTable[1].Rows[i][0].ToString().Trim(),
|
description = excelTable[1].Rows[i][4].ToString().Trim(),
|
isscan = excelTable[1].Rows[i][5].ToString().Trim(),
|
Operator = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,模具清单】
|
public static string TwelveSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入模具清单
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
sql = @"insert into TMouldInfo(code,name,spec,status,surp_life,resi_life,lm_user,lm_date,warehousecode,location_code)
|
values(@code,@name,@spec,@status,@surp_life,@resi_life,@lm_user,@lm_date,@warehousecode,@location_code)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[k][0].ToString().Trim(),
|
name = excelTable[0].Rows[k][1].ToString().Trim(),
|
spec = excelTable[0].Rows[k][2].ToString().Trim(),
|
status = excelTable[0].Rows[k][3].ToString().Trim(),
|
warehousecode = excelTable[0].Rows[k][4].ToString().Trim(),
|
location_code = excelTable[0].Rows[k][5].ToString().Trim(),
|
surp_life = decimal.Parse(excelTable[0].Rows[k][6].ToString().Trim() == "" ? "0" : excelTable[0].Rows[k][6].ToString().Trim()),
|
resi_life = decimal.Parse(excelTable[0].Rows[k][7].ToString().Trim() == "" ? "0" : excelTable[0].Rows[k][7].ToString().Trim()),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//写入模具关联产品表
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
sql = @"insert into TMouldInfoPart(mouldcode,partcode,lm_user,lm_date)
|
values(@mouldcode,@partcode,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
mouldcode = excelTable[1].Rows[i][0].ToString().Trim(),
|
partcode = excelTable[1].Rows[i][1].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,模具点检项目】
|
public static string ThirteenSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入模具点检项目表
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
sql = @"insert into TMouldchk_Item(code,name,description,lm_user,lm_date)
|
values(@code,@name,@description,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[i][0].ToString().Trim(),
|
name = excelTable[0].Rows[i][1].ToString().Trim(),
|
description = excelTable[0].Rows[i][2].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,模具点检标准】
|
public static string Thirteen_oneSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入模具点检标准主表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
sql = @"insert into TMouldchk_Main(code,name,description,lm_user,lm_date)
|
values(@code,@name,@description,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[k][0].ToString().Trim(),
|
name = excelTable[0].Rows[k][1].ToString().Trim(),
|
description = excelTable[0].Rows[k][2].ToString().Trim(),
|
CreateDate = DateTime.Now.ToString(),
|
Operator = us.usercode
|
}
|
});
|
}
|
//导入模具点检标准关联点检项子表
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
sql = @"insert into TMouldchk_Deta(seq,code,name,mouldchk_main_code,chkdesc,lm_user,lm_date)
|
values(@seq,@code,@name,@mouldchk_main_code,@description,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
seq = excelTable[1].Rows[i][1].ToString().Trim(),
|
code = excelTable[1].Rows[i][2].ToString().Trim(),
|
name = excelTable[1].Rows[i][3].ToString().Trim(),
|
mouldchk_main_code = excelTable[1].Rows[i][0].ToString().Trim(),
|
description = excelTable[1].Rows[i][4].ToString().Trim(),
|
Operator = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,模具保养项目】
|
public static string FourteenSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入模具保养项目表
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
sql = @"insert into TMouldmai_Item(code,name,description,lm_user,lm_date)
|
values(@code,@name,@description,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[i][0].ToString().Trim(),
|
name = excelTable[0].Rows[i][1].ToString().Trim(),
|
description = excelTable[0].Rows[i][2].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,模具保养标准】
|
public static string Fourteen_oneSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入模具保养标准主表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
sql = @"insert into TMouldmai_Main(code,name,description,lm_user,lm_date)
|
values(@code,@name,@description,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[k][0].ToString().Trim(),
|
name = excelTable[0].Rows[k][1].ToString().Trim(),
|
description = excelTable[0].Rows[k][2].ToString().Trim(),
|
CreateDate = DateTime.Now.ToString(),
|
Operator = us.usercode
|
}
|
});
|
}
|
//导入模具保养标准关联保养项子表
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
sql = @"insert into TMouldmai_Deta(seq,code,name,mouldmai_main_code,chkdesc,lm_user,lm_date)
|
values(@seq,@code,@name,@mouldmai_main_code,@description,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
seq = excelTable[1].Rows[i][1].ToString().Trim(),
|
code = excelTable[1].Rows[i][2].ToString().Trim(),
|
name = excelTable[1].Rows[i][3].ToString().Trim(),
|
mouldmai_main_code = excelTable[1].Rows[i][0].ToString().Trim(),
|
description = excelTable[1].Rows[i][4].ToString().Trim(),
|
Operator = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,工序定义】
|
public static string FifteenSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入工序
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
sql = @"insert into TStep(stepcode,stepname,enable,descr,flwtype,lm_user,lm_date)
|
values(@stepcode,@stepname,@enable,@descr,@flwtype,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
stepcode = excelTable[0].Rows[i][0].ToString().Trim(),
|
stepname = excelTable[0].Rows[i][1].ToString().Trim(),
|
enable = excelTable[0].Rows[i][3].ToString().Trim(),
|
descr = excelTable[0].Rows[i][4].ToString().Trim(),
|
flwtype = excelTable[0].Rows[i][2].ToString().Trim(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,工艺路线】
|
public static string SixteenSubmit(string FileCode, string User, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
try
|
{
|
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,节拍工价】
|
public static string TwentyFourSubmit(string FileCode, string User, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
DataTable excelTable = new DataTable();
|
excelTable = ImportExcel.ExcelToTable(FileCode);
|
//导入设备点检项目表
|
for (int i = 0; i < excelTable.Rows.Count; i++)
|
{
|
string partcode = "", routecode = "", stepcode = "", eqpcode = "", wkspcode = "";
|
//查询产品编码
|
sql = @"select partcode from TMateriel_Info
|
where partname=@partname";
|
dynamicParams.Add("@partname", excelTable.Rows[i][1].ToString().Trim());
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
partcode = data.Rows[0]["partcode"].ToString();
|
}
|
else
|
{
|
StuCode = "产品名称：【" + excelTable.Rows[i][1].ToString().Trim() + "】对应编码不存在！";
|
message = "导入失败！";
|
}
|
//查询工艺路线编码
|
sql = @"select code from TFlw_Rout
|
where name=@routename";
|
dynamicParams.Add("@routename", excelTable.Rows[i][2].ToString().Trim());
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
routecode = data0.Rows[0]["code"].ToString();
|
}
|
else
|
{
|
StuCode = "工艺路线名称：【" + excelTable.Rows[i][2].ToString().Trim() + "】对应编码不存在！";
|
message = "导入失败！";
|
}
|
//查询工序编码
|
sql = @"select stepcode from TStep
|
where stepname=@stepname";
|
dynamicParams.Add("@stepname", excelTable.Rows[i][3].ToString().Trim());
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data1.Rows.Count > 0)
|
{
|
stepcode = data1.Rows[0]["stepcode"].ToString();
|
}
|
else
|
{
|
StuCode = "工序名称：【" + excelTable.Rows[i][3].ToString().Trim() + "】对应编码不存在！";
|
message = "导入失败！";
|
}
|
//查询设备编码
|
sql = @"select code from TEqpInfo
|
where name=@eqpname";
|
dynamicParams.Add("@eqpname", excelTable.Rows[i][4].ToString().Trim());
|
var data4 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data4.Rows.Count > 0)
|
{
|
eqpcode = data4.Rows[0]["code"].ToString();
|
}
|
else
|
{
|
StuCode = "设备名称：【" + excelTable.Rows[i][4].ToString().Trim() + "】对应编码不存在！";
|
message = "导入失败！";
|
}
|
//查询车间编码
|
sql = @"select wksp_code from TEqpInfo
|
where name=@eqpname";
|
dynamicParams.Add("@eqpname", excelTable.Rows[i][4].ToString().Trim());
|
var data5 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data5.Rows.Count > 0)
|
{
|
wkspcode = data5.Rows[0]["wksp_code"].ToString();
|
}
|
else
|
{
|
StuCode = "设备名称：【" + excelTable.Rows[i][4].ToString().Trim() + "】对应车间编码不存在！";
|
message = "导入失败！";
|
}
|
//查询工序节拍表中：产品+工艺路线+工序+设备是否存在
|
sql = @"select * from TPrteEqp_Stad
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode";
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@routecode", routecode);
|
dynamicParams.Add("@stepcode", stepcode);
|
dynamicParams.Add("@eqpcode", eqpcode);
|
var data6 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data6.Rows.Count > 0)
|
{
|
sql = @"update TPrteEqp_Stad set stand_value=@stand_value,eqp_value=@eqp_value,cavity_qty=@cavity_qty,unprice=@unprice,lm_user=@username,lm_date=@userdate
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode ";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = partcode,
|
routecode = routecode,
|
stepcode = stepcode,
|
eqpcode = eqpcode,
|
stand_value = excelTable.Rows[i][7].ToString().Trim(),
|
eqp_value = excelTable.Rows[i][6].ToString().Trim(),
|
cavity_qty = excelTable.Rows[i][8].ToString().Trim(),
|
unprice = excelTable.Rows[i][5].ToString().Trim(),
|
username = User,
|
userdate = DateTime.Now.ToString()
|
}
|
});
|
}
|
else
|
{
|
sql = @"insert into TPrteEqp_Stad(materiel_code,eqp_code,stand_value,route_code,unprice,eqp_value,cavity_qty,wkspcode,lm_user,lm_date,step_code)
|
values(@partcode,@eqpcode,@stand_value,@routecode,@unprice,@eqp_value,@cavity_qty,@wkspcode,@username,@userdate,@stepcode)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = partcode,
|
eqpcode = eqpcode,
|
stand_value = excelTable.Rows[i][7].ToString().Trim(),
|
routecode = routecode,
|
unprice = excelTable.Rows[i][5].ToString().Trim(),
|
eqp_value = excelTable.Rows[i][6].ToString().Trim(),
|
cavity_qty = excelTable.Rows[i][8].ToString().Trim(),
|
wkspcode = wkspcode,
|
username = User,
|
userdate = DateTime.Now.ToString(),
|
stepcode = stepcode
|
}
|
});
|
}
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,质检标准】
|
public static string SeventeenSubmit(string FileCode, string User, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
try
|
{
|
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,缺陷定义】
|
public static string EighteenSubmit(List<DataTable> excelTable, User us, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//导入缺陷
|
for (int i = 0; i < excelTable[0].Rows.Count; i++)
|
{
|
sql = @"insert into TDefect(code,name,description,is_step,lm_user,lm_date)
|
values(@code,@name,@description,@is_step,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[0].Rows[i][0].ToString().Trim(),
|
name = excelTable[0].Rows[i][1].ToString().Trim(),
|
description = excelTable[0].Rows[i][2].ToString().Trim(),
|
is_step = "N",
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,生产订单】
|
public static string NineteenSubmit(string FileCode, string User, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
try
|
{
|
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,库存查询】
|
public static string TwentySubmit(string FileCode, string User, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
try
|
{
|
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#region【Excel数据上传,物料清单】
|
public static string TwentyOneSubmit(string FileCode, string User, out string StuCode)
|
{
|
string message = ""; StuCode = "";
|
string sql = "";
|
int BomID = 0; //最大id
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
List<DataTable> excelTable = new List<DataTable>();
|
excelTable = ImportExcel.ExcelToTableList(FileCode);
|
//获取主表当前最大ID
|
BomID = DapperHelper.insertReturnId("TBom_Main");
|
//导入Bom主表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
BomID = BomID + 1;
|
sql = @"insert into TBom_Main(materiel_code,quantity,status,version,lm_user,lm_date,startdate)
|
values(@materiel_code,@quantity,@status,@version,@lm_user,@lm_date,@startdate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
materiel_code = excelTable[0].Rows[k][1].ToString().Trim(),
|
quantity = excelTable[0].Rows[k][3].ToString().Trim(),
|
status = excelTable[0].Rows[k][4].ToString().Trim(),
|
version = excelTable[0].Rows[k][5].ToString().Trim(),
|
lm_user = User,
|
lm_date = DateTime.Now.ToString(),
|
startdate = DateTime.Now.ToString()
|
}
|
});
|
//查询母件信息对应的子件信息
|
DataTable ds = excelTable[1].AsEnumerable().Where(item => item["主表序列号"].ToString().Equals(excelTable[0].Rows[k][0].ToString())).CopyToDataTable();
|
//导入Bom子表
|
for (int i = 0; i < ds.Rows.Count; i++)
|
{
|
string Type = "";
|
switch (ds.Rows[i][9].ToString().Trim())
|
{
|
case "主料":
|
Type = "Z";
|
break;
|
case "辅料":
|
Type = "F";
|
break;
|
default:
|
break;
|
}
|
sql = @"insert into TBom_Deta(m_id,seq,smateriel_code,base_quantity,loss_quantity,total_quantity,pn_type)
|
values(@m_id,@seq,@smateriel_code,@base_quantity,@loss_quantity,@total_quantity,@pn_type)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
m_id = BomID,
|
seq = ds.Rows[i][2].ToString().Trim(),
|
smateriel_code = ds.Rows[i][3].ToString().Trim(),
|
base_quantity = ds.Rows[i][6].ToString().Trim(),
|
loss_quantity = ds.Rows[i][7].ToString().Trim(),
|
total_quantity = ds.Rows[i][8].ToString().Trim(),
|
pn_type = Type
|
}
|
});
|
}
|
}
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
StuCode = "200";
|
message = "导入成功！";
|
}
|
else
|
{
|
StuCode = "300";
|
message = "导入失败！";
|
}
|
}
|
catch (Exception e)
|
{
|
StuCode = "300";
|
message = e.Message;
|
}
|
return message;
|
}
|
#endregion
|
|
#endregion
|
}
|
}
|
