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