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