public List <SettlementSheetMonthSum> getPaySheet(SettlementSheetFilter filter)
{
DataTable dt = ConnectionClass.getResult("select employee.id as ФИО, sum(CASE WHEN settle_type_plus ='True' THEN endSum ELSE -endSum END ) AS Сумма from( SELECT employee_id,settle_type_plus, CASE WHEN settle_sum_fixed ='True' THEN settlement_sum ELSE settlement_sum*salary/100 END AS endSum FROM settlement_sheet,settlement,employee where employee.department_id = " + filter.department_id + " and settlement.id=settlement_sheet.settlement_id and strftime('%Y %m', date) = strftime('%Y %m', '" + filter.date.ToString("yyyy-MM-dd") + "') and employee.id = settlement_sheet.employee_id) as settles,employee where employee.id=settles.employee_id group by employee_id");
List <SettlementSheetMonthSum> list = new List <SettlementSheetMonthSum>();
for (int i = 0; i < dt.Rows.Count; i++)
{
list.Add(new SettlementSheetMonthSum(dt.Rows[i]));
}
return(list);
}
public List <SettlementSheet> getListSheetsFiltered(SettlementSheetFilter filter)
{
DataTable dt = ConnectionClass.getResult("SELECT id,employee_id,settlement_id,date,settlement_sum,department_id " +
"from settlement_sheet where department_id = " + filter.department_id + " and date = '" + filter.date.ToString("yyyy-MM-dd") + "'");
List <SettlementSheet> list = new List <SettlementSheet>();
for (int i = 0; i < dt.Rows.Count; i++)
{
list.Add(new SettlementSheet(dt.Rows[i]));
}
return(list);
}
public SettlementSheetForm(SettlementSheetFilter filter) : this()
{
this.filter = filter;
recountBtn.Visible = true;
for (int i = 0; i < departCbx.Items.Count; i++)
{
if (((Department)departCbx.Items[i]).id == filter.department_id)
{
departCbx.SelectedItem = departCbx.Items[i];
break;
}
}
datePck.Value = filter.date;
List <SettlementSheet> list = settleSheetSet.getListSheetsFiltered(filter);
for (int i = 0; i < list.Count; i++)
{
dataGridSheet.Rows.Add();
for (int j = 0; j < ((DataGridViewComboBoxCell)(dataGridSheet.Rows[i].Cells["employee"])).Items.Count; j++)
{
if (((Employee)((DataGridViewComboBoxCell)(dataGridSheet.Rows[i].Cells["employee"])).Items[j]).id == list[i].employee_id)
{
((DataGridViewComboBoxCell)(dataGridSheet.Rows[i].Cells["employee"])).Value = ((DataGridViewComboBoxCell)(dataGridSheet.Rows[i].Cells["employee"])).Items[j];
break;
}
}
for (int j = 0; j < ((DataGridViewComboBoxCell)(dataGridSheet.Rows[i].Cells["settle_type"])).Items.Count; j++)
{
if (((Settlement)((DataGridViewComboBoxCell)(dataGridSheet.Rows[i].Cells["settle_type"])).Items[j]).id == list[i].settlement_id)
{
((DataGridViewComboBoxCell)(dataGridSheet.Rows[i].Cells["settle_type"])).Value = ((DataGridViewComboBoxCell)(dataGridSheet.Rows[i].Cells["settle_type"])).Items[j];
break;
}
}
dataGridSheet["value", i].Value = list[i].settlement_sum;
}
}
public void deleteSheetsFilteredTransaction(SettlementSheetFilter filter)
{
ConnectionClass.executeQueryTransaction("delete from settlement_sheet where department_id = " + filter.department_id + " and strftime('%Y %m', date) = strftime('%Y %m', '" + filter.date.ToString("yyyy-MM-dd") + "')");
}
