protected void Page_Load(object sender, EventArgs e) { 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(); string TemplateFile = "rpt_hrpm00700_2.xls"; string TempFile = "../../../../system/temp/rpt_hrpm00700_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 SQL = "SELECT A.FULL_NAME full_nm, " + " A.EMP_ID emp_id, " + " a.DEPT_NM dept_nm, " + " TO_CHAR (TO_DATE (A.JOIN_DT, 'yyyymmdd'), 'dd/mm/yyyy') join_dt, " + " (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, " + " ROUND (NVL (A.ALLOW_AMT1, 0), 10) allow_amt6, " + " ROUND (NVL (A.ALLOW_AMT2, 0), 10) allow_amt1, " + " ROUND (NVL (A.ALLOW_AMT3, 0), 10) allow_amt1, " + " ROUND (NVL (A.ALLOW_AMT4, 0), 10) allow_amt4, " + " ROUND (NVL (A.ALLOW_AMT5, 0), 10) allow_amt5, " + " ROUND (NVL (A.ALLOW_AMT6, 0), 10) allow_amt6, " + " ROUND (NVL (A.ALLOW_AMT7, 0), 10) allow_amt7, " + " ROUND (NVL (A.ALLOW_AMT8, 0), 10) allow_amt8, " + " ROUND (NVL (A.ALLOW_AMT9, 0), 10) allow_amt9, " + " ROUND (NVL (A.ALLOW_AMT10, 0), 10) allow_amt10, " + " ROUND (NVL (A.ALLOW_AMT11, 0), 10) allow_amt11, " + " ROUND (NVL (A.ALLOW_AMT12, 0), 10) allow_amt12,'', " + " ROUND (NVL (A.WT_L1_AMT, 0)+ NVL (a.HOL_DAY_L1_AMT, 0)+ NVL (A.WT_L2_AMT, 0)+ NVL (a.HOL_DAY_L2_AMT, 0),3) total_wd_amt_23, " + " ROUND (NVL (A.ABS_ALE_L1_AMT, 0) + NVL (A.ABS_ALE_L2_AMT, 0),3) abs_ale_amt, " + " ROUND (NVL (A.ABS_OTHER_PAY_L1_AMT, 0) + NVL (A.ABS_OTHER_PAY_L2_AMT, 0),3) abs_other_amt, " + " ROUND (NVL (A.OT_L1_AMT, 0) + NVL (A.OT_L2_AMT, 0), 3) ot_amt, " + " ROUND (NVL (A.ST_L1_AMT, 0) + NVL (A.OST_L1_AMT, 0) + NVL (A.OST_L2_AMT, 0) + NVL (A.ST_L2_AMT, 0), 3) st_amt, " + " ROUND (NVL (A.HT_L1_AMT, 0) + NVL (A.OHT_L1_AMT, 0) + NVL (A.HT_L2_AMT, 0) + NVL (A.OHT_L2_AMT, 0), 3) hol_amt, " + " ROUND (NVL (A.NT_30_L1_AMT, 0) + NVL (A.NT_30_L2_AMT, 0), 3) nt30_amt, " + " ROUND (NVL (A.NT_45_L1_AMT, 0) + NVL (A.NT_45_L2_AMT, 0) + NVL (A.NT_45_2_L1_AMT, 0) + NVL (A.NT_45_2_L2_AMT, 0), 3) nt45_amt, " + " ROUND (NVL (A.NT_60_L1_AMT, 0) + NVL (A.NT_60_L2_AMT, 0), 3) nt60_amt, " + " ROUND (NVL (A.NT_90_L1_AMT, 0) + NVL (A.NT_90_L2_AMT, 0), 3) nt90_amt_34, " + " ROUND (NVL (A.RETURN_AMT, 0), 3) return_amt_36, " + " ROUND (NVL (A.INC_AMT, 0) + NVL (A.BONUS_YEARLY_AMT, 0), 3) inc_amt, " + " ROUND (NVL (A.ALE_STOP_AMT, 0), 3) ale_stop_amt, " + " ROUND (NVL (A.SEVERANCE_AMT, 0), 3) severance_amt, " + " ROUND (NVL (A.GROSS_AMT, 0), 3) gross_amt, " + " ROUND (NVL (A.BREAK_CONTRACT_AMT, 0), 3) break_day_amt, " + " ROUND (NVL (A.SOCIAL_AMT, 0), 3) total_ins_amt, " + " ROUND (NVL (A.HEALTH_AMT, 0), 3) health_amt, " + " ROUND (NVL (A.UNEMP_INS, 0), 3) unemp_amt, " + " ROUND (NVL (A.ADVANCE_AMT, 0), 3) advance_amt, " + " ROUND (NVL (A.EQUIP1_AMT, 0), 3) uniform_amt, " + " ROUND (NVL (A.OT_TAX_L1_AMT, 0) + NVL (A.OT_TAX_L2_AMT, 0), 3) ot_100_amt, " + " ROUND (NVL (A.DEDUCT_PIT, 0), 3) deduct_pit, " + " ROUND (NVL (A.INCOME_BEFORE_TAX, 0), 3) income_amt, " + " ROUND (NVL (A.INCOME_TAX, 0), 3) income_tax, " + " ROUND (NVL (A.UNION_AMT, 0), 3) union_amt, " + " ROUND (NVL (A.RETURN_NO_PIT, 0) , 3) return_no_pit, " + " ROUND (NVL (A.ADVANCE_NO_PIT, 0), 3) advance_no_pit, " + " 0 pit_final, " + " ROUND (NVL (A.NET_AMT, 0), 2) net_amt_46, " + " ROUND (NVL (A.WT_L1, 0) / 8 + NVL (a.HOL_DAY_L1, 0),0) wd_l1, " + " ROUND (NVL (A.ABS_ALE_L1, 0) / 8,0) ale_l1, " + " ROUND (NVL (A.ABS_OTHER_PAY_L1, 0)/8,0) abs_other_l1, " + " ROUND (NVL (A.OT_L1, 0),0) ot_l1, " + " ROUND (NVL (A.ST_L1, 0) + NVL (A.OST_L1, 0),0) st_l1, " + " ROUND (NVL (A.HT_L1, 0) + NVL (A.OHT_L1, 0),0) ht_l1, " + " ROUND (NVL (A.NT_30_L1, 0), 0) , " + " ROUND (NVL (A.NT_45_L1, 0) + NVL (A.NT_45_2_L1, 0), 0) , " + " ROUND (NVL (A.NT_60_L1, 0), 0) , " + " ROUND (NVL (A.NT_90_L1, 0), 0) , " + " ROUND (NVL (A.WT_L2, 0) / 8 + NVL (a.HOL_DAY_L2, 0) , 2) wd_l2, " + " ROUND (NVL (A.ABS_ALE_L2, 0) / 8, 2) ale_l2, " + " ROUND (NVL (A.ABS_OTHER_PAY_L2, 0)/8,3) abs_other_l2, " + " ROUND (NVL (A.OT_L2, 0), 3) ot_l2, " + " ROUND (NVL (A.OST_L2, 0) + NVL (A.ST_L2, 0),3) ot_l2, " + " ROUND (NVL (A.HT_L2, 0) + NVL (A.OHT_L2, 0), 3) ot_hol_52, " + " ROUND (NVL (A.NT_30_L2, 0), 3) nt30, " + " ROUND (NVL (A.NT_45_L2, 0) + NVL (A.NT_45_2_L2, 0), 3) nt45, " + " ROUND (NVL (A.NT_60_L2, 0), 3) nt60, " + " ROUND (NVL (A.NT_90_L2, 0), 3) nt90, " + " ROUND (NVL (A.ALE_STOP, 0), 3) ale_stop, " + " ROUND (NVL (A.SEVERANCE_MONTH, 0), 3) severance_month " + " FROM thr_month_salary a, " + " tco_org b, thr_work_group c " + " WHERE a.del_if = 0 and c.del_if =0 " + " AND a.work_mon = '" + p_work_mon + "' " + " AND b.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 C.WORKGROUP_NM,nvl(b.seq,0),A.DEPT_NM, a.emp_id "; //Response.Write(SQL); //Response.End(); DataTable dt_emp = CtlLib.TableReadOpen(SQL); int irow = dt_emp.Rows.Count; int icol = dt_emp.Columns.Count; if (irow == 0) { Response.Write("There is no data 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"); //Khai bao bien int i, j; int loop_row = 51, stt = 0; int r_allow = 9; string allow_a = ""; double allow_amt = 0; //Insert new row for (i = 1; i < irow; i++) { //exSheet.Range[2, 7, 54, 7].Rows.EntireRow.Insert(); exSheet.Range["A1:G" + loop_row].Copy(exSheet.Range["A" + (loop_row * i + 1) + ":G" + (loop_row + loop_row * i)], XlPasteType.xlPasteAll); for (j = 1; j < loop_row; j++) { exSheet.Range[loop_row * i + j, 1].RowHeight = exSheet.Range[j, 1].RowHeight; } } //Show data for (i = 0; i < irow; i++) { //insert picture if (dt_com.Rows[0]["image"].ToString() != "") { byte[] MyData = new byte[0]; MyData = (byte[])dt_com.Rows[0]["image"]; MemoryStream stream = new MemoryStream(MyData); Bitmap b = new Bitmap(stream); exSheet.Cells[1 + loop_row * i, 1].Select(); exSheet.Shapes.AddPicture(ResizeImage(b, 140, 60)); } stt++; exSheet.Cells["B" + (2 + loop_row * i)].Value = "PAY-SLIP IN " + dt_com.Rows[0][4].ToString(); exSheet.Cells["B" + (3 + loop_row * i)].Value = "PHIẾU LƯƠNG THÁNG " + dt_com.Rows[0][5].ToString(); exSheet.Cells["A" + (4 + loop_row * i)].Value = string.Format("Họ tên: {0}", dt_emp.Rows[i]["full_nm"].ToString()); exSheet.Cells["B" + (5 + loop_row * i)].Value = dt_emp.Rows[i]["emp_id"].ToString(); exSheet.Cells["F" + (5 + loop_row * i)].Value = dt_emp.Rows[i]["dept_nm"].ToString(); exSheet.Cells["B" + (6 + loop_row * i)].Value = dt_emp.Rows[i]["join_dt"].ToString(); exSheet.Cells["D" + (8 + loop_row * i)].Value = double.Parse(dt_emp.Rows[i]["sal_level1_7"].ToString()); exSheet.Cells["F" + (8 + loop_row * i)].Value = double.Parse(dt_emp.Rows[i]["sal_level2"].ToString()); //ten phu cap for (j = 0; j < 12; j++) { exSheet.Cells["A" + (r_allow + j + loop_row * i)].Value = dt_allow.Rows[0][24 + j].ToString(); exSheet.Cells["B" + (r_allow + j + loop_row * i)].Value = string.Format("({0})", dt_allow.Rows[0][0 + j].ToString()); } //cot tien tu dong 9 den dong 47 int k = 6; for (j = 0; j < 43; j++) { if (j != 12) { exSheet.Cells["G" + (9 + j + loop_row * i)].Value = double.Parse(dt_emp.Rows[i][j + k].ToString()); } } //Ngay cong, gio tang ca l1 k += 43; for (j = 0; j < 10; j++) { exSheet.Cells["D" + (22 + j + loop_row * i)].Value = double.Parse(dt_emp.Rows[i][j + k].ToString()); } //Ngay cong, gio tang ca l1 k += 10; for (j = 0; j < 10; j++) { exSheet.Cells["E" + (22 + j + loop_row * i)].Value = double.Parse(dt_emp.Rows[i][j + k].ToString()); } k += 10; exSheet.Cells["D" + (34 + loop_row * i)].Value = double.Parse(dt_emp.Rows[i]["ale_stop"].ToString()); exSheet.Cells["D" + (35 + loop_row * i)].Value = double.Parse(dt_emp.Rows[i]["severance_month"].ToString()); //Insert page break IPageBreaks pbs = exBook.Worksheets[1].HPageBreaks; IRange usedrange = exBook.Worksheets[1].UsedRange; //pbs.Reset(); pbs.Add(usedrange.Rows[loop_row + loop_row * i + 1]); //end insert page beak //Hide allowance row allowance for (j = 0; j < 12; j++) { if (dt_allow.Rows[0][j + 12].ToString() != "1") { exSheet.Range["A" + (r_allow + j + loop_row * i)].Rows.Hidden = true; } } } //Hide row // end loop detail percent if (File.Exists(TempFile)) { File.Delete(TempFile); } // hide row firts //range = exSheet.Range["A1"]; // hide row A5 //range.Rows.Hidden = true; exBook.SaveAs(TempFile); //CtlLib.ExcelToPdf(TempFile); //string pdfFilePath = TempFile.Replace(".xls", ".pdf"); //write out to client broswer System.IO.FileInfo file = new System.IO.FileInfo(TempFile); //System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath); Response.Clear(); Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.UTF8; //Add header, give a default file name for "File Download/Store as" Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); //Add header, set file size to enable browser display download progress Response.AddHeader("Content-Length", file.Length.ToString()); //Set the return string is unavailable reading for client, and must be downloaded Response.ContentType = "application/ms-exSheet"; //Response.ContentType = "application/pdf"; //Send file string to client Response.WriteFile(TempFile); //Response.WriteFile(pdfFilePath); //Stop execute Response.End(); }
protected void Page_Load(object sender, EventArgs e) { CtlLib.SetUser(Session["APP_DBUSER"].ToString()); string l_wgroup = Request.QueryString["l_wgroup"].ToString(); string l_bn_times = Request.QueryString["l_bn_times"].ToString(); string l_tco_org = Request.QueryString["l_tco_org"].ToString(); string l_year = Request.QueryString["l_year"].ToString(); string l_emp_type = Request.QueryString["l_emp_type"].ToString(); string l_nation = Request.QueryString["l_nation"].ToString(); string l_contract = Request.QueryString["l_contract"].ToString(); string p_input = Request.QueryString["l_Temp"].ToString(); string l_j_from = Request.QueryString["l_j_from"].ToString(); string l_j_to = Request.QueryString["l_j_to"].ToString(); string l_l_from = Request.QueryString["l_l_from"].ToString(); string l_l_to = Request.QueryString["l_l_to"].ToString(); string l_type = Request.QueryString["l_type"].ToString(); string l_Senority_from = Request.QueryString["l_Senority_from"].ToString(); string l_Senority_to = Request.QueryString["l_Senority_to"].ToString(); string TemplateFile = "rpt_hrpm00900_0.xls"; string TempFile = "../../../../system/temp/rpt_hrpm00900_0_" + Session["User_ID"].ToString() + ".xls"; TemplateFile = Server.MapPath(TemplateFile); TempFile = Server.MapPath(TempFile); //Create a new workbook IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile); //Add worksheet IWorksheet exSheet = exBook.Worksheets[1]; IRange range; string para = "'" + l_wgroup + "','" + l_bn_times + "','" + l_tco_org + "','" + l_year + "','" + l_emp_type + "','" + l_nation + "','" + l_contract + "','"; para = para + p_input + "','" + l_j_from + "','" + l_j_to + "','" + l_l_from + "','" + l_l_to + "','" + l_type + "','" + l_Senority_from + "','" + l_Senority_to + "'"; DataTable dt_emp = CtlLib.TableReadOpenCursor("ST_HR_rpt_hrpm00900_0", para); if (dt_emp.Rows.Count == 0) { Response.Write("There is no data of bonus amount."); Response.End(); } string SQL = "select A.REMARK " + "from thr_bonus_master a " + "where a.del_if = 0 " + "and A.TIMES = '" + l_bn_times + "' " + "and A.YEAR = '" + l_year + "' "; DataTable dt_remark = CtlLib.TableReadOpen(SQL); //Khai bao bien int pos = 5; int i, j, n_dept = 0, emp_dept = 0; Double[] subtotal = new Double[2] { 0, 0 }; Double[] grandtotal = new Double[2] { 0, 0 }; //Insert new row for (i = 0; i < dt_emp.Rows.Count - 1; i++) { if (dt_emp.Rows[i][1].ToString() != dt_emp.Rows[i + 1][1].ToString()) //khac bo phan { n_dept++; } } for (i = 0; i < dt_emp.Rows.Count + n_dept - 1; i++) { exSheet.Range[pos + 1 + i, 1].Rows.EntireRow.Insert(); } exSheet.Cells[2, 5].Value = "Bonus Year: " + l_year; exSheet.Cells[2, 7].Value = "Times: " + l_bn_times + " - " + dt_remark.Rows[0][0].ToString(); //Show data for (i = 0; i < dt_emp.Rows.Count; i++) { subtotal[0] = subtotal[0] + Double.Parse(dt_emp.Rows[i][6].ToString()); subtotal[1] = subtotal[1] + Double.Parse(dt_emp.Rows[i][8].ToString()); grandtotal[0] = grandtotal[0] + Double.Parse(dt_emp.Rows[i][6].ToString()); grandtotal[1] = grandtotal[1] + Double.Parse(dt_emp.Rows[i][8].ToString()); emp_dept++; exSheet.Cells[pos + i, 1].Value = emp_dept; for (j = 1; j <= 8; j++) { if (j >= 6) { exSheet.Cells[pos + i, j + 1].Value = Double.Parse(dt_emp.Rows[i][j].ToString()); } else { exSheet.Cells[pos + i, j + 1].Value = dt_emp.Rows[i][j].ToString(); } } if (i != dt_emp.Rows.Count - 1 && dt_emp.Rows[i][1].ToString() != dt_emp.Rows[i + 1][1].ToString()) //khac bo phan { exSheet.Cells[pos + i + 1, 2, pos + i + 1, 5].Merge(); exSheet.Cells[pos + i + 1, 2].Value = "Sub Total " + dt_emp.Rows[i][1].ToString() + " - " + emp_dept + " employee(s)"; exSheet.Cells[pos + i + 1, 2, pos + i + 1, 9].Rows.Font.Bold = true; exSheet.Cells[pos + i + 1, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; exSheet.Cells[pos + i + 1, 7].Value = subtotal[0]; exSheet.Cells[pos + i + 1, 9].Value = subtotal[1]; //Insert page break IPageBreaks pbs = exBook.Worksheets[1].HPageBreaks; IRange usedrange = exBook.Worksheets[1].UsedRange; //pbs.Reset(); pbs.Add(usedrange.Rows[pos + i + 2]); subtotal[0] = 0; subtotal[1] = 0; pos = pos + 1; emp_dept = 0; } else if (i == dt_emp.Rows.Count - 1) { exSheet.Cells[pos + i + 1, 2].Value = "Sub Total " + dt_emp.Rows[i][1].ToString() + " - " + emp_dept + " employee(s)"; exSheet.Cells[pos + i + 2, 1].Value = "Grand Total - " + dt_emp.Rows.Count + " employee(s)"; exSheet.Cells[pos + i + 1, 7].Value = subtotal[0]; exSheet.Cells[pos + i + 1, 9].Value = subtotal[1]; exSheet.Cells[pos + i + 2, 7].Value = grandtotal[0]; exSheet.Cells[pos + i + 2, 9].Value = grandtotal[1]; } } // 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_K1_AMT, 0), 10) allow_amt1_9, " + " ROUND (NVL (A.ALLOW_K2_AMT, 0), 10) allow_amt2_10, " + " ROUND (NVL (A.ALLOW_K3_AMT, 0), 10) allow_amt3_11, " + " ROUND (NVL (A.ALLOW_K4_AMT, 0), 10) allow_amt4_12, " + " ROUND (NVL (A.ALLOW_K5_AMT, 0), 10) allow_amt5_13, " + " ROUND (NVL (A.ALLOW_K6_AMT, 0), 10) allow_amt6_14, " + " ROUND (NVL (A.ALLOW_K7_AMT, 0), 10) allow_amt7_15, " + " ROUND (NVL (A.ALLOW_K8_AMT, 0), 10) allow_amt8_16, " + " ROUND (NVL (A.ALLOW_K9_AMT, 0), 10) allow_amt1_9, " + " ROUND (NVL (A.ALLOW_K10_AMT, 0), 10) allow_amt1_9, " + " ROUND (NVL (A.ALLOW_K11_AMT, 0), 10) allow_amt1_9, " + " ROUND (NVL (A.ALLOW_K12_AMT, 0), 10) allow_amt1_9, " + " ROUND (NVL (A.TOTAL_ALLOW_K_AMT, 0) , 10) total_allow, " + " ROUND ((NVL (A.WT_L1, 0) + NVL (a.HOL_DAY_L1, 0)) / 8 , 10) wd1_17, " + " ROUND ((NVL (A.WT_L2, 0) + NVL (a.HOL_DAY_L2, 0)) / 8 , 10) wd2_18, " + " ROUND (NVL (A.WT_L1_AMT, 0) + NVL (A.HOL_DAY_L1_AMT, 0) , 10) wd1_17, " + " ROUND (NVL (A.WT_L2_AMT, 0) + NVL (A.HOL_DAY_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, " + " ROUND (NVL (A.TOTAL_WT_HT_ABS_L1_AMT, 0) + NVL (A.TOTAL_WT_HT_ABS_L2_AMT, 0) , 10) sal_wd_23, " + " 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) + NVL(A.OST_L1, 0), 10) st_l1_24, " + " ROUND (NVL (A.ST_L2, 0) + NVL(A.OST_L2, 0), 10) st_l2_25, " + " ROUND (NVL (A.ST_L1_amt, 0) + NVL(A.OST_L1_AMT, 0), 10) st_l1_amt_24, " + " ROUND (NVL (A.ST_L2_amt, 0) + NVL(A.OST_L2_AMT, 0), 10) st_l2_amt_25, " + " ROUND (NVL (A.HT_L1, 0) + NVL(A.OHT_L1, 0), 10) ht_l1_24, " + " ROUND (NVL (A.HT_L2, 0) + NVL(A.OHT_L2, 0), 10) ht_l2_25, " + " ROUND (NVL (A.HT_L1_amt, 0) + NVL(A.OHT_L1_AMT, 0), 10) ht_l1_amt_24, " + " ROUND (NVL (A.HT_L2_amt, 0) + NVL(A.OHT_L2_AMT, 0), 10) ht_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_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.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.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_TAX_AMT1, 0), 10) return_amt_46, " + " ROUND (NVL (A.RETURN_TAX_AMT2, 0), 10) return_amt_46, " + " ROUND (NVL (A.RETURN_TAX_AMT3, 0), 10) return_amt_46, " + " ROUND (NVL (A.RETURN_TAX_AMT4, 0), 10) return_amt_46, " + " ROUND (NVL (A.RETURN_TAX_AMT5, 0), 10) return_amt_46, " + " 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.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.BREAK_CONTRACT_AMT, 0), 10) break_con_amt_53, " + " 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.health_card_amt, 0), 10) break_con_amt_53, " + " ROUND (NVL (A.DEDUCT_NOTAX_AMT1, 0), 10) equip1_amt_66, " + " ROUND (NVL (A.DEDUCT_NOTAX_AMT2, 0), 10) equip2_amt_67, " + " ROUND (NVL (A.DEDUCT_NOTAX_AMT3, 0), 10) equip3_amt_68, " + " ROUND (NVL (A.DEDUCT_NOTAX_AMT4, 0), 10) equip4_amt_69, " + " ROUND (NVL (A.DEDUCT_NOTAX_AMT5, 0), 10) equip5_amt_70, " + " ROUND (NVL (A.TOTAL_DEDUCT_NOTAX_AMT, 0), 10) equip6_amt_71, " + " ROUND (NVL (A.RETURN_NOTAX_AMT1, 0), 10) equip7_amt_72, " + " ROUND (NVL (A.RETURN_NOTAX_AMT2, 0), 10) equip8_amt_73, " + " ROUND (NVL (A.RETURN_NOTAX_AMT3, 0), 10) equip8_amt_73, " + " ROUND (NVL (A.RETURN_NOTAX_AMT4, 0), 10) equip8_amt_73, " + " ROUND (NVL (A.RETURN_NOTAX_AMT5, 0), 10) RETURN_NOTAX_AMT5, " + " ROUND (NVL (A.TOTAL_RETURN_NOTAX_AMT, 0), 10) TOTAL_RETURN_NOTAX_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, " + " '' SIGNATURE, " + " 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 " + " 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"); para_com = "'" + p_work_mon + "'"; DataTable dt_adj = CtlLib.TableReadOpenCursor("ST_HR_RPT_ADJUST_SALARY", para_com); 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, i_return_tax, i_deduct_notax, i_return_notax; int pos = 7; int allow = 11, c_return_tax = 67, c_deduct_notax = 87, c_return_notax = 93, allow_k = 24, equip = 125; //vi tri phu cap int pos_nt = 49; 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("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("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()); 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_return_tax = 0; i_return_tax < 5; i_return_tax++) { if (dt_adj.Rows[0][i_return_tax].ToString() != "") { exSheet.Cells[4, c_return_tax + i_return_tax].Value = dt_adj.Rows[0][i_return_tax].ToString(); } } //deduct notax for (i_deduct_notax = 0; i_deduct_notax < 5; i_deduct_notax++) { if (dt_adj.Rows[0][i_deduct_notax + 5].ToString() != "") { exSheet.Cells[4, c_deduct_notax + i_deduct_notax].Value = dt_adj.Rows[0][i_deduct_notax + 5].ToString(); } } //return notax for (i_return_notax = 0; i_return_notax < 5; i_return_notax++) { if (dt_adj.Rows[0][i_return_notax + 10].ToString() != "") { exSheet.Cells[4, c_return_notax + i_return_notax].Value = dt_adj.Rows[0][i_return_notax + 10].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 (IsNumeric(dt_emp.Rows[i][j].ToString()) && j != 3) { exSheet.Cells[pos + i, j].Value = double.Parse(dt_emp.Rows[i][j].ToString()); } else { exSheet.Cells[pos + i, j].Value = 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; } } //Hide adj return tax for (i_return_tax = 0; i_return_tax < 5; i_return_tax++) { if (dt_adj.Rows[0][i_return_tax].ToString() == "") { exSheet.Range[1, c_return_tax + i_return_tax].Columns.Hidden = true; } } //Hide adj deduct notax for (i_deduct_notax = 0; i_deduct_notax < 5; i_deduct_notax++) { if (dt_adj.Rows[0][i_deduct_notax + 5].ToString() == "") { exSheet.Range[1, c_deduct_notax + i_deduct_notax].Columns.Hidden = true; } } //Hide adj return notax for (i_deduct_notax = 0; i_deduct_notax < 5; i_deduct_notax++) { if (dt_adj.Rows[0][i_deduct_notax + 5].ToString() == "") { exSheet.Range[1, c_deduct_notax + i_deduct_notax].Columns.Hidden = true; } } //Hide adj return notax for (i_return_notax = 0; i_return_notax < 5; i_return_notax++) { if (dt_adj.Rows[0][i_return_notax + 10].ToString() == "") { exSheet.Range[1, c_return_notax + i_return_notax].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()); string l_group = Request.QueryString["l_group"].ToString(); string l_pay_type = Request.QueryString["l_pay_type"].ToString(); string l_m_type = Request.QueryString["l_m_type"].ToString(); string l_bn_times = Request.QueryString["l_bn_times"].ToString(); string l_emp_id = Request.QueryString["l_emp_id"].ToString(); string l_dept = Request.QueryString["l_dept"].ToString(); string l_year = Request.QueryString["l_year"].ToString(); string TemplateFile = "rpt_hrpm00700_12.xls"; string TempFile = "../../../../system/temp/rpt_hrpm00700_12_" + Session["User_ID"].ToString() + ".xls"; TemplateFile = Server.MapPath(TemplateFile); TempFile = Server.MapPath(TempFile); //Create a new workbook IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile); //Add worksheet IWorksheet exSheet = exBook.Worksheets[1]; IRange range; string SQL = " SELECT '', " + " '', " + " org.org_nm dept_nm, " + " te.EMP_ID, " + " te.FULL_NAME, " + " TO_CHAR (TO_DATE (te.JOIN_DT, 'yyyymmdd'), 'dd-mm-yyyy') join_dt, " + " ROUND (NVL (tb.SENIORITY, 0), 2), " + " ROUND (NVL (tb.SALARY, 0), 2), " + " ROUND (NVL (tb.RATIO, 0), 2), " + " CASE " + " WHEN tb.MONEY_KIND = '02' " + " THEN " + " ROUND (tb.BN_AMT * NVL (tb.BN_EXT_RATE, 0), 3) " + " ELSE " + " ROUND (NVL (tb.BN_AMT, 0), 0) " + " END " + " AS bonus_amt " + " FROM thr_bonus tb, " + " thr_work_group tg, " + " thr_employee te, " + " tco_org org " + " WHERE tb.DEL_IF = 0 " + " AND org.del_if = 0 " + " AND org.pk = te.tco_org_pk " + " AND NVL (tb.BN_AMT, 0) > 0 " + " AND te.DEL_IF = 0 " + " AND tg.DEL_IF = 0 " + " AND tb.THR_EMP_PK = te.pk " + " AND tg.PK = te.THR_wG_PK " + " AND tb.BN_YEAR = '" + l_year + "' " + " and decode('" + l_group + "','ALL','" + l_group + "',te.THR_wg_PK)='" + l_group + "' " + " and decode('" + l_pay_type + "','ALL','" + l_pay_type + "',tb.PAY_TYPE)='" + l_pay_type + "' " + " and decode('" + l_m_type + "','ALL','" + l_m_type + "',tb.MONEY_KIND)='" + l_m_type + "' " + " and decode('" + l_bn_times + "','ALL','" + l_bn_times + "',tb.BN_TIMES)='" + l_bn_times + "' " + " and (te.EMP_ID like '%'||'" + l_emp_id + "'||'%' or '" + l_emp_id + "' is null) " + " and (te.TCO_org_PK in ( " + " SELECT g.pk " + " FROM tco_org g " + " WHERE g.del_if = 0 " + " START WITH g.pk = " + " DECODE ('" + l_dept + "', " + " 'ALL', 0, " + " '" + l_dept + "' " + " ) " + " CONNECT BY PRIOR g.pk = g.p_pk) " + " OR '" + l_dept + "' = 'ALL') " + "ORDER BY org.org_nm, te.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(); } //Khai bao bien int i, j; int pos = 5; int emp_dept = 0, n_dept = 0; double sub_total1 = 0, sub_total2 = 0, grand_total1 = 0, grand_total2 = 0; //-----header--- exSheet.Cells["F2"].Value = "Bonus Year: " + l_year; exSheet.Cells["H2"].Value = "Times " + l_bn_times; for (i = 0; i < irow_emp - 1; i++) { j = i + 1; if (dt_emp.Rows[i]["dept_nm"].ToString() != dt_emp.Rows[j]["dept_nm"].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++) { sub_total1 = sub_total1 + double.Parse(dt_emp.Rows[i][7].ToString()); sub_total2 = sub_total2 + double.Parse(dt_emp.Rows[i][9].ToString()); grand_total1 = grand_total1 + double.Parse(dt_emp.Rows[i][7].ToString()); grand_total2 = grand_total2 + double.Parse(dt_emp.Rows[i][9].ToString()); emp_dept++; exSheet.Cells[pos + i, 1].Value = emp_dept; for (j = 2; j < icol_emp; j++) { if (j <= 5) { 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_nm"].ToString() != dt_emp.Rows[i + 1]["dept_nm"].ToString())) { exSheet.Cells[pos + i + 1, 1, pos + i + 1, 5].Merge(); exSheet.Cells[pos + i + 1, 1].Value = "Sub 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; exSheet.Cells[pos + i + 1, 1, pos + i + 1, icol_emp - 1].Interior.Color = Color.FromArgb(204, 255, 204); //to mau cho sub exSheet.Cells[pos + i + 1, 7].Value = sub_total1; exSheet.Cells[pos + i + 1, 9].Value = sub_total2; sub_total1 = sub_total2 = 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].Value = "Total " + dt_emp.Rows[i]["dept_nm"].ToString() + ": " + emp_dept + " employee(s)"; exSheet.Cells[pos + i + 2, 1].Value = "Grant Total: " + irow_emp + " employee(s)"; exSheet.Cells[pos + i + 1, 7].Value = sub_total1; exSheet.Cells[pos + i + 1, 9].Value = sub_total2; exSheet.Cells[pos + i + 2, 7].Value = grand_total1; exSheet.Cells[pos + i + 2, 9].Value = grand_total2; } } // 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(); }