public static List <LegalEntityLegalRepresentativeAuditView> GetLegalEntityLegalRepresentativeAuditView(ObjectContext context, int relatedEntityPK)
{
IAuditingDetailsRepository auditingDetailsRepository = new AuditingDetailsRepository(context);
IAuditingMasterRepository auditingMasterRepository = new AuditingMasterRepository(context);
var sessionTokens = (from am in auditingMasterRepository.GetAll().Where(c => c.TableName == "LegalEntityLegalRepresentatives")
where am.RelatedEntityPK == relatedEntityPK
select new {
AuditingMasterPK = am.AuditingMasterPK,
RelatedEntityPK = am.RelatedEntityPK,
SessionToken = am.SessionToken
}).ToList();
List <LegalEntityLegalRepresentativeAuditView> legalEntityLegalRepresentativeAuditViewList = new List <LegalEntityLegalRepresentativeAuditView>();
foreach (var item in sessionTokens)
{
var record = auditingDetailsRepository.GetAuditingDetailByAuditingMasterPK(item.AuditingMasterPK).ToList();
LegalEntityLegalRepresentativeAuditView legalEntityLegalRepresentativeAuditView = new LegalEntityLegalRepresentativeAuditView();
legalEntityLegalRepresentativeAuditView.LegalEntityFK = record.checkInteger("LegalEntityFK");
legalEntityLegalRepresentativeAuditView.LegalRepresentativeFK = record.checkInteger("LegalRepresentativeFK");
legalEntityLegalRepresentativeAuditView.WayOfRepresentationFK = record.checkInteger("WayOfRepresentationFK");
legalEntityLegalRepresentativeAuditView.ChangeDate = record.checkDate("ChangeDate");
legalEntityLegalRepresentativeAuditView.Deleted = record.checkBoolean("Deleted");
legalEntityLegalRepresentativeAuditViewList.Add(legalEntityLegalRepresentativeAuditView);
}
IPhysicalEntitiesRepository physicalEntitiesRepository = new PhysicalEntitiesRepository(context);
IQueryable <PhysicalEntity> physicalEntityTable = physicalEntitiesRepository.GetValid();
IWaysOfRepresentationRepository WaysOfRepresentationRepository = new WaysOfRepresentationRepository(context);
IQueryable <WayOfRepresentation> wayOfRepresentationTable = WaysOfRepresentationRepository.GetValid();
List <LegalEntityLegalRepresentativeAuditView> legalEntityLegalRepresentative =
(from t in legalEntityLegalRepresentativeAuditViewList
from t1 in physicalEntityTable.Where(tbl => tbl.PhysicalEntityPK == t.LegalRepresentativeFK).DefaultIfEmpty()
from t2 in wayOfRepresentationTable.Where(tbl => tbl.WayOfRepresentationPK == t.WayOfRepresentationFK).DefaultIfEmpty()
where t.ChangeDate != null
select new LegalEntityLegalRepresentativeAuditView
{
LegalEntityLegalRepresentativePK = t.LegalEntityLegalRepresentativePK,
LegalRepresentativeName = t1 != null && t1.Firstname != null && t1.Lastname != null ? t1.Firstname + " " + t1.Lastname : null,
WayOfRepresentationName = t2 != null && t2.Name != null ? t2.Name : null,
ChangeDate = t.ChangeDate != null ? t.ChangeDate : null,
Deleted = t.Deleted != null ? t.Deleted : null,
}).OrderBy(c => c.ChangeDate).ToList();
return(legalEntityLegalRepresentative);
}
public static List <LegalEntityBankAuditView> GetLegalEntityBankAuditView(ObjectContext context, int relatedEntityPK)
{
IAuditingDetailsRepository auditingDetailsRepository = new AuditingDetailsRepository(context);
IAuditingMasterRepository auditingMasterRepository = new AuditingMasterRepository(context);
var sessionTokens = (from am in auditingMasterRepository.GetAll().Where(c => c.TableName == "LegalEntityBanks")
where am.RelatedEntityPK == relatedEntityPK
select new {
AuditingMasterPK = am.AuditingMasterPK,
RelatedEntityPK = am.RelatedEntityPK,
SessionToken = am.SessionToken
}).ToList();
List <LegalEntityBankAuditView> legalEntityBankAuditViewList = new List <LegalEntityBankAuditView>();
foreach (var item in sessionTokens)
{
var record = auditingDetailsRepository.GetAuditingDetailByAuditingMasterPK(item.AuditingMasterPK).ToList();
LegalEntityBankAuditView legalEntityBankAuditView = new LegalEntityBankAuditView();
legalEntityBankAuditView.BankFK = record.checkInteger("BankFK");
legalEntityBankAuditView.Iban = record.checkString("Iban");
legalEntityBankAuditView.ChangeDate = record.checkDate("ChangeDate");
legalEntityBankAuditView.Deleted = record.checkBoolean("Deleted");
legalEntityBankAuditViewList.Add(legalEntityBankAuditView);
}
IBanksRepository banksRepository = new BanksRepository(context);
IQueryable <Bank> banksTable = banksRepository.GetValid();
List <LegalEntityBankAuditView> legalEntityBanks =
(from t in legalEntityBankAuditViewList
from t1 in banksTable.Where(tbl => tbl.BankPK == t.BankFK).DefaultIfEmpty()
where t.ChangeDate != null
select new LegalEntityBankAuditView
{
LegalEntityBankPK = t.LegalEntityBankPK,
BankFK = t.BankFK,
BankIban = t.Iban,
BankName = t1 != null && t1.Name != null ? t1.Name : null,
ChangeDate = t.ChangeDate != null ? t.ChangeDate : null,
Deleted = t.Deleted != null ? t.Deleted : null,
}).OrderBy(c => c.ChangeDate).ToList();
return(legalEntityBanks);
}
public static List <LegalEntityBranchAuditView> GetLegalEntityBranchAuditView(ObjectContext context, int relatedEntityPK)
{
IAuditingDetailsRepository auditingDetailsRepository = new AuditingDetailsRepository(context);
IAuditingMasterRepository auditingMasterRepository = new AuditingMasterRepository(context);
var sessionTokens = (from am in auditingMasterRepository.GetAll().Where(c => c.TableName == "LegalEntityBranches")
where am.RelatedEntityPK == relatedEntityPK
select new {
AuditingMasterPK = am.AuditingMasterPK,
RelatedEntityPK = am.RelatedEntityPK,
SessionToken = am.SessionToken
}).ToList();
List <LegalEntityBranchAuditView> legalEntityLegalEntityBranchAuditViewList = new List <LegalEntityBranchAuditView>();
foreach (var item in sessionTokens)
{
var record = auditingDetailsRepository.GetAuditingDetailByAuditingMasterPK(item.AuditingMasterPK).ToList();
LegalEntityBranchAuditView legalEntityLegalEntityBranchAuditView = new LegalEntityBranchAuditView();
legalEntityLegalEntityBranchAuditView.Name = record.checkString("Name");
legalEntityLegalEntityBranchAuditView.CountryFK = record.checkInteger("CountryFK");
legalEntityLegalEntityBranchAuditView.CountyFK = record.checkInteger("CountyFK");
legalEntityLegalEntityBranchAuditView.CityCommunityFK = record.checkInteger("CityCommunityFK");
legalEntityLegalEntityBranchAuditView.PostalOfficeFK = record.checkInteger("PostalOfficeFK");
legalEntityLegalEntityBranchAuditView.PlaceFK = record.checkInteger("PlaceFK");
legalEntityLegalEntityBranchAuditView.StreetName = record.checkString("StreetName");
legalEntityLegalEntityBranchAuditView.Phone = record.checkString("Phone");
legalEntityLegalEntityBranchAuditView.Fax = record.checkString("Fax");
legalEntityLegalEntityBranchAuditView.Mobile = record.checkString("Mobile");
legalEntityLegalEntityBranchAuditView.EMail = record.checkString("EMail");
legalEntityLegalEntityBranchAuditView.ChangeDate = record.checkDate("ChangeDate");
legalEntityLegalEntityBranchAuditView.Deleted = record.checkBoolean("Deleted");
legalEntityLegalEntityBranchAuditViewList.Add(legalEntityLegalEntityBranchAuditView);
}
ICountriesRepository countriesRepository = new CountriesRepository(context);
IQueryable <Country> countriesTable = countriesRepository.GetValid();
ICountiesRepository countiesRepository = new CountiesRepository(context);
IQueryable <County> countiesTable = countiesRepository.GetValid();
ICitiesCommunitiesRepository citiesCommunitiesRepository = new CitiesCommunitiesRepository(context);
IQueryable <CityCommunity> citiesCommunitiesTable = citiesCommunitiesRepository.GetValid();
IPlacesRepository placesRepository = new PlacesRepository(context);
IQueryable <Place> placesTable = placesRepository.GetValid();
IPostalOfficesRepository postalOfficesRepository = new PostalOfficesRepository(context);
IQueryable <PostalOffice> postalOfficesTable = postalOfficesRepository.GetValid();
List <LegalEntityBranchAuditView> legalEntityLegalEntityBranch =
(from t in legalEntityLegalEntityBranchAuditViewList
from t1 in countriesTable.Where(tbl => tbl.CountryPK == t.CountryFK).DefaultIfEmpty()
from t2 in countiesTable.Where(tbl => tbl.CountyPK == t.CountyFK).DefaultIfEmpty()
from t3 in citiesCommunitiesTable.Where(tbl => tbl.CityCommunityPK == t.CityCommunityFK).DefaultIfEmpty()
from t4 in placesTable.Where(tbl => tbl.PlacePK == t.PlaceFK).DefaultIfEmpty()
from t5 in postalOfficesTable.Where(tbl => tbl.PostalOfficePK == t.PostalOfficeFK).DefaultIfEmpty()
where t.ChangeDate != null
select new LegalEntityBranchAuditView
{
LegalEntityBranchPK = t.LegalEntityBranchPK,
Name = t.Name,
CountryName = t1 != null && t1.Name != null ? t1.Name : null,
CountyName = t2 != null && t2.Name != null ? t2.Name : null,
CityCommunityName = t3 != null && t3.Name != null ? t3.Name : null,
PlaceName = t4 != null && t4.Name != null ? t4.Name : null,
PostalOfficeName = t5 != null && t5.Name != null ? t5.Name : null,
StreetName = t.StreetName != null ? t.StreetName : null,
Phone = t.Phone != null ? t.Phone : null,
Fax = t.Fax != null ? t.Fax : null,
Mobile = t.Mobile != null ? t.Mobile : null,
EMail = t.EMail != null ? t.EMail : null,
ChangeDate = t.ChangeDate != null ? t.ChangeDate : null,
Deleted = t.Deleted != null ? t.Deleted : null,
}).OrderBy(c => c.ChangeDate).ToList();
return(legalEntityLegalEntityBranch);
}
public static List <LegalEntityOwnerAuditView> GetLegalEntityOwnerAuditView(ObjectContext context, int relatedEntityPK)
{
IAuditingDetailsRepository auditingDetailsRepository = new AuditingDetailsRepository(context);
IAuditingMasterRepository auditingMasterRepository = new AuditingMasterRepository(context);
var sessionTokens = (from am in auditingMasterRepository.GetAll().Where(c => c.TableName == "LegalEntityOwners")
where am.RelatedEntityPK == relatedEntityPK
select new {
AuditingMasterPK = am.AuditingMasterPK,
RelatedEntityPK = am.RelatedEntityPK,
SessionToken = am.SessionToken
}).ToList();
List <LegalEntityOwnerAuditView> legalEntityOwnerAuditViewList = new List <LegalEntityOwnerAuditView>();
foreach (var item in sessionTokens)
{
var record = auditingDetailsRepository.GetAuditingDetailByAuditingMasterPK(item.AuditingMasterPK).ToList();
LegalEntityOwnerAuditView legalEntityOwnerAuditView = new LegalEntityOwnerAuditView();
legalEntityOwnerAuditView.LegalEntityFK = record.checkInteger("LegalEntityFK");
legalEntityOwnerAuditView.OwnerFK = record.checkInteger("OwnerFK");
legalEntityOwnerAuditView.OwnerType = record.checkString("OwnerType");
legalEntityOwnerAuditView.BusinessShareAmount = record.checkDecimal("BusinessShareAmount");
legalEntityOwnerAuditView.PaidBussinesShareAmount = record.checkDecimal("PaidBussinesShareAmount");
legalEntityOwnerAuditView.NominalBussinesShareAmount = record.checkDecimal("NominalBussinesShareAmount");
legalEntityOwnerAuditView.AdditionalFactorFK = record.checkInteger("AdditionalFactorFK");
legalEntityOwnerAuditView.FulfilledFactorFK = record.checkInteger("FulfilledFactorFK");
legalEntityOwnerAuditView.BussinesShareBurdenFK = record.checkInteger("BussinesShareBurdenFK");
legalEntityOwnerAuditView.ChangeTypeFK = record.checkInteger("ChangeTypeFK");
legalEntityOwnerAuditView.NumberOfVotes = record.checkInteger("NumberOfVotes");
legalEntityOwnerAuditView.EntryDate = record.checkDate("EntryDate");
legalEntityOwnerAuditView.ChangeDate = record.checkDate("ChangeDate");
legalEntityOwnerAuditView.Deleted = record.checkBoolean("Deleted");
legalEntityOwnerAuditViewList.Add(legalEntityOwnerAuditView);
}
ILegalEntitiesRepository legalEntitiesRepository = new LegalEntitiesRepository(context);
IQueryable <LegalEntity> legalEntitiesTable = legalEntitiesRepository.GetValid();
IPhysicalEntitiesRepository physicalEntitiesRepository = new PhysicalEntitiesRepository(context);
IQueryable <PhysicalEntity> physicalEntitiesTable = physicalEntitiesRepository.GetValid();
IAdditionalFactorsRepository additionalFactorsRepository = new AdditionalFactorsRepository(context);
IQueryable <AdditionalFactor> additionalFactorsTable = additionalFactorsRepository.GetValid();
IFulfilledFactorsRepository fulfilledFactorsRepository = new FulfilledFactorsRepository(context);
IQueryable <FulfilledFactor> fulfilledFactorsTable = fulfilledFactorsRepository.GetValid();
IBussinesShareBurdensRepository bussinesShareBurdensRepository = new BussinesShareBurdensRepository(context);
IQueryable <BussinesShareBurden> bussinesShareBurdensTable = bussinesShareBurdensRepository.GetValid();
IChangeTypesRepository changeTypesRepository = new ChangeTypesRepository(context);
IQueryable <ChangeType> changeTypesTable = changeTypesRepository.GetValid();
IQueryable <LegalEntityOwnerAuditView> legalEntityOwnersLE =
(from t in legalEntityOwnerAuditViewList
from t1 in legalEntitiesTable.Where(tbl => tbl.LegalEntityPK == t.OwnerFK).DefaultIfEmpty()
from t2 in additionalFactorsTable.Where(tbl => tbl.AdditionalFactorPK == t.AdditionalFactorFK).DefaultIfEmpty()
from t3 in fulfilledFactorsTable.Where(tbl => tbl.FulfilledFactorPK == t.FulfilledFactorFK).DefaultIfEmpty()
from t4 in bussinesShareBurdensTable.Where(tbl => tbl.BussinesShareBurdenPK == t.BussinesShareBurdenFK).DefaultIfEmpty()
from t5 in changeTypesTable.Where(tbl => tbl.ChangeTypePK == t.ChangeTypeFK).DefaultIfEmpty()
where t.ChangeDate != null && t.OwnerType != null && t.OwnerType.Contains("le")
select new LegalEntityOwnerAuditView
{
LegalEntityOwnerPK = t.LegalEntityOwnerPK,
OwnerName = t1 != null && t1.Name != null ? t1.Name : null,
AdditionalFactorName = t2 != null && t2.Name != null ? t2.Name : null,
FulfilledFactorName = t3 != null && t3.Name != null ? t3.Name : null,
BussinesShareBurdenName = t4 != null && t4.Name != null ? t4.Name : null,
ChangeTypeName = t5 != null && t5.Name != null ? t5.Name : null,
BusinessShareAmount = t.BusinessShareAmount != null ? t.BusinessShareAmount : null,
PaidBussinesShareAmount = t.PaidBussinesShareAmount != null ? t.PaidBussinesShareAmount : null,
NominalBussinesShareAmount = t.NominalBussinesShareAmount != null ? t.NominalBussinesShareAmount : null,
NumberOfVotes = t.NumberOfVotes != null ? t.NumberOfVotes : null,
EntryDate = t.EntryDate != null ? t.EntryDate : null,
ChangeDate = t.ChangeDate != null ? t.ChangeDate : null,
Deleted = t.Deleted != null ? t.Deleted : null,
}).AsQueryable <LegalEntityOwnerAuditView>();
IQueryable <LegalEntityOwnerAuditView> legalEntityOwnersPE =
(from t in legalEntityOwnerAuditViewList
from t1 in physicalEntitiesTable.Where(tbl => tbl.PhysicalEntityPK == t.OwnerFK).DefaultIfEmpty()
from t2 in additionalFactorsTable.Where(tbl => tbl.AdditionalFactorPK == t.AdditionalFactorFK).DefaultIfEmpty()
from t3 in fulfilledFactorsTable.Where(tbl => tbl.FulfilledFactorPK == t.FulfilledFactorFK).DefaultIfEmpty()
from t4 in bussinesShareBurdensTable.Where(tbl => tbl.BussinesShareBurdenPK == t.BussinesShareBurdenFK).DefaultIfEmpty()
from t5 in changeTypesTable.Where(tbl => tbl.ChangeTypePK == t.ChangeTypeFK).DefaultIfEmpty()
where t.ChangeDate != null && t.OwnerType != null && t.OwnerType.Contains("pe")
select new LegalEntityOwnerAuditView
{
LegalEntityOwnerPK = t.LegalEntityOwnerPK,
OwnerName = t1 != null && t1.Firstname != null && t1.Lastname != null ? t1.Firstname + " " + t1.Lastname : null,
AdditionalFactorName = t2 != null && t2.Name != null ? t2.Name : null,
FulfilledFactorName = t3 != null && t3.Name != null ? t3.Name : null,
BussinesShareBurdenName = t4 != null && t4.Name != null ? t4.Name : null,
ChangeTypeName = t5 != null && t5.Name != null ? t5.Name : null,
BusinessShareAmount = t.BusinessShareAmount != null ? t.BusinessShareAmount : null,
PaidBussinesShareAmount = t.PaidBussinesShareAmount != null ? t.PaidBussinesShareAmount : null,
NominalBussinesShareAmount = t.NominalBussinesShareAmount != null ? t.NominalBussinesShareAmount : null,
NumberOfVotes = t.NumberOfVotes != null ? t.NumberOfVotes : null,
EntryDate = t.EntryDate != null ? t.EntryDate : null,
ChangeDate = t.ChangeDate != null ? t.ChangeDate : null,
Deleted = t.Deleted != null ? t.Deleted : null,
}).AsQueryable <LegalEntityOwnerAuditView>();
List <LegalEntityOwnerAuditView> legalEntityOwners = legalEntityOwnersLE.Union(legalEntityOwnersPE).OrderBy(c => c.ChangeDate).ToList();
return(legalEntityOwners);
}
public static List <PhysicalEntityAuditView> GetPhysicalEntityAuditView(ObjectContext context, int relatedEntityPK)
{
IAuditingDetailsRepository auditingDetailsRepository = new AuditingDetailsRepository(context);
IAuditingMasterRepository auditingMasterRepository = new AuditingMasterRepository(context);
List <PhysicalEntityAuditView> physicalEntityAuditViewList = new List <PhysicalEntityAuditView>();
var sessionTokens = (from am in auditingMasterRepository.GetAll().Where(c => c.TableName == "PhysicalEntities")
where am.RelatedEntityPK == relatedEntityPK
select new {
auditingMasterPK = am.AuditingMasterPK,
sessionToken = am.SessionToken
}).ToList();
foreach (var item in sessionTokens)
{
List <AuditingDetail> record = auditingDetailsRepository.GetAuditingDetailByAuditingMasterPK(item.auditingMasterPK).ToList();
PhysicalEntityAuditView peav = new PhysicalEntityAuditView();
peav.Firstname = record.checkString("Firstname");
peav.Lastname = record.checkString("Lastname");
peav.Gender = record.checkString("Gender");
peav.OIB = record.checkString("OIB");
peav.JMBG = record.checkString("JMBG");
peav.DateOfBirth = record.checkDate("DateOfBirth");
peav.CitizenshipFK = record.checkInteger("CitizenshipFK");
peav.BirthCountryFK = record.checkInteger("BirthCountryFK");
peav.BirthCountyFK = record.checkInteger("BirthCountyFK");
peav.BirthCityCommunityFK = record.checkInteger("BirthCityCommunityFK");
peav.BirthPlace = record.checkString("BirthPlace");
peav.ResidenceCountryFK = record.checkInteger("ResidenceCountryFK");
peav.ResidenceCountyFK = record.checkInteger("ResidenceCountyFK");
peav.ResidenceCityCommunityFK = record.checkInteger("ResidenceCityCommunityFK");
peav.ResidencePostalOfficeFK = record.checkInteger("ResidencePostalOfficeFK");
peav.ResidencePlaceFK = record.checkInteger("ResidencePlaceFK");
peav.ResidencePlace = record.checkString("ResidencePlace");
peav.ResidenceStreetName = record.checkString("ResidenceStreetName");
peav.IdentityCardNumber = record.checkString("IdentityCardNumber");
peav.IdentityCardDateOfIssue = record.checkDate("IdentityCardDateOfIssue");
peav.IdentityCardRegionalOfficeFK = record.checkInteger("IdentityCardRegionalOfficeFK");
peav.IdentityCardDateTillValid = record.checkDate("IdentityCardDateTillValid");
peav.PassportNumber = record.checkString("PassportNumber");
peav.PassportDateOfIssue = record.checkDate("PassportDateOfIssue");
peav.PassportDateTillValid = record.checkDate("PassportDateTillValid");
peav.PassportCountryOfIssueFK = record.checkInteger("PassportCountryOfIssueFK");
peav.PassportPlaceOfIssue = record.checkString("PassportPlaceOfIssue");
peav.ReferentRegionalOfficeFK = record.checkInteger("ReferentRegionalOfficeFK");
peav.ReferentSubstationFK = record.checkInteger("ReferentSubstationFK");
peav.ChangeDate = record.checkDate("ChangeDate");
peav.LegalRepresentative = record.checkBoolean("LegalRepresentative");
peav.Owner = record.checkBoolean("Owner");
peav.Referent = record.checkBoolean("Referent");
physicalEntityAuditViewList.Add(peav);
}
// Connect all foreign keys and return data collection as List<PhysicalEntityAuditView>
ICountriesRepository countriesRepository = new CountriesRepository(context);
IQueryable <Country> countryTable = countriesRepository.GetValid();
ICountiesRepository countiesRepository = new CountiesRepository(context);
IQueryable <County> countyTable = countiesRepository.GetValid();
ICitiesCommunitiesRepository citiesCommunitiesRepository = new CitiesCommunitiesRepository(context);
IQueryable <CityCommunity> cityCommunityTable = citiesCommunitiesRepository.GetValid();
IPostalOfficesRepository postalOfficesRepository = new PostalOfficesRepository(context);
IQueryable <PostalOffice> postalOfficeTable = postalOfficesRepository.GetValid();
IPlacesRepository placesRepository = new PlacesRepository(context);
IQueryable <Place> placeTable = placesRepository.GetValid();
IRegionalOfficesRepository regionalOfficesRepository = new RegionalOfficesRepository(context);
IQueryable <RegionalOffice> regionalOfficeTable = regionalOfficesRepository.GetValid();
ISubstationsRepository substationsRepository = new SubstationsRepository(context);
IQueryable <Substation> substationTable = substationsRepository.GetValid();
List <PhysicalEntityAuditView> physicalEntityAuditView =
(from t in physicalEntityAuditViewList
from t1 in countryTable.Where(tbl => tbl.CountryPK == t.CitizenshipFK).DefaultIfEmpty()
from t2 in countryTable.Where(tbl => tbl.CountryPK == t.CitizenshipFK).DefaultIfEmpty()
from t3 in countyTable.Where(tbl => tbl.CountyPK == t.BirthCountyFK).DefaultIfEmpty()
from t4 in cityCommunityTable.Where(tbl => tbl.CityCommunityPK == t.BirthCityCommunityFK).DefaultIfEmpty()
from t5 in countryTable.Where(tbl => tbl.CountryPK == t.ResidenceCountryFK).DefaultIfEmpty()
from t6 in countyTable.Where(tbl => tbl.CountyPK == t.ResidenceCountyFK).DefaultIfEmpty()
from t7 in cityCommunityTable.Where(tbl => tbl.CityCommunityPK == t.ResidenceCityCommunityFK).DefaultIfEmpty()
from t8 in postalOfficeTable.Where(tbl => tbl.PostalOfficePK == t.ResidencePostalOfficeFK).DefaultIfEmpty()
from t9 in placeTable.Where(tbl => tbl.PlacePK == t.ResidencePlaceFK).DefaultIfEmpty()
from t10 in countryTable.Where(tbl => tbl.CountryPK == t.PassportCountryOfIssueFK).DefaultIfEmpty()
from t11 in regionalOfficeTable.Where(tbl => tbl.RegionalOfficePK == t.IdentityCardRegionalOfficeFK).DefaultIfEmpty()
from t12 in regionalOfficeTable.Where(tbl => tbl.RegionalOfficePK == t.ReferentRegionalOfficeFK).DefaultIfEmpty()
from t13 in substationTable.Where(tbl => tbl.SubstationPK == t.ReferentSubstationFK).DefaultIfEmpty()
select new PhysicalEntityAuditView
{
PhysicalEntityPK = t.PhysicalEntityPK,
Firstname = t.Firstname != null ? t.Firstname : null,
Lastname = t.Lastname != null ? t.Lastname : null,
Gender = t.Gender != null ? t.Gender : null,
OIB = t.OIB != null ? t.OIB : null,
JMBG = t.JMBG != null ? t.JMBG : null,
DateOfBirth = t.DateOfBirth != null ? t.DateOfBirth : null,
CitizenshipFK = t.CitizenshipFK != null ? t.CitizenshipFK : null,
BirthCountryFK = t.BirthCountryFK != null ? t.BirthCountryFK : null,
BirthCountyFK = t.BirthCountyFK != null ? t.BirthCountyFK : null,
BirthCityCommunityFK = t.BirthCityCommunityFK != null ? t.BirthCityCommunityFK : null,
BirthPlace = t.BirthPlace != null ? t.BirthPlace : null,
ResidenceCountryFK = t.ResidenceCountryFK != null ? t.ResidenceCountryFK : null,
ResidenceCountyFK = t.ResidenceCountyFK != null ? t.ResidenceCountyFK : null,
ResidenceCityCommunityFK = t.ResidenceCityCommunityFK != null ? t.ResidenceCityCommunityFK : null,
ResidencePostalOfficeFK = t.ResidencePostalOfficeFK != null ? t.ResidencePostalOfficeFK : null,
ResidencePlaceFK = t.ResidencePlaceFK != null ? t.ResidencePlaceFK : null,
ResidencePlace = t.ResidencePlace != null ? t.ResidencePlace : null,
ResidenceStreetName = t.ResidenceStreetName != null ? t.ResidenceStreetName : null,
IdentityCardNumber = t.IdentityCardNumber != null ? t.IdentityCardNumber : null,
IdentityCardDateOfIssue = t.IdentityCardDateOfIssue != null ? t.IdentityCardDateOfIssue : null,
IdentityCardRegionalOfficeFK = t.IdentityCardRegionalOfficeFK != null ? t.IdentityCardRegionalOfficeFK : null,
IdentityCardDateTillValid = t.IdentityCardDateTillValid != null ? t.IdentityCardDateTillValid : null,
PassportNumber = t.PassportNumber != null ? t.PassportNumber : null,
PassportDateOfIssue = t.PassportDateOfIssue != null ? t.PassportDateOfIssue : null,
PassportDateTillValid = t.PassportDateTillValid != null ? t.PassportDateTillValid : null,
PassportCountryOfIssueFK = t.PassportCountryOfIssueFK != null ? t.PassportCountryOfIssueFK : null,
PassportPlaceOfIssue = t.PassportPlaceOfIssue != null ? t.PassportPlaceOfIssue : null,
ReferentRegionalOfficeFK = t.ReferentRegionalOfficeFK != null ? t.ReferentRegionalOfficeFK : null,
ReferentSubstationFK = t.ReferentSubstationFK != null ? t.ReferentSubstationFK : null,
ChangeDate = t.ChangeDate != null ? t.ChangeDate : null,
LegalRepresentative = t.LegalRepresentative != null ? t.LegalRepresentative : null,
Owner = t.Owner != null ? t.Owner : null,
Referent = t.Referent != null ? t.Referent : null,
Deleted = t.Deleted != null ? t.Deleted : null,
Citizenship = t1 != null && t1.Citizenship != null ? t1.Citizenship : null,
BirthCountryName = t2 != null && t2.Name != null ? t2.Name : null,
BirthCountyName = t3 != null && t3.Name != null ? t3.Name : null,
BirthCityCommunityName = t4 != null && t4.Name != null ? t4.Name : null,
ResidenceCountryName = t5 != null && t5.Name != null ? t5.Name : null,
ResidenceCountyName = t6 != null && t6.Name != null ? t6.Name : null,
ResidenceCityCommunityName = t7 != null && t7.Name != null ? t7.Name : null,
ResidencePostalOfficeName = t8 != null && t8.Name != null ? t8.Name : null,
ResidencePlaceName = t9 != null && t9.Name != null ? t9.Name : null,
PassportCountryOfIssueName = t10 != null && t10.Name != null ? t10.Name : null,
IdentityCardRegionalOfficeName = t11 != null && t11.Name != null ? t11.Name : null,
ReferentRegionalOfficeName = t12 != null && t12.Name != null ? t12.Name : null,
ReferentSubstationName = t13 != null && t13.Name != null ? t13.Name : null,
}).OrderBy(c => c.ChangeDate).ToList();
return(physicalEntityAuditView);
}
public static List <LegalEntityAuditView> GetLegalEntityAuditView(ObjectContext context, int relatedEntityPK)
{
IAuditingDetailsRepository auditingDetailsRepository = new AuditingDetailsRepository(context);
IAuditingMasterRepository auditingMasterRepository = new AuditingMasterRepository(context);
List <LegalEntityAuditView> legalEntityAuditViewList = new List <LegalEntityAuditView>();
var sessionTokens = (from am in auditingMasterRepository.GetAll().Where(c => c.TableName == "LegalEntities")
where am.RelatedEntityPK == relatedEntityPK
select new {
auditingMasterPK = am.AuditingMasterPK,
sessionToken = am.SessionToken
}).ToList();
foreach (var item in sessionTokens)
{
var record = auditingDetailsRepository.GetAuditingDetailByAuditingMasterPK(item.auditingMasterPK).ToList();
LegalEntityAuditView leav = new LegalEntityAuditView();
leav.Name = record.checkString("Name");
leav.ShortName = record.checkString("ShortName");
leav.OIB = record.checkString("OIB");
leav.MB = record.checkString("MB");
leav.MBS = record.checkString("MBS");
leav.ActivityFK = record.checkInteger("ActivityFK");
leav.ActivityDescription = record.checkString("ActivityDescription");
leav.FormFK = record.checkInteger("FormFK");
leav.FundamentalCapital = record.checkDecimal("FundamentalCapital");
leav.CountryFK = record.checkInteger("CountryFK");
leav.CountyFK = record.checkInteger("CountyFK");
leav.PlaceFK = record.checkInteger("PlaceFK");
leav.PostalOfficeFK = record.checkInteger("PostalOfficeFK");
leav.Place = record.checkString("Place");
leav.CityCommunityFK = record.checkInteger("CityCommunityFK");
leav.StreetName = record.checkString("StreetName");
leav.RegionalOfficeFK = record.checkInteger("RegionalOfficeFK");
leav.SubstationFK = record.checkInteger("SubstationFK");
leav.ReferentFK = record.checkInteger("ReferentFK");
leav.DateOfRegistration = record.checkDate("DateOfRegistration");
leav.CommercialCourtFK = record.checkInteger("CommercialCourtFK");
leav.TaxFK = record.checkInteger("TaxFK");
leav.Phone = record.checkString("Phone");
leav.Fax = record.checkString("Fax");
leav.Mobile = record.checkString("Mobile");
leav.EMail = record.checkString("EMail");
leav.FirstContactDate = record.checkDate("FirstContactDate");
leav.TouristOffice = record.checkBoolean("TouristOffice");
leav.TouristOfficeDescription = record.checkString("TouristOfficeDescription");
leav.MonumentAnnuity = record.checkBoolean("MonumentAnnuity");
leav.MonumentAnnuityDescription = record.checkString("MonumentAnnuityDescription");
leav.NumberOfEmployees = record.checkInteger("NumberOfEmployees");
leav.MIORRegistrationNumber = record.checkString("MIORRegistrationNumber");
leav.HZZOObligationNumber = record.checkString("HZZOObligationNumber");
leav.HZZOBussinesEntityCode = record.checkString("HZZOBussinesEntityCode");
leav.ChangeDate = record.checkDate("ChangeDate");
leav.Owner = record.checkBoolean("Owner");
leav.LegalEntity = record.checkBoolean("LegalEntity");
leav.Active = record.checkBoolean("Active");
legalEntityAuditViewList.Add(leav);
}
// Connect all foreign keys and return data collection as List<LegalEntityAuditView>
IFormsRepository formsRepository = new FormsRepository(context);
IQueryable <Form> formsTable = formsRepository.GetValid();
IActivitiesRepository activitiesRepository = new ActivitiesRepository(context);
IQueryable <Activity> activitiesTable = activitiesRepository.GetValid();
IRegionalOfficesRepository regionalOfficesRepository = new RegionalOfficesRepository(context);
IQueryable <RegionalOffice> regionalOfficesTable = regionalOfficesRepository.GetValid();
ISubstationsRepository substationsRepository = new SubstationsRepository(context);
IQueryable <Substation> substationsTable = substationsRepository.GetValid();
ICommercialCourtsRepository commercialCourtsRepository = new CommercialCourtsRepository(context);
IQueryable <CommercialCourt> commercialCourtsTable = commercialCourtsRepository.GetValid();
ITaxesRepository taxesRepository = new TaxesRepository(context);
IQueryable <Tax> taxesTable = taxesRepository.GetValid();
ICountriesRepository countriesRepository = new CountriesRepository(context);
IQueryable <Country> countriesTable = countriesRepository.GetValid();
ICountiesRepository countiesRepository = new CountiesRepository(context);
IQueryable <County> countiesTable = countiesRepository.GetValid();
ICitiesCommunitiesRepository citiesCommunitiesRepository = new CitiesCommunitiesRepository(context);
IQueryable <CityCommunity> cityCommunityTable = citiesCommunitiesRepository.GetValid();
IPostalOfficesRepository postalOfficesRepository = new PostalOfficesRepository(context);
IQueryable <PostalOffice> postalOfficeTable = postalOfficesRepository.GetValid();
IPlacesRepository placesRepository = new PlacesRepository(context);
IQueryable <Place> placesTable = placesRepository.GetValid();
IPhysicalEntitiesRepository physicalEntitiesRepository = new PhysicalEntitiesRepository(context);
IQueryable <PhysicalEntity> physicalEntitiesTable = physicalEntitiesRepository.GetValid();
List <LegalEntityAuditView> legalEntityAuditView =
(from t in legalEntityAuditViewList
from t1 in formsTable.Where(tbl => tbl.FormPK == t.FormFK).DefaultIfEmpty()
from t2 in activitiesTable.Where(tbl => tbl.ActivityPK == t.ActivityFK).DefaultIfEmpty()
from t3 in regionalOfficesTable.Where(tbl => tbl.RegionalOfficePK == t.RegionalOfficeFK).DefaultIfEmpty()
from t4 in substationsTable.Where(tbl => tbl.SubstationPK == t.SubstationFK).DefaultIfEmpty()
from t5 in commercialCourtsTable.Where(tbl => tbl.CommercialCourtPK == t.CommercialCourtFK).DefaultIfEmpty()
from t6 in taxesTable.Where(tbl => tbl.TaxPK == t.TaxFK).DefaultIfEmpty()
from t7 in countriesTable.Where(tbl => tbl.CountryPK == t.CountryFK).DefaultIfEmpty()
from t8 in countiesTable.Where(tbl => tbl.CountyPK == t.CountyFK).DefaultIfEmpty()
from t9 in cityCommunityTable.Where(tbl => tbl.CityCommunityPK == t.CityCommunityFK).DefaultIfEmpty()
from t10 in postalOfficeTable.Where(tbl => tbl.PostalOfficePK == t.PostalOfficeFK).DefaultIfEmpty()
from t11 in placesTable.Where(tbl => tbl.PlacePK == t.PlaceFK).DefaultIfEmpty()
from t12 in physicalEntitiesTable.Where(tbl => tbl.PhysicalEntityPK == t.ReferentFK).DefaultIfEmpty()
select new LegalEntityAuditView
{
LegalEntityPK = t.LegalEntityPK,
Name = t.Name != null ? t.Name : null,
ShortName = t.ShortName != null ? t.ShortName : null,
OIB = t.OIB != null ? t.OIB : null,
MB = t.MB != null ? t.MB : null,
MBS = t.MBS != null ? t.MBS : null,
ActivityFK = t.ActivityFK != null ? t.ActivityFK : null,
ActivityDescription = t.ActivityDescription != null ? t.ActivityDescription : null,
FormFK = t.FormFK != null ? t.FormFK : null,
FundamentalCapital = t.FundamentalCapital != null ? t.FundamentalCapital : null,
CountryFK = t.CountryFK != null ? t.CountryFK : null,
CountyFK = t.CountyFK != null ? t.CountyFK : null,
CityCommunityFK = t.CityCommunityFK != null ? t.CityCommunityFK : null,
PostalOfficeFK = t.PostalOfficeFK != null ? t.PostalOfficeFK : null,
PlaceFK = t.PlaceFK != null ? t.PlaceFK : null,
Place = t.Place != null ? t.Place : null,
StreetName = t.StreetName != null ? t.StreetName : null,
RegionalOfficeFK = t.RegionalOfficeFK != null ? t.RegionalOfficeFK : null,
SubstationFK = t.SubstationFK != null ? t.SubstationFK : null,
ReferentFK = t.ReferentFK != null ? t.ReferentFK : null,
DateOfRegistration = t.DateOfRegistration != null ? t.DateOfRegistration : null,
CommercialCourtFK = t.CommercialCourtFK != null ? t.CommercialCourtFK : null,
TaxFK = t.TaxFK != null ? t.TaxFK : null,
Phone = t.Phone != null ? t.Phone : null,
Fax = t.Fax != null ? t.Fax : null,
Mobile = t.Mobile != null ? t.Mobile : null,
EMail = t.EMail != null ? t.EMail : null,
FirstContactDate = t.FirstContactDate != null ? t.FirstContactDate : null,
TouristOffice = t.TouristOffice != null ? t.TouristOffice : null,
TouristOfficeDescription = t.TouristOfficeDescription != null ? t.TouristOfficeDescription : null,
MonumentAnnuity = t.MonumentAnnuity != null ? t.MonumentAnnuity : null,
MonumentAnnuityDescription = t.MonumentAnnuityDescription != null ? t.MonumentAnnuityDescription : null,
NumberOfEmployees = t.NumberOfEmployees != null ? t.NumberOfEmployees : null,
MIORRegistrationNumber = t.MIORRegistrationNumber != null ? t.MIORRegistrationNumber : null,
HZZOObligationNumber = t.HZZOObligationNumber != null ? t.HZZOObligationNumber : null,
HZZOBussinesEntityCode = t.HZZOBussinesEntityCode != null ? t.HZZOBussinesEntityCode : null,
ChangeDate = t.ChangeDate != null ? t.ChangeDate : null,
Owner = t.Owner != null ? t.Owner : null,
LegalEntity = t.LegalEntity != null ? t.LegalEntity : null,
Active = t.Active != null ? t.Active : null,
Deleted = t.Deleted != null ? t.Deleted : null,
FormName = t1 != null && t1.Name != null ? t1.Name : null,
ActivityName = t2 != null && t2.Name != null ? t2.Name : null,
RegionalOfficeName = t3 != null && t3.Name != null ? t3.Name : null,
SubstationName = t4 != null && t4.Name != null ? t4.Name : null,
ReferentName = t12 != null && t12.Firstname != null && t12.Lastname != null ? t12.Firstname + " " + t12.Lastname : null,
CommercialCourtName = t5 != null && t5.Name != null ? t5.Name : null,
TaxName = t6 != null && t6.Name != null ? t6.Name : null,
CountryName = t7 != null && t7.Name != null ? t7.Name : null,
CountyName = t8 != null && t8.Name != null ? t8.Name : null,
CityCommunityName = t9 != null && t9.Name != null ? t9.Name : null,
PostalOfficeName = t10 != null && t10.Name != null ? t10.Name : null,
PlaceName = t11 != null && t11.Name != null ? t11.Name : null,
}).OrderBy(c => c.ChangeDate).ToList();
return(legalEntityAuditView);
}
