目的: V20108F – [匯出(人力資源)] – Excel檔案(含■ , □)
處理說明:1>取得子畫面的輸入參數
Tmp_MITM = Ext.getCmp('sub_MITM').getValue();
Tmp_WC = Ext.getCmp('sub_WC').getValue();
Tmp_PN = Ext.getCmp('sub_PN').getValue();
var np = {};
//var np = s_JSON('mySubFormF');
np["sub_MITM"] = Tmp_MITM;
np["sub_WC"] = Tmp_WC;
np["sub_PN"] = Tmp_PN;
var Tmp_url = "../../api/V20108FAPI/AMM_WKSTPD2XLS";
Ext.util.Cookies.set("Rtn_Msg", "");
Ext.getCmp('s_form').submit({
url: Tmp_url,
method: 'POST',
async: false,
standardSubmit: true, //若要傳送檔案至前端, standardSubmit必需設為 true
params: np,
});
2>*.cs - 寫入 *.xls
string Tmp_FName = "V20108_匯出_人力資源設定.xlsx"; //套表的檔案名稱
string documentPath = HttpContext.Current.Server.MapPath("~") + "document\\";
string Tmp_FName1 = "V20108_匯出_人力資源設定" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
Tmp_FName1 = documentPath + Tmp_FName1;
FileStream fs = null;
Workbook wk = null;
fs = myfunc.get_SS_FILES(Tmp_FName);
wk = new Workbook();
wk.LoadFromStream(fs);
Worksheet ws = wk.Worksheets[0];//獲取第一個工作表
ws.Range[myfunc.GetExcelPos(2, 0)].Text = Tmp_PN; //件號
ws.Range[myfunc.GetExcelPos(0, cur_row)].Text = "□"; //EMPID
1>*.js
Tmp_MITM = Ext.getCmp('sub_MITM').getValue();
Tmp_WC = Ext.getCmp('sub_WC').getValue();
Tmp_PN = Ext.getCmp('sub_PN').getValue();
var np = {};
//var np = s_JSON('mySubFormF'); //TMFunction.js, 組合form上的查詢條件為json參數傳遞 , type=2, 所有欄位值 , 均傳至後端(不排除_ CaluField)
np["sub_MITM"] = Tmp_MITM;
np["sub_WC"] = Tmp_WC;
np["sub_PN"] = Tmp_PN;
var Tmp_url = "../../api/V20108FAPI/AMM_WKSTPD2XLS";
//getUrlStr : 必需傳回字串( dt.JSON字串) , 非 ds
//var rtn_Str = getUrlStr(Tmp_url, np, "匯出[工作編號-人力資源](AMM_WKSTPD)");
Ext.util.Cookies.set("Rtn_Msg", "");
Ext.getCmp('s_form').submit({
url: Tmp_url,
method: 'POST',
async: false,
standardSubmit: true, //若要傳送檔案至前端, standardSubmit必需設為 true
params: np,
});
var mask = new Ext.LoadMask(Ext.getBody(), {
msg: '資料處理中, 請稍待...'
});
mask.show();//使用 mask 需手動呼叫show() 方法下
//隔兩秒才顯示完成訊息
var timer = setInterval(function () {
var cookie_token = Ext.util.Cookies.get("Rtn_Msg");
var Tmp_Str;
if (!checkisnull(cookie_token)) {
clearInterval(timer);
mask.hide();
timer = null;
var rtn_Msg = r_cookies("Rtn_Msg");
if (checkRtnOK(rtn_Msg)) {
Tmp_Str = "匯出[工作編號-人力資源]成功!!";
mysuccessalert(Tmp_Str);
}
else {
Tmp_Str = "匯出[工作編號-人力資源]失敗!!<br>"
+ rtn_Msg;
mywarnalert(Tmp_Str);
}
me.up("window").close();
me.up("window").destroy();
}
}, 2000); //等待 2000ms =1sec
}; // end of Ok_process() { //批次新增
2>*.cs
// AMM_WKSTPD2XLS() - 工作步序.人力資源(AMM_WKSTPD) 匯出 XLS
//若成功,傳回 "[匯出]成功"
//若失敗, 則傳回"錯誤: [匯出]失敗"
[HttpPost]
public dynamic AMM_WKSTPD2XLS()
{
//取得參數值
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
string Tmp_RtnMsg = "";
string Tmp_MITM = nvc["sub_MITM"];
string Tmp_WC = nvc["sub_WC"];
string Tmp_PN = nvc["sub_PN"];
HttpCookie MyCookie;
int cur_row = 0;
string Tmp_Str = "";
string Tmp_FName = "V20108_匯出_人力資源設定.xlsx"; //套表的檔案名稱
string documentPath = HttpContext.Current.Server.MapPath("~") + "document\\";
//string pathFName = documentPath + Tmp_FName;
string Tmp_FName1 = "V20108_匯出_人力資源設定" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
Tmp_FName1 = documentPath + Tmp_FName1;
FileStream fs = null;
Workbook wk = null;
fs = myfunc.get_SS_FILES(Tmp_FName);
wk = new Workbook();
wk.LoadFromStream(fs);
Worksheet ws = wk.Worksheets[0];//獲取第一個工作表
//件號/件號名稱
DataTable Tmp_dt1 = new DataTable();
DataTable Tmp_dt2 = new DataTable(); //機具財編/名稱
DataTable Tmp_dt3 = new DataTable(); //支援工作中心.人員編號+人員姓名
int Tmp_cnt1;
int Tmp_cnt2;
int Tmp_cnt3;
MemoryStream mstream = new MemoryStream();
string Tmp_CNM = "", Tmp_WITM = "", Tmp_WHR = "", Tmp_PQTY = "", Tmp_AQTY = "";
string Tmp_EMPID = "", Tmp_EMPNM = "",Tmp_PV="";
try
{
string Tmp_Sql = " SELECT A.MITM,A.WC,A.DEPID,A.PN,A.CNM,"
+ " B.WITM,B.WHR,B.PQTY,B.AQTY "
+ " FROM AMM_WKSTP A, AMM_WKSTPD B "
+ " WHERE A.MITM=B.MITM "
+ " AND A.MITM=" + myfunc.AA(Tmp_MITM)
+ " AND A.WC=" + myfunc.AA(Tmp_WC)
+ " AND A.PN=" + myfunc.AA(Tmp_PN);
Tmp_dt1 = myfunc.SqlOpen(Tmp_dt1, Tmp_Sql);
Tmp_cnt1 = Tmp_dt1.Rows.Count;
//進廠檢測
ws.Range[myfunc.GetExcelPos(1, 3)].Text = "0"; //工時-進廠檢測
ws.Range[myfunc.GetExcelPos(1, 5)].Text = "0"; //人數-熟手
ws.Range[myfunc.GetExcelPos(3, 5)].Text = "0"; //人數-生手
//拆檢
ws.Range[myfunc.GetExcelPos(5, 3)].Text = "0"; //工時 - 拆檢
ws.Range[myfunc.GetExcelPos(6, 5)].Text = "0"; //人數-熟手
ws.Range[myfunc.GetExcelPos(9, 5)].Text = "0"; //人數-生手
//組裝
ws.Range[myfunc.GetExcelPos(12, 3)].Text = "0"; //工時 - 組裝
ws.Range[myfunc.GetExcelPos(12, 5)].Text = "0"; //人數-熟手
ws.Range[myfunc.GetExcelPos(15, 5)].Text = "0"; //人數-生手
//完工測試
ws.Range[myfunc.GetExcelPos(18, 3)].Text = "0"; //工時 - 完工測試
ws.Range[myfunc.GetExcelPos(18, 5)].Text = "0"; //人數-熟手
ws.Range[myfunc.GetExcelPos(21, 5)].Text = "0"; //人數-生手
for (var i = 0; i < Tmp_cnt1; i++)
{
Tmp_CNM = Tmp_dt1.Rows[i]["CNM"].ToString();
Tmp_WITM = Tmp_dt1.Rows[i]["WITM"].ToString();
Tmp_WHR = Tmp_dt1.Rows[i]["WHR"].ToString();
Tmp_PQTY = Tmp_dt1.Rows[i]["PQTY"].ToString();
Tmp_AQTY = Tmp_dt1.Rows[i]["AQTY"].ToString();
if (Tmp_WITM=="1") //進廠檢測
{
ws.Range[myfunc.GetExcelPos(1, 3)].Text = Tmp_WHR; //工時-進廠檢測
ws.Range[myfunc.GetExcelPos(1, 5)].Text = Tmp_PQTY; //人數-熟手
ws.Range[myfunc.GetExcelPos(3, 5)].Text = Tmp_AQTY; //人數-生手
}
if (Tmp_WITM == "2") //拆檢
{
ws.Range[myfunc.GetExcelPos(5, 3)].Text = Tmp_WHR; //工時 - 拆檢
ws.Range[myfunc.GetExcelPos(6, 5)].Text = Tmp_PQTY; //人數-熟手
ws.Range[myfunc.GetExcelPos(9, 5)].Text = Tmp_AQTY; //人數-生手
}
if (Tmp_WITM == "3") //組裝
{
ws.Range[myfunc.GetExcelPos(12, 3)].Text = Tmp_WHR; //工時 - 組裝
ws.Range[myfunc.GetExcelPos(12, 5)].Text = Tmp_PQTY; //人數-熟手
ws.Range[myfunc.GetExcelPos(15, 5)].Text = Tmp_AQTY; //人數-生手
}
if (Tmp_WITM == "4") //完工測試
{
ws.Range[myfunc.GetExcelPos(18, 3)].Text = Tmp_WHR; //工時 - 組裝
ws.Range[myfunc.GetExcelPos(18, 5)].Text = Tmp_PQTY; //人數-熟手
ws.Range[myfunc.GetExcelPos(21, 5)].Text = Tmp_AQTY; //人數-生手
}
}
string Tmp_WEANO="", Tmp_NM="";
string Tmp_WEANO_Str="", Tmp_NM_Str="";
Tmp_Sql = " SELECT DISTINCT WEANO,NM "
+ " FROM AMM_DEVD "
+ " WHERE 1=1 "
+ " AND MITM=" + myfunc.AA(Tmp_MITM);
Tmp_dt2 = myfunc.SqlOpen(Tmp_dt2, Tmp_Sql);
Tmp_cnt2 = Tmp_dt2.Rows.Count;
for (var i = 0; i < Tmp_cnt2; i++)
{
Tmp_WEANO = Tmp_dt2.Rows[i]["WEANO"].ToString();
Tmp_NM = Tmp_dt2.Rows[i]["NM"].ToString();
Tmp_WEANO_Str = Tmp_WEANO_Str+Tmp_WEANO+"/";
Tmp_NM_Str = Tmp_NM_Str +Tmp_NM+"/";
}
ws.Range[myfunc.GetExcelPos(2, 0)].Text = Tmp_PN; //件號
ws.Range[myfunc.GetExcelPos(2, 1)].Text = Tmp_CNM; //名稱
ws.Range[myfunc.GetExcelPos(13, 0)].Text = Tmp_WEANO_Str; //關鍵機具財編
ws.Range[myfunc.GetExcelPos(13, 1)].Text = Tmp_NM_Str;//關鍵機具名稱
Tmp_Sql = " SELECT DISTINCT EMPID,EMPNM "
+ " FROM AMM_EMP "
+ " WHERE 1=1 "
+ " AND SUPWC=" + myfunc.AA(Tmp_WC)
+" ORDER BY EMPID ";
Tmp_dt3 = myfunc.SqlOpen(Tmp_dt3, Tmp_Sql);
Tmp_cnt3 = Tmp_dt3.Rows.Count;
cur_row = 7;
for (var i = 0; i < Tmp_cnt3; i++)
{
Tmp_EMPID= Tmp_dt3.Rows[i]["EMPID"].ToString();
Tmp_EMPNM = Tmp_dt3.Rows[i]["EMPNM"].ToString();
ws.Range[myfunc.GetExcelPos(0, cur_row)].Text = "□"; //EMPID
ws.Range[myfunc.GetExcelPos(1, cur_row)].Text = Tmp_EMPID; //EMPID
ws.Range[myfunc.GetExcelPos(2, cur_row)].Text = Tmp_EMPNM; //EMPNM
ws.Range[myfunc.GetExcelPos(3, cur_row)].Text = "□"; //EMPID
ws.Range[myfunc.GetExcelPos(4, cur_row)].Text = Tmp_EMPID; //EMPID
ws.Range[myfunc.GetExcelPos(5, cur_row)].Text = Tmp_EMPNM; //EMPNM
ws.Range[myfunc.GetExcelPos(6, cur_row)].Text = "□"; //EMPID
ws.Range[myfunc.GetExcelPos(7, cur_row)].Text = Tmp_EMPID; //EMPID
ws.Range[myfunc.GetExcelPos(8, cur_row)].Text = Tmp_EMPNM; //EMPNM
ws.Range[myfunc.GetExcelPos(9, cur_row)].Text = "□"; //EMPID
ws.Range[myfunc.GetExcelPos(10, cur_row)].Text = Tmp_EMPID; //EMPID
ws.Range[myfunc.GetExcelPos(11, cur_row)].Text = Tmp_EMPNM; //EMPNM
ws.Range[myfunc.GetExcelPos(12, cur_row)].Text = "□"; //EMPID
ws.Range[myfunc.GetExcelPos(13, cur_row)].Text = Tmp_EMPID; //EMPID
ws.Range[myfunc.GetExcelPos(14, cur_row)].Text = Tmp_EMPNM; //EMPNM
ws.Range[myfunc.GetExcelPos(15, cur_row)].Text = "□"; //EMPID
ws.Range[myfunc.GetExcelPos(16, cur_row)].Text = Tmp_EMPID; //EMPID
ws.Range[myfunc.GetExcelPos(17, cur_row)].Text = Tmp_EMPNM; //EMPNM
ws.Range[myfunc.GetExcelPos(18, cur_row)].Text = "□"; //EMPID
ws.Range[myfunc.GetExcelPos(19, cur_row)].Text = Tmp_EMPID; //EMPID
ws.Range[myfunc.GetExcelPos(20, cur_row)].Text = Tmp_EMPNM; //EMPNM
ws.Range[myfunc.GetExcelPos(21, cur_row)].Text = "□"; //EMPID
ws.Range[myfunc.GetExcelPos(22, cur_row)].Text = Tmp_EMPID; //EMPID
ws.Range[myfunc.GetExcelPos(23, cur_row)].Text = Tmp_EMPNM; //EMPNM
for (var j = 0; j <= 3; j++)
{
Tmp_Sql = " SELECT DISTINCT MITM,WITM,EMPID,EMPNM,PV"
+ " FROM AMM_EMPD "
+ " WHERE 1=1 "
+ " AND MITM=" + myfunc.AA(Tmp_MITM)
+ " AND EMPID=" + myfunc.AA(Tmp_EMPID)
+ " AND WITM=" + myfunc.AA((j+1).ToString());
Tmp_Str = myfunc.SqlValue(Tmp_Sql);
Tmp_PV = myfunc.StrExtract(Tmp_Str, 5);
if (Tmp_PV == "2") //熟手
{
ws.Range[myfunc.GetExcelPos(j*6+0, cur_row)].Text = "■"; //EMPID
}
if (Tmp_PV == "1") //生手
{
ws.Range[myfunc.GetExcelPos(j*6+3, cur_row)].Text = "■"; //EMPID
}
} // end of for (var j = 1; j <= 4; j++)
cur_row = cur_row + 1;
} // end of for (var i = 0; i < Tmp_cnt3; i++)
wk.SaveToStream(mstream, FileFormat.Version2007);
wk.SaveToFile(Tmp_FName1, FileFormat.Version2007);
string Tmp_Row = "5";
Tmp_RtnMsg = "已匯出完成!!(" + Tmp_Row.ToString() + "筆)<br>"
+ "檔案名稱(" + Tmp_FName + ")";
MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_RtnMsg));
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(Tmp_FName, System.Text.Encoding.UTF8) + "\"");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(mstream.ToArray());
HttpContext.Current.Response.End();
}
catch (Exception e)
{
Tmp_RtnMsg = myfunc.Get1ORA(e.Message);
Tmp_RtnMsg = "錯誤:匯出(人力資源設定-AMM_WKSTPD)失敗!!<br>"
+ Tmp_RtnMsg;
MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_RtnMsg));
HttpContext.Current.Response.Cookies.Add(MyCookie);
}
finally
{
wk.Dispose();
fs.Close();
fs.Dispose();
}
沒有留言:
張貼留言