private void PrintPreview(bool Privew) { ObjLt = new LabTest(); if (cbxMedicines.Text.Length > 0) { ObjLt.LabTestId = ((LabTest)(cbxMedicines.SelectedItem)).LabTestId; } ds = new LabTestBLL().GetIssuedMedPur(rbAll.Checked, rbPurchase.Checked, rbRetaiReport.Checked, ObjLt, dtpfromDate.Value.Date, dtpTodate.Value.Date); string SummaryType = ""; string From = ""; string TO = ""; if (rbPurchase.Checked == true && rbAll.Checked == false) { SummaryType = "Purchase Price"; From = dtpfromDate.Value.Date.ToString(); TO = dtpTodate.Value.Date.ToString(); } else if (rbRetaiReport.Checked == true && rbAll.Checked == false) { SummaryType = "Retail Price"; From = dtpfromDate.Value.Date.ToString(); TO = dtpTodate.Value.Date.ToString(); } else if (rbAll.Checked == true && rbRetaiReport.Checked == false && rbPurchase.Checked == false) { SummaryType = "Purchase&Retail"; From = "Start"; TO = "Till Now"; } //else if (rbAll.Checked == true && rbPurchase.Checked == true) // SummaryType = "Purchase Price"; //else if (rbAll.Checked == true && rbRetaiReport.Checked == true) // SummaryType = "Retail Price"; crp.SetDataSource((DataTable)ds); crp.SetParameterValue("SummaryType", SummaryType); crp.SetParameterValue("From", From); crp.SetParameterValue("TO", TO); FrmReportViewer frmViewer = new FrmReportViewer(); frmViewer.crystalReportViewer1.ReportSource = crp; if (Privew) { frmViewer.ShowDialog(); } else { frmViewer.crystalReportViewer1.RefreshReport(); frmViewer.crystalReportViewer1.PrintReport(); } }
public DSIssuedMedPur.dtIssuedMedPurDataTable GetIssuedMedPur(bool All, bool Purchase, bool Retail, LabTest MedID, DateTime From, DateTime To) { DSIssuedMedPur.dtIssuedMedPurDataTable dt = new DSIssuedMedPur.dtIssuedMedPurDataTable(); try { string Pur = "select ilt.TokenDate as IssueDate, ilt.LabTestID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as PurchasePrice,'' as BranchName,ilt.DosePerDay*ilt.Days as Quantity from InjectionLabTest ilt,LabTest lt where ilt.LabTestID=lt.ID and lt.ID=" + MedID.LabTestId + " and ilt.TokenDate>=#" + From + "# and ilt.TokenDate<=#" + To + "# union all" + " select mi.TokenDate as IsssueDate,mi.ID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as PurchasePrice,'' as BranchName,mi.DosePerDay as Quantity from MedicineIssued mi,LabTest lt where mi.ID=lt.ID and mi.MedicinesIssued=True and" + " lt.ID=" + MedID.LabTestId + " and mi.TokenDate>=#" + From + "# and mi.TokenDate<=#" + To + "# union all " + " select im.IssueDate as IssueDate,im.MedicineID as MedicineID,im.MedicineName,lt.PurchasePrice as PurchasePrice,im.BranchName as BranchName,im.Qty as Quantity from IssuedMedicine im,LabTest lt where im.MedicineID=lt.ID and lt.ID=" + MedID.LabTestId + "and im.Issuedate>=#" + From + "# and im.Issuedate<=#" + To + "#"; //string PurAll = "select ilt.TokenDate as IssueDate, ilt.LabTestID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as Price,ilt.DosePerDay*ilt.Days as Quantity from InjectionLabTest ilt,LabTest lt where ilt.LabTestID=lt.ID "+ // " union all" + // " select mi.TokenDate as IsssueDate,mi.ID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as Price,mi.DosePerDay as Quantity from MedicineIssued mi,LabTest lt where mi.ID=lt.ID and mi.MedicinesIssued=True" + // " union all " + // " select im.IssueDate as IssueDate,im.MedicineID as MedicineID,im.MedicineName,lt.PurchasePrice as Price,im.Qty as Quantity from IssuedMedicine im,LabTest lt where im.MedicineID=lt.ID"; string Ret = "select ilt.TokenDate as IssueDate, ilt.LabTestID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.RetailPrice as RetailPrice,'' as BranchName,ilt.DosePerDay*ilt.Days as Quantity from InjectionLabTest ilt,LabTest lt where ilt.LabTestID=lt.ID and lt.ID=" + MedID.LabTestId + " and ilt.TokenDate>=#" + From + "# and ilt.TokenDate<=#" + To + "# union all" + " select mi.TokenDate as IssueDate, mi.ID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.RetailPrice as RetailPrice,''as BranchName,mi.DosePerDay as Quantity from MedicineIssued mi,LabTest lt where mi.ID=lt.ID and mi.MedicinesIssued=True and" + " lt.ID=" + MedID.LabTestId + " and mi.TokenDate>=#" + From + "# and mi.TokenDate<=#" + To + "# union all" + " select im.IssueDate as IssueDate,im.MedicineID as MedicineID,im.MedicineName,lt.RetailPrice as RetailPrice,im.Branchname as BranchName,im.Qty as Quantity from IssuedMedicine im,LabTest lt where im.MedicineID=lt.ID and lt.ID=" + MedID.LabTestId + " and im.Issuedate>=#" + From + "# and im.Issuedate<=#" + To + "#"; // string RetAll = "select ilt.TokenDate as IssueDate, ilt.LabTestID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.RetailPrice as Price,ilt.DosePerDay*ilt.Days as Quantity from InjectionLabTest ilt,LabTest lt where ilt.LabTestID=lt.ID"+ // " union all" + // " select mi.TokenDate as IssueDate, mi.ID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.RetailPrice as Price,mi.DosePerDay as Quantity from MedicineIssued mi,LabTest lt where mi.ID=lt.ID and mi.MedicinesIssued=True" + // " union all" + //" select im.IssueDate as IssueDate,im.MedicineID as MedicineID,im.MedicineName,lt.RetailPrice as Price,im.Qty as Quantity from IssuedMedicine im,LabTest lt where im.MedicineID=lt.ID"; string all = "select ilt.TokenDate as IssueDate,ilt.LabTestID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as PurchasePrice,lt.RetailPrice as RetailPrice,'' as Branchname,ilt.DosePerDay*ilt.Days as Quantity from InjectionLabTest ilt,LabTest lt where ilt.LabTestID=lt.ID " + " and ilt.TokenDate>=#" + From + "# and ilt.TokenDate<=#" + To + "# union all" + " select mi.TokenDate as IssueDate, mi.ID as MedicineID,lt.[Test (A-Z)] as MedicineName,lt.PurchasePrice as PurchasePrice,lt.RetailPrice as RetailPrice,'' as Branchname,mi.DosePerDay as Quantity from MedicineIssued mi,LabTest lt where mi.ID=lt.ID and mi.MedicinesIssued=True" + " and mi.Tokendate>=#" + From + "# and mi.TokenDate<=#" + To + "# union all" + " select im.IssueDate as IssueDate,im.MedicineID as MedicineID,im.MedicineName,lt.PurchasePrice as PurchasePrice,lt.RetailPrice as RetailPrice,im.BranchName as Branchname,im.Qty as Quantity from IssuedMedicine im,LabTest lt where im.MedicineID=lt.ID" + " and im.IssueDate>=#" + From + "# and im.IssueDate<=#" + To + "#"; con = new OleDbConnection(); readconfile = new ReadConfigFile(); con.ConnectionString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile); con.Open(); if (con.State == ConnectionState.Open) { if (Purchase == true) { da = new OleDbDataAdapter(Pur, con); } else if (Retail == true) { da = new OleDbDataAdapter(Ret, con); } //else if(Purchase==true&&All==true) // da = new OleDbDataAdapter(PurAll, con); //else if (Retail== true && All == true) // da = new OleDbDataAdapter(RetAll, con); // da = new OleDbDataAdapter(all, con); else if (All == true) { da = new OleDbDataAdapter(all, con); } da.Fill(dt); } } catch (Exception ex) { throw ex; } finally { con.Close(); } return(dt); }