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

        string TemplateFile = "rpt_bisc00171_ST03.xls";
        string TempFile     = "../../../../system/temp/rpt_bisc00171_ST03_" + 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_lang          = Request.QueryString["p_lang"];
        string p_wh_name       = Request.QueryString["p_wh_name"];
        string p_wh_type       = Request.QueryString["p_wh_type"];
        string p_wh_pk         = Request.QueryString["p_wh_pk"];
        string p_item_group_pk = Request.QueryString["p_item_group_pk"];
        string p_item_group_nm = Request.QueryString["p_item_group_nm"];
        string p_item          = Request.QueryString["p_item"];
        string p_date          = Request.QueryString["p_date"];



        string SQL_WH
            = "select b.wh_type a0,wh_t.code_nm a1" +
              " from tlg_lg_wh_item_group a,tlg_in_warehouse b,tlg_it_itemgrp c " +
              ",(SELECT   a.code, a.code_nm FROM tlg_lg_code a WHERE a.del_if = 0 AND tlg_lg_code_group_pk = '14') wh_t " +
              "where a.del_if=0 and b.del_if=0 and c.del_if=0 " +
              "and A.TLG_IN_WAREHOUSE_PK=b.pk " +
              "and A.TLG_IT_ITEMGRP_PK=c.pk " +
              "and c.pk='" + p_item_group_pk + "' " +
              "and (b.wh_type = '" + p_wh_type + "' or '" + p_wh_type + "' is null)" +
              "and wh_t.code=b.wh_type " +
              "group by b.wh_type,wh_t.code_nm order by a0,a1";


        DataTable dt_WH    = CtlLib.TableReadOpen(SQL_WH);
        int       i_row_wh = dt_WH.Rows.Count;

        if (i_row_wh == 0)
        {
            Response.Write("There is no data of warehouse type");
            Response.End();
        }
        string SQL
            = "select max(t.item_code) a0 " +
              ",max(t.item_name) a1 " +
              ",lg_f_get_spec_nm_by_seq(a1.tlg_it_item_pk,1) " +
              ",lg_f_get_spec_nm_by_seq(a1.tlg_it_item_pk,2) " +
              ",lg_f_get_spec_nm_by_seq(a1.tlg_it_item_pk,3) " +
              ",(select sum(g.min_qty) from TLG_IT_SECURE g where g.del_if=0 and g.tlg_it_item_pk=a1.tlg_it_item_pk and (TLG_IN_WAREHOUSE_PK='" + p_wh_pk + "' or '" + p_wh_pk + "' is null ) ) ";

        for (int i = 0; i < dt_WH.Rows.Count; i++)
        {
            SQL = SQL + "                               ,sum(decode(a1.wh_type,'" + dt_WH.Rows[i][0].ToString() + "',a1.end_qty)) wh_" + dt_WH.Rows[i][0].ToString();
        }
        SQL = SQL +
              "                               from ( " +
              "                                 select wh.wh_type " +
              "                                ,wh.pk " +
              "                                ,tr.tlg_it_item_pk " +
              "                                ,tr.end_qty " +
              "                                    from (SELECT tlg_in_warehouse_pk, " +
              "                                           tlg_it_item_pk, " +
              "                                           nvl(SUM (begin_qty),0) + nvl(SUM (in_qty),0) - nvl(SUM (out_qty),0) end_qty " +
              "                                      FROM (SELECT a.tlg_in_warehouse_pk, " +
              "                                                   b.tlg_it_item_pk, " +
              "                                                   b.end_qty begin_qty, " +
              "                                                   NULL in_qty, " +
              "                                                   NULL out_qty " +
              "                                              FROM tlg_sa_stock_closing_m a, " +
              "                                                   tlg_sa_stock_closing_d b,tlg_it_item c " +
              "                                             WHERE     a.del_if = 0 and c.del_if=0 and b.tlg_it_item_pk=c.pk" +
              "                                                   AND (a.tlg_in_warehouse_pk, " +
              "                                                        a.stock_date) IN " +
              "                                                          (  SELECT b.tlg_in_warehouse_pk, " +
              "                                                                    MAX ( " +
              "                                                                       b.stock_date) " +
              "                                                               FROM tlg_sa_stock_closing_m b " +
              "                                                              WHERE     b.del_if = 0 " +
              "                                                                    AND b.stock_date < " +
              "                                                                           '" + p_date + "' " +
              "                                                           GROUP BY b.tlg_in_warehouse_pk) " +
              "                                                   AND b.del_if = 0 " +
              "                                                   AND a.pk = " +
              "                                                          b.tlg_sa_stock_closing_m_pk " +
              "													and (c.tlg_it_itemgrp_pk = '"+ p_item_group_pk + "' or '" + p_item_group_pk + "' is null) " +
              "                                                   AND NVL (b.end_qty, 0) <> 0 " +
              "                                            UNION ALL " +
              "                                            SELECT a.tlg_in_warehouse_pk, " +
              "                                                   a.tlg_it_item_pk, " +
              "                                                   NULL begin_qty, " +
              "                                                   a.input_qty in_qty, " +
              "                                                   a.output_qty out_qty " +
              "                                              FROM tlg_in_stocktr a,tlg_it_item b " +
              "                                             WHERE     a.del_if = 0 and b.del_if=0 and a.tlg_it_item_pk=b.pk " +
              "                                                   AND a.tr_date > " +
              "                                                          NVL ( " +
              "                                                             (SELECT MAX ( " +
              "                                                                        b.stock_date) " +
              "                                                                FROM tlg_sa_stock_closing_m b " +
              "                                                               WHERE     b.del_if = " +
              "                                                                            0 " +
              "                                                                     AND b.stock_date < " +
              "                                                                            '" + p_date + "' " +
              "                                                                     AND b.tlg_in_warehouse_pk = " +
              "                                                                            a.tlg_in_warehouse_pk), " +
              "                                                             '0') " +
              "													and (b.tlg_it_itemgrp_pk = '"+ p_item_group_pk + "' or '" + p_item_group_pk + "' is null) " +
              "                                                   AND a.tr_date <= '" + p_date + "')  " +
              "                                  GROUP BY tlg_in_warehouse_pk, " +
              "                                           tlg_it_item_pk ) tr,tlg_in_warehouse wh " +
              "                                  where wh.del_if=0  " +
              "								   and (wh.wh_type ='"+ p_wh_type + "' or '" + p_wh_type + "' is null) " +
              "								   and (wh.pk ='"+ p_wh_pk + "' or '" + p_wh_pk + "' is null) " +
              "                                  and tr.tlg_in_warehouse_pk =wh.pk)a1,tlg_it_item t  " +
              "                                  where t.del_if=0 and a1.tlg_it_item_pk=t.pk  " +
              "                            group by a1.tlg_it_item_pk order by a0     " +
              "                                   ";
        //Response.Write(SQL);
        //Response.End();
        DataTable dt_Detail = CtlLib.TableReadOpen(SQL);

        int i_row = dt_Detail.Rows.Count;
        int i_col = dt_Detail.Columns.Count;

        if (i_row == 0)
        {
            Response.Write("There is no data!");
            Response.End();
        }
        exSheet.Cells["E3"].Value = p_item_group_nm;
        exSheet.Range["A1:A" + (i_col + 2)].Columns.Merge();
        //exSheet.Cells["A1"].Value = "END STOCK BY ITEM GROUP";
        exSheet.Cells["E5"].Value = p_date.Substring(6, 2) + "/" + p_date.Substring(4, 2) + "/" + p_date.Substring(0, 4);
        //---------

        ////end-set-----DataBind--in---Master---
        int i_header_row = 7;
        int i_header_col = 8;
        int i_b_row      = 9;

        Double[] grant_total = new Double[i_col + 1];
        Double   sub_total   = 0;

        //-------------bind header--------------
        for (int i = 0; i < i_row_wh; i++)
        {
            if (i != 0)
            {
                exSheet.Range[i_header_row, i_header_col + i, i_header_row, i_header_col + i].Columns.EntireColumn.Insert();           //insert row new of sheet
            }
            exSheet.Range[i_header_row, i_header_col + i, i_header_row, i_header_col + i].Value = dt_WH.Rows[i][1].ToString();
        }
        //insert new row
        for (int i = 1; i < i_row; i++)
        {
            exSheet.Range["A" + (i_b_row + i)].EntireRow.Insert();
        }
        for (int i = 0; i < i_col + 1; i++)
        {
            grant_total[i] = 0;
        }
        for (int i = 0; i < i_row; i++)
        {
            sub_total = 0;
            exSheet.Range[i_b_row + i, 1, i_b_row + i, 1].Value = i + 1;
            for (int j = 0; j < i_col; j++)
            {
                if (j <= 5)
                {
                    exSheet.Range[i_b_row + i, 2 + j, i_b_row + i, j + 2].Value = dt_Detail.Rows[i][j].ToString();
                }
                else
                {
                    if (dt_Detail.Rows[i][j].ToString() != "")
                    {
                        exSheet.Range[i_b_row + i, 2 + j, i_b_row + i, j + 2].Value = Double.Parse(dt_Detail.Rows[i][j].ToString());
                        sub_total      += Double.Parse(dt_Detail.Rows[i][j].ToString());
                        grant_total[j] += Double.Parse(dt_Detail.Rows[i][j].ToString());
                    }
                }
            }
            exSheet.Range[i_b_row + i, 2 + i_col, i_b_row + i, 2 + i_col].Value = sub_total;
            grant_total[i_col] += sub_total;
            if (sub_total != 0 && dt_Detail.Rows[i][5].ToString() != "")
            {
                exSheet.Range[i_b_row + i, 3 + i_col, i_b_row + i, 3 + i_col].Value = Double.Parse(dt_Detail.Rows[i][5].ToString()) / sub_total;
            }
            else
            {
                exSheet.Range[i_b_row + i, 3 + i_col, i_b_row + i, 3 + i_col].Value = 0;
            }
        }
        for (int j = 6; j < i_col + 1; j++)
        {
            exSheet.Range[i_b_row + i_row, 2 + j, i_b_row + i_row, j + 2].Value = grant_total[j];
        }
        // 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

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = true;*/

        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();
    }
