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 List<Booking> SelectAllStandardBookings()
{
const string sqlText = @"SELECT Id, [Name], debit_account_id, credit_account_id FROM StandardBookings";
List<Booking> list = new List<Booking>();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return list;
while (reader.Read())
{
Booking standardBooking = new Booking();
standardBooking.Id = reader.GetInt("Id");
standardBooking.Name = reader.GetString("Name");
Account account =
_chartOfAccounts.GetAccountById(reader.GetInt("debit_account_id"));
standardBooking.DebitAccount = account;
account =
_chartOfAccounts.GetAccountById(reader.GetInt("credit_account_id"));
standardBooking.CreditAccount = account;
list.Add(standardBooking);
}
}
}
return list;
}
}
///// <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);
}
}
}
}
public Currency GetPivot()
{
Currency pivot = null;
const string q = "SELECT * FROM Currencies where is_pivot = 1";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(pivot);
}
while (r.Read())
{
pivot = new Currency
{
Id = r.GetInt("id"),
Name = r.GetString("name"),
Code = r.GetString("code"),
IsPivot = r.GetBool("is_pivot"),
IsSwapped = r.GetBool("is_swapped"),
UseCents = r.GetBool("use_cents")
};
}
return(pivot);
}
}
public List <Account> SelectAllAccounts()
{
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
ORDER BY account_number";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(list);
}
while (reader.Read())
{
list.Add(GetAccount(reader));
}
}
return(list);
}
}
}
public bool SelectAccountingRuleByChartOfAccountsId(Account account)
{
const string sqlText = @"SELECT TOP 1 id
FROM AccountingRules
WHERE debit_account_number_id = @id OR credit_account_number_id = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@id", account.Id);
int?inUse;
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(false);
}
reader.Read();
inUse = reader.GetInt("id");
}
return(inUse != null);
}
}
}
public Account SelectChartAccount(int pAccountId)
{
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 = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@id", pAccountId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(null);
}
reader.Read();
return(GetAccount(reader));
}
}
}
}
public List <AccountCategory> SelectAccountCategories()
{
List <AccountCategory> list = new List <AccountCategory>();
const string sqlText = @"SELECT id, name FROM AccountsCategory";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(list);
}
while (reader.Read())
{
list.Add(new AccountCategory
{
Id = reader.GetSmallInt("id"),
Name = reader.GetString("name"),
}
);
}
return(list);
}
}
}
}
public AccountCategory SelectAccountCategoriesById(int id)
{
const string sqlText = @"SELECT id, name
FROM AccountsCategory
WHERE id = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@id", id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(null);
}
reader.Read();
return(new AccountCategory
{
Id = reader.GetSmallInt("id"),
Name = reader.GetString("name"),
});
}
}
}
}
public Currency SelectCurrencyByName(string name)
{
const string q = @"SELECT id, code, is_pivot, is_swapped, use_cents FROM [Currencies] WHERE name = @name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", name);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(null);
}
r.Read();
return(new Currency
{
Id = r.GetInt("id"),
Code = r.GetString("code"),
Name = name,
IsPivot = r.GetBool("is_pivot"),
IsSwapped = r.GetBool("is_swapped"),
UseCents = r.GetBool("use_cents")
});
}
}
}
public List <Currency> SelectAllCurrencies(SqlTransaction t)
{
List <Currency> currencies = new List <Currency>();
const string q = @"SELECT * FROM Currencies ORDER BY is_pivot DESC";
using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(currencies);
}
while (r.Read())
{
currencies.Add(new Currency
{
Id = r.GetInt("id"),
Name = r.GetString("name"),
Code = r.GetString("code"),
IsPivot = r.GetBool("is_pivot"),
IsSwapped = r.GetBool("is_swapped"),
UseCents = r.GetBool("use_cents")
});
}
}
return(currencies);
}
public Booking SelectStandardBookingById(int id)
{
const string sqlText = @"SELECT Id, [Name], debit_account_id, credit_account_id
FROM StandardBookings
WHERE Id = @Id";
Booking standardBooking = new Booking();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@Id", id);
using (OpenCbsReader reader = select.ExecuteReader())
{
while (reader.Read())
{
standardBooking.Id = reader.GetInt("Id");
standardBooking.Name = reader.GetString("Name");
Account account =
_chartOfAccounts.GetAccountById(reader.GetInt("debit_account_id"));
standardBooking.DebitAccount = account;
account =
_chartOfAccounts.GetAccountById(reader.GetInt("credit_account_id"));
standardBooking.CreditAccount = account;
}
}
}
}
return standardBooking;
}
public ExchangeRate Select(DateTime pDate, Currency pCurrency)
{
const string q = @"SELECT exchange_date, exchange_rate, currency_id
FROM ExchangeRates
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, exchange_date)) = @date
AND currency_id = @currency";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@date", pDate.Date);
c.AddParam("@currency", pCurrency.Id);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(null);
}
r.Read();
return(new ExchangeRate
{
Date = pDate,
Rate = r.GetDouble("exchange_rate"),
Currency = pCurrency
});
}
}
}
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);
}
/// <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);
}
}
/// <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 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);
}
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);
}
public List <Account> SelectAccountByCategory(AccountCategory accountCategory)
{
const string sqlText = @"SELECT id,
account_number,
label,
debit_plus,
type_code,
account_category_id,
type,
parent_account_id, lft, rgt
FROM ChartOfAccounts
WHERE account_category_id = @account_category_id";
List <Account> accounts = new List <Account>();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@account_category_id", accountCategory.Id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(null);
}
reader.Read();
while (reader.Read())
{
accounts.Add(GetAccount(reader));
}
return(accounts);
}
}
}
}
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);
}
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 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 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 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);
}