예제 #1
0
        private void btnSelect1_Click(object sender, EventArgs e)
        {
            //得到excel数据源
            if (opfDialog.ShowDialog() == DialogResult.OK)
            {
                Button btn = sender as Button;
                if (btn == null)
                {
                    return;
                }
                switch (btn.Name)
                {
                case "btnSelect1":
                    txtFile1.Text   = opfDialog.FileName;
                    _BSWCTYPE_excel = _excelManager.GetExcelTableByOleDB(opfDialog.FileName, "BSWCTYPE");
                    _BSWCTYPE_excel.Columns.Add("GUID");
                    MessageBox.Show("读取笔数:" + _BSWCTYPE_excel.Rows.Count + "");
                    break;
                }

                ClearSql();
                if (_BSWCTYPE_excel == null || _BSWCTYPE_excel.Rows.Count <= 0)
                {
                    WGMessage.ShowWarning(@"无法读取当前Excel!");
                    return;
                }
            }
        }
예제 #2
0
        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                if (isCheck)
                {
                    WGMessage.ShowAsterisk("已验证,不用重复验证!");
                    return;
                }

                if (_HREMPLOYEES_excel == null)
                {
                    WGMessage.ShowWarning("请选择职工上岗证文件!");
                    return;
                }
                bool   isError = false;
                string message = "";
                foreach (DataRow dr in _HREMPLOYEE_DB.Rows)
                {
                    if (!UpdateSkillPoint(dr["GUID"].ToString()))
                    {
                        isError  = true;
                        message += dr["员工编码"] + "技能分更新不成功" + Environment.NewLine;
                    }
                }
                if (isError)
                {
                    WGMessage.ShowAsterisk(message);
                }
                WGMessage.ShowAsterisk("技能分更新成功!");
            }
            catch (Exception ex)
            {
            }
        }
예제 #3
0
        private void btnSelect_Click(object sender, EventArgs e)
        {
            //得到excel数据源
            if (opfDialog0.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    txtFileMain.Text = opfDialog0.FileName;
                    _BSDEPT_excel    = _excelManager.GetExcelTableByOleDB(opfDialog0.FileName, "BSDEPT");
                    _BSDEPT_excel.Columns.Add("GUID");
                    foreach (DataRow dr in _BSDEPT_excel.Rows)
                    {
                        dr["GUID"] = Guid.NewGuid();
                    }
                    MessageBox.Show("读取笔数:" + _BSDEPT_excel.Rows.Count + "");
                }
                catch
                { }
                if (_BSDEPT_excel == null || _BSDEPT_excel.Rows.Count <= 0)
                {
                    WGMessage.ShowWarning(@"无法读取当前Excel!");
                    return;
                }

                isCheck = false;
            }
            else
            {
                return;
            }
        }
예제 #4
0
        private void btnSelect_Click(object sender, EventArgs e)
        {
            //得到excel数据源
            if (opfDialog0.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    txtFileMain.Text = opfDialog0.FileName;
                    _CT_excel        = _excelManager.GetExcelTableByOleDB(opfDialog0.FileName, "BSDEPT");
                    MessageBox.Show("读取笔数:" + _CT_excel.Rows.Count + "");
                }
                catch
                { }
                if (_CT_excel == null || _CT_excel.Rows.Count <= 0)
                {
                    WGMessage.ShowWarning(@"无法读取当前Excel!");
                    return;
                }

                isCheck = false;
            }
            else
            {
                return;
            }
        }
예제 #5
0
        private void btnSelect_Click(object sender, EventArgs e)
        {
            //得到excel数据源
            if (opfDialog0.ShowDialog() == DialogResult.OK)
            {
                txtFileMain.Text = opfDialog0.FileName;
                _DMLIBRARY_excel = _excelManager.GetExcelTableByOleDB(opfDialog0.FileName, "DMLIBRARY");

                _DMLIBRARY_excel.Columns.Add("MGUID");
                _DMLIBRARY_excel.Columns.Add("LGUID");
                _DMLIBRARY_excel.Columns.Add("FKEY");
                _DMLIBRARY_excel.Columns.Add("FSIZE");
                isCheck = false;

                MessageBox.Show("读取笔数:" + _DMLIBRARY_excel.Rows.Count + "");
                //ClearSql();
                if (_DMLIBRARY_excel == null || _DMLIBRARY_excel.Rows.Count <= 0)
                {
                    WGMessage.ShowWarning(@"无法读取当前Excel!");
                    return;
                }
            }
            else
            {
                return;
            }
        }
예제 #6
0
 private void btnImport_Click(object sender, EventArgs e)
 {
     if (!isCheck)
     {
         WGMessage.ShowAsterisk("还未验证,不能导入!");
         return;
     }
     foreach (DataRow item in _ECInfo_excel.Rows)
     {
         Create(item["文件名"].ToString(), item["文件KEY"].ToString());
         OpenWrite(item["FULLFILENAME"].ToString(), item["文件KEY"].ToString());
         string newcode = item["文档编号"].ToString();
         string dtype   = item["受控类型"].ToString().Trim();
         string day     = item["有效天数"].ToString().Trim() == "" ? "0" : item["有效天数"].ToString().Trim();
         if (dtype == "1")
         {
             CreateWatermarkFile(item["文件名"].ToString(), item["文件KEY"].ToString(), dtype, newcode, DateTime.Now);
         }
         else
         {
             CreateWatermarkFile(item["文件名"].ToString(), item["文件KEY"].ToString(), dtype, newcode, DateTime.Now.AddDays(int.Parse(day)));
         }
     }
     richTextBox1.Text = strSql;
     WriteLog(strSql, true);
     WGMessage.ShowAsterisk("导入成功!");
 }
예제 #7
0
        private void button1_Click(object sender, EventArgs e)
        {
            //得到excel数据源
            if (opfDialog0.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    txtFileSon.Text  = opfDialog0.FileName;
                    _BSPROCESS_excel = _excelManager.GetExcelTableByOleDB(opfDialog0.FileName, "BSPROCESS");
                    _BSPROCESS_excel.Columns.Add("GUID");
                    MessageBox.Show("读取笔数:" + _BSPROCESS_excel.Rows.Count + "");
                }
                catch
                { }
                if (_BSPROCESS_excel == null || _BSPROCESS_excel.Rows.Count <= 0)
                {
                    WGMessage.ShowWarning(@"无法读取当前Excel!");
                    return;
                }

                isCheck = false;
            }
            else
            {
                return;
            }
        }
예제 #8
0
 private void button1_Click(object sender, EventArgs e)
 {
     //得到excel数据源
     if (opfDialog0.ShowDialog() == DialogResult.OK)
     {
         try
         {
             txtFileSon.Text    = opfDialog0.FileName;
             _HREMPLOYEES_excel = _excelManager.GetExcelTableByOleDB(opfDialog0.FileName, "HREMPLOYEES");
             MessageBox.Show("读取笔数:" + _HREMPLOYEES_excel.Rows.Count + "");
         }
         catch
         {
         }
         //ClearSql();
         if (_HREMPLOYEES_excel == null || _HREMPLOYEES_excel.Rows.Count <= 0)
         {
             WGMessage.ShowWarning(@"无法读取当前Excel!");
             return;
         }
         isCheck = false;
     }
     else
     {
         return;
     }
 }
예제 #9
0
 private void btnImport_Click(object sender, EventArgs e)
 {
     if (RunSql(sqlLs, Main.CONN_Model))
     {
         WGMessage.ShowAsterisk("导入成功!");
         ClearSql();
     }
     else
     {
         WGMessage.ShowAsterisk("导入失败!");
     }
 }
예제 #10
0
 private void btnImport_Click(object sender, EventArgs e)
 {
     if (!isCheck)
     {
         WGMessage.ShowAsterisk("还未验证,不能导入!");
         return;
     }
     if (RunSql(sqlLs, Main.CONN_Public))
     {
         WGMessage.ShowAsterisk("导入成功!");
         ClearSql();
     }
     else
     {
         WGMessage.ShowAsterisk("导入失败!");
     }
 }
