public bool CalculateMainStreetIntersectionsUDI(int mainStID, int surveyNo, string user)
{
RemovePreviousCalculations(mainStID, surveyNo);
int rows = 0;
decimal DEDUCT_DEN_RAT = 0;
//decimal de_valu = 0;
string intersectDistressSQL = ""; //, maxSurveyDate = "";
DataTable dtIntersectSampleDistresses; //, dtExists; //, dtSurveyDate;
string sql = string.Format("SELECT intersection_id, Inter_no, inter_SAMP_ID, inter_SAMP_NO, interSEC_samp_AREA FROM GV_INTERSECTION_SAMPLES WHERE STREET_ID={0} and INTerSEC_samp_AREA<>0 order by Inter_no, inter_SAMP_NO ", mainStID);
Shared.LogStatment(sql); // MAIN_STREET_ID
DataTable dtIntersectSamples = db.ExecuteQuery(sql);
foreach (DataRow dr in dtIntersectSamples.Rows)
{
udi = new UdiRecord();
string intersection_id = dr["intersection_id"].ToString();
// STATUS, STATUS_UPD, ENTRY_DATE_UPD
intersectDistressSQL = string.Format("SELECT arname, inter_no, INTER_SAMP_NO, inter_SAMP_ID, SURVEY_NO, to_char(MAX(SURVEY_DATE), 'DD/MM/YYYY', 'NLS_CALENDAR=''GREGORIAN''') as Max_SURVEY_DATE, " +
" SUM(DEDUCT_DEN_DASH) DEDUCT_DEN_RAT, SUM(DIST_DENSITY) DEN, MAX(DEDUCT_VALUE) DE_VALUE, MAX(DEN_DASH) DEN_D, MAX(DIST_CODE) DIS_CODE " +
" FROM GV_inters_SMPL_DISTRESS WHERE inter_SAMP_ID={0} AND SURVEY_NO={1} GROUP BY arname, inter_no, INTER_SAMP_NO, inter_SAMP_ID, SURVEY_NO ",
dr["inter_SAMP_ID"].ToString(), surveyNo);
Shared.LogStatment(intersectDistressSQL);
dtIntersectSampleDistresses = db.ExecuteQuery(intersectDistressSQL);
if (dtIntersectSampleDistresses.Rows.Count == 0)
{
continue;
}
else
{
#region All Distresses
DataRow Ors = dtIntersectSampleDistresses.Rows[0];
DEDUCT_DEN_RAT = decimal.Parse(Ors["DEDUCT_DEN_RAT"].ToString());
if (DEDUCT_DEN_RAT >= 0 && DEDUCT_DEN_RAT <= 5)
{
udi = UdiShared.GetUDI(DEDUCT_DEN_RAT);
}
else if (DEDUCT_DEN_RAT > 5)
{
udi = UdiShared.GetUDI(decimal.Parse(Ors["DE_VALUE"].ToString()));
}
// INTER_NO='{0}' AND INTER_SAMP_NO='{1}' dr["inter_SAMP_No"].ToString(),
//sql = string.Format("SELECT INTER_NO, INTER_SAMP_NO, SURVEY_NO FROM UDI_INTERSECTION_SAMPLE WHERE inter_SAMP_ID={0} AND SURVEY_NO={1} ", dr["inter_SAMP_ID"].ToString(), surveyNo);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// sql = string.Format("update UDI_INTERSECTION_SAMPLE set INTER_NO='{0}', INTER_SAMP_NO='{1}', INTER_SAMP_AREA={2}, SURVEY_DATE=TO_DATE('{3}','DD/MM/YYYY'), " +
// " UDI_DATE=(select sysdate from dual), UDI_VALUE={4}, UDI_RATE='{5}', STREET_ID={6}, INTER_ID={7} where INTER_SAMP_ID={8} and SURVEY_NO={9} ",
// dr["INTER_NO"].ToString(), dr["inter_SAMP_No"].ToString(), dr["interSEC_samp_AREA"].ToString(), Ors["Max_SURVEY_DATE"].ToString(),
// udi.udiValue.ToString("N0"), udi.udiRate, mainStID, dr["intersection_id"].ToString(), dr["INTER_SAMP_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_INTERSECTION_SAMPLE WHERE inter_SAMP_ID={0} AND SURVEY_NO={1} ", dr["inter_SAMP_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// db.ExecuteNonQuery(sql);
// }
sql = string.Format("INSERT INTO UDI_INTERSECTION_SAMPLE(INTER_NO, INTER_SAMP_NO, INTER_SAMP_AREA, SURVEY_DATE, UDI_DATE, UDI_VALUE, UDI_RATE, SURVEY_NO, RECORD_ID, INTER_SAMP_ID, STREET_ID, INTER_ID) " +
" VALUES('{0}','{1}', {2}, TO_DATE('{3}','DD/MM/YYYY'), (select sysdate from dual), " +
" {4}, '{5}', {6}, SEQ_UDI_INTERSECTION_SAMPLE.nextval, {7}, {8}, {9}) ",
dr["INTER_NO"].ToString(), dr["inter_SAMP_No"].ToString(), dr["interSEC_samp_AREA"].ToString(), Ors["Max_SURVEY_DATE"].ToString(),
udi.udiValue.ToString("N0"), udi.udiRate, surveyNo, dr["INTER_SAMP_ID"].ToString(), mainStID, dr["intersection_id"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
#endregion
#region Patching Distresses
intersectDistressSQL = string.Format("SELECT arname, inter_no, INTER_SAMP_NO, inter_SAMP_ID, SURVEY_NO, to_char(MAX(SURVEY_DATE), 'DD/MM/YYYY', 'NLS_CALENDAR=''GREGORIAN''') as Max_SURVEY_DATE, " +
" SUM(DEDUCT_DEN_DASH) DEDUCT_DEN_RAT, SUM(DIST_DENSITY) DEN, MAX(DEDUCT_VALUE) DE_VALUE, MAX(DEN_DASH) DEN_D, MAX(DIST_CODE) DIS_CODE " +
" FROM GV_inters_SMPL_DISTRESS WHERE inter_SAMP_ID={0} AND SURVEY_NO={1} and DIST_CODE in (12, 13, 14, 15) " +
" GROUP BY arname, inter_no, INTER_SAMP_NO, inter_SAMP_ID, SURVEY_NO ",
dr["inter_SAMP_ID"].ToString(), surveyNo);
Shared.LogStatment(intersectDistressSQL);
dtIntersectSampleDistresses = db.ExecuteQuery(intersectDistressSQL);
if (dtIntersectSampleDistresses.Rows.Count == 0)
{
//continue;
double sampleArea = double.Parse(dr["interSEC_samp_AREA"].ToString());
udi = UdiShared.GetUDI(0);
}
else
{
Ors = dtIntersectSampleDistresses.Rows[0];
DEDUCT_DEN_RAT = decimal.Parse(Ors["DEDUCT_DEN_RAT"].ToString());
if (DEDUCT_DEN_RAT >= 0 && DEDUCT_DEN_RAT <= 5)
{
udi = UdiShared.GetUDI(DEDUCT_DEN_RAT);
}
else if (DEDUCT_DEN_RAT > 5)
{
udi = UdiShared.GetUDI(decimal.Parse(Ors["DE_VALUE"].ToString()));
}
}
if (udi.udiValue == -1)
{
continue;
}
// INTER_NO='{0}' AND INTER_SAMP_NO='{1}' dr["inter_SAMP_No"].ToString(),
//sql = string.Format("SELECT INTER_NO, INTER_SAMP_NO, SURVEY_NO FROM UDI_INTERSECT_SAMPLE_PATCHING WHERE inter_SAMP_ID={0} AND SURVEY_NO={1} ", dr["inter_SAMP_ID"].ToString(), surveyNo);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// sql = string.Format("update UDI_INTERSECT_SAMPLE_PATCHING set INTER_NO='{0}', INTER_SAMP_NO='{1}', INTER_SAMP_AREA={2}, SURVEY_DATE=TO_DATE('{3}','DD/MM/YYYY'), " +
// " UDI_DATE=(select sysdate from dual), UDI_VALUE={4}, UDI_RATE='{5}', STREET_ID={6}, INTER_ID={7} where INTER_SAMP_ID={8} and SURVEY_NO={9} ",
// dr["INTER_NO"].ToString(), dr["inter_SAMP_No"].ToString(), dr["interSEC_samp_AREA"].ToString(), Ors["Max_SURVEY_DATE"].ToString(),
// udi.udiValue.ToString("N0"), udi.udiRate, mainStID, dr["intersection_id"].ToString(), dr["INTER_SAMP_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_INTERSECT_SAMPLE_PATCHING WHERE inter_SAMP_ID={0} AND SURVEY_NO={1} ", dr["inter_SAMP_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// db.ExecuteNonQuery(sql);
// }
sql = string.Format("INSERT INTO UDI_INTERSECT_SAMPLE_PATCHING (INTER_NO, INTER_SAMP_NO, INTER_SAMP_AREA, SURVEY_DATE, UDI_DATE, UDI_VALUE, UDI_RATE, SURVEY_NO, RECORD_ID, INTER_SAMP_ID, STREET_ID, INTER_ID) " +
" VALUES('{0}','{1}', {2}, TO_DATE('{3}','DD/MM/YYYY'), (select sysdate from dual), " +
" {4}, '{5}', {6}, SEQ_UDI_INTERSECTION_SAMPLE.nextval, {7}, {8}, {9}) ",
dr["INTER_NO"].ToString(), dr["inter_SAMP_No"].ToString(), dr["interSEC_samp_AREA"].ToString(), Ors["Max_SURVEY_DATE"].ToString(),
udi.udiValue.ToString("N0"), udi.udiRate, surveyNo, dr["INTER_SAMP_ID"].ToString(), mainStID, dr["intersection_id"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
//}
#endregion
}
}
Shared.SaveLogfile("UDI", dtIntersectSamples.Rows.Count.ToString(), "UDI Calculation - Main Street Intersections:" + mainStID.ToString(), user);
//rows += CalculateIntersectionSamplesUDI(mainStID, surveyNo);
rows += CalculateIntersectionUDI(mainStID, surveyNo);
return(rows > 0);
}
public bool CalculateRegionSecondaryStreetsUDI(int regionID, int surveyNo, string user)
{
RemovePreviousCalculations(regionID, surveyNo);
int rows = 0;
decimal DEDUCT_DEN_RAT = 0;
DataTable dtSampleDistresses; //, dtExists;
DataRow drDist;
//string maxSurveyDate = ""; , dtSurveyDate
// decimal de_valu = 0; SECOND_ID
string sql = string.Format("SELECT REGION_NO, SUBDISTRICT, DIST_NAME, MUNIC_NAME, SECOND_ST_NO, STREET_ID, REGION_ID, SECOND_ST_LENGTH, SECOND_ST_WIDTH, " +
" round((SECOND_ST_LENGTH*SECOND_ST_WIDTH), 2) as SECONDARY_AREA FROM GV_SEC_STREET WHERE REGION_ID={0} and SECOND_ST_LENGTH<>0 and SECOND_ST_WIDTH<>0 " +
" order by lpad(SECOND_ST_NO,10) ", regionID);
Shared.LogStatment(sql);
DataTable dtRegionSecStreets = db.ExecuteQuery(sql);
foreach (DataRow dr in dtRegionSecStreets.Rows)
{
udi = new UdiRecord();
sql = string.Format("SELECT STREET_ID, SECOND_ST_NO, SURVEY_NO, region_no, SUBDISTRICT, DIST_NAME, MUNIC_NAME, " + // SECOND_ID
" to_char(MAX(SURVEY_DATE),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as MAX_SURVEY_DATE, SUM(DEDUCT_DEN_DASH) DEDUCT_DEN_RAT, SUM(DIST_DENSITY) DEN, " +
" MAX(DEDUCT_VALUE) DE_VALUE, MAX(DEN_DASH) DEN_D, MAX(DIST_CODE) DIS_CODE " +
" FROM GV_SEC_ST_DISTRESS WHERE STREET_ID={0} AND SURVEY_NO={1} " + // SECOND_ID
" GROUP BY STREET_ID, SECOND_ST_NO, SURVEY_NO, region_no, SUBDISTRICT, DIST_NAME, MUNIC_NAME ",
dr["STREET_ID"].ToString(), surveyNo);
Shared.LogStatment(sql);
dtSampleDistresses = db.ExecuteQuery(sql);
if (dtSampleDistresses.Rows.Count == 0)
{
continue;
}
#region UDI For Each Secondary Street for all distresses
drDist = dtSampleDistresses.Rows[0];
DEDUCT_DEN_RAT = decimal.Parse(drDist["DEDUCT_DEN_RAT"].ToString());
if (DEDUCT_DEN_RAT >= 0 && DEDUCT_DEN_RAT <= 5)
{
udi = UdiShared.GetUDI(DEDUCT_DEN_RAT);
}
else if (DEDUCT_DEN_RAT > 5)
{
udi = UdiShared.GetUDI(decimal.Parse(drDist["DE_VALUE"].ToString()));
}
if (udi.udiValue == -1)
{
continue;
}
//sql = string.Format("SELECT REGION_NO, SECONDARY_NO, SURVEY_NO FROM UDI_SECONDARY WHERE STREET_ID={0} AND SURVEY_NO={1} ", dr["STREET_ID"].ToString(), surveyNo);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// sql = string.Format("update UDI_SECONDARY set SURVEY_DATE=TO_DATE('{0}','DD/MM/YYYY'), SECONDARY_NO='{1}', SECONDARY_LENGTH={2}, SECONDARY_WIDTH={3}, " +
// " SECONDARY_AREA={4}, UDI_DATE=(select sysdate from dual), UDI_VALUE={5}, UDI_RATE='{6}', SUBDISTRICT='{7}', DIST_NAME='{8}', MUNIC_NAME='{9}' " +
// " where STREET_ID={10} and SURVEY_NO={11} ", // SECOND_ID
// drDist["MAX_SURVEY_DATE"].ToString(), dr["SECOND_ST_NO"].ToString(), dr["SECOND_ST_LENGTH"].ToString(), dr["SECOND_ST_WIDTH"].ToString(),
// dr["SECONDARY_AREA"].ToString(), udi.udiValue.ToString("N0"), udi.udiRate, dr["SUBDISTRICT"].ToString(),
// dr["DIST_NAME"].ToString(), dr["MUNIC_NAME"].ToString(), dr["STREET_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_SECONDARY WHERE STREET_ID={0} AND SURVEY_NO={1} ", dr["STREET_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// db.ExecuteNonQuery(sql);
// }
// ready to insert secondary street UDI
sql = string.Format("INSERT INTO UDI_SECONDARY(REGION_NO, SURVEY_DATE, SECONDARY_NO, SECONDARY_LENGTH, SECONDARY_WIDTH, SECONDARY_AREA, UDI_DATE, UDI_VALUE, UDI_RATE, SURVEY_NO, SUBDISTRICT, RECORD_ID, REGION_ID, STREET_ID, DIST_NAME, MUNIC_NAME) " +
" VALUES('{0}', TO_DATE('{1}','DD/MM/YYYY'), '{2}', {3}, " +
" {4}, {5}, (select sysdate from dual), {6}, '{7}', {8}, '{9}', SEQ_UDI_SECONDARY.nextval, " +
" {10}, {11}, '{12}', '{13}') ",
dr["Region_no"].ToString(), drDist["MAX_SURVEY_DATE"].ToString(), dr["SECOND_ST_NO"].ToString(), dr["SECOND_ST_LENGTH"].ToString(),
dr["SECOND_ST_WIDTH"].ToString(), dr["SECONDARY_AREA"].ToString(), udi.udiValue.ToString("N0"), udi.udiRate, surveyNo, dr["SUBDISTRICT"].ToString(),
regionID, dr["STREET_ID"].ToString(), dr["DIST_NAME"].ToString(), dr["MUNIC_NAME"].ToString()); // SECOND_ID
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
#endregion
#region UDI For Each Secondary Street for patching distresses
sql = string.Format("SELECT STREET_ID, SECOND_ST_NO, SURVEY_NO, region_no, SUBDISTRICT, DIST_NAME, MUNIC_NAME, " +
" to_char(MAX(SURVEY_DATE),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as MAX_SURVEY_DATE, SUM(DEDUCT_DEN_DASH) DEDUCT_DEN_RAT, SUM(DIST_DENSITY) DEN, " +
" MAX(DEDUCT_VALUE) DE_VALUE, MAX(DEN_DASH) DEN_D, MAX(DIST_CODE) DIS_CODE " +
" FROM GV_SEC_ST_DISTRESS WHERE STREET_ID={0} AND SURVEY_NO={1} and DIST_CODE in (12, 13, 14, 15) " + //SECOND_ID
" GROUP BY STREET_ID, SECOND_ST_NO, SURVEY_NO, region_no, SUBDISTRICT, DIST_NAME, MUNIC_NAME ",
dr["STREET_ID"].ToString(), surveyNo);
Shared.LogStatment(sql);
dtSampleDistresses = db.ExecuteQuery(sql);
if (dtSampleDistresses.Rows.Count == 0)
{
//continue;
double sampleArea = double.Parse(dr["SECONDARY_AREA"].ToString());
udi = UdiShared.GetUDI(0);
}
else
{
drDist = dtSampleDistresses.Rows[0];
DEDUCT_DEN_RAT = decimal.Parse(drDist["DEDUCT_DEN_RAT"].ToString());
if (DEDUCT_DEN_RAT >= 0 && DEDUCT_DEN_RAT <= 5)
{
udi = UdiShared.GetUDI(DEDUCT_DEN_RAT);
}
else if (DEDUCT_DEN_RAT > 5)
{
udi = UdiShared.GetUDI(decimal.Parse(drDist["DE_VALUE"].ToString()));
}
}
if (udi.udiValue == -1)
{
continue;
}
//sql = string.Format("SELECT REGION_NO, SECONDARY_NO, SURVEY_NO FROM UDI_SECONDARY_PATCHING WHERE STREET_ID={0} AND SURVEY_NO={1} ", dr["STREET_ID"].ToString(), surveyNo);
//dtExists = db.ExecuteQuery(sql); //SECOND_ID
//if (dtExists.Rows.Count == 1)
//{
// // update
// sql = string.Format("update UDI_SECONDARY_PATCHING set SURVEY_DATE=TO_DATE('{0}','DD/MM/YYYY'), SECONDARY_NO='{1}', SECONDARY_LENGTH={2}, SECONDARY_WIDTH={3}, " +
// " SECONDARY_AREA={4}, UDI_DATE=(select sysdate from dual), UDI_VALUE={5}, UDI_RATE='{6}', SUBDISTRICT='{7}', DIST_NAME='{8}', MUNIC_NAME='{9}' " +
// " where STREET_ID={10} and SURVEY_NO={11} ",
// drDist["MAX_SURVEY_DATE"].ToString(), dr["SECOND_ST_NO"].ToString(), dr["SECOND_ST_LENGTH"].ToString(), dr["SECOND_ST_WIDTH"].ToString(),
// dr["SECONDARY_AREA"].ToString(), udi.udiValue.ToString("N0"), udi.udiRate, dr["SUBDISTRICT"].ToString(),
// dr["DIST_NAME"].ToString(), dr["MUNIC_NAME"].ToString(), dr["STREET_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_SECONDARY_PATCHING WHERE STREET_ID={0} AND SURVEY_NO={1} ", dr["STREET_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// db.ExecuteNonQuery(sql);
// }
// ready to insert secondary street UDI
sql = string.Format("INSERT INTO UDI_SECONDARY_PATCHING (REGION_NO, SURVEY_DATE, SECONDARY_NO, SECONDARY_LENGTH, SECONDARY_WIDTH, SECONDARY_AREA, UDI_DATE, UDI_VALUE, UDI_RATE, SURVEY_NO, SUBDISTRICT, RECORD_ID, REGION_ID, STREET_ID, DIST_NAME, MUNIC_NAME) " +
" VALUES('{0}', TO_DATE('{1}','DD/MM/YYYY'), '{2}', {3}, {4}, " +
" {5}, (select sysdate from dual), {6}, '{7}', {8}, '{9}', SEQ_UDI_SECONDARY.nextval, {10}, {11}, " +
" '{12}', '{13}') ",
dr["Region_no"].ToString(), drDist["MAX_SURVEY_DATE"].ToString(), dr["SECOND_ST_NO"].ToString(), dr["SECOND_ST_LENGTH"].ToString(), dr["SECOND_ST_WIDTH"].ToString(),
dr["SECONDARY_AREA"].ToString(), udi.udiValue.ToString("N0"), udi.udiRate, surveyNo, dr["SUBDISTRICT"].ToString(), regionID, dr["STREET_ID"].ToString(),
dr["DIST_NAME"].ToString(), dr["MUNIC_NAME"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
//}
#endregion
}
rows += CalculateRegionUDI(regionID, surveyNo);
Shared.SaveLogfile("UDI", dtRegionSecStreets.Rows.Count.ToString(), "UDI Calculation - Region ID:" + regionID.ToString(), user);
return(rows > 0);
}
public bool CalculateMainStreetSectionsUDI(int mainStID, int surveyNo, string user)
{
if (new JpmmsClasses.BL.MainStreet().RemoveIRILength())
{
if (new JpmmsClasses.BL.MainStreet().InsertLengthDDF(mainStID.ToString(), surveyNo.ToString()))
{
new JpmmsClasses.BL.MainStreet().UpdateLengthSAMPLESOld();
}
}
//if (mainStID == 0 || surveyNo == 0)
// return false;
RemovePreviousCalculations(mainStID, surveyNo);
// retrieve main street samples
int rows = 0;
string sqlDistressesInfo = "";
double sampleArea = 0;
decimal DEDUCT_DEN_RAT = 0;
DataTable dtSampleDistressesInfo; //, dtExists;
DataRow dr;
string sql = string.Format("SELECT SECTION_ID, SECTION_NO, LANE_ID, LANE_TYPE, SAMPLE_ID, SAMPLE_NO, MUNICIPALITY, SAMPLE_LENGTH, SAMPLE_WIDTH, " +
" (SAMPLE_LENGTH*SAMPLE_WIDTH) as SAMPLE_AREA FROM GV_SAMPLES WHERE STREET_ID={0} and SAMPLE_LENGTH<>0 and SAMPLE_WIDTH<>0 " +
" order by SECTION_NO, LANE_TYPE, SAMPLE_NO ", mainStID); // main_st_id
//string sql = string.Format("SELECT * FROM GV_SAMPLESNEW WHERE STREET_ID={0} ", mainStID); // main_st_id
DataTable dtSamples = db.ExecuteQuery(sql);
Shared.LogStatment(sql);
if (dtSamples.Rows.Count == 0) //> 0)
{
return(false);
}
foreach (DataRow drSample in dtSamples.Rows)
{
udi = new UdiRecord();
// STATUS, STATUS_UPD, ENTRY_DATE_UPD ORDER BY SAMPLE_ID
sqlDistressesInfo = string.Format("SELECT section_no, arname, lane_type, sample_no, SAMPLE_ID, SURVEY_NO, " +
" to_char(max(SURVEY_DATE),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as MAX_SURVEY_DATE, SUM(DEDUCT_DEN_DASH) DEDUCT_DEN_RAT, " +
" SUM(DIST_DENSITY) DEN, MAX(DEDUCT_VALUE) DE_VALUE, MAX(DEN_DASH) DEN_D, MAX(DIST_CODE) DIS_CODE " +
" FROM GV_SAMPLE_DISTRESS WHERE SAMPLE_ID={0} AND SURVEY_NO={1} GROUP BY section_no, arname, lane_type, sample_no, SAMPLE_ID, SURVEY_NO ",
drSample["SAMPLE_ID"].ToString(), surveyNo);
Shared.LogStatment(sqlDistressesInfo);
dtSampleDistressesInfo = db.ExecuteQuery(sqlDistressesInfo);
if (dtSampleDistressesInfo.Rows.Count == 0)
{
continue;
}
else
{
#region All Distresses
dr = dtSampleDistressesInfo.Rows[0];
sampleArea = double.Parse(drSample["SAMPLE_AREA"].ToString());
DEDUCT_DEN_RAT = decimal.Parse(dr["DEDUCT_DEN_RAT"].ToString());
if (DEDUCT_DEN_RAT >= 0 && DEDUCT_DEN_RAT <= 5)
{
udi = UdiShared.GetUDI(DEDUCT_DEN_RAT);
}
else if (DEDUCT_DEN_RAT > 5)
{
udi = UdiShared.GetUDI(decimal.Parse(dr["DE_VALUE"].ToString()));
}
if (udi.udiValue == -1)
{
continue;
}
//sql = string.Format("SELECT SECTION_NO, LANE_TYPE, SAMPLE_NO, SURVEY_NO FROM UDI_SECTION_SAMPLE WHERE SAMPLE_ID={0} AND SURVEY_NO={1} ", drSample["SAMPLE_ID"].ToString(), surveyNo);
//Shared.LogStatment(sql);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// sql = string.Format("update UDI_SECTION_SAMPLE set SAMPLE_LENGTH={0}, SAMPLE_WIDTH={1}, SAMPLE_AREA={2}, SURVEY_DATE=TO_DATE('{3}','DD/MM/YYYY'), " +
// " UDI_DATE=(select sysdate from dual), UDI_VALUE={4}, UDI_RATE='{5}', MUNIC_NAME='{6}', LANE_ID={7}, STREET_ID={8}, SECTION_ID={9}, " +
// " SECTION_NO='{10}', LANE_TYPE='{11}', SAMPLE_NO='{12}' where SAMPLE_ID={13} and SURVEY_No={14} ", // MAIN_ST_ID
// drSample["SAMPLE_LENGTH"].ToString(), drSample["SAMPLE_WIDTH"].ToString(), sampleArea.ToString("0.00"), dr["MAX_SURVEY_DATE"].ToString(),
// udi.udiValue.ToString("N0"), udi.udiRate, drSample["MUNICIPALITY"].ToString(), drSample["LANE_ID"].ToString(), mainStID, drSample["SECTION_ID"].ToString(),
// drSample["SECTION_NO"].ToString(), drSample["LANE_TYPE"].ToString(), drSample["SAMPLE_NO"].ToString(), drSample["SAMPLE_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_SECTION_SAMPLE WHERE SAMPLE_ID={0} AND SURVEY_NO={1} ", drSample["SAMPLE_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// db.ExecuteNonQuery(sql);
// }
sql = string.Format("INSERT INTO UDI_SECTION_SAMPLE(SECTION_NO, LANE_TYPE, SAMPLE_NO, SAMPLE_LENGTH, SAMPLE_WIDTH, SAMPLE_AREA, SURVEY_DATE, UDI_DATE, UDI_VALUE, UDI_RATE, SURVEY_No, MUNIC_NAME, RECORD_ID, SAMPLE_ID, LANE_ID, STREET_ID, SECTION_ID) " +
" VALUES('{0}', '{1}', '{2}', {3}, " +
" {4}, {5}, TO_DATE('{6}','DD/MM/YYYY'), (select sysdate from dual), {7}, '{8}', " +
" {9}, '{10}', SEQ_UDI_SEC_SAMPLE.nextval, {11}, {12}, {13}, {14}) ",
drSample["SECTION_NO"].ToString(), drSample["LANE_TYPE"].ToString(), drSample["SAMPLE_NO"].ToString(), drSample["SAMPLE_LENGTH"].ToString(),
drSample["SAMPLE_WIDTH"].ToString(), sampleArea.ToString("0.00"), dr["MAX_SURVEY_DATE"].ToString(), udi.udiValue.ToString("N0"), udi.udiRate,
surveyNo, drSample["MUNICIPALITY"].ToString(), drSample["SAMPLE_ID"].ToString(), drSample["LANE_ID"].ToString(), mainStID, drSample["SECTION_ID"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
#endregion
#region Patching Distresses
sqlDistressesInfo = string.Format("SELECT section_no, arname, lane_type, sample_no, SAMPLE_ID, SURVEY_NO, " +
" to_char(max(SURVEY_DATE),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as MAX_SURVEY_DATE, " +
" SUM(DEDUCT_DEN_DASH) DEDUCT_DEN_RAT, SUM(DIST_DENSITY) DEN, MAX(DEDUCT_VALUE) DE_VALUE, MAX(DEN_DASH) DEN_D, MAX(DIST_CODE) DIS_CODE " +
" FROM GV_SAMPLE_DISTRESS WHERE SAMPLE_ID={0} AND SURVEY_NO={1} and DIST_CODE in (12, 13, 14, 15) " +
" GROUP BY section_no, arname, lane_type, sample_no, SAMPLE_ID, SURVEY_NO ",
drSample["SAMPLE_ID"].ToString(), surveyNo);
Shared.LogStatment(sqlDistressesInfo);
dtSampleDistressesInfo = db.ExecuteQuery(sqlDistressesInfo);
if (dtSampleDistressesInfo.Rows.Count == 0)
{
//continue;
sampleArea = double.Parse(drSample["SAMPLE_AREA"].ToString());
udi = UdiShared.GetUDI(0);
}
else
{
dr = dtSampleDistressesInfo.Rows[0];
sampleArea = double.Parse(drSample["SAMPLE_AREA"].ToString());
DEDUCT_DEN_RAT = decimal.Parse(dr["DEDUCT_DEN_RAT"].ToString());
if (DEDUCT_DEN_RAT >= 0 && DEDUCT_DEN_RAT <= 5)
{
udi = UdiShared.GetUDI(DEDUCT_DEN_RAT);
}
else if (DEDUCT_DEN_RAT > 5)
{
udi = UdiShared.GetUDI(decimal.Parse(dr["DE_VALUE"].ToString()));
}
}
if (udi.udiValue == -1)
{
continue;
}
//sql = string.Format("SELECT SECTION_NO, LANE_TYPE, SAMPLE_NO, SURVEY_NO FROM UDI_SECTION_SAMPLE_PATCHING WHERE SAMPLE_ID={0} AND SURVEY_NO={1} ",
// drSample["SAMPLE_ID"].ToString(), surveyNo);
//Shared.LogStatment(sql);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// sql = string.Format("update UDI_SECTION_SAMPLE_PATCHING set SAMPLE_LENGTH={0}, SAMPLE_WIDTH={1}, SAMPLE_AREA={2}, SURVEY_DATE=TO_DATE('{3}','DD/MM/YYYY'), " +
// " UDI_DATE=(select sysdate from dual), UDI_VALUE={4}, UDI_RATE='{5}', MUNIC_NAME='{6}', LANE_ID={7}, STREET_ID={8}, SECTION_ID={9}, " +
// " SECTION_NO='{10}', LANE_TYPE='{11}', SAMPLE_NO='{12}' where SAMPLE_ID={13} and SURVEY_No={14} ",
// drSample["SAMPLE_LENGTH"].ToString(), drSample["SAMPLE_WIDTH"].ToString(), sampleArea.ToString("0.00"), dr["MAX_SURVEY_DATE"].ToString(),
// udi.udiValue.ToString("N0"), udi.udiRate, drSample["MUNICIPALITY"].ToString(), drSample["LANE_ID"].ToString(), mainStID, drSample["SECTION_ID"].ToString(),
// drSample["SECTION_NO"].ToString(), drSample["LANE_TYPE"].ToString(), drSample["SAMPLE_NO"].ToString(), drSample["SAMPLE_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_SECTION_SAMPLE_PATCHING WHERE SAMPLE_ID={0} AND SURVEY_NO={1} ", drSample["SAMPLE_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// db.ExecuteNonQuery(sql);
// }
sql = string.Format("INSERT INTO UDI_SECTION_SAMPLE_PATCHING(SECTION_NO, LANE_TYPE, SAMPLE_NO, SAMPLE_LENGTH, SAMPLE_WIDTH, SAMPLE_AREA, SURVEY_DATE, UDI_DATE, UDI_VALUE, UDI_RATE, SURVEY_No, MUNIC_NAME, RECORD_ID, SAMPLE_ID, LANE_ID, STREET_ID, SECTION_ID) " +
" VALUES('{0}', '{1}', '{2}', {3}, " +
" {4}, {5}, TO_DATE('{6}','DD/MM/YYYY'), (select sysdate from dual), {7}, '{8}', " +
" {9}, '{10}', SEQ_UDI_SEC_SAMPLE.nextval, {11}, {12}, {13}, {14}) ",
drSample["SECTION_NO"].ToString(), drSample["LANE_TYPE"].ToString(), drSample["SAMPLE_NO"].ToString(), drSample["SAMPLE_LENGTH"].ToString(),
drSample["SAMPLE_WIDTH"].ToString(), sampleArea.ToString("0.00"), dr["MAX_SURVEY_DATE"].ToString(), udi.udiValue.ToString("N0"), udi.udiRate,
surveyNo, drSample["MUNICIPALITY"].ToString(), drSample["SAMPLE_ID"].ToString(), drSample["LANE_ID"].ToString(), mainStID, drSample["SECTION_ID"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
// }
#endregion
}
}
//rows += CalculateSectionSamplesUDI(mainStID, surveyNo);
rows += CalcualateLaneUDI(mainStID, surveyNo);
rows += CalculateSectionUDI(mainStID, surveyNo);
if (rows == 0)
{
Shared.SaveLogfile("MAINTENANCE_DECISIONS", mainStID.ToString(), "Non-complete survey - Section/Lane UDI is not calculated yet!", user);
}
else
{
Shared.SaveLogfile("UDI", dtSamples.Rows.Count.ToString(), "UDI Calculation - Main Street Sections:" + mainStID.ToString(), user);
}
// throw new Exception(); "لم يتم حساب معامل حالة الرصف بسبب عدم اكتمال مسح هذا الطريق"
return(rows > 0);
}