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