public AccountRangeDTO selectAccountRange(AccountRangeTemplatesEnum accountRange, int companyId)
{
return this.data.selectAccountRange(accountRange, companyId);
}
public AccountRangeDTO selectAccountRange(AccountRangeTemplatesEnum accountRange, int companyId)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT AR.Id, CompanyId, AR.Name, AR.CodeFrom, AR.CodeTo, AT.Id AccountTypeId, AT.Type AccountType, BT.Id BalanceTypeId, BT.Type BalanceType, AR.CurrentAccount, AR.Removable FROM AccountRanges AR");
query.AppendLine("INNER JOIN AccountType AT ON AR.AccountTypeId = AT.Id");
query.AppendLine("INNER JOIN BalanceType BT ON AT.BalanceTypeId = BT.Id");
query.AppendLine("INNER JOIN AccountRangeTemplates ART ON ART.Id = @accountRangeTemplateId");
query.AppendLine("WHERE CompanyId = @companyId AND AR.Name = ART.Name;");
SqlCommand command = new SqlCommand(query.ToString(), this.conection);
command.CommandType = System.Data.CommandType.Text;
command.Parameters.AddWithValue("@companyId", companyId);
command.Parameters.AddWithValue("@accountRangeTemplateId", (int)accountRange);
AccountRangeDTO result = null;
try
{
this.conection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
result = new AccountRangeDTO(
(int)reader["Id"], companyId, reader["Name"].ToString(), (int)reader["CodeFrom"],
(int)reader["CodeTo"], new AccountTypeDTO((int)reader["AccountTypeId"],
new BalanceTypeDTO((int)reader["BalanceTypeId"], reader["BalanceType"].ToString()), reader["AccountType"].ToString()),
(bool)reader["CurrentAccount"], (bool)reader["Removable"]);
}
}
finally
{
if (this.conection.State == ConnectionState.Open)
this.conection.Close();
}
return result;
}
public int selectAccountCodeAvailable(AccountRangeTemplatesEnum accountRange, int companyId)
{
return this.data.selectAccountCodeAvailable(accountRange, companyId);
}
public int selectAccountCodeAvailable(AccountRangeTemplatesEnum accountRange, int companyId)
{
StringBuilder query = new StringBuilder();
query.AppendLine("DECLARE @index INT, @accountRangeId INT, @min INT, @max INT;");
query.AppendLine("SELECT @min = AR.CodeFrom, @max = AR.CodeTo, @accountRangeId = AR.Id FROM AccountRanges AR");
query.AppendLine("INNER JOIN AccountRangeTemplates ART ON ART.Id = @accountRangeTemplateId");
query.AppendLine("WHERE CompanyId = @companyId AND AR.Name = ART.Name;");
query.AppendLine("SET @index = @min + 1;");
query.AppendLine("WHILE (@index <= @max AND EXISTS(SELECT Id FROM Accounts WHERE CODE = @index AND AccountRangeId = @accountRangeId))");
query.AppendLine("BEGIN");
query.AppendLine("SET @index = @index + 1;");
query.AppendLine("END");
query.AppendLine("SELECT @index;");
SqlCommand command = new SqlCommand(query.ToString(), this.conection);
command.CommandType = System.Data.CommandType.Text;
command.Parameters.AddWithValue("@companyId", companyId);
command.Parameters.AddWithValue("@accountRangeTemplateId", (int)accountRange);
int id;
try
{
this.conection.Open();
id = (int)command.ExecuteScalar();
}
finally
{
if (this.conection.State == ConnectionState.Open)
this.conection.Close();
}
return id;
}
