public static Boolean CheckTaxRecordExists(etblPropertyTaxMap eobj)
{
Boolean result = false;
using (OneFineRateEntities db = new OneFineRateEntities())
{
List <PropertyTaxList> user = new List <PropertyTaxList>();
SqlParameter[] MyParam = new SqlParameter[5];
MyParam[0] = new SqlParameter("@StayFrom", eobj.dtStayFrom);
MyParam[1] = new SqlParameter("@StayTo", eobj.dtStayTo);
MyParam[2] = new SqlParameter("@PropId", eobj.iPropId);
MyParam[3] = new SqlParameter("@RoomId", eobj.iRoomId == null ? 0 : eobj.iRoomId);
MyParam[4] = new SqlParameter("@TotalCount", 0);
MyParam[4].Direction = System.Data.ParameterDirection.Output;
user = db.Database.SqlQuery <PropertyTaxList>("uspCheckTaxRecordExists @StayFrom, @StayTo, @PropId , @RoomId , @TotalCount out", MyParam).ToList();
if (user.Count > 0)
{
result = true;
}
else
{
result = false;
}
}
return(result);
}
//Get Single Record
public static etblPropertyTaxMap GetSingleRecordById(int id)
{
etblPropertyTaxMap eobj = new etblPropertyTaxMap();
using (OneFineRateEntities db = new OneFineRateEntities())
{
var dbobj = db.tblPropertyTaxMaps.SingleOrDefault(u => u.iPropTaxId == id);
if (dbobj != null)
{
eobj = (etblPropertyTaxMap)OneFineRateAppUtil.clsUtils.ConvertToObject(dbobj, eobj);
}
}
return(eobj);
}
public ActionResult Edit(int Id)
{
obj = BL_tblPropertyTaxMap.GetSingleRecordById(Id);
ViewBag.heading = "Modify Taxes";
obj.Mode = "Edit";
if (obj.dtStayFrom != null)
{
obj.stayfrom = String.Format("{0:dd/MM/yyyy}", obj.dtStayFrom);
}
if (obj.dtStayTo != null)
{
obj.stayto = String.Format("{0:dd/MM/yyyy}", obj.dtStayTo);
}
//obj.PlanId = obj.iRoomId + "|" + obj.iRPId;
if (obj.iRoomId == null)
{
obj.iRoomId = 0;
}
obj.ListRatePlans = BL_tblPropertyRoomMap.GetAllPropertyTypes(Convert.ToInt32(Session["PropId"]));
obj.ListTaxes = BL_Tax.GetAllTaxOfProperty(Id);
return(PartialView("PropertyTax", obj));
}
public string UpdateStatus(int Id, bool Mode)
{
object result = null;
string strReturn = string.Empty;
try
{
obj = BL_tblPropertyTaxMap.GetSingleRecordById(Id);
if (Mode == true)
{
obj.cStatus = "A";
}
else
{
obj.cStatus = "I";
}
int i = BL_tblPropertyTaxMap.UpdateStatus(obj);
if (i == 1)
{
result = new { st = 1, msg = "Updated successfully." };
}
else if (i == 2)
{
result = new { st = 0, msg = "Tax already exixts for selected Rate Plan and Stay Validity" };
}
else
{
result = new { st = 0, msg = "Kindly try after some time." };
}
}
catch (Exception)
{
result = new { st = 0, msg = "Kindly try after some time." };
}
strReturn = OneFineRateAppUtil.clsUtils.ConvertToJson(result);
return(strReturn);
}
//Get Single Record
public static Boolean CheckRecordExist(int ipropid, Nullable <long> iroomId, int iproptaxId)
{
Boolean result = false;
etblPropertyTaxMap eobj = new etblPropertyTaxMap();
using (OneFineRateEntities db = new OneFineRateEntities())
{
var dbobj = db.tblPropertyTaxMaps.SingleOrDefault(u => u.iPropId == ipropid && u.iRoomId == iroomId && u.iPropTaxId != iproptaxId);
if (dbobj != null)
{
eobj = (etblPropertyTaxMap)OneFineRateAppUtil.clsUtils.ConvertToObject(dbobj, eobj);
}
if (eobj.iPropTaxId != 0)
{
result = true;
}
else
{
result = false;
}
}
return(result);
}
public ActionResult AddUpdate(etblPropertyTaxMap eObj)
{
object result = null;
try
{
if (ModelState.IsValid)
{
if (eObj.iRoomId == 0)
{
eObj.iRoomId = null;
}
if (eObj.SelectedTaxes != null)
{
JArray jArray = (JArray)JsonConvert.DeserializeObject(eObj.SelectedTaxes.Replace("\\", "\""));
if (jArray != null)
{
List <etblPropertyTaxesMap> lstPropertyTaxesMap = new List <etblPropertyTaxesMap>();
foreach (var item in jArray)
{
lstPropertyTaxesMap.Add(new etblPropertyTaxesMap()
{
iTaxId = Convert.ToInt32(item["TaxId"]),
bIsPercent = Convert.ToBoolean(item["Type"]),
dValue = Convert.ToDecimal(item["value"]),
dtActionDate = DateTime.Now,
iActionBy = ((OneFineRateBLL.BL_Login.UserDetails)Session["UserDetails"]).iUserId
});
}
eObj.PropertyTaxesList = lstPropertyTaxesMap;
}
}
//if (eObj.Mode == "Edit")
//{
// bool ResultType = BL_tblPropertyTaxMap.CheckRecordExist(eObj.iPropId, eObj.iRoomId,eObj.iPropTaxId);
// //var existingRecord = BL_tblPropertyTaxMap.GetSingleRecordById(eObj.iPropTaxId);
// //if(string.IsNullOrEmpty(eObj.PlanId))
// //{
// // if (existingRecord.iRoomId != null)
// // {
// // var ratePlanResult = new { st = 0, msg = "Tax already added / mapped with another ratePlan ." };
// // return Json(ratePlanResult, JsonRequestBehavior.AllowGet);
// // }
// //}
// //else
// //{
// if (ResultType == true)
// {
// //var ratePlanResult = new { st = 0, msg = "Tax already added / mapped with another ratePlan ." };
// var ratePlanResult = new { st = 0, msg = "Tax already added." };
// return Json(ratePlanResult, JsonRequestBehavior.AllowGet);
// }
// //}
//}
eObj.dtStayFrom = clsUtils.ConvertddmmyyyytoDateTime(eObj.stayfrom);
eObj.dtStayTo = clsUtils.ConvertddmmyyyytoDateTime(eObj.stayto);
int i = BL_tblPropertyTaxMap.CheckStatus(Convert.ToInt32(eObj.iPropId), Convert.ToInt32(eObj.iRPId), Convert.ToInt32(eObj.iRoomId), Convert.ToDateTime(eObj.dtStayFrom), Convert.ToDateTime(eObj.dtStayTo), Convert.ToInt32(eObj.iPropTaxId));
if (i > 0)
{
result = new { st = 0, msg = "Taxes for these dates has already been made." };
return(Json(result, JsonRequestBehavior.AllowGet));
}
if (eObj.Mode == "ADD")
{
eObj.iPropId = Convert.ToInt32(Session["PropId"]);
eObj.dtActionDate = DateTime.Now;
eObj.cStatus = "A";
eObj.iActionBy = ((OneFineRateBLL.BL_Login.UserDetails)Session["UserDetails"]).iUserId;
int j = BL_tblPropertyTaxMap.AddRecord(eObj);
if (j == 1)
{
bool bookingAffected = false;
var statusChange = BL_tblPropertyTaxMap.CheckAffectedBookingsAfterPropertyTaxChange(Convert.ToDateTime(eObj.dtStayFrom), Convert.ToDateTime(eObj.dtStayTo));
if (statusChange.Key == 1)
{
bookingAffected = true;
}
result = new { st = 1, bookingAffected = bookingAffected, msg = "Added successfully." };
}
else if (j == 2)
{
result = new { st = 0, msg = "Tax already exists for selected Rate Plan and Stay Validity" };
}
else if (j == 3)
{
result = new { st = 0, msg = "Tax already exists for single room types" };
}
else if (j == 4)
{
result = new { st = 0, msg = "Tax already exists for all rooms" };
}
else
{
result = new { st = 0, msg = "Kindly try after some time" };
}
}
else if (eObj.Mode == "Edit")
{
eObj.iActionBy = ((OneFineRateBLL.BL_Login.UserDetails)Session["UserDetails"]).iUserId;
eObj.dtActionDate = DateTime.Now;
int j = BL_tblPropertyTaxMap.UpdateRecord(eObj);
if (j == 1)
{
bool bookingAffected = false;
var statusChange = BL_tblPropertyTaxMap.CheckAffectedBookingsAfterPropertyTaxChange(Convert.ToDateTime(eObj.dtStayFrom), Convert.ToDateTime(eObj.dtStayTo));
if (statusChange.Key == 1)
{
bookingAffected = true;
}
result = new { st = 1, bookingAffected = bookingAffected, msg = "Updated successfully." };
}
else if (j == 2)
{
result = new { st = 0, msg = "Tax already exixts for selected Rate Plan and Stay Validity" };
}
else
{
result = new { st = 0, msg = "Kindly try after some time" };
}
}
}
else
{
string errormsg = "";
foreach (ModelState modelState in ViewData.ModelState.Values)
{
foreach (ModelError error in modelState.Errors)
{
errormsg += error.ErrorMessage;
errormsg += "</br>";
}
}
result = new { st = 0, msg = errormsg };
}
eObj.ListRatePlans = BL_tblPropertyRatePlanMap.GetAllRatePlans(Convert.ToInt32(Session["PropId"]));
eObj.ListTaxes = BL_Tax.GetAllTaxOfProperty(eObj.iPropTaxId);
}
catch (Exception)
{
result = new { st = 0, msg = "Kindly try after some time" };
}
// return PartialView("PropertyTax", eObj);
return(Json(result, JsonRequestBehavior.AllowGet));
}
//Add new record
public static int AddRecord(etblPropertyTaxMap eobj)
{
int PropTaxId = 0;
string Status = "";
int retval = 0;
int count = 0;
using (OneFineRateEntities db = new OneFineRateEntities())
{
try
{
bool checkExists = false;
//checkExists = CheckTaxRecordExists(eobj);
if (eobj.iRoomId != null)
{
checkExists = db.tblPropertyTaxMaps.Any(
p => p.iPropId == eobj.iPropId &&
(p.iRoomId == eobj.iRoomId || p.iRoomId == null) &&
p.cStatus == "A" &&
(((p.dtStayFrom <= eobj.dtStayFrom && p.dtStayTo >= eobj.dtStayFrom) || (p.dtStayFrom <= eobj.dtStayTo && p.dtStayTo >= eobj.dtStayTo)) ||
((eobj.dtStayFrom <= p.dtStayFrom && eobj.dtStayTo >= p.dtStayFrom) || (eobj.dtStayFrom <= p.dtStayTo && eobj.dtStayTo >= p.dtStayTo)))
);
}
else
{
checkExists = db.tblPropertyTaxMaps.Any(
p => p.iPropId == eobj.iPropId
//&& ((p.iRoomId != null && p.cStatus != "A") || (p.iRoomId == null))
&& (((p.iRoomId != null) && p.cStatus == "A") || ((p.iRoomId == null)))
&& (((p.dtStayFrom <= eobj.dtStayFrom && p.dtStayTo >= eobj.dtStayFrom) || (p.dtStayFrom <= eobj.dtStayTo && p.dtStayTo >= eobj.dtStayTo)) ||
((eobj.dtStayFrom <= p.dtStayFrom && eobj.dtStayTo >= p.dtStayFrom) || (eobj.dtStayFrom <= p.dtStayTo && eobj.dtStayTo >= p.dtStayTo)))
);
}
if (checkExists != true)
{
OneFineRate.tblPropertyTaxMap dbuser = (OneFineRate.tblPropertyTaxMap)OneFineRateAppUtil.clsUtils.ConvertToObject(eobj, new OneFineRate.tblPropertyTaxMap());
db.tblPropertyTaxMaps.Add(dbuser);
db.SaveChanges();
PropTaxId = dbuser.iPropTaxId;
Status = dbuser.cStatus;
OneFineRate.tblPropertyTaxesMap dbnew = (OneFineRate.tblPropertyTaxesMap)OneFineRateAppUtil.clsUtils.ConvertToObject(eobj, new OneFineRate.tblPropertyTaxesMap());
//Add mapings
if (eobj.PropertyTaxesList != null)
{
db.tblPropertyTaxesMaps.AddRange(eobj.PropertyTaxesList.Select(x => new tblPropertyTaxesMap()
{
iPropTaxId = PropTaxId,
iTaxId = x.iTaxId,
bIsPercent = x.bIsPercent,
dValue = x.dValue,
dtActionDate = x.dtActionDate,
iActionBy = x.iActionBy
}).ToList());
}
db.SaveChanges();
#region Propperty Room Tax List
List <PropertyRoomTaxList> LPRPC = new List <PropertyRoomTaxList>();
if (eobj.iRoomId != null)
{
DateTime CurrentDate = (DateTime)eobj.dtStayFrom;
while (CurrentDate <= eobj.dtStayTo)
{
PropertyRoomTaxList PRPC = new PropertyRoomTaxList();
PRPC.dtStay = CurrentDate;
PRPC.iPropTaxId = PropTaxId;
PRPC.iPropId = eobj.iPropId;
PRPC.iRoomId = (long)eobj.iRoomId;
PRPC.iActionBy = (int)eobj.iActionBy;
PRPC.dtActionDate = DateTime.Now;
PRPC.cStatus = Status;
LPRPC.Add(PRPC);
CurrentDate = CurrentDate.AddDays(1);
}
}
else
{
List <PNames> objRoomList = new List <PNames>();
objRoomList = BL_tblPropertyRoomMap.GetAllPropertyTypes(eobj.iPropId);
foreach (var item in objRoomList)
{
DateTime CurrentDate = (DateTime)eobj.dtStayFrom;
while (CurrentDate <= eobj.dtStayTo)
{
PropertyRoomTaxList PRPC = new PropertyRoomTaxList();
PRPC.dtStay = CurrentDate;
PRPC.iPropTaxId = PropTaxId;
PRPC.iPropId = eobj.iPropId;
PRPC.iRoomId = Convert.ToInt64(item.Id);
PRPC.iActionBy = (int)eobj.iActionBy;
PRPC.dtActionDate = DateTime.Now;
PRPC.cStatus = Status;
LPRPC.Add(PRPC);
CurrentDate = CurrentDate.AddDays(1);
}
}
}
if (LPRPC != null)
{
db.tblPropertyRoomTaxMaps.AddRange(LPRPC.Select(m => new tblPropertyRoomTaxMap()
{
dtStay = m.dtStay,
iPropTaxId = m.iPropTaxId,
iPropId = m.iPropId,
iRoomId = (long)m.iRoomId,
dtActionDate = m.dtActionDate,
iActionBy = m.iActionBy,
cStatus = m.cStatus
}));
}
db.SaveChanges();
#endregion
retval = 1;
}
else
{
retval = 2;
}
}
catch (Exception)
{
throw;
}
}
return(retval);
}
// Please Check In
//Update active inactive
public static int UpdateStatus(etblPropertyTaxMap eobj)
{
int retval = 0;
using (OneFineRateEntities db = new OneFineRateEntities())
{
try
{
var dbobjRoomId = db.tblPropertyTaxMaps.SingleOrDefault(u => u.iPropId == eobj.iPropId && u.iPropTaxId == eobj.iPropTaxId);
eobj.iRoomId = dbobjRoomId.iRoomId;
if (eobj.cStatus == "A")
{
bool checkExists = false;
if (dbobjRoomId.iRoomId != null)
{
checkExists = db.tblPropertyTaxMaps.Any(
p => p.iPropId == eobj.iPropId
//&& p.iRoomId == null
//&& p.cStatus == "A"
&& (p.iRoomId == eobj.iRoomId || p.iRoomId == null) &&
p.cStatus == "A" &&
p.iPropTaxId != eobj.iPropTaxId &&
(((p.dtStayFrom <= eobj.dtStayFrom && p.dtStayTo >= eobj.dtStayFrom) || (p.dtStayFrom <= eobj.dtStayTo && p.dtStayTo >= eobj.dtStayTo)) ||
((eobj.dtStayFrom <= p.dtStayFrom && eobj.dtStayTo >= p.dtStayFrom) || (eobj.dtStayFrom <= p.dtStayTo && eobj.dtStayTo >= p.dtStayTo)))
);
}
else
{
checkExists = db.tblPropertyTaxMaps.Any(
p => p.iPropId == eobj.iPropId
//&& (p.iRoomId != null)
//&& p.cStatus == "A"
&& ((p.iRoomId == null) || (p.iRoomId != null && p.cStatus == "A")) &&
p.iPropTaxId != eobj.iPropTaxId &&
(((p.dtStayFrom <= eobj.dtStayFrom && p.dtStayTo >= eobj.dtStayFrom) || (p.dtStayFrom <= eobj.dtStayTo && p.dtStayTo >= eobj.dtStayTo)) ||
((eobj.dtStayFrom <= p.dtStayFrom && eobj.dtStayTo >= p.dtStayFrom) || (eobj.dtStayFrom <= p.dtStayTo && eobj.dtStayTo >= p.dtStayTo)))
);
}
if (checkExists != true)
{
using (OneFineRateEntities db1 = new OneFineRateEntities())
{
OneFineRate.tblPropertyTaxMap obj = (OneFineRate.tblPropertyTaxMap)OneFineRateAppUtil.clsUtils.ConvertToObject(eobj, new OneFineRate.tblPropertyTaxMap());
db1.tblPropertyTaxMaps.Attach(obj);
db1.Entry(obj).State = System.Data.Entity.EntityState.Modified;
db1.SaveChanges();
retval = 1;
}
}
else
{
retval = 2;
}
}
else
{
using (OneFineRateEntities db2 = new OneFineRateEntities())
{
OneFineRate.tblPropertyTaxMap obj = (OneFineRate.tblPropertyTaxMap)OneFineRateAppUtil.clsUtils.ConvertToObject(eobj, new OneFineRate.tblPropertyTaxMap());
db2.tblPropertyTaxMaps.Attach(obj);
db2.Entry(obj).State = System.Data.Entity.EntityState.Modified;
db2.SaveChanges();
retval = 1;
}
}
}
catch (Exception)
{
throw;
}
}
return(retval);
}
//Update a record
public static int UpdateRecord(etblPropertyTaxMap eobj)
{
int retval = 0;
using (OneFineRateEntities db = new OneFineRateEntities())
{
try
{
var dbobjRoomId = db.tblPropertyTaxMaps.SingleOrDefault(u => u.iPropId == eobj.iPropId && u.iPropTaxId == eobj.iPropTaxId);
eobj.iRoomId = dbobjRoomId.iRoomId;
bool checkExists = false;
if (dbobjRoomId.iRoomId != null)
{
checkExists = db.tblPropertyTaxMaps.Any(
p => p.iPropId == eobj.iPropId &&
(p.iRoomId == eobj.iRoomId || p.iRoomId == null) &&
p.cStatus == "A" &&
p.iPropTaxId != eobj.iPropTaxId
// && p.iRoomId == null
// && p.cStatus == "A"
&& (((p.dtStayFrom <= eobj.dtStayFrom && p.dtStayTo >= eobj.dtStayFrom) || (p.dtStayFrom <= eobj.dtStayTo && p.dtStayTo >= eobj.dtStayTo)) ||
((eobj.dtStayFrom <= p.dtStayFrom && eobj.dtStayTo >= p.dtStayFrom) || (eobj.dtStayFrom <= p.dtStayTo && eobj.dtStayTo >= p.dtStayTo)))
);
}
else
{
checkExists = db.tblPropertyTaxMaps.Any(
p => p.iPropId == eobj.iPropId &&
((p.iRoomId == null) || (p.iRoomId != null && p.cStatus == "A")) &&
p.iPropTaxId != eobj.iPropTaxId &&
(((p.dtStayFrom <= eobj.dtStayFrom && p.dtStayTo >= eobj.dtStayFrom) || (p.dtStayFrom <= eobj.dtStayTo && p.dtStayTo >= eobj.dtStayTo)) ||
((eobj.dtStayFrom <= p.dtStayFrom && eobj.dtStayTo >= p.dtStayFrom) || (eobj.dtStayFrom <= p.dtStayTo && eobj.dtStayTo >= p.dtStayTo)))
);
}
if (checkExists != true)
{
using (OneFineRateEntities db1 = new OneFineRateEntities())
{
OneFineRate.tblPropertyTaxMap obj = (OneFineRate.tblPropertyTaxMap)OneFineRateAppUtil.clsUtils.ConvertToObject(eobj, new OneFineRate.tblPropertyTaxMap());
db1.tblPropertyTaxMaps.Attach(obj);
db1.Entry(obj).State = System.Data.Entity.EntityState.Modified;
//remove old mapings
db1.tblPropertyTaxesMaps.RemoveRange(db1.tblPropertyTaxesMaps.Where(x => x.iPropTaxId == eobj.iPropTaxId));
if (eobj.PropertyTaxesList != null)
{
db1.tblPropertyTaxesMaps.AddRange(eobj.PropertyTaxesList.Select(x => new tblPropertyTaxesMap()
{
iPropTaxId = eobj.iPropTaxId,
iTaxId = x.iTaxId,
bIsPercent = x.bIsPercent,
dValue = x.dValue,
dtActionDate = x.dtActionDate,
iActionBy = x.iActionBy
}).ToList());
}
db1.SaveChanges();
#region Propperty Room Tax List
//db1.tblPropertyRoomTaxMaps.RemoveRange(db1.tblPropertyRoomTaxMaps.Where(n => n.iPropTaxId == eobj.iPropTaxId));
SqlParameter[] MyParam = new SqlParameter[1];
MyParam[0] = new SqlParameter("@PropTaxId", eobj.iPropTaxId);
db.Database.ExecuteSqlCommand("uspRemovetblPropertyRoomTaxMaps @PropTaxId", MyParam);
List <PropertyRoomTaxList> LPRPC = new List <PropertyRoomTaxList>();
if (eobj.iRoomId != null)
{
DateTime CurrentDate = (DateTime)eobj.dtStayFrom;
while (CurrentDate <= eobj.dtStayTo)
{
PropertyRoomTaxList PRPC = new PropertyRoomTaxList();
PRPC.dtStay = CurrentDate;
PRPC.iPropTaxId = eobj.iPropTaxId;
PRPC.iPropId = eobj.iPropId;
PRPC.iRoomId = (long)eobj.iRoomId;
PRPC.iActionBy = (int)eobj.iActionBy;
PRPC.dtActionDate = DateTime.Now;
PRPC.cStatus = eobj.cStatus;
LPRPC.Add(PRPC);
CurrentDate = CurrentDate.AddDays(1);
}
}
else
{
List <PNames> objRoomList = new List <PNames>();
objRoomList = BL_tblPropertyRoomMap.GetAllPropertyTypes(eobj.iPropId);
foreach (var item in objRoomList)
{
DateTime CurrentDate = (DateTime)eobj.dtStayFrom;
while (CurrentDate <= eobj.dtStayTo)
{
PropertyRoomTaxList PRPC = new PropertyRoomTaxList();
PRPC.dtStay = CurrentDate;
PRPC.iPropTaxId = eobj.iPropTaxId;;
PRPC.iPropId = eobj.iPropId;
PRPC.iRoomId = Convert.ToInt64(item.Id);
PRPC.iActionBy = (int)eobj.iActionBy;
PRPC.dtActionDate = DateTime.Now;
PRPC.cStatus = eobj.cStatus;
LPRPC.Add(PRPC);
CurrentDate = CurrentDate.AddDays(1);
}
}
}
if (LPRPC != null)
{
db.tblPropertyRoomTaxMaps.AddRange(LPRPC.Select(m => new tblPropertyRoomTaxMap()
{
dtStay = m.dtStay,
iPropTaxId = m.iPropTaxId,
iPropId = m.iPropId,
iRoomId = (long)m.iRoomId,
dtActionDate = m.dtActionDate,
iActionBy = m.iActionBy,
cStatus = m.cStatus
}));
}
db.SaveChanges();
#endregion
retval = 1;
}
}
else
{
retval = 2;
}
}
catch (Exception)
{
throw;
}
}
return(retval);
}
