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

namespace PatientMan.Classes
{
    public class clsMer
    {
        public DevExpress.XtraSpreadsheet.SpreadsheetControl spreed;
        public DateTime BeginDate;
        public DateTime EndDate;
        private DevExpress.Spreadsheet.Worksheet _Rep, _Tracking;
        public DataAccessAdapter adapter = new DataAccessAdapter(SettingInfo.Constr);
        public LinqMetaData meta = new LinqMetaData(new DataAccessAdapter(SettingInfo.Constr));
        public void Calculate()
        {
            _Rep = spreed.Document.Worksheets["Rep"];
            _Tracking = spreed.Document.Worksheets["Tracking"];

           CARE_NEW();
           CARE_CURR();
           TX_NEW();
           TX_CURR();
           TX_RETA();
           TX_RETB();
           TB_IPTA();
           TB_IPTB();
           TB_SCREENA();
           TB_SCREENB();
           _Rep.Cells[2, 1].Value = "Từ ngày " + BeginDate.ToShortDateString() + " Đến ngày " + EndDate.ToShortDateString();
        }

        private void CARE_NEW()
        {
            int[] indexM = new int[8];
            int[] indexF = new int[8];
            string[] indexsM = new string[8];
            string[] indexsF = new string[8];

            EntityCollection<TblExaminationInfoEntity> Exams = new EntityCollection<TblExaminationInfoEntity>();
            EntityCollection<TblPatientEntity> Patients = new EntityCollection<TblPatientEntity>();
            adapter.FetchEntityCollection(Exams, null);
            adapter.FetchEntityCollection(Patients, null);

            List<string> Includes = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate).Select(p => p.PatientId).ToList();
        
         #region Man
            var indexMpreresult = Patients.Where(p => p.DateofRegistration >= BeginDate && p.DateofRegistration <= EndDate && p.Dateofreferral == null).Where(p => Includes.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 1).ToList();
                            
