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()) + "'"); }