예제 #2
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        string l_dtfrm = Request["p_date"];

        string TemplateFile = "rpt_60300010_Nigh_Audit_daily_USD.xls";
        string TempFile     = "../../../../system/temp/rpt_60300010_Nigh_Audit_daily_USD_" + 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 = "", date_para = "";
        DataTable dt, dtAdjPoint;

        para = "'" + l_dtfrm + "'";

        dt         = CtlLib.TableReadOpenCursor("rpt_60300010_rev_daily_vnd", para);
        dtAdjPoint = CtlLib.TableReadOpenCursor("rpt_60300010_adjust_by_point", para);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        date_para = "Date :" + l_dtfrm.Substring(6, 2) + "/" + l_dtfrm.Substring(4, 2) + "/" + l_dtfrm.Substring(0, 4);
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[3, 3].Value = "There is not data!";
        }
        if (dt.Rows.Count > 0)
        {
            exSheet.Cells[2, 8].Value = "Print Time :           " + dt.Rows[0]["print_time"].ToString();
            exSheet.Cells[3, 1].Value = "" + date_para;
            exSheet.Cells[3, 4].Value = int.Parse(dt.Rows[0][11].ToString());
            exSheet.Cells[3, 6].Value = int.Parse(dt.Rows[0][12].ToString());
            exSheet.Cells[3, 8].Value = int.Parse(dt.Rows[0][13].ToString());

            for (int i = 0; i <= 41; i++)
            {
                exSheet.Cells[i + 7, 3].Value  = double.Parse(dt.Rows[i]["qty_today"].ToString());            //#  today
                exSheet.Cells[i + 7, 4].Value  = double.Parse(dt.Rows[i]["supply_today"].ToString());         //Supply
                exSheet.Cells[i + 7, 5].Value  = double.Parse(dt.Rows[i]["svc_today"].ToString());            //Service charge
                exSheet.Cells[i + 7, 6].Value  = double.Parse(dt.Rows[i]["vat_today"].ToString());            //vat
                exSheet.Cells[i + 7, 7].Value  = double.Parse(dt.Rows[i]["total_today"].ToString());          //total
                exSheet.Cells[i + 7, 8].Value  = double.Parse(dt.Rows[i]["qty_mtd"].ToString());              //# month
                exSheet.Cells[i + 7, 9].Value  = double.Parse(dt.Rows[i]["supply_mtd"].ToString());           //supply
                exSheet.Cells[i + 7, 10].Value = double.Parse(dt.Rows[i]["svc_mtd"].ToString());              //Service charge
                exSheet.Cells[i + 7, 11].Value = double.Parse(dt.Rows[i]["vat_mtd"].ToString());              //vat
                exSheet.Cells[i + 7, 12].Value = double.Parse(dt.Rows[i]["total_mtd"].ToString());            //total
                exSheet.Cells[i + 7, 13].Value = double.Parse(dt.Rows[i]["qty_ytd"].ToString());              //# year
                exSheet.Cells[i + 7, 14].Value = double.Parse(dt.Rows[i]["supply_ytd"].ToString());           //supply
                exSheet.Cells[i + 7, 15].Value = double.Parse(dt.Rows[i]["svc_ytd"].ToString());              //Service charge
                exSheet.Cells[i + 7, 16].Value = double.Parse(dt.Rows[i]["vat_ytd"].ToString());              //vat
                exSheet.Cells[i + 7, 17].Value = double.Parse(dt.Rows[i]["total_ytd"].ToString());            //total
            }
        }
        for (int l_addrow = 1; l_addrow < dtAdjPoint.Rows.Count; l_addrow++)
        {
            exSheet.Range["A52"].Rows.EntireRow.Insert();//insert row new of sheet
        }
        if (dtAdjPoint.Rows.Count > 0)
        {
            for (int l_row = 1; l_row <= dtAdjPoint.Rows.Count; l_row++)
            {
                for (int col = 2; col <= 3; col++)
                {
                    if (col == 2)
                    {
                        exSheet.Cells[50 + l_row, col].Value = dtAdjPoint.Rows[l_row - 1]["point_name"].ToString();
                        exSheet.Cells[l_row + 50, col].HorizontalAlignment = XlHAlign.xlHAlignLeft;
                        //exSheet.Range[5, col + 1, l_row + 5, 7].Font.Bold = false;
                    }
                    else
                    {
                        exSheet.Cells[50 + l_row, col].Value = double.Parse(dtAdjPoint.Rows[l_row - 1]["amount"].ToString());
                        exSheet.Cells[50, col, l_row + 50, col].NumberFormat = "#,##0.00";
                        //exSheet.Cells[49, col, l_row + 49, col].Font.Color = Color.Red;
                        exSheet.Cells[l_row + 50, col].HorizontalAlignment = XlHAlign.xlHAlignRight;

                        if (double.Parse(dtAdjPoint.Rows[l_row - 1][1].ToString()) == 0.00)
                        {
                            exSheet.Cells[50 + l_row, 3].Value = "";
                        }
                    }
                }
            }
        }
        //----------------------
        ////end-set-----DataBind--in---Master---


        //set date title of year
        //exSheet.Cells[2, 1].Value = "Income Statement Year" + " " + l_year;

        //set data to detail.
        // loop detail not percent


        // 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 l_wh_pk    = Request["p_wh_pk"];
        string l_group_pk = Request["p_group_pk"];
        string l_item     = Request["p_item"];
        string l_date     = Request["p_date"];

        string TemplateFile = "rpt_dscd00120_ST02.xls";

        string TempFile = "../../../../system/temp/rpt_dscd00120_ST02_" + 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 l_parameter = "'" + l_group_pk + "'";

        l_parameter = l_parameter + ",'" + l_group_pk + "'";
        l_parameter = l_parameter + ",'" + l_item + "'";
        l_parameter = l_parameter + ",'" + l_date + "'";

        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00120_ST01_2", l_parameter);
        DataTable dtHead   = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00120_ST02_head", l_date);


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

        exSheet.Cells[3, 6].Value  = dtHead.Rows[0][0].ToString();
        exSheet.Cells[3, 7].Value  = dtHead.Rows[0][1].ToString();
        exSheet.Cells[3, 8].Value  = dtHead.Rows[0][2].ToString();
        exSheet.Cells[3, 9].Value  = dtHead.Rows[0][3].ToString();
        exSheet.Cells[3, 10].Value = dtHead.Rows[0][4].ToString();
        exSheet.Cells[3, 11].Value = dtHead.Rows[0][5].ToString();
        exSheet.Cells[3, 12].Value = dtHead.Rows[0][6].ToString();
        exSheet.Cells[3, 13].Value = dtHead.Rows[0][7].ToString();
        exSheet.Cells[3, 14].Value = dtHead.Rows[0][8].ToString();
        exSheet.Cells[3, 15].Value = dtHead.Rows[0][9].ToString();
        exSheet.Cells[3, 16].Value = dtHead.Rows[0][10].ToString();
        exSheet.Cells[3, 17].Value = dtHead.Rows[0][11].ToString();
        exSheet.Cells[3, 18].Value = dtHead.Rows[0][12].ToString();
        exSheet.Cells[3, 19].Value = dtHead.Rows[0][13].ToString();
        exSheet.Cells[3, 20].Value = dtHead.Rows[0][14].ToString();
        exSheet.Cells[3, 21].Value = dtHead.Rows[0][15].ToString();
        exSheet.Cells[3, 22].Value = dtHead.Rows[0][16].ToString();
        exSheet.Cells[3, 23].Value = dtHead.Rows[0][17].ToString();
        exSheet.Cells[3, 24].Value = dtHead.Rows[0][18].ToString();
        exSheet.Cells[3, 25].Value = dtHead.Rows[0][19].ToString();
        exSheet.Cells[3, 26].Value = dtHead.Rows[0][20].ToString();
        exSheet.Cells[3, 27].Value = dtHead.Rows[0][21].ToString();
        exSheet.Cells[3, 28].Value = dtHead.Rows[0][22].ToString();
        exSheet.Cells[3, 29].Value = dtHead.Rows[0][23].ToString();
        exSheet.Cells[3, 30].Value = dtHead.Rows[0][24].ToString();
        exSheet.Cells[3, 31].Value = dtHead.Rows[0][25].ToString();
        exSheet.Cells[3, 32].Value = dtHead.Rows[0][26].ToString();
        exSheet.Cells[3, 33].Value = dtHead.Rows[0][27].ToString();
        exSheet.Cells[3, 34].Value = dtHead.Rows[0][28].ToString();
        exSheet.Cells[3, 35].Value = dtHead.Rows[0][29].ToString();
        exSheet.Cells[3, 36].Value = dtHead.Rows[0][30].ToString();

        int l_row = 4;

        int i;

        for (i = 0; i < dtDetail.Rows.Count; i++)
        {
            exSheet.Cells[l_row + i, 1].Value  = dtDetail.Rows[i]["item_seq"];
            exSheet.Cells[l_row + i, 2].Value  = dtDetail.Rows[i]["item_code"];
            exSheet.Cells[l_row + i, 3].Value  = dtDetail.Rows[i]["item_name"];
            exSheet.Cells[l_row + i, 4].Value  = "";
            exSheet.Cells[l_row + i, 5].Value  = dtDetail.Rows[i]["total_qty"];
            exSheet.Cells[l_row + i, 6].Value  = dtDetail.Rows[i]["day00_qty"];
            exSheet.Cells[l_row + i, 7].Value  = dtDetail.Rows[i]["day01_qty"];
            exSheet.Cells[l_row + i, 8].Value  = dtDetail.Rows[i]["day02_qty"];
            exSheet.Cells[l_row + i, 9].Value  = dtDetail.Rows[i]["day03_qty"];
            exSheet.Cells[l_row + i, 10].Value = dtDetail.Rows[i]["day04_qty"];
            exSheet.Cells[l_row + i, 11].Value = dtDetail.Rows[i]["day05_qty"];
            exSheet.Cells[l_row + i, 12].Value = dtDetail.Rows[i]["day06_qty"];
            exSheet.Cells[l_row + i, 13].Value = dtDetail.Rows[i]["day07_qty"];
            exSheet.Cells[l_row + i, 14].Value = dtDetail.Rows[i]["day08_qty"];
            exSheet.Cells[l_row + i, 15].Value = dtDetail.Rows[i]["day09_qty"];
            exSheet.Cells[l_row + i, 16].Value = dtDetail.Rows[i]["day10_qty"];
            exSheet.Cells[l_row + i, 17].Value = dtDetail.Rows[i]["day11_qty"];
            exSheet.Cells[l_row + i, 18].Value = dtDetail.Rows[i]["day12_qty"];
            exSheet.Cells[l_row + i, 19].Value = dtDetail.Rows[i]["day13_qty"];
            exSheet.Cells[l_row + i, 20].Value = dtDetail.Rows[i]["day14_qty"];
            exSheet.Cells[l_row + i, 21].Value = dtDetail.Rows[i]["day15_qty"];
            exSheet.Cells[l_row + i, 22].Value = dtDetail.Rows[i]["day16_qty"];
            exSheet.Cells[l_row + i, 23].Value = dtDetail.Rows[i]["day17_qty"];
            exSheet.Cells[l_row + i, 24].Value = dtDetail.Rows[i]["day18_qty"];
            exSheet.Cells[l_row + i, 25].Value = dtDetail.Rows[i]["day19_qty"];
            exSheet.Cells[l_row + i, 26].Value = dtDetail.Rows[i]["day20_qty"];
            exSheet.Cells[l_row + i, 27].Value = dtDetail.Rows[i]["day21_qty"];
            exSheet.Cells[l_row + i, 28].Value = dtDetail.Rows[i]["day22_qty"];
            exSheet.Cells[l_row + i, 29].Value = dtDetail.Rows[i]["day23_qty"];
            exSheet.Cells[l_row + i, 30].Value = dtDetail.Rows[i]["day24_qty"];
            exSheet.Cells[l_row + i, 31].Value = dtDetail.Rows[i]["day25_qty"];
            exSheet.Cells[l_row + i, 32].Value = dtDetail.Rows[i]["day26_qty"];
            exSheet.Cells[l_row + i, 33].Value = dtDetail.Rows[i]["day27_qty"];
            exSheet.Cells[l_row + i, 34].Value = dtDetail.Rows[i]["day28_qty"];
            exSheet.Cells[l_row + i, 35].Value = dtDetail.Rows[i]["day29_qty"];
            exSheet.Cells[l_row + i, 36].Value = dtDetail.Rows[i]["day30_qty"];
        }

        exSheet.Cells[l_row + i, 5].Value  = "=SUM(E4:E" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 6].Value  = "=SUM(F4:F" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 7].Value  = "=SUM(G4:G" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 8].Value  = "=SUM(H4:H" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 9].Value  = "=SUM(I4:I" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 10].Value = "=SUM(J4:J" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 11].Value = "=SUM(K4:K" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 12].Value = "=SUM(L4:L" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 13].Value = "=SUM(M4:M" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 14].Value = "=SUM(N4:N" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 15].Value = "=SUM(O4:O" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 16].Value = "=SUM(P4:P" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 17].Value = "=SUM(Q4:Q" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 18].Value = "=SUM(R4:R" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 19].Value = "=SUM(S4:S" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 20].Value = "=SUM(T4:T" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 21].Value = "=SUM(U4:U" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 22].Value = "=SUM(V4:V" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 23].Value = "=SUM(W4:W" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 24].Value = "=SUM(X4:X" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 25].Value = "=SUM(Y4:Y" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 26].Value = "=SUM(Z4:Z" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 27].Value = "=SUM(AA4:AA" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 28].Value = "=SUM(AB4:AB" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 29].Value = "=SUM(AC4:AC" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 30].Value = "=SUM(AD4:AD" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 31].Value = "=SUM(AE4:AE" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 32].Value = "=SUM(AF4:AF" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 33].Value = "=SUM(AG4:AG" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 34].Value = "=SUM(AH4:AH" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 35].Value = "=SUM(AI4:AI" + (l_row + i - 1) + ")";
        exSheet.Cells[l_row + i, 36].Value = "=SUM(AJ4:AJ" + (l_row + i - 1) + ")";

        //**************************************************//
        //****************SHEET 2***********************//
        //***************************************************//
        //Add worksheet
        IWorksheet exSheet2 = exBook.Worksheets[2];
        //bind data to excel file
        DataTable dtDetail2 = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00120_ST01_2", l_parameter);


        for (int l_addrow = 1; l_addrow < dtDetail2.Rows.Count; l_addrow++)
        {
            exSheet2.Range["A5"].Rows.EntireRow.Insert();//insert row new of sheet
        }

        exSheet2.Cells[3, 6].Value  = dtHead.Rows[0][0].ToString();
        exSheet2.Cells[3, 7].Value  = dtHead.Rows[0][1].ToString();
        exSheet2.Cells[3, 8].Value  = dtHead.Rows[0][2].ToString();
        exSheet2.Cells[3, 9].Value  = dtHead.Rows[0][3].ToString();
        exSheet2.Cells[3, 10].Value = dtHead.Rows[0][4].ToString();
        exSheet2.Cells[3, 11].Value = dtHead.Rows[0][5].ToString();
        exSheet2.Cells[3, 12].Value = dtHead.Rows[0][6].ToString();
        exSheet2.Cells[3, 13].Value = dtHead.Rows[0][7].ToString();
        exSheet2.Cells[3, 14].Value = dtHead.Rows[0][8].ToString();
        exSheet2.Cells[3, 15].Value = dtHead.Rows[0][9].ToString();
        exSheet2.Cells[3, 16].Value = dtHead.Rows[0][10].ToString();
        exSheet2.Cells[3, 17].Value = dtHead.Rows[0][11].ToString();
        exSheet2.Cells[3, 18].Value = dtHead.Rows[0][12].ToString();
        exSheet2.Cells[3, 19].Value = dtHead.Rows[0][13].ToString();
        exSheet2.Cells[3, 20].Value = dtHead.Rows[0][14].ToString();
        exSheet2.Cells[3, 21].Value = dtHead.Rows[0][15].ToString();
        exSheet2.Cells[3, 22].Value = dtHead.Rows[0][16].ToString();
        exSheet2.Cells[3, 23].Value = dtHead.Rows[0][17].ToString();
        exSheet2.Cells[3, 24].Value = dtHead.Rows[0][18].ToString();
        exSheet2.Cells[3, 25].Value = dtHead.Rows[0][19].ToString();
        exSheet2.Cells[3, 26].Value = dtHead.Rows[0][20].ToString();
        exSheet2.Cells[3, 27].Value = dtHead.Rows[0][21].ToString();
        exSheet2.Cells[3, 28].Value = dtHead.Rows[0][22].ToString();
        exSheet2.Cells[3, 29].Value = dtHead.Rows[0][23].ToString();
        exSheet2.Cells[3, 30].Value = dtHead.Rows[0][24].ToString();
        exSheet2.Cells[3, 31].Value = dtHead.Rows[0][25].ToString();
        exSheet2.Cells[3, 32].Value = dtHead.Rows[0][26].ToString();
        exSheet2.Cells[3, 33].Value = dtHead.Rows[0][27].ToString();
        exSheet2.Cells[3, 34].Value = dtHead.Rows[0][28].ToString();
        exSheet2.Cells[3, 35].Value = dtHead.Rows[0][29].ToString();
        exSheet2.Cells[3, 36].Value = dtHead.Rows[0][30].ToString();
        // loop detail not percent

        l_row = 4;

        for (i = 0; i < dtDetail2.Rows.Count; i++)
        {
            exSheet2.Cells[l_row + i, 1].Value  = dtDetail2.Rows[i]["item_seq"];
            exSheet2.Cells[l_row + i, 2].Value  = dtDetail2.Rows[i]["item_code"];
            exSheet2.Cells[l_row + i, 3].Value  = dtDetail2.Rows[i]["item_name"];
            exSheet2.Cells[l_row + i, 4].Value  = "";
            exSheet2.Cells[l_row + i, 5].Value  = dtDetail2.Rows[i]["total_qty"];
            exSheet2.Cells[l_row + i, 6].Value  = dtDetail2.Rows[i]["day00_qty"];
            exSheet2.Cells[l_row + i, 7].Value  = dtDetail2.Rows[i]["day01_qty"];
            exSheet2.Cells[l_row + i, 8].Value  = dtDetail2.Rows[i]["day02_qty"];
            exSheet2.Cells[l_row + i, 9].Value  = dtDetail2.Rows[i]["day03_qty"];
            exSheet2.Cells[l_row + i, 10].Value = dtDetail2.Rows[i]["day04_qty"];
            exSheet2.Cells[l_row + i, 11].Value = dtDetail2.Rows[i]["day05_qty"];
            exSheet2.Cells[l_row + i, 12].Value = dtDetail2.Rows[i]["day06_qty"];
            exSheet2.Cells[l_row + i, 13].Value = dtDetail2.Rows[i]["day07_qty"];
            exSheet2.Cells[l_row + i, 14].Value = dtDetail2.Rows[i]["day08_qty"];
            exSheet2.Cells[l_row + i, 15].Value = dtDetail2.Rows[i]["day09_qty"];
            exSheet2.Cells[l_row + i, 16].Value = dtDetail2.Rows[i]["day10_qty"];
            exSheet2.Cells[l_row + i, 17].Value = dtDetail2.Rows[i]["day11_qty"];
            exSheet2.Cells[l_row + i, 18].Value = dtDetail2.Rows[i]["day12_qty"];
            exSheet2.Cells[l_row + i, 19].Value = dtDetail2.Rows[i]["day13_qty"];
            exSheet2.Cells[l_row + i, 20].Value = dtDetail2.Rows[i]["day14_qty"];
            exSheet2.Cells[l_row + i, 21].Value = dtDetail2.Rows[i]["day15_qty"];
            exSheet2.Cells[l_row + i, 22].Value = dtDetail2.Rows[i]["day16_qty"];
            exSheet2.Cells[l_row + i, 23].Value = dtDetail2.Rows[i]["day17_qty"];
            exSheet2.Cells[l_row + i, 24].Value = dtDetail2.Rows[i]["day18_qty"];
            exSheet2.Cells[l_row + i, 25].Value = dtDetail2.Rows[i]["day19_qty"];
            exSheet2.Cells[l_row + i, 26].Value = dtDetail2.Rows[i]["day20_qty"];
            exSheet2.Cells[l_row + i, 27].Value = dtDetail2.Rows[i]["day21_qty"];
            exSheet2.Cells[l_row + i, 28].Value = dtDetail2.Rows[i]["day22_qty"];
            exSheet2.Cells[l_row + i, 29].Value = dtDetail2.Rows[i]["day23_qty"];
            exSheet2.Cells[l_row + i, 30].Value = dtDetail2.Rows[i]["day24_qty"];
            exSheet2.Cells[l_row + i, 31].Value = dtDetail2.Rows[i]["day25_qty"];
            exSheet2.Cells[l_row + i, 32].Value = dtDetail2.Rows[i]["day26_qty"];
            exSheet2.Cells[l_row + i, 33].Value = dtDetail2.Rows[i]["day27_qty"];
            exSheet2.Cells[l_row + i, 34].Value = dtDetail2.Rows[i]["day28_qty"];
            exSheet2.Cells[l_row + i, 35].Value = dtDetail2.Rows[i]["day29_qty"];
            exSheet2.Cells[l_row + i, 36].Value = dtDetail2.Rows[i]["day30_qty"];
        }

        //---------
        exSheet2.Cells[l_row + i, 5].Value  = "=SUM(E4:E" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 6].Value  = "=SUM(F4:F" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 7].Value  = "=SUM(G4:G" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 8].Value  = "=SUM(H4:H" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 9].Value  = "=SUM(I4:I" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 10].Value = "=SUM(J4:J" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 11].Value = "=SUM(K4:K" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 12].Value = "=SUM(L4:L" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 13].Value = "=SUM(M4:M" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 14].Value = "=SUM(N4:N" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 15].Value = "=SUM(O4:O" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 16].Value = "=SUM(P4:P" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 17].Value = "=SUM(Q4:Q" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 18].Value = "=SUM(R4:R" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 19].Value = "=SUM(S4:S" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 20].Value = "=SUM(T4:T" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 21].Value = "=SUM(U4:U" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 22].Value = "=SUM(V4:V" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 23].Value = "=SUM(W4:W" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 24].Value = "=SUM(X4:X" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 25].Value = "=SUM(Y4:Y" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 26].Value = "=SUM(Z4:Z" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 27].Value = "=SUM(AA4:AA" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 28].Value = "=SUM(AB4:AB" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 29].Value = "=SUM(AC4:AC" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 30].Value = "=SUM(AD4:AD" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 31].Value = "=SUM(AE4:AE" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 32].Value = "=SUM(AF4:AF" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 33].Value = "=SUM(AG4:AG" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 34].Value = "=SUM(AH4:AH" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 35].Value = "=SUM(AI4:AI" + (l_row + i - 1) + ")";
        exSheet2.Cells[l_row + i, 36].Value = "=SUM(AJ4:AJ" + (l_row + i - 1) + ")";



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

        // font bold header

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = true;*/

        exBook.SaveAs(TempFile);
        //write out to client broswer
        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";
        //Send file string to client
        Response.WriteFile(TempFile);
        //Stop execute
        Response.End();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser("sale");

        string TemplateFile = "rpt_dscd00030_ST01.xls";
        string TempFile     = "../../../../system/temp/rpt_dscd00030_ST01_" + 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_from_date     = Request["p_from_date"];
        string p_to_date       = Request["p_to_date"];
        string p_order_type    = Request["p_order_type"];
        string p_po_vendor     = Request["p_po_vendor"];
        string p_item_group_pk = Request["p_item_group_pk"];
        string p_item          = Request["p_item"];
        string p_type          = Request["p_type"];
        string p_inout_type    = Request["p_inout_type"];
        string p_wh_type       = Request["p_wh_type"];
        string p_wh_pk         = Request["p_wh_pk"];

        string    para     = "'" + p_from_date + "','" + p_to_date + "','" + Session["User_ID"].ToString() + "'";
        DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00030_ST01_1", para);

        if (dtMaster.Rows.Count > 0)
        {
            exSheet.Cells["B2"].Value = dtMaster.Rows[0]["partner_name"];
            exSheet.Cells["H2"].Value = "Tel: " + dtMaster.Rows[0]["phone_no"];

            exSheet.Cells["B3"].Value = dtMaster.Rows[0]["addr1"];
            exSheet.Cells["H3"].Value = "Fax: " + dtMaster.Rows[0]["fax_no"];

            exSheet.Cells["B4"].Value = "Tax No: " + dtMaster.Rows[0]["tax_code"];
            exSheet.Cells["H4"].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy HH:mm");
        }
        exSheet.Cells["A7"].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");

        para = "'" + p_from_date + "','" + p_to_date + "','" + p_order_type + "','" + p_po_vendor + "','" + p_item_group_pk + "','" + p_item + "','" + p_type + "','" + p_inout_type + "','" + p_wh_type + "','" + p_wh_pk + "'";
        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00030_ST01_2", para);

        for (int r = 0; r < dtDetail.Rows.Count; r++)
        {
            if (r < dtDetail.Rows.Count - 2)
            {
                exSheet.Range["A" + (10 + r)].Rows.EntireRow.Insert();
            }
            for (int c = 0; c < dtDetail.Columns.Count; c++)
            {
                exSheet.Cells[9 + r, c + 1].Value = dtDetail.Rows[r][c];
            }
        }
        // end loop detail not percent
        if (dtDetail.Rows.Count > 0)
        {
            exSheet.Cells["G" + (dtDetail.Rows.Count + 9)].Value = "=SUM(G9:G" + (dtDetail.Rows.Count + 8) + ")";
        }
        // 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

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = 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(pdfFilePath);
        Response.WriteFile(TempFile);
        //Stop execute
        Response.End();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        string l_dtfrom = Request["p_dtfrm"];
        string l_dtto   = Request["p_dtto"];

        string TemplateFile = "rpt_60300010_cancel_noshow.xls";
        string TempFile     = "../../../../system/temp/rpt_60300010_cancel_noshow_" + 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_dtfrom + "','" + l_dtto + "'";
        dt   = CtlLib.TableReadOpenCursor("rpt_60300010_cancel_noshow", para);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[6, 3].Value = "There is not data!";
        }
        if (dt.Rows.Count > 0)
        {
            exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString();
            exSheet.Cells[3, 9].Value = dt.Rows[0]["print_time"].ToString();
        }

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

        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range["A6"].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_first = 5;

        if (dt.Rows.Count > 0)
        {
            int l_cnt = dt.Rows.Count;
            for (int l_row = 0; l_row < dt.Rows.Count; l_row++)
            {
                exSheet.Cells[l_first + l_row, 1].Value = dt.Rows[l_row]["SLIP_NO"].ToString();
                exSheet.Cells[l_first + l_row, 2].Value = dt.Rows[l_row]["LAST_NAME"].ToString();
                exSheet.Cells[l_first + l_row, 3].Value = dt.Rows[l_row]["company"].ToString();
                exSheet.Cells[l_first + l_row, 4].Value = dt.Rows[l_row]["arrive_date"].ToString();
                exSheet.Cells[l_first + l_row, 5].Value = dt.Rows[l_row]["departure"].ToString();
                //exSheet.Cells[l_first + l_row, 6].Value = dt.Rows[l_row]["departure"].ToString(); total guest
                exSheet.Cells[l_first + l_row, 7].Value  = dt.Rows[l_row]["room_type"].ToString();
                exSheet.Cells[l_first + l_row, 8].Value  = dt.Rows[l_row]["room_no"].ToString();
                exSheet.Cells[l_first + l_row, 9].Value  = dt.Rows[l_row]["status"].ToString();
                exSheet.Cells[l_first + l_row, 10].Value = dt.Rows[l_row]["description"].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);
        //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());
        //CtlLib.SetUser("SALE");

        string TemplateFile = "rpt_dsbs00200_YJ01.xls";
        string TempFile     = "../../../../system/temp/rpt_dsbs00200_YJ01_" + 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_parent_pk = Request.QueryString["p_parent_pk"];
        string para        = "'" + p_parent_pk + "'";

        DataTable dt = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00200_YJ01_2", para);

        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00200_YJ01_3", para);


        if (dt.Rows.Count > 0)
        {
            exSheet.Cells["H3"].Value = dt.Rows[0]["slip_no"];
            exSheet.Cells["B6"].Value = dt.Rows[0]["partner_name"];
            exSheet.Cells["B7"].Value = dt.Rows[0]["addr1"];
            exSheet.Cells["B8"].Value = dt.Rows[0]["dest_nation"];
            exSheet.Cells["F6"].Value = dt.Rows[0]["po_no"];
            exSheet.Cells["F7"].Value = dt.Rows[0]["po_date"];
            exSheet.Cells["F8"].Value = dt.Rows[0]["ETD"];            //ship date

            exSheet.Cells["B14"].Value = dt.Rows[0]["PRODUCTION_MARKS"];
            exSheet.Cells["B20"].Value = dt.Rows[0]["MARKING_MARKS"];
            exSheet.Cells["B26"].Value = dt.Rows[0]["FINISHING_MARKS"];
            exSheet.Cells["B32"].Value = dt.Rows[0]["PACKING_MARKS"];
            exSheet.Cells["B38"].Value = dt.Rows[0]["SHIPPING_MARKS"];             //Finish:
        }
        // INSERT ROWS
        for (int l_addrow = 0; l_addrow < dtDetail.Rows.Count - 1; l_addrow++)
        {
            exSheet.Range["A12"].Rows.EntireRow.Insert();//insert row new of sheet
        }

        // SET DATA
        int pos = 11;
        int i   = 0;

        //Color color1 = ColorTranslator.FromHtml("0xCCFFFF");
        //Color color2 = ColorTranslator.FromHtml("0xFFFFCC");

        for (i = 0; i < dtDetail.Rows.Count; i++)
        {
            exSheet.Cells[pos + i, 1].Value = dtDetail.Rows[i]["pattern"];
            exSheet.Cells[pos + i, 2].Value = dtDetail.Rows[i]["prod_code"];
            exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["item_code"];
            exSheet.Cells[pos + i, 4].Value = dtDetail.Rows[i]["description"];
            exSheet.Cells[pos + i, 5].Value = dtDetail.Rows[i]["ansi"];
            exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["gauge"];
            exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["finishing"];
            exSheet.Cells[pos + i, 8].Value = dtDetail.Rows[i]["ord_qty"];
            //-------------
        }

        int t = pos + i - 1;

        exSheet.Cells[pos + i, 8].Value = "=sum(H11:H" + t + ")";

        //---------
        if (File.Exists(TempFile))
        {
            File.Delete(TempFile);
        }
        // hide row firts
        range = exSheet.Range["A1"];
        // hide row A5
        // range.Rows.Hidden = true;
        // font bold header

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = true;*/

        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();
    }
예제 #7
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        string l_monthly = Request["p_monthly"];


        string TemplateFile = "rpt_60300010_consolidate.xls";
        string TempFile     = "../../../../system/temp/rpt_60300010_consolidate_" + 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_monthly + "'";
        dt   = CtlLib.TableReadOpenCursor("rpt_60300010_division_report", para);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[6, 3].Value = "There is not data!";
        }
        if (dt.Rows.Count > 0)
        {
            //	exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString();
            //exSheet.Cells[3, 9].Value = dt.Rows[0]["print_time"].ToString();
        }

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

        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range["A7"].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;

        if (dt.Rows.Count > 0)
        {
            int l_cnt = dt.Rows.Count;
            for (int l_row = 1; l_row <= dt.Rows.Count; l_row++)
            {
                for (int col = 0; col <= 9; col++)
                {
                    if (col == 0)
                    {
                        exSheet.Cells[5 + l_row, 1].Value = dt.Rows[l_row - 1][0].ToString();
                    }
                    if (col == 1)
                    {
                        exSheet.Cells[5 + l_row, 2].Value = dt.Rows[l_row - 1][1].ToString();
                    }
                    if (col == 2)
                    {
                        if (dt.Rows[l_row - 1][2].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 3].Value = double.Parse(dt.Rows[l_row - 1][2].ToString());
                        }
                    }
                    if (col == 3)
                    {
                        exSheet.Cells[5 + l_row, 4].Value = dt.Rows[l_row - 1][3].ToString();
                    }
                    if (col == 4)
                    {
                        if (dt.Rows[l_row - 1][4].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 5].Value = double.Parse(dt.Rows[l_row - 1][4].ToString());
                        }
                    }
                    if (col == 5)
                    {
                        if (dt.Rows[l_row - 1][5].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 6].Value = double.Parse(dt.Rows[l_row - 1][5].ToString());
                        }
                    }
                    if (col == 6)
                    {
                        if (dt.Rows[l_row - 1][6].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 7].Value = double.Parse(dt.Rows[l_row - 1][6].ToString());
                        }
                    }
                    if (col == 7)
                    {
                        if (dt.Rows[l_row - 1][7].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 8].Value = double.Parse(dt.Rows[l_row - 1][7].ToString());
                        }
                    }
                    if (col == 8)
                    {
                        if (dt.Rows[l_row - 1][8].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 9].Value = double.Parse(dt.Rows[l_row - 1][8].ToString());
                        }
                    }
                    if (col == 9)
                    {
                        if (dt.Rows[l_row - 1][9].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 10].Value = double.Parse(dt.Rows[l_row - 1][9].ToString());
                        }
                    }
                    if (dt.Rows[l_row - 1][0].ToString() == "" && dt.Rows[l_row - 1][1].ToString() == "")
                    {
                        exSheet.Cells[5 + l_row, 2].Value = "Total";
                        exSheet.Cells[5 + l_row, 1, 5 + l_row, 10].Font.Bold = true;
                    }
                    if (dt.Rows[l_row - 1][0].ToString() != "" && dt.Rows[l_row - 1][1].ToString() == "")
                    {
                        exSheet.Cells[5 + l_row, 2].Value = "Sub Total";
                        exSheet.Cells[5 + l_row, 1, 5 + l_row, 10].Font.Bold = true;
                    }
                    if (col == 0)
                    {
                        if (l_row < dt.Rows.Count)
                        {
                            if (col == 0)
                            {
                                if (dt.Rows[l_row - 1][0].ToString() == dt.Rows[l_row][0].ToString())
                                {
                                    exSheet.Range[l_row + 5, 1, l_row + 6, 1].Merge();
                                    //l_long_term=l_long_term+1;
                                }
                            }
                        }
                    }
                }
            }
            //use Merge
        }
        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();
    }
