public CessReportModel GetCessReport(string fromDate, string toDate)
{
CessReportModel cessReport = new CessReportModel();
using (var db = new SqlConnection(appSettings.GetConnectionString()))
{
string cessSummaryQuery = string.Empty;
string cessDetailQuery = string.Empty;
cessSummaryQuery = " SELECT convert(varchar(10), CO.Orderdate,103) AS BillDate,SUM(isnull(CO.VatableAmount,0.00)+isnull(CO.NonVatableAmount,0.00)) AS NetSales, SUM(isnull(CO.VatableAmount,0.00)) AS Vatable,SUM(isnull(CO.NonVatableAmount,0.00)) AS NonVatable, SUM(isnull(CO.TaxAmount,0.00)) AS TotalTax, SUM(ISNULL(CO.GrossAmount,0.00)) AS GrandTotal " +
" , convert(numeric(18,2),round(((SUM(isnull(CO.VatableAmount,0)+isnull(CO.NonVatableAmount,0)))*2)/100,2)) As CateringLevy " +
" FROM BILL B " +
" INNER JOIN CustomerOrder CO ON CO.ID = B.CustomerOrderId " +
" Where Convert(Date, CO.Orderdate, 103) between Convert(Date, '" + fromDate + "', 103) and Convert(Date, '" + toDate + "' , 103) And CO.OrderStatus = 4 AND CO.OutletId = " + LoginDetail.OutletId +
" GROUP BY convert(varchar(10), CO.Orderdate,103) " +
" ORDER BY convert(varchar(10), CO.Orderdate,103)";
cessDetailQuery = " SELECT convert(varchar(10), CO.Orderdate, 103) AS BillDate, CO.SalesInvoiceNumber AS InvoiceNumber,(ISNULL(CO.VatableAmount, 0.00) + ISNULL(CO.NonVatableAmount, 0.00)) AS NetSales, ISNULL(CO.VatableAmount, 0.00) AS Vatable, ISNULL(CO.NonVatableAmount, 0.00) AS NonVatable, ISNULL(CO.TaxAmount, 0.00) AS TotalTax, ISNULL(CO.GrossAmount, 0.00) AS GrandTotal " +
", convert(numeric(18, 2), round(((isnull(CO.VatableAmount, 0.00) + isnull(CO.NonVatableAmount, 0.00)) * 2) / 100, 2)) As CateringLevy" +
" FROM BILL B " +
" INNER JOIN CustomerOrder CO ON CO.ID = B.CustomerOrderId " +
" Where Convert(Date, CO.Orderdate, 103) between Convert(Date, '" + fromDate + "', 103) and Convert(Date, '" + toDate + "' , 103) And CO.OrderStatus = 4 AND CO.OutletId = " + LoginDetail.OutletId +
" ORDER BY BillDate";
cessReport.CessSummaryList = db.Query <CessSummaryModel>(cessSummaryQuery).ToList();
cessReport.CessDetailList = db.Query <CessDetailModel>(cessDetailQuery).ToList();
return(cessReport);
}
}
private void btnCessReportExport_Click(object sender, RoutedEventArgs e)
{
int categoryId = -1, foodMenuId = -1;
try
{
if (!DateValidated(dpFromDate, dpToDate))
{
var messageBoxResult = WpfMessageBox.Show(StatusMessages.AppTitle, "Please select FROM DATE grater than or equal to TO DATE", MessageBoxButton.OK, EnumUtility.MessageBoxImage.Warning);
return;
}
CommonMethods commonMethods = new CommonMethods();
string path = string.Empty, firstLine = string.Empty;
CustomerOrderViewModel customerOrderViewModel = new CustomerOrderViewModel();
CessReportModel cessReportModel = new CessReportModel();
CessCategoryReportModel cessCategoryReportModel = new CessCategoryReportModel();
if (chkCess.IsChecked == false)
{
cessReportModel = customerOrderViewModel.GetCessReport(dpFromDate.SelectedDate.Value.ToString(CommonMethods.DateFormat), dpToDate.SelectedDate.Value.ToString(CommonMethods.DateFormat));
string fileName = "CessReport_" + DateTime.Now.ToString("MM-dd-yyyy_HHmmss");
var saveFileDialog = new SaveFileDialog
{
FileName = fileName != "" ? fileName : "gpmfca-exportedDocument",
DefaultExt = ".xlsx",
Filter = "Common Seprated Documents (.xlsx)|*.xlsx"
};
if (saveFileDialog.ShowDialog() == true)
{
path = saveFileDialog.FileName;
firstLine = LoginDetail.ClientName;
commonMethods.WriteCessExcelFile(commonMethods.ConvertToDataTable(cessReportModel.CessSummaryList), commonMethods.ConvertToDataTable(cessReportModel.CessDetailList), path, firstLine);
}
}
else
{
cessCategoryReportModel = customerOrderViewModel.GetCessCategoryReport(dpFromDate.SelectedDate.Value.ToString(CommonMethods.DateFormat), dpToDate.SelectedDate.Value.ToString(CommonMethods.DateFormat), categoryId, foodMenuId);
string fileName = "CategoryWiseCessReport_" + DateTime.Now.ToString("MM-dd-yyyy_HHmmss");
var saveFileDialog = new SaveFileDialog
{
FileName = fileName != "" ? fileName : "gpmfca-exportedDocument",
DefaultExt = ".xlsx",
Filter = "Common Seprated Documents (.xlsx)|*.xlsx"
};
if (saveFileDialog.ShowDialog() == true)
{
path = saveFileDialog.FileName;
firstLine = LoginDetail.ClientName;
commonMethods.WriteCessCategoryExcelFile(commonMethods.ConvertToDataTable(cessCategoryReportModel.CessSummaryList), commonMethods.ConvertToDataTable(cessCategoryReportModel.CessDetailList), path, firstLine);
}
}
}
catch (Exception ex)
{
SystemError.Register(ex);
}
}
