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)
{
}
}
