Code |
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();
//patients in CCHC in food integration
var qryana = (from p in db.d_person
from service in db.d_status.Where(w => w.id_person == p.id_person && w.domain == "SERVICE" && w.active == true)
from q in q_uvu.Where(w => p.id_person == w.id_person).DefaultIfEmpty()
from s in db.d_status.Where(w => w.id_person == p.id_person && w.domain == "FOODI" && w.active == true).DefaultIfEmpty()
where
s.status == "FOOD_INT_YES" &&
service.status == "CCHC" && //last service = CCHC
p.date_stopped == null
select new
{
p.id_person,
q.idApt,
p.id_dream,
p.pe_surname,
p.pe_name,
p.pe_sex,
p.pe_dob,
p.date_started,
p.pe_note
}).AsEnumerable().Select(s => new
{
s.id_person,
s.idApt,
s.id_dream,
s.pe_surname,
s.pe_name,
s.pe_sex,
dob = (s.pe_dob.HasValue) ? s.pe_dob.Value.ToShortDateString() : "",
s.date_started,
s.pe_note
});
//last BMI date and value
var qryBMI = db.d_events_att
.Where(w => w.domain == "A_VISIT" && w.date_event != null && w.vn2.HasValue)
.Select(s => new
{
s.id_person,
s.date_event,
BMI = s.vn2
}).ToList()
.GroupBy(g => new { g.id_person })
.Select(s => new
{
dati = s.MaxBy(w => w.date_event)
})
.Select(s => new
{
s.dati.id_person,
s.dati.date_event,
s.dati.BMI
}).ToList();
var qry = (from t_ana in qryana
from joined_diaria in qryBMI.Where(w => t_ana.id_person == w.id_person)
where joined_diaria.BMI > 18.5
select new
{
t_ana.id_person,
t_ana.idApt,
t_ana.id_dream,
t_ana.pe_surname,
t_ana.pe_name,
t_ana.pe_sex,
t_ana.dob,
BMI = (joined_diaria.BMI.HasValue) ? joined_diaria.BMI.ToString() : ""
}).ToDataTable();
dt = qry;
dt.TableName = "lst_data";
|