            var indexMresult = indexMpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId,p.Age, Grp1=q.Grp1.Trim() }).ToList();
            
            indexM[0] = indexMresult.Where(p => p.Grp1 == "<1").Count();
            indexsM[0] = string.Join(",", indexMresult.Where(p => p.Grp1 == "<1").Select(p=>p.PatientId).ToArray());
          
            indexM[1] = indexMresult.Where(p => p.Grp1 == "1-4").Count();
            indexsM[1] = string.Join(",", indexMresult.Where(p => p.Grp1 == "1-4").Select(p => p.PatientId).ToArray());
            
            indexM[2] = indexMresult.Where(p => p.Grp1 == "5-9").Count();
            indexsM[2] = string.Join(",", indexMresult.Where(p => p.Grp1 == "5-9").Select(p => p.PatientId).ToArray());

            indexM[3] = indexMresult.Where(p => p.Grp1 == "10-14").Count();
            indexsM[3] = string.Join(",", indexMresult.Where(p => p.Grp1 == "10-14").Select(p => p.PatientId).ToArray());
           
            indexM[4] = indexMresult.Where(p => p.Grp1 == "15-19").Count();
            indexsM[4] = string.Join(",", indexMresult.Where(p => p.Grp1 == "15-19").Select(p => p.PatientId).ToArray());

            indexM[5] = indexMresult.Where(p => p.Grp1 == "20-24").Count();
            indexsM[5] = string.Join(",", indexMresult.Where(p => p.Grp1 == "20-24").Select(p => p.PatientId).ToArray());

            indexM[6] = indexMresult.Where(p => p.Grp1 == "25-49").Count();
            indexsM[6] = string.Join(",", indexMresult.Where(p => p.Grp1 == "25-49").Select(p => p.PatientId).ToArray());
          
            indexM[7] = indexMresult.Where(p => p.Grp1 == "50+").Count();
            indexsM[7] = string.Join(",", indexMresult.Where(p => p.Grp1 == "50+").Select(p => p.PatientId).ToArray());
                                                                                                                                              
         
         #endregion
         #region Female

            var indexFpreresult = Patients.Where(p => p.DateofRegistration >= BeginDate && p.DateofRegistration <= EndDate && p.Dateofreferral == null).Where(p => Includes.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 2).ToList();
            var indexFresult = indexFpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp1 }).ToList();
       
            indexF[0] = indexFresult.Where(p => p.Grp1 == "<1").Count();
            indexsF[0] = string.Join(",", indexFresult.Where(p => p.Grp1 == "<1").Select(p => p.PatientId).ToArray());

            indexF[1] = indexFresult.Where(p => p.Grp1 == "1-4").Count();
            indexsF[1] = string.Join(",", indexFresult.Where(p => p.Grp1 == "1-4").Select(p => p.PatientId).ToArray());

            indexF[2] = indexFresult.Where(p => p.Grp1 == "5-9").Count();
            indexsF[2] = string.Join(",", indexFresult.Where(p => p.Grp1 == "5-9").Select(p => p.PatientId).ToArray());

            indexF[3] = indexFresult.Where(p => p.Grp1 == "10-14").Count();
            indexsF[3] = string.Join(",", indexFresult.Where(p => p.Grp1 == "10-14").Select(p => p.PatientId).ToArray());

            indexF[4] = indexFresult.Where(p => p.Grp1 == "15-19").Count();
            indexsF[4] = string.Join(",", indexFresult.Where(p => p.Grp1 == "15-19").Select(p => p.PatientId).ToArray());

            indexF[5] = indexFresult.Where(p => p.Grp1 == "20-24").Count();
            indexsF[5] = string.Join(",", indexFresult.Where(p => p.Grp1 == "20-24").Select(p => p.PatientId).ToArray());

            indexF[6] = indexFresult.Where(p => p.Grp1 == "25-49").Count();
            indexsF[6] = string.Join(",", indexFresult.Where(p => p.Grp1 == "25-49").Select(p => p.PatientId).ToArray());

            indexF[7] = indexFresult.Where(p => p.Grp1 == "50+").Count();
            indexsF[7] = string.Join(",", indexFresult.Where(p => p.Grp1 == "50+").Select(p => p.PatientId).ToArray());


            #endregion

         #region fill
            int row = 4;

            _Rep.Cells[row++, 2].Value = indexM[0]; _Tracking.Cells[row-1, 2].Value = indexsM[0];
            _Rep.Cells[row++, 2].Value = indexM[1]; _Tracking.Cells[row - 1, 2].Value = indexsM[1];
            _Rep.Cells[row++, 2].Value = indexM[2]; _Tracking.Cells[row - 1, 2].Value = indexsM[2];
            _Rep.Cells[row++, 2].Value = indexM[3]; _Tracking.Cells[row - 1, 2].Value = indexsM[3];
            _Rep.Cells[row++, 2].Value = indexM[4]; _Tracking.Cells[row - 1, 2].Value = indexsM[4];
            _Rep.Cells[row++, 2].Value = indexM[5]; _Tracking.Cells[row - 1, 2].Value = indexsM[5];
            _Rep.Cells[row++, 2].Value = indexM[6]; _Tracking.Cells[row - 1, 2].Value = indexsM[6];
            _Rep.Cells[row++, 2].Value = indexM[7]; _Tracking.Cells[row - 1, 2].Value = indexsM[7];

            //Nữ
            _Rep.Cells[row++, 2].Value = indexF[0]; _Tracking.Cells[row - 1, 2].Value = indexsF[0];
            _Rep.Cells[row++, 2].Value = indexF[1]; _Tracking.Cells[row - 1, 2].Value = indexsF[1];
            _Rep.Cells[row++, 2].Value = indexF[2]; _Tracking.Cells[row - 1, 2].Value = indexsF[2];
            _Rep.Cells[row++, 2].Value = indexF[3]; _Tracking.Cells[row - 1, 2].Value = indexsF[3];
            _Rep.Cells[row++, 2].Value = indexF[4]; _Tracking.Cells[row - 1, 2].Value = indexsF[4];
            _Rep.Cells[row++, 2].Value = indexF[5]; _Tracking.Cells[row - 1, 2].Value = indexsF[5];
            _Rep.Cells[row++, 2].Value = indexF[6]; _Tracking.Cells[row - 1, 2].Value = indexsF[6];
            _Rep.Cells[row++, 2].Value = indexF[7]; _Tracking.Cells[row - 1, 2].Value = indexsF[7];
                   



         #endregion
        }
        private void CARE_CURR()
        {
            int[] indexM = new int[8];
            int[] indexF = new int[8];
            string[] indexsM = new string[8];
            string[] indexsF = new string[8];
            EntityCollection<TblPatientEntity> Patients = new EntityCollection<TblPatientEntity>();
            EntityCollection<TblExaminationInfoEntity> Exams = new EntityCollection<TblExaminationInfoEntity>();
            EntityCollection<TblPreClinicsInfoEntity> PreClinicInfo = new EntityCollection<TblPreClinicsInfoEntity>();


            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(Exams, null);
            adapter.FetchEntityCollection(PreClinicInfo, null);

            List<string> clinicStage = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate && p.ClinicStage != 0).Select(p => p.PatientId).ToList();
            List<string> Tests = PreClinicInfo.Where(p => p.TestDate >= BeginDate && p.TestDate <= EndDate && (p.Testid == "01" || p.Testid == "02")).Select(p => p.PatientId).ToList();

            #region Man
            var indexMpreresult = Patients.Where(p => clinicStage.Contains(p.PatientId)).Union(Patients.Where(p => Tests.Contains(p.PatientId))).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 1).GroupBy(p => p.PatientId).Select(g => g.First()).ToList();

            var indexMresult = indexMpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp1 }).ToList();

            indexM[0] = indexMresult.Where(p => p.Grp1 == "<1").Count();
            indexsM[0] = string.Join(",", indexMresult.Where(p => p.Grp1 == "<1").Select(p => p.PatientId).ToArray());

            indexM[1] = indexMresult.Where(p => p.Grp1 == "1-4").Count();
            indexsM[1] = string.Join(",", indexMresult.Where(p => p.Grp1 == "1-4").Select(p => p.PatientId).ToArray());

            indexM[2] = indexMresult.Where(p => p.Grp1 == "5-9").Count();
            indexsM[2] = string.Join(",", indexMresult.Where(p => p.Grp1 == "5-9").Select(p => p.PatientId).ToArray());

            indexM[3] = indexMresult.Where(p => p.Grp1 == "10-14").Count();
            indexsM[3] = string.Join(",", indexMresult.Where(p => p.Grp1 == "10-14").Select(p => p.PatientId).ToArray());

            indexM[4] = indexMresult.Where(p => p.Grp1 == "15-19").Count();
            indexsM[4] = string.Join(",", indexMresult.Where(p => p.Grp1 == "15-19").Select(p => p.PatientId).ToArray());

            indexM[5] = indexMresult.Where(p => p.Grp1 == "20-24").Count();
            indexsM[5] = string.Join(",", indexMresult.Where(p => p.Grp1 == "20-24").Select(p => p.PatientId).ToArray());

            indexM[6] = indexMresult.Where(p => p.Grp1 == "25-49").Count();
            indexsM[6] = string.Join(",", indexMresult.Where(p => p.Grp1 == "25-49").Select(p => p.PatientId).ToArray());

            indexM[7] = indexMresult.Where(p => p.Grp1 == "50+").Count();
            indexsM[7] = string.Join(",", indexMresult.Where(p => p.Grp1 == "50+").Select(p => p.PatientId).ToArray());


            #endregion
            #region Female

            var indexFpreresult = Patients.Where(p => clinicStage.Contains(p.PatientId)).Union(Patients.Where(p => Tests.Contains(p.PatientId))).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 2).GroupBy(p => p.PatientId).Select(g => g.First()).ToList();

            var indexFresult = indexFpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp1 }).ToList();

            indexF[0] = indexFresult.Where(p => p.Grp1 == "<1").Count();
            indexsF[0] = string.Join(",", indexFresult.Where(p => p.Grp1 == "<1").Select(p => p.PatientId).ToArray());

            indexF[1] = indexFresult.Where(p => p.Grp1 == "1-4").Count();
            indexsF[1] = string.Join(",", indexFresult.Where(p => p.Grp1 == "1-4").Select(p => p.PatientId).ToArray());

            indexF[2] = indexFresult.Where(p => p.Grp1 == "5-9").Count();
            indexsF[2] = string.Join(",", indexFresult.Where(p => p.Grp1 == "5-9").Select(p => p.PatientId).ToArray());

            indexF[3] = indexFresult.Where(p => p.Grp1 == "10-14").Count();
            indexsF[3] = string.Join(",", indexFresult.Where(p => p.Grp1 == "10-14").Select(p => p.PatientId).ToArray());

            indexF[4] = indexFresult.Where(p => p.Grp1 == "15-19").Count();
            indexsF[4] = string.Join(",", indexFresult.Where(p => p.Grp1 == "15-19").Select(p => p.PatientId).ToArray());

            indexF[5] = indexFresult.Where(p => p.Grp1 == "20-24").Count();
            indexsF[5] = string.Join(",", indexFresult.Where(p => p.Grp1 == "20-24").Select(p => p.PatientId).ToArray());

            indexF[6] = indexFresult.Where(p => p.Grp1 == "25-49").Count();
            indexsF[6] = string.Join(",", indexFresult.Where(p => p.Grp1 == "25-49").Select(p => p.PatientId).ToArray());

            indexF[7] = indexFresult.Where(p => p.Grp1 == "50+").Count();
            indexsF[7] = string.Join(",", indexFresult.Where(p => p.Grp1 == "50+").Select(p => p.PatientId).ToArray());


            #endregion

            #region fill
            int row = 20;
           //Nam
            _Rep.Cells[row++, 2].Value = indexM[0]; _Tracking.Cells[row - 1, 2].Value = indexsM[0];
            _Rep.Cells[row++, 2].Value = indexM[1]; _Tracking.Cells[row - 1, 2].Value = indexsM[1];
            _Rep.Cells[row++, 2].Value = indexM[2]; _Tracking.Cells[row - 1, 2].Value = indexsM[2];
            _Rep.Cells[row++, 2].Value = indexM[3]; _Tracking.Cells[row - 1, 2].Value = indexsM[3];
            _Rep.Cells[row++, 2].Value = indexM[4]; _Tracking.Cells[row - 1, 2].Value = indexsM[4];
            _Rep.Cells[row++, 2].Value = indexM[5]; _Tracking.Cells[row - 1, 2].Value = indexsM[5];
            _Rep.Cells[row++, 2].Value = indexM[6]; _Tracking.Cells[row - 1, 2].Value = indexsM[6];
            _Rep.Cells[row++, 2].Value = indexM[7]; _Tracking.Cells[row - 1, 2].Value = indexsM[7];

          //Nữ
            _Rep.Cells[row++, 2].Value = indexF[0]; _Tracking.Cells[row - 1, 2].Value = indexsF[0];
            _Rep.Cells[row++, 2].Value = indexF[1]; _Tracking.Cells[row - 1, 2].Value = indexsF[1];
            _Rep.Cells[row++, 2].Value = indexF[2]; _Tracking.Cells[row - 1, 2].Value = indexsF[2];
            _Rep.Cells[row++, 2].Value = indexF[3]; _Tracking.Cells[row - 1, 2].Value = indexsF[3];
            _Rep.Cells[row++, 2].Value = indexF[4]; _Tracking.Cells[row - 1, 2].Value = indexsF[4];
            _Rep.Cells[row++, 2].Value = indexF[5]; _Tracking.Cells[row - 1, 2].Value = indexsF[5];
            _Rep.Cells[row++, 2].Value = indexF[6]; _Tracking.Cells[row - 1, 2].Value = indexsF[6];
            _Rep.Cells[row++, 2].Value = indexF[7]; _Tracking.Cells[row - 1, 2].Value = indexsF[7];
                   
            
            #endregion
        }

        private void TX_NEW()
        {
            int[] indexM = new int[8];
            int[] indexF = new int[8];
            string[] indexsM = new string[8];
            string[] indexsF = new string[8];

            #region Man
            var indexMpreresult = meta.TblPatient.Where(p => p.DateofArv >= BeginDate && p.DateofArv <= EndDate && p.HaveArvBefore !=1 ).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 1).ToList();
            var indexMresult = indexMpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp1 }).ToList();

            indexM[0] = indexMresult.Where(p => p.Grp1 == "<1").Count();
            indexsM[0] = string.Join(",", indexMresult.Where(p => p.Grp1 == "<1").Select(p => p.PatientId).ToArray());

            indexM[1] = indexMresult.Where(p => p.Grp1 == "1-4").Count();
            indexsM[1] = string.Join(",", indexMresult.Where(p => p.Grp1 == "1-4").Select(p => p.PatientId).ToArray());

            indexM[2] = indexMresult.Where(p => p.Grp1 == "5-9").Count();
            indexsM[2] = string.Join(",", indexMresult.Where(p => p.Grp1 == "5-9").Select(p => p.PatientId).ToArray());

            indexM[3] = indexMresult.Where(p => p.Grp1 == "10-14").Count();
            indexsM[3] = string.Join(",", indexMresult.Where(p => p.Grp1 == "10-14").Select(p => p.PatientId).ToArray());

            indexM[4] = indexMresult.Where(p => p.Grp1 == "15-19").Count();
            indexsM[4] = string.Join(",", indexMresult.Where(p => p.Grp1 == "15-19").Select(p => p.PatientId).ToArray());

            indexM[5] = indexMresult.Where(p => p.Grp1 == "20-24").Count();
            indexsM[5] = string.Join(",", indexMresult.Where(p => p.Grp1 == "20-24").Select(p => p.PatientId).ToArray());

            indexM[6] = indexMresult.Where(p => p.Grp1 == "25-49").Count();
            indexsM[6] = string.Join(",", indexMresult.Where(p => p.Grp1 == "25-49").Select(p => p.PatientId).ToArray());

            indexM[7] = indexMresult.Where(p => p.Grp1 == "50+").Count();
            indexsM[7] = string.Join(",", indexMresult.Where(p => p.Grp1 == "50+").Select(p => p.PatientId).ToArray());


            #endregion
            #region Female

            var indexFpreresult = meta.TblPatient.Where(p => p.DateofArv >= BeginDate && p.DateofArv <= EndDate && p.HaveArvBefore != 1).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 2).ToList();
            var indexFresult = indexFpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp1 }).ToList();

            indexF[0] = indexFresult.Where(p => p.Grp1 == "<1").Count();
            indexsF[0] = string.Join(",", indexFresult.Where(p => p.Grp1 == "<1").Select(p => p.PatientId).ToArray());

            indexF[1] = indexFresult.Where(p => p.Grp1 == "1-4").Count();
            indexsF[1] = string.Join(",", indexFresult.Where(p => p.Grp1 == "1-4").Select(p => p.PatientId).ToArray());

            indexF[2] = indexFresult.Where(p => p.Grp1 == "5-9").Count();
            indexsF[2] = string.Join(",", indexFresult.Where(p => p.Grp1 == "5-9").Select(p => p.PatientId).ToArray());

            indexF[3] = indexFresult.Where(p => p.Grp1 == "10-14").Count();
            indexsF[3] = string.Join(",", indexFresult.Where(p => p.Grp1 == "10-14").Select(p => p.PatientId).ToArray());

            indexF[4] = indexFresult.Where(p => p.Grp1 == "15-19").Count();
            indexsF[4] = string.Join(",", indexFresult.Where(p => p.Grp1 == "15-19").Select(p => p.PatientId).ToArray());

            indexF[5] = indexFresult.Where(p => p.Grp1 == "20-24").Count();
            indexsF[5] = string.Join(",", indexFresult.Where(p => p.Grp1 == "20-24").Select(p => p.PatientId).ToArray());

            indexF[6] = indexFresult.Where(p => p.Grp1 == "25-49").Count();
            indexsF[6] = string.Join(",", indexFresult.Where(p => p.Grp1 == "25-49").Select(p => p.PatientId).ToArray());

            indexF[7] = indexFresult.Where(p => p.Grp1 == "50+").Count();
            indexsF[7] = string.Join(",", indexFresult.Where(p => p.Grp1 == "50+").Select(p => p.PatientId).ToArray());


            #endregion        
            #region fill
            int row = 36;
            //Nam
            _Rep.Cells[row++, 2].Value = indexM[0]; _Tracking.Cells[row - 1, 2].Value = indexsM[0];
            _Rep.Cells[row++, 2].Value = indexM[1]; _Tracking.Cells[row - 1, 2].Value = indexsM[1];
            _Rep.Cells[row++, 2].Value = indexM[2]; _Tracking.Cells[row - 1, 2].Value = indexsM[2];
            _Rep.Cells[row++, 2].Value = indexM[3]; _Tracking.Cells[row - 1, 2].Value = indexsM[3];
            _Rep.Cells[row++, 2].Value = indexM[4]; _Tracking.Cells[row - 1, 2].Value = indexsM[4];
            _Rep.Cells[row++, 2].Value = indexM[5]; _Tracking.Cells[row - 1, 2].Value = indexsM[5];
            _Rep.Cells[row++, 2].Value = indexM[6]; _Tracking.Cells[row - 1, 2].Value = indexsM[6];
            _Rep.Cells[row++, 2].Value = indexM[7]; _Tracking.Cells[row - 1, 2].Value = indexsM[7];

            //Nữ
            _Rep.Cells[row++, 2].Value = indexF[0]; _Tracking.Cells[row - 1, 2].Value = indexsF[0];
            _Rep.Cells[row++, 2].Value = indexF[1]; _Tracking.Cells[row - 1, 2].Value = indexsF[1];
            _Rep.Cells[row++, 2].Value = indexF[2]; _Tracking.Cells[row - 1, 2].Value = indexsF[2];
            _Rep.Cells[row++, 2].Value = indexF[3]; _Tracking.Cells[row - 1, 2].Value = indexsF[3];
            _Rep.Cells[row++, 2].Value = indexF[4]; _Tracking.Cells[row - 1, 2].Value = indexsF[4];
            _Rep.Cells[row++, 2].Value = indexF[5]; _Tracking.Cells[row - 1, 2].Value = indexsF[5];
            _Rep.Cells[row++, 2].Value = indexF[6]; _Tracking.Cells[row - 1, 2].Value = indexsF[6];
            _Rep.Cells[row++, 2].Value = indexF[7]; _Tracking.Cells[row - 1, 2].Value = indexsF[7];


            #endregion
                    
        }

        private void TX_CURR()
        {
            var Patients = new EntityCollection<TblPatientEntity>();
            var Treatments = new EntityCollection<TblTreatmentInfoEntity>();
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(Treatments, null);
                      
            int[] indexM = new int[8];
            int[] indexF = new int[8];
            string[] indexsM = new string[8];
            string[] indexsF = new string[8];
            var LastArvs = Treatments.Where(p => p.TreatmentSdate < EndDate && p.TreatmentId == "01").GroupBy(q => q.PatientId).Select(g => g.OrderByDescending(m => m.TreatmentSdate).First()).ToList().Where(s => s.TreatmentEdate == null || s.TreatmentEdate > EndDate).Select(t => t.PatientId).ToList();

            var indexpreresult = Patients.Where(m => m.DateofArv != null && m.DateofArv <= EndDate).Where(p => LastArvs.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).ToList();
   


            #region Man
            var indexMpreresult = indexpreresult.Where(p => p.Sex == 1).ToList();
            var indexMresult = indexMpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexM[0] = indexMresult.Where(p => p.Grp2 == "<1").Count();
            indexsM[0] = string.Join(",", indexMresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexM[1] = indexMresult.Where(p => p.Grp2 == "1-4").Count();
            indexsM[1] = string.Join(",", indexMresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexM[2] = indexMresult.Where(p => p.Grp2 == "5-9").Count();
            indexsM[2] = string.Join(",", indexMresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexM[3] = indexMresult.Where(p => p.Grp2 == "10-14").Count();
            indexsM[3] = string.Join(",", indexMresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexM[4] = indexMresult.Where(p => p.Grp2 == "15-19").Count();
            indexsM[4] = string.Join(",", indexMresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());
            
            indexM[5] = indexMresult.Where(p => p.Grp2 == "20+").Count();
            indexsM[5] = string.Join(",", indexMresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());


            #endregion
            #region Female

            var indexFpreresult = indexpreresult.Where(p => p.Sex == 2).ToList();
            var indexFresult = indexFpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexF[0] = indexFresult.Where(p => p.Grp2 == "<1").Count();
            indexsF[0] = string.Join(",", indexFresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexF[1] = indexFresult.Where(p => p.Grp2 == "1-4").Count();
            indexsF[1] = string.Join(",", indexFresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexF[2] = indexFresult.Where(p => p.Grp2 == "5-9").Count();
            indexsF[2] = string.Join(",", indexFresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexF[3] = indexFresult.Where(p => p.Grp2 == "10-14").Count();
            indexsF[3] = string.Join(",", indexFresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexF[4] = indexFresult.Where(p => p.Grp2 == "15-19").Count();
            indexsF[4] = string.Join(",", indexFresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());

            indexF[5] = indexFresult.Where(p => p.Grp2 == "20+").Count();
            indexsF[5] = string.Join(",", indexFresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());


            #endregion
            #region fill
            int row = 52;
            //Nam
            _Rep.Cells[row++, 2].Value = indexM[0]; _Tracking.Cells[row - 1, 2].Value = indexsM[0];
            _Rep.Cells[row++, 2].Value = indexM[1]; _Tracking.Cells[row - 1, 2].Value = indexsM[1];
            _Rep.Cells[row++, 2].Value = indexM[2]; _Tracking.Cells[row - 1, 2].Value = indexsM[2];
            _Rep.Cells[row++, 2].Value = indexM[3]; _Tracking.Cells[row - 1, 2].Value = indexsM[3];
            _Rep.Cells[row++, 2].Value = indexM[4]; _Tracking.Cells[row - 1, 2].Value = indexsM[4];
            _Rep.Cells[row++, 2].Value = indexM[5]; _Tracking.Cells[row - 1, 2].Value = indexsM[5];
           
            //Nữ
            _Rep.Cells[row++, 2].Value = indexF[0]; _Tracking.Cells[row - 1, 2].Value = indexsF[0];
            _Rep.Cells[row++, 2].Value = indexF[1]; _Tracking.Cells[row - 1, 2].Value = indexsF[1];
            _Rep.Cells[row++, 2].Value = indexF[2]; _Tracking.Cells[row - 1, 2].Value = indexsF[2];
            _Rep.Cells[row++, 2].Value = indexF[3]; _Tracking.Cells[row - 1, 2].Value = indexsF[3];
            _Rep.Cells[row++, 2].Value = indexF[4]; _Tracking.Cells[row - 1, 2].Value = indexsF[4];
            _Rep.Cells[row++, 2].Value = indexF[5]; _Tracking.Cells[row - 1, 2].Value = indexsF[5];
            


            #endregion
                             
        }

        private void TX_RETA()
        {
            var Patients = new EntityCollection<TblPatientEntity>();
            var Treatments = new EntityCollection<TblTreatmentInfoEntity>();
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(Treatments, null);

            int[] indexM = new int[8];
            int[] indexF = new int[8];
            string[] indexsM = new string[8];
            string[] indexsF = new string[8];
            var Arvs = Treatments.Where(p => p.TreatmentSdate < EndDate && p.TreatmentSdate >= BeginDate.AddMonths(-12) && p.TreatmentId == "01").ToList();
            var SelectedPatients = Arvs.Join(Patients.Where(p => p.DateofArv != null && p.DateofArv.Value.AddYears(1) > p.DateofRegistration && p.DateofArv >= BeginDate.AddMonths(-12) && p.DateofArv <= EndDate.AddMonths(-12)), a => a.PatientId, p => p.PatientId, (a, p) => new { p.PatientId, p.Sex, EndPointArv = p.DateofArv.Value.AddMonths(12), Age = p.DateofArv.Value.AddMonths(12).Year - p.BirthYear, a.TreatmentSdate, a.TreatmentEdate, a.ReasonId }).Where(p => p.TreatmentSdate <= p.EndPointArv).ToList();
            var indexpreresult = SelectedPatients.GroupBy(p => p.PatientId).Select(g => g.OrderBy(p => p.TreatmentSdate).Last()).Select(p => new { p.PatientId, p.Sex, p.Age, moveout = p.TreatmentEdate != null && p.TreatmentEdate < p.EndPointArv && p.ReasonId == "09" ? true : false, lost = p.TreatmentEdate != null && p.TreatmentEdate < p.EndPointArv && p.ReasonId == "10" ? true : false, dead = p.TreatmentEdate != null && p.TreatmentEdate < p.EndPointArv && p.ReasonId == "11" ? true : false }).Where(p => !p.moveout && !p.dead && !p.lost).ToList();
           
            #region Man
            var indexMpreresult = indexpreresult.Where(p => p.Sex == 1).ToList();
            var indexMresult = indexMpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexM[0] = indexMresult.Where(p => p.Grp2 == "<1").Count();
            indexsM[0] = string.Join(",", indexMresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexM[1] = indexMresult.Where(p => p.Grp2 == "1-4").Count();
            indexsM[1] = string.Join(",", indexMresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexM[2] = indexMresult.Where(p => p.Grp2 == "5-9").Count();
            indexsM[2] = string.Join(",", indexMresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexM[3] = indexMresult.Where(p => p.Grp2 == "10-14").Count();
            indexsM[3] = string.Join(",", indexMresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexM[4] = indexMresult.Where(p => p.Grp2 == "15-19").Count();
            indexsM[4] = string.Join(",", indexMresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());

            indexM[5] = indexMresult.Where(p => p.Grp2 == "20+").Count();
            indexsM[5] = string.Join(",", indexMresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());


            #endregion
            #region Female

            var indexFpreresult = indexpreresult.Where(p => p.Sex == 2).ToList();
            var indexFresult = indexFpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexF[0] = indexFresult.Where(p => p.Grp2 == "<1").Count();
            indexsF[0] = string.Join(",", indexFresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexF[1] = indexFresult.Where(p => p.Grp2 == "1-4").Count();
            indexsF[1] = string.Join(",", indexFresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexF[2] = indexFresult.Where(p => p.Grp2 == "5-9").Count();
            indexsF[2] = string.Join(",", indexFresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexF[3] = indexFresult.Where(p => p.Grp2 == "10-14").Count();
            indexsF[3] = string.Join(",", indexFresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexF[4] = indexFresult.Where(p => p.Grp2 == "15-19").Count();
            indexsF[4] = string.Join(",", indexFresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());

            indexF[5] = indexFresult.Where(p => p.Grp2 == "20+").Count();
            indexsF[5] = string.Join(",", indexFresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());

            #endregion
     
            #region fill
            int row =64;
            //Nam
            _Rep.Cells[row++, 2].Value = indexM[0]; _Tracking.Cells[row - 1, 2].Value = indexsM[0];
            _Rep.Cells[row++, 2].Value = indexM[1]; _Tracking.Cells[row - 1, 2].Value = indexsM[1];
            _Rep.Cells[row++, 2].Value = indexM[2]; _Tracking.Cells[row - 1, 2].Value = indexsM[2];
            _Rep.Cells[row++, 2].Value = indexM[3]; _Tracking.Cells[row - 1, 2].Value = indexsM[3];
            _Rep.Cells[row++, 2].Value = indexM[4]; _Tracking.Cells[row - 1, 2].Value = indexsM[4];
            _Rep.Cells[row++, 2].Value = indexM[5]; _Tracking.Cells[row - 1, 2].Value = indexsM[5];
          
            //Nữ
            _Rep.Cells[row++, 2].Value = indexF[0]; _Tracking.Cells[row - 1, 2].Value = indexsF[0];
            _Rep.Cells[row++, 2].Value = indexF[1]; _Tracking.Cells[row - 1, 2].Value = indexsF[1];
            _Rep.Cells[row++, 2].Value = indexF[2]; _Tracking.Cells[row - 1, 2].Value = indexsF[2];
            _Rep.Cells[row++, 2].Value = indexF[3]; _Tracking.Cells[row - 1, 2].Value = indexsF[3];
            _Rep.Cells[row++, 2].Value = indexF[4]; _Tracking.Cells[row - 1, 2].Value = indexsF[4];
            _Rep.Cells[row++, 2].Value = indexF[5]; _Tracking.Cells[row - 1, 2].Value = indexsF[5];
         
            #endregion
     
        }

        private void TX_RETB()
        {
            var Patients = new EntityCollection<TblPatientEntity>();
            var Treatments = new EntityCollection<TblTreatmentInfoEntity>();
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(Treatments, null);

            int[] indexM = new int[8];
            int[] indexF = new int[8];
            string[] indexsM = new string[8];
            string[] indexsF = new string[8];
            var Arvs = Treatments.Where(p => p.TreatmentSdate < EndDate && p.TreatmentSdate >= BeginDate.AddMonths(-12) && p.TreatmentId == "01").ToList();

            var SelectedPatients = Arvs.Join(Patients.Where(p => p.DateofArv != null && p.DateofArv.Value.AddYears(1) > p.DateofRegistration && p.DateofArv >= BeginDate.AddMonths(-12) && p.DateofArv <= EndDate.AddMonths(-12)), a => a.PatientId, p => p.PatientId, (a, p) => new { p.PatientId, p.Sex, EndPointArv = p.DateofArv.Value.AddMonths(12), Age = p.DateofArv.Value.AddMonths(12).Year - p.BirthYear, a.TreatmentSdate, a.TreatmentEdate, a.ReasonId }).Where(p => p.TreatmentSdate <= p.EndPointArv).ToList();
          
            var indexpreresult = SelectedPatients.GroupBy(p => p.PatientId).Select(g => g.OrderBy(p => p.TreatmentSdate).Last()).Select(p => new { p.PatientId, p.Sex, p.Age, moveout = p.TreatmentEdate != null && p.TreatmentEdate < p.EndPointArv && p.ReasonId == "09" ? true : false }).Where(p => !p.moveout).ToList();
    
            #region Man
            var indexMpreresult = indexpreresult.Where(p => p.Sex == 1).ToList();
            var indexMresult = indexMpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexM[0] = indexMresult.Where(p => p.Grp2 == "<1").Count();
            indexsM[0] = string.Join(",", indexMresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexM[1] = indexMresult.Where(p => p.Grp2 == "1-4").Count();
            indexsM[1] = string.Join(",", indexMresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexM[2] = indexMresult.Where(p => p.Grp2 == "5-9").Count();
            indexsM[2] = string.Join(",", indexMresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexM[3] = indexMresult.Where(p => p.Grp2 == "10-14").Count();
            indexsM[3] = string.Join(",", indexMresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexM[4] = indexMresult.Where(p => p.Grp2 == "15-19").Count();
            indexsM[4] = string.Join(",", indexMresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());                   

            indexM[5] = indexMresult.Where(p => p.Grp2 == "20+").Count();
            indexsM[5] = string.Join(",", indexMresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());


            #endregion
            #region Female

            var indexFpreresult = indexpreresult.Where(p => p.Sex == 2).ToList();
            var indexFresult = indexFpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexF[0] = indexFresult.Where(p => p.Grp2 == "<1").Count();
            indexsF[0] = string.Join(",", indexFresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexF[1] = indexFresult.Where(p => p.Grp2 == "1-4").Count();
            indexsF[1] = string.Join(",", indexFresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexF[2] = indexFresult.Where(p => p.Grp2 == "5-9").Count();
            indexsF[2] = string.Join(",", indexFresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexF[3] = indexFresult.Where(p => p.Grp2 == "10-14").Count();
            indexsF[3] = string.Join(",", indexFresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexF[4] = indexFresult.Where(p => p.Grp2 == "15-19").Count();
            indexsF[4] = string.Join(",", indexFresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());                

            indexF[5] = indexFresult.Where(p => p.Grp2 == "20+").Count();
            indexsF[5] = string.Join(",", indexFresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());

        
            #endregion

            #region fill
            int row = 76;
            //Nam
            _Rep.Cells[row++, 2].Value = indexM[0]; _Tracking.Cells[row - 1, 2].Value = indexsM[0];
            _Rep.Cells[row++, 2].Value = indexM[1]; _Tracking.Cells[row - 1, 2].Value = indexsM[1];
            _Rep.Cells[row++, 2].Value = indexM[2]; _Tracking.Cells[row - 1, 2].Value = indexsM[2];
            _Rep.Cells[row++, 2].Value = indexM[3]; _Tracking.Cells[row - 1, 2].Value = indexsM[3];
            _Rep.Cells[row++, 2].Value = indexM[4]; _Tracking.Cells[row - 1, 2].Value = indexsM[4];
            _Rep.Cells[row++, 2].Value = indexM[5]; _Tracking.Cells[row - 1, 2].Value = indexsM[5];
            
            //Nữ
            _Rep.Cells[row++, 2].Value = indexF[0]; _Tracking.Cells[row - 1, 2].Value = indexsF[0];
            _Rep.Cells[row++, 2].Value = indexF[1]; _Tracking.Cells[row - 1, 2].Value = indexsF[1];
            _Rep.Cells[row++, 2].Value = indexF[2]; _Tracking.Cells[row - 1, 2].Value = indexsF[2];
            _Rep.Cells[row++, 2].Value = indexF[3]; _Tracking.Cells[row - 1, 2].Value = indexsF[3];
            _Rep.Cells[row++, 2].Value = indexF[4]; _Tracking.Cells[row - 1, 2].Value = indexsF[4];
            _Rep.Cells[row++, 2].Value = indexF[5]; _Tracking.Cells[row - 1, 2].Value = indexsF[5];

            #endregion
        }

        private void TB_IPTA()
        {
            int[] indexM = new int[8];
            int[] indexF = new int[8];
            string[] indexsM = new string[8];
            string[] indexsF = new string[8];

            var Patients = new EntityCollection<TblPatientEntity>();
            var Exams = new EntityCollection<TblExaminationInfoEntity>();
            var Treatments = new EntityCollection<TblTreatmentInfoEntity>();
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(Exams, null);
            adapter.FetchEntityCollection(Treatments, null);

            List<string> Includes = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate && p.ClinicStage != 0).Select(p => p.PatientId).ToList();
            #region Man
            var indexMpreresult = Patients.Where(p => p.DateofRegistration >= BeginDate && p.DateofRegistration <= EndDate && p.Dateofreferral == null).Where(p => Includes.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 1).ToList();
            var indexMresult = indexMpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            List<string> INH = Treatments.Where(p => p.TreatmentSdate >= BeginDate && p.TreatmentSdate <= EndDate && p.TreatmentId == "03").Select(p => p.PatientId).ToList();

      
            indexM[0] = indexMresult.Where(p => p.Grp2 == "<1").Where(p=>INH.Contains(p.PatientId)).Count();
            indexsM[0] = string.Join(",", indexMresult.Where(p => p.Grp2 == "<1").Where(p=>INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());

            indexM[1] = indexMresult.Where(p => p.Grp2 == "1-4").Where(p => INH.Contains(p.PatientId)).Count();
            indexsM[1] = string.Join(",", indexMresult.Where(p => p.Grp2 == "1-4").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());

            indexM[2] = indexMresult.Where(p => p.Grp2 == "5-9").Where(p => INH.Contains(p.PatientId)).Count();
            indexsM[2] = string.Join(",", indexMresult.Where(p => p.Grp2 == "5-9").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());

            indexM[3] = indexMresult.Where(p => p.Grp2 == "10-14").Where(p => INH.Contains(p.PatientId)).Count();
            indexsM[3] = string.Join(",", indexMresult.Where(p => p.Grp2 == "10-14").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());

            indexM[4] = indexMresult.Where(p => p.Grp2 == "15-19").Where(p => INH.Contains(p.PatientId)).Count();
            indexsM[4] = string.Join(",", indexMresult.Where(p => p.Grp2 == "15-19").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());

            indexM[5] = indexMresult.Where(p => p.Grp2 == "20+").Where(p => INH.Contains(p.PatientId)).Count();
            indexsM[5] = string.Join(",", indexMresult.Where(p => p.Grp2 == "20+").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());
            #endregion

            #region Female
            var indexFpreresult = Patients.Where(p => p.DateofRegistration >= BeginDate && p.DateofRegistration <= EndDate && p.Dateofreferral == null).Where(p => Includes.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 2).ToList();
            var indexFresult = indexFpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexF[0] = indexFresult.Where(p => p.Grp2 == "<1").Where(p => INH.Contains(p.PatientId)).Count();
            indexsF[0] = string.Join(",", indexFresult.Where(p => p.Grp2 == "<1").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());

            indexF[1] = indexFresult.Where(p => p.Grp2 == "1-4").Where(p => INH.Contains(p.PatientId)).Count();
            indexsF[1] = string.Join(",", indexFresult.Where(p => p.Grp2 == "1-4").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());

            indexF[2] = indexFresult.Where(p => p.Grp2 == "5-9").Where(p => INH.Contains(p.PatientId)).Count();
            indexsF[2] = string.Join(",", indexFresult.Where(p => p.Grp2 == "5-9").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());

            indexF[3] = indexFresult.Where(p => p.Grp2 == "10-14").Where(p => INH.Contains(p.PatientId)).Count();
            indexsF[3] = string.Join(",", indexFresult.Where(p => p.Grp2 == "10-14").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());

            indexF[4] = indexFresult.Where(p => p.Grp2 == "15-19").Where(p => INH.Contains(p.PatientId)).Count();
            indexsF[4] = string.Join(",", indexFresult.Where(p => p.Grp2 == "15-19").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());

            indexF[5] = indexFresult.Where(p => p.Grp2 == "20+").Where(p => INH.Contains(p.PatientId)).Count();
            indexsF[5] = string.Join(",", indexFresult.Where(p => p.Grp2 == "20+").Where(p => INH.Contains(p.PatientId)).Select(p => p.PatientId).ToArray());
            #endregion
            #region fill
            int row = 88;
            //Nam
            _Rep.Cells[row++, 2].Value = indexM[0]; _Tracking.Cells[row - 1, 2].Value = indexsM[0];
            _Rep.Cells[row++, 2].Value = indexM[1]; _Tracking.Cells[row - 1, 2].Value = indexsM[1];
            _Rep.Cells[row++, 2].Value = indexM[2]; _Tracking.Cells[row - 1, 2].Value = indexsM[2];
            _Rep.Cells[row++, 2].Value = indexM[3]; _Tracking.Cells[row - 1, 2].Value = indexsM[3];
            _Rep.Cells[row++, 2].Value = indexM[4]; _Tracking.Cells[row - 1, 2].Value = indexsM[4];
            _Rep.Cells[row++, 2].Value = indexM[5]; _Tracking.Cells[row - 1, 2].Value = indexsM[5];

            //Nữ
            _Rep.Cells[row++, 2].Value = indexF[0]; _Tracking.Cells[row - 1, 2].Value = indexsF[0];
            _Rep.Cells[row++, 2].Value = indexF[1]; _Tracking.Cells[row - 1, 2].Value = indexsF[1];
            _Rep.Cells[row++, 2].Value = indexF[2]; _Tracking.Cells[row - 1, 2].Value = indexsF[2];
            _Rep.Cells[row++, 2].Value = indexF[3]; _Tracking.Cells[row - 1, 2].Value = indexsF[3];
            _Rep.Cells[row++, 2].Value = indexF[4]; _Tracking.Cells[row - 1, 2].Value = indexsF[4];
            _Rep.Cells[row++, 2].Value = indexF[5]; _Tracking.Cells[row - 1, 2].Value = indexsF[5];

            #endregion

        }

        private void TB_IPTB()
        {

            int[] indexM = new int[8];
            int[] indexF = new int[8];
            string[] indexsM = new string[8];
            string[] indexsF = new string[8];

            var Patients = new EntityCollection<TblPatientEntity>();
            var Exams = new EntityCollection<TblExaminationInfoEntity>();
            var Treatments = new EntityCollection<TblTreatmentInfoEntity>();
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(Exams, null);
            adapter.FetchEntityCollection(Treatments, null);


            List<string> Includes = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate && p.ClinicStage != 0).Select(p => p.PatientId).ToList();
            #region Man
            var indexMpreresult = Patients.Where(p => p.DateofRegistration >= BeginDate && p.DateofRegistration <= EndDate && p.Dateofreferral == null).Where(p => Includes.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 1).ToList();
 
            var indexMresult = indexMpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();
        
            indexM[0] = indexMresult.Where(p => p.Grp2 == "<1").Count();
            indexsM[0] = string.Join(",", indexMresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexM[1] = indexMresult.Where(p => p.Grp2 == "1-4").Count();
            indexsM[1] = string.Join(",", indexMresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexM[2] = indexMresult.Where(p => p.Grp2 == "5-9").Count();
            indexsM[2] = string.Join(",", indexMresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexM[3] = indexMresult.Where(p => p.Grp2 == "10-14").Count();
            indexsM[3] = string.Join(",", indexMresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexM[4] = indexMresult.Where(p => p.Grp2 == "15-19").Count();
            indexsM[4] = string.Join(",", indexMresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());

            indexM[5] = indexMresult.Where(p => p.Grp2 == "20+").Count();
        
            indexsM[5] = string.Join(",", indexMresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());
            #endregion

            #region Female
            var indexFpreresult = Patients.Where(p => p.DateofRegistration >= BeginDate && p.DateofRegistration <= EndDate && p.Dateofreferral == null).Where(p => Includes.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 2).ToList();
            var indexFresult = indexFpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexF[0] = indexFresult.Where(p => p.Grp2 == "<1").Count();
            indexsF[0] = string.Join(",", indexFresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexF[1] = indexFresult.Where(p => p.Grp2 == "1-4").Count();
            indexsF[1] = string.Join(",", indexFresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexF[2] = indexFresult.Where(p => p.Grp2 == "5-9").Count();
            indexsF[2] = string.Join(",", indexFresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexF[3] = indexFresult.Where(p => p.Grp2 == "10-14").Count();
            indexsF[3] = string.Join(",", indexFresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexF[4] = indexFresult.Where(p => p.Grp2 == "15-19").Count();
            indexsF[4] = string.Join(",", indexFresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());

            indexF[5] = indexFresult.Where(p => p.Grp2 == "20+").Count();
            indexsF[5] = string.Join(",", indexFresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());
            #endregion

            #region fill
            int row = 100;
            //Nam
            _Rep.Cells[row++, 2].Value = indexM[0]; _Tracking.Cells[row - 1, 2].Value = indexsM[0];
            _Rep.Cells[row++, 2].Value = indexM[1]; _Tracking.Cells[row - 1, 2].Value = indexsM[1];
            _Rep.Cells[row++, 2].Value = indexM[2]; _Tracking.Cells[row - 1, 2].Value = indexsM[2];
            _Rep.Cells[row++, 2].Value = indexM[3]; _Tracking.Cells[row - 1, 2].Value = indexsM[3];
            _Rep.Cells[row++, 2].Value = indexM[4]; _Tracking.Cells[row - 1, 2].Value = indexsM[4];
            _Rep.Cells[row++, 2].Value = indexM[5]; _Tracking.Cells[row - 1, 2].Value = indexsM[5];

            //Nữ
            _Rep.Cells[row++, 2].Value = indexF[0]; _Tracking.Cells[row - 1, 2].Value = indexsF[0];
            _Rep.Cells[row++, 2].Value = indexF[1]; _Tracking.Cells[row - 1, 2].Value = indexsF[1];
            _Rep.Cells[row++, 2].Value = indexF[2]; _Tracking.Cells[row - 1, 2].Value = indexsF[2];
            _Rep.Cells[row++, 2].Value = indexF[3]; _Tracking.Cells[row - 1, 2].Value = indexsF[3];
            _Rep.Cells[row++, 2].Value = indexF[4]; _Tracking.Cells[row - 1, 2].Value = indexsF[4];
            _Rep.Cells[row++, 2].Value = indexF[5]; _Tracking.Cells[row - 1, 2].Value = indexsF[5];

            #endregion
        }

        private void TB_SCREENA()
        {
            int[] indexM = new int[8];
            int[] indexF = new int[8];
            string[] indexsM = new string[8];
            string[] indexsF = new string[8];
            EntityCollection<TblExaminationInfoEntity> Exams = new EntityCollection<TblExaminationInfoEntity>();
            EntityCollection<TblPatientEntity> Patients = new EntityCollection<TblPatientEntity>();
            EntityCollection<TblPreClinicsInfoEntity> PreClinicsInfo = new EntityCollection<TblPreClinicsInfoEntity>();

            adapter.FetchEntityCollection(Exams, null);
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(PreClinicsInfo, null);
            List<string> clinicStage = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate && p.ClinicStage != 0).Select(p => p.PatientId).ToList();
            List<string> Tests = PreClinicsInfo.Where(p => p.TestDate >= BeginDate && p.TestDate <= EndDate && (p.Testid == "01" || p.Testid == "02")).Select(p => p.PatientId).ToList();
            List<string> ClinicCondition = Tests.Union(clinicStage).ToList();


            List<string> Includes = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate).GroupBy(p => p.PatientId).Select(p => p.OrderBy(g => g.ExamDate).Last()).Where(p => p.TbScreen == 1 && p.ClinicStage != 0).Where(p => ClinicCondition.Contains(p.PatientId)).Select(p => p.PatientId).ToList();
            //var g1 = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate).GroupBy(p => p.PatientId).Select(p => p.OrderBy(g => g.ExamDate).Last()).ToList();

            //Forms.frmTest frm = new Forms.frmTest();
            //frm.grdTest.DataSource = g1;
            //frm.Show();
            #region Man


            var indexMpreresult = Patients.Where(p => Includes.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 1).ToList();
            var indexMresult = indexMpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexM[0] = indexMresult.Where(p => p.Grp2 == "<1").Count();
            indexsM[0] = string.Join(",", indexMresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexM[1] = indexMresult.Where(p => p.Grp2 == "1-4").Count();
            indexsM[1] = string.Join(",", indexMresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexM[2] = indexMresult.Where(p => p.Grp2 == "5-9").Count();
            indexsM[2] = string.Join(",", indexMresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexM[3] = indexMresult.Where(p => p.Grp2 == "10-14").Count();
            indexsM[3] = string.Join(",", indexMresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexM[4] = indexMresult.Where(p => p.Grp2 == "15-19").Count();
            indexsM[4] = string.Join(",", indexMresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());

            indexM[5] = indexMresult.Where(p => p.Grp2 == "20+").Count();
            indexsM[5] = string.Join(",", indexMresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());


            #endregion
            #region Female

            var indexFpreresult = Patients.Where(p => Includes.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 2).ToList();
                 
            var indexFresult = indexFpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexF[0] = indexFresult.Where(p => p.Grp2 == "<1").Count();
            indexsF[0] = string.Join(",", indexFresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexF[1] = indexFresult.Where(p => p.Grp2 == "1-4").Count();
            indexsF[1] = string.Join(",", indexFresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexF[2] = indexFresult.Where(p => p.Grp2 == "5-9").Count();
            indexsF[2] = string.Join(",", indexFresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexF[3] = indexFresult.Where(p => p.Grp2 == "10-14").Count();
            indexsF[3] = string.Join(",", indexFresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexF[4] = indexFresult.Where(p => p.Grp2 == "15-19").Count();
            indexsF[4] = string.Join(",", indexFresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());

            indexF[5] = indexFresult.Where(p => p.Grp2 == "20+").Count();
            indexsF[5] = string.Join(",", indexFresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());


            #endregion

            #region fill
            int row = 112;
            //Nam
            _Rep.Cells[row++, 2].Value = indexM[0]; _Tracking.Cells[row - 1, 2].Value = indexsM[0];
            _Rep.Cells[row++, 2].Value = indexM[1]; _Tracking.Cells[row - 1, 2].Value = indexsM[1];
            _Rep.Cells[row++, 2].Value = indexM[2]; _Tracking.Cells[row - 1, 2].Value = indexsM[2];
            _Rep.Cells[row++, 2].Value = indexM[3]; _Tracking.Cells[row - 1, 2].Value = indexsM[3];
            _Rep.Cells[row++, 2].Value = indexM[4]; _Tracking.Cells[row - 1, 2].Value = indexsM[4];
            _Rep.Cells[row++, 2].Value = indexM[5]; _Tracking.Cells[row - 1, 2].Value = indexsM[5];

            //Nữ
            _Rep.Cells[row++, 2].Value = indexF[0]; _Tracking.Cells[row - 1, 2].Value = indexsF[0];
            _Rep.Cells[row++, 2].Value = indexF[1]; _Tracking.Cells[row - 1, 2].Value = indexsF[1];
            _Rep.Cells[row++, 2].Value = indexF[2]; _Tracking.Cells[row - 1, 2].Value = indexsF[2];
            _Rep.Cells[row++, 2].Value = indexF[3]; _Tracking.Cells[row - 1, 2].Value = indexsF[3];
            _Rep.Cells[row++, 2].Value = indexF[4]; _Tracking.Cells[row - 1, 2].Value = indexsF[4];
            _Rep.Cells[row++, 2].Value = indexF[5]; _Tracking.Cells[row - 1, 2].Value = indexsF[5];

            #endregion

        }

        private void TB_SCREENB()
        {
            int[] indexM = new int[8];
            int[] indexF = new int[8];
            string[] indexsM = new string[8];
            string[] indexsF = new string[8];

            EntityCollection<TblExaminationInfoEntity> Exams = new EntityCollection<TblExaminationInfoEntity>();
            EntityCollection<TblPatientEntity> Patients = new EntityCollection<TblPatientEntity>();
            EntityCollection<TblPreClinicsInfoEntity> PreClinicsInfo = new EntityCollection<TblPreClinicsInfoEntity>();

            adapter.FetchEntityCollection(Exams, null);
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(PreClinicsInfo, null);

            List<string> Includes = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate && p.ClinicStage != 0).Select(p => p.PatientId).ToList();
            List<string> clinicStage = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate && p.ClinicStage != 0).Select(p => p.PatientId).ToList();
            List<string> Tests = PreClinicsInfo.Where(p => p.TestDate >= BeginDate && p.TestDate <= EndDate && (p.Testid == "01" || p.Testid == "02")).Select(p => p.PatientId).ToList();

            #region Man
            var indexMpreresult = Patients.Where(p => clinicStage.Contains(p.PatientId)).Union(Patients.Where(p => Tests.Contains(p.PatientId))).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 1).GroupBy(p => p.PatientId).Select(g => g.First()).ToList();

            var indexMresult = indexMpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexM[0] = indexMresult.Where(p => p.Grp2 == "<1").Count();
            indexsM[0] = string.Join(",", indexMresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexM[1] = indexMresult.Where(p => p.Grp2 == "1-4").Count();
            indexsM[1] = string.Join(",", indexMresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexM[2] = indexMresult.Where(p => p.Grp2 == "5-9").Count();
            indexsM[2] = string.Join(",", indexMresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexM[3] = indexMresult.Where(p => p.Grp2 == "10-14").Count();
            indexsM[3] = string.Join(",", indexMresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexM[4] = indexMresult.Where(p => p.Grp2 == "15-19").Count();
            indexsM[4] = string.Join(",", indexMresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());

            indexM[5] = indexMresult.Where(p => p.Grp2 == "20+").Count();
            indexsM[5] = string.Join(",", indexMresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());


            #endregion
            #region Female

            var indexFpreresult = Patients.Where(p => clinicStage.Contains(p.PatientId)).Union(Patients.Where(p => Tests.Contains(p.PatientId))).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).Where(p => p.Sex == 2).GroupBy(p => p.PatientId).Select(g => g.First()).ToList();
         
            var indexFresult = indexFpreresult.Join(meta.TblAgeGroup, p => p.Age, q => (int)q.Age, (p, q) => new { p.PatientId, q.Grp2 }).ToList();

            indexF[0] = indexFresult.Where(p => p.Grp2 == "<1").Count();
            indexsF[0] = string.Join(",", indexFresult.Where(p => p.Grp2 == "<1").Select(p => p.PatientId).ToArray());

            indexF[1] = indexFresult.Where(p => p.Grp2 == "1-4").Count();
            indexsF[1] = string.Join(",", indexFresult.Where(p => p.Grp2 == "1-4").Select(p => p.PatientId).ToArray());

            indexF[2] = indexFresult.Where(p => p.Grp2 == "5-9").Count();
            indexsF[2] = string.Join(",", indexFresult.Where(p => p.Grp2 == "5-9").Select(p => p.PatientId).ToArray());

            indexF[3] = indexFresult.Where(p => p.Grp2 == "10-14").Count();
            indexsF[3] = string.Join(",", indexFresult.Where(p => p.Grp2 == "10-14").Select(p => p.PatientId).ToArray());

            indexF[4] = indexFresult.Where(p => p.Grp2 == "15-19").Count();
            indexsF[4] = string.Join(",", indexFresult.Where(p => p.Grp2 == "15-19").Select(p => p.PatientId).ToArray());
                      
            indexF[5] = indexFresult.Where(p => p.Grp2 == "20+").Count();
            indexsF[5] = string.Join(",", indexFresult.Where(p => p.Grp2 == "20+").Select(p => p.PatientId).ToArray());


            #endregion

            #region fill
            int row = 124;
            //Nam
            _Rep.Cells[row++, 2].Value = indexM[0]; _Tracking.Cells[row - 1, 2].Value = indexsM[0];
            _Rep.Cells[row++, 2].Value = indexM[1]; _Tracking.Cells[row - 1, 2].Value = indexsM[1];
            _Rep.Cells[row++, 2].Value = indexM[2]; _Tracking.Cells[row - 1, 2].Value = indexsM[2];
            _Rep.Cells[row++, 2].Value = indexM[3]; _Tracking.Cells[row - 1, 2].Value = indexsM[3];
            _Rep.Cells[row++, 2].Value = indexM[4]; _Tracking.Cells[row - 1, 2].Value = indexsM[4];
            _Rep.Cells[row++, 2].Value = indexM[5]; _Tracking.Cells[row - 1, 2].Value = indexsM[5];

            //Nữ
            _Rep.Cells[row++, 2].Value = indexF[0]; _Tracking.Cells[row - 1, 2].Value = indexsF[0];
            _Rep.Cells[row++, 2].Value = indexF[1]; _Tracking.Cells[row - 1, 2].Value = indexsF[1];
            _Rep.Cells[row++, 2].Value = indexF[2]; _Tracking.Cells[row - 1, 2].Value = indexsF[2];
            _Rep.Cells[row++, 2].Value = indexF[3]; _Tracking.Cells[row - 1, 2].Value = indexsF[3];
            _Rep.Cells[row++, 2].Value = indexF[4]; _Tracking.Cells[row - 1, 2].Value = indexsF[4];
            _Rep.Cells[row++, 2].Value = indexF[5]; _Tracking.Cells[row - 1, 2].Value = indexsF[5];

            #endregion
        }

        
    }
}