/*
* Parte relacionada a adição dos códigos de Depto ao contrato
*/
//private void btnAddDept_Click(object sender, EventArgs e)
//{
// string dptCode = this.txtDptCode.Text;
// Department dept = _facade.GetDepartmentByCode(dptCode);
// if (dept == null)
// {
// if (DialogResult.Yes == MessageBox.Show("Departamento não encontrado na base de dados, deseja adicioná-lo?", "Adicionar Departamento Novo",
// MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1))
// {
// FrmAddDepartment frmAddDepartment = new FrmAddDepartment(dptCode);
// frmAddDepartment.FormClosed += new FormClosedEventHandler(_FormAddDeptClosed);
// frmAddDepartment.ShowDialog();
// }
// }
// else
// {
// _TryAddingDept(dept);
// }
//}
//private void _TryAddingDept(Department dept)
//{
// if (this.allDeptCodes.Add(dept))
// {
// ListViewItem item;
// item = new ListViewItem();
// item.Text = dept.Code;
// item.SubItems.Add(dept.Name);
// this.listDepts.Items.Add(item);
// this.txtDptCode.Text = "";
// }
//}
//private void _FormAddDeptClosed (object sender, FormClosedEventArgs e)
//{
// FrmAddDepartment frmAddDepartment = (FrmAddDepartment)sender;
// _TryAddingDept(frmAddDepartment.Department);
//}
/*
* Parte relacionada a adição das alíquotas ao contrato
*/
private void btnAddAliquot_Click(object sender, EventArgs e)
{
try
{
if (Regex.IsMatch(this.txtAliquotValue.Text, aliqPattern))
{
double aliqValue = Convert.ToDouble(this.txtAliquotValue.Text);
ContingencyFund cf = this.cbContigencyFunds.SelectedItem as ContingencyFund;
ContingencyAliquot contAliq = new ContingencyAliquot(aliqValue, cf);
_TryAddingContingencyAliquot(contAliq);
}
else
{
MessageBox.Show("A taxa de alíquota inserida deve conter dois dígitos decimais e duas casas decimais separadas por vírgula, exemplo: 10,30%",
"Erro de Conversão de Taxa", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (FormatException)
{
MessageBox.Show("A taxa de alíquota inserida deve ser válida, exemplo: 10,30% ou 8,00%", "Erro de Conversão de Taxa", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (OverflowException)
{
MessageBox.Show("A taxa inserida deve ser entre 00,00 e 99,99%", "Erro de Conversão de Taxa", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void _TryAddingContingencyAliquot(ContingencyAliquot contAliq)
{
if (this.contingencyFundsAliquots.Add(contAliq))
{
ListViewItem item;
item = new ListViewItem();
item.Text = contAliq.ContingencyFund.Name;
item.SubItems.Add(contAliq.Value.ToString());
this.listContFundsAliquots.Items.Add(item);
this.txtAliquotValue.Text = "";
}
}
private void btnExport_Click(object sender, EventArgs e)
{
List <EmployeeHistory> employeeHistoriesChecked = new List <EmployeeHistory>();
foreach (DataGridViewRow row in this.dgvEmployees.Rows)
{
//se tiver marcado com CHECKED o funcionário
if (Convert.ToBoolean(row.Cells[0].Value) == true)
{
foreach (EmployeeHistory eh in employeeHistoriesVacation)
{
if (eh.Employee.Matriculation == row.Cells["matriculaColumn"].Value.ToString())
{
List <DateTime> dateTimes = (List <DateTime>)row.Cells["perAqColumn"].Tag;
DateTime dt1, dt2;
if (dateTimes.Count > 0 && dateTimes.Count < 2)
{
dt1 = dt2 = dateTimes[0];
}
else
{
dt1 = dateTimes[0];
dt2 = dateTimes[1];
}
if ((DateTime.Compare(eh.StartVacationTaken, dt1) == 0 || DateTime.Compare(eh.StartVacationTaken, dt2) == 0) &&
(DateTime.Compare(eh.EndVacationTaken, dt1) == 0 || DateTime.Compare(eh.EndVacationTaken, dt2) == 0))
{
employeeHistoriesChecked.Add(eh);
}
}
}
}
}
HashSet <ContingencyPast> empHistoryContPast =
_facade.GetContingencyPastsByEmployeeHistoryList(employeeHistoriesChecked, new ContingencyFund("Férias"));
//int year = (int)this.cbLowYear.SelectedItem;
//List<ContingencyPast> cpListByYear = this.yearListCPsPairs[year];
ContingencyAliquot caInc = new ContingencyAliquot();
foreach (ContingencyAliquot ca in taxFunds)
{
if (ca.ContingencyFund.Name.ToUpper().Equals("INCIDÊNCIA"))
{
caInc = ca;
}
}
IWorkbook workbook = DefaultExporterWorksheet.ExportCtgencyVacationEmployeeList(employeeHistoriesChecked, empHistoryContPast, caInc);
_SaveExcelFile(workbook);
}
public List <ContingencyAliquot> GetByContract(Contract ct)
{
List <ContingencyAliquot> contingencyAliquots = new List <ContingencyAliquot>();
NpgsqlDataReader reader = null;
try
{
string selectCMD = "SELECT ca.id as ca_id, ca.value as ca_value, " +
"cf.id as cf_id, cf.name as cf_name, ctt.id as ctt_id, ctt.name as ctt_name " +
"FROM (contingency_aliquot ca INNER JOIN contingency_funds cf ON (ca.contingency_fund_id = cf.id)) " +
"INNER JOIN contracts ctt ON (ca.contract_id = ctt.id) " +
"WHERE ca.contract_id = :ctID ORDER BY ca.id";
NpgsqlCommand cmd = new NpgsqlCommand(selectCMD);
cmd.Parameters.Add(new NpgsqlParameter("ctID", NpgsqlTypes.NpgsqlDbType.Bigint));
cmd.Parameters[0].Value = ct.Id;
Contract contract;
ContingencyFund contingencyFund;
ContingencyAliquot contingencyAliquot;
dal.OpenConnection();
reader = dal.ExecuteDataReader(cmd);
while (reader.Read())
{
contingencyAliquot = new ContingencyAliquot();
contingencyAliquot.Id = Convert.ToInt64(reader["ca_id"]);
contingencyAliquot.Value = Convert.ToDouble(reader["ca_value"]);
contract = new Contract(Convert.ToInt64(reader["ctt_id"]), reader["ctt_name"].ToString());
contingencyAliquot.Contract = contract;
contingencyFund = new ContingencyFund(Convert.ToInt64(reader["cf_id"]), reader["cf_name"].ToString());
contingencyAliquot.ContingencyFund = contingencyFund;
contingencyAliquots.Add(contingencyAliquot);
}
reader.Close();
}
finally
{
if (reader != null)
{
reader.Close();
}
this.dal.CloseConection();
}
return(contingencyAliquots);
}
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);
}
//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);
}
public void Update <K>(K id, ContingencyAliquot contingencyAliquot)
{
int rowsAffected = -1;
try
{
NpgsqlCommand cmd = new NpgsqlCommand("UPDATE contingency_aliquot SET \"start_date\" = :startDate, \"end_date\" = :endDate, \"contract_id\" = :contractId, " +
"\"value\" = :value, \"contingency_fund_id\" = :contingencyFundId "
+ "WHERE \"id\" = '" + id + "' ;");
cmd.Parameters.Add(new NpgsqlParameter("startDate", NpgsqlTypes.NpgsqlDbType.Date));
cmd.Parameters.Add(new NpgsqlParameter("endDate", NpgsqlTypes.NpgsqlDbType.Date));
cmd.Parameters.Add(new NpgsqlParameter("value", NpgsqlTypes.NpgsqlDbType.Double));
cmd.Parameters.Add(new NpgsqlParameter("contractId", NpgsqlTypes.NpgsqlDbType.Bigint));
cmd.Parameters.Add(new NpgsqlParameter("contingencyFundId", NpgsqlTypes.NpgsqlDbType.Bigint));
if (contingencyAliquot.StartDate == null)
{
cmd.Parameters[0].Value = DBNull.Value;
}
else
{
cmd.Parameters[0].Value = contingencyAliquot.StartDate;
}
if (contingencyAliquot.EndDate == null)
{
cmd.Parameters[1].Value = DBNull.Value;
}
else
{
cmd.Parameters[1].Value = contingencyAliquot.EndDate;
}
cmd.Parameters[2].Value = contingencyAliquot.Value;
if (contingencyAliquot.Contract == null)
{
cmd.Parameters[3].Value = DBNull.Value;
}
else
{
cmd.Parameters[3].Value = contingencyAliquot.Contract.Id;
}
if (contingencyAliquot.ContingencyFund == null)
{
cmd.Parameters[4].Value = DBNull.Value;
}
else
{
cmd.Parameters[4].Value = contingencyAliquot.ContingencyFund.Id;
}
dal.OpenConnection();
rowsAffected = dal.ExecuteNonQuery(cmd);
}
finally
{
this.dal.CloseConection();
}
}
public long Insert(ContingencyAliquot contingencyAliquot)
{
//int rowsAffected = -1;
object obj = null;
long returnedId = -1;
try
{
string cmdInsert = "INSERT INTO contingency_aliquot (start_date, end_date, value, contract_id, contingency_fund_id) " +
"VALUES (:startDate, :endDate, :value, :contractId, :contingencyFundId) RETURNING id";
NpgsqlCommand cmd = new NpgsqlCommand(cmdInsert);
cmd.Parameters.Add(new NpgsqlParameter("startDate", NpgsqlTypes.NpgsqlDbType.Date));
cmd.Parameters.Add(new NpgsqlParameter("endDate", NpgsqlTypes.NpgsqlDbType.Date));
cmd.Parameters.Add(new NpgsqlParameter("value", NpgsqlTypes.NpgsqlDbType.Double));
cmd.Parameters.Add(new NpgsqlParameter("contractId", NpgsqlTypes.NpgsqlDbType.Bigint));
cmd.Parameters.Add(new NpgsqlParameter("contingencyFundId", NpgsqlTypes.NpgsqlDbType.Bigint));
if (contingencyAliquot.StartDate == null)
{
cmd.Parameters[0].Value = DBNull.Value;
}
else
{
cmd.Parameters[0].Value = contingencyAliquot.StartDate;
}
if (contingencyAliquot.EndDate == null)
{
cmd.Parameters[1].Value = DBNull.Value;
}
else
{
cmd.Parameters[1].Value = contingencyAliquot.EndDate;
}
cmd.Parameters[2].Value = contingencyAliquot.Value;
if (contingencyAliquot.Contract == null)
{
cmd.Parameters[3].Value = DBNull.Value;
}
else
{
cmd.Parameters[3].Value = contingencyAliquot.Contract.Id;
}
if (contingencyAliquot.ContingencyFund == null)
{
cmd.Parameters[4].Value = DBNull.Value;
}
else
{
cmd.Parameters[4].Value = contingencyAliquot.ContingencyFund.Id;
}
dal.OpenConnection();
obj = dal.ExecuteScalar(cmd);
if (obj != null)
{
returnedId = (long)obj;
}
}
finally
{
this.dal.CloseConection();
}
return(returnedId);
}
public long InsertContingencyAliquot(ContingencyAliquot contingencyAliquot)
{
return(this._contingencyAliquotDAO.Insert(contingencyAliquot));
}
public void UpdateContingencyAliquot(long id, ContingencyAliquot contingencyAliquot)
{
this._contingencyAliquotDAO.Update <long>(id, contingencyAliquot);
}
