public void ExecuteSQLExtension(HttpContext ctx)
{
string connString = ConfigurationManager.AppSettings["strConn"];
connString += "User Id=" + _dbuser + ";Password="******"|!" };
string procName;
XmlTextWriter xw = new XmlTextWriter(ctx.Response.OutputStream, new System.Text.UTF8Encoding(true));
XmlNodeList xinputs;
OracleConnection connection = new OracleConnection(connString);
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleTransaction transaction;
transaction = connection.BeginTransaction();
command.Transaction = transaction;
try
{
if (_dsoType == "process")
{
//get input parameter listt
xinputs = _clientXMLDoc.SelectNodes("//input");
procName = "Call " + _procedure + "( ";
_exeStatement = procName;
_parameter = xinputs[0].InnerText;
parameter = _parameter.Split(CDelimeter, System.StringSplitOptions.None);
command.CommandText = "select data from esys.tes_dxf_file where pk = '" + parameter[0] + "'";
//SaveLogData();
//string s = (string) command.ExecuteOracleScalar();
OracleDataReader reader = command.ExecuteReader();
reader.Read();
OracleLob blob = reader.GetOracleLob(0);
StreamReader myStreamReader = new StreamReader(blob, System.Text.Encoding.Default);
string s = myStreamReader.ReadToEnd();
string file_name = "temp/tmp" + DateTime.Now.ToString("yymmdd-hh24miss") + ".dxf";
CtlLib.EmptyFolder("temp");
CtlLib.CreateFile(file_name, s);
_p_rtn_value = file_name;
}
transaction.Commit();
ExecuteSQLSelect(ctx);
}
catch (Exception ex)
{
transaction.Rollback();
_errorno = "1";
_errormsg = ex.Message;
SaveLogData();
BuildErrorXML(xw);
}
finally
{
command.Dispose();
connection.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string connString = ConfigurationManager.AppSettings["strConn"];
connString += "User Id=Ctl;Password=Ctl2";
OracleConnection connection = new OracleConnection(connString);
connection.Open();
OracleCommand command = connection.CreateCommand();
string login_user = Session["User_ID"].ToString();
command.CommandText = "call Ctl.sp_upd_user_logout('" + login_user + "')";
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
CtlLib.WriteLogError(ex.Message);
}
Session.Abandon();
Response.Redirect("../Login.aspx");
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser("sale");
string p_from_dt = Request["p_from_dt"];
string p_to_dt = Request["p_to_dt"];
string p_item = Request["p_item"];
string p_item_type = Request["p_item_type"];
string TemplateFile = "rpt_dsbs00230_2.xls";
string TempFile = "../../../../system/temp/rpt_dsbs00230_2_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
string para = "'" + p_from_dt + "','" + p_to_dt + "','" + p_item + "','" + p_item_type + "'";
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_DSBS00230_3", para);
if (dtDetail.Rows.Count == 0)
{
Response.Write("No Data"); Response.End();
}
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A5"].Rows.EntireRow.Insert();//insert row new of sheet
}
int pos = 4;
for (int l_row = 0; l_row < dtDetail.Rows.Count; l_row++)
{
exSheet.Cells[pos + l_row, 1].Value = dtDetail.Rows[l_row]["item_code"].ToString();
exSheet.Cells[pos + l_row, 2].Value = dtDetail.Rows[l_row]["item_name"].ToString();
exSheet.Cells[pos + l_row, 3].Value = dtDetail.Rows[l_row]["out_uom"].ToString();
exSheet.Cells[pos + l_row, 4].Value = dtDetail.Rows[l_row]["out_qty"];
exSheet.Cells[pos + l_row, 5].Value = dtDetail.Rows[l_row]["item_amount"];
exSheet.Cells[pos + l_row, 6].Value = dtDetail.Rows[l_row]["tax_amount"];
exSheet.Cells[pos + l_row, 7].Value = dtDetail.Rows[l_row]["total_amount"];
exSheet.Cells[pos + l_row, 8].Value = dtDetail.Rows[l_row]["ccy_unit"].ToString();
}
exSheet.Cells[dtDetail.Rows.Count + 4, 5].Value = "=SUM(E4:E" + (dtDetail.Rows.Count + 3) + ")";
exSheet.Cells[dtDetail.Rows.Count + 4, 6].Value = "=SUM(F4:F" + (dtDetail.Rows.Count + 3) + ")";
exSheet.Cells[dtDetail.Rows.Count + 4, 7].Value = "=SUM(G4:G" + (dtDetail.Rows.Count + 3) + ")";
// end loop detail not percent
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
//string 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)
{
if (!Page.IsPostBack)
{
string sqlText = "";
try
{
string img_pk = Request.QueryString["img_pk"];
string table_name = Request.QueryString["table_name"];
if (img_pk == null || img_pk == "" || img_pk == "0")
{
img_pk = "-1";//show default image
}
if (table_name.IndexOf(".") > 1)
{
dbuser = table_name.Substring(0, table_name.IndexOf("."));
//get database pass by database user name
conString = ConfigurationManager.AppSettings["strConn"];
conString += "User Id=GASP;password=gasp2";
connection = new OracleConnection(conString);
command = new OracleCommand();
command.CommandText = "Call GASP.sp_sel_db_pass2('" + dbuser + "', :p_rtn_value)";
command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output;
connection.Open();
command.Connection = connection;
OracleDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
dbpass = reader["dbuser_pwd"].ToString();
}
conString = ConfigurationManager.AppSettings["strConn"];
conString += "User Id=" + dbuser + ";password="******"select * from " + table_name + " where pk =" + img_pk;
}
}
else
{
dbuser = Session["APP_DBUSER"].ToString();
dbpass = Session["APP_DBPASS"].ToString();
conString = ConfigurationManager.AppSettings["strConn"];
conString += "User Id=" + dbuser + ";password="******"select * from " + dbuser + "." + table_name + " where pk =" + img_pk;
}
connection = new OracleConnection(conString);
connection.Open();
command = connection.CreateCommand();
command.CommandText = sqlText;
OracleDataReader dr = command.ExecuteReader();
byte[] fileData = null;
string contenttype = "";
string filename = "";
if (dr.Read())
{
filename = dr["filename"].ToString();
contenttype = dr["CONTENTTYPE"].ToString();
fileData = (byte[])dr["DATA"];
}
connection.Close();
Response.ClearContent();
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
BinaryWriter bw = new BinaryWriter(Response.OutputStream);
bw.Write(fileData);
bw.Close();
Response.ContentType = contenttype;
Response.End();
}
catch (Exception ex)
{
CtlLib.WriteLogError(ex.Message + ". " + sqlText);
throw (ex);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string l_room_no_guest_group = Request["p_room"];
string l_report_type = Request["p_tamtru"];
string TemplateFile = "rpt_60300020_PA18.xml";
string TempFile = "../../../../system/temp/rpt_60300020_PA18_" + Session["User_ID"].ToString() + ".xml";
//string TempFile = "../../../../system/temp/rpt_60300020_PA18" + ".xml";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
//IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
//IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string para = "";
DataTable dt, dtSum;
para = "'" + l_room_no_guest_group + "','" + l_report_type + "'";
dt = CtlLib.TableReadOpenCursor("ht_rpt_60250030_PA18", para);
//-----header---
//---end--header---
//--set-----DataBind--in---Master---
/*if (dt.Rows.Count == 0)
* {
* exSheet.Cells[3, 3].Value = "There is not data!";
*
* }
* if (dt.Rows.Count > 0)
* {
* exSheet.Cells[3, 6].Value = dt.Rows[0]["print_time"].ToString();
* }*/
//----------------------
////end-set-----DataBind--in---Master---
int l_row_first = 5, l_cnt = dt.Rows.Count;
string v_all_data_to_xml = "";
/*for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
* {
* exSheet.Range["A6"].Rows.EntireRow.Insert();//insert row new of sheet
* }*/
for (int l_row = 0; l_row < dt.Rows.Count; l_row++)
{
if (dt.Rows[l_row]["col1"].ToString() != "")
{
v_all_data_to_xml = v_all_data_to_xml + dt.Rows[l_row]["col1"].ToString();
}
}
//==============================
StreamWriter log;
if (!File.Exists(TempFile))
{
File.WriteAllText(TempFile, String.Empty);
//log = new StreamWriter(TempFile);
}
/*else
* {
* //File.AppendAllText("c:\\file.txt","Your Text");
* log = File.AppendText(TempFile);
* }*/
//TempFile = "../../../../system/temp/rpt_60300020_PA18.xml";
// Write to the file:
//log.WriteLine(DateTime.Now);
log = new StreamWriter(TempFile);
log.WriteLine(v_all_data_to_xml);
log.WriteLine();
// Close the stream:
log.Close();
//==============================
// 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
//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/xml";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string p_dept, 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_1.xls";
string TempFile = "../../../../system/temp/rpt_hrpm00700_1_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
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, " +
" A.FULL_NAME, " +
" to_char(to_date(A.JOIN_DT,'yyyymmdd'),'dd/mm/yyyy') join_dt, " +
" to_char(to_date(A.left_DT,'yyyymmdd'),'dd/mm/yyyy') left_dt, " +
" A.POS_NM, " +
" ROUND(NVL(A.SALARY_LEVEL1,0),10), " +
" ROUND(NVL(A.SALARY_LEVEL2,0),10), " +
" ROUND(NVL(A.SALARY_LEVEL1,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.SALARY_LEVEL2,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.ALLOW_AMT1,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.ALLOW_AMT2,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.ALLOW_AMT3,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND (NVL (A.ALLOW_AMT1, 0) + NVL (A.ALLOW_AMT2, 0) + NVL (A.ALLOW_AMT3, 0), 0) total_allow, " +
" ROUND ((NVL (A.ALLOW_AMT1, 0) + NVL (A.ALLOW_AMT2, 0) + NVL (A.ALLOW_AMT3, 0))*NVL(A.EXCHANGE_RATE,1), 0) total_allow_vnd, " +
" ROUND(NVL(A.WT_L1,0)/8 + NVL(a.HOL_DAY_L1,0),10), " +
" ROUND(NVL(A.WT_L2,0)/8 + NVL(a.HOL_DAY_L2,0),10), " +
" ROUND(NVL(A.ABS_ALE_L1,0)/8,10), " +
" ROUND(NVL(A.ABS_ALE_L2,0)/8,10), " +
" ROUND(NVL(A.ABS_OTHER_PAY_L1,0)/8,10), " +
" ROUND(NVL(A.ABS_OTHER_PAY_L2,0)/8,10), " +
" ROUND((NVL(A.WT_L1_AMT,0) + nvl(A.WT_L2_AMT,0) + nvl(A.TOTAL_ABS_PAY_L1_AMT,0) + nvl(A.TOTAL_ABS_PAY_L2_AMT,0)+ NVL(a.HOL_DAY_L1_AMT,0)+ NVL(a.HOL_DAY_L2_AMT,0))*NVL(A.EXCHANGE_RATE,1),0) total_wd_amt, " +
" ROUND(NVL(A.OT_L1,0),10), " +
" ROUND(NVL(A.OT_L2,0),10), " +
" ROUND(NVL(A.ST_L1,0) + nvl(A.HT_L1,0)+ nvl(A.OHT_L1,0),10), " +
" ROUND(NVL(A.ST_L2,0) + nvl(A.HT_L2,0)+ nvl(A.OHT_L2,0),10), " +
" ROUND(NVL(A.NT_30_L1,0),10), " +
" ROUND(NVL(A.NT_30_L2,0),10), " +
" ROUND(NVL(A.NT_45_L1,0),10), " +
" ROUND(NVL(A.NT_45_L2,0),10), " +
" ROUND(NVL(A.NT_60_L1,0),10), " +
" ROUND(NVL(A.NT_60_L2,0),10), " +
" ROUND(NVL(A.NT_90_L1,0),10), " +
" ROUND(NVL(A.NT_90_L2,0),10), " +
" ROUND((NVL(A.TOTAL_OT_L1_AMT,0) + nvl(A.TOTAL_OT_L2_AMT,0))*NVL(A.EXCHANGE_RATE,1),0) total_ot_amt, " +
" ROUND((NVL(A.OT_TAX_L1_AMT,0) + nvl(A.OT_TAX_L2_AMT,0))*NVL(A.EXCHANGE_RATE,1),0) total_ot_tax, " +
" ROUND((NVL(A.RETURN_AMT,0) + nvl(A.INC_AMT,0))*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.ALE_STOP,0),10), " +
" ROUND((NVL(A.ALE_STOP_AMT,0))*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.SEVERANCE_MONTH,0),10), " +
" ROUND(NVL(A.SEVERANCE_AMT,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.GROSS_AMT,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.NET_AMT,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.ADVANCE_AMT,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.DEDUCT_PIT,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.HEALTH_COM_NET,0)*NVL(A.EXCHANGE_RATE,1) ,0), " +
" ROUND(NVL(A.INCOME_AMT_AFTER,0)*NVL(A.EXCHANGE_RATE,1) ,0), " +
" ROUND(NVL(A.ALLOW_AMT4,0)*NVL(A.EXCHANGE_RATE,1),0) house_company, " +
" ROUND(NVL(A.HOUSING_FEE,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND((NVL(A.NET_AMT,0) + NVL(A.HOUSING_FEE,0))*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.INCOME_BEFORE_TAX,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.INCOME_TAX_NET,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.INS_SALARY,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND(NVL(A.HEALTH_45_AMT,0)*NVL(A.EXCHANGE_RATE,1),0), " +
" ROUND((NVL(A.NET_AMT,0) + NVL(A.HOUSING_FEE,0) + NVL(A.INCOME_TAX_NET,0) + NVL(A.HEALTH_45_AMT,0))*NVL(A.EXCHANGE_RATE,1),0) total_cost " +
" FROM THR_MONTH_SALARY A " +
" WHERE A.DEL_IF=0 AND A.WORK_MON='" + p_work_mon + "' " +
" 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_m_type + "','ALL','ALL',a.MONEY_KIND)='" + p_m_type + "'" +
" and decode('" + p_emp_type + "','ALL','ALL',A.EMPLOYEE_TYPE)='" + p_emp_type + "'" +
" AND NVL(A.NET_AMT,0) " + p_type +
" ORDER BY A.DEPT_NM,A.THR_GROUP_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);
SQL
= "SELECT NT_RATE nt30, " +
" NOT_RATE nt45, " +
" NOT_2_RATE nt45_2, " +
" OST_RATE ost, " +
" NOST_RATE nost, " +
" OHT_RATE oht, " +
" NOHT_RATE noht, " +
" NST_RATE nt60, " +
" NHT_RATE nt90 " +
" FROM THR_MONTH_OT_RATE " +
" WHERE del_if = 0 AND work_mon = '" + p_work_mon + "' ";
DataTable dt_rate = CtlLib.TableReadOpen(SQL);
int i, j, k, i_allow;
int pos = 6;
int allow = 11, allow_k = 23, equip = 112; //vi tri phu cap
int pos_nt = 23;
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();
//---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; i++)
{
exSheet.Range[pos + i + 1, 1].Rows.EntireRow.Insert();
}
//Show data
for (i = 0; i < irow_emp; i++)
{
for (k = 8; k < icol_emp; k++)
{
grant_total[k] = grant_total[k] + double.Parse(dt_emp.Rows[i][k].ToString());
}
emp_dept++;
exSheet.Cells[pos + i, 1].Value = emp_dept;
for (j = 2; j < icol_emp; j++)
{
if (j < 8)
{
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)
{
exSheet.Cells[pos + i + 1, 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 = 8; k < icol_emp; k++)
{
exSheet.Cells[pos + i + 1, k].Value = grant_total[k];
}
}
}
// 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("sale");
string p_from_date = Request["p_from_date"];
string p_to_date = Request["p_to_date"];
string p_order_type = Request["p_order_type"];
string p_po_vendor = Request["p_po_vendor"];
string p_item_group = Request["p_item_group"];
string p_item = Request["p_item"];
string p_search_type = Request["p_searchtype"];
string p_curr = Request["p_curr"];
string p_io_type = Request["p_io_type"];
string p_sale_yn = Request["p_sale_yn"];
string p_wh_type = Request["p_wh_type"];
string p_wh_pk = Request["p_wh_pk"];
string p_gd_type = Request["p_gd_type"];
string TemplateFile = "";
string TempFile = "";
if (p_search_type == "REQ")
{
TemplateFile = "rpt_dscd00030.xls";
TempFile = "../../../../system/temp/rpt_dscd00030_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
}
else
{
TemplateFile = "rpt_dscd00030_1.xls";
TempFile = "../../../../system/temp/rpt_dscd00030_1_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
}
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string para = "'" + p_from_date + "','" + p_to_date + "','" + p_order_type + "','" + p_po_vendor + "','" + p_item_group + "','" + p_item + "','" + p_search_type + "','" + p_curr + "','" + p_io_type + "','" + p_sale_yn + "','" + p_wh_type + "','" + p_wh_pk + "','" + p_gd_type + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00030_INFO");
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00030", para);
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells[1, 1].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtInfo.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 95, 55));
}
exSheet.Cells["P2"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
string s_date = System.DateTime.ParseExact(p_from_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
s_date += " - " + System.DateTime.ParseExact(p_to_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
exSheet.Cells["B2"].Value = "Date : " + s_date;
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A5"].Rows.EntireRow.Insert();//insert row new of sheet
}
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
for (int j = 0; j < dtDetail.Columns.Count; j++)
{
exSheet.Cells[4 + i, j + 1].Value = dtDetail.Rows[i][j];
}
}
exSheet.Cells[dtDetail.Rows.Count + 4, 12].Value = "=SUM(L4:L" + (dtDetail.Rows.Count + 3) + ")";
exSheet.Cells[dtDetail.Rows.Count + 4, 14].Value = "=SUM(N4:N" + (dtDetail.Rows.Count + 3) + ")";
exSheet.Cells[dtDetail.Rows.Count + 4, 16].Value = "=SUM(P4:P" + (dtDetail.Rows.Count + 3) + ")";
exSheet.Cells[dtDetail.Rows.Count + 4, 17].Value = "=SUM(Q4:Q" + (dtDetail.Rows.Count + 3) + ")";
exSheet.Cells[dtDetail.Rows.Count + 4, 19].Value = "=SUM(S4:S" + (dtDetail.Rows.Count + 3) + ")";
//exSheet.Cells[dtDetail.Rows.Count + 9, 7].Value = "=SUM(G9:G" + (dtDetail.Rows.Count + 8) + ")";
//exSheet.Cells[dtDetail.Rows.Count + 9, 8].Value = "=SUM(H9:H" + (dtDetail.Rows.Count + 8) + ")";
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string l_pk = Request["p_master_pk"];
string TemplateFile = "rpt_60240010_Group_Record.xls";
string TempFile = "../../../../system/temp/rpt_60240010_Group_Record_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string para = "";
DataTable dt, dtSum;
para = "'" + l_pk + "'";
dt = CtlLib.TableReadOpenCursor("ht_rpt_60240010_Group_Record", para);
//-----header---
//---end--header---
//--set-----DataBind--in---Master---
if (dt.Rows.Count == 0)
{
exSheet.Cells[6, 8].Value = "There is not data!";
}
if (dt.Rows.Count > 0)
{
//No.of person: Adult: Child:
exSheet.Cells[4, 3].Value = "No. of Room :" + dt.Rows[0]["total_room"].ToString();
exSheet.Cells[4, 1].Value = "No.of person: Adult: " + dt.Rows[0]["adult"].ToString() + " Child: " + dt.Rows[0]["childrens"].ToString();
exSheet.Cells[3, 2].Value = dt.Rows[0]["from_date"].ToString();
exSheet.Cells[3, 4].Value = dt.Rows[0]["arrival_time"].ToString();
exSheet.Cells[3, 6].Value = dt.Rows[0]["date_to"].ToString();
exSheet.Cells[3, 8].Value = dt.Rows[0]["departure_time"].ToString();
exSheet.Cells[2, 6].Value = dt.Rows[0]["company_name"].ToString();
exSheet.Cells[2, 2].Value = dt.Rows[0]["group_name"].ToString();
}
//----------------------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A10"].Rows.EntireRow.Insert();//insert row new of sheet
}
//set date title of year
//exSheet.Cells[2, 1].Value = "Income Statement Year" + " " + l_year;
//set data to detail.
// loop detail not percent
int l_total_room = 0, l_total_guest = 0;
if (dt.Rows.Count > 0)
{
int l_cnt = dt.Rows.Count;
for (int l_row = 1; l_row <= dt.Rows.Count; l_row++)
{
exSheet.Cells[l_row + 8, 4, l_row + 8, 8].Merge();
for (int col = 0; col <= 6; col++)
{
if (col == 0)
{
exSheet.Cells[8 + l_row, 1].Value = dt.Rows[l_row - 1]["room_no"].ToString();
}
if (col == 1)
{
exSheet.Cells[8 + l_row, 2].Value = dt.Rows[l_row - 1]["guest_name"].ToString();
}
if (col == 3)
{
exSheet.Cells[8 + l_row, 4].Value = dt.Rows[l_row - 1]["description"].ToString();
}
}
}
}
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
//range = exSheet.Range["A1"];
// hide row A5
//range.Rows.Hidden = true;
// font bold header
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
//string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string l_room_no_guest_group = Request["p_room"];
string l_report_type = Request["p_report_type"];
string l_dt_from = Request["p_from_date"];
string l_dt_to = Request["p_to_date"];
string TemplateFile = "rpt_60250020_inhouse_list_date.xls";
string TempFile = "../../../../system/temp/rpt_60250020_inhouse_list_date_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string para = "";
DataTable dt, dtSum;
para = "'" + l_room_no_guest_group + "','" + l_report_type + "','" + l_dt_from + "','" + l_dt_to + "'";
dt = CtlLib.TableReadOpenCursor("ht_rpt_60250090_in_ListDate", para);
//-----header---
//---end--header---
//--set-----DataBind--in---Master---
if (dt.Rows.Count == 0)
{
exSheet.Cells[3, 3].Value = "There is not data!";
}
if (dt.Rows.Count > 0)
{
// exSheet.Cells[3, 1].Value = "Total : "+dt.Rows.Count+" Rooms.";
exSheet.Cells[3, 13].Value = dt.Rows[0]["print_time"].ToString();
exSheet.Cells[3, 1].Value = dt.Rows[0]["para_date"].ToString();
if (l_report_type == "1")
{
exSheet.Cells[2, 2].Value = "Inhouse list ";
}
if (l_report_type == "2")
{
exSheet.Cells[2, 2].Value = "Inhouse list (Normal)";
}
if (l_report_type == "3")
{
exSheet.Cells[2, 2].Value = "Inhouse list (VIP)";
}
}
//----------------------
////end-set-----DataBind--in---Master---
int l_row_first = 5, l_cnt = dt.Rows.Count;
double tt_adult_total_guest = 0, tt_child_total_guest = 0, l_total_room = 0;
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A6"].Rows.EntireRow.Insert();//insert row new of sheet
}
for (int l_row = 0; l_row < dt.Rows.Count; l_row++)
{
exSheet.Cells[l_row_first + l_row, 1].Value = dt.Rows[l_row]["guest_name"].ToString();
exSheet.Cells[l_row_first + l_row, 2].Value = dt.Rows[l_row]["checkin_date"].ToString();
exSheet.Cells[l_row_first + l_row, 3].Value = dt.Rows[l_row]["departure_date"].ToString();
if (dt.Rows[l_row]["room_no"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 4].Value = double.Parse(dt.Rows[l_row]["room_no"].ToString());
}
exSheet.Cells[l_row_first + l_row, 5].Value = dt.Rows[l_row]["room_code"].ToString();
if (dt.Rows[l_row]["room_rate"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 6].Value = double.Parse(dt.Rows[l_row]["room_rate"].ToString());
}
if (dt.Rows[l_row]["child_total_guest"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 7].Value = double.Parse(dt.Rows[l_row]["child_total_guest"].ToString());
tt_child_total_guest = tt_child_total_guest + double.Parse(dt.Rows[l_row]["child_total_guest"].ToString());
}
if (dt.Rows[l_row]["adult_total_guest"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 8].Value = double.Parse(dt.Rows[l_row]["adult_total_guest"].ToString());
tt_adult_total_guest = tt_adult_total_guest + double.Parse(dt.Rows[l_row]["adult_total_guest"].ToString());
}
exSheet.Cells[l_row_first + l_row, 9].Value = dt.Rows[l_row]["company_name"].ToString();
exSheet.Cells[l_row_first + l_row, 10].Value = dt.Rows[l_row]["nation"].ToString();
exSheet.Cells[l_row_first + l_row, 11].Value = dt.Rows[l_row]["request_master"].ToString();
exSheet.Cells[l_row_first + l_row, 12].Value = dt.Rows[l_row]["detail_request"].ToString();
//exSheet.Cells[l_row_first + l_row, 13].Value = dt.Rows[l_row]["adult_total_guest"].ToString(); special service
exSheet.Cells[l_row_first + l_row, 14].Value = dt.Rows[l_row]["VIP"].ToString();
}
exSheet.Cells[l_row_first + l_cnt, 7].Value = tt_child_total_guest;
exSheet.Cells[l_row_first + l_cnt, 8].Value = tt_adult_total_guest;
//exSheet.Cells[3, 1].Value = "Total : "+l_total_room+" Room(s.)";
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
range.Rows.Hidden = true;
// font bold header
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
//string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string TemplateFile = "rpt_60300010_Reservation_daily_report.xls";
string TempFile = "../../../../system/temp/rpt_60300010_Reservation_daily_report_" + 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_dtfrm = Request["p_dtfrm"];
string para = "'" + p_dtfrm + "'";
DataTable dt = CtlLib.TableReadOpenCursor("ht_rpt_60300010_rm_rsv_state", para);
// MASTER
exSheet.Cells["c2"].Value = dt.Rows[0]["p_date"].ToString();
exSheet.Cells[2, 43].Value = dt.Rows[0]["print_time"].ToString();
//exSheet.Cells[6, 1].Value ="Print time: "+ dt.Rows[0]["print_time"].ToString();
exSheet.Cells[4, 13].Value = dt.Rows[0]["month_curr"].ToString();
exSheet.Cells[4, 16].Value = dt.Rows[0]["month02"].ToString();
exSheet.Cells[4, 19].Value = dt.Rows[0]["month03"].ToString();
exSheet.Cells[4, 22].Value = dt.Rows[0]["month04"].ToString();
exSheet.Cells[4, 25].Value = dt.Rows[0]["month05"].ToString();
exSheet.Cells[4, 28].Value = dt.Rows[0]["month06"].ToString();
exSheet.Cells[4, 31].Value = dt.Rows[0]["month07"].ToString();
exSheet.Cells[4, 34].Value = dt.Rows[0]["month08"].ToString();
exSheet.Cells[4, 37].Value = dt.Rows[0]["month09"].ToString();
exSheet.Cells[4, 40].Value = dt.Rows[0]["month10"].ToString();
exSheet.Cells[4, 43].Value = dt.Rows[0]["month11"].ToString();
exSheet.Cells[4, 46].Value = dt.Rows[0]["month12"].ToString();
// INSERT ROWS
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A6"].Rows.EntireRow.Insert();//insert row new of sheet
}
int pos = 4, row_num = 0;
for (int i = 1; i <= dt.Rows.Count; i++)
{
if ((dt.Rows[i - 1]["mkt_segment"].ToString() != "") && (dt.Rows[i - 1]["company_name"].ToString() != ""))
{
row_num = row_num + 1;
exSheet.Cells[pos + i, 1].Value = row_num;
}
exSheet.Cells[pos + i, 2].Value = dt.Rows[i - 1]["mkt_segment"];
exSheet.Cells[pos + i, 3].Value = dt.Rows[i - 1]["company_name"];
//exSheet.Cells[pos + i, 4 ].Value = dt.Rows[i-1]["Allotment_day"];
exSheet.Cells[pos + i, 4].Value = dt.Rows[i - 1]["total_night"];
exSheet.Cells[pos + i, 5].Value = dt.Rows[i - 1]["weekday"];
exSheet.Cells[pos + i, 6].Value = dt.Rows[i - 1]["weekday_rate"];
exSheet.Cells[pos + i, 7].Value = dt.Rows[i - 1]["weekend"];
exSheet.Cells[pos + i, 8].Value = dt.Rows[i - 1]["weekend_rate"];
exSheet.Cells[pos + i, 9].Value = dt.Rows[i - 1]["total_rm_amt"];
exSheet.Cells[pos + i, 10].Value = dt.Rows[i - 1]["total_rate"];
exSheet.Cells[pos + i, 11].Value = dt.Rows[i - 1]["n1"];
exSheet.Cells[pos + i, 12].Value = dt.Rows[i - 1]["rate1"];
exSheet.Cells[pos + i, 13].Value = dt.Rows[i - 1]["d1"];
exSheet.Cells[pos + i, 14].Value = dt.Rows[i - 1]["n2"];
exSheet.Cells[pos + i, 15].Value = dt.Rows[i - 1]["rate2"];
exSheet.Cells[pos + i, 16].Value = dt.Rows[i - 1]["d2"];
exSheet.Cells[pos + i, 17].Value = dt.Rows[i - 1]["n3"];
exSheet.Cells[pos + i, 18].Value = dt.Rows[i - 1]["rate3"];
exSheet.Cells[pos + i, 19].Value = dt.Rows[i - 1]["d3"];
exSheet.Cells[pos + i, 20].Value = dt.Rows[i - 1]["n4"];
exSheet.Cells[pos + i, 21].Value = dt.Rows[i - 1]["rate4"];
exSheet.Cells[pos + i, 22].Value = dt.Rows[i - 1]["d4"];
exSheet.Cells[pos + i, 23].Value = dt.Rows[i - 1]["n5"];
exSheet.Cells[pos + i, 24].Value = dt.Rows[i - 1]["rate5"];
exSheet.Cells[pos + i, 25].Value = dt.Rows[i - 1]["d5"];
exSheet.Cells[pos + i, 26].Value = dt.Rows[i - 1]["n6"];
exSheet.Cells[pos + i, 27].Value = dt.Rows[i - 1]["rate6"];
exSheet.Cells[pos + i, 28].Value = dt.Rows[i - 1]["d6"];
exSheet.Cells[pos + i, 29].Value = dt.Rows[i - 1]["n7"];
exSheet.Cells[pos + i, 30].Value = dt.Rows[i - 1]["rate7"];
exSheet.Cells[pos + i, 31].Value = dt.Rows[i - 1]["d7"];
exSheet.Cells[pos + i, 32].Value = dt.Rows[i - 1]["n8"];
exSheet.Cells[pos + i, 33].Value = dt.Rows[i - 1]["rate8"];
exSheet.Cells[pos + i, 34].Value = dt.Rows[i - 1]["d8"];
exSheet.Cells[pos + i, 35].Value = dt.Rows[i - 1]["n9"];
exSheet.Cells[pos + i, 36].Value = dt.Rows[i - 1]["rate9"];
exSheet.Cells[pos + i, 37].Value = dt.Rows[i - 1]["d9"];
exSheet.Cells[pos + i, 38].Value = dt.Rows[i - 1]["n10"];
exSheet.Cells[pos + i, 39].Value = dt.Rows[i - 1]["rate10"];
exSheet.Cells[pos + i, 40].Value = dt.Rows[i - 1]["d10"];
exSheet.Cells[pos + i, 41].Value = dt.Rows[i - 1]["n11"];
exSheet.Cells[pos + i, 42].Value = dt.Rows[i - 1]["rate11"];
exSheet.Cells[pos + i, 43].Value = dt.Rows[i - 1]["d11"];
exSheet.Cells[pos + i, 44].Value = dt.Rows[i - 1]["n12"];
exSheet.Cells[pos + i, 45].Value = dt.Rows[i - 1]["rate12"];
exSheet.Cells[pos + i, 46].Value = dt.Rows[i - 1]["d12"];
exSheet.Cells[pos + i, 47].Value = dt.Rows[i - 1]["night_month_over"];
exSheet.Cells[pos + i, 48].Value = dt.Rows[i - 1]["rate_over"];
exSheet.Cells[pos + i, 49].Value = dt.Rows[i - 1]["amt_month_over"];
if ((dt.Rows[i - 1]["mkt_segment"].ToString() != "") && (dt.Rows[i - 1]["company_name"].ToString() == ""))
{
string l_Color = "0XD8D8D8";
Color colorValueFrmHex = ColorTranslator.FromHtml(l_Color); //var color
exSheet.Cells[pos + i, 3, pos + i, 49].Interior.Color = colorValueFrmHex; //set color
exSheet.Cells[pos + i, 3].Value = "Sub Total:";
}
if ((dt.Rows[i - 1]["mkt_segment"].ToString() == "") && (dt.Rows[i - 1]["company_name"].ToString() == ""))
{
string l_Color = "0X18D8D8";
Color colorValueFrmHex = ColorTranslator.FromHtml(l_Color); //var color
exSheet.Cells[pos + i, 3, pos + i, 49].Interior.Color = colorValueFrmHex; //set color
exSheet.Cells[pos + i, 3].Value = "Total:";
}
if (i < dt.Rows.Count)
{
if (dt.Rows[i - 1]["mkt_segment"].ToString() == dt.Rows[i]["mkt_segment"].ToString())
{
exSheet.Range[i + 4, 2, i + 5, 2].Merge();
}
}
}
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser("sale");
string l_pk = Request["master_pk"];
string TemplateFile = "rpt_dscd00076.xls";
string TempFile = "../../../../system/temp/rpt_dscd00076_" + 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 + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00075_2", "'" + Session["User_ID"].ToString() + "'");
dtInfo.Rows.Add();
DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00075", para);
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00075_1", para);
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells[2, 2].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtInfo.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 143, 84));
}
exSheet.Cells["P3"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
exSheet.Cells["E5"].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells["M5"].Value = dtMaster.Rows[0]["slip_no"].ToString();
exSheet.Cells["E6"].Value = dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells["M6"].Value = dtMaster.Rows[0]["out_date"].ToString();
exSheet.Cells["E7"].Value = dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells["M7"].Value = dtMaster.Rows[0]["description"].ToString();
exSheet.Cells["E8"].Value = dtInfo.Rows[0]["phone_no"].ToString();
//---------
exSheet.Cells["E10"].Value = dtMaster.Rows[0]["bill_partner_name"].ToString();
exSheet.Cells["M10"].Value = dtMaster.Rows[0]["deli_partner_name"].ToString();
exSheet.Cells["E11"].Value = dtMaster.Rows[0]["short_nm"].ToString();
exSheet.Cells["M11"].Value = dtMaster.Rows[0]["deli_addr1"].ToString();
exSheet.Cells["E12"].Value = dtMaster.Rows[0]["bill_addr1"].ToString();
exSheet.Cells["E13"].Value = dtMaster.Rows[0]["tax_code"].ToString();
exSheet.Cells["G13"].Value = dtMaster.Rows[0]["phone_no"].ToString();
exSheet.Cells["E14"].Value = dtMaster.Rows[0]["po_date"].ToString();
exSheet.Cells["E15"].Value = dtMaster.Rows[0]["po_no"].ToString();
//----------------------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A22"].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 pos = 21;
for (int l_row = 0; l_row < dtDetail.Rows.Count; l_row++)
{
exSheet.Cells[pos + l_row, 1].Value = l_row + 1;
exSheet.Cells[pos + l_row, 2].Value = dtDetail.Rows[l_row]["item_bc"];
exSheet.Cells[pos + l_row, 3].Value = dtDetail.Rows[l_row]["item_code"];
exSheet.Cells[pos + l_row, 4].Value = dtDetail.Rows[l_row]["item_name"];
exSheet.Cells[pos + l_row, 4, pos + l_row, 6].Merge();
exSheet.Cells[pos + l_row, 7].Value = dtDetail.Rows[l_row]["out_uom"];
exSheet.Cells[pos + l_row, 8].Value = dtDetail.Rows[l_row]["out_qty"];
exSheet.Cells[pos + l_row, 9].Value = dtDetail.Rows[l_row]["unit_price"];
exSheet.Cells[pos + l_row, 10].Value = dtDetail.Rows[l_row]["discount_rate"];
exSheet.Cells[pos + l_row, 11].Value = dtDetail.Rows[l_row]["discount_amount"];
exSheet.Cells[pos + l_row, 11, pos + l_row, 13].Merge();
exSheet.Cells[pos + l_row, 14].Value = dtDetail.Rows[l_row]["total_amount"];
exSheet.Cells[pos + l_row, 15].Value = dtDetail.Rows[l_row]["ref_no"];
exSheet.Cells[pos + l_row, 16].Value = dtDetail.Rows[l_row]["description"];
}
exSheet.Cells[dtDetail.Rows.Count + 21, 8].Value = "=SUM(H21:H" + (dtDetail.Rows.Count + 20) + ")";
exSheet.Cells[dtDetail.Rows.Count + 21, 11].Value = "=SUM(K21:K" + (dtDetail.Rows.Count + 20) + ")";
exSheet.Cells[dtDetail.Rows.Count + 21, 14].Value = "=SUM(N21:N" + (dtDetail.Rows.Count + 20) + ")";
exSheet.Cells[dtDetail.Rows.Count + 22, 14].Value = "=SUM(N21:N" + (dtDetail.Rows.Count + 20) + ")*10%";
exSheet.Cells[dtDetail.Rows.Count + 23, 14].Value = "=SUM(N21:N" + (dtDetail.Rows.Count + 20) + ")+ (SUM(N21:N" + (dtDetail.Rows.Count + 20) + ")*10%)";
// end loop detail not percent
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
//CtlLib.SetUser("SALE");
string TemplateFile = "rpt_dsbs00200_ST01.xls";
string TempFile = "../../../../system/temp/rpt_dsbs00200_ST01_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string p_company_pk = Request.QueryString["p_company_pk"];
string p_date_type = Request.QueryString["p_date_type"];
string p_from_dt = Request.QueryString["p_from_dt"];
string p_to_dt = Request.QueryString["p_to_dt"];
string p_po_item = Request.QueryString["p_po_item"];
string p_order_type = Request.QueryString["p_order_type"];
string p_bill_to = Request.QueryString["p_bill_to"];
string para = "'" + p_from_dt + "','" + p_to_dt + "'";
DataTable dtHeader = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00063_1", para);
para = "'" + p_company_pk + "','" + p_date_type + "','" + p_from_dt + "','" + p_to_dt + "','" + p_po_item + "','" + p_order_type + "','" + p_bill_to + "'";
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_dsbs00063", para);
// MASTER
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtHeader.Rows[0]["pic"].ToString()))
{
exSheet.Cells[1, 1].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtHeader.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 90, 59));
}
exSheet.Cells["C1"].Value = dtHeader.Rows[0]["partner_name"];
exSheet.Cells["C2"].Value = dtHeader.Rows[0]["addr1"];
exSheet.Cells["C3"].Value = "Tax Code: " + dtHeader.Rows[0]["tax_code"];
exSheet.Cells["Q1"].Value = "Tel: " + dtHeader.Rows[0]["phone_no"];
exSheet.Cells["Q2"].Value = "Fax No: " + dtHeader.Rows[0]["fax_no"];
exSheet.Cells["Q3"].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
exSheet.Cells["Q7"].Value = dtHeader.Rows[0]["t_date"];
// END MASTER
// INSERT ROWS
for (int l_addrow = 0; l_addrow < dtDetail.Rows.Count - 1; l_addrow++)
{
exSheet.Range["A11"].Rows.EntireRow.Insert();//insert row new of sheet
}
// SET DATA
int pos = 10;
int i = 0;
Color color1 = ColorTranslator.FromHtml("0xCCFFFF");
Color color2 = ColorTranslator.FromHtml("0xFFFFCC");
for (i = 0; i < dtDetail.Rows.Count; i++)
{
exSheet.Cells[pos + i, 1].Value = i + 1;
exSheet.Cells[pos + i, 2].Value = dtDetail.Rows[i]["bill_to_name"];
exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["grp_nm"];
exSheet.Cells[pos + i, 4].Value = dtDetail.Rows[i]["company_name"];
exSheet.Cells[pos + i, 5].Value = dtDetail.Rows[i]["order_year"];
exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["po_no"];
exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["ITEM_ETD"];
exSheet.Cells[pos + i, 8].Value = dtDetail.Rows[i]["item_code"];
exSheet.Cells[pos + i, 9].Value = dtDetail.Rows[i]["item_name"];
exSheet.Cells[pos + i, 10].Value = dtDetail.Rows[i]["ord_qty"];
exSheet.Cells[pos + i, 11].Value = dtDetail.Rows[i]["unit_price"];
exSheet.Cells[pos + i, 12].Value = dtDetail.Rows[i]["item_amount"];
exSheet.Cells[pos + i, 13].Value = dtDetail.Rows[i]["act_etd"];
exSheet.Cells[pos + i, 14].Value = dtDetail.Rows[i]["out_qty"];
exSheet.Cells[pos + i, 15].Value = dtDetail.Rows[i]["bal_qty"];
exSheet.Cells[pos + i, 16].Value = dtDetail.Rows[i]["out_amount"];
exSheet.Cells[pos + i, 17].Value = dtDetail.Rows[i]["description"];
exSheet.Cells[pos + i, 18].Value = dtDetail.Rows[i]["dest_port"];
exSheet.Cells[pos + i, 19].Value = dtDetail.Rows[i]["att01"];
exSheet.Cells[pos + i, 20].Value = dtDetail.Rows[i]["ex_nation"];
//-------------
}
int t = pos + i - 1;
exSheet.Cells[pos + i, 10].Value = "=sum(J10:J" + t + ")";
exSheet.Cells[pos + i, 12].Value = "=sum(L10:L" + t + ")";
exSheet.Cells[pos + i, 14].Value = "=sum(N10:N" + t + ")";
exSheet.Cells[pos + i, 15].Value = "=sum(O10:O" + t + ")";
exSheet.Cells[pos + i, 16].Value = "=sum(P10:P" + t + ")";
//---------
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string 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("SALE");
//string l_pk = Request["master_pk"];
string TemplateFile = "rpt_bisc00070_ST01.xls";
string TempFile = "../../../../system/temp/rpt_bisc00070_ST01_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string p_from_date = Request.QueryString["p_from_date"];
string p_to_date = Request.QueryString["p_to_date"];
string p_wh_type = Request.QueryString["p_wh_type"];
string p_tin_warehouse_pk = Request.QueryString["p_tin_warehouse_pk"];
string p_use_yn = Request.QueryString["p_use_yn"];
string p_item_group_pk = Request.QueryString["p_item_group_pk"];
string p_item = Request.QueryString["p_item"];
string p_in_out_yn = Request.QueryString["p_in_out_yn"];
string p_lang = Request.QueryString["p_lang"];
string p_wh_name = Request.QueryString["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 + "','" + p_lang + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00070_ST01_2", "'" + Session["User_ID"].ToString() + "'");
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00070_ST01_1", para);
if (dtDetail.Rows.Count == 0)
{
Response.Write("There is no data");
Response.End();
}
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells[1, 1].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtInfo.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 100, 55));
}
exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells["C3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells["K1"].Value = "Tell: " + dtInfo.Rows[0]["phone_no"].ToString();
exSheet.Cells["K2"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();
exSheet.Cells["K3"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
exSheet.Cells["I5"].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");
exSheet.Cells["C5"].Value = p_wh_name;
//---------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A9"].Rows.EntireRow.Insert();//insert row new of sheet
}
//set data to detail.
// loop detail not percent
int l_pos = 8;
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
exSheet.Cells[l_pos + i, 1].Value = i + 1;
exSheet.Cells[l_pos + i, 2].Value = dtDetail.Rows[i]["wh_name"];
exSheet.Cells[l_pos + i, 3].Value = dtDetail.Rows[i]["item_code"];
exSheet.Cells[l_pos + i, 4].Value = dtDetail.Rows[i]["item_name"];
exSheet.Cells[l_pos + i, 5].Value = dtDetail.Rows[i]["uom"];
exSheet.Cells[l_pos + i, 6].Value = dtDetail.Rows[i]["lot_no"];
exSheet.Cells[l_pos + i, 7].Value = dtDetail.Rows[i]["begin_qty"];
exSheet.Cells[l_pos + i, 8].Value = dtDetail.Rows[i]["open_qty"];
exSheet.Cells[l_pos + i, 9].Value = dtDetail.Rows[i]["income_qty"];
exSheet.Cells[l_pos + i, 10].Value = dtDetail.Rows[i]["prod_in_qty"];
exSheet.Cells[l_pos + i, 11].Value = dtDetail.Rows[i]["ass_in_qty"];
exSheet.Cells[l_pos + i, 12].Value = dtDetail.Rows[i]["trans_in_qty"];
exSheet.Cells[l_pos + i, 13].Value = dtDetail.Rows[i]["ex_in_qty"];
exSheet.Cells[l_pos + i, 14].Value = dtDetail.Rows[i]["in_return_qty"];
exSheet.Cells[l_pos + i, 15].Value = dtDetail.Rows[i]["others_in_qty"];
exSheet.Cells[l_pos + i, 16].Value = dtDetail.Rows[i]["total_in"];
exSheet.Cells[l_pos + i, 17].Value = dtDetail.Rows[i]["outgo_qty"];
exSheet.Cells[l_pos + i, 18].Value = dtDetail.Rows[i]["deli_qty"];
exSheet.Cells[l_pos + i, 19].Value = dtDetail.Rows[i]["prod_out_qty"];
exSheet.Cells[l_pos + i, 20].Value = dtDetail.Rows[i]["ass_out_qty"];
exSheet.Cells[l_pos + i, 21].Value = dtDetail.Rows[i]["trans_out_qty"];
exSheet.Cells[l_pos + i, 22].Value = dtDetail.Rows[i]["ex_out_qty"];
exSheet.Cells[l_pos + i, 23].Value = dtDetail.Rows[i]["out_return_qty"];
exSheet.Cells[l_pos + i, 24].Value = dtDetail.Rows[i]["discard_qty"];
exSheet.Cells[l_pos + i, 25].Value = dtDetail.Rows[i]["others_out_qty"];
exSheet.Cells[l_pos + i, 26].Value = dtDetail.Rows[i]["adj_qty"];
exSheet.Cells[l_pos + i, 27].Value = dtDetail.Rows[i]["total_out"];
exSheet.Cells[l_pos + i, 28].Value = dtDetail.Rows[i]["end_qty"];
}
exSheet.Cells[dtDetail.Rows.Count + l_pos, 7].Value = "=SUM(G8:G" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 8].Value = "=SUM(H8:H" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 9].Value = "=SUM(I8:I" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 10].Value = "=SUM(J8:J" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 11].Value = "=SUM(K8:K" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 12].Value = "=SUM(L8:L" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 13].Value = "=SUM(M8:M" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 14].Value = "=SUM(N8:N" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 15].Value = "=SUM(O8:O" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 16].Value = "=SUM(P8:P" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 17].Value = "=SUM(Q8:Q" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 18].Value = "=SUM(R8:R" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 19].Value = "=SUM(S8:S" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 20].Value = "=SUM(T8:T" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 21].Value = "=SUM(U8:U" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 22].Value = "=SUM(V8:V" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 23].Value = "=SUM(W8:W" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 24].Value = "=SUM(X8:X" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 25].Value = "=SUM(Y8:Y" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 26].Value = "=SUM(Z8:Z" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 27].Value = "=SUM(AA8:AA" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
exSheet.Cells[dtDetail.Rows.Count + l_pos, 28].Value = "=SUM(AB8:AB" + (dtDetail.Rows.Count - 1 + l_pos) + ")";
// end loop detail not percent
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
//range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string TemplateFile = "rpt_inio00100_0.xls";
string TempFile = "../../../../system/temp/rpt_inio00100_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;
//bind data to excel file
string p_master_pk;
p_master_pk = Request.QueryString["master_pk"];
string para = "'" + p_master_pk + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("ST_LG_RPT_INIO00100_0_0", "'" + Session["User_ID"].ToString() + "'");
DataTable dtMaster = CtlLib.TableReadOpenCursor("ST_LG_RPT_INIO00100_0_1", para);
DataTable dtDetail = CtlLib.TableReadOpenCursor("ST_LG_RPT_INIO00100_0_2", para);
// MASTER
exSheet.Cells[1, 3].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells[1, 8].Value = "Tel: " + dtInfo.Rows[0]["phone_no"].ToString();
exSheet.Cells[2, 3].Value = "Addr: " + dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells[2, 8].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();
exSheet.Cells[3, 3].Value = "MST: " + dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells[3, 8].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy"); //dtInfo.Rows[0]["issue_dt"].ToString();
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells[1, 1].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtInfo.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 90, 59));
}
if (dtMaster.Rows.Count > 0)
{
exSheet.Cells["H4"].Value = dtMaster.Rows[0]["slip_no"];
exSheet.Cells["H5"].Value = dtMaster.Rows[0]["req_date"];
exSheet.Cells["C6"].Value = dtMaster.Rows[0]["wh_name"];
exSheet.Cells["C7"].Value = dtMaster.Rows[0]["requester_name"];
exSheet.Cells["G7"].Value = dtMaster.Rows[0]["ref_no"];
exSheet.Cells["C8"].Value = dtMaster.Rows[0]["description"];
}
// END MASTER
// INSERT ROWS
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A13"].Rows.EntireRow.Insert();//insert row new of sheet
}
// SET DATA
int pos = 12;
int i = 0;
double total_sub = 0;
for (i = 0; i < dtDetail.Rows.Count; i++)
{
exSheet.Cells[pos + i, 1].Value = dtDetail.Rows[i]["seq"];
exSheet.Cells[pos + i, 2].Value = dtDetail.Rows[i]["item_code"];
exSheet.Cells[pos + i, 3].Value = dtDetail.Rows[i]["item_name"];
exSheet.Cells[pos + i, 6].Value = dtDetail.Rows[i]["req_uom"];
exSheet.Cells[pos + i, 7].Value = dtDetail.Rows[i]["req_qty"];
exSheet.Cells[pos + i, 9].Value = dtDetail.Rows[i]["description"];
total_sub += Convert.ToDouble(dtDetail.Rows[i]["req_qty"]);
}
// total
exSheet.Cells["G" + (pos + i + 0)].Value = total_sub;
//---------
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
//CtlLib.SetUser("SALE");
string TemplateFile = "rpt_bisc00171_ST02.xls";
string TempFile = "../../../../system/temp/rpt_bisc00171_ST02_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string p_lang = Request.QueryString["p_lang"];
string p_wh_name = Request.QueryString["p_wh_name"];
string p_wh_pk = Request.QueryString["p_wh_pk"];
string p_item_group_pk = Request.QueryString["p_item_group_pk"];
string p_item = Request.QueryString["p_item"];
string p_date = Request.QueryString["p_date"];
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00171_ST02", "'" + Session["User_ID"].ToString() + "'");
string para = "'" + p_date + "'";
DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00171_ST02_1", para);
para = "'" + p_lang + "','" + p_wh_pk + "','" + p_item_group_pk + "','" + p_item + "','" + p_date + "'";
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00171_ST02_2", para);
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells[1, 1].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtInfo.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 100, 55));
}
exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells["C3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells["I1"].Value = "Tell: " + dtInfo.Rows[0]["phone_no"].ToString();
exSheet.Cells["I1"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();
exSheet.Cells["G5"].Value = System.DateTime.ParseExact(p_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
exSheet.Cells["C5"].Value = p_wh_name;
//---------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count * 3 - 2; l_addrow++)
{
exSheet.Range["A11"].Rows.EntireRow.Insert();//insert row new of sheet
}
if (dtMaster.Rows.Count > 0)
{
exSheet.Cells["H6"].Value = dtMaster.Rows[0]["date01"];
exSheet.Cells["I6"].Value = dtMaster.Rows[0]["date02"];
exSheet.Cells["J6"].Value = dtMaster.Rows[0]["date03"];
exSheet.Cells["K6"].Value = dtMaster.Rows[0]["date04"];
exSheet.Cells["L6"].Value = dtMaster.Rows[0]["date05"];
exSheet.Cells["M6"].Value = dtMaster.Rows[0]["date06"];
exSheet.Cells["N6"].Value = dtMaster.Rows[0]["date07"];
exSheet.Cells["O6"].Value = dtMaster.Rows[0]["date08"];
exSheet.Cells["P6"].Value = dtMaster.Rows[0]["date09"];
exSheet.Cells["Q6"].Value = dtMaster.Rows[0]["date10"];
exSheet.Cells["R6"].Value = dtMaster.Rows[0]["date11"];
exSheet.Cells["S6"].Value = dtMaster.Rows[0]["date12"];
exSheet.Cells["T6"].Value = dtMaster.Rows[0]["date13"];
exSheet.Cells["U6"].Value = dtMaster.Rows[0]["date14"];
exSheet.Cells["V6"].Value = dtMaster.Rows[0]["date15"];
exSheet.Cells["W6"].Value = dtMaster.Rows[0]["date16"];
exSheet.Cells["X6"].Value = dtMaster.Rows[0]["date17"];
exSheet.Cells["Y6"].Value = dtMaster.Rows[0]["date18"];
exSheet.Cells["Z6"].Value = dtMaster.Rows[0]["date19"];
exSheet.Cells["AA6"].Value = dtMaster.Rows[0]["date20"];
exSheet.Cells["AB6"].Value = dtMaster.Rows[0]["date21"];
exSheet.Cells["AC6"].Value = dtMaster.Rows[0]["date22"];
exSheet.Cells["AD6"].Value = dtMaster.Rows[0]["date23"];
exSheet.Cells["AE6"].Value = dtMaster.Rows[0]["date24"];
exSheet.Cells["AF6"].Value = dtMaster.Rows[0]["date25"];
exSheet.Cells["AG6"].Value = dtMaster.Rows[0]["date26"];
exSheet.Cells["AH6"].Value = dtMaster.Rows[0]["date27"];
exSheet.Cells["AI6"].Value = dtMaster.Rows[0]["date28"];
exSheet.Cells["AJ6"].Value = dtMaster.Rows[0]["date29"];
exSheet.Cells["AK6"].Value = dtMaster.Rows[0]["date30"];
exSheet.Cells["AL6"].Value = dtMaster.Rows[0]["date31"];
exSheet.Cells["H7"].Value = dtMaster.Rows[1]["date01"];
exSheet.Cells["I7"].Value = dtMaster.Rows[1]["date02"];
exSheet.Cells["J7"].Value = dtMaster.Rows[1]["date03"];
exSheet.Cells["K7"].Value = dtMaster.Rows[1]["date04"];
exSheet.Cells["L7"].Value = dtMaster.Rows[1]["date05"];
exSheet.Cells["M7"].Value = dtMaster.Rows[1]["date06"];
exSheet.Cells["N7"].Value = dtMaster.Rows[1]["date07"];
exSheet.Cells["O7"].Value = dtMaster.Rows[1]["date08"];
exSheet.Cells["P7"].Value = dtMaster.Rows[1]["date09"];
exSheet.Cells["Q7"].Value = dtMaster.Rows[1]["date10"];
exSheet.Cells["R7"].Value = dtMaster.Rows[1]["date11"];
exSheet.Cells["S7"].Value = dtMaster.Rows[1]["date12"];
exSheet.Cells["T7"].Value = dtMaster.Rows[1]["date13"];
exSheet.Cells["U7"].Value = dtMaster.Rows[1]["date14"];
exSheet.Cells["V7"].Value = dtMaster.Rows[1]["date15"];
exSheet.Cells["W7"].Value = dtMaster.Rows[1]["date16"];
exSheet.Cells["X7"].Value = dtMaster.Rows[1]["date17"];
exSheet.Cells["Y7"].Value = dtMaster.Rows[1]["date18"];
exSheet.Cells["Z7"].Value = dtMaster.Rows[1]["date19"];
exSheet.Cells["AA7"].Value = dtMaster.Rows[1]["date20"];
exSheet.Cells["AB7"].Value = dtMaster.Rows[1]["date21"];
exSheet.Cells["AC7"].Value = dtMaster.Rows[1]["date22"];
exSheet.Cells["AD7"].Value = dtMaster.Rows[1]["date23"];
exSheet.Cells["AE7"].Value = dtMaster.Rows[1]["date24"];
exSheet.Cells["AF7"].Value = dtMaster.Rows[1]["date25"];
exSheet.Cells["AG7"].Value = dtMaster.Rows[1]["date26"];
exSheet.Cells["AH7"].Value = dtMaster.Rows[1]["date27"];
exSheet.Cells["AI7"].Value = dtMaster.Rows[1]["date28"];
exSheet.Cells["AJ7"].Value = dtMaster.Rows[1]["date29"];
exSheet.Cells["AK7"].Value = dtMaster.Rows[1]["date30"];
exSheet.Cells["AL7"].Value = dtMaster.Rows[1]["date31"];
}
//--------------------------
double p_begin = 0, p_total = 0, p_01 = 0, p_02 = 0, p_03 = 0, p_04 = 0, p_05 = 0, p_06 = 0, p_07 = 0, p_08 = 0, p_09 = 0, p_10 = 0,
p_11 = 0, p_12 = 0, p_13 = 0, p_14 = 0, p_15 = 0, p_16 = 0, p_17 = 0, p_18 = 0, p_19 = 0, p_20 = 0,
p_21 = 0, p_22 = 0, p_23 = 0, p_24 = 0, p_25 = 0, p_26 = 0, p_27 = 0, p_28 = 0, p_29 = 0, p_30 = 0, p_31 = 0;
int l_pos = 8;
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
if (i > 0)
{
exSheet.Range[8, 1, 10, 39].Copy(exSheet.Range[l_pos, 1, l_pos + 2, 39], XlPasteType.xlPasteAll);
}
exSheet.Cells[l_pos, 1].Value = i + 1;
exSheet.Cells[l_pos, 2].Value = dtDetail.Rows[i]["item_code"];
exSheet.Cells[l_pos, 3].Value = dtDetail.Rows[i]["item_name"];
exSheet.Cells[l_pos, 4].Value = dtDetail.Rows[i]["uom"];
exSheet.Cells[l_pos, 6].Value = dtDetail.Rows[i]["begin_qty"];
exSheet.Cells[l_pos + 0, 7].Value = dtDetail.Rows[i]["total_in"];
exSheet.Cells[l_pos + 1, 7].Value = dtDetail.Rows[i]["total_out"];
exSheet.Cells[l_pos + 2, 7].Value = dtDetail.Rows[i]["end_qty"];
exSheet.Range[l_pos, 1, l_pos + 2, 1].Merge();
exSheet.Range[l_pos, 2, l_pos + 2, 2].Merge();
exSheet.Range[l_pos, 3, l_pos + 2, 3].Merge();
exSheet.Range[l_pos, 4, l_pos + 2, 4].Merge();
exSheet.Range[l_pos, 6, l_pos + 2, 6].Merge();
exSheet.Cells[l_pos, 8].Value = dtDetail.Rows[i]["in_qty_01"];
exSheet.Cells[l_pos + 1, 8].Value = dtDetail.Rows[i]["out_qty_01"];
exSheet.Cells[l_pos + 2, 8].Value = "=F" + (l_pos) + "+H" + (l_pos) + "-H" + (l_pos + 1);
exSheet.Cells[l_pos, 9].Value = dtDetail.Rows[i]["in_qty_02"];
exSheet.Cells[l_pos + 1, 9].Value = dtDetail.Rows[i]["out_qty_02"];
exSheet.Cells[l_pos + 2, 9].Value = "=H" + (l_pos + 2) + "+I" + (l_pos) + "-I" + (l_pos + 1);
exSheet.Cells[l_pos, 10].Value = dtDetail.Rows[i]["in_qty_03"];
exSheet.Cells[l_pos + 1, 10].Value = dtDetail.Rows[i]["out_qty_03"];
exSheet.Cells[l_pos + 2, 10].Value = "=I" + (l_pos + 2) + "+J" + (l_pos) + "-J" + (l_pos + 1);
exSheet.Cells[l_pos, 11].Value = dtDetail.Rows[i]["in_qty_04"];
exSheet.Cells[l_pos + 1, 11].Value = dtDetail.Rows[i]["out_qty_04"];
exSheet.Cells[l_pos + 2, 11].Value = "=J" + (l_pos + 2) + "+K" + (l_pos) + "-K" + (l_pos + 1);
exSheet.Cells[l_pos, 12].Value = dtDetail.Rows[i]["in_qty_05"];
exSheet.Cells[l_pos + 1, 12].Value = dtDetail.Rows[i]["out_qty_05"];
exSheet.Cells[l_pos + 2, 12].Value = "=K" + (l_pos + 2) + "+L" + (l_pos) + "-L" + (l_pos + 1);
exSheet.Cells[l_pos, 13].Value = dtDetail.Rows[i]["in_qty_06"];
exSheet.Cells[l_pos + 1, 13].Value = dtDetail.Rows[i]["out_qty_06"];
exSheet.Cells[l_pos + 2, 13].Value = "=L" + (l_pos + 2) + "+M" + (l_pos) + "-M" + (l_pos + 1);
exSheet.Cells[l_pos, 14].Value = dtDetail.Rows[i]["in_qty_07"];
exSheet.Cells[l_pos + 1, 14].Value = dtDetail.Rows[i]["out_qty_07"];
exSheet.Cells[l_pos + 2, 14].Value = "=M" + (l_pos + 2) + "+N" + (l_pos) + "-N" + (l_pos + 1);
exSheet.Cells[l_pos, 15].Value = dtDetail.Rows[i]["in_qty_08"];
exSheet.Cells[l_pos + 1, 15].Value = dtDetail.Rows[i]["out_qty_08"];
exSheet.Cells[l_pos + 2, 15].Value = "=N" + (l_pos + 2) + "+O" + (l_pos) + "-O" + (l_pos + 1);
exSheet.Cells[l_pos, 16].Value = dtDetail.Rows[i]["in_qty_09"];
exSheet.Cells[l_pos + 1, 16].Value = dtDetail.Rows[i]["out_qty_09"];
exSheet.Cells[l_pos + 2, 16].Value = "=O" + (l_pos + 2) + "+P" + (l_pos) + "-P" + (l_pos + 1);
exSheet.Cells[l_pos, 17].Value = dtDetail.Rows[i]["in_qty_10"];
exSheet.Cells[l_pos + 1, 17].Value = dtDetail.Rows[i]["out_qty_10"];
exSheet.Cells[l_pos + 2, 17].Value = "=P" + (l_pos + 2) + "+Q" + (l_pos) + "-Q" + (l_pos + 1);
exSheet.Cells[l_pos, 18].Value = dtDetail.Rows[i]["in_qty_11"];
exSheet.Cells[l_pos + 1, 18].Value = dtDetail.Rows[i]["out_qty_11"];
exSheet.Cells[l_pos + 2, 18].Value = "=Q" + (l_pos + 2) + "+R" + (l_pos) + "-R" + (l_pos + 1);
exSheet.Cells[l_pos, 19].Value = dtDetail.Rows[i]["in_qty_12"];
exSheet.Cells[l_pos + 1, 19].Value = dtDetail.Rows[i]["out_qty_12"];
exSheet.Cells[l_pos + 2, 19].Value = "=R" + (l_pos + 2) + "+S" + (l_pos) + "-S" + (l_pos + 1);
exSheet.Cells[l_pos, 20].Value = dtDetail.Rows[i]["in_qty_13"];
exSheet.Cells[l_pos + 1, 20].Value = dtDetail.Rows[i]["out_qty_13"];
exSheet.Cells[l_pos + 2, 20].Value = "=S" + (l_pos + 2) + "+T" + (l_pos) + "-T" + (l_pos + 1);
exSheet.Cells[l_pos, 21].Value = dtDetail.Rows[i]["in_qty_14"];
exSheet.Cells[l_pos + 1, 21].Value = dtDetail.Rows[i]["out_qty_14"];
exSheet.Cells[l_pos + 2, 21].Value = "=T" + (l_pos + 2) + "+U" + (l_pos) + "-U" + (l_pos + 1);
exSheet.Cells[l_pos, 22].Value = dtDetail.Rows[i]["in_qty_15"];
exSheet.Cells[l_pos + 1, 22].Value = dtDetail.Rows[i]["out_qty_15"];
exSheet.Cells[l_pos + 2, 22].Value = "=U" + (l_pos + 2) + "+V" + (l_pos) + "-V" + (l_pos + 1);
exSheet.Cells[l_pos, 23].Value = dtDetail.Rows[i]["in_qty_16"];
exSheet.Cells[l_pos + 1, 23].Value = dtDetail.Rows[i]["out_qty_16"];
exSheet.Cells[l_pos + 2, 23].Value = "=V" + (l_pos + 2) + "+W" + (l_pos) + "-W" + (l_pos + 1);
exSheet.Cells[l_pos, 24].Value = dtDetail.Rows[i]["in_qty_17"];
exSheet.Cells[l_pos + 1, 24].Value = dtDetail.Rows[i]["out_qty_17"];
exSheet.Cells[l_pos + 2, 24].Value = "=W" + (l_pos + 2) + "+X" + (l_pos) + "-X" + (l_pos + 1);
exSheet.Cells[l_pos, 25].Value = dtDetail.Rows[i]["in_qty_18"];
exSheet.Cells[l_pos + 1, 25].Value = dtDetail.Rows[i]["out_qty_18"];
exSheet.Cells[l_pos + 2, 25].Value = "=X" + (l_pos + 2) + "+Y" + (l_pos) + "-Y" + (l_pos + 1);
exSheet.Cells[l_pos, 26].Value = dtDetail.Rows[i]["in_qty_19"];
exSheet.Cells[l_pos + 1, 26].Value = dtDetail.Rows[i]["out_qty_19"];
exSheet.Cells[l_pos + 2, 26].Value = "=Y" + (l_pos + 2) + "+Z" + (l_pos) + "-Z" + (l_pos + 1);
exSheet.Cells[l_pos, 27].Value = dtDetail.Rows[i]["in_qty_20"];
exSheet.Cells[l_pos + 1, 27].Value = dtDetail.Rows[i]["out_qty_20"];
exSheet.Cells[l_pos + 2, 27].Value = "=Z" + (l_pos + 2) + "+AA" + (l_pos) + "-AA" + (l_pos + 1);
exSheet.Cells[l_pos, 28].Value = dtDetail.Rows[i]["in_qty_21"];
exSheet.Cells[l_pos + 1, 28].Value = dtDetail.Rows[i]["out_qty_21"];
exSheet.Cells[l_pos + 2, 28].Value = "=AA" + (l_pos + 2) + "+AB" + (l_pos) + "-AB" + (l_pos + 1);
exSheet.Cells[l_pos, 29].Value = dtDetail.Rows[i]["in_qty_22"];
exSheet.Cells[l_pos + 1, 29].Value = dtDetail.Rows[i]["out_qty_22"];
exSheet.Cells[l_pos + 2, 29].Value = "=AB" + (l_pos + 2) + "+AC" + (l_pos) + "-AC" + (l_pos + 1);
exSheet.Cells[l_pos, 30].Value = dtDetail.Rows[i]["in_qty_23"];
exSheet.Cells[l_pos + 1, 30].Value = dtDetail.Rows[i]["out_qty_23"];
exSheet.Cells[l_pos + 2, 30].Value = "=AC" + (l_pos + 2) + "+AD" + (l_pos) + "-AD" + (l_pos + 1);
exSheet.Cells[l_pos, 31].Value = dtDetail.Rows[i]["in_qty_24"];
exSheet.Cells[l_pos + 1, 31].Value = dtDetail.Rows[i]["out_qty_24"];
exSheet.Cells[l_pos + 2, 31].Value = "=AD" + (l_pos + 2) + "+AE" + (l_pos) + "-AE" + (l_pos + 1);
exSheet.Cells[l_pos, 32].Value = dtDetail.Rows[i]["in_qty_25"];
exSheet.Cells[l_pos + 1, 32].Value = dtDetail.Rows[i]["out_qty_25"];
exSheet.Cells[l_pos + 2, 32].Value = "=AE" + (l_pos + 2) + "+AF" + (l_pos) + "-AF" + (l_pos + 1);
exSheet.Cells[l_pos, 33].Value = dtDetail.Rows[i]["in_qty_26"];
exSheet.Cells[l_pos + 1, 33].Value = dtDetail.Rows[i]["out_qty_26"];
exSheet.Cells[l_pos + 2, 33].Value = "=AF" + (l_pos + 2) + "+AG" + (l_pos) + "-AG" + (l_pos + 1);
exSheet.Cells[l_pos, 34].Value = dtDetail.Rows[i]["in_qty_27"];
exSheet.Cells[l_pos + 1, 34].Value = dtDetail.Rows[i]["out_qty_27"];
exSheet.Cells[l_pos + 2, 34].Value = "=AG" + (l_pos + 2) + "+AH" + (l_pos) + "-AH" + (l_pos + 1);
exSheet.Cells[l_pos, 35].Value = dtDetail.Rows[i]["in_qty_28"];
exSheet.Cells[l_pos + 1, 35].Value = dtDetail.Rows[i]["out_qty_28"];
exSheet.Cells[l_pos + 2, 35].Value = "=AH" + (l_pos + 2) + "+AI" + (l_pos) + "-AI" + (l_pos + 1);
exSheet.Cells[l_pos, 36].Value = dtDetail.Rows[i]["in_qty_29"];
exSheet.Cells[l_pos + 1, 36].Value = dtDetail.Rows[i]["out_qty_29"];
exSheet.Cells[l_pos + 2, 36].Value = "=AI" + (l_pos + 2) + "+AJ" + (l_pos) + "-AJ" + (l_pos + 1);
exSheet.Cells[l_pos, 37].Value = dtDetail.Rows[i]["in_qty_30"];
exSheet.Cells[l_pos + 1, 37].Value = dtDetail.Rows[i]["out_qty_30"];
exSheet.Cells[l_pos + 2, 37].Value = "=AJ" + (l_pos + 2) + "+AK" + (l_pos) + "-AK" + (l_pos + 1);
exSheet.Cells[l_pos, 38].Value = dtDetail.Rows[i]["in_qty_31"];
exSheet.Cells[l_pos + 1, 38].Value = dtDetail.Rows[i]["out_qty_31"];
exSheet.Cells[l_pos + 2, 38].Value = "=AK" + (l_pos + 2) + "+AL" + (l_pos) + "-AL" + (l_pos + 1);
exSheet.Cells[l_pos, 39].Value = dtDetail.Rows[i]["end_qty"];
exSheet.Range[l_pos, 39, l_pos + 2, 39].Merge();
p_total += Convert.ToDouble(dtDetail.Rows[i]["end_qty"]);
p_begin += Convert.ToDouble(dtDetail.Rows[i]["begin_qty"]);
p_01 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 8].Value);
p_02 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 9].Value);
p_03 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 10].Value);
p_04 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 11].Value);
p_05 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 12].Value);
p_06 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 13].Value);
p_07 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 14].Value);
p_08 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 15].Value);
p_09 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 16].Value);
p_10 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 17].Value);
p_11 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 18].Value);
p_12 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 19].Value);
p_13 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 20].Value);
p_14 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 21].Value);
p_15 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 22].Value);
p_16 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 23].Value);
p_17 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 24].Value);
p_18 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 25].Value);
p_19 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 26].Value);
p_20 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 27].Value);
p_21 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 28].Value);
p_22 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 29].Value);
p_23 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 30].Value);
p_24 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 31].Value);
p_25 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 32].Value);
p_26 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 33].Value);
p_27 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 34].Value);
p_28 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 35].Value);
p_29 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 36].Value);
p_30 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 37].Value);
p_31 += Convert.ToDouble(exSheet.Cells[l_pos + 2, 38].Value);
l_pos = l_pos + 3;
}
exSheet.Cells[l_pos, 6].Value = p_begin;
exSheet.Cells[l_pos, 8].Value = p_01;
exSheet.Cells[l_pos, 9].Value = p_02;
exSheet.Cells[l_pos, 10].Value = p_03;
exSheet.Cells[l_pos, 11].Value = p_04;
exSheet.Cells[l_pos, 12].Value = p_05;
exSheet.Cells[l_pos, 13].Value = p_06;
exSheet.Cells[l_pos, 14].Value = p_07;
exSheet.Cells[l_pos, 15].Value = p_08;
exSheet.Cells[l_pos, 16].Value = p_09;
exSheet.Cells[l_pos, 17].Value = p_10;
exSheet.Cells[l_pos, 18].Value = p_11;
exSheet.Cells[l_pos, 19].Value = p_12;
exSheet.Cells[l_pos, 20].Value = p_13;
exSheet.Cells[l_pos, 21].Value = p_14;
exSheet.Cells[l_pos, 22].Value = p_15;
exSheet.Cells[l_pos, 23].Value = p_16;
exSheet.Cells[l_pos, 24].Value = p_17;
exSheet.Cells[l_pos, 25].Value = p_18;
exSheet.Cells[l_pos, 26].Value = p_19;
exSheet.Cells[l_pos, 27].Value = p_20;
exSheet.Cells[l_pos, 28].Value = p_21;
exSheet.Cells[l_pos, 29].Value = p_22;
exSheet.Cells[l_pos, 30].Value = p_23;
exSheet.Cells[l_pos, 31].Value = p_24;
exSheet.Cells[l_pos, 32].Value = p_25;
exSheet.Cells[l_pos, 33].Value = p_26;
exSheet.Cells[l_pos, 34].Value = p_27;
exSheet.Cells[l_pos, 35].Value = p_28;
exSheet.Cells[l_pos, 36].Value = p_29;
exSheet.Cells[l_pos, 37].Value = p_30;
exSheet.Cells[l_pos, 38].Value = p_31;
exSheet.Cells[l_pos, 39].Value = p_total;
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
//range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
//CtlLib.SetUser(Session["crm"].ToString());
CtlLib.SetUser("crm");
string TemplateFile = "rpt_ht_60270070_POS_Adjust.xls";
string TempFile = "../../../../system/temp/rpt_ht_60270070_POS_Adjust_" + 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_master_pk = Request["p_master_pk"];
string para = "'" + p_master_pk + "'";
DataTable dt = CtlLib.TableReadOpenCursor("sp_rpt_htrt00120", para);
// MASTER
// INSERT ROWS
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A5"].Rows.EntireRow.Insert();//insert row new of sheet
}
// SET DATA
int pos = 4;
int i = 0;
for (i = 0; i < dt.Rows.Count; i++)
{
exSheet.Cells[pos + i, 1].Value = dt.Rows[i]["invoice_id"];
exSheet.Cells[pos + i, 2].Value = dt.Rows[i]["room_no"];
exSheet.Cells[pos + i, 3].Value = dt.Rows[i]["guest_name"];
exSheet.Cells[pos + i, 4].Value = dt.Rows[i]["invoice_amtvat"];
exSheet.Cells[pos + i, 5].Value = dt.Rows[i]["adj_date"];
exSheet.Cells[pos + i, 6].Value = dt.Rows[i]["adj_amt_vnd"];
exSheet.Cells[pos + i, 7].Value = dt.Rows[i]["adj_by_pk"];
exSheet.Cells[pos + i, 8].Value = dt.Rows[i]["adj_reason"];
}
exSheet.Cells["B2"].Value = dt.Rows[0]["print_time"];
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string p_from_date = Request["p_from_date"];
string p_to_date = Request["p_to_date"];
string p_wh_type = Request["p_wh_type"];
string p_wh_pk = Request["p_wh_pk"];
string p_cust = Request["p_cust"];
string TemplateFile = "rpt_dsbs00300.xls";
string TempFile = "../../../../system/temp/rpt_dsbs00300_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_wh_pk + "','" + p_cust + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_RPT_dsbs00300_1", "'" + Session["User_ID"].ToString() + "','" + p_from_date + "','" + p_to_date + "','" + p_wh_pk + "'");
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_dsbs00300", para);
if (dtInfo.Rows.Count == 0)
{
goto xxx;
}
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells["A1"].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtInfo.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 131, 71));
}
exSheet.Cells["J1"].Value = "Tel: " + dtInfo.Rows[0]["phone_no"].ToString();
exSheet.Cells["J2"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();
exSheet.Cells["J3"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells["C3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells["J7"].Value = dtInfo.Rows[0]["t_date"].ToString();
exSheet.Cells["C7"].Value = dtInfo.Rows[0]["warehouse"].ToString();
////end-set-----DataBind--in---Master---
xxx:
if (dtDetail.Rows.Count == 0)
{
return;
}
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A10"].Rows.EntireRow.Insert();//insert row new of sheet
}
for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++)
{
exSheet.Cells[8 + l_row, 1].Value = dtDetail.Rows[l_row - 1]["wh_id"].ToString();
exSheet.Cells[8 + l_row, 2].Value = dtDetail.Rows[l_row - 1]["wh_name"].ToString();
exSheet.Cells[8 + l_row, 3].Value = dtDetail.Rows[l_row - 1]["OUT_DATE"].ToString();
exSheet.Cells[8 + l_row, 4].Value = dtDetail.Rows[l_row - 1]["partner_id"].ToString();
exSheet.Cells[8 + l_row, 5].Value = dtDetail.Rows[l_row - 1]["partner_name"].ToString();
exSheet.Cells[8 + l_row, 6].Value = dtDetail.Rows[l_row - 1]["slip_no"].ToString();
exSheet.Cells[8 + l_row, 7].Value = dtDetail.Rows[l_row - 1]["ccy_unit"].ToString();
exSheet.Cells[8 + l_row, 8].Value = dtDetail.Rows[l_row - 1]["item_amount"];
exSheet.Cells[8 + l_row, 9].Value = dtDetail.Rows[l_row - 1]["tax_amount"];
exSheet.Cells[8 + l_row, 10].Value = dtDetail.Rows[l_row - 1]["total_amount"];
exSheet.Cells[8 + l_row, 11].Value = dtDetail.Rows[l_row - 1]["INVOICE_NO"].ToString();
exSheet.Cells[8 + l_row, 12].Value = dtDetail.Rows[l_row - 1]["invoice_date"].ToString();
exSheet.Cells[8 + l_row, 13].Value = dtDetail.Rows[l_row - 1]["invoice_net_Tr_amt"];
exSheet.Cells[8 + l_row, 14].Value = dtDetail.Rows[l_row - 1]["invoice_vat_tax_amt"];
exSheet.Cells[8 + l_row, 15].Value = dtDetail.Rows[l_row - 1]["invoice_total_amount"];
}
exSheet.Cells[9 + dtDetail.Rows.Count, 8].Value = "=SUM(H9:H" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[9 + dtDetail.Rows.Count, 9].Value = "=SUM(I9:I" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[9 + dtDetail.Rows.Count, 10].Value = "=SUM(J9:J" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[9 + dtDetail.Rows.Count, 13].Value = "=SUM(M9:M" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[9 + dtDetail.Rows.Count, 14].Value = "=SUM(N9:N" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[9 + dtDetail.Rows.Count, 15].Value = "=SUM(O9:O" + (dtDetail.Rows.Count + 8) + ")";
// end loop detail not percent
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string l_fr = Request["p_fr"];
string l_to = Request["p_to"];
string l_loc = Request["p_loc"];
string TemplateFile = "rpt_60270030_Invoice.xls";
string TempFile = "../../../../system/temp/rpt_60270030_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 sheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string para = "";
string[][] title = new string[2][] { new string[] { "1", "2", "6", "7", "8", "9", "10", "11", "12", "13" },
new string[] { "Item Code", "Item Name", "Unit", "Q.Ty", "Price", "Sub", "Service", "Tax", "Discount", "Total" } };
int row = 6, tt_dt = 0;
DataTable dt;
para = "'" + l_fr + "','" + l_to + "','" + l_loc + "'";
dt = CtlLib.TableReadOpenCursor("rpt_60270030_item_invoice2", para);
if (dt.Rows.Count > 0)
{
sheet.Cells[4, 5].Value = dt.Rows[0][14];
sheet.Cells[4, 11].Value = dt.Rows[0][15];
sheet.Cells[5, 5].Value = dt.Rows[0][16];
for (int y = 0; y < dt.Rows.Count; y++)
{
if (dt.Rows[y][0].ToString() != "" && dt.Rows[y][3].ToString() == "")
{
row++; for (int x = 0; x < title[0].Length; x++)
{
sheet.Cells[row, int.Parse(title[0][x])].Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlDot;
sheet.Cells[row, int.Parse(title[0][x])].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
if (title[0][x] == "2")
{
sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlDot;
sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
}
switch (x)
{
case 0:
sheet.Cells[row, int.Parse(title[0][x])].Value = "Total Invoice ID: " + dt.Rows[y][0].ToString();
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
break;
case 5:
case 6:
case 7:
case 8:
case 9:
sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x + 3].ToString());
sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0";
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignRight;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
break;
}
}
row++; sheet.Cells[row, 1].RowHeight = 20;
}
else if (dt.Rows[y][0].ToString() != "" && dt.Rows[y][3].ToString() != "")
{
if (y == 0 || (y > 0 && dt.Rows[y - 1][0].ToString() != "" && dt.Rows[y - 1][3].ToString() == ""))
{
if (y == 0)
{
for (int x = 0; x < title[0].Length; x++)
{
sheet.Cells[row, int.Parse(title[0][x])].Value = "'" + title[1][x];
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
sheet.Cells[row, int.Parse(title[0][x])].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
if (title[0][x] == "2")
{
sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Merge();
sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
}
}
}
row++; for (int x = 0; x < title[0].Length; x++)
{
switch (x)
{
case 0:
sheet.Cells[row, int.Parse(title[0][x])].Value = "Invoice ID: " + dt.Rows[y][0].ToString();
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignBottom;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
sheet.Cells[row, int.Parse(title[0][x])].Font.Underline = XlUnderlineStyle.xlUnderlineStyleSingle;
break;
case 2:
sheet.Cells[row, int.Parse(title[0][x])].Value = "Guest Name: " + dt.Rows[y][2].ToString();
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignBottom;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
break;
case 9:
sheet.Cells[row, int.Parse(title[0][x])].Value = "Cover: " + dt.Rows[y][1].ToString();
sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignBottom;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 20;
break;
}
}
}
row++; for (int x = 0; x < title[0].Length; x++)
{
//sheet.Cells[row, int.Parse(title[0][x])].Font.Bold = true;
sheet.Cells[row, int.Parse(title[0][x])].Font.Color = Color.Black;
sheet.Cells[row, int.Parse(title[0][x])].WrapText = false;
sheet.Cells[row, int.Parse(title[0][x])].Font.Size = 8;
sheet.Cells[row, int.Parse(title[0][x])].VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheet.Cells[row, int.Parse(title[0][x])].RowHeight = 15;
switch (x)
{
case 0:
sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][x + 3].ToString();
break;
case 1:
sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][x + 3].ToString();
sheet.Cells[row, int.Parse(title[0][x]), row, int.Parse(title[0][x + 1]) - 1].Merge();
break;
case 2:
sheet.Cells[row, int.Parse(title[0][x])].Value = dt.Rows[y][x + 3].ToString();
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter;
break;
case 3:
sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x + 3].ToString());
sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0";
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignCenter;
break;
default:
sheet.Cells[row, int.Parse(title[0][x])].Value = double.Parse(dt.Rows[y][x + 3].ToString());
sheet.Cells[row, int.Parse(title[0][x])].NumberFormat = "###,###,##0";
sheet.Cells[row, int.Parse(title[0][x])].HorizontalAlignment = XlHAlign.xlHAlignRight;
break;
}
}
}
}
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = sheet.Range["A1"];
// hide row A5
range.Rows.Hidden = true;
// font bold header
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
//string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser("sale");
string l_pk = Request["master_pk"];
string TemplateFile = "rpt_dscd00075.xls";
string TempFile = "../../../../system/temp/rpt_dscd00075_" + 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 + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00075_2", "'" + Session["User_ID"].ToString() + "'");
DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00075", para);
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00075_1", para);
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells[2, 2].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtInfo.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 131, 71));
}
exSheet.Cells[3, 15].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
exSheet.Cells[5, 4].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells[5, 12].Value = dtMaster.Rows[0]["slip_no"].ToString();
exSheet.Cells[6, 4].Value = dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells[6, 12].Value = dtMaster.Rows[0]["out_date"].ToString();
exSheet.Cells[7, 4].Value = dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells[7, 12].Value = dtMaster.Rows[0]["description"].ToString();
exSheet.Cells[8, 4].Value = dtInfo.Rows[0]["phone_no"].ToString();
//---------
exSheet.Cells[10, 4].Value = dtMaster.Rows[0]["bill_partner_name"].ToString();
exSheet.Cells[10, 12].Value = dtMaster.Rows[0]["deli_partner_name"].ToString();
exSheet.Cells[11, 4].Value = dtMaster.Rows[0]["short_nm"].ToString();
exSheet.Cells[11, 12].Value = dtMaster.Rows[0]["deli_addr1"].ToString();
exSheet.Cells[12, 4].Value = dtMaster.Rows[0]["bill_addr1"].ToString();
exSheet.Cells[13, 4].Value = dtMaster.Rows[0]["tax_code"].ToString();
exSheet.Cells[13, 6].Value = dtMaster.Rows[0]["phone_no"].ToString();
exSheet.Cells[14, 4].Value = dtMaster.Rows[0]["po_date"].ToString();
exSheet.Cells[15, 4].Value = dtMaster.Rows[0]["po_no"].ToString();
/*exSheet.Cells[11, 2].Value = dtMst.Rows[0]["charger"].ToString();
* exSheet.Cells[12, 2].Value = dtMst.Rows[0]["department_name"].ToString();
* exSheet.Cells[20, 2].Value = dtMst.Rows[0]["partner_name"].ToString();
* exSheet.Cells[20, 3].Value = dtMst.Rows[0]["ACCOUNT_NO"].ToString();
* exSheet.Cells[22, 2].Value = dtMst.Rows[0]["BEDEFI_BANK_NM"].ToString();
* exSheet.Cells[17, 6].Value = dtMst.Rows[0]["PAY_METHOD"].ToString();
*
* exSheet.Cells[13, 4].Value = "Amount (" + dtMst.Rows[0]["REQ_CCY"].ToString() + " )";
*
*
* exSheet.Cells[15, 4].Value = long.Parse(dtTotal.Rows[0][0].ToString());//total
* exSheet.Cells[15, 4, 15, 4].NumberFormat = "#,##0";
*
* exSheet.Cells[17, 4].Value = long.Parse(dtTotal.Rows[0][0].ToString());//total rerurn company
* exSheet.Cells[17, 4, 17, 4].NumberFormat = "#,##0";*/
//----------------------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A22"].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
for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++)
{
for (int col = 0; col <= 14; col++)
{
if (col == 0) // seq
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["rownum"].ToString();
}
else if (col == 1) // item code
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["item_code"].ToString();
}
else if (col == 2) // item name
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["item_name"].ToString();
exSheet.Cells[20 + l_row, 3, 20 + l_row, 5].Merge();
}
else if (col == 5) // uom
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["out_uom"].ToString();
}
else if (col == 6) // qty
{
exSheet.Cells[20 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["out_qty"]);
}
else if (col == 7) // unit_price
{
exSheet.Cells[20 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["unit_price"]);
}
else if (col == 8) // discount_rate
{
exSheet.Cells[20 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["discount_rate"]);
}
else if (col == 9) // discount_amount
{
exSheet.Cells[20 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["discount_amount"]);
exSheet.Cells[20 + l_row, 10, 20 + l_row, 12].Merge();
}
else if (col == 12) // total_amount
{
exSheet.Cells[20 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["total_amount"]);
}
else if (col == 13) // ref_no
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["ref_no"].ToString();
}
else if (col == 14) // description
{
exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["description"].ToString();
}
/* if (col == 0)
* {
* exSheet.Cells[20 + l_row, col].Value = l_row; // no
* }
* else
* {
* exSheet.Cells[20 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["item_code"].ToString();
* }
*
* else
* {
* exSheet.Cells[12 + l_row, col + 1].Value = long.Parse(dtDetail.Rows[l_row - 1][col].ToString());
* exSheet.Cells[13, col + 1, l_row + 13, col + 1].NumberFormat = "#,##0";
* exSheet.Cells[l_row + 12, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight;
* exSheet.Range[13, col + 1, l_row + 13, 6].Font.Bold = false;
* }
*
* exSheet.Cells[l_row + 12, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 3].HorizontalAlignment = XlHAlign.xlHAlignLeft;
* exSheet.Cells[l_row + 13, 5].HorizontalAlignment = XlHAlign.xlHAlignLeft;
* exSheet.Cells[l_row + 13, 6].HorizontalAlignment = XlHAlign.xlHAlignLeft;*/
}
}
exSheet.Cells[dtDetail.Rows.Count + 21, 7].Value = "=SUM(G21:G" + (dtDetail.Rows.Count + 20) + ")";
exSheet.Cells[dtDetail.Rows.Count + 21, 10].Value = "=SUM(J21:J" + (dtDetail.Rows.Count + 20) + ")";
exSheet.Cells[dtDetail.Rows.Count + 21, 13].Value = "=SUM(M21:M" + (dtDetail.Rows.Count + 20) + ")";
exSheet.Cells[dtDetail.Rows.Count + 22, 13].Value = "=SUM(M21:M" + (dtDetail.Rows.Count + 20) + ")*10%";
exSheet.Cells[dtDetail.Rows.Count + 23, 13].Value = "=SUM(M21:M" + (dtDetail.Rows.Count + 20) + ")+ (SUM(M21:M" + (dtDetail.Rows.Count + 20) + ")*10%)";
// end loop detail not percent
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
CtlLib.ExcelToPdf(TempFile);
string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
int C_GRP_NM = 1;
int C_ITEM_CODE = 2;
int C_ITEM_NAME = 3;
int C_UOM = 4;
int C_STOCK_QTY = 5;
int C_WH_1 = 6;
int C_WH_2 = 7;
int C_WH_3 = 8;
int C_WH_4 = 9;
int C_WH_5 = 10;
int C_WH_6 = 11;
int C_WH_7 = 12;
int C_WH_8 = 13;
int C_WH_9 = 14;
int C_WH_10 = 15;
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string TemplateFile = "rpt_bisc00140_grand.xls";
string TempFile = "../../../../system/temp/rpt_bisc00140_grand_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string p_lang = Request.QueryString["p_lang"];
string p_item_grp = Request.QueryString["p_item_grp"];
string p_item = Request.QueryString["p_item"];
string p_rad_type = Request.QueryString["p_rad_type"];
string p_dt_from = Request.QueryString["p_dt_from"];
string p_wh_pk_1 = Request.QueryString["p_wh_pk_1"];
string p_wh_pk_2 = Request.QueryString["p_wh_pk_2"];
string p_wh_pk_3 = Request.QueryString["p_wh_pk_3"];
string p_wh_pk_4 = Request.QueryString["p_wh_pk_4"];
string p_wh_pk_5 = Request.QueryString["p_wh_pk_5"];
string p_wh_pk_6 = Request.QueryString["p_wh_pk_6"];
string p_wh_pk_7 = Request.QueryString["p_wh_pk_7"];
string p_wh_pk_8 = Request.QueryString["p_wh_pk_8"];
string p_wh_pk_9 = Request.QueryString["p_wh_pk_9"];
string p_wh_pk_10 = Request.QueryString["p_wh_pk_10"];
string p_storage = Request.QueryString["p_storage"];
string p_wh_type = Request.QueryString["p_wh_type"];
string paraHeader = "'" + p_storage +
"','" + p_wh_type + "'";
string para = "'" + p_lang +
"','" + p_item_grp +
"','" + p_item +
"','" + p_rad_type +
"','" + p_dt_from +
"','" + p_wh_pk_1 +
"','" + p_wh_pk_2 +
"','" + p_wh_pk_3 +
"','" + p_wh_pk_4 +
"','" + p_wh_pk_5 +
"','" + p_wh_pk_6 +
"','" + p_wh_pk_7 +
"','" + p_wh_pk_8 +
"','" + p_wh_pk_9 +
"','" + p_wh_pk_10 + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00140_2", "'" + Session["User_ID"].ToString() + "'");
DataTable dtHeader = CtlLib.TableReadOpenCursor("st_lg_rpt_bisc00140", paraHeader);
DataTable dtDetail = CtlLib.TableReadOpenCursor("lg_sel_bisc00140_1", para);
//Set Data In Master
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells[1, 1].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtInfo.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 100, 55));
}
exSheet.Cells["C1"].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells["C2"].Value = dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells["C3"].Value = "Tax code: " + dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells["I1"].Value = "Tell: " + dtInfo.Rows[0]["phone_no"].ToString();
exSheet.Cells["I1"].Value = "Fax: " + dtInfo.Rows[0]["fax_no"].ToString();
exSheet.Cells["I1"].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
string strFromDt = System.DateTime.ParseExact(p_dt_from, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
exSheet.Cells["E6"].Value = String.Format("Date: {0}", strFromDt);
//Set data for header
if (dtHeader.Rows != null && dtHeader.Rows.Count > 0)
{
exSheet.Cells[7, C_WH_1].Value = dtHeader.Rows[0]["wh_01_nm"];
exSheet.Cells[7, C_WH_2].Value = dtHeader.Rows[0]["wh_02_nm"];
exSheet.Cells[7, C_WH_3].Value = dtHeader.Rows[0]["wh_03_nm"];
exSheet.Cells[7, C_WH_4].Value = dtHeader.Rows[0]["wh_04_nm"];
exSheet.Cells[7, C_WH_5].Value = dtHeader.Rows[0]["wh_05_nm"];
exSheet.Cells[7, C_WH_6].Value = dtHeader.Rows[0]["wh_06_nm"];
exSheet.Cells[7, C_WH_7].Value = dtHeader.Rows[0]["wh_07_nm"];
exSheet.Cells[7, C_WH_8].Value = dtHeader.Rows[0]["wh_08_nm"];
exSheet.Cells[7, C_WH_9].Value = dtHeader.Rows[0]["wh_09_nm"];
exSheet.Cells[7, C_WH_10].Value = dtHeader.Rows[0]["wh_10_nm"];
}
//insert row new of sheet
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A9"].Rows.EntireRow.Insert();
}
//sum variable
double sum_qty1 = 0, sum_qty2 = 0, sum_qty3 = 0, sum_qty4 = 0, sum_qty5 = 0;
double sum_qty6 = 0, sum_qty7 = 0, sum_qty8 = 0, sum_qty9 = 0, sum_qty10 = 0;
double tmp_qty1 = 0, tmp_qty2 = 0, tmp_qty3 = 0, tmp_qty4 = 0, tmp_qty5 = 0;
double tmp_qty6 = 0, tmp_qty7 = 0, tmp_qty8 = 0, tmp_qty9 = 0, tmp_qty10 = 0;
double sum_qty1_sub = 0, sum_qty2_sub = 0, sum_qty3_sub = 0, sum_qty4_sub = 0, sum_qty5_sub = 0;
double sum_qty6_sub = 0, sum_qty7_sub = 0, sum_qty8_sub = 0, sum_qty9_sub = 0, sum_qty10_sub = 0;
//Set data
int l_pos = 7;
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
l_pos += 1;
if (i > 0 && dtDetail.Rows[i]["tlg_it_itemgrp_pk"].ToString() != dtDetail.Rows[i - 1]["tlg_it_itemgrp_pk"].ToString())
{
exSheet.Cells[l_pos, C_WH_1].Value = String.Format("{0:0,0}", sum_qty1_sub);
exSheet.Cells[l_pos, C_WH_2].Value = String.Format("{0:0,0}", sum_qty2_sub);
exSheet.Cells[l_pos, C_WH_3].Value = String.Format("{0:0,0}", sum_qty3_sub);
exSheet.Cells[l_pos, C_WH_4].Value = String.Format("{0:0,0}", sum_qty4_sub);
exSheet.Cells[l_pos, C_WH_5].Value = String.Format("{0:0,0}", sum_qty5_sub);
exSheet.Cells[l_pos, C_WH_6].Value = String.Format("{0:0,0}", sum_qty6_sub);
exSheet.Cells[l_pos, C_WH_7].Value = String.Format("{0:0,0}", sum_qty7_sub);
exSheet.Cells[l_pos, C_WH_8].Value = String.Format("{0:0,0}", sum_qty8_sub);
exSheet.Cells[l_pos, C_WH_9].Value = String.Format("{0:0,0}", sum_qty9_sub);
exSheet.Cells[l_pos, C_WH_10].Value = String.Format("{0:0,0}", sum_qty10_sub);
FormatTotalRange(exSheet, l_pos, C_GRP_NM, l_pos, C_WH_10);
exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].Merge();
l_pos += 1;
sum_qty1_sub = 0; sum_qty2_sub = 0; sum_qty3_sub = 0; sum_qty4_sub = 0; sum_qty5_sub = 0;
sum_qty6_sub = 0; sum_qty7_sub = 0; sum_qty8_sub = 0; sum_qty9_sub = 0; sum_qty10_sub = 0;
}
exSheet.Cells[l_pos, C_GRP_NM].Value = dtDetail.Rows[i]["grp_nm"];
exSheet.Cells[l_pos, C_ITEM_CODE].Value = dtDetail.Rows[i]["item_code"];
exSheet.Cells[l_pos, C_ITEM_NAME].Value = dtDetail.Rows[i]["item_name"];
exSheet.Cells[l_pos, C_UOM].Value = dtDetail.Rows[i]["uom"];
exSheet.Cells[l_pos, C_STOCK_QTY].Value = dtDetail.Rows[i]["stock_qty"];
exSheet.Cells[l_pos, C_WH_1].Value = dtDetail.Rows[i]["qty1"];
exSheet.Cells[l_pos, C_WH_2].Value = dtDetail.Rows[i]["qty2"];
exSheet.Cells[l_pos, C_WH_3].Value = dtDetail.Rows[i]["qty3"];
exSheet.Cells[l_pos, C_WH_4].Value = dtDetail.Rows[i]["qty4"];
exSheet.Cells[l_pos, C_WH_5].Value = dtDetail.Rows[i]["qty5"];
exSheet.Cells[l_pos, C_WH_6].Value = dtDetail.Rows[i]["qty6"];
exSheet.Cells[l_pos, C_WH_7].Value = dtDetail.Rows[i]["qty7"];
exSheet.Cells[l_pos, C_WH_8].Value = dtDetail.Rows[i]["qty8"];
exSheet.Cells[l_pos, C_WH_9].Value = dtDetail.Rows[i]["qty9"];
exSheet.Cells[l_pos, C_WH_10].Value = dtDetail.Rows[i]["qty10"];
FormatNormalRange(exSheet, l_pos, C_GRP_NM, l_pos, C_WH_10);
if (double.TryParse(dtDetail.Rows[i]["qty1"].ToString(), out tmp_qty1))
{
sum_qty1_sub += tmp_qty1;
sum_qty1 += tmp_qty1;
}
if (double.TryParse(dtDetail.Rows[i]["qty2"].ToString(), out tmp_qty2))
{
sum_qty2_sub += tmp_qty2;
sum_qty2 += tmp_qty2;
}
if (double.TryParse(dtDetail.Rows[i]["qty3"].ToString(), out tmp_qty3))
{
sum_qty3_sub += tmp_qty3;
sum_qty3 += tmp_qty3;
}
if (double.TryParse(dtDetail.Rows[i]["qty4"].ToString(), out tmp_qty4))
{
sum_qty4_sub += tmp_qty4;
sum_qty4 += tmp_qty4;
}
if (double.TryParse(dtDetail.Rows[i]["qty5"].ToString(), out tmp_qty5))
{
sum_qty5_sub += tmp_qty5;
sum_qty5 += tmp_qty5;
}
if (double.TryParse(dtDetail.Rows[i]["qty6"].ToString(), out tmp_qty6))
{
sum_qty6_sub += tmp_qty6;
sum_qty6 += tmp_qty6;
}
if (double.TryParse(dtDetail.Rows[i]["qty7"].ToString(), out tmp_qty7))
{
sum_qty7_sub += tmp_qty7;
sum_qty7 += tmp_qty7;
}
if (double.TryParse(dtDetail.Rows[i]["qty8"].ToString(), out tmp_qty8))
{
sum_qty8_sub += tmp_qty8;
sum_qty8 += tmp_qty8;
}
if (double.TryParse(dtDetail.Rows[i]["qty9"].ToString(), out tmp_qty9))
{
sum_qty9_sub += tmp_qty9;
sum_qty9 += tmp_qty9;
}
if (double.TryParse(dtDetail.Rows[i]["qty10"].ToString(), out tmp_qty10))
{
sum_qty10_sub += tmp_qty10;
sum_qty10 += tmp_qty10;
}
}
l_pos += 1;
exSheet.Cells[l_pos, C_WH_1].Value = String.Format("{0:0,0}", sum_qty1_sub);
exSheet.Cells[l_pos, C_WH_2].Value = String.Format("{0:0,0}", sum_qty2_sub);
exSheet.Cells[l_pos, C_WH_3].Value = String.Format("{0:0,0}", sum_qty3_sub);
exSheet.Cells[l_pos, C_WH_4].Value = String.Format("{0:0,0}", sum_qty4_sub);
exSheet.Cells[l_pos, C_WH_5].Value = String.Format("{0:0,0}", sum_qty5_sub);
exSheet.Cells[l_pos, C_WH_6].Value = String.Format("{0:0,0}", sum_qty6_sub);
exSheet.Cells[l_pos, C_WH_7].Value = String.Format("{0:0,0}", sum_qty7_sub);
exSheet.Cells[l_pos, C_WH_8].Value = String.Format("{0:0,0}", sum_qty8_sub);
exSheet.Cells[l_pos, C_WH_9].Value = String.Format("{0:0,0}", sum_qty9_sub);
exSheet.Cells[l_pos, C_WH_10].Value = String.Format("{0:0,0}", sum_qty10_sub);
FormatTotalRange(exSheet, l_pos, C_GRP_NM, l_pos, C_WH_10);
exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].Merge();
l_pos += 1;
exSheet.Cells[l_pos, C_WH_1].Value = String.Format("{0:0,0}", sum_qty1);
exSheet.Cells[l_pos, C_WH_2].Value = String.Format("{0:0,0}", sum_qty2);
exSheet.Cells[l_pos, C_WH_3].Value = String.Format("{0:0,0}", sum_qty3);
exSheet.Cells[l_pos, C_WH_4].Value = String.Format("{0:0,0}", sum_qty4);
exSheet.Cells[l_pos, C_WH_5].Value = String.Format("{0:0,0}", sum_qty5);
exSheet.Cells[l_pos, C_WH_6].Value = String.Format("{0:0,0}", sum_qty6);
exSheet.Cells[l_pos, C_WH_7].Value = String.Format("{0:0,0}", sum_qty7);
exSheet.Cells[l_pos, C_WH_8].Value = String.Format("{0:0,0}", sum_qty8);
exSheet.Cells[l_pos, C_WH_9].Value = String.Format("{0:0,0}", sum_qty9);
exSheet.Cells[l_pos, C_WH_10].Value = String.Format("{0:0,0}", sum_qty10);
FormatTotalRange(exSheet, l_pos, C_GRP_NM, l_pos, C_WH_10);
exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].Merge();
exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].HorizontalAlignment = XlHAlign.xlHAlignCenter;
exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].Value = "GRAND TOTAL";
exSheet.Cells[l_pos, C_GRP_NM, l_pos, C_STOCK_QTY].Font.Bold = false;
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string l_dt_frm = "", l_dt_departure = "", l_parameter = "", l_guest_name = "", l_total_room = "", l_report_type = "";
l_dt_frm = Request["p_dt_frm"];
l_dt_departure = Request["p_dt_departure"];
l_guest_name = Request["p_guest_name"];
l_total_room = Request["p_count_RM"];
l_report_type = Request["p_report_type"];
l_parameter = "'" + l_dt_frm + "','" + l_dt_departure + "','" + l_guest_name + "','" + l_report_type + "'";
string TemplateFile = "rpt_60300010.xls";
string TempFile = "../../../../system/temp/rpt_60300010_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
DataTable dt;
dt = CtlLib.TableReadOpenCursor("ht_rpt_60300010_arrival", l_parameter);
//-----header---
//---end--header---
//--set-----DataBind--in---Master---
if (dt.Rows.Count == 0)
{
exSheet.Cells[6, 1].Value = "There is not data!";
}
if (dt.Rows.Count > 0)
{
if (l_report_type == "1")
{
exSheet.Cells[1, 5].Value = "Arrival list ";
}
if (l_report_type == "2")
{
exSheet.Cells[1, 5].Value = "Arrival list (Normal)";
}
if (l_report_type == "3")
{
exSheet.Cells[1, 5].Value = "Arrival list (VIP)";
}
exSheet.Cells[2, 1].Value = dt.Rows[0]["date_para"].ToString();
exSheet.Cells[2, 14].Value = dt.Rows[0]["print_time"].ToString();
// exSheet.Cells[4, 12].Value = dt.Rows[0]["date_header"].ToString();
}
//----------------------
////end-set-----DataBind--in---Master---
int l_row_first = 4;
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A5"].Rows.EntireRow.Insert();//insert row new of sheet
}
for (int l_row = 0; l_row < dt.Rows.Count; l_row++)
{
exSheet.Cells[l_row_first + l_row, 1].Value = dt.Rows[l_row]["company_name"].ToString();
exSheet.Cells[l_row_first + l_row, 2].Value = dt.Rows[l_row]["last_name"].ToString();
exSheet.Cells[l_row_first + l_row, 3].Value = dt.Rows[l_row]["from_date"].ToString();
exSheet.Cells[l_row_first + l_row, 4].Value = dt.Rows[l_row]["date_to"].ToString();
if (dt.Rows[l_row]["total_day"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 5].Value = double.Parse(dt.Rows[l_row]["total_day"].ToString());
}
if (dt.Rows[l_row]["room_rate"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 6].Value = double.Parse(dt.Rows[l_row]["room_rate"].ToString());
}
exSheet.Cells[l_row_first + l_row, 7].Value = dt.Rows[l_row]["room_type"].ToString();
if (dt.Rows[l_row]["room_no"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 8].Value = double.Parse(dt.Rows[l_row]["room_no"].ToString());
}
//exSheet.Cells[l_row_first + l_row, 9].Value = dt.Rows[l_row]["contact_person"].ToString();
if (dt.Rows[l_row]["total_adult"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 9].Value = double.Parse(dt.Rows[l_row]["total_adult"].ToString());
}
if (dt.Rows[l_row]["total_child"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 10].Value = double.Parse(dt.Rows[l_row]["total_child"].ToString());
}
if (dt.Rows[l_row]["slip_no"].ToString() != "")
{
exSheet.Cells[l_row_first + l_row, 11].Value = dt.Rows[l_row]["slip_no"].ToString();
}
exSheet.Cells[l_row_first + l_row, 12].Value = dt.Rows[l_row]["detail_request"].ToString();
exSheet.Cells[l_row_first + l_row, 13].Value = dt.Rows[l_row]["vip_type"].ToString();
exSheet.Cells[l_row_first + l_row, 14].Value = dt.Rows[l_row]["description"].ToString();
//exSheet.Cells[l_row_first + l_row, 15].Value = dt.Rows[l_row]["description"].ToString();
exSheet.Cells[l_row_first + l_row, 16].Value = dt.Rows[l_row]["nation"].ToString();
exSheet.Cells[l_row_first + l_row, 17].Value = dt.Rows[l_row]["group_name"].ToString();
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
// range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
//string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
//string l_pk = Request["master_pk"];
string TemplateFile = "rpt_bisc00070_happycook.xls";
string TempFile = "../../../../system/temp/rpt_bisc00070_happycook_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
//string para = "'" + l_pk + "'";
string p_from_date = Request["p_from_date"];
string p_to_date = Request["p_to_date"];
string p_wh_type = Request["p_wh_type"];
string p_tin_warehouse_pk = Request["p_tin_warehouse_pk"];
string p_item_group_pk = Request["p_item_group_pk"];
string p_item = Request["p_item"];
string p_in_out_yn = Request["p_in_out_yn"];
string p_wh_name = Request["p_wh_name"];
string para = "'" + p_from_date + "','" + p_to_date + "','" + p_wh_type + "','" + p_tin_warehouse_pk + "','" + p_item_group_pk + "','" + p_item + "','" + p_in_out_yn + "'";
DataTable dtInfo = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00070", "'" + Session["User_ID"].ToString() + "'");
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_rpt_BISC00070_HAPPY_1", para);
//--set-----DataBind--in---Master---
if (!string.IsNullOrEmpty(dtInfo.Rows[0]["pic"].ToString()))
{
exSheet.Cells[1, 1].Select();
byte[] MyData = new byte[0];
MyData = (byte[])dtInfo.Rows[0]["pic"];
MemoryStream stream = new MemoryStream(MyData);
Bitmap b = new Bitmap(stream);
exSheet.Shapes.AddPicture(ResizeImage(b, 131, 71));
}
exSheet.Cells[1, 3].Value = dtInfo.Rows[0]["partner_name"].ToString();
exSheet.Cells[2, 3].Value = dtInfo.Rows[0]["addr1"].ToString();
exSheet.Cells[3, 3].Value = "Tax code:" + dtInfo.Rows[0]["tax_code"].ToString();
exSheet.Cells[1, 13].Value = "Tell:" + dtInfo.Rows[0]["phone_no"].ToString();
exSheet.Cells[2, 13].Value = "Fax:" + dtInfo.Rows[0]["fax_no"].ToString();
exSheet.Cells[3, 13].Value = "Print date: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm");
exSheet.Cells[5, 13].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");
//---------
/*exSheet.Cells[10, 4].Value = dtMaster.Rows[0]["bill_partner_name"].ToString();
* exSheet.Cells[10, 12].Value = dtMaster.Rows[0]["deli_partner_name"].ToString();
*
* exSheet.Cells[11, 4].Value = dtMaster.Rows[0]["short_nm"].ToString();
* exSheet.Cells[11, 12].Value = dtMaster.Rows[0]["deli_addr1"].ToString();
*
* exSheet.Cells[12, 4].Value = dtMaster.Rows[0]["bill_addr1"].ToString();
* exSheet.Cells[13, 4].Value = dtMaster.Rows[0]["tax_code"].ToString();
* exSheet.Cells[13, 6].Value = dtMaster.Rows[0]["phone_no"].ToString();
*
* exSheet.Cells[14, 4].Value = dtMaster.Rows[0]["po_date"].ToString();
*
* exSheet.Cells[15, 4].Value = dtMaster.Rows[0]["po_no"].ToString();
* exSheet.Cells[11, 2].Value = dtMst.Rows[0]["charger"].ToString();
* exSheet.Cells[12, 2].Value = dtMst.Rows[0]["department_name"].ToString();
* exSheet.Cells[8, 2].Value = dtMst.Rows[0]["partner_name"].ToString();
* exSheet.Cells[8, 3].Value = dtMst.Rows[0]["ACCOUNT_NO"].ToString();
* exSheet.Cells[22, 2].Value = dtMst.Rows[0]["BEDEFI_BANK_NM"].ToString();
* exSheet.Cells[17, 6].Value = dtMst.Rows[0]["PAY_METHOD"].ToString();
*
* exSheet.Cells[13, 4].Value = "Amount (" + dtMst.Rows[0]["REQ_CCY"].ToString() + " )";
*
*
* exSheet.Cells[15, 4].Value = long.Parse(dtTotal.Rows[0][0].ToString());//total
* exSheet.Cells[15, 4, 15, 4].NumberFormat = "#,##0";
*
* exSheet.Cells[17, 4].Value = long.Parse(dtTotal.Rows[0][0].ToString());//total rerurn company
* exSheet.Cells[17, 4, 17, 4].NumberFormat = "#,##0";*/
//----------------------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A10"].Rows.EntireRow.Insert();//insert row new of sheet
}
//set date title of year
//exSheet.Cells[2, 1].Value = "Income Statement Year" + " " + l_year;
//set data to detail.
// loop detail not percent
for (int l_row = 1; l_row <= dtDetail.Rows.Count; l_row++)
{
for (int col = 0; col <= 14; col++)
{
if (col == 0) // seq
{
exSheet.Cells[8 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["rownum"].ToString();
}
else if (col == 1) // item code
{
exSheet.Cells[8 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["item_code"].ToString();
}
else if (col == 2) // uom
{
exSheet.Cells[8 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["uom"].ToString();
//exSheet.Cells[8 + l_row, 3, 8 + l_row, 5].Merge();
}
else if (col == 3) // begin qty
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["begin_qty"]);
}
else if (col == 4) // Production
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["prod"]);
}
else if (col == 5) // other in
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["other_in"]);
}
else if (col == 6) //buy
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["buy"]);
}
else if (col == 7) // transfer_in
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["transfer_in"]);
}
else if (col == 8) // assembly_in
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["assembly_in"]);
}
else if (col == 9) // return
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToDouble(dtDetail.Rows[l_row - 1]["return"]);
}
else if (col == 10) // total_in
{
exSheet.Cells[8 + l_row, col + 1].Value = "=SUM(E" + (8 + l_row) + ":J" + (8 + l_row) + ")";
}
else if (col == 11) // sale
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["sale"]);
}
else if (col == 12) // pro_order
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["pro_order"]);
}
else if (col == 13) // warranty
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["warranty"]);
}
else if (col == 14) // promote
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["promote"]);
}
else if (col == 15) // manager
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["manager"]);
}
else if (col == 16) // sample
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["sample"]);
}
else if (col == 17) // packing
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["packing"]);
}
else if (col == 18) // assembly_out
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["assembly_out"]);
}
else if (col == 19) // transfer_out
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["transfer_out"]);
}
else if (col == 8) // gift
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["gift"]);
}
else if (col == 21) // export
{
exSheet.Cells[8 + l_row, col + 1].Value = Convert.ToInt32(dtDetail.Rows[l_row - 1]["export"]);
}
else if (col == 22) // total_out
{
exSheet.Cells[8 + l_row, col + 1].Value = "=SUM(L" + (8 + l_row) + ":V" + (8 + l_row) + ")";
}
else if (col == 23) // end_qty
{
exSheet.Cells[8 + l_row, col + 1].Value = "=D" + (8 + l_row) + "+ K" + (8 + l_row) + "- W" + (8 + l_row);
}
else if (col == 24) // description
{
exSheet.Cells[8 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["description"].ToString();
}
/* if (col == 0)
* {
* exSheet.Cells[8 + l_row, col].Value = l_row; // no
* }
* else
* {
* exSheet.Cells[8 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1]["item_code"].ToString();
* }
*
* else
* {
* exSheet.Cells[12 + l_row, col + 1].Value = long.Parse(dtDetail.Rows[l_row - 1][col].ToString());
* exSheet.Cells[13, col + 1, l_row + 13, col + 1].NumberFormat = "#,##0";
* exSheet.Cells[l_row + 12, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight;
* exSheet.Range[13, col + 1, l_row + 13, 6].Font.Bold = false;
* }
*
* exSheet.Cells[l_row + 12, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
* exSheet.Cells[l_row + 13, 3].HorizontalAlignment = XlHAlign.xlHAlignLeft;
* exSheet.Cells[l_row + 13, 5].HorizontalAlignment = XlHAlign.xlHAlignLeft;
* exSheet.Cells[l_row + 13, 6].HorizontalAlignment = XlHAlign.xlHAlignLeft;*/
}
}
exSheet.Cells[dtDetail.Rows.Count + 9, 4].Value = "=SUM(D9:D" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 5].Value = "=SUM(E9:E" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 6].Value = "=SUM(F9:F" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 7].Value = "=SUM(G9:G" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 8].Value = "=SUM(H9:H" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 9].Value = "=SUM(I9:I" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 10].Value = "=SUM(J9:J" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 11].Value = "=SUM(K9:K" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 12].Value = "=SUM(L9:L" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 13].Value = "=SUM(M9:M" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 14].Value = "=SUM(N9:N" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 15].Value = "=SUM(O9:O" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 16].Value = "=SUM(P9:P" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 17].Value = "=SUM(Q9:Q" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 18].Value = "=SUM(R9:R" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 19].Value = "=SUM(S9:S" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 20].Value = "=SUM(T9:T" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 21].Value = "=SUM(U9:U" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 22].Value = "=SUM(V9:V" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 23].Value = "=SUM(W9:W" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells[dtDetail.Rows.Count + 9, 24].Value = "=SUM(X9:X" + (dtDetail.Rows.Count + 8) + ")";
//exSheet.Cells[dtDetail.Rows.Count + 22, 13].Value = "=SUM(M21:M" + (dtDetail.Rows.Count + 8) + ")*10%";
//exSheet.Cells[dtDetail.Rows.Count + 23, 13].Value = "=SUM(M21:M" + (dtDetail.Rows.Count + 8) + ")+ (SUM(M21:M" + (dtDetail.Rows.Count + 8) + ")*10%)";
// end loop detail not percent
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
CtlLib.ExcelToPdf(TempFile);
string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser("sale");
string TemplateFile = "rpt_dscd00030_ST02.xls";
string TempFile = "../../../../system/temp/rpt_dscd00030_ST02_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string p_from_date = Request["p_from_date"];
string p_to_date = Request["p_to_date"];
string p_order_type = Request["p_order_type"];
string p_po_vendor = Request["p_po_vendor"];
string p_item_group_pk = Request["p_item_group_pk"];
string p_item = Request["p_item"];
string p_type = Request["p_type"];
string p_inout_type = Request["p_inout_type"];
string p_wh_type = Request["p_wh_type"];
string p_wh_pk = Request["p_wh_pk"];
string para = "'" + p_from_date + "','" + p_to_date + "','" + Session["User_ID"].ToString() + "'";
DataTable dtMaster = CtlLib.TableReadOpenCursor("st_lg_rpt_dscd00030_ST02_1", para);
if (dtMaster.Rows.Count > 0)
{
exSheet.Cells["B2"].Value = dtMaster.Rows[0]["partner_name"];
exSheet.Cells["H2"].Value = "Tel: " + dtMaster.Rows[0]["phone_no"];
exSheet.Cells["B3"].Value = dtMaster.Rows[0]["addr1"];
exSheet.Cells["H3"].Value = "Fax: " + dtMaster.Rows[0]["fax_no"];
exSheet.Cells["B4"].Value = "Tax No: " + dtMaster.Rows[0]["tax_code"];
exSheet.Cells["H4"].Value = "Print Date: " + DateTime.Now.ToString("dd/MM/yyyy HH:mm");
}
exSheet.Cells["A7"].Value = System.DateTime.ParseExact(p_from_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy") + " ~ " + System.DateTime.ParseExact(p_to_date, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
para = "'" + p_from_date + "','" + p_to_date + "','" + p_order_type + "','" + p_po_vendor + "','" + p_item_group_pk + "','" + p_item + "','" + p_type + "','" + p_inout_type + "','" + p_wh_type + "','" + p_wh_pk + "'";
DataTable dtDetail = CtlLib.TableReadOpenCursor("st_lg_RPT_DSCD00030_ST02_2", para);
for (int r = 0; r < dtDetail.Rows.Count; r++)
{
if (r < dtDetail.Rows.Count - 2)
{
exSheet.Range["A" + (10 + r)].Rows.EntireRow.Insert();
}
for (int c = 0; c < dtDetail.Columns.Count; c++)
{
exSheet.Cells[9 + r, c + 1].Value = dtDetail.Rows[r][c];
}
}
// end loop detail not percent
if (dtDetail.Rows.Count > 0)
{
exSheet.Cells["F" + (dtDetail.Rows.Count + 9)].Value = "=SUM(F9:F" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells["G" + (dtDetail.Rows.Count + 9)].Value = "=SUM(G9:G" + (dtDetail.Rows.Count + 8) + ")";
exSheet.Cells["H" + (dtDetail.Rows.Count + 9)].Value = "=SUM(H9:H" + (dtDetail.Rows.Count + 8) + ")";
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
//string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string l_dtfrm = Request["p_dtfrom"];
string l_dtto = Request["p_dtto"];
string l_guest_name = Request["p_guest_name"];
string l_parameter = "'" + l_dtfrm + "','" + l_dtto + "','" + l_guest_name + "'";
string TemplateFile = "rpt_60280090_inhouse_log.xls";
string TempFile = "../../../../system/temp/rpt_60280090_inhouse_log_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
//DataTable dtMst = CtlLib.TableReadOpenCursor("acnt.sp_rpt_gfrp00010_mst", l_parameter);
DataTable dtDetail = CtlLib.TableReadOpenCursor("rpt_60280090", l_parameter);
//-----header---
//---end--header---
//--set-----DataBind--in---Master---
exSheet.Cells[3, 1].Value = dtDetail.Rows[0]["date_para"].ToString();
exSheet.Cells[3, 9].Value = dtDetail.Rows.Count;
exSheet.Cells[3, 10].Value = " Room(s)";
exSheet.Cells[3, 22].Value = dtDetail.Rows[0]["print_time"].ToString();
//----------------------
////end-set-----DataBind--in---Master---
//sumtotal
// not tax
//exSheet.Cells[13, 7].Value = long.Parse(dtTotal.Rows[0][0].ToString());//total
// exSheet.Cells[16, 7].Value = long.Parse(dtTotal.Rows[0][0].ToString());//grand total
//end sum total
for (int l_addrow = 1; l_addrow < dtDetail.Rows.Count; l_addrow++)
{
exSheet.Range["A7"].Rows.EntireRow.Insert();//insert row new of sheet
}
//set date title of year
//exSheet.Cells[2, 1].Value = "Income Statement Year" + " " + l_year;
//set data to detail.
// loop detail not percent
int l_firstt = 6;
for (int l_row = 0; l_row < dtDetail.Rows.Count; l_row++)
{
for (int col = 0; col <= 22; col++)
{
//exSheet.Cells[11 + l_row, col + 1].Value = dtDetail.Rows[l_row - 1][col].ToString();
//exSheet.Cells[5, col + 1].Value = dt.Rows[0][col].ToString();
if (col == 0 || col == 1 || col == 2 || col == 3 || col == 4 || col == 5 || col == 6 || col == 7 || col == 9 || col == 10 || col == 11 || col == 12 || col == 13 || col == 18 || col == 19 || col == 20 || col == 22)
{
exSheet.Cells[l_firstt + l_row, col + 1].Value = dtDetail.Rows[l_row][col].ToString();
}
else
{
if (dtDetail.Rows[l_row][col].ToString() != "")
{
exSheet.Cells[l_firstt + l_row, col + 1].Value = double.Parse(dtDetail.Rows[l_row][col].ToString());
}
}
}
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Send file string to client
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string p_user, p_workgroup, p_search, p_org, p_from_dt, p_to_dt, p_nation, p_lang;
p_user = Request["p_user"].ToString();
p_workgroup = Request["p_wg"].ToString();
p_search = Request["p_emp_search"].ToString();
p_org = Request["p_tco_org_pk"].ToString();
p_from_dt = Request["p_from_dt"].ToString();
p_to_dt = Request["p_to_dt"].ToString();
p_nation = Request["p_nation"].ToString();
p_lang = Request["p_lang"].ToString();
string TemplateFile;
if (p_lang == "ENG")
{
TemplateFile = "rpt_hrti00400_1_eng.xls";
}
else
{
TemplateFile = "rpt_hrti00400_1_vie.xls";
}
string TempFile = "../../../../system/temp/rpt_hrti00400_1_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
string para = "'" + p_org + "','" + p_workgroup + "','" + p_search + "','" + p_from_dt +
"','" + p_to_dt + "','" + p_nation + "','" + p_lang + "'";
DataTable dt_emp = CtlLib.TableReadOpenCursor("ST_HR_RPT_HRTI00400_1", para);
if (dt_emp.Rows.Count == 0)
{
Response.Write("Không có dữ liệu!");
Response.End();
}
int row_loop = 4;
int l_col = dt_emp.Columns.Count;
int count = 0;
for (int l_addrow = 1; l_addrow < dt_emp.Rows.Count; l_addrow++)
{
exSheet.Range[row_loop + l_addrow, 1].Rows.EntireRow.Insert(); //insert row new of sheet
}
if (p_from_dt != "" && p_to_dt != "")
{
if (p_lang == "ENG")
{
exSheet.Cells["A2"].Value = "From Date " + dt_emp.Rows[0][0].ToString() + " -----" + " To Date " + dt_emp.Rows[0][1].ToString();
}
else
{
exSheet.Cells["A2"].Value = "Từ ngày " + dt_emp.Rows[0][0].ToString() + " -----" + " Đến ngày " + dt_emp.Rows[0][1].ToString();
}
}
// xu ly du lieu do vao excel
if (dt_emp.Rows.Count > 0)
{
for (int l_row = 0; l_row < dt_emp.Rows.Count; l_row++)
{
count++;
exSheet.Cells[row_loop + l_row, 1].Value = count;
// do du lieu vao cac dong
for (int col = 2; col < l_col; col++)
{
if (IsNumeric(dt_emp.Rows[l_row][col].ToString()) && col != 3)
{
exSheet.Cells[row_loop + l_row, col].Value = double.Parse(dt_emp.Rows[l_row][col].ToString());
}
else
{
exSheet.Cells[row_loop + l_row, col].Value = dt_emp.Rows[l_row][col].ToString();
}
}
}
}
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
/*range = exSheet.Range["A1:AC1"];
* range.Rows[4].Font.Bold = true;*/
exBook.SaveAs(TempFile);
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
//Response.WriteFile(pdfFilePath);
Response.WriteFile(TempFile);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string p_year = Request["p_year"];
string TemplateFile = "rpt_60300010_nationality_customer_analysis.xls";
string TempFile = "../../../../system/temp/rpt_60300010_nationality_customer_analysis_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string para = "";
DataTable dt, dtSum;
para = "'" + p_year + "'";
dt = CtlLib.TableReadOpenCursor("rpt_60300010_bk_source_year", para);
//-----header---
//---end--header---
//--set-----DataBind--in---Master---
if (dt.Rows.Count == 0)
{
exSheet.Cells[6, 1].Value = "There is not data!";
}
/*if (dt.Rows.Count > 0)
* {
* exSheet.Cells[3, 1].Value = dt.Rows[0]["date_para"].ToString();
* exSheet.Cells[3, 9].Value = dt.Rows[0]["print_time"].ToString();
* exSheet.Cells[4, 11].Value = dt.Rows[0]["date_header"].ToString();
* }*/
//----------------------
////end-set-----DataBind--in---Master---
/*for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
* {
* exSheet.Range["A7"].Rows.EntireRow.Insert();//insert row new of sheet
*
* }*/
for (int l_addrow_1 = 0; l_addrow_1 < dt.Rows.Count - 1; l_addrow_1++) //add row show with data of table
{
exSheet.Range[5 + l_addrow_1 + 1, 1].Rows.EntireRow.Insert(); //insert row new of sheet
}
//set date title of year
//exSheet.Cells[2, 1].Value = "Income Statement Year" + " " + l_year;
//set data to detail.
// loop detail not percent
int l_cnt = dt.Rows.Count;
for (int l_row = 0; l_row < dt.Rows.Count; l_row++)
{
exSheet.Cells[3, 28].Value = "Total " + p_year;
exSheet.Cells[5 + l_row, 2].Value = dt.Rows[l_row]["nation"];
exSheet.Cells[5 + l_row, 3].Value = dt.Rows[l_row]["booking_source"];
exSheet.Cells[5 + l_row, 4].Value = dt.Rows[l_row]["mm1"];
exSheet.Cells[5 + l_row, 5].Value = dt.Rows[l_row]["rate_mm1"];
exSheet.Cells[5 + l_row, 6].Value = dt.Rows[l_row]["mm2"];
exSheet.Cells[5 + l_row, 7].Value = dt.Rows[l_row]["rate_mm2"];
exSheet.Cells[5 + l_row, 8].Value = dt.Rows[l_row]["mm3"];
exSheet.Cells[5 + l_row, 9].Value = dt.Rows[l_row]["rate_mm3"];
exSheet.Cells[5 + l_row, 10].Value = dt.Rows[l_row]["mm4"];
exSheet.Cells[5 + l_row, 11].Value = dt.Rows[l_row]["rate_mm4"];
exSheet.Cells[5 + l_row, 12].Value = dt.Rows[l_row]["mm5"];
exSheet.Cells[5 + l_row, 13].Value = dt.Rows[l_row]["rate_mm5"];
exSheet.Cells[5 + l_row, 14].Value = dt.Rows[l_row]["mm6"];
exSheet.Cells[5 + l_row, 15].Value = dt.Rows[l_row]["rate_mm6"];
exSheet.Cells[5 + l_row, 16].Value = dt.Rows[l_row]["mm7"];
exSheet.Cells[5 + l_row, 17].Value = dt.Rows[l_row]["rate_mm7"];
exSheet.Cells[5 + l_row, 18].Value = dt.Rows[l_row]["mm8"];
exSheet.Cells[5 + l_row, 19].Value = dt.Rows[l_row]["rate_mm8"];
exSheet.Cells[5 + l_row, 20].Value = dt.Rows[l_row]["mm9"];
exSheet.Cells[5 + l_row, 21].Value = dt.Rows[l_row]["rate_mm9"];
exSheet.Cells[5 + l_row, 22].Value = dt.Rows[l_row]["mm10"];
exSheet.Cells[5 + l_row, 23].Value = dt.Rows[l_row]["rate_mm10"];
exSheet.Cells[5 + l_row, 24].Value = dt.Rows[l_row]["mm11"];
exSheet.Cells[5 + l_row, 25].Value = dt.Rows[l_row]["rate_mm11"];
exSheet.Cells[5 + l_row, 26].Value = dt.Rows[l_row]["mm12"];
exSheet.Cells[5 + l_row, 27].Value = dt.Rows[l_row]["rate_mm12"];
exSheet.Cells[5 + l_row, 28].Value = dt.Rows[l_row]["total_year"];
exSheet.Cells[5 + l_row, 29].Value = dt.Rows[l_row]["rate_year"];
Color color2 = ColorTranslator.FromHtml("0xFFFFCC");
if (dt.Rows[l_row]["booking_source"].ToString() == "" && dt.Rows[l_row]["mm1"].ToString() != "")
{
exSheet.Cells[5 + l_row, 3, 5 + l_row, 29].Interior.Color = color2; //Color.Green;
}
if (dt.Rows[l_row]["nation"].ToString() != "" && dt.Rows[l_row]["booking_source"].ToString() == "" && dt.Rows[l_row]["mm1"].ToString() != "")
{
exSheet.Cells[5 + l_row, 3].Value = "Sub Total"; //SUBTOTAL;
exSheet.Cells[5 + l_row, 29].Value = "100%";
}
if (dt.Rows[l_row]["nation"].ToString() == "" && dt.Rows[l_row]["booking_source"].ToString() == "" && dt.Rows[l_row]["mm1"].ToString() != "")
{
exSheet.Cells[5 + l_row, 3].Value = "Total"; //TOTAL;
exSheet.Cells[5 + l_row, 29].Value = "100%";
exSheet.Cells[5 + l_row, 5].Value = "100%";
exSheet.Cells[5 + l_row, 7].Value = "100%";
exSheet.Cells[5 + l_row, 9].Value = "100%";
exSheet.Cells[5 + l_row, 11].Value = "100%";
exSheet.Cells[5 + l_row, 13].Value = "100%";
exSheet.Cells[5 + l_row, 15].Value = "100%";
exSheet.Cells[5 + l_row, 17].Value = "100%";
exSheet.Cells[5 + l_row, 19].Value = "100%";
exSheet.Cells[5 + l_row, 21].Value = "100%";
exSheet.Cells[5 + l_row, 23].Value = "100%";
exSheet.Cells[5 + l_row, 25].Value = "100%";
exSheet.Cells[5 + l_row, 27].Value = "100%";
}
if (l_row > 1 && l_row < dt.Rows.Count)
{
if (dt.Rows[l_row - 1]["nation"].ToString() == dt.Rows[l_row]["nation"].ToString())
{
exSheet.Range[l_row + 4, 2, l_row + 5, 2].Merge();
//l_long_term=l_long_term+1;
}
}
if (l_row > 0 && l_row == 1)
{
if (dt.Rows[l_row - 1]["nation"].ToString() == dt.Rows[l_row]["nation"].ToString())
{
exSheet.Range[l_row + 4, 2, l_row + 5, 2].Merge();
//l_long_term=l_long_term+1;
}
}
}
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
// range.Rows.Hidden = true;
// font bold header
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
//string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
string l_room_no_guest_group = Request["p_room"];
string TemplateFile = "rpt_60250030_inhouse_list2.xls";
string TempFile = "../../../../system/temp/rpt_60250030_inhouse_list2_" + Session["User_ID"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
//bind data to excel file
string para = "";
DataTable dt, dtSum;
para = "'" + l_room_no_guest_group + "'";
dt = CtlLib.TableReadOpenCursor("ht_rpt_60250030_new", para);
//-----header---
//---end--header---
//--set-----DataBind--in---Master---
if (dt.Rows.Count == 0)
{
exSheet.Cells[3, 3].Value = "There is not data!";
}
if (dt.Rows.Count > 0)
{
// exSheet.Cells[3, 1].Value = "Total : "+dt.Rows.Count+" Rooms.";
exSheet.Cells[3, 8].Value = dt.Rows[0]["print_time"].ToString();
}
//----------------------
////end-set-----DataBind--in---Master---
for (int l_addrow = 1; l_addrow < dt.Rows.Count; l_addrow++)
{
exSheet.Range["A6"].Rows.EntireRow.Insert();//insert row new of sheet
}
//set date title of year
//exSheet.Cells[2, 1].Value = "Income Statement Year" + " " + l_year;
//set data to detail.
// loop detail not percent
int l_total_room = 0, l_total_guest = 0;
if (dt.Rows.Count > 0)
{
int l_cnt = dt.Rows.Count;
for (int l_row = 1; l_row <= dt.Rows.Count; l_row++)
{
for (int col = 0; col <= 7; col++)
{
if (col == 3)
{
if (int.Parse(dt.Rows[l_row - 1][col].ToString()) > 999)
{
l_total_room = l_total_room + 1;
}
}
if (col == 4)
{
l_total_guest = l_total_guest + int.Parse(dt.Rows[l_row - 1][col].ToString());
}
if (col == 0 || col == 1 || col == 2 || col == 3 || col == 5 || col == 6 || col == 7)
{
exSheet.Cells[4 + l_row, col + 1].Value = dt.Rows[l_row - 1][col].ToString();
exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
//exSheet.Range[5, col + 1, l_row + 5, 7].Font.Bold = false;
}
else
{
exSheet.Cells[4 + l_row, col + 1].Value = double.Parse(dt.Rows[l_row - 1][col].ToString());
exSheet.Cells[4, col + 1, l_row + 4, col + 1].NumberFormat = "#,##0";
exSheet.Cells[l_row + 4, col + 1].HorizontalAlignment = XlHAlign.xlHAlignRight;
}
}
}
}
exSheet.Cells[3, 1].Value = "Total : " + l_total_room + " Room(s.)";
//exSheet.Cells[3, 6].Value = "Total : "+l_total_guest+" Guest(s.).";
// end loop detail percent
if (File.Exists(TempFile))
{
File.Delete(TempFile);
}
// hide row firts
range = exSheet.Range["A1"];
// hide row A5
range.Rows.Hidden = true;
// font bold header
exBook.SaveAs(TempFile);
//CtlLib.ExcelToPdf(TempFile);
//string pdfFilePath = TempFile.Replace(".xls", ".pdf");
//write out to client broswer
System.IO.FileInfo file = new System.IO.FileInfo(TempFile);
//System.IO.FileInfo file = new System.IO.FileInfo(pdfFilePath);
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-exSheet";
//Response.ContentType = "application/pdf";
//Send file string to client
Response.WriteFile(TempFile);
//Response.WriteFile(pdfFilePath);
//Stop execute
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
CtlLib.SetUser(Session["APP_DBUSER"].ToString());
Response.ContentType = "application/vnd.ms-excel";
Response.Buffer = false;
string p_tco_org_pk = Request["p_tco_org_pk"].ToString();
string p_thr_wg_pk = Request["p_thr_wg_pk"].ToString();
string p_temp = Request["p_temp"].ToString();
string p_contract_type = Request["p_contract_type"].ToString();
string p_from_begin_contract = Request["p_from_begin_contract"].ToString();
string p_to_begin_contract = Request["p_to_begin_contract"].ToString();
string p_from_end_contract = Request["p_from_begin_contract"].ToString();
string p_to_end_contract = Request["p_to_begin_contract"].ToString();
string p_from_begin_prob = Request["p_from_begin_probation"].ToString();
string p_to_begin_prob = Request["p_to_begin_probation"].ToString();
string p_from_end_prob = Request["p_from_end_probation"].ToString();
string p_to_end_prob = Request["p_to_end_probation"].ToString();
string p_status = Request["p_status"].ToString();
string TemplateFile = "rpt_hrem00500_6.xls";
string TempFile = "../../../../system/temp/rpt_hrem00500_6_" + Session["APP_DBUSER"].ToString() + ".xls";
TemplateFile = Server.MapPath(TemplateFile);
TempFile = Server.MapPath(TempFile);
//Create a new workbook
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(TemplateFile);
//Add worksheet
IWorksheet exSheet = exBook.Worksheets[1];
IRange range;
string para = "'" + p_tco_org_pk + "','" + p_thr_wg_pk + "', '" + p_temp + "','" + p_contract_type + "','" + p_from_begin_contract + "','" + p_to_begin_contract;
para += "','" + p_from_end_contract + "','" + p_to_end_contract + "','" + p_from_begin_prob + "','" + p_to_begin_prob + "','" + p_from_end_prob + "','" + p_to_end_prob + "','" + p_status + "'";
DataTable dt_emp = CtlLib.TableReadOpenCursor("ST_HR_RPT_HREM00500_6", para);
int irow, icol_emp;
irow = dt_emp.Rows.Count;
icol_emp = dt_emp.Columns.Count;
if (irow == 0)
{
Response.Write("There is no data");
Response.End();
}
int i, j, k;
int pos = 5, total_col = icol_emp;
//bind data to excel file
//string retstring = p_curentmonth.Substring(4,2) +"-"+p_curentmonth.Substring(0,4);
//exSheet.Cells["C1"].Value=exSheet.Cells["C1"].Value+retstring;
exSheet.Cells["B1"].Value = "Report by: " + Session["APP_DBUSER"].ToString();
exSheet.Cells["B2"].Value = "Report date: " + DateTime.Now.ToString("dd/MM/yyyy");
//---end--header---
//Insert new row
for (i = 0; i < irow - 1; i++)
{
exSheet.Range[pos + 1, 1].Rows.EntireRow.Insert();
}
//Show data
for (i = 0; i < irow; i++)
{
//----------------------------------------
exSheet.Cells[pos + i, 1].Value = i + 1;
for (j = 2; j < icol_emp; j++)
{
if (j >= 12)
{
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();
}
}
}
//--------------------------------------
//----------------------------------------
// hide row firts
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();
}
