private bool boolUpEmpRankID(string AD, string FlowCaseID, string UpEmpRankID) { DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); DataTable dt; string MapEmpRankID, OTEmpID; sb.Append(" SELECT Top 1 isnull(P.CompID,'') as CompID,isnull(P.RankID,'')as RankID FROM " + CustVerify.ADTable(AD) + " OT "); sb.Append(" LEFT JOIN " + _eHRMSDB + ".[dbo].[Personal] P ON OT.OTEmpID=P.EmpID and OT.OTCompID=P.CompID"); sb.Append(" WHERE OT.FlowCaseID='" + FlowCaseID + "' and OTSeqNo='1'"); dt = db.ExecuteDataSet(sb.BuildCommand()).Tables[0]; if (dt.Rows.Count == 0) { return(false); } MapEmpRankID = RankIDMapping(UserInfo.getUserInfo().CompID, UpEmpRankID); if (MapEmpRankID == "") { return(false); } OTEmpID = RankIDMapping(dt.Rows[0]["CompID"].ToString(), dt.Rows[0]["RankID"].ToString()); if (OTEmpID == "") { return(false); } return(int.Parse(MapEmpRankID) <= int.Parse(OTEmpID) ? true : false); }
/// <summary> /// 事先申請到事後申報的送簽單被駁回,須重寫一筆有OTFromAdvanceTxnId的暫存到事後申報,供使用者再送簽 /// </summary> /// <param name="FlowCaseID">事後的</param> /// <param name="strSQL">要組合並一起執行的SQL字串</param> public static void AfterReject_CheckAndInsert(string FlowCaseID, ref CommandHelper strSQL) { DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); //sb.Append(" SELECT DISTINCT OTFromAdvanceTxnId "); //sb.Append(" FROM OverTimeDeclaration"); //sb.Append(" WHERE FlowCaseID=").AppendParameter("FlowCaseID", FlowCaseID); //sb.Append(" and OTSeqNo='1' "); /*================*/ sb.Append(" SELECT isnull(A.FlowCaseID,'') as AFlowCaseID "); sb.Append(" FROM OverTimeDeclaration D "); sb.Append(" left join OverTimeAdvance A on D.OTFromAdvanceTxnId=A.OTTxnID and A.OTSeqNo='1' and A.OTTxnID!='' "); sb.Append(" WHERE D.FlowCaseID=").AppendParameter("FlowCaseID", FlowCaseID); sb.Append(" and D.OTSeqNo='1' "); DataTable dt = db.ExecuteDataSet(sb.BuildCommand()).Tables[0]; if (dt.Rows.Count > 0) { string AFlowCaseID = dt.Rows[0]["AFlowCaseID"].ToString(); if (!string.IsNullOrEmpty(AFlowCaseID)) { insertFromAdvanceToData(AFlowCaseID, ref strSQL); } } }
/// <summary> /// 新增附件 /// </summary> /// <param name="AttachID"></param> /// <param name="attach"></param> /// <returns></returns> public bool InsertAttach(string AttachID, string attach) { bool isSuccess = false; try { DbHelper db = new DbHelper(_overtimeDBName); CommandHelper strSQL = db.CreateCommandHelper(); DbConnection cn = db.OpenConnection(); DbTransaction tx = cn.BeginTransaction(); strSQL.Reset(); strSQL.AppendStatement(" UPDATE AttachInfo SET FileSize = -1, FileBody = null WHERE AttachID = '" + AttachID + "'"); strSQL.AppendStatement(" INSERT INTO AttachInfo (AttachID, SeqNo, FileName, FileExtName, FileSize, AnonymousAccess, UpdUser, UpdDate, UpdTime, FileBody, MD5Check) "); strSQL.Append(" SELECT '" + AttachID + "'"); strSQL.Append(" , SeqNo, FileName, FileExtName, FileSize, AnonymousAccess, UpdUser, UpdDate, UpdTime, FileBody, MD5Check "); strSQL.Append(" FROM AttachInfo "); strSQL.Append(" WHERE AttachID = '" + attach + "'"); using (DataTable dtSchedule = db.ExecuteDataSet(strSQL.BuildCommand()).Tables[0]) { db.ExecuteNonQuery(strSQL.BuildCommand(), tx); tx.Commit(); } } catch (Exception ex) { Debug.Print(">>>InsertAttach: " + ex.Message); LogHelper.WriteSysLog(ex); //將 Exception 丟給 Log 模組 } return(isSuccess); }
protected void btnMainDelete_Click(object sender, EventArgs e) { DbHelper db = new DbHelper(_DBName); try { Dictionary <string, string> dicKey = new Dictionary <string, string>(); dicKey.Clear(); dicKey.Add("RuleID", _RuleID); dicKey.Add("AreaID", _AreaID); AclExpress.IsAclTableLog("AclAdminRuleArea", dicKey, LogHelper.AppTableLogType.Delete); AclExpress.IsAclTableLog("AclAdminRuleAreaGrantList", dicKey, LogHelper.AppTableLogType.Delete); CommandHelper sb = db.CreateCommandHelper(); sb.Reset(); sb.AppendStatement("Delete AclAdminRuleAreaGrantList Where RuleID = ").AppendParameter("RuleID", _RuleID); sb.Append(" and AreaID = ").AppendParameter("AreaID", _AreaID); sb.AppendStatement("Delete AclAdminRuleArea Where RuleID = ").AppendParameter("RuleID", _RuleID); sb.Append(" and AreaID = ").AppendParameter("AreaID", _AreaID); db.ExecuteNonQuery(sb.BuildCommand()); Util.NotifyMsg("刪除成功", Util.NotifyKind.Success); _RuleID = ""; _AreaID = ""; _GrantID = ""; Refresh(true, true); } catch { throw; } }
/// <summary> /// 「更新」按鈕 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnUpdate_Click(object sender, EventArgs e) { DbHelper db = new DbHelper(_DBName); CommandHelper sb = db.CreateCommandHelper(); //取得編輯結果 Dictionary <string, string> oEditResult = Util.getControlEditResult(fmMain); //去除不必要欄位 oEditResult.Remove("UpdUser"); oEditResult.Remove("UpdDateTime"); //組合SQL sb.Reset(); sb.AppendStatement("Update ").Append(_TableName).Append(" set "); //處理非鍵值欄位 bool isComma = false; foreach (var pair in oEditResult) { if (!_PKList.Contains(pair.Key)) { switch (pair.Key) { default: if (isComma) { sb.Append(" , "); } else { isComma = true; } sb.Append(pair.Key).Append(" = ").AppendParameter(pair.Key, pair.Value); break; } } } //處理鍵值欄位 sb.Append(" Where 0 = 0 "); sb.Append(Util.getDataQueryKeySQL(_PKList, oEditResult)); //執行SQL if (db.ExecuteNonQuery(sb.BuildCommand()) >= 0) { Util.NotifyMsg(RS.Resources.Msg_EditSucceed, Util.NotifyKind.Success); //更新成功 } else { Util.NotifyMsg(RS.Resources.Msg_EditFail, Util.NotifyKind.Error); //更新失敗 } divMainFormView.Visible = false; divMainGridview.Visible = true; ucGridView1.Refresh(); }
public bool IsDataExists(String strTable, String strWhere) { DbHelper db = new DbHelper(_DBName); CommandHelper sb = db.CreateCommandHelper(); sb.Append("Select Count(*) Cnt From " + strTable); sb.Append(" Where 1 = 1 " + strWhere); return(int.Parse(db.ExecuteScalar(sb.BuildCommand()).ToString()) == 0 ? true : false); }
public static string getFlowLogID(string FlowCustDB, string FlowCaseID) { DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); sb.Append(" select FlowLogID from " + FlowCustDB + "FlowFullLog "); sb.Append(" WHERE FlowCaseID =").AppendParameter("FlowCaseID", FlowCaseID); sb.Append(" Order by FlowLogID desc "); return(db.ExecuteScalar(sb.BuildCommand()).ToString()); }
/// <summary> /// 取得收件人相關資料 /// </summary> /// <returns>回傳DataTable[EMail],[Name]</returns> private DataTable getMailInfo(string CompID, string EmpID) { DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); sb.Append(" select distinct isnull(C.EMail,'') as EMail,P.Name from " + _eHRMSDB + ".[dbo].Personal P "); sb.Append(" left join " + _eHRMSDB + ".[dbo].[Communication] C on C.IDNo=P.IDNo "); sb.Append(" where P.CompID='" + CompID + "' and P.EmpID='" + EmpID + "' "); return(db.ExecuteDataSet(sb.BuildCommand()).Tables[0]); }
/// <summary> /// UpdateOnBizReqAppd_ITRDFlowCaseSql /// 公出查詢 /// 核准_註銷 /// 更新OnBizReqAppd_ITRDFlowCase狀態 /// </summary> /// <param name="sb">傳入之前組好Command</param> /// <param name="isReset">StringBuilder Reset or not</param> public static void UpdateOnBizReqAppd_ITRDFlowCaseSql(OnBizPublicOutBean model, ref CommandHelper sb, bool isReset = false) { if (isReset) { sb.Reset(); } sb.Append(" UPDATE OnBizReqAppd_ITRDFlowCase SET "); sb.Append(" FlowCurrStepID = 'Z03' "); sb.Append(" WHERE FlowCaseID = ").AppendParameter("@FlowCaseID", model.FlowCaseID); sb.Append(" ; "); }
protected void btnVerify_Click(object sender, EventArgs e) { Button oBtn = (Button)sender; //檢查流程意見是否為必需輸入 if (string.IsNullOrEmpty(txtFlowOpinion.ucTextData)) { if (oBtn.CommandArgument.ToUpper() == "Y") { //必需輸入 Util.NotifyMsg(WorkRS.Resources.FlowVerifyMsg_OpinionRequired, Util.NotifyKind.Error); return; } else { //非必需輸入,填入預設意見 txtFlowOpinion.ucTextData = WorkRS.Resources.FlowDefOpinion; } } //將意見暫存,方便審核畫面引用 2017.04.28 FlowExpress oFlow = new FlowExpress(); DbHelper dblog = new DbHelper(oFlow.FlowLogDB); CommandHelper sb = dblog.CreateCommandHelper(); sb.Reset(); sb.AppendStatement(string.Format("Update {0}FlowOpenLog Set ", oFlow.FlowID)); sb.Append(" FlowStepOpinion = ").AppendParameter("FlowStepOpinion", txtFlowOpinion.ucTextData.Replace(",", "''")); sb.Append(" Where FlowLogID = ").AppendParameter("FlowLogID", oFlow.FlowLogID); if (dblog.ExecuteNonQuery(sb.BuildCommand()) <= 0) { Util.NotifyMsg(string.Format(RS.Resources.Msg_Error1, WorkRS.Resources.FlowVerifyTab_SaveFlowTempOpinion), Util.NotifyKind.Error); return; } Dictionary <string, string> dicPara = Util.getRequestQueryString(); if (dicPara.IsNullOrEmpty()) { //缺少流程參數 Util.NotifyMsg(WorkRS.Resources.FlowFullLogMsg_FlowParaError, Util.NotifyKind.Error); return; } else { ucModalPopup1.Reset(); ucModalPopup1.ucFrameURL = string.Format("{0}?FlowID={1}&FlowLogID={2}&FlowStepBtnID={3}", FlowExpress._FlowPageVerifyFrameURL, dicPara["FlowID"], dicPara["FlowLogID"], oBtn.CommandName); //FlowExpress._FlowPageVerifyFrameURL + "?Dummy=" + Util.getRandomCode(); ucModalPopup1.ucPopupWidth = 680; ucModalPopup1.ucPopupHeight = 580; ucModalPopup1.Show(); } }
//Judy在Aattendant撈取StartDate會有問題改成EndDate 去掉CompID public DataTable QueryData(string strColumn, string strTable, string strWhere) //查詢datatable { DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); sb.Append("SELECT " + strColumn + " FROM " + strTable); sb.Append(" WHERE 1=1 "); sb.Append(strWhere); DataTable dt = db.ExecuteDataSet(sb.BuildCommand()).Tables[0]; return(dt); }
protected void btnMainInsert_Click(object sender, EventArgs e) { //取得表單輸入資料 Dictionary <string, string> oDic = Util.getControlEditResult(fmMain); DbHelper db = new DbHelper(_DBName); CommandHelper sb = db.CreateCommandHelper(); sb.Reset(); sb.AppendStatement("Insert AclRule "); sb.Append("( "); sb.Append(" RuleID,RuleName,IsEnabled,Remark,UpdUser,UpdDateTime"); sb.Append(" ) Values ("); sb.Append(" ").AppendParameter("RuleID", oDic["RuleID"]); sb.Append(" ,").AppendParameter("RuleName", oDic["RuleName"]); sb.Append(" ,").AppendParameter("IsEnabled", oDic["IsEnabled"]); sb.Append(" ,").AppendParameter("Remark", oDic["Remark"]); sb.Append(" ,").AppendParameter("UpdUser", UserInfo.getUserInfo().UserID); sb.Append(" ,").AppendDbDateTime(); sb.Append(" )"); if (_IsADD && _RuleID != oDic["RuleID"]) { //Copy Detail sb.AppendStatement(Util.getDataCopySQL(AclExpress._AclDBName, _Main_KeyList, _RuleID.Split(','), oDic["RuleID"].Split(','), "AclRuleExp".Split(','))); } try { if (db.ExecuteNonQuery(sb.BuildCommand()) >= 0) { _IsADD = false; _RuleID = oDic["RuleID"]; Dictionary <string, string> dicKey = new Dictionary <string, string>(); dicKey.Clear(); dicKey.Add("RuleID", _RuleID); AclExpress.IsAclTableLog("AclRule", dicKey, LogHelper.AppTableLogType.Create); AclExpress.IsAclTableLog("AclRuleExp", dicKey, LogHelper.AppTableLogType.Create); Util.NotifyMsg(string.Format("[{0}]新增成功,可進行後續編修。", oDic["RuleID"]), Util.NotifyKind.Success); Refresh(true, true); } else { Util.NotifyMsg("新增失敗", Util.NotifyKind.Error); } } catch { Util.NotifyMsg("新增錯誤,請檢查資料是否重複", Util.NotifyKind.Error); } }
/// <summary> /// 更新AD的Table狀態(2:送審 3:核准 4:駁回) /// </summary> /// <param name="AD">A事先/D事後</param> /// <param name="OTStatus">狀態</param> /// <param name="FlowCaseID">FlowCaseID</param> /// <param name="sb">回傳SQL語法</param> public static void UpdateOverTime(string AD, string OTStatus, string FlowCaseID, ref CommandHelper sb) { AD = CustVerify.ADTable(AD); sb.Append(" UPDATE " + AD + " SET OTStatus='" + OTStatus + "',"); //審核人員 sb.Append(" OTValidDate='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',"); sb.Append(" OTValidID='" + UserInfo.getUserInfo().UserID + "' "); ////最後異動人員(Kat說不更新) //sb.Append(" LastChgDate='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',"); //sb.Append(" LastChgID='" + UserInfo.getUserInfo().UserID + "', "); //sb.Append(" LastChgComp='" + UserInfo.getUserInfo().CompID + "' "); sb.Append(" WHERE FlowCaseID=").AppendParameter("FlowCaseID", FlowCaseID); }
/// <summary> ///Judy切割出來多筆審核撈取FlowLogID /// </summary> public static string getFlowLogID(string FlowCaseID, string AD) { FlowExpress tbFlow = new FlowExpress(Aattendant._AattendantFlowID); AD = CustVerify.ADTable(AD); DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); sb.AppendStatement(" SELECT top 1 AL.FlowLogID,* FROM " + AD + " OT "); sb.Append(" LEFT JOIN " + tbFlow.FlowCustDB + "FlowFullLog AL ON OT.FlowCaseID=AL.FlowCaseID "); sb.Append(" WHERE OT.FlowCaseID =").AppendParameter("FlowCaseID", FlowCaseID); sb.Append(" Order by AL.FlowLogID desc "); return(db.ExecuteScalar(sb.BuildCommand()).ToString()); }
/// <summary> /// 確認新增明細 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnDetailInsert_Click(object sender, EventArgs e) { Dictionary <string, string> oDic = Util.getControlEditResult(fmDetail); if (!string.IsNullOrEmpty(_MainID)) { //取出目前 PODetail最後一筆的 POSeq 備用(若無資料會傳回 -1) int intLastPOSeq = Util.getLastKeySeqNo(_DBName, "PODetail", "POID,POSeq".Split(','), _MainID.Split(',')); DbHelper db = new DbHelper(_DBName); CommandHelper sb = db.CreateCommandHelper(); sb.Reset(); //新增明細 sb.AppendStatement("Insert PODetail "); sb.Append("(POID,POSeq,ItemDesc,ItemExpDate,Unit,UnitPrice,Qty,Subtotal) "); sb.Append(" Values (").AppendParameter("POID", _MainID); sb.Append(" ,").AppendParameter("POSeq", (intLastPOSeq >= 0) ? intLastPOSeq + 1 : 1); sb.Append(" ,").AppendParameter("ItemDesc", oDic["ItemDesc"]); sb.Append(" ,").AppendParameter("ItemExpDate", oDic["ItemExpDate"]); sb.Append(" ,").AppendParameter("Unit", oDic["Unit"]); sb.Append(" ,").AppendParameter("UnitPrice", oDic["UnitPrice"]); sb.Append(" ,").AppendParameter("Qty", oDic["Qty"]); sb.Append(" ,").AppendParameter("Subtotal", int.Parse(oDic["UnitPrice"]) * int.Parse(oDic["Qty"])); sb.Append(")"); //重新計算加總值 sb.AppendStatement(string.Format(_ReCal_BaseUpdSQL, _MainID)); DbConnection cn = db.OpenConnection(); DbTransaction tx = cn.BeginTransaction(); try { db.ExecuteNonQuery(sb.BuildCommand(), tx); tx.Commit(); _DetailSeq = (intLastPOSeq >= 0) ? intLastPOSeq + 1 : 1; Util.NotifyMsg("明細新增成功", Util.NotifyKind.Success); Refresh(false, true); } catch { tx.Rollback(); Util.NotifyMsg("明細新增失敗", Util.NotifyKind.Error); } finally { cn.Close(); cn.Dispose(); tx.Dispose(); } } }
public static void PunchAppdOperation_UpdateHROtherFlowLog(string FlowCaseID, string FlowStatus, ref CommandHelper sb, bool isReset = false) { string HROtherFlowLog_ = "HROtherFlowLog_"; if (isReset) { sb.Reset(); } sb.AppendStatement(" Update HROtherFlowLog set "); sb.Append(" FlowStatus=").AppendParameter(HROtherFlowLog_ + "FlowStatus", FlowStatus); sb.Append(" where FlowCaseID=").AppendParameter(HROtherFlowLog_ + "0_FlowCaseID", FlowCaseID); sb.Append(" and Seq= (select Top 1 Seq from HROverTimeLog where FlowCaseID=").AppendParameter(HROtherFlowLog_ + "1_FlowCaseID", FlowCaseID); sb.Append(" order by Seq desc) ; "); }
/// <summary> /// 明細整批刪除 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnDetailBatchDelete_Click(object sender, EventArgs e) { string[] CheckedKeyList = ucGridDetail1.ucCheckedKeyList; if (CheckedKeyList.Count() < 1) { Util.NotifyMsg(RS.Resources.Msg_NoDataToModify); } else { DbHelper db = new DbHelper(_DBName); CommandHelper sb = db.CreateCommandHelper(); string[] oKey; sb.Reset(); //批次明細刪除(SQL Parameter名稱需 Unique) for (int i = 0; i < CheckedKeyList.Count(); i++) { oKey = CheckedKeyList[i].Split(','); sb.AppendStatement("Delete PODetail Where 0=0 "); sb.Append(" And POID =").AppendParameter("POID" + i, oKey[0]); sb.Append(" And POSeq =").AppendParameter("POSeq" + i, oKey[1]); } //重新計算加總值 sb.AppendStatement(string.Format(_ReCal_BaseUpdSQL, _MainID)); DbConnection cn = db.OpenConnection(); DbTransaction tx = cn.BeginTransaction(); try { db.ExecuteNonQuery(sb.BuildCommand(), tx); tx.Commit(); _DetailSeq = -1; Util.NotifyMsg("[選取項目]刪除成功", Util.NotifyKind.Success); } catch { tx.Rollback(); Util.NotifyMsg("[選取項目]刪除失敗", Util.NotifyKind.Error); } finally { cn.Close(); cn.Dispose(); tx.Dispose(); } } fmMainRefresh(); Refresh(true, true); }
/// <summary> /// notSP:true 現在是改派人員,需要重複向上送時,撈取最近一筆非特殊人員(行政、功能或HR?)線資料 /// notSP:false 直接找最新一筆(現在)關卡資訊 /// </summary> /// <param name="FlowCaseID">FlowCaseID</param> /// <param name="notSP">現在關卡是否為指定人員</param> /// <returns>HROverTimeLog整個Table</returns> public static DataTable HROverTimeLog(string FlowCaseID, bool notSP) { DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); sb.Reset(); if (notSP) { sb.Append(" select Top 1 * from HROverTimeLog where FlowCaseID='" + FlowCaseID + "' and SignLine in ('1','2','4') order by Seq desc"); } else { sb.Append(" select Top 1 * from HROverTimeLog where FlowCaseID='" + FlowCaseID + "' order by Seq desc"); } return(db.ExecuteDataSet(sb.BuildCommand()).Tables[0]); }
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"] + "'); "); } } }
protected void fmMainRefresh() { DbHelper db = new DbHelper(_DBName); DataTable dt = null; if (!string.IsNullOrEmpty(_RuleID)) { CommandHelper sb = db.CreateCommandHelper(); sb.Reset(); sb.AppendStatement("Select * From AclAdminRuleArea Where RuleID = ").AppendParameter("RuleID", _RuleID); sb.Append(" and AreaID = ").AppendParameter("AreaID", _AreaID); dt = db.ExecuteDataSet(sb.BuildCommand()).Tables[0]; } if (_IsADD) { // Add,Copy fmMain.ChangeMode(FormViewMode.Insert); fmMain.DataSource = dt; fmMain.DataBind(); } else { //Edit fmMain.ChangeMode(FormViewMode.Edit); fmMain.DataSource = dt; fmMain.DataBind(); } Util.setJS_AlertDirtyData(fmMain); }
protected void btnMainInsert_Click(object sender, EventArgs e) { //取得表單輸入資料 Dictionary <string, string> oDic = Util.getControlEditResult(fmMain); DbHelper db = new DbHelper(_DBName); CommandHelper sb = db.CreateCommandHelper(); sb.Reset(); sb.AppendStatement("Insert AclAdminRuleArea "); sb.Append("( "); sb.Append(" RuleID,AreaID,AdminType,Remark,UpdUser,UpdDateTime"); sb.Append(" ) Values ("); sb.Append(" ").AppendParameter("RuleID", oDic["RuleID"]); sb.Append(" ,").AppendParameter("AreaID", oDic["AreaID"]); sb.Append(" ,").AppendParameter("AdminType", oDic["AdminType"]); sb.Append(" ,").AppendParameter("Remark", oDic["Remark"]); sb.Append(" ,").AppendParameter("UpdUser", UserInfo.getUserInfo().UserID); sb.Append(" ,").AppendDbDateTime(); sb.Append(" )"); try { if (db.ExecuteNonQuery(sb.BuildCommand()) >= 0) { _IsADD = false; _RuleID = oDic["RuleID"]; _AreaID = oDic["AreaID"]; Dictionary <string, string> dicKey = new Dictionary <string, string>(); dicKey.Clear(); dicKey.Add("RuleID", _RuleID); dicKey.Add("AreaID", _AreaID); AclExpress.IsAclTableLog("AclAdminRuleArea", dicKey, LogHelper.AppTableLogType.Create); Util.NotifyMsg(string.Format("[{0}-{1}]新增成功,可進行後續編修。", _RuleID, _AreaID), Util.NotifyKind.Success); Refresh(true, true); } else { Util.NotifyMsg("新增失敗", Util.NotifyKind.Error); } } catch { Util.NotifyMsg("新增錯誤,請檢查資料是否重複", Util.NotifyKind.Error); } }
protected void btnQry_Click(object sender, EventArgs e) { DbHelper db = new DbHelper(_DBName); CommandHelper sb = db.CreateCommandHelper(); sb.Reset(); sb.AppendStatement(_QryBaseSQL).Append(" Where 0=0 "); Dictionary <string, string> oDicResult = Util.getControlEditResult(DivQryCondition); //組合查詢條件 if (!string.IsNullOrEmpty(oDicResult["LogApp"])) { sb.Append(" And LogApp = ").AppendParameter("LogApp", oDicResult["LogApp"]); } if (!string.IsNullOrEmpty(oDicResult["LogType"])) { sb.Append(" And LogType = ").AppendParameter("LogType", oDicResult["LogType"]); } if (!string.IsNullOrEmpty(oDicResult["LogUser"])) { sb.Append(" And LogUser = "******"LogUser", oDicResult["LogUser"]); } if (!string.IsNullOrEmpty(oDicResult["LogDate1"])) { if (string.IsNullOrEmpty(oDicResult["LogDate2"])) { sb.Append(" And convert(varchar(10),[LogDateTime],111) = ").AppendParameter("LogDateTime1", oDicResult["LogDate1"]); } else { if (string.Compare(oDicResult["LogDate1"], oDicResult["LogDate2"]) > 0) { //若起始日期 大於 終止日期,則自動交換 string tmpDate = oDicResult["LogDate1"]; oDicResult["LogDate1"] = oDicResult["LogDate2"]; oDicResult["LogDate2"] = tmpDate; LogDate1.ucSelectedDate = oDicResult["LogDate1"]; LogDate2.ucSelectedDate = oDicResult["LogDate2"]; } sb.Append(" And ( "); sb.Append(" convert(varchar(10),[LogDateTime],111) Between ").AppendParameter("LogDateTime1", oDicResult["LogDate1"]); sb.Append(" And ").AppendParameter("LogDateTime2", oDicResult["LogDate2"]); sb.Append(" ) "); } } //將查詢條件的SQL物件,還原為純 SQL 字串,以便存到 _QryResultSQL 變數 _QryResultSQL = Util.getPureSQL(sb); //Response.Write(_QryResultSQL); //顯示查結果 ShowQryResult(); }
/// <summary> /// LogoffVisitFormSql /// 公出查詢 /// 核准_註銷 /// 把狀態更新為9 /// </summary> /// <param name="sb">傳入之前組好Command</param> /// <param name="isReset">StringBuilder Reset or not</param> public static void LogoffVisitFormSql(OnBizPublicOutBean model, ref CommandHelper sb, bool isReset = false) { if (isReset) { sb.Reset(); } sb.Append(" UPDATE VisitForm SET "); sb.Append(" OBFormStatus = '9' ,"); sb.Append(" LastChgComp = ").AppendParameter("@LastChgComp", model.LastChgComp).Append(" ,"); sb.Append(" LastChgID = ").AppendParameter("@LastChgID", model.LastChgID).Append(" ,"); sb.Append(" LastChgDate = getDate() "); sb.Append(" WHERE CompID = ").AppendParameter("@CompID", model.CompID); sb.Append(" AND EmpID = ").AppendParameter("@EmpID", model.EmpID); sb.Append(" AND WriteDate = ").AppendParameter("@WriteDate", model.WriteDate); sb.Append(" AND FormSeq = ").AppendParameter("@FormSeq", model.FormSeq); sb.Append(" ; "); }
/// <summary> /// 依照事先事後找尋最近一筆(行政或功能)線資料,依照AD判別 /// </summary> /// <param name="FlowCaseID">FlowCaseID</param> /// <param name="AD">A事先/D事後</param> /// <returns>全部欄位</returns> public static DataTable HROverTimeLogAD(string FlowCaseID, string AD) { DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); sb.Append(" select Top 1 * from HROverTimeLog where FlowCaseID='" + FlowCaseID + "' and OTMode='" + AD + "' order by Seq desc"); return(db.ExecuteDataSet(sb.BuildCommand()).Tables[0]); }
/// <summary> /// 公司班表 /// </summary> /// <param name="compID">公司別</param> /// <param name="empID">員工編號</param> /// <param name="errMsg">錯誤訊息</param> /// <param name="schedule">班表</param> /// <returns>bool</returns> /// <remarks>目前不曉得該怎取得過去日期的班表,日後可能需要再修改</remarks> private bool GetCompSchedule(string compID, string empID, out StringBuilder errMsg, out DataTable schedule) { errMsg = new StringBuilder(); schedule = new DataTable(); try { DbHelper db = new DbHelper(_eHRMSDB); CommandHelper sb = db.CreateCommandHelper(); sb.Reset(); sb.AppendStatement("SELECT"); sb.Append(" WT.BeginTime, WT.EndTime, WT.RestBeginTime, WT.RestEndTime "); sb.Append(" FROM PersonalOther PO "); sb.Append(" LEFT JOIN WorkTime WT ON WT.CompID = PO.CompID AND WT.WTID = PO.WTID "); sb.Append(" WHERE 0 = 0 "); sb.Append(" AND PO.CompID = '" + compID + "' "); sb.Append(" AND PO.EmpID = '" + empID + "' "); sb.Append(" ; "); using (DataTable dtSchedule = db.ExecuteDataSet(sb.BuildCommand()).Tables[0]) { schedule = dtSchedule; return(dtSchedule.Rows.Count > 0 ? true : false); } } catch (Exception ex) { Debug.Print(">>>GetCompSchedule: " + ex.Message); LogHelper.WriteSysLog(ex); //將 Exception 丟給 Log 模組 errMsg.AppendLine(ex.Message); schedule = null; return(false); } }
public static Dictionary <string, string> getEmpID_Name_Dictionary(string EmpID, string CompID) { DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); DataTable dt; sb.Append(" SELECT DISTINCT P.EmpID,O.OrganName+'-'+P.Name as Name "); sb.Append(" FROM " + _eHRMSDB + ".dbo. Personal P "); sb.Append(" left join " + _eHRMSDB + ".dbo. Organization O on O.CompID=P.CompID and O.OrganID=P.DeptID"); sb.Append(" WHERE P.EmpID in('").Append(EmpID).Append("')"); sb.Append(" and P.CompID in('").Append(CompID).Append("')"); //sb.Append(" UNION select 'Test' as EmpID , 'Test' as Name "); //測試用 dt = db.ExecuteDataSet(sb.BuildCommand()).Tables[0]; Dictionary <string, string> _dic = new Dictionary <string, string>(); _dic = Util.getDictionary(dt, 0, 1); return(_dic); }
void ucGridView1_GridViewCommand(object sender, Util_ucGridView.GridViewEventArgs e) { DbHelper db = new DbHelper(_DBName); CommandHelper sb = db.CreateCommandHelper(); DataTable dt = e.DataTable; if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { sb.AppendStatement("Update ").Append(_TableName).Append(" set UpdDateTime = ").AppendDbDateTime(); sb.Append(" , UpdUser = "******"UpdUser", UserInfo.getUserInfo().UserID); for (int j = 1; j < dt.Columns.Count; j++) { sb.Append(" ," + dt.Columns[j].ColumnName + " = ").AppendParameter(dt.Columns[j].ColumnName + i, dt.Rows[i][j].ToString()); } sb.Append(" Where 0=0 "); sb.Append(Util.getDataQueryKeySQL(_PKList, dt.Rows[i][0].ToString().Split(','))); } DbConnection cn = db.OpenConnection(); DbTransaction tx = cn.BeginTransaction(); try { db.ExecuteNonQuery(sb.BuildCommand(), tx); tx.Commit(); Util.NotifyMsg(RS.Resources.Msg_EditSucceed, Util.NotifyKind.Success); } catch { tx.Rollback(); Util.NotifyMsg(RS.Resources.Msg_EditFail, Util.NotifyKind.Error); } finally { cn.Close(); cn.Dispose(); tx.Dispose(); } } }
private String RankIDMapping(string CompID, string RankID) { if (CompID == "") { return(""); } if (RankID == "") { return(""); } DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); sb.Append("SELECT Top 1 [RankIDMap]"); sb.Append("FROM " + _eHRMSDB + ".[dbo].[RankMapping]"); sb.Append("where CompID='" + CompID + "' and RankID='" + RankID + "' ;"); DataTable dt = db.ExecuteDataSet(sb.BuildCommand()).Tables[0]; return(dt.Rows.Count > 0 ? dt.Rows[0]["RankIDMap"].ToString() : ""); }
protected void btnSaveTempOpinion_Click(object sender, EventArgs e) { gvFlowPrevStepLog.Refresh(); FlowExpress oFlow = new FlowExpress(); DbHelper dblog = new DbHelper(oFlow.FlowLogDB); CommandHelper sb = dblog.CreateCommandHelper(); sb.Reset(); sb.AppendStatement(string.Format("Update {0}FlowOpenLog Set ", oFlow.FlowID)); sb.Append(" FlowStepOpinion = ").AppendParameter("FlowStepOpinion", txtFlowOpinion.ucTextData.Replace(",", "''")); sb.Append(" Where FlowLogID = ").AppendParameter("FlowLogID", oFlow.FlowLogID); if (dblog.ExecuteNonQuery(sb.BuildCommand()) > 0) { Util.NotifyMsg(string.Format(RS.Resources.Msg_Succeed1, WorkRS.Resources.FlowVerifyTab_SaveFlowTempOpinion), Util.NotifyKind.Success); } else { Util.NotifyMsg(string.Format(RS.Resources.Msg_Error1, WorkRS.Resources.FlowVerifyTab_SaveFlowTempOpinion), Util.NotifyKind.Error); } }
/// <summary> /// 當收件人Mail是空的,通知預設的HR人員 /// </summary> /// <param name="Subject">主旨</param> /// <param name="Content">內容</param> /// <param name="CompID">收件人公司</param> /// <param name="EmpID">收件人員編</param> /// <returns>是否成功</returns> private bool MailisNull(string Subject, string Content, string CompID, string EmpID) { DbHelper db = new DbHelper(Aattendant._AattendantDBName); CommandHelper sb = db.CreateCommandHelper(); DataTable dt; string Title = ""; string Mail = ""; Subject = "FW:" + Subject; sb.Append("select P.CompID,P.EmpID,P.Name,C.EMail "); sb.Append(" from " + _HRDB + ".[dbo].SC_UserGroup UG "); sb.Append("left join " + _eHRMSDB + ".[dbo].Personal P on P.EmpID=UG.UserID and P.CompID=UG.CompID "); sb.Append("left join " + _eHRMSDB + ".[dbo].Communication C on C.IDNo=P.IDNo "); sb.Append("where UG.CompID='SPHBK1' and UG.GroupID='ADM-OT'"); dt = db.ExecuteDataSet(sb.BuildCommand()).Tables[0]; if (dt.Rows.Count == 0) { return(true); } sb.Reset(); DataRow[] dr = dt.Select(); Title = EmpID + "-" + getMailInfo(CompID, EmpID).Rows[0]["Name"].ToString(); Content = "||BM@QuitMailContent99||" + Content; foreach (DataRow row in dt.Rows) { Mail = row["EMail"].ToString(); InsertMailLogCommand("人力資源處", row["CompID"].ToString(), row["EmpID"].ToString(), Mail, Subject, Content, false, ref sb); } try { db.ExecuteNonQuery(sb.BuildCommand()); } catch (Exception ex) { Util.MsgBox("人力資源處E-Mail寄送失敗-" + ex); return(false); } return(true); }