/// <summary> /// Date Created: 05/May/2015 /// Created By: Josephine Monteza /// (description) Save Hotel Forecast to Override Room Blocks /// ---------------------------------------- /// </summary> public void ApproveForecastManifest(int iHotelID, //float fRateSGL, Int32 iCurrencySGL, float fTaxPercentSGL, bool bIsTaxInclusiveSGL, //float fRateDBL, Int32 iCurrencyDBL, float fTaxPercentDBL, bool bIsTaxInclusiveDBL, string sUserName, string sDescription, string sFunction, string sFileName, DateTime dDateGMT, DateTime dtDateCreated, DataTable dtRoomToBeAdded) { Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase(); DbCommand dbCommand = null; DbConnection connection = db.CreateConnection(); connection.Open(); DbTransaction trans = connection.BeginTransaction(); try { string strTimeZone = TimeZone.CurrentTimeZone.StandardName.ToString(); dbCommand = db.GetStoredProcCommand("uspHotelForecastApproveWithEmail"); db.AddInParameter(dbCommand, "@pBranchIDInt", DbType.Int32, iHotelID); //db.AddInParameter(dbCommand, "@pRatePerDayMoneySGL", DbType.Decimal, fRateSGL); //db.AddInParameter(dbCommand, "@pCurrencyIDIntSGL", DbType.Int32, iCurrencySGL); //db.AddInParameter(dbCommand, "@pRoomRateTaxPercentageSGL", DbType.Decimal, fTaxPercentSGL); //db.AddInParameter(dbCommand, "@pRoomRateTaxInclusiveSGL", DbType.Boolean, bIsTaxInclusiveSGL); //db.AddInParameter(dbCommand, "@pRatePerDayMoneyDBL", DbType.Decimal, fRateDBL); //db.AddInParameter(dbCommand, "@pCurrencyIDIntDBL", DbType.Int32, iCurrencyDBL); //db.AddInParameter(dbCommand, "@pRoomRateTaxPercentageDBL", DbType.Decimal, fTaxPercentDBL); //db.AddInParameter(dbCommand, "@pRoomRateTaxInclusiveDBL", DbType.Boolean, bIsTaxInclusiveDBL); db.AddInParameter(dbCommand, "@pUserId", DbType.String, sUserName); db.AddInParameter(dbCommand, "@pDescription", DbType.String, sDescription); db.AddInParameter(dbCommand, "@pFunction", DbType.String, sFunction); db.AddInParameter(dbCommand, "@pFileName", DbType.String, sFileName); db.AddInParameter(dbCommand, "@pTimezone", DbType.String, strTimeZone); db.AddInParameter(dbCommand, "@pGMTDATE", DbType.DateTime, dDateGMT); db.AddInParameter(dbCommand, "@pCreateDate", DbType.DateTime, dtDateCreated); SqlParameter param = new SqlParameter("@pTempHotelForecast", dtRoomToBeAdded); param.Direction = ParameterDirection.Input; param.SqlDbType = SqlDbType.Structured; dbCommand.Parameters.Add(param); db.ExecuteNonQuery(dbCommand, trans); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } finally { if (connection != null) { connection.Close(); connection.Dispose(); } if (dbCommand != null) { dbCommand.Dispose(); } if (trans != null) { trans.Dispose(); } if (dtRoomToBeAdded != null) { dtRoomToBeAdded.Dispose(); } } }
/// <summary> /// Author: Josephine Gad /// Date 29/Jan/2015 /// Description: Get Forecast from Micro /// </summary> /// <returns></returns> public List <HotelForecastForApprovalList> GetForecastManifestList(string sBranchName, string sDateFrom, string sDateTo, string sVesselCode, int sPortID, string sUser, string sRole, bool bIsHotelVendorView, Int16 LoadType, bool bShowAll, int StartRow, int MaxRow) { List <HotelForecastForApprovalList> list = new List <HotelForecastForApprovalList>(); List <HotelForecastCurrency> listCurrencySelected = new List <HotelForecastCurrency>(); List <Currency> listCurrency = new List <Currency>(); Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase(); DbCommand dbCommand = null; Int32 maxRows = 0; DataTable dt = null; DataTable dtNoOfDays = null; DataTable dtCurrencySelected = null; DataTable dtCurrency = null; DataTable dtHotelBranch = null; DataSet ds = null; try { dbCommand = db.GetStoredProcCommand("uspHotelForecastGet"); db.AddInParameter(dbCommand, "@pLoadType", DbType.Int16, LoadType); db.AddInParameter(dbCommand, "@pUserId", DbType.String, sUser); db.AddInParameter(dbCommand, "@pBranchName", DbType.String, sBranchName); db.AddInParameter(dbCommand, "@pDateFrom", DbType.DateTime, GlobalCode.Field2DateTime(sDateFrom)); db.AddInParameter(dbCommand, "@pDateTo", DbType.DateTime, GlobalCode.Field2DateTime(sDateTo)); db.AddInParameter(dbCommand, "@pVesselCode", DbType.String, ""); db.AddInParameter(dbCommand, "@pAirportCode", DbType.String, ""); db.AddInParameter(dbCommand, "@pHotelVendorView", DbType.Boolean, bIsHotelVendorView); db.AddInParameter(dbCommand, "@pShowAll", DbType.Boolean, bShowAll); //db.AddInParameter(dbCommand, "@pStartRow", DbType.String, StartRow); //db.AddInParameter(dbCommand, "@pMaxRow", DbType.String, MaxRow); //db.AddInParameter(dbCommand, "@pLoadType", DbType.String, LoadType); ds = db.ExecuteDataSet(dbCommand); dt = ds.Tables[1]; maxRows = Int32.Parse(ds.Tables[0].Rows[0][0].ToString()); list = (from a in dt.AsEnumerable() select new HotelForecastForApprovalList { colBranchIDInt = GlobalCode.Field2Long(a["colBranchIDInt"]), colDate = GlobalCode.Field2DateTime(a["colDate"]), // Confirmed_DBL = GlobalCode.Field2Int(a["Confirmed_DBL"]), // Overflow_DBL = GlobalCode.Field2Int(a["Overflow_DBL"]), // Confirmed_SGL = GlobalCode.Field2Int(a["Confirmed_SGL"]), // Overflow_SGL = GlobalCode.Field2Int(a["Overflow_SGL"]), Forecast_DBL = GlobalCode.Field2Int(a["colForecastDBL"]), Forecast_SGL = GlobalCode.Field2Int(a["colForecastSGL"]), Forecast_DBL_Adj = GlobalCode.Field2Int(a["colForecastDBLAdj"]), Forecast_SGL_Adj = GlobalCode.Field2Int(a["colForecastSGLAdj"]), RoomBlock_DBL = GlobalCode.Field2Int(a["colRoomBlockDBL"]), RoomBlock_SGL = GlobalCode.Field2Int(a["colRoomBlockSGL"]), RoomBlock_DBL_Total = GlobalCode.Field2Int(a["colRoomBlockDBLTotal"]), RoomBlock_SGL_Total = GlobalCode.Field2Int(a["colRoomBlockSGLTotal"]), TMBooked_DBL = GlobalCode.Field2Float(a["colTMBookedDBL"]), TMBooked_SGL = GlobalCode.Field2Float(a["colTMBookedSGL"]), ToBeAdded_DBL = GlobalCode.Field2Int(a["colToAddDBL"]), ToBeAdded_SGL = GlobalCode.Field2Int(a["colToAddSGL"]), IsEnable = GlobalCode.Field2Bool(a["colIsEnableBit"]), Forecast_DBL_Old = GlobalCode.Field2Int(a["colForecastDBLOld"]), Forecast_SGL_Old = GlobalCode.Field2Int(a["colForecastSGLOld"]), ToBeAdded_DBL_Suggested = GlobalCode.Field2Int(a["colToAddDBLSuggested"]), ToBeAdded_SGL_Suggested = GlobalCode.Field2Int(a["colToAddSGLSuggested"]), Remarks = a.Field <string>("colRemarksVarchar"), ApprovedDBL = GlobalCode.Field2Int(a["colApprovedDBL"]), ApprovedSGL = GlobalCode.Field2Int(a["colApprovedSGL"]), ActionDone = a.Field <string>("colActionVarchar"), IsLinkToRequestVisibleDBL = GlobalCode.Field2Bool(a["IsLinkToRequestVisibleDBL"]), IsLinkToRequestVisibleSGL = GlobalCode.Field2Bool(a["IsLinkToRequestVisibleSGL"]), IsNeededHotelVisibleDBL = GlobalCode.Field2Bool(a["IsNeededHotelVisibleDBL"]), IsNeededHotelVisibleSGL = GlobalCode.Field2Bool(a["IsNeededHotelVisibleSGL"]), RoomToDropDBL = GlobalCode.Field2Int(a["colRoomToDropDBL"]), RoomToDropSGL = GlobalCode.Field2Int(a["colRoomToDropSGL"]), RoomToDropColorDBL = a.Field <string>("RoomToDropColorDBL"), RoomToDropColorSGL = a.Field <string>("RoomToDropColorSGL"), RatePerDayMoneySGL = GlobalCode.Field2Float(a["colRatePerDayMoneySGL"]), RatePerDayMoneyDBL = GlobalCode.Field2Float(a["colRatePerDayMoneyDBL"]), CurrencyID = GlobalCode.Field2Int(a["colCurrencyIDInt"]), RoomRateTaxPercentage = GlobalCode.Field2Float(a["colRoomRateTaxPercentage"]), RoomRateIsTaxInclusive = GlobalCode.Field2Bool(a["colRoomRateIsTaxInclusive"]), IsRoomToDropVisibleToVendorBDL = GlobalCode.Field2Bool(a["IsRoomToDropVisibleToVendorBDL"]), IsRoomToDropVisibleToVendorSGL = GlobalCode.Field2Bool(a["IsRoomToDropVisibleToVendorSGL"]), IsRCCLApprovalVisible = GlobalCode.Field2Bool(a["IsRCCLApprovalVisible"]), MessageToVendor = GlobalCode.Field2String(a["MessageToVendor"]), CurrencyName = GlobalCode.Field2String(a["CurrencyName"]), }).ToList(); HttpContext.Current.Session["HotelForecastMicroApproval_Count"] = GlobalCode.Field2Int(maxRows); dtCurrencySelected = ds.Tables[2]; listCurrencySelected = (from a in dtCurrencySelected.AsEnumerable() select new HotelForecastCurrency { CurrencyID = GlobalCode.Field2Int(a["CurrencyID"]), CurrencyName = a.Field <string>("CurrencyName"), RateMoney = GlobalCode.Field2Decimal(a["RateMoney"]), IsTaxInclusive = GlobalCode.Field2Bool(a["IsTaxInclusive"]), Tax = GlobalCode.Field2Decimal(a["TaxPercentage"]), RoomTypeID = GlobalCode.Field2TinyInt(a["colRoomTypeIDInt"]), }).ToList(); HttpContext.Current.Session["HotelForecastMicroApproval_CurrencySelected"] = listCurrencySelected; dtHotelBranch = ds.Tables[3]; List <ContractHotel> listBranch = new List <ContractHotel>(); listBranch = (from a in dtHotelBranch.AsEnumerable() select new ContractHotel { contractID = GlobalCode.Field2Long(a["colContractIdInt"]), contractStatus = GlobalCode.Field2String(a["colContractStatusVarchar"]), contractStartDate = a.Field <DateTime?>("colContractDateStartedDate"), contractEndDate = a.Field <DateTime?>("colContractDateEndDate"), }).ToList(); HttpContext.Current.Session["HotelForecastMicroApproval_ContractHotel"] = listBranch; if (LoadType == 0) { dtNoOfDays = ds.Tables[4]; TMSettings.NoOfDaysForecast = GlobalCode.Field2TinyInt(dtNoOfDays.Rows[0]["colNoOfDays_Forecast"]); TMSettings.NoOfDaysForecastVendor = GlobalCode.Field2TinyInt(dtNoOfDays.Rows[0]["colNoOfDays_Forecast_Vendor"]); //dtCurrency = ds.Tables[5]; //listCurrency = (from a in dtCurrency.AsEnumerable() // select new Currency // { // CurrencyID = GlobalCode.Field2Int(a["colCurrencyIDInt"]), // CurrencyName = a.Field<string>("colCurrencyNameVarchar"), // }).ToList(); //HttpContext.Current.Session["HotelForecastMicroApproval_Currency"] = listCurrency; } return(list); } catch (Exception ex) { throw ex; } finally { if (dbCommand != null) { dbCommand.Dispose(); } if (ds != null) { ds.Dispose(); } if (dt != null) { dt.Dispose(); } if (dtCurrency != null) { dtCurrency.Dispose(); } if (dtCurrencySelected != null) { dtCurrencySelected.Dispose(); } if (dtNoOfDays != null) { dtNoOfDays.Dispose(); } if (dtHotelBranch != null) { dtHotelBranch.Dispose(); } if (list != null) { list = null; } } }
/// <summary> /// Date Created: 04/Feb/2015 /// Created By: Josephine Monteza /// (description) Save Hotel Forecast /// ---------------------------------------- /// </summary> public void UpdateForecastManifest(int iHotelID, string sUserName, string sDescription, string sFunction, string sFileName, DateTime dDateGMT, DateTime dtDateCreated, DataTable dtRoomToBeAdded) { Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase(); DbCommand dbCommand = null; DbConnection connection = db.CreateConnection(); connection.Open(); DbTransaction trans = connection.BeginTransaction(); try { string strTimeZone = TimeZone.CurrentTimeZone.StandardName.ToString(); dbCommand = db.GetStoredProcCommand("uspHotelForecastUpdate"); db.AddInParameter(dbCommand, "@pBranchIDInt", DbType.Int32, iHotelID); db.AddInParameter(dbCommand, "@pUserId", DbType.String, sUserName); db.AddInParameter(dbCommand, "@pDescription", DbType.String, sDescription); db.AddInParameter(dbCommand, "@pFunction", DbType.String, sFunction); db.AddInParameter(dbCommand, "@pFileName", DbType.String, sFileName); db.AddInParameter(dbCommand, "@pTimezone", DbType.String, strTimeZone); db.AddInParameter(dbCommand, "@pGMTDATE", DbType.DateTime, dDateGMT); db.AddInParameter(dbCommand, "@pCreateDate", DbType.DateTime, dtDateCreated); SqlParameter param = new SqlParameter("@pTempHotelForecast", dtRoomToBeAdded); param.Direction = ParameterDirection.Input; param.SqlDbType = SqlDbType.Structured; dbCommand.Parameters.Add(param); db.ExecuteNonQuery(dbCommand, trans); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } finally { if (connection != null) { connection.Close(); connection.Dispose(); } if (dbCommand != null) { dbCommand.Dispose(); } if (trans != null) { trans.Dispose(); } if (dtRoomToBeAdded != null) { dtRoomToBeAdded.Dispose(); } } }
/// <summary> /// Date Created: 07/Oct/2017 /// Created By: Josephine Monteza /// (description) Add/Edit Greeter Vendor Matrix /// ---------------------------------------- /// </summary> public static void GreeterVendorAddEdit(string sUserID, bool bIsToBeAdded, DataTable dtVendor, string sVendorType, String strLogDescription, String strFunction, String strPageName, DateTime DateGMT, String CreatedBy) { Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase() DbCommand dbCommand = null; DbConnection connection = db.CreateConnection(); connection.Open(); DbTransaction trans = connection.BeginTransaction(); try { string strTimeZone = TimeZone.CurrentTimeZone.StandardName.ToString(); dbCommand = db.GetStoredProcCommand("uspGreeterVendorAddEdit"); db.AddInParameter(dbCommand, "@pUserName", DbType.String, sUserID); db.AddInParameter(dbCommand, "@pIsToBeAdded", DbType.Boolean, bIsToBeAdded); db.AddInParameter(dbCommand, "@pVendorType", DbType.String, sVendorType); db.AddInParameter(dbCommand, "@pLogDescriptionVarchar", DbType.String, strLogDescription); db.AddInParameter(dbCommand, "@pFunctionVarchar", DbType.String, strFunction); db.AddInParameter(dbCommand, "@pPageNameVarchar", DbType.String, strPageName); db.AddInParameter(dbCommand, "@pTimeZoneVarchar", DbType.String, strTimeZone); db.AddInParameter(dbCommand, "@pDateCreatedGMT", DbType.DateTime, DateGMT); //db.AddInParameter(dbCommand, "@pDateCreatedDate", DbType.DateTime, CreatedDate); db.AddInParameter(dbCommand, "@pCreatedByVarchar", DbType.String, CreatedBy); SqlParameter param = new SqlParameter("@pTblUserVendor", dtVendor); param.Direction = ParameterDirection.Input; param.SqlDbType = SqlDbType.Structured; dbCommand.Parameters.Add(param); db.ExecuteNonQuery(dbCommand, trans); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } finally { if (connection != null) { connection.Close(); connection.Dispose(); } if (dbCommand != null) { dbCommand.Dispose(); } if (trans != null) { trans.Dispose(); } if (dtVendor != null) { dtVendor.Dispose(); } } }
/// <summary> /// Author: Charlene Remotigue /// Date Created: 09/05/2012 /// Description: Load Region Seaport page /// </summary> /// <param name="RegionId"></param> /// <returns></returns> public static List <RegionGenericClass> LoadRegionPage(int RegionId, string strLogDescription, string strFunction, string PathName, DateTime GMTDate, DateTime DateNow, string UserName) { List <RegionGenericClass> overflow = new List <RegionGenericClass>(); Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase() DbCommand dbCommand = null; DataSet ds = null; DataTable dtRegionSeaport = null; DataTable dtContinent = null; try { string strTimeZone = TimeZone.CurrentTimeZone.StandardName.ToString(); dbCommand = db.GetStoredProcCommand("uspLoadRegionSeaportPage"); db.AddInParameter(dbCommand, "@pRegionIDInt", DbType.Int32, RegionId); db.AddInParameter(dbCommand, "@pDescription", DbType.String, strLogDescription); db.AddInParameter(dbCommand, "@pFunction", DbType.String, strFunction); db.AddInParameter(dbCommand, "@pPathName", DbType.String, PathName); db.AddInParameter(dbCommand, "@pGMTDate", DbType.DateTime, GMTDate); db.AddInParameter(dbCommand, "@pDateNow", DbType.DateTime, DateNow); db.AddInParameter(dbCommand, "@pUserName", DbType.String, UserName); db.AddInParameter(dbCommand, "@pTimeZone", DbType.String, strTimeZone); ds = db.ExecuteDataSet(dbCommand); dtContinent = ds.Tables[0]; dtRegionSeaport = ds.Tables[1]; overflow.Add(new RegionGenericClass() { ContinentList = (from a in dtContinent.AsEnumerable() select new Continent { ContinentID = a.Field <int>("colMapIDInt"), ContinentName = a.Field <string>("colMapNameVarchar"), }).ToList(), RegionSeaportList = (from a in dtRegionSeaport.AsEnumerable() select new RegionSeaport { RegionSeaportID = a.Field <Int64>("RegionSeaportID"), RegionID = a.Field <int>("RegionID"), CountryID = a.Field <int>("CountryID"), SeaportID = a.Field <int>("SeaportID"), SeaportName = a.Field <string>("SeaportName"), }).ToList() }); return(overflow); } catch (Exception ex) { throw ex; } finally { if (dbCommand != null) { dbCommand.Dispose(); } if (dtContinent != null) { dtContinent.Dispose(); } if (dtRegionSeaport != null) { dtRegionSeaport.Dispose(); } if (ds != null) { ds.Dispose(); } } }
/// <summary> /// Author: Muhallidin G Wali /// Date Created: 01/oct/2014 /// Description: Get Excption Data to load in the page /// --------------------------------------------------------------- /// </summary> /// <returns></returns> public List <ExceptionPageData> GetExceptionPageData(short LoadType, string UserId , DateTime Date, int RegionID, int PortID, int CountryID, string UserRole ) { DataSet ds = new DataSet(); List <ExceptionPageData> ExceptionPageData = new List <ExceptionPageData>(); DbCommand com = null; try { Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase(); com = db.GetStoredProcCommand("upsGetExceptionPageData"); db.AddInParameter(com, "@pLoadType", DbType.Int16, LoadType); db.AddInParameter(com, "@pUserId", DbType.String, UserId); db.AddInParameter(com, "@pDate", DbType.DateTime, Date); db.AddInParameter(com, "@pRegionIDInt", DbType.Int32, RegionID); db.AddInParameter(com, "@pPortIDInt", DbType.Int32, PortID); db.AddInParameter(com, "@pCountryIdInt", DbType.Int32, CountryID); db.AddInParameter(com, "@pUserRolevarchar", DbType.String, UserRole); ds = db.ExecuteDataSet(com); ExceptionPageData.Add(new ExceptionPageData { RegionList = (from a in ds.Tables[0].AsEnumerable() select new RegionList { RegionId = GlobalCode.Field2Int(a["colRegionIDInt"]), RegionName = GlobalCode.Field2String(a["colRegionNameVarchar"]) }).ToList(), PortList = (from a in ds.Tables[1].AsEnumerable() select new PortList { PortId = GlobalCode.Field2Int(a["PORTID"]), PortName = GlobalCode.Field2String(a["PORT"]) }).ToList(), Hotels = (from a in ds.Tables[2].AsEnumerable() select new Hotels { VendorId = GlobalCode.Field2Int(a["VendorId"]), BranchId = GlobalCode.Field2Int(a["BranchId"]), BranchName = GlobalCode.Field2String(a["BranchName"]), CountryId = GlobalCode.Field2Int(a["CountryId"]), CityId = GlobalCode.Field2Int(a["CityId"]), isAccredited = GlobalCode.Field2Bool(a["isAccredited"]), withEvent = GlobalCode.Field2Bool(a["withEvent"]), withContract = GlobalCode.Field2Bool(a["withContract"]), ContractId = GlobalCode.Field2Int(a["ContractId"]), colDate = GlobalCode.Field2DateTime(a["colDate"]) }).ToList(), ExceptionBooking = (from a in ds.Tables[3].AsEnumerable() select new ExceptionBooking { ExceptionIdBigInt = GlobalCode.Field2Int(a["ExceptionIdBigInt"]), IdBigint = GlobalCode.Field2Int(a["IDBigint"]), SeqNo = GlobalCode.Field2Int(a["SeqNo"]), TravelReqId = GlobalCode.Field2Int(a["TravelReqId"]), E1TravelReqId = GlobalCode.Field2Int(a["E1TravelReqId"]), SeafarerId = GlobalCode.Field2Int(a["SeafarerId"]), SeafarerName = GlobalCode.Field2String(a["SeafarerName"]), PortId = GlobalCode.Field2Int(a["PortId"]), PortName = GlobalCode.Field2String(a["PortName"]), VesselName = GlobalCode.Field2String(a["VesselName"]), SFStatus = GlobalCode.Field2String(a["SFStatus"]), OnOffDate = GlobalCode.Field2DateTime(a["OnOffDate"]), ArrivalDepartureDatetime = GlobalCode.Field2DateTime(a["ArrivalDepartureDatetime"]), Carrier = GlobalCode.Field2String(a["Carrier"]), FlightNo = GlobalCode.Field2String(a["FlightNo"]), FromCity = GlobalCode.Field2String(a["FromCity"]), ToCity = GlobalCode.Field2String(a["ToCity"]), RankName = GlobalCode.Field2String(a["RankName"]), Stripes = GlobalCode.Field2Decimal(a["Stripes"]), RecordLocator = GlobalCode.Field2String(a["RecordLocator"]), Gender = GlobalCode.Field2String(a["Gender"]), Nationality = GlobalCode.Field2String(a["Nationality"]), RoomTypeId = GlobalCode.Field2Int(a["RoomTypeId"]), RoomType = GlobalCode.Field2String(a["RoomType"]), ReasonCode = GlobalCode.Field2String(a["ReasonCode"]), ExceptionRemarks = GlobalCode.Field2String(a["ExceptionRemarks"]), Invalid = GlobalCode.Field2Bool(a["Invalid"]), BookingRemarks = GlobalCode.Field2String(a["BookingRemarks"]), HotelCity = GlobalCode.Field2String(a["HotelCity"]), IsByPort = GlobalCode.Field2String(a["IsPort"]), //Comments = GlobalCode.Field2String(a["VendorId"]), //RemovedBy = GlobalCode.Field2String(a["VendorId"]), //Birthday = GlobalCode.Field2DateTime(a["VendorId"]), }).ToList() }); return(ExceptionPageData); } catch (Exception ex) { throw ex; } finally { if (com != null) { com.Dispose(); } if (ds != null) { ds.Dispose(); } } }
/// <summary> /// Author: Josephine Monteza /// Date Created: 07/Oct/2017 /// Descrption: Get Vendor list of Greeter /// ============================================================= /// </summary> public static void GreeterVendorGet(string sLoginUser, string sUserID, string sVendorToFind, bool bIsToBeAdded, string sVendorType, int iStartRow, int iMaxRow, Int16 iLoadType, string sOrderBy) { Int32 iCountVehicleToAdd = 0; Int32 iCountVehicleAdded = 0; Int32 iCountHotelToAdd = 0; Int32 iCountHotelAdded = 0; Int32 iCountServiceProviderToAdd = 0; Int32 iCountServiceProviderAdded = 0; List <VehicleVendorDTO> listVehicleVendorToAdd = new List <VehicleVendorDTO>(); List <VehicleVendorDTO> listVehicleVendorAdded = new List <VehicleVendorDTO>(); List <HotelDTO> listHotelVendorToAdd = new List <HotelDTO>(); List <HotelDTO> listHotelVendorAdded = new List <HotelDTO>(); List <PortAgentDTO> listServiceProviderToAdd = new List <PortAgentDTO>(); List <PortAgentDTO> listServiceProviderAdded = new List <PortAgentDTO>(); Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase() DbCommand dbCommand = null; DataTable dtVehicleVendorToAdd = null; DataTable dtVehicleVendorAdded = null; DataTable dtHotelVendorToAdd = null; DataTable dtHotelVendorAdded = null; DataTable dtServiceProviderToAdd = null; DataTable dtServiceProviderAdded = null; DataSet ds = null; try { dbCommand = db.GetStoredProcCommand("uspGreeterVendorGet"); db.AddInParameter(dbCommand, "@pLoginName", DbType.String, sLoginUser); db.AddInParameter(dbCommand, "@pUserName", DbType.String, sUserID); db.AddInParameter(dbCommand, "@pVendorToFind", DbType.String, sVendorToFind); db.AddInParameter(dbCommand, "@pIsToBeAdded", DbType.Boolean, bIsToBeAdded); db.AddInParameter(dbCommand, "@pVendorType", DbType.String, sVendorType); db.AddInParameter(dbCommand, "@pStartRow", DbType.Int32, iStartRow); db.AddInParameter(dbCommand, "@pMaxRow", DbType.Int32, iMaxRow); db.AddInParameter(dbCommand, "@pLoadType", DbType.Int16, iLoadType); db.AddInParameter(dbCommand, "@pOrderby", DbType.String, sOrderBy); ds = db.ExecuteDataSet(dbCommand); //Load All if (iLoadType == 0) { if (ds != null) { dtVehicleVendorToAdd = ds.Tables[0]; iCountVehicleToAdd = GlobalCode.Field2Int(ds.Tables[1].Rows[0][0]); dtVehicleVendorAdded = ds.Tables[2]; iCountVehicleAdded = GlobalCode.Field2Int(ds.Tables[3].Rows[0][0]); } } else { //Load Vehicle Vendor list if (sVendorType == "Vehicle") { if (bIsToBeAdded) { dtVehicleVendorToAdd = ds.Tables[0]; iCountVehicleToAdd = GlobalCode.Field2Int(ds.Tables[1].Rows[0][0]); } else { dtVehicleVendorAdded = ds.Tables[0]; iCountVehicleAdded = GlobalCode.Field2Int(ds.Tables[1].Rows[0][0]); } } } //Vehicle Vendor if (dtVehicleVendorToAdd != null) { listVehicleVendorToAdd = (from a in dtVehicleVendorToAdd.AsEnumerable() select new VehicleVendorDTO { VehicleID = GlobalCode.Field2Int(a["VendorID"]).ToString(), VehicleName = a.Field <string>("VendorName") }).ToList(); HttpContext.Current.Session["User_VehicleVendorCountToAdd"] = iCountVehicleToAdd; HttpContext.Current.Session["User_VehicleVendorToAdd"] = listVehicleVendorToAdd; } if (dtVehicleVendorAdded != null) { listVehicleVendorAdded = (from a in dtVehicleVendorAdded.AsEnumerable() select new VehicleVendorDTO { VehicleID = GlobalCode.Field2Int(a["VendorID"]).ToString(), VehicleName = a.Field <string>("VendorName") }).ToList(); HttpContext.Current.Session["User_VehicleVendorCountAdded"] = iCountVehicleAdded; HttpContext.Current.Session["User_VehicleVendorAdded"] = listVehicleVendorAdded; } //Hotel Vendor if (dtHotelVendorToAdd != null) { listHotelVendorToAdd = (from a in dtHotelVendorToAdd.AsEnumerable() select new HotelDTO { HotelIDString = GlobalCode.Field2Int(a["VendorID"]).ToString(), HotelNameString = a.Field <string>("VendorName") }).ToList(); HttpContext.Current.Session["User_HotelVendorCountToAdd"] = iCountHotelToAdd; HttpContext.Current.Session["User_HotelVendorToAdd"] = listHotelVendorToAdd; } if (dtHotelVendorAdded != null) { listHotelVendorAdded = (from a in dtHotelVendorAdded.AsEnumerable() select new HotelDTO { HotelIDString = GlobalCode.Field2Int(a["VendorID"]).ToString(), HotelNameString = a.Field <string>("VendorName") }).ToList(); HttpContext.Current.Session["User_HotelVendorCountAdded"] = iCountHotelAdded; HttpContext.Current.Session["User_HotelVendorAdded"] = listHotelVendorAdded; } // Service Provider if (dtServiceProviderToAdd != null) { listServiceProviderToAdd = (from a in dtServiceProviderToAdd.AsEnumerable() select new PortAgentDTO { PortAgentID = GlobalCode.Field2Int(a["VendorID"]).ToString(), PortAgentName = a.Field <string>("VendorName") }).ToList(); HttpContext.Current.Session["User_ServiceProviderCountToAdd"] = iCountServiceProviderToAdd; HttpContext.Current.Session["User_ServiceProviderToAdd"] = listServiceProviderToAdd; } if (dtServiceProviderAdded != null) { listServiceProviderAdded = (from a in dtServiceProviderAdded.AsEnumerable() select new PortAgentDTO { PortAgentID = GlobalCode.Field2Int(a["VendorID"]).ToString(), PortAgentName = a.Field <string>("VendorName") }).ToList(); HttpContext.Current.Session["User_ServiceProviderCountAdded"] = iCountServiceProviderAdded; HttpContext.Current.Session["User_ServiceProviderAdded"] = listServiceProviderAdded; } } catch (Exception ex) { throw ex; } finally { if (dtVehicleVendorToAdd != null) { dtVehicleVendorToAdd.Dispose(); } if (dtVehicleVendorAdded != null) { dtVehicleVendorAdded.Dispose(); } if (dtHotelVendorToAdd != null) { dtHotelVendorToAdd.Dispose(); } if (dtHotelVendorAdded != null) { dtHotelVendorAdded.Dispose(); } if (dtServiceProviderToAdd != null) { dtServiceProviderToAdd.Dispose(); } if (dtServiceProviderAdded != null) { dtServiceProviderAdded.Dispose(); } if (ds != null) { ds.Dispose(); } if (dbCommand != null) { dbCommand.Dispose(); } } }
/// <summary> /// Author: Josephine Gad /// Date Modified: Apr-02-2014 /// Description: Get exception list from 0 to 6 days /// --------------------------------------------------------------- /// Modified By: Josephine Monteza /// Modified Date: Apr-021-2016 /// Description: Added colNoOfDays_CrewAssist /// Added sRole and iLoadType parameters /// --------------------------------------------------------------- /// <returns></returns> public List <HotelTransactionExceptionGenericClass> LoadHotelExceptionPageDays(DateTime dDate, string UserId, int RegionID, int PortID, string sRole, Int16 iLoadType) { List <HotelTransactionExceptionGenericClass> overflow = new List <HotelTransactionExceptionGenericClass>(); Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase(); DbCommand dbCommand = null; DataSet ds = null; DataTable dtException = null; DataTable dtHotels = null; DataTable dtNoOfDays = null; try { dbCommand = db.GetStoredProcCommand("uspGetHotelExceptionPageDays"); db.AddInParameter(dbCommand, "@pDate", DbType.Date, dDate); db.AddInParameter(dbCommand, "@pUserId", DbType.String, UserId); db.AddInParameter(dbCommand, "@pRegionIDInt", DbType.Int32, RegionID); db.AddInParameter(dbCommand, "@pPortIDInt", DbType.Int32, PortID); db.AddInParameter(dbCommand, "@pRole", DbType.String, sRole); db.AddInParameter(dbCommand, "@pLoadType", DbType.Int16, iLoadType); ds = db.ExecuteDataSet(dbCommand); dtHotels = ds.Tables[0]; dtException = ds.Tables[1]; overflow.Add(new HotelTransactionExceptionGenericClass() { Hotels = (from a in dtHotels.AsEnumerable() select new Hotels { VendorId = a.Field <int>("VendorId"), BranchId = a.Field <int>("BranchId"), BranchName = a.Field <string>("BranchName"), CountryId = a.Field <int>("CountryId"), CityId = a.Field <int>("CityId"), isAccredited = a.Field <bool>("isAccredited"), withEvent = a.Field <bool>("withEvent"), ContractId = a.Field <int>("ContractId"), withContract = a.Field <bool>("withContract"), colDate = a.Field <DateTime>("colDate"), }).ToList(), ExceptionBooking = (from a in dtException.AsEnumerable() select new ExceptionBooking { ExceptionIdBigInt = GlobalCode.Field2Int(a["ExceptionIdBigInt"]), IdBigint = GlobalCode.Field2Int(a["IDBigint"]), SeqNo = GlobalCode.Field2TinyInt(a["SeqNo"]), TravelReqId = a.Field <int?>("TravelReqId"), E1TravelReqId = a.Field <int?>("E1TravelReqId"), SeafarerId = a.Field <int?>("SeafarerId"), SeafarerName = a.Field <string>("SeafarerName"), PortId = a.Field <int?>("PortId"), PortName = a.Field <string>("PortName"), SFStatus = a.Field <string>("SFStatus"), OnOffDate = a.Field <DateTime?>("OnOffDate"), ArrivalDepartureDatetime = a.Field <DateTime?>("ArrivalDepartureDatetime"), ArrivalDeparturetime = a.Field <string>("ArrivalDeparturetime"), Carrier = a.Field <string>("Carrier"), FlightNo = a.Field <string>("FlightNo"), FromCity = a.Field <string>("FromCity"), ToCity = a.Field <string>("ToCity"), RankName = a.Field <string>("RankName"), Stripes = a.Field <decimal?>("Stripes"), RecordLocator = a.Field <string>("RecordLocator"), Gender = a.Field <string>("Gender"), Nationality = a.Field <string>("Nationality"), RoomTypeId = a.Field <int?>("RoomTypeId"), RoomType = a.Field <string>("RoomType"), ReasonCode = a.Field <string>("ReasonCode"), ExceptionRemarks = a.Field <string>("ExceptionRemarks"), Invalid = a.Field <bool?>("Invalid"), VesselName = a.Field <string>("VesselName"), BookingRemarks = a.Field <string>("BookingRemarks"), DateCreated = a.Field <DateTime?>("DateCreated"), HotelCity = GlobalCode.Field2String(a["HotelCity"]), IsByPort = GlobalCode.Field2String(a["IsPort"]) }).ToList() }); if (iLoadType == 0) { dtNoOfDays = ds.Tables[2]; TMSettings.NoOfDays = GlobalCode.Field2Int(dtNoOfDays.Rows[0][0].ToString()); } return(overflow); } catch (Exception ex) { throw ex; } finally { if (dbCommand != null) { dbCommand.Dispose(); } if (dtException != null) { dtException.Dispose(); } if (dtHotels != null) { dtHotels.Dispose(); } if (dtNoOfDays != null) { dtNoOfDays.Dispose(); } if (ds != null) { ds.Dispose(); } } }
/// <summary> /// Author: Josephine Gad /// Date Created: 01/10/2012 /// Description: Get records removed from Exception List /// --------------------------------------------------------------- /// Modified By: Josephine Gad /// Date Modified: 05/Mar/2013 /// Description: Add Comments field /// --------------------------------------------------------------- /// </summary> /// <param name="Date"></param> /// <param name="UserId"></param> /// <param name="Loadtype"></param> /// <param name="RegionID"></param> /// <param name="PortID"></param> /// <returns></returns> public static List <ExceptionBooking> ExceptionGetRemovedList(DateTime Date, string UserId, int Loadtype, int RegionID, int PortID) { List <ExceptionBooking> ExceptionList = new List <ExceptionBooking>(); Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase(); DbCommand dbCommand = null; DataSet ds = null; DataTable dtException = null; int iTotalRows = 0; try { dbCommand = db.GetStoredProcCommand("uspGetHotelExceptionRemovedList"); db.AddInParameter(dbCommand, "@pDate", DbType.Date, Date); db.AddInParameter(dbCommand, "@pUserId", DbType.String, UserId); db.AddInParameter(dbCommand, "@pRegionIDInt", DbType.Int32, RegionID); db.AddInParameter(dbCommand, "@pPortIDInt", DbType.Int32, PortID); ds = db.ExecuteDataSet(dbCommand); dtException = ds.Tables[1]; iTotalRows = GlobalCode.Field2Int(ds.Tables[0].Rows[0][0].ToString()); ExceptionList = (from a in dtException.AsEnumerable() select new ExceptionBooking { ExceptionIdBigInt = GlobalCode.Field2Int(a["ExceptionIdBigInt"]), IdBigint = GlobalCode.Field2Int(a["IDBigint"]), SeqNo = GlobalCode.Field2TinyInt(a["SeqNo"]), TravelReqId = a.Field <int?>("TravelReqId"), E1TravelReqId = a.Field <int?>("E1TravelReqId"), SeafarerId = a.Field <int?>("SeafarerId"), SeafarerName = a.Field <string>("SeafarerName"), PortId = a.Field <int?>("PortId"), PortName = a.Field <string>("PortName"), SFStatus = a.Field <string>("SFStatus"), OnOffDate = a.Field <DateTime?>("OnOffDate"), ArrivalDepartureDatetime = a.Field <DateTime?>("ArrivalDepartureDatetime"), Carrier = a.Field <string>("Carrier"), FlightNo = a.Field <string>("FlightNo"), FromCity = a.Field <string>("FromCity"), ToCity = a.Field <string>("ToCity"), RankName = a.Field <string>("RankName"), Stripes = a.Field <decimal?>("Stripes"), RecordLocator = a.Field <string>("RecordLocator"), Gender = a.Field <string>("Gender"), Nationality = a.Field <string>("Nationality"), RoomTypeId = a.Field <int?>("RoomTypeId"), RoomType = a.Field <string>("RoomType"), ReasonCode = a.Field <string>("ReasonCode"), ExceptionRemarks = a.Field <string>("ExceptionRemarks"), Invalid = a.Field <bool?>("Invalid"), VesselName = a.Field <string>("VesselName"), BookingRemarks = a.Field <string>("BookingRemarks"), Comments = a.Field <string>("Comments"), RemovedBy = a.Field <string>("RemovedBy") }).ToList(); HttpContext.Current.Session["HotelTransactionExceptionExceptionBookingRemovedCount"] = iTotalRows; return(ExceptionList); } catch (Exception ex) { throw ex; } finally { if (dbCommand != null) { dbCommand.Dispose(); } if (dtException != null) { dtException.Dispose(); } if (ds != null) { ds.Dispose(); } } }
/// <summary> /// Date Created: 29/June/2015 /// Created By: Josephine Monteza /// (description) Get Crew Assist Remarks By Date /// --------------------------------------- /// </summary> public List <RemarksByDateList> GetCrewAssistRemarksByDate( Int16 iRequestSourceID, bool bIsByDateRange, Int32 iYear, Int32 iMonth, DateTime dDateForm, DateTime dDateTo, string sUserID, Int16 iLoadType, string sOrderBy, int iStartRow, int iMaxRow) { Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase() DbCommand comm = null; DataSet ds = null; DataTable dt = null; DataTable dtSource = null; //int iRow = 0; List <RemarksByDateList> list = new List <RemarksByDateList>(); try { comm = db.GetStoredProcCommand("uspGetCrewAssistRemarksReportByDate"); db.AddInParameter(comm, "@pRequestSourceIDint", DbType.Int16, iRequestSourceID); db.AddInParameter(comm, "@pIsByDateRange", DbType.Boolean, bIsByDateRange); db.AddInParameter(comm, "@pYear", DbType.Int32, iYear); db.AddInParameter(comm, "@pMonth", DbType.Int32, iMonth); db.AddInParameter(comm, "@pDateFrom", DbType.DateTime, dDateForm); db.AddInParameter(comm, "@pDateTo", DbType.DateTime, dDateTo); db.AddInParameter(comm, "@pUserID", DbType.String, sUserID); db.AddInParameter(comm, "@pLoadType", DbType.Int16, iLoadType); db.AddInParameter(comm, "@pOrderBy", DbType.String, sOrderBy); db.AddInParameter(comm, "@pStartRow", DbType.Int32, iStartRow); db.AddInParameter(comm, "@pMaxRow", DbType.Int32, iMaxRow); ds = db.ExecuteDataSet(comm); if (ds.Tables.Count > 0) { //iRow = GlobalCode.Field2Int(ds.Tables[1].Rows[0][0]); dt = ds.Tables[0]; list = (from a in dt.AsEnumerable() select new RemarksByDateList { RequestType = GlobalCode.Field2String(a["colRemarksTypeVarchar"]), iCount = GlobalCode.Field2Int(a["xCount"]), }).ToList(); //HttpContext.Current.Session["CrewAssistRemarksReportByDate_Total"] = iRow; if (iLoadType == 0) { dtSource = ds.Tables[1]; List <RemarksSourceList> listSource = new List <RemarksSourceList>(); listSource = (from a in dtSource.AsEnumerable() select new RemarksSourceList { RequestSource = GlobalCode.Field2String(a["RequestSource"]), RequestSourceIDint = GlobalCode.Field2Int(a["RequestSourceIDint"]) }).ToList(); HttpContext.Current.Session["CrewAssistRemarksReportByDate_SourceList"] = listSource; } HttpContext.Current.Session["CrewAssistRemarksReportByDate_RemarksList"] = list; } return(list); } catch (Exception ex) { throw ex; } finally { if (comm != null) { comm.Dispose(); } if (ds != null) { ds.Dispose(); } if (dt != null) { dt.Dispose(); } if (dtSource != null) { dtSource.Dispose(); } } }
/// <summary> /// Date Created: 25/June/2015 /// Created By: Josephine Monteza /// (description) Get Crew Assist Remarks /// --------------------------------------- /// </summary> public static List <CrewAssistRemarksList> GetCrewAssistRemarks(Int32 iYear, Int32 iMonth, string sCreatedBy, string sUserID, Int16 iLoadType, Int16 iFilterBy, string sFilterValue, string sOrderBy, short IR, int iStartRow, int iMaxRow) { Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase() DbCommand comm = null; DataSet ds = null; DataTable dt = null; int iRow = 0; List <CrewAssistRemarksList> list = new List <CrewAssistRemarksList>(); try { comm = db.GetStoredProcCommand("uspGetCrewAssistRemarks"); db.AddInParameter(comm, "@pYear", DbType.Int32, iYear); db.AddInParameter(comm, "@pMonth", DbType.Int32, iMonth); db.AddInParameter(comm, "@pCreatedBy", DbType.String, sCreatedBy); db.AddInParameter(comm, "@pUserID", DbType.String, sUserID); db.AddInParameter(comm, "@pLoadType", DbType.Int16, iLoadType); db.AddInParameter(comm, "@pFilterBy", DbType.Int16, iFilterBy); db.AddInParameter(comm, "@pFilterValue", DbType.String, sFilterValue); db.AddInParameter(comm, "@pOrderBy", DbType.String, sOrderBy); db.AddInParameter(comm, "@pStartRow", DbType.Int32, iStartRow); db.AddInParameter(comm, "@pMaxRow", DbType.Int32, iMaxRow); db.AddInParameter(comm, "@pIncidentReport", DbType.Int16, IR); ds = db.ExecuteDataSet(comm); if (ds.Tables.Count > 0) { iRow = GlobalCode.Field2Int(ds.Tables[0].Rows[0][0]); dt = ds.Tables[1]; list = (from a in dt.AsEnumerable() select new CrewAssistRemarksList { TravelRequestID = GlobalCode.Field2Long(a["colTravelReqIdInt"]), SeafarerID = GlobalCode.Field2Long(a["colSeafarerIDBigint"]), Source = a.Field <string>("RequestSource"), RequestHeader = a.Field <string>("RemarksTypeHeader"), RequestType = a.Field <string>("RemarksType"), Summary = a.Field <string>("Summary"), Remarks = a.Field <string>("Remarks"), RemarksStatus = a.Field <string>("RemarksStatus"), Requestor = a.Field <string>("Requestor"), CreatedDate = GlobalCode.Field2DateTime(a["colDateCreatedDateTime"]), CreatedBy = a.Field <string>("colCreatedByVarchar"), TransactionDate = a.Field <DateTime?>("colTransactionDate"), TransactionTime = a.Field <TimeSpan?>("colTransactionTime"), IR = GlobalCode.Field2Bool(a["IRBit"]) }).ToList(); HttpContext.Current.Session["CrewAssistRemarks_Count"] = iRow; } return(list); } catch (Exception ex) { throw ex; } finally { if (comm != null) { comm.Dispose(); } if (ds != null) { ds.Dispose(); } if (dt != null) { dt.Dispose(); } } }
/// Date Modified: 26/03/2013 /// Modified By: Marco Abejar /// (description) Save SF Hotel Request /// ------------------------------------------- /// Date Modified: 30/May/2013 /// Modified By: Josephine Gad /// (description) Add Shuttle, MealLunchDinner, Tax Bit and Tax Percent /// Add fields for audit trail use /// ------------------------------------------- /// </summary> public static string SeafarerSaveRequest(string RequestNo, string SfID, string LastName, string FirstName, string Gender, string RegionID, string PortID, string AirportID, string HotelID, string CheckinDate, string CheckoutDate, string NoNites, string RoomType, bool MealBreakfast, bool MealLunch, bool MealDinner, bool MealLunchDinner, bool WithShuttle, string RankID, string VesselInt, string CostCenter, string Comments, string SfStatus, string TimeIn, string TimeOut, string RoomAmount, bool IsRoomTax, string RoomTaxPercent, string UserID, string TrID, string Currency, string strLogDescription, string strFunction, string strPageName, DateTime DateGMT, DateTime CreatedDate) { Database SFDatebase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase() DbCommand SFDbCommand = null; DbConnection connection = SFDatebase.CreateConnection(); connection.Open(); DbTransaction trans = connection.BeginTransaction(); try { string strTimeZone = TimeZone.CurrentTimeZone.StandardName.ToString(); SFDbCommand = SFDatebase.GetStoredProcCommand("uspInsertSFHotelRequest"); SFDatebase.AddInParameter(SFDbCommand, "@pRequestNo", DbType.String, RequestNo); SFDatebase.AddInParameter(SFDbCommand, "@pSeafarerIDInt", DbType.Int32, Int32.Parse(SfID)); SFDatebase.AddInParameter(SFDbCommand, "@pLastNameVarchar", DbType.String, LastName); SFDatebase.AddInParameter(SFDbCommand, "@pFirstNameVarchar", DbType.String, FirstName); SFDatebase.AddInParameter(SFDbCommand, "@pGender", DbType.Int32, Int32.Parse(Gender)); SFDatebase.AddInParameter(SFDbCommand, "@pRegionIDInt", DbType.Int32, Int32.Parse(RegionID)); SFDatebase.AddInParameter(SFDbCommand, "@pPortIDInt", DbType.Int32, Int32.Parse(PortID)); SFDatebase.AddInParameter(SFDbCommand, "@pAirportIDInt", DbType.Int32, Int32.Parse(AirportID)); SFDatebase.AddInParameter(SFDbCommand, "@pHotelIDInt", DbType.Int32, Int32.Parse(HotelID)); SFDatebase.AddInParameter(SFDbCommand, "@pCheckinDate", DbType.DateTime, CheckinDate); SFDatebase.AddInParameter(SFDbCommand, "@pCheckoutDate", DbType.DateTime, CheckoutDate); SFDatebase.AddInParameter(SFDbCommand, "@pNoNitesInt", DbType.Int32, Int32.Parse(NoNites)); SFDatebase.AddInParameter(SFDbCommand, "@pRoomTypeId", DbType.String, RoomType); SFDatebase.AddInParameter(SFDbCommand, "@pMealBreakfastBit", DbType.Boolean, MealBreakfast); SFDatebase.AddInParameter(SFDbCommand, "@pMealLunchBit", DbType.Boolean, MealLunch); SFDatebase.AddInParameter(SFDbCommand, "@pMealDinnerBit", DbType.Boolean, MealDinner); SFDatebase.AddInParameter(SFDbCommand, "@pMealLunchDinnerBit", DbType.Boolean, MealLunchDinner); SFDatebase.AddInParameter(SFDbCommand, "@pWithShuttleBit", DbType.Boolean, WithShuttle); SFDatebase.AddInParameter(SFDbCommand, "@pRankIDInt", DbType.Int32, Int32.Parse(RankID)); SFDatebase.AddInParameter(SFDbCommand, "@pVesselInt", DbType.Int32, Int32.Parse(VesselInt)); SFDatebase.AddInParameter(SFDbCommand, "@pCostCenterInt", DbType.Int32, Int32.Parse(CostCenter)); SFDatebase.AddInParameter(SFDbCommand, "@pCommentsVarchar", DbType.String, Comments); SFDatebase.AddInParameter(SFDbCommand, "@pSfStatus", DbType.String, SfStatus); SFDatebase.AddInParameter(SFDbCommand, "@pTimeIn", DbType.String, TimeIn); SFDatebase.AddInParameter(SFDbCommand, "@pTimeOut", DbType.String, TimeOut); SFDatebase.AddInParameter(SFDbCommand, "@pRoomAmount", DbType.String, RoomAmount); SFDatebase.AddInParameter(SFDbCommand, "@pRoomRateTaxInclusive", DbType.Boolean, IsRoomTax); SFDatebase.AddInParameter(SFDbCommand, "@pRoomRateTaxPercentage", DbType.Double, GlobalCode.Field2Double(RoomTaxPercent)); SFDatebase.AddInParameter(SFDbCommand, "@pUserID", DbType.String, UserID); SFDatebase.AddInParameter(SFDbCommand, "@pTrID", DbType.String, TrID); SFDatebase.AddInParameter(SFDbCommand, "@pCurrency", DbType.String, Currency); SFDatebase.AddInParameter(SFDbCommand, "@pDescription", DbType.String, strLogDescription); SFDatebase.AddInParameter(SFDbCommand, "@pFunction", DbType.String, strFunction); SFDatebase.AddInParameter(SFDbCommand, "@pFileName", DbType.String, strPageName); SFDatebase.AddInParameter(SFDbCommand, "@pTimezone", DbType.String, strTimeZone); SFDatebase.AddInParameter(SFDbCommand, "@pGMTDATE", DbType.DateTime, DateGMT); SFDatebase.AddInParameter(SFDbCommand, "@pCreateDate", DbType.DateTime, CreatedDate); string sHRID = SFDatebase.ExecuteScalar(SFDbCommand).ToString(); trans.Commit(); return(sHRID); } catch (Exception ex) { trans.Rollback(); throw ex; } finally { if (connection != null) { connection.Close(); connection.Dispose(); } if (SFDbCommand != null) { SFDbCommand.Dispose(); } if (trans != null) { trans.Dispose(); } } }
public List <VehicleTransactionMedical> InsertVehicleTransactionMedical(List <VehicleTransactionMedical> Medical) { Database SFDatebase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase() DbCommand SFDbCommand = null; try { List <VehicleTransactionMedical> VehicleTransMedical = new List <VehicleTransactionMedical>(); GlobalCode gc = new GlobalCode(); DataTable dt = new DataTable(); DataSet ds = new DataSet(); dt = gc.getDataTable(Medical); dt.Columns.Remove("ColorCode"); dt.Columns.Remove("ForeColor"); string strTimeZone = TimeZone.CurrentTimeZone.StandardName.ToString(); SFDbCommand = SFDatebase.GetStoredProcCommand("uspVehicleTransactionMedicalIns"); SqlParameter param = new SqlParameter("@pVehicleTransactionMedical", dt); param.Direction = ParameterDirection.Input; param.SqlDbType = SqlDbType.Structured; SFDbCommand.Parameters.Add(param); ds = SFDatebase.ExecuteDataSet(SFDbCommand); VehicleTransMedical = (from n in ds.Tables[0].AsEnumerable() select new VehicleTransactionMedical { TransVehicleID = GlobalCode.Field2Long(n["colTransVehicleIDBigint"]), SeafarerID = GlobalCode.Field2Long(n["colSeafarerIDBigint"]), IdBigint = GlobalCode.Field2Long(n["colIdBigint"]), TravelReqID = GlobalCode.Field2Long(n["colTravelReqIDInt"]), RecordLocator = GlobalCode.Field2String(n["colRecordLocatorVarchar"]), TranspoVendorID = GlobalCode.Field2Long(n["colTranspoVendorIDInt"]), VehiclePlateNo = GlobalCode.Field2String(n["colVehiclePlateNoVarchar"]), PickUpDate = GlobalCode.Field2DateTime(n["colPickUpDate"]), PickUpTime = GlobalCode.Field2DateTime(n["colPickUpTime"]), DropOffDate = GlobalCode.Field2DateTime(n["colDropOffDate"]), DropOffTime = GlobalCode.Field2DateTime(n["colDropOffTime"]), ConfirmationNo = GlobalCode.Field2String(n["colConfirmationNoVarchar"]), VehicleStatus = GlobalCode.Field2String(n["colVehicleStatusVarchar"]), VehicleTypeId = GlobalCode.Field2Int(n["colVehicleTypeIdInt"]), SFStatus = GlobalCode.Field2String(n["colSFStatus"]), RouteIDFrom = GlobalCode.Field2Int(n["colRouteIDFromInt"]), RouteIDTo = GlobalCode.Field2Int(n["colRouteIDToInt"]), From = GlobalCode.Field2String(n["colFromVarchar"]), To = GlobalCode.Field2String(n["colToVarchar"]), DateCreated = GlobalCode.Field2DateTime(n["colDateCreatedDatetime"]), DateModified = GlobalCode.Field2DateTime(n["colDateModifiedDatetime"]), CreatedBy = GlobalCode.Field2String(n["colCreatedByVarchar"]), Modifiedby = GlobalCode.Field2String(n["colModifiedbyVarchar"]), IsActive = GlobalCode.Field2Bool(n["colIsActiveBit"]), RemarksForAudit = GlobalCode.Field2String(n["colRemarksForAuditVarchar"]), HotelID = GlobalCode.Field2Int(n["colHotelIDInt"]), IsVisible = GlobalCode.Field2Bool(n["colIsVisibleBit"]), ContractId = GlobalCode.Field2Int(n["colContractIdInt"]), IsSeaport = GlobalCode.Field2Bool(n["colIsSeaportBit"]), SeqNo = GlobalCode.Field2Int(n["colSeqNoInt"]), Driver = GlobalCode.Field2String(n["colDriverVarchar"]), VehicleDispatchTime = GlobalCode.Field2String(n["colVehicleDispatchTime"]), RouteFrom = GlobalCode.Field2String(n["colRouteFromVarchar"]), RouteTo = GlobalCode.Field2String(n["colRouteToVarchar"]), VehicleUnset = GlobalCode.Field2Bool(n["colVehicleUnset"]), VehicleUnsetBy = GlobalCode.Field2String(n["colVehicleUnsetBy"]), VehicleUnsetDate = GlobalCode.Field2DateTime(n["colVehicleUnsetDate"]), ConfirmBy = GlobalCode.Field2String(n["colConfirmByVarchar"]), Comments = GlobalCode.Field2String(n["colCommentsVarchar"]), VehicleVendor = GlobalCode.Field2String(n["colVehicleVendorName"]), ContractedRateMoney = GlobalCode.Field2Double(n["colContractedRateMoney"]), ConfirmRateMoney = GlobalCode.Field2Double(n["colConfirmRateMoney"]), CurrencyInt = GlobalCode.Field2Int(n["colCurrencyInt"]), StatusID = GlobalCode.Field2TinyInt(n["colStatusIDTinyint"]), ApprovedBy = GlobalCode.Field2String(n["colApprovedByVarchar"]), ApprovedDate = GlobalCode.Field2DateTime(n["colApprovedDate"]), EmailTo = GlobalCode.Field2String(n["colEmailTovarchar"]), RequestSourceID = GlobalCode.Field2TinyInt(n["colRequestSourceIDInt"]), TransportationDetails = GlobalCode.Field2String(n["colTransportationDetails"]), IsPortAgent = GlobalCode.Field2Bool(n["colIsPortAgentBit"]), ColorCode = GlobalCode.Field2String(n["ColorCode"]), ForeColor = GlobalCode.Field2String(n["ForeColor"]), }).ToList(); return(VehicleTransMedical); } catch (Exception ex) { throw ex; } finally { if (SFDbCommand != null) { SFDbCommand.Dispose(); } } }
public List <HotelTransactionMedical> InsertHotelTransactionMedical(List <HotelTransactionMedical> Medical) { Database SFDatebase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase() DbCommand SFDbCommand = null; try { List <HotelTransactionMedical> HotelTransactionMedical = new List <HotelTransactionMedical>(); GlobalCode gc = new GlobalCode(); DataTable dt = new DataTable(); DataSet ds = new DataSet(); dt = gc.getDataTable(Medical); dt.Columns.Remove("ColorCode"); dt.Columns.Remove("ForeColor"); dt.Columns.Remove("IsMedical"); dt.Columns.Remove("CancellationTermsInt"); dt.Columns.Remove("HotelTimeZoneID"); dt.Columns.Remove("CutOffTime"); dt.Columns.Remove("IsConfirmed"); dt.Columns.Remove("Address"); dt.Columns.Remove("ContactNo"); dt.Columns.Remove("VendorName"); dt.Columns.Remove("RoomType"); string strTimeZone = TimeZone.CurrentTimeZone.StandardName.ToString(); SFDbCommand = SFDatebase.GetStoredProcCommand("uspHotelTransactionMedicalIns"); //SFDatebase.AddInParameter(SFDbCommand, "@pUserID", DbType.Int16, UserID ); SqlParameter param = new SqlParameter("@pHotelTransactionMedical", dt); param.Direction = ParameterDirection.Input; param.SqlDbType = SqlDbType.Structured; SFDbCommand.Parameters.Add(param); ds = SFDatebase.ExecuteDataSet(SFDbCommand); HotelTransactionMedical = (from n in ds.Tables[0].AsEnumerable() select new HotelTransactionMedical { TransHotelID = GlobalCode.Field2Long(n["colTransHotelIDBigInt"]), SeafarerID = GlobalCode.Field2Long(n["colSeafarerIDBigInt"]), FullName = GlobalCode.Field2String(n["colFullNameVarchar"]), TravelReqID = GlobalCode.Field2Long(n["colTravelReqIDInt"]), IdBigint = GlobalCode.Field2Long(n["colIdBigint"]), RecordLocator = GlobalCode.Field2String(n["colRecordLocatorVarchar"]), SeqNo = GlobalCode.Field2Int(n["colSeqNoInt"]), PortAgentVendorID = GlobalCode.Field2Long(n["colPortAgentVendorIDInt"]), RoomTypeID = GlobalCode.Field2Int(n["colRoomTypeIDInt"]), RoomType = GlobalCode.Field2String(n["RoomType"]), ReserveUnderName = GlobalCode.Field2String(n["colReserveUnderNameVarchar"]), TimeSpanStartDate = GlobalCode.Field2DateTime(n["colTimeSpanStartDate"]), TimeSpanStartTime = GlobalCode.Field2DateTime(n["colTimeSpanStartTime"]), TimeSpanEndDate = GlobalCode.Field2DateTime(n["colTimeSpanEndDate"]), TimeSpanEndTime = GlobalCode.Field2DateTime(n["colTimeSpanEndTime"]), TimeSpanDuration = GlobalCode.Field2Int(n["colTimeSpanDurationInt"]), ConfirmationNo = GlobalCode.Field2String(n["colConfirmationNoVarchar"]), HotelStatus = GlobalCode.Field2String(n["colHotelStatusVarchar"]), DateCreatedDatetime = GlobalCode.Field2DateTime(n["colDateCreatedDatetime"]), CreatedBy = GlobalCode.Field2String(n["colCreatedByVarchar"]), IsActive = GlobalCode.Field2Bool(n["colIsActiveBit"]), VoucherAmount = GlobalCode.Field2Long(n["colVoucherAmountMoney"]), ContractID = GlobalCode.Field2Long(n["colContractIDInt"]), ApprovedBy = GlobalCode.Field2String(n["colApprovedByVarchar"]), ApprovedDate = GlobalCode.Field2DateTime(n["colApprovedDate"]), ContractFrom = GlobalCode.Field2String(n["colContractFromVarchar"]), RemarksForAudit = GlobalCode.Field2String(n["colRemarksForAuditVarchar"]), HotelCity = GlobalCode.Field2String(n["colHotelCityVarchar"]), RoomCount = GlobalCode.Field2Float(n["colRoomCountFloat"]), HotelName = GlobalCode.Field2String(n["colHotelNameVarchar"]), ConfirmRateMoney = GlobalCode.Field2Decimal(n["colConfirmRateMoney"]), ContractedRateMoney = GlobalCode.Field2Decimal(n["colContractedRateMoney"]), EmailTo = GlobalCode.Field2String(n["colEmailToVarchar"]), Comment = GlobalCode.Field2String(n["colCommentVarchar"]), CurrencyID = GlobalCode.Field2Int(n["colCurrencyInt"]), ConfirmBy = GlobalCode.Field2String(n["colConfirmByVarchar"]), StatusID = GlobalCode.Field2TinyInt(n["colStatusIDTinyint"]), ColorCode = GlobalCode.Field2String(n["ColorCode"]), ForeColor = GlobalCode.Field2String(n["ForeColor"]), IsMedical = GlobalCode.Field2Bool(n["IsMedical"]), CancellationTermsInt = GlobalCode.Field2String(n["CancellationTermsInt"]), HotelTimeZoneID = GlobalCode.Field2String(n["HotelTimeZoneID"]), CutOffTime = GlobalCode.Field2String(n["CutOffTime"]), IsConfirmed = GlobalCode.Field2String(n["IsConfirmed"]), Address = GlobalCode.Field2String(n["Address"]), ContactNo = GlobalCode.Field2String(n["ContactNo"]), Breakfast = GlobalCode.Field2Bool(n["colBreakfastBit"]), IsBilledToCrew = GlobalCode.Field2Bool(n["colIsBilledToCrewBit"]), Lunch = GlobalCode.Field2Bool(n["colLunchBit"]), Dinner = GlobalCode.Field2Bool(n["colDinnerBit"]), LunchOrDinner = GlobalCode.Field2Bool(n["colLunchOrDinnerBit"]), WithShuttle = GlobalCode.Field2Bool(n["colWithShuttleBit"]), VendorName = GlobalCode.Field2String(n["VendorName"]), IsPortAgent = GlobalCode.Field2Bool(n["IsPortAgent"]), }).ToList(); return(HotelTransactionMedical); } catch (Exception ex) { throw ex; } finally { if (SFDbCommand != null) { SFDbCommand.Dispose(); } } }
/// <summary> /// Created By: Muha Wali /// Date Modified: Sept 2016 /// (description) /// ================================================ /// Modified By: Josephine Monteza /// Date Modified: 08/Sept/2016 /// (description) Change index to name of column when retrieving InvoiceException /// Add field DateCreated in the result /// ================================================ /// </summary> public List <InvoiceHeader> GetInvoice(string vendorNo, string from, string to, int StatusID) { Database SFDatebase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase() DbCommand SFDbCommand = null; try { InvoiceHeader = new List <InvoiceHeader>(); SFDbCommand = SFDatebase.GetStoredProcCommand("uspGetInvoicesToBill"); SFDatebase.AddInParameter(SFDbCommand, "@pLoadType", DbType.Int16, 0); SFDatebase.AddInParameter(SFDbCommand, "@pVendorNum", DbType.String, vendorNo); SFDatebase.AddInParameter(SFDbCommand, "@pFromDate", DbType.DateTime, GlobalCode.Field2DateTime(from)); SFDatebase.AddInParameter(SFDbCommand, "@pToDate", DbType.DateTime, GlobalCode.Field2DateTime(to)); SFDatebase.AddInParameter(SFDbCommand, "@pStatusID", DbType.Int32, StatusID); ds = SFDatebase.ExecuteDataSet(SFDbCommand); InvoiceHeader = (from n in ds.Tables[0].AsEnumerable() //where a["colVendorNumber"].ToString() == n["colVendorNumber"].ToString() select new InvoiceHeader { InvoiceNumID = n["colInvoiceNumIDInt"].ToString(), VendorNumID = n["colVendorNumber"].ToString(), InvoiceNumber = n["colInvoiceNumber"].ToString(), InvoiceDate = GlobalCode.Field2DateTime(n["colInvoiceDate"]), InvoiceTotal = n["colInvoiceTotal"].ToString(), PortNumber = n["colPortNumber"].ToString(), ShipNumber = n["colShipNumber"].ToString(), BusinessUnitCode = n["colBusinessUnitCode"].ToString(), CreatedByVarchar = n["colCreatedByVarchar"].ToString(), Port = n["PortName"].ToString(), Ship = n["VesselName"].ToString(), ExceptionID = GlobalCode.Field2TinyInt(n["ExceptionID"]), Exception = n["InvoiceStatus"].ToString(), Image = n["Image"].ToString(), InvoiceDetail = (from e in ds.Tables[1].AsEnumerable() where n["colInvoiceNumIDInt"].ToString() == e["colInvoiceNumIDInt"].ToString() select new InvoiceDetail { InvoiceNumID = e["colInvoiceNumIDInt"].ToString(), InvoiceDetailID = e["colInvoiceDetailIDInt"].ToString(), ExpenseTypeCode = e["colExpenseTypeCodeVarchar"].ToString(), Quantity = e["colQuantity"].ToString(), UnitCost = e["colUnitCost"].ToString(), CurrencyCode = e["colCurrencyCode"].ToString(), TotalCost = e["colTotalCost"].ToString(), Comment = e["colComment"].ToString(), EmployeeNumber = e["colEmployeeNumber"].ToString(), CrewServiceStartDate = GlobalCode.Field2DateTime(e["colCrewServiceStartDate"]), CrewServiceEndDate = GlobalCode.Field2DateTime(e["colCrewServiceEndDate"]), UnitofMeasureType = e["colUnitofMeasureType"].ToString(), TripNumber = e["colTripNumber"].ToString(), CreatedByVarchar = e["colCreatedByVarchar"].ToString(), CreatedDateTime = e["colCreatedDateTime"].ToString(), InvoiceNumber = n["colInvoiceNumber"].ToString(), Exclude = GlobalCode.Field2Bool(e["colIsExcludeBit"]), Backcolor = e["BackColor"].ToString(), IsExcetion = GlobalCode.Field2TinyInt(e["ExceptionID"]) }).ToList(), InvoiceException = (from i in ds.Tables[2].AsEnumerable() where i["colInvoiceNumber"].ToString() == n["colInvoiceNumber"].ToString() select new InvoiceException { InvoiceExceptionID = i["colInvoiceExceptionIDInt"].ToString(), Success = i["Success"].ToString(), Message = i["colMessageVarchar"].ToString(), InvoiceNumber = i["colInvoiceNumber"].ToString(), VendorNumber = i["colVendorNumber"].ToString(), DateCreated = i["DateCreated"].ToString(), InvoiceExceptionDetail = (from a in ds.Tables[3].AsEnumerable() where i[0].ToString() == a[1].ToString() select new InvoiceExceptionDetail { InvoiceExceptionID = n[0].ToString(), Success = n[1].ToString(), Message = n[2].ToString(), InvoiceNumber = n[3].ToString(), VendorNumber = n[4].ToString(), ErrorMessage = a["colErrorMessageVarchar"].ToString(), Type = a["colTypeVarchar"].ToString(), Owner = a["colErrorTypeOwnerVarchar"].ToString(), }).ToList() }).ToList() }).ToList(); } catch (Exception ex) { throw ex; } finally { if (SFDbCommand != null) { SFDbCommand.Dispose(); } } return(InvoiceHeader); }
public List <InvoiceHeader> GetSingleInvoicesToBill(string VendorNum, string InvoiceDate, ref string Token, ref string validation) { Database SFDatebase = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase() DbCommand SFDbCommand = null; try { InvoiceHeader = new List <InvoiceHeader>(); SFDbCommand = SFDatebase.GetStoredProcCommand("uspGetSingleInvoicesToBill"); SFDatebase.AddInParameter(SFDbCommand, "@pLoadType", DbType.Int16, 0); SFDatebase.AddInParameter(SFDbCommand, "@pInvoiceNumber", DbType.String, VendorNum); SFDatebase.AddInParameter(SFDbCommand, "@pInvoiceDate", DbType.DateTime, GlobalCode.Field2DateTime(InvoiceDate)); ds = SFDatebase.ExecuteDataSet(SFDbCommand); InvoiceHeader = (from n in ds.Tables[0].AsEnumerable() select new InvoiceHeader { InvoiceNumID = n["colInvoiceNumIDInt"].ToString(), VendorNumber = n["colVendorNumber"].ToString(), InvoiceNumber = n["colInvoiceNumber"].ToString(), InvoiceDate = GlobalCode.Field2DateTime(n["colInvoiceDate"]), InvoiceTotal = n["colInvoiceTotal"].ToString(), PortNumber = n["colPortNumber"].ToString(), ShipNumber = n["colShipNumber"].ToString(), BusinessUnitCode = n["colBusinessUnitCode"].ToString(), CreatedByVarchar = n["colCreatedByVarchar"].ToString(), Port = n["PortName"].ToString(), Ship = n["VesselName"].ToString(), InvoiceDetail = (from e in ds.Tables[1].AsEnumerable() where n["colVendorNumber"].ToString() == e["colVendorNumber"].ToString() && n["colInvoiceNumIDInt"].ToString() == e["colInvoiceNumIDInt"].ToString() select new InvoiceDetail { InvoiceNumID = e["colInvoiceDetailIDInt"].ToString(), InvoiceDetailID = e["colInvoiceNumIDInt"].ToString(), ExpenseTypeCode = e["colExpenseTypeCodeVarchar"].ToString(), Quantity = e["colQuantity"].ToString(), UnitCost = e["colUnitCost"].ToString(), CurrencyCode = e["colCurrencyCode"].ToString(), TotalCost = e["colTotalCost"].ToString(), Comment = e["colComment"].ToString(), EmployeeNumber = e["colEmployeeNumber"].ToString(), //CrewServiceStartDate = String.Format("{0:yyyy-MM-dd}", e["colCrewServiceStartDate"]),//e["colCrewServiceStartDate"].ToString(), //CrewServiceEndDate = String.Format("{0:yyyy-MM-dd}", e["colCrewServiceEndDate"]),// e["colCrewServiceEndDate"].ToString(), CrewServiceStartDate = GlobalCode.Field2DateTime(e["colCrewServiceStartDate"]), CrewServiceEndDate = GlobalCode.Field2DateTime(e["colCrewServiceEndDate"]), UnitofMeasureType = e["colUnitofMeasureType"].ToString(), TripNumber = e["colTripNumber"].ToString(), CreatedByVarchar = e["colCreatedByVarchar"].ToString(), CreatedDateTime = e["colCreatedDateTime"].ToString(), }).ToList() }).ToList(); Token = ds.Tables[2].Rows[0]["colAccesTokenVarchar"].ToString(); validation = ds.Tables[3].Rows[0]["InvoiceStatus"].ToString();; } catch (Exception ex) { throw ex; } finally { if (SFDbCommand != null) { SFDbCommand.Dispose(); } if (ds != null) { ds.Dispose(); } } return(InvoiceHeader); }