public string FastMoveItems(bool FastOrSlowReport, int NumberOfItems, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "") { try { Excel.Range chartRange; xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlApp.Visible = false; xlApp.SheetsInNewWorkbook = 1; xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue string ReportName = (FastOrSlowReport ? "Fast Move Items Report" : "Slow Move Items Report"); DataTable aTable = ReportsMgmt.FastMovItemBasedOnQty(NumberOfItems, DateFrom, DateTo, FastOrSlowReport); if (aTable.Rows.Count > 0) { int RowCnt = 1; xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Name = ReportName; List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1); List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1); for (int i = 0; i < aHeader.Count; i++) { string astringss = aHeader[i]; xlWorkSheet.Cells[RowCnt, 2] = aHeader[i]; RowCnt++; } xlWorkSheet.Cells[RowCnt++, 1] = ReportName; xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom; xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo; RowCnt++; xlWorkSheet.Cells[RowCnt, 1] = "Item Description"; xlWorkSheet.Cells[RowCnt, 2] = "Qty Sold"; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true; //xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; RowCnt++; int DataStart = RowCnt; foreach (DataRow aRow in aTable.Rows) { xlWorkSheet.Cells[RowCnt, 1] = aRow["ItemDescription"].ToString(); xlWorkSheet.Cells[RowCnt, 2] = aRow["Summation"].ToString(); RowCnt++; } xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()); oRng.EntireColumn.AutoFit(); Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); xlWorkSheet.DisplayRightToLeft = false; int size = aTable.Rows.Count * 100; if (size >= 600) { size = 600; } if (size < 300) { size = 300; } Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300); Excel.Chart chartPage = myChart.Chart; Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Excel.Series series1 = seriesCollection.NewSeries(); RowCnt--; //because we started from 1 suppose to be 0 series1.Name = ReportName; series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()]; chartPage.ChartType = Excel.XlChartType.xlColumnClustered; Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis; series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml; xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format); if (ExportToExcel) { format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7; xlWorkSheet.SaveAs(ExportPath, format); } xlApp.UserControl = false; return("TRUE"); } else { return("EMPTY"); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return("ERROR"); } finally { //Once done close and quit Excel xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } }
public string VendorBalanceStatement(int VendorID, string DateFrom, string DateTo, bool TableBorder = false, bool Preview = true, bool PrintToThermal = false, bool ExportToPdf = false, bool ExportToExcel = false, string ExportPath = "", bool colored = false) { List <DataTable> aDTlist = new List <DataTable>(); bool aRevisedExist = false; DataTable aBillDataTable = ReportsMgmt.SelectVendorPurchaseINVOICEsByIDandDate(VendorID, DateFrom, DateTo); DataTable aPaymentDataTable = ReportsMgmt.SelectVendorPaymentsByIDandDate(VendorID, DateFrom, DateTo); #region ConcatTables DataTable ConcatTable = new DataTable(); ConcatTable.Columns.Add("Description"); ConcatTable.Columns.Add("Date", typeof(DateTime)); ConcatTable.Columns.Add("#"); ConcatTable.Columns.Add("Debit"); ConcatTable.Columns.Add("Credit"); int Cnt = 0; char AM = (char)1589; char PM = (char)1605; foreach (DataRow aRow in aPaymentDataTable.Rows) { ConcatTable.Rows.Add(); ConcatTable.Rows[Cnt]["Description"] = "Payment"; ConcatTable.Rows[Cnt]["Date"] = Convert.ToDateTime((Convert.ToDateTime(aRow["Date"]).ToShortDateString() + " " + aRow["Time"].ToString().ToString().Replace((string)(AM + ""), "AM").Replace((string)(PM + ""), "PM"))); ConcatTable.Rows[Cnt]["#"] = aRow["PaymentNumber"]; ConcatTable.Rows[Cnt]["Credit"] = "-"; ConcatTable.Rows[Cnt]["Debit"] = aRow["Amount"]; Cnt++; if (int.Parse(aRow["IsRevised"].ToString()) == 1) { ConcatTable.Rows.Add(); ConcatTable.Rows[Cnt]["Description"] = "Reversed Payment"; ConcatTable.Rows[Cnt]["Date"] = Convert.ToDateTime((Convert.ToDateTime(aRow["ReviseDate"]).ToShortDateString() + " " + aRow["ReviseTime"].ToString().ToString().Replace((string)(AM + ""), "AM").Replace((string)(PM + ""), "PM"))); ConcatTable.Rows[Cnt]["#"] = aRow["PaymentNumber"]; ConcatTable.Rows[Cnt]["Credit"] = double.Parse(aRow["Amount"].ToString()); ConcatTable.Rows[Cnt]["Debit"] = "-"; Cnt++; } } foreach (DataRow aRow in aBillDataTable.Rows) { ConcatTable.Rows.Add(); ConcatTable.Rows[Cnt]["Description"] = "Bill"; ConcatTable.Rows[Cnt]["Date"] = Convert.ToDateTime((Convert.ToDateTime(aRow["Date"]).ToShortDateString() + " " + aRow["Time"].ToString().ToString().Replace((string)(AM + ""), "AM").Replace((string)(PM + ""), "PM"))); ConcatTable.Rows[Cnt]["#"] = aRow["VoucherNumber"]; ConcatTable.Rows[Cnt]["Credit"] = aRow["TotalCost"]; ConcatTable.Rows[Cnt]["Debit"] = "-"; Cnt++; if (int.Parse(aRow["IsRevised"].ToString()) == 1) { ConcatTable.Rows.Add(); ConcatTable.Rows[Cnt]["Description"] = "Reversed Bill"; ConcatTable.Rows[Cnt]["Date"] = Convert.ToDateTime((Convert.ToDateTime(aRow["ReviseDate"]).ToShortDateString() + " " + aRow["ReviseTime"].ToString().ToString().Replace((string)(AM + ""), "AM").Replace((string)(PM + ""), "PM"))); ConcatTable.Rows[Cnt]["#"] = aRow["VoucherNumber"]; ConcatTable.Rows[Cnt]["Credit"] = "-"; ConcatTable.Rows[Cnt]["Debit"] = double.Parse(aRow["TotalCost"].ToString()); Cnt++; } } ConcatTable.DefaultView.Sort = "Date ASC"; ConcatTable = ConcatTable.DefaultView.ToTable(); #endregion ConcatTables DataTable aMergedTable = new DataTable(); aMergedTable.Columns.Add("Description"); aMergedTable.Columns.Add("Date", typeof(DateTime)); aMergedTable.Columns.Add("#"); aMergedTable.Columns.Add("Debit"); aMergedTable.Columns.Add("Credit"); aMergedTable.Columns.Add("Balance"); aMergedTable.Rows.Add(); //for startign balance aMergedTable.Rows[0]["Description"] = "Opening Balance"; aMergedTable.Rows[0]["#"] = "-"; aMergedTable.Rows[0]["Debit"] = "-"; aMergedTable.Rows[0]["Credit"] = "-"; if (aBillDataTable.Rows.Count > 0) { aMergedTable.Rows[0]["Date"] = Convert.ToDateTime(Convert.ToDateTime(aBillDataTable.Rows[0]["Date"]).ToShortDateString() + " " + Convert.ToDateTime(aBillDataTable.Rows[0]["Time"].ToString()).ToShortTimeString()); aMergedTable.Rows[0]["Balance"] = Math.Round(double.Parse(aBillDataTable.Rows[0]["VendorAccountAmountOld"].ToString()), 2); } if (aPaymentDataTable.Rows.Count > 0) { DateTime tempdt = Convert.ToDateTime(Convert.ToDateTime(aPaymentDataTable.Rows[0]["Date"]).ToShortDateString() + " " + Convert.ToDateTime(aPaymentDataTable.Rows[0]["Time"].ToString()).ToShortTimeString()); if (aBillDataTable.Rows.Count > 0) { if (tempdt < Convert.ToDateTime(aMergedTable.Rows[0]["Date"])) { aMergedTable.Rows[0]["Date"] = tempdt; aMergedTable.Rows[0]["Balance"] = Math.Round(double.Parse(aPaymentDataTable.Rows[0]["OldVendorAccountAmount"].ToString()), 2); } } else { aMergedTable.Rows[0]["Date"] = tempdt; aMergedTable.Rows[0]["Balance"] = Math.Round(double.Parse(aPaymentDataTable.Rows[0]["OldVendorAccountAmount"].ToString()), 2); } } double FinalAmount = 0.00; double.TryParse(aMergedTable.Rows[0]["Balance"].ToString(), out FinalAmount); int aCounter = 1; #region OLDCODE //foreach (DataRow aRow in aPaymentDataTable.Rows) //{ // aMergedTable.Rows.Add(); // aMergedTable.Rows[aCounter]["Description"] = "Payment"; // aMergedTable.Rows[aCounter]["Date"] = Convert.ToDateTime(Convert.ToDateTime(aRow["Date"]).ToShortDateString() + " " + Convert.ToDateTime(aRow["Time"].ToString()).ToShortTimeString()); // aMergedTable.Rows[aCounter]["#"] = aRow["PaymentNumber"]; // aMergedTable.Rows[aCounter]["Debit"] = aRow["Amount"]; // aMergedTable.Rows[aCounter]["Credit"] = "-"; // aMergedTable.Rows[aCounter]["Balance"] = Math.Round((double.Parse(aRow["OldVendorAccountAmount"].ToString()) - double.Parse(aMergedTable.Rows[aCounter]["Debit"].ToString())), 2); // aCounter++; // if (int.Parse(aRow["IsRevised"].ToString()) == 1) // { // aRevisedExist = true; // aMergedTable.Rows.Add(); // aMergedTable.Rows[aCounter]["Description"] = "Reversed Invoice"; // aMergedTable.Rows[aCounter]["Date"] = Convert.ToDateTime(Convert.ToDateTime(aRow["ReviseDate"]).ToShortDateString() + " " + Convert.ToDateTime(aRow["ReviseTime"].ToString()).ToShortTimeString()); // aMergedTable.Rows[aCounter]["#"] = aRow["PaymentNumber"]; // aMergedTable.Rows[aCounter]["Credit"] = double.Parse(aRow["Amount"].ToString()); // aMergedTable.Rows[aCounter]["Debit"] = "-"; // aMergedTable.Rows[aCounter]["Balance"] = Math.Round((double.Parse(aMergedTable.Rows[aCounter - 1]["Balance"].ToString()) + double.Parse(aMergedTable.Rows[aCounter]["Credit"].ToString())), 2); // aCounter++; // } //} //foreach (DataRow aRow in aBillDataTable.Rows) //{ // aMergedTable.Rows.Add(); // aMergedTable.Rows[aCounter]["Description"] = "Bill"; // aMergedTable.Rows[aCounter]["Date"] = Convert.ToDateTime(Convert.ToDateTime(aRow["Date"]).ToShortDateString() + " " + Convert.ToDateTime(aRow["Time"].ToString()).ToShortTimeString()); // aMergedTable.Rows[aCounter]["#"] = aRow["VoucherNumber"]; // aMergedTable.Rows[aCounter]["Debit"] = "-"; // aMergedTable.Rows[aCounter]["Credit"] = aRow["TotalCost"]; // aMergedTable.Rows[aCounter]["Balance"] = Math.Round((double.Parse(aRow["VendorAccountAmountOld"].ToString()) + double.Parse(aMergedTable.Rows[aCounter]["Credit"].ToString())), 2); // aCounter++; // if (int.Parse(aRow["IsRevised"].ToString()) == 1) // { // aRevisedExist = true; // aMergedTable.Rows.Add(); // aMergedTable.Rows[aCounter]["Description"] = "Reversed Bill"; // aMergedTable.Rows[aCounter]["Date"] = Convert.ToDateTime(Convert.ToDateTime(aRow["ReviseDate"]).ToShortDateString() + " " + Convert.ToDateTime(aRow["ReviseTime"].ToString()).ToShortTimeString()); // aMergedTable.Rows[aCounter]["#"] = aRow["VoucherNumber"]; // aMergedTable.Rows[aCounter]["Credit"] = "-"; // aMergedTable.Rows[aCounter]["Debit"] = double.Parse(aRow["TotalCost"].ToString()); // aMergedTable.Rows[aCounter]["Balance"] = Math.Round((double.Parse(aMergedTable.Rows[aCounter - 1]["Balance"].ToString()) - double.Parse(aMergedTable.Rows[aCounter]["Debit"].ToString())), 2); // aCounter++; // } //} //aMergedTable.DefaultView.Sort = "Date ASC"; //aMergedTable = aMergedTable.DefaultView.ToTable(); #endregion OLDCODE foreach (DataRow aRow in ConcatTable.Rows) { aMergedTable.Rows.Add(); aMergedTable.Rows[aCounter]["Description"] = aRow["Description"]; aMergedTable.Rows[aCounter]["Date"] = aRow["Date"]; aMergedTable.Rows[aCounter]["#"] = aRow["#"]; if (aRow["Debit"].ToString() != "-") { aMergedTable.Rows[aCounter]["Credit"] = "-"; aMergedTable.Rows[aCounter]["Debit"] = aRow["Debit"]; FinalAmount -= double.Parse(aRow["Debit"].ToString()); } else { aMergedTable.Rows[aCounter]["Credit"] = aRow["Credit"]; aMergedTable.Rows[aCounter]["Debit"] = "-"; FinalAmount += double.Parse(aRow["Credit"].ToString()); } aMergedTable.Rows[aCounter]["Balance"] = Math.Round(FinalAmount, 3); aCounter++; } aMergedTable.Rows.Add(); aMergedTable.Rows[aCounter]["Description"] = "Total"; aMergedTable.Rows[aCounter]["Balance"] = FinalAmount; if (aMergedTable.Rows.Count > 1) { aDTlist.Add(aMergedTable); if (TableBorder) { aMergedTable.Columns[0].ColumnName = "[Border=true1]<th style=\"width:18%\";>" + aMergedTable.Columns[0].ColumnName; } else { aMergedTable.Columns[0].ColumnName = "<th style=\"width:18%\";>" + aMergedTable.Columns[0].ColumnName; } aMergedTable.Columns[1].ColumnName = "<th style=\"width:27%\";>" + aMergedTable.Columns[1].ColumnName; aMergedTable.Columns[2].ColumnName = "<th style=\"width:15%\";>" + aMergedTable.Columns[2].ColumnName; List <string> aHeaderList = ReportsHelper.ImportReportHeader(0, 1); List <string> aFooterList = ReportsHelper.ImportReportHeader(1, 1); double prevrow; double row; double data; if (aRevisedExist) { for (int i = 1; i < aMergedTable.Rows.Count; i++) { if (double.TryParse(aMergedTable.Rows[i - 1]["Balance"].ToString(), out prevrow)) { if (double.TryParse(aMergedTable.Rows[i]["Balance"].ToString(), out row)) { //debit if (double.TryParse(aMergedTable.Rows[i]["Debit"].ToString(), out data)) { aMergedTable.Rows[i]["Balance"] = prevrow - data; } //credit else if (double.TryParse(aMergedTable.Rows[i]["Credit"].ToString(), out data)) { aMergedTable.Rows[i]["Balance"] = prevrow + data; } else { //TOTAL FINALLY aMergedTable.Rows[i]["Balance"] = prevrow; } } } } } aHeaderList.Add(SharedVariables.Line_Solid_10px_Black); aHeaderList.Add(Text.ReportsNames.VenStatementOfAcctRepNm); DataRow vendorName = VendorsMgmt.SelectVendorRowByID(VendorID); //DataRow aCustomerRow = CustomerMgmt.SelectCustomerRowByID(VendorID); aHeaderList.Add(" <h2>Phone:<b>" + vendorName["Phone1"].ToString() + "</b> Name: <b>" + vendorName["Name"].ToString() + "</b></h2>"); aHeaderList.Add("<h2> From: " + DateFrom + " To:" + DateTo + "</h2>"); aHeaderList.Add("<h3>Print By: <b>" + SharedFunctions.ReturnLoggedUserName() + "</b> On <b>" + DateTime.Now + "</b></h3>"); aHeaderList.Add(SharedVariables.Line_Solid_10px_Black); if (Preview) { PrintingManager.Instance.PrintTables(aDTlist, aHeaderList, aFooterList, ReportsHelper.TempOutputPath, ReportsHelper.TempOutputPath, PrintToThermal, false, false, "", false, "", colored); return("True"); } else if (ExportToPdf) { PrintingManager.Instance.PrintTables(aDTlist, aHeaderList, aFooterList, ReportsHelper.TempOutputPath, ReportsHelper.TempOutputPath, PrintToThermal, false, true, ExportPath, false, "", colored); return("True"); } else if (ExportToExcel) { PrintingManager.Instance.PrintTables(aDTlist, aHeaderList, aFooterList, ReportsHelper.TempOutputPath, ReportsHelper.TempOutputPath, PrintToThermal, false, false, "", true, ExportPath, colored); return("True"); } else if (PrintToThermal) { PrintingManager.Instance.PrintTables(aDTlist, aHeaderList, aFooterList, ReportsHelper.TempOutputPath, ReportsHelper.TempOutputPath, true, true); return("True"); } else { return("True"); } } else { return("No-Data"); } }
//Revenues Comparison public string RevenuesComparison(int ByDDMMYYYY, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "") { try { string ReportName = "Revenues Comparison Report"; DataTable aTable = ReportsMgmt.RevenuesComparions(ByDDMMYYYY, DateFrom, DateTo); if (aTable.Rows.Count > 0) { xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlApp.Visible = false; xlApp.SheetsInNewWorkbook = 1; xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue int RowCnt = 1; xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Name = ReportName; List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1); List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1); for (int i = 0; i < aHeader.Count; i++) { xlWorkSheet.Cells[RowCnt, 2] = aHeader[i]; RowCnt++; } xlWorkSheet.Cells[RowCnt++, 1] = ReportName; xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom; xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo; RowCnt++; xlWorkSheet.Cells[RowCnt, 1] = "Date"; xlWorkSheet.Cells[RowCnt, 2] = "Total Cost JOD"; xlWorkSheet.Cells[RowCnt, 3] = "Total Sales JOD"; xlWorkSheet.Cells[RowCnt, 4] = "Gross Profit JOD"; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true; //xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; RowCnt++; int DataStart = RowCnt; foreach (DataRow aRow in aTable.Rows) { if (ByDDMMYYYY == 0) //BY DAY { xlWorkSheet.Cells[RowCnt, 1] = aRow["Day"].ToString() + '\\' + aRow["Month"].ToString() + "\\" + aRow["Year"].ToString(); } else if (ByDDMMYYYY == 1)//BY MONTH { xlWorkSheet.Cells[RowCnt, 1] = aRow["Month"].ToString() + '\\' + aRow["Year"].ToString(); } else //BY YEAR { xlWorkSheet.Cells[RowCnt, 1] = aRow["Year"].ToString(); } xlWorkSheet.Cells[RowCnt, 2] = Math.Round(double.Parse(aRow["TotalCost"].ToString()), 2); xlWorkSheet.Cells[RowCnt, 3] = Math.Round(double.Parse(aRow["TotalPrice"].ToString()), 2); xlWorkSheet.Cells[RowCnt, 4] = Math.Round(double.Parse(aRow["TotalProfit"].ToString()), 2); RowCnt++; } xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()); oRng.EntireColumn.AutoFit(); Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); xlWorkSheet.DisplayRightToLeft = false; int size = aTable.Rows.Count * 100; if (size > 600) { size = 600; } if (size < 300) { size = 300; } Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300); Excel.Chart chartPage = myChart.Chart; Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Excel.Series series1 = seriesCollection.NewSeries(); Excel.Series series2 = seriesCollection.NewSeries(); Excel.Series series3 = seriesCollection.NewSeries(); RowCnt--; //because we started from 1 suppose to be 0 series1.Name = "Total Cost"; series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()]; series2.Name = "Total Sales"; series2.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series2.Values = xlWorkSheet.Range["C" + DataStart.ToString(), "C" + RowCnt.ToString()]; series3.Name = "Gross Profit"; series3.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series3.Values = xlWorkSheet.Range["D" + DataStart.ToString(), "D" + RowCnt.ToString()]; chartPage.ChartType = Excel.XlChartType.xlLineMarkers; Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis; series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); series2.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); series3.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml; xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format); if (ExportToExcel) { format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7; xlWorkSheet.SaveAs(ExportPath, format); } xlApp.UserControl = false; //Once done close and quit Excel return("TRUE"); } else { return("EMPTY"); } } catch (Exception ex) { MessageBox.Show("{EXCEPTION in Revenues Comparison}" + ex.Message + ex.ToString()); return("ERROR " + ex.Message); } finally { //Once done close and quit Excel xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } }
public static string TaxReport(string FilterDateFrom, string FilterDateTo, bool TableBorder = false, bool Preview = true, bool PrintToThermal = false, bool ExportToPdf = false, bool ExportToExcel = false, string ExportPath = "", bool colored = false) { // string RCV = "<b>recieved</b>"; // string REV = "<b>reversed</b>"; List <string> aStringList = ReportsHelper.ImportReportHeader(0, 1); List <string> aFooterList = ReportsHelper.ImportReportHeader(1, 1); aStringList.Add(SharedVariables.Line_Solid_10px_Black); aStringList.Add("تقرير الضريبة "); aStringList.Add("<h2> Date From: " + FilterDateFrom + "</h2>"); aStringList.Add("<h2> Date To : " + FilterDateTo + "</h2>"); aStringList.Add("<h2>Printed on:" + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString() + "</h2>"); aStringList.Add("<h2>Printed By: <b>" + SharedFunctions.ReturnLoggedUserName() + "</b></h2>"); aStringList.Add(SharedVariables.Line_Solid_10px_Black); //end headers and foters List <DataTable> aDTList = new List <DataTable>(); DataTable TaxLevelsDT = ItemTaxLevelMgmt.SelectAll(); if (TaxLevelsDT == null) { return("ERROR"); } foreach (DataRow aRow in TaxLevelsDT.Rows) { DataTable TitleTable = new DataTable(); TitleTable.Columns.Add("TaxLevel [ " + aRow["Percentage"].ToString() + "](" + aRow["Description"].ToString() + " )"); aDTList.Add(TitleTable); DataTable aTaxTable = new DataTable(); if (TableBorder) { aTaxTable.Columns.Add("[border=true1]Description"); } else { aTaxTable.Columns.Add("[border=false]Description"); } aTaxTable.Columns.Add("Total JOD"); aTaxTable.Columns.Add("Tax JOD"); double TotalSold = 0; double TotalCost = 0; double TotalCustReturnsSold = 0; double TotalCustReturnsCost = 0; double TotalPurchase = 0; double TotalVendorReturnCost = 0; #region Sales DataRow aSaleRow = ReportsMgmt.SelectSalesTax(int.Parse(aRow["ID"].ToString()), FilterDateFrom, FilterDateTo); if (aSaleRow != null) { if (double.TryParse(aSaleRow["TotalSold"].ToString(), out TotalSold)) { TotalSold = Math.Round(TotalSold, 3); } if (double.TryParse(aSaleRow["TotalCost"].ToString(), out TotalCost)) { TotalCost = Math.Round(TotalCost, 3); } } int RowCnt = 0; aTaxTable.Rows.Add(); aTaxTable.Rows[RowCnt][0] = "Total Sales"; aTaxTable.Rows[RowCnt][1] = TotalSold.ToString(); double TestParser = 0; if (double.TryParse(aRow["Percentage"].ToString(), out TestParser)) { aTaxTable.Rows[RowCnt][2] = Math.Round(TotalSold * TestParser / 100, 3); } else { aTaxTable.Rows[RowCnt][2] = 0; } RowCnt++; aTaxTable.Rows.Add(); aTaxTable.Rows[RowCnt][0] = "Total Sales COST"; aTaxTable.Rows[RowCnt][1] = TotalCost.ToString(); if (double.TryParse(aRow["Percentage"].ToString(), out TestParser)) { aTaxTable.Rows[RowCnt][2] = Math.Round(TotalCost * TestParser / 100, 3); } else { aTaxTable.Rows[RowCnt][2] = 0; } RowCnt++; #endregion Sales #region CustomersReturns DataRow aCustReturnsRow = ReportsMgmt.SelectCustomersReturnsTax(int.Parse(aRow["ID"].ToString()), FilterDateFrom, FilterDateTo); if (aCustReturnsRow != null) { if (double.TryParse(aCustReturnsRow["TotalSold"].ToString(), out TotalCustReturnsSold)) { TotalCustReturnsSold = Math.Round(TotalCustReturnsSold, 3); } if (double.TryParse(aCustReturnsRow["TotalCost"].ToString(), out TotalCustReturnsCost)) { TotalCustReturnsCost = Math.Round(TotalCustReturnsCost, 3); } } aTaxTable.Rows.Add(); aTaxTable.Rows[RowCnt][0] = "Customers Returns"; aTaxTable.Rows[RowCnt][1] = TotalCustReturnsSold.ToString(); TestParser = 0; if (double.TryParse(aRow["Percentage"].ToString(), out TestParser)) { aTaxTable.Rows[RowCnt][2] = Math.Round(TotalCustReturnsSold * TestParser / 100, 3); } else { aTaxTable.Rows[RowCnt][2] = 0; } RowCnt++; aTaxTable.Rows.Add(); aTaxTable.Rows[RowCnt][0] = "Customers Returns COST"; aTaxTable.Rows[RowCnt][1] = TotalCustReturnsCost.ToString(); if (double.TryParse(aRow["Percentage"].ToString(), out TestParser)) { aTaxTable.Rows[RowCnt][2] = Math.Round(TotalCustReturnsCost * TestParser / 100, 3); } else { aTaxTable.Rows[RowCnt][2] = 0; } RowCnt++; #endregion CustomersReturns #region Purchase DataRow aPurchaseRow = ReportsMgmt.SelectPurchaseTax(int.Parse(aRow["ID"].ToString()), FilterDateFrom, FilterDateTo); if (aPurchaseRow != null) { if (double.TryParse(aPurchaseRow["TotalCost"].ToString(), out TotalPurchase)) { TotalPurchase = Math.Round(TotalPurchase, 3); } } aTaxTable.Rows.Add(); aTaxTable.Rows[RowCnt][0] = "Total Purchase COST"; aTaxTable.Rows[RowCnt][1] = TotalPurchase.ToString(); if (double.TryParse(aRow["Percentage"].ToString(), out TestParser)) { aTaxTable.Rows[RowCnt][2] = Math.Round(TotalPurchase * TestParser / 100, 3); } else { aTaxTable.Rows[RowCnt][2] = 0; } RowCnt++; #endregion Purchase #region VendorReturns DataRow aVendorReturnsRow = ReportsMgmt.SelectVendorsReturnsTax(int.Parse(aRow["ID"].ToString()), FilterDateFrom, FilterDateTo); if (aVendorReturnsRow != null) { if (double.TryParse(aVendorReturnsRow["TotalCost"].ToString(), out TotalVendorReturnCost)) { TotalVendorReturnCost = Math.Round(TotalVendorReturnCost, 3); } } aTaxTable.Rows.Add(); aTaxTable.Rows[RowCnt][0] = "Vendors Returns COST"; aTaxTable.Rows[RowCnt][1] = TotalVendorReturnCost.ToString(); TestParser = 0; if (double.TryParse(aRow["Percentage"].ToString(), out TestParser)) { aTaxTable.Rows[RowCnt][2] = Math.Round(TotalVendorReturnCost * TestParser / 100, 3); } else { aTaxTable.Rows[RowCnt][2] = 0; } RowCnt++; #endregion VendorReturns #region Disposal DataRow aDisposalRow = ReportsMgmt.SelectDisposalTax(int.Parse(aRow["ID"].ToString()), FilterDateFrom, FilterDateTo); if (aDisposalRow != null) { if (double.TryParse(aDisposalRow["TotalCost"].ToString(), out TotalCost)) { TotalCost = Math.Round(TotalCost, 3); } } aTaxTable.Rows.Add(); aTaxTable.Rows[RowCnt][0] = "Total Disposals"; aTaxTable.Rows[RowCnt][1] = TotalCost.ToString(); if (double.TryParse(aRow["Percentage"].ToString(), out TestParser)) { aTaxTable.Rows[RowCnt][2] = Math.Round(TotalCost * TestParser / 100, 3); } else { aTaxTable.Rows[RowCnt][2] = 0; } RowCnt++; #endregion Disposal aDTList.Add(aTaxTable); } if (Preview) { PrintingManager.Instance.PrintTables(aDTList, aStringList, aFooterList, ReportsHelper.TempOutputPath, ReportsHelper.TempOutputPath, PrintToThermal, false, false, "", false, "", colored); return("True"); } else if (ExportToPdf) { PrintingManager.Instance.PrintTables(aDTList, aStringList, aFooterList, ReportsHelper.TempOutputPath, ReportsHelper.TempOutputPath, false, false, true, ExportPath, false, "", colored); return("True"); } else if (ExportToExcel) { PrintingManager.Instance.PrintTables(aDTList, aStringList, aFooterList, ReportsHelper.TempOutputPath, ReportsHelper.TempOutputPath, false, false, false, "", ExportToExcel, ExportPath, colored); return("True"); } else if (PrintToThermal) { PrintingManager.Instance.PrintTables(aDTList, aStringList, aFooterList, ReportsHelper.TempOutputPath, ReportsHelper.TempOutputPath, PrintToThermal, true); return("True"); } else { return("ERROR"); } }