/// <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 Province AddProvinceIntoDatabase()
{
OpenCbsCommand command = new OpenCbsCommand("INSERT INTO Provinces (name,deleted) VALUES ('Sugh',0) SELECT SCOPE_IDENTITY()", SqlConnection);
Province province = new Province();
province.Name = "Sugh";
province.Id = int.Parse(command.ExecuteScalar().ToString());
return(province);
}
public void AddGenericEvent(int userId)
{
int contractId = AddGenericCreditContractIntoDatabase(true);
int id = 1;
OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO [ContractEvents]([id], [event_type], [contract_id], " +
"[event_date],[is_deleted], [user_id]) VALUES(" + id + ",'PDLE', " + contractId + ", '10/10/2006',1, " + userId + ") SELECT SCOPE_IDENTITY()", SqlConnection);
insert.ExecuteScalar();
}
public int AddGenericEvent()
{
int contractId = AddGenericCreditContractIntoDatabase(true);
int userId = AddUserWithIntermediaryAttributs();
OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO [ContractEvents]([event_type], [contract_id], " +
"[event_date],[is_deleted], [user_id]) VALUES('PDLE', " + contractId + ", '10/10/2006',0, " + userId + ")", SqlConnection);
return(int.Parse(insert.ExecuteScalar().ToString()));
}
public int InsertAccountCategory(AccountCategory accountCategory, SqlTransaction pSqlTransac)
{
const string sqlText = @"INSERT INTO AccountsCategory (name) VALUES (@name) SELECT SCOPE_IDENTITY()";
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
{
insert.AddParam("@name", accountCategory.Name);
return(int.Parse(insert.ExecuteScalar().ToString()));
}
}
public bool NumbersExist(string[] accountNumbers)
{
string query = string.Format(
"IF EXISTS(SELECT 1 FROM ChartOfAccounts WHERE account_number in ({0})) SELECT 1 ELSE SELECT 0",
string.Join(",", accountNumbers)
);
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand command = new OpenCbsCommand(query, connection))
return(Convert.ToInt32(command.ExecuteScalar()) == 1);
}
public Branch AddBranchIntoDatabase()
{
Branch branch = new Branch();
branch.Name = "Default";
OpenCbsCommand command = new OpenCbsCommand("INSERT INTO Branches (name) VALUES ('@name') SELECT SCOPE_IDENTITY()", SqlConnection);
command.AddParam("@name", branch.Name);
command.ExecuteScalar().ToString();
return(branch);
}
public int AddGenericFundingLine2()
{
OpenCbsCommand insert = new OpenCbsCommand(@"INSERT INTO FundingLines (name,begin_date,end_date,amount,purpose,deleted, currency_id)
VALUES ('AFD1302', @startDate, @endDate,1000,'TEST',0, 1) SELECT SCOPE_IDENTITY()", SqlConnection);
insert.AddParam("@startDate", DateTime.Now);
insert.AddParam("@endDate", DateTime.Now);
int id = Convert.ToInt32(insert.ExecuteScalar());
return(id);
}
public bool IdsExist(int[] ids)
{
string[] idStrings = ids.Select(i => i.ToString(CultureInfo.InvariantCulture)).ToArray();
string query = string.Format(
"IF EXISTS(SELECT 1 FROM ChartOfAccounts WHERE id in ({0})) SELECT 1 ELSE SELECT 0",
string.Join(",", idStrings)
);
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand command = new OpenCbsCommand(query, connection))
return(Convert.ToInt32(command.ExecuteScalar()) == 1);
}
public District AddDistrictIntoDatabase()
{
Province province = AddProvinceIntoDatabase();
District district = new District();
district.Province = province;
district.Name = "District";
OpenCbsCommand command = new OpenCbsCommand("INSERT INTO Districts (name,province_id,deleted) VALUES ('" + district.Name + "',@provinceId,0) SELECT SCOPE_IDENTITY()", SqlConnection);
command.AddParam("@provinceId", province.Id);
district.Id = int.Parse(command.ExecuteScalar().ToString());
return(district);
}
public static bool DatabaseExists(string name, SqlConnection conn)
{
string q = @"SELECT CASE
WHEN DB_ID('{0}') IS NULL THEN 0 ELSE 1 END";
q = string.Format(q, name);
OpenCbsCommand cmd = new OpenCbsCommand {
CommandText = q, Connection = conn
};
return(Convert.ToBoolean(cmd.ExecuteScalar()));
}
public void DeleteFundingLineEvent(FundingLineEvent pFundingLineEvent, SqlTransaction sqlTransac)
{
const string sqlText = @"
UPDATE [FundingLineEvents]
SET [deleted]=1
WHERE [id] = @id";
using (OpenCbsCommand c = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
{
c.AddParam("@id", pFundingLineEvent.Id);
c.AddParam("@deleted", true);
c.ExecuteScalar();
}
}
public int AddGenericCreditContractIntoDatabase(bool disburse)
{
DeleteCreditContract();
int tiersId = AddGenericTiersIntoDatabase(OClientTypes.Group);
int projectId = AddGenericProjectIntoDatabase(tiersId);
OpenCbsCommand insertContract = new OpenCbsCommand("INSERT INTO [Contracts]([contract_code], [branch_code], [creation_date], [start_date], [close_date], [closed], [project_id]) VALUES " +
"('KH/130','SU','10/10/2004','10/10/2005','10/10/2006',0," + projectId + ") SELECT SCOPE_IDENTITY()", SqlConnection);
int contractId = int.Parse(insertContract.ExecuteScalar().ToString());
return(contractId);
}
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 int Add(SavingEvent pSavingEvent, int pSavingContractId, SqlTransaction sqlTransac)
{
const string q = @"INSERT INTO [SavingEvents](
[user_id],
[contract_id],
[code],
[amount],
[description],
[reference_number],
[deleted],
[creation_date],
[cancelable],
[is_fired],
[related_contract_code],
[fees],
[savings_method],
[pending],
[is_local],
[pending_event_id],
[teller_id],
[loan_event_id])
VALUES(
@user_id,
@contract_id,
@code,
@amount,
@description,
@reference_number,
@deleted,
@creation_date,
@cancelable,
@is_fired,
@related_contract_code,
@fees,
@savings_method,
@pending,
@is_local,
@pending_event_id,
@teller_id,
@loan_event_id)
SELECT CONVERT(int, SCOPE_IDENTITY())" ;
using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransac.Connection, sqlTransac))
{
SetInsertCommandForSavingEvent(c, pSavingEvent, pSavingContractId);
pSavingEvent.Id = Convert.ToInt32(c.ExecuteScalar());
return(pSavingEvent.Id);
}
}
public double GetNearestRate(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 static string GetDatabasePath(SqlConnection pSqlConnection)
{
const string sqlText =
@"
SELECT physical_name
FROM [master].sys.master_files
WHERE database_id = 1 AND FILE_ID = 1
";
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
string dbFilePath = (string)select.ExecuteScalar();
return(Path.GetDirectoryName(dbFilePath));
}
}
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 Cur.id=@currency_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@currency_id", currencyId);
return (int)c.ExecuteScalar();
}
}
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 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 InstallmentType AddBiWeeklyInstallmentType()
{
InstallmentType biWeekly = new InstallmentType();
biWeekly.Name = "Bi-Weekly";
biWeekly.NbOfDays = 14;
biWeekly.NbOfMonths = 0;
string sqlText = "INSERT INTO InstallmentTypes (name,nb_of_days,nb_of_months) VALUES ('" + biWeekly.Name + "'," + biWeekly.NbOfDays + "," + biWeekly.NbOfMonths + ") SELECT SCOPE_IDENTITY()";
OpenCbsCommand insert = new OpenCbsCommand(sqlText, SqlConnection);
biWeekly.Id = int.Parse(insert.ExecuteScalar().ToString());
return(biWeekly);
}
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 Cur.id=@currency_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@currency_id", currencyId);
return((int)c.ExecuteScalar());
}
}
public int AddFundingLineEvent(FundingLineEvent pFundingLineEvent, SqlTransaction pTransac)
{
const string sqlText =
@"INSERT INTO [FundingLineEvents]
([code],
[amount],
[direction],
[fundingline_id],
[deleted],
[creation_date],
[type],
user_id,
contract_event_id)
VALUES
(@code,
@amount,
@direction,
@fundingLineId,
@deleted,
@creationDate,
@type,
@user_id,
@contract_event_id
)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = pTransac == null
? new OpenCbsCommand(sqlText, conn)
: new OpenCbsCommand(sqlText, pTransac.Connection, pTransac))
{
cmd.AddParam("@code", pFundingLineEvent.Code);
cmd.AddParam("@amount", pFundingLineEvent.Amount);
cmd.AddParam("@direction", (int)pFundingLineEvent.Movement);
cmd.AddParam("@fundingLineId", pFundingLineEvent.FundingLine.Id);
// pFundingLineId);
cmd.AddParam("@deleted", false);
cmd.AddParam("@creationDate", pFundingLineEvent.CreationDate);
cmd.AddParam("@type", (int)pFundingLineEvent.Type);
cmd.AddParam("@user_id", _user == null? (object)null:_user.Id);
cmd.AddParam("contract_event_id", pFundingLineEvent.AttachTo == null ?
(object)null: pFundingLineEvent.AttachTo.Id);
pFundingLineEvent.Id = Convert.ToInt32(cmd.ExecuteScalar());
return(pFundingLineEvent.Id);
}
}
public static string GetDatabaseNameForAccount(string pAccountName, SqlConnection pSqlConnection)
{
const string sqlText = @"SELECT database_name
FROM Accounts.dbo.SqlAccounts
WHERE account_name = @account";
if (pSqlConnection.State == ConnectionState.Closed)
{
pSqlConnection.Open();
}
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
select.AddParam("@account", pAccountName);
return((string)select.ExecuteScalar());
}
}
public int AddAccountingRule(IAccountingRule pRule)
{
const string sqlText = @"INSERT INTO [AccountingRules] (
[debit_account_number_id],
[credit_account_number_id],
[rule_type],
[booking_direction],
[event_type],
[event_attribute_id],
[order],
[description])
VALUES (@debit_account_number_id,
@credit_account_number_id,
@rule_type,
@booking_direction,
@event_type,
@event_attribute_id,
@order,
@description)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, conn))
{
insert.AddParam("@debit_account_number_id", pRule.DebitAccount.Id);
insert.AddParam("@credit_account_number_id", pRule.CreditAccount.Id);
insert.AddParam("@rule_type", pRule is ContractAccountingRule ? 'C' : 'F');
insert.AddParam("@booking_direction", (int)pRule.BookingDirection);
insert.AddParam("@event_type", pRule.EventType.EventCode);
insert.AddParam("@event_attribute_id", pRule.EventAttribute.Id);
insert.AddParam("@order", pRule.Order);
insert.AddParam("@description", pRule.Description);
pRule.Id = Convert.ToInt32(insert.ExecuteScalar());
}
}
if (pRule is ContractAccountingRule)
{
AddContractAccountingRule(pRule as ContractAccountingRule);
}
return(pRule.Id);
}
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 int AddGenericFundingLine4()
{
OpenCbsCommand delete = new OpenCbsCommand(@"DELETE FROM FundingLineEvents
where fundingline_id = (SELECT id FROM FundingLines where name = 'AFD_TEST')", SqlConnection);
delete.ExecuteNonQuery();
OpenCbsCommand delete2 = new OpenCbsCommand(@"DELETE FROM FundingLines WHERE name = 'AFD_TEST'", SqlConnection);
delete2.ExecuteNonQuery();
OpenCbsCommand insert = new OpenCbsCommand(@"INSERT INTO FundingLines (name,begin_date,end_date,amount,purpose,deleted, currency_id)
VALUES ('AFD_TEST',@startDate, @endDate,1000,'TEST',0,1) SELECT SCOPE_IDENTITY()", SqlConnection);
insert.AddParam("@startDate", DateTime.Now);
insert.AddParam("@endDate", DateTime.Now);
return(Convert.ToInt32(insert.ExecuteScalar()));
}
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 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 int Add(EmailAccount emailAccount)
{
const string q = @"INSERT INTO dbo.EmailAccounts
(Email, DisplayName, Host, Port, Username, Password, EnableSsl, UseDefaultCredentials, IsDefaultEmailAccount, Deleted)
VALUES (@email, @displayName, @host, @port, @userName, @password, @enableSSL, @useDefaultCred, @isDefault, @deleted)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@email", emailAccount.Email);
c.AddParam("@displayName", emailAccount.DisplayName);
c.AddParam("@host", emailAccount.Host);
c.AddParam("@port", emailAccount.Port);
c.AddParam("@userName", emailAccount.Username);
c.AddParam("@password", emailAccount.Password);
c.AddParam("@enableSSL", emailAccount.EnableSsl);
c.AddParam("@useDefaultCred", emailAccount.UseDefaultCredentials);
c.AddParam("@isDefault", emailAccount.IsDefaultEmailAccount);
c.AddParam("@deleted", false);
return(int.Parse(c.ExecuteScalar().ToString()));
}
}
public int Add(QueuedSMS queuedSMS)
{
const string q = @"INSERT INTO dbo.QueuedSMS
([From], [Recipient], [RecipientId], [ContractId], [Charged], [Message], CreatedOnUtc, SentOnUtc, SentTries, Deleted)
VALUES (@From, @Recipient, @RecipientId, @ContractId, @Charged, @Message, @CreatedOnUtc, @SentOnUtc, @SentTries, @Deleted)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@From", queuedSMS.From);
c.AddParam("@Recipient", queuedSMS.Recipient);
c.AddParam("@RecipientId", queuedSMS.RecipientId);
c.AddParam("@ContractId", queuedSMS.ContractId);
c.AddParam("@Charged", queuedSMS.Charged);
c.AddParam("@Message", queuedSMS.Message);
c.AddParam("@CreatedOnUtc", queuedSMS.CreatedOnUtc);
c.AddParam("@SentOnUtc", queuedSMS.SentOnUtc);
c.AddParam("@SentTries", queuedSMS.SentTries);
c.AddParam("@Deleted", false);
return(int.Parse(c.ExecuteScalar().ToString()));
}
}
public int Add(MessageTemplate messageTemplate)
{
const string q = @"INSERT INTO dbo.MessageTemplates
(Name, BccEmailAddresses, Subject, Body, EmailBody, IsActive, SendEmail, SendSMS, EmailAccountId, IsDefault, Deleted)
VALUES (@Name, @BccEmailAddresses, @Subject, @Body, @EmailBody, @IsActive, @SendEmail, @SendSMS, @EmailAccountId, @IsDefault, @Deleted)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@Name", messageTemplate.Name);
c.AddParam("@BccEmailAddresses", messageTemplate.BccEmailAddresses);
c.AddParam("@Subject", messageTemplate.Subject);
c.AddParam("@Body", messageTemplate.Body);
c.AddParam("@EmailBody", messageTemplate.EmailBody);
c.AddParam("@SendEmail", messageTemplate.SendEmail);
c.AddParam("@SendSMS", messageTemplate.SendSMS);
c.AddParam("@IsActive", messageTemplate.IsActive);
c.AddParam("@EmailAccountId", messageTemplate.EmailAccountId);
c.AddParam("@IsDefault", messageTemplate.IsDefault);
c.AddParam("@Deleted", false);
return(int.Parse(c.ExecuteScalar().ToString()));
}
}
private void _SetLoanShareAmount(Loan pLoan, SqlTransaction pSqlTransac)
{
// Get group id
int group_id;
const string q = @"SELECT p.tiers_id
FROM dbo.Projects AS p
LEFT JOIN dbo.Contracts AS c ON c.project_id = p.id
WHERE c.id = @contract_id";
using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
c.AddParam("@contract_id", pLoan.Id);
group_id = Convert.ToInt32(c.ExecuteScalar());
}
const string sqlText = @"INSERT INTO LoanShareAmounts (person_id, group_id, contract_id, amount)
VALUES (@person_id, @group_id, @contract_id, @amount)";
using(OpenCbsCommand c = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
{
foreach (LoanShare ls in pLoan.LoanShares)
{
c.ResetParams();
c.AddParam("@person_id", ls.PersonId);
c.AddParam("@group_id", group_id);
c.AddParam("@contract_id", pLoan.Id);
c.AddParam("@amount", ls.Amount.Value);
c.ExecuteNonQuery();
}
}
}
private int _AddContract(Loan pContract, int pProjectId, SqlTransaction pSqlTransac)
{
const string q = @"INSERT INTO [Contracts]([contract_code],
[branch_code],
[closed],
[creation_date],
[start_date],
[align_disbursed_date],
[close_date],
[project_id],
[status],
[credit_commitee_date],
[credit_commitee_comment],
[credit_commitee_code],
[loan_purpose],
[comments],
[nsg_id],
[activity_id],
preferred_first_installment_date)
VALUES(@code,
@branchCode,
@closed,
@creationDate,
@startDate,
@align_disbursed_date,
@closeDate,
@projectId,
@status,
@creditCommiteeDate,
@creditCommiteeComment,
@creditCommiteeCode,
@loanPurpose,
@comments,
@NsgID,
@activityId,
@preferredFirstInstallmentDate)
SELECT SCOPE_IDENTITY()";
using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
c.AddParam("@code", "fake_code");
c.AddParam("@status", (int) pContract.ContractStatus);
c.AddParam("@creditCommiteeDate", pContract.CreditCommiteeDate);
c.AddParam("@creditCommiteeComment", pContract.CreditCommiteeComment);
c.AddParam("@creditCommiteeCode", pContract.CreditCommitteeCode);
c.AddParam("@branchCode", pContract.BranchCode);
c.AddParam("@closed", pContract.Closed);
c.AddParam("@creationDate", pContract.CreationDate);
c.AddParam("@startDate", pContract.StartDate);
c.AddParam("@align_disbursed_date", pContract.AlignDisbursementDate);
c.AddParam("@closeDate", pContract.CloseDate);
c.AddParam("@projectId", pProjectId);
c.AddParam("@loanPurpose", pContract.LoanPurpose);
c.AddParam("@comments", pContract.Comments);
c.AddParam("@NsgID", pContract.NsgID);
c.AddParam("activityId", pContract.EconomicActivityId);
c.AddParam("@preferredFirstInstallmentDate",pContract.FirstInstallmentDate);
pContract.Id = Convert.ToInt32(c.ExecuteScalar());
}
if (string.IsNullOrEmpty(pContract.Code)) pContract.Code = "-";
if (pContract.Guarantors.Count != 0)
foreach (Guarantor guarantor in pContract.Guarantors)
_AddGuarantor(guarantor, pContract.Id, pSqlTransac);
if (pContract.Collaterals.Count != 0)
foreach (ContractCollateral contractCollateral in pContract.Collaterals)
AddCollateral(contractCollateral, pContract.Id, pSqlTransac);
// Compulsory savings handling
//if (pContract.Product.UseCompulsorySavings)
if (pContract.CompulsorySavings != null)
{
const string sqlText = @"INSERT INTO LoansLinkSavingsBook ([loan_id], [savings_id], [loan_percentage])
VALUES (@loanId, @savingsId, @loanPercentage)";
using (OpenCbsCommand c = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
{
if (pContract.CompulsorySavings != null)
c.AddParam("@savingsId", pContract.CompulsorySavings.Id);
else
c.AddParam("@savingsId", null);
c.AddParam("@loanId", pContract.Id);
c.AddParam("@loanPercentage", pContract.CompulsorySavingsPercentage);
c.ExecuteNonQuery();
}
}
return pContract.Id;
}
public void AddCollateral(ContractCollateral contractCollateral, int contractId, SqlTransaction pSqlTransac)
{
string q = @"INSERT INTO [CollateralsLinkContracts] ([contract_id])
VALUES (@contract_id) SELECT CONVERT(int, SCOPE_IDENTITY())";
using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
c.AddParam("@contract_id", contractId);
contractCollateral.Id = Convert.ToInt32(c.ExecuteScalar());
}
foreach (CollateralPropertyValue propertyValue in contractCollateral.PropertyValues)
{
AddCollateralPropertyValue(contractCollateral, propertyValue, pSqlTransac);
}
}
public int AddGenericFundingLine()
{
OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO FundingLines (name,amount, purpose, deleted, currency_id, begin_date, end_date) VALUES ('AFD130',0, 'Not Set', 0, " + AddGenericCurrency() + ", '20080101', '20120101') SELECT SCOPE_IDENTITY()", _connection);
return(int.Parse(insert.ExecuteScalar().ToString()));
}
public int CreateClosure(int countOfTransactions)
{
const string sqlText =
@"INSERT INTO dbo.AccountingClosure
( user_id ,
date_of_closure ,
count_of_transactions
)
VALUES (@user_id,
@date,
@count
)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand command = new OpenCbsCommand(sqlText, conn))
{
command.AddParam("@user_id", User.CurrentUser.Id);
command.AddParam("@date", TimeProvider.Now);
command.AddParam("@count", countOfTransactions);
return int.Parse(command.ExecuteScalar().ToString());
}
}
}
public bool IsLoanDisbursed(int contractId)
{
const string query =
"SELECT [disbursed] FROM Credit WHERE id = @contractId";
using (var connection = GetConnection())
using (var cmd = new OpenCbsCommand(query, connection))
{
cmd.AddParam("contractId", contractId);
var result = cmd.ExecuteScalar();
return result != null && Convert.ToBoolean(result);
}
}
private static void BackupToFileImpl(string pTempSavingPath, string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlRecoveryMode1 = String.Format("ALTER DATABASE {0} SET RECOVERY SIMPLE", pDatabaseName);
string sqlAutoShrink2 = String.Format("ALTER DATABASE {0} SET AUTO_SHRINK ON", pDatabaseName);
string sqlBackupFile3 = String.Format(@"
DECLARE
@DataBaseName varchar(255),
@BackupFile varchar(255)
SET @DataBaseName = '{0}'
SET @BackupFile = N'{1}'
BACKUP DATABASE @DataBaseName TO DISK= @BackupFile
WITH FORMAT", pDatabaseName, pTempSavingPath);
string sqlTruncLogFile4 = String.Format(@"
DECLARE
@DestDataBaseName varchar(255)
SET @DestDataBaseName = '{0}'
BACKUP LOG @DestDataBaseName WITH TRUNCATE_ONLY", pDatabaseName);
string sqlTruncLogFile5 = String.Format(@"
DECLARE
@DestDataBaseName varchar(255),
@LogName varchar(255)
SET @DestDataBaseName = '{0}'
SET @LogName = (SELECT name from sysfiles where groupid = 0)
DBCC SHRINKFILE(@Logname)", pDatabaseName);
const string sqlSqlServerVersion
= @"SELECT CONVERT(INTEGER, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('productversion'))))";
// Ensure recovery mode is FULL
var cmd = new OpenCbsCommand(sqlRecoveryMode1, pSqlConnection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
//Ensure auto shrink is on
cmd = new OpenCbsCommand(sqlAutoShrink2, pSqlConnection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
// Backup data int file
cmd = new OpenCbsCommand(sqlBackupFile3, pSqlConnection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
// Trunc transaction log
cmd = new OpenCbsCommand(sqlSqlServerVersion, pSqlConnection);
cmd.CommandTimeout = 300;
var sqlVersion = (int)cmd.ExecuteScalar();
if (sqlVersion < 10) // If SQL Server is 2000 or 2005
{
cmd = new OpenCbsCommand(sqlTruncLogFile4, pSqlConnection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
}
else // If SQL Server is 2008 or higher
{
cmd = new OpenCbsCommand(sqlTruncLogFile5, pSqlConnection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
}
}
public int Add(SavingEvent pSavingEvent, int pSavingContractId, SqlTransaction sqlTransac)
{
const string q = @"INSERT INTO [SavingEvents](
[user_id],
[contract_id],
[code],
[amount],
[description],
[deleted],
[creation_date],
[cancelable],
[is_fired],
[related_contract_code],
[fees],
[savings_method],
[pending],
[pending_event_id],
[teller_id],
[loan_event_id])
VALUES(
@user_id,
@contract_id,
@code,
@amount,
@description,
@deleted,
@creation_date,
@cancelable,
@is_fired,
@related_contract_code,
@fees,
@savings_method,
@pending,
@pending_event_id,
@teller_id,
@loan_event_id)
SELECT CONVERT(int, SCOPE_IDENTITY())";
using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransac.Connection, sqlTransac))
{
SetInsertCommandForSavingEvent(c, pSavingEvent, pSavingContractId);
pSavingEvent.Id = Convert.ToInt32(c.ExecuteScalar());
return pSavingEvent.Id;
}
}
public int AddAccountingRule(IAccountingRule pRule)
{
const string sqlText = @"INSERT INTO [AccountingRules] (
[debit_account_number_id],
[credit_account_number_id],
[rule_type],
[booking_direction],
[event_type],
[event_attribute_id],
[order],
[description])
VALUES (@debit_account_number_id,
@credit_account_number_id,
@rule_type,
@booking_direction,
@event_type,
@event_attribute_id,
@order,
@description)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, conn))
{
insert.AddParam("@debit_account_number_id", pRule.DebitAccount.Id);
insert.AddParam("@credit_account_number_id", pRule.CreditAccount.Id);
insert.AddParam("@rule_type", pRule is ContractAccountingRule ? 'C' : 'F');
insert.AddParam("@booking_direction", (int)pRule.BookingDirection);
insert.AddParam("@event_type", pRule.EventType.EventCode);
insert.AddParam("@event_attribute_id", pRule.EventAttribute.Id);
insert.AddParam("@order", pRule.Order);
insert.AddParam("@description", pRule.Description);
pRule.Id = Convert.ToInt32(insert.ExecuteScalar());
}
}
if (pRule is ContractAccountingRule)
AddContractAccountingRule(pRule as ContractAccountingRule);
return pRule.Id;
}
public int InsertAccountCategory(AccountCategory accountCategory, SqlTransaction pSqlTransac)
{
const string sqlText = @"INSERT INTO AccountsCategory (name) VALUES (@name) SELECT SCOPE_IDENTITY()";
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
{
insert.AddParam("@name", accountCategory.Name);
return int.Parse(insert.ExecuteScalar().ToString());
}
}
public bool NumbersExist(string[] accountNumbers)
{
string query = string.Format(
"IF EXISTS(SELECT 1 FROM ChartOfAccounts WHERE account_number in ({0})) SELECT 1 ELSE SELECT 0",
string.Join(",", accountNumbers)
);
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand command = new OpenCbsCommand(query, connection))
return Convert.ToInt32(command.ExecuteScalar()) == 1;
}
public void Insert(Account account, SqlTransaction sqlTransac, bool setIdentity)
{
int rightMostSibling;
string sqlText;
if (null == account.ParentAccountId || 0 == account.ParentAccountId)
{
sqlText = @"SELECT CASE
WHEN MAX(rgt) IS NULL THEN 1
ELSE MAX(rgt) + 1
END
FROM dbo.ChartOfAccounts";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
{
rightMostSibling = Convert.ToInt32(cmd.ExecuteScalar());
}
}
else
{
sqlText = @"SELECT rgt
FROM dbo.ChartOfAccounts
WHERE id = @parent_account_id";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
{
cmd.AddParam("@parent_account_id", account.ParentAccountId);
rightMostSibling = Convert.ToInt32(cmd.ExecuteScalar());
}
}
sqlText = @"UPDATE dbo.ChartOfAccounts
SET lft = CASE
WHEN lft > @right_most_sibling THEN lft + 2
ELSE lft
END
, rgt = CASE
WHEN rgt >= @right_most_sibling THEN rgt + 2
ELSE rgt
END
WHERE rgt >= @right_most_sibling";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
{
cmd.AddParam("@right_most_sibling", rightMostSibling);
cmd.ExecuteNonQuery();
account.Left = rightMostSibling;
account.Right = account.Left + 1;
}
if (setIdentity)
{
sqlText = @"
SET IDENTITY_INSERT [ChartOFAccounts] ON
INSERT INTO ChartOFAccounts (
id,
account_number,
label,
debit_plus,
type_code,
account_category_id,
type,
parent_account_id,
lft,
rgt)
VALUES (
@id,
@number,
@label,
@debitPlus,
@typeCode,
@account_category_id,
@type,
@parentAccountId,
@lft,
@rgt)
SET IDENTITY_INSERT [ChartOFAccounts] OFF
";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
{
cmd.AddParam("@id", account.Id);
SetAccount(cmd, account);
cmd.ExecuteNonQuery();
}
}
else
{
sqlText =
@"INSERT INTO ChartOFAccounts (
account_number,
label,
debit_plus,
type_code,
account_category_id,
type,
parent_account_id,
lft,
rgt)
VALUES (
@number,
@label,
@debitPlus,
@typeCode,
@account_category_id,
@type,
@parentAccountId,
@lft,
@rgt)";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
{
SetAccount(cmd, account);
cmd.ExecuteNonQuery();
}
}
}
public bool IdsExist(int[] ids)
{
string[] idStrings = ids.Select(i => i.ToString(CultureInfo.InvariantCulture)).ToArray();
string query = string.Format(
"IF EXISTS(SELECT 1 FROM ChartOfAccounts WHERE id in ({0})) SELECT 1 ELSE SELECT 0",
string.Join(",", idStrings)
);
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand command = new OpenCbsCommand(query, connection))
return Convert.ToInt32(command.ExecuteScalar()) == 1;
}
public void AddGeneralParameterIntoDatabase()
{
OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO [GeneralParameters]([key], [value]) VALUES('ID_PATTERN', '/d/d/w/w') SELECT SCOPE_IDENTITY()", _connection);
insert.ExecuteScalar();
}
public decimal GetGlobalOLBForProvisionning()
{
const string q = @"SELECT ISNULL(SUM(Installments.capital_repayment - Installments.paid_capital),0)
FROM Credit
INNER JOIN Installments ON Credit.id = Installments.contract_id
WHERE (Credit.disbursed = 1)
AND (Credit.written_off = 0)
AND (Credit.bad_loan = 0)";
using (SqlConnection conn = GetConnection())
using(OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
return Convert.ToDecimal(c.ExecuteScalar());
}
}
public int GetNumberOfSavings(int clientId)
{
const string sqlText = @"SELECT COUNT(id) FROM SavingContracts WHERE SavingContracts.tiers_id = @tiers_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@tiers_id", clientId);
return (int)select.ExecuteScalar();
}
}
public int GetNbOfLoansForClosure()
{
const string q = @"SELECT COUNT(Credit.id) FROM Credit WHERE Credit.disbursed = 1 AND Credit.written_off = 0 AND
(NOT ((SELECT SUM(interest_repayment) + SUM(capital_repayment) - SUM(paid_interest) - SUM(paid_capital)
FROM Installments WHERE contract_id = Credit.id) < 0.02))";
using (SqlConnection conn = GetConnection())
using(OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
return Convert.ToInt32(c.ExecuteScalar());
}
}
public static string GetDatabaseNameForAccount(string pAccountName, SqlConnection pSqlConnection)
{
const string sqlText = @"SELECT database_name
FROM Accounts.dbo.SqlAccounts
WHERE account_name = @account";
if (pSqlConnection.State == ConnectionState.Closed)
pSqlConnection.Open();
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
select.AddParam("@account", pAccountName);
return (string)select.ExecuteScalar();
}
}
public int SelectLoanID(string pLoanContractCode)
{
const string q = @"SELECT ID
FROM Contracts
WHERE contract_code = @contractCode";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@contractCode", pLoanContractCode);
object val = c.ExecuteScalar();
return null == val ? 0 : Convert.ToInt32(val);
}
}
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 int Add(ISavingsContract savings, Client client, SqlTransaction sqlTransac)
{
const string sqlText = @"INSERT INTO [SavingContracts]
(
[product_id],
[user_id],
[code],
[status],
[tiers_id],
[creation_date],
[interest_rate],
[closed_date],
[savings_officer_id],
[initial_amount],
[entry_fees],
[nsg_id]
)
VALUES
(
@product_id,
@user_id,
@code,
@status,
@tiers_id,
@creation_date,
@interest_rate,
@closedDate,
@savings_officer_id,
@initial_amount,
@entry_fees,
@nsg_id
)
SELECT CONVERT(int, SCOPE_IDENTITY())";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
{
cmd.AddParam("@product_id", savings.Product.Id);
cmd.AddParam("@user_id", savings.User.Id);
cmd.AddParam("@code", savings.Code);
cmd.AddParam("@status", (int)savings.Status);
cmd.AddParam("@tiers_id", client.Id);
cmd.AddParam("@creation_date", savings.CreationDate);
cmd.AddParam("@interest_rate", savings.InterestRate);
cmd.AddParam("@closedDate", savings.ClosedDate);
cmd.AddParam("@savings_officer_id", savings.SavingsOfficer.Id);
cmd.AddParam("@initial_amount", savings.InitialAmount);
cmd.AddParam("@entry_fees", savings.EntryFees);
cmd.AddParam("@nsg_id", savings.NsgID);
savings.Id = int.Parse(cmd.ExecuteScalar().ToString());
}
AddSavingsBookContract((SavingBookContract)savings, sqlTransac);
return savings.Id;
}
public int AddGenericCurrency()
{
OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO Currencies (name, code, is_pivot, is_swapped) VALUES ('USD', 'USD', 1, 0) SELECT SCOPE_IDENTITY()", _connection);
return(int.Parse(insert.ExecuteScalar().ToString()));
}
public static bool DatabaseExists(string name, SqlConnection conn)
{
string q = @"SELECT CASE
WHEN DB_ID('{0}') IS NULL THEN 0 ELSE 1 END";
q = string.Format(q, name);
OpenCbsCommand cmd = new OpenCbsCommand {CommandText = q, Connection = conn};
return Convert.ToBoolean(cmd.ExecuteScalar());
}
public void UpdateLoanShare(LoanShare pLoanShare, int pLoanId, int pGroupId, SqlTransaction pSqlTransac)
{
// Check if share exists
const string q = @"SELECT COUNT(*)
FROM dbo.LoanShareAmounts
WHERE person_id = @person_id
AND group_id = @group_id
AND contract_id = @contract_id";
bool exists;
using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
c.AddParam("@person_id", pLoanShare.PersonId);
c.AddParam("@group_id", pGroupId);
c.AddParam("@contract_id", pLoanId);
c.AddParam("@amount", pLoanShare.Amount);
exists = Convert.ToInt32(c.ExecuteScalar()) > 0;
}
string query;
if (exists)
{
query = @"UPDATE LoanShareAmounts
SET amount = @amount
WHERE person_id = @person_id
AND group_id = @group_id
AND contract_id = @contract_id";
}
else
{
query = @"INSERT INTO LoanShareAmounts (person_id, group_id, contract_id, amount)
VALUES (@person_id, @group_id, @contract_id, @amount)";
}
using(OpenCbsCommand c = new OpenCbsCommand(query, pSqlTransac.Connection, pSqlTransac))
{
c.AddParam("@person_id", pLoanShare.PersonId);
c.AddParam("@group_id", pGroupId);
c.AddParam("@contract_id", pLoanId);
c.AddParam("@amount", pLoanShare.Amount);
c.ExecuteNonQuery();
}
}
public static string GetDatabasePath(SqlConnection pSqlConnection)
{
const string sqlText =
@"
SELECT physical_name
FROM [master].sys.master_files
WHERE database_id = 1 AND FILE_ID = 1
";
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
string dbFilePath = (string)select.ExecuteScalar();
return Path.GetDirectoryName(dbFilePath);
}
}
public void UpdateMeetingAttendees(VillageAttendee attendee)
{
if (attendee.Id == 0)
{
const string q = @"INSERT INTO dbo.VillagesAttendance (village_id, person_id, [date], attended, comment, loan_id)
VALUES (@village_id, @person_id, @attended_date, @attended, @comment, @loan_id)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@attended", attendee.Attended);
c.AddParam("@comment", attendee.Comment);
c.AddParam("@village_id", attendee.VillageId);
c.AddParam("@person_id", attendee.TiersId);
c.AddParam("@attended_date", attendee.AttendedDate);
c.AddParam("@loan_id", attendee.LoanId);
attendee.Id = Convert.ToInt32(c.ExecuteScalar());
}
}
else
{
const string q = @"UPDATE dbo.VillagesAttendance
SET attended = @attended, comment = @comment
WHERE village_id = @village_id
AND person_id = @person_id
AND [date] = @attended_date";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@attended", attendee.Attended);
c.AddParam("@comment", attendee.Comment);
c.AddParam("@village_id", attendee.VillageId);
c.AddParam("@person_id", attendee.TiersId);
c.AddParam("@attended_date", attendee.AttendedDate);
c.ExecuteNonQuery();
}
}
}
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 string GetRepaymentModuleLastStartupDate()
{
const string sqlText = @"SELECT value
FROM dbo.TechnicalParameters
WHERE name='repayment_module_last_startup_date'";
using (var conn = GetConnection())
using (var select = new OpenCbsCommand(sqlText, conn))
{
var re = select.ExecuteScalar();
return re == null ? String.Empty : re.ToString();
}
}