public static string Generate_PaymentId()
{
var dbcontext = new PegasusEntities();
int id = 0;
try
{
var seriesParameter = new SqlParameter()
{
ParameterName = "series",
DbType = DbType.Int32,
Direction = ParameterDirection.Output
};
var seriesId = dbcontext.Database.SqlQuery <int>("exec Generate_PmtCode @series out", seriesParameter).FirstOrDefault();
id = Convert.ToInt32(seriesId);
}
catch (NullReferenceException)
{
id = id + 1;
}
catch (FormatException)
{
id = id + 1;
}
return(String.Format("{0:0000000}", id));
}
public IEnumerable <SalesSummaryViewModel> GetSalesSummary()
{
List <SalesSummaryViewModel> listofSales = new List <SalesSummaryViewModel>();
TransRecievablesViewModel t = new TransRecievablesViewModel();
TransactionDetailsViewModel transdetails = new TransactionDetailsViewModel();
PegasusEntities db_entities = new PegasusEntities();
try
{
IEnumerable <Booking> bookings = (from booking in db_entities.Bookings select booking).ToList();
listofSales = (from b in bookings join p in db_entities.Payments on b.trn_Id equals p.trn_Id
select new SalesSummaryViewModel()
{
transId = b.trn_Id,
accountname = Utilities.getfullname(b.Customer.lastname, b.Customer.firstname, b.Customer.middle),
dateTrans = Convert.ToDateTime(p.dateofPayment),
reference = p.particular,
particular = b.Package.p_descripton,
CashSales = Convert.ToDecimal(p.amtPay),
OnAccount = transdetails.GetTotalBookingAmount(b.trn_Id) - transdetails.GetTotalPaymentByTrans(b.trn_Id)
}).ToList();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(listofSales.ToList());
}
public IEnumerable <MainMenuListViewModel> ListofMainMenu()
{
List <MainMenuListViewModel> mainmenulist = new List <MainMenuListViewModel>();
var _dbentities = new PegasusEntities();
try
{
mainmenulist = (from m in _dbentities.Menus
join c in _dbentities.CourseCategories
on m.CourserId equals c.CourserId
select new MainMenuListViewModel()
{
menuId = m.menuid,
menu_name = m.menu_name,
course = c.Course,
isMainMenu = c.Main_Bol
}).OrderBy(x => x.course).ToList();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(mainmenulist);
}
public IEnumerable <PrintContractDetailsMenuAddonsViewModel> GetAllMenuAddons(int transId)
{
PegasusEntities dbEntities = new PegasusEntities();
List <PrintContractDetailsMenuAddonsViewModel> list = new List <PrintContractDetailsMenuAddonsViewModel>();
var listdummy = (from b in dbEntities.Bookings
join m in dbEntities.Book_Menus on b.trn_Id equals m.trn_Id
join ad in dbEntities.BookingAddons on b.trn_Id equals ad.trn_Id
join ma in dbEntities.Menus on m.menuid equals ma.menuid
where b.trn_Id == transId
select new
{
tId = b.trn_Id,
menu = ma.menu_name,
addOns = ad.Addondesc
}).ToList();
foreach (var item in listdummy)
{
list.Add(new PrintContractDetailsMenuAddonsViewModel
{
transId = item.tId,
menu = item.menu,
addOns = item.addOns
});
}
return(list);
}
public static string EventSlip_Generator()
{
int id = 0;
var dbEntities = new PegasusEntities();
try
{
var seriesParam = new SqlParameter
{
ParameterName = "series",
DbType = DbType.Int32,
Direction = ParameterDirection.Output
};
var seriesId = dbEntities.Database.SqlQuery <int>("exec Generate_EventSlip @series out", seriesParam).FirstOrDefault();
id = Convert.ToInt32(seriesId);
}
catch (NullReferenceException)
{
id = id + 1;
}
catch (FormatException)
{
id = id + 1;
}
return(string.Format("{0:0000}", id));
}
public static int GetTotalLackingMenus(int pid, int transid)
{
var dbcontext = new PegasusEntities();
var packagemenucount = (from p in dbcontext.Packages where p.p_id == pid
join pb in dbcontext.PackageBodies on p.p_id equals pb.p_id
select new
{
courseid = pb.courseId
}).Count();
var intmenusselected = (from bm in dbcontext.Book_Menus where bm.trn_Id == transid
join m in dbcontext.Menus on bm.menuid equals m.menuid
select new
{
_menu = bm.menuid
}).Count();
int count = packagemenucount - intmenusselected;
if (count < 0)
{
count = 0;
}
return(count);
}
public IEnumerable <ReservationViewModel> GetAll_Reservations()
{
List <ReservationViewModel> listreservations = new List <ReservationViewModel>();
PegasusEntities dbEntities = new PegasusEntities();
try
{
var list = (from r in dbEntities.Reservations select r).ToList();
listreservations = (from s in list
select new ReservationViewModel()
{
reservationId = s.resId,
customerId = s.c_Id,
fullname = Utilities.getfullname(s.Customer.lastname, s.Customer.firstname, s.Customer.middle),
noofperson = s.noofPax,
occasion = s.occasion,
reserveDate = s.resDate,
eventVenue = s.eventVenue,
resStat = s.reserveStat
}).ToList();
}
catch (Exception)
{
throw;
}
return(listreservations.OrderBy(x => x.reserveDate));
}
public IEnumerable <PaymentsViewModel> GetPaymentDetaiilsBooking(int transId)
{
var _dbcontext = new PegasusEntities();
List <PaymentsViewModel> paylist = new List <PaymentsViewModel>();
try
{
paylist = (from p in _dbcontext.Payments
where p.trn_Id == transId
select new PaymentsViewModel()
{
PayNo = p.payNo,
transId = p.trn_Id,
dateofPayment = p.dateofPayment,
particular = p.particular,
payType = p.payType,
amtPay = p.amtPay,
pay_means = p.pay_means,
notes = p.notes
}).OrderBy(x => x.PayNo).ToList();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(paylist.ToList());
}
//=== check if has transaction on list of book menus
public bool isCoursehasBooking(int courseId)
{
bool hasexistingbook = false;
using (var dbentities = new PegasusEntities())
{
var list = (from b in dbentities.Bookings
join bm in dbentities.Book_Menus on b.trn_Id equals bm.trn_Id
join m in dbentities.Menus on bm.menuid equals m.menuid
join c in dbentities.CourseCategories on m.CourserId equals c.CourserId
where c.CourserId == courseId
select new
{
courseId = c.CourserId
}).ToList();
if (list.Any())
{
hasexistingbook = true;
}
}
return(hasexistingbook);
}
public List <BookingsViewModel> GetListofBookings()
{
var _entities = new PegasusEntities();
// _entities.Configuration.ProxyCreationEnabled = false;
List <Booking> bookings = new List <Booking>();
List <BookingsViewModel> bookingdetails = new List <BookingsViewModel>();
var context = new ApplicationUser.ApplicationDbContext();
try
{
bookings = (from c in _entities.Bookings select c).ToList();
bookingdetails = (from b in bookings
let firstOrDefault = context.Users.FirstOrDefault(x => x.Id == b.b_createdbyUser)
where firstOrDefault != null
join s in _entities.ServiceTypes on b.typeofservice equals s.serviceId
select new BookingsViewModel
{
trn_Id = b.trn_Id,
c_Id = b.c_Id,
noofperson = b.noofperson,
occasion = b.occasion,
packagename = b.Package.p_descripton,
packageType = b.Package.p_type.Trim(),
amoutperPax = Convert.ToDecimal(b.Package.p_amountPax),
venue = b.venue,
typeofservice = b.typeofservice,
startdate = b.startdate,
enddate = b.enddate,
transdate = b.transdate,
serve_status = b.serve_stat,
serviceType = s.servicetypedetails,
eventcolor = b.eventcolor,
pId = Convert.ToInt32(b.p_id),
fullname = Utilities.getfullname(b.Customer.lastname, b.Customer.firstname, b.Customer.middle),
b_createdbyUser = b.b_createdbyUser,
b_createdbyUserName = firstOrDefault.UserName,
refernce = b.reference,
apply_extendedAmount = (bool)b.apply_extendedAmount,
b_updatedDate = Convert.ToDateTime(b.b_updatedDate),
iscancelled = Convert.ToBoolean(b.is_cancelled),
booktypecode = !string.Equals(b.booktype, null, StringComparison.Ordinal) ?b.booktype:"",
no_of_lackingMenus = BookMenusViewModel.GetTotalLackingMenus(Convert.ToInt32(b.p_id), b.trn_Id)
}).ToList();
//}).Where(x=>x.serve_status==false).OrderBy(d => d.startdate).ToList();
}
catch (EntityCommandExecutionException eceex)
{
if (eceex.InnerException != null)
{
throw eceex.InnerException;
}
throw;
}
return(bookingdetails.ToList());
}
public ActionResult ModifyPackageLocation(int packageId, int p_area_No)
{
var dbentities = new PegasusEntities();
PackageAreaLocationViewModel p_area = new PackageAreaLocationViewModel();
var packageareacoverage = dbentities.PackageAreaCoverages.FirstOrDefault(p => p.p_areaNo == p_area_No);
if (packageareacoverage != null)
{
p_area = new PackageAreaLocationViewModel()
{
p_id = packageId,
p_areaNo = p_area_No,
aID = Convert.ToInt32(packageareacoverage.aID),
is_extended = Convert.ToBoolean(packageareacoverage.is_extended),
ext_amount = Convert.ToDecimal(packageareacoverage.ext_amount
)
//AreasSelectList = packageArea.GetArea_SelectListItems()
};
var arealoc = (dbentities.PackageAreaCoverages.Join(dbentities.Areas, p => p.aID, pa => pa.aID,
(p, pa) => new { areano = p.p_areaNo, area_detail = pa.AreaDetails }))
.ToList().FirstOrDefault(x => x.areano == p_area_No);
if (arealoc != null)
{
p_area.areadeatails = arealoc.area_detail;
}
}
return(PartialView("_modifypackagelocationCoverage", p_area));
}
public ActionResult RecievableprintOption(int?cusId, string selprintopt)
{
string url = null;
var dbcontext = new PegasusEntities();
var pOption = new PrintOptionViewModel()
{
Id = Convert.ToInt32(cusId),
selPrintOpt = selprintopt,
Customer = (from c in dbcontext.Customers select c).FirstOrDefault(c => c.c_Id == cusId)
};
if (selprintopt == "accnrecievesummary")
{
url = "~/Views/Shared/ReportContainer.cshtml";
}
else
{
ViewBag.CusId = pOption.Id;
//return PartialView("_ClientRecievablesPartialView");
url = "AccountRecieveCustomer";
}
return(View(url, pOption));
}
public IEnumerable <AddonsViewModel> ListofAddons()
{
List <AddonsViewModel> list = new List <AddonsViewModel>();
var _dbentities = new PegasusEntities();
try
{
list = (from a in _dbentities.BookingAddons
select new AddonsViewModel
{
No = a.No,
TransId = (int)a.trn_Id,
AddonsDescription = a.Addondesc,
AddonAmount = (decimal)a.AddonAmount,
AddonNote = a.Note
}).ToList();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(list.ToList());
}
public IEnumerable <CustomerBookingsViewModel> getAllReservationBookings()
{
var _dbEntities = new PegasusEntities();
List <CustomerBookingsViewModel> lst = new List <CustomerBookingsViewModel>();
List <Reservation> reservation = new List <Reservation>();
try
{
reservation = (from res in _dbEntities.Reservations select res).ToList();
lst = (from r in reservation
select new CustomerBookingsViewModel()
{
transId = r.resId,
cusId = r.c_Id,
cusfullname = Utilities.getfullname_nonreverse(r.Customer.lastname, r.Customer.firstname, r.Customer.middle),
occasion = r.occasion,
venue = r.eventVenue,
bookdatetime = r.resDate,
package = "None",
transType = "res"
}).ToList();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(lst);
}
public decimal getTotalAddons(int transId)
{
var _dbcontext = new PegasusEntities();
var addonsList = _dbcontext.BookingAddons.Where(x => x.trn_Id == transId).ToList();
return(Convert.ToDecimal(addonsList.Sum(x => x.AddonAmount)));
}
public int getTodateBookingSchedule()
{
dbEntities = new PegasusEntities();
DateTime now = DateTime.Now;
return(dbEntities.Bookings.Where(x => DbFunctions.TruncateTime(x.startdate.Value) == DbFunctions.TruncateTime(now)).ToList().Count != 0 ? dbEntities.Bookings.Where(x => DbFunctions.TruncateTime(x.startdate.Value) == DbFunctions.TruncateTime(now)).ToList().Count : 0);
//return dbEntities.Bookings.Count() != 0 ? dbEntities.Bookings.Count() : 0;
}
public IEnumerable <BookMenusViewModel> GetLackingMenus(int transId)
{
List <BookMenusViewModel> list = new List <BookMenusViewModel>();
var dbentities = new PegasusEntities();
return(list);
}
public IEnumerable <SelectListItem> GetServiceType_SelectListItems()
{
var _entities = new PegasusEntities();
var servicetypeList = _entities.ServiceTypes.AsEnumerable().Select(x => new SelectListItem
{
Value = x.serviceId.ToString(),
Text = x.servicetypedetails
}).ToList();
return(new SelectList(servicetypeList, "Value", "Text"));
}
public IEnumerable <SelectListItem> Get_SelectListAddonCat()
{
var dbentities = new PegasusEntities();
var addoncatselectlist = dbentities.AddonCategories.AsEnumerable().Select(x => new SelectListItem()
{
Value = x.addoncatId.ToString(),
Text = x.addoncatdesc
});
return(new SelectList(addoncatselectlist, "Value", "Text"));
}
public IEnumerable <SelectListItem> GetPackageNoofPaxListItems()
{
var dbcontext = new PegasusEntities();
var packagenoofpax = dbcontext.Packages_No_Pax_applicable.AsEnumerable().Select(x => new SelectListItem
{
Value = x.nopax_id.ToString(),
Text = x.package_n_pax
}
);
return(new SelectList(packagenoofpax, "Value", "Text"));
}
public int getThisMonthReservation()
{
dbEntities = new PegasusEntities();
DateTime now = DateTime.Now;
DateTime startDayofMonth = new DateTime(now.Year, now.Month, 1);
DateTime endDayofMonth = startDayofMonth.AddMonths(1).AddDays(-1);
int totalreservechedulethisMonth = dbEntities.Reservations
.Where(x => DbFunctions.TruncateTime(x.resDate) >= DbFunctions.TruncateTime(startDayofMonth) && DbFunctions.TruncateTime(x.resDate) <= DbFunctions.TruncateTime(endDayofMonth)).ToList().Count;
return(totalreservechedulethisMonth);
}
public IEnumerable <SelectedAddonsViewModel> GetSelectedAddons()
{
var dbconext = new PegasusEntities();
var list = (from b in dbconext.BookingAddons select new SelectedAddonsViewModel()
{
bookingNo = (int)b.trn_Id,
addonId = (int)b.addonId,
addondetails = b.Addondesc
});
return(list);
}
private int get_thisMonthBookings()
{
dbEntities = new PegasusEntities();
DateTime now = DateTime.Now;
DateTime startDayofMonth = new DateTime(now.Year, now.Month, 1);
DateTime endDayofMonth = startDayofMonth.AddMonths(1).AddDays(-1);
int totalbookthisMonth = dbEntities.Bookings
.Where(x => DbFunctions.TruncateTime(x.transdate) >= DbFunctions.TruncateTime(startDayofMonth) && DbFunctions.TruncateTime(x.transdate) <= DbFunctions.TruncateTime(endDayofMonth)).ToList().Count;
return(totalbookthisMonth);
}
public IEnumerable <SelectListItem> GetListofDiscount()
{
using (var dbentities = new PegasusEntities())
{
var listofdiscode = dbentities.Discounts.AsEnumerable().Select(x => new SelectListItem
{
Value = x.disc_Id.ToString(),
Text = x.discCode
}).ToList();
return(new SelectList(listofdiscode, "Value", "Text"));
}
}
public int getThisWeekReservation()
{
dbEntities = new PegasusEntities();
//DateTime firstday = DateTime.Now.AddDays(-(int) DateTime.Now.DayOfWeek);
DateTime startDayofWeek = DateTime.Today.AddDays(-1 * (int)DateTime.Today.DayOfWeek);
DateTime endDayofWeek = DateTime.Today.AddDays(6 - (int)DateTime.Today.DayOfWeek);
int totalreserveschedulethisWeek = dbEntities.Reservations
.Where(x => DbFunctions.TruncateTime(x.resDate) >= DbFunctions.TruncateTime(startDayofWeek) && DbFunctions.TruncateTime(x.resDate) <= DbFunctions.TruncateTime(endDayofWeek)).ToList().Count;
return(totalreserveschedulethisWeek);
}
public int getThisWeekBookingSchedule()
{
dbEntities = new PegasusEntities();
//DateTime firstday = DateTime.Now.AddDays(-(int) DateTime.Now.DayOfWeek);
DateTime startDayofWeek = DateTime.Today.AddDays(-1 * (int)DateTime.Today.DayOfWeek);
DateTime endDayofWeek = DateTime.Today.AddDays(6 - (int)DateTime.Today.DayOfWeek);
int totalbookschedulethisWeek = dbEntities.Bookings
.Where(x => DbFunctions.TruncateTime(x.startdate.Value) >= DbFunctions.TruncateTime(startDayofWeek) && DbFunctions.TruncateTime(x.startdate.Value) <= DbFunctions.TruncateTime(endDayofWeek)).ToList().Count;
return(totalbookschedulethisWeek);
}
public IEnumerable <PackageBodyViewModel> GetAllMenusinPackage()
{
var context = new PegasusEntities();
var listofmenusselected = (from pb in context.PackageBodies join cc in context.CourseCategories on pb.courseId equals cc.CourserId
select new PackageBodyViewModel()
{
package_Id = (int)pb.p_id,
pbodyKey = pb.No,
pbodycourseid = (int)pb.courseId,
pbodycoursename = cc.Course
}).ToList();
return(listofmenusselected);
}
public int Get_PackageMainMenusInt(int packageId)
{
int x = 0;
var dbentities = new PegasusEntities();
var list = (from p in dbentities.PackageBodies
join cc in dbentities.CourseCategories on p.courseId equals cc.CourserId
where p.p_id == packageId && cc.Main_Bol == true
select new { mainmenus = cc.Course }).ToList();
x = list.Select(c => c.mainmenus).Count();
return(x);
}
public IEnumerable <AddonCatViewModel> GetListofAddonCategories()
{
var dbentities = new PegasusEntities();
List <AddonCatViewModel> list = new List <AddonCatViewModel>();
list = (from a in dbentities.AddonCategories
select new AddonCatViewModel()
{
addoncatId = a.addoncatId,
addoncatdetails = a.addoncatdesc
}).ToList();
return(list);
}
public IEnumerable <PackageBodyViewModel> DisplayPackageBody()
{
var dbcontext = new PegasusEntities();
var packagebody = (from pb in dbcontext.PackageBodies
join cc in dbcontext.CourseCategories on pb.courseId equals cc.CourserId
select new PackageBodyViewModel()
{
pbodyKey = pb.No,
package_Id = (int)pb.p_id,
pbodycoursename = cc.Course,
pbodycourseid = (int)pb.courseId
}).ToList();
return(packagebody);
}
