예제 #1
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        //string l_pk = Request["master_pk"];

        string TemplateFile = "rpt_bisc00020_O_4.xls";
        string TempFile     = "../../../../system/temp/rpt_bisc00020_O_4_" + Session["User_ID"].ToString() + ".xls";

        TemplateFile = Server.MapPath(TemplateFile);
        TempFile     = Server.MapPath(TempFile);

        //Create a new workbook
        IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);

        //Add worksheet
        IWorksheet exSheet = exBook.Worksheets[1];

        IRange range;

        //bind data to excel file

        //string para = "'" + l_pk + "'";
        string p_from_date        = Request["p_from_date"];
        string p_to_date          = Request["p_to_date"];
        string p_wh_type          = Request["p_wh_type"];
        string p_tin_warehouse_pk = Request["p_wh"];
        string p_use_yn           = Request["p_use_yn"];
        string p_item_group_pk    = Request["p_item_group_pk"];
        string p_item             = Request["p_item"];
        string p_in_out_yn        = Request["p_in_out_yn"];
        string p_wh_name          = Request["p_wh_name"];

        string    para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_tin_warehouse_pk + "','" + p_use_yn + "','" + p_item_group_pk + "','" + p_item + "','" + p_in_out_yn + "'";
        DataTable dt   = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_O_4", "'" + Session["User_ID"].ToString() + "'");
        DataTable dt1  = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_O_1", para);

        //--set-----DataBind--in---Master---
        if (!string.IsNullOrEmpty(dt.Rows[0]["pic"].ToString()))
        {
            exSheet.Cells[1, 1].Select();
            byte[] MyData = new byte[0];
            MyData = (byte[])dt.Rows[0]["pic"];
            MemoryStream stream = new MemoryStream(MyData);

            Bitmap b = new Bitmap(stream);
            exSheet.Shapes.AddPicture(ResizeImage(b, 90, 59));
        }

        exSheet.Cells[6, 1].Value = "W/H Name : " + p_wh_name;

        exSheet.Cells[2, 3].Value = dt.Rows[0]["partner_name"].ToString();

        exSheet.Cells[3, 3].Value = dt.Rows[0]["addr1"].ToString();

        exSheet.Cells[4, 3].Value = "Tax code:" + dt.Rows[0]["tax_code"].ToString();

        exSheet.Cells[2, 6].Value = "Tell:" + dt.Rows[0]["phone_no"].ToString();

        exSheet.Cells[3, 6].Value = "Fax:" + dt.Rows[0]["fax_no"].ToString();

        exSheet.Cells[4, 6].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");

        exSheet.Cells[6, 6].Value = System.DateTime.ParseExact(p_from_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy") + "-" + System.DateTime.ParseExact(p_to_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");

        for (int l_addrow = 1; l_addrow < dt1.Rows.Count; l_addrow++)
        {
            exSheet.Range["A9"].Rows.EntireRow.Insert();//insert row new of sheet
        }
        double total = 0;

        for (int l_row = 0; l_row <= dt1.Rows.Count - 1; l_row++)
        {
            total = total + CommondLib.ConvertToDbl(dt1.Rows[l_row]["begin_qty"].ToString());
            exSheet.Cells[8 + l_row, 1].Value = l_row + 1;
            exSheet.Cells[8 + l_row, 2].Value = dt1.Rows[l_row]["item_code"];
            exSheet.Cells[8 + l_row, 3].Value = dt1.Rows[l_row]["item_name"];
            exSheet.Cells[8 + l_row, 4].Value = dt1.Rows[l_row]["uom"];
            if (dt1.Rows[l_row]["begin_qty"].ToString() != "")
            {
                exSheet.Cells[8 + l_row, 5].Value = CommondLib.ConvertToDbl(dt1.Rows[l_row]["begin_qty"].ToString());
            }
        }

        exSheet.Cells[8 + dt1.Rows.Count, 5].Value = total;

        if (File.Exists(TempFile))
        {
            File.Delete(TempFile);
        }


        // hide row firts

        range = exSheet.Range["A1"];
        // hide row A5
        range.Rows.Hidden = true;

        // font bold header

        exBook.SaveAs(TempFile);
        System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
        Response.Clear();
        Response.Charset         = "UTF-8";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        //Add header, give a default file name for "File Download/Store as"
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
        //Add header, set file size to enable browser display download progress
        Response.AddHeader("Content-Length", file.Length.ToString());
        //Set the return string is unavailable reading for client, and must be downloaded
        Response.ContentType = "application/ms-exSheet";
        //Response.ContentType = "application/pdf";
        //Send file string to client
        Response.WriteFile(TempFile);
        //Response.WriteFile(pdfFilePath);
        //Stop execute
        Response.End();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        string l_repayment_pk = Request["p_tht_repayment_pk"];
        string p_employee_pk  = Request["p_employee_pk"];

        string TemplateFile = "rpt_60250170_Guest_Invoice.xls";
        string TempFile     = "../../../../system/temp/rpt_60250170_Guest_Invoice_" + Session["User_ID"].ToString() + ".xls";

        TemplateFile = Server.MapPath(TemplateFile);
        TempFile     = Server.MapPath(TempFile);

        //Create a new workbook
        IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);

        //Add worksheet
        IWorksheet exSheet = exBook.Worksheets[1];

        IRange range;

        //bind data to excel file
        string    para = "";
        DataTable dt;

        para = "'" + l_repayment_pk + "'";
        dt   = CtlLib.TableReadOpenCursor("ht_rpt_60250170_repayment", para);
        string SQL = "";

        SQL = "select max(full_name) from thr_employee where pk='" + p_employee_pk + "'";
        DataTable dtEmp = new DataTable();

        dtEmp = CtlLib.TableReadOpen(SQL);
        exSheet.Cells[33, 5].Value = dtEmp.Rows[0][0].ToString();         //full_name login sofware
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[8, 4].Value = "There is not data!";
        }
        if (dt.Rows.Count > 0)
        {
            string l_status_title = "GUEST INVOICE";


            exSheet.Cells[7, 3].Value = l_status_title;
            //exSheet.Cells[ 8, 1].Value = dt.Rows[0]["confirm_msg"].ToString();
            exSheet.Cells[9, 3].Value  = dt.Rows[0]["guest_name"].ToString();
            exSheet.Cells[9, 6].Value  = dt.Rows[0]["print_time"].ToString();
            exSheet.Cells[10, 3].Value = dt.Rows[0]["partner_name"].ToString();
            exSheet.Cells[10, 6].Value = dt.Rows[0]["room_no"].ToString();
            //exSheet.Cells[10, 7].Value = l_folio_type;
            exSheet.Cells[11, 3].Value = dt.Rows[0]["customer_address"].ToString();
            exSheet.Cells[11, 6].Value = dt.Rows[0]["invoice_no"].ToString();
            exSheet.Cells[12, 3].Value = dt.Rows[0]["tax_code"].ToString();
            exSheet.Cells[12, 6].Value = dt.Rows[0]["arrival_dt"].ToString();
            //exSheet.Cells[13, 3].Value = dt.Rows[0]["guest_name"].ToString(); //total guest
            exSheet.Cells[13, 6].Value = dt.Rows[0]["checkout_dt"].ToString();
            exSheet.Cells[14, 4].Value = dt.Rows[0]["paymethod"].ToString();

            exSheet.Cells[17, 5].Value = double.Parse(dt.Rows[0]["total_due_vnd_pp"].ToString());                    //
            exSheet.Cells[17, 7].Value = float.Parse(dt.Rows[0]["total_due_usd_pp"].ToString());
            //exSheet.Cells[18, 5].Value = dt.Rows[0]["paymethod"].ToString();TTDB  VND
            //exSheet.Cells[18, 7].Value = dt.Rows[0]["paymethod"].ToString();TTDB  USD
            exSheet.Cells[19, 5].Value = int.Parse(dt.Rows[0]["svc_amt"].ToString());
            exSheet.Cells[19, 7].Value = float.Parse(dt.Rows[0]["svc_amt_usd"].ToString());

            exSheet.Cells[20, 5].Value = int.Parse(dt.Rows[0]["total_vnd"].ToString());
            exSheet.Cells[20, 7].Value = float.Parse(dt.Rows[0]["total_usd"].ToString());

            exSheet.Cells[21, 5].Value = int.Parse(dt.Rows[0]["vat_amt"].ToString());
            exSheet.Cells[21, 7].Value = float.Parse(dt.Rows[0]["vat_amt_usd"].ToString());

            //exSheet.Cells[22, 5].Value = int.Parse(dt.Rows[0]["total_discount_amt"].ToString());
            //exSheet.Cells[22, 7].Value = float.Parse(dt.Rows[0]["total_discount_amt_usd"].ToString());

            //exSheet.Cells[22, 5].Value = int.Parse(dt.Rows[0]["advance_amt"].ToString());
            //exSheet.Cells[22, 7].Value = float.Parse(dt.Rows[0]["advance_amt_usd"].ToString());

            exSheet.Cells[23, 5].Value = int.Parse(dt.Rows[0]["total_due_end_vnd"].ToString());
            exSheet.Cells[23, 7].Value = float.Parse(dt.Rows[0]["total_due_usd"].ToString());

            exSheet.Cells[24, 3].Value = int.Parse(dt.Rows[0]["cash_vnd"].ToString());
            exSheet.Cells[24, 5].Value = float.Parse(dt.Rows[0]["cash_usd"].ToString());

            exSheet.Cells[25, 3].Value = int.Parse(dt.Rows[0]["credit_vnd"].ToString());
            exSheet.Cells[25, 5].Value = float.Parse(dt.Rows[0]["credit_usd"].ToString());
            if (int.Parse(dt.Rows[0]["cash_vnd"].ToString()) == 0 && float.Parse(dt.Rows[0]["cash_usd"].ToString()) == 0)
            {
                range             = exSheet.Range["A24"];
                range.Rows.Hidden = true;
            }
            if (int.Parse(dt.Rows[0]["credit_vnd"].ToString()) == 0 && float.Parse(dt.Rows[0]["credit_usd"].ToString()) == 0)
            {
                range             = exSheet.Range["A25"];
                range.Rows.Hidden = true;
            }
            string inword_amt, inword_amt_usd;
            inword_amt                 = CommondLib.Num2VNText(dt.Rows[0]["total_due_end_vnd"].ToString(), "VND");
            inword_amt_usd             = CommondLib.Num2EngText(dt.Rows[0]["total_due_usd"].ToString(), "USD");
            exSheet.Cells[26, 3].Value = inword_amt.ToString();
            exSheet.Cells[27, 3].Value = inword_amt_usd.ToString();
        }

        /*if (l_svc < 1)//not svc then hide row svc
         * {
         *  range = exSheet.Range["A15"];
         *  // hide row A15
         *  range.Rows.Hidden = true;
         * }*/

        //----------------------
        ////end-set-----DataBind--in---Master---

        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range["A16"].Rows.EntireRow.Insert();//insert row new of sheet
        }
        //set date title of year
        //exSheet.Cells[2, 1].Value = "Income Statement Year" + " " + l_year;

        //set data to detail.
        // loop detail not percent
        int l_cnt = dt.Rows.Count;

        if (dt.Rows.Count > 0)
        {
            for (int l_row = 1; l_row <= dt.Rows.Count; l_row++)
            {
                for (int col = 0; col <= 6; col++)
                {
                    if (col == 1 || col == 2 || col == 3)
                    {
                        exSheet.Cells[15 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString();
                        exSheet.Cells[l_row + 15, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
                        exSheet.Range[16, col + 1, l_row + 15, 7].Font.Bold    = false;
                    }
                    else
                    {
                        if (col == 6)
                        {
                            if (dt.Rows[l_row - 1][col].ToString() != "")
                            {
                                exSheet.Cells[15 + l_row, col + 1].Value = float.Parse(dt.Rows[l_row - 1][col].ToString());
                                exSheet.Cells[15, col + 1, l_row + 15, col + 1].NumberFormat = "#,##0.00";
                                exSheet.Cells[l_row + 15, col + 1].HorizontalAlignment       = XlHAlign.xlHAlignRight;
                            }
                        }
                        else
                        {
                            if (dt.Rows[l_row - 1][col].ToString() != "")
                            {
                                exSheet.Cells[15 + l_row, col + 1].Value = long.Parse(dt.Rows[l_row - 1][col].ToString());
                                exSheet.Cells[15, col + 1, l_row + 15, col + 1].NumberFormat = "#,##0";
                                exSheet.Cells[l_row + 15, col + 1].HorizontalAlignment       = XlHAlign.xlHAlignRight;
                            }
                        }
                        exSheet.Range[16, col + 1, l_row + 15, 7].Font.Bold = false;
                    }

                    //exSheet.Cells[l_row + 12, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
                }
            }
        }

        // end loop detail percent
        if (File.Exists(TempFile))
        {
            File.Delete(TempFile);
        }


        // hide row firts

        range = exSheet.Range["A1"];
        // hide row A5
        range.Rows.Hidden = true;

        // font bold header

        exBook.SaveAs(TempFile);
        //CtlLib.ExcelToPdf(TempFile);
        //string pdfFilePath = TempFile.Replace(".xls", ".pdf");
        //write out to client broswer
        System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
        //System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
        Response.Clear();
        Response.Charset         = "UTF-8";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        //Add header, give a default file name for "File Download/Store as"
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
        //Add header, set file size to enable browser display download progress
        Response.AddHeader("Content-Length", file.Length.ToString());
        //Set the return string is unavailable reading for client, and must be downloaded
        Response.ContentType = "application/ms-exSheet";
        //Response.ContentType = "application/pdf";
        //Send file string to client
        Response.WriteFile(TempFile);
        //Response.WriteFile(pdfFilePath);
        //Stop execute
        Response.End();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        string TemplateFile = "rpt_60250300_vat_invoice.xls";
        string TempFile     = "../../../../system/temp/rpt_60250300_vat_invoice_" + Session["User_ID"].ToString() + ".xls";

        TemplateFile = Server.MapPath(TemplateFile);
        TempFile     = Server.MapPath(TempFile);

        //Create a new workbook
        IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);

        //Add worksheet
        IWorksheet exSheet = exBook.Worksheets[1];

        IRange range;

        //bind data to excel file
        string p_tac_crca_pk = Request.QueryString["p_tac_crca_pk"];
        string para          = "'" + p_tac_crca_pk + "'";



        DataTable dtMaster = CtlLib.TableReadOpenCursor("ac_rpt_60250300_vatinvoice_mst", para);
        DataTable dtDetail = CtlLib.TableReadOpenCursor("ac_rpt_60250300_vatinvoice_dtl", para);


        //set master data
        //day
        exSheet.Cells["D13"].Value = dtMaster.Rows[0]["dd"].ToString();
        //month
        exSheet.Cells["E13"].Value = dtMaster.Rows[0]["mm"].ToString();
        //year
        exSheet.Cells["G13"].Value = dtMaster.Rows[0]["yyyy"].ToString();
        //guest name
        exSheet.Cells["D14"].Value = dtMaster.Rows[0]["recv_person"].ToString();
        //customer name
        exSheet.Cells["D15"].Value = dtMaster.Rows[0]["customer_name"].ToString();
        //tax code
        exSheet.Cells["D16"].Value = dtMaster.Rows[0]["taxcode"].ToString();
        //customer address
        exSheet.Cells["C17"].Value = dtMaster.Rows[0]["addr1"].ToString();
        //pay_method
        exSheet.Cells["D18"].Value = dtMaster.Rows[0]["pay_method"].ToString();

        //sub net total
        exSheet.Cells["H33"].Value = double.Parse(dtMaster.Rows[0]["net_amt"].ToString());
        //service charge amt
        exSheet.Cells["H34"].Value = double.Parse(dtMaster.Rows[0]["svc_amt"].ToString());
        //vat amt
        exSheet.Cells["H35"].Value = double.Parse(dtMaster.Rows[0]["vat_amt"].ToString());
        //total amt
        exSheet.Cells["H36"].Value = double.Parse(dtMaster.Rows[0]["total_amt"].ToString());

        //VAT RATE
        exSheet.Cells["HC5"].Value = 10;
        //number to VN words
        exSheet.Cells["A39"].Value = CommondLib.Num2VNText(dtMaster.Rows[0]["total_amt"].ToString(), "VND");

        //set detail data
        int l_pos = 22;
        int i     = 0;

        for (i = 0; i < dtDetail.Rows.Count; i++)
        {
            //exSheet.Cells[l_pos+i,1].Value =i+1;
            exSheet.Cells[l_pos + i, 2].Value = dtDetail.Rows[i]["item_name"].ToString();          //item name

            //exSheet.Cells[l_pos+i,4].Value = dtDetail.Rows[i]["item_uom"].ToString(); //unit
            //exSheet.Cells[l_pos+i,5].Value = dtDetail.Rows[i]["qty"].ToString(); //qty
            //exSheet.Cells[l_pos+i,6].Value = dtDetail.Rows[i]["u_price"].ToString(); //price

            exSheet.Cells[l_pos + i, 8].Value = double.Parse(dtDetail.Rows[i]["amt"].ToString());          //net amt
        }

        if (File.Exists(TempFile))
        {
            File.Delete(TempFile);
        }


        exBook.SaveAs(TempFile);
        //write out to client broswer
        System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
        //System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
        Response.Clear();
        Response.Charset         = "UTF-8";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        //Add header, give a default file name for "File Download/Store as"
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
        //Add header, set file size to enable browser display download progress
        Response.AddHeader("Content-Length", file.Length.ToString());
        //Set the return string is unavailable reading for client, and must be downloaded
        Response.ContentType = "application/ms-exSheet";
        //Response.ContentType = "application/pdf";
        //Send file string to client
        //Response.WriteFile(pdfFilePath);
        Response.WriteFile(TempFile);
        //Stop execute
        Response.End();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        //CtlLib.SetUser("hr");
        string p_mon      = Request["p_mon"].ToString();
        string p_reg_type = Request.QueryString["p_reg_type"].ToString();
        string p_org_pk   = Request.QueryString["p_org"].ToString();

        string TemplateFile = "rpt_hris00600_0.xls";
        string TempFile     = "../../../system/temp/rpt_hris00600_0_" + Session["User_ID"].ToString() + ".xls";

        TemplateFile = Server.MapPath(TemplateFile);
        TempFile     = Server.MapPath(TempFile);

        //Create a new workbook
        IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);

        //Add worksheet
        IWorksheet exSheet = exBook.Worksheets[1];

        string SQL
            = "select code,v.CODE_NM " +
              "from vhr_hr_code v " +
              "where v.ID='HR0049' and nvl(v.tco_company_pk,'" + p_org_pk + "')=" + "(select a.tco_company_pk from tco_org a where a.del_if=0 and a.pk='" + p_org_pk + "')";
        //Response.Write(SQL);
        //Response.End();
        DataTable dt_com = CtlLib.TableReadOpen(SQL);

        SQL
            = "  SELECT UPPER((select F.CODE_FNM from vhr_hr_code f where F.ID = 'HR0054' and F.CODE = V.CHAR_5)) title ,v.CODE_NM, " +
              "         ti.FULL_NAME,  " +
              "         case when E.SEX = 'M' then substr(E.BIRTH_DT,1,4) else '' end BIRTH_DT_M,  " +
              "         case when E.SEX = 'F' then substr(E.BIRTH_DT,1,4) else '' end BIRTH_DT_F,  " +
              "         ti.SOCIAL_NO,  " +
              "         (case when TI.INS_REG_TYPE='02' and TI.DETAIL_INS_REG_TYPE='07' then (CASE WHEN ti.ST_SOCIAL_DT IS NOT NULL THEN (case when months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')) >12 then '0-12' " +
              "                                                                                                                                else 0 || '-'||months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')) end) ELSE NULL END)  " +
              "                    else (CASE WHEN ti.ST_SOCIAL_DT IS NOT NULL THEN TRUNC(months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')) / 12) ||'-' || MOD(months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')),12) ELSE NULL END)  " +
              "           end)  tg_thamgia, " +
              "         ROUND (NVL (ti.AVERAGE_SALARY, 0), 0),  " +
              "         ti.CONDITION,  " +
              "         ti.THOI_DIEM,  " +
              "         TO_CHAR (TO_DATE (ti.FROM_DT, 'yyyymmdd'), 'dd/mm/yyyy') from_dt,  " +
              "         TO_CHAR (TO_DATE (ti.TO_DT, 'yyyymmdd'), 'dd/mm/yyyy') to_dt,  " +
              "         ROUND (NVL (ti.DAYS, 0), 2),  " +
              "         ROUND (NVL (ti.PROGRESSIVE, 0), 0),  " +
              "         ROUND (ti.INS_AMT, -2),'',  " +
              "         e.emp_id  " +
              "    FROM THR_INS_REGULATION ti, vhr_hr_code v, thr_employee e  " +
              "   WHERE     ti.DEL_IF = 0  " +
              "         AND e.del_if = 0  " +
              "         AND e.pk = ti.thr_emp_pk  " +
              "         AND ti.DEL_IF = 0  " +
              "         AND ti.INS_MONTH = '" + p_mon + "'  " +
              "         and decode('" + p_reg_type + "','ALL','ALL',ti.INS_REG_TYPE)='" + p_reg_type + "' " +
              "         AND v.ID = 'HR0055'  " +
              "         AND v.CODE = ti.DETAIL_INS_REG_TYPE  " +
              "ORDER BY ( (SELECT F.CODE " +
              "                    FROM vhr_hr_code f " +
              "                   WHERE F.ID = 'HR0054' AND F.CODE = V.CHAR_5)), NVL (v.num_1, 0),  " +
              "         v.code_nm,  " +
              "         e.emp_id,ti.FULL_NAME,  " +
              "         ti.from_dt  ";


        DataTable dt_emp = CtlLib.TableReadOpen(SQL);
        int       irow   = dt_emp.Rows.Count;
        int       icol   = dt_emp.Columns.Count;

        //Response.Write(SQL);
        //Response.End();

        if (dt_emp.Rows.Count == 0)
        {
            Response.Write("There is no data to show");
            Response.End();
        }


        //Khai bao bien
        int i, j = 0, k = 0, pos = 20;
        int count = 0;

        string[] atype = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K" };
        string[] btype = new string[] { "I", "II", "III", "IV", "V", "VI", "VII", "VIII", "IX", "X", "XI" };
        Double   total_day = 0, total_prog = 0, total_amt = 0, grand_total = 0;
        int      a = 0, b = 0;
        string   quy = "";
        int      month;

        //Insert new row
        for (i = 0; i < irow - 1; i++)
        {
            if (dt_emp.Rows[i][0].ToString() != dt_emp.Rows[i + 1][0].ToString())
            {
                a++;
            }
            if (dt_emp.Rows[i][1].ToString() != dt_emp.Rows[i + 1][1].ToString())
            {
                b++;
            }
        }

        for (i = 0; i < irow + a + b; i++)
        {
            exSheet.Range[pos + 1, 1].Rows.EntireRow.Insert();
        }

        month = int.Parse(p_mon.Substring(4, 2).ToString());

        if (month >= 1 && month <= 3)
        {
            quy = "01";
        }
        else if (month >= 4 && month <= 6)
        {
            quy = "02";
        }
        else if (month >= 7 && month <= 9)
        {
            quy = "03";
        }
        else
        {
            quy = "04";
        }

        exSheet.Cells["A1"].Value = "Tên cơ quan (đơn vị): " + dt_com.Rows[0][1].ToString();
        exSheet.Cells["A2"].Value = "Mã đơn vị: " + dt_com.Rows[1][1].ToString();
        exSheet.Cells["A3"].Value = "Điện thoại: " + dt_com.Rows[12][1].ToString();
        exSheet.Cells["A6"].Value = "Tháng " + p_mon.Substring(4, 2).ToString() + " quý " + quy + " năm " + p_mon.Substring(0, 4).ToString();
        exSheet.Cells["A7"].Value = "Số hiệu tài khoản: " + dt_com.Rows[8][1].ToString() + " mở tại: " + dt_com.Rows[9][1].ToString();

        //Show data
        for (i = 0; i < irow; i++)
        {
            //total_day = total_day + Double.Parse(dt_emp.Rows[i][12].ToString());
            //total_prog = total_prog + Double.Parse(dt_emp.Rows[i][13].ToString());
            total_amt   = total_amt + Double.Parse(dt_emp.Rows[i][14].ToString());
            grand_total = grand_total + Double.Parse(dt_emp.Rows[i][14].ToString());
            //Dong dau tien
            if (i == 0)
            {
                exSheet.Cells[pos + i - 2, 1].Value = atype[j].ToString();
                exSheet.Cells[pos + i - 1, 1].Value = btype[k].ToString();
                exSheet.Cells[pos + i - 2, 2].Value = dt_emp.Rows[i][0].ToString();
                exSheet.Cells[pos + i - 1, 2].Value = dt_emp.Rows[i][1].ToString();
                k++;
            }

            count++;
            exSheet.Cells[pos + i, 1].Value = count;

            for (int col = 2; col < icol; col++)
            {
                if (col == 7 || (col >= 12 && col <= 14))
                {
                    exSheet.Cells[pos + i, col].Value = Double.Parse(dt_emp.Rows[i][col].ToString());
                }
                else
                {
                    exSheet.Cells[pos + i, col].Value = dt_emp.Rows[i][col].ToString();
                }
            }

            if (i == irow - 1)  //Dong cuoi cung
            {
                exSheet.Cells[pos + i + 1, 2].Value = "Tổng cộng ";
                //exSheet.Cells[pos + i + 1, 13].Value = total_day;
                //exSheet.Cells[pos + i + 1, 14].Value = total_prog;
                exSheet.Cells[pos + i + 1, 14].Value = total_amt;
                exSheet.Cells[pos + i + 1, 1, pos + i + 1, 15].Rows.Font.Bold = true;
                pos = pos + 1;

                total_day  = 0;
                total_prog = 0;
                total_amt  = 0;
            }
            else
            {
                if (dt_emp.Rows[i][0].ToString() != dt_emp.Rows[i + 1][0].ToString())
                {
                    exSheet.Cells[pos + i + 1, 2].Value = "Tổng cộng ";
                    //exSheet.Cells[pos + i + 1, 13].Value = total_day;
                    //exSheet.Cells[pos + i + 1, 14].Value = total_prog;
                    exSheet.Cells[pos + i + 1, 14].Value = total_amt;
                    exSheet.Cells[pos + i + 1, 1, pos + i + 1, 15].Rows.Font.Bold = true;


                    j++;
                    exSheet.Cells[pos + i + 2, 1].Value = atype[j].ToString();
                    exSheet.Cells[pos + i + 2, 2].Value = dt_emp.Rows[i + 1][0].ToString();
                    exSheet.Cells[pos + i + 2, 1, pos + i + 1, 11].Rows.Font.Bold = true;
                    pos = pos + 2;

                    k          = 0;
                    count      = 0;
                    total_day  = 0;
                    total_prog = 0;
                    total_amt  = 0;
                }

                if (dt_emp.Rows[i][1].ToString() != dt_emp.Rows[i + 1][1].ToString())
                {
                    exSheet.Cells[pos + i + 1, 1].Value = btype[k].ToString();
                    exSheet.Cells[pos + i + 1, 2].Value = dt_emp.Rows[i + 1][1].ToString();
                    exSheet.Cells[pos + i + 1, 1, pos + i + 1, 11].Rows.Font.Bold = true;
                    pos = pos + 1;
                    k++;
                    count = 0;
                }
            }
        }

        pos = 20 + irow + a + b + 20;  //cộng thêm phần điều chỉnh
        exSheet.Cells["F" + (pos + 3)].Value = grand_total;
        exSheet.Cells["C" + (pos + 6)].Value = "(" + string.Format("Viết bằng chữ: {0}", CommondLib.Num2VNText(grand_total.ToString(), "VND")) + ")";

        exSheet.Cells["K" + (pos + 8)].Value = string.Format("Ngày {0} tháng {1} năm {2}", DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year);

        exSheet.Cells["a" + (pos + 15)].Value = dt_com.Rows[5][1].ToString();
        exSheet.Cells["g" + (pos + 15)].Value = dt_com.Rows[6][1].ToString();
        exSheet.Cells["k" + (pos + 15)].Value = dt_com.Rows[7][1].ToString();

        // end loop detail percent
        if (File.Exists(TempFile))
        {
            File.Delete(TempFile);
        }


        // hide row firts

        //range = exSheet.Range["A1"];
        // hide row A5
        //range.Rows.Hidden = true;

        exBook.SaveAs(TempFile);
        //CtlLib.ExcelToPdf(TempFile);
        //string pdfFilePath = TempFile.Replace(".xls", ".pdf");

        //write out to client broswer
        System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
        //System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
        Response.Clear();
        Response.Charset         = "UTF-8";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        //Add header, give a default file name for "File Download/Store as"
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
        //Add header, set file size to enable browser display download progress
        Response.AddHeader("Content-Length", file.Length.ToString());
        //Set the return string is unavailable reading for client, and must be downloaded
        Response.ContentType = "application/ms-exSheet";
        //Response.ContentType = "application/pdf";
        //Send file string to client
        Response.WriteFile(TempFile);
        //Response.WriteFile(pdfFilePath);
        //Stop execute
        Response.End();
    }