private void btnCheck_Click(object sender, EventArgs e)
{
if (_BSPRODUCT_excel == null)
{
WGMessage.ShowWarning("请选择[产品基本信息]文件!");
return;
}
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
string sql = "SELECT GUID,CODE,NAME,CTYPE FROM BSPRODTYPE";
_BSPRODTYPE_DB = FillDatatablde(sql, Main.CONN_Public);
sql = "SELECT GUID,NAME FROM BSUNIT WHERE ST = 1";
_BSUNIT_DB = FillDatatablde(sql, Main.CONN_Public);
sql = "SELECT GUID,CODE,NAME FROM BSPRODUCT";
_BSPRODUCT_DB = FillDatatablde(sql, Main.CONN_Public);
//错误
List <int[]> col_error = new List <int[]>();
//重复数据
DataTable dt_repet = _BSPRODUCT_excel.Clone();
DataTable dt_error = _BSPRODUCT_excel.Clone();
DataTable dt_repet_excel = _BSPRODUCT_excel.Clone();
for (int i = 0; i < _BSPRODUCT_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _BSPRODUCT_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["产品编号"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["产品编号"].ToString()) ||
_BSPRODUCT_excel.Select("产品编号='" + WGHelper.ReturnString(dr_excel["产品编号"].ToString()) + "'").Length > 1)
{
//空、重复
isRepet_excel = true;
}
if (_BSPRODUCT_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["产品编号"].ToString()) + "'").Length > 0)
{
// 存在
isRepet = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["产品名称"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["产品名称"].ToString()) ||
_BSPRODUCT_excel.Select("产品名称='" + WGHelper.ReturnString(dr_excel["产品名称"].ToString()) + "'").Length > 1)
{
//空、重复
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
else
if (_BSPRODUCT_DB.Select("NAME='" + WGHelper.ReturnString(dr_excel["产品名称"].ToString()) + "'").Length > 0)
{
// 存在
isRepet = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["产品类别编号"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
DataRow[] drs_type = _BSPRODTYPE_DB.Select("CODE = '" + dr_excel["产品类别编号"] + "'");
if (drs_type.Length == 0)
{
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["单位名称"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 4 });
isError = true;
}
DataRow[] drs_unit = _BSUNIT_DB.Select("NAME = '" + dr_excel["单位名称"] + "'");
if (drs_unit.Length == 0)
{
col_error.Add(new int[] { dt_error.Rows.Count, 4 });
isError = true;
}
int NEEDREADY = 0;
int NEEDTRACE = 0;
int ISSCHEDULING = 0;
int ISDBBARCODE = 0;
decimal SAFETYSTOCK = 0;
if (string.IsNullOrWhiteSpace(dr_excel["是否需要备料"].ToString()) || !int.TryParse(dr_excel["是否需要备料"].ToString(), out NEEDREADY))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["安全库存量"].ToString()) || !decimal.TryParse(dr_excel["安全库存量"].ToString(), out SAFETYSTOCK))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["是否批次管理"].ToString()) || !int.TryParse(dr_excel["是否批次管理"].ToString(), out ISDBBARCODE))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["是否追溯"].ToString()) || !int.TryParse(dr_excel["是否追溯"].ToString(), out NEEDTRACE))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["是否排产"].ToString()) || !int.TryParse(dr_excel["是否排产"].ToString(), out ISSCHEDULING))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
decimal bzknum = 0;
decimal dbznum = 0;
decimal xbznum = 0;
if (string.IsNullOrWhiteSpace(dr_excel["标准框数量"].ToString()) || !decimal.TryParse(dr_excel["标准框数量"].ToString(), out bzknum))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["小包装数量"].ToString()) || !decimal.TryParse(dr_excel["小包装数量"].ToString(), out dbznum))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["大包装数量"].ToString()) || !decimal.TryParse(dr_excel["大包装数量"].ToString(), out xbznum))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string NewGUID = Guid.NewGuid().ToString();
string temp = string.Format(@"INSERT INTO BSPRODUCT
(GUID,CODE,NAME,AGUID,ANAME,ACODE,CTYPE,SPEC,BGUID,BNAME,LPQTY,SPQTY,ANUM,NEEDREADY,NEEDTRACE,ISSCHEDULING,ISDBBARCODE,SAFETYSTOCK,NOTE,ST,ND,CD)
VALUES
('{0}',{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},GETDATE(),GETDATE())",
NewGUID, Main.SetDBValue(dr_excel["产品编号"].ToString()), Main.SetDBValue(dr_excel["产品名称"].ToString()), Main.SetDBValue(drs_type[0]["GUID"].ToString()),
Main.SetDBValue(dr_excel["产品类别编号"].ToString()), Main.SetDBValue(dr_excel["产品类别名称"].ToString()), Convert.ToInt32(drs_type[0]["CTYPE"].ToString()),
Main.SetDBValue(dr_excel["产品描述"].ToString()), Main.SetDBValue(drs_unit[0]["GUID"].ToString()), Main.SetDBValue(dr_excel["单位名称"].ToString()),
dbznum, xbznum, bzknum, NEEDREADY, NEEDTRACE, ISSCHEDULING, ISDBBARCODE, SAFETYSTOCK, "NULL", 0);
temp += string.Format(@"INSERT INTO BSPRODUCTVER
(GUID,PGUID,PCODE,PNAME,VER,ST,STATE,VDATE)
VALUES
(NEWID(),'{0}',{1},{2},'{3}',0, 1 ,GETDATE())", NewGUID, Main.SetDBValue(dr_excel["产品编号"].ToString()),
Main.SetDBValue(dr_excel["产品名称"].ToString()), "A.0", 0, 0);
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError.DataSource = dt_error;
dgRepet.DataSource = dt_repet;
dgRepet_Excel.DataSource = dt_repet_excel;
if (dt_error.Rows.Count > 0 || dt_repet.Rows.Count > 0 || dt_repet_excel.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
isCheck = true;
}
private void btnCheck_Click(object sender, EventArgs e)
{
sb.Clear();
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
rbSql.Text = "";
sqlLs = new List <string>();
if (_DMLIBRARY_excel == null)
{
WGMessage.ShowWarning("请选择主文件!");
return;
}
if (_DMLINK_excel == null)
{
WGMessage.ShowWarning("请选择子文件!");
return;
}
//if (DOC == null)
//{
// WGMessage.ShowWarning("请选择含PDF的文件夹!");
// return;
//}
//if (DOC.Length == 0)
//{
// WGMessage.ShowWarning("请选择含PDF的文件夹!");
// return;
//}
//if (txtFileAim.Text == "")
//{
// WGMessage.ShowWarning("请选择目的地文件夹!");
// return;
//}
string sql = @"SELECT * FROM BSWORKSHOP";
_WORKSHOP_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT * FROM BSWORKCENTERS";
_ECINFO_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT * FROM BSWORKCENTER";
_WORKCENTER_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT GUID,EMPCODE FROM HREMPLOYEE";
_HREMPLOYEE_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT GUID,CODE FROM BSDEPT";
_DEPT_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT * FROM DMTYPE";
_DMTYPE_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT * FROM DMLIBRARY
LEFT JOIN DMVERSION ON DMVERSION.PGUID = DMLIBRARY.GUID";
_DMLIBRARY_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT BSPRODUCT.GUID,[BSPRODUCT].[CODE],[dbo].[BSPRODUCTVER].[VER] FROM [dbo].[BSPRODUCT]
LEFT JOIN BSPRODUCTVER ON BSPRODUCTVER.[PGUID] = [BSPRODUCT].[GUID]";
_BSPRODUCT_DB = FillDatatablde(sql, Main.CONN_Public);
//错误
List <int[]> col_error1 = new List <int[]>();
List <int[]> col_error2 = new List <int[]>();
DataTable dt_error1 = _DMLIBRARY_excel.Clone();
//重复数据
DataTable dt_repet1 = _DMLIBRARY_excel.Clone();
DataTable dt_repet1_excel = _DMLIBRARY_excel.Clone();
DataTable dt_error2 = _DMLINK_excel.Clone();
//重复数据
DataTable dt_repet2 = _DMLINK_excel.Clone();
//需要保持的数据
DataTable dt = _DMLIBRARY_excel.Clone();
dt.Columns.Add();
Dictionary <string, string> doclist = new Dictionary <string, string>();
dtout = new DataTable();
dtout.Columns.Add("文件名");
dtout.Columns.Add("文件KEY");
dtout.Columns.Add("受控类型");
dtout.Columns.Add("有效天数");
dtout.Columns.Add("文档编号");
#region 主子
for (int i = 0; i < _DMLIBRARY_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _DMLIBRARY_excel.Rows[i];
doclist.Add(Guid.NewGuid().ToString(), dr_excel["文档名"].ToString());
if (string.IsNullOrWhiteSpace(dr_excel["文档编号"].ToString()) ||
_DMLIBRARY_excel.Select("文档编号='" + WGHelper.ReturnString(dr_excel["文档编号"].ToString()) + "'").Length > 1)
{
//空、重复
isRepet_excel = true;
}
if (_DMLIBRARY_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["文档编号"].ToString()) + "'").Length > 0)
{
// 存在
isRepet = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["文档名"].ToString()))
{
//空
col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
isError = true;
}
DataRow[] drs_DMTYPE_pos = _DMTYPE_DB.Select("CODE='"
+ WGHelper.ReturnString(dr_excel["文档类别编号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["文档类别编号"].ToString()) ||
drs_DMTYPE_pos.Length == 0)
{
//空、不存在
col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
isError = true;
}
//DataRow[] drs_DMCATALOG_pos = _DMCATALOG_DB.Select("NAME='"
// + WGHelper.ReturnString(dr_excel["文档目录名称"].ToString()) + "'");
//if (string.IsNullOrWhiteSpace(dr_excel["文档目录名称"].ToString())
// || drs_DMCATALOG_pos.Length == 0)
//{
// //空、不存在
// col_error1.Add(new int[] { dt_error1.Rows.Count, 3 });
// isError = true;
//}
DataRow[] drs_HREMPLOYEE_pos = _HREMPLOYEE_DB.Select("EMPCODE ='"
+ WGHelper.ReturnString(dr_excel["上传人工号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["上传人工号"].ToString()) ||
drs_HREMPLOYEE_pos.Length == 0)
{
//空、不存在
col_error1.Add(new int[] { dt_error1.Rows.Count, 4 });
isError = true;
}
DataRow[] drs_DEPT_pos = _DEPT_DB.Select("CODE ='"
+ WGHelper.ReturnString(dr_excel["上传人所在部门编号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["上传人所在部门编号"].ToString() + "'") ||
drs_DEPT_pos.Length == 0)
{
//空、不存在
col_error1.Add(new int[] { dt_error1.Rows.Count, 5 });
isError = true;
}
DateTime ADATE = new DateTime();
if (string.IsNullOrWhiteSpace(dr_excel["上传时间"].ToString()) ||
!DateTime.TryParse(dr_excel["上传时间"].ToString(), out ADATE))
{
//空、类型不符
col_error1.Add(new int[] { dt_error1.Rows.Count, 6 });
isError = true;
}
if (dr_excel["受控类型"].ToString() != "1" && dr_excel["受控类型"].ToString() != "2")
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 7 });
isError = true;
}
DateTime VDATE = new DateTime();
int day = 0;
if (dr_excel["受控类型"].ToString() == "2")
{
if (string.IsNullOrWhiteSpace(dr_excel["有效期"].ToString()) ||
!DateTime.TryParse(dr_excel["有效期"].ToString(), out VDATE))
{
//空、类型不符
col_error1.Add(new int[] { dt_error1.Rows.Count, 9 });
isError = true;
}
if (!int.TryParse(dr_excel["有效天数"].ToString(), out day))
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 8 });
isError = true;
}
}
else if (dr_excel["受控类型"].ToString() == "1")
{
dr_excel["有效天数"] = 0;
dr_excel["有效期"] = "";
}
if (string.IsNullOrWhiteSpace(dr_excel["正式版本号"].ToString()))
{
//空
col_error1.Add(new int[] { dt_error1.Rows.Count, 10 });
isError = true;
}
if (dr_excel["文档来源"].ToString() != "1" && dr_excel["文档来源"].ToString() != "3")
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 11 });
isError = true;
}
DateTime ndTime = DateTime.Now;
DateTime cdTime = ndTime;
DateTime.TryParse(dr_excel["创建时间"].ToString(), out ndTime);
DateTime.TryParse(dr_excel["修改时间"].ToString(), out cdTime);
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error1.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet1.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet1_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
//bool ishavedoc = false;
//FileInfo dr_info = new FileInfo (DOC[0]);
//for (int j = 0; j < DOC.Length; j++)
//{
// FileInfo info = new FileInfo(DOC[j]);
// if (info.Name.Contains(".pdf") && info.Name == dr_excel["文档名"].ToString())
// {
// ishavedoc = true;
// dr_info = info;
// break;
// }
//}
//if (ishavedoc == false)
//{
// WGMessage.ShowAsterisk("选择文件夹中不包含"+dr_excel["文档名"]+"!");
// return;
//}
string DMLIBRARY_GUID = Guid.NewGuid().ToString();
string DMVERSION_GUID = Guid.NewGuid().ToString();
_DMLIBRARY_excel.Rows[i]["MGUID"] = DMLIBRARY_GUID;
_DMLIBRARY_excel.Rows[i]["LGUID"] = DMVERSION_GUID;
try
{
string vdate = "";
if (dr_excel["受控类型"].ToString() == "1")
{
vdate = "null";
}
else
{
vdate = "'" + dr_excel["有效期"] + "'";
}
string temp = @"INSERT INTO [DMLIBRARY]
([GUID],[FGUID],[CODE]
,[NAME],[KEYWORD],[AGUID]
,[DEPTRI],[ADATE],[VDATE],[VER],[ST]
,[NOTE],[CC],[ND],[CD],[CTYPE]
,[VDAYS],[FTYPE])
VALUES
(" + Main.SetDBValue(DMLIBRARY_GUID) + "," + Main.SetDBValue(drs_DMTYPE_pos[0]["GUID"]) + "," + Main.SetDBValue(dr_excel["文档编号"].ToString()) + @"
," + Main.SetDBValue(dr_excel["文档名"].ToString()) + "," + Main.SetDBValue(dr_excel["关键字"]) + "," + Main.SetDBValue(drs_HREMPLOYEE_pos[0]["GUID"]) + @"
," + Main.SetDBValue(Guid.NewGuid().ToString() /*drs_DEPT_pos[0]["GUID"]*/) + "," + Main.SetDBValue(dr_excel["上传时间"]) + "," + (vdate) + "," + Main.SetDBValue(dr_excel["正式版本号"]) + ",2" + @"
," + Main.SetDBValue(dr_excel["备注"].ToString()) + ",''," + Main.SetDBValue(ndTime) + "," + Main.SetDBValue(cdTime) + ",'" + dr_excel["受控类型"].ToString() + "'" + "" +
"," + day + ",'" + dr_excel["文档来源"].ToString() + "');";
string FKEY = Guid.NewGuid().ToString();
dr_excel["FKEY"] = FKEY;
temp += @" INSERT INTO DMVERSION (GUID,PGUID,VER,FKEY,AGUID,ADATE,ST) VALUES
('" + DMVERSION_GUID + "','" + DMLIBRARY_GUID + "'," + Main.SetDBValue(dr_excel["正式版本号"]) + ",'" + FKEY + "'," + Main.SetDBValue(drs_HREMPLOYEE_pos[0]["GUID"]) + "," + Main.SetDBValue(dr_excel["上传时间"]) + ",2);";
sb.Append(temp + Environment.NewLine);
sqlLs.Add(temp);
DataRow drnew = dtout.NewRow();
drnew["文件名"] = dr_excel["文档名"].ToString();
drnew["文件KEY"] = FKEY;
drnew["受控类型"] = dr_excel["受控类型"].ToString();
drnew["有效天数"] = day;
drnew["文档编号"] = dr_excel["文档编号"].ToString();
dtout.Rows.Add(drnew);
}
catch
{ }
}
#endregion
#region 孙孙
for (int i = 0; i < _DMLINK_excel.Rows.Count; i++)
{
isError = false;
isRepet = false;
DataRow dr_excel = _DMLINK_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["文档编号"].ToString()))
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
isError = true;
}
if (_DMLINK_excel.Select("文档编号='" + WGHelper.ReturnString(dr_excel["文档编号"].ToString()) + "' AND 正式版本号 = '" + WGHelper.ReturnString(dr_excel["正式版本号"].ToString()) + "' and 车间编号 = '" + WGHelper.ReturnString(dr_excel["车间编号"].ToString()) + "' AND 工作中心编号 = '" + WGHelper.ReturnString(dr_excel["工作中心编号"].ToString()) + "' AND 工位编号 = '" + WGHelper.ReturnString(dr_excel["工位编号"].ToString()) + "'").Length > 1)
{
// 存在
isRepet = true;
}
if (_DMLINK_excel.Select("文档编号='" + WGHelper.ReturnString(dr_excel["文档编号"].ToString()) + "' AND 正式版本号 = '" + WGHelper.ReturnString(dr_excel["正式版本号"].ToString()) + "' AND 产品编号 = '" + WGHelper.ReturnString(dr_excel["产品编号"].ToString()) + "' AND 产品版本 = '" + WGHelper.ReturnString(dr_excel["产品版本"].ToString()) + "' AND 工序码 = '" + WGHelper.ReturnString(dr_excel["工序码"].ToString()) + "'").Length > 1)
{
isRepet = true;
}
/* DataRow[] drss = _DMLINK_excel.Select("文档编号 = '"+dr_excel["文档编号"].ToString()+ "' AND 正式版本号 = '"+ dr_excel["正式版本号"].ToString() + "'");
*
* if (drss.Length > 1)
* {
* col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
* col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
* isError = true;
* }*/
if (string.IsNullOrWhiteSpace(dr_excel["正式版本号"].ToString()))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
isError = true;
}
int numa;
if (dr_excel["类型"].ToString() != "0" && dr_excel["类型"].ToString() != "1")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
isError = true;
}
DataRow[] drs_WORKCENTER_pos = new DataRow[0];
DataRow[] drs_WORKSHOP_pos = new DataRow[0];
if (dr_excel["类型"].ToString() == "0")
{
if (dr_excel["序号"].ToString() == "" && dr_excel["车间编号"].ToString() != "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
isError = true;
}
else if (dr_excel["序号"].ToString() == "" && dr_excel["车间编号"].ToString() == "")
{
}
else if (!int.TryParse(dr_excel["序号"].ToString(), out numa))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
isError = true;
}
drs_WORKSHOP_pos = _WORKSHOP_DB.Select("CODE='"
+
WGHelper.ReturnString(dr_excel["车间编号"].ToString()) +
"'");
if (dr_excel["车间编号"].ToString() == "" && dr_excel["工作中心编号"].ToString() != "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 4 });
isError = true;
}
else if (dr_excel["车间编号"].ToString() == "" && dr_excel["工作中心编号"].ToString() == "")
{
}
else if (drs_WORKSHOP_pos.Length == 0)
{
//空、不存在
col_error2.Add(new int[] { dt_error2.Rows.Count, 4 });
isError = true;
}
drs_WORKCENTER_pos = _WORKCENTER_DB.Select("CODE ='"
+
WGHelper.ReturnString(
dr_excel["工作中心编号"].ToString()) + "'");
if (dr_excel["工作中心编号"].ToString() == "" && dr_excel["工位编号"].ToString() != "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 5 });
isError = true;
}
else if (dr_excel["工作中心编号"].ToString() == "" && dr_excel["工位编号"].ToString() == "")
{
}
else if (drs_WORKCENTER_pos.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 5 });
isError = true;
}
if (dr_excel["工位编号"].ToString() != "" && drs_WORKCENTER_pos.Length == 0)
{
//空、不存在
col_error2.Add(new int[] { dt_error2.Rows.Count, 6 });
isError = true;
}
}
string[] ECINFO = dr_excel["工位编号"].ToString().Split(',');
string[] ECINFOGUID = new string[ECINFO.Length];
string[] ECINFONAME = new string[ECINFO.Length];
int index = 0;
if (dr_excel["工位编号"].ToString() == "")
{
}
else
{
foreach (var ei in ECINFO)
{
if (drs_WORKCENTER_pos.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 6 });
isError = true;
break;
}
DataRow[] drs_ECINFO_pos = _ECINFO_DB.Select("CODE ='"
+ WGHelper.ReturnString(ECINFO[index].ToString()) + "' AND PGUID = '" + drs_WORKCENTER_pos [0]["GUID"].ToString() + "'");
if (drs_ECINFO_pos.Length == 0)
{
//空、不存在
col_error2.Add(new int[] { dt_error2.Rows.Count, 6 });
isError = true;
break;
}
ECINFOGUID[index] = drs_ECINFO_pos[0]["GUID"].ToString();
ECINFONAME[index] = drs_ECINFO_pos[0]["NAME"].ToString();
index++;
}
}
DataRow[] drs_DMLINK = new DataRow[0];
if (dr_excel["类型"].ToString() == "1")
{
if (dr_excel["产品编号"].ToString() == "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 7 });
isError = true;
}
if (dr_excel["产品版本"].ToString() == "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 8 });
isError = true;
}
if (dr_excel["工序码"].ToString() == "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 9 });
isError = true;
}
drs_DMLINK = _BSPRODUCT_DB.Select("CODE = '" + WGHelper.ReturnString(dr_excel["产品编号"].ToString()) + "' AND VER = '" + dr_excel["产品版本"].ToString() + "' ");
if (drs_DMLINK.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 7 });
col_error2.Add(new int[] { dt_error2.Rows.Count, 8 });
isError = true;
}
}
DataRow[] drs = _DMLIBRARY_excel.Select("文档编号 = '" + dr_excel["文档编号"].ToString() + "' AND 正式版本号 = '" + dr_excel["正式版本号"].ToString() + "' ");
if (drs.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
isError = true;
}
if (isError || isRepet)
{
if (isError)
{
dt_error2.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet2.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string DMLINK_GUID = Guid.NewGuid().ToString();
string AGUID = Guid.NewGuid().ToString();
string BGUID = Guid.NewGuid().ToString();
try
{
AGUID = drs_WORKSHOP_pos[0]["GUID"].ToString();
}
catch
{
}
try
{
BGUID = drs_WORKCENTER_pos[0]["GUID"].ToString();
}
catch
{
}
if (dr_excel["序号"].ToString() != "")
{
string temp = "";
if (dr_excel["类型"].ToString() == "0")
{
temp = @" INSERT INTO DMLINK (GUID,PGUID,SNO,AGUID,BGUID,CTYPE) VALUES
('" + DMLINK_GUID + "','" + drs[0]["LGUID"].ToString() + "','" + dr_excel["序号"].ToString() + "','" + AGUID +
"','" + BGUID + "','" + dr_excel["类型"] + "');";
}
else if (dr_excel["类型"].ToString() == "1")
{
temp = @" INSERT INTO DMLINK (GUID,PGUID,SNO,CTYPE,CGUID,PVER,CODE) VALUES
('" + DMLINK_GUID + "','" + drs[0]["LGUID"].ToString() + "','" + dr_excel["序号"].ToString() + "','" + dr_excel["类型"] + "','" + drs_DMLINK[0]["GUID"] + "','" +
dr_excel["产品版本"] + "','" + WGHelper.ReturnString(dr_excel["工序码"].ToString()) + "');";
}
for (int j = 0; j < ECINFO.Length; j++)
{
if (ECINFOGUID[j] == null)
{
continue;
}
temp += @" INSERT INTO DMLINKS (GUID,PGUID,SNO,FGUID) VALUES
('" + Guid.NewGuid().ToString() + "','" + DMLINK_GUID + "','" + (j + 1) + "','" + ECINFOGUID[j] + "')";
}
sb.Append(temp + Environment.NewLine);
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
}
#endregion
#region （BSFILEUPLOAD）
for (int i = 0; i < _DMLIBRARY_excel.Rows.Count; i++)
{
string temp = @"INSERT INTO BSFILEUPLOAD (GUID,DOMAIN,TBDM,PGUID,PKEY,FNAME,SAVENAME,EXTNAME,ST)
VALUES ('" + Guid.NewGuid().ToString() + "','DMLIBRARY','DMVERSION','" + _DMLIBRARY_excel.Rows[i]["LGUID"].ToString() + "','" + _DMLIBRARY_excel.Rows[i]["FKEY"].ToString() + "','" + _DMLIBRARY_excel.Rows[i]["文档名"] + "','" + _DMLIBRARY_excel.Rows[i]["FKEY"].ToString() + ".pdf','.pdf',1);";
sb.Append(temp + Environment.NewLine);
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
#endregion
dt_error1.Columns.Remove("MGUID");
dt_error1.Columns.Remove("LGUID");
dt_error1.Columns.Remove("FKEY");
dt_error1.Columns.Remove("FSIZE");
dt_repet1.Columns.Remove("MGUID");
dt_repet1.Columns.Remove("LGUID");
dt_repet1.Columns.Remove("FKEY");
dt_repet1.Columns.Remove("FSIZE");
//dt_error2.Columns.Remove("F7");
//dt_error2.Columns.Remove("F8");
//dt_error2.Columns.Remove("F9");
//dt_error2.Columns.Remove("F10");
//dt_repet2.Columns.Remove("F7");
//dt_repet2.Columns.Remove("F8");
//dt_repet2.Columns.Remove("F9");
//dt_repet2.Columns.Remove("F10");
dgError1.DataSource = dt_error1; dgError2.DataSource = dt_error2;
dgRepet1_excel.DataSource = dt_repet1_excel; dgRepet2.DataSource = dt_repet2;
dgRepet1.DataSource = dt_repet1;
if (dt_error1.Rows.Count > 0 || dt_error2.Rows.Count > 0 || dt_repet1_excel.Rows.Count > 0 || dt_repet1.Rows.Count > 0 || dt_repet2.Rows.Count > 0)
{
Main.SetErrorCell(dgError1, col_error1);
Main.SetErrorCell(dgError2, col_error2);
rbSql.Text = "";
isCheck = false;
return;
}
this.rbSql.Text = sb.ToString();
isCheck = true;
}
private void btnCheck_Click(object sender, EventArgs e)
{
if (_ECInfo_excel == null)
{
WGMessage.ShowWarning("请选择[设备信息]文件!");
return;
}
if (_ECInfoD_excel == null)
{
WGMessage.ShowWarning("请选择[物料校验项]文件!");
return;
}
if (_ECInfoE_excel == null)
{
WGMessage.ShowWarning("请选择[物料保养项]文件!");
return;
}
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
//加载对应厂部的部门-岗位
string sql = @"SELECT t1.[GUID] BSDEPTPOS_GUID,t2.GUID BSDEPT_GUID,t2.CODE BSDEPT_CODE,t2.NAME BSDEPT_NAME,t3.CODE BSPOSITION_CODE,t3.NAME BSPOSITION_NAME
FROM [BSDEPTPOS] t1
left join BSDEPT t2 on t1.PGUID=t2.GUID
left join BSPOSITION t3 on t1.FGUID=t3.GUID";
_hrDeptPos_DB = FillDatatablde(sql, Main.CONN_Public);
//加载物料基本信息
sql = @"select GUID,CODE,NAME from BSPRODUCT WHERE [CTYPE] IN (2,3,4,5,6)";
_ECInfo_DB = FillDatatablde(sql, Main.CONN_Public);
//加载物料类别
sql = @"select GUID,CODE,NAME,CTYPE from [BSPRODTYPE] WHERE CTYPE IN (2,3,4,5,6)";
_ECType_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"select GUID,CODE,NAME FROM BSPRODUCT WHERE CTYPE IN (2,3,4,5,6)";
_BSPRODUCT_DB = FillDatatablde(sql, Main.CONN_Public);
//错误
List <int[]> col_error = new List <int[]>();
//重复数据
DataTable dt_repet = _ECInfo_excel.Clone();
#region 物料信息 验证
DataTable dt_error = _ECInfo_excel.Clone();
DataTable dt_repet_excel = _ECInfo_excel.Clone();
DataTable dt_repet_excel1 = _ECInfoD_excel.Clone();
DataTable dt_repet_excel2 = _ECInfoE_excel.Clone();
//存放要保存的设备厂内编号对应的guid
Dictionary <string, Guid> newEC = new Dictionary <string, Guid>();
Dictionary <string, Guid> newDJName = new Dictionary <string, Guid>();
for (int i = 0; i < _ECInfo_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _ECInfo_excel.Rows[i];
DataRow[] drs_ectype = _ECType_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["MRO类别"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["MRO类别"].ToString()) || drs_ectype.Length == 0)
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["MRO编号"].ToString()))
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
DataRow[] drs_mrcode = _ECInfo_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["MRO编号"].ToString()) + "'");
if (drs_mrcode.Length > 0)
{
isRepet = true;
}
DataRow[] drs_repet_excel = _ECInfo_excel.Select("MRO编号 = '" + WGHelper.ReturnString(dr_excel["MRO编号"].ToString()) + "'");
if (drs_repet_excel.Length > 1)
{
isRepet_excel = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["MRO名称"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
if (!string.IsNullOrWhiteSpace(dr_excel["安全库存管理"].ToString()) &&
dr_excel["安全库存管理"].ToString().ToLower() == "true" && string.IsNullOrWhiteSpace(dr_excel["安全库存"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 2 });
isError = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
Guid n = Guid.NewGuid();
string temp = @"INSERT INTO [BSPRODUCT]
([GUID],[CODE],[NAME],[PGUID],[CTYPE]
,[ISMAINTMGR],[ISCHECKMGR],[ISSTORAGEMGR],[STORAGENUM],[ISSNMGR],[ISSEQUESTMGR],[ND])
VALUES
(" + Main.SetDBValue(n) + "," + Main.SetDBValue(dr_excel["MRO编号"]) + "," + Main.SetDBValue(dr_excel["MRO名称"]) + "," + Main.SetDBValue(drs_ectype[0]["GUID"]) + @"
," + Main.SetDBValue(drs_ectype[0]["CTYPE"]) + "," + Main.SetDBValue(dr_excel["保养管理"]) + "," + Main.SetDBValue(dr_excel["校验管理"]) + "," + Main.SetDBValue(dr_excel["安全库存管理"]) + "," + Main.SetDBValue(dr_excel["安全库存"]) + "," +
"" + Main.SetDBValue(dr_excel["标识管理"]) + " ," + Main.SetDBValue(dr_excel["封存管理"]) + "," + Main.SetDBValue(DateTime.Now) + ")";
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
newEC.Add(dr_excel["MRO编号"].ToString(), n);
}
dgError.DataSource = dt_error;
dgRepet.DataSource = dt_repet;
dgRepet_excel.DataSource = dt_repet_excel;
if (dt_error.Rows.Count > 0 || dt_repet.Rows.Count > 0 || dt_repet_excel.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
#endregion
#region 物料校验项 验证
dt_error = _ECInfoD_excel.Clone();
for (int i = 0; i < _ECInfoD_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _ECInfoD_excel.Rows[i];
int SNO = 0;
if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) ||
!int.TryParse(dr_excel["序号"].ToString(), out SNO))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["MRO编号"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
else if (!newEC.ContainsKey(dr_excel["MRO编号"].ToString()))
{
// 不存在
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["校验项"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 2 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["类型"].ToString()) ||
!CTYPEs.ContainsKey(dr_excel["类型"].ToString()))
{
//空、不包含
col_error.Add(new int[] { dt_error.Rows.Count, 5 });
isError = true;
}
DataRow[] drs_repet = _ECInfoD_excel.Select("校验项 = '" + dr_excel["校验项"].ToString() + "' AND MRO编号 = '" + dr_excel["MRO编号"] + "' ");
if (drs_repet.Length > 1)
{
isRepet_excel = true;
}
DataRow[] drs_dept_pos_a = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["处理部门"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["处理岗位"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["处理部门"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["处理岗位"].ToString()) ||
drs_dept_pos_a.Length == 0)
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 8 });
col_error.Add(new int[] { dt_error.Rows.Count, 9 });
isError = true;
}
DataRow[] drs_dept_pos_b = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["响应部门"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["响应岗位"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["响应部门"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["响应岗位"].ToString()) ||
drs_dept_pos_b.Length == 0)
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 10 });
col_error.Add(new int[] { dt_error.Rows.Count, 11 });
isError = true;
}
if (dr_excel["类型"].ToString() == "数值")
{
if (string.IsNullOrWhiteSpace(dr_excel["是否区间"].ToString()) ||
!ISINTERVALs.ContainsKey(dr_excel["是否区间"].ToString()))
{
//空、不包含
col_error.Add(new int[] { dt_error.Rows.Count, 12 });
isError = true;
}
}
int DAYS = 0;
//if (string.IsNullOrWhiteSpace(dr_excel["周期(天)"].ToString())
// && !int.TryParse(dr_excel["周期(天)"].ToString(), out DAYS))
//{
// //空
// col_error.Add(new int[] { dt_error.Rows.Count, 17 });
// isError = true;
//}
string Stand_Str = dr_excel["符号1"].ToString() + dr_excel["最小值"].ToString() + " " + dr_excel["符号2"].ToString() + dr_excel["最大值"].ToString();
if (dr_excel["类型"].ToString() == "文本" && string.IsNullOrWhiteSpace(dr_excel["标准"].ToString()))
{
col_error.Add(new int[] { dt_error.Rows.Count, 7 });
col_error.Add(new int[] { dt_error.Rows.Count, 13 });
col_error.Add(new int[] { dt_error.Rows.Count, 14 });
col_error.Add(new int[] { dt_error.Rows.Count, 15 });
col_error.Add(new int[] { dt_error.Rows.Count, 16 });
isError = true;
}
if (dr_excel["类型"].ToString() == "数值")
{
dr_excel["标准"] = Stand_Str;
}
if ((dr_excel["是否区间"].ToString() == "是" && dr_excel["符号1"].ToString() == "=") || ((dr_excel["是否区间"].ToString() == "否" && dr_excel["符号1"].ToString() != "=")))
{
col_error.Add(new int[] { dt_error.Rows.Count, 12 });
col_error.Add(new int[] { dt_error.Rows.Count, 15 });
isError = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel1.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = @"INSERT INTO [dbo].[MRCHECKCT]
([GUID],[PGUID],[SNO],[NAME],[METHOD]
,[REQUEST],[CTYPE],[SVALUE],[ISINTERVAL],[MINVALUE]
,[MAXVALUE],[SG1],[SG2],[CYCLE],[AGUID]
,[BGUID])
VALUES
(" + Main.SetDBValue(Guid.NewGuid()) + "," + Main.SetDBValue(newEC[dr_excel["MRO编号"].ToString()]) + "," + Main.SetDBValue(dr_excel["序号"]) + "," + Main.SetDBValue(dr_excel["校验项"]) + "," + Main.SetDBValue(dr_excel["方法"]) + @"
," + Main.SetDBValue(dr_excel["要求"]) + "," + Main.SetDBValue(CTYPEs[dr_excel["类型"].ToString()]) + "," + Main.SetDBValue(dr_excel["标准"]) + "," + (GetISINTERVAL(dr_excel["是否区间"].ToString())) + "," + Main.SetDBValue(dr_excel["最小值"]) + @"
," + Main.SetDBValue(dr_excel["最大值"]) + "," + Main.SetDBValue(dr_excel["符号1"]) + "," + Main.SetDBValue(dr_excel["符号2"]) + "," + Main.SetDBValue(dr_excel["周期(天)"]) + "," + Main.SetDBValue(drs_dept_pos_a[0]["BSDEPTPOS_GUID"]) + @"
," + Main.SetDBValue(drs_dept_pos_b[0]["BSDEPTPOS_GUID"]) + ")";
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError.DataSource = dt_error;
dgRepet_excel1.DataSource = dt_repet_excel1;
if (dt_error.Rows.Count > 0 || dt_repet_excel1.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
#endregion
#region 物料保养项 验证
dt_error = _ECInfoE_excel.Clone();
for (int i = 0; i < _ECInfoE_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _ECInfoE_excel.Rows[i];
int SNO = 0;
if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) ||
!int.TryParse(dr_excel["序号"].ToString(), out SNO))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["MRO编号"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
else if (!newEC.ContainsKey(dr_excel["MRO编号"].ToString()))
{
// 不存在
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["保养项"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 2 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["类型"].ToString()) ||
!CTYPEs.ContainsKey(dr_excel["类型"].ToString()))
{
//空、不包含
col_error.Add(new int[] { dt_error.Rows.Count, 5 });
isError = true;
}
DataRow[] drs_repet = _ECInfoE_excel.Select("保养项 = '" + dr_excel["保养项"].ToString() + "' AND MRO编号 = '" + dr_excel["MRO编号"] + "' ");
if (drs_repet.Length > 1)
{
isRepet_excel = true;
}
DataRow[] drs_dept_pos_a = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["处理部门"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["处理岗位"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["处理部门"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["处理岗位"].ToString()) ||
drs_dept_pos_a.Length == 0)
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 8 });
col_error.Add(new int[] { dt_error.Rows.Count, 9 });
isError = true;
}
DataRow[] drs_dept_pos_b = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["响应部门"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["响应岗位"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["响应部门"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["响应岗位"].ToString()) ||
drs_dept_pos_b.Length == 0)
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 10 });
col_error.Add(new int[] { dt_error.Rows.Count, 11 });
isError = true;
}
int DAYS = 0;
if (string.IsNullOrWhiteSpace(dr_excel["周期(天)"].ToString()) &&
!int.TryParse(dr_excel["周期(天)"].ToString(), out DAYS))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 17 });
isError = true;
}
string Stand_Str = dr_excel["符号1"].ToString() + dr_excel["最小值"].ToString() + " " + dr_excel["符号2"].ToString() + dr_excel["最大值"].ToString();
if (dr_excel["类型"].ToString() == "文本" && string.IsNullOrWhiteSpace(dr_excel["标准"].ToString()))
{
col_error.Add(new int[] { dt_error.Rows.Count, 7 });
col_error.Add(new int[] { dt_error.Rows.Count, 13 });
col_error.Add(new int[] { dt_error.Rows.Count, 14 });
col_error.Add(new int[] { dt_error.Rows.Count, 15 });
col_error.Add(new int[] { dt_error.Rows.Count, 16 });
isError = true;
}
if (dr_excel["类型"].ToString() == "数值")
{
dr_excel["标准"] = Stand_Str;
}
if ((dr_excel["是否区间"].ToString() == "是" && dr_excel["符号1"].ToString() == "=") || ((dr_excel["是否区间"].ToString() == "否" && dr_excel["符号1"].ToString() != "=")))
{
col_error.Add(new int[] { dt_error.Rows.Count, 12 });
col_error.Add(new int[] { dt_error.Rows.Count, 15 });
isError = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel2.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = @"INSERT INTO [dbo].[MRMAINT]
([GUID],[PGUID],[SNO],[NAME],[METHOD]
,[REQUEST],[CTYPE],[SVALUE],[ISINTERVAL],[MINVALUE]
,[MAXVALUE],[SG1],[SG2],[CYCLE],[AGUID]
,[BGUID])
VALUES
(" + Main.SetDBValue(Guid.NewGuid()) + "," + Main.SetDBValue(newEC[dr_excel["MRO编号"].ToString()]) + "," + Main.SetDBValue(dr_excel["序号"]) + "," + Main.SetDBValue(dr_excel["保养项"]) + "," + Main.SetDBValue(dr_excel["方法"]) + @"
," + Main.SetDBValue(dr_excel["要求"]) + "," + Main.SetDBValue(CTYPEs[dr_excel["类型"].ToString()]) + "," + Main.SetDBValue(dr_excel["标准"]) + "," + (GetISINTERVAL(dr_excel["是否区间"].ToString())) + "," + Main.SetDBValue(dr_excel["最小值"]) + @"
," + Main.SetDBValue(dr_excel["最大值"]) + "," + Main.SetDBValue(dr_excel["符号1"]) + "," + Main.SetDBValue(dr_excel["符号2"]) + "," + Main.SetDBValue(dr_excel["周期(天)"]) + "," + Main.SetDBValue(drs_dept_pos_a[0]["BSDEPTPOS_GUID"]) + @"
," + Main.SetDBValue(drs_dept_pos_b[0]["BSDEPTPOS_GUID"]) + ")";
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError.DataSource = dt_error;
dgRepet_excel2.DataSource = dt_repet_excel2;
if (dt_error.Rows.Count > 0 || dt_repet_excel2.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
#endregion
isCheck = true;
}
private void btnCheck_Click(object sender, EventArgs e)
{
if (_MRBOM_excel == null)
{
WGMessage.ShowWarning("请选择[模具BOM]文件!");
return;
}
if (_MRBOMSUB_excel == null)
{
WGMessage.ShowWarning("请选择[模具BOM明细]文件!");
return;
}
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
//加载对应厂部的部门-岗位
string sql = @"SELECT GUID,[EMPCODE],[EMPNAME] FROM [dbo].[HREMPLOYEE]";
_HREMPLOYEE_DB = FillDatatablde(sql, Main.CONN_Public);
//加载物料基本信息
sql = @"select GUID,CODE,NAME from BSPRODUCT WHERE [CTYPE] IN (2)";
_MROPRODUCT_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @" SELECT [MRBOM].[CODE] 单号,[BSPRODUCT].[CODE] 物料 FROM [MRBOM]
LEFT JOIN [dbo].[BSPRODUCT] ON [BSPRODUCT].[GUID] = [dbo].[MRBOM].[BGUID]" ;
_MRBOM_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @" SELECT [MRBOM].CODE 单号,[BSPRODUCT].[CODE] 物料 FROM MRBOMSUB
LEFT JOIN [dbo].[MRBOM] ON [MRBOM].[GUID] = [MRBOMSUB].[PGUID]
LEFT JOIN [dbo].[BSPRODUCT] ON [BSPRODUCT].[GUID] = [dbo].MRBOMSUB.WGUID" ;
_MRBOMSUB_DB = FillDatatablde(sql, Main.CONN_Public);
#region 模具BOM 验证
DataTable dt_error = _MRBOM_excel.Clone();
DataTable dt_repet_excel = _MRBOM_excel.Clone();
//错误
List <int[]> col_error = new List <int[]>();
//重复数据
DataTable dt_repet = _MRBOM_excel.Clone();
Dictionary <string, Guid> newMRO = new Dictionary <string, Guid>();
for (int i = 0; i < _MRBOM_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _MRBOM_excel.Rows[i];
//DataRow[] drs_ectype = _ECType_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["MRO类别"].ToString()) + "'");
//if (string.IsNullOrWhiteSpace(dr_excel["MRO类别"].ToString()) || drs_ectype.Length == 0)
//{
// //空、不存在
// col_error.Add(new int[] { dt_error.Rows.Count, 3 });
// isError = true;
//}
if (string.IsNullOrWhiteSpace(dr_excel["单号"].ToString()))
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
DataRow[] drs_HREMPLOYEE1 = _HREMPLOYEE_DB.Select("EMPCODE='" + WGHelper.ReturnString(dr_excel["录入人工号"].ToString()) + "'");
if (drs_HREMPLOYEE1.Length == 0)
{
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
DateTime dtTime1 = new DateTime();
if (!DateTime.TryParse(dr_excel["录入日期"].ToString(), out dtTime1))
{
col_error.Add(new int[] { dt_error.Rows.Count, 2 });
isError = true;
}
DataRow[] drs_mroproduct = _MROPRODUCT_DB.Select("CODE = '" + WGHelper.ReturnString(dr_excel["模具编号"].ToString()) + "'");
if (drs_mroproduct.Length == 0)
{
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (!STs.ContainsKey(dr_excel["单据状态"].ToString()))
{
col_error.Add(new int[] { dt_error.Rows.Count, 4 });
isError = true;
}
DataRow[] drs_HREMPLOYEE2 = _HREMPLOYEE_DB.Select("EMPCODE='" + WGHelper.ReturnString(dr_excel["审核人工号"].ToString()) + "'");
if (drs_HREMPLOYEE2.Length == 0)
{
col_error.Add(new int[] { dt_error.Rows.Count, 5 });
isError = true;
}
DateTime dtTime2 = new DateTime();
if (!DateTime.TryParse(dr_excel["审核日期"].ToString(), out dtTime2))
{
col_error.Add(new int[] { dt_error.Rows.Count, 6 });
isError = true;
}
DataRow[] drs_repet = _MRBOM_DB.Select("单号 = '" + WGHelper.ReturnString(dr_excel["单号"].ToString()) + "'");
if (drs_repet.Length > 0)
{
isRepet = true;
}
drs_repet = _MRBOM_DB.Select("物料 = '" + WGHelper.ReturnString(dr_excel["模具编号"].ToString()) + "'");
if (drs_repet.Length > 0)
{
isRepet = true;
}
DataRow[] drs_repet_excel = _MRBOM_excel.Select("单号 = '" + WGHelper.ReturnString(dr_excel["单号"].ToString()) + "'");
if (drs_repet_excel.Length > 1)
{
isRepet_excel = true;
}
drs_repet_excel = _MRBOM_excel.Select("模具编号 = '" + WGHelper.ReturnString(dr_excel["模具编号"].ToString()) + "'");
if (drs_repet_excel.Length > 1)
{
isRepet_excel = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
Guid n = Guid.NewGuid();
string temp = string.Format(@"INSERT INTO [dbo].[MRBOM]
( [GUID] ,
[CODE] ,
[AGUID] ,
[INTIME] ,
[BGUID] ,
[ST] ,
[AUDITGUID] ,
[ADATE] ,
[NOTE] ,
[ND] ,
[CD]
)
VALUES ( '{0}' , -- GUID - uniqueidentifier
'{1}' , -- CODE - nvarchar(50)
'{2}' , -- AGUID - uniqueidentifier
'{3}' , -- INTIME - datetime
'{4}' , -- BGUID - uniqueidentifier
'{5}' , -- ST - bit
'{6}' , -- AUDITGUID - uniqueidentifier
'{7}' , -- ADATE - datetime
'{8}' , -- NOTE - nvarchar(200)
GETDATE() , -- ND - datetime
GETDATE() -- CD - datetime
)"
, n
, WGHelper.ReturnString(dr_excel["单号"].ToString())
, drs_HREMPLOYEE1[0]["GUID"].ToString()
, dtTime1
, drs_mroproduct[0]["GUID"].ToString()
, STs[dr_excel["单据状态"].ToString()]
, drs_HREMPLOYEE2[0]["GUID"].ToString()
, dtTime2
, WGHelper.ReturnString(dr_excel["备注"].ToString())
);
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
newMRO.Add(dr_excel["单号"].ToString(), n);
}
dgError.DataSource = dt_error;
dgRepet.DataSource = dt_repet;
dgRepet_excel.DataSource = dt_repet_excel;
if (dt_error.Rows.Count > 0 || dt_repet.Rows.Count > 0 || dt_repet_excel.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
#endregion
#region 模具BOM明细 验证
DataTable dt_error2 = _MRBOMSUB_excel.Clone();
DataTable dt_repet2_excel = _MRBOMSUB_excel.Clone();
//错误
List <int[]> col_error2 = new List <int[]>();
//重复数据
DataTable dt_repet2 = _MRBOMSUB_excel.Clone();
for (int i = 0; i < _MRBOMSUB_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _MRBOMSUB_excel.Rows[i];
if (!newMRO.ContainsKey(dr_excel["单号"].ToString()))
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
isError = true;
}
int SNO = 0;
if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) ||
!int.TryParse(dr_excel["序号"].ToString(), out SNO))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
isError = true;
}
DataRow[] drs_mroproduct = _MROPRODUCT_DB.Select("CODE = '" + WGHelper.ReturnString(dr_excel["模具编号"].ToString()) + "'");
if (drs_mroproduct.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
isError = true;
}
int num = 0;
if (!int.TryParse(dr_excel["数量"].ToString(), out num))
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
isError = true;
}
DataRow[] drs_repet = _MRBOMSUB_DB.Select("单号 = '" + WGHelper.ReturnString(dr_excel["单号"].ToString()) + "'");
if (drs_repet.Length > 0)
{
isRepet = true;
}
DataRow[] drs_repet_excel = _MRBOMSUB_excel.Select("单号 = '" + WGHelper.ReturnString(dr_excel["单号"].ToString()) + "' AND 模具编号 = '" + WGHelper.ReturnString(dr_excel["模具编号"].ToString()) + "'");
if (drs_repet_excel.Length > 1)
{
isRepet_excel = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error2.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet2.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet2_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = string.Format(@"
INSERT INTO [dbo].[MRBOMSUB]
( [GUID] ,
[SNO] ,
[PGUID] ,
[WGUID] ,
[QTY]
)
VALUES ( NEWID() , -- GUID - uniqueidentifier
'{0}' , -- SNO - int
'{1}' , -- PGUID - uniqueidentifier
'{2}' , -- WGUID - uniqueidentifier
{3} -- QTY - int
)"
, dr_excel["序号"].ToString()
, newMRO[dr_excel["单号"].ToString()]
, drs_mroproduct[0]["GUID"].ToString()
, num);
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError2.DataSource = dt_error2;
dgRepet2.DataSource = dt_repet2;
dgRepet_excel2.DataSource = dt_repet2_excel;
if (dt_error2.Rows.Count > 0 || dt_repet2.Rows.Count > 0 || dt_repet2_excel.Rows.Count > 0)
{
Main.SetErrorCell(dgError2, col_error2);
rbSql.Text = "";
return;
}
#endregion
isCheck = true;
StringBuilder last = new StringBuilder();
foreach (string sql1 in sqlLs)
{
last.Append(sql1 + Environment.NewLine);
}
rbSql.Text = last.ToString();
}
private void btnCheck_Click(object sender, EventArgs e)
{
if (_ECInfoC_excel == null)
{
_ECInfoC_excel = new DataTable();
}
if (_ECInfoCS_excel == null)
{
_ECInfoCS_excel = new DataTable();
}
if (_ECInfoD_excel == null)
{
_ECInfoD_excel = new DataTable();
}
if (_ECInfoE_excel == null)
{
_ECInfoE_excel = new DataTable();
}
if (_ECInfoA_excel == null)
{
_ECInfoA_excel = new DataTable();
}
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
//加载对应厂部的部门-岗位
string sql = @"SELECT t1.[GUID] BSDEPTPOS_GUID,t2.GUID BSDEPT_GUID,t2.CODE BSDEPT_CODE,t2.NAME BSDEPT_NAME,t3.CODE BSPOSITION_CODE,t3.NAME BSPOSITION_NAME
FROM [BSDEPTPOS] t1
left join BSDEPT t2 on t1.PGUID=t2.GUID
left join BSPOSITION t3 on t1.FGUID=t3.GUID";
_hrDeptPos_DB = FillDatatablde(sql, Main.CONN_Public);
//加载设备信息
sql = @"select GUID,CODE,NAME from ECINFO";
_ECInfo_DB = FillDatatablde(sql, Main.CONN_Public);
//加载设备类别
sql = @"select GUID,CODE,NAME from ECTYPE";
_ECType_DB = FillDatatablde(sql, Main.CONN_Public);
//加载供应商
sql = @"select GUID,CODE,NAME from BSSUPPLIER";
_BSSupplier_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"select GUID,CODE,NAME FROM BSDEPT";
_BSDEPT_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"select GUID,CODE,NAME FROM BSPRODUCT WHERE CTYPE = 1";
_BSPRODUCT_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT GUID,CODE,NAME FROM ECCLASS";
_ECCLASS_DB = FillDatatablde(sql, Main.CONN_Public);
Dictionary <string, Guid> newDJName = new Dictionary <string, Guid>();
#region AM点检 验证
List <int[]> col_error2 = new List <int[]>();
//重复数据
DataTable dt_repet2 = _ECInfoC_excel.Clone();
DataTable dt_error2 = _ECInfoC_excel.Clone();
dt_error2 = _ECInfoC_excel.Clone();
for (int i = 0; i < _ECInfoC_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
string mguid = "";
DataRow dr_excel = _ECInfoC_excel.Rows[i];
int SNO = 0;
if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) ||
!int.TryParse(dr_excel["序号"].ToString(), out SNO))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["设备编号"].ToString()))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
isError = true;
}
else
{
DataRow[] drs = _ECInfo_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["设备编号"].ToString()) + "'");
if (drs.Length > 0)
{
mguid = drs[0]["GUID"].ToString();
}
else
{
// 不存在
col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
isError = true;
}
}
if (string.IsNullOrWhiteSpace(dr_excel["点检项"].ToString()))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["类型"].ToString()) ||
!CTYPEs.ContainsKey(dr_excel["类型"].ToString()))
{
//空、不包含
col_error2.Add(new int[] { dt_error2.Rows.Count, 5 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["标准"].ToString()) && dr_excel["类型"].ToString() == "文本")
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 6 });
isError = true;
}
DataRow[] drs_dept_pos = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["响应部门编号"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["响应岗位编号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["响应部门编号"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["响应岗位编号"].ToString()) ||
drs_dept_pos.Length == 0)
{
//空、不存在
col_error2.Add(new int[] { dt_error2.Rows.Count, 7 });
col_error2.Add(new int[] { dt_error2.Rows.Count, 8 });
isError = true;
}
if (dr_excel["类型"].ToString() == "数值")
{
if (string.IsNullOrWhiteSpace(dr_excel["是否区间"].ToString()) ||
!ISINTERVALs.ContainsKey(dr_excel["是否区间"].ToString()))
{
//空、不包含
col_error2.Add(new int[] { dt_error2.Rows.Count, 9 });
isError = true;
}
}
if (string.IsNullOrWhiteSpace(dr_excel["适用范围"].ToString()) ||
!BTYPE.ContainsKey(dr_excel["适用范围"].ToString()))
{
//空、不包含
col_error2.Add(new int[] { dt_error2.Rows.Count, 14 });
isError = true;
}
if (!string.IsNullOrWhiteSpace(dr_excel["周期(天)"].ToString()) && !int.TryParse(dr_excel["周期(天)"].ToString(), out SNO))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 16 });
isError = true;
}
string Stand_Str = dr_excel["符号1"].ToString() + dr_excel["最小值"].ToString() + " " + dr_excel["符号2"].ToString() + dr_excel["最大值"].ToString();
if (dr_excel["类型"].ToString() == "数值")
{
dr_excel["标准"] = Stand_Str;
}
if ((dr_excel["是否区间"].ToString() == "是" && dr_excel["符号1"].ToString() == "=") || ((dr_excel["是否区间"].ToString() == "否" && dr_excel["符号1"].ToString() != "=")))
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 9 });
col_error2.Add(new int[] { dt_error2.Rows.Count, 12 });
isError = true;
}
if (dr_excel["适用范围"].ToString() == "通用" && dr_excel["点检频率"].ToString() == "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 15 });
isError = true;
}
if (dr_excel["适用范围"].ToString() == "产品" && dr_excel["点检频率"].ToString() != "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 15 });
isError = true;
}
if (dr_excel["点检频率"].ToString() == "按天" && dr_excel["周期(天)"].ToString() == "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 16 });
isError = true;
}
if (dr_excel["点检频率"].ToString() == "换班" && dr_excel["周期(天)"].ToString() != "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 16 });
isError = true;
}
DataRow[] drs_repit = _ECInfoC_excel.Select("设备编号 = '" + WGHelper.ReturnString(dr_excel["设备编号"].ToString()) + "' AND 点检项 = '" + WGHelper.ReturnString(dr_excel["点检项"].ToString()) + "'");
if (drs_repit.Length > 1)
{
isRepet = true;
}
if (isError || isRepet)
{
if (isError)
{
dt_error2.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet2.Rows.Add(dr_excel.ItemArray);
}
continue;
}
Guid kidGUID = Guid.NewGuid();
string temp = @"INSERT INTO [dbo].[ECINFOC]
([GUID],[PGUID],[SNO],[NAME],[METHOD]
,[REQUEST],[CTYPE],[SVALUE],[ISINTERVAL],[MINVALUE]
,[MAXVALUE],[SG1],[SG2]
,[ATYPE],[BTYPE],[CYCLE],[AGUID])
VALUES
(" + Main.SetDBValue(kidGUID) + "," + Main.SetDBValue(mguid) + "," + Main.SetDBValue(dr_excel["序号"]) + "," + Main.SetDBValue(dr_excel["点检项"]) + "," + Main.SetDBValue(dr_excel["方法"]) + @"
," + Main.SetDBValue(dr_excel["要求"]) + "," + Main.SetDBValue(CTYPEs[dr_excel["类型"].ToString()]) + "," + Main.SetDBValue(dr_excel["标准"]) + "," + GetISINTERVAL(dr_excel["是否区间"].ToString()) + "," + Main.SetDBValue(dr_excel["最小值"]) + @"
," + Main.SetDBValue(dr_excel["最大值"]) + "," + Main.SetDBValue(dr_excel["符号1"]) + "," + Main.SetDBValue(dr_excel["符号2"]) +
"," + Main.SetDBValue(dr_excel["适用范围"]) + "," + Main.SetDBValue(dr_excel["点检频率"]) + "," + Main.SetDBValue(dr_excel["周期（天）"].ToString()) + "," + Main.SetDBValue(drs_dept_pos[0]["BSDEPTPOS_GUID"]) + ")";
try
{
newDJName.Add(dr_excel["点检项"].ToString() + dr_excel["设备编号"], kidGUID);
}
catch
{
MessageBox.Show(dr_excel["设备编号"].ToString() + dr_excel["点检项"].ToString() + "重复！");
rbSql.Text = "";
return;
}
sqlLs.Add(temp);
}
dgError2.DataSource = dt_error2;
dgRepet2.DataSource = dt_repet2;
if (dt_error2.Rows.Count > 0 || dt_repet2.Rows.Count > 0)
{
Main.SetErrorCell(dgError2, col_error2);
rbSql.Text = "";
return;
}
#endregion
#region AM点检产品
List <int[]> col_error3 = new List <int[]>();
DataTable dt_repet3 = _ECInfoCS_excel.Clone();
DataTable dt_error3 = _ECInfoCS_excel.Clone();
for (int i = 0; i < _ECInfoCS_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
DataRow dr_excel = _ECInfoCS_excel.Rows[i];
int SNO = 0;
if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) ||
!int.TryParse(dr_excel["序号"].ToString(), out SNO))
{
//空
col_error3.Add(new int[] { dt_error3.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["设备编号"].ToString()))
{
//空
col_error3.Add(new int[] { dt_error3.Rows.Count, 1 });
isError = true;
}
else
{
DataRow[] drs = _ECInfo_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["设备编号"].ToString()) + "'");
if (drs.Length > 0)
{
}
else
{
// 不存在
col_error3.Add(new int[] { dt_error3.Rows.Count, 1 });
isError = true;
}
}
if (string.IsNullOrWhiteSpace(dr_excel["点检项"].ToString()))
{
//空
col_error3.Add(new int[] { dt_error3.Rows.Count, 2 });
isError = true;
}
else if (!newDJName.ContainsKey(dr_excel["点检项"].ToString() + dr_excel["设备编号"]))
{
// 不存在
col_error3.Add(new int[] { dt_error3.Rows.Count, 2 });
isError = true;
}
DataRow[] drs_bsproduct = _BSPRODUCT_DB.Select("CODE = '" + dr_excel["产品编号"].ToString() + "'");
if (string.IsNullOrWhiteSpace(dr_excel["产品编号"].ToString()))
{
//空
col_error3.Add(new int[] { dt_error3.Rows.Count, 3 });
isError = true;
}
if (drs_bsproduct.Length == 0)
{
col_error3.Add(new int[] { dt_error3.Rows.Count, 3 });
isError = true;
}
if (isError || isRepet)
{
if (isError)
{
dt_error3.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = string.Format(@"INSERT INTO [dbo].[ECINFOCS]
( [GUID], [PGUID], [SNO], [FGUID] )
VALUES ( NEWID(), -- GUID - uniqueidentifier
{0}, -- PGUID - uniqueidentifier
{1}, -- SNO - int
{2} -- FGUID - uniqueidentifier
)", Main.SetDBValue(newDJName[dr_excel["点检项"].ToString() + dr_excel["设备编号"].ToString()]), dr_excel["序号"].ToString(), Main.SetDBValue(drs_bsproduct[0]["GUID"].ToString()));
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError3.DataSource = dt_error3;
dgRepet3.DataSource = dt_repet3;
if (dt_error3.Rows.Count > 0 || dt_repet3.Rows.Count > 0)
{
Main.SetErrorCell(dgError3, col_error3);
rbSql.Text = "";
return;
}
#endregion
#region PM巡检 验证
List <int[]> col_error4 = new List <int[]>();
DataTable dt_repet4 = _ECInfoD_excel.Clone();
DataTable dt_error4 = _ECInfoD_excel.Clone();
for (int i = 0; i < _ECInfoD_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
string mguid = "";
DataRow dr_excel = _ECInfoD_excel.Rows[i];
int SNO = 0;
if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) ||
!int.TryParse(dr_excel["序号"].ToString(), out SNO))
{
//空
col_error4.Add(new int[] { dt_error4.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["设备编号"].ToString()))
{
//空
col_error4.Add(new int[] { dt_error4.Rows.Count, 1 });
isError = true;
}
else
{
DataRow[] drs = _ECInfo_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["设备编号"].ToString()) + "'");
if (drs.Length > 0)
{
mguid = drs[0]["GUID"].ToString();
}
else
{
// 不存在
col_error4.Add(new int[] { dt_error4.Rows.Count, 1 });
isError = true;
}
}
if (string.IsNullOrWhiteSpace(dr_excel["巡检项"].ToString()))
{
//空
col_error4.Add(new int[] { dt_error4.Rows.Count, 2 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["类型"].ToString()) ||
!CTYPEs.ContainsKey(dr_excel["类型"].ToString()))
{
//空、不包含
col_error4.Add(new int[] { dt_error4.Rows.Count, 5 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["标准"].ToString()) && dr_excel["类型"].ToString() == "文本")
{
//空
col_error4.Add(new int[] { dt_error4.Rows.Count, 6 });
isError = true;
}
DataRow[] drs_dept_pos_a = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["处理部门编号"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["处理岗位编号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["处理部门编号"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["处理岗位编号"].ToString()) ||
drs_dept_pos_a.Length == 0)
{
//空、不存在
col_error4.Add(new int[] { dt_error4.Rows.Count, 7 });
col_error4.Add(new int[] { dt_error4.Rows.Count, 8 });
isError = true;
}
DataRow[] drs_dept_pos_b = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["响应部门编号"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["响应岗位编号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["响应部门编号"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["响应岗位编号"].ToString()) ||
drs_dept_pos_b.Length == 0)
{
//空、不存在
col_error4.Add(new int[] { dt_error4.Rows.Count, 9 });
col_error4.Add(new int[] { dt_error4.Rows.Count, 10 });
isError = true;
}
if (dr_excel["类型"].ToString() == "数值")
{
if (string.IsNullOrWhiteSpace(dr_excel["是否区间"].ToString()) ||
!ISINTERVALs.ContainsKey(dr_excel["是否区间"].ToString()))
{
//空、不包含
col_error4.Add(new int[] { dt_error4.Rows.Count, 12 });
isError = true;
}
}
int DAYS = 0;
if (string.IsNullOrWhiteSpace(dr_excel["周期(天)"].ToString()) &&
!int.TryParse(dr_excel["周期(天)"].ToString(), out DAYS))
{
//空
col_error4.Add(new int[] { dt_error4.Rows.Count, 17 });
isError = true;
}
string Stand_Str = dr_excel["符号1"].ToString() + dr_excel["最小值"].ToString() + " " + dr_excel["符号2"].ToString() + dr_excel["最大值"].ToString();
if (dr_excel["类型"].ToString() == "数值")
{
dr_excel["标准"] = Stand_Str;
}
if (Stand_Str.Contains(dr_excel["标准"].ToString()) || dr_excel["类型"].ToString() == "文本")
{
}
else
{
col_error4.Add(new int[] { dt_error4.Rows.Count, 7 });
col_error4.Add(new int[] { dt_error4.Rows.Count, 13 });
col_error4.Add(new int[] { dt_error4.Rows.Count, 14 });
col_error4.Add(new int[] { dt_error4.Rows.Count, 15 });
col_error4.Add(new int[] { dt_error4.Rows.Count, 16 });
isError = true;
}
if ((dr_excel["是否区间"].ToString() == "是" && dr_excel["符号1"].ToString() == "=") || ((dr_excel["是否区间"].ToString() == "否" && dr_excel["符号1"].ToString() != "=")))
{
col_error4.Add(new int[] { dt_error4.Rows.Count, 12 });
col_error4.Add(new int[] { dt_error4.Rows.Count, 15 });
isError = true;
}
DataRow[] drs_repit = _ECInfoD_excel.Select("设备编号 = '" + WGHelper.ReturnString(dr_excel["设备编号"].ToString()) + "' AND 巡检项 = '" + WGHelper.ReturnString(dr_excel["巡检项"].ToString()) + "'");
if (drs_repit.Length > 1)
{
isRepet = true;
}
if (isError || isRepet)
{
if (isError)
{
dt_error4.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet4.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = @"INSERT INTO [dbo].[ECINFOD]
([GUID],[PGUID],[SNO],[NAME],[METHOD]
,[REQUEST],[CTYPE],[SVALUE],[ISINTERVAL],[MINVALUE]
,[MAXVALUE],[SG1],[SG2],[DAYS],[AGUID]
,[BGUID])
VALUES
(" + Main.SetDBValue(Guid.NewGuid()) + "," + Main.SetDBValue(mguid) + "," + Main.SetDBValue(dr_excel["序号"]) + "," + Main.SetDBValue(dr_excel["巡检项"]) + "," + Main.SetDBValue(dr_excel["方法"]) + @"
," + Main.SetDBValue(dr_excel["要求"]) + "," + Main.SetDBValue(CTYPEs[dr_excel["类型"].ToString()]) + "," + Main.SetDBValue(dr_excel["标准"]) + "," + (GetISINTERVAL(dr_excel["是否区间"].ToString())) + "," + Main.SetDBValue(dr_excel["最小值"]) + @"
," + Main.SetDBValue(dr_excel["最大值"]) + "," + Main.SetDBValue(dr_excel["符号1"]) + "," + Main.SetDBValue(dr_excel["符号2"]) + "," + Main.SetDBValue(dr_excel["周期(天)"]) + "," + Main.SetDBValue(drs_dept_pos_a[0]["BSDEPTPOS_GUID"]) + @"
," + Main.SetDBValue(drs_dept_pos_b[0]["BSDEPTPOS_GUID"]) + ")";
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError4.DataSource = dt_error4;
dgRepet4.DataSource = dt_repet4;
if (dt_error4.Rows.Count > 0 || dt_repet4.Rows.Count > 0)
{
Main.SetErrorCell(dgError4, col_error4);
rbSql.Text = "";
return;
}
#endregion
#region PM保养 验证
List <int[]> col_error5 = new List <int[]>();
DataTable dt_repet5 = _ECInfoE_excel.Clone();
DataTable dt_error5 = _ECInfoE_excel.Clone();
for (int i = 0; i < _ECInfoE_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
string mguid = "";
DataRow dr_excel = _ECInfoE_excel.Rows[i];
int SNO = 0;
if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) ||
!int.TryParse(dr_excel["序号"].ToString(), out SNO))
{
//空
col_error5.Add(new int[] { dt_error5.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["设备编号"].ToString()))
{
//空
col_error5.Add(new int[] { dt_error5.Rows.Count, 1 });
isError = true;
}
else
{
DataRow[] drs = _ECInfo_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["设备编号"].ToString()) + "'");
if (drs.Length > 0)
{
mguid = drs[0]["GUID"].ToString();
}
else
{
// 不存在
col_error5.Add(new int[] { dt_error5.Rows.Count, 1 });
isError = true;
}
}
if (string.IsNullOrWhiteSpace(dr_excel["保养项"].ToString()))
{
//空
col_error5.Add(new int[] { dt_error5.Rows.Count, 2 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["类型"].ToString()) ||
!CTYPEs.ContainsKey(dr_excel["类型"].ToString()))
{
//空、不包含
col_error5.Add(new int[] { dt_error5.Rows.Count, 5 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["标准"].ToString()) && dr_excel["类型"].ToString() == "文本")
{
//空
col_error5.Add(new int[] { dt_error5.Rows.Count, 6 });
isError = true;
}
DataRow[] drs_dept_pos_a = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["处理部门编号"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["处理岗位编号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["处理部门编号"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["处理岗位编号"].ToString()) ||
drs_dept_pos_a.Length == 0)
{
//空、不存在
col_error5.Add(new int[] { dt_error5.Rows.Count, 8 });
col_error5.Add(new int[] { dt_error5.Rows.Count, 9 });
isError = true;
}
DataRow[] drs_dept_pos_b = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["响应部门编号"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["响应岗位编号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["响应部门编号"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["响应岗位编号"].ToString()) ||
drs_dept_pos_b.Length == 0)
{
//空、不存在
col_error5.Add(new int[] { dt_error5.Rows.Count, 10 });
col_error5.Add(new int[] { dt_error5.Rows.Count, 11 });
isError = true;
}
int DAYS = 0;
if (string.IsNullOrWhiteSpace(dr_excel["周期(天)"].ToString()) &&
!int.TryParse(dr_excel["周期(天)"].ToString(), out DAYS))
{
//空
col_error5.Add(new int[] { dt_error5.Rows.Count, 16 });
isError = true;
}
string Stand_Str = dr_excel["符号1"].ToString() + dr_excel["最小值"].ToString() + " " + dr_excel["符号2"].ToString() + dr_excel["最大值"].ToString();
if (dr_excel["类型"].ToString() == "数值")
{
dr_excel["标准"] = Stand_Str;
}
if ((dr_excel["是否区间"].ToString() == "是" && dr_excel["符号1"].ToString() == "=") || ((dr_excel["是否区间"].ToString() == "否" && dr_excel["符号1"].ToString() != "=")))
{
col_error5.Add(new int[] { dt_error5.Rows.Count, 12 });
col_error5.Add(new int[] { dt_error5.Rows.Count, 15 });
isError = true;
}
DataRow[] drs_repit = _ECInfoE_excel.Select("设备编号 = '" + WGHelper.ReturnString(dr_excel["设备编号"].ToString()) + "' AND 保养项 = '" + WGHelper.ReturnString(dr_excel["保养项"].ToString()) + "'");
if (drs_repit.Length > 1)
{
isRepet = true;
}
if (isError || isRepet)
{
if (isError)
{
dt_error5.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet5.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = @"INSERT INTO [dbo].[ECINFOE]
([GUID],[PGUID],[SNO],[NAME],[METHOD]
,[REQUEST],[CTYPE],[SVALUE],[ISINTERVAL],[MINVALUE]
,[MAXVALUE],[SG1],[SG2],[DAYS],[AGUID]
,[BGUID])
VALUES
(" + Main.SetDBValue(Guid.NewGuid()) + "," + Main.SetDBValue(mguid) + "," + Main.SetDBValue(dr_excel["序号"]) + "," + Main.SetDBValue(dr_excel["保养项"]) + "," + Main.SetDBValue(dr_excel["方法"]) + @"
," + Main.SetDBValue(dr_excel["要求"]) + "," + Main.SetDBValue(CTYPEs[dr_excel["类型"].ToString()]) + "," + Main.SetDBValue(dr_excel["标准"]) + "," + (GetISINTERVAL(dr_excel["是否区间"].ToString())) + "," + Main.SetDBValue(dr_excel["最小值"]) + @"
," + Main.SetDBValue(dr_excel["最大值"]) + "," + Main.SetDBValue(dr_excel["符号1"]) + "," + Main.SetDBValue(dr_excel["符号2"]) + "," + Main.SetDBValue(dr_excel["周期(天)"]) + "," + Main.SetDBValue(drs_dept_pos_a[0]["BSDEPTPOS_GUID"]) + @"
," + Main.SetDBValue(drs_dept_pos_b[0]["BSDEPTPOS_GUID"]) + ")";
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError5.DataSource = dt_error5;
dgRepet5.DataSource = dt_repet5;
if (dt_error5.Rows.Count > 0 || dt_repet5.Rows.Count > 0)
{
Main.SetErrorCell(dgError5, col_error5);
rbSql.Text = "";
return;
}
#endregion
isCheck = true;
StringBuilder last = new StringBuilder();
foreach (string sql1 in sqlLs)
{
last.Append(sql1 + Environment.NewLine);
}
rbSql.Text = last.ToString();
}
private void btnCheck_Click(object sender, EventArgs e)
{
string sql = @"SELECT GUID,[NAME] FROM [dbo].[HRLIBRARYTYPE]";
_HRLIBRARYTYPE_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @" SELECT GUID,[EMPCODE],[EMPNAME] FROM HREMPLOYEE";
_HREMPLOYEE_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT HRWORKLICENSE.GUID, HRWORKLICENSE.NAME 上岗证,[BSWORKSHOP].[CODE] 车间编号,[ECTYPE].[CODE] 设备类别编号,[ECINFO].[CODE] 设备编号 FROM HRWORKLICENSE
LEFT JOIN [dbo].[BSWORKSHOP] ON [BSWORKSHOP].[GUID] = [HRWORKLICENSE].[AGUID]
LEFT JOIN [dbo].[ECTYPE] ON [ECTYPE].[GUID] = HRWORKLICENSE.[BGUID]
LEFT JOIN [dbo].[ECINFO] ON [ECINFO].[GUID] = [HRWORKLICENSE].[CGUID]";
_HRWORKCENTER_DB = FillDatatablde(sql, Main.CONN_Public);
#region 题库
DataTable HRLIBRARY_DB_ADD = new DataTable();
HRLIBRARY_DB_ADD.Columns.Add("试题内容");
HRLIBRARY_DB_ADD.Columns.Add("GUID");
List <int[]> col_error1 = new List <int[]>();
DataTable dt_repet1 = _HRLIBRARY_excel.Clone();
DataTable dt_error1 = _HRLIBRARY_excel.Clone();
for (int i = 0; i < _HRLIBRARY_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
DateTime dtTime = new DateTime();
DataRow dr_excel = _HRLIBRARY_excel.Rows[i];
if (!DateTime.TryParse(dr_excel["编制日期"].ToString(), out dtTime))
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["试题内容"].ToString()))
{
//空
col_error1.Add(new int[] { dt_error1.Rows.Count, 1 });
isError = true;
}
DataRow[] drs_HRLIBRARY_name = _HRLIBRARY_excel.Select("试题内容 = '" + WGHelper.ReturnString(dr_excel["试题内容"].ToString()) + "'");
if (drs_HRLIBRARY_name.Length > 1)
{
isRepet = true;
}
DataRow[] drs_HRLIBRARY_HRLIBRARYTYPE = _HRLIBRARYTYPE_DB.Select("NAME = '" + WGHelper.ReturnString(dr_excel["考题类型"].ToString()) + "'");
if (drs_HRLIBRARY_HRLIBRARYTYPE.Length == 0)
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
isError = true;
}
if (dr_excel["试题类型"].ToString() != "选择题" && dr_excel["试题类型"].ToString() != "判断题")
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 3 });
isError = true;
}
//DataRow[] drs_HRLIBRARY_HREMPLOYEE = _HREMPLOYEE_DB.Select("EMPCODE = '"+WGHelper.ReturnString(dr_excel["编制人员"].ToString())+"'");
//if (drs_HRLIBRARY_HREMPLOYEE.Length == 0)
//{
// col_error1.Add(new int[] { dt_error1.Rows.Count, 4 });
// isError = true;
//}
if (dr_excel["试题类型"].ToString() == "判断题")
{
if (!ISSURE.ContainsKey(dr_excel["判断题正确答案"].ToString()))
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 5 });
isError = true;
}
}
if (isError || isRepet)
{
if (isError)
{
dt_error1.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet1.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string MainGUID = Guid.NewGuid().ToString();
DataRow dr_HRLIBRARY_DB_ADD_newor = HRLIBRARY_DB_ADD.NewRow();
dr_HRLIBRARY_DB_ADD_newor["GUID"] = MainGUID;
dr_HRLIBRARY_DB_ADD_newor["试题内容"] = dr_excel["试题内容"].ToString();
string temp = string.Format(@"INSERT INTO [dbo].[HRLIBRARY]
( [GUID] ,
[FGUID] ,
[EDATE] ,
[NAME] ,
[CTYPE] ,
[EUSER] ,
[ANSWER] ,
[ST] ,
[CC] ,
[ND] ,
[CD]
)
VALUES ( '{0}' , -- GUID - uniqueidentifier
'{1}' , -- FGUID - uniqueidentifier
'{2}' , -- EDATE - datetime
'{3}' , -- NAME - nvarchar(200)
'{4}' , -- CTYPE - nvarchar(10)
'{5}' , -- EUSER - nvarchar(20)
{6} , -- ANSWER - bit
1 , -- ST - bit
NULL , -- CC - nvarchar(20)
GETDATE() , -- ND - datetime
GETDATE() -- CD - datetime
)", MainGUID, drs_HRLIBRARY_HRLIBRARYTYPE[0]["GUID"], dtTime, dr_excel["试题内容"], dr_excel["试题类型"], dr_excel["编制人员"], GetISSURE(dr_excel["判断题正确答案"].ToString()));
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
HRLIBRARY_DB_ADD.Rows.Add(dr_HRLIBRARY_DB_ADD_newor);
}
dgError1.DataSource = dt_error1;
dgRepet1.DataSource = dt_repet1;
if (dt_error1.Rows.Count > 0 || dt_repet1.Rows.Count > 0)
{
Main.SetErrorCell(dgError1, col_error1);
rbSql.Text = "";
return;
}
#endregion
#region 项
List <int[]> col_error2 = new List <int[]>();
DataTable dt_repet2 = _HRLIBRARYS_excel.Clone();
DataTable dt_error2 = _HRLIBRARYS_excel.Clone();
for (int i = 0; i < _HRLIBRARYS_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
DataRow dr_excel = _HRLIBRARYS_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["试题内容"].ToString()))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
isError = true;
}
DataRow[] drs_HRLIBRARY_name = HRLIBRARY_DB_ADD.Select("试题内容 = '" + WGHelper.ReturnString(dr_excel["试题内容"].ToString()) + "'");
if (drs_HRLIBRARY_name.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
isError = true;
}
int sno = 1;
if (!int.TryParse(dr_excel["序号"].ToString(), out sno))
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
isError = true;
}
if (!ISINTERVALs.ContainsKey(dr_excel["是否正确答案"].ToString()))
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
isError = true;
}
DataRow[] _HRLIBRARYS_DB_RIGHTANWER_drs = _HRLIBRARYS_excel.Select("试题内容 = '" + WGHelper.ReturnString(dr_excel["试题内容"].ToString()) + "' AND 是否正确答案 = '是'");
if (_HRLIBRARYS_DB_RIGHTANWER_drs.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
isError = true;
}
if (isError || isRepet)
{
if (isError)
{
dt_error2.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = string.Format(@"
INSERT INTO [dbo].[HRLIBRARYS]
( [GUID] ,
[PGUID] ,
[SNO] ,
[NAME] ,
[ISRIGHT]
)
VALUES ( NEWID() , -- GUID - uniqueidentifier
'{0}' , -- PGUID - uniqueidentifier
'{1}' , -- SNO - int
'{2}' , -- NAME - nvarchar(100)
{3} -- ISRIGHT - bit
)", drs_HRLIBRARY_name[0]["GUID"], dr_excel["序号"], dr_excel["内容"], GetISINTERVAL(dr_excel["是否正确答案"].ToString()));
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError2.DataSource = dt_error2;
dgRepet2.DataSource = dt_repet2;
if (dt_error2.Rows.Count > 0 || dt_repet2.Rows.Count > 0)
{
Main.SetErrorCell(dgError2, col_error2);
rbSql.Text = "";
return;
}
#endregion
#region 关联上岗证
List <int[]> col_error3 = new List <int[]>();
DataTable dt_repet3 = _HRLIBRARYWS_excel.Clone();
DataTable dt_error3 = _HRLIBRARYWS_excel.Clone();
DataTable dt_HRLIBRARYW_DB = new DataTable();
dt_HRLIBRARYW_DB.Columns.Add("GUID");
dt_HRLIBRARYW_DB.Columns.Add("FGUID");
dt_HRLIBRARYW_DB.Columns.Add("试题内容");
for (int i = 0; i < _HRLIBRARYWS_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
DataRow dr_excel = _HRLIBRARYWS_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["试题内容"].ToString()))
{
//空
col_error3.Add(new int[] { dt_error3.Rows.Count, 0 });
isError = true;
}
DataRow[] drs_HRLIBRARY_name = HRLIBRARY_DB_ADD.Select("试题内容 = '" + WGHelper.ReturnString(dr_excel["试题内容"].ToString()) + "'");
if (drs_HRLIBRARY_name.Length == 0)
{
col_error3.Add(new int[] { dt_error3.Rows.Count, 0 });
isError = true;
}
int sno = 1;
if (!int.TryParse(dr_excel["序号"].ToString(), out sno))
{
col_error3.Add(new int[] { dt_error3.Rows.Count, 1 });
isError = true;
}
int star = 1;
if (!int.TryParse(dr_excel["星级"].ToString(), out star))
{
col_error3.Add(new int[] { dt_error3.Rows.Count, 1 });
isError = true;
}
string where = "";
if (dr_excel["设备编号"].ToString() == "")
{
}
else
{
where = "AND 设备编号 = '" + WGHelper.ReturnString(dr_excel["设备编号"].ToString()) + "'";
}
DataRow[] drs__HRWORKCENTER_DB = _HRWORKCENTER_DB.Select("上岗证 = '" + WGHelper.ReturnString(dr_excel["上岗证名称"].ToString()) + "' AND 车间编号 = '" + WGHelper.ReturnString(dr_excel["车间编号"].ToString()) + "' AND 设备类别编号 = '" + WGHelper.ReturnString(dr_excel["设备类别编号"].ToString()) + "' " + where);
if (drs__HRWORKCENTER_DB.Length == 0)
{
col_error3.Add(new int[] { dt_error3.Rows.Count, 2 });
col_error3.Add(new int[] { dt_error3.Rows.Count, 3 });
col_error3.Add(new int[] { dt_error3.Rows.Count, 4 });
col_error3.Add(new int[] { dt_error3.Rows.Count, 5 });
isError = true;
}
DataRow[] drs_repit = _HRLIBRARYWS_excel.Select("试题内容 = '" + WGHelper.ReturnString(dr_excel["试题内容"].ToString()) + "' AND 上岗证名称 = '" + WGHelper.ReturnString(dr_excel["上岗证名称"].ToString()) + "' AND 车间编号 = '" + WGHelper.ReturnString(dr_excel["车间编号"].ToString()) + "' AND 设备类别编号 = '" + WGHelper.ReturnString(dr_excel["设备类别编号"].ToString()) + "' AND 设备编号 = '" + WGHelper.ReturnString(dr_excel["设备编号"].ToString()) + "' AND 星级 = '" + dr_excel["星级"].ToString() + "'");
if (drs_repit.Length > 1)
{
isRepet = true;
}
if (isError || isRepet)
{
if (isError)
{
dt_error3.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet3.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string KidGUID = Guid.NewGuid().ToString();
DataRow DR_dt_HRLIBRARYW_DB = dt_HRLIBRARYW_DB.NewRow();
DR_dt_HRLIBRARYW_DB["GUID"] = KidGUID;
DR_dt_HRLIBRARYW_DB["FGUID"] = drs__HRWORKCENTER_DB[0]["GUID"];
DR_dt_HRLIBRARYW_DB["试题内容"] = dr_excel["试题内容"];
Boolean ISHAVE = false;
foreach (DataRow dr in dt_HRLIBRARYW_DB.Rows)
{
if (dr["FGUID"].ToString().ToUpper() == drs__HRWORKCENTER_DB[0]["GUID"].ToString().ToUpper() && dr["试题内容"].ToString() == dr_excel["试题内容"].ToString())
{
KidGUID = dr["GUID"].ToString();
ISHAVE = true;
}
}
if (!ISHAVE)
{
string temp = string.Format(@"
INSERT INTO [dbo].[HRLIBRARYW]
( [GUID], [PGUID], [SNO], [FGUID] )
VALUES ( '{0}', -- GUID - uniqueidentifier
'{1}', -- PGUID - uniqueidentifier
'{2}', -- SNO - int
'{3}' -- FGUID - uniqueidentifier
)", KidGUID, drs_HRLIBRARY_name[0]["GUID"], sno, drs__HRWORKCENTER_DB[0]["GUID"]);
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
dt_HRLIBRARYW_DB.Rows.Add(DR_dt_HRLIBRARYW_DB);
}
else
{
}
string temp1 = string.Format(@"
INSERT INTO [dbo].[HRLIBRARYWS]
( [GUID], [PGUID], [LVL] )
VALUES ( NEWID(), -- GUID - uniqueidentifier
'{0}', -- PGUID - uniqueidentifier
'{1}' -- LVL - int
)", KidGUID, dr_excel["星级"]);
rbSql.Text += temp1 + Environment.NewLine;
sqlLs.Add(temp1);
}
dgError3.DataSource = dt_error3;
dgRepet3.DataSource = dt_repet3;
if (dt_error3.Rows.Count > 0 || dt_repet3.Rows.Count > 0)
{
Main.SetErrorCell(dgError3, col_error3);
rbSql.Text = "";
return;
}
#endregion
isCheck = true;
}
private void btnCheck_Click(object sender, EventArgs e)
{
if (_CT_excel == null)
{
WGMessage.ShowWarning("请选择[CT修改]文件!");
return;
}
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
//错误
List <int[]> col_error = new List <int[]>();
//重复数据
DataTable dt_error = _CT_excel.Clone();
DataTable dt_repet_excel = _CT_excel.Clone();
decimal CT = 0;
decimal ACT = 0;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < _CT_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet_excel = false;
DataRow dr_excel = _CT_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["零件号"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["版本"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["工序"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 2 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["工作中心编号"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
DataRow[] drs = _CT_excel.Select(string.Format("零件号='{0}' AND 版本='{1}' AND 工序='{2}' AND 工作中心编号='{3}'",
WGHelper.ReturnString(dr_excel["零件号"].ToString()),
WGHelper.ReturnString(dr_excel["版本"].ToString()),
WGHelper.ReturnString(dr_excel["工序"].ToString()),
WGHelper.ReturnString(dr_excel["工作中心编号"].ToString())));
if (drs.Length > 1)
{
isRepet_excel = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["标准工时"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 4 });
isError = true;
}
else
{
if (!decimal.TryParse(dr_excel["标准工时"].ToString(), out CT))
{
col_error.Add(new int[] { dt_error.Rows.Count, 4 });
isError = true;
}
}
if (string.IsNullOrWhiteSpace(dr_excel["CT"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 5 });
isError = true;
}
else
{
if (!decimal.TryParse(dr_excel["CT"].ToString(), out ACT))
{
col_error.Add(new int[] { dt_error.Rows.Count, 5 });
isError = true;
}
}
if (isError || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = string.Format(@"UPDATE BSPRODSTDSS SET CT={4},ACT={5}
WHERE BSPRODSTDSS.GUID=
(SELECT BSPRODSTDSS.GUID FROM BSPRODSTDSS
JOIN BSPRODSTDS ON BSPRODSTDS.GUID=BSPRODSTDSS.PGUID
JOIN BSPRODSTD ON BSPRODSTD.GUID=BSPRODSTDS.PGUID
JOIN BSWORKCENTER ON BSWORKCENTER.GUID=BSPRODSTDSS.FGUID
JOIN BSPRODUCT ON BSPRODUCT.GUID=BSPRODSTD.PGUID
WHERE BSPRODUCT.CODE='{0}'
AND BSPRODSTD.VER='{1}'
AND BSPRODSTDS.CPCODE='{2}'
AND BSWORKCENTER.CODE='{3}')", WGHelper.ReturnString(dr_excel["零件号"].ToString()), WGHelper.ReturnString(dr_excel["版本"].ToString()),
WGHelper.ReturnString(dr_excel["工序"].ToString()), WGHelper.ReturnString(dr_excel["工作中心编号"].ToString()), CT, ACT);
sb.AppendLine(temp);
sb.AppendLine();
sqlLs.Add(temp);
}
rbSql.Text = sb.ToString();
dgError.DataSource = dt_error;
dgRepet.DataSource = dt_repet_excel;
if (dt_error.Rows.Count > 0 || dt_repet_excel.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
isCheck = true;
}
/// <summary>
/// 用于数据库赋值(包括单引号转义)
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static string SetDBValue(object value)
{
return((value == null || value.ToString() == "") ? "null" : "'" + WGHelper.ReturnString(value.ToString()) + "'");
}