예제 #11
0
        private void btnImport_Click(object sender, EventArgs e)
        {
            if (!isCheck)
            {
                WGMessage.ShowAsterisk("还未验证,不能导入!");
                return;
            }
            if (isError)
            {
                WGMessage.ShowAsterisk("有误不得导入!");
                return;
            }
            if (isRepet)
            {
                WGMessage.ShowAsterisk("有重复不得导入!");
                return;
            }

            string result = "";

            if (RunSql(sqlLs, Main.CONN_Public))
            {
                result += "导入成功!";
                ClearSql();
            }
            else
            {
                result += "导入失败!";
            }

            //if (UploadDoc())
            //{
            //    result += "上传成功!";
            //}
            //else
            //{
            //    result += "上传失败!";
            //}

            WGMessage.ShowAsterisk(result);
            Dictionary <string, string> _dicDataFieldCaption = new Dictionary <string, string>();

            ExportToExcel("文档清单.xlsx", "文档清单", _dicDataFieldCaption, dtout);
            ClearSql();
        }
예제 #12
0
        public static void ExportToExcel(string fileName, string title, Dictionary <string, string> ColDic,
                                         DataTable dtSource, IList <Image> jpgImagesList = null, Dictionary <bool, string> boolKeyValue = null)
        {
            DataSet   ds = new DataSet();
            DataTable dt = dtSource.Copy();

            dt.TableName = title;
            DataTable dtTemp = FormatDataTable(dt, ColDic);

            ds.Tables.Add(dtTemp);
            SaveFileDialog dlg = new SaveFileDialog();

            dlg.DefaultExt = "xlsx";
            dlg.Title      = "导出Excel";
            dlg.FileName   = fileName;
            dlg.Filter     = "Excel|*.xlsx;*.xls";
            if (dlg.ShowDialog() == DialogResult.OK && !string.IsNullOrWhiteSpace(dlg.FileName) && !string.IsNullOrWhiteSpace(Path.GetExtension(dlg.FileName)))
            {
                string fileExt = Path.GetExtension(dlg.FileName).ToLower();
                if (!(fileExt == ".xls" || fileExt == ".xlsx"))
                {
                    MessageBox.Show("指定文件格式错误!导出格式Excel格式应为xlsx或xls!");
                    return;
                }
                MemoryStream stream = Export(ds, jpgImagesList, boolKeyValue, fileExt);
                try
                {
                    byte[]       bytes = stream.ToArray();
                    FileStream   fs    = new FileStream(dlg.FileName, FileMode.Create);
                    BinaryWriter bw    = new BinaryWriter(fs);
                    bw.Write(bytes);
                    bw.Close();
                    fs.Close();
                }
                catch (Exception ex)
                {
                    WGMessage.ShowAsterisk("导出文件时出错,文件可能正被打开!\n" + ex.Message);
                    return;
                }
                MessageBox.Show("请您注意,导出完成! ", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
예제 #13
0
        private void button3_Click(object sender, EventArgs e)
        {
            //得到excel数据源
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                txtFileSon.Text = openFileDialog1.FileName;
                _DMLINK_excel   = _excelManager.GetExcelTableByOleDB(openFileDialog1.FileName, "DMLINK");
                //ClearSql();

                MessageBox.Show("读取笔数:" + _DMLINK_excel.Rows.Count + "");
                isCheck = false;
                if (_DMLINK_excel == null || _DMLINK_excel.Rows.Count <= 0)
                {
                    WGMessage.ShowWarning(@"无法读取当前Excel!");
                    return;
                }
            }
            else
            {
                return;
            }
        }
예제 #14
0
 private void btnSelect_Click(object sender, EventArgs e)
 {
     //得到excel数据源
     if (opfDialog.ShowDialog() == DialogResult.OK)
     {
         Button btn = sender as Button;
         if (btn.Name == "btnSelect1")
         {
             txtFile1.Text = opfDialog.FileName;
             _ECInfo_excel = _excelManager.GetExcelTableByOleDB(opfDialog.FileName, "MRPRODUCT");
             MessageBox.Show("读取笔数:" + _ECInfo_excel.Rows.Count + "");
         }
         else if (btn.Name == "btnSelect3")
         {
             //物料校验项
             txtFile3.Text  = opfDialog.FileName;
             _ECInfoD_excel = _excelManager.GetExcelTableByOleDB(opfDialog.FileName, "MRCHECKCT");
             MessageBox.Show("读取笔数:" + _ECInfoD_excel.Rows.Count + "");
         }
         else if (btn.Name == "btnSelect4")
         {
             //物料保养项
             txtFile4.Text  = opfDialog.FileName;
             _ECInfoE_excel = _excelManager.GetExcelTableByOleDB(opfDialog.FileName, "MRMAINT");
             MessageBox.Show("读取笔数:" + _ECInfoE_excel.Rows.Count + "");
         }
         ClearSql();
         if (_ECInfo_excel == null || _ECInfo_excel.Rows.Count <= 0)
         {
             WGMessage.ShowWarning(@"无法读取当前Excel!");
             return;
         }
     }
     else
     {
         return;
     }
 }
예제 #15
0
 private void btnSelect1_Click(object sender, EventArgs e)
 {
     //得到excel数据源
     if (opfDialog.ShowDialog() == DialogResult.OK)
     {
         Button btn = sender as Button;
         if (btn.Name == "btnSelect1")
         {
             txtFile1.Text = opfDialog.FileName;
             _ECInfo_excel = _excelManager.GetExcelTableByOleDB(opfDialog.FileName, "DMLIBUARY");
             MessageBox.Show("读取笔数:" + _ECInfo_excel.Rows.Count + "");
         }
         if (_ECInfo_excel == null || _ECInfo_excel.Rows.Count < 0)
         {
             WGMessage.ShowWarning(@"无法读取当前Excel!");
             return;
         }
     }
     else
     {
         return;
     }
 }
예제 #16
0
        private void btnCheck_Click(object sender, EventArgs e)
        {
            try
            {
                if (isCheck)
                {
                    WGMessage.ShowAsterisk("已验证,不用重复验证!");
                    return;
                }
                rbSql.Text = "";
                sqlLs      = new List <string>();

                if (_BSPROCESS_excel == null)
                {
                    WGMessage.ShowWarning("请选择工艺信息文件!");
                    return;
                }

                Dictionary <string, bool> dic = new Dictionary <string, bool>();
                dic.Add("是", true);
                dic.Add("否", false);

                Dictionary <string, int> dicST = new Dictionary <string, int>();
                dicST.Add("未启用", 0);
                dicST.Add("已启用", 1);
                dicST.Add("已停用", 2);

                string sql = "";
                sql           = "SELECT * FROM BSPROCESS";
                _BSPROCESS_DB = FillDatatable(sql, Main.CONN_Public);

                //错误
                List <int[]> col_error1 = new List <int[]>();
                DataTable    dt_error1  = _BSPROCESS_excel.Clone();

                //重复数据
                DataTable dt_repet1     = _BSPROCESS_excel.Clone();
                DataTable dt_datarepet1 = _BSPROCESS_excel.Clone();


                #region 主

                for (int i = 0; i < _BSPROCESS_excel.Rows.Count; i++)
                {
                    bool isError1     = false;
                    bool isRepet1     = false;
                    bool isDataRepet1 = false;

                    DataRow dr_excel = _BSPROCESS_excel.Rows[i];

                    if (string.IsNullOrWhiteSpace(dr_excel["编码"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
                        isError1 = true;
                    }
                    else
                    {
                        DataRow[] drss = _BSPROCESS_excel.Select("编码 = '" + ReturnString(dr_excel["编码"].ToString()) + "'");
                        if (drss.Length > 1)
                        {
                            isRepet1 = true;
                        }

                        DataRow[] drss1 = _BSPROCESS_DB.Select(string.Format("CODE='{0}'", ReturnString(dr_excel["编码"].ToString())));
                        if (drss1.Length > 0)
                        {
                            isDataRepet1 = true;
                        }
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["名称"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 1 });
                        isError1 = true;
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["是否组装"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
                        isError1 = true;
                    }
                    else
                    {
                        if (!dic.ContainsKey(dr_excel["是否组装"].ToString()))
                        {
                            col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
                            isError1 = true;
                        }
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["是否包装"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 3 });
                        isError1 = true;
                    }
                    else
                    {
                        if (!dic.ContainsKey(dr_excel["是否包装"].ToString()))
                        {
                            col_error1.Add(new int[] { dt_error1.Rows.Count, 3 });
                            isError1 = true;
                        }
                    }


                    if (string.IsNullOrWhiteSpace(dr_excel["状态"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 4 });
                        isError1 = true;
                    }
                    else
                    {
                        if (!dicST.ContainsKey(dr_excel["状态"].ToString()))
                        {
                            col_error1.Add(new int[] { dt_error1.Rows.Count, 4 });
                            isError1 = true;
                        }
                    }


                    string BSPROCESS_GUID = Guid.NewGuid().ToString();

                    _BSPROCESS_excel.Rows[i]["GUID"] = BSPROCESS_GUID;


                    if (isError1 || isRepet1 || isDataRepet1)
                    {
                        if (isError1)
                        {
                            dt_error1.Rows.Add(dr_excel.ItemArray);
                        }
                        if (isRepet1)
                        {
                            dt_repet1.Rows.Add(dr_excel.ItemArray);
                        }
                        if (isDataRepet1)
                        {
                            dt_datarepet1.Rows.Add(dr_excel.ItemArray);
                        }
                        continue;
                    }

                    try
                    {
                        string temp = string.Format(@"INSERT INTO [BSPROCESS]
                                       ([GUID],[CODE],[NAME],[NOTE],[ST],[ISPACKAGE],[ISASSEMBLE],[ND],[CD])
                                        VALUES ('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{7}')",
                                                    BSPROCESS_GUID, ReturnString(dr_excel["编码"].ToString()), ReturnString(dr_excel["名称"].ToString()), ReturnString(dr_excel["备注"].ToString()),
                                                    dicST[dr_excel["状态"].ToString()], dic[dr_excel["是否组装"].ToString()], dic[dr_excel["是否包装"].ToString()],
                                                    DateTime.Now.ToString());

                        rbSql.Text += temp + Environment.NewLine;
                        sqlLs.Add(temp);
                    }
                    catch
                    { }
                }


                #endregion



                dt_error1.Columns.Remove("GUID");
                dt_repet1.Columns.Remove("GUID");
                dt_datarepet1.Columns.Remove("GUID");

                dgError1.DataSource     = dt_error1;
                dgRepet1.DataSource     = dt_repet1;
                dgDataRepet1.DataSource = dt_datarepet1;
                if (dt_error1.Rows.Count > 0 || dt_repet1.Rows.Count > 0 || dt_datarepet1.Rows.Count > 0)
                {
                    Main.SetErrorCell(dgError1, col_error1);
                    rbSql.Text = "";
                    isCheck    = false;
                    return;
                }
                isCheck = true;
            }
            catch (Exception ex)
            {
                WGMessage.ShowAsterisk("出现未知异常!请检查Excel文件正确性!" + ex.ToString());
                return;
            }
        }
예제 #17
0
        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;
        }
예제 #18
0
        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;
        }
예제 #19
0
        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;
        }
