public EstateCases(int EstateID = 0, string EstateName = null,
int _linkedObjID = 0)
{
Estate = new Estates {
EstatedID = EstateID
};
if (!string.IsNullOrWhiteSpace(EstateName))
{
Estate.EstateName = EstateName;
}
CaseActionCollection = new ObservableCollection <CaseActions>();
HistoryCollection = new ObservableCollection <CaseHistoryItem>();
LinkedObjectID = _linkedObjID;
CaseData = new CaseData();
}
public static Estates GetEstatedByUnitID(int UnitID)
{
string q = "select core.Estates.ID, core.Estates.Name from Core.Estates " +
"inner join core.Units on core.Estates.ID = core.Units.EstateID " +
"where core.Units.ID = " + UnitID.ToString();
DBConnectionObject db = GlobalVariables.GetConnection();
DataTable dt = db.Connection.GetDataTable(q);
Estates r = new Estates();
foreach (DataRow dr in dt.Rows)
{
r.EstatedID = Convert.ToInt32(dr[0]);
r.EstateName = dr[1].ToString();
}
return(r);
}
public static Estates GetExpenditurebyPO(int ponumber)
{
//each db call is nested so only executed if previous call does not return an error.
//first get budetid, schedule name and heading
string q = "select core.PurchaseOrders.BudgetID, core.PurchaseOrders.scheduleName, " +
"core.PurchaseOrders.Heading, core.purchaseorders.headingID from core.PurchaseOrders " +
"where core.PurchaseOrders.id = " + ponumber.ToString();
DBConnectionObject db = GlobalVariables.GetConnection();
DataTable dt = db.Connection.GetDataTable(q);
Estates ReturnEst = new Estates();
if (dt.Rows.Count > 0)
{
DataRow d = dt.Rows[0];
if (d[0].ToString() != "Error")
{
ReturnEst.BudgetId = Convert.ToInt32(d[0]);
ReturnEst.SCScheduleName = d[1].ToString();
ReturnEst.SCHeading = d[2].ToString();
ReturnEst.HeadingID = Convert.ToInt32(d[3]);
//Get expenditure details based on the above
q = "select sum(core.Transactions.TransAmount) from core.Transactions where " +
"budgetID = " + ReturnEst.BudgetId + " and budgetSchedule = '" +
ReturnEst.SCScheduleName + "' and HeadingID = " + ReturnEst.HeadingID;
DataTable dt1 = db.Connection.GetDataTable(q);
if (dt1.Rows.Count > 0)
{
DataRow d1 = dt1.Rows[0];
if (d1[0] == DBNull.Value || d1[0].ToString() == "Error")
{
ReturnEst.TotalExp = 0;
ReturnEst.TotalExpstr = Controls.CurrencyString(ReturnEst.TotalExp);
}
else
{
ReturnEst.TotalExp = Convert.ToDouble(d1[0]);
ReturnEst.TotalExpstr = Controls.CurrencyString(ReturnEst.TotalExp);
}
d1 = null;
dt1 = null;
//gets budgeted amount for scehdule and heading
q = "select sum(core.ServiceChargeBudgetDetail.Cost) from core.ServiceChargeBudgetDetail " +
"where BudgetID = " + ReturnEst.BudgetId + " and ScheduleName = '" + ReturnEst.SCScheduleName +
"' and Heading = '" + ReturnEst.SCHeading + "'";
DataTable dt2 = db.Connection.GetDataTable(q);
if (dt2.Rows.Count > 0)
{
DataRow d2 = dt2.Rows[0];
if (d2[0] == DBNull.Value || d2[0].ToString() == "Error")
{
ReturnEst.TotalBudget = 0;
ReturnEst.TotalBudgetstr = Controls.CurrencyString(ReturnEst.TotalBudget);
}
else
{
ReturnEst.TotalBudget = Convert.ToDouble(d2[0]);
ReturnEst.TotalBudgetstr = Controls.CurrencyString(ReturnEst.TotalBudget);
}
d2 = null;
}
dt2 = null;
}
}
}
return(ReturnEst);
}
public static Estates GetBVA(Estates Estate)
{
//get budegt
//string q = "select core.ServiceChargeBudgetDetail.ScheduleName, core.ServiceChargeBudgetDetail.Heading, " +
// "core.ServiceChargeBudgetDetail.Cost, core.ServiceChargeBudgetDetail.BudgetID from core.ServiceChargeBudgetDetail " +
// "inner join core.ServiceChargeBudgets on core.ServiceChargeBudgets.BudgetID = core.ServiceChargeBudgetDetail.BudgetID " +
// "where core.ServiceChargeBudgets.EstateID = " + Estate.EstatedID + " and core.ServiceChargeBudgets.startDate = " +
// "'04/01/2017' and core.ServiceChargeBudgets.endDate = '03/31/2018'";
string q = "select core.ServiceChargeBudgetDetail.ScheduleName, core.ServiceChargeBudgetDetail.Heading, " +
"core.ServiceChargeBudgetDetail.Cost, core.ServiceChargeBudgetDetail.BudgetID, core.ServiceChargeBudgets.startDate, " +
"core.ServiceChargeBudgets.endDate, core.ServiceChargeBudgetDetail.ID from core.ServiceChargeBudgetDetail " +
"inner join core.ServiceChargeBudgets on core.ServiceChargeBudgets.ID = core.ServiceChargeBudgetDetail.BudgetID " +
"where core.ServiceChargeBudgets.ID = " + Estate.BudgetId.ToString() + " and core.ServiceChargeBudgets._status = 'Completed'" +
" and not core.ServiceChargeBudgetDetail.Heading = 'Roundings' and not core.servicechargebudgets.FundTypeID = 4";
DBConnectionObject db = GlobalVariables.GetConnection();
DataTable BudgetDT = db.Connection.GetDataTable(q);
DateTime scStart = new DateTime();
DateTime scEnd = new DateTime();
Estate.BVAList = new List <Estates>();
if (BudgetDT.Rows.Count > 0 && BudgetDT.Rows[0][0].ToString() != "Error")
{
DataRow dr = BudgetDT.Rows[0];
Estate.BudgetId = Convert.ToInt32(dr[3]);
DateTime.TryParse(dr[4].ToString(), out scStart);
Estate.SCStartDate = scStart.ToShortDateString();
DateTime.TryParse(dr[5].ToString(), out scEnd);
Estate.SCEndDate = scEnd.ToShortDateString();
int hid = Convert.ToInt32(dr[6]);
foreach (DataRow d in BudgetDT.Rows)
{
//Cost
double c = 0;
if (d[2] != DBNull.Value)
{
c = Convert.ToDouble(d[2]);
}
string cstr = Controls.CurrencyString(c);
Estate.BVAList.Add(new Estates
{
SCScheduleName = d[0].ToString(),
SCHeading = d[1].ToString(),
SCCost = c,
SCCoststr = cstr,
//HeadingID = hid
});
}
dr = null;
}
BudgetDT = null;
//Expenditure
q = "select core.Transactions.budgetSchedule, core.ServiceChargeBudgetDetail.Heading, core.Transactions.TransAmount " +
"from core.Transactions " +
"inner join core.ServiceChargeBudgetDetail on core.ServiceChargeBudgetDetail.id = " +
" core.Transactions.HeadingID where core.transactions.budgetID = " + Estate.BudgetId.ToString() +
" and Core.Transactions.inError is null or core.Transactions.inError = 0";
//q = "select core.Transactions.budgetSchedule, core.ServiceChargeBudgetDetail.Heading, core.Transactions.TransAmount " +
// "from core.Transactions where core.transactions.budgetID = " + Estate.BudgetId.ToString() +
// " and Core.Transactions.inError is null or core.Transactions.inError = 0";
DataTable ExpDT = db.Connection.GetDataTable(q);
if (ExpDT.Rows.Count > 0)
{
DataRow dr1 = ExpDT.Rows[0];
if (dr1[0].ToString() != "Error")
{
string sched = "";
string head = "";
Estate.Schedules = new List <string>();
foreach (Estates es in Estate.BVAList)
{
sched = es.SCScheduleName;
head = es.SCHeading;
//Sets the schedule list used by the view to separate the tables
if (Estate.Schedules.Count == 0)
{
Estate.Schedules.Add(sched);
}
else
{
bool found = false;
foreach (string s in Estate.Schedules)
{
if (s == sched)
{
found = true;
}
}
if (found == false)
{
Estate.Schedules.Add(sched);
}
else
{
found = false;
}
}
es.TrCost = 0;
foreach (DataRow dr2 in ExpDT.Rows)
{
if (dr2[0].ToString() == sched && dr2[1].ToString() == head)
{
if (dr2[2] != DBNull.Value)
{
es.TrCost = es.TrCost + Convert.ToDouble(dr2[2]);
es.TrCoststr = Controls.CurrencyString(es.TrCost);
}
}
}
}
}
}
//totals
foreach (Estates es in Estate.BVAList)
{
if (es.SCCost == 0)
{
es.SCCoststr = "£0.00";
}
if (es.TrCost == 0)
{
es.TrCoststr = "£0.00";
}
es.BVADiff = es.SCCost - es.TrCost;
es.BVADiffstr = Controls.CurrencyString(es.BVADiff);
//Estate.TotalBudget = Estate.TotalBudget + es.SCCost;
//Estate.TotalBudgetstr = Estate.TotalBudget.ToString("C", CultureInfo.CurrentCulture);
//Estate.TotalExp = Estate.TotalExp + es.TrCost;
//Estate.TotalExpstr = Estate.TotalExp.ToString("C", CultureInfo.CurrentCulture);
//Estate.TotalDiff = Estate.TotalBudget - Estate.TotalExp;
//Estate.TotalDiffstr = Estate.TotalDiff.ToString("C", CultureInfo.CurrentCulture);
}
return(Estate);
}
public static Estates GetServiceChargeExpenditure(Estates Estate)
{
//gets budget ID
Estate.TransactionList = new List <Estates>();
string q = "select core.ServiceChargeBudgets.ID from core.ServiceChargeBudgets where estateID = "
+ Estate.EstatedID + " and not fundtypeid = 4 and not fundtypeid = 5";
//exclude opening balance funds and admin funds
DBConnectionObject db = GlobalVariables.GetConnection();
DataTable dt1 = db.Connection.GetDataTable(q);
if (dt1.Rows.Count > 0 && dt1.Rows[0][0].ToString() != "Error")
{
//set string for all budgetids
q = "select cast(core.authorisedinvoices.invoicedate as date), transdesc, transamount, " +
"core.ServiceChargeBudgetDetail.Heading from core.Transactions " +
"inner join core.ServiceChargeBudgetDetail on core.Transactions.HeadingID = " +
"core.ServiceChargeBudgetDetail.id " +
" inner join core.authorisedinvoices on core.transactions.id = core.authorisedinvoices.invoiceid " +
"where core.Transactions.TransactionTypeID = 4 " +
"and inerror is null " +
"and core.transactions.budgetid = ";
for (int i = 0; i <= dt1.Rows.Count - 1; i++)
{
int bid = 0;
if (i == 0)
{
int.TryParse(dt1.Rows[i][0].ToString(), out bid);
q += bid.ToString() + " ";
}
else
{
int.TryParse(dt1.Rows[i][0].ToString(), out bid);
q += "or core.transactions.budgetid = " + bid.ToString() + " ";
}
}
q += "order by core.authorisedinvoices.invoicedate desc";
DataTable dt = db.Connection.GetDataTable(q);
Estate.TransactionList = new List <Estates>();
if (dt.Rows.Count > 0 && dt.Rows[0][0].ToString() != "Error")
{
foreach (DataRow dr in dt.Rows)
{
//Transaction date
DateTime TDate = new DateTime();
if (dr[0] != DBNull.Value)
{
DateTime.TryParse(dr[0].ToString(), out TDate);
}
//transaction cost
double c = 0;
if (dr[2] != DBNull.Value)
{
c = Convert.ToDouble(dr[2]);
}
string cstr = Controls.CurrencyString(c);
Estate.TransactionList.Add(new Estates {
TransDate = Controls.DateString(TDate),
TransDescription = dr[1].ToString(),
SCCost = c,
SCCoststr = cstr,
SCHeading = dr[3].ToString()
});
}
}
}
return(Estate);
}
public static Models.Estates GetServiceChargeBudget(Estates Estate, int PropertyID)
{
//get current year service charge budget
string q = "select core.ServiceChargeBudgets.startDate, core.ServiceChargeBudgets.endDate, " +
"core.ServiceChargeBudgetDetail.ScheduleName, core.ServiceChargeBudgetDetail.Heading, " +
"core.ServiceChargeBudgetDetail.Cost, core.ServiceChargeBudgets.ID, core.ServiceChargeBudgets._status, " +
" core.servicechargebudgetdetail.notes from core.ServiceChargeBudgets inner join " +
"core.ServiceChargeBudgetDetail on core.ServiceChargeBudgetDetail.BudgetID = " +
"core.ServiceChargeBudgets.ID where core.ServiceChargeBudgets.ID = " + Estate.BudgetId.ToString() +
"and core.ServiceChargeBudgets._status = 'Completed' " +
"and core.servicechargebudgetdetail.headingid <> 1 " +
"and core.servicechargebudgetdetail.headingid <> 2 " +
"order by displayorder asc";
DBConnectionObject db = GlobalVariables.GetConnection();
DataTable dt = db.Connection.GetDataTable(q);
Estate.BudgetList = new List <Estates>();
if (dt.Rows.Count > 0 && dt.Rows[0][0].ToString() != "Error")
{
DataRow d = dt.Rows[0];
if (d[0].ToString() != "Error")
{
foreach (DataRow dr in dt.Rows)
{
double c = 0;
if (dr[4] != DBNull.Value)
{
c = Convert.ToDouble(dr[4]);
}
DateTime scstart = new DateTime();
if (dr[0] != DBNull.Value)
{
scstart = Convert.ToDateTime(dr[0]);
}
DateTime scEnd = new DateTime();
if (dr[1] != DBNull.Value)
{
scEnd = Convert.ToDateTime(dr[1]);
}
string cstr = Controls.CurrencyString(c);
Estate.BudgetList.Add(new Estates
{
SCStartDate = Controls.DateString(scstart),
SCEndDate = Controls.DateString(scEnd),
SCScheduleName = dr[2].ToString(),
SCHeading = dr[3].ToString(),
SCCost = c,
SCCoststr = cstr,
BudgetId = Convert.ToInt32(dr[5]),
BudgetNotes = dr[7].ToString()
});
}
}
//string sched = "";
bool Addsched = true;
double app = 0;
Estate.Schedules = new List <string>();
foreach (Estates es in Estate.BudgetList)
{
Addsched = true;
foreach (string sched in Estate.Schedules)
{
if (es.SCScheduleName == sched)
{
Addsched = false;
break;
}
}
if (Addsched == true)
{
//get apportionements.
app = GetApportionment(es.BudgetId, es.SCScheduleName, PropertyID);
es.Apportionment = app;
es.strApportionment = app.ToString();
//contribution
es.contribution = es.SCCost * (es.Apportionment / 100);
es.strContribution = Controls.CurrencyString(es.contribution);
//
Estate.Schedules.Add(es.SCScheduleName);
}
else
{
es.Apportionment = app;
es.strApportionment = app.ToString();
es.contribution = es.SCCost * (es.Apportionment / 100);
es.strContribution = Controls.CurrencyString(es.contribution);
}
}
}
return(Estate);
}