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