Beispiel #1
0
 public DatasetTableDetailsDataAccess(string connectionString, ZionContext dbContext = null)
 {
     _connectionString = connectionString;
     _dbContext        = dbContext;
 }
Beispiel #2
0
 public InterfaceDbDetailsDataAccess(string connectionString, ZionContext dbContext = null)
 {
     _connectionString = connectionString;
     _dbContext        = dbContext;
 }
Beispiel #3
0
        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);
        }
Beispiel #4
0
 public LoginAuditDetailsDataAccess(string connectionString, ZionContext dbContext = null)
 {
     _connectionString = connectionString;
     _dbContext        = dbContext;
 }
Beispiel #5
0
        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
        }
Beispiel #6
0
        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());
        }
Beispiel #7
0
 public IngestionDataAccess(string connectionString, ZionContext dbContext = null)
 {
     _connectionString = connectionString;
     _dbContext        = dbContext;
 }
Beispiel #8
0
        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());
        }
Beispiel #9
0
 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;
 }