protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
//string l_pk = Request["master_pk"];
string TemplateFile = "rpt_bisc00020_O_4.xls";
string TempFile = "../../../../system/temp/rpt_bisc00020_O_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 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_wh"];
string p_use_yn = Request["p_use_yn"];
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_use_yn + "','" + p_item_group_pk + "','" + p_item + "','" + p_in_out_yn + "'";
DataTable dt = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_O_4", "'" + Session["User_ID"].ToString() + "'");
DataTable dt1 = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00020_O_1", 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]["begin_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]["begin_qty"].ToString() != "")
{
exSheet.Cells[8 + l_row, 5].Value = CommondLib.ConvertToDbl(dt1.Rows[l_row]["begin_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 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();
exSheet.Cells[17, 5].Value = double.Parse(dt.Rows[0]["total_due_vnd_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
exSheet.Cells[19, 5].Value = int.Parse(dt.Rows[0]["svc_amt"].ToString());
exSheet.Cells[19, 7].Value = float.Parse(dt.Rows[0]["svc_amt_usd"].ToString());
exSheet.Cells[20, 5].Value = int.Parse(dt.Rows[0]["total_vnd"].ToString());
exSheet.Cells[20, 7].Value = float.Parse(dt.Rows[0]["total_usd"].ToString());
exSheet.Cells[21, 5].Value = int.Parse(dt.Rows[0]["vat_amt"].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());
exSheet.Cells[23, 5].Value = int.Parse(dt.Rows[0]["total_due_end_vnd"].ToString());
exSheet.Cells[23, 7].Value = float.Parse(dt.Rows[0]["total_due_usd"].ToString());
exSheet.Cells[24, 3].Value = int.Parse(dt.Rows[0]["cash_vnd"].ToString());
exSheet.Cells[24, 5].Value = float.Parse(dt.Rows[0]["cash_usd"].ToString());
exSheet.Cells[25, 3].Value = int.Parse(dt.Rows[0]["credit_vnd"].ToString());
exSheet.Cells[25, 5].Value = float.Parse(dt.Rows[0]["credit_usd"].ToString());
if (int.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 (int.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(Session["APP_DBUSER"].ToString());
string TemplateFile = "rpt_60250300_vat_invoice.xls";
string TempFile = "../../../../system/temp/rpt_60250300_vat_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 p_tac_crca_pk = Request.QueryString["p_tac_crca_pk"];
string para = "'" + p_tac_crca_pk + "'";
DataTable dtMaster = CtlLib.TableReadOpenCursor("ac_rpt_60250300_vatinvoice_mst", para);
DataTable dtDetail = CtlLib.TableReadOpenCursor("ac_rpt_60250300_vatinvoice_dtl", para);
//set master data
//day
exSheet.Cells["D13"].Value = dtMaster.Rows[0]["dd"].ToString();
//month
exSheet.Cells["E13"].Value = dtMaster.Rows[0]["mm"].ToString();
//year
exSheet.Cells["G13"].Value = dtMaster.Rows[0]["yyyy"].ToString();
//guest name
exSheet.Cells["D14"].Value = dtMaster.Rows[0]["recv_person"].ToString();
//customer name
exSheet.Cells["D15"].Value = dtMaster.Rows[0]["customer_name"].ToString();
//tax code
exSheet.Cells["D16"].Value = dtMaster.Rows[0]["taxcode"].ToString();
//customer address
exSheet.Cells["C17"].Value = dtMaster.Rows[0]["addr1"].ToString();
//pay_method
exSheet.Cells["D18"].Value = dtMaster.Rows[0]["pay_method"].ToString();
//sub net total
exSheet.Cells["H33"].Value = double.Parse(dtMaster.Rows[0]["net_amt"].ToString());
//service charge amt
exSheet.Cells["H34"].Value = double.Parse(dtMaster.Rows[0]["svc_amt"].ToString());
//vat amt
exSheet.Cells["H35"].Value = double.Parse(dtMaster.Rows[0]["vat_amt"].ToString());
//total amt
exSheet.Cells["H36"].Value = double.Parse(dtMaster.Rows[0]["total_amt"].ToString());
//VAT RATE
exSheet.Cells["HC5"].Value = 10;
//number to VN words
exSheet.Cells["A39"].Value = CommondLib.Num2VNText(dtMaster.Rows[0]["total_amt"].ToString(), "VND");
//set detail data
int l_pos = 22;
int i = 0;
for (i = 0; i < dtDetail.Rows.Count; i++)
{
//exSheet.Cells[l_pos+i,1].Value =i+1;
exSheet.Cells[l_pos + i, 2].Value = dtDetail.Rows[i]["item_name"].ToString(); //item name
//exSheet.Cells[l_pos+i,4].Value = dtDetail.Rows[i]["item_uom"].ToString(); //unit
//exSheet.Cells[l_pos+i,5].Value = dtDetail.Rows[i]["qty"].ToString(); //qty
//exSheet.Cells[l_pos+i,6].Value = dtDetail.Rows[i]["u_price"].ToString(); //price
exSheet.Cells[l_pos + i, 8].Value = double.Parse(dtDetail.Rows[i]["amt"].ToString()); //net amt
}
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());
//CtlLib.SetUser("hr");
string p_mon = Request["p_mon"].ToString();
string p_reg_type = Request.QueryString["p_reg_type"].ToString();
string p_org_pk = Request.QueryString["p_org"].ToString();
string TemplateFile = "rpt_hris00600_0.xls";
string TempFile = "../../../system/temp/rpt_hris00600_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];
string SQL
= "select code,v.CODE_NM " +
"from vhr_hr_code v " +
"where v.ID='HR0049' and nvl(v.tco_company_pk,'" + p_org_pk + "')=" + "(select a.tco_company_pk from tco_org a where a.del_if=0 and a.pk='" + p_org_pk + "')";
//Response.Write(SQL);
//Response.End();
DataTable dt_com = CtlLib.TableReadOpen(SQL);
SQL
= " SELECT UPPER((select F.CODE_FNM from vhr_hr_code f where F.ID = 'HR0054' and F.CODE = V.CHAR_5)) title ,v.CODE_NM, " +
" ti.FULL_NAME, " +
" case when E.SEX = 'M' then substr(E.BIRTH_DT,1,4) else '' end BIRTH_DT_M, " +
" case when E.SEX = 'F' then substr(E.BIRTH_DT,1,4) else '' end BIRTH_DT_F, " +
" ti.SOCIAL_NO, " +
" (case when TI.INS_REG_TYPE='02' and TI.DETAIL_INS_REG_TYPE='07' then (CASE WHEN ti.ST_SOCIAL_DT IS NOT NULL THEN (case when months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')) >12 then '0-12' " +
" else 0 || '-'||months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')) end) ELSE NULL END) " +
" else (CASE WHEN ti.ST_SOCIAL_DT IS NOT NULL THEN TRUNC(months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')) / 12) ||'-' || MOD(months_between(TO_DATE (ti.INS_MONTH, 'yyyymm'),TO_DATE (ti.ST_SOCIAL_DT, 'yyyymm')),12) ELSE NULL END) " +
" end) tg_thamgia, " +
" ROUND (NVL (ti.AVERAGE_SALARY, 0), 0), " +
" ti.CONDITION, " +
" ti.THOI_DIEM, " +
" TO_CHAR (TO_DATE (ti.FROM_DT, 'yyyymmdd'), 'dd/mm/yyyy') from_dt, " +
" TO_CHAR (TO_DATE (ti.TO_DT, 'yyyymmdd'), 'dd/mm/yyyy') to_dt, " +
" ROUND (NVL (ti.DAYS, 0), 2), " +
" ROUND (NVL (ti.PROGRESSIVE, 0), 0), " +
" ROUND (ti.INS_AMT, -2),'', " +
" e.emp_id " +
" FROM THR_INS_REGULATION ti, vhr_hr_code v, thr_employee e " +
" WHERE ti.DEL_IF = 0 " +
" AND e.del_if = 0 " +
" AND e.pk = ti.thr_emp_pk " +
" AND ti.DEL_IF = 0 " +
" AND ti.INS_MONTH = '" + p_mon + "' " +
" and decode('" + p_reg_type + "','ALL','ALL',ti.INS_REG_TYPE)='" + p_reg_type + "' " +
" AND v.ID = 'HR0055' " +
" AND v.CODE = ti.DETAIL_INS_REG_TYPE " +
"ORDER BY ( (SELECT F.CODE " +
" FROM vhr_hr_code f " +
" WHERE F.ID = 'HR0054' AND F.CODE = V.CHAR_5)), NVL (v.num_1, 0), " +
" v.code_nm, " +
" e.emp_id,ti.FULL_NAME, " +
" ti.from_dt ";
DataTable dt_emp = CtlLib.TableReadOpen(SQL);
int irow = dt_emp.Rows.Count;
int icol = dt_emp.Columns.Count;
//Response.Write(SQL);
//Response.End();
if (dt_emp.Rows.Count == 0)
{
Response.Write("There is no data to show");
Response.End();
}
//Khai bao bien
int i, j = 0, k = 0, pos = 20;
int count = 0;
string[] atype = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K" };
string[] btype = new string[] { "I", "II", "III", "IV", "V", "VI", "VII", "VIII", "IX", "X", "XI" };
Double total_day = 0, total_prog = 0, total_amt = 0, grand_total = 0;
int a = 0, b = 0;
string quy = "";
int month;
//Insert new row
for (i = 0; i < irow - 1; i++)
{
if (dt_emp.Rows[i][0].ToString() != dt_emp.Rows[i + 1][0].ToString())
{
a++;
}
if (dt_emp.Rows[i][1].ToString() != dt_emp.Rows[i + 1][1].ToString())
{
b++;
}
}
for (i = 0; i < irow + a + b; i++)
{
exSheet.Range[pos + 1, 1].Rows.EntireRow.Insert();
}
month = int.Parse(p_mon.Substring(4, 2).ToString());
if (month >= 1 && month <= 3)
{
quy = "01";
}
else if (month >= 4 && month <= 6)
{
quy = "02";
}
else if (month >= 7 && month <= 9)
{
quy = "03";
}
else
{
quy = "04";
}
exSheet.Cells["A1"].Value = "Tên cơ quan (đơn vị): " + dt_com.Rows[0][1].ToString();
exSheet.Cells["A2"].Value = "Mã đơn vị: " + dt_com.Rows[1][1].ToString();
exSheet.Cells["A3"].Value = "Điện thoại: " + dt_com.Rows[12][1].ToString();
exSheet.Cells["A6"].Value = "Tháng " + p_mon.Substring(4, 2).ToString() + " quý " + quy + " năm " + p_mon.Substring(0, 4).ToString();
exSheet.Cells["A7"].Value = "Số hiệu tài khoản: " + dt_com.Rows[8][1].ToString() + " mở tại: " + dt_com.Rows[9][1].ToString();
//Show data
for (i = 0; i < irow; i++)
{
//total_day = total_day + Double.Parse(dt_emp.Rows[i][12].ToString());
//total_prog = total_prog + Double.Parse(dt_emp.Rows[i][13].ToString());
total_amt = total_amt + Double.Parse(dt_emp.Rows[i][14].ToString());
grand_total = grand_total + Double.Parse(dt_emp.Rows[i][14].ToString());
//Dong dau tien
if (i == 0)
{
exSheet.Cells[pos + i - 2, 1].Value = atype[j].ToString();
exSheet.Cells[pos + i - 1, 1].Value = btype[k].ToString();
exSheet.Cells[pos + i - 2, 2].Value = dt_emp.Rows[i][0].ToString();
exSheet.Cells[pos + i - 1, 2].Value = dt_emp.Rows[i][1].ToString();
k++;
}
count++;
exSheet.Cells[pos + i, 1].Value = count;
for (int col = 2; col < icol; col++)
{
if (col == 7 || (col >= 12 && col <= 14))
{
exSheet.Cells[pos + i, col].Value = Double.Parse(dt_emp.Rows[i][col].ToString());
}
else
{
exSheet.Cells[pos + i, col].Value = dt_emp.Rows[i][col].ToString();
}
}
if (i == irow - 1) //Dong cuoi cung
{
exSheet.Cells[pos + i + 1, 2].Value = "Tổng cộng ";
//exSheet.Cells[pos + i + 1, 13].Value = total_day;
//exSheet.Cells[pos + i + 1, 14].Value = total_prog;
exSheet.Cells[pos + i + 1, 14].Value = total_amt;
exSheet.Cells[pos + i + 1, 1, pos + i + 1, 15].Rows.Font.Bold = true;
pos = pos + 1;
total_day = 0;
total_prog = 0;
total_amt = 0;
}
else
{
if (dt_emp.Rows[i][0].ToString() != dt_emp.Rows[i + 1][0].ToString())
{
exSheet.Cells[pos + i + 1, 2].Value = "Tổng cộng ";
//exSheet.Cells[pos + i + 1, 13].Value = total_day;
//exSheet.Cells[pos + i + 1, 14].Value = total_prog;
exSheet.Cells[pos + i + 1, 14].Value = total_amt;
exSheet.Cells[pos + i + 1, 1, pos + i + 1, 15].Rows.Font.Bold = true;
j++;
exSheet.Cells[pos + i + 2, 1].Value = atype[j].ToString();
exSheet.Cells[pos + i + 2, 2].Value = dt_emp.Rows[i + 1][0].ToString();
exSheet.Cells[pos + i + 2, 1, pos + i + 1, 11].Rows.Font.Bold = true;
pos = pos + 2;
k = 0;
count = 0;
total_day = 0;
total_prog = 0;
total_amt = 0;
}
if (dt_emp.Rows[i][1].ToString() != dt_emp.Rows[i + 1][1].ToString())
{
exSheet.Cells[pos + i + 1, 1].Value = btype[k].ToString();
exSheet.Cells[pos + i + 1, 2].Value = dt_emp.Rows[i + 1][1].ToString();
exSheet.Cells[pos + i + 1, 1, pos + i + 1, 11].Rows.Font.Bold = true;
pos = pos + 1;
k++;
count = 0;
}
}
}
pos = 20 + irow + a + b + 20; //cộng thêm phần điều chỉnh
exSheet.Cells["F" + (pos + 3)].Value = grand_total;
exSheet.Cells["C" + (pos + 6)].Value = "(" + string.Format("Viết bằng chữ: {0}", CommondLib.Num2VNText(grand_total.ToString(), "VND")) + ")";
exSheet.Cells["K" + (pos + 8)].Value = string.Format("Ngày {0} tháng {1} năm {2}", DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year);
exSheet.Cells["a" + (pos + 15)].Value = dt_com.Rows[5][1].ToString();
exSheet.Cells["g" + (pos + 15)].Value = dt_com.Rows[6][1].ToString();
exSheet.Cells["k" + (pos + 15)].Value = dt_com.Rows[7][1].ToString();
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
//range = exSheet.Range["A1"];
// hide row A5
//range.Rows.Hidden = true;
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
//string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}