public void VisualizarPdf(ReportDocument relatorio, ParameterFields paramFields, string caminho) { CrystalReportViewer1.ReportSource = relatorio; CrystalReportViewer1.ParameterFieldInfo = paramFields; ArquivoDownload adExtratoPdf = new ArquivoDownload(); adExtratoPdf.nome_arquivo = "Aviso_pagto" + DateTime.Now.ToFileTime() + ".pdf"; adExtratoPdf.caminho_arquivo = Server.MapPath(@"UploadFile\") + adExtratoPdf.nome_arquivo; adExtratoPdf.modo_abertura = System.Net.Mime.DispositionTypeNames.Inline; ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = adExtratoPdf.caminho_arquivo; CrExportOptions = relatorio.ExportOptions;//Report document object has to be given here CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; relatorio.Export(); Session[ValidaCaracteres(adExtratoPdf.nome_arquivo)] = adExtratoPdf; string fullUrl = "WebFile.aspx?dwFile=" + ValidaCaracteres(adExtratoPdf.nome_arquivo); AdicionarAcesso(fullUrl); //AbrirNovaAba(this, fullUrl, adExtratoPdf.nome_arquivo); Response.Redirect(fullUrl); //((BasePage)this.Page).ResponsePdf(adExtratoPDF.caminho_arquivo, adExtratoPDF.nome_arquivo); //ResponsePdf(adExtratoPDF.caminho_arquivo, adExtratoPDF.nome_arquivo); }
private void ExcuteExportReportToPDFCommand() { try { string path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\PinnaFace\\"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } var doc = ReportToView; var crDiskFileDestinationOptions = new DiskFileDestinationOptions(); var crFormatTypeOptions = new PdfRtfWordFormatOptions(); var filePath = path + "\\SampleReport.pdf"; crDiskFileDestinationOptions.DiskFileName = filePath; var crExportOptions = doc.ExportOptions; { crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; crExportOptions.DestinationOptions = crDiskFileDestinationOptions; crExportOptions.FormatOptions = crFormatTypeOptions; } doc.Export(); System.Diagnostics.Process.Start(@filePath); } catch (Exception ex) { MessageBox.Show(ex.Message + Environment.NewLine + ex.InnerException, "Can't Export Document"); } }
private void button1_Click(object sender, EventArgs e) // Export Pdf { try { var file = new FolderBrowserDialog(); var result = file.ShowDialog(); if (result == DialogResult.OK && file.SelectedPath != null) { ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file.SelectedPath; CrExportOptions = File.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } File.Export(); MessageBox.Show("Exported Successfully!"); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public static bool export_pdf(string pdf_path, string db_name, string crystal_path, string docentry) { try { ReportDocument cryRpt = new ReportDocument(); cryRpt.Load(crystal_path); new TableLogOnInfos(); TableLogOnInfo crtableLogoninfo; var crConnectionInfo = new ConnectionInfo(); ParameterFieldDefinitions crParameterFieldDefinitions; ParameterFieldDefinition crParameterFieldDefinition; ParameterValues crParameterValues = new ParameterValues(); ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue(); crParameterDiscreteValue.Value = Convert.ToString(docentry); crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields; crParameterFieldDefinition = crParameterFieldDefinitions["@DOCENTRY"]; crParameterValues = crParameterFieldDefinition.CurrentValues; crParameterValues.Clear(); crParameterValues.Add(crParameterDiscreteValue); crParameterFieldDefinition.ApplyCurrentValues(crParameterValues); crConnectionInfo.ServerName = "SYSPEXSAP04"; crConnectionInfo.DatabaseName = db_name; crConnectionInfo.UserID = "sa"; crConnectionInfo.Password = "******"; var crTables = cryRpt.Database.Tables; foreach (Table crTable in crTables) { crtableLogoninfo = crTable.LogOnInfo; crtableLogoninfo.ConnectionInfo = crConnectionInfo; crTable.ApplyLogOnInfo(crtableLogoninfo); } ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = pdf_path; CrExportOptions = cryRpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cryRpt.Export(); return(true); } catch (CrystalReportsException ex) { throw ex; } }
public void VistaPrevia(string key) { try { ReportDocument objRpt = new ReportDocument(); DataSet ds = (DataSet)this.GetDataReport(key); //string reportPath = Application.StartupPath + "\\Reporte\\co_ordenCompra_rpt.rpt"; string reportPath = "C:\\Reportes\\CR_Cliente_rpt01.rpt"; objRpt.Load(reportPath); DiskFileDestinationOptions crDiskFileDestinationOption = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions crFormatTypeOption = new PdfRtfWordFormatOptions(); ExportOptions crExportOptions = new ExportOptions(); objRpt.SetDataSource(ds.Tables[0]); string strfolder = "C:\\Reporte\\"; crDiskFileDestinationOption.DiskFileName = strfolder + "Cliente.pdf"; crExportOptions = objRpt.ExportOptions; crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; crExportOptions.ExportDestinationOptions = crDiskFileDestinationOption; crExportOptions.ExportFormatOptions = crFormatTypeOption; crystalReportViewer1.ReportSource = objRpt; //objRpt.Export(); } catch (Exception ex) { ex.ToString(); } }
private string SavePDF() { try { ReportDocument rpt = new ReportDocument(); rpt.Load(ReportsFolder + "\\CashReport.rpt"); rpt.SetDataSource(getBillDate(Convert.ToInt32(lblADID.Text), Convert.ToInt32(lblPaymentID.Text))); ExportOptions rptExportOption; DiskFileDestinationOptions rptFileDestOption = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions rptFormatOption = new PdfRtfWordFormatOptions(); string reportFileName = CashFolder + "\\Cash Receipt No - " + lblPaymentID.Text + ".pdf"; rptFileDestOption.DiskFileName = reportFileName; rptExportOption = rpt.ExportOptions; { rptExportOption.ExportDestinationType = ExportDestinationType.DiskFile; //if we want to generate the report as PDF, change the ExportFormatType as "ExportFormatType.PortableDocFormat" //if we want to generate the report as Excel, change the ExportFormatType as "ExportFormatType.Excel" rptExportOption.ExportFormatType = ExportFormatType.PortableDocFormat; rptExportOption.ExportDestinationOptions = rptFileDestOption; rptExportOption.ExportFormatOptions = rptFormatOption; } rpt.Export(); return(reportFileName); } catch (Exception ex) { MessageBox.Show("Error while saving PDF."); return("Error"); } }
private void btnSendMail_Click(object sender, EventArgs e) { try { cryRpt = new ReportDocument(); //cryRpt.Load(".\\crptEstimate.rpt"); cryRpt.Load("C:\\Users\\sopan\\source\\repos\\amolbshinde\\Gorakshnath-Billing-System\\Gorakshnath Billing System\\Report_Generator\\CrystalReport\\crptEstimate.rpt"); crptEstimateViewer.ReportSource = cryRpt; crptEstimateViewer.Refresh(); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = "E:\\EstimateSampleReport.pdf"; CrExportOptions = cryRpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cryRpt.Export(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public void ExportarRelatorioPdf(string caminho_arquivo) { InicializaRpt(); foreach (var p in _relatorio.parametros) { relatorio.SetParameterValue(p.parametro, p.valor); } ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = caminho_arquivo; CrExportOptions = relatorio.ExportOptions;//Report document object has to be given here CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; relatorio.Export(); //((BasePage)this.Page).ResponsePdf(adExtratoPDF.caminho_arquivo, adExtratoPDF.nome_arquivo); //ResponsePdf(adExtratoPDF.caminho_arquivo, adExtratoPDF.nome_arquivo); }
public bool ExportarPDF(string nombreArchivo) { bool resultado = false; try { ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = nombreArchivo; CrExportOptions = _docRpt.ExportOptions; CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; _docRpt.Export(); resultado = true; } catch (Exception ex) { MessageBox.Show("Exportar : " + ex.Message); PistaMgr.Instance.Error("Reportes.ExportarPDF", ex); } return(resultado); }
protected void GeneratePOAsPDF(DataTable dt, string FileName, int EmailID) { string repFilePath = Server.MapPath("TRVPOReport.rpt"); ReportDocument repDoc = new ReportDocument(); repDoc.Load(repFilePath); repDoc.SetDataSource(dt); ExportOptions exp = new ExportOptions(); DiskFileDestinationOptions dk = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions pd = new PdfRtfWordFormatOptions(); string destFile = Server.MapPath("../Uploads/Travel") + "\\" + FileName; dk.DiskFileName = destFile; //string Network_Path = System.Configuration.ConfigurationManager.AppSettings["APP_URL"].ToString()+@"Uploads\Travel\"; string Network_Path = @"Uploads\Travel\"; exp.ExportDestinationType = ExportDestinationType.DiskFile; exp.ExportFormatType = ExportFormatType.PortableDocFormat; exp.DestinationOptions = dk; exp.FormatOptions = pd; repDoc.Export(exp); BLL_Infra_Common.Insert_EmailAttachedFile(EmailID, FileName, Network_Path + FileName); }
//SAVE PDF TO FILE - method not used public void savePDF() { using (SqlConnection con = new SqlConnection(connectionstring)) { SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM WIZ_BulkInvoice_CrystalReport WHERE InvNumber = '" + label_InvoiceNumber.Text + "' ", con); ReportDocument rpt = new ReportDocument(); rpt.Load(@"C:\\BulkInvoice\\CrystalReport\\Invoice.rpt"); DataSet datatablesource = new DataSet(); sda.Fill(datatablesource, "WIZ_BulkInvoice_Report"); rpt.SetDataSource(datatablesource); ExportOptions rptExportOption; DiskFileDestinationOptions rptFileDestOption = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions rptFormatOption = new PdfRtfWordFormatOptions(); string reportFileName = @"C:\BulkInvoice\Documents\" + label_InvoiceNumber.Text + ".pdf"; //save path rptFileDestOption.DiskFileName = reportFileName; rptExportOption = rpt.ExportOptions; { rptExportOption.ExportDestinationType = ExportDestinationType.DiskFile; rptExportOption.ExportFormatType = ExportFormatType.PortableDocFormat; rptExportOption.ExportDestinationOptions = rptFileDestOption; rptExportOption.ExportFormatOptions = rptFormatOption; } rpt.Export(); rpt.Dispose(); } }
protected void doreport1() { string strConnString = ConfigurationManager.ConnectionStrings["TOHWConnectionString"].ConnectionString; DataSet ds = new DataSet(); rpt = new ReportDocument(); using (SqlConnection con = new SqlConnection(strConnString)) { SqlDataAdapter da = new SqlDataAdapter("Report_Encounters", con); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add("@username", SqlDbType.VarChar).Value = username; da.SelectCommand.Parameters.Add("@fromdate", SqlDbType.VarChar).Value = Request.QueryString["fromdate"]; da.SelectCommand.Parameters.Add("@todate", SqlDbType.VarChar).Value = Request.QueryString["todate"]; da.Fill(ds); //int records = ds.Tables["Table"].Rows.Count; } string report = Server.MapPath("~/private/reports/crystal/EncountersByPersonDate.rpt"); rpt.Load(report); rpt.SetDataSource(ds.Tables["Table"]); //rpt.SetParameterValue("fromdate", Request.QueryString["fromdate"]); //rpt.SetParameterValue("todate", Request.QueryString["todate"]); //crv_report.ReportSource = rpt; //int PageCount = rpt.FormatEngine.GetLastPageNumber(new ReportPageRequestContext()); //Literal1.Text = PageCount.ToString(); if (1 == 2) { ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = "C:\\temp\\test.pdf"; CrExportOptions = rpt.ExportOptions; CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrFormatTypeOptions.UsePageRange = true; //CrFormatTypeOptions.FirstPageNumber = 1; //CrFormatTypeOptions.LastPageNumber = 1; CrExportOptions.FormatOptions = CrFormatTypeOptions; rpt.Export(); } else { crv_report.ReportSource = rpt; Session.Add("Report", rpt); } }
private void GeneraReport(string StringaOdl) { report_pmp DtRpt = new report_pmp(); TheSite.SchemiXSD.ReportPmp ds = new TheSite.SchemiXSD.ReportPmp(); ds = DtRpt.GetDataRpt(StringaOdl); string pathRptSource = Server.MapPath(Request.ApplicationPath + ConfigurationSettings.AppSettings["SourceReports"]); crReportDocument.Load(pathRptSource + "REPORT_PMP.rpt"); crReportDocument.SetDataSource(ds); CRView1.ReportSource = crReportDocument; CRView1.DataBind(); // per pdf string Fname = Server.MapPath("../Report/" + Session.SessionID.ToString() + ".pdf");//pathRptSource + Session.SessionID.ToString() + ".pdf" ; ExportOptions optExp; DiskFileDestinationOptions optDsk = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions optPdfRtf = new PdfRtfWordFormatOptions(); optExp = crReportDocument.ExportOptions; optExp.ExportFormatType = ExportFormatType.PortableDocFormat; optExp.FormatOptions = optPdfRtf; optExp.ExportDestinationType = ExportDestinationType.DiskFile; optDsk.DiskFileName = Fname; optExp.DestinationOptions = optDsk; crReportDocument.Export(); Response.ClearContent(); Response.ClearHeaders(); Response.ContentType = "application/pdf"; Response.WriteFile(Fname); Response.Flush(); Response.Close(); System.IO.File.Delete(Fname); }
// GET: Reports public ActionResult Index() { /*String _reportPath = ""; * if(System.IO.File.Exists(Server.MapPath(_reportPath))) * { * System.Web.UI.Page page = this; * string vprinter = ""; * string vLetter = ""; * Components.ReportViewer rprt = new Components.ReportViewer(ref page); * }*/ try { ReportDocument cryRpt = null; ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = "E:\\csharp.net-informations.pdf"; CrExportOptions = cryRpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cryRpt.Export(); } catch (Exception ex) { } return(View()); }
/** * Funcion que crea el pdf y lo convierte a un httpresponseMessage para enviar por http * @param pbjeto de Crystal report que contiene la tabla del pdf a generar * @returns httpResponseMessage del pdf */ public HttpResponseMessage obtener_Response(ReportClass objRpt) { ExportOptions CrExportOptions; PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrExportOptions = objRpt.ExportOptions; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.FormatOptions = CrFormatTypeOptions; Stream stream = objRpt.ExportToStream(ExportFormatType.PortableDocFormat); objRpt.Export(CrExportOptions); MemoryStream ms = new MemoryStream(); stream.CopyTo(ms); var result = new HttpResponseMessage(HttpStatusCode.OK) { Content = new ByteArrayContent(ms.ToArray()) }; result.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment") { FileName = "Report.pdf" }; result.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream"); return(result); }
private void Report2_Load(object sender, EventArgs e) { ReportDocument cryRpt = new ReportDocument(); cryRpt.Load("C:\\Users\\M.ALI\\Documents\\GitHub\\ProjectA\\ProjectA\\ProjectA\\CrystalReport4.rpt"); crystalReportViewer1.ReportSource = cryRpt; crystalReportViewer1.Refresh(); try { ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = "C:\\Users\\M.ALI\\Documents\\ProjectAreports\\EvalReport.pdf"; CrExportOptions = cryRpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cryRpt.Export(); MessageBox.Show("Report created.At Path C:\\Users\\M.ALI\\Documents\\ProjectAreports."); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public void ExportReport(ReportDocument oReport, string cFileName, ExportTypes oExportTypes, int nFirstPage, int nLastPage) { ExportOptions oExportOptions = new ExportOptions(); PdfRtfWordFormatOptions oFormatOptions = ExportOptions.CreatePdfRtfWordFormatOptions(); DiskFileDestinationOptions oDestinationOptions = ExportOptions.CreateDiskFileDestinationOptions(); switch (oExportTypes) { case ExportTypes.PDF: case ExportTypes.MSWord: oExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; PdfRtfWordFormatOptions oPDFFormatOptions = ExportOptions.CreatePdfRtfWordFormatOptions(); if (nFirstPage > 0 && nLastPage > 0) { oPDFFormatOptions.FirstPageNumber = nFirstPage; oPDFFormatOptions.LastPageNumber = nLastPage; oPDFFormatOptions.UsePageRange = true; } oExportOptions.ExportFormatOptions = oPDFFormatOptions; break; case ExportTypes.MSExcel: oExportOptions.ExportFormatType = ExportFormatType.Excel; ExcelFormatOptions oExcelFormatOptions = ExportOptions.CreateExcelFormatOptions(); if (nFirstPage > 0 && nLastPage > 0) { oExcelFormatOptions.FirstPageNumber = nFirstPage; oExcelFormatOptions.LastPageNumber = nLastPage; oExcelFormatOptions.UsePageRange = true; } oExcelFormatOptions.ExcelUseConstantColumnWidth = false; oExportOptions.ExportFormatOptions = oExcelFormatOptions; break; case ExportTypes.HTML: oExportOptions.ExportFormatType = ExportFormatType.HTML40; HTMLFormatOptions oHTMLFormatOptions = ExportOptions.CreateHTMLFormatOptions(); if (nFirstPage > 0 && nLastPage > 0) { oHTMLFormatOptions.FirstPageNumber = nFirstPage; oHTMLFormatOptions.LastPageNumber = nLastPage; oHTMLFormatOptions.UsePageRange = true; } // can set additional HTML export options here oExportOptions.ExportFormatOptions = oHTMLFormatOptions; break; } oDestinationOptions.DiskFileName = cFileName; oExportOptions.ExportDestinationOptions = oDestinationOptions; oExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; oReport.Export(oExportOptions); }
protected void btn_Export_Click(object sender, EventArgs e) { try { RoutewisePaymentAbstract(); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); DateTime frmdate = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null); string fdate = frmdate.ToString("dd" + "_" + "MM" + "_" + "yyyy"); DateTime todate = DateTime.ParseExact(txt_ToDate.Text, "dd/MM/yyyy", null); string tdate = todate.ToString("dd" + "_" + "MM" + "_" + "yyyy"); string CurrentCreateFolderName = fdate + "_" + tdate + "_" + DateTime.Now.ToString("ddMMyyyy"); string path = @"C:\BILL VYSHNAVI\" + ccode + "_" + "_" + pcode + CurrentCreateFolderName + "\\"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } CrDiskFileDestinationOptions.DiskFileName = path + ccode + "_" + pcode + "_" + "RoutewiseAbstract.pdf"; CrExportOptions = cr.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cr.Export(); WebMsgBox.Show("Report Export Successfully..."); string MFileName = @"C:/BILL VYSHNAVI/" + ccode + "_" + "_" + pcode + CurrentCreateFolderName + "/" + ccode + "_" + pcode + "_" + "RoutewiseAbstract.pdf"; System.IO.FileInfo file = new System.IO.FileInfo(MFileName); if (File.Exists(MFileName.ToString())) { FileStream sourceFile = new FileStream(file.FullName, FileMode.Open); float FileSize; FileSize = sourceFile.Length; byte[] getContent = new byte[(int)FileSize]; sourceFile.Read(getContent, 0, (int)sourceFile.Length); sourceFile.Close(); Response.ClearContent(); // neded to clear previous (if any) written content Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name); Response.AddHeader("Content-Length", file.Length.ToString()); Response.ContentType = "text/plain"; Response.BinaryWrite(getContent); File.Delete(file.FullName.ToString()); Response.Flush(); Response.End(); } else { Response.Write("File Not Found..."); } } catch (Exception ex) { WebMsgBox.Show("Please Check the ExportPath..."); } }
private void SaveReportToPDF() { try { rprt.Load(Server.MapPath("~\\Crystal Reports\\UserReport.rpt")); string Query = QueryObj.GetQueryViaFileAndTagName("LogIn.xml", "UserReport"); Connection.Open(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(Query, Connection); DataSet dataSetObj = new DataSet(); adapter.Fill(dataSetObj); if (dataSetObj != null) { ExportFormatType formatType = ExportFormatType.NoFormat; switch (rbFormat.SelectedItem.Value = "PDF") { case "Word": formatType = ExportFormatType.WordForWindows; break; case "PDF": formatType = ExportFormatType.PortableDocFormat; break; case "Excel": formatType = ExportFormatType.Excel; break; case "CSV": formatType = ExportFormatType.CharacterSeparatedValues; break; } adapter.Fill(dataSetObj, "User"); rprt.SetDataSource(dataSetObj.Tables[0]); UserReportId.ReportSource = rprt; } ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = "D:\\Roshan Project\\RequiredCommonInfo\\AlumacSystem\\AlumacSystem\\Crystal Reports\\Card.pdf"; CrExportOptions = rprt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } rprt.Export(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { Connection.Close(); } }
protected void GeneratePOAsPDF(DataTable dt, string strPath, string FileName) { string repFilePath = Server.MapPath("POReport.rpt"); //using (ReportDocument repDoc = new ReportDocument()) //{ using (ReportDocument repDoc = new ReportDocument()) { repDoc.Load(repFilePath); repDoc.SetDataSource(dt); decimal Total_Price = 0; decimal Exchane_rate = 1; decimal Vat = 0; decimal sercharge = 0; decimal discount = 0; foreach (DataRow dr in dt.Rows) { Exchane_rate = Convert.ToDecimal(dr["EXCHANGE_RATE"].ToString()); discount = Convert.ToDecimal(dr["DISCOUNT"].ToString()); Vat = Convert.ToDecimal(dr["VAT"].ToString()); sercharge = Convert.ToDecimal(dr["SURCHARGES"].ToString()); Total_Price = Total_Price + (Convert.ToDecimal(dr["REQUESTED_QTY"].ToString()) * Convert.ToDecimal(dr["QUOTED_RATE"].ToString()) - (Convert.ToDecimal(dr["REQUESTED_QTY"].ToString()) * Convert.ToDecimal(dr["QUOTED_RATE"].ToString()) * Convert.ToDecimal(dr["QUOTED_DISCOUNT"].ToString()) / 100)); } repDoc.DataDefinition.FormulaFields[1].Text = (Total_Price / Exchane_rate).ToString(); repDoc.DataDefinition.FormulaFields[2].Text = (Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[1].Text) * discount / 100).ToString(); repDoc.DataDefinition.FormulaFields[3].Text = ((Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[1].Text) - (Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[1].Text) * discount / 100)) * sercharge / 100).ToString(); repDoc.DataDefinition.FormulaFields[4].Text = ((Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[1].Text) - Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[2].Text) + Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[3].Text)) * Vat / 100).ToString(); repDoc.DataDefinition.FormulaFields[0].Text = ((Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[1].Text) - Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[2].Text) + Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[3].Text) + Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[4].Text)).ToString()); ExportOptions exp = new ExportOptions(); DiskFileDestinationOptions dk = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions pd = new PdfRtfWordFormatOptions(); string sFile = strPath + FileName; dk.DiskFileName = strPath + FileName; exp.ExportDestinationType = ExportDestinationType.DiskFile; exp.ExportFormatType = ExportFormatType.PortableDocFormat; exp.DestinationOptions = dk; exp.FormatOptions = pd; repDoc.Export(exp); //for email attachment string destFile = Server.MapPath("../Uploads/Purchase") + "\\" + FileName;; File.Copy(sFile, destFile, true); repDoc.Close(); repDoc.Dispose(); } //} }
void ExportCrystalReport(ReportDocument cryRpt, string formatExport, string pathReportGenerate) { if (string.IsNullOrEmpty(pathReportGenerate)) { throw new Exception("Path report generate not found"); } if (cryRpt == null) { throw new Exception("Resource not found"); } ExportFormatType exportFormatType = ExportFormatType.NoFormat; string extensionFile = Path.GetExtension(pathReportGenerate); object formatOption = new PdfRtfWordFormatOptions(); switch (formatExport) { case "WORD": exportFormatType = ExportFormatType.WordForWindows; extensionFile = ".doc"; break; case "PDF": exportFormatType = ExportFormatType.PortableDocFormat; break; case "EXCEL": exportFormatType = ExportFormatType.Excel; extensionFile = ".xls"; formatOption = new ExcelFormatOptions(); break; } var downloadReportPath = System.Web.Hosting.HostingEnvironment.MapPath("~/DownloadReports/"); //Check exist folder DownloadReports if (!Directory.Exists(downloadReportPath)) { Directory.CreateDirectory(downloadReportPath); } DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); CrDiskFileDestinationOptions.DiskFileName = pathReportGenerate; ExportOptions CrExportOptions = cryRpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = exportFormatType; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = formatOption; } cryRpt.Export(); }
protected void GeneratePOAsPDF(DataTable dt, string strPath, string FileName) { string repFilePath = Server.MapPath("POReport.rpt"); using (ReportDocument repDoc = new ReportDocument()) { repDoc.Load(repFilePath); repDoc.SetDataSource(dt); decimal Total_Price = 0; decimal Exchane_rate = 1; decimal Vat = 0; decimal sercharge = 0; decimal discount = 0; decimal withholding = 0; foreach (DataRow dr in dt.Rows) { Exchane_rate = Convert.ToDecimal(dr["EXCHANGE_RATE"].ToString()); discount = Convert.ToDecimal(dr["DISCOUNT"].ToString()); Vat = Convert.ToDecimal(dr["VAT"].ToString()); sercharge = Convert.ToDecimal(dr["SURCHARGES"].ToString()); Total_Price = Total_Price + (Convert.ToDecimal(dr["REQUESTED_QTY"].ToString()) * Convert.ToDecimal(dr["QUOTED_RATE"].ToString()) - (Convert.ToDecimal(dr["REQUESTED_QTY"].ToString()) * Convert.ToDecimal(dr["QUOTED_RATE"].ToString()) * Convert.ToDecimal(dr["QUOTED_DISCOUNT"].ToString()) / 100)); if (dr["SURCHARGES"].ToString() == "Spares") { repDoc.ReportDefinition.Sections[1].SectionFormat.EnableSuppress = true; } } repDoc.DataDefinition.FormulaFields[1].Text = (Total_Price / Exchane_rate).ToString(); repDoc.DataDefinition.FormulaFields[2].Text = (Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[1].Text) * discount / 100).ToString(); repDoc.DataDefinition.FormulaFields[3].Text = ((Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[1].Text) - (Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[1].Text) * discount / 100)) * sercharge / 100).ToString(); repDoc.DataDefinition.FormulaFields[4].Text = ((Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[1].Text) - Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[2].Text) + Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[3].Text)) * Vat / 100).ToString(); repDoc.DataDefinition.FormulaFields[0].Text = ((Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[1].Text) - Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[2].Text) + Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[3].Text) + Convert.ToDecimal(repDoc.DataDefinition.FormulaFields[4].Text)).ToString()); Response.Buffer = false; // Clear the response content and headers Response.ClearContent(); Response.ClearHeaders(); ExportOptions exp = new ExportOptions(); exp.ExportFormatType = ExportFormatType.PortableDocFormat; PdfRtfWordFormatOptions pd = new PdfRtfWordFormatOptions(); exp.ExportDestinationType = ExportDestinationType.NoDestination; exp.ExportFormatType = ExportFormatType.PortableDocFormat; exp.FormatOptions = pd; repDoc.ExportToHttpResponse(exp, Response, false, "ghjgj"); repDoc.Close(); repDoc.Dispose(); } }
private void BtnImprimir_Click(object sender, RoutedEventArgs e) { try { var boton = e.Source as Button; if (boton != null) { var elemento = boton.CommandParameter as ImpresionCalidadMezcladoModel; var calidad = new CalidadMezcladoFormulasAlimentoPL(); var listaAuxiliar = new List <ImpresionCalidadMezcladoModel> { elemento }; calidad.CalculosDetalle(listaAuxiliar); var documento = new ReportDocument(); var reporte = String.Format("{0}{1}", AppDomain.CurrentDomain.BaseDirectory, "\\Administracion\\Impresiones\\rptEf.MezRotomix 2.rpt"); documento.Load(reporte); documento.DataSourceConnections.Clear(); documento.SetDataSource(listaAuxiliar); documento.Refresh(); ExportOptions rptExportOption; var rptFileDestOption = new DiskFileDestinationOptions(); var rptFormatOption = new PdfRtfWordFormatOptions(); string reportFileName = string.Format("{0}_{1}_{2}.pdf", @"C:\CheckListCalidadMezclado", elemento.Tecnica, Contexto.Fecha.ToString("ddMMyyyy")); rptFileDestOption.DiskFileName = reportFileName; rptExportOption = documento.ExportOptions; { rptExportOption.ExportDestinationType = ExportDestinationType.DiskFile; rptExportOption.ExportFormatType = ExportFormatType.PortableDocFormat; rptExportOption.ExportDestinationOptions = rptFileDestOption; rptExportOption.ExportFormatOptions = rptFormatOption; } documento.Export(); SkMessageBox.Show(Application.Current.Windows[ConstantesVista.WindowPrincipal], Properties.Resources.Impresion_ReporteGeneradoConExito, MessageBoxButton.OK, MessageImage.Correct); Process.Start(reportFileName); } } catch (Exception ex) { Logger.Error(ex); SkMessageBox.Show(Application.Current.Windows[ConstantesVista.WindowPrincipal], Properties.Resources.ImpresionSupervisionTecnicasDeteccion_ErrorImpresion, MessageBoxButton.OK, MessageImage.Error); } }
private bool SendWebMail(string strTo, string subj, string cont, string cc, string bcc, string strfrom) { // generate reports DataTable dre = bus.generatereport(); rd.Load(Server.MapPath(Request.ApplicationPath) + "/user/MonthlyReport.rpt"); rd.SetDataSource(dre); // location of empty pdf file string filename = Server.MapPath("~/pdf/Monthly_Report.pdf"); // export the report to pdf and write to empty pdf file inside pdf folder ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = filename; CrExportOptions = rd.ExportOptions;//Report document object has to be given here CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; rd.Export(); // send email with attachment bool flg = false; MailMessage msg = new MailMessage(); msg.Body = "<table border='1' cellpadding='0' cellspacing='0' style='width: 750px; border-color: black;'><tr><td colspan='9'><br>   Dear Sir / Madam,<br /><br />     PFA Monthly report for the Month of <b>" + dre.Rows[0][5].ToString() + " " + dre.Rows[0][6].ToString() + "</b>. The details are as follows.<br /><br /></td></tr><tr style='font-weight: 700;'></tr><tr><td colspan='9'><br/><p></p><p>    Opening Date: " + dre.Rows[0][2].ToString() + "</p><p>   Opening Balance: " + dre.Rows[0][7].ToString() + "</p><p>   Closing Balance: " + dre.Rows[0][15].ToString() + "</p><p>   Opened By: " + dre.Rows[0][4].ToString() + " </p><p>   Freezed Date: " + dre.Rows[0][3].ToString() + " </p><p>   click<a href=" + url + "> here </a>to login into the application</p><br/></td></tr><tr></tr><td colspan='9' style='font-weight: bold' align='right'><br /><br />Regards,<br />Team Petty Cash App</td></tr><tr><td align='center'><p style='color:blue;'> This is a system generated response. Please do not respond to this email id.</p></td></tr></table>"; msg.From = strfrom; msg.To = strTo; msg.Subject = subj + " " + dre.Rows[0][5].ToString() + " " + dre.Rows[0][6].ToString(); msg.Cc = cc; msg.Bcc = bcc; //System.Net.Mail.Attachment attach; //attach = new System.Net.Mail.Attachment() msg.Attachments.Add(new MailAttachment(filename)); msg.BodyFormat = MailFormat.Html; try { //SmtpMail.SmtpServer = "175.143.44.165"; SmtpMail.SmtpServer = "192.168.1.4"; // change the ip address to this when hosting in server SmtpMail.Send(msg); flg = true; } catch (Exception) { flg = false; } return(flg); }
private void download_leaves(int id, int rid) { bus.lid = id; DataTable dt = bus.fetch_download_leaves(); int reg = rid; if (dt.Rows.Count > 0) { if (reg == 2) { rd.Load(Server.MapPath(Request.ApplicationPath) + "/user/approved.rpt"); rd.SetDataSource(dt); // location of empty pdf file string exportPath = Server.MapPath("~/pdf/Approved_Leave.pdf"); // export the report to pdf and write to empty pdf file inside pdf folder ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = exportPath; CrExportOptions = rd.ExportOptions;//Report document object has to be given here CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; rd.Export(); Response.Redirect("~/ViewPdf?reportFile=" + exportPath); } else if (reg == 1) { rd.Load(Server.MapPath(Request.ApplicationPath) + "/user/approved_malaysia.rpt"); rd.SetDataSource(dt); // location of empty pdf file string exportPath = Server.MapPath("~/pdf/Approved_Leave.pdf"); // export the report to pdf and write to empty pdf file inside pdf folder ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = exportPath; CrExportOptions = rd.ExportOptions;//Report document object has to be given here CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; rd.Export(); Response.Redirect("~/ViewPdf?reportFile=" + exportPath); } } }
public void PrintReport(string key) { try { ReportDocument objRpt = new ReportDocument(); DataSet ds = (DataSet)this.GetDataReport(key); string reportPath = "C:\\Reportes\\CRIngreso_etiqueta02.rpt"; objRpt.Load(reportPath); DiskFileDestinationOptions crDiskFileDestinationOption = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions crFormatTypeOption = new PdfRtfWordFormatOptions(); ExportOptions crExportOptions = new ExportOptions(); objRpt.SetDataSource(ds.Tables[0]); string strfolder = "C:\\Reporte\\"; crDiskFileDestinationOption.DiskFileName = strfolder + "Etiqueta.pdf"; crExportOptions = objRpt.ExportOptions; crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; crExportOptions.ExportDestinationOptions = crDiskFileDestinationOption; crExportOptions.ExportFormatOptions = crFormatTypeOption; objRpt.Export(); this.printDialog1.Document = this.printDocument1; DialogResult dr = this.printDialog1.ShowDialog(); if (dr == DialogResult.OK) { PageMargins margins; margins = objRpt.PrintOptions.PageMargins; margins.bottomMargin = 250; margins.leftMargin = 250; margins.rightMargin = 250; margins.topMargin = 250; objRpt.PrintOptions.ApplyPageMargins(margins); string PrinterName = this.printDocument1.PrinterSettings.PrinterName; objRpt.PrintOptions.PrinterName = PrinterName; objRpt.PrintToPrinter(1, false, 0, 0); } } catch (Exception ex) { ex.ToString(); } }
public void GeneratePDF(string poId) { ReportDocument rptDoc = new ReportDocument(); dsPSMS ds = new dsPSMS(); DataTable dt = new DataTable(); dt.TableName = "PO"; dt = getAllPOs(); ds.Tables["dtPO"].Merge(dt); //subreport data table dt = new DataTable(); dt.TableName = "Terms"; dt = getAllTerms(); ds.Tables["dtTerms"].Merge(dt); rptDoc.Load(Server.MapPath("~/Reports/crPO.rpt")); rptDoc.SetDataSource(ds); string fileName = "PO" + poId + "_.pdf"; String targetFolder = Server.MapPath(POAttachmentfolderPath); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = targetFolder + "\\" + fileName; CrExportOptions = rptDoc.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } rptDoc.Export(); string strFields = ""; string[] strValues = new string[3]; strFields = "[POID],[FilePath],[Note]"; string filePath = POAttachmentfolderPath + "/" + fileName; string sqlStr = "select [FilePath] from [dbo].[POAttach] where [FilePath]=@FilePath"; DataTable hasData = am.DataAccess.RecordSet(sqlStr, new string[] { filePath }); if (hasData.Rows.Count == 0) { strValues = new string[] { poId.ToString() , filePath , txtAttachmentNote.Text.Trim() }; am.DataAccess.BatchQuery.Insert("[dbo].[POAttach]", strFields, strValues); am.DataAccess.BatchQuery.Execute(); } }
private void CreatePDFFile(string fileName = "TestFile") { // Setup DataSet datasets.DataSetTest ds = new datasets.DataSetTest(); // IList customerList = new List(); DataTable dt = CommonFunction.getTable("select p.id,p.firstname,p.lastname,ph.id as phid,ph.phone_num,ph.phone_type from people p left outer join phones ph on ph.pid=p.id order by p.id", CommonFunction.getConnection()); foreach (DataRow dr1 in dt.Rows) { DataRow dr = ds.people.NewpeopleRow(); if (dr1["id"] != DBNull.Value) { dr["id"] = Convert.ToInt32(dr1["id"]); } if (dr1["firstname"] != DBNull.Value) { dr["name"] = Convert.ToString(dr1["firstname"]); } if (dr1["phone_num"] != DBNull.Value) { dr["phone"] = Convert.ToString(dr1["phone_num"]); } if (dr1["phone_type"] != DBNull.Value) { dr["type"] = Convert.ToString(dr1["phone_type"]); } // ds.phone.Rows.Add(dr2); ds.people.Rows.Add(dr); } ReportDocument rpt = new ReportDocument(); rpt.Load(@Server.MapPath("reports/Crosstabtest.rpt")); rpt.SetDataSource(ds); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = "E:\\SampleReport.pdf"; CrExportOptions = rpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } rpt.Export(); }
protected void cmdPnt_Click(object sender, EventArgs e) { MCImport mc1 = new MCImport(g_user_id, g_lang); Message msg = new Message(g_user_id, g_lang); DataSet dsList = new DataSet(); //Crystal_Report.PickingListRpt pl = new Crystal_Report.PickingListRpt(); dsList = mc1.GetPickingList(); //pl.SetDataSource(dsList); ReportClass Rpt = null; //Rpt = new HondaTraceabilitySystem.Common.Report.FeOnline(); Rpt = new HondaTraceabilitySystem.Crystal_Report.PickingListRpt(); string ReportName = "PickingList"; try { //Rpt.Subreports["SubReport"].SetDataSource(prtDtTotal); Rpt.SetDataSource(dsList.Tables[0]); //Rpt.SetParameterValue("H_TITLE", "INSPECTION SHEET(SEMICONDUCTOR"); PdfRtfWordFormatOptions pdfFormatOpts = new PdfRtfWordFormatOptions(); DiskFileDestinationOptions diskOpts = new DiskFileDestinationOptions(); // exportOpts; ExportOptions exportOpts = Rpt.ExportOptions; exportOpts.ExportDestinationType = ExportDestinationType.DiskFile; exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat; exportOpts.FormatOptions = pdfFormatOpts; string tmpFilenm = ReportName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".pdf"; diskOpts.DiskFileName = Server.MapPath(WebConfigurationManager.ConnectionStrings["ReportOutDir"].ConnectionString + tmpFilenm); exportOpts.DestinationOptions = diskOpts; //print Rpt.Export(); Type cstype = this.GetType(); this.Page.ClientScript.RegisterClientScriptBlock(cstype, "a", @"<script>window.open('" + WebConfigurationManager.ConnectionStrings["ReportOutDir"].ConnectionString + tmpFilenm + "', '" + ReportName + "', 'status,resizable=yes');</script>"); } catch (Exception ex) { string strErr = ex.ToString(); lblMsg.Text = ex.ToString(); return; } lblMsg.Text = msg.GetMessage("NORMAL_PRINT"); lblMsg.ForeColor = Color.Blue; }
/// <summary> /// Stampa il report che gli viene passato con il nome specificato /// overloading: viene passato un ReporDocument /// </summary> public void StampaPdf(ReportDocument ReportDaStampare, string NomeFile) { NomeFile = _s_DirectoryStampa + NomeFile + ".pdf"; ExportOptions optExp; DiskFileDestinationOptions optDsk = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions optPdfRtf = new PdfRtfWordFormatOptions(); optExp = ReportDaStampare.ExportOptions; optExp.ExportFormatType = ExportFormatType.PortableDocFormat; optExp.FormatOptions = optPdfRtf; optExp.ExportDestinationType = ExportDestinationType.DiskFile; optDsk.DiskFileName = NomeFile; optExp.DestinationOptions = optDsk; ReportDaStampare.Export(); }
private void ExPortReport(ReportDocument ReportDoc, string rptPath) { CrystalDecisions.Shared.ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = Server.MapPath("~/CrystalReports/Payroll/VirtualReport/" + rptPath); CrExportOptions = ReportDoc.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } ReportDoc.Export(); }
public static void ExportPDF(ReportDocument rpt, string sCaminhoSave) { CrystalReportViewer cryView = new CrystalReportViewer(); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = sCaminhoSave; CrExportOptions = rpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } rpt.Export(); }
private void emailQuote_Click(object sender, RoutedEventArgs e) { // var reportLocation = Properties.Settings.Default.ReportsFolder+"Quote.rpt"; ReportDocument mailReport = new ReportDocument(); { //Report Data settings for export DataSet myData = new DataSet(); MySql.Data.MySqlClient.MySqlConnection conn; MySql.Data.MySqlClient.MySqlCommand cmd; MySql.Data.MySqlClient.MySqlDataAdapter myAdapter; conn = new MySql.Data.MySqlClient.MySqlConnection(); cmd = new MySql.Data.MySqlClient.MySqlCommand(); myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(); conn.ConnectionString = Properties.Settings.Default.abab1199_qsyncConnectionString; try { cmd.CommandText = "SELECT * FROM quotes WHERE QuoteNumber = " + qtNbr.Text; cmd.Connection = conn; myAdapter.SelectCommand = cmd; myAdapter.Fill(myData); mailReport.Load(reportLocation); mailReport.Database.Tables[0].SetDataSource(myData.Tables[0]); mailReport.SetParameterValue("Quote", qtNbr.Text); mailReport.SetParameterValue("Quote_Number", qtNbr.Text); //Report Exporting settings ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = tempLocation; CrExportOptions = mailReport.ExportOptions; CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; mailReport.Export(); sendmail(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } }
public void PrintReport(string key) { try { //---------------------------------------- ReportDocument objRpt = new ReportDocument(); DataSet ds = (DataSet)this.GetDataReport(key); string reportPath = "C:\\Reportes\\CRIngreso_etiqueta02.rpt"; objRpt.Load(reportPath); DiskFileDestinationOptions crDiskFileDestinationOption = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions crFormatTypeOption = new PdfRtfWordFormatOptions(); ExportOptions crExportOptions = new ExportOptions(); objRpt.SetDataSource(ds.Tables[0]); string strfolder = "C:\\Reporte\\"; crDiskFileDestinationOption.DiskFileName = strfolder + "Etiqueta.pdf"; crExportOptions = objRpt.ExportOptions; crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; crExportOptions.ExportDestinationOptions = crDiskFileDestinationOption; crExportOptions.ExportFormatOptions = crFormatTypeOption; objRpt.Export(); //--------------------------------------- ////string PrinterName = this.printDocument1.PrinterSettings.PrinterName; //string NombreImpresora = ""; ////for (int i = 0; i < PrinterSettings.InstalledPrinters.Count; i++) ////{ //// PrinterSettings a = new PrinterSettings(); //// a.PrinterName = PrinterSettings.InstalledPrinters[i].ToString(); //// if (a.IsDefaultPrinter) //// { //// PrinterName = PrinterSettings.InstalledPrinters[i].ToString(); //// } ////} //this.printDialog1.Document = this.printDocument1; //DialogResult dr = this.printDialog1.ShowDialog(); //if (dr == DialogResult.OK) // { PageMargins margins; margins = objRpt.PrintOptions.PageMargins; margins.bottomMargin = 250; margins.leftMargin = 250; margins.rightMargin = 250; margins.topMargin = 250; objRpt.PrintOptions.ApplyPageMargins(margins); //string PrinterName = this.printDocument1.PrinterSettings.PrinterName; // ////objRpt.PrintOptions.PrinterName = PrinterName; objRpt.PrintOptions.PrinterName = GetImpresoraDefecto(); //objRpt.PrintOptions.PrinterName = PrinterName; objRpt.PrintToPrinter(1, false, 0, 0); //} } catch (Exception ex) { ex.ToString(); } }
private void Export_pdf(string branch_code,DataTable dt) { try { if (dt.Rows.Count > 0) { string file_path = tbFilePath.Text.Trim() + "\\" + branch_code.Trim() + "_" + dp_from_date.Text.Trim().Replace("/", ".") + "_" + dp_to_date.Text.Trim().Replace("/", ".") + ".pdf"; //string file_path = tbFilePath.Text.Trim() + "\\" + branch_code.Trim() + ".pdf"; //export PDF Rpt_PHT18 cry = new Rpt_PHT18(); cry.SetDataSource(dt); string title = "", Fromdate = "", Todate = ""; string title2 = ""; title = "DANH SÁCH CÁC THẺ CẦN GIA HẠN "; Fromdate = dp_from_date.Text.Trim(); Todate = dp_to_date.Text.Trim(); //title2 = "(Từ ngày " + dp_from_date.Text.Trim() + " đến ngày " + dp_to_date.Text.Trim() + " )"; cry.SetParameterValue("title", title); cry.SetParameterValue("Fromdate", Fromdate); cry.SetParameterValue("Todate", Todate); //cry.SetParameterValue("title2", title2); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export(); //--Fix for Load Report failse if (cry != null) { cry.Close(); cry.Dispose(); } } } catch (Exception ex) { //MessageBox.Show("Err Export_pdf: \n Branch: "+ branch_code +"\n" + ex.ToString()); string[] row = { branch_code, ex.ToString() }; dgv_err.Rows.Add(row); } }
private void button1_Click(object sender, EventArgs e) { string sql = "", err = ""; string title = ""; string type = ""; if (cbOption.SelectedIndex + 1 == 1) type = "MB-TNT01"; if (cbOption.SelectedIndex + 1 == 2) type = "MB-TNT04"; if (cbOption.SelectedIndex + 1 == 3) type = "MB-TNT05"; if (cbOption.SelectedIndex + 1 == 4) type = "CR_TN21"; if (cbOption.SelectedIndex + 1 == 5) type = "CR_TN3"; if (cbOption.SelectedIndex + 1 == 6) type = "CR_ADC"; if (cbOption.SelectedIndex + 1 == 7) type = "MB-TNT09"; if (cbOption.SelectedIndex + 1 == 8) type = "CR_LV1"; if (cbOption.SelectedIndex + 1 == 9) type = "CR_TD1"; this.Cursor = Cursors.WaitCursor; string folderOut = tbFilePath.Text.Trim(); //Tao thu muc if (!Directory.Exists(folderOut)) { Directory.CreateDirectory(folderOut); } if (type != "") { sql = ""; err = ""; sql = " select *"; sql += " from "+Businessbp.executedb.owner+"nab_credit_xln_sendmail g"; sql += " where trim(g.type_report)='" + type + "'"; sql += " and trunc(g.date_create)=trunc(sysdate)"; DataTable dtCheck = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); return; } else { if (dtCheck.Rows.Count > 0) { MessageBox.Show("Đã xử lý Report này cho hôm nay."); return; } } for (int k = 0; k < dtBranch.Rows.Count; k++)//Vòng lặp để xuất ra từng Branch { string branch = dtBranch.Rows[k]["branch_code"].ToString().Trim(); tbStatus.Text = "Da xu ly : "+ (k +1).ToString() +" --" +"Dang xu ly :"+branch; if (type.ToUpper() == "MB-TNT04") { //sql = "select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = "select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } else { sql = "select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.cr_account_nbr"; sql += " and c.cr_account_nbr = d.card_number"; sql += " and d.application_no = e.source_application_no"; //sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 1 "; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) < 16 "; sql += " and trim(e.branch_code_dsa_code) = '" + branch + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH KHÁCH HÀNG CẦN THÚC NỢ LẦN 1"; } #region chualam else if (type.ToUpper() == "MB-TNT05") { //sql = "select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due," + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = "select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due," + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } else { sql = "select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due," + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } sql += " nvl(" +Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.cr_account_nbr"; sql += " and c.cr_account_nbr = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 16 "; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) < 31 "; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH KHÁCH HÀNG CẦN THÚC NỢ LẦN 2"; } else if (type.ToUpper() == "CR_TN21") { //sql = "select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, trunc(b.payment_date) payment_date,"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = "select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, trunc(b.payment_date) payment_date,"; //2014-04-01 (1) } else { sql = "select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, trunc(b.payment_date) payment_date,"; //2014-04-01 (1) } sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.cr_account_nbr = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 31"; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) < 61 "; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH KHÁCH HÀNG CẦN THÚC NỢ LẦN 3";// VÀ GỬI THƯ LẦN 1"; } else if (type.ToUpper() == "CR_TN3") { //sql = "select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = "select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } else { sql = "select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 61"; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) < 91"; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH CÁC THẺ CẦN GỬI THƯ LẦN 2"; } else if (type.ToUpper() == "CR_ADC") { //sql = " select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status,to_char(b.payment_date,'dd/mm/yyyy') payment_date,'' as number_days,'' as Ghi_chu"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = " select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status,to_char(b.payment_date,'dd/mm/yyyy') payment_date,'' as number_days,'' as Ghi_chu"; //old 2014-04-01 (1) } else { sql = " select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status,to_char(b.payment_date,'dd/mm/yyyy') payment_date,'' as number_days,'' as Ghi_chu"; //old 2014-04-01 (1) } sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) ,0) >= 3"; sql += " and nvl(a.acc_adm_status,0) = 0 "; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH CÁC THẺ CẦN KHÓA ADMIN STATUS"; } else if (type.ToUpper() == "CR_LV1") { //sql = " select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = " select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } else { sql = " select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr,e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 91"; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) < 121 "; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH CÁC THẺ CẦN LÀM VIỆC TRỰC TIẾP"; } else if (type.ToUpper() == "CR_TD1") { //sql = " select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,";//old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = " select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,";//2014-04-01 (1) } else { sql = " select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,";//2014-04-01 (1) } sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 121"; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) <= 150"; sql += " and a.acc_adm_status not in ('3')"; //sql += " and c.embossed_name not in ('%NQH-CD')"; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH CÁC THẺ CẦN TÁI THẨM ĐỊNH"; } else if (type.ToUpper() == "MB-TNT01") { //sql = "select a.cr_account_nbr, e.branch_code_dsa_code branch_code,";//old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = "select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code,";// 2014-04-01 (1) } else { sql = "select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code,";// 2014-04-01 (1) } sql += " decode(e.ADDON_CARD_FLAG, '1', nvl(("; sql += " select aa.embossed_name"; sql += " from " + Businessbp.executedb.owner + "card aa"; sql += " where aa.card_number = e.ADDON_CARD_NUMBER"; sql += " ),' ')"; sql += " , c.embossed_name) embossed_name,";//Lấy embossing name cho thẻ phụ sql += " c.family_name, b.opening_bal, b.closing_bal, b.min_due," + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; //sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and b.min_due>0 "; sql += " and nvl(b.closing_bal,0)>0 "; sql += " and trunc(b.stat_date)<=trunc(sysdate) ";// Sau ngay chay mounthly sql += " and trunc(sysdate)<= trunc(nvl(b.payment_date,sysdate)) ";// Nho hon ngay T sql += " and a.cr_account_nbr not in (select r.cr_account_nbr from " + Businessbp.executedb.owner + "nab_credit_unpaid r) "; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH CÁC THẺ CẦN NHẮC NỢ"; } ////else if (cbOption.SelectedValue.ToString().ToUpper() == "MB-CNQH") ////{ //// Session["type"] = "MB-CNQH"; //// //sql += " select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, a.acc_unpaid_status, b.payment_date,"; //// //sql += " ((sysdate - nvl(a.acc_unpaid_status_date, sysdate)) + (nvl(a.acc_unpaid_status,0) - 1)*30) number_days,'' as Ghi_chu"; //// sql = " select to_char(rownum) as STT,a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, e.legal_id as CMND "; //// sql += " ,(nvl(a.bal_purchase,0) + nvl(a.bal_cash,0) + nvl(a.bal_te,0) + nvl(a.bal_unique,0) + nvl(a.bal_cheque,0) + nvl(a.bal_fee,0) + nvl(a.bal_interest,0) - nvl(a.bal_payment,0) + nvl(a.bal_transfer,0) + nvl(a.cur_purchase,0) + nvl(a.cur_cash,0) + nvl(a.cur_te,0) + nvl(a.cur_unique,0) + nvl(a.cur_cheque,0) + nvl(a.cur_fee,0) + nvl(a.cur_interest,0) - nvl(a.cur_payment,0) + nvl(a.cur_transfer,0)) as du_no"; //// sql += " , b.opening_bal, b.closing_bal, b.min_due, a.acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') as payment_date"; //// sql += " ,a.cycoff_code"; //// sql += " ,round(((sysdate - nvl(a.acc_unpaid_status_date, sysdate)) + (nvl(a.acc_unpaid_status,0) - 1)*30)) number_days,'' as Ghi_chu"; //// sql += " from " + executedb.owner + "account a, " + executedb.owner + "cr_term b, card c, " + executedb.owner + "application d, " + executedb.owner + "cp_card e"; //// sql += " where a.cr_account_nbr = b.cr_account_nbr"; //// sql += " and a.cr_account_nbr = c.card_number"; //// sql += " and c.card_number = d.card_number"; //// sql += " and d.application_no = e.source_application_no"; //// sql += " and b.stat_date = ("; //// sql += " select max(f.stat_date)"; //// sql += " from " + executedb.owner + "cr_term f"; //// sql += " where f.cr_account_nbr = a.cr_account_nbr"; //// sql += " )"; //// sql += " and ((sysdate - nvl(a.acc_unpaid_status_date, sysdate)) + (nvl(a.acc_unpaid_status,0) - 1)*30) >= 106"; //// sql += " and not exists ("; //// sql += " select 1 "; //// sql += " from " + executedb.owner + "nab_credit_unpaid v"; //// sql += " where v.cr_account_nbr = a.cr_account_nbr"; //// sql += " )"; //// if (cbBranch.SelectedValue.ToString().ToUpper() != "9999")//9999: all //// sql += " and e.branch_code_dsa_code = '" + cbBranch.SelectedValue.ToString().ToUpper() + "'";// --set for choosing branch"; //// if (tbAccount.Text.Trim() != "") //// sql += " and a.cr_account_nbr = '" + tbAccount.Text.Trim() + "'";// --set for choosing account"; //// if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All //// sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; //// Session["sql"] = sql; //// Session["title"] = "DANH SÁCH KHÁCH HÀNG CẦN CHUYỂN NỢ QUÁ HẠN NHƯNG CHƯA CHUYỂN"; //// //Session["fromtime"] = dpFromDate.Text; //// // Session["totime"] = dpToDate.Text; //// Response.Write("<script type='text/javascript'>window.open('reportview.aspx','window_name','scrollbars=yes,menubar=no,height=700,width=785,resizable=yes,toolbar=no,location=no,status=no,fullscreen=no,titlebar=no,directories=no');</script>"); ////} else if (type.ToUpper() == "MB-TNT09") { //sql += " select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, a.acc_unpaid_status, b.payment_date,"; //sql += " ((sysdate - nvl(a.acc_unpaid_status_date, sysdate)) + (nvl(a.acc_unpaid_status,0) - 1)*30) number_days,'' as Ghi_chu"; if (Frm_main.admin_flag) { sql = " select to_char(rownum) as STT,a.cr_account_nbr,a.cr_account_nbr account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, e.legal_id as CMND "; } else { sql = " select to_char(rownum) as STT,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr ,a.cr_account_nbr account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, e.legal_id as CMND "; } sql += " ,(nvl(a.bal_purchase,0) + nvl(a.bal_cash,0) + nvl(a.bal_te,0) + nvl(a.bal_unique,0) + nvl(a.bal_cheque,0) + nvl(a.bal_fee,0) + nvl(a.bal_interest,0) - nvl(a.bal_payment,0) + nvl(a.bal_transfer,0) + nvl(a.cur_purchase,0) + nvl(a.cur_cash,0) + nvl(a.cur_te,0) + nvl(a.cur_unique,0) + nvl(a.cur_cheque,0) + nvl(a.cur_fee,0) + nvl(a.cur_interest,0) - nvl(a.cur_payment,0) + nvl(a.cur_transfer,0)) as du_no"; sql += " , b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') as payment_date"; sql += " ,a.cycoff_code"; sql += " ,nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 //sql += " and ((sysdate - nvl(a.acc_unpaid_status_date, sysdate)) + (nvl(a.acc_unpaid_status,0) - 1)*30) >= 91"; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 106"; sql += " and not exists ("; sql += " select 1 "; sql += " from " + Businessbp.executedb.owner + "nab_credit_unpaid v"; sql += " where v.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH KHÁCH HÀNG CẦN CHUYỂN NỢ QUÁ HẠN NHƯNG CHƯA CHUYỂN"; } #endregion chualam if (sql != "") { DataTable dtReport = Businessbp.executedb.getTable(sql, ref err); dtReport.Columns.Remove("account_nbr"); if (err != "") { MessageBox.Show(err); this.Cursor = Cursors.Default; return; } else { string file_path = tbFilePath.Text.Trim() + branch.Trim() + "_" + type + "_" + DateTime.Now.ToString("ddMMyyyy") + ".pdf"; //Export pdf try { if (dtReport.Rows.Count > 0) { rpt_Xu_Ly_No cry = new rpt_Xu_Ly_No(); cry.SetDataSource(dtReport); // string title = ""; cry.SetParameterValue("title", title); cry.SetParameterValue("MauBieu", "Mẫu: "); cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString()); cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString()); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; //CrExportOptions.ExportFormatType = ExportFormatType.WordForWindows; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export(); //--Fix for Load Report failse if (cry != null) { cry.Close(); cry.Dispose(); } //lấy dữ liệu insert table sql = ""; err = ""; sql = "insert into " + Businessbp.executedb.owner + "NAB_CREDIT_XLN_SENDMAIL"; sql += " (BRANCH_CODE ,FILE_PATH ,USER_CREATE ,DATE_CREATE,SEND_FLAG ,TYPE_REPORT)"; sql += "values ("; sql += "'" + branch + "',"; sql += "'" + file_path.Replace("\\",@"\") + "',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate,"; sql += "'0',"; sql += "'" + type + "'"; sql += ")"; Businessbp.executedb.ExecuteDML(sql, ref err); if (err != "") { MessageBox.Show(err); //return; // this.Cursor = Cursors.Default; } } else { // MessageBox.Show("");//+ card_number.Trim()); } } catch (Exception ex) { MessageBox.Show(ex.Message); this.Cursor = Cursors.Default; } } }//End for branch this.Cursor = Cursors.Default; tbStatus.Text += "-- Success"; } } }
private void button1_Click(object sender, EventArgs e) { /* Điều kiện kiểm tra * B1: kiểm tra trong ổ D:\KTT\SEND_EMAIL\BTBGD\yyyymmdd đã xử lý với ngày hiện tại chưa * Nếu đã xử lý: Không cho xử lý nữa * B2: Kiểm tra trong bảng NAB_CREDIT_SENDMAIL đã xử lý với ngày hiện tại chưa? Nếu rồi--> Không xử lý * Xử lý tạo file pdf và lưu vào đường dẫn trên D:\KTT\SEND_EMAIL\BTBGD\yyyymmdd\số thẻ.pdf * */ //B1: Kiểm tra đường dẫn đã tồn tại chưa? this.Cursor = Cursors.WaitCursor; string folderOut = tbFilePath.Text.Trim(); if (Directory.Exists(folderOut)) { MessageBox.Show("Đã xử lý SendMail ngày " + DateTime.Now.ToString("yyyyMMdd")); this.Cursor = Cursors.Default; return; // Directory.CreateDirectory(folderOut); } string sql = "", err = ""; sql += " select * from " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL a"; sql += " where trunc(a.DATE_CREATE)= trunc(sysdate)"; //sql += " and trunc(sysdate)>=(select trunc(max(b.stat_date)) from cr_term b)"; DataTable dt = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); this.Cursor = Cursors.Default; return; } else { if (dt.Rows.Count > 0) { MessageBox.Show("Đã xử lý SendMail ngày " + DateTime.Now.ToString("yyyyMMdd")); this.Cursor = Cursors.Default; return; } else { //Xử lý và insert vào table //tạo thư mục Directory.CreateDirectory(folderOut); try { sql = ""; err = ""; #region 2014-10-28 (6) old //sql = " select distinct a.cr_account_nbr "; //sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a"; //sql += " where trunc(a.stat_date)=trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))"; //sql += " order by a.cr_account_nbr asc"; #endregion 2014-10-28 (6) old #region 2014-10-28 (6) new sql += "select distinct a.cr_account_nbr"; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a, " + Businessbp.executedb.owner + "card c"; sql += " where a.card_number = c.card_number"; sql += " and trunc(a.stat_date) = trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy'))"; sql += " and trim(c.email_id) is not null"; //sql += " and a.card_number ='5331470000139786'"; sql += " order by a.cr_account_nbr asc"; #endregion 2014-10-28 (6) new DataTable dt_card_process = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); this.Cursor = Cursors.Default; return; } else { if (dt_card_process.Rows.Count <= 0) { MessageBox.Show("Chưa xử lý cuối ngày."); this.Cursor = Cursors.Default; return; } else { DataTable dt_bill = new DataTable(); for (int i = 0; i < dt_card_process.Rows.Count; i++) { //string card_number = dt_card_process.Rows[i]["card_number"].ToString(); string cr_account_nbr = dt_card_process.Rows[i]["cr_account_nbr"].ToString(); sql = ""; err = ""; #region old 2014-06-19 (2) /*sql = " select a.card_number, a.embossed_name,a.credit_limit"; sql += " ,to_char(a.processing_date,'dd/mm/yyyy') as processing_date"; sql += " ,to_char(a.payment_date,'dd/mm/yyyy') as payment_date"; sql += " ,a.opening_bal,a.total_debit,a.bal_payment,a.closing_bal,a.min_due"; sql += " ,to_char(a.transaction_date,'dd/mm/yyyy') as transaction_date"; sql += " ,to_char(a.value_date,'dd/mm/yyyy') as value_date"; sql += " ,a.arn,a.details,nvl(a.billing_amount,0) billing_amount"; sql += " ,(a.MAILING_ADDRESS1||' '||a.MAILING_ADDRESS2||' '||a.MAILING_ADDRESS3||' '||a.MAILING_ADDRESS4) address"; sql += " ,a.PHONE_HOME, a.PHONE_MOBILE, a.cr_account_nbr, a.transaction_amount "; sql += " ,(select b.currency_code_alpha from " + Businessbp.executedb.owner + "currency_table b where trim(b.currency_code) = trim(a.transaction_currency)) transaction_currency"; sql += " ,(select b.currency_code_alpha from " + Businessbp.executedb.owner + "currency_table b where trim(b.currency_code) = trim(a.billing_currency)) billing_currency"; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a"; sql += " where trunc(a.stat_date)=trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))"; //sql += " and a.card_number='" + card_number.Trim() + "'"; sql += " and a.cr_account_nbr='" + cr_account_nbr.Trim() + "'"; sql += " order by a.card_number asc,a.transaction_date asc";*/ #endregion old 2014-06-19 (2) //dt_bill = null; #region 2015-05-20 (10) comment //#region 2014-06-19 (2) //sql += " select r.card_number, "; //sql += " r.embossed_name, "; //sql += " r.credit_limit, "; //sql += " r.processing_date, "; //sql += " r.payment_date, "; //sql += " r.opening_bal, "; //sql += " r.total_debit, "; //sql += " r.bal_payment, "; //sql += " r.closing_bal, "; //sql += " r.min_due, "; //sql += " r.transaction_date, "; //sql += " r.value_date, "; //sql += " r.arn, "; //sql += " r.details, "; //sql += " r.billing_amount_du_co, "; //sql += " r.billing_amount_du_no, "; //sql += " r.address, "; //sql += " r.PHONE_HOME, "; //sql += " r.PHONE_MOBILE, "; //sql += " r.cr_account_nbr, "; //sql += " r.transaction_amount, "; //sql += " r.transaction_currency, "; //sql += " r.billing_currency, "; //sql += " to_char(nvl(r.cashback_amt_old, 0), '999,999,999,999') cashback_old, "; //2015-04-22 (9) //sql += " to_char(nvl(r.cashback_amt, 0), '999,999,999,999') cashback"; //2015-03-19 (8) //sql += " from (select a.card_number, "; //sql += " a.embossed_name, "; //sql += " a.credit_limit, "; ////sql += " to_char(a.processing_date, 'dd/mm/yyyy') as processing_date, "; //old 2014-08-26 (3) //sql += " to_char(a.stat_date, 'dd/mm/yyyy') as processing_date, "; // 2014-08-26 (3) //sql += " to_char(a.payment_date, 'dd/mm/yyyy') as payment_date, "; //sql += " a.opening_bal, "; //sql += " a.total_debit, "; //sql += " a.bal_payment, "; //sql += " a.closing_bal, "; //sql += " a.min_due, "; //sql += " to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date, "; //sql += " to_char(a.value_date, 'dd/mm/yyyy') as value_date, "; //sql += " a.arn, "; //sql += " a.details, "; //sql += " decode(e.transaction_sign, "; //sql += " '0', "; //sql += " to_number(''), "; //sql += " '1', "; //sql += " a.billing_amount, "; //sql += " a.billing_amount) billing_amount_du_co, "; //sql += " decode(e.transaction_sign, "; //sql += " '0', "; //sql += " a.billing_amount, "; //sql += " '1', "; //sql += " to_number(''), "; //sql += " a.billing_amount) billing_amount_du_no, "; //sql += " (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' || "; //sql += " a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address, "; //sql += " a.PHONE_HOME, "; //sql += " a.PHONE_MOBILE, "; //sql += " a.cr_account_nbr, "; //sql += " a.transaction_amount, "; //sql += " (select b.currency_code_alpha "; //sql += " from " + Businessbp.executedb.owner + "currency_table b "; //sql += " where trim(b.currency_code) = trim(a.transaction_currency)) transaction_currency, "; //sql += " (select b.currency_code_alpha "; //sql += " from " + Businessbp.executedb.owner + "currency_table b "; //sql += " where trim(b.currency_code) = trim(a.billing_currency)) billing_currency, "; //#region 2015-03-19 (8) ////sql += " " + Businessbp.executedb.owner + "nab_getCashBack_Amount(a.cr_account_nbr,"; ////sql += " to_char(ADD_MONTHS(a.stat_date,"; ////sql += " -1) + 1,"; ////sql += " 'dd/mm/yyyy'),"; ////sql += " to_char(a.stat_date,"; ////sql += " 'dd/mm/yyyy')) cashback_amt"; //#endregion 2015-03-19 (8) //#region 2015-04-22 (9) //sql += " (select sum(nvl(b.transaction_amount, 0)) "; //sql += " from " + Businessbp.executedb.owner + "nab_balance_credit b"; //sql += " where 1 = 1"; //sql += " and b.type = '9'"; //sql += " and b.user_create = 'AutoCashBack'"; //sql += " and b.batch_id = '000656'"; //sql += " and b.card_number = a.cr_account_nbr) cashback_amt_old,"; //sql += " " + Businessbp.executedb.owner + "nab_getCashBack_Amount(a.cr_account_nbr,"; //sql += " '11/03/2015','10/04/2015') cashback_amt"; //#endregion 2015-04-22 (9) //sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a, " + Businessbp.executedb.owner + "trans_def_stand e "; //sql += " where 1 = 1 "; //sql += " and a.transaction_code = e.transaction_code "; //sql += " and trunc(a.stat_date) = "; //sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) "; //sql += " and a.cr_account_nbr = '" + cr_account_nbr.Trim() + "' "; //sql += " UNION ALL "; //sql += " select a.card_number, "; //sql += " a.embossed_name, "; //sql += " a.credit_limit, "; ////sql += " to_char(a.processing_date, 'dd/mm/yyyy') as processing_date, "; //old 2014-08-26 (3) //sql += " to_char(a.stat_date, 'dd/mm/yyyy') as processing_date, "; //2014-08-26 (3) //sql += " to_char(a.payment_date, 'dd/mm/yyyy') as payment_date, "; //sql += " a.opening_bal, "; //sql += " a.total_debit, "; //sql += " a.bal_payment, "; //sql += " a.closing_bal, "; //sql += " a.min_due, "; //sql += " to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date, "; //sql += " to_char(a.value_date, 'dd/mm/yyyy') as value_date, "; //sql += " a.arn, "; //sql += " a.details, "; //sql += " a.billing_amount billing_amount_du_co, "; //sql += " a.billing_amount billing_amount_du_no, "; //sql += " (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' || "; //sql += " a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address, "; //sql += " a.PHONE_HOME, "; //sql += " a.PHONE_MOBILE, "; //sql += " a.cr_account_nbr, "; //sql += " a.transaction_amount transaction_amount, "; //sql += " (select b.currency_code_alpha "; //sql += " from " + Businessbp.executedb.owner + "currency_table b "; //sql += " where trim(b.currency_code) = trim(a.transaction_currency)) transaction_currency, "; //sql += " (select b.currency_code_alpha "; //sql += " from " + Businessbp.executedb.owner + "currency_table b "; //sql += " where trim(b.currency_code) = trim(a.billing_currency)) billing_currency, "; //#region 2015-03-19 (8) ////sql += " " + Businessbp.executedb.owner + "nab_getCashBack_Amount(a.cr_account_nbr,"; ////sql += " to_char(ADD_MONTHS(a.stat_date,"; ////sql += " -1) + 1,"; ////sql += " 'dd/mm/yyyy'),"; ////sql += " to_char(a.stat_date,"; ////sql += " 'dd/mm/yyyy')) cashback_amt"; //#endregion 2015-03-19 (8) //#region 2015-04-22 (9) //sql += " (select sum(nvl(b.transaction_amount, 0)) "; //sql += " from " + Businessbp.executedb.owner + "nab_balance_credit b"; //sql += " where 1 = 1"; //sql += " and b.type = '9'"; //sql += " and b.user_create = 'AutoCashBack'"; //sql += " and b.batch_id = '000656'"; //sql += " and b.card_number = a.cr_account_nbr) cashback_amt_old,"; //sql += " " + Businessbp.executedb.owner + "nab_getCashBack_Amount(a.cr_account_nbr,"; //sql += " '11/03/2015','10/04/2015') cashback_amt"; //#endregion 2015-04-22 (9) //sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a "; //sql += " where trunc(a.stat_date) = "; //sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) "; //sql += " and trim(a.transaction_code) is null "; //sql += " and a.cr_account_nbr = '" + cr_account_nbr.Trim() + "') r , "; //#region 2014-10-03 (5) ////sql += " (select s.cr_account_nbr, s.so_luong "; ////sql += " from (select r.cr_account_nbr, "; ////sql += " count(r.microfilm_ref_number) as so_luong "; ////sql += " from (select distinct cct.cr_account_nbr, "; ////sql += " cct.microfilm_ref_number "; ////sql += " from " + Businessbp.executedb.owner + "cr_current_trans cct, "; ////sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l "; ////sql += " where cct.cr_account_nbr = l.cr_account_nbr "; ////sql += " and trunc(cct.statement_date) = "; ////sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) "; ////sql += " union all "; ////sql += " select distinct cht.cr_account_nbr, "; ////sql += " cht.microfilm_ref_number "; ////sql += " from " + Businessbp.executedb.owner + "cr_hist_trans cht, "; ////sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l "; ////sql += " where cht.cr_account_nbr = l.cr_account_nbr "; ////sql += " and trunc(cht.statement_date) = "; ////sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy'))) r "; ////sql += " group by r.cr_account_nbr) s "; ////sql += " where s.so_luong > 0) t "; ////sql += " where 1 = 1 "; ////sql += " and r.cr_account_nbr = t.cr_account_nbr "; //#endregion 2014-10-03 (5) //#region 2014-10-28 (7) //sql += " (select distinct l.cr_account_nbr "; //sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log l "; //sql += " where 1 = 1 "; //sql += " and trunc(l.stat_date) = to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy') "; //sql += " and l.cr_account_nbr = '" + cr_account_nbr.Trim() + "' "; //sql += " and (l.closing_bal <> 0 or "; //sql += " l.cr_account_nbr = "; //sql += " (select s.cr_account_nbr "; //sql += " from (select r.cr_account_nbr, "; //sql += " count(r.microfilm_ref_number) as so_luong "; //sql += " from (select distinct cct.cr_account_nbr, "; //sql += " cct.microfilm_ref_number "; //sql += " from " + Businessbp.executedb.owner + "cr_current_trans cct, "; //sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l "; //sql += " where cct.cr_account_nbr = l.cr_account_nbr "; //sql += " and trunc(cct.statement_date) = "; //sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', "; //sql += " 'dd/mm/yyyy')) "; //sql += " union all "; //sql += " select distinct cht.cr_account_nbr, "; //sql += " cht.microfilm_ref_number "; //sql += " from " + Businessbp.executedb.owner + "cr_hist_trans cht, "; //sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l "; //sql += " where cht.cr_account_nbr = l.cr_account_nbr "; //sql += " and trunc(cht.statement_date) = "; //sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', "; //sql += " 'dd/mm/yyyy'))) r "; //sql += " group by r.cr_account_nbr) s "; //sql += " where s.so_luong > 0 "; //sql += " and s.cr_account_nbr = '" + cr_account_nbr.Trim() + "'))) t "; //sql += " where 1 = 1 "; //sql += " and r.cr_account_nbr = t.cr_account_nbr "; //#endregion 2014-10-28 (7) //////sql += " order by r.card_number asc, to_date(r.transaction_date,'dd/mm/yyyy') asc "; // 2014-08-26 (3) ////sql += " order by r.card_number asc, to_date(r.transaction_date,'dd/mm/yyyy') desc "; // 2014-10-03 (4) ////#endregion 2014-06-19 (2) //#endregion 2015-05-20 (10) comment #endregion 2015-05-20 (10) comment if (DateTime.ParseExact(dateTimePicker1.Text.Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture) < DateTime.ParseExact("24/09/2015", "dd/MM/yyyy", CultureInfo.InvariantCulture)) // 2015-09-08 (12) { #region 2015-05-20 (10) sql += "select distinct A.*,"; sql += " case"; sql += " when (A.date_create + 90 <"; sql += " add_months(to_date(A.processing_date, 'dd/mm/yyyy'),"; sql += " -1)) then"; sql += " '0'"; sql += " else"; sql += " to_char(decode(decode(A.machinhsachtd,"; sql += " 'B01 - STK - UU DAI',"; sql += " '1',"; sql += " '0'),"; sql += " '1',"; sql += " " + Businessbp.executedb.owner + "nab_getcashback_amount(A.cr_account_nbr,"; sql += " to_char(add_months(to_date(A.processing_date,"; sql += " 'dd/mm/yyyy'),"; sql += " -1) + 1,"; sql += " 'dd/mm/yyyy'),"; sql += " to_char(least(A.date_create + 90,"; sql += " to_date(A.processing_date,"; sql += " 'dd/mm/yyyy')),"; sql += " 'dd/mm/yyyy')),"; sql += " 0),'999,999,999,999')"; sql += " end cashback"; sql += " from (select r.card_number,"; sql += " r.embossed_name,"; sql += " r.credit_limit,"; sql += " r.processing_date,"; sql += " r.payment_date,"; sql += " r.opening_bal,"; sql += " r.total_debit,"; sql += " r.bal_payment,"; sql += " r.closing_bal,"; sql += " r.min_due,"; sql += " r.transaction_date,"; sql += " r.value_date,"; sql += " r.arn,"; sql += " r.details,"; sql += " r.billing_amount_du_co,"; sql += " r.billing_amount_du_no,"; sql += " r.address,"; sql += " r.PHONE_HOME,"; sql += " r.PHONE_MOBILE,"; sql += " r.cr_account_nbr,"; sql += " r.transaction_amount,"; sql += " r.transaction_currency,"; sql += " r.billing_currency,"; sql += " r.machinhsachtd,"; sql += " (select date_create"; sql += " from " + Businessbp.executedb.owner + "card"; sql += " where card_number = r.cr_account_nbr) date_create"; sql += " from (select a.card_number,"; sql += " a.embossed_name,"; sql += " a.credit_limit,"; sql += " to_char(a.stat_date, 'dd/mm/yyyy') as processing_date,"; sql += " to_char(a.payment_date, 'dd/mm/yyyy') as payment_date,"; sql += " a.opening_bal,"; sql += " a.total_debit,"; sql += " a.bal_payment,"; sql += " a.closing_bal,"; sql += " a.min_due,"; sql += " to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date,"; sql += " to_char(a.value_date, 'dd/mm/yyyy') as value_date,"; sql += " a.arn,"; sql += " a.details,"; sql += " decode(e.transaction_sign,"; sql += " '0',"; sql += " to_number(''),"; sql += " '1',"; sql += " a.billing_amount,"; sql += " a.billing_amount) billing_amount_du_co,"; sql += " decode(e.transaction_sign,"; sql += " '0',"; sql += " a.billing_amount,"; sql += " '1',"; sql += " to_number(''),"; sql += " a.billing_amount) billing_amount_du_no,"; sql += " (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' ||"; sql += " a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address,"; sql += " a.PHONE_HOME,"; sql += " a.PHONE_MOBILE,"; sql += " a.cr_account_nbr,"; sql += " a.transaction_amount,"; sql += " (select b.currency_code_alpha"; sql += " from " + Businessbp.executedb.owner + "currency_table b"; sql += " where trim(b.currency_code) ="; sql += " trim(a.transaction_currency)) transaction_currency,"; sql += " (select b.currency_code_alpha"; sql += " from " + Businessbp.executedb.owner + "currency_table b"; sql += " where trim(b.currency_code) ="; sql += " trim(a.billing_currency)) billing_currency,"; sql += " c.machinhsachtd"; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a,"; sql += " " + Businessbp.executedb.owner + "trans_def_stand e,"; sql += " " + Businessbp.executedb.owner + "application app,"; sql += " " + Businessbp.executedb.owner + "cp_card c"; sql += " where 1 = 1"; sql += " and a.transaction_code = e.transaction_code"; sql += " and a.cr_account_nbr = app.card_number"; sql += " and app.application_no = c.source_application_no"; sql += " and trunc(a.stat_date) ="; sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy'))"; sql += " and a.cr_account_nbr = '" + cr_account_nbr.Trim() + "'"; sql += " UNION ALL"; sql += " select a.card_number,"; sql += " a.embossed_name,"; sql += " a.credit_limit,"; sql += " to_char(a.stat_date, 'dd/mm/yyyy') as processing_date,"; sql += " to_char(a.payment_date, 'dd/mm/yyyy') as payment_date,"; sql += " a.opening_bal,"; sql += " a.total_debit,"; sql += " a.bal_payment,"; sql += " a.closing_bal,"; sql += " a.min_due,"; sql += " to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date,"; sql += " to_char(a.value_date, 'dd/mm/yyyy') as value_date,"; sql += " a.arn,"; sql += " a.details,"; sql += " a.billing_amount billing_amount_du_co,"; sql += " a.billing_amount billing_amount_du_no,"; sql += " (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' ||"; sql += " a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address,"; sql += " a.PHONE_HOME,"; sql += " a.PHONE_MOBILE,"; sql += " a.cr_account_nbr,"; sql += " a.transaction_amount transaction_amount,"; sql += " (select b.currency_code_alpha"; sql += " from " + Businessbp.executedb.owner + "currency_table b"; sql += " where trim(b.currency_code) ="; sql += " trim(a.transaction_currency)) transaction_currency,"; sql += " (select b.currency_code_alpha"; sql += " from " + Businessbp.executedb.owner + "currency_table b"; sql += " where trim(b.currency_code) ="; sql += " trim(a.billing_currency)) billing_currency,"; sql += " c.machinhsachtd"; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a,"; sql += " " + Businessbp.executedb.owner + "application app,"; sql += " " + Businessbp.executedb.owner + "cp_card c"; sql += " where trunc(a.stat_date) ="; sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy'))"; sql += " and a.cr_account_nbr = app.card_number"; sql += " and app.application_no = c.source_application_no"; sql += " and trim(a.transaction_code) is null"; sql += " and a.cr_account_nbr = '" + cr_account_nbr.Trim() + "') r,"; sql += " (select distinct l.cr_account_nbr"; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log l"; sql += " where 1 = 1"; sql += " and trunc(l.stat_date) ="; sql += " to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')"; sql += " and l.cr_account_nbr = '" + cr_account_nbr.Trim() + "'"; sql += " and (l.closing_bal <> 0 or"; sql += " l.cr_account_nbr ="; sql += " (select s.cr_account_nbr"; sql += " from (select r.cr_account_nbr,"; sql += " count(r.microfilm_ref_number) as so_luong"; sql += " from (select distinct cct.cr_account_nbr,"; sql += " cct.microfilm_ref_number"; sql += " from " + Businessbp.executedb.owner + "cr_current_trans cct,"; sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l"; sql += " where cct.cr_account_nbr ="; sql += " l.cr_account_nbr"; sql += " and trunc(cct.statement_date) ="; sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "',"; sql += " 'dd/mm/yyyy'))"; sql += " union all"; sql += " select distinct cht.cr_account_nbr,"; sql += " cht.microfilm_ref_number"; sql += " from " + Businessbp.executedb.owner + "cr_hist_trans cht,"; sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l"; sql += " where cht.cr_account_nbr ="; sql += " l.cr_account_nbr"; sql += " and trunc(cht.statement_date) ="; sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "',"; sql += " 'dd/mm/yyyy'))) r"; sql += " group by r.cr_account_nbr) s"; sql += " where s.so_luong > 0"; sql += " and s.cr_account_nbr = '" + cr_account_nbr.Trim() + "'))) t"; sql += " where 1 = 1"; sql += " and r.cr_account_nbr = t.cr_account_nbr"; sql += " order by r.card_number asc,"; sql += " to_date(r.transaction_date, 'dd/mm/yyyy') desc) A"; sql += " order by A.card_number asc, to_date(A.transaction_date, 'dd/mm/yyyy') desc ";//2015-06-23 (11) #endregion 2015-05-20 (10) } #region 2015-09-08 (12) else { sql = " select (substr(l.card_number,1,4) || ' '||substr(l.card_number,5,2) || 'xx xxxx '||substr(l.card_number,13,4)) card_number, "; sql += " (substr(l.cr_account_nbr,1,4) || ' '||substr(l.cr_account_nbr,5,2) || 'xx xxxx '||substr(l.cr_account_nbr,13,4)) cr_account_nbr, "; sql += " l.embossed_name, "; sql += " l.phone_mobile, "; sql += " nvl(l.credit_limit,0) credit_limit_acc, "; sql += " nvl(l.credit_limit_client,0) credit_limit_client, "; sql += " nvl(l.avail_amount_acc,0) avail_amount_acc, "; sql += " nvl(l.avail_amount_client,0) avail_amount_client, "; sql += " to_char(l.stat_date, 'dd/mm/yyyy') as stat_date, "; sql += " to_char(l.payment_date, 'dd/mm/yyyy') as payment_date, "; sql += " nvl(l.opening_bal,0) opening_bal, "; sql += " nvl(l.total_debit,0) total_debit, "; sql += " (nvl(l.fee,0) + nvl(l.interest,0)) fee_and_interest, "; sql += " nvl(l.bal_payment,0) bal_payment, "; sql += " nvl(l.closing_bal,0) closing_bal, "; sql += " (trim(l.MAILING_ADDRESS1) || ' ' || trim(l.MAILING_ADDRESS2) || ' ' || "; sql += " trim(l.MAILING_ADDRESS3) || ' ' || trim(l.MAILING_ADDRESS4)) address, "; sql += " to_char(l.transaction_date, 'dd/mm/yyyy') as transaction_date, "; sql += " to_char(l.value_date, 'dd/mm/yyyy') as value_date, "; sql += " l.arn, "; sql += " nvl(l.transaction_amount,0) transaction_amount, "; sql += " (select '('||ccy.currency_code_alpha||')' "; sql += " from " + Businessbp.executedb.owner + "currency_table ccy "; sql += " where trim(ccy.currency_code) = "; sql += " trim(l.transaction_currency)) "; sql += " transaction_currency, "; sql += " nvl(l.billing_amount,0) billing_amount, "; sql += " decode((select tds.transaction_sign "; sql += " from " + Businessbp.executedb.owner + "trans_def_stand tds "; sql += " where tds.transaction_code = l.transaction_code), "; sql += " '0','(DR)','1','(CR)','') transaction_sign, "; sql += " l.details, "; sql += " (case when not exists (select 1 "; sql += " from " + Businessbp.executedb.owner + "nab_credit_unpaid un "; sql += " where 1 = 1"; sql += " and un.cr_account_nbr = l.cr_account_nbr "; sql += " and substr(un.nqh_code, 1, 1) = 'O') then "; sql += " nvl(l.min_due, 0) "; sql += " else "; sql += " nvl(l.closing_bal, 0) "; sql += " end ) min_due, "; sql += " nvl(l.min_due_install,0) min_due_install, "; sql += " nvl(l.min_due_crterm,0) min_due_crterm, "; sql += " nvl(l.cash_back_amount,0) cash_back_amount, "; sql += " nvl(l.diamondbay_amount,0) diamondbay_amount, "; sql += " nvl(l.diamondbay_rank,0) diamondbay_rank, "; sql += " nvl(l.loyalty_score_earned,0) loyalty_score_earned, "; sql += " nvl(l.loyalty_score_spent,0) loyalty_score_spent, "; sql += " nvl(l.loyalty_cash_exchanged,0) loyalty_cash_exchanged, "; sql += " nvl(l.loyalty_score_avail,0) loyalty_score_avail "; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log l, "; sql += " " + Businessbp.executedb.owner + "application app, "; sql += " " + Businessbp.executedb.owner + "cp_card cc, "; sql += " " + Businessbp.executedb.owner + "card c "; sql += " where 1=1 "; sql += " and trunc(l.stat_date) =trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) "; sql += " and (l.cr_account_nbr = app.card_number) "; sql += " and l.card_number = c.card_number "; sql += " and app.application_no = cc.source_application_no "; //sql += " and (trim(l.arn) is not null or l.card_number=l.cr_account_nbr) "; //2015-10-21 (13) //old 2015-11-30 (14) sql += " and (trim(l.arn) is not null or (l.total_debit+l.fee+l.bal_payment+abs(l.closing_bal)) <>0 ) "; //2015-11-30 (14) sql += " and trim(c.cr_account_nbr)='" + cr_account_nbr.Trim() + "' "; sql += " order by l.card_number asc, to_date(l.transaction_date, 'dd/mm/yyyy') desc,to_date(l.value_date, 'dd/mm/yyyy') desc "; } #endregion 2015-09-08 (12) dt_bill.Clear(); dt_bill = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); this.Cursor = Cursors.Default; return; } else { //dt_bill.Columns.Add("card_number", typeof(string));//card_number //dt_bill.Columns.Add("embossed_name", typeof(string));//embossed_name //dt_bill.Columns.Add("credit_limit", typeof(string));//credit_limit //dt_bill.Columns.Add("processing_date", typeof(string));//processing_date //dt_bill.Columns.Add("payment_date", typeof(string));//payment_date //dt_bill.Columns.Add("opening_bal", typeof(string));//opening_bal //dt_bill.Columns.Add("total_debit", typeof(string));//total_debit //dt_bill.Columns.Add("bal_payment", typeof(string));//bal_payment //dt_bill.Columns.Add("closing_bal", typeof(string));//closing_bal //dt_bill.Columns.Add("min_due", typeof(string));//min_due //dt_bill.Columns.Add("transaction_date", typeof(string));//transaction_date //dt_bill.Columns.Add("value_date", typeof(string));//value_date //dt_bill.Columns.Add("arn", typeof(string)); //dt_bill.Columns.Add("details", typeof(string)); //dt_bill.Columns.Add("billing_amount", typeof(string)); ////get data //string card_number = dt_card_process.Rows[i]["card_number"].ToString(); //string embossed_name = dt_card_process.Rows[i]["embossed_name"].ToString(); //string credit_limit = dt_card_process.Rows[i]["credit_limit"].ToString(); //string processing_date = dt_card_process.Rows[i]["processing_date"].ToString(); //string payment_date = dt_card_process.Rows[i]["payment_date"].ToString(); //string opening_bal = dt_card_process.Rows[i]["opening_bal"].ToString(); //string total_debit = dt_card_process.Rows[i]["total_debit"].ToString(); //string bal_payment = dt_card_process.Rows[i]["bal_payment"].ToString(); //string closing_bal = dt_card_process.Rows[i]["closing_bal"].ToString(); //string min_due = dt_card_process.Rows[i]["min_due"].ToString(); //string transaction_date = dt_card_process.Rows[i]["transaction_date"].ToString(); //string value_date = dt_card_process.Rows[i]["value_date"].ToString(); //string arn = dt_card_process.Rows[i]["arn"].ToString(); //string details = dt_card_process.Rows[i]["details"].ToString(); //string billing_amount = dt_card_process.Rows[i]["billing_amount"].ToString(); //try //{ // dt_bill.Rows.Add(card_number, embossed_name, credit_limit, processing_date, payment_date, opening_bal, total_debit, bal_payment // , closing_bal, min_due, transaction_date, value_date, arn, details, billing_amount); //} //catch (Exception ex) //{ // MessageBox.Show(ex.ToString()); //} //string file_path = tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf"; string file_path = tbFilePath.Text.Trim() + "\\" + cr_account_nbr.Trim() + ".pdf"; //Export pdf try { if (dt_bill.Rows.Count > 0) { if (DateTime.ParseExact(dateTimePicker1.Text.Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture) < DateTime.ParseExact("24/09/2015", "dd/MM/yyyy", CultureInfo.InvariantCulture)) // 2015-09-08 (12) { //Rpt_Credit_Billing_new cry = new Rpt_Credit_Billing_new(); //2015-03-19 (8) old Rpt_Credit_Billing_new_2 cry = new Rpt_Credit_Billing_new_2(); //2015-03-19 (8) new cry.SetDataSource(dt_bill); string title = ""; string title2 = ""; string title3 = ""; title = "BẢNG THÔNG BÁO GIAO DỊCH"; title2 = "(Từ ngày " + dateTimePicker1.Value.AddMonths(-1).ToString("dd/MM/yyyy").Trim() + " đến ngày " + dateTimePicker1.Text.Trim() + " )"; cry.SetParameterValue("title", title); cry.SetParameterValue("title2", title2); title3 = Get_title3(cr_account_nbr); if (title3.Trim() != "") cry.SetParameterValue("title3", title3); else { MessageBox.Show("Err when get title3"); return; } // a.title3 = "Yêu cầu thanh toán trước 10h ngày:"; ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export(); //--Fix for Load Report failse if (cry != null) { cry.Close(); cry.Dispose(); } } #region 2015-09-08 (12) else { Rpt_Credit_Billing_new_3 cry = new Rpt_Credit_Billing_new_3(); cry.SetDataSource(dt_bill); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export(); //--Fix for Load Report failse if (cry != null) { cry.Close(); cry.Dispose(); } } #endregion 2015-09-08 (12) //ExportOptions CrExportOptions; //DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); //PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); //CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; //CrExportOptions = cry.ExportOptions; //{ // CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; // CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; // CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; // CrExportOptions.FormatOptions = CrFormatTypeOptions; //} //cry.Export(); ////--Fix for Load Report failse //if (cry != null) //{ // cry.Close(); // cry.Dispose(); //} //lấy dữ liệu insert table sql = ""; err = ""; //sql += " select distinct to_char(a.stat_date,'dd/mm/yyyy') DATE_START,c.email_id EMAIL,to_char(a.stat_date,'dd/mm/yyyy') BILL_DATE"; //sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a," + Businessbp.executedb.owner + "application b, " + Businessbp.executedb.owner + "cp_card c"; //sql += " where a.card_number=b.card_number"; //sql += " and b.application_no=c.source_application_no"; //sql += " and trunc(a.stat_date) =trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))"; //sql += " and a.card_number='" + card_number.Trim() + "'"; //sql += " and c.email_id is not null"; //sql += " select distinct to_char(a.stat_date,'dd/mm/yyyy') DATE_START,e.email_id EMAIL,to_char(a.stat_date,'dd/mm/yyyy') BILL_DATE,a.card_number"; //sql += " select distinct to_char(a.stat_date,'dd/mm/yyyy') DATE_START,e.email_id EMAIL,to_char(a.stat_date,'dd/mm/yyyy') BILL_DATE,a.cr_account_nbr"; //sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a," + Businessbp.executedb.owner + "card e"; //sql += " where a.cr_account_nbr=e.card_number"; // 2016-03-04 (15) //sql += " and trunc(a.stat_date) =trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))"; ////sql += " and e.email_id is not null"; //OLD 2014-05-08 (1) //sql += " and trim(e.email_id) is not null"; // 2014-05-08 (1) ////sql += " and a.card_number='" + card_number.Trim() + "'"; //sql += " and a.cr_account_nbr='" + cr_account_nbr.Trim() + "'"; #region 2016-06-16 (16) sql += "select distinct to_char(a.stat_date, 'dd/mm/yyyy') DATE_START, "; sql += " e.email_id EMAIL, "; sql += " to_char(a.stat_date, 'dd/mm/yyyy') BILL_DATE, "; sql += " a.cr_account_nbr, "; sql += " cor.email_id EMAILCOR "; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a "; sql += " left join " + Businessbp.executedb.owner + "nab_corporate_card cor "; sql += " on a.cr_account_nbr = cor.cr_account_number_corporate "; sql += " and trim(cor.email_id) is not null, " + Businessbp.executedb.owner + "card e "; sql += " where a.cr_account_nbr = e.card_number "; sql += " and trunc(a.stat_date) =trunc(to_date('" + dateTimePicker1.Text.Trim ( ) + "','dd/mm/yyyy'))"; sql += " and trim(e.email_id) is not null "; sql += " and a.cr_account_nbr='" + cr_account_nbr.Trim ( ) + "'"; #endregion 2016-06-16 (16) sql += " "; DataTable dtInsert = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); // return; } else { if (dtInsert.Rows.Count > 0) { string DATE_START = dtInsert.Rows[0]["DATE_START"].ToString().Trim(); string EMAIL = dtInsert.Rows[0]["EMAIL"].ToString().Trim(); string BILL_DATE = dtInsert.Rows[0]["BILL_DATE"].ToString().Trim(); // sql = ""; err = ""; if (EMAIL.Trim() == "") { } else { if (!cr_account_nbr.Substring ( 0, 4 ).Trim ( ).Equals ( "5592" )) { sql = "insert into " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)"; sql += "values ("; sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ","; //sql += "'" + card_number + "',"; sql += "'" + cr_account_nbr + "',"; sql += "'" + file_path + "',"; sql += "" + ( EMAIL.Trim ( ) == "" ? "null" : ( "'" + EMAIL + "'" ) ) + ","; sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ","; sql += "'0',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate"; sql += ")"; Businessbp.executedb.ExecuteDML ( sql, ref err ); if (err != "") { MessageBox.Show ( err ); //return; } } else //2016-06-16 (16) { string EMAILCOR = dtInsert.Rows[0]["EMAILCOR"].ToString ( ).Trim ( ); if (EMAILCOR.Trim ( ) != "") { sql = " begin "; sql += " insert into " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)"; sql += " values ("; sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ","; //sql += "'" + card_number + "',"; sql += "'" + cr_account_nbr + "',"; sql += "'" + file_path + "',"; sql += "" + ( EMAILCOR.Trim ( ) == "" ? "null" : ( "'" + EMAILCOR.Trim ( ) + "'" ) ) + ","; sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ","; sql += "'0',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate"; sql += ");"; sql += " insert into " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)"; sql += " values ("; sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ","; //sql += "'" + card_number + "',"; sql += "'" + cr_account_nbr + "',"; sql += "'" + file_path + "',"; sql += "" + ( EMAIL.Trim ( ) == "" ? "null" : ( "'" + EMAIL + "'" ) ) + ","; sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ","; sql += "'0',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate"; sql += ");"; sql += " insert into " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)"; sql += " values ("; sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ","; //sql += "'" + card_number + "',"; sql += "'" + cr_account_nbr + "',"; sql += "'" + file_path + "',"; sql += " '*****@*****.**',"; sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ","; sql += "'0',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate"; sql += ");"; sql += " commit;"; sql += " exception"; sql += " when others then"; sql += " rollback;"; sql += " end;"; Businessbp.executedb.ExecuteDML ( sql, ref err ); if (err != "") { MessageBox.Show ( err ); //return; } } } } } else//Nếu không có dữ liệu { } } } else { //MessageBox.Show("" + card_number.Trim()); //MessageBox.Show("" + cr_account_nbr.Trim()); //2014-10-28 Dong tam thoi MessageBox } } catch (Exception ex) { MessageBox.Show(ex.Message); } } } tbStatus.Text = "Successful!!!"; this.Cursor = Cursors.Default; } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } }
private void btProcess_Click(object sender, EventArgs e) { try { if (rbMBPHT08.Checked) { string sql = "", err = ""; DataTable dtReport = new DataTable(); string from_dt = dpFromDate.Text; string to_dt = dpToDate.Text; #region SQL_PHT08 sql += " select h.branch_code,h.branch_name,nvl(sum(h.SoLuongThe),0) SoLuongThe "; sql += " from ( "; sql += " Select trim(cp.branch_receive_card ) branch_code "; sql += " ,(select b.code ||'- '|| b.name from " + Businessbp.executedb.owner + "branch b "; sql += " where trim(b.code)=trim(cp.branch_receive_card) and b.amnd_state='A' "; sql += " ) branch_name "; sql += " ,count(c.contract_number) as SoLuongThe "; sql += " from " + Businessbp.executedb.owner + "acnt_contract c"; sql += " ,nab_cp_card_prepaid cp,"; sql += " nab_product sp "; sql += " where c.add_info_01 = cp.source_application_no "; sql += " and c.amnd_state = 'A'"; sql += " and c.con_cat = 'C' "; sql += " and c.contr_status = '267'"; sql += " and trunc(c.date_open) >= trunc(to_date('" + from_dt + "','dd/mm/yyyy')) "; sql += " and trunc(c.date_open) <= trunc(to_date('" + to_dt + "','dd/mm/yyyy')) "; sql += " and sp.product_code <> '333' "; sql += " and c.product = sp.product_code "; sql += " and sp.type in ('D','P') "; if (cb_branch.SelectedValue.ToString().Trim() != "888") sql += " and trim(cp.branch_receive_card) ='" + cb_branch.SelectedValue.ToString().Trim() + "' "; sql += " group by cp.branch_receive_card "; sql += " union all "; sql += " Select trim(cd.branch_receive_card) branch_code, "; sql += " (select b.code ||'- '|| b.name from " + Businessbp.executedb.owner + "branch b "; sql += " where trim(b.code)=trim(cd.branch_receive_card) and b.amnd_state='A') branch_name "; sql += " ,count(c.contracT_number) as SoLuongThe "; sql += " from " + Businessbp.executedb.owner + "acnt_contract c,nab_cp_card cd, nab_product sp2"; sql += " where c.add_info_01 = cd.source_application_no "; sql += " and c.amnd_state = 'A'"; sql += " and c.con_cat = 'C' "; sql += " and c.contr_status = '267'"; sql += " and trunc(c.date_open) >= trunc(to_date('" + from_dt + "','dd/mm/yyyy')) "; sql += " and trunc(c.date_open) <= trunc(to_date('" + to_dt + "','dd/mm/yyyy')) "; sql += " and sp2.product_code <> '333' "; sql += " and c.product = sp2.product_code "; sql += " and sp2.type in ('C') "; if (cb_branch.SelectedValue.ToString().Trim() != "888") sql += " and trim(cd.branch_receive_card) ='" + cb_branch.SelectedValue.ToString().Trim() + "' "; sql += " group by cd.branch_receive_card "; sql += " )h "; sql += " group by h.branch_code,h.branch_name "; #endregion SQL_PHT08 this.Cursor = Cursors.WaitCursor; string type = "MB_PHT08"; if (sql != "") { dtReport = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); this.Cursor = Cursors.Default; return; } else { string folderOut = tbFilePath.Text.Trim() + "" + type + "\\"; ; //Tao thu muc if (!Directory.Exists(folderOut)) { Directory.CreateDirectory(folderOut); } //Export pdf #region Export try { if (dtReport.Rows.Count > 0) { for (int i = 0; i < dtReport.Rows.Count; i++) { string branch = dtReport.Rows[i]["branch_code"].ToString().Trim(); string Branch_name = dtReport.Rows[i]["branch_name"].ToString().Trim(); string file_path = folderOut.Trim() + branch.Trim() + "_" + type + "_" + dpFromDate.Text.Trim().Replace("/", "") + "_" + dpToDate.Text.Trim().Replace("/", "") + ".doc"; ConvertNumToStr objConvert = new ConvertNumToStr(); string SoLuongThe_Chu = ""; string SoLuongThe_Nbr = dtReport.Rows[i]["SoLuongThe"].ToString().Trim(); SoLuongThe_Chu = objConvert.converNumToString(objConvert.slipArray(SoLuongThe_Nbr)); string Date_Send = " ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year; string From_To_Date = dpFromDate.Text + " - " + dpToDate.Text; Rpt_MH_PHT08_GiaoNhanThePin cry = new Rpt_MH_PHT08_GiaoNhanThePin(); // cry.SetDataSource(dtReport); // string title = ""; cry.SetParameterValue("Branch", Branch_name); cry.SetParameterValue("Date_Send", Date_Send); cry.SetParameterValue("SoLuongThe_Nbr", SoLuongThe_Nbr); cry.SetParameterValue("SoLuongThe_Chu", SoLuongThe_Chu + " Thẻ"); cry.SetParameterValue("SoLuongPin_Nbr", SoLuongThe_Nbr); cry.SetParameterValue("SoLuongPin_Chu", SoLuongThe_Chu + " PIN"); cry.SetParameterValue("From_To_Date", From_To_Date); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; //CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.ExportFormatType = ExportFormatType.WordForWindows; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export(); //--Fix for Load Report failse if (cry != null) { cry.Close(); cry.Dispose(); } } } else { // MessageBox.Show("");//+ card_number.Trim()); } } catch (Exception ex) { MessageBox.Show(ex.Message); this.Cursor = Cursors.Default; } #endregion Export } }//End for branch this.Cursor = Cursors.Default; tbStatus.Text += "-- Success:" + dtReport.Rows.Count; } else //MB-PHT06 { this.Cursor = Cursors.WaitCursor; string branch_code = cb_branch.SelectedValue.ToString().Trim(); try { process_MB_PHT06(cb_branch.SelectedValue.ToString().Trim()); tbStatus.Text = "DONE!!!"; } catch (Exception ex) { MessageBox.Show("Err btn_Process_Click: " + ex.ToString()); } this.Cursor = Cursors.Default; } } catch { } }
private void button1_Click(object sender, EventArgs e) { string sql = "", err = ""; string title = ""; string type = ""; if (cbOption.SelectedIndex + 1 == 1) { type = "MB-TNT01"; } if (cbOption.SelectedIndex + 1 == 2) { type = "MB-TNT02"; } if (cbOption.SelectedIndex + 1 == 3) { type = "MB-TNT03"; } if (cbOption.SelectedIndex + 1 == 4) { type = "MB-TLT01"; } this.Cursor = Cursors.WaitCursor; string folderOut = tbFilePath.Text.Trim(); //Tao thu muc if (!Directory.Exists(folderOut)) { Directory.CreateDirectory(folderOut); } if (type != "") { sql = ""; err = ""; sql = " select *"; sql += " from nab_credit_xln_sendmail g"; sql += " where trim(g.type_report)='" + type + "'"; sql += " and trunc(g.date_create)=trunc(sysdate)"; DataTable dtCheck = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); return; } else { //if (dtCheck.Rows.Count > 0) //{ // MessageBox.Show("Đã xử lý Report này cho hôm nay."); // return; //} } for (int k = 0; k < dtBranch.Rows.Count; k++)//Vòng lặp để xuất ra từng Branch { string branch = dtBranch.Rows[k]["branch_code"].ToString().Trim(); tbStatus.Text = "Da xu ly : "+ (k +1).ToString() +" --" +"Dang xu ly :"+branch; if (type.ToUpper() == "MB-TNT01") { sql = "select cl.short_name embossed_name, "; if (Frm_main.admin_flag) { sql += " ctr_a.contract_number cr_account_nbr, "; } else { sql += " substr(ctr_a.contract_number,1,4)||'xxxxxxxx'||substr(ctr_a.contract_number,-4) cr_account_nbr, "; } sql += " cl.reg_number, "; sql += " cl.client_number, "; sql += " abs(te.CLOSE_CASH + te.CLOSE_SALE) closing_bal, "; sql += " (select abs(blg.total_balance) "; sql += " from " + Businessbp.executedb.owner + "billing_log blg "; sql += " where blg.acnt_contract__oid = bl.acnt_contract__oid "; sql += " and blg.start_billing_date = "; sql += " (select max(blg1.start_billing_date) "; sql += " from " + Businessbp.executedb.owner + "billing_log blg1 "; sql += " where blg1.acnt_contract__oid = ctr_a.id)) opening_bal, "; sql += " (nab_pck_out.nab_fnc_Get_payment(ctr_a.contract_number, "; sql += " (case "; sql += " when extract(day from sysdate) >= 24 then "; sql += " to_date('24/' || to_char(sysdate, 'mm/yyyy'), 'dd/mm/yyyy') "; sql += " else "; sql += " to_date('24/' || to_char(add_months(sysdate, -1), 'mm/yyyy'), "; sql += " 'dd/mm/yyyy') "; sql += " end), "; sql += " trunc(sysdate)) + "; sql += " (select nvl(sum(r.transaction_amount), 0) "; sql += " from nab_balance_credit r "; sql += " where (select ctr.contract_number "; sql += " from " + Businessbp.executedb.owner + "Acnt_Contract ctr "; sql += " where ctr.amnd_state = 'A' "; sql += " and ctr.id = "; sql += " (select ctr1.acnt_contract__oid "; sql += " from " + Businessbp.executedb.owner + "acnt_contract ctr1 "; sql += " where 1 = 1 "; sql += " and ctr1.amnd_state = 'A' "; sql += " and ctr1.contract_number = r.card_number)) = "; sql += " ctr_a.contract_number "; sql += " and nvl(r.extract_flag, 'N') = 'N' "; sql += " and substr(r.message_receive, 1, 7) = 'success' "; sql += " and nvl(r.authorize, 'N') = 'Y')) cur_payment, "; sql += " abs(bl.total_due) + "; sql += " (select abs(nvl(sum(acc1.current_balance), 0)) "; sql += " from " + Businessbp.executedb.owner + "account acc1 "; sql += " where acc1.acnt_contract__oid = ctr_a.id "; sql += " and acc1.code in ('L2', 'L4')) min_due, "; sql += " bl.due_date, "; sql += " nvl(ctr_a.add_info_03, 0) unpaid_period, "; sql += " nab_pck_out.nab_fnc_getThucNoDays(ctr_a.contract_number) unpaid_date, "; sql += " (select addr.phone_m "; sql += " from " + Businessbp.executedb.owner + "contract_addresses addr "; sql += " where 1 = 1 "; sql += " and addr.acnt_contract__oid = ctr_a.id "; sql += " and addr.address_type__id = "; sql += " (select at.id "; sql += " from " + Businessbp.executedb.owner + "address_type at "; sql += " where 1 = 1 "; sql += " and at.amnd_state = 'A' "; sql += " and at.code = 'PHS_ADDR')) phone_mobile, "; sql += " cl.e_mail, "; sql += " decode(nvl(ctr_a.add_info_01, '0'), "; sql += " 'M', "; sql += " 'MAD', "; sql += " '100', "; sql += " 'TAD', "; sql += " '0', "; sql += " 'not registed', "; sql += " (to_number(ctr_a.add_info_01) || '%')) auto_payment, "; sql += " ctr_a.rbs_number, "; sql += " (select nvl(acy_avl_bal, 0) "; sql += " from sttm_cust_account@Flexcube "; sql += " where cust_ac_no = ctr_a.rbs_number "; sql += " and record_stat = 'O' "; sql += " and auth_stat = 'A') So_Tien_TKTT "; sql += " from " + Businessbp.executedb.owner + "acnt_contract ctr_a, "; sql += " " + Businessbp.executedb.owner + "acnt_contract ctr_c, "; sql += " " + Businessbp.executedb.owner + "appl_product prd, "; sql += " nab_cp_card cc, "; sql += " nab_vw_total_endcycle te, "; sql += " " + Businessbp.executedb.owner + "billing_log bl, "; sql += " " + Businessbp.executedb.owner + "client cl "; sql += " where 1 = 1 "; sql += " and cl.id = ctr_c.client__id "; sql += " and ctr_c.acnt_contract__oid = ctr_a.id "; sql += " and ctr_c.product = prd.internal_code "; sql += " and ctr_a.contract_number = te.contract_number "; sql += " and ctr_c.add_info_01 = cc.source_application_no "; sql += " and bl.acnt_contract__oid = ctr_a.id "; sql += " and te.DATE_TO = bl.finish_billing_date "; sql += " and ctr_a.amnd_state = 'A' "; sql += " and ctr_c.amnd_state = 'A' "; sql += " and prd.amnd_state = 'A' "; sql += " and substr(prd.code, 5, 1) = 'M' "; sql += " and te.DATE_TO = (case "; sql += " when extract(day from sysdate) >= 24 then "; sql += " to_date('24/' || to_char(sysdate, 'mm/yyyy'), 'dd/mm/yyyy') "; sql += " else "; sql += " to_date('24/' || to_char(add_months(sysdate, -1), 'mm/yyyy'), "; sql += " 'dd/mm/yyyy') "; sql += " end) "; sql += " "; sql += " and ctr_c.branch = '" + branch + "' "; title = "DANH SÁCH KHÁCH HÀNG CẦN NHẮC NỢ"; } #region chualam else if (type.ToUpper() == "MB-TNT02") { sql = "select cl.short_name embossed_name, "; sql += " cl.client_number client_number, "; if (Frm_main.admin_flag) { sql += " ca.contract_number cr_account_nbr, "; } else { sql += " substr(ca.contract_number,1,4)||'xxxxxxxx'||substr(ca.contract_number,-4) cr_account_nbr, "; } sql += " -ca.total_balance closing_bal, "; sql += " -l.total_balance opening_bal, "; sql += " -l.total_due min_due, "; sql += " to_char(trunc(l.due_date-1),'dd/mm/yyyy') payment_date, "; sql += " to_char(nab_pck_out.GET_UNPAID_STATUS(ca.contract_number),'999') acc_unpaid_status, "; //sql += " to_char(nab_pck_out.nab_fnc_getThucNoDays(ca.contract_number),'999') number_days, "; sql += " cl.phone_m phone_mobile, "; sql += " cl.e_mail, "; sql += " ca.add_info_01 auto_payment, "; sql += " ca.rbs_number rbs_number, "; sql += " (select ltrim(to_char(nvl(a.acy_avl_bal, 0), '999,999,999,999')) as acy_avl_bal "; sql += " from sttm_cust_account@FLEXCUBE a "; sql += " WHERE a.cust_ac_no = trim(ca.rbs_number)) So_Tien_TKTT, "; sql += " (select br.name"; sql += " from ows.branch br"; sql += " where br.amnd_state = 'A'"; sql += " and br.code = ca.branch) branch_code"; sql += " from " + Businessbp.executedb.owner + "acnt_contract ca, "; sql += " " + Businessbp.executedb.owner + "client cl, "; sql += " " + Businessbp.executedb.owner + "billing_log l "; sql += " where ca.amnd_state = 'A' and ca.con_cat = 'A' "; sql += " and cl.amnd_state = 'A' "; sql += " and ca.client__id = cl.id "; sql += " and ca.id = l.acnt_contract__oid "; sql += " and l.finish_billing_date = (select max(l1.finish_billing_date) "; sql += " from " + Businessbp.executedb.owner + "billing_log l1 "; sql += " where l1.acnt_contract__oid = ca.id) "; sql += " and nab_pck_out.GET_UNPAID_STATUS(ca.contract_number) = '4' "; sql += " and not exists (select 1 from " + Businessbp.executedb.owner + "V_CS_ALL_ACNT_STATUS vcs "; sql += " where vcs.acnt_contract__oid = ca.id "; sql += " and vcs.status_type_code = 'DEBT_LEVEL' "; sql += " and substr(vcs.status_value_code,1,1) = 'O') "; sql += " and ca.branch = '" + branch + "' "; title = "DANH SÁCH KHÁCH HÀNG CẦN CHUYỂN NỢ QUÁ HẠN"; } else if (type.ToUpper() == "MB-TNT03") { sql = ""; sql += " with payment as"; sql += " ("; sql += " select nvl(sum(th.local_amount),0) payment, acnt.id"; sql += " from v_transaction_hist th, " + Businessbp.executedb.owner + "acnt_contract acnt "; sql += " where th.issuing_id = acnt.id"; sql += " and acnt.amnd_state = 'A'"; sql += " and acnt.con_cat = 'A'"; sql += " and th.POSTING_DATE >= acnt.last_billing_date"; sql += " and th.trans_type in ('36628','36630','36643','36646','36461','37250','146','700','15','36522','36682','36994')"; sql += " group by acnt.id"; sql += " ), "; sql += " payment_total as"; sql += " ( "; sql += " select p.payment, "; sql += " ac.id,"; sql += " -bl.total_balance total_balance,"; sql += " -bl.total_due total_due,"; sql += " bl.due_date"; sql += " from " + Businessbp.executedb.owner + "billing_log bl,"; sql += " " + Businessbp.executedb.owner + "acnt_contract ac,"; sql += " (select *"; sql += " from payment p "; sql += " union "; sql += " select 0 payment , id"; sql += " from " + Businessbp.executedb.owner + "acnt_contract ac"; sql += " where ac.amnd_state = 'A'"; sql += " and ac.con_cat = 'A'"; sql += " and ac.liab_category is not null"; sql += " and substr(ac.contract_number,1,1) in ('3','6')"; sql += " and not exists (select 1 from payment p1 where p1.id = ac.id)) p"; sql += " where bl.finish_billing_date = (select max(l.finish_billing_date) from ows.billing_log l where l.acnt_contract__oid = bl.acnt_contract__oid)"; sql += " and ac.con_cat = 'A'"; sql += " and ac.amnd_state = 'A'"; sql += " and ac.id = bl.acnt_contract__oid"; sql += " and bl.total_due < 0"; sql += " and p.id = ac.id "; sql += " ),"; sql += " email_phone as"; sql += " (select cp.mobile_number,"; sql += " cp.email_id,"; sql += " c.acnt_contract__oid,"; sql += " cp.machinhsachtd"; sql += " from " + Businessbp.executedb.owner + "acnt_contract c, nab_cp_card cp, " + Businessbp.executedb.owner + "appl_product pr"; sql += " where c.amnd_state = 'A'"; sql += " and c.con_cat = 'C'"; sql += " and c.add_info_01 = cp.source_application_no"; sql += " and c.product = pr.internal_code"; sql += " and pr.amnd_state = 'A'"; sql += " and substr(pr.code, 2, 1) = 'C'"; sql += " and substr(pr.code, 5, 1) = 'M'"; sql += " and c.contr_status in ('14', '423', '267', '174', '109')"; sql += " "; sql += " )"; sql += " select (select br.name"; sql += " from ows.branch br"; sql += " where br.amnd_state = 'A'"; sql += " and br.code = ctr_a.branch) branch_code,"; sql += " cl.short_name embossed_name,"; sql += " cl.client_number client_number,"; sql += " cl.reg_number legal_id,"; sql += " ctr_a.contract_number cr_account_nbr,"; sql += " -ctr_a.total_balance closing_bal,"; sql += " (select sum(acc.current_balance)"; sql += " from ows.account acc"; sql += " where acc.acnt_contract__oid = ctr_a.id"; sql += " and acc.account_type in ('929', '933', '1098', '1099')) lai_ngoai_bang,"; sql += " to_char((select min(ua.start_date)"; sql += " from ows.usage_action ua"; sql += " where ua.posting_status = 'C'"; sql += " and (select substr(et.name, 1, 4)"; sql += " from ows.event_type et"; sql += " where et.id = ua.event_type) = 'Dept'"; sql += " and ua.acnt_contract__id = ctr_a.id),"; sql += " 'dd/mm/yyyy') ngay_chuyen_lan_dau,"; sql += " bl.total_balance opening_bal,"; sql += " to_char(bl.total_due,'999,999,999,999') min_due,"; sql += " bl.payment,"; sql += " to_char(bl.due_date-1, 'dd/mm/yyyy') payment_date,"; sql += " ctr_a.add_info_03 acc_unpaid_status,"; //sql += " to_char(nab_pck_out.nab_fnc_getThucNoDays(ctr_a.contract_number),'999') number_days,"; sql += " (select c.mobile_number"; sql += " from email_phone c"; sql += " where c.acnt_contract__oid = ctr_a.id) phone_mobile,"; sql += " (select c.email_id"; sql += " from email_phone c"; sql += " where c.acnt_contract__oid = ctr_a.id) e_mail,"; sql += " decode(nvl(ctr_a.add_info_01, '0'),"; sql += " 'M',"; sql += " 'MAD',"; sql += " '100',"; sql += " 'TAD',"; sql += " '0',"; sql += " 'not registed',"; sql += " (to_number(ctr_a.add_info_01) || '%')) auto_payment,"; sql += " ctr_a.rbs_number,"; sql += " (select nvl(acy_avl_bal, 0)"; sql += " from sttm_cust_account@Flexcube"; sql += " where cust_ac_no = ctr_a.rbs_number"; sql += " and record_stat = 'O'"; sql += " and auth_stat = 'A') So_Tien_TKTT"; sql += " from " + Businessbp.executedb.owner + "acnt_contract ctr_a, payment_total bl, " + Businessbp.executedb.owner + "client cl"; sql += " where 1 = 1"; sql += " and cl.id = ctr_a.client__id"; sql += " and ctr_a.amnd_state = 'A'"; sql += " and ctr_a.con_cat = 'A'"; sql += " and cl.amnd_state = 'A'"; sql += " and ctr_a.liab_category is not null"; sql += " and ctr_a.add_info_03 = 2"; sql += " and bl.id = ctr_a.id"; sql += " and bl.total_due > bl.payment"; sql += " and ctr_a.contr_status = '51'"; sql += " and ctr_a.branch = '"+branch+"' "; title = "DANH SÁCH KHÁCH HÀNG QUÁ HẠN/KÉO THEO"; } else if (type.ToUpper() == "MB-TLT01") { if (Frm_main.admin_flag) { sql = " select br.name branch_code, a.contract_name hoten,cl.reg_number gttt,cl.client_number makh, a.contract_number sotaikhoan, st.name trangthai,to_char(h.amnd_date,'dd/mm/yyyy') ngaythanhly,to_char(-a.total_balance,'999,999,999,999,999') duno"; } else { sql = " select br.name branch_code, a.contract_name hoten,cl.reg_number gttt,cl.client_number makh, substr(a.contract_number,1,4)||'xxxxxxxx'||substr(a.contract_number,-4) sotaikhoan, st.name trangthai,to_char(a.amnd_date,'dd/mm/yyyy') ngaythanhly,to_char(-a.total_balance,'999,999,999,999,999') duno"; } sql += " from " + Businessbp.executedb.owner + "acnt_contract a, " + Businessbp.executedb.owner + "contr_status st," + Businessbp.executedb.owner + "client cl, " + Businessbp.executedb.owner + "branch br, status_history h" + " where a.amnd_state ='A' and cl.amnd_state = 'A'"+ " and a.con_cat = 'A' "+ " and st.id = a.contr_status"+ " and cl.id = a.client__id and br.code = a.branch" + " and h.contract_number = a.contract_number" + " and h.val like '%--> 13 (Account Closed)%'" + " and trunc(h.amnd_date)= to_date('" + dt_Date.Text + "', 'dd/mm/yyyy')"; sql += " and a.branch = '" + branch + "' "; title = "THÔNG BÁO XÁC NHẬN DƯ NỢ VÀ THANH LÝ TÀI KHOẢN THẺ TÍN DỤNG CỦA KHÁCH HÀNG"; } #endregion chualam if (sql != "") { DataTable dtReport = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); this.Cursor = Cursors.Default; return; } else { string file_path = ""; if (cbOption.SelectedIndex + 1 != 4) { file_path = tbFilePath.Text.Trim() + branch.Trim() + "_" + type + "_" + DateTime.Now.ToString("ddMMyyyy") + ".pdf"; } else { string s = Convert.ToDateTime(dt_Date.Value).ToString("ddMMyyyy"); file_path = tbFilePath.Text.Trim() + branch.Trim() + "_" + type + "_" + s + ".pdf"; } //Export pdf try { if (dtReport.Rows.Count > 0) { if (cbOption.SelectedIndex + 1 != 4) { rpt_Xu_Ly_No cry = new rpt_Xu_Ly_No(); cry.SetDataSource(dtReport); // string title = ""; cry.SetParameterValue("title", title); cry.SetParameterValue("MauBieu", "Mẫu: "); cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString()); cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString()); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; //CrExportOptions.ExportFormatType = ExportFormatType.WordForWindows; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export(); //--Fix for Load Report failse if (cry != null) { cry.Close(); cry.Dispose(); } //lấy dữ liệu insert table sql = ""; err = ""; sql = "insert into NAB_CREDIT_XLN_SENDMAIL"; sql += " (BRANCH_CODE ,FILE_PATH ,USER_CREATE ,DATE_CREATE,SEND_FLAG ,TYPE_REPORT)"; sql += "values ("; sql += "'" + branch + "',"; sql += "'" + file_path.Replace("\\", @"\") + "',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate,"; sql += "'0',"; sql += "'" + type + "'"; sql += ")"; Businessbp.executedb.ExecuteDML(sql, ref err); if (err != "") { MessageBox.Show(err); //return; // this.Cursor = Cursors.Default; } } else { rpt_Thanh_ly_tai_khoan cry1 = new rpt_Thanh_ly_tai_khoan(); cry1.SetDataSource(dtReport); // string title = ""; cry1.SetParameterValue("title", title); cry1.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString()); cry1.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString()); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry1.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; //CrExportOptions.ExportFormatType = ExportFormatType.WordForWindows; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry1.Export(); //--Fix for Load Report failse if (cry1 != null) { cry1.Close(); cry1.Dispose(); } //lấy dữ liệu insert table sql = ""; err = ""; sql = "insert into NAB_CREDIT_XLN_SENDMAIL"; sql += " (BRANCH_CODE ,FILE_PATH ,USER_CREATE ,DATE_CREATE,SEND_FLAG ,TYPE_REPORT)"; sql += "values ("; sql += "'" + branch + "',"; sql += "'" + file_path.Replace("\\", @"\") + "',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate,"; sql += "'0',"; sql += "'" + type + "'"; sql += ")"; Businessbp.executedb.ExecuteDML(sql, ref err); if (err != "") { MessageBox.Show(err); //return; // this.Cursor = Cursors.Default; } } } else { // MessageBox.Show("");//+ card_number.Trim()); } } catch (Exception ex) { MessageBox.Show(ex.Message); this.Cursor = Cursors.Default; } } }//End for branch this.Cursor = Cursors.Default; tbStatus.Text += "-- Success"; if (k+1 == dtBranch.Rows.Count) { MessageBox.Show("Đã process thành công!!!"); } } } }
public void VistaPrevia(string key) { try { ReportDocument objRpt = new ReportDocument(); DataSet ds = (DataSet)this.GetDataReport(key); //string reportPath = Application.StartupPath + "\\Reporte\\co_ordenCompra_rpt.rpt"; string reportPath = "C:\\Reportes\\CRTejTicket_rpt.rpt"; objRpt.Load(reportPath); DiskFileDestinationOptions crDiskFileDestinationOption = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions crFormatTypeOption = new PdfRtfWordFormatOptions(); ExportOptions crExportOptions = new ExportOptions(); objRpt.SetDataSource(ds.Tables[0]); string strfolder = "C:\\Reporte\\"; crDiskFileDestinationOption.DiskFileName = strfolder + "Etiqueta.pdf"; crExportOptions = objRpt.ExportOptions; crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; crExportOptions.ExportDestinationOptions = crDiskFileDestinationOption; crExportOptions.ExportFormatOptions = crFormatTypeOption; crystalReportViewer1.ReportSource = objRpt; //PageMargins margins; //margins = objRpt.PrintOptions.PageMargins; //margins.bottomMargin = 250; //margins.leftMargin = 250; //margins.rightMargin = 250; //margins.topMargin = 250; //objRpt.PrintOptions.ApplyPageMargins(margins); //objRpt.Export(); } catch (Exception ex) { ex.ToString(); } }
private void button1_Click(object sender, EventArgs e) { /* Điều kiện kiểm tra * B1: kiểm tra trong ổ D:\KTT\SEND_EMAIL\BTBGD\yyyymmdd đã xử lý với ngày hiện tại chưa * Nếu đã xử lý: Không cho xử lý nữa * B2: Kiểm tra trong bảng NAB_CREDIT_SENDMAIL đã xử lý với ngày hiện tại chưa? Nếu rồi--> Không xử lý * Xử lý tạo file pdf và lưu vào đường dẫn trên D:\KTT\SEND_EMAIL\BTBGD\yyyymmdd\số thẻ.pdf * */ //B1: Kiểm tra đường dẫn đã tồn tại chưa? this.Cursor = Cursors.WaitCursor; string folderOut = tbFilePath.Text.Trim(); if (Directory.Exists(folderOut)) { MessageBox.Show("Đã xử lý SendMail ngày " + DateTime.Now.ToString("yyyyMMdd")); this.Cursor = Cursors.Default; return; // Directory.CreateDirectory(folderOut); } string sql = "", err = ""; sql += " select * from " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL a"; sql += " where trunc(a.DATE_CREATE)=trunc(sysdate)"; // sql += " and trunc(sysdate)>=(select trunc(max(b.stat_date)) from cr_term b)"; DataTable dt = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); this.Cursor = Cursors.Default; return; } else { if (dt.Rows.Count > 0) { MessageBox.Show("Đã xử lý SendMail ngày " + DateTime.Now.ToString("yyyyMMdd")); this.Cursor = Cursors.Default; return; } else { //Xử lý và insert vào table //tạo thư mục Directory.CreateDirectory(folderOut); try { sql = ""; err = ""; sql = " select distinct a.cr_account_nbr "; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a"; sql += " where trunc(a.stat_date)=trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))"; sql += " order by a.cr_account_nbr asc"; DataTable dt_card_process = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); this.Cursor = Cursors.Default; return; } else { if (dt_card_process.Rows.Count <= 0) { MessageBox.Show("Chưa xử lý cuối ngày."); this.Cursor = Cursors.Default; return; } else { DataTable dt_bill = new DataTable(); for (int i = 0; i < dt_card_process.Rows.Count; i++) { //string card_number = dt_card_process.Rows[i]["card_number"].ToString(); string cr_account_nbr = dt_card_process.Rows[i]["cr_account_nbr"].ToString(); sql = ""; err = ""; #region old 2014-06-19 (2) /*sql = " select a.card_number, a.embossed_name,a.credit_limit"; sql += " ,to_char(a.processing_date,'dd/mm/yyyy') as processing_date"; sql += " ,to_char(a.payment_date,'dd/mm/yyyy') as payment_date"; sql += " ,a.opening_bal,a.total_debit,a.bal_payment,a.closing_bal,a.min_due"; sql += " ,to_char(a.transaction_date,'dd/mm/yyyy') as transaction_date"; sql += " ,to_char(a.value_date,'dd/mm/yyyy') as value_date"; sql += " ,a.arn,a.details,nvl(a.billing_amount,0) billing_amount"; sql += " ,(a.MAILING_ADDRESS1||' '||a.MAILING_ADDRESS2||' '||a.MAILING_ADDRESS3||' '||a.MAILING_ADDRESS4) address"; sql += " ,a.PHONE_HOME, a.PHONE_MOBILE, a.cr_account_nbr, a.transaction_amount "; sql += " ,(select b.currency_code_alpha from " + Businessbp.executedb.owner + "currency_table b where trim(b.currency_code) = trim(a.transaction_currency)) transaction_currency"; sql += " ,(select b.currency_code_alpha from " + Businessbp.executedb.owner + "currency_table b where trim(b.currency_code) = trim(a.billing_currency)) billing_currency"; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a"; sql += " where trunc(a.stat_date)=trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))"; //sql += " and a.card_number='" + card_number.Trim() + "'"; sql += " and a.cr_account_nbr='" + cr_account_nbr.Trim() + "'"; sql += " order by a.card_number asc,a.transaction_date asc";*/ #endregion old 2014-06-19 (2) //dt_bill = null; #region 2014-06-19 (2) sql += " select r.card_number, "; sql += " r.embossed_name, "; sql += " r.credit_limit, "; sql += " r.processing_date, "; sql += " r.payment_date, "; sql += " r.opening_bal, "; sql += " r.total_debit, "; sql += " r.bal_payment, "; sql += " r.closing_bal, "; sql += " r.min_due, "; sql += " r.transaction_date, "; sql += " r.value_date, "; sql += " r.arn, "; sql += " r.details, "; sql += " r.billing_amount_du_co, "; sql += " r.billing_amount_du_no, "; sql += " r.address, "; sql += " r.PHONE_HOME, "; sql += " r.PHONE_MOBILE, "; sql += " r.cr_account_nbr, "; sql += " r.transaction_amount, "; sql += " r.transaction_currency, "; sql += " r.billing_currency "; sql += " from (select a.card_number, "; sql += " a.embossed_name, "; sql += " a.credit_limit, "; sql += " to_char(a.processing_date, 'dd/mm/yyyy') as processing_date, "; sql += " to_char(a.payment_date, 'dd/mm/yyyy') as payment_date, "; sql += " a.opening_bal, "; sql += " a.total_debit, "; sql += " a.bal_payment, "; sql += " a.closing_bal, "; sql += " a.min_due, "; sql += " to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date, "; sql += " to_char(a.value_date, 'dd/mm/yyyy') as value_date, "; sql += " a.arn, "; sql += " a.details, "; sql += " decode(e.transaction_sign, "; sql += " '0', "; sql += " to_number(''), "; sql += " '1', "; sql += " a.billing_amount, "; sql += " a.billing_amount) billing_amount_du_co, "; sql += " decode(e.transaction_sign, "; sql += " '0', "; sql += " a.billing_amount, "; sql += " '1', "; sql += " to_number(''), "; sql += " a.billing_amount) billing_amount_du_no, "; sql += " (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' || "; sql += " a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address, "; sql += " a.PHONE_HOME, "; sql += " a.PHONE_MOBILE, "; sql += " a.cr_account_nbr, "; sql += " a.transaction_amount, "; sql += " (select b.currency_code_alpha "; sql += " from " + Businessbp.executedb.owner + "currency_table b "; sql += " where trim(b.currency_code) = trim(a.transaction_currency)) transaction_currency, "; sql += " (select b.currency_code_alpha "; sql += " from " + Businessbp.executedb.owner + "currency_table b "; sql += " where trim(b.currency_code) = trim(a.billing_currency)) billing_currency "; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a, " + Businessbp.executedb.owner + "trans_def_stand e "; sql += " where 1 = 1 "; sql += " and a.transaction_code = e.transaction_code "; sql += " and trunc(a.stat_date) = "; sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) "; sql += " and a.cr_account_nbr = '" + cr_account_nbr.Trim() + "' "; sql += " UNION ALL "; sql += " select a.card_number, "; sql += " a.embossed_name, "; sql += " a.credit_limit, "; sql += " to_char(a.processing_date, 'dd/mm/yyyy') as processing_date, "; sql += " to_char(a.payment_date, 'dd/mm/yyyy') as payment_date, "; sql += " a.opening_bal, "; sql += " a.total_debit, "; sql += " a.bal_payment, "; sql += " a.closing_bal, "; sql += " a.min_due, "; sql += " to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date, "; sql += " to_char(a.value_date, 'dd/mm/yyyy') as value_date, "; sql += " a.arn, "; sql += " a.details, "; sql += " a.billing_amount billing_amount_du_co, "; sql += " a.billing_amount billing_amount_du_no, "; sql += " (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' || "; sql += " a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address, "; sql += " a.PHONE_HOME, "; sql += " a.PHONE_MOBILE, "; sql += " a.cr_account_nbr, "; sql += " a.transaction_amount transaction_amount, "; sql += " (select b.currency_code_alpha "; sql += " from " + Businessbp.executedb.owner + "currency_table b "; sql += " where trim(b.currency_code) = trim(a.transaction_currency)) transaction_currency, "; sql += " (select b.currency_code_alpha "; sql += " from " + Businessbp.executedb.owner + "currency_table b "; sql += " where trim(b.currency_code) = trim(a.billing_currency)) billing_currency "; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a "; sql += " where trunc(a.stat_date) = "; sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) "; sql += " and trim(a.transaction_code) is null "; sql += " and a.cr_account_nbr = '" + cr_account_nbr.Trim() + "') r "; sql += " order by r.card_number asc, r.transaction_date asc "; #endregion 2014-06-19 (2) dt_bill.Clear(); dt_bill = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); this.Cursor = Cursors.Default; return; } else { //dt_bill.Columns.Add("card_number", typeof(string));//card_number //dt_bill.Columns.Add("embossed_name", typeof(string));//embossed_name //dt_bill.Columns.Add("credit_limit", typeof(string));//credit_limit //dt_bill.Columns.Add("processing_date", typeof(string));//processing_date //dt_bill.Columns.Add("payment_date", typeof(string));//payment_date //dt_bill.Columns.Add("opening_bal", typeof(string));//opening_bal //dt_bill.Columns.Add("total_debit", typeof(string));//total_debit //dt_bill.Columns.Add("bal_payment", typeof(string));//bal_payment //dt_bill.Columns.Add("closing_bal", typeof(string));//closing_bal //dt_bill.Columns.Add("min_due", typeof(string));//min_due //dt_bill.Columns.Add("transaction_date", typeof(string));//transaction_date //dt_bill.Columns.Add("value_date", typeof(string));//value_date //dt_bill.Columns.Add("arn", typeof(string)); //dt_bill.Columns.Add("details", typeof(string)); //dt_bill.Columns.Add("billing_amount", typeof(string)); ////get data //string card_number = dt_card_process.Rows[i]["card_number"].ToString(); //string embossed_name = dt_card_process.Rows[i]["embossed_name"].ToString(); //string credit_limit = dt_card_process.Rows[i]["credit_limit"].ToString(); //string processing_date = dt_card_process.Rows[i]["processing_date"].ToString(); //string payment_date = dt_card_process.Rows[i]["payment_date"].ToString(); //string opening_bal = dt_card_process.Rows[i]["opening_bal"].ToString(); //string total_debit = dt_card_process.Rows[i]["total_debit"].ToString(); //string bal_payment = dt_card_process.Rows[i]["bal_payment"].ToString(); //string closing_bal = dt_card_process.Rows[i]["closing_bal"].ToString(); //string min_due = dt_card_process.Rows[i]["min_due"].ToString(); //string transaction_date = dt_card_process.Rows[i]["transaction_date"].ToString(); //string value_date = dt_card_process.Rows[i]["value_date"].ToString(); //string arn = dt_card_process.Rows[i]["arn"].ToString(); //string details = dt_card_process.Rows[i]["details"].ToString(); //string billing_amount = dt_card_process.Rows[i]["billing_amount"].ToString(); //try //{ // dt_bill.Rows.Add(card_number, embossed_name, credit_limit, processing_date, payment_date, opening_bal, total_debit, bal_payment // , closing_bal, min_due, transaction_date, value_date, arn, details, billing_amount); //} //catch (Exception ex) //{ // MessageBox.Show(ex.ToString()); //} //string file_path = tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf"; string file_path = tbFilePath.Text.Trim() + "\\" + cr_account_nbr.Trim() + ".pdf"; //Export pdf try { if (dt_bill.Rows.Count > 0) { Rpt_Credit_Billing_new cry = new Rpt_Credit_Billing_new(); cry.SetDataSource(dt_bill); string title = ""; string title2 = ""; string title3 = ""; title = "BẢNG THÔNG BÁO GIAO DỊCH"; title2 = "(Từ ngày " + dateTimePicker1.Value.AddMonths(-1).ToString("dd/MM/yyyy").Trim() + " đến ngày " + dateTimePicker1.Text.Trim() + " )"; cry.SetParameterValue("title", title); cry.SetParameterValue("title2", title2); title3 = Get_title3(cr_account_nbr); if (title3.Trim() != "") cry.SetParameterValue("title3", title3); else { MessageBox.Show("Err when get title3"); return; } // a.title3 = "Yêu cầu thanh toán trước 10h ngày:"; ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export(); //--Fix for Load Report failse if (cry != null) { cry.Close(); cry.Dispose(); } //lấy dữ liệu insert table sql = ""; err = ""; //sql += " select distinct to_char(a.stat_date,'dd/mm/yyyy') DATE_START,c.email_id EMAIL,to_char(a.stat_date,'dd/mm/yyyy') BILL_DATE"; //sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a," + Businessbp.executedb.owner + "application b, " + Businessbp.executedb.owner + "cp_card c"; //sql += " where a.card_number=b.card_number"; //sql += " and b.application_no=c.source_application_no"; //sql += " and trunc(a.stat_date) =trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))"; //sql += " and a.card_number='" + card_number.Trim() + "'"; //sql += " and c.email_id is not null"; //sql += " select distinct to_char(a.stat_date,'dd/mm/yyyy') DATE_START,e.email_id EMAIL,to_char(a.stat_date,'dd/mm/yyyy') BILL_DATE,a.card_number"; sql += " select distinct to_char(a.stat_date,'dd/mm/yyyy') DATE_START,e.email_id EMAIL,to_char(a.stat_date,'dd/mm/yyyy') BILL_DATE,a.cr_account_nbr"; sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a," + Businessbp.executedb.owner + "card e"; sql += " where a.card_number=e.card_number"; sql += " and trunc(a.stat_date) =trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))"; //sql += " and e.email_id is not null"; //OLD 2014-05-08 (1) sql += " and trim(e.email_id) is not null"; // 2014-05-08 (1) //sql += " and a.card_number='" + card_number.Trim() + "'"; sql += " and a.cr_account_nbr='" + cr_account_nbr.Trim() + "'"; DataTable dtInsert = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); // return; } else { if (dtInsert.Rows.Count > 0) { string DATE_START = dtInsert.Rows[0]["DATE_START"].ToString(); string EMAIL = dtInsert.Rows[0]["EMAIL"].ToString(); string BILL_DATE = dtInsert.Rows[0]["BILL_DATE"].ToString(); // sql = ""; err = ""; if (EMAIL.Trim() == "") { } else { sql = "insert into " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)"; sql += "values ("; sql += "" + (DATE_START.Trim() == "" ? "null" : ("to_date('" + DATE_START + "','dd/MM/yyyy')")) + ","; //sql += "'" + card_number + "',"; sql += "'" + cr_account_nbr + "',"; sql += "'" + file_path + "',"; sql += "" + (EMAIL.Trim() == "" ? "null" : ("'" + EMAIL + "'")) + ","; sql += "" + (BILL_DATE == "" ? "null" : ("to_date('" + BILL_DATE + "','dd/mm/yyyy')")) + ","; sql += "'0',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate"; sql += ")"; Businessbp.executedb.ExecuteDML(sql, ref err); if (err != "") { MessageBox.Show(err); //return; } } } else//Nếu không có dữ liệu { } } } else { //MessageBox.Show("" + card_number.Trim()); MessageBox.Show("" + cr_account_nbr.Trim()); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } } tbStatus.Text = "Successful!!!"; this.Cursor = Cursors.Default; } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } }
private void btProcess_Click ( object sender, EventArgs e ) { { /* Điều kiện kiểm tra * B1: kiểm tra trong ổ D:\KTT\SEND_EMAIL\BTBGD\yyyymmdd đã xử lý với ngày hiện tại chưa * Nếu đã xử lý: Không cho xử lý nữa * B2: Kiểm tra trong bảng NAB_CREDIT_SENDMAIL đã xử lý với ngày hiện tại chưa? Nếu rồi--> Không xử lý * Xử lý tạo file pdf và lưu vào đường dẫn trên D:\KTT\SEND_EMAIL\BTBGD\yyyymmdd\số thẻ.pdf * */ //B1: Kiểm tra đường dẫn đã tồn tại chưa? this.Cursor = Cursors.WaitCursor; string folderOut = tbFilePath.Text.Trim ( ); if (Directory.Exists ( folderOut )) { MessageBox.Show ( "Đã xử lý SendMail ngày " + DateTime.Now.ToString ( "yyyyMMdd" ) ); this.Cursor = Cursors.Default; return; // Directory.CreateDirectory(folderOut); } string sql = "", err = ""; sql += " select * from NAB_CREDIT_SENDMAIL a"; sql += " where trunc(a.DATE_CREATE)=trunc(sysdate)"; // sql += " and trunc(sysdate)>=(select trunc(max(b.stat_date)) from cr_term b)"; DataTable dt = Businessbp.executedb.getTable ( sql, ref err ); if (err != "") { MessageBox.Show ( err ); this.Cursor = Cursors.Default; return; } else { if (dt.Rows.Count > 0) { MessageBox.Show ( "Đã xử lý SendMail ngày " + DateTime.Now.ToString ( "yyyyMMdd" ) ); this.Cursor = Cursors.Default; return; } else { //Xử lý và insert vào table //tạo thư mục Directory.CreateDirectory ( folderOut ); try { sql = ""; err = ""; #region 2014-10-28 (6) new sql += " select distinct a.cr_account_nbr "; sql += " ,acc.id "; sql += " ,to_char(a.stat_date, 'dd/mm/yyyy') DATE_START"; sql += " ,to_char(a.stat_date, 'dd/mm/yyyy') BILL_DATE"; sql += " from nab_billing_credit_log a "; sql += " ," + Businessbp.executedb.owner + "acnt_contract acc "; sql += " where a.cr_account_nbr = acc.contract_number "; sql += " and a.stat_date = to_date('" + dtpckDate.Text.Trim ( ) + "', 'dd/mm/yyyy') "; sql += " and acc.amnd_state = 'A' "; sql += " and acc.con_cat = 'A' "; sql += " and (a.total_debit + a.fee + a.bal_payment + abs(a.closing_bal)) <> 0"; sql += " "; #endregion 2014-10-28 (6) new DataTable dt_card_process = Businessbp.executedb.getTable ( sql, ref err ); if (err != "") { MessageBox.Show ( err ); this.Cursor = Cursors.Default; return; } else { if (dt_card_process.Rows.Count <= 0) { MessageBox.Show ( "Chưa xử lý cuối ngày." ); this.Cursor = Cursors.Default; return; } else { DataTable dt_bill = new DataTable ( ); for (int i = 0; i < dt_card_process.Rows.Count; i++) { sql = ""; err = ""; sql += " select 1 "; sql += " from " + Businessbp.executedb.owner + "contract_addresses ca "; sql += " where ca.acnt_contract__oid = '" + dt_card_process.Rows[i]["id"].ToString ( ) + "' "; sql += " and ca.address_type__id = '1' "; sql += " and instr(ca.e_mail,'@',1) > 0 "; DataTable dt_email = Businessbp.executedb.getTable ( sql, ref err ); if (err != "") { MessageBox.Show ( err ); this.Cursor = Cursors.Default; return; } else { pbProcess.Minimum = 0; pbProcess.Maximum = dt_card_process.Rows.Count; pbProcess.Value = 1; pbProcess.Step = 1; if (dt_email.Rows.Count > 0) { int min = (int)( (double)pbProcess.Value ); int max = (int)( (double)pbProcess.Maximum ); pbProcess.CreateGraphics ( ).DrawString ( min.ToString ( ) + "/" + max.ToString ( ), new Font ( "Arial", (float)10, FontStyle.Regular ), Brushes.Black, new PointF ( pbProcess.Width / 2 - 10, pbProcess.Height / 2 - 7 ) ); pbProcess.PerformStep ( ); string cr_account_nbr = dt_card_process.Rows[i]["cr_account_nbr"].ToString ( ); string id = dt_card_process.Rows[i]["id"].ToString ( ); sql = ""; err = ""; if (DateTime.ParseExact ( dtpckDate.Text.Trim ( ), "dd/MM/yyyy", CultureInfo.InvariantCulture ) < DateTime.ParseExact ( "24/09/2015", "dd/MM/yyyy", CultureInfo.InvariantCulture )) // 2015-09-08 (12) { #region 08-09-2015 //sql += "select distinct A.*, "; //sql += " case "; //sql += " when (A.date_create 90 < add_months(to_date(A.processing_date, "; //sql += " 'dd/mm/yyyy'), "; //sql += " -1)) then "; //sql += " '0' "; //sql += " else "; //sql += " to_char(decode(decode(A.machinhsachtd, "; //sql += " 'B01 - STK - UU DAI', "; //sql += " '1', "; //sql += " '0'), "; //sql += " '1', "; //sql += " nab_pck_out.nab_func_getcashback_amt(A.cr_account_nbr, "; //sql += " to_char(add_months(to_date(A.processing_date, "; //sql += " 'dd/mm/yyyy'), "; //sql += " -1) 1, "; //sql += " 'dd/mm/yyyy'), "; //sql += " to_char(least(A.date_create 90, "; //sql += " to_date(A.processing_date, "; //sql += " 'dd/mm/yyyy')), "; //sql += " 'dd/mm/yyyy')), "; //sql += " 0), "; //sql += " '999,999,999,999') "; //sql += " end cashback "; //sql += " from (select substr(r.card_number, 1, 4) || 'xxxxxxxx' || "; //sql += " substr(r.card_number, 13, 4) card_number, "; //sql += " r.embossed_name, "; //sql += " r.credit_limit, "; //sql += " r.processing_date, "; //sql += " r.payment_date, "; //sql += " r.opening_bal, "; //sql += " r.total_debit, "; //sql += " r.bal_payment, "; //sql += " r.closing_bal, "; //sql += " r.min_due, "; //sql += " r.transaction_date, "; //sql += " r.value_date, "; //sql += " r.arn, "; //sql += " r.details, "; //sql += " r.billing_amount_du_co, "; //sql += " r.billing_amount_du_no, "; //sql += " r.address, "; //sql += " r.PHONE_HOME, "; //sql += " r.PHONE_MOBILE, "; //sql += " r.cr_account_nbr cr_account_nbr, "; //sql += " r.transaction_amount, "; //sql += " r.transaction_currency, "; //sql += " r.billing_currency, "; //sql += " r.machinhsachtd, "; //sql += " (select date_open "; //sql += " from " + Businessbp.executedb.owner + "acnt_contract "; //sql += " where contract_number = r.cr_account_nbr) date_create "; //sql += " from (select substr(a.card_number, 1, 4) || 'xxxxxxxx' || "; //sql += " substr(a.card_number, 13, 4) card_number, "; //sql += " a.embossed_name, "; //sql += " a.credit_limit, "; //sql += " to_char(a.stat_date, 'dd/mm/yyyy') as processing_date, "; //sql += " to_char(a.payment_date, 'dd/mm/yyyy') as payment_date, "; //sql += " a.opening_bal, "; //sql += " a.total_debit, "; //sql += " a.bal_payment, "; //sql += " a.closing_bal, "; //sql += " a.min_due, "; //sql += " to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date, "; //sql += " to_char(a.value_date, 'dd/mm/yyyy') as value_date, "; //sql += " a.arn, "; //sql += " a.details, "; //sql += " decode(a.transaction_sign, "; //sql += " '0', "; //sql += " to_number(''), "; //sql += " '1', "; //sql += " a.billing_amount, "; //sql += " a.billing_amount) billing_amount_du_co, "; //sql += " decode(a.transaction_sign, "; //sql += " '0', "; //sql += " a.billing_amount, "; //sql += " '1', "; //sql += " to_number(''), "; //sql += " a.billing_amount) billing_amount_du_no, "; //sql += " (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' || "; //sql += " a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address, "; //sql += " a.PHONE_HOME, "; //sql += " a.PHONE_MOBILE, "; //sql += " a.cr_account_nbr cr_account_nbr, "; //sql += " a.transaction_amount, "; //sql += " (select b.name "; //sql += " from " + Businessbp.executedb.owner + "currency b "; //sql += " where trim(b.code) = trim(a.transaction_currency)"; //sql += " and b.amnd_state = 'A') transaction_currency, "; //sql += " (select b.name "; //sql += " from " + Businessbp.executedb.owner + "currency b "; //sql += " where trim(b.code) = trim(a.billing_currency)"; //sql += " and b.amnd_state = 'A') billing_currency, "; //sql += " c.machinhsachtd "; //sql += " from nab_billing_credit_log a, "; //sql += " " + Businessbp.executedb.owner + "acnt_contract crd, "; //sql += " nab_cp_card c "; //sql += " where trunc(a.stat_date) = "; //sql += " trunc(to_date('" + dtpckDate.Text.Trim ( ) + "', "; //sql += " 'dd/mm/yyyy')) "; //sql += " and a.card_number = crd.contract_number "; //sql += " and crd.add_info_01 = c.source_application_no "; //sql += " and crd.amnd_state = 'A' "; //sql += " and crd.con_cat = 'C' "; //sql += " UNION ALL"; //sql += " select a.card_number,"; //sql += " c.client_code,"; //sql += " a.embossed_name,"; //sql += " a.credit_limit,"; //sql += " to_char(a.stat_date, 'dd/mm/yyyy') as processing_date,"; //sql += " to_char(a.payment_date, 'dd/mm/yyyy') as payment_date,"; //sql += " a.opening_bal,"; //sql += " a.total_debit,"; //sql += " a.bal_payment,"; //sql += " a.closing_bal,"; //sql += " a.min_due,"; //sql += " to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date,"; //sql += " to_char(a.value_date, 'dd/mm/yyyy') as value_date,"; //sql += " a.arn,"; //sql += " a.details,"; //sql += " a.billing_amount billing_amount_du_co,"; //sql += " a.billing_amount billing_amount_du_no,"; //sql += " (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' ||"; //sql += " a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address,"; //sql += " a.PHONE_HOME,"; //sql += " a.PHONE_MOBILE,"; //sql += " a.cr_account_nbr,"; //sql += " a.transaction_amount transaction_amount,"; //sql += " (select b.currency_code_alpha"; //sql += " from " + Businessbp.executedb.owner + "currency_table b"; //sql += " where trim(b.currency_code) ="; //sql += " trim(a.transaction_currency)) transaction_currency,"; //sql += " (select b.currency_code_alpha"; //sql += " from " + Businessbp.executedb.owner + "currency_table b"; //sql += " where trim(b.currency_code) ="; //sql += " trim(a.billing_currency)) billing_currency,"; //sql += " c.machinhsachtd"; //sql += " from nab_billing_credit_log a,"; //sql += " " + Businessbp.executedb.owner + "application app,"; //sql += " " + Businessbp.executedb.owner + "cp_card c"; //sql += " where 1 = 1"; //sql += " and a.cr_account_nbr = app.card_number"; //sql += " and app.application_no = c.source_application_no"; //sql += " and trunc(a.stat_date) ="; //sql += " trunc(to_date('" + dtpckDate.Text.Trim ( ) + "', 'dd/mm/yyyy'))"; //sql += " and trim(a.transaction_code) is null"; //sql += " ) r"; //sql += " where 1 = 1"; //sql += " order by r.client_code,"; //sql += " r.card_number asc,"; //sql += " to_date(r.transaction_date, 'dd/mm/yyyy') desc) A"; //sql += " where 1 = 1"; //2015-05-27 (10) //sql += " order by A.client_code asc, A.cr_account_nbr asc, A.card_number asc, to_date(A.transaction_date, 'dd/mm/yyyy') desc "; //2015-05-27 (10) //2015-06-23 (11) #endregion 2015-05-20 (9) } else { #region 03-04-2016 sql += "select (case when l.card_number = l.cr_account_nbr then l.card_number else (substr(l.card_number, 1, 4) || ' ' || substr(l.card_number, 5, 2) || "; sql += " 'xx xxxx ' || substr(l.card_number, 13, 4)) end) card_number, "; sql += " l.cr_account_nbr cr_account_nbr, "; sql += " l.embossed_name, "; sql += " l.phone_mobile, "; sql += " nvl(l.credit_limit, 0) credit_limit_acc, "; sql += " nvl(l.credit_limit_client, 0) credit_limit_client, "; sql += " nvl(l.avail_amount_acc, 0) avail_amount_acc, "; sql += " nvl(l.avail_amount_client, 0) avail_amount_client, "; sql += " to_char(l.stat_date, 'dd/mm/yyyy') as stat_date, "; sql += " to_char(l.payment_date, 'dd/mm/yyyy') as payment_date, "; sql += " nvl(l.opening_bal, 0) opening_bal, "; sql += " nvl(l.total_debit, 0) total_debit, "; sql += " (nvl(l.fee, 0) + nvl(l.interest, 0)) fee_and_interest, "; sql += " nvl(l.bal_payment, 0) bal_payment, "; sql += " nvl(l.closing_bal, 0) closing_bal, "; sql += " (trim(l.MAILING_ADDRESS1) || ' ' || trim(l.MAILING_ADDRESS2) || ' ' || "; sql += " trim(l.MAILING_ADDRESS3) || ' ' || trim(l.MAILING_ADDRESS4)) address, "; sql += " to_char(l.transaction_date, 'dd/mm/yyyy') as transaction_date, "; sql += " to_char(l.value_date, 'dd/mm/yyyy') as value_date, "; sql += " l.arn, "; sql += " nvl(l.transaction_amount, 0) transaction_amount, "; sql += " (select b.name "; sql += " from " + Businessbp.executedb.owner + "currency b "; sql += " where trim(b.code) = trim(l.transaction_currency)"; sql += " and b.amnd_state = 'A') transaction_currency, "; sql += " nvl(l.billing_amount, 0) billing_amount, "; sql += " decode((l.transaction_sign), "; sql += " '0', "; sql += " '(DR)', "; sql += " '1', "; sql += " '(CR)', "; sql += " '') transaction_sign, "; sql += " l.details, "; sql += " (case "; sql += " when not exists (select 1 "; sql += " from nab_credit_unpaid un "; sql += " where 1 = 1 "; sql += " and un.cr_account_nbr = l.cr_account_nbr "; sql += " and substr(un.nqh_code, 1, 1) = 'O') then "; sql += " nvl(l.min_due, 0) "; sql += " else "; sql += " nvl(l.closing_bal, 0) "; sql += " end) min_due, "; sql += " nvl(l.min_due_install, 0) min_due_install, "; sql += " nvl(l.min_due_crterm, 0) min_due_crterm, "; sql += " nvl(l.cash_back_amount, 0) cash_back_amount, "; sql += " nvl(l.diamondbay_amount, 0) diamondbay_amount, "; sql += " nvl(l.diamondbay_rank, 0) diamondbay_rank, "; sql += " nvl(l.loyalty_score_earned, 0) loyalty_score_earned, "; sql += " nvl(l.loyalty_score_spent, 0) loyalty_score_spent, "; sql += " nvl(l.loyalty_cash_exchanged, 0) loyalty_cash_exchanged, "; sql += " nvl(l.loyalty_score_avail, 0) loyalty_score_avail, "; sql += " nvl(l.ovd_flag,'N') ovd_flag"; sql += " from nab_billing_credit_log l "; sql += " where 1 = 1 "; sql += " and l.stat_date = trunc(to_date('" + dtpckDate.Text.Trim ( ) + "', 'dd/mm/yyyy')) "; sql += " and (l.total_debit + l.fee + l.bal_payment + abs(l.closing_bal)) <> 0 "; sql += " and trim(l.cr_account_nbr) = '" + cr_account_nbr.Trim ( ) + "' "; sql += " order by l.cr_account_nbr asc, "; sql += " to_date(l.transaction_date, 'dd/mm/yyyy') desc, "; sql += " to_date(l.value_date, 'dd/mm/yyyy') desc "; #endregion 2015-09-08 (12) } dt_bill.Clear ( ); dt_bill = Businessbp.executedb.getTable ( sql, ref err ); if (err != "") { MessageBox.Show ( err ); this.Cursor = Cursors.Default; return; } else { //string file_path = tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf"; string file_path = tbFilePath.Text.Trim ( ) + "\\" + cr_account_nbr.Trim ( ) + ".pdf"; //Export pdf try { if (dt_bill.Rows.Count > 0) { if (DateTime.ParseExact ( dtpckDate.Text.Trim ( ), "dd/MM/yyyy", CultureInfo.InvariantCulture ) < DateTime.ParseExact ( "24/09/2015", "dd/MM/yyyy", CultureInfo.InvariantCulture )) // 2015-09-08 (12) { Rpt_Credit_Billing_new_2 cry = new Rpt_Credit_Billing_new_2 ( ); //2015-03-19 (8) new cry.SetDataSource ( dt_bill ); string title = ""; string title2 = ""; string title3 = ""; title = "BẢNG THÔNG BÁO GIAO DỊCH"; title2 = "(Từ ngày " + dtpckDate.Value.AddMonths ( -1 ).ToString ( "dd/MM/yyyy" ).Trim ( ) + " đến ngày " + dtpckDate.Text.Trim ( ) + " )"; cry.SetParameterValue ( "title", title ); cry.SetParameterValue ( "title2", title2 ); title3 = Get_title3 ( id ); if (title3.Trim ( ) != "") cry.SetParameterValue ( "title3", title3 ); else { MessageBox.Show ( "Err when get title3" ); return; } // a.title3 = "Yêu cầu thanh toán trước 10h ngày:"; ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions ( ); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions ( ); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export ( ); //--Fix for Load Report failse if (cry != null) { cry.Close ( ); cry.Dispose ( ); } } #region 2015-09-08 (12) else { Rpt_Credit_Billing_new_3 cry = new Rpt_Credit_Billing_new_3 ( ); cry.SetDataSource ( dt_bill ); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions ( ); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions ( ); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export ( ); //--Fix for Load Report failse if (cry != null) { cry.Close ( ); cry.Dispose ( ); } } #endregion 2015-09-08 (12) #region 2016-06-16 (16) sql = ""; err = ""; sql += " select ca.e_mail EMAIL "; sql += " from " + Businessbp.executedb.owner + "contract_addresses ca "; sql += " where ca.acnt_contract__oid = '" + dt_card_process.Rows[i]["id"].ToString ( ) + "' "; sql += " and ca.address_type__id = '1' "; sql += " union all "; sql += " select ca.email_id EMAIL "; sql += " from nab_corporate_card ca "; sql += " where ca.cr_account_number_corporate = '" + dt_card_process.Rows[i]["cr_account_nbr"].ToString ( ) + "' "; sql += " and ca.email_id is not null "; sql += " and nvl(ca.active_flag,'0') = '1'"; #endregion 2016-06-16 (16) DataTable dtInsert = Businessbp.executedb.getTable ( sql, ref err ); if (err != "") { MessageBox.Show ( err ); // return; } else { if (dtInsert.Rows.Count > 0) { string DATE_START = dt_card_process.Rows[0]["DATE_START"].ToString ( ); string EMAIL = dtInsert.Rows[0]["EMAIL"].ToString ( ).Trim ( ); string BILL_DATE = dt_card_process.Rows[0]["BILL_DATE"].ToString ( ); // sql = ""; err = ""; if (EMAIL.Trim ( ) == "") { } else { if (dtInsert.Rows.Count == 1) { sql = "insert into NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)"; sql += "values ("; sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ","; //sql += "'" + card_number + "',"; sql += "'" + cr_account_nbr + "',"; sql += "'" + file_path + "',"; sql += "" + ( EMAIL.Trim ( ) == "" ? "null" : ( "'" + EMAIL + "'" ) ) + ","; sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ","; sql += "'0',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate"; sql += ")"; Businessbp.executedb.ExecuteDML ( sql, ref err ); if (err != "") { MessageBox.Show ( err ); //return; } } else //2016-06-16 (16) { string EMAILCOR = dtInsert.Rows[1]["EMAIL"].ToString ( ).Trim ( ); if (EMAILCOR.Trim ( ) != "") { sql = " begin "; sql += " insert into NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)"; sql += " values ("; sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ","; //sql += "'" + card_number + "',"; sql += "'" + cr_account_nbr + "',"; sql += "'" + file_path + "',"; sql += "" + ( EMAILCOR.Trim ( ) == "" ? "null" : ( "'" + EMAILCOR.Trim ( ) + "'" ) ) + ","; sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ","; sql += "'0',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate"; sql += ");"; sql += " insert into NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)"; sql += " values ("; sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ","; //sql += "'" + card_number + "',"; sql += "'" + cr_account_nbr + "',"; sql += "'" + file_path + "',"; sql += "" + ( EMAIL.Trim ( ) == "" ? "null" : ( "'" + EMAIL + "'" ) ) + ","; sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ","; sql += "'0',"; sql += "'" + Businessbp.executedb.Usrid + "',"; sql += "sysdate"; sql += ");"; //sql += " insert into " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)"; //sql += " values ("; //sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ","; ////sql += "'" + card_number + "',"; //sql += "'" + cr_account_nbr + "',"; //sql += "'" + file_path + "',"; //sql += " '*****@*****.**',"; //sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ","; //sql += "'0',"; //sql += "'" + Businessbp.executedb.Usrid + "',"; //sql += "sysdate"; //sql += ");"; sql += " commit;"; sql += " exception"; sql += " when others then"; sql += " rollback;"; sql += " end;"; Businessbp.executedb.ExecuteDML ( sql, ref err ); if (err != "") { MessageBox.Show ( err ); //return; } } } } } else//Nếu không có dữ liệu { } } } else { } } catch (Exception ex) { MessageBox.Show ( ex.Message ); } } } } } tbStatus.Text = "Successful!!!"; this.Cursor = Cursors.Default; } } } catch (Exception ex) { MessageBox.Show ( ex.ToString ( ) ); } } } } }
private void printToFile(string fileName) { ListReport list = new ListReport(); try { ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = fileName; CrExportOptions = list.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } //need to set the datasource or it wont work list.SetDataSource(playerInfoTable); //refresh the data list.Refresh(); DataRow theRow = playerInfoDataSet.FileDetails.Rows[0]; list.SetParameterValue("nameOfList", theRow["FileName"]); list.Export(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void ExportarComprobanteButton_Click(object sender, EventArgs e) { try { Cursor = System.Windows.Forms.Cursors.WaitCursor; if (DetalleLoteDataGridView.SelectedRows.Count != 0) { for (int i = 0; i < DetalleLoteDataGridView.SelectedRows.Count; i++) { int renglon = DetalleLoteDataGridView.SelectedRows[i].Index; ReportDocument ReporteDocumento = new ReportDocument(); ProcesarComprobante(out ReporteDocumento, lote, renglon); //Nombre del comprobante ( pdf ) System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append(lote.CuitVendedor); sb.Append("-"); sb.Append(Convert.ToInt32(lote.PuntoVenta).ToString("0000")); sb.Append("-"); sb.Append(Convert.ToInt32(lote.Comprobantes[renglon].IdTipoComprobante).ToString("00")); sb.Append("-"); sb.Append(Convert.ToInt32(lote.Comprobantes[renglon].NumeroComprobante).ToString("00000000")); sb.Append(".pdf"); //ExportOptions DiskFileDestinationOptions crDiskFileDestinationOptions = new DiskFileDestinationOptions(); crDiskFileDestinationOptions.DiskFileName = Aplicacion.Aplic.ArchPathPDF + sb.ToString(); ReporteDocumento.ExportOptions.ExportDestinationOptions = crDiskFileDestinationOptions; ReporteDocumento.ExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; ReporteDocumento.ExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); ReporteDocumento.ExportOptions.ExportFormatOptions = CrFormatTypeOptions; ReporteDocumento.Export(); ReporteDocumento.Close(); } if (DetalleLoteDataGridView.SelectedRows.Count == 1) { MessageBox.Show("El comprobante seleccionado se ha exportado satisfactoriamente.", "Exportar Comprobantes", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); } else { MessageBox.Show("Los comprobantes seleccionados se han exportado satisfactoriamente.", "Exportar Comprobantes", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Problemas al procesar el comprobante", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); } finally { Cursor = System.Windows.Forms.Cursors.Default; } }
public override void Start() { FrmProgress frmProgreso = new FrmProgress(); frmProgreso.Start(2, "Exportando ..."); frmProgreso.Next(); try { CReporte reporte = null; ExportFormatOptions opciones = null; ExportFormatType formato; string nombreArchivo = string.Empty; string extension = string.Empty; string sql = string.Empty; if (base.m_ObjectFlow is DocumentoGenerico) { DocumentoGenerico documento = (DocumentoGenerico)base.m_ObjectFlow; reporte = documento.TipoDocumento.Reporte; nombreArchivo = string.Format("{0} - Nº {1} {2}", reporte.Nombre, documento.Numeracion, DateTime.Now.ToString("yyyy-MM-dd")); sql = reporte.SQL; foreach (ParametroReporte Parametro in reporte.ParametrosSQL) sql = sql.Replace(Parametro.Nombre, documento.ValueByProperty(Parametro.Propiedad).ToString()); } else if (base.m_ObjectFlow is CReporte) { reporte = (CReporte)base.m_ObjectFlow; nombreArchivo = string.Format("{0} {1}", reporte.Nombre, DateTime.Now.ToString("yyyy-MM-dd")); sql = reporte.SQL; foreach (ParametroReporte Parametro in reporte.ParametrosSQL) sql = sql.Replace(Parametro.Nombre, Parametro.Valor); } switch (base.m_Parameter) { case TypeEnum.CEnumExportFormat.PDF: formato = ExportFormatType.PortableDocFormat; opciones = new PdfRtfWordFormatOptions(); extension = ".pdf"; break; case TypeEnum.CEnumExportFormat.WORD: formato = ExportFormatType.WordForWindows; opciones = new PdfRtfWordFormatOptions(); extension = ".doc"; break; case TypeEnum.CEnumExportFormat.EXCEL: formato = ExportFormatType.Excel; opciones = new ExcelFormatOptions(); extension = ".xls"; break; default: throw new Exception("El formato no es válido."); } if (reporte != null) { ReportDocument CryRpt = new ReportDocument(); CryRpt.Load(String.Format("{0}{1}", FrmMain.CarpetaReportes, reporte.Ubicacion)); frmProgreso.Next(); // Si existe una consulta SQL se ejecuta. if (sql.Trim().Length > 0) { CryRpt.SetDataSource(HelperNHibernate.GetDataSet(sql)); } // Se reemplazan los parámetros Crystal. foreach (ParametroReporte Parametro in reporte.ParametrosCrystal) CryRpt.SetParameterValue(Parametro.Nombre, Parametro.Valor); // Se exporta el reporte. ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); CrDiskFileDestinationOptions.DiskFileName = string.Format("{0}{1}{2}", FrmMain.CarpetaExportacion, nombreArchivo, extension); CrExportOptions = CryRpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = formato; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = opciones; } // Se exporta el archivo. CryRpt.Export(); // Se inicia un proceso para abrir el archivo. Process.Start(CrDiskFileDestinationOptions.DiskFileName); } else throw new Exception("Entidad no válida."); base.m_ResultProcess = EnumResult.SUCESS; } catch (Exception ex) { SoftException.Control(ex); } finally { frmProgreso.Close(); base.Start(); } }
private static void ExportPDF(ReportDocument rpt, string sCaminhoSave) { CrystalDecisions.Windows.Forms.CrystalReportViewer cryView = new CrystalDecisions.Windows.Forms.CrystalReportViewer(); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = sCaminhoSave; CrExportOptions = rpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } rpt.Export(); }
protected void lnkbtnDownload_Click(object sender, EventArgs e) { try { string InvoicePath = Server.MapPath("~/openpages/"); InvoicePath = InvoicePath + "Invoice.pdf"; ReportDocument doc = new ReportDocument(); string fileName = Server.MapPath("~/Reports/Invoice.rpt"); doc.Load(fileName); doc.SetParameterValue("@invoiceNo", lblInvoiceNo.Text); doc.SetParameterValue("@notes", lblNotes.Text); doc.SetParameterValue("@subTotal", lblSubTotal.Text); doc.SetParameterValue("@discount", lbldiscount.Text); doc.SetParameterValue("@Tax", lblTaxAmount.Text); doc.SetParameterValue("@total", lblTotalAmount.Text); doc.SetParameterValue("@invoiceDate", lblInvoiceDate.Text); doc.SetParameterValue("@clientName", clientName); doc.SetParameterValue("@clientAdd", clientAdd); doc.SetParameterValue("@clientState", clientState); doc.SetParameterValue("@clientCountry", clientCountry); doc.SetParameterValue("@clientEmail", clientEmail); doc.SetParameterValue("@description", lbldesc.Text); doc.SetParameterValue("@NoOfEmailsSent", lblnoofemailsent.Text); doc.SetParameterValue("@priceRate", lblprice.Text); doc.SetParameterValue("@amount", lblamount.Text); doc.SetParameterValue("@status", lblStatus.Text); doc.SetParameterValue("@clientMobileNo", clientMobileNo); doc.SetParameterValue("@payvia", lblPayVia.Text); doc.SetParameterValue("@tranId", lblTranId.Text); DataTable dtDes = null; if (ViewState["invoiceDesc"] != null && ViewState["invoiceDesc"].ToString() != string.Empty) { dtDes = (DataTable)ViewState["invoiceDesc"]; for (int i = 1; i <= dtDes.Rows.Count; i++) { doc.SetParameterValue("@des" + i.ToString(), dtDes.Rows[i - 1]["Description"].ToString()); doc.SetParameterValue("@amt" + i.ToString(), dtDes.Rows[i - 1]["SubAmount"].ToString()); } if (dtDes.Rows.Count != 5) { for (int i = dtDes.Rows.Count + 1; i <= 5; i++) { doc.SetParameterValue("@des" + i.ToString(), string.Empty); doc.SetParameterValue("@amt" + i.ToString(), string.Empty); } } } else { for (int i = 1; i <= 5; i++) { doc.SetParameterValue("@des" + i.ToString(), string.Empty); doc.SetParameterValue("@amt" + i.ToString(), string.Empty); } } ExportOptions exportOpts = new ExportOptions(); DiskFileDestinationOptions diskOpts = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions pdfFormatOps = new PdfRtfWordFormatOptions(); exportOpts = doc.ExportOptions; exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat; exportOpts.ExportDestinationType = ExportDestinationType.DiskFile; FileInfo TheFile = new FileInfo(InvoicePath); if (TheFile.Exists) { File.Delete(InvoicePath); } doc.ExportToDisk(ExportFormatType.PortableDocFormat, InvoicePath); byte[] binaryData = File.ReadAllBytes(InvoicePath); Response.Clear(); Response.ContentType = "application/octet-stream"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + InvoicePath); Response.BinaryWrite(binaryData); Response.End(); } catch (Exception ex) { //ExceptionPolicy.HandleException(ex, "Web.UIPolicy"); } }
private void button1_Click(object sender, EventArgs e) { if (dataGridView1.DataSource != null) { if (dataGridView1.RowCount > 0) { for (int i = 0; i <= dataGridView1.Rows.Count - 1; i++) { try { if (dataGridView1.Columns[0] is DataGridViewCheckBoxColumn) { if (((bool)dataGridView1[0, i].Value).ToString().ToUpper() == "true".ToUpper()) { //string ref_no = dataGridView1["So_GD", i].Value.ToString(); //string type = dataGridView1["TYPE", i].Value.ToString(); //string batch_id = dataGridView1["batch_id", i].Value.ToString(); try {//xác định vị trí của row string ref_no = dataGridView1["ref_no", i].Value.ToString(); // string ref_no = dataGridView1["So_GD", i].Value.ToString(); string type = dataGridView1["TYPE", i].Value.ToString(); string batch_id = dataGridView1["batch_id", i].Value.ToString(); string sql = "", err = ""; if (type.Trim().ToUpper() == "PREPAID CARD") { sql = " select c.branch_name,'0300872315' as Ma_So_thue,substr(a.message_receive,9,16) as so_gd, nvl(a.type,'1') as Loai_gd"; if (Frm_main.admin_flag) { sql += " ,to_char(a.date_modify,'dd/mm/yyyy') as ngay_gd,e.client_code,d.card_number,a.embossed_name"; } else { sql += " ,to_char(a.date_modify,'dd/mm/yyyy') as ngay_gd,e.client_code,decode(d.card_number,null,d.card_number,substr(d.card_number,1,4)||'xxxxxxxx'||substr(d.card_number,-4)) card_number,a.embossed_name"; } sql += " ,upper(d.address1 || d.address2||d.address3||d.address4) as Dia_chi"; sql += " ,e.legal_id,a.decription as dien_giai,'' as sotien_bang_Chu"; sql += " from " + Businessbp.executedb.owner + "nab_balance_prepaid_realtime a," + Businessbp.executedb.owner + "app_users b," + Businessbp.executedb.owner + "branch c," + Businessbp.executedb.owner + "card d," + Businessbp.executedb.owner + "client e"; sql += " where a.card_number=d.card_number"; sql += " and trim(a.user_create)=trim(b.userid)"; sql += " and b.appid='" + Businessbp.executedb.APPID + "'"; sql += " and trim(b.branch)=trim(c.branch_code)"; sql += " and c.bank_code='970428'"; sql += " and d.client_code=e.client_code"; sql += " and nvl(a.authorize,'N')='Y'"; sql += " and a.batch_id='" + batch_id + "'"; sql += " and a.ref_no='" + ref_no + "'"; if (tbUser.Text.Trim() == "") sql += " and b.userid='" + Businessbp.executedb.Usrid + "'"; else sql += " and b.userid='" + tbUser.Text.Trim() + "'"; sql += " union all "; sql += " select c.branch_name,'0300872315' as Ma_So_thue,substr(a.message_receive,9,16) as so_gd, nvl(a.type,'1') as Loai_gd"; if (Frm_main.admin_flag) { sql += " ,to_char(a.date_modify,'dd/mm/yyyy') as ngay_gd,e.client_code,d.card_number,a.embossed_name"; } else { sql += " ,to_char(a.date_modify,'dd/mm/yyyy') as ngay_gd,e.client_code,decode(d.card_number,null,d.card_number,substr(d.card_number,1,4)||'xxxxxxxx'||substr(d.card_number,-4)) card_number,a.embossed_name"; } sql += " ,upper(d.address1 || d.address2||d.address3||d.address4) as Dia_chi"; sql += " ,e.legal_id,a.decription as dien_giai,'' as sotien_bang_Chu"; sql += " from " + Businessbp.executedb.owner + "nab_balance_prepaid a," + Businessbp.executedb.owner + "app_users b," + Businessbp.executedb.owner + "branch c," + Businessbp.executedb.owner + "card d," + Businessbp.executedb.owner + "client e"; sql += " where a.card_number=d.card_number"; sql += " and trim(a.user_create)=trim(b.userid)"; sql += " and b.appid='" + Businessbp.executedb.APPID + "'"; sql += " and trim(b.branch)=trim(c.branch_code)"; sql += " and c.bank_code='970428'"; sql += " and d.client_code=e.client_code"; sql += " and nvl(a.authorize,'N')='Y'"; sql += " and a.batch_id='" + batch_id + "'"; sql += " and a.ref_no='" + ref_no + "'"; if (tbUser.Text.Trim() == "") sql += " and b.userid='" + Businessbp.executedb.Usrid + "'"; else sql += " and b.userid='" + tbUser.Text.Trim() + "'"; // sql += " and b.userid='" + Businessbp.executedb.Usrid + "'"; } else { sql = " select c.branch_name,'0300872315' as Ma_So_thue,substr(a.message_receive,9,16) as so_gd, nvl(a.type,'1') as Loai_gd"; if (Frm_main.admin_flag) { sql += " ,to_char(a.date_modify,'dd/mm/yyyy') as ngay_gd,e.client_code,d.card_number,a.embossed_name"; } else { sql += " ,to_char(a.date_modify,'dd/mm/yyyy') as ngay_gd,e.client_code,decode(d.card_number,null,d.card_number,substr(d.card_number,1,4)||'xxxxxxxx'||substr(d.card_number,-4)) card_number,a.embossed_name"; } sql += " ,upper(d.address1 || d.address2||d.address3||d.address4) as Dia_chi"; sql += " ,e.legal_id,a.decription as dien_giai,'' as sotien_bang_Chu"; sql += " from " + Businessbp.executedb.owner + "nab_balance_credit a," + Businessbp.executedb.owner + "app_users b," + Businessbp.executedb.owner + "branch c," + Businessbp.executedb.owner + "card d," + Businessbp.executedb.owner + "client e"; sql += " where a.card_number=d.card_number"; sql += " and trim(a.user_create)=trim(b.userid)"; sql += " and b.appid='" + Businessbp.executedb.APPID + "'"; sql += " and trim(b.branch)=trim(c.branch_code)"; sql += " and c.bank_code='970428'"; sql += " and d.client_code=e.client_code"; sql += " and nvl(a.authorize,'N')='Y'"; sql += " and a.batch_id='" + batch_id + "'"; sql += " and a.ref_no='" + ref_no + "'"; } DataTable dtCheckAuthorize = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); } else { if (dtCheckAuthorize.Rows.Count <= 0) { MessageBox.Show("No data found!!!"); //executedb.WriteLogERROR(Session["Username"].ToString(), "IP: " + Request.UserHostAddress, this.Page.ToString(), System.Reflection.MethodBase.GetCurrentMethod().ToString(), "No data found!!!: ref_no=" + e.Item.Cells[9].Text); //Alert.Show("No data found!!!", this); //return; } else { if (dtCheckAuthorize.Rows.Count > 0) { //Lấy thông tin tài khoản // Session["sql1"] = sql; string trn_ref_no = dtCheckAuthorize.Rows[0]["so_gd"].ToString(); sql = ""; err = ""; sql = ""; sql += " select '' as ref_no,'' as Don_vi,'' as Ma_so_thue,c.TRN_REF_NO,'' as ngay_gd,'' as CIF"; sql += " ,'' TK_Tham_chieu,'' ho_tenKH,'' address_KH,'' CMND,'' ngay_cap,'' noi_cap"; sql += " ,to_char(c.AC_ENTRY_SR_NO) as So_but_toan"; sql += " ,decode(c.DRCR_IND,'D','N','C') as no_co"; // sql += " ,to_char(c.AC_BRANCH) as Ma_CN"; if (Frm_main.admin_flag) { sql += " ,to_char(c.AC_BRANCH) as Ma_CN,to_char(c.AC_NO) as So_Tai_khoan"; } else { sql += " ,to_char(c.AC_BRANCH) as Ma_CN,decode(c.AC_NO,null,c.AC_NO,substr(c.AC_NO,1,4)||'xxxxxxxx'||substr(c.AC_NO,-4)) as So_Tai_khoan"; } sql += " ,nvl((select gl_desc from gltm_glmaster@Flexcube where gl_code = c.ac_no)"; sql += " ,(select sca.ac_desc"; sql += " from sttm_cust_account@Flexcube sca"; sql += " where sca.cust_ac_no = c.ac_no)) as Ten_tai_khoan"; sql += " ,ltrim(to_char(nvl(c.LCY_AMOUNT,0), '999,999,999,999'))||' VND' as so_tien"; sql += " ,'' as so_tien_bang_chu,'' dien_giai,'' as User_create,'' as user_authorize,'' as Ghi_chu"; sql += " from acvw_all_ac_entries@Flexcube c"; sql += " where c.TRN_REF_NO='" + trn_ref_no + "'"; sql += " and substr(c.ac_no, 1, 4) not in ('4711', '4712') "; sql += " and substr(c.ac_no, 1, 6) not in ('519101','519201')"; sql += " and c.event not in ('CYPO', 'RPOS')"; ////Lấy thông tin giao dịch string folderOut = "D:\\KTT\\SOGIAODICH\\".Trim(); string card_number = dtCheckAuthorize.Rows[0]["card_number"].ToString(); string file_path = folderOut + "\\" +card_number+ "-" + trn_ref_no.Trim() + ".pdf"; if (!Directory.Exists(folderOut)) { Directory.CreateDirectory(folderOut); } string typerp = ""; string title = ""; if (dtCheckAuthorize.Rows[0]["Loai_gd"].ToString() == "1") title = "PHIẾU NỘP TIỀN MẶT"; else title = "PHIẾU CHUYỂN KHOẢN"; string fromtime = dpFromDate.Text; string totime = dpToDate.Text; string Donvi = ""; string MaSoThue = ""; string NgayGD = ""; string MaKH = ""; string TaiKhoanThamChieu = ""; string HoTenKH = ""; string DiaChi = ""; string CMND = ""; string NgayCap = ""; string NoiCap = ""; string MaSoThueKH = ""; string DienGiai = ""; string SoTienBangChu = ""; if (dtCheckAuthorize.Rows.Count <= 0) { MessageBox.Show("No data found!!"); return; } else { Donvi = dtCheckAuthorize.Rows[0]["branch_name"].ToString(); MaSoThue = dtCheckAuthorize.Rows[0]["Ma_So_thue"].ToString(); NgayGD = dtCheckAuthorize.Rows[0]["ngay_gd"].ToString(); MaKH = dtCheckAuthorize.Rows[0]["client_code"].ToString(); TaiKhoanThamChieu = dtCheckAuthorize.Rows[0]["card_number"].ToString(); HoTenKH = dtCheckAuthorize.Rows[0]["embossed_name"].ToString(); DiaChi = dtCheckAuthorize.Rows[0]["Dia_chi"].ToString(); CMND = dtCheckAuthorize.Rows[0]["legal_id"].ToString(); NgayCap = ""; NoiCap = ""; MaSoThueKH = ""; DienGiai = dtCheckAuthorize.Rows[0]["dien_giai"].ToString(); SoTienBangChu = dtCheckAuthorize.Rows[0]["sotien_bang_Chu"].ToString(); } //Session["sql"] = sql; //if (dtCheckAuthorize.Rows[0]["Loai_gd"].ToString() == "1") // Session["title"] = "PHIẾU NỘP TIỀN MẶT"; //else // Session["title"] = "PHIẾU CHUYỂN KHOẢN"; //Session["type"] = "PHIEUHT"; //Session["fromtime"] = dpFromDate.Text; //Session["totime"] = dpToDate.Text; //Response.Write("<script type='text/javascript'>window.open('Reportview_HachToan.aspx','window_name','scrollbars=yes,menubar=no,height=700,width=785,resizable=yes,toolbar=no,location=no,status=no,fullscreen=no,titlebar=no,directories=no');</script>"); // DataTable dt = new DataTable(); DataTable dt = Businessbp.executedb.getTable(sql, ref err); if (err != "") { MessageBox.Show(err); //return; } for (int j = 0; j < dt.Rows.Count; j++) { if (dt.Rows[j]["So_Tai_khoan"].ToString().Trim() == Businessbp.executedb.TKTienmat) { dt.Rows[j]["Ten_tai_khoan"] = "Tien mat tai don vi"; } else if (dt.Rows[j]["So_Tai_khoan"].ToString().Trim() == Businessbp.executedb.DomesticPrepaid) { dt.Rows[j]["Ten_tai_khoan"] = "TIEN GUI DE BAO DAM THANH TOAN THE NOI DIA-PREPAID"; } else if (dt.Rows[j]["So_Tai_khoan"].ToString().Trim() == Businessbp.executedb.InternalPrepaid) { dt.Rows[j]["Ten_tai_khoan"] = "TIEN GUI DE BAO DAM THANH TOAN THE QUOC TE-PRREPAID"; } else if (dt.Rows[j]["So_Tai_khoan"].ToString().Trim() == Businessbp.executedb.DomesticCredit) { dt.Rows[j]["Ten_tai_khoan"] = "TIEN GUI DE BAO DAM THANH TOAN THE NOI DIA-CREDIT"; } else if (dt.Rows[j]["So_Tai_khoan"].ToString().Trim() == Businessbp.executedb.InternalCredit) { dt.Rows[j]["Ten_tai_khoan"] = "TIEN GUI DE BAO DAM THANH TOAN THE QUOC TE-CREDIT"; } else if (dt.Rows[j]["So_Tai_khoan"].ToString().Trim() == Businessbp.executedb.TK_TamTreoNapTien) { dt.Rows[j]["Ten_tai_khoan"] = "PHAI TRA TRONG NV NAP TIEN THE PREPAID,CREDIT"; } } //Export Rpt_Cash_Deposit cry = new Rpt_Cash_Deposit(); cry.SetDataSource(dt); cry.SetParameterValue("Title", title); cry.SetParameterValue("Donvi", Donvi); cry.SetParameterValue("MaSoThue", MaSoThue); cry.SetParameterValue("NgayGD", NgayGD); cry.SetParameterValue("MaKH", MaKH); cry.SetParameterValue("TaiKhoanThamChieu", TaiKhoanThamChieu); cry.SetParameterValue("HoTenKH", HoTenKH); cry.SetParameterValue("DiaChi", DiaChi); cry.SetParameterValue("CMND", CMND); cry.SetParameterValue("NgayCap", NgayCap); cry.SetParameterValue("NoiCap", NoiCap); cry.SetParameterValue("MaSoThueKH", MaSoThueKH); cry.SetParameterValue("DienGiai", DienGiai); cry.SetParameterValue("SoTienBangChu", SoTienBangChu); // CrystalReportViewer1.ReportSource = rptDoc; // cry.SetDataSource(dt_bill); //string title = ""; // cry.SetParameterValue("title", title); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export(); //--Fix for Load Report failse if (cry != null) { cry.Close(); cry.Dispose(); } } } } } catch (Exception ex) { #region logException //executedb.WriteLogERROR(Session["Username"].ToString(), "IP: " + Request.UserHostAddress, this.Page.ToString(), System.Reflection.MethodBase.GetCurrentMethod().ToString(), ex.Message); ////executedb.WriteLogPri(Environment.NewLine + DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToLongTimeString() + " - " + Session["Username"].ToString() + ": " + ex.Message); ////Response.Write("<script>alert('Error when executing coding')</script>"); //Alert.Show("Error when executing coding!!!", this); #endregion logException } //dt2.Rows.Add("'" + dataGridView1["file_id", i].Value.ToString(), "'" + dataGridView1["settlement_currency", i].Value.ToString() // , "'" + dataGridView1["type", i].Value.ToString(), dataGridView1["billing_amount_electra", i].Value.ToString() // , dataGridView1["billing_amount_flexcube", i].Value.ToString(), dataGridView1["settlement_amount", i].Value.ToString() // , dataGridView1["fee_phai_thu", i].Value.ToString(), dataGridView1["fee_phai_tra", i].Value.ToString() // , action);//.to.Add(dataGridView1.Rows[i]); } } } catch { } } // Frm_ReportAcc.ExportToExcel(dt2, "", title, "Ngày thanh toán " + DateTime.Parse(DateTime.Now.ToShortDateString()).ToString("dd/MM/yyyy")); } MessageBox.Show("OK!"); } }
private void cmdPDF_Click(object sender, EventArgs e) { conn.Open(); string sql = "Select student_id,sname,major,minor From student order by student_id"; OleDbCommand dbComm = new OleDbCommand(sql, conn); OleDbDataAdapter dbAdap = new OleDbDataAdapter(dbComm); DataSet ds = new DataSet(); dbAdap.Fill(ds, "Student"); rptStudent rptStudent = new rptStudent(); rptStudent.SetDataSource(ds); conn.Close(); IEnumerator ie = rptStudent.Section1.ReportObjects.GetEnumerator(); ie.Reset(); while (ie.MoveNext()) { if (ie.Current != null && ie.Current.GetType().ToString().Equals("CrystalDecisions.CrystalReports.Engine.TextObject")) { CrystalDecisions.CrystalReports.Engine.TextObject txt = (CrystalDecisions.CrystalReports.Engine.TextObject)ie.Current; if (txt.Name == "txtLevelTerm") txt.Text = "Class : Level 4 Term 2"; } } ie = rptStudent.Section5.ReportObjects.GetEnumerator(); ie.Reset(); while (ie.MoveNext()) { if (ie.Current != null && ie.Current.GetType().ToString().Equals("CrystalDecisions.CrystalReports.Engine.TextObject")) { CrystalDecisions.CrystalReports.Engine.TextObject txt = (CrystalDecisions.CrystalReports.Engine.TextObject)ie.Current; if (txt.Name == "txtCGPA") txt.Text = "CGPA : 3.42"; } } try { ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = Application.StartupPath + "\\Student.pdf"; CrExportOptions = rptStudent.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.ExportDestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.ExportFormatOptions = CrFormatTypeOptions; } rptStudent.Export(); Process process = new Process(); process.StartInfo.FileName=Application.StartupPath+"\\Student.pdf"; process.Start(); } catch(IOException) { MessageBox.Show("The pdf file is already open. Please close it and try again."); } }
public void PrintReport(string key) { try { ReportDocument objRpt = new ReportDocument(); DataSet ds = (DataSet)this.GetDataReport(key); //string reportPath = Application.StartupPath + "\\Reportes\\OrdenCompraxprov.rpt"; string reportPath = "C:\\Reportes\\OrdenCompraxprov.rpt"; objRpt.Load(reportPath); DiskFileDestinationOptions crDiskFileDestinationOption = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions crFormatTypeOption = new PdfRtfWordFormatOptions(); ExportOptions crExportOptions = new ExportOptions(); objRpt.SetDataSource(ds.Tables[0]); string strfolder = "C:\\Reporte\\"; crDiskFileDestinationOption.DiskFileName = strfolder + "Data.pdf"; crExportOptions = objRpt.ExportOptions; crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; crExportOptions.ExportDestinationOptions = crDiskFileDestinationOption; crExportOptions.ExportFormatOptions = crFormatTypeOption; objRpt.Export(); MessageBox.Show("Registros exportados", "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { } }
private void ExportPDF(string sNameFile) { try { DirectoryInfo dinfo = new DirectoryInfo(Server.MapPath("Pedidos")); if (!dinfo.Exists) { dinfo.Create(); } if (File.Exists(Server.MapPath("Pedidos\\" + sNameFile))) { File.Delete(Server.MapPath("Pedidos\\" + sNameFile + ".pdf")); } // PesquisarDados(); ReportDocument rpt = new ReportDocument(); DataTable TabelaImpressao = (DataTable)Session["PedidoRes"]; rpt.Load(Server.MapPath("RelatorioPedido.rpt")); rpt.SetDataSource(TabelaImpressao); CrystalDecisions.Web.CrystalReportViewer cryView = new CrystalDecisions.Web.CrystalReportViewer(); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = Server.MapPath("Pedidos\\" + sNameFile + ".pdf"); CrExportOptions = rpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } rpt.Export(); } catch (Exception ex) { throw ex; //lblInfo.Text = ex.Message + (ex.InnerException != null ? ex.InnerException.Message : ""); } }
private void ExportPDF() { try { lblInfo.Text = "Gerando arquivo pdf para anexo"; DirectoryInfo dinfo = new DirectoryInfo(Server.MapPath("Pedidos")); if (!dinfo.Exists) { dinfo.Create(); } if (File.Exists(Server.MapPath("Pedidos\\" + sCodigoPedido + ".pdf"))) { File.Delete(Server.MapPath("Pedidos\\" + sCodigoPedido + ".pdf")); } CarregaDataTableParaImpressao(); ReportDocument rpt = new ReportDocument(); dsPedido TabelaImpressao = (dsPedido)Session["PedidoRes"]; rpt.Load(Server.MapPath("rptPedido.rpt")); rpt.SetDataSource(TabelaImpressao); CrystalDecisions.Web.CrystalReportViewer cryView = new CrystalDecisions.Web.CrystalReportViewer(); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = Server.MapPath("Pedidos\\" + sCodigoPedido + ".pdf"); CrExportOptions = rpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } rpt.Export(); lblInfo.Text = "Exportando arquivo para o servidor"; } catch (Exception ex) { throw ex; //lblInfo.Text = ex.Message + (ex.InnerException != null ? ex.InnerException.Message : ""); } }
private void Export_pdf(DataTable dt_export, string branch_code, string type, string folderOut) { try { if (dt_export.Rows.Count > 0) { string file_path = folderOut.Trim() + branch_code.Trim() + "_" + type + "_" + dpFromDate.Text.Trim().Replace("/", "") + "_" + dpToDate.Text.Trim().Replace("/", "") + ".pdf"; PHT06 cry = new PHT06(); cry.SetDataSource(dt_export); string title = "DANH SÁCH THẺ ĐÃ PHÁT HÀNH, IN PIN"; cry.SetParameterValue("Title", title); cry.SetParameterValue("Fromdate", dpFromDate.Text.Trim()); cry.SetParameterValue("Todate", dpToDate.Text.Trim()); ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf"; CrExportOptions = cry.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; //CrExportOptions.ExportFormatType = ExportFormatType.WordForWindows; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cry.Export(); //--Fix for Load Report failse if (cry != null) { cry.Close(); cry.Dispose(); } } } catch (Exception ex) { //MessageBox.Show("Err Export_pdf: \n Branch: "+ branch_code +"\n" + ex.ToString()); string[] row = { branch_code, ex.ToString() }; dgv_err.Rows.Add(row); } }
private string ConvertReportToPDF() { var summaryReport = CreateSummarySalesReport(); InsertSummaryData(summaryReport); summaryReport.WriteXml("summaryReport.xml"); var saleSum = new SalesSummary(); saleSum.SetDataSource(summaryReport); try { ExportOptions crOptions; DiskFileDestinationOptions crDestinationOptions=new DiskFileDestinationOptions(); PdfRtfWordFormatOptions pdfRtf=new PdfRtfWordFormatOptions(); crDestinationOptions.DiskFileName = @"saleSummary.pdf"; crOptions = saleSum.ExportOptions; { crOptions.ExportDestinationType = ExportDestinationType.DiskFile; crOptions.ExportFormatType = ExportFormatType.PortableDocFormat; crOptions.DestinationOptions = crDestinationOptions; crOptions.FormatOptions = pdfRtf; } saleSum.Export(); return @"saleSummary.pdf"; } catch (Exception e) { //ignore } return null; }
/// <summary> /// Liefert den Dateipfad des exportierten Reports. /// </summary> /// <param name="reportname">Der Name des gewählten Reports.</param> /// <param name="recordId">ID des Datensatzes, für den der Exportdateipfad ermittelt werden soll.</param> /// <returns>Export-Dateipfad des übergebenen Reports.</returns> public static String GetExportFilepath(EReports reportname, int recordId) { ReportDocument report = null; report = GetReportByName(reportname); report.SetParameterValue("Param" + reportname + "ID", recordId); var crExportOptions = report.ExportOptions; var crDiskFileDestinationOptions = new DiskFileDestinationOptions(); var crFormatTypeOptions = new PdfRtfWordFormatOptions(); String exportFilePath = DefaultFilepath + reportname.ToString() + " " + recordId.ToString() + ".pdf"; crDiskFileDestinationOptions.DiskFileName = exportFilePath; crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; crExportOptions.DestinationOptions = crDiskFileDestinationOptions; crExportOptions.FormatOptions = crFormatTypeOptions; report.Export(); return exportFilePath; }
private string exportCrystalReports(string crUserID, string crPassword) { //Instanciation des classes permettant l'exportation des données ReportDocument cryRpt = new ReportDocument(); TableLogOnInfos crtableLogoninfos = new TableLogOnInfos(); TableLogOnInfo crtableLogoninfo = new TableLogOnInfo(); ConnectionInfo crConnectionInfo = new ConnectionInfo(); Tables CrTables; //Enregistre le chemin du répertoire de sortie du projet --> répertoire de sortie veut dire Debug ou Release var outPutDirectory = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase); //Répertoire où se trouve le fichier .rpt var rptPath = Path.Combine(outPutDirectory, "Reports\\Serveur mail - OT quotidiens.rpt"); //Cast en chaine de caractere de rptPath string rpt_path = new Uri(rptPath).LocalPath; //Répertoire où se trouve le PDF envoyé par mail var pdfPath = Path.Combine(outPutDirectory, "Reports\\Serveur mail - OT quotidiens.pdf"); //Cast en chaine de caractere de pdfPath string pdf_path = new Uri(pdfPath).LocalPath; try { //Chargement du rapport dans cette instance cryRpt.Load(rpt_path); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } //Paramétres de la base de données crConnectionInfo.UserID = crUserID; crConnectionInfo.Password = crPassword; CrTables = cryRpt.Database.Tables; foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables) { crtableLogoninfo = CrTable.LogOnInfo; crtableLogoninfo.ConnectionInfo = crConnectionInfo; CrTable.ApplyLogOnInfo(crtableLogoninfo); } try { crystalReportViewer4.ReportSource = cryRpt; crystalReportViewer4.Refresh(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } try { //Paramètres d'exportation du fichier .rpt ExportOptions CrExportOptions; DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions(); CrDiskFileDestinationOptions.DiskFileName = pdf_path; CrExportOptions = cryRpt.ExportOptions; { CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions; CrExportOptions.FormatOptions = CrFormatTypeOptions; } cryRpt.Export(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } return pdf_path; }
public void PrintReport(string key) { try { ReportDocument objRpt = new ReportDocument(); DataSet ds = (DataSet)this.GetDataReport(key); //string reportPath = Application.StartupPath + "\\Reporte\\co_ordenCompra_rpt.rpt"; string reportPath = "C:\\Reportes\\co_ordenCompra_rpt.rpt"; objRpt.Load(reportPath); DiskFileDestinationOptions crDiskFileDestinationOption = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions crFormatTypeOption = new PdfRtfWordFormatOptions(); ExportOptions crExportOptions = new ExportOptions(); objRpt.SetDataSource(ds.Tables[0]); string strfolder = "C:\\Reporte\\"; crDiskFileDestinationOption.DiskFileName = strfolder + txtOc.Text + ".pdf"; crExportOptions = objRpt.ExportOptions; crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile; crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; crExportOptions.ExportDestinationOptions = crDiskFileDestinationOption; crExportOptions.ExportFormatOptions = crFormatTypeOption; objRpt.Export(); this.printDialog1.Document = this.printDocument1; DialogResult dr = this.printDialog1.ShowDialog(); if (dr == DialogResult.OK) { int ncopy = this.printDocument1.PrinterSettings.Copies; int npage = this.printDocument1.PrinterSettings.FromPage; int epage = this.printDocument1.PrinterSettings.ToPage; string PrinterName = this.printDocument1.PrinterSettings.PrinterName; objRpt.PrintOptions.PrinterName = PrinterName; objRpt.PrintToPrinter(ncopy, false, npage, epage); } } catch (Exception ex) { } }