예제 #20
0
        private void btnCheck_Click(object sender, EventArgs e)
        {
            try
            {
                if (isCheck)
                {
                    WGMessage.ShowAsterisk("已验证,不用重复验证!");
                    return;
                }
                rbSql.Text = "";
                sqlLs      = new List <string>();

                if (_BSCLIENT_excel == null)
                {
                    WGMessage.ShowWarning("请选择职工信息文件!");
                    return;
                }


                string sql = "";
                sql = @"SELECT * FROM BSCLIENT";

                _BSCLIENT_DB = FillDatatable(sql, Main.CONN_Public);

                //sql = @"SELECT * FROM HRWORKLICENSE";
                //_HRWORKLICENSE_DB = FillDatatable(sql, Main.CONN_Public);

                //错误
                List <int[]> col_error1 = new List <int[]>();
                List <int[]> col_error2 = new List <int[]>();
                List <int[]> col_error3 = new List <int[]>();
                DataTable    dt_error1  = _BSCLIENT_excel.Clone();

                //重复数据
                DataTable dt_repet1     = _BSCLIENT_excel.Clone();
                DataTable dt_datarepet1 = _BSCLIENT_excel.Clone();



                Dictionary <string, string> doclist = new Dictionary <string, string>();

                #region 主

                for (int i = 0; i < _BSCLIENT_excel.Rows.Count; i++)
                {
                    bool isError1     = false;
                    bool isRepet1     = false;
                    bool isDataRepet1 = false;

                    DataRow dr_excel = _BSCLIENT_excel.Rows[i];

                    if (string.IsNullOrWhiteSpace(dr_excel["编号"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
                        isError1 = true;
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["简称"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 1 });
                        isError1 = true;
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["税号"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
                        isError1 = true;
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["法人"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 3 });
                        isError1 = true;
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["联系人"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 4 });
                        isError1 = true;
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["联系电话"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 4 });
                        isError1 = true;
                    }

                    if (isError1 || isRepet1 || isDataRepet1)
                    {
                        if (isError1)
                        {
                            dt_error1.Rows.Add(dr_excel.ItemArray);
                        }
                        if (isRepet1)
                        {
                            dt_repet1.Rows.Add(dr_excel.ItemArray);
                        }
                        if (isDataRepet1)
                        {
                            dt_datarepet1.Rows.Add(dr_excel.ItemArray);
                        }
                        continue;
                    }

                    try
                    {
                        string temp = string.Format(@"INSERT INTO BSCLIENT
                                                      (GUID ,CODE,NAME,FNAME,TAXID,LEGAL,CONTACT,CPHONE,EMAIL,FAX,WEB,NOTE,ND,CD)
                                                      VALUES  
                                                      ( NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',GETDATE())",
                                                    ReturnString(dr_excel["编号"].ToString()), ReturnString(dr_excel["简称"].ToString()), ReturnString(dr_excel["全称"].ToString()),
                                                    ReturnString(dr_excel["税号"].ToString()), ReturnString(dr_excel["法人"].ToString()), ReturnString(dr_excel["联系人"].ToString()),
                                                    ReturnString(dr_excel["联系电话"].ToString()), ReturnString(dr_excel["邮箱"].ToString()), ReturnString(dr_excel["传真号码"].ToString()),
                                                    ReturnString(dr_excel["网址"].ToString()), ReturnString(dr_excel["备注"].ToString()), ReturnString(dr_excel["创建时间"].ToString()));

                        rbSql.Text += temp + Environment.NewLine;
                        sqlLs.Add(temp);
                    }
                    catch
                    { }
                }


                #endregion

                dt_error1.Columns.Remove("GUID");
                dt_repet1.Columns.Remove("GUID");
                dt_datarepet1.Columns.Remove("GUID");

                dgError1.DataSource     = dt_error1;
                dgRepet1.DataSource     = dt_repet1;
                dgDataRepet1.DataSource = dt_datarepet1;
                if (dt_error1.Rows.Count > 0 || dt_repet1.Rows.Count > 0 || dt_datarepet1.Rows.Count > 0)
                {
                    Main.SetErrorCell(dgError1, col_error1);
                    rbSql.Text = "";
                    isCheck    = false;
                    return;
                }
                isCheck = true;
            }
            catch (Exception ex)
            {
                WGMessage.ShowAsterisk("出现未知异常!请检查2个Excel文件正确性和顺序的正确性!" + ex.ToString());
                return;
            }
        }
