private void cbeRLLX_Validating(object sender, CancelEventArgs e)
{
if (!string.IsNullOrEmpty(DataVerifyHelper.VerifyRequired(string.Empty, this.cbeRLLX.Text.Trim())))
{
this.dxErrorProvider1.SetError(cbeRLLX, DataVerifyHelper.VerifyRequired(string.Empty, this.cbeRLLX.Text.Trim()).TrimStart('\n'));
}
else
{
dxErrorProvider1.SetError(cbeRLLX, "");
}
}
// 校验数据是否合乎规则
private string VerifyRLLXPARAM()
{
string message = string.Empty;
this.gvOCN_RLLXPARAM.CloseEditor();
this.gvOCN_RLLXPARAM.UpdateCurrentRow();
//OCN验证
if (teSC_OCN.Text.Equals(""))
{
message += "\nOCN不能为空";
}
var dt = (DataTable)this.gcOCN_RLLXPARAM.DataSource;
int paramCountCTNY = Convert.ToInt32(OracleHelper.GetSingle(OracleHelper.conn, "SELECT COUNT(*) FROM RLLX_PARAM WHERE STATUS='1' AND FUEL_TYPE='传统能源'"));
int paramCountCDD = Convert.ToInt32(OracleHelper.GetSingle(OracleHelper.conn, "SELECT COUNT(*) FROM RLLX_PARAM WHERE STATUS='1' AND FUEL_TYPE='纯电动'"));
int paramCountCDS = Convert.ToInt32(OracleHelper.GetSingle(OracleHelper.conn, "SELECT COUNT(*) FROM RLLX_PARAM WHERE STATUS='1' AND FUEL_TYPE='插电式混合动力'"));
int paramCountFCDS = Convert.ToInt32(OracleHelper.GetSingle(OracleHelper.conn, "SELECT COUNT(*) FROM RLLX_PARAM WHERE STATUS='1' AND FUEL_TYPE='非插电式混合动力'"));
int paramCountRLDC = Convert.ToInt32(OracleHelper.GetSingle(OracleHelper.conn, "SELECT COUNT(*) FROM RLLX_PARAM WHERE STATUS='1' AND FUEL_TYPE='燃料电池'"));
// 参数个数验证
if (this.cbeRLLX.Text.Equals("纯电动") && paramCountCDD != dt.Rows.Count)
{
message += "\n" + this.cbeRLLX.Text + "的燃料参数个数应为" + paramCountCDD + "个，当前为" + dt.Rows.Count + "个";
}
else if (this.cbeRLLX.Text.Equals("非插电式混合动力") && paramCountFCDS != dt.Rows.Count)
{
message += "\n" + this.cbeRLLX.Text + "的燃料参数个数应为" + paramCountFCDS + "个，当前为" + dt.Rows.Count + "个";
}
else if (this.cbeRLLX.Text.Equals("插电式混合动力") && paramCountCDS != dt.Rows.Count)
{
message += "\n" + this.cbeRLLX.Text + "的燃料参数个数应为" + paramCountCDS + "个，当前为" + dt.Rows.Count + "个";
}
else if (this.cbeRLLX.Text.Equals("燃料电池") && paramCountRLDC != dt.Rows.Count)
{
message += "\n" + this.cbeRLLX.Text + "的燃料参数个数应为" + paramCountRLDC + "个，当前为" + dt.Rows.Count + "个";
}
else if (this.cbeRLLX.Text.Equals("传统能源") && paramCountCTNY != dt.Rows.Count)
{
message += "\n" + this.cbeRLLX.Text + "的燃料参数个数应为" + paramCountCTNY + "个，当前为" + dt.Rows.Count + "个";
}
foreach (DataRow dr in dt.Rows)
{
switch (this.cbeRLLX.Text)
{
case "纯电动":
message += DataVerifyHelper.VerifyCDD_RLLXPARAM(dr);
break;
case "非插电式混合动力":
message += DataVerifyHelper.VerifyHHDL_RLLXPARAM(dr);
break;
case "插电式混合动力":
message += DataVerifyHelper.VerifyHHDL_RLLXPARAM(dr);
break;
case "燃料电池":
message += DataVerifyHelper.VerifyRLDC_RLLXPARAM(dr);
break;
default:
message += DataVerifyHelper.VerifyCTNY_RLLXPARAM(dr);
break;
}
}
return(message);
}
// 导入Excel
private void barBtnImport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
Dictionary <string, string> error = new Dictionary <string, string>();
string msg = string.Empty;
try
{
SplashScreenManager.ShowForm(typeof(DevWaitForm));
// STEP1：导入系统，验证单元格格式
var ds = ImportExcel.ReadExcelToDataSet(openFileDialog1.FileName);
for (int i = 0; i < ds.Tables["TEMPLATE"].Columns.Count; i++)
{
if (ds.Tables[0].Columns[i].ColumnName.Equals("车辆制造/进口日期"))
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.DBNull))
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.DateTime))
{
MessageBox.Show(String.Format("【{1}】列中第【{0}】行的单元格格式不正确，应为日期格式!", j + 2, ds.Tables[0].Columns[i].ColumnName), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
}
}
else
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.DBNull))
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.String))
{
MessageBox.Show(String.Format("【{1}】列中第【{0}】行的单元格格式不正确，应为文本格式!", j + 2, ds.Tables[0].Columns[i].ColumnName), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
}
}
}
// STEP2：替换列名，验证参数的数值
var dt = ImportExcel.SwitchCLJBXXColumnName(ds);
if (dt != null)
{
error = DataVerifyHelper.VerifyCLJBXXData(dt);
if (error.Count == 0)
{
using (OracleConnection conn = new OracleConnection(OracleHelper.conn))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
// STEP3：验证无误，导入系统数据库
foreach (DataRow dr in dt.Rows)
{
try
{
string exist = OracleHelper.ExecuteScalar(trans, string.Format("SELECT COUNT(*) FROM OCN_CLJBXX WHERE OPERATION!='4' AND SC_OCN='{0}'", dr["SC_OCN"])).ToString();
int existNum = string.IsNullOrEmpty(exist) ? 0 : Convert.ToInt32(exist);
if (existNum > 0)
{
error.Add(dr["SC_OCN"].ToString().Trim(), "系统已经存在改生产OCN的整车基础数据！");
continue;
}
OracleParameter[] parameters =
{
new OracleParameter("SC_OCN", OracleDbType.NVarchar2, 255),
new OracleParameter("XT_OCN", OracleDbType.NVarchar2, 255),
new OracleParameter("MI_XT_OCN", OracleDbType.NVarchar2, 255),
new OracleParameter("TYMC", OracleDbType.NVarchar2, 255),
new OracleParameter("CLXH", OracleDbType.NVarchar2, 255),
new OracleParameter("PFBZ", OracleDbType.NVarchar2, 255),
new OracleParameter("SFJKQC", OracleDbType.NVarchar2, 255),
new OracleParameter("QCSCQY", OracleDbType.NVarchar2, 255),
new OracleParameter("JKQCZJXS", OracleDbType.NVarchar2, 255),
new OracleParameter("JCJGMC", OracleDbType.NVarchar2, 255),
new OracleParameter("BGBH", OracleDbType.NVarchar2, 255),
new OracleParameter("BAH", OracleDbType.NVarchar2, 255),
new OracleParameter("CLZZRQ", OracleDbType.Date),
new OracleParameter("CLZL", OracleDbType.NVarchar2, 255),
new OracleParameter("YYC", OracleDbType.NVarchar2, 255),
new OracleParameter("QDXS", OracleDbType.NVarchar2, 255),
new OracleParameter("ZWPS", OracleDbType.NVarchar2, 255),
new OracleParameter("ZGCS", OracleDbType.NVarchar2, 255),
new OracleParameter("EDZK", OracleDbType.NVarchar2, 255),
new OracleParameter("LTGG", OracleDbType.NVarchar2, 255),
new OracleParameter("LJ", OracleDbType.NVarchar2, 255),
new OracleParameter("ZJ", OracleDbType.NVarchar2, 255),
new OracleParameter("RLLX", OracleDbType.NVarchar2, 255),
new OracleParameter("YHDYBAH", OracleDbType.NVarchar2, 255),
new OracleParameter("ZCZBZL", OracleDbType.NVarchar2, 255),
new OracleParameter("ZDSJZZL", OracleDbType.NVarchar2, 255),
new OracleParameter("ZHGKRLXHL", OracleDbType.NVarchar2, 255),
new OracleParameter("RLXHLMBZ", OracleDbType.NVarchar2, 255),
new OracleParameter("JDBZMBZ4", OracleDbType.NVarchar2, 255),
new OracleParameter("BSQXS", OracleDbType.NVarchar2, 255),
new OracleParameter("PL", OracleDbType.NVarchar2, 255),
new OracleParameter("CDDQDMSZHGKXHLC", OracleDbType.NVarchar2, 255),
new OracleParameter("OPERATION", OracleDbType.NVarchar2, 255),
new OracleParameter("CREATE_TIME", OracleDbType.Date),
new OracleParameter("CREATE_ROLE", OracleDbType.NVarchar2, 255),
new OracleParameter("UPDATE_TIME", OracleDbType.Date),
new OracleParameter("UPDATE_ROLE", OracleDbType.NVarchar2, 255),
new OracleParameter("VERSION", OracleDbType.Int32),
};
parameters[0].Value = dr["SC_OCN"].ToString().Trim();
parameters[1].Value = dr["XT_OCN"].ToString().Trim();
parameters[2].Value = dr["MI_XT_OCN"].ToString().Trim();
parameters[3].Value = dr["TYMC"].ToString().Trim();
parameters[4].Value = dr["CLXH"].ToString().Trim();
parameters[5].Value = dr["PFBZ"].ToString().Trim();
parameters[6].Value = dr["SFJKQC"].ToString().Trim();
parameters[7].Value = dr["QCSCQY"].ToString().Trim();
parameters[8].Value = dr["JKQCZJXS"].ToString().Trim();
parameters[9].Value = dr["JCJGMC"].ToString().Trim();
parameters[10].Value = dr["BGBH"].ToString().Trim();
parameters[11].Value = dr["BAH"].ToString().Trim();
parameters[12].Value = dr["CLZZRQ"];
parameters[13].Value = dr["CLZL"].ToString().Trim();
parameters[14].Value = dr["YYC"].ToString().Trim();
parameters[15].Value = dr["QDXS"].ToString().Trim();
parameters[16].Value = dr["ZWPS"].ToString().Trim();
parameters[17].Value = dr["ZGCS"].ToString().Trim();
parameters[18].Value = dr["EDZK"].ToString().Trim();
parameters[19].Value = dr["LTGG"].ToString().Trim();
parameters[20].Value = dr["LJ"].ToString().Trim();
parameters[21].Value = dr["ZJ"].ToString().Trim();
parameters[22].Value = dr["RLLX"].ToString().Trim();
parameters[23].Value = dr["YHDYBAH"].ToString().Trim();
parameters[24].Value = dr["ZCZBZL"].ToString().Trim();
parameters[25].Value = dr["ZDSJZZL"].ToString().Trim();
parameters[26].Value = dr["ZHGKRLXHL"].ToString().Trim();
parameters[27].Value = dr["RLXHLMBZ"].ToString().Trim();
parameters[28].Value = dr["JDBZMBZ4"].ToString().Trim();
parameters[29].Value = dr["BSQXS"].ToString().Trim();
parameters[30].Value = dr["PL"].ToString().Trim();
parameters[31].Value = dr["CDDQDMSZHGKXHLC"].ToString().Trim();
parameters[32].Value = "0";
parameters[33].Value = System.DateTime.Today;
parameters[34].Value = Utils.localUserId;
parameters[35].Value = System.DateTime.Today;
parameters[36].Value = Utils.localUserId;
parameters[37].Value = 0;
OracleHelper.ExecuteNonQuery(trans, "Insert into OCN_CLJBXX (SC_OCN,XT_OCN,MI_XT_OCN,TYMC,CLXH,PFBZ,SFJKQC,QCSCQY,JKQCZJXS,JCJGMC,BGBH,BAH,CLZZRQ,CLZL,YYC,QDXS,ZWPS,ZGCS,EDZK,LTGG,LJ,ZJ,RLLX,YHDYBAH,ZCZBZL,ZDSJZZL,ZHGKRLXHL,RLXHLMBZ,JDBZMBZ4,BSQXS,PL,CDDQDMSZHGKXHLC,OPERATION,CREATE_TIME,CREATE_ROLE,UPDATE_TIME,UPDATE_ROLE,VERSION) values (:SC_OCN,:XT_OCN,:MI_XT_OCN,:TYMC,:CLXH,:PFBZ,:SFJKQC,:QCSCQY,:JKQCZJXS,:JCJGMC,:BGBH,:BAH,:CLZZRQ,:CLZL,:YYC,:QDXS,:ZWPS,:ZGCS,:EDZK,:LTGG,:LJ,:ZJ,:RLLX,:YHDYBAH,:ZCZBZL,:ZDSJZZL,:ZHGKRLXHL,:RLXHLMBZ,:JDBZMBZ4,:BSQXS,:PL,:CDDQDMSZHGKXHLC,:OPERATION,:CREATE_TIME,:CREATE_ROLE,:UPDATE_TIME,:UPDATE_ROLE,:VERSION)", parameters);
}
catch (Exception ex)
{
error.Add(dr["SC_OCN"].ToString().Trim(), ex.Message);
continue;
}
}
if (trans.Connection != null)
{
trans.Commit();
}
}
}
// STEP4：处理无误，处理完成的文件
if (error.Count == 0)
{
var destFolder = Path.Combine(Path.GetDirectoryName(openFileDialog1.FileName), DateTime.Today.ToLongDateString() + "-整车基础数据-Imported");
Directory.CreateDirectory(destFolder);
try
{
File.Move(openFileDialog1.FileName, Path.Combine(destFolder, String.Format("Imported-{0}{1}", Path.GetFileNameWithoutExtension(openFileDialog1.FileName), Path.GetExtension(openFileDialog1.FileName))));
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Excel处理操作异常：导入完成，{0}", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
}
foreach (KeyValuePair <string, string> kvp in error)
{
msg += String.Format("{0}\r\n{1}\r\n", kvp.Key, kvp.Value);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Excel导入操作异常：" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
SplashScreenManager.CloseForm();
}
MessageForm msgForm = new MessageForm(msg + String.Format("\r\n{0}Excel导入操作完成", Path.GetFileNameWithoutExtension(openFileDialog1.FileName)))
{
Text = "整车基础数据导入信息"
};
msgForm.Show();
SearchLocal(1);
}
}
// 导入Excel
private void barBtnImport_ItemClick(object sender, ItemClickEventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
Dictionary <string, string> error = new Dictionary <string, string>();
string msg = string.Empty;
try
{
SplashScreenManager.ShowForm(typeof(DevWaitForm));
// STEP1：导入系统，验证单元格格式
var ds = ImportExcel.ReadExcelToDataSet(openFileDialog1.FileName);
for (int i = 0; i < ds.Tables["TEMPLATE"].Columns.Count; i++)
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.DBNull))
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.String))
{
MessageBox.Show(String.Format("【{1}】列中第【{0}】行的单元格格式不正确，应为文本格式!", j + 2, ds.Tables[0].Columns[i].ColumnName), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
}
}
// STEP2：替换列名，验证参数的数值
var dt = ImportExcel.SwitchRLLXPARAMColumnName(ds);
if (dt != null)
{
string sc_ocn_error = string.Empty;
error = DataVerifyHelper.VerifyRLLXPARAMData(dt);
if (error.Count == 0)
{
using (OracleConnection conn = new OracleConnection(OracleHelper.conn))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
// STEP3：验证无误，导入系统数据库
foreach (DataRow dr in dt.Rows)
{
if (sc_ocn_error.Equals(dr["SC_OCN"].ToString()))
{
continue;
}
try
{
string exist = OracleHelper.ExecuteScalar(trans, string.Format("SELECT COUNT(*) FROM OCN_RLLX_PARAM_ENTITY WHERE OPERATION!='4' AND SC_OCN='{0}' AND CSBM='{1}'", dr["SC_OCN"], dr["CSBM"])).ToString();
int existNum = string.IsNullOrEmpty(exist) ? 0 : Convert.ToInt32(exist);
if (existNum > 0)
{
error.Add(dr["SC_OCN"].ToString().Trim(), "系统已经存在改生产OCN的燃料参数数据！");
sc_ocn_error = dr["SC_OCN"].ToString();
continue;
}
OracleParameter[] parameters =
{
new OracleParameter("SC_OCN", OracleDbType.NVarchar2, 255),
new OracleParameter("CSBM", OracleDbType.NVarchar2, 255),
new OracleParameter("CSMC", OracleDbType.NVarchar2, 255),
new OracleParameter("RLLX", OracleDbType.NVarchar2, 255),
new OracleParameter("CSZ", OracleDbType.NVarchar2, 255),
new OracleParameter("OPERATION", OracleDbType.NVarchar2, 255),
new OracleParameter("CREATE_TIME", OracleDbType.Date),
new OracleParameter("CREATE_ROLE", OracleDbType.NVarchar2, 255),
new OracleParameter("UPDATE_TIME", OracleDbType.Date),
new OracleParameter("UPDATE_ROLE", OracleDbType.NVarchar2, 255),
new OracleParameter("VERSION", OracleDbType.Int32),
};
parameters[0].Value = dr["SC_OCN"];
parameters[1].Value = dr["CSBM"];
parameters[2].Value = dr["CSMC"];
parameters[3].Value = dr["RLLX"];
parameters[4].Value = dr["CSZ"];
parameters[5].Value = "0";
parameters[6].Value = System.DateTime.Today;
parameters[7].Value = Utils.localUserId;
parameters[8].Value = System.DateTime.Today;
parameters[9].Value = Utils.localUserId;
parameters[10].Value = 0;
OracleHelper.ExecuteNonQuery(trans, "Insert into OCN_RLLX_PARAM_ENTITY (SC_OCN,CSBM,CSMC,RLLX,CSZ,OPERATION,CREATE_TIME,CREATE_ROLE,UPDATE_TIME,UPDATE_ROLE,VERSION) values (:SC_OCN,:CSBM,:CSMC,:RLLX,:CSZ,:OPERATION,:CREATE_TIME,:CREATE_ROLE,:UPDATE_TIME,:UPDATE_ROLE,:VERSION)", parameters);
}
catch (Exception ex)
{
error.Add(String.Format("{0} {0}", dr["SC_OCN"], dr["CSBM"]), ex.Message);
sc_ocn_error = dr["SC_OCN"].ToString();
continue;
}
}
if (trans.Connection != null)
{
trans.Commit();
}
}
}
// STEP4：处理无误，处理完成的文件
if (error.Count == 0)
{
var destFolder = Path.Combine(Path.GetDirectoryName(openFileDialog1.FileName), DateTime.Today.ToLongDateString() + "-燃料参数数据-Imported");
Directory.CreateDirectory(destFolder);
try
{
File.Move(openFileDialog1.FileName, Path.Combine(destFolder, String.Format("Imported-{0}{1}", Path.GetFileNameWithoutExtension(openFileDialog1.FileName), Path.GetExtension(openFileDialog1.FileName))));
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Excel处理操作异常：导入完成，{0}", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
}
foreach (KeyValuePair <string, string> kvp in error)
{
msg += String.Format("{0}\r\n{1}\r\n", kvp.Key, kvp.Value);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Excel导入操作异常：" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
SplashScreenManager.CloseForm();
}
MessageForm msgForm = new MessageForm(msg + String.Format("\r\n{0}Excel导入操作完成", Path.GetFileNameWithoutExtension(openFileDialog1.FileName)))
{
Text = "燃料参数导入信息"
};
msgForm.Show();
SearchLocal(1);
}
}
