public bool createNewAuditLogEntry(AuditLog aLog)
{
bool didSucceed = false;
try
{
using (var scope = new TransactionScope())
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.StoredProcedure, "sp_truckschedapp_createNewAuditLogEntry", new SqlParameter("@pIPAddress", aLog.IPAddress),
new SqlParameter("@pUserID", aLog.UserID),
new SqlParameter("@pTimeStamp", aLog.TimeStamp),
new SqlParameter("@pPageAddress", aLog.PageAddress),
new SqlParameter("@pUserAgentStringID", aLog.UserAgentStringID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in AuditLog createNewAuditLogEntry(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
}
finally
{
}
return(didSucceed);
}
public static void updateTankCurrentVolume(int tankID, double newVolume)
{
try
{
using (var scope = new TransactionScope())
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "UPDATE dbo.Tanks SET CurrentTankVolume = @VOL WHERE TankID = @TANKID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@TANKID", tankID), new SqlParameter("@VOL", newVolume));
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in dataProcessingAndCleanup updateTankCurrentVolume(). 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 updateTankCurrentVolume(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
finally
{
}
}
public static void setCOFAComment(int SAMPLEID, string COMMENT)
{
DateTime now = DateTime.Now;
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, "Samples", "COFAComment", now, zxpUD._uid, ChangeLog.ChangeLogDataType.NVARCHAR, COMMENT, null, "SampleID", SAMPLEID.ToString());
cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.Samples SET COFAComment = @COFAComment " +
"WHERE (SampleID = @SampleID)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@COFAComment", COMMENT),
new SqlParameter("@SampleID", SAMPLEID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in COFAUpload setCOFAComment(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
}
public static Object getPendSamplesTruckData()
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT MS.MSID, MS.PONumber, MS.LocationShort, S.SampleID, S.TestApproved, TrailerNumber, MS.StatusID, isRejected, MS.isOpenInCMS, " +
"DATEDIFF(minute, (SELECT TOP 1 MSE.Timestamp FROM dbo.MainScheduleEvents MSE WHERE EventTypeID = 1025 AND MSE.MSID = MS.MSID and isHidden = 'false' ORDER BY TimeStamp DESC), GETDATE() ) AS DemurrageTime, " +
"PCMS.ProductName_CMS, POD.ProductID_CMS " +
"FROM dbo.MainSchedule AS MS " +
"INNER JOIN dbo.PODetails AS POD ON MS.MSID = POD.MSID " +
"INNER JOIN dbo.Samples AS S ON S.PODetailsID = POD.PODetailsID " +
"LEFT JOIN dbo.ProductsCMS AS PCMS ON POD.ProductID_CMS = PCMS.ProductID_CMS " +
"WHERE S.isHidden = 0 AND MS.isHidden = 0 AND MS.isOpenInCMS = 'true' AND S.TestApproved IS NULL " +
//"WHERE S.isHidden = 0 AND MS.isHidden = 0 AND TestApproved IS NULL AND (MS.LocationShort NOT IN ('NOS')) " +
"ORDER BY MS.MSID";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
}
catch (Exception ex)
{
string strErr = " Exception Error in WaitAndDockOverview getPendSamplesTruckData(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(data);
}
public static void DeleteFileDBEntry(int SampleID, 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;
sqlCmdText = "INSERT INTO dbo.MainScheduleEvents (MSID, EventTypeID, TimeStamp, UserID, isHidden) VALUES (@MSID, 4098, @TSTAMP, @UserID, 'false'); " +
"SELECT SCOPE_IDENTITY()";
int eventID = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", MSID),
new SqlParameter("@TSTAMP", now),
new SqlParameter("@UserID", zxpUD._uid)));
ChangeLog cl = new ChangeLog(ChangeLog.ChangeLogChangeType.UPDATE, "Samples", "FileID_COFA", now, zxpUD._uid, ChangeLog.ChangeLogDataType.INT, null, eventID, "SampleID", SampleID.ToString());
cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.Samples SET FileID_COFA = NULL, COFAEventID = NULL WHERE SampleID = @SampleID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@SampleID", SampleID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in COFAUpload DeleteFileDBEntry(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
}
public static Object GetPODetailsFromMSID(int MSID)
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT PD.PODetailsID, PD.ProductID_CMS, PD.QTY, PD.LotNumber, PD.UnitOfMeasure, PCMS.ProductName_CMS " +
"FROM dbo.PODetails PD " +
"LEFT JOIN dbo.ProductsCMS PCMS ON PCMS.ProductID_CMS = PD.ProductID_CMS " +
"WHERE PD.MSID = @MSID ORDER BY PD.ProductID_CMS ";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", MSID));
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
}
catch (Exception ex)
{
string strErr = " Exception Error in WaitAndDockOverview GetPODetailsFromMSID(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(data);
}
public static Object getDockBulkData()
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT MS.MSID, MS.PONumber, MS.TrailerNumber, MS.currentDockSpotID, " +
"(SELECT TOP(1) MSE.TimeStamp FROM dbo.MainScheduleEvents AS MSE WHERE MSE.MSID = MS.MSID AND MSE.isHidden = 'false' AND (MSE.EventTypeID = 6)) TimePlacedInDock, MS.isRejected, MS.isOpenInCMS, " +
"DATEDIFF(minute, (SELECT TOP 1 MSE.Timestamp FROM dbo.MainScheduleEvents MSE WHERE EventTypeID = 1025 AND MSE.MSID = MS.MSID and isHidden = 'false' ORDER BY TimeStamp DESC), GETDATE() ) AS DemurrageTime " +
"FROM dbo.MainSchedule AS MS WHERE MS.LocationShort = 'DOCKBULK' AND MS.isHidden = 'false'";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
}
catch (Exception ex)
{
string strErr = " Exception Error in WaitAndDockOverview getDockBulkData(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(data);
}
public int createNewUserAgentStringEntry()
{
int newUserAgentStringID;
SQLDataConnectionHelper sqlConnHelper = new SQLDataConnectionHelper();
try
{
using (var scope = new TransactionScope())
{
sqlConnHelper.setSqlConnectionStringUsingConfiguration();
sqlConnHelper.openConnection();
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
newUserAgentStringID = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.StoredProcedure, "sp_truckscheduleapp_insertNewUserAgentString", new SqlParameter("@pUserAgentString", this.userAgentString)));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in UserAgentString getUserAgentStringID(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
throw ex;
}
finally
{
if (sqlConnHelper.doesConnectionExist())
{
sqlConnHelper.closeConnection();
}
}
return(newUserAgentStringID);
}
public static Object getDockSpotsForVan()
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT SpotID, SpotDescription FROM TruckDockSpots Where SpotType = 'Van' AND isDisabled = 0 ORDER BY SpotDescription";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
}
catch (Exception ex)
{
string strErr = " Exception Error in WaitAndDockOverview getDockSpotsForVan(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(data);
}
public static Object GetLogList()
{
List <object[]> data = new List <object[]>();
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
TruckLogHelperFunctions.logListConnection(sql_connStr, data);
}
catch (SqlException excep)
{
string strErr = " SQLException Error in COFAUpload GetLogList(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 2;
ErrorLogging.sendtoErrorPage(2);
}
catch (Exception ex)
{
string strErr = " Exception Error in COFAUpload GetLogList(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
}
finally
{
}
return(data);
}
public static Object getWaitingAreaData()
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT MS.MSID, MS.PONumber, MS.TrailerNumber, MS.isDropTrailer," +
"(SELECT TOP 1 S.StatusText FROM dbo.Status AS S WHERE S.StatusID = MS.StatusID) AS Status, MS.isEmpty, MS.WaitingAreaComment, " +
"(SELECT TOP 1 MSE.TimeStamp FROM dbo.MainScheduleEvents AS MSE WHERE MSE.EventTypeID = 3074 AND MS.MSID = MSE.MSID AND isHidden = 'false') AS EmptyTime, MS.isOpenInCMS, MS.isRejected, " +
"DATEDIFF(minute, (SELECT TOP 1 MSE.Timestamp FROM dbo.MainScheduleEvents MSE WHERE EventTypeID = 1025 AND MSE.MSID = MS.MSID and isHidden = 'false' ORDER BY TimeStamp DESC), GETDATE() ) AS DemurrageTime, " +
"(SELECT TOP (1) MSE.TimeStamp FROM dbo.MainScheduleEvents AS MSE WHERE MSE.EventTypeID = 4 AND MS.MSID = MSE.MSID AND isHidden = 'false' order by TimeStamp DESC) AS ArrivedAtWaitTime " +
"FROM dbo.MainSchedule AS MS " +
"WHERE MS.LocationShort = 'WAIT' AND MS.isHidden = 'false'";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
}
catch (Exception ex)
{
string strErr = " Exception Error in WaitAndDockOverview getWaitingAreaData(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(data);
}
public static void SetRejectionComment(int MSID, string COMMENT)
{
DateTime now = DateTime.Now;
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", "RejectionComment", now, zxpUD._uid, ChangeLog.ChangeLogDataType.NVARCHAR, TransportHelperFunctions.convertStringEmptyToDBNULL(COMMENT).ToString(), null, "MSID", MSID.ToString());
cl.CreateChangeLogEntryIfChanged();
sqlCmdText = "UPDATE dbo.MainSchedule SET RejectionComment = @COMMENT " +
"WHERE (MSID = @MSID)";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@COMMENT", TransportHelperFunctions.convertStringEmptyToDBNULL(COMMENT)),
new SqlParameter("@MSID", MSID));
scope.Complete();
}
}
catch (Exception ex)
{
string strErr = " Exception Error in RejectTruck SetRejectionComment(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
}
public static List <vw_LoadAndUnloadRequests> getLoadAndUnloadRequests(int MSID)
{
List <vw_LoadAndUnloadRequests> requestsData = new List <vw_LoadAndUnloadRequests>();
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
string sqlCmdText = "SELECT RequestID, RequestTypeID, TimeRequestStart, TimeRequestEnd FROM dbo.vw_LoadAndUnloadRequests WHERE MSID = @msid";
SqlDataReader reader = SqlHelper.ExecuteReader(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", MSID));
while (reader.Read())
{
int RequestID = reader.GetValueOrDefault <int>("RequestID");
int RequestTypeID = reader.GetValueOrDefault <int>("RequestTypeID");
DateTime?TimeRequestStart = reader.GetValueOrDefault <DateTime>("TimeRequestStart");
DateTime?TimeRequestEnd = reader.GetValueOrDefault <DateTime>("TimeRequestEnd");
TimeRequestStart = TimeRequestStart == default(DateTime) ? null : TimeRequestStart;
TimeRequestEnd = TimeRequestEnd == default(DateTime) ? null : TimeRequestEnd;
vw_LoadAndUnloadRequests request = new vw_LoadAndUnloadRequests(RequestID, MSID, RequestTypeID, TimeRequestStart, TimeRequestEnd);
requestsData.Add(request);
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in loaderMobile getLoadAndUnloadRequests(). Details: " + excep.ToString();
ErrorLogging.LogErrorAndRedirect(2, strErr);
}
catch (Exception ex)
{
string strErr = " Exception Error in loaderMobile getLoadAndUnloadRequests(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(requestsData);
}
private void createAlertRunsEntry(int alertID, DateTime timestamp, int?MSID, string AlertRunsObjectID)
{
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
using (var scope = new TransactionScope())
{
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.StoredProcedure, "sp_truckschedapp_LogAlertRuns", new SqlParameter("@pALERTID", alertID),
new SqlParameter("@pTIME", timestamp),
new SqlParameter("@pMSID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSID)),
new SqlParameter("@pOBJID", TransportHelperFunctions.convertStringEmptyToDBNULL(AlertRunsObjectID)));
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in alertsProcessing createAlertRunsEntry(). Details: " + excep.ToString();
ErrorLogging.LogErrorAndRedirect(2, strErr);
}
catch (Exception ex)
{
string strErr = " Exception Error in alertsProcessing createAlertRunsEntry(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
finally
{
}
}
public static DataSet GetTruckInfoForRejectCustomMessage(int MSID)
{
DataSet TruckData = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT MS.PONumber, MS.PONumber_ZXPOutbound, MS.CustomerID, MS.TrailerNumber, MS.RejectionComment, U.FirstName, U.LastName, " +
"ISNULL(ProdDet.PDCount, 0) AS ProdCount, ProdDet.topProdID, PCMS.ProductName_CMS " +
"FROM dbo.MainSchedule AS MS " +
"INNER JOIN dbo.MainScheduleEvents MSE ON MS.MSID = MSE.MSID " +
"INNER JOIN dbo.Users U ON U.UserID = MSE.UserId " +
"LEFT JOIN (SELECT MSID, COUNT(PODetailsID) AS PDCount, " +
"(SELECT TOP 1 PD_A.ProductID_CMS " +
"FROM dbo.PODetails PD_A " +
"INNER JOIN dbo.ProductsCMS PCMS_A ON PD_A.ProductID_CMS = PCMS_A.ProductID_CMS " +
"WHERE PD_A.MSID = PD.MSID " +
") AS topProdID " +
"FROM dbo.PODetails PD " +
"GROUP BY MSID " +
") ProdDet ON ProdDet.MSID = MS.MSID " +
"LEFT JOIN dbo.ProductsCMS PCMS ON PCMS.ProductID_CMS = ProdDet.topProdID " +
"WHERE MS.MSID = @MSID AND MSE.EventTypeID = 2037";
TruckData = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", MSID));
}
catch (Exception ex)
{
string strErr = " Exception Error in RejectTruck GetTruckInfoForRejectCustomMessage(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(TruckData);
}
public static Object GetStatusOptions()
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT StatusID, StatusText FROM dbo.Status";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
}
catch (Exception ex)
{
string strErr = " Exception Error in WaitAndDockOverview GetStatusOptions(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(data);
}
public static Object getTimeDiff()
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT * FROM " +
"(SELECT MS.MSID, " +
"(SELECT TOP 1 MSE.Timestamp FROM dbo.MainScheduleEvents MSE WHERE EventTypeID = 1025 AND MSE.MSID = MS.MSID and isHidden = 'false' ORDER BY TimeStamp DESC) As StartTime, " +
"(SELECT TOP 1 MSE.Timestamp FROM dbo.MainScheduleEvents MSE WHERE EventTypeID = 1026 AND MSE.MSID = MS.MSID and isHidden = 'false' ORDER BY TimeStamp DESC) As EndTime " +
"FROM dbo.MainSchedule AS MS " +
"WHERE MS.isOpenInCMS = 'true' " +
") AS DemurrageTime " +
"WHERE StartTime IS NOT NULL AND EndTime IS NULL";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
}
catch (Exception ex)
{
string strErr = " Exception Error in WaitAndDockOverview getTimeDiff(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(data);
}
protected int getTimesAlertRan(int alertID, int?MSID, string AlertRunsObjectID)
{
int runNum = 0;
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
using (var scope = new TransactionScope())
{
string sqlCmdText;
sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT TimesRan FROM dbo.AlertRuns WHERE AlertID = @ALERTID";
if (MSID == null && string.IsNullOrEmpty(AlertRunsObjectID))
{
runNum = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID)));
}
else if (MSID != null && string.IsNullOrEmpty(AlertRunsObjectID))
{
sqlCmdText = sqlCmdText + " AND MSID = @MSID";
runNum = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSID)),
new SqlParameter("@ALERTID", alertID)));
}
else if (!string.IsNullOrEmpty(AlertRunsObjectID) && MSID == null)
{
sqlCmdText = sqlCmdText + " AND ObjectID = @OBJID";
runNum = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID),
new SqlParameter("@OBJID", TransportHelperFunctions.convertStringEmptyToDBNULL(AlertRunsObjectID))));
}
else if (!string.IsNullOrEmpty(AlertRunsObjectID) && MSID != null)
{
sqlCmdText = sqlCmdText + " AND ObjectID = @OBJID AND MSID = @MSID";
runNum = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID),
new SqlParameter("@MSID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSID)),
new SqlParameter("@OBJID", TransportHelperFunctions.convertStringEmptyToDBNULL(AlertRunsObjectID))));
}
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in alertsProcessing getTimesAlertRan(). Details: " + excep.ToString();
ErrorLogging.LogErrorAndRedirect(2, strErr);
}
catch (Exception ex)
{
string strErr = " Exception Error in alertsProcessing getTimesAlertRan(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
finally
{
}
return(runNum);
}
public static Object getTrailerInYardData()
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT MS.MSID, MS.PONumber, MS.TrailerNumber, " +
"(SELECT TOP 1 S.StatusText FROM dbo.Status AS S WHERE S.StatusID = MS.StatusID) AS Status, MS.isDropTrailer, " +
"(SELECT TOP 1 MSE.TimeStamp FROM dbo.MainScheduleEvents AS MSE WHERE MSE.EventTypeID = 3070 AND MS.MSID = MSE.MSID AND isHidden = 'false') AS DroppedTime, MS.isEmpty, MS.YardComment, " +
"(SELECT TOP 1 MSE.TimeStamp FROM dbo.MainScheduleEvents AS MSE WHERE MSE.EventTypeID = 3074 AND MS.MSID = MSE.MSID AND isHidden = 'false') AS EmptyTime, MS.isOpenInCMS, " +
"DATEDIFF(minute, (SELECT TOP 1 MSE.Timestamp FROM dbo.MainScheduleEvents MSE WHERE EventTypeID = 1025 AND MSE.MSID = MS.MSID and isHidden = 'false' ORDER BY TimeStamp DESC), GETDATE() ) AS DemurrageTime, " +
"ISNULL(ProdDet.PDCount, 0) AS ProdCount, ProdDet.topProdID, PCMS.ProductName_CMS " +
"FROM dbo.MainSchedule AS MS " +
"LEFT JOIN (SELECT MSID, COUNT(PODetailsID) AS PDCount, " +
"(SELECT TOP 1 PD_A.ProductID_CMS " +
"FROM dbo.PODetails PD_A " +
"INNER JOIN dbo.ProductsCMS PCMS_A ON PD_A.ProductID_CMS = PCMS_A.ProductID_CMS " +
"WHERE PD_A.MSID = PD.MSID " +
") AS topProdID " +
"FROM dbo.PODetails PD " +
"GROUP BY MSID " +
") ProdDet ON ProdDet.MSID = MS.MSID " +
"LEFT JOIN dbo.ProductsCMS PCMS ON PCMS.ProductID_CMS = ProdDet.topProdID " +
"LEFT JOIN dbo.TrailersInYard as TiY ON Tiy.MSID = MS.MSID " +
"WHERE (MS.LocationShort = 'YARD' OR (MS.LocationShort = 'NOS' AND TiY.MSID is not NULL)) AND MS.isRejected = 'false' AND MS.isHidden = 'false' " +
"UNION " +
"SELECT -1 AS MSID, -1 AS PONumber, TiY.TrailerNumber, " +
"'Waiting' AS Status, 'false' AS isDropTrailer, " +
"NULL AS DroppedTime, 'true' AS isEmpty, NULL AS YardComment, " +
"NULL AS EmptyTime, -1 AS isOpenInCMS, NULL AS DemurrageTime, " +
"0 AS ProdCount, NULL AS topProdID, NULL AS ProductName_CMS " +
"FROM dbo.TrailersInYard as TiY " +
"WHERE tiy.MSID is Null";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
}
catch (Exception ex)
{
string strErr = " Exception Error in WaitAndDockOverview getTrailerInYardData(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(data);
}
public static List <object> GetTankDataByProduct(string CmsProduct)
{
List <object> tankData = new List <object>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT ISNULL(SUM(TankCapacity),0) AS ttlTankCapacity " +
",ISNULL(SUM(CurrentTankVolume),0) AS ttlCurrentTankVolume " +
",COUNT(T.TankID) AS numberOfTanksWithProducts " +
",ISNULL(MAX(TankProdData.ProdCount),0) AS maxProdCount " +
"FROM dbo.Tanks T " +
"INNER JOIN dbo.TankProducts TP ON TP.TankID = T.TankID " +
"INNER JOIN (SELECT SUB1_T.TankID, COUNT(SUB1_TP.ProductID_CMS) AS ProdCount " +
"FROM dbo.Tanks SUB1_T " +
"INNER JOIN dbo.TankProducts SUB1_TP ON SUB1_T.TankID = SUB1_TP.TankID " +
"WHERE SUB1_T.isDisabled = 0 AND SUB1_TP.isDisabled = 0 " +
"GROUP BY SUB1_T.TankID " +
") AS TankProdData ON TankProdData.TankID = TP.TankID " +
"WHERE TP.ProductID_CMS = @CMSPROD AND T.isDisabled = 0 AND TP.isDisabled = 0";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@CMSPROD", CmsProduct));
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
tankData.AddRange(row.ItemArray);
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in dataProcessingAndCleanup GetTankDataByProduct(). 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 GetTankDataByProduct(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
finally
{
}
return(tankData);
}
private void updateAlertRunsEntry(int alertID, DateTime timestamp, int newRunNum, int?MSID, string AlertRunsObjectID)
{
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
using (var scope = new TransactionScope())
{
string sqlCmdText;
sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "UPDATE dbo.AlertRuns SET TimeStampAlertLastRan = @TIME, TimesRan = @RUN WHERE AlertID = @ALERTID";
if (MSID == null && string.IsNullOrEmpty(AlertRunsObjectID))
{
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID));
}
else if (MSID != null && string.IsNullOrEmpty(AlertRunsObjectID))
{
sqlCmdText = sqlCmdText + " AND MSID = @MSID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSID)),
new SqlParameter("@ALERTID", alertID));
}
else if (!string.IsNullOrEmpty(AlertRunsObjectID) && MSID == null)
{
sqlCmdText = sqlCmdText + " AND ObjectID = @OBJID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID),
new SqlParameter("@OBJID", TransportHelperFunctions.convertStringEmptyToDBNULL(AlertRunsObjectID)));
}
else if (!string.IsNullOrEmpty(AlertRunsObjectID) && MSID != null)
{
sqlCmdText = sqlCmdText + " AND ObjectID = @OBJID AND MSID = @MSID";
SqlHelper.ExecuteNonQuery(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@ALERTID", alertID),
new SqlParameter("@MSID", TransportHelperFunctions.convertStringEmptyToDBNULL(MSID)),
new SqlParameter("@OBJID", TransportHelperFunctions.convertStringEmptyToDBNULL(AlertRunsObjectID)));
}
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in alertsProcessing updateAlertRunsEntry(). Details: " + excep.ToString();
ErrorLogging.LogErrorAndRedirect(2, strErr);
}
catch (Exception ex)
{
string strErr = " Exception Error in alertsProcessing updateAlertRunsEntry(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
finally
{
}
}
public static Sample getSampleInformationForMSID(int MSID)
{
Sample sampleInfo = new Sample();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = string.Concat("SELECT MSID ,PODetailsID ,PONumber ,ProductID_CMS ,FileID ,Filepath ,FilenameOld ,SampleID, LotusID ,TimeSampleTaken ,TimeSampleSent ",
",TimeSampleReceived ,didLabNotReceived ,Comments ,FilenameNew ,TestApproved ,TrailerNumber ,FirstName ,LastName ,bypassCOFAComment ,SpecificGravity ",
",isOpenInCMS ,isRejected ,ProductName_CMS ",
"FROM dbo.vw_SampleGridData ",
"WHERE MSID = @pMSID ",
"ORDER BY TestApproved, didLabNotReceived, TimeSampleReceived");
SqlDataReader reader = SqlHelper.ExecuteReader(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@pMSID", MSID));
while (reader.Read())
{
sampleInfo.MSID = reader.GetValueOrDefault <int>("MSID");
sampleInfo.PODetailsID = reader.GetValueOrDefault <int>("PODetailsID");
sampleInfo.PONumber = reader.GetValueOrDefault <int>("PONumber");
sampleInfo.ProductID_CMS = reader.GetValueOrDefault <string>("ProductID_CMS");
sampleInfo.FileID = reader.GetValueOrDefault <int>("FileID");
sampleInfo.Filepath = reader.GetValueOrDefault <string>("Filepath");
sampleInfo.FilenameOld = reader.GetValueOrDefault <string>("FilenameOld");
sampleInfo.SampleID = reader.GetValueOrDefault <int>("SampleID");
sampleInfo.LotusID = reader.GetValueOrDefault <string>("LotusID");
DateTime?tempTime = reader.GetValueOrDefault <DateTime>("TimeSampleTaken");
sampleInfo.TimeSampleTaken = tempTime == default(DateTime)? null: tempTime;
DateTime?tempTime2 = reader.GetValueOrDefault <DateTime>("TimeSampleReceived");
sampleInfo.TimeSampleReceived = tempTime == default(DateTime) ? null : tempTime2;
sampleInfo.didLabNotReceived = reader.GetValueOrDefault <int>("didLabNotReceived");
sampleInfo.Comments = reader.GetValueOrDefault <string>("Comments");
sampleInfo.FilenameNew = reader.GetValueOrDefault <string>("FilenameNew");
sampleInfo.TestApproved = reader.GetValueOrDefault <bool>("TestApproved");
sampleInfo.TrailerNumber = reader.GetValueOrDefault <string>("TrailerNumber");
sampleInfo.FirstName = reader.GetValueOrDefault <string>("FirstName");
sampleInfo.LastName = reader.GetValueOrDefault <string>("LastName");
sampleInfo.bypassCOFAComment = reader.GetValueOrDefault <string>("bypassCOFAComment");
sampleInfo.SpecificGravity = reader.GetValueOrDefault <int>("SpecificGravity");
sampleInfo.isOpenInCMS = reader.GetValueOrDefault <bool>("isOpenInCMS");
sampleInfo.isRejected = reader.GetValueOrDefault <bool>("isRejected");
sampleInfo.ProductName_CMS = reader.GetValueOrDefault <string>("ProductName_CMS");
}
}
catch (Exception ex)
{
string strErr = " Exception Error in loaderMobile getSampleGridData(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(sampleInfo);
}
public void setSqlConnectionStringUsingConfiguration()
{
TruckScheduleConfigurationKeysHelper TSCKHelper = new TruckScheduleConfigurationKeysHelper();
try
{
sql_connStr = TSCKHelper.sql_connStr;
}
catch (Exception ex)
{
string strErr = " Exception Error in SQLDataConnectionHelper getSqlConnectionStringFromConfiguration(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
}
}
public static ICollection <Tuple <string, string> > GetProductInfoRejectCustomMessage(int MSID)
{
DataSet productInfo = new DataSet();
string productName;
string partNum;
ICollection <Tuple <string, string> > listOfProductDetails = new List <Tuple <string, string> >();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT PODetailsID, ProductID_CMS " +
"FROM dbo.PODetails POD " +
"WHERE MSID = @MSID";
productInfo = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@MSID", MSID));
foreach (System.Data.DataRow row in productInfo.Tables[0].Rows)
{
if (productInfo.Tables[0].Rows[0]["ProductID_CMS"].Equals(DBNull.Value))
{
productName = "";
}
else
{
productName = Convert.ToString(productInfo.Tables[0].Rows[0]["ProductID_CMS"]);
}
if (productInfo.Tables[0].Rows[0]["PODetailsID"].Equals(DBNull.Value))
{
partNum = "";
}
else
{
partNum = Convert.ToString(productInfo.Tables[0].Rows[0]["PODetailsID"]);
}
listOfProductDetails.Add(Tuple.Create(partNum, productName));
}
}
catch (Exception ex)
{
string strErr = " Exception Error in RejectTruck GetProductInfoRejectCustomMessage(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(listOfProductDetails);
}
public static bool CheckInspectionValidationSetting()
{
bool ValidationSetting = false;
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
ValidationSetting = InspectionsHelperFunctions.CheckInspectionValidationSetting(sql_connStr);
}
catch (Exception ex)
{
string strErr = " Exception Error in loaderMobile CheckInspectionValidationSetting(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(ValidationSetting);
}
public static List <object> canInspectionBeEdited(int prodDetailID, int MSInspectionListID, int MSInspectionID)
{
List <object> returnObj = new List <object>();
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
ZXPUserData zxpUD = ZXPUserData.GetZXPUserDataFromCookie();
returnObj = InspectionsHelperFunctions.canInspectionBeEdited(prodDetailID, MSInspectionListID, MSInspectionID, sql_connStr, zxpUD);
}
catch (Exception ex)
{
string strErr = " Exception Error in loaderMobile canInspectionBeStarted(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(returnObj);
}
public static Object GetGridData()
{
List <object[]> data = new List <object[]>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
//gets MSID and other data needed
sqlCmdText = "SELECT MS.MSID, MS.PONumber, MS.TrailerNumber, LS.LocationLong, " +
"(SELECT TOP 1 TimeStamp FROM dbo.MainScheduleEvents MSE WHERE (MSE.MSID = MS.MSID) AND (MS.isRejected = 'true') AND (isHidden = 'false') ORDER BY Timestamp DESC) AS TimeRejected, " +
"MS.RejectionComment, MS.isOpenInCMS, " +
"ISNULL(ProdDet.PDCount, 0) AS ProdCount, ProdDet.topProdID, PCMS.ProductName_CMS " +
"FROM dbo.MainSchedule AS MS " +
"INNER JOIN dbo.Locations LS ON LS.LocationShort = MS.LocationShort " +
"LEFT JOIN (SELECT MSID, COUNT(PODetailsID) AS PDCount, " +
"(SELECT TOP 1 PD_A.ProductID_CMS " +
"FROM dbo.PODetails PD_A " +
"INNER JOIN dbo.ProductsCMS PCMS_A ON PD_A.ProductID_CMS = PCMS_A.ProductID_CMS " +
"WHERE PD_A.MSID = PD.MSID " +
") AS topProdID " +
"FROM dbo.PODetails PD " +
"GROUP BY MSID " +
") ProdDet ON ProdDet.MSID = MS.MSID " +
"LEFT JOIN dbo.ProductsCMS PCMS ON PCMS.ProductID_CMS = ProdDet.topProdID " +
"WHERE isHidden = 0 AND LS.LocationShort != 'NOS' " +
"ORDER BY MSID";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
data.Add(row.ItemArray);
}
}
catch (Exception ex)
{
string strErr = " Exception Error in RejectTruck GetGridData(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
return(data);
}
private DataSet getDemurrageAlerts()
{
string sqlGetAlerts = "SELECT * FROM dbo.vw_Alerts_isNearDemurrageTimeLimit";
DataSet dsDemurrageAlerts;
try {
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
dsDemurrageAlerts = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlGetAlerts);
}
catch (SqlException excep)
{
string strErr = " SQLException Error in alertsProcessing getAndPopulateDemurrageAlerts(). Details: " + excep.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
throw excep;
}
return(dsDemurrageAlerts);
}
private bool loginForAlertProcessing(string userName, string password)
{
ZXPUserData zxpUD = new ZXPUserData();
int rowCount = 0;
bool isValidUser = false;
try
{
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
using (var scope = new TransactionScope())
{
string sqlCmdText;
sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT COUNT (*) FROM dbo.Users WHERE [Password] = @UPASS AND UserName = @UNAME AND isDisabled = 0";
rowCount = Convert.ToInt32(SqlHelper.ExecuteScalar(sql_connStr, CommandType.Text, sqlCmdText, new SqlParameter("@UNAME", userName), new SqlParameter("@UPASS", DataTransformer.PasswordHash(password))));
if (rowCount > 0)
{
isValidUser = true;
}
else
{
isValidUser = false;
throw new Exception("Invalid login.");
}
scope.Complete();
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in alertsProcessing loginForAlertProcessing(). Details: " + excep.ToString();
ErrorLogging.LogErrorAndRedirect(2, strErr);
}
catch (Exception ex)
{
string strErr = " Exception Error in alertsProcessing loginForAlertProcessing(). Details: " + ex.ToString();
ErrorLogging.LogErrorAndRedirect(1, strErr);
}
finally
{
}
return(isValidUser);
}
public static List <int> getTrucksWithPOStillOpenInCMS()
{
List <int> truckPO = new List <int>();
DataSet dataSet = new DataSet();
try
{
string sqlCmdText;
string sql_connStr = new TruckScheduleConfigurationKeysHelper().sql_connStr;
sqlCmdText = "SELECT PONumber FROM dbo.MainSchedule " +
"WHERE (isHidden = 0 AND LocationShort = 'NOS' " +
"AND StatusID = 10 AND isOpenInCMS = 1) OR (isHidden = 0 AND LocationShort = 'NOS' " +
"AND TimeDeparted IS NOT NULL AND isOpenInCMS = 1)" +
"ORDER BY PONumber";
dataSet = SqlHelper.ExecuteDataset(sql_connStr, CommandType.Text, sqlCmdText);
//populate return object
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
truckPO.Add(Convert.ToInt32(row.ItemArray[0]));
}
}
catch (SqlException excep)
{
string strErr = " SQLException Error in dataProcessingAndCleanup getTrucksWithPOStillOpenInCMS(). 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 getTrucksWithPOStillOpenInCMS(). Details: " + ex.ToString();
ErrorLogging.WriteEvent(strErr, EventLogEntryType.Error);
System.Web.HttpContext.Current.Session["ErrorNum"] = 1;
ErrorLogging.sendtoErrorPage(1);
throw ex;
}
finally
{
}
return(truckPO);
}
