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); }