public DataTable GetBridgeSectionInfo(int bridgeID)
{
string sql = string.Format("select SECTION_ID from BRIDGES where BRIDGE_ID={0} ", bridgeID);
int sectionID = int.Parse(db.ExecuteScalar(sql).ToString());
return(new MainStreetSection().GetSectionInfo(sectionID));
}
public bool InsertNewStreetRegion(string SECOND_ST_NO, int REGION_ID, string REGION_NO)
{
int value;
if (REGION_ID == 0 || string.IsNullOrEmpty(REGION_NO) || REGION_NO.Length < 6)
{
return(false);
}
REGION_NO = REGION_NO.Substring(0, 6);
if (REGION_NO.Length == 6 && int.TryParse(REGION_NO, out value))
{
string Exsists = string.Format(" select count(*) from STREETS where SECOND_ST_NO='{0}' and REGION_NO='{1}' and REGION_ID ='{2}'", SECOND_ST_NO, REGION_NO, REGION_ID);
string rowsExsists = db.ExecuteScalar(Exsists).ToString();
if (int.Parse(rowsExsists) > 0)
{
return(false);
}
else
{
string sql = string.Format(" insert INTO STREETS (SECOND_ST_NO,REGION_NO,REGION_ID,IS_R4,STREET_TYPE,DATECREATE,STREET_ID,OBJECTID) values ('{0}','{1}',{2},'N',0,(SELECT SYSDATE FROM dual),(select max(STREET_ID) from STREETS)+1,(select max(OBJECTID) from STREETS)+1)",
SECOND_ST_NO, REGION_NO, REGION_ID);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
}
else
{
return(false);
}
}
public DataTable GetTunnelSectionInfo(int tunnelID)
{
string sql = string.Format("select SECTION_ID from TUNNELS where TUNNEL_ID={0} ", tunnelID);
int sectionID = int.Parse(db.ExecuteScalar(sql).ToString());
return(new MainStreetSection().GetSectionInfo(sectionID));
}
public string GetFeedbackImage(int id)
{
if (id == 0)
{
return("");
}
string sql = string.Format("select nvl(JOB_ORDER_FILE, '') as JOB_ORDER_FILE from FEEDBACKS where FEEDBACK_ID={0} ", id);
return(db.ExecuteScalar(sql).ToString());
}
public DataTable GetTunnelSectionInfo(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 SECTION_ID from {0} where {1}={2} ", tablename, idCol, value);
string s = db.ExecuteScalar(sql).ToString();
int sectionID = string.IsNullOrEmpty(s) ? 0 : int.Parse(s);
return(new MainStreetSection().GetSectionInfo(sectionID));
}
public double GetMainStreetSectionsMaintenanceDecisionsCost(int mainStID)
{
string mainStPart = (mainStID == 0) ? "" : string.Format(" where STREET_ID={0} ", mainStID);
string sql = string.Format("select nvl(sum(MAINT_COST), 0) from VW_LATEST_COST_SECTIONS {0} ", mainStPart);
return(double.Parse(db.ExecuteScalar(sql).ToString()));
}
public string GetAllDecisionsFWD_HAFRIAT()
{
//string sql = "select count(*)/3 from FWD_DATA where SURVEY_NO=3 and main_no is not null";
string sql = "select sum(Points) from (SELECT SUM(FWD_COUNT) Points from JPMMS.EQUIPMENTMAINQC where IS_FWD=1 union SELECT sum(POINTS) Points from jpmms.HAFRIAT_FWD)";
return(db.ExecuteScalar(sql).ToString());
}
public bool DeleteImage(int RECORD_ID)
{
if (RECORD_ID == 0)
{
return(false);
}
string sql = string.Format("select PHOTO_NAME from PHOTOS where RECORD_ID={0} ", RECORD_ID);
string fileName = db.ExecuteScalar(sql).ToString();
System.IO.File.Delete(HttpContext.Current.Server.MapPath("~/uploads/") + fileName);
sql = string.Format("delete from PHOTOS where RECORD_ID={0} ", RECORD_ID);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool InsertRegionQcCheckRecord(DateTime?qcDate, DateTime?surveyDate, int surveyorID, int checkerID, int regionID, int secondID, double?areaSurveyor,
double?areaChecker, string user, int userID)
{
int rows = 0;
string areaSurveyorPart = (areaSurveyor == null) ? "NULL" : ((double)areaSurveyor).ToString("0.00");
string areaCheckerPart = (areaChecker == null) ? "NULL" : ((double)areaChecker).ToString("0.00");
string sql = string.Format("select nvl(count(*), 0) from distress where region_id={0} ", regionID);
rows = int.Parse(db.ExecuteScalar(sql).ToString());
if (rows > 0)
{
sql = string.Format("select nvl(count(*), 0) from QC_CHECK where STREET_ID={0} ", secondID); // SECOND_ID
rows = int.Parse(db.ExecuteScalar(sql).ToString());
if (rows > 0)
{
throw new Exception(Feedback.InsertExceptionUnique());
}
// 0 1 2 3 4 5 6 7 8
sql = string.Format("insert into QC_CHECK(QC_CHECK_ID, QC_DATE, SURVEY_DATE, SURVEYOR_ID, QC_CHECKER_ID, REGION_ID, STREET_ID, SURVEYOR_AREA, CHECKER_AREA, SURV_SUM_POINTS, SURV_SUM_MAX_POINTS, SURV_RATE, ENTRY_DATE, DONY_BY) " +
" values(SEQ_QC_CHECK.nextval, '{0}', '{1}', {2}, {3}, {4}, {5}, {6}, {7}, 0, 0, 0, (select sysdate from dual), {8}) ",
((DateTime)qcDate).ToString("dd/MM/yyyy"), ((DateTime)surveyDate).ToString("dd/MM/yyyy"), surveyorID, checkerID, regionID, secondID, areaSurveyorPart,
areaCheckerPart, userID); // SECOND_ID
rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
else
{
throw new Exception("لم يتم مسح هذه المنطقة بعد");
}
}
public double GetDailyDataEntryTotalArea(DateTime?dtpDate)
{
if (dtpDate == null)
{
return(0);
}
//string sql = string.Format("select (select nvl(sum(sample_length*sample_width), 0) from lane_samples where sample_id in (select distinct sample_id from distress where entry_date>='{0}' and entry_date<'{1}' and sample_id is not null))" +
// "+ (select nvl(sum(intersec_samp_area), 0) from intersection_samples where inter_samp_id in (select distinct inter_samp_id from distress where entry_date>='{0}' and entry_date<'{1}' and inter_samp_id is not null)) " +
// "+(select nvl(sum(second_st_length*second_st_width), 0) from secondary_streets where second_id in (select distinct second_id from distress where entry_date>='{0}' and entry_date<'{1}' and second_id is not null)) " +
// " as total_entred_areas_distress from dual", ((DateTime)dtpDate).ToString("dd/MM/yyyy"), ((DateTime)dtpDate).AddDays(1).ToString("dd/MM/yyyy"));
string sql = string.Format("SELECT (SELECT NVL (SUM (sample_length * sample_width), 0) FROM lane_samples " +
" WHERE sample_id IN ( SELECT DISTINCT sample_id FROM distress WHERE entry_date >= to_date('{0}', 'DD/MM/YYYY') AND entry_date < to_date('{1}', 'DD/MM/YYYY') AND sample_id IS NOT NULL)) + " +
" (SELECT NVL (SUM (intersec_samp_area), 0) FROM intersection_samples " +
" WHERE inter_samp_id IN (SELECT DISTINCT inter_samp_id FROM distress WHERE entry_date >= to_date('{0}', 'DD/MM/YYYY') AND entry_date <to_date('{1}', 'DD/MM/YYYY') AND inter_samp_id IS NOT NULL)) + " +
" (SELECT NVL (SUM (second_st_length * second_st_width), 0) FROM streets " + // secondary_streets second_id
" WHERE STREET_ID IN (SELECT DISTINCT STREET_ID FROM distress WHERE entry_date >= to_date('{0}', 'DD/MM/YYYY') AND entry_date < to_date('{1}', 'DD/MM/YYYY') AND region_no IS NOT NULL))" +
" AS total_entred_areas_distress FROM DUAL ", ((DateTime)dtpDate).ToString("dd/MM/yyyy"), ((DateTime)dtpDate).AddDays(1).ToString("dd/MM/yyyy"));
return(double.Parse(db.ExecuteScalar(sql).ToString()));
}
public bool CalculateMaintenanceDecisionForStreetIntersections(int m_MAIN_ST_ID, int SRV_ID)
{
int rows = 0;
int mdID = 0;
double intersectArea = 0;
double sampleArea = 0;
//double sumMaintDArea = 0;
//double sampleMaintArea = 0;
double deepPatchingDistsArea = 0;
double iri;
string iri_value = "";
MaintDecision maintD;
MaintDecision[] intersectMaintD;
MaintDecision[,] sampleMaintD;
DataTable dtSmpDist, dtSmpUDI;
double[] sampleMaintArea;
// MAIN_ST_ID
string sql = string.Format("select STREET_ID, INTERSECTION_ID, INTER_NO, INTERSECTION_AREA, UDI_VALUE, to_char(SURVEY_DATE,'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as SURVEY_DATE, " +
" to_char(UDI_DATE,'DD/MM/YYYY','NLS_CALENDAR=''GREGORIAN''') as UDI_DATE, SURVEY_NO from UDI_INTERSECTION where STREET_ID={0} and SURVEY_NO={1} ",
m_MAIN_ST_ID, SRV_ID);
Shared.LogMdStatment(sql);
DataTable dtIntersectUDI = db.ExecuteQuery(sql);
foreach (DataRow dr in dtIntersectUDI.Rows)
{
iri = 0;
iri = m_C_Get_Data.GET_INTERSECTION_IRI(dr["INTER_NO"].ToString());
iri_value = (iri == -1) ? "NULL" : iri.ToString("0.00");
//
//for (int i = 1; i <= 9; i++)
//{
// intersectMaintD[i] = new MaintDecision();
// intersectMaintD[i].AffectedSamples = new Dictionary<int, int>();
//}
intersectArea = double.Parse(dr["INTERSECTION_AREA"].ToString());
if (double.Parse(dr["UDI_VALUE"].ToString()) >= UDI_LIMIT_I) // udi>=70
{
#region IRI
//if (iri == -1)
// continue; else
if (iri != -1 && iri > IRI_LIMIT_I)
{
// IRI>4: mill and repace for the whole intersection
mdID = (int)MaintenanceDecisions.Mill_Repave_5cm;
// 0 1 2 3 4 5 6 7 8
sql = string.Format("Insert Into MAINTENANCE_DECISIONS (RECORD_ID, RECOMMENDED_DECISION_ID, INTERSECTION_ID, MAINT_AREA, SURVEY_DATE, UDI_DATE, SURVEY_NO, USING_UDI, USING_IRI, STREET_ID, THICKNESS, UDI, IRI) " +
" Values(SEQ_MAINTENANCE_DECISION.nextval, {0}, {1}, {2}, To_date('{3}', 'DD/MM/YYYY'), To_date('{4}', 'DD/MM/YYYY'), {5}, 1, 1, {6}, " +
" (select THICKNESS from MAINT_DECISIONS where RECOMMENDED_DECISION_ID={0}), {7}, {8}) ",
mdID, dr["INTERSECTION_ID"].ToString(), intersectArea.ToString("0.00"), dr["SURVEY_DATE"].ToString(), dr["UDI_DATE"].ToString(), SRV_ID, m_MAIN_ST_ID,
dr["UDI_VALUE"].ToString(), iri_value);
Shared.LogMdStatment(sql);
rows += db.ExecuteNonQuery(sql);
}
else if ((iri != -1 && iri <= IRI_LIMIT_I) || iri == -1)
{
#region perSample
intersectMaintD = new MaintDecision[10];
for (int i = 1; i <= 9; i++)
{
intersectMaintD[i] = new MaintDecision();
//intersectMaintD[i].AffectedSamples = new Dictionary<int, int>();
}
sql = string.Format("select STREET_ID, INTER_ID, INTER_NO, INTER_SAMP_ID, INTER_SAMP_NO, INTER_SAMP_AREA, UDI_VALUE, UDI_DATE, SURVEY_DATE, SURVEY_NO " +
" from UDI_INTERSECTION_SAMPLE where INTER_ID={0} and SURVEY_NO={1} ", dr["INTERSECTION_ID"].ToString(), SRV_ID);
Shared.LogMdStatment(sql);
dtSmpUDI = db.ExecuteQuery(sql);
sampleMaintD = new MaintDecision[10, dtSmpUDI.Rows.Count];
for (int i = 1; i <= 9; i++)
{
for (int j = 0; j < dtSmpUDI.Rows.Count; j++)
{
sampleMaintD[i, j] = new MaintDecision();
}
}
int c = 0;
sampleMaintArea = new Double[dtSmpUDI.Rows.Count];
foreach (DataRow drSmpUDI in dtSmpUDI.Rows)
{
//sampleMaintArea = 0;
deepPatchingDistsArea = 0;
sampleArea = double.Parse(drSmpUDI["INTER_SAMP_AREA"].ToString());
if (double.Parse(drSmpUDI["UDI_VALUE"].ToString()) < UDI_LIMIT_IS)
{
// sample UDI < 70: mill and repave the whole sample
sampleMaintArea[c] = sampleArea;
mdID = (int)MaintenanceDecisions.Mill_Repave_5cm;
intersectMaintD[(int)MaintenanceDecisions.Mill_Repave_5cm].MaintArea += sampleArea;
sampleMaintD[(int)MaintenanceDecisions.Mill_Repave_5cm, c].MaintArea = sampleArea;
sampleMaintD[(int)MaintenanceDecisions.Mill_Repave_5cm, c].UDI = int.Parse(drSmpUDI["UDI_VALUE"].ToString());
sampleMaintD[(int)MaintenanceDecisions.Mill_Repave_5cm, c].AffectedSampleID = int.Parse(drSmpUDI["INTER_SAMP_ID"].ToString());
}
else
{
// sample UDI >= 70: check maintenance decisions for sample distresses
sql = string.Format("select DIST_CODE, DIST_SEVERITY, DIST_AREA, DIST_DENSITY from V_INTERSECT_SAMPLE_DECISIONS " +
" where INTER_SAMP_ID={0} and SURVEY_NO={1} ", drSmpUDI["INTER_SAMP_ID"].ToString(), SRV_ID);
Shared.LogMdStatment(sql);
dtSmpDist = db.ExecuteQuery(sql);
foreach (DataRow drDist in dtSmpDist.Rows)
{
maintD = deciding.GetMaintDecision(int.Parse(drDist["DIST_CODE"].ToString()), drDist["DIST_SEVERITY"].ToString()[0],
double.Parse(drDist["DIST_DENSITY"].ToString()), double.Parse(drDist["DIST_AREA"].ToString()));
if (maintD.MaintDecisionID != 0 && maintD.MaintArea < (sampleArea / 2))
{
sampleMaintArea[c] += maintD.MaintArea;
intersectMaintD[maintD.MaintDecisionID].MaintArea += maintD.MaintArea;
sampleMaintD[maintD.MaintDecisionID, c].MaintArea += maintD.MaintArea;
sampleMaintD[maintD.MaintDecisionID, c].UDI = int.Parse(drSmpUDI["UDI_VALUE"].ToString());
sampleMaintD[maintD.MaintDecisionID, c].AffectedSampleID = int.Parse(drSmpUDI["INTER_SAMP_ID"].ToString());
//intersectMaintD[maintD.MaintDecisionID].MaintArea += maintD.MaintArea;
//intersectMaintD[maintD.MaintDecisionID].AffectedSamples.Remove(int.Parse(drSmpUDI["INTER_SAMP_ID"].ToString()));
//intersectMaintD[maintD.MaintDecisionID].AffectedSamples.Add(int.Parse(drSmpUDI["INTER_SAMP_ID"].ToString()), int.Parse(drSmpUDI["UDI_VALUE"].ToString()));
//if (maintD.MaintDecisionID != 1)
// intersectMaintD[(int)MaintenanceDecisions.DoNothing].AffectedSamples.Remove(int.Parse(drSmpUDI["INTER_SAMP_ID"].ToString()));
}
else
{
// remove all previously decisions taken on sample and take "Deep Patching" and "Mill-repave" decisions
for (int i = 1; i <= 9; i++)
{
sampleMaintD[i, c].AffectedSampleID = 0;
sampleMaintD[i, c].MaintArea = 0;
sampleMaintD[i, c].UDI = int.Parse(drSmpUDI["UDI_VALUE"].ToString());
}
sampleMaintArea[c] += sampleArea;
sql = string.Format("select nvl(sum(DIST_AREA), 0) as sum_dist_area from V_INTERSECT_SAMPLE_DECISIONS where INTER_SAMP_ID= {0} and survey_no={1} " +
" and ((dist_code in (6, 7, 8, 13) and DIST_SEVERITY='H') or (dist_code=1 and DIST_SEVERITY='M')) ",
drSmpUDI["INTER_SAMP_ID"].ToString(), SRV_ID);
Shared.LogMdStatment(sql);
deepPatchingDistsArea = double.Parse(db.ExecuteScalar(sql).ToString());
intersectMaintD[(int)MaintenanceDecisions.DeepPatching].MaintArea += deepPatchingDistsArea;
intersectMaintD[(int)MaintenanceDecisions.Mill_Repave_5cm].MaintArea += sampleArea;
sampleMaintD[(int)MaintenanceDecisions.DeepPatching, c].MaintArea = deepPatchingDistsArea;
sampleMaintD[(int)MaintenanceDecisions.DeepPatching, c].UDI = int.Parse(drSmpUDI["UDI_VALUE"].ToString());
sampleMaintD[(int)MaintenanceDecisions.DeepPatching, c].AffectedSampleID = int.Parse(drSmpUDI["INTER_SAMP_ID"].ToString());
sampleMaintD[(int)MaintenanceDecisions.Mill_Repave_5cm, c].MaintArea = sampleArea;
sampleMaintD[(int)MaintenanceDecisions.Mill_Repave_5cm, c].UDI = int.Parse(drSmpUDI["UDI_VALUE"].ToString());
sampleMaintD[(int)MaintenanceDecisions.Mill_Repave_5cm, c].AffectedSampleID = int.Parse(drSmpUDI["INTER_SAMP_ID"].ToString());
break;
}
}
}
if (sampleMaintArea[c] == 0)
{
sampleMaintD[(int)MaintenanceDecisions.DoNothing, c].AffectedSampleID = int.Parse(drSmpUDI["INTER_SAMP_ID"].ToString());
sampleMaintD[(int)MaintenanceDecisions.DoNothing, c].UDI = int.Parse(drSmpUDI["UDI_VALUE"].ToString());
sampleMaintD[(int)MaintenanceDecisions.DoNothing, c].MaintArea = 0;
}
else
{
sampleMaintD[(int)MaintenanceDecisions.DoNothing, c].AffectedSampleID = 0;
}
c += 1;
}
// check maintenance area not exceeding 50% of intersection area
for (int md = 1; md <= 9; md++)
{
if (intersectMaintD[md].MaintArea > intersectArea / 2)
{
intersectMaintD[md].MaintArea = intersectArea;
for (c = 0; c < dtSmpUDI.Rows.Count; c++)
{
sampleMaintD[(int)MaintenanceDecisions.DoNothing, c].AffectedSampleID = 0;
sampleMaintD[md, c].AffectedSampleID = int.Parse(dtSmpUDI.Rows[c]["INTER_SAMP_ID"].ToString());
sampleMaintD[md, c].UDI = int.Parse(dtSmpUDI.Rows[c]["UDI_VALUE"].ToString());
sampleMaintD[md, c].MaintArea = double.Parse(dtSmpUDI.Rows[c]["INTER_SAMP_AREA"].ToString());
}
//foreach (DataRow drSample in dtSmpUDI.Rows)
//{
// intersectMaintD[i].AffectedSamples.Remove(int.Parse(drSample["INTER_SAMP_ID"].ToString()));
// intersectMaintD[i].AffectedSamples.Add(int.Parse(drSample["INTER_SAMP_ID"].ToString()), int.Parse(drSample["UDI_VALUE"].ToString()));
//}
}
// save lane maintenance decision to DB
if ((md != 1 && intersectMaintD[md].MaintArea == 0))
{
continue;
}
else
{
for (c = 0; c < dtSmpUDI.Rows.Count; c++)
{
if (sampleMaintD[md, c].AffectedSampleID != 0)
{
// 0 1 2 3 4 5 6 7 8 9
sql = string.Format("Insert Into MAINTENANCE_DECISIONS (RECORD_ID, RECOMMENDED_DECISION_ID, INTERSECTION_ID, MAINT_AREA, SURVEY_DATE, UDI_DATE, SURVEY_NO, USING_UDI, USING_IRI, STREET_ID, THICKNESS, UDI, INT_SMP_ID, IRI) " +
" Values(SEQ_MAINTENANCE_DECISION.nextval, {0}, {1}, {2}, To_date('{3}', 'DD/MM/YYYY'), To_date('{4}', 'DD/MM/YYYY'), " +
" {5}, 1, 1, {6}, (select THICKNESS from MAINT_DECISIONS where RECOMMENDED_DECISION_ID={0}), {7}, {8}, {9}) ",
md, dr["INTERSECTION_ID"].ToString(), sampleMaintD[md, c].MaintArea.ToString("0.00"), dr["SURVEY_DATE"].ToString(), dr["UDI_DATE"].ToString(),
SRV_ID, m_MAIN_ST_ID, sampleMaintD[md, c].UDI, sampleMaintD[md, c].AffectedSampleID, iri_value);
Shared.LogMdStatment(sql);
rows += db.ExecuteNonQuery(sql);
}
else
{
continue;
}
}
}
}
#endregion
}
#endregion
}
else // udi <70
{
mdID = (int)MaintenanceDecisions.Mill_Repave_5cm;
// 0 1 2 3 4 5 6 7 8
sql = string.Format("Insert Into MAINTENANCE_DECISIONS (RECORD_ID, RECOMMENDED_DECISION_ID, INTERSECTION_ID, MAINT_AREA, SURVEY_DATE, UDI_DATE, SURVEY_NO, USING_UDI, USING_IRI, STREET_ID, THICKNESS, UDI, IRI) " +
" Values(SEQ_MAINTENANCE_DECISION.nextval, {0}, {1}, {2}, To_date('{3}', 'DD/MM/YYYY'), To_date('{4}', 'DD/MM/YYYY'), {5}, 1, 1, {6}, " +
" (select THICKNESS from MAINT_DECISIONS where RECOMMENDED_DECISION_ID={0}), {7}, {8}) ",
mdID, dr["INTERSECTION_ID"].ToString(), intersectArea.ToString("0.00"), dr["SURVEY_DATE"].ToString(), dr["UDI_DATE"].ToString(), SRV_ID, m_MAIN_ST_ID,
dr["UDI_VALUE"].ToString(), iri_value);
Shared.LogMdStatment(sql);
rows += db.ExecuteNonQuery(sql);
}
}
return(rows > 0);
}
public bool Insert(int surveyorID, DateTime?issueDate, DateTime?recieveDate, int surveyNo, string notes, string ID, JobType jobType)
{
// sectionNo, interNo, regionNo, string surveyorName,
string sql = "";
int rows = 0;
int x = 0;
if (string.IsNullOrEmpty(ID))
{
throw new Exception("الرجاء اختيار العنصر الممسوح - مقطع أو تقاطع أو منطقة فرعية");
}
string surveyorName = Surveyor.GetSurveyorByID(surveyorID).Rows[0]["SURVEYOR_NAME"].ToString();
string issueDatePart = (issueDate == null) ? "NULL" : string.Format("'{0}'", ((DateTime)issueDate).ToString("dd/MM/yyyy"));
string recieveDatePart = (recieveDate == null) ? "NULL" : string.Format("'{0}'", ((DateTime)recieveDate).ToString("dd/MM/yyyy"));
notes = string.IsNullOrEmpty(notes) ? "NULL" : string.Format("'{0}'", notes.Replace("'", "''"));
switch (jobType)
{
case JobType.Section:
sql = string.Format("SELECT Count(*) as X FROM SURVEYORS_SUBMIT_JOB where section_NO=(select section_no from sections where section_id={0}) and SURVEY_NO={1} and SURVEYOR_ID={2} ", ID, surveyNo, surveyorID);
x = int.Parse(db.ExecuteScalar(sql).ToString());
if (x >= 1)
{
throw new Exception("هذا المسح مدخل من قبل");
}
// 0 1 2 3 4 5 6
sql = string.Format("insert into SURVEYORS_SUBMIT_JOB(ISSUE_DATE, SURVEY_NO, NOTES, RECEIVE_DATE, SURVEYOR_NAME, SURVEYOR_ID, SECTION_NO, RECORD_ID, IS_SECTION) " +
" values({0}, {1}, {2}, {3}, '{4}', {5}, (select SECTION_NO from SECTIONS where SECTION_ID={6}), SEQ_SURVEYORS_SUBMIT.nextval, 1) ",
issueDatePart, surveyNo, notes, recieveDatePart, surveyorName, surveyorID, ID);
rows += db.ExecuteNonQuery(sql);
break;
case JobType.Intersection:
sql = string.Format("SELECT Count(*) as X FROM SURVEYORS_SUBMIT_JOB where inter_no=(select inter_no from INTERSECTIONS where INTERSECTION_ID={0}) and SURVEY_NO={1} and SURVEYOR_ID={2} ", ID, surveyNo, surveyorID);
x = int.Parse(db.ExecuteScalar(sql).ToString());
if (x >= 1)
{
throw new Exception("هذا المسح مدخل من قبل");
}
// 0 1 2 3 4 5 6
sql = string.Format("insert into SURVEYORS_SUBMIT_JOB(ISSUE_DATE, SURVEY_NO, NOTES, RECEIVE_DATE, SURVEYOR_NAME, SURVEYOR_ID, INTER_NO, RECORD_ID, IS_INTERSECT) " +
" values({0}, {1}, {2}, {3}, '{4}', {5}, (select INTER_NO from INTERSECTIONS where INTERSECTION_ID={6}), SEQ_SURVEYORS_SUBMIT.nextval, 1) ",
issueDatePart, surveyNo, notes, recieveDatePart, surveyorName, surveyorID, ID);
rows += db.ExecuteNonQuery(sql);
break;
case JobType.RegionSecondaryStreets:
sql = string.Format("SELECT Count(*) as X FROM SURVEYORS_SUBMIT_JOB where REGION_NO=(select REGION_NO from REGIONS where REGION_ID={0}) and SURVEY_NO={1} and SURVEYOR_ID={2} ", ID, surveyNo, surveyorID);
x = int.Parse(db.ExecuteScalar(sql).ToString());
if (x >= 1)
{
throw new Exception("هذا المسح مدخل من قبل");
}
// 0 1 2 3 4 5 6
sql = string.Format("insert into SURVEYORS_SUBMIT_JOB(ISSUE_DATE, SURVEY_NO, NOTES, RECEIVE_DATE, SURVEYOR_NAME, SURVEYOR_ID, REGION_NO, RECORD_ID, IS_REGION) " +
" values({0}, {1}, {2}, {3}, '{4}', {5}, (select REGION_NO from REGIONS where REGION_ID={6}), SEQ_SURVEYORS_SUBMIT.nextval, 1) ",
issueDatePart, surveyNo, notes, recieveDatePart, surveyorName, surveyorID, ID);
rows += db.ExecuteNonQuery(sql);
break;
default:
return(false);
}
return(rows > 0);
}