예제 #21
0
        private void btnCheck_Click(object sender, EventArgs e)
        {
            if (_ECInfo_excel == null)
            {
                WGMessage.ShowWarning("请选择[文件清单]文件!");
                return;
            }
            if (textBox1.Text.Trim() == "")
            {
                WGMessage.ShowWarning("请选择文档源目录!");
                return;
            }
            if (textBox2.Text.Trim() == null)
            {
                WGMessage.ShowWarning("请选择服务器文档存储目录!");
                return;
            }
            if (isCheck)
            {
                WGMessage.ShowAsterisk("已验证,不用重复验证!");
                return;
            }
            DirectoryInfo folder = new DirectoryInfo(textBox1.Text);

            FileInfo[] dirInfo = folder.GetFiles();
            if (dirInfo.Length != _ECInfo_excel.Rows.Count)
            {
                WGMessage.ShowAsterisk("文件清单和文档源目录文件数量不匹配!");
                return;
            }
            DataTable dataTablFile = new DataTable();

            dataTablFile.Columns.Add("FILENAME", typeof(string));
            dataTablFile.Columns.Add("FULLFILENAME", typeof(string));
            foreach (FileInfo item in dirInfo)
            {
                dataTablFile.Rows.Add(item.ToString().Trim(), item.FullName.ToString().Trim());
            }
            strSql = "";
            _ECInfo_excel.Columns.Add("FULLFILENAME", typeof(string));
            DataTable dt_error = _ECInfo_excel.Clone();

            foreach (DataRow item in _ECInfo_excel.Rows)
            {
                DataRow[] drs = dataTablFile.Select("FILENAME='" + ReturnString(item["文件名"].ToString()) + "'");
                if (drs.Length == 0)
                {
                    dt_error.Rows.Add(item.ItemArray);
                }
                FileInfo file  = new FileInfo(drs[0]["FULLFILENAME"].ToString());
                string   Fsize = (file.Length / 1024 == 0 ? 1 : file.Length / 1024) + "";
                if (strSql == "")
                {
                    strSql = "UPDATE DMVERSION SET FSIZE=" + Fsize + " WHERE FKEY='" + item["文件KEY"].ToString() + "'";
                }
                else
                {
                    strSql = strSql + "\r\n" + "UPDATE DMVERSION SET FSIZE=" + Fsize + " WHERE FKEY='" + item["文件KEY"].ToString() + "'";
                }
                item["FULLFILENAME"] = drs[0]["FULLFILENAME"].ToString();
            }
            if (dt_error.Rows.Count > 0)
            {
                isCheck = false;
                dgError.SetDataBinding(dt_error, "", false);
            }
            else
            {
                WGMessage.ShowAsterisk("数据检验通过!");
                isCheck = true;
            }
        }
예제 #22
0
        private void btnCheck_Click(object sender, EventArgs e)
        {
            try
            {
                if (isCheck)
                {
                    WGMessage.ShowAsterisk("已验证,不用重复验证!");
                    return;
                }
                rbSql.Text = "";
                sqlLs      = new List <string>();

                string       sql        = "";
                List <int[]> col_error2 = new List <int[]>();

                if (_HRWORKLICENSEA_excel == null)
                {
                    WGMessage.ShowWarning("请选择职工信息上岗证文件!");
                    return;
                }
                else
                {
                    sql            = "SELECT * FROM HRPHYSICAL WHERE ST=1";
                    _HRPHYSICAL_DB = FillDatatable(sql, Main.CONN_Public);

                    sql            = @"SELECT * FROM BSWORKSHOP";
                    _BSWORKSHOP_DB = FillDatatable(sql, Main.CONN_Public);

                    sql        = @"SELECT * FROM ECTYPE";
                    _ECTYPE_DB = FillDatatable(sql, Main.CONN_Public);

                    sql        = @"SELECT * FROM ECINFO";
                    _ECINFO_DB = FillDatatable(sql, Main.CONN_Public);

                    sql = @"SELECT * FROM HRWORKLICENSE";
                    _HRWORKLICENSE_DB = FillDatatable(sql, Main.CONN_Public);

                    DataTable dt_error2 = _HRWORKLICENSEA_excel.Clone();
                    //重复数据
                    DataTable dt_repet2 = _HRWORKLICENSEA_excel.Clone();
                    #region 子
                    string BWguid = "";
                    string ETGuid = "";
                    string ECguid = "";
                    string Mguid  = "";
                    string Fguid  = "";

                    for (int i = 0; i < _HRWORKLICENSEA_excel.Rows.Count; i++)
                    {
                        bool isError2 = false;
                        bool isRepet2 = false;

                        DataRow dr_excel = _HRWORKLICENSEA_excel.Rows[i];

                        if (string.IsNullOrWhiteSpace(dr_excel["车间编号"].ToString()))
                        {
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
                            isError2 = true;
                        }
                        else
                        {
                            DataRow[] drss = _BSWORKSHOP_DB.Select("CODE='" + ReturnString(dr_excel["车间编号"].ToString()) + "'");
                            if (drss.Length > 0)
                            {
                                BWguid = drss[0]["GUID"].ToString();
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
                                isError2 = true;
                            }
                        }


                        if (string.IsNullOrWhiteSpace(dr_excel["设备类别编号"].ToString()))
                        {
                            //空
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
                            isError2 = true;
                        }
                        else
                        {
                            DataRow[] drss = _ECTYPE_DB.Select("CODE='" + ReturnString(dr_excel["设备类别编号"].ToString()) + "'");
                            if (drss.Length > 0)
                            {
                                ETGuid = drss[0]["GUID"].ToString();
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
                                isError2 = true;
                            }
                        }


                        if (string.IsNullOrEmpty(dr_excel["设备编号"].ToString()))
                        {
                            DataRow[] drss = _HRWORKLICENSE_DB.Select(string.Format("AGUID='{0}' AND BGUID='{1}'", BWguid, ETGuid));
                            if (drss.Length > 0)
                            {
                                Mguid = drss[0]["GUID"].ToString();
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
                                isError2 = true;
                            }
                        }
                        else
                        {
                            DataRow[] drss = _ECINFO_DB.Select(string.Format("CODE='{0}' AND AGUID='{1}'", ReturnString(dr_excel["设备编号"].ToString()), ETGuid == "" ? Guid.Empty.ToString() : ETGuid));
                            if (drss.Length > 0)
                            {
                                DataRow[] drss1 = _HRWORKLICENSE_DB.Select(string.Format("AGUID='{0}' AND BGUID='{1}' AND CGUID='{2}'", BWguid, ETGuid, drss[0]["GUID"].ToString()));
                                if (drss1.Length > 0)
                                {
                                    Mguid = drss1[0]["GUID"].ToString();
                                }
                                else
                                {
                                    col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
                                    isError2 = true;
                                }
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
                                isError2 = true;
                            }
                        }



                        int numa = 0;
                        if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) || !int.TryParse(dr_excel["序号"].ToString(), out numa))
                        {
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
                            isError2 = true;
                        }


                        if (string.IsNullOrWhiteSpace(dr_excel["体检要求"].ToString()))
                        {
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
                            isError2 = true;
                        }
                        else
                        {
                            DataRow[] drss = _HRPHYSICAL_DB.Select("NAME='" + ReturnString(dr_excel["体检要求"].ToString()) + "'");
                            if (drss.Length > 0)
                            {
                                Fguid = drss[0]["GUID"].ToString();
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 4 });
                                isError2 = true;
                            }
                        }


                        DataRow[] drsp = _HRWORKLICENSEA_excel.Select(string.Format(@"车间编号='{0}' AND 设备类别编号='{1}' AND 设备编号='{2}' AND 体检要求='{3}'",
                                                                                    ReturnString(dr_excel["车间编号"].ToString()), ReturnString(dr_excel["设备类别编号"].ToString()), ReturnString(dr_excel["设备编号"].ToString()), ReturnString(dr_excel["体检要求"].ToString())));
                        if (drsp.Length > 1)
                        {
                            isRepet2 = true;
                        }



                        if (isError2 || isRepet2)
                        {
                            if (isError2)
                            {
                                dt_error2.Rows.Add(dr_excel.ItemArray);
                            }
                            if (isRepet2)
                            {
                                dt_repet2.Rows.Add(dr_excel.ItemArray);
                            }
                            continue;
                        }

                        string temp = string.Format(@" INSERT INTO HRWORKLICENSEA (GUID,PGUID,FGUID,LVL) 
                                                VALUES(NEWID(),'{0}','{1}',{2})",
                                                    Mguid, Fguid, numa);

                        rbSql.Text += temp + Environment.NewLine;
                        sqlLs.Add(temp);
                    }
                    #endregion
                    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 = "";
                        isCheck    = false;
                        return;
                    }
                    isCheck = true;
                }
            }
            catch (Exception ex)
            {
                WGMessage.ShowAsterisk("出现未知异常!请检查Excel文件正确性!" + ex.ToString());
                return;
            }
        }
