目的: V80403 – [匯出EXCEL]鈕,將各頁的資料匯出成Excel檔案,將 [HTTP] 函式轉成 static 函式
處理說明: 1>[HTTP]函式,資料接收傳回前端
[HttpPost]
public dynamic get_sub_Data1()
2>static 函式, 供其他模組呼叫 , 不需有 instance --> 改將 instance 當參數傳入
public static DataTable get_sub_Data11(string par_PN ,BaseAPIController me)
1>*. cs
[HttpPost]
public dynamic get_sub_Data1()
{
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
string Tmp_PN = nvc["PN"];
OracleCommand cmd = new OracleCommand();
var response = this.Request.CreateResponse();
DataSet ds = null;
try
{
string Tmp_Sql1 = ""; //for 日期起迄
string Tmp_Sql = " SELECT RNK, PN, MITM, "
+ " WITM, EMPID, EMPNM, PV, "
+ " WEANO, QMSNO, DEV_PN, NM, DEVNO "
+ " FROM ( SELECT dense_rank() over(order by A.PN,A.MITM,B.WITM,C.WEANO) as RNK,"
+ " A.PN, A.MITM, "
+ " B.WITM, B.EMPID, B.EMPNM, B.PV, "
+ " C.WEANO, C.QMSNO, C.PN as DEV_PN, C.NM, C.DEVNO "
+ " FROM AMM_WKSTP A, AMM_EMPD B, AMM_DEVD C "
+ " WHERE A.MITM=B.MITM "
+ " AND A.MITM=C.MITM "
+ " AND B.WITM= C.WITM "
+ "AND A.PN =" + myfunc.AA(Tmp_PN);
Tmp_Sql = Tmp_Sql + ")";
cmd.CommandText = Tmp_Sql;
string countSql = " SELECT COUNT(*) as total FROM (" + Tmp_Sql + ")";
//不分頁
ds = getDataTable(cmd, countSql, false);
int Tmp_Cnt = ds.Tables["T1"].Rows.Count;
ds.Tables["T1"].Columns.Add(new DataColumn("PV_", typeof(string)));//熟練度說明
ds.Tables["T1"].Columns.Add(new DataColumn("C_NM_", typeof(string)));//中文名稱
ds.Tables["T1"].Columns.Add(new DataColumn("ES_NM_", typeof(string)));//英文簡稱
string Tmp_Str = "";
string Tmp_PV, Tmp_PV_;
string Tmp_C_NM_, Tmp_ES_NM_;
for (int i = 0; i < Tmp_Cnt; i++)
{
DataRow dr = ds.Tables["T1"].Rows[i];
//1>熟練度說明
Tmp_PV = dr["PV"].ToString();
Tmp_PN = dr["PN"].ToString();
//熟練度說明
Tmp_Sql = " SELECT DESCPT "
+ " FROM KEYCODE "
+ " WHERE TBL_NAME=" + myfunc.AA("AMM_EMPD")
+" AND KEY_NAME=" + myfunc.AA("PV")
+" AND KEY_CODE=" + myfunc.AA(Tmp_PV);
Tmp_PV_ = myfunc.SqlValue(Tmp_Sql);
if (Tmp_PV_ == "Y")
Tmp_PV_ = "熟手";
else if (Tmp_PV_ == "N")
Tmp_PV_ = "生手";
dr["PV_"] = Tmp_PV_;
//中文名稱 C_NM_, //英文簡稱 ES_NM_
Tmp_Sql = " SELECT DISTINCT C_NM,ES_NM "
+ " FROM AMM_FM "
+ " WHERE PN=" + myfunc.AA(Tmp_PN)
+ " AND C_NM IS NOT NULL "
+ " AND ROWNUM=1 ";
Tmp_Str = myfunc.SqlValue(Tmp_Sql);
Tmp_C_NM_ = myfunc.StrExtract(Tmp_Str,1);
Tmp_ES_NM_ = myfunc.StrExtract(Tmp_Str, 2);
dr["C_NM_"] = Tmp_C_NM_;
dr["ES_NM_"] = Tmp_ES_NM_;
} // for (int i = 0; i < Tmp_Cnt; i++)
return ds;
} //try
finally
{
cmd.Dispose();
}
}// end of get_sub_Data1();
//static 函式,供其他 函式呼叫
public static DataTable get_sub_Data11(string par_PN ,BaseAPIController me)
{
//var c = HttpContext.Current;
//NameValueCollection nvc = c.Request.Form;
//string Tmp_PN = nvc["PN"];
string Tmp_PN = par_PN;
OracleCommand cmd = new OracleCommand();
//var response = this.Request.CreateResponse();
DataSet ds = null;
try
{
string Tmp_Sql1 = ""; //for 日期起迄
string Tmp_Sql = " SELECT RNK, PN, MITM, "
+ " WITM, EMPID, EMPNM, PV, "
+ " WEANO, QMSNO, DEV_PN, NM, DEVNO "
+ " FROM ( SELECT dense_rank() over(order by A.PN,A.MITM,B.WITM,C.WEANO) as RNK,"
+ " A.PN, A.MITM, "
+ " B.WITM, B.EMPID, B.EMPNM, B.PV, "
+ " C.WEANO, C.QMSNO, C.PN as DEV_PN, C.NM, C.DEVNO "
+ " FROM AMM_WKSTP A, AMM_EMPD B, AMM_DEVD C "
+ " WHERE A.MITM=B.MITM "
+ " AND A.MITM=C.MITM "
+ " AND B.WITM= C.WITM "
+ "AND A.PN =" + myfunc.AA(Tmp_PN);
Tmp_Sql = Tmp_Sql + ")";
cmd.CommandText = Tmp_Sql;
string countSql = " SELECT COUNT(*) as total FROM (" + Tmp_Sql + ")";
//不分頁
ds = me.getDataTable(cmd, countSql, false);
int Tmp_Cnt = ds.Tables["T1"].Rows.Count;
ds.Tables["T1"].Columns.Add(new DataColumn("PV_", typeof(string)));//熟練度說明
ds.Tables["T1"].Columns.Add(new DataColumn("C_NM_", typeof(string)));//中文名稱
ds.Tables["T1"].Columns.Add(new DataColumn("ES_NM_", typeof(string)));//英文簡稱
string Tmp_Str = "";
string Tmp_PV, Tmp_PV_;
string Tmp_C_NM_, Tmp_ES_NM_;
for (int i = 0; i < Tmp_Cnt; i++)
{
DataRow dr = ds.Tables["T1"].Rows[i];
//1>熟練度說明
Tmp_PV = dr["PV"].ToString();
Tmp_PN = dr["PN"].ToString();
//熟練度說明
Tmp_Sql = " SELECT DESCPT "
+ " FROM KEYCODE "
+ " WHERE TBL_NAME=" + myfunc.AA("AMM_EMPD")
+ " AND KEY_NAME=" + myfunc.AA("PV")
+ " AND KEY_CODE=" + myfunc.AA(Tmp_PV);
Tmp_PV_ = myfunc.SqlValue(Tmp_Sql);
if (Tmp_PV_ == "Y")
Tmp_PV_ = "熟手";
else if (Tmp_PV_ == "N")
Tmp_PV_ = "生手";
dr["PV_"] = Tmp_PV_;
//中文名稱 C_NM_, //英文簡稱 ES_NM_
Tmp_Sql = " SELECT DISTINCT C_NM,ES_NM "
+ " FROM AMM_FM "
+ " WHERE PN=" + myfunc.AA(Tmp_PN)
+ " AND C_NM IS NOT NULL "
+ " AND ROWNUM=1 ";
Tmp_Str = myfunc.SqlValue(Tmp_Sql);
Tmp_C_NM_ = myfunc.StrExtract(Tmp_Str, 1);
Tmp_ES_NM_ = myfunc.StrExtract(Tmp_Str, 2);
dr["C_NM_"] = Tmp_C_NM_;
dr["ES_NM_"] = Tmp_ES_NM_;
} // for (int i = 0; i < Tmp_Cnt; i++)
//return ds;
DataTable Tmp_dt = ds.Tables["T1"];
return Tmp_dt;
} //try
finally
{
cmd.Dispose();
}
}// end of get_sub_Data1();
2>*.cs
//查詢 - [匯出EXCEL]的資料匯出至 xls - 套表 - 共5Sheet 套表
[HttpPost]
public void XlsOut()
{
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
//[Sheet5_機具人員]Sheet寫入欄位 - 呼叫 static 函式
Tmp_dt = V80403TDAPIController.get_sub_Data11(Tmp_PN, this);
for (var j = 0; j < Tmp_dt.Rows.Count; j++)
{
Tmp_ws5_row = Tmp_ws5_row + 1;
ws5.Range[myfunc.GetExcelPos(0, Tmp_ws5_row)].Text = Tmp_PN;
ws5.Range[myfunc.GetExcelPos(1, Tmp_ws5_row)].Text = Tmp_C_NM;
ws5.Range[myfunc.GetExcelPos(2, Tmp_ws5_row)].Text = Tmp_ES_NM;
ws5.Range[myfunc.GetExcelPos(3, Tmp_ws5_row)].Text = Tmp_dt.Rows[j]["MITM"].ToString();
ws5.Range[myfunc.GetExcelPos(4, Tmp_ws5_row)].Text = Tmp_dt.Rows[j]["WITM"].ToString();
ws5.Range[myfunc.GetExcelPos(5, Tmp_ws5_row)].Text = Tmp_dt.Rows[j]["EMPID"].ToString();
ws5.Range[myfunc.GetExcelPos(6, Tmp_ws5_row)].Text = Tmp_dt.Rows[j]["EMPNM"].ToString();
ws5.Range[myfunc.GetExcelPos(7, Tmp_ws5_row)].Text = Tmp_dt.Rows[j]["PV_"].ToString();
ws5.Range[myfunc.GetExcelPos(8, Tmp_ws5_row)].Text = Tmp_dt.Rows[j]["WEANO"].ToString();
ws5.Range[myfunc.GetExcelPos(9, Tmp_ws5_row)].Text = Tmp_dt.Rows[j]["QMSNO"].ToString();
ws5.Range[myfunc.GetExcelPos(10, Tmp_ws5_row)].Text = Tmp_dt.Rows[j]["DEV_PN"].ToString();
ws5.Range[myfunc.GetExcelPos(11, Tmp_ws5_row)].Text = Tmp_dt.Rows[j]["NM"].ToString();
} //for (var j=
}
3>V80403.js - 跳頁時,呼叫 [HTTP] 函式
//[機具人員]頁
if (newCard.id == "tab_EMPD1") {
//開啟 store;
cur_recs = Ext.getCmp("grid_Single").selModel.getSelection();
if (cur_recs.length == 0) {
Tmp_Str = "請先選擇要查詢機具人員的資料<br>";
mywarnalert(Tmp_Str);
return;
}
var np = {};
cur_rec = cur_recs[0];
np["PN"] = cur_rec.data["PN"];
Ext.getCmp('sub_V80403TD_Grid').store.getProxy().url = '../../api/V80403TDAPI/get_sub_data1',
Ext.getCmp('sub_V80403TD_Grid').store.getProxy().extraParams = np; //分頁OK,篩選條件OK
Ext.getCmp('sub_V80403TD_Grid').store.load();
}
