csharpcode/patientman/PatientMan/Service References/Actions/clsHivQualExport.cs

using System;
using System.Collections.Generic;
using System.Linq;
using Datalib.DatabaseSpecific;
using System.Windows.Forms;
using Datalib.Linq;
using Datalib.EntityClasses;
using Datalib.FactoryClasses;
using Datalib.HelperClasses;
using PatientMan.Classes;



namespace PatientMan.Classes
{
    class clsHivQualExport
    {

  
        public DateTime BeginDate;
        public DateTime EndDate;
        public DataAccessAdapter adapter = new DataAccessAdapter(SettingInfo.Constr);
        
        private EntityCollection<TblPatientEntity> Patients = new EntityCollection<TblPatientEntity>();
        private EntityCollection<TblExaminationInfoEntity> Exams = new EntityCollection<TblExaminationInfoEntity>();
        private EntityCollection<TblTreatmentInfoEntity> Treatments = new EntityCollection<TblTreatmentInfoEntity>();
        private EntityCollection<TblPreClinicsInfoEntity> Tests = new EntityCollection<TblPreClinicsInfoEntity>();

     public void HivQual()
        {
            SaveFileDialog saveDlg = new SaveFileDialog();
            saveDlg.Filter = "Excel File|*.xlsx";
            if (saveDlg.ShowDialog() != DialogResult.OK) return;
          
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(Exams, null);
            adapter.FetchEntityCollection(Treatments, null);
            adapter.FetchEntityCollection(Tests, null);
            var setLastExams = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate).GroupBy(p => p.PatientId).Select (g=>g.OrderBy(m=>m.ExamDate).Last()).ToList();
         
           var setPatients = setLastExams.Join(Patients, p => p.PatientId, q => q.PatientId, (p, q) => new
           {
               MaOPC = DBNull.Value,
               MaBA = p.PatientId,
               TuNgay = BeginDate,
               DenNgay = EndDate,
               NgayThuThap = DateTime.Today,
               NguoiThuThap = "USAID/SMART TA eLog",
               NgayDangKy = q.DateofRegistration,
               ChuaARVCT = (q.HaveArvBefore != 1 && q.Dateofreferral != null)?"True":"False",
               ARVCT = (q.HaveArvBefore == 1 && q.Dateofreferral != null)?"True":"False",
               NgayXNMG1 = (Tests.Where(m => m.Testid == "03" || m.Testid == "04").Where(m => m.TestDate >= BeginDate && m.TestDate <= EndDate && m.PatientId == p.PatientId).Count() != 0) ? Tests.Where(m => m.Testid == "03" || m.Testid == "04").Where(m => m.TestDate >= BeginDate && m.TestDate <= EndDate && m.PatientId == p.PatientId).OrderBy(m => m.TestDate).Last().TestDate : null,
               ALT1 = (Tests.Where(m => m.Testid == "03" && m.TestDate >= BeginDate && m.TestDate <= EndDate && m.PatientId == p.PatientId).Count() != 0) ? Tests.Where(m => m.Testid == "03" && m.TestDate >= BeginDate && m.TestDate <= EndDate && m.PatientId == p.PatientId).OrderBy(m => m.TestDate).Last().QuantityResult  : null,
               AST1 = (Tests.Where(m => m.Testid == "04" && m.TestDate >= BeginDate && m.TestDate <= EndDate && m.PatientId == p.PatientId).Count() != 0) ? Tests.Where(m => m.Testid == "04" && m.TestDate >= BeginDate && m.TestDate <= EndDate && m.PatientId == p.PatientId).OrderBy(m => m.TestDate).Last().QuantityResult : null,
               NgayXNMG2 = DBNull.Value,
               ALT2 = DBNull.Value,
               AST2 = DBNull.Value,
               NgayDieuTriARV = (q.DateofArv != null && q.DateofArv < EndDate) ? q.DateofArv : null,
               NgayGDLS3 = (Exams.Where(m => m.PatientId == p.PatientId).Where(m => m.ClinicStage == 3).Count() != 0) ? Exams.Where(m => m.PatientId == p.PatientId).Where(m => m.ClinicStage == 3).First().ExamDate : null,
               NgayGDLS4 = (Exams.Where(m => m.PatientId == p.PatientId).Where(m => m.ClinicStage == 4).Count() != 0) ? Exams.Where(m => m.PatientId == p.PatientId).Where(m => m.ClinicStage == 4).First().ExamDate : null,

               NgayDuTCDieuTriARV = DBNull.Value,
               NgaySanSangDieuTriARV = DBNull.Value,
               NgayHen = (Exams.Where(g => g.PatientId == p.PatientId && g.ExamDate > BeginDate && g.ExamDate < p.ExamDate).Count() != 0) ? Exams.Where(g => g.PatientId == p.PatientId && g.ExamDate > BeginDate && g.ExamDate < p.ExamDate).OrderBy(m => m.ExamDate).Last().ReExamDate : p.ExamDate,
               NgayKham = p.ExamDate,
               NgayHenKeTiep = p.ReExamDate,
               DuPhongCTX = (Treatments.Where(m => m.PatientId == p.PatientId && m.TreatmentId == "04").Select(m => new { m.TreatmentSdate, TreatmentEdate = (m.TreatmentEdate == null) ? m.TreatmentSdate.Value.AddYears(100) : m.TreatmentEdate.Value }).Where(m => m.TreatmentSdate <= p.ExamDate && m.TreatmentEdate >= p.ExamDate).Count() != 0) ? 1 : 2,
               INH = (Treatments.Where(m => m.PatientId == p.PatientId && m.TreatmentId == "03").Select(m => new {m.TreatmentSdate, TreatmentEdate = (m.TreatmentEdate == null) ? m.TreatmentSdate.Value.AddYears(100) : m.TreatmentEdate }).Where(m => m.TreatmentSdate <= p.ExamDate && m.TreatmentEdate >= p.ExamDate).Count() != 0) ? 1 : 2,
               NguoiNhaLanhThuoc = (p.NoSelfExam==1)?1:2,
               GiaiDoanLS =(p.ClinicStage>0)?p.ClinicStage:9,
               SangLocLao = (Treatments.Where(m => m.PatientId == p.PatientId && m.TreatmentId == "02").Select(m => new {m.TreatmentSdate, TreatmentEdate = (m.TreatmentEdate == null) ? m.TreatmentSdate.Value.AddYears(100) : m.TreatmentEdate }).Where(m => m.TreatmentSdate <= p.ExamDate && m.TreatmentEdate >= p.ExamDate).Count() != 0) ? 3:(p.TbScreenResult == string.Empty)?9:(p.TbScreenResult=="02"?1:2),
               RLThanKinh=DBNull.Value,	
               VangDa =(p.Jaundice==1)?1:2,
 	           DGiaTThuDT = (q.DateofArv==null || q.DateofArv> p.ExamDate)?3:p.TreatmentFollow==1?1:2,
               NgayKT =p.EndExamDate,

           }


           ).ToList();

        

          
          
