protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string l_date = Request["p_Month"];
string l_week = Request["p_week"];
string TemplateFile = "rpt_60300010_WeeklyRevenue.xls";
string TempFile = "../../../../system/temp/rpt_60300010_WeeklyRevenue_" + 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_week + "'";
dt = CtlLib.TableReadOpenCursor("rpt_60300010_rev_weekly_usd", para);
//-----header---
exSheet.Cells[2, 3].Value = dt.Rows[0]["months"].ToString();
exSheet.Cells[2, 6].Value = l_week;
exSheet.Cells[4, 4].Value = dt.Rows[0]["mon_d"].ToString();
exSheet.Cells[4, 6].Value = dt.Rows[0]["tue_d"].ToString();
exSheet.Cells[4, 8].Value = dt.Rows[0]["wed_d"].ToString();
exSheet.Cells[4, 10].Value = dt.Rows[0]["thu_d"].ToString();
exSheet.Cells[4, 12].Value = dt.Rows[0]["fri_d"].ToString();
exSheet.Cells[4, 14].Value = dt.Rows[0]["sat_d"].ToString();
exSheet.Cells[4, 16].Value = dt.Rows[0]["sun_d"].ToString();
//---end--header---
//--set-----DataBind--in---Master---
exSheet.Cells[7, 4].Value = float.Parse(dt.Rows[0]["d8"].ToString());
exSheet.Cells[7, 5].Value = float.Parse(dt.Rows[0]["d1"].ToString());
exSheet.Cells[7, 6].Value = float.Parse(dt.Rows[0]["d9"].ToString());
exSheet.Cells[7, 7].Value = float.Parse(dt.Rows[0]["d2"].ToString());
exSheet.Cells[7, 8].Value = float.Parse(dt.Rows[0]["d10"].ToString());
exSheet.Cells[7, 9].Value = float.Parse(dt.Rows[0]["d3"].ToString());
exSheet.Cells[7, 10].Value = float.Parse(dt.Rows[0]["d11"].ToString());
exSheet.Cells[7, 11].Value = float.Parse(dt.Rows[0]["d4"].ToString());
exSheet.Cells[7, 12].Value = float.Parse(dt.Rows[0]["d12"].ToString());
exSheet.Cells[7, 13].Value = float.Parse(dt.Rows[0]["d5"].ToString());
exSheet.Cells[7, 14].Value = float.Parse(dt.Rows[0]["d13"].ToString());
exSheet.Cells[7, 15].Value = float.Parse(dt.Rows[0]["d6"].ToString());
exSheet.Cells[7, 16].Value = float.Parse(dt.Rows[0]["d14"].ToString());
exSheet.Cells[7, 17].Value = float.Parse(dt.Rows[0]["d7"].ToString());
exSheet.Cells[7, 18].Value = float.Parse(dt.Rows[0]["sum_guest_qty"].ToString());
exSheet.Cells[7, 19].Value = float.Parse(dt.Rows[0]["mm_sum"].ToString());
exSheet.Cells[7, 20].Value = float.Parse(dt.Rows[0]["avg_guest_qty"].ToString());
exSheet.Cells[7, 21].Value = float.Parse(dt.Rows[0]["avg_amount_qty"].ToString());
exSheet.Cells[8, 4].Value = float.Parse(dt.Rows[1]["d8"].ToString());
exSheet.Cells[8, 5].Value = float.Parse(dt.Rows[1]["d1"].ToString());
exSheet.Cells[8, 6].Value = float.Parse(dt.Rows[1]["d9"].ToString());
exSheet.Cells[8, 7].Value = float.Parse(dt.Rows[1]["d2"].ToString());
exSheet.Cells[8, 8].Value = float.Parse(dt.Rows[1]["d10"].ToString());
exSheet.Cells[8, 9].Value = float.Parse(dt.Rows[1]["d3"].ToString());
exSheet.Cells[8, 10].Value = float.Parse(dt.Rows[1]["d11"].ToString());
exSheet.Cells[8, 11].Value = float.Parse(dt.Rows[1]["d4"].ToString());
exSheet.Cells[8, 12].Value = float.Parse(dt.Rows[1]["d12"].ToString());
exSheet.Cells[8, 13].Value = float.Parse(dt.Rows[1]["d5"].ToString());
exSheet.Cells[8, 14].Value = float.Parse(dt.Rows[1]["d13"].ToString());
exSheet.Cells[8, 15].Value = float.Parse(dt.Rows[1]["d6"].ToString());
exSheet.Cells[8, 16].Value = float.Parse(dt.Rows[1]["d14"].ToString());
exSheet.Cells[8, 17].Value = float.Parse(dt.Rows[1]["d7"].ToString());
exSheet.Cells[8, 18].Value = float.Parse(dt.Rows[1]["sum_guest_qty"].ToString());
exSheet.Cells[8, 19].Value = float.Parse(dt.Rows[1]["mm_sum"].ToString());
exSheet.Cells[8, 20].Value = float.Parse(dt.Rows[1]["avg_guest_qty"].ToString());
exSheet.Cells[8, 21].Value = float.Parse(dt.Rows[1]["avg_amount_qty"].ToString());
exSheet.Cells[9, 4].Value = float.Parse(dt.Rows[2]["d8"].ToString());
exSheet.Cells[9, 5].Value = float.Parse(dt.Rows[2]["d1"].ToString());
exSheet.Cells[9, 6].Value = float.Parse(dt.Rows[2]["d9"].ToString());
exSheet.Cells[9, 7].Value = float.Parse(dt.Rows[2]["d2"].ToString());
exSheet.Cells[9, 8].Value = float.Parse(dt.Rows[2]["d10"].ToString());
exSheet.Cells[9, 9].Value = float.Parse(dt.Rows[2]["d3"].ToString());
exSheet.Cells[9, 10].Value = float.Parse(dt.Rows[2]["d11"].ToString());
exSheet.Cells[9, 11].Value = float.Parse(dt.Rows[2]["d4"].ToString());
exSheet.Cells[9, 12].Value = float.Parse(dt.Rows[2]["d12"].ToString());
exSheet.Cells[9, 13].Value = float.Parse(dt.Rows[2]["d5"].ToString());
exSheet.Cells[9, 14].Value = float.Parse(dt.Rows[2]["d13"].ToString());
exSheet.Cells[9, 15].Value = float.Parse(dt.Rows[2]["d6"].ToString());
exSheet.Cells[9, 16].Value = float.Parse(dt.Rows[2]["d14"].ToString());
exSheet.Cells[9, 17].Value = float.Parse(dt.Rows[2]["d7"].ToString());
exSheet.Cells[9, 18].Value = float.Parse(dt.Rows[2]["sum_guest_qty"].ToString());
exSheet.Cells[9, 19].Value = float.Parse(dt.Rows[2]["mm_sum"].ToString());
exSheet.Cells[9, 20].Value = float.Parse(dt.Rows[2]["avg_guest_qty"].ToString());
exSheet.Cells[9, 21].Value = float.Parse(dt.Rows[2]["avg_amount_qty"].ToString());
exSheet.Cells[10, 4].Value = float.Parse(dt.Rows[3]["d8"].ToString());
exSheet.Cells[10, 5].Value = float.Parse(dt.Rows[3]["d1"].ToString());
exSheet.Cells[10, 6].Value = float.Parse(dt.Rows[3]["d9"].ToString());
exSheet.Cells[10, 7].Value = float.Parse(dt.Rows[3]["d2"].ToString());
exSheet.Cells[10, 8].Value = float.Parse(dt.Rows[3]["d10"].ToString());
exSheet.Cells[10, 9].Value = float.Parse(dt.Rows[3]["d3"].ToString());
exSheet.Cells[10, 10].Value = float.Parse(dt.Rows[3]["d11"].ToString());
exSheet.Cells[10, 11].Value = float.Parse(dt.Rows[3]["d4"].ToString());
exSheet.Cells[10, 12].Value = float.Parse(dt.Rows[3]["d12"].ToString());
exSheet.Cells[10, 13].Value = float.Parse(dt.Rows[3]["d5"].ToString());
exSheet.Cells[10, 14].Value = float.Parse(dt.Rows[3]["d13"].ToString());
exSheet.Cells[10, 15].Value = float.Parse(dt.Rows[3]["d6"].ToString());
exSheet.Cells[10, 16].Value = float.Parse(dt.Rows[3]["d14"].ToString());
exSheet.Cells[10, 17].Value = float.Parse(dt.Rows[3]["d7"].ToString());
exSheet.Cells[10, 18].Value = float.Parse(dt.Rows[3]["sum_guest_qty"].ToString());
exSheet.Cells[10, 19].Value = float.Parse(dt.Rows[3]["mm_sum"].ToString());
exSheet.Cells[10, 20].Value = float.Parse(dt.Rows[3]["avg_guest_qty"].ToString());
exSheet.Cells[10, 21].Value = float.Parse(dt.Rows[3]["avg_amount_qty"].ToString());
exSheet.Cells[11, 4].Value = float.Parse(dt.Rows[4]["d8"].ToString());
exSheet.Cells[11, 5].Value = float.Parse(dt.Rows[4]["d1"].ToString());
exSheet.Cells[11, 6].Value = float.Parse(dt.Rows[4]["d9"].ToString());
exSheet.Cells[11, 7].Value = float.Parse(dt.Rows[4]["d2"].ToString());
exSheet.Cells[11, 8].Value = float.Parse(dt.Rows[4]["d10"].ToString());
exSheet.Cells[11, 9].Value = float.Parse(dt.Rows[4]["d3"].ToString());
exSheet.Cells[11, 10].Value = float.Parse(dt.Rows[4]["d11"].ToString());
exSheet.Cells[11, 11].Value = float.Parse(dt.Rows[4]["d4"].ToString());
exSheet.Cells[11, 12].Value = float.Parse(dt.Rows[4]["d12"].ToString());
exSheet.Cells[11, 13].Value = float.Parse(dt.Rows[4]["d5"].ToString());
exSheet.Cells[11, 14].Value = float.Parse(dt.Rows[4]["d13"].ToString());
exSheet.Cells[11, 15].Value = float.Parse(dt.Rows[4]["d6"].ToString());
exSheet.Cells[11, 16].Value = float.Parse(dt.Rows[4]["d14"].ToString());
exSheet.Cells[11, 17].Value = float.Parse(dt.Rows[4]["d7"].ToString());
exSheet.Cells[11, 18].Value = float.Parse(dt.Rows[4]["sum_guest_qty"].ToString());
exSheet.Cells[11, 19].Value = float.Parse(dt.Rows[4]["mm_sum"].ToString());
exSheet.Cells[11, 20].Value = float.Parse(dt.Rows[4]["avg_guest_qty"].ToString());
exSheet.Cells[11, 21].Value = float.Parse(dt.Rows[4]["avg_amount_qty"].ToString());
exSheet.Cells[12, 4].Value = float.Parse(dt.Rows[5]["d8"].ToString());
exSheet.Cells[12, 5].Value = float.Parse(dt.Rows[5]["d1"].ToString());
exSheet.Cells[12, 6].Value = float.Parse(dt.Rows[5]["d9"].ToString());
exSheet.Cells[12, 7].Value = float.Parse(dt.Rows[5]["d2"].ToString());
exSheet.Cells[12, 8].Value = float.Parse(dt.Rows[5]["d10"].ToString());
exSheet.Cells[12, 9].Value = float.Parse(dt.Rows[5]["d3"].ToString());
exSheet.Cells[12, 10].Value = float.Parse(dt.Rows[5]["d11"].ToString());
exSheet.Cells[12, 11].Value = float.Parse(dt.Rows[5]["d4"].ToString());
exSheet.Cells[12, 12].Value = float.Parse(dt.Rows[5]["d12"].ToString());
exSheet.Cells[12, 13].Value = float.Parse(dt.Rows[5]["d5"].ToString());
exSheet.Cells[12, 14].Value = float.Parse(dt.Rows[5]["d13"].ToString());
exSheet.Cells[12, 15].Value = float.Parse(dt.Rows[5]["d6"].ToString());
exSheet.Cells[12, 16].Value = float.Parse(dt.Rows[5]["d14"].ToString());
exSheet.Cells[12, 17].Value = float.Parse(dt.Rows[5]["d7"].ToString());
exSheet.Cells[12, 18].Value = float.Parse(dt.Rows[5]["sum_guest_qty"].ToString());
exSheet.Cells[12, 19].Value = float.Parse(dt.Rows[5]["mm_sum"].ToString());
exSheet.Cells[12, 20].Value = float.Parse(dt.Rows[5]["avg_guest_qty"].ToString());
exSheet.Cells[12, 21].Value = float.Parse(dt.Rows[5]["avg_amount_qty"].ToString());
exSheet.Cells[13, 4].Value = float.Parse(dt.Rows[6]["d8"].ToString());
exSheet.Cells[13, 5].Value = float.Parse(dt.Rows[6]["d1"].ToString());
exSheet.Cells[13, 6].Value = float.Parse(dt.Rows[6]["d9"].ToString());
exSheet.Cells[13, 7].Value = float.Parse(dt.Rows[6]["d2"].ToString());
exSheet.Cells[13, 8].Value = float.Parse(dt.Rows[6]["d10"].ToString());
exSheet.Cells[13, 9].Value = float.Parse(dt.Rows[6]["d3"].ToString());
exSheet.Cells[13, 10].Value = float.Parse(dt.Rows[6]["d11"].ToString());
exSheet.Cells[13, 11].Value = float.Parse(dt.Rows[6]["d4"].ToString());
exSheet.Cells[13, 12].Value = float.Parse(dt.Rows[6]["d12"].ToString());
exSheet.Cells[13, 13].Value = float.Parse(dt.Rows[6]["d5"].ToString());
exSheet.Cells[13, 14].Value = float.Parse(dt.Rows[6]["d13"].ToString());
exSheet.Cells[13, 15].Value = float.Parse(dt.Rows[6]["d6"].ToString());
exSheet.Cells[13, 16].Value = float.Parse(dt.Rows[6]["d14"].ToString());
exSheet.Cells[13, 17].Value = float.Parse(dt.Rows[6]["d7"].ToString());
exSheet.Cells[13, 18].Value = float.Parse(dt.Rows[6]["sum_guest_qty"].ToString());
exSheet.Cells[13, 19].Value = float.Parse(dt.Rows[6]["mm_sum"].ToString());
exSheet.Cells[13, 20].Value = float.Parse(dt.Rows[6]["avg_guest_qty"].ToString());
exSheet.Cells[13, 21].Value = float.Parse(dt.Rows[6]["avg_amount_qty"].ToString());
exSheet.Cells[14, 4].Value = float.Parse(dt.Rows[7]["d8"].ToString());
exSheet.Cells[14, 5].Value = float.Parse(dt.Rows[7]["d1"].ToString());
exSheet.Cells[14, 6].Value = float.Parse(dt.Rows[7]["d9"].ToString());
exSheet.Cells[14, 7].Value = float.Parse(dt.Rows[7]["d2"].ToString());
exSheet.Cells[14, 8].Value = float.Parse(dt.Rows[7]["d10"].ToString());
exSheet.Cells[14, 9].Value = float.Parse(dt.Rows[7]["d3"].ToString());
exSheet.Cells[14, 10].Value = float.Parse(dt.Rows[7]["d11"].ToString());
exSheet.Cells[14, 11].Value = float.Parse(dt.Rows[7]["d4"].ToString());
exSheet.Cells[14, 12].Value = float.Parse(dt.Rows[7]["d12"].ToString());
exSheet.Cells[14, 13].Value = float.Parse(dt.Rows[7]["d5"].ToString());
exSheet.Cells[14, 14].Value = float.Parse(dt.Rows[7]["d13"].ToString());
exSheet.Cells[14, 15].Value = float.Parse(dt.Rows[7]["d6"].ToString());
exSheet.Cells[14, 16].Value = float.Parse(dt.Rows[7]["d14"].ToString());
exSheet.Cells[14, 17].Value = float.Parse(dt.Rows[7]["d7"].ToString());
exSheet.Cells[14, 18].Value = float.Parse(dt.Rows[7]["sum_guest_qty"].ToString());
exSheet.Cells[14, 19].Value = float.Parse(dt.Rows[7]["mm_sum"].ToString());
exSheet.Cells[14, 20].Value = float.Parse(dt.Rows[7]["avg_guest_qty"].ToString());
exSheet.Cells[14, 21].Value = float.Parse(dt.Rows[7]["avg_amount_qty"].ToString());
exSheet.Cells[15, 4].Value = float.Parse(dt.Rows[8]["d8"].ToString());
exSheet.Cells[15, 5].Value = float.Parse(dt.Rows[8]["d1"].ToString());
exSheet.Cells[15, 6].Value = float.Parse(dt.Rows[8]["d9"].ToString());
exSheet.Cells[15, 7].Value = float.Parse(dt.Rows[8]["d2"].ToString());
exSheet.Cells[15, 8].Value = float.Parse(dt.Rows[8]["d10"].ToString());
exSheet.Cells[15, 9].Value = float.Parse(dt.Rows[8]["d3"].ToString());
exSheet.Cells[15, 10].Value = float.Parse(dt.Rows[8]["d11"].ToString());
exSheet.Cells[15, 11].Value = float.Parse(dt.Rows[8]["d4"].ToString());
exSheet.Cells[15, 12].Value = float.Parse(dt.Rows[8]["d12"].ToString());
exSheet.Cells[15, 13].Value = float.Parse(dt.Rows[8]["d5"].ToString());
exSheet.Cells[15, 14].Value = float.Parse(dt.Rows[8]["d13"].ToString());
exSheet.Cells[15, 15].Value = float.Parse(dt.Rows[8]["d6"].ToString());
exSheet.Cells[15, 16].Value = float.Parse(dt.Rows[8]["d14"].ToString());
exSheet.Cells[15, 17].Value = float.Parse(dt.Rows[8]["d7"].ToString());
exSheet.Cells[15, 18].Value = float.Parse(dt.Rows[8]["sum_guest_qty"].ToString());
exSheet.Cells[15, 19].Value = float.Parse(dt.Rows[8]["mm_sum"].ToString());
exSheet.Cells[15, 20].Value = float.Parse(dt.Rows[8]["avg_guest_qty"].ToString());
exSheet.Cells[15, 21].Value = float.Parse(dt.Rows[8]["avg_amount_qty"].ToString());
exSheet.Cells[16, 4].Value = float.Parse(dt.Rows[9]["d8"].ToString());
exSheet.Cells[16, 5].Value = float.Parse(dt.Rows[9]["d1"].ToString());
exSheet.Cells[16, 6].Value = float.Parse(dt.Rows[9]["d9"].ToString());
exSheet.Cells[16, 7].Value = float.Parse(dt.Rows[9]["d2"].ToString());
exSheet.Cells[16, 8].Value = float.Parse(dt.Rows[9]["d10"].ToString());
exSheet.Cells[16, 9].Value = float.Parse(dt.Rows[9]["d3"].ToString());
exSheet.Cells[16, 10].Value = float.Parse(dt.Rows[9]["d11"].ToString());
exSheet.Cells[16, 11].Value = float.Parse(dt.Rows[9]["d4"].ToString());
exSheet.Cells[16, 12].Value = float.Parse(dt.Rows[9]["d12"].ToString());
exSheet.Cells[16, 13].Value = float.Parse(dt.Rows[9]["d5"].ToString());
exSheet.Cells[16, 14].Value = float.Parse(dt.Rows[9]["d13"].ToString());
exSheet.Cells[16, 15].Value = float.Parse(dt.Rows[9]["d6"].ToString());
exSheet.Cells[16, 16].Value = float.Parse(dt.Rows[9]["d14"].ToString());
exSheet.Cells[16, 17].Value = float.Parse(dt.Rows[9]["d7"].ToString());
exSheet.Cells[16, 18].Value = float.Parse(dt.Rows[9]["sum_guest_qty"].ToString());
exSheet.Cells[16, 19].Value = float.Parse(dt.Rows[9]["mm_sum"].ToString());
exSheet.Cells[16, 20].Value = float.Parse(dt.Rows[9]["avg_guest_qty"].ToString());
exSheet.Cells[16, 21].Value = float.Parse(dt.Rows[9]["avg_amount_qty"].ToString());
exSheet.Cells[17, 4].Value = float.Parse(dt.Rows[10]["d8"].ToString());
exSheet.Cells[17, 5].Value = float.Parse(dt.Rows[10]["d1"].ToString());
exSheet.Cells[17, 6].Value = float.Parse(dt.Rows[10]["d9"].ToString());
exSheet.Cells[17, 7].Value = float.Parse(dt.Rows[10]["d2"].ToString());
exSheet.Cells[17, 8].Value = float.Parse(dt.Rows[10]["d10"].ToString());
exSheet.Cells[17, 9].Value = float.Parse(dt.Rows[10]["d3"].ToString());
exSheet.Cells[17, 10].Value = float.Parse(dt.Rows[10]["d11"].ToString());
exSheet.Cells[17, 11].Value = float.Parse(dt.Rows[10]["d4"].ToString());
exSheet.Cells[17, 12].Value = float.Parse(dt.Rows[10]["d12"].ToString());
exSheet.Cells[17, 13].Value = float.Parse(dt.Rows[10]["d5"].ToString());
exSheet.Cells[17, 14].Value = float.Parse(dt.Rows[10]["d13"].ToString());
exSheet.Cells[17, 15].Value = float.Parse(dt.Rows[10]["d6"].ToString());
exSheet.Cells[17, 16].Value = float.Parse(dt.Rows[10]["d14"].ToString());
exSheet.Cells[17, 17].Value = float.Parse(dt.Rows[10]["d7"].ToString());
exSheet.Cells[17, 18].Value = float.Parse(dt.Rows[10]["sum_guest_qty"].ToString());
exSheet.Cells[17, 19].Value = float.Parse(dt.Rows[10]["mm_sum"].ToString());
exSheet.Cells[17, 20].Value = float.Parse(dt.Rows[10]["avg_guest_qty"].ToString());
exSheet.Cells[17, 21].Value = float.Parse(dt.Rows[10]["avg_amount_qty"].ToString());
exSheet.Cells[18, 4].Value = float.Parse(dt.Rows[11]["d8"].ToString());
exSheet.Cells[18, 5].Value = float.Parse(dt.Rows[11]["d1"].ToString());
exSheet.Cells[18, 6].Value = float.Parse(dt.Rows[11]["d9"].ToString());
exSheet.Cells[18, 7].Value = float.Parse(dt.Rows[11]["d2"].ToString());
exSheet.Cells[18, 8].Value = float.Parse(dt.Rows[11]["d10"].ToString());
exSheet.Cells[18, 9].Value = float.Parse(dt.Rows[11]["d3"].ToString());
exSheet.Cells[18, 10].Value = float.Parse(dt.Rows[11]["d11"].ToString());
exSheet.Cells[18, 11].Value = float.Parse(dt.Rows[11]["d4"].ToString());
exSheet.Cells[18, 12].Value = float.Parse(dt.Rows[11]["d12"].ToString());
exSheet.Cells[18, 13].Value = float.Parse(dt.Rows[11]["d5"].ToString());
exSheet.Cells[18, 14].Value = float.Parse(dt.Rows[11]["d13"].ToString());
exSheet.Cells[18, 15].Value = float.Parse(dt.Rows[11]["d6"].ToString());
exSheet.Cells[18, 16].Value = float.Parse(dt.Rows[11]["d14"].ToString());
exSheet.Cells[18, 17].Value = float.Parse(dt.Rows[11]["d7"].ToString());
exSheet.Cells[18, 18].Value = float.Parse(dt.Rows[11]["sum_guest_qty"].ToString());
exSheet.Cells[18, 19].Value = float.Parse(dt.Rows[11]["mm_sum"].ToString());
exSheet.Cells[18, 20].Value = float.Parse(dt.Rows[11]["avg_guest_qty"].ToString());
exSheet.Cells[18, 21].Value = float.Parse(dt.Rows[11]["avg_amount_qty"].ToString());
exSheet.Cells[19, 4].Value = float.Parse(dt.Rows[12]["d8"].ToString());
exSheet.Cells[19, 5].Value = float.Parse(dt.Rows[12]["d1"].ToString());
exSheet.Cells[19, 6].Value = float.Parse(dt.Rows[12]["d9"].ToString());
exSheet.Cells[19, 7].Value = float.Parse(dt.Rows[12]["d2"].ToString());
exSheet.Cells[19, 8].Value = float.Parse(dt.Rows[12]["d10"].ToString());
exSheet.Cells[19, 9].Value = float.Parse(dt.Rows[12]["d3"].ToString());
exSheet.Cells[19, 10].Value = float.Parse(dt.Rows[12]["d11"].ToString());
exSheet.Cells[19, 11].Value = float.Parse(dt.Rows[12]["d4"].ToString());
exSheet.Cells[19, 12].Value = float.Parse(dt.Rows[12]["d12"].ToString());
exSheet.Cells[19, 13].Value = float.Parse(dt.Rows[12]["d5"].ToString());
exSheet.Cells[19, 14].Value = float.Parse(dt.Rows[12]["d13"].ToString());
exSheet.Cells[19, 15].Value = float.Parse(dt.Rows[12]["d6"].ToString());
exSheet.Cells[19, 16].Value = float.Parse(dt.Rows[12]["d14"].ToString());
exSheet.Cells[19, 17].Value = float.Parse(dt.Rows[12]["d7"].ToString());
exSheet.Cells[19, 18].Value = float.Parse(dt.Rows[12]["sum_guest_qty"].ToString());
exSheet.Cells[19, 19].Value = float.Parse(dt.Rows[12]["mm_sum"].ToString());
exSheet.Cells[19, 20].Value = float.Parse(dt.Rows[12]["avg_guest_qty"].ToString());
exSheet.Cells[19, 21].Value = float.Parse(dt.Rows[12]["avg_amount_qty"].ToString());
exSheet.Cells[20, 4].Value = float.Parse(dt.Rows[13]["d8"].ToString());
exSheet.Cells[20, 5].Value = float.Parse(dt.Rows[13]["d1"].ToString());
exSheet.Cells[20, 6].Value = float.Parse(dt.Rows[13]["d9"].ToString());
exSheet.Cells[20, 7].Value = float.Parse(dt.Rows[13]["d2"].ToString());
exSheet.Cells[20, 8].Value = float.Parse(dt.Rows[13]["d10"].ToString());
exSheet.Cells[20, 9].Value = float.Parse(dt.Rows[13]["d3"].ToString());
exSheet.Cells[20, 10].Value = float.Parse(dt.Rows[13]["d11"].ToString());
exSheet.Cells[20, 11].Value = float.Parse(dt.Rows[13]["d4"].ToString());
exSheet.Cells[20, 12].Value = float.Parse(dt.Rows[13]["d12"].ToString());
exSheet.Cells[20, 13].Value = float.Parse(dt.Rows[13]["d5"].ToString());
exSheet.Cells[20, 14].Value = float.Parse(dt.Rows[13]["d13"].ToString());
exSheet.Cells[20, 15].Value = float.Parse(dt.Rows[13]["d6"].ToString());
exSheet.Cells[20, 16].Value = float.Parse(dt.Rows[13]["d14"].ToString());
exSheet.Cells[20, 17].Value = float.Parse(dt.Rows[13]["d7"].ToString());
exSheet.Cells[20, 18].Value = float.Parse(dt.Rows[13]["sum_guest_qty"].ToString());
exSheet.Cells[20, 19].Value = float.Parse(dt.Rows[13]["mm_sum"].ToString());
exSheet.Cells[20, 20].Value = float.Parse(dt.Rows[13]["avg_guest_qty"].ToString());
exSheet.Cells[20, 21].Value = float.Parse(dt.Rows[13]["avg_amount_qty"].ToString());
exSheet.Cells[21, 4].Value = float.Parse(dt.Rows[14]["d8"].ToString());
exSheet.Cells[21, 5].Value = float.Parse(dt.Rows[14]["d1"].ToString());
exSheet.Cells[21, 6].Value = float.Parse(dt.Rows[14]["d9"].ToString());
exSheet.Cells[21, 7].Value = float.Parse(dt.Rows[14]["d2"].ToString());
exSheet.Cells[21, 8].Value = float.Parse(dt.Rows[14]["d10"].ToString());
exSheet.Cells[21, 9].Value = float.Parse(dt.Rows[14]["d3"].ToString());
exSheet.Cells[21, 10].Value = float.Parse(dt.Rows[14]["d11"].ToString());
exSheet.Cells[21, 11].Value = float.Parse(dt.Rows[14]["d4"].ToString());
exSheet.Cells[21, 12].Value = float.Parse(dt.Rows[14]["d12"].ToString());
exSheet.Cells[21, 13].Value = float.Parse(dt.Rows[14]["d5"].ToString());
exSheet.Cells[21, 14].Value = float.Parse(dt.Rows[14]["d13"].ToString());
exSheet.Cells[21, 15].Value = float.Parse(dt.Rows[14]["d6"].ToString());
exSheet.Cells[21, 16].Value = float.Parse(dt.Rows[14]["d14"].ToString());
exSheet.Cells[21, 17].Value = float.Parse(dt.Rows[14]["d7"].ToString());
exSheet.Cells[21, 18].Value = float.Parse(dt.Rows[14]["sum_guest_qty"].ToString());
exSheet.Cells[21, 19].Value = float.Parse(dt.Rows[14]["mm_sum"].ToString());
exSheet.Cells[21, 20].Value = float.Parse(dt.Rows[14]["avg_guest_qty"].ToString());
exSheet.Cells[21, 21].Value = float.Parse(dt.Rows[14]["avg_amount_qty"].ToString());
exSheet.Cells[22, 4].Value = float.Parse(dt.Rows[15]["d8"].ToString());
exSheet.Cells[22, 5].Value = float.Parse(dt.Rows[15]["d1"].ToString());
exSheet.Cells[22, 6].Value = float.Parse(dt.Rows[15]["d9"].ToString());
exSheet.Cells[22, 7].Value = float.Parse(dt.Rows[15]["d2"].ToString());
exSheet.Cells[22, 8].Value = float.Parse(dt.Rows[15]["d10"].ToString());
exSheet.Cells[22, 9].Value = float.Parse(dt.Rows[15]["d3"].ToString());
exSheet.Cells[22, 10].Value = float.Parse(dt.Rows[15]["d11"].ToString());
exSheet.Cells[22, 11].Value = float.Parse(dt.Rows[15]["d4"].ToString());
exSheet.Cells[22, 12].Value = float.Parse(dt.Rows[15]["d12"].ToString());
exSheet.Cells[22, 13].Value = float.Parse(dt.Rows[15]["d5"].ToString());
exSheet.Cells[22, 14].Value = float.Parse(dt.Rows[15]["d13"].ToString());
exSheet.Cells[22, 15].Value = float.Parse(dt.Rows[15]["d6"].ToString());
exSheet.Cells[22, 16].Value = float.Parse(dt.Rows[15]["d14"].ToString());
exSheet.Cells[22, 17].Value = float.Parse(dt.Rows[15]["d7"].ToString());
exSheet.Cells[22, 18].Value = float.Parse(dt.Rows[15]["sum_guest_qty"].ToString());
exSheet.Cells[22, 19].Value = float.Parse(dt.Rows[15]["mm_sum"].ToString());
exSheet.Cells[22, 20].Value = float.Parse(dt.Rows[15]["avg_guest_qty"].ToString());
exSheet.Cells[22, 21].Value = float.Parse(dt.Rows[15]["avg_amount_qty"].ToString());
exSheet.Cells[23, 4].Value = float.Parse(dt.Rows[16]["d8"].ToString());
exSheet.Cells[23, 5].Value = float.Parse(dt.Rows[16]["d1"].ToString());
exSheet.Cells[23, 6].Value = float.Parse(dt.Rows[16]["d9"].ToString());
exSheet.Cells[23, 7].Value = float.Parse(dt.Rows[16]["d2"].ToString());
exSheet.Cells[23, 8].Value = float.Parse(dt.Rows[16]["d10"].ToString());
exSheet.Cells[23, 9].Value = float.Parse(dt.Rows[16]["d3"].ToString());
exSheet.Cells[23, 10].Value = float.Parse(dt.Rows[16]["d11"].ToString());
exSheet.Cells[23, 11].Value = float.Parse(dt.Rows[16]["d4"].ToString());
exSheet.Cells[23, 12].Value = float.Parse(dt.Rows[16]["d12"].ToString());
exSheet.Cells[23, 13].Value = float.Parse(dt.Rows[16]["d5"].ToString());
exSheet.Cells[23, 14].Value = float.Parse(dt.Rows[16]["d13"].ToString());
exSheet.Cells[23, 15].Value = float.Parse(dt.Rows[16]["d6"].ToString());
exSheet.Cells[23, 16].Value = float.Parse(dt.Rows[16]["d14"].ToString());
exSheet.Cells[23, 17].Value = float.Parse(dt.Rows[16]["d7"].ToString());
exSheet.Cells[23, 18].Value = float.Parse(dt.Rows[16]["sum_guest_qty"].ToString());
exSheet.Cells[23, 19].Value = float.Parse(dt.Rows[16]["mm_sum"].ToString());
exSheet.Cells[23, 20].Value = float.Parse(dt.Rows[16]["avg_guest_qty"].ToString());
exSheet.Cells[23, 21].Value = float.Parse(dt.Rows[16]["avg_amount_qty"].ToString());
exSheet.Cells[24, 4].Value = float.Parse(dt.Rows[17]["d8"].ToString());
exSheet.Cells[24, 5].Value = float.Parse(dt.Rows[17]["d1"].ToString());
exSheet.Cells[24, 6].Value = float.Parse(dt.Rows[17]["d9"].ToString());
exSheet.Cells[24, 7].Value = float.Parse(dt.Rows[17]["d2"].ToString());
exSheet.Cells[24, 8].Value = float.Parse(dt.Rows[17]["d10"].ToString());
exSheet.Cells[24, 9].Value = float.Parse(dt.Rows[17]["d3"].ToString());
exSheet.Cells[24, 10].Value = float.Parse(dt.Rows[17]["d11"].ToString());
exSheet.Cells[24, 11].Value = float.Parse(dt.Rows[17]["d4"].ToString());
exSheet.Cells[24, 12].Value = float.Parse(dt.Rows[17]["d12"].ToString());
exSheet.Cells[24, 13].Value = float.Parse(dt.Rows[17]["d5"].ToString());
exSheet.Cells[24, 14].Value = float.Parse(dt.Rows[17]["d13"].ToString());
exSheet.Cells[24, 15].Value = float.Parse(dt.Rows[17]["d6"].ToString());
exSheet.Cells[24, 16].Value = float.Parse(dt.Rows[17]["d14"].ToString());
exSheet.Cells[24, 17].Value = float.Parse(dt.Rows[17]["d7"].ToString());
exSheet.Cells[24, 18].Value = float.Parse(dt.Rows[17]["sum_guest_qty"].ToString());
exSheet.Cells[24, 19].Value = float.Parse(dt.Rows[17]["mm_sum"].ToString());
exSheet.Cells[24, 20].Value = float.Parse(dt.Rows[17]["avg_guest_qty"].ToString());
exSheet.Cells[24, 21].Value = float.Parse(dt.Rows[17]["avg_amount_qty"].ToString());
exSheet.Cells[25, 4].Value = float.Parse(dt.Rows[18]["d8"].ToString());
exSheet.Cells[25, 5].Value = float.Parse(dt.Rows[18]["d1"].ToString());
exSheet.Cells[25, 6].Value = float.Parse(dt.Rows[18]["d9"].ToString());
exSheet.Cells[25, 7].Value = float.Parse(dt.Rows[18]["d2"].ToString());
exSheet.Cells[25, 8].Value = float.Parse(dt.Rows[18]["d10"].ToString());
exSheet.Cells[25, 9].Value = float.Parse(dt.Rows[18]["d3"].ToString());
exSheet.Cells[25, 10].Value = float.Parse(dt.Rows[18]["d11"].ToString());
exSheet.Cells[25, 11].Value = float.Parse(dt.Rows[18]["d4"].ToString());
exSheet.Cells[25, 12].Value = float.Parse(dt.Rows[18]["d12"].ToString());
exSheet.Cells[25, 13].Value = float.Parse(dt.Rows[18]["d5"].ToString());
exSheet.Cells[25, 14].Value = float.Parse(dt.Rows[18]["d13"].ToString());
exSheet.Cells[25, 15].Value = float.Parse(dt.Rows[18]["d6"].ToString());
exSheet.Cells[25, 16].Value = float.Parse(dt.Rows[18]["d14"].ToString());
exSheet.Cells[25, 17].Value = float.Parse(dt.Rows[18]["d7"].ToString());
exSheet.Cells[25, 18].Value = float.Parse(dt.Rows[18]["sum_guest_qty"].ToString());
exSheet.Cells[25, 19].Value = float.Parse(dt.Rows[18]["mm_sum"].ToString());
exSheet.Cells[25, 20].Value = float.Parse(dt.Rows[18]["avg_guest_qty"].ToString());
exSheet.Cells[25, 21].Value = float.Parse(dt.Rows[18]["avg_amount_qty"].ToString());
//----------------------
////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_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();
}
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(Session["APP_DBUSER"].ToString());
string TemplateFile = "rpt_60280060_Invoice_List.xls";
string TempFile = "../../../../system/temp/rpt_60280060_Invoice_List_" + 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_month = Request["p_month"];
string p_crt_by = Request["p_crt_by"];
string para = "'" + p_month + "','" + p_crt_by + "'";
DataTable dt = CtlLib.TableReadOpenCursor("ht_sel_60280060_month", para);
// MASTER
// INSERT ROWS
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A9"].Rows.EntireRow.Insert();//insert row new of sheet
}
// SET DATA
int pos = 8;
int i = 0;
for (i = 0; i < dt.Rows.Count; i++)
{
// exSheet.Cells[pos + i, 1 ].Value = i+1;
exSheet.Cells[pos + i, 1].Value = dt.Rows[i]["invoice_no"];
// exSheet.Cells[pos + i, 3 ].Value = dt.Rows[i]["invoice_no"];
// exSheet.Cells[pos + i, 4 ].Value = dt.Rows[i]["pay_date"];
// exSheet.Cells[pos + i, 5 ].Value = dt.Rows[i]["guest_name"];
// exSheet.Cells[pos + i, 6 ].Value = dt.Rows[i]["checkin_date"];
// exSheet.Cells[pos + i, 7 ].Value = dt.Rows[i]["checkout_date"];
// exSheet.Cells[pos + i, 8 ].Value = dt.Rows[i]["partner_name"];
// exSheet.Cells[pos + i, 9 ].Value = dt.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);
}
// 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_DR07.xls";
string TempFile = "../../../../system/temp/rpt_bisc00030_DR07_" + 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 dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00030_DR07_1", l_parameter);
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00030_DR07_2", l_parameter);
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A12"].Rows.EntireRow.Insert();//insert row new of sheet
}
if (dtMaster.Rows.Count > 0)
{
exSheet.Cells["C8"].Value = dtMaster.Rows[0]["doc_date"].ToString();
}
//-----------------------
exSheet.Cells["L8"].Value = DateTime.Now.ToString("HH:mm");
int l_row = 0;
// loop detail not percent
for (l_row = 0; l_row < dtDetail.Rows.Count; l_row++)
{
exSheet.Cells[11 + l_row, 1].Value = dtDetail.Rows[l_row]["item_seq"];
exSheet.Cells[11 + l_row, 2].Value = dtDetail.Rows[l_row]["item_code"];
exSheet.Cells[11 + l_row, 3].Value = dtDetail.Rows[l_row]["virtual_code"];
exSheet.Cells[11 + l_row, 4].Value = dtDetail.Rows[l_row]["item_name"];
exSheet.Cells[11 + l_row, 5].Value = dtDetail.Rows[l_row]["virtual_name"];
exSheet.Cells[11 + l_row, 6].Value = dtDetail.Rows[l_row]["uom"];
exSheet.Cells[11 + l_row, 7].Value = dtDetail.Rows[l_row]["begin_act"];
exSheet.Cells[11 + l_row, 8].Value = dtDetail.Rows[l_row]["begin_doc"];
exSheet.Cells[11 + l_row, 9].Value = dtDetail.Rows[l_row]["income_qty"];
exSheet.Cells[11 + l_row, 10].Value = "=(H" + (11 + l_row) + "+I" + (11 + l_row) + ")";
exSheet.Cells[11 + l_row, 11].Value = "=(G" + (11 + l_row) + "+I" + (11 + l_row) + ")";
exSheet.Cells[11 + l_row, 12].Value = dtDetail.Rows[l_row]["outgo_qty"];
exSheet.Cells[11 + l_row, 13].Value = dtDetail.Rows[l_row]["return_qty"];
exSheet.Cells[11 + l_row, 14].Value = "=J" + (11 + l_row) + "-L" + (11 + l_row) + "+M" + (11 + l_row);
exSheet.Cells[11 + l_row, 15].Value = dtDetail.Rows[l_row]["plan_qty"];
exSheet.Cells[11 + l_row, 16].Value = "=O" + (11 + l_row) + "/25";
exSheet.Cells[11 + l_row, 17].Value = dtDetail.Rows[l_row]["val01"];
exSheet.Cells[11 + l_row, 18].Value = "=K" + (11 + l_row) + "-L" + (11 + l_row) + "+M" + (11 + l_row);
exSheet.Cells[11 + l_row, 19].Value = dtDetail.Rows[l_row]["unit_price"];
exSheet.Cells[11 + l_row, 20].Value = "=S" + (11 + l_row) + "*R" + (11 + l_row);
exSheet.Cells[11 + l_row, 23].Value = "=R" + (11 + l_row) + "/(O" + (11 + l_row) + "/30)";
exSheet.Cells[11 + l_row, 24].Value = dtDetail.Rows[l_row]["po_qty"];
}
exSheet.Cells[11 + l_row, 7].Value = "=SUM(G11:G" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 8].Value = "=SUM(H11:H" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 9].Value = "=SUM(I11:I" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 10].Value = "=SUM(J11:J" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 11].Value = "=SUM(K11:K" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 12].Value = "=SUM(L11:L" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 13].Value = "=SUM(M11:M" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 14].Value = "=SUM(N11:N" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 15].Value = "=SUM(O11:O" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 16].Value = "=SUM(P11:P" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 17].Value = "=SUM(Q11:Q" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 18].Value = "=SUM(R11:R" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 20].Value = "=SUM(T11:T" + (11 + l_row - 1) + ")";
exSheet.Cells[11 + l_row, 24].Value = "=SUM(X11:X" + (11 + l_row - 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(Session["APP_DBUSER"].ToString());
string l_room_no_guest_group = Request["p_room"];
string l_report_type = Request["p_report_type"];
string TemplateFile = "rpt_60250260_inhouse_list_detail.xls";
string TempFile = "../../../../system/temp/rpt_60250260_inhouse_list_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_room_no_guest_group + "','" + l_report_type + "'";
dt = CtlLib.TableReadOpenCursor("ht_rpt_60250260_new", 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 = "Total : "+dt.Rows.Count+" Rooms.";
exSheet.Cells[3, 13].Value = dt.Rows[0]["print_time"].ToString();
//exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString();
if (l_report_type == "1")
{
exSheet.Cells[2, 2].Value = "Inhouse list ";
}
if (l_report_type == "2")
{
exSheet.Cells[2, 2].Value = "Inhouse list (Normal)";
}
if (l_report_type == "3")
{
exSheet.Cells[2, 2].Value = "Inhouse list (VIP)";
}
}
//----------------------
////end-set-----DataBind--in---Master---
int l_row_first = 5, l_cnt = dt.Rows.Count;
double tt_adult_total_guest = 0, tt_child_total_guest = 0, l_total_room = 0;
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A6"].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]["guest_name"].ToString();
exSheet.Cells[l_row_first + l_row, 2].Value = dt.Rows[l_row]["checkin_date"].ToString();
exSheet.Cells[l_row_first + l_row, 3].Value = dt.Rows[l_row]["departure_date"].ToString();
if (dt.Rows[l_row]["room_no"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 4].Value = double.Parse(dt.Rows[l_row]["room_no"].ToString());
}
exSheet.Cells[l_row_first + l_row, 5].Value = dt.Rows[l_row]["room_code"].ToString();
if (dt.Rows[l_row]["room_rate"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 6].Value = double.Parse(dt.Rows[l_row]["room_rate"].ToString());
}
if (dt.Rows[l_row]["child_total_guest"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 7].Value = double.Parse(dt.Rows[l_row]["child_total_guest"].ToString());
tt_child_total_guest = tt_child_total_guest + double.Parse(dt.Rows[l_row]["child_total_guest"].ToString());
}
if (dt.Rows[l_row]["adult_total_guest"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 8].Value = double.Parse(dt.Rows[l_row]["adult_total_guest"].ToString());
tt_adult_total_guest = tt_adult_total_guest + double.Parse(dt.Rows[l_row]["adult_total_guest"].ToString());
}
exSheet.Cells[l_row_first + l_row, 9].Value = dt.Rows[l_row]["company_name"].ToString();
exSheet.Cells[l_row_first + l_row, 10].Value = dt.Rows[l_row]["nation"].ToString();
exSheet.Cells[l_row_first + l_row, 11].Value = dt.Rows[l_row]["request_master"].ToString();
exSheet.Cells[l_row_first + l_row, 12].Value = dt.Rows[l_row]["detail_request"].ToString();
//exSheet.Cells[l_row_first + l_row, 13].Value = dt.Rows[l_row]["adult_total_guest"].ToString(); special service
exSheet.Cells[l_row_first + l_row, 14].Value = dt.Rows[l_row]["VIP"].ToString();
exSheet.Cells[l_row_first + l_row, 15].Value = dt.Rows[l_row]["birthday"].ToString();
}
exSheet.Cells[l_row_first + l_cnt, 7].Value = tt_child_total_guest;
exSheet.Cells[l_row_first + l_cnt, 8].Value = tt_adult_total_guest;
//exSheet.Cells[3, 1].Value = "Total : "+l_total_room+" Room(s.)";
// 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_dsbs00200_YJ03.xls";
string TempFile = "../../../../system/temp/rpt_dsbs00200_YJ03_" + 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_YJ03_2", para);
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00200_YJ03_3", para);
if (dt.Rows.Count > 0)
{
exSheet.Cells["C6"].Value = dt.Rows[0]["slip_no"];
exSheet.Cells["F6"].Value = dt.Rows[0]["order_date"];
exSheet.Cells["B7"].Value = dt.Rows[0]["partner_name"];
exSheet.Cells["A12"].Value = dt.Rows[0]["PACKING_MARKS"];
}
// INSERT ROWS
for (int l_addrow = 0; l_addrow < dtDetail.Rows.Count - 1; l_addrow++)
{
exSheet.Range["A10"].Rows.EntireRow.Insert();//insert row new of sheet
}
// SET DATA
int pos = 9;
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]["ref_no"];
exSheet.Cells[pos + i, 2].Value = dtDetail.Rows[i]["description"];
exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["color"];
exSheet.Cells[pos + i, 4].Value = dtDetail.Rows[i]["ord_qty"];
exSheet.Cells[pos + i, 5].Value = dtDetail.Rows[i]["ord_uom"];
exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["unit_price"];
exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["item_amount"];
exSheet.Cells[pos + i, 8].Value = dtDetail.Rows[i]["month_plan"];
exSheet.Cells[pos + i, 9].Value = dtDetail.Rows[i]["item_etd"];
//-------------
}
int t = pos + i - 1;
exSheet.Cells[pos + i, 4].Value = "=sum(D9:D" + t + ")";
exSheet.Cells[pos + i, 7].Value = "=sum(G4:G" + 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_dt_from = Request["p_dtfrm"];
string l_dt_to = Request["p_dtto"];
string TemplateFile = "rpt_60300010_complimentary.xls";
string TempFile = "../../../../system/temp/rpt_60300010_complimentary_" + 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_com", para);
//-----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[3, 1].Value = "Date :"+l_dt_from.Substring(6, 2)+"/"+l_dt_from.Substring(4, 2)+"/"+l_dt_from.Substring(0,4);
exSheet.Cells[3, 6].Value = dt.Rows[0]["print_time"].ToString();
exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].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;
int l_first = 5;
if (dt.Rows.Count > 0)
{
for (int l_row = 0; l_row < dt.Rows.Count; l_row++)
{
exSheet.Cells[l_first + l_row, 1].Value = dt.Rows[l_row]["last_name"].ToString();
exSheet.Cells[l_first + l_row, 2].Value = dt.Rows[l_row]["total_guest"].ToString();
exSheet.Cells[l_first + l_row, 3].Value = dt.Rows[l_row]["ROOM_TYPE"].ToString();
if (dt.Rows[l_row]["room_no"].ToString() != "")
{
exSheet.Cells[l_first + l_row, 4].Value = double.Parse(dt.Rows[l_row]["room_no"].ToString());
}
exSheet.Cells[l_first + l_row, 5].Value = dt.Rows[l_row]["checkin_date"].ToString();
exSheet.Cells[l_first + l_row, 6].Value = dt.Rows[l_row]["departure_date"].ToString();
exSheet.Cells[l_first + l_row, 7].Value = dt.Rows[l_row]["company_name"].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();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string l_fr = Request["p_fr"];
string l_to = Request["p_to"];
string l_loc = Request["p_loc"];
string TemplateFile = "rpt_60270030_Item_Type.xls";
string TempFile = "../../../../system/temp/rpt_60270030_Item_Type_" + 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 sheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string para = "";
string[][] title = new string[2][] { new string[] { "1", "2", "6", "7", "8", "9", "10", "11", "12", "13" },
new string[] { "Item Code", "Item Name", "Unit", "Q.Ty", "Price", "Sub", "Service", "Tax", "Discount", "Total" } };
int row = 6, tt_dt = 0;
DataTable dt;
para = "'" + l_fr + "','" + l_to + "','" + l_loc + "'";
dt = CtlLib.TableReadOpenCursor("rpt_60270030_item_type", para);
if (dt.Rows.Count > 0)
{
sheet.Cells[4, 5].Value = dt.Rows[0][12];
sheet.Cells[4, 11].Value = dt.Rows[0][13];
sheet.Cells[5, 5].Value = dt.Rows[0][14];
for (int y = 0; y < dt.Rows.Count; y++)
{
if (dt.Rows[y][10].ToString() != "" && dt.Rows[y][1].ToString() == "")
{
row++; for (int x = 0; x < title[0].Length; x++)
{
sheet.Cells[row, int.Parse(title[0][x])].Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlDot;
sheet.Cells[row, int.Parse(title[0][x])].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
if (title[0][x] == "2")
{
sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlDot;
sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
}
switch (x)
{
case 0:
sheet.Cells[row, int.Parse(title[0][x])].Value = "Total " + dt.Rows[y][10].ToString() + " " + dt.Rows[y][11].ToString();
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
break;
case 3:
sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString());
sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0";
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
break;
case 5:
case 6:
case 7:
case 8:
case 9:
sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString());
sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0";
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignRight;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
break;
}
}
row++; sheet.Cells[row, 1].RowHeight = 20;
}
else if (dt.Rows[y][10].ToString() != "" && dt.Rows[y][1].ToString() != "")
{
if (y == 0 || (y > 0 && dt.Rows[y - 1][10].ToString() != "" && dt.Rows[y - 1][1].ToString() == ""))
{
if (y == 0)
{
for (int x = 0; x < title[0].Length; x++)
{
sheet.Cells[row, int.Parse(title[0][x])].Value = "'" + title[1][x];
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
sheet.Cells[row, int.Parse(title[0][x])].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
if (title[0][x] == "2")
{
sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Merge();
sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
}
}
}
row++; for (int x = 0; x < title[0].Length; x++)
{
switch (x)
{
case 0:
sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][10].ToString() + " " + dt.Rows[y][11].ToString();
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignBottom;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
sheet.Cells[row, int.Parse(title[0][x])].Font.Underline = XlUnderlineStyle.xlUnderlineStyleSingle;
break;
}
}
}
row++; for (int x = 0; x < title[0].Length; x++)
{
//sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 15;
switch (x)
{
case 0:
sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][x].ToString();
break;
case 1:
sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][x].ToString();
sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Merge();
break;
case 2:
sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][x].ToString();
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter;
break;
case 3:
sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString());
sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0";
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter;
break;
default:
sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString());
sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0";
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignRight;
break;
}
}
}
else if (dt.Rows[y][10].ToString() == "" && dt.Rows[y][1].ToString() == "")
{
row++; for (int x = 0; x < title[0].Length; x++)
{
switch (x)
{
case 3:
sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString());
sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0";
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
break;
case 5:
case 6:
case 7:
case 8:
case 9:
sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x].ToString());
sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0";
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignRight;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
break;
}
}
}
}
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = sheet.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_dtfrm = Request["p_from"];
string l_dtto = Request["p_to"];
string l_guest_name = Request["p_guest_name"];
string l_parameter = "'" + l_dtfrm + "','" + l_dtto + "'";
string TemplateFile = "rpt_6028010_revenue_item.xls";
string TempFile = "../../../../system/temp/rpt_6028010_revenue_item_" + 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 dtMst = CtlLib.TableReadOpenCursor("acnt.sp_rpt_gfrp00010_mst", l_parameter);
DataTable dtDetail = CtlLib.TableReadOpenCursor("ht_rpt_60280100_1", l_parameter);
//-----header---
//---end--header---
//--set-----DataBind--in---Master---
if (dtDetail.Rows.Count > 0)
{
exSheet.Cells[3, 1].Value = dtDetail.Rows[0]["date_para"].ToString();
exSheet.Cells[3, 7].Value = dtDetail.Rows[0]["print_time"].ToString();
}
//----------------------
int l_first_row1 = 6;
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range[l_first_row1 + 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_first_row = 5;
int l_cnt = dtDetail.Rows.Count;
double l_total_qty = 0, l_total_amt = 0;
for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++)
{
exSheet.Cells[l_first_row + l_row, 1].Value = dtDetail.Rows[l_row - 1]["invoice_date"];
exSheet.Cells[l_first_row + l_row, 2].Value = dtDetail.Rows[l_row - 1]["invoice_id"];
exSheet.Cells[l_first_row + l_row, 3].Value = dtDetail.Rows[l_row - 1]["item_name"];
exSheet.Cells[l_first_row + l_row, 4].Value = dtDetail.Rows[l_row - 1]["item_qty"];
if (dtDetail.Rows[l_row - 1]["item_qty"].ToString() != "")
{
l_total_qty = l_total_qty + double.Parse(dtDetail.Rows[l_row - 1]["item_qty"].ToString());
}
exSheet.Cells[l_first_row + l_row, 5].Value = dtDetail.Rows[l_row - 1]["price"];
exSheet.Cells[l_first_row + l_row, 6].Value = dtDetail.Rows[l_row - 1]["amount"];
exSheet.Cells[l_first_row + l_row, 7].Value = dtDetail.Rows[l_row - 1]["discount_amt"];
exSheet.Cells[l_first_row + l_row, 8].Value = dtDetail.Rows[l_row - 1]["total_due"];
if (dtDetail.Rows[l_row - 1]["total_due"].ToString() != "")
{
l_total_amt = l_total_amt + double.Parse(dtDetail.Rows[l_row - 1]["total_due"].ToString());
}
if (l_row < dtDetail.Rows.Count)
{
if (dtDetail.Rows[l_row - 1]["invoice_id"].ToString() == dtDetail.Rows[l_row]["invoice_id"].ToString())
{
exSheet.Range[l_first_row + l_row, 1, l_first_row + l_row + 1, 1].Merge();
exSheet.Range[l_first_row + l_row, 2, l_first_row + l_row + 1, 2].Merge();
exSheet.Range[l_first_row + l_row, 8, l_first_row + l_row + 1, 8].Merge();
//l_long_term=l_long_term+1;
}
}
}
//exSheet.Cells[6 + l_cnt, 3].Value=l_total_qty;
//exSheet.Cells[6 + l_cnt, 5].Value=l_total_amt;
// 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);
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 p_tco_org_pk, p_wg, p_from, p_to, p_txtID, p_work_mon, p_lang;
p_tco_org_pk = Request["p_tco_org_pk"].ToString();
p_wg = Request["p_wg"].ToString();
p_from = Request["p_from_dt"].ToString();
p_to = Request["p_to_dt"].ToString();
p_txtID = Request["p_emp_search"].ToString();
p_work_mon = Request["p_month"].ToString();
p_lang = Request["p_lang"].ToString();
string TemplateFile;
if (p_lang == "ENG")
{
TemplateFile = "rpt_hrti00400_2_eng.xls";
}
else
{
TemplateFile = "rpt_hrti00400_2_vie.xls";
}
string TempFile = "../../../../system/temp/rpt_hrti00400_2_" + 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_tco_org_pk + "','" + p_wg + "','" + p_work_mon + "','" + p_from + "','" + p_to + "','" + p_txtID + "'";
DataTable dt_detail = CtlLib.TableReadOpenCursor("ST_HR_RPT_HRTI00400_2", para);
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 + "' and '" + p_to + "' " +
"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);
if (dt_detail.Rows.Count == 0)
{
Response.Write("Không có dữ liệu!");
Response.End();
}
int row_loop = 5;
int l_col = dt_detail.Columns.Count - 31;
int row_emp = dt_detail.Rows.Count;
int row_day = dt_day.Rows.Count;
int count = 0;
int s_row = 4;
int pos = 4;
int s_col = 8;
int temp = 0;
int l_col_pass = 6;
for (int d = 0; d < dt_day.Rows.Count; d++)
{
exSheet.Cells[4, s_col + d].Value = dt_day.Rows[d][0].ToString();
}
for (int l_addrow = 1; l_addrow < dt_detail.Rows.Count; l_addrow++)
{
exSheet.Range[row_loop + l_addrow, 1].Rows.EntireRow.Insert(); //insert row new of sheet
}
if (p_from != "" && p_to != "")
{
if (p_lang == "ENG")
{
exSheet.Cells["A2"].Value = "From Date " + dt_detail.Rows[0][0].ToString() + " -----" + " To Date " + dt_detail.Rows[0][1].ToString();
}
else
{
exSheet.Cells["A2"].Value = "Từ ngày " + dt_detail.Rows[0][0].ToString() + " -----" + " Đến ngày " + dt_detail.Rows[0][1].ToString();
}
}
// 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, 1].Value = count;
// do du lieu vao cac dong
for (int col = 2; col < l_col; col++)
{
if (IsNumeric(dt_detail.Rows[l_row][col].ToString()) && col != 2)
{
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();
}
}
pos++;
for (int col = s_col; col < s_col + 31; col++)
{
if (dt_detail.Rows[l_row][col + 31].ToString() == "SUN")
{
exSheet.Range[row_loop + l_row, col].Interior.Color = Color.SkyBlue;
}
if (dt_detail.Rows[l_row][col + 31].ToString() == "HOL")
{
exSheet.Range[row_loop + l_row, col].Interior.Color = Color.Pink;
if (dt_detail.Rows[l_row][col].ToString() == "")
{
exSheet.Cells[row_loop + l_row, col].Value = "PH";
}
}
}
}
}
for (int row = 0; row < row_day; row++)
{
temp = 31 - row_day;
switch (temp)
{
case 3:
exSheet.Range["AJ1"].Columns.Hidden = true;
exSheet.Range["AK1"].Columns.Hidden = true;
exSheet.Range["AL1"].Columns.Hidden = true;
break;
case 2:
exSheet.Range["AK1"].Columns.Hidden = true;
exSheet.Range["AL1"].Columns.Hidden = true;
break;
case 1:
exSheet.Range["AL1"].Columns.Hidden = true;
break;
}
switch (dt_day.Rows[row][1].ToString())
{
case "SUN":
exSheet.Range[s_row, row + s_col].Interior.Color = Color.Green;
break;
case "HOL":
exSheet.Range[s_row, row + s_col, s_row, row + s_col].Interior.Color = Color.Pink;
break;
}
}
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_bisc00150_grand.xls";
string TempFile = "../../../../system/temp/rpt_bisc00150_grand_" + 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_item_group = Request.QueryString["p_item_grp"];
string p_item = Request.QueryString["p_item"];
string p_wh_type = Request.QueryString["p_wh_type"];
string p_wh = Request.QueryString["p_wh"];
string p_wh_name = Request.QueryString["p_wh_name"];
string p_use_yn = Request.QueryString["p_use_yn"];
string p_rad_type = Request.QueryString["p_rad_type"];
string p_dt_from = Request.QueryString["p_dt_from"];
string para = "'" + p_lang +
"','" + p_item_group +
"','" + p_item +
"','" + p_wh_type +
"','" + p_wh +
"','" + p_use_yn +
"','" + p_rad_type +
"','" + p_dt_from + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00150_2", "'" + Session["User_ID"].ToString() + "'");
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00150_1", para);
//Set Data 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_dt_from, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
exSheet.Cells["A6"].Value = String.Format("W/H name: {0}", p_wh_name);
exSheet.Cells["G6"].Value = String.Format("Date: {0}", System.DateTime.ParseExact(p_dt_from, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy").ToString());
//insert row new of sheet
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A9"].Rows.EntireRow.Insert();
}
//sum variable
double sum_stock_qty = 0;
double sum_qty1 = 0;
double sum_qty2 = 0;
double sum_qty3 = 0;
double sum_qty4 = 0;
double sum_qty5 = 0;
double sum_stock_qty_sub = 0;
double sum_qty1_sub = 0;
double sum_qty2_sub = 0;
double sum_qty3_sub = 0;
double sum_qty4_sub = 0;
double sum_qty5_sub = 0;
double tmp_stock_qty = 0;
double tmp_qty1 = 0;
double tmp_qty2 = 0;
double tmp_qty3 = 0;
double tmp_qty4 = 0;
double tmp_qty5 = 0;
//Set data
int l_pos = 7;
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
l_pos += 1;
if (i > 0 && dtDetail.Rows[i]["tlg_it_itemgrp_pk"].ToString() != dtDetail.Rows[i - 1]["tlg_it_itemgrp_pk"].ToString())
{
exSheet.Cells[l_pos, 4].Value = String.Format("{0:0,0}", sum_stock_qty_sub);
exSheet.Cells[l_pos, 7].Value = String.Format("{0:0,0}", sum_qty1_sub);
exSheet.Cells[l_pos, 10].Value = String.Format("{0:0,0}", sum_qty2_sub);
exSheet.Cells[l_pos, 13].Value = String.Format("{0:0,0}", sum_qty3_sub);
exSheet.Cells[l_pos, 16].Value = String.Format("{0:0,0}", sum_qty4_sub);
exSheet.Cells[l_pos, 19].Value = String.Format("{0:0,0}", sum_qty5_sub);
FormatTotalRange(exSheet, l_pos, 1, l_pos, 20);
exSheet.Cells[l_pos, 1, l_pos, 3].Merge();
l_pos += 1;
sum_stock_qty_sub = 0;
sum_qty1_sub = 0;
sum_qty2_sub = 0;
sum_qty3_sub = 0;
sum_qty4_sub = 0;
sum_qty5_sub = 0;
}
exSheet.Cells[l_pos, 1].Value = dtDetail.Rows[i]["grp_nm"];
exSheet.Cells[l_pos, 2].Value = dtDetail.Rows[i]["item_code"];
exSheet.Cells[l_pos, 2].Font.Bold = true;
exSheet.Cells[l_pos, 3].Value = dtDetail.Rows[i]["item_name"];
exSheet.Cells[l_pos, 4].Value = dtDetail.Rows[i]["stock_qty"];
exSheet.Cells[l_pos, 4].Font.Bold = true;
exSheet.Cells[l_pos, 5].Value = dtDetail.Rows[i]["uom"];
exSheet.Cells[l_pos, 6].Value = dtDetail.Rows[i]["trans_rate_1"];
exSheet.Cells[l_pos, 7].Value = dtDetail.Rows[i]["qty1"];
exSheet.Cells[l_pos, 7].Font.Bold = true;
exSheet.Cells[l_pos, 8].Value = dtDetail.Rows[i]["trans_uom_1"];
exSheet.Cells[l_pos, 9].Value = dtDetail.Rows[i]["trans_rate_2"];
exSheet.Cells[l_pos, 10].Value = dtDetail.Rows[i]["qty2"];
exSheet.Cells[l_pos, 10].Font.Bold = true;
exSheet.Cells[l_pos, 11].Value = dtDetail.Rows[i]["trans_uom_2"];
exSheet.Cells[l_pos, 12].Value = dtDetail.Rows[i]["trans_rate_3"];
exSheet.Cells[l_pos, 13].Value = dtDetail.Rows[i]["qty3"];
exSheet.Cells[l_pos, 13].Font.Bold = true;
exSheet.Cells[l_pos, 14].Value = dtDetail.Rows[i]["trans_uom_3"];
exSheet.Cells[l_pos, 15].Value = dtDetail.Rows[i]["trans_rate_4"];
exSheet.Cells[l_pos, 16].Value = dtDetail.Rows[i]["qty4"];
exSheet.Cells[l_pos, 16].Font.Bold = true;
exSheet.Cells[l_pos, 17].Value = dtDetail.Rows[i]["trans_uom_4"];
exSheet.Cells[l_pos, 18].Value = dtDetail.Rows[i]["trans_rate_5"];
exSheet.Cells[l_pos, 19].Value = dtDetail.Rows[i]["qty5"];
exSheet.Cells[l_pos, 19].Font.Bold = true;
exSheet.Cells[l_pos, 20].Value = dtDetail.Rows[i]["trans_uom_5"];
FormatNormalRange(exSheet, l_pos, 1, l_pos, 20);
if (double.TryParse(dtDetail.Rows[i]["stock_qty"].ToString(), out tmp_stock_qty))
{
sum_stock_qty_sub += tmp_stock_qty;
sum_stock_qty += tmp_stock_qty;
}
if (double.TryParse(dtDetail.Rows[i]["qty1"].ToString(), out tmp_qty1))
{
sum_qty1_sub += tmp_qty1;
sum_qty1 += tmp_qty1;
}
if (double.TryParse(dtDetail.Rows[i]["qty2"].ToString(), out tmp_qty2))
{
sum_qty2_sub += tmp_qty2;
sum_qty2 += tmp_qty2;
}
if (double.TryParse(dtDetail.Rows[i]["qty3"].ToString(), out tmp_qty3))
{
sum_qty3_sub += tmp_qty3;
sum_qty3 += tmp_qty3;
}
if (double.TryParse(dtDetail.Rows[i]["qty4"].ToString(), out tmp_qty4))
{
sum_qty4_sub += tmp_qty4;
sum_qty4 += tmp_qty4;
}
if (double.TryParse(dtDetail.Rows[i]["qty5"].ToString(), out tmp_qty5))
{
sum_qty5_sub += tmp_qty5;
sum_qty5 += tmp_qty5;
}
}
//The final sub total row
l_pos += 1;
exSheet.Cells[l_pos, 4].Value = String.Format("{0:0,0}", sum_stock_qty_sub);
exSheet.Cells[l_pos, 7].Value = String.Format("{0:0,0}", sum_qty1_sub);
exSheet.Cells[l_pos, 10].Value = String.Format("{0:0,0}", sum_qty2_sub);
exSheet.Cells[l_pos, 13].Value = String.Format("{0:0,0}", sum_qty3_sub);
exSheet.Cells[l_pos, 16].Value = String.Format("{0:0,0}", sum_qty4_sub);
exSheet.Cells[l_pos, 19].Value = String.Format("{0:0,0}", sum_qty5_sub);
FormatTotalRange(exSheet, l_pos, 1, l_pos, 20);
exSheet.Cells[l_pos, 1, l_pos, 3].Merge();
//The total row
l_pos += 1;
exSheet.Cells[l_pos, 4].Value = String.Format("{0:0,0}", sum_stock_qty);
exSheet.Cells[l_pos, 7].Value = String.Format("{0:0,0}", sum_qty1);
exSheet.Cells[l_pos, 10].Value = String.Format("{0:0,0}", sum_qty2);
exSheet.Cells[l_pos, 13].Value = String.Format("{0:0,0}", sum_qty3);
exSheet.Cells[l_pos, 16].Value = String.Format("{0:0,0}", sum_qty4);
exSheet.Cells[l_pos, 19].Value = String.Format("{0:0,0}", sum_qty5);
FormatTotalRange(exSheet, l_pos, 1, l_pos, 20);
exSheet.Cells[l_pos, 1, l_pos, 3].Merge();
exSheet.Cells[l_pos, 1, l_pos, 3].HorizontalAlignment = XlHAlign.xlHAlignCenter;
exSheet.Cells[l_pos, 1, l_pos, 3].Value = "GRAND TOTAL";
exSheet.Cells[l_pos, 1, l_pos, 3].Font.Bold = false;
// end loop detail percent
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 l_repayment_pk = Request["p_tht_repayment_pk"];
string p_employee_pk = Request["p_employee_pk"];
string TemplateFile = "rpt_60250170_Guest_Invoice.xls";
string TempFile = "../../../../system/temp/rpt_60250170_Guest_Invoice_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string para = "";
DataTable dt;
para = "'" + l_repayment_pk + "'";
dt = CtlLib.TableReadOpenCursor("ht_rpt_60250170_repayment", para);
string SQL = "";
SQL = "select max(full_name) from thr_employee where pk='" + p_employee_pk + "'";
DataTable dtEmp = new DataTable();
dtEmp = CtlLib.TableReadOpen(SQL);
exSheet.Cells[33, 5].Value = dtEmp.Rows[0][0].ToString(); //full_name login sofware
//-----header---
//---end--header---
//--set-----DataBind--in---Master---
if (dt.Rows.Count == 0)
{
exSheet.Cells[8, 4].Value = "There is not data!";
}
if (dt.Rows.Count > 0)
{
string l_status_title = "GUEST INVOICE";
exSheet.Cells[7, 3].Value = l_status_title;
//exSheet.Cells[ 8, 1].Value = dt.Rows[0]["confirm_msg"].ToString();
exSheet.Cells[9, 3].Value = dt.Rows[0]["guest_name"].ToString();
exSheet.Cells[9, 6].Value = dt.Rows[0]["print_time"].ToString();
exSheet.Cells[10, 3].Value = dt.Rows[0]["partner_name"].ToString();
exSheet.Cells[10, 6].Value = dt.Rows[0]["room_no"].ToString();
//exSheet.Cells[10, 7].Value = l_folio_type;
exSheet.Cells[11, 3].Value = dt.Rows[0]["customer_address"].ToString();
exSheet.Cells[11, 6].Value = dt.Rows[0]["invoice_no"].ToString();
exSheet.Cells[12, 3].Value = dt.Rows[0]["tax_code"].ToString();
exSheet.Cells[12, 6].Value = dt.Rows[0]["arrival_dt"].ToString();
//exSheet.Cells[13, 3].Value = dt.Rows[0]["guest_name"].ToString(); //total guest
exSheet.Cells[13, 6].Value = dt.Rows[0]["checkout_dt"].ToString();
exSheet.Cells[14, 4].Value = dt.Rows[0]["paymethod"].ToString();
if (dt.Rows[0]["total_due_vnd_pp"].ToString() != "")
{
exSheet.Cells[17, 5].Value = double.Parse(dt.Rows[0]["total_due_vnd_pp"].ToString()); //
}
if (dt.Rows[0]["total_due_usd_pp"].ToString() != "")
{
exSheet.Cells[17, 7].Value = float.Parse(dt.Rows[0]["total_due_usd_pp"].ToString());
}
//exSheet.Cells[18, 5].Value = dt.Rows[0]["paymethod"].ToString();TTDB VND
//exSheet.Cells[18, 7].Value = dt.Rows[0]["paymethod"].ToString();TTDB USD
if (dt.Rows[0]["svc_amt"].ToString() != "")
{
exSheet.Cells[19, 5].Value = double.Parse(dt.Rows[0]["svc_amt"].ToString());
}
if (dt.Rows[0]["svc_amt_usd"].ToString() != "")
{
exSheet.Cells[19, 7].Value = float.Parse(dt.Rows[0]["svc_amt_usd"].ToString());
}
if (dt.Rows[0]["total_vnd"].ToString() != "")
{
exSheet.Cells[20, 5].Value = double.Parse(dt.Rows[0]["total_vnd"].ToString());
}
if (dt.Rows[0]["total_usd"].ToString() != "")
{
exSheet.Cells[20, 7].Value = float.Parse(dt.Rows[0]["total_usd"].ToString());
}
if (dt.Rows[0]["vat_amt"].ToString() != "")
{
exSheet.Cells[21, 5].Value = double.Parse(dt.Rows[0]["vat_amt"].ToString());
}
if (dt.Rows[0]["vat_amt_usd"].ToString() != "")
{
exSheet.Cells[21, 7].Value = float.Parse(dt.Rows[0]["vat_amt_usd"].ToString());
}
//exSheet.Cells[22, 5].Value = int.Parse(dt.Rows[0]["total_discount_amt"].ToString());
//exSheet.Cells[22, 7].Value = float.Parse(dt.Rows[0]["total_discount_amt_usd"].ToString());
//exSheet.Cells[22, 5].Value = int.Parse(dt.Rows[0]["advance_amt"].ToString());
//exSheet.Cells[22, 7].Value = float.Parse(dt.Rows[0]["advance_amt_usd"].ToString());
if (dt.Rows[0]["total_due_end_vnd"].ToString() != "")
{
exSheet.Cells[23, 5].Value = double.Parse(dt.Rows[0]["total_due_end_vnd"].ToString());
}
if (dt.Rows[0]["total_due_usd"].ToString() != "")
{
exSheet.Cells[23, 7].Value = float.Parse(dt.Rows[0]["total_due_usd"].ToString());
}
if (dt.Rows[0]["cash_vnd"].ToString() != "")
{
exSheet.Cells[24, 3].Value = double.Parse(dt.Rows[0]["cash_vnd"].ToString());
}
if (dt.Rows[0]["cash_usd"].ToString() != "")
{
exSheet.Cells[24, 5].Value = float.Parse(dt.Rows[0]["cash_usd"].ToString());
}
if (dt.Rows[0]["credit_vnd"].ToString() != "")
{
exSheet.Cells[25, 3].Value = double.Parse(dt.Rows[0]["credit_vnd"].ToString());
}
if (dt.Rows[0]["credit_usd"].ToString() != "")
{
exSheet.Cells[25, 5].Value = float.Parse(dt.Rows[0]["credit_usd"].ToString());
}
if (double.Parse(dt.Rows[0]["cash_vnd"].ToString()) == 0 && float.Parse(dt.Rows[0]["cash_usd"].ToString()) == 0)
{
range = exSheet.Range["A24"];
range.Rows.Hidden = true;
}
if (double.Parse(dt.Rows[0]["credit_vnd"].ToString()) == 0 && float.Parse(dt.Rows[0]["credit_usd"].ToString()) == 0)
{
range = exSheet.Range["A25"];
range.Rows.Hidden = true;
}
string inword_amt, inword_amt_usd;
inword_amt = CommondLib.Num2VNText(dt.Rows[0]["total_due_end_vnd"].ToString(), "VND");
inword_amt_usd = CommondLib.Num2EngText(dt.Rows[0]["total_due_usd"].ToString(), "USD");
exSheet.Cells[26, 3].Value = inword_amt.ToString();
exSheet.Cells[27, 3].Value = inword_amt_usd.ToString();
}
/*if (l_svc < 1)//not svc then hide row svc
* {
* range = exSheet.Range["A15"];
* // hide row A15
* range.Rows.Hidden = true;
* }*/
//----------------------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A16"].Rows.EntireRow.Insert();//insert row new of sheet
}
//set date title of year
//exSheet.Cells[2, 1].Value = "Income Statement Year" + " " + l_year;
//set data to detail.
// loop detail not percent
int l_cnt = dt.Rows.Count;
if (dt.Rows.Count > 0)
{
for (int l_row = 1; l_row <= dt.Rows.Count; l_row++)
{
for (int col = 0; col <= 6; col++)
{
if (col == 1 || col == 2 || col == 3)
{
exSheet.Cells[15 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString();
exSheet.Cells[l_row + 15, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
exSheet.Range[16, col + 1, l_row + 15, 7].Font.Bold = false;
}
else
{
if (col == 6)
{
if (dt.Rows[l_row - 1][col].ToString() != "")
{
exSheet.Cells[15 + l_row, col + 1].Value = float.Parse(dt.Rows[l_row - 1][col].ToString());
exSheet.Cells[15, col + 1, l_row + 15, col + 1].NumberFormat = "#,##0.00";
exSheet.Cells[l_row + 15, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight;
}
}
else
{
if (dt.Rows[l_row - 1][col].ToString() != "")
{
exSheet.Cells[15 + l_row, col + 1].Value = long.Parse(dt.Rows[l_row - 1][col].ToString());
exSheet.Cells[15, col + 1, l_row + 15, col + 1].NumberFormat = "#,##0";
exSheet.Cells[l_row + 15, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight;
}
}
exSheet.Range[16, col + 1, l_row + 15, 7].Font.Bold = false;
}
//exSheet.Cells[l_row + 12, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
}
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
range.Rows.Hidden = true;
// font bold header
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
//string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser("sale");
string TemplateFile = "rpt_dscd00070_KB_4.xls";
string TempFile = "../../../../system/temp/rpt_dscd00070_KB_4_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string master_pk = "'" + Request.QueryString["master_pk"] + "'";
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00070_KB_1", master_pk);
master_pk = "'" + Request.QueryString["master_pk"] + "','" + Session["User_ID"].ToString() + "'";
DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00070_KB", master_pk);
// MASTER
if (dtMaster.Rows.Count > 0)
{
StringBuilder sb = new StringBuilder();
sb.Append("Số: ");
sb.Append(dtMaster.Rows[0]["slip_no"].ToString() + " / ");
sb.Append("Ngày " + dtMaster.Rows[0]["out_dd"].ToString() + " ");
sb.Append("Tháng " + dtMaster.Rows[0]["out_mm"].ToString() + " ");
sb.Append("Năm " + dtMaster.Rows[0]["out_yy"].ToString());
exSheet.Cells["C2"].Value = sb.ToString();
exSheet.Cells["C4"].Value = dtMaster.Rows[0]["partner_name"].ToString();
exSheet.Cells["C5"].Value = dtMaster.Rows[0]["addr1"].ToString();
exSheet.Cells["C6"].Value = dtMaster.Rows[0]["tax_code"].ToString();
exSheet.Cells["C7"].Value = dtMaster.Rows[0]["phone_no"].ToString();
exSheet.Cells["F7"].Value = dtMaster.Rows[0]["fax_no"].ToString();
exSheet.Cells["C8"].Value = dtMaster.Rows[0]["loc_addr"].ToString();
}
// END MASTER
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A13"].Rows.EntireRow.Insert();//insert row new of sheet
}
// DETAIL
int pos = 12;
for (int l_row = 0; l_row < dtDetail.Rows.Count; l_row++)
{
exSheet.Cells[pos + l_row, 1].Value = l_row + 1;
exSheet.Cells[pos + l_row, 2].Value = dtDetail.Rows[l_row]["item"];
exSheet.Cells[pos + l_row, 2, pos + l_row, 4].Merge();
exSheet.Cells[pos + l_row, 5].Value = dtDetail.Rows[l_row]["out_qty"];
exSheet.Cells[pos + l_row, 6].Value = dtDetail.Rows[l_row]["unit_price"];
exSheet.Cells[pos + l_row, 7].Value = "=E" + (pos + l_row) + "*F" + (pos + l_row);
exSheet.Cells[pos + l_row, 8].Value = dtDetail.Rows[l_row]["description"];
}
// END DETAIL
exSheet.Cells["F" + (dtDetail.Rows.Count + 12)].Value = "=SUM(G12:G" + (dtDetail.Rows.Count + 11) + ")";
exSheet.Cells["F" + (dtDetail.Rows.Count + 13)].Value = "=F" + (dtDetail.Rows.Count + 12) + "*" + dtDetail.Rows[0]["tax_rate"] + "%";
exSheet.Cells["F" + (dtDetail.Rows.Count + 14)].Value = "=F" + (dtDetail.Rows.Count + 12) + "+" + "F" + (dtDetail.Rows.Count + 13);
exSheet.Cells["H" + (dtDetail.Rows.Count + 14)].Value = "(" + dtMaster.Rows[0]["tr_ccy"].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
/*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_year = Request["p_year"];
string TemplateFile = "rpt_60300010_nationality_mkt_segment_yearly.xls";
string TempFile = "../../../../system/temp/rpt_60300010_nationality_mkt_segment_yearly_" + 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_year + "'";
dt = CtlLib.TableReadOpenCursor("rpt_60300010_mkt_segment_year", para);
//-----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[3, 1].Value = dt.Rows[0]["date_para"].ToString();
* exSheet.Cells[3, 9].Value = dt.Rows[0]["print_time"].ToString();
* exSheet.Cells[4, 11].Value = dt.Rows[0]["date_header"].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
*
* }*/
for (int l_addrow_1 = 0; l_addrow_1 < dt.Rows.Count - 1; l_addrow_1++) //add row show with data of table
{
exSheet.Range[5 + l_addrow_1 + 1, 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_cnt = dt.Rows.Count;
for (int l_row = 0; l_row < dt.Rows.Count; l_row++)
{
exSheet.Cells[3, 28].Value = "Total " + p_year;
exSheet.Cells[5 + l_row, 2].Value = dt.Rows[l_row]["nation"];
exSheet.Cells[5 + l_row, 3].Value = dt.Rows[l_row]["customer_type"];
exSheet.Cells[5 + l_row, 4].Value = dt.Rows[l_row]["mm1"];
exSheet.Cells[5 + l_row, 5].Value = dt.Rows[l_row]["rate_mm1"];
exSheet.Cells[5 + l_row, 6].Value = dt.Rows[l_row]["mm2"];
exSheet.Cells[5 + l_row, 7].Value = dt.Rows[l_row]["rate_mm2"];
exSheet.Cells[5 + l_row, 8].Value = dt.Rows[l_row]["mm3"];
exSheet.Cells[5 + l_row, 9].Value = dt.Rows[l_row]["rate_mm3"];
exSheet.Cells[5 + l_row, 10].Value = dt.Rows[l_row]["mm4"];
exSheet.Cells[5 + l_row, 11].Value = dt.Rows[l_row]["rate_mm4"];
exSheet.Cells[5 + l_row, 12].Value = dt.Rows[l_row]["mm5"];
exSheet.Cells[5 + l_row, 13].Value = dt.Rows[l_row]["rate_mm5"];
exSheet.Cells[5 + l_row, 14].Value = dt.Rows[l_row]["mm6"];
exSheet.Cells[5 + l_row, 15].Value = dt.Rows[l_row]["rate_mm6"];
exSheet.Cells[5 + l_row, 16].Value = dt.Rows[l_row]["mm7"];
exSheet.Cells[5 + l_row, 17].Value = dt.Rows[l_row]["rate_mm7"];
exSheet.Cells[5 + l_row, 18].Value = dt.Rows[l_row]["mm8"];
exSheet.Cells[5 + l_row, 19].Value = dt.Rows[l_row]["rate_mm8"];
exSheet.Cells[5 + l_row, 20].Value = dt.Rows[l_row]["mm9"];
exSheet.Cells[5 + l_row, 21].Value = dt.Rows[l_row]["rate_mm9"];
exSheet.Cells[5 + l_row, 22].Value = dt.Rows[l_row]["mm10"];
exSheet.Cells[5 + l_row, 23].Value = dt.Rows[l_row]["rate_mm10"];
exSheet.Cells[5 + l_row, 24].Value = dt.Rows[l_row]["mm11"];
exSheet.Cells[5 + l_row, 25].Value = dt.Rows[l_row]["rate_mm11"];
exSheet.Cells[5 + l_row, 26].Value = dt.Rows[l_row]["mm12"];
exSheet.Cells[5 + l_row, 27].Value = dt.Rows[l_row]["rate_mm12"];
exSheet.Cells[5 + l_row, 28].Value = dt.Rows[l_row]["total_year"];
exSheet.Cells[5 + l_row, 29].Value = dt.Rows[l_row]["rate_year"];
Color color2 = ColorTranslator.FromHtml("0xFFFFCC");
if (dt.Rows[l_row]["customer_type"].ToString() == "" && dt.Rows[l_row]["mm1"].ToString() != "")
{
exSheet.Cells[5 + l_row, 3, 5 + l_row, 29].Interior.Color = color2; //Color.Green;
}
if (dt.Rows[l_row]["nation"].ToString() != "" && dt.Rows[l_row]["customer_type"].ToString() == "" && dt.Rows[l_row]["mm1"].ToString() != "")
{
exSheet.Cells[5 + l_row, 3].Value = "Sub Total"; //SUBTOTAL;
exSheet.Cells[5 + l_row, 29].Value = "100%";
}
if (dt.Rows[l_row]["nation"].ToString() == "" && dt.Rows[l_row]["customer_type"].ToString() == "" && dt.Rows[l_row]["mm1"].ToString() != "")
{
exSheet.Cells[5 + l_row, 3].Value = "Total"; //TOTAL;
exSheet.Cells[5 + l_row, 29].Value = "100%";
exSheet.Cells[5 + l_row, 5].Value = "100%";
exSheet.Cells[5 + l_row, 7].Value = "100%";
exSheet.Cells[5 + l_row, 9].Value = "100%";
exSheet.Cells[5 + l_row, 11].Value = "100%";
exSheet.Cells[5 + l_row, 13].Value = "100%";
exSheet.Cells[5 + l_row, 15].Value = "100%";
exSheet.Cells[5 + l_row, 17].Value = "100%";
exSheet.Cells[5 + l_row, 19].Value = "100%";
exSheet.Cells[5 + l_row, 21].Value = "100%";
exSheet.Cells[5 + l_row, 23].Value = "100%";
exSheet.Cells[5 + l_row, 25].Value = "100%";
exSheet.Cells[5 + l_row, 27].Value = "100%";
}
if (l_row > 1 && l_row < dt.Rows.Count)
{
if (dt.Rows[l_row - 1]["nation"].ToString() == dt.Rows[l_row]["nation"].ToString())
{
exSheet.Range[l_row + 4, 2, l_row + 5, 2].Merge();
//l_long_term=l_long_term+1;
}
}
if (l_row > 0 && l_row == 1)
{
if (dt.Rows[l_row - 1]["nation"].ToString() == dt.Rows[l_row]["nation"].ToString())
{
exSheet.Range[l_row + 4, 2, l_row + 5, 2].Merge();
//l_long_term=l_long_term+1;
}
}
}
// 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("sale");
string l_date = Request["p_date"];
string l_wh = Request["p_wh"];
string l_item = Request["p_item"];
string l_parameter = "'" + l_date + "','" + l_wh + "','" + l_item + "'";
string TemplateFile = "rpt_bisc00090.xls";
string TempFile = "../../../../system/temp/rpt_bisc00090_" + 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
//-----header---
DataTable dtHeader = CtlLib.TableReadOpenCursor("LG_SEL_BISC00090_HEADER", l_parameter);
int count = 5;
for (int i = 0; i < dtHeader.Rows.Count; i++)
{
exSheet.Cells[4, count].Value = dtHeader.Rows[i][0].ToString();
count++;
}
//---end--header---
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00090", l_parameter);
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A6"].Rows.EntireRow.Insert();//insert row new of sheet
}
// loop detail not percent
for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++)
{
for (int col = 0; col <= 15; col++)
{
if (col == 0 || col == 1 || col == 2 || col == 3)
{
exSheet.Cells[4 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1][col].ToString();
}
else
{
if (col >= 4 && col <= 15)
{
if (!string.IsNullOrEmpty(dtDetail.Rows[l_row - 1][col].ToString()))
{
exSheet.Cells[4 + l_row, col + 1].Value = float.Parse(dtDetail.Rows[l_row - 1][col].ToString());
}
else
{
exSheet.Cells[4 + l_row, col + 1].Value = string.Empty;
}
}
}
}
}
// 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);
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_dt_from = Request["p_dtfrm"];
string l_dt_to = Request["p_dtto"];
string TemplateFile = "rpt_60300010_complimentary_BK.xls";
string TempFile = "../../../../system/temp/rpt_60300010_complimentary_BK_" + 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_company_stt_bk", para);
//-----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[3, 1].Value = dt.Rows[0]["date_para"].ToString();
exSheet.Cells[3, 10].Value = dt.Rows[0]["print_time"].ToString();
exSheet.Cells[4, 12].Value = dt.Rows[0]["date_header"].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;
if (dt.Rows.Count > 0)
{
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 == 14)
{
exSheet.Cells[5 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString();
exSheet.Cells[l_row + 5, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
else
{
if (col == 4 || col == 7 || col == 10 || col == 13)
{
exSheet.Cells[5 + l_row, col + 1].Value = float.Parse(dt.Rows[l_row - 1][col].ToString());
exSheet.Cells[5, col + 1, l_row + 5, col + 1].NumberFormat = "#,##0.00";
exSheet.Cells[l_row + 5, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight;
}
else
{
exSheet.Cells[5 + l_row, col + 1].Value = double.Parse(dt.Rows[l_row - 1][col].ToString());
exSheet.Cells[5, col + 1, l_row + 5, col + 1].NumberFormat = "#,##0";
exSheet.Cells[l_row + 5, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight;
}
}
}
}
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
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("hr");
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string p_dept, p_group, p_status, p_type, p_pay_type, p_emp_id, p_work_mon, p_m_type, p_sal_period, p_emp_type;
p_dept = Request["l_dept"].ToString();
p_group = Request["l_group"].ToString();
p_status = Request["l_status"].ToString();
p_type = Request["l_type"].ToString();
p_pay_type = Request["l_pay_type"].ToString();
p_emp_id = Request["l_emp_id"].ToString();
p_work_mon = Request["l_work_mon"].ToString();
p_sal_period = Request["l_sal_period"].ToString();
p_emp_type = Request["l_Emp_Type"].ToString();
p_m_type = Request.QueryString["l_m_type"].ToString();
//p_from_leftdate = Request["l_from_leftdate"].ToString();
//p_to_leftdate = Request["l_to_leftdate"].ToString();
string TemplateFile = "rpt_hrpm00700_0.xls";
string TempFile = "../../../../system/temp/rpt_hrpm00700_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;
if (p_type == "ALL")
{
p_type = "= NVL (a.net_amt, 0)";
}
string SQL
= "SELECT A.TCO_DEPT_PK dept_pk,'', " +
" a.DEPT_NM dept_nm, " +
" A.EMP_ID emp_id_2, " +
" A.FULL_NAME full_nm_3, " +
" TO_CHAR (TO_DATE (A.JOIN_DT, 'yyyymmdd'), 'dd/mm/yyyy') join_dt_4, " +
" TO_CHAR (TO_DATE (A.left_DT, 'yyyymmdd'), 'dd/mm/yyyy') left_dt_5, " +
" A.POS_NM pos_nm_6, " +
" (SELECT code_nm " +
" FROM vhr_hr_code " +
" WHERE id = 'HR0117' AND code = a.times) " +
" times_74, " +
" (CASE " +
" WHEN NVL (a.CONFIRM_DT, a.from_dt) > a.from_dt " +
" THEN " +
" ROUND (NVL (A.SALARY_LEVEL1, 0), 10) " +
" ELSE " +
" 0 " +
" END) " +
" sal_level1_7, " +
" (CASE " +
" WHEN NVL (a.CONFIRM_DT, a.to_dt) > a.to_DT THEN 0 " +
" ELSE ROUND (NVL (A.SALARY_LEVEL2, 0), 10) " +
" END) " +
" sal_level2_8, " +
" ROUND (NVL (A.ALLOW_AMT1, 0), 10) allow_amt1_9, " +
" ROUND (NVL (A.ALLOW_AMT2, 0), 10) allow_amt2_10, " +
" ROUND (NVL (A.ALLOW_AMT3, 0), 10) allow_amt3_11, " +
" ROUND (NVL (A.ALLOW_AMT4, 0), 10) allow_amt4_12, " +
" ROUND (NVL (A.ALLOW_AMT5, 0), 10) allow_amt5_13, " +
" ROUND (NVL (A.ALLOW_AMT6, 0), 10) allow_amt6_14, " +
" ROUND (NVL (A.ALLOW_AMT7, 0), 10) allow_amt7_15, " +
" ROUND (NVL (A.ALLOW_AMT8, 0), 10) allow_amt8_16, " +
" ROUND (NVL (A.ALLOW_AMT9, 0), 10) allow_amt1_9, " +
" ROUND (NVL (A.ALLOW_AMT10, 0), 10) allow_amt1_9, " +
" ROUND (NVL (A.ALLOW_AMT11, 0), 10) allow_amt1_9, " +
" ROUND (NVL (A.ALLOW_AMT12, 0), 10) allow_amt1_9, " +
" ROUND (NVL (A.ALLOW_AMT1, 0) + NVL (A.ALLOW_AMT2, 0) + NVL (A.ALLOW_AMT3, 0) + NVL (A.ALLOW_AMT4, 0)" +
" + NVL (A.ALLOW_AMT5, 0) + NVL (A.ALLOW_AMT6, 0) + NVL (A.ALLOW_AMT7, 0) + NVL (A.ALLOW_AMT8, 0)" +
" + NVL (A.ALLOW_AMT9, 0) + NVL (A.ALLOW_AMT10, 0) + NVL (A.ALLOW_AMT11, 0) + NVL (A.ALLOW_AMT12, 0), 0) total_allow, " +
" ROUND (NVL (A.ALLOW_K1_AMT, 0), 10) allow_k1_amt_38, " +
" ROUND (NVL (A.ALLOW_K2_AMT, 0), 10) allow_k2_amt_39, " +
" ROUND (NVL (A.ALLOW_K3_AMT, 0), 10) allow_k3_amt_40, " +
" ROUND (NVL (A.ALLOW_K4_AMT, 0), 10) allow_k4_amt_41, " +
" ROUND (NVL (A.ALLOW_K5_AMT, 0), 10) allow_k5_amt_42, " +
" ROUND (NVL (A.ALLOW_K6_AMT, 0), 10) allow_k6_amt_43, " +
" ROUND (NVL (A.ALLOW_K7_AMT, 0), 10) allow_k7_amt_44, " +
" ROUND (NVL (A.ALLOW_K8_AMT, 0), 10) allow_k8_amt_45, " +
" ROUND (NVL (A.WT_L1, 0) / 8 + NVL (a.HOL_DAY_L1, 0), 10) wd1_17, " +
" ROUND (NVL (A.WT_L2, 0) / 8 + NVL (a.HOL_DAY_L2, 0), 10) wd2_18, " +
" ROUND (NVL (A.TOTAL_ABS_NPAY_L1, 0) / 8, 10) abs_npay_19, " +
" ROUND (NVL (A.TOTAL_ABS_NPAY_L2, 0) / 8, 10) abs_npay_20, " +
" ROUND (NVL (A.WT_L1_AMT, 0), 10) wd1_17, " +
" ROUND (NVL (A.WT_L2_AMT, 0), 10) wd2_18, " +
" ROUND (NVL (A.ABS_ALE_L1, 0) / 8, 10) abs_ale1_19, " +
" ROUND (NVL (A.ABS_ALE_L2, 0) / 8, 10) abs_ale2_20, " +
" ROUND (NVL (A.ABS_ALE_L1_AMT, 0), 10) abs_ale1_19, " +
" ROUND (NVL (A.ABS_ALE_L2_AMT, 0), 10) abs_ale2_20, " +
" ROUND (NVL (A.ABS_OTHER_PAY_L1, 0) / 8, 10) abs_other1_21, " +
" ROUND (NVL (A.ABS_OTHER_PAY_L2, 0) / 8, 10) abs_other2_22, " +
" ROUND (NVL (A.ABS_OTHER_PAY_L1_AMT, 0), 10) abs_other1_21, " +
" ROUND (NVL (A.ABS_OTHER_PAY_L2_AMT, 0), 10) abs_other2_22, " +
" 0 abs_12_1_19, "+
" 0 abs_12_2_20, "+
" 0 abs_12_1_19, "+
" 0 abs_12_2_20, "+
//" ROUND (NVL (A.ABS_12_L1, 0) / 8, 10) abs_12_1_19, " +
//" ROUND (NVL (A.ABS_12_L2, 0) / 8, 10) abs_12_2_20, " +
//" ROUND (NVL (A.ABS_12_L1_AMT, 0), 10) abs_12_1_19, " +
//" ROUND (NVL (A.ABS_12_L2_AMT, 0), 10) abs_12_2_20, " +
" ROUND (NVL (A.WT_L1_AMT, 0) + NVL (A.WT_L2_AMT, 0) + NVL (A.HOL_DAY_L1_AMT, 0) + NVL (A.HOL_DAY_L2_AMT, 0), 10) sal_wd_23, " +
" ROUND (NVL (A.WT_L1, 0) + NVL (a.HOL_DAY_L1, 0), 10) wd1_17, " +
" ROUND (NVL (A.WT_L2, 0) + NVL (a.HOL_DAY_L2, 0), 10) wd2_18, " +
" ROUND (NVL (A.OT_L1, 0), 10) ot_l1_24, " +
" ROUND (NVL (A.OT_L2, 0), 10) ot_l2_25, " +
" ROUND (NVL (A.OT_L1_amt, 0), 10) ot_l1_amt_24, " +
" ROUND (NVL (A.OT_L2_amt, 0), 10) ot_l2_amt_25, " +
" ROUND (NVL (A.ST_L1, 0), 10) st_l1_24, " +
" ROUND (NVL (A.ST_L2, 0), 10) st_l2_25, " +
" ROUND (NVL (A.ST_L1_amt, 0), 10) st_l1_amt_24, " +
" ROUND (NVL (A.ST_L2_amt, 0), 10) st_l2_amt_25, " +
" ROUND (NVL (A.OST_L1, 0), 10) ost_l1_24, " +
" ROUND (NVL (A.OST_L2, 0), 10) ost_l2_25, " +
" ROUND (NVL (A.OST_L1_amt, 0), 10) ost_l1_amt_24, " +
" ROUND (NVL (A.OST_L2_amt, 0), 10) ost_l2_amt_25, " +
" ROUND (NVL (A.HT_L1, 0), 10) ht_l1_24, " +
" ROUND (NVL (A.HT_L2, 0), 10) ht_l2_25, " +
" ROUND (NVL (A.HT_L1_amt, 0), 10) ht_l1_amt_24, " +
" ROUND (NVL (A.HT_L2_amt, 0), 10) ht_l2_amt_25, " +
" ROUND (NVL (A.OHT_L1, 0), 10) oht_l1_24, " +
" ROUND (NVL (A.OHT_L2, 0), 10) oht_l2_25, " +
" ROUND (NVL (A.OHT_L1_amt, 0), 10) oht_l1_amt_24, " +
" ROUND (NVL (A.OHT_L2_amt, 0), 10) oht_l2_amt_25, " +
" ROUND (NVL (A.NT_30_L1, 0), 10) nt_30_l1_28, " +
" ROUND (NVL (A.NT_30_L2, 0), 10) nt_30_l2_29, " +
" ROUND (NVL (A.NT_30_L1_amt, 0), 10) nt_30_l1_amt_28, " +
" ROUND (NVL (A.NT_30_L2_amt, 0), 10) nt_30_l2_amt_29, " +
" ROUND (NVL (A.NT_45_L1, 0), 10) nt_45_l1_30, " +
" ROUND (NVL (A.NT_45_L2, 0), 10) nt_45_l2_31, " +
" ROUND (NVL (A.NT_45_L1_amt, 0), 10) nt_45_l1_amt_30, " +
" ROUND (NVL (A.NT_45_L2_amt, 0), 10) nt_45_l2_amt_31, " +
" ROUND (NVL (A.NT_45_2_L1, 0), 10) nt_45_l1_30, " +
" ROUND (NVL (A.NT_45_2_L2, 0), 10) nt_45_l2_31, " +
" ROUND (NVL (A.NT_45_2_L1_amt, 0), 10) nt_45_l1_amt_30, " +
" ROUND (NVL (A.NT_45_2_L2_amt, 0), 10) nt_45_l2_amt_31, " +
" ROUND (NVL (A.NT_60_L1, 0), 10) nt_60_l1_32, " +
" ROUND (NVL (A.NT_60_L2, 0), 10) nt_60_l2_33, " +
" ROUND (NVL (A.NT_60_L1_amt, 0), 10) nt_60_l1_amt_32, " +
" ROUND (NVL (A.NT_60_L2_amt, 0), 10) nt_60_l2_amt_33, " +
" ROUND (NVL (A.NOST_L1, 0), 10) nt_60_l1_32, " +
" ROUND (NVL (A.NOST_L2, 0), 10) nt_60_l2_33, " +
" ROUND (NVL (A.NOST_L1_amt, 0), 10) nt_60_l1_amt_32, " +
" ROUND (NVL (A.NOST_L2_amt, 0), 10) nt_60_l2_amt_33, " +
" ROUND (NVL (A.NT_90_L1, 0), 10) nt_90_l1_34, " +
" ROUND (NVL (A.NT_90_L2, 0), 10) nt_90_l2_35, " +
" ROUND (NVL (A.NT_90_L1_amt, 0), 10) nt_90_l1_amt_34, " +
" ROUND (NVL (A.NT_90_L2_amt, 0), 10) nt_90_l2_amt_35, " +
" ROUND (NVL (A.NOHT_L1, 0), 10) nt_90_l1_34, " +
" ROUND (NVL (A.NOHT_L2, 0), 10) nt_90_l2_35, " +
" ROUND (NVL (A.NOHT_L1_amt, 0), 10) nt_90_l1_amt_34, " +
" ROUND (NVL (A.NOHT_L2_amt, 0), 10) nt_90_l2_amt_35, " +
" ROUND (NVL (A.TOTAL_OT_L1_AMT, 0) + NVL (A.TOTAL_OT_L2_AMT, 0), 10) " +
" total_ot_amt_36, " +
" ROUND (NVL (A.OT_TAX_L1_AMT, 0) + NVL (A.OT_TAX_L2_AMT, 0), 10) " +
" ot_tax_amt_37, " +
" ROUND (NVL (A.RETURN_AMT, 0), 10) return_amt_46, " +
" ROUND (NVL (A.INC_AMT, 0) + NVL (A.BONUS_YEARLY_AMT, 0), 10) " +
" incentive_amt_47, " +
" ROUND (NVL (A.ALE_STOP, 0), 10) ale_stop_48, " +
" ROUND (NVL (A.ALE_STOP_AMT, 0), 10) ale_stop_amt_49, " +
" ROUND (NVL (A.SEVERANCE_MONTH, 0), 10) serverance_mon_50, " +
" ROUND (NVL (A.SEVERANCE_AMT, 0), 10) serverance_amt_51, " +
" ROUND (NVL (A.GROSS_AMT, 0), 10) gross_amt_52, " +
" ROUND (NVL (A.BREAK_CONTRACT_AMT, 0), 10) break_con_amt_53, " +
" ROUND (NVL (A.ADVANCE_AMT, 0), 10) advance_amt_54, " +
" ROUND (NVL (A.ADD_FOR_TAX, 0), 10) add_for_tax, " +
" ROUND (NVL (A.GROSS_AMT_2, 0), 10) gross_amt_2_52, " +
" ROUND (NVL (A.DEDUCT_PIT, 0), 10) deduct_pit_55, " +
" ROUND (NVL (A.INCOME_AMT_2, 0), 10) income_amt_56, " +
" ROUND (NVL (A.INCOME_BEFORE_TAX, NVL (A.INCOME_AMT, 0)), 10) " +
" income_bef_amt_57, " +
" ROUND (NVL (A.INCOME_TAX, 0), 10) income_tax_58, " +
" ROUND (NVL (A.UNION_AMT, 0), 10) union_amt_59, " +
" ROUND (NVL (A.SOCIAL_AMT, 0), 10) social_amt_60, " +
" ROUND (NVL (A.HEALTH_AMT, 0), 10) health_amt_61, " +
" ROUND (NVL (A.UNEMP_INS, 0), 10) unemp_ins_amt_62, " +
" ROUND (NVL (A.RETURN_NO_PIT, 0) + NVL (A.ADJ_FOR_TAX, 0), 10) " +
" return_no_pit_63, " +
" ROUND (NVL (A.ADVANCE_NO_PIT, 0), 10) advance_no_pit_64, " +
" ROUND (NVL (A.health_card_amt, 0), 10) break_con_amt_53, " +
" ROUND (NVL (A.EQUIP1_AMT, 0), 10) equip1_amt_66, " +
" ROUND (NVL (A.EQUIP2_AMT, 0), 10) equip2_amt_67, " +
" ROUND (NVL (A.EQUIP3_AMT, 0), 10) equip3_amt_68, " +
" ROUND (NVL (A.EQUIP4_AMT, 0), 10) equip4_amt_69, " +
" ROUND (NVL (A.EQUIP5_AMT, 0), 10) equip5_amt_70, " +
" ROUND (NVL (A.EQUIP6_AMT, 0), 10) equip6_amt_71, " +
" ROUND (NVL (A.EQUIP7_AMT, 0), 10) equip7_amt_72, " +
" ROUND (NVL (A.EQUIP8_AMT, 0), 10) equip8_amt_73, " +
" ROUND (NVL (A.health_card_amt, 0) + NVL (A.EQUIP1_AMT, 0) - NVL (A.EQUIP2_AMT, 0) - NVL (A.EQUIP3_AMT, 0) - NVL (A.EQUIP4_AMT, 0) - NVL (A.EQUIP5_AMT, 0) - NVL (A.EQUIP6_AMT, 0) - NVL (A.EQUIP7_AMT, 0) - NVL (A.EQUIP8_AMT, 0), 10) total_deduct, " +
" ROUND (NVL (A.actual_deduct, 0), 10) actual_deduct, " +
" ROUND (NVL (A.ADVANCE_AMT2, 0), 10) advance_amt_2, " +
" ROUND (NVL (A.UNIFORM_AMT, 0), 10) uniform_amt, " +
" DECODE ('" + p_m_type + "', " +
" 'ALL', ROUND (NVL (A.NET_AMT, 0), 0), " +
" '01', ROUND (NVL (A.NET_AMT, 0), 0), " +
" ROUND (NVL (A.NET_AMT, 0), 10)) " +
" net_amt_65,'', " +
" ROUND (NVL (A.SOCIAL_COMPANY, 0), 10) social_com, " +
" ROUND (NVL (A.HEALTH_COMPANY, 0), 10) health_com, " +
" ROUND (NVL (A.UNEMP_COM_INS, 0), 10) unemp_com, " +
" ROUND (NVL (A.UNION_COMPANY, 0), 10) union_com, " +
" (SELECT TO_CHAR (TO_DATE (m.from_dt, 'yyyymmdd'), 'dd-mm-yy') " +
" || ' to ' " +
" || TO_CHAR (TO_DATE (m.to_dt, 'yyyymmdd'), 'dd-mm-yy') " +
" FROM thr_close m " +
" WHERE m.del_if = 0 " +
" AND m.ID = '" + p_sal_period + "' " +
" AND m.month_close = '" + p_work_mon + "' " +
" AND m.times = NVL (a.times, '0')) " +
" period_resign_75 " +
" FROM thr_month_salary a, tco_org b, thr_work_group c " +
" WHERE a.del_if = 0 " +
" AND a.work_mon = '" + p_work_mon + "' " +
" AND b.del_if = 0 " +
" AND c.del_if = 0 " +
" AND a.tco_dept_pk = b.pk " +
" AND a.thr_group_pk = c.pk " +
" AND (a.tco_dept_pk IN " +
" ( SELECT g.pk " +
" FROM tco_org g " +
" WHERE g.del_if = 0 " +
" START WITH g.pk = " +
" DECODE ('" + p_dept + "', " +
" 'ALL', 0, " +
" '" + p_dept + "') " +
" CONNECT BY PRIOR g.pk = g.p_pk) " +
" OR '" + p_dept + "' = 'ALL') " +
" AND DECODE ('" + p_group + "', 'ALL', 'ALL', a.thr_group_pk) = '" + p_group + "' " +
" AND a.emp_id LIKE '%" + p_emp_id + "%' " +
" AND DECODE ('" + p_status + "', 'ALL', 'ALL', a.status) = '" + p_status + "' " +
" AND DECODE ('" + p_pay_type + "', 'ALL', 'ALL', a.pay_type) = '" + p_pay_type + "' " +
" and decode('" + p_emp_type + "' ,'ALL','ALL',A.EMPLOYEE_TYPE)='" + p_emp_type + "' " +
" AND DECODE ('" + p_m_type + "', 'ALL', 'ALL', a.MONEY_KIND) = '" + p_m_type + "' " +
" AND DECODE ('" + p_sal_period + "', 'ALL', 'ALL', A.SAL_PERIOD) = '" + p_sal_period + "' " +
" AND NVL (a.net_amt, 0) " + p_type +
"ORDER BY A.DEPT_NM, a.emp_id ";
//Response.Write(SQL);
//Response.End();
DataTable dt_emp = CtlLib.TableReadOpen(SQL);
int irow_emp, icol_emp;
irow_emp = dt_emp.Rows.Count;
icol_emp = dt_emp.Columns.Count;
if (irow_emp == 0)
{
Response.Write("There is no data of salary");
Response.End();
}
string para_com = "'" + p_dept + "','" + p_work_mon + "'";
DataTable dt_com = CtlLib.TableReadOpenCursor("ST_HR_RPT_CO_COMPANY", para_com);
DataTable dt_allow = CtlLib.TableReadOpenCursor("ST_HR_RPT_CO_ALLOW_SALARY");
DataTable dt_allow_k = CtlLib.TableReadOpenCursor("ST_HR_RPT_CO_ALLOW_K_SALARY");
DataTable dt_equip = CtlLib.TableReadOpenCursor("ST_HR_RPT_CO_EQUIP");
SQL
= "SELECT NT_RATE nt30, " +
" NOT_RATE nt45, " +
" NOT_2_RATE nt45_2, " +
" OST_RATE ost, " +
" NOST_RATE nost, " +
" OHT_RATE oht, " +
" NOHT_RATE noht, " +
" NST_RATE nt60, " +
" NHT_RATE nt90 " +
" FROM THR_MONTH_OT_RATE " +
" WHERE del_if = 0 AND work_mon = '" + p_work_mon + "' ";
DataTable dt_rate = CtlLib.TableReadOpen(SQL);
int i, j, k, i_allow;
int pos = 7;
int allow = 11, allow_k = 24, equip = 125; //vi tri phu cap
int pos_nt = 73;
int emp_dept = 0, n_dept = 0;
Double[] sub_total = new Double[icol_emp];
Double[] grant_total = new Double[icol_emp];
//bind data to excel file
//-----header---
exSheet.Cells[1, 1].Select();
exSheet.Cells["C1"].Value = dt_com.Rows[0][0].ToString();
exSheet.Cells["C2"].Value = dt_com.Rows[0][1].ToString();
exSheet.Cells["C3"].Value = "Phone: " + dt_com.Rows[0][2].ToString();
exSheet.Cells[1, pos_nt].Value = "EMPLOYEE'S PAYROLL IN " + dt_com.Rows[0][4].ToString();
exSheet.Cells[2, pos_nt].Value = "BẢNG LƯƠNG NHÂN VIÊN THÁNG " + dt_com.Rows[0][5].ToString();
exSheet.Cells[4, pos_nt].Value = string.Format("NT {0}%", dt_rate.Rows[0]["NT30"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp đêm", dt_rate.Rows[0]["NT30"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NT {0}% Amount", dt_rate.Rows[0]["NT30"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp đêm {0}%", dt_rate.Rows[0]["NT30"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NOT {0}%", dt_rate.Rows[0]["NT45"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp T.C đêm", dt_rate.Rows[0]["NT45"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NOT {0}% Amount", dt_rate.Rows[0]["NT45"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ T.C cấp đêm {0}%", dt_rate.Rows[0]["NT45"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NOT {0}%", dt_rate.Rows[0]["NT45_2"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp T.C đêm", dt_rate.Rows[0]["NT45_2"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NOT {0}% Amount", dt_rate.Rows[0]["NT45_2"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp T.C đêm {0}%", dt_rate.Rows[0]["NT45_2"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NST {0}%", dt_rate.Rows[0]["NT60"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp đêm chủ nhật", dt_rate.Rows[0]["NT60"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NST {0}% Amount", dt_rate.Rows[0]["NT60"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp đêm chủ nhật {0}%", dt_rate.Rows[0]["NT60"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NOST {0}%", dt_rate.Rows[0]["NOST"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp T.C đêm chủ nhật", dt_rate.Rows[0]["NOST"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NOST {0}% Amount", dt_rate.Rows[0]["NOST"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp T.C đêm chủ nhật {0}%", dt_rate.Rows[0]["NOST"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NHT {0}%", dt_rate.Rows[0]["NT90"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp đêm lễ", dt_rate.Rows[0]["NT90"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NHT {0}% Amount", dt_rate.Rows[0]["NT90"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp đêm lễ {0}%", dt_rate.Rows[0]["NT90"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NOHT {0}%", dt_rate.Rows[0]["NOHT"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Trợ cấp T.C đêm lễ", dt_rate.Rows[0]["NOHT"].ToString());
pos_nt += 2;
exSheet.Cells[4, pos_nt].Value = string.Format("NOHT {0}% Amount", dt_rate.Rows[0]["NOHT"].ToString());
exSheet.Cells[5, pos_nt].Value = string.Format("Tiền trợ cấp T.C đêm lễ {0}%", dt_rate.Rows[0]["NOHT"].ToString());
for (i_allow = 0; i_allow < 12; i_allow++)
{
if (dt_allow.Rows[0][i_allow + 12].ToString() == "1")
{
exSheet.Cells[4, allow + i_allow].Value = dt_allow.Rows[0][i_allow].ToString();
exSheet.Cells[5, allow + i_allow].Value = dt_allow.Rows[0][24 + i_allow].ToString();
}
}
for (i_allow = 0; i_allow < 8; i_allow++)
{
if (dt_allow_k.Rows[0][i_allow].ToString() != "")
{
exSheet.Cells[4, allow_k + i_allow].Value = dt_allow_k.Rows[0][i_allow].ToString();
exSheet.Cells[5, allow_k + i_allow].Value = dt_allow_k.Rows[0][i_allow + 8].ToString();
}
if (dt_equip.Rows[0][i_allow].ToString() != "")
{
exSheet.Cells[4, equip + i_allow].Value = dt_equip.Rows[0][i_allow].ToString();
exSheet.Cells[6, equip + i_allow].Value = dt_equip.Rows[0][i_allow + 8].ToString();
}
}
//insert picture
if (dt_com.Rows[0][3].ToString() != "")
{
byte[] MyData = new byte[0];
MyData = (byte[])dt_com.Rows[0][3];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 115, 85));
}
//---end--header---
for (k = 0; k < icol_emp; k++)
{
sub_total[k] = 0;
grant_total[k] = 0;
}
for (i = 0; i < irow_emp - 1; i++)
{
j = i + 1;
if (dt_emp.Rows[i]["dept_pk"].ToString() != dt_emp.Rows[j]["dept_pk"].ToString())
{
n_dept++;
}
}
//Insert new row
for (i = 0; i < irow_emp - 1 + n_dept; i++)
{
exSheet.Range[pos + 1, 1].Rows.EntireRow.Insert();
}
//Show data
for (i = 0; i < irow_emp; i++)
{
for (k = 9; k < icol_emp - 1; k++)
{
if (k == icol_emp - 6)
{
continue;
}
else
{
sub_total[k] = sub_total[k] + double.Parse(dt_emp.Rows[i][k].ToString());
grant_total[k] = grant_total[k] + double.Parse(dt_emp.Rows[i][k].ToString());
}
}
emp_dept++;
exSheet.Cells[pos + i, 1].Value = emp_dept;
for (j = 2; j < icol_emp; j++)
{
if (j < 9 || j == icol_emp - 6 || j == icol_emp - 1)
{
exSheet.Cells[pos + i, j].Value = dt_emp.Rows[i][j].ToString();
}
else
{
exSheet.Cells[pos + i, j].Value = double.Parse(dt_emp.Rows[i][j].ToString());
}
}
if (i != irow_emp - 1 && (dt_emp.Rows[i]["dept_pk"].ToString() != dt_emp.Rows[i + 1]["dept_pk"].ToString()))
{
exSheet.Cells[pos + i + 1, 1, pos + i + 1, 4].Merge();
exSheet.Cells[pos + i + 1, 1].Value = "Total " + dt_emp.Rows[i]["dept_nm"].ToString() + " (" + emp_dept + " employees)";
exSheet.Cells[pos + i + 1, 1, pos + i + 1, icol_emp - 1].Rows.Font.Bold = true;
for (k = 9; k < icol_emp - 1; k++)
{
exSheet.Cells[pos + i + 1, k].Value = sub_total[k];
sub_total[k] = 0;
}
//Insert page break
IPageBreaks pbs = exBook.Worksheets[1].HPageBreaks;
IRange usedrange = exBook.Worksheets[1].UsedRange;
//pbs.Reset();
pbs.Add(usedrange.Rows[pos + i + 2]);
//end insert page beak
pos = pos + 1;
emp_dept = 0;
}
else if (i == irow_emp - 1)
{
exSheet.Cells[pos + i + 1, 1, pos + i + 1, 4].Merge();
exSheet.Cells[pos + i + 1, 1].Value = "Total " + dt_emp.Rows[i]["dept_nm"].ToString() + ": " + emp_dept + " employee(s)";
exSheet.Cells[pos + i + 1, 1, pos + i + 1, icol_emp - 1].Rows.Font.Bold = true;
exSheet.Cells[pos + i + 2, 1, pos + i + 2, 4].Merge();
exSheet.Cells[pos + i + 2, 1].Value = "Grant Total: " + dt_emp.Rows.Count + " employee(s)";
exSheet.Cells[pos + i + 2, 1, pos + i + 2, icol_emp - 1].Rows.Font.Bold = true;
for (k = 9; k < icol_emp - 1; k++)
{
exSheet.Cells[pos + i + 1, k].Value = sub_total[k];
exSheet.Cells[pos + i + 2, k].Value = grant_total[k];
}
}
}
//Hide allowance column
for (i_allow = 0; i_allow < 12; i_allow++)
{
if (dt_allow.Rows[0][i_allow + 12].ToString() != "1")
{
exSheet.Range[1, allow + i_allow].Columns.Hidden = true;
}
}
for (i_allow = 0; i_allow < 8; i_allow++)
{
if (dt_allow_k.Rows[0][i_allow].ToString() == "")
{
exSheet.Range[1, allow_k + i_allow].Columns.Hidden = true;
}
if (dt_equip.Rows[0][i_allow].ToString() == "")
{
exSheet.Range[1, equip + i_allow].Columns.Hidden = true;
}
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
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_dscd00070_kb01.xls";
string TempFile = "../../../../system/temp/rpt_dscd00070_kb01_" + 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 master_pk = Request["master_pk"];
string para = "'" + master_pk + "'";
DataTable dt = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00070_kb01", para);
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00070_kb01_1", para);
// DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_epbp00010_st01_2", "'" + Session["User_ID"].ToString() + "'");
// MASTER
if (dt.Rows.Count > 0)
{
exSheet.Cells["h6"].Value = dt.Rows[0]["slip_no"];
exSheet.Cells["h7"].Value = "No/dr "; //+dt.Rows[0]["req_date"];
exSheet.Cells["h8"].Value = "Co/cr"; //;+dt.Rows[0]["dept_name"];
exSheet.Cells["d8"].Value = dt.Rows[0]["dd"];
exSheet.Cells["e8"].Value = dt.Rows[0]["mm"];
exSheet.Cells["f8"].Value = dt.Rows[0]["yy"];
exSheet.Cells["d9"].Value = dt.Rows[0]["partner_name"];
exSheet.Cells["d10"].Value = dt.Rows[0]["addr"];
exSheet.Cells["d11"].Value = dt.Rows[0]["TAX_CODE"];
exSheet.Cells["d12"].Value = dt.Rows[0]["PHONE_NO"];
exSheet.Cells["f12"].Value = dt.Rows[0]["FAX_NO"];
exSheet.Cells["d16"].Value = dt.Rows[0]["description"];
exSheet.Cells["e13"].Value = dt.Rows[0]["deli_loc_pk"];
exSheet.Cells["e14"].Value = dt.Rows[0]["full_name"];
exSheet.Cells["h14"].Value = dt.Rows[0]["tel"];
}
// INSERT ROWS
//for (int l_addrow = 0; l_addrow < dtDetail.Rows.Count-1; l_addrow++)
//{
// exSheet.Range["A17"].Rows.EntireRow.Insert();//insert row new of sheet
//}
// SET DATA
int pos = 19;
int i = 0;
for (i = 0; i < dtDetail.Rows.Count; i++)
{
exSheet.Range[pos + i, 4, pos + i, 5].Merge();
exSheet.Cells[pos + i, 2].Value = i + 1;
exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["out_item_code"];
exSheet.Cells[pos + i, 4].Value = dtDetail.Rows[i]["out_item_name"];
exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["lot_no"];
exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["out_qty"];
// exSheet.Cells[pos + i, 8 ].Value = dtDetail.Rows[i]["in_uom"];
exSheet.Cells[pos + i, 9].Value = dtDetail.Rows[i]["description"];
}
// exSheet.Cells["i30"].Value =dtDetail.Rows[0]["item_amount"];
// exSheet.Cells["i31"].Value =dtDetail.Rows[0]["vat_amount"];
// exSheet.Cells["i32"].Value =dtDetail.Rows[0]["total_amount"];
//---------
//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);
}
// 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_floor_worksheet = Request["p_floor_worksheet"];
string l_date = Request["p_date"];
string TemplateFile = "rpt_60260030_worksheet.xls";
string TempFile = "../../../../system/temp/rpt_60260030_worksheet_" + 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_floor_worksheet + "','" + l_date + "'";
dt = CtlLib.TableReadOpenCursor("ht_rpt_60260030_worksheet", 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[4, 12].Value = dt.Rows[0]["date_para"].ToString();
exSheet.Cells[4, 28].Value = dt.Rows[0]["print_time"].ToString();
exSheet.Cells[5, 1].Value = dt.Rows[0]["floor_name"].ToString();
}
//----------------------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A10"].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 <= 30; col++)
{
if (col == 3 || col == 4)
{
exSheet.Cells[8 + l_row, col + 1].Font.Name = "Wingdings 2";
exSheet.Cells[8 + l_row, col + 1].Font.Bold = true;
}
exSheet.Cells[8 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString();
exSheet.Cells[l_row + 8, col + 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("crm");
string TemplateFile = "rpt_ht_60270090_POS_Adjust_Inquiry.xls";
string TempFile = "../../../../system/temp/rpt_ht_60270090_POS_Adjust_Inquiry_" + 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_invoice_or_room_no = Request["p_invoice_or_room_no"];
string p_dt_frm = Request["p_dt_frm"];
string p_dt_to = Request["p_dt_to"];
string para = "'" + p_invoice_or_room_no + "','" + p_dt_frm + "','" + p_dt_to + "'";
DataTable dt = CtlLib.TableReadOpenCursor("sp_rpt_htrt00160", para);
// MASTER
// INSERT ROWS
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A5"].Rows.EntireRow.Insert();//insert row new of sheet
}
// SET DATA
int pos = 4;
int i = 0;
for (i = 0; i < dt.Rows.Count; i++)
{
exSheet.Cells[pos + i, 1].Value = dt.Rows[i]["invoice_id"];
exSheet.Cells[pos + i, 2].Value = dt.Rows[i]["room_no"];
exSheet.Cells[pos + i, 3].Value = dt.Rows[i]["guest_name"];
exSheet.Cells[pos + i, 4].Value = dt.Rows[i]["invoice_amtvat"];
exSheet.Cells[pos + i, 5].Value = dt.Rows[i]["adj_date1"];
exSheet.Cells[pos + i, 6].Value = dt.Rows[i]["adj_amt_vnd"];
exSheet.Cells[pos + i, 7].Value = dt.Rows[i]["adj_by_pk"];
exSheet.Cells[pos + i, 8].Value = dt.Rows[i]["adj_reason"];
exSheet.Cells[pos + i, 9].Value = dt.Rows[i]["paymethod"];
exSheet.Cells[pos + i, 10].Value = dt.Rows[i]["crt_dt"];
}
exSheet.Cells["B2"].Value = dt.Rows[0]["para_date"];
exSheet.Cells["I2"].Value = dt.Rows[0]["print_time"];
//exSheet.Cells[pos + i, 6 ].Value = "=sum(F10:F"+(pos+i-1)+")";
exSheet.Cells[pos + i, 4].Value = "=sum(D4:D" + (pos + i - 1) + ")";
exSheet.Cells[pos + i, 6].Value = "=sum(F4:F" + (pos + i - 1) + ")";
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_60250010_birthday.xls";
string TempFile = "../../../../system/temp/rpt_60250010_birthday_" + 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_pk = Request.QueryString["p_pk"];
string p_dtfrom = Request.QueryString["p_dtfrom"];
string p_dtto = Request.QueryString["p_dtto"];
string para = "'" + p_pk + "','" + p_dtfrom + "','" + p_dtto + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("ht_rpt_60250010_info", "'" + Session["User_ID"].ToString() + "'");
DataTable dtDetail = CtlLib.TableReadOpenCursor("hr_sel_10010023_announce_data", para);
////end-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();
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A7"].Rows.EntireRow.Insert();//insert row new of sheet
}
//set data to detail.
// loop detail not percent
int l_pos = 6;
int i = 0;
for (i = 0; i < dtDetail.Rows.Count; i++)
{
exSheet.Cells[4, 1].Value = "Birthday :" + dtDetail.Rows[0]["dtfrom"].ToString() + " ~ " + dtDetail.Rows[0]["dtto"].ToString();
exSheet.Cells[4, 7].Value = "Print time :" + dtDetail.Rows[0]["print_time"].ToString();
exSheet.Cells[l_pos + i, 1].Value = i + 1;
exSheet.Cells[l_pos + i, 2].Value = dtDetail.Rows[i]["room_no"].ToString();
exSheet.Cells[l_pos + i, 3].Value = dtDetail.Rows[i]["full_name"].ToString();
exSheet.Cells[l_pos + i, 4].Value = dtDetail.Rows[i]["gender"].ToString();
exSheet.Cells[l_pos + i, 5].Value = dtDetail.Rows[i]["nation"];
exSheet.Cells[l_pos + i, 6].Value = dtDetail.Rows[i]["checkin_date"].ToString();
exSheet.Cells[l_pos + i, 7].Value = dtDetail.Rows[i]["departure_date"].ToString();
exSheet.Cells[l_pos + i, 8].Value = dtDetail.Rows[i]["birthday"].ToString();
}
//exSheet.Cells[dtDetail.Rows.Count + 6, 36].Value = "=SUM(AJ5:AJ" + (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);
//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_bisc00160_grand.xls";
string TempFile = "../../../../system/temp/rpt_bisc00160_grand_" + 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 = Request.QueryString["p_wh"];
string p_wh_name = Request.QueryString["p_wh_name"];
string p_use_yn = Request.QueryString["p_use_yn"];
string p_item_grp = Request.QueryString["p_item_grp"];
string p_item = Request.QueryString["p_item"];
string p_lang = Request.QueryString["p_lang"];
string para = "'" + p_from_date +
"','" + p_to_date +
"','" + p_wh_type +
"','" + p_wh +
"','" + p_use_yn +
"','" + p_item_grp +
"','" + p_item +
"','" + p_lang + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00160_2", "'" + Session["User_ID"].ToString() + "'");
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00160_1", para);
//Set Data 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");
string strFromDt = System.DateTime.ParseExact(p_from_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
string strToDt = System.DateTime.ParseExact(p_to_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
exSheet.Cells["A6"].Value = String.Format("W/H name: {0}", p_wh_name);
exSheet.Cells["E6"].Value = String.Format("Date: {0} - {1}", strFromDt, strToDt);
//insert row new of sheet
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A9"].Rows.EntireRow.Insert();
}
//sum variable
double sum_begin_qty = 0;
double sum_begin_ref_qty = 0;
double sum_in_qty = 0;
double sum_in_ref_qty = 0;
double sum_out_qty = 0;
double sum_out_ref_qty = 0;
double sum_end_qty = 0;
double sum_end_ref_qty = 0;
double tmp_begin_qty = 0;
double tmp_begin_ref_qty = 0;
double tmp_in_qty = 0;
double tmp_in_ref_qty = 0;
double tmp_out_qty = 0;
double tmp_out_ref_qty = 0;
double tmp_end_qty = 0;
double tmp_end_ref_qty = 0;
//Set data
int l_pos = 8;
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
exSheet.Cells[l_pos + i, 1].Value = dtDetail.Rows[i]["grp_nm"];
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]["lot_no"];
exSheet.Cells[l_pos + i, 6].Value = dtDetail.Rows[i]["begin_qty"];
exSheet.Cells[l_pos + i, 7].Value = dtDetail.Rows[i]["begin_ref_qty"];
exSheet.Cells[l_pos + i, 8].Value = dtDetail.Rows[i]["in_qty"];
exSheet.Cells[l_pos + i, 9].Value = dtDetail.Rows[i]["in_ref_qty"];
exSheet.Cells[l_pos + i, 10].Value = dtDetail.Rows[i]["out_qty"];
exSheet.Cells[l_pos + i, 11].Value = dtDetail.Rows[i]["out_ref_qty"];
exSheet.Cells[l_pos + i, 12].Value = dtDetail.Rows[i]["end_qty"];
exSheet.Cells[l_pos + i, 13].Value = dtDetail.Rows[i]["end_ref_qty"];
if (double.TryParse(dtDetail.Rows[i]["begin_qty"].ToString(), out tmp_begin_qty))
{
sum_begin_qty += tmp_begin_qty;
}
if (double.TryParse(dtDetail.Rows[i]["begin_ref_qty"].ToString(), out tmp_begin_ref_qty))
{
sum_begin_ref_qty += tmp_begin_ref_qty;
}
if (double.TryParse(dtDetail.Rows[i]["in_qty"].ToString(), out tmp_in_qty))
{
sum_in_qty += tmp_in_qty;
}
if (double.TryParse(dtDetail.Rows[i]["in_ref_qty"].ToString(), out tmp_in_ref_qty))
{
sum_in_ref_qty += tmp_in_ref_qty;
}
if (double.TryParse(dtDetail.Rows[i]["out_qty"].ToString(), out tmp_out_qty))
{
sum_out_qty += tmp_out_qty;
}
if (double.TryParse(dtDetail.Rows[i]["out_ref_qty"].ToString(), out tmp_out_ref_qty))
{
sum_out_ref_qty += tmp_out_ref_qty;
}
if (double.TryParse(dtDetail.Rows[i]["end_qty"].ToString(), out tmp_end_qty))
{
sum_end_qty += tmp_end_qty;
}
if (double.TryParse(dtDetail.Rows[i]["end_ref_qty"].ToString(), out tmp_end_ref_qty))
{
sum_end_ref_qty += tmp_end_ref_qty;
}
}
exSheet.Cells[dtDetail.Rows.Count + l_pos, 6].Value = String.Format("{0:0,0}", sum_begin_qty);
exSheet.Cells[dtDetail.Rows.Count + l_pos, 7].Value = String.Format("{0:0,0}", sum_begin_ref_qty);
exSheet.Cells[dtDetail.Rows.Count + l_pos, 8].Value = String.Format("{0:0,0}", sum_in_qty);
exSheet.Cells[dtDetail.Rows.Count + l_pos, 9].Value = String.Format("{0:0,0}", sum_in_ref_qty);
exSheet.Cells[dtDetail.Rows.Count + l_pos, 10].Value = String.Format("{0:0,0}", sum_out_qty);
exSheet.Cells[dtDetail.Rows.Count + l_pos, 11].Value = String.Format("{0:0,0}", sum_out_ref_qty);
exSheet.Cells[dtDetail.Rows.Count + l_pos, 12].Value = String.Format("{0:0,0}", sum_end_qty);
exSheet.Cells[dtDetail.Rows.Count + l_pos, 13].Value = String.Format("{0:0,0}", sum_end_ref_qty);
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].Interior.Color = Color.FromArgb(255, 204, 153);
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].RowHeight = 30;
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].VerticalAlignment = XlVAlign.xlVAlignCenter;
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].HorizontalAlignment = XlHAlign.xlHAlignRight;
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].Font.Bold = true;
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].Borders[XlBordersIndex.xlInsideAll].LineStyle = XlLineStyle.xlContinuous;
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 13].Borders[XlBordersIndex.xlAround].LineStyle = XlLineStyle.xlContinuous;
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 5].Merge();
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 5].HorizontalAlignment = XlHAlign.xlHAlignCenter;
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 5].Value = "GRAND TOTAL";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 1, dtDetail.Rows.Count + l_pos, 5].Font.Bold = false;
// end loop detail percent
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 l_pk = Request["master_pk"];
string TemplateFile = "rpt_bisc00020_ST02.xls";
string TempFile = "../../../../system/temp/rpt_bisc00020_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 para = "'" + l_pk + "'";
string p_from_date = Request["p_from_date"];
string p_to_date = Request["p_to_date"];
string p_wh_type = Request["p_wh_type"];
string p_tin_warehouse_pk = Request["p_tin_warehouse_pk"];
string p_item_group_pk = Request["p_item_group_pk"];
string p_item = Request["p_item"];
string p_in_out_yn = Request["p_in_out_yn"];
string p_wh_name = Request["p_wh_name"];
string para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_tin_warehouse_pk + "','" + p_item_group_pk + "','" + p_item + "','" + p_in_out_yn + "'";
DataTable dt = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_ST02_1", "'" + Session["User_ID"].ToString() + "'");
DataTable dt1 = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_ST02_2", para);
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dt.Rows[0]["pic"].ToString()))
{
exSheet.Cells[1, 1].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dt.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 90, 59));
}
exSheet.Cells[6, 1].Value = "W/H Name : " + p_wh_name;
exSheet.Cells[2, 3].Value = dt.Rows[0]["partner_name"].ToString();
exSheet.Cells[3, 3].Value = dt.Rows[0]["addr1"].ToString();
exSheet.Cells[4, 3].Value = "Tax code:" + dt.Rows[0]["tax_code"].ToString();
exSheet.Cells[2, 6].Value = "Tell:" + dt.Rows[0]["phone_no"].ToString();
exSheet.Cells[3, 6].Value = "Fax:" + dt.Rows[0]["fax_no"].ToString();
exSheet.Cells[4, 6].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
exSheet.Cells[6, 6].Value = System.DateTime.ParseExact(p_from_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy") + "-" + System.DateTime.ParseExact(p_to_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
for (int l_addrow = 1; l_addrow < dt1.Rows.Count; l_addrow++)
{
exSheet.Range["A9"].Rows.EntireRow.Insert();//insert row new of sheet
}
double total = 0;
for (int l_row = 0; l_row <= dt1.Rows.Count - 1; l_row++)
{
total = total + CommondLib.ConvertToDbl(dt1.Rows[l_row]["end_qty"].ToString());
exSheet.Cells[8 + l_row, 1].Value = l_row + 1;
exSheet.Cells[8 + l_row, 2].Value = dt1.Rows[l_row]["item_code"];
exSheet.Cells[8 + l_row, 3].Value = dt1.Rows[l_row]["item_name"];
exSheet.Cells[8 + l_row, 4].Value = dt1.Rows[l_row]["uom"];
if (dt1.Rows[l_row]["end_qty"].ToString() != "")
{
exSheet.Cells[8 + l_row, 5].Value = CommondLib.ConvertToDbl(dt1.Rows[l_row]["end_qty"].ToString());
}
}
exSheet.Cells[8 + dt1.Rows.Count, 5].Value = total;
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
range.Rows.Hidden = true;
// font bold header
exBook.SaveAs(TempFile);
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string p_org, p_position, p_search, p_requestno, p_pass, p_select, p_from_dt, p_to_dt, p_lang;
p_org = Request["p_org"].ToString();
p_position = Request["p_position"].ToString();
p_search = Request["p_search"].ToString();
p_requestno = Request["p_request_no"].ToString();
p_pass = Request["p_pass"].ToString();
p_select = Request["p_select"].ToString();
p_from_dt = Request["p_from_dt"].ToString();
p_to_dt = Request["p_to_dt"].ToString();
p_lang = Request["p_lang"].ToString();
string TemplateFile;
if (p_lang == "ENG")
{
TemplateFile = "rpt_hrrc00400_1_eng.xls";
}
else
{
TemplateFile = "rpt_hrrc00400_1_vie.xls";
}
string TempFile = "../../../../system/temp/rpt_hrrc00400_1" + 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_org + "','" + p_position + "','" + p_search + "','" + p_requestno + "','" + p_pass + "','" + p_select + "','" + p_from_dt
+ "','" + p_to_dt + "','" + p_lang + "'";
DataTable dt_emp = CtlLib.TableReadOpenCursor("ST_HR_RPT_HRRC00400_1", para);
if (dt_emp.Rows.Count == 0)
{
Response.Write("Không có dữ liệu!");
Response.End();
}
int row_loop = 4;
int l_col = dt_emp.Columns.Count;
int count = 0;
for (int l_addrow = 1; l_addrow < dt_emp.Rows.Count; l_addrow++)
{
exSheet.Range[row_loop + l_addrow, 1].Rows.EntireRow.Insert(); //insert row new of sheet
}
if (p_from_dt != "" && p_to_dt != "")
{
exSheet.Cells["A2"].Value = "Từ ngày " + dt_emp.Rows[0][0].ToString() + " Đến ngày " + dt_emp.Rows[0][1].ToString();
}
// xu ly du lieu do vao excel
if (dt_emp.Rows.Count > 0)
{
for (int l_row = 0; l_row < dt_emp.Rows.Count; l_row++)
{
count++;
exSheet.Cells[row_loop + l_row, 1].Value = count;
// do du lieu vao cac dong
for (int col = 2; col < l_col; col++)
{
if (IsNumeric(dt_emp.Rows[l_row][col].ToString()) && col != 2)
{
exSheet.Cells[row_loop + l_row, col].Value = double.Parse(dt_emp.Rows[l_row][col].ToString());
}
else
{
exSheet.Cells[row_loop + l_row, col].Value = dt_emp.Rows[l_row][col].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
/*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("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---
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_Room_Charge = 0, l_Total_Due = 0, l_Payed = 0, l_Deposit = 0, l_F_B_Hotel = 0, l_MiniBar = 0, l_Laundry = 0;
double 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 = 1; l_row <= dt.Rows.Count; l_row++)
{
for (int col = 0; col <= 25; col++)
{
if (col == 1 || col == 2 || col == 3 || col == 4 || col == 6 || col == 20 || col == 21 || col == 22 || col == 23 || col == 24 || col == 25)
{
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 (dt.Rows[l_row - 1][col].ToString() != "")
{
exSheet.Cells[4 + l_row, col + 1].Value = double.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 8)
{
l_Room_Charge = l_Room_Charge + double.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 9)
{
l_Total_Due = l_Total_Due + double.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 10)
{
if (dt.Rows[l_row - 1][col].ToString() != "")
{
l_Payed = l_Payed + double.Parse(dt.Rows[l_row - 1][col].ToString());
}
}
if (col == 11)
{
l_Deposit = l_Deposit + double.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 12)
{
l_F_B_Hotel = l_F_B_Hotel + double.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 13)
{
l_MiniBar = l_MiniBar + double.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 14)
{
l_Laundry = l_Laundry;
}
if (col == 15)
{
l_Telephone = l_Telephone + double.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 16)
{
l_Others = l_Others + double.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 17)
{
l_Spa_fee = l_Spa_fee + double.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 18)
{
l_Lotus_shop = l_Lotus_shop + double.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 19)
{
l_Adjust = l_Adjust + 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 (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 = "";
}
}
}
}
exSheet.Cells[5 + dt.Rows.Count, 9].Value = l_Room_Charge;
exSheet.Cells[5 + dt.Rows.Count, 10].Value = l_Total_Due;
exSheet.Cells[5 + dt.Rows.Count, 11].Value = l_Payed;
exSheet.Cells[5 + dt.Rows.Count, 12].Value = l_Deposit;
exSheet.Cells[5 + dt.Rows.Count, 13].Value = l_F_B_Hotel;
exSheet.Cells[5 + dt.Rows.Count, 14].Value = l_MiniBar;
exSheet.Cells[5 + dt.Rows.Count, 15].Value = l_Laundry;
exSheet.Cells[5 + dt.Rows.Count, 16].Value = l_Telephone;
exSheet.Cells[5 + dt.Rows.Count, 17].Value = l_Others;
exSheet.Cells[5 + dt.Rows.Count, 18].Value = l_Spa_fee;
exSheet.Cells[5 + dt.Rows.Count, 19].Value = l_Lotus_shop;
exSheet.Cells[5 + dt.Rows.Count, 20].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("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_from = Request["p_dtfrom"];
string l_to = Request["p_dtto"];
string TemplateFile = "rpt_60280170_rvn_cabana_detail.xls";
string TempFile = "../../../../system/temp/rpt_60280170_rvn_cabana_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_60280170", 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 l_dtfrm = Request["p_dtfrm"];
string l_dtto = Request["p_dtto"];
string TemplateFile = "rpt_60240050_special_service_booking.xls";
string TempFile = "../../../../system/temp/rpt_60240050_special_service_booking_" + 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_dtfrm + "','" + l_dtto + "'";
dt = CtlLib.TableReadOpenCursor("ht_rpt_60240050", 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]["print_time"].ToString();
exSheet.Cells[3, 5].Value = dt.Rows[0]["date_para"].ToString();
/*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_total_vnd = 0;
float l_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 <= 7; col++)
{
if (col == 0 || col == 1 || col == 2 || col == 3 || col == 5 || col == 6)
{
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 (dt.Rows[l_row - 1][col].ToString() == "")
{
exSheet.Cells[4 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString();
}
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 (dt.Rows[l_row - 1][0].ToString() != "" && dt.Rows[l_row - 1][3].ToString() == "")
{
exSheet.Cells[l_row + 4, 1, l_row + 4, 8].Interior.Color = Color.Silver;
}
if (dt.Rows[l_row - 1][0].ToString() == "" && dt.Rows[l_row - 1][3].ToString() == "")
{
exSheet.Cells[l_row + 4, 1, l_row + 4, 8].Interior.Color = Color.Orange;
}
}
}
// 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_dsos00010_ST01.xls";
string TempFile = "../../../../system/temp/rpt_dsos00010_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_tsa_saleorder_m_pk = Request.QueryString["p_tsa_saleorder_m_pk"];
string para = "'" + p_tsa_saleorder_m_pk + "'";
DataTable dtSaleOrd = CtlLib.TableReadOpenCursor("st_lg_rpt_dsos00010_st01_1", para);
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dsos00010_st01_2", para);
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_dsos00010_st01_3", "'" + Session["User_ID"].ToString() + "'");
// MASTER
//--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, 90, 59));
}
//exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"];
//exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"];
//exSheet.Cells["C3"].Value = "Tax Code: " + dtInfo.Rows[0]["tax_code"];
//exSheet.Cells["G1"].Value = "Tel: " + dtInfo.Rows[0]["phone_no"];
//exSheet.Cells["G2"].Value = "Fax No: " + dtInfo.Rows[0]["fax_no"];
exSheet.Cells["I4"].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
//SET DATA MASTER SALE ORDER
if (dtSaleOrd.Rows.Count > 0)
{
exSheet.Cells["C6"].Value = dtSaleOrd.Rows[0]["item_name"];
exSheet.Cells["D9"].Value = dtSaleOrd.Rows[0]["unit_price"];
exSheet.Cells["C10"].Value = dtSaleOrd.Rows[0]["ord_qty"];
}
// INSERT ROWS
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A15"].Rows.EntireRow.Insert();//insert row new of sheet
}
// SET DATA DETAIL
int pos = 14, l_cnt = 0;
int i = 0, l_total_amount = 0;
l_cnt = dtDetail.Rows.Count;
for (i = 0; i < dtDetail.Rows.Count; i++)
{
exSheet.Cells[pos + i, 1].Value = i + 1;
exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["item_code"];
exSheet.Cells[pos + i, 4].Value = dtDetail.Rows[i]["item_name"];
exSheet.Cells[pos + i, 5].Value = dtDetail.Rows[i]["cons_qty"];
exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["mat_uom"];
exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["pur_price"];
exSheet.Cells[pos + i, 8].Value = dtDetail.Rows[i]["loss_rate"];
exSheet.Cells[pos + i, 9].Value = dtDetail.Rows[i]["amount"];
if (dtDetail.Rows[i]["amount"].ToString() != "")
{
l_total_amount = l_total_amount + int.Parse(dtDetail.Rows[i]["amount"].ToString());
}
}
exSheet.Cells[pos + l_cnt, 9].Value = l_total_amount;
exSheet.Cells["I9"].Value = l_total_amount;
//---------
//---------
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();
}