目的: V20107D - Email 附加檔案 - *.xlsx 檔案
處理說明: 1>產生 *.xlsx 檔案 , 並產生網路路徑檔案
Workbook wk = new Workbook();
Worksheet sheet1 = wk.Worksheets[0];//獲取第一個工作表
:
sheet1.Range[myfunc.GetExcelPos(0, i + 1)].Text = reader["DEPID"].ToString();
:
// 取得實實的路徑
string documentPath = HttpContext.Current.Server.MapPath("~") + "document\\"; string FileName1 = documentPath + par_FName;
wk.SaveToFile(FileName1, FileFormat.Version2007);
2>Email 處理: 附加檔案 - 以完整檔案路徑檔名 , 附加檔案
MailMessage ChkMail = new MailMessage();
ChkMail.To.add("xxx@xxxx") ;
string mail_from = "tlsapp@ms.aidc.com.tw";
ChkMail.From = new MailAddress(mail_from, "AMM系統", System.Text.Encoding.UTF8);
string subject = "AMM V20107 工作步序資料維護 - 工時不完整(=0)";
string content = "附檔內的件號工作步序資料不完整<br>"
+ "請至 AMM V20107維護工作步序資料<br>";
ContentType ct = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
Attachment attm = new Attachment(Tmp_FName, ct);
ChkMail.Attachments.Add(attm);
sendMail(subject, content, ChkMail); //baseAPIController
1>*.js//[MAIL通知]鈕
function MAILNotifyBtn_click() {
var np = {};
np = {};
Ext.Ajax.request({
url: '../../api/V20107DAPI/DoMAIL', method: 'POST',
async: false,
//standardSubmit: true, //若要傳送檔案至前端, standardSubmit必需設為 true
params: np,
success: function (response, opts) {
var Tmp_Obj = Ext.decode(response.responseText);
if (Tmp_Obj["success"] == false) {
var Tmp_Rtn_Msg = "EMAIL通知人員失敗!!<br>"
+ "請檢核<br>"
+ Tmp_Obj["Rtn_Msg"];
mywarnalert(Tmp_Rtn_Msg);
return;
}
else {
var Tmp_Rtn_Msg = "EMAIL通知人員成功!!<br>";
mysuccessalert(Tmp_Rtn_Msg);
}
}
});
} // end of function MAILNotifyBtn_click() {
2>*.cs
// [MAIL通知] - DoMAIL
[HttpPost]
public HttpResponseMessage DoMAIL()
{
//取得參數值
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
string Tmp_USERID = LoginUserModel.LoginUserId;
string Tmp_RtnMsg = "";
string To_Adr = "";
string n = funId + "_DoMAIL";
OracleConnection conn = new OracleConnection(DBService.ConnectionString(DBLINK));
OracleCommand cmd = new OracleCommand();
OracleDataReader reader;
var response = Request.CreateResponse();
MailMessage ChkMail = new MailMessage();
try
{
conn.Open();
conn.ClientInfo = User.Identity.Name;
conn.ModuleName = BaseSYS + "_" + BaseMODID;
conn.ActionName = ActionName;
cmd.BindByName = true;
cmd.Connection = conn;
string Tmp_Sql;
int Tmp_cnt=0;
Tmp_Sql = " SELECT COUNT(*) "
+" FROM AMM_WKSTP A, AMM_WKSTPD B "
+" WHERE A.MITM = B.MITM "
+" AND (B.WHR = '0' OR B.WHR IS NULL) "
+" AND SUBSTR(DEPID,1,2) = ( SELECT SUBSTR(DEPID, 1, 2) "
+ " FROM HR_EMPLYM "
+ " WHERE EMPLYID = "+myfunc.AA(Tmp_USERID)+") ";
Tmp_cnt = int.Parse(myfunc.SqlValue(Tmp_Sql));
if (Tmp_cnt==0)
{
Tmp_RtnMsg = "目前登入使用者(" + Tmp_USERID + ")的部門相關工作步序資料<br>"
+ "工時資料均完整 ( 工時>0 ) <br>"
+ "不需 MAIL 通知相關人員";
response.Content = new StringContent("{'success': false,'Rtn_Msg':'" + Tmp_RtnMsg + "'}"); // 回應內容
return response;
}
//填寫 Email.From, To, subject, BODY
Tmp_Sql= " SELECT A.EMPLYID,B.NOTES_MAIL "
+" FROM AMM_MAILIST A, HR_EMPLYM B "
+" WHERE A.EMPLYID = B.EMPLYID "
+" AND A.DEPID = ( SELECT SUBSTR(DEPID, 1, 2) "
+ " FROM HR_EMPLYM "
+ " WHERE EMPLYID ="+myfunc.AA(Tmp_USERID)+" ) ";
cmd.CommandText = Tmp_Sql;
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ChkMail.To.Add(reader["NOTES_MAIL"].ToString());
}
};
string mail_from = "tlsapp@ms.aidc.com.tw";
ChkMail.From = new MailAddress(mail_from, "AMM系統", System.Text.Encoding.UTF8);//發送者
string subject = "AMM V20107 工作步序資料維護 - 工時不完整(=0)";
string content = "附檔內的件號工作步序資料不完整<br>"
+ "請至 AMM V20107維護工作步序資料<br>";
//產生 *.xls 檔案
string Tmp_FName = "V20107工作步序資料維護.xlsx";
Tmp_FName = ExportFile1(Tmp_FName);
//附加檔案處理
ContentType ct = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
Attachment attm = new Attachment(Tmp_FName, ct);
ChkMail.Attachments.Add(attm);
sendMail(subject, content, ChkMail);
response.StatusCode = HttpStatusCode.OK;
Tmp_RtnMsg = "已 Email 通知相關人員 !! <br>";
response.Content = new StringContent("{'success': true,'Rtn_Msg':'" + Tmp_RtnMsg + "'}"); // 回應內容
return response;
//excuteSQLTran
} //try
catch (Exception e)
{
string Tmp_msg;
Tmp_msg = myfunc.Get1ORA(e.Message);
Tmp_RtnMsg = "Email 通知失敗<br>"
+ Tmp_msg;
response.Content = new StringContent("{'success': false,'Rtn_Msg':'" + Tmp_RtnMsg + "'}"); // 回應內容
return response;
}
finally
{
conn.Close();
}
} // end of DoMAIL
//產生 Email 附件檔案 , 傳回檔名
public string ExportFile1(string par_FName)
{
OracleConnection conn = new OracleConnection(DBService.ConnectionString(DBLINK));
OracleCommand cmd = new OracleCommand();
OracleDataReader reader;
string Tmp_USERID = LoginUserModel.LoginUserId;
Workbook wk = new Workbook();
MemoryStream stream = new MemoryStream();
try
{
conn.Open();
conn.ClientInfo = User.Identity.Name;
conn.ModuleName = BaseSYS + "_" + BaseMODID;
conn.ActionName = ActionName;
cmd.BindByName = true;
cmd.Connection = conn;
string Tmp_Sql;
//wk = new Workbook();
Worksheet sheet1 = wk.Worksheets[0];//獲取第一個工作表
string[] outFieldArray = { "部門編號", "工作中心", "EO_ECN", "情況處理碼", "件號",
"中文名稱","英文名稱","料號","建檔人員","建檔日期"};
for (int i = 0; i < outFieldArray.Length; i++)
{
sheet1.Range[myfunc.GetExcelPos(i, 0)].Text = outFieldArray[i];
}
//填寫 Email.From, To, subject, BODY
Tmp_Sql = " SELECT DISTINCT A.DEPID , A.WC , A.ECNO , A.CNDPROCCODE , A.PN , "
+ " A.CNM , A.ENM , A.NSN , A.ENER , A.ENDT "
+ " FROM AMM_WKSTP A, AMM_WKSTPD B "
+ " WHERE A.MITM = B.MITM "
+ " AND (B.WHR = '0' OR B.WHR IS NULL) "
+ " AND SUBSTR(DEPID,1,2) = ( SELECT SUBSTR(DEPID, 1, 2) "
+ " FROM HR_EMPLYM "
+ " WHERE EMPLYID = " + myfunc.AA(Tmp_USERID) + " ) ";
cmd.CommandText = Tmp_Sql;
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
int i = 0;
while (reader.Read())
{
sheet1.Range[myfunc.GetExcelPos(0, i + 1)].Text = reader["DEPID"].ToString();
sheet1.Range[myfunc.GetExcelPos(1, i + 1)].Text = reader["WC"].ToString();
sheet1.Range[myfunc.GetExcelPos(2, i + 1)].Text = reader["ECNO"].ToString();
sheet1.Range[myfunc.GetExcelPos(3, i + 1)].Text = reader["CNDPROCCODE"].ToString();
sheet1.Range[myfunc.GetExcelPos(4, i + 1)].Text = reader["PN"].ToString();
sheet1.Range[myfunc.GetExcelPos(5, i + 1)].Text = reader["CNM"].ToString();
sheet1.Range[myfunc.GetExcelPos(6, i + 1)].Text = reader["ENM"].ToString();
sheet1.Range[myfunc.GetExcelPos(7, i + 1)].Text = reader["NSN"].ToString();
sheet1.Range[myfunc.GetExcelPos(8, i + 1)].Text = reader["ENER"].ToString();
if (!myfunc.checkisnull(reader["ENDT"].ToString()))
{ sheet1.Range[myfunc.GetExcelPos(9, i + 1)].Text = String.Format("{0:yyyy/MM/dd}", DateTime.Parse(reader["ENDT"].ToString())); };
i = i + 1;
} // end of while (reader.Read())
} // end of if reader.HasRows
sheet1.AllocatedRange.AutoFitColumns();
wk.SaveToStream(stream, FileFormat.Version2007);
string documentPath = HttpContext.Current.Server.MapPath("~") + "document\\"; // 取得實實的路徑
string FileName1 = documentPath + par_FName;
wk.SaveToFile(FileName1, FileFormat.Version2007);
return FileName1;
}// end of try{
finally
{
conn.Close();
wk.Dispose();
}
} // end of ExportFile1
2>V30702E.cs
Tmp_Sql = " SELECT * "
+ " FROM AMM_DSPHCAR "
+ " WHERE TASKNO=" + myfunc.AA(Tmp_TASKNO);
Tmp_dt0 = myfunc.SqlOpen(Tmp_dt0, Tmp_Sql);
MailMessage ChkMail = new MailMessage();
string mail_from = "tlsapp@ms.aidc.com.tw";
ChkMail.From = new MailAddress(mail_from, "AMM系統", System.Text.Encoding.UTF8);
Tmp_Sql = " SELECT B.NOTES_MAIL "
+ " FROM HR_EMPLYM B "
+ " WHERE B.EMPLYID = " + myfunc.AA(Tmp_dt0.Rows[0]["RQMAN"].ToString());
string Tmp_toMail = myfunc.SqlValue(Tmp_Sql);
ChkMail.To.Add(Tmp_toMail);
string subject = "地裝派遣單剔退通知 - 派遣單號("+ Tmp_TASKNO+")";
string content = "地裝派遣單已剔退<br>"
+ "派遣單號:" + Tmp_TASKNO + "<br>"
+ "請至系統修正後再提出!!<br>"
+"<br>"
+ "詳細內容如下:<br>"
+ "派遣單號:" + myfunc.AA(Tmp_TASKNO) + "<br>"
+ "需求人:" + myfunc.AA(Tmp_dt0.Rows[0]["RQMAN"].ToString()) + "<br>"
+ "需求日期:" + myfunc.AA(Tmp_dt0.Rows[0]["RQDT"].ToString()) + "<br>"
+ "需求單位:" + myfunc.AA(Tmp_dt0.Rows[0]["RQDEP"].ToString()) + "<br>"
+ "起點:" + myfunc.AA(Tmp_dt0.Rows[0]["STLC"].ToString()) + "<br>"
+ "迄點:" + myfunc.AA(Tmp_dt0.Rows[0]["EDLC"].ToString()) + "<br>"
+ "工作簡述:" + myfunc.AA(Tmp_dt0.Rows[0]["TASKREMARK"].ToString()) + "<br>"
+ "剔退原因:" + myfunc.AA(Tmp_DELCAUSE) + "<br>"
+ "剔退人員:" + myfunc.AA(Tmp_DELMAN) + "<br>";
sendMail(subject, content, ChkMail);
Tmp_RtnMsg = "地裝派遣單號(" + Tmp_TASKNO + ")已[剔退]完成 & Email 原需求人員 !!";
