private void SetDaily()
{
DateTime dailyDate = dailyPicker.Value;
String dailyQuery = "SELECT dailytrust, dailyown, dailyfile FROM tblDebtors WHERE (buildingID = @bid) AND (completeDate = @cdate)";
foreach (ListDaily ld in dailyList)
{
ld.Trust = false;
ld.Own = false;
ld.File = false;
foreach (Building b in buildings)
{
if (ld.Code == b.Abbr)
{
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
sqlParms.Add("@bid", b.ID);
sqlParms.Add("@cdate", dailyDate.ToString("yyyy/MM/dd"));
DataSet dsDaily = dataHandler.GetData(dailyQuery, sqlParms, out status);
if (dsDaily != null && dsDaily.Tables.Count > 0 && dsDaily.Tables[0].Rows.Count > 0)
{
DataRow drDaily = dsDaily.Tables[0].Rows[0];
try
{
ld.Trust = bool.Parse(drDaily["dailytrust"].ToString());
ld.Own = bool.Parse(drDaily["dailyown"].ToString());
ld.File = bool.Parse(drDaily["dailyfile"].ToString());
}
catch { }
}
break;
}
}
}
dgDaily.Invalidate();
}
private void LoadDebtors()
{
cmbDebtor.SelectedIndexChanged -= cmbDebtor_SelectedIndexChanged;
String query = "SELECT id, name FROM tblUsers WHERE (usertype = 3) and Active = 1 ORDER BY name";
String status;
DataSet ds = dh.GetData(query, null, out status);
List <DebtorSelector> debtors = new List <DebtorSelector>();
DebtorSelector debtor = new DebtorSelector
{
ID = 0,
Name = "All debtors"
};
debtors.Add(debtor);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DebtorSelector drDebtor = new DebtorSelector
{
ID = int.Parse(dr["id"].ToString()),
Name = dr["name"].ToString()
};
debtors.Add(drDebtor);
}
}
cmbDebtor.DataSource = debtors;
cmbDebtor.DisplayMember = "Name";
cmbDebtor.ValueMember = "ID";
cmbDebtor.SelectedIndex = 0;
cmbDebtor.SelectedIndexChanged += cmbDebtor_SelectedIndexChanged;
}
private List <int> getReqBuildings()
{
String query = "SELECT DISTINCT b.id FROM tblRequisition AS r INNER JOIN tblBuildings AS b ON r.building = b.id";
DataSet dsB = dh.GetData(query, null, out status);
List <int> myBuildings = new List <int>();
if (dsB != null && dsB.Tables.Count > 0 && dsB.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dsB.Tables[0].Rows)
{
myBuildings.Add(int.Parse(dr["id"].ToString()));
}
}
return(myBuildings);
}
private byte[] GetAttachment(String fileName, int attachmentType, out int fileID)
{
String fileQuery = "SELECT * FROM tblAttachments WHERE fileName = @fileName AND attachmentType = @at";
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
String status;
SqlDataHandler dataHandler = new SqlDataHandler();
sqlParms.Clear();
sqlParms.Add("@fileName", fileName);
sqlParms.Add("@at", attachmentType);
DataSet dsAttachment = dataHandler.GetData(fileQuery, sqlParms, out status);
if (dsAttachment != null && dsAttachment.Tables.Count > 0 && dsAttachment.Tables[0].Rows.Count > 0)
{
byte[] file = (byte[])dsAttachment.Tables[0].Rows[0]["fileContent"];
fileID = int.Parse(dsAttachment.Tables[0].Rows[0]["id"].ToString());
return(file);
}
else
{
fileID = 0;
return(null);
}
}
private void LoadBreakdown()
{
bs.Clear();
String query = " SELECT u.name, ps.status, ps.actionDate FROM tblPMJobStatus AS ps INNER JOIN tblUsers AS u ON ps.actioned = u.id WHERE (ps.jobID = @jid) ORDER BY ps.actionDate";
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
sqlParms.Add("@jid", jobID);
SqlDataHandler dh = new SqlDataHandler();
String status;
DataSet ds = dh.GetData(query, sqlParms, out status);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DateTime iniDate = DateTime.Now;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
jobBreakdown jb = new jobBreakdown
{
user = dr["name"].ToString(),
status = dr["status"].ToString(),
date = dr["actionDate"].ToString()
};
jb.delay = i == 0 ? "" : CalcDiff(iniDate, DateTime.Parse(jb.date)).ToString();
iniDate = DateTime.Parse(jb.date);
bs.Add(jb);
}
}
}
private void LoadDefaultValues()
{
SqlDataHandler dh = new SqlDataHandler();
String settingsQuery = "SELECT centrec FROM tblSettings";
String status;
DataSet dsDefault = dh.GetData(settingsQuery, null, out status);
if (dsDefault != null && dsDefault.Tables.Count > 0 && dsDefault.Tables[0].Rows.Count > 0)
{
DataRow dr = dsDefault.Tables[0].Rows[0];
centrec = dr["centrec"].ToString();
}
}
private DataSet GetUnits(bool statements)
{
String query = "SELECT DISTINCT unit" + (!statements ? "no" : "") + " as item FROM " + (!statements ? "tblLetterRun" : "tblStatementRun") + " ORDER BY unit" + (!statements ? "no" : "");
String status = String.Empty;
DataSet ds = dh.GetData(query, null, out status);
return(ds);
}
private void btnSearch_Click(object sender, EventArgs e)
{
if (cmbBuilding.SelectedItem != null)
{
this.Cursor = Cursors.WaitCursor;
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
String abbr = cmbBuilding.SelectedIndex > 0 ? buildings[cmbBuilding.SelectedIndex].Abbr : String.Empty;
String query = "SELECT id as ID, unit AS Unit, debtorEmail AS [Sent From], email1 AS [Sent To], queueDate AS Queued, sentDate1 AS Sent, errorMessage AS Status FROM tblStatementRun";
String orderBy = "";
if (rdSent.Checked)
{
query += " WHERE (sentDate1 >= @dtFrom AND sentDate1 <= @dtTo) ";
orderBy = "sentDate1";
}
else if (rdUnsent.Checked)
{
query += " WHERE (queueDate >= @dtFrom AND queueDate <= @dtTo) AND (sentDate1 is null)";
orderBy = "queueDate";
}
else
{
query += " WHERE ((sentDate1 >= @dtFrom AND sentDate1 <= @dtTo) OR (queueDate >= @dtFrom AND queueDate <= @dtTo)) ";
orderBy = "queueDate, sentDate1";
}
sqlParms.Add("@dtFrom", dtFrom.Value);
sqlParms.Add("@dtTo", dtTo.Value);
if (!String.IsNullOrEmpty(abbr))
{
query += " AND LEFT(unit, " + abbr.Length.ToString() + ") = '" + abbr + "' ";
query += " AND ISNUMERIC(SUBSTRING(unit, " + (abbr.Length + 1).ToString() + ", 1)) = 1";
sqlParms.Add("@abbr", abbr);
}
query += " ORDER BY " + orderBy;
// MessageBox.Show(query);
String status;
DataSet ds = dh.GetData(query, sqlParms, out status);
dgStatements.DataSource = null;
if (ds != null && ds.Tables.Count > 0)
{
dgStatements.DataSource = ds.Tables[0];
lblStatements.Text = "Statement count: " + ds.Tables[0].Rows.Count.ToString();
}
else
{
lblStatements.Text = "Statement count: 0";
}
this.Cursor = Cursors.Arrow;
}
}
public ClearanceValues()
{
String query = "SELECT clearance, ex_clearance, recon_split, centrec, business FROM tblSettings";
SqlDataHandler dh = new SqlDataHandler();
String status;
DataSet ds = dh.GetData(query, null, out status);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
clearanceFee = double.Parse(dr["clearance"].ToString());
exClearanceFee = double.Parse(dr["ex_clearance"].ToString());
splitFee = double.Parse(dr["recon_split"].ToString());
centrec = dr["centrec"].ToString();
business = dr["business"].ToString();
}
}
public MonthlyFinancial(int buildingID, int period, int year)
{
DateTime startDate = new DateTime(year, 1, 1, 0, 0, 0);
DateTime endDate = new DateTime(year, 12, 31, 23, 59, 59);
SqlDataHandler dh = new SqlDataHandler();
String query = "SELECT id, completeDate, buildingID, finPeriod, levies, leviesReason, sewage, sewageNotes, electricity, electricityNotes, water, waterNotes, specialLevies, ";
query += " specialLevyNotes, otherIncomeDescription, otherIncome, otherIncomeNotes, memberInterest, memberInterestNotes, bankInterest, bankInterestNotes, accountingFees, ";
query += " accountingFeesNotes, bankCharges, bankChargesNotes, sewageExpense, sewageExpenseNotes, deliveries, deliveriesNotes, electricityExpense, ";
query += " electricityExpenseNotes, gardens, gardensNotes, insurance, insuranceNotes, interestPaid, interestPaidNotes, managementFees, managementFeesNotes, ";
query += " meterReading, meterReadingNotes, printing, printingNotes, post, postNotes, repairs, repairsNotes, refuse, refuseNotes, salaries, salariesNotes, security, ";
query += " securityNotes, telephone, telephoneNotes, waterExpense, waterExpenseNotes, municipal, municipalReason, trust, trustNotes, own, ownNotes, investment, ";
query += " investmentNotes, sundy, sundryNotes, assets, assetsNotes, debtors, debtorsNotes, municipalAccounts, municipalAccountsNotes, owners, ownersNotes, suppliers, ";
query += " suppliersNotes, liabilities, liabilitiesNotes, electricityRecon, waterRecon";
query += " FROM tblMonthFin WHERE buildingID = " + buildingID.ToString() + " AND finPeriod = " + period.ToString() + " AND completeDate >= '" + startDate.ToString() + "' AND completeDate <= '" + endDate.ToString() + "'";
String status;
DataSet ds = dh.GetData(query, null, out status);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
id = int.Parse(dr["id"].ToString());
bool result;
completeDate = DateTime.Parse(dr["completeDate"].ToString());
buildingID = int.Parse(dr["buildingID"].ToString());
finPeriod = int.Parse(dr["finPeriod"].ToString());
levies = (bool.TryParse(dr["levies"].ToString(), out result) ? result : false);
leviesReason = dr["leviesReason"].ToString();
sewage = (bool.TryParse(dr["sewage"].ToString(), out result) ? result : false);
sewageNotes = dr["sewageNotes"].ToString();
electricity = (bool.TryParse(dr["electricity"].ToString(), out result) ? result : false);
electricityNotes = dr["electricityNotes"].ToString();
water = (bool.TryParse(dr["water"].ToString(), out result) ? result : false);
waterNotes = dr["waterNotes"].ToString();
specialLevies = (bool.TryParse(dr["specialLevies"].ToString(), out result) ? result : false);
specialLevyNotes = dr["specialLevyNotes"].ToString();
otherIncomeDescription = dr["otherIncomeDescription"].ToString();
otherIncome = (bool.TryParse(dr["otherIncome"].ToString(), out result) ? result : false);
otherIncomeNotes = dr["otherIncomeNotes"].ToString();
memberInterest = (bool.TryParse(dr["memberInterest"].ToString(), out result) ? result : false);
memberInterestNotes = dr["memberInterestNotes"].ToString();
bankInterest = (bool.TryParse(dr["bankInterest"].ToString(), out result) ? result : false);
bankInterestNotes = dr["bankInterestNotes"].ToString();
accountingFees = (bool.TryParse(dr["accountingFees"].ToString(), out result) ? result : false);
accountingFeesNotes = dr["accountingFeesNotes"].ToString();
bankCharges = (bool.TryParse(dr["bankCharges"].ToString(), out result) ? result : false);
bankChargesNotes = dr["bankChargesNotes"].ToString();
sewageExpense = (bool.TryParse(dr["sewageExpense"].ToString(), out result) ? result : false);
sewageExpenseNotes = dr["sewageExpenseNotes"].ToString();
deliveries = (bool.TryParse(dr["deliveries"].ToString(), out result) ? result : false);
deliveriesNotes = dr["deliveriesNotes"].ToString();
electricityExpense = (bool.TryParse(dr["electricityExpense"].ToString(), out result) ? result : false);
electricityExpenseNotes = dr["electricityExpenseNotes"].ToString();
gardens = (bool.TryParse(dr["gardens"].ToString(), out result) ? result : false);
gardensNotes = dr["gardensNotes"].ToString();
insurance = (bool.TryParse(dr["insurance"].ToString(), out result) ? result : false);
insuranceNotes = dr["insuranceNotes"].ToString();
interestPaid = (bool.TryParse(dr["interestPaid"].ToString(), out result) ? result : false);
interestPaidNotes = dr["interestPaidNotes"].ToString();
managementFees = (bool.TryParse(dr["managementFees"].ToString(), out result) ? result : false);
managementFeesNotes = dr["managementFeesNotes"].ToString();
meterReading = (bool.TryParse(dr["meterReading"].ToString(), out result) ? result : false);
meterReadingNotes = dr["meterReadingNotes"].ToString();
printing = (bool.TryParse(dr["printing"].ToString(), out result) ? result : false);
printingNotes = dr["printingNotes"].ToString();
post = (bool.TryParse(dr["post"].ToString(), out result) ? result : false);
postNotes = dr["postNotes"].ToString();
repairs = (bool.TryParse(dr["repairs"].ToString(), out result) ? result : false);
repairsNotes = dr["repairsNotes"].ToString();
refuse = (bool.TryParse(dr["refuse"].ToString(), out result) ? result : false);
refuseNotes = dr["refuseNotes"].ToString();
salaries = (bool.TryParse(dr["salaries"].ToString(), out result) ? result : false);
salariesNotes = dr["salariesNotes"].ToString();
security = (bool.TryParse(dr["security"].ToString(), out result) ? result : false);
securityNotes = dr["securityNotes"].ToString();
telephone = (bool.TryParse(dr["telephone"].ToString(), out result) ? result : false);
telephoneNotes = dr["telephoneNotes"].ToString();
waterExpense = (bool.TryParse(dr["waterExpense"].ToString(), out result) ? result : false);
waterExpenseNotes = dr["waterExpenseNotes"].ToString();
municipal = (bool.TryParse(dr["municipal"].ToString(), out result) ? result : false);
municipalReason = dr["municipalReason"].ToString();
trust = (bool.TryParse(dr["trust"].ToString(), out result) ? result : false);
trustNotes = dr["trustNotes"].ToString();
own = (bool.TryParse(dr["own"].ToString(), out result) ? result : false);
ownNotes = dr["ownNotes"].ToString();
investment = (bool.TryParse(dr["investment"].ToString(), out result) ? result : false);
investmentNotes = dr["investmentNotes"].ToString();
sundy = (bool.TryParse(dr["sundy"].ToString(), out result) ? result : false);
sundryNotes = dr["sundryNotes"].ToString();
assets = (bool.TryParse(dr["assets"].ToString(), out result) ? result : false);
assetsNotes = dr["assetsNotes"].ToString();
debtors = (bool.TryParse(dr["debtors"].ToString(), out result) ? result : false);
debtorsNotes = dr["debtorsNotes"].ToString();
municipalAccounts = (bool.TryParse(dr["municipalAccounts"].ToString(), out result) ? result : false);
municipalAccountsNotes = dr["municipalAccountsNotes"].ToString();
owners = (bool.TryParse(dr["owners"].ToString(), out result) ? result : false);
ownersNotes = dr["ownersNotes"].ToString();
suppliers = (bool.TryParse(dr["suppliers"].ToString(), out result) ? result : false);
suppliersNotes = dr["suppliersNotes"].ToString();
liabilities = (bool.TryParse(dr["liabilities"].ToString(), out result) ? result : false);
liabilitiesNotes = dr["liabilitiesNotes"].ToString();
electricityRecon = int.Parse(dr["electricityRecon"].ToString());
waterRecon = int.Parse(dr["waterRecon"].ToString());
}
else
{
id = 0;
}
}
private void LoadJobs()
{
int totJobs = 0;
int totAss = 0;
int totComp = 0;
bs.Clear();
if (validSelection && selectionType != 0)
{
String query = "SELECT j.id, u1.name AS creator, u2.name AS processor, j.buildingCode, j.status, j.createDate, j.assignedDate, ";
query += " CASE WHEN DATEDIFF(hour, j.createDate, j.assignedDate) IS NULL THEN - 1 ELSE DATEDIFF(hour, j.createDate, j.assignedDate) END AS assDiff, ";
query += " j.completeDate, CASE WHEN DATEDIFF(hour, j.assignedDate, j.completeDate) IS NULL THEN - 1 ELSE DATEDIFF(hour, j.assignedDate, j.completeDate) ";
query += " END AS compDiff FROM tblPMJob AS j LEFT OUTER JOIN tblUsers AS u2 ON j.processedBy = u2.id LEFT OUTER JOIN ";
query += " tblUsers AS u1 ON j.creator = u1.id WHERE (j.createDate >= @start) AND (j.createDate <= @end) AND ";
switch (selectionType)
{
case 1:
query += " (j.status = @crit)";
break;
case 2:
query += " (j.creator = @crit)";
break;
case 3:
query += " (j.processedBy = @crit)";
break;
}
Dictionary <String, Object> sqlParms = new Dictionary <string, object>();
DateTime start = new DateTime(dtStart.Value.Year, dtStart.Value.Month, dtStart.Value.Day, 0, 0, 0);
DateTime finish = new DateTime(dtTo.Value.Year, dtTo.Value.Month, dtTo.Value.Day, 23, 59, 59);
sqlParms.Add("@start", start);
sqlParms.Add("@end", finish);
sqlParms.Add("@crit", criteria);
DataSet dsJobs = dh.GetData(query, sqlParms, out status);
if (dsJobs != null && dsJobs.Tables.Count > 0 && dsJobs.Tables[0].Rows.Count > 0)
{
totJobs = dsJobs.Tables[0].Rows.Count;
foreach (DataRow drJob in dsJobs.Tables[0].Rows)
{
JobData jd = new JobData();
jd.id = drJob["id"].ToString();
jd.creator = drJob["creator"].ToString();
jd.processor = drJob["processor"].ToString();
jd.buildingCode = drJob["buildingCode"].ToString();
jd.status = drJob["status"].ToString();
jd.createDate = drJob["createDate"].ToString();
jd.assignedDate = drJob["assignedDate"].ToString();
jd.assDiff = (jd.assignedDate == "" ? "" : CalcDiff(DateTime.Parse(jd.createDate), DateTime.Parse(jd.assignedDate)).ToString());
if (jd.assDiff != "")
{
totAss += int.Parse(jd.assDiff);
}
jd.completeDate = drJob["completeDate"].ToString();
jd.compDiff = (jd.assignedDate == "" || jd.completeDate == "" ? "" : CalcDiff(DateTime.Parse(jd.assignedDate), DateTime.Parse(jd.completeDate)).ToString());
if (jd.compDiff != "")
{
totComp += int.Parse(jd.compDiff);
}
bs.Add(jd);
}
}
}
lblTotal.Text = totJobs.ToString();
lblTotAss.Text = totAss.ToString();
lblTotComp.Text = totComp.ToString();
lblAvgAss.Text = ((double)totAss / (double)totJobs).ToString("#,##0.00");
lblAvgComp.Text = ((double)totComp / (double)totJobs).ToString("#,##0.00");
dataGridView1.Refresh();
}