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(); } }