예제 #23
0
        private void btnCheck_Click(object sender, EventArgs e)
        {
            try
            {
                if (isCheck)
                {
                    WGMessage.ShowAsterisk("已验证,不用重复验证!");
                    return;
                }
                rbSql.Text = "";
                sqlLs      = new List <string>();

                if (_BSWORKCENTER_excel == null)
                {
                    WGMessage.ShowWarning("请选择工作中心文件!");
                    return;
                }
                if (_BSWORKCENTERS_excel == null)
                {
                    WGMessage.ShowWarning("请选择工作中心工位文件!");
                    return;
                }

                string sql = "";

                sql = "SELECT * FROM BSWORKCENTER";
                _BSWORKCENTER_DB = FillDatatablde(sql, Main.CONN_Public);

                sql = "SELECT * FROM BSWORKCENTERA";
                _BSWORKCENTERS_DB = FillDatatablde(sql, Main.CONN_Public);

                sql            = "SELECT * FROM BSWORKSHOP";
                _BSWORKSHOP_DB = FillDatatablde(sql, Main.CONN_Public);

                sql          = "SELECT * FROM BSWCTYPE";
                _BSWCTYPE_DB = FillDatatablde(sql, Main.CONN_Public);

                sql        = "SELECT * FROM ECINFO";
                _ECINFO_DB = FillDatatablde(sql, Main.CONN_Public);
                //错误
                List <int[]> col_error1 = new List <int[]>();
                List <int[]> col_error2 = new List <int[]>();

                DataTable dt_error1 = _BSWORKCENTER_excel.Clone();
                //重复数据
                DataTable dt_repet1     = _BSWORKCENTER_excel.Clone();
                DataTable dt_datarepet1 = _BSWORKCENTER_excel.Clone();

                DataTable dt_error2 = _BSWORKCENTERS_excel.Clone();
                //重复数据
                DataTable dt_repet2     = _BSWORKCENTERS_excel.Clone();
                DataTable dt_datarepet2 = _BSWORKCENTERS_excel.Clone();

                Dictionary <string, string> doclist = new Dictionary <string, string>();

                #region 主
                string Pguid = "";
                string Tguid = "";
                Dictionary <string, int> dic = new Dictionary <string, int>();
                dic.Add("正常", 0);
                dic.Add("停用", 1);
                for (int i = 0; i < _BSWORKCENTER_excel.Rows.Count; i++)
                {
                    bool isError1     = false;
                    bool isRepet1     = false;
                    bool isDataRepet1 = false;

                    DataRow dr_excel = _BSWORKCENTER_excel.Rows[i];

                    if (string.IsNullOrWhiteSpace(dr_excel["工作中心编号"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
                        isError1 = true;
                    }
                    else
                    {
                        DataRow[] drss = _BSWORKCENTER_excel.Select("工作中心编号 = '" + ReturnString(dr_excel["工作中心编号"].ToString()) + "'");
                        if (drss.Length > 1)
                        {
                            isRepet1 = true;
                        }

                        DataRow[] drss1 = _BSWORKCENTER_DB.Select(string.Format("CODE='{0}'", ReturnString(dr_excel["工作中心编号"].ToString())));
                        if (drss1.Length > 0)
                        {
                            isDataRepet1 = true;
                        }
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["工作中心名称"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 1 });
                        isError1 = true;
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["车间编号"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
                        isError1 = true;
                    }
                    else
                    {
                        DataRow[] drss = _BSWORKSHOP_DB.Select("CODE = '" + ReturnString(dr_excel["车间编号"].ToString()) + "'");
                        if (drss.Length > 0)
                        {
                            Pguid = drss[0]["GUID"].ToString();
                        }
                        else
                        {
                            col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
                            isError1 = true;
                        }
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["工作中心类别编号"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
                        isError1 = true;
                    }
                    else
                    {
                        DataRow[] drss = _BSWCTYPE_DB.Select("CODE = '" + ReturnString(dr_excel["工作中心类别编号"].ToString()) + "'");
                        if (drss.Length > 0)
                        {
                            Tguid = drss[0]["GUID"].ToString();
                        }
                        else
                        {
                            col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
                            isError1 = true;
                        }
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["状态"].ToString()) || !dic.ContainsKey(dr_excel["状态"].ToString()))
                    {
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 6 });
                        isError1 = true;
                    }

                    string BSWORKCENTER_GUID = Guid.NewGuid().ToString();

                    _BSWORKCENTER_excel.Rows[i]["GUID"] = BSWORKCENTER_GUID;


                    if (isError1 || isRepet1 || isDataRepet1)
                    {
                        if (isError1)
                        {
                            dt_error1.Rows.Add(dr_excel.ItemArray);
                        }
                        if (isRepet1)
                        {
                            dt_repet1.Rows.Add(dr_excel.ItemArray);
                        }
                        if (isDataRepet1)
                        {
                            dt_datarepet1.Rows.Add(dr_excel.ItemArray);
                        }
                        continue;
                    }

                    try
                    {
                        string temp = string.Format(@"INSERT INTO BSWORKCENTER
                                                      (GUID,AGUID,ACODE,ANAME,CODE,NAME,BGUID,BCODE,BNAME,NOTE,ST,ND,CD)
                                                      VALUES  
                                                      ('{0}',{1},'{2}', '{3}', '{4}','{5}','{6}','{7}','{8}','{9}',0,GETDATE(),GETDATE())",
                                                    BSWORKCENTER_GUID, Tguid == "" ? "null" : "'" + Tguid + "'", ReturnString(dr_excel["工作中心类别编号"].ToString()),
                                                    ReturnString(dr_excel["工作中心类别名称"].ToString()), ReturnString(dr_excel["工作中心编号"].ToString()),
                                                    ReturnString(dr_excel["工作中心名称"].ToString()), Pguid, ReturnString(dr_excel["车间编号"].ToString()), ReturnString(dr_excel["车间编号"].ToString()),
                                                    ReturnString(dr_excel["备注"].ToString()), dic[dr_excel["状态"].ToString()],
                                                    DateTime.Now.ToString());

                        rbSql.Text += temp + Environment.NewLine;
                        sqlLs.Add(temp);
                    }
                    catch
                    { }
                }


                #endregion

                #region 子
                string Mguid = "";
                string FGUID = "null";
                Dictionary <string, bool> dicISTASKSTART = new Dictionary <string, bool>();
                dicISTASKSTART.Add("是", true);
                dicISTASKSTART.Add("否", false);

                Dictionary <string, bool> dicISREPORT = new Dictionary <string, bool>();
                dicISREPORT.Add("是", true);
                dicISREPORT.Add("否", false);

                for (int i = 0; i < _BSWORKCENTERS_excel.Rows.Count; i++)
                {
                    bool isError2     = false;
                    bool isRepet2     = false;
                    bool isdataRepet2 = false;

                    DataRow dr_excel = _BSWORKCENTERS_excel.Rows[i];

                    if (string.IsNullOrWhiteSpace(dr_excel["工作中心编号"].ToString()))
                    {
                        //空
                        col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
                        isError2 = true;
                    }
                    else
                    {
                        DataRow[] drss = _BSWORKCENTER_excel.Select("工作中心编号='" + ReturnString(dr_excel["工作中心编号"].ToString()) + "'");
                        if (drss.Length > 0)
                        {
                            Mguid = drss[0]["GUID"].ToString();
                        }
                        else
                        {
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
                            isError2 = true;
                        }
                    }

                    int numa = 0;
                    if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) || !int.TryParse(dr_excel["序号"].ToString(), out numa))
                    {
                        //空
                        col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
                        isError2 = true;
                    }
                    else
                    {
                        if (numa <= 0)
                        {
                            //空
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
                            isError2 = true;
                        }
                    }

                    if (string.IsNullOrEmpty(dr_excel["工位编号"].ToString()))
                    {
                        //空
                        col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
                        isError2 = true;
                    }
                    else
                    {
                        DataRow[] drss = _BSWORKCENTERS_excel.Select("工作中心编号='" + ReturnString(dr_excel["工作中心编号"].ToString()) + "' AND 工位编号='" + ReturnString(dr_excel["工位编号"].ToString()) + "'");
                        if (drss.Length > 1)
                        {
                            isRepet2 = true;
                        }

                        DataRow[] drs = _BSWORKCENTERS_DB.Select("CODE='" + ReturnString(dr_excel["工位编号"].ToString()) + "'");
                        if (drs.Length > 0)
                        {
                            isdataRepet2 = true;
                        }
                    }

                    if (string.IsNullOrWhiteSpace(dr_excel["工位名称"].ToString()))
                    {
                        col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
                        isError2 = true;
                    }

                    if (!string.IsNullOrWhiteSpace(dr_excel["设备编号"].ToString()))
                    {
                        DataRow[] drss = _ECINFO_DB.Select("CODE='" + dr_excel["设备编号"] + "'");
                        if (drss.Length > 0)
                        {
                            FGUID = "'" + drss[0]["GUID"].ToString() + "'";
                        }
                        else
                        {
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 4 });
                            isError2 = true;
                        }
                    }
                    else
                    {
                        FGUID = "null";
                    }

                    //if (string.IsNullOrWhiteSpace(dr_excel["是否开启生产任务工位"].ToString()))
                    //{
                    //    col_error2.Add(new int[] { dt_error2.Rows.Count, 6 });
                    //    isError2 = true;
                    //}
                    //else
                    //{
                    //    if (!dicISTASKSTART.ContainsKey(dr_excel["是否开启生产任务工位"].ToString()))
                    //    {
                    //        col_error2.Add(new int[] { dt_error2.Rows.Count, 6 });
                    //        isError2 = true;
                    //    }
                    //}

                    //if (string.IsNullOrWhiteSpace(dr_excel["是否报工工位"].ToString()))
                    //{
                    //    col_error2.Add(new int[] { dt_error2.Rows.Count, 7 });
                    //    isError2 = true;
                    //}
                    //else
                    //{
                    //    if (!dicISREPORT.ContainsKey(dr_excel["是否报工工位"].ToString()))
                    //    {
                    //        col_error2.Add(new int[] { dt_error2.Rows.Count, 7 });
                    //        isError2 = true;
                    //    }
                    //}


                    if (isError2 || isRepet2 || isdataRepet2)
                    {
                        if (isError2)
                        {
                            dt_error2.Rows.Add(dr_excel.ItemArray);
                        }
                        if (isRepet2)
                        {
                            dt_repet2.Rows.Add(dr_excel.ItemArray);
                        }
                        if (isdataRepet2)
                        {
                            dt_datarepet2.Rows.Add(dr_excel.ItemArray);
                        }
                        continue;
                    }

                    string temp = string.Format(@" INSERT INTO BSWORKCENTERA
                                                  (GUID,SNO,PGUID,CODE,NAME,AGUID,ACODE,ANAME,NOTE,ND,CD)
                                                  VALUES  
                                                  ( NEWID(),'{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}',GETDATE(), GETDATE())",
                                                dr_excel["序号"], Mguid, dr_excel["工位编号"], dr_excel["工位名称"], FGUID, dr_excel["设备名称"], dr_excel["设备编号"], dr_excel["备注"]);

                    rbSql.Text += temp + Environment.NewLine;
                    sqlLs.Add(temp);
                }
                #endregion


                dt_error1.Columns.Remove("GUID");
                dt_repet1.Columns.Remove("GUID");
                dt_datarepet1.Columns.Remove("GUID");

                dgError1.DataSource     = dt_error1; dgError2.DataSource = dt_error2;
                dgRepet1.DataSource     = dt_repet1; dgRepet2.DataSource = dt_repet2;
                dgDataRepet1.DataSource = dt_datarepet1; dgDataRepet2.DataSource = dt_datarepet2;
                if (dt_error1.Rows.Count > 0 || dt_error2.Rows.Count > 0 || dt_repet1.Rows.Count > 0 || dt_repet2.Rows.Count > 0 || dt_datarepet1.Rows.Count > 0 || dt_datarepet2.Rows.Count > 0)
                {
                    Main.SetErrorCell(dgError1, col_error1);
                    Main.SetErrorCell(dgError2, col_error2);
                    rbSql.Text = "";
                    isCheck    = false;
                    return;
                }
                isCheck = true;
            }
            catch (Exception ex)
            {
                WGMessage.ShowAsterisk("出现未知异常!请检查2个Excel文件正确性和顺序的正确性!" + ex.ToString());
                return;
            }
        }
