public DataTable GetMainStreetIriSurveys(string mainNo, bool isIntersection, bool allRoads, bool allIntersects) { if (!(allRoads || allIntersects) && (string.IsNullOrEmpty(mainNo) || mainNo == "0")) { return(new DataTable()); } string sql = ""; if (isIntersection) { sql = string.Format("SELECT SURVEY_NO, max(SURVEY_DATE) as SURVEY_DATE, (SURVEY_NO || ' - ' || to_char(max(SURVEY_DATE), 'dd/mm/yyyy')) as survey_title FROM iri_INTERSECTION WHERE MAIN_NO ='{0}' GROUP BY SURVEY_NO order by SURVEY_DATE desc ", mainNo); } else if (allRoads) { //sql = "SELECT SURVEY_NO, max(SURVEY_DATE), (SURVEY_NO || ' - ' || to_char(max(SURVEY_DATE), 'dd/mm/yyyy')) as survey_title FROM iri_LANE GROUP BY SURVEY_NO "; sql = "select distinct survey_no, survey_no as survey_title from iri_lane order by SURVEY_no desc "; //survey_no "; } else if (allIntersects) { sql = "select distinct survey_no, survey_no as survey_title from iri_INTERSECTION order by SURVEY_no desc "; //survey_no "; } else { sql = string.Format("SELECT SURVEY_NO, max(SURVEY_DATE) as SURVEY_DATE, (SURVEY_NO || ' - ' || to_char(max(SURVEY_DATE), 'dd/mm/yyyy')) as survey_title FROM iri_LANE WHERE MAIN_NO ='{0}' GROUP BY SURVEY_NO order by SURVEY_DATE desc ", mainNo); } return((string.IsNullOrEmpty(sql)) ? new DataTable() : db.ExecuteQuery(sql)); }
public bool Insert(int DIST_CODE, char DIST_SEVER, int low_dens_maint_dec, int med_dens_maint_dec, int high_dens_maint_dec) { int rows = 0; string sql = string.Format("select * from MAINT_DECIDING where DIST_CODE={0} and DIST_SEVER='{1}' ", DIST_CODE, DIST_SEVER); if (db.ExecuteQuery(sql).Rows.Count > 0) { throw new Exception(Feedback.InsertExceptionUnique()); } // 0 1 2 3 4 sql = string.Format("insert into MAINT_DECIDING(RECORD_ID, DIST_CODE, DIST_SEVER, DENSITY_FROM, DENSITY_TO, MAINT_DEC_ID) " + " values(SEQ_MAINT_DECIDING.nextval, {0}, '{1}', {2}, {3}, {4})", DIST_CODE, DIST_SEVER, 0, 10, low_dens_maint_dec); rows += db.ExecuteNonQuery(sql); sql = string.Format("insert into MAINT_DECIDING(RECORD_ID, DIST_CODE, DIST_SEVER, DENSITY_FROM, DENSITY_TO, MAINT_DEC_ID) " + " values(SEQ_MAINT_DECIDING.nextval, {0}, '{1}', {2}, {3}, {4})", DIST_CODE, DIST_SEVER, 11, 50, med_dens_maint_dec); rows += db.ExecuteNonQuery(sql); sql = string.Format("insert into MAINT_DECIDING(RECORD_ID, DIST_CODE, DIST_SEVER, DENSITY_FROM, DENSITY_TO, MAINT_DEC_ID) " + " values(SEQ_MAINT_DECIDING.nextval, {0}, '{1}', {2}, {3}, {4})", DIST_CODE, DIST_SEVER, 51, 100, high_dens_maint_dec); rows += db.ExecuteNonQuery(sql); return(rows > 0); }
public PavementStatusReport GetAllPavementStatusTotals() { //OracleDatabaseClass db = new OracleDatabaseClass(); string sql = "select sum(TOTAL_AREA) as TOTAL_AREA, sum(MAIN_ST_TOTAL_AREA) as MAIN_ST_TOTAL_AREA, sum(INTERSECT_TOTAL_AREA) as INTERSECT_TOTAL_AREA, sum(EXCELLENT_AREA) as EXCELLENT_AREA, sum(FAIR_AREA) as FAIR_AREA, sum(GOOD_AREA) as GOOD_AREA, sum(POOR_AREA) as POOR_AREA from VW_MAINST_SURVEYED_TOTAL ";//VW_MAINST_PAVEMNT_STATUS DataTable dtMainSt = db.ExecuteQuery(sql); sql = "select sum(EXCELLENT_AREA) as EXCELLENT_AREA, sum(FAIR_AREA) as FAIR_AREA, sum(GOOD_AREA) as GOOD_AREA, sum(POOR_AREA) as POOR_AREA, sum(TOTAL_AREA) as TOTAL_AREA from VW_REGION_PAVEMENT_STATUS "; DataTable dtRegions = db.ExecuteQuery(sql); if (dtMainSt.Rows.Count == 0 || dtRegions.Rows.Count == 0) { return(new PavementStatusReport()); } else { DataRow drM = dtMainSt.Rows[0]; DataRow drR = dtRegions.Rows[0]; return(new PavementStatusReport(double.Parse(drM["MAIN_ST_TOTAL_AREA"].ToString()), double.Parse(drM["INTERSECT_TOTAL_AREA"].ToString()), double.Parse(drM["TOTAL_AREA"].ToString()), double.Parse(drR["TOTAL_AREA"].ToString()), 0, double.Parse(drM["EXCELLENT_AREA"].ToString()), double.Parse(drM["FAIR_AREA"].ToString()), double.Parse(drM["GOOD_AREA"].ToString()), double.Parse(drM["POOR_AREA"].ToString()), double.Parse(drR["EXCELLENT_AREA"].ToString()), double.Parse(drR["FAIR_AREA"].ToString()), double.Parse(drR["GOOD_AREA"].ToString()), double.Parse(drR["POOR_AREA"].ToString()))); } }
//public void UpdateNoDistressesSample(int surveyNo) //{ // if (surveyNo == 0) // return; // string sql = string.Format("UPDATE DISTRESS SET DIST_CODE=0, DIST_SEVERITY='N', dist_area=0 ,DIST_CODE_UPD=0, DIST_SEVERITY_upd='N', dist_area_UPD=0 WHERE DIST_CODE is null AND DIST_SEVERITY is null and STATUS='N' AND SURVEY_NO={0} ", surveyNo); // db.ExecuteNonQuery(sql); //} public double GetSamplePatchesArea(int ID, JobType jobType) { string sql = ""; switch (jobType) { case JobType.Section: sql = string.Format("select nvl(sum(dist_area), 0) as sum_dist_area from distress where dist_code=4 and SAMPLE_ID={0} ", ID); break; case JobType.Intersection: sql = string.Format("select nvl(sum(dist_area), 0) as sum_dist_area from distress where dist_code=4 and INTER_SAMP_ID={0} ", ID); break; case JobType.RegionSecondaryStreets: sql = string.Format("select nvl(sum(dist_area), 0) as sum_dist_area from distress where dist_code=4 and STREET_ID={0} ", ID); // SECOND_ID break; } if (!string.IsNullOrEmpty(sql)) { DataTable dt = db.ExecuteQuery(sql); return((dt.Rows.Count > 0) ? double.Parse(dt.Rows[0]["sum_dist_area"].ToString()) : 0); } else { return(0); } }
public bool InsertElementEvaluation(int BRIDGE_ID, int TUNNEL_ID, int ELEMENT_ID, int EVAL_ID, string DETAILS) { if (BRIDGE_ID == 0 && TUNNEL_ID == 0) { return(false); } string bridgeIDPart = (BRIDGE_ID == 0) ? "NULL" : BRIDGE_ID.ToString(); string tunnelIDPart = (TUNNEL_ID == 0) ? "NULL" : TUNNEL_ID.ToString(); string typeColumn = (BRIDGE_ID == 0) ? "TUNNEL_ID" : "BRIDGE_ID"; string valueColumn = (BRIDGE_ID == 0) ? TUNNEL_ID.ToString() : BRIDGE_ID.ToString(); DETAILS = string.IsNullOrEmpty(DETAILS) ? "NULL" : string.Format("'{0}'", DETAILS.Replace("'", "''")); string sql = string.Format("select * from BRIDGE_EVALUATION where {0}={1} and ELEMENT_ID={2} and EVAL_ID={3} ", typeColumn, valueColumn, ELEMENT_ID, EVAL_ID); DataTable dt = db.ExecuteQuery(sql); if (dt.Rows.Count == 0) { // 0 1 2 3 4 sql = string.Format("insert into BRIDGE_EVALUATION(RECORD_ID, BRIDGE_ID, TUNNEL_ID, ELEMENT_ID, EVAL_ID, DETAILS) values(SEQ_EVALUATION.nextval, {0}, {1}, {2}, {3}, {4})", bridgeIDPart, tunnelIDPart, ELEMENT_ID, EVAL_ID, DETAILS); int rows = db.ExecuteNonQuery(sql); return(rows > 0); } else { throw new Exception(Feedback.InsertExceptionUnique()); } }
public DataTable GetMainStreetIriSurveys(string mainNo, bool allRoads, bool intersect, bool allintersect) { string sql = ""; if (allintersect) { sql = "SELECT SURVEY_NO, max(SURVEY_DATE) as SURVEY_DATE, (SURVEY_NO || ' - ' || to_char(max(SURVEY_DATE), 'dd/mm/yyyy')) as survey_title FROM RUTTING_SECTIONS GROUP BY SURVEY_NO order by SURVEY_DATE desc "; } else { if (!allRoads && (string.IsNullOrEmpty(mainNo) || mainNo == "0")) { return(new DataTable()); } if (intersect) { sql = string.Format("SELECT SURVEY_NO, max(SURVEY_DATE) as SURVEY_DATE, (SURVEY_NO || ' - ' || to_char(max(SURVEY_DATE), 'dd/mm/yyyy')) as survey_title FROM RUTTING_INTERSECTIONS WHERE MAIN_NO='{0}' GROUP BY SURVEY_NO order by SURVEY_DATE desc ", mainNo); } else if (allRoads) { sql = "SELECT SURVEY_NO, max(SURVEY_DATE) as SURVEY_DATE, (SURVEY_NO || ' - ' || to_char(max(SURVEY_DATE), 'dd/mm/yyyy')) as survey_title FROM RUTTING_SECTIONS GROUP BY SURVEY_NO order by SURVEY_DATE desc "; } else { sql = string.Format("SELECT SURVEY_NO, max(SURVEY_DATE) as SURVEY_DATE, (SURVEY_NO || ' - ' || to_char(max(SURVEY_DATE), 'dd/mm/yyyy')) as survey_title FROM RUTTING_SECTIONS WHERE MAIN_NO='{0}' GROUP BY SURVEY_NO order by SURVEY_DATE desc ", mainNo); } } return((string.IsNullOrEmpty(sql)) ? new DataTable() : db.ExecuteQuery(sql)); }
public DataTable GetSurveyorsInterSections() { string sql = @"SELECT SURVEYOR_NO, SURVEYOR_NAME, SURVEYOR_WORK_ENDDATE, SURVEYOR_WORK_STARTDATE, SURVEYOR_PHONE_NO, SUSPENDED, SUSPENDED_AR FROM VW_SURVEYORS where SURVEYOR_NO in (11,12,44) order by SURVEYOR_NAME "; return(db.ExecuteQuery(sql)); }
public DataTable GetSettings() { string sql = "SELECT RECORD_ID, MAIN_ST_GOOD_WEIGHT, MAIN_ST_POOR_WEIGHT, SECOND_ST_GOOD_WEIGHT, SECOND_ST_POOR_WEIGHT, HOUSES_SECTIONS, COMMERIAL_SECTIONS, " + " INDISTERIAL_SECTIONS, REST_HOUSE_SECTIONS, GARDENS_SECTIONS, PUBLICS_SECTIONS, HOUSES_REGIONS, COMMERIAL_REGIONS, INDISTERIAL_REGIONS, REST_HOUSE_REGIONS, " + " GARDENS_REGIONS, PUBLICS_REGIONS, SCHOOL_REGIONS, MASJID_REGIONS, HOSPITAL_REGIONS, SPORT_CLUB_REGIONS, NEW_BUILDINGS_REGIONS, OTHER_UTIL_REGIONS, " + " TRAFFIC_LOW_WEIGHT, TRAFFIC_MEDIUM_WEIGHT, TRAFFIC_HIGH_WEIGHT, TRAFFIC_VERY_HIGH_WEIGHT FROM MAINT_PRIO_WEIGHTS "; return(db.ExecuteQuery(sql)); }
//public DataTable GetTrafficCountingForMainStreet() //{ // string sql = "SELECT * FROM traffic_counting_street ORDER BY arname, SEC_DIRECTION, SEC_ORDER, SECTION_NO "; // return db.ExecuteQuery(sql); //} public DataTable GetTrafficCountingForMainStreet(int mainStreetID) { //if (mainStreetID == 0) // return new DataTable(); string streetPart = (mainStreetID == 0) ? "" : string.Format(" where STREET_ID={0} ", mainStreetID); string sql = string.Format("SELECT * FROM traffic_counting_street {0} ORDER BY SEC_DIRECTION, SEC_ORDER, SECTION_NO ", streetPart); return(db.ExecuteQuery(sql)); }
public DataTable GetTunnelInfo(int TUNNEL_ID) { if (TUNNEL_ID == 0) { return(new DataTable()); } string sql = string.Format("select * from VW_TUNNEL_FULL_INFO where TUNNEL_ID={0} ", TUNNEL_ID); return(db.ExecuteQuery(sql)); }
public DataTable GetSectionBridges(int sectionID) { if (sectionID == 0) { return(new DataTable()); } string sql = string.Format("select * from VW_BRIDGE_FULL_INFO where SECTION_ID={0} ", sectionID); return(db.ExecuteQuery(sql)); }
public DataTable GetLaneSamples(int laneID) { //if (string.IsNullOrEmpty(laneID)) if (laneID == 0) { return(new DataTable()); } string sql = string.Format("SELECT SAMPLE_NO, SAMPLE_LENGTH, SAMPLE_WIDTH, (nvl(SAMPLE_LENGTH, 0) * nvl(SAMPLE_WIDTH, 0)) AS AREA, SAMPLE_ID, NOTES FROM LANE_SAMPLES where lane_id={0} ORDER BY lpad(SAMPLE_NO, 10) ", laneID); return(db.ExecuteQuery(sql)); }
public DataTable GetIntersectionSamples(int intersectionID) { if (intersectionID == 0) { return(new DataTable()); } string sql = string.Format(@"SELECT INTER_SAMP_ID, INTER_SAMP_NO, INTERSEC_SAMP_AREA, INTER_NO, NOTES, (select count(STREET_ID) TotaDISTRESS from jpmms.DISTRESS xd where xd.INTERSECTION_ID=S.INTERSECTION_ID and xd.INTER_SAMP_ID=S.INTER_SAMP_ID and xd.SURVEY_NO=(select max(SURVEY_NO) from jpmms.DISTRESS xd where xd.INTERSECTION_ID=S.INTERSECTION_ID and xd.INTER_SAMP_ID=S.INTER_SAMP_ID) group by INTERSECTION_ID ) DISTRESS FROM INTERSECTION_samples s where INTERSECTION_ID={0} ORDER BY INTER_SAMP_NO ", intersectionID); return(db.ExecuteQuery(sql)); }
public DataTable GetImages(int ID, RoadType type) { if (ID == 0) { return(new DataTable()); } string sql1 = ""; string sql2 = ""; string sql = ""; switch (type) { case RoadType.Section: sql1 = string.Format("SELECT RECORD_ID, PHOTO_NAME, DETAILS FROM PHOTOS where SECTION_ID={0} ", ID); sql2 = string.Format("select d.DIST_ID as RECORD_ID, d.DISTRESS_IMAGE as PHOTO_NAME, dc.DISTRESS_AR_TYPE as DETAILS from DISTRESS d, distress_code dc where d.dist_code=dc.dist_code and SECTION_ID={0} and DISTRESS_IMAGE is not null ", ID); break; case RoadType.Intersect: sql1 = string.Format("SELECT RECORD_ID, PHOTO_NAME, DETAILS FROM PHOTOS where INTER_ID={0} ", ID); sql2 = string.Format("select d.DIST_ID as RECORD_ID, d.DISTRESS_IMAGE as PHOTO_NAME, dc.DISTRESS_AR_TYPE as DETAILS from DISTRESS d, distress_code dc where d.dist_code=dc.dist_code and INTERSECTION_ID={0} and DISTRESS_IMAGE is not null ", ID); break; case RoadType.RegionSecondarySt: sql1 = string.Format("SELECT RECORD_ID, PHOTO_NAME, DETAILS FROM PHOTOS where STREET_ID in (select STREET_ID from STREETS where REGION_ID={0}) ", ID); sql2 = string.Format("select d.DIST_ID as RECORD_ID, d.DISTRESS_IMAGE as PHOTO_NAME, dc.DISTRESS_AR_TYPE as DETAILS from DISTRESS d, distress_code dc where d.dist_code=dc.dist_code and REGION_ID={0} and DISTRESS_IMAGE is not null ", ID); //sql1 = string.Format("SELECT RECORD_ID, PHOTO_NAME, DETAILS FROM PHOTOS where SECOND_ID in (select SECOND_ID from SECONDARY_STREETS where REGION_ID={0}) ", ID); //sql2 = string.Format("select d.DIST_ID as RECORD_ID, d.DISTRESS_IMAGE as PHOTO_NAME, dc.DISTRESS_AR_TYPE as DETAILS from DISTRESS d, distress_code dc where d.dist_code=dc.dist_code and REGION_ID={0} and DISTRESS_IMAGE is not null ", ID); break; case RoadType.MainStreet: sql1 = string.Format("SELECT RECORD_ID, PHOTO_NAME, DETAILS FROM PHOTOS where SECTION_ID in (select section_id from sections where STREET_ID={0}) or INTER_ID in (select INTERSECTION_ID from INTERSECTIONS where STREET_ID={0}) ", ID); // MAIN_STREET_ID sql2 = string.Format("select d.DIST_ID as RECORD_ID, d.DISTRESS_IMAGE as PHOTO_NAME, dc.DISTRESS_AR_TYPE as DETAILS from DISTRESS d, distress_code dc " + " where d.dist_code=dc.dist_code and (SECTION_NO in (select section_no from sections where STREET_ID={0}) " + " or INTER_NO in (select inter_no from INTERSECTIONS where STREET_ID={0})) and DISTRESS_IMAGE is not null ", ID); // MAIN_STREET_ID break; default: break; } if (!string.IsNullOrEmpty(sql1) && !string.IsNullOrEmpty(sql2)) { sql = string.Format("{0} union {1} ", sql1, sql2); return(db.ExecuteQuery(sql)); } else { return(new DataTable()); } }
public DataTable SkidReportForMainStreet(string mainNo, bool isIntersection, bool allSections, bool allIntersects, string SURVEYNO) { if (!(allSections || allIntersects) && (string.IsNullOrEmpty(mainNo) || mainNo == "0")) { return(new DataTable()); } string sql = ""; mainNo = mainNo.Replace("'", "''"); if (isIntersection) //intersection_order, { sql = string.Format("SELECT * FROM SKID_JEDDAH_int_TEST where SURVEY_NO={1} and MAIN_No='{0}' AND INTERSECTION_NO IS NOT NULL order by intersection_no, direction ", mainNo, SURVEYNO); } else if (allSections) { sql = string.Format("SELECT * FROM SKID_JEDDAH where SURVEY_NO={0} and SECTION_NO IS NOT NULL order by main_no, sec_direction, sec_order, section_no, distance ", SURVEYNO); //, mainNo); MAIN_No='{0}' AND } else if (allIntersects) { sql = string.Format("SELECT * FROM SKID_JEDDAH_int_TEST where SURVEY_NO={0} and INTERSECTION_NO IS NOT NULL order by arname, intersection_no, direction ", SURVEYNO); //, mainNo); // MAIN_No='{0}' AND intersection_order } else { sql = string.Format("SELECT * FROM SKID_JEDDAH where SURVEY_NO={1} and MAIN_No='{0}' AND SECTION_NO IS NOT NULL order by sec_direction, sec_order, section_no, distance ", mainNo, SURVEYNO); } return(!string.IsNullOrEmpty(sql) ? db.ExecuteQuery(sql) : new DataTable()); }
public DataTable GetGprReportForMainStreet(bool isIntersection, string SURVEYNO) { string sql = ""; // where MAIN_No='{0}' mainNo intersectION_ORDER if (isIntersection) { sql = string.Format("SELECT * FROM GPR_INTERSECTIONS where SURVEY_NO='{0}' order by arname, direction, INTERSECTION_NO, lane ", SURVEYNO); } else { sql = string.Format("SELECT * FROM GPR_LANE where SURVEY_NO='{0}' order by SEC_DIRECTION, SEC_ORDER, section_no, lane ", SURVEYNO); } return((string.IsNullOrEmpty(sql)) ? new DataTable() : db.ExecuteQuery(sql)); }
protected void btnShowReport_Click(object sender, EventArgs e) { try { if (radByAllinters122.Checked) { string sql = string.Format("SELECT * FROM RUTTING_INTERSECTIONS where SURVEY_NO={0} ORDER BY INTERSECTION_ORDER, INTER_NO, LANE ", int.Parse(radlOldSurveys.SelectedValue)); DataTable dt1 = new DataTable(); dt1 = db.ExecuteQuery(sql); Session.Add("option", "radByIntersects"); Session.Add("ReportData", dt1); string url = "ViewRuttingReport.aspx"; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "RedirectScriptCoupon", "window.open('" + url + "', '_blank')", true); } else { lblFeedback.Text = ""; //string lang = Session["lang"].ToString(); if (!radByAllLanes.Checked && ddlMainStreets.SelectedValue == "0") { throw new Exception(Feedback.NoMainStreetSelected()); } else if (radlOldSurveys.SelectedIndex == -1) { throw new Exception(Feedback.NoSurveyDateNum()); } DataTable dt = new RuttingReport().GetRuttingReportForMainStreet(ddlMainStreets.SelectedValue, int.Parse(radlOldSurveys.SelectedValue), radByAllLanes.Checked, radByIntersects.Checked); if (radByMainLanes.Checked) { Session.Add("option", "radByMainLanes"); Session.Add("ReportData", dt); string url = "ViewRuttingReport.aspx"; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "RedirectScriptCoupon", "window.open('" + url + "', '_blank')", true); } else if (radByAllLanes.Checked) { Session.Add("option", "radByAllLanes"); Session.Add("ReportData", dt); string url = "ViewRuttingReport.aspx"; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "RedirectScriptCoupon", "window.open('" + url + "', '_blank')", true); } else if (radByIntersects.Checked) { Session.Add("option", "radByIntersects"); Session.Add("ReportData", dt); string url = "ViewRuttingReport.aspx"; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "RedirectScriptCoupon", "window.open('" + url + "', '_blank')", true); } } } catch (Exception ex) { lblFeedback.Text = ex.Message; } }
public DataTable GetSectionsRatingChart(int mainStID) { if (mainStID == 0) { return(new DataTable()); } // MAIN_ST_ID, main_no , order by MAIN_ST_ID //string sql = string.Format("SELECT UDI_RATE, count(UDI_RATE) as udi_rate_count FROM UDI_SECTION WHERE (MAIN_ST_ID ={0}) group by UDI_RATE ", mainStID); //string sql = string.Format("SELECT r.u_rating, (select count(section_no) from udi_section s where MAIN_ST_ID={0} and s.udi_rate = r.u_rating) as udi_rate_count from udi_ratings r ", mainStID); string sql = string.Format("SELECT r.u_rating, ((select count(section_no) from udi_section s where STREET_ID={0} and s.udi_rate = r.u_rating)+" + " (select count(INTER_NO) from UDI_INTERSECTION s where STREET_ID={0} and s.udi_rate = r.u_rating)) as udi_rate_count from udi_ratings r order by u_rating ", mainStID); return(db.ExecuteQuery(sql)); // MAIN_ST_ID }
public DataTable GetMaintenancePrioritiesForRegionsReport(string unitNo, bool udiDesc, bool udiAsc, bool priority, RegionReportLevel level) { string wherePart = ""; switch (level) { case RegionReportLevel.Region: wherePart = (int.Parse(unitNo) == 0) ? "" : string.Format(" where REGION_ID={0} ", unitNo); break; case RegionReportLevel.Subdistrict: wherePart = (string.IsNullOrEmpty(unitNo) || unitNo == "0") ? "" : string.Format(" where SUBDISTRICT='{0}' ", unitNo); break; case RegionReportLevel.District: wherePart = (string.IsNullOrEmpty(unitNo) || unitNo == "0") ? "" : string.Format(" where DIST_NAME='{0}' ", unitNo); break; case RegionReportLevel.Municipality: wherePart = (string.IsNullOrEmpty(unitNo) || unitNo == "0") ? "" : string.Format(" where MUNIC_NAME='{0}' ", unitNo); break; case RegionReportLevel.None: default: return(new DataTable()); } string orderByPart = GetOrderByPriorityPart(udiDesc, udiAsc, priority); string sql = string.Format("select * from VW_LATEST_PRIO_REGIONS {0} {1} ", wherePart, orderByPart); return(db.ExecuteQuery(sql)); }
public DataTable GetBridgeTunnelFiles(int tunnelID, int bridgeID) { string tablename = (tunnelID == 0) ? "Bridges" : "Tunnels"; string idCol = (tunnelID == 0) ? "BRIDGE_ID" : "TUNNEL_ID"; string value = (tunnelID == 0) ? bridgeID.ToString() : tunnelID.ToString(); string sql = string.Format("SELECT RECORD_ID, PHOTO_NAME, DETAILS FROM PHOTOS where {1}={0} ", value, idCol); return(db.ExecuteQuery(sql)); }
public double GET_INTERSECTION_AREA(int INT_ID) { //double AREA = -1; string SQL = string.Format("Select sum(INTERSEC_SAMP_AREA) as AREA from GV_INTERSECTION_SAMPLES where INTERSECTION_ID={0} ", INT_ID); Shared.LogMdStatment(SQL); DataTable dt = db.ExecuteQuery(SQL); return((dt.Rows.Count > 0) ? double.Parse(dt.Rows[0][0].ToString()) : 0); }
public DataTable GetSectionLanes(int sectionID) { if (sectionID == 0) { return(new DataTable()); } // SAMPLE_count, (select count(SAMPLE_NO) from LANE_SAMPLES where LANE_SAMPLES.lane_id=LANE.LANE_id) as SAMPLE_count, (lane_length*LANE_WIDTH) as SAMPLE_area string sql = string.Format("SELECT LANE_ID, LANE_TYPE, lane_length, LANE_WIDTH, SAMPLE_COUNT, SAMPLE_AREA FROM LANE WHERE SECTION_ID={0} ORDER BY LANE_TYPE ", sectionID); return(db.ExecuteQuery(sql)); }
public DataTable GetSurveyedAreas(MachineSurveyType type) { string sql = ""; switch (type) { case MachineSurveyType.FWD: return(mainStreet.LoadMainStreetHavingFWD()); case MachineSurveyType.IRI_Sections: return(mainStreet.LoadMainStreetsHavingCalculatedIri(true)); case MachineSurveyType.IRI_Intersects: return(mainStreet.LoadMainStreetsHavingCalculatedIri(false)); case MachineSurveyType.GPR_Sections: return(mainStreet.LoadMainStreetsHavingGPR(false)); case MachineSurveyType.GPR_Intersects: return(mainStreet.LoadMainStreetsHavingGPR(true)); case MachineSurveyType.SKID_Sections: sql = "SELECT MAIN_NO, STREET_ID, (ARNAME) as main_title, ARNAME, ARNAME as main_name FROM STREETS WHERE MAIN_NO IN (select distinct main_no from SKID_DATA where section_no is not null) and street_type=1 ORDER BY ARNAME "; break; case MachineSurveyType.SKID_Intersects: sql = "SELECT MAIN_NO, STREET_ID, (ARNAME) as main_title, ARNAME, ARNAME as main_name FROM STREETS WHERE MAIN_NO IN (select distinct main_no from SKID_DATA where INTERSECTION_no is not null) and street_type=1 ORDER BY ARNAME "; break; case MachineSurveyType.Rutting_Sections: return(mainStreet.LoadMainStreetsHavingCalculatedRutting(false)); case MachineSurveyType.Rutting_Intersects: return(mainStreet.LoadMainStreetsHavingCalculatedRutting(true)); case MachineSurveyType.SectionTrafficCounting: sql = "select * from VW_MAINST_MAX_TRAFFIC order by arname "; // main_no break; case MachineSurveyType.None: default: break; } return(!string.IsNullOrEmpty(sql) ? db.ExecuteQuery(sql) : new DataTable()); }
public DataTable GetFwdReport(string mainNo, bool all, string SURVEYNO) { string sql = ""; mainNo = mainNo.Replace("'", "''"); if (all) //(string.IsNullOrEmpty(mainNo) || mainNo == "0") main_no { sql = string.Format("SELECT * FROM FWD_SECTION_DETIALS where SURVEY_NO={0} ORDER by arname, SEC_DIRECTION, sec_order, SECTION_NO, STATION_ID, DROP_ID ", SURVEYNO); } else { sql = string.Format("SELECT * FROM FWD_SECTION_DETIALS where MAIN_No='{0}' AND SURVEY_NO={1} ORDER by SEC_DIRECTION, sec_order, SECTION_NO, STATION_ID, DROP_ID ", mainNo, SURVEYNO); } return(string.IsNullOrEmpty(sql) ? new DataTable() : db.ExecuteQuery(sql)); }
public DataTable GetAll() { string sql = "select INTERSECT_TYPE_ID, INTERSECT_TYPE from INTERSECT_TYPES order by INTERSECT_TYPE "; return(db.ExecuteQuery(sql)); }
public DataTable GetIntersectionTypes() { string sql = "select * from INTERSECT_TYPES order by INTERSECT_TYPE "; return(db.ExecuteQuery(sql)); }
public DataTable GetQcDetails(int qcID) { if (qcID == 0) { return(new DataTable()); } string sql = string.Format("select * from VW_QCHECK_DETAILS where QC_CHECK_ID={0} order by DIST_CODE ", qcID); return(db.ExecuteQuery(sql)); }
public DataTable GetBridgeTypes() { string sql = "select BRIDGE_TYPE_ID, BRIDGE_TYPE from BRIDGE_TYPES order by BRIDGE_TYPE_ID "; return(db.ExecuteQuery(sql)); }
//private DataTable dtMaintAffectedArea; //private int recordsAffected = 0; #region Reporting public DataTable GetMaintenanceDecisionsForMainStreetLanes(int mainStID, bool forServiceLanes, int surveyNum) { if (mainStID == 0) //|| survey == 0) { return(new DataTable()); } if (surveyNum == 0) { DataTable dt = survey.GetLastSurveyForRoadsNetwork(0, "", "", "", false, false, false, false, mainStID, true, false); if (dt.Rows.Count == 0) { return(new DataTable()); } surveyNum = int.Parse(dt.Rows[0]["SURVEY_NO"].ToString()); } // MAINTE_DECI MAIN_STREET_ID string sql = string.Format("SELECT * FROM VW_MAINT_DEC_IRI_LSAMPLES where STREET_ID={0} and SURVEY_NO={1} ORDER BY SECTION_NO, LANE_TYPE, SAMPLE_NO ", mainStID, surveyNum); return(db.ExecuteQuery(sql)); }
public DataTable GetContractorsList() { string sql = "select CONTRACTOR_ID, CONTRACTOR_NAME, CONTRACTOR_NO from CONTRACTOR order by CONTRACTOR_NAME "; return(db.ExecuteQuery(sql)); }