            Forms.frmExp frm = new Forms.frmExp();
            frm.grdHivQual.DataSource = setPatients;
            frm.grdHivQual.ExportToXlsx(saveDlg.FileName);
            MessageBox.Show("Dữ liệu đã được kết xuất ra tệp " + saveDlg.FileName);
            frm.Dispose();


        }
     public void CD4()
     {
         SaveFileDialog saveDlg = new SaveFileDialog();
         saveDlg.Filter = "Excel File|*.xlsx";
         if (saveDlg.ShowDialog() != DialogResult.OK) return;

         adapter.FetchEntityCollection(Patients, null);
         adapter.FetchEntityCollection(Exams, null);
         adapter.FetchEntityCollection(Treatments, null);
         adapter.FetchEntityCollection(Tests, null);
         var setLastExams = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate).GroupBy(p => p.PatientId).Select(g => g.OrderBy(m => m.ExamDate).Last()).ToList();
         var CD4 = Tests.Where(p => p.Testid == "01" && p.TestDate <= EndDate && p.TestDate >= EndDate.AddYears(-1)).ToList();
         var SetCD4 = setLastExams.Join(CD4, p => p.PatientId, q => q.PatientId, (p, q) => new
         {
             MaOPC = DBNull.Value,
             MaBA = p.PatientId,
             TuNgay = BeginDate,
             DenNgay = EndDate,
             NgayThuThap = DateTime.Today,
             NguoiThuThap = "USAID/SMART TA eLog",
             NgayXNCD4 = q.TestDate,
             KQCD4= q.QuantityResult           

         }


         ).OrderBy(m=>m.MaBA).ThenBy(m=>m.NgayXNCD4 ).ToList();
         Forms.frmExp frm = new Forms.frmExp();
         frm.grdHivQual.DataSource = SetCD4;
         frm.grdHivQual.ExportToXlsx(saveDlg.FileName);
         MessageBox.Show("Dữ liệu đã được kết xuất ra tệp " + saveDlg.FileName);
         frm.Dispose();


     }
    }
}