private void GeneratePDF() { ReportDocument rpt = new ReportDocument(); switch (cboView.SelectedItem.Value) { case "0": rpt.Load(Server.MapPath(Constants.ROOT_DIRECTORY + "/Reports/ChartOfAccountsDetails.rpt")); break; case "1": rpt.Load(Server.MapPath(Constants.ROOT_DIRECTORY + "/Reports/ChartOfAccountsSummary.rpt")); break; } ExportOptions exportop = new ExportOptions(); DiskFileDestinationOptions dest = new DiskFileDestinationOptions(); string strPath = Server.MapPath(@"\RetailPlus\temp\"); string strFileName = "chartofacc_" + Session["UserName"].ToString() + "_" + DateTime.Now.ToString("yyyyMMddhhmmssff") + ".pdf"; if (System.IO.File.Exists(strPath + strFileName)) System.IO.File.Delete(strPath + strFileName); dest.DiskFileName = strPath + strFileName; exportop = rpt.ExportOptions; SetDataSource(rpt); exportop.DestinationOptions = dest; exportop.ExportDestinationType = ExportDestinationType.DiskFile; exportop.ExportFormatType = ExportFormatType.PortableDocFormat; rpt.Export(); rpt.Close(); rpt.Dispose(); fraViewer.Attributes.Add("src","/RetailPlus/temp/" + strFileName); }
public void ShowGenericRpt() { try { bool isValid = true; string strReportName = System.Web.HttpContext.Current.Session["ReportName"].ToString(); // Setting ReportName if (string.IsNullOrEmpty(strReportName)) { isValid = false; } if (isValid) { ReportDocument rd = new ReportDocument(); string strRptPath = System.Web.HttpContext.Current.Server.MapPath("~/") + "Report//" + strReportName; rd.Load(strRptPath); rd.VerifyDatabase(); rd.ExportToHttpResponse(ExportFormatType.PortableDocFormat, System.Web.HttpContext.Current.Response, false, "crReport"); // Clear all sessions value Session["ReportName"] = null; } else { Response.Write("<H2>Nothing Found; No Report name found</H2>"); } } catch (Exception ex) { Response.Write(ex.ToString()); } }
public ReportDocument GetVendorListByActivationStatus(string sortedBy, string sortingOrder, string isActive, LumexDBPlayer db) { try { LumexSessionManager.Add("rptName", "Vendor_List"); ReportDocument reportDocument = new ReportDocument(); db.AddParameters("@SortedBy", sortedBy.Trim()); db.AddParameters("@SortingOrder", sortingOrder.Trim()); db.AddParameters("@IsActive", isActive.Trim()); if (isActive == "All") { reportDocument.Load(reportPath + "GET_VENDORS.rpt"); } else { reportDocument.Load(reportPath + "GET_VENDORS_BY_ACTIVATION_STATUS.rpt"); } reportDocument.SetDataSource(db.ExecuteDataTable("REPORT_GET_VENDORS_BY_ACTIVATION_STATUS", true)); return reportDocument; } catch (Exception) { throw; } }
private void CrystalReportViewer1_Loaded(object sender, RoutedEventArgs e) { ReportDocument report = new ReportDocument(); if (m_isurduvisible) { report.Load("../../Reports/SaleReceipt_u.rpt"); } if (!m_isurduvisible) { report.Load("../../Reports/SaleReceipt_e.rpt"); } ArrayList reportdata = new ArrayList(); reportdata.Add(productSold); using(var db = new HCSMLEntities1()) { try { report.SetDataSource(from c in db.saleproducts where c.seqid == productSold.seqid select c); } catch (NotSupportedException ex) {} catch (Exception ex) {} } crystalReportsViewer1.ViewerCore.ReportSource = report; report.Dispose(); }
private void btnGenerar_Click(object sender, EventArgs e) { if (rbgTipoConsulta.SelectedIndex == 0) { ReportDocument rpt = new ReportDocument(); rpt.Load(Application.StartupPath+ "//Taller//crptOrdenesTrabajosG.rpt"); rptOrdenesTrabajos report = new rptOrdenesTrabajos(rpt); report.ShowDialog(); } if (rbgTipoConsulta.SelectedIndex == 1) { ReportDocument rpt = new ReportDocument(); rpt.Load(Application.StartupPath + "//Taller//crptOrdenesTrabajosC.rpt"); rptOrdenesTrabajos report = new rptOrdenesTrabajos(rpt); report.ShowDialog(); } if (rbgTipoConsulta.SelectedIndex == 2) { ReportDocument rpt = new ReportDocument(); rpt.Load(Application.StartupPath + "//Taller//crptOrdenesTrabajosV.rpt"); rpt.Load(@"C:\Users\Mario\Desktop\Proyecto\taller\SistemasIntegrados\datos\Taller\crptOrdenesTrabajosV.rpt"); rptOrdenesTrabajos report = new rptOrdenesTrabajos(rpt); report.ShowDialog(); } }
private void btnSearch_Click(object sender, EventArgs e) { try { int DoctorID; //string DoctorName; string StartDate; string EndDate; DoctorID = Convert.ToInt32(cbDoctor.SelectedValue.ToString()); // DoctorName=Convert.ToString(cbDoctor.SelectedText.ToString()); StartDate = Convert.ToString(dtpStartDate.Text); EndDate = Convert.ToString(dtpEndDate.Text); ReportDocument reportDocument = new ReportDocument(); ParameterField paramField = new ParameterField(); ParameterFields paramFields = new ParameterFields(); ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue(); string ReportPath = ConfigurationManager.AppSettings["ReportPath"]; paramField.Name = "@DoctorID"; //paramField.Name = "@DoctorName"; paramField.Name = "@StartDate"; paramField.Name = "@EndDate"; paramDiscreteValue.Value = 1; if (chkdailyreport.Checked == true) { reportDocument.Load(ReportPath + "Report\\DoctorPay_CrystalReportNew1.rpt"); } else { reportDocument.Load(ReportPath + "Report\\DoctorPayment_ReportNew.rpt"); } ConnectionInfo connectionInfo = new ConnectionInfo(); connectionInfo.DatabaseName = "DB_MedicalShop_02Sept20159PM"; //connectionInfo.UserID = "wms"; //connectionInfo.Password = "******"; connectionInfo.IntegratedSecurity = true; SetDBLogonForReport(connectionInfo, reportDocument); reportDocument.SetParameterValue("@DoctorID", DoctorID); //reportDocument.SetParameterValue("@DoctorName", DoctorName); reportDocument.SetParameterValue("@StartDate", StartDate); reportDocument.SetParameterValue("@EndDate", EndDate); SupplierCrystalRpt.ReportSource = reportDocument; SupplierCrystalRpt.ToolPanelView = CrystalDecisions.Windows.Forms.ToolPanelViewType.None; } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } }
private ReportDocument getReportDocument() { ReportDocument rpt = new ReportDocument(); if (!chkGroupItems.Checked) rpt.Load(Server.MapPath(Constants.ROOT_DIRECTORY + "/Reports/_MostSalableItemsReport.rpt")); else rpt.Load(Server.MapPath(Constants.ROOT_DIRECTORY + "/Reports/_MostSalableItemsReportGrouped.rpt")); return rpt; }
protected void Page_Init(object sender, EventArgs e) { //if (!IsPostBack) //{ int temp; if (Request["tipo"] != null && int.TryParse(Request["tipo"].ToString(), out temp) && (int.Parse(Request["tipo"].ToString()) == 1 || int.Parse(Request["tipo"].ToString()) == 2) && Request["codigo"] != null && int.TryParse(Request["codigo"].ToString(), out temp)) { ReportDocument doc = new ReportDocument(); Hashtable hash = null; if (int.Parse(Request["tipo"].ToString()) == 1) //Movimentação { IList<Movimento> lm = new List<Movimento>(); lm.Add(Factory.GetInstance<IMovimentacao>().BuscarPorCodigo<Movimento>(int.Parse(Request["codigo"].ToString()))); hash = Factory.GetInstance<IMovimentacao>().RetornaHashMovimentacaoRemanejamento<IList<Movimento>>(int.Parse(Request["tipo"].ToString()), lm); DSCorpoMovimentacao dsc = new DSCorpoMovimentacao(); DSCabecalhoMovimentacao dscab = new DSCabecalhoMovimentacao(); dsc.Tables.Add((DataTable)hash["corpo"]); dscab.Tables.Add((DataTable)hash["cabecalho"]); doc.Load(Server.MapPath("RelatoriosCrystal/RelMovimentacao.rpt")); doc.SetDataSource(dscab.Tables[1]); doc.Subreports[0].SetDataSource(dsc.Tables[1]); CrystalReportViewer_Relatorio.ReportSource = doc; CrystalReportViewer_Relatorio.DataBind(); } else //Remanejamento { IList<RemanejamentoMedicamento> lrm = new List<RemanejamentoMedicamento>(); lrm.Add(Factory.GetInstance<IMovimentacao>().BuscarPorCodigo<RemanejamentoMedicamento>(int.Parse(Request["codigo"].ToString()))); hash = Factory.GetInstance<IMovimentacao>().RetornaHashMovimentacaoRemanejamento<IList<RemanejamentoMedicamento>>(int.Parse(Request["tipo"].ToString()),lrm); DSCorpoRemanejamento dsr = new DSCorpoRemanejamento(); DSCabecalhoRemanejamento dsc = new DSCabecalhoRemanejamento(); dsr.Tables.Add((DataTable)hash["corpo"]); dsc.Tables.Add((DataTable)hash["cabecalho"]); doc.Load(Server.MapPath("RelatoriosCrystal/RelRemanejamento.rpt")); doc.SetDataSource(dsc.Tables[1]); doc.Subreports[0].SetDataSource(dsr.Tables[1]); CrystalReportViewer_Relatorio.ReportSource = doc; CrystalReportViewer_Relatorio.DataBind(); } } //} }
protected void Page_Load(object sender, EventArgs e) { try { DataTable _dtCabeceraOper = null; DataTable _dtDetalleOper = null; _dtCabeceraOper = (DataTable)Session["dtCabeceraOperacion"]; _dtDetalleOper = g_dtDetOperacion; if (_dtDetalleOper != null && _dtCabeceraOper != null) { if (_dtDetalleOper.Rows.Count != 0) { ReportDocument rpt = new ReportDocument(); rpt.Load(Server.MapPath("rpt/crFactura.rpt")); rpt.FileName = Server.MapPath("rpt/crFactura.rpt"); rpt.SetDataSource(_dtCabeceraOper); rpt.Subreports[0].SetDataSource(_dtDetalleOper); CrystalReportViewer1.ReportSource = rpt; CrystalReportViewer1.ReuseParameterValuesOnRefresh = true; } } } catch (Exception ex) { throw ex; } }
private void CustomerTransactionReportForm_Load(object sender, EventArgs e) { ReportDocument cryRpt; using (Devart.Data.SQLite.SQLiteConnection myConn = new Devart.Data.SQLite.SQLiteConnection(myConfig.connstr)) { using (SQLiteCommand myCmd = new SQLiteCommand(sqlcmd, myConn)) { myConn.Open(); using (SQLiteDataReader myReader = myCmd.ExecuteReader()) { dataSet2.customer_trans.Load(myReader); myConn.Close(); } } } this.customerTableAdapter1.Fill(this.dataSet2.customer); this.configurationTableAdapter1.Fill(this.dataSet2.configuration); CustomerTransactionReport rpt = new CustomerTransactionReport(); rpt.SetDataSource(this.dataSet2); cryRpt = new ReportDocument(); cryRpt.Load(rpt.FileName.ToString()); cryRpt.SetDataSource(this.dataSet2); cryRpt.ExportToDisk(ExportFormatType.PortableDocFormat, ReportFolder.reportFolderName + @"\CustomerTransactions.pdf"); }
//结帐查询 public static ReportDocument LoadReport(string rptfilename, string sql, string tablename) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); SqlDataAdapter dbDA = new SqlDataAdapter(); DataSet dataSet = new DataSet(); conn.ConnectionString = rms_var.ConnStr; try { SqlCommand selectCMD = new SqlCommand(sql, conn); selectCMD.CommandTimeout = 30; dbDA.SelectCommand = selectCMD; conn.Open(); dbDA.Fill(dataSet, tablename); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } ReportDocument cr_doc; cr_doc = new ReportDocument(); cr_doc.Load(rptfilename); cr_doc.SetDataSource(dataSet); return cr_doc; }
private void btnSearch_Click(object sender, EventArgs e) { int DoctorID; DoctorID = Convert.ToInt32(cbDoctor.SelectedValue.ToString()); ReportDocument reportDocument = new ReportDocument(); ParameterField paramField = new ParameterField(); ParameterFields paramFields = new ParameterFields(); ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue(); string ReportPath = ConfigurationManager.AppSettings["ReportPath"]; paramField.Name = "@DoctorID"; paramDiscreteValue.Value = DoctorID; reportDocument.Load(ReportPath + "Report\\DoctorCrystalReport.rpt"); ConnectionInfo connectionInfo = new ConnectionInfo(); connectionInfo.DatabaseName = "DB_MedicalShop_02Sept20159PM"; //connectionInfo.UserID = "wms"; //connectionInfo.Password = "******"; connectionInfo.IntegratedSecurity = true; SetDBLogonForReport(connectionInfo, reportDocument); reportDocument.SetParameterValue("@DoctorID", DoctorID); DoctorCrystalRpt.ReportSource = reportDocument; DoctorCrystalRpt.ToolPanelView = CrystalDecisions.Windows.Forms.ToolPanelViewType.None; }
private void FrmProjectReportViewer_Load(object sender, EventArgs e) { try { if (FrmFlagProject.ProjectFlag == 0) { lblProjectFlag.Text = "لیست طرح های پژوهشی در دست اجرا در سال" + " " + FrmFlagProject.year; } else if (FrmFlagProject.ProjectFlag == 1) { lblProjectFlag.Text = "لیست طرح های پژوهشی پایان یافته در سال" + " " + FrmFlagProject.year; } DataSet dataset = new DataSet(); ReportDocument RepDoc = new ReportDocument(); string dir = Environment.CurrentDirectory; dir = dir + "\\CrtMainProject.rpt"; RepDoc.Load(dir); RepDoc.SetDataSource(FrmFlagProject.objTbl); crystalReportViewer1.ReportSource = RepDoc; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void cboYear_SelectedIndexChanged(object sender, EventArgs e) { try { string RepPath; DataSet dataset = new DataSet(); ReportDocument RepDoc = new ReportDocument(); RepPath = @"D:\Project\Industrial College\Project1\Source23\Source22\Source21\Pazhohesh\Pazhohesh\Main\CrsThesis1.rpt"; RepDoc.Load(RepPath); objDataAdapter.SelectCommand = new SqlCommand(); SqlConnection objConnection = new SqlConnection("Data Source=User-PC;Initial Catalog=ResearchProje1;Integrated Security=True"); objDataAdapter.SelectCommand.Connection = objConnection; DataTable objGozaresh = new DataTable(); objGozaresh.Clear(); objDataAdapter.SelectCommand.Parameters.AddWithValue("@DeliveryDate" , cboYear.Text + "%"); objDataAdapter.SelectCommand.CommandText = "Select * from VwThesisGozaresh1 where DeliveryDate Like @DeliveryDate"; objDataAdapter.Fill(objGozaresh); RepDoc.SetDataSource(objGozaresh); crystalReportViewer1.ReportSource = RepDoc; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void LoadReportFile(string rptName, string procName, string rptTitle, Dictionary<string, string> paraValues, Dictionary<string, string> procValues) { try { string rptPath = Server.MapPath("/ReportPages/" + rptName); _rptDoc = new ReportDocument(); _rptDoc.Load(rptPath); _rptDoc.SummaryInfo.ReportTitle = rptTitle; _rptDoc.SetDataSource(ExcuteQuery(procName, procValues)); if (paraValues.Count > 0) foreach (KeyValuePair<string, string> par in paraValues) { ParameterDiscreteValue pValue = new ParameterDiscreteValue(); pValue.Value = par.Value; ParameterFieldDefinition pDef = _rptDoc.DataDefinition.ParameterFields[par.Key]; pDef.CurrentValues.Clear(); pDef.CurrentValues.Add(pValue); pDef.ApplyCurrentValues(pDef.CurrentValues); } crystalReportViewer.ReportSource = _rptDoc; } catch (Exception e) { label.Text = "Report Error: " + e.Message; } finally { GC.Collect(); } }
public void Exportar(string key) { try { ReportDocument objRpt = new ReportDocument(); DataSet ds = (DataSet)this.GetDataReport(key); string reportPath = "C:\\Reportes\\CRTejTicket_rpt.rpt"; objRpt.Load(reportPath); ExportOptions crExportOptions = new ExportOptions(); objRpt.SetDataSource(ds.Tables[0]); SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.InitialDirectory = Environment.SpecialFolder.MyDocuments.ToString(); saveFileDialog.Filter = "Document (*.pdf)|*.PDF"; saveFileDialog.FilterIndex = 1; saveFileDialog.FileName = "Etiqueta.pdf"; if (saveFileDialog.ShowDialog() == DialogResult.OK) { objRpt.ExportToDisk(ExportFormatType.PortableDocFormat, saveFileDialog.FileName); ; } crExportOptions = objRpt.ExportOptions; objRpt.Export(); } catch (Exception ex) { ex.ToString(); } }
public RptDefinitionWriter(string filename) { Report = new ReportDocument(); Report.Load(filename, OpenReportMethod.OpenReportByTempCopy); Trace.WriteLine("Loaded report"); }
private void btnGerar_Click(object sender, EventArgs e) { ReportDocument crDocument = new ReportDocument(); crDocument.Load(Application.StartupPath + @"\..\..\RelLocalizacaoProcesso.rpt"); if (rbSemFiltro.Checked) { crDocument.SetDataSource(new RelProcesso(new Banco()).semfiltro()); } else if (ckbNome.Checked && !ckbSituacao.Checked) { crDocument.SetDataSource(new RelProcesso(new Banco()).porNome(ttbNome.Text)); } else if (!ckbNome.Checked && ckbSituacao.Checked) { //if(cbbSituacao.SelectedText == "Em Pasta") // crDocument.SetDataSource(new RelProcesso(new Banco()).emPasta())); //else // crDocument.SetDataSource(new RelProcesso(new Banco()).arquivado())); } else if (ckbNome.Checked && ckbSituacao.Checked) { } frmVisualizador f = new frmVisualizador(); f.carregar(crDocument); f.ShowDialog(); }
protected void GenerateReport() { System.Console.Beep(); //Create report document ReportDocument crystalReport = new ReportDocument(); if (txtStart.Text != "" && txtEnd.Text != "" ) { //Load crystal report made in design view crystalReport.Load(Server.MapPath("Reports/Notifications.rpt")); //Set DataBase Login Info crystalReport.SetDatabaseLogon("root", "123", @"localhost", "nsis"); //Provide parameter values crystalReport.SetParameterValue("start", txtStart.Text.ToString()); crystalReport.SetParameterValue("end", txtEnd.Text.ToString()); //Set Report in to Report Viewer crvReportViewer.ReportSource = crystalReport; } else { // lblError.ForeColor = Color.Red; // lblError.Text = "Please Provide Valid Details"; } }
private void frmReamMoneyPrint_Load(object sender, EventArgs e) { this.WindowState = FormWindowState.Maximized; ReamMoney reamMoney = new ReamMoney(); reamMoney = reamMoneyService.getReamMoneyByID(rid); List<ReamMoneyReport> reamMoneyReports = new List<ReamMoneyReport>(); ReamMoneyReport reamMoneyReport = new ReamMoneyReport(); Person p = new Person(); p = personService.getPersonBypsnCode(reamMoney.person.psnCode); reamMoneyReport.fullName = p.psnPreFix + " " + p.psnName + " " + p.psnLastName; reamMoneyReport.amount =Convert.ToString(reamMoney.amount); reamMoneyReport.reamDate = reamMoney.reamdate; reamMoneyReports.Add(reamMoneyReport); ReportDocument rp = new ReportDocument(); string path = Application.StartupPath + @"\report\rptReamMoney.rpt"; rp.Load(path); rp.SetDataSource(reamMoneyReports); PV.ReportSource = rp; }
protected void Page_Load(object sender, EventArgs e) { Label1.Text = Session["uname"].ToString(); ReportDocument cryRpt = new ReportDocument(); cryRpt.Load(Server.MapPath("SoldProduct.rpt")); /* ParameterFieldDefinitions PFDS; ParameterFieldDefinition PFD; ParameterValues PVS = new ParameterValues(); ParameterDiscreteValue PDV = new ParameterDiscreteValue(); PVS.Clear(); PDV.Value = ""; PFDS = cryRpt.DataDefinition.ParameterFields; PFD = PFDS["@sDate"]; PVS = PFD.CurrentValues; // PVS.Add(PDV); PFD.ApplyCurrentValues(PVS); PDV.Value = ""; PFDS = cryRpt.DataDefinition.ParameterFields; PFD = PFDS["@eDate"]; PVS = PFD.CurrentValues; //PVS.Clear(); PVS.Add(PDV); PFD.ApplyCurrentValues(PVS);*/ cryRpt.SetDatabaseLogon("sa", "123", "server", "auction"); CrystalReportViewer1.ReportSource = cryRpt; CrystalReportViewer1.RefreshReport(); }
private void button1_Click(object sender, EventArgs e) { try { ReportDocument rd; rd = new ReportDocument(); rd.Load(@"C:\Users\PLABON\Documents\Visual Studio 2013\Projects\SalesAndInventorySystem\SalesAndInventorySystemUI\Report\a.rpt"); List<PersonType> company = companyGateway.GetCompanies(); var companyX = company.Select(x => new {x.ID, x.Name}); rd.SetDataSource(companyX); crystalReportViewer1.ReportSource = rd; crystalReportViewer1.Refresh(); int a = 10; if (File.Exists(@"D:\" + "AAAA" + a +".pdf")) File.Delete(@"D:\" + a++ + ".pdf"); rd.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, @"D:\" + a + ".pdf"); } catch (Exception) { throw; } }
public ActionResult ExportReport() { List<Investment> allInvestment = new List<Investment>(); using (efarmingEntities dc = new efarmingEntities()) { allInvestment = dc.Investments.ToList(); } ReportDocument rd = new ReportDocument(); rd.Load(Path.Combine(Server.MapPath("~/Reports"), "rpt_InvestmentList.rpt")); rd.SetDataSource(allInvestment); Response.Buffer = false; Response.ClearContent(); Response.ClearHeaders(); try { Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); stream.Seek(0, SeekOrigin.Begin); return File(stream, "application/pdf", "InvestmentList.pdf"); } catch (Exception) { throw; } }
private void frminhoadon_Load(object sender, EventArgs e) { DataTable ds = hdbus.getHoaDon(KHAIBAO.mabenhnhan, KHAIBAO.ngaykham); ds.TableName = "HoaDon"; DataTable dt2 = ctkbus.getDonThuoc(KHAIBAO.mabenhnhan + KHAIBAO.ngaykham); dt2.TableName = "DonThuoc"; DataTable dt3 = cdbus.getCachDung(); dt3.TableName = "CachDung"; DataTable dt4 = ltbus.getLoaiThuoc(); dt4.TableName = "LoaiThuoc"; DataTable dt5 = dvbus.getDonVi(); dt5.TableName = "DonVi"; DataSet dts = new DataSet(); dts.Tables.Add(ds); dts.Tables.Add(dt2); dts.Tables.Add(dt3); dts.Tables.Add(dt4); dts.Tables.Add(dt5); if (float.Parse(ds.Rows[0]["TienThuoc"].ToString()) == 0) { ReportDocument report = new ReportDocument(); report.Load("..\\..\\inhoadon1.rpt"); report.SetDataSource(dts); crystalReportViewer.ReportSource = report; inhoadon1 dt = new inhoadon1(); } else { ReportDocument report = new ReportDocument(); report.Load("..\\..\\inhoadon.rpt"); report.SetDataSource(dts); crystalReportViewer.ReportSource = report; inhoadon dt = new inhoadon(); } }
/// <summary> /// Erzeugt aus einer CrystalReportsDatei oder einem PDF einen PDF Stream und sendet ihn an den Client /// </summary> /// <param name="pin_ReportVorlage">Pfad der Vorlage</param> /// <param name="pin_ReportAuswahl">reportspezifische Daten</param> /// <returns>Stream der ein PDF enthält</returns> public Stream ErzeugeReport(string pin_ReportVorlage, string pin_ReportAuswahl) { if (pin_ReportVorlage.Substring(pin_ReportVorlage.LastIndexOf(".") + 1) == "pdf") { // Umwandeln des PDF Vordrucks in einen Stream FileStream Report = new FileStream(Directory.GetCurrentDirectory() + pin_ReportVorlage, System.IO.FileMode.Open, System.IO.FileAccess.Read); byte[] Buffer = new byte[(int)Report.Length]; Report.Read(Buffer, 0, (int)Report.Length); Report.Close(); // Versenden der Daten als MemoryStream System.Windows.Forms.MessageBox.Show("Schicke Raus !!"); return new MemoryStream(Buffer, 0, Buffer.Length); } else { // Erstellen eines Reports ReportDocument Report = new ReportDocument(); // Lade Reportvorlage Report.Load(Directory.GetCurrentDirectory() + pin_ReportVorlage); // Besondere SELECT Anfrage auf den Datensatz anwenden if (pin_ReportAuswahl != String.Empty) Report.DataDefinition.RecordSelectionFormula = pin_ReportAuswahl; // muss als Stream übertragen werden, da ReportDocuments nicht Serializable sind return Report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); } }
/// <summary> /// 產生報表 /// </summary> private void ConfigureCrystalReports() { try { string strDate = Session[string.Format("{0}QueryDateRange", Request["TimeStamp"].ToString())].ToString(); if (strDate == "~") strDate = ""; string strTime = Session[string.Format("{0}QueryTimeRange", Request["TimeStamp"].ToString())].ToString(); if (strTime == "~") strTime = ""; customerReport = new ReportDocument(); //取得報表格式檔RPT customerReport.Load(Server.MapPath("ITM061R.rpt")); customerReport.SetDataSource((DataTable)Session[string.Format("{0}ITM061", Request["TimeStamp"].ToString())]); ParameterFields par1 = new ParameterFields(); AddParameter("ProgramName", PageProgramName, par1); ParameterFields par2 = new ParameterFields(); AddParameter("UserName", Session["UID"].ToString(), par2); ParameterFields par3 = new ParameterFields(); AddParameter("QueryDateRange", strDate, par3); ParameterFields par4 = new ParameterFields(); AddParameter("QueryTime", strTime, par4); CRV1.ParameterFieldInfo.Add(par1[0]); CRV1.ParameterFieldInfo.Add(par2[0]); CRV1.ParameterFieldInfo.Add(par3[0]); CRV1.ParameterFieldInfo.Add(par4[0]); CRV1.ReportSource = customerReport; } catch (Exception ex) { lblErrorMessage.Text = ex.Message; } }//ConfigureCrystalReports
private void GenerateWord() { ReportDocument rpt = new ReportDocument(); rpt.Load(Server.MapPath(Constants.ROOT_DIRECTORY + "/Reports/GeneralLedger.rpt")); ExportOptions exportop = new ExportOptions(); DiskFileDestinationOptions dest = new DiskFileDestinationOptions(); string strPath = Server.MapPath(@"\retailplus\temp\"); string strFileName = "generalledger_" + Session["UserName"].ToString() + "_" + DateTime.Now.ToString("yyyyMMddhhmmssff") + ".doc"; if (System.IO.File.Exists(strPath + strFileName)) System.IO.File.Delete(strPath + strFileName); dest.DiskFileName = strPath + strFileName; exportop = rpt.ExportOptions; SetDataSource(rpt); exportop.DestinationOptions = dest; exportop.ExportDestinationType = ExportDestinationType.DiskFile; exportop.ExportFormatType = ExportFormatType.WordForWindows; rpt.Export(); rpt.Close(); rpt.Dispose(); fraViewer.Attributes.Add("src","/retailplus/temp/" + strFileName); }
private void CriaPDF() { Hashtable hashtable = (Hashtable)Session["HashAgendaMontadaPublicada"]; DSCabecalhoAgendaMontadaPublicada cabecalho = new DSCabecalhoAgendaMontadaPublicada(); cabecalho.Tables.Add((DataTable)hashtable["cabecalho"]); DSRelatorioAgendaMontadaPublicada dados = new DSRelatorioAgendaMontadaPublicada(); dados.Tables.Add((DataTable)hashtable["dados"]); ReportDocument repDoc = new ReportDocument(); repDoc.Load(Server.MapPath("RelatoriosCrystal/CrystalReportViewer_AgendaMontadaPublicada.rpt")); repDoc.SetDataSource(dados.Tables[1]); repDoc.Subreports["CrystalReportViewer_CabecalhoAgendaMontadaPublicada.rpt"].SetDataSource(cabecalho.Tables[1]); System.IO.Stream s = repDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); Response.ClearContent(); Response.ClearHeaders(); Response.ContentType = "applicattion/octect-stream"; Response.AddHeader("Content-Disposition", "attachment;filename=RelatorioDeAgendasMontadasPublicadas.pdf"); Response.AddHeader("Content-Length", s.Length.ToString()); Response.BinaryWrite(((System.IO.MemoryStream)s).ToArray()); Response.End(); Session.Remove("HashAgendaMontadaPublicada"); }
protected void CriaPDF() { Hashtable hash = (Hashtable)Session["HashVagasDisponivel"]; //CrystalReportViewer_AgendaPrestador.DisplayToolbar = true; //CrystalReportViewer_AgendaPrestador.EnableDatabaseLogonPrompt = false; DSRelatorioVagasDisponivel conteudo = new DSRelatorioVagasDisponivel(); conteudo.Tables.Add((DataTable)hash["corpo"]); ReportDocument repDoc = new ReportDocument(); repDoc.Load(Server.MapPath("RelatoriosCrystal/CrystalReportViewer_VagasDisponivel.rpt")); repDoc.SetDataSource(conteudo.Tables[1]); //repDoc.Database.Tables["CabecalhoAgendaPrestador"].SetDataSource((DataTable)hash["cabecalho"]); //repDoc.Database.Tables["RelatorioAgendaPrestador"].SetDataSource((DataTable)hash["corpo"]); System.IO.Stream s = repDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); Response.ClearContent(); Response.ClearHeaders(); Response.ContentType = "applicattion/octect-stream"; Response.AddHeader("Content-Disposition", "attachment;filename=RelatorioVagasDisponivel.pdf"); Response.AddHeader("Content-Length", s.Length.ToString()); Response.BinaryWrite(((System.IO.MemoryStream)s).ToArray()); Response.End(); }
protected void Page_Load(object sender, EventArgs e) { // string showreport = Request.QueryString["showreport"] == null ? "0" : Request.QueryString["showreport"].ToString(); if (showreport == "1") { try { this.Button1.Visible = false; string PARM = Request.QueryString["PARM"] == null ? "0" : Request.QueryString["PARM"].ToString(); if (PARM != "0") { string data_index = GlobeAtt.DATA_INDEX; string strdata = data_index + DES.DESDecrypt(PARM, DES.Key); strdata = strdata.Trim(); report_file = BaseFun.getStrByIndex(strdata, data_index + "REPORT_FILE|", data_index); report_sql = BaseFun.getStrByIndex(strdata, data_index + "REPORT_SQL|", data_index); report_parm = BaseFun.getStrByIndex(strdata, data_index + "REPORT_PARM|", data_index); } else { report_file = Session["REPORT_FILE"].ToString(); report_sql = Session["REPORT_SQL"].ToString(); report_parm = Session["REPORT_PARM"].ToString(); } // Response.Write(report_sql); ReportDocument rd = new ReportDocument(); string file_ = Server.MapPath("../Report/" + report_file); try { rd.Load(file_); // cs.ReportDocument.Load(rd.FileName); PageMargins pm = new PageMargins(); rd.PrintOptions.ApplyPageMargins(pm); } catch (Exception ex) { Response.Write(report_file + ex.Message); return; } if (report_sql.Length > 10) { Oracle db = new Oracle(); DataTable dt_report = new DataTable(); db.ExcuteDataTable(dt_report, report_sql, CommandType.Text); rd.SetDataSource(dt_report); CrystalReportViewer1.ReportSource = rd; CrystalReportViewer1.DataBind(); } else { Oracle db = new Oracle(); ConnectionInfo ConnectionInfo1 = new ConnectionInfo(); ConnectionInfo1 = rd.Database.Tables[0].LogOnInfo.ConnectionInfo; rd.SetDatabaseLogon(db.user_id, db.password); ConnectionInfo1.DatabaseName = ""; ConnectionInfo1.UserID = db.user_id; ConnectionInfo1.Password = db.password; ConnectionInfo1.ServerName = db.data_source; SetDBLogonForReport(ConnectionInfo1, rd); if (rd.HasSavedData == true) { rd.Refresh(); } string[] parm = report_parm.Split(','); ParameterFields ParamFields = new ParameterFields(); for (int i = 0; i < rd.ParameterFields.Count; i++) { ParameterDiscreteValue ParamDiscreteValue = new ParameterDiscreteValue(); if (parm[i] != "[]") { ParamDiscreteValue.Value = parm[i]; if (i < parm.Length && parm[i] != "") { rd.ParameterFields[i].CurrentValues.Add(ParamDiscreteValue); } } } CrystalReportViewer1.ReportSource = rd; CrystalReportViewer1.DataBind(); } } catch (Exception ex) { Response.Write(ex.Message); return; } } else { // string a002_key = Request.QueryString["A002KEY"] == null ? "-1" : Request.QueryString["A002KEY"].ToString(); // string A00201KEY = Request.QueryString["A00201KEY"] == null ? "-1" : Request.QueryString["A00201KEY"].ToString(); string report_conditon = Request.QueryString["reportconditon"] == null ? "1" : Request.QueryString["reportconditon"].ToString(); string key = Request.QueryString["JUMP_KEY"] == null ? "-1" : Request.QueryString["JUMP_KEY"].ToString(); string a002_key = Request.QueryString["JUMP_A002_KEY"] == null ? "-1" : Request.QueryString["JUMP_A002_KEY"].ToString(); string IF_JUMP = Request.QueryString["IF_JUMP"] == null ? "1" : Request.QueryString["IF_JUMP"].ToString(); // dt_a002 = Fun.getDtBySql("Select t.* from A002 t where menu_id='" + a002_key +"'"); dt_main = Fun.getDtBySql("Select t.*,pkg_a.getmenuname(t.a002_key,'" + GlobeAtt.A007_KEY + "') as show_name from A002_v01 t where a002_key='" + a002_key + "'"); try { report_file = dt_main.Rows[0]["datawindow_name"].ToString(); } catch (Exception ex) { Response.Write("report_file" + ex.Message); return; } try { menu_name = dt_main.Rows[0]["show_name"].ToString(); } catch (Exception ex) { menu_name = ""; Response.Write("title" + ex.Message); } try { if (report_conditon == "1") { report_sql = dt_main.Rows[0]["query_table"].ToString(); if (report_sql.Length < 20) { report_sql = "Select t.* from " + report_sql + "t where 1=1 "; } Session["REPORT_SQL"] = report_sql; } else { report_sql = Session["REPORT_SQL"].ToString(); } } catch (Exception ex) { Response.Write("report_sql" + ex.Message); return; } string file_ = Server.MapPath("../report/" + report_file); try { rd.Load(file_); ConnectionInfo ConnectionInfo1 = new ConnectionInfo(); ConnectionInfo1 = rd.Database.Tables[0].LogOnInfo.ConnectionInfo; ConnectionInfo1.DatabaseName = ""; ConnectionInfo1.UserID = Fun.db.user_id; ConnectionInfo1.Password = Fun.db.password; ConnectionInfo1.ServerName = Fun.db.data_source; SetDBLogonForReport(ConnectionInfo1, rd); if (rd.HasSavedData == true) { rd.Refresh(); } CrystalReportViewer1.ReportSource = rd; CrystalReportViewer1.DataBind(); } catch (Exception ex) { Response.Write(report_file + ex.Message); return; } t_con.Visible = false; Button1.Visible = false; //else //{ // cs = (CrystalReportSource) CrystalReportViewer1.ReportSource; //} /* * * if (report_conditon == "1") * { * set_condition(); * } * else * { * // Response.Write(report_sql); * t_con.Visible = false; * Button1.Visible = false; * //cs.ReportDocument.SetDatabaseLogon(Fun.db.user_id, Fun.db.password, Fun.db.data_source, Fun.db.data_source); * //cs.ReportDocument.Refresh(); * // CrystalReportViewer1.ReportSource = cs; * //CrystalReportViewer1.DataBind(); * // cs.DataBind(); * return; * Oracle db = new Oracle(); * DataTable dt_report = new DataTable(); * db.ExcuteDataTable(dt_report, report_sql, CommandType.Text); * * cs.ReportDocument.SetDataSource(dt_report); * cs.DataBind(); * * CrystalReportViewer1.ReportSource = cs; * CrystalReportViewer1.DataBind(); * * * } * * * * } * * // CryReport.get_report_condition(CrystalReportViewer1, Server.MapPath("../report/" + report_file), t_con); */ } }
private void GenerateReport(string Title, string From, string To, string group, string way, string strcond) { try { DL_DBAccess = new DatabaseAccessLayer(); string Query = ""; // Query = "SELECT CR_GIN_NO,CONVERT(VARCHAR,CR_GIN_DATE,106) AS CR_GIN_DATE,CR_P_CODE,P_NAME,CR_NET_AMT,CD_I_CODE,I_NAME,I_UOM_NAME,CD_UOM,CD_PO_CODE,CPOM_PONO,cast(CD_RATE as numeric(20,2)) as Rate,cast(CD_ORIGIONAL_QTY as numeric(10,3)) as OriginalQty,cast(CD_CHALLAN_QTY as numeric(10,3)) as ChallanQty,cast(CD_RECEIVED_QTY as numeric(10,3)) as ReceivedQty,cast(CD_AMOUNT as numeric(20,2)) as Amount FROM CUSTREJECTION_MASTER,CUSTREJECTION_DETAIL,CUSTPO_MASTER,ITEM_UNIT_MASTER,ITEM_MASTER,PARTY_MASTER where CUSTREJECTION_MASTER.CR_P_CODE=P_CODE and CUSTREJECTION_DETAIL.CD_CR_CODE=CR_CODE and ITEM_UNIT_MASTER.I_UOM_CODE=CD_UOM and CPOM_CODE=CD_PO_CODE and I_CODE=CD_I_CODE"; if (way == "Direct") { Query = "SELECT DISTINCT ITEM_MASTER.I_CODENO, ITEM_MASTER.I_CODE, ISSUE_MASTER.IM_CODE, ISSUE_MASTER.IM_MATERIAL_REQ, ISSUE_MASTER.IM_NO, CONVERT(varchar, ISSUE_MASTER.IM_DATE, 106) AS IM_DATE, (CASE WHEN IM_TYPE = 1 THEN 'As Per Material Req' ELSE 'Direct' END) AS IM_TYPE ,IM_ISSUEBY,IM_REQBY, ISNULL(MATERIAL_REQUISITION_MASTER.MR_BATCH_NO, '') AS MR_BATCH_NO, ITEM_MASTER.I_NAME, ITEM_UNIT_MASTER.I_UOM_NAME, ISNULL(ITEM_MASTER.I_INV_RATE, 0.00) AS I_CURRENT_BAL, ISSUE_MASTER_DETAIL.IMD_REQ_QTY, ISSUE_MASTER_DETAIL.IMD_ISSUE_QTY, ISSUE_MASTER_DETAIL.IMD_REMARK FROM MATERIAL_REQUISITION_MASTER RIGHT OUTER JOIN ISSUE_MASTER INNER JOIN ISSUE_MASTER_DETAIL INNER JOIN ITEM_MASTER ON ISSUE_MASTER_DETAIL.IMD_I_CODE = ITEM_MASTER.I_CODE AND ISSUE_MASTER_DETAIL.IMD_I_CODE = ITEM_MASTER.I_CODE INNER JOIN ITEM_UNIT_MASTER ON ISSUE_MASTER_DETAIL.IMD_UOM = ITEM_UNIT_MASTER.I_UOM_CODE AND ISSUE_MASTER_DETAIL.IMD_UOM = ITEM_UNIT_MASTER.I_UOM_CODE ON ISSUE_MASTER.IM_CODE = ISSUE_MASTER_DETAIL.IM_CODE ON MATERIAL_REQUISITION_MASTER.MR_CODE = ISSUE_MASTER.IM_MATERIAL_REQ WHERE " + strcond + " (ISSUE_MASTER.ES_DELETE = 0) AND (ISSUE_MASTER.IM_COMP_ID = " + Session["CompanyCode"] + ") AND (ISSUE_MASTER_DETAIL.ES_DELETE = 0) and IM_TYPE=2 "; // Query = "SELECT distinct(I_CODENO) as I_CODENO,ISSUE_MASTER.IM_CODE,IM_MATERIAL_REQ,IM_NO,convert(varchar,IM_DATE,106)as IM_DATE,(case when IM_TYPE=1 then 'As Per Material Req' else 'Direct' end) as IM_TYPE,isnull(MR_BATCH_NO,'') as MR_BATCH_NO ,I_NAME,I_UOM_NAME,I_CURRENT_BAL,IMD_REQ_QTY,IMD_ISSUE_QTY,IMD_REMARK FROM ISSUE_MASTER LEFT OUTER JOIN MATERIAL_REQUISITION_MASTER ON ISSUE_MASTER.IM_MATERIAL_REQ = MATERIAL_REQUISITION_MASTER.MR_CODE,ITEM_MASTER,MATERIAL_REQUISITION_DETAIL,ITEM_UNIT_MASTER,ISSUE_MASTER_DETAIL where ISSUE_MASTER.ES_DELETE=0 and IM_COMP_ID=1 and I_CODE=IMD_I_CODE and ITEM_UNIT_MASTER.I_UOM_CODE=ISSUE_MASTER_DETAIL.IMD_UOM and ISSUE_MASTER_DETAIL.ES_DELETE=0 "; } else { Query = " SELECT distinct(I_CODENO) as I_CODENO,ITEM_MASTER.I_CODE,ISSUE_MASTER.IM_CODE,IM_MATERIAL_REQ,IM_NO,convert(varchar,IM_DATE,106)as IM_DATE,(case when IM_TYPE=1 then 'As Per Material Req' else 'Direct' end) as IM_TYPE ,IM_ISSUEBY,IM_REQBY,isnull(MR_BATCH_NO,'') as MR_BATCH_NO ,I_NAME,I_UOM_NAME,isnull(I_CURRENT_BAL,0.00)as I_CURRENT_BAL,IMD_REQ_QTY,IMD_ISSUE_QTY,IMD_REMARK FROM MATERIAL_REQUISITION_MASTER , ISSUE_MASTER INNER JOIN ISSUE_MASTER_DETAIL INNER JOIN ITEM_MASTER ON ISSUE_MASTER_DETAIL.IMD_I_CODE = ITEM_MASTER.I_CODE INNER JOIN ITEM_UNIT_MASTER ON ISSUE_MASTER_DETAIL.IMD_UOM = ITEM_UNIT_MASTER.I_UOM_CODE ON ISSUE_MASTER.IM_CODE = ISSUE_MASTER_DETAIL.IM_CODE where " + strcond + " ISSUE_MASTER.ES_DELETE=0 and IM_COMP_ID=" + Session["CompanyCode"] + " and I_CODE=IMD_I_CODE and ITEM_UNIT_MASTER.I_UOM_CODE=ISSUE_MASTER_DETAIL.IMD_UOM and ISSUE_MASTER_DETAIL.ES_DELETE=0 and MATERIAL_REQUISITION_MASTER.MR_CODE = ISSUE_MASTER.IM_MATERIAL_REQ "; //Query = "SELECT distinct(I_CODENO) as I_CODENO,ISSUE_MASTER.IM_CODE,IM_MATERIAL_REQ,IM_NO,convert(varchar,IM_DATE,106)as IM_DATE,(case when IM_TYPE=1 then 'As Per Material Req' else 'Direct' end) as IM_TYPE,isnull(MR_BATCH_NO,'') as MR_BATCH_NO ,I_NAME,I_UOM_NAME,I_CURRENT_BAL,IMD_REQ_QTY,IMD_ISSUE_QTY,IMD_REMARK FROM ISSUE_MASTER , MATERIAL_REQUISITION_MASTER,ITEM_MASTER,MATERIAL_REQUISITION_DETAIL,ITEM_UNIT_MASTER,ISSUE_MASTER_DETAIL where ISSUE_MASTER.ES_DELETE=0 and IM_COMP_ID=1 and I_CODE=IMD_I_CODE and ITEM_UNIT_MASTER.I_UOM_CODE=ISSUE_MASTER_DETAIL.IMD_UOM and ISSUE_MASTER_DETAIL.ES_DELETE=0 and ISSUE_MASTER.IM_MATERIAL_REQ = MATERIAL_REQUISITION_MASTER.MR_CODE "; } #region MyRegion /* #region Detail * if (way == "Direct") * { #region Datewise * if (group == "Datewise") * { * * if (date1 == "All" && item == "All") * { * Query = Query; * * * } * if (date1 != "All" && item != "All") * { * Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "' and ITEM_MASTER.I_CODE='" + i_name + "' "; * } * if (date1 != "All" && item == "All") * { * Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "'"; * * } * if (date1 == "All" && item != "All") * { * Query = Query + " and ITEM_MASTER.I_CODE='" + i_name + "' "; * * } * * } #endregion #region ItemWise * if (group == "ItemWise") * { * if (date1 == "All" && item == "All") * { * Query = Query; * * * } * if (date1 != "All" && item != "All") * { * Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "' and ITEM_MASTER.I_CODE='" + i_name + "'"; * * } * if (date1 != "All" && item == "All") * { * Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "'"; * * } * if (date1 == "All" && item != "All") * { * Query = Query + " and ITEM_MASTER.I_CODE='" + i_name + "' "; * * } * * } #endregion * * } #endregion * #region AsPerReq * if (way == "AsperReq") * { #region Datewise * if (group == "Datewise") * { * * if (date1 == "All" && item == "All") * { * Query = Query; * * * } * if (date1 != "All" && item != "All") * { * Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "' and ITEM_MASTER.I_CODE='" + i_name + "' "; * } * if (date1 != "All" && item == "All") * { * Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "'"; * * } * if (date1 == "All" && item != "All") * { * Query = Query + " and ITEM_MASTER.I_CODE='" + i_name + "' "; * * } * } #endregion #region ItemWise * if (group == "ItemWise") * { * * if (date1 == "All" && item == "All") * { * Query = Query; * * * } * if (date1 != "All" && item != "All") * { * Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "' and ITEM_MASTER.I_CODE='" + i_name + "'"; * * } * if (date1 != "All" && item == "All") * { * Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "'"; * * } * if (date1 == "All" && item != "All") * { * Query = Query + " and ITEM_MASTER.I_CODE='" + i_name + "' "; * * } * } #endregion * * * } #endregion * * */ #endregion DataSet ds = new DataSet(); DataTable dt = new DataTable(); dt = CommonClasses.Execute(Query); if (dt.Rows.Count > 0) { ReportDocument rptname = null; rptname = new ReportDocument(); if (group == "Datewise") { rptname.Load(Server.MapPath("~/Reports/rptIssueToProductionDateWise.rpt")); rptname.FileName = Server.MapPath("~/Reports/rptIssueToProductionDateWise.rpt"); rptname.Refresh(); rptname.SetDataSource(dt); //if (way == "AsPerReg") //{ // rptname.SetParameterValue("txtType", "1"); //} //else //{ // rptname.SetParameterValue("txtType", "0"); //} rptname.SetParameterValue("txtCompName", Session["CompanyName"].ToString()); rptname.SetParameterValue("txtPeriod", "From " + From + " to " + To); CrystalReportViewer1.ReportSource = rptname; } if (group == "ItemWise") { rptname.Load(Server.MapPath("~/Reports/rptIssueToProductionItemWise.rpt")); rptname.FileName = Server.MapPath("~/Reports/rptIssueToProductionItemWise.rpt"); //rptname.Load(Server.MapPath("~/Reports/rptQtnRegDatewise.rpt")); //rptname.FileName = Server.MapPath("~/Reports/rptQtnRegDatewise.rpt"); rptname.Refresh(); rptname.SetDataSource(dt); //if (way == "Summary") //{ // rptname.SetParameterValue("txtType", "1"); //} //else //{ // rptname.SetParameterValue("txtType", "0"); //} rptname.SetParameterValue("txtCompName", Session["CompanyName"].ToString()); rptname.SetParameterValue("txtPeriod", "From " + From + " to " + To); CrystalReportViewer1.ReportSource = rptname; } } else { PanelMsg.Visible = true; lblmsg.Text = "Record Not Found"; return; } } catch (Exception Ex) { } }
public ActionResult Print(int Id) { ReportDocument rd = new ReportDocument(); rd.Load(Path.Combine(Server.MapPath("~/Reports"), "CustomerInvoice.rpt")); DataSet ds = new DataSet(); ds.Tables.Add("Head"); ds.Tables.Add("Items"); // //-------HEAD ds.Tables["Head"].Columns.Add("DocumentNo"); ds.Tables["Head"].Columns.Add("DocumentDate"); ds.Tables["Head"].Columns.Add("Location"); ds.Tables["Head"].Columns.Add("InsuredName"); ds.Tables["Head"].Columns.Add("SpecialRemarks"); //-------DT ds.Tables["Items"].Columns.Add("EffectiveDate"); ds.Tables["Items"].Columns.Add("InsuranceType"); ds.Tables["Items"].Columns.Add("PolicyOrEndorseNo"); ds.Tables["Items"].Columns.Add("Company"); ds.Tables["Items"].Columns.Add("Premium"); ds.Tables["Items"].Columns.Add("Remarks"); CustomerInvoiceRepository repo = new CustomerInvoiceRepository(); var Head = repo.GetCustomerInvoiceHdDetails(Id); DataRow dr = ds.Tables["Head"].NewRow(); dr["DocumentNo"] = Head.CusInvoiceRefNo; dr["DocumentDate"] = Head.CusInvoiceDate.Value.ToString("dd-MMM-yyyy"); dr["InsuredName"] = Head.CusName; dr["SpecialRemarks"] = Head.SpecialRemarks; ds.Tables["Head"].Rows.Add(dr); var Items = repo.GetCustomerInvoicePrint(Id); foreach (var item in Items) { var CustomerInvoiceItem = new CustomerInvoiceItem { EffectiveDate = item.EffectiveDate, TranType = item.TranType, PolicyNo = item.PolicyNo, InsCmpName = item.InsCmpName, TotalPremium = item.TotalPremium, Remarks = item.Remarks }; DataRow dri = ds.Tables["Items"].NewRow(); dri["EffectiveDate"] = CustomerInvoiceItem.EffectiveDate.Value.ToString("dd-MMM-yyyy"); dri["InsuranceType"] = CustomerInvoiceItem.TranType; dri["PolicyOrEndorseNo"] = CustomerInvoiceItem.PolicyNo; dri["Company"] = CustomerInvoiceItem.InsCmpName; dri["Premium"] = CustomerInvoiceItem.TotalPremium; dri["Remarks"] = CustomerInvoiceItem.Remarks; ds.Tables["Items"].Rows.Add(dri); } ds.WriteXml(Path.Combine(Server.MapPath("~/XML"), "CustomerInvoice.xml"), XmlWriteMode.WriteSchema); rd.SetDataSource(ds); Response.Buffer = false; Response.ClearContent(); Response.ClearHeaders(); try { Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); stream.Seek(0, SeekOrigin.Begin); return(File(stream, "application/pdf")); } catch (Exception ex) { throw; } }
public void Print(bool Quick, string printerName) { string path = "";// =SystemReports.GetPathReport(StrCode); try { if (Crpt == null && !string.IsNullOrEmpty(StrCode)) { ReportDocument crptDocument = new ReportDocument(); crptDocument.Load(path); Crpt = crptDocument; //GetCrptFromAssembly(StrCode); // Crpt = GetCrptFromAssembly(StrCode); } if (ValidData()) { bool hasSubReport = CheckHasSubReport(Crpt); var oForm = new frmPrintPreview("Phiếu KQ Xét nghiệm", Crpt, true, ReportSourceTable.Rows.Count <= 0 ? false : true); oForm.crptTrinhKyName = Path.GetFileName(path); if (!hasSubReport) { Crpt.SetDataSource(ReportSourceTable); SetReportParameter(Crpt); // var oForm = new frmPrintPreview(FormPreviewTitle, Crpt, true, true); if (SysPara.ParamaterReport != null) { if (SysPara.ParamaterReport == "THANHNHAN") { var patientId = Utility.Int32Dbnull(ReportSourceTable.Rows[0]["Patient_ID"]); var dtAssignDoctor = new Select(TTestInfo.Columns.AssignId, LUser.Columns.UserName).From(TTestInfo.Schema.Name).InnerJoin (LUser.UserIdColumn, TTestInfo.AssignIdColumn).Where(TTestInfo.Columns.PatientId).IsEqualTo(patientId).And(TTestInfo.Columns.TestTypeId).In(testTypeList).ExecuteDataSet().Tables[0]; if (dtAssignDoctor.Rows.Count > 0) { Crpt.SetParameterValue("sBacSyChiDinh", "BÁC SỸ CHỈ ĐỊNH"); Crpt.SetParameterValue("sTenBacSyChiDinh", Utility.sDbnull(dtAssignDoctor.Rows[0]["User_Name"])); } //else //{ // Crpt.SetParameterValue("sBacSyChiDinh", ""); // Crpt.SetParameterValue("sTenBacSyChiDinh", ""); //} var sFullName = new Select(SysUser.Columns.SFullName).From(SysUser.Schema.Name).Where(SysUser.Columns.PkSuid) .IsEqualTo(globalVariables.UserName).ExecuteScalar(); if (!string.IsNullOrEmpty(Utility.sDbnull(sFullName))) { Crpt.SetParameterValue("sTenUser", sFullName); } //else //{ // Crpt.SetParameterValue("sTenUser",""); //} } } oForm.crptViewer.ReportSource = Crpt; // FieldObject mv_oRptFieldObj = Crpt.ReportDefinition.ReportObjects["Field150181"] as FieldObject; // Crpt.PrintToPrinter(); if (Quick) { // mv_oNguoiKy = new VietBaIT.LABLink.Reports.Class.cls_SignInfor(mv_oRptFieldObj, "", Crpt.ToString(), Crpt.DataDefinition.FormulaFields["Formula_1"].Text); // mv_oNguoiKy = new cls_SignInfor(Crpt.ToString(), "", ReportSourceTable); // mv_oNguoiKy = newcls_SignInfor(string.IsNullOrEmpty(oForm.crptTrinhKyName) , Crpt.ToString(),oForm.crptTrinhKyName, "", ""); mv_oNguoiKy = new cls_SignInfor_new(string.IsNullOrEmpty(oForm.crptTrinhKyName) ? oForm.crptTrinhKyName : oForm.crptTrinhKyName, ""); // mv_oNguoiKy = newcls_SignInfor(" ", "", Crpt, ReportSourceTable); //chkPrint_CheckedChanged(chkPrint, New System.EventArgs) if (mv_oNguoiKy._TonTai) { Crpt.DataDefinition.FormulaFields["Formula_1"].Text = Strings.Chr(34) + mv_oNguoiKy.mv_NOI_DUNG.Replace( "&NHANVIEN", globalVariables.UserName) .Replace("\t", Strings.Chr(34) + "&Chr(13)&" + Strings.Chr(34)) + Strings.Chr(34); } else { Crpt.DataDefinition.FormulaFields["Formula_1"].Text = ""; } Crpt.PrintOptions.PrinterName = printerName; Crpt.PrintToPrinter(0, true, 0, 0); Crpt.Dispose(); CleanTemporaryFolders(); } else { oForm.ShowDialog(); oForm.Dispose(); } } else { //DataTable dt = new Select().From(TblTrinhky.Schema.Name).Where(TblTrinhky.Columns.ReportName). // IsEqualTo(Crpt.GetClassName()).ExecuteDataSet().Tables[0]; string sql = string.Format("SELECT * \n" + "FROM Sys_Trinhky With (NOLOCK) \n" + "WHERE ReportName = N'{0}'", oForm.crptTrinhKyName); var x = new InlineQuery().ExecuteAsCollection <SysTrinhkyCollection>(sql); if (x.Count > 0) { Utility.GetDataRow(ReportParameterTable, colName, "Formula_1")[colValue] = Strings.Chr(34) + Utility.sDbnull( x[0] .ObjectContent) + Strings.Chr(34); } Crpt.SetDataSource(ReportSourceTable); SetReportParameter(Crpt); if (!ReportSourceTable.Columns.Contains("ParentBranchName")) { ReportSourceTable.Columns.Add("ParentBranchName"); } if (!ReportSourceTable.Columns.Contains("BranchName")) { ReportSourceTable.Columns.Add("BranchName"); } DataTable dtHematology = ReportSourceTable.Clone(); DataTable dtOther = ReportSourceTable.Clone(); DataTable dtImages = ReportSourceTable.Clone(); string sql2 = string.Format("SELECT Sys_Users.sFullName \n" + "FROM Sys_Users WITH (NOLOCK) \n" + "WHERE Sys_Users.PK_sUID = '{0}'", globalVariables.UserName); var objuserName = new InlineQuery().ExecuteScalar <string>(sql2); string sNguoiDung = objuserName == null ? "" : objuserName; foreach (DataRow row in ReportSourceTable.Rows) { row["ParentBranchName"] = globalVariables.ParentBranch_Name; row["BranchName"] = globalVariables.Branch_Name; row["Nguoidung"] = sNguoiDung; if (row["isHematology"].ToString() == "1") { if (row["Data_Sequence"].ToString().Contains("-")) { string pvSImgPath = row["Test_Result"].ToString(); if (File.Exists(pvSImgPath)) { //row["BarcodeImg"] = Utility.bytGetImage(pvSImgPath); var bitmap = new Bitmap(pvSImgPath); Invert(ref bitmap); row["BarcodeImg"] = Utility.ConvertImageToByteArray(bitmap, ImageFormat.Tiff); dtImages.ImportRow(row); } } else { dtHematology.ImportRow(row); } } else { dtOther.ImportRow(row); } } // đẹt me lam rieng cho noi tiet to su bo no(.) // cho cai kho A5 if (StrCode == "A5") { Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Hematology_Images.rpt" ].SetDataSource(dtImages); Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"] .SetDataSource(dtHematology); Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"] .SetDataSource(dtOther); SetReportParameter( Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"]); SetReportParameter( Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"]); //Crpt.SetParameterValue("PrintDate", printDateTime, Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"].Name); //Crpt.SetParameterValue("PrintDate", printDateTime, Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"].Name); SetReportParameter(Crpt); } // cho cai kho A4 else if (StrCode == "A4") { SetReportParameter( Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"]); SetReportParameter( Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"]); Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology_Images.rpt" ].SetDataSource(dtImages); Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"] .SetDataSource(dtHematology); Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"] .SetDataSource(dtOther); SetReportParameter(Crpt); // Crpt.SetParameterValue("Nguoidung", sNguoiDung); } //neu deo khai bao A4 hay A5 thi mac dinh lay cai nay nhung pai dung cai bao cao ko an cut no chay dc. else if (StrCode == "LABREPORT") { SetReportParameter( Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"]); SetReportParameter( Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"]); Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology_Images.rpt" ].SetDataSource(dtImages); Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"] .SetDataSource(dtHematology); Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"] .SetDataSource(dtOther); SetReportParameter(Crpt); } // if (Quick) Crpt.PrintToPrinter(0, false, 0, 0); if (Quick) { // mv_oNguoiKy = new VietBaIT.LABLink.Reports.Class.cls_SignInfor(mv_oRptFieldObj, "", Crpt.ToString(), Crpt.DataDefinition.FormulaFields["Formula_1"].Text); // mv_oNguoiKy = new cls_SignInfor(Crpt.ToString(), "", ReportSourceTable); // mv_oNguoiKy = newcls_SignInfor(string.IsNullOrEmpty(oForm.crptTrinhKyName) , Crpt.ToString(),oForm.crptTrinhKyName, "", ""); mv_oNguoiKy = new cls_SignInfor_new( string.IsNullOrEmpty(oForm.crptTrinhKyName) ? oForm.crptTrinhKyName : oForm.crptTrinhKyName, ""); // mv_oNguoiKy = newcls_SignInfor(" ", "", Crpt, ReportSourceTable); //chkPrint_CheckedChanged(chkPrint, New System.EventArgs) if (mv_oNguoiKy._TonTai) { Crpt.DataDefinition.FormulaFields["Formula_1"].Text = Strings.Chr(34) + mv_oNguoiKy.mv_NOI_DUNG.Replace( "&NHANVIEN", globalVariables.UserName) .Replace("\t", Strings.Chr(34) + "&Chr(13)&" + Strings.Chr(34)) + Strings.Chr(34); } else { Crpt.DataDefinition.FormulaFields["Formula_1"].Text = ""; } Crpt.PrintOptions.PrinterName = printerName; Crpt.PrintToPrinter(0, true, 0, 0); Crpt.Dispose(); CleanTemporaryFolders(); } else { // var oForm = newfrmPrintPreview("Phiếu KQ Xét nghiệm", Crpt, true, ReportSourceTable.Rows.Count <= 0 ? false : true); // oForm.crptTrinhKyName = Path.GetFileName(path); oForm.ShowDialog(); oForm.Dispose(); } } } } catch (Exception ex) { Utility.ShowMsg(ex.Message); } }
void DoReport() { try { CrystalDecisions.CrystalReports.Engine.ReportDocument oRpt = new ReportDocument(); //c_strRptFileName = ""; //if (MemoryData.c_dic_report.ContainsKey(gv_strRptName.ToUpper()) == true) //{ // // lấy tên file báo cáo // c_strRptFileName = MemoryData.c_dic_report[gv_strRptName.ToUpper()].Rpt_File_Name; //} //else return; c_strRptFileName = "TM_PLB01SDD.rpt"; // lấy thông tin về đơn chung Get_AppHeaderbyId(); Create_Each_Report(gv_strRptName); string mv_strLocation = Server.MapPath("~/Report/") + c_strRptFileName; if (System.IO.File.Exists(mv_strLocation) == false) { ScriptManager.RegisterStartupScript(this, GetType(), "ServerControlScript", "alert(\"Không tồn tại File báo cáo!\");", true); return; } // load rpt oRpt.Load(mv_strLocation); if (mv_dsData != null) { oRpt.SetDataSource(mv_dsData); } // tạo mới báo cáo . oRpt.Refresh(); // Truyền các tham số Formular vào báo cáo Puth_Data2_Fomulator(ref oRpt); DiskFileDestinationOptions crDiskFileDestinationOptions = new DiskFileDestinationOptions(); ExportOptions crExportOptions = oRpt.ExportOptions; crExportOptions.DestinationOptions = crDiskFileDestinationOptions; crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; crExportOptions.FormatOptions = new PdfFormatOptions(); // xoay dọc hay ngang if (cboOrientation.SelectedValue.ToString() == "1") { oRpt.PrintOptions.PaperOrientation = PaperOrientation.Portrait; } else { oRpt.PrintOptions.PaperOrientation = PaperOrientation.Landscape; } // Kết xuất không dùng chữ ký string _FileName = gv_strRptName + "_" + DateTime.Now.ToString("yyyyMMdd"); Export_PDF_Non_Signal(oRpt, _FileName); //// View báo cáo //rptViewer.ReportSource = oRpt; //rptViewer.ShowFirstPage(); //// phần này lâu rồi ko nhớ để làm gì //if (SessionData.GetDataSession("oRpt") == null) //{ // Hashtable _hs_opt_khoitao = new Hashtable(); // SessionData.SetDataSession("oRpt", _hs_opt_khoitao); //} //Hashtable _hs_opt = (Hashtable)SessionData.GetDataSession("oRpt"); //Hashtable _hs_tem = _hs_opt; //_hs_tem[gv_strRptName] = oRpt; //SessionData.SetDataSession("oRpt", _hs_tem); } catch (CrystalDecisions.CrystalReports.Engine.LogOnException ex1) { rptViewer.Dispose(); Logger.Log().Error(ex1.ToString()); } catch (Exception ex) { rptViewer.Dispose(); Logger.LogException(ex); } }
protected void Page_Load(object sender, EventArgs e) { try { if (Session["ReportData"] != null && Session["type"] != null) { string rptFullPath = ""; if (Session["type"].ToString() == "bridge") { rptFullPath = Server.MapPath(@".\RptFiles\Descriptive\rptBridges.rpt"); } else if (Session["type"].ToString() == "tunnel") { rptFullPath = Server.MapPath(@".\RptFiles\Descriptive\rptTunnels.rpt"); } DataTable dt = (DataTable)Session["ReportData"]; ReportDocument rpt = new ReportDocument(); rpt.Load(rptFullPath); rpt.SetDataSource(dt); Session.Remove("ReportData"); Stream memStream; Response.Buffer = false; Response.Clear(); Response.ClearContent(); Response.ClearHeaders(); if (Request.QueryString["type"] == "x") { ExcelFormatOptions excelOptions = new ExcelFormatOptions(); excelOptions.ExcelUseConstantColumnWidth = false; rpt.ExportOptions.FormatOptions = excelOptions; memStream = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.ExcelRecord); Response.ContentType = "application/vnd.ms-excel"; } else if (Request.QueryString["type"] == "w") { memStream = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.WordForWindows); Response.ContentType = "application/doc"; } else { memStream = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); Response.ContentType = "application/pdf"; //memStream = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.WordForWindows); //Response.ContentType = "application/msword"; } byte[] ArryStream = new byte[memStream.Length + 1]; memStream.Read(ArryStream, 0, System.Convert.ToInt32(memStream.Length)); Response.BinaryWrite(ArryStream); Response.End(); memStream.Flush(); memStream.Close(); memStream.Dispose(); rpt.Close(); rpt.Dispose(); GC.Collect(); } else { Response.Redirect("ReportSectionsInfo.aspx", false); } } catch (Exception ex) { Response.Write(ex.Message); } finally { Session["ReportData"] = null; } }
private void btnSelect_Click(object sender, EventArgs e) { //MessageBox.Show("shuangji"); if (this.txtYear.Text.Trim() == "" || this.txtMonth.Text.Trim() == "" || this.txtFcurnumber.Text.Trim() == "") { MessageBox.Show("请输入查询年份和期间以及客户代码!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } ds = k3db.GetDataSet("SELECT fitemid FROM t_Organization WHERE FNumber='" + this.txtFcurnumber.Text.Trim() + "'", "cur"); if (ds.Tables[0].Rows.Count != 1) { MessageBox.Show("请输入准确并且存在的客户代码!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } string strReportPath = Application.StartupPath; // Application.StartupPath.Substring(0, Application.StartupPath.Substring(0, // Application.StartupPath.LastIndexOf("\\")).LastIndexOf("\\")); //strReportPath += @"\RP\RPT\" + strReportFileName; strReportPath += @"\RPT\dzpyxtj.rpt"; //得到dt数据源 SqlParameter param1 = new SqlParameter("@year", SqlDbType.VarChar); param1.Value = this.txtYear.Text.Trim(); SqlParameter param2 = new SqlParameter("@month", SqlDbType.VarChar); param2.Value = this.txtMonth.Text.Trim(); SqlParameter param3 = new SqlParameter("@fcurnumber", SqlDbType.VarChar); param3.Value = this.txtFcurnumber.Text.Trim(); //创建泛型 List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(param1); parameters.Add(param2); parameters.Add(param3); //把泛型中的元素复制到数组中 SqlParameter[] inputParameters = parameters.ToArray(); DataTable dt = k3db.GetDataTable("sp_DzpYsTJ_one", inputParameters); //ReportDocument对象加载rpt文件并绑定到数据源dt //明细 ReportDocument reportDoc = new ReportDocument(); reportDoc.Load(strReportPath); reportDoc.SetDataSource(dt.DefaultView); //DataView是接口IEnumerable的实现子类,此处使用了“接口”的多态特性 // crystalReportViewer1.ReportSource = reportDoc; //对账单 string strReportPath2 = Application.StartupPath; strReportPath2 += @"\RPT\dzpysdzd.rpt"; //dzpysdzd.rpt //SELECT '林水凉' AS 客户名称, 'AAAA' 片区 '2014-9' AS 期间,'2014-09-30 '截止日期,-4037.35 期初余额,0 本期借方,10000 本期贷方,-6549.3 期末余额 string Sqlstr = " select '" + dt.Rows[0]["客户名称"].ToString() + "' AS 客户名称, '" + dt.Rows[0]["片区"].ToString() + "' 片区,'" + dt.Rows[0]["年份"].ToString() + "-" + dt.Rows[0]["期间"].ToString() + "' AS 期间,'" + dt.Rows[0]["年份"].ToString() + "-" + dt.Rows[0]["期间"].ToString() + "-" + dt.Rows[dt.Rows.Count - 2]["日期"].ToString() + "' as 截止日期," + dt.Rows[dt.Rows.Count - 1]["期初余额"].ToString() + " as 期初余额," + dt.Rows[dt.Rows.Count - 1]["出货"].ToString() + "-(" + dt.Rows[dt.Rows.Count - 1]["退货"].ToString() + ") as 本期借方," + dt.Rows[dt.Rows.Count - 1]["回款"].ToString().Trim() + " as 本期贷方," + dt.Rows[dt.Rows.Count - 1]["期末余额"].ToString() + " as 期末余额 "; // " 0 as 期末余额 "; DataTable dtdzd = k3db.GetDataTable(Sqlstr, "tab"); ReportDocument reportDoc2 = new ReportDocument(); reportDoc2.Load(strReportPath2); reportDoc2.SetDataSource(dtdzd.DefaultView); //DataView是接口IEnumerable的实现子类,此处使用了“接口”的多态特性 crystalReportViewer2.ReportSource = reportDoc2; }
public ActionResult CRInvoice(int id) { //Lijsten maken en vullen List <SingleInvoiceCRViewModel> CRSingleInvoiceListVM = new List <SingleInvoiceCRViewModel>(); SingleInvoiceCRViewModel CRSO = new SingleInvoiceCRViewModel(); Invoice invoice = db.Invoices.Find(id); Company company = db.Companies.Find(1); Customer cus = db.Customers.Find(invoice.CustomerId); //vulling Header info //company CRSO.BIC = company.BIC; CRSO.CompanyId = company.CompanyId; CRSO.CompanyName = company.CompanyName; CRSO.Country = company.Country; CRSO.District = company.District; CRSO.EmailCompany = company.Email; CRSO.Iban = company.Iban; CRSO.Mobile = company.Mobile; CRSO.Phone = company.Phone; CRSO.Postalcode = company.Postalcode; CRSO.Street = company.Street; CRSO.VatNumber = company.VatNumber; CRSO.Website = company.Website; //quotation CRSO.Annotation = invoice.Annotation; CRSO.Box = invoice.Box; CRSO.CellPhone = invoice.CellPhone; CRSO.CustomerId = invoice.CustomerId; CRSO.Date = invoice.Date; CRSO.EmailCustomer = invoice.Email; CRSO.FirstName = invoice.FirstName; CRSO.LastName = invoice.LastName; CRSO.PostalCodeNumber = invoice.PostalCodeNumber; CRSO.InvoiceID = invoice.InvoiceId; CRSO.InvoiceNumber = invoice.InvoiceNumber; CRSO.StreetName = invoice.StreetName; CRSO.StreetNumber = invoice.StreetNumber; CRSO.TotalPrice = invoice.TotalPrice; CRSO.Town = invoice.Town; CRSO.VATnumberCustomer = cus.VATNumber; //customer contact info CRSO.ContactCellPhone = cus.ContactCellPhone; CRSO.ContactEmail = cus.ContactEmail; CRSO.ContactName = cus.ContactName; //delivery info CRSO.DeliveryAddressInfo = invoice.customerDeliveryAddress.DeliveryAddressInfo; CRSO.PostalCodeNumberTown = invoice.customerDeliveryAddress.PostalCodeNumber + " " + invoice.customerDeliveryAddress.Town; CRSO.StreetNameNumberBox = invoice.customerDeliveryAddress.StreetName + " " + invoice.customerDeliveryAddress.StreetNumber + " " + invoice.customerDeliveryAddress.Box; //vulling details info foreach (var item in invoice.InvoiceDetail) { CRSO.ProductCode = item.ProductCode; CRSO.PriceExVAT = item.PriceExVAT; CRSO.Quantity = item.Quantity; CRSO.Description = item.Description; CRSO.VATValue = Convert.ToInt16(item.VAT.VATValue); CRSO.Auvibel = item.Auvibel; CRSO.Recupel = item.Recupel; CRSO.Reprobel = item.Reprobel; CRSO.Bebat = item.Bebat; CRSO.ProductCode = item.ProductCode; CRSO.ProductName = item.ProductName; CRSingleInvoiceListVM.Add(CRSO); SingleInvoiceCRViewModel empty = new SingleInvoiceCRViewModel(); CRSO = empty; } ReportDocument rd = new ReportDocument(); rd.Load(Path.Combine(Server.MapPath("~/Reports/Invoice"), "SingleInvoice.rpt")); rd.SetDataSource(CRSingleInvoiceListVM); Response.Buffer = false; Response.ClearContent(); Response.ClearHeaders(); try { Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); stream.Seek(0, SeekOrigin.Begin); string pdfName = "Factuur - " + invoice.InvoiceNumber + " - " + invoice.FullName.ToString() + ".pdf"; return(File(stream, "application/pdf", pdfName)); } catch (Exception ex) { if (ex.Data == null) { throw; } else { throw; } } }
public void LoadReport() { System.IO.MemoryStream stream1 = new System.IO.MemoryStream(); Processing p = new Processing(); try { if (Request.QueryString["Type"] == "S") { string Department = Request.QueryString["Department"].ToString(); string Semester = Request.QueryString["Semester"].ToString(); string Year = Request.QueryString["Year"].ToString(); string ItemType = Request.QueryString["ItemType"].ToString(); string Path = Server.MapPath("CalenderAuditSummary.rpt"); Path = Path.Substring(0, Path.LastIndexOf('\\')); Path = Path.Substring(0, Path.LastIndexOf('\\')); Path = Path + "\\Reports\\CalenderAuditSummary.rpt"; myReportDocument.Load(Path); //myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea", "192.168.167.207", "DB_SkylineCalendarEvents"); myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea"); myReportDocument.SetParameterValue("@department", Department); myReportDocument.SetParameterValue("@semester", Semester); myReportDocument.SetParameterValue("@year", Year); myReportDocument.SetParameterValue("@ItemType", ItemType); } else if (Request.QueryString["Type"] == "C") { string Department = Request.QueryString["Department"].ToString(); string Semester = Request.QueryString["Semester"].ToString(); string Year = Request.QueryString["Year"].ToString(); string ItemType = Request.QueryString["ItemType"].ToString(); string Path = Server.MapPath("CalenderauditCummulative.rpt"); Path = Path.Substring(0, Path.LastIndexOf('\\')); Path = Path.Substring(0, Path.LastIndexOf('\\')); Path = Path + "\\Reports\\CalenderauditCummulative.rpt"; myReportDocument.Load(Path); //myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea", "192.168.167.207", "DB_SkylineCalendarEvents"); myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea"); myReportDocument.SetParameterValue("@department", Department); myReportDocument.SetParameterValue("@semester", Semester); myReportDocument.SetParameterValue("@year", Year); myReportDocument.SetParameterValue("@ItemType", ItemType); } else { string Department = Request.QueryString["Department"].ToString(); string Semester = Request.QueryString["Semester"].ToString(); string Year = Request.QueryString["Year"].ToString(); string ItemType = Request.QueryString["ItemType"].ToString(); string Path = Server.MapPath("calendarFeedback.rpt"); Path = Path.Substring(0, Path.LastIndexOf('\\')); Path = Path.Substring(0, Path.LastIndexOf('\\')); Path = Path + "\\Reports\\calendarFeedback.rpt"; myReportDocument.Load(Path); //myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea", "192.168.167.207", "DB_SkylineCalendarEvents"); myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea"); myReportDocument.SetParameterValue("@department", Department); myReportDocument.SetParameterValue("@semester", Semester); myReportDocument.SetParameterValue("@year", Year); myReportDocument.SetParameterValue("@ItemType", ItemType); } System.IO.Stream oStream = null; byte[] byteArray = null; oStream = myReportDocument.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); byteArray = new byte[oStream.Length]; oStream.Read(byteArray, 0, Convert.ToInt32(oStream.Length - 1)); Response.ClearContent(); Response.ClearHeaders(); Response.ContentType = "application/pdf"; Response.BinaryWrite(byteArray); Response.Flush(); Response.Close(); myReportDocument.Close(); myReportDocument.Dispose(); } catch (Exception Ex) { //LogFile L = new LogFile(); //L.LogError("CalendarAuditSummaryPrintReport.aspx"); //L.LogError(Ex.Message.ToString()); //L.LogError(Session["User"].ToString()); //Response.Write(Ex.Message); } }
public ActionResult CRAllQuotations(int?id) { List <Quotation> allQuotations = new List <Quotation>(); allQuotations = db.Quotations.ToList(); List <Company> company = new List <Company>(); company = db.Companies.ToList(); List <AllQuotationsCRViewModel> allQuotationsCR = new List <AllQuotationsCRViewModel>(); foreach (var item in allQuotations) { var allQuotationCR = new AllQuotationsCRViewModel(); allQuotationCR.QuotationId = item.QuotationId; allQuotationCR.QuotationNumber = item.QuotationNumber; allQuotationCR.Active = item.Active; allQuotationCR.ExpirationDate = item.ExpirationDate; allQuotationCR.Date = item.Date; allQuotationCR.CustomerId = item.CustomerId; allQuotationCR.Annotation = item.Annotation; allQuotationCR.FirstName = item.FirstName; allQuotationCR.LastName = item.LastName; allQuotationCR.StreetName = item.StreetName; allQuotationCR.StreetNumber = item.StreetNumber; allQuotationCR.Town = item.Town; allQuotationCR.PostalCodeNumber = item.PostalCodeNumber; allQuotationCR.Box = item.Box; allQuotationCR.TotalPrice = item.TotalPrice; allQuotationsCR.Add(allQuotationCR); } ReportDocument rd = new ReportDocument(); rd.Load(Path.Combine(Server.MapPath("~/Reports"), "AllQuotationsMain.rpt")); rd.OpenSubreport("Header.rpt").SetDataSource(company); rd.OpenSubreport("allQuotationsSub.rpt").SetDataSource(allQuotationsCR); //rd.SetDataSource(company); Response.Buffer = false; Response.ClearContent(); Response.ClearHeaders(); try { Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); stream.Seek(0, SeekOrigin.Begin); return(File(stream, "application/pdf", "All_Quotations.pdf")); } catch (Exception ex) { if (ex.Data == null) { throw; } else { throw; } } }
override protected void OnInit(EventArgs e) { InitializeComponent(); base.OnInit(e); try { int int_pinicio; int int_pfin; string strServer; string strDataBase; string strUid; string strPwd; string pf = "ctl00$CPHC$"; // pf = ""; // obtengo de la cadena de conexión los parámetros para luego // modificar localizaciones string strconexion = Utilidades.CadenaConexion; int_pfin = strconexion.IndexOf(";database=", 0); strServer = strconexion.Substring(7, int_pfin - 7); int_pinicio = int_pfin + 10; int_pfin = strconexion.IndexOf(";uid=", int_pinicio); strDataBase = strconexion.Substring(int_pinicio, int_pfin - int_pinicio); int_pinicio = int_pfin + 5; int_pfin = strconexion.IndexOf(";pwd=", int_pinicio); strUid = strconexion.Substring(int_pinicio, int_pfin - int_pinicio); int_pinicio = int_pfin + 5; int_pfin = strconexion.IndexOf(";Trusted_Connection=", int_pinicio); strPwd = strconexion.Substring(int_pinicio, int_pfin - int_pinicio); //creo un objeto ReportDocument ReportDocument rdInforme = new ReportDocument(); try { rdInforme.Load(Server.MapPath(".") + @"\sup_parteactividad_mod_b.rpt"); } catch (Exception ex) { rdInforme.Close(); rdInforme.Dispose(); Response.Write("Error al abrir el report: " + ex.Message); } try { rdInforme.SetDatabaseLogon(strUid, strPwd, strServer, strDataBase); } catch (Exception ex) { rdInforme.Close(); rdInforme.Dispose(); Response.Write("Error al logarse al report: " + ex.Message); } //creo un objeto logon . CrystalDecisions.Shared.TableLogOnInfo tliCurrent; try { foreach (CrystalDecisions.CrystalReports.Engine.Table tbCurrent in rdInforme.Database.Tables) { //obtengo el logon por tabla tliCurrent = tbCurrent.LogOnInfo; tliCurrent.ConnectionInfo.DatabaseName = strDataBase; tliCurrent.ConnectionInfo.UserID = strUid; tliCurrent.ConnectionInfo.Password = strPwd; tliCurrent.ConnectionInfo.ServerName = strServer; //aplico los cambios hechos al objeto TableLogonInfo tbCurrent.ApplyLogOnInfo(tliCurrent); } } catch (Exception ex) { rdInforme.Close(); rdInforme.Dispose(); Response.Write("Error al actualizar la localización: " + ex.Message); } bool bReducida = false; string strTipoFormato = ""; if (Request.Form["FORMATO"] != null)//Por si se llama desde la versión reducida (sin Máster) { bReducida = true; strTipoFormato = Request.Form["FORMATO"]; } else { strTipoFormato = Request.Form[pf + "FORMATO"]; // ESTO LO RECOGERE COMO PARAMETRO } strControl = strTipoFormato; DiskFileDestinationOptions diskOpts = ExportOptions.CreateDiskFileDestinationOptions(); ExportOptions exportOpts = new ExportOptions(); try { if (!bReducida) { rdInforme.SetParameterValue("@sConsumos", Request.Form[pf + "hdnConsumos"]); } else { rdInforme.SetParameterValue("@sConsumos", Request.Form["hdnConsumos"]); } } catch (Exception ex) { rdInforme.Close(); rdInforme.Dispose(); Response.Write("Error al actualizar los parámetros del report: " + ex.Message); } try { System.IO.Stream oStream; byte[] byteArray = null; //switch (strTipoFormato) //{ // // PDF // case "PDF": oStream = rdInforme.ExportToStream(ExportFormatType.PortableDocFormat); byteArray = new byte[oStream.Length]; oStream.Read(byteArray, 0, Convert.ToInt32(oStream.Length - 1)); // break; // case "EXC": // oStream = rdInforme.ExportToStream(ExportFormatType.Excel); // byteArray = new byte[oStream.Length]; // oStream.Read(byteArray, 0, Convert.ToInt32(oStream.Length - 1)); // break; // case "EXC2": // // // rdInforme.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "Exportacion"); // return; // break; //} // FIN Response.Clear(); Response.ClearContent(); Response.ClearHeaders(); //String nav = HttpContext.Current.Request.Browser.Browser.ToString(); //if (nav.IndexOf("IE") == -1) //{ switch (strTipoFormato) { case "PDF": Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", "Filename.pdf")); break; case "EXC": Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", "Filename.xls")); break; } //} switch (strTipoFormato) { // PDF case "PDF": Response.ContentType = "application/pdf"; break; case "EXC": // EXCEL Response.ContentType = "application/xls"; break; } Response.BinaryWrite(byteArray); //Response.Flush(); //Response.Close(); //HttpContext.Current.Response.End(); Response.End(); } catch (Exception ex) { rdInforme.Close(); rdInforme.Dispose(); Response.Write("Error al exportar el report: " + ex.Message); } } //handle any exceptions catch (Exception ex) { Response.Write("No se puede crear el report: " + ex.Message); } }
public ActionResult Print(string Itmid = "", string PartNo = "") { ReportDocument rd = new ReportDocument(); rd.Load(Path.Combine(Server.MapPath("~/Reports"), "GINRegister.rpt")); DataSet ds = new DataSet(); ds.Tables.Add("Head"); ds.Tables.Add("Items"); //-------HEAD ds.Tables["Head"].Columns.Add("Item"); ds.Tables["Head"].Columns.Add("OrganizationName"); ds.Tables["Head"].Columns.Add("Image1"); ds.Tables["Head"].Columns.Add("PartNo"); //-------DT ds.Tables["Items"].Columns.Add("MaterialCode"); ds.Tables["Items"].Columns.Add("Material"); ds.Tables["Items"].Columns.Add("PartNo"); ds.Tables["Items"].Columns.Add("Stock"); ds.Tables["Items"].Columns.Add("Unit"); ds.Tables["Items"].Columns.Add("WR.No"); ds.Tables["Items"].Columns.Add("RequiredQty"); ds.Tables["Items"].Columns.Add("IssuedQty"); ds.Tables["Items"].Columns.Add("BalanceQty"); OrganizationRepository repo = new OrganizationRepository(); var Head = repo.GetOrganization(OrganizationId); DataRow dr = ds.Tables["Head"].NewRow(); dr["Item"] = Itmid; dr["OrganizationName"] = Head.OrganizationName; dr["Image1"] = Server.MapPath("~/App_images/") + Head.Image1; dr["PartNo"] = PartNo; ds.Tables["Head"].Rows.Add(dr); SalesRegisterRepository repo1 = new SalesRegisterRepository(); //var Items = repo1.GetSOVarianceDataDTPrint(from, to, itmid, itmName, SupId, SupName); var Items = repo1.GetGINRegisterDataDetailsPrint(id: Itmid, OrganizationId: OrganizationId, partno: PartNo); foreach (var item in Items) { var SupplyOrderRegItem = new GINRegister { ItemRefNo = item.ItemRefNo, ItemName = item.ItemName, PartNo = item.PartNo, STOCK = item.STOCK, UnitName = item.UnitName, WorkShopRequestRefNo = item.WorkShopRequestRefNo, Quantity = item.Quantity, ISSQTY = item.ISSQTY, BALQTY = item.BALQTY, }; DataRow dri = ds.Tables["Items"].NewRow(); dri["MaterialCode"] = SupplyOrderRegItem.ItemRefNo; dri["Material"] = SupplyOrderRegItem.ItemName; dri["PartNo"] = SupplyOrderRegItem.PartNo; dri["Stock"] = SupplyOrderRegItem.STOCK; dri["Unit"] = SupplyOrderRegItem.UnitName; dri["WR.No"] = SupplyOrderRegItem.WorkShopRequestRefNo; dri["RequiredQty"] = SupplyOrderRegItem.Quantity; dri["IssuedQty"] = SupplyOrderRegItem.ISSQTY; dri["BalanceQty"] = SupplyOrderRegItem.BALQTY; ds.Tables["Items"].Rows.Add(dri); } ds.WriteXml(Path.Combine(Server.MapPath("~/XML"), "GINRegister.xml"), XmlWriteMode.WriteSchema); rd.SetDataSource(ds); Response.Buffer = false; Response.ClearContent(); Response.ClearHeaders(); try { Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); stream.Seek(0, SeekOrigin.Begin); return(File(stream, "application/pdf", String.Format("GINRegister.pdf"))); } catch (Exception ex) { throw; } }
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) { IMPRESION_REPORTES VEN = new IMPRESION_REPORTES(); ReportParameter[] parametros = new ReportParameter[2]; if (comboBox1.Text.Equals("LIQUIDOS - JARABE")) { timer1.Start(); VISRO frm = new VISRO(); //formulario con CrystalReportViewer ReportDocument rpt = new ReportDocument(); //rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\LIQJARA.rpt"); rpt.Load("C:/SI_Gestion/REPORTES/LIQJARA.rpt"); DataSet ds = new DataSet(); DataTable dt_c = new DataTable(); //ResultSet de DB dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value)); ds.Tables.Add(dt_c); rpt.Database.Tables[0].SetDataSource(ds.Tables[0]); rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString()); rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString()); ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt; ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false; frm.Refresh(); //se refresca timer1.Stop(); progressBar1.Visible = false; progressBar1.Value = 0; //rpt.PrintOptions.PrinterName = "PRIMER_PISO"; //rpt.PrintToPrinter(1, false, 0, 0); //se imprime frm.Text = "IMPRESION DE FORMATOS DE INSPECCION"; frm.Show(); } else if (comboBox1.Text.Equals("LIQUIDOS - AMPOLLA")) { timer1.Start(); VISRO frm = new VISRO(); //formulario con CrystalReportViewer ReportDocument rpt = new ReportDocument(); //rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\LIQAMPOLLAS.rpt"); rpt.Load("C:/SI_Gestion/REPORTES/LIQAMPOLLAS.rpt"); DataSet ds = new DataSet(); DataTable dt_c = new DataTable(); //ResultSet de DB dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value)); ds.Tables.Add(dt_c); rpt.Database.Tables[0].SetDataSource(ds.Tables[0]); rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString()); rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString()); ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt; ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false; frm.Refresh(); //se refresca timer1.Stop(); progressBar1.Visible = false; progressBar1.Value = 0; //rpt.PrintOptions.PrinterName = "PRIMER_PISO"; //rpt.PrintToPrinter(1, false, 0, 0); //se imprime frm.Text = "IMPRESION DE FORMATOS DE INSPECCION"; frm.Show(); } else if (comboBox1.Text.Equals("LIQUIDOS - PRODUCTOS SANITARIOS")) { timer1.Start(); VISRO frm = new VISRO(); //formulario con CrystalReportViewer ReportDocument rpt = new ReportDocument(); //rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\LIQPROSANIT.rpt"); rpt.Load("C:/SI_Gestion/REPORTES/LIQPROSANIT.rpt"); DataSet ds = new DataSet(); DataTable dt_c = new DataTable(); //ResultSet de DB dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value)); ds.Tables.Add(dt_c); rpt.Database.Tables[0].SetDataSource(ds.Tables[0]); rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString()); rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString()); ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt; ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false; frm.Refresh(); //se refresca timer1.Stop(); progressBar1.Visible = false; progressBar1.Value = 0; //rpt.PrintOptions.PrinterName = "PRIMER_PISO"; //rpt.PrintToPrinter(1, false, 0, 0); //se imprime frm.Text = "IMPRESION DE FORMATOS DE INSPECCION"; frm.Show(); } else if (comboBox1.Text.Equals("SOLIDOS - TABLETAS")) { timer1.Start(); VISRO frm = new VISRO(); //formulario con CrystalReportViewer ReportDocument rpt = new ReportDocument(); //rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\SOLTAB.rpt"); rpt.Load("C:/SI_Gestion/REPORTES/SOLTAB.rpt"); DataSet ds = new DataSet(); DataTable dt_c = new DataTable(); //ResultSet de DB dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value)); ds.Tables.Add(dt_c); rpt.Database.Tables[0].SetDataSource(ds.Tables[0]); rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString()); rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString()); ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt; ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false; frm.Refresh(); //se refresca timer1.Stop(); progressBar1.Visible = false; progressBar1.Value = 0; //rpt.PrintOptions.PrinterName = "PRIMER_PISO"; //rpt.PrintToPrinter(1, false, 0, 0); //se imprime frm.Text = "IMPRESION DE FORMATOS DE INSPECCION"; frm.Show(); } else if (comboBox1.Text.Equals("SOLIDOS - OVULOS Y CREMAS")) { timer1.Start(); VISRO frm = new VISRO(); //formulario con CrystalReportViewer ReportDocument rpt = new ReportDocument(); //rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\SOLOVCREMA.rpt"); rpt.Load("C:/SI_Gestion/REPORTES/SOLOVCREMA.rpt"); DataSet ds = new DataSet(); DataTable dt_c = new DataTable(); //ResultSet de DB dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value)); ds.Tables.Add(dt_c); rpt.Database.Tables[0].SetDataSource(ds.Tables[0]); rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString()); rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString()); ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt; ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false; frm.Refresh(); //se refresca timer1.Stop(); progressBar1.Visible = false; progressBar1.Value = 0; //rpt.PrintOptions.PrinterName = "PRIMER_PISO"; //rpt.PrintToPrinter(1, false, 0, 0); //se imprime frm.Text = "IMPRESION DE FORMATOS DE INSPECCION"; frm.Show(); } else if (comboBox1.Text.Equals("SOLIDOS - VIALES")) { timer1.Start(); VISRO frm = new VISRO(); //formulario con CrystalReportViewer ReportDocument rpt = new ReportDocument(); //rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\SOLTVIALES.rpt"); rpt.Load("C:/SI_Gestion/REPORTES/SOLTVIALES.rpt"); DataSet ds = new DataSet(); DataTable dt_c = new DataTable(); //ResultSet de DB dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value)); ds.Tables.Add(dt_c); rpt.Database.Tables[0].SetDataSource(ds.Tables[0]); rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString()); rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString()); ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt; ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false; frm.Refresh(); //se refresca timer1.Stop(); progressBar1.Visible = false; progressBar1.Value = 0; //rpt.PrintOptions.PrinterName = "PRIMER_PISO"; //rpt.PrintToPrinter(1, false, 0, 0); //se imprime frm.Text = "IMPRESION DE FORMATOS DE INSPECCION"; frm.Show(); } else { timer1.Start(); VISRO frm = new VISRO(); //formulario con CrystalReportViewer ReportDocument rpt = new ReportDocument(); //rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\SOLCAP.rpt"); rpt.Load("C:/SI_Gestion/REPORTES/SOLCAP.rpt"); DataSet ds = new DataSet(); DataTable dt_c = new DataTable(); //ResultSet de DB dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value)); ds.Tables.Add(dt_c); rpt.Database.Tables[0].SetDataSource(ds.Tables[0]); rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString()); rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString()); ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt; ((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false; frm.Refresh(); //se refresca timer1.Stop(); progressBar1.Visible = false; progressBar1.Value = 0; //rpt.PrintOptions.PrinterName = "PRIMER_PISO"; //rpt.PrintToPrinter(1, false, 0, 0); //se imprime frm.Text = "IMPRESION DE FORMATOS DE INSPECCION"; frm.Show(); } }
private void PrintDeptCodeClickHandler(object sender, EventArgs e) { //RptDept rptDeptNameList = new RptDept(); //rptDeptNameList.WindowState = FormWindowState.Maximized; //rptDeptNameList.rptName = "DeptCode"; //rptDeptNameList.rptLabel = "DEPARTMENTS REFERENCE LIST SORTED BY CODE"; //rptDeptNameList.Show(); string rpt = ""; string strServer = "PSSQL01"; string strDBName = "PTS"; ReportDocument crDoc = new ReportDocument(); ConnectionInfo crConnectionInfo = new ConnectionInfo(); TableLogOnInfos crtableLogoninfos = new TableLogOnInfos(); TableLogOnInfo crtableLogoninfo = new TableLogOnInfo(); rpt = @"\\gblnj4\GIS\Reports\" + "DepartmentNames.rpt"; //rpt = Application.StartupPath + @"\Reports\" + "DepartmentsRef.rpt"; crConnectionInfo.Type = ConnectionInfoType.SQL; crConnectionInfo.ServerName = strServer; crConnectionInfo.DatabaseName = strDBName; crConnectionInfo.IntegratedSecurity = true; crtableLogoninfo.ConnectionInfo = crConnectionInfo; crDoc.Load(rpt); DataTable dt = new DataTable(); if (rptName == "DeptCode") { dt = PSSClass.Departments.DepartmentsMaster(1); } else { dt = PSSClass.Departments.DepartmentsMaster(2); } crDoc.Load(rpt); crDoc.SetDataSource(dt); ////Open the PrintDialog //this.printDialog1.Document = this.printDocument1; //DialogResult dr = this.printDialog1.ShowDialog(); //if (dr == DialogResult.OK) //{ // //Get the Copy times // int nCopy = this.printDocument1.PrinterSettings.Copies; // //Get the number of Start Page // int sPage = this.printDocument1.PrinterSettings.FromPage; // //Get the number of End Page // int ePage = this.printDocument1.PrinterSettings.ToPage; // //Get the printer name // string PrinterName = this.printDocument1.PrinterSettings.PrinterName; // try // { // //Set the printer name to print the report to. By default the sample // //report does not have a defult printer specified. This will tell the // //engine to use the specified printer to print the report. Print out // //a test page (from Printer properties) to get the correct value. // crDoc.PrintOptions.PrinterName = PrinterName; // //Start the printing process. Provide details of the print job // //using the arguments. // crDoc.PrintToPrinter(nCopy, false, sPage, ePage); // } // catch (Exception err) // { // MessageBox.Show(err.ToString()); // } //} crDoc.PrintOptions.PrinterName = PSSClass.Users.UserPrinterName(LogIn.nUserID); //@"\\it5\46 IT Brother Printer";// @"\\it5\46 Brother FAX"; crDoc.PrintToPrinter(1, false, 0, 0); //crDoc.PrintOptions.PrinterName = @"\\it5\46 IT Brother Printer"; }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { //记录页面使用次数 DB.RecordPageUseCount("Plant_BLOOMCOSTCON"); date1 = "1910-01"; newdate = false; int CurrentYear = Convert.ToInt32(DateTime.Now.Year); int i; for (i = CurrentYear - 5; i <= CurrentYear; i++) { ListItem Year = new ListItem(); Year.Text = Convert.ToString(i); Year.Value = Convert.ToString(i); if (i == CurrentYear) { Year.Selected = true; } else { Year.Selected = false; } ddlYear.Items.Add(Year); //EndYear.Items.Add(Year); } string CurrentDate = Convert.ToString(DateTime.Now.Month); if (CurrentDate.Length == 1) { CurrentDate = "0" + CurrentDate; } foreach (ListItem Month in ddlMonth.Items) { if (Month.Text == CurrentDate) { Month.Selected = true; ddlMonth.Text = Month.Text; //EndMonth.Text = Month.Text; break; } else { Month.Selected = false; } } AddDate(sender, e); CurrentDate = Convert.ToString(DateTime.Now.Day); if (CurrentDate.Length == 1) { CurrentDate = "0" + CurrentDate; } foreach (ListItem day in ddlDay.Items) { if (day.Text == CurrentDate) { day.Selected = true; ddlDay.Text = day.Text; //EndDay.Text = day.Text; break; } else { day.Selected = false; } } } else { date1 = ddlYear.SelectedValue.ToString() + '-' + ddlMonth.SelectedValue.ToString() + '-' + ddlDay.SelectedValue.ToString(); string strCurrentDate = date1; OracleConnection conn = DB.createConn(); try { OracleDataAdapter da = new OracleDataAdapter(); if (newdate) { CrystalReportViewer1.ResetReportPartNavigation(); newdate = false; } conn.Open(); DataSet ds = new DataSet(); string strSQL = "Select * from TP_BLOOM_COST_CON where LOGTIME='" + strCurrentDate + "'AND RPTID='" + ddlBloomCostcon.SelectedValue + "' ORDER BY mat_order"; da.SelectCommand = new OracleCommand(strSQL, conn); da.Fill(ds, "TP_BLOOM_COST_CON"); rdt.Load(this.Server.MapPath("").ToString() + "\\BLOOMCOSTCON.rpt"); rdt.SetDataSource(ds); CrystalReportViewer1.ReportSource = rdt; CrystalReportViewer1.DataBind(); ds.Dispose(); } catch (Exception ee) { Response.Write(ee.ToString());//"出错"); } finally { conn.Close(); } } }
/// <summary> /// Init_pages this instance. /// </summary> private void init_page() { ReceiptType defaultType = ReceiptType.BillPayment; if (Request.QueryString.Count > 0) { if (this.Request.QueryString["ReceiptTrxCode"] != null) { //Application.Common.Utility objUtil = new Application.Common.Utility(); Session["transactionID"] = this.Request.QueryString["ReceiptTrxCode"];// objUtil.Decrypt(this.Request.QueryString["ReceiptTrxCode"]); } if (this.Request.QueryString["TranXORType"] != null) { string transOX = this.Request.QueryString["TranXORType"].ToString(); if (transOX == "9") { defaultType = ReceiptType.DepositTransaction; } else if (transOX == "17") { defaultType = ReceiptType.ReversalTransaction; } } } IBilling BManager = (IBilling)ObjectFactory.CreateInstance("BusinessProcess.SCM.BBilling, BusinessProcess.SCM"); DataSet theDataSet = BManager.GetReceipt(Convert.ToInt32(Session["transactionID"]), Convert.ToInt32(Session["AppLocationId"]), defaultType); this.rptDocument = new ReportDocument(); DataTable theDT = theDataSet.Tables[1]; if (this.PrintOnThermal) { rptDocument.Load(MapPath("..\\Reports\\Billing\\rptBillingRecieptThermal.rpt")); } else { rptDocument.Load(MapPath("..\\Reports\\Billing\\rptBillingReciept.rpt")); } String facilityName = (String)theDT.Rows[0]["FacilityName"]; String dupl; if (Request.QueryString["reprint"] != null && Request.QueryString["RePrint"] == "true") { dupl = "DUPLICATE"; } else { dupl = ""; } rptDocument.SetDataSource(theDataSet); rptDocument.SetParameterValue("FacilityName", facilityName); rptDocument.SetParameterValue("Currency", "KES"); rptDocument.SetParameterValue("DuplicateReceipt", dupl); if (!this.PrintOnThermal) { String facilityLogo = (String)theDT.Rows[0]["FacilityLogo"]; string f = GblIQCare.GetPath() + facilityLogo; string p = Server.MapPath("~/Images/" + facilityLogo); rptDocument.SetParameterValue("PicturePath", p); } billingRptViewer.EnableParameterPrompt = false; billingRptViewer.ReportSource = rptDocument; billingRptViewer.DataBind(); }
private void CreateReport() { Tables CrTables; ConnectionInfo crConnectionInfo = new ConnectionInfo(); TableLogOnInfos crtableLogoninfos = new TableLogOnInfos(); TableLogOnInfo crtableLogoninfo = new TableLogOnInfo(); crConnectionInfo.Type = ConnectionInfoType.SQL; crConnectionInfo.ServerName = "172.16.4.12"; crConnectionInfo.DatabaseName = "PTS"; crConnectionInfo.IntegratedSecurity = false; crConnectionInfo.UserID = "sa"; crConnectionInfo.Password = "******"; crtableLogoninfo.ConnectionInfo = crConnectionInfo; SqlConnection sqlcnn = PSSClass.DBConnection.PSSConnection(); if (sqlcnn == null) { MessageBox.Show("Connection problems encountered." + Environment.NewLine + "Please contact the IT Department.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } SqlCommand sqlcmd = new SqlCommand(); SqlDataReader sqldr; crDoc = new ReportDocument(); if (rptName == "InvYrTotal") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "InvYrTotal.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cYr"].Text = "'FY " + nYr.ToString() + "'"; sqlcmd = new SqlCommand("spInvYrTotRpt", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); } else if (rptName == "LogYrTotal") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "LogYrTotal.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cGBL"].Text = "" + nGBL + ""; crDoc.DataDefinition.FormulaFields["cYr"].Text = "'FY " + nYr.ToString() + "'"; sqlcmd = new SqlCommand("spLogYrTotRpt", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); sqlcmd.Parameters.AddWithValue("@GBL", nGBL); } else if (rptName == "RptYrTotal") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "RptYrTotal.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cGBL"].Text = "" + nGBL + ""; crDoc.DataDefinition.FormulaFields["cYr"].Text = "'FY " + nYr.ToString() + "'"; sqlcmd = new SqlCommand("spRptYrTot", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); sqlcmd.Parameters.AddWithValue("@GBL", nGBL); } else if (rptName == "NewSponsors") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "NewSponsors.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FOR THE YEAR " + nYr.ToString() + "'"; sqlcmd = new SqlCommand("spNewCustomers", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); } else if (rptName == "InactiveSponsors") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "InactiveSponsors.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FOR THE YEAR " + nYr.ToString() + "'"; sqlcmd = new SqlCommand("spInactiveCustomers", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); } else if (rptName == "MgmtGraph") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtSummary.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cYr"].Text = "'FY " + nYr.ToString() + "'"; sqlcmd = new SqlCommand("spMgmtTotRpt", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); } else if (rptName == "MgmtQuoteRpt") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "QuotesByDept.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FY " + nYr.ToString() + "'"; sqlcmd = new SqlCommand("spQuoteAnaDept", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); sqlcmd.Parameters.AddWithValue("@Sort", 1); } else if (rptName == "ProformaRev") { string rpt = ""; if (nMgmtRev >= 1 && nMgmtRev <= 4) { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "ProformaRev.rpt"; } else { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "ProformaRevSC.rpt"; } crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FY " + nYr.ToString() + "'"; if (nMgmtRev == 1) { crDoc.DataDefinition.FormulaFields["cCategory"].Text = "'ALL SPONSORS'"; sqlcmd = new SqlCommand("spProformaRevAll", sqlcnn); } //else if (nMgmtRev == 2) //{ // crDoc.DataDefinition.FormulaFields["cCategory"].Text = "'NON-INGREDION SPONSORS'"; // sqlcmd = new SqlCommand("spProformaRev", sqlcnn); //} //else if (nMgmtRev == 3) //{ // crDoc.DataDefinition.FormulaFields["cCategory"].Text = "'INGREDION'"; // sqlcmd = new SqlCommand("spProformaRevIng", sqlcnn); //} else if (nMgmtRev == 4) { crDoc.DataDefinition.FormulaFields["cCategory"].Text = "'STABILITY'"; sqlcmd = new SqlCommand("spProformaRevSta", sqlcnn); } //else if (nMgmtRev == 5) //{ // crDoc.DataDefinition.FormulaFields["cCategory"].Text = "'STERILIZATION'"; // sqlcmd = new SqlCommand("spProformaRevSter", sqlcnn); //} sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Year", nYr); sqlcmd.Parameters.AddWithValue("@SpID", SpID); sqlcmd.Parameters.AddWithValue("@SC", SC); sqlcmd.CommandTimeout = 60; } else if (rptName == "IngredionRevenue") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "IngredionRevenue.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cYear"].Text = "'" + nYr.ToString() + "'"; sqlcmd = new SqlCommand("spIngredionRevRpt", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); } else if (rptName == "IngredionProfDtls") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "IngredionProfDtls.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cYear"].Text = "'" + nYr.ToString() + "'"; sqlcmd = new SqlCommand("spIngredionProfDtls", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); } else if (rptName == "MgmtRevenue") { string rpt = ""; if (nMgmtRev == 1 || nMgmtRev == 2) { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevenue.rpt"; } else if (nMgmtRev == 3 || nMgmtRev == 4) { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevSC.rpt"; } else if (nMgmtRev >= 5 && nMgmtRev <= 8) { if (nMgmtRev == 5 || nMgmtRev == 6) { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevSpSumm.rpt"; } else { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevSpDtls.rpt"; } } else if (nMgmtRev >= 9 && nMgmtRev <= 12) { if (nMgmtRev == 9 || nMgmtRev == 10) { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevSCSumm.rpt"; } else { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevSCDtls.rpt"; } } crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FY " + nYr.ToString() + "'"; sqlcmd = new SqlCommand("spRevPivotRpt", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Year", nYr); if (nMgmtRev == 1 || nMgmtRev == 3 || nMgmtRev == 5 || nMgmtRev == 7 || nMgmtRev == 9 || nMgmtRev == 11) { sqlcmd.Parameters.AddWithValue("@SpID", 0); sqlcmd.Parameters.AddWithValue("@SC", 0); } else if (nMgmtRev == 2 || nMgmtRev == 4 || nMgmtRev == 6 || nMgmtRev == 8 || nMgmtRev == 10 || nMgmtRev == 12) { sqlcmd.Parameters.AddWithValue("@SpID", SpID); sqlcmd.Parameters.AddWithValue("@SC", SC); } } else if (rptName == "PSSFinancial") { sqlcnn.Close(); sqlcmd.Dispose(); sqlcnn = PSSClass.DBConnection.MDFConnection("172.16.4.12", "PTSFinancials", false, "sa", "Pass2018", ""); if (sqlcnn == null) { MessageBox.Show("Connection problems encountered." + Environment.NewLine + "Please contact the IT Department.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } sqlcmd = new SqlCommand(); crConnectionInfo.DatabaseName = "PTSFinancials"; crConnectionInfo.IntegratedSecurity = false; crConnectionInfo.UserID = "sa"; crConnectionInfo.Password = "******"; crtableLogoninfo.ConnectionInfo = crConnectionInfo; this.Text = "FINANCIAL REPORT"; string strDay = ""; decimal nIT = PSSClass.Financials.IncomeTax(nYr); string[] strMonths = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }; int[] nLYMonths = new int[] { 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }; int[] nNLYMonths = new int[] { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }; int nR = nYr % 4; if (nR == 0) { strDay = nLYMonths[nMo - 1].ToString(); } else { strDay = nNLYMonths[nMo - 1].ToString(); } string rpt = ""; if (nFSFormat == 0) { //rpt = Application.StartupPath + @"\Reports\" + "PSSFinancial.rpt"; rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancial.rpt"; } else if (nFSFormat == 1) { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFSSummary.rpt"; } else if (nFSFormat == 2) { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancialNoPer.rpt"; } else if (nFSFormat == 3) { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancialGL.rpt"; } crDoc.Load(rpt); CrTables = crDoc.Database.Tables; foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables) { crtableLogoninfo = CrTable.LogOnInfo; crtableLogoninfo.ConnectionInfo = crConnectionInfo; CrTable.ApplyLogOnInfo(crtableLogoninfo); } crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FOR THE PERIOD ENDED " + strMonths[nMo - 1].ToUpper() + " " + strDay + ", " + nYr.ToString() + "'"; crDoc.DataDefinition.FormulaFields["strMo"].Text = "'" + nMo.ToString() + "'"; crDoc.DataDefinition.FormulaFields["cTaxes"].Text = nIT.ToString(); sqlcmd = new SqlCommand("spLinkFinancial", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); } else if (rptName == "PSSFinancial_12Month") { sqlcnn.Close(); sqlcmd.Dispose(); sqlcnn = PSSClass.DBConnection.MDFConnection("172.16.4.12", "PTSFinancials", false, "sa", "Pass2018", ""); if (sqlcnn == null) { MessageBox.Show("Connection problems encountered." + Environment.NewLine + "Please contact the IT Department.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } sqlcmd = new SqlCommand(); crConnectionInfo.DatabaseName = "PTSFinancials"; crConnectionInfo.IntegratedSecurity = false; crConnectionInfo.UserID = "sa"; crConnectionInfo.Password = "******"; crtableLogoninfo.ConnectionInfo = crConnectionInfo; this.Text = "FINANCIAL REPORT::12 MONTH OVERVIEW"; string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancial_12Month.rpt"; //string strDay = ""; //decimal nIT = PSSClass.Financials.IncomeTax(nYr); //string[] strMonths = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }; //int[] nLYMonths = new int[] { 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }; //int[] nNLYMonths = new int[] { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }; //int nR = nYr % 4; //if (nR == 0) // strDay = nLYMonths[nMo - 1].ToString(); //else // strDay = nNLYMonths[nMo - 1].ToString(); //string rpt = ""; //if (nFSFormat == 0) // rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancial_12Month.rpt"; //else if (nFSFormat == 1) // rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFSSummary.rpt"; //else if (nFSFormat == 2) // rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancialNoPer.rpt"; //else if (nFSFormat == 3) // rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancialGL.rpt"; crDoc.Load(rpt); CrTables = crDoc.Database.Tables; //foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables) //{ // crtableLogoninfo = CrTable.LogOnInfo; // crtableLogoninfo.ConnectionInfo = crConnectionInfo; // CrTable.ApplyLogOnInfo(crtableLogoninfo); //} //crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FOR THE PERIOD ENDED " + strMonths[nMo - 1].ToUpper() + " " + strDay + ", " + nYr.ToString() + "'"; //crDoc.DataDefinition.FormulaFields["strMo"].Text = "'" + nMo.ToString() + "'"; //crDoc.DataDefinition.FormulaFields["cTaxes"].Text = nIT.ToString(); sqlcmd = new SqlCommand("spLinkFinancial", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); } else if (rptName.IndexOf("Variance") != -1) { this.Text = "VARIANCE REPORT - BUDGET VS. ACTUAL"; string strDay = ""; string rpt = ""; decimal nIT = PSSClass.Financials.IncomeTax(nYr); string[] strMonths = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }; int[] nLYMonths = new int[] { 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }; int[] nNLYMonths = new int[] { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }; int nR = nYr % 4; if (nR == 0) { strDay = nLYMonths[nMo - 1].ToString(); } else { strDay = nNLYMonths[nMo - 1].ToString(); } if (rptName == "VarianceYTD") { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "Variance.rpt"; } else if (rptName == "VarianceMonthly") { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "VarianceMonthly.rpt"; } else { rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "VarianceQtrly.rpt"; } crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cMo"].Text = "'" + nMo.ToString() + "'"; crDoc.DataDefinition.FormulaFields["cYr"].Text = "'" + nYr.ToString() + "'"; crDoc.DataDefinition.FormulaFields["cDay"].Text = "'" + strDay + "'"; crDoc.DataDefinition.FormulaFields["cQtr"].Text = "'" + nQtr.ToString() + "'"; } else if (rptName == "TestsCompleted") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "TestsCompleted.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["dteStart"].Text = "'" + dteStart.ToShortDateString() + "'"; crDoc.DataDefinition.FormulaFields["dteEnd"].Text = "'" + dteEnd.ToShortDateString() + "'"; sqlcmd = new SqlCommand("spRptTestsCompleted", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@StartDate", dteStart); sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd); sqlcmd.Parameters.AddWithValue("@DeptList", strDept); //nDeptID } else if (rptName == "OutstandingTests") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "OutstandingTests.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["dteStart"].Text = "'" + dteStart.ToShortDateString() + "'"; crDoc.DataDefinition.FormulaFields["dteEnd"].Text = "'" + dteEnd.ToShortDateString() + "'"; sqlcmd = new SqlCommand("spRptOutstandingGBLTesting", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@StartDate", dteStart); sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd); sqlcmd.Parameters.AddWithValue("@DeptList", strDept); //nDeptID } else if (rptName == "TestsForCompletion") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "TestsForCompletion.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["dteStart"].Text = "'" + dteStart.ToShortDateString() + "'"; crDoc.DataDefinition.FormulaFields["dteEnd"].Text = "'" + dteEnd.ToShortDateString() + "'"; sqlcmd = new SqlCommand("spRptTestsForCompletion", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@StartDate", dteStart); sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd); } else if (rptName == "StabilityReport") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "StabilityReport-New.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'RANGE PERIOD: " + dteStart.ToShortDateString() + " - " + dteEnd.ToShortDateString() + "'"; sqlcmd = new SqlCommand("spStabilityReport", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@SDate", dteStart); sqlcmd.Parameters.AddWithValue("@EDate", dteEnd); } else if (rptName == "StudyDirReport") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "StudyDirRpt.rpt"; crDoc.Load(rpt); if (dteStart == dteEnd) { crDoc.DataDefinition.FormulaFields["strPeriod"].Text = "'FOR THE PERIOD: " + dteStart.ToShortDateString() + "'"; } else { crDoc.DataDefinition.FormulaFields["strPeriod"].Text = "'FOR THE PERIOD: " + dteStart.ToShortDateString() + " - " + dteEnd.ToShortDateString() + "'"; } sqlcmd = new SqlCommand("spStudyDirReport", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@StartDate", dteStart); sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd); sqlcmd.Parameters.AddWithValue("@SDID", nSDID); } else if (rptName == "UnmailedReports") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "UnmailedReports.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["dteStart"].Text = "'" + dteStart.ToShortDateString() + "'"; crDoc.DataDefinition.FormulaFields["dteEnd"].Text = "'" + dteEnd.ToShortDateString() + "'"; sqlcmd = new SqlCommand("spRptUnmailedReports", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@StartDate", dteStart); sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd); } else if (rptName == "EqptServiceSched") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "EquipmentServiceSched.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["dteStart"].Text = "'" + dteStart.ToShortDateString() + "'"; crDoc.DataDefinition.FormulaFields["dteEnd"].Text = "'" + dteEnd.ToShortDateString() + "'"; sqlcmd = new SqlCommand("spRptEqptSrvcSched", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@StartDate", dteStart); sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd); } else if (rptName == "SCDepartments") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "SCDepartments.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'" + dteStart.ToShortDateString() + " - " + dteEnd.ToShortDateString() + "'"; sqlcmd = new SqlCommand("spRptDeptSCSP", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@StartDate", dteStart); sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd); sqlcmd.Parameters.AddWithValue("@DeptID", nDeptID); sqlcmd.Parameters.AddWithValue("@SC", SC); } else if (rptName == "DocExpiring") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "DocExpiring.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'As of " + DateTime.Now.ToString("MMMM dd, yyyy") + "'"; sqlcmd = new SqlCommand("spDocExpiringAlert", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; } else if (rptName == "SpeedResponse") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "SpeedResponse.rpt"; string strSpName = PSSClass.Sponsors.SpName(SpID); crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cYr"].Text = "'" + nYr.ToString("###0") + "'"; crDoc.DataDefinition.FormulaFields["cMo"].Text = "'" + nMo.ToString("###0") + "'"; crDoc.DataDefinition.FormulaFields["cSpID"].Text = "'" + SpID.ToString() + "'"; crDoc.DataDefinition.FormulaFields["cSpName"].Text = "'" + strSpName + "'"; } else if (rptName == "RptGBLErrors") { string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "RptGBLErrors.rpt"; crDoc.Load(rpt); crDoc.DataDefinition.FormulaFields["cYr"].Text = "'" + nYr.ToString() + "'"; crDoc.DataDefinition.FormulaFields["cGBL"].Text = "'" + nGBL.ToString() + "'"; sqlcmd = new SqlCommand("spRptGBLErrors", sqlcnn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@Yr", nYr); sqlcmd.Parameters.AddWithValue("@IncGBL", nGBL); sqlcmd.CommandTimeout = 60; } DataTable dTable = new DataTable(); if (rptName == "PSSFinancial") { dTable = PSSClass.Financials.AcctSummary(nYr); } else if (rptName == "PSSFinancial_12Month") { dTable = PSSClass.Financials.AcctSummary(nYr); } else if (rptName.IndexOf("Variance") != -1) { dTable = dtRpt; } else if (rptName == "SpeedResponse") { dTable.Columns.Add("Category", typeof(int)); dTable.Columns.Add("NoDays", typeof(int)); dTable.Columns.Add("DayCount", typeof(int)); //LogTests Start Date DataTable dtX = PSSClass.ManagementReports.SpeedResponse(1, Convert.ToInt16(nYr), Convert.ToInt16(nMo), "LT.StartDate", Convert.ToInt16(SpID)); for (int i = 0; i < dtX.Rows.Count; i++) { DataRow dR = dTable.NewRow(); dR["Category"] = dtX.Rows[i]["Category"]; dR["NoDays"] = dtX.Rows[i]["NoDays"]; dR["DayCount"] = dtX.Rows[i]["DayCount"]; dTable.Rows.Add(dR); } //FinalRptLog DateOn LogTests End Date dtX = PSSClass.ManagementReports.SpeedResponse(2, Convert.ToInt16(nYr), Convert.ToInt16(nMo), "FL.DateOn", Convert.ToInt16(SpID)); for (int i = 0; i < dtX.Rows.Count; i++) { DataRow dR = dTable.NewRow(); dR["Category"] = dtX.Rows[i]["Category"]; dR["NoDays"] = dtX.Rows[i]["NoDays"]; dR["DayCount"] = dtX.Rows[i]["DayCount"]; dTable.Rows.Add(dR); } //LogTests End Date dtX = PSSClass.ManagementReports.SpeedResponse(3, Convert.ToInt16(nYr), Convert.ToInt16(nMo), "LT.EndDate", Convert.ToInt16(SpID)); for (int i = 0; i < dtX.Rows.Count; i++) { DataRow dR = dTable.NewRow(); dR["Category"] = dtX.Rows[i]["Category"]; dR["NoDays"] = dtX.Rows[i]["NoDays"]; dR["DayCount"] = dtX.Rows[i]["DayCount"]; dTable.Rows.Add(dR); } //FinalRptLog DateOff dtX = PSSClass.ManagementReports.SpeedResponse(4, Convert.ToInt16(nYr), Convert.ToInt16(nMo), "FL.DateOff", Convert.ToInt16(SpID)); for (int i = 0; i < dtX.Rows.Count; i++) { DataRow dR = dTable.NewRow(); dR["Category"] = dtX.Rows[i]["Category"]; dR["NoDays"] = dtX.Rows[i]["NoDays"]; dR["DayCount"] = dtX.Rows[i]["DayCount"]; dTable.Rows.Add(dR); } //LogTest Start Date vs FinalRptLog DateOn dtX = PSSClass.ManagementReports.SpeedResponseSD(4, Convert.ToInt16(nYr), Convert.ToInt16(nMo), Convert.ToInt16(SpID)); for (int i = 0; i < dtX.Rows.Count; i++) { DataRow dR = dTable.NewRow(); dR["Category"] = dtX.Rows[i]["Category"]; dR["NoDays"] = dtX.Rows[i]["NoDays"]; dR["DayCount"] = dtX.Rows[i]["DayCount"]; dTable.Rows.Add(dR); } } else { sqldr = sqlcmd.ExecuteReader(); try { dTable.Load(sqldr); } catch { } } crDoc.SetDataSource(dTable); CrTables = crDoc.Database.Tables; foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables) { crtableLogoninfo = CrTable.LogOnInfo; crtableLogoninfo.ConnectionInfo = crConnectionInfo; CrTable.ApplyLogOnInfo(crtableLogoninfo); } crReport.ReportSource = crDoc; crReport.ShowGroupTreeButton = false; crReport.Refresh(); sqlcnn.Close(); sqlcnn.Dispose(); this.WindowState = FormWindowState.Maximized; }
public FileResult ViewPDF(int?id) { Stream stream = null; var nombreArchivo = ""; ReportDocument reportDocument = new ReportDocument(); byte[] pdfByte = null; try { EPRTA_MOVIMIENTO solicitud = unitOfWork.MovimientoRepository.GetById(id); EntitiesProveduria db = new EntitiesProveduria(); SqlConnectionStringBuilder builderVenta = new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString); String pathReport = ""; if (solicitud.ID_TIPO_MOVIMIENTO == 2) { SP_EGRESO_BODEGATableAdapter tableAdapter = new SP_EGRESO_BODEGATableAdapter(); object objetos = new object(); DataTable dataTable = tableAdapter.GetData(id, out objetos); pathReport = Path.Combine(HttpRuntime.AppDomainAppPath, "Reports\\Cr_Egreso_Bodega.rpt"); reportDocument.Load(pathReport); reportDocument.SetDataSource(dataTable); } else if (solicitud.ID_TIPO_MOVIMIENTO == 4) { SP_INGRESO_BODEGATableAdapter tableAdapter = new SP_INGRESO_BODEGATableAdapter(); object objetos = new object(); DataTable dataTable = tableAdapter.GetData(id, out objetos); pathReport = Path.Combine(HttpRuntime.AppDomainAppPath, "Reports\\Cr_Ingreso_Bodega.rpt"); reportDocument.Load(pathReport); reportDocument.SetDataSource(dataTable); } else if (solicitud.ID_TIPO_MOVIMIENTO == 10 || solicitud.ID_TIPO_MOVIMIENTO == 11) { SP_AJUSTE_BODEGATableAdapter tableAdapter = new SP_AJUSTE_BODEGATableAdapter(); object objetos = new object(); DataTable dataTable = tableAdapter.GetData(id, out objetos); pathReport = Path.Combine(HttpRuntime.AppDomainAppPath, "Reports\\Cr_Ajuste_Bodega.rpt"); reportDocument.Load(pathReport); reportDocument.SetDataSource(dataTable); } reportDocument.SetDatabaseLogon(builderVenta.UserID, builderVenta.Password); stream = reportDocument.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); pdfByte = ReadFully(stream); nombreArchivo = "Movimiento_bodega"; Response.AddHeader("content-disposition", "inline; title='';" + "filename=" + nombreArchivo + ".pdf"); } catch (Exception ex) { logger.Error(ex, ex.Message); } finally { if (reportDocument != null) { if (reportDocument.IsLoaded) { reportDocument.Close(); reportDocument.Dispose(); } } } return(File(pdfByte, MediaTypeNames.Application.Pdf)); }
private void barButtonItem5_ItemClick(object sender, ItemClickEventArgs e) { if (DGvTransView.FocusedRowHandle < 0) { return; } int p_FlatId = 0; DataTable dt = new DataTable(); int p_MaintanceId = 0; string s = ""; frmReport objReport; ReportDocument cryRpt; decimal dBillAmount = 0, dArrear = 0; DateTime dStartDate = DateTime.Today;; DateTime dEndDate = DateTime.Today;; string[] DataFiles; string sHeader = ""; int sMonth = 0; int sYear = 0; DateTime sFifetDate = DateTime.Today; p_FlatId = Convert.ToInt32(DGvTransView.GetRowCellValue(DGvTransView.FocusedRowHandle, "FlatId")); p_MaintanceId = Convert.ToInt32(DGvTransView.GetRowCellValue(DGvTransView.FocusedRowHandle, "MaintenanceId")); dt = MaintenanceBL.GetReport(p_FlatId, p_MaintanceId); if (dt.Rows.Count > 0) { dBillAmount = Convert.ToDecimal(CommFun.IsNullCheck(dt.Rows[0]["BillAmount"], CommFun.datatypes.vartypenumeric)); dArrear = Convert.ToDecimal(CommFun.IsNullCheck(dt.Rows[0]["Arrear"], CommFun.datatypes.vartypenumeric)); if (dArrear == 0) { dStartDate = Convert.ToDateTime(CommFun.IsNullCheck(DGvTransView.GetRowCellValue(DGvTransView.FocusedRowHandle, "StartDate"), CommFun.datatypes.VarTypeDate)); dEndDate = Convert.ToDateTime(CommFun.IsNullCheck(DGvTransView.GetRowCellValue(DGvTransView.FocusedRowHandle, "EndDate"), CommFun.datatypes.VarTypeDate)); sMonth = dStartDate.Month; sYear = dStartDate.Year; sFifetDate = Convert.ToDateTime("15" + "/" + sMonth + "/" + sYear); } else { dStartDate = Convert.ToDateTime(CommFun.IsNullCheck(dt.Rows[0]["FromDate"], CommFun.datatypes.VarTypeDate)); dEndDate = Convert.ToDateTime(CommFun.IsNullCheck(dt.Rows[0]["ToDate"], CommFun.datatypes.VarTypeDate)); sMonth = dEndDate.Month; sYear = dEndDate.Year; sFifetDate = Convert.ToDateTime("15" + "/" + sMonth + "/" + sYear); } //dEndDate = Convert.ToDateTime(CommFun.IsNullCheck(dt.Rows[0]["ToDate"], CommFun.datatypes.VarTypeDate)); } decimal dTotalPayAmt = Convert.ToDecimal(dBillAmount + dArrear); objReport = new frmReport(); string strReportPath = Application.StartupPath + "\\Maintenance.Rpt"; cryRpt = new ReportDocument(); cryRpt.Load(strReportPath); s = "{MaintenanceDet.FlatId}=" + p_FlatId + " "; DataFiles = new string[] { BsfGlobal.g_sCRMDBName, BsfGlobal.g_sCRMDBName, BsfGlobal.g_sCRMDBName, BsfGlobal.g_sCRMDBName, BsfGlobal.g_sWorkFlowDBName, BsfGlobal.g_sWorkFlowDBName, BsfGlobal.g_sWorkFlowDBName, BsfGlobal.g_sWorkFlowDBName, BsfGlobal.g_sCRMDBName }; objReport.ReportConvert(cryRpt, DataFiles); if (s.Length > 0) { cryRpt.RecordSelectionFormula = s; } objReport.rptViewer.ReportSource = null; objReport.rptViewer.SelectionFormula = s; objReport.rptViewer.ReportSource = cryRpt; cryRpt.DataDefinition.FormulaFields["Decimal"].Text = string.Format(CommFun.g_iCurrencyDigit.ToString()); cryRpt.DataDefinition.FormulaFields["MainAmt"].Text = "'" + string.Format(dBillAmount.ToString()) + "'"; cryRpt.DataDefinition.FormulaFields["Arrear"].Text = "'" + string.Format(dArrear.ToString()) + "'"; cryRpt.DataDefinition.FormulaFields["TotalPay"].Text = "'" + string.Format(dTotalPayAmt.ToString()) + "'"; if (dArrear == 0) { cryRpt.DataDefinition.FormulaFields["EndFiftenDate"].Text = String.Format("'{0}'", sHeader = sHeader = String.Format(" {0} ", sFifetDate.ToString("dd-MMM-yyyy"))); cryRpt.DataDefinition.FormulaFields["StartfiftenDate"].Text = String.Format("'{0}'", sHeader = sHeader = String.Format(" {0} ", sFifetDate.ToString("dd-MMM-yyyy"))); } else { cryRpt.DataDefinition.FormulaFields["EndFiftenDate"].Text = String.Format("'{0}'", sHeader = sHeader = String.Format(" {0} ", sFifetDate.ToString("dd-MMM-yyyy"))); cryRpt.DataDefinition.FormulaFields["StartfiftenDate"].Text = String.Format("'{0}'", sHeader = sHeader = String.Format(" {0} ", sFifetDate.ToString("dd-MMM-yyyy"))); } cryRpt.DataDefinition.FormulaFields["Period"].Text = String.Format("'{0}'", sHeader = sHeader = String.Format(" {0} to {1} ", dStartDate.ToString("dd-MMM-yyyy"), dEndDate.ToString("dd-MMM-yyyy"))); objReport.WindowState = FormWindowState.Maximized; objReport.rptViewer.Refresh(); objReport.Show(); }
private void ShowReport() { ReportDocument myReportDocument = new ReportDocument(); if (rdoNormalEmployee.Checked) { myReportDocument.Load(Server.MapPath("~/MainReport/Reports/rptNormalEmployee.rpt")); } else if (rdoSpecEmployee.Checked) { myReportDocument.Load(Server.MapPath("~/MainReport/Reports/rptSpecEmployee.rpt")); } myReportDocument.Refresh(); CrystalReportViewer1.ReportSource = myReportDocument; long Code = 0; int sex = 0; long JobId = 0; long Idno = 0; long CountryId = 0; long CityId = 0; string FirstName = "0"; string LastName = "0"; long DepartmentId = 0; long DivisionId = 0; long QualificationId = 0; if (!string.IsNullOrEmpty(txtCode.Text)) { Code = long.Parse(txtCode.Text); } if (ddlSex.SelectedValue != "") { sex = int.Parse(ddlSex.SelectedValue); } if (ddlJob.SelectedValue != "") { JobId = long.Parse(ddlJob.SelectedValue); } if (!string.IsNullOrEmpty(txtIdNo.Text)) { Idno = long.Parse(txtIdNo.Text); } if (ddlCountry.SelectedValue != "") { CountryId = long.Parse(ddlCountry.SelectedValue); } if (ddlCity.SelectedValue != "") { CityId = long.Parse(ddlCity.SelectedValue); } if (!string.IsNullOrEmpty(txtFirstName.Text)) { FirstName = txtFirstName.Text; } if (!string.IsNullOrEmpty(txtLastName.Text)) { LastName = txtLastName.Text; } if (ddlDepartment.SelectedValue != "") { DepartmentId = long.Parse(ddlDepartment.SelectedValue); } if (ddlDivision.SelectedValue != "") { DivisionId = long.Parse(ddlDivision.SelectedValue); } if (ddlQualification.SelectedValue != "") { QualificationId = long.Parse(ddlQualification.SelectedValue); } //myReportDocument.SetDatabaseLogon(csGetPermission.DBUser, csGetPermission.DBPassword, csGetPermission.DBServerName, csGetPermission.DBName); myReportDocument.DataSourceConnections[0].SetConnection(csGetPermission.DBServerName, csGetPermission.DBName, csGetPermission.DBUser, csGetPermission.DBPassword); myReportDocument.SetParameterValue("@Code", Code); myReportDocument.SetParameterValue("@Sex", sex); myReportDocument.SetParameterValue("@JobId", JobId); myReportDocument.SetParameterValue("@IdNo", Idno); myReportDocument.SetParameterValue("@CountryId", CountryId); myReportDocument.SetParameterValue("@CityId", CityId); myReportDocument.SetParameterValue("@FirstName", FirstName); myReportDocument.SetParameterValue("@LastName", LastName); myReportDocument.SetParameterValue("@DepartmentId", DepartmentId); myReportDocument.SetParameterValue("@DivisionId", DivisionId); myReportDocument.SetParameterValue("@QualificationId", QualificationId); myReportDocument.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, ""); }
protected void Page_Load(object sender, EventArgs e) { StringBuilder sbFilter = new StringBuilder(); string fundCode = ""; string fundName = ""; string balDate = ""; string statementType = ""; if (Session["UserID"] == null) { Session.RemoveAll(); Response.Redirect("../../Default.aspx"); } else { statementType = (string)Session["statementType"]; fundCode = (string)Session["fundCode"]; fundName = (string)Session["fundName"]; balDate = (string)Session["balDate"]; } string strSQL, strSQLGrandTotCostVal; string Path = ""; double cs_asset, cf_unlist, TotalAssetValue_Mar, GrandTotCostVal; DataTable dtReprtSource = new DataTable(); DataTable dtGrandTotCostVal = new DataTable(); DataTable dtNAV = new DataTable(); DataTable dtNonListed = new DataTable(); strSQL = "select nvl(sum(n.costprice),0) total_asset_c,nvl(sum(n.marketprice),0) total_asset_m from nav.nav_details n, nav.nav_master m where " + " n.NAVROWTYPE = 'A' and n.navfundid = " + fundCode + " and m.navfundid = " + fundCode + " and m.navno = (select max(navno) from nav.nav_master m where m.navfundid = " + fundCode + " AND m.NAVDATE <='" + balDate + "' )" + " and n.navfundid = m.navfundid and m.navno = n.navno"; dtNAV = commonGatewayObj.Select(strSQL); cs_asset = Convert.ToDouble(dtNAV.Rows[0]["total_asset_c"].ToString()); if (cs_asset == 0) { cs_asset = 1; } TotalAssetValue_Mar = Convert.ToDouble(dtNAV.Rows[0]["total_asset_m"].ToString()); strSQL = "select f_cd, inv_amount, inv_date FROM(SELECT f_cd, inv_amount, inv_date, " + "rank() over (partition by f_cd order by inv_date desc) rnk FROM NON_LISTED_SECURITIES where inv_amount>0 and F_CD IN(" + fundCode + ") and inv_date<='" + balDate + "' )" + " WHERE rnk = 1"; dtNonListed = commonGatewayObj.Select(strSQL); if (dtNonListed.Rows.Count > 0) { cf_unlist = Convert.ToDouble(dtNonListed.Rows[0]["inv_amount"].ToString()); } else { cf_unlist = 0; } if (string.Compare(statementType, "Summary (Total Asset Value)", true) == 0) { strSQL = "select p.sect_maj_nm,p.f_cd, p.sect_maj_cd, sum(p.nos_t)TotalShares, sum(p.rt_acm),sum(p.tcst_aft_com)TotalCost,sum(p.cost_percent)TotalCostPercent,sum(p.m_p)," + cs_asset + " cs_asset," + TotalAssetValue_Mar + "TotalAssetValue_Mar,p.unl_p," + cf_unlist + "NonListVal from " + "(select trim(c.comp_nm)comp_nm, sect_maj_nm,a.f_cd, a.sect_maj_cd, trunc(tot_nos) nos_t, bal_dt , trunc(tcst_aft_com / tot_nos, 2) rt_acm, " + "trunc(tcst_aft_com, 2) tcst_aft_com,ROUND((tot_cost / tot_nos),2) c_rt, tot_cost,round((tcst_aft_com/" + cs_asset + ")*100,2) cost_percent," + "nvl(a.dse_rt, 0) DSE_rate, nvl(a.cse_rt, 0) CSE_rate, a.adc_rt m_rt, a.dse_rt* tot_nos m_p," + "(a.dse_rt - trunc(tcst_aft_com / tot_nos, 2)) diff ," + "(round(a.dse_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain,round((" + cf_unlist / cs_asset + ")*100,2)unl_p" + " from pfolio_bk a, comp c , nav.nav_master n " + " where c.comp_cd = a.comp_cd and a.f_cd =" + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode + " and n.navno = (select max(navno) from nav.nav_master where navfundid = " + fundCode + " AND NAVDATE<='" + balDate + "' ) order by a.sect_maj_cd)p" + " group by p.sect_maj_cd,p.sect_maj_nm,p.f_cd order by p.sect_maj_cd"; dtReprtSource = commonGatewayObj.Select(strSQL); strSQLGrandTotCostVal = "select sum(TotalCost) as GrandTotCostVal from" + "(select p.sect_maj_nm,p.f_cd, p.sect_maj_cd, sum(p.nos_t)TotalShares, sum(p.rt_acm),sum(p.tcst_aft_com)TotalCost,sum(p.cost_percent)TotalCostPercent,sum(p.m_p)," + cs_asset + " cs_asset," + TotalAssetValue_Mar + "TotalAssetValue_Mar,p.unl_p," + cf_unlist + "NonListVal from " + "(select trim(c.comp_nm)comp_nm, sect_maj_nm,a.f_cd, a.sect_maj_cd, trunc(tot_nos) nos_t, bal_dt , trunc(tcst_aft_com / tot_nos, 2) rt_acm, " + "trunc(tcst_aft_com, 2) tcst_aft_com,ROUND((tot_cost / tot_nos),2) c_rt, tot_cost,round((tcst_aft_com/" + cs_asset + ")*100,2) cost_percent," + "nvl(a.dse_rt, 0) DSE_rate, nvl(a.cse_rt, 0) CSE_rate, a.adc_rt m_rt, a.dse_rt* tot_nos m_p," + "(a.dse_rt - trunc(tcst_aft_com / tot_nos, 2)) diff ," + "(round(a.dse_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain,round((" + cf_unlist / cs_asset + ")*100,2)unl_p" + " from pfolio_bk a, comp c , nav.nav_master n " + " where c.comp_cd = a.comp_cd and a.f_cd =" + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode + " and n.navno = (select max(navno) from nav.nav_master where navfundid = " + fundCode + " AND NAVDATE<='" + balDate + "' ) order by a.sect_maj_cd)p" + " group by p.sect_maj_cd,p.sect_maj_nm,p.f_cd order by p.sect_maj_cd)"; dtGrandTotCostVal = commonGatewayObj.Select(strSQLGrandTotCostVal); if (!dtGrandTotCostVal.Rows[0].IsNull("GrandTotCostVal")) { GrandTotCostVal = Convert.ToDouble(dtGrandTotCostVal.Rows[0]["GrandTotCostVal"].ToString()); GrandTotCostVal = GrandTotCostVal + cf_unlist; } else { GrandTotCostVal = 1; } if (dtReprtSource.Rows.Count > 0) { dtReprtSource.TableName = "AsstPerNAVSummaryAndPortfolio"; // dtReprtSource.WriteXmlSchema(@"E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report\xsdAsstPerNAVSummaryAndPortfolio.xsd"); Path = Server.MapPath("Report/crtAssetPercSummaryTotalAssetValueReport.rpt"); rdoc.Load(Path); rdoc.SetDataSource(dtReprtSource); CRV_AssetPercNAVSummaryAndPortfolio.ReportSource = rdoc; CRV_AssetPercNAVSummaryAndPortfolio.DisplayToolbar = true; CRV_AssetPercNAVSummaryAndPortfolio.HasExportButton = true; CRV_AssetPercNAVSummaryAndPortfolio.HasPrintButton = true; rdoc.SetParameterValue("prmbalDate", balDate); rdoc.SetParameterValue("prmStatementType", statementType); rdoc.SetParameterValue("prmFundName", fundName); rdoc.SetParameterValue("prmGrandTotCostVal", GrandTotCostVal); // rdoc.SetParameterValue("prmappOrEro", appOrEro); rdoc = ReportFactory.GetReport(rdoc.GetType()); } else { Response.Write("No Data Found"); } } else if (string.Compare(statementType, "Portfolio (Total Asset Value)", true) == 0) { strSQL = "select trim(c.comp_nm), sect_maj_nm, a.sect_maj_cd,trunc(tot_nos) nos_t, bal_dt, trunc(tcst_aft_com / tot_nos, 2) rt_acm," + cs_asset + " cs_asset," + cf_unlist + "NonListVal, " + "trunc(tcst_aft_com, 2) tcst_aft_com, round((tot_cost / tot_nos),2) c_rt, tot_cost," + "a.adc_rt m_rt, a.adc_rt* tot_nos m_p,(a.adc_rt * tot_nos - tcst_aft_com) diff," + "(round(a.adc_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain,round(((trunc(tot_nos) / c.no_shrs) * 100),2) paid_cap " + " from pfolio_bk a, comp c , nav.nav_master n " + " where c.comp_cd = a.comp_cd and a.f_cd = " + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode + "and navno = (select max(navno) from nav.nav_master where navfundid =" + fundCode + " AND NAVDATE<='" + balDate + "' ) order by a.sect_maj_cd"; dtReprtSource = commonGatewayObj.Select(strSQL); strSQLGrandTotCostVal = "select sum(tcst_aft_com) as GrandTotCostVal from " + "(select trim(c.comp_nm), sect_maj_nm, a.sect_maj_cd,trunc(tot_nos) nos_t, bal_dt, trunc(tcst_aft_com / tot_nos, 2) rt_acm," + cs_asset + " cs_asset," + cf_unlist + "NonListVal, " + "trunc(tcst_aft_com, 2) tcst_aft_com, round((tot_cost / tot_nos),2) c_rt, tot_cost," + "a.adc_rt m_rt, a.adc_rt* tot_nos m_p,(a.adc_rt * tot_nos - tcst_aft_com) diff," + "(round(a.adc_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain,round(((trunc(tot_nos) / c.no_shrs) * 100),2) paid_cap " + " from pfolio_bk a, comp c , nav.nav_master n " + " where c.comp_cd = a.comp_cd and a.f_cd = " + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode + "and navno = (select max(navno) from nav.nav_master where navfundid =" + fundCode + " AND NAVDATE<='" + balDate + "' ) order by a.sect_maj_cd)"; dtGrandTotCostVal = commonGatewayObj.Select(strSQLGrandTotCostVal); if (!dtGrandTotCostVal.Rows[0].IsNull("GrandTotCostVal")) { GrandTotCostVal = Convert.ToDouble(dtGrandTotCostVal.Rows[0]["GrandTotCostVal"].ToString()); GrandTotCostVal = GrandTotCostVal + cf_unlist; } else { GrandTotCostVal = 1; } if (dtReprtSource.Rows.Count > 0) { dtReprtSource.TableName = "AsstPercentagePortfolio"; dtReprtSource.WriteXmlSchema(@"E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report\xsdAsstPercentagePortfolio.xsd"); Path = Server.MapPath("Report/crtAssetPercentagePortfolioTotalAssetValueReport.rpt"); rdoc.Load(Path); rdoc.SetDataSource(dtReprtSource); CRV_AssetPercNAVSummaryAndPortfolio.ReportSource = rdoc; CRV_AssetPercNAVSummaryAndPortfolio.DisplayToolbar = true; CRV_AssetPercNAVSummaryAndPortfolio.HasExportButton = true; CRV_AssetPercNAVSummaryAndPortfolio.HasPrintButton = true; rdoc.SetParameterValue("prmbalDate", balDate); rdoc.SetParameterValue("prmStatementType", statementType); rdoc.SetParameterValue("prmFundName", fundName); rdoc.SetParameterValue("prmGrandTotCostVal", GrandTotCostVal); // rdoc.SetParameterValue("prmappOrEro", appOrEro); rdoc = ReportFactory.GetReport(rdoc.GetType()); } else { Response.Write("No Data Found"); } } else { strSQL = "select p.sect_maj_nm,p.f_cd, p.sect_maj_cd, sum(p.nos_t)TotalShares, sum(p.rt_acm),sum(p.tcst_aft_com)TotalCost,p.asset,sum(p.cost_percent)TotalCostPercent,sum(p.m_p)TotalMarketPrice," + cf_unlist + " NonListVal from " + "(select trim(c.comp_nm), sect_maj_nm, a.sect_maj_cd,a.f_cd,trunc(tot_nos) nos_t, bal_dt , trunc(tcst_aft_com / tot_nos, 2) rt_acm," + " trunc(tcst_aft_com, 2) tcst_aft_com, round((tot_cost / tot_nos),2) c_rt, tot_cost, round(((tcst_aft_com / n.navtotalcostprice) * 100),2) cost_percent," + "a.adc_rt m_rt, a.dse_rt* tot_nos m_p,(a.dse_rt - trunc(tcst_aft_com / tot_nos, 2)) diff ,(round(a.dse_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain," + "n.navtotalcostprice asset from pfolio_bk a, comp c, nav.nav_master n where c.comp_cd = a.comp_cd and " + "a.f_cd =" + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode + " and navno = (select max(navno) from nav.nav_master where navfundid = " + fundCode + " AND NAVDATE<='" + balDate + "') order by c.comp_nm)p" + " group by p.sect_maj_cd,p.sect_maj_nm,p.f_cd,p.asset order by p.sect_maj_cd"; dtReprtSource = commonGatewayObj.Select(strSQL); strSQLGrandTotCostVal = "select sum(TotalCost) as GrandTotCostVal from " + "(select p.sect_maj_nm,p.f_cd, p.sect_maj_cd, sum(p.nos_t)TotalShares, sum(p.rt_acm),sum(p.tcst_aft_com)TotalCost,p.asset,sum(p.cost_percent)TotalCostPercent,sum(p.m_p)TotalMarketPrice," + cf_unlist + " NonListVal from " + "(select trim(c.comp_nm), sect_maj_nm, a.sect_maj_cd,a.f_cd,trunc(tot_nos) nos_t, bal_dt , trunc(tcst_aft_com / tot_nos, 2) rt_acm," + " trunc(tcst_aft_com, 2) tcst_aft_com, round((tot_cost / tot_nos),2) c_rt, tot_cost, round(((tcst_aft_com / n.navtotalcostprice) * 100),2) cost_percent," + "a.adc_rt m_rt, a.dse_rt* tot_nos m_p,(a.dse_rt - trunc(tcst_aft_com / tot_nos, 2)) diff ,(round(a.dse_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain," + "n.navtotalcostprice asset from pfolio_bk a, comp c, nav.nav_master n where c.comp_cd = a.comp_cd and " + "a.f_cd =" + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode + " and navno = (select max(navno) from nav.nav_master where navfundid = " + fundCode + " AND NAVDATE<='" + balDate + "') order by c.comp_nm)p" + " group by p.sect_maj_cd,p.sect_maj_nm,p.f_cd,p.asset order by p.sect_maj_cd)"; dtGrandTotCostVal = commonGatewayObj.Select(strSQLGrandTotCostVal); if (!dtGrandTotCostVal.Rows[0].IsNull("GrandTotCostVal")) { GrandTotCostVal = Convert.ToDouble(dtGrandTotCostVal.Rows[0]["GrandTotCostVal"].ToString()); GrandTotCostVal = GrandTotCostVal + cf_unlist; } else { GrandTotCostVal = 1; } if (dtReprtSource.Rows.Count > 0) { dtReprtSource.TableName = "AsstPercentageNAV"; dtReprtSource.WriteXmlSchema(@"E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report\xsdAsstPercentageNAV.xsd"); Path = Server.MapPath("Report/crtAssetPercNetAssetValueReport.rpt"); rdoc.Load(Path); rdoc.SetDataSource(dtReprtSource); CRV_AssetPercNAVSummaryAndPortfolio.ReportSource = rdoc; CRV_AssetPercNAVSummaryAndPortfolio.DisplayToolbar = true; CRV_AssetPercNAVSummaryAndPortfolio.HasExportButton = true; CRV_AssetPercNAVSummaryAndPortfolio.HasPrintButton = true; rdoc.SetParameterValue("prmbalDate", balDate); rdoc.SetParameterValue("prmStatementType", statementType); rdoc.SetParameterValue("prmFundName", fundName); rdoc.SetParameterValue("prmGrandTotCostVal", GrandTotCostVal); rdoc = ReportFactory.GetReport(rdoc.GetType()); } else { Response.Write("No Data Found"); } } }
protected void Page_Init(object sender, EventArgs e) { DataSetAssetClass imageDataSet = new DataSetAssetClass(); string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; OracleConnection conn = new OracleConnection(connStr); OracleCommand cmdl; OracleDataAdapter oradata; DataTable dtc; string AsOnDate = Request.QueryString["AsOnDate"].ToString(); string MakeAsOnDate = Request.QueryString["AsOnDate"].ToString(); string[] MakeAsOnDateSplit = MakeAsOnDate.Split('-'); String AsOnDateTemp = MakeAsOnDateSplit[0].Replace("/", "-"); DateTime AsOnDateNewD = DateTime.ParseExact(AsOnDateTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture); string AsOnDateNew = AsOnDateNewD.ToString("dd-MM-yyyy"); DateTime curDate = AsOnDateNewD; DateTime startDate = curDate.AddMonths(-1); DateTime LastDateTemp = curDate.AddDays(-(curDate.Day)); string LastDate = LastDateTemp.ToString("dd-MM-yyyy"); string LastMonth = startDate.ToString("MM-yyyy"); string CurrentMonth = AsOnDateNewD.ToString("MM-yyyy"); // string sqlString = " SELECT PI.ITEM_ID, PI.ITEM_NAME, nvl(BEGPFSIH.FINAL_STOCK_WT,0) AS BEG_FSTOCK_WT, nvl(BEGPFSIH.ITEM_AVG_RATE,0) AS BEG_AVG_RATE, ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) AS BEG_AMT, nvl(PPMD.ITEM_WEIGHT,0) AS PURCHASE_WTD, ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2) AS PURCHASE_AMTD, nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) AS PRODUCTION_WT, ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) AS PRODUCTION_AMT, ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS PROD_PRO_COST_AMT, ROUND((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS PROD_TOTAL_COST_AMT, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) AS AVAIL_SALE_WT, ROUND((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)),3) AS AVAIL_SALE_AMT, ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/(nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2) AS AVAIL_SALE_AVG_RATE, nvl(PSM.ITEM_WEIGHT,0) AS SALE_WT, nvl(PSMR.ITEM_WEIGHT,0) AS SALE_RETURN_WET, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)) AS END_FSTOCK_WT,ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)))* ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/(nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2),3) AS END_AMT, ROUND((((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)))* ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/(nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2)) / ((nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0)+nvl(PSMR.ITEM_WEIGHT,0))),3) AS END_AVG_RATE FROM PF_ITEM PI LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPRSIH ON PI.ITEM_ID = BEGPRSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_FG_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPFSIH ON PI.ITEM_ID = BEGPFSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT,0)) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + LastMonth + "' GROUP BY ITEM_ID) BEGPPM ON PI.ITEM_ID = BEGPPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT, sum(ITEM_AMOUNT) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPM ON PI.ITEM_ID = PPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_PURCHASE_MASTER WHERE PUR_TYPE_ID = 1 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMD ON PI.ITEM_ID = PPMD.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT_IN_FG) AS ITEM_WEIGHT_IN_FG FROM PF_PRODUCTION_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMAS ON PI.ITEM_ID = PPMAS.ITEM_ID LEFT JOIN (SELECT ITEM_ID, COST_RATE FROM PF_PROCESSING_COST WHERE TO_CHAR(TO_DATE(MONTH_YEAR), 'mm-YYYY') = '" + CurrentMonth + "' ) PPC ON PI.ITEM_ID = PPC.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN IS NULL GROUP BY ITEM_ID) PSM ON PI.ITEM_ID = PSM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN = 'Yes' GROUP BY ITEM_ID) PSMR ON PI.ITEM_ID = PSMR.ITEM_ID ORDER BY PI.ITEM_ID "; // string sqlString = " SELECT PI.ITEM_ID, PI.ITEM_NAME, nvl(BEGPFSIH.FINAL_STOCK_WT,0) AS BEG_FSTOCK_WT, nvl(BEGPFSIH.ITEM_AVG_RATE,0) AS BEG_AVG_RATE, ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) AS BEG_AMT, nvl(PPMD.ITEM_WEIGHT,0) AS PURCHASE_WTD, nvl(PPMD.ITEM_AMOUNT,0) AS PURCHASE_AMTD, nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) AS PRODUCTION_WT, nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) AS PRODUCTION_AMT, ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS PROD_PRO_COST_AMT, nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) + ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS PROD_TOTAL_COST_AMT, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) AS AVAIL_SALE_WT, ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) + nvl(PPMD.ITEM_AMOUNT,0) + nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) + ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS AVAIL_SALE_AMT, ROUND((ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) + nvl(PPMD.ITEM_AMOUNT,0) + nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) + ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3)) / (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2) AS AVAIL_SALE_AVG_RATE, nvl(PSM.ITEM_WEIGHT,0) AS SALE_WT, nvl(PSMR.ITEM_WEIGHT,0) AS SALE_RETURN_WET, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)) AS END_FSTOCK_WT, nvl((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)),0) * ROUND((ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) + nvl(PPMD.ITEM_AMOUNT,0) + nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) + ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3)) / (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2) AS END_AMT, nvl((nvl((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)),0) * ROUND((ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) + nvl(PPMD.ITEM_AMOUNT,0) + nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) + ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3)) / (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2)) / nullif((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)),0),0) AS END_AVG_RATE FROM PF_ITEM PI LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPRSIH ON PI.ITEM_ID = BEGPRSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_FG_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPFSIH ON PI.ITEM_ID = BEGPFSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT,0)) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + LastMonth + "' GROUP BY ITEM_ID) BEGPPM ON PI.ITEM_ID = BEGPPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT, sum(ITEM_AMOUNT) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPM ON PI.ITEM_ID = PPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT, sum(ITEM_AMOUNT) AS ITEM_AMOUNT FROM PF_SALES_MASTER WHERE PUR_TYPE_ID = 1 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMD ON PI.ITEM_ID = PPMD.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT_IN_FG FROM PF_PRODUCTION_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMAS ON PI.ITEM_ID = PPMAS.ITEM_ID LEFT JOIN (SELECT ITEM_ID, COST_RATE FROM PF_PROCESSING_COST WHERE TO_CHAR(TO_DATE(MONTH_YEAR), 'mm-YYYY') = '" + CurrentMonth + "' ) PPC ON PI.ITEM_ID = PPC.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN IS NULL GROUP BY ITEM_ID) PSM ON PI.ITEM_ID = PSM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN = 'Yes' GROUP BY ITEM_ID) PSMR ON PI.ITEM_ID = PSMR.ITEM_ID WHERE ( ((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0))) > 0 OR nvl(BEGPFSIH.FINAL_STOCK_WT,0) > 0 OR nvl(PSM.ITEM_WEIGHT,0) > 0) ORDER BY PI.ITEM_ID "; string sqlString = " SELECT PI.ITEM_ID, PI.ITEM_NAME, nvl(BEGPFSIH.FINAL_STOCK_WT,0) AS BEG_FSTOCK_WT, nvl(BEGPFSIH.ITEM_AVG_RATE,0) AS BEG_AVG_RATE, ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) AS BEG_AMT, nvl(PPMD.ITEM_WEIGHT,0) AS PURCHASE_WTD, ROUND(nvl((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1 )* nvl(PPMD.ITEM_WEIGHT,0),0),2) AS PURCHASE_AMTD, nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) AS PRODUCTION_WT, nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1 )* nvl(PPMD.ITEM_WEIGHT,0),2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) AS PRODUCTION_AMT, ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS PROD_PRO_COST_AMT, ROUND(nvl((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),0),3) AS PROD_TOTAL_COST_AMT, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) AS AVAIL_SALE_WT, ROUND(nvl((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)),0),3) AS AVAIL_SALE_AMT, ROUND(nvl(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/ nullif((nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),0) * 1,0),2) AS AVAIL_SALE_AVG_RATE, nvl(PSM.ITEM_WEIGHT,0) AS SALE_WT, nvl(PSM.ITEM_AMOUNT,0) AS SALES_AMOUNT, nvl(PSMR.ITEM_WEIGHT,0) AS SALE_RETURN_WET, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)) AS END_FSTOCK_WT, ROUND(nvl(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)))* ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/(nullif((nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),0) * 1),2),0),3) AS END_AMT, ROUND(nvl((((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)))* ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1) * nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1) * nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/(nullif((nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),0) * 1),2)) / nullif(((nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0)+nvl(PSMR.ITEM_WEIGHT,0))),0),0),3) AS END_AVG_RATE FROM PF_ITEM PI LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPRSIH ON PI.ITEM_ID = BEGPRSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_FG_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPFSIH ON PI.ITEM_ID = BEGPFSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT,0)+nvl(VAT_AMOUNT,0)) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + LastMonth + "' GROUP BY ITEM_ID) BEGPPM ON PI.ITEM_ID = BEGPPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT,0)+nvl(VAT_AMOUNT,0)) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPM ON PI.ITEM_ID = PPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE PUR_TYPE_ID = 1 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMD ON PI.ITEM_ID = PPMD.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT_IN_FG FROM PF_PRODUCTION_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMAS ON PI.ITEM_ID = PPMAS.ITEM_ID LEFT JOIN (SELECT ITEM_ID, nvl(COST_RATE,0) AS COST_RATE FROM PF_PROCESSING_COST WHERE TO_CHAR(TO_DATE(MONTH_YEAR), 'mm-YYYY') = '" + CurrentMonth + "' AND nvl(COST_RATE,0) !=0 ORDER BY ITEM_ID) PPC ON PI.ITEM_ID = PPC.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, SUM(CASE WHEN SALES_MODE = 'Local' THEN nvl(ITEM_AMOUNT,0)+nvl(VAT_AMOUNT,0) WHEN SALES_MODE = 'Export' THEN nvl(ITEM_AMOUNT,0) END) AS ITEM_AMOUNT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN IS NULL GROUP BY ITEM_ID) PSM ON PI.ITEM_ID = PSM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN = 'Yes' GROUP BY ITEM_ID) PSMR ON PI.ITEM_ID = PSMR.ITEM_ID WHERE ( ((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0))) > 0 OR nvl(BEGPFSIH.FINAL_STOCK_WT,0) > 0 OR nvl(PSM.ITEM_WEIGHT,0) > 0) ORDER BY PI.ITEM_ID "; OracleCommand cmd = new OracleCommand(sqlString, conn); cmd.CommandType = CommandType.Text; cmd.CommandText = sqlString; OracleDataAdapter dt = new OracleDataAdapter(cmd); conn.Open(); dt.Fill(imageDataSet.Tables["reportTable"]); // for processing cost string makeSQL = " SELECT nvl(PPC.COST_RATE,0) AS COST_RATE FROM PF_ITEM PI LEFT JOIN PF_PROCESSING_COST PPC ON PPC.ITEM_ID = PI.ITEM_ID WHERE TO_CHAR(TO_DATE(MONTH_YEAR), 'mm-YYYY') = '" + CurrentMonth + "' ORDER BY PI.ITEM_ID asc "; cmdl = new OracleCommand(makeSQL); oradata = new OracleDataAdapter(cmdl.CommandText, conn); dtc = new DataTable(); oradata.Fill(dtc); int RowCount = dtc.Rows.Count; double[] items_cost = new double[100]; for (int i = 0; i < RowCount; i++) { items_cost[i] = Convert.ToDouble(dtc.Rows[i]["COST_RATE"]); } double HDPE = items_cost[0]; double LDPE = items_cost[1]; double PC = items_cost[2]; double PET = items_cost[3]; double PP = items_cost[4]; double PVC = items_cost[5]; double MIX_PLASTIC = items_cost[6]; double XLPE = items_cost[7]; double PS = items_cost[8]; ReportDocument crystalReport = new ReportDocument(); crystalReport.Load(Server.MapPath("~/PF/PF_Reports/Pf_Fg_Statement_Report.rpt")); crystalReport.SetDataSource(imageDataSet.Tables["reportTable"]); crystalReport.SetParameterValue("AsOnDate", AsOnDateNewD); // crystalReport.SetParameterValue("ItemCost", items_cost); crystalReport.SetParameterValue("HDPE", HDPE); crystalReport.SetParameterValue("LDPE", LDPE); crystalReport.SetParameterValue("PC", PC); crystalReport.SetParameterValue("PET", PET); crystalReport.SetParameterValue("PP", PP); crystalReport.SetParameterValue("PVC", PVC); crystalReport.SetParameterValue("MIX_PLASTIC", MIX_PLASTIC); crystalReport.SetParameterValue("XLPE", XLPE); crystalReport.SetParameterValue("PS", PS); string datetime = DateTime.Now.ToString("dd-MM-yyyy"); CrystalReportViewer1.ID = "FG_Statement_Report_(As_On_Date)_" + datetime + ""; CrystalReportViewer1.ReportSource = crystalReport; conn.Close(); }
public void Labelnumbers() { if (ddlproduct.SelectedValue != "0") { int productid = Convert.ToInt32(ddlproduct.SelectedValue); // string strPattern = "([a-z?])[_ ]?([A-Z])"; DataTable dtAudit = new DataTable(); dtAudit.Columns.Add("KeyDataName", typeof(string)); dtAudit.Columns.Add("KeyDataValue", typeof(string)); dtAudit.Columns.Add("KeyDataDesc", typeof(string)); dtAudit.Columns.Add("DataName", typeof(string)); dtAudit.Columns.Add("DataType", typeof(string)); dtAudit.Columns.Add("ActionTaken", typeof(string)); dtAudit.Columns.Add("OldValue", typeof(string)); dtAudit.Columns.Add("NewValue", typeof(string)); dtAudit.Columns.Add("DateDone", typeof(string)); dtAudit.Columns.Add("DoneBy", typeof(string)); int nProdID = productid; //Product id //Get All Label No. Activities DataTable dtAuLogGPLS = st_dll.ProductLabelGPLS(nProdID); DataTable dtAuLabelNos = st_dll.LabelNosGPLS(nProdID); if (dtAuLabelNos != null && dtAuLabelNos.Rows.Count > 0) { for (int i = 0; i < dtAuLabelNos.Rows.Count; i++) { if (dtAuLabelNos.Rows[i]["DatePrinted"] != DBNull.Value) { string strType = dtAuLabelNos.Columns["DatePrinted"].DataType.ToString(); DataRow dR = dtAudit.NewRow(); dR["KeyDataName"] = "PRODUCT ID"; dR["KeyDataValue"] = nProdID.ToString(); dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString(); dR["DataType"] = strType; dR["DataName"] = "Date Printed (" + dtAuLabelNos.Rows[i]["CtrlID"].ToString() + "-" + dtAuLabelNos.Rows[i]["CategoryCode"].ToString() + "-" + Convert.ToInt16(dtAuLabelNos.Rows[i]["LabelNo"]).ToString("000") + ")"; dR["OldValue"] = DBNull.Value; dR["NewValue"] = dtAuLabelNos.Rows[i]["DatePrinted"].ToString(); dR["ActionTaken"] = "2"; dR["Datedone"] = dtAuLabelNos.Rows[i]["DatePrinted"].ToString(); dR["DoneBy"] = st_dll.UserFullName(Convert.ToInt16(dtAuLabelNos.Rows[i]["PrintedByID"])); dtAudit.Rows.Add(dR); } } //Voided for (int i = 0; i < dtAuLabelNos.Rows.Count; i++) { if (dtAuLabelNos.Rows[i]["DateVoided"] != DBNull.Value) { string strType = dtAuLabelNos.Columns["DateVoided"].DataType.ToString(); DataRow dR = dtAudit.NewRow(); dR["KeyDataName"] = "PRODUCT ID"; dR["KeyDataValue"] = nProdID.ToString(); dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString(); dR["DataType"] = strType; dR["DataName"] = "DateVoided (" + dtAuLabelNos.Rows[i]["CtrlID"].ToString() + "-" + dtAuLabelNos.Rows[i]["CategoryCode"].ToString() + "-" + Convert.ToInt16(dtAuLabelNos.Rows[i]["LabelNo"]).ToString("000") + ")"; dR["OldValue"] = DBNull.Value; dR["NewValue"] = dtAuLabelNos.Rows[i]["DateVoided"].ToString(); dR["ActionTaken"] = "2"; dR["Datedone"] = dtAuLabelNos.Rows[i]["DateVoided"].ToString(); dR["DoneBy"] = st_dll.UserFullName(Convert.ToInt16(dtAuLabelNos.Rows[i]["VoidedByID"])); dtAudit.Rows.Add(dR); } } //Approved for (int i = 0; i < dtAuLabelNos.Rows.Count; i++) { if (dtAuLabelNos.Rows[i]["DateApproved"] != DBNull.Value) { string strType = dtAuLabelNos.Columns["DateApproved"].DataType.ToString(); DataRow dR = dtAudit.NewRow(); dR["KeyDataName"] = "PRODUCT ID"; dR["KeyDataValue"] = nProdID.ToString(); dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString(); dR["DataType"] = strType; dR["DataName"] = "DateApproved (" + dtAuLabelNos.Rows[i]["CtrlID"].ToString() + "-" + dtAuLabelNos.Rows[i]["CategoryCode"].ToString() + "-" + Convert.ToInt16(dtAuLabelNos.Rows[i]["LabelNo"]).ToString("000") + ")"; dR["OldValue"] = DBNull.Value; dR["NewValue"] = dtAuLabelNos.Rows[i]["DateApproved"].ToString(); dR["ActionTaken"] = "2"; dR["Datedone"] = dtAuLabelNos.Rows[i]["DateApproved"].ToString(); dR["DoneBy"] = st_dll.UserFullName(Convert.ToInt16(dtAuLabelNos.Rows[i]["ApprovedByID"])); dtAudit.Rows.Add(dR); } } //Added for (int i = 0; i < dtAuLabelNos.Rows.Count; i++) { if (dtAuLabelNos.Rows[i]["LabelStatus"].ToString() == "2") { string strType = dtAuLabelNos.Columns["DateApproved"].DataType.ToString(); DataRow dR = dtAudit.NewRow(); dR["KeyDataName"] = "PRODUCT ID"; dR["KeyDataValue"] = nProdID.ToString(); dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString(); dR["DataType"] = strType; dR["DataName"] = "DateApproved (" + dtAuLabelNos.Rows[i]["CtrlID"].ToString() + "-" + dtAuLabelNos.Rows[i]["CategoryCode"].ToString() + "-" + Convert.ToInt16(dtAuLabelNos.Rows[i]["LabelNo"]).ToString("000") + ")"; dR["OldValue"] = DBNull.Value; dR["NewValue"] = dtAuLabelNos.Rows[i]["DateApproved"].ToString(); dR["ActionTaken"] = "1"; dR["Datedone"] = dtAuLabelNos.Rows[i]["DateApproved"].ToString(); dR["DoneBy"] = st_dll.UserFullName(Convert.ToInt16(dtAuLabelNos.Rows[i]["ApprovedByID"])); dtAudit.Rows.Add(dR); } } //Rejected for (int i = 0; i < dtAuLabelNos.Rows.Count; i++) { if (dtAuLabelNos.Rows[i]["DateRejected"] != DBNull.Value) { string strType = dtAuLabelNos.Columns["DateRejected"].DataType.ToString(); DataRow dR = dtAudit.NewRow(); dR["KeyDataName"] = "PRODUCT ID"; dR["KeyDataValue"] = nProdID.ToString(); dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString(); dR["DataType"] = strType; dR["DataName"] = "DateRejected (" + dtAuLabelNos.Rows[i]["CtrlID"].ToString() + "-" + dtAuLabelNos.Rows[i]["CategoryCode"].ToString() + "-" + Convert.ToInt16(dtAuLabelNos.Rows[i]["LabelNo"]).ToString("000") + ")"; dR["OldValue"] = DBNull.Value; dR["NewValue"] = dtAuLabelNos.Rows[i]["DateRejected"].ToString(); dR["ActionTaken"] = "2"; dR["Datedone"] = dtAuLabelNos.Rows[i]["DateRejected"].ToString(); dR["DoneBy"] = st_dll.UserFullName(Convert.ToInt16(dtAuLabelNos.Rows[i]["RejectedByID"])); dtAudit.Rows.Add(dR); } } } if (dtAudit == null || dtAudit.Rows.Count == 0) { ErrorMessage("No audit records found."); return; } //AuditRpt rpt = new AuditRpt(); //rpt.rptName = "Audit Products"; //rpt.dt = dtAudit; //rpt.WindowState = FormWindowState.Maximized; //rpt.Show(); rptDoc = new ReportDocument(); CrystalReportViewer1.HasToggleGroupTreeButton = false; CrystalReportViewer1.BestFitPage = false; CrystalReportViewer1.Width = 920; rptDoc.Load(Server.MapPath("~/Reports/AuditProducts.rpt")); //rptDoc.SetParameterValue("@PRODUCTID", productid); rptDoc.SetDataSource(dtAudit); rptDoc.SetDatabaseLogon("sa", "Pass2018", "172.16.4.12", "PLSAudit"); rptDoc.DataSourceConnections[0].SetConnection("172.16.4.12", "PLSAudit", "sa", "Pass2018"); CrystalReportViewer1.Page.Title = "Audit Label Numbers"; rptDoc.SummaryInfo.ReportTitle = "Audit Label Numbers"; rptDoc.DataDefinition.FormulaFields["cTitle"].Text = "'Audit Trail : Label Numbers'"; CrystalReportViewer1.ReportSource = rptDoc; Response.ClearContent(); Response.ClearHeaders(); Response.ContentType = "application/pdf"; rptDoc.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, true, "Audit Label Numbers"); CrystalReportViewer1.ToolPanelView = ToolPanelViewType.None; CrystalReportViewer1.SeparatePages = true; CrystalReportViewer1.DataBind(); CrystalReportViewer1.HasExportButton = false; Response.Flush(); Response.Close(); } else { ErrorMessage("Please select the Product/Component."); } }
protected void Page_Load(object sender, EventArgs e) { string fundCode = ""; string branchCode = ""; if (BaseContent.IsSessionExpired()) { Response.Redirect("../../Default.aspx"); return; } bcContent = (BaseClass)Session["BCContent"]; userObj.UserID = bcContent.LoginID.ToString(); branchCode = (string)Session["branchCode"]; fundCode = (string)Session["fundCode"]; DataTable dtUnitHolderInfo = reportObj.getDtHolderInfo(); dtUnitHolderInfo.TableName = "UnitHolderInfo"; DataRow drUnitHolderInfo; DataTable dtHolderInfo = (DataTable)Session["dtHolderInfo"]; if (dtHolderInfo.Rows.Count > 0) { for (int looper = 0; looper < dtHolderInfo.Rows.Count; looper++) { drUnitHolderInfo = dtUnitHolderInfo.NewRow(); drUnitHolderInfo["REG_NO"] = dtHolderInfo.Rows[looper]["REG_NO"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["REG_NO"].ToString(); drUnitHolderInfo["REG_DT"] = dtHolderInfo.Rows[looper]["REG_DT"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["REG_DT"].ToString(); if (!dtHolderInfo.Rows[looper]["REG_TYPE"].Equals(DBNull.Value)) { drUnitHolderInfo["REG_TYPE"] = reportObj.getRegTypeFullName(dtHolderInfo.Rows[looper]["REG_TYPE"].ToString()).ToString(); } drUnitHolderInfo["HNAME"] = dtHolderInfo.Rows[looper]["HNAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["HNAME"].ToString(); drUnitHolderInfo["FMH_NAME"] = dtHolderInfo.Rows[looper]["FMH_NAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["FMH_NAME"].ToString(); drUnitHolderInfo["MO_NAME"] = dtHolderInfo.Rows[looper]["MO_NAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["MO_NAME"].ToString(); drUnitHolderInfo["ADDRS1"] = dtHolderInfo.Rows[looper]["ADDRS1"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["ADDRS1"].ToString(); drUnitHolderInfo["ADDRS2"] = dtHolderInfo.Rows[looper]["ADDRS2"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["ADDRS2"].ToString(); drUnitHolderInfo["CITY"] = dtHolderInfo.Rows[looper]["CITY"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["CITY"].ToString(); drUnitHolderInfo["NATIONALITY"] = dtHolderInfo.Rows[looper]["NATIONALITY"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["NATIONALITY"].ToString(); drUnitHolderInfo["OCC_CODE"] = dtHolderInfo.Rows[looper]["OCC_CODE"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["OCC_CODE"].ToString(); drUnitHolderInfo["B_DATE"] = dtHolderInfo.Rows[looper]["B_DATE"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["B_DATE"].ToString(); if (!dtHolderInfo.Rows[looper]["SEX"].Equals(DBNull.Value)) { if (string.Compare(dtHolderInfo.Rows[looper]["SEX"].ToString(), "M", true) == 0) { drUnitHolderInfo["SEX"] = "MAlE"; } else if (string.Compare(dtHolderInfo.Rows[looper]["SEX"].ToString(), "F", true) == 0) { drUnitHolderInfo["SEX"] = "FEMALE"; } } if (!dtHolderInfo.Rows[looper]["MAR_STAT"].Equals(DBNull.Value)) { drUnitHolderInfo["MAR_STAT"] = reportObj.getMaritialFullName(dtHolderInfo.Rows[looper]["MAR_STAT"].ToString()).ToString(); } if (!dtHolderInfo.Rows[looper]["RELIGION"].Equals(DBNull.Value)) { drUnitHolderInfo["RELIGION"] = reportObj.getReligionFullName(dtHolderInfo.Rows[looper]["RELIGION"].ToString()).ToString(); } drUnitHolderInfo["EDU_QUA"] = dtHolderInfo.Rows[looper]["EDU_QUA"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["EDU_QUA"].ToString(); drUnitHolderInfo["TEL_NO"] = dtHolderInfo.Rows[looper]["TEL_NO"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["TEL_NO"].ToString(); drUnitHolderInfo["EMAIL"] = dtHolderInfo.Rows[looper]["EMAIL"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["EMAIL"].ToString(); drUnitHolderInfo["JNT_NAME"] = dtHolderInfo.Rows[looper]["JNT_NAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_NAME"].ToString(); drUnitHolderInfo["JNT_FMH_NAME"] = dtHolderInfo.Rows[looper]["JNT_FMH_NAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_FMH_NAME"].ToString(); drUnitHolderInfo["JNT_MO_NAME"] = dtHolderInfo.Rows[looper]["JNT_MO_NAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_MO_NAME"].ToString(); drUnitHolderInfo["JNT_OCC_CODE"] = dtHolderInfo.Rows[looper]["JNT_OCC_CODE"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_OCC_CODE"].ToString(); drUnitHolderInfo["JNT_ADDRS1"] = dtHolderInfo.Rows[looper]["JNT_ADDRS1"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_ADDRS1"].ToString(); drUnitHolderInfo["JNT_ADDRS2"] = dtHolderInfo.Rows[looper]["JNT_ADDRS2"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_ADDRS2"].ToString(); drUnitHolderInfo["JNT_NATIONALITY"] = dtHolderInfo.Rows[looper]["JNT_NATIONALITY"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_NATIONALITY"].ToString(); drUnitHolderInfo["JNT_CITY"] = dtHolderInfo.Rows[looper]["JNT_CITY"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_CITY"].ToString(); drUnitHolderInfo["JNT_TEL_NO"] = dtHolderInfo.Rows[looper]["JNT_TEL_NO"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_TEL_NO"].ToString(); drUnitHolderInfo["JNT_FMH_REL"] = dtHolderInfo.Rows[looper]["JNT_FMH_REL"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_FMH_REL"].ToString(); drUnitHolderInfo["CIP"] = dtHolderInfo.Rows[looper]["CIP"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["CIP"].ToString(); if (!dtHolderInfo.Rows[looper]["CIP"].Equals(DBNull.Value)) { if (string.Compare(dtHolderInfo.Rows[looper]["CIP"].ToString(), "Y", true) == 0) { drUnitHolderInfo["CIP"] = "YES"; } else if (string.Compare(dtHolderInfo.Rows[looper]["CIP"].ToString(), "N", true) == 0) { drUnitHolderInfo["CIP"] = "NO"; } } if (!dtHolderInfo.Rows[looper]["ID_FLAG"].Equals(DBNull.Value)) { if (string.Compare(dtHolderInfo.Rows[looper]["ID_FLAG"].ToString(), "Y", true) == 0) { drUnitHolderInfo["ID_FLAG"] = "YES"; drUnitHolderInfo["ID_AC"] = dtHolderInfo.Rows[looper]["ID_AC"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["ID_AC"].ToString(); drUnitHolderInfo["ID_INTSTITUTE_NAME"] = reportObj.getBankNameByBankCode(Convert.ToInt16(dtHolderInfo.Rows[0]["ID_BK_NM_CD"].Equals(DBNull.Value) ? "0" : dtHolderInfo.Rows[0]["ID_BK_NM_CD"].ToString())).ToString(); drUnitHolderInfo["ID_INTSTITUTE_BRANCH_NAME"] = reportObj.getBankBranchNameByCode(Convert.ToInt16(dtHolderInfo.Rows[0]["ID_BK_NM_CD"].Equals(DBNull.Value) ? "0" : dtHolderInfo.Rows[0]["ID_BK_NM_CD"].ToString()), Convert.ToInt16(dtHolderInfo.Rows[0]["ID_BK_BR_NM_CD"].Equals(DBNull.Value) ? "0" : dtHolderInfo.Rows[0]["ID_BK_BR_NM_CD"].ToString())).ToString(); } else if (string.Compare(dtHolderInfo.Rows[looper]["ID_FLAG"].ToString(), "N", true) == 0) { drUnitHolderInfo["ID_FLAG"] = "NO"; } } if (!dtHolderInfo.Rows[looper]["BK_FLAG"].Equals(DBNull.Value)) { if (string.Compare(dtHolderInfo.Rows[looper]["BK_FLAG"].ToString(), "Y", true) == 0) { if (!dtHolderInfo.Rows[looper]["BK_NM_CD"].Equals(DBNull.Value) && !dtHolderInfo.Rows[looper]["BK_BR_NM_CD"].Equals(DBNull.Value) && !dtHolderInfo.Rows[looper]["BK_AC_NO"].Equals(DBNull.Value)) { drUnitHolderInfo["BK_AC_NO"] = dtHolderInfo.Rows[looper]["BK_AC_NO"].ToString(); drUnitHolderInfo["BANK_NAME"] = reportObj.getBankNameByBankCode(Convert.ToInt16(dtHolderInfo.Rows[looper]["BK_NM_CD"].ToString())).ToString(); drUnitHolderInfo["BRANCH_NAME"] = reportObj.getBankBranchNameByCode(Convert.ToInt16(dtHolderInfo.Rows[looper]["BK_NM_CD"].ToString()), Convert.ToInt16(dtHolderInfo.Rows[looper]["BK_BR_NM_CD"].ToString())).ToString(); drUnitHolderInfo["BRANCH_ADDRESS"] = reportObj.getBankBranchAddressByCode(Convert.ToInt16(dtHolderInfo.Rows[looper]["BK_NM_CD"].ToString()), Convert.ToInt16(dtHolderInfo.Rows[looper]["BK_BR_NM_CD"].ToString())).ToString(); } else { string branchAddress = ""; string BankAccInfo = dtHolderInfo.Rows[looper]["SPEC_IN1"].ToString() + dtHolderInfo.Rows[looper]["SPEC_IN2"].ToString(); string[] BankAccountInfo = BankAccInfo.Split(','); if (BankAccountInfo.Length > 0) { drUnitHolderInfo["BK_AC_NO"] = BankAccountInfo[0].ToString(); if (BankAccountInfo.Length > 1) { drUnitHolderInfo["BANK_NAME"] = BankAccountInfo[1].ToString(); } if (BankAccountInfo.Length > 2) { drUnitHolderInfo["BRANCH_NAME"] = BankAccountInfo[2].ToString(); } if (BankAccountInfo.Length > 3) { for (int loop = 3; loop < BankAccountInfo.Length; loop++) { branchAddress = branchAddress + BankAccountInfo[loop].ToString(); } drUnitHolderInfo["BRANCH_ADDRESS"] = branchAddress; } } } } } DataTable dtNominee = reportObj.dtNominee(dtHolderInfo.Rows[looper]["REG_BK"].ToString(), dtHolderInfo.Rows[looper]["REG_BR"].ToString(), Convert.ToInt32(dtHolderInfo.Rows[looper]["R_NO"].ToString())); if (dtNominee.Rows.Count > 0) { for (int loop = 0; loop < dtNominee.Rows.Count; loop++) { if (Convert.ToInt16(dtNominee.Rows[loop]["NOMI_NO"].ToString()) == 1) { drUnitHolderInfo["NOMI_CTL_NO"] = dtNominee.Rows[loop]["NOMI_CTL_NO"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_CTL_NO"].ToString(); drUnitHolderInfo["NOMI1_NAME"] = dtNominee.Rows[loop]["NOMI_NAME"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_NAME"].ToString(); drUnitHolderInfo["NOMI1_FMH_NAME"] = dtNominee.Rows[loop]["NOMI_FMH_NAME"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_FMH_NAME"].ToString(); drUnitHolderInfo["NOMI1_OCC_CODE"] = dtNominee.Rows[loop]["DESCR"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["DESCR"].ToString(); drUnitHolderInfo["NOMI1_ADDRS1"] = dtNominee.Rows[loop]["NOMI_ADDRS1"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_ADDRS1"].ToString(); drUnitHolderInfo["NOMI1_ADDRS2"] = dtNominee.Rows[loop]["NOMI_ADDRS2"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_ADDRS2"].ToString(); drUnitHolderInfo["NOMI1_CITY"] = dtNominee.Rows[loop]["NOMI_CITY"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_CITY"].ToString(); drUnitHolderInfo["NOMI1_NATIONALITY"] = dtNominee.Rows[loop]["NOMI_NATIONALITY"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_NATIONALITY"].ToString(); drUnitHolderInfo["NOMI1_NOMI_REL"] = dtNominee.Rows[loop]["NOMI_REL"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_REL"].ToString(); drUnitHolderInfo["NOMI1_PERCENTAGE"] = dtNominee.Rows[loop]["PERCENTAGE"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["PERCENTAGE"].ToString(); } else if (Convert.ToInt16(dtNominee.Rows[loop]["NOMI_NO"].ToString()) == 2) { drUnitHolderInfo["NOMI2_NAME"] = dtNominee.Rows[loop]["NOMI_NAME"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_NAME"].ToString(); drUnitHolderInfo["NOMI2_FMH_NAME"] = dtNominee.Rows[loop]["NOMI_FMH_NAME"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_FMH_NAME"].ToString(); drUnitHolderInfo["NOMI2_OCC_CODE"] = dtNominee.Rows[loop]["DESCR"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["DESCR"].ToString(); drUnitHolderInfo["NOMI2_ADDRS1"] = dtNominee.Rows[loop]["NOMI_ADDRS1"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_ADDRS1"].ToString(); drUnitHolderInfo["NOMI2_ADDRS2"] = dtNominee.Rows[loop]["NOMI_ADDRS2"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_ADDRS2"].ToString(); drUnitHolderInfo["NOMI2_CITY"] = dtNominee.Rows[loop]["NOMI_CITY"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_CITY"].ToString(); drUnitHolderInfo["NOMI2_NATIONALITY"] = dtNominee.Rows[loop]["NOMI_NATIONALITY"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_NATIONALITY"].ToString(); drUnitHolderInfo["NOMI2_NOMI_REL"] = dtNominee.Rows[loop]["NOMI_REL"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_REL"].ToString(); drUnitHolderInfo["NOMI2_PERCENTAGE"] = dtNominee.Rows[loop]["PERCENTAGE"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["PERCENTAGE"].ToString(); } } } dtUnitHolderInfo.Rows.Add(drUnitHolderInfo); } // dtUnitHolderInfo.WriteXmlSchema(@"D:\Project\Web\AMCL.OPENMF\AMCL.Web\UI\ReportViewer\Report\dtUnitHolderInfo.xsd"); string Path = Server.MapPath("Report/rptRegHolderInfo.rpt"); rdoc.Load(Path); rdoc.SetDataSource(dtUnitHolderInfo); CrystalReportViewer1.ReportSource = rdoc; rdoc.SetParameterValue("fundName", opendMFDAO.GetFundName(fundCode.ToString())); rdoc = ReportFactory.GetReport(rdoc.GetType()); } else { Response.Write("No data found"); } }
private void FilterReport(string action) { string foldername = Server.MapPath("~/CS_REPORT/rpt/ftmandytdsellinperchannelreport.rpt"); _User Ousr = new _User(Session["username"].ToString()); string[] No_Filter_Users = { "ceo", "vpbsm", "vptfi", "ssgm", "ssm", "brd", "admin" }; string[] User_more_roles = { "6", "14", "20", "21", "23", "31", "36", "52", "62", "64", "65", "71", "72", "77", "79", "90" }; string orig_ids = string.Join(",", User_more_roles); string tmp_ids = ""; DataTable user_counterid = SqlDbHelper.getDataDT("SELECT counterid FROM userheader WHERE username='******'"); foreach (DataRow ids in user_counterid.Rows) { tmp_ids = ids["counterid"].ToString(); } string val_region = txt_region.Text.ToUpper(); if (val_region.Trim() != "") { try { ReportDocument ReportDoc = new ReportDocument(); ReportDoc.Load(foldername); ReportDoc.SetDatabaseLogon("sa", "p@ssw0rd"); ParameterFields PFIELDS = ReportDoc.ParameterFields; ParameterDiscreteValue[] param_values = new ParameterDiscreteValue[] { }; ParameterDiscreteValue region_default_val = new ParameterDiscreteValue(); region_default_val.Value = "C"; ParameterDiscreteValue channel_default_val = new ParameterDiscreteValue(); channel_default_val.Value = "All"; ParameterDiscreteValue brand_default_val = new ParameterDiscreteValue(); brand_default_val.Value = "All"; PFIELDS["Region"].CurrentValues.Clear(); PFIELDS["Channel"].CurrentValues.Clear(); PFIELDS["Brand"].CurrentValues.Clear(); if (action == "button_click" && (Ousr.HasPositionsOf(No_Filter_Users) || orig_ids.IndexOf(tmp_ids) != -1)) { if (val_region == "LUZON") { region_default_val.Value = "CL"; } else if (val_region == "VISMIN") { region_default_val.Value = "CV"; } else { region_default_val.Value = "C"; } } else { ReportHelper.GetRegions(Session["username"].ToString(), "Region", ref PFIELDS, ref param_values); foreach (ParameterDiscreteValue pd in PFIELDS["Region"].CurrentValues) { if (pd.Value.ToString() == "LUZON") { pd.Value = "CL"; } if (pd.Value.ToString() == "VISMIN") { pd.Value = "CV"; } } } ReportHelper.GetChannels(Session["username"].ToString(), "Channel", ref PFIELDS, ref param_values); ReportHelper.GetBrands(Session["username"].ToString(), "Brand", ref PFIELDS, ref param_values); bool has_region = false, has_channel = false, has_brand = false; foreach (ParameterField pd in PFIELDS) { bool has_val = pd.CurrentValues.Count > 0 ? true : false; if (has_val) { switch (pd.Name.ToUpper()) { case "REGION": has_region = true; break; case "CHANNEL": has_channel = true; break; case "BRAND": has_brand = true; break; } } } if (!has_region) { ReportDoc.ParameterFields["Region"].CurrentValues.Add(region_default_val); } if (!has_channel) { ReportDoc.ParameterFields["Channel"].CurrentValues.Add(channel_default_val); } if (!has_brand) { ReportDoc.ParameterFields["Brand"].CurrentValues.Add(brand_default_val); } CrystalReportViewer1.ReportSource = ReportDoc; } catch (Exception ex) { } } }
public void ProductLabelReport() { if (ddlproduct.SelectedValue != "0") { int productid = Convert.ToInt32(ddlproduct.SelectedValue); string strPattern = "([a-z?])[_ ]?([A-Z])"; DataTable dtAudit = new DataTable(); dtAudit.Columns.Add("KeyDataName", typeof(string)); dtAudit.Columns.Add("KeyDataValue", typeof(string)); dtAudit.Columns.Add("KeyDataDesc", typeof(string)); dtAudit.Columns.Add("DataName", typeof(string)); dtAudit.Columns.Add("DataType", typeof(string)); dtAudit.Columns.Add("ActionTaken", typeof(string)); dtAudit.Columns.Add("OldValue", typeof(string)); dtAudit.Columns.Add("NewValue", typeof(string)); dtAudit.Columns.Add("DateDone", typeof(string)); dtAudit.Columns.Add("DoneBy", typeof(string)); int nProdID = productid;//Product id //Products Master Table // DataTable dtAuLogGPLS = GISClass.AuditReport.ProductsGPLS(nProdID); // DataTable dtAuLog = GISClass.AuditReport.AuProducts(nProdID); DataTable dtAuLogGPLS = st_dll.ProductLabelGPLS(nProdID); DataTable dtAuLog = st_dll.AuProductLabel(nProdID); if (dtAuLog != null && dtAuLog.Rows.Count > 0) { for (int j = 0; j < dtAuLog.Columns.Count; j++) { if (j < dtAuLog.Columns.Count - 3) { if (dtAuLogGPLS.Rows[0][j].ToString() != dtAuLog.Rows[0][j].ToString()) { string strType = dtAuLog.Columns[j].DataType.ToString(); DataRow dR = dtAudit.NewRow(); dR["KeyDataName"] = "Product ID"; dR["KeyDataValue"] = dtAuLog.Rows[0]["ProductID"].ToString(); dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString(); dR["DataName"] = Regex.Replace(dtAuLog.Columns[j].ColumnName.ToString(), strPattern, "$1 $2"); dR["DataType"] = strType; dR["OldValue"] = dtAuLog.Rows[0][j]; dR["NewValue"] = dtAuLogGPLS.Rows[0][j]; dR["ActionTaken"] = dtAuLog.Rows[0]["FileMaintCode"].ToString(); dR["Datedone"] = dtAuLog.Rows[0]["FileMaintDate"].ToString(); dR["DoneBy"] = dtAuLog.Rows[0]["FileMaintByID"].GetType() == typeof(string) ? st_dll.GetUserFullName(dtAuLog.Rows[0]["FileMaintByID"].ToString()) : st_dll.UserFullName(Convert.ToInt16(dtAuLog.Rows[0]["FileMaintByID"])); dtAudit.Rows.Add(dR); } } } int k = 1; for (int i = 0; i < dtAuLog.Rows.Count; i++) { if (k >= dtAuLog.Rows.Count) { break; } for (int j = 0; j < dtAuLog.Columns.Count; j++) { if (j < dtAuLog.Columns.Count - 3) { if (dtAuLog.Rows[i][j].ToString() != dtAuLog.Rows[k][j].ToString()) { string strType = dtAuLog.Columns[j].DataType.ToString(); DataRow dR = dtAudit.NewRow(); dR["KeyDataName"] = "Product ID"; dR["KeyDataValue"] = dtAuLog.Rows[i]["ProductID"].ToString(); dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString(); dR["DataType"] = strType; dR["DataName"] = Regex.Replace(dtAuLog.Columns[j].ColumnName.ToString(), strPattern, "$1 $2"); dR["OldValue"] = dtAuLog.Rows[k][j]; dR["NewValue"] = dtAuLog.Rows[i][j]; dR["ActionTaken"] = dtAuLog.Rows[k]["FileMaintCode"].ToString(); dR["Datedone"] = dtAuLog.Rows[k]["FileMaintDate"].ToString(); dR["DoneBy"] = dtAuLog.Rows[k]["FileMaintByID"].GetType() == typeof(string) ? st_dll.GetUserFullName(dtAuLog.Rows[k]["FileMaintByID"].ToString()) : st_dll.UserFullName(Convert.ToInt16(dtAuLog.Rows[k]["FileMaintByID"])); dtAudit.Rows.Add(dR); } } } k++; } } if (dtAudit == null || dtAudit.Rows.Count == 0) { ErrorMessage("No audit records found."); return; } //AuditRpt rpt = new AuditRpt(); //rpt.rptName = "Audit Products"; //rpt.dt = dtAudit; //rpt.WindowState = FormWindowState.Maximized; //rpt.Show(); rptDoc = new ReportDocument(); CrystalReportViewer1.HasToggleGroupTreeButton = false; CrystalReportViewer1.BestFitPage = false; CrystalReportViewer1.Width = 920; rptDoc.Load(Server.MapPath("~/Reports/AuditProducts.rpt")); //rptDoc.SetParameterValue("@PRODUCTID", productid); rptDoc.SetDataSource(dtAudit); rptDoc.SetDatabaseLogon("sa", "Pass2018", "172.16.4.12", "PLSAudit"); rptDoc.DataSourceConnections[0].SetConnection("172.16.4.12", "PLSAudit", "sa", "Pass2018"); CrystalReportViewer1.Page.Title = "Audit ProductLabels"; rptDoc.SummaryInfo.ReportTitle = "Audit ProductLabels"; rptDoc.DataDefinition.FormulaFields["cTitle"].Text = "'Audit Trail : ProductLabels Master File'"; CrystalReportViewer1.ReportSource = rptDoc; Response.ClearContent(); Response.ClearHeaders(); Response.ContentType = "application/pdf"; rptDoc.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, true, "Audit ProductLabels"); CrystalReportViewer1.ToolPanelView = ToolPanelViewType.None; CrystalReportViewer1.SeparatePages = true; CrystalReportViewer1.DataBind(); CrystalReportViewer1.HasExportButton = false; Response.Flush(); Response.Close(); } else { ErrorMessage("Please select the Product/Component."); } }
private bool GenerarPDFOC(string _codEmp, int _NroOrdenCompra, string Email) { try { StaCatalina.Forms.Reports _Reporte = new Reports(); ReportDocument objReport = new ReportDocument(); String reportPath = ConfigurationManager.AppSettings["Reports"] + "\\Reporting\\" + "OrdenDeCompra.rpt"; objReport.Load(reportPath); objReport.Refresh(); objReport.ReportOptions.EnableSaveDataWithReport = false; // PARAMETROS DE CONEXION TableLogOnInfo logoninfo = new TableLogOnInfo(); logoninfo.ConnectionInfo.ServerName = ConfigurationManager.AppSettings["Source"]; logoninfo.ConnectionInfo.DatabaseName = ConfigurationManager.AppSettings["CatalogSTACATALINA"]; logoninfo.ConnectionInfo.UserID = ConfigurationManager.AppSettings["User ID"]; logoninfo.ConnectionInfo.Password = ConfigurationManager.AppSettings["Password"]; logoninfo.ConnectionInfo.IntegratedSecurity = false; Tables tables = objReport.Database.Tables; foreach (Table table in tables) { table.ApplyLogOnInfo(logoninfo); } //PAOS LOS PARAMTROS EN ORDEN objReport.SetParameterValue(0, _codEmp); objReport.SetParameterValue(1, "OC"); objReport.SetParameterValue(2, _NroOrdenCompra); objReport.SetParameterValue(3, _NroOrdenCompra); objReport.SetParameterValue(4, null); objReport.SetParameterValue(5, null); objReport.SetParameterValue(6, 1); objReport.SetParameterValue(7, ""); objReport.SetParameterValue(8, null); objReport.SetParameterValue(9, null); //GUARDO EN FORMATO PDF string _path = ConfigurationManager.AppSettings["PDF"] + "\\PDF\\" + "OrdenDeCompra.PDF"; objReport.ExportToDisk(ExportFormatType.PortableDocFormat, _path); //_codEmp = (_codEmp == "EGES") ? "EGESAC S.A." : "RSC S.A."; if (EnviarMail(_codEmp, _path, Email)) { return(true); } else { return(false); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return(false); } }
static void ExportToMSMail(string _codEmp, int _NroOrdenCompra) { StaCatalina.Forms.Reports _Reporte = new Reports(); ReportDocument objReport = new ReportDocument(); String reportPath = ConfigurationManager.AppSettings["Reports"] + "\\Reporting\\" + "OrdenDeCompra.rpt"; objReport.Load(reportPath); objReport.Refresh(); objReport.ReportOptions.EnableSaveDataWithReport = false; // PARAMETROS DE CONEXION TableLogOnInfo logoninfo = new TableLogOnInfo(); logoninfo.ConnectionInfo.ServerName = ConfigurationManager.AppSettings["Source"]; logoninfo.ConnectionInfo.DatabaseName = ConfigurationManager.AppSettings["CatalogSTACATALINA"]; logoninfo.ConnectionInfo.UserID = ConfigurationManager.AppSettings["User ID"]; logoninfo.ConnectionInfo.Password = ConfigurationManager.AppSettings["Password"]; logoninfo.ConnectionInfo.IntegratedSecurity = false; Tables tables = objReport.Database.Tables; foreach (Table table in tables) { table.ApplyLogOnInfo(logoninfo); } //PAOS LOS PARAMTROS EN ORDEN objReport.SetParameterValue(0, _codEmp); objReport.SetParameterValue(1, "OC"); objReport.SetParameterValue(2, _NroOrdenCompra); objReport.SetParameterValue(3, _NroOrdenCompra); objReport.SetParameterValue(4, null); objReport.SetParameterValue(5, null); objReport.SetParameterValue(6, 1); //PdfRtfWordFormatOptions pdfOpts = ExportOptions.CreatePdfRtfWordFormatOptions(); PdfFormatOptions pdfOpts = new PdfFormatOptions(); MicrosoftMailDestinationOptions mailOpts = ExportOptions.CreateMicrosoftMailDestinationOptions(); ExportOptions exportOpts = new ExportOptions(); pdfOpts.UsePageRange = false; exportOpts.ExportFormatOptions = pdfOpts; mailOpts.MailCCList = "*****@*****.**"; mailOpts.MailMessage = "PRUEBA DE CORREO OC HECTOR"; mailOpts.MailSubject = "PRUEBA"; mailOpts.MailToList = "*****@*****.**"; mailOpts.Password = "******"; mailOpts.UserName = "******"; exportOpts.ExportDestinationOptions = mailOpts; exportOpts.ExportDestinationType = ExportDestinationType.MicrosoftMail; exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat; objReport.Export(exportOpts); }
protected void Page_Load(object sender, EventArgs e) { try { if (Session["ReportData"] != null && Session["option"] != null) { string rptFullPath = ""; if (Session["option"].ToString() == "radByMainLanes" || Session["option"].ToString() == "radByServiceLanes") { rptFullPath = Server.MapPath(@".\RptFiles\MD_Prio_cost\JPMMS_VMIDMAINT_DECI.RPT"); } else if (Session["option"].ToString() == "radByIntersections") { rptFullPath = Server.MapPath(@".\RptFiles\MD_Prio_cost\JPMMS_VMIDIntSecMaint_Deci.RPT"); } else if (Session["option"].ToString() == "radByRegionNo" && Session["details"].ToString() == "details") { rptFullPath = Server.MapPath(@".\RptFiles\MD_Prio_cost\JPMMS_VSecst_Maint_deci_Subdist_udi.RPT"); } else if (Session["option"].ToString() == "radByRegionNo" && Session["details"].ToString() != "details") { rptFullPath = Server.MapPath(@".\RptFiles\MD_Prio_cost\rptMaintDecisionRegionWise.rpt"); } else { throw new Exception("Invalid report option!"); } DataTable dt = (DataTable)Session["ReportData"]; ReportDocument rpt = new ReportDocument(); rpt.Load(rptFullPath); rpt.SetDataSource(dt); if (Session["option"].ToString() == "radByMainLanes") { if (!string.IsNullOrEmpty(Session["title"].ToString())) { rpt.SetParameterValue("Title", Session["title"].ToString()); } else { rpt.SetParameterValue("Title", ""); } } Session.Remove("ReportData"); Session.Remove("title"); MemoryStream memStream; Response.Buffer = false; Response.Clear(); Response.ClearContent(); Response.ClearHeaders(); if (Request.QueryString["type"] == "x") { ExcelFormatOptions excelOptions = new ExcelFormatOptions(); excelOptions.ExcelUseConstantColumnWidth = false; rpt.ExportOptions.FormatOptions = excelOptions; memStream = (MemoryStream)rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.ExcelRecord); Response.ContentType = "application/vnd.ms-excel"; } else { memStream = (MemoryStream)rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); Response.ContentType = "application/pdf"; //memStream = (MemoryStream)rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.WordForWindows); //Response.ContentType = "application/msword"; } Response.BinaryWrite(memStream.ToArray()); Response.End(); memStream.Flush(); memStream.Close(); memStream.Dispose(); rpt.Close(); rpt.Dispose(); GC.Collect(); } else { Response.Redirect("MaintenanceDecisionsReport.aspx", false); } } catch (Exception ex) { Response.Write(ex.Message); } finally { Session["ReportData"] = null; } }
protected void Page_Load(object sender, EventArgs e) { // since we are using Crystal to do everything, the web page is built here if (!IsPostBack) { Session["dtPrint"] = null; if (Session["UserName"] == null) { UserPrefix = "NoUser"; } else { UserPrefix = Session["UserName"].ToString().Trim(); } if ((Request.QueryString["InvoiceNo"] != null) || (Request.QueryString["OrderNo"] != null)) { // clear out any previous exports first DirectoryInfo OldPages = new DirectoryInfo(Server.MapPath(DocName)); if (OldPages.Exists) { // Get a reference to each file in that directory. FileInfo[] AllOldPages = OldPages.GetFiles(); // go through the files foreach (FileInfo OldPage in AllOldPages) { if (OldPage.Name.Substring(0, UserPrefix.Length) == UserPrefix) { OldPage.Delete(); } } } // get the TEST watermark indicator DataSet dsAppPref = new DataSet(); dsAppPref = SqlHelper.ExecuteDataset(connectionString, "[UGEN_SP_Select]", new SqlParameter("@tableName", "SystemMaster with (NOLOCK)"), new SqlParameter("@displayColumns", "PrintRqst"), new SqlParameter("@whereCondition", " SystemMasterID = 0")); PrintRequest = dsAppPref.Tables[0].Rows[0]["PrintRqst"].ToString().Trim(); // load the crystal report using the path in an app pref record dsAppPref = SqlHelper.ExecuteDataset(connectionString, "[UGEN_SP_Select]", new SqlParameter("@tableName", "AppPref with (NOLOCK)"), new SqlParameter("@displayColumns", "AppOptionValue"), new SqlParameter("@whereCondition", " (ApplicationCd = 'SOE') AND (AppOptionType = 'InvoiceLocation')")); CrystalPath = dsAppPref.Tables[0].Rows[0]["AppOptionValue"].ToString(); RGRDoc.Load(CrystalPath + DocName + ".rpt", OpenReportMethod.OpenReportByTempCopy); // set the database connections for the report SqlConnectionStringBuilder ConnectBuilder = new SqlConnectionStringBuilder(); ConnectBuilder.ConnectionString = connectionString; ConnectionInfo ConnectInfo = new ConnectionInfo(); ConnectInfo.DatabaseName = ConnectBuilder["Initial Catalog"].ToString(); ConnectInfo.UserID = ConnectBuilder["User Id"].ToString(); ConnectInfo.Password = ConnectBuilder["Password"].ToString(); ConnectInfo.ServerName = ConnectBuilder["Data Source"].ToString(); // AutoDataBind="true" foreach (CrystalDecisions.CrystalReports.Engine.Table RGRTable in RGRDoc.Database.Tables) { TableLogOnInfo RGRTableLogonInfo = RGRTable.LogOnInfo; RGRTableLogonInfo.ConnectionInfo = ConnectInfo; RGRTable.ApplyLogOnInfo(RGRTableLogonInfo); } // set the selection criteria if (Request.QueryString["InvoiceNo"] != null) { RGRDoc.RecordSelectionFormula = "{SOHeaderHist.InvoiceNo}=\"" + Request.QueryString["InvoiceNo"].ToString() + "\" and isnull({SODetailHist.DeleteDt})"; } if (Request.QueryString["OrderNo"] != null) { RGRDoc.RecordSelectionFormula = "{SOHeaderHist.OrderNo}=" + Request.QueryString["OrderNo"].ToString() + " and isnull({SODetailHist.DeleteDt})"; } // we are ready to go RGRDoc.Refresh(); // Now set up the export DiskFileDestinationOptions diskOpts = ExportOptions.CreateDiskFileDestinationOptions(); ExportOptions exportOpts = new ExportOptions(); exportOpts.ExportFormatType = ExportFormatType.HTML40; exportOpts.ExportDestinationType = ExportDestinationType.DiskFile; exportOpts.ExportDestinationOptions = diskOpts; HTMLFormatOptions htmlFormatOpts = new HTMLFormatOptions(); htmlFormatOpts.FirstPageNumber = 1; htmlFormatOpts.HTMLEnableSeparatedPages = true; htmlFormatOpts.HTMLHasPageNavigator = false; htmlFormatOpts.HTMLBaseFolderName = Server.MapPath(""); htmlFormatOpts.HTMLFileName = UserPrefix + "InvoiceExport.htm"; exportOpts.ExportFormatOptions = htmlFormatOpts; // set the parameters if (PrintRequest == "T") { RGRDoc.SetParameterValue("IsTest", true); } else { RGRDoc.SetParameterValue("IsTest", false); } // Export the report to separate html files RGRDoc.Export(exportOpts); RGRDoc.Close(); // now that we have created the individual pages, make one big page DirectoryInfo PageFiles = new DirectoryInfo(Server.MapPath(DocName)); // Get a reference to each file in that directory. FileInfo[] AllPages = PageFiles.GetFiles(); // start the HTML page Response.Write("<head>\n<title>RGR Document</title>\n"); if ((Request.QueryString["ScriptX"] != null) && (Request.QueryString["ScriptX"] == "YES")) { // Embed ScriptX objects using (StreamReader sr = new StreamReader(Server.MapPath("common/include/ScriptX.inc"))) { String incline; // Read and display lines from the Spriptx include file while ((incline = sr.ReadLine()) != null) { Response.Write(incline + "\n"); } } // Load ScriptX javascript function Response.Write("<script src=\"" + ConfigurationManager.AppSettings["SOESiteURL"].ToString() + "Common/JavaScript/ScriptX.js\" type=\"text/javascript\"></script>\n"); } Response.Write("</head>\n<body style=\"margin: 0px\" >\n"); PagePosition = (decimal)0.0; foreach (FileInfo InvPage in AllPages) { // get only the files for the current user if (InvPage.Name.Substring(0, UserPrefix.Length) == UserPrefix) { WriteOK = false; Response.Write("<DIV style=\"position:absolute; top:" + PagePosition.ToString() + "pt; height:1000px \">\n"); //Response.Write(""); using (StreamReader sr = new StreamReader(InvPage.FullName)) { String line; // Read and display lines from the file until the end of // the file is reached. while ((line = sr.ReadLine()) != null) { if (line.Contains("</BODY>")) { WriteOK = false; } if (line.Contains("<style>")) { WriteOK = true; } if (line.Contains("class=\"crystalstyle\"")) { line = line.Replace("31", "0"); } if (line.Contains("images/")) { line = line.Replace("images/", ConfigurationManager.AppSettings["SOESiteURL"].ToString() + DocName + "/images/"); } if (line.Contains("<DIV style=\"position:absolute; top:")) { WriteOK = false; } if (line.Contains("</TR></TABLE></CENTER></Div>")) { WriteOK = false; } // write the line if (WriteOK) { Response.Write(line + "\n"); } if (line.Contains("</style>")) { WriteOK = false; } if (line.Contains("<BODY")) { WriteOK = true; } } } Response.Write("</DIV>\n"); if (PagePosition == (decimal)0.0) { PagePosition += (decimal)757; } } } Response.Write("</body>\n"); if ((Request.QueryString["ScriptX"] != null) && (Request.QueryString["ScriptX"] == "YES")) { Response.Write("<script language=\"javascript\">\n"); Response.Write("SetPrintSettings(true, 0.25, 0.25, 0.25, 0.25);\n"); Response.Write("</script>\n"); } Response.Write("</html>\n"); } } }
protected void CriaPDF() { Hashtable hash = (Hashtable)Session["HashProducaoMedicoRegulador"]; DSCabecalhoProducaoMedicoRegulador cabecalho = new DSCabecalhoProducaoMedicoRegulador(); cabecalho.Tables.Add((DataTable)hash["cabecalho"]); DSRelatorioProducaoMedicoRegulador conteudo = new DSRelatorioProducaoMedicoRegulador(); conteudo.Tables.Add((DataTable)hash["corpo"]); ReportDocument repDoc = new ReportDocument(); repDoc.Load(Server.MapPath("RelatoriosCrystal/CrystalReportViewer_RelatorioProducaoMedicoRegulador.rpt")); repDoc.SetDataSource(conteudo.Tables[1]); repDoc.Subreports["CrystalReportViewer_CabecalhoProducaoMedicoRegulador.rpt"].SetDataSource(cabecalho.Tables[1]); //repDoc.Database.Tables["CabecalhoAgendaPrestador"].SetDataSource((DataTable)hash["cabecalho"]); //repDoc.Database.Tables["RelatorioAgendaPrestador"].SetDataSource((DataTable)hash["corpo"]); System.IO.Stream s = repDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); Response.ClearContent(); Response.ClearHeaders(); Response.ContentType = "applicattion/octect-stream"; Response.AddHeader("Content-Disposition", "attachment;filename=RelatorioProducaoMedicosReguladores.pdf"); Response.AddHeader("Content-Length", s.Length.ToString()); Response.BinaryWrite(((System.IO.MemoryStream)s).ToArray()); Response.End(); }