public int Insert(MonetaryFund monetaryFund)
{
//int rowsAffected = -1;
object obj = null;
int idReturned = -1;
try
{
string cmdInsert = "INSERT INTO monetary_funds(name, primal) VALUES (:name, :primal) RETURNING id";
NpgsqlCommand cmd = new NpgsqlCommand(cmdInsert);
cmd.Parameters.Add(new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("primal", NpgsqlTypes.NpgsqlDbType.Boolean));
cmd.Parameters[0].Value = monetaryFund.Name;
cmd.Parameters[1].Value = monetaryFund.Primal;
dal.OpenConnection();
obj = dal.ExecuteScalar(cmd);
if (obj != null)
{
idReturned = (int)obj;
}
}
finally
{
this.dal.CloseConection();
}
return(idReturned);
}
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);
}
}
private MonetaryFund _GetSelectedMonetaryFund()
{
int checkeds = 0;
//Varrendo o node pai (verbas monetárias)
MonetaryFund retMF = null;
MonetaryFund currentMF;
foreach (TreeNode mfNode in this.treeMonetaryFunds.Nodes)
{
if (mfNode.Checked)
{
checkeds++;
currentMF = (MonetaryFund)mfNode.Tag;
retMF = new MonetaryFund(currentMF.Id, currentMF.Name, currentMF.Primal);
//varrendo nodes filhos, se houver(verbas adicionais)
foreach (TreeNode efNode in mfNode.Nodes)
{
//Se o node filho não tiver marcado, removo ele da List do objeto MonetaryFund que vai ser retornada
if (efNode.Checked)
{
retMF.ExtraFunds.Add((ExtraFund)efNode.Tag);
}
}
}
}
if (checkeds != 1)
{
return(null);
}
else
{
return(retMF);
}
}
private void btnDoConting_Click(object sender, EventArgs e)
{
MonetaryFund selectedMF = _GetSelectedMonetaryFund();
if (selectedMF == null)
{
MessageBox.Show("Selecione apenas uma verba monetária.", "Verba Monetária", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
try
{
//desabilita os botões enquanto a tarefa é executada.
_EnableAllButtons(false);
bgWorkDatabase.RunWorkerAsync(selectedMF);
//define a progressBar para Marquee
pbContingency.Style = ProgressBarStyle.Marquee;
pbContingency.MarqueeAnimationSpeed = 6;
//informa que a tarefa esta sendo executada.
this.lblWaiting.Text = "Aguarde, processando e salvando na base de dados...";
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "Importação para Base de Dados", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
//Cliente cliente = this.cbClientes.SelectedItem as Cliente;
//Contrato contrato = this.cbContratos.SelectedItem as Contrato;
//Verba verba = this.cbVerbasFilter.SelectedItem as Verba;
//ContratoAliquota cAliq = new ContratoAliquota();
MonetaryFund mf = new MonetaryFund();
mf.Primal = true;
if (!String.IsNullOrEmpty(txtMonetaryFunds.Text))
{
mf.Name = txtMonetaryFunds.Text;
}
//if (contrato != null)
// cAliq.Contrato = contrato;
//if (verba != null)
// cAliq.Verba = verba;
//cAliq.Aliquota = Convert.ToDouble(txtAliquota.Text);
//cAliq.Ano = Convert.ToInt32(txtAno.Text);
try
{
_facade.InsertMonetaryFund(mf);
MessageBox.Show("Verba de Base " + mf.Name + " cadastrada com sucesso.",
"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 List <ContingencyPast> ProcessContingencyContract(Contract contract, MonetaryFund monetaryFund)
{
List <EmployeeHistory> employeeHistories = this._employeeHistoryDAO.GetByContract(contract, false);
List <ContingencyPast> contingencyPasts = new List <ContingencyPast>();
ContingencyPast cp;
ContingencyAliquot caToAdd;
foreach (EmployeeHistory eh in employeeHistories)
{
cp = new ContingencyPast();
cp.EmployeeHistory = eh;
cp.MonetaryFundName = monetaryFund.Name;
foreach (ContingencyAliquot ca in contract.ContingencyAliquot)
{
caToAdd = new ContingencyAliquot();
caToAdd.Id = ca.Id;
caToAdd.ContingencyFund = new ContingencyFund(ca.ContingencyFund.Id, ca.ContingencyFund.Name);
caToAdd.Value = ca.Value;
//essa parte tá horrorosa (calculando para cada Mês)
if (monetaryFund.Name.ToUpper().Equals("SALÁRIO BASE"))
{
caToAdd.CalculatedValue = Math.Round((ca.Value / 100) * eh.BaseSalary, 2);
}
else if (monetaryFund.Name.ToUpper().Equals("PROVENTOS TOTAIS"))
{
caToAdd.CalculatedValue = Math.Round((ca.Value / 100) * eh.TotalEarnings, 2);
}
else
{
caToAdd.CalculatedValue = Math.Round((ca.Value / 100) * eh.NetSalary, 2);
}
//if (ca.ContingencyFund.Name.ToUpper() == "FÉRIAS")
// caToAdd.CalculatedValue = Math.Round(System.Convert.ToDouble(ca.CalculatedValue/3), 2);
cp.ContingencyAliquots.Add(caToAdd);
}
contingencyPasts.Add(cp);
}
return(contingencyPasts);
}
private void btnSaveMonetaryFunds_Click(object sender, EventArgs e)
{
MonetaryFund mf = new MonetaryFund();
mf.Primal = true;
if (!String.IsNullOrEmpty(txtMonetaryFunds.Text))
{
mf.Name = txtMonetaryFunds.Text;
}
try
{
int retId = _facade.InsertMonetaryFund(mf);
if (retId > 0)
{
mf.Id = retId;
ListViewItem item;
this.monetaryFunds.Add(mf);
item = new ListViewItem();
item.Text = mf.Id.ToString();
item.SubItems.Add(mf.Name);
this.listMonetaryFunds.Items.Add(item);
this.cbMonetaryFunds.DataSource = this.monetaryFunds;
MessageBox.Show("Verba de Base " + mf.Name + " cadastrada com sucesso.",
"Cadastro de Verbas de Base", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Verba de Base " + mf.Name + " não retornou um ID de cadastro válido!",
"Cadastro de Verbas de Base", 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 void Update <K>(K id, MonetaryFund monetaryFund)
{
int rowsAffected = -1;
try
{
NpgsqlCommand cmd = new NpgsqlCommand("UPDATE monetary_funds set \"name\" = :name, \"primal\" = :primal"
+ " WHERE \"id\" = '" + id + "' ;");
cmd.Parameters.Add(new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("primal", NpgsqlTypes.NpgsqlDbType.Boolean));
cmd.Parameters[0].Value = monetaryFund.Name;
cmd.Parameters[1].Value = monetaryFund.Primal;
dal.OpenConnection();
rowsAffected = dal.ExecuteNonQuery(cmd);
}
finally
{
this.dal.CloseConection();
}
}
public int InsertMonetaryFund(MonetaryFund monetaryFund)
{
return(this._monetaryFundDAO.Insert(monetaryFund));
}
public void UpdateMonetaryFund(int id, MonetaryFund monetaryFund)
{
this._monetaryFundDAO.Update <int>(id, monetaryFund);
}
private void bgWorkDatabase_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
{
MonetaryFund selectedMF = (MonetaryFund)e.Argument;
try
{
contPasts = _facade.ProcessContingencyContract(this.currentContract, selectedMF);
if (contPasts.Count == 0)
{
MessageBox.Show("O histórico para esse contrato já se encontra contingenciado na base de dados.",
"Contingenciamento Já Realizado",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
double calcFerias = -1;
double calc13Sal = -1;
foreach (ContingencyPast cp in contPasts)
{
calcFerias = -1;
calc13Sal = -1;
foreach (ContingencyAliquot ca in cp.ContingencyAliquots)
{
if (ca.ContingencyFund.Name.ToUpper().Equals("FÉRIAS")) //TERIA QUE FAZER DINAMICO!
{
calcFerias = ca.CalculatedValue;
}
else if (ca.ContingencyFund.Name.ToUpper().Equals("13º SALÁRIO"))
{
calc13Sal = ca.CalculatedValue;
}
}
if (calcFerias != -1 && calc13Sal != -1)
{
foreach (ContingencyAliquot ca in cp.ContingencyAliquots)
{
if (ca.ContingencyFund.Name.ToUpper().Equals("INCIDÊNCIA")) //TERIA QUE FAZER DINAMICO!
{
ca.CalculatedValue = Math.Round((ca.Value / 100) * (calcFerias + calc13Sal), 2);
break;
}
}
}
}
int countRows = _facade.InsertContingencyPastList(contPasts);
this.selectedMFToContingency = selectedMF.Name;
_CreateDataSet(contPasts);
if (countRows > 0)
{//BRIR TELA SIM/NÃO para visualizar no form de visualização correto do contingenciamento
MessageBox.Show("O contingenciamento foi processado e salvo com sucesso na base de dados" +
"Contagem retornada: " + countRows, "Contingenciamento Salvo",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Ops, ocorreu um erro e o contingenciamento não foi adicionado a base de dados!", "Erro ao inserir contingenciamento na base de dados",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex.Message, "Erro ao salvar na base de dados", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//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);
}
