///// <summary> ///// This method Fill the instance of the ProvisioningTable object accessed by singleton ///// </summary> public List <ProvisioningRate> SelectAllProvisioningRates() { List <ProvisioningRate> list = new List <ProvisioningRate>(); const string sqlText = @"SELECT id, number_of_days_min, number_of_days_max, provisioning_value FROM ProvisioningRules"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(list); } while (reader.Read()) { list.Add(GetProvisionningRate(reader)); } return(list); } } } }
private static Dictionary <string, char> GetFilelist(string file, SqlConnection connection) { //list of files of a backup. Dictionary <string, char> databaseFiles = new Dictionary <string, char>(); string q = @"RESTORE FILELISTONLY FROM DISK = '{0}'"; q = string.Format(q, file); OpenCbsCommand cmd = new OpenCbsCommand(q, connection); using (OpenCbsReader reader = cmd.ExecuteReader()) { if (null == reader || reader.Empty) { return(null); } while (reader.Read()) { string logicalName = reader.GetString("LogicalName"); char type = reader.GetString("Type").ToCharArray()[0]; databaseFiles.Add(logicalName, type); } } return(databaseFiles); }
public static string GetDatabaseVersion(string database, SqlConnection connection) { if (connection == null) { throw new ArgumentException("Both connection and transaction cannot be null."); } if (!IsOctopusDatabase(database, connection)) { return(string.Empty); } string sqlText = string.Format( "USE [{0}] SELECT [value] FROM [TechnicalParameters] WHERE [name]='version'", database); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, connection)) using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(string.Empty); } reader.Read(); return(reader.GetString("value")); } }
/// <summary> /// Select all events for selected Funding Line /// </summary> /// <param name="fundingLine">funding line </param> /// <returns>list of Funding Line events</returns> public List <FundingLineEvent> SelectFundingLineEvents(FundingLine fundingLine) { List <FundingLineEvent> list = new List <FundingLineEvent>(); const string sqlText = @"SELECT [id], [code], [amount], [direction], [fundingline_id], [deleted], [creation_date], [type] FROM [FundingLineEvents] WHERE fundingline_id = @fundingline_id ORDER BY creation_date DESC, id DESC"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn)) { cmd.AddParam("@fundingline_id", fundingLine.Id); using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader == null || reader.Empty) { return(list); } { while (reader.Read()) { FundingLineEvent fundingLineEvent = new FundingLineEvent { Id = reader.GetInt("id"), Code = reader.GetString("code"), Amount = reader.GetMoney("amount"), Movement = ((OBookingDirections) reader.GetSmallInt("direction")), IsDeleted = reader.GetBool("deleted"), CreationDate = reader.GetDateTime("creation_date"), Type = ((OFundingLineEventTypes) reader.GetSmallInt("type")), FundingLine = fundingLine }; list.Add(fundingLineEvent); } } } return(list); } }
public AccountingRuleCollection SelectAllByEventType(string type) { const string sqlText = @"SELECT id, rule_type FROM [AccountingRules] WHERE deleted = 0 AND (event_type = @event_type OR @event_type = '')"; AccountingRuleCollection rules = new AccountingRuleCollection(); using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@event_type", type); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(rules); } while (reader.Read()) { if (reader.GetChar("rule_type") == 'C') { rules.Add(new ContractAccountingRule { Id = reader.GetInt("id") }); } } } } } List <Account> accounts = _accountManager.SelectAllAccounts(); for (int i = 0; i < rules.Count; i++) { if (rules[i] is ContractAccountingRule) { rules[i] = SelectContractAccountingRule(rules[i].Id); } else { rules[i] = SelectFundingLineAccountingRule(rules[i].Id); } rules[i].DebitAccount = accounts.FirstOrDefault(item => item.Id == rules[i].DebitAccount.Id); rules[i].CreditAccount = accounts.FirstOrDefault(item => item.Id == rules[i].CreditAccount.Id); } return(rules); }
/// <summary> /// Create at "SetupPath" a OCTOPUS_('version').xml who contains diagram of the current database /// </summary> public static void SaveDatabaseDiagramsInXml(bool pDestination, string pDatabaseName, SqlConnection pSqlConnection) { string sql = string.Format(@"USE [{0}] SELECT table_name, column_name, data_type, is_nullable FROM information_schema.columns WHERE(table_name IN (SELECT table_name FROM Information_Schema.Tables WHERE Table_Type = 'Base Table')) ORDER BY table_name" , pDatabaseName); OpenCbsCommand command = new OpenCbsCommand(sql, pSqlConnection); string path = !pDestination ? Path.Combine(UserSettings.GetUpdatePath, string.Format(SCHEMA_FILE_NAME, TechnicalSettings.SoftwareVersion)) : Path.Combine(UserSettings.GetUpdatePath, string.Format(LOCAL_SCHEMA_FILE_NAME, TechnicalSettings.SoftwareVersion)); XmlTextWriter xml = new XmlTextWriter(path, Encoding.Unicode); xml.WriteStartDocument(); xml.WriteStartElement("Database"); xml.WriteAttributeString("Version", TechnicalSettings.SoftwareVersion); xml.WriteAttributeString("SystemDate", DateTime.Today.ToString("dd MM yyyy")); bool firstTable = true; using (OpenCbsReader reader = command.ExecuteReader()) { string tableName = ""; while (reader.Read()) { string tableNameTemp = reader.GetString("table_name"); if ((tableNameTemp != "sysdiagrams") && (tableNameTemp != "dtproperties") && (!tableNameTemp.StartsWith("Tconso_SP_Octopus_Consolidation_"))) { if (tableName != tableNameTemp && firstTable) { xml.WriteStartElement("table"); tableName = tableNameTemp; xml.WriteAttributeString("name", tableNameTemp); firstTable = false; } else if (tableName != tableNameTemp) { xml.WriteEndElement(); //table xml.WriteStartElement("table"); tableName = tableNameTemp; xml.WriteAttributeString("name", tableNameTemp); } xml.WriteStartElement("column"); xml.WriteAttributeString("name", reader.GetString("column_name")); xml.WriteAttributeString("type", reader.GetString("data_type")); xml.WriteAttributeString("is_nullable", reader.GetString("is_nullable").Trim().ToUpper()); xml.WriteEndElement(); //column } } } xml.WriteEndElement(); //database xml.Close(); }
public List <ExchangeRate> SelectRatesByDate(DateTime beginDate, DateTime endDate) { const string q = @"SELECT exchange_date, exchange_rate, currency_id, is_pivot, is_swapped, name, code FROM ExchangeRates INNER JOIN Currencies ON ExchangeRates.currency_id = Currencies.id WHERE exchange_date BETWEEN @beginDate AND @endDate"; List <ExchangeRate> rates; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@beginDate", beginDate.Date); c.AddParam("@endDate", endDate.Date); using (OpenCbsReader r = c.ExecuteReader()) { if (r == null || r.Empty) { return(null); } rates = new List <ExchangeRate>(); while (r.Read()) { ExchangeRate newRate = new ExchangeRate { Date = r.GetDateTime("exchange_date"), Rate = r.GetDouble("exchange_rate"), Currency = new Currency { Id = r.GetInt("currency_id"), IsPivot = r.GetBool("is_pivot"), IsSwapped = r.GetBool("is_swapped"), Name = r.GetString("name"), Code = r.GetString("code") } }; rates.Add(newRate); } } } return(rates); }
private static string BackupToFile(string fileName, string database, SqlConnection connection) { string backupDirectory; if (TechnicalSettings.UseDemoDatabase) { backupDirectory = @"C:\Users\Public"; } else { // To perform a backup we first need to get the appropriate backup folder, which is a bit tricky. // First, we need to get the service name. const string query = "SELECT @@SERVICENAME AS name"; OpenCbsCommand cmd = new OpenCbsCommand(query, connection); string serviceName = string.Empty; using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader.Empty) { return(null); } reader.Read(); serviceName = reader.GetString("name"); } // Then get the instance name from the registry const string sqlServerKey = @"SOFTWARE\Microsoft\Microsoft SQL Server"; string key = string.Format(@"{0}\Instance Names\SQL", sqlServerKey); string instanceName = ExtendedRegistry.GetKeyValue(key, serviceName); if (string.IsNullOrEmpty(instanceName)) { return(null); } // Finally, get the backup directory key = string.Format(@"{0}\{1}\MSSQLServer", sqlServerKey, instanceName); backupDirectory = ExtendedRegistry.GetKeyValue(key, "BackupDirectory"); if (string.IsNullOrEmpty(backupDirectory)) { return(null); } } string path = Path.Combine(backupDirectory, fileName); BackupToFileImpl(path, database, connection); return(path); }
public List <QueuedEmail> SelectAll() { List <QueuedEmail> qeuedEmails = new List <QueuedEmail>(); const string q = @"SELECT * FROM dbo.QueuedEmails where Deleted <> 1"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(qeuedEmails); } while (r.Read()) { var queuedEmail = new QueuedEmail { Id = r.GetInt("Id"), Priority = r.GetInt("Priority"), From = r.GetString("From"), FromName = r.GetString("FromName"), To = r.GetString("To"), ToName = r.GetString("ToName"), ReplyTo = r.GetString("ReplyTo"), ReplyToName = r.GetString("ReplyToName"), CC = r.GetString("CC"), Bcc = r.GetString("Bcc"), Body = r.GetString("Body"), CreatedOnUtc = r.GetDateTime("CreatedOnUtc"), SentOnUtc = r.GetNullDateTime("SentOnUtc"), EmailAccountId = r.GetInt("EmailAccountId"), SentTries = r.GetInt("SentTries"), Subject = r.GetString("Subject"), Deleted = r.GetBool("Deleted"), }; if (queuedEmail.EmailAccountId > 0) { EmailAccountManager _emailAccountManager = new EmailAccountManager(user); queuedEmail.EmailAccount = _emailAccountManager.SelectById(queuedEmail.EmailAccountId); } qeuedEmails.Add(queuedEmail); } } return(qeuedEmails); }
public Guid?GetGuid() { string query = "SELECT [value] FROM TechnicalParameters WHERE [name] = 'GUID'"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(query, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (!r.Empty) { r.Read(); string temp = r.GetString("value"); return(new Guid(temp)); } } return(null); }
public IAccountingRule Select(int pId) { const string sqlText = @"SELECT rule_type FROM [AccountingRules] WHERE deleted = 0 AND id = @id"; IAccountingRule rule = new ContractAccountingRule(); using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@id", pId); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(null); } reader.Read(); if (reader.GetChar("rule_type") == 'C') { rule = new ContractAccountingRule { Id = pId } } ; } } } if (rule is ContractAccountingRule) { rule = SelectContractAccountingRule(rule.Id); } List <Account> accounts = _accountManager.SelectAllAccounts(); rule.DebitAccount = accounts.FirstOrDefault(item => item.Id == rule.DebitAccount.Id); rule.CreditAccount = accounts.FirstOrDefault(item => item.Id == rule.CreditAccount.Id); return(rule); }
public static string GetDatabaseBranchCode(string pDatabaseName, SqlConnection pSqlConnection) { string sqlText = string.Format("USE [{0}] SELECT [value] FROM [GeneralParameters] WHERE [key]='BRANCH_CODE'", pDatabaseName); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(string.Empty); } reader.Read(); return(reader.GetString("value")); } } }
/// <summary> /// Fills General Settings with values from database /// </summary> /// public void FillGeneralSettings() { ApplicationSettings.GetInstance(_user.Md5).DeleteAllParameters(); string sqlText = "SELECT UPPER([key]) as [key], [value] FROM GeneralParameters"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn)) using (OpenCbsReader r = c.ExecuteReader()) { while (r.Read()) { ApplicationSettings.GetInstance(_user.Md5).AddParameter(r.GetString("key"), r.GetString("value")); } } }
public MessageTemplate SelectByName(string name) { string q = @"SELECT * FROM dbo.MessageTemplates WHERE Name LIKE '%{0}%' AND Deleted <> 1"; q = string.Format(q, name); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(null); } if (!r.Read()) { return(null); } var messageTemplate = new MessageTemplate { Id = r.GetInt("Id"), Name = r.GetString("Name"), BccEmailAddresses = r.GetString("BccEmailAddresses"), Subject = r.GetString("Subject"), Body = r.GetString("Body"), EmailBody = r.GetString("EmailBody"), SendEmail = r.GetNullBool("SendEmail"), SendSMS = r.GetNullBool("SendSMS"), IsActive = r.GetBool("IsActive"), EmailAccountId = r.GetInt("EmailAccountId"), IsDefault = r.GetBool("IsDefault"), Deleted = r.GetBool("Deleted"), }; if (messageTemplate.EmailAccountId > 0) { EmailAccountManager _emailAccountManager = new EmailAccountManager(user); messageTemplate.EmailAccount = _emailAccountManager.SelectById(messageTemplate.EmailAccountId); } return(messageTemplate); } }
public int SelectFundingLineEventId(FundingLineEvent pFundingLineEvent, SqlTransaction sqlTransac, bool includeDeleted) { int id = -1; string sqlText = @"SELECT [id] FROM [FundingLineEvents] WHERE [code] = @code AND [amount] = @amount AND [direction] = @direction AND [type] = @type AND [fundingline_id] = @fundinglineid"; if (!includeDeleted) { sqlText += " and deleted = @deleted"; } OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac); cmd.AddParam("@code", pFundingLineEvent.Code); cmd.AddParam("@amount", pFundingLineEvent.Amount); cmd.AddParam("@direction", (int)pFundingLineEvent.Movement); cmd.AddParam("@type", (int)pFundingLineEvent.Type); cmd.AddParam("@fundinglineid", pFundingLineEvent.FundingLine.Id); if (!includeDeleted) { cmd.AddParam("@deleted", pFundingLineEvent.IsDeleted); } using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader != null) { if (!reader.Empty) { reader.Read(); id = reader.GetInt("id"); } } } return(id); }
public object SelectParameterValue(string key) { string sqlText = "SELECT [value] FROM GeneralParameters WHERE [key] = @name"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn)) { c.AddParam("@name", key); using (OpenCbsReader r = c.ExecuteReader()) { if (!r.Empty) { r.Read(); return(r.GetString("value")); } } } return(null); }
public List <MessageTemplate> SelectAll() { List <MessageTemplate> messageTemplates = new List <MessageTemplate>(); const string q = @"SELECT * FROM dbo.MessageTemplates where Deleted <> 1"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(messageTemplates); } while (r.Read()) { var messageTemplate = new MessageTemplate { Id = r.GetInt("Id"), Name = r.GetString("Name"), BccEmailAddresses = r.GetString("BccEmailAddresses"), Subject = r.GetString("Subject"), Body = r.GetString("Body"), EmailBody = r.GetString("EmailBody"), SendEmail = r.GetNullBool("SendEmail"), SendSMS = r.GetNullBool("SendSMS"), IsActive = r.GetBool("IsActive"), EmailAccountId = r.GetInt("EmailAccountId"), IsDefault = r.GetBool("IsDefault"), Deleted = r.GetBool("Deleted"), }; if (messageTemplate.EmailAccountId > 0) { EmailAccountManager _emailAccountManager = new EmailAccountManager(user); messageTemplate.EmailAccount = _emailAccountManager.SelectById(messageTemplate.EmailAccountId); } messageTemplates.Add(messageTemplate); } } return(messageTemplates); }
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); }
public static string GetDatabaseSize(string pDatabase, SqlConnection pSqlConnection) { string sql = string.Format("USE [{0}] EXEC sp_spaceused", pDatabase); using (OpenCbsCommand cmd = new OpenCbsCommand(sql, pSqlConnection)) { using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader.Empty) { return(string.Empty); } if (reader.Read()) { return(reader.GetString("database_size")); } return(string.Empty); } } }
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 Currency SelectCurrencyByName(string pName) //{ // const string q = @"SELECT id, name, code, is_pivot, is_swapped FROM [Currencies] where name = @name"; // using (SqlConnection conn = GetConnection()) // using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) // { // c.AddParam("@name", pName); // using (OpenCbsReader r = c.ExecuteReader()) // { // if (r == null || r.Empty) return null; // r.Read(); // return new Currency // { // Id = r.GetInt("id", reader), // Code = r.GetString("code", reader), // Name = r.GetString("name", reader), // IsPivot = r.GetBool("is_pivot", reader), // IsSwapped = r.GetBool("is_swapped", reader) // }; // } // } //} public bool IsThisCurrencyAlreadyExist(string pCode, string pName) { const string q = @"SELECT * FROM [Currencies] where name = @name AND code = @code"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@code", pCode); c.AddParam("@name", pName); using (OpenCbsReader r = c.ExecuteReader()) { if (r == null || r.Empty) { return(false); } return(true); } } }
public static string GetDatabaseVersion(string pDatabase, SqlConnection pSqlConnection) { if (!IsOctopusDatabase(pDatabase, pSqlConnection)) { return(string.Empty); } string sqlText = string.Format( "USE [{0}] SELECT [value] FROM [TechnicalParameters] WHERE [name]='version'", pDatabase); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(string.Empty); } reader.Read(); return(reader.GetString("value")); } }
private FundingLineAccountingRule SelectFundingLineAccountingRule(int pId) { const string sqlText = @"SELECT AccountingRules.id, AccountingRules.debit_account_number_id, AccountingRules.credit_account_number_id, AccountingRules.booking_direction, FundingLineAccountingRules.funding_line_id FROM AccountingRules INNER JOIN FundingLineAccountingRules ON AccountingRules.id = FundingLineAccountingRules.id WHERE AccountingRules.id = @id"; FundingLineAccountingRule rule; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@id", pId); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(null); } reader.Read(); rule = _getFundingLineAccountingRule(reader); } } } if (rule.FundingLine != null) { rule.FundingLine = _fundingLineManager.SelectFundingLineById(rule.FundingLine.Id, false); } return(rule); }
public EmailAccount SelectById(int Id) { const string q = @"SELECT * FROM dbo.EmailAccounts where Id = @Id AND Deleted <> 1"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@Id", Id); using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(null); } if (!r.Read()) { return(null); } return(new EmailAccount { Id = r.GetInt("Id"), Email = r.GetString("Email"), DisplayName = r.GetString("DisplayName"), Host = r.GetString("Host"), Port = r.GetInt("Port"), Username = r.GetString("Username"), Password = r.GetString("Password"), EnableSsl = r.GetBool("EnableSsl"), UseDefaultCredentials = r.GetBool("UseDefaultCredentials"), IsDefaultEmailAccount = r.GetBool("IsDefaultEmailAccount"), Deleted = r.GetBool("Deleted"), }); } } }
public static string GetDatabaseBranchCodeFromBranches(string pDatabaseName, SqlConnection pSqlConnection) { string sqlText = string.Format("USE [{0}] SELECT TOP 1 [code] FROM [Branches] WHERE [deleted] = 0", pDatabaseName); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(string.Empty); } reader.Read(); string code = reader.GetString("code"); if (string.IsNullOrEmpty(code)) { throw new ApplicationException( string.Format("Emty or no branch code found for database: {0}.", pDatabaseName)); } return(code); } } }
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 List <QueuedSMS> SelectAll() { List <QueuedSMS> qeuedSMSs = new List <QueuedSMS>(); const string q = @"SELECT * FROM dbo.QueuedSMS where Deleted <> 1"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(qeuedSMSs); } while (r.Read()) { var queuedSMS = new QueuedSMS { Id = r.GetInt("Id"), From = r.GetString("From"), Recipient = r.GetString("Recipient"), RecipientId = r.GetNullInt("RecipientId"), ContractId = r.GetNullInt("ContractId"), Charged = r.GetNullBool("Charged"), Message = r.GetString("Message"), CreatedOnUtc = r.GetDateTime("CreatedOnUtc"), SentOnUtc = r.GetNullDateTime("SentOnUtc"), SentTries = r.GetInt("SentTries"), Response = r.GetString("Response"), Deleted = r.GetBool("Deleted"), }; qeuedSMSs.Add(queuedSMS); } } return(qeuedSMSs); }
public List <Account> SelectAllAccountsWithoutTeller(int accountId) { List <Account> list = new List <Account>(); const string sqlText = @"SELECT id, account_number, label, debit_plus, type_code, account_category_id, type, parent_account_id, lft, rgt FROM ChartOfAccounts WHERE id NOT IN (SELECT account_id FROM dbo.Tellers WHERE deleted = 0) OR id = @id ORDER BY account_number"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@id", accountId); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader == null || reader.Empty) { return(list); } while (reader.Read()) { list.Add(GetAccount(reader)); } } return(list); } } }
public List <FiscalYear> SelectFiscalYears() { const string sqlText = @"SELECT id, name, open_date, close_date FROM dbo.FiscalYear"; List <FiscalYear> fiscalYears = new List <FiscalYear>(); using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(null); } while (reader.Read()) { fiscalYears.Add(new FiscalYear { Id = reader.GetInt("id"), Name = reader.GetString("name"), CloseDate = reader.GetNullDateTime("close_date"), OpenDate = reader.GetNullDateTime("open_date") }); } } } } return(fiscalYears); }
public List <EmailAccount> SelectAll() { List <EmailAccount> emailAccounts = new List <EmailAccount>(); const string q = @"SELECT * FROM dbo.EmailAccounts where Deleted <> 1"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(emailAccounts); } while (r.Read()) { var b = new EmailAccount { Id = r.GetInt("Id"), Email = r.GetString("Email"), DisplayName = r.GetString("DisplayName"), Host = r.GetString("Host"), Port = r.GetInt("Port"), Username = r.GetString("Username"), Password = r.GetString("Password"), EnableSsl = r.GetBool("EnableSsl"), UseDefaultCredentials = r.GetBool("UseDefaultCredentials"), IsDefaultEmailAccount = r.GetBool("IsDefaultEmailAccount"), Deleted = r.GetBool("Deleted"), }; emailAccounts.Add(b); } } return(emailAccounts); }
public static string GetDatabaseVersion(string database, SqlConnection connection) { if (connection == null) { throw new ArgumentException("Both connection and transaction cannot be null."); } if (!IsOctopusDatabase(database, connection)) return string.Empty; string sqlText = string.Format( "USE [{0}] SELECT [value] FROM [TechnicalParameters] WHERE [name]='version'", database); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, connection)) using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return string.Empty; reader.Read(); return reader.GetString("value"); } }
public static string GetDatabaseBranchCodeFromBranches(string pDatabaseName, SqlConnection pSqlConnection) { string sqlText = string.Format("USE [{0}] SELECT TOP 1 [code] FROM [Branches] WHERE [deleted] = 0", pDatabaseName); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return string.Empty; reader.Read(); string code = reader.GetString("code"); if (string.IsNullOrEmpty(code)) throw new ApplicationException( string.Format("Emty or no branch code found for database: {0}.",pDatabaseName)); return code; } } }
///// <summary> ///// This method Fill the instance of the ProvisioningTable object accessed by singleton ///// </summary> public List<ProvisioningRate> SelectAllProvisioningRates() { List<ProvisioningRate> list = new List<ProvisioningRate>(); const string sqlText = @"SELECT id, number_of_days_min, number_of_days_max, provisioning_value, provisioning_interest, provisioning_penalty FROM ProvisioningRules"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return list; while (reader.Read()) { list.Add(GetProvisionningRate(reader)); } return list; } } } }
public void SelectSavingsBookContract(SavingBookContract saving) { const string sqlText = @"SELECT sbc.flat_withdraw_fees, sbc.rate_withdraw_fees, sbc.flat_transfer_fees, sbc.rate_transfer_fees, sbc.flat_deposit_fees, sbc.flat_close_fees, sbc.flat_management_fees, sbc.flat_overdraft_fees, sbc.in_overdraft, sbc.rate_agio_fees, sbc.cheque_deposit_fees, sbc.flat_reopen_fees, sbc.flat_ibt_fee, sbc.rate_ibt_fee, sbc.use_term_deposit, sbc.term_deposit_period, sbc.term_deposit_period_min, sbc.term_deposit_period_max, sbc.transfer_account, sbc.rollover, t.branch_id, sbc.next_maturity FROM dbo.SavingBookContracts sbc INNER JOIN dbo.SavingContracts sc on sc.id = sbc.id INNER JOIN dbo.Tiers t on t.id = sc.tiers_id WHERE sbc.id = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@id", saving.Id); using (OpenCbsReader reader = select.ExecuteReader()) { if (!reader.Read()) return; GetSavingBookFromReader(saving, reader); } //It needs to launch a new reader, that's why next code is here saving.TransferAccount = Select(saving.TransferAccount.Code); } OnSavingSelected(saving); }
public List<Loan> SelectLoansBySavingsId(int savingsId) { List<Loan> listLoans = new List<Loan>(); const string sqlText = @"SELECT loan_id FROM LoansLinkSavingsBook WHERE savings_id = @savingsId"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@savingsId", savingsId); using (OpenCbsReader reader = select.ExecuteReader()) { if (!reader.Empty) { while (reader.Read()) { listLoans.Add(_loanManager.SelectLoan(reader.GetInt("loan_id"), true, false, false)); } } } } return listLoans; }
public List<ISavingsContract> SelectAllActive() { List<ISavingsContract> listSaving = new List<ISavingsContract>(); string sqlText = @"SELECT SavingContracts.*, u2.first_name AS so_first_name, u2.last_name AS so_last_name, SavingProducts.product_type, Users.id AS user_id, Users.deleted, Users.user_name, Users.user_pass, Users.role_code, Users.first_name, Users.last_name FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id INNER JOIN Users ON SavingContracts.user_id = Users.id WHERE SavingContracts.status != " + ((int) OSavingsStatus.Closed); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (!reader.Empty) { while (reader.Read()) { listSaving.Add(GetSavingFromReader(reader)); } } } } foreach (ISavingsContract saving in listSaving) { SelectSavingsBookContract((SavingBookContract)saving); saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id); saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product)); if (_clientManager != null) saving.Client = _clientManager.SelectClientBySavingsId(saving.Id); } return listSaving; }
public SavingBookContract Select(int pSavingId) { SavingBookContract saving; const string sqlText = @"SELECT SavingContracts.*, u2.first_name AS so_first_name, u2.last_name AS so_last_name, SavingProducts.product_type, Users.id AS user_id, Users.deleted, Users.user_name, Users.user_pass, Users.role_code, Users.first_name, Users.last_name FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN Users ON SavingContracts.user_id = Users.id INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id WHERE SavingContracts.id = @id"; using (SqlConnection conn = GetConnection()) using(var cmd = new OpenCbsCommand(sqlText, conn)) { cmd.AddParam("@id", pSavingId); using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader.Empty) return null; reader.Read(); saving = GetSavingFromReader(reader); } } SelectSavingsBookContract(saving); saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id); saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product)); return saving; }
public List<Loan> SelectAllLoansOfClient(int pClientId) { const string sql = @"SELECT Contracts.id as id FROM Contracts INNER JOIN Projects ON Contracts.project_id = Projects.id WHERE Projects.tiers_id = @id AND Contracts.nsg_id IS NOT NULL"; List<Loan> list = new List<Loan>(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(sql, conn)) { c.AddParam("@id", pClientId); using (OpenCbsReader r = c.ExecuteReader()) { if (r == null || r.Empty) return list; while (r.Read()) list.Add(SelectLoan(r.GetInt("id"), true, true, true)); } } return list; }
public List<Alert_v2> SelectAllAlerts(DateTime date, int userId) { List<Alert_v2> alerts = new List<Alert_v2>(); string q = "SELECT * FROM dbo.Alerts(@date, @user_id, @branch_id)"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.CommandTimeout = 600; c.AddParam("@date", date.Date); c.AddParam("@user_id", userId); c.AddParam("@branch_id", 1); using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) return alerts; while (r.Read()) { Alert_v2 alert = new Alert_v2 { Address = r.GetString("address") , Amount = r.GetMoney("amount") , City = r.GetString("city") , ClientName = r.GetString("client_name") , ContractCode = r.GetString("contract_code") , Date = r.GetDateTime("date") , Id = r.GetInt("id") , LateDays = r.GetInt("late_days") , LoanOfficer = new User {Id = r.GetInt("loan_officer_id")} , Phone = r.GetString("phone") , Status = (OContractStatus) r.GetInt("status") , UseCents = r.GetBool("use_cents") , Kind = (AlertKind) r.GetInt("kind") , BranchName = r.GetString("branch_name") }; alerts.Add(alert); } } return alerts; } }
/// <summary> /// Select the latest active loan for a client /// </summary> /// <param name="pClientId"></param> /// <returns></returns> public List<Loan> SelectActiveLoans(int pClientId) { const string q = @"SELECT Contracts.id as id FROM Contracts INNER JOIN Projects ON Contracts.project_id = Projects.id WHERE Projects.tiers_id = @id AND Contracts.status IN (@status1, @status2, @status3, @status4) AND Contracts.nsg_id IS NOT NULL"; List<Loan> list = new List<Loan>(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", pClientId); c.AddParam("@status1", (int)OContractStatus.Active); c.AddParam("@status2", (int)OContractStatus.Validated); c.AddParam("@status3", (int)OContractStatus.Pending); c.AddParam("@status4", (int)OContractStatus.Postponed); using (OpenCbsReader r = c.ExecuteReader()) { if (r == null || r.Empty) return list; while (r.Read()) list.Add(SelectLoan(r.GetInt("id"), true, true, true)); } } return list; }
public List<CreditSearchResult> SearchCreditContractByCriteres(int pageNumber, string pQuery, out int count) { int startRow = 20*(pageNumber - 1) + 1; int endRow = 20*pageNumber; const string query = @" SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [user_id]) row, COUNT(1) OVER(PARTITION BY [user_id]) row_count, * FROM ( SELECT Contracts.id, Contracts.contract_code, Contracts.status, Contracts.start_date, Contracts.align_disbursed_date, Contracts.close_date, Persons.identification_data as identification_data, Credit.amount, Credit.loanofficer_id, Tiers.client_type_code, ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name, ISNULL(ISNULL(ISNULL(Groups.name, Persons.first_name + SPACE(1) + Persons.last_name), Corporates.name),'Error!') AS client_name, ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name, Credit.[amount_min], Credit.[amount_max], Credit.[ir_min], Credit.[ir_max], Credit.[nmb_of_inst_min], Credit.[nmb_of_inst_max], Credit.[loan_cycle], @user_id [user_id] FROM Contracts INNER JOIN Credit ON Contracts.id = Credit.id INNER JOIN Projects ON Contracts.project_id = Projects.id INNER JOIN Tiers ON Projects.tiers_id = Tiers.id AND Tiers.branch_id IN (select branch_id from dbo.UsersBranches where user_id = @user_id) LEFT JOIN Users ON Users.id = Credit.loanofficer_id LEFT OUTER JOIN Persons ON Tiers.id = Persons.id LEFT OUTER JOIN Groups ON Tiers.id = Groups.id LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id UNION ALL SELECT ISNULL(Contracts.id,0) AS id, ISNULL(Contracts.contract_code,'No contract') AS contract_code, ISNULL(Contracts.status,0) AS status, ISNULL(Contracts.start_date,'01-01-1900') AS start_date, ISNULL(Contracts.align_disbursed_date,'01-01-1900') AS align_disbursed_date, ISNULL(Contracts.close_date,'01-01-1900') AS close_date, Persons.identification_data AS identification_data, ISNULL(Credit.amount,0) AS amount, ISNULL(Credit.loanofficer_id,0) AS loanofficer_id, CASE (Tiers.client_type_code) WHEN 'I' THEN 'V' ELSE '-' END AS client_type_code, ISNULL(ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name),'No contract') AS user_name, Villages.name AS client_name, ISNULL(ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name),'No contract') AS loanofficer_name, Credit.[amount_min], Credit.[amount_max], Credit.[ir_min], Credit.[ir_max], Credit.[nmb_of_inst_min], Credit.[nmb_of_inst_max], Credit.[loan_cycle], @user_id [user_id] FROM Villages INNER JOIN VillagesPersons ON VillagesPersons.village_id = Villages.id INNER JOIN Persons ON Persons.id = VillagesPersons.person_id INNER JOIN Tiers ON Persons.id = Tiers.id AND Tiers.branch_id IN (select branch_id from dbo.UsersBranches where user_id = @user_id) INNER JOIN Projects ON Tiers.id = Projects.tiers_id INNER JOIN Contracts ON Contracts.project_id = Projects.id INNER JOIN Credit ON Credit.id = Contracts.id LEFT JOIN Users ON Users.id = Credit.loanofficer_id ) R WHERE (ISNULL(contract_code, '') + ' ' + ISNULL(client_name, '') + ' ' + ISNULL([user_name], '') + ' ' + ISNULL(identification_data, '') + ' ' + ISNULL(loanofficer_name, '')) LIKE @criteria ) T WHERE row BETWEEN @startRow AND @endRow "; count = 0; var list = new List<CreditSearchResult>(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(query, conn)) { cmd.AddParam("user_id", User.CurrentUser.Id); cmd.AddParam("startRow", startRow); cmd.AddParam("endRow", endRow); cmd.AddParam("criteria", string.Format("%{0}%", pQuery)); using (OpenCbsReader reader = cmd.ExecuteReader()) while (reader.Read()) { if (count == 0) count = reader.GetInt("row_count"); var result = new CreditSearchResult { Id = reader.GetInt("id"), ContractCode = reader.GetString("contract_code"), ClientType = reader.GetString("client_type_code"), ClientName = reader.GetString("client_name"), ContractStartDate = reader.GetDateTime("start_date").ToShortDateString(), ContractEndDate = reader.GetDateTime("close_date").ToShortDateString(), ContractStatus = ((OContractStatus)reader.GetSmallInt("status")).ToString(), LoanOfficer = new User { Id = reader.GetInt("loanofficer_id") } }; list.Add(result); } } return list; }
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 List<SavingSearchResult> SearchSavingContractByCritere(int pPageNumber, string pQuery, bool all, bool activeContractOnly) { List<SavingSearchResult> list = new List<SavingSearchResult>(); string sql = @"SELECT SavingContracts.id, SavingContracts.code AS contract_code, SavingContracts.status AS contract_status, SavingContracts.creation_date as start_date, SavingContracts.closed_date as end_date, SavingProducts.product_type as product_type, Persons.identification_data as identification_data, Tiers.client_type_code, Tiers.id AS client_id, ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name, ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name, ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name, SavingContracts.user_id AS loan_officer_id, SavingProducts.currency_id FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN Users ON SavingContracts.user_id = Users.id INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.id LEFT OUTER JOIN Persons ON Tiers.id = Persons.id LEFT OUTER JOIN Groups ON Tiers.id = Groups.id LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id"; sql += " WHERE 1=1 "; if (activeContractOnly) sql += " AND SavingContracts.status=1 "; if (!all) { sql += @" AND Tiers.branch_id in ( select branch_id from dbo.UsersBranches WHERE user_id = @user_id )"; } sql += ") maTable"; const string closeWhere = @" WHERE ( contract_code LIKE @contractCode OR client_name LIKE @clientName OR user_name LIKE @UserName OR identification_data LIKE @numberPassport OR loanofficer_name LIKE @loanofficerName )) maTable"; QueryEntity q = new QueryEntity(pQuery, sql, closeWhere); string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pPageNumber - 1) * 20); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(pSqlText, conn)) { foreach (var item in q.DynamiqParameters()) { select.AddParam(item.Key, string.Format("%{0}%", item.Value)); } select.AddParam("@user_id", User.CurrentUser.Id); using (OpenCbsReader reader = select.ExecuteReader()) { if (!reader.Empty) { while (reader.Read()) { SavingSearchResult result = new SavingSearchResult { Id = reader.GetInt("id"), ClientId = reader.GetInt("client_id"), ContractCode = reader.GetString("contract_code"), Status = (OSavingsStatus)reader.GetSmallInt("contract_status"), ClientTypeCode = reader.GetString("client_type_code") }; switch (result.ClientTypeCode) { case "I": result.ClientType = OClientTypes.Person; break; case "G": result.ClientType = OClientTypes.Group; break; case "V": result.ClientType = OClientTypes.Village; break; case "C": result.ClientType = OClientTypes.Corporate; break; default: result.ClientType = OClientTypes.Person; break; } result.ClientName = reader.GetString("client_name"); result.ContractStartDate = reader.GetDateTime("start_date"); result.ContractEndDate = reader.GetNullDateTime("end_date"); result.ContractType = reader.GetString("product_type"); result.LoanOfficer = new User { Id = reader.GetInt("loan_officer_id") }; result.CurrencyId = reader.GetInt("currency_id"); list.Add(result); } } } return list; } }
public List<LoanEntryFee> SelectInstalledLoanEntryFees(int loanId) { List<LoanEntryFee> loanEntryFees = new List<LoanEntryFee>(); string q = @"SELECT id ,[entry_fee_id] ,[fee_value] FROM [dbo].[CreditEntryFees] WHERE [credit_id]=@credit_id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@credit_id", loanId); using (OpenCbsReader r = c.ExecuteReader()) { while (r.Read()) { LoanEntryFee lef = new LoanEntryFee(); lef.Id = r.GetInt("id"); lef.ProductEntryFeeId = r.GetInt("entry_fee_id"); lef.FeeValue = r.GetDecimal("fee_value"); loanEntryFees.Add(lef); } } } return loanEntryFees; }
public List<ISavingsContract> SelectAll(string pSavingCode) { List<ISavingsContract> listSaving = new List<ISavingsContract>(); const string sqlText = @"SELECT SavingContracts.*, u2.first_name AS so_first_name, u2.last_name AS so_last_name, SavingProducts.product_type, Users.id AS user_id, Users.deleted, Users.user_name, Users.user_pass, Users.role_code, Users.first_name, Users.last_name FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN Users ON SavingContracts.user_id = Users.id INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id WHERE SavingContracts.Code LIKE @code"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@code", string.Format("%{0}%", pSavingCode)); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return new List<ISavingsContract>(); while (reader.Read()) { ISavingsContract saving = GetSavingFromReader(reader); listSaving.Add(saving); } } } foreach (ISavingsContract saving in listSaving) { SelectSavingsBookContract((SavingBookContract)saving); saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id); saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product)); } return listSaving; }
public List<DateTime> SelectInstallmentDatesForVillageActiveContracts(int villageId) { List<DateTime> installmentDates = new List<DateTime>(); string q = @"SELECT Installments.expected_date FROM dbo.VillagesPersons vp INNER JOIN dbo.Projects pr ON pr.tiers_id = vp.person_id INNER JOIN dbo.Contracts c ON c.project_id = pr.id INNER JOIN dbo.Installments ON Installments.contract_id = c.id WHERE village_id = @village_id AND c.[status] = 5 GROUP BY Installments.expected_date ORDER BY Installments.expected_date"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@village_id", villageId); using (OpenCbsReader r = c.ExecuteReader()) { while (r.Read()) { installmentDates.Add(r.GetDateTime("expected_date")); } } } return installmentDates; }
public KeyValuePair<int, string>[] SelectClientSavingBookCodes(int tiersId, int? currencyId) { const string sqlText = @"SELECT sc.id, sc.code FROM SavingContracts sc INNER JOIN SavingProducts sp ON sc.product_id = sp.id AND (@currency_id IS NULL OR sp.currency_id = @currency_id) WHERE tiers_id = @tiersId AND closed_date IS NULL"; using (SqlConnection conn = GetConnection()) using (var command = new OpenCbsCommand(sqlText, conn)) { command.AddParam("tiersId", tiersId); command.AddParam("currency_id", currencyId); var result = new List<KeyValuePair<int, string>>(); using (var reader = command.ExecuteReader()) { while(reader.Read()) { var savingId = reader.GetInt("id"); var savingCode = reader.GetString("code"); result.Add( new KeyValuePair<int, string>(savingId, savingCode) ); } return result.ToArray(); } } }
/// <summary> /// Returns Loan /// </summary> /// <param name="pLoanId">Loan id</param> /// <param name="pAddGeneralInformation">add product, events, chartOfAccount, and funding line</param> /// <param name="pAddOptionalInformation"></param> /// <param name="pAddOptionalEventInformation"></param> /// <returns></returns> public Loan SelectLoan(int pLoanId, bool pAddGeneralInformation, bool pAddOptionalInformation, bool pAddOptionalEventInformation) { Loan loan; int productId; int installmentTypeId; int loanOfficerId; int fundingLineId; const string q = @"SELECT Credit.id AS credit_id, Credit.package_id, Credit.amount, Credit.interest_rate, Credit.installment_type, Credit.nb_of_installment, Credit.non_repayment_penalties_based_on_overdue_principal, Credit.non_repayment_penalties_based_on_overdue_interest, Credit.non_repayment_penalties_based_on_olb, Credit.non_repayment_penalties_based_on_initial_amount, Credit.anticipated_total_repayment_penalties, Credit.anticipated_partial_repayment_penalties, Credit.anticipated_total_repayment_base, Credit.anticipated_partial_repayment_base, Credit.disbursed, Credit.loanofficer_id, Credit.fundingLine_id, Credit.grace_period, Credit.written_off, Credit.rescheduled, Credit.bad_loan, Credit.grace_period_of_latefees, Contracts.contract_code, Tiers.client_type_code, Credit.synchronize, Credit.schedule_changed, ISNULL(Credit.amount_under_loc, 0) AS amount_under_loc, Contracts.branch_code, Contracts.creation_date, Contracts.start_date, ISNULL(Contracts.align_disbursed_date, Contracts.start_date) AS align_disbursed_date, Contracts.close_date, Contracts.closed, Contracts.status, Contracts.credit_commitee_date, Contracts.credit_commitee_comment, Contracts.credit_commitee_code, Contracts.loan_purpose, Contracts.comments, Contracts.nsg_id, Contracts.activity_id, Contracts.preferred_first_installment_date, LoansLinkSavingsBook.loan_percentage, Credit.[amount_min], Credit.[amount_max], Credit.[ir_min], Credit.[ir_max], Credit.[nmb_of_inst_min], Credit.[nmb_of_inst_max], Credit.[loan_cycle], Credit.insurance FROM Contracts INNER JOIN Credit ON Contracts.id = Credit.id INNER JOIN Projects ON Contracts.project_id = Projects.id INNER JOIN Tiers ON Projects.tiers_id = Tiers.id LEFT JOIN LoansLinkSavingsBook ON LoansLinkSavingsBook.loan_id = Contracts.id WHERE Credit.id = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", pLoanId); using (OpenCbsReader r = c.ExecuteReader()) { if (r == null || r.Empty) return null; r.Read(); loan = _GetLoan(r); installmentTypeId = r.GetInt("installment_type"); productId = r.GetInt("package_id"); loanOfficerId = r.GetInt("loanofficer_id"); fundingLineId = r.GetInt("fundingLine_id"); } } loan.InstallmentType = _installmentTypeManagement.SelectInstallmentType(installmentTypeId); loan.InstallmentList = _installmentManagement.SelectInstallments(loan.Id); loan.EconomicActivity = _economicActivityManager.SelectEconomicActivity(loan.EconomicActivityId); loan.GivenTranches = SelectTranches(loan.Id); if (pAddGeneralInformation) { loan.Product = _packageManager.Select(productId); loan.Events = _eventManagement.SelectEvents(loan.Id); foreach (Event loanEvent in loan.Events) { if (loanEvent is LoanDisbursmentEvent) { if (((LoanDisbursmentEvent) loanEvent).PaymentMethodId==null) continue; int paymentMethodId = (int)((LoanDisbursmentEvent) loanEvent).PaymentMethodId; loanEvent.PaymentMethod = _paymentMethodManager.SelectPaymentMethodById(paymentMethodId); } if (loanEvent is RepaymentEvent) { if (((RepaymentEvent)loanEvent).PaymentMethodId==null) continue; int paymentMethodId = (int) ((RepaymentEvent) loanEvent).PaymentMethodId; loanEvent.PaymentMethod = _paymentMethodManager.SelectPaymentMethodById(paymentMethodId); } } if (_projectManager != null) loan.Project = _projectManager.SelectProjectByContractId(loan.Id); foreach (Installment installment in loan.InstallmentList) { installment.OLB = loan.CalculateExpectedOlb(installment.Number, loan.Product.KeepExpectedInstallment); } } if (pAddOptionalInformation) { loan.FundingLine = _fundingLineManager.SelectFundingLineById(fundingLineId, false); loan.LoanOfficer = _userManager.SelectUser(loanOfficerId, true); loan.Guarantors = GetGuarantors(loan.Id); loan.Collaterals = GetCollaterals(loan.Id); if (loan.ClientType == OClientTypes.Group) loan.LoanShares.AddRange(GetLoanShareAmount(pLoanId)); } return loan; }
public List<ISavingsContract> SelectSavings(int pClientId) { const string sqlText = @"SELECT SavingContracts.[id] ,SavingContracts.[product_id] ,SavingContracts.[user_id] ,SavingContracts.[code] ,SavingContracts.[tiers_id] ,SavingContracts.[creation_date] ,SavingContracts.[interest_rate] ,SavingContracts.[status] ,SavingContracts.[closed_date] ,SavingContracts.[savings_officer_id] ,SavingContracts.[initial_amount] ,SavingContracts.[entry_fees] ,SavingContracts.[nsg_id] ,u2.first_name AS so_first_name ,u2.last_name AS so_last_name ,SavingProducts.product_type ,Users.id AS user_id ,Users.deleted ,Users.user_name ,Users.user_pass ,Users.role_code ,Users.first_name ,Users.last_name FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN Users ON SavingContracts.user_id = Users.id INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id WHERE SavingContracts.tiers_id = @tiers_id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { List<ISavingsContract> savings = new List<ISavingsContract>(); select.AddParam("@tiers_id", pClientId); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return new List<ISavingsContract>(); while (reader.Read()) { savings.Add(GetSavingFromReader(reader)); } } foreach (ISavingsContract saving in savings) { SelectSavingsBookContract((SavingBookContract)saving); saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id); saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product)); ((SavingBookContract)saving).Loans = SelectLoansBySavingsId(saving.Id); } return savings; } }
public List<Loan> SelectLoansByClientId(int clientId) { List<int> ids = new List<int>(); const string q = @"SELECT Cont.id FROM Contracts AS Cont INNER JOIN Projects AS Pr ON Cont.project_id = Pr.id INNER JOIN Tiers AS Tr ON Tr.id = Pr.tiers_id WHERE Tr.id = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", clientId); using (OpenCbsReader r = c.ExecuteReader()) { if (r == null || r.Empty) return new List<Loan>(); while (r.Read()) ids.Add(r.GetInt("id")); } } List<Loan> loans = new List<Loan>(); foreach (int id in ids) loans.Add(SelectLoan(id, true, true, true)); return loans; }
public SavingBookContract SelectSavingsByLoanId(int pLoanId, bool loadLoans) { SavingBookContract saving; const string sqlText = @"SELECT SavingContracts.*, u2.first_name AS so_first_name, u2.last_name AS so_last_name, SavingProducts.product_type, Users.id AS [user_id], Users.deleted, Users.user_name, Users.user_pass, Users.role_code, Users.first_name, Users.last_name FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN LoansLinkSavingsBook ON LoansLinkSavingsBook.savings_id = SavingContracts.id INNER JOIN Users ON SavingContracts.[user_id] = Users.id INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id WHERE LoansLinkSavingsBook.loan_id = @loanId"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@loanId", pLoanId); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return null; reader.Read(); saving = (SavingBookContract)GetSavingFromReader(reader); } } if (saving != null) { saving.Product = (SavingsBookProduct)_savingProductManager.SelectSavingProduct(saving.Product.Id); saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product)); if (loadLoans) saving.Loans = SelectLoansBySavingsId(saving.Id); } return saving; }
public AlertStock SelectLoansByLoanOfficer(int pLoanOfficerId) { string addLoanOfficer = ""; if (pLoanOfficerId != 0) addLoanOfficer = " AND (Credit.loanofficer_id = @loanOfficerId) "; string sqlTextRepaymentAlert = string.Format(@"SELECT Credit.id AS contract_id, Credit.interest_rate, Contracts.contract_code, Contracts.creation_date, Contracts.start_date, Contracts.align_disbursed_date, Contracts.close_date, CASE Contracts.status WHEN 1 THEN 'Pending' WHEN 2 THEN 'Validated' WHEN 3 THEN 'Refused' WHEN 4 THEN 'Abandoned' WHEN 5 THEN 'Active' WHEN 6 THEN 'Closed' WHEN 7 THEN 'WrittenOff' ELSE '-' END AS loan_status, InstallmentTypes.name AS installment_types, ISNULL(Groups.name, ISNULL(Persons.first_name + ' ' + Persons.last_name,Corporates.name)) AS client_name, Districts.name as district_name, Installments.capital_repayment + Installments.interest_repayment - Installments.paid_capital - Installments.paid_interest AS amount, Installments.expected_date AS effect_date, ISNULL(( SELECT SUM(principal) FROM contractEvents INNER JOIN repaymentEvents ON repaymentEvents.id = contractEvents.id WHERE is_deleted = 0 AND contract_id = Contracts.id ), 0) AS olb FROM Credit INNER JOIN Contracts ON Contracts.id = Credit.id INNER JOIN Installments ON Installments.contract_id = Credit.id INNER JOIN InstallmentTypes ON dbo.Credit.installment_type = dbo.InstallmentTypes.id INNER JOIN Projects ON Contracts.project_id = Projects.id INNER JOIN Tiers ON Projects.tiers_id = Tiers.id LEFT OUTER JOIN Corporates ON Tiers.id=Corporates.id LEFT OUTER JOIN Persons ON dbo.Tiers.id = Persons.id LEFT OUTER JOIN Groups ON dbo.Tiers.id = Groups.id LEFT OUTER JOIN Districts ON dbo.Tiers.district_id = Districts.id WHERE ( Installments.capital_repayment + Installments.interest_repayment - Installments.paid_capital - Installments.paid_interest > 0.02 ) AND Contracts.status = 5 {0} ORDER BY contract_id, effect_date DESC", addLoanOfficer); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand cmdSelectRepayment = new OpenCbsCommand(sqlTextRepaymentAlert, conn)) { if (pLoanOfficerId != 0) cmdSelectRepayment.AddParam("@loanOfficerId", pLoanOfficerId); using (OpenCbsReader reader = cmdSelectRepayment.ExecuteReader()) { if(reader.Empty) return new AlertStock(); AlertStock alertStock = new AlertStock(); while (reader.Read()) { alertStock.Add(GetAlert(reader, 'R')); } return alertStock; } } }
public static string GetDatabaseBranchCode(string pDatabaseName, SqlConnection pSqlConnection) { string sqlText = string.Format("USE [{0}] SELECT [value] FROM [GeneralParameters] WHERE [key]='BRANCH_CODE'", pDatabaseName); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return string.Empty; reader.Read(); return reader.GetString("value"); } } }
private static string BackupToFile(string fileName, string database, SqlConnection connection) { string backupDirectory; if (TechnicalSettings.UseDemoDatabase) { backupDirectory = @"C:\Users\Public"; } else { // To perform a backup we first need to get the appropriate backup folder, which is a bit tricky. // First, we need to get the service name. const string query = "SELECT @@SERVICENAME AS name"; OpenCbsCommand cmd = new OpenCbsCommand(query, connection); string serviceName = string.Empty; using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader.Empty) return null; reader.Read(); serviceName = reader.GetString("name"); } // Then get the instance name from the registry const string sqlServerKey = @"SOFTWARE\Microsoft\Microsoft SQL Server"; string key = string.Format(@"{0}\Instance Names\SQL", sqlServerKey); string instanceName = ExtendedRegistry.GetKeyValue(key, serviceName); if (string.IsNullOrEmpty(instanceName)) return null; // Finally, get the backup directory key = string.Format(@"{0}\{1}\MSSQLServer", sqlServerKey, instanceName); backupDirectory = ExtendedRegistry.GetKeyValue(key, "BackupDirectory"); if (string.IsNullOrEmpty(backupDirectory)) return null; } string path = Path.Combine(backupDirectory, fileName); BackupToFileImpl(path, database, connection); return path; }
public static string GetDatabaseSize(string pDatabase, SqlConnection pSqlConnection) { string sql = string.Format("USE [{0}] EXEC sp_spaceused", pDatabase); using (OpenCbsCommand cmd = new OpenCbsCommand(sql, pSqlConnection)) { using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader.Empty) return string.Empty; if (reader.Read()) { return reader.GetString("database_size"); } return string.Empty; } } }
private static Dictionary<string, char> GetFilelist(string file,SqlConnection connection) { //list of files of a backup. Dictionary<string, char> databaseFiles = new Dictionary<string, char>(); string q = @"RESTORE FILELISTONLY FROM DISK = '{0}'"; q = string.Format(q, file); OpenCbsCommand cmd = new OpenCbsCommand(q, connection); using (OpenCbsReader reader = cmd.ExecuteReader()) { if (null == reader || reader.Empty) return null; while (reader.Read()) { string logicalName = reader.GetString("LogicalName"); char type = reader.GetString("Type").ToCharArray()[0]; databaseFiles.Add(logicalName, type); } } return databaseFiles; }
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; }
public int GetNumberSavingContract(string pQuery, bool all, bool activeContractsOnly) { string sql =@"SELECT TOP 100 percent SavingContracts.id, SavingContracts.code AS contract_code, SavingContracts.creation_date as start_date, Persons.identification_data as identification_data, Tiers.client_type_code, ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name, ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name, ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name FROM SavingContracts INNER JOIN Users ON SavingContracts.user_id = Users.id INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.id LEFT OUTER JOIN Persons ON Tiers.id = Persons.id LEFT OUTER JOIN Groups ON Tiers.id = Groups.id LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id"; sql += " WHERE 1=1 "; if (activeContractsOnly) sql += " AND SavingContracts.[status]=1 "; if (!all) { sql += @" AND Tiers.branch_id in ( select branch_id from dbo.UsersBranches WHERE user_id = @user_id )"; } sql += @") maTable"; const string closeWhere = @" WHERE (contract_code LIKE @contractCode OR client_name LIKE @clientName OR user_name LIKE @userName OR identification_data LIKE @numberPassport OR loanofficer_name LIKE @loanofficerName )) maTable"; QueryEntity q = new QueryEntity(pQuery, sql, closeWhere); string sqlText = q.ConstructSQLEntityNumberProxy(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { foreach (var item in q.DynamiqParameters()) { select.AddParam(item.Key, string.Format("%{0}%", item.Value)); } select.AddParam("@user_id", User.CurrentUser.Id); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return 0; reader.Read(); return reader.GetInt(0); } } }
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 List<LoanShare> GetLoanShareAmount(int pContractId) { const string q = @"SELECT lsa.group_id, lsa.person_id, lsa.amount, p.first_name + ' ' + p.last_name AS person_name FROM LoanShareAmounts AS lsa LEFT JOIN Persons AS p ON p.id = lsa.person_id WHERE contract_id = @contract_id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("contract_id", pContractId); using (OpenCbsReader r = c.ExecuteReader()) { if (r == null || r.Empty) return new List<LoanShare>(); List<LoanShare> loanShares = new List<LoanShare>(); while (r.Read()) { loanShares.Add(new LoanShare { PersonId = r.GetInt("person_id"), PersonName = r.GetString("person_name"), Amount = r.GetMoney("amount") }); } return loanShares; } } }