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); }