예제 #8
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        string p_from_date     = Request["p_from_date"];
        string p_to_date       = Request["p_to_date"];
        string p_po_vendor     = Request["p_po_vendor"];
        string p_item_group_pk = Request["p_item_group_pk"];
        string p_item          = Request["p_item"];

        string TemplateFile = "rpt_bini00040.xls";
        string TempFile     = "../../../../system/temp/rpt_bini00040_" + 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     = "'" + p_from_date + "','" + p_to_date + "','" + p_po_vendor + "','" + p_item_group_pk + "','" + p_item + "'";
        DataTable dtInfo   = CtlLib.TableReadOpenCursor("ST_LG_RPT_BINI00040_1", "'" + Session["User_ID"].ToString() + "'");
        DataTable dtDetail = CtlLib.TableReadOpenCursor("ST_LG_RPT_BINI00040", para);

        if (dtInfo.Rows.Count == 0)
        {
            goto xxx;
        }
        //--set-----DataBind--in---Master---
        if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
        {
            exSheet.Cells["A1"].Select();
            byte[] MyData = new byte[0];
            MyData = (byte[])dtInfo.Rows[0]["pic"];
            MemoryStream stream = new MemoryStream(MyData);

            Bitmap b = new Bitmap(stream);
            exSheet.Shapes.AddPicture(ResizeImage(b, 131, 71));
        }

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

        exSheet.Cells["B1"].Value = dtInfo.Rows[0]["partner_name"].ToString();
        exSheet.Cells["B2"].Value = dtInfo.Rows[0]["addr1"].ToString();
        exSheet.Cells["S1"].Value = "Tel: " + dtInfo.Rows[0]["phone_no"].ToString();
        exSheet.Cells["S2"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();
        exSheet.Cells["B3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString();
        ////end-set-----DataBind--in---Master---
xxx:
        if (dtDetail.Rows.Count == 0)
        {
            return;
        }

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

        for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++)
        {
            for (int col = 0; col <= 18; col++)
            {
                if (col >= 8 && col <= 15)
                {
                    exSheet.Cells[5 + l_row, col + 1].Value = string.IsNullOrEmpty(dtDetail.Rows[l_row - 1][col].ToString()) ? 0 : Convert.ToInt32(dtDetail.Rows[l_row - 1][col]);
                }
                else
                {
                    exSheet.Cells[5 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1][col].ToString();
                }
            }
        }

        exSheet.Cells["I" + (dtDetail.Rows.Count + 6)].Value = "=SUM(I6:I" + (dtDetail.Rows.Count + 5) + ")";
        exSheet.Cells["J" + (dtDetail.Rows.Count + 6)].Value = "=SUM(J6:J" + (dtDetail.Rows.Count + 5) + ")";
        exSheet.Cells["K" + (dtDetail.Rows.Count + 6)].Value = "=SUM(K6:K" + (dtDetail.Rows.Count + 5) + ")";
        exSheet.Cells["L" + (dtDetail.Rows.Count + 6)].Value = "=SUM(L6:L" + (dtDetail.Rows.Count + 5) + ")";
        exSheet.Cells["M" + (dtDetail.Rows.Count + 6)].Value = "=SUM(M6:M" + (dtDetail.Rows.Count + 5) + ")";
        exSheet.Cells["N" + (dtDetail.Rows.Count + 6)].Value = "=SUM(N6:N" + (dtDetail.Rows.Count + 5) + ")";
        exSheet.Cells["O" + (dtDetail.Rows.Count + 6)].Value = "=SUM(O6:O" + (dtDetail.Rows.Count + 5) + ")";
        exSheet.Cells["P" + (dtDetail.Rows.Count + 6)].Value = "=SUM(P6:P" + (dtDetail.Rows.Count + 5) + ")";

        // end loop detail not percent

        // 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

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = 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(pdfFilePath);
        Response.WriteFile(TempFile);
        //Stop execute
        Response.End();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        //string l_pk = Request["master_pk"];

        string TemplateFile = "rpt_bisc00200.xlsx";
        string TempFile     = "../../../../system/temp/rpt_bisc00200_" + 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_from_date        = Request.QueryString["p_from_date"];
        string p_to_date          = Request.QueryString["p_to_date"];
        string p_wh_type          = Request.QueryString["p_wh_type"];
        string p_tin_warehouse_pk = Request.QueryString["p_tin_warehouse_pk"];
        string p_wh_name          = Request.QueryString["p_wh_name"];
        string p_item_grp         = Request.QueryString["p_item_grp"];
        string p_item             = Request.QueryString["p_item"];
        string p_chk_bal          = Request.QueryString["p_chk_bal"];
        string p_bal = Request.QueryString["p_bal"];

        string para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_tin_warehouse_pk + "','" + p_item_grp + "','" + p_item + "','" + p_chk_bal + "','" + p_bal + "'";

        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00200", para);
        DataTable dtHeader = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00200_header", "'" + Session["User_ID"].ToString() + "'");

        if (dtDetail.Rows.Count == 0)
        {
            Response.Write("There is no data!");
            Response.End();
        }

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

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

        exSheet.Cells["C1"].Value = dtHeader.Rows[0]["partner_name"];
        exSheet.Cells["C2"].Value = dtHeader.Rows[0]["addr1"];
        exSheet.Cells["C3"].Value = "Tax Code: " + dtHeader.Rows[0]["tax_code"];

        exSheet.Cells["Q1"].Value = "Tel: " + dtHeader.Rows[0]["phone_no"];
        exSheet.Cells["Q2"].Value = "Fax No: " + dtHeader.Rows[0]["fax_no"];
        exSheet.Cells["Q3"].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");

        exSheet.Cells["B5"].Value = p_from_date + " ~ " + p_to_date;

        int l_pos = 8;

        //-----------------------------detail--------------------
        for (int l_addrow = 0; l_addrow < dtDetail.Rows.Count - 1; l_addrow++)
        {
            exSheet.Range[l_pos + l_addrow + 1, 1].Rows.EntireRow.Insert();//insert row new of sheet
        }
        // loop detail not percent
        int i = 0;

        for (i = 0; i < dtDetail.Rows.Count; i++)
        {
            exSheet.Cells[l_pos + i, 1].Value = i + 1;
            for (int k = 1; k <= dtDetail.Columns.Count; k++)
            {
                exSheet.Cells[l_pos + i, k + 1].Value = dtDetail.Rows[i][k - 1];
            }
        }

        exSheet.Cells[l_pos + i, 6].Value  = "=SUM(F" + l_pos + ":F" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 7].Value  = "=SUM(G" + l_pos + ":G" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 8].Value  = "=SUM(H" + l_pos + ":H" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 9].Value  = "=SUM(I" + l_pos + ":I" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 10].Value = "=SUM(J" + l_pos + ":J" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 11].Value = "=SUM(K" + l_pos + ":K" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 12].Value = "=SUM(L" + l_pos + ":L" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 13].Value = "=SUM(M" + l_pos + ":M" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 14].Value = "=SUM(N" + l_pos + ":N" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 15].Value = "=SUM(O" + l_pos + ":O" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 16].Value = "=SUM(P" + l_pos + ":P" + (dtDetail.Rows.Count + 4) + ")";
        exSheet.Cells[l_pos + i, 17].Value = "=SUM(Q" + l_pos + ":Q" + (dtDetail.Rows.Count + 4) + ")";
        // end loop detail not percent

        // 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

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = true;*/

        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());

        string TemplateFile = "rpt_hrem00201_0.xls";
        string TempFile     = "../../../../system/temp/rpt_hrem00201_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];

        IRange range;

        string p_tco_org_pk, p_nation, p_emp_search, p_select_date, p_from_date, p_to_date, p_status, p_salary_yn, p_position;
        string p_user, p_sal_security;
        string p_data, p_lang;

        p_user         = Request["p_user"].ToString();
        p_tco_org_pk   = Request["p_tco_org_pk"].ToString();
        p_nation       = Request["p_nation"].ToString();
        p_emp_search   = Request["p_emp_search"].ToString();
        p_select_date  = Request["p_select_date"].ToString();
        p_from_date    = Request["p_from_date"].ToString();
        p_to_date      = Request["p_to_date"].ToString();
        p_salary_yn    = Request["p_salary_yn"].ToString();
        p_position     = Request["p_position"].ToString();
        p_status       = Request["p_status"].ToString();
        p_sal_security = Request["p_sal_security"].ToString();
        p_data         = Request["p_data"].ToString();
        p_lang         = Request["p_lang"].ToString();

        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 = CtlLib.TableReadOpen(SQL);

        DataTable dt_allow = CtlLib.TableReadOpenCursor("ST_HR_RPT_HREM00201_0");

        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 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_emp_search + "' is null  " +
              "                 or  (upper(a.emp_id) like  '%' || upper('" + p_emp_search + "')|| '%') " +
              "                 or  (upper(a.old_id ) like  '%' || upper('" + p_emp_search + "')|| '%') " +
              "                 or  (upper(a.full_name) like  '%' || upper('" + p_emp_search + "')|| '%')) " +
              "     and (('" + p_from_date + "' is null and '" + p_to_date + "' is null) " +
              "            or ('" + p_select_date + "' = 1 and (decode(length(a.birth_dt),4,a.birth_dt || '0101',a.birth_dt) between '" + p_from_date + "' and '" + p_to_date + "'))  " +
              "            or ('" + p_select_date + "' = 2 and a.join_dt between '" + p_from_date + "' and '" + p_to_date + "') " +
              "            or ('" + p_select_date + "' = 3 and a.left_dt between '" + p_from_date + "' and '" + p_to_date + "') " +
              "         ) " +
              "     and decode('" + p_salary_yn + "','ALL','ALL',a.salary_yn )='" + p_salary_yn + "' " +
              "     and decode('" + p_position + "','ALL','ALL',a.pos_type )='" + p_position + "' " +
              "     and decode('" + p_status + "','ALL','ALL',a.status)='" + p_status + "' " +
              "      and (   a.tco_org_pk in ( " +
              "                              select     g.pk " +
              "                                    from tco_org g " +
              "                                   where g.del_if = 0 " +
              "                              start with g.pk = " +
              "                                            decode ('" + p_tco_org_pk + "', " +
              "                                                    'ALL', 0, " +
              "                                                    '" + p_tco_org_pk + "' " +
              "                                                   ) " +
              "                              connect by prior g.pk = g.p_pk) " +
              "                        or '" + p_tco_org_pk + "' = 'ALL') " +
              "      order by nvl(c.seq,0) ,org_nm  ,a.emp_id ";



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

        DataTable dt_emp = CtlLib.TableReadOpen(SQL);



        SQL
            = "select to_char(sysdate,'dd/mm/yyyy') from dual ";
        DataTable dt_sysdt = CtlLib.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();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser("APP_DBUSER");


        string p_user, p_tco_org_pk, p_wg, p_work_shift, p_search_by, p_input;
        string p_from_date, p_to_date, p_from_date_text, p_to_date_text, p_date_type, p_nation, p_reg, p_data_type, p_confirm;


        p_tco_org_pk = Request["p_tco_org_pk"].ToString();
        p_wg         = Request["p_wg"].ToString();
        p_date_type  = Request["p_date_type"].ToString();
        p_input      = Request["p_search_temp"].ToString();
        p_work_shift = Request["p_work_shift"].ToString();
        p_from_date  = Request["p_from_date"].ToString();
        p_to_date    = Request["p_to_date"].ToString();
        p_user       = Request["p_user"].ToString();
        p_nation     = Request["p_nation"].ToString();
        p_reg        = Request["p_reg"].ToString();
        p_data_type  = Request["p_data_type"].ToString();
        p_confirm    = Request["p_confirm"].ToString();

        int p_tmp;

        string SQL_Com
            = "SELECT A.NUM_4 FROM VHR_HR_CODE A " +
              " WHERE A.ID='HR0006' AND A.CODE='11'";

        DataTable dt_Com = CtlLib.TableReadOpen(SQL_Com);
        int       irow_com;

        irow_com = dt_Com.Rows.Count;
        if (irow_com == 0)
        {
            p_tmp = 0;
        }
        else
        {
            p_tmp = int.Parse(dt_Com.Rows[0][0].ToString());
        }

        string SQL = "";

        #region statement query

        if (p_data_type == "5")
        {
            SQL = "      SELECT c.ORG_NM C0,gp.WORKGROUP_NM C1,A.EMP_ID C2, A.FULL_NAME C3,TO_CHAR(TO_DATE(GW.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') C4 " +
                  "      ,WS.SHIFT C5,WKD.TIME_IN C6,WKD.TIME_OUT C7,NULLIF(WKD.WORK_TIME,0) C8 " +
                  "       ,NULLIF(WKD.OVT,0) C9,NULLIF(WKD.NT,0) C10,NULLIF(WKD.NT2,0) C11,NULLIF(WKD.NT3,0) C11_2,NULLIF(WKD.HT,0) C12 " +
                  "       ,(SELECT V.CODE_fNM FROM VHR_HR_CODE V WHERE V.ID='HR0003' AND V.CODE=ABS.CODE) C13 " +
                  "       , NULLIF(ABS.ABSENCE_TIME,0) c14 " +
                  "       ,gw.note_att c15 " +
                  "      FROM THR_EMPLOYEE A, TCO_ORG C,THR_WORK_GROUP GP,THR_WG_SCH_DETAIL GW,THR_WORK_SHIFT WS " +
                  "             ,(SELECT D.THR_EMP_PK AS THR_EMP_PK,D.WORK_DT AS W_DATE,MAX( D.TIME_IN) AS TIME_IN, MAX(D.TIME_OUT) AS TIME_OUT, MAX(D.WORK_TIME) AS WORK_TIME " +
                  "           ,MAX(D.PK) AS PK,MAX(D.THR_WS_PK) AS THR_WS_PK " +
                  "           ,SUM(DECODE(H.OT_TYPE,'OT',NVL(H.OT_TIME,0),0)) AS OVT " +
                  "           ,SUM(DECODE(H.OT_TYPE,'NT',NVL(H.OT_TIME,0),0)) AS NT " +
                  "           ,SUM(DECODE(H.OT_TYPE,'NT2',NVL(H.OT_TIME,0),0)) AS NT2 " +
                  "           ,SUM(DECODE(H.OT_TYPE,'NT3',NVL(H.OT_TIME,0),0)) AS NT3 " +
                  "            ,SUM(DECODE(H.OT_TYPE,'HT',NVL(H.OT_TIME,0),0)) AS HT " +
                  "             ,SUM(DECODE(H.OT_TYPE,'OT',H.PK,0)) AS OVT_PK " +
                  "             ,SUM(DECODE(H.OT_TYPE,'NT',H.PK,0)) AS NT_PK " +
                  "             ,SUM(DECODE(H.OT_TYPE,'NT2',H.PK,0)) AS NT2_PK " +
                  "             ,SUM(DECODE(H.OT_TYPE,'HT',H.PK,0)) AS HT_PK " +
                  "             ,MAX( D.DATE_IN) AS DATE_IN " +
                  "             ,MAX( D.DATE_OUT) AS DATE_OUT " +
                  "             ,MAX(DECODE(NVL(D.MOD_BYHAND_YN,'N'),'N',NVL(D.MOD_AUTO_YN,'N'),'Y')) AS BYHAND_YN " +
                  "             ,MAX(DECODE(NVL(H.MOD_BYHAND_YN,'N'),'N',NVL(H.MOD_AUTO_YN,'N'),'Y')) AS BYHAND_YN2 " +
                  "             ,D.NO_SCAN_FLAG AS NO_SCAN " +
                  "                FROM THR_TIME_MACHINE D,THR_EXTRA_TIME H,THR_EMPLOYEE V " +
                  "              WHERE D.DEL_IF = 0 AND V.DEL_IF=0  " +
                  "              AND H.DEL_IF(+)=0 AND D.THR_EMP_PK=V.PK   " +
                  "              AND D.THR_EMP_PK=H.THR_EMP_PK(+) " +
                  "              AND D.WORK_DT=H.WORK_DT(+) " +
                  "              AND D.WORK_DT BETWEEN '" + p_from_date + "' AND '" + p_to_date + "' " +
                  "              AND ('" + p_input + "' IS NULL  " +
                  "                     OR  (UPPER(V.EMP_ID) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(V.OLD_ID ) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(V.FULL_NAME) LIKE  '%' || UPPER('" + p_input + "')|| '%')) " +
                  "               AND V.TCO_ORG_PK IN ( " +
                  "                                  SELECT     G.PK " +
                  "                                        FROM TCO_ORG G " +
                  "                                       WHERE G.DEL_IF = 0 " +
                  "                                  START WITH G.PK =(CASE WHEN '" + p_tco_org_pk + "'='ALL' THEN G.PK ELSE TO_NUMBER('" + p_tco_org_pk + "') END) " +
                  "                                  CONNECT BY PRIOR G.PK = G.P_PK) " +
                  "              GROUP BY  D.THR_EMP_PK, D.WORK_DT,D.NO_SCAN_FLAG) WKD " +
                  "           ,(SELECT E.ABSENCE_DT AS ABS_DATE,E.THR_EMP_PK, E.ABSENCE_TYPE AS CODE, E.ABSENCE_TIME,E.PK " +
                  "               FROM THR_ABSENCE E,THR_EMPLOYEE F " +
                  "              WHERE E.DEL_IF = 0 AND F.DEL_IF=0  AND F.PK=E.THR_EMP_PK " +
                  "                AND E.ABSENCE_DT  BETWEEN '" + p_from_date + "' AND '" + p_to_date + "' " +
                  "              AND ('" + p_input + "' IS NULL  " +
                  "                     OR  (UPPER(F.EMP_ID) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(F.OLD_ID ) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(F.FULL_NAME) LIKE  '%' || UPPER('" + p_input + "')|| '%')) " +
                  "                AND F.TCO_ORG_PK IN ( " +
                  "                                  SELECT     G.PK " +
                  "                                        FROM TCO_ORG G " +
                  "                                       WHERE G.DEL_IF = 0 " +
                  "                                  START WITH G.PK =(CASE WHEN '" + p_tco_org_pk + "'='ALL' THEN G.PK ELSE TO_NUMBER('" + p_tco_org_pk + "') END) " +
                  "                                  CONNECT BY PRIOR G.PK = G.P_PK) " +
                  "            ) ABS " +
                  "     WHERE A.DEL_IF = 0 AND C.DEL_IF=0 AND GP.DEL_IF=0 AND GW.DEL_IF=0 AND WS.DEL_IF=0  " +
                  "       AND A.PK=GW.THR_EMP_PK AND GW.THR_WS_PK=WS.PK  " +
                  "       AND NVL(A.OLD_JOIN_DT,A.JOIN_DT) <= '" + p_to_date + "' " +
                  "       AND (A.LEFT_DT > TO_CHAR(TO_DATE(GW.WORK_DT,'YYYYMMDD')-'" + p_tmp + "','YYYYMMDD') OR A.LEFT_DT IS NULL) " +
                  "       AND A.TCO_ORG_PK = C.PK AND A.THR_WG_PK=GP.PK " +
                  "       AND GW.WORK_DT BETWEEN '" + p_from_date + "' AND '" + p_to_date + "' " +
                  "       AND GW.THR_EMP_PK = WKD.THR_EMP_PK(+) " +
                  "       AND GW.THR_EMP_PK = ABS.THR_EMP_PK(+) " +
                  "       AND GW.WORK_DT=WKD.W_DATE(+) " +
                  "       AND GW.WORK_DT=ABS.ABS_DATE(+) " +
                  "     AND DECODE ('" + p_wg + "', 'ALL', '" + p_wg + "', GP.PK) = '" + p_wg + "'" +
                  "   AND DECODE ('" + p_work_shift + "', 'ALL', '" + p_work_shift + "', WS.PK) = '" + p_work_shift + "'" +
                  "   AND DECODE ('" + p_nation + "', 'ALL', '" + p_nation + "', a.nation) = '" + p_nation + "'" +
                  "   AND ('" + p_reg + "'='ALL' OR A.PK IN(SELECT R.THR_EMP_PK " +
                  "                                         FROM THR_REGULATION R  " +
                  "                                         WHERE R.DEL_IF=0 AND R.THR_EMP_PK=A.PK " +
                  "                                            AND GW.WORK_DT>=R.START_DT AND NVL(R.END_DT,GW.WORK_DT)>=GW.WORK_DT) " +
                  "        ) " +
                  "       AND C.PK IN ( " +
                  "                                  SELECT     G.PK " +
                  "                                        FROM TCO_ORG G " +
                  "                                       WHERE G.DEL_IF = 0 " +
                  "                                  START WITH G.PK =(CASE WHEN '" + p_tco_org_pk + "'='ALL' THEN G.PK ELSE TO_NUMBER('" + p_tco_org_pk + "') END) " +
                  "                                  CONNECT BY PRIOR G.PK = G.P_PK) " +
                  "       AND A.PK NOT IN(SELECT AB.THR_EMP_PK FROM THR_ABSENCE_REG AB WHERE AB.DEL_IF=0 " +
                  "                    AND GW.WORK_DT BETWEEN AB.START_DT AND AB.END_DT AND AB.THR_EMP_PK=A.PK ) " +
                  "              AND ('" + p_input + "' IS NULL  " +
                  "                     OR  (UPPER(A.EMP_ID) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(A.OLD_ID ) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(A.FULL_NAME) LIKE  '%' || UPPER('" + p_input + "')|| '%')) " +
                  " and decode('" + p_confirm + "','ALL','ALL',nvl(GW.APPROVE_YN,'N')) = '" + p_confirm + "'   " +
                  "    ORDER BY C.ORG_NM,A.EMP_ID,GW.WORK_DT ";
        }
        else if (p_data_type == "1")
        {
            SQL = "      SELECT c.ORG_NM C0,gp.WORKGROUP_NM C1,A.EMP_ID C2, A.FULL_NAME C3,TO_CHAR(TO_DATE(GW.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') C4 " +
                  "      ,WS.SHIFT C5,NULL C6,NULL C7,NULL C8 " +
                  "       ,NULL C9,NULL C10,NULL C11,NULL C11_2,NULL C12 " +
                  "       ,(SELECT V.CODE_fNM FROM VHR_HR_CODE V WHERE V.ID='HR0003' AND V.CODE=ABS.CODE) C13 " +
                  "       , NULLIF(ABS.ABSENCE_TIME,0) " +
                  "       ,gw.note_att c15 " +
                  "      FROM THR_EMPLOYEE A, TCO_ORG C,THR_WORK_GROUP GP,THR_WG_SCH_DETAIL GW,THR_WORK_SHIFT WS " +
                  "           ,(SELECT E.ABSENCE_DT AS ABS_DATE,E.THR_EMP_PK, E.ABSENCE_TYPE AS CODE, E.ABSENCE_TIME,E.PK " +
                  "               FROM THR_ABSENCE E,THR_EMPLOYEE F " +
                  "              WHERE E.DEL_IF = 0 AND F.DEL_IF=0  AND F.PK=E.THR_EMP_PK " +
                  "                AND E.ABSENCE_DT  BETWEEN '" + p_from_date + "' AND '" + p_to_date + "' " +
                  "              AND ('" + p_input + "' IS NULL  " +
                  "                     OR  (UPPER(F.EMP_ID) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(F.OLD_ID ) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(F.FULL_NAME) LIKE  '%' || UPPER('" + p_input + "')|| '%')) " +
                  "                AND F.TCO_ORG_PK IN ( " +
                  "                                  SELECT     G.PK " +
                  "                                        FROM TCO_ORG G " +
                  "                                       WHERE G.DEL_IF = 0 " +
                  "                                  START WITH G.PK =(CASE WHEN '" + p_tco_org_pk + "'='ALL' THEN G.PK ELSE TO_NUMBER('" + p_tco_org_pk + "') END) " +
                  "                                  CONNECT BY PRIOR G.PK = G.P_PK) " +
                  "            ) ABS " +
                  "     WHERE A.DEL_IF = 0 AND C.DEL_IF=0 AND GP.DEL_IF=0 AND GW.DEL_IF=0 AND WS.DEL_IF=0  " +
                  "       AND A.PK=GW.THR_EMP_PK AND GW.THR_WS_PK=WS.PK  " +
                  "       AND NVL(A.OLD_JOIN_DT,A.JOIN_DT) <= '" + p_to_date + "' " +
                  "       AND (A.LEFT_DT > TO_CHAR(TO_DATE(GW.WORK_DT,'YYYYMMDD')-'" + p_tmp + "','YYYYMMDD') OR A.LEFT_DT IS NULL) " +
                  "       AND A.TCO_ORG_PK = C.PK AND A.THR_WG_PK=GP.PK " +
                  "       AND GW.WORK_DT BETWEEN '" + p_from_date + "' AND '" + p_to_date + "' " +
                  "       AND GW.THR_EMP_PK = ABS.THR_EMP_PK " +
                  "       AND GW.WORK_DT=ABS.ABS_DATE " +
                  "     AND DECODE ('" + p_wg + "', 'ALL', '" + p_wg + "', GP.PK) = '" + p_wg + "'" +
                  "   AND DECODE ('" + p_work_shift + "', 'ALL', '" + p_work_shift + "', WS.PK) = '" + p_work_shift + "'" +
                  "   AND DECODE ('" + p_nation + "', 'ALL', '" + p_nation + "', a.nation) = '" + p_nation + "'" +
                  "   AND ('" + p_reg + "'='ALL' OR A.PK IN(SELECT R.THR_EMP_PK " +
                  "                                         FROM THR_REGULATION R  " +
                  "                                         WHERE R.DEL_IF=0 AND R.THR_EMP_PK=A.PK " +
                  "                                            AND GW.WORK_DT>=R.START_DT AND NVL(R.END_DT,GW.WORK_DT)>=GW.WORK_DT) " +
                  "        ) " +
                  "       AND C.PK IN ( " +
                  "                                  SELECT     G.PK " +
                  "                                        FROM TCO_ORG G " +
                  "                                       WHERE G.DEL_IF = 0 " +
                  "                                  START WITH G.PK =(CASE WHEN '" + p_tco_org_pk + "'='ALL' THEN G.PK ELSE TO_NUMBER('" + p_tco_org_pk + "') END) " +
                  "                                  CONNECT BY PRIOR G.PK = G.P_PK) " +
                  "       AND A.PK NOT IN(SELECT AB.THR_EMP_PK FROM THR_ABSENCE_REG AB WHERE AB.DEL_IF=0 " +
                  "                    AND GW.WORK_DT BETWEEN AB.START_DT AND AB.END_DT AND AB.THR_EMP_PK=A.PK ) " +
                  "              AND ('" + p_input + "' IS NULL  " +
                  "                     OR  (UPPER(A.EMP_ID) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(A.OLD_ID ) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(A.FULL_NAME) LIKE  '%' || UPPER('" + p_input + "')|| '%')) " +
                  " and decode('" + p_confirm + "','ALL','ALL',nvl(GW.APPROVE_YN,'N')) = '" + p_confirm + "'   " +
                  "    ORDER BY C.ORG_NM,A.EMP_ID,GW.WORK_DT ";
        }
        else if (p_data_type == "2")
        {
            SQL = "      SELECT c.ORG_NM C0,gp.WORKGROUP_NM C1,A.EMP_ID C2, A.FULL_NAME C3,TO_CHAR(TO_DATE(GW.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') C4 " +
                  "      ,WS.SHIFT C5,WKD.TIME_IN C6,WKD.TIME_OUT C7,NULLIF(WKD.WORK_TIME,0) C8 " +
                  "       ,NULLIF(WKD.OVT,0) C9,NULLIF(WKD.NT,0) C10,NULLIF(WKD.NT2,0) C11,NULLIF(WKD.NT3,0) C11_2,NULLIF(WKD.HT,0) C12 " +
                  "       ,NULL C13 " +
                  "       , NULL " +
                  "       ,gw.note_att c15 " +
                  "      FROM THR_EMPLOYEE A, TCO_ORG C,THR_WORK_GROUP GP,THR_WG_SCH_DETAIL GW,THR_WORK_SHIFT WS " +
                  "             ,(SELECT D.THR_EMP_PK AS THR_EMP_PK,D.WORK_DT AS W_DATE,MAX( D.TIME_IN) AS TIME_IN, MAX(D.TIME_OUT) AS TIME_OUT, MAX(D.WORK_TIME) AS WORK_TIME " +
                  "           ,MAX(D.PK) AS PK,MAX(D.THR_WS_PK) AS THR_WS_PK " +
                  "           ,SUM(DECODE(H.OT_TYPE,'OT',NVL(H.OT_TIME,0),0)) AS OVT " +
                  "           ,SUM(DECODE(H.OT_TYPE,'NT',NVL(H.OT_TIME,0),0)) AS NT " +
                  "           ,SUM(DECODE(H.OT_TYPE,'NT2',NVL(H.OT_TIME,0),0)) AS NT2 " +
                  "           ,SUM(DECODE(H.OT_TYPE,'NT3',NVL(H.OT_TIME,0),0)) AS NT3 " +
                  "            ,SUM(DECODE(H.OT_TYPE,'HT',NVL(H.OT_TIME,0),0)) AS HT " +
                  "             ,SUM(DECODE(H.OT_TYPE,'OT',H.PK,0)) AS OVT_PK " +
                  "             ,SUM(DECODE(H.OT_TYPE,'NT',H.PK,0)) AS NT_PK " +
                  "             ,SUM(DECODE(H.OT_TYPE,'NT2',H.PK,0)) AS NT2_PK " +
                  "             ,SUM(DECODE(H.OT_TYPE,'HT',H.PK,0)) AS HT_PK " +
                  "             ,MAX( D.DATE_IN) AS DATE_IN " +
                  "             ,MAX( D.DATE_OUT) AS DATE_OUT " +
                  "             ,MAX(DECODE(NVL(D.MOD_BYHAND_YN,'N'),'N',NVL(D.MOD_AUTO_YN,'N'),'Y')) AS BYHAND_YN " +
                  "             ,MAX(DECODE(NVL(H.MOD_BYHAND_YN,'N'),'N',NVL(H.MOD_AUTO_YN,'N'),'Y')) AS BYHAND_YN2 " +
                  "             ,D.NO_SCAN_FLAG AS NO_SCAN, max(h.OT_PLUS) as OT_PLUS " +
                  "                FROM THR_TIME_MACHINE D,THR_EXTRA_TIME H,THR_EMPLOYEE V " +
                  "              WHERE D.DEL_IF = 0 AND V.DEL_IF=0  " +
                  "              AND H.DEL_IF(+)=0 AND D.THR_EMP_PK=V.PK   " +
                  "              AND D.THR_EMP_PK=H.THR_EMP_PK(+) " +
                  "              AND D.WORK_DT=H.WORK_DT(+) " +
                  "              AND D.WORK_DT BETWEEN '" + p_from_date + "' AND '" + p_to_date + "' " +
                  "              AND ('" + p_input + "' IS NULL  " +
                  "                     OR  (UPPER(V.EMP_ID) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(V.OLD_ID ) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(V.FULL_NAME) LIKE  '%' || UPPER('" + p_input + "')|| '%')) " +
                  "               AND V.TCO_ORG_PK IN ( " +
                  "                                  SELECT     G.PK " +
                  "                                        FROM TCO_ORG G " +
                  "                                       WHERE G.DEL_IF = 0 " +
                  "                                  START WITH G.PK =(CASE WHEN '" + p_tco_org_pk + "'='ALL' THEN G.PK ELSE TO_NUMBER('" + p_tco_org_pk + "') END) " +
                  "                                  CONNECT BY PRIOR G.PK = G.P_PK) " +
                  " AND D.TIME_IN IS NOT NULL AND D.TIME_OUT IS NOT NULL " +
                  "              GROUP BY  D.THR_EMP_PK, D.WORK_DT,D.NO_SCAN_FLAG) WKD " +
                  "     WHERE A.DEL_IF = 0 AND C.DEL_IF=0 AND GP.DEL_IF=0 AND GW.DEL_IF=0 AND WS.DEL_IF=0  " +
                  "       AND A.PK=GW.THR_EMP_PK AND GW.THR_WS_PK=WS.PK  " +
                  "       AND NVL(A.OLD_JOIN_DT,A.JOIN_DT) <= '" + p_to_date + "' " +
                  "       AND (A.LEFT_DT > TO_CHAR(TO_DATE(GW.WORK_DT,'YYYYMMDD')-'" + p_tmp + "','YYYYMMDD') OR A.LEFT_DT IS NULL) " +
                  "       AND A.TCO_ORG_PK = C.PK AND A.THR_WG_PK=GP.PK " +
                  "       AND GW.WORK_DT BETWEEN '" + p_from_date + "' AND '" + p_to_date + "' " +
                  "       AND GW.THR_EMP_PK = WKD.THR_EMP_PK " +
                  "       AND GW.WORK_DT=WKD.W_DATE " +
                  "     AND DECODE ('" + p_wg + "', 'ALL', '" + p_wg + "', GP.PK) = '" + p_wg + "'" +
                  "   AND DECODE ('" + p_work_shift + "', 'ALL', '" + p_work_shift + "', WS.PK) = '" + p_work_shift + "'" +
                  "   AND DECODE ('" + p_nation + "', 'ALL', '" + p_nation + "', a.nation) = '" + p_nation + "'" +
                  "   AND ('" + p_reg + "'='ALL' OR A.PK IN(SELECT R.THR_EMP_PK " +
                  "                                         FROM THR_REGULATION R  " +
                  "                                         WHERE R.DEL_IF=0 AND R.THR_EMP_PK=A.PK " +
                  "                                            AND GW.WORK_DT>=R.START_DT AND NVL(R.END_DT,GW.WORK_DT)>=GW.WORK_DT) " +
                  "        ) " +
                  "       AND C.PK IN ( " +
                  "                                  SELECT     G.PK " +
                  "                                        FROM TCO_ORG G " +
                  "                                       WHERE G.DEL_IF = 0 " +
                  "                                  START WITH G.PK =(CASE WHEN '" + p_tco_org_pk + "'='ALL' THEN G.PK ELSE TO_NUMBER('" + p_tco_org_pk + "') END) " +
                  "                                  CONNECT BY PRIOR G.PK = G.P_PK) " +
                  "       AND A.PK NOT IN(SELECT AB.THR_EMP_PK FROM THR_ABSENCE_REG AB WHERE AB.DEL_IF=0 " +
                  "                    AND GW.WORK_DT BETWEEN AB.START_DT AND AB.END_DT AND AB.THR_EMP_PK=A.PK ) " +
                  "              AND ('" + p_input + "' IS NULL  " +
                  "                     OR  (UPPER(A.EMP_ID) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(A.OLD_ID ) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(A.FULL_NAME) LIKE  '%' || UPPER('" + p_input + "')|| '%')) " +
                  " and decode('" + p_confirm + "','ALL','ALL',nvl(GW.APPROVE_YN,'N')) = '" + p_confirm + "'   " +
                  "    ORDER BY C.ORG_NM,A.EMP_ID,GW.WORK_DT ";
        }
        else if (p_data_type == "3")
        {
            SQL = "      SELECT c.ORG_NM C0,gp.WORKGROUP_NM C1,A.EMP_ID C2, A.FULL_NAME C3,TO_CHAR(TO_DATE(GW.WORK_DT,'YYYYMMDD'),'DD/MM/YYYY') C4 " +
                  "      ,WS.SHIFT C5,WKD.TIME_IN C6,WKD.TIME_OUT C7,NULLIF(WKD.WORK_TIME,0) C8 " +
                  "       ,NULLIF(WKD.OVT,0) C9,NULLIF(WKD.NT,0) C10,NULLIF(WKD.NT2,0) C11,NULLIF(WKD.NT3,0) C11_2,NULLIF(WKD.HT,0) C12 " +
                  "       ,NULL C13 " +
                  "       ,NULL " +
                  "       ,gw.note_att c15 " +
                  "      FROM THR_EMPLOYEE A, TCO_ORG C,THR_WORK_GROUP GP,THR_WG_SCH_DETAIL GW,THR_WORK_SHIFT WS " +
                  "             ,(SELECT D.THR_EMP_PK AS THR_EMP_PK,D.WORK_DT AS W_DATE,MAX( D.TIME_IN) AS TIME_IN, MAX(D.TIME_OUT) AS TIME_OUT, MAX(D.WORK_TIME) AS WORK_TIME " +
                  "           ,MAX(D.PK) AS PK,MAX(D.THR_WS_PK) AS THR_WS_PK " +
                  "           ,SUM(DECODE(H.OT_TYPE,'OT',NVL(H.OT_TIME,0),0)) AS OVT " +
                  "           ,SUM(DECODE(H.OT_TYPE,'NT',NVL(H.OT_TIME,0),0)) AS NT " +
                  "           ,SUM(DECODE(H.OT_TYPE,'NT2',NVL(H.OT_TIME,0),0)) AS NT2 " +
                  "           ,SUM(DECODE(H.OT_TYPE,'NT2',NVL(H.OT_TIME,0),0)) AS NT3 " +
                  "            ,SUM(DECODE(H.OT_TYPE,'HT',NVL(H.OT_TIME,0),0)) AS HT " +
                  "             ,SUM(DECODE(H.OT_TYPE,'OT',H.PK,0)) AS OVT_PK " +
                  "             ,SUM(DECODE(H.OT_TYPE,'NT',H.PK,0)) AS NT_PK " +
                  "             ,SUM(DECODE(H.OT_TYPE,'NT2',H.PK,0)) AS NT2_PK " +
                  "             ,SUM(DECODE(H.OT_TYPE,'HT',H.PK,0)) AS HT_PK " +
                  "             ,MAX( D.DATE_IN) AS DATE_IN " +
                  "             ,MAX( D.DATE_OUT) AS DATE_OUT " +
                  "             ,MAX(DECODE(NVL(D.MOD_BYHAND_YN,'N'),'N',NVL(D.MOD_AUTO_YN,'N'),'Y')) AS BYHAND_YN " +
                  "             ,MAX(DECODE(NVL(H.MOD_BYHAND_YN,'N'),'N',NVL(H.MOD_AUTO_YN,'N'),'Y')) AS BYHAND_YN2 " +
                  "             ,D.NO_SCAN_FLAG AS NO_SCAN, max(h.OT_PLUS) as OT_PLUS " +
                  "                FROM THR_TIME_MACHINE D,THR_EXTRA_TIME H,THR_EMPLOYEE V " +
                  "              WHERE D.DEL_IF = 0 AND V.DEL_IF=0  " +
                  "              AND H.DEL_IF(+)=0 AND D.THR_EMP_PK=V.PK   " +
                  "              AND D.THR_EMP_PK=H.THR_EMP_PK(+) " +
                  "              AND D.WORK_DT=H.WORK_DT(+) " +
                  "              AND D.WORK_DT BETWEEN '" + p_from_date + "' AND '" + p_to_date + "' " +
                  "              AND ('" + p_input + "' IS NULL  " +
                  "                     OR  (UPPER(V.EMP_ID) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(V.OLD_ID ) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(V.FULL_NAME) LIKE  '%' || UPPER('" + p_input + "')|| '%')) " +
                  "               AND V.TCO_ORG_PK IN ( " +
                  "                                  SELECT     G.PK " +
                  "                                        FROM TCO_ORG G " +
                  "                                       WHERE G.DEL_IF = 0 " +
                  "                                  START WITH G.PK =(CASE WHEN '" + p_tco_org_pk + "'='ALL' THEN G.PK ELSE TO_NUMBER('" + p_tco_org_pk + "') END) " +
                  "                                  CONNECT BY PRIOR G.PK = G.P_PK) " +
                  " AND ( (D.TIME_IN IS NULL AND D.TIME_OUT IS NOT NULL) " +
                  "        OR (D.TIME_IN IS NOT NULL AND D.TIME_OUT IS NULL) " +
                  "       ) " +
                  "              GROUP BY  D.THR_EMP_PK, D.WORK_DT,D.NO_SCAN_FLAG) WKD " +
                  "     WHERE A.DEL_IF = 0 AND C.DEL_IF=0 AND GP.DEL_IF=0 AND GW.DEL_IF=0 AND WS.DEL_IF=0  " +
                  "       AND A.PK=GW.THR_EMP_PK AND GW.THR_WS_PK=WS.PK  " +
                  "       AND NVL(A.OLD_JOIN_DT,A.JOIN_DT) <= '" + p_to_date + "' " +
                  "       AND (A.LEFT_DT > TO_CHAR(TO_DATE(GW.WORK_DT,'YYYYMMDD')-'" + p_tmp + "','YYYYMMDD') OR A.LEFT_DT IS NULL) " +
                  "       AND A.TCO_ORG_PK = C.PK AND A.THR_WG_PK=GP.PK " +
                  "       AND GW.WORK_DT BETWEEN '" + p_from_date + "' AND '" + p_to_date + "' " +
                  "       AND GW.THR_EMP_PK = WKD.THR_EMP_PK " +
                  "       AND GW.WORK_DT=WKD.W_DATE " +
                  "     AND DECODE ('" + p_wg + "', 'ALL', '" + p_wg + "', GP.PK) = '" + p_wg + "'" +
                  "   AND DECODE ('" + p_work_shift + "', 'ALL', '" + p_work_shift + "', WS.PK) = '" + p_work_shift + "'" +
                  "   AND DECODE ('" + p_nation + "', 'ALL', '" + p_nation + "', a.nation) = '" + p_nation + "'" +
                  "   AND ('" + p_reg + "'='ALL' OR A.PK IN(SELECT R.THR_EMP_PK " +
                  "                                         FROM THR_REGULATION R  " +
                  "                                         WHERE R.DEL_IF=0 AND R.THR_EMP_PK=A.PK " +
                  "                                            AND GW.WORK_DT>=R.START_DT AND NVL(R.END_DT,GW.WORK_DT)>=GW.WORK_DT) " +
                  "        ) " +
                  "       AND C.PK IN ( " +
                  "                                  SELECT     G.PK " +
                  "                                        FROM TCO_ORG G " +
                  "                                       WHERE G.DEL_IF = 0 " +
                  "                                  START WITH G.PK =(CASE WHEN '" + p_tco_org_pk + "'='ALL' THEN G.PK ELSE TO_NUMBER('" + p_tco_org_pk + "') END) " +
                  "                                  CONNECT BY PRIOR G.PK = G.P_PK) " +
                  "       AND A.PK NOT IN(SELECT AB.THR_EMP_PK FROM THR_ABSENCE_REG AB WHERE AB.DEL_IF=0 " +
                  "                    AND GW.WORK_DT BETWEEN AB.START_DT AND AB.END_DT AND AB.THR_EMP_PK=A.PK ) " +
                  "              AND ('" + p_input + "' IS NULL  " +
                  "                     OR  (UPPER(A.EMP_ID) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(A.OLD_ID ) LIKE  '%' || UPPER('" + p_input + "')|| '%') " +
                  "                     OR  (UPPER(A.FULL_NAME) LIKE  '%' || UPPER('" + p_input + "')|| '%')) " +
                  " and decode('" + p_confirm + "','ALL','ALL',nvl(GW.APPROVE_YN,'N')) = '" + p_confirm + "'   " +
                  "    ORDER BY C.ORG_NM,A.EMP_ID,GW.WORK_DT ";
        }


        #endregion
        //Response.Write(SQL);
        //Response.End();
        DataTable dt_total = CtlLib.TableReadOpen(SQL);
        int       irow, icol;
        irow = dt_total.Rows.Count;
        icol = dt_total.Columns.Count;
        if (irow == 0)
        {
            Response.Write("There is no data");
            Response.End();
        }


        string TemplateFile = "rpt_hrti00700_0.xls";
        string TempFile     = "../../../../system/temp/rpt_hrti00700_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["Sheet1"];
        //begin export data here
        int      pos = 7;
        int      i;
        double[] grant_total = new double[icol + 1];
        for (int k = 0; k < icol; k++)
        {
            grant_total[k] = 0;
        }

        //Insert new row
        for (i = 0; i < irow - 1; i++)
        {
            exSheet.Range[pos + i + 1, 1].Rows.EntireRow.Insert();
        }

        //begin export data here
        exSheet.Cells["A2"].Value = string.Format("From date: {0} To Date: {1}", p_from_date.Substring(6, 2) + "/" + p_from_date.Substring(4, 2) + "/" + p_from_date.Substring(0, 4), p_to_date.Substring(6, 2) + "/" + p_to_date.Substring(4, 2) + "/" + p_to_date.Substring(0, 4));

        exSheet.Cells["P3"].Value = p_user;
        exSheet.Cells["P4"].Value = DateTime.Now.ToString("dd/MM/yyyy");

        for (i = 0; i < irow; i++)
        {
            exSheet.Cells[pos + i, 1].Value = i + 1;
            for (int j = 0; j < icol; j++)
            {
                if (IsNumeric(dt_total.Rows[i][j].ToString()))
                {
                    exSheet.Cells[pos + i, j + 2].Value = double.Parse(dt_total.Rows[i][j].ToString());
                    grant_total[j] += double.Parse(dt_total.Rows[i][j].ToString());
                }
                else
                {
                    exSheet.Cells[pos + i, j + 2].Value = dt_total.Rows[i][j].ToString();
                }
            }
        }

        for (int j = 0; j < icol; j++)
        {
            if (j == 15 || (j >= 8 && j <= 13))
            {
                exSheet.Cells[pos + irow, j + 2].Value = grant_total[j];
            }
        }

        //end export data
        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();
    }
