private void btnSave_Click(object sender, EventArgs e)
{
MonetaryFund mf = this.cbMonetaryFunds.SelectedItem as MonetaryFund;
ExtraFund ef = new ExtraFund();
if (!String.IsNullOrEmpty(txtExtraFunds.Text))
{
ef.Name = txtExtraFunds.Text;
}
if (mf != null)
{
ef.MonetaryFund = mf;
}
try
{
_facade.InsertExtraFund(ef);
MessageBox.Show("Verba " + ef.Name + " cadastrada com sucesso como verba adicional de " + mf.Name + ".",
"Cadastro de Verbas de Base", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "Erro no Cadastro da Verba de Base", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public List <MonetaryFund> GetTop()
{
HashSet <MonetaryFund> hashMonetaryFunds = new HashSet <MonetaryFund>(new MonetaryFundComparer());
//List<MonetaryFund> monetaryFunds = hashMonetaryFunds.ToList();
NpgsqlDataReader reader = null;
try
{
string query = "SELECT mf.id as mf_id, mf.name as mf_name, mf.primal as mf_primal, ef.name as ef_name, ef.id as ef_id " +
"FROM monetary_funds mf LEFT JOIN " +
"extra_funds ef ON mf.id = ef.monetary_funds_id ORDER BY mf.id";
dal.OpenConnection();
reader = dal.ExecuteDataReader(query);
MonetaryFund monetaryFund;
ExtraFund extraFund;
while (reader.Read())
{
extraFund = null;
monetaryFund = new MonetaryFund();
monetaryFund.Id = Convert.ToInt64(reader["mf_id"]);
monetaryFund.Name = reader["mf_name"].ToString();
monetaryFund.Primal = Convert.ToBoolean(reader["mf_primal"]);
if (reader["ef_id"] != DBNull.Value && reader["ef_name"] != DBNull.Value)
{
extraFund = new ExtraFund();
extraFund.Id = Convert.ToInt64(reader["ef_id"]);
extraFund.Name = reader["ef_name"].ToString();
monetaryFund.ExtraFunds.Add(extraFund);
}
if (!hashMonetaryFunds.Add(monetaryFund))
{
foreach (var item in hashMonetaryFunds)
{
if (item.Id == monetaryFund.Id)
{
if (extraFund != null)
{
item.ExtraFunds.Add(extraFund);
}
break;
}
}
}
}
reader.Close();
}
finally
{
if (reader != null)
{
reader.Close();
}
this.dal.CloseConection();
}
return(new List <MonetaryFund>(hashMonetaryFunds));
}
private void btnSaveExtraFunds_Click(object sender, EventArgs e)
{
MonetaryFund mf = this.cbMonetaryFunds.SelectedItem as MonetaryFund;
ExtraFund ef = new ExtraFund();
if (!String.IsNullOrEmpty(txtExtraFunds.Text))
{
ef.Name = txtExtraFunds.Text;
}
if (mf != null)
{
ef.MonetaryFund = mf;
}
try
{
int retId = _facade.InsertExtraFund(ef);
if (retId > 0)
{
ef.Id = retId;
ListViewItem item;
this.extraFunds.Add(ef);
item = new ListViewItem();
item.Text = ef.Id.ToString();
item.SubItems.Add(ef.Name);
item.SubItems.Add(ef.MonetaryFund.Name);
this.listExtraFunds.Items.Add(item);
MessageBox.Show("Verba " + ef.Name + " cadastrada com sucesso como verba adicional de " + mf.Name + ".",
"Cadastro de Verbas Adicionais", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Verba " + ef.Name + " não retornou um ID de cadastro válido!",
"Cadastro de Verbas Adicionais", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "Erro no Cadastro da Verba de Base", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public int Insert(ExtraFund extraFund)
{
//int rowsAffected = -1;
object obj = null;
int returnedId = -1;
try
{
string cmdInsert = "INSERT INTO extra_funds(name, monetary_funds_id) VALUES (:name, :monetaryFundId) RETURNING id";
NpgsqlCommand cmd = new NpgsqlCommand(cmdInsert);
cmd.Parameters.Add(new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("monetaryFundId", NpgsqlTypes.NpgsqlDbType.Bigint));
cmd.Parameters[0].Value = extraFund.Name;
if (extraFund.MonetaryFund == null)
{
cmd.Parameters[1].Value = DBNull.Value;
}
else
{
cmd.Parameters[1].Value = extraFund.MonetaryFund.Id;
}
dal.OpenConnection();
obj = dal.ExecuteScalar(cmd);
if (obj != null)
{
returnedId = (int)obj;
}
}
finally
{
this.dal.CloseConection();
}
return(returnedId);
}
public void Update <K>(K id, ExtraFund extraFund)
{
int rowsAffected = -1;
try
{
NpgsqlCommand cmd = new NpgsqlCommand("UPDATE extra_funds set \"name\" = :name, \"monetary_funds_id\" = :monetaryFundId"
+ " WHERE \"id\" = '" + id + "' ;");
cmd.Parameters.Add(new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("monetaryFundId", NpgsqlTypes.NpgsqlDbType.Bigint));
cmd.Parameters[0].Value = extraFund.Name;
cmd.Parameters[1].Value = extraFund.MonetaryFund.Id;
dal.OpenConnection();
rowsAffected = dal.ExecuteNonQuery(cmd);
}
finally
{
this.dal.CloseConection();
}
}
public ExtraFund Get <K>(K id)
{
ExtraFund extraFund = new ExtraFund();
NpgsqlDataReader reader = null;
try
{
string cmdSelect = "SELECT ef.id as ef_id, ef.name as ef_name, mf.id as mf_id, mf.name as mf_name " +
"FROM (extra_funds ef INNER JOIN monetary_funds mf ON (ef.monetary_funds_id = mf.id)) " +
"WHERE ef.id = :passedId ORDER BY ef.id";
//string cmdSelect = "SELECT * FROM extra_funds WHERE id = " + id;
NpgsqlCommand cmd = new NpgsqlCommand(cmdSelect);
cmd.Parameters.Add(new NpgsqlParameter("passedId", NpgsqlTypes.NpgsqlDbType.Bigint));
cmd.Parameters[0].Value = id;
dal.OpenConnection();
reader = dal.ExecuteDataReader(cmd);
if (reader.Read())
{
extraFund.Id = Convert.ToInt64(reader["id"]);
extraFund.Name = reader["name"].ToString();
}
reader.Close();
}
finally
{
if (reader != null)
{
reader.Close();
}
this.dal.CloseConection();
}
return(extraFund);
}
public List <ExtraFund> GetTop()
{
List <ExtraFund> extraFunds = new List <ExtraFund>();
NpgsqlDataReader reader = null;
try
{
string query = "SELECT ef.id as ef_id, ef.name as ef_name, mf.id as mf_id, mf.name as mf_name " +
"FROM (extra_funds ef INNER JOIN monetary_funds mf ON (ef.monetary_funds_id = mf.id)) " +
"ORDER BY ef.id";
dal.OpenConnection();
reader = dal.ExecuteDataReader(query);
while (reader.Read())
{
ExtraFund extraFund = new ExtraFund();
extraFund.Id = Convert.ToInt64(reader["ef_id"]);
extraFund.Name = reader["ef_name"].ToString();
extraFund.MonetaryFund = new MonetaryFund(Convert.ToInt64(reader["mf_id"]),
reader["mf_name"].ToString(), true);
extraFunds.Add(extraFund);
}
reader.Close();
}
finally
{
if (reader != null)
{
reader.Close();
}
this.dal.CloseConection();
}
return(extraFunds);
}
public int InsertExtraFund(ExtraFund extraFund)
{
return(this._extraFundDAO.Insert(extraFund));
}
public void UpdateExtraFund(int id, ExtraFund extraFund)
{
this._extraFundDAO.Update <int>(id, extraFund);
}
//public Contract GetByName<K>(K name)
//{
// Contract contract = new Contract();
// NpgsqlDataReader reader = null;
// try
// {
// string SELECTCMD = "SELECT * FROM contracts WHERE matricula = '" + name + "'";
// dal.OpenConnection();
// reader = dal.ExecuteDataReader(SELECTCMD);
// if (reader.Read())
// {
// contract.Id = Convert.ToInt64(reader["id"]);
// contract.Name = reader["name"].ToString();
// contract.Matriculation = reader["matriculation"].ToString();
// contract.Birthday = Convert.ToDateTime(reader["birthday"]);
// contract.CurrentStartDate = Convert.ToDateTime(reader["start_date"]);
// contract.CurrentEndDate = Convert.ToDateTime(reader["end_date"]);
// contract.PIS = reader["pis"].ToString();
// contract.CPF = reader["cpf"].ToString();
// }
// reader.Close();
// }
// finally
// {
// if (reader != null)
// {
// reader.Close();
// }
// this.dal.CloseConection();
// }
// return contract;
//}
public List <Contract> GetTop()
{
List <Contract> contracts = new List <Contract>();
Contract contract = null;
//List<Department> departments = new List<Department>();
HashSet <MonetaryFund> monetaryFunds = new HashSet <MonetaryFund>(new MonetaryFundComparer());
List <ContingencyAliquot> contingencyAliquots = new List <ContingencyAliquot>();
NpgsqlDataReader reader = null;
try
{
NpgsqlCommand cmd;
string selectCMD = "SELECT * FROM contracts";
cmd = new NpgsqlCommand(selectCMD);
dal.OpenConnection();
reader = dal.ExecuteDataReader(cmd);
while (reader.Read())
{
contract = new Contract();
contract.Id = Convert.ToInt64(reader["id"]);
contract.Name = reader["name"].ToString();
contract.Description = reader["description"].ToString();
contract.Balance = Convert.ToDouble(reader["balance"]);
if (reader["start_date"] is DBNull)
{
contract.StartDate = DateTime.MinValue;
}
else
{
contract.StartDate = Convert.ToDateTime(reader["start_date"]);
}
if (reader["end_date"] is DBNull)
{
contract.EndDate = DateTime.MinValue;
}
else
{
contract.EndDate = Convert.ToDateTime(reader["end_date"]);
}
contracts.Add(contract);
}
reader.Close();
//Department department;
MonetaryFund monetaryFund;
ExtraFund extraFund;
ContingencyAliquot contingencyAliquot;
ContingencyFund contingencyFund;
string selectMonetaryFundsCMD = "SELECT c.id as c_id, c.name as c_name, c.start_date as c_start_date, c.end_date as c_end_date, " +
"cmf.id as cmf_id, cmf.contract_id as cmf_contract_id, cmf.monetary_fund_id as cmf_monetary_fund_id, " +
"mf.id as mf_id, mf.name as mf_name, mf.primal as mf_primal, " +
"ef.id as ef_id, ef.name as ef_name " +
"FROM (contracts c INNER JOIN contract_monetary_funds cmf ON " +
"(c.id = cmf.contract_id)) " +
"INNER JOIN monetary_funds mf ON (cmf.monetary_fund_id = mf.id) " +
"LEFT JOIN extra_funds ef ON (cmf.extra_fund_id = ef.id) " +
"WHERE c.id = :passedId ORDER BY c.id";
string selectContAliquotCMD = "SELECT c.id as c_id, c.name as c_name, c.start_date as c_start_date, c.end_date as c_end_date, " +
"ca.id as ca_id, ca.start_date as ca_start_date, ca.end_date as ca_end_date, ca.value as ca_value, " +
"cf.id as cf_id, cf.name as cf_name FROM (contracts c INNER JOIN contingency_aliquot ca ON " +
"(c.id = ca.contract_id)) " +
"LEFT JOIN contingency_funds cf ON (ca.contingency_fund_id = cf.id) " +
"WHERE c.id = :passedId ORDER BY c.id";
cmd.Parameters.Add(new NpgsqlParameter("passedId", NpgsqlTypes.NpgsqlDbType.Bigint));
foreach (Contract ct in contracts)
{
//Consultar as verbas monetárias do contrato
cmd.CommandText = selectMonetaryFundsCMD;
cmd.Parameters[0].Value = ct.Id;
monetaryFunds = new HashSet <MonetaryFund>(new MonetaryFundComparer());
contingencyAliquots = new List <ContingencyAliquot>();
reader = dal.ExecuteDataReader(cmd);
long currentMFId = 0;
while (reader.Read())
{
currentMFId = Convert.ToInt64(reader["mf_id"]);
if (reader["ef_id"] is DBNull && reader["ef_name"] is DBNull)
{
monetaryFund = new MonetaryFund(Convert.ToInt64(reader["mf_id"]), reader["mf_name"].ToString());
monetaryFunds.Add(monetaryFund);
}
else
{
extraFund = new ExtraFund(Convert.ToInt64(reader["ef_id"]), reader["ef_name"].ToString());
monetaryFund = new MonetaryFund(Convert.ToInt64(reader["mf_id"]), reader["mf_name"].ToString());
if (monetaryFunds.Add(monetaryFund))
{
monetaryFund.ExtraFunds.Add(extraFund);
}
else
{
foreach (MonetaryFund mf in monetaryFunds)
{
if (mf.Id == currentMFId)
{
mf.ExtraFunds.Add(extraFund);
}
}
}
}
}
reader.Close();
ct.MonetaryFunds = new List <MonetaryFund>(monetaryFunds);
//Consultar as alíquotas do contrato
cmd.CommandText = selectContAliquotCMD;
reader = dal.ExecuteDataReader(cmd);
while (reader.Read())
{
contingencyFund = new ContingencyFund(Convert.ToInt64(reader["cf_id"]), reader["cf_name"].ToString());
contingencyAliquot = new ContingencyAliquot(Convert.ToInt64(reader["ca_id"]), Convert.ToDouble(reader["ca_value"]),
Convert.ToDateTime(reader["ca_start_date"]), Convert.ToDateTime(reader["ca_end_date"]), contingencyFund);
contingencyAliquots.Add(contingencyAliquot);
}
reader.Close();
ct.ContingencyAliquot = contingencyAliquots;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
this.dal.CloseConection();
}
return(contracts);
}
