Пример #1
0
        public IncomeForm()
        {
            InitializeComponent();

            monthComboBox.DataSource    = _months;
            monthComboBox.DisplayMember = "Name";

            filterFromMonthComboBox.DataSource    = MonthUtility.GetMonths();
            filterFromMonthComboBox.DisplayMember = "Name";

            filterToMonthComboBox.DataSource    = MonthUtility.GetMonths();
            filterToMonthComboBox.DisplayMember = "Name";

            filterFromYearTextBox.Text = DateTime.Now.Year.ToString();
            filterToYearTextBox.Text   = DateTime.Now.Year.ToString();

            Month month = _months.Find(p => p.Index == DateTime.Now.Month);

            filterFromMonthComboBox.SelectedItem = month;
            filterToMonthComboBox.SelectedItem   = month;

            _incomeSources = LogicFactory.IncomeSourceLogic.GetAllIncomeSources();
            incomeSourceComboBox.DataSource    = _incomeSources;
            incomeSourceComboBox.DisplayMember = "Description";

            SetUIControlsAvailability();
        }
Пример #2
0
        public AccountingReportForm()
        {
            InitializeComponent();

            filterFromMonthComboBox.DataSource    = _months;
            filterFromMonthComboBox.DisplayMember = "Name";

            filterToMonthComboBox.DataSource    = MonthUtility.GetMonths();
            filterToMonthComboBox.DisplayMember = "Name";

            yearTextBox.Text = DateTime.Now.Year.ToString();
        }
Пример #3
0
        public BalanceReportForm()
        {
            InitializeComponent();

            filterFromMonthComboBox.DataSource    = _months;
            filterFromMonthComboBox.DisplayMember = "Name";

            filterToMonthComboBox.DataSource    = MonthUtility.GetMonths();
            filterToMonthComboBox.DisplayMember = "Name";

            yearTextBox.Text = DateTime.Now.Year.ToString();

            Month month = _months.Find(p => p.Index == DateTime.Now.Month);
        }
        public IncomeClusterReportForm()
        {
            InitializeComponent();

            _clusters = LogicFactory.ClusterLogic.GetAllCluster();
            clusterComboBox.DataSource    = _clusters;
            clusterComboBox.DisplayMember = "ClusterName";

            filterFromMonthComboBox.DataSource    = _months;
            filterFromMonthComboBox.DisplayMember = "Name";

            filterToMonthComboBox.DataSource    = MonthUtility.GetMonths();
            filterToMonthComboBox.DisplayMember = "Name";

            filterFromYearTextBox.Text = DateTime.Now.Year.ToString();
            filterToYearTextBox.Text   = DateTime.Now.Year.ToString();
        }
Пример #5
0
        public IncomeClusterForm()
        {
            InitializeComponent();
            SetUIControlsAvailability();

            _clusters = LogicFactory.ClusterLogic.GetAllCluster();
            clusterComboBox.DataSource    = _clusters;
            clusterComboBox.DisplayMember = "ClusterName";

            filterClusterComboBox.DataSource    = LogicFactory.ClusterLogic.GetAllCluster();
            filterClusterComboBox.DisplayMember = "ClusterName";

            monthComboBox.DataSource    = _months;
            monthComboBox.DisplayMember = "Name";

            filterFromMonthComboBox.DataSource    = MonthUtility.GetMonths();
            filterFromMonthComboBox.DisplayMember = "Name";

            filterToMonthComboBox.DataSource    = MonthUtility.GetMonths();
            filterToMonthComboBox.DisplayMember = "Name";

            filterFromYearTextBox.Text = DateTime.Now.Year.ToString();
            filterToYearTextBox.Text   = DateTime.Now.Year.ToString();

            Month month = _months.Find(p => p.Index == DateTime.Now.Month);

            filterFromMonthComboBox.SelectedItem = month;
            filterToMonthComboBox.SelectedItem   = month;

            _addressBlocks = LogicFactory.AddressBlockLogic.GetAllAddressBlocks();
            addressBlockComboBox.DataSource    = _addressBlocks;
            addressBlockComboBox.DisplayMember = "Block";

            List <AddressBlockDomain> addressBlocks = LogicFactory.AddressBlockLogic.GetAllAddressBlocks();

            filterAddressBlockComboBox.DataSource    = addressBlocks;
            filterAddressBlockComboBox.DisplayMember = "Block";
        }
