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