/// <summary>
 /// Method to add a TrancheEvent into database. We use the NullableTypes to make the correspondance between
 /// nullable int, decimal and double types in database and our own objects
 /// </summary>
 /// <param name="trancheEvent">TrancheEvent Object</param>
 /// <returns>The id of the Tranche Event which has been added</returns>
 public int Add(TrancheEvent trancheEvent)
 {
     const string sqlText = @"
         INSERT INTO [TrancheEvents]
                    ( [id]
                     ,[interest_rate]
                     ,[amount]
                     ,[maturity]
                     ,[start_date]
                     ,[applied_new_interest])
                     VALUES
                     (@Id,
                      @InterestRate,
                      @Amount,
                      @Maturity,
                      @StartDate,
                      @applied_new_interest)
         SELECT SCOPE_IDENTITY()";
     using (SqlConnection conn = GetConnection())
     using (var cmd = new OpenCbsCommand(sqlText, conn))
     {
         SetTrancheEvent(cmd, trancheEvent);
         return int.Parse(cmd.ExecuteScalar().ToString());
     }
 }

 public void Delete(SqlTransaction pSqlTransaction)
 {
     const string sqltext = "DELETE FROM LoanScale";
     using (OpenCbsCommand delete = new OpenCbsCommand(sqltext, pSqlTransaction.Connection, pSqlTransaction))
     {
         delete.ExecuteNonQuery();
     }
 }

 public void DeleteAllProvisioningRules(SqlTransaction sqlTransac)
 {
     const string sqlText = "DELETE FROM ProvisioningRules";
     using (OpenCbsCommand delete = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
     {
         delete.ExecuteNonQuery();
     }
 }

        public void InsertLoanScale(LoanScaleRate pLoanScaleRate, SqlTransaction pSqlTransaction)
        {
            const string sqlText = @"INSERT INTO LoanScale(id,ScaleMin, ScaleMax) VALUES(@number,@Min, @Max)";

            using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlTransaction.Connection, pSqlTransaction))
            {
                SetLoanScale(insert, pLoanScaleRate);
                insert.ExecuteNonQuery();
            }
        }

 public void Add(DateTime pDate, double pRate, Currency pCurrency)
 {
     const string q = "INSERT INTO [ExchangeRates] ([exchange_date], [exchange_rate],[currency_id]) " +
                      "VALUES(@date,@rate,@currency)";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         SetExchangeRate(c, pDate.Date, pRate,pCurrency);
         c.ExecuteNonQuery();
     }
 }

 public void DeleteStandardBooking(int id)
 {
     const string sqlText = @"DELETE FROM StandardBookings WHERE Id = @Id";
     using (SqlConnection conn = GetConnection())
     {
         using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn))
         {
             insertIntoTable.AddParam("@Id", id);
             insertIntoTable.ExecuteNonQuery();
         }
     }
 }

 public void CreateStandardBooking(Booking booking)
 {
     const string sqlText = @"INSERT INTO StandardBookings([Name], debit_account_id, credit_account_id)
                              VALUES (@name, @debit_account_id, @credit_account_id)";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn))
         {
             insertIntoTable.AddParam("@name", booking.Name);
             insertIntoTable.AddParam("@debit_account_id", booking.DebitAccount.Id);
             insertIntoTable.AddParam("@credit_account_id", booking.CreditAccount.Id);
             insertIntoTable.ExecuteNonQuery();
         }
 }

 public int Add(Currency pCurrency, SqlTransaction t)
 {
     const string q = @"INSERT INTO [Currencies] ([name], [code], [is_pivot], [is_swapped],use_cents)
                             VALUES(@name, @code, @is_pivot, @is_swapped,@use_cents) SELECT SCOPE_IDENTITY()";
     using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
     {
         c.AddParam("@name", pCurrency.Name);
         c.AddParam("@code", pCurrency.Code);
         c.AddParam("@is_pivot", pCurrency.IsPivot);
         c.AddParam("@is_swapped", pCurrency.IsSwapped);
         c.AddParam("@use_cents", pCurrency.UseCents);
         return int.Parse(c.ExecuteScalar().ToString());
     }
 }

        public double GetMostRecentlyRate(DateTime pDate, Currency pCurrency)
        {
            const string q = @"SELECT TOP 1 exchange_rate
                                    FROM ExchangeRates
                                    WHERE exchange_date <= @date AND currency_id = @currency
                                    ORDER BY exchange_date DESC";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                c.AddParam("@currency",pCurrency.Id);
                c.AddParam("@date", pDate);
                return Convert.ToDouble(c.ExecuteScalar());
            }
        }

 public int GetContractQuantityByCurrencyId(int currencyId)
 {
     const string q = @"
                     SELECT COUNT(Credit.id) AS contract_quantity
                     FROM [dbo].[Currencies] AS Cur
                     INNER JOIN Packages AS Pack ON Pack.currency_id=Cur.id
                     INNER JOIN Credit ON Credit.package_id=Pack.id
                     WHERE [email protected]_id";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@currency_id", currencyId);
         return (int)c.ExecuteScalar();
     }
 }

        public static void AddDatabaseToAccounts(string pAccountName, string pDatabaseName, string pLogin, string pPassword, SqlConnection pSqlConnection)
        {
            const string sqlText = @"INSERT INTO [Accounts].[dbo].[SqlAccounts]
                                   ([account_name],[database_name],[user_name],[password],[active])
                                    VALUES (@accountName, @databaseName, @login, @password,1)";

            using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlConnection))
            {
                insert.AddParam("@accountName",  pAccountName);
                insert.AddParam("@databaseName",  pDatabaseName);
                insert.AddParam("@login",  pLogin);
                insert.AddParam("@password", pPassword);

                insert.ExecuteNonQuery();
            }
        }

 public void AddCreditInsuranceEvent(CreditInsuranceEvent pEvent, SqlTransaction pSqlTransac)
 {
     const string q = @"
                 INSERT INTO [dbo].[CreditInsuranceEvents]
                ([id]
                ,[commission]
                 ,[principal])
                  VALUES
                (@id
                ,@commission
                ,@principal)";
     using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
     {
         SetCreditInsuranceEvent(pEvent, c);
         c.ExecuteNonQuery();
     }
 }

        public void AddParameter(DictionaryEntry entry)
        {
            ApplicationSettings.GetInstance(_user.Md5).AddParameter(entry.Key.ToString(), entry.Value);

            string sqlText = "INSERT INTO [GeneralParameters]([key], [value])" +
                " VALUES(@name,@value)";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn))
            {
                c.AddParam("@name", entry.Key.ToString());

                if (entry.Value != null)
                    c.AddParam("@value", entry.Value.ToString());
                else
                    c.AddParam("@value", null);

                c.ExecuteNonQuery();
            }
        }

        public void AddProvisioningRate(ProvisioningRate pR, SqlTransaction sqlTransac)
        {
            const string sqlText = @"INSERT INTO ProvisioningRules(
                                        id,
                                        number_of_days_min,
                                        number_of_days_max,
                                        provisioning_value)
                                    VALUES(
                                      @number,
                                      @numberOfDaysMin,
                                      @numberOfDaysMax,
                                      @provisioningPercentage)
                                   SELECT SCOPE_IDENTITY()";

            using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
            {
                SetProvisioningRate(insert, pR);
                insert.ExecuteScalar();
            }
        }

        public void SelectLoanScales()
        {
            LoanScaleTable loanscaleTable = LoanScaleTable.GetInstance(_user);
            const string sqlText = @"SELECT id, ScaleMin, ScaleMax
                                     FROM LoanScale";

            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
                {
                    using (OpenCbsReader reader = select.ExecuteReader())
                    {
                        if (reader.Empty) return;
                        while (reader.Read())
                        {
                            loanscaleTable.AddLoanScaleRate(GetLoanScale(reader));
                        }
                    }
                }
            }
        }

        public static bool IsOctopusDatabase(string dbName, SqlConnection connection)
        {
            string query =
                string.Format("USE [{0}] SELECT 1 FROM sys.tables WHERE NAME = 'TechnicalParameters'", dbName);

            using (OpenCbsCommand command = new OpenCbsCommand(query, connection))
                return command.ExecuteScalar() != null;
        }

 public static List<SqlDatabaseSettings> GetOpenCbsDatabases(SqlConnection connection)
 {
     var result = new List<SqlDatabaseSettings>();
     var query = ReadQuery("OpenCBSDatabases.sql");
     query = string.Format(query, WindowsIdentity.GetCurrent().Name);
     using (var command = new OpenCbsCommand(query, connection))
     using (var reader = command.ExecuteReader())
     {
         while (reader.Read())
         {
             result.Add(new SqlDatabaseSettings
             {
                 Name = reader.GetString("Name"),
                 BranchCode = reader.GetString("BranchCode"),
                 DataFileSize = reader.GetInt("DataFileSize"),
                 LogFileSize = reader.GetInt("LogFileSize"),
                 Version = reader.GetString("Version"),
             });
         }
     }
     return result;
 }

        public static string GetObjectDropScript(string db, string name, SqlConnection conn)
        {
            string q = @"SELECT xtype
            FROM {0}..sysobjects
            WHERE name = @name";
            q = string.Format(q, db);

            OpenCbsCommand c = new OpenCbsCommand(q, conn);
            c.AddParam("@name", name);
            string xtype = c.ExecuteScalar().ToString().Trim();

            string xtypeObject = string.Empty;
            switch (xtype)
            {
                case "FN":
                case "IF":
                case "TF":
                    xtypeObject = "FUNCTION";
                    break;

                case "P":
                    xtypeObject = "PROCEDURE";
                    break;

                case "V":
                    xtypeObject = "VIEW";
                    break;

                default:
                    Debug.Fail("Cannot be here.");
                    break;
            }

            const string retval = @"IF  EXISTS (
                SELECT *
                FROM sys.objects
                WHERE object_id = OBJECT_ID(N'dbo.{0}') AND type = N'{1}'
            )
            DROP {2} [dbo].[{0}]";

            return string.Format(retval, name, xtype, xtypeObject);
        }

 public static string GetObjectCreateScript(string db, string name, SqlConnection conn)
 {
     string q = string.Format("{0}..sp_helptext", db);
     OpenCbsCommand cmd = new OpenCbsCommand(q, conn).AsStoredProcedure();
     cmd.AddParam("@objname", name);
     var buffer = new StringBuilder(2048);
     using (OpenCbsReader reader = cmd.ExecuteReader())
     {
         if (null == reader) return string.Empty;
         while (reader.Read())
         {
             buffer.Append(reader.GetString("Text"));
         }
     }
     return buffer.ToString();
 }

        public static List<SqlAccountsDatabase> GetListDatabasesIntoAccounts(SqlConnection pSqlConnection)
        {
            List<SqlAccountsDatabase> databases = new List<SqlAccountsDatabase>();

            const string sqlText = @"SELECT *
                                     FROM [Accounts].[dbo].[SqlAccounts]";

            if (pSqlConnection.State == ConnectionState.Closed)
                pSqlConnection.Open();

            using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
            {
                using (OpenCbsReader reader = select.ExecuteReader())
                {

                    if (reader == null || reader.Empty) return databases;

                    while (reader.Read())
                    {
                        databases.Add(new SqlAccountsDatabase
                        {
                            Account = reader.GetString("account_name"),
                            Database = reader.GetString("database_name"),
                            Login = reader.GetString("user_name"),
                            Password = reader.GetString("password"),
                            Active = reader.GetBool("active")
                        });
                    }
                }
            }

            foreach (SqlAccountsDatabase db in databases)
            {
                db.Version = GetDatabaseVersion(db.Database, pSqlConnection);
            }

            return databases;
        }