public static void UndoARejectedTruck(int MSID)
{
DateTime now = DateTime.Now;
try
{
ZXPUserData zxpUD = ZXPUserData.GetZXPUserDataFromCookie();
using (var scope = new TransactionScope())
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
//Create an entry in Event Log
sqlCmdText = "INSERT INTO dbo.MainScheduleEvents (MSID, EventTypeID,Timestamp, UserId, isHidden) " +
"VALUES (@MSID, 3037, @TIME, @USER, 'false'); " +
"SELECT SCOPE_IDENTITY()";
int eventID = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", MSID),
new SqlParameter("@TIME", now),
new SqlParameter("@USER", zxpUD._uid)));
sqlCmdText = "UPDATE dbo.MainScheduleEvents SET isHidden = 'true' " +
"WHERE (MSID = @MSID) AND EventTypeID = 2037;";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", MSID));
ChangeLog cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "MainSchedule", "isRejected", now, zxpUD._uid, ChangeLog.ChangeLogDataType.BIT, "false", eventID, "MSID", MSID.ToString());
cl.CreateChangeLogEntryIfChanged();
cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "MainSchedule", "LastUpdated", now, zxpUD._uid, ChangeLog.ChangeLogDataType.DATETIME, now.ToString(), eventID, "MSID", MSID.ToString());
cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.MainSchedule SET isRejected = 'false', LastUpdated = @TIME " +
"WHERE (MSID = @MSID)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", MSID),
new SqlParameter("@TIME", now));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in RejectTruck UndoARejectedTruck(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
}
public static void closePOStatus(int POnum)
{
try
{
using (var scope = new TransactionScope())
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "UPDATE dbo.MainSchedule SET isOpenInCMS = 0" +
"WHERE isHidden = 0 AND PONumber = @PONUM ";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@PONUM", POnum));
ChangeLog cLog;
cLog = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "MainSchedule", "isOpenInCMS", DateTime.Now, zxpUD._uid, ChangeLog.ChangeLogDataType.BIT, "0", null, "PONumber", POnum.ToString());
cLog.CreateChangeLogEntryIfChanged();
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in dataProcessingAndCleanup closePOStatus(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 2;
ErrorLogging.sendtoErrorPage(2);
throw excep;
}
catch (Exception ex)
{
string strErr = " Exception Error in dataProcessingAndCleanup closePOStatus(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
finally
{
}
}
public static string RejectATruck(int MSID)
{
DateTime now = DateTime.Now;
string nowFormatted;
try
{
ZXPUserData zxpUD = ZXPUserData.GetZXPUserDataFromCookie();
using (var scope = new TransactionScope())
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
ChangeLog cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "MainSchedule", "isRejected", now, zxpUD._uid, ChangeLog.ChangeLogDataType.BIT, "1", null, "MSID", MSID.ToString());
cl.CreateChangeLogEntryIfChanged();
cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "MainSchedule", "LastUpdated", now, zxpUD._uid, ChangeLog.ChangeLogDataType.DATETIME, now.ToString(), null, "MSID", MSID.ToString());
cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.MainSchedule SET isRejected = 1, LastUpdated = @TIME " +
"WHERE (MSID = @MSID)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@TIME", now),
new SqlParameter("@MSID", MSID));
scope.Complete();
}
MainScheduleEventLogger msEventLog = new MainScheduleEventLogger();
MainScheduleEvent msEvent = new MainScheduleEvent(MSID, 2037, null, now, zxpUD._uid, false);
int newEventID = msEventLog.createNewEventLog(msEvent);
string customAlertMsg = CreateCustomRejectTruckMessage(MSID);
msEventLog.TriggerExistingAlertForEvent(newEventID, customAlertMsg);
}
catch (Exception ex)
{
string strErr = " Exception Error in RejectTruck GetGridData(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
nowFormatted = now.ToString("ddd MMM dd yyyy HH:mm:ss K");
return(now.ToString());
}
public static DateTime completeRequest(int requestID, int requestTypeID, int MSID)
{
SqlConnection sqlConn = new SqlConnection();
DateTime timeStamp = DateTime.Now;
try
{
ZXPUserData zxpUD = ZXPUserData.GetZXPUserDataFromCookie();
sqlConn = new SqlConnection();
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
string sqlQuery = string.Empty;
int eventID = 0;
MainScheduleEventLogger msEventLog = new MainScheduleEventLogger();
if (requestTypeID == 1) //Load
{
MainScheduleEvent msEvent = new MainScheduleEvent(MSID, 16, null, timeStamp, zxpUD._uid, false);
string newAlertMsg = createCustomMessageForCompletedTask(requestID);
eventID = msEventLog.createNewEventLogAndTriggerExistingAlerts(msEvent, "Loading Finished. " + newAlertMsg);
}
else if (requestTypeID == 2) //Unload
{
MainScheduleEvent msEvent = new MainScheduleEvent(MSID, 14, null, timeStamp, zxpUD._uid, false);
string newAlertMsg = createCustomMessageForCompletedTask(requestID);
eventID = msEventLog.createNewEventLogAndTriggerExistingAlerts(msEvent, "Unloading Finished. " + newAlertMsg);
}
else if (requestTypeID == 3) //Other
{
MainScheduleEvent msEvent = new MainScheduleEvent(MSID, 2031, null, timeStamp, zxpUD._uid, false);
string newAlertMsg = createCustomMessageForCompletedTask(requestID);
eventID = msEventLog.createNewEventLogAndTriggerExistingAlerts(msEvent, newAlertMsg);
}
if (eventID == 0)
{
throw new Exception("Invalid eventID. Error creating new event log.");
}
using (var scope = new TransactionScope())
{
sqlQuery = "INSERT INTO dbo.MainScheduleRequestEvents(RequestID, EventID) " +
"VALUES(@RID, @EID)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlQuery, new SqlParameter("@RID", requestID), new SqlParameter("@EID", eventID));
sqlQuery = "UPDATE dbo.MainSchedule SET LastUpdated = @TIME WHERE (MSID = @MSID)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlQuery, new SqlParameter("@MSID", MSID), new SqlParameter("@TIME", timeStamp));
sqlConn = new SqlConnection(new TruckScheduleConfigurationKeysHelper().sql_connStr);
ChangeLog Cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleRequestEvents", "RequestID", timeStamp, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, requestID.ToString(), eventID, "RequestID", requestID.ToString(), "EventID", eventID.ToString());
Cl.CreateChangeLogEntryIfChanged();
Cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleRequestEvents", "EventID", timeStamp, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, eventID.ToString(), eventID, "RequestID", requestID.ToString(), "EventID", eventID.ToString());
Cl.CreateChangeLogEntryIfChanged();
Cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "MainSchedule", "LastUpdated", timeStamp, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, timeStamp.ToString(), eventID, "MSID", MSID.ToString());
Cl.CreateChangeLogEntryIfChanged();
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in loaderMobile completeRequest(). Details: " + excep.ToString();
ErrorLogging.LogErrorAndRedirect(2, strErr);
}
catch (Exception ex)
{
string strErr = " Exception Error in loaderMobile completeRequest(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
finally
{
if (sqlConn != null && sqlConn.State != ConnectionState.Closed)
{
sqlConn.Close();
sqlConn.Dispose();
}
}
return(timeStamp);
}
public static DateTime startRequest(int requestID, int requestTypeID, int MSID)
{
DateTime timeStamp = DateTime.Now;
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
ZXPUserData zxpUD = ZXPUserData.GetZXPUserDataFromCookie();
using (var scope = new TransactionScope())
{
string sqlCmdText = string.Empty;
if (requestTypeID == 1) //Load
{
//loader - EventTypeID = 15 --> "Loading Started"
sqlCmdText = "INSERT INTO dbo.MainScheduleEvents (MSID, EventTypeID,Timestamp, UserId, isHidden) " +
"VALUES (@MSID, 15, @TIME, @LOADER, 'false'); " +
"SELECT SCOPE_IDENTITY()";
}
else if (requestTypeID == 2) //Unload
{
//loader - EventTypeID = 13 --> "Unloading Started"
sqlCmdText = "INSERT INTO dbo.MainScheduleEvents (MSID, EventTypeID,Timestamp, UserId, isHidden) " +
"VALUES (@MSID, 13, @TIME, @LOADER, 'false'); " +
"SELECT SCOPE_IDENTITY()";
}
else if (requestTypeID == 3) //Other
{
//loader - EventTypeID = 2030 --> "Loader Assignment Started"
sqlCmdText = "INSERT INTO dbo.MainScheduleEvents (MSID, EventTypeID,Timestamp, UserId, isHidden) " +
"VALUES (@MSID, 2030, @TIME, @LOADER, 'false'); " +
"SELECT SCOPE_IDENTITY()";
}
int eventID = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", MSID),
new SqlParameter("@TIME", timeStamp),
new SqlParameter("@LOADER", zxpUD._uid)));
ChangeLog Cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "Requests", "Assignee", timeStamp, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, zxpUD._uid.ToString(), eventID, "RequestID", requestID.ToString(), "RequestID", requestID.ToString());
Cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.Requests SET Assignee = @LOADER WHERE RequestID = @RID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@LOADER", zxpUD._uid),
new SqlParameter("@RID", requestID));
Cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleRequestEvents", "RequestID", timeStamp, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, requestID.ToString(), eventID, "RequestID", requestID.ToString(), "EventID", eventID.ToString());
Cl.CreateChangeLogEntryIfChanged();
Cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleRequestEvents", "EventID", timeStamp, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, eventID.ToString(), eventID, "RequestID", requestID.ToString(), "EventID", eventID.ToString());
Cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "INSERT INTO dbo.MainScheduleRequestEvents(RequestID, EventID) " +
"VALUES(@RID, @EID)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@RID", requestID),
new SqlParameter("@EID", eventID));
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in loaderMobile startRequest(). Details: " + excep.ToString();
ErrorLogging.LogErrorAndRedirect(2, strErr);
}
catch (Exception ex)
{
string strErr = " Exception Error in loaderMobile startRequest(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(timeStamp);
}
public static void AddFileDBEntry(int MSID, string fileType, string filenameOld, string filenameNew, string filepath, string fileDescription, int SampleID)
{
DateTime now = DateTime.Now;
int eventID = 0;
try
{
ZXPUserData zxpUD = ZXPUserData.GetZXPUserDataFromCookie();
using (var scope = new TransactionScope())
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
//First find filetypeID
sqlCmdText = "SELECT FileTypeID FROM dbo.FileTypes WHERE FileType = @FTYPE";
int filetypeID = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@FTYPE", fileType)));
//1. create event in Main Schedule Events
sqlCmdText = "INSERT INTO dbo.MainScheduleEvents (MSID, EventTypeID, TimeStamp, UserID, isHidden) VALUES (@PMSID, 4097, @TSTAMP, @UserID, 'false'); " +
"SELECT CAST(scope_identity() AS int)";
eventID = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@PMSID", MSID),
new SqlParameter("@TSTAMP", now),
new SqlParameter("@UserID", zxpUD._uid)));
//2. create file data in Main Schedule Files
sqlCmdText = "INSERT INTO dbo.MainScheduleFiles (MSID, FileTypeID, FileDescription, Filepath, FilenameNew, FilenameOld, isHidden) " +
"VALUES (@PMSID, @PFTID, @PDESC, @PFPATH, @PFNEW, @PFOLD, 0);" +
"SELECT SCOPE_IDENTITY()";
int newFileID = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@PMSID", MSID),
new SqlParameter("@PFTID", filetypeID),
new SqlParameter("@PDESC", fileDescription),
new SqlParameter("@PFPATH", filepath),
new SqlParameter("@PFNEW", filenameNew),
new SqlParameter("@PFOLD", filenameOld)));
ChangeLog cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleFiles", "MSID", now, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, MSID.ToString(), eventID, "FileID", newFileID.ToString());
cl.CreateChangeLogEntryIfChanged();
cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleFiles", "FileTyepID", now, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, filetypeID.ToString(), eventID, "FileID", newFileID.ToString());
cl.CreateChangeLogEntryIfChanged();
cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleFiles", "FileDescription", now, zxpUD._uid, ChangeLog.ChangeLogDataType.NVARCHAR, fileDescription.ToString(), eventID, "FileID", newFileID.ToString());
cl.CreateChangeLogEntryIfChanged();
cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleFiles", "Filepath", now, zxpUD._uid, ChangeLog.ChangeLogDataType.NVARCHAR, filepath.ToString(), eventID, "FileID", newFileID.ToString());
cl.CreateChangeLogEntryIfChanged();
cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleFiles", "FilenameNew", now, zxpUD._uid, ChangeLog.ChangeLogDataType.NVARCHAR, filenameNew.ToString(), eventID, "FileID", newFileID.ToString());
cl.CreateChangeLogEntryIfChanged();
cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleFiles", "FilenameOld", now, zxpUD._uid, ChangeLog.ChangeLogDataType.NVARCHAR, filenameOld.ToString(), eventID, "FileID", newFileID.ToString());
cl.CreateChangeLogEntryIfChanged();
cl = new ChangeLog(ChangeLog.ChangeLogChangeType.INSERT, "MainScheduleFiles", "isHidden", now, zxpUD._uid, ChangeLog.ChangeLogDataType.BIT, "0", eventID, "FileID", newFileID.ToString());
cl.CreateChangeLogEntryIfChanged();
//3.Update LastUpdated in MainSchedule
cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "MainSchedule", "LastUpdated", now, zxpUD._uid, ChangeLog.ChangeLogDataType.DATETIME, now.ToString(), eventID, "MSID", MSID.ToString());
cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.MainSchedule SET LastUpdated = @TSTAMP WHERE (MSID = @PMSID)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@TSTAMP", now),
new SqlParameter("@PMSID", MSID));
//4. update samples tbl
cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "Samples", "FileID_COFA", now, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, newFileID.ToString(), eventID, "SampleID", SampleID.ToString());
cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.Samples SET FileID_COFA = @FID, COFAEventID = @eventID WHERE SampleID = @SampleID; ";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@FID", newFileID),
new SqlParameter("@eventID", eventID),
new SqlParameter("@SampleID", SampleID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in COFAUpload AddFileDBEntry(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
}
