public bool AddTrafficEnhanceLocationsforRegions(int trafficEnhanceID, int mainID, int subID, bool landUse, string landUseDetails)
{
int rows = 0;
string sql = "";
//string lang = HttpContext.Current.Session["lang"].ToString();
landUseDetails = string.IsNullOrEmpty(landUseDetails) ? "NULL" : string.Format("'{0}'", landUseDetails.Replace("'", "''"));
if (subID != 0)
{
sql = string.Format("select record_id from TRAFFENHANCE_DETAIL_LOCATIONS where TRAFFENHANCE_DET_ID={0} and SECOND_ST_ID={1} and IS_REGION=1 ", trafficEnhanceID, subID);
if (db.ExecuteQuery(sql).Rows.Count > 0)
{
throw new Exception(Feedback.InsertExceptionUnique());
}
// 0 1 2 3 4
sql = string.Format("insert into TRAFFENHANCE_DETAIL_LOCATIONS(RECORD_ID, TRAFFENHANCE_DET_ID, REGION_ID, STREET_ID, IS_REGION, IS_LAND_USE, LANDUSE_DETAILS) " +
" values(SEQ_TRAFFENHANCE_LOC.nextval, {0}, {1}, {2}, 1, {3}, {4}) ", // SECOND_ST_ID
trafficEnhanceID, mainID, subID, Shared.Bool2Int(landUse), landUseDetails);
rows = db.ExecuteNonQuery(sql);
}
else
{
// SECOND_ST_ID second_id
//sql = string.Format("select STREET_ID from SECONDARY_STREETS where REGION_ID={0} and second_id not in (select STREET_ID from TRAFFENHANCE_DETAIL_LOCATIONS where TRAFFENHANCE_DET_ID={1} and REGION_ID is not null) ", mainID, trafficEnhanceID);
sql = string.Format("select STREET_ID from STREETS where REGION_ID={0} and STREET_ID not in (select STREET_ID from TRAFFENHANCE_DETAIL_LOCATIONS where TRAFFENHANCE_DET_ID={1} and REGION_ID is not null) ", mainID, trafficEnhanceID);
DataTable dtSecSt = db.ExecuteQuery(sql);
foreach (DataRow dr in dtSecSt.Rows)
{
// 0 1 2 3 4
sql = string.Format("insert into TRAFFENHANCE_DETAIL_LOCATIONS(RECORD_ID, TRAFFENHANCE_DET_ID, REGION_ID, STREET_ID, IS_REGION, IS_LAND_USE, LANDUSE_DETAILS) " +
" values(SEQ_TRAFFENHANCE_LOC.nextval, {0}, {1}, {2}, 1, {3}, {4}) ",
trafficEnhanceID, mainID, dr["STREET_ID"].ToString(), Shared.Bool2Int(landUse), landUseDetails); // second_id
rows += db.ExecuteNonQuery(sql);
}
}
return(rows > 0);
}
public bool UpdateRegionQcCheckRecord(DateTime?QC_DATE, DateTime?SURVEY_DATE, int SURVEYOR_ID, int QC_CHECKER_ID, double?SURVEYOR_AREA, double?CHECKER_AREA,
int QC_CHECK_ID, bool IS_CORRECTED)
{
if (SURVEYOR_ID == QC_CHECKER_ID)
{
throw new Exception("مساح المنطقة لايمكن ان يكون هو نفس المراقب");
}
int rows = 0;
string areaSurveyorPart = (SURVEYOR_AREA == null) ? "NULL" : ((double)SURVEYOR_AREA).ToString("0.00");
string areaCheckerPart = (CHECKER_AREA == null) ? "NULL" : ((double)CHECKER_AREA).ToString("0.00");
string sql = string.Format("update QC_CHECK set QC_DATE='{0}', SURVEY_DATE='{1}', SURVEYOR_ID={2}, QC_CHECKER_ID={3}, SURVEYOR_AREA={4}, CHECKER_AREA={5}, " +
" IS_CORRECTED={6} where QC_CHECK_ID={7} ",
((DateTime)QC_DATE).ToString("dd/MM/yyyy"), ((DateTime)SURVEY_DATE).ToString("dd/MM/yyyy"), SURVEYOR_ID, QC_CHECKER_ID, SURVEYOR_AREA, CHECKER_AREA,
Shared.Bool2Int(IS_CORRECTED), QC_CHECK_ID);
rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool UpdateIntersectQcCheckRecord(DateTime?QC_DATE, DateTime?SURVEY_DATE, int SURVEYOR_ID, int QC_CHECKER_ID, int QC_CHECK_ID, bool IS_CORRECTED)
{
if (SURVEYOR_ID == QC_CHECKER_ID)
{
throw new Exception("مساح المنطقة لايمكن ان يكون هو نفس المراقب");
}
int rows = 0;
string sql = string.Format("update QC_CHECK set QC_DATE='{0}', SURVEY_DATE='{1}', SURVEYOR_ID={2}, QC_CHECKER_ID={3}, IS_CORRECTED={5} where QC_CHECK_ID={4} ",
((DateTime)QC_DATE).ToString("dd/MM/yyyy"), ((DateTime)SURVEY_DATE).ToString("dd/MM/yyyy"), SURVEYOR_ID, QC_CHECKER_ID, QC_CHECK_ID, Shared.Bool2Int(IS_CORRECTED));
rows = db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool Update(string CONTRACT_NO, string CONTRACT_NAME, int CONTRACTOR_ID, DateTime?CONTRACT_DATE, DateTime?CONTRACT_BEGIN, DateTime?CONTRACT_END,
int MAINTAIN_ORDER_ID, int WORK_STATUS)
{
// bool UP_WORKING, bool STOPPED, bool CANCELLED
//string CONTRACT_DATE, string CONTRACT_BEGIN, string CONTRACT_END, int MAINTAIN_ORDER_ID)
CONTRACT_NO = CONTRACT_NO.Replace("'", "''");
CONTRACT_NAME = CONTRACT_NAME.Replace("'", "''");
bool upWorking = (WORK_STATUS == 1);
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("UPDATE MAINTENANCE_ORDERS SET CONTRACT_NO='{0}', CONTRACT_NAME='{1}', CONTRACTOR_ID={2}, CONTRACT_DATE={3}, CONTRACT_BEGIN={4}, " +
" CONTRACT_END={5}, UP_WORKING={7}, WORK_STATUS={8} WHERE MAINTAIN_ORDER_ID={6} ",
CONTRACT_NO, CONTRACT_NAME, CONTRACTOR_ID, contractDatePart, contractBeginPart, contractEndPart, MAINTAIN_ORDER_ID, Shared.Bool2Int(upWorking),
WORK_STATUS);
int rows = db.ExecuteNonQuery(sql);
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 UpdateRegion(string REGION_NO, int SUBDISTRICT_ID, int REGION_ID, bool SURVEYABLE, string NOTES)
{
if (SUBDISTRICT_ID == 0 || REGION_ID == 0 || string.IsNullOrEmpty(REGION_NO) || REGION_NO.Length < 6)
{
return(false);
}
REGION_NO = REGION_NO.Replace("'", "''");
NOTES = string.IsNullOrEmpty(NOTES) ? "NULL" : string.Format("'{0}'", NOTES.Replace("'", "''"));
DataTable dt = distSubdist.GetSubDistFullInfo(SUBDISTRICT_ID);
if (dt.Rows.Count == 0)
{
return(false);
}
DataRow dr = dt.Rows[0];
DataTable dtDist = distSubdist.GetDistrictFullInfo(int.Parse(dr["DISTRICTNO"].ToString()));
if (dtDist.Rows.Count == 0)
{
return(false);
}
DataRow drDist = dtDist.Rows[0];
string sql = string.Format("update REGIONS set REGION_NO='{0}', SUBDISTRICT='{1}', DIST_NAME='{2}', DIST_NO='{3}', SUBMUNICIP={4}, SUBDISTRIC='{5}', " +
" ARNAME='{6}', ENNAME='{7}', DISTRICTNO={8}, DISTRICT_ID={8}, SUBDISTRICT_ID={9}, MUNIC_NAME='{10}', NOTES={11}, SURVEYABLE={12} " +
" where region_id={13} ",
REGION_NO, dr["ARNAME"].ToString(), drDist["ARNAME"].ToString(), drDist["DIST_NO"].ToString(), dr["SUBMUNICIP"].ToString(), dr["SUBDISTRIC"].ToString(),
dr["ARNAME"].ToString(), dr["ENNAME"].ToString(), dr["DISTRICTNO"].ToString(), SUBDISTRICT_ID, drDist["MUNIC_NAME"].ToString(), NOTES, Shared.Bool2Int(SURVEYABLE),
REGION_ID);
int rows = db.ExecuteNonQuery(sql);
sql = string.Format("update SECTIONS set SUBDISTRICT='{0}', district='{1}' where SECTION_NO like '{2}%' ",
dr["ARNAME"].ToString(), drDist["ARNAME"].ToString(), REGION_NO);
rows += db.ExecuteNonQuery(sql);
return(rows > 0);
}
public bool Update(string SURVEYOR_NAME, DateTime?SURVEYOR_WORK_STARTDATE, DateTime?SURVEYOR_WORK_ENDDATE, string SURVEYOR_PHONE_NO, int SURVEYOR_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) ? "NULL" : string.Format("to_date('{0}', 'DD/MM/YYYY')", ((DateTime)SURVEYOR_WORK_ENDDATE).ToString("dd/MM/yyyy"));
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("UPDATE SURVEYORS SET SURVEYOR_NAME='{0}', SURVEYOR_WORK_STARTDATE=to_date('{1}', 'DD/MM/YYYY'), SURVEYOR_WORK_ENDDATE={2}, " +
" SURVEYOR_PHONE_NO={3}, SUSPENDED={5} WHERE SURVEYOR_NO={4} ",
SURVEYOR_NAME, ((DateTime)SURVEYOR_WORK_STARTDATE).ToString("dd/MM/yyyy"), workEndDate, SURVEYOR_PHONE_NO, SURVEYOR_NO, Shared.Bool2Int(SUSPENDED));
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);
}