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); }