private void ReadIIC(IDictionary <string, Model.DecisionProjectObjDJ> ts, Model.DetectRecordObjDJ detectRecordObj, DataTable dtUnitAreaConfig, DataTable dtSystemConfig, int intDataID, int intRecordID, int intTaskID, int intPlanID, string strIICPath, string strFileName, string strImpDes)
{
string strIICFullPath = GetIICDirection(dtSystemConfig) + strIICPath + "\\" + strFileName;
using (OleDbConnection oleCn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strIICFullPath + ";Persist Security Info=True"))
{
oleCn.Open();
OleDbCommand cmd = new OleDbCommand("select * from fix_defects", oleCn);
OleDbDataReader myRed;
bool bFxIsExists = true;
try
{
myRed = cmd.ExecuteReader();
}
catch
{
bFxIsExists = false;
}
string strSQLForDefects = string.Empty;
string strSQLForTQI = string.Empty;
if (!bFxIsExists)
{
strSQLForDefects = "select * from defects where valid <> 'N' and severity is not null";
strSQLForTQI = "TRANSFORM max([tqivalue]) SELECT [FROMPOST],round((round([basepost],3)-[FROMPOST])*1000,0) as [FROMMINOR] FROM tqi GROUP BY [FROMPOST],round((round([basepost],3)-[FROMPOST])*1000,0) PIVOT [tqimetricname]";
}
else
{
//new表存在的情况
strSQLForDefects = "select * from fix_defects where valid <> 'N' and severity is not null and maxval2=-200";
//strSQLForTQI = "TRANSFORM max([tqivalue]) SELECT [FROMPOST],[FROMMINOR] FROM fix_tqi GROUP BY [FROMPOST],[FROMMINOR] PIVOT [tqimetricname]";
strSQLForTQI = "select * FROM fix_tqi";
}
DataTable dtDefectsData = new DataTable("defects");
OleDbDataAdapter dapt = new OleDbDataAdapter();
dapt.SelectCommand = new OleDbCommand();
dapt.SelectCommand.Connection = oleCn;
dapt.SelectCommand.CommandText = strSQLForDefects;
dapt.Fill(dtDefectsData);
DataTable dtTQIData = new DataTable("tqi");
dapt.SelectCommand.CommandText = strSQLForTQI;
dapt.Fill(dtTQIData);
UtilityDBDJ utility = new UtilityDBDJ();
//if (!bFxIsExists)
//{ strImpDes="TQI无效,-1009";}
int intImpID = utility.OperatorImpLog(intDataID, intRecordID, strFileName, strIICPath, strImpDes);
OracleConnection cn = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["OraConnStringDJ"].ConnectionString);
cn.Open();
//OracleTransaction transaction = cn.BeginTransaction();
OracleCommand cmdInner = new OracleCommand();
cmdInner.CommandType = CommandType.Text;
cmdInner.Connection = cn;
//cmdInner.Transaction = transaction;
try
{
UtilityDBDJ.CreateErrorLog("开始导入TQI数据!");
utility.OperatorTQI(cmdInner, detectRecordObj, dtUnitAreaConfig, dtTQIData, bFxIsExists, intImpID, intRecordID, intTaskID, intPlanID);
UtilityDBDJ.CreateErrorLog("完成导入TQI数据!");
UtilityDBDJ.CreateErrorLog("开始导入号为:" + intImpID.ToString() + "的文件");
utility.OperatorDefects(cmdInner, ts, detectRecordObj, dtUnitAreaConfig, dtDefectsData, intImpID, intRecordID, intTaskID, intPlanID);
UtilityDBDJ.CreateErrorLog("完成导入号为:" + intImpID.ToString() + "的文件");
UtilityDBDJ.CreateErrorLog("开始执行存储过程!");
utility.OperatorStore(cmdInner, intImpID, intRecordID, dtSystemConfig, 1);
utility.OperatorStore(cmdInner, intImpID, intRecordID, dtSystemConfig, 0);
UtilityDBDJ.CreateErrorLog("完成执行存储过程!");
//transaction.Commit();
utility.SetDetectDataManage(intDataID);
}
catch (Exception e)
{
//transaction.Rollback();
utility.SetImpLog(e.Message.ToString(), intImpID);
}
finally
{
cmdInner.Dispose();
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}
if (cn != null)
{
cn.Dispose();
}
}
}
}
public void OperatorDefects(OracleCommand cmd, IDictionary <string, Model.DecisionProjectObjDJ> ts, Model.DetectRecordObjDJ detectRecordObj, DataTable dtUnitAreaConfig, DataTable dtDefectsData, int intImpID, int intRecordID, int intTaskID, int intPlanID)
{
try
{
int intDectsionProjectID = 0;
int intDurationTime = 0;//偏差持续时间
string str = "CALL P_TBL00_DETECT_EXCEPTION";
//string strMain = "CALL P_TBL00_DETECT_EXCEPTION_MAIN";
Model.DecisionProjectObjDJ obj = null;
string strValue = string.Empty;
//int intNewRecordID = 0, intDetectType = 0;
//1、根据RecordID从record表 获得，detectdate、linecode，linedir、plan表中的type
//2、select TaskID from record表 where 检测日期<：detectdate and linecode== and linedir== and type==order by 检测日期 DESC
//cmd.CommandText = "select id RecordID, detectType from (" +
// " select r.id,linecode,linedir,p.type detectType from tbl00_detect_record r,tbl00_detect_plan p " +
// " where linecode = '" + detectRecordObj.LineCode + "' and r.linedir = '" + detectRecordObj.LineDir + "' and " +
// " r.detectdate < to_date('" + detectRecordObj.DetectDate.ToString("yyyy-MM-dd") + "','yyyy-MM-dd') " +
// " and r.planid = " + detectRecordObj.PlanID + " order by detectdate desc) " +
// "where rownum = 1";
//this.myRed = cmd.ExecuteReader();
//if (this.myRed.Read())
//{
// intNewRecordID = int.Parse(this.myRed["RecordID"].ToString());
// intDetectType = int.Parse(this.myRed["detectType"].ToString());
//}
//if (!this.myRed.IsClosed) this.myRed.Close();
string sYear = detectRecordObj.iXunCi.Substring(0, 4);
string sMonth = detectRecordObj.iXunCi.Substring(4, 2);
string sCi = detectRecordObj.iXunCi.Substring(7, 1);
foreach (DataRow row in dtDefectsData.Rows)
{
#region 内部循环遍历
float fMile = 0F;
int intRepeateTime = 0;//1重复;0未重复
int intDefectClass = int.Parse(row["defectclass"].ToString());
string strDefectType = row["defecttype"].ToString();
if (ts.TryGetValue(strDefectType, out obj))
{
intDectsionProjectID = obj.ProjectID;
}
else
{
intDectsionProjectID = 0;
continue;
}
float maxM = float.Parse(row["MAXMINOR"].ToString()) / 1000;
fMile = float.Parse(row["MAXPOST"].ToString()) + maxM;
float fFromPost = string.IsNullOrEmpty(row["frompost"].ToString()) ? 0F : float.Parse(row["frompost"].ToString());
float fFromMinor = string.IsNullOrEmpty(row["fromminor"].ToString()) ? 0F : float.Parse(row["fromminor"].ToString());
float fToPost = string.IsNullOrEmpty(row["topost"].ToString()) ? 0F : float.Parse(row["topost"].ToString());
float fToMinor = string.IsNullOrEmpty(row["tominor"].ToString()) ? 0F : float.Parse(row["tominor"].ToString());
//if (intDefectClass >= 2)
//{
// if (intNewRecordID != 0)
// intRepeateTime = SpExeFor(cmd, intNewRecordID, intDectsionProjectID, intDefectClass, detectRecordObj.LineCode, detectRecordObj.LineDir,
// detectRecordObj.DetectDate.ToString("yyyy-MM-dd"), intDetectType, fMile);
//}
//IMPID,RECORDID,TASKID,PLANID,DETECTTYPE,LINEID,LINEDIRECTION,DETECT_DATE,MAX_KM,MAX_M,EXCEPTIONITEMID,EXCEPTION_LEVEL,START_KM,START_M,END_KM,END_M,DURATION_TIME,DURATION_DISTANCE,
//EXCEPTION_VALUE,SPEEDSECTION,DETECTTRAINID,SPEED,TRACKLINETYPE,SEVERITY,TBCE,valid,DECISIONPROJECTID,REPEATTIME)
strValue = "(" + intImpID + "," + intRecordID + "," + intTaskID + "," + intPlanID + ",'" +
detectRecordObj.DetectType + "','" + detectRecordObj.LineCode + "','" + detectRecordObj.LineDir + "'," +
"'" + detectRecordObj.DetectDate.ToString("yyyy-MM-dd") + "'," + row["MAXPOST"] + "," + row["MAXMINOR"] + "," +
intDectsionProjectID + "," + row["defectclass"] + "," + fFromPost + "," + fFromMinor + "," +
fToPost + "," + fToMinor + "," + intDurationTime + ",'" + row["LENGTH"] + "'," + row["MAXVAL1"] + "," + row["postedspd"] + ",'" +
detectRecordObj.DetectTrain + "'," + row["speedatmaxval"] + ",'" + detectRecordObj.LineType + "'," +
row["severity"] + ",'" + row["TBCE"] + "'," + intDectsionProjectID + "," + intRepeateTime + ",'" + sYear + "','" +
sMonth + "','" + sCi + "')";
//基础偏差
this.strOra = str + strValue;
cmd.CommandText = this.strOra;;
cmd.ExecuteNonQuery();
#endregion
}
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
}
/// <summary>
/// IIC文件导入
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool IICDataImp(int id)
{
#region IIC文件导入
DataTable dtReadIICFiles = new DataTable();
DataSet ds = new DataSet();
IDictionary <string, Model.DecisionProjectObjDJ> ts = null;
IDictionary <int, Model.DetectRecordObjDJ> tsDetectRecord = null;
DataTable dtUnitAreaConfig = new DataTable("UnitAreaConfig");
DataTable dtSystemConfig = new DataTable("SystemConfig");
string strOra = "select * from tbl00_detect_data_manage t where completestatus = 1 and id=" + id;
using (OracleConnection cn = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["OraConnStringDJ"].ConnectionString))
{
try
{
OracleDataAdapter dap = new OracleDataAdapter();
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strOra;
cmd.Connection = cn;
dap.SelectCommand = cmd;
dap.Fill(ds);
strOra = "select id, unittypeid, unit_code, unit_name, line_code, line_id, line_dir, startmile, endmile, status from tbl00_dic_unit_areaconfig";
dap.SelectCommand.CommandText = strOra;
dap.Fill(dtUnitAreaConfig);
strOra = "select * from tbl00_system_config";
dap.SelectCommand.CommandText = strOra;
dap.Fill(dtSystemConfig);
strOra = "select exceptionid,exceptioncn,exceptionitem,exceptionen from tbl01_dic_01exceptiontype where exceptionid<200";
cmd.CommandText = strOra;
cn.Open();
OracleDataReader red = cmd.ExecuteReader();
ts = new Dictionary <string, Model.DecisionProjectObjDJ>();
while (red.Read())
{
Model.DecisionProjectObjDJ obj = new Model.DecisionProjectObjDJ
{
ProjectID = int.Parse(red["exceptionid"].ToString()),
MethodID = int.Parse(red["exceptionid"].ToString()),
ProjectList = red["exceptioncn"].ToString(),
ProjectDes = red["exceptionitem"].ToString(),
ProjectSourceName = red["exceptionen"].ToString()
};
ts.Add(obj.ProjectSourceName, obj);
}
if (!red.IsClosed)
{
red.Close();
}
strOra = "select d.detecttrain,c.linetype,b.type detecttype,a.detectid, a.taskid, a.planid, a.detectdate, " +
" a.lineid, a.linedirection, a.linename, a.rundirection, a.sys_train_id, '' as des,d.taskindex " +
" from pw_run_record a,tbl00_detect_plan b,tbl01_dic_06iiclinecode c,tbl00_detect_task d " +
" where a.planid=b.id and a.lineid=c.linecode and a.taskid=d.id";
cmd.CommandText = strOra;
red = cmd.ExecuteReader();
tsDetectRecord = new Dictionary <int, Model.DetectRecordObjDJ>();
while (red.Read())
{
Model.DetectRecordObjDJ obj = new Model.DetectRecordObjDJ
{
DetectTrain = red["detecttrain"].ToString(),
LineType = red["linetype"].ToString(),
DetectType = red["detecttype"].ToString(),
ID = int.Parse(red["detectid"].ToString()),
TaskID = int.Parse(red["taskid"].ToString()),
PlanID = int.Parse(red["planid"].ToString()),
DetectDate = DateTime.Parse(red["detectdate"].ToString()),
LineName = red["linename"].ToString(),
LineDir = red["linedirection"].ToString(),
TrainDir = red["rundirection"].ToString(),
LineCode = red["lineid"].ToString(),
TrainNumber = red["sys_train_id"].ToString(),
Des = red["des"].ToString(),
iXunCi = red["taskindex"].ToString()
};
tsDetectRecord.Add(obj.ID, obj);
}
if (!red.IsClosed)
{
red.Close();
}
}
catch (Exception ex)
{
string ss = ex.Message.ToString();
}
finally
{
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}
if (cn != null)
{
cn.Dispose();
}
}
}
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
try
{
foreach (DataRow row in ds.Tables[0].Rows)
{
string strIICPath = row["DATAPATH"].ToString();
string strIICFileName = row["DATANAME"].ToString();
int intDataID = int.Parse(row["ID"].ToString());
int intRecordID = int.Parse(row["RECORDID"].ToString());
int intTaskID = int.Parse(row["TASKID"].ToString());
int intPlanID = int.Parse(row["PLANID"].ToString());
if (tsDetectRecord.ContainsKey(intRecordID))
{
Model.DetectRecordObjDJ obj = tsDetectRecord[intRecordID];
ReadIIC(ts, obj, dtUnitAreaConfig, dtSystemConfig, intDataID, intRecordID, intTaskID, intPlanID, strIICPath, strIICFileName, string.Empty);
}
}
}
catch
{
return(false);
}
}
return(true);
#endregion
}
public void OperatorTQI(OracleCommand cmd, Model.DetectRecordObjDJ detectRecordObj, DataTable dtUnitAreaConfig, DataTable dtTQI, bool isFixIsExists, int intImpID, int intRecordID, int intTaskID, int intPlanID)
{
try
{
int intTQILength = 0;
int intUnitTypeID = 0;
string strUnitCode = "00";//单位编码
if (string.IsNullOrEmpty(strUnitCode))
{
strUnitCode = "00";
}
foreach (DataRow row in dtTQI.Rows)
{
string strMile = string.Empty;
float fMile = 0.0F;
if (!isFixIsExists)
{
float fMinor = float.Parse(row["FROMMINOR"].ToString());
if (fMinor >= 0.0F)
{
strMile = row["FROMPOST"] + "." + row["FROMMINOR"];
}
else
{
fMile = float.Parse(row["FROMPOST"].ToString()) + fMinor / 1000;
}
}
else
{
float fMinor = float.Parse(row["FromMinor"].ToString());
if (fMinor >= 0.0F)
{
strMile = row["FromPost"] + "." + row["FromMinor"];
}
else
{
fMile = float.Parse(row["FromPost"].ToString()) + fMinor / 1000;
}
}
try
{
if (!string.IsNullOrEmpty(strMile))
{
fMile = float.Parse(strMile);
}
}
catch (Exception)
{
//发生异常则将此条记录不导入！
continue;
}
DataRow[] drs = dtUnitAreaConfig.Select("line_code = '" + detectRecordObj.LineCode + "' and line_dir='" + detectRecordObj.LineDir + "' and startmile <= " + fMile + " and " + fMile + " < endmile", "unittypeid desc");
if (drs.Length >= 1)
{
intUnitTypeID = int.Parse(drs[0]["unittypeid"].ToString());
strUnitCode = GetUnitCode(int.Parse(drs[0]["unittypeid"].ToString()), drs[0]["unit_code"].ToString());
}
string sYear = detectRecordObj.iXunCi.Substring(0, 4);
string sMonth = detectRecordObj.iXunCi.Substring(4, 2);
string sCi = detectRecordObj.iXunCi.Substring(7, 1);
if (!isFixIsExists)
{
float fSTDLATACCEL = string.IsNullOrEmpty(row["STDLATACCEL"].ToString()) ? 0F : float.Parse(row["STDLATACCEL"].ToString());
float fSTDVERTACCEL = string.IsNullOrEmpty(row["STDVERTACCEL"].ToString()) ? 0F : float.Parse(row["STDVERTACCEL"].ToString());
float fMAXSPEED = string.IsNullOrEmpty(row["MAXSPEED"].ToString()) ? 0F : float.Parse(row["MAXSPEED"].ToString());
float fL_STDSURF = string.IsNullOrEmpty(row["L_STDSURF"].ToString()) ? 0F : float.Parse(row["L_STDSURF"].ToString());
float fR_STDSURF = string.IsNullOrEmpty(row["R_STDSURF"].ToString()) ? 0F : float.Parse(row["R_STDSURF"].ToString());
float fL_STDALIGNF = string.IsNullOrEmpty(row["L_STDALIGN"].ToString()) ? 0F : float.Parse(row["L_STDALIGN"].ToString());
float fR_STDALIGN = string.IsNullOrEmpty(row["R_STDALIGN"].ToString()) ? 0F : float.Parse(row["R_STDALIGN"].ToString());
float fSTDGAUGE = string.IsNullOrEmpty(row["STDGAUGE"].ToString()) ? 0F : float.Parse(row["STDGAUGE"].ToString());
float fSTDCROSSLEVEL = string.IsNullOrEmpty(row["STDCROSSLEVEL"].ToString()) ? 0F : float.Parse(row["STDCROSSLEVEL"].ToString());
float fSTDTWIST = string.IsNullOrEmpty(row["STDTWIST"].ToString()) ? 0F : float.Parse(row["STDTWIST"].ToString());
float fSTDSUMS = string.IsNullOrEmpty(row["STDSUMS"].ToString()) ? 0F : float.Parse(row["STDSUMS"].ToString());
this.strOra = "CALL P_TBL00_DETECT_TQI('" + detectRecordObj.DetectDate.ToString("yyyy-MM-dd") + "','" +
detectRecordObj.LineCode + "','" + detectRecordObj.LineDir + "'," + intImpID + "," + intRecordID + "," + intTaskID + "," + intPlanID + "," +
row["FROMPOST"] + "," + row["FROMMINOR"] + "," + intTQILength + "," + fL_STDSURF + "," + fR_STDSURF + "," +
fL_STDALIGNF + "," + fR_STDALIGN + "," + fSTDGAUGE + "," + fSTDCROSSLEVEL + "," + fSTDTWIST + "," +
fSTDSUMS + "," + fSTDLATACCEL + "," + fSTDVERTACCEL + "," + fMAXSPEED + ",'" + row["TBCE"] + "','" +
strUnitCode + "','" + intUnitTypeID + "','" + sYear + "','" + sMonth + "','" + sCi + "',1)";
}
else
{
//fix 的情况下
try
{
this.strOra = "CALL P_TBL00_DETECT_TQI('" + detectRecordObj.DetectDate.ToString("yyyy-MM-dd") + "','" +
detectRecordObj.LineCode + "','" + detectRecordObj.LineDir + "'," + intImpID + "," +
intRecordID + "," + intTaskID + "," + intPlanID + "," +
row["FromPost"] + "," + row["FromMinor"] + "," + intTQILength + "," +
row["L_Prof_Value"] + "," + row["R_Prof_Value"] + "," +
row["L_Align_Value"] + "," + row["R_Align_Value"] + "," + row["Gage_Value"] +
"," + row["Crosslevel_TQIValue"] + "," + row["ShortTwist_Value"] + "," +
row["TQISum_Value"] + "," + row["LATACCEL_Value"] + "," + row["VERTACCEL_Value"] + "," + row["AVERAGE_SPEED"] + ",'','" +
strUnitCode + "','" + intUnitTypeID + "','" + sYear + "','" + sMonth + "','" + sCi + "'," + row["valid"] + ")";
}
catch
{
this.strOra = "CALL P_TBL00_DETECT_TQI('" + detectRecordObj.DetectDate.ToString("yyyy-MM-dd") + "','" +
detectRecordObj.LineCode + "','" + detectRecordObj.LineDir + "'," + intImpID + "," +
intRecordID + "," + intTaskID + "," + intPlanID + "," +
row["FromPost"] + "," + row["FromMinor"] + "," + intTQILength + "," +
row["L_Prof_Value"] + "," + row["R_Prof_Value"] + "," +
row["L_Align_Value"] + "," + row["R_Align_Value"] + "," + row["Gage_Value"] +
"," + row["Crosslevel_TQIValue"] + "," + row["ShortTwist_Value"] + "," +
row["TQISum_Value"] + "," + row["LATACCEL_Value"] + "," + row["VERTACCEL_Value"] + ",0,'','" +
strUnitCode + "','" + intUnitTypeID + "','" + sYear + "','" + sMonth + "','" + sCi + "'," + row["valid"] + ")";
}
}
cmd.CommandText = this.strOra;
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
}
