protected void Page_Load(object sender, EventArgs e)
    {
        ESysLib.SetUser(Session["APP_DBUSER"].ToString());

        string l_pk             = Request["p_master_pk"];
        string l_tco_company_pk = Request["p_company_pk"];
        string TemplateFile     = "rpt_60240010_Reservation.xls";
        string TempFile         = "../../../system/temp/rpt_60240010_Reservation_" + 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, dtSum;

        para = "'" + l_pk + "','" + l_tco_company_pk + "'";
        dt   = ESysLib.TableReadOpenCursor("ht_rpt_60240010_reservation", para);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[6, 8].Value = "There is not data!";
        }
        //insert row de can bang page
        if (dt.Rows.Count == 1)
        {
            for (int l_addrow = 1; l_addrow < 17; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 2)
        {
            for (int l_addrow = 1; l_addrow < 16; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 3)
        {
            for (int l_addrow = 1; l_addrow < 15; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 4)
        {
            for (int l_addrow = 1; l_addrow < 14; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 5)
        {
            for (int l_addrow = 1; l_addrow < 13; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 6)
        {
            for (int l_addrow = 1; l_addrow < 12; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 7)
        {
            for (int l_addrow = 1; l_addrow < 11; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 8)
        {
            for (int l_addrow = 1; l_addrow < 10; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 9)
        {
            for (int l_addrow = 1; l_addrow < 9; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 10)
        {
            for (int l_addrow = 1; l_addrow < 8; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 11)
        {
            for (int l_addrow = 1; l_addrow < 7; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 12)
        {
            for (int l_addrow = 1; l_addrow < 6; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 13)
        {
            for (int l_addrow = 1; l_addrow < 5; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 14)
        {
            for (int l_addrow = 1; l_addrow < 4; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }
        if (dt.Rows.Count == 15)
        {
            for (int l_addrow = 1; l_addrow < 3; l_addrow++)
            {
                exSheet.Range["A32"].Rows.EntireRow.Insert();                //insert row new of sheet
            }
        }

        //end insert row de can bang page
        if (dt.Rows.Count > 0)
        {
            //exSheet.Cells[3, 1].Value = "Month :"+l_month.Substring(4,2)+"/"+l_month.Substring(0,4);
            //exSheet.Cells[3, 6].Value = dt.Rows[0]["print_time"].ToString();

            exSheet.Cells[6, 3].Value = dt.Rows[0]["full_name"].ToString();
            exSheet.Cells[6, 7].Value = dt.Rows[0]["contact_person"].ToString();

            exSheet.Cells[7, 7].Value  = dt.Rows[0]["company_name"].ToString();
            exSheet.Cells[11, 3].Value = dt.Rows[0]["crt_dt"].ToString();
            exSheet.Cells[9, 7].Value  = dt.Rows[0]["phone"].ToString();
            exSheet.Cells[10, 7].Value = dt.Rows[0]["fax"].ToString();
            exSheet.Cells[11, 7].Value = dt.Rows[0]["email"].ToString();
            exSheet.Cells[12, 6].Value = "Reservation# :" + dt.Rows[0]["slip_no"].ToString();
            exSheet.Cells[14, 3].Value = dt.Rows[0]["contact_person"].ToString();            //Dear:
            if (dt.Rows[0]["total_deposit_amt_bk"].ToString() != "")
            {
                exSheet.Cells[24, 4].Value = double.Parse(dt.Rows[0]["total_deposit_amt_bk"].ToString());
            }
            exSheet.Cells[25, 3].Value = dt.Rows[0]["credit_card_owner"].ToString();
            exSheet.Cells[26, 3].Value = dt.Rows[0]["credit_type"].ToString();
            exSheet.Cells[27, 3].Value = dt.Rows[0]["credit_no"].ToString();
            exSheet.Cells[28, 3].Value = dt.Rows[0]["expire_date"].ToString();
            exSheet.Cells[21, 3].Value = dt.Rows[0]["request_desc"].ToString();
        }

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

        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range["A19"].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_total_room = 0, l_total_guest = 0;
        int l_begin_row = 18;

        int l_cnt = dt.Rows.Count;

        for (int l_row = 0; l_row < dt.Rows.Count; l_row++)
        {
            exSheet.Range[l_begin_row + l_row, 2, l_begin_row + l_row, 4].Merge();

            exSheet.Cells[l_begin_row + l_row, 2].Value = dt.Rows[l_row]["guest_name"].ToString();
            exSheet.Cells[l_begin_row + l_row, 5].Value = dt.Rows[l_row]["room_type"].ToString();
            if (dt.Rows[l_row]["total_amt"].ToString() != "")
            {
                exSheet.Cells[l_begin_row + l_row, 6].Value = double.Parse(dt.Rows[l_row]["total_amt"].ToString());
            }
            exSheet.Cells[l_begin_row + l_row, 7].Value = dt.Rows[l_row]["from_date"].ToString();
            exSheet.Cells[l_begin_row + l_row, 8].Value = dt.Rows[l_row]["date_to"].ToString();
            if (dt.Rows[l_row]["night"].ToString() != "")
            {
                exSheet.Cells[l_begin_row + l_row, 9].Value = double.Parse(dt.Rows[l_row]["night"].ToString());
            }
            if (dt.Rows[l_row]["total_guest"].ToString() != "")
            {
                exSheet.Cells[l_begin_row + l_row, 10].Value = double.Parse(dt.Rows[l_row]["total_guest"].ToString());
            }
        }

        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);
        //ESysLib.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)
    {
        ESysLib.SetUser(Session["APP_DBUSER"].ToString());

        string TemplateFile = "rpt_employee_detail_list_pcss.xls";
        string TempFile     = "../../../system/temp/rpt_employee_detail_list_pcss_" + 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;

        string p_thr_group_pk, p_nation, p_search_by, p_search_temp, p_from_joindate, p_to_joindate;
        string p_salary_yn, p_position, p_pay_type, p_contract_kind, p_from_contract, p_to_contract;
        string p_sex, p_insurance_yn, p_status, p_from_leftdate, p_to_leftdate, p_from_birthdate;
        string p_ot_yn, p_to_birthdate, p_tco_dept_pk, p_sal_security, p_union;
        string p_user;
        string p_job, p_education, p_lstEmpKind, p_lstProject, p_wg, p_birth_month;
        string p_data, p_lang;

        p_user           = Request["p_user"].ToString();
        p_select_date    = Request["p1"].ToString();
        p_nation         = Request["p2"].ToString();
        p_search_by      = Request["p3"].ToString();
        p_search_temp    = Request["p4"].ToString();
        p_from_joindate  = Request["p5"].ToString();
        p_to_joindate    = Request["p6"].ToString();
        p_salary_yn      = Request["p7"].ToString();
        p_position       = Request["p8"].ToString();
        p_pay_type       = Request["p9"].ToString();
        p_contract_kind  = Request["p10"].ToString();
        p_from_contract  = Request["p11"].ToString();
        p_to_contract    = Request["p12"].ToString();
        p_sex            = Request["p13"].ToString();
        p_insurance_yn   = Request["p14"].ToString();
        p_status         = Request["p15"].ToString();
        p_from_leftdate  = Request["p16"].ToString();
        p_to_leftdate    = Request["p17"].ToString();
        p_from_birthdate = Request["p18"].ToString();
        p_ot_yn          = Request["p19"].ToString();
        p_to_birthdate   = Request["p20"].ToString();
        p_tco_dept_pk    = Request["p21"].ToString();
        p_sal_security   = Request["p22"].ToString();

        p_job         = Request["p23"].ToString();
        p_education   = Request["p24"].ToString();
        p_union       = Request["p25"].ToString();
        p_birth_month = Request["p26"].ToString();
        p_data        = Request["p27"].ToString();
        p_lang        = Request["p28"].ToString();

        /*        string[] field_E = {  ""                                                                                          //0
         *                          ,"c.ORG_NM"                                                                                 //1
         *                          ,"d.WORKGROUP_NM"                                                                           //2
         *                          ,"A.EMP_ID"                                                                                 //3
         *                          ,"A.OLD_ID"                                                                                 //4
         *                          ,"A.FULL_NAME"                                                                              //5
         *                          ,"A.ID_NUM"                                                                                 //6
         *                          ,"TO_CHAR(TO_DATE(A.JOIN_DT,'YYYYMMDD'),'DD/MM/YYYY')"                                  //7
         *                          ,"DECODE(LENGTH(BIRTH_DT),4,BIRTH_DT,TO_CHAR(TO_DATE(BIRTH_DT,'YYYYMMDD'),'DD/MM/YYYY'))"   //8
         *                          ,"sf_get_hr_common_code(a.PLACE_BIRTH,'HR0021',a.tco_company_pk)"                         //9
         *                          ,"TEL"                                                                                      //10
         *                          ,"PERMANENT_ADDR"                                                                           //11
         *                          ,"PRESENT_ADDR"                                                                             //12
         *                          ,"sf_get_hr_common_code(a.SEX,'HR0007',a.tco_company_pk)"                                 //13
         *                          ,"PERSON_ID"                                                                                //14
         *                          ,"TO_CHAR(TO_DATE(ISSUE_DT,'YYYYMMDD'),'DD/MM/YYYY')"                                   //15
         *                          ,"sf_get_hr_common_code(a.PLACE_PER_ID,'HR0014',a.tco_company_pk)"                        //16
         *                          ,"decode(MARRIED_YN,'Y','Married','Single')"
         *                          ,"sf_get_hr_common_code(a.Nation,'HR0009',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code(a.ETHNIC_TYPE,'HR0015',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code(a.RELIG_TYPE,'HR0016',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code(a.EDU_TYPE,'HR0011',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code(a.JOB_TYPE,'HR0010',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code(a.POS_TYPE,'HR0008',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code(a.PROB_TYPE,'HR0002',a.tco_company_pk)"
         *                          ,"to_char(to_date(a.BEGIN_PROBATION ,'yyyymmdd'),'dd/mm/yyyy')"
         *                          ,"to_char(to_date(a.END_PROBATION ,'yyyymmdd'),'dd/mm/yyyy')"
         *                          ,"sf_get_hr_common_code(a.CONTRACT_TYPE,'HR0001',a.tco_company_pk)"
         *                          ,"a.contract_no"
         *                          ,"to_char(to_date(a.BEGIN_Contract ,'yyyymmdd'),'dd/mm/yyyy')"
         *                          ,"to_char(to_date(a.END_contract ,'yyyymmdd'),'dd/mm/yyyy')"
         *                          ,"decode(a.SOCIAL_YN,'Y','Yes','No')"
         *                          ,"a.social_no"
         *                          ,"sf_get_hr_common_code(a.SOCIAL_PLACE,'HR0013',a.tco_company_pk)"
         *                          ,"to_char(to_date(a.SOCIAL_DT,'yyyymm'),'mm/yyyy')"
         *                          ,"decode(a.Health_YN,'Y','Yes','No')"
         *                          ,"a.health_no"
         *                          ,"sf_get_hr_common_code(a.health_PLACE,'HR0012',a.tco_company_pk)"
         *                          ,"to_char(to_date(a.health_DT,'yyyymm'),'mm/yyyy')"
         *                          ,"decode(a.Unemp_YN,'Y','Yes','No')"
         *                          ,"to_char(to_date(a.unemp_DT,'yyyymm'),'mm/yyyy')"
         *                          ,"sf_get_hr_common_code(a.EMPLOYEE_TYPE,'HR0017',a.tco_company_pk)"
         *                          ,"decode(a.OT_YN,'Y','Yes','No')"
         *                          ,"decode(a.Salary_YN,'Y','Yes','No')"
         *                          ,"nvl(a.pro_SAL,0)"
         *                          ,"a.BASIC_SAL"
         *                          ,"ALLOW_AMT1"
         *                          ,"ALLOW_AMT2"
         *                          ,"ALLOW_AMT3"
         *                          ,"ALLOW_AMT4"
         *                          ,"ALLOW_AMT5"
         *                          ,"ALLOW_AMT6"
         *                          ,"ALLOW_AMT7"
         *                          ,"ALLOW_AMT8"
         *                          ,"sf_get_hr_common_code(a.pay_type,'HR0023',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code(a.BANK_TYPE,'HR0020',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code(a.BANK_BRANCH,'HR0014',a.tco_company_pk)"
         *                          ,"a.account"
         *                          ,"sf_get_hr_common_code(a.status,'HR0022',a.tco_company_pk)"
         *                          ,"to_char(to_date(left_dt,'yyyymmdd'),'dd/mm/yyyy')"
         *                          ,"sf_get_hr_common_code(a.RESIGN_TYPE,'HR0004',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code(a.COST_GROUP,'HR0092',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code(a.COST_KIND,'HR0093',a.tco_company_pk)"
         *                          ,"pl.pl_nm"
         *                          ,"a.PASS_PORT_NO"
         *                          ,"to_char(to_date(a.pass_port_dt,'yyyymmdd'),'dd/mm/yyyy')"
         *                          ,"a.remark"
         *                          ,"decode(a.union_YN,'Y','Yes','No')"
         *                          ,"a.pit_tax_no"
         *                          ,"decode(A.ID_BYHAND_YN,'Y','Yes','No')"
         *                          ,"decode(A.HOUSE_YN,'Y','Yes','No')"
         *                          ,"decode(A.MEAL_YN,'Y','Yes','No')"
         *                          ,"c.pk"
         *                          ,"sf_get_hr_common_code(a.sal_period,'HR0030',a.tco_company_pk)"
         *                          ,"decode(a.reg_ins_yn,'Y','Yes','No')"
         *                          ,"n_country2"
         *                          ,"sf_get_hr_common_code(A.MAJOR,'HR0148',a.tco_company_pk)"
         *                          ,"i.com_nm"
         *                          ,"i.duties"
         *                          ,"i.period"
         *                          ,"substr(to_char(sysdate,'yyyy'),1,4) - substr(A.BIRTH_DT,1,4)"
         *                          ,"nvl(A.BASIC_SAL,0) + nvl(A.ALLOW_AMT1,0) + nvl(A.ALLOW_AMT2,0) + nvl(A.ALLOW_AMT3,0) + nvl(A.ALLOW_AMT4,0) + nvl(A.ALLOW_AMT5,0) + nvl(A.ALLOW_AMT6,0) + nvl(A.ALLOW_AMT7,0) + nvl(A.ALLOW_AMT8,0)"
         *                          ,""
         *                          ,""
         *                          ,""
         *                       };
         *
         *      string[] field_V = {  ""                                                                                          //0
         *                          ,"c.ORG_FNM"                                                                                 //1
         *                          ,"d.WORKGROUP_FNM"                                                                           //2
         *                          ,"A.EMP_ID"                                                                                 //3
         *                          ,"A.OLD_ID"                                                                                 //4
         *                          ,"A.FULL_NAME"                                                                              //5
         *                          ,"A.ID_NUM"                                                                                 //6
         *                          ,"TO_CHAR(TO_DATE(A.JOIN_DT,'YYYYMMDD'),'DD/MM/YYYY')"                                  //7
         *                          ,"DECODE(LENGTH(BIRTH_DT),4,BIRTH_DT,TO_CHAR(TO_DATE(BIRTH_DT,'YYYYMMDD'),'DD/MM/YYYY'))"   //8
         *                          ,"sf_get_hr_common_code_fn(a.PLACE_BIRTH,'HR0021',a.tco_company_pk)"                         //9
         *                          ,"TEL"                                                                                      //10
         *                          ,"PERMANENT_ADDR"                                                                           //11
         *                          ,"PRESENT_ADDR"                                                                             //12
         *                          ,"sf_get_hr_common_code_fn(a.SEX,'HR0007',a.tco_company_pk)"                                 //13
         *                          ,"PERSON_ID"                                                                                //14
         *                          ,"TO_CHAR(TO_DATE(ISSUE_DT,'YYYYMMDD'),'DD/MM/YYYY')"                                   //15
         *                          ,"sf_get_hr_common_code_fn(a.PLACE_PER_ID,'HR0014',a.tco_company_pk)"                        //16
         *                          ,"decode(MARRIED_YN,'Y','Đã kết hôn','Độc thân')"                                       //17
         *                          ,"sf_get_hr_common_code_fn(a.Nation,'HR0009',a.tco_company_pk)"                         //18
         *                          ,"sf_get_hr_common_code_fn(a.ETHNIC_TYPE,'HR0015',a.tco_company_pk)"                    //19
         *                          ,"sf_get_hr_common_code_fn(a.RELIG_TYPE,'HR0016',a.tco_company_pk)"                     //20
         *                          ,"sf_get_hr_common_code_fn(a.EDU_TYPE,'HR0011',a.tco_company_pk)"                       //21
         *                          ,"sf_get_hr_common_code_fn(a.JOB_TYPE,'HR0010',a.tco_company_pk)"                       //22
         *                          ,"sf_get_hr_common_code_fn(a.POS_TYPE,'HR0008',a.tco_company_pk)"                       //23
         *                          ,"sf_get_hr_common_code_fn(a.PROB_TYPE,'HR0002',a.tco_company_pk)"                      //24
         *                          ,"a.contract_no"
         *                          ,"to_char(to_date(a.BEGIN_PROBATION ,'yyyymmdd'),'dd/mm/yyyy')"                         //25
         *                          ,"to_char(to_date(a.END_PROBATION ,'yyyymmdd'),'dd/mm/yyyy')"                           //26
         *                          ,"sf_get_hr_common_code_fn(a.CONTRACT_TYPE,'HR0001',a.tco_company_pk)"                  //27
         *                          ,"a.contract_no"                                                                        //28
         *                          ,"to_char(to_date(a.BEGIN_Contract ,'yyyymmdd'),'dd/mm/yyyy')"                          //29
         *                          ,"to_char(to_date(a.END_contract ,'yyyymmdd'),'dd/mm/yyyy')"                            //30
         *                          ,"decode(a.SOCIAL_YN,'Y','Có','Không')"                                                 //31
         *                          ,"a.social_no"                                                                          //32
         *                          ,"sf_get_hr_common_code_fn(a.SOCIAL_PLACE,'HR0013',a.tco_company_pk)"                   //33
         *                          ,"to_char(to_date(a.SOCIAL_DT,'yyyymm'),'mm/yyyy')"                                     //34
         *                          ,"decode(a.Health_YN,'Y','Có','Không')"                                                 //35
         *                          ,"a.health_no"                                                                          //36
         *                          ,"sf_get_hr_common_code_fn(a.health_PLACE,'HR0012',a.tco_company_pk)"                   //37
         *                          ,"to_char(to_date(a.health_DT,'yyyymm'),'mm/yyyy')"                                     //38
         *                          ,"decode(a.Unemp_YN,'Y','Có','Không')"                                                  //39
         *                          ,"to_char(to_date(a.unemp_DT,'yyyymm'),'mm/yyyy')"                                      //40
         *                          ,"sf_get_hr_common_code_fn(a.EMPLOYEE_TYPE,'HR0017',a.tco_company_pk)"                  //41
         *                          ,"decode(a.OT_YN,'Y','Có','Không')"                                                     //42
         *                          ,"decode(a.Salary_YN,'Y','Có','Không')"                                                 //43
         *                          ,"nvl(a.pro_SAL,0)"                                                                     //44
         *                          ,"a.BASIC_SAL"                                                                          //45
         *                          ,"ALLOW_AMT1"                                                                           //46
         *                          ,"ALLOW_AMT2"                                                                           //47
         *                          ,"ALLOW_AMT3"                                                                           //48
         *                          ,"ALLOW_AMT4"                                                                           //49
         *                          ,"ALLOW_AMT5"                                                                           //50
         *                          ,"ALLOW_AMT6"                                                                           //51
         *                          ,"ALLOW_AMT7"                                                                           //52
         *                          ,"ALLOW_AMT8"                                                                           //53
         *                          ,"sf_get_hr_common_code_fn(a.pay_type,'HR0023',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code_fn(a.BANK_TYPE,'HR0020',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code_fn(a.BANK_BRANCH,'HR0014',a.tco_company_pk)"
         *                          ,"a.account"
         *                          ,"sf_get_hr_common_code_fn(a.status,'HR0022',a.tco_company_pk)"
         *                          ,"to_char(to_date(left_dt,'yyyymmdd'),'dd/mm/yyyy')"
         *                          ,"sf_get_hr_common_code_fn(a.RESIGN_TYPE,'HR0004',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code_fn(a.COST_GROUP,'HR0092',a.tco_company_pk)"
         *                          ,"sf_get_hr_common_code_fn(a.COST_KIND,'HR0093',a.tco_company_pk)"
         *                          ,"pl.pl_nm"
         *                          ,"a.PASS_PORT_NO"
         *                          ,"to_char(to_date(a.pass_port_dt,'yyyymmdd'),'dd/mm/yyyy')"
         *                          ,"a.remark"
         *                          ,"decode(a.union_YN,'Y','Có','Không')"
         *                          ,"a.pit_tax_no"
         *                          ,"decode(A.ID_BYHAND_YN,'Y','Có','Không')"
         *                          ,"decode(A.HOUSE_YN,'Y','Có','Không')"
         *                          ,"decode(A.MEAL_YN,'Y','Có','Không')"
         *                          ,"c.pk"
         *                          ,"sf_get_hr_common_code_fn(a.sal_period,'HR0030',a.tco_company_pk)"
         *                          ,"decode(a.reg_ins_yn,'Y','Có','Không')"
         *                          ,"n_country2"
         *                          ,"sf_get_hr_common_code_fn(A.MAJOR,'HR0148',a.tco_company_pk)"
         *                          ,"i.com_nm"
         *                          ,"i.duties"
         *                          ,"i.period"
         *                          ,"substr(to_char(sysdate,'yyyy'),1,4) - substr(A.BIRTH_DT,1,4)"
         *                          ,"nvl(A.BASIC_SAL,0) + nvl(A.ALLOW_AMT1,0) + nvl(A.ALLOW_AMT2,0) + nvl(A.ALLOW_AMT3,0) + nvl(A.ALLOW_AMT4,0) + nvl(A.ALLOW_AMT5,0) + nvl(A.ALLOW_AMT6,0) + nvl(A.ALLOW_AMT7,0) + nvl(A.ALLOW_AMT8,0)"
         *                          ,""
         *                          ,""
         *                          ,""
         *                       };     */
        string SQL
            = "select E.COLUMN_NUM,E.TITLE_NM,E.TITLE_FIELD,E.TITLE_FNM,E.TITLE_FIELD_FNM " +
              "from thr_title_emp e " +
              "where del_if = 0 ";

        DataTable dt_data = ESysLib.TableReadOpen(SQL);

        DataTable dt_allow = ESysLib.TableReadOpenCursor("hr_rpt_allowance_salary");

        int i, j, k, l;

        //int pic;

        int[]    pos_allow = new int[8];
        string[] allow_nm  = new string[8];
        string[] allow_fnm = new string[8];
        string[] p         = p_data.Split('|');
        //Response.Write(p_field);
        string s_field = "";
        string s_name  = "";



        //Response.Write(p[k].ToString() + " -- " + k.ToString() +" /n ");
        for (l = 0; l < p.Length; l++)
        {
            for (k = 0; k < dt_data.Rows.Count; k++)
            {
                /*               if (int.Parse(p[l].ToString()) == allow && allow < 54)
                 *             {
                 *                 pos_allow[k] = l + 2;  //ghi nhan lai vi tri column cua Allowance
                 *
                 *
                 *             }
                 */
                if (p[l].ToString() == dt_data.Rows[k][0].ToString())
                {
                    //Response.Write(p[l].ToString());
                    if (p_lang == "ENG")
                    {
                        s_field = s_field + dt_data.Rows[k][2].ToString() + ",";

                        if (int.Parse(p[l].ToString()) == 46)
                        {
                            s_name = s_name + dt_allow.Rows[0][0].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 47)
                        {
                            s_name = s_name + dt_allow.Rows[0][1].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 48)
                        {
                            s_name = s_name + dt_allow.Rows[0][2].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 49)
                        {
                            s_name = s_name + dt_allow.Rows[0][3].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 50)
                        {
                            s_name = s_name + dt_allow.Rows[0][4].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 51)
                        {
                            s_name = s_name + dt_allow.Rows[0][5].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 52)
                        {
                            s_name = s_name + dt_allow.Rows[0][6].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 53)
                        {
                            s_name = s_name + dt_allow.Rows[0][7].ToString() + "|";
                        }
                        else
                        {
                            s_name = s_name + dt_data.Rows[k][1].ToString() + "|";
                        }

                        if (dt_data.Rows[k][0].ToString() == "86") //picture
                        {
                            pic = l;                               //lay vi tri cua hinh
                        }
                    }
                    else
                    {
                        s_field = s_field + dt_data.Rows[k][4].ToString() + ",";

                        if (int.Parse(p[l].ToString()) == 46)
                        {
                            s_name = s_name + dt_allow.Rows[0][16].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 47)
                        {
                            s_name = s_name + dt_allow.Rows[0][17].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 48)
                        {
                            s_name = s_name + dt_allow.Rows[0][18].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 49)
                        {
                            s_name = s_name + dt_allow.Rows[0][19].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 50)
                        {
                            s_name = s_name + dt_allow.Rows[0][20].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 51)
                        {
                            s_name = s_name + dt_allow.Rows[0][21].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 52)
                        {
                            s_name = s_name + dt_allow.Rows[0][22].ToString() + "|";
                        }
                        else if (int.Parse(p[l].ToString()) == 53)
                        {
                            s_name = s_name + dt_allow.Rows[0][23].ToString() + "|";
                        }
                        else
                        {
                            s_name = s_name + dt_data.Rows[k][3].ToString() + "|";
                        }

                        if (dt_data.Rows[k][0].ToString() == "86") //picture
                        {
                            pic = l;                               //lay vi tri cua hinh
                        }
                    }
                }
            }
        }
        //Response.Write(pic);
        //Response.Write(s_name);
        //Response.End();
        s_field = s_field.Substring(0, s_field.Length - 1);
        SQL     = " select  " + s_field;
        SQL     = SQL + " from thr_employee a, tco_org c,thr_work_group d,THR_F_PASSPORT p,THR_F_LABOUR_LICENCE l,THR_F_VISA f, thr_f_residence g, TAC_ABPL pl, thr_family fa, thr_family fa1,  ";
        SQL     = SQL + "(SELECT edu1.thr_emp_pk,edu1.E_NAME, edu1.Major, edu1.graduate_year " +
                  "             FROM thr_emp_edu edu1,(SELECT edu2.THR_EMP_PK ,MAX (graduate_year) AS graduate_year from thr_emp_edu edu2 where edu2.del_if = 0 group by edu2.THR_EMP_PK ) edu_mas         " +
                  "             where edu1.del_if = 0 and edu_mas.THR_EMP_PK = edu1.THR_EMP_PK and  edu_mas.graduate_year = edu1.graduate_year  " +
                  "             )edu   ";

/*      SQL = SQL + "(SELECT e.pk as pk,D.PL_NM||' - '||B.PLC_NM as pl_nm ";
 *      SQL = SQL + "                       FROM TAC_ABPL D, TAC_ABPLCENTER E, TAC_ABCENTER B ";
 *      SQL = SQL + "                        WHERE D.DEL_IF    = 0 ";
 *      SQL = SQL + "                          AND E.DEL_IF    = 0 ";
 *      SQL = SQL + "                          AND E.TAC_ABPL_PK = D.PK ";
 *      SQL = SQL + "                          AND E.TAC_ABCENTER_PK = B.PK) pl ";	*/
        SQL = SQL + "         ,(select G.THR_EMPLOYEE_PK emp_pk,G.COM_NM com_nm, G.DUTIES duties,g.start_dt ,g.end_dt,HR_F_GET_PERIOD_PCSS(g.THR_EMPLOYEE_PK) period ";
        SQL = SQL + "            from thr_experience g, ";
        SQL = SQL + "            (select F.THR_EMPLOYEE_PK emp_pk, max(f.END_DT) end_dt ";
        SQL = SQL + "            from thr_experience f ";
        SQL = SQL + "            where f.del_if = 0  ";
        SQL = SQL + "            group by F.THR_EMPLOYEE_PK) h ";
        SQL = SQL + "            where G.DEL_IF = 0 and G.THR_EMPLOYEE_PK = h.emp_pk ";
        SQL = SQL + "            and G.END_DT = h.end_dt) i         ";
        SQL = SQL + "where a.del_if=0   and c.del_if=0 and d.del_if=0 and fa.del_if(+) = 0 and fa1.del_if(+) = 0 ";
        SQL = SQL + "and edu.thr_emp_pk(+) =a.pk  ";
        SQL = SQL + "and a.tco_org_pk=c.pk and a.thr_wg_pk=d.pk and pl.pk(+)=a.TAC_ABPL_PK and A.PK = i.emp_pk(+) and fa.thr_employee_pk(+) = a.pk and fa1.thr_employee_pk(+) = a.pk and nvl(fa.tutor(+),'N') = 'Y' AND NVL(fa1.family_other(+),'N') = 'Y' ";
        SQL = SQL + "AND A.THR_F_PASSPORT_PK = p.pk(+) AND A.THR_F_LABOUR_LICENCE_PK = l.pk(+) AND A.THR_F_VISA_PK = f.pk(+) AND A.THR_F_RESIDENCE_PK = g.pk(+)  ";
        SQL = SQL + "and decode('" + p_nation + "','ALL','ALL',a.NATION )='" + p_nation + "'";
        SQL = SQL + "and (('" + p_search_by + "'='1' and upper(a.FULL_NAME) like  '%' || upper('" + p_search_temp + "') || '%') ";
        SQL = SQL + "         or ('" + p_search_by + "'='2' and upper(a.emp_id) like  '%' || upper('" + p_search_temp + "') || '%') ";
        SQL = SQL + "         or ('" + p_search_by + "'='3' and upper(a.id_num) like  '%' || upper('" + p_search_temp + "') || '%') ";
        SQL = SQL + "         or ('" + p_search_by + "'='4' and upper(a.PERSON_ID ) like  '%' || upper('" + p_search_temp + "') || '%') ";
        SQL = SQL + "         or ('" + p_search_by + "'='5' and upper(a.OLD_ID ) like  '%' || upper('" + p_search_temp + "') || '%')) ";
        SQL = SQL + "and (('" + p_from_joindate + "' is null and '" + p_to_joindate + "' is null)  ";
        SQL = SQL + "         or (a.JOIN_DT between '" + p_from_joindate + "' and '" + p_to_joindate + "') ) ";
        SQL = SQL + "and decode('" + p_salary_yn + "','ALL','ALL',a.SALARY_YN )='" + p_salary_yn + "'";
        SQL = SQL + "and decode('" + p_position + "','ALL','ALL',a.POS_TYPE )='" + p_position + "'";
        SQL = SQL + "and decode('" + p_pay_type + "','ALL','ALL',a.PAY_TYPE )='" + p_pay_type + "'";
        SQL = SQL + "and decode('" + p_contract_kind + "','ALL','ALL',a.CONTRACT_TYPE )='" + p_contract_kind + "'";
        SQL = SQL + "and (('" + p_from_contract + "' is null and '" + p_to_contract + "' is null)  ";
        SQL = SQL + "    or (a.BEGIN_CONTRACT between '" + p_from_contract + "' and '" + p_to_contract + "') ) ";
        SQL = SQL + "and decode('" + p_sex + "','ALL','ALL',a.SEX )='" + p_sex + "' ";
        SQL = SQL + "and decode('" + p_insurance_yn + "','ALL','ALL',a.SOCIAL_YN )='" + p_insurance_yn + "'";
        SQL = SQL + "and decode('" + p_status + "','ALL','ALL',a.status)='" + p_status + "'";
        SQL = SQL + "and (('" + p_from_leftdate + "' is null and '" + p_to_leftdate + "' is null)  ";
        SQL = SQL + "    or (a.LEFT_DT between '" + p_from_leftdate + "' and '" + p_to_leftdate + "') ) ";
        SQL = SQL + " and (('" + p_from_birthdate + "' is null and '" + p_to_birthdate + "' is null)  ";
        SQL = SQL + "    or (decode(length(a.BIRTH_DT),4,a.birth_dt || '0101',a.birth_dt) between '" + p_from_birthdate + "' and '" + p_to_birthdate + "') ) ";
        SQL = SQL + "and decode('" + p_ot_yn + "','ALL','ALL',a.ot_yn)='" + p_ot_yn + "'";
        SQL = SQL + "and decode('" + p_thr_group_pk + "','ALL','ALL',d.pk)='" + p_thr_group_pk + "'";
        SQL = SQL + "and decode('" + p_union + "','ALL','ALL',a.union_yn)='" + p_union + "'";
        SQL = SQL + "AND (   a.tco_org_pk IN ( ";
        SQL = SQL + "                         SELECT     g.pk ";
        SQL = SQL + "                               FROM tco_org g ";
        SQL = SQL + "                              WHERE g.del_if = 0 ";
        SQL = SQL + "                         START WITH g.pk = ";
        SQL = SQL + "                                       DECODE ('" + p_tco_dept_pk + "', ";
        SQL = SQL + "                                               'ALL', 0, ";
        SQL = SQL + "                                               '" + p_tco_dept_pk + "'";
        SQL = SQL + "                                              ) ";
        SQL = SQL + "                         CONNECT BY PRIOR g.pk = g.p_pk) ";
        SQL = SQL + "                   OR '" + p_tco_dept_pk + "' = 'ALL') ";
        SQL = SQL + "and decode('" + p_job + "','ALL','ALL',A.JOB_TYPE)='" + p_job + "'";
        SQL = SQL + "and decode('" + p_education + "','ALL','ALL',A.EDU_TYPE)='" + p_education + "'";
        SQL = SQL + "and ('" + p_birth_month + "'= 'ALL' or (LENGTH(A.BIRTH_DT)=8 and substr(A.BIRTH_DT,5,2)= '" + p_birth_month + "')) ";
        SQL = SQL + "ORDER BY NVL (c.seq, 99999), " +
              "         (    SELECT MAX (T.org_nm) " +
              "                FROM tco_org t " +
              "               WHERE t.del_if = 0 AND T.ORG_TYPE IN ('03') " +
              "          START WITH t.pk = A.TCO_ORG_PK " +
              "          CONNECT BY PRIOR t.p_pk = t.pk), " +
              "         (    SELECT MAX (T.org_nm) " +
              "                FROM tco_org t " +
              "               WHERE t.del_if = 0 AND T.ORG_TYPE IN ('06') " +
              "          START WITH t.pk = A.TCO_ORG_PK " +
              "          CONNECT BY PRIOR t.p_pk = t.pk), " +
              "         (SELECT V.NUM_1 " +
              "            FROM VHR_HR_CODE V " +
              "           WHERE V.ID = 'HR0008' AND V.CODE = A.POS_TYPE), " +
              "         a.join_dt, " +
              "         a.EMP_ID ";


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

        DataTable dt_emp = ESysLib.TableReadOpen(SQL);



        SQL
            = "select to_char(sysdate,'dd/mm/yyyy') from dual ";
        DataTable dt_sysdt = ESysLib.TableReadOpen(SQL);


        string[] s = s_name.Split('|');
        //Insert column
        for (i = 3; i < s.Length - 1; i++)
        {
            exSheet.Range[1, 5, 1, 5].Columns.EntireColumn.Insert();
        }

        for (i = 0; i < s.Length - 1; i++)
        {
            exSheet.Cells[5, 2 + i].Value = s[i];
        }
        //Header
        exSheet.Range[1, 1, 1, s.Length].Merge();
        if (p_lang == "ENG")
        {
            exSheet.Cells[1, 1].Value = "LIST OF EMPLOYEES";
            exSheet.Cells[2, 2].Value = "Reporter";
            exSheet.Cells[3, 2].Value = "Report Date";
        }
        else
        {
            exSheet.Cells[1, 1].Value = "DANH SÁCH THÔNG TIN NHÂN VIÊN";
            exSheet.Cells[2, 2].Value = "Người báo cáo";
            exSheet.Cells[3, 2].Value = "Ngày báo cáo";
        }

        exSheet.Cells[2, 3].Value = Session["User_ID"].ToString();
        exSheet.Cells[3, 3].Value = dt_sysdt.Rows[0][0].ToString();

        //Insert Row
        for (i = 0; i < dt_emp.Rows.Count - 1; i++)
        {
            if (pic != 1000)
            {
                exSheet.Range[6, 1].Rows.RowHeight = 70;
            }

            exSheet.Range[7, 1, 7, 1].Rows.EntireRow.Insert();
            //exSheet.Range[4, 1, 5,11].Copy(exSheet.Range[6,1,7,11], XlPasteType.xlPasteAll);
        }

        for (i = 0; i < dt_emp.Rows.Count; i++)
        {
            exSheet.Cells[6 + i, 1].Value = i + 1;
            for (j = 2; j <= s.Length; j++)
            {
                //if (IsNumber(dt_emp.Rows[i][j - 2].ToString()) == false || int.Parse(p[j-2].ToString()) <= 14 )
                if (j - 2 == pic && pic != 1000)
                {
                    exSheet.Cells[6 + i, j].Select();
                    if (dt_emp.Rows[i][j - 2].ToString() != "")
                    {
                        byte[] MyData = new byte[0];
                        MyData = (byte[])dt_emp.Rows[i][j - 2];
                        MemoryStream stream = new MemoryStream(MyData);

                        Bitmap b = new Bitmap(stream);

                        exSheet.Shapes.AddPicture(ResizeImage(b, 105, 93));
                    }
                }

                exSheet.Cells[6 + i, j].Value = dt_emp.Rows[i][j - 2].ToString();
                //else
                //{
                //    exSheet.Cells[6 + i, j].NumberFormat = "_(* #,##0_);_(* (#,##0);_(* " + "-" + "_);_(@_)";
                //    exSheet.Cells[6 + i, j].Value = Double.Parse(dt_emp.Rows[i][j - 2].ToString());
                //}
            }
        }

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

        //Xoa column theo security
        int tmp = 0, tmp1 = 0;

        if (p_sal_security == "N")
        {
            for (i = 0; i < s.Length - 1; i++)
            {
                if (int.Parse(p[i].ToString()) >= 44 && int.Parse(p[i].ToString()) <= 53)
                {
                    tmp++;
                    if (tmp1 == 0)
                    {
                        tmp1 = i;
                    }
                }
            }
            tmp1 = tmp1 + 2;        //trong report bat dau bang colunm thu 2
            for (i = 0; i < tmp; i++)
            {
                exSheet.Range[1, tmp1].Columns.EntireColumn.Delete();
            }
        }

        exSheet.Cells.Columns.Autofit();


        exBook.SaveAs(TempFile);
        //ESysLib.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();
    }