private void frmSalesInvoice_Load(object sender, EventArgs e)
{
string query = "";
string strQueryHeader_Footer = "";
string PaymentQuery = "";
if (IsArabicEnabled())
{
query = "SELECT p1.ProductName," + clsUtility.DBName + ".dbo.fun_ToArabicNum(s1.QTY) as QTY," +
"" + clsUtility.DBName + ".dbo.fun_ToArabicNum(s1.Rate) as Rate," +
"" + clsUtility.DBName + ".dbo.fun_ToArabicNum((s1.Qty*s1.Rate)) AS Total,ps.barcodeNo as BarNumber FROM " + clsUtility.DBName + ".[dbo].[SalesDetails] s1 JOIN " + clsUtility.DBName + ".dbo.ProductMaster p1 " +
" ON s1.ProductID = p1.ProductID " +
" JOIN " + clsUtility.DBName + ".dbo.ProductStockColorSizeMaster ps" +
" ON s1.SubProductID=ps.SubProductID AND s1.ProductID = ps.ProductID AND ps.colorID=s1.ColorID AND ps.SizeID=s1.SizeID " +
" AND ps.StoreID=(select ShopeID from SalesInvoiceDetails where Id=s1.InvoiceID) WHERE s1.InvoiceID = " + InvoiceID;
PaymentQuery = " select PaymentType," + clsUtility.DBName + ".dbo.fun_ToArabicNum(Amount) as Amount from " + clsUtility.DBName + ".dbo.[tblSalesPayment] where SalesInvoiceID=" + InvoiceID;
}
else
{
query = "SELECT p1.ProductName,s1.QTY," +
"s1.Rate," +
"(s1.Qty*s1.Rate) AS Total,ps.barcodeNo as BarNumber FROM " + clsUtility.DBName + ".[dbo].[SalesDetails] s1 JOIN " + clsUtility.DBName + ".dbo.ProductMaster p1 " +
" ON s1.ProductID = p1.ProductID " +
" JOIN " + clsUtility.DBName + ".dbo.ProductStockColorSizeMaster ps" +
" ON s1.SubProductID=ps.SubProductID AND s1.ProductID = ps.ProductID AND s1.ColorID=ps.ColorID AND s1.SizeID=ps.SizeID " +
" AND ps.StoreID=(select ShopeID from SalesInvoiceDetails where Id=s1.InvoiceID) WHERE s1.InvoiceID = " + InvoiceID;
PaymentQuery = "select PaymentTYpe,Amount from " + clsUtility.DBName + ".dbo.[tblSalesPayment] WITH(NOLOCK) where SalesInvoiceID=" + InvoiceID;
}
DataTable dtSalesDetails = ObjCon.ExecuteSelectStatement(query);
DataTable dtPaymentDetails = ObjCon.ExecuteSelectStatement(PaymentQuery);
if (IsArabicEnabled())
{
strQueryHeader_Footer = "SELECT s1.InvoiceNumber,s1.InvoiceDate, c1.Name AS CustName,e1.Name AS empName,st1.StoreName AS StoreName," + clsUtility.DBName + ".dbo.fun_ToArabicNum(s1.SubTotal) as SubTotal," + clsUtility.DBName + ".dbo.fun_ToArabicNum(s1.Discount) as Discount," + clsUtility.DBName + ".dbo.fun_ToArabicNum(s1.Tax) as Tax," + clsUtility.DBName + ".dbo.fun_ToArabicNum(s1.GrandTotal) as GrandTotal,s1.PaymentMode,s1.PaymentAutoID,c1.PhoneNo AS CustomerMobile,s1.CashTendered, s1.Change FROM " + clsUtility.DBName + ".dbo.SalesInvoiceDetails s1 left JOIN " +
" " + clsUtility.DBName + ".dbo.EmployeeDetails e1 ON s1.SalesMan = e1.EmpID left JOIN" +
" " + clsUtility.DBName + ".[dbo].[CustomerMaster] c1 ON s1.CustomerID = c1.CustomerID left join" +
" " + clsUtility.DBName + ".dbo.StoreMaster st1 ON st1.StoreID = s1.ShopeID WHERE s1.Id=" + InvoiceID;
}
else
{
strQueryHeader_Footer = "SELECT s1.InvoiceNumber,s1.InvoiceDate, c1.Name AS CustName,e1.Name AS empName,st1.StoreName AS StoreName,s1.SubTotal,s1.Discount,s1.Tax,s1.GrandTotal,s1.PaymentMode,s1.PaymentAutoID,c1.PhoneNo AS CustomerMobile,s1.CashTendered,s1.Change FROM " + clsUtility.DBName + ".dbo.SalesInvoiceDetails s1 left JOIN " +
" " + clsUtility.DBName + ".dbo.EmployeeDetails e1 ON s1.SalesMan = e1.EmpID left JOIN" +
" " + clsUtility.DBName + ".[dbo].[CustomerMaster] c1 ON s1.CustomerID = c1.CustomerID left join" +
" " + clsUtility.DBName + ".dbo.StoreMaster st1 ON st1.StoreID = s1.ShopeID WHERE s1.Id=" + InvoiceID;
}
DataTable dtSalesHeader_Footer = ObjCon.ExecuteSelectStatement(strQueryHeader_Footer);
Bitmap bmpBarCode = Barcode.clsBarCodeUtility.GenerateBarCode(dtSalesHeader_Footer.Rows[0]["InvoiceNumber"].ToString());
string imgPath = "";
if (bmpBarCode != null)
{
imgPath = Application.StartupPath + "//BarCodeImg//" + dtSalesHeader_Footer.Rows[0]["InvoiceNumber"].ToString() + ".jpeg";
if (!File.Exists(imgPath))
{
bmpBarCode.Save(imgPath);
}
}
reportViewer1.LocalReport.DataSources.Clear();
ReportDataSource rds = new ReportDataSource("ds_SalesDetails", dtSalesDetails);
ReportDataSource rds2 = new ReportDataSource("ds_InvoiceHeader", dtSalesHeader_Footer);
ReportDataSource rds3 = new ReportDataSource("ds_Payment", dtPaymentDetails);
string strcomName = "";
string strAddress = "";
DataTable dtCompinfo = ObjCon.ExecuteSelectStatement("SELECT CompanyName,[Address] FROM " + clsUtility.DBName + ".dbo.CompanyMaster WITH(NOLOCK)");
if (dtCompinfo != null && dtCompinfo.Rows.Count > 0)
{
strcomName = dtCompinfo.Rows[0]["CompanyName"].ToString();
strAddress = dtCompinfo.Rows[0]["Address"].ToString();
}
else
{
strcomName = "Default Store/Shop Name";
strAddress = "Default Address Line. Road No, Block No, Pin Code and State.";
}
DataTable dtFooterNote = ObjCon.ExecuteSelectStatement("SELECT InvoiceFooterNote FROM " + clsUtility.DBName + ".dbo.DefaultStoreSetting WITH(NOLOCK)");
string footerNoteDefault = "Once payment made will not be refunded under any circumstances. ";
if (dtFooterNote != null && dtFooterNote.Rows.Count > 0)
{
if (dtFooterNote.Rows[0]["InvoiceFooterNote"] != DBNull.Value)
{
footerNoteDefault = dtFooterNote.Rows[0]["InvoiceFooterNote"].ToString();
}
}
else
{
strcomName = "Default Store/Shop Name";
strAddress = "Default Address Line. Road No, Block No, Pin Code and State.";
}
string NetAmt = "NA";
try
{
DataTable dtNetAmount = ObjCon.ExecuteSelectStatement("select SUM(Amount) from " + clsUtility.DBName + ".dbo.[tblSalesPayment] WITH(NOLOCK) where SalesInvoiceID=" + InvoiceID);
if (dtNetAmount != null && dtNetAmount.Rows.Count > 0)
{
if (Convert.ToDecimal(dtNetAmount.Rows[0][0].ToString()) == 0)
{
NetAmt = "Zero";
}
else
{
NetAmt = new Sales.NumberToEnglish().changeCurrencyToWords(dtNetAmount.Rows[0][0].ToString());
}
}
}
catch (Exception)
{
}
reportViewer1.LocalReport.EnableExternalImages = true;
// creating the parameter with the extact name as in the report.
ReportParameter param1 = new ReportParameter("CompanyName", strcomName, true);
ReportParameter param2 = new ReportParameter("CompanyAddress", strAddress, true);
ReportParameter param3 = new ReportParameter("Note", footerNoteDefault, true);
ReportParameter param4 = new ReportParameter("NetAmount", NetAmt, true);
ReportParameter param5 = new ReportParameter("BarCodeImage", imgPath, true);
// adding the parameter in the report dynamically
reportViewer1.LocalReport.SetParameters(param1);
reportViewer1.LocalReport.SetParameters(param2);
reportViewer1.LocalReport.SetParameters(param3);
reportViewer1.LocalReport.SetParameters(param4);
reportViewer1.LocalReport.SetParameters(param5);
reportViewer1.LocalReport.DataSources.Add(rds);
reportViewer1.LocalReport.DataSources.Add(rds2);
reportViewer1.LocalReport.DataSources.Add(rds3);
reportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout);
reportViewer1.ZoomMode = ZoomMode.Percent;
reportViewer1.ZoomPercent = 100;
this.reportViewer1.RefreshReport();
if (IsDirectPrint)
{
ReportPrinting reportPrinting = new ReportPrinting();
reportPrinting.Export(this.reportViewer1.LocalReport);
PrinterSettings printerSetting = new PrinterSettings();
// printerSetting.DefaultPageSettings.PaperSize = new PaperSize("IMS_Paper", 80, 297);
if (clsBarCodeUtility.GetPrinterName(clsBarCodeUtility.PrinterType.InvoicePrinter).Trim().Length == 0)
{
bool b = clsUtility.ShowQuestionMessage("Printer Not Configured for Invoice. Do you want to print on default printer?", clsUtility.strProjectTitle);
if (b)
{
reportPrinting.Print();
}
}
else
{
printerSetting.PrinterName = clsBarCodeUtility.GetPrinterName(clsBarCodeUtility.PrinterType.InvoicePrinter);
reportPrinting.Print(printerSetting);
}
this.Close();
}
}
private void frmSalesInvoice_Load(object sender, EventArgs e)
{
string query = "SELECT p1.ProductName,s1.QTY,s1.Rate, (s1.Qty*s1.Rate) AS Total,ps.barcodeNo as BarNumber FROM " + clsUtility.DBName + ".[dbo].[SalesDetails] s1 JOIN " + clsUtility.DBName + ".dbo.ProductMaster p1 " +
" ON s1.ProductID = p1.ProductID " +
" JOIN " + clsUtility.DBName + ".dbo.ProductStockMaster ps" +
" ON ps.colorID=s1.ColorID and ps.SizeID=s1.SizeID " +
" WHERE s1.InvoiceID = " + InvoiceID;
DataTable dtSalesDetails = ObjCon.ExecuteSelectStatement(query);
string strQueryHeader_Footer = "SELECT s1.InvoiceNumber,s1.InvoiceDate, c1.Name AS CustName,e1.Name AS empName,st1.StoreName AS StoreName,s1.SubTotal,s1.Discount,s1.Tax,s1.GrandTotal,s1.PaymentMode,s1.PaymentAutoID,c1.PhoneNo AS CustomerMobile FROM " + clsUtility.DBName + ".dbo.SalesInvoiceDetails s1 left JOIN " +
" " + clsUtility.DBName + ".dbo.EmployeeDetails e1 ON s1.SalesMan = e1.EmpID left JOIN" +
" " + clsUtility.DBName + ".[dbo].[CustomerMaster] c1 ON s1.CustomerID = c1.CustomerID left join" +
" " + clsUtility.DBName + ".dbo.StoreMaster st1 ON st1.StoreID = s1.ShopeID WHERE s1.Id=" + InvoiceID;
DataTable dtSalesHeader_Footer = ObjCon.ExecuteSelectStatement(strQueryHeader_Footer);
reportViewer1.LocalReport.DataSources.Clear();
ReportDataSource rds = new ReportDataSource("ds_SalesDetails", dtSalesDetails);
ReportDataSource rds2 = new ReportDataSource("ds_InvoiceHeader", dtSalesHeader_Footer);
string strcomName = "";
string strAddress = "";
DataTable dtCompinfo = ObjCon.ExecuteSelectStatement("SELECT CompanyName,[Address] FROM " + clsUtility.DBName + ".dbo.CompanyMaster WITH(NOLOCK)");
if (dtCompinfo != null && dtCompinfo.Rows.Count > 0)
{
strcomName = dtCompinfo.Rows[0]["CompanyName"].ToString();
strAddress = dtCompinfo.Rows[0]["Address"].ToString();
}
else
{
strcomName = "Default Store/Shop Name";
strAddress = "Default Address Line. Road No, Block No, Pin Code and State.";
}
DataTable dtFooterNote = ObjCon.ExecuteSelectStatement("SELECT InvoiceFooterNote FROM " + clsUtility.DBName + ".dbo.DefaultStoreSetting WITH(NOLOCK)");
string footerNoteDefault = "Once payment made will not be refunded under any circumstances. ";
if (dtFooterNote != null && dtFooterNote.Rows.Count > 0)
{
if (dtFooterNote.Rows[0]["InvoiceFooterNote"] != DBNull.Value)
{
footerNoteDefault = dtFooterNote.Rows[0]["InvoiceFooterNote"].ToString();
}
}
else
{
strcomName = "Default Store/Shop Name";
strAddress = "Default Address Line. Road No, Block No, Pin Code and State.";
}
string NetAmt = new Sales.NumberToEnglish().changeCurrencyToWords(dtSalesHeader_Footer.Rows[0]["GrandTotal"].ToString());
// creating the parameter with the extact name as in the report.
ReportParameter param1 = new ReportParameter("CompanyName", strcomName, true);
ReportParameter param2 = new ReportParameter("CompanyAddress", strAddress, true);
ReportParameter param3 = new ReportParameter("Note", footerNoteDefault, true);
ReportParameter param4 = new ReportParameter("NetAmount", NetAmt, true);
// adding the parameter in the report dynamically
reportViewer1.LocalReport.SetParameters(param1);
reportViewer1.LocalReport.SetParameters(param2);
reportViewer1.LocalReport.SetParameters(param3);
reportViewer1.LocalReport.SetParameters(param4);
reportViewer1.LocalReport.DataSources.Add(rds);
reportViewer1.LocalReport.DataSources.Add(rds2);
this.reportViewer1.RefreshReport();
if (IsDirectPrint)
{
ReportPrinting reportPrinting = new ReportPrinting();
reportPrinting.Export(this.reportViewer1.LocalReport);
reportPrinting.Print();
this.Close();
}
}
