public int CalculateRegionUDI(int regionID, int surveyNo)
{
int rows = 0;
//DataTable dtExists;
udi = new UdiRecord();
# region For all distresses
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 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 int CalculateIntersectionUDI(int mainStID, int surveyNo)
{
int rows = 0;
DataRow drIntersectionRecord;
DataTable dtIntersectionRecord; //, dtExists;
string sql = string.Format("SELECT INTERSECTION_ID, INTER_NO, SUM(INTERSEC_SAMP_AREA) AS INTERSECTION_AREA, COUNT(distinct INTER_SAMP_NO) AS NO_OF_SAMPLE FROM GV_INTERSECTION_SAMPLES " +
" WHERE STREET_ID={0} and INTERSEC_SAMP_AREA is not null GROUP BY INTERSECTION_ID, INTER_NO order by Inter_no ", mainStID);
Shared.LogStatment(sql);
DataTable dtIntersections = db.ExecuteQuery(sql);
foreach (DataRow dr in dtIntersections.Rows)
{
udi = new UdiRecord();
#region All Distresses
sql = string.Format("SELECT INTER_NO, SURVEY_NO, to_char(MAX(SURVEY_DATE),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as MAX_SURVEY_DATE, " +
" to_char(MAX(udi_date),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as udidate, ROUND(AVG(udi_value)) udivalue " +
" FROM UDI_intersection_sample WHERE INTER_ID={0} AND SURVEY_NO={1} GROUP BY INTER_no, SURVEY_NO ", // INTER_NO='{0}'
dr["INTERSECTION_ID"].ToString(), surveyNo);
Shared.LogStatment(sql);
dtIntersectionRecord = db.ExecuteQuery(sql);
if (dtIntersectionRecord.Rows.Count > 0)
{
drIntersectionRecord = dtIntersectionRecord.Rows[0];
udi = UdiShared.GetUDIRatio(decimal.Parse(drIntersectionRecord["udivalue"].ToString()));
if (udi.udiValue == -1)
{
continue;
}
//sql = string.Format("SELECT INTER_NO, SURVEY_NO FROM UDI_INTERSECTION WHERE INTERSECTION_ID={0} AND SURVEY_NO={1} ", dr["INTERSECTION_ID"].ToString(), surveyNo);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// sql = string.Format("update UDI_INTERSECTION set INTER_NO='{0}', SURVEY_DATE=TO_DATE('{1}','DD/MM/YYYY'), INTERSECTION_AREA={2}, NO_OF_SAMPLES={3}, " +
// " UDI_DATE=to_date('{4}', 'DD/MM/YYYY'), UDI_VALUE={5}, UDI_RATE='{6}', STREET_ID={7} where INTERSECTION_ID={8} and SURVEY_NO={9} ",
// dr["INTER_NO"].ToString(), drIntersectionRecord["MAX_SURVEY_DATE"].ToString(), dr["INTERSECTION_AREA"].ToString(), dr["NO_OF_SAMPLE"].ToString(),
// drIntersectionRecord["udidate"].ToString(), udi.udiValue.ToString("N0"), udi.udiRate, mainStID, dr["INTERSECTION_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_INTERSECTION WHERE INTERSECTION_ID={0} AND SURVEY_NO={1} ", dr["INTERSECTION_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// db.ExecuteNonQuery(sql);
// }
sql = string.Format("INSERT INTO UDI_INTERSECTION(INTER_NO, SURVEY_DATE, INTERSECTION_AREA, NO_OF_SAMPLES, UDI_DATE, UDI_VALUE, UDI_RATE, SURVEY_NO, RECORD_ID, STREET_ID, INTERSECTION_ID) " +
" VALUES('{0}', TO_DATE('{1}','DD/MM/YYYY'), {2}, {3}, " +
" to_date('{4}', 'DD/MM/YYYY'), {5}, '{6}', {7}, SEQ_UDI_INTERSECTION.nextval, {8}, {9}) ",
dr["INTER_NO"].ToString(), drIntersectionRecord["MAX_SURVEY_DATE"].ToString(), dr["INTERSECTION_AREA"].ToString(), dr["NO_OF_SAMPLE"].ToString(),
drIntersectionRecord["udidate"].ToString(), udi.udiValue.ToString("N0"), udi.udiRate, surveyNo, mainStID, dr["INTERSECTION_ID"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
}
#endregion
#region Patching Distresses
sql = string.Format("SELECT INTER_NO, SURVEY_NO, to_char(MAX(SURVEY_DATE),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as MAX_SURVEY_DATE, " +
" to_char(MAX(udi_date),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as udidate, ROUND(AVG(udi_value)) udivalue " +
" FROM UDI_INTERSECT_SAMPLE_PATCHING WHERE INTER_ID={0} AND SURVEY_NO={1} GROUP BY INTER_no, SURVEY_NO ", // INTER_NO='{0}'
dr["INTERSECTION_ID"].ToString(), surveyNo);
Shared.LogStatment(sql);
dtIntersectionRecord = db.ExecuteQuery(sql);
if (dtIntersectionRecord.Rows.Count > 0)
{
drIntersectionRecord = dtIntersectionRecord.Rows[0];
udi = UdiShared.GetUDIRatio(decimal.Parse(drIntersectionRecord["udivalue"].ToString()));
if (udi.udiValue == -1)
{
continue;
}
//sql = string.Format("SELECT INTER_NO, SURVEY_NO FROM UDI_INTERSECTION_PATCHING WHERE INTERSECTION_ID={0} AND SURVEY_NO={1} ", dr["INTERSECTION_ID"].ToString(), surveyNo);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// sql = string.Format("update UDI_INTERSECTION_PATCHING set INTER_NO='{0}', SURVEY_DATE=TO_DATE('{1}','DD/MM/YYYY'), INTERSECTION_AREA={2}, NO_OF_SAMPLES={3}, " +
// " UDI_DATE=to_date('{4}', 'DD/MM/YYYY'), UDI_VALUE={5}, UDI_RATE='{6}', STREET_ID={7} where INTERSECTION_ID={8} and SURVEY_NO={9} ",
// dr["INTER_NO"].ToString(), drIntersectionRecord["MAX_SURVEY_DATE"].ToString(), dr["INTERSECTION_AREA"].ToString(), dr["NO_OF_SAMPLE"].ToString(),
// drIntersectionRecord["udidate"].ToString(), udi.udiValue.ToString("N0"), udi.udiRate, mainStID, dr["INTERSECTION_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_INTERSECTION_PATCHING WHERE INTERSECTION_ID={0} AND SURVEY_NO={1} ", dr["INTERSECTION_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// db.ExecuteNonQuery(sql);
// }
sql = string.Format("INSERT INTO UDI_INTERSECTION_PATCHING(INTER_NO, SURVEY_DATE, INTERSECTION_AREA, NO_OF_SAMPLES, UDI_DATE, UDI_VALUE, UDI_RATE, SURVEY_NO, RECORD_ID, STREET_ID, INTERSECTION_ID) " +
" VALUES('{0}', TO_DATE('{1}','DD/MM/YYYY'), {2}, {3}, " +
" to_date('{4}', 'DD/MM/YYYY'), {5}, '{6}', {7}, SEQ_UDI_INTERSECTION.nextval, {8}, {9}) ",
dr["INTER_NO"].ToString(), drIntersectionRecord["MAX_SURVEY_DATE"].ToString(), dr["INTERSECTION_AREA"].ToString(), dr["NO_OF_SAMPLE"].ToString(),
drIntersectionRecord["udidate"].ToString(), udi.udiValue.ToString("N0"), udi.udiRate, surveyNo, mainStID, dr["INTERSECTION_ID"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
}
#endregion
}
return(rows);
}
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);
}
public int CalculateSectionUDI(int mainStID, int surveyNo)
{
int rows = 0;
DataRow drLane;
DataTable dtSectionRecord; //, dtExists;
string sql = string.Format("SELECT SECTION_ID, SECTION_NO, SURVEY_NO, sum(NO_OF_SAMPLES) as NO_OF_SAMPLES, MUNIC_NAME FROM udi_laneS " +
" where STREET_ID={0} AND SURVEY_NO={1} group by SECTION_ID, SECTION_NO, SURVEY_NO, MUNIC_NAME ", mainStID, surveyNo);
Shared.LogStatment(sql); // MAIN_ST_ID
DataTable dtSections = db.ExecuteQuery(sql);
if (dtSections.Rows.Count == 0)
{
return(0);
}
foreach (DataRow drSection in dtSections.Rows)
{
udi = new UdiRecord();
#region All Distresses
sql = string.Format("SELECT SECTION_NO, SURVEY_NO, to_char(MAX(SURVEY_DATE),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as MAX_SURVEY_DATE, " +
" to_char(MAX(udi_date),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as udidate, ROUND(AVG(udi_value), 2) udi_value, SUM(LANE_AREA) AS SECTION_AREA, " +
" COUNT(LANE_TYPE) AS NO_OF_LANE, max(lane_length) as section_length, sum(lane_width) as section_width FROM UDI_LANES " +
" WHERE SECTION_NO='{0}' AND SURVEY_NO={1} GROUP BY SECTION_NO, SURVEY_NO ",
drSection["SECTION_NO"].ToString(), surveyNo);
Shared.LogStatment(sql);
dtSectionRecord = db.ExecuteQuery(sql);
if (dtSectionRecord.Rows.Count > 0)
{
drLane = dtSectionRecord.Rows[0];
udi = UdiShared.GetUDIRatio(decimal.Parse(drLane["udi_value"].ToString()));
if (udi.udiValue == -1)
{
continue;
}
//sql = string.Format("SELECT SECTION_NO, SURVEY_NO FROM UDI_SECTION WHERE SECTION_NO='{0}' AND SURVEY_NO={1} ", drSection["SECTION_NO"].ToString(), surveyNo);
//Shared.LogStatment(sql);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// sql = string.Format("update UDI_SECTION set SECTION_NO='{0}', SURVEY_DATE=TO_DATE('{1}','DD/MM/YYYY'), NO_OF_LANES={2}, NO_OF_SAMPLES={3}, " +
// " SECTION_AREA={4}, section_length={5}, section_width={6}, UDI_VALUE={7}, " +
// " UDI_DATE=to_date('{8}','DD/MM/YYYY'), UDI_RATE='{9}', MUNIC_NAME='{10}', STREET_ID={11} where SECTION_ID={12} and SURVEY_NO={13} ",
// drLane["SECTION_NO"].ToString(), drLane["MAX_SURVEY_DATE"].ToString(), drLane["NO_OF_LANE"].ToString(), drSection["NO_OF_SAMPLES"].ToString(),
// drLane["SECTION_AREA"].ToString(), drLane["section_length"].ToString(), drLane["section_width"].ToString(), udi.udiValue.ToString("N0"),
// drLane["udidate"].ToString(), udi.udiRate, drSection["MUNIC_NAME"].ToString(), mainStID, drSection["SECTION_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_SECTION WHERE SECTION_NO='{0}' AND SURVEY_NO={1} ", drSection["SECTION_NO"].ToString(), surveyNo);
// db.ExecuteNonQuery(sql);
// Shared.LogStatment(sql);
// }
sql = string.Format("INSERT INTO UDI_SECTION(RECORD_ID, SECTION_NO, SURVEY_DATE, NO_OF_LANES, NO_OF_SAMPLES, SECTION_AREA, section_length, section_width, UDI_VALUE, UDI_DATE, UDI_RATE, SURVEY_NO, MUNIC_NAME, STREET_ID, SECTION_ID) " +
" VALUES(SEQ_UDI_SECTION.nextval, '{0}', TO_DATE('{1}','DD/MM/YYYY'), {2}, {3}, " +
" {4}, {5}, {6}, {7}, " +
" to_date('{8}','DD/MM/YYYY'), '{9}', {10}, '{11}', {12}, {13}) ",
drLane["SECTION_NO"].ToString(), drLane["MAX_SURVEY_DATE"].ToString(), drLane["NO_OF_LANE"].ToString(), drSection["NO_OF_SAMPLES"].ToString(),
drLane["SECTION_AREA"].ToString(), drLane["section_length"].ToString(), drLane["section_width"].ToString(), udi.udiValue.ToString("N0"),
drLane["udidate"].ToString(), udi.udiRate, surveyNo, drSection["MUNIC_NAME"].ToString(), mainStID, drSection["SECTION_ID"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
}
#endregion
#region Patching Distresses
sql = string.Format("SELECT SECTION_NO, SURVEY_NO, to_char(MAX(SURVEY_DATE),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as MAX_SURVEY_DATE, " +
" to_char(MAX(udi_date),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as udidate, ROUND(AVG(udi_value), 2) udi_value, SUM(LANE_AREA) AS SECTION_AREA, " +
" COUNT(LANE_TYPE) AS NO_OF_LANE, max(lane_length) as section_length, sum(lane_width) as section_width " +
" FROM UDI_LANES_PATCHING WHERE SECTION_NO='{0}' AND SURVEY_NO={1} GROUP BY SECTION_NO, SURVEY_NO ",
drSection["SECTION_NO"].ToString(), surveyNo);
Shared.LogStatment(sql);
dtSectionRecord = db.ExecuteQuery(sql);
if (dtSectionRecord.Rows.Count > 0)
{
drLane = dtSectionRecord.Rows[0];
udi = UdiShared.GetUDIRatio(decimal.Parse(drLane["udi_value"].ToString()));
if (udi.udiValue == -1)
{
continue;
}
//sql = string.Format("SELECT SECTION_NO, SURVEY_NO FROM UDI_SECTION_PATCHING WHERE SECTION_NO='{0}' AND SURVEY_NO={1} ", drSection["SECTION_NO"].ToString(), surveyNo);
//Shared.LogStatment(sql);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// sql = string.Format("update UDI_SECTION_PATCHING set SECTION_NO='{0}', SURVEY_DATE=TO_DATE('{1}','DD/MM/YYYY'), NO_OF_LANES={2}, NO_OF_SAMPLES={3}, " +
// " SECTION_AREA={4}, section_length={5}, section_width={6}, UDI_VALUE={7}, " +
// " UDI_DATE=to_date('{8}','DD/MM/YYYY'), UDI_RATE='{9}', MUNIC_NAME='{10}', STREET_ID={11} where SECTION_ID={12} and SURVEY_NO={13} ",
// drLane["SECTION_NO"].ToString(), drLane["MAX_SURVEY_DATE"].ToString(), drLane["NO_OF_LANE"].ToString(), drSection["NO_OF_SAMPLES"].ToString(),
// drLane["SECTION_AREA"].ToString(), drLane["section_length"].ToString(), drLane["section_width"].ToString(), udi.udiValue.ToString("N0"),
// drLane["udidate"].ToString(), udi.udiRate, drSection["MUNIC_NAME"].ToString(), mainStID, drSection["SECTION_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_SECTION_PATCHING WHERE SECTION_NO='{0}' AND SURVEY_NO={1} ", drSection["SECTION_NO"].ToString(), surveyNo);
// db.ExecuteNonQuery(sql);
// Shared.LogStatment(sql);
// }
sql = string.Format("INSERT INTO UDI_SECTION_PATCHING (RECORD_ID, SECTION_NO, SURVEY_DATE, NO_OF_LANES, NO_OF_SAMPLES, SECTION_AREA, section_length, section_width, UDI_VALUE, UDI_DATE, UDI_RATE, SURVEY_NO, MUNIC_NAME, STREET_ID, SECTION_ID) " +
" VALUES(SEQ_UDI_SECTION.nextval, '{0}', TO_DATE('{1}','DD/MM/YYYY'), {2}, {3}, " +
" {4}, {5}, {6}, {7}, " +
" to_date('{8}','DD/MM/YYYY'), '{9}', {10}, '{11}', {12}, {13}) ",
drLane["SECTION_NO"].ToString(), drLane["MAX_SURVEY_DATE"].ToString(), drLane["NO_OF_LANE"].ToString(), drSection["NO_OF_SAMPLES"].ToString(),
drLane["SECTION_AREA"].ToString(), drLane["section_length"].ToString(), drLane["section_width"].ToString(), udi.udiValue.ToString("N0"),
drLane["udidate"].ToString(), udi.udiRate, surveyNo, drSection["MUNIC_NAME"].ToString(), mainStID, drSection["SECTION_ID"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
}
#endregion
}
return(rows);
}
public int CalcualateLaneUDI(int mainStID, int surveyNo)
{
int rows = 0;
DataRow sampleDr;
DataTable dtSampleRecord; //, dtExists;
string sectionNum, laneType;
string sql = string.Format("SELECT SECTION_ID, SECTION_NO, LANE_TYPE, LANE_ID, SURVEY_NO, MUNIC_NAME, COUNT(SAMPLE_NO) AS NO_OF_SAMPLES, SUM(SAMPLE_LENGTH*SAMPLE_WIDTH) AS LANE_AREA " +
" FROM udi_section_sample WHERE STREET_ID={0} AND SURVEY_NO={1} and lane_id is not null " +
" GROUP BY SECTION_ID, SECTION_NO, LANE_TYPE, LANE_ID, SURVEY_NO, MUNIC_NAME order by SECTION_NO, LANE_TYPE ", mainStID, surveyNo);
Shared.LogStatment(sql);
DataTable dtSamplesInfo = db.ExecuteQuery(sql);
if (dtSamplesInfo.Rows.Count == 0)
{
return(0);
}
foreach (DataRow drLane in dtSamplesInfo.Rows)
{
udi = new UdiRecord();
#region All Distresses
sectionNum = drLane["SECTION_NO"].ToString();
laneType = drLane["LANE_TYPE"].ToString();
sql = string.Format("SELECT SECTION_NO, LANE_TYPE, SURVEY_NO, to_char(MAX(SURVEY_DATE),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as MAX_SURVEY_DATE, " +
" to_char(MAX(udi_date),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as udidate, ROUND(AVG(udi_value), 2) udivalue, SUM(SAMPLE_LENGTH*SAMPLE_WIDTH) AS LANE_AREA, " +
" sum(sample_length) as lane_Length, round(Avg(sample_Width), 2) as lane_Width FROM udi_section_sample " +
" WHERE SECTION_NO='{0}' AND LANE_TYPE='{1}' AND SURVEY_NO={2} GROUP BY SECTION_NO, LANE_TYPE, SURVEY_NO ",
sectionNum, laneType, surveyNo);
Shared.LogStatment(sql);
dtSampleRecord = db.ExecuteQuery(sql);
if (dtSampleRecord.Rows.Count > 0)
{
sampleDr = dtSampleRecord.Rows[0];
udi = UdiShared.GetUDIRatio(decimal.Parse(sampleDr["udivalue"].ToString()));
if (udi.udiValue == -1)
{
continue;
}
//sql = string.Format("SELECT SECTION_NO, LANE_TYPE, SURVEY_NO FROM UDI_LANES WHERE SECTION_NO='{0}' AND LANE_TYPE='{1}' AND SURVEY_NO={2} ", sectionNum, laneType, surveyNo);
//Shared.LogStatment(sql);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// // update
// sql = string.Format("update UDI_laneS set SECTION_NO='{0}', SURVEY_DATE=TO_DATE('{1}', 'DD/MM/YYYY'), LANE_TYPE='{2}', NO_OF_SAMPLES={3}, " +
// " LANE_AREA={4}, lane_length={5}, lane_width={6}, UDI_DATE=to_date('{7}', 'DD/MM/YYYY'), " +
// " UDI_VALUE={8}, UDI_RATE='{9}', MUNIC_NAME='{10}', STREET_ID={11}, SECTION_ID={12} " +
// " where LANE_ID={13} and SURVEY_NO={14} ",
// sampleDr["SECTION_NO"].ToString(), sampleDr["MAX_SURVEY_DATE"].ToString(), drLane["LANE_TYPE"].ToString(), drLane["NO_OF_SAMPLES"].ToString(),
// sampleDr["LANE_AREA"].ToString(), sampleDr["lane_Length"].ToString(), sampleDr["lane_Width"].ToString(), sampleDr["udidate"].ToString(),
// udi.udiValue.ToString("N0"), udi.udiRate, drLane["MUNIC_NAME"].ToString(), mainStID, drLane["SECTION_ID"].ToString(),
// drLane["LANE_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_LANES WHERE SECTION_NO='{0}' AND LANE_TYPE='{1}' AND SURVEY_NO={2} ", sectionNum, laneType, surveyNo);
// Shared.LogStatment(sql);
// db.ExecuteNonQuery(sql);
// }
sql = string.Format("INSERT INTO UDI_laneS(RECORD_ID, SECTION_NO, SURVEY_DATE, LANE_TYPE, NO_OF_SAMPLES, LANE_AREA, lane_length, lane_width, UDI_DATE, UDI_VALUE, UDI_RATE, SURVEY_NO, MUNIC_NAME, LANE_ID, STREET_ID, SECTION_ID) " +
" VALUES(SEQ_UDI_LANE.nextval, '{0}', TO_DATE('{1}', 'DD/MM/YYYY'), '{2}', {3}, " +
" {4}, {5}, {6}, to_date('{7}', 'DD/MM/YYYY'), " +
" {8}, '{9}', {10}, '{11}', {12}, {13}, {14})",
sampleDr["SECTION_NO"].ToString(), sampleDr["MAX_SURVEY_DATE"].ToString(), drLane["LANE_TYPE"].ToString(), drLane["NO_OF_SAMPLES"].ToString(),
sampleDr["LANE_AREA"].ToString(), sampleDr["lane_Length"].ToString(), sampleDr["lane_Width"].ToString(), sampleDr["udidate"].ToString(),
udi.udiValue.ToString("N0"), udi.udiRate, surveyNo, drLane["MUNIC_NAME"].ToString(), drLane["LANE_ID"].ToString(), mainStID, drLane["SECTION_ID"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
}
#endregion
#region Patching Distresses
sectionNum = drLane["SECTION_NO"].ToString();
laneType = drLane["LANE_TYPE"].ToString();
sql = string.Format("SELECT SECTION_NO, LANE_TYPE, SURVEY_NO, to_char(MAX(SURVEY_DATE),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as MAX_SURVEY_DATE, " +
" to_char(MAX(udi_date),'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as udidate, ROUND(AVG(udi_value), 2) udivalue, SUM(SAMPLE_LENGTH*SAMPLE_WIDTH) AS LANE_AREA, " +
" sum(sample_length) as lane_Length, round(Avg(sample_Width), 2) as lane_Width FROM UDI_SECTION_SAMPLE_PATCHING " +
" WHERE SECTION_NO='{0}' AND LANE_TYPE='{1}' AND SURVEY_NO={2} GROUP BY SECTION_NO, LANE_TYPE, SURVEY_NO ",
sectionNum, laneType, surveyNo);
Shared.LogStatment(sql);
dtSampleRecord = db.ExecuteQuery(sql);
if (dtSampleRecord.Rows.Count > 0)
{
sampleDr = dtSampleRecord.Rows[0];
udi = UdiShared.GetUDIRatio(decimal.Parse(sampleDr["udivalue"].ToString()));
if (udi.udiValue == -1)
{
continue;
}
//sql = string.Format("SELECT SECTION_NO, LANE_TYPE, SURVEY_NO FROM UDI_LANES_PATCHING WHERE SECTION_NO='{0}' AND LANE_TYPE='{1}' AND SURVEY_NO={2} ", sectionNum, laneType, surveyNo);
//Shared.LogStatment(sql);
//dtExists = db.ExecuteQuery(sql);
//if (dtExists.Rows.Count == 1)
//{
// sql = string.Format("update UDI_LANES_PATCHING set SECTION_NO='{0}', SURVEY_DATE=TO_DATE('{1}', 'DD/MM/YYYY'), LANE_TYPE='{2}', NO_OF_SAMPLES={3}, " +
// " LANE_AREA={4}, lane_length={5}, lane_width={6}, UDI_DATE=to_date('{7}', 'DD/MM/YYYY'), " +
// " UDI_VALUE={8}, UDI_RATE='{9}', MUNIC_NAME='{10}', STREET_ID={11}, SECTION_ID={12} " +
// " where LANE_ID={13} and SURVEY_NO={14} ",
// sampleDr["SECTION_NO"].ToString(), sampleDr["MAX_SURVEY_DATE"].ToString(), drLane["LANE_TYPE"].ToString(), drLane["NO_OF_SAMPLES"].ToString(),
// sampleDr["LANE_AREA"].ToString(), sampleDr["lane_Length"].ToString(), sampleDr["lane_Width"].ToString(), sampleDr["udidate"].ToString(),
// udi.udiValue.ToString("N0"), udi.udiRate, drLane["MUNIC_NAME"].ToString(), mainStID, drLane["SECTION_ID"].ToString(),
// drLane["LANE_ID"].ToString(), surveyNo);
// Shared.LogStatment(sql);
// rows += db.ExecuteNonQuery(sql);
//}
//else
//{
// if (dtExists.Rows.Count > 1)
// {
// sql = string.Format("DELETE FROM UDI_LANES_PATCHING WHERE SECTION_NO='{0}' AND LANE_TYPE='{1}' AND SURVEY_NO={2} ", sectionNum, laneType, surveyNo);
// Shared.LogStatment(sql);
// db.ExecuteNonQuery(sql);
// }
sql = string.Format("INSERT INTO UDI_LANES_PATCHING(RECORD_ID, SECTION_NO, SURVEY_DATE, LANE_TYPE, NO_OF_SAMPLES, LANE_AREA, lane_length, lane_width, UDI_DATE, UDI_VALUE, UDI_RATE, SURVEY_NO, MUNIC_NAME, LANE_ID, STREET_ID, SECTION_ID) " +
" VALUES(SEQ_UDI_LANE.nextval, '{0}', TO_DATE('{1}', 'DD/MM/YYYY'), '{2}', {3}, " +
" {4}, {5}, {6}, to_date('{7}', 'DD/MM/YYYY'), " +
" {8}, '{9}', {10}, '{11}', {12}, {13}, {14})",
sampleDr["SECTION_NO"].ToString(), sampleDr["MAX_SURVEY_DATE"].ToString(), drLane["LANE_TYPE"].ToString(), drLane["NO_OF_SAMPLES"].ToString(),
sampleDr["LANE_AREA"].ToString(), sampleDr["lane_Length"].ToString(), sampleDr["lane_Width"].ToString(), sampleDr["udidate"].ToString(),
udi.udiValue.ToString("N0"), udi.udiRate, surveyNo, drLane["MUNIC_NAME"].ToString(), drLane["LANE_ID"].ToString(), mainStID, drLane["SECTION_ID"].ToString());
Shared.LogStatment(sql);
rows += db.ExecuteNonQuery(sql);
//}
}
#endregion
}
return(rows);
}
