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!"); } }
public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport() { Rpt_Cash_Deposit rpt = new Rpt_Cash_Deposit(); rpt.Site = this.Site; return rpt; }