예제 #12
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        string l_pk = Request["p_detail_pk"];
        //string l_tco_company_pk=Request["p_company_pk"];
        string TemplateFile = "rpt_60240020_R_Card2.xls";
        string TempFile     = "../../../../system/temp/rpt_60240020_R_Card2_" + 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 + "'";
        dt   = CtlLib.TableReadOpenCursor("ht_rpt_60240020_rcard", 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 < 7; 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 < 6; 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 < 5; 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 < 4; 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 < 3; 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 < 2; 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[2, 6].Value  = "Print time :" + dt.Rows[0]["print_time"].ToString();
            exSheet.Cells[6, 2].Value  = dt.Rows[0]["slip_no"].ToString();
            exSheet.Cells[6, 3].Value  = dt.Rows[0]["from_date"].ToString();
            exSheet.Cells[6, 5].Value  = dt.Rows[0]["date_to"].ToString();
            exSheet.Cells[9, 3].Value  = dt.Rows[0]["guest_name"].ToString();
            exSheet.Cells[10, 3].Value = dt.Rows[0]["nation"].ToString();

            exSheet.Cells[12, 3].Value = dt.Rows[0]["addr1"].ToString();            //partner
            exSheet.Cells[13, 3].Value = dt.Rows[0]["email_address"].ToString();    //partner
            exSheet.Cells[13, 7].Value = dt.Rows[0]["phone_no"].ToString();         //partner

            /*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["A9"].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 = 8;

        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, 6, l_begin_row + l_row, 7].Merge();

            exSheet.Cells[l_begin_row + l_row, 2].Value = dt.Rows[l_row]["total_amt"];
            exSheet.Cells[l_begin_row + l_row, 3].Value = dt.Rows[l_row]["total_guest"];
            exSheet.Cells[l_begin_row + l_row, 4].Value = dt.Rows[l_row]["total_deposit_amt_bk"];
            exSheet.Cells[l_begin_row + l_row, 5].Value = dt.Rows[l_row]["room_type"];
            exSheet.Cells[l_begin_row + l_row, 6].Value = dt.Rows[l_row]["room_no"];
        }

        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("crm");
        string l_room_no           = Request["p_room_no"];
        string l_checkout_date_frm = Request["p_checkout_date_frm"];
        string l_checkout_date_to  = Request["p_checkout_date_to"];

        string TemplateFile = "rpt_60280040_inquiry_checkout.xls";
        string TempFile     = "../../../../system/temp/rpt_60280040_inquiry_checkout_" + 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_room_no + "','" + l_checkout_date_frm + "','" + l_checkout_date_to + "'";
        dt   = CtlLib.TableReadOpenCursor("rpt_60280040_inquiry", para);
        //-----header---

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

        if (dt.Rows.Count > 0)
        {
            exSheet.Cells[3, 1].Value  = dt.Rows[0]["date_para"].ToString();
            exSheet.Cells[3, 25].Value = dt.Rows[0]["print_time"].ToString();
        }

        //----------------------
        ////end-set-----DataBind--in---Master---
        int l_first_row = 5;

        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range[l_first_row + l_addrow, 1].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_Room_Charge = 0, l_Total_Due = 0, l_Payed = 0, l_Deposit = 0, l_F_B_Hotel = 0, l_MiniBar = 0, l_Laundry = 0;
        int l_Telephone = 0, l_Others = 0, l_Spa_fee = 0, l_Lotus_shop = 0, l_Adjust = 0;
        int l_cnt = dt.Rows.Count;

        for (int l_row = 0; l_row < dt.Rows.Count; l_row++)
        {
            exSheet.Cells[l_first_row + l_row, 1].Value = dt.Rows[l_row]["folio"];
            exSheet.Cells[l_first_row + l_row, 2].Value = dt.Rows[l_row]["room_no"];
            exSheet.Cells[l_first_row + l_row, 3].Value = dt.Rows[l_row]["last_name"];
            exSheet.Cells[l_first_row + l_row, 4].Value = dt.Rows[l_row]["nation"];
            exSheet.Cells[l_first_row + l_row, 5].Value = dt.Rows[l_row]["checkin_date"];
            exSheet.Cells[l_first_row + l_row, 6].Value = dt.Rows[l_row]["checkout_date"];
            exSheet.Cells[l_first_row + l_row, 7].Value = dt.Rows[l_row]["days"];
            exSheet.Cells[l_first_row + l_row, 8].Value = dt.Rows[l_row]["room_type"];
            exSheet.Cells[l_first_row + l_row, 9].Value = dt.Rows[l_row]["avg_rate_amt"];

            exSheet.Cells[l_first_row + l_row, 10].Value = dt.Rows[l_row]["room_charge"];
            exSheet.Cells[l_first_row + l_row, 11].Value = dt.Rows[l_row]["total_due"];
            exSheet.Cells[l_first_row + l_row, 12].Value = dt.Rows[l_row]["actual_pay"];
            exSheet.Cells[l_first_row + l_row, 13].Value = dt.Rows[l_row]["advance_amt"];
            exSheet.Cells[l_first_row + l_row, 14].Value = dt.Rows[l_row]["fnb_hotel_amt"];
            exSheet.Cells[l_first_row + l_row, 15].Value = dt.Rows[l_row]["minibar_amt"];
            exSheet.Cells[l_first_row + l_row, 16].Value = dt.Rows[l_row]["laundry_amt"];
            exSheet.Cells[l_first_row + l_row, 17].Value = dt.Rows[l_row]["telephone"];
            exSheet.Cells[l_first_row + l_row, 18].Value = dt.Rows[l_row]["others_amt"];
            exSheet.Cells[l_first_row + l_row, 19].Value = dt.Rows[l_row]["spa_amt"];
            exSheet.Cells[l_first_row + l_row, 20].Value = dt.Rows[l_row]["shop_amt"];
            exSheet.Cells[l_first_row + l_row, 21].Value = dt.Rows[l_row]["adjust_amt"];

            exSheet.Cells[l_first_row + l_row, 22].Value = dt.Rows[l_row]["checkin_by"];
            exSheet.Cells[l_first_row + l_row, 23].Value = dt.Rows[l_row]["checkin_time"];
            exSheet.Cells[l_first_row + l_row, 24].Value = dt.Rows[l_row]["checkout_by"];
            exSheet.Cells[l_first_row + l_row, 25].Value = dt.Rows[l_row]["checkout_time"];
            exSheet.Cells[l_first_row + l_row, 26].Value = dt.Rows[l_row]["rate_name"];
            exSheet.Cells[l_first_row + l_row, 27].Value = dt.Rows[l_row]["pay_method"];

            if (dt.Rows[l_row]["room_charge"].ToString() != "")
            {
                l_Room_Charge = l_Room_Charge + int.Parse(dt.Rows[l_row]["room_charge"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["total_due"].ToString() != "")
            {
                l_Total_Due = l_Total_Due + int.Parse(dt.Rows[l_row]["total_due"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["actual_pay"].ToString() != "")
            {
                l_Payed = l_Payed + int.Parse(dt.Rows[l_row]["actual_pay"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["advance_amt"].ToString() != "")
            {
                l_Deposit = l_Deposit + int.Parse(dt.Rows[l_row]["advance_amt"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["fnb_hotel_amt"].ToString() != "")
            {
                l_F_B_Hotel = l_F_B_Hotel + int.Parse(dt.Rows[l_row]["fnb_hotel_amt"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["minibar_amt"].ToString() != "")
            {
                l_MiniBar = l_MiniBar + int.Parse(dt.Rows[l_row]["minibar_amt"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["laundry_amt"].ToString() != "")
            {
                l_Laundry = l_Laundry + int.Parse(dt.Rows[l_row]["laundry_amt"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["telephone"].ToString() != "")
            {
                l_Telephone = l_Telephone + int.Parse(dt.Rows[l_row]["telephone"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["others_amt"].ToString() != "")
            {
                l_Others = l_Others + int.Parse(dt.Rows[l_row]["others_amt"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["spa_amt"].ToString() != "")
            {
                l_Spa_fee = l_Spa_fee + int.Parse(dt.Rows[l_row]["spa_amt"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["shop_amt"].ToString() != "")
            {
                l_Lotus_shop = l_Lotus_shop + int.Parse(dt.Rows[l_row]["shop_amt"].ToString());                    //8--19
            }
            if (dt.Rows[l_row]["adjust_amt"].ToString() != "")
            {
                l_Adjust = l_Adjust + int.Parse(dt.Rows[l_row]["adjust_amt"].ToString());                    //8--19
            }
        }
        exSheet.Cells[5 + dt.Rows.Count, 10].Value = l_Room_Charge;
        exSheet.Cells[5 + dt.Rows.Count, 11].Value = l_Total_Due;
        exSheet.Cells[5 + dt.Rows.Count, 12].Value = l_Payed;
        exSheet.Cells[5 + dt.Rows.Count, 13].Value = l_Deposit;
        exSheet.Cells[5 + dt.Rows.Count, 14].Value = l_F_B_Hotel;
        exSheet.Cells[5 + dt.Rows.Count, 15].Value = l_MiniBar;
        exSheet.Cells[5 + dt.Rows.Count, 16].Value = l_Laundry;
        exSheet.Cells[5 + dt.Rows.Count, 17].Value = l_Telephone;
        exSheet.Cells[5 + dt.Rows.Count, 18].Value = l_Others;
        exSheet.Cells[5 + dt.Rows.Count, 19].Value = l_Spa_fee;
        exSheet.Cells[5 + dt.Rows.Count, 20].Value = l_Lotus_shop;
        exSheet.Cells[5 + dt.Rows.Count, 21].Value = l_Adjust;

        // 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();
    }
예제 #14
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        string l_dt_from = Request["p_dt_from"];
        string l_dt_to   = Request["p_dt_to"];

        string TemplateFile = "rpt_60300010_fee_adjust_date_frm_to.xls";
        string TempFile     = "../../../../system/temp/rpt_60300010_fee_adjust_date_frm_to_" + 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_dt_from + "','" + l_dt_to + "'";
        dt   = CtlLib.TableReadOpenCursor("rpt_60300010_dt_frm_to", para);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[3, 3].Value = "There is not data!";
        }
        if (dt.Rows.Count > 0)
        {
            exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString();
            exSheet.Cells[3, 8].Value = dt.Rows[0]["print_time"].ToString();
        }

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

        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range["A6"].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 <= 7; col++)
                {
                    if (col == 0 || col == 1 || col == 2 || col == 3 || col == 7)
                    {
                        exSheet.Cells[4 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString();
                        exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
                        //exSheet.Range[5, col + 1, l_row + 5, 7].Font.Bold = false;
                    }
                    else
                    {
                        if (col == 6)
                        {
                            exSheet.Cells[4 + l_row, col + 1].Value = float.Parse(dt.Rows[l_row - 1][col].ToString());
                            exSheet.Cells[4, col + 1, l_row + 4, col + 1].NumberFormat = "#,##0.00";
                            exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment      = XlHAlign.xlHAlignRight;
                            if (float.Parse(dt.Rows[l_row - 1][col].ToString()) == 0.0)
                            {
                                exSheet.Cells[4 + l_row, col + 1].Value = "";
                            }
                        }
                        else
                        {
                            exSheet.Cells[4 + l_row, col + 1].Value = double.Parse(dt.Rows[l_row - 1][col].ToString());
                            exSheet.Cells[4, col + 1, l_row + 4, col + 1].NumberFormat = "#,##0";
                            exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment      = XlHAlign.xlHAlignRight;

                            if (double.Parse(dt.Rows[l_row - 1][col].ToString()) == 0)
                            {
                                exSheet.Cells[4 + l_row, col + 1].Value = "";
                            }
                        }
                    }
                }
            }
        }
        // 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();
    }
예제 #15
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        string l_date           = Request["p_date"];
        string l_date_to        = Request["p_date_to"];
        string l_room           = Request["p_room"];
        string l_payment_method = Request["p_payment_method"];
        string l_partner_PK     = Request["p_partner_PK"];
        string l_time_frm       = Request["p_time_frm"];
        string l_time_to        = Request["p_time_to"];
        string l_cashier        = Request["p_cashier"];

        string TemplateFile = "rpt_60280120_cash_daily_summary2.xls";
        string TempFile     = "../../../../system/temp/rpt_60280120_cash_daily_summary2_" + 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_date + "','" + l_date_to + "','" + l_room + "','" + l_payment_method + "','" + l_partner_PK + "','" + l_time_frm + "','" + l_time_to + "','" + l_cashier + "'";

        dt = CtlLib.TableReadOpenCursor("ht_rpt_60280120", para);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[3, 8].Value = "There is not data!";
        }
        if (dt.Rows.Count > 0)
        {
            if (l_time_frm != "")
            {
                exSheet.Cells[3, 4].Value = "Time :" + l_time_frm + " ~ " + l_time_to;
            }
            if (l_cashier != "")
            {
                exSheet.Cells[3, 8].Value = "Cashier ID :" + l_cashier;
            }
            exSheet.Cells[3, 1].Value  = dt.Rows[0]["date_para"].ToString();
            exSheet.Cells[3, 12].Value = dt.Rows[0]["print_time"].ToString();
            exSheet.Cells[3, 6].Value  = "Total :" + dt.Rows.Count + " Room(s).";

            /*exSheet.Cells[2, 1].Select();
             *
             * byte[] MyData = new byte[0];
             * MyData = (byte[])dt.Rows[0]["logo_lotus"];
             * MemoryStream stream = new MemoryStream(MyData);
             * exSheet.Shapes.AddPicture(stream);*/
        }

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

        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range["A6"].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
        double l_ar_total_vnd = 0, l_credit_total_vnd = 0, l_total_cash_vnd = 0, l_transfer_total_vnd = 0;
        float  l_cash_total_usd = 0, l_credit_total_usd = 0;

        int l_cnt = dt.Rows.Count;

        for (int l_row = 1; l_row <= dt.Rows.Count; l_row++)
        {
            for (int col = 0; col <= 14; col++)
            {
                if (col == 0 || col == 1 || col == 2 || col == 3 || col == 4 || col == 5 || col == 6 || col == 7 || col == 14)
                {
                    exSheet.Cells[4 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString();
                    exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
                }
                else
                {
                    if (col == 8 || col == 10 || col == 12 || col == 13)
                    {
                        exSheet.Cells[4 + l_row, col + 1].Value = double.Parse(dt.Rows[l_row - 1][col].ToString());
                        exSheet.Cells[4, col + 1, l_row + 4, col + 1].NumberFormat = "#,##0";
                        exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment      = XlHAlign.xlHAlignRight;
                        if (col == 8)
                        {
                            l_total_cash_vnd = l_total_cash_vnd + double.Parse(dt.Rows[l_row - 1][col].ToString());
                        }
                        if (col == 10)
                        {
                            l_credit_total_vnd = l_credit_total_vnd + double.Parse(dt.Rows[l_row - 1][col].ToString());
                        }
                        if (col == 12)
                        {
                            l_transfer_total_vnd = l_transfer_total_vnd + double.Parse(dt.Rows[l_row - 1][col].ToString());
                        }
                        if (col == 13)
                        {
                            l_ar_total_vnd = l_ar_total_vnd + double.Parse(dt.Rows[l_row - 1][col].ToString());
                        }

                        if (double.Parse(dt.Rows[l_row - 1][col].ToString()) == 0)
                        {
                            exSheet.Cells[4 + l_row, col + 1].Value = "";
                        }
                    }
                    else
                    {
                        exSheet.Cells[4 + l_row, col + 1].Value = float.Parse(dt.Rows[l_row - 1][col].ToString());
                        exSheet.Cells[4, col + 1, l_row + 4, col + 1].NumberFormat = "#,##0.00";
                        exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment      = XlHAlign.xlHAlignRight;

                        if (col == 9)
                        {
                            l_cash_total_usd = l_cash_total_usd + float.Parse(dt.Rows[l_row - 1][col].ToString());
                        }
                        if (col == 11)
                        {
                            l_credit_total_usd = l_credit_total_usd + float.Parse(dt.Rows[l_row - 1][col].ToString());
                        }


                        if (float.Parse(dt.Rows[l_row - 1][col].ToString()) == 0.00)
                        {
                            exSheet.Cells[4 + l_row, col + 1].Value = "";
                        }
                    }
                }
            }
            exSheet.Cells[5 + dt.Rows.Count, 9].Value  = l_total_cash_vnd;
            exSheet.Cells[5 + dt.Rows.Count, 11].Value = l_credit_total_vnd;
            exSheet.Cells[5 + dt.Rows.Count, 13].Value = l_transfer_total_vnd;
            exSheet.Cells[5 + dt.Rows.Count, 14].Value = l_ar_total_vnd;
            exSheet.Cells[5 + dt.Rows.Count, 10].Value = l_cash_total_usd;
            exSheet.Cells[5 + dt.Rows.Count, 12].Value = l_credit_total_usd;
        }

        // 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_60250090_luutru.xls";
        string TempFile     = "../../../../system/temp/rpt_60250090_luutru_" + 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_dfr = Request.QueryString["p_dfr"];
        string p_dto = Request.QueryString["p_dto"];

        string para = "'" + p_dfr + "','" + p_dto + "'";


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

        for (int l_addrow = 0; l_addrow < dtDetail.Rows.Count - 1; l_addrow++)
        {
            exSheet.Range["A9"].Rows.EntireRow.Insert();//insert row new of sheet
        }
        //set detail data
        int pos = 8;
        int i   = 0;

        for (i = 0; i < dtDetail.Rows.Count; i++)
        {
            // exSheet.Range[pos + i, 2,pos + i, 3].Merge();
            //exSheet.Cells[pos + i, 1 ].Value = i+1;
            //exSheet.Cells[pos + i, 2 ].Value = dtDetail.Rows[i]["item_name"]+" "+dtDetail.Rows[i]["item_lname"];
            exSheet.Cells[pos + i, 1].Value = dtDetail.Rows[i]["nation_name"];
            exSheet.Cells[pos + i, 2].Value = dtDetail.Rows[i]["total"];

            exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["male"];
            exSheet.Cells[pos + i, 4].Value = dtDetail.Rows[i]["female"];
            exSheet.Cells[pos + i, 5].Value = dtDetail.Rows[i]["foreign"];
            exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["local_oversea"];
            exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["vietnam"];
            //exSheet.Cells[pos + i, 5 ].Value = dtDetail.Rows[i]["req_uom_01"];
            //exSheet.Cells[pos + i, 6 ].Value = dtDetail.Rows[i]["req_qty_01"];
            //exSheet.Cells[pos + i, 7 ].Value = dtDetail.Rows[i]["pr_stock_qty"];
            //exSheet.Cells[pos + i, 8 ].Value = dtDetail.Rows[i]["description"];
        }
        //---------
        //exSheet.Cells[pos + i, 6 ].Value = "=sum(F10:F"+(pos+i-1)+")";
        //exSheet.Cells[pos + i, 7].Value = "=sum(G10:G"+(pos+i-1)+")";
        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());

        string p_dept_pk       = Request["l_tco_dept_pk"].ToString();
        string p_group_pk      = Request["l_thr_group_pk"].ToString();
        string p_from_dt       = Request["l_from_dt"].ToString();
        string p_to_dt         = Request["l_to_dt"].ToString();
        string p_search        = Request["l_search_by"].ToString();
        string p_input         = Request["l_search_tmp"].ToString();
        string p_meal_emp_type = Request["l_meal_emp_type"].ToString();
        string p_meal_type     = Request["l_meal_type"].ToString();
        string p_meal_times    = Request["l_meal_times"].ToString();

        string TemplateFile = "rpt_chrm00010_04.xls";
        string TempFile     = "../../../../system/temp/rpt_chrm00010_04_" + 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 '','',e.org_nm, " +
              "       (    SELECT g.ORG_NM " +
              "              FROM TCO_ORG g " +
              "             WHERE g.del_if = 0 AND G.ORG_TYPE = '06' " +
              "        START WITH g.pk = e.pk " +
              "        CONNECT BY PRIOR g.p_pk = g.pk) " +
              "          DIV_NM, " +
              "         b.WORKGROUP_NM AS group_nm, " +
              "         a.EMP_ID AS emp_id, " +
              "         a.FULL_NAME AS full_name, " +
              "         to_char(to_date(a.join_dt,'yyyymmdd'),'dd/mm/yyyy') AS join_dt, " +
              "         to_char(to_date(d.WORK_DT_OF_SHIFT,'yyyymmdd'),'dd/mm/yyyy') AS work_dt, " +
              "         d.TIME_IN AS time, " +
              "         (select CODE_NM from vhr_hr_code where id='HR0191' and code =D.MEAL_TIMES) meal_times, " +
              "         decode(NVL(D.MOD_BY_HAND,'N'),'N','NO','YES'), " +
              "         decode(d.meal_emp_type,'IN','Employee of Company','Out of Company') " +
              "    FROM THR_EMPLOYEE A, " +
              "         THR_WORK_GROUP B, " +
              "         THR_MEAL_DUPLICATE D, " +
              "         tco_org e " +
              "   WHERE     A.DEL_IF = 0 AND D.DEL_IF = 0 " +
              "         AND B.DEL_IF = 0 " +
              "         AND e.del_if = 0 " +
              "         AND A.THR_WG_PK = B.PK " +
              "         AND A.pk = D.thr_emp_pk " +
              "         AND a.tco_org_pk = e.pk " +
              "         AND D.WORK_DT_OF_SHIFT BETWEEN '" + p_from_dt + "' AND '" + p_to_dt + "' " +
              "         AND D.MEAL_EMP_TYPE = 'IN' " +
              "         AND DECODE ('" + p_group_pk + "', 'ALL', 'ALL', B.PK) = '" + p_group_pk + "' " +
              "         AND  A.TCO_ORG_PK IN " +
              "                    (    SELECT g.pk " +
              "                           FROM TCO_ORG g " +
              "                          WHERE g.del_if = 0 " +
              "                     START WITH g.pk = case when '" + p_dept_pk + "' = 'ALL' then g.pk else to_number('" + p_dept_pk + "') end " +
              "                     CONNECT BY PRIOR g.pk = g.p_pk) " +
              "         AND (   DECODE ('" + p_search + "', " +
              "                         '1', UPPER (A.EMP_ID), " +
              "                         '2', A.ID_NUM, " +
              "                         '3', UPPER (A.FULL_NAME)) LIKE " +
              "                    '%' || UPPER ('" + p_input + "') || '%' " +
              "              OR '" + p_input + "' IS NULL) " +
              "         AND DECODE ('" + p_meal_emp_type + "', 'ALL', 'ALL', d.meal_emp_type) = " +
              "                '" + p_meal_emp_type + "' " +
              "         AND DECODE ('" + p_meal_type + "', 'ALL', 'ALL', D.MEAL_TYPE) = '" + p_meal_type + "' " +
              "         AND DECODE ('" + p_meal_times + "', 'ALL', 'ALL', D.MEAL_TIMES) = '" + p_meal_times + "' " +
              "    UNION ALL " +
              "    SELECT '','','' DIV_NM,'' org_nm, " +
              "         '' AS group_nm, " +
              "         d.EMP_ID AS emp_id, " +
              "         D.FULL_NAME AS full_name, " +
              "         '' AS join_dt, " +
              "         to_char(to_date(d.WORK_DT_OF_SHIFT,'yyyymmdd'),'dd/mm/yyyy') AS work_dt, " +
              "         d.TIME_IN AS time, " +
              "         (select CODE_NM from vhr_hr_code where id='HR0191' and code =D.MEAL_TIMES) meal_times, " +
              "         decode(NVL(D.MOD_BY_HAND,'N'),'N','NO','YES'), " +
              "         decode(d.meal_emp_type,'IN','Employee of Company','Out of Company') " +
              "    FROM THR_MEAL_DUPLICATE D " +
              "   WHERE     D.DEL_IF = 0 " +
              "         AND D.WORK_DT_OF_SHIFT BETWEEN '" + p_from_dt + "' AND '" + p_to_dt + "' " +
              "         AND D.MEAL_EMP_TYPE = 'OUT' " +
              "         AND (   DECODE ('" + p_search + "', " +
              "                         '1', UPPER (D.EMP_ID), " +
              "                         '3', UPPER (D.FULL_NAME)) LIKE " +
              "                    '%' || UPPER ('" + p_input + "') || '%' " +
              "              OR '" + p_input + "' IS NULL) " +
              "         AND DECODE ('" + p_meal_emp_type + "', 'ALL', 'ALL', d.meal_emp_type) = " +
              "                '" + p_meal_emp_type + "' " +
              "         AND DECODE ('" + p_meal_type + "', 'ALL', 'ALL', D.MEAL_TYPE) = '" + p_meal_type + "' " +
              "         AND DECODE ('" + p_meal_times + "', 'ALL', 'ALL', D.MEAL_TIMES) = '" + p_meal_times + "'      " +
              "ORDER BY DIV_NM,org_nm,group_nm,emp_id,work_dt ";

        //Response.Write(SQL);
        //Response.End();
        DataTable dt_emp = CtlLib.TableReadOpen(SQL);

        int irow = dt_emp.Rows.Count;
        int icol = dt_emp.Columns.Count;

        if (irow == 0)
        {
            Response.Write("There is no data.");
            Response.End();
        }



        //Khai bao bien
        int i, j, pos = 5;

        //Insert new row

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

        //Title value
        exSheet.Cells["F2"].Value = p_from_dt.Substring(6, 2).ToString() + "/" + p_from_dt.Substring(4, 2).ToString() + "/" + p_from_dt.Substring(0, 4).ToString();
        exSheet.Cells["I2"].Value = p_to_dt.Substring(6, 2).ToString() + "/" + p_to_dt.Substring(4, 2).ToString() + "/" + p_to_dt.Substring(0, 4).ToString();

        //Show data
        for (i = 0; i < irow; i++)
        {
            exSheet.Cells[pos + i, 1].Value = i + 1;
            for (j = 2; j < icol; j++)
            {
                exSheet.Cells[pos + i, j].Value = dt_emp.Rows[i][j].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();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        // string p_dept_pk = Request["l_tco_dept_pk"].ToString();
        // string p_group_pk = Request["l_thr_group_pk"].ToString();
        string p_from_dt = Request["l_from_dt"].ToString();
        string p_to_dt   = Request["l_to_dt"].ToString();
        //string p_search = Request["l_search_by"].ToString();
        //string p_input = Request["l_search_tmp"].ToString();
        // string p_meal_emp_type = Request["l_meal_emp_type"].ToString();
        // string p_meal_type = Request["l_meal_type"].ToString();
        // string p_meal_times = Request["l_meal_times"].ToString();

        string TemplateFile = "rpt_chrm00020_5.xls";
        string TempFile     = "../../../../system/temp/rpt_chrm00020_5_" + 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 1,DIV_SEQ,ORG_SEQ,DIV_0,PL_CD,SUM(MEAL_01),SUM(MEAL_02),SUM(MEAL_03),SUM(MEAL_04),SUM(MEAL_05),SUM(TOTAL)  " +
              "FROM (   " +
              "SELECT 1, NVL(DIV_SEQ,99999999) DIV_SEQ,NVL(ORG_SEQ,99999999) ORG_SEQ,NVL(DIV_0,99999999) DIV_0 ,PL_CD " +
              ",SUM(MEAL_01) MEAL_01,SUM(MEAL_02) MEAL_02,SUM(MEAL_03) MEAL_03,SUM(MEAL_04) MEAL_04  " +
              ",SUM(MEAL_05) MEAL_05,SUM(TOTAL) TOTAL  " +
              "FROM(  " +
              " SELECT (    SELECT g.seq   " +
              "              FROM TCO_ORG g   " +
              "             WHERE g.del_if = 0 AND G.ORG_TYPE = '03'   " +
              "        START WITH g.pk = e.pk   " +
              "        CONNECT BY PRIOR g.p_pk = g.pk) DIV_seq,  " +
              " nvl((SELECT g.seq   " +
              "              FROM TCO_ORG g   " +
              "             WHERE g.del_if = 0 AND G.ORG_TYPE = '07'   " +
              "        START WITH g.pk = e.pk   " +
              "        CONNECT BY PRIOR g.p_pk = g.pk),e.seq) ORG_seq,  " +
              " nvl((SELECT g.ORG_NM   " +
              "              FROM TCO_ORG g   " +
              "             WHERE g.del_if = 0 AND G.ORG_TYPE = '07'   " +
              "        START WITH g.pk = e.pk   " +
              "        CONNECT BY PRIOR g.p_pk = g.pk),e.org_nm) DIV_0  " +
              " ,P.PL_CD        " +
              " ,NVL(MEAL.MEAL_01,0) MEAL_01,NVL(MEAL.MEAL_02,0) MEAL_02,NVL(MEAL.MEAL_03,0) MEAL_03,NVL(MEAL.MEAL_04,0) MEAL_04  " +
              " ,NVL(MEAL.MEAL_05,0) MEAL_05           " +
              " ,NVL(MEAL.MEAL_01,0)+NVL(MEAL.MEAL_02,0)+NVL(MEAL.MEAL_03,0)+NVL(MEAL.MEAL_04,0)+NVL(MEAL.MEAL_05,0) TOTAL         " +
              " FROM THR_EMPLOYEE A,THR_WG_SCH_DETAIL D,tco_org e, tac_abpl p  " +
              "    ,(SELECT M1.THR_EMP_PK,   " +
              "               M1.WORK_DT_OF_SHIFT,   " +
              "               SUM(CASE WHEN M1.MEAL_TIMES = '01' THEN 1 ELSE 0 END) MEAL_01,   " +
              "               SUM (CASE WHEN M1.MEAL_TIMES = '02' THEN 1 ELSE 0 END) MEAL_02,   " +
              "               SUM (CASE WHEN M1.MEAL_TIMES = '03' THEN 1 ELSE 0 END) MEAL_03,   " +
              "               SUM (CASE WHEN M1.MEAL_TIMES = '04' THEN 1 ELSE 0 END) MEAL_04,   " +
              "               SUM (CASE WHEN M1.MEAL_TIMES = '05' THEN 1 ELSE 0 END) MEAL_05   " +
              "               FROM THR_MEAL_CHECKING M1    " +
              "               WHERE M1.DEL_IF=0    " +
              "               AND M1.WORK_DT_OF_SHIFT BETWEEN '" + p_from_dt + "' AND '" + p_to_dt + "'  AND M1.MEAL_EMP_TYPE='IN'  " +
              "               AND M1.THR_EMP_PK IS NOT NULL    " +
              "               GROUP BY M1.THR_EMP_PK,M1.WORK_DT_OF_SHIFT) MEAL  " +
              " WHERE A.DEL_IF=0 AND D.DEL_IF=0 AND E.DEL_IF=0 and p.del_if =0   " +
              " AND A.TCO_ORG_PK=E.PK AND A.PK=D.THR_EMP_PK AND A.NATION='01' and A.TAC_ABPL_PK = p.pk " +
              "AND D.WORK_DT BETWEEN '" + p_from_dt + "' AND '" + p_to_dt + "' AND D.THR_EMP_PK=MEAL.THR_EMP_PK(+) AND D.WORK_DT=MEAL.WORK_DT_OF_SHIFT(+)  " +
              "AND A.JOIN_DT<='" + p_from_dt + "') dorco2  " +
              "GROUP BY DIV_0,DIV_SEQ,ORG_SEQ ,PL_CD " +
              "UNION ALL  " +
              "SELECT 2,0,0, 'OUT OF COMPANY','',   " +
              "SUM(CASE WHEN M1.MEAL_TIMES = '01' THEN 1 ELSE 0 END) MEAL_01,   " +
              "SUM(CASE WHEN M1.MEAL_TIMES = '02' THEN 1 ELSE 0 END) MEAL_02,   " +
              "SUM(CASE WHEN M1.MEAL_TIMES = '03' THEN 1 ELSE 0 END) MEAL_03,   " +
              "SUM(CASE WHEN M1.MEAL_TIMES = '04' THEN 1 ELSE 0 END) MEAL_04,   " +
              "0 MEAL_05,COUNT(*) TOTAL   " +
              "FROM THR_MEAL_CHECKING M1    " +
              "WHERE M1.DEL_IF=0    " +
              "AND M1.WORK_DT_OF_SHIFT BETWEEN '" + p_from_dt + "' AND '" + p_to_dt + "' AND M1.MEAL_EMP_TYPE='OUT'  " +
              "GROUP BY M1.WORK_DT_OF_SHIFT  " +
              "UNION ALL  " +
              "SELECT 3,0,0, 'VISITOR','',   " +
              "SUM(NVL(M1.MEAL_QTY,1)*(CASE WHEN M1.MEAL_TIMES = '01' THEN 1 ELSE 0 END)) MEAL_01,   " +
              "SUM(NVL(M1.MEAL_QTY,1)*(CASE WHEN M1.MEAL_TIMES = '02' THEN 1 ELSE 0 END)) MEAL_02,   " +
              "SUM(NVL(M1.MEAL_QTY,1)*(CASE WHEN M1.MEAL_TIMES = '03' THEN 1 ELSE 0 END)) MEAL_03,   " +
              "SUM(NVL(M1.MEAL_QTY,1)*(CASE WHEN M1.MEAL_TIMES = '04' THEN 1 ELSE 0 END)) MEAL_04,   " +
              "0 MEAL_05,SUM(NVL(M1.MEAL_QTY,1)) TOTAL   " +
              "FROM THR_MEAL_VISITOR M1    " +
              "WHERE M1.DEL_IF=0    " +
              "AND M1.WORK_DT_OF_SHIFT BETWEEN '" + p_from_dt + "' AND '" + p_to_dt + "' " +
              "GROUP BY M1.WORK_DT_OF_SHIFT  " +
              "UNION ALL  " +
              "SELECT 4,0,0, 'OTHER','',   " +
              "SUM(NVL(M1.MEAL_QTY,1)*(CASE WHEN M1.MEAL_TIMES = '01' THEN 1 ELSE 0 END)) MEAL_01,   " +
              "SUM(NVL(M1.MEAL_QTY,1)*(CASE WHEN M1.MEAL_TIMES = '02' THEN 1 ELSE 0 END)) MEAL_02,   " +
              "SUM(NVL(M1.MEAL_QTY,1)*(CASE WHEN M1.MEAL_TIMES = '03' THEN 1 ELSE 0 END)) MEAL_03,   " +
              "SUM(NVL(M1.MEAL_QTY,1)*(CASE WHEN M1.MEAL_TIMES = '04' THEN 1 ELSE 0 END)) MEAL_04,   " +
              "0 MEAL_05,SUM(NVL(M1.MEAL_QTY,1)) TOTAL   " +
              "FROM THR_MEAL_OTHER M1    " +
              "WHERE M1.DEL_IF=0    " +
              "AND M1.WORK_DT_OF_SHIFT BETWEEN '" + p_from_dt + "' AND '" + p_to_dt + "' " +
              "GROUP BY M1.WORK_DT_OF_SHIFT)  " +
              " GROUP BY 1,DIV_SEQ,ORG_SEQ,DIV_0,PL_CD  " +
              "ORDER BY 1,2,3  ";


        //Response.Write(SQL);
        //Response.End();
        DataTable dt_emp = CtlLib.TableReadOpen(SQL);

        int irow = dt_emp.Rows.Count;
        int icol = dt_emp.Columns.Count;

        if (irow == 0)
        {
            Response.Write("There is no data.");
            Response.End();
        }



        //Khai bao bien
        int i, j, pos = 5;

        double[] grant_total = new double[icol + 1];

        //Insert new row

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

        //Title value
        exSheet.Cells["D2"].Value = p_from_dt.Substring(6, 2).ToString() + "/" + p_from_dt.Substring(4, 2).ToString() + "/" + p_from_dt.Substring(0, 4).ToString()
                                    + " ~ " + p_to_dt.Substring(6, 2).ToString() + "/" + p_to_dt.Substring(4, 2).ToString() + "/" + p_to_dt.Substring(0, 4).ToString();


        //Show data
        for (i = 0; i < irow; i++)
        {
            exSheet.Cells[pos + i, 1].Value = i + 1;
            for (j = 3; j < icol; j++)
            {
                if (j >= 5)
                {
                    exSheet.Cells[pos + i, j - 1].Value = double.Parse(dt_emp.Rows[i][j].ToString());
                    grant_total[j] += double.Parse(dt_emp.Rows[i][j].ToString());
                }
                else
                {
                    exSheet.Cells[pos + i, j - 1].Value = dt_emp.Rows[i][j].ToString();
                }
            }
        }

        for (j = 3; j < icol; j++)
        {
            exSheet.Cells[pos + irow, j - 1].Value = grant_total[j];
        }



        // 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();
    }
예제 #19
0
    protected void Page_Load(object sender, EventArgs e)
    {
        //CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        CtlLib.SetUser("SALE");

        string TemplateFile = "rpt_bisc00171_SG01.xls";
        string TempFile     = "../../../../system/temp/rpt_bisc00171_SG01_" + 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_lang          = Request.QueryString["p_lang"];
        string p_wh_name       = Request.QueryString["p_wh_name"];
        string p_wh_pk         = Request.QueryString["p_wh_pk"];
        string p_item_group_pk = Request.QueryString["p_item_group_pk"];
        string p_item          = Request.QueryString["p_item"];
        string p_date          = Request.QueryString["p_date"];
        string p_type          = Request.QueryString["p_type"];

        DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00171_SG01", "'" + Session["User_ID"].ToString() + "'");

        string    para     = "'" + p_date + "'";
        DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00171_SG01_1", para);

        para = "'" + p_lang + "','" + p_wh_pk + "','" + p_item_group_pk + "','" + p_item + "','" + p_date + "','" + p_type + "'";
        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00171_SG01_2", para);

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

            Bitmap b = new Bitmap(stream);
            exSheet.Shapes.AddPicture(ResizeImage(b, 100, 55));
        }

        exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"].ToString();

        exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"].ToString();

        exSheet.Cells["C3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString();

        exSheet.Cells["I1"].Value = "Tell: " + dtInfo.Rows[0]["phone_no"].ToString();

        exSheet.Cells["I1"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();

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

        exSheet.Cells["F5"].Value = System.DateTime.ParseExact(p_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");

        exSheet.Cells["C5"].Value = p_wh_name;
        //---------

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

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

        if (dtMaster.Rows.Count > 0)
        {
            exSheet.Cells["F6"].Value  = dtMaster.Rows[0]["date01"];
            exSheet.Cells["I6"].Value  = dtMaster.Rows[0]["date02"];
            exSheet.Cells["L6"].Value  = dtMaster.Rows[0]["date03"];
            exSheet.Cells["O6"].Value  = dtMaster.Rows[0]["date04"];
            exSheet.Cells["R6"].Value  = dtMaster.Rows[0]["date05"];
            exSheet.Cells["U6"].Value  = dtMaster.Rows[0]["date06"];
            exSheet.Cells["X6"].Value  = dtMaster.Rows[0]["date07"];
            exSheet.Cells["AA6"].Value = dtMaster.Rows[0]["date08"];
            exSheet.Cells["AD6"].Value = dtMaster.Rows[0]["date09"];
            exSheet.Cells["AG6"].Value = dtMaster.Rows[0]["date10"];
            exSheet.Cells["AJ6"].Value = dtMaster.Rows[0]["date11"];
            exSheet.Cells["AM6"].Value = dtMaster.Rows[0]["date12"];
            exSheet.Cells["AP6"].Value = dtMaster.Rows[0]["date13"];
            exSheet.Cells["AS6"].Value = dtMaster.Rows[0]["date14"];
            exSheet.Cells["AV6"].Value = dtMaster.Rows[0]["date15"];
            exSheet.Cells["AY6"].Value = dtMaster.Rows[0]["date16"];
            exSheet.Cells["BB6"].Value = dtMaster.Rows[0]["date17"];
            exSheet.Cells["BE6"].Value = dtMaster.Rows[0]["date18"];
            exSheet.Cells["BH6"].Value = dtMaster.Rows[0]["date19"];
            exSheet.Cells["BK6"].Value = dtMaster.Rows[0]["date20"];
            exSheet.Cells["BN6"].Value = dtMaster.Rows[0]["date21"];
            exSheet.Cells["BQ6"].Value = dtMaster.Rows[0]["date22"];
            exSheet.Cells["BT6"].Value = dtMaster.Rows[0]["date23"];
            exSheet.Cells["BW6"].Value = dtMaster.Rows[0]["date24"];
            exSheet.Cells["BZ6"].Value = dtMaster.Rows[0]["date25"];
            exSheet.Cells["CC6"].Value = dtMaster.Rows[0]["date26"];
            exSheet.Cells["CF6"].Value = dtMaster.Rows[0]["date27"];
            exSheet.Cells["CI6"].Value = dtMaster.Rows[0]["date28"];
            exSheet.Cells["CL6"].Value = dtMaster.Rows[0]["date29"];
            exSheet.Cells["CO6"].Value = dtMaster.Rows[0]["date30"];
            exSheet.Cells["CR6"].Value = dtMaster.Rows[0]["date31"];
        }
        //--------------------------
        int l_pos = 8;

        for (int 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_code"];
            exSheet.Cells[l_pos + i, 3].Value = dtDetail.Rows[i]["item_name"];

            exSheet.Cells[l_pos + i, 4].Value = dtDetail.Rows[i]["uom"];

            exSheet.Cells[l_pos + i, 5].Value = dtDetail.Rows[i]["begin_qty"];

            exSheet.Cells[l_pos + i, 6].Value = dtDetail.Rows[i]["in_qty_01"];
            exSheet.Cells[l_pos + i, 7].Value = dtDetail.Rows[i]["out_qty_01"];
            exSheet.Cells[l_pos + i, 8].Value = "=E" + (l_pos + i) + "+F" + (l_pos + i) + "-G" + (l_pos + i);

            exSheet.Cells[l_pos + i, 9].Value  = dtDetail.Rows[i]["in_qty_02"];
            exSheet.Cells[l_pos + i, 10].Value = dtDetail.Rows[i]["out_qty_02"];
            exSheet.Cells[l_pos + i, 11].Value = "=H" + (l_pos + i) + "+I" + (l_pos + i) + "-J" + (l_pos + i);

            exSheet.Cells[l_pos + i, 12].Value = dtDetail.Rows[i]["in_qty_03"];
            exSheet.Cells[l_pos + i, 13].Value = dtDetail.Rows[i]["out_qty_03"];
            exSheet.Cells[l_pos + i, 14].Value = "=K" + (l_pos + i) + "+L" + (l_pos + i) + "-M" + (l_pos + i);

            exSheet.Cells[l_pos + i, 15].Value = dtDetail.Rows[i]["in_qty_04"];
            exSheet.Cells[l_pos + i, 16].Value = dtDetail.Rows[i]["out_qty_04"];
            exSheet.Cells[l_pos + i, 17].Value = "=N" + (l_pos + i) + "+O" + (l_pos + i) + "-P" + (l_pos + i);

            exSheet.Cells[l_pos + i, 18].Value = dtDetail.Rows[i]["in_qty_05"];
            exSheet.Cells[l_pos + i, 19].Value = dtDetail.Rows[i]["out_qty_05"];
            exSheet.Cells[l_pos + i, 20].Value = "=Q" + (l_pos + i) + "+R" + (l_pos + i) + "-S" + (l_pos + i);

            exSheet.Cells[l_pos + i, 21].Value = dtDetail.Rows[i]["in_qty_06"];
            exSheet.Cells[l_pos + i, 22].Value = dtDetail.Rows[i]["out_qty_06"];
            exSheet.Cells[l_pos + i, 23].Value = "=T" + (l_pos + i) + "+U" + (l_pos + i) + "-V" + (l_pos + i);

            exSheet.Cells[l_pos + i, 24].Value = dtDetail.Rows[i]["in_qty_07"];
            exSheet.Cells[l_pos + i, 25].Value = dtDetail.Rows[i]["out_qty_07"];
            exSheet.Cells[l_pos + i, 26].Value = "=W" + (l_pos + i) + "+X" + (l_pos + i) + "-Y" + (l_pos + i);

            exSheet.Cells[l_pos + i, 27].Value = dtDetail.Rows[i]["in_qty_08"];
            exSheet.Cells[l_pos + i, 28].Value = dtDetail.Rows[i]["out_qty_08"];
            exSheet.Cells[l_pos + i, 29].Value = "=Z" + (l_pos + i) + "+AA" + (l_pos + i) + "-AB" + (l_pos + i);

            exSheet.Cells[l_pos + i, 30].Value = dtDetail.Rows[i]["in_qty_09"];
            exSheet.Cells[l_pos + i, 31].Value = dtDetail.Rows[i]["out_qty_09"];
            exSheet.Cells[l_pos + i, 32].Value = "=AC" + (l_pos + i) + "+AD" + (l_pos + i) + "-AE" + (l_pos + i);

            exSheet.Cells[l_pos + i, 33].Value = dtDetail.Rows[i]["in_qty_10"];
            exSheet.Cells[l_pos + i, 34].Value = dtDetail.Rows[i]["out_qty_10"];
            exSheet.Cells[l_pos + i, 35].Value = "=AF" + (l_pos + i) + "+AG" + (l_pos + i) + "-AH" + (l_pos + i);

            exSheet.Cells[l_pos + i, 36].Value = dtDetail.Rows[i]["in_qty_11"];
            exSheet.Cells[l_pos + i, 37].Value = dtDetail.Rows[i]["out_qty_11"];
            exSheet.Cells[l_pos + i, 38].Value = "=AI" + (l_pos + i) + "+AJ" + (l_pos + i) + "-AK" + (l_pos + i);

            exSheet.Cells[l_pos + i, 39].Value = dtDetail.Rows[i]["in_qty_12"];
            exSheet.Cells[l_pos + i, 40].Value = dtDetail.Rows[i]["out_qty_12"];
            exSheet.Cells[l_pos + i, 41].Value = "=AL" + (l_pos + i) + "+AM" + (l_pos + i) + "-AN" + (l_pos + i);

            exSheet.Cells[l_pos + i, 42].Value = dtDetail.Rows[i]["in_qty_13"];
            exSheet.Cells[l_pos + i, 43].Value = dtDetail.Rows[i]["out_qty_13"];
            exSheet.Cells[l_pos + i, 44].Value = "=AO" + (l_pos + i) + "+AP" + (l_pos + i) + "-AQ" + (l_pos + i);

            exSheet.Cells[l_pos + i, 45].Value = dtDetail.Rows[i]["in_qty_14"];
            exSheet.Cells[l_pos + i, 46].Value = dtDetail.Rows[i]["out_qty_14"];
            exSheet.Cells[l_pos + i, 47].Value = "=AR" + (l_pos + i) + "+AS" + (l_pos + i) + "-AT" + (l_pos + i);

            exSheet.Cells[l_pos + i, 48].Value = dtDetail.Rows[i]["in_qty_15"];
            exSheet.Cells[l_pos + i, 49].Value = dtDetail.Rows[i]["out_qty_15"];
            exSheet.Cells[l_pos + i, 50].Value = "=AU" + (l_pos + i) + "+AV" + (l_pos + i) + "-AW" + (l_pos + i);

            exSheet.Cells[l_pos + i, 51].Value = dtDetail.Rows[i]["in_qty_16"];
            exSheet.Cells[l_pos + i, 52].Value = dtDetail.Rows[i]["out_qty_16"];
            exSheet.Cells[l_pos + i, 53].Value = "=AX" + (l_pos + i) + "+AY" + (l_pos + i) + "-AZ" + (l_pos + i);

            exSheet.Cells[l_pos + i, 54].Value = dtDetail.Rows[i]["in_qty_17"];
            exSheet.Cells[l_pos + i, 55].Value = dtDetail.Rows[i]["out_qty_17"];
            exSheet.Cells[l_pos + i, 56].Value = "=BA" + (l_pos + i) + "+BB" + (l_pos + i) + "-BC" + (l_pos + i);

            exSheet.Cells[l_pos + i, 57].Value = dtDetail.Rows[i]["in_qty_18"];
            exSheet.Cells[l_pos + i, 58].Value = dtDetail.Rows[i]["out_qty_18"];
            exSheet.Cells[l_pos + i, 59].Value = "=BD" + (l_pos + i) + "+BE" + (l_pos + i) + "-BF" + (l_pos + i);

            exSheet.Cells[l_pos + i, 60].Value = dtDetail.Rows[i]["in_qty_19"];
            exSheet.Cells[l_pos + i, 61].Value = dtDetail.Rows[i]["out_qty_19"];
            exSheet.Cells[l_pos + i, 62].Value = "=BG" + (l_pos + i) + "+BH" + (l_pos + i) + "-BI" + (l_pos + i);

            exSheet.Cells[l_pos + i, 63].Value = dtDetail.Rows[i]["in_qty_20"];
            exSheet.Cells[l_pos + i, 64].Value = dtDetail.Rows[i]["out_qty_20"];
            exSheet.Cells[l_pos + i, 65].Value = "=BJ" + (l_pos + i) + "+BK" + (l_pos + i) + "-BL" + (l_pos + i);

            exSheet.Cells[l_pos + i, 66].Value = dtDetail.Rows[i]["in_qty_21"];
            exSheet.Cells[l_pos + i, 67].Value = dtDetail.Rows[i]["out_qty_21"];
            exSheet.Cells[l_pos + i, 68].Value = "=BM" + (l_pos + i) + "+BN" + (l_pos + i) + "-BO" + (l_pos + i);

            exSheet.Cells[l_pos + i, 69].Value = dtDetail.Rows[i]["in_qty_22"];
            exSheet.Cells[l_pos + i, 70].Value = dtDetail.Rows[i]["out_qty_22"];
            exSheet.Cells[l_pos + i, 71].Value = "=BP" + (l_pos + i) + "+BQ" + (l_pos + i) + "-BR" + (l_pos + i);

            exSheet.Cells[l_pos + i, 72].Value = dtDetail.Rows[i]["in_qty_23"];
            exSheet.Cells[l_pos + i, 73].Value = dtDetail.Rows[i]["out_qty_23"];
            exSheet.Cells[l_pos + i, 74].Value = "=BS" + (l_pos + i) + "+BT" + (l_pos + i) + "-BU" + (l_pos + i);

            exSheet.Cells[l_pos + i, 75].Value = dtDetail.Rows[i]["in_qty_24"];
            exSheet.Cells[l_pos + i, 76].Value = dtDetail.Rows[i]["out_qty_24"];
            exSheet.Cells[l_pos + i, 77].Value = "=BV" + (l_pos + i) + "+BW" + (l_pos + i) + "-BX" + (l_pos + i);

            exSheet.Cells[l_pos + i, 78].Value = dtDetail.Rows[i]["in_qty_25"];
            exSheet.Cells[l_pos + i, 79].Value = dtDetail.Rows[i]["out_qty_25"];
            exSheet.Cells[l_pos + i, 80].Value = "=BY" + (l_pos + i) + "+BZ" + (l_pos + i) + "-CA" + (l_pos + i);

            exSheet.Cells[l_pos + i, 81].Value = dtDetail.Rows[i]["in_qty_26"];
            exSheet.Cells[l_pos + i, 82].Value = dtDetail.Rows[i]["out_qty_26"];
            exSheet.Cells[l_pos + i, 83].Value = "=CB" + (l_pos + i) + "+CC" + (l_pos + i) + "-CD" + (l_pos + i);

            exSheet.Cells[l_pos + i, 84].Value = dtDetail.Rows[i]["in_qty_27"];
            exSheet.Cells[l_pos + i, 85].Value = dtDetail.Rows[i]["out_qty_27"];
            exSheet.Cells[l_pos + i, 86].Value = "=CE" + (l_pos + i) + "+CF" + (l_pos + i) + "-CG" + (l_pos + i);

            exSheet.Cells[l_pos + i, 87].Value = dtDetail.Rows[i]["in_qty_28"];
            exSheet.Cells[l_pos + i, 88].Value = dtDetail.Rows[i]["out_qty_28"];
            exSheet.Cells[l_pos + i, 89].Value = "=CH" + (l_pos + i) + "+CI" + (l_pos + i) + "-CJ" + (l_pos + i);

            exSheet.Cells[l_pos + i, 90].Value = dtDetail.Rows[i]["in_qty_29"];
            exSheet.Cells[l_pos + i, 91].Value = dtDetail.Rows[i]["out_qty_29"];
            exSheet.Cells[l_pos + i, 92].Value = "=CK" + (l_pos + i) + "+CL" + (l_pos + i) + "-CM" + (l_pos + i);

            exSheet.Cells[l_pos + i, 93].Value = dtDetail.Rows[i]["in_qty_30"];
            exSheet.Cells[l_pos + i, 94].Value = dtDetail.Rows[i]["out_qty_30"];
            exSheet.Cells[l_pos + i, 95].Value = "=CN" + (l_pos + i) + "+CO" + (l_pos + i) + "-CP" + (l_pos + i);

            exSheet.Cells[l_pos + i, 96].Value = dtDetail.Rows[i]["in_qty_31"];
            exSheet.Cells[l_pos + i, 97].Value = dtDetail.Rows[i]["out_qty_31"];
            exSheet.Cells[l_pos + i, 98].Value = "=CQ" + (l_pos + i) + "+CR" + (l_pos + i) + "-CS" + (l_pos + i);

            exSheet.Cells[l_pos + i, 99].Value = dtDetail.Rows[i]["end_qty"];
        }

        exSheet.Cells[dtDetail.Rows.Count + 8, 5].Value = "=SUM(E8:E" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 6].Value = "=SUM(F8:F" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 7].Value = "=SUM(G8:G" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 8].Value = "=SUM(H8:H" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 9].Value  = "=SUM(I8:I" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 10].Value = "=SUM(J8:J" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 11].Value = "=SUM(K8:K" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 12].Value = "=SUM(L8:L" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 13].Value = "=SUM(M8:M" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 14].Value = "=SUM(N8:N" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 15].Value = "=SUM(O8:O" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 16].Value = "=SUM(P8:P" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 17].Value = "=SUM(Q8:Q" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 18].Value = "=SUM(R8:R" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 19].Value = "=SUM(S8:S" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 20].Value = "=SUM(T8:T" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 21].Value = "=SUM(U8:U" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 22].Value = "=SUM(V8:V" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 23].Value = "=SUM(W8:W" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 24].Value = "=SUM(X8:X" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 25].Value = "=SUM(Y8:Y" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 26].Value = "=SUM(Z8:Z" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 27].Value = "=SUM(AA8:AA" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 28].Value = "=SUM(AB8:AB" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 29].Value = "=SUM(AC8:AC" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 30].Value = "=SUM(AD8:AD" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 31].Value = "=SUM(AE8:AE" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 32].Value = "=SUM(AF8:AF" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 33].Value = "=SUM(AG8:AG" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 34].Value = "=SUM(AH8:AH" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 35].Value = "=SUM(AI8:AI" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 36].Value = "=SUM(AJ8:AJ" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 37].Value = "=SUM(AK8:AK" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 38].Value = "=SUM(AL8:AL" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 39].Value = "=SUM(AM8:AM" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 40].Value = "=SUM(AN8:AN" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 41].Value = "=SUM(AO8:AO" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 42].Value = "=SUM(AP8:AP" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 43].Value = "=SUM(AQ8:AQ" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 44].Value = "=SUM(AR8:AR" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 45].Value = "=SUM(AS8:AS" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 46].Value = "=SUM(AT8:AT" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 47].Value = "=SUM(AU8:AU" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 48].Value = "=SUM(AV8:AV" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 49].Value = "=SUM(AW8:AW" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 50].Value = "=SUM(AX8:AX" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 51].Value = "=SUM(AY8:AY" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 52].Value = "=SUM(AZ8:AZ" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 53].Value = "=SUM(BA8:BA" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 54].Value = "=SUM(BB8:BB" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 55].Value = "=SUM(BC8:BC" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 56].Value = "=SUM(BD8:BD" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 57].Value = "=SUM(BE8:BE" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 58].Value = "=SUM(BF8:BF" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 59].Value = "=SUM(BG8:BG" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 60].Value = "=SUM(BH8:BH" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 61].Value = "=SUM(BI8:BI" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 62].Value = "=SUM(BJ8:BJ" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 63].Value = "=SUM(BK8:BK" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 64].Value = "=SUM(BL8:BL" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 65].Value = "=SUM(BM8:BM" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 66].Value = "=SUM(BN8:BN" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 67].Value = "=SUM(BO8:BO" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 68].Value = "=SUM(BP8:BP" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 69].Value = "=SUM(BQ8:BQ" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 70].Value = "=SUM(BR8:BR" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 71].Value = "=SUM(BS8:BS" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 72].Value = "=SUM(BT8:BT" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 73].Value = "=SUM(BU8:BU" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 74].Value = "=SUM(BV8:BV" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 75].Value = "=SUM(BW8:BW" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 76].Value = "=SUM(BS8:BS" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 77].Value = "=SUM(BY8:BY" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 78].Value = "=SUM(BZ8:BZ" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 79].Value = "=SUM(CA8:CA" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 80].Value = "=SUM(CB8:CB" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 81].Value = "=SUM(CC8:CC" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 82].Value = "=SUM(CD8:CD" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 83].Value = "=SUM(CE8:CE" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 84].Value = "=SUM(CF8:CF" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 85].Value = "=SUM(CG8:CG" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 86].Value = "=SUM(CH8:CH" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 87].Value = "=SUM(CI8:CI" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 88].Value = "=SUM(CJ8:CJ" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 89].Value = "=SUM(CK8:CK" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 90].Value = "=SUM(CL8:CL" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 91].Value = "=SUM(CM8:CM" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 92].Value = "=SUM(CN8:CN" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 93].Value = "=SUM(CO8:CO" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 94].Value = "=SUM(CP8:CP" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 95].Value = "=SUM(CQ8:CQ" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 96].Value = "=SUM(CR8:CR" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 97].Value = "=SUM(CS8:CS" + (dtDetail.Rows.Count + 7) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 8, 98].Value = "=SUM(CT8:CT" + (dtDetail.Rows.Count + 7) + ")";

        exSheet.Cells[dtDetail.Rows.Count + 8, 99].Value = "=SUM(CU8:CU" + (dtDetail.Rows.Count + 7) + ")";

        // 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

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = true;*/

        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("SALE");

        string TemplateFile = "rpt_dsbs00063.xls";
        string TempFile     = "../../../../system/temp/rpt_dsbs00063_" + 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_company_pk = Request.QueryString["p_company_pk"];
        string p_date_type  = Request.QueryString["p_date_type"];
        string p_from_dt    = Request.QueryString["p_from_dt"];
        string p_to_dt      = Request.QueryString["p_to_dt"];
        string p_po_item    = Request.QueryString["p_po_item"];
        string p_order_type = Request.QueryString["p_order_type"];
        string p_bill_to    = Request.QueryString["p_bill_to"];

        string    para     = "'" + p_from_dt + "','" + p_to_dt + "'";
        DataTable dtHeader = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00063_1", para);

        para = "'" + p_company_pk + "','" + p_date_type + "','" + p_from_dt + "','" + p_to_dt + "','" + p_po_item + "','" + p_order_type + "','" + p_bill_to + "'";
        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00063", para);

        // MASTER

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

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

        exSheet.Cells["C1"].Value = dtHeader.Rows[0]["partner_name"];
        exSheet.Cells["C2"].Value = dtHeader.Rows[0]["addr1"];
        exSheet.Cells["C3"].Value = "Tax Code: " + dtHeader.Rows[0]["tax_code"];

        exSheet.Cells["Q1"].Value = "Tel: " + dtHeader.Rows[0]["phone_no"];
        exSheet.Cells["Q2"].Value = "Fax No: " + dtHeader.Rows[0]["fax_no"];
        exSheet.Cells["Q3"].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");

        exSheet.Cells["Q7"].Value = dtHeader.Rows[0]["t_date"];
        // END MASTER

        // INSERT ROWS

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

        // SET DATA
        int pos = 10;
        int i   = 0;

        Color color1 = ColorTranslator.FromHtml("0xCCFFFF");
        Color color2 = ColorTranslator.FromHtml("0xFFFFCC");

        for (i = 0; i < dtDetail.Rows.Count; i++)
        {
            exSheet.Cells[pos + i, 1].Value  = i + 1;
            exSheet.Cells[pos + i, 2].Value  = dtDetail.Rows[i]["bill_to_name"];
            exSheet.Cells[pos + i, 3].Value  = dtDetail.Rows[i]["grp_nm"];
            exSheet.Cells[pos + i, 4].Value  = dtDetail.Rows[i]["company_name"];
            exSheet.Cells[pos + i, 5].Value  = dtDetail.Rows[i]["order_year"];
            exSheet.Cells[pos + i, 6].Value  = dtDetail.Rows[i]["po_no"];
            exSheet.Cells[pos + i, 7].Value  = dtDetail.Rows[i]["ITEM_ETD"];
            exSheet.Cells[pos + i, 8].Value  = dtDetail.Rows[i]["item_code"];
            exSheet.Cells[pos + i, 9].Value  = dtDetail.Rows[i]["item_name"];
            exSheet.Cells[pos + i, 10].Value = dtDetail.Rows[i]["ord_qty"];
            exSheet.Cells[pos + i, 11].Value = dtDetail.Rows[i]["unit_price"];
            exSheet.Cells[pos + i, 12].Value = dtDetail.Rows[i]["item_amount"];
            exSheet.Cells[pos + i, 13].Value = dtDetail.Rows[i]["act_etd"];
            exSheet.Cells[pos + i, 14].Value = dtDetail.Rows[i]["out_qty"];
            exSheet.Cells[pos + i, 15].Value = dtDetail.Rows[i]["bal_qty"];
            exSheet.Cells[pos + i, 16].Value = dtDetail.Rows[i]["out_amount"];
            exSheet.Cells[pos + i, 17].Value = dtDetail.Rows[i]["description"];
            exSheet.Cells[pos + i, 18].Value = dtDetail.Rows[i]["dest_port"];
            exSheet.Cells[pos + i, 19].Value = dtDetail.Rows[i]["att01"];
            exSheet.Cells[pos + i, 20].Value = dtDetail.Rows[i]["ex_nation"];
            //-------------
        }

        int t = pos + i - 1;

        exSheet.Cells[pos + i, 10].Value = "=sum(J10:J" + t + ")";
        exSheet.Cells[pos + i, 12].Value = "=sum(L10:L" + t + ")";
        exSheet.Cells[pos + i, 14].Value = "=sum(N10:N" + t + ")";
        exSheet.Cells[pos + i, 15].Value = "=sum(O10:O" + t + ")";
        exSheet.Cells[pos + i, 16].Value = "=sum(P10:P" + t + ")";

        //---------
        if (File.Exists(TempFile))
        {
            File.Delete(TempFile);
        }
        // hide row firts
        range = exSheet.Range["A1"];
        // hide row A5
        // range.Rows.Hidden = true;
        // font bold header

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = true;*/

        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());

        string p_tco_org_pk = Request["l_tco_org_pk"];
        string p_work_mon   = Request["l_work_mon"];
        string p_emp_id     = Request["l_emp_id"];
        string p_from_dt    = Request["dt_from"];
        string p_to_dt      = Request["dt_to"];
        string p_nation     = Request["l_nation"];
        string p_sal_period = Request["l_sal_period"];

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

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

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

        List <IWorksheet> listWB = new List <IWorksheet>();

        //Add worksheet
        IWorksheet exSheet;

        string sql_day
            = "select substr(c.car_date,-2), c.hol_type from thr_calendar c  " +
              "where c.del_if=0  " +
              "and c.car_date between '" + p_from_dt + "' and '" + p_to_dt + "' " +
              "and c.tco_company_pk = (select g.tco_company_pk " +
              "                                       from tco_org g " +
              "                                      where g.del_if = 0 and g.pk = '" + p_tco_org_pk + "') " +
              " order by c.car_date ";

        DataTable dt_day = CtlLib.TableReadOpen(sql_day);

        string sql_date
            = "select  " +
              "    to_char(to_date('" + p_work_mon + "', 'yyyymm'), 'mm yyyy')  " +
              "  from dual ";

        DataTable dt_date = CtlLib.TableReadOpen(sql_date);

        string sql_org
            = "select b.pk, substr(b.org_nm, 1, 30) org_nm from thr_work_month a, tco_org b " +
              "where a.del_if=0 and b.del_if=0 " +
              "and a.outlet_pk = b.pk " +
              "and a.work_mon = '" + p_work_mon + "' " +
              "and (a.emp_id = '" + p_emp_id + "' or '" + p_emp_id + "' is null) " +
              "and a.outlet_pk in ( select  g.pk " +
              "                                from tco_org g " +
              "                               where g.del_if = 0 " +
              "                          start with g.pk =(case when '" + p_tco_org_pk + "'='ALL' then g.pk else to_number('" + p_tco_org_pk + "') end) " +
              "                          connect by prior g.pk = g.p_pk) " +
              "group by b.pk, b.org_nm " +
              "order by b.org_nm ";

        DataTable dt_org = CtlLib.TableReadOpen(sql_org);

        int    count_org = dt_org.Rows.Count;
        string sheet_nm  = string.Empty;

        int row_loop = 7;

        for (int i = 0; i < count_org; i++)
        {
            //Create a new workbook
            exBook.Worksheets.Add();

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

            if (i < dt_org.Rows.Count)
            {
                sheet_nm = dt_org.Rows[i][1].ToString();

                exSheet.Name = sheet_nm;
                listWB.Add(exSheet);
            }
            if (i == 0)
            {
                listWB[0].Cells["A6"].Value = string.Format("THÁNG {0} ", dt_date.Rows[0][0]);

                IInterior interior = listWB[0].Cells["E5:E5"].Interior;
                interior.Color = System.Drawing.Color.FromArgb(0, 204, 204);
                //listWB[0].Cells["A3"].Value = "Bộ phận: " + sheet_nm;

                /*
                 * for (int j = 0; j < dt_cal.Rows.Count; j++) //to mau cn cho dong tieu de
                 * {
                 * int tmp = int.Parse(dt_cal.Rows[j][0].ToString()) + 4;
                 * if(dt_cal.Rows[j][1].ToString()=="HOL")
                 * listWB[0].Cells[row_loop - 1, tmp].Rows.Font.Color = Color.FromArgb(255, 000, 000);
                 * else
                 * listWB[0].Cells[row_loop - 1, tmp].Interior.Color = Color.FromArgb(141, 180, 226);
                 *
                 *
                 *
                 * } */
            }
            else
            {
                listWB[0].Range["A1:AT30"].Copy(listWB[i].Range["A1:AT30"], XlPasteType.xlPasteAll);
                listWB[i].Cells["A6"].Value = string.Format("THÁNG {0} ", dt_date.Rows[0][0]);
                //listWB[i].Cells["E5"].Rows.Font.Color = Color.FromArgb(0, 153, 153);
                listWB[i].Cells["A1"].Rows.RowHeight = 20.25;
                listWB[i].Cells["A2"].Rows.RowHeight = 15.75;
                listWB[i].Cells["A3"].Rows.RowHeight = 18;
                listWB[i].Cells["A4"].Rows.RowHeight = 15;
                listWB[i].Cells["A5"].Rows.RowHeight = 22.5;
                listWB[i].Cells["A6"].Rows.RowHeight = 42;

                IInterior interior = listWB[i].Cells["E5:E5"].Interior;
                interior.Color = System.Drawing.Color.FromArgb(0, 204, 204);

                /*
                 *      listWB[i].FreezePanes = listWB[0].FreezePanes;
                 *      listWB[i].PageSetup.LeftMargin = listWB[0].PageSetup.LeftMargin;
                 *      listWB[i].PageSetup.Orientation = listWB[0].PageSetup.Orientation;
                 *      listWB[i].PageSetup.FitToPagesWide = 1;
                 *      listWB[i].Zoom = 120;
                 *      listWB[i].PageSetup.PrintTitleRows = listWB[0].PageSetup.PrintTitleRows;
                 *      listWB[i].PageSetup.RightMargin = listWB[0].PageSetup.RightMargin;
                 *      listWB[i].PageSetup.HeaderMargin = listWB[0].PageSetup.HeaderMargin;
                 *      listWB[i].PageSetup.FooterMargin = listWB[0].PageSetup.FooterMargin;
                 *      listWB[i].PageSetup.FitToPages = true;
                 *      listWB[i].PageSetup.CenterFooter = listWB[0].PageSetup.CenterFooter;
                 *      listWB[i].PageSetup.PaperSize = listWB[0].PageSetup.PaperSize;
                 *      listWB[i].PageSetup.TopMargin = listWB[0].PageSetup.TopMargin;
                 *      listWB[i].PageSetup.CenterHorizontally = listWB[0].PageSetup.CenterHorizontally;
                 */
            }

            // do du lieu header ngay
            for (int d = 0; d < dt_day.Rows.Count; d++)
            {
                listWB[i].Cells[6, 5 + d].Value = dt_day.Rows[d][0].ToString();
            }
        }

        // binding data
        string SQL_EMP = string.Empty;
        long   outlet_pk;

        for (int i = 0; i < count_org; i++)
        {
            outlet_pk = long.Parse(dt_org.Rows[i][0].ToString());

            SQL_EMP
                = "select 0, " +
                  "              1, " +
                  "              a.emp_id, " +
                  "              e.full_name, " +
                  "              (select initcap(v.code_nm) from vhr_hr_code v where v.id='HR0008' and v.code = a.pos_type ), " +
                  "              nullif(a.wt1,'0'), " +
                  "              nullif(a.wt2,'0'), " +
                  "              nullif(a.wt3,'0'), " +
                  "              nullif(a.wt4,'0'), " +
                  "              nullif(a.wt5,'0'), " +
                  "              nullif(a.wt6,'0'), " +
                  "              nullif(a.wt7,'0'), " +
                  "              nullif(a.wt8,'0'), " +
                  "              nullif(a.wt9,'0'), " +
                  "              nullif(a.wt10,'0'), " +
                  "              nullif(a.wt11,'0'), " +
                  "              nullif(a.wt12,'0'), " +
                  "              nullif(a.wt13,'0'), " +
                  "              nullif(a.wt14,'0'), " +
                  "              nullif(a.wt15,'0'), " +
                  "              nullif(a.wt16,'0'), " +
                  "              nullif(a.wt17,'0'), " +
                  "              nullif(a.wt18,'0'), " +
                  "              nullif(a.wt19,'0'), " +
                  "              nullif(a.wt20,'0'), " +
                  "              nullif(a.wt21,'0'), " +
                  "              nullif(a.wt22,'0'), " +
                  "              nullif(a.wt23,'0'), " +
                  "              nullif(a.wt24,'0'), " +
                  "              nullif(a.wt25,'0'), " +
                  "              nullif(a.wt26,'0'), " +
                  "              nullif(a.wt27,'0'), " +
                  "              nullif(a.wt28,'0'), " +
                  "              nullif(a.wt29,'0'), " +
                  "              nullif(a.wt30,'0'), " +
                  "              nullif(a.wt31,'0'), " +
                  "              nullif(round(nvl(a.total_wd, 0) + nvl(a.total_hol, 0), 2), 0), " +
                  "              nullif(round(nvl(a.abs1_pay, 0)/8, 2), 0),  " +
                  "              nullif(round(nvl(a.abs2_pay, 0)/8, 2), 0),  " +
                  "              nullif(round(nvl(a.abs3_pay, 0)/8, 2), 0),  " +
                  "              nullif(round(nvl(a.abs4_pay, 0)/8, 2), 0),  " +
                  "              nullif((round(nvl(a.total_wd, 0) + nvl(a.total_hol, 0), 2) + round(nvl(a.abs1_pay, 0)/8, 2) + round(nvl(a.abs2_pay, 0)/8, 2) " +
                  "                                                    + round(nvl(a.abs3_pay, 0)/8, 2) + round(nvl(a.abs4_pay, 0)/8, 2)), 0) as total_day_wt_abs, " +
                  "              nullif(nvl(a.total_ot, 0) , 0) ,                                       " +
                  "              nullif(nvl(a.total_st, 0) + nvl(a.total_ost, 0) , 0) , " +
                  "              nullif(nvl(a.total_ht, 0) + nvl(a.total_oht, 0) , 0) , " +
                  "              nullif(round((nvl(a.total_rate_ot, 0) + nvl(a.total_rate_st, 0) + nvl(a.total_rate_ost, 0) + nvl(a.total_rate_ht, 0) + nvl(a.total_rate_oht, 0))/8, 2), 0) total_day_ot, " +
                  "              nullif((round(nvl(a.total_wd, 0) + nvl(a.total_hol, 0), 2) + round(nvl(a.abs1_pay, 0)/8, 2) + round(nvl(a.abs2_pay, 0)/8, 2) " +
                  "                                                    + round(nvl(a.abs3_pay, 0)/8, 2) + round(nvl(a.abs4_pay, 0)/8, 2)) " +
                  "                      + round((nvl(a.total_rate_ot, 0) + nvl(a.total_rate_st, 0) + nvl(a.total_rate_ost, 0) + nvl(a.total_rate_ht, 0) + nvl(a.total_rate_oht, 0))/8, 2)                              " +
                  "                     , 0) as total_day_wt_abs_ot " +
                  "       from thr_work_month a, tco_org o, thr_employee e " +
                  "       where a.del_if=0 and o.del_if=0 and e.del_if=0 " +
                  "       and a.thr_emp_pk = e.pk " +
                  "       and a.tco_org_pk = o.pk " +
                  "       and a.work_mon = '" + p_work_mon + "' " +
                  "		and a.outlet_pk ='"+ outlet_pk + "' " +
                  "		and (a.emp_id = '"+ p_emp_id + "' or '" + p_emp_id + "' is null) " +
                  "       and decode('" + p_nation + "','ALL','ALL',e.nation )= '" + p_nation + "' " +
                  "       and a.sal_period  = '" + p_sal_period + "' " +
                  "       order by a.work_mon, a.emp_id ";

            DataTable dt_detail;
            dt_detail = CtlLib.TableReadOpen(SQL_EMP);

            int col_emp = dt_detail.Columns.Count;
            int row_emp = dt_detail.Rows.Count;
            int count   = 0;
            for (int l_addrow = 1; l_addrow < dt_detail.Rows.Count; l_addrow++)
            {
                listWB[i].Range[row_loop + l_addrow, 50].Rows.EntireRow.Insert();                 //insert row new of sheet
            }

            // xu ly du lieu do vao excel
            if (dt_detail.Rows.Count > 0)
            {
                for (int l_row = 0; l_row < dt_detail.Rows.Count; l_row++)
                {
                    count++;
                    listWB[i].Cells[row_loop + l_row, 1].Value = count;

                    // do du lieu vao cac dong
                    for (int col = 2; col < col_emp; col++)
                    {
                        if (IsNumeric(dt_detail.Rows[l_row][col].ToString()) && col != 2)
                        {
                            listWB[i].Cells[row_loop + l_row, col].Value = double.Parse(dt_detail.Rows[l_row][col].ToString());
                        }
                        else
                        {
                            listWB[i].Cells[row_loop + l_row, col].Value = dt_detail.Rows[l_row][col].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;

        // font bold header

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = true;*/

        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());

        string l_from_date = Request["p_from_date"];
        string l_to_date   = Request["p_to_date"];

        string TemplateFile = "rpt_bisc00030_DR05.xls";

        string TempFile = "../../../../system/temp/rpt_bisc00030_DR05_" + 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 l_parameter = "'" + l_from_date + "','" + l_to_date + "'";

        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00030_DR05_1", l_parameter);
        DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00030_DR05_2", l_parameter);

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

        if (dtDetail.Rows.Count > 0)
        {
            exSheet.Cells["B2"].Value = dtMaster.Rows[0]["search_date"].ToString();
        }

        //-----------------------
        exSheet.Cells["B4"].Value = DateTime.Now.ToString("HH:mm");

        // loop detail not percent
        for (int l_row = 0; l_row < dtDetail.Rows.Count; l_row++)
        {
            exSheet.Cells[8 + l_row, 1].Value = l_row + 1;
            exSheet.Cells[8 + l_row, 2].Value = dtDetail.Rows[l_row]["item_name"].ToString();
            exSheet.Cells[8 + l_row, 3].Value = dtDetail.Rows[l_row]["item_Code"].ToString();
            exSheet.Cells[8 + l_row, 4].Value = dtDetail.Rows[l_row]["kvp_usage_forecast"];
            exSheet.Cells[8 + l_row, 5].Value = dtDetail.Rows[l_row]["daily_consumption"];

            exSheet.Cells[8 + l_row, 6].Value = dtDetail.Rows[l_row]["today_deli_plan"];
            exSheet.Cells[8 + l_row, 7].Value = dtDetail.Rows[l_row]["tomo_deli_plan"];

            exSheet.Cells[8 + l_row, 8].Value  = dtDetail.Rows[l_row]["begin_production"];
            exSheet.Cells[8 + l_row, 9].Value  = dtDetail.Rows[l_row]["usable_days_production"];
            exSheet.Cells[8 + l_row, 10].Value = dtDetail.Rows[l_row]["begin_visual"];
            exSheet.Cells[8 + l_row, 11].Value = dtDetail.Rows[l_row]["usable_days_visual"];

            exSheet.Cells[8 + l_row, 12].Value = dtDetail.Rows[l_row]["begin_finish_good"];

            exSheet.Cells[8 + l_row, 13].Value = dtDetail.Rows[l_row]["usable_days_finish_good"];
            exSheet.Cells[8 + l_row, 14].Value = dtDetail.Rows[l_row]["begin_kvp"];
            exSheet.Cells[8 + l_row, 15].Value = dtDetail.Rows[l_row]["usable_days_kvp"];

            exSheet.Cells[8 + l_row, 16].Value = dtDetail.Rows[l_row]["begin_total"];

            exSheet.Cells[8 + l_row, 17].Value = dtDetail.Rows[l_row]["usable_days_total"];

            exSheet.Cells[8 + l_row, 18].Value = dtDetail.Rows[l_row]["begin_company"];
            exSheet.Cells[8 + l_row, 19].Value = dtDetail.Rows[l_row]["usable_days_company"];
        }

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

        // font bold header

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = true;*/

        exBook.SaveAs(TempFile);
        //write out to client broswer
        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";
        //Send file string to client
        Response.WriteFile(TempFile);
        //Stop execute
        Response.End();
    }
예제 #23
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        string l_pk         = Request["p_tht_room_allocate_pk"];
        string l_cashier_pk = Request["p_cashier_pk"];
        string l_printed_yn = Request["p_printed_yn"];
        string l_paymethod  = Request["p_paymethod"];
        string l_folio_type = Request["p_folio_type"];

        string TemplateFile = "rpt_60280040_Guest_Invoice.xls";
        string TempFile     = "../../../../system/temp/rpt_60280040_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;

        if (l_folio_type == "")
        {
            para = "'" + l_pk + "','" + l_cashier_pk + "','" + l_printed_yn + "','" + l_paymethod + "'";
            dt   = CtlLib.TableReadOpenCursor("rpt_60280040_hotel", para);          //use
        }
        else
        {
            para = "'" + l_pk + "','" + l_cashier_pk + "','" + l_printed_yn + "','" + l_paymethod + "','" + l_folio_type + "'";
            dt   = CtlLib.TableReadOpenCursor("rpt_60280040_invoice", para);          // not use
        }
        //-----header---

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

        if (dt.Rows.Count > 0)
        {
            if (l_printed_yn == "Y")
            {
                l_status_title = "GUEST INVOICE";
            }
            else
            {
                l_status_title = "GUEST INVOICE (FOR PREVIEW ONLY)";
            }
            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_date"].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();
            if (dt.Rows[0]["total_amt_pp"].ToString() != "")
            {
                exSheet.Cells[17, 5].Value = int.Parse(dt.Rows[0]["total_amt_pp"].ToString());                        //
            }
            if (dt.Rows[0]["total_amt_pp_usd"].ToString() != "")
            {
                exSheet.Cells[17, 7].Value = float.Parse(dt.Rows[0]["total_amt_pp_usd"].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
            if (dt.Rows[0]["svc_amt"].ToString() != "")
            {
                exSheet.Cells[18, 5].Value = int.Parse(dt.Rows[0]["svc_amt"].ToString());
            }
            if (dt.Rows[0]["svc_amt_usd"].ToString() != "")
            {
                exSheet.Cells[18, 7].Value = float.Parse(dt.Rows[0]["svc_amt_usd"].ToString());
            }

            //exSheet.Cells[20, 5].Value = int.Parse(dt.Rows[0]["total_notvat_amt"].ToString());
            //exSheet.Cells[20, 7].Value = float.Parse(dt.Rows[0]["total_notvat_amt_usd"].ToString());
            if (dt.Rows[0]["vat_amt"].ToString() != "")
            {
                exSheet.Cells[19, 5].Value = int.Parse(dt.Rows[0]["vat_amt"].ToString());
            }
            if (dt.Rows[0]["vat_amt_usd"].ToString() != "")
            {
                exSheet.Cells[19, 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());
            if (dt.Rows[0]["advance_amt"].ToString() != "")
            {
                exSheet.Cells[21, 5].Value = int.Parse(dt.Rows[0]["advance_amt"].ToString());
            }
            if (dt.Rows[0]["advance_amt_usd"].ToString() != "")
            {
                exSheet.Cells[21, 7].Value = float.Parse(dt.Rows[0]["advance_amt_usd"].ToString());
            }
            if (dt.Rows[0]["refund_vnd"].ToString() != "0")
            {
                if (dt.Rows[0]["refund_vnd"].ToString() != "")
                {
                    exSheet.Cells[22, 5].Value = -1 * double.Parse(dt.Rows[0]["refund_vnd"].ToString());
                }
                if (dt.Rows[0]["refund_usd"].ToString() != "")
                {
                    if (dt.Rows[0]["refund_usd"].ToString() != "0.00")
                    {
                        exSheet.Cells[22, 7].Value = -1 * double.Parse(dt.Rows[0]["refund_usd"].ToString());
                    }
                }
            }
            else
            {
                range             = exSheet.Range["A22"];
                range.Rows.Hidden = true;
            }
            if (dt.Rows[0]["total_due"].ToString() != "")
            {
                exSheet.Cells[23, 5].Value = float.Parse(dt.Rows[0]["total_due"].ToString());                        //int.Parse(dt.Rows[0]["total_due"].ToString());
            }
            if (dt.Rows[0]["total_due_usd"].ToString() != "")
            {
                exSheet.Cells[23, 7].Value = float.Parse(dt.Rows[0]["total_due_usd"].ToString());
            }
            //exSheet.Cells[24, 1].Value = dt.Rows[0]["description_deposit"].ToString();//remark deposit row 24
            //if(int.Parse(dt.Rows[0]["advance_amt"].ToString()) == 0 )
            //{
            //range = exSheet.Range["A24"];
            //range.Rows.Hidden = true;
            //}
            if (dt.Rows[0]["cash_amt_vnd"].ToString() != "")
            {
                exSheet.Cells[25, 3].Value = int.Parse(dt.Rows[0]["cash_amt_vnd"].ToString());
            }
            if (dt.Rows[0]["cash_amt_usd"].ToString() != "")
            {
                exSheet.Cells[25, 5].Value = float.Parse(dt.Rows[0]["cash_amt_usd"].ToString());
            }
            if (dt.Rows[0]["credit_amt_vnd"].ToString() != "")
            {
                exSheet.Cells[26, 3].Value = int.Parse(dt.Rows[0]["credit_amt_vnd"].ToString());
            }
            if (dt.Rows[0]["credit_amt_usd"].ToString() != "")
            {
                exSheet.Cells[26, 5].Value = float.Parse(dt.Rows[0]["credit_amt_usd"].ToString());
            }
            if (dt.Rows[0]["ar_amt_vnd"].ToString() != "")
            {
                exSheet.Cells[27, 3].Value = float.Parse(dt.Rows[0]["ar_amt_vnd"].ToString());
            }
            //exSheet.Cells[26, 5].Value = float.Parse(dt.Rows[0]["ar_amt_usd"].ToString());

            if (int.Parse(dt.Rows[0]["cash_amt_vnd"].ToString()) == 0 && float.Parse(dt.Rows[0]["cash_amt_usd"].ToString()) == 0)
            {
                range             = exSheet.Range["A25"];
                range.Rows.Hidden = true;
            }
            if (int.Parse(dt.Rows[0]["credit_amt_vnd"].ToString()) == 0 && float.Parse(dt.Rows[0]["credit_amt_usd"].ToString()) == 0)
            {
                range             = exSheet.Range["A26"];
                range.Rows.Hidden = true;
            }
            if (float.Parse(dt.Rows[0]["ar_amt_vnd"].ToString()) == 0 && float.Parse(dt.Rows[0]["ar_amt_usd"].ToString()) == 0)
            {
                range             = exSheet.Range["A27"];
                range.Rows.Hidden = true;
            }
            string inword_amt, inword_amt_usd;
            //inword_amt     = CommondLib.Num2VNText(dt.Rows[0]["total_amt"].ToString(), "VND");
            //inword_amt_usd = CommondLib.Num2EngText(dt.Rows[0]["total_amt_usd"].ToString(), "USD");

            inword_amt                 = CommondLib.Num2VNText(dt.Rows[0]["total_due"].ToString(), "VND");
            inword_amt_usd             = CommondLib.Num2EngText(dt.Rows[0]["total_due_usd"].ToString(), "USD");
            exSheet.Cells[28, 3].Value = inword_amt.ToString();
            exSheet.Cells[29, 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;

        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)
                    {
                        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 = dt.Rows[l_row - 1][col].ToString();
                        }
                        else
                        {
                            exSheet.Cells[15 + l_row, col + 1].Value = double.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();
    }
예제 #24
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        //CtlLib.SetUser("SALE");

        string TemplateFile = "rpt_bisc00020_ST05.xls";
        string TempFile     = "../../../../system/temp/rpt_bisc00020_ST05_" + 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_from_date     = Request.QueryString["p_from_date"];
        string p_to_date       = Request.QueryString["p_to_date"];
        string p_wh_type       = Request.QueryString["p_wh_type"];
        string p_wh_pk         = Request.QueryString["p_tin_warehouse_pk"];
        string p_wh_name       = Request.QueryString["p_wh_name"];
        string p_item_group_pk = Request.QueryString["p_item_grp"];
        string p_item          = Request.QueryString["p_item"];
        string p_lang          = Request.QueryString["p_lang"];

        DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_ST05_1", "'" + Session["User_ID"].ToString() + "'");

        string para = "'" + p_wh_pk + "','" + p_from_date + "','" + p_to_date + "'";

        DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_ST05_2", para);

        para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_wh_pk + "','" + p_item_group_pk + "','" + p_item + "','" + p_lang + "'";

        DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_ST05_3", para);

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

            Bitmap b = new Bitmap(stream);
            exSheet.Shapes.AddPicture(ResizeImage(b, 100, 55));
        }

        exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"].ToString();

        exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"].ToString();

        exSheet.Cells["C3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString();

        exSheet.Cells["G1"].Value = "Tell: " + dtInfo.Rows[0]["phone_no"].ToString();

        exSheet.Cells["G2"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();

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

        exSheet.Cells["C5"].Value = dtMaster.Rows[0]["wh_name"].ToString();

        exSheet.Cells["G5"].Value = dtMaster.Rows[0]["range_date"].ToString();
        //---------

        int l_pos = 7;

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


        for (int 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]["grp_nm"].ToString();

            exSheet.Cells[l_pos + i, 3].Value = dtDetail.Rows[i]["virtual_code"].ToString();

            exSheet.Cells[l_pos + i, 4].Value = dtDetail.Rows[i]["item_code"].ToString();
            exSheet.Cells[l_pos + i, 5].Value = dtDetail.Rows[i]["item_name"].ToString();
            exSheet.Cells[l_pos + i, 6].Value = dtDetail.Rows[i]["uom"].ToString();

            exSheet.Cells[l_pos + i, 7].Value  = dtDetail.Rows[i]["begin_qty"];
            exSheet.Cells[l_pos + i, 8].Value  = dtDetail.Rows[i]["in_qty"];
            exSheet.Cells[l_pos + i, 9].Value  = dtDetail.Rows[i]["out_qty"];
            exSheet.Cells[l_pos + i, 10].Value = dtDetail.Rows[i]["end_qty"];
        }

        exSheet.Cells[dtDetail.Rows.Count + 7, 7].Value  = "=SUM(G7:G" + (dtDetail.Rows.Count + 6) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 7, 8].Value  = "=SUM(H7:H" + (dtDetail.Rows.Count + 6) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 7, 9].Value  = "=SUM(I7:I" + (dtDetail.Rows.Count + 6) + ")";
        exSheet.Cells[dtDetail.Rows.Count + 7, 10].Value = "=SUM(J7:J" + (dtDetail.Rows.Count + 6) + ")";

        // 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

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = true;*/

        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();
    }
예제 #25
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        String module       = Request["module"];
        string TemplateFile = "rpt_gsau00600_0.xls";
        string TempFile     = "../../../../system/temp/rpt_gsau00600_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];

        IRange range;

        string SQL
            = "SELECT MN.PARENT_ID,MN.PARENT_NM,MN.MENU_ID,MN.form_nm,FORM_LNM , FORM_FNM,MN.MN_LEVEL,MN.FORM_URL,FORM_DESC, MN.PK,MN.parent_pk " +
              "     FROM(select TF.PK PK,TF.MENU_ID,tf.form_nm,tf.FORM_LNM , tf. FORM_FNM, level MN_LEVEL,TF.FORM_URL ,tf. FORM_DESC" +
              "                ,(select a.menu_id from tes_obj a where a.del_if=0 and a.pk=tf.p_pk) parent_id " +
              "                ,(select a.form_nm from tes_obj a where a.del_if=0 and a.pk=tf.p_pk) parent_nm " +
              "                ,(select a.PK from tes_obj a where a.del_if=0 and a.pk=tf.p_pk) parent_pk " +
              "                                   from tes_obj tf " +
              "                                  where tf.del_if = 0  and nvl(TF.USE_YN,'N') = 'Y' ";

        if (module == null || module == "")
        {
            SQL += "                             start with TF.p_pk = 0 and tf.del_if = 0 and nvl(TF.USE_YN,'N')='Y' and p_pk is not null ";
        }
        else
        {
            SQL += "                             start with TF.pk = " + module + " and tf.del_if = 0 and nvl(TF.USE_YN,'N')='Y' and p_pk is not null ";
        }

        SQL += "                             connect by prior tf.pk = tf.p_pk and tf.del_if = 0  and nvl(TF.USE_YN,'N') = 'Y' " +
               "                              order siblings by no) MN  where  parent_id is not null";



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

        DataTable dt_emp = CtlLib.TableReadOpen(SQL);
        int       irow, icol_emp;

        irow     = dt_emp.Rows.Count;
        icol_emp = dt_emp.Columns.Count - 2;
        if (irow == 0)
        {
            Response.Write("There is no data");
            Response.End();
        }

        int i, j, k, i_allow;
        int pos = 3;

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

        //Show data
        for (i = 0; i < irow; i++)
        {
            exSheet.Cells[pos + i, 1].Value = i + 1;
            for (j = 0; j < icol_emp; j++)
            {
                exSheet.Cells[pos + i, j + 2].Value = dt_emp.Rows[i][j].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();
    }
예제 #26
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());
        string l_from = Request["p_dtfrom"];
        string l_to   = Request["p_dtto"];

        string TemplateFile = "rpt_60280150_rvn_tour_detail.xls";
        string TempFile     = "../../../../system/temp/rpt_60280150_rvn_tour_detail" + 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_from + "','" + l_to + "'";
        dt   = CtlLib.TableReadOpenCursor("rpt_ht_sel_60280150", para);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[3, 3].Value = "There is not data!";
        }
        if (dt.Rows.Count > 0)
        {
            exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString();
            //   exSheet.Cells[3, 4].Value = dt.Rows[0]["roomno"].ToString();
            //  exSheet.Cells[3, 5].Value = dt.Rows[0]["paymnent"].ToString();
            exSheet.Cells[3, 7].Value = dt.Rows[0]["print_time"].ToString();
            // exSheet.Cells[2, 3].Value = dt.Rows[0]["report_name"].ToString();
        }

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

        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range["A7"].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;
        double l_total_qty = 0, l_total_amount = 0;

        if (dt.Rows.Count > 0)
        {
            //int l_cnt = dt.Rows.Count;
            for (int l_row = 1; l_row <= dt.Rows.Count; l_row++)
            {
                for (int col = 0; col <= 8; col++)
                {
                    if (col == 0)
                    {
                        exSheet.Cells[5 + l_row, 1].Value = dt.Rows[l_row - 1][0].ToString();
                    }
                    if (col == 1)
                    {
                        exSheet.Cells[5 + l_row, 2].Value = dt.Rows[l_row - 1][1].ToString();
                    }
                    if (col == 2)
                    {
                        if (dt.Rows[l_row - 1][2].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 3].Value = dt.Rows[l_row - 1][2].ToString();
                        }
                    }
                    if (col == 3)
                    {
                        exSheet.Cells[5 + l_row, 4].Value = dt.Rows[l_row - 1][3].ToString();
                    }
                    if (col == 4)
                    {
                        if (dt.Rows[l_row - 1][4].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 5].Value = dt.Rows[l_row - 1][4].ToString();
                        }
                    }
                    if (col == 5)
                    {
                        if (dt.Rows[l_row - 1][5].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 6].Value = dt.Rows[l_row - 1][5].ToString();
                        }
                    }
                    if (col == 6)
                    {
                        if (dt.Rows[l_row - 1][6].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 7].Value = double.Parse(dt.Rows[l_row - 1][6].ToString());
                        }
                    }
                    if (col == 7)
                    {
                        if (dt.Rows[l_row - 1][7].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 8].Value = double.Parse(dt.Rows[l_row - 1][7].ToString());
                            l_total_qty = l_total_qty + double.Parse(dt.Rows[l_row - 1][7].ToString());
                        }
                    }
                    if (col == 8)
                    {
                        if (dt.Rows[l_row - 1][8].ToString() != "")
                        {
                            exSheet.Cells[5 + l_row, 9].Value = double.Parse(dt.Rows[l_row - 1][8].ToString());
                            l_total_amount = l_total_amount + double.Parse(dt.Rows[l_row - 1][8].ToString());
                        }
                    }
                }
            }
        }
        exSheet.Cells[6 + l_cnt, 8].Value = l_total_qty;
        exSheet.Cells[6 + l_cnt, 9].Value = l_total_amount;

        // end loop detail percent
        if (File.Exists(TempFile))
        {
            File.Delete(TempFile);
        }
        range = exSheet.Range["A1"];
        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();
    }
