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