예제 #24
0
        private void btnSelect1_Click(object sender, EventArgs e)
        {
            //得到excel数据源
            if (opfDialog.ShowDialog() == DialogResult.OK)
            {
                Button btn = sender as Button;
                if (btn.Name == "btnSelect1")
                {
                    txtFile1.Text    = opfDialog.FileName;
                    _HRLIBRARY_excel = _excelManager.GetExcelTableByOleDB(opfDialog.FileName, "HRLIBRARY");
                    _HRLIBRARY_excel.Columns.Add("GUID");
                    MessageBox.Show("读取笔数:" + _HRLIBRARY_excel.Rows.Count + "");

                    if (_HRLIBRARY_excel == null || _HRLIBRARY_excel.Rows.Count <= 0)
                    {
                        WGMessage.ShowWarning(@"无法读取当前Excel!");
                        return;
                    }
                }
                else if (btn.Name == "btnSelect2")
                {
                    txtFile2.Text     = opfDialog.FileName;
                    _HRLIBRARYS_excel = _excelManager.GetExcelTableByOleDB(opfDialog.FileName, "HRLIBRARYS");
                    _HRLIBRARYS_excel.Columns.Add("GUID");
                    MessageBox.Show("读取笔数:" + _HRLIBRARYS_excel.Rows.Count + "");

                    if (_HRLIBRARYS_excel == null || _HRLIBRARYS_excel.Rows.Count <= 0)
                    {
                        WGMessage.ShowWarning(@"无法读取当前Excel!");
                        return;
                    }
                }
                else if (btn.Name == "btnSelect3")
                {
                    txtFile3.Text      = opfDialog.FileName;
                    _HRLIBRARYWS_excel = _excelManager.GetExcelTableByOleDB(opfDialog.FileName, "HRLIBRARYW");
                    MessageBox.Show("读取笔数:" + _HRLIBRARYWS_excel.Rows.Count + "");

                    if (_HRLIBRARYWS_excel == null || _HRLIBRARYWS_excel.Rows.Count <= 0)
                    {
                        WGMessage.ShowWarning(@"无法读取当前Excel!");
                        return;
                    }
                }
                //else if (btn.Name == "btnSelect4")
                //{
                //    txtFile4.Text = opfDialog.FileName;
                //    _BSPRODPLAN_excel = _excelManager.GetExcelTableByOleDB(opfDialog.FileName, "BSPRODPLAN");
                //    _BSPRODPLAN_excel.Columns.Add("GUID");
                //    MessageBox.Show("读取笔数:" + _BSPRODPLAN_excel.Rows.Count + "");

                //    if (_BSPRODPLAN_excel == null || _BSPRODPLAN_excel.Rows.Count <= 0)
                //    {
                //        WGMessage.ShowWarning(@"无法读取当前Excel!");
                //        return;
                //    }
                //}
                ClearSql();
            }
            else
            {
                return;
            }
        }
예제 #25
0
        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();
        }
예제 #26
0
파일: MRBOM.cs 프로젝트: 18368404576/Import
        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();
        }
