public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
Rpt_MH_PHT08_GiaoNhanThePin rpt = new Rpt_MH_PHT08_GiaoNhanThePin();
rpt.Site = this.Site;
return rpt;
}
private void btProcess_Click(object sender, EventArgs e)
{
try
{
if (rbMBPHT08.Checked)
{
string sql = "", err = "";
string sql1 = "", sql2 = "", debit = "", prepaid = "", credit = "";//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.branch_code ||'- '|| b.branch_name from " + Businessbp.executedb.owner + "branch b ";
sql += " where trim(b.branch_code)=trim(cp.branch_receive_card) and b.bank_code='970428' ";
sql += " and b.office_type='B') branch_name ";
sql += " ,count(c.card_number) as SoLuongThe ";
sql += " from " + Businessbp.executedb.owner + "card c," + Businessbp.executedb.owner + "cp_card_prepaid cp," + Businessbp.executedb.owner + "nab_phanloaisp sp ";
sql += " where to_char(c.status_code) = '0' ";
sql += " and c.card_number=cp.card_number ";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from_dt + "','dd/mm/yyyy')) ";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to_dt + "','dd/mm/yyyy')) ";
sql += " and c.product_code <> '333' ";
sql += " and c.product_code=sp.product_code ";
sql += " and sp.type in ('D','P') ";
sql += " and not exists (select rp.card_number from " + Businessbp.executedb.owner + "card_replace rp where rp.card_number= c.card_number)";// and rp.reason_code='10' : Upgrade
if (cb_branch.SelectedValue.ToString().Trim() != "8888")
sql += " and trim(cp.branch_receive_card) ='" + cb_branch.SelectedValue.ToString().Trim() + "' ";
sql += " group by cp.branch_receive_card ";
sql += " union all ";
sql += " ";
sql += " Select trim(cd.branch_receive_card) branch_code, ";
sql += " (select b.branch_code ||'- '|| b.branch_name from " + Businessbp.executedb.owner + "branch b ";
sql += " where trim(b.branch_code)=trim(cd.branch_receive_card) and b.bank_code='970428') branch_name ";
sql += " ,count(c.card_number) as SoLuongThe ";
sql += " from " + Businessbp.executedb.owner + "card c," + Businessbp.executedb.owner + "cp_card cd," + Businessbp.executedb.owner + "application app," + Businessbp.executedb.owner + "nab_phanloaisp sp2 ," + Businessbp.executedb.owner + "cr_card_balance_details crd";
sql += " where to_char(c.status_code) = '0' ";
sql += " and app.source_application_no=cd.source_application_no ";
sql += " and c.card_number=crd.card_number ";
sql += " and c.branch_ref_number = app.source_application_no "; //2014-07-30 (1)
//sql += " and crd.cr_account_nbr=app.card_number ";// old 2014-07-30 (1)
// sql += " and (c.branch_ref_number=app.source_application_no or c.cr_account_nbr=app.card_number)";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from_dt + "','dd/mm/yyyy')) ";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to_dt + "','dd/mm/yyyy')) ";
sql += " and c.product_code <> '333' ";
sql += " and c.product_code=sp2.product_code ";
sql += " and sp2.type='C' ";
sql += " and not exists (select rp.card_number from " + Businessbp.executedb.owner + "card_replace rp where rp.card_number= c.card_number)";// and rp.reason_code='10': Upgrade
if (cb_branch.SelectedValue.ToString().Trim() != "8888")
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
{ }
}
