protected override void OnRollback(int pass)
        {
            DeliveryHistoryEntry prepareEntry = null;
            string guid = this.CurrentDelivery.DeliveryID.ToString("N");
            IEnumerable <DeliveryHistoryEntry> prepareEntries = this.CurrentDelivery.History.Where(entry => entry.Operation == DeliveryOperation.Prepared);

            if (prepareEntries != null && prepareEntries.Count() > 0)
            {
                prepareEntry = (DeliveryHistoryEntry)prepareEntries.Last();
            }
            if (prepareEntry == null)
            {
                throw new Exception(String.Format("The delivery '{0}' has never been committed so it cannot be rolled back.", guid));
            }

            _rollbackCommand             = _rollbackCommand ?? DataManager.CreateCommand(this.Options.SqlRollbackCommand, CommandType.StoredProcedure);
            _rollbackCommand.Connection  = _sqlConnection;
            _rollbackCommand.Transaction = _rollbackTransaction;

            _rollbackCommand.Parameters["@DeliveryID"].Value = guid;
            _rollbackCommand.Parameters["@TableName"].Value  = prepareEntry.Parameters[Consts.DeliveryHistoryParameters.CommitTableName];

            _rollbackCommand.ExecuteNonQuery();
            this.CurrentDelivery.IsCommited = false;
        }
