public int addAccountRange(AccountRangeDTO accountRange)
{
StringBuilder query = new StringBuilder();
query.AppendLine("INSERT INTO AccountRanges");
query.AppendLine("(CompanyId, CodeFrom, CodeTo, AccountTypeId, CurrentAccount, Name, Removable)");
query.AppendLine("VALUES (@companyId, @codeFrom, @codeTo, @accountTypeId, @currentAccount, @name, @removable);");
query.AppendLine("SELECT SCOPE_IDENTITY();");
SqlCommand command = new SqlCommand(query.ToString(), this.conection);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@companyId", accountRange.CompanyId);
command.Parameters.AddWithValue("@codeFrom", accountRange.CodeFrom);
command.Parameters.AddWithValue("@codeTo", accountRange.CodeTo);
command.Parameters.AddWithValue("@accountTypeId", accountRange.Type.Id);
command.Parameters.AddWithValue("@currentAccount", accountRange.CurrentAccount);
command.Parameters.AddWithValue("@name", accountRange.Name);
command.Parameters.AddWithValue("@removable", accountRange.Removable);
int id;
try
{
this.conection.Open();
id = (int)(decimal)command.ExecuteScalar();
}
finally
{
if (this.conection.State == ConnectionState.Open)
this.conection.Close();
}
return id;
}
public FormAccounts(DataValidation dataValidation, AccountRangeDTO accountRange)
{
this.dataValidation = dataValidation;
this.accountRange = accountRange;
InitializeComponent();
this.dgvAccounts.AutoGenerateColumns = false;
this.updateDataGrid();
}
public FormAddAccount(DataValidation dataValidation, AccountRangeDTO accountRange)
{
this.dataValidation = dataValidation;
this.accountRange = accountRange;
InitializeComponent();
this.nudCode.Minimum = accountRange.CodeFrom;
this.nudCode.Maximum = accountRange.CodeTo;
this.validationsAccount = new Dictionary<Control, ErrorProvider>();
this.validationsAccount.Add(tbName, epAccount);
}
public FormModifyAccount(DataValidation dataValidation, AccountRangeDTO accountRange, AccountDTO account)
: base(dataValidation, accountRange)
{
this.account = account;
InitializeComponent();
this.Text = "Modificación de cuenta";
this.bAccept.Text = "Modificar";
this.nudCode.Value = account.Code;
this.tbName.Text = account.Name;
this.cbRegularizingAccount.Checked = account.RegularizingAccount;
}
public void deleteAccountRange(AccountRangeDTO accountRange)
{
this.data.deleteAccountRange(accountRange);
}
public List<AccountDTO> selectAccounts(AccountRangeDTO accountRange)
{
return this.data.selectAccounts(accountRange);
}
public void saveAccountRange(AccountRangeDTO accountRange)
{
string name = this.getNameOfAccountRangeOverlapped(accountRange);
if (name != null)
throw new ArgumentException(String.Format("El rango ingresado se superpone con '{0}'", name));
if (accountRange.Id == null)
{
accountRange.Id = this.data.addAccountRange(accountRange);
}
else
{
this.data.modifyAccountRange(accountRange);
}
}
public string getNameOfAccountRangeOverlapped(AccountRangeDTO accountRange)
{
return this.data.getNameOfAccountRangeOverlapped(accountRange);
}
public List<AccountDTO> selectAccounts(AccountRangeDTO accountRange)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT Id, Code, AccountRangeId, Deleted, RegularizingAccount, Name FROM Accounts");
query.AppendLine("WHERE AccountRangeId = @accountRangeId");
query.AppendLine("ORDER BY ID;");
SqlCommand command = new SqlCommand(query.ToString(), this.conection);
command.CommandType = System.Data.CommandType.Text;
command.Parameters.AddWithValue("@accountRangeId", accountRange.Id);
List<AccountDTO> accounts = new List<AccountDTO>();
try
{
this.conection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
AccountDTO account = new AccountDTO((int)reader["Id"], (int)reader["Code"], reader["Name"].ToString(), accountRange, (bool)reader["RegularizingAccount"], (bool)reader["Deleted"]);
accounts.Add(account);
}
}
}
}
finally
{
if (this.conection.State == ConnectionState.Open)
this.conection.Close();
}
return accounts;
}
public List<AccountRangeDTO> selectAccountRangesByCompanyId(int companyId)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT AR.Id, CompanyId, AR.Name, CodeFrom, CodeTo, AT.Id AccountTypeId, AT.Type AccountType, BT.Id BalanceTypeId, BT.Type BalanceType, CurrentAccount, 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("WHERE CompanyId = @companyId");
query.AppendLine("ORDER BY CodeFrom;");
SqlCommand command = new SqlCommand(query.ToString(), this.conection);
command.CommandType = System.Data.CommandType.Text;
command.Parameters.AddWithValue("@companyId", companyId);
List<AccountRangeDTO> accountRanges = new List<AccountRangeDTO>();
try
{
this.conection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
AccountRangeDTO accountRange = new AccountRangeDTO(
(int)reader["Id"], (int)reader["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"]);
accountRanges.Add(accountRange);
}
}
}
}
finally
{
if (this.conection.State == ConnectionState.Open)
this.conection.Close();
}
return accountRanges;
}
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 void modifyAccountRange(AccountRangeDTO accountRange)
{
StringBuilder query = new StringBuilder();
query.AppendLine("UPDATE AccountRanges");
query.AppendLine("SET CompanyId = @companyId, CodeFrom = @codeFrom, CodeTo = @codeTo, AccountTypeId = @accountTypeId, Name = @name, CurrentAccount = @currentAccount, Removable = @removable");
query.AppendLine("WHERE Id = @id;");
SqlCommand command = new SqlCommand(query.ToString(), this.conection);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@id", accountRange.Id);
command.Parameters.AddWithValue("@companyId", accountRange.CompanyId);
command.Parameters.AddWithValue("@codeFrom", accountRange.CodeFrom);
command.Parameters.AddWithValue("@codeTo", accountRange.CodeTo);
command.Parameters.AddWithValue("@accountTypeId", accountRange.Type.Id);
command.Parameters.AddWithValue("@currentAccount", accountRange.CurrentAccount);
command.Parameters.AddWithValue("@name", accountRange.Name);
command.Parameters.AddWithValue("@removable", accountRange.Removable);
try
{
this.conection.Open();
command.ExecuteNonQuery();
}
finally
{
if (this.conection.State == ConnectionState.Open)
this.conection.Close();
}
}
public string getNameOfAccountRangeOverlapped(AccountRangeDTO accountRange)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT Name FROM AccountRanges");
query.AppendLine("WHERE Id != @id AND CompanyId = @idCompany AND ((CodeFrom BETWEEN @codeFrom AND @codeTo) OR (CodeTo BETWEEN @codeFrom AND @codeTo));");
SqlCommand command = new SqlCommand(query.ToString(), this.conection);
command.CommandType = System.Data.CommandType.Text;
if (accountRange.Id != null)
command.Parameters.AddWithValue("@id", accountRange.Id);
else
command.Parameters.AddWithValue("@id", SqlInt32.Null);
command.Parameters.AddWithValue("@idCompany", accountRange.CompanyId);
command.Parameters.AddWithValue("@codeFrom", accountRange.CodeFrom);
command.Parameters.AddWithValue("@codeTo", accountRange.CodeTo);
string name;
try
{
this.conection.Open();
object obj = command.ExecuteScalar();
name = obj == null ? null : obj.ToString();
}
finally
{
if (this.conection.State == ConnectionState.Open)
this.conection.Close();
}
return name;
}
public void deleteAccountRange(AccountRangeDTO accountRange)
{
StringBuilder query = new StringBuilder();
query.AppendLine("DELETE FROM AccountRanges");
query.AppendLine("WHERE Id = @id;");
SqlCommand command = new SqlCommand(query.ToString(), this.conection);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@id", accountRange.Id);
try
{
this.conection.Open();
command.ExecuteNonQuery();
}
finally
{
if (this.conection.State == ConnectionState.Open)
this.conection.Close();
}
}
