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