예제 #27
0
    protected void Page_Load(object sender, EventArgs e)
    {
        CtlLib.SetUser(Session["APP_DBUSER"].ToString());

        string p_dt_frm    = Request["p_dt_frm"];
        string p_dt_to     = Request["p_dt_to"];
        string P_DummyRoom = Request["P_DummyRoom"];
        string p_GroupName = Request["p_GroupName"];

        string TemplateFile = "rpt_60280140.xls";
        string TempFile     = "../../../../system/temp/rpt_60280140_" + 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 = "'" + p_dt_frm + "','" + p_dt_to + "','" + P_DummyRoom + "','" + p_GroupName + "'";

        dt = CtlLib.TableReadOpenCursor("rpt_ht_sel_60280140", para);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[6, 3].Value = "There is not data!";
        }
        //  if (dt.Rows.Count > 0)
        //  {
        ////	exSheet.Cells[3, 1].Value = "Date: "+l_dtfrm.Substring(6, 2)+"/"+l_dtfrm.Substring(4, 2)+"/"+l_dtfrm.Substring(0, 4)+" ~ "+""+l_dtto.Substring(6, 2)+"/"+l_dtto.Substring(4, 2)+"/"+l_dtto.Substring(0, 4);
        //    exSheet.Cells[3, 6].Value = dt.Rows[0]["print_time"].ToString();
        //	exSheet.Cells[3, 3].Value = "Total : "+dt.Rows.Count.ToString()+" row(s).";
        // }

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

        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range["A6"].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;
        float  l_total_amt_usd = 0;
        double l_total_amt_vnd = 0;
        int    pos             = 5;
        int    i = 0;

        for (i = 0; i < dt.Rows.Count; i++)
        {
            exSheet.Cells[pos + i, 1].Value  = dt.Rows[i]["audit_date"];
            exSheet.Cells[pos + i, 2].Value  = dt.Rows[i]["dummy_room"];
            exSheet.Cells[pos + i, 3].Value  = dt.Rows[i]["room_no"];
            exSheet.Cells[pos + i, 4].Value  = dt.Rows[i]["room_type"];
            exSheet.Cells[pos + i, 5].Value  = dt.Rows[i]["in_room_amt"];
            exSheet.Cells[pos + i, 6].Value  = dt.Rows[i]["group_name"];
            exSheet.Cells[pos + i, 7].Value  = dt.Rows[i]["guest_name"];
            exSheet.Cells[pos + i, 8].Value  = dt.Rows[i]["rate_plan"];
            exSheet.Cells[pos + i, 9].Value  = dt.Rows[i]["checkin_date"];
            exSheet.Cells[pos + i, 10].Value = dt.Rows[i]["checkout_date"];
        }
        exSheet.Cells[pos + i, 5].Value = "=sum(E5:E" + (pos + i - 1) + ")";
        //exSheet.Cells[5 + l_cnt, 6].Value =l_total_amt_usd;
        // 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 p_master_pk = Request["master_pk"];

        string TemplateFile = "rpt_bini00050_0.xls";
        string TempFile     = "../../../../system/temp/rpt_bini00050_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];

        IRange range;


        string    para      = "'" + p_master_pk + "'";
        DataTable dt_com    = CtlLib.TableReadOpenCursor("lg_rpt_bini00050_0_0", para);
        DataTable dt_master = CtlLib.TableReadOpenCursor("lg_rpt_bini00050_0_1", para);
        DataTable dt_detail = CtlLib.TableReadOpenCursor("lg_rpt_bini00050_0_2", para);

        if (dt_master.Rows.Count == 0)
        {
            Response.Write("there is not have data!");
            Response.End();
        }
        //insert picture
        if (dt_com.Rows[0]["image"].ToString() != "")
        {
            byte[] MyData = new byte[0];
            MyData = (byte[])dt_com.Rows[0]["image"];
            MemoryStream stream = new MemoryStream(MyData);

            Bitmap b = new Bitmap(stream);
            exSheet.Cells[1, 1].Select();
            exSheet.Shapes.AddPicture(ResizeImage(b, 90, 85));
        }
        exSheet.Cells["C1"].Value = dt_com.Rows[0]["com_name"];
        exSheet.Cells["C2"].Value = dt_com.Rows[0]["address"];
        exSheet.Cells["C3"].Value = "MST: " + dt_com.Rows[0]["tax_code"];
        exSheet.Cells["J1"].Value = "Tel: " + dt_com.Rows[0]["tel"];
        exSheet.Cells["J2"].Value = "Fax: " + dt_com.Rows[0]["fax"];
        exSheet.Cells["J3"].Value = "Print Date: " + dt_com.Rows[0]["print_dt"];

        exSheet.Cells["B6"].Value = "DATE: " + dt_master.Rows[0]["out_date"];
        exSheet.Cells["B7"].Value = "Số (No.): " + dt_master.Rows[0]["slip_no"];
        exSheet.Cells["D8"].Value = dt_master.Rows[0]["wh_name"];
        exSheet.Cells["D9"].Value = dt_master.Rows[0]["line_name"];


        int    row_loop    = 15;
        int    l_col       = dt_detail.Columns.Count;
        int    count       = 0;
        double total_qty   = 0;
        double total_amt   = 0;
        double total_ttamt = 0;

        for (int l_addrow = 1; l_addrow < dt_detail.Rows.Count; l_addrow++)
        {
            exSheet.Range[row_loop + l_addrow, 20].Rows.EntireRow.Insert(); //insert row new of sheet
        }

        // xu ly du lieu do vao excel
        if (dt_detail.Rows.Count > 0)
        {
            for (int l_row = 0; l_row < dt_detail.Rows.Count; l_row++)
            {
                count++;
                exSheet.Cells[row_loop + l_row, 2].Value = count;

                total_qty   += double.Parse(dt_detail.Rows[l_row]["out_qty"].ToString());
                total_amt   += double.Parse(dt_detail.Rows[l_row]["item_amount"].ToString());
                total_ttamt += double.Parse(dt_detail.Rows[l_row]["total_amount"].ToString());
                // do du lieu vao cac dong
                for (int col = 3; col < l_col; col++)
                {
                    if (IsNumeric(dt_detail.Rows[l_row][col].ToString()))
                    {
                        exSheet.Cells[row_loop + l_row, col].Value = double.Parse(dt_detail.Rows[l_row][col].ToString());
                    }
                    else
                    {
                        exSheet.Cells[row_loop + l_row, col].Value = dt_detail.Rows[l_row][col].ToString();
                    }
                }
            }
            exSheet.Cells[row_loop + count, 6].Value  = total_qty;
            exSheet.Cells[row_loop + count, 8].Value  = total_amt;
            exSheet.Cells[row_loop + count, 10].Value = total_ttamt;
        }

        exSheet.Cells[row_loop + count + 2, 10].Value = "Ngày " + dt_master.Rows[0]["dd"].ToString() + " Tháng " + dt_master.Rows[0]["mm"].ToString() + " Năm " + dt_master.Rows[0]["yyyy"].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;

        // font bold header

        /*range = exSheet.Range["A1:AC1"];
         * range.Rows[4].Font.Bold = true;*/

        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());

        string l_dt_from       = Request["p_dt_from"];
        string l_dt_to         = Request["p_dt_to"];
        string l_crt_by        = Request["p_crt_by"];
        string l_services_name = Request["p_services_name"];
        string l_location      = Request["p_location"];
        string l_time_from     = Request["p_time_from"];
        string l_time_to       = Request["p_time_to"];

        string TemplateFile = "rpt_60250200_list_transaction_posted.xls";
        string TempFile     = "../../../../system/temp/rpt_60250200_list_transaction_posted_" + 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_dt_from + "','" + l_dt_to + "','" + l_crt_by + "','" + l_services_name + "','" + l_location + "','" + l_time_from + "','" + l_time_to + "'";
        dt   = CtlLib.TableReadOpenCursor("ht_rpt_6025000_list_posted", para);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[3, 3].Value = "There is not data!";
        }
        if (dt.Rows.Count > 0)
        {
            exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString();
            if (l_time_from != "")
            {
                exSheet.Cells[3, 5].Value = "Time :" + l_time_from + " ~ " + l_time_to;
            }
            exSheet.Cells[3, 4].Value  = dt.Rows[0]["location_name"].ToString();
            exSheet.Cells[3, 10].Value = dt.Rows[0]["print_time"].ToString();

            /*byte[] MyData = new byte[0];
             * MyData = (byte[])dtpicture.Rows[0]["logo_lotus"];
             * MemoryStream stream = new MemoryStream(MyData);
             * exSheet.Shapes.AddPicture(stream);*/
        }

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

        for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
        {
            exSheet.Range["A6"].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;

        for (int l_row = 1; l_row <= dt.Rows.Count; l_row++)
        {
            for (int col = 0; col <= 10; col++)
            {
                if (col == 0 || col == 1 || col == 2 || col == 3 || col == 4 || col == 5 || col == 7 || col == 9 || col == 10)
                {
                    exSheet.Cells[4 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString();
                    exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    //exSheet.Range[5, col + 1, l_row + 5, 7].Font.Bold = false;
                }
                else
                {
                    if (col == 8)
                    {
                        exSheet.Cells[4 + l_row, col + 1].Value = float.Parse(dt.Rows[l_row - 1][col].ToString());
                        exSheet.Cells[4, col + 1, l_row + 4, col + 1].NumberFormat = "#,##0.00";
                        exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment      = XlHAlign.xlHAlignRight;
                        if (float.Parse(dt.Rows[l_row - 1][col].ToString()) == 0.0)
                        {
                            exSheet.Cells[4 + l_row, col + 1].Value = "";
                        }
                    }
                    else
                    {
                        exSheet.Cells[4 + l_row, col + 1].Value = double.Parse(dt.Rows[l_row - 1][col].ToString());
                        exSheet.Cells[4, col + 1, l_row + 4, col + 1].NumberFormat = "#,##0";
                        exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment      = XlHAlign.xlHAlignRight;

                        if (double.Parse(dt.Rows[l_row - 1][col].ToString()) == 0)
                        {
                            exSheet.Cells[4 + l_row, col + 1].Value = "";
                        }
                    }
                }

                if (dt.Rows[l_row - 1][1].ToString() == "" && dt.Rows[l_row - 1][2].ToString() == "" && dt.Rows[l_row - 1][3].ToString() == "" && dt.Rows[l_row - 1][4].ToString() == "")
                {
                    string l_Color3          = "0XF77A7A";
                    Color  colorValueFrmHex3 = ColorTranslator.FromHtml(l_Color3);
                    exSheet.Cells[l_row + 4, 1, l_row + 4, 11].Interior.Color = colorValueFrmHex3;                                       //set color sub total
                }

                if (dt.Rows[l_row - 1][1].ToString() == "" && dt.Rows[l_row - 1][2].ToString() == "" && dt.Rows[l_row - 1][3].ToString() == "" && dt.Rows[l_row - 1][4].ToString() != "")
                {
                    string l_Color3          = "0XD8D8D8";
                    Color  colorValueFrmHex3 = ColorTranslator.FromHtml(l_Color3);
                    exSheet.Cells[l_row + 4, 1, l_row + 4, 11].Interior.Color = colorValueFrmHex3;                                       //set color sub total
                }
            }
        }

        // 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 l_no_parameter = Request["p_no_parameter"];


        string l_parameter = "'" + l_no_parameter + "'";

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

        DataTable dt;


        dt = CtlLib.TableReadOpenCursor("ht_rpt_60300010_OOO", l_parameter);
        //-----header---

        //---end--header---
        //--set-----DataBind--in---Master---
        if (dt.Rows.Count == 0)
        {
            exSheet.Cells[6, 1].Value = "There is not data!";
        }
        if (dt.Rows.Count > 0)
        {
            //exSheet.Cells[2, 1].Value = dt.Rows[0]["date_para"].ToString();
            // exSheet.Cells[2, 15].Value = dt.Rows[0]["print_time"].ToString();
            //	exSheet.Cells[4, 12].Value = dt.Rows[0]["date_header"].ToString();
        }

        //----------------------
        ////end-set-----DataBind--in---Master---
        int l_row_first = 6;

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

        for (int l_row = 0; l_row < dt.Rows.Count; l_row++)
        {
            exSheet.Cells[l_row_first + l_row, 1].Value = dt.Rows[l_row]["room_type"].ToString();
            exSheet.Cells[l_row_first + l_row, 2].Value = dt.Rows[l_row]["room_no"].ToString();
            exSheet.Cells[l_row_first + l_row, 3].Value = dt.Rows[l_row]["from_dt"].ToString();
            exSheet.Cells[l_row_first + l_row, 4].Value = dt.Rows[l_row]["to_dt"].ToString();
            exSheet.Cells[l_row_first + l_row, 5].Value = dt.Rows[l_row]["description"].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;

        // 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();
    }