예제 #27
0
        private void btnCheck_Click(object sender, EventArgs e)
        {
            try
            {
                if (isCheck)
                {
                    WGMessage.ShowAsterisk("已验证,不用重复验证!");
                    return;
                }
                rbSql.Text = "";
                sqlLs      = new List <string>();

                string       sql        = "";
                List <int[]> col_error2 = new List <int[]>();

                if (_HREMPLOYEES_excel == null)
                {
                    WGMessage.ShowWarning("请选择职工信息上岗证文件!");
                    return;
                }
                else
                {
                    sql            = "SELECT * FROM HREMPLOYEE";
                    _HREMPLOYEE_DB = FillDatatable(sql, Main.CONN_Public);

                    sql            = @"SELECT * FROM BSWORKSHOP";
                    _BSWORKSHOP_DB = FillDatatable(sql, Main.CONN_Public);

                    sql        = @"SELECT * FROM ECTYPE";
                    _ECTYPE_DB = FillDatatable(sql, Main.CONN_Public);

                    sql        = @"SELECT * FROM ECINFO";
                    _ECINFO_DB = FillDatatable(sql, Main.CONN_Public);

                    sql = @"SELECT * FROM HRWORKLICENSE";
                    _HRWORKLICENSE_DB = FillDatatable(sql, Main.CONN_Public);

                    DataTable dt_error2 = _HREMPLOYEES_excel.Clone();
                    //重复数据
                    DataTable dt_repet2 = _HREMPLOYEES_excel.Clone();
                    #region 子
                    string BWguid = "";
                    string ETGuid = "";
                    string ECguid = "";
                    string Mguid  = "";
                    string Fguid  = "";
                    string EC     = "";
                    int    LVL    = 0;
                    Dictionary <string, int> dicST = new Dictionary <string, int>();
                    dicST.Add("试用", 0);
                    dicST.Add("待批", 1);
                    dicST.Add("正常", 2);
                    dicST.Add("冻结", 3);
                    dicST.Add("失效", 4);
                    List <int> lstLVL = new List <int>()
                    {
                        1, 2, 3, 4
                    };
                    for (int i = 0; i < _HREMPLOYEES_excel.Rows.Count; i++)
                    {
                        bool isError2 = false;
                        bool isRepet2 = false;

                        DataRow dr_excel = _HREMPLOYEES_excel.Rows[i];

                        if (string.IsNullOrWhiteSpace(dr_excel["员工工号"].ToString()))
                        {
                            //空
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
                            isError2 = true;
                        }
                        else
                        {
                            DataRow[] drs = _HREMPLOYEE_DB.Select("EMPCODE='" + ReturnString(dr_excel["员工工号"].ToString()) + "'");
                            if (drs.Length > 0)
                            {
                                Mguid = drs[0]["GUID"].ToString();
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
                                isError2 = true;
                            }
                        }

                        int numa;
                        if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) || !int.TryParse(dr_excel["序号"].ToString(), out numa))
                        {
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
                            isError2 = true;
                        }

                        if (string.IsNullOrWhiteSpace(dr_excel["车间编号"].ToString()))
                        {
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
                            isError2 = true;
                        }
                        else
                        {
                            DataRow[] drss = _BSWORKSHOP_DB.Select("CODE='" + ReturnString(dr_excel["车间编号"].ToString()) + "'");
                            if (drss.Length > 0)
                            {
                                BWguid = drss[0]["GUID"].ToString();
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
                                isError2 = true;
                            }
                        }

                        if (string.IsNullOrWhiteSpace(dr_excel["设备类别编号"].ToString()))
                        {
                            //空
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
                            isError2 = true;
                        }
                        else
                        {
                            DataRow[] drss = _ECTYPE_DB.Select("CODE='" + ReturnString(dr_excel["设备类别编号"].ToString()) + "'");
                            if (drss.Length > 0)
                            {
                                ETGuid = drss[0]["GUID"].ToString();
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
                                isError2 = true;
                            }
                        }



                        if (string.IsNullOrEmpty(dr_excel["设备编号"].ToString()))
                        {
                            EC     = " AND 设备编号 is null";
                            ECguid = " AND CGUID is null";
                        }
                        else
                        {
                            DataRow[] drss = _ECINFO_DB.Select(string.Format("CODE='{0}' AND AGUID='{1}'", ReturnString(dr_excel["设备编号"].ToString()), ETGuid == "" ? Guid.Empty.ToString() : ETGuid));
                            if (drss.Length > 0)
                            {
                                ECguid = "AND CGUID='" + drss[0]["GUID"].ToString() + "'";
                                EC     = " AND 设备编号='" + drss[0]["CODE"].ToString() + "'";
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 4 });
                                isError2 = true;
                            }
                        }


                        if (string.IsNullOrWhiteSpace(dr_excel["星级"].ToString()) || !int.TryParse(dr_excel["星级"].ToString(), out numa))
                        {
                            //空
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 5 });
                            isError2 = true;
                        }
                        else
                        {
                            if (lstLVL.Contains(numa))
                            {
                                LVL = numa;
                            }
                            else
                            {
                                LVL = 0;
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 5 });
                                isError2 = true;
                            }

                            DataRow[] drss = _HREMPLOYEES_excel.Select(string.Format("员工工号='{0}' AND 车间编号='{1}' AND 设备类别编号='{2}' {3}", ReturnString(dr_excel["员工工号"].ToString()), ReturnString(dr_excel["车间编号"].ToString()), ReturnString(dr_excel["设备类别编号"].ToString()), EC));
                            if (drss.Length > 1)
                            {
                                isRepet2 = true;
                            }
                            else
                            {
                                drss = _HRWORKLICENSE_DB.Select(string.Format("AGUID='{0}' AND BGUID='{1}' {2}", BWguid, ETGuid, ECguid));
                                if (drss.Length > 0)
                                {
                                    Fguid = drss[0]["GUID"].ToString();
                                }
                                else
                                {
                                    col_error2.Add(new int[] { dt_error2.Rows.Count, 4 });
                                    isError2 = true;
                                }
                            }
                        }

                        DateTime DATE = new DateTime();
                        if (string.IsNullOrWhiteSpace(dr_excel["上岗证颁发日期"].ToString()) || !DateTime.TryParse(dr_excel["上岗证颁发日期"].ToString(), out DATE))
                        {
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 6 });
                            isError2 = true;
                        }

                        string EDATE = "";
                        if (string.IsNullOrWhiteSpace(dr_excel["最后一次上岗时间"].ToString()))
                        {
                            EDATE = "null";
                        }
                        else
                        {
                            if (DateTime.TryParse(dr_excel["最后一次上岗时间"].ToString(), out DATE))
                            {
                                EDATE = "'" + DATE.ToString() + "'";
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 7 });
                                isError2 = true;
                            }
                        }

                        string ADATE = "";
                        if (string.IsNullOrWhiteSpace(dr_excel["冻结时间/失效时间"].ToString()))
                        {
                            ADATE = "null";
                        }
                        else
                        {
                            if (DateTime.TryParse(dr_excel["冻结时间/失效时间"].ToString(), out DATE))
                            {
                                ADATE = "'" + DATE.ToString() + "'";
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 8 });
                                isError2 = true;
                            }
                        }

                        string CDATE = "";
                        if (string.IsNullOrWhiteSpace(dr_excel["试用期"].ToString()))
                        {
                            CDATE = "null";
                        }
                        else
                        {
                            if (DateTime.TryParse(dr_excel["试用期"].ToString(), out DATE))
                            {
                                CDATE = "'" + DATE.ToString() + "'";
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 9 });
                                isError2 = true;
                            }
                        }

                        if (string.IsNullOrWhiteSpace(dr_excel["状态"].ToString()))
                        {
                            //空
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 10 });
                            isError2 = true;
                        }
                        else
                        {
                            if (!dicST.ContainsKey(dr_excel["状态"].ToString()))
                            {
                                //空
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 10 });
                                isError2 = true;
                            }
                        }

                        if (isError2 || isRepet2)
                        {
                            if (isError2)
                            {
                                dt_error2.Rows.Add(dr_excel.ItemArray);
                            }
                            if (isRepet2)
                            {
                                dt_repet2.Rows.Add(dr_excel.ItemArray);
                            }
                            continue;
                        }

                        string temp = string.Format(@" INSERT INTO HREMPLOYEES (GUID,PGUID,FGUID,LVL,ADATE,CDATE,DDATE,EDATE,ISREMIND,ISTRIAL,ST) 
                                                VALUES(NEWID(),'{0}','{1}',{2},{3},{4},'{5}',{6},'0','0',{7})",
                                                    Mguid, Fguid, LVL, ADATE, CDATE, dr_excel["上岗证颁发日期"], EDATE, dicST[dr_excel["状态"].ToString()]);

                        rbSql.Text += temp + Environment.NewLine;
                        sqlLs.Add(temp);
                    }
                    #endregion
                    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 = "";
                        isCheck    = false;
                        return;
                    }
                    isCheck = true;
                }
            }
            catch (Exception ex)
            {
                WGMessage.ShowAsterisk("出现未知异常!请检查Excel文件正确性!" + ex.ToString());
                return;
            }
        }
