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;
}
private void btnGenerateReport_Click(object sender, EventArgs e)
{
Datasets.dsOnSiteMachines.dtMachinesDataTable dtMachines = new Solsage_Process_Management_System.Datasets.dsOnSiteMachines.dtMachinesDataTable();
Datasets.dsOnSiteMachines.dtCustomerDataTable dtCustomer = new Solsage_Process_Management_System.Datasets.dsOnSiteMachines.dtCustomerDataTable();
string sCustomer = "";
string sCustCode = "";
string sCustPhone = "";
string sCustFax = "";
string sCustContact = "";
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
{
Cursor = System.Windows.Forms.Cursors.WaitCursor;
PsqlConnection oConnPms = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr);
oConnPms.Open();
oConn.Open();
string sLastCustomerCode = "";
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 = '" + txtCustomerCode.Text.Trim() + "' or '' = '" + txtCustomerCode.Text.Trim() + "') ";
sSql2 += "and (HistoryLines.ItemCode = '" + txtAssetsCode.Text.Trim() + "' or '' = '" + txtAssetsCode.Text.Trim() + "') ";
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();
}
Solsage_Process_Management_System.Classes.Generate.printAssetOnsite(dtMachines,sCustomer,sCustCode,sCustPhone,sCustFax,sCustContact);
Cursor = System.Windows.Forms.Cursors.Default;
}