public static void Update(Invoices invoice, decimal?value, int?stairCaseId, string description, int?redistributionId,
string issueNumber, DateTime?issueDate)
{
Invoices result;
result = GetContext(true).Invoices.FirstOrDefault(c => c.Id == invoice.Id);
if (result != null)
{
result.Value = value;
result.Description = description;
result.Id_StairCase = stairCaseId;
result.id_Redistributiontype = redistributionId;
result.issueDate = issueDate;
result.issueNumber = issueNumber;
GetContext().Entry(result).CurrentValues.SetValues(result);
if (invoice.Id_EstateExpense.HasValue)
{
var ee = AssociationExpensesManager.GetById(invoice.Id_EstateExpense.Value);
if (ee != null && ee.Id_Expense != (int)Expense.Diverse)
{
ApartmentExpensesManager.UpdateApartmentExpenses(ee, value, stairCaseId, result.id_assCounter);
}
}
GetContext().SaveChanges();
}
}
protected void gvExpensesPerIndex_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
var id_exes = Request.QueryString["id_exes"];
int idExpenseEstate;
if (int.TryParse(id_exes, out idExpenseEstate))
{
var row = gvExpensesPerIndex.Rows[e.RowIndex];
int apartmentExpenseId;
if (int.TryParse(gvExpensesPerIndex.DataKeys[e.RowIndex].Value.ToString(), out apartmentExpenseId))
{
if (row.Cells.Count > 3 && row.Cells[3].Controls.Count > 0 && row.Cells[3].Controls[0] is TextBox)
{
var cellOld = row.Cells[3].Controls[0] as TextBox;
decimal newValue;
if (!string.IsNullOrEmpty(cellOld.Text) && decimal.TryParse(cellOld.Text, out newValue))
{
ApartmentExpensesManager.UpdateApartmentExpense(apartmentExpenseId, newValue);
}
else
{
ApartmentExpensesManager.UpdateApartmentExpense(apartmentExpenseId, null);
}
}
}
}
InitializeGridViewExpensesPerIndex(new DataTable(), idExpenseEstate);
gvExpensesPerIndex.EditIndex = -1;
gvExpensesPerIndex.DataBind();
}
private void InitializeGridViewExpensesPerIndex(DataTable dt, int esexId)
{
var apartments = ApartmentsManager.GetForIndividual(Association.Id, esexId);
AssociationExpenses ee = AssociationExpensesManager.GetById(esexId);
foreach (var apartment in apartments)
{
ApartmentExpensesManager.ConfigureIndividual(ee, apartment);
string query = @"
Select
TE.Id as Id,
A.Number as Apartament,
TE.Value as 'Valoare'
from ApartmentExpenses TE
Inner join Apartments A
ON TE.Id_Tenant = A.Id
where Id_EstateExpense = " + esexId + " and Id_Tenant = " + apartment.Id +
" and A.Id_Estate = " + Association.Id;
SqlConnection cnn = new SqlConnection("data source=HOME\\SQLEXPRESS;initial catalog=Administratoro;integrated security=True;MultipleActiveResultSets=True;");
SqlCommand cmd = new SqlCommand(query, cnn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
}
ViewState["dtIndividual"] = dt;
gvExpensesPerIndex.DataSource = dt;
gvExpensesPerIndex.DataBind();
}
protected void lblExpenseMeessageDownload_Click(object sender, EventArgs e)
{
DataTable dt;
int stairCase;
if (int.TryParse(drpDisplayMode.SelectedValue, out stairCase))
{
dt = ApartmentExpensesManager.GetMonthlyRaportAsDataTable(Association.Id, Year, Month, stairCase);
dt.Rows.Add(new TableCell());
}
else
{
dt = ApartmentExpensesManager.GetMonthlyRaportAsDataTable(Association.Id, Year, Month, null);
}
MemoryStream stream;
using (XLWorkbook wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add(dt, "Cheltuieli");
ws.Row(1).InsertRowsAbove(1);
ws.Cells("A1").Value = ws.Cell(2, 1).Value;
ws.Range("A1:A2").Merge();
ws.Cells("B1").Value = ws.Cell(2, 2).Value;
ws.Range("B1:B2").Merge();
ws.Cells("C1").Value = ws.Cell(3, 2).Value;
ws.Range("C1:C2").Merge();
ws.Cells("D1").Value = ws.Cell(4, 2).Value;
ws.Range("D1:D2").Merge();
var expenses = AssociationExpensesManager.GetByMonthAndYearNotDisabled(Association.Id, Year, Month).GroupBy(ee => ee.Id_ExpenseType).OrderBy(er => er.Key);
char position = 'E';
foreach (var expense in expenses)
{
var tempPosition = position;
position = (char)(position + expense.ToList().Count - 1);
var range = string.Format("{0}1:{1}1", tempPosition.ToString(), position.ToString());
ws.Cells(tempPosition + "1").Value = expense.FirstOrDefault().ExpenseTypes.Name;
ws.Range(range).Merge();
position = (char)(position + 1);
}
stream = GetStream(wb);
}
string myName = Server.UrlEncode("Cheltuieli" + "_" + DateTime.Now.ToShortDateString() + ".xlsx");
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + myName);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.Flush();
Response.SuppressContent = true;
}
private void InitializeGridView(int?stairCase = null)
{
RecalculationManager.RecalculateMonthlyExpenses(Association.Id, Year, Month);
var dt = ApartmentExpensesManager.GetMonthlyRaportAsDataTable(Association.Id, Year, Month, stairCase);
ViewState["dt"] = dt;
GridView1.DataSource = dt;
GridView1.DataBind();
}
private static decimal?RedistributeValuePerApartment(AssociationExpenses associationExpense, Apartments apartment)
{
decimal?result = null;
// todo change this to be for index expense or add new if branch
//if (associationExpense.SplitPerStairCase.HasValue && associationExpense.SplitPerStairCase.Value)
//{
var redistributeSum = GetRedistributeValueForIndexExpenseForStairCase(associationExpense, apartment);
var allApartmentsOnSamecounter = ApartmentExpensesManager.GetApartmentsOnSameCounter(associationExpense.Id, apartment.Id_StairCase).Count();
if (redistributeSum.HasValue && allApartmentsOnSamecounter != 0)
{
if (associationExpense.Id_ExpenseType == (int)ExpenseType.PerIndex && !AssociationExpensesManager.HasCounterOfExpense(apartment.Id, associationExpense.Id_Expense))
{
}
else
{
result = redistributeSum / allApartmentsOnSamecounter;
}
}
//}
//else
//{
// var redistributeVal = CalculateRedistributeValue(associationExpense.Id);
// int allApartmentsNr;
// if (associationExpense.Id_ExpenseType == (int)ExpenseType.PerIndex)
// {
// IEnumerable<Apartments> apartments = AssociationExpensesManager.GetApartmentsNrThatShouldRedistributeTo(associationExpense.Id);
// allApartmentsNr = apartments.Count();
// }
// else
// {
// allApartmentsNr = associationExpense.Associations.Apartments.Count();
// }
// if (redistributeVal != null && redistributeVal.HasValue && allApartmentsNr != 0)
// {
// if (associationExpense.Id_ExpenseType == (int)ExpenseType.PerIndex && !AssociationExpensesManager.HasCounterOfExpense(apartment.Id, associationExpense.Id_Expense))
// {
// }
// else
// {
// result = redistributeVal.Value / allApartmentsNr;
// }
// }
//}
return(result);
}
public static void ConfigurePerIndex(Associations association, int year, int month)
{
IEnumerable <Expenses> allExpenses = ExpensesManager.GetAllExpenses();
var associationExpenses = GetByMonthAndYearNotDisabled(association.Id, year, month);
foreach (Expenses expense in allExpenses)
{
var associationExpense = associationExpenses.FirstOrDefault(ea => ea.Id_Expense == expense.Id);
if (associationExpense != null)
{
var apartments = ApartmentsManager.GetAllThatAreRegisteredWithSpecificCounters(association.Id, associationExpense.Id);
ApartmentExpensesManager.ConfigurePerIndex(associationExpense, apartments);
}
}
}
private static decimal?RedistributeValuePerConsumption(AssociationExpenses associationExpense, Apartments apartment, IEnumerable <ApartmentExpenses> apartmentExpenses)
{
decimal?result = null;
decimal apartmentConsumption = GetApartmentConsumprionAsDecimal(apartmentExpenses);
var redistributeValue = GetRedistributeValuePerIndex(associationExpense, apartment.Id_StairCase);
decimal?sumOfIndexes = ApartmentExpensesManager.GetSumOfIndexesOnSameCounter(associationExpense.Id, apartment.Id_StairCase);
if (redistributeValue.HasValue && sumOfIndexes.HasValue && sumOfIndexes.Value != 0)
{
result = (apartmentConsumption / sumOfIndexes.Value) * redistributeValue;
}
return(result);
}
private static decimal?GetRedistributeValueForIndexExpenseForStairCase(AssociationExpenses associationExpense, Apartments apartment)
{
decimal?result = null;
decimal?sumOfInvoiceForCounter = GetSumOfInvoiceForStairCaseCounter(associationExpense, apartment.Id_StairCase);
if (sumOfInvoiceForCounter.HasValue)
{
var sumOfExpensesForCounter = ApartmentExpensesManager.GetConsumption(associationExpense, apartment.Id_StairCase);
if (sumOfExpensesForCounter.HasValue)
{
result = sumOfInvoiceForCounter - sumOfExpensesForCounter;
}
}
return(result);
}
private void InitializeGridViewExpensesPerIndex(DataTable dt, int esexId)
{
AssociationExpenses ee = AssociationExpensesManager.GetById(esexId);
int stairCase;
List <Administratoro.DAL.Apartments> apartments;
if (Association.HasStaircase && !string.IsNullOrEmpty(drpStairCases.SelectedValue) && int.TryParse(drpStairCases.SelectedValue, out stairCase))
{
apartments = ApartmentsManager.GetAllThatAreRegisteredWithSpecificCounters(Association.Id, esexId, stairCase);
}
else
{
apartments = ApartmentsManager.GetAllThatAreRegisteredWithSpecificCounters(Association.Id, esexId);
}
ApartmentExpensesManager.ConfigurePerIndex(ee, apartments);
foreach (var apartment in apartments)
{
string query = @"
Select
AE.Id as Id,
A.Number as Apartament,
cast(AE.IndexOld as float) as 'Index vechi',
cast(AE.IndexNew as float) as 'Index nou',
(AE.IndexNew - AE.IndexOld ) as 'Consum',
AE.Value as 'Valoare'
from ApartmentExpenses AE
Inner join Apartments A
ON AE.Id_Tenant = A.Id
where Id_EstateExpense = " + esexId + " and Id_Tenant = " + apartment.Id +
" and A.Id_Estate = " + Association.Id;
SqlConnection cnn = new SqlConnection("data source=HOME\\SQLEXPRESS;initial catalog=Administratoro;integrated security=True;MultipleActiveResultSets=True;");
SqlCommand cmd = new SqlCommand(query, cnn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
}
ViewState["dtPerIndex"] = dt;
gvExpensesPerIndex.DataSource = dt;
gvExpensesPerIndex.DataBind();
}
private static decimal?RedistributeValuePerApartmentDependents(AssociationExpenses associationExpense, Apartments apartment)
{
decimal?result = null;
//if (associationExpense.SplitPerStairCase.HasValue && associationExpense.SplitPerStairCase.Value)
//{
var redistributeSum = GetRedistributeValueForIndexExpenseForStairCase(associationExpense, apartment);
var allApartmentsDependents = ApartmentExpensesManager.GetApartmentsOnSameCounter(associationExpense.Id, apartment.Id_StairCase).Sum(t => t.Dependents);
if (redistributeSum.HasValue && allApartmentsDependents != 0)
{
if (associationExpense.Id_ExpenseType == (int)ExpenseType.PerIndex && !AssociationExpensesManager.HasCounterOfExpense(apartment.Id, associationExpense.Id_Expense))
{
}
else
{
result = redistributeSum * apartment.Dependents / allApartmentsDependents;
}
}
//}
//else
//{
// var allApartmentsDependentsu = AssociationExpensesManager.GetApartmentsNrThatShouldRedistributeTo(associationExpense.Id).Sum(e => e.Dependents);
//if (allApartmentsDependents != 0)
//{
// var redistributeVal = CalculateRedistributeValue(associationExpense.Id);
// if (redistributeVal != null && redistributeVal.HasValue && allApartmentsDependents != 0)
// {
// if (associationExpense.Id_ExpenseType == (int)ExpenseType.PerIndex && !AssociationExpensesManager.HasCounterOfExpense(apartment.Id, associationExpense.Id_Expense))
// {
// }
// else
// {
// result = redistributeVal.Value * apartment.Dependents / allApartmentsDependents;
// }
// }
//}
//}
return(result);
}
private static void RelalculateExpense(AssociationExpenses item)
{
switch (item.Id_ExpenseType)
{
case (int)ExpenseType.PerNrTenants:
case (int)ExpenseType.PerCotaIndiviza:
if (item.Invoices.Count != 1)
{
return;
}
var invoice = item.Invoices.FirstOrDefault();
if (invoice != null && invoice.Value.HasValue)
{
ApartmentExpensesManager.UpdateApartmentExpenses(invoice.AssociationExpenses, invoice.Value.Value, null);
}
break;
case (int)ExpenseType.PerIndex:
//redistribute
break;
}
}
public static decimal?GetRedistributeValuePerIndex(AssociationExpenses associationExpense, int?stairCase)
{
decimal?result = null;
var invoice = associationExpense.Invoices.FirstOrDefault(i => i.Id_StairCase == null && i.Id_EstateExpense == associationExpense.Id);
var assCountersForThisExpense = AssociationCountersManager.GetByExpenseAndStairCase(associationExpense, stairCase);
if (assCountersForThisExpense != null)
{
var pricePerUnit = UnitPricesManager.GetPrice(associationExpense.Id, assCountersForThisExpense.AssociationCountersStairCase.FirstOrDefault().Id_StairCase);
decimal?sumOfIndexes = ApartmentExpensesManager.GetSumOfIndexesOnSameCounter(associationExpense.Id, assCountersForThisExpense.AssociationCountersStairCase.FirstOrDefault().Id_StairCase);
if (sumOfIndexes.HasValue & invoice != null && pricePerUnit.HasValue)
{
var consummedIndices = InvoiceIndexesManager.GetByInvoiceAndCounterFirst(invoice, assCountersForThisExpense);
if (consummedIndices != null && consummedIndices.IndexOld.HasValue && consummedIndices.IndexNew.HasValue)
{
result = ((consummedIndices.IndexNew.Value - consummedIndices.IndexOld.Value) - sumOfIndexes.Value) * pricePerUnit.Value;
}
}
}
return(result);
}
public static void UpdatePricePerUnit(int idAssociationExpense, decimal?newPricePerUnit, int?stairCase)
{
UnitPricesManager.AddOrUpdate(stairCase, idAssociationExpense, newPricePerUnit);
ApartmentExpensesManager.UpdateValueForPriceUpdate(idAssociationExpense, newPricePerUnit);
}