| Code |
public static DataTable evics01(params object[] Args)
{
string qry_ID = (String)Args[0];
DateTime date_from = (DateTime)Args[1];
DateTime date_to = (DateTime)Args[2];
return aptall(qry_ID, date_from, date_to, 45);
}
public static DataTable aptall(string qry_ID, DateTime date_from, DateTime date_to, double day)
{
#region DB connection DT definition and Parameters
DREAMDatabase.DREAMContext db = ConnectToModel();
DataTable dt = new DataTable("lst_data");
#endregion
//ONLY (visit or drugs)
#region Query
DateTime date_to1 = date_to.AddDays(-7);
DateTime date_p = date_to.AddDays(-day);
//urgent verify
var q_uvu = db.d_events
.AsNoTracking()
.Where(x => x.event_type.ToLower() == "app_verify" && x.date_apt_exe == null)
.Select(s => new { s.id_person, idApt = (string.IsNullOrEmpty(s.id_event) ? "" : "!") }).Distinct();
//Last appointment executed (visit or drugs)
var qry_uae = db.d_events
.AsNoTracking()
.Where(w => w.event_status == "APE-002" && w.event_type != "APP_VERIFY" )
.GroupBy(g => g.id_person)
.Select(s => new
{
id_person = s.Key,
MaxDiData = s.Max(m => m.date_apt_exe)
});
//In assistance
var patinass = (from a in db.d_person
from b in q_uvu.Where(w => w.id_person == a.id_person).DefaultIfEmpty()
where !a.date_stopped.HasValue
select new
{
a.id_person,
a.pe_name,
a.pe_surname,
a.id_dream,
a.pe_dob,
a.pe_sex,
a.date_started,
vu = b.idApt,
a.pe_note,
age = (a.pe_dob != (DateTime?)null) ?
System.Math.Round(((double)DbFunctions.DiffDays(a.pe_dob.Value, DateTime.Today) / (double)365), 1) : 0,
});
//active patients who started TARV not in AR_GAAC, PAR_FASTFLOW,PAR_3MDELIVERY
var qry_tarv = (from a in qry_uae
from b in patinass.Where(w => a.id_person == w.id_person)
from s in db.d_events.Where(w => w.id_person == b.id_person && w.event_type == "FIRST-TARV-STAR")
from s2 in db.d_status.Where(w => w.id_person == b.id_person && (w.domain == "PAR_GAAC" || w.domain == "PAR_FASTFLOW" || w.domain == "PAR_3MDELIVERY" || w.domain == "PAR_6MDELIVERY") && w.status == "YES" && w.active == true).DefaultIfEmpty()
where
s2.id_status == null // NO patients in AR_GAAC, PAR_FASTFLOW,PAR_3MDELIVERY,PAR_6MDELIVERY
select new
{
b.id_person,
b.pe_name,
b.pe_surname,
b.id_dream,
b.pe_dob,
b.pe_sex,
b.date_started,
b.vu,
b.pe_note,
startTARV2 = s.date_event, //First start tarv
a.MaxDiData,
b.age,
}).AsEnumerable()
.Where(w => w.MaxDiData.Value.Date.AddMonths(-1) > w.startTARV2.Value.Date)
.Select(s => new
{
s.pe_name,
s.pe_surname,
s.id_person,
s.id_dream,
s.pe_dob,
s.pe_sex,
s.date_started,
s.vu,
s.age,
s.pe_note,
s.MaxDiData
});
//active appointments in the future
var qry_attfut = db.d_events
.AsNoTracking()
.Where(w => w.date_apt >= date_to1 && //in the future
w.event_type != "APP_VERIFY" && //no urgent check
w.event_status == "APE-001") //active
.Select(s => new
{
s.id_person,
s.date_apt
}); //.AsQueryable();
//people with last execute appointment > x days and no active apopintments in the future
var qry_fal = from a in qry_uae
from b in qry_attfut.Where(w => w.id_person == a.id_person).DefaultIfEmpty()
where b.id_person == null &&
a.MaxDiData < date_p
select new { a.id_person };
//join
var qry_ttt = (from a in qry_tarv
join b in qry_fal on a.id_person equals b.id_person
select new
{
a.id_person,
a.id_dream,
a.pe_name,
a.pe_surname,
a.pe_dob,
a.pe_sex,
a.date_started,
a.vu,
a.age,
a.pe_note,
a.MaxDiData
}).AsEnumerable()
.Select(s => new
{
s.id_person,
s.vu,
s.id_dream,
s.pe_name,
s.pe_surname,
s.pe_sex,
s.pe_dob,
s.age,
s.MaxDiData,
s.pe_note
});
dt = qry_ttt.ToDataTable();
dt.TableName = "lst_data";
#endregion
return dt;
}
|