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 Two(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数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 5)
|
{
|
code = "300";
|
Message = "用户班组模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns.Count != 10)
|
{
|
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 = "用户班组模板：表头信息不符合规范,第2列应为{班组编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "班组名称")
|
{
|
code = "300";
|
Message = "用户班组模板：表头信息不符合规范,第3列应为{班组名称}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "班组描述")
|
{
|
code = "300";
|
Message = "用户班组模板：表头信息不符合规范,第4列应为{班组描述}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "用户清单模板：表头信息不符合规范,第1列应为{序号}";
|
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 if (excelTable[1].Columns[7].ColumnName != "组织编码")
|
{
|
code = "300";
|
Message = "用户清单模板：表头信息不符合规范,第8列应为{组织编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[8].ColumnName != "工资类型")
|
{
|
code = "300";
|
Message = "用户清单模板：表头信息不符合规范,第9列应为{工资类型}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[9].ColumnName != "用户组编码")
|
{
|
code = "300";
|
Message = "用户清单模板：表头信息不符合规范,第10列应为{用户组编码}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,角色权限模板】
|
public static string One(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数";
|
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 = "角色类型模板：表头信息不符合规范,第1列应为{组织编码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "角色类型编号(唯一)")
|
{
|
code = "300";
|
Message = "角色类型模板：表头信息不符合规范,第2列应为{角色类型编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "角色类型名称")
|
{
|
code = "300";
|
Message = "角色类型模板：表头信息不符合规范,第3列应为{角色类型名称}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "角色清单模板：表头信息不符合规范,第1列应为{序号}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[1].ColumnName != "组织编码")
|
{
|
code = "300";
|
Message = "角色清单模板：表头信息不符合规范,第1列应为{组织编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[2].ColumnName != "角色编号(唯一)")
|
{
|
code = "300";
|
Message = "角色清单模板：表头信息不符合规范,第2列应为{角色编号(唯一)}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[3].ColumnName != "角色姓名")
|
{
|
code = "300";
|
Message = "角色清单模板：表头信息不符合规范,第3列应为{角色姓名}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[4].ColumnName != "角色类型编码")
|
{
|
code = "300";
|
Message = "角色清单模板：表头信息不符合规范,第4列应为{角色类型编码}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[5].ColumnName != "角色描述")
|
{
|
code = "300";
|
Message = "角色清单模板：表头信息不符合规范,第5列应为{角色描述}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,工位清单模板】
|
public static string Three(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
DataTable excelTable = new DataTable();
|
excelTable = ImportExcel.ExcelToTable(FileCode);
|
if (excelTable.Columns.Count != 9)
|
{
|
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 != "数采标识1")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[8].ColumnName != "数采标识2")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,往来单位清单模板】
|
public static string Four(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 != 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[0].Columns[4].ColumnName != "联系人")
|
{
|
code = "300";
|
Message = "往来单位模板不符合规范：表头信息不符合规范,第5列应为{联系人}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[5].ColumnName != "联系方式")
|
{
|
code = "300";
|
Message = "往来单位模板不符合规范：表头信息不符合规范,第6列应为{联系方式}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[6].ColumnName != "地址")
|
{
|
code = "300";
|
Message = "往来单位模板不符合规范：表头信息不符合规范,第7列应为{地址}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,仓库、库位定义模板】
|
public static string SixOne(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数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 4)
|
{
|
code = "300";
|
Message = "仓库清单模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns.Count != 5)
|
{
|
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 != "序号")
|
{
|
code = "300";
|
Message = "库位清单模板：表头信息不符合规范,第1列应为{序号}";
|
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
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,容器定义模板】
|
public static string Seven(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
DataTable excelTable = new DataTable();
|
excelTable = ImportExcel.ExcelToTable(FileCode);
|
if (excelTable.Columns.Count != 5)
|
{
|
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 != "容器自重（KG）")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else if (excelTable.Columns[4].ColumnName != "容器类型")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,存货档案模板】
|
public static string Eight(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
DataTable excelTable = new DataTable();
|
excelTable = ImportExcel.ExcelToTable(FileCode);
|
if (excelTable.Columns.Count != 11)
|
{
|
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 if (excelTable.Columns[10].ColumnName != "库存上限")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,设备清单模板】
|
public static string Nine(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 != 7)
|
{
|
code = "300";
|
Message = "设备清单模板(设备清单)不符合规范,请检查列名字段数";
|
}
|
else if (excelTable[0].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{序号}";
|
}
|
else if (excelTable[0].Columns[1].ColumnName != "设备编号(唯一)")
|
{
|
code = "300";
|
Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{设备编号(唯一)}";
|
}
|
else if (excelTable[0].Columns[2].ColumnName != "设备名称")
|
{
|
code = "300";
|
Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{设备名称}";
|
}
|
else if (excelTable[0].Columns[3].ColumnName != "投入日期")
|
{
|
code = "300";
|
Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{投入日期}";
|
}
|
else if (excelTable[0].Columns[4].ColumnName != "车间编码")
|
{
|
code = "300";
|
Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{车间编码}";
|
}
|
else if (excelTable[0].Columns[5].ColumnName != "使用状态")
|
{
|
code = "300";
|
Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{使用状态}";
|
}
|
else if (excelTable[0].Columns[6].ColumnName != "稼动率（%）")
|
{
|
code = "300";
|
Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{稼动率（%）}";
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,设备点检项目模板】
|
public static string Ten(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 != 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[0].Columns[4].ColumnName != "是否扫码")
|
{
|
code = "300";
|
Message = "设备点检项目模板不符合规范：表头信息不符合规范,第4列应为{是否扫码}";
|
return Message;
|
}
|
else if (excelTable[0].Columns[5].ColumnName != "点检项目要求")
|
{
|
code = "300";
|
Message = "设备点检项目模板不符合规范：表头信息不符合规范,第4列应为{点检项目要求}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,设备点检标准模板】
|
public static string TwentyTwo(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数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 5)
|
{
|
code = "300";
|
Message = "设备点检标准页签模板不符合规范,请检查列名字段数";
|
return Message;
|
}
|
else if (excelTable[1].Columns.Count != 8)
|
{
|
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 = "设备点检标准页签模板：表头信息不符合规范,第5列应为{标准描述}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第1列应为{序号}";
|
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 if (excelTable[1].Columns[7].ColumnName != "点检周期")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第8列应为{点检周期}";
|
return Message;
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,设备保养项目模板】
|
public static string Eleven(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 != 5)
|
{
|
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
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,设备保养标准模板】
|
public static string TwentyThree(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数";
|
return Message;
|
|
}
|
else if (excelTable[0].Columns.Count != 5)
|
{
|
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[0].Columns[4].ColumnName != "标准描述")
|
{
|
code = "300";
|
Message = "设备保养标准页签模板：表头信息不符合规范,第5列应为{标准描述}";
|
return Message;
|
}
|
else if (excelTable[1].Columns[0].ColumnName != "序号")
|
{
|
code = "300";
|
Message = "设备点检部位页签模板：表头信息不符合规范,第1列应为{序号}";
|
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 Twelve(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 != "使用状态")
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名";
|
}
|
else
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,工装点检模板】
|
public static string Thirteen(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 != 3)
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名字段数";
|
}
|
else if (excelTable[1].Columns.Count != 8)
|
{
|
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[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 != "SEQ(部位顺序)")
|
{
|
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
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,工装保养模板】
|
public static string Fourteen(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 != 3)
|
{
|
code = "300";
|
Message = "模板不符合规范,请检查列名字段数";
|
}
|
else if (excelTable[1].Columns.Count != 8)
|
{
|
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[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 != "SEQ(部位顺序)")
|
{
|
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
|
{
|
code = "200";
|
Message = "模板检验通过";
|
}
|
return Message;
|
}
|
#endregion
|
|
#region【Excel模板上传验证,工序定义模板】
|
public static string Fifteen(string FileCode, out string code)
|
{
|
string Message = "";
|
code = "";
|
DataTable excelTable = new DataTable();
|
excelTable = ImportExcel.ExcelToTable(FileCode);
|
if (excelTable.Columns.Count != 4)
|
{
|
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
|
{
|
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(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 != 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 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> TwoData(string FileCode, 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<DataTable> excelTable = new List<DataTable>();
|
list = ImportExcel.ExcelToTableListErro(FileCode); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
excelTable = ImportExcel.ExcelToTableList(FileCode); //获取Excel数据
|
//主表
|
for (int j = 0; j < excelTable[0].Rows.Count; j++)
|
{
|
if (excelTable[0].Rows[j][2].ToString().Trim() != null && excelTable[0].Rows[j][2].ToString().Trim() != "")
|
{
|
sql = @"select * from TGroup where group_code=@group_code";
|
dynamicParams.Add("@group_code", excelTable[0].Rows[j][2].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][2].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//子表
|
for (int k = 0; k < excelTable[1].Rows.Count; k++)
|
{
|
if (excelTable[1].Rows[k][1].ToString().Trim() != null && excelTable[1].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TUser where usercode=@usercode";
|
dynamicParams.Add("@usercode", 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][7].ToString().Trim() != null && excelTable[1].Rows[k][7].ToString().Trim() != "")
|
{
|
sql = @"select * from TOrganization where org_code=@org_code";
|
dynamicParams.Add("@org_code", excelTable[1].Rows[k][7].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt == null || dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{组织编码}";
|
erro.ErrorCont = "用户表:{组织编码}字段" + excelTable[1].Rows[k][7].ToString().Trim() + "不存在";
|
list.Add(erro);
|
}
|
}
|
if (excelTable[1].Rows[k][9].ToString().Trim() != null && excelTable[1].Rows[k][9].ToString().Trim() != "")
|
{
|
sql = @"select torg_code from TGroup where group_code=@group_code";
|
dynamicParams.Add("@group_code", excelTable[1].Rows[k][9].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["torg_code"].ToString() != excelTable[1].Rows[k][7].ToString().Trim())
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{用户组编码}";
|
erro.ErrorCont = "用户表:{用户组编码}字段" + excelTable[1].Rows[k][9].ToString().Trim() + " 不是组织编码:" + excelTable[1].Rows[k][7].ToString().Trim() + "下的用户组";
|
list.Add(erro);
|
}
|
}
|
}
|
}
|
//判断子表组织编码不存在于主表组织编码中的数据
|
var dt2 = 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> listRow2 = dt2.ToList();
|
if (listRow2.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{组织编码}";
|
erro.ErrorCont = "用户表:{组织编码}字段中有值在班组表:{组织编码}中不存在";
|
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> OneData(string FileCode, 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<DataTable> excelTable = new List<DataTable>();
|
list = ImportExcel.ExcelToTableListErro(FileCode); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
excelTable = ImportExcel.ExcelToTableList(FileCode); //获取Excel数据
|
//主表
|
for (int j = 0; j < excelTable[0].Rows.Count; j++)
|
{
|
if (excelTable[1].Rows[j][1].ToString().Trim() != null && excelTable[1].Rows[j][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TOrganization where org_code=@org_code";
|
dynamicParams.Add("@org_code", excelTable[1].Rows[j][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt == null || 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[0].Rows[j][2].ToString().Trim() != null && excelTable[0].Rows[j][2].ToString().Trim() != "")
|
{
|
sql = @"select * from TRoleType where roletype_code=@roletype_code";
|
dynamicParams.Add("@roletype_code", excelTable[0].Rows[j][2].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][2].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//子表
|
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 TRole where role_code=@role_code";
|
dynamicParams.Add("@role_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);
|
}
|
}
|
if (excelTable[1].Rows[k][1].ToString().Trim() != null && excelTable[1].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TOrganization where org_code=@org_code";
|
dynamicParams.Add("@org_code", excelTable[1].Rows[k][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt == null || 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][4].ToString().Trim() != null && excelTable[1].Rows[k][4].ToString().Trim() != "")
|
{
|
sql = @"select torg_code from TRoleType where roletype_code=@roletype_code";
|
dynamicParams.Add("@roletype_code", excelTable[1].Rows[k][4].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
if (dt.Rows[0]["torg_code"].ToString() != excelTable[1].Rows[k][1].ToString().Trim())
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{角色类型编码}";
|
erro.ErrorCont = "角色表:{角色类型编码}字段" + excelTable[1].Rows[k][4].ToString().Trim() + " 不是组织编码:" + excelTable[1].Rows[k][1].ToString().Trim() + "下的角色组";
|
list.Add(erro);
|
}
|
}
|
}
|
}
|
//判断子表组织编码不存在于主表组织编码中的数据
|
var dt2 = 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> listRow2 = dt2.ToList();
|
if (listRow2.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{组织编码}";
|
erro.ErrorCont = "角色表:{组织编码}字段中有值在角色类型表:{组织编码}中不存在";
|
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> ThreeData(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> FourData(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++)
|
{
|
if (excelTable.Rows[i][1].ToString().Trim() != null && excelTable.Rows[i][1].ToString().Trim() != "")
|
{
|
sql = @"select code from TCustomer where code=@code";
|
|
dynamicParams.Add("@code", excelTable.Rows[i][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{往来单位编码(唯一)}";
|
erro.ErrorCont = "往来单位表:{往来单位编码(唯一)}字段" + excelTable.Rows[i][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.Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,仓库、库位定义】
|
public static List<ExcelErro> SixOneData(string FileCode, 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<DataTable> excelTable = new List<DataTable>();
|
list = ImportExcel.ExcelToTableListErro(FileCode); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
excelTable = ImportExcel.ExcelToTableList(FileCode); //获取Excel数据
|
//仓库表
|
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 T_Sec_Stck where code=@code";
|
dynamicParams.Add("@code", 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][1].ToString().Trim() != null && excelTable[1].Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from T_Sec_Loca where code=@code";
|
dynamicParams.Add("@code", 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][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> SevenData(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> EightData(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> NineData(string FileCode, 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>();
|
DataTable excelTable = new DataTable();
|
list = ImportExcel.ExcelToTableErro(FileCode); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
excelTable = ImportExcel.ExcelToTable(FileCode); //获取Excel数据
|
//设备清单表
|
for (int k = 0; k < excelTable.Rows.Count; k++)
|
{
|
if (excelTable.Rows[k][1].ToString().Trim() != null && excelTable.Rows[k][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpInfo where code=@code";
|
dynamicParams.Add("@code", excelTable.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.Rows[k][1].ToString().Trim() + "已存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//车间判断
|
for (int m = 0; m < excelTable.Rows.Count; m++)
|
{
|
if (excelTable.Rows[m][4].ToString().Trim() != null && excelTable.Rows[m][4].ToString().Trim() != "")
|
{
|
sql = @"select * from TOrganization where org_code=@code and description='W'";
|
dynamicParams.Add("@code", excelTable.Rows[m][4].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count <= 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{车间编码}";
|
erro.ErrorCont = "设备表:{车间编码}字段" + excelTable.Rows[m][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> TenData(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数据
|
//当前Excel数据中点检标准页签中,设备点检标准编号(唯一是否有重复)
|
DataTable distinct = excelTable.DefaultView.ToTable(true, "点检项目编号(唯一)");
|
if (distinct.Rows.Count != excelTable.Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{点检项目编号(唯一)}";
|
erro.ErrorCont = "设备点检项目页签模板:{点检项目编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
for (int i = 0; i < excelTable.Rows.Count; i++)
|
{
|
if (excelTable.Rows[i][1].ToString().Trim() != null && excelTable.Rows[i][1].ToString().Trim() != "")
|
{
|
sql = @"select code from TEqpchk_Item where code=@code";
|
|
dynamicParams.Add("@code", excelTable.Rows[i][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{点检项目编号(唯一)}";
|
erro.ErrorCont = "设备点检项目表:{点检项目编号(唯一)}字段" + excelTable.Rows[i][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.Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,设备点检标准】
|
public static List<ExcelErro> TwentyTwoData(string FileCode, 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<DataTable> excelTable = new List<DataTable>();
|
list = ImportExcel.ExcelToTableListErro(FileCode); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
excelTable = ImportExcel.ExcelToTableList(FileCode); //获取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][1].ToString().Trim() != null && excelTable[0].Rows[j][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpchk_Main where code=@code";
|
dynamicParams.Add("@code", 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 var_dtTable = excelTable[1].AsEnumerable().Where<DataRow>(W => W["设备点检标准编号"].ToString() == excelTable[0].Rows[j][1].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][2].ToString().Trim() + " 对应{设备点检部位编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{设备点检部位编号(唯一)}";
|
erro.ErrorCont = "设备点检部位页签模板:{设备点检标准编号}:" + excelTable[1].Rows[j][2].ToString().Trim() + " 在设备点检标准页签模板中不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//当前Excel数据中点检部位页签中,设备点检部位编号(唯一)在设备点检部位表中是否存在
|
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 TEqpchk_Item where code=@code";
|
dynamicParams.Add("@code", 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() + "在数据表中不存在";
|
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> ElevenData(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数据
|
//当前Excel数据中点检标准页签中,设备点检标准编号(唯一是否有重复)
|
DataTable distinct = excelTable.DefaultView.ToTable(true, "保养项目编号(唯一)");
|
if (distinct.Rows.Count != excelTable.Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{保养项目编号(唯一)}";
|
erro.ErrorCont = "设备保养项目页签模板:{保养项目编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
for (int i = 0; i < excelTable.Rows.Count; i++)
|
{
|
if (excelTable.Rows[i][1].ToString().Trim() != null && excelTable.Rows[i][1].ToString().Trim() != "")
|
{
|
sql = @"select code from TEqpmai_Item where code=@code";
|
|
dynamicParams.Add("@code", excelTable.Rows[i][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{保养项目编号(唯一)}";
|
erro.ErrorCont = "设备保养项目表:{点检保养编号(唯一)}字段" + excelTable.Rows[i][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.Rows.Count;
|
}
|
return list;
|
}
|
#endregion
|
|
#region【Excel上传数据验证,设备保养标准】
|
public static List<ExcelErro> TwentyThreeData(string FileCode, 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<DataTable> excelTable = new List<DataTable>();
|
list = ImportExcel.ExcelToTableListErro(FileCode); //验证Excel数据必填字段是否为空、唯一字段是否重复
|
excelTable = ImportExcel.ExcelToTableList(FileCode); //获取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][1].ToString().Trim() != null && excelTable[0].Rows[j][1].ToString().Trim() != "")
|
{
|
sql = @"select * from TEqpmai_Main where code=@code";
|
dynamicParams.Add("@code", 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 var_dtTable = excelTable[1].AsEnumerable().Where<DataRow>(W => W["设备保养标准编号"].ToString() == excelTable[0].Rows[j][1].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][2].ToString().Trim() + " 对应{设备保养部位编号(唯一)}有重复";
|
list.Add(erro);
|
}
|
}
|
else
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{设备保养部位编号(唯一)}";
|
erro.ErrorCont = "设备保养部位页签模板:{设备保养标准编号}:" + excelTable[1].Rows[j][2].ToString().Trim() + " 在设备保养标准页签模板中不存在";
|
list.Add(erro);
|
}
|
}
|
}
|
//当前Excel数据中点检部位页签中,设备保养部位编号(唯一)在设备保养部位表中是否存在
|
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 TEqpmai_Item where code=@code";
|
dynamicParams.Add("@code", 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() + "在数据表中不存在";
|
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> TwelveData(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> ThirteenData(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> FourteenData(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> FifteenData(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> 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(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++)
|
{
|
if (excelTable.Rows[i][1].ToString().Trim() != null && excelTable.Rows[i][1].ToString().Trim() != "")
|
{
|
sql = @"select code from TDefect where code=@code";
|
|
dynamicParams.Add("@code", excelTable.Rows[i][1].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{缺陷定义编号(唯一)}";
|
erro.ErrorCont = "缺陷定义表:{缺陷定义编号(唯一)}字段" + excelTable.Rows[i][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.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 TwoSubmit(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 = @"select * from TGroup where group_code=@group_code";
|
dynamicParams.Add("@group_code", excelTable[0].Rows[k][2].ToString().Trim());
|
dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
continue;
|
}
|
else
|
{
|
sql = @"insert into TGroup(group_code,group_name,description,lm_user,lm_date,torg_code)
|
values(@group_code,@group_name,@description,@Operator,@CreateDate,@torg_code)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
group_code = excelTable[0].Rows[k][2].ToString().Trim(),
|
group_name = excelTable[0].Rows[k][3].ToString().Trim(),
|
description = excelTable[0].Rows[k][4].ToString().Trim(),
|
CreateDate = DateTime.Now.ToString(),
|
Operator = User,
|
torg_code = excelTable[0].Rows[k][1].ToString().Trim()
|
}
|
});
|
}
|
}
|
//导入用户
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
if (excelTable[1].Rows[i][3].ToString().Trim() == "在职")
|
{
|
Enable = "Y";
|
}
|
else
|
{
|
Enable = "N";
|
}
|
//工资类型
|
switch (excelTable[1].Rows[i][8].ToString().Trim())
|
{
|
case "计件制":
|
WageType = "2";
|
break;
|
case "计时制":
|
WageType = "1";
|
break;
|
default:
|
break;
|
}
|
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;
|
}
|
sql = @"insert into TUser(usercode,username,password,enable,mobile,lm_date,email,lm_user,stu_torgcode,wagetype,usergroup_code)
|
values(@UserCode,@UserName,@password,@Enable,@Mobile,@CreateDate,@Email,@Operator,@StuOrg,@wagetype,@usergroup_code)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
UserCode = excelTable[1].Rows[i][1].ToString().Trim(),
|
UserName = excelTable[1].Rows[i][2].ToString().Trim(),
|
password = excelTable[1].Rows[i][4].ToString().Trim(),
|
Enable = Enable,
|
Mobile = excelTable[1].Rows[i][5].ToString().Trim(),
|
CreateDate = DateTime.Now.ToString(),
|
Email = excelTable[1].Rows[i][6].ToString().Trim(),
|
Operator = User,
|
StuOrg = excelTable[1].Rows[i][7].ToString().Trim(),
|
wagetype = WageType,
|
usergroup_code = excelTable[1].Rows[i][9].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(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 ThreeSubmit(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 FourSubmit(string FileCode, string User, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
DataTable dt;
|
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 Type = "";
|
switch (excelTable.Rows[i][3].ToString().Trim())
|
{
|
case "供应商":
|
Type = "226";
|
break;
|
case "客户":
|
Type = "211";
|
break;
|
case "客户/供应商":
|
Type = "228";
|
break;
|
default:
|
break;
|
}
|
|
sql = @"insert into TCustomer(code,name,type,conttacts,conttphone,addr,lm_user,lm_date)
|
values(@code,@name,@type,@conttacts,@conttphone,@addr,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable.Rows[i][1].ToString().Trim(),
|
name = excelTable.Rows[i][2].ToString().Trim(),
|
type = Type,
|
conttacts = excelTable.Rows[i][4].ToString().Trim(),
|
conttphone = excelTable.Rows[i][5].ToString().Trim(),
|
addr = excelTable.Rows[i][6].ToString().Trim(),
|
Operator = User,
|
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 SixOneSubmit(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();
|
List<DataTable> excelTable = new List<DataTable>();
|
excelTable = ImportExcel.ExcelToTableList(FileCode);
|
//导入仓库
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
sql = @"insert into T_Sec_Stck(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][1].ToString().Trim(),
|
name = excelTable[0].Rows[k][2].ToString().Trim(),
|
description = excelTable[0].Rows[k][3].ToString().Trim(),
|
CreateDate = DateTime.Now.ToString(),
|
Operator = User
|
}
|
});
|
}
|
//导入库位
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
sql = @"insert into T_Sec_Loca(code,name,description,stock_code,lm_user,lm_date)
|
values(@code,@name,@description,@stock_code,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable[1].Rows[i][1].ToString().Trim(),
|
name = excelTable[1].Rows[i][2].ToString().Trim(),
|
stock_code = excelTable[1].Rows[i][3].ToString().Trim(),
|
description = excelTable[1].Rows[i][4].ToString().Trim(),
|
Operator = User,
|
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 SevenSubmit(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 EightSubmit(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 NineSubmit(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 k = 0; k < excelTable.Rows.Count; k++)
|
{
|
sql = @"select * from TEqpInfo where code=@eqp_code";
|
dynamicParams.Add("@eqp_code", excelTable.Rows[k][1].ToString().Trim());
|
DataTable dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
continue;
|
}
|
sql = @"insert into TEqpInfo(code,name,wksp_code,input_date,operation_ration,enable,opc_uom,lm_user,lm_date)
|
values(@devicecode,@devicename,@workshop,@importdate,@ratio,@status,@uom,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
devicecode = excelTable.Rows[k][1].ToString().Trim(),
|
devicename = excelTable.Rows[k][2].ToString().Trim(),
|
workshop = excelTable.Rows[k][4].ToString().Trim(),
|
importdate = excelTable.Rows[k][3].ToString().Trim(),
|
ratio = excelTable.Rows[k][6].ToString().Trim(),
|
status = excelTable.Rows[k][5].ToString().Trim(),
|
uom = "台",
|
lm_user = User,
|
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(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 Type = "", IsScan = "";
|
switch (excelTable.Rows[i][3].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.Rows[i][4].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.Rows[i][1].ToString().Trim(),
|
name = excelTable.Rows[i][2].ToString().Trim(),
|
descr = excelTable.Rows[i][5].ToString().Trim(),
|
isscan = IsScan,
|
cycle = Type,
|
Operator = User,
|
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 TwentyTwoSubmit(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();
|
List<DataTable> excelTable = new List<DataTable>();
|
excelTable = ImportExcel.ExcelToTableList(FileCode);
|
//导入设备点检标准主表
|
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][1].ToString().Trim(),
|
name = excelTable[0].Rows[k][2].ToString().Trim(),
|
iscontr = excelTable[0].Rows[k][3].ToString().Trim(),
|
description = excelTable[0].Rows[k][4].ToString().Trim(),
|
CreateDate = DateTime.Now.ToString(),
|
Operator = User
|
}
|
});
|
}
|
//导入设备点检标准部位子表
|
for (int i = 0; i < excelTable[1].Rows.Count; i++)
|
{
|
string Type = "";
|
switch (excelTable[1].Rows[i][7].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][3].ToString().Trim(),
|
name = excelTable[1].Rows[i][4].ToString().Trim(),
|
eqpchk_main_code = excelTable[1].Rows[i][2].ToString().Trim(),
|
description = excelTable[1].Rows[i][5].ToString().Trim(),
|
isscan = excelTable[1].Rows[i][6].ToString().Trim(),
|
cycle = Type,
|
Operator = User,
|
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(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 Type = "", IsScan = "";
|
//switch (excelTable.Rows[i][3].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.Rows[i][3].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.Rows[i][1].ToString().Trim(),
|
name = excelTable.Rows[i][2].ToString().Trim(),
|
descr = excelTable.Rows[i][4].ToString().Trim(),
|
isscan = IsScan,
|
cycle = "",
|
Operator = User,
|
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 TwentyThreeSubmit(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();
|
List<DataTable> excelTable = new List<DataTable>();
|
excelTable = ImportExcel.ExcelToTableList(FileCode);
|
//导入设备保养标准主表
|
for (int k = 0; k < excelTable[0].Rows.Count; k++)
|
{
|
string Type = "";
|
switch (excelTable[0].Rows[k][3].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][1].ToString().Trim(),
|
name = excelTable[0].Rows[k][2].ToString().Trim(),
|
main_cycle = Type,
|
description = excelTable[0].Rows[k][4].ToString().Trim(),
|
CreateDate = DateTime.Now.ToString(),
|
Operator = User
|
}
|
});
|
}
|
//导入设备保养标准部位子表
|
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][3].ToString().Trim(),
|
name = excelTable[1].Rows[i][4].ToString().Trim(),
|
eapmai_code = excelTable[1].Rows[i][2].ToString().Trim(),
|
description = excelTable[1].Rows[i][5].ToString().Trim(),
|
isscan = excelTable[1].Rows[i][6].ToString().Trim(),
|
Operator = User,
|
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(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 ThirteenSubmit(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 FourteenSubmit(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 FifteenSubmit(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 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(string FileCode, string User, out string StuCode)
|
{
|
string message = "";
|
StuCode = "";
|
string sql = "";
|
DataTable dt;
|
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++)
|
{
|
sql = @"insert into TDefect(code,name,descr,lm_user,lm_date)
|
values(@code,@name,@descr,@Operator,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = excelTable.Rows[i][1].ToString().Trim(),
|
name = excelTable.Rows[i][2].ToString().Trim(),
|
descr = excelTable.Rows[i][3].ToString().Trim(),
|
Operator = User,
|
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 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
|
}
|
}
|
