public static void insertFromAdvanceToData(string flow, ref CommandHelper sb) { DbHelper db = new DbHelper(_DBName); CommandHelper sb2 = db.CreateCommandHelper(); Aattendant At = new Aattendant(); string OTTxnID = ""; sb2.Append(" SELECT * FROM OverTimeAdvance WHERE FlowCaseID='" + flow.Substring(0, 14) + "'"); DataTable dtOT = db.ExecuteDataSet(sb2.BuildCommand()).Tables[0]; if (dtOT.Rows.Count > 0) { sb2.Reset(); for (int i = 0; i < dtOT.Rows.Count; i++) { //leo modify 20170110 OTTxnID OTSeqNo int OTSeq = At.QuerySeq("OverTimeDeclaration", dtOT.Rows[i]["OTCompID"].ToString(), dtOT.Rows[i]["OTEmpID"].ToString(), dtOT.Rows[i]["OTStartDate"].ToString()); if (dtOT.Rows[i]["OTSeqNo"].ToString() == "1") { OTTxnID = (UserInfo.getUserInfo().CompID + dtOT.Rows[i]["OTEmpID"].ToString() + Convert.ToDateTime(dtOT.Rows[i]["OTStartDate"]).ToString("yyyyMMdd") + OTSeq.ToString("00")); } sb.Append(" INSERT INTO OverTimeDeclaration(OTCompID,OTEmpID,OTStartDate,OTEndDate,OTSeq,OTTxnID,OTSeqNo,OTFromAdvanceTxnId,DeptID,OrganID,DeptName,OrganName,FlowCaseID,OTStartTime,OTEndTime,OTTotalTime,SalaryOrAdjust,AdjustInvalidDate,AdjustStatus,AdjustDate,MealFlag,MealTime,OTTypeID,OTReasonID,OTReasonMemo,OTAttachment,OTFormNO,OTRegisterID,OTRegisterDate,OTStatus,OTValidDate,OTValidID,OTRejectDate,OTRejectID,OTGovernmentNo,OTSalaryPaid,HolidayOrNot,ProcessDate,OTPayDate,OTModifyDate,OTRemark,KeyInComp,KeyInID,HRKeyInFlag,LastChgComp,LastChgID,LastChgDate,OTRegisterComp) "); sb.Append(" VALUES('" + dtOT.Rows[i]["OTCompID"] + "', '" + dtOT.Rows[i]["OTEmpID"] + "', '" + dtOT.Rows[i]["OTStartDate"] + "', '" + dtOT.Rows[i]["OTEndDate"] + "', '" + OTSeq + "',"); sb.Append(" '" + OTTxnID + "','" + dtOT.Rows[i]["OTSeqNo"] + "','" + dtOT.Rows[i]["OTTxnID"] + "',"); //leo modify 20170110 sb.Append(" '" + dtOT.Rows[i]["DeptID"] + "', '" + dtOT.Rows[i]["OrganID"] + "','" + dtOT.Rows[i]["DeptName"] + "','" + dtOT.Rows[i]["OrganName"] + "',"); sb.Append(" '', "); //流程ID sb.Append(" '" + dtOT.Rows[i]["OTStartTime"] + "', '" + dtOT.Rows[i]["OTEndTime"] + "', '" + dtOT.Rows[i]["OTTotalTime"] + "',"); sb.Append(" '" + dtOT.Rows[i]["SalaryOrAdjust"] + "' ,"); //轉薪資或補休 sb.Append(" '" + Convert.ToDateTime(dtOT.Rows[i]["AdjustInvalidDate"]).ToString("yyyy-MM-dd") + "', "); //失效時間 " '1900-01-01 00:00:00.000', " //失效時間 " '" + dtOT.Rows[i]["AdjustInvalidDate"] + "', " //20170309-leo modify //失效時間 " '" + Convert.ToDateTime(dtOT.Rows[i]["AdjustInvalidDate"]).ToString("yyyy-MM-dd") + "', " sb.Append(" '', "); sb.Append(" '1900-01-01 00:00:00.000', "); sb.Append(" '" + dtOT.Rows[i]["MealFlag"] + "', '" + dtOT.Rows[i]["MealTime"] + "', '" + dtOT.Rows[i]["OTTypeID"] + "',"); sb.Append(" '" + dtOT.Rows[i]["OTReasonID"] + "', "); //加班原因的代號 sb.Append(" '" + dtOT.Rows[i]["OTReasonMemo"] + "', "); sb.Append(" '" + dtOT.Rows[i]["OTAttachment"] + "', '" + dtOT.Rows[i]["OTFormNO"] + "', "); sb.Append(" '" + dtOT.Rows[i]["OTRegisterID"] + "', '" + Convert.ToDateTime(dtOT.Rows[i]["OTRegisterDate"]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "', '1',");//申請單狀態 sb.Append(" '1900-01-01 00:00:00.000', "); sb.Append(" '', "); sb.Append(" '1900-01-01 00:00:00.000', "); sb.Append(" '', "); sb.Append(" '', "); sb.Append(" '0', "); sb.Append(" '" + dtOT.Rows[i]["HolidayOrNot"] + "', "); sb.Append(" '1900-01-01 00:00:00.000', "); sb.Append(" '', "); sb.Append(" '1900-01-01 00:00:00.000', "); sb.Append(" '', "); sb.Append(" '" + dtOT.Rows[i]["LastChgComp"] + "', '" + dtOT.Rows[i]["OTRegisterID"] + "', "); sb.Append(" '', "); sb.Append(" '" + dtOT.Rows[i]["LastChgComp"] + "', "); sb.Append(" '" + dtOT.Rows[i]["OTRegisterID"] + "', "); sb.Append(" '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "',"); sb.Append(" '" + dtOT.Rows[0]["OTRegisterComp"] + "'); "); } } }
private string RankPara(DataTable dt, string CompID, string RankType) { string RankID = "0"; Aattendant a = new Aattendant(); if (dt.Select("CompID='" + CompID + "'").Count() > 0) { RankID = a.Json2DataTable(dt.Select("CompID='" + CompID + "'").CopyToDataTable().Rows[0]["Para"].ToString()).Rows[0][RankType].ToString(); return(RankID); } return(RankID); }
/// <summary> /// 寄EMail Log /// </summary> /// <param name="datas"></param> /// <returns></returns> private static void sendEMail(OnBizPublicOutBean datas, ref CommandHelper sb) { string Subject_1 = ""; string Content_1 = ""; string[] regArray = { "EmpID", "DeputyID", "ValidID" }; for (int i = 0; i < regArray.Length; i++) { string mail = ""; if (!SelectPersonMail(datas, regArray[i], out mail)) { SelectHRMail(datas.CompID, out mail); Subject_1 = "系統查無通知者E-mail"; if ("ValidID".Equals(regArray[i])) { Content_1 = "OnBizForValid||BM@SenderName||" + datas.ValidID + "-" + datas.ValidName + "||BM@EmpID||" + datas.EmpID + "||BM@EmpName||" + datas.EmpNameN; } else { Content_1 = "OnBizForEmp||BM@SenderName||" + datas.ValidID + "-" + datas.ValidName + "||BM@EmpID||" + datas.EmpID + "||BM@EmpName||" + datas.EmpNameN; } } else { Subject_1 = "【公出-待簽核通知】" + datas.OrganName + "(部門)-" + datas.EmpNameN + "(公出人)公出申請待簽核"; if ("ValidID".Equals(regArray[i])) { Content_1 = "OnBizForValid||BM@SenderName||" + datas.ValidID + "-" + datas.ValidName + "||BM@EmpID||" + datas.EmpID + "||BM@EmpName||" + datas.EmpNameN; } else { Content_1 = "OnBizForEmp||BM@SenderName||" + datas.ValidID + "-" + datas.ValidName + "||BM@EmpID||" + datas.EmpID + "||BM@EmpName||" + datas.EmpNameN; } } Aattendant.InsertMailLogCommand("人力資源處", datas.CompID, datas.ValidID, mail, "", "", Subject_1, Content_1, false, ref sb); } }
/// <summary> /// Judy給的寄信範本 /// </summary> public static void InsertMailLogCommand(string Sender, string AcceptorCompID, string Acceptor, string EMail, string Subject_1, string Content_1, bool isResetCommand, ref CommandHelper sb) { Aattendant at = new Aattendant(); var dateNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"); int seq = 1; DataTable dt = at.QueryData("ISNULL(MAX(Seq),0) AS Seq", Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[MailLog]", " AND CreateTime='" + dateNow + "'"); if (dt.Rows.Count > 0) { seq = Convert.ToInt32(dt.Rows[0]["Seq"].ToString()) + 1; } if (isResetCommand) { sb.Reset(); } sb.AppendStatement(" INSERT INTO " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[MailLog] "); sb.Append(" ( "); sb.Append(" CreateTime, Seq, Sender, AcceptorCompID, Acceptor, EMail, Subject, Content"); sb.Append(" ) "); sb.Append(" VALUES "); sb.Append(" ('" + dateNow + "','" + seq.ToString() + "','" + Sender + "','" + AcceptorCompID + "','" + Acceptor + "','" + EMail + "','" + Subject_1 + "','" + Content_1 + "' "); sb.Append(" ); "); }
/// <summary> /// 原本不允許送審重複主管,現在又改成可以,所以把這方法註解,改成不檢核直接查詢 /// </summary> /// <param name="SignID">下一關主管ID</param> /// <param name="SignIDComp">下一關主管公司</param> /// <param name="oAssDic">回傳Dictionary格式給永豐流程使用</param> // private void oAssDicCheck(string SignID, string SignIDComp, ref Dictionary<string, string> oAssDic) //{ // if (SignID.Trim().Equals(UserInfo.getUserInfo().UserID.Trim())) // { // oAssDic.Clear(); // } // else // { // oAssDic = CustVerify.getEmpID_Name_Dictionary(SignID, SignIDComp); // } //} protected void Page_Load(object sender, EventArgs e) { //代辦畫面傳來的多筆審核資料 DataTable dtOverTimeAdvance = (DataTable)Session["dtOverTimeAdvance"]; DataTable dtOverTimeDeclaration = (DataTable)Session["dtOverTimeDeclaration"]; //永豐流程相關資料 FlowExpress oFlow = new FlowExpress(Aattendant._AattendantFlowID, Request["FlowLogID"], true); //錯誤訊息儲存 string ErrMsg = ""; CustVerify CV = new CustVerify(); Dictionary <string, string> toUserData = (Dictionary <string, string>)Session["toUserData"]; //EmpInfo.QueryOrganData || EmpInfo.QueryFlowOrganData 使用 //string SignOrganID = "", SignID = "", SignIDComp = ""; if (!IsPostBack) { if (Session["FlowVerifyInfo"] != null) { #region "變數" //TrtCatchIsFlowVerify()使用 DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); DbConnection cn = db.OpenConnection(); DbTransaction tx = cn.BeginTransaction(); //撈取該筆加班單相關資料 DataTable dtOverTime; //預設下關待辦人(會變,沒變代表沒找到相關資料) Dictionary <string, string> oVerifyInfo = (Dictionary <string, string>)Session["FlowVerifyInfo"]; Util.setJSContent(oVerifyInfo["FlowVerifyJS"]); Session["FlowVerifyInfo"] = null; Dictionary <string, string> oAssDic = Util.getDictionary(oVerifyInfo["FlowStepAssignToList"]); //共用檔 Aattendant _Aattendant = new Aattendant(); //Para撈取參數設定 DataTable dtOverTimePara_All = db.ExecuteDataSet(CommandType.Text, string.Format("SELECT DISTINCT CompID,Para FROM OverTimePara ")).Tables[0]; string AD = ""; #endregion "變數" switch (oVerifyInfo["FlowStepBtnID"].ToString()) { case "btnClose": case "btnReApprove": case "FlowReassign": case "btnApprove": //審核 //單筆審核(使用oFlow.FlowCaseID判斷) if (dtOverTimeAdvance == null && dtOverTimeDeclaration == null) { #region "單筆審核" //給IsFlowVerify的下一關簽核主管 //oAssDicCheck(toUserData["SignID"], toUserData["SignIDComp"], ref oAssDic); //原本要檢核主管是否重複,後來說不要了 oAssDic = CustVerify.getEmpID_Name_Dictionary(toUserData["SignID"], toUserData["SignIDComp"]); switch (oFlow.FlowCurrStepID) { //事先 case "A10": case "A20": AD = "A"; break; //事後 case "A30": case "A40": AD = "D"; break; } dtOverTime = CustVerify.OverTime_find_by_FlowCaseID(oFlow.FlowCaseID, AD); if (oVerifyInfo["FlowStepBtnID"].ToString() == "btnClose") //結案 { sb.Reset(); CustVerify.UpdateOverTime(AD, "3", oFlow.FlowCaseID, ref sb); CustVerify.UpdateHROrverTimeLog(oFlow.FlowCaseID, "2", ref sb); CustVerify.CloseHROverTimeLog(oFlow.FlowCaseID, AD, ref sb); if (AD == "A") { CustVerify.insertFromAdvanceToData(oFlow.FlowCaseID, ref sb); } if (TryCatchIsFlowVerify(Request["FlowLogID"], oVerifyInfo["FlowStepBtnID"], oAssDic, oVerifyInfo["FlowStepOpinion"].ToString().Replace("'", "''"), sb, out ErrMsg, dtOverTime.Rows[0], true)) { CV.MailLogContent(oFlow.FlowCaseID, "3", AD, "", "", true); labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Succeed, "審核成功!"); } else { labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Error, "審核失敗"); } } else //沒結案 { sb.Reset(); DataTable dtHROverTimeLog = CustVerify.HROverTimeLog(oFlow.FlowCaseID, false); CustVerify.UpdateOverTime(AD, "2", oFlow.FlowCaseID, ref sb); CustVerify.UpdateHROrverTimeLog(oFlow.FlowCaseID, "2", ref sb); FlowUtility.InsertHROverTimeLogCommand( oFlow.FlowCaseID, CustVerify.addone(dtHROverTimeLog.Rows[0]["FlowLogBatNo"].ToString()), CustVerify.FlowLogIDadd(dtHROverTimeLog.Rows[0]["FlowLogID"].ToString()), AD, dtHROverTimeLog.Rows[0]["OTEmpID"].ToString(), dtHROverTimeLog.Rows[0]["EmpOrganID"].ToString(), dtHROverTimeLog.Rows[0]["EmpFlowOrganID"].ToString(), UserInfo.getUserInfo().UserID, dtHROverTimeLog.Rows[0]["FlowCode"].ToString(), dtHROverTimeLog.Rows[0]["FlowSN"].ToString(), CustVerify.addone(dtHROverTimeLog.Rows[0]["FlowSeq"].ToString()), toUserData["SignLine"], toUserData["SignIDComp"], toUserData["SignID"], toUserData["SignOrganID"], toUserData["SignFlowOrganID"], "1", false, ref sb, int.Parse(dtHROverTimeLog.Rows[0]["Seq"].ToString()) + 1 ); if (TryCatchIsFlowVerify(Request["FlowLogID"], oVerifyInfo["FlowStepBtnID"], oAssDic, oVerifyInfo["FlowStepOpinion"].ToString().Replace("'", "''"), sb, out ErrMsg, dtOverTime.Rows[0], true)) { CV.MailLogContent(oFlow.FlowCaseID, "2", AD, "", "", false); labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Succeed, "審核成功!"); } else { labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Error, "審核失敗"); } } #endregion "單筆審核" } /*================================================*/ //多筆審核有四個迴圈,事先兩個是後兩個 //UpdateAD、HROverTimeLog、MailLog在第一迴圈 //其他在第二迴圈 //多筆審核按鈕(非印章) else { #region "事先多筆" if (dtOverTimeAdvance.Rows.Count > 0) { AD = "A"; for (int i = 0; i < dtOverTimeAdvance.Rows.Count; i++) { if (dtOverTimeAdvance.Rows[i]["OTSeqNo"].ToString() == "1") { oFlow = new FlowExpress(Aattendant._AattendantFlowID, CustVerify.getFlowLogID(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), "A"), true); string btnName = dtOverTimeAdvance.Rows[i]["btnName"].ToString(); //oAssDicCheck(dtOverTimeAdvance.Rows[i]["SignID"].ToString(), dtOverTimeAdvance.Rows[i]["SignIDComp"].ToString(), ref oAssDic); oAssDic = CustVerify.getEmpID_Name_Dictionary(dtOverTimeAdvance.Rows[i]["SignID"].ToString(), dtOverTimeAdvance.Rows[i]["SignIDComp"].ToString()); if (btnName == "btnClose") //結案 { sb.Reset(); CustVerify.UpdateOverTime(AD, "3", dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), ref sb); CustVerify.UpdateHROrverTimeLog(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), "2", ref sb); CustVerify.CloseHROverTimeLog(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), AD, ref sb); CustVerify.insertFromAdvanceToData(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), ref sb); if (TryCatchIsFlowVerify(oFlow.FlowCurrLastLogID, btnName, oAssDic, oVerifyInfo["FlowStepOpinion"].ToString().Replace("'", "''"), sb, out ErrMsg, dtOverTimeAdvance.Rows[i], false)) { CV.MailLogContent(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), "3", AD, "", "", true); labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Succeed, "審核成功!"); } else { labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Error, "審核失敗"); } } else //沒結案 { sb.Reset(); DataTable dtHROverTimeLog = CustVerify.HROverTimeLog(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), false); CustVerify.UpdateOverTime(AD, "2", dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), ref sb); CustVerify.UpdateHROrverTimeLog(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), "2", ref sb); FlowUtility.InsertHROverTimeLogCommand( dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), CustVerify.addone(dtHROverTimeLog.Rows[0]["FlowLogBatNo"].ToString()), CustVerify.FlowLogIDadd(dtHROverTimeLog.Rows[0]["FlowLogID"].ToString()), AD, dtHROverTimeLog.Rows[0]["OTEmpID"].ToString(), dtHROverTimeLog.Rows[0]["EmpOrganID"].ToString(), dtHROverTimeLog.Rows[0]["EmpFlowOrganID"].ToString(), UserInfo.getUserInfo().UserID, dtHROverTimeLog.Rows[0]["FlowCode"].ToString(), dtHROverTimeLog.Rows[0]["FlowSN"].ToString(), CustVerify.addone(dtHROverTimeLog.Rows[0]["FlowSeq"].ToString()), //toUserData start dtOverTimeAdvance.Rows[i]["SignLine"].ToString(), dtOverTimeAdvance.Rows[i]["SignIDComp"].ToString(), dtOverTimeAdvance.Rows[i]["SignID"].ToString(), dtOverTimeAdvance.Rows[i]["SignOrganID"].ToString(), dtOverTimeAdvance.Rows[i]["SignFlowOrganID"].ToString(), //toUserData end "1", false, ref sb, int.Parse(dtHROverTimeLog.Rows[0]["Seq"].ToString()) + 1 ); if (TryCatchIsFlowVerify(oFlow.FlowCurrLastLogID, btnName, oAssDic, oVerifyInfo["FlowStepOpinion"].ToString().Replace("'", "''"), sb, out ErrMsg, dtOverTimeAdvance.Rows[i], false)) { CV.MailLogContent(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), "2", AD, "", "", false); labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Succeed, "審核成功!"); } else { labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Error, "審核失敗"); } } } } } #endregion "事先多筆" /*------------------*/ #region "事後多筆" if (dtOverTimeDeclaration.Rows.Count > 0) { AD = "D"; for (int i = 0; i < dtOverTimeDeclaration.Rows.Count; i++) { if (dtOverTimeDeclaration.Rows[i]["OTSeqNo"].ToString() == "1") { oFlow = new FlowExpress(Aattendant._AattendantFlowID, CustVerify.getFlowLogID(dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), "D"), true); string btnName = dtOverTimeDeclaration.Rows[i]["btnName"].ToString(); //oAssDicCheck(dtOverTimeDeclaration.Rows[i]["SignID"].ToString(), dtOverTimeDeclaration.Rows[i]["SignIDComp"].ToString(), ref oAssDic); oAssDic = CustVerify.getEmpID_Name_Dictionary(dtOverTimeDeclaration.Rows[i]["SignID"].ToString(), dtOverTimeDeclaration.Rows[i]["SignIDComp"].ToString()); if (btnName == "btnClose") //結案 { sb.Reset(); CustVerify.UpdateOverTime(AD, "3", dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), ref sb); CustVerify.UpdateHROrverTimeLog(dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), "2", ref sb); CustVerify.CloseHROverTimeLog(dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), AD, ref sb); if (TryCatchIsFlowVerify(oFlow.FlowCurrLastLogID, btnName, oAssDic, oVerifyInfo["FlowStepOpinion"].ToString().Replace("'", "''"), sb, out ErrMsg, dtOverTimeDeclaration.Rows[i], false)) { CV.MailLogContent(dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), "3", AD, "", "", true); labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Succeed, "審核成功!"); } else { labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Error, "審核失敗"); } } else //沒結案 { sb.Reset(); DataTable dtHROverTimeLog = CustVerify.HROverTimeLog(dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), false); CustVerify.UpdateOverTime(AD, "2", dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), ref sb); CustVerify.UpdateHROrverTimeLog(dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), "2", ref sb); FlowUtility.InsertHROverTimeLogCommand( dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), CustVerify.addone(dtHROverTimeLog.Rows[0]["FlowLogBatNo"].ToString()), CustVerify.FlowLogIDadd(dtHROverTimeLog.Rows[0]["FlowLogID"].ToString()), AD, dtHROverTimeLog.Rows[0]["OTEmpID"].ToString(), dtHROverTimeLog.Rows[0]["EmpOrganID"].ToString(), dtHROverTimeLog.Rows[0]["EmpFlowOrganID"].ToString(), UserInfo.getUserInfo().UserID, dtHROverTimeLog.Rows[0]["FlowCode"].ToString(), dtHROverTimeLog.Rows[0]["FlowSN"].ToString(), CustVerify.addone(dtHROverTimeLog.Rows[0]["FlowSeq"].ToString()), //toUserData start dtOverTimeDeclaration.Rows[i]["SignLine"].ToString(), dtOverTimeDeclaration.Rows[i]["SignIDComp"].ToString(), dtOverTimeDeclaration.Rows[i]["SignID"].ToString(), dtOverTimeDeclaration.Rows[i]["SignOrganID"].ToString(), dtOverTimeDeclaration.Rows[i]["SignFlowOrganID"].ToString(), //toUserData end "1", false, ref sb, int.Parse(dtHROverTimeLog.Rows[0]["Seq"].ToString()) + 1 ); if (TryCatchIsFlowVerify(oFlow.FlowCurrLastLogID, btnName, oAssDic, oVerifyInfo["FlowStepOpinion"].ToString().Replace("'", "''"), sb, out ErrMsg, dtOverTimeDeclaration.Rows[i], false)) { CV.MailLogContent(dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), "2", AD, "", "", false); labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Succeed, "審核成功!"); } else { labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Error, "審核失敗"); } } } } } #endregion "事後多筆" } break; /*=================================================*/ case "btnReject": //駁回 if (dtOverTimeAdvance == null && dtOverTimeDeclaration == null) //true 印章 false 多筆審核按鈕 { #region "單筆駁回" switch (oFlow.FlowCurrStepID) { //事先 case "A10": case "A20": AD = "A"; break; //事後 case "A30": case "A40": AD = "D"; break; } dtOverTime = CustVerify.OverTime_find_by_FlowCaseID(oFlow.FlowCaseID, AD); sb.Reset(); CustVerify.UpdateOverTime(AD, "4", oFlow.FlowCaseID, ref sb); CustVerify.UpdateHROrverTimeLog(oFlow.FlowCaseID, "3", ref sb); CustVerify.CloseHROverTimeLog(oFlow.FlowCaseID, AD, ref sb); if (AD == "D") { CustVerify.AfterReject_CheckAndInsert(oFlow.FlowCaseID, ref sb); } //ClearBtn(oFlow.FlowCurrLogAssignTo); if (TryCatchIsFlowVerify(Request["FlowLogID"], "btnReject", oAssDic, oVerifyInfo["FlowStepOpinion"].ToString().Replace("'", "''"), sb, out ErrMsg, dtOverTime.Rows[0], true)) { CV.MailLogContent(oFlow.FlowCaseID, "4", AD, "", "", true); labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Succeed, "審核成功!"); } else { labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Error, "審核失敗"); } #endregion "單筆駁回" } else //多筆審核按鈕 { #region "多筆駁回" if (dtOverTimeAdvance.Rows.Count > 0) { AD = "A"; for (int i = 0; i < dtOverTimeAdvance.Rows.Count; i++) { if (dtOverTimeAdvance.Rows[i]["OTSeqNo"].ToString() == "1") { oFlow = new FlowExpress(Aattendant._AattendantFlowID, CustVerify.getFlowLogID(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), "A"), true); sb.Reset(); CustVerify.UpdateOverTime(AD, "4", dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), ref sb); CustVerify.UpdateHROrverTimeLog(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), "3", ref sb); CustVerify.CloseHROverTimeLog(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), AD, ref sb); //ClearBtn(oFlow.FlowCurrLogAssignTo); if (TryCatchIsFlowVerify(oFlow.FlowCurrLastLogID, "btnReject", oAssDic, oVerifyInfo["FlowStepOpinion"].ToString().Replace("'", "''"), sb, out ErrMsg, dtOverTimeAdvance.Rows[i], false)) { CV.MailLogContent(dtOverTimeAdvance.Rows[i]["FlowCaseID"].ToString(), "4", AD, "", "", true); labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Succeed, "審核成功!"); } else { labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Error, "審核失敗"); } } } } if (dtOverTimeDeclaration.Rows.Count > 0) { AD = "D"; for (int i = 0; i < dtOverTimeDeclaration.Rows.Count; i++) { if (dtOverTimeDeclaration.Rows[i]["OTSeqNo"].ToString() == "1") { oFlow = new FlowExpress(Aattendant._AattendantFlowID, CustVerify.getFlowLogID(dtOverTimeDeclaration.Rows[i]["FlowCaseID"].ToString(), "D"), true); sb.Reset(); CustVerify.UpdateOverTime(AD, "4", oFlow.FlowCaseID, ref sb); CustVerify.UpdateHROrverTimeLog(oFlow.FlowCaseID, "3", ref sb); CustVerify.CloseHROverTimeLog(oFlow.FlowCaseID, AD, ref sb); CustVerify.AfterReject_CheckAndInsert(oFlow.FlowCaseID, ref sb); //ClearBtn(oFlow.FlowCurrLogAssignTo); if (TryCatchIsFlowVerify(oFlow.FlowCurrLastLogID, "btnReject", oAssDic, oVerifyInfo["FlowStepOpinion"].ToString().Replace("'", "''"), sb, out ErrMsg, dtOverTimeDeclaration.Rows[i], false)) { CV.MailLogContent(oFlow.FlowCaseID, "4", AD, "", "", true); labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Succeed, "審核成功!"); } else { labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.Error, "審核失敗"); } } } } #endregion "多筆駁回" } break; /*==============================*/ } if (txtErrMsg.Text != "") { txtErrMsg.Text = "審核失敗清單:" + txtErrMsg.Text; txtErrMsg.Visible = true; } } else { //參數錯誤 labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.ParaError); } } }
public void subGetData() { Aattendant at = new Aattendant(); DbHelper db = new DbHelper(_DBName); CommandHelper sb = db.CreateCommandHelper(); DataTable dt = null; double cntStart = 0; double cntEnd = 0; double cntTotal = 0; sb.Append(" SELECT * FROM ("); sb.Append(" SELECT OT.OTEmpID,OT.OTCompID,C.CompName,OT.DeptID,OT.DeptName,OT.OrganID,OT.OrganName,OT.OTFormNO,P.NameN,OT.OTRegisterID,PR.NameN AS RegisterNameN,OT.OTTxnID,OTT.CodeCName,ISNULL(AI.FileName,'') AS FileName,OT.OTStatus,OT.MealFlag,isnull(OT.MealTime,0)+isnull(OT2.MealTime,0) AS MealTime, OT.OTSeq,"); sb.Append(" Case OT.OTStatus WHEN '1' THEN '暫存' WHEN '2' THEN '送簽' WHEN '3' THEN '核准' WHEN '4' THEN '駁回' WHEN '5' THEN '刪除' WHEN '6' THEN '取消' WHEN '7' THEN '作廢' WHEN '9' THEN '計薪後收回' END AS OTStatusName,"); sb.Append(" CASE OT.SalaryOrAdjust WHEN '1' THEN '轉薪資' WHEN '2' THEN '轉補休' END AS SalaryOrAdjustName,OT.OTAttachment, "); sb.Append(" (OT.OTStartDate+'~'+isnull(OT2.OTEndDate,OT.OTEndDate)) AS OTDate,"); sb.Append(" (OT.OTStartTime+'~'+isnull(OT2.OTEndTime,OT.OTEndTime)) AS OTTime, OT.AdjustInvalidDate,"); //sb.Append(" ((CAST(OT.OTTotalTime AS FLOAT)-CAST(OT.MealTime AS FLOAT))/CAST(60 AS FLOAT))+((CAST(ISNULL(OT2.OTTotalTime,0) AS FLOAT)-CAST(ISNULL(OT2.MealTime,0) AS FLOAT))/CAST(60 AS FLOAT)) AS OTTotalTime,"); sb.Append(" Convert(Decimal(10,1),ROUND(Convert(Decimal(10,2),((CAST(OT.OTTotalTime AS FLOAT)-CAST(OT.MealTime AS FLOAT))/CAST(60 AS FLOAT))+((CAST(ISNULL(OT2.OTTotalTime,0) AS FLOAT)-CAST(ISNULL(OT2.MealTime,0) AS FLOAT))/CAST(60 AS FLOAT))),1)) AS OTTotalTime,"); sb.Append(" OT.LastChgID,PL.NameN AS LastChgNameN,OT.LastChgDate,OT.OTReasonMemo,OT.OTPayDate"); sb.Append(" FROM OverTimeDeclaration OT "); sb.Append(" LEFT JOIN OverTimeDeclaration OT2 on OT2.OTTxnID=OT.OTTxnID AND OT2.OTSeqNo=2 AND OT2.OverTimeFlag='1'"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Company] C ON C.CompID=OT.OTCompID AND C.InValidFlag = '0' And C.NotShowFlag = '0'"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Personal] P ON P.CompID = OT.OTCompID AND P.EmpID=OT.OTEmpID"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Personal] PR ON PR.CompID = OT.OTRegisterComp AND PR.EmpID=OT.OTRegisterID"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Company] C2 ON C2.CompID=OT.LastChgComp AND C2.InValidFlag = '0' And C2.NotShowFlag = '0'"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Personal] PL ON PL.EmpID=OT.LastChgID AND PL.CompID = C2.CompID "); sb.Append(" LEFT JOIN AttachInfo AI ON AI.AttachID IS NOT NULL AND AI.AttachID <> '' AND AI.AttachID = OT.OTAttachment AND FileSize > 0"); sb.Append(" LEFT JOIN AT_CodeMap AS OTT ON OT.OTTypeID = OTT.Code AND OTT.TabName='OverTime' AND OTT.FldName='OverTimeType'"); sb.Append(" WHERE OT.OTSeqNo=1 AND OT.OTTxnID='" + _OTTxnID + "' AND OT.OverTimeFlag='1') A"); sb.Append(" WHERE 1=1 AND A.OTCompID = '" + _OTCompID + "' AND A.OTEmpID = '" + _EmpID + "'"); sb.Append(" AND A.OTDate='" + _OTStartDate + "~" + _OTEndDate + "'"); sb.Append(" AND A.OTTime='" + _OTStartTime + "~" + _OTEndTime + "'"); dt = db.ExecuteDataSet(sb.BuildCommand()).Tables[0]; btnAttachDownload.ucAttachDB = _DBName; btnAttachDownload.ucAttachID = dt.Rows[0]["OTAttachment"].ToString(); if (dt.Rows[0]["FileName"].ToString() == "") { btnAttachDownload.Visible = false; } else { lblAttachName.Text = dt.Rows[0]["FileName"].ToString(); } lblCompName.Text = dt.Rows[0]["CompName"].ToString(); lblDeptName.Text = dt.Rows[0]["DeptName"].ToString(); lblOrganName.Text = dt.Rows[0]["OrganName"].ToString(); lblEmpName.Text = dt.Rows[0]["OTEmpID"].ToString() + " " + dt.Rows[0]["NameN"].ToString(); lblOTRegisterName.Text = dt.Rows[0]["OTRegisterID"].ToString() + " " + dt.Rows[0]["RegisterNameN"].ToString(); lblOTDateValue.Text = _OTStartDate; lblOTDateValueEnd.Text = _OTEndDate; lblBeginTime.Text = _OTStartTime.Substring(0, 2) + ":" + _OTStartTime.Substring(2, 2); lblEndTime.Text = _OTEndTime.Substring(0, 2) + ":" + _OTEndTime.Substring(2, 2); chkMealFlag.Checked = (dt.Rows[0]["MealFlag"].ToString() == "1") ? true : false; lblMealTime.Text = dt.Rows[0]["MealTime"].ToString(); lblOTTotalTime.Text = Aattendant.GetNumString(dt.Rows[0]["OTTotalTime"].ToString(), 1); lblOTTypeID.Text = dt.Rows[0]["CodeCName"].ToString(); lblSalaryOrAdjust.Text = dt.Rows[0]["SalaryOrAdjustName"].ToString(); if (lblSalaryOrAdjust.Text == "轉補休") { lblAdjustInvalidDate.Visible = true; txtAdjustInvalidDate.Visible = true; } string ottxnid = dt.Rows[0]["OTTxnID"].ToString(); txtAdjustInvalidDate.Text = Convert.ToDateTime(dt.Rows[0]["AdjustInvalidDate"].ToString()).ToString("yyyy/MM/dd"); lblOTReasonMemo.Text = dt.Rows[0]["OTReasonMemo"].ToString(); lblLastChgID.Text = dt.Rows[0]["LastChgID"].ToString(); lblLastChgName.Text = dt.Rows[0]["LastChgNameN"].ToString(); lblLastChgDate.Text = Convert.ToDateTime(dt.Rows[0]["LastChgDate"].ToString()).ToString("yyyy/MM/dd HH:mm:ss"); if (dt.Rows[0]["OTPayDate"].ToString() == "0") { lblOTPayDate.Text = ""; } else { lblOTPayDate.Text = dt.Rows[0]["OTPayDate"].ToString(); } string otseq = dt.Rows[0]["OTSeq"].ToString(); SignData(); if (_OTStartDate == _OTEndDate) { cntTotal = (Convert.ToDouble(_OTEndTime.Substring(0, 2)) * 60 + Convert.ToDouble(_OTEndTime.Substring(2, 2))) - (Convert.ToDouble(_OTStartTime.Substring(0, 2)) * 60 + Convert.ToDouble(_OTStartTime.Substring(2, 2))); #region "計算時段" string returnPeriodCount = ""; bool bOTTimeStart = !string.IsNullOrEmpty(_OTStartTime); bool bOTTimeEnd = !string.IsNullOrEmpty(_OTEndTime); if (bOTTimeStart && bOTTimeEnd) { trTwo.Visible = false; int iOTTimeStart = 0; int iOTTimeEnd = 0; if (int.TryParse(_OTStartTime, out iOTTimeStart) && int.TryParse(_OTEndTime, out iOTTimeEnd)) { string mealFlag = chkMealFlag.Checked ? "1" : "0"; string sMealTime = string.IsNullOrEmpty(lblMealTime.Text) ? "0" : lblMealTime.Text.Trim(); int iMealTime = 0; int.TryParse(sMealTime, out iMealTime); bool bPeriodCount = at.PeriodCount("OverTimeDeclaration", _EmpID, cntTotal, 0, iOTTimeStart, iOTTimeEnd, _OTStartDate, 0, 0, "1900/01/01", iMealTime, mealFlag, ottxnid, out returnPeriodCount); if (bPeriodCount && !string.IsNullOrEmpty(returnPeriodCount) && returnPeriodCount.Split(';').Length > 0) { var sReturnPeriodList = returnPeriodCount.Split(';'); for (var i = 0; i < sReturnPeriodList.Length; i++) { var datas = sReturnPeriodList[i]; lblPeriod.Visible = true; tbTime.Visible = true; if (i == 0 && datas.Split(',').Length > 0 && datas.Split(',')[0] != "1900/01/01") { trOne.Visible = true; if (datas.Split(',').Length >= 1) { lblDateOne.Text = datas.Split(',')[0] == "1900/01/01" ? "-" : datas.Split(',')[0]; } if (datas.Split(',').Length >= 2) { lblDateOne_0.Text = datas.Split(',')[1] == "0.0" ? "-" : datas.Split(',')[1]; } if (datas.Split(',').Length >= 3) { lblDateOne_1.Text = datas.Split(',')[2] == "0.0" ? "-" : datas.Split(',')[2]; } if (datas.Split(',').Length >= 4) { lblDateOne_2.Text = datas.Split(',')[3] == "0.0" ? "-" : datas.Split(',')[3]; } } if (i == 1 && datas.Split(',').Length > 0 && datas.Split(',')[0] != "1900/01/01") { trTwo.Visible = true; if (datas.Split(',').Length >= 1) { lblDateTwo.Text = datas.Split(',')[0] == "1900/01/01" ? "-" : datas.Split(',')[0]; } if (datas.Split(',').Length >= 2) { lblDateTwo_0.Text = datas.Split(',')[1] == "0.0" ? "-" : datas.Split(',')[1]; } if (datas.Split(',').Length >= 3) { lblDateTwo_1.Text = datas.Split(',')[2] == "0.0" ? "-" : datas.Split(',')[2]; } if (datas.Split(',').Length >= 4) { lblDateTwo_2.Text = datas.Split(',')[3] == "0.0" ? "-" : datas.Split(',')[3]; } } } txtTotalDescription.Text = (lblMealTime.Text != "" && Convert.ToDouble(lblMealTime.Text) > 0 && chkMealFlag.Checked == true) ? "(已扣除用餐時數" + lblMealTime.Text + "分鐘)" : ""; txtTotalDescription.Visible = true; string meal = (chkMealFlag.Checked == false) ? "0" : lblMealTime.Text; lblOTTotalTime.Text = Convert.ToDouble((cntTotal - Convert.ToDouble(meal)) / 60).ToString("0.0"); } } } #endregion "計算時段" } else { cntStart = (23 - (Convert.ToDouble(_OTStartTime.Substring(0, 2)))) * 60 + (60 - Convert.ToDouble(_OTStartTime.Substring(2, 2))); cntEnd = (Convert.ToDouble(_OTEndTime.Substring(0, 2)) * 60 + Convert.ToDouble(_OTEndTime.Substring(2, 2))); #region "計算時段" string returnPeriodCount = ""; bool bOTTimeStart = !string.IsNullOrEmpty(_OTStartTime); bool bOTTimeEnd = !string.IsNullOrEmpty(_OTEndTime); if (bOTTimeStart && bOTTimeEnd) { trTwo.Visible = false; int iOTTimeStart = 0; int iOTTimeEnd = 0; if (int.TryParse(_OTStartTime, out iOTTimeStart) && int.TryParse(_OTEndTime, out iOTTimeEnd)) { string mealFlag = chkMealFlag.Checked ? "1" : "0"; string sMealTime = string.IsNullOrEmpty(lblMealTime.Text) ? "0" : lblMealTime.Text.Trim(); int iMealTime = 0; int.TryParse(sMealTime, out iMealTime); bool bPeriodCount = at.PeriodCount("OverTimeDeclaration", _EmpID, cntStart, cntEnd, iOTTimeStart, 2359, _OTStartDate, 0, iOTTimeEnd, _OTEndDate, iMealTime, mealFlag, ottxnid, out returnPeriodCount); if (bPeriodCount && !string.IsNullOrEmpty(returnPeriodCount) && returnPeriodCount.Split(';').Length > 0) { var sReturnPeriodList = returnPeriodCount.Split(';'); for (var i = 0; i < sReturnPeriodList.Length; i++) { var datas = sReturnPeriodList[i]; lblPeriod.Visible = true; tbTime.Visible = true; if (i == 0 && datas.Split(',').Length > 0 && datas.Split(',')[0] != "1900/01/01") { trOne.Visible = true; if (datas.Split(',').Length >= 1) { lblDateOne.Text = datas.Split(',')[0] == "1900/01/01" ? "-" : datas.Split(',')[0]; } if (datas.Split(',').Length >= 2) { lblDateOne_0.Text = datas.Split(',')[1] == "0.0" ? "-" : datas.Split(',')[1]; } if (datas.Split(',').Length >= 3) { lblDateOne_1.Text = datas.Split(',')[2] == "0.0" ? "-" : datas.Split(',')[2]; } if (datas.Split(',').Length >= 4) { lblDateOne_2.Text = datas.Split(',')[3] == "0.0" ? "-" : datas.Split(',')[3]; } } if (i == 1 && datas.Split(',').Length > 0 && datas.Split(',')[0] != "1900/01/01") { trTwo.Visible = true; if (datas.Split(',').Length >= 1) { lblDateTwo.Text = datas.Split(',')[0] == "1900/01/01" ? "-" : datas.Split(',')[0]; } if (datas.Split(',').Length >= 2) { lblDateTwo_0.Text = datas.Split(',')[1] == "0.0" ? "-" : datas.Split(',')[1]; } if (datas.Split(',').Length >= 3) { lblDateTwo_1.Text = datas.Split(',')[2] == "0.0" ? "-" : datas.Split(',')[2]; } if (datas.Split(',').Length >= 4) { lblDateTwo_2.Text = datas.Split(',')[3] == "0.0" ? "-" : datas.Split(',')[3]; } } } txtTotalDescription.Text = (lblMealTime.Text != "" && Convert.ToDouble(lblMealTime.Text) > 0 && chkMealFlag.Checked == true) ? "(已扣除用餐時數" + lblMealTime.Text + "分鐘)" : ""; txtTotalDescription.Visible = true; string meal = (chkMealFlag.Checked == false) ? "0" : lblMealTime.Text; lblOTTotalTime.Text = Convert.ToDouble((cntEnd + cntStart - Convert.ToDouble(meal)) / 60).ToString("0.0"); } } } #endregion "計算時段" } }
public void subGetData() { _CurrFlowCaseID = _CurrFlowLogID.Remove(14); DbHelper db = new DbHelper(_DBName); CommandHelper sb = db.CreateCommandHelper(); Aattendant at = new Aattendant(); string strdata = ""; double cntStart = 0; double cntEnd = 0; double cntTotal = 0; sb.Append("SELECT OT.OTCompID,C.CompName,OT.DeptID,OT.DeptName,OT.OrganID,OT.OrganName,OT.SalaryOrAdjust,CONVERT(varchar(12),OT.AdjustInvalidDate,111) as AdjustInvalidDate,P.NameN,OT.OTRegisterID,PR.NameN AS RegisterNameN,"); sb.Append("OT.OTTxnID,OT.OTStartTime,"); sb.Append("isnull(OT1.OTEndTime,OT.OTEndTime) as OTEndTime,isnull(OT1.OTEndDate,OT.OTEndDate) as OTEndDate,"); sb.Append(" OT.MealFlag,OT.MealTime+isnull(OT1.MealTime,0) as MealTime,OT.OTTotalTime+isnull(OT1.OTTotalTime,0) as OTTotalTime,OTT.CodeCName as OTTypeID,OT.OTReasonID,OT.OTReasonMemo,"); //sb.Append(" OT.MealFlag,OT.MealTime+isnull(OT1.MealTime,0) as MealTime,OT.OTTotalTime+isnull(OT1.OTTotalTime,0) as OTTotalTime,OTT.OTTypeName as OTTypeID,OT.OTReasonID,OT.OTReasonMemo,"); sb.Append(" OT.LastChgID,PL.NameN AS LastChgNameN,CONVERT(varchar(12),OT.LastChgDate,111)+' '+CONVERT(varchar(8),OT.LastChgDate,108) as LastChgDate,ISNULL(AI.FileName,'') AS FileName,OT.OTAttachment,'A' as Input "); //時間分割 //sb.Append(" OT1.OTTotalTime as StartHr,OT.OTTotalTime as EndHr"); sb.Append(" FROM OverTimeAdvance OT"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Company] C ON C.CompID=OT.OTCompID AND C.InValidFlag = '0' And C.NotShowFlag = '0'"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Personal] P ON P.CompID = OT.OTCompID AND P.EmpID=OT.OTEmpID"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Personal] PR ON PR.CompID = OT.OTRegisterComp AND PR.EmpID=OT.OTRegisterID"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Personal] PL ON PL.CompID = OT.LastChgComp AND PL.EmpID=OT.LastChgID"); //sb.Append(" LEFT JOIN AttachInfo AI ON AI.AttachID = OT.OTAttachment"); sb.Append(" LEFT JOIN AttachInfo AI ON AI.AttachID IS NOT NULL AND AI.AttachID <> '' AND AI.AttachID = OT.OTAttachment AND FileSize > 0");//20170209 leo modify -參考Judy的After //sb.Append(" LEFT JOIN OverTimeAdvance OT1 on OT1.OTCompID='" + _CurrOTCompID + "' AND OT1.OTEmpID='" + _CurrOTEmpID + "' AND OT1.OTStartDate='" + _CurrOTEndDate + "' AND OT1.OTSeqNo='" + 2 + "' AND OT1.OTEndDate='" + _CurrOTEndDate + "' AND OT1.FlowCaseID='" + _CurrFlowCaseID + "'"); sb.Append(" LEFT JOIN OverTimeAdvance OT1 on OT1.OTCompID='" + _CurrOTCompID + "' AND OT1.OTEmpID='" + _CurrOTEmpID + "' AND OT1.OTSeqNo='" + 2 + "' AND OT1.FlowCaseID='" + _CurrFlowCaseID + "'"); sb.Append(" LEFT JOIN AT_CodeMap OTT ON OTT.TabName='OverTime' and OTT.FldName='OverTimeType' and OT.OTTypeID = OTT.Code"); //sb.Append(" LEFT JOIN OverTimeType OTT ON OT.OTCompID = OTT.CompID and OT.OTTypeID = OTT.OTTypeId"); sb.Append(" WHERE OT.OTCompID='" + _CurrOTCompID + "'"); sb.Append(" AND OT.OTEmpID='" + _CurrOTEmpID + "'"); sb.Append(" AND OT.OTStartDate='" + _CurrOTStartDate + "'"); sb.Append(" AND OT.OTSeq='" + _CurrOTSeq + "'"); sb.Append(" AND OT.OTEndDate='" + _CurrOTStartDate + "'"); sb.Append(" AND OT.FlowCaseID='" + _CurrFlowCaseID + "'"); sb.Append(" UNION"); sb.Append(" SELECT OD.OTCompID,C.CompName,OD.DeptID,OD.DeptName,OD.OrganID,OD.OrganName,OD.SalaryOrAdjust,CONVERT(varchar(12),OD.AdjustInvalidDate,111) as AdjustInvalidDate,P.NameN,OD.OTRegisterID,PR.NameN AS RegisterNameN,"); sb.Append("OD.OTTxnID,OD.OTStartTime,"); //sb.Append(" OD.OTEndTime,OD.OTEndDate,"); sb.Append("isnull(OD1.OTEndTime,OD.OTEndTime) as OTEndTime,isnull(OD1.OTEndDate,OD.OTEndDate) as OTEndDate,"); sb.Append(" OD.MealFlag,OD.MealTime+isnull(OD1.MealTime,0) as MealTime,OD.OTTotalTime+isnull(OD1.OTTotalTime,0) as OTTotalTime,ODT.CodeCName as OTTypeID,OD.OTReasonID,OD.OTReasonMemo,"); sb.Append(" OD.LastChgID,PL.NameN AS LastChgNameN,CONVERT(varchar(12),OD.LastChgDate,111)+' '+CONVERT(varchar(8),OD.LastChgDate,108) as LastChgDate,ISNULL(AI.FileName,'') AS FileName,OD.OTAttachment,'D' as Input"); sb.Append(" FROM OverTimeDeclaration OD"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Company] C ON C.CompID=OD.OTCompID AND C.InValidFlag = '0' And C.NotShowFlag = '0'"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Personal] P ON P.CompID = OD.OTCompID AND P.EmpID=OD.OTEmpID"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Personal] PR ON PR.CompID = OD.OTRegisterComp AND PR.EmpID=OD.OTRegisterID"); sb.Append(" LEFT JOIN " + Util.getAppSetting("app://eHRMSDB_OverTime/") + ".[dbo].[Personal] PL ON PL.CompID = OD.LastChgComp AND PL.EmpID=OD.LastChgID"); //sb.Append(" LEFT JOIN AttachInfo AI ON AI.AttachID = OD.OTAttachment"); sb.Append(" LEFT JOIN AttachInfo AI ON AI.AttachID IS NOT NULL AND AI.AttachID <> '' AND AI.AttachID = OD.OTAttachment AND FileSize > 0");//20170209 leo modify -參考Judy的After sb.Append(" LEFT JOIN OverTimeDeclaration OD1 on OD1.OTCompID='" + _CurrOTCompID + "' AND OD1.OTEmpID='" + _CurrOTEmpID + "' AND OD1.OTSeqNo='" + 2 + "' AND OD1.FlowCaseID='" + _CurrFlowCaseID + "'"); sb.Append(" LEFT JOIN AT_CodeMap ODT ON ODT.TabName='OverTime' and ODT.FldName='OverTimeType' and OD.OTTypeID = ODT.Code"); //sb.Append(" LEFT JOIN OverTimeType ODT ON OD.OTCompID = ODT.CompID and OD.OTTypeID = ODT.OTTypeId"); sb.Append(" WHERE OD.OTCompID='" + _CurrOTCompID + "'"); sb.Append(" AND OD.OTEmpID='" + _CurrOTEmpID + "'"); sb.Append(" AND OD.OTStartDate='" + _CurrOTStartDate + "'"); sb.Append(" AND OD.OTSeq='" + _CurrOTSeq + "'"); sb.Append(" AND OD.OTEndDate='" + _CurrOTStartDate + "'"); sb.Append(" AND OD.FlowCaseID='" + _CurrFlowCaseID + "'"); DataTable dt = db.ExecuteDataSet(sb.BuildCommand()).Tables[0]; if (dt.Rows.Count > 0) { if (dt.Rows[0]["Input"].ToString() == "D") { Label3.Text = "月已申報時數合計: 送簽"; } //lblCompID.Text = dt.Rows[0]["OTCompID"].ToString(); lblCompName.Text = dt.Rows[0]["CompName"].ToString(); //lblDeptID.Text = dt.Rows[0]["DeptID"].ToString(); lblDeptName.Text = dt.Rows[0]["DeptName"].ToString(); //lblOrganID.Text = dt.Rows[0]["OrganID"].ToString(); lblOrganName.Text = dt.Rows[0]["OrganName"].ToString(); lblEmpID.Text = _CurrOTEmpID; lblSalaryOrAdjust.Text = dt.Rows[0]["SalaryOrAdjust"].ToString() == "2" ? "轉補休" : "轉薪資"; lblAdjustInvalidDate.Visible = dt.Rows[0]["SalaryOrAdjust"].ToString() == "2"; txtAdjustInvalidDate.Visible = dt.Rows[0]["SalaryOrAdjust"].ToString() == "2"; txtAdjustInvalidDate.Text = dt.Rows[0]["AdjustInvalidDate"].ToString(); lblEmpName.Text = dt.Rows[0]["NameN"].ToString(); lblOTRegisterID.Text = dt.Rows[0]["OTRegisterID"].ToString(); lblOTRegisterName.Text = dt.Rows[0]["RegisterNameN"].ToString(); lblOTDateValue.Text = _CurrOTStartDate; lblOTDateValueEnd.Text = dt.Rows[0]["OTEndDate"].ToString(); lblBeginTime.Text = dt.Rows[0]["OTStartTime"].ToString().Substring(0, 2) + ":" + dt.Rows[0]["OTStartTime"].ToString().Substring(2, 2); //lblBeginTime.Text = dt.Rows[0]["OTStartTime"].ToString().Substring(0, 2) + ":" + dt.Rows[0]["OTStartTime"].ToString().Substring(2, 2); lblEndTime.Text = dt.Rows[0]["OTEndTime"].ToString().Substring(0, 2) + ":" + dt.Rows[0]["OTEndTime"].ToString().Substring(2, 2); //lblEndTime.Text = dt.Rows[0]["OTEndTime"].ToString().Substring(0, 2) + ":" + dt.Rows[0]["OTEndTime"].ToString().Substring(2, 2); chkMealFlag.Checked = (dt.Rows[0]["MealFlag"].ToString() == "1") ? true : false; lblMealTime.Text = dt.Rows[0]["MealTime"].ToString(); //lblOTTotalTime.Text = (Convert.ToDouble(dt.Rows[0]["OTTotalTime"].ToString()) - Convert.ToDouble(dt.Rows[0]["MealTime"].ToString())/60).ToString("0.0").ToString(); //20170213 leo modify TotalTime 依照起訖時間分鐘計算 Double TotalTime = _CurrOTStartDate.ToString() == dt.Rows[0]["OTEndDate"].ToString() ? (Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(0, 2)) * 60) + Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(2, 2)) - (Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(0, 2)) * 60) - Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(2, 2)) : 1440.0 - (Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(0, 2)) * 60) - Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(2, 2)) + (Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(0, 2)) * 60) + Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(2, 2)); TotalTime = TotalTime - (chkMealFlag.Checked ? Convert.ToDouble(dt.Rows[0]["MealTime"].ToString()) : 0); //Double TotalTime = 0.0; //if (_CurrOTStartDate.ToString() == dt.Rows[0]["OTEndDate"].ToString()) // TotalTime = (Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(0, 2)) * 60) + Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(2, 2)) - (Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(0, 2)) * 60) - Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(2, 2)); //else //TotalTime = 1440.0 - (Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(0, 2)) * 60) - Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(2, 2)) + (Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(0, 2)) * 60) + Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(2, 2)); lblOTTotalTime.Text = Math.Round(TotalTime / 60, 1, MidpointRounding.AwayFromZero).ToString("0.0"); lblOTTypeID.Text = dt.Rows[0]["OTTypeID"].ToString(); lblOTReasonMemo.Text = dt.Rows[0]["OTReasonMemo"].ToString(); lblLastChgID.Text = dt.Rows[0]["LastChgID"].ToString(); lblLastChgName.Text = dt.Rows[0]["LastChgNameN"].ToString(); lblLastChgDate.Text = dt.Rows[0]["LastChgDate"].ToString(); ViewState["Input"] = dt.Rows[0]["Input"].ToString() == "A" ? "OverTimeAdvance" : "OverTimeDeclaration"; //StartHr = Convert.ToDouble(dt.Rows[0]["StartHr"].ToString()); //EndHr = Convert.ToDouble(dt.Rows[0]["EndHr"].ToString()); if (dt.Rows[0]["FileName"].ToString() == "") { btnAttachDownload.Visible = false; } else { lblAttachName.Text = dt.Rows[0]["FileName"].ToString(); btnAttachDownload.ucAttachDB = _DBName; btnAttachDownload.ucAttachID = dt.Rows[0]["OTAttachment"].ToString(); } SignData(); if (_CurrOTStartDate == dt.Rows[0]["OTEndDate"].ToString()) { if (dt.Rows[0]["OTStartTime"].ToString().Substring(0, 2) == "00") { cntStart = Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(2, 2)); } else { cntStart = ((Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(0, 2))) * 60) + Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(2, 2)); } cntEnd = ((Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(0, 2))) * 60) + Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(2, 2)); cntTotal = cntEnd - cntStart; DataTable dt1 = at.QueryData("SUM(OTTotalTime) AS OTTotalTime", "OverTimeDeclaration", " AND OTStatus='3' AND OTStartDate='" + _CurrOTStartDate + "' AND OTEndDate='" + dt.Rows[0]["OTEndDate"].ToString() + "'"); double hr;//資料庫的加班總時數 if (dt1.Rows[0]["OTTotalTime"].ToString() == "") { hr = 0.0; } else { hr = Convert.ToDouble(dt.Rows[0]["OTTotalTime"].ToString()); } if (at.PeriodCount(ViewState["Input"].ToString(), _CurrOTEmpID, cntEnd - cntStart, 0, Convert.ToInt32(dt.Rows[0]["OTStartTime"]), Convert.ToInt32(dt.Rows[0]["OTEndTime"]), _CurrOTStartDate, 0, 0, "1900/01/01", Convert.ToDouble(dt.Rows[0]["MealTime"].ToString()), dt.Rows[0]["MealFlag"].ToString(), dt.Rows[0]["OTTxnID"].ToString(), out strdata)) { } else { Util.MsgBox("時段有問題"); } //strdata = at.OneDay(ViewState["Input"].ToString(), _CurrOTEmpID, cntTotal / 60, hr, _CurrOTStartDate, Convert.ToDouble(dt.Rows[0]["MealTime"].ToString()) / 60, dt.Rows[0]["MealFlag"].ToString(), dt.Rows[0]["OTStartTime"].ToString(), dt.Rows[0]["OTEndTime"].ToString(), _CurrOTSeq); //strdata = at.OneDay(cntTotal, hr, _CurrOTStartDate, Convert.ToDouble(dt.Rows[0]["MealTime"].ToString()), dt.Rows[0]["MealFlag"].ToString()); strdata = "0;0;" + strdata; trOne.Visible = true; lblDateOne.Text = (strdata.Split(';')[2]).Split(',')[0]; lblDateOne_0.Text = ((strdata.Split(';')[2]).Split(',')[1] == "0.0") ? "-" : (strdata.Split(';')[2]).Split(',')[1]; lblDateOne_1.Text = ((strdata.Split(';')[2]).Split(',')[2] == "0.0") ? "-" : (strdata.Split(';')[2]).Split(',')[2]; lblDateOne_2.Text = ((strdata.Split(';')[2]).Split(',')[3] == "0.0") ? "-" : (strdata.Split(';')[2]).Split(',')[3]; } else { cntStart = (23 - (Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(0, 2)))) * 60 + (60 - Convert.ToDouble(dt.Rows[0]["OTStartTime"].ToString().Substring(2, 2))); cntEnd = (Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(0, 2))) * 60 + Convert.ToDouble(dt.Rows[0]["OTEndTime"].ToString().Substring(2, 2)); DataTable dtStart = at.QueryData("SUM(OTTotalTime) AS OTTotalTime", "OverTimeDeclaration", " AND OTStatus='3' AND OTStartDate='" + _CurrOTStartDate + "' AND OTEndDate='" + _CurrOTStartDate + "'"); DataTable dtEnd = at.QueryData("SUM(OTTotalTime) AS OTTotalTime", "OverTimeDeclaration", " AND OTStatus='3' AND OTStartDate='" + dt.Rows[0]["OTEndDate"].ToString() + "' AND OTEndDate='" + dt.Rows[0]["OTEndDate"].ToString() + "'"); double hrStart;//資料庫的加班總時數 if (dtStart.Rows[0]["OTTotalTime"].ToString() == "") { hrStart = 0.0; } else { hrStart = Convert.ToDouble(dtStart.Rows[0]["OTTotalTime"].ToString()); } double hrEnd;//資料庫的加班總時數 if (dtEnd.Rows[0]["OTTotalTime"].ToString() == "") { hrEnd = 0.0; } else { hrEnd = Convert.ToDouble(dtEnd.Rows[0]["OTTotalTime"].ToString()); } if (at.PeriodCount(ViewState["Input"].ToString(), _CurrOTEmpID, cntStart, cntEnd, Convert.ToInt32(dt.Rows[0]["OTStartTime"]), 2359, _CurrOTStartDate, 0, Convert.ToInt32(dt.Rows[0]["OTEndTime"]), dt.Rows[0]["OTEndDate"].ToString(), Convert.ToDouble(dt.Rows[0]["MealTime"].ToString()), dt.Rows[0]["MealFlag"].ToString(), dt.Rows[0]["OTTxnID"].ToString(), out strdata)) { } else { Util.MsgBox("時段有問題"); } //strdata = at.TwoDay(ViewState["Input"].ToString(), _CurrOTEmpID, cntStart / 60, cntEnd / 60, hrStart / 60, hrEnd / 60, _CurrOTStartDate, dt.Rows[0]["OTEndDate"].ToString(), Convert.ToDouble(dt.Rows[0]["MealTime"].ToString()) / 60, dt.Rows[0]["MealFlag"].ToString(), dt.Rows[0]["OTTxnID"].ToString()); strdata = "0;0;" + strdata; trOne.Visible = true; trTwo.Visible = true; lblDateOne.Text = (strdata.Split(';')[2]).Split(',')[0]; lblDateOne_0.Text = ((strdata.Split(';')[2]).Split(',')[1] == "0.0") ? "-" : (strdata.Split(';')[2]).Split(',')[1]; lblDateOne_1.Text = ((strdata.Split(';')[2]).Split(',')[2] == "0.0") ? "-" : (strdata.Split(';')[2]).Split(',')[2]; lblDateOne_2.Text = ((strdata.Split(';')[2]).Split(',')[3] == "0.0") ? "-" : (strdata.Split(';')[2]).Split(',')[3]; lblDateTwo.Text = (strdata.Split(';')[3]).Split(',')[0]; lblDateTwo_0.Text = ((strdata.Split(';')[3]).Split(',')[1] == "0.0") ? "-" : (strdata.Split(';')[3]).Split(',')[1]; lblDateTwo_1.Text = ((strdata.Split(';')[3]).Split(',')[2] == "0.0") ? "-" : (strdata.Split(';')[3]).Split(',')[2]; lblDateTwo_2.Text = ((strdata.Split(';')[3]).Split(',')[3] == "0.0") ? "-" : (strdata.Split(';')[3]).Split(',')[3]; } } else { return; } string meal = (chkMealFlag.Checked == false) ? "0" : lblMealTime.Text; //lblOTTotalTime.Text = Convert.ToString(Convert.ToDouble(cntStart + cntEnd - Convert.ToDouble(meal)) / 60); txtTotalDescription.Text = (lblMealTime.Text != "" && Convert.ToDouble(lblMealTime.Text) > 0 && chkMealFlag.Checked == true) ? "(已扣除用餐時數" + lblMealTime.Text + "分鐘)" : ""; txtTotalDescription.Visible = true; }
public static string QueryColumn(string strColumn, string strTable, string strWhere) //查詢datatable { var at = new Aattendant(); return(at.QueryColumn(strColumn, strTable, strWhere)); }
public static DataTable getOrganHRBoss(string _CompID, string _EmpID, string _OTCompID, string _OTEmpID) //for Add 送簽 { var at = new Aattendant(); return(at.getOrganHRBoss(_CompID, _EmpID, _OTCompID, _OTEmpID)); }