public JsonResult AddTenant(FinancialModel model)
{
AddTenantToPropertyModel tenant = new AddTenantToPropertyModel();
var user = User.Identity.Name;
var login = AccountService.GetLoginByEmail(user);
var ten = AccountService.GetLoginByEmail(model.TenantToPropertyModel.TenantEmail);
if (ten == null)
{
tenant.TenantEmail = model.TenantToPropertyModel.TenantEmail;
tenant.StartDate = model.TenantToPropertyModel.StartDate;
tenant.EndDate = model.TenantToPropertyModel.EndDate;
tenant.PaymentAmount = model.TenantToPropertyModel.PaymentAmount;
tenant.PaymentFrequencyId = model.TenantToPropertyModel.PaymentFrequencyId;
tenant.PropertyId = model.PropId;
//var TenantEmailResult = SendInvitationEmailToTenant(tenant);
return(Json(new { Success = false, NewPropId = model.PropId, Todo = "Send email", ErrorMsg = "Cannot find person in login table!" }));
}
else
{
var person = AccountService.GetPersonByLoginId(ten.Id);
var result = PropertyService.AddTenantToProperty(login, person.Id, model.PropId, model.TenantToPropertyModel.StartDate,
model.TenantToPropertyModel.EndDate, model.TenantToPropertyModel.PaymentFrequencyId, model.TenantToPropertyModel.PaymentAmount);
if (result.IsSuccess)
{
return(Json(new { Sucess = true, Msg = "Added!", result = "Redirect", url = Url.Action("Index", "PropertyOwners") }));
}
else
{
return(Json(new { Sucess = false, Msg = result.ErrorMessage, redirect = "Redirect", url = Url.Action("Index", "PropertyOwners") }));
}
}
}
public ActionResult GetPropertyFinanceDetails(int propId)
{
var prop = db.Property.FirstOrDefault(x => x.Id == propId);
var propFinance = db.PropertyFinance.FirstOrDefault(x => x.PropertyId == propId);
var propRepayments = PropertyService.GetRepayments(propId);
var propExpense = PropertyService.GetExpenses(propId);
var homeValues = PropertyService.GetHomeValues(propId);
var rentalPayments = PropertyService.GetRentalPayment(propId);
var financeReport = DataService.ExecuteStoredProcedure <PropertyFinanceResultModel>("sp_FinanceReport", new GetFinanceReportModel {
PropertyId = propId
});
var model = new FinancialModel
{
PropId = propId,
YearBuilt = prop.YearBuilt ?? 1900,
PurchasePrice = propFinance?.PurchasePrice ?? 0,
Mortgage = propFinance?.Mortgage ?? 0,
CurrentHomeValue = propFinance?.CurrentHomeValue ?? 0,
HomeValueType = prop.PropertyHomeValue.FirstOrDefault(x => x.PropertyId == propId)?.PropertyHomeValueType?.Id ?? 0,
PropertyValueType = prop.PropertyHomeValue.FirstOrDefault(x => x.PropertyId == propId)?.PropertyHomeValueType?.HomeValueType,
HomeValues = homeValues,
Repayments = propRepayments.ToList(),
Expenses = propExpense.ToList(),
RentalPayments = rentalPayments,
FinanceReport = financeReport.First()
};
return(Json(model, JsonRequestBehavior.AllowGet));
}
public FinancialModel GetFinancialStatus()
{
var accounts = GetAccountStatus().account.Where(x => x.status == "open");
if (accounts == null)
{
return(null);
}
int totalSalaryFor6Months = 0;
int claims = 0;
var currentYear = DateTime.Now.Year;
var currentMonth = DateTime.Now.Month;
var lastMonth = DateTime.Now.AddMonths(-1).Month;
if (DateTime.Now.Day > 15)
{
lastMonth = DateTime.Now.Month;
}
foreach (var acc in accounts)
{
var salary = GetAccountTransaction(acc.accountID, "1", "1500", DateTime.Now, DateTime.Now.AddMonths(-6));
int currentSalary = 0;
if (salary.transaction != null)
{
var temp = salary.transaction.Where(x => x.category == "Laun");
foreach (var tempsal in temp)
{
currentSalary += Convert.ToInt32(tempsal.amount);
}
}
totalSalaryFor6Months += Convert.ToInt32(currentSalary * -1);
claims += Convert.ToInt32(GetTotalClaims(new DateTime(currentYear, currentMonth, 1), new DateTime(currentMonth, lastMonth, 15), acc.accountID));
}
var model = new FinancialModel();
totalSalaryFor6Months = (totalSalaryFor6Months / 6) * 5000;
model.TotalAmount = totalSalaryFor6Months;
model.AmountAfterBills = totalSalaryFor6Months - 80000;
if (model.AmountAfterBills < 100000)
{
model.AllegedFoodCost = 30000;
}
if (model.AmountAfterBills > 100000 && model.AmountAfterBills < 200000)
{
model.AllegedFoodCost = 50000;
}
if (model.AmountAfterBills > 200000 && model.AmountAfterBills < 400000)
{
model.AllegedFoodCost = 80000;
}
if (model.AmountAfterBills > 400000)
{
model.AllegedFoodCost = 80000;
}
return(model);
}
public IActionResult Create(FinancialModel fmodel)
{
try
{
return(View("Index", fmodel));
}
catch
{
return(View());
}
}
public ActionResult Index()
{
PersonalizedCardProvider provider = new PersonalizedCardProvider();
FinancialModel finmod = arionHelper.GetFinancialStatus();
SuperModel model = new SuperModel();
model.Finance = finmod;
model.FoodCards = provider.GetMealPlan("low");
return(View(model));
}
public static PropertyFinance AddPropertyFinance(Login user, FinancialModel model, int PropertyId, decimal TotalRepayment)
{
using (var db = new KeysEntities())
{
try
{
//var oldFinancial = db.PropertyFinance.Where(x => x.PropertyId == PropertyId).First();
var newFinancial = new PropertyFinance();
var newHomeValues = new PropertyHomeValue();
var totalExpense = db.PropertyExpense.Where(t => t.PropertyId == PropertyId).Select(t => t.Amount).DefaultIfEmpty().Sum();
newFinancial.CurrentHomeValue = model.CurrentHomeValue;
newFinancial.PurchasePrice = model.PurchasePrice;
newFinancial.Mortgage = model.Mortgage;
newFinancial.PropertyId = PropertyId;
newFinancial.PurchasePrice = model.PurchasePrice;
newFinancial.TotalRepayment = TotalRepayment;
newFinancial.TotalExpense = totalExpense;
//db.SaveChanges();
db.PropertyFinance.Add(newFinancial);
// Add the homeValue to db.PropertyHomeValue with the property id into propertyHomeValue
newHomeValues.PropertyId = PropertyId;
newHomeValues.Value = model.CurrentHomeValue ?? 0;
newHomeValues.Date = DateTime.Today;
newHomeValues.HomeValueTypeId = model.HomeValueType;
newHomeValues.CreatedBy = user.UserName;
newHomeValues.CreatedOn = DateTime.Today;
db.PropertyHomeValue.Add(newHomeValues);
db.SaveChanges();
return(newFinancial);
}
catch (Exception ex)
{
return(null);
}
}
}
public FinancialApiController(FinancialModel financial)
{
this._financial = financial;
}
public ActionResult Index(int period1 = 1, int period2 = 12)
{
DataSet invoiceTable = new DataSet();
DataSet customerTable = new DataSet();
DataSet FinancialTable = new DataSet();
DataSet companyTable = new DataSet();
FinancialModel FinancialDashboardModel = new FinancialModel();
string connectionString = FirstREST.SqlConnection.GetConnectionString();
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("Select * From dbo.Company", connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(companyTable, "company");
SaftFileDateModel temp = new SaftFileDateModel();
temp.startDate = companyTable.Tables["company"].Rows[0].Field <DateTime>("StartDate");
temp.endDate = companyTable.Tables["company"].Rows[0].Field <DateTime>("EndDate");
FinancialDashboardModel.SaftInfo = temp;
}
}
}
string monthQuery = "";
if (period1 == period2)
{
monthQuery = " Month=" + period1;
}
else if (period1 < period2)
{
monthQuery = " (Month >=" + period1 + "AND Month <=" + period2 + ")";
}
else if (period1 > period2 && period2 < FinancialDashboardModel.SaftInfo.startDate.Month)
{
monthQuery = " (Month >=" + period1 + "AND Month <=12) OR (Month >=1 AND Month <=" + period2 + ")";
}
else
{
monthQuery = " Month LIKE '%%'";
}
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("Select Year, Month, AccountID, accountDescription, Amount, IsCredit From dbo.MonthlyAccountSums INNER JOIN dbo.Account on MonthlyAccountSums.AccountID=Account.id WHERE " + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(invoiceTable, "Account");
foreach (DataRow row in invoiceTable.Tables["Account"].Rows)
{
AccountModel tempAccount = new AccountModel();
tempAccount.accountId = row.Field <Int64>("AccountId");
tempAccount.accountDescription = row.Field <string>("accountDescription");
tempAccount.amount = row.Field <Int64>("Amount");
FinancialDashboardModel.CompanyAccounts.Add(tempAccount);
}
}
}
using (SqlCommand command = new SqlCommand("Select * from dbo.Journal", connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(customerTable, "Journal");
foreach (DataRow row in customerTable.Tables["Journal"].Rows)
{
JournalModel tempJournal = new JournalModel();
tempJournal.journalID = row.Field <string>("JournalID");
tempJournal.journalDescription = row.Field <string>("Description");
tempJournal.journalTotalCredit = row.Field <double>("TotalCredit");
tempJournal.journalTotalDebit = row.Field <double>("TotalDebit");
FinancialDashboardModel.CompanyJournals.Add(tempJournal);
}
}
}
ProfitsAndLossesModel tempModel = new ProfitsAndLossesModel();
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '71%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "sales");
tempModel.sales = 0;
foreach (DataRow row in FinancialTable.Tables["sales"].Rows)
{
tempModel.sales += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '72%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "services");
tempModel.services = 0;
foreach (DataRow row in FinancialTable.Tables["services"].Rows)
{
tempModel.services += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '61%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "salesExpenses");
tempModel.salesExpenses = 0;
foreach (DataRow row in FinancialTable.Tables["salesExpenses"].Rows)
{
tempModel.salesExpenses += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '62%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "administrativeExpenses");
tempModel.administrativeExpenses = 0;
foreach (DataRow row in FinancialTable.Tables["administrativeExpenses"].Rows)
{
tempModel.administrativeExpenses += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '63%' OR AccountId LIKE '64%' OR AccountId LIKE '65%' OR AccountId LIKE '66%' OR AccountId LIKE '67%' or AccountId LIKE '68%' or AccountId LIKE '69%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "otherExpenses");
tempModel.otherExpenses = 0;
foreach (DataRow row in FinancialTable.Tables["otherExpenses"].Rows)
{
tempModel.otherExpenses += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '73%' OR AccountId LIKE '74%' OR AccountId LIKE '75%' OR AccountId LIKE '76%' OR AccountId LIKE '77%' or AccountId LIKE '78%' or AccountId LIKE '79%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "otherIncomes");
tempModel.otherIncomes = 0;
foreach (DataRow row in FinancialTable.Tables["otherIncomes"].Rows)
{
tempModel.otherIncomes += row.Field <Int64>("Amount");
}
}
}
tempModel.totalSalesAndRevenue = tempModel.sales + tempModel.services + tempModel.otherIncomes;
tempModel.totalOperatingCosts = tempModel.salesExpenses + tempModel.administrativeExpenses + tempModel.otherExpenses;
tempModel.netIncome = tempModel.totalSalesAndRevenue - tempModel.totalOperatingCosts;
FinancialDashboardModel.ProfitsAndLosses.Add(tempModel);
Int64 revenue = 0;
Int64 operatingExpensesWithoutAmortizations = 0;
Int64 amortizationAndDepreciation = 0;
Int64 currentLiabilities = 0;
Int64 inventories = 0;
Int64 accountsReceivable = 0;
Int64 liquidAssets = 0;
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '22%' OR AccountId LIKE '23%' OR AccountId LIKE '24%' OR AccountId LIKE '25%' OR AccountId LIKE '26%' OR AccountId LIKE '27%' OR AccountId LIKE '28%' OR AccountId LIKE '29%' OR AccountId LIKE '6%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "currentLiabilities");
foreach (DataRow row in FinancialTable.Tables["currentLiabilities"].Rows)
{
currentLiabilities += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '3%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "inventories");
foreach (DataRow row in FinancialTable.Tables["inventories"].Rows)
{
inventories += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '21%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "accountsReceivable");
foreach (DataRow row in FinancialTable.Tables["accountsReceivable"].Rows)
{
accountsReceivable += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '1%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "liquidAssets");
foreach (DataRow row in FinancialTable.Tables["liquidAssets"].Rows)
{
liquidAssets += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '7%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "revenue");
foreach (DataRow row in FinancialTable.Tables["revenue"].Rows)
{
revenue += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '61%' OR AccountId LIKE '62%' OR AccountId LIKE '63%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "operatingExpensesWithoutAmortizations");
foreach (DataRow row in FinancialTable.Tables["operatingExpensesWithoutAmortizations"].Rows)
{
operatingExpensesWithoutAmortizations += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '64%' OR AccountId LIKE '65%' OR AccountId LIKE '66%' OR AccountId LIKE '67%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "amortizationAndDepreciation");
foreach (DataRow row in FinancialTable.Tables["amortizationAndDepreciation"].Rows)
{
amortizationAndDepreciation += row.Field <Int64>("Amount");
}
}
}
FinancialDashboardModel.CompanyIndicators.EBIT = revenue - operatingExpensesWithoutAmortizations + amortizationAndDepreciation;
FinancialDashboardModel.CompanyIndicators.EBITDA = revenue - operatingExpensesWithoutAmortizations;
FinancialDashboardModel.CompanyIndicators.quickRatio = (liquidAssets + accountsReceivable + inventories + revenue) / (accountsReceivable);
FinancialDashboardModel.CompanyIndicators.currentRatio = (liquidAssets + accountsReceivable + revenue) / (accountsReceivable);
}
return(View(FinancialDashboardModel));
}
public void Update(FinancialModel model)
{
_queueProducer.Send(model);
}
// GET: FinancialController/Edit/
public ActionResult Edit(FinancialModel fmodel)
{
return(View(fmodel));
}
public IActionResult Create()
{
FinancialModel fmodel = new FinancialModel();
return(View(fmodel));
}
public JsonResult UpdateFinance(FinancialModel model)
{
var status = true;
decimal actualTotalRepayment = 0;
var message = "Record added successfully";
var data = model;
// AddTenantToPropertyModel tenant = new AddTenantToPropertyModel();
//*********** AddNewProperty
var user = User.Identity.Name;
var login = AccountService.GetLoginByEmail(user);
//var newProp = PropertyOwnerService.AddOnboardProperty(login, model);
////*********** AddRepayments
var newRepayment = new PropertyRepayment();
newRepayment = PropertyOwnerService.AddOnboardRepayment(login, model.Repayments, model.PropId);
decimal _totalRepayment = 0;
int _nosWeeks = 0;
int _nosFortnights = 0;
int _nosMonthly = 0;
if (newRepayment != null)
{
foreach (Service.Models.RepaymentViewModel repayment in model.Repayments)
{
switch (repayment.FrequencyType)
{
case 1: // Weekly
// find the nos of weeks in datediff(StartDate, EndDate)
_nosWeeks = ((newRepayment.EndDate - newRepayment.StartDate) ?? TimeSpan.Zero).Days / 7;
// _totalAmount = nos weeks * amount
_totalRepayment = _nosWeeks * newRepayment.Amount;
break;
case 2: // Fortnightly
// find the nos of Fortnights in datediff(StartDate, EndDate)
_nosFortnights = ((newRepayment.EndDate - newRepayment.StartDate) ?? TimeSpan.Zero).Days / 14;
// _totalAmount = nos weeks * amount
_totalRepayment = _nosFortnights * newRepayment.Amount;
break;
case 3: //Monthly
// find the nos of Monthls in datediff(StartDate, EndDate)
_nosMonthly = ((newRepayment.EndDate - newRepayment.StartDate) ?? TimeSpan.Zero).Days / 30;
_totalRepayment = _nosMonthly * newRepayment.Amount;
// _totalAmount = nos Monthls * amount
break;
}
actualTotalRepayment += _totalRepayment;
}
//*****AddExpenses
var newExpense = new PropertyExpense();
newExpense = PropertyOwnerService.AddOnboardExpense(login, model.Expenses, model.PropId);
//******AddFinancial
var newFinancial = new PropertyFinance();
newFinancial = PropertyOwnerService.AddPropertyFinance(login, model, model.PropId, actualTotalRepayment);
var newRentalPayment = new PropertyRentalPayment();
// newRentalPayment = PropertyOwnerService.AddOnboardRentalPayment(login, model.RentalPayments, model.PropId);
}
return(Json(new { success = status, message = message }));
}
public IActionResult Put(string uid, [FromBody] FinancialModel model)
{
model.Uid = uid;
_useCase.Update(model);
return(Ok(model));
}
