public IHttpActionResult Insert([FromBody] JOBDC jobDC)
{
try
{
JOBBL objUser = new JOBBL();
List <JOBDC> list = new List <JOBDC>();
list.Add(jobDC);
int id = objUser.Insert(list);
jobDC.JOB_ID = id;
Task.Run(() => ApplyGeocodingAsync(null, jobDC, Request));
return(Ok(id));
}
catch (Exception ex)
{
if (ex.Message.Contains("UNIQUE KEY constraint"))
{
throw new System.InvalidOperationException("JobNumber");
}
else
{
throw ex;
}
}
}
public IHttpActionResult UpdateGeoCodding([FromBody] JOBDC jobDC)
{
List <EXCEPTIONDC> lstException = new List <EXCEPTIONDC>();
try
{
var bl = new JOBBL();
JOBBL objUser = new JOBBL();
JOBDC oldObj = new JOBDC();
//get Object before saving
oldObj = objUser.LoadByPrimaryKey(jobDC.JOB_ID);
List <JOBDC> list = new List <JOBDC>();
list.Add(jobDC);
int UpdatedCount = bl.Update(list, ref lstException);
Task.Run(() => ApplyGeocodingAsync(oldObj, jobDC, Request));
return(Ok(UpdatedCount));
}
catch (Exception ex)
{
return(new TextResult(lstException, Request));
}
}
public JOBDC LoadByKey(DBConnection Connection, int PROJECT_ID, int JOB_FILE_NUMBER)
{
JOBDC objMANAGE_JOB = new JOBDC();
StringBuilder sql = new StringBuilder();
sql.Append("proc_MANAGE_JOBSLoadByKey");
DBCommandWarpper dbCommandWrapper = new DBCommandWarpper(Connection.dataBase.GetStoredProcCommand(sql.ToString()), Connection);
dbCommandWrapper.AddInParameter("p_PROJECT_ID", DbType.Int32, PROJECT_ID);
dbCommandWrapper.AddInParameter("p_JOB_FILE_NUMBER", DbType.String, JOB_FILE_NUMBER);
IDataReader reader = null;
if (Connection.Transaction != null)
{
reader = Connection.dataBase.ExecuteReader(dbCommandWrapper.DBCommand, Connection.Transaction);
}
else
{
reader = Connection.dataBase.ExecuteReader(dbCommandWrapper.DBCommand);
}
objMANAGE_JOB = FillObject(reader);
return(objMANAGE_JOB);
}
public JOBDC LoadByPrimaryKey(DBConnection Connection, int JOB_ID)
{
JOBDC objMANAGE_JOB = new JOBDC();
StringBuilder sql = new StringBuilder();
sql.Append("proc_MANAGE_JOBSLoadByPrimaryKey");
DBCommandWarpper dbCommandWrapper = new DBCommandWarpper(Connection.dataBase.GetStoredProcCommand(sql.ToString()), Connection);
dbCommandWrapper.AddInParameter("p_JOB_ID", DbType.Int32, JOB_ID);
DataSet ds = new DataSet();
if (Connection.Transaction != null)
{
ds = Connection.dataBase.ExecuteDataSet(dbCommandWrapper.DBCommand, Connection.Transaction);
}
else
{
ds = Connection.dataBase.ExecuteDataSet(dbCommandWrapper.DBCommand);
}
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
objMANAGE_JOB = FillObject(ds.Tables[0].Rows[0]);
}
return(objMANAGE_JOB);
}
private int Delete(DBConnection Connection, JOBDC objMANAGE_JOB)
{
int deleteCount = 0;
try
{
StringBuilder sql = new StringBuilder();
sql.Append("proc_MANAGE_JOBSDelete");
DBCommandWarpper dbCommandWrapper = new DBCommandWarpper(Connection.dataBase.GetStoredProcCommand(sql.ToString()), Connection);
dbCommandWrapper.AddInParameter("p_JOB_ID", DbType.Int32, objMANAGE_JOB.JOB_ID);
if (Connection.Transaction != null)
{
deleteCount = Connection.dataBase.ExecuteNonQuery(dbCommandWrapper.DBCommand, Connection.Transaction);
}
else
{
deleteCount = Connection.dataBase.ExecuteNonQuery(dbCommandWrapper.DBCommand);
}
}
catch (Exception exp)
{
//Utilities.InsertIntoErrorLog("Error: JOB DELETE ", exp.Message + "\r\n" + exp.StackTrace, Convert.ToInt32(objMANAGE_JOB.MODIFIED_BY));
objMANAGE_JOB.SetError(exp);
throw exp;
}
return(deleteCount);
}
public IHttpActionResult GetByID(int jobid)
{
JOBBL objUser = new JOBBL();
JOBDC objResult = new JOBDC();
objResult = objUser.LoadByPrimaryKey(jobid);
return(Ok(new { objResult }));
}
public IHttpActionResult Get(int projectid, int jobfilenumber)
{
JOBBL objUser = new JOBBL();
JOBDC objResult = new JOBDC();
objResult = objUser.LoadByKey(projectid, jobfilenumber);
return(Ok(new { objResult }));
}
private async Task ApplyGeocodingAsync(JOBDC oldObject, JOBDC newObject, HttpRequestMessage request)
{
bool applyGeoCoding = false;
if (oldObject == null)
{
applyGeoCoding = true;
//new geo codding
}
if (oldObject != null && oldObject.CompleteAddress != newObject.CompleteAddress)
{
applyGeoCoding = true;
}
if (applyGeoCoding)
{
JOBBL jobsBL = new JOBBL();
string completeAddress = newObject.CompleteAddress;
GeocoderLocation geoCode = null;
try
{
geoCode = await GMGeocoder.GoeCodeAsync(completeAddress);
}
catch (Exception exp)
{
int userID = Common.Utility.GetUserID(Request);
Util.Utility.InsertIntoErrorLog(exp.Message, exp.StackTrace, userID);
}
if (geoCode != null)
{
newObject.LAT = geoCode.Latitude.ToString();
newObject.LONG = geoCode.Longitude.ToString();
if (newObject != null)
{
List <EXCEPTIONDC> lstException = new List <EXCEPTIONDC>();
try
{
List <JOBDC> jobListToBeUpdated = new List <JOBDC>();
jobListToBeUpdated.Add(newObject);
if (jobListToBeUpdated.Count > 0)
{
jobsBL.Update(jobListToBeUpdated, ref lstException, true);
}
}
catch (Exception exp)
{
new TextResult(lstException, request);
}
}
}
}
//return "Finished";
}
public JOBDC LoadByKey(int PROJECT_ID, int JOB_FILE_NUMBER)
{
DBConnection objConnection = new DBConnection();
JOBDA objMANAGE_JOBDA = new JOBDA();
JOBDC objMANAGE_JOBDC = null;
try
{
objConnection.Open(false);
objMANAGE_JOBDC = objMANAGE_JOBDA.LoadByKey(objConnection, PROJECT_ID, JOB_FILE_NUMBER);
}
catch (Exception ex)
{
throw ex;
}
finally
{
objConnection.Close();
}
return(objMANAGE_JOBDC);
}
public JOBDC LoadByPrimaryKey(int JOB_ID)
{
DBConnection objConnection = new DBConnection();
JOBDA objMANAGE_JOBDA = new JOBDA();
JOBDC objMANAGE_JOBDC = null;
try
{
objConnection.Open(false);
objMANAGE_JOBDC = objMANAGE_JOBDA.LoadByPrimaryKey(objConnection, JOB_ID);
}
catch (Exception ex)
{
throw ex;
}
finally
{
objConnection.Close();
}
return(objMANAGE_JOBDC);
}
private JOBDC FillObject(DataRow row)
{
JOBDC objMANAGE_JOB = null;
objMANAGE_JOB = new JOBDC();
objMANAGE_JOB.JOB_ID = (int)row["JOB_ID"];
objMANAGE_JOB.PROJECT_ID = (int)row["PROJECT_ID"];
if (row.Table.Columns.Contains("HYLAN_PROJECT_ID"))
{
objMANAGE_JOB.HYLAN_PROJECT_ID = Convert.ToString(row["HYLAN_PROJECT_ID"]);
}
if (row.Table.Columns.Contains("NEEDED_TASKS_COUNT"))
{
objMANAGE_JOB.NEEDED_TASKS_COUNT = (int)row["NEEDED_TASKS_COUNT"];
}
objMANAGE_JOB.JOB_FILE_NUMBER = Convert.ToString(row["JOB_FILE_NUMBER"]);
objMANAGE_JOB.CJ_NUMBER = Convert.ToString(row["CJ_NUMBER"]);
objMANAGE_JOB.DID_NUMBER = Convert.ToString(row["DID_NUMBER"]);
objMANAGE_JOB.NODE_ID1 = Convert.ToString(row["NODE_ID1"]);
objMANAGE_JOB.NODE_ID2 = Convert.ToString(row["NODE_ID2"]);
objMANAGE_JOB.NODE_ID3 = Convert.ToString(row["NODE_ID3"]);
objMANAGE_JOB.HUB = Convert.ToString(row["HUB"]);
objMANAGE_JOB.HYLAN_PM = row["HYLAN_PM"] == DBNull.Value ? null : (int?)row["HYLAN_PM"];
objMANAGE_JOB.STREET_ADDRESS = Convert.ToString(row["STREET_ADDRESS"]);
objMANAGE_JOB.CITY = Convert.ToString(row["CITY"]);
objMANAGE_JOB.STATE = (String)row["STATE"];
objMANAGE_JOB.ZIP = Convert.ToString(row["ZIP"]);
objMANAGE_JOB.LAT = Convert.ToString(row["LAT"]);
objMANAGE_JOB.LONG = Convert.ToString(row["LONG"]);
objMANAGE_JOB.POLE_LOCATION = Convert.ToString(row["POLE_LOCATION"]);
objMANAGE_JOB.DOITT_NTP_STATUS = row["DOITT_NTP_STATUS"] == DBNull.Value ? null : (int?)row["DOITT_NTP_STATUS"];
if (objMANAGE_JOB.DOITT_NTP_STATUS != null && row.Table.Columns.Contains("DOITT_NTP_STATUS_NAME"))
{
objMANAGE_JOB.DOITT_NTP_STATUS_LU.LOOK_UP_ID = (int)objMANAGE_JOB.DOITT_NTP_STATUS;
objMANAGE_JOB.DOITT_NTP_STATUS_LU.LU_TYPE = "DOITT_NTP_STATUS";
objMANAGE_JOB.DOITT_NTP_STATUS_LU.LU_NAME = (String)row["DOITT_NTP_STATUS_NAME"];
}
objMANAGE_JOB.DOITT_NTP_GRANTED_DATE = row["DOITT_NTP_GRANTED_DATE"] == DBNull.Value ? null : (DateTime?)row["DOITT_NTP_GRANTED_DATE"];
objMANAGE_JOB.JOB_CATEGORY = row["JOB_CATEGORY"] == DBNull.Value ? null : (int?)row["JOB_CATEGORY"];
if (objMANAGE_JOB.JOB_CATEGORY != null && row.Table.Columns.Contains("JOB_CATEGORY_NAME"))
{
objMANAGE_JOB.JOB_CATEGORY_LU.LOOK_UP_ID = (int)objMANAGE_JOB.JOB_CATEGORY;
objMANAGE_JOB.JOB_CATEGORY_LU.LU_TYPE = "JOB_CATEGORY";
objMANAGE_JOB.JOB_CATEGORY_LU.LU_NAME = (String)row["JOB_CATEGORY_NAME"];
}
objMANAGE_JOB.JOB_STATUS = (int)row["JOB_STATUS"];
if (row.Table.Columns.Contains("JOB_STATUS_NAME"))
{
objMANAGE_JOB.JOB_STATUS_LU.LOOK_UP_ID = (int)objMANAGE_JOB.JOB_STATUS;
objMANAGE_JOB.JOB_STATUS_LU.LU_TYPE = "JOB_STATUS";
objMANAGE_JOB.JOB_STATUS_LU.LU_NAME = (String)row["JOB_STATUS_NAME"];
}
objMANAGE_JOB.ON_HOLD_REASON = Convert.ToString(row["ON_HOLD_REASON"]);
objMANAGE_JOB.CLIENT_PM = Convert.ToString(row["CLIENT_PM"]);
objMANAGE_JOB.JOB_NOTES = Utilities.NotesFormat(Convert.ToInt32(row["NOTES_COUNT"].ToString()), row["NOTES_DATE"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(row["NOTES_DATE"].ToString()));
objMANAGE_JOB.ATTACHMENTS = Utilities.AttachmentsFormat(Convert.ToInt32(row["ATTACHMENTS_COUNT"].ToString()));
objMANAGE_JOB.NUMBER_OF_SUBMITTED_PERMITS = row["NUMBER_OF_SUBMITTED_PERMITS"] == DBNull.Value ? null : (int?)row["NUMBER_OF_SUBMITTED_PERMITS"];
objMANAGE_JOB.PERMIT_NOTES = Convert.ToString(row["PERMIT_NOTES"]);
objMANAGE_JOB.PUNCHLIST_COMPLETE = Convert.ToString(row["PUNCHLIST_COMPLETE"]);
objMANAGE_JOB.PUNCHLIST_SUBMITTED_DATE = row["PUNCHLIST_SUBMITTED_DATE"] == DBNull.Value ? null : (DateTime?)row["PUNCHLIST_SUBMITTED_DATE"];
objMANAGE_JOB.CLIENT_APPROVAL_DATE = row["CLIENT_APPROVAL_DATE"] == DBNull.Value ? null : (DateTime?)row["CLIENT_APPROVAL_DATE"];
objMANAGE_JOB.PRIORITY = Convert.ToString(row["PRIORITY"]);
objMANAGE_JOB.CREATED_BY = row["CREATED_BY"] == DBNull.Value ? null : (int?)row["CREATED_BY"];
objMANAGE_JOB.CREATED_ON = row["CREATED_ON"] == DBNull.Value ? null : (DateTime?)row["CREATED_ON"];
objMANAGE_JOB.MODIFIED_BY = row["MODIFIED_BY"] == DBNull.Value ? null : (int?)row["MODIFIED_BY"];
objMANAGE_JOB.MODIFIED_ON = row["MODIFIED_ON"] == DBNull.Value ? null : (DateTime?)row["MODIFIED_ON"];
objMANAGE_JOB.LOCK_COUNTER = row["LOCK_COUNTER"] == DBNull.Value ? null : (int?)row["LOCK_COUNTER"];
if (row.Table.Columns.Contains("HYLAN_PM_NAME"))
{
objMANAGE_JOB.HYLAN_PM_NAME = Convert.ToString(row["HYLAN_PM_NAME"]);
}
if (row.Table.Columns.Contains("CLIENT_NAME"))
{
objMANAGE_JOB.CLIENT_NAME = Convert.ToString(row["CLIENT_NAME"]);
}
if (row.Table.Columns.Contains("CLIENT"))
{
objMANAGE_JOB.CLIENT_ID = (int)(row["CLIENT"]);
}
if (row.Table.Columns.Contains("PERMITS_COUNT"))
{
objMANAGE_JOB.PermitsSummary.PERMITS_COUNT = row["PERMITS_COUNT"] == DBNull.Value ? null : (int?)row["PERMITS_COUNT"];
objMANAGE_JOB.PermitsSummary.ACTIVE_COUNT = row["ACTIVE_COUNT"] == DBNull.Value ? null : (int?)row["ACTIVE_COUNT"];
objMANAGE_JOB.PermitsSummary.EXPIRED_COUNT = row["EXPIRED_COUNT"] == DBNull.Value ? null : (int?)row["EXPIRED_COUNT"];
objMANAGE_JOB.PermitsSummary.EXPIRING_5DAYS_COUNT = row["EXPIRING_5DAYS_COUNT"] == DBNull.Value ? null : (int?)row["EXPIRING_5DAYS_COUNT"];
objMANAGE_JOB.PermitsSummary.ON_HOLD_COUNT = row["ON_HOLD_COUNT"] == DBNull.Value ? null : (int?)row["ON_HOLD_COUNT"];
objMANAGE_JOB.PermitsSummary.REQUEST_EXTENSION_COUNT = row["REQUEST_EXTENSION_COUNT"] == DBNull.Value ? null : (int?)row["REQUEST_EXTENSION_COUNT"];
objMANAGE_JOB.PermitsSummary.REQUEST_RENEWAL_COUNT = row["REQUEST_RENEWAL_COUNT"] == DBNull.Value ? null : (int?)row["REQUEST_RENEWAL_COUNT"];
objMANAGE_JOB.PermitsSummary.PENDING_COUNT = row["PENDING_COUNT"] == DBNull.Value ? null : (int?)row["PENDING_COUNT"];
objMANAGE_JOB.PermitsSummary.REJECTED_COUNT = row["REJECTED_COUNT"] == DBNull.Value ? null : (int?)row["REJECTED_COUNT"];
}
objMANAGE_JOB.PO_NUMBER = row["PO_NUMBER"] == DBNull.Value ? null : (String)row["PO_NUMBER"];
objMANAGE_JOB.PO_AMOUNT = row["PO_AMOUNT"] == DBNull.Value ? 0 : (decimal?)row["PO_AMOUNT"];
objMANAGE_JOB.INVOICE_DATE = row["INVOICE_DATE"] == DBNull.Value ? null : (DateTime?)row["INVOICE_DATE"];
objMANAGE_JOB.INVOICE_AMOUNT = row["INVOICE_AMOUNT"] == DBNull.Value ? 0 : (decimal?)row["INVOICE_AMOUNT"];
return(objMANAGE_JOB);
}
private JOBDC FillObject(IDataReader reader)
{
JOBDC objMANAGE_JOB = null;
if (reader != null && reader.Read())
{
DataTable schemaTable = reader.GetSchemaTable();
objMANAGE_JOB = new JOBDC();
objMANAGE_JOB.JOB_ID = (int)reader["JOB_ID"];
objMANAGE_JOB.PROJECT_ID = (int)reader["PROJECT_ID"];
if (schemaTable.Columns.Contains("HYLAN_PROJECT_ID"))
{
objMANAGE_JOB.HYLAN_PROJECT_ID = Convert.ToString(reader["HYLAN_PROJECT_ID"]);
}
objMANAGE_JOB.JOB_FILE_NUMBER = Convert.ToString(reader["JOB_FILE_NUMBER"]);
objMANAGE_JOB.CJ_NUMBER = Convert.ToString(reader["CJ_NUMBER"]);
objMANAGE_JOB.DID_NUMBER = Convert.ToString(reader["DID_NUMBER"]);
objMANAGE_JOB.NODE_ID1 = Convert.ToString(reader["NODE_ID1"]);
objMANAGE_JOB.NODE_ID2 = Convert.ToString(reader["NODE_ID2"]);
objMANAGE_JOB.NODE_ID3 = Convert.ToString(reader["NODE_ID3"]);
objMANAGE_JOB.HUB = Convert.ToString(reader["HUB"]);
objMANAGE_JOB.HYLAN_PM = objMANAGE_JOB.HYLAN_PM = reader["HYLAN_PM"] == DBNull.Value ? null : (int?)reader["HYLAN_PM"];
objMANAGE_JOB.STREET_ADDRESS = Convert.ToString(reader["STREET_ADDRESS"]);
objMANAGE_JOB.CITY = Convert.ToString(reader["CITY"]);
objMANAGE_JOB.STATE = (String)reader["STATE"];
objMANAGE_JOB.ZIP = Convert.ToString(reader["ZIP"]);
objMANAGE_JOB.LAT = Convert.ToString(reader["LAT"]);
objMANAGE_JOB.LONG = Convert.ToString(reader["LONG"]);
objMANAGE_JOB.POLE_LOCATION = Convert.ToString(reader["POLE_LOCATION"]);
objMANAGE_JOB.DOITT_NTP_STATUS = reader["DOITT_NTP_STATUS"] == DBNull.Value ? null : (int?)reader["DOITT_NTP_STATUS"];
if (objMANAGE_JOB.DOITT_NTP_STATUS != null && schemaTable.Columns.Contains("DOITT_NTP_STATUS_NAME"))
{
objMANAGE_JOB.DOITT_NTP_STATUS_LU.LOOK_UP_ID = (int)objMANAGE_JOB.DOITT_NTP_STATUS;
objMANAGE_JOB.DOITT_NTP_STATUS_LU.LU_TYPE = "DOITT_NTP_STATUS";
objMANAGE_JOB.DOITT_NTP_STATUS_LU.LU_NAME = (String)reader["DOITT_NTP_STATUS_NAME"];
}
objMANAGE_JOB.DOITT_NTP_GRANTED_DATE = reader["DOITT_NTP_GRANTED_DATE"] == DBNull.Value ? null : (DateTime?)reader["DOITT_NTP_GRANTED_DATE"];
objMANAGE_JOB.JOB_CATEGORY = reader["JOB_CATEGORY"] == DBNull.Value ? null : (int?)reader["JOB_CATEGORY"];
if (objMANAGE_JOB.JOB_CATEGORY != null && schemaTable.Columns.Contains("JOB_CATEGORY_NAME"))
{
objMANAGE_JOB.JOB_CATEGORY_LU.LOOK_UP_ID = (int)objMANAGE_JOB.JOB_CATEGORY;
objMANAGE_JOB.JOB_CATEGORY_LU.LU_TYPE = "JOB_CATEGORY";
objMANAGE_JOB.JOB_CATEGORY_LU.LU_NAME = (String)reader["JOB_CATEGORY_NAME"];
}
objMANAGE_JOB.JOB_STATUS = (int)reader["JOB_STATUS"];
if (schemaTable.Columns.Contains("JOB_STATUS_NAME"))
{
objMANAGE_JOB.JOB_STATUS_LU.LOOK_UP_ID = (int)objMANAGE_JOB.JOB_STATUS;
objMANAGE_JOB.JOB_STATUS_LU.LU_TYPE = "JOB_STATUS";
objMANAGE_JOB.JOB_STATUS_LU.LU_NAME = (String)reader["JOB_STATUS_NAME"];
}
objMANAGE_JOB.ON_HOLD_REASON = Convert.ToString(reader["ON_HOLD_REASON"]);
objMANAGE_JOB.CLIENT_PM = Convert.ToString(reader["CLIENT_PM"]);
objMANAGE_JOB.JOB_NOTES = Utilities.NotesFormat(Convert.ToInt32(reader["NOTES_COUNT"].ToString()), reader["NOTES_DATE"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader["NOTES_DATE"].ToString()));
objMANAGE_JOB.ATTACHMENTS = Utilities.AttachmentsFormat(Convert.ToInt32(reader["ATTACHMENTS_COUNT"].ToString()));
objMANAGE_JOB.NUMBER_OF_SUBMITTED_PERMITS = reader["NUMBER_OF_SUBMITTED_PERMITS"] == DBNull.Value ? null : (int?)reader["NUMBER_OF_SUBMITTED_PERMITS"];
objMANAGE_JOB.PERMIT_NOTES = Convert.ToString(reader["PERMIT_NOTES"]);
objMANAGE_JOB.PUNCHLIST_COMPLETE = Convert.ToString(reader["PUNCHLIST_COMPLETE"]);
objMANAGE_JOB.PUNCHLIST_SUBMITTED_DATE = reader["PUNCHLIST_SUBMITTED_DATE"] == DBNull.Value ? null : (DateTime?)reader["PUNCHLIST_SUBMITTED_DATE"];
objMANAGE_JOB.CLIENT_APPROVAL_DATE = reader["CLIENT_APPROVAL_DATE"] == DBNull.Value ? null : (DateTime?)reader["CLIENT_APPROVAL_DATE"];
objMANAGE_JOB.PRIORITY = Convert.ToString(reader["PRIORITY"]);
objMANAGE_JOB.CREATED_BY = reader["CREATED_BY"] == DBNull.Value ? null : (int?)reader["CREATED_BY"];
objMANAGE_JOB.CREATED_ON = reader["CREATED_ON"] == DBNull.Value ? null : (DateTime?)reader["CREATED_ON"];
objMANAGE_JOB.MODIFIED_BY = reader["MODIFIED_BY"] == DBNull.Value ? null : (int?)reader["MODIFIED_BY"];
objMANAGE_JOB.MODIFIED_ON = reader["MODIFIED_ON"] == DBNull.Value ? null : (DateTime?)reader["MODIFIED_ON"];
objMANAGE_JOB.LOCK_COUNTER = reader["LOCK_COUNTER"] == DBNull.Value ? null : (int?)reader["LOCK_COUNTER"];
if (schemaTable.Columns.Contains("NEEDED_TASKS_COUNT"))
{
objMANAGE_JOB.NEEDED_TASKS_COUNT = (int)reader["NEEDED_TASKS_COUNT"];
}
if (schemaTable.Columns.Contains("CLIENT"))
{
objMANAGE_JOB.CLIENT_ID = (int)(reader["CLIENT"]);
}
objMANAGE_JOB.PO_NUMBER = reader["PO_NUMBER"] == DBNull.Value ? null : (String)reader["PO_NUMBER"];
objMANAGE_JOB.PO_AMOUNT = reader["PO_AMOUNT"] == DBNull.Value ? 0 : (decimal?)reader["PO_AMOUNT"];
objMANAGE_JOB.INVOICE_DATE = reader["INVOICE_DATE"] == DBNull.Value ? null : (DateTime?)reader["INVOICE_DATE"];
objMANAGE_JOB.INVOICE_AMOUNT = reader["INVOICE_AMOUNT"] == DBNull.Value ? 0 : (decimal?)reader["INVOICE_AMOUNT"];
reader.Close();
reader.Dispose();
}
return(objMANAGE_JOB);
}
private int Insert(DBConnection Connection, JOBDC objMANAGE_JOB)
{
int p_JOB_ID = 0;
StringBuilder sql = new StringBuilder();
sql.Append("proc_MANAGE_JOBSInsert");
DBCommandWarpper dbCommandWrapper = new DBCommandWarpper(Connection.dataBase.GetStoredProcCommand(sql.ToString()), Connection);
dbCommandWrapper.AddOutParameter("p_JOB_ID", DbType.Int32, objMANAGE_JOB.JOB_ID);
dbCommandWrapper.AddInParameter("p_PROJECT_ID", DbType.Int32, objMANAGE_JOB.PROJECT_ID);
dbCommandWrapper.AddInParameter("p_JOB_FILE_NUMBER", DbType.String, objMANAGE_JOB.JOB_FILE_NUMBER);
dbCommandWrapper.AddInParameter("p_NODE_ID1", DbType.String, objMANAGE_JOB.NODE_ID1);
dbCommandWrapper.AddInParameter("p_NODE_ID2", DbType.String, objMANAGE_JOB.NODE_ID2);
dbCommandWrapper.AddInParameter("p_NODE_ID3", DbType.String, objMANAGE_JOB.NODE_ID3);
dbCommandWrapper.AddInParameter("p_HUB", DbType.String, objMANAGE_JOB.HUB);
dbCommandWrapper.AddInParameter("p_HYLAN_PM", DbType.Int32, objMANAGE_JOB.HYLAN_PM);
dbCommandWrapper.AddInParameter("p_STREET_ADDRESS", DbType.String, objMANAGE_JOB.STREET_ADDRESS);
dbCommandWrapper.AddInParameter("p_CITY", DbType.String, objMANAGE_JOB.CITY);
dbCommandWrapper.AddInParameter("p_STATE", DbType.String, objMANAGE_JOB.STATE);
dbCommandWrapper.AddInParameter("p_ZIP", DbType.String, objMANAGE_JOB.ZIP);
dbCommandWrapper.AddInParameter("p_LAT", DbType.String, objMANAGE_JOB.LAT);
dbCommandWrapper.AddInParameter("p_LONG", DbType.String, objMANAGE_JOB.LONG);
dbCommandWrapper.AddInParameter("p_POLE_LOCATION", DbType.String, objMANAGE_JOB.POLE_LOCATION);
dbCommandWrapper.AddInParameter("p_DOITT_NTP_STATUS", DbType.Int32, objMANAGE_JOB.DOITT_NTP_STATUS);
dbCommandWrapper.AddInParameter("p_DOITT_NTP_GRANTED_DATE", DbType.DateTime, objMANAGE_JOB.DOITT_NTP_GRANTED_DATE);
dbCommandWrapper.AddInParameter("p_JOB_CATEGORY", DbType.Int32, objMANAGE_JOB.JOB_CATEGORY);
dbCommandWrapper.AddInParameter("p_JOB_STATUS", DbType.Int32, objMANAGE_JOB.JOB_STATUS);
dbCommandWrapper.AddInParameter("p_ON_HOLD_REASON", DbType.String, objMANAGE_JOB.ON_HOLD_REASON);
dbCommandWrapper.AddInParameter("p_CLIENT_PM", DbType.String, objMANAGE_JOB.CLIENT_PM);
dbCommandWrapper.AddInParameter("p_JOB_NOTES", DbType.String, objMANAGE_JOB.JOB_NOTES);
dbCommandWrapper.AddInParameter("p_NUMBER_OF_SUBMITTED_PERMITS", DbType.Int32, objMANAGE_JOB.NUMBER_OF_SUBMITTED_PERMITS);
dbCommandWrapper.AddInParameter("p_PERMIT_NOTES", DbType.String, objMANAGE_JOB.PERMIT_NOTES);
dbCommandWrapper.AddInParameter("p_PUNCHLIST_COMPLETE", DbType.String, objMANAGE_JOB.PUNCHLIST_COMPLETE);
dbCommandWrapper.AddInParameter("p_PUNCHLIST_SUBMITTED_DATE", DbType.DateTime, objMANAGE_JOB.PUNCHLIST_SUBMITTED_DATE);
dbCommandWrapper.AddInParameter("p_CLIENT_APPROVAL_DATE", DbType.DateTime, objMANAGE_JOB.CLIENT_APPROVAL_DATE);
dbCommandWrapper.AddInParameter("p_PRIORITY", DbType.String, objMANAGE_JOB.PRIORITY);
dbCommandWrapper.AddInParameter("p_CJ_NUMBER", DbType.String, objMANAGE_JOB.CJ_NUMBER);
dbCommandWrapper.AddInParameter("p_DID_NUMBER", DbType.String, objMANAGE_JOB.DID_NUMBER);
dbCommandWrapper.AddInParameter("p_CREATED_BY", DbType.Int32, objMANAGE_JOB.CREATED_BY);
dbCommandWrapper.AddInParameter("p_CREATED_ON", DbType.DateTime, objMANAGE_JOB.CREATED_ON);
dbCommandWrapper.AddInParameter("p_MODIFIED_BY", DbType.String, objMANAGE_JOB.MODIFIED_BY);
dbCommandWrapper.AddInParameter("p_MODIFIED_ON", DbType.DateTime, objMANAGE_JOB.MODIFIED_ON);
dbCommandWrapper.AddInParameter("p_LOCK_COUNTER", DbType.Int32, objMANAGE_JOB.LOCK_COUNTER);
dbCommandWrapper.AddInParameter("p_PO_NUMBER", DbType.String, objMANAGE_JOB.PO_NUMBER);
dbCommandWrapper.AddInParameter("p_PO_AMOUNT", DbType.Decimal, objMANAGE_JOB.PO_AMOUNT);
dbCommandWrapper.AddInParameter("p_INVOICE_DATE", DbType.DateTime, objMANAGE_JOB.INVOICE_DATE);
dbCommandWrapper.AddInParameter("p_INVOICE_AMOUNT", DbType.Decimal, objMANAGE_JOB.INVOICE_AMOUNT);
if (Connection.Transaction != null)
{
p_JOB_ID = Connection.dataBase.ExecuteNonQuery(dbCommandWrapper.DBCommand, Connection.Transaction);
}
else
{
p_JOB_ID = Connection.dataBase.ExecuteNonQuery(dbCommandWrapper.DBCommand);
}
p_JOB_ID = Convert.ToInt32(dbCommandWrapper.DBCommand.Parameters["@p_JOB_ID"].Value);
return(p_JOB_ID);
}
public int Update(DBConnection Connection, JOBDC objMANAGE_JOB, bool OnlyUpdateLatLong = false)
{
int updateCount = 0;
StringBuilder sql = new StringBuilder();
sql.Append("proc_MANAGE_JOBSUpdate");
DBCommandWarpper dbCommandWrapper = new DBCommandWarpper(Connection.dataBase.GetStoredProcCommand(sql.ToString()), Connection);
dbCommandWrapper.AddInParameter("p_JOB_ID", DbType.Int32, objMANAGE_JOB.JOB_ID);
dbCommandWrapper.AddInParameter("p_PROJECT_ID", DbType.Int32, objMANAGE_JOB.PROJECT_ID);
dbCommandWrapper.AddInParameter("p_JOB_FILE_NUMBER", DbType.String, objMANAGE_JOB.JOB_FILE_NUMBER);
dbCommandWrapper.AddInParameter("p_NODE_ID1", DbType.String, objMANAGE_JOB.NODE_ID1);
dbCommandWrapper.AddInParameter("p_NODE_ID2", DbType.String, objMANAGE_JOB.NODE_ID2);
dbCommandWrapper.AddInParameter("p_NODE_ID3", DbType.String, objMANAGE_JOB.NODE_ID3);
dbCommandWrapper.AddInParameter("p_HUB", DbType.String, objMANAGE_JOB.HUB);
dbCommandWrapper.AddInParameter("p_HYLAN_PM", DbType.Int32, objMANAGE_JOB.HYLAN_PM);
dbCommandWrapper.AddInParameter("p_STREET_ADDRESS", DbType.String, objMANAGE_JOB.STREET_ADDRESS);
dbCommandWrapper.AddInParameter("p_CITY", DbType.String, objMANAGE_JOB.CITY);
dbCommandWrapper.AddInParameter("p_STATE", DbType.String, objMANAGE_JOB.STATE);
dbCommandWrapper.AddInParameter("p_ZIP", DbType.String, objMANAGE_JOB.ZIP);
dbCommandWrapper.AddInParameter("p_LAT", DbType.String, objMANAGE_JOB.LAT);
dbCommandWrapper.AddInParameter("p_LONG", DbType.String, objMANAGE_JOB.LONG);
dbCommandWrapper.AddInParameter("p_POLE_LOCATION", DbType.String, objMANAGE_JOB.POLE_LOCATION);
dbCommandWrapper.AddInParameter("p_DOITT_NTP_STATUS", DbType.Int32, objMANAGE_JOB.DOITT_NTP_STATUS);
dbCommandWrapper.AddInParameter("p_DOITT_NTP_GRANTED_DATE", DbType.DateTime, objMANAGE_JOB.DOITT_NTP_GRANTED_DATE);
dbCommandWrapper.AddInParameter("p_JOB_CATEGORY", DbType.Int32, objMANAGE_JOB.JOB_CATEGORY);
dbCommandWrapper.AddInParameter("p_JOB_STATUS", DbType.Int32, objMANAGE_JOB.JOB_STATUS);
dbCommandWrapper.AddInParameter("p_ON_HOLD_REASON", DbType.String, objMANAGE_JOB.ON_HOLD_REASON);
dbCommandWrapper.AddInParameter("p_CLIENT_PM", DbType.String, objMANAGE_JOB.CLIENT_PM);
dbCommandWrapper.AddInParameter("p_JOB_NOTES", DbType.String, objMANAGE_JOB.JOB_NOTES);
dbCommandWrapper.AddInParameter("p_NUMBER_OF_SUBMITTED_PERMITS", DbType.Int32, objMANAGE_JOB.NUMBER_OF_SUBMITTED_PERMITS);
dbCommandWrapper.AddInParameter("p_PERMIT_NOTES", DbType.String, objMANAGE_JOB.PERMIT_NOTES);
dbCommandWrapper.AddInParameter("p_PUNCHLIST_COMPLETE", DbType.String, objMANAGE_JOB.PUNCHLIST_COMPLETE);
dbCommandWrapper.AddInParameter("p_PUNCHLIST_SUBMITTED_DATE", DbType.DateTime, objMANAGE_JOB.PUNCHLIST_SUBMITTED_DATE);
dbCommandWrapper.AddInParameter("p_CLIENT_APPROVAL_DATE", DbType.DateTime, objMANAGE_JOB.CLIENT_APPROVAL_DATE);
dbCommandWrapper.AddInParameter("p_CJ_NUMBER", DbType.String, objMANAGE_JOB.CJ_NUMBER);
dbCommandWrapper.AddInParameter("p_DID_NUMBER", DbType.String, objMANAGE_JOB.DID_NUMBER);
dbCommandWrapper.AddInParameter("p_PRIORITY", DbType.String, objMANAGE_JOB.PRIORITY);
dbCommandWrapper.AddInParameter("p_CREATED_BY", DbType.Int32, objMANAGE_JOB.CREATED_BY);
dbCommandWrapper.AddInParameter("p_CREATED_ON", DbType.DateTime, objMANAGE_JOB.CREATED_ON);
dbCommandWrapper.AddInParameter("p_MODIFIED_BY", DbType.String, objMANAGE_JOB.MODIFIED_BY);
dbCommandWrapper.AddInParameter("p_MODIFIED_ON", DbType.DateTime, objMANAGE_JOB.MODIFIED_ON);
dbCommandWrapper.AddInParameter("p_OnlyUpdateLatLong", DbType.Boolean, OnlyUpdateLatLong);
dbCommandWrapper.AddInParameter("p_LOCK_COUNTER", DbType.Int32, objMANAGE_JOB.LOCK_COUNTER);
dbCommandWrapper.AddInParameter("p_PO_NUMBER", DbType.String, objMANAGE_JOB.PO_NUMBER);
dbCommandWrapper.AddInParameter("p_PO_AMOUNT", DbType.Decimal, objMANAGE_JOB.PO_AMOUNT);
dbCommandWrapper.AddInParameter("p_INVOICE_DATE", DbType.DateTime, objMANAGE_JOB.INVOICE_DATE);
dbCommandWrapper.AddInParameter("p_INVOICE_AMOUNT", DbType.Decimal, objMANAGE_JOB.INVOICE_AMOUNT);
try
{
if (Connection.Transaction != null)
{
updateCount = Connection.dataBase.ExecuteNonQuery(dbCommandWrapper.DBCommand, Connection.Transaction);
}
else
{
updateCount = Connection.dataBase.ExecuteNonQuery(dbCommandWrapper.DBCommand);
}
if (updateCount == 0)
{
objMANAGE_JOB.IsDirty = IsDirty = true;
throw new Exception(Constants.ConcurrencyMessageSingleRow);
}
}
catch (Exception exp)
{
//Utilities.InsertIntoErrorLog("Error: JOB UPDATE ", exp.Message + "\r\n" + exp.StackTrace, Convert.ToInt32(objMANAGE_JOB.MODIFIED_BY));
objMANAGE_JOB.SetError(exp);
throw exp;
}
return(updateCount);
}