示例#2
0
        protected override IEnumerable <ValidationResult> Validate()
        {
            Channel channel = new Channel();

            progress += 0.1;
            this.ReportProgress(progress);

            #region Getting Service option params
            //Getting Accounts list
            string[] accounts;
            if (this.Instance.AccountID == -1)
            {
                if (String.IsNullOrEmpty(this.Instance.Configuration.Options["AccountsList"]))
                {
                    throw new Exception("Missing Configuration option AccountsList");
                }
                accounts = this.Instance.Configuration.Options["AccountsList"].Split(',');
            }
            else
            {
                List <string> account = new List <string>()
                {
                    this.Instance.AccountID.ToString()
                };
                accounts = account.ToArray();
            }


            //Getting Table
            string comparisonTable;
            if (String.IsNullOrEmpty(this.Instance.Configuration.Options["SourceTable"]))
            {
                throw new Exception("Missing Configuration option SourceTable");
            }
            else
            {
                comparisonTable = this.Instance.Configuration.Options["SourceTable"];
            }

            //Getting Channel List
            if (String.IsNullOrEmpty(this.Instance.Configuration.Options["ChannelList"]))
            {
                throw new Exception("Missing Configuration option ChannelList");
            }
            string[] channels = this.Instance.Configuration.Options["ChannelList"].Split(',');

            //Getting TimePeriod
            DateTime fromDate, toDate;
            if ((String.IsNullOrEmpty(this.Instance.Configuration.Options["fromDate"])) && (String.IsNullOrEmpty(this.Instance.Configuration.Options["toDate"])))
            {
                fromDate = this.TargetPeriod.Start.ToDateTime();
                toDate   = this.TargetPeriod.End.ToDateTime();
            }
            else
            {
                fromDate = Convert.ToDateTime(this.Instance.Configuration.Options["fromDate"]);
                toDate   = Convert.ToDateTime(this.Instance.Configuration.Options["toDate"]);
            }
            #endregion



            if (this.Delivery == null || this.Delivery.DeliveryID.Equals(Guid.Empty))
            {
                #region Creating Delivery Search List
                List <DeliverySearchItem> deliverySearchList = new List <DeliverySearchItem>();

                while (fromDate <= toDate)
                {
                    // {start: {base : '2009-01-01', h:0}, end: {base: '2009-01-01', h:'*'}}
                    var subRange = new DateTimeRange()
                    {
                        Start = new DateTimeSpecification()
                        {
                            BaseDateTime = fromDate,
                            Hour         = new DateTimeTransformation()
                            {
                                Type = DateTimeTransformationType.Exact, Value = 0
                            },
                        },

                        End = new DateTimeSpecification()
                        {
                            BaseDateTime = fromDate,
                            Hour         = new DateTimeTransformation()
                            {
                                Type = DateTimeTransformationType.Max
                            },
                        }
                    };

                    foreach (var Channel in channels)
                    {
                        foreach (string account in accounts)
                        {
                            DeliverySearchItem delivery = new DeliverySearchItem();
                            delivery.account = new Account()
                            {
                                ID = Convert.ToInt32(account)
                            };
                            delivery.channel = new Channel()
                            {
                                ID = Convert.ToInt32(Channel)
                            };
                            delivery.targetPeriod = subRange;
                            deliverySearchList.Add(delivery);

                            progress += 0.3 * ((1 - progress) / (channels.LongLength + accounts.LongLength));
                            this.ReportProgress(progress);
                        }
                    }
                    fromDate = fromDate.AddDays(1);
                }
                #endregion
                foreach (DeliverySearchItem deliveryToSearch in deliverySearchList)
                {
                    #region Foreach

                    //Getting criterion matched deliveries
                    Delivery[] deliveriesToCheck = Delivery.GetByTargetPeriod(deliveryToSearch.targetPeriod.Start.ToDateTime(), deliveryToSearch.targetPeriod.End.ToDateTime(), deliveryToSearch.channel, deliveryToSearch.account);
                    bool       foundCommited     = false;

                    progress += 0.3 * (1 - progress);
                    this.ReportProgress(progress);

                    foreach (Delivery d in deliveriesToCheck)
                    {
                        int rollbackIndex = -1;
                        int commitIndex   = -1;

                        #region Searching and researching commited and rolledback deliveries
                        for (int i = 0; i < d.History.Count; i++)
                        {
                            if (d.History[i].Operation == DeliveryOperation.Committed)
                            {
                                commitIndex = i;
                            }
                            else if (d.History[i].Operation == DeliveryOperation.RolledBack)
                            {
                                rollbackIndex = i;
                            }
                        }

                        if (commitIndex > rollbackIndex)
                        {
                            object totalso;
                            foundCommited = true;

                            DeliveryHistoryEntry commitEntry = null;
                            IEnumerable <DeliveryHistoryEntry> processedEntries = d.History.Where(entry => (entry.Operation == DeliveryOperation.Imported));
                            if (processedEntries != null && processedEntries.Count() > 0)
                            {
                                commitEntry = (DeliveryHistoryEntry)processedEntries.Last();
                            }
                            else
                            {
                                continue;
                            }

                            if (commitEntry.Parameters.TryGetValue(Edge.Data.Pipeline.Common.Importing.Consts.DeliveryHistoryParameters.ChecksumTotals, out totalso))
                            {
                                Dictionary <string, double> totals = (Dictionary <string, double>)totalso;

                                //Check Delivery data vs OLTP
                                yield return(DeliveryDbCompare(d, totals, "OltpDB", comparisonTable));
                            }
                        }
                        #endregion
                    }


                    //could not find deliveries by user criterions
                    if (deliveriesToCheck.Length == 0)
                    {
                        yield return(new ValidationResult()
                        {
                            ResultType = ValidationResultType.Error,
                            AccountID = deliveryToSearch.account.ID,
                            TargetPeriodStart = deliveryToSearch.targetPeriod.Start.ToDateTime(),
                            TargetPeriodEnd = deliveryToSearch.targetPeriod.End.ToDateTime(),
                            Message = "Cannot find deliveries in DB",
                            ChannelID = deliveryToSearch.channel.ID,
                            CheckType = this.Instance.Configuration.Name
                        });
                    }
                    else if (!foundCommited)
                    {
                        yield return(new ValidationResult()
                        {
                            ResultType = ValidationResultType.Error,
                            AccountID = deliveryToSearch.account.ID,
                            TargetPeriodStart = deliveryToSearch.targetPeriod.Start.ToDateTime(),
                            TargetPeriodEnd = deliveryToSearch.targetPeriod.End.ToDateTime(),
                            Message = "Cannot find Commited deliveries in DB",
                            ChannelID = deliveryToSearch.channel.ID,
                            CheckType = this.Instance.Configuration.Name
                        });
                    }
                    #endregion
                }                 // End of foreach
            }
            else
            {
                //Getting current Delivery totals
                object totalso;
                DeliveryHistoryEntry commitEntry = null;
                IEnumerable <DeliveryHistoryEntry> processedEntries = this.Delivery.History.Where(entry => (entry.Operation == DeliveryOperation.Imported));
                if (processedEntries != null && processedEntries.Count() > 0)
                {
                    commitEntry = (DeliveryHistoryEntry)processedEntries.Last();
                    if (commitEntry.Parameters.TryGetValue(Edge.Data.Pipeline.Common.Importing.Consts.DeliveryHistoryParameters.ChecksumTotals, out totalso))
                    {
                        Dictionary <string, double> totals = (Dictionary <string, double>)totalso;
                        yield return(DeliveryDbCompare(this.Delivery, totals, "OltpDB", comparisonTable));
                    }
                }
            }
        }
        protected override void OnCommit(int pass)
        {
            DeliveryHistoryEntry processedEntry = this.CurrentDelivery.History.Last(entry => entry.Operation == DeliveryOperation.Imported);

            if (processedEntry == null)
            {
                throw new Exception("This delivery has not been imported yet (could not find an 'Imported' history entry).");
            }

            DeliveryHistoryEntry preparedEntry = this.CurrentDelivery.History.Last(entry => entry.Operation == DeliveryOperation.Prepared);

            if (preparedEntry == null)
            {
                throw new Exception("This delivery has not been prepared yet (could not find an 'Prepared' history entry).");
            }

            // get this from last 'Processed' history entry
            string measuresFieldNamesSQL = processedEntry.Parameters[Consts.DeliveryHistoryParameters.MeasureOltpFieldsSql].ToString();
            string measuresNamesSQL      = processedEntry.Parameters[Consts.DeliveryHistoryParameters.MeasureNamesSql].ToString();

            string tablePerfix = processedEntry.Parameters[Consts.DeliveryHistoryParameters.TablePerfix].ToString();
            string deliveryId  = this.CurrentDelivery.DeliveryID.ToString("N");


            // ...........................
            // COMMIT data to OLTP

            _commitCommand             = _commitCommand ?? DataManager.CreateCommand(this.Options.SqlCommitCommand, CommandType.StoredProcedure);
            _commitCommand.Connection  = _sqlConnection;
            _commitCommand.Transaction = _commitTransaction;

            _commitCommand.Parameters["@DeliveryFileName"].Size       = 4000;
            _commitCommand.Parameters["@DeliveryFileName"].Value      = tablePerfix;
            _commitCommand.Parameters["@CommitTableName"].Size        = 4000;
            _commitCommand.Parameters["@CommitTableName"].Value       = preparedEntry.Parameters["CommitTableName"];
            _commitCommand.Parameters["@MeasuresNamesSQL"].Size       = 4000;
            _commitCommand.Parameters["@MeasuresNamesSQL"].Value      = measuresNamesSQL;
            _commitCommand.Parameters["@MeasuresFieldNamesSQL"].Size  = 4000;
            _commitCommand.Parameters["@MeasuresFieldNamesSQL"].Value = measuresFieldNamesSQL;
            _commitCommand.Parameters["@Signature"].Size  = 4000;
            _commitCommand.Parameters["@Signature"].Value = this.CurrentDelivery.Signature;;
            _commitCommand.Parameters["@DeliveryIDsPerSignature"].Size      = 4000;
            _commitCommand.Parameters["@DeliveryIDsPerSignature"].Direction = ParameterDirection.Output;
            _commitCommand.Parameters["@DeliveryID"].Size  = 4000;
            _commitCommand.Parameters["@DeliveryID"].Value = deliveryId;


            try
            {
                _commitCommand.ExecuteNonQuery();
                //	_commitTransaction.Commit();

                string deliveryIDsPerSignature = _commitCommand.Parameters["@DeliveryIDsPerSignature"].Value.ToString();

                string[] existDeliveries;
                if ((!string.IsNullOrEmpty(deliveryIDsPerSignature) && deliveryIDsPerSignature != "0"))
                {
                    _commitTransaction.Rollback();
                    existDeliveries = deliveryIDsPerSignature.Split(',');
                    List <Delivery> deliveries = new List <Delivery>();
                    foreach (string existDelivery in existDeliveries)
                    {
                        deliveries.Add(Delivery.Get(Guid.Parse(existDelivery)));
                    }
                    throw new DeliveryConflictException(string.Format("Deliveries with the same signature are already committed in the database\n Deliveries:\n {0}:", deliveryIDsPerSignature))
                          {
                              ConflictingDeliveries = deliveries.ToArray()
                          };
                }
                else
                {
                    //already updated by sp, this is so we don't override it
                    this.CurrentDelivery.IsCommited = true;
                }
            }
            finally
            {
                this.State = DeliveryImportManagerState.Idle;
            }
        }
        protected override void OnPrepare(int pass)
        {
            DeliveryHistoryEntry processedEntry = this.CurrentDelivery.History.Last(entry => entry.Operation == DeliveryOperation.Imported);

            if (processedEntry == null)
            {
                throw new Exception("This delivery has not been imported yet (could not find an 'Imported' history entry).");
            }

            // get this from last 'Processed' history entry
            string measuresFieldNamesSQL = processedEntry.Parameters[Consts.DeliveryHistoryParameters.MeasureOltpFieldsSql].ToString();
            string measuresNamesSQL      = processedEntry.Parameters[Consts.DeliveryHistoryParameters.MeasureNamesSql].ToString();

            string tablePerfix = processedEntry.Parameters[Consts.DeliveryHistoryParameters.TablePerfix].ToString();
            string deliveryId  = this.CurrentDelivery.DeliveryID.ToString("N");

            if (pass == Prepare_PREPARE_PASS)
            {
                // ...........................
                // PREPARE data

                _prepareCommand            = _prepareCommand ?? DataManager.CreateCommand(this.Options.SqlPrepareCommand, CommandType.StoredProcedure);
                _prepareCommand.Connection = _sqlConnection;

                _prepareCommand.Parameters["@DeliveryID"].Size             = 4000;
                _prepareCommand.Parameters["@DeliveryID"].Value            = deliveryId;
                _prepareCommand.Parameters["@DeliveryTablePrefix"].Size    = 4000;
                _prepareCommand.Parameters["@DeliveryTablePrefix"].Value   = tablePerfix;
                _prepareCommand.Parameters["@MeasuresNamesSQL"].Size       = 4000;
                _prepareCommand.Parameters["@MeasuresNamesSQL"].Value      = measuresNamesSQL;
                _prepareCommand.Parameters["@MeasuresFieldNamesSQL"].Size  = 4000;
                _prepareCommand.Parameters["@MeasuresFieldNamesSQL"].Value = measuresFieldNamesSQL;
                _prepareCommand.Parameters["@CommitTableName"].Size        = 4000;
                _prepareCommand.Parameters["@CommitTableName"].Direction   = ParameterDirection.Output;

                try { _prepareCommand.ExecuteNonQuery(); }
                catch (Exception ex)
                {
                    throw new Exception(String.Format("Delivery {0} failed during Prepare.", deliveryId), ex);
                }

                this.HistoryEntryParameters[Consts.DeliveryHistoryParameters.CommitTableName] = _prepareCommand.Parameters["@CommitTableName"].Value;
            }
            else if (pass == Prepare_VALIDATE_PASS)
            {
                object totalso;

                if (processedEntry.Parameters.TryGetValue(Consts.DeliveryHistoryParameters.ChecksumTotals, out totalso))
                {
                    var totals = (Dictionary <string, double>)totalso;

                    object sql;
                    if (processedEntry.Parameters.TryGetValue(Consts.DeliveryHistoryParameters.MeasureValidateSql, out sql))
                    {
                        string measuresValidateSQL = (string)sql;
                        measuresValidateSQL = measuresValidateSQL.Insert(0, "SELECT ");
                        measuresValidateSQL = measuresValidateSQL + string.Format("\nFROM {0}_{1} \nWHERE DeliveryID=@DeliveryID:Nvarchar", tablePerfix, ValidationTable);

                        SqlCommand validateCommand = DataManager.CreateCommand(measuresValidateSQL);
                        validateCommand.Connection = _sqlConnection;
                        validateCommand.Parameters["@DeliveryID"].Value = this.CurrentDelivery.DeliveryID.ToString("N");
                        using (SqlDataReader reader = validateCommand.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                var results = new StringBuilder();
                                foreach (KeyValuePair <string, double> total in totals)
                                {
                                    if (reader[total.Key] is DBNull)
                                    {
                                        if (total.Value == 0)
                                        {
                                            Log.Write(string.Format("[zero totals] {0} has no data or total is 0 in table {1} for target period {2}", total.Key, ValidationTable, CurrentDelivery.TargetPeriod), LogMessageType.Information);
                                        }
                                        else
                                        {
                                            results.AppendFormat("{0} is null in table {1}\n but {2} in measure {3}", total.Key, ValidationTable, total.Key, total.Value);
                                        }
                                    }
                                    else
                                    {
                                        double val  = Convert.ToDouble(reader[total.Key]);
                                        double diff = Math.Abs((total.Value - val) / total.Value);
                                        if (diff > this.Options.CommitValidationThreshold)
                                        {
                                            results.AppendFormat("{0}: processor totals = {1}, {2} table = {3}\n", total.Key, total.Value, ValidationTable, val);
                                        }
                                        else if (val == 0 && total.Value == 0)
                                        {
                                            Log.Write(string.Format("[zero totals] {0} has no data or total is 0 in table {1} for target period {2}", total.Key, ValidationTable, CurrentDelivery.TargetPeriod), LogMessageType.Information);
                                        }
                                    }
                                }
                                if (results.Length > 0)
                                {
                                    throw new Exception("Commit validation (checksum) failed:\n" + results.ToString());
                                }
                            }
                            else
                            {
                                throw new Exception(String.Format("Commit validation (checksum) did not find any data matching this delivery in {0}.", ValidationTable));
                            }
                        }
                    }
                }
            }
        }