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;
}
