public DatasetTableDetailsDataAccess(string connectionString, ZionContext dbContext = null)
{
_connectionString = connectionString;
_dbContext = dbContext;
}
public InterfaceDbDetailsDataAccess(string connectionString, ZionContext dbContext = null)
{
_connectionString = connectionString;
_dbContext = dbContext;
}
public List <CoreTableDetails> GetCoreTableDetails(string coreTableName, List <string> exceptionList, string dataDirection, bool isErrorOutbound, int datasetId)
{
List <CoreTableDetails> lstCoreTableDetails = new List <CoreTableDetails>();
try
{
using (_dbContext = string.IsNullOrEmpty(_connectionString) ? _dbContext : new ZionContext(_connectionString))
{
int datafeedId = _dbContext.DatasetMaster.FirstOrDefault(d => d.DatasetId == datasetId).DataSetTypeId.Value;
int accountId = _dbContext.DatasetMaster.Where(d => d.DatasetId == datasetId)
.Join(_dbContext.InterfaceMaster, dm => dm.InterfaceId, im => im.InterfaceId, (dm, im) => im.CustomerId ?? im.PartyId).FirstOrDefault().Value;
if (isErrorOutbound)
{
lstCoreTableDetails = _dbContext.InvalidTableMaster.Where(c => c.InvalidTableName.Trim() == coreTableName)
.Join(_dbContext.InvalidTableDetails, ms => ms.InvalidTableId, dt => dt.InvalidTableId,
(ms, dt) => new CoreTableDetails
{
CoreDetailId = dt.InvalidDetailId,
TableName = ms.InvalidTableName,
ColumnName = dt.ColumnName,
DefaultFlag = "",
PrimaryKeyFlag = false,
IsTransformed = _dbContext.DatasetTransformation.Where(d => d.TargetColumn.ToUpper() == dt.ColumnName.ToUpper() &&
d.DataDirection.ToUpper() == dataDirection.ToUpper() && d.DatafeedId == datafeedId &&
(d.AccountId == 0 || d.AccountId == accountId) &&
(d.DatasetId == 0 || d.DatasetId == datasetId)).Any()
})
.Where(c => !exceptionList.Any(s => s.Equals(c.ColumnName)))
.OrderBy(c => c.ColumnName)
.ToList();
}
else
{
lstCoreTableDetails = _dbContext.CoreTableDetails
.Select(c => new CoreTableDetails
{
CoreDetailId = c.CoreDetailId,
TableName = c.TableName,
ColumnName = c.ColumnName,
DefaultFlag = (c.DefaultFlag == null) ? "" : c.DefaultFlag,
PrimaryKeyFlag = (c.PrimaryKeyFlag == null) ? false : (bool)c.PrimaryKeyFlag,
IsTransformed = _dbContext.DatasetTransformation.Where(d => d.TargetColumn.ToUpper() == c.ColumnName.ToUpper() &&
d.DataDirection.ToUpper() == dataDirection.ToUpper() && d.DatafeedId == datafeedId &&
(d.AccountId == 0 || d.AccountId == accountId) &&
(d.DatasetId == 0 || d.DatasetId == datasetId)).Any()
})
.Where(c => c.TableName.Trim() == coreTableName &&
!exceptionList.Any(s => s.Equals(c.ColumnName))
)
.OrderBy(c => c.ColumnName)
.ToList();
}
}
}
catch
{
throw;
}
return(lstCoreTableDetails);
}
public LoginAuditDetailsDataAccess(string connectionString, ZionContext dbContext = null)
{
_connectionString = connectionString;
_dbContext = dbContext;
}
public List <LandingToCoreMapRecordModel> LoadMappingDetails(int datasetId) //earlier returned MapDetailsJsonModel
{
List <LandingToCoreMapRecordModel> queryCore;
List <LandingToCoreMapRecordModel> queryTemp;
//List<MapDetailsJsonModel> zoneRecords;
try
{
using (_dbContext = string.IsNullOrEmpty(_connectionString) ? _dbContext : new ZionContext(_connectionString))
{
int accountId = _dbContext.DatasetMaster.Where(d => d.DatasetId == datasetId)
.Join(_dbContext.InterfaceMaster, dm => dm.InterfaceId, im => im.InterfaceId, (dm, im) => im.CustomerId ?? im.PartyId).FirstOrDefault().Value;
queryCore = (from map in _dbContext.LandingToCoreMapDetails
join L in _dbContext.LandingTableDetails on map.LandingDetailId equals L.LandingDetailId
join C in _dbContext.CoreTableDetails on map.CoreDetailId equals C.CoreDetailId
join M in _dbContext.DatasetMaster on map.DatasetId equals M.DatasetId
where (map.DatasetId == datasetId)
select new LandingToCoreMapRecordModel
{
StagingCoreId = map.StagingCoreId,
DataSetId = map.DatasetId,
LandingTableName = L.TableName,
CoreTableName = C.TableName,
LandingDetailId = map.LandingDetailId,
CoreDetailId = map.CoreDetailId,
LandingColumnName = L.ColumnName,
CoreColumnName = C.ColumnName,
InterfaceId = map.InterfaceId,
Type = "Core",
CreatedBy = map.CreatedBy,
UpdatedBy = map.UpdatedBy,
IsParentFlag = map.IsParentFlag,
IsTransformed = _dbContext.DatasetTransformation.Where(d => d.DatafeedId == M.DataSetTypeId && d.DataDirection.ToUpper().Equals(M.DataDirection.ToUpper()) &&
d.TargetColumn.ToUpper().Equals(C.ColumnName.ToUpper()) && (d.AccountId == 0 || d.AccountId == accountId) && (d.DatasetId == 0 || d.DatasetId == datasetId)).Any()
}
).OrderBy(x => x.CoreTableName).ToList();
string createdBy = "";
string updatedBy = "";
if (queryCore.Count > 0)
{
createdBy = queryCore[0].CreatedBy;
updatedBy = queryCore[0].UpdatedBy;
}
queryTemp = (from map in _dbContext.LandingToCoreTempDetails
join L in _dbContext.LandingTableDetails on map.LandingDetailId equals L.LandingDetailId
join C in _dbContext.CoreTableDetails on map.CoreDetailId equals C.CoreDetailId
join M in _dbContext.DatasetMaster on map.DatasetId equals M.DatasetId
where (map.DatasetId == datasetId)
select new LandingToCoreMapRecordModel
{
StagingCoreId = map.StagingCoreId,
DataSetId = map.DatasetId,
LandingTableName = L.TableName,
CoreTableName = C.TableName,
LandingDetailId = map.LandingDetailId,
CoreDetailId = map.CoreDetailId,
LandingColumnName = L.ColumnName,
CoreColumnName = C.ColumnName,
InterfaceId = map.InterfaceId,
Type = "Temp",
CreatedBy = createdBy != ""? createdBy:map.CreatedBy,
UpdatedBy = updatedBy != ""? updatedBy : map.UpdatedBy,
IsParentFlag = map.IsParentFlag,
IsTransformed = _dbContext.DatasetTransformation.Where(d => d.DatafeedId == M.DataSetTypeId && d.DataDirection.ToUpper().Equals(M.DataDirection.ToUpper()) &&
d.TargetColumn.ToUpper().Equals(C.ColumnName.ToUpper()) && (d.AccountId == 0 || d.AccountId == accountId) && (d.DatasetId == 0 || d.DatasetId == datasetId)).Any()
}
).OrderBy(x => x.CoreTableName).ToList();
}
queryTemp.AddRange(queryCore);
}
catch
{
throw;
}
return(queryTemp); //zoneRecords
}
public IEnumerable <MappingGridModel> GetIngestionGridDetails()
{
List <MappingGridModel> lstMasterTableDetails = new List <MappingGridModel>();
try
{
using (_dbContext = string.IsNullOrEmpty(_connectionString) ? _dbContext : new ZionContext(_connectionString))
{
lstMasterTableDetails = (from map in _dbContext.LandingToCoreMapDetails
join L in _dbContext.LandingTableDetails on map.LandingDetailId equals L.LandingDetailId
join ds in _dbContext.DatasetMaster on map.DatasetId equals ds.DatasetId
join inf in _dbContext.InterfaceMaster on ds.InterfaceId equals inf.InterfaceId
join p in _dbContext.PartyAcctDetails on inf.PartyId equals p.PartyId into PartyDetails
from party in PartyDetails.DefaultIfEmpty()
join c in _dbContext.CustomerAcctDetails on inf.CustomerId equals c.CustomerId into CustDetails
from customer in CustDetails.DefaultIfEmpty()
select new MappingGridModel
{
DatasetId = map.DatasetId,
DatasetName = ds.DatasetName,
TableName = L.TableName,
InterfaceId = inf.InterfaceId,
InterfaceName = inf.InterfaceName,
CustomerId = (customer != null) ? customer.CustomerId : -1,
CustomerName = ((customer != null && customer.CustomerName != null) ? customer.CustomerName : string.Empty),
PartyId = (party != null) ? party.PartyId : -1, //((party != null && party.PartyId != null) ? party.PartyId : null),
PartyName = ((party != null && party.PartyName != null) ? party.PartyName : string.Empty),
PartyType = ((party != null && party.PartyType != null) ? party.PartyType : string.Empty),
CreatedDate = map.CreatedDate,
UpdatedDate = map.UpdatedDate,
Status = map.Status
}
).Distinct().ToList();
//DateTime? createdDate = null;
//DateTime? updatedDate = null;
//if (lstMasterTableDetails.Count > 0)
//{
// createdDate = lstMasterTableDetails[0].CreatedDate;
// updatedDate = lstMasterTableDetails[0].UpdatedDate;
//}
List <MappingGridModel> lstTempTableDetails = (from map in _dbContext.LandingToCoreTempDetails
join L in _dbContext.LandingTableDetails on map.LandingDetailId equals L.LandingDetailId
join ds in _dbContext.DatasetMaster on map.DatasetId equals ds.DatasetId
join inf in _dbContext.InterfaceMaster on ds.InterfaceId equals inf.InterfaceId
join p in _dbContext.PartyAcctDetails on inf.PartyId equals p.PartyId into PartyDetails
from party in PartyDetails.DefaultIfEmpty()
join c in _dbContext.CustomerAcctDetails on inf.CustomerId equals c.CustomerId into CustDetails
from customer in CustDetails.DefaultIfEmpty()
select new MappingGridModel
{
DatasetId = map.DatasetId,
DatasetName = ds.DatasetName,
TableName = L.TableName,
InterfaceId = inf.InterfaceId,
InterfaceName = inf.InterfaceName,
CustomerId = (customer != null) ? customer.CustomerId : -1,
CustomerName = ((customer != null && customer.CustomerName != null) ? customer.CustomerName : string.Empty),
PartyId = (party != null) ? party.PartyId : -1, //((party != null && party.PartyId != null) ? party.PartyId : null),
PartyName = ((party != null && party.PartyName != null) ? party.PartyName : string.Empty),
PartyType = ((party != null && party.PartyType != null) ? party.PartyType : string.Empty),
CreatedDate = map.CreatedDate,
UpdatedDate = map.UpdatedDate,
Status = map.Status
}
).Distinct().ToList();
lstTempTableDetails.ForEach(row =>
{
int count = lstMasterTableDetails.Where(x => x.DatasetId == row.DatasetId).Count();
if (count <= 0)
{
lstMasterTableDetails.Add(row);
}
});
}
}
catch
{
throw;
}
return((from map in lstMasterTableDetails
let updated = map.UpdatedDate ?? map.CreatedDate
orderby updated descending
select map).ToList());
}
public IngestionDataAccess(string connectionString, ZionContext dbContext = null)
{
_connectionString = connectionString;
_dbContext = dbContext;
}
public IEnumerable <DataSetGridModel> GetAllDatasetsForParty()
{
List <DataSetGridModel> datasets = new List <DataSetGridModel>();
try
{
using (_dbContext = string.IsNullOrEmpty(_connectionString) ? _dbContext : new ZionContext(_connectionString))
{
datasets = _dbContext.InterfaceMaster.Where(p => !String.IsNullOrEmpty(p.PartyId.ToString()))
.Join(_dbContext.PartyAcctDetails, a => a.PartyId,
b => b.PartyId, (a, b) => new InterfaceModel
{
InterfaceId = a.InterfaceId,
InterfaceName = a.InterfaceName,
InterfaceType = a.InterfaceType,
PartyType = b.PartyType,
CustomerId = a.CustomerId,
CustomerName = "",
PartyId = a.PartyId,
PartyName = b.PartyName
}).Join(_dbContext.DatasetMaster, d => d.InterfaceId, e => e.InterfaceId,
(d, e) => new DataSetGridModel
{
InterfaceId = d.InterfaceId,
InterfaceName = d.InterfaceName,
InterfaceType = d.InterfaceType,
PartyType = d.PartyType,
CustomerId = d.CustomerId,
CustomerName = d.CustomerName,
PartyId = d.PartyId,
PartyName = d.PartyName,
DatasetName = e.DatasetName,
DatasetId = e.DatasetId,
DataDirection = e.DataDirection,
DataSetTypeId = e.DataSetTypeId,
DataSetType = _dbContext.DataFeedType.Where(x => x.DataSetTypeId == e.DataSetTypeId).Select(y => y.Feedname).FirstOrDefault(),
CreatedDate = e.CreatedDate,
CreatedBy = e.CreatedBy,
UpdatedDate = e.UpdatedDate,
UpdatedBy = e.UpdatedBy,
Status = e.Status,
TermByAbsenceFlag = e.TermByAbsenceFlag,
ResponseEmailFlag = e.ResponseEmailFlag,
ValidationFlag = e.ValidationFlag,
TransformationFlag = e.TransformationFlag,
FrequencyDelta = _dbContext.DatasetSchedulingDetails.Where(h => h.DatasetId == e.DatasetId && h.LoadType.ToLower() == "delta").Select(x => x.RepeatBy).FirstOrDefault(),
FrequencyFull = _dbContext.DatasetSchedulingDetails.Where(h => h.DatasetId == e.DatasetId && h.LoadType.ToLower() == "full").Select(x => x.RepeatBy).FirstOrDefault(),
IsLandingTableExists = _dbContext.LandingTableDetails.Where(h => h.DatasetId == e.DatasetId).Count() > 0,
LandingTableStatusId = _dbContext.PipelineAuditDetails.Where(h => h.DatasetId == e.DatasetId && h.LoadType.Equals("One Time Load") && h.ExecutionOrder == 1).Count() == 0 ? 0 :
(string.IsNullOrEmpty(_dbContext.PipelineAuditDetails.Where(h => h.DatasetId == e.DatasetId && h.LoadType.Equals("One Time Load") && h.ExecutionOrder == 1).OrderByDescending(p => p.CreatedDate).FirstOrDefault().Status) ? 1 : 0)
}).ToList();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
return((from ds in datasets
let updated = ds.UpdatedDate ?? ds.CreatedDate
orderby updated descending
select ds
).ToList());
}
public DatasetMasterDataAccess(string connectionString, ZionContext dbContext = null)
{
_connectionString = connectionString;
_dbContext = dbContext;
}
public void UpdateTemplate(TemplateMaster template) //List<TemplateDetails> templateColumns
{
try
{
using (_dbContext = string.IsNullOrEmpty(_connectionString) ? _dbContext : new ZionContext(_connectionString))
{
var entity = _dbContext.TemplateMaster.Where(t => t.TemplateId == template.TemplateId).FirstOrDefault();
entity.TemplateName = template.TemplateName;
entity.Status = template.Status;
entity.UpdatedBy = template.UpdatedBy;
entity.UpdatedDate = template.UpdatedDate;
_dbContext.TemplateMaster.Update(entity);
#region
//code_for_update template columns
////get all existing records
//List<TemplateDetails> existingRecords = (from existCols in _dbContext.TemplateDetails
// where (existCols.TemplateId == template.TemplateId)
// select existCols).ToList();
////delete removed records
//List<TemplateDetails> deleteRecords = existingRecords.Where(ex => {
// var found = templateColumns.Where(t => t.TemplateDetailId == ex.TemplateDetailId).FirstOrDefault();
// if (found != null) return false; else return true;
//}).ToList();
////delete records from outgestion maping also
//OutgestionDataAccess outgestion = new OutgestionDataAccess(_connectionString);
//if (deleteRecords.Count > 0)
//{
// outgestion.DeleteMapRecordForTemplateId(_dbContext, deleteRecords);
// _dbContext.SaveChanges();
//}
////Delete record(s) for given template Id
//_dbContext.RemoveRange(deleteRecords);
////Modify/Add records
//templateColumns.ForEach(x => {
// var existingRecord = existingRecords.Where(y => y.TemplateDetailId == x.TemplateDetailId).FirstOrDefault();
// //look for modified column name in mapping and replace with new column data
// if (existingRecord != null) {
// var templateDetailsEntity = _dbContext.TemplateDetails.Where(t=>t.TemplateDetailId== x.TemplateDetailId).FirstOrDefault();
// if( existingRecord.ColumnName != x.ColumnName || existingRecord.MandatoryFlag != x.MandatoryFlag)
// {
// templateDetailsEntity.ColumnName = x.ColumnName;
// templateDetailsEntity.MandatoryFlag = x.MandatoryFlag;
// templateDetailsEntity.UpdatedBy = x.UpdatedBy;
// templateDetailsEntity.UpdatedDate = x.UpdatedDate;
// }
// templateDetailsEntity.Order = x.Order;
// _dbContext.TemplateDetails.Update(templateDetailsEntity);
// }
// else //Add record if not exists
// {
// TemplateDetails tempNewRecord = Extensions.Clone(x);
// tempNewRecord.TemplateDetailId = 0;
// tempNewRecord.CreatedDate = DateTime.Now;
// tempNewRecord.CreatedBy = x.UpdatedBy;
// tempNewRecord.UpdatedBy = null;
// tempNewRecord.UpdatedDate = null;
// _dbContext.Add(tempNewRecord);
// }
//});
////check for status and update mapping status accordingly
//outgestion.OutgestionStatusUpdateForTemplateId(_dbContext, template.TemplateId, template.Status);
#endregion
_dbContext.SaveChanges();
}
}
catch
{
throw;
}
}
public TemplateDataAccess(string connectionString, ZionContext dbContext = null)
{
_connectionString = connectionString;
_dbContext = dbContext;
}