public async static Task <List <EstateCases> > GetAllEstateCases(
int UserID = 0,
int estateID = 0,
EstateCases.CaseStatus _status = Cases.CaseStatus.None,
DateTime StartDate = new DateTime(), DateTime EndDate = new DateTime(),
int userid = 0)
{
DataTable dt = await GetAllEstateCasesDataTable(UserID, estateID, _status,
StartDate : StartDate, EndDate : EndDate, userid : userid);
List <EstateCases> r = new List <EstateCases>();
if (dt.Rows.Count > 0 && dt.Rows[0][0].ToString() != "Error")
{
foreach (DataRow dr in dt.Rows)
{
try
{
int id = Convert.ToInt32(dr[0].ToString());
int eID = Convert.ToInt32(dr[3].ToString());
int ownerid = Convert.ToInt32(dr[5].ToString());
int _TemplateID = Convert.ToInt32(dr[10].ToString());
long docinstanceid = (long)Convert.ToInt64(dr[6].ToString());
DateTime TargetDate = Convert.ToDateTime(dr[8].ToString());
//int.TryParse(dr[0].ToString(), out int id);
//int.TryParse(dr[3].ToString(), out int eID);
//int.TryParse(dr[5].ToString(), out int ownerid);
//int.TryParse(dr[10].ToString(), out int _TemplateID);
//long.TryParse(dr[6].ToString(), out long docinstanceid);
//DateTime.TryParse(dr[8].ToString(), out DateTime TargetDate);
EstateCases.CaseStatus status = new Cases.CaseStatus();
if (dr[9].ToString() == "Open")
{
status = Cases.CaseStatus.Open;
}
else if (dr[9].ToString() == "Closed")
{
status = Cases.CaseStatus.Closed;
}
r.Add(new EstateCases(eID, dr[4].ToString())
{
id = id,
CaseDetails = dr[2].ToString(),
CaseTitle = dr[1].ToString(),
CaseOwner = new UsersDLL.Users {
UserID = ownerid,
UserFullName = dr[7].ToString()
},
DocInstanceId = docinstanceid,
TargetCompletionDate = TargetDate,
TargetCompletionString = TargetDate.ToShortDateString(),
caseStatus = status,
TemplateID = _TemplateID
});
} catch { }
}
}
//List<EstateCases> r = new List<EstateCases>();
return(r);
}
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);
}
