private void expenseDataGridView_SelectionChanged(object sender, EventArgs e)
{
if ((_setSelectedExpense && (expenseDataGridView.SelectedRows == null || expenseDataGridView.SelectedRows.Count == 0)) ||
(!_setSelectedExpense && _selectedExpense == null))
{
;
}
{
return;
}
if (_setSelectedExpense)
{
_selectedExpense = expenseDataGridView.SelectedRows[0].DataBoundItem as ExpenseDomain;
}
Month month = _months.Find(p => p.Index == _selectedExpense.Month);
monthComboBox.SelectedItem = month;
descriptionTextBox.Text = _selectedExpense.Description;
yearTextBox.Text = _selectedExpense.Year.ToString();
amountTextBox.Text = _selectedExpense.Amount.ToString();
_setSelectedExpense = true;
}
public List <ExpenseDomain> GetExpenses(int fromMonthIndex, int toMonthIndex, int year)
{
SQLiteCommand command = new SQLiteCommand("select * from expense where (month >= @frommonthindex and month <= @tomonthindex) and year=@year", DatabaseManager.SQLiteConnection);
command.Parameters.Add(new SQLiteParameter("frommonthindex", fromMonthIndex));
command.Parameters.Add(new SQLiteParameter("tomonthindex", toMonthIndex));
command.Parameters.Add(new SQLiteParameter("year", year));
SQLiteDataReader reader = command.ExecuteReader();
List <ExpenseDomain> expenses = new List <ExpenseDomain>();
while (reader.Read())
{
ExpenseDomain expense = new ExpenseDomain();
expense.Id = Convert.ToInt32(reader["id"]);
expense.Description = reader["description"] != null ? reader["description"].ToString() : null;
expense.Month = Convert.ToInt32(reader["month"]);
expense.Year = reader["year"] != null?Convert.ToInt32(reader["year"]) : 0;
expense.Amount = reader["amount"] != null?Convert.ToDouble(reader["amount"]) : 0;
expenses.Add(expense);
}
return(expenses);
}
public static ExpenseDetailsResponse GetExpenseDetails(ExpenseDetailsRequest request) //Get Details for a specific Expense
{
int ExpenseID = request.ExpenseID;
var response = new ExpenseDetailsResponse();
var expense = new ExpenseDomain();
using (var entities = new ExpenseProjectDBEntities())
{
expense = (from e in entities.Expenses
where (e.ID == ExpenseID)
select new ExpenseDomain
{
ID = e.ID,
TotalAmount = e.TotalAmount,
CreatedDate = e.CreatedDate,
CreatedUserID = e.Created_User,
StatusID = e.StatusId,
ModifiedByID = e.ModifiedBy,
ModifiedDate = e.ModifiedDate,
RejectDescription = e.RejectDescription
}
).FirstOrDefault();
response.ExpenseInformation = expense;
response.ExpenseItemList = GetExpenseItems(request);
return(response);
}
}
public void Delete(ExpenseDomain expense)
{
SQLiteCommand command = new SQLiteCommand("delete from expense where id=@expenseid", DatabaseManager.SQLiteConnection);
command.Parameters.Add(new SQLiteParameter("expenseid", expense.Id));
command.ExecuteNonQuery();
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(yearTextBox.Text) || !_numericRegex.IsMatch(yearTextBox.Text))
{
MessageBox.Show("Tahun tidak boleh kosong dah harus diisi dengan angka!", null, MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (!String.IsNullOrEmpty(amountTextBox.Text) && !_numericRegex.IsMatch(amountTextBox.Text))
{
MessageBox.Show("Nominal harus diisi dengan angka!", null, MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
Month month = monthComboBox.SelectedValue as Month;
int year = Convert.ToInt32(yearTextBox.Text);
double amount = 0;
if (!String.IsNullOrEmpty(amountTextBox.Text))
{
amount = Convert.ToDouble(amountTextBox.Text);
}
if (_uiMode == UserInterfaceModes.Adding)
{
ExpenseDomain expense = LogicFactory.ExpenseLogic.AddExpense(descriptionTextBox.Text, month.Index, year, amount);
expense.DisplayedMonth = month.Name;
expense.DisplayedAmount = amount.ToString("#,##0");
_expenses.Add(expense);
_selectedExpense = expense;
}
else if (_uiMode == UserInterfaceModes.Editing)
{
_selectedExpense.Description = descriptionTextBox.Text;
_selectedExpense.Month = month.Index;
_selectedExpense.Year = year;
_selectedExpense.Amount = amount;
ExpenseDomain expense = LogicFactory.ExpenseLogic.UpdateExpense(_selectedExpense);
expense.DisplayedMonth = month.Name;
expense.DisplayedAmount = amount.ToString("#,##0");
int index = _expenses.IndexOf(_selectedExpense);
_expenses.Remove(_selectedExpense);
_expenses.Insert(index, expense);
_selectedExpense = expense;
}
_uiMode = UserInterfaceModes.Viewing;
SetUIControlsAvailability();
_filterActive = false;
_setSelectedExpense = false;
RefreshGrid();
}
public ExpenseDomain AddExpense(string description, int month, int year, double amount)
{
ExpenseDomain expense = new ExpenseDomain();
expense.Description = description;
expense.Month = month;
expense.Year = year;
expense.Amount = amount;
return(DaoFactory.ExpenseDao.Save(expense));
}
public ExpenseDomain Update(ExpenseDomain expense)
{
SQLiteCommand command = new SQLiteCommand("update expense set description=@description, month=@month, year=@year, amount=@amount where id=@expenseid", DatabaseManager.SQLiteConnection);
command.Parameters.Add(new SQLiteParameter("expenseid", expense.Id));
command.Parameters.Add(new SQLiteParameter("description", expense.Description));
command.Parameters.Add(new SQLiteParameter("month", expense.Month));
command.Parameters.Add(new SQLiteParameter("year", expense.Year));
command.Parameters.Add(new SQLiteParameter("amount", expense.Amount));
command.ExecuteNonQuery();
return(expense);
}
private void deleteToolStripButton_Click(object sender, EventArgs e)
{
if (_selectedExpense == null)
{
return;
}
LogicFactory.ExpenseLogic.DeleteExpense(_selectedExpense);
_expenses.Remove(_selectedExpense);
_filterActive = false;
_selectedExpense = null;
_setSelectedExpense = true;
RefreshGrid();
}
public static void SetStatusToWaitingForManager(int ID)
{
var expense = new ExpenseDomain();
using (var entities = new ExpenseProjectDBEntities())
{
expense = (from e in entities.Expenses
where (e.ID == ID)
select new ExpenseDomain
{
StatusID = 4,
}
).FirstOrDefault();
entities.SaveChanges();
}
}
public ExpenseDomain Save(ExpenseDomain expense)
{
SQLiteCommand command = new SQLiteCommand("insert into expense (description, month, year, amount) values (@description, @month, @year, @amount)", DatabaseManager.SQLiteConnection);
command.Parameters.Add(new SQLiteParameter("description", expense.Description));
command.Parameters.Add(new SQLiteParameter("month", expense.Month));
command.Parameters.Add(new SQLiteParameter("year", expense.Year));
command.Parameters.Add(new SQLiteParameter("amount", expense.Amount));
command.ExecuteNonQuery();
command = new SQLiteCommand("select last_insert_rowid()", DatabaseManager.SQLiteConnection);
Int64 id = (Int64)command.ExecuteScalar();
expense.Id = (int)id;
return(expense);
}
private void SelectExpenseOnTheGrid(int expenseId)
{
int index = 0;
foreach (DataGridViewRow row in expenseDataGridView.Rows)
{
ExpenseDomain expense = row.DataBoundItem as ExpenseDomain;
if (expense.Id == _selectedExpense.Id)
{
_selectedExpense = expense;
break;
}
index++;
}
expenseDataGridView.Rows[index].Selected = true;
}
public List <ExpenseDomain> GetAllExpenses()
{
SQLiteCommand command = new SQLiteCommand("select * from expense", DatabaseManager.SQLiteConnection);
SQLiteDataReader reader = command.ExecuteReader();
List <ExpenseDomain> expenses = new List <ExpenseDomain>();
while (reader.Read())
{
ExpenseDomain expense = new ExpenseDomain();
expense.Id = Convert.ToInt32(reader["id"]);
expense.Description = reader["description"] != null ? reader["description"].ToString() : null;
expense.Month = Convert.ToInt32(reader["month"]);
expense.Year = reader["year"] != null?Convert.ToInt32(reader["year"]) : 0;
expense.Amount = reader["amount"] != null?Convert.ToDouble(reader["amount"]) : 0;
expenses.Add(expense);
}
return(expenses);
}
public void GenerateAccountingReport(Month fromMonth, Month toMonth, int year, out string message)
{
message = null;
bool success = true;
try
{
ExcelUtility.CreateExcelDocument();
ExcelUtility.SetGridVisibility(false);
ExcelUtility.Write(5, 2, 7, 2, "NO", null, "Tahoma", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 8.33);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
ExcelUtility.Write(5, 3, 7, 3, "KETERANGAN", null, "Tahoma", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 98.33);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
List <Month> months = MonthUtility.GetMonths();
int column = 4;
Dictionary <int, int> columnReferences = new Dictionary <int, int>();
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
columnReferences[j] = column;
Month month = months.Find(p => p.Index == j);
ExcelUtility.Write(5, column, 7, column, month.Name.ToUpper(), null, "Tahoma", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 16.67);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
column++;
}
ExcelUtility.Write(5, column, 7, column, "JUMLAH", null, "Tahoma", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 16.67);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
ExcelUtility.Write(9, 3, "PENDAPATAN CLUSTER", null, "Tahoma", 12, true, true);
List <ClusterDomain> clusters = DaoFactory.ClusterDao.GetAllClusters();
List <IncomeClusterDomain> incomeClusters = DaoFactory.IncomeClusterDao.GetIncomeClusters(fromMonth.Index, toMonth.Index, year);
List <int> clusterIds = new List <int>();
foreach (IncomeClusterDomain incomeCluster in incomeClusters)
{
if (!clusterIds.Contains(incomeCluster.ClusterId))
{
clusterIds.Add(incomeCluster.ClusterId);
}
}
int rowIndex = 10;
foreach (int clusterId in clusterIds)
{
ClusterDomain cluster = clusters.Find(p => p.Id == clusterId);
if (cluster == null)
{
continue;
}
List <IncomeClusterDomain> result = incomeClusters.FindAll(p => p.ClusterId == clusterId);
if (result == null || result.Count == 0)
{
continue;
}
ExcelUtility.Write(rowIndex, 2, (rowIndex - 9).ToString(), "@", "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(rowIndex, 3, "IPL WARGA " + cluster.ClusterName, null, "Tahoma", 12, true, false);
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
List <IncomeClusterDomain> currentMonthIncomeClusters = result.FindAll(p => p.Month == j);
if (currentMonthIncomeClusters == null || currentMonthIncomeClusters.Count == 0)
{
continue;
}
double amount = 0;
foreach (IncomeClusterDomain income in currentMonthIncomeClusters)
{
amount += income.Amount;
}
ExcelUtility.Write(rowIndex, columnReferences[j], amount, "#,##0", "Tahoma", 12, true, false);
}
rowIndex++;
}
List <IncomeDomain> incomes = DaoFactory.IncomeDao.GetIncomes(fromMonth.Index, toMonth.Index, year);
List <int> incomeSourceIds = new List <int>();
foreach (IncomeDomain income in incomes)
{
if (!incomeSourceIds.Contains(income.IncomeSourceId))
{
incomeSourceIds.Add(income.IncomeSourceId);
}
}
List <IncomeSourceDomain> incomeSources = DaoFactory.IncomeSourceDao.GetAllIncomeSources();
foreach (int incomeSourceId in incomeSourceIds)
{
IncomeSourceDomain incomeSource = incomeSources.Find(p => p.Id == incomeSourceId);
if (incomeSource == null)
{
continue;
}
ExcelUtility.Write(rowIndex, 2, (rowIndex - 9).ToString(), "@", "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(rowIndex, 3, incomeSource.Description, null, "Tahoma", 12, true, false);
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
List <IncomeDomain> currentMonthIncomes = incomes.FindAll(p => p.Month == j && p.IncomeSourceId == incomeSourceId);
if (currentMonthIncomes == null || currentMonthIncomes.Count == 0)
{
continue;
}
double amount = 0;
foreach (IncomeDomain income in currentMonthIncomes)
{
amount += income.Amount;
}
ExcelUtility.Write(rowIndex, columnReferences[j], amount, "#,##0", "Tahoma", 12, true, false);
}
rowIndex++;
}
for (int i = 10; i < rowIndex; i++)
{
ExcelUtility.Write(i, column, "=SUM(" + ExcelUtility.GetExcelCellName(4, i) + ":" + ExcelUtility.GetExcelCellName(column - 1, i), "#,##0", "Tahoma", 12, true, false);
}
rowIndex++;
ExcelUtility.Write(rowIndex, 3, "SUB TOTAL PENDAPATAN", null, "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
int columnIndex = 4;
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
ExcelUtility.Write(rowIndex, columnIndex, "=SUM(" + ExcelUtility.GetExcelCellName(columnIndex, 10) + ":" + ExcelUtility.GetExcelCellName(columnIndex, rowIndex - 2), "#,##0", "Tahoma", 12, true, false);
columnIndex++;
}
ExcelUtility.Write(rowIndex, columnIndex, "=SUM(" + ExcelUtility.GetExcelCellName(columnIndex, 10) + ":" + ExcelUtility.GetExcelCellName(columnIndex, rowIndex - 2), "#,##0", "Tahoma", 12, true, false);
ExcelUtility.Write(rowIndex + 2, 3, "PENGELUARAN CLUSTER", null, "Tahoma", 12, true, true);
rowIndex += 4;
int startExpenseRowIndex = rowIndex;
List <ExpenseDomain> expenses = DaoFactory.ExpenseDao.GetExpenses(fromMonth.Index, toMonth.Index, year);
List <ExpenseDomain> condensedExpenses = new List <ExpenseDomain>();
foreach (ExpenseDomain expense in expenses)
{
ExpenseDomain result = condensedExpenses.Find(p => p.Description.ToLower().Trim() == expense.Description.ToLower().Trim() && p.Month == expense.Month);
if (result == null)
{
condensedExpenses.Add(expense);
}
else
{
result.Amount += expense.Amount;
}
}
Dictionary <string, int> rowReferences = new Dictionary <string, int>();
foreach (ExpenseDomain expense in condensedExpenses)
{
if (rowReferences.ContainsKey(expense.Description.ToLower().Trim()))
{
ExcelUtility.Write(rowReferences[expense.Description.ToLower().Trim()], columnReferences[expense.Month], expense.Amount, "#,##0", "Tahoma", 12, true, false);
}
else
{
ExcelUtility.Write(rowIndex, 2, (rowIndex - startExpenseRowIndex + 1).ToString(), null, "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(rowIndex, 3, expense.Description, null, "Tahoma", 12, true, false);
ExcelUtility.Write(rowIndex, columnReferences[expense.Month], expense.Amount, "#,##0", "Tahoma", 12, true, false);
rowReferences[expense.Description.ToLower().Trim()] = rowIndex;
rowIndex++;
}
}
for (int i = startExpenseRowIndex; i < rowIndex; i++)
{
ExcelUtility.Write(i, column, "=SUM(" + ExcelUtility.GetExcelCellName(4, i) + ":" + ExcelUtility.GetExcelCellName(column - 1, i), "#,##0", "Tahoma", 12, true, false);
}
rowIndex++;
ExcelUtility.Write(rowIndex, 3, "SUB TOTAL PENGELUARAN", null, "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
columnIndex = 4;
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
ExcelUtility.Write(rowIndex, columnIndex, "=SUM(" + ExcelUtility.GetExcelCellName(columnIndex, startExpenseRowIndex) + ":" + ExcelUtility.GetExcelCellName(columnIndex, rowIndex - 2), "#,##0", "Tahoma", 12, true, false);
columnIndex++;
}
ExcelUtility.Write(rowIndex, columnIndex, "=SUM(" + ExcelUtility.GetExcelCellName(columnIndex, startExpenseRowIndex) + ":" + ExcelUtility.GetExcelCellName(columnIndex, rowIndex - 2), "#,##0", "Tahoma", 12, true, false);
rowIndex++;
ExcelUtility.Write(rowIndex, 3, "SALDO BULANAN", null, "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
for (int i = 4; i <= columnIndex; i++)
{
ExcelUtility.Write(rowIndex, i, "=" + ExcelUtility.GetExcelCellName(i, startExpenseRowIndex - 4) + "-" + ExcelUtility.GetExcelCellName(i, rowIndex - 1), "#,##0", "Tahoma", 12, true, false);
}
ExcelUtility.Write(0, 2, 0, column, "LAPORAN KEUANGAN", null, "Tahoma", 14, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(1, 2, 1, column, "WARGA CLUSTER ARGA PADMA NIRWANA", null, "Tahoma", 16, true, true, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
string monthText = fromMonth.Name;
if (toMonth.Index != fromMonth.Index)
{
monthText += " - " + toMonth.Name;
}
ExcelUtility.Write(2, 2, 2, column, "PERIODE BULAN " + monthText + " " + year.ToString(), null, "Tahoma", 14, false, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
for (int i = 8; i <= rowIndex; i++)
{
for (int j = 2; j <= column; j++)
{
ExcelUtility.SetCurrentCell(i, j);
BorderWeight topWeight = BorderWeight.Thin;
BorderWeight bottomWeight = BorderWeight.Thin;
if (i == startExpenseRowIndex - 4 || i == rowIndex - 1 || i == rowIndex)
{
topWeight = BorderWeight.Thick;
bottomWeight = BorderWeight.Thick;
}
else if (i == startExpenseRowIndex - 3)
{
topWeight = BorderWeight.Thick;
}
ExcelUtility.SetBorder(true, i > 8, true, true, BorderWeight.Thick, topWeight, BorderWeight.Thick, bottomWeight);
}
}
ExcelUtility.SetCurrentCell(startExpenseRowIndex - 4, 2, startExpenseRowIndex - 4, column);
ExcelUtility.SetPropertyValue(RangeProperty.CellColor, System.Drawing.Color.FromArgb(141, 180, 226));
ExcelUtility.SetCurrentCell(rowIndex - 1, 2, rowIndex - 1, column);
ExcelUtility.SetPropertyValue(RangeProperty.CellColor, System.Drawing.Color.Yellow);
ExcelUtility.SetCurrentCell(rowIndex, 2, rowIndex, column);
ExcelUtility.SetPropertyValue(RangeProperty.CellColor, System.Drawing.Color.FromArgb(141, 180, 226));
}
catch (Exception ex)
{
message = ex.Message;
success = false;
}
finally
{
if (success)
{
ExcelUtility.DisplayExcelDocument();
}
else
{
ExcelUtility.CloseExcelDocument();
}
}
}
public void DeleteExpense(ExpenseDomain expense)
{
DaoFactory.ExpenseDao.Delete(expense);
}
public ExpenseDomain UpdateExpense(ExpenseDomain expense)
{
return(DaoFactory.ExpenseDao.Update(expense));
}
