public bool CreateBankReconcilation(JVMasterModel model)
{
using (EHMSEntities ent = new EHMSEntities())
{
int fiscalyearid = Utility.GetCurrentFiscalYearID();
foreach (var item in model.objGetBankReconcilationModelView.objGetBankReconcilationModelViewList)
{
RemoveBankReconcilationJv(item.JvNumber);
var BankReconObj = new BankReconcilation()
{
BankID = model.objGetBankReconcilationModelView.BankID,
BankSubID = model.objGetBankReconcilationModelView.BankSubID,
FromDate = model.objGetBankReconcilationModelView.StartDate,
ToDate = model.objGetBankReconcilationModelView.EndDate,
JvNo = item.JvNumber,
JvDate = item.TransactionDate,
Narration = item.Narration,
DrAmount = item.TotalDebit,
CrAmount = item.TotalCredit,
BankDate = item.State,
CreatedBy = Utility.GetCurrentLoginUserId(),
CreatedDate = DateTime.Now,
Status = true
};
ent.BankReconcilations.Add(BankReconObj);
}
ent.SaveChanges();
}
return(true);
}
public List <IncomeAndExpenditureModelView> GetIncomeAndExpenditure(JVMasterModel model)
{
using (EHMSEntities ent = new EHMSEntities())
{
return(ent.Database.SqlQuery <IncomeAndExpenditureModelView>("GetIncomeAndExpenditure '" + model.objIncomeAndExpenditureModelView.StartDate + "', '" + model.objIncomeAndExpenditureModelView.EndDate + "'").ToList());
}
}
public ActionResult ShowJournalVoucher(int id)
{
JVMasterModel model = new JVMasterModel();
model.ObjJVDetailsViewModel = new JVDetailsViewModel();
string Name = Utility.GetJVFormName(id);
if (Name == "Payroll" || Name == "Stock")
{
model.JVDetailsModelList = pro.GetJvPayrollDisplayReport(id);
}
else if (Name == "Comission")
{
model.JVDetailsModelList = pro.GetJvCommisionDisplayReport(id);
}
else
{
model.JVDetailsModelList = pro.GetJvDisplayReport(id);
}
model.ObjJVDetailsViewModel.JVnumber = pro.GetJVNumberFromJVMasterId(id);
model.ObjJVDetailsViewModel.JVDate = pro.GetJVTransactionDate(id);
model.Narration1 = pro.GetJVNarration(id);
model.JvType = pro.GetJVTypeFromJVMasterId(id);
return(View(model));
}
public List <GetAllTrialBalanceModelView> GetAllTrialBalance(JVMasterModel model)
{
using (EHMSEntities ent = new EHMSEntities())
{
return(ent.Database.SqlQuery <GetAllTrialBalanceModelView>("GetTrialBalanceAll '" + model.objGetAllTrialBalanceModelView.StartDate + "', '" + model.objGetAllTrialBalanceModelView.EndDate + "', '" + model.objGetAllTrialBalanceModelView.Level + "','" + model.objGetAllTrialBalanceModelView.ParentID + "'").ToList());
}
}
public List <BalanceSheetModelView> GetBalanceSheetLiability(JVMasterModel model)
{
using (EHMSEntities ent = new EHMSEntities())
{
return(ent.Database.SqlQuery <BalanceSheetModelView>("GetBalanceSheetLiability '" + model.objBalanceSheetModelView.EndDate + "', '" + model.objBalanceSheetModelView.FiscalYear + "'").ToList());
}
}
public JVMasterModel GetJVListBySearchParameter(JVMasterModel model, string StartingDate, string EndDate)
{
string JvNumber = string.Empty;
using (EHMSEntities ent = new EHMSEntities())
{
if (model.JvNumber != null)
{
JvNumber = "JV-" + model.JvNumber;
}
var result = ent.Database.SqlQuery <JVMasterModel>("ListJournalVoucherBySearch '" + JvNumber + "', '" + StartingDate + "', '" + EndDate + "', '" + model.JvType + "'").ToList();
foreach (var item in result)
{
var Viewmodel = new JVMasterModel()
{
JvNumber = item.JvNumber,
TransactionDate = item.TransactionDate,
Narration1 = item.Narration1,
JvMasterId = item.JvMasterId,
TotalAmount = item.TotalAmount
};
model.JVMasterModelList.Add(Viewmodel);
}
return(model);
}
}
public List <GetCashHandoverDetailModelView> GetCashDetailByUser(JVMasterModel model)
{
using (EHMSEntities ent = new EHMSEntities())
{
return(ent.Database.SqlQuery <GetCashHandoverDetailModelView>("HandOverCashDetail '" + Utility.GetCurrentLoginUserId() + "','" + model.objGetCashHandoverDetailModelView.HandoverDateFrom + "','" + model.objGetCashHandoverDetailModelView.HandoverDateTo + "'").ToList());
}
}
public List <HandoverDetailReportModelView> HandOverReport(JVMasterModel model)
{
using (EHMSEntities ent = new EHMSEntities())
{
return(ent.Database.SqlQuery <HandoverDetailReportModelView>("ReportOfHandOverDetail '" + model.objHandoverDetailReportModelView.HandoverDateFrom + "','" + model.objHandoverDetailReportModelView.HandoverDateTo + "'").ToList());
}
}
public List <GetBilldetailByDepartmentModelView> GetBillDetailByDepartment(JVMasterModel model)
{
using (EHMSEntities ent = new EHMSEntities())
{
return(ent.Database.SqlQuery <GetBilldetailByDepartmentModelView>("BillDetailByDepartment '" + model.objGetBilldetailByDepartmentModelView.BillDate + "','" + model.objGetBilldetailByDepartmentModelView.BillDateTo + "'").ToList());
}
}
public List <BSScheduleModelView> GetBSSchedule(JVMasterModel model)
{
using (EHMSEntities ent = new EHMSEntities())
{
return(ent.Database.SqlQuery <BSScheduleModelView>("GetSchedule '" + model.objBSScheduleModelView.StartDate + "', '" + model.objBSScheduleModelView.EndDate + "', '" + model.objBSScheduleModelView.HierarchyCode + "', '" + model.objBSScheduleModelView.Level + "'").ToList());
}
}
public List <LedgerDetailsViewModel> GetLedgerDeatilList(JVMasterModel model)
{
using (EHMSEntities ent = new EHMSEntities())
{
return(ent.Database.SqlQuery <LedgerDetailsViewModel>("PatientDepositDetail '" + model.ObjLedgerDetailsViewModel.StartDate + "', '" + model.ObjLedgerDetailsViewModel.EndDate + "'").ToList());
}
}
public ActionResult NotifyHandOver()
{
JVMasterModel model = new JVMasterModel();
model.objNotificationHandoverModelViewList = pro.NotifyHandOver(Utility.GetCurrentLoginUserId());
return(View(model));
}
public ActionResult LedgerAccountHeadWise(JVMasterModel model)
{
int DrOrCr = 1;
model.LedgerViewModelAccountHeadWiseList = pro.GetLeadgerAccountHeadwise(model, DrOrCr);
model.ObjLedgerViewModelAccountHeadWise.DrOrCrAmountType = DrOrCr;
return(PartialView("_LedgerDetailsAccWise", model));
}
public ActionResult GetCashHandover(JVMasterModel model)
{
model.objGetCashHandoverDetailModelViewList = pro.GetCashDetailByUser(model);
foreach (var item in model.objGetCashHandoverDetailModelViewList)
{
model.objGetCashHandoverDetailModelView.Amount += item.Amount;
}
return(PartialView("_GetCashHandover", model));
}
public List <LedgerViewModelAccountHeadWise> GetLeadgerAccountHeadwise(JVMasterModel model, int DrOrCr)
{
using (EHMSEntities ent = new EHMSEntities())
{
if (model.ObjLedgerViewModelAccountHeadWise.FeeSubTypeId > 0)
{
model.ObjLedgerViewModelAccountHeadWise.FeeTypeId = model.ObjLedgerViewModelAccountHeadWise.FeeSubTypeId;
}
return(ent.Database.SqlQuery <LedgerViewModelAccountHeadWise>("GetLedgerAccountHeadWise '" + model.ObjLedgerViewModelAccountHeadWise.StartDate + "', '" + model.ObjLedgerViewModelAccountHeadWise.EndDate + "', '" + model.ObjLedgerViewModelAccountHeadWise.FeeTypeId + "','" + model.ObjLedgerViewModelAccountHeadWise.FeeSubTypeId + "'").ToList());
}
}
public ActionResult GetAllTrialBalance1(DateTime startdate, DateTime enddate, int upto)
{
JVMasterModel model = new JVMasterModel();
model.objGetAllTrialBalanceModelView.StartDate = startdate;
model.objGetAllTrialBalanceModelView.EndDate = enddate.Date;
model.objGetAllTrialBalanceModelView.Level = 1;
model.objGetAllTrialBalanceModelView.ParentID = 1;
model.objGetAllTrialBalanceModelView.UpToLevel = upto;
model.objGetAllTrialBalanceModelViewList = pro.GetAllTrialBalance(model);
return(PartialView("_GetAllTrialBalance", model));
}
public ActionResult ShowPaymentAndReceiptVoucher(int id)
{
JVMasterModel model = new JVMasterModel();
model.ObjJVDetailsViewModel = new JVDetailsViewModel();
model.JVDetailsModelList = pro.GetListFromJVDetails(id);
model.JVDetailsModelList = pro.GetJvDisplayReport(id);
model.ObjJVDetailsViewModel.JVnumber = pro.GetJVNumberFromJVMasterId(id);
model.ObjJVDetailsViewModel.JVDate = pro.GetJVTransactionDate(id);
model.Narration1 = pro.GetJVNarration(id);
return(View(model));
}
public int InsertHandoverDetail(JVMasterModel model)
{
int i = 0;
int UserID = Utility.GetCurrentLoginUserId();
using (EHMSEntities ent = new EHMSEntities())
{
var HandoverDetailObj = new HandoverDetail()
{
HandOverDate = DateTime.Today,
HandOverDateFrom = model.objGetCashHandoverDetailModelView.HandoverDateFrom,
HandOverDateTo = model.objGetCashHandoverDetailModelView.HandoverDateTo,
HandOverFrom = Utility.GetCurrentLoginUserId(),
HandOverTo = model.objGetCashHandoverDetailModelView.HandoverTo,
HandOverAmount = model.objGetCashHandoverDetailModelView.Amount,
Rs1000 = model.objGetCashHandoverDetailModelView.Rs1000,
Rs500 = model.objGetCashHandoverDetailModelView.Rs500,
Rs100 = model.objGetCashHandoverDetailModelView.Rs100,
Rs50 = model.objGetCashHandoverDetailModelView.Rs50,
Rs20 = model.objGetCashHandoverDetailModelView.Rs20,
Rs10 = model.objGetCashHandoverDetailModelView.Rs10,
Rs5 = model.objGetCashHandoverDetailModelView.Rs5,
Rs2 = model.objGetCashHandoverDetailModelView.Rs2,
Rs1 = model.objGetCashHandoverDetailModelView.Rs1,
Status = "R",
IsHandOver = false,
Remarks = model.objGetCashHandoverDetailModelView.Remarks,
TotalBankDeposit = model.objGetCashHandoverDetailModelView.TotalBankDeposit,
TotalExpenses = model.objGetCashHandoverDetailModelView.TotalExpenses
};
ent.HandoverDetails.Add(HandoverDetailObj);
var CBMaster = (from x in ent.CentralizedBillingMasters
where (x.BillDate.Year >= model.objGetCashHandoverDetailModelView.HandoverDateFrom.Year && x.BillDate.Month >= model.objGetCashHandoverDetailModelView.HandoverDateFrom.Month && x.BillDate.Day >= model.objGetCashHandoverDetailModelView.HandoverDateFrom.Day && (x.BillDate.Year <= model.objGetCashHandoverDetailModelView.HandoverDateTo.Year && x.BillDate.Month <= model.objGetCashHandoverDetailModelView.HandoverDateTo.Month && x.BillDate.Day <= model.objGetCashHandoverDetailModelView.HandoverDateTo.Day)) && x.CreatedBy == UserID
select x);
foreach (CentralizedBillingMaster cb in CBMaster)
{
cb.IsHandover = true;
}
var HandOver = (from x in ent.HandoverDetails
where (x.HandOverDateFrom.Year >= model.objGetCashHandoverDetailModelView.HandoverDateFrom.Year && x.HandOverDateFrom.Month >= model.objGetCashHandoverDetailModelView.HandoverDateFrom.Month && x.HandOverDateFrom.Day >= model.objGetCashHandoverDetailModelView.HandoverDateFrom.Day && (x.HandOverDateTo.Year <= model.objGetCashHandoverDetailModelView.HandoverDateTo.Year && x.HandOverDateTo.Month <= model.objGetCashHandoverDetailModelView.HandoverDateTo.Month && x.HandOverDateTo.Day <= model.objGetCashHandoverDetailModelView.HandoverDateTo.Day)) && x.HandOverTo == UserID
select x);
foreach (HandoverDetail ho in HandOver)
{
ho.IsHandOver = true;
}
i = ent.SaveChanges();
return(i);
}
}
public ActionResult GetBillDetailByUser(int id)
{
JVMasterModel model = new JVMasterModel();
if (id == 1)
{
model.Narration2 = "Master";
}
else
{
model.Narration2 = "Slave";
}
return(View(model));
}
public ActionResult CreateNewJV(JVMasterModel model)
{
int i = 0;
string ViewName = "";
if (model.ObjAddMoreParticularsJVModel.DrAmountTotal == model.ObjAddMoreParticularsJVModel.CrAmountTotal)
{
i = pro.CreateNewJv(model);
return(RedirectToAction("ShowJournalVoucher", "JVMaster", new { id = i }));
}
else
{
return(View(model));
}
}
public ActionResult GetBillDetailByDepartment(int id)
{
JVMasterModel model = new JVMasterModel();
if (id == 1)
{
ViewBag.LayoutId = "Master";
}
else
{
ViewBag.LayoutId = "Slave";
}
return(View(model));
}
public ActionResult Index(string BillingDept, string JvDate, string JvDateEnd)
{
JVMasterModel model = new JVMasterModel();
BillingDept = "1";
if (!string.IsNullOrEmpty(BillingDept))
{
model.objBillingDetailViewModel = new BillingDetailViewModel();
model.BillingDetailViewModelList = pro.GetTodayTransaction(JvDate, JvDateEnd);
model.BillingDetailViewModelListNew = pro.GetBillDetailDisplayReport(JvDate, JvDateEnd);
model.objBillingDetailViewModel.DepartmentName = BillingDept;
model.objBillingDetailViewModel.BillingDate = JvDate;
model.objBillingDetailViewModel.StartDate = JvDate;
model.objBillingDetailViewModel.EndDate = JvDateEnd;
model.Count = pro.CountifComisionPosted(JvDate, JvDateEnd);
}
return(View(model));
}
public List <GetBankReconcilationModelView> GetBankReconcilation(JVMasterModel model)
{
using (EHMSEntities ent = new EHMSEntities())
{
string sql = "";
if (model.objGetBankReconcilationModelView.CheckValue)
{
if (model.objGetBankReconcilationModelView.BankSubID == 0)
{
sql = @"select dbo.GetNepaliDate(JVMaster.TransactionDate)TransactionDate, JVMaster.JvNumber, JVMaster.Narration1 as Narration,JVDetails.DrAmount as TotalDebit, JVDetails.CrAmount as TotalCredit, BankReconcilation.BankDate as State from JVDetails
inner join JVMaster on JVDetails.JVMasterId=JVMaster.JvMasterId
left join BankReconcilation on JVMaster.JvNumber=BankReconcilation.JvNo
where JVDetails.FeeTypeId='" + model.objGetBankReconcilationModelView.BankID + "' and JVMaster.TransactionDate Between '" + model.objGetBankReconcilationModelView.StartDate + "' and '" + model.objGetBankReconcilationModelView.EndDate + "' and isnull(BankReconcilation.BankDate,'')!=''";
}
else
{
sql = @"select dbo.GetNepaliDate(JVMaster.TransactionDate)TransactionDate, JVMaster.JvNumber, JVMaster.Narration1 as Narration,JVDetails.DrAmount as TotalDebit, JVDetails.CrAmount as TotalCredit, BankReconcilation.BankDate as State from JVDetails
inner join JVMaster on JVDetails.JVMasterId=JVMaster.JvMasterId
left join BankReconcilation on JVMaster.JvNumber=BankReconcilation.JvNo
where JVDetails.FeeTypeId='" + model.objGetBankReconcilationModelView.BankID + "' and JVDetails.FeeTypeSubId='" + model.objGetBankReconcilationModelView.BankSubID + "' and JVMaster.TransactionDate Between '" + model.objGetBankReconcilationModelView.StartDate + "' and '" + model.objGetBankReconcilationModelView.EndDate + "' and isnull(BankReconcilation.BankDate,'')!=''";
}
}
else
{
if (model.objGetBankReconcilationModelView.BankSubID == 0)
{
sql = @"select dbo.GetNepaliDate(JVMaster.TransactionDate)TransactionDate, JVMaster.JvNumber, JVMaster.Narration1 as Narration,JVDetails.DrAmount as TotalDebit, JVDetails.CrAmount as TotalCredit, BankReconcilation.BankDate as State from JVDetails
inner join JVMaster on JVDetails.JVMasterId=JVMaster.JvMasterId
left join BankReconcilation on JVMaster.JvNumber=BankReconcilation.JvNo
where JVDetails.FeeTypeId='" + model.objGetBankReconcilationModelView.BankID + "' and JVMaster.TransactionDate Between '" + model.objGetBankReconcilationModelView.StartDate + "' and '" + model.objGetBankReconcilationModelView.EndDate + "' and isnull(BankReconcilation.BankDate,'')=''";
}
else
{
sql = @"select dbo.GetNepaliDate(JVMaster.TransactionDate)TransactionDate, JVMaster.JvNumber, JVMaster.Narration1 as Narration,JVDetails.DrAmount as TotalDebit, JVDetails.CrAmount as TotalCredit, BankReconcilation.BankDate as State from JVDetails
inner join JVMaster on JVDetails.JVMasterId=JVMaster.JvMasterId
left join BankReconcilation on JVMaster.JvNumber=BankReconcilation.JvNo
where JVDetails.FeeTypeId='" + model.objGetBankReconcilationModelView.BankID + "' and JVDetails.FeeTypeSubId='" + model.objGetBankReconcilationModelView.BankSubID + "' and JVMaster.TransactionDate Between '" + model.objGetBankReconcilationModelView.StartDate + "' and '" + model.objGetBankReconcilationModelView.EndDate + "' and isnull(BankReconcilation.BankDate,'')=''";
}
}
return(ent.Database.SqlQuery <GetBankReconcilationModelView>(sql).ToList());
}
}
public int InsertOpeningBalance(JVMasterModel model)
{
int i = 0;
using (EHMSEntities ent = new EHMSEntities())
{
int fyid = Utility.GetCurrentFiscalYearID();
foreach (var item in model.objOpeningBalanceModelViewList)
{
int count = ent.OpeningBalances.Where(x => x.AccountHeadId == item.AccountHeadId && x.FiscalYear >= fyid).Count();
if (count > 0)
{
var OpeningBal = (from x in ent.OpeningBalances
where (x.AccountHeadId == item.AccountHeadId && x.FiscalYear == fyid)
select x);
foreach (OpeningBalance opng in OpeningBal)
{
opng.DrAmount = item.DrAmount;
opng.CrAmount = item.CrAmount;
}
}
else
{
if (item.DrAmount + item.CrAmount != 0)
{
var objOpening = new OpeningBalance()
{
FiscalYear = fyid,
AccountHeadId = item.AccountHeadId,
DrAmount = item.DrAmount,
CrAmount = item.CrAmount,
Status = true
};
ent.OpeningBalances.Add(objOpening);
}
}
i = ent.SaveChanges();
}
return(i);
}
}
public ActionResult IncomeAndExpenditure(JVMasterModel model)
{
return(PartialView("_IncomeAndExpenditure", model));
}
public ActionResult IncomeAndExpenditure()
{
JVMasterModel model = new JVMasterModel();
return(View(model));
}
public ActionResult BalanceSheet(JVMasterModel model)
{
return(PartialView("_BalanceSheet", model));
}
public ActionResult BalanceSheet()
{
JVMasterModel model = new JVMasterModel();
return(View(model));
}
public ActionResult BSSchedule(JVMasterModel model)
{
return(PartialView("_BSSchedule", model));
}
public ActionResult BSSchedule()
{
JVMasterModel model = new JVMasterModel();
return(View(model));
}
