public static void UpdateProperty(Property property)
{
using (var hdc = new HotelDataEntryDataContext())
{
var prop = hdc.Properties.Single(item => item.PropertyId == property.PropertyId);
prop.PropertyName = property.PropertyName;
prop.PropertyCode = property.PropertyCode;
prop.CurrencyId = property.CurrencyId;
prop.Status = property.Status;
prop.UpdateDateTime = DateTime.Now;
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static List<Reports> BudgetReport(HotelBudget hBudget)
{
var hdc = new HotelDataEntryDataContext();
List<Reports> list = null;
list = (from hotelBudget in hdc.HotelBudgets
join budgetEntry in hdc.BudgetEntries on hotelBudget.HotelBudgetId equals budgetEntry.HotelBudgetId
where hotelBudget.PropertyId == hBudget.PropertyId && hotelBudget.Year == hBudget.Year
orderby budgetEntry.BudgetId
select new Reports()
{
BudgetId = budgetEntry.BudgetId,
MonthYear = budgetEntry.PositionMonth,
OccupancyRoomBudget = budgetEntry.OccupancyRoom,
FBBudget = budgetEntry.FBBudget,
SpaBudget = budgetEntry.SpaBudget,
RoomBudget = budgetEntry.RoomBudget,
OtherBudget = budgetEntry.Others,
OccupancyRoomActual = 0,
FBActual = 0.00,
RoomActual = 0.00,
SpaActual = 0.00,
OtherActual = 0.00
}).ToList();
return list;
}
public static void AddCurrency(Currency currency)
{
if (currency.IsBase == 1)
{
UpdateIsBaseCurrency();
}
using (var hdc = new HotelDataEntryDataContext())
{
hdc.Currencies.InsertOnSubmit(new HotelDataEntryLib.Currency
{
CurrencyCode = currency.CurrencyCode,
CurrencyName = currency.CurrencyName,
UpdateDateTime = DateTime.Now,
Status = currency.Status,
ConversionRate = currency.ConversionRate,
IsBase = currency.IsBase
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void UpdateCurrency(Currency currency)
{
if (currency.IsBase == 1)
{
UpdateIsBaseCurrency();
}
using (var hdc = new HotelDataEntryDataContext())
{
var cur = hdc.Currencies.Single(item => item.CurrencyId == currency.CurrencyId);
cur.CurrencyName = currency.CurrencyName;
cur.CurrencyCode = currency.CurrencyCode;
cur.Status = currency.Status;
cur.ConversionRate = currency.ConversionRate;
cur.UpdateDateTime = DateTime.Now;
cur.IsBase = currency.IsBase;
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void AddBudgetEntryListByYear(HotelBudget hotelEntry, string username)
{
using (var hdc = new HotelDataEntryDataContext())
{
for (var i = 0; i < 12; i++)
{
hdc.BudgetEntries.InsertOnSubmit(new BudgetEntry()
{
HotelBudgetId = hotelEntry.HotelBudgetId,
OccupancyRoom = 0,
RoomBudget = 0.00,
FBBudget = 0.00,
SpaBudget = 0.00,
Others = 0.00,
Total = 0.00,
UpdateDateTime = DateTime.Now,
UpdateUser = username,
PositionMonth = (i+1)+"/"+hotelEntry.Year
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
}
public static void AddProperty(Property property)
{
using (var hdc = new HotelDataEntryDataContext())
{
hdc.Properties.InsertOnSubmit(new Property
{
PropertyCode = property.PropertyCode,
PropertyName = property.PropertyName,
CurrencyId = property.CurrencyId,
UpdateDateTime = DateTime.Now,
Status = property.Status
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void AddProperty(Property property)
{
using (var hdc = new HotelDataEntryDataContext())
{
hdc.Properties.InsertOnSubmit(new Property
{
PropertyCode = property.PropertyCode,
PropertyName = property.PropertyName,
CurrencyId = property.CurrencyId,
UpdateDateTime = DateTime.Now,
Status = property.Status
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void UpdateBudgetEntry(BudgetEntry budgetEntry)
{
using (var hdc = new HotelDataEntryDataContext())
{
try
{
var entry = hdc.BudgetEntries.Single(item => item.BudgetId == budgetEntry.BudgetId);
entry.OccupancyRoom = budgetEntry.OccupancyRoom;
entry.RoomBudget = budgetEntry.RoomBudget;
entry.FBBudget = budgetEntry.FBBudget;
entry.SpaBudget = budgetEntry.SpaBudget;
entry.Others = budgetEntry.Others;
entry.Total = budgetEntry.Total;
entry.UpdateDateTime = DateTime.Now;
entry.UpdateUser = budgetEntry.UpdateUser;
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void StoreError(string errMsg, string stacktrace, string Url)
{
try
{
using (var hdc = new HotelDataEntryDataContext())
{
hdc.Logs.InsertOnSubmit(new HotelDataEntryLib.Log()
{
ErrorDate = DateTime.Now,
ClientIP = HttpContext.Current.Request.UserHostAddress,
Detail = stacktrace,
Message = errMsg,
Url = Url
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static HotelDataEntryLib.HotelRevenue AddHotelEntryListByMonthYear(HotelRevenue hotelEntry)
{
HotelRevenue hotelEntrySubmit;
using (var hdc = new HotelDataEntryDataContext())
{
hotelEntrySubmit = new HotelRevenue()
{
PropertyId = hotelEntry.PropertyId,
Month = hotelEntry.Month,
Year = hotelEntry.Year,
UpdateDateTime = DateTime.Now
};
hdc.HotelRevenues.InsertOnSubmit(hotelEntrySubmit);
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
return(hotelEntrySubmit);
}
public static List <Reports> BudgetReport(HotelBudget hBudget)
{
var hdc = new HotelDataEntryDataContext();
List <Reports> list = null;
list = (from hotelBudget in hdc.HotelBudgets
join budgetEntry in hdc.BudgetEntries on hotelBudget.HotelBudgetId equals budgetEntry.HotelBudgetId
where hotelBudget.PropertyId == hBudget.PropertyId && hotelBudget.Year == hBudget.Year
orderby budgetEntry.BudgetId
select new Reports()
{
BudgetId = budgetEntry.BudgetId,
MonthYear = budgetEntry.PositionMonth,
OccupancyRoomBudget = budgetEntry.OccupancyRoom,
FBBudget = budgetEntry.FBBudget,
SpaBudget = budgetEntry.SpaBudget,
RoomBudget = budgetEntry.RoomBudget,
OtherBudget = budgetEntry.Others,
OccupancyRoomActual = 0,
FBActual = 0.00,
RoomActual = 0.00,
SpaActual = 0.00,
OtherActual = 0.00
}).ToList();
return(list);
}
public static void AddCurrency(Currency currency)
{
if (currency.IsBase == 1)
{
UpdateIsBaseCurrency();
}
using (var hdc = new HotelDataEntryDataContext())
{
hdc.Currencies.InsertOnSubmit(new HotelDataEntryLib.Currency
{
CurrencyCode = currency.CurrencyCode,
CurrencyName = currency.CurrencyName,
UpdateDateTime = DateTime.Now,
Status = currency.Status,
ConversionRate = currency.ConversionRate,
IsBase = currency.IsBase
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static List <BudgetEntry> ListBudgetEntryByYear(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var revenueEntryList = hdc.BudgetEntries.Where(item => item.HotelBudgetId == hotelEntry.HotelBudgetId).ToList();
return(revenueEntryList);
}
public static bool ExistMothYear(HotelRevenue hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var count = hdc.HotelRevenues.Count(item => item.Month == hotelEntry.Month && item.Year == hotelEntry.Year && item.PropertyId == hotelEntry.PropertyId);
return(count != 0);
}
public static void AddBudgetEntryListByYear(HotelBudget hotelEntry, string username)
{
using (var hdc = new HotelDataEntryDataContext())
{
for (var i = 0; i < 12; i++)
{
hdc.BudgetEntries.InsertOnSubmit(new BudgetEntry()
{
HotelBudgetId = hotelEntry.HotelBudgetId,
OccupancyRoom = 0,
RoomBudget = 0.00,
FBBudget = 0.00,
SpaBudget = 0.00,
Others = 0.00,
Total = 0.00,
UpdateDateTime = DateTime.Now,
UpdateUser = username,
PositionMonth = (i + 1) + "/" + hotelEntry.Year
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
}
public static IEnumerable <Report> CalculateYearlyReport(DateTime dateFrom, DateTime dateTo, int propertyId)
{
var hdc = new HotelDataEntryDataContext();
IEnumerable <Report> list = null;
//list = (from hotelEntry in hdc.HotelEntries
// join dataEntry in hdc.DataEntries on hotelEntry.HotelEntryId equals dataEntry.HotelEntryId
// join dataSubEntryType in hdc.DataEntrySubTypes on hotelEntry.DataEntrySubTypeId equals dataSubEntryType.DataEntrySubTypeId
// join dataEntryType in hdc.DataEntryTypes on dataSubEntryType.DataEntryTypeId equals dataEntryType.DataEntryTypeId
// where hotelEntry.PropertyId == propertyId
// && dataEntry.PositionDate >= dateFrom
// && dataEntry.PositionDate<=dateTo
// group new { dataEntry, dataSubEntryType, dataEntryType } by new
// {
// dataEntryType.DataEntryTypeName,
// dataSubEntryType.DataEntrySubTypeName
// }into g
// orderby g.Key.DataEntryTypeName
// select new Report()
// {
// Type = g.Key.DataEntryTypeName,
// SubType = g.Key.DataEntrySubTypeName,
// BudgetTY = g.Sum(item => item.dataEntry.Budget)
// }).ToList();
return(list);
}
public static IEnumerable<Report> CalculateYearlyReport(DateTime dateFrom, DateTime dateTo, int propertyId)
{
var hdc = new HotelDataEntryDataContext();
IEnumerable<Report> list = null;
//list = (from hotelEntry in hdc.HotelEntries
// join dataEntry in hdc.DataEntries on hotelEntry.HotelEntryId equals dataEntry.HotelEntryId
// join dataSubEntryType in hdc.DataEntrySubTypes on hotelEntry.DataEntrySubTypeId equals dataSubEntryType.DataEntrySubTypeId
// join dataEntryType in hdc.DataEntryTypes on dataSubEntryType.DataEntryTypeId equals dataEntryType.DataEntryTypeId
// where hotelEntry.PropertyId == propertyId
// && dataEntry.PositionDate >= dateFrom
// && dataEntry.PositionDate<=dateTo
// group new { dataEntry, dataSubEntryType, dataEntryType } by new
// {
// dataEntryType.DataEntryTypeName,
// dataSubEntryType.DataEntrySubTypeName
// }into g
// orderby g.Key.DataEntryTypeName
// select new Report()
// {
// Type = g.Key.DataEntryTypeName,
// SubType = g.Key.DataEntrySubTypeName,
// BudgetTY = g.Sum(item => item.dataEntry.Budget)
// }).ToList();
return list;
}
public static HotelBudget GetHotelEntry(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var hEntry = hdc.HotelBudgets.Single(item => item.Year == hotelEntry.Year && item.PropertyId == hotelEntry.PropertyId);
return(hEntry);
}
public static bool ExistYear(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var count = hdc.HotelBudgets.Count(item => item.Year == hotelEntry.Year && item.PropertyId == hotelEntry.PropertyId);
return(count != 0);
}
public static void UpdateUserProfile(User user)
{
using (var hdc = new HotelDataEntryDataContext())
{
var getUser = (from dataItem in hdc.Users
where dataItem.UserId == user.UserId
select dataItem).First();
getUser.FirstName = user.FirstName;
getUser.LastName = user.LastName;
getUser.Email = user.Email;
getUser.PropertyId = user.PropertyId;
getUser.UpdateDateTime = DateTime.Now;
getUser.PermissionId = user.PermissionId;
getUser.Status = user.Status;
getUser.Position = user.Position;
getUser.AccessProperties = user.AccessProperties;
getUser.Username = user.Username;
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static HotelRevenue GetHotelEntry(HotelRevenue hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var hEntry = hdc.HotelRevenues.Single(item => item.Month == hotelEntry.Month && item.Year == hotelEntry.Year && item.PropertyId == hotelEntry.PropertyId);
return(hEntry);
}
public static void UpdateBudgetEntry(BudgetEntry budgetEntry)
{
using (var hdc = new HotelDataEntryDataContext())
{
try
{
var entry = hdc.BudgetEntries.Single(item => item.BudgetId == budgetEntry.BudgetId);
entry.OccupancyRoom = budgetEntry.OccupancyRoom;
entry.RoomBudget = budgetEntry.RoomBudget;
entry.FBBudget = budgetEntry.FBBudget;
entry.SpaBudget = budgetEntry.SpaBudget;
entry.Others = budgetEntry.Others;
entry.Total = budgetEntry.Total;
entry.UpdateDateTime = DateTime.Now;
entry.UpdateUser = budgetEntry.UpdateUser;
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void StoreError(string errMsg, string stacktrace, string Url)
{
try
{
using (var hdc = new HotelDataEntryDataContext())
{
hdc.Logs.InsertOnSubmit(new HotelDataEntryLib.Log()
{
ErrorDate = DateTime.Now,
ClientIP = HttpContext.Current.Request.UserHostAddress,
Detail = stacktrace,
Message = errMsg,
Url = Url
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void UpdateCurrency(Currency currency)
{
if (currency.IsBase == 1)
{
UpdateIsBaseCurrency();
}
using (var hdc = new HotelDataEntryDataContext())
{
var cur = hdc.Currencies.Single(item => item.CurrencyId == currency.CurrencyId);
cur.CurrencyName = currency.CurrencyName;
cur.CurrencyCode = currency.CurrencyCode;
cur.Status = currency.Status;
cur.ConversionRate = currency.ConversionRate;
cur.UpdateDateTime = DateTime.Now;
cur.IsBase = currency.IsBase;
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static List<HotelRevenue> GetHotelRevenueList(HotelBudget hotelBudget)
{
var hdc = new HotelDataEntryDataContext();
return
hdc.HotelRevenues.Where(
item => item.Year == hotelBudget.Year && item.PropertyId == hotelBudget.PropertyId).ToList();
}
public static HotelDataEntryLib.HotelBudget AddHotelEntryListByYear(HotelBudget hotelEntry)
{
HotelBudget hotelEntrySubmit;
using (var hdc = new HotelDataEntryDataContext())
{
hotelEntrySubmit = new HotelBudget()
{
PropertyId = hotelEntry.PropertyId,
Year =hotelEntry.Year,
UpdateDateTime = DateTime.Now
};
hdc.HotelBudgets.InsertOnSubmit(hotelEntrySubmit);
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
return hotelEntrySubmit;
}
public static void UpdateRevenueEntry(RevenueEntry revenueEntry)
{
using (var hdc = new HotelDataEntryDataContext())
{
try
{
var entry = hdc.RevenueEntries.Single(item => item.RevenueId == revenueEntry.RevenueId);
entry.OccupancyRoom = revenueEntry.OccupancyRoom;
entry.FBRevenue = revenueEntry.FBRevenue;
entry.SpaRevenue = revenueEntry.SpaRevenue;
entry.RoomRevenue = revenueEntry.RoomRevenue;
entry.Others = revenueEntry.Others;
entry.Total = revenueEntry.Total;
entry.UpdateDateTime = DateTime.Now;
entry.UpdateUser = revenueEntry.UpdateUser;
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static List <HotelDataEntryLib.Helper.Revenue> ListRevenueEntryByMonthYear(HotelRevenue hotelEntry)
{
var dates = GetLastDayOfMonth(hotelEntry.Month, hotelEntry.Year);
var positionMonth = hotelEntry.Month + "/" + hotelEntry.Year;
var hdc = new HotelDataEntryDataContext();
var list = (from revenueEntry in hdc.RevenueEntries
join hotelRevenue in hdc.HotelRevenues on revenueEntry.HotelRevenueId equals hotelRevenue.HotelRevenueId
join hotelBudget in hdc.HotelBudgets on new { hotelRevenue.Year, hotelRevenue.PropertyId } equals new { hotelBudget.Year, hotelBudget.PropertyId }
join budgetEntry in hdc.BudgetEntries on hotelBudget.HotelBudgetId equals budgetEntry.HotelBudgetId
where revenueEntry.HotelRevenueId == hotelEntry.HotelRevenueId &&
budgetEntry.PositionMonth == positionMonth
orderby revenueEntry.PositionDate
select new Revenue()
{
RevenueId = revenueEntry.RevenueId,
PositionDate = revenueEntry.PositionDate,
HotelRevenueId = revenueEntry.HotelRevenueId,
OccupancyRoom = revenueEntry.OccupancyRoom,
RoomRevenue = revenueEntry.RoomRevenue,
FBRevenue = revenueEntry.FBRevenue,
SpaRevenue = revenueEntry.SpaRevenue,
Others = revenueEntry.Others,
Total = revenueEntry.Total,
Budget = revenueEntry.Total <= 0?0:budgetEntry.Total / dates,
Day = revenueEntry.PositionDate.DayOfWeek.ToString(),
UpdateDateTime = revenueEntry.UpdateDateTime,
DateNowMillisecond = DateTime.Now.Ticks,
UpdateDateTimeMillisecond = Convert.ToDateTime(revenueEntry.UpdateDateTime).Ticks
}).ToList();
return(list);
}
public static List <Property> Properites()
{
using (var hdc = new HotelDataEntryDataContext())
{
return(hdc.Properties.OrderBy(item => item.PropertyCode).ToList());
}
}
public static List <HotelRevenue> GetHotelRevenueList(HotelBudget hotelBudget)
{
var hdc = new HotelDataEntryDataContext();
return
(hdc.HotelRevenues.Where(
item => item.Year == hotelBudget.Year && item.PropertyId == hotelBudget.PropertyId).ToList());
}
public static List<Permission> ListPermissions()
{
using (var hdc = new HotelDataEntryDataContext())
{
var listPermissions = new List<Permission> { new Permission() { PermissionId = 0, PermissionName = "Select a permission", PermissionCode = 0 } };
listPermissions.AddRange(hdc.Permissions.ToList());
return listPermissions;
}
}
public static bool IsUserExist(string username)
{
var user = false;
using (var hdc = new HotelDataEntryDataContext())
{
var count = hdc.Users.Count(item => item.Username == username);
if (count != 0)
{
user = true;
}
}
return user;
}
public static User GetUser(string username)
{
var user = new User();
using(var hdc = new HotelDataEntryDataContext())
{
var count = hdc.Users.Count(item => item.Username == username);
if(count !=0)
{
user = hdc.Users.Single(item => item.Username == username);
}
}
return user;
}
public static Permission GetPermission(int permissionId)
{
var permission = new Permission();
using (var hdc = new HotelDataEntryDataContext())
{
var count = hdc.Permissions.Count(item => item.PermissionId == permissionId);
if (count != 0)
{
permission = hdc.Permissions.Single(item => item.PermissionId == permissionId);
}
}
return permission;
}
public static bool IsUserExist(string username)
{
var user = false;
using (var hdc = new HotelDataEntryDataContext())
{
var count = hdc.Users.Count(item => item.Username == username);
if (count != 0)
{
user = true;
}
}
return(user);
}
public static User GetUser(string username)
{
var user = new User();
using (var hdc = new HotelDataEntryDataContext())
{
var count = hdc.Users.Count(item => item.Username == username);
if (count != 0)
{
user = hdc.Users.Single(item => item.Username == username);
}
}
return(user);
}
public static List <Property> ListProperites()
{
using (var hdc = new HotelDataEntryDataContext())
{
var listCompany = new List <Property> {
new Property()
{
PropertyId = 0, PropertyName = "Select a Property", PropertyCode = "Select a Property"
}
};
listCompany.AddRange(hdc.Properties.ToList());
return(listCompany);
}
}
public static List <Permission> ListPermissions()
{
using (var hdc = new HotelDataEntryDataContext())
{
var listPermissions = new List <Permission> {
new Permission()
{
PermissionId = 0, PermissionName = "Select a permission", PermissionCode = 0
}
};
listPermissions.AddRange(hdc.Permissions.ToList());
return(listPermissions);
}
}
public static Permission GetPermission(int permissionId)
{
var permission = new Permission();
using (var hdc = new HotelDataEntryDataContext())
{
var count = hdc.Permissions.Count(item => item.PermissionId == permissionId);
if (count != 0)
{
permission = hdc.Permissions.Single(item => item.PermissionId == permissionId);
}
}
return(permission);
}
public static IEnumerable <object> ListAllProperties()
{
var hdc = new HotelDataEntryDataContext();
IEnumerable <object> listCompany = null;
listCompany = (from company in hdc.Properties
join currency in hdc.Currencies on company.CurrencyId equals currency.CurrencyId
select new
{
company.PropertyId,
company.PropertyName,
company.PropertyCode,
company.StatusLabel,
currency.CurrencyCode
}).ToList();
return(listCompany);
}
public static List<Budget> GetAllPropertyByHotelBudget(int year)
{
var hdc = new HotelDataEntryDataContext();
var list = (from property in hdc.Properties
join hotelBudget in hdc.HotelBudgets on property.PropertyId equals hotelBudget.PropertyId
join currency in hdc.Currencies on property.CurrencyId equals currency.CurrencyId
where hotelBudget.Year == year
orderby property.PropertyCode
select new Budget()
{
HotelBudgetId = hotelBudget.HotelBudgetId,
PropertyId = property.PropertyId,
PropertyName = property.PropertyName,
CurrencyCode = currency.CurrencyCode
}).ToList();
return list;
}
public static List<HotelDataEntryLib.Helper.HotelRevenue> GetAllPropertyByHotelRevenue(int year, int month)
{
var hdc = new HotelDataEntryDataContext();
var list = (from property in hdc.Properties
join hotelRevenue in hdc.HotelRevenues on property.PropertyId equals hotelRevenue.PropertyId
join currency in hdc.Currencies on property.CurrencyId equals currency.CurrencyId
where hotelRevenue.Year == year && hotelRevenue.Month==month
orderby property.PropertyCode
select new HotelDataEntryLib.Helper.HotelRevenue()
{
HotelRevenueId = hotelRevenue.HotelRevenueId,
PropertyId = property.PropertyId,
PropertyName = property.PropertyName,
CurrencyCode = currency.CurrencyCode
}).ToList();
return list;
}
public static List <HotelDataEntryLib.Helper.HotelRevenue> GetAllPropertyByHotelRevenue(int year, int month)
{
var hdc = new HotelDataEntryDataContext();
var list = (from property in hdc.Properties
join hotelRevenue in hdc.HotelRevenues on property.PropertyId equals hotelRevenue.PropertyId
join currency in hdc.Currencies on property.CurrencyId equals currency.CurrencyId
where hotelRevenue.Year == year && hotelRevenue.Month == month
orderby property.PropertyCode
select new HotelDataEntryLib.Helper.HotelRevenue()
{
HotelRevenueId = hotelRevenue.HotelRevenueId,
PropertyId = property.PropertyId,
PropertyName = property.PropertyName,
CurrencyCode = currency.CurrencyCode
}).ToList();
return(list);
}
public static List <Budget> GetAllPropertyByHotelBudget(int year)
{
var hdc = new HotelDataEntryDataContext();
var list = (from property in hdc.Properties
join hotelBudget in hdc.HotelBudgets on property.PropertyId equals hotelBudget.PropertyId
join currency in hdc.Currencies on property.CurrencyId equals currency.CurrencyId
where hotelBudget.Year == year
orderby property.PropertyCode
select new Budget()
{
HotelBudgetId = hotelBudget.HotelBudgetId,
PropertyId = property.PropertyId,
PropertyName = property.PropertyName,
CurrencyCode = currency.CurrencyCode
}).ToList();
return(list);
}
public static void DeleteProperty(int propertyId)
{
using (var hdc = new HotelDataEntryDataContext())
{
var property = hdc.Properties.Single(item => item.PropertyId == propertyId);
hdc.Properties.DeleteOnSubmit(property);
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void DeleteUserProfile(int userId)
{
using (var hdc = new HotelDataEntryDataContext())
{
var user = hdc.Users.Single(item => item.UserId == userId);
hdc.Users.DeleteOnSubmit(user);
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void AddUserProfile(User user)
{
using (var hdc = new HotelDataEntryDataContext())
{
user.UpdateDateTime = DateTime.Now;
hdc.Users.InsertOnSubmit(user);
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void AddUserProfile(User user)
{
using (var hdc = new HotelDataEntryDataContext())
{
user.UpdateDateTime = DateTime.Now;
hdc.Users.InsertOnSubmit(user);
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void DeleteUserProfile(int userId)
{
using (var hdc = new HotelDataEntryDataContext())
{
var user = hdc.Users.Single(item => item.UserId == userId);
hdc.Users.DeleteOnSubmit(user);
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void DeleteCurrency(int currencyId)
{
using (var hdc = new HotelDataEntryDataContext())
{
var currency = hdc.Currencies.Single(item => item.CurrencyId == currencyId);
hdc.Currencies.DeleteOnSubmit(currency);
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static void AddRevenueEntryListByMonthYear(HotelRevenue hotelEntry, string username)
{
var dates = GetLastDayOfMonth(hotelEntry.Month,hotelEntry.Year);
using (var hdc = new HotelDataEntryDataContext())
{
for (var i = 0; i < dates; i++)
{
hdc.RevenueEntries.InsertOnSubmit(new RevenueEntry()
{
HotelRevenueId = hotelEntry.HotelRevenueId,
OccupancyRoom = 0,
RoomRevenue = 0.00,
FBRevenue = 0.00,
SpaRevenue = 0.00,
Others = 0.00,
Total = 0.00,
UpdateDateTime = DateTime.Now,
UpdateUser = username,
PositionDate = new DateTime(hotelEntry.Year, hotelEntry.Month, (i + 1))
});
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
}
public static HotelBudget GetHotelEntry(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var hEntry = hdc.HotelBudgets.Single(item => item.Year == hotelEntry.Year && item.PropertyId == hotelEntry.PropertyId);
return hEntry;
}
public static bool ExistYear(HotelBudget hotelEntry)
{
var hdc = new HotelDataEntryDataContext();
var count = hdc.HotelBudgets.Count(item => item.Year==hotelEntry.Year && item.PropertyId == hotelEntry.PropertyId);
return count != 0;
}
public static List<Property> ListProperites()
{
using (var hdc = new HotelDataEntryDataContext())
{
var listCompany = new List<Property> { new Property() { PropertyId = 0, PropertyName = "Select a Property", PropertyCode = "Select a Property"} };
listCompany.AddRange(hdc.Properties.ToList());
return listCompany;
}
}
public static List<Property> Properites()
{
using (var hdc = new HotelDataEntryDataContext())
{
return hdc.Properties.OrderBy(item=>item.PropertyCode).ToList();
}
}
public static void UpdateProperty(Property property)
{
using (var hdc = new HotelDataEntryDataContext())
{
var prop = hdc.Properties.Single(item => item.PropertyId == property.PropertyId);
prop.PropertyName =property.PropertyName;
prop.PropertyCode = property.PropertyCode;
prop.CurrencyId = property.CurrencyId;
prop.Status = property.Status;
prop.UpdateDateTime = DateTime.Now;
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
public static List<Currency> ListCurreny()
{
var currencyList = new HotelDataEntryDataContext().Currencies.ToList();
return currencyList;
}
public static Currency GetCurrency(int currencyId)
{
var currency = new HotelDataEntryDataContext().Currencies.Single(item => item.CurrencyId == currencyId);
return currency;
}
public static IEnumerable<object> ListAllProperties()
{
var hdc = new HotelDataEntryDataContext();
IEnumerable<object> listCompany = null;
listCompany = (from company in hdc.Properties
join currency in hdc.Currencies on company.CurrencyId equals currency.CurrencyId
select new
{
company.PropertyId,
company.PropertyName,
company.PropertyCode,
company.StatusLabel,
currency.CurrencyCode
}).ToList();
return listCompany;
}
public static void UpdateIsBaseCurrency()
{
using (var hdc = new HotelDataEntryDataContext())
{
var baseCurrency = hdc.Currencies.Single(item => item.IsBase == 1);
if (baseCurrency != null)
{
baseCurrency.IsBase = 0;
try
{
hdc.SubmitChanges();
}
catch (SqlException ex)
{
if (ex.Number == 2601 || ex.Number == 2627)
{
throw;
}
}
}
}
}