예제 #28
0
        private void btnCheck_Click(object sender, EventArgs e)
        {
            try
            {
                if (isCheck)
                {
                    WGMessage.ShowAsterisk("已验证,不用重复验证!");
                    return;
                }
                rbSql.Text = string.Empty;
                sqlLs      = new List <string>();
                if (_BSWCTYPE_excel == null)
                {
                    WGMessage.ShowWarning("请选择[工作中心类别]文件!");
                    return;
                }
                string sql = string.Empty;


                //错误
                List <int[]> col_error1 = new List <int[]>();
                DataTable    dt_error1  = _BSWCTYPE_excel.Clone();
                //重复数据

                DataTable dt_repet1 = _BSWCTYPE_excel.Clone();
                foreach (DataRow row in _BSWCTYPE_excel.Rows)
                {
                    bool isError1 = false;
                    bool isRepet1 = false;
                    if (string.IsNullOrWhiteSpace(row["类别编号"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
                        isError1 = true;
                    }
                    else
                    {
                        DataRow[] drss = _BSWCTYPE_excel.Select(string.Format("类别编号 = '{0}'", ReturnString(row["类别编号"].ToString())));
                        if (drss.Length > 1)
                        {
                            isRepet1 = true;
                        }
                    }

                    if (string.IsNullOrWhiteSpace(row["类别名称"].ToString()))
                    {
                        //空
                        col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
                        isError1 = true;
                    }
                    else
                    {
                        DataRow[] drss = _BSWCTYPE_excel.Select(string.Format("类别名称 = '{0}'", ReturnString(row["类别名称"].ToString())));
                        if (drss.Length > 1)
                        {
                            isRepet1 = true;
                        }
                    }


                    string BSWCTYP_GUID = Guid.NewGuid().ToString();
                    row["GUID"] = BSWCTYP_GUID;

                    if (isError1 || isRepet1)
                    {
                        if (isError1)
                        {
                            dt_error1.Rows.Add(row.ItemArray);
                        }
                        if (isRepet1)
                        {
                            dt_repet1.Rows.Add(row.ItemArray);
                        }
                        continue;
                    }

                    try
                    {
                        string temp = string.Format(@"  INSERT INTO [BSWCTYPE] ( [GUID], [CODE], [NAME], ST, [NOTE], [CC], [ND], [CD] )
                                                    VALUES  ( '{0}', -- GUID - uniqueidentifier
                                                              '{1}', -- CODE - nvarchar(20)
                                                              '{2}', -- NAME - nvarchar(40)
                                                               0,
                                                              '{3}', -- NOTE - nvarchar(200)
                                                              '1023', -- CC - nvarchar(20)
                                                              GETDATE(), -- ND - datetime
                                                              NULL  -- CD - datetime
                                                              )", row["GUID"], row["类别编号"], row["类别名称"], row["备注"]);

                        rbSql.Text += temp + Environment.NewLine;
                        sqlLs.Add(temp);
                    }
                    catch
                    { }
                }
                dt_error1.Columns.Remove("GUID");
                dt_repet1.Columns.Remove("GUID");

                dgError.DataSource = dt_error1;
                dgRepet.DataSource = dt_repet1;
                DataTable dt_repetFromDB = CheckDBRepeat(_BSWCTYPE_excel.Copy());
                dgRepetFromDB.DataSource = dt_repetFromDB;

                if (dt_error1.Rows.Count > 0 || dt_repet1.Rows.Count > 0 || dt_repetFromDB.Rows.Count > 0)
                {
                    Main.SetErrorCell(dgError, col_error1);
                    rbSql.Text = "";
                    isCheck    = false;
                    return;
                }
                isCheck = true;
            }
            catch (Exception ex)
            {
                WGMessage.ShowAsterisk("出现未知异常!请检查Excel文件正确性和顺序的正确性!" + ex.ToString());
                return;
            }
        }
예제 #29
0
        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;
        }
예제 #30
0
        private void btnCheck_Click(object sender, EventArgs e)
        {
            try
            {
                if (isCheck)
                {
                    WGMessage.ShowAsterisk("已验证,不用重复验证!");
                    return;
                }
                rbSql.Text = "";
                sqlLs      = new List <string>();

                string       sql        = "";
                List <int[]> col_error2 = new List <int[]>();

                if (_HREMPLOYEEB_excel == null)
                {
                    WGMessage.ShowWarning("请选择职工信息体检要求文件!");
                    return;
                }
                else
                {
                    sql            = "SELECT * FROM HREMPLOYEE";
                    _HREMPLOYEE_DB = FillDatatable(sql, Main.CONN_Public);

                    sql            = @"SELECT * FROM HRPHYSICAL WHERE ST=1";
                    _HRPHYSICAL_DB = FillDatatable(sql, Main.CONN_Public);

                    DataTable dt_error2 = _HREMPLOYEEB_excel.Clone();
                    //重复数据
                    DataTable dt_repet2 = _HREMPLOYEEB_excel.Clone();

                    string Mguid = "";
                    string Fguid = "";
                    #region 子
                    for (int i = 0; i < _HREMPLOYEEB_excel.Rows.Count; i++)
                    {
                        bool isError2 = false;
                        bool isRepet2 = false;

                        DataRow dr_excel = _HREMPLOYEEB_excel.Rows[i];

                        if (string.IsNullOrWhiteSpace(dr_excel["员工工号"].ToString()))
                        {
                            //空
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
                            isError2 = true;
                        }
                        else
                        {
                            DataRow[] drs = _HREMPLOYEE_DB.Select("EMPCODE='" + ReturnString(dr_excel["员工工号"].ToString()) + "'");
                            if (drs.Length > 0)
                            {
                                Mguid = drs[0]["GUID"].ToString();
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
                                isError2 = true;
                            }
                        }

                        int numa = 0;
                        if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) || !int.TryParse(dr_excel["序号"].ToString(), out numa))
                        {
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
                            isError2 = true;
                        }

                        if (string.IsNullOrWhiteSpace(dr_excel["体检要求"].ToString()))
                        {
                            col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
                            isError2 = true;
                        }
                        else
                        {
                            DataRow[] drss = _HRPHYSICAL_DB.Select("NAME='" + ReturnString(dr_excel["体检要求"].ToString()) + "'");
                            if (drss.Length > 0)
                            {
                                Fguid = drss[0]["GUID"].ToString();
                            }
                            else
                            {
                                col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
                                isError2 = true;
                            }
                        }

                        DataRow[] drss1 = _HREMPLOYEEB_excel.Select(string.Format("员工工号='{0}' AND 体检要求='{1}'", ReturnString(dr_excel["员工工号"].ToString()), ReturnString(dr_excel["体检要求"].ToString())));
                        if (drss1.Length > 1)
                        {
                            isRepet2 = true;
                        }

                        if (isError2 || isRepet2)
                        {
                            if (isError2)
                            {
                                dt_error2.Rows.Add(dr_excel.ItemArray);
                            }
                            if (isRepet2)
                            {
                                dt_repet2.Rows.Add(dr_excel.ItemArray);
                            }
                            continue;
                        }

                        string temp = string.Format(@" INSERT INTO HREMPLOYEEB (GUID,PGUID,SNO,FGUID) 
                                                VALUES(NEWID(),'{0}',{1},'{2}')",
                                                    Mguid, numa, Fguid);

                        rbSql.Text += temp + Environment.NewLine;
                        sqlLs.Add(temp);
                    }
                    #endregion
                    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 = "";
                        isCheck    = false;
                        return;
                    }
                    isCheck = true;
                }
            }
            catch (Exception ex)
            {
                WGMessage.ShowAsterisk("出现未知异常!请检查Excel文件正确性!" + ex.ToString());
                return;
            }
        }