public static string getMOSReport(string sCustomer) { Datasets.dsOnSiteMachines.dtMachinesDataTable dtMachines = new Solsage_Process_Management_System.Datasets.dsOnSiteMachines.dtMachinesDataTable(); string sCustCode = ""; string sCustPhone = ""; string sCustFax = ""; string sCustContact = ""; string sFilename = ""; using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { PsqlConnection oConnPms = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr); oConnPms.Open(); oConn.Open(); string sSql2 = "Select HistoryLines.ItemCode hItemCode, HistoryLines.Description hDescription, UnitUsed, HistoryLines.UnitPrice hPriceExcl, "; sSql2 += "HistoryLines.DocumentNumber hDocNum, OrderNumber, UserDefNum01, LinkNum, HistoryLines.CustomerCode hCustCode, "; sSql2 += "CustomerDesc, DeliveryAddresses.Telephone CustTelephone, DeliveryAddresses.Fax CustFax ,DeliveryAddresses.Contact CustContact "; sSql2 += "from HistoryLines "; sSql2 += "Left join HistoryHeader on HistoryHeader.DocumentNumber = HistoryLines.DocumentNumber "; sSql2 += "Left join Inventory on Inventory.ItemCode = HistoryLines.ItemCode "; sSql2 += "Left join CustomerMaster on CustomerMaster.CustomerCode = HistoryLines.CustomerCode "; sSql2 += "left join DeliveryAddresses on DeliveryAddresses.CustomerCode = CustomerMaster.CustomerCode "; sSql2 += "Where (HistoryLines.DocumentType = '102' or HistoryLines.DocumentType = '2') "; sSql2 += "and CustDelivCode = '' "; sSql2 += "and (HistoryLines.CustomerCode = '" + sCustomer + "' or '' = '" + sCustomer + "') "; sSql2 += "order by HistoryLines.CustomerCode, HistoryLines.DocumentNumber "; PsqlDataReader rdRead = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql2, oConn).ExecuteReader(); while (rdRead.Read()) { sCustomer = rdRead["CustomerDesc"].ToString(); sCustCode = rdRead["hCustCode"].ToString(); sCustPhone = rdRead["CustTelephone"].ToString(); sCustFax = rdRead["CustFax"].ToString(); sCustContact = rdRead["CustContact"].ToString(); //////////////////////////////// //Get ItemInformation from SOLHL //////////////////////////////// if (rdRead["hItemCode"].ToString().Trim() != "'") { string sSqlPms = "Select DeliveryDate, Status, Multiplier from SOLHL "; sSqlPms += "where ItemCode = '" + rdRead["hItemCode"].ToString().Trim() + "' "; sSqlPms += "and LinkNum = " + rdRead["LinkNum"].ToString() + " and "; sSqlPms += "Header = '" + rdRead["hDocNum"].ToString().Trim() + "'"; //get sitename from solhh string sSiteName = ""; string sSql = "Select SiteName From SOLHH where DocNumber = '" + rdRead["hDocNum"].ToString().Trim() + "'"; PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnPms).ExecuteReader(); while (rdReader.Read()) { if (rdReader["SiteName"].ToString().Trim() != "") { sSiteName = rdReader["SiteName"].ToString(); } else { sSiteName = "Main"; } } PsqlDataReader rdReadPms = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSqlPms, oConnPms).ExecuteReader(); while (rdReadPms.Read()) { if (rdReadPms["Status"].ToString() == "0" && rdRead["UserDefNum01"].ToString() == "1") { DataRow drRow = dtMachines.NewRow(); drRow["ItemCode"] = rdRead["hItemCode"].ToString().Substring(0,4); try { drRow["DeliveryDate"] = rdReadPms["DeliveryDate"].ToString().Substring(0, 11); } catch { } drRow["CustRef"] = rdRead["OrderNumber"].ToString(); drRow["DelNoteNumber"] = rdRead["hDocNum"].ToString(); drRow["ItemDescription"] = rdRead["hDescription"].ToString(); drRow["Qty"] = Convert.ToDouble(rdReadPms["Multiplier"].ToString()); drRow["Unit"] = rdRead["UnitUsed"].ToString().Substring(0,1); drRow["PriceExcl"] = Convert.ToDecimal(rdRead["hPriceExcl"].ToString()).ToString("N2"); drRow["CustomerCode"] = rdRead["hCustCode"].ToString(); drRow["SiteName"] = sSiteName; drRow["CustName"] = sCustomer; drRow["CustPhone"] = sCustPhone; drRow["CustFax"] = sCustFax; drRow["CustContact"] = sCustContact; dtMachines.Rows.Add(drRow); } } } } oConn.Dispose(); oConnPms.Dispose(); } DataSet dsOnSiteMachines = new DataSet(); dsOnSiteMachines.Tables.Add(dtMachines); using (Solsage_Process_Management_System.Documents.MachinesOnSite reportMachinesOnSite = new Solsage_Process_Management_System.Documents.MachinesOnSite()) { using (Solsage_Process_Management_System.Documents.PrintInvoice frmPrint = new Solsage_Process_Management_System.Documents.PrintInvoice()) { reportMachinesOnSite.SetDataSource(dsOnSiteMachines.Tables["dtMachines"]); foreach (CrystalDecisions.CrystalReports.Engine.FormulaFieldDefinition forReport in reportMachinesOnSite.DataDefinition.FormulaFields) { switch (forReport.FormulaName) { case "{@sGlobCompanyName}": forReport.Text = "'" + Global.sCompanyName.Trim() + "'"; break; case "{@sGlobCompanyRegName}": forReport.Text = "'" + Global.sRegName.Trim() + "'"; break; case "{@sGlobTel}": forReport.Text = "'" + Global.sCompanyTel.Trim() + "'"; break; case "{@sGlobFax}": forReport.Text = "'" + Global.sCompanyFax.Trim() + "'"; break; case "{@sGlobPost1}": forReport.Text = "'" + Global.sCompanyPostAd1.Trim() + "'"; break; case "{@sGlobPost2}": forReport.Text = "'" + Global.sCompanyPostAd2.Trim() + "'"; break; case "{@sGlobPost3}": forReport.Text = "'" + Global.sCompanyPostAd3.Trim() + "'"; break; case "{@sGlobAdd1}": forReport.Text = "'" + Global.sCompanyAd1.Trim() + "'"; break; case "{@sGlobAdd2}": forReport.Text = "'" + Global.sCompanyAd2.Trim() + "'"; break; case "{@sGlobAdd3}": forReport.Text = "'" + Global.sCompanyAd3.Trim() + "'"; break; case "{@CompanyCell}": forReport.Text = "'" + Global.sInvoiceContactNumber + "'"; break; case "{@sCustomer}": forReport.Text = "'" + sCustomer + "'"; break; case "{@sCustCode}": forReport.Text = "'" + sCustCode + "'"; break; case "{@sCustPhone}": forReport.Text = "'" + sCustPhone + "'"; break; case "{@sCustFax}": forReport.Text = "'" + sCustFax + "'"; break; case "{@sCustContact}": forReport.Text = "'" + sCustContact + "'"; break; } } //frmPrint.crystalReportViewer1.ReportSource = reportMachinesOnSite; // frmPrint.printThisDocument(); //frmPrint.ShowDialog(); sFilename = sCustomer.Trim().Replace("/","").Replace("\\","") + ".pdf"; reportMachinesOnSite.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Application.StartupPath + "\\Temp\\" + sFilename); reportMachinesOnSite.Dispose(); } } return sFilename; }
public static void printInvoice(string sDocumentNumber, bool bFirstPrint, bool sendToDepot,string sCurrentSalesman, PrintDialog pdPrintDetails) { //get printquantity decimal iFPQuantity = Convert.ToDecimal(ConfigurationSettings.AppSettings.Get("InvoiceFirstPrintCopies")); decimal iDPQuantity = Convert.ToDecimal(ConfigurationSettings.AppSettings.Get("InvoiceDuplicatePrintCopies")); //using (PsqlConnection oConn = new PsqlConnection(Connect.sConnStr)) //{ // oConn.Open(); // string sSql = "SELECT InvoiceFirst, InvoiceDuplicate FROM SOLCS"; // PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader(); // while (rdReader.Read()) // { // iFPQuantity = Convert.ToDecimal(rdReader["InvoiceFirst"].ToString()); // iDPQuantity = Convert.ToInt16(rdReader["InvoiceDuplicate"].ToString()); // } //} ReportClass reportDelivery = getInvoiceReport(sDocumentNumber,"","","","","","NormalPrint",sCurrentSalesman); if (reportDelivery != null) { try { if (sendToDepot) { //Send file to workstation if (!Directory.Exists(Application.StartupPath + "\\Temp")) { Directory.CreateDirectory(Application.StartupPath + "\\Temp"); } string sFilename = sDocumentNumber + ".rpt"; if (File.Exists(Application.StartupPath + "\\Temp\\" + sFilename)) { File.Delete(Application.StartupPath + "\\Temp\\" + sFilename); } // reportDelivery.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Application.StartupPath + "\\Temp\\" + sFilename); reportDelivery.SaveAs(Application.StartupPath + "\\Temp\\" + sFilename, true); sDepotFilename = Application.StartupPath + "\\Temp\\" + sFilename; return; } if (Global.sInvoiceTemplate == "Kings Hire") { reportDelivery.PrintOptions.PrinterName = pdPrintDetails.PrinterSettings.PrinterName; reportDelivery.PrintToPrinter(Convert.ToInt16(pdPrintDetails.PrinterSettings.Copies), false, 0, 0); } else { if (bFirstPrint) { reportDelivery.PrintOptions.PrinterName = Global.sDefaultDocPrinter; reportDelivery.PrintToPrinter(Convert.ToInt16(iFPQuantity), false, 0, 0); } else if (!bFirstPrint) { reportDelivery.PrintOptions.PrinterName = Global.sDefaultDocPrinter; reportDelivery.PrintToPrinter(Convert.ToInt16(iDPQuantity), false, 0, 0); } } } catch { using (Solsage_Process_Management_System.Documents.PrintInvoice frmPrint = new Solsage_Process_Management_System.Documents.PrintInvoice()) { frmPrint.crystalReportViewer1.ReportSource = reportDelivery; frmPrint.printThisDocument(); } } } }