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