public static IEnumerable<SubscriptionFileViewModel> GetInformationSubscriptionFilesCgp(Guid idUser, string letter = "tous", string search = "")
        {
            using (var ctx = new UpsilabEntities())
            {
                letter = letter.ToLower();
                search = search.ToLower();

                var SFCrit = Upsilab.Business.Document.ElectronicSafeDocumentBL.DocumentType.SF.ToString();
                var entity = (from sf in ctx.SubscriptionFile
                    from p in ctx.Product.Where(x => sf.IdProduct == x.IdProduct)
                    from fr in ctx.FirmInstitution.Where(x => x.idFirmInstitution == p.IdFirmInstitution)
                    from sg in ctx.SignatureTransaction.Where(x => sf.IdSignatureTransaction == x.idSignatureTransaction)
                    from ci in ctx.CustomerProspect.Where(x => x.idCustomer == sf.IdCustomerProspect1).DefaultIfEmpty()
                    from dc in ctx.Document.Where(x => x.idSignatureTransaction == sg.idSignatureTransaction)
                    from fv in ctx.FieldValue.Where(x => x.IdDocument == dc.idDocument).DefaultIfEmpty()
                    from fd in ctx.Fields.Where(x => x.IdField == fv.IdField).DefaultIfEmpty()
                    where (sf.IdUserCreated == idUser && dc.DocumentType == SFCrit && !sf.IsDeleted)
                    select new { sf,p,sg,fr,ci,dc,fv,fd} 
                ).ToList();

                List<SubscriptionFileModel> list = new List<SubscriptionFileModel>();
                if (entity != null)
                {
                    List<SubscriptionFileModel> sfLst = new List<SubscriptionFileModel>();
                    entity.ForEach(itm =>
                    {
                        if (!sfLst.Any(x => x.IdSubscriptionFile == itm.sf.IdSubscriptionFile))
                        {
                            var model = new SubscriptionFileModel();
                            model.Role = FrsRole.CGP;
                            model.IdSubscriptionFile = itm.sf.IdSubscriptionFile;
                            model.DateCreated = itm.sf.DateCreated;
                            model.SubmissionMode = itm.sf.SubmissionMode;
                            model.DatesReportsLab = itm.sf.DatesReportLab;
                            var st = new SignatureTransactionEntity();
                            st.IdSignatureTransaction = itm.sg.idSignatureTransaction;
                            st.Status = itm.sg.Status;
                            st.DateSignaturesObjet = itm.sg.DateSignaturesObjet;
                            model.SignatureTransaction = st;
                            var pt = new ProductModel();
                            pt.IdProduct = itm.p.IdProduct;
                            pt.Name = itm.p.Name;
                            var frm = new FirmInstitutionViewModel();
                            frm.IdFirmInstitution = itm.fr.idFirmInstitution;
                            frm.Email = itm.fr.E_mail;
                            frm.FirmInstitutionName = itm.fr.FirmInstitutionName;
                            pt.FirmInstitution = frm;
                            model.Product = pt;

                            var ui = new UserModel();
                            if (itm.fv != null && itm.fv.Fields != null)
                            {
                                ui.UserName = itm.fv.Fields.NameKey == NOM_SSC ? itm.fv.Value : string.Empty;
                                ui.UserFirstName = itm.fv.Fields.NameKey == PRENOM_SSC ? itm.fv.Value : string.Empty;
                                ui.UserMobilePhone = itm.fv.Fields.NameKey == MOBILE_SSC ? itm.fv.Value : string.Empty;
                                ui.IdDocument = itm.fv.IdDocument;
                            }
                            model.User = ui;
                            
                            sfLst.Add(model);
                        }
                        else
                        {
                            var item = sfLst.FirstOrDefault(x => x.IdSubscriptionFile == itm.sf.IdSubscriptionFile);
                            if (item != null)
                            {
                                if (item.User != null && itm.dc != null && item.User.IdDocument == itm.dc.idDocument)
                                {
                                    if (string.IsNullOrEmpty(item.User.UserName) && itm.fv.Fields.NameKey == NOM_SSC)
                                        item.User.UserName = itm.fv.Value;
                                    if (string.IsNullOrEmpty(item.User.UserFirstName) && itm.fv.Fields.NameKey == PRENOM_SSC)
                                        item.User.UserFirstName = itm.fv.Value;
                                    if (string.IsNullOrEmpty(item.User.UserMobilePhone) && itm.fv.Fields.NameKey == MOBILE_SSC)
                                        item.User.UserMobilePhone = itm.fv.Value;
                                }
                            }
                        }

                    });

                    #region "FILTER"

                    list = sfLst.ToList();

                    list.ForEach(delegate(SubscriptionFileModel sf)
                    {
                        sf.UserCgp = GetUserModelBySubscriptionFile(sf.IdSubscriptionFile);
                    });

                    if (!string.IsNullOrEmpty(letter) && letter.ToUpper() != "TOUS")
                    {
                        list =
                            list.Where(
                                x =>
                                    (x.User != null && x.User.UserName != null && x.User.UserFirstName != null &&
                                     (x.User.UserName.ToLower().StartsWith(letter) || x.User.UserFirstName.ToLower().StartsWith(letter))) ||
                                    (x.FirmInstitution != null && x.FirmInstitution.FirmInstitutionName.ToLower().StartsWith(letter))).ToList();
                    }
                    else if (!string.IsNullOrEmpty(search))
                    {
                        list = list.Where(x =>
                 (x.User != null && x.User.UserName != null && x.User.UserFirstName != null &&
                  (x.User.UserName.ToLower().Contains(search) || x.User.UserFirstName.ToLower().Contains(search))) ||
                 (x.FirmInstitution != null && x.FirmInstitution.FirmInstitutionName.ToLower().Contains(search))).ToList();
                    }
                    #endregion
                }
                

                return list.ToList().ToViewModel();
            }
        }
 public static SubscriptionFileViewModel GetInfoForSessionById(int idSubscriptionFile)
 {
     using (var ctx = new UpsilabEntities())
     {
         var _SF = ElectronicSafeDocumentBL.DocumentType.SF.ToString();
         var entity = (from sf in ctx.SubscriptionFile
                       from dc in ctx.Document.Where(x => x.idSignatureTransaction == sf.IdSignatureTransaction)
                       where sf.IdSubscriptionFile == idSubscriptionFile && dc.DocumentType == _SF
                       select new { sf, dc } ).FirstOrDefault();
         var result = new SubscriptionFileModel();
         if(entity != null) 
         {
             result = new SubscriptionFileModel 
             {
                 IdSubscriptionFile = entity.sf.IdSubscriptionFile,
                 Product = new ProductModel() { IdProduct = entity.sf.IdProduct.Value },
                 User = new UserModel() { IdDocument = entity.dc.idDocument },
                 DatesReportsLab = entity.sf.DatesReportLab
             };
         }
         return result.ToViewModel();
     }
 }
        public static SubscriptionFileViewModel GetInfoLiteById(int idSubscriptionFile)
        {
            using (var ctx = new UpsilabEntities())
            {
                var entity = (from sf in ctx.SubscriptionFile
                              from pd in ctx.Product.Where(x => x.IdProduct == sf.IdProduct)
                              from ui in ctx.User.Where(x => x.idUser == sf.IdUserCreated)
                              from fa in ctx.FirmInstitutionAdviser.Where(x => x.idUser == sf.IdUserCreated)
                              from firmInstitutionCgp in ctx.FirmInstitution.Where(x => x.idFirmInstitution == fa.idFirmInstitutionParent)
                             // from ci in ctx.CustomerProspect.Where(x => x.idCustomer == sf.IdCustomerProspect1).DefaultIfEmpty()
                              //from ui2 in ctx.User.Where(x => x.idUser == ci.IdUser)
                              where sf.IdSubscriptionFile == idSubscriptionFile
                              select new { sf, ui, pd, firmInstitutionCgp }).FirstOrDefault();
                var sfile = new SubscriptionFileModel();
                if (entity != null)
                {
                    sfile.IdSubscriptionFile = entity.sf.IdSubscriptionFile;
                    sfile.Product = new ProductModel() {IdProduct = entity.pd.IdProduct, Name = entity.pd.Name};
                    sfile.User = new UserModel()
                    {
                        IdUser = entity.ui.idUser,
                        UserName = entity.ui.UserName,
                        UserFirstName = entity.ui.UserFirstName,
                        HasLab =  Upsilab.Business.License.LicenseBL.IsValid(entity.ui.IdLicenseLab.HasValue?entity.ui.IdLicenseLab.Value:Guid.Empty),
                        HasReport = Upsilab.Business.License.LicenseBL.IsValid(entity.ui.IdLicenseReport.HasValue ? entity.ui.IdLicenseReport.Value : Guid.Empty)
                    };
                    sfile.DatesReportsLab = entity.sf.DatesReportLab;
                    sfile.IdSignatureTransaction = entity.sf.IdSignatureTransaction.HasValue ? entity.sf.IdSignatureTransaction.Value : Guid.Empty;
                    sfile.IdCustomer1 = entity.sf.IdCustomerProspect1;
                    var citmp = new CustomerProspectViewModel();

                    var custom = ctx.SubscriptionFile.Join(ctx.CustomerProspect,
                        sf => sf.IdCustomerProspect1,
                        ci => ci.idCustomer,
                        (sf, ci) => new {Sf = sf, Ci = ci})
                        .FirstOrDefault(x => x.Sf.IdSubscriptionFile == idSubscriptionFile);
                    if (custom != null && custom.Ci != null)
                    {
                        citmp.idCustomer = custom.Ci.idCustomer;
                        var ui2 = ctx.User.FirstOrDefault(x => x.idUser == custom.Ci.IdUser);

                        if (ui2 != null)
                        {
                            citmp.UserEmail = ui2.UserEmail ?? string.Empty;
                            citmp.UserName = ui2.UserName ?? string.Empty;
                            citmp.UserFirstName = ui2.UserFirstName ?? string.Empty; 
                        }
                    }
                    
                   sfile.CustomerProspectTitulaire1 = citmp;
                    sfile.IdSubscriptionFile = entity.sf.IdSubscriptionFile;
                    sfile.Product = new ProductModel() {IdProduct = entity.pd.IdProduct, Name = entity.pd.Name};
                    sfile.User = new UserModel()
                    {
                        IdUser = entity.ui.idUser,
                        UserName = entity.ui.UserName,
                        UserFirstName = entity.ui.UserFirstName
                    };
                    sfile.FirmInstitution = new FirmInstitutionViewModel { IdFirmInstitution = entity.firmInstitutionCgp.idFirmInstitution, FirmInstitutionName = entity.firmInstitutionCgp.FirmInstitutionName };
                    sfile.DatesReportsLab = entity.sf.DatesReportLab;
                    sfile.IdSignatureTransaction = entity.sf.IdSignatureTransaction.HasValue ? entity.sf.IdSignatureTransaction.Value : Guid.Empty;
                }
                return sfile.ToViewModel();
            }
        }
        public static IEnumerable<SubscriptionFileViewModel> GetInformationSubscriptionFilesFrs(Guid idFirmInstitution, string letter = "tous", string search = "")
        {
            using (var ctx = new UpsilabEntities())
            {
                letter = letter.ToLower();
                search = search.ToLower();

                var SFCrit = Upsilab.Business.Document.ElectronicSafeDocumentBL.DocumentType.SF.ToString();
                var entity = (from sf in ctx.SubscriptionFile
                              from p in ctx.Product.Where(x => sf.IdProduct == x.IdProduct)
                              from cgp in ctx.User.Where(x => x.idUser == sf.IdUserCreated)
                              from sg in ctx.SignatureTransaction.Where(x => sf.IdSignatureTransaction == x.idSignatureTransaction)
                              from firm in ctx.FirmInstitution.Where(x => x.idFirmInstitution == p.IdFirmInstitution)
                              from ci in ctx.CustomerProspect.Where(x => x.idCustomer == sf.IdCustomerProspect1).DefaultIfEmpty()
                              from dc in ctx.Document.Where(x => x.idSignatureTransaction == sg.idSignatureTransaction)
                              from fv in ctx.FieldValue.Where(x => x.IdDocument == dc.idDocument).DefaultIfEmpty()
                              from fd in ctx.Fields.Where(x => x.IdField == fv.IdField).DefaultIfEmpty()
                              where (firm.idFirmInstitution == idFirmInstitution && dc.DocumentType == SFCrit && !sf.IsDeleted)
                              select new { sf,p,cgp,sg,firm,ci,dc,fv,fd}
            ).ToList();

                List<SubscriptionFileModel> list = new List<SubscriptionFileModel>();
                if (entity != null)
                {
                    List<SubscriptionFileModel> sfLst = new List<SubscriptionFileModel>();
                    var lst = new List<SubscriptionFileModel>();
                    entity.ForEach(x => {
                        var model = new SubscriptionFileModel();
                        model.Role = FrsRole.FRS;
                        model.IdSubscriptionFile = x.sf.IdSubscriptionFile;
                        model.DateCreated = x.sf.DateCreated;
                        model.SubmissionMode = x.sf.SubmissionMode;
                        model.DatesReportsLab = x.sf.DatesReportLab;
                        model.EmailUserCreator = x.cgp.UserEmail;

                        var signature = new SignatureTransactionEntity();
                        signature.IdSignatureTransaction = x.sg.idSignatureTransaction;
                        signature.Status = x.sg.Status;
                        signature.DateSignaturesObjet = x.sg.DateSignaturesObjet;
                        model.SignatureTransaction = signature;

                        var product = new ProductModel();
                        product.IdProduct = x.p.IdProduct;
                        product.Name = x.p.Name;
                        model.Product = product;

                        var user = new UserModel();
                        user.UserName = x.fv != null && x.fv.Fields != null && !string.IsNullOrEmpty(x.fv.Fields.NameKey) && x.fv.Fields.NameKey == NOM_SSC ? x.fv.Value : string.Empty;
                        user.UserFirstName = x.fv != null && x.fv.Fields != null && !string.IsNullOrEmpty(x.fv.Fields.NameKey) && x.fv.Fields.NameKey == PRENOM_SSC ? x.fv.Value : string.Empty;
                        user.UserMobilePhone = x.fv != null && x.fv.Fields != null && !string.IsNullOrEmpty(x.fv.Fields.NameKey) && x.fv.Fields.NameKey == MOBILE_SSC ? x.fv.Value : string.Empty;
                        user.IdDocument = x.fv != null ? x.fv.IdDocument : Guid.Empty;
                        model.User = user;

                        var firmInstitution = new FirmInstitutionViewModel();
                        firmInstitution.IdFirmInstitution = x.firm.idFirmInstitution;
                        firmInstitution.Email = x.firm.E_mail;
                        firmInstitution.FirmInstitutionName = x.firm.FirmInstitutionName;
                        model.FirmInstitution = firmInstitution;

                        if (x.ci != null)
                        {
                            var customerProspect = new CustomerProspectViewModel();
                            customerProspect.DateUpdatedLast = x.ci.DateUpdatedLast;
                            customerProspect.DateUpdatedNext = x.ci.DateUpdatedNext;
                            customerProspect.DateShiftLABLast = x.ci.DateShiftLABLast;
                            customerProspect.DateShiftLABNext = x.ci.DateShiftLABNext;
                            model.CustomerProspectTitulaire1 = customerProspect;
                        }

                        model.IdUserCgp = x.cgp.idUser;

                        lst.Add(model);
                    });

                    lst.ToList().ForEach(delegate(SubscriptionFileModel itm)
                    {
                        if (!sfLst.Any(x => x.IdSubscriptionFile == itm.IdSubscriptionFile))
                        {
                            sfLst.Add(itm);
                        }
                        else
                        {
                            var item = itm != null ? sfLst.FirstOrDefault(x => x.IdSubscriptionFile == itm.IdSubscriptionFile) : null;
                            if (item != null)
                            {
                                if (item.User != null && itm.User != null && item.User.IdDocument.HasValue && item.User.IdDocument != Guid.Empty && item.User.IdDocument == itm.User.IdDocument)
                                {
                                    if (string.IsNullOrEmpty(item.User.UserName))
                                        item.User.UserName = itm.User.UserName;
                                    if (string.IsNullOrEmpty(item.User.UserFirstName))
                                        item.User.UserFirstName = itm.User.UserFirstName;
                                    if (string.IsNullOrEmpty(item.User.UserMobilePhone))
                                        item.User.UserMobilePhone = itm.User.UserMobilePhone;
                                }
                            }
                        }

                    });

                    #region "FILTER"

                    list = sfLst.ToList();

                    list.ForEach(delegate(SubscriptionFileModel sf)
                    {
                        sf.UserCgp = GetUserModelBySubscriptionFile(sf.IdSubscriptionFile);
                    });

                    if (!string.IsNullOrEmpty(letter) && letter.ToUpper() != "TOUS")
                    {
                        list =
                            list.Where(
                                x =>
                                    (x.User != null && x.User.UserName != null && x.User.UserFirstName != null &&
                                     (x.User.UserName.ToLower().StartsWith(letter) || x.User.UserFirstName.ToLower().StartsWith(letter))) ||
                                    (x.FirmInstitution != null && x.FirmInstitution.FirmInstitutionName.ToLower().StartsWith(letter))).ToList();
                    }
                    else if (!string.IsNullOrEmpty(search))
                    {
                        list = list.Where(x =>
                 (x.User != null && x.User.UserName != null && x.User.UserFirstName != null &&
                  (x.User.UserName.ToLower().Contains(search) || x.User.UserFirstName.ToLower().Contains(search))) ||
                 (x.FirmInstitution != null && x.FirmInstitution.FirmInstitutionName.ToLower().Contains(search))).ToList();
                    }
                    #endregion
                }
                // lst = lst;

                
                
                return list.OrderBy(x => x.IdSubscriptionFile).ToViewModel();

            }
        }