public bool UpdateSubdistrict(string ARNAME, string ENNAME, int DISTRICTNO, int?SUBDISTRIC, int ID1)
{
if (ID1 == 0 || DISTRICTNO == 0 || string.IsNullOrEmpty(ARNAME)) //|| SUBDISTRIC == null
{
return(false);
}
ARNAME = ARNAME.Replace("'", "''");
ENNAME = ENNAME.Replace("'", "''");
string SUBDISTRIC_part = (SUBDISTRIC == null) ? "NULL" : SUBDISTRIC.ToString();
DataTable dt = GetDistrictFullInfo(DISTRICTNO);
if (dt.Rows.Count == 0)
{
return(false);
}
DataRow dr = dt.Rows[0];
string sql = string.Format("update SUBDISTRICT set ARNAME='{0}', ENNAME='{1}', DISTRICTNO={2}, SUBDISTRIC={3}, SUBMUNICIP={4} where ID1={5} ",
ARNAME, ENNAME, DISTRICTNO, SUBDISTRIC_part, dr["MUNIC_ID"].ToString(), ID1);
int rows = db.ExecuteNonQuery(sql);
sql = string.Format("update regions set ARNAME='{1}', SUBDISTRICT='{1}', ENNAME='{2}', SUBDISTRIC={3} where SUBDISTRICT_ID={0} ", ID1, ARNAME, ENNAME, SUBDISTRIC_part);
rows += db.ExecuteNonQuery(sql);
return(rows > 0);
}
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 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 bool Update(string MUNIC_NO, string ARNAME, string ENNAME, int MUNIC_ID)
{
if (MUNIC_ID == 0 || string.IsNullOrEmpty(MUNIC_NO) || string.IsNullOrEmpty(ARNAME))
{
return(false);
}
MUNIC_NO = MUNIC_NO.Replace("'", "''");
ARNAME = ARNAME.Replace("'", "''");
ENNAME = ENNAME.Replace("'", "''");
string sql = string.Format("update SUBMUNICIPALITY set MUNIC_NO='{0}', ARNAME='{1}', ENNAME='{2}' where MUNIC_ID={3} ", MUNIC_NO, ARNAME, ENNAME, MUNIC_ID);
int rows = db.ExecuteNonQuery(sql);
sql = string.Format("update DISTRICT set MUNIC_NAME='{0}', SUBMUNICIP={1} where MUNIC_ID={1} ", ARNAME, MUNIC_ID);
rows += db.ExecuteNonQuery(sql);
//sql = string.Format("update SUBDISTRICT set MUNIC_NAME='{0}' where SUBMUNICIP={1} ", ARNAME, MUNIC_ID);
//rows += db.ExecuteNonQuery(sql);
sql = string.Format("update REGIONS set MUNIC_NAME='{0}', SUBMUNICIP={1} where region_no like '{2}%' ", ARNAME, MUNIC_ID, MUNIC_NO);
rows += db.ExecuteNonQuery(sql);
sql = string.Format("update SECTIONS set MUNICIPALITY='{0}' where SECTION_NO like '{1}%' ", ARNAME, MUNIC_NO);
rows += db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool Insert(string UNIT_NAME)
{
UNIT_NAME = UNIT_NAME.Replace("'", "''");
string sql = string.Format("insert into UNITS(UNIT_ID, UNIT_NAME) values(SEQ_UNITS.nextval, '{0}') ", UNIT_NAME);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
//public MaintDecision(int id, int thickness, double area, int iSampleID, int udi, bool sampleWise)
//{
// MaintDecisionID = id;
// Thickness = thickness;
// MaintArea = (id == 1) ? 0 : area;
// AffectedSampleID = iSampleID;
// UDI = udi;
// SampleWise = sampleWise;
//}
public bool Insert(string RECOMMENDED_DECISION, int UNIT_ID, double?UNIT_PRICE, int?LIFECYCLE_AGE, double?THICKNESS, int UDI_ENHANCE)
{
RECOMMENDED_DECISION = RECOMMENDED_DECISION.Replace("'", "''");
// 0 1 2 3 4
string sql = string.Format("insert into MAINT_DECISIONS(RECOMMENDED_DECISION_ID, RECOMMENDED_DECISION, UNIT_ID, UNIT_PRICE, LIFECYCLE_AGE, THICKNESS) values(SEQ_MAINT_DEC.nextval, '{0}', {1}, {2}, {3}, {4}) ",
RECOMMENDED_DECISION, UNIT_ID, UNIT_PRICE, LIFECYCLE_AGE, THICKNESS);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool Update(double?MAIN_ST_GOOD_WEIGHT, double?MAIN_ST_POOR_WEIGHT, double?SECOND_ST_GOOD_WEIGHT, double?SECOND_ST_POOR_WEIGHT,
double?HOUSES_SECTIONS, double?COMMERIAL_SECTIONS, double?INDISTERIAL_SECTIONS, double?REST_HOUSE_SECTIONS, double?GARDENS_SECTIONS, double?PUBLICS_SECTIONS,
double?HOUSES_REGIONS, double?COMMERIAL_REGIONS, double?INDISTERIAL_REGIONS, double?REST_HOUSE_REGIONS, double?GARDENS_REGIONS, double?PUBLICS_REGIONS,
double?SCHOOL_REGIONS, double?MASJID_REGIONS, double?HOSPITAL_REGIONS, double?SPORT_CLUB_REGIONS, double?NEW_BUILDINGS_REGIONS, double?OTHER_UTIL_REGIONS,
double?TRAFFIC_LOW_WEIGHT, double?TRAFFIC_MEDIUM_WEIGHT, double?TRAFFIC_HIGH_WEIGHT, double?TRAFFIC_VERY_HIGH_WEIGHT)
{
double sumSections = (double)HOUSES_SECTIONS + (double)COMMERIAL_SECTIONS + (double)INDISTERIAL_SECTIONS + (double)REST_HOUSE_SECTIONS + (double)GARDENS_SECTIONS
+ (double)PUBLICS_SECTIONS;
double sumRegions = (double)HOUSES_REGIONS + (double)COMMERIAL_REGIONS + (double)INDISTERIAL_REGIONS + (double)REST_HOUSE_REGIONS + (double)GARDENS_REGIONS
+ (double)PUBLICS_REGIONS + (double)SCHOOL_REGIONS + (double)MASJID_REGIONS + (double)HOSPITAL_REGIONS + (double)SPORT_CLUB_REGIONS + (double)NEW_BUILDINGS_REGIONS
+ (double)OTHER_UTIL_REGIONS;
if (sumSections != 100)
{
throw new Exception("الرجاء ضبط معاملات أوزان النشاطات الجانبية للطرق الرئيسية بحيث يساوي مجموعها 100 ");
}
else if (sumRegions != 100)
{
throw new Exception("الرجاء ضبط معاملات أوزان النشاطات الجانبية للمناطق الفرعية بحيث يساوي مجموعها 100 ");
}
string sql = "select * from MAINT_PRIO_WEIGHTS ";
DataTable dt = db.ExecuteQuery(sql);
if (dt.Rows.Count != 0)
{
sql = "delete from MAINT_PRIO_WEIGHTS ";
db.ExecuteNonQuery(sql);
}
sql = string.Format("insert into MAINT_PRIO_WEIGHTS (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) " +
" values(1, {0}, {1}, {2}, {3}, " +
" {4}, {5}, {6}, {7}, {8}, {9}, " +
" {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, " +
" {19}, {20}, {21}, " +
" {22}, {23}, {24}, {25}) ",
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);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool UpdateIntersectionSampleArea(int INTER_SAMP_ID, double INTERSEC_SAMP_AREA, string user, string NOTES)
{
string sql = "";
int lastSurveyNum = DistressSurvey.GetLastIntersectionSurveyNumber(INTER_SAMP_ID);
if (lastSurveyNum == 0)
{
// no surveys have been done over this secondar street, so we can update its length and width directly
sql = string.Format("UPDATE INTERSECTION_SAMPLES SET INTERSEC_SAMP_AREA={0}, NOTES='{2}' WHERE INTER_SAMP_ID={1} ", INTERSEC_SAMP_AREA, INTER_SAMP_ID, NOTES);
int rows = db.ExecuteNonQuery(sql);
Shared.SaveLogfile("INTERSECTION_SAMPLES", INTER_SAMP_ID.ToString(), "Update", user);
return(rows > 0);
}
else
{
// surveys have been done over this secondar street, so we have to remove them before we can update its length and width directly
sql = string.Format("UPDATE INTERSECTION_SAMPLES SET INTERSEC_SAMP_AREA={0}, NOTES='{2}' WHERE INTER_SAMP_ID={1} ", INTERSEC_SAMP_AREA, INTER_SAMP_ID, NOTES);
int rows = db.ExecuteNonQuery(sql);
//if (INTERSEC_SAMP_AREA == 0)
//{
// sql = string.Format("delete from DISTRESS WHERE INTER_SAMP_ID={0} ", INTER_SAMP_ID);
// db.ExecuteNonQuery(sql);
//}
//else
//{
// sql = string.Format("UPDATE DISTRESS SET STATUS='N' WHERE INTER_SAMP_ID={0} ", INTER_SAMP_ID);
// db.ExecuteNonQuery(sql);
//}
//sql = string.Format("UPDATE UDI SET UDI_DATE=NULL, UDI_VALUE=NULL, UDI_RATE=NULL, UDI_UP_DATE=NULL, UDI_UPD_VALUE=NULL, UDI_UPD_RATE=NULL, STATUS='N', STATUS_UPD='N' WHERE INTER_SAMP_ID={0} ", INTER_SAMP_ID);
//db.ExecuteNonQuery(sql);
//sql = string.Format("DELETE FROM PREVENT_MAINT_DECISIONS WHERE INTER_SAMP_ID={0} ", INTER_SAMP_ID);
//db.ExecuteNonQuery(sql);
//sql = string.Format("DELETE FROM MAINTENANCE_DECISIONS WHERE INTER_SAMP_ID={0} ", INTER_SAMP_ID);
//db.ExecuteNonQuery(sql);
rows += FixDistressesAfterAreaChange(INTER_SAMP_ID, INTERSEC_SAMP_AREA, user);
sql = string.Format("UPDATE UDI_INTERSECTION_SAMPLE SET UDI_DATE=NULL, UDI_VALUE=NULL, UDI_RATE=NULL WHERE INTER_SAMP_ID={0} ", INTER_SAMP_ID);
rows += db.ExecuteNonQuery(sql);
Shared.SaveLogfile("INTERSECTION_SAMPLES", INTER_SAMP_ID.ToString(), "Update", user);
return(rows > 0);
}
}
public bool UpdateImageDetails(int RECORD_ID, string DETAILS)
{
if (RECORD_ID == 0)
{
return(false);
}
string detailsPart = string.IsNullOrEmpty(DETAILS) ? "NULL" : string.Format("'{0}'", DETAILS.Replace("'", "''"));
string sql = string.Format("update PHOTOS set DETAILS={0} where RECORD_ID={1} ", detailsPart, RECORD_ID);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool Insert(string Projects_NAME, string Projects_NO)
{
Projects_NAME = Projects_NAME.Replace("'", "''");
Projects_NO = string.IsNullOrEmpty(Projects_NO) ? "NULL" : string.Format("'{0}'", Projects_NO.Replace("'", "''"));
string sql = string.Format("insert into Projects(Projects_NO, Projects_NAME, Projects_ID) " +
"values({0}, '{1}', SEQ_Projects.nextval) ", Projects_NO, Projects_NAME);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public int FixDistressesAfterAreaChange(double SECOND_ST_LENGTH, double SECOND_ST_WIDTH, int SECOND_ID, string user)
{
int rows = 0;
string sql = "";
if (SECOND_ST_LENGTH == 0 || SECOND_ST_WIDTH == 0)
{
sql = string.Format("delete from DISTRESS WHERE STREET_ID={0} ", SECOND_ID); // second_id
rows += db.ExecuteNonQuery(sql);
}
else
{
int distID = 0;
int distressCode = 0;
char DIST_SEVERITY;
double distressDensity = 0;
double distArea = 0;
double deductValue = 0;
double densityDashValue = 0;
double deductDashValue = 0;
double SampleArea = SECOND_ST_LENGTH * SECOND_ST_WIDTH;
sql = string.Format("SELECT STATUS_UPD, dist_code, DIST_SEVERITY, DIST_AREA, DIST_ID FROM DISTRESS WHERE STREET_ID={0} ", SECOND_ID); // SECOND_ID
DataTable dtSecondStDist = db.ExecuteQuery(sql);
foreach (DataRow dr in dtSecondStDist.Rows)
{
distID = int.Parse(dr["DIST_ID"].ToString());
distressCode = int.Parse(dr["dist_code"].ToString());
DIST_SEVERITY = dr["DIST_SEVERITY"].ToString()[0];
distArea = double.Parse(dr["DIST_AREA"].ToString());
distressDensity = (distArea / SampleArea) * 100.0;
distressDensity = (distressDensity > 100) ? 100 : distressDensity;
deductValue = DistressShared.CalculateDeductValue(distressCode, DIST_SEVERITY);
densityDashValue = DistressShared.CalculateDensityDashValue(distressCode, double.Parse(distressDensity.ToString("G2")));
deductDashValue = DistressShared.CalculateDeductDashValue(deductValue, densityDashValue);
sql = string.Format("UPDATE DISTRESS SET STATUS='N', STATUS_UPD='N', DIST_DENSITY={0}, DEDUCT_VALUE={1}, DEN_DASH={2}, DEDUCT_DEN_DASH={3} WHERE DIST_ID={4} ",
distressDensity.ToString("0.00"), deductValue.ToString("0.00"), densityDashValue.ToString("0.00"), deductDashValue.ToString("0.00"), distID);
rows += db.ExecuteNonQuery(sql);
Shared.SaveLogfile("DISTRESS", distID.ToString(), "Distress Update", user);
}
}
return(rows);
}
public bool Insert(int DIST_CODE, string DISTRESS_AR_TYPE, string DISTRESS_EN_TYPE, bool DISTRESS_SEVERITY, double DISTRESS_DENSITY_L, double DISTRESS_DENSITY_M,
double DISTRESS_DENSITY_H)
{
DISTRESS_AR_TYPE = string.IsNullOrEmpty(DISTRESS_AR_TYPE) ? "NULL" : string.Format("'{0}'", DISTRESS_AR_TYPE.Replace("'", "''"));
DISTRESS_EN_TYPE = string.IsNullOrEmpty(DISTRESS_EN_TYPE) ? "NULL" : string.Format("'{0}'", DISTRESS_EN_TYPE.Replace("'", "''"));
// 0 1 2 3 4 5 6
string sql = string.Format("insert into DISTRESS_CODE(DIST_CODE, DISTRESS_AR_TYPE, DISTRESS_EN_TYPE, DISTRESS_SEVERITY, DISTRESS_DENSITY_L, DISTRESS_DENSITY_M, DISTRESS_DENSITY_H) " +
" values({0}, {1}, {2}, '{3}', {4}, {5}, {6}) ", DIST_CODE, DISTRESS_AR_TYPE, DISTRESS_EN_TYPE, Shared.Bool2YN(DISTRESS_SEVERITY), DISTRESS_DENSITY_L,
DISTRESS_DENSITY_M, DISTRESS_DENSITY_H);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool Insert(string CONTRACT_NO, string CONTRACT_NAME, DateTime?START_DATE, DateTime?END_DATE, DateTime?CONTRACT_DATE, int CONTRACTOR_ID)
{
CONTRACT_NAME = CONTRACT_NAME.Replace("'", "''");
CONTRACT_NO = CONTRACT_NO.Replace("'", "''");
string contractBeginPart = (START_DATE == null) ? "NULL" : string.Format("'{0}'", ((DateTime)START_DATE).ToString("dd/MM/yyyy"));
string contractEndPart = (END_DATE == null) ? "NULL" : string.Format("'{0}'", ((DateTime)END_DATE).ToString("dd/MM/yyyy"));
string contractDatePart = (CONTRACT_DATE == null) ? "NULL" : string.Format("'{0}'", ((DateTime)CONTRACT_DATE).ToString("dd/MM/yyyy"));
string sql = string.Format("insert into CONTRACT(CONTRACT_ID, CONTRACT_NO, CONTRACT_NAME, START_DATE, END_DATE, CONTRACT_DATE, CONTRACTOR_ID) values(SEQ_CONTRACTS.nextval, '{0}', '{1}', {2}, {3}, {4}, {5}) ",
CONTRACT_NO, CONTRACT_NAME, contractBeginPart, contractEndPart, contractDatePart, CONTRACTOR_ID);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool Insert(string SURVEYOR_NAME, DateTime?SURVEYOR_WORK_STARTDATE, DateTime?SURVEYOR_WORK_ENDDATE, string SURVEYOR_PHONE_NO, bool SUSPENDED)
{
SURVEYOR_NAME = SURVEYOR_NAME.Replace("'", "''");
SURVEYOR_PHONE_NO = string.IsNullOrEmpty(SURVEYOR_PHONE_NO) ? "NULL" : string.Format("'{0}'", SURVEYOR_PHONE_NO.Replace("'", "''"));
string workEndDate = (SURVEYOR_WORK_ENDDATE == null) ?
(SUSPENDED ? string.Format("to_date('{0}', 'DD/MM/YYYY')", DateTime.Today.ToString("dd/MM/yyyy")) : "NULL") :
string.Format("to_date('{0}', 'DD/MM/YYYY')", ((DateTime)SURVEYOR_WORK_ENDDATE).ToString("dd/MM/yyyy"));
string sql = string.Format("INSERT INTO SURVEYORS (SURVEYOR_NO, SURVEYOR_NAME, SURVEYOR_WORK_STARTDATE, SURVEYOR_WORK_ENDDATE, SURVEYOR_PHONE_NO, SUSPENDED) " +
" VALUES (SEQ_SURVEYORS.nextval, '{0}', to_date('{1}', 'DD/MM/YYYY'), {2}, {3}, {4}) ",
SURVEYOR_NAME, ((DateTime)SURVEYOR_WORK_STARTDATE).ToString("dd/MM/yyyy"), workEndDate, SURVEYOR_PHONE_NO, Shared.Bool2Int(SUSPENDED));
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool Insert(string CONTRACTOR_NAME, string PHONE, string FAX, string MOBILE, string EMAIL, string CONTRACTOR_NO)
{
CONTRACTOR_NAME = CONTRACTOR_NAME.Replace("'", "''");
PHONE = string.IsNullOrEmpty(PHONE) ? "NULL" : string.Format("'{0}'", PHONE.Replace("'", "''"));
FAX = string.IsNullOrEmpty(FAX) ? "NULL" : string.Format("'{0}'", FAX.Replace("'", "''"));
MOBILE = string.IsNullOrEmpty(MOBILE) ? "NULL" : string.Format("'{0}'", MOBILE.Replace("'", "''"));
EMAIL = string.IsNullOrEmpty(EMAIL) ? "NULL" : string.Format("'{0}'", EMAIL.Replace("'", "''"));
CONTRACTOR_NO = string.IsNullOrEmpty(CONTRACTOR_NO) ? "NULL" : string.Format("'{0}'", CONTRACTOR_NO.Replace("'", "''"));
// 0 1 2 3 4 5
string sql = string.Format("insert into CONTRACTOR(CONTRACTOR_NO, CONTRACTOR_NAME, PHONE, FAX, MOBILE, EMAIL, CONTRACTOR_ID) " +
"values({0}, '{1}', {2}, {3}, {4}, {5}, SEQ_CONTRACTOR.nextval) ", CONTRACTOR_NO, CONTRACTOR_NAME, PHONE, FAX, MOBILE, EMAIL);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool Update(double MIN, double MAX, int RATING_ID)
{
if (RATING_ID == 0)
{
return(false);
}
string sql = string.Format("update UDI_RATINGS set MIN={0}, MAX={1} where RATING_ID={2} ", MIN, MAX, RATING_ID);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool AddImage(int ID, string imageName, string details, RoadType type)
{
if (ID == 0 || string.IsNullOrEmpty(imageName))
{
return(false);
}
string sql = "";
imageName = imageName.Replace("'", "''");
string detailsPart = string.IsNullOrEmpty(details) ? "NULL" : string.Format("'{0}'", details.Replace("'", "''"));
switch (type)
{
case RoadType.Section:
sql = string.Format("insert into PHOTOS(RECORD_ID, SECTION_ID, PHOTO_NAME, DETAILS, entry_date) values (SEQ_PHOTOS.nextval, {0}, '{1}', {2}, (select sysdate from dual)) ", ID, imageName, detailsPart);
break;
case RoadType.Intersect:
sql = string.Format("insert into PHOTOS(RECORD_ID, INTER_ID, PHOTO_NAME, DETAILS, entry_date) values (SEQ_PHOTOS.nextval, {0}, '{1}', {2}, (select sysdate from dual)) ", ID, imageName, detailsPart);
break;
case RoadType.RegionSecondarySt:
sql = string.Format("insert into PHOTOS(RECORD_ID, STREET_ID, PHOTO_NAME, DETAILS, entry_date) values (SEQ_PHOTOS.nextval, {0}, '{1}', {2}, (select sysdate from dual)) ", ID, imageName, detailsPart);
break; // SECOND_ID
default:
break;
}
if (!string.IsNullOrEmpty(sql))
{
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
else
{
return(false);
}
}
public bool UpdateLaneInfo(double?LANE_LENGTH, double?LANE_WIDTH, int?SAMPLE_COUNT, double?SAMPLE_AREA, int LANE_ID)
{
string laneLengthpart = (LANE_LENGTH == null) ? "NULL" : ((double)LANE_LENGTH).ToString("0.00");
string laneWidthpart = (LANE_WIDTH == null) ? "NULL" : ((double)LANE_WIDTH).ToString("0.00");
string samplesCountPart = (SAMPLE_COUNT == null) ? "NULL" : SAMPLE_COUNT.ToString();
string samplesAreapart = (SAMPLE_AREA == null) ? "NULL" : ((double)SAMPLE_AREA).ToString("0.00");
string sql = string.Format("update LANE set LANE_LENGTH={0}, LANE_WIDTH={1}, SAMPLE_COUNT={2}, SAMPLE_AREA={3} where LANE_ID='{4}' ",
laneLengthpart, laneWidthpart, samplesCountPart, samplesAreapart, LANE_ID);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool UpdateLaneInfo(double SAMPLE_LENGTH, double SAMPLE_WIDTH, int SAMPLE_ID, string user, string NOTES)
{
string sql = "";
if (SAMPLE_LENGTH == 0 || SAMPLE_WIDTH == 0)
{
sql = string.Format("delete from DISTRESS WHERE SAMPLE_ID={0} ", SAMPLE_ID);
db.ExecuteNonQuery(sql);
}
sql = string.Format("update LANE_SAMPLES set SAMPLE_LENGTH={0}, SAMPLE_WIDTH={1}, NOTES='{3}' where SAMPLE_ID={2} ", SAMPLE_LENGTH, SAMPLE_WIDTH, SAMPLE_ID, NOTES);
int rows = db.ExecuteNonQuery(sql);
// SAMPLE_LENGTH={0}, SAMPLE_WIDTH={1}, SAMPLE_LENGTH, SAMPLE_WIDTH,
sql = string.Format("update LANE_SAMPLE_DETAILS set NOTES='{0}' where SAMPLE_ID={1} ", NOTES, SAMPLE_ID);
rows += db.ExecuteNonQuery(sql);
rows += FixDistressesAfterAreaChange(SAMPLE_LENGTH, SAMPLE_WIDTH, SAMPLE_ID, user);
Shared.SaveLogfile("LANE_SAMPLES", SAMPLE_ID.ToString(), "Update Area", user);
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 bool Insert(string r4No, string r4Name, DateTime?r4Date, DateTime?openingDate, DateTime?surveyDate, DateTime?sectionsDefiningDate, int contractorID,
bool houses, bool warehouses, bool commerial, bool gardens, bool indisterial, bool rest_house, bool publics, bool drinage_cb_true, string drinage_cb_count,
bool drinage_mh_true, string drinage_mh_count, bool sewage_mh_true, string sewage_mh_count, bool Elect_mh_true, string Elect_mh_count, bool stc_mh_true,
string stc_mh_count, bool water_valve_true, string water_valve_count, bool notPavedByMunic, string NotPavedDetails, bool OwnedByMunic, string OwnedDetails,
char populationDensity, string topographyDetails, bool needMidIsland, bool needTrees, bool needLighting, bool needInfraWorks, bool needTrafficSigns,
bool needServiceLanes, bool needSpeedBumps, string neededLanesCount, bool innerWaterHas, string soilType, string moreDetails, int lightingContractorID,
DateTime?lightingFinishDate, string lightingContractName, string lightingContractNo, int treesContractorID, DateTime?treesFinishDate, string treesContractName,
string treesContractNo, int pavingContractorID, DateTime?pavingFinishDate, string pavingContractName, string pavingContractNo, string r4_length, int mainStID)
{
int rows = 0;
int newID = 0;
string r4_lengthPart = string.IsNullOrEmpty(r4_length) ? "NULL" : decimal.Parse(r4_length).ToString("N2");
string r4DatePart = (r4Date == null) ? "NULL" : string.Format("'{0}'", ((DateTime)r4Date).ToString("dd/MM/yyyy"));
string openingDatePart = (openingDate == null) ? "NULL" : string.Format("'{0}'", ((DateTime)openingDate).ToString("dd/MM/yyyy"));
string surveyDatePart = (surveyDate == null) ? "NULL" : string.Format("'{0}'", ((DateTime)surveyDate).ToString("dd/MM/yyyy"));
string sectionsDefiningDatePart = (sectionsDefiningDate == null) ? "NULL" : string.Format("'{0}'", ((DateTime)sectionsDefiningDate).ToString("dd/MM/yyyy"));
string neededLanesPart = string.IsNullOrEmpty(neededLanesCount) ? "NULL" : int.Parse(neededLanesCount).ToString();
string drinageCbCount = (drinage_cb_true || !string.IsNullOrEmpty(drinage_cb_count)) ? drinage_cb_count : "NULL";
string drinageMhCount = (drinage_mh_true || !string.IsNullOrEmpty(drinage_mh_count)) ? drinage_mh_count : "NULL";
string sewageMhCount = (sewage_mh_true || !string.IsNullOrEmpty(sewage_mh_count)) ? sewage_mh_count : "NULL";
string ElectMhCount = (Elect_mh_true || !string.IsNullOrEmpty(Elect_mh_count)) ? Elect_mh_count : "NULL";
string stcMhCount = (stc_mh_true || !string.IsNullOrEmpty(stc_mh_count)) ? stc_mh_count : "NULL";
string waterValveCount = (water_valve_true || !string.IsNullOrEmpty(water_valve_count)) ? water_valve_count : "NULL";
r4No = string.IsNullOrEmpty(r4No) ? "NULL" : string.Format("'{0}'", r4No.Replace("'", "''"));
r4Name = string.IsNullOrEmpty(r4Name) ? "NULL" : string.Format("'{0}'", r4Name.Replace("'", "''"));
NotPavedDetails = string.IsNullOrEmpty(NotPavedDetails) ? "NULL" : string.Format("'{0}'", NotPavedDetails.Replace("'", "''"));
OwnedDetails = string.IsNullOrEmpty(OwnedDetails) ? "NULL" : string.Format("'{0}'", OwnedDetails.Replace("'", "''"));
topographyDetails = string.IsNullOrEmpty(topographyDetails) ? "NULL" : string.Format("'{0}'", topographyDetails.Replace("'", "''"));
moreDetails = string.IsNullOrEmpty(moreDetails) ? "NULL" : string.Format("'{0}'", moreDetails.Replace("'", "''"));
soilType = string.IsNullOrEmpty(soilType) ? "NULL" : string.Format("'{0}'", soilType.Replace("'", "''"));
// 0 1 2 3 4 5 6
string sql = string.Format("insert into R4_STREETS(R4_ST_ID, R4_NO, R4_NAME, R4_DATE, OPENING_DATE, SURVEY_DATE, SECTIONS_DEFINING_DATE, CONTRACTOR_ID, " +
// 7 8 9 10 11 12 13 14 15 16
"DRAIN_CB_TRUE, DRAIN_CB_COUNT, DRAIN_MH_TRUE, DRAIN_MH_COUNT, ELEC_MH_TRUE, ELEC_MH_COUNT, WATER_MH_TRUE, WATER_MH_COUNT, SEWAGE_MH_TRUE, SEWAGE_MH_COUNT, " +
// 17 18 19 20 21 22 23 24 25 26 27
"STC_MH_TRUE, STC_MH_COUNT, NOT_PAVED_BY_MUNIC, NOT_PAVED_BY_DETAILS, OWNED_BY_MUNIC, OWNED_DETAILS, HOUSING, WAREHOUSES, COMMERCIAL, GARDENS, INDUSTRIAL, " +
// 28 29 30 31 32 33 34 35 36 37
"REST_HOUSES, PUBLICS, POPULATION, TOPOGRAPHY_DETAILS, NEED_MID_ISLAND, NEED_TREES, NEED_LIGHTING, NEED_INFRA_WORKS, NEED_TRAFFIC_SIGNS, NEEDED_LANES_COUNT, " +
// 38 39 40 41 42 43 44
"NEED_SERVICE_LANES, NEED_SPEED_BUMPS, INNER_WATER, SOIL_TYPE_DETAILS, R4_LENGTH, MORE_DETAILS, STREET_ID) " + // MAIN_ST_ID
" values(SEQ_R4_STREETS.nextval, {0}, {1}, {2}, {3}, {4}, {5}, {6}, " +
" '{7}', {8}, '{9}', {10}, '{11}', {12}, '{13}', {14}, '{15}', {16}, " +
" '{17}', {18}, '{19}', {20}, '{21}', {22}, '{23}', '{24}', '{25}', '{26}', '{27}', " +
" '{28}', '{29}', '{30}', {31}, '{32}', '{33}', " +
" '{34}', '{35}', '{36}', {37}, '{38}', '{39}', '{40}', {41}, {42}, {43}, {44}) ",
r4No, r4Name, r4DatePart, openingDatePart, surveyDatePart, sectionsDefiningDatePart, contractorID,
Shared.Bool2YN(drinage_cb_true), drinageCbCount, Shared.Bool2YN(drinage_mh_true), drinageMhCount, Shared.Bool2YN(Elect_mh_true), ElectMhCount,
Shared.Bool2YN(water_valve_true), waterValveCount, Shared.Bool2YN(sewage_mh_true), sewageMhCount,
Shared.Bool2YN(stc_mh_true), stcMhCount, Shared.Bool2YN(notPavedByMunic), NotPavedDetails, Shared.Bool2YN(OwnedByMunic), OwnedDetails, Shared.Bool2YN(houses),
Shared.Bool2YN(warehouses), Shared.Bool2YN(commerial), Shared.Bool2YN(gardens), Shared.Bool2YN(indisterial),
Shared.Bool2YN(rest_house), Shared.Bool2YN(publics), populationDensity, topographyDetails, Shared.Bool2YN(needMidIsland), Shared.Bool2YN(needTrees),
Shared.Bool2YN(needLighting), Shared.Bool2YN(needInfraWorks), Shared.Bool2YN(needTrafficSigns), neededLanesPart,
Shared.Bool2YN(needServiceLanes), Shared.Bool2YN(needSpeedBumps), Shared.Bool2YN(innerWaterHas), soilType, r4_lengthPart, moreDetails, mainStID);
newID = db.ExecuteInsertWithIDReturn(sql, "R4_STREETS");
if (newID != 0)
{
string lightingFinishDatePart = (lightingFinishDate == null) ? "NULL" : string.Format("'{0}'", ((DateTime)lightingFinishDate).ToString("dd/MM/yyyy"));
lightingContractNo = string.IsNullOrEmpty(lightingContractNo) ? "NULL" : string.Format("'{0}'", lightingContractNo.Replace("'", "''"));
lightingContractName = string.IsNullOrEmpty(lightingContractName) ? "NULL" : string.Format("'{0}'", lightingContractName.Replace("'", "''"));
// 0 0 1 2 3 4
sql = string.Format("insert into R4_LIGHTING(RECORD_ID, R4_ID, CONTRACTOR_ID, FINISH_DATE, CONTRACT_NAME, CONTRACT_NO) " +
" values({0}, {0}, {1}, {2}, {3}, {4})", newID, lightingContractorID, lightingFinishDatePart, lightingContractName, lightingContractNo);
rows += db.ExecuteNonQuery(sql);
string treesFinishDatePart = (treesFinishDate == null) ? "NULL" : string.Format("'{0}'", ((DateTime)treesFinishDate).ToString("dd/MM/yyyy"));
treesContractNo = string.IsNullOrEmpty(treesContractNo) ? "NULL" : string.Format("'{0}'", treesContractNo.Replace("'", "''"));
treesContractName = string.IsNullOrEmpty(treesContractName) ? "NULL" : string.Format("'{0}'", treesContractName.Replace("'", "''"));
sql = string.Format("insert into R4_PAVING(RECORD_ID, R4_ID, CONTRACTOR_ID, FINISH_DATE, CONTRACT_NAME, CONTRACT_NO) " +
" values({0}, {0}, {1}, {2}, {3}, {4})", newID, treesContractorID, treesFinishDatePart, treesContractName, treesContractNo);
rows += db.ExecuteNonQuery(sql);
string pavingFinishDatePart = (pavingFinishDate == null) ? "NULL" : string.Format("'{0}'", ((DateTime)pavingFinishDate).ToString("dd/MM/yyyy"));
pavingContractNo = string.IsNullOrEmpty(pavingContractNo) ? "NULL" : string.Format("'{0}'", pavingContractNo.Replace("'", "''"));
pavingContractName = string.IsNullOrEmpty(pavingContractName) ? "NULL" : string.Format("'{0}'", pavingContractName.Replace("'", "''"));
sql = string.Format("insert into R4_TREES(RECORD_ID, R4_ID, CONTRACTOR_ID, FINISH_DATE, CONTRACT_NAME, CONTRACT_NO) " +
" values({0}, {0}, {1}, {2}, {3}, {4})", newID, pavingContractorID, pavingFinishDatePart, pavingContractName, pavingContractNo);
rows += db.ExecuteNonQuery(sql);
return(rows > 0);
}
else
{
return(false);
}
}
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);
}
public bool RemoveSectionsPreviousMaintDecisions(int m_MAIN_ST_ID, int surveyNum)
{
try
{
string SQL = string.Format("delete from MAINTENANCE_DECISIONS where SECTION_ID in (select section_id from sections where STREET_ID={0}) and SURVEY_NO={1} ", m_MAIN_ST_ID, surveyNum);
Shared.LogMdStatment(SQL); // MAIN_STREET_ID
int res = db.ExecuteNonQuery(SQL);
return(true);
}
catch
{
return(false);
}
}
public bool Insert(string bridgeName, int sectionID, int intersectID, string details, bool pededstrians, bool midIsland, bool sideCurb, bool midIslandGood,
bool sideCurbGood, bool lighting, string lightLoc, string lightCount, bool elecMH, bool stcMH, string elecMhCount, string stcMhCount, bool shoulders, bool shouldersGood,
bool trafficSigns, bool guideSigns, int contractorID, string num, string user, int bridgeTypeID, int cammerTypeID, double?cammerheight, int supporterTypeID,
int?supportersCount, int sideBarrierTypeID, int?lanesCount, double?laneWidth, double?tileWidth, double?entranceWidth, double?x, double?y, double?z,
int?builtYear, bool curved, bool perpend, double?curbHeight, double?curbWidth, double?midIslandWidth, bool drain, int?openingsCount, double?bridgeHeight,
int surfaceTypeID)
{
if (sectionID == 0 && intersectID == 0)
{
return(false);
}
string openingsCountPart = (openingsCount == null) ? "NULL" : ((int)openingsCount).ToString();
string cammerheightPart = (cammerheight == null) ? "NULL" : ((double)cammerheight).ToString();
string supportersCountPart = (supportersCount == null) ? "NULL" : ((int)supportersCount).ToString();
string lanesCountPart = (lanesCount == null) ? "NULL" : ((int)lanesCount).ToString();
string laneWidthPart = (laneWidth == null) ? "NULL" : ((double)laneWidth).ToString();
string tileWidthPart = (tileWidth == null) ? "NULL" : ((double)tileWidth).ToString();
string entranceWidthPart = (entranceWidth == null) ? "NULL" : ((double)entranceWidth).ToString();
string xPart = (x == null) ? "NULL" : ((double)x).ToString();
string yPart = (y == null) ? "NULL" : ((double)y).ToString();
string zPart = (z == null) ? "NULL" : ((double)z).ToString();
string builtYearPart = (builtYear == null) ? "NULL" : ((int)builtYear).ToString();
string curbHeightPart = (curbHeight == null) ? "NULL" : ((double)curbHeight).ToString();
string bridgeHeightPart = (bridgeHeight == null) ? "NULL" : ((double)bridgeHeight).ToString();
string curbWidthPart = (curbWidth == null) ? "NULL" : ((double)curbWidth).ToString();
string midIslandWidthPart = (midIslandWidth == null) ? "NULL" : ((double)midIslandWidth).ToString();
string locationIdPart = (sectionID == 0) ? "INTERSECT_ID " : "SECTION_ID ";
string locationIDValue = (sectionID == 0) ? intersectID.ToString() : sectionID.ToString();
lightLoc = string.IsNullOrEmpty(lightLoc) ? "NULL" : string.Format("'{0}'", lightLoc.Replace("'", "''"));
details = string.IsNullOrEmpty(details) ? "NULL" : string.Format("'{0}'", details.Replace("'", "''"));
num = string.IsNullOrEmpty(num) ? "NULL" : string.Format("'{0}'", num.Replace("'", "''"));
string lightCountPart = lighting ? int.Parse(lightCount).ToString() : "NULL";
string elecMhCountPart = elecMH ? int.Parse(elecMhCount).ToString() : "NULL";
string stcMhCountPart = stcMH ? int.Parse(stcMhCount).ToString() : "NULL";
string sql = string.Format("insert into BRIDGES(BRIDGE_ID, BRIDGE_NAME, PEDESTRIAN, {0}, LIGHTING_TRUE, LIGHTING_LOC, LIGHTING_COUNT, MID_ISLAND_TRUE, " +
" SIDE_CURB_TRUE, MID_ISLAND_GOOD, SIDE_CURB_GOOD, SHOULDERS_TRUE, SHOULDERS_GOOD, " +
" ELEC_MH_TRUE, ELEC_MH_COUNT, STC_MH_TRUE, STC_MH_COUNT, TRAFFIC_SIGNS_TRUE, GUIDE_SIGNS_TRUE, DETAILS, " +
" CONTRACTOR_ID, BRIDGE_NO, BRIDGE_TYPE_ID, OPENINGS_COUNT, CAMMERS_TYPE_ID, CAMMERS_HEIGHT, SUPPORTER_TYPE_ID, SUPPORTERS_COUNT, SIDE_BARRIER_TYPE_ID, LANES_COUNT, " +
" LANE_WIDTH, TILE_WIDTH, ENTRANCE_WIDTH, COORD_X, COORD_Y, COORD_Z, BUILT_YEAR, BRIDGE_HEIGHT, CURVED, ROAD_PERPEND, " +
" CURB_HEIGHT, CURB_WIDTH, MID_ISLAND_WIDTH, DRAIN_EXISTS, SURFACE_TYPE_ID) " +
" values(SEQ_BRIDGE.nextval, '{1}', '{2}', {3}, '{4}', {5}, {6}, '{7}', " +
" '{8}', '{9}', '{10}', '{11}', '{12}', " +
" '{13}', {14}, '{15}', {16}, '{17}', '{18}', {19}, " +
" {20}, {21}, {22}, {23}, {24}, {25}, {26}, {27}, {28}, {29}, " +
" {30}, {31}, {32}, {33}, {34}, {35}, {36}, {37}, '{38}', '{39}', " +
" {40}, {41}, {42}, '{43}', {44}) ",
// cammersCountPart, int? cammersCount,
locationIdPart, bridgeName, Shared.Bool2YN(pededstrians), locationIDValue, Shared.Bool2YN(lighting), lightLoc, lightCountPart, Shared.Bool2YN(midIsland),
Shared.Bool2YN(sideCurb), Shared.Bool2YN(midIslandGood), Shared.Bool2YN(sideCurbGood), Shared.Bool2YN(shoulders), Shared.Bool2YN(shouldersGood),
Shared.Bool2YN(elecMH), elecMhCountPart, Shared.Bool2YN(stcMH), stcMhCountPart, Shared.Bool2YN(trafficSigns), Shared.Bool2YN(guideSigns), details,
contractorID, num, bridgeTypeID, openingsCountPart, cammerTypeID, cammerheightPart, supporterTypeID, supportersCountPart, sideBarrierTypeID, lanesCountPart,
laneWidthPart, tileWidthPart, entranceWidthPart, xPart, yPart, zPart, builtYearPart, bridgeHeightPart, Shared.Bool2YN(curved), Shared.Bool2YN(perpend),
curbHeightPart, curbWidthPart, midIslandWidthPart, Shared.Bool2YN(drain), surfaceTypeID);
//int rows = db.ExecuteNonQuery(sql);
int newID = db.ExecuteNonQuery(sql); //.ExecuteInsertWithIDReturn(sql, "BRIDGES");
Shared.SaveLogfile("Bridges", newID.ToString(), "Insert Info", user);
return(newID > 0);
}
public bool Insert(string PROPOSE_TITLE, string APPROVE_DATE, string LETTER_FROM, string LETTER_DATE, string LETTER_NO, string COMMITTE_HEAD_NAME, string NOTES,
string MUNIC_NAME, string APPROVE_DATE_H, string LETTER_DATE_H)
{
string approveDatePart = (APPROVE_DATE == null) ? "NULL" : string.Format("'{0}'", DateTime.Parse(APPROVE_DATE).ToString("dd/MM/yyyy"));
string letterDatePart = (LETTER_DATE == null) ? "NULL" : string.Format("'{0}'", DateTime.Parse(LETTER_DATE).ToString("dd/MM/yyyy"));
PROPOSE_TITLE = PROPOSE_TITLE.Replace("'", "''");
LETTER_FROM = LETTER_FROM.Replace("'", "''");
COMMITTE_HEAD_NAME = COMMITTE_HEAD_NAME.Replace("'", "''");
LETTER_NO = string.IsNullOrEmpty(LETTER_NO) ? "NULL" : string.Format("'{0}'", LETTER_NO.Replace("'", "''"));
NOTES = string.IsNullOrEmpty(NOTES) ? "NULL" : string.Format("'{0}'", NOTES.Replace("'", "''"));
APPROVE_DATE_H = string.IsNullOrEmpty(APPROVE_DATE_H) ? "NULL" : string.Format("'{0}'", APPROVE_DATE_H.Replace("'", "''"));
LETTER_DATE_H = string.IsNullOrEmpty(LETTER_DATE_H) ? "NULL" : string.Format("'{0}'", LETTER_DATE_H.Replace("'", "''"));
// 0 1 2 3 4 5 6 7 8 9
string sql = string.Format("insert into TRAFF_ENHANCES(RECORD_ID, PROPOSE_TITLE, APPROVE_DATE, LETTER_FROM, LETTER_DATE, LETTER_NO, COMMITTE_HEAD_NAME, NOTES, MUNIC_NAME, APPROVE_DATE_H, LETTER_DATE_H) " +
" values(SEQ_TRAFFIC_ENHANCES.nextval, '{0}', {1}, '{2}', {3}, {4}, '{5}', {6}, '{7}', {8}, {9}) ",
PROPOSE_TITLE, approveDatePart, LETTER_FROM, letterDatePart, LETTER_NO, COMMITTE_HEAD_NAME, NOTES, MUNIC_NAME, APPROVE_DATE_H, LETTER_DATE_H);
int rows = db.ExecuteNonQuery(sql);
if (rows > 0)
{
// TODO: Add job order for newly added traffic enhancement entry
}
return(rows > 0);
}
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 Insert(string CONTRACT_NO, string CONTRACT_NAME, int CONTRACTOR_ID, DateTime?CONTRACT_DATE, DateTime?CONTRACT_BEGIN, DateTime?CONTRACT_END,
int WORK_STATUS)
{
CONTRACT_NO = CONTRACT_NO.Replace("'", "''");
CONTRACT_NAME = CONTRACT_NAME.Replace("'", "''");
bool upWorking = (WORK_STATUS == 1);
// bool upWorking, bool stopped, bool cancelled
//CONTRACT_DATE = string.IsNullOrEmpty(CONTRACT_DATE) ? "NULL" : string.Format("'{0}'", Shared.FormatDateArEgDMY(CONTRACT_DATE));
//CONTRACT_BEGIN = string.IsNullOrEmpty(CONTRACT_BEGIN) ? "NULL" : string.Format("'{0}'", Shared.FormatDateArEgDMY(CONTRACT_BEGIN));
//CONTRACT_END = string.IsNullOrEmpty(CONTRACT_END) ? "NULL" : string.Format("'{0}'", Shared.FormatDateArEgDMY(CONTRACT_END));
string contractDatePart = (CONTRACT_DATE == null) ? "NULL" : string.Format("'{0}'", ((DateTime)CONTRACT_DATE).ToString("dd/MM/yyyy"));
string contractBeginPart = (CONTRACT_BEGIN == null) ? "NULL" : string.Format("'{0}'", ((DateTime)CONTRACT_BEGIN).ToString("dd/MM/yyyy"));
string contractEndPart = (CONTRACT_END == null) ? "NULL" : string.Format("'{0}'", ((DateTime)CONTRACT_END).ToString("dd/MM/yyyy"));
string sql = string.Format("INSERT INTO MAINTENANCE_ORDERS(MAINTAIN_ORDER_ID, CONTRACT_NO, CONTRACT_NAME, CONTRACTOR_ID, CONTRACT_DATE, CONTRACT_BEGIN, CONTRACT_END, UP_WORKING, WORK_STATUS) " +
" VALUES (SEQ_MAINTENANCE_ORDERS.nextval, '{0}', '{1}', {2}, {3}, {4}, {5}, {6}, {7}) ",
CONTRACT_NO, CONTRACT_NAME, CONTRACTOR_ID, contractDatePart, contractBeginPart, contractEndPart, Shared.Bool2Int(upWorking), WORK_STATUS);
int rows = db.ExecuteNonQuery(sql);
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 bool Update_SURVEYABLE(string REGION_NO, int REGION_ID, bool SURVEYABLE, string NOTES)
{
if (REGION_ID == 0 || string.IsNullOrEmpty(REGION_NO) || REGION_NO.Length < 6)
{
return(false);
}
string sql = string.Format("update regions set SURVEYABLE={0} , NOTES='{1}' where REGION_NO={2} and REGION_ID={3}", SURVEYABLE == true ? 1 : 0, NOTES, REGION_NO, REGION_ID);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool Insert(string CONTRACT_NO, int CONTRACTOR_ID, string JOB_ORDER_NO, DateTime?JOB_ORDER_DATE, DateTime?FINISH_DATE)
{
CONTRACT_NO = CONTRACT_NO.Replace("'", "''");
JOB_ORDER_NO = JOB_ORDER_NO.Replace("'", "''");
string jobOrderDate = ((DateTime)JOB_ORDER_DATE).ToString("dd/MM/yyyy");
string finishDate = ((DateTime)FINISH_DATE).ToString("dd/MM/yyyy");
string sql = string.Format("insert into FEEDBACKS(FEEDBACK_ID, CONTRACTOR_ID, CONTRACT_NO, JOB_ORDER_NO, JOB_ORDER_DATE, FINISH_DATE, ENTRY_DATE) " +
" values(SEQ_FEEDBACKS.nextval, {0}, '{1}', '{2}', To_date('{3}','DD/MM/YYYY'), To_date('{4}','DD/MM/YYYY'), (select sysdate from dual)) ",
CONTRACTOR_ID, CONTRACT_NO, JOB_ORDER_NO, jobOrderDate, finishDate);
int rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}