public List <IncomeClusterDomain> GetAllIncomeClusters() { SQLiteCommand command = new SQLiteCommand("select * from income_cluster", DatabaseManager.SQLiteConnection); SQLiteDataReader reader = command.ExecuteReader(); List <IncomeClusterDomain> clusterIncomes = new List <IncomeClusterDomain>(); while (reader.Read()) { IncomeClusterDomain clusterIncome = new IncomeClusterDomain(); clusterIncome.Id = Convert.ToInt32(reader["id"]); clusterIncome.ClusterId = Convert.ToInt32(reader["cluster_id"]); clusterIncome.AddressBlock = reader["address_block"] != null ? reader["address_block"].ToString() : null; clusterIncome.AddressNumber = reader["address_number"] != null ? reader["address_number"].ToString() : null; clusterIncome.PhoneNumber = reader["phone_number"] != null ? reader["phone_number"].ToString() : null; clusterIncome.OccupantName = reader["occupant_name"] != null ? reader["occupant_name"].ToString() : null; clusterIncome.Month = Convert.ToInt32(reader["month"]); clusterIncome.Year = Convert.ToInt32(reader["year"]); clusterIncome.Amount = Convert.ToDouble(reader["amount"]); clusterIncomes.Add(clusterIncome); } return(clusterIncomes); }
public void Delete(IncomeClusterDomain incomeCluster) { SQLiteCommand command = new SQLiteCommand("delete from income_cluster where id=@clusterincomeid", DatabaseManager.SQLiteConnection); command.Parameters.Add(new SQLiteParameter("clusterincomeid", incomeCluster.Id)); command.ExecuteNonQuery(); }
public IncomeClusterDomain AddIncomeCluster(ClusterDomain cluster, string occupantName, string addressBlock, string addressNumber, string phoneNumber, int month, int year, double amount) { IncomeClusterDomain incomeCluster = new IncomeClusterDomain(); incomeCluster.OccupantName = occupantName; incomeCluster.ClusterId = cluster.Id; incomeCluster.AddressBlock = addressBlock; incomeCluster.AddressNumber = addressNumber; incomeCluster.PhoneNumber = phoneNumber; incomeCluster.Month = month; incomeCluster.Year = year; incomeCluster.Amount = amount; return(DaoFactory.IncomeClusterDao.Save(incomeCluster)); }
public IncomeClusterDomain Update(IncomeClusterDomain incomeCluster) { SQLiteCommand command = new SQLiteCommand( "update income_cluster set cluster_id=@clusterid, occupant_name=@occupantname, address_block=@addressblock, address_number=@addressnumber, " + "phone_number=@phonenumber, month=@month, year=@year, amount=@amount where id=@clusterincomeid", DatabaseManager.SQLiteConnection); command.Parameters.Add(new SQLiteParameter("clusterincomeid", incomeCluster.Id)); command.Parameters.Add(new SQLiteParameter("clusterid", incomeCluster.ClusterId)); command.Parameters.Add(new SQLiteParameter("occupantname", incomeCluster.OccupantName)); command.Parameters.Add(new SQLiteParameter("addressblock", incomeCluster.AddressBlock)); command.Parameters.Add(new SQLiteParameter("addressnumber", incomeCluster.AddressNumber)); command.Parameters.Add(new SQLiteParameter("phonenumber", incomeCluster.PhoneNumber)); command.Parameters.Add(new SQLiteParameter("month", incomeCluster.Month)); command.Parameters.Add(new SQLiteParameter("year", incomeCluster.Year)); command.Parameters.Add(new SQLiteParameter("amount", incomeCluster.Amount)); command.ExecuteNonQuery(); return(incomeCluster); }
private void SelectIncomeClusterOnTheGrid(int incomeClusterId) { int index = -1; foreach (DataGridViewRow row in incomeClusterDataGridView.Rows) { index++; IncomeClusterDomain incomeCluster = row.DataBoundItem as IncomeClusterDomain; if (incomeCluster.Id == _selectedIncomeCluster.Id) { _selectedIncomeCluster = incomeCluster; break; } } if (index != -1) { incomeClusterDataGridView.Rows[index].Selected = true; } }
private void incomeClusterDataGridView_SelectionChanged(object sender, EventArgs e) { if ((_setSelectedIncomeCluster && (incomeClusterDataGridView.SelectedRows == null || incomeClusterDataGridView.SelectedRows.Count == 0)) || (!_setSelectedIncomeCluster && _selectedIncomeCluster == null)) { return; } if (_setSelectedIncomeCluster) { _selectedIncomeCluster = incomeClusterDataGridView.SelectedRows[0].DataBoundItem as IncomeClusterDomain; } ClusterDomain cluster = _clusters.Find(p => p.Id == _selectedIncomeCluster.ClusterId); if (cluster != null) { clusterComboBox.SelectedItem = cluster; } Month month = _months.Find(p => p.Index == _selectedIncomeCluster.Month); monthComboBox.SelectedItem = month; yearTextBox.Text = _selectedIncomeCluster.Year.ToString(); AddressBlockDomain addressBlock = _addressBlocks.Find(p => p.Block == _selectedIncomeCluster.AddressBlock); if (addressBlock != null) { addressBlockComboBox.SelectedItem = _selectedIncomeCluster.AddressBlock; } addressNumberTextBox.Text = _selectedIncomeCluster.AddressNumber; phoneNumberTextBox.Text = _selectedIncomeCluster.PhoneNumber; occupantTextBox.Text = _selectedIncomeCluster.OccupantName; amountTextBox.Text = _selectedIncomeCluster.Amount.ToString(); }
public void GenerateIncomeClusterReport(ClusterDomain cluster, Month fromMonth, int fromYear, Month toMonth, int toYear, out string message) { message = null; bool success = true; try { ExcelUtility.CreateExcelDocument(); ExcelUtility.SetGridVisibility(false); ExcelUtility.Write(4, 1, 5, 1, "No.", null, "Arial", 12, false, false, true); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 5.89); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thin); ExcelUtility.Write(4, 2, 5, 2, "Nama Pemilik/Penghuni Rumah", null, "Arial", 12, true, false, true); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 37.22); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thin); ExcelUtility.Write(4, 3, 4, 4, "Alamat", null, "Arial", 12, true, false, true); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thin); ExcelUtility.Write(5, 3, 5, 3, "Blok", null, "Arial", 12, true, false, true); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 5.78); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin); ExcelUtility.Write(5, 4, 5, 4, "No.", null, "Arial", 12, true, false, true); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 5.78); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin); ExcelUtility.Write(4, 5, 5, 5, "No. Telpon", null, "Arial", 12, true, false, true); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 11.89); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thin); List <Month> months = MonthUtility.GetMonths(); int column = 6; Dictionary <int, Dictionary <int, int> > columnReferences = new Dictionary <int, Dictionary <int, int> >(); for (int i = fromYear; i <= toYear; i++) { columnReferences[i] = new Dictionary <int, int>(); for (int j = fromMonth.Index; j <= toMonth.Index; j++) { columnReferences[i][j] = column; Month month = months.Find(p => p.Index == j); ExcelUtility.Write(5, column, month.ShortName.ToUpper(), null, "Arial", 12, true, false); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 11.33); ExcelUtility.SetBorder(j > fromMonth.Index, true, j < toMonth.Index, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin); ExcelUtility.SetCurrentCell(4, column); ExcelUtility.SetBorder(false, true, false, true, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thin); column++; } } ExcelUtility.Write(4, column, 5, column, "TOTAL", null, "Arial", 12, true, false, true); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 11.89); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thin); List <IncomeClusterDomain> incomeClusters = DaoFactory.IncomeClusterDao.GetIncomeClusters(cluster, fromMonth.Index, fromYear, toMonth.Index, toYear); incomeClusters.Sort((p1, p2) => { if (p1.AddressBlock.CompareTo(p2.AddressBlock) != 0) { return(p1.AddressBlock.CompareTo(p2.AddressBlock)); } else { return(p1.AddressNumber.CompareTo(p2.AddressNumber)); } }); Dictionary <string, int> detailsIndices = new Dictionary <string, int>(); int currentRow = 6; for (int i = 0; i < incomeClusters.Count; i++) { IncomeClusterDomain incomeCluster = incomeClusters[i]; string index = string.Join(";", new string[] { incomeCluster.OccupantName, incomeCluster.AddressBlock, incomeCluster.AddressNumber }); if (detailsIndices.ContainsKey(index)) { ExcelUtility.Write(detailsIndices[index], columnReferences[incomeCluster.Year][incomeCluster.Month], incomeCluster.Amount, "#,##0", "Arial", 12, true, false); } else { ExcelUtility.Write(currentRow, 1, (currentRow - 5).ToString(), null, "Arial", 12, true, false); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin); ExcelUtility.Write(currentRow, 2, incomeCluster.OccupantName, null, "Arial", 12, true, false); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin); ExcelUtility.Write(currentRow, 3, incomeCluster.AddressBlock, null, "Arial", 12, true, false); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.Write(currentRow, 4, incomeCluster.AddressNumber, null, "Arial", 12, true, false); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.Write(currentRow, 5, incomeCluster.PhoneNumber, null, "Arial", 12, true, false); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin); ExcelUtility.Write(currentRow, columnReferences[incomeCluster.Year][incomeCluster.Month], incomeCluster.Amount, "#,##0", "Arial", 12, true, false); detailsIndices[index] = currentRow; currentRow++; } } for (int rowIndex = 6; rowIndex < currentRow; rowIndex++) { int columnIndex = 6; for (int i = fromYear; i <= toYear; i++) { for (int j = fromMonth.Index; j <= toMonth.Index; j++) { ExcelUtility.SetCurrentCell(rowIndex, columnIndex); ExcelUtility.SetBorder(columnIndex > 6, true, true, columnIndex < column, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin); columnIndex++; } } } foreach (KeyValuePair <string, int> detailsIndex in detailsIndices) { ExcelUtility.Write(detailsIndex.Value, column, "=SUM(" + ExcelUtility.GetExcelCellName(6, detailsIndex.Value) + ":" + ExcelUtility.GetExcelCellName(column - 1, detailsIndex.Value), "#,##0", "Arial", 12, true, false); ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin); } for (int columnIndex = 1; columnIndex <= column; columnIndex++) { ExcelUtility.SetCurrentCell(currentRow, columnIndex); BorderWeight leftBorderWeight = BorderWeight.Thin; if (columnIndex == 1 || columnIndex == 6 || columnIndex == column) { leftBorderWeight = BorderWeight.Thick; } ExcelUtility.SetBorder(true, false, true, true, leftBorderWeight, BorderWeight.Thin, columnIndex == column ? BorderWeight.Thick : BorderWeight.Thin, BorderWeight.Thick); } for (int columnIndex = 1; columnIndex <= column; columnIndex++) { ExcelUtility.SetCurrentCell(currentRow + 1, columnIndex); BorderWeight leftBorderWeight = BorderWeight.Thick; if (columnIndex == 2 || columnIndex == 3 || columnIndex == 4 || columnIndex == 5) { leftBorderWeight = BorderWeight.Thin; } ExcelUtility.SetBorder(true, false, columnIndex == column, true, leftBorderWeight, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thick); } ExcelUtility.Write(currentRow + 1, 5, "TOTAL", null, "Arial", 12, true, false); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center); for (int i = 6; i <= column; i++) { ExcelUtility.Write(currentRow + 1, i, "=SUM(" + ExcelUtility.GetExcelCellName(i, 6) + ":" + ExcelUtility.GetExcelCellName(i, currentRow - 1), "#,##0", "Arial", 12, true, false); } ExcelUtility.Write(0, 1, 0, column, "YAYASAN WARGA ARGA PADMA NIRWANA BOGOR", null, "Arial", 12, true, false, true); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 23.3); ExcelUtility.Write(1, 1, 1, column, "Rekapitulasi Iuran Pengelolaan Lingkungan ( I P L )", null, "Arial", 12, true, false, true); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 23.3); ExcelUtility.Write(2, 1, 2, column, "Cluster: " + cluster.ClusterName, null, "Arial", 12, true, false, true); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center); ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 23.3); ExcelUtility.Write(3, column, DateTime.Today.ToString("dd MMMM yyyy"), null, "Arial", 12, true, false); ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Right); } catch (Exception ex) { message = ex.Message; success = false; } finally { if (success) { ExcelUtility.DisplayExcelDocument(); } else { ExcelUtility.CloseExcelDocument(); } } }
public IncomeClusterDomain UpdateIncomeCluster(IncomeClusterDomain incomeCluster) { return(DaoFactory.IncomeClusterDao.Update(incomeCluster)); }
public void DeleteIncomeCluster(IncomeClusterDomain incomeCluster) { DaoFactory.IncomeClusterDao.Delete(incomeCluster); }
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; } ClusterDomain cluster = clusterComboBox.SelectedValue as ClusterDomain; 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); } AddressBlockDomain addressBlock = (addressBlockComboBox.SelectedItem as AddressBlockDomain); if (_uiMode == UserInterfaceModes.Adding) { IncomeClusterDomain incomeCluster = LogicFactory.IncomeLogic.AddIncomeCluster(cluster, occupantTextBox.Text, (addressBlock != null ? addressBlock.Block : String.Empty), addressNumberTextBox.Text, phoneNumberTextBox.Text, month.Index, year, amount); incomeCluster.ClusterName = cluster.ClusterName; incomeCluster.DisplayedAmount = amount.ToString("#,##0"); incomeCluster.DisplayedMonth = month.Name; _incomeClusters.Add(incomeCluster); _selectedIncomeCluster = incomeCluster; } else if (_uiMode == UserInterfaceModes.Editing) { IncomeClusterDomain incomeCluster = new IncomeClusterDomain(); incomeCluster.Id = _selectedIncomeCluster.Id; incomeCluster.ClusterId = cluster.Id; incomeCluster.ClusterName = cluster.ClusterName; incomeCluster.OccupantName = occupantTextBox.Text; incomeCluster.AddressBlock = addressBlock != null ? addressBlock.Block : String.Empty; incomeCluster.AddressNumber = addressNumberTextBox.Text; incomeCluster.PhoneNumber = phoneNumberTextBox.Text; incomeCluster.Month = month.Index; incomeCluster.Year = year; incomeCluster.Amount = amount; incomeCluster.DisplayedAmount = amount.ToString("#,##0"); incomeCluster.DisplayedMonth = month.Name; LogicFactory.IncomeLogic.UpdateIncomeCluster(incomeCluster); _incomeClusters.Remove(_selectedIncomeCluster); _incomeClusters.Add(incomeCluster); _selectedIncomeCluster = incomeCluster; } _setSelectedIncomeCluster = false; _filterActive = false; _uiMode = UserInterfaceModes.Viewing; SetUIControlsAvailability(); RefreshGrid(); }