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); }