public string AppvendorList(int BatchID, string approveremailID, string type)
{
var status = string.Empty;
try
{
List <VendorModels> datalist = _context.NONSODVendorMaster.Where(a => (a.BatchId == BatchID || a.ID == BatchID) && a.IsApproved != 0).ToList();
if (datalist.Count > 0)
{
foreach (var i in datalist)
{
if (i.IsApproved == 1)
{
return("Vendor Detail has been approved already");//Exist
}
else if (i.IsApproved == 2)
{
return("Vendor Detail has been rejected already");//Exist
}
}
}
else
{
datalist = _context.NONSODVendorMaster.Where(a => (a.BatchId == BatchID || a.ID == BatchID) && a.IsApproved != 1).ToList();
List <VendorApprovallog> logTable = new List <VendorApprovallog>();
if (datalist != null)
{
foreach (var data in datalist)
{
VendorApprovallog modal = new VendorApprovallog();
data.IsApproved = 1;
data.Approvaldate = DateTime.Now;
data.ApprovedbyEmpEmailID = approveremailID;
modal.VendorCode = data.VendorCode;
modal.ApprovalStatus = data.IsApproved;
modal.ApprovedbyEmpEmailID = data.ApprovedbyEmpEmailID;
modal.Approvaldate = DateTime.Now;
modal.ApprovalModifiedDate = DateTime.Now;
modal.BatchID = data.BatchId;
modal.Remarks = "Approved";
logTable.Add(modal);
var insertdata = _context.NONSODVendorApprovalLog.AddRange(logTable);
var s = _context.SaveChanges();
}
status = "Vendor Detail has been Approved";
}
else
{
return("error");//Error
}
}
}
catch (Exception ex)
{
throw;
}
return(status);
}
public int ApproveOatBookingRequest(OatTravelRequestApprovalModel travelRequestApprovalModels)
{
var c = _context.OatTravelRequestApprovalModel.Where(o => o.TravelRequestId == travelRequestApprovalModels.TravelRequestId).ToList();
if (c.Count() > 0)
{
return(1);
}
_context.OatTravelRequestApprovalModel.Add(travelRequestApprovalModels);
var s = _context.SaveChanges();
return(s);
}
/// <summary>
/// update city master data
/// </summary>
/// <param name="elist"></param>
/// <returns></returns>
public int UpdateCityList(List <SodCityCodeMasterModels> elist)
{
var s = 0;
var id = elist[0].Id;
var hotelcode = elist[0].CityCode;
var list = _context.SodCityCodeMasterModel.Where(o => o.Id == id).ToList();
foreach (var i in list)
{
i.CityName = elist[0].CityName;
i.CityCode = elist[0].CityCode;
i.Type = elist[0].Type;
}
s = _context.SaveChanges();
return(s);
}
/// <summary>
/// Mail Remarks
/// </summary>
/// <param name="HRremarks"></param>
/// <param name="ReqID"></param>
public void updateconfirmationMailRemarks(string HRremarks, Int64 ReqID)
{
var data = _context.SODEmployeeChangeRequestDetails.Where(x => x.ReqId == ReqID).FirstOrDefault();
if (data != null && data.ToString() != "")
{
data.HRApprovalRemarks = HRremarks;
_context.SaveChanges();
}
}
/// <summary>
/// Register User
/// </summary>
/// <param name="UserData"></param>
/// <returns></returns>
public Int64 registerUser(SJSCUserMasterModels UserData)
{
SJSCUserMasterModels regData = new SJSCUserMasterModels();
regData.EmpCode = UserData.EmpCode.Trim();
regData.Title = UserData.Title.Trim();
regData.FirstName = UserData.FirstName.Trim();
regData.LastName = UserData.LastName.Trim();
regData.Designation = UserData.Designation.Trim();
regData.Department = UserData.Department.Trim();
regData.EmailID = UserData.EmailID.Trim();
regData.MobileNo = UserData.MobileNo.Trim();
regData.SJSCVerticalID = UserData.SJSCVerticalID;
regData.Pwd = UserData.Pwd.Trim();
regData.HodTitle = UserData.HodTitle.Trim();
regData.HodName = UserData.HodName.Trim();
regData.HodEmailId = UserData.HodEmailId.Trim();
regData.HodMobileNo = UserData.HodMobileNo.Trim();
regData.UserId = UserData.EmailID;
regData.IsApprover = UserData.EmailID.Trim().Equals(UserData.HodEmailId.Trim()) ? true : false;
regData.IsActive = true;
regData.CreateDate = DateTime.Now;
regData.ModifiedDate = DateTime.Parse("01/01/1900");
_context.SJSCUserMasterModel.Add(regData);
//manage sjsc role
_context.SaveChanges();
return(regData.ID);
}
/// <summary>
/// update inclusion master data
/// </summary>
/// <param name="elist"></param>
/// <returns></returns>
public int UpdateHotelInclusion(List <HotelInclusionMasterModels> elist)
{
var s = 0;
var id = elist[0].Id;
var list = _context.HotelInclusionMasterModel.Where(o => o.Id == id).ToList();
foreach (var i in list)
{
i.HotelName = elist[0].HotelName;
i.Accomodation = elist[0].Accomodation;
i.AirportTransfers = elist[0].AirportTransfers;
i.Amenities = elist[0].Amenities;
i.BuffetTime = elist[0].BuffetTime;
i.Discount = elist[0].BuffetTime;
i.DrinkingWater = elist[0].DrinkingWater;
i.Food = elist[0].Food;
i.Laundry = elist[0].Laundry;
i.Location = elist[0].Location;
i.Newspaper = elist[0].Newspaper;
i.RoomService = elist[0].RoomService;
i.TeaMaker = elist[0].TeaMaker;
i.WiFi = elist[0].WiFi;
i.CheckinOutHours = elist[0].CheckinOutHours;
i.RetentionCancellation = elist[0].RetentionCancellation;
i.SpouseStay = elist[0].SpouseStay;
}
s = _context.SaveChanges();
return(s);
}
/// <summary>
/// save hod status
/// </summary>
/// <param name="TravelRequestId"></param>
/// <param name="status"></param>
/// <returns></returns>
public int saveHODStatus(long TravelRequestId, string status)
{
var _context = new SodEntities();
var updateItem = _context.OALTravelRequestMasterModel.Where(b => b.TravelRequestId == TravelRequestId).ToList();
foreach (var p in updateItem)
{
p.FinancialApproval = status;
}
var i = _context.SaveChanges();
return(i);
}
/// <summary>
/// save hod response
/// </summary>
/// <param name="IdList"></param>
/// <param name="criteria"></param>
/// <returns></returns>
public int saveHODResponseData(string IdList, int criteria)
{
List <ITHResponseDetailModels> ithdetails = new List <ITHResponseDetailModels>();
ithdetails = ADO.SodCommonServices.saveHODResponseData(IdList, criteria);
List <ITHFinancialApprovalHODModels> hlist = new List <ITHFinancialApprovalHODModels>();
var _context = new SodEntities();
foreach (var item in ithdetails)
{
var lst = new ITHFinancialApprovalHODModels();
lst.TravelRequestId = item.TravelRequestId;
lst.ResponseDate = item.ResponseDate;
lst.OriginPlace = item.OriginPlace;
lst.DestinationPlace = item.DestinationPlace;
lst.TravelDate = item.TravelDate;
lst.FlightNo = item.FlightNo;
lst.FlightName = item.FlightName;
lst.DepartureTime = item.DepartureTime;
lst.ArrivalTime = item.ArrivalTime;
lst.Amount = item.Amount;
lst.FlightType = item.FlightType;
hlist.Add(lst);
}
_context.ITHFinancialApprovalHODModel.AddRange(hlist);
//save approval status in ith details table
for (var j = 0; j < IdList.Split(',').Length - 1; j++)
{
var id = Convert.ToInt64(IdList.Split(',')[j]);
var updateith = _context.ITHResponseDetailModel.Where(b => b.ID == id).ToList();
foreach (var p in updateith)
{
p.HodApproval = "Approved";
}
}
//save approval status in master table
var trid = ithdetails[0].TravelRequestId;
var updateItem = _context.OALTravelRequestMasterModel.Where(b => b.TravelRequestId == trid).ToList();
foreach (var p in updateItem)
{
p.FinancialApproval = "Approved";
}
var i = _context.SaveChanges();
return(i);
}
public string AddUpdateVendor(VendorModels vendorModels)
{
string msg = "Record successfully added!";
try
{
if (vendorModels.ID > 0)
{
var data = _context.NONSODVendorMaster.Where(n => n.ID == vendorModels.ID).OrderBy(n => n.ID).FirstOrDefault();
if (data != null)
{
msg = "Record has been updated successfully.";
data.FirstName = vendorModels.FirstName;
data.LastName = vendorModels.LastName;
data.MobileNo = vendorModels.MobileNo;
data.EmailId = vendorModels.EmailId;
data.Gender = vendorModels.Gender;
data.CompanyName = vendorModels.CompanyName;
data.ModifiedDate = vendorModels.ModifiedDate;
data.AddVendorOnBehalfof = vendorModels.AddVendorOnBehalfof;
}
}
else
{
var num = _context.NONSODVendorMaster.Select(x => x.ID).Max();
vendorModels.VendorCode = WebConfigurationManager.AppSettings["VendorPrefixKey"] + (num > 0 ? (num + 1) : 1);
_context.NONSODVendorMaster.Add(vendorModels);
}
_context.SaveChanges();
}
catch (Exception ex)
{
throw;
}
return(msg);
}
/// <summary>
/// Save Sod and non-Sod Request Info
/// </summary>
/// <param name="sodRequestsList"></param>
/// <param name="sodflightList"></param>
/// <param name="passengerList"></param>
/// <returns></returns>
public Int64 SaveHotelRequest(List <TravelRequestHotelDetailModels> hotelDetailList)
{
var s = 0;
using (System.Data.Entity.DbContextTransaction dbTran = _context.Database.BeginTransaction())
{
try
{
//Get Max Request Id
//var requestMaxId = _context.TravelRequestHotelDetailModel.DefaultIfEmpty().Max(x => x == null ? 1 : x.TravelRequestId);
//hotelDetailList[0].TravelRequestId = requestMaxId;
hotelDetailList[0].HotelReferenceID = ConfigurationManager.AppSettings["Hotel_Booking_ReferenceId"].Trim() + hotelDetailList[0].TravelRequestId;
foreach (var i in hotelDetailList)
{
_context.TravelRequestHotelDetailModel.Add(i);
}
var trId = Convert.ToInt32(hotelDetailList[0].TravelRequestId);
var updateItem = _context.TravelRequestMasterModel.Where(o => o.TravelRequestId == trId);
foreach (var p in updateItem)
{
p.IsHotelRequired = true;
}
_context.SaveChanges();
//_context.TravelRequestMasterModel.Add(updateItem);
dbTran.Commit();
s = (int)hotelDetailList[0].TravelRequestId;
}
catch (Exception ex)
{
//Rollback transaction if exception occurs
dbTran.Rollback();
s = -1;
throw;
}
}
return(s);
}
/// <summary>
/// save ith response data
/// </summary>
/// <param name=""></param>
/// <returns></returns>
public int SaveITHResponseData(List <ITHResponseDetailModels> ithList)
{
var _context = new SodEntities();
_context.ITHResponseDetailModel.AddRange(ithList);
var trid = ithList[0].TravelRequestId;
var updateItem = _context.OALTravelRequestMasterModel.Where(b => b.TravelRequestId == trid).ToList();
foreach (var p in updateItem)
{
p.ITHResponseStatus = "Response Received";
}
var i = _context.SaveChanges();
return(i);
}
/// <summary>
/// save ITH request
/// </summary>
/// <param name="oatList"></param>
/// <returns></returns>
public int SaveITHRequestData(List <ITHRequestApprovalModels> hlist)
{
var _context = new SodEntities();
_context.ITHRequestApprovalModel.AddRange(hlist);
var trid = hlist[0].TravelRequestId;
var updateItem = _context.OALTravelRequestMasterModel.Where(b => b.TravelRequestId == trid).ToList();
foreach (var p in updateItem)
{
p.ITHResponseStatus = "Response Pending from ITH";
}
var i = _context.SaveChanges();
return(i);
}
/// <summary>
/// Send SMS and Save data in DB
/// </summary>
public static void getSmsData()
{
SodEntities _context = new SodEntities();
DateTime currentDate = DateTime.Now;
//DateTime currentDate = new DateTime(2018, 07, 04, 11, 8, 0);
var time1 = currentDate.AddMinutes(30).ToShortTimeString();
var time = currentDate.AddMinutes(35).ToShortTimeString();
try
{
var data = SodCommonServices.GetSMSData(currentDate.Date, time1, time).ToList();
if (data != null && data.Count > 0)
{
foreach (var item in data)
{
//modify link for cancellation in sms
string rqstQueryString = ConfigurationManager.AppSettings["smsLink"].ToString() + "?trid=" + item.TravelRequestId.ToString()
+ "," + item.HotelRequestId.ToString() + ",sms";
string msgContent = ConfigurationManager.AppSettings["smsMsg"].ToString();
msgContent = msgContent.Replace("[rqstQueryString]", rqstQueryString);
//save sms sent status in database
var flightData = _context.FlightDetailModel.SingleOrDefault(s => s.Id == item.Id);
if (flightData != null)
{
if (!flightData.IsHotelSendSms)
{
bool status = SendSmsViaApi(msgContent, item.Phone);
if (status)
{
flightData.IsHotelSendSms = true;
}
_context.SaveChanges();
}
}
}
}
}
catch (Exception ex)
{
AddDBLogging(ex, "getSmsData", "SmsNotifications.cs");
}
}
/// <summary>
/// Reset Password
/// </summary>
/// <param name="userLoginDal"></param>
/// <returns></returns>
public int ResetPassword(UserAccountModels userLoginDal)
{
var s = 0;
using (_context)
{
var result = from u in _context.SodUsersInfo
where (u.UserName == userLoginDal.UserName) && (u.Password == userLoginDal.OldPassword)
select u;
if (result.Count() == 1)
{
var dbuser = result.First();
dbuser.Password = userLoginDal.Password;
s = _context.SaveChanges();
}
}
return(s);
}
/// <summary>
/// Add DB Logging for SMS
/// </summary>
/// <param name="ex"></param>
/// <param name="methosName"></param>
/// <param name="filePath"></param>
public static void AddDBLogging(Exception ex, string methosName, string filePath)
{
SodEntities _context = new SodEntities();
SmsLoggingModels obj = new SmsLoggingModels();
try
{
obj.LogDate = System.DateTime.Now;
obj.LogMessage = ex.Message.ToString();
obj.LogData = ex.Data.ToString();
obj.LogSource = ex.Source;
obj.HelpLink = ex.HelpLink == null ? "" : ex.HelpLink;
obj.HResult = ex.HResult == null ? "" : ex.HResult.ToString();
obj.InnerException = ex.InnerException == null ? "" : ex.InnerException.Message;
obj.MethodName = methosName;
obj.FilePath = filePath;
_context.SmsLoggingModel.Add(obj);
_context.SaveChanges();
}
catch (Exception)
{
}
}
/// <summary>
/// Save OAL details
/// </summary>
/// <param name="flightList"></param>
/// <param name="passengerList"></param>
/// <param name="hotelList"></param>
/// <param name="cabList"></param>
/// <param name="masterList"></param>
/// <returns></returns>
public Int64 SaveOALData(List <OALModels> oalList, List <OALPassengerModel> plist, List <OALHotelModel> hlist, List <OALCabModel> clist, List <OALTravelRequestMasterModel> mlist)
{
Int64 requestMaxId = 0;
using (System.Data.Entity.DbContextTransaction dbTran = _context.Database.BeginTransaction())
{
try
{
//Master List
_context.OALTravelRequestMasterModel.Add(mlist[0]);
_context.SaveChanges();
requestMaxId = _context.OALTravelRequestMasterModel.DefaultIfEmpty().Max(x => x == null ? 1 : x.TravelRequestId);
//Update Code for OAT Travel Request Info
var mlists = _context.OALTravelRequestMasterModel.Where(o => o.TravelRequestId == requestMaxId);
foreach (var list in mlists)
{
list.TravelRequestId = requestMaxId;
list.TravelRequestCode = "OAT-" + requestMaxId;
}
//For OAT Details
foreach (var list in oalList)
{
list.TravelRequestId = requestMaxId;
}
_context.OALModels.AddRange(oalList);
//For Passenger List
foreach (var list in plist)
{
list.TravelRequestId = requestMaxId;
}
_context.OALPassengerModel.AddRange(plist);
//For Hotel List
if (hlist != null)
{
foreach (var list in hlist)
{
list.TravelRequestId = requestMaxId;
list.HotelReferenceId = "SH" + requestMaxId;
}
_context.OALHotelModel.AddRange(hlist);
}
//For Cab Request List
if (clist != null)
{
foreach (var list in clist)
{
list.TravelRequestId = requestMaxId;
}
_context.OALCabModel.AddRange(clist);
}
_context.SaveChanges();
dbTran.Commit();
}
catch (Exception ex)
{
//Rollback transaction if exception occurs
dbTran.Rollback();
requestMaxId = -1;
throw;
}
}
return(requestMaxId);
}
/// <summary>
/// Save Sod Approval List
/// </summary>
/// <param name="sodApproverModels"></param>
/// <returns></returns>
public int Save(SodApproverModels sodApproverModels)
{
_context.SodApprovers.Add(sodApproverModels);
_context.SaveChanges();
return(1);
}
/// <summary>
/// Save logg/Error into database
/// </summary>
/// <param name="loggingModel"></param>
/// <returns></returns>
public string SaveDBLogg(LoggingModels loggingModel)
{
this._context.LoggingModel.Add(loggingModel);
_context.SaveChanges();
return("ER001");
}
/// <summary>
/// Approve hotel booking request
/// </summary>
/// <param name="clubId"></param>
/// <param name="confirmationNo"></param>
/// <param name="sodOat"></param>
/// <returns></returns>
public int ApproveHotelBookingRequest(string clubId, string confirmationNo, string hotelname, string sodOat)
{
var s = 0;
var cId = Convert.ToInt32(clubId);
if (sodOat == "SOD")
{
var updateItem = _context.HotelRequestApprovalModel.Where(b => b.clubId == cId && b.HotelName.ToLower().Trim() == hotelname.ToLower().Trim()).ToList();
foreach (var p in updateItem)
{
p.Status = "Approved";
p.HotelConfirmationNo = confirmationNo;
p.ApprovalDate = DateTime.Now;
}
var hoteldetails = new List <HotelRequestApprovalModel>();
hoteldetails = _context.HotelRequestApprovalModel.Where(b => b.clubId == cId && b.HotelName.ToLower().Trim() == hotelname.ToLower().Trim()).ToList();
//var hCode = hoteldetails[0].HotelCode;
//var priceList = new List<SodHotelPriceListMasterModels>();
//priceList = _context.SodHotelPriceListMasterModel.Where(b => b.HotelCode == hCode).ToList();
var updateItem2 = _context.TravelRequestHotelDetailModel.Where(o => o.clubId == cId).ToList();
foreach (var p in updateItem2)
{
p.HotelStatus = "Approved by Hotel";
p.HotelCode = hoteldetails[0].HotelCode;
p.HotelName = hoteldetails[0].HotelName;
p.HotelAddress = hoteldetails[0].HotelAddress;
p.HotelPhoneNo = hoteldetails[0].HotelPhone;
p.PrimaryEmail = hoteldetails[0].PrimaryEmail;
p.SecondaryEmail = hoteldetails[0].SecondaryEmail;
p.HotelType = hoteldetails[0].HotelType;
p.HotelConfirmationNo = confirmationNo;
p.IsAllocated = 1;
//if (hoteldetails[0].HotelType == "Contractual")
//{
// if (updateItem.Count > 1)
// {
// p.HotelPrice = priceList[0].DoublePrice;
// }
// else
// {
// p.HotelPrice = priceList[0].SinglePrice;
// }
//}
}
//if (hoteldetails[0].HotelType == "Contractual")
//{
// if (updateItem.Count > 1)
// {
// foreach (var p in updateItem)
// {
// p.HotelPrice = priceList[0].DoublePrice;
// }
// }
// else
// {
// foreach (var p in updateItem)
// {
// p.HotelPrice = priceList[0].SinglePrice;
// }
// }
//}
}
else
{
var updateItem = _context.HotelRequestApprovalOatModel.Where(b => b.clubId == cId && b.HotelName.ToLower().Trim() == hotelname.ToLower().Trim()).ToList();
foreach (var p in updateItem)
{
p.Status = "Approved";
p.HotelConfirmationNo = confirmationNo;
p.ApprovalDate = DateTime.Now;
}
var hoteldetails = new List <HotelRequestApprovalOatModels>();
hoteldetails = _context.HotelRequestApprovalOatModel.Where(b => b.clubId == cId && b.HotelName.ToLower().Trim() == hotelname.ToLower().Trim()).ToList();
//var hCode = hoteldetails[0].HotelCode;
//var priceList = new List<SodHotelPriceListMasterModels>();
//priceList = _context.SodHotelPriceListMasterModel.Where(b => b.HotelCode == hCode).ToList();
var updateItem2 = _context.OALHotelModel.Where(o => o.clubId == cId);
foreach (var p in updateItem2)
{
p.HotelStatus = "Approved by Hotel";
p.HotelCode = hoteldetails[0].HotelCode;
p.HotelName = hoteldetails[0].HotelName;
p.HotelAddress = hoteldetails[0].HotelAddress;
p.HotelPhoneNo = hoteldetails[0].HotelPhone;
p.PrimaryEmail = hoteldetails[0].PrimaryEmail;
p.SecondaryEmail = hoteldetails[0].SecondaryEmail;
p.HotelType = hoteldetails[0].HotelType;
p.HotelConfirmationNo = confirmationNo;
}
}
s = _context.SaveChanges();
return(s);
}
/// <summary>
/// Save Sod Approver
/// </summary>
/// <param name="sodApproverModels"></param>
/// <returns></returns>
public int SaveSodApprover(List <SodApproverModels> sodApproverModels)
{
var counter = 0;
foreach (var item in sodApproverModels)
{
var s = _context.SodApprovers.Where(id => id.DepartmentId == item.DepartmentId && id.EmployeeId == item.EmployeeId && id.VerticleId == item.VerticleId).ToList();
if (s.Count() > 0)
{
counter++;
}
}
if (counter > 0)
{
return(0);
}
_context.SodApprovers.AddRange(sodApproverModels);
return(_context.SaveChanges());
}
