// [件號報工統計] - [確定匯入] 鈕 , 匯入 件號XLS 檔案後,產出該[件號報工資料]
[HttpPost]
public void XLSIN2XLSOUT()
{
HttpContext c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
//OracleConnection conn = new OracleConnection(DBService.ConnectionString(("AMMEU"));//
OracleConnection conn = new OracleConnection(DBService.ConnectionString((DBLINK)));//
OracleCommand cmd = new OracleCommand();
OracleDataReader reader;
DataSet ds = null;
//取得匯入檔案名稱 of filefield
string Tmp_in_FName = nvc["in_FName"];
//取得匯出檔案名稱 of filefield
string Tmp_out_FName = nvc["out_FName"];
string Tmp_sub3_PROJID = nvc["sub3_PROJID"];
Workbook wk = null;
Workbook wk2 = null;
MemoryStream Tmp_out_Stream = new MemoryStream();
HttpRequest Request = HttpContext.Current.Request;
var response = this.Request.CreateResponse();
//string file_Name = Tmp_in_FName;
string FILEDT = DateTime.Now.ToString("yyyy/MM/dd");
string Tmp_RtnMsg = "";
string Tmp_RtnMsg1 = "";
string Tmp_Sql = "", Tmp_Str = "";
int Tmp_cnt = 0;
//HttpCookie MyCookie = new HttpCookie("Rtn_Msg");
HttpCookie MyCookie = new HttpCookie("EX_DFile", HttpUtility.UrlEncode(Tmp_Str));
int row2 = 0;
conn.Open();
cmd.Connection = conn;
try
{
foreach (string cur_FName in Request.Files)
{
string fileType = Request.Files[cur_FName].ContentType;
Stream Tmp_in_Stream = Request.Files[cur_FName].InputStream;
Tmp_in_FName = Path.GetFileName(Request.Files[cur_FName].FileName);
int fileSize = Request.Files[cur_FName].ContentLength;
byte[] fileRcrd = new byte[fileSize];
//更新資料庫欄位值
//匯入的 xls
wk = new Workbook();
wk.LoadFromStream(Tmp_in_Stream);
Worksheet sheet1 = wk.Worksheets[0];
//匯出的 xls
wk2 = new Workbook();
Worksheet sheet2 = wk2.Worksheets[0];//獲取第一個工作表
string[] outFieldArray = { "AMM單號", "工號", "版本", "件號", "件號名稱",
"製程", "製程資料內容", "WBS", "工單類型", "數量", "單位",
"EOECN", "專案","機號","起始值(STARTVAL)", "情況處理碼",
"工單報工統計(小時)", "工作中心", "工作中心報工統計(小時)"
};
for (int i = 0; i < outFieldArray.Length; i++)
{
sheet2.Range[myfunc.GetExcelPos(i, 0)].Text = outFieldArray[i];
}
int RowsCount = sheet1.LastRow;
string Tmp_AMMNO, Tmp_SAPNO, Tmp_ED, Tmp_PN, Tmp_PNNAME;
string Tmp_MKNO, Tmp_MKDESCPT, Tmp_WBS, Tmp_SROTP, Tmp_QTY, Tmp_UNIT;
string Tmp_ECNO, Tmp_PROJID, Tmp_ACNO, Tmp_STARTVAL, Tmp_CNDPROCCODE;
string Tmp_ALS_HR_, Tmp_WC_, Tmp_WCHR_;
//STEP1 檢核 XLS 欄位名稱資料是否正確 -取得 標題 of 件號
Tmp_PN = sheet1.Range[myfunc.GetExcelPos(0, 0)].Value;
//xls 欄位名稱有問題
if ((Tmp_PN != "件號"))
{
Tmp_RtnMsg = "匯入檔案欄位名稱必需如下:<br>";
//(Tmp_DTSOR != "資料來源") || (Tmp_OGONOAF != "拆檢提領編號") || (Tmp_NGONOAF != "維修提領編號") || (Tmp_RPNOAF != "拆檢交修單號") || (Tmp_MRPNOAF != "維修約交修單號") ||
if (Tmp_PN != "件號")
Tmp_RtnMsg = Tmp_RtnMsg + "第1欄欄位名稱: [件號];";
//Tmp_RtnMsg = "匯入檔案欄位名稱必需如下:<br>"
// + "\件號"
// + " 敬請檢核";
//Tmp_Str = "{success: false,FName:" + myfunc.AA(FName) + ",Rtn_Msg:" + myfunc.AA(Tmp_RtnMsg) + "}";
//response.Content = new StringContent(Tmp_Str); // 回應內容
//return response;
//以 Cookie 方式傳回訊息 , 因為要下載[匯出檔案],無傳回值 所以利用 Cookie 傳回訊息 -,f_downloadFile('EX_DFile', '../api/V80C03API/DB2XLS', np, 'POST', function () {
MyCookie.Value = HttpUtility.UrlEncode(Tmp_RtnMsg);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
};
//STEP2 取得xls.[目前件號].報工資料
for (int i = 0; i < RowsCount; i++)
{
Tmp_PN = sheet1.Range[myfunc.GetExcelPos(0, i + 1)].Value;
if (myfunc.checkisnull(Tmp_PN))
{ break; }
Tmp_Sql = "SELECT AMMNO,SAPNO, "
+ " ( SELECT ED "
+ " FROM AMM_EDLST b "
+ " WHERE (AMMNO, ITM) in ( select AMMNO, max(to_number(ITM)) "
+ " from AMM_EDLST group by AMMNO) "
+ " AND a.AMMNO = b.AMMNO) ED, "
+ " PN,PNNAME,MKNO,MKDESCPT,WBS,SROTP,QTY,UNIT ,ECNO,PROJID,ACNO,STARTVAL,CNDPROCCODE "
+ " FROM AMM_SRO a "
+ " WHERE LENGTH(SAPNO)> 7 ";
if (!myfunc.checkisnull(Tmp_sub3_PROJID))
Tmp_Sql = Tmp_Sql + " AND PROJID = " + myfunc.AA(Tmp_sub3_PROJID);
Tmp_Sql = Tmp_Sql + " AND PN = " + myfunc.AA(Tmp_PN)
+ " ORDER BY ACNO,PN ";
cmd.CommandText = Tmp_Sql;
reader = cmd.ExecuteReader();
while (reader.Read())
{
Tmp_AMMNO = reader["AMMNO"].ToString();
Tmp_SAPNO = reader["SAPNO"].ToString();
Tmp_ED = reader["ED"].ToString();
Tmp_PN = reader["PN"].ToString();
Tmp_PNNAME = reader["PNNAME"].ToString();
Tmp_MKNO = reader["MKNO"].ToString();
Tmp_MKDESCPT = reader["MKDESCPT"].ToString();
Tmp_WBS = reader["WBS"].ToString();
Tmp_SROTP = reader["SROTP"].ToString();
Tmp_QTY = reader["QTY"].ToString();
Tmp_UNIT = reader["UNIT"].ToString();
Tmp_ECNO = reader["ECNO"].ToString();
Tmp_PROJID = reader["PROJID"].ToString();
Tmp_ACNO = reader["ACNO"].ToString();
Tmp_STARTVAL = reader["STARTVAL"].ToString();
Tmp_CNDPROCCODE = reader["CNDPROCCODE"].ToString();
//取得[工單報工工時]
Tmp_Sql = " SELECT round((sum(HOUR_WORK)+sum(HOUR_OT))/60,2) HR "
+ " FROM "
+ " ( "
+ " SELECT JCN, ACT, WC, HOUR_WORK, work_ID, HOUR_OT from HOUR.ALS_HOUR_COMP @ALS_HOUR where JCN = '0000' || " + myfunc.AA(Tmp_SAPNO)
+ " union all "
+ " select JCN, ACT, WC, HOUR_WORK, work_ID, HOUR_OT from HOUR.ALS_HOUR_COMP_HISTORY @ALS_HOUR where JCN = '0000' || " + myfunc.AA(Tmp_SAPNO)
+ " ) "
+ " group by JCN ";
Tmp_ALS_HR_ = myfunc.SqlValue(Tmp_Sql);
Tmp_Sql = " SELECT WC,round((sum(HOUR_WORK)+sum(HOUR_OT))/60,2) WCHR "
+ " FROM "
+ " ( "
+ " select JCN, ACT, WC, HOUR_WORK, work_ID, HOUR_OT from HOUR.ALS_HOUR_COMP @ALS_HOUR where JCN = '0000' || " + myfunc.AA(Tmp_SAPNO)
+ " union all "
+ " select JCN, ACT, WC, HOUR_WORK, work_ID, HOUR_OT from HOUR.ALS_HOUR_COMP_HISTORY @ALS_HOUR where JCN = '0000' || " + myfunc.AA(Tmp_SAPNO)
+ " ) "
+ " group by JCN,WC ";
Tmp_Str = myfunc.SqlValue(Tmp_Sql);
Tmp_WC_ = myfunc.StrExtract(Tmp_Str, 1);
Tmp_WCHR_ = myfunc.StrExtract(Tmp_Str, 2);
//匯出至 wk2.Sheet2
/*
{ AMM單號, 工號, 版本, 件號, 件號名稱,
製程, 製程資料內容, WBS, 工單類型, 數量, 單位,
EOECN, 專案,機號,起始值(STARTVAL), 情況處理碼,
工單報工統計(小時), 工作中心, 工作中心報工統計(小時)
];
*/
//- 匯出 { AMM單號, 工號, 版本, 件號, 件號名稱,
row2 = row2 + 1;
sheet2.Range[myfunc.GetExcelPos(0, row2)].Text = Tmp_AMMNO;
sheet2.Range[myfunc.GetExcelPos(1, row2)].Text = Tmp_SAPNO;
sheet2.Range[myfunc.GetExcelPos(2, row2)].Text = Tmp_ED;
sheet2.Range[myfunc.GetExcelPos(3, row2)].Text = Tmp_PN;
sheet2.Range[myfunc.GetExcelPos(4, row2)].Text = Tmp_PNNAME;
//製程, 製程資料內容, WBS, 工單類型, 數量, 單位,
sheet2.Range[myfunc.GetExcelPos(5, row2)].Text = Tmp_MKNO;
sheet2.Range[myfunc.GetExcelPos(6, row2)].Text = Tmp_MKDESCPT;
sheet2.Range[myfunc.GetExcelPos(7, row2)].Text = Tmp_WBS;
sheet2.Range[myfunc.GetExcelPos(8, row2)].Text = Tmp_SROTP;
sheet2.Range[myfunc.GetExcelPos(9, row2)].Text = Tmp_QTY;
sheet2.Range[myfunc.GetExcelPos(10, row2)].Text = Tmp_UNIT;
//EOECN, 專案,機號,起始值(STARTVAL), 情況處理碼,
sheet2.Range[myfunc.GetExcelPos(11, row2)].Text = Tmp_ECNO;
sheet2.Range[myfunc.GetExcelPos(12, row2)].Text = Tmp_PROJID;
sheet2.Range[myfunc.GetExcelPos(13, row2)].Text = Tmp_ACNO;
sheet2.Range[myfunc.GetExcelPos(14, row2)].Text = Tmp_STARTVAL;
sheet2.Range[myfunc.GetExcelPos(15, row2)].Text = Tmp_CNDPROCCODE;
// 工單報工統計(小時), 工作中心, 工作中心報工統計(小時)
sheet2.Range[myfunc.GetExcelPos(16, row2)].Text = Tmp_ALS_HR_;
sheet2.Range[myfunc.GetExcelPos(17, row2)].Text = Tmp_WC_;
sheet2.Range[myfunc.GetExcelPos(18, row2)].Text = Tmp_WCHR_;
} // end of while reader.read()
} // end of for (int i = 0; i < RowsCount; i++)
sheet2.AllocatedRange.AutoFitColumns();
wk2.SaveToStream(Tmp_out_Stream, FileFormat.Version2010);
} //end of foreach (string cur_FName in Request.Files)
} // end fo try{
catch (Exception e)
{
//Console.WriteLine(e);
Tmp_RtnMsg = "錯誤訊息如下:"
+ e.Message;
MyCookie.Value = HttpUtility.UrlEncode(Tmp_RtnMsg);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
//throw;
}
finally
{
wk.Dispose();
wk2.Dispose();
conn.Close();
}
//return response;
//Tmp_Cnt = 6;
Tmp_Str = "已匯出完成!! (" + row2.ToString() + "筆) <br>"
+ "檔案名稱(" + Tmp_out_FName + ")";
//MyCookie = new HttpCookie("EX_DFile", HttpUtility.UrlEncode(Tmp_Str));
MyCookie.Value = HttpUtility.UrlEncode(Tmp_Str);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(Tmp_out_FName, System.Text.Encoding.UTF8) + "\"");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(Tmp_out_Stream.ToArray());
HttpContext.Current.Response.End();
} // end of XLSIN2XLSOUT()
