public static CallReports GetRepairReport(int repairID)
{
//Query with report outcome removed
string q = "select core.CallReports.id, core.CallReports.ReporterName, core.CallReports.ReportTitle, " +
"core.CallReports.ReportDetails, core.CallReports.NextActionDate, core.CallReports.DisplayAnon, " +
"Core.NextActions.NextAction, core.reporterType.ReporterType, " +
"core.CallReports.ReportDate " +
"from core.CallReports inner join core.Repairs on core.Repairs.reportID = core.CallReports.id " +
"inner join core.nextactions on core.NextActions.id = core.callreports.nextactionid " +
"inner join core.ReporterType on core.ReporterType.id = core.CallReports.ReportertypeID " +
"where core.Repairs.id = " + repairID.ToString();
//Original query
//"select core.CallReports.id, core.CallReports.ReporterName, core.CallReports.ReportTitle, " +
// "core.CallReports.ReportDetails, core.CallReports.NextActionDate, core.CallReports.DisplayAnon, " +
// "core.ReportOutcomes.Outcome, Core.NextActions.NextAction, core.reporterType.ReporterType, " +
// "core.CallReports.ReportDate " +
// "from core.CallReports inner join core.Repairs on core.Repairs.reportID = core.CallReports.id " +
// "inner join core.ReportOutcomes on core.reportOutcomes.id = core.callreports.outcomeid " +
// "inner join core.nextactions on core.NextActions.id = core.callreports.nextactionid " +
// "inner join core.ReporterType on core.ReporterType.id = core.CallReports.reportTypeID " +
// "where core.Repairs.id = " + repairID.ToString();
DBConnectionObject db = GlobalVariables.GetConnection();
DataTable dt = db.Connection.GetDataTable(q);
CallReports c = new CallReports();
if (dt.Rows.Count > 0)
{
DataRow d = dt.Rows[0];
if (d[0].ToString() != "Error")
{
foreach (DataRow dr in dt.Rows)
{
//anon
bool anon = false;
if (dr[5] != DBNull.Value)
{
anon = Convert.ToBoolean(dr[5]);
}
else
{
anon = true;
}
string name = "";
if (anon == true)
{
name = "Not Displayed";
}
else
{
name = dr[1].ToString();
}
//next action date
DateTime nextaxtiondate = new DateTime();
if (dr[4] != DBNull.Value)
{
nextaxtiondate = Convert.ToDateTime(dr[4]);
}
//Report Date
DateTime logged = new DateTime();
if (dr[8] != DBNull.Value)
{
logged = Convert.ToDateTime(dr[8]);
}
CallReports cd = new CallReports
{
ID = Convert.ToInt32(dr[0]),
ReporterName = name,
ReportTitle = dr[2].ToString(),
ReportDetails = dr[3].ToString(),
NextActionDateStr = Controls.DateString(nextaxtiondate),
NextAction = dr[6].ToString(),
ReporterType = dr[7].ToString(),
ReportDate = logged,
ReportDateStr = Controls.DateString(logged)
};
c = cd;
}
}
}
return(c);
}
public static Repairs GetAllRepairDetails(int repairID)
{
string q = "select core.Repairs.RepairTitle, core.Repairs.RepairDetails, " +
"core.Repairs.RaisedDate, core.Repairs.TargetCompletionDate, " +
"core.Repairs.ScheduleName, core.Repairs.QupteID, core.RepairStatus.RepairStatus, " +
"core.Repairs.HeadAssetID, core.Repairs.SubAssetID, " +
"core.PurchaseOrders.EstCost, core.PurchaseOrders.ID from core.Repairs inner join core.RepairStatus on " +
"core.Repairs.RepairStatusID = core.RepairStatus.id " +
"inner join core.PurchaseOrders on " +
"core.Repairs.PONumber = core.PurchaseOrders.id where core.Repairs.ID = " + repairID.ToString();
DBConnectionObject db = GlobalVariables.GetConnection();
DataTable dt = db.Connection.GetDataTable(q);
Repairs c = new Repairs();
if (dt.Rows.Count > 0)
{
DataRow d = dt.Rows[0];
if (d[0].ToString() != "Error")
{
foreach (DataRow dr in dt.Rows)
{
//raised date
DateTime raised = new DateTime();
if (dr[2] != DBNull.Value)
{
raised = Convert.ToDateTime(dr[2]);
}
//Target Completion
DateTime target = new DateTime();
if (dr[3] != DBNull.Value)
{
target = Convert.ToDateTime(dr[3]);
}
//Estiated Cost
double est = 0;
if (dr[9] != DBNull.Value)
{
est = Convert.ToDouble(dr[9]);
}
string eststr = Controls.CurrencyString(est);
//po number
int po = 0;
if (dr[10] != DBNull.Value)
{
po = Convert.ToInt32(dr[10]);
}
Repairs r = new Repairs
{
Title = dr[0].ToString(),
Details = dr[1].ToString(),
RaisedDate = raised,
RaisedDatestr = Controls.DateString(raised),
TargetDate = target,
TargetDatestr = Controls.DateString(target),
Status = dr[6].ToString(),
EstimatedCostStr = eststr,
HeadAssetID = Convert.ToInt32(dr[7]),
SubAssetID = Convert.ToInt32(dr[8]),
PONumber = po
};
c = r;
}
//Get Asset Details
if (c.HeadAssetID != 0)
{
q = "Select AssetName, AssetLocation from core.headassets where id = " + c.HeadAssetID.ToString();
DataTable dt1 = db.Connection.GetDataTable(q);
if (dt1.Rows.Count > 0)
{
if (dt1.Rows[0][0] != DBNull.Value || dt1.Rows[0][0].ToString() != "Error")
{
c.HeadAssetName = dt1.Rows[0][0].ToString();
c.HeadAssetLocation = dt1.Rows[0][1].ToString();
}
}
}
if (c.SubAssetID != 0)
{
q = "Select AssetName, AssetLocation from core.subassets where id = " + c.SubAssetID.ToString();
DataTable dt1 = db.Connection.GetDataTable(q);
if (dt1.Rows.Count > 0)
{
if (dt1.Rows[0][0] != DBNull.Value || dt1.Rows[0][0].ToString() != "Error")
{
c.SubAssetName = dt1.Rows[0][0].ToString();
c.SubAssetLocation = dt1.Rows[0][1].ToString();
}
}
}
//sets head asset location to null if sub asset location present.
//view only needs one of the asset locations to be visible.
if (c.SubAssetLocation != null && c.SubAssetLocation != "")
{
c.HeadAssetLocation = null;
}
}
}
//if quote id is not 0 get quote details
return(c);
}
public void CompareNotificationSettings()
{
//compares old notification settings with those submitted and displays relevant
NotificationSettings.NotificationSettings NewObj = new NotificationSettings.NotificationSettings();
NewObj.UnitID = NotificationSettingObj.UnitID;
NewObj.CustomerID = NotificationSettingObj.CustomerID;
NewObj.GetNotificationSettings(GlobalVariables.GetConnection());
if (!NewObj.NewRepairNotification || !NotificationSettingObj.NewRepairNotification)
{
if (!NewObj.NewRepairNotification && NotificationSettingObj.NewRepairNotification)
{
this.RepairNotificationReceiveMessage = true;
}
else if (NewObj.NewRepairNotification && !NotificationSettingObj.NewRepairNotification)
{
this.RepairNotificationCancelMessage = true;
}
}
if (!NewObj.NewAccountCharge || !NotificationSettingObj.NewAccountCharge)
{
if (!NewObj.NewAccountCharge && NotificationSettingObj.NewAccountCharge)
{
this.ChargeNotificationReceiveMessage = true;
}
else if (NewObj.NewAccountCharge && !NotificationSettingObj.NewAccountCharge)
{
this.ChargeNotificationCancelMessage = true;
}
}
if (!NewObj.NewAccountPayment || !NotificationSettingObj.NewAccountPayment)
{
if (!NewObj.NewAccountPayment && NotificationSettingObj.NewAccountPayment)
{
this.PaymentNotificationReceiveMessage = true;
}
else if (NewObj.NewAccountPayment && !NotificationSettingObj.NewAccountPayment)
{
this.PaymentNotificationCancelMessage = true;
}
}
if (!NewObj.NewSCBudget || !NotificationSettingObj.NewSCBudget)
{
if (!NewObj.NewSCBudget && NotificationSettingObj.NewSCBudget)
{
this.BudgetNotificationReceiveMessage = true;
}
else if (NewObj.NewSCBudget && !NotificationSettingObj.NewSCBudget)
{
this.BudgetNotificationCancelMessage = true;
}
}
if (!NewObj.NewInsurance || !NotificationSettingObj.NewInsurance)
{
if (!NewObj.NewInsurance && NotificationSettingObj.NewInsurance)
{
this.InsuranceNotificationReceiveMessage = true;
}
else if (NewObj.NewInsurance && !NotificationSettingObj.NewInsurance)
{
this.InsuranceNotificationCancelMessage = true;
}
}
}
public void SetAttendanceList(int estateID = 0)
{
int estateid = 0;
if (estateID == 0)
{
string q = $"select estateid from core.units where id ={SelectedPropertyid}";
dbConn.DBConnectionObject db = GlobalVariables.GetConnection();
System.Data.DataTable dt = db.Connection.GetDataTable(q);
if (dt.Rows.Count > 0 && dt.Rows[0][0].ToString() != "Error")
{
int.TryParse(dt.Rows[0][0].ToString(), out estateid);
}
}
else
{
estateid = estateID;
}
AttendanceList = new List <AttendanceVisits.AttendanceVisits>();
AttendanceList =
AttendanceVisits.AttendanceVisitsMethods.AttendanceHistoryList(estateid, GlobalVariables.GetConnection(),
new AttendanceVisits.ImageParams {
width = 200, height = 100
});
AccordionList = new List <Syncfusion.EJ2.Navigations.AccordionAccordionItem>();
foreach (AttendanceVisits.AttendanceVisits item in AttendanceList)
{
AccordionList.Add(new Syncfusion.EJ2.Navigations.AccordionAccordionItem
{
Header = item.VisitDate.ToLongDateString(),
Content = ContentHTML(item)
});
}
}
public async static Task <EstateCases> GetEstateCase(int CaseID)
{
string q = "Select * from core.estatecases " +
"inner join core.EstateCaseSettingData on core.estatecases.id = " +
"core.EstateCaseSettingData.EstateCaseID " +
"inner join core.CaseNotifications on core.EstateCases.id = core.CaseNotifications.CaseID " +
"where core.estatecases.id = " + CaseID.ToString();
DataTable dt = await
GlobalVariables.GetConnection().Connection.GetDataTableAsync(q);
EstateCases r = new EstateCases();
if (dt.Rows.Count > 0 && dt.Rows[0][0].ToString() != "Error")
{
foreach (DataRow dr in dt.Rows)
{
int caseid = Convert.ToInt32(dr[0].ToString());
int estID = Convert.ToInt32(dr[7].ToString());
int caseOwnerid = Convert.ToInt32(dr[4].ToString());
int caseCreatedbyid = Convert.ToInt32(dr[5].ToString());
int TemplateID = Convert.ToInt32(dr[10].ToString());
long docinsID = (long)Convert.ToInt64(dr[8].ToString());
DateTime targetdate = Convert.ToDateTime(dr[9].ToString());
DateTime CreatedDate = Convert.ToDateTime(dr[6].ToString());
//int.TryParse(dr[14].ToString(), out int NotificationID);
//long.TryParse(dr[16].ToString(), out long NotificationDocInstance);
r.id = caseid;
r.CaseTitle = dr[1].ToString();
r.CaseDetails = dr[2].ToString();
if (dr[3].ToString() == "Closed")
{
r.caseStatus = EstateCases.CaseStatus.Closed;
}
else if (dr[3].ToString() == "Open")
{
r.caseStatus = EstateCases.CaseStatus.Open;
}
r.CaseOwner = new UsersDLL.Users {
UserID = caseOwnerid
};
r.CreatedBy = new UsersDLL.Users {
UserID = caseCreatedbyid
};
r.CreatedDate = CreatedDate;
r.Estate = new Estates {
EstatedID = estID
};
r.DocInstanceId = docinsID;
r.TargetCompletionDate = targetdate;
r.TemplateID = TemplateID;
r.CaseData.CaseID = caseid;
r.EmailGuid = dr[11].ToString();
//r.EstateCaseNotification = new EstateCaseNotifications(r.id);
//r.EstateCaseNotification.id = NotificationID;
//r.EstateCaseNotification.DocinstanceID = NotificationDocInstance;
}
}
return(r);
}
//wrapper so can be done on thread
public void UpdateSettings()
{
NotificationSettingObj.Delete(GlobalVariables.GetConnection());
NotificationSettingObj.Insert(GlobalVariables.GetConnection());
}
public async static Task <DataTable> GetAllEstateCasesDataTable(
int UserID = 0,
int estateID = 0,
EstateCases.CaseStatus status = Cases.CaseStatus.None,
DateTime StartDate = new DateTime(),
DateTime EndDate = new DateTime(),
int userid = 0)
{
string q = "select core.EstateCases.id, core.EstateCases.caseTitle, " +
"core.EstateCases.CaseDetails, core.Estates.id, " +
"core.Estates.Name, core.EstateCases.caseownerid, core.EstateCases.docinstanceid, concat(Users.Users.firstname, ' ' , Users.Users.surname), " +
"core.estatecases.TargetCompletionDate, core.EstateCases.casestatus, " +
" core.EstateCases.CaseTemplateID" +
" from core.EstateCases " +
"inner join core.Estates on core.EstateCases.EstateID = core.Estates.ID " +
" inner join Users.Users on core.estatecases.caseownerid = Users.Users.id ";
List <string> WhereParams = new List <string>();
if (UserID > 0)
{
WhereParams.Add($" core.EstateCases.CaseOwnerID = {UserID}");
}
if (estateID > 0)
{
WhereParams.Add($" core.EstateCases.estateid = {estateID}");
}
if (status != Cases.CaseStatus.None)
{
WhereParams.Add($" core.estatecases.casestatus = '{status}'");
}
DateTime Test = new DateTime();
if (StartDate > Test)
{
string date = ControlsDLL.ControlActions.DateTimeForDBQuery(StartDate);
WhereParams.Add($" core.estatecases.createddate >= '{date}'");
}
if (EndDate > Test)
{
string date = ControlsDLL.ControlActions.DateTimeForDBQuery(EndDate);
WhereParams.Add($" core.estatecases.createddate <= '{date}'");
}
if (userid > 0)
{
WhereParams.Add($" core.estatecases.caseownerid <= {userid}");
}
if (WhereParams.Count > 0)
{
q += " where ";
int WhereCount = 0;
foreach (string param in WhereParams)
{
if (WhereCount == 0)
{
q += param;
}
else
{
q += " and " + param;
}
WhereCount += 1;
}
}
q += " Order by core.estatecases.TargetCompletionDate asc";
DataTable dt = await GlobalVariables.GetConnection().Connection.GetDataTableAsync(q);
return(dt);
}
//for updating single property contact preferences
public static void UpdatePropertyAccountPreferences(Models.AccountDetails Account, int customerID, int propertyID = 0)
{
//if PropertyID = 0 then will update contact prefs for all properties.
bool post = false;
bool email = false;
bool postandemail = false;
if (Account.ContactPref == AccountDetails.ContactPreferences.Post)
{
post = true;
}
if (Account.ContactPref == AccountDetails.ContactPreferences.email)
{
email = true;
}
if (Account.ContactPref == AccountDetails.ContactPreferences.postandemail)
{
postandemail = true;
}
List <string> colnames = new List <string>();
colnames.Add("PostOnly");
colnames.Add("Emailonly");
colnames.Add("PostandEmail");
colnames.Add("Address_1");
colnames.Add("Address_2");
colnames.Add("Address_3");
colnames.Add("Address_4");
colnames.Add("Address_5");
colnames.Add("email");
colnames.Add("contactnumer");
List <string> PList = new List <string>();
PList.Add("@PostOnly");
PList.Add("@Emailonly");
PList.Add("@PostandEmail");
PList.Add("@Address_1");
PList.Add("@Address_2");
PList.Add("@Address_3");
PList.Add("@Address_4");
PList.Add("@Address_5");
PList.Add("@email");
PList.Add("@contactnumer");
List <object> values = new List <object>();
values.Add(post);
values.Add(email);
values.Add(postandemail);
values.Add(Account.ContactAdd1);
if (Account.ContactAdd2 == null)
{
values.Add("");
}
else
{
values.Add(Account.ContactAdd2);
}
if (Account.ContactAdd3 == null)
{
values.Add("");
}
else
{
values.Add(Account.ContactAdd3);
}
if (Account.ContactAdd4 == null)
{
values.Add("");
}
else
{
values.Add(Account.ContactAdd4);
}
values.Add(Account.ContactAdd5);
if (Account.Email == null)
{
values.Add("");
}
else
{
values.Add(Account.Email);
}
if (Account.phoneNumber == null)
{
values.Add("");
}
else
{
values.Add(Account.phoneNumber);
}
DBConnectionObject db = GlobalVariables.GetConnection();
if (propertyID > 0)
{
DataTable dt = db.Connection.UpdateCommand("core.LeaseholderContactPreferences", colnames, PList, values, " where customerID = " + customerID.ToString() + " and unitId = " + propertyID.ToString());
}
else
{
DataTable dt = db.Connection.UpdateCommand("core.LeaseholderContactPreferences", colnames, PList, values, " where customerID = " + customerID.ToString());
}
}
//updates mian user account contact preferences
public static void UpdateUserAccountPreferences(Models.AccountDetails Account, int customerID)
{
List <string> colnames = new List <string>();
colnames.Add("Address_1");
colnames.Add("Address_2");
colnames.Add("Address_3");
colnames.Add("Address_4");
colnames.Add("Address_5");
colnames.Add("Email_1");
colnames.Add("ContactNumber_1");
colnames.Add("emailonly");
colnames.Add("postonly");
colnames.Add("postandemail");
List <string> PList = new List <string>();
PList.Add("@Address_1");
PList.Add("@Address_2");
PList.Add("@Address_3");
PList.Add("@Address_4");
PList.Add("@Address_5");
PList.Add("@email_1");
PList.Add("@ContactNumber_1");
PList.Add("@emailonly");
PList.Add("@postonly");
PList.Add("@postandemail");
List <object> values = new List <object>();
values.Add(Account.ContactAdd1);
if (Account.ContactAdd2 == null)
{
values.Add("");
}
else
{
values.Add(Account.ContactAdd2);
}
if (Account.ContactAdd3 == null)
{
values.Add("");
}
else
{
values.Add(Account.ContactAdd3);
}
if (Account.ContactAdd4 == null)
{
values.Add("");
}
else
{
values.Add(Account.ContactAdd4);
}
values.Add(Account.ContactAdd5);
if (Account.Email == null)
{
values.Add("");
}
else
{
values.Add(Account.Email);
}
if (Account.phoneNumber == null)
{
values.Add("");
}
else
{
values.Add(Account.phoneNumber);
}
values.Add(Account.EmailOnly);
values.Add(Account.PostOnly);
values.Add(Account.PostAndEmail);
DBConnectionObject db = GlobalVariables.GetConnection();
DataTable dt = db.Connection.UpdateCommand("core.Leaseholders", colnames, PList, values, " where ID = " + customerID.ToString());
}
//From account level
private static AccountDetails SetContactPreferences(AccountDetails ad, int CustomerID)
{
string q = "Select Postonly, emailonly, PostAndEmail from core.LeaseholderContactPreferences where CustomerID = " + CustomerID.ToString();
DBConnectionObject db = GlobalVariables.GetConnection();
DataTable dt = db.Connection.GetDataTable(q);
//set first to compare
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
if (dr[0] != DBNull.Value)
{
ad.PostOnly = Convert.ToBoolean(dr[0]);
if (ad.PostOnly == true)
{
ad.ContactPref = AccountDetails.ContactPreferences.Post;
}
}
if (dr[1] != DBNull.Value)
{
ad.EmailOnly = Convert.ToBoolean(dr[1]);
if (ad.EmailOnly == true)
{
ad.ContactPref = AccountDetails.ContactPreferences.email;
}
}
if (dr[2] != DBNull.Value)
{
ad.PostAndEmail = Convert.ToBoolean(dr[2]);
if (ad.PostAndEmail == true)
{
ad.ContactPref = AccountDetails.ContactPreferences.postandemail;
}
}
}
AccountDetails ad1 = new AccountDetails();
foreach (DataRow dr in dt.Rows)
{
if (dr[0] != DBNull.Value)
{
ad1.PostOnly = Convert.ToBoolean(dr[0]);
if (ad1.PostOnly == true)
{
ad1.ContactPref = AccountDetails.ContactPreferences.Post;
}
}
if (dr[1] != DBNull.Value)
{
ad1.EmailOnly = Convert.ToBoolean(dr[1]);
if (ad1.EmailOnly == true)
{
ad1.ContactPref = AccountDetails.ContactPreferences.email;
}
}
if (dr[2] != DBNull.Value)
{
ad1.PostAndEmail = Convert.ToBoolean(dr[2]);
if (ad1.PostAndEmail == true)
{
ad1.ContactPref = AccountDetails.ContactPreferences.postandemail;
}
}
if (ad.ContactPref != ad1.ContactPref)
{
ad.EmailOnly = false;
ad.PostAndEmail = false;
ad.PostOnly = false;
}
}
return(ad);
}
public static List <ServiceCharges> GetAllBankTransactions(int BankAccountID)
{
List <string> p = new List <string>();
p.Add("@BankAccountID");
List <object> o = new List <object>();
o.Add(BankAccountID);
dbConn.DBConnectionObject db = GlobalVariables.GetConnection();
DataTable dt = db.Connection.GetDataTable("dbo.GetBankAccountTransactions", p, o);
List <ServiceCharges> r = new List <ServiceCharges>();
if (dt.Rows.Count > 0 && dt.Rows[0][0].ToString() != "Error")
{
double TotalBalance = 0;
foreach (DataRow dr in dt.Rows)
{
int id = 0;
int.TryParse(dr[0].ToString(), out id);
DateTime TDate = new DateTime();
if (dr[2] != DBNull.Value)
{
DateTime.TryParse(dr[2].ToString(), out TDate);
}
//transaction cost
double c = 0;
if (dr[3] != DBNull.Value)
{
c = Convert.ToDouble(dr[3]);
}
//balance
if (dr[5].ToString() == "Cr")
{
TotalBalance += c;
}
else if (dr[5].ToString() == "Dr")
{
TotalBalance -= c;
}
string cstr = Controls.CurrencyString(c);
r.Add(new ServiceCharges
{
ID = id,
TransDatestr = Controls.DateString(TDate),
TransDate = TDate,
TransDescription = dr[4].ToString(),
CrDr = dr[5].ToString(),
TransAmount = c,
TransAmountstr = cstr,
Balance = TotalBalance,
BalanceStr = Controls.CurrencyString(TotalBalance)
});
}
}
return(r);
}
