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