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;
}
}
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)
{
}
}
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;
}
}
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;
}
}
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;
}
}
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;
}
}
}
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;
}
}
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;
}
}
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;
}
}
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;
}
}
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;
}
}
private void btnCheck_Click(object sender, EventArgs e)
{
sb.Clear();
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
rbSql.Text = "";
sqlLs = new List <string>();
if (_DMLIBRARY_excel == null)
{
WGMessage.ShowWarning("请选择主文件!");
return;
}
if (_DMLINK_excel == null)
{
WGMessage.ShowWarning("请选择子文件!");
return;
}
//if (DOC == null)
//{
// WGMessage.ShowWarning("请选择含PDF的文件夹!");
// return;
//}
//if (DOC.Length == 0)
//{
// WGMessage.ShowWarning("请选择含PDF的文件夹!");
// return;
//}
//if (txtFileAim.Text == "")
//{
// WGMessage.ShowWarning("请选择目的地文件夹!");
// return;
//}
string sql = @"SELECT * FROM BSWORKSHOP";
_WORKSHOP_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT * FROM BSWORKCENTERS";
_ECINFO_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT * FROM BSWORKCENTER";
_WORKCENTER_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT GUID,EMPCODE FROM HREMPLOYEE";
_HREMPLOYEE_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT GUID,CODE FROM BSDEPT";
_DEPT_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT * FROM DMTYPE";
_DMTYPE_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT * FROM DMLIBRARY
LEFT JOIN DMVERSION ON DMVERSION.PGUID = DMLIBRARY.GUID";
_DMLIBRARY_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT BSPRODUCT.GUID,[BSPRODUCT].[CODE],[dbo].[BSPRODUCTVER].[VER] FROM [dbo].[BSPRODUCT]
LEFT JOIN BSPRODUCTVER ON BSPRODUCTVER.[PGUID] = [BSPRODUCT].[GUID]";
_BSPRODUCT_DB = FillDatatablde(sql, Main.CONN_Public);
//错误
List <int[]> col_error1 = new List <int[]>();
List <int[]> col_error2 = new List <int[]>();
DataTable dt_error1 = _DMLIBRARY_excel.Clone();
//重复数据
DataTable dt_repet1 = _DMLIBRARY_excel.Clone();
DataTable dt_repet1_excel = _DMLIBRARY_excel.Clone();
DataTable dt_error2 = _DMLINK_excel.Clone();
//重复数据
DataTable dt_repet2 = _DMLINK_excel.Clone();
//需要保持的数据
DataTable dt = _DMLIBRARY_excel.Clone();
dt.Columns.Add();
Dictionary <string, string> doclist = new Dictionary <string, string>();
dtout = new DataTable();
dtout.Columns.Add("文件名");
dtout.Columns.Add("文件KEY");
dtout.Columns.Add("受控类型");
dtout.Columns.Add("有效天数");
dtout.Columns.Add("文档编号");
#region 主子
for (int i = 0; i < _DMLIBRARY_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _DMLIBRARY_excel.Rows[i];
doclist.Add(Guid.NewGuid().ToString(), dr_excel["文档名"].ToString());
if (string.IsNullOrWhiteSpace(dr_excel["文档编号"].ToString()) ||
_DMLIBRARY_excel.Select("文档编号='" + WGHelper.ReturnString(dr_excel["文档编号"].ToString()) + "'").Length > 1)
{
//空、重复
isRepet_excel = true;
}
if (_DMLIBRARY_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["文档编号"].ToString()) + "'").Length > 0)
{
// 存在
isRepet = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["文档名"].ToString()))
{
//空
col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
isError = true;
}
DataRow[] drs_DMTYPE_pos = _DMTYPE_DB.Select("CODE='"
+ WGHelper.ReturnString(dr_excel["文档类别编号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["文档类别编号"].ToString()) ||
drs_DMTYPE_pos.Length == 0)
{
//空、不存在
col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
isError = true;
}
//DataRow[] drs_DMCATALOG_pos = _DMCATALOG_DB.Select("NAME='"
// + WGHelper.ReturnString(dr_excel["文档目录名称"].ToString()) + "'");
//if (string.IsNullOrWhiteSpace(dr_excel["文档目录名称"].ToString())
// || drs_DMCATALOG_pos.Length == 0)
//{
// //空、不存在
// col_error1.Add(new int[] { dt_error1.Rows.Count, 3 });
// isError = true;
//}
DataRow[] drs_HREMPLOYEE_pos = _HREMPLOYEE_DB.Select("EMPCODE ='"
+ WGHelper.ReturnString(dr_excel["上传人工号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["上传人工号"].ToString()) ||
drs_HREMPLOYEE_pos.Length == 0)
{
//空、不存在
col_error1.Add(new int[] { dt_error1.Rows.Count, 4 });
isError = true;
}
DataRow[] drs_DEPT_pos = _DEPT_DB.Select("CODE ='"
+ WGHelper.ReturnString(dr_excel["上传人所在部门编号"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["上传人所在部门编号"].ToString() + "'") ||
drs_DEPT_pos.Length == 0)
{
//空、不存在
col_error1.Add(new int[] { dt_error1.Rows.Count, 5 });
isError = true;
}
DateTime ADATE = new DateTime();
if (string.IsNullOrWhiteSpace(dr_excel["上传时间"].ToString()) ||
!DateTime.TryParse(dr_excel["上传时间"].ToString(), out ADATE))
{
//空、类型不符
col_error1.Add(new int[] { dt_error1.Rows.Count, 6 });
isError = true;
}
if (dr_excel["受控类型"].ToString() != "1" && dr_excel["受控类型"].ToString() != "2")
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 7 });
isError = true;
}
DateTime VDATE = new DateTime();
int day = 0;
if (dr_excel["受控类型"].ToString() == "2")
{
if (string.IsNullOrWhiteSpace(dr_excel["有效期"].ToString()) ||
!DateTime.TryParse(dr_excel["有效期"].ToString(), out VDATE))
{
//空、类型不符
col_error1.Add(new int[] { dt_error1.Rows.Count, 9 });
isError = true;
}
if (!int.TryParse(dr_excel["有效天数"].ToString(), out day))
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 8 });
isError = true;
}
}
else if (dr_excel["受控类型"].ToString() == "1")
{
dr_excel["有效天数"] = 0;
dr_excel["有效期"] = "";
}
if (string.IsNullOrWhiteSpace(dr_excel["正式版本号"].ToString()))
{
//空
col_error1.Add(new int[] { dt_error1.Rows.Count, 10 });
isError = true;
}
if (dr_excel["文档来源"].ToString() != "1" && dr_excel["文档来源"].ToString() != "3")
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 11 });
isError = true;
}
DateTime ndTime = DateTime.Now;
DateTime cdTime = ndTime;
DateTime.TryParse(dr_excel["创建时间"].ToString(), out ndTime);
DateTime.TryParse(dr_excel["修改时间"].ToString(), out cdTime);
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error1.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet1.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet1_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
//bool ishavedoc = false;
//FileInfo dr_info = new FileInfo (DOC[0]);
//for (int j = 0; j < DOC.Length; j++)
//{
// FileInfo info = new FileInfo(DOC[j]);
// if (info.Name.Contains(".pdf") && info.Name == dr_excel["文档名"].ToString())
// {
// ishavedoc = true;
// dr_info = info;
// break;
// }
//}
//if (ishavedoc == false)
//{
// WGMessage.ShowAsterisk("选择文件夹中不包含"+dr_excel["文档名"]+"!");
// return;
//}
string DMLIBRARY_GUID = Guid.NewGuid().ToString();
string DMVERSION_GUID = Guid.NewGuid().ToString();
_DMLIBRARY_excel.Rows[i]["MGUID"] = DMLIBRARY_GUID;
_DMLIBRARY_excel.Rows[i]["LGUID"] = DMVERSION_GUID;
try
{
string vdate = "";
if (dr_excel["受控类型"].ToString() == "1")
{
vdate = "null";
}
else
{
vdate = "'" + dr_excel["有效期"] + "'";
}
string temp = @"INSERT INTO [DMLIBRARY]
([GUID],[FGUID],[CODE]
,[NAME],[KEYWORD],[AGUID]
,[DEPTRI],[ADATE],[VDATE],[VER],[ST]
,[NOTE],[CC],[ND],[CD],[CTYPE]
,[VDAYS],[FTYPE])
VALUES
(" + Main.SetDBValue(DMLIBRARY_GUID) + "," + Main.SetDBValue(drs_DMTYPE_pos[0]["GUID"]) + "," + Main.SetDBValue(dr_excel["文档编号"].ToString()) + @"
," + Main.SetDBValue(dr_excel["文档名"].ToString()) + "," + Main.SetDBValue(dr_excel["关键字"]) + "," + Main.SetDBValue(drs_HREMPLOYEE_pos[0]["GUID"]) + @"
," + Main.SetDBValue(Guid.NewGuid().ToString() /*drs_DEPT_pos[0]["GUID"]*/) + "," + Main.SetDBValue(dr_excel["上传时间"]) + "," + (vdate) + "," + Main.SetDBValue(dr_excel["正式版本号"]) + ",2" + @"
," + Main.SetDBValue(dr_excel["备注"].ToString()) + ",''," + Main.SetDBValue(ndTime) + "," + Main.SetDBValue(cdTime) + ",'" + dr_excel["受控类型"].ToString() + "'" + "" +
"," + day + ",'" + dr_excel["文档来源"].ToString() + "');";
string FKEY = Guid.NewGuid().ToString();
dr_excel["FKEY"] = FKEY;
temp += @" INSERT INTO DMVERSION (GUID,PGUID,VER,FKEY,AGUID,ADATE,ST) VALUES
('" + DMVERSION_GUID + "','" + DMLIBRARY_GUID + "'," + Main.SetDBValue(dr_excel["正式版本号"]) + ",'" + FKEY + "'," + Main.SetDBValue(drs_HREMPLOYEE_pos[0]["GUID"]) + "," + Main.SetDBValue(dr_excel["上传时间"]) + ",2);";
sb.Append(temp + Environment.NewLine);
sqlLs.Add(temp);
DataRow drnew = dtout.NewRow();
drnew["文件名"] = dr_excel["文档名"].ToString();
drnew["文件KEY"] = FKEY;
drnew["受控类型"] = dr_excel["受控类型"].ToString();
drnew["有效天数"] = day;
drnew["文档编号"] = dr_excel["文档编号"].ToString();
dtout.Rows.Add(drnew);
}
catch
{ }
}
#endregion
#region 孙孙
for (int i = 0; i < _DMLINK_excel.Rows.Count; i++)
{
isError = false;
isRepet = false;
DataRow dr_excel = _DMLINK_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["文档编号"].ToString()))
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
isError = true;
}
if (_DMLINK_excel.Select("文档编号='" + WGHelper.ReturnString(dr_excel["文档编号"].ToString()) + "' AND 正式版本号 = '" + WGHelper.ReturnString(dr_excel["正式版本号"].ToString()) + "' and 车间编号 = '" + WGHelper.ReturnString(dr_excel["车间编号"].ToString()) + "' AND 工作中心编号 = '" + WGHelper.ReturnString(dr_excel["工作中心编号"].ToString()) + "' AND 工位编号 = '" + WGHelper.ReturnString(dr_excel["工位编号"].ToString()) + "'").Length > 1)
{
// 存在
isRepet = true;
}
if (_DMLINK_excel.Select("文档编号='" + WGHelper.ReturnString(dr_excel["文档编号"].ToString()) + "' AND 正式版本号 = '" + WGHelper.ReturnString(dr_excel["正式版本号"].ToString()) + "' AND 产品编号 = '" + WGHelper.ReturnString(dr_excel["产品编号"].ToString()) + "' AND 产品版本 = '" + WGHelper.ReturnString(dr_excel["产品版本"].ToString()) + "' AND 工序码 = '" + WGHelper.ReturnString(dr_excel["工序码"].ToString()) + "'").Length > 1)
{
isRepet = true;
}
/* DataRow[] drss = _DMLINK_excel.Select("文档编号 = '"+dr_excel["文档编号"].ToString()+ "' AND 正式版本号 = '"+ dr_excel["正式版本号"].ToString() + "'");
*
* if (drss.Length > 1)
* {
* col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
* col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
* isError = true;
* }*/
if (string.IsNullOrWhiteSpace(dr_excel["正式版本号"].ToString()))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
isError = true;
}
int numa;
if (dr_excel["类型"].ToString() != "0" && dr_excel["类型"].ToString() != "1")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
isError = true;
}
DataRow[] drs_WORKCENTER_pos = new DataRow[0];
DataRow[] drs_WORKSHOP_pos = new DataRow[0];
if (dr_excel["类型"].ToString() == "0")
{
if (dr_excel["序号"].ToString() == "" && dr_excel["车间编号"].ToString() != "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
isError = true;
}
else if (dr_excel["序号"].ToString() == "" && dr_excel["车间编号"].ToString() == "")
{
}
else if (!int.TryParse(dr_excel["序号"].ToString(), out numa))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
isError = true;
}
drs_WORKSHOP_pos = _WORKSHOP_DB.Select("CODE='"
+
WGHelper.ReturnString(dr_excel["车间编号"].ToString()) +
"'");
if (dr_excel["车间编号"].ToString() == "" && dr_excel["工作中心编号"].ToString() != "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 4 });
isError = true;
}
else if (dr_excel["车间编号"].ToString() == "" && dr_excel["工作中心编号"].ToString() == "")
{
}
else if (drs_WORKSHOP_pos.Length == 0)
{
//空、不存在
col_error2.Add(new int[] { dt_error2.Rows.Count, 4 });
isError = true;
}
drs_WORKCENTER_pos = _WORKCENTER_DB.Select("CODE ='"
+
WGHelper.ReturnString(
dr_excel["工作中心编号"].ToString()) + "'");
if (dr_excel["工作中心编号"].ToString() == "" && dr_excel["工位编号"].ToString() != "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 5 });
isError = true;
}
else if (dr_excel["工作中心编号"].ToString() == "" && dr_excel["工位编号"].ToString() == "")
{
}
else if (drs_WORKCENTER_pos.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 5 });
isError = true;
}
if (dr_excel["工位编号"].ToString() != "" && drs_WORKCENTER_pos.Length == 0)
{
//空、不存在
col_error2.Add(new int[] { dt_error2.Rows.Count, 6 });
isError = true;
}
}
string[] ECINFO = dr_excel["工位编号"].ToString().Split(',');
string[] ECINFOGUID = new string[ECINFO.Length];
string[] ECINFONAME = new string[ECINFO.Length];
int index = 0;
if (dr_excel["工位编号"].ToString() == "")
{
}
else
{
foreach (var ei in ECINFO)
{
if (drs_WORKCENTER_pos.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 6 });
isError = true;
break;
}
DataRow[] drs_ECINFO_pos = _ECINFO_DB.Select("CODE ='"
+ WGHelper.ReturnString(ECINFO[index].ToString()) + "' AND PGUID = '" + drs_WORKCENTER_pos [0]["GUID"].ToString() + "'");
if (drs_ECINFO_pos.Length == 0)
{
//空、不存在
col_error2.Add(new int[] { dt_error2.Rows.Count, 6 });
isError = true;
break;
}
ECINFOGUID[index] = drs_ECINFO_pos[0]["GUID"].ToString();
ECINFONAME[index] = drs_ECINFO_pos[0]["NAME"].ToString();
index++;
}
}
DataRow[] drs_DMLINK = new DataRow[0];
if (dr_excel["类型"].ToString() == "1")
{
if (dr_excel["产品编号"].ToString() == "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 7 });
isError = true;
}
if (dr_excel["产品版本"].ToString() == "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 8 });
isError = true;
}
if (dr_excel["工序码"].ToString() == "")
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 9 });
isError = true;
}
drs_DMLINK = _BSPRODUCT_DB.Select("CODE = '" + WGHelper.ReturnString(dr_excel["产品编号"].ToString()) + "' AND VER = '" + dr_excel["产品版本"].ToString() + "' ");
if (drs_DMLINK.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 7 });
col_error2.Add(new int[] { dt_error2.Rows.Count, 8 });
isError = true;
}
}
DataRow[] drs = _DMLIBRARY_excel.Select("文档编号 = '" + dr_excel["文档编号"].ToString() + "' AND 正式版本号 = '" + dr_excel["正式版本号"].ToString() + "' ");
if (drs.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
isError = true;
}
if (isError || isRepet)
{
if (isError)
{
dt_error2.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet2.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string DMLINK_GUID = Guid.NewGuid().ToString();
string AGUID = Guid.NewGuid().ToString();
string BGUID = Guid.NewGuid().ToString();
try
{
AGUID = drs_WORKSHOP_pos[0]["GUID"].ToString();
}
catch
{
}
try
{
BGUID = drs_WORKCENTER_pos[0]["GUID"].ToString();
}
catch
{
}
if (dr_excel["序号"].ToString() != "")
{
string temp = "";
if (dr_excel["类型"].ToString() == "0")
{
temp = @" INSERT INTO DMLINK (GUID,PGUID,SNO,AGUID,BGUID,CTYPE) VALUES
('" + DMLINK_GUID + "','" + drs[0]["LGUID"].ToString() + "','" + dr_excel["序号"].ToString() + "','" + AGUID +
"','" + BGUID + "','" + dr_excel["类型"] + "');";
}
else if (dr_excel["类型"].ToString() == "1")
{
temp = @" INSERT INTO DMLINK (GUID,PGUID,SNO,CTYPE,CGUID,PVER,CODE) VALUES
('" + DMLINK_GUID + "','" + drs[0]["LGUID"].ToString() + "','" + dr_excel["序号"].ToString() + "','" + dr_excel["类型"] + "','" + drs_DMLINK[0]["GUID"] + "','" +
dr_excel["产品版本"] + "','" + WGHelper.ReturnString(dr_excel["工序码"].ToString()) + "');";
}
for (int j = 0; j < ECINFO.Length; j++)
{
if (ECINFOGUID[j] == null)
{
continue;
}
temp += @" INSERT INTO DMLINKS (GUID,PGUID,SNO,FGUID) VALUES
('" + Guid.NewGuid().ToString() + "','" + DMLINK_GUID + "','" + (j + 1) + "','" + ECINFOGUID[j] + "')";
}
sb.Append(temp + Environment.NewLine);
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
}
#endregion
#region （BSFILEUPLOAD）
for (int i = 0; i < _DMLIBRARY_excel.Rows.Count; i++)
{
string temp = @"INSERT INTO BSFILEUPLOAD (GUID,DOMAIN,TBDM,PGUID,PKEY,FNAME,SAVENAME,EXTNAME,ST)
VALUES ('" + Guid.NewGuid().ToString() + "','DMLIBRARY','DMVERSION','" + _DMLIBRARY_excel.Rows[i]["LGUID"].ToString() + "','" + _DMLIBRARY_excel.Rows[i]["FKEY"].ToString() + "','" + _DMLIBRARY_excel.Rows[i]["文档名"] + "','" + _DMLIBRARY_excel.Rows[i]["FKEY"].ToString() + ".pdf','.pdf',1);";
sb.Append(temp + Environment.NewLine);
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
#endregion
dt_error1.Columns.Remove("MGUID");
dt_error1.Columns.Remove("LGUID");
dt_error1.Columns.Remove("FKEY");
dt_error1.Columns.Remove("FSIZE");
dt_repet1.Columns.Remove("MGUID");
dt_repet1.Columns.Remove("LGUID");
dt_repet1.Columns.Remove("FKEY");
dt_repet1.Columns.Remove("FSIZE");
//dt_error2.Columns.Remove("F7");
//dt_error2.Columns.Remove("F8");
//dt_error2.Columns.Remove("F9");
//dt_error2.Columns.Remove("F10");
//dt_repet2.Columns.Remove("F7");
//dt_repet2.Columns.Remove("F8");
//dt_repet2.Columns.Remove("F9");
//dt_repet2.Columns.Remove("F10");
dgError1.DataSource = dt_error1; dgError2.DataSource = dt_error2;
dgRepet1_excel.DataSource = dt_repet1_excel; dgRepet2.DataSource = dt_repet2;
dgRepet1.DataSource = dt_repet1;
if (dt_error1.Rows.Count > 0 || dt_error2.Rows.Count > 0 || dt_repet1_excel.Rows.Count > 0 || dt_repet1.Rows.Count > 0 || dt_repet2.Rows.Count > 0)
{
Main.SetErrorCell(dgError1, col_error1);
Main.SetErrorCell(dgError2, col_error2);
rbSql.Text = "";
isCheck = false;
return;
}
this.rbSql.Text = sb.ToString();
isCheck = true;
}
private void btnCheck_Click(object sender, EventArgs e)
{
if (_MRBOM_excel == null)
{
WGMessage.ShowWarning("请选择[模具BOM]文件!");
return;
}
if (_MRBOMSUB_excel == null)
{
WGMessage.ShowWarning("请选择[模具BOM明细]文件!");
return;
}
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
//加载对应厂部的部门-岗位
string sql = @"SELECT GUID,[EMPCODE],[EMPNAME] FROM [dbo].[HREMPLOYEE]";
_HREMPLOYEE_DB = FillDatatablde(sql, Main.CONN_Public);
//加载物料基本信息
sql = @"select GUID,CODE,NAME from BSPRODUCT WHERE [CTYPE] IN (2)";
_MROPRODUCT_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @" SELECT [MRBOM].[CODE] 单号,[BSPRODUCT].[CODE] 物料 FROM [MRBOM]
LEFT JOIN [dbo].[BSPRODUCT] ON [BSPRODUCT].[GUID] = [dbo].[MRBOM].[BGUID]" ;
_MRBOM_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @" SELECT [MRBOM].CODE 单号,[BSPRODUCT].[CODE] 物料 FROM MRBOMSUB
LEFT JOIN [dbo].[MRBOM] ON [MRBOM].[GUID] = [MRBOMSUB].[PGUID]
LEFT JOIN [dbo].[BSPRODUCT] ON [BSPRODUCT].[GUID] = [dbo].MRBOMSUB.WGUID" ;
_MRBOMSUB_DB = FillDatatablde(sql, Main.CONN_Public);
#region 模具BOM 验证
DataTable dt_error = _MRBOM_excel.Clone();
DataTable dt_repet_excel = _MRBOM_excel.Clone();
//错误
List <int[]> col_error = new List <int[]>();
//重复数据
DataTable dt_repet = _MRBOM_excel.Clone();
Dictionary <string, Guid> newMRO = new Dictionary <string, Guid>();
for (int i = 0; i < _MRBOM_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _MRBOM_excel.Rows[i];
//DataRow[] drs_ectype = _ECType_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["MRO类别"].ToString()) + "'");
//if (string.IsNullOrWhiteSpace(dr_excel["MRO类别"].ToString()) || drs_ectype.Length == 0)
//{
// //空、不存在
// col_error.Add(new int[] { dt_error.Rows.Count, 3 });
// isError = true;
//}
if (string.IsNullOrWhiteSpace(dr_excel["单号"].ToString()))
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
DataRow[] drs_HREMPLOYEE1 = _HREMPLOYEE_DB.Select("EMPCODE='" + WGHelper.ReturnString(dr_excel["录入人工号"].ToString()) + "'");
if (drs_HREMPLOYEE1.Length == 0)
{
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
DateTime dtTime1 = new DateTime();
if (!DateTime.TryParse(dr_excel["录入日期"].ToString(), out dtTime1))
{
col_error.Add(new int[] { dt_error.Rows.Count, 2 });
isError = true;
}
DataRow[] drs_mroproduct = _MROPRODUCT_DB.Select("CODE = '" + WGHelper.ReturnString(dr_excel["模具编号"].ToString()) + "'");
if (drs_mroproduct.Length == 0)
{
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (!STs.ContainsKey(dr_excel["单据状态"].ToString()))
{
col_error.Add(new int[] { dt_error.Rows.Count, 4 });
isError = true;
}
DataRow[] drs_HREMPLOYEE2 = _HREMPLOYEE_DB.Select("EMPCODE='" + WGHelper.ReturnString(dr_excel["审核人工号"].ToString()) + "'");
if (drs_HREMPLOYEE2.Length == 0)
{
col_error.Add(new int[] { dt_error.Rows.Count, 5 });
isError = true;
}
DateTime dtTime2 = new DateTime();
if (!DateTime.TryParse(dr_excel["审核日期"].ToString(), out dtTime2))
{
col_error.Add(new int[] { dt_error.Rows.Count, 6 });
isError = true;
}
DataRow[] drs_repet = _MRBOM_DB.Select("单号 = '" + WGHelper.ReturnString(dr_excel["单号"].ToString()) + "'");
if (drs_repet.Length > 0)
{
isRepet = true;
}
drs_repet = _MRBOM_DB.Select("物料 = '" + WGHelper.ReturnString(dr_excel["模具编号"].ToString()) + "'");
if (drs_repet.Length > 0)
{
isRepet = true;
}
DataRow[] drs_repet_excel = _MRBOM_excel.Select("单号 = '" + WGHelper.ReturnString(dr_excel["单号"].ToString()) + "'");
if (drs_repet_excel.Length > 1)
{
isRepet_excel = true;
}
drs_repet_excel = _MRBOM_excel.Select("模具编号 = '" + WGHelper.ReturnString(dr_excel["模具编号"].ToString()) + "'");
if (drs_repet_excel.Length > 1)
{
isRepet_excel = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
Guid n = Guid.NewGuid();
string temp = string.Format(@"INSERT INTO [dbo].[MRBOM]
( [GUID] ,
[CODE] ,
[AGUID] ,
[INTIME] ,
[BGUID] ,
[ST] ,
[AUDITGUID] ,
[ADATE] ,
[NOTE] ,
[ND] ,
[CD]
)
VALUES ( '{0}' , -- GUID - uniqueidentifier
'{1}' , -- CODE - nvarchar(50)
'{2}' , -- AGUID - uniqueidentifier
'{3}' , -- INTIME - datetime
'{4}' , -- BGUID - uniqueidentifier
'{5}' , -- ST - bit
'{6}' , -- AUDITGUID - uniqueidentifier
'{7}' , -- ADATE - datetime
'{8}' , -- NOTE - nvarchar(200)
GETDATE() , -- ND - datetime
GETDATE() -- CD - datetime
)"
, n
, WGHelper.ReturnString(dr_excel["单号"].ToString())
, drs_HREMPLOYEE1[0]["GUID"].ToString()
, dtTime1
, drs_mroproduct[0]["GUID"].ToString()
, STs[dr_excel["单据状态"].ToString()]
, drs_HREMPLOYEE2[0]["GUID"].ToString()
, dtTime2
, WGHelper.ReturnString(dr_excel["备注"].ToString())
);
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
newMRO.Add(dr_excel["单号"].ToString(), n);
}
dgError.DataSource = dt_error;
dgRepet.DataSource = dt_repet;
dgRepet_excel.DataSource = dt_repet_excel;
if (dt_error.Rows.Count > 0 || dt_repet.Rows.Count > 0 || dt_repet_excel.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
#endregion
#region 模具BOM明细 验证
DataTable dt_error2 = _MRBOMSUB_excel.Clone();
DataTable dt_repet2_excel = _MRBOMSUB_excel.Clone();
//错误
List <int[]> col_error2 = new List <int[]>();
//重复数据
DataTable dt_repet2 = _MRBOMSUB_excel.Clone();
for (int i = 0; i < _MRBOMSUB_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _MRBOMSUB_excel.Rows[i];
if (!newMRO.ContainsKey(dr_excel["单号"].ToString()))
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
isError = true;
}
int SNO = 0;
if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) ||
!int.TryParse(dr_excel["序号"].ToString(), out SNO))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 1 });
isError = true;
}
DataRow[] drs_mroproduct = _MROPRODUCT_DB.Select("CODE = '" + WGHelper.ReturnString(dr_excel["模具编号"].ToString()) + "'");
if (drs_mroproduct.Length == 0)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 2 });
isError = true;
}
int num = 0;
if (!int.TryParse(dr_excel["数量"].ToString(), out num))
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
isError = true;
}
DataRow[] drs_repet = _MRBOMSUB_DB.Select("单号 = '" + WGHelper.ReturnString(dr_excel["单号"].ToString()) + "'");
if (drs_repet.Length > 0)
{
isRepet = true;
}
DataRow[] drs_repet_excel = _MRBOMSUB_excel.Select("单号 = '" + WGHelper.ReturnString(dr_excel["单号"].ToString()) + "' AND 模具编号 = '" + WGHelper.ReturnString(dr_excel["模具编号"].ToString()) + "'");
if (drs_repet_excel.Length > 1)
{
isRepet_excel = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error2.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet2.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet2_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = string.Format(@"
INSERT INTO [dbo].[MRBOMSUB]
( [GUID] ,
[SNO] ,
[PGUID] ,
[WGUID] ,
[QTY]
)
VALUES ( NEWID() , -- GUID - uniqueidentifier
'{0}' , -- SNO - int
'{1}' , -- PGUID - uniqueidentifier
'{2}' , -- WGUID - uniqueidentifier
{3} -- QTY - int
)"
, dr_excel["序号"].ToString()
, newMRO[dr_excel["单号"].ToString()]
, drs_mroproduct[0]["GUID"].ToString()
, num);
//rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError2.DataSource = dt_error2;
dgRepet2.DataSource = dt_repet2;
dgRepet_excel2.DataSource = dt_repet2_excel;
if (dt_error2.Rows.Count > 0 || dt_repet2.Rows.Count > 0 || dt_repet2_excel.Rows.Count > 0)
{
Main.SetErrorCell(dgError2, col_error2);
rbSql.Text = "";
return;
}
#endregion
isCheck = true;
StringBuilder last = new StringBuilder();
foreach (string sql1 in sqlLs)
{
last.Append(sql1 + Environment.NewLine);
}
rbSql.Text = last.ToString();
}
private void btnCheck_Click(object sender, EventArgs e)
{
if (_BSPRODUCT_excel == null)
{
WGMessage.ShowWarning("请选择[产品基本信息]文件!");
return;
}
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
string sql = "SELECT GUID,CODE,NAME,CTYPE FROM BSPRODTYPE";
_BSPRODTYPE_DB = FillDatatablde(sql, Main.CONN_Public);
sql = "SELECT GUID,NAME FROM BSUNIT WHERE ST = 1";
_BSUNIT_DB = FillDatatablde(sql, Main.CONN_Public);
sql = "SELECT GUID,CODE,NAME FROM BSPRODUCT";
_BSPRODUCT_DB = FillDatatablde(sql, Main.CONN_Public);
//错误
List <int[]> col_error = new List <int[]>();
//重复数据
DataTable dt_repet = _BSPRODUCT_excel.Clone();
DataTable dt_error = _BSPRODUCT_excel.Clone();
DataTable dt_repet_excel = _BSPRODUCT_excel.Clone();
for (int i = 0; i < _BSPRODUCT_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _BSPRODUCT_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["产品编号"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["产品编号"].ToString()) ||
_BSPRODUCT_excel.Select("产品编号='" + WGHelper.ReturnString(dr_excel["产品编号"].ToString()) + "'").Length > 1)
{
//空、重复
isRepet_excel = true;
}
if (_BSPRODUCT_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["产品编号"].ToString()) + "'").Length > 0)
{
// 存在
isRepet = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["产品名称"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["产品名称"].ToString()) ||
_BSPRODUCT_excel.Select("产品名称='" + WGHelper.ReturnString(dr_excel["产品名称"].ToString()) + "'").Length > 1)
{
//空、重复
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
else
if (_BSPRODUCT_DB.Select("NAME='" + WGHelper.ReturnString(dr_excel["产品名称"].ToString()) + "'").Length > 0)
{
// 存在
isRepet = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["产品类别编号"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
DataRow[] drs_type = _BSPRODTYPE_DB.Select("CODE = '" + dr_excel["产品类别编号"] + "'");
if (drs_type.Length == 0)
{
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["单位名称"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 4 });
isError = true;
}
DataRow[] drs_unit = _BSUNIT_DB.Select("NAME = '" + dr_excel["单位名称"] + "'");
if (drs_unit.Length == 0)
{
col_error.Add(new int[] { dt_error.Rows.Count, 4 });
isError = true;
}
int NEEDREADY = 0;
int NEEDTRACE = 0;
int ISSCHEDULING = 0;
int ISDBBARCODE = 0;
decimal SAFETYSTOCK = 0;
if (string.IsNullOrWhiteSpace(dr_excel["是否需要备料"].ToString()) || !int.TryParse(dr_excel["是否需要备料"].ToString(), out NEEDREADY))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["安全库存量"].ToString()) || !decimal.TryParse(dr_excel["安全库存量"].ToString(), out SAFETYSTOCK))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["是否批次管理"].ToString()) || !int.TryParse(dr_excel["是否批次管理"].ToString(), out ISDBBARCODE))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["是否追溯"].ToString()) || !int.TryParse(dr_excel["是否追溯"].ToString(), out NEEDTRACE))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["是否排产"].ToString()) || !int.TryParse(dr_excel["是否排产"].ToString(), out ISSCHEDULING))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
decimal bzknum = 0;
decimal dbznum = 0;
decimal xbznum = 0;
if (string.IsNullOrWhiteSpace(dr_excel["标准框数量"].ToString()) || !decimal.TryParse(dr_excel["标准框数量"].ToString(), out bzknum))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["小包装数量"].ToString()) || !decimal.TryParse(dr_excel["小包装数量"].ToString(), out dbznum))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["大包装数量"].ToString()) || !decimal.TryParse(dr_excel["大包装数量"].ToString(), out xbznum))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string NewGUID = Guid.NewGuid().ToString();
string temp = string.Format(@"INSERT INTO BSPRODUCT
(GUID,CODE,NAME,AGUID,ANAME,ACODE,CTYPE,SPEC,BGUID,BNAME,LPQTY,SPQTY,ANUM,NEEDREADY,NEEDTRACE,ISSCHEDULING,ISDBBARCODE,SAFETYSTOCK,NOTE,ST,ND,CD)
VALUES
('{0}',{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},GETDATE(),GETDATE())",
NewGUID, Main.SetDBValue(dr_excel["产品编号"].ToString()), Main.SetDBValue(dr_excel["产品名称"].ToString()), Main.SetDBValue(drs_type[0]["GUID"].ToString()),
Main.SetDBValue(dr_excel["产品类别编号"].ToString()), Main.SetDBValue(dr_excel["产品类别名称"].ToString()), Convert.ToInt32(drs_type[0]["CTYPE"].ToString()),
Main.SetDBValue(dr_excel["产品描述"].ToString()), Main.SetDBValue(drs_unit[0]["GUID"].ToString()), Main.SetDBValue(dr_excel["单位名称"].ToString()),
dbznum, xbznum, bzknum, NEEDREADY, NEEDTRACE, ISSCHEDULING, ISDBBARCODE, SAFETYSTOCK, "NULL", 0);
temp += string.Format(@"INSERT INTO BSPRODUCTVER
(GUID,PGUID,PCODE,PNAME,VER,ST,STATE,VDATE)
VALUES
(NEWID(),'{0}',{1},{2},'{3}',0, 1 ,GETDATE())", NewGUID, Main.SetDBValue(dr_excel["产品编号"].ToString()),
Main.SetDBValue(dr_excel["产品名称"].ToString()), "A.0", 0, 0);
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError.DataSource = dt_error;
dgRepet.DataSource = dt_repet;
dgRepet_Excel.DataSource = dt_repet_excel;
if (dt_error.Rows.Count > 0 || dt_repet.Rows.Count > 0 || dt_repet_excel.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
isCheck = true;
}
private void btnCheck_Click(object sender, EventArgs e)
{
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;
}
}
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;
}
}
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;
}
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;
}
}
private void btnCheck_Click(object sender, EventArgs e)
{
if (_ECInfo_excel == null)
{
WGMessage.ShowWarning("请选择[设备信息]文件!");
return;
}
if (_ECInfoD_excel == null)
{
WGMessage.ShowWarning("请选择[物料校验项]文件!");
return;
}
if (_ECInfoE_excel == null)
{
WGMessage.ShowWarning("请选择[物料保养项]文件!");
return;
}
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
//加载对应厂部的部门-岗位
string sql = @"SELECT t1.[GUID] BSDEPTPOS_GUID,t2.GUID BSDEPT_GUID,t2.CODE BSDEPT_CODE,t2.NAME BSDEPT_NAME,t3.CODE BSPOSITION_CODE,t3.NAME BSPOSITION_NAME
FROM [BSDEPTPOS] t1
left join BSDEPT t2 on t1.PGUID=t2.GUID
left join BSPOSITION t3 on t1.FGUID=t3.GUID";
_hrDeptPos_DB = FillDatatablde(sql, Main.CONN_Public);
//加载物料基本信息
sql = @"select GUID,CODE,NAME from BSPRODUCT WHERE [CTYPE] IN (2,3,4,5,6)";
_ECInfo_DB = FillDatatablde(sql, Main.CONN_Public);
//加载物料类别
sql = @"select GUID,CODE,NAME,CTYPE from [BSPRODTYPE] WHERE CTYPE IN (2,3,4,5,6)";
_ECType_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"select GUID,CODE,NAME FROM BSPRODUCT WHERE CTYPE IN (2,3,4,5,6)";
_BSPRODUCT_DB = FillDatatablde(sql, Main.CONN_Public);
//错误
List <int[]> col_error = new List <int[]>();
//重复数据
DataTable dt_repet = _ECInfo_excel.Clone();
#region 物料信息 验证
DataTable dt_error = _ECInfo_excel.Clone();
DataTable dt_repet_excel = _ECInfo_excel.Clone();
DataTable dt_repet_excel1 = _ECInfoD_excel.Clone();
DataTable dt_repet_excel2 = _ECInfoE_excel.Clone();
//存放要保存的设备厂内编号对应的guid
Dictionary <string, Guid> newEC = new Dictionary <string, Guid>();
Dictionary <string, Guid> newDJName = new Dictionary <string, Guid>();
for (int i = 0; i < _ECInfo_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _ECInfo_excel.Rows[i];
DataRow[] drs_ectype = _ECType_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["MRO类别"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["MRO类别"].ToString()) || drs_ectype.Length == 0)
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 3 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["MRO编号"].ToString()))
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
DataRow[] drs_mrcode = _ECInfo_DB.Select("CODE='" + WGHelper.ReturnString(dr_excel["MRO编号"].ToString()) + "'");
if (drs_mrcode.Length > 0)
{
isRepet = true;
}
DataRow[] drs_repet_excel = _ECInfo_excel.Select("MRO编号 = '" + WGHelper.ReturnString(dr_excel["MRO编号"].ToString()) + "'");
if (drs_repet_excel.Length > 1)
{
isRepet_excel = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["MRO名称"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
if (!string.IsNullOrWhiteSpace(dr_excel["安全库存管理"].ToString()) &&
dr_excel["安全库存管理"].ToString().ToLower() == "true" && string.IsNullOrWhiteSpace(dr_excel["安全库存"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 2 });
isError = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet)
{
dt_repet.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel.Rows.Add(dr_excel.ItemArray);
}
continue;
}
Guid n = Guid.NewGuid();
string temp = @"INSERT INTO [BSPRODUCT]
([GUID],[CODE],[NAME],[PGUID],[CTYPE]
,[ISMAINTMGR],[ISCHECKMGR],[ISSTORAGEMGR],[STORAGENUM],[ISSNMGR],[ISSEQUESTMGR],[ND])
VALUES
(" + Main.SetDBValue(n) + "," + Main.SetDBValue(dr_excel["MRO编号"]) + "," + Main.SetDBValue(dr_excel["MRO名称"]) + "," + Main.SetDBValue(drs_ectype[0]["GUID"]) + @"
," + Main.SetDBValue(drs_ectype[0]["CTYPE"]) + "," + Main.SetDBValue(dr_excel["保养管理"]) + "," + Main.SetDBValue(dr_excel["校验管理"]) + "," + Main.SetDBValue(dr_excel["安全库存管理"]) + "," + Main.SetDBValue(dr_excel["安全库存"]) + "," +
"" + Main.SetDBValue(dr_excel["标识管理"]) + " ," + Main.SetDBValue(dr_excel["封存管理"]) + "," + Main.SetDBValue(DateTime.Now) + ")";
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
newEC.Add(dr_excel["MRO编号"].ToString(), n);
}
dgError.DataSource = dt_error;
dgRepet.DataSource = dt_repet;
dgRepet_excel.DataSource = dt_repet_excel;
if (dt_error.Rows.Count > 0 || dt_repet.Rows.Count > 0 || dt_repet_excel.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
#endregion
#region 物料校验项 验证
dt_error = _ECInfoD_excel.Clone();
for (int i = 0; i < _ECInfoD_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _ECInfoD_excel.Rows[i];
int SNO = 0;
if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) ||
!int.TryParse(dr_excel["序号"].ToString(), out SNO))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["MRO编号"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
else if (!newEC.ContainsKey(dr_excel["MRO编号"].ToString()))
{
// 不存在
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["校验项"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 2 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["类型"].ToString()) ||
!CTYPEs.ContainsKey(dr_excel["类型"].ToString()))
{
//空、不包含
col_error.Add(new int[] { dt_error.Rows.Count, 5 });
isError = true;
}
DataRow[] drs_repet = _ECInfoD_excel.Select("校验项 = '" + dr_excel["校验项"].ToString() + "' AND MRO编号 = '" + dr_excel["MRO编号"] + "' ");
if (drs_repet.Length > 1)
{
isRepet_excel = true;
}
DataRow[] drs_dept_pos_a = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["处理部门"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["处理岗位"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["处理部门"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["处理岗位"].ToString()) ||
drs_dept_pos_a.Length == 0)
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 8 });
col_error.Add(new int[] { dt_error.Rows.Count, 9 });
isError = true;
}
DataRow[] drs_dept_pos_b = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["响应部门"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["响应岗位"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["响应部门"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["响应岗位"].ToString()) ||
drs_dept_pos_b.Length == 0)
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 10 });
col_error.Add(new int[] { dt_error.Rows.Count, 11 });
isError = true;
}
if (dr_excel["类型"].ToString() == "数值")
{
if (string.IsNullOrWhiteSpace(dr_excel["是否区间"].ToString()) ||
!ISINTERVALs.ContainsKey(dr_excel["是否区间"].ToString()))
{
//空、不包含
col_error.Add(new int[] { dt_error.Rows.Count, 12 });
isError = true;
}
}
int DAYS = 0;
//if (string.IsNullOrWhiteSpace(dr_excel["周期(天)"].ToString())
// && !int.TryParse(dr_excel["周期(天)"].ToString(), out DAYS))
//{
// //空
// col_error.Add(new int[] { dt_error.Rows.Count, 17 });
// isError = true;
//}
string Stand_Str = dr_excel["符号1"].ToString() + dr_excel["最小值"].ToString() + " " + dr_excel["符号2"].ToString() + dr_excel["最大值"].ToString();
if (dr_excel["类型"].ToString() == "文本" && string.IsNullOrWhiteSpace(dr_excel["标准"].ToString()))
{
col_error.Add(new int[] { dt_error.Rows.Count, 7 });
col_error.Add(new int[] { dt_error.Rows.Count, 13 });
col_error.Add(new int[] { dt_error.Rows.Count, 14 });
col_error.Add(new int[] { dt_error.Rows.Count, 15 });
col_error.Add(new int[] { dt_error.Rows.Count, 16 });
isError = true;
}
if (dr_excel["类型"].ToString() == "数值")
{
dr_excel["标准"] = Stand_Str;
}
if ((dr_excel["是否区间"].ToString() == "是" && dr_excel["符号1"].ToString() == "=") || ((dr_excel["是否区间"].ToString() == "否" && dr_excel["符号1"].ToString() != "=")))
{
col_error.Add(new int[] { dt_error.Rows.Count, 12 });
col_error.Add(new int[] { dt_error.Rows.Count, 15 });
isError = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel1.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = @"INSERT INTO [dbo].[MRCHECKCT]
([GUID],[PGUID],[SNO],[NAME],[METHOD]
,[REQUEST],[CTYPE],[SVALUE],[ISINTERVAL],[MINVALUE]
,[MAXVALUE],[SG1],[SG2],[CYCLE],[AGUID]
,[BGUID])
VALUES
(" + Main.SetDBValue(Guid.NewGuid()) + "," + Main.SetDBValue(newEC[dr_excel["MRO编号"].ToString()]) + "," + Main.SetDBValue(dr_excel["序号"]) + "," + Main.SetDBValue(dr_excel["校验项"]) + "," + Main.SetDBValue(dr_excel["方法"]) + @"
," + Main.SetDBValue(dr_excel["要求"]) + "," + Main.SetDBValue(CTYPEs[dr_excel["类型"].ToString()]) + "," + Main.SetDBValue(dr_excel["标准"]) + "," + (GetISINTERVAL(dr_excel["是否区间"].ToString())) + "," + Main.SetDBValue(dr_excel["最小值"]) + @"
," + Main.SetDBValue(dr_excel["最大值"]) + "," + Main.SetDBValue(dr_excel["符号1"]) + "," + Main.SetDBValue(dr_excel["符号2"]) + "," + Main.SetDBValue(dr_excel["周期(天)"]) + "," + Main.SetDBValue(drs_dept_pos_a[0]["BSDEPTPOS_GUID"]) + @"
," + Main.SetDBValue(drs_dept_pos_b[0]["BSDEPTPOS_GUID"]) + ")";
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError.DataSource = dt_error;
dgRepet_excel1.DataSource = dt_repet_excel1;
if (dt_error.Rows.Count > 0 || dt_repet_excel1.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
#endregion
#region 物料保养项 验证
dt_error = _ECInfoE_excel.Clone();
for (int i = 0; i < _ECInfoE_excel.Rows.Count; i++)
{
bool isError = false;
bool isRepet = false;
bool isRepet_excel = false;
DataRow dr_excel = _ECInfoE_excel.Rows[i];
int SNO = 0;
if (string.IsNullOrWhiteSpace(dr_excel["序号"].ToString()) ||
!int.TryParse(dr_excel["序号"].ToString(), out SNO))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 0 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["MRO编号"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
else if (!newEC.ContainsKey(dr_excel["MRO编号"].ToString()))
{
// 不存在
col_error.Add(new int[] { dt_error.Rows.Count, 1 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["保养项"].ToString()))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 2 });
isError = true;
}
if (string.IsNullOrWhiteSpace(dr_excel["类型"].ToString()) ||
!CTYPEs.ContainsKey(dr_excel["类型"].ToString()))
{
//空、不包含
col_error.Add(new int[] { dt_error.Rows.Count, 5 });
isError = true;
}
DataRow[] drs_repet = _ECInfoE_excel.Select("保养项 = '" + dr_excel["保养项"].ToString() + "' AND MRO编号 = '" + dr_excel["MRO编号"] + "' ");
if (drs_repet.Length > 1)
{
isRepet_excel = true;
}
DataRow[] drs_dept_pos_a = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["处理部门"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["处理岗位"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["处理部门"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["处理岗位"].ToString()) ||
drs_dept_pos_a.Length == 0)
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 8 });
col_error.Add(new int[] { dt_error.Rows.Count, 9 });
isError = true;
}
DataRow[] drs_dept_pos_b = _hrDeptPos_DB.Select("BSDEPT_CODE='"
+ WGHelper.ReturnString(dr_excel["响应部门"].ToString())
+ "' and BSPOSITION_CODE='" + WGHelper.ReturnString(dr_excel["响应岗位"].ToString()) + "'");
if (string.IsNullOrWhiteSpace(dr_excel["响应部门"].ToString()) ||
string.IsNullOrWhiteSpace(dr_excel["响应岗位"].ToString()) ||
drs_dept_pos_b.Length == 0)
{
//空、不存在
col_error.Add(new int[] { dt_error.Rows.Count, 10 });
col_error.Add(new int[] { dt_error.Rows.Count, 11 });
isError = true;
}
int DAYS = 0;
if (string.IsNullOrWhiteSpace(dr_excel["周期(天)"].ToString()) &&
!int.TryParse(dr_excel["周期(天)"].ToString(), out DAYS))
{
//空
col_error.Add(new int[] { dt_error.Rows.Count, 17 });
isError = true;
}
string Stand_Str = dr_excel["符号1"].ToString() + dr_excel["最小值"].ToString() + " " + dr_excel["符号2"].ToString() + dr_excel["最大值"].ToString();
if (dr_excel["类型"].ToString() == "文本" && string.IsNullOrWhiteSpace(dr_excel["标准"].ToString()))
{
col_error.Add(new int[] { dt_error.Rows.Count, 7 });
col_error.Add(new int[] { dt_error.Rows.Count, 13 });
col_error.Add(new int[] { dt_error.Rows.Count, 14 });
col_error.Add(new int[] { dt_error.Rows.Count, 15 });
col_error.Add(new int[] { dt_error.Rows.Count, 16 });
isError = true;
}
if (dr_excel["类型"].ToString() == "数值")
{
dr_excel["标准"] = Stand_Str;
}
if ((dr_excel["是否区间"].ToString() == "是" && dr_excel["符号1"].ToString() == "=") || ((dr_excel["是否区间"].ToString() == "否" && dr_excel["符号1"].ToString() != "=")))
{
col_error.Add(new int[] { dt_error.Rows.Count, 12 });
col_error.Add(new int[] { dt_error.Rows.Count, 15 });
isError = true;
}
if (isError || isRepet || isRepet_excel)
{
if (isError)
{
dt_error.Rows.Add(dr_excel.ItemArray);
}
if (isRepet_excel)
{
dt_repet_excel2.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = @"INSERT INTO [dbo].[MRMAINT]
([GUID],[PGUID],[SNO],[NAME],[METHOD]
,[REQUEST],[CTYPE],[SVALUE],[ISINTERVAL],[MINVALUE]
,[MAXVALUE],[SG1],[SG2],[CYCLE],[AGUID]
,[BGUID])
VALUES
(" + Main.SetDBValue(Guid.NewGuid()) + "," + Main.SetDBValue(newEC[dr_excel["MRO编号"].ToString()]) + "," + Main.SetDBValue(dr_excel["序号"]) + "," + Main.SetDBValue(dr_excel["保养项"]) + "," + Main.SetDBValue(dr_excel["方法"]) + @"
," + Main.SetDBValue(dr_excel["要求"]) + "," + Main.SetDBValue(CTYPEs[dr_excel["类型"].ToString()]) + "," + Main.SetDBValue(dr_excel["标准"]) + "," + (GetISINTERVAL(dr_excel["是否区间"].ToString())) + "," + Main.SetDBValue(dr_excel["最小值"]) + @"
," + Main.SetDBValue(dr_excel["最大值"]) + "," + Main.SetDBValue(dr_excel["符号1"]) + "," + Main.SetDBValue(dr_excel["符号2"]) + "," + Main.SetDBValue(dr_excel["周期(天)"]) + "," + Main.SetDBValue(drs_dept_pos_a[0]["BSDEPTPOS_GUID"]) + @"
," + Main.SetDBValue(drs_dept_pos_b[0]["BSDEPTPOS_GUID"]) + ")";
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
dgError.DataSource = dt_error;
dgRepet_excel2.DataSource = dt_repet_excel2;
if (dt_error.Rows.Count > 0 || dt_repet_excel2.Rows.Count > 0)
{
Main.SetErrorCell(dgError, col_error);
rbSql.Text = "";
return;
}
#endregion
isCheck = true;
}
private void btnCheck_Click(object sender, EventArgs e)
{
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;
}
}
private void btnCheck_Click(object sender, EventArgs e)
{
try
{
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
rbSql.Text = "";
sqlLs = new List <string>();
if (_HREMPLOYEE_excel == null)
{
WGMessage.ShowWarning("请选择职工信息文件!");
return;
}
Dictionary <string, int> dic = new Dictionary <string, int>();
dic.Add("正式工", 1);
dic.Add("派遣工", 2);
dic.Add("其他", 3);
Dictionary <string, bool> dicSex = new Dictionary <string, bool>();
dicSex.Add("男", true);
dicSex.Add("女", false);
string sql = "";
Dictionary <string, bool> dicISSTOP = new Dictionary <string, bool>();
dicISSTOP.Add("是", true);
dicISSTOP.Add("否", false);
sql = "SELECT * FROM HREMPLOYEE";
_HREMPLOYEE_DB = FillDatatable(sql, Main.CONN_Public);
//sql = "SELECT * FROM HREMPLOYEES";
//_HREMPLOYEES_DB = FillDatatable(sql, Main.CONN_Public);
sql = @"SELECT * FROM BSDEPT";
_BSDEPT_DB = FillDatatable(sql, Main.CONN_Public);
sql = @"SELECT BSDEPTA.*,BSPOSITION.CODE FROM BSDEPTA LEFT JOIN BSPOSITION ON BSPOSITION.GUID=BSDEPTA.AGUID";
_BSDEPTPOS_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);
//错误
List <int[]> col_error1 = new List <int[]>();
List <int[]> col_error2 = new List <int[]>();
List <int[]> col_error3 = new List <int[]>();
DataTable dt_error1 = _HREMPLOYEE_excel.Clone();
//重复数据
DataTable dt_repet1 = _HREMPLOYEE_excel.Clone();
DataTable dt_datarepet1 = _HREMPLOYEE_excel.Clone();
Dictionary <string, string> doclist = new Dictionary <string, string>();
#region 主
string Aguid = "";
string Bguid = "";
bool sex = false;
string sBIRTHDAY = "";
string sJOBSDATE = "";
int POSST = 0;
bool isSTOP = false;
string pwd = "";
sql = "SELECT * FROM SSPARAM WHERE PKEY='ITCPassword'";
DataTable dtParam = FillDatatable(sql, Main.CONN_Public);
if (dtParam != null && dtParam.Rows.Count > 0)
{
pwd = dtParam.Rows[0]["PVAL"] == DBNull.Value || dtParam.Rows[0]["PVAL"].ToString() == "" ? c_Pwd.SEncrypt("0000") : dtParam.Rows[0]["PVAL"].ToString();
}
else
{
pwd = c_Pwd.SEncrypt("0000");
}
for (int i = 0; i < _HREMPLOYEE_excel.Rows.Count; i++)
{
bool isError1 = false;
bool isRepet1 = false;
bool isDataRepet1 = false;
DataRow dr_excel = _HREMPLOYEE_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["员工编码"].ToString()))
{
//空
col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
isError1 = true;
}
else
{
DataRow[] drss = _HREMPLOYEE_excel.Select("员工编码 = '" + ReturnString(dr_excel["员工编码"].ToString()) + "'");
if (drss.Length > 1)
{
isRepet1 = true;
}
DataRow[] drss1 = _HREMPLOYEE_DB.Select(string.Format("EMPCODE='{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 (dicSex.ContainsKey(dr_excel["性别"].ToString()))
{
sex = dicSex[dr_excel["性别"].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, 3 });
isError1 = true;
}
else
{
DataRow[] drss = _BSDEPT_DB.Select("CODE='" + ReturnString(dr_excel["部门编号"].ToString()) + "'");
if (drss.Length == 0)
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 3 });
isError1 = true;
}
else
{
Aguid = drss[0]["GUID"].ToString();
}
}
if (string.IsNullOrWhiteSpace(dr_excel["岗位编号"].ToString()))
{
//空
col_error1.Add(new int[] { dt_error1.Rows.Count, 4 });
isError1 = true;
}
else
{
DataRow[] drss = _BSDEPTPOS_DB.Select("CODE='" + ReturnString(dr_excel["岗位编号"].ToString()) + "' AND PGUID='" + Aguid + "'");
if (drss.Count() == 0)
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 4 });
isError1 = true;
}
else
{
Bguid = drss[0]["GUID"].ToString();
}
}
DateTime BIRTHDAY = new DateTime();
if (string.IsNullOrWhiteSpace(dr_excel["出生日期"].ToString()))
{
sBIRTHDAY = "null";
}
else
{
if (!DateTime.TryParse(dr_excel["出生日期"].ToString(), out BIRTHDAY))
{
//空、类型不符
col_error1.Add(new int[] { dt_error1.Rows.Count, 5 });
isError1 = true;
}
else
{
sBIRTHDAY = "'" + BIRTHDAY.ToString("yyyy/MM/dd") + "'";
}
}
//if (string.IsNullOrWhiteSpace(dr_excel["员工类型"].ToString()))
//{
// //空
// col_error1.Add(new int[] { dt_error1.Rows.Count, 8 });
// isError1 = true;
//}
//else
//{
// if (dic.ContainsKey(dr_excel["员工类型"].ToString()))
// {
// POSST = dic[dr_excel["员工类型"].ToString()];
// }
// else
// {
// col_error1.Add(new int[] { dt_error1.Rows.Count, 8 });
// isError1 = true;
// }
//}
if (string.IsNullOrWhiteSpace(dr_excel["入职日期"].ToString()))
{
sJOBSDATE = "null";
}
else
{
if (!DateTime.TryParse(dr_excel["入职日期"].ToString(), out BIRTHDAY))
{
//空、类型不符
col_error1.Add(new int[] { dt_error1.Rows.Count, 9 });
isError1 = true;
}
else
{
sJOBSDATE = "'" + BIRTHDAY.ToString("yyyy/MM/dd") + "'";
}
}
//if (string.IsNullOrWhiteSpace(dr_excel["是否离职"].ToString()))
//{
// //空
// col_error1.Add(new int[] { dt_error1.Rows.Count, 10 });
// isError1 = true;
//}
//else
//{
// if (dicISSTOP.ContainsKey(dr_excel["是否离职"].ToString()))
// {
// isSTOP = dicISSTOP[dr_excel["是否离职"].ToString()];
// }
// else
// {
// col_error1.Add(new int[] { dt_error1.Rows.Count, 10 });
// isError1 = true;
// }
//}
//if (string.IsNullOrWhiteSpace(dr_excel["英文名"].ToString()))
//{
// //空
// col_error1.Add(new int[] { dt_error1.Rows.Count, 13 });
// isError1 = true;
//}
//if (string.IsNullOrWhiteSpace(dr_excel["紧急联系人"].ToString()))
//{
// //空
// col_error1.Add(new int[] { dt_error1.Rows.Count, 14 });
// isError1 = true;
//}
//if (string.IsNullOrWhiteSpace(dr_excel["家庭住址"].ToString()))
//{
// //空
// col_error1.Add(new int[] { dt_error1.Rows.Count, 15 });
// isError1 = true;
//}
string HREMPLOYEE_GUID = Guid.NewGuid().ToString();
_HREMPLOYEE_excel.Rows[i]["GUID"] = HREMPLOYEE_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 [HREMPLOYEE]
([GUID],[EMPCODE],[EMPNAME],[SEX],[AGUID]
,[BGUID],[BIRTHDAY],[PHONE],[MAIL],[POSST],[JOBSDATE],[ISSTOP],[LNOTE],[NOTE]
,[CC],[ND],[CD],[PWD],[ENAME],[SPECIALITY],[ADDRESS])
VALUES ('{0}','{1}','{2}','{3}','{4}',
'{5}',{6},'{7}','{8}',{9},{10},'{11}','{12}','{13}','1023','{14}','{14}','{15}','{16}','{17}','{18}')",
HREMPLOYEE_GUID, ReturnString(dr_excel["员工编码"].ToString()), ReturnString(dr_excel["中文姓名"].ToString()), sex, Aguid,
Bguid, sBIRTHDAY, ReturnString(dr_excel["联系方式"].ToString()), ReturnString(dr_excel["办公邮箱"].ToString()), POSST, sJOBSDATE, isSTOP, ReturnString(dr_excel["离职备注"].ToString()), dr_excel["备注"].ToString(), DateTime.Now.ToString(), pwd, ReturnString(dr_excel["英文名"].ToString()), ReturnString(dr_excel["紧急联系人"].ToString()), ReturnString(dr_excel["家庭住址"].ToString()));
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
string temp1 = string.Format("INSERT INTO HREMPLOYEEA(GUID,PGUID) VALUES(NEWID(),'{0}')", HREMPLOYEE_GUID);
rbSql.Text += temp1 + Environment.NewLine;
sqlLs.Add(temp1);
}
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;
}
}
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;
}
}
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;
}
}
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;
}
}
private void btnCheck_Click(object sender, EventArgs e)
{
try
{
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
rbSql.Text = "";
sqlLs = new List <string>();
if (_BSDEPT_excel == null)
{
WGMessage.ShowWarning("请选择部门文件!");
return;
}
if (_BSDEPTPOS_excel == null)
{
WGMessage.ShowWarning("请选择部门职位文件!");
return;
}
string sql = "";
sql = "SELECT * FROM BSDEPT";
_BSDEPT_DB = FillDatatablde(sql, Main.CONN_Public);
sql = "SELECT * FROM BSDEPTA";
_BSDEPTPOS_DB = FillDatatablde(sql, Main.CONN_Public);
sql = "SELECT * FROM BSCOMPANY";
_BSCOMPANY_DB = FillDatatablde(sql, Main.CONN_Public);
sql = "SELECT * FROM BSPOSITION";
_BSPOSITION_DB = FillDatatablde(sql, Main.CONN_Public);
//错误
List <int[]> col_error1 = new List <int[]>();
List <int[]> col_error2 = new List <int[]>();
DataTable dt_error1 = _BSDEPT_excel.Clone();
//重复数据
DataTable dt_repet1 = _BSDEPT_excel.Clone();
DataTable dt_datarepet1 = _BSDEPT_excel.Clone();
DataTable dt_error2 = _BSDEPTPOS_excel.Clone();
//重复数据
DataTable dt_repet2 = _BSDEPTPOS_excel.Clone();
Dictionary <string, string> doclist = new Dictionary <string, string>();
#region 主
string Pguid = "null";
string Aguid = "";
for (int i = 0; i < _BSDEPT_excel.Rows.Count; i++)
{
bool isError1 = false;
bool isRepet1 = false;
bool isDataRepet1 = false;
DataRow dr_excel = _BSDEPT_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["编号"].ToString()))
{
//空
col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
isError1 = true;
}
else
{
DataRow[] drss = _BSDEPT_excel.Select("编号 = '" + ReturnString(dr_excel["编号"].ToString()) + "'");
if (drss.Length > 1)
{
isRepet1 = true;
}
DataRow[] drss1 = _BSDEPT_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 = _BSCOMPANY_DB.Select("CODE = '" + ReturnString(dr_excel["公司编号"].ToString()) + "'");
if (drss.Length > 0)
{
Aguid = drss[0]["GUID"].ToString();
}
else
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
isError1 = true;
}
}
if (!string.IsNullOrWhiteSpace(dr_excel["上级部门编号"].ToString()))
{
DataRow[] drss = _BSDEPT_excel.Select("编号 = '" + ReturnString(dr_excel["上级部门编号"].ToString()) + "'");
if (drss.Length > 0)
{
Pguid = "'" + drss[0]["GUID"].ToString() + "'";
}
else
{
drss = _BSDEPT_DB.Select(string.Format("CODE='{0}'", ReturnString(dr_excel["上级部门编号"].ToString())));
if (drss.Length > 0)
{
Pguid = "'" + drss[0]["GUID"].ToString() + "'";
}
else
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 3 });
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 [BSDEPT]
([GUID],[AGUID],[ACODE],[CODE]
,[NAME],[PGUID],[NOTE]
,[CC],[ND],[CD])
VALUES ('{0}','{1}','{2}',
'{3}','{4}',{5},'{6}',
'1023','{7}','{7}')",
dr_excel["GUID"], Aguid, ReturnString(dr_excel["公司编号"].ToString()), ReturnString(dr_excel["编号"].ToString()),
ReturnString(dr_excel["名称"].ToString()), Pguid, ReturnString(dr_excel["备注"].ToString()),
DateTime.Now.ToString());
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
catch
{ }
}
#endregion
#region 子
string Mguid = "";
string AGUID = "";
Dictionary <string, bool> dic = new Dictionary <string, bool>();
dic.Add("是", true);
dic.Add("否", false);
for (int i = 0; i < _BSDEPTPOS_excel.Rows.Count; i++)
{
bool isError2 = false;
bool isRepet2 = false;
DataRow dr_excel = _BSDEPTPOS_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["部门编号"].ToString()))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 0 });
isError2 = true;
}
else
{
DataRow[] drss = _BSDEPT_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 = _BSPOSITION_DB.Select("CODE='" + ReturnString(dr_excel["职位编号"].ToString()) + "'");
if (drss.Length > 0)
{
AGUID = drss[0]["GUID"].ToString();
drss = _BSDEPTPOS_excel.Select("部门编号='" + ReturnString(dr_excel["部门编号"].ToString()) + "' AND 职位编号='" + ReturnString(dr_excel["职位编号"].ToString()) + "'");
if (drss.Length > 1)
{
isRepet2 = true;
}
}
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
{
if (!dic.ContainsKey(dr_excel["部门审批权限"].ToString()))
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
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 BSDEPTA (GUID,PGUID,SNO,AGUID,ACODE,ANAME,FLOWAUDIT)
VALUES(NEWID(),'{0}',{1},'{2}','{3}','{4}','{5}')", Mguid, dr_excel["序号"], AGUID, dr_excel["职位编号"], dr_excel["职位名称"], dic[dr_excel["部门审批权限"].ToString()]);
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;
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)
{
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;
}
}
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;
}
}
private void btnCheck_Click(object sender, EventArgs e)
{
try
{
if (isCheck)
{
WGMessage.ShowAsterisk("已验证，不用重复验证！");
return;
}
rbSql.Text = "";
sqlLs = new List <string>();
if (_HRWORKLICENSE_excel == null)
{
WGMessage.ShowWarning("请选择上岗证文件!");
return;
}
if (_HRWORKLICENSES_excel == null)
{
WGMessage.ShowWarning("请选择上岗证技能分文件!");
return;
}
string sql = "";
sql = "SELECT * FROM HRWORKLICENSE";
_HRWORKLICENSE_DB = FillDatatablde(sql, Main.CONN_Public);
sql = "SELECT * FROM HRWORKLICENSES";
_HRWORKLICENSES_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT * FROM BSWORKSHOP";
_BSWORKSHOP_DB = FillDatatablde(sql, Main.CONN_Public);
sql = @"SELECT * FROM ECTYPE";
_ECTYPE_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[]>();
List <int[]> col_error3 = new List <int[]>();
DataTable dt_error1 = _HRWORKLICENSE_excel.Clone();
//重复数据
DataTable dt_repet1 = _HRWORKLICENSE_excel.Clone();
DataTable dt_datarepet1 = _HRWORKLICENSE_excel.Clone();
DataTable dt_lack1 = _HRWORKLICENSE_excel.Clone();
DataTable dt_error2 = _HRWORKLICENSES_excel.Clone();
//重复数据
DataTable dt_repet2 = _HRWORKLICENSES_excel.Clone();
DataTable dt_lack2 = _HRWORKLICENSES_excel.Clone();
Dictionary <string, string> doclist = new Dictionary <string, string>();
#region 主
string Aguid = "";
string Bguid = "";
string Cguid = "";
for (int i = 0; i < _HRWORKLICENSE_excel.Rows.Count; i++)
{
bool isError1 = false;
bool isRepet1 = false;
bool islack1 = false;
bool isDataRepet1 = false;
DataRow dr_excel = _HRWORKLICENSE_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["上岗证名称"].ToString()))
{
//空
col_error1.Add(new int[] { dt_error1.Rows.Count, 0 });
isError1 = true;
}
else
{
DataRow[] drss = _HRWORKLICENSE_excel.Select("上岗证名称 = '" + ReturnString(dr_excel["上岗证名称"].ToString()) + "'");
if (drss.Length > 1)
{
isRepet1 = true;
}
DataRow[] drss1 = _HRWORKLICENSE_DB.Select(string.Format("NAME='{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;
}
else
{
DataRow[] drss = _BSWORKSHOP_DB.Select("CODE='" + ReturnString(dr_excel["车间编号"].ToString()) + "'");
if (drss.Count() == 0)
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 1 });
isError1 = true;
}
else
{
Aguid = drss[0]["GUID"].ToString();
}
}
if (string.IsNullOrWhiteSpace(dr_excel["设备类别编号"].ToString()))
{
//空
col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
isError1 = true;
}
else
{
DataRow[] drss = _ECTYPE_DB.Select("CODE='" + ReturnString(dr_excel["设备类别编号"].ToString()) + "'");
if (drss.Count() == 0)
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 2 });
isError1 = true;
}
else
{
Bguid = drss[0]["GUID"].ToString();
}
}
if (string.IsNullOrWhiteSpace(dr_excel["设备编号"].ToString()))
{
DataRow[] drss = _HRWORKLICENSE_excel.Select("车间编号 = '" + ReturnString(dr_excel["车间编号"].ToString()) + "' AND 设备类别编号='" + ReturnString(dr_excel["设备类别编号"].ToString()) + "' AND 设备编号='' ");
if (drss.Length > 1)
{
isRepet1 = true;
}
drss = _HRWORKLICENSE_DB.Select("AGUID = '" + Aguid + "' AND BGUID='" + Bguid + "' AND CGUID IS NULL");
if (drss.Length > 0)
{
isDataRepet1 = true;
}
}
else
{
DataRow[] drss = _HRWORKLICENSE_excel.Select("车间编号 = '" + ReturnString(dr_excel["车间编号"].ToString()) + "' AND 设备类别编号='" + ReturnString(dr_excel["设备类别编号"].ToString()) + "' AND 设备编号='" + ReturnString(dr_excel["设备编号"].ToString()) + "'");
if (drss.Length > 1)
{
isRepet1 = true;
}
else
{
drss = _HRWORKLICENSE_DB.Select("AGUID = '" + Aguid + "' AND BGUID='" + Bguid + "' AND CGUID='" + Cguid + "'");
if (drss.Length > 0)
{
isDataRepet1 = true;
}
else
{
drss = _ECINFO_DB.Select("CODE='" + ReturnString(dr_excel["设备编号"].ToString()) + "' AND AGUID='" + Bguid + "'");
if (drss.Length > 0)
{
Cguid = drss[0]["GUID"].ToString();
}
else
{
col_error1.Add(new int[] { dt_error1.Rows.Count, 3 });
isError1 = true;
}
}
}
}
DataRow[] drs1 = _HRWORKLICENSES_excel.Select(string.Format(@"车间编号='{0}' AND 设备类别编号='{1}'{2}", ReturnString(dr_excel["车间编号"].ToString()), ReturnString(dr_excel["设备类别编号"].ToString()), Cguid == "" ? " AND 设备编号 IS NULL" : " AND 设备编号='" + dr_excel["设备编号"] + "'"));
if (drs1.Length == 0)
{
islack1 = true;
}
string HRWORKLICENSE_GUID = Guid.NewGuid().ToString();
_HRWORKLICENSE_excel.Rows[i]["GUID"] = HRWORKLICENSE_GUID;
if (isError1 || isRepet1 || islack1 || isDataRepet1)
{
if (isError1)
{
dt_error1.Rows.Add(dr_excel.ItemArray);
}
if (isRepet1)
{
dt_repet1.Rows.Add(dr_excel.ItemArray);
}
if (islack1)
{
dt_lack1.Rows.Add(dr_excel.ItemArray);
}
if (isDataRepet1)
{
dt_datarepet1.Rows.Add(dr_excel.ItemArray);
}
continue;
}
try
{
string temp = string.Format(@"INSERT INTO [HRWORKLICENSE]
([GUID],[NAME],[AGUID]
,[BGUID],[CGUID],[NOTE]
,[ST],[CC],[ND],[CD])
VALUES ('{0}','{1}','{2}','{3}',{4},'{5}',1,'1023','{6}','{6}')",
HRWORKLICENSE_GUID, ReturnString(dr_excel["上岗证名称"].ToString()), Aguid,
Bguid, Cguid == "" ? "null" : "'" + Cguid + "'", ReturnString(dr_excel["备注"].ToString()), DateTime.Now.ToString());
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
catch
{ }
}
#endregion
#region 子
string Mguid = "";
string EC = "";
for (int i = 0; i < _HRWORKLICENSES_excel.Rows.Count; i++)
{
bool isError2 = false;
bool isRepet2 = false;
bool isLack2 = false;
DataRow dr_excel = _HRWORKLICENSES_excel.Rows[i];
if (string.IsNullOrWhiteSpace(dr_excel["车间编号"].ToString()))
{
//空
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;
}
if (string.IsNullOrEmpty(dr_excel["设备编号"].ToString()))
{
EC = " AND 设备编号 is null";
}
else
{
EC = " AND 设备编号='" + dr_excel["设备编号"].ToString() + "'";
}
int numa;
if (!int.TryParse(dr_excel["星级"].ToString(), out numa))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
isError2 = true;
}
else
{
if (numa <= 0 || numa >= 5)
{
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
isError2 = true;
}
DataRow[] drss = _HRWORKLICENSES_excel.Select("车间编号='" + ReturnString(dr_excel["车间编号"].ToString()) + "' AND 设备类别编号='" + ReturnString(dr_excel["设备类别编号"].ToString()) + "' " + EC + " AND 星级=" + dr_excel["星级"].ToString());
if (drss.Length > 1)
{
isRepet2 = true;
}
else
{
drss = _HRWORKLICENSE_excel.Select("车间编号='" + ReturnString(dr_excel["车间编号"].ToString()) + "' AND 设备类别编号='" + ReturnString(dr_excel["设备类别编号"].ToString()) + "' " + EC + "");
if (drss.Length > 0)
{
Mguid = drss[0]["GUID"].ToString();
}
else
{
isError2 = true;
col_error2.Add(new int[] { dt_error2.Rows.Count, 3 });
}
}
}
if (!int.TryParse(dr_excel["技能分"].ToString(), out numa))
{
//空
col_error2.Add(new int[] { dt_error2.Rows.Count, 4 });
isError2 = true;
}
DataRow[] drs = _HRWORKLICENSES_excel.Select("车间编号='" + ReturnString(dr_excel["车间编号"].ToString()) + "' AND 设备类别编号='" + ReturnString(dr_excel["设备类别编号"].ToString()) + "' " + EC);
List <int> lst = new List <int>();
foreach (DataRow dr in drs)
{
if (int.TryParse(dr["星级"].ToString(), out numa) && !lst.Contains(numa))
{
lst.Add(numa);
}
}
if (lst.Count != 4)
{
isLack2 = true;
}
if (isError2 || isRepet2 || isLack2)
{
if (isError2)
{
dt_error2.Rows.Add(dr_excel.ItemArray);
}
if (isRepet2)
{
dt_repet2.Rows.Add(dr_excel.ItemArray);
}
if (isLack2)
{
dt_lack2.Rows.Add(dr_excel.ItemArray);
}
continue;
}
string temp = string.Format(@" INSERT INTO HRWORKLICENSES (GUID,PGUID,LVL,SPNUM) VALUES(NEWID(),'{0}',{1},{2})", Mguid, dr_excel["星级"], dr_excel["技能分"]);
rbSql.Text += temp + Environment.NewLine;
sqlLs.Add(temp);
}
#endregion
dt_error1.Columns.Remove("GUID");
dt_repet1.Columns.Remove("GUID");
dt_lack1.Columns.Remove("GUID");
dt_datarepet1.Columns.Remove("GUID");
dgError1.DataSource = dt_error1; dgError2.DataSource = dt_error2; dgLack1.DataSource = dt_lack1;
dgRepet1.DataSource = dt_repet1; dgRepet2.DataSource = dt_repet2; dgLack2.DataSource = dt_lack2;
dgDataRepet1.DataSource = dt_datarepet1;
if (dt_error1.Rows.Count > 0 || dt_error2.Rows.Count > 0 || dt_repet1.Rows.Count > 0 || dt_repet2.Rows.Count > 0 || dt_lack2.Rows.Count > 0 || dt_lack1.Rows.Count > 0 || dt_datarepet1.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;
}
}