public void Delete(ClusterDomain cluster)
{
SQLiteCommand command = new SQLiteCommand("delete from cluster where id=@clusterid", DatabaseManager.SQLiteConnection);
command.Parameters.Add(new SQLiteParameter("clusterid", cluster.Id));
command.ExecuteNonQuery();
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(clusterNameTextBox.Text))
{
MessageBox.Show("Nama cluster tidak boleh dikosongkan!", null, MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (_uiMode == UserInterfaceModes.Adding)
{
ClusterDomain cluster = LogicFactory.ClusterLogic.AddCluster(clusterNameTextBox.Text);
_clusters.Add(cluster);
}
else if (_uiMode == UserInterfaceModes.Editing)
{
_selectedCluster.ClusterName = clusterNameTextBox.Text;
LogicFactory.ClusterLogic.UpdateCluster(_selectedCluster);
ClusterDomain cluster = _clusters.Find(p => p.Id == _selectedCluster.Id);
_clusters.Remove(cluster);
_clusters.Add(_selectedCluster);
}
RefreshGrid();
_uiMode = UserInterfaceModes.Viewing;
SetUIControlsAvailability();
}
private void IncomeClusterForm_Load(object sender, EventArgs e)
{
Size = this.MdiParent.Size;
ProgressTrackerForm progressTrackerForm = new ProgressTrackerForm();
progressTrackerForm.ProcessInformation = "Mengambil data pendapatan cluster...";
progressTrackerForm.Task = new ProgressTrackerForm.BackgroundTask(
() =>
{
_incomeClusters = new List <IncomeClusterDomain>();
List <IncomeClusterDomain> incomeClusters = LogicFactory.IncomeLogic.GetAllIncomeClusters();
foreach (IncomeClusterDomain incomeCluster in incomeClusters)
{
ClusterDomain cluster = _clusters.Find(p => p.Id == incomeCluster.ClusterId);
if (cluster != null)
{
incomeCluster.ClusterName = cluster.ClusterName;
}
incomeCluster.DisplayedAmount = incomeCluster.Amount.ToString("#,##0");
Month month = _months.Find(p => p.Index == incomeCluster.Month);
incomeCluster.DisplayedMonth = month.Name;
_incomeClusters.Add(incomeCluster);
}
}
);
progressTrackerForm.ShowDialog();
RefreshGrid();
}
public List <IncomeClusterDomain> GetIncomeClusters(ClusterDomain cluster, int fromMonthIndex, int fromYear, int toMonthIndex, int toYear)
{
SQLiteCommand command = new SQLiteCommand("select * from income_cluster where cluster_id=@clusterid and (month >= @frommonthindex and month <= @tomonthindex) and (year >= @fromyear and year <= @toyear)", DatabaseManager.SQLiteConnection);
command.Parameters.Add(new SQLiteParameter("clusterid", cluster.Id));
command.Parameters.Add(new SQLiteParameter("frommonthindex", fromMonthIndex));
command.Parameters.Add(new SQLiteParameter("tomonthindex", toMonthIndex));
command.Parameters.Add(new SQLiteParameter("fromyear", fromYear));
command.Parameters.Add(new SQLiteParameter("toyear", toYear));
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 ClusterDomain AddCluster(string clusterName)
{
ClusterDomain newCluster = new ClusterDomain();
newCluster.ClusterName = clusterName;
return(DaoFactory.ClusterDao.Save(newCluster));
}
public int GetIncomeClusterCountByCluster(ClusterDomain cluster)
{
SQLiteCommand command = new SQLiteCommand("select count(p.Id) from income_cluster p where p.cluster_id=@clusterid", DatabaseManager.SQLiteConnection);
command.Parameters.Add(new SQLiteParameter("clusterid", cluster.Id));
int incomeClusterCount = Convert.ToInt32(command.ExecuteScalar());
return(incomeClusterCount);
}
public ClusterDomain Update(ClusterDomain cluster)
{
SQLiteCommand command = new SQLiteCommand("update cluster set cluster_name=@clustername where id=@clusterid", DatabaseManager.SQLiteConnection);
command.Parameters.Add(new SQLiteParameter("clustername", cluster.ClusterName));
command.Parameters.Add(new SQLiteParameter("clusterid", cluster.Id));
command.ExecuteNonQuery();
return(cluster);
}
public ClusterDomain Save(ClusterDomain cluster)
{
SQLiteCommand command = new SQLiteCommand("insert into cluster (cluster_name) values (@clustername)", DatabaseManager.SQLiteConnection);
command.Parameters.Add(new SQLiteParameter("clustername", cluster.ClusterName));
command.ExecuteNonQuery();
command = new SQLiteCommand("select last_insert_rowid()", DatabaseManager.SQLiteConnection);
Int64 id = (Int64)command.ExecuteScalar();
cluster.Id = (int)id;
return(cluster);
}
private void clusterDataGridView_SelectionChanged(object sender, EventArgs e)
{
if (clusterDataGridView.SelectedRows == null || clusterDataGridView.SelectedRows.Count == 0)
{
return;
}
DataGridViewRow row = clusterDataGridView.SelectedRows[0];
_selectedCluster = row.DataBoundItem as ClusterDomain;
if (_selectedCluster != null)
{
clusterNameTextBox.Text = _selectedCluster.ClusterName;
}
}
public bool DeleteCluster(ClusterDomain cluster, out string errorMessage)
{
errorMessage = null;
int count = DaoFactory.IncomeClusterDao.GetIncomeClusterCountByCluster(cluster);
if (count > 0)
{
errorMessage = "Cluster tidak dapat dihapus karena Cluster '" + cluster.ClusterName + "' digunakan pada data pendapatan cluster!";
return(false);
}
DaoFactory.ClusterDao.Delete(cluster);
return(true);
}
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));
}
private async Task ProcessHttpsDomainLink(DomainLinkingRequestContract request, Cluster cluster, Domain domainName)
{
var linkEntry = new ClusterDomain()
{
ClusterId = cluster.Id,
Name = string.IsNullOrEmpty(request.SubDomain) ? domainName.Value : request.SubDomain,
Value = string.IsNullOrEmpty(request.SubDomain) ? domainName.Value : request.SubDomain,
RootDomainId = domainName.Id,
Protocol = request.Protocol,
Resolver = request.Resolver
};
if ((await clusterDomainRepository.InsertAsync(linkEntry)) > 0)
{
var domainToInsert = string.IsNullOrEmpty(request.SubDomain) ? domainName.Value : $"{request.SubDomain}.{domainName.Value}";
await traefikRouterService.StoreNewHttpsRule(cluster, request.SubDomain, domainName.Value);
}
}
public List <ClusterDomain> GetAllClusters()
{
SQLiteCommand command = new SQLiteCommand("select * from cluster", DatabaseManager.SQLiteConnection);
SQLiteDataReader reader = command.ExecuteReader();
List <ClusterDomain> clusters = new List <ClusterDomain>();
while (reader.Read())
{
ClusterDomain cluster = new ClusterDomain();
cluster.Id = Convert.ToInt32(reader["id"]);
cluster.ClusterName = reader["cluster_name"] != null?Convert.ToString(reader["cluster_name"]) : null;
clusters.Add(cluster);
}
return(clusters);
}
private void generateReportButton_Click(object sender, EventArgs e)
{
Month fromMonth = filterFromMonthComboBox.SelectedItem as Month;
Month toMonth = filterToMonthComboBox.SelectedItem as Month;
int fromYear;
bool includeFromYear = int.TryParse(filterFromYearTextBox.Text, out fromYear);
int toYear;
bool includeToYear = int.TryParse(filterToYearTextBox.Text, out toYear);
if (fromYear > toYear)
{
MessageBox.Show("Tahun sampai tidak boleh lebih lama dari tahun dari!");
return;
}
else if (fromYear == toYear)
{
if (fromMonth.Index > toMonth.Index)
{
MessageBox.Show("Bulan sampai tidak boleh lebih lama dari bulan dari!");
return;
}
}
ClusterDomain cluster = clusterComboBox.SelectedItem as ClusterDomain;
string message = null;
ProgressTrackerForm form = new ProgressTrackerForm();
form.ProcessInformation = "Mencetak laporan...";
form.Task = new ProgressTrackerForm.BackgroundTask(
() =>
{
LogicFactory.IncomeLogic.GenerateIncomeClusterReport(cluster, fromMonth, fromYear, toMonth, toYear, out message);
});
form.ShowDialog();
if (!String.IsNullOrEmpty(message))
{
MessageBox.Show("Gagal mencetak laporan!");
Clipboard.SetText(message);
}
}
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 Task <int> DeleteAsync(ClusterDomain entity)
{
serviceDbContext.ClusterDomain.Remove(entity);
return(serviceDbContext.SaveChangesAsync());
}
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();
}
}
}
private void filterButton_Click(object sender, EventArgs e)
{
Month fromMonth = filterFromMonthComboBox.SelectedItem as Month;
Month toMonth = filterToMonthComboBox.SelectedItem as Month;
int fromYear;
bool includeFromYear = int.TryParse(filterFromYearTextBox.Text, out fromYear);
int toYear;
bool includeToYear = int.TryParse(filterToYearTextBox.Text, out toYear);
if (fromYear > toYear)
{
MessageBox.Show("Tahun sampai tidak boleh lebih lama dari tahun dari!");
return;
}
else if (fromYear == toYear)
{
if (fromMonth.Index > toMonth.Index)
{
MessageBox.Show("Bulan sampai tidak boleh lebih lama dari bulan dari!");
return;
}
}
_filterActive = true;
ClusterDomain cluster = filterClusterComboBox.SelectedItem as ClusterDomain;
AddressBlockDomain addressBlock = filterAddressBlockComboBox.SelectedItem as AddressBlockDomain;
ProgressTrackerForm progressTrackerForm = new ProgressTrackerForm();
progressTrackerForm.ProcessInformation = "Memfilter data pendapatan cluster...";
progressTrackerForm.Task = new ProgressTrackerForm.BackgroundTask(
() =>
{
_displayedIncomeClusters = _incomeClusters.FindAll(p =>
{
bool match = true;
if (cluster != null)
{
match &= p.ClusterId == cluster.Id;
}
match &= fromMonth.Index <= p.Month;
if (includeFromYear)
{
match &= fromYear <= p.Year;
}
match &= toMonth.Index >= p.Month;
if (includeToYear)
{
match &= toYear >= p.Year;
}
if (addressBlock != null)
{
match &= (!String.IsNullOrEmpty(p.AddressBlock) && p.AddressBlock.ToLower().Trim() == addressBlock.Block.ToLower().Trim());
}
if (!String.IsNullOrEmpty(filterAddressNumberTextBox.Text))
{
match &= (!String.IsNullOrEmpty(p.AddressNumber) && p.AddressNumber.ToLower().Trim() == filterAddressNumberTextBox.Text.ToLower().Trim());
}
return(match);
});
});
progressTrackerForm.ShowDialog();
_setSelectedIncomeCluster = true;
RefreshGrid();
}
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();
}
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 ClusterDomain UpdateCluster(ClusterDomain cluster)
{
return(DaoFactory.ClusterDao.Update(cluster));
}
public Task <int> InsertAsync(ClusterDomain entity)
{
serviceDbContext.ClusterDomain.Add(entity);
return(serviceDbContext.SaveChangesAsync());
}
public Task <int> UpdateAsync(ClusterDomain entity)
{
serviceDbContext.Entry(entity).State = EntityState.Modified;
return(serviceDbContext.SaveChangesAsync());
}
