private void CreateDateHeader(DailyReportModel report) { var dateHeader = _sheet.Cell(1, 2); dateHeader.Style.DateFormat.Format = "MMM dd, yyyy"; dateHeader.Value = report.Day; dateHeader.Style.Font.FontSize = FontSize; }
public void ExporDailytReport(DailyReportModel report, string path) { XLWorkbook workbookForSaving = new XLWorkbook(); WorkSheetMaker maker = new WorkSheetMaker(workbookForSaving); maker.Make(report); workbookForSaving.SaveAs(path); }
private void CreateTotal(DailyReportModel report) { _currentRowIndex = _currentRowIndex + 2; var subtotalLabel = _sheet.Cell(_currentRowIndex, 1); subtotalLabel.Value = "Total"; subtotalLabel.Style.Font.FontSize = FontSize; subtotalLabel.Style.Font.Bold = true; var totalAmount = _sheet.Cell(_currentRowIndex, 5); totalAmount.Value = report.TotalAmount; totalAmount.Style.NumberFormat.Format = "#,##0.00_);[Red](#,##0.00)"; totalAmount.DataType = XLCellValues.Number; totalAmount.Style.Font.FontSize = FontSize; totalAmount.Style.Font.Bold = true; _currentRowIndex++; var settledlabel = _sheet.Cell(_currentRowIndex, 1); settledlabel.Value = "Total Settled"; settledlabel.Style.Font.FontSize = FontSize; settledlabel.Style.Font.Bold = true; var settledAmount = _sheet.Cell(_currentRowIndex, 5); settledAmount.Value = report.TotalSettledAmount; settledAmount.Style.NumberFormat.Format = "#,##0.00_);[Red](#,##0.00)"; settledAmount.DataType = XLCellValues.Number; settledAmount.Style.Font.FontSize = FontSize; settledAmount.Style.Font.Bold = true; _currentRowIndex++; var fundingLabel = _sheet.Cell(_currentRowIndex, 1); fundingLabel.Value = "Total For Funding"; fundingLabel.Style.Font.FontSize = FontSize; fundingLabel.Style.Font.Bold = true; var forFundingAmount = _sheet.Cell(_currentRowIndex, 5); forFundingAmount.Value = report.TotalRemaining; forFundingAmount.Style.NumberFormat.Format = "#,##0.00_);[Red](#,##0.00)"; forFundingAmount.DataType = XLCellValues.Number; forFundingAmount.Style.Border.TopBorder = XLBorderStyleValues.Thick; forFundingAmount.Style.Font.FontSize = FontSize; forFundingAmount.Style.Font.Bold = true; }
public IXLWorksheet Make(DailyReportModel report) { SetDefaultValues(); _sheet = _workbook.Worksheets.Add(report.Day.ToString("MMM dd, yyyy")); CreateDateHeader(report); CreateColumn(); foreach (var section in report.SectionsPerDepartment) { CreateDepartmentSection(section); } CreateTotal(report); SetAutoAdjustColumnToContent(); _workbook.Dispose(); return(_sheet); }
public void CreateDaySection(DailyReportModel day) { _currentRowIndex = _currentRowIndex + 5; foreach (var dept in day.SectionsPerDepartment) { _currentColumnIndex = _previousColumnIndex; if (_isFirstInterval) { CreateDepartmentLabel(dept); } _currentRowIndex++; _currentColumnIndex++; _indexOfFirstRowBorder = _currentRowIndex; _indexOfFirstColumnBorder = _currentColumnIndex; CreateWeekDay(day); CreateBanksPerDay(dept); SurroundWithBorder(); } }
private void CreateWeekDay(DailyReportModel day) { var dayCell = _worksheet.Cell(_currentRowIndex, _currentColumnIndex); dayCell.Value = day.DayOfTheWeek; dayCell.DataType = XLCellValues.Text; dayCell.Style.Font.FontSize = FontSize; dayCell.Style.Fill.BackgroundColor = XLColor.PowderBlue; if (day.IsTotalForEntireWeek) { dayCell.Style.Font.Bold = true; } _currentColumnIndex++; _indexOfFirstColumn = _currentColumnIndex - 1; var mergedDay = _worksheet.Range(_currentRowIndex, _indexOfFirstColumn, _currentRowIndex, _currentColumnIndex); mergedDay.Style.Border.OutsideBorder = XLBorderStyleValues.Medium; mergedDay.Merge(); mergedDay.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; _currentRowIndex++; }
public void SetDataSummary(UCDailySaleSumaryReport ucDailyReport, DailyReportModel data) { var fomat = new MoneyFortmat(1); ucDailyReport.lblNetSalse.Text = fomat.getValue(data.NetSale).ToString("C"); Double gst = Convert.ToDouble(data.NetSale) / 11000; if (gst < 1) { ucDailyReport.lblGST.Text = "$0" + money.Format2(Convert.ToDouble(data.NetSale) / 11); } else { ucDailyReport.lblGST.Text = "$" + money.Format2(Convert.ToDouble(data.NetSale) / 11); } ucDailyReport.lblDiscount.Text = fomat.getValue(data.Discount).ToString("C"); ucDailyReport.lblRefund.Text = fomat.getValue(data.Refund).ToString("C"); ucDailyReport.lblTotakReceiva.Text = "$" + fomat.getValue(data.NetSale + data.GST - data.Discount).ToString("N2"); ucDailyReport.lblTotakCash.Text = "$" + fomat.getValue(data.CashTotal).ToString("N2"); ucDailyReport.lblTotalCard.Text = fomat.getValue(data.CardTotal).ToString("C"); ucDailyReport.lblTotalAccount.Text = fomat.getValue(data.AccountTotal).ToString("C"); var export = new List <ExportExcelToDataTable>(); var temp1 = new ExportExcelToDataTable(); temp1.Tilte = "Net Sale"; temp1.Value = "$" + fomat.getValue(data.NetSale).ToString("N2"); export.Add(temp1); var temp2 = new ExportExcelToDataTable(); temp2.Tilte = "GST"; if (gst < 1) { temp2.Value = "$0" + money.Format2(Convert.ToDouble(data.NetSale) / 11); } else { temp2.Value = "$" + money.Format2(Convert.ToDouble(data.NetSale) / 11); } export.Add(temp2); var temp3 = new ExportExcelToDataTable(); temp3.Tilte = "Discount"; temp3.Value = "$" + fomat.getValue(data.Discount).ToString("N2"); export.Add(temp3); var temp4 = new ExportExcelToDataTable(); temp4.Tilte = "Refund"; temp4.Value = "$" + fomat.getValue(data.Refund).ToString("N2"); export.Add(temp4); var temp5 = new ExportExcelToDataTable(); temp5.Tilte = "Total Receivable"; temp5.Value = "$" + fomat.getValue(data.NetSale + data.GST - data.Discount).ToString("N2"); export.Add(temp5); var temp6 = new ExportExcelToDataTable(); temp6.Tilte = "Total CASH"; temp6.Value = "$" + fomat.getValue(data.CashTotal).ToString("N2"); export.Add(temp6); var temp7 = new ExportExcelToDataTable(); temp7.Tilte = "Total CARD"; temp7.Value = "$" + fomat.getValue(data.CardTotal).ToString("N2"); export.Add(temp7); var temp8 = new ExportExcelToDataTable(); temp8.Tilte = "Total ACCOUNT"; temp8.Value = "$" + fomat.getValue(data.AccountTotal).ToString("N2"); export.Add(temp8); ucDailyReport.btnExport.Tag = export; ucDailyReport.btnExport.Click += ExportExcel_Click; ucDailyReport.btnPrint.Tag = export; ucDailyReport.btnPrint.Click += PrinterClick_Click; }
public Daily_Report(DailyReportModel model, Daily_DS ds) { this.model = model; this.ds = ds; InitializeComponent(); }
private async void button1_Click(object sender, EventArgs e) { var ds = new Daily_DS(); var cmd = DBConn.CreateCommand(); var i = 1; decimal income = 0; decimal expense = 0; decimal profit = 0; cmd.CommandText = "SELECT SUM(price*size/1000), SUM((price-buyingPrice)*size/1000) FROM resorder INNER JOIN receipt ON resorder.resID = receipt.resID WHERE date = @date AND goodStatus = 2"; cmd.Parameters.AddWithValue("@date", dtpDate.Value.ToString("yyyy-MM-dd")); var reader = await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { if (reader.GetValue(0) is DBNull) { reader.Dispose(); break; } var tempIncome = "Rs. " + Math.Round(reader.GetFieldValue <decimal>(0), 2); var tempProfit = "Rs. " + Math.Round(reader.GetFieldValue <decimal>(1), 2); DataRow dr = ds.Tables["Daily Report"].NewRow(); dr["Index"] = i++; dr["Description"] = "Receipt balance"; dr["Income"] = tempIncome; dr["Expenses"] = ""; dr["Profit"] = tempProfit; ds.Tables["Daily Report"].Rows.Add(dr); income += reader.GetFieldValue <decimal>(0); profit += reader.GetFieldValue <decimal>(1); } reader.Dispose(); cmd.CommandText = "SELECT place, SUM(IF(type = 1, qty * selling, 0)), SUM(IF(type = 1, (selling - buying) * qty, 0)), SUM(IF(type = 2, selling, 0)) FROM tripdetails INNER JOIN trip ON tripdetails.tripID = trip.tripID WHERE date = @date GROUP BY place"; reader = await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { if (reader.GetValue(1) is DBNull && reader.GetValue(2) is DBNull && reader.GetValue(3) is DBNull) { reader.Dispose(); break; } string tempIncome = "Rs. " + Math.Round(reader.GetFieldValue <decimal>(1), 2); string tempProfit = "Rs. " + (Math.Round(reader.GetFieldValue <decimal>(2), 2) - Math.Round(reader.GetFieldValue <decimal>(3), 2)); string tempExpense = "Rs. " + Math.Round(reader.GetFieldValue <decimal>(3), 2); DataRow dr = ds.Tables["Daily Report"].NewRow(); dr["Index"] = i++; dr["Description"] = "Trip - " + reader.GetFieldValue <string>(0); dr["Income"] = tempIncome; dr["Expenses"] = tempExpense; dr["Profit"] = tempProfit; ds.Tables["Daily Report"].Rows.Add(dr); income += reader.GetFieldValue <decimal>(1); profit += reader.GetFieldValue <decimal>(2) - reader.GetFieldValue <decimal>(3); expense += reader.GetFieldValue <decimal>(3); } reader.Dispose(); cmd.CommandText = "SELECT description, amount FROM expenses WHERE date = @date"; reader = await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { string tempExpense = "Rs. " + Math.Round(reader.GetFieldValue <decimal>(1), 2); DataRow dr = ds.Tables["Daily Report"].NewRow(); dr["Index"] = i++; dr["Description"] = reader.GetFieldValue <string>(0); dr["Income"] = ""; dr["Expenses"] = tempExpense; dr["Profit"] = ""; ds.Tables["Daily Report"].Rows.Add(dr); profit -= reader.GetFieldValue <decimal>(1); expense += reader.GetFieldValue <decimal>(1); } reader.Dispose(); if (income == 0 && profit == 0 && expense == 0) { MessageBox.Show("No data to show in this day", "No data", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); return; } var model = new DailyReportModel() { date = dtpDate.Value, income = Math.Round(income, 2), expenses = Math.Round(expense, 2), profit = Math.Round(profit, 2) }; dataGridView1.DataSource = ds.Tables["Daily Report"]; var frm = new Daily_Report(model, ds); frm.Show(); }