public CessCategoryReportModel GetCessCategoryReport(string fromDate, string toDate, int categoryId, int foodMenuId) { CessCategoryReportModel cessReport = new CessCategoryReportModel(); using (var db = new SqlConnection(appSettings.GetConnectionString())) { string cessSummaryQuery = string.Empty; string cessDetailQuery = string.Empty; cessSummaryQuery = " SELECT FoodmenucategoryName," + " SUM(isnull(COI.VatableAmount, 0.00) + isnull(COI.NonVatableAmount, 0.00)) AS NetSales, SUM(isnull(COI.VatableAmount, 0)) as Vatable, SUM(isnull(COI.NonVatableAmount, 0.00)) AS NonVatable," + " SUM(isnull(COI.FoodMenuVat, 0.00)) AS TotalTax, SUM(ISNULL(COI.Grossamount, 0.00)) AS GrandTotal," + " convert(numeric(18, 2), round(((SUM(isnull(COI.VatableAmount, 0) + isnull(COI.NonVatableAmount, 0))) * 2) / 100, 2)) As CateringLevy" + " FROM CustomerOrder CO" + " INNER join CustomerOrderItem coi on CO.Id = COI.CustomerOrderId" + " inner join Foodmenu FM on FM.Id = COI.Foodmenuid" + " inner join Foodmenucategory FMC on FMC.ID = FM.FoodCAtegoryId" + " where CO.Orderstatus = 4 "; if (categoryId != -1) { cessSummaryQuery += " And FMC.Id = " + categoryId; } if (foodMenuId != -1) { cessSummaryQuery += " And FM.Id = " + foodMenuId; } cessSummaryQuery += " AND Convert(Date, CO.Orderdate, 103) between Convert(Date, '" + fromDate + "', 103) and Convert(Date, '" + toDate + "' , 103) and CO.Isdeleted = 0 AND CO.OutletId = " + LoginDetail.OutletId + " group by FoodmenucategoryName ORDER BY FoodmenucategoryName"; 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 <CessCategorySummaryModel>(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); } }