Пример #6
0
        public ExpenseForm()
        {
            InitializeComponent();

            monthComboBox.DataSource    = _months;
            monthComboBox.DisplayMember = "Name";

            filterFromMonthComboBox.DataSource    = MonthUtility.GetMonths();
            filterFromMonthComboBox.DisplayMember = "Name";

            filterToMonthComboBox.DataSource    = MonthUtility.GetMonths();
            filterToMonthComboBox.DisplayMember = "Name";

            filterFromYearTextBox.Text = DateTime.Now.Year.ToString();
            filterToYearTextBox.Text   = DateTime.Now.Year.ToString();

            Month month = _months.Find(p => p.Index == DateTime.Now.Month);

            filterFromMonthComboBox.SelectedItem = month;
            filterToMonthComboBox.SelectedItem   = month;

            SetUIControlsAvailability();
        }
Пример #7
0
        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();
                }
            }
        }
Пример #8
0
        public void GenerateBalanceReport(Month fromMonth, Month toMonth, int year, out string message)
        {
            message = null;
            string fontName = "Calibri";
            bool   success  = true;

            try
            {
                ExcelUtility.CreateExcelDocument();
                ExcelUtility.SetGridVisibility(false);

                ExcelUtility.Write(0, 0, 0, 4, "LAPORAN KEUANGAN", null, fontName, 20, true, false, true);
                ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
                ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 25.8);

                ExcelUtility.Write(1, 0, 1, 4, "WARGA CLUSTER ARGA PADMA NIRWANA", null, fontName, 22, true, true, true);
                ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
                ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 28.8);

                string monthText = fromMonth.Name;
                if (toMonth.Index != fromMonth.Index)
                {
                    monthText += " - " + toMonth.Name;
                }
                ExcelUtility.Write(2, 0, 2, 4, "PERIODE BULAN " + monthText + " " + year.ToString(), null, fontName, 14, true, false, true);
                ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
                ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 18);

                ExcelUtility.Write(3, 4, DateTime.Today, "dd MMM yyyy", fontName, 14, false, false);
                ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Right);

                string[] columnNames  = new string[] { "NO", "KETERANGAN", "PEMASUKAN", "PENGELUARAN", "SALDO" };
                double[] columnWidths = new double[] { 5.67, 77.11, 20.56, 20.56, 20.56 };
                for (int i = 0; i < columnNames.Length; i++)
                {
                    ExcelUtility.Write(4, i, columnNames[i], null, fontName, 14, true, false);
                    ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, columnWidths[i]);
                    ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 51);
                    ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
                    ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
                    ExcelUtility.SetPropertyValue(RangeProperty.CellColor, System.Drawing.Color.FromArgb(183, 222, 223));
                }

                ExcelUtility.Write(6, 1, "Saldo akhir tahun " + (year - 1).ToString(), null, fontName, 14, false, false);
                ExcelUtility.Write(6, 4, 0, "#,##0", fontName, 14, true, false);

                List <IncomeClusterDomain> lastYearIncomeCluster = DaoFactory.IncomeClusterDao.GetIncomeClusters(1, 12, (year - 1));

                List <IncomeClusterDomain> incomeClusters = DaoFactory.IncomeClusterDao.GetIncomeClusters(fromMonth.Index, toMonth.Index, year);
                List <ClusterDomain>       clusters       = DaoFactory.ClusterDao.GetAllClusters();

                List <Month> months = MonthUtility.GetMonths();

                List <int> incomeRows = new List <int>();

                int  rowIndex        = 7;
                int  lastRowIndex    = 6;
                int  startRowIndex   = 7;
                bool collectStartRow = true;
                for (int i = 0; i < clusters.Count; i++)
                {
                    List <IncomeClusterDomain> lastYearCurrentClusterIncomes = lastYearIncomeCluster.FindAll(p => p.ClusterId == clusters[i].Id);
                    List <IncomeClusterDomain> currentClusterIncomes         = incomeClusters.FindAll(p => p.ClusterId == clusters[i].Id);

                    if ((lastYearCurrentClusterIncomes == null || lastYearCurrentClusterIncomes.Count == 0) &&
                        (currentClusterIncomes == null || currentClusterIncomes.Count == 0))
                    {
                        continue;
                    }

                    ExcelUtility.Write(rowIndex++, 1, clusters[i].ClusterName, null, fontName, 14, true, false);

                    if (collectStartRow)
                    {
                        startRowIndex   = rowIndex;
                        collectStartRow = false;
                    }

                    double amount = 0;
                    foreach (IncomeClusterDomain incomeCluster in lastYearCurrentClusterIncomes)
                    {
                        amount += incomeCluster.Amount;
                    }

                    ExcelUtility.Write(rowIndex, 0, "1", "@", fontName, 14, false, false);
                    ExcelUtility.Write(rowIndex, 1, "Setoran IPL Cluster " + clusters[i].ClusterName + " Tahun " + (year - 1).ToString(), "@", fontName, 14, false, false);
                    ExcelUtility.Write(rowIndex, 2, amount, "#,##0", fontName, 14, false, false);

                    ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(4, lastRowIndex) + "+" + ExcelUtility.GetExcelCellName(2, rowIndex), "#,##0", fontName, 14, false, false);
                    lastRowIndex = rowIndex;
                    rowIndex++;

                    int  count         = 2;
                    bool printSubTotal = false;
                    for (int month = 1; month <= 12; month++)
                    {
                        List <IncomeClusterDomain> result = currentClusterIncomes.FindAll(p => p.Month == month);
                        if (result == null || result.Count == 0)
                        {
                            continue;
                        }

                        if (!printSubTotal)
                        {
                            printSubTotal = true;
                        }

                        double totalIncome = 0;
                        foreach (IncomeClusterDomain row in result)
                        {
                            totalIncome += row.Amount;
                        }

                        Month m = months.Find(p => p.Index == month);
                        ExcelUtility.Write(rowIndex, 0, count.ToString(), "@", fontName, 14, false, false);
                        ExcelUtility.Write(rowIndex, 1, "Setoran IPL Cluster " + clusters[i].ClusterName + " Bulan " + m.Name + " Tahun " + year.ToString(), "@", fontName, 14, false, false);
                        ExcelUtility.Write(rowIndex, 2, totalIncome, "#,##0", fontName, 14, false, false);

                        ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(4, lastRowIndex) + "+" + ExcelUtility.GetExcelCellName(2, rowIndex), "#,##0", fontName, 14, false, false);
                        lastRowIndex = rowIndex;
                        rowIndex++;
                        count++;
                    }

                    if (printSubTotal)
                    {
                        ExcelUtility.Write(rowIndex, 1, "Sub Total Cluster " + clusters[i].ClusterName, "@", fontName, 14, true, false);
                        ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);

                        ExcelUtility.Write(rowIndex, 2, "=SUM(" + ExcelUtility.GetExcelCellName(2, startRowIndex) + ":" + ExcelUtility.GetExcelCellName(2, rowIndex - 1) + ")", "#,##0", fontName, 14, true, false);

                        incomeRows.Add(rowIndex);
                        rowIndex++;
                    }
                }

                List <IncomeDomain> incomes = DaoFactory.IncomeDao.GetIncomes(fromMonth.Index, toMonth.Index, year);

                if (incomes != null && incomes.Count > 0)
                {
                    List <IncomeSourceDomain> incomeSources = DaoFactory.IncomeSourceDao.GetAllIncomeSources();
                    Dictionary <int, double>  totalIncomes  = new Dictionary <int, double>();
                    foreach (IncomeDomain income in incomes)
                    {
                        if (totalIncomes.ContainsKey(income.IncomeSourceId))
                        {
                            totalIncomes[income.IncomeSourceId] += income.Amount;
                        }
                        else
                        {
                            totalIncomes[income.IncomeSourceId] = income.Amount;
                        }
                    }

                    ExcelUtility.Write(rowIndex++, 1, "PEMASUKAN", "@", fontName, 14, true, false);

                    int count = 1;
                    startRowIndex = rowIndex;
                    foreach (KeyValuePair <int, double> pair in totalIncomes)
                    {
                        IncomeSourceDomain source = incomeSources.Find(p => p.Id == pair.Key);

                        ExcelUtility.Write(rowIndex, 0, count.ToString(), "@", fontName, 14, false, false);
                        ExcelUtility.Write(rowIndex, 1, source.Description + " selama tahun " + year.ToString(), "@", fontName, 14, false, false);
                        ExcelUtility.Write(rowIndex, 2, pair.Value, "#,##0", fontName, 14, false, false);

                        ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(4, lastRowIndex) + "+" + ExcelUtility.GetExcelCellName(2, rowIndex), "#,##0", fontName, 14, false, false);
                        lastRowIndex = rowIndex;
                        rowIndex++;
                        count++;
                    }

                    ExcelUtility.Write(rowIndex, 1, "Sub Total", null, fontName, 14, true, false);
                    ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);

                    ExcelUtility.Write(rowIndex, 2, "=SUM(" + ExcelUtility.GetExcelCellName(2, startRowIndex) + ":" + ExcelUtility.GetExcelCellName(2, rowIndex - 1) + ")", "#,##0", fontName, 14, true, false);

                    incomeRows.Add(rowIndex);

                    rowIndex++;
                }

                List <ExpenseDomain> expenses = DaoFactory.ExpenseDao.GetExpenses(fromMonth.Index, toMonth.Index, year);

                int startExpenseRow = 0;
                if (expenses != null && expenses.Count > 0)
                {
                    ExcelUtility.Write(rowIndex++, 1, "PENGELUARAN", "@", fontName, 14, true, false);

                    startExpenseRow = rowIndex;
                    int countExpense = 1;
                    for (int month = 1; month <= 12; month++)
                    {
                        List <ExpenseDomain> result = expenses.FindAll(p => p.Month == month);
                        if (result == null || result.Count == 0)
                        {
                            continue;
                        }

                        double totalExpense = 0;
                        foreach (ExpenseDomain row in result)
                        {
                            totalExpense += row.Amount;
                        }

                        Month m = months.Find(p => p.Index == month);
                        ExcelUtility.Write(rowIndex, 0, countExpense.ToString(), "@", fontName, 14, false, false);
                        ExcelUtility.Write(rowIndex, 1, "Pengeluaran selama bulan " + m.Name + " " + year.ToString(), "@", fontName, 14, false, false);
                        ExcelUtility.Write(rowIndex, 3, totalExpense, "#,##0", fontName, 14, false, false);

                        ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(4, lastRowIndex) + "-" + ExcelUtility.GetExcelCellName(3, rowIndex), "#,##0", fontName, 14, false, false);
                        lastRowIndex = rowIndex;
                        rowIndex++;
                        countExpense++;
                    }
                }

                rowIndex += 2;

                ExcelUtility.Write(rowIndex, 1, "Saldo", "@", fontName, 14, true, false);
                string[] cellNames = new string[incomeRows.Count];
                for (int x = 0; x < incomeRows.Count; x++)
                {
                    cellNames[x] = ExcelUtility.GetExcelCellName(2, incomeRows[x]);
                }

                ExcelUtility.Write(rowIndex, 2, "=" + String.Join("+", cellNames), "#,##0", fontName, 14, false, false);
                ExcelUtility.Write(rowIndex, 3, "=SUM(" + ExcelUtility.GetExcelCellName(3, startExpenseRow) + ":" + ExcelUtility.GetExcelCellName(3, rowIndex - 3) + ")", "#,##0", fontName, 14, false, false);

                ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(2, rowIndex) + "-" + ExcelUtility.GetExcelCellName(3, rowIndex) + "+" + ExcelUtility.GetExcelCellName(3, 6), "#,##0", fontName, 14, false, false);

                rowIndex++;

                ExcelUtility.Write(rowIndex, 1, "Saldo di buku tabungan tahun " + (year - 1).ToString(), "@", fontName, 14, true, false);
                ExcelUtility.Write(rowIndex, 4, "0", "#,##0", fontName, 14, false, false);
                rowIndex++;

                ExcelUtility.Write(rowIndex, 1, "Cash on Hand", "@", fontName, 14, true, false);
                ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(4, rowIndex - 2) + "-" + ExcelUtility.GetExcelCellName(4, rowIndex - 1), "#,##0", fontName, 14, false, false);
                rowIndex += 2;

                ExcelUtility.Write(rowIndex, 1, "Dibuat Oleh,", "@", fontName, 14, false, false);
                ExcelUtility.Write(rowIndex + 6, 1, "Bendahara", "@", fontName, 14, false, false);

                ExcelUtility.Write(rowIndex, 3, "Mengetahui,", "@", fontName, 14, false, false);
                ExcelUtility.Write(rowIndex + 6, 3, "Ketua Cluster", "@", fontName, 14, false, false);

                for (int i = 4; i <= rowIndex - 2; i++)
                {
                    for (int j = 0; j <= 4; j++)
                    {
                        BorderWeight topBorderWeight    = BorderWeight.Thin;
                        BorderWeight leftBorderWeight   = BorderWeight.Thin;
                        BorderWeight rightBorderWeight  = BorderWeight.Thin;
                        BorderWeight bottomBorderWeight = BorderWeight.Thin;
                        if (i == 4 || i == rowIndex - 4 || i == rowIndex - 3 || i == rowIndex - 2)
                        {
                            topBorderWeight = BorderWeight.Thick;
                        }
                        if (i == rowIndex - 2)
                        {
                            bottomBorderWeight = BorderWeight.Thick;
                        }

                        if (j == 0)
                        {
                            leftBorderWeight = BorderWeight.Thick;
                        }

                        if (j == 4)
                        {
                            rightBorderWeight = BorderWeight.Thick;
                        }

                        ExcelUtility.SetCurrentCell(i, j);
                        ExcelUtility.SetBorder(true, true, true, true, leftBorderWeight, topBorderWeight, rightBorderWeight, bottomBorderWeight);
                    }
                }

                ExcelUtility.SetCurrentCell(rowIndex - 1, 0, rowIndex + 7, 0);
                ExcelUtility.SetBorder(true, false, false, false, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);

                ExcelUtility.SetCurrentCell(rowIndex - 1, 4, rowIndex + 7, 4);
                ExcelUtility.SetBorder(false, false, true, false, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);

                ExcelUtility.SetCurrentCell(rowIndex + 7, 0, rowIndex + 7, 4);
                ExcelUtility.SetBorder(false, false, false, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
            }
            catch (Exception ex)
            {
                message = ex.Message;
                success = false;
            }
            finally
            {
                if (success)
                {
                    ExcelUtility.DisplayExcelDocument();
                }
                else
                {
                    ExcelUtility.CloseExcelDocument();
                }
            }
        }
Пример #9
0
        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();
                }
            }
        }