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 clsProgress
    {

        public DevExpress.XtraSpreadsheet.SpreadsheetControl spreed;
        public DateTime BeginDate;
        public DateTime EndDate;
        private DevExpress.Spreadsheet.Worksheet _Progress,  _Tracking;
        public DataAccessAdapter adapter = new DataAccessAdapter(SettingInfo.Constr);
        public void Calculate()
        {
            
            _Progress = this.spreed.Document.Worksheets["Progress"];
            _Tracking = this.spreed.Document.Worksheets["Tracking"];
            //V1A(); 
         //   V1B();
            V1C(); VIIPreArv(); VIIARV(); V3C();          

        }

        private void V1A()
        {



            LinqMetaData meta = new LinqMetaData(adapter);

            DateTime BeginDate = this.BeginDate;
            DateTime EndDate = this.EndDate;
            #region index1patie
            // Chỉ số 1 - 1A -  Số lượt bệnh nhân đến PKNT trong kỳ báo cáo - Liên quan đến điều trị Lao (TreatmentId	= "02"
            int[] index1 = { 0, 0 };
            string[] indexs1 = { "", "" };

            index1[0] = meta.TblExaminationInfo.Where(m => m.ExamDate >= BeginDate).Where(m => m.ExamDate <= EndDate).Where(m => m.NoSelfExam != 1).Select(g => g.PatientId).ToList().Count();
            indexs1[0] = string.Join(",", meta.TblTreatmentInfo.Where(m => m.TreatmentSdate <= EndDate).Where(m => m.TreatmentEdate == null || m.TreatmentEdate > EndDate).GroupBy(m => m.PatientId).Select(g => g.Key).ToArray());

            index1[1] = meta.TblExaminationInfo.Where(m => m.ExamDate >= BeginDate).Where(m => m.ExamDate <= EndDate).Where(m => m.NoSelfExam == 1).Select(g => g.PatientId).ToList().Count();

            //Fill

            _Progress.Cells[11, 3].Value = index1[0]; _Progress.Cells[11, 7].Value = index1[1];
            _Tracking.Cells[11, 3].Value = indexs1[0]; _Tracking.Cells[11, 7].Value = indexs1[1];

            #endregion
            #region index2
            //chỉ số 2 - 1A -  số bệnh nhân mới đăng ký trong kỳ báo cáo
            int[] index2 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs2 = { "", "", "", "", "", "" };
            var index2result = meta.TblPatient.Where(m => m.DateofRegistration >= BeginDate).Where(m => m.DateofRegistration <= EndDate).Select(m => new { PatientId = m.PatientId, Sex = m.Sex, Age = EndDate.Year - m.BirthYear }).ToList();
            // Nam < 1 tuổi
            index2[0] = index2result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs2[0] = string.Join(",", index2result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index2[1] = index2result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs2[1] = string.Join(",", index2result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15
            index2[2] = index2result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs2[2] = string.Join(",", index2result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());

            // Nữ < 1  tuổi
            index2[3] = index2result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs2[3] = string.Join(",", index2result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());

            // Nữ từ 1-14
            index2[4] = index2result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs2[4] = string.Join(",", index2result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());

            //Nữ từ 15
            index2[5] = index2result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();

            // Fill 
            _Progress.Cells[12, 3].Value = index2[0]; _Progress.Cells[12, 4].Value = index2[1]; _Progress.Cells[12, 5].Value = index2[2];
            _Progress.Cells[12, 7].Value = index2[3]; _Progress.Cells[12, 8].Value = index2[4]; _Progress.Cells[12, 9].Value = index2[5];

            _Tracking.Cells[12, 3].Value = indexs2[0]; _Tracking.Cells[12, 4].Value = indexs2[1]; _Tracking.Cells[12, 5].Value = indexs2[2];
            _Tracking.Cells[12, 7].Value = indexs2[3]; _Tracking.Cells[12, 8].Value = indexs2[4]; _Tracking.Cells[12, 9].Value = indexs2[5];
            #endregion

            #region index 3
            //chỉ số 3 - 1A - số bệnh đang quản lý trong kỳ báo cáo
            int[] index3 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs3 = { "", "", "", "", "", "" };
            var index3preresult = meta.TblExaminationInfo.Where(m => m.ExamDate <= EndDate).Join(meta.TblPatient, e => e.PatientId, p => p.PatientId, (e, p) => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear, ExamDate = e.ExamDate, EndExamDate = e.EndExamDate }).ToList();
            var index3result = index3preresult.GroupBy(m => m.PatientId).Select(g => g.OrderBy(m => m.ExamDate).Last()).Where(m => m.EndExamDate == null || m.EndExamDate > EndDate).ToList();
            //Nam < 1 tuổi
            index3[0] = index3result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs3[0] = string.Join(",", index3result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14`
            index3[1] = index3result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs3[1] = string.Join(",", index3result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index3[2] = index3result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs3[2] = string.Join(",", index3result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index3[3] = index3result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs3[3] = string.Join(",", index3result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index3[4] = index3result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs3[4] = string.Join(",", index3result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index3[5] = index3result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs3[5] = string.Join(",", index3result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Fill 
            _Progress.Cells[13, 3].Value = index3[0]; _Progress.Cells[13, 4].Value = index3[1]; _Progress.Cells[13, 5].Value = index3[2];
            _Progress.Cells[13, 7].Value = index3[3]; _Progress.Cells[13, 8].Value = index3[4]; _Progress.Cells[13, 9].Value = index3[5];

            _Tracking.Cells[13, 3].Value = indexs3[0]; _Tracking.Cells[13, 4].Value = indexs3[1]; _Tracking.Cells[13, 5].Value = indexs3[2];
            _Tracking.Cells[13, 7].Value = indexs3[3]; _Tracking.Cells[13, 8].Value = indexs3[4]; _Tracking.Cells[13, 9].Value = indexs3[5];
            #endregion

        }
        private void V1B()
        {
            int age = 15;

            LinqMetaData meta = new LinqMetaData(adapter);

            DateTime thisBeginDate = this.BeginDate;
            DateTime thisEndDate = this.EndDate;
            DateTime EndDate = this.EndDate;
            #region index1
            // Chỉ số 1 - 1B -  Số lượt bệnh nhân đến PKNT trong kỳ báo cáo - Liên quan đến điều trị Comtrimoxazole (TreatmentId	= "04"
            int[] index1 = { 0, 0, 0, 0 };
            string[] indexs1 = { "", "", "", "" };
            List<string> endTreatment = meta.TblReferralsTo.Where(m => m.EndDate <= thisBeginDate).GroupBy(m => m.PatientId).Select(g => g.Key).ToList();
            List<string> Comtri = meta.TblTreatmentInfo.Where(c => !endTreatment.Contains(c.PatientId)).Where(m => m.TreatmentId == "04").Where(m => m.TreatmentSdate <= thisEndDate).Where(m => m.TreatmentEdate == null || m.TreatmentEdate > thisEndDate).GroupBy(m => m.PatientId).Select(g => g.Key).ToList();
            var Result = meta.TblPatient.Where(c => Comtri.Contains(c.PatientId)).Select(m => new { PatientId = m.PatientId, Sex = m.Sex, Age = thisEndDate.Year - m.BirthYear }).ToList();
            //Nam < 15
            index1[0] = Result.Where(m => m.Age < age).Where(m => m.Sex == 1).Count();
            //Nam  >=15
            index1[1] = Result.Where(m => m.Age >= age).Where(m => m.Sex == 1).Count();
            //Nữ < 15 tuổi
            index1[2] = Result.Where(m => m.Age < age).Where(m => m.Sex == 2).Count();
            //Nữ >=15
            index1[3] = Result.Where(m => m.Age >= age).Where(m => m.Sex == 2).Count();
            _Progress.Cells[20, 3].Value = index1[0]; _Progress.Cells[20, 5].Value = index1[1]; _Progress.Cells[20, 7].Value = index1[2]; _Progress.Cells[20, 9].Value = index1[3];
            _Tracking.Cells[20, 3].Value = indexs1[0]; _Tracking.Cells[20, 5].Value = indexs1[1]; _Tracking.Cells[20, 7].Value = indexs1[2]; _Tracking.Cells[20, 9].Value = indexs1[3];
          
            #endregion
        }

        private void V1C()
        {
            LinqMetaData meta = new LinqMetaData(adapter);
            EntityCollection<TblTreatmentInfoEntity> TreatmentInfo = new EntityCollection<TblTreatmentInfoEntity>();
            EntityCollection <TblPatientEntity> Patients = new EntityCollection<TblPatientEntity>();
            adapter.FetchEntityCollection(TreatmentInfo, null);
            adapter.FetchEntityCollection(Patients, null);
            int age = 15;
            DateTime thisBeginDate = this.BeginDate;
            DateTime thisEndDate = this.EndDate;
            #region index1
            ////Chỉ số 1 - 1C - Số lượt bệnh nhân tự đến được sàng lọc Lao trong kỳ báo cáo	
            //int[] index1 = { 0, 0, 0, 0 };
            //var tbScreens = meta.TblExaminationInfo.Where(p => p.TbScreen == 1).Where(p => p.ExamDate >= thisBeginDate && p.ExamDate <= thisEndDate).ToList();
            //var tbPositive = tbScreens.Where(p => p.TbScreenResult == "02").ToList();
            //var tbNegative = tbScreens.Where(p => p.TbScreenResult != "02").ToList();
            //List<string> tbTreat = meta.TblTreatmentInfo.Where(p => p.TreatmentId == "02").Where(p => p.TreatmentSdate >= thisBeginDate && p.TreatmentSdate <= thisEndDate).GroupBy(p => p.PatientId).Select(g => g.Key).ToList();

            //index1[0] = tbPositive.Where(p => tbTreat.Contains(p.PatientId)).ToList().Count();
            //index1[1] = tbNegative.Where(p => tbTreat.Contains(p.PatientId)).ToList().Count();
            //index1[2] = tbPositive.Count - index1[0];
            //index1[3] = tbNegative.Count - index1[1];
            ////Fill 

            //_Progress.Cells[25, 3].Value = index1[0]; _Progress.Cells[25, 5].Value = index1[1];
            //_Progress.Cells[25, 7].Value = index1[2]; _Progress.Cells[25, 9].Value = index1[3];
            #endregion
            #region index3
            //Chỉ số 3 - Số bệnh nhân bắt đầu điều trị Lao trong kỳ báo cáo	
            //Lọc tất cả các bản ghi trong bảng tblTreatmentInfo có kiểu điều trị là Lao (02) và có ngày điều trị trong khoảng báo cáo, nhóm lại lấy bản ghi duy nhất
            int[] index3 = { 0, 0 };
            string[] indexs3 =  {"", ""};

            var index3prevresult = (from tr in TreatmentInfo where (tr.TreatmentId == "02" && tr.TreatmentSdate >= thisBeginDate && tr.TreatmentSdate <= thisEndDate) group tr by tr.PatientId into g select g.First()).ToList();
            var index3result = index3prevresult.Join(Patients,
                                   t => t.PatientId,
                                   p => p.PatientId,
                                   (t, p)
                                   => new { PatientId = p.PatientId, Sex = p.Sex, Age = thisEndDate.Year - p.BirthYear }).ToList();

            //Nam 
            index3[0] = index3result.Where(m => m.Sex == 1).Count();
            indexs3[0] = string.Join(",",index3result.Where(m => m.Sex == 1).Select(p=>p.PatientId).ToArray());
            //Nữ 
            index3[1] = index3result.Where(m => m.Sex == 2).Count();
            indexs3[1] = string.Join(",", index3result.Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
             _Progress.Cells[16, 3].Value = index3[0]; _Progress.Cells[16, 7].Value = index3[1];
             _Tracking.Cells[16, 3].Value = indexs3[0]; _Tracking.Cells[16, 7].Value = indexs3[1];   
            #endregion
            #region index4
            //Chỉ số 4 - Số bệnh nhân bắt đầu điều trị dự phòng mắc Lao bằng INH (IPT) trong kì báo cáo	

            //int[] index4 = { 0, 0, 0, 0 };
            //string[] indexs4 = { "", "", "", "" };
            //var index4prevresult = (from tr in TreatmentInfo where tr.TreatmentId == "03" group tr by tr.PatientId into g select g.OrderBy(p => p.TreatmentEdate).First()).Where(p => p.TreatmentEdate >= thisBeginDate && p.TreatmentEdate <= thisEndDate).ToList();
            //var index4result = index4prevresult.Join(meta.TblPatient,
            //                       t => t.PatientId,
            //                       p => p.PatientId,
            //                       (t, p)
            //                       => new { PatientId = p.PatientId, Sex = p.Sex, Age = thisEndDate.Year - p.BirthYear }).ToList();

            ////Nam < 15
            //index4[0] = index4result.Where(m => m.Age < age).Where(m => m.Sex == 1).Count();
            ////Nam  >=15
            //index4[1] = index4result.Where(m => m.Age >= age).Where(m => m.Sex == 1).Count();
            ////Nữ < 15 tuổi
            //index4[2] = index4result.Where(m => m.Age < age).Where(m => m.Sex == 2).Count();
            ////Nữ >=15
            //index4[3] = index4result.Where(m => m.Age >= age).Where(m => m.Sex == 2).Count();
            ////fill
            //_Progress.Cells[31, 3].Value = index4[0]; _Progress.Cells[31, 5].Value = index4[1]; _Progress.Cells[31, 7].Value = index4[2]; _Progress.Cells[31, 9].Value = index4[3];
            //_Tracking.Cells[31, 3].Value = indexs4[0]; _Tracking.Cells[31, 5].Value = indexs4[1]; _Tracking.Cells[31, 7].Value = indexs4[2]; _Tracking.Cells[31, 9].Value = indexs4[3];
            #endregion

        }
        private void VIIPreArv()
        {
            //Dịch vụ trước điều trị ARV
            EntityCollection<TblExaminationInfoEntity> exams = new EntityCollection<TblExaminationInfoEntity>();
            EntityCollection<TblPatientEntity> Patients = new EntityCollection<TblPatientEntity>();
            EntityCollection<TblReferralsToEntity> ReferralTo = new EntityCollection<TblReferralsToEntity>();
            adapter.FetchEntityCollection(exams, null);
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(ReferralTo, null);

            int row = 0;
            #region index1
            // Đang quản lý
            int[] index1 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs1 = { "", "", "", "", "", "" };
            List<string> ends = exams.Where(p => p.ExamDate <= EndDate).GroupBy(q => q.PatientId).Select(g => g.OrderByDescending(m => m.ExamDate).First()).ToList().Where(s => s.EndExamDate == null || s.EndExamDate > EndDate).Select(t => t.PatientId).ToList();
            var index1Result = Patients.Where(m => m.DateofArv >= EndDate || m.DateofArv == null).Where(p => ends.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).ToList();
        

            //Nam < 1 tuổi
            index1[0] = index1Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs1[0] = string.Join(",", index1Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index1[1] = index1Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs1[1] = string.Join(",", index1Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index1[2] = index1Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
          
            indexs1[2] = string.Join(",", index1Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index1[3] = index1Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs1[3] = string.Join(",", index1Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index1[4] = index1Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs1[4] = string.Join(",", index1Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index1[5] = index1Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs1[5] = string.Join(",", index1Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());

            
            //fill
            row = 21;
            _Progress.Cells[row, 3].Value = index1[0]; _Progress.Cells[row, 4].Value = index1[1]; _Progress.Cells[row, 5].Value = index1[2];
            _Progress.Cells[row, 7].Value = index1[3]; _Progress.Cells[row, 8].Value = index1[4]; _Progress.Cells[row, 9].Value = index1[5];
          
            _Tracking.Cells[row, 3].Value = indexs1[0]; _Tracking.Cells[row, 4].Value = indexs1[1]; _Tracking.Cells[row, 5].Value = indexs1[2];
            _Tracking.Cells[row, 7].Value = indexs1[3]; _Tracking.Cells[row, 8].Value = indexs1[4]; _Tracking.Cells[row, 9].Value = indexs1[5];

            #endregion

            #region index2
            // Mới đăng ký trong kỳ báo cáo
            int[] index2 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs2 = { "", "", "", "", "", "" };
            var index2Result = Patients.Where(m => m.DateofRegistration >= BeginDate && m.DateofRegistration <= EndDate && m.Dateofreferral ==null).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).ToList();
            //Nam < 1 tuổi
            index2[0] = index2Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs2[0] = string.Join(",", index2Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index2[1] = index2Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs2[1] = string.Join(",", index2Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index2[2] = index2Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs2[2] = string.Join(",", index2Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index2[3] = index2Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs2[3] = string.Join(",", index2Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index2[4] = index2Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs2[4] = string.Join(",", index2Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index2[5] = index2Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs2[5] = string.Join(",", index2Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 22;
            _Progress.Cells[row, 3].Value = index2[0]; _Progress.Cells[row, 4].Value = index2[1]; _Progress.Cells[row, 5].Value = index2[2];
            _Progress.Cells[row, 7].Value = index2[3]; _Progress.Cells[row, 8].Value = index2[4]; _Progress.Cells[row, 9].Value = index2[5];

            _Tracking.Cells[row, 3].Value = indexs2[0]; _Tracking.Cells[row, 4].Value = indexs2[1]; _Tracking.Cells[row, 5].Value = indexs2[2];
            _Tracking.Cells[row, 7].Value = indexs2[3]; _Tracking.Cells[row, 8].Value = indexs2[4]; _Tracking.Cells[row, 9].Value = indexs2[5];

   #endregion

            #region index3
            //Điều trị lại trong kỳ báo cáo
            int[] index3 = { 0, 0, 0, 0, 0, 4 };
            string[] indexs3 = { "", "", "", "", "", "" };
            var index3Preresult = exams.Where(m => m.ExamDate >= BeginDate && m.ExamDate <= EndDate && m.ReTreatment == 1).ToList();
            var index3result = index3Preresult.Join(Patients, e => e.PatientId, p => p.PatientId, (e, p) => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear, ExamDate = e.ExamDate, DateofArv = p.DateofArv }).Where(m => m.DateofArv == null || m.ExamDate <= m.DateofArv).ToList();
            //Nam < 1 tuổi
            index3[0] = index3result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs3[0] = string.Join(",", index3result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index3[1] = index3result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs3[1] = string.Join(",", index3result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index3[2] = index3result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs3[2] = string.Join(",", index3result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index3[3] = index3result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs3[3] = string.Join(",", index3result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index3[4] = index3result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs3[4] = string.Join(",", index3result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index3[5] = index3result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs3[5] = string.Join(",", index3result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 23;
            _Progress.Cells[row, 3].Value = index3[0]; _Progress.Cells[row, 4].Value = index3[1]; _Progress.Cells[row, 5].Value = index3[2];
            _Progress.Cells[row, 7].Value = index3[3]; _Progress.Cells[row, 8].Value = index3[4]; _Progress.Cells[row, 9].Value = index3[5];

            _Tracking.Cells[row, 3].Value = indexs3[0]; _Tracking.Cells[row, 4].Value = indexs3[1]; _Tracking.Cells[row, 5].Value = indexs3[2];
            _Tracking.Cells[row, 7].Value = indexs3[3]; _Tracking.Cells[row, 8].Value = indexs3[4]; _Tracking.Cells[row, 9].Value = indexs3[5];
            #endregion

            #region index4
            //Chuyển đến trong kỳ báo cáo
            int[] index4 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs4 = { "", "", "", "", "", "" };
            var Returns = exams.Where(p=>p.ExamDate >=BeginDate && p.ExamDate <= EndDate && p.ReTreatment==2).Join (Patients , p=>p.PatientId, q=>q.PatientId, (p,q)=>new {p.PatientId, p.ExamDate, q.DateofArv, q.Sex, q.BirthYear}).Where (p=>p.DateofArv==null || p.DateofArv > p.ExamDate).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).ToList();
            var index4Result = Patients.Where(p => p.Dateofreferral != null && p.DateofRegistration >= BeginDate && p.DateofRegistration <= EndDate && p.HaveArvBefore != 1).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).ToList();
            index4Result.AddRange(Returns);
            //Nam < 1 tuổi
            index4[0] = index4Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs4[0] = string.Join(",", index4Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(q => q.PatientId).ToArray());
            // Nam từ 1-14
            index4[1] = index4Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs4[1] = string.Join(",", index4Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(q => q.PatientId).ToArray());
            // Nam từ 15 tuổi
            index4[2] = index4Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs4[2] = string.Join(",", index4Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(q => q.PatientId).ToArray());
            //Nữ < 1 tuổi
            index4[3] = index4Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs4[3] = string.Join(",", index4Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(q => q.PatientId).ToArray());
            //Nữ từ 1-14
            index4[4] = index4Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs4[4] = string.Join(",", index4Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(q => q.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index4[5] = index4Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs4[5] = string.Join(",", index4Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(q => q.PatientId).ToArray());

            //fill
            row = 24;
            _Progress.Cells[row, 3].Value = index4[0]; _Progress.Cells[row, 4].Value = index4[1]; _Progress.Cells[row, 5].Value = index4[2];
            _Progress.Cells[row, 7].Value = index4[3]; _Progress.Cells[row, 8].Value = index4[4]; _Progress.Cells[row, 9].Value = index4[5];

            _Tracking.Cells[row, 3].Value = indexs4[0]; _Tracking.Cells[row, 4].Value = indexs4[1]; _Tracking.Cells[row, 5].Value = indexs4[2];
            _Tracking.Cells[row, 7].Value = indexs4[3]; _Tracking.Cells[row, 8].Value = indexs4[4]; _Tracking.Cells[row, 9].Value = indexs4[5];

            #endregion

            #region index5

            //Chuyển đi trong kỳ báo cáo
            int[] index5 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs5 = { "", "", "", "", "", "" };
            var referrals = ReferralTo.Where(p => p.EndDate >= BeginDate && p.EndDate <= EndDate && p.TypeId == "09").ToList();
            var index5Result = referrals.Join(Patients, e => e.PatientId, p => p.PatientId, (e, p) => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear, DateofArv = p.DateofArv, EndDate = e.EndDate }).Where(m => m.DateofArv == null || m.EndDate < m.DateofArv).ToList();

            //Nam < 1 tuổi
            index5[0] = index5Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs5[0] = string.Join(",", index5Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index5[1] = index5Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs5[1] = string.Join(",", index5Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index5[2] = index5Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs5[2] = string.Join(",", index5Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index5[3] = index5Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs5[3] = string.Join(",", index5Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index5[4] = index5Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs5[4] = string.Join(",", index5Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index5[5] = index5Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs5[5] = string.Join(",", index5Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 25;
            _Progress.Cells[row, 3].Value = index5[0]; _Progress.Cells[row, 4].Value = index5[1]; _Progress.Cells[row, 5].Value = index5[2];
            _Progress.Cells[row, 7].Value = index5[3]; _Progress.Cells[row, 8].Value = index5[4]; _Progress.Cells[row, 9].Value = index5[5];

            _Tracking.Cells[row, 3].Value = indexs5[0]; _Tracking.Cells[row, 4].Value = indexs5[1]; _Tracking.Cells[row, 5].Value = indexs5[2];
            _Tracking.Cells[row, 7].Value = indexs5[3]; _Tracking.Cells[row, 8].Value = indexs5[4]; _Tracking.Cells[row, 9].Value = indexs5[5];

            #endregion

            #region index6
            int[] index6 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs6 = { "", "", "", "", "", "" };
            var lossfollows = ReferralTo.Where(p => p.EndDate >= BeginDate && p.EndDate <= EndDate && p.TypeId == "10").ToList();
            var index6Result = lossfollows.Join(Patients, e => e.PatientId, p => p.PatientId, (e, p) => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear, DateofArv = p.DateofArv, EndDate = e.EndDate }).Where(m => m.DateofArv == null || m.EndDate < m.DateofArv).ToList();

            //Nam < 1 tuổi
            index6[0] = index6Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs6[0] = string.Join(",", index6Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index6[1] = index6Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs6[1] = string.Join(",", index6Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index6[2] = index6Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs6[2] = string.Join(",", index6Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index6[3] = index6Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs6[3] = string.Join(",", index6Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index6[4] = index6Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs6[4] = string.Join(",", index6Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index6[5] = index6Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs6[5] = string.Join(",", index6Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 26;
            _Progress.Cells[row, 3].Value = index6[0]; _Progress.Cells[row, 4].Value = index6[1]; _Progress.Cells[row, 5].Value = index6[2];
            _Progress.Cells[row, 7].Value = index6[3]; _Progress.Cells[row, 8].Value = index6[4]; _Progress.Cells[row, 9].Value = index6[5];

            _Tracking.Cells[row, 3].Value = indexs6[0]; _Tracking.Cells[row, 4].Value = indexs6[1]; _Tracking.Cells[row, 5].Value = indexs6[2];
            _Tracking.Cells[row, 7].Value = indexs6[3]; _Tracking.Cells[row, 8].Value = indexs6[4]; _Tracking.Cells[row, 9].Value = indexs6[5];
            #endregion

            #region index7

            //Tử vong
            int[] index7 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs7 = { "", "", "", "", "", "" };
            var deaths = ReferralTo.Where(p => p.EndDate >= BeginDate && p.EndDate <= EndDate && p.TypeId == "11").ToList();
            var index7Result = deaths.Join(Patients, e => e.PatientId, p => p.PatientId, (e, p) => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear, DateofArv = p.DateofArv, EndDate = e.EndDate }).Where(m => m.DateofArv == null || m.EndDate < m.DateofArv).ToList();

            //Nam < 1 tuổi
            index7[0] = index7Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs7[0] = string.Join(",", index7Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(q => q.PatientId).ToArray());
            // Nam từ 1-14
            index7[1] = index7Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs7[1] = string.Join(",", index7Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(q => q.PatientId).ToArray());
            // Nam từ 15 tuổi
            index7[2] = index7Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs7[2] = string.Join(",", index7Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(q => q.PatientId).ToArray());
            //Nữ < 1 tuổi
            index7[3] = index7Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs7[3] = string.Join(",", index7Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(q => q.PatientId).ToArray());
            //Nữ từ 1-14
            index7[4] = index7Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs7[4] = string.Join(",", index7Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(q => q.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index7[5] = index7Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs7[5] = string.Join(",", index7Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(q => q.PatientId).ToArray());
            //fill
            row = 27;
            _Progress.Cells[row, 3].Value = index7[0]; _Progress.Cells[row, 4].Value = index7[1]; _Progress.Cells[row, 5].Value = index7[2];
            _Progress.Cells[row, 7].Value = index7[3]; _Progress.Cells[row, 8].Value = index7[4]; _Progress.Cells[row, 9].Value = index7[5];

            _Tracking.Cells[row, 3].Value = indexs7[0]; _Tracking.Cells[row, 4].Value = indexs7[1]; _Tracking.Cells[row, 5].Value = indexs7[2];
            _Tracking.Cells[row, 7].Value = indexs7[3]; _Tracking.Cells[row, 8].Value = indexs7[4]; _Tracking.Cells[row, 9].Value = indexs7[5];
         
            #endregion


        }
        private void VIIARV()
        {
            //Dịch vụ sau điều trị ARV

            LinqMetaData meta = new LinqMetaData(adapter);
                    
     
            EntityCollection<TblPatientEntity> Patients = new EntityCollection<TblPatientEntity>();
            EntityCollection<TblTreatmentInfoEntity> TreatmentInfo = new EntityCollection<TblTreatmentInfoEntity>();
            EntityCollection<TblExaminationInfoEntity> Exams = new EntityCollection<TblExaminationInfoEntity>();
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(TreatmentInfo, null);
            adapter.FetchEntityCollection(Exams, null);
            #region index1
            // Đang quản lý
            int[] index1 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs1 = { "", "", "", "", "", "" };
            int row = 0;
            //Lưu ý những cá nhân được đánh dấu là kết thúc điều trị nhưng lại quay lại
            List<string> LastArv = TreatmentInfo.Where(p => p.TreatmentSdate <= EndDate && p.TreatmentId == "01").GroupBy(p => p.PatientId).Select(p => p.OrderBy(q => q.TreatmentSdate).Last()).ToList().Where(p => p.TreatmentEdate == null || p.TreatmentEdate > EndDate).Select(p => p.PatientId).ToList();
            var index1Result = Patients.Where(p => LastArv.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).ToList();
            //Nam < 1 tuổi
            index1[0] = index1Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs1[0] = string.Join(",", index1Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index1[1] = index1Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs1[1] = string.Join(",", index1Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index1[2] = index1Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs1[2] = string.Join(",", index1Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());

            //Nữ < 1 tuổi
            index1[3] = index1Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs1[3] = string.Join(",", index1Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index1[4] = index1Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs1[4] = string.Join(",", index1Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index1[5] = index1Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs1[5] = string.Join(",", index1Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 32;
            _Progress.Cells[row, 3].Value = index1[0]; _Progress.Cells[row, 4].Value = index1[1]; _Progress.Cells[row, 5].Value = index1[2];
            _Progress.Cells[row, 7].Value = index1[3]; _Progress.Cells[row, 8].Value = index1[4]; _Progress.Cells[row, 9].Value = index1[5];

            _Tracking.Cells[row, 3].Value = indexs1[0]; _Tracking.Cells[row, 4].Value = indexs1[1]; _Tracking.Cells[row, 5].Value = indexs1[2];
            _Tracking.Cells[row, 7].Value = indexs1[3]; _Tracking.Cells[row, 8].Value = indexs1[4]; _Tracking.Cells[row, 9].Value = indexs1[5];

            #endregion

            #region index2
            //Bắt đầu điều trị Arv trong kỳ báo cáo

            int[] index2 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs2 = { "", "", "", "", "", "" };
            var index2Result = Patients.Where(m => m.DateofArv != null && m.DateofArv >= BeginDate && m.DateofArv <= EndDate && m.HaveArvBefore != 1).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).ToList();
            //Nam < 1 tuổi
            index2[0] = index2Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs2[0] = string.Join(",", index2Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index2[1] = index2Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs2[1] = string.Join(",", index2Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index2[2] = index2Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs2[2] = string.Join(",", index2Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index2[3] = index2Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs2[3] = string.Join(",", index2Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index2[4] = index2Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs2[4] = string.Join(",", index2Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index2[5] = index2Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs2[5] = string.Join(",", index2Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 33;
            _Progress.Cells[row, 3].Value = index2[0]; _Progress.Cells[row, 4].Value = index2[1]; _Progress.Cells[row, 5].Value = index2[2];
            _Progress.Cells[row, 7].Value = index2[3]; _Progress.Cells[row, 8].Value = index2[4]; _Progress.Cells[row, 9].Value = index2[5];

            _Tracking.Cells[row, 3].Value = indexs2[0]; _Tracking.Cells[row, 4].Value = indexs2[1]; _Tracking.Cells[row, 5].Value = indexs2[2];
            _Tracking.Cells[row, 7].Value = indexs2[3]; _Tracking.Cells[row, 8].Value = indexs2[4]; _Tracking.Cells[row, 9].Value = indexs2[5];

            #endregion

            #region index3
            //Điều trị lại trong kỳ báo cáo
            int[] index3 = { 0, 0, 0, 0, 0, 4 };
            string[] indexs3 = { "", "", "", "", "", "" };
            var index3Preresult = Exams.Where(m => m.ExamDate >= BeginDate && m.ExamDate <= EndDate && m.ReTreatment == 1).ToList();
            var index3Result = index3Preresult.Join(Patients, e => e.PatientId, p => p.PatientId, (e, p) => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear, ExamDate = e.ExamDate, DateofArv = p.DateofArv }).Where(m => m.DateofArv != null && m.ExamDate > m.DateofArv).ToList();
            //Nam < 1 tuổi
            index3[0] = index3Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs3[0] = string.Join(",", index3Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index3[1] = index3Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs3[1] = string.Join(",", index3Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index3[2] = index3Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs3[2] = string.Join(",", index3Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index3[3] = index3Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs3[3] = string.Join(",", index3Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index3[4] = index3Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs3[4] = string.Join(",", index3Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index3[5] = index3Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs3[5] = string.Join(",", index3Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 34;
            _Progress.Cells[row, 3].Value = index3[0]; _Progress.Cells[row, 4].Value = index3[1]; _Progress.Cells[row, 5].Value = index3[2];
            _Progress.Cells[row, 7].Value = index3[3]; _Progress.Cells[row, 8].Value = index3[4]; _Progress.Cells[row, 9].Value = index3[5];

            _Tracking.Cells[row, 3].Value = indexs3[0]; _Tracking.Cells[row, 4].Value = indexs3[1]; _Tracking.Cells[row, 5].Value = indexs3[2];
            _Tracking.Cells[row, 7].Value = indexs3[3]; _Tracking.Cells[row, 8].Value = indexs3[4]; _Tracking.Cells[row, 9].Value = indexs3[5];

            #endregion

            #region index4
            //Chuyển đến trong kỳ báo cáo
            int[] index4 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs4 = { "", "", "", "", "", "" };
            var Returns = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate && p.ReTreatment == 2).Join(Patients, p => p.PatientId, q => q.PatientId, (p, q) => new { p.PatientId, p.ExamDate, q.DateofArv, q.Sex, q.BirthYear }).Where(p => p.DateofArv!= null && p.DateofArv < p.ExamDate).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).ToList();
            var index4Result = Patients.Where(p=>p.DateofRegistration >= BeginDate && p.DateofRegistration <= EndDate &&  p.HaveArvBefore == 1).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).ToList();
            index4Result.AddRange(Returns);
            //Nam < 1 tuổi
            index4[0] = index4Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs4[0] = string.Join(",", index4Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index4[1] = index4Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs4[1] = string.Join(",", index4Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index4[2] = index4Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs4[2] = string.Join(",", index4Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index4[3] = index4Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs4[3] = string.Join(",", index4Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index4[4] = index4Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs4[4] = string.Join(",", index4Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index4[5] = index4Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs4[5] = string.Join(",", index4Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 35;
            _Progress.Cells[row, 3].Value = index4[0]; _Progress.Cells[row, 4].Value = index4[1]; _Progress.Cells[row, 5].Value = index4[2];
            _Progress.Cells[row, 7].Value = index4[3]; _Progress.Cells[row, 8].Value = index4[4]; _Progress.Cells[row, 9].Value = index4[5];

            _Tracking.Cells[row, 3].Value = indexs4[0]; _Tracking.Cells[row, 4].Value = indexs4[1]; _Tracking.Cells[row, 5].Value = indexs4[2];
            _Tracking.Cells[row, 7].Value = indexs4[3]; _Tracking.Cells[row, 8].Value = indexs4[4]; _Tracking.Cells[row, 9].Value = indexs4[5];
            #endregion

            #region index5
            //Chuyển đi trong kỳ báo cáo
            int[] index5 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs5 = { "", "", "", "", "", "" };
            var referrals = TreatmentInfo.Where(p => p.TreatmentEdate != null && p.TreatmentEdate >= BeginDate && p.TreatmentEdate <= EndDate && p.ReasonId == "09" && p.TreatmentId == "01").ToList();
            var index5Result = referrals.Join(Patients, e => e.PatientId, p => p.PatientId, (e, p) => new { PatientId = p.PatientId, Sex = p.Sex, Age = e.TreatmentEdate.Value.Year - p.BirthYear }).ToList();

            //Nam < 1 tuổi
            index5[0] = index5Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs5[0] = string.Join(",", index5Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index5[1] = index5Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs5[1] = string.Join(",", index5Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index5[2] = index5Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs5[2] = string.Join(",", index5Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index5[3] = index5Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs5[3] = string.Join(",", index5Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index5[4] = index5Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs5[4] = string.Join(",", index5Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index5[5] = index5Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs5[5] = string.Join(",", index5Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 36;
            _Progress.Cells[row, 3].Value = index5[0]; _Progress.Cells[row, 4].Value = index5[1]; _Progress.Cells[row, 5].Value = index5[2];
            _Progress.Cells[row, 7].Value = index5[3]; _Progress.Cells[row, 8].Value = index5[4]; _Progress.Cells[row, 9].Value = index5[5];

            _Tracking.Cells[row, 3].Value = indexs5[0]; _Tracking.Cells[row, 4].Value = indexs5[1]; _Tracking.Cells[row, 5].Value = indexs5[2];
            _Tracking.Cells[row, 7].Value = indexs5[3]; _Tracking.Cells[row, 8].Value = indexs5[4]; _Tracking.Cells[row, 9].Value = indexs5[5];
            #endregion

            #region index6
            //Tử vong trong kỳ báo cáo
            
            int[] index6 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs6 = { "", "", "", "", "", "" };
            var deaths = TreatmentInfo.Where(p => p.TreatmentEdate != null && p.TreatmentEdate >= BeginDate && p.TreatmentEdate <= EndDate && p.ReasonId == "11" && p.TreatmentId == "01").ToList();
            var index6Result = deaths.Join(Patients, e => e.PatientId, p => p.PatientId, (e, p) => new { PatientId = p.PatientId, Sex = p.Sex, Age = e.TreatmentEdate.Value.Year - p.BirthYear }).ToList();

            //Nam < 1 tuổi
            index6[0] = index6Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs6[0] = string.Join(",", index6Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index6[1] = index6Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs6[1] = string.Join(",", index6Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index6[2] = index6Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs6[2] = string.Join(",", index6Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index6[3] = index6Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs6[3] = string.Join(",", index6Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index6[4] = index6Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs6[4] = string.Join(",", index6Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index6[5] = index6Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs6[5] = string.Join(",", index6Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
             row = 37;
             _Progress.Cells[row, 3].Value = index6[0]; _Progress.Cells[row, 4].Value = index6[1]; _Progress.Cells[row, 5].Value = index6[2];
             _Progress.Cells[row, 7].Value = index6[3]; _Progress.Cells[row, 8].Value = index6[4]; _Progress.Cells[row, 9].Value = index6[5];

             _Tracking.Cells[row, 3].Value = indexs6[0]; _Tracking.Cells[row, 4].Value = indexs6[1]; _Tracking.Cells[row, 5].Value = indexs6[2];
             _Tracking.Cells[row, 7].Value = indexs6[3]; _Tracking.Cells[row, 8].Value = indexs6[4]; _Tracking.Cells[row, 9].Value = indexs6[5];

            #endregion

            #region index7
            //Bỏ trị trong kỳ báo cáo
            int[] index7 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs7 = { "", "", "", "", "", "" };
            var lossfollows = TreatmentInfo.Where(p => p.TreatmentEdate != null && p.TreatmentEdate >= BeginDate && p.TreatmentEdate <= EndDate && p.ReasonId == "10" && p.TreatmentId == "01").ToList();
            var index7Result = lossfollows.Join(Patients, e => e.PatientId, p => p.PatientId, (e, p) => new { PatientId = p.PatientId, Sex = p.Sex, Age = e.TreatmentEdate.Value.Year - p.BirthYear}).ToList();

            //Nam < 1 tuổi
            index7[0] = index7Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs7[0] = string.Join(",", index7Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index7[1] = index7Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs7[1] = string.Join(",", index7Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index7[2] = index7Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs7[2] = string.Join(",", index7Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index7[3] = index7Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs7[3] = string.Join(",", index7Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index7[4] = index7Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs7[4] = string.Join(",", index7Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index7[5] = index7Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs7[5] = string.Join(",", index7Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 38;
            _Progress.Cells[row, 3].Value = index7[0]; _Progress.Cells[row, 4].Value = index7[1]; _Progress.Cells[row, 5].Value = index7[2];
            _Progress.Cells[row, 7].Value = index7[3]; _Progress.Cells[row, 8].Value = index7[4]; _Progress.Cells[row, 9].Value = index7[5];

            _Tracking.Cells[row, 3].Value = indexs7[0]; _Tracking.Cells[row, 4].Value = indexs7[1]; _Tracking.Cells[row, 5].Value = indexs7[2];
            _Tracking.Cells[row, 7].Value = indexs7[3]; _Tracking.Cells[row, 8].Value = indexs7[4]; _Tracking.Cells[row, 9].Value = indexs7[5];

            #endregion

            #region index8

            //Không khám trong kỳ báo cáo - lưu ý đến ngày hẹn tái khám
            int[] index8 = { 0, 0, 0, 0, 0, 0 };
            string[] indexs8 = { "", "", "", "", "", "" };
            var ReExams = Exams.Where(p => p.ReExamDate >= BeginDate && p.ReExamDate <= EndDate && (p.EndExamDate==null || p.EndExamDate > p.ReExamDate)).GroupBy(m => m.PatientId).Select(g => new { PatientId = g.Key, c1 = g.Count() }).ToList();
            var exams = Exams.Where(p => p.ExamDate.Value.AddDays(p.DayDiff<0?Math.Abs(p.DayDiff.Value):0) >= BeginDate && p.ExamDate.Value.AddDays(p.DayDiff<0?Math.Abs(p.DayDiff.Value):0) <= EndDate).GroupBy(m => m.PatientId).Select(g => new { PatientId = g.Key, c2 = g.Count() }).ToList();

            //var exams = Exams.Where(p => p.ExamDate >= BeginDate && p.ExamDate <= EndDate).GroupBy(m => m.PatientId).Select(g => new { PatientId = g.Key, c2 = g.Count() }).ToList();
            var skips1 = (from p in ReExams join q in exams on p.PatientId equals q.PatientId into g from q in g.DefaultIfEmpty() select new { PatientId = p.PatientId, p.c1, c2 = (q == null) ? 0 : q.c2 }).ToList().Where(m => m.c1 > m.c2).Select(m => new { PatientId =m.PatientId,c1= m.c1, c2=m.c2 }).ToList();

            //PatientMan.Forms.frmTest frm = new PatientMan.Forms.frmTest();
            //frm.grdTest.DataSource = skips1;
            //frm.Show();

              List<string> skips = skips1.Select(m => m.PatientId).ToList();
            var index8Result = Patients.Where(p=>p.DateofArv != null && p.DateofArv < BeginDate).Where(p => skips.Contains(p.PatientId)).Select(p => new { PatientId = p.PatientId, Sex = p.Sex, Age = EndDate.Year - p.BirthYear }).ToList();

            //Nam < 1 tuổi
            index8[0] = index8Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Count();
            indexs8[0] = string.Join(",", index8Result.Where(m => m.Age < 1).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 1-14
            index8[1] = index8Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Count();
            indexs8[1] = string.Join(",", index8Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            // Nam từ 15 tuổi
            index8[2] = index8Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Count();
            indexs8[2] = string.Join(",", index8Result.Where(m => m.Age >= 15).Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ < 1 tuổi
            index8[3] = index8Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Count();
            indexs8[3] = string.Join(",", index8Result.Where(m => m.Age < 1).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 1-14
            index8[4] = index8Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Count();
            indexs8[4] = string.Join(",", index8Result.Where(m => m.Age >= 1 && m.Age < 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //Nữ từ 15 tuổi
            index8[5] = index8Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Count();
            indexs8[5] = string.Join(",", index8Result.Where(m => m.Age >= 15).Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 39;
            //_Progress.Cells[row, 3].Value = index8[0]; _Progress.Cells[row, 4].Value = index8[1]; _Progress.Cells[row, 5].Value = index8[2];
            //_Progress.Cells[row, 7].Value = index8[3]; _Progress.Cells[row, 8].Value = index8[4]; _Progress.Cells[row, 9].Value = index8[5];

            //_Tracking.Cells[row, 3].Value = indexs8[0]; _Tracking.Cells[row, 4].Value = indexs8[1]; _Tracking.Cells[row, 5].Value = indexs8[2];
            //_Tracking.Cells[row, 7].Value = indexs8[3]; _Tracking.Cells[row, 8].Value = indexs8[4]; _Tracking.Cells[row, 9].Value = indexs8[5];
            #endregion

        }
        private void V3C()
        {
            LinqMetaData meta = new LinqMetaData(adapter);
            int row = 0;
            DateTime thisBeginDate = this.BeginDate;
            DateTime thisEndDate = this.EndDate;
            EntityCollection<TblPatientEntity> Patients = new EntityCollection<TblPatientEntity>();
            EntityCollection<TblTreatmentInfoEntity> TreatmentInfo = new EntityCollection<TblTreatmentInfoEntity>();
            adapter.FetchEntityCollection(Patients, null);
            adapter.FetchEntityCollection(TreatmentInfo, null);           
            var Arvs = TreatmentInfo.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();
     

            #region "Index1"
            int[] index1 = new int[2];
            string[] index1s = new string[2];
            var index1preresult = 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();
          
            //Nam 
            index1[0] = index1preresult.Where(m => m.Sex == 1).Count();
            index1s[0] = string.Join(",", index1preresult.Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ 
            index1[1] = index1preresult.Where(m => m.Sex == 2).Count();
            index1s[1] = string.Join(",", index1preresult.Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 52;
            _Progress.Cells[row, 3].Value = index1[0]; _Progress.Cells[row, 7].Value = index1[1];
            _Tracking.Cells[row, 3].Value = index1s[0]; _Tracking.Cells[row, 7].Value = index1s[1];  

            #endregion

            #region "Index2"
            int[] index2 = new int[2];
            string[] index2s = new string[2];
            var index2preresult = 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).ToList();

            //Nam 
            index2[0] = index2preresult.Where(m => m.Sex == 1).Count();
            index2s[0] = string.Join(",", index2preresult.Where(m => m.Sex == 1).Select(p => p.PatientId).ToArray());
            //Nữ 
            index2[1] = index2preresult.Where(m => m.Sex == 2).Count();
            index2s[1] = string.Join(",", index2preresult.Where(m => m.Sex == 2).Select(p => p.PatientId).ToArray());
            //fill
            row = 53;
            _Progress.Cells[row, 3].Value = index2[0]; _Progress.Cells[row, 7].Value = index2[1];
            _Tracking.Cells[row, 3].Value = index2s[0]; _Tracking.Cells[row, 7].Value = index2s[1];

            #endregion
        
        }

    }
}