sryang
9adbcfe0a4
- BpsnDAC 新增 SelectPage/SelectCount/SelectBPENOIndex - SelectPage 採 KEYSET：先在窄集合 BPSNView 算 ROWID，再 join 該頁明細 - SelectCount/SelectBPENOIndex 改用窄集合（免 join） - fmBpsn.ascx dsBPSNGrid 改 ObjectDataSource（EnablePaging） - fmBpsn.ascx.cs 調整 Search/Selected/OnInit - 修復 BpsnDAC.cs 亂碼註解並改存 UTF-8 BOM - 新增 Doc/GridView改用ObjectDataSource計畫.md（盤點、計畫、效能比較）
527 lines
16 KiB
C#
527 lines
16 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Configuration;
|
|
using System.Text;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using Wellan.Data;
|
|
|
|
/// <summary>
|
|
/// 員工資料的資料處理類別
|
|
/// </summary>
|
|
[DataObject(true)]
|
|
public class BpsnDAC : Wellan.Data.BPSN_DAC
|
|
{
|
|
private DbCommand cmdSelect;
|
|
private DbCommand cmdInsert;
|
|
private DbCommand cmdUpdate;
|
|
private DbCommand cmdDelete;
|
|
private StringBuilder sb = new StringBuilder();
|
|
private string select = "SELECT * ";
|
|
private string from = " FROM BPSNView ";
|
|
private string where = " WHERE 1=1 ";
|
|
private string order = " ORDER BY BPENO ";
|
|
|
|
public BpsnDAC()
|
|
{
|
|
}
|
|
|
|
public BpsnDAC(string connectionConfig, string connectionConfigName)
|
|
: this()
|
|
{
|
|
this._connectionConfig = connectionConfig;
|
|
this._connectionConfigName = connectionConfigName;
|
|
}
|
|
|
|
public BpsnDAC(WQuery query)
|
|
{
|
|
this._query = query;
|
|
}
|
|
|
|
private void Init()
|
|
{
|
|
EnsureQuery();
|
|
EnsureDbObjects();
|
|
}
|
|
|
|
private void EnsureDbObjects()
|
|
{
|
|
cmdSelect = Query.NewCommand();
|
|
cmdInsert = Query.NewCommand();
|
|
cmdUpdate = Query.NewCommand();
|
|
cmdDelete = Query.NewCommand();
|
|
}
|
|
|
|
public DataTable SelectAll()
|
|
{
|
|
Init();
|
|
cmdSelect.CommandText = select + from + order;
|
|
cmdSelect.Parameters.Clear();
|
|
return Query.Select(cmdSelect);
|
|
}
|
|
|
|
public void ClearWhere()
|
|
{
|
|
Init();
|
|
cmdSelect.Parameters.Clear();
|
|
sb.Length = 0;
|
|
sb.Append(select);
|
|
sb.Append(from);
|
|
sb.Append(where);
|
|
}
|
|
|
|
public void AddWhere(string fieldName, string value1, string value2, bool like)
|
|
{
|
|
Init();
|
|
|
|
if (value1 != null && value1.Length > 0 && value2 != null && value2.Length > 0)
|
|
{
|
|
sb.Append(String.Format(" AND {0} >= @{0}1 AND {0} <= @{0}2 ", fieldName));
|
|
cmdSelect.Parameters.Add(Query.NewParameter(fieldName + "1", value1));
|
|
cmdSelect.Parameters.Add(Query.NewParameter(fieldName + "2", value2));
|
|
}
|
|
else if (value1 != null && value1.Length > 0)
|
|
{
|
|
if (like)
|
|
{
|
|
sb.Append(String.Format(" AND {0} LIKE @{0} ", fieldName));
|
|
cmdSelect.Parameters.Add(Query.NewParameter(fieldName, value1.ToString() + "%"));
|
|
}
|
|
else
|
|
{
|
|
sb.Append(String.Format(" AND {0} = @{0} ", fieldName));
|
|
cmdSelect.Parameters.Add(Query.NewParameter(fieldName, value1));
|
|
}
|
|
}
|
|
else if (value2 != null && value2.Length > 0)
|
|
{
|
|
if (like)
|
|
{
|
|
sb.Append(String.Format(" AND {0} LIKE @{0} ", fieldName));
|
|
cmdSelect.Parameters.Add(Query.NewParameter(fieldName, value2 + "%"));
|
|
}
|
|
else
|
|
{
|
|
sb.Append(String.Format(" AND {0} = @{0} ", fieldName));
|
|
cmdSelect.Parameters.Add(Query.NewParameter(fieldName, value2));
|
|
}
|
|
}
|
|
}
|
|
|
|
public DataTable SelectByWhere()
|
|
{
|
|
Init();
|
|
sb.Append(order);
|
|
return Query.Select(cmdSelect);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得人員目前派駐專案
|
|
/// </summary>
|
|
/// <param name="bpeno"></param>
|
|
/// <returns></returns>
|
|
public string GetCurrentProject(string bpeno)
|
|
{
|
|
return GetProjectByDate(bpeno, DateTime.UtcNow.AddHours(8).Date);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得人員目前派駐專案
|
|
/// </summary>
|
|
/// <param name="bpeno"></param>
|
|
/// <returns></returns>
|
|
public string GetProjectByDate(string bpeno, DateTime date)
|
|
{
|
|
DbCommand cmd = Query.NewCommand(@"
|
|
SELECT TOP 1 RAPYN
|
|
FROM
|
|
(
|
|
SELECT 1 AS O, RAPYN
|
|
FROM RASN
|
|
WHERE RANUM = @BPENO
|
|
AND RASDT <= @DATE AND (RAEDT >= @DATE OR RAEDT IS NULL)
|
|
UNION
|
|
SELECT TOP 1 2 AS O, RAPYN
|
|
FROM RASN
|
|
WHERE RANUM = @BPENO
|
|
AND DATEPART(YEAR, RASDT) = DATEPART(YEAR, @DATE)
|
|
AND DATEPART(MONTH, RASDT) = DATEPART(MONTH, @DATE)
|
|
) AS TMP1
|
|
WHERE RAPYN <> '' AND RAPYN IS NOT NULL
|
|
ORDER BY O");
|
|
AddParam(cmd, "BPENO", bpeno);
|
|
AddParam(cmd, "DATE", date);
|
|
return GetStringValue(Query.ExecuteScalar(cmd));
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得人員目前派駐地點
|
|
/// </summary>
|
|
/// <param name="bpeno"></param>
|
|
/// <returns></returns>
|
|
public string GetCurrentSite(string bpeno)
|
|
{
|
|
return GetSiteByDate(bpeno, DateTime.UtcNow.AddHours(8).Date);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得人員目前派駐地點
|
|
/// </summary>
|
|
/// <param name="bpeno"></param>
|
|
/// <returns></returns>
|
|
public string GetSiteByDate(string bpeno, DateTime date)
|
|
{
|
|
DbCommand cmd = Query.NewCommand(@"
|
|
SELECT C.BSSCR
|
|
FROM BPSNView A
|
|
LEFT OUTER JOIN RASN B ON A.BPENO=B.RANUM AND (B.RASDT <= @DATE AND (B.RAEDT >= @DATE OR B.RAEDT IS NULL))
|
|
LEFT OUTER JOIN BCLS C ON B.RABRH=C.BSCLS AND C.BSPAR='BPSNBRH'
|
|
WHERE A.BPENO=@BPENO");
|
|
AddParam(cmd, "BPENO", bpeno);
|
|
AddParam(cmd, "DATE", date);
|
|
return GetStringValue(Query.ExecuteScalar(cmd));
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得人員電子郵件
|
|
/// </summary>
|
|
/// <param name="bpeno"></param>
|
|
/// <returns></returns>
|
|
public string GetEmail(string bpeno)
|
|
{
|
|
DbCommand cmd = Query.NewCommand(@"
|
|
SELECT BPEML
|
|
FROM BPSNView
|
|
WHERE BPENO=@BPENO");
|
|
AddParam(cmd, "BPENO", bpeno);
|
|
return GetStringValue(Query.ExecuteScalar(cmd));
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得人員電子郵件2
|
|
/// </summary>
|
|
/// <param name="bpeno"></param>
|
|
/// <returns></returns>
|
|
public string GetEmail2(string bpeno)
|
|
{
|
|
DbCommand cmd = Query.NewCommand(@"
|
|
SELECT BPEM2
|
|
FROM BPSNView
|
|
WHERE BPENO=@BPENO");
|
|
AddParam(cmd, "BPENO", bpeno);
|
|
return GetStringValue(Query.ExecuteScalar(cmd));
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得某日期人員所屬公司名稱
|
|
/// </summary>
|
|
/// <param name="bpeno">員工編號</param>
|
|
/// <param name="date">日期</param>
|
|
/// <returns></returns>
|
|
public List<string> GetCompanyByDate(string bpeno, DateTime date)
|
|
{
|
|
DbCommand cmd = Query.NewCommand(@"
|
|
SELECT TOP 1 RPCPY FROM
|
|
(
|
|
SELECT RPENO, RPCPY, RPSDT, RPEDT, @YYMM-RPSDT AS SDTDIFF FROM RPIR
|
|
WHERE RPENO=@BPENO AND
|
|
(RPSDT <= @YYMM AND RPEDT >= @YYMM
|
|
OR RPSDT <= @YYMM AND RPEDT IS NULL)
|
|
UNION
|
|
SELECT RPENO, RPCPY, RPSDT, RPEDT, @YYMM-RPSDT AS SDTDIFF FROM RPIB
|
|
WHERE RPENO=@BPENO AND
|
|
(RPSDT <= @YYMM AND RPEDT >= @YYMM
|
|
OR RPSDT <= @YYMM AND RPEDT IS NULL)
|
|
) AS TMP1
|
|
ORDER BY SDTDIFF");
|
|
AddParam(cmd, "BPENO", bpeno);
|
|
AddParam(cmd, "YYMM", date);
|
|
|
|
List<string> result = new List<string>();
|
|
result.Add(GetStringValue(Query.ExecuteScalar(cmd)));
|
|
result.Add(CompanyDAC.GetCompanyName(result[0]));
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得人員到職日期
|
|
/// </summary>
|
|
/// <param name="bpeno"></param>
|
|
/// <returns></returns>
|
|
public DateTime GetBPSDY(string bpeno)
|
|
{
|
|
DbCommand cmd = Query.NewCommand(@"
|
|
SELECT BPSDY
|
|
FROM BPSNView
|
|
WHERE BPENO=@BPENO");
|
|
AddParam(cmd, "BPENO", bpeno);
|
|
return GetDateTimeValue(Query.ExecuteScalar(cmd));
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得人員離職日期
|
|
/// </summary>
|
|
/// <param name="bpeno"></param>
|
|
/// <returns></returns>
|
|
public DateTime GetBPEDY(string bpeno)
|
|
{
|
|
DbCommand cmd = Query.NewCommand(@"
|
|
SELECT BPEDY
|
|
FROM BPSNView
|
|
WHERE BPENO=@BPENO");
|
|
AddParam(cmd, "BPENO", bpeno);
|
|
return GetDateTimeValue(Query.ExecuteScalar(cmd));
|
|
}
|
|
|
|
private static List<string> managerIds = new List<string>();
|
|
|
|
public static bool IsManager(string bpeno)
|
|
{
|
|
if (managerIds.Count == 0)
|
|
{
|
|
managerIds.Add("00001");
|
|
managerIds.Add("00002");
|
|
managerIds.Add("00003");
|
|
}
|
|
|
|
return managerIds.Contains(bpeno);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取出某日期在職的員工
|
|
/// </summary>
|
|
/// <param name="date">日期</param>
|
|
/// <returns></returns>
|
|
public DataTable GetOnBoardEmployee(DateTime date)
|
|
{
|
|
return Query.Select(Query.NewCommand("SELECT * FROM BPSNView WHERE dbo.EmployeeOnboard(BPENO, @DATE) = 1"));
|
|
}
|
|
private const string GridSelectColumns =
|
|
@" A2.*
|
|
, B.RAPYN, C.BPPYM RAPYNX, B.RABRH, D.BSSCR RABRHX, B.RAJNO, E.BPJME RAJNOX, B.RACNO
|
|
, F.BPCME RACNOX, ISNULL(H.RPCPY, G.RPCPY) AS RPCPY ";
|
|
|
|
// 只 join 該頁明細用 (明細主表為 A2)
|
|
private const string GridDetailJoins =
|
|
@" LEFT OUTER JOIN RASN B ON A2.BPENO=B.RANUM AND (B.RAEDT IS NULL OR (B.RASDT <= @TODAY AND B.RAEDT >= @TODAY))
|
|
LEFT OUTER JOIN BPAA C ON B.RAPYN=C.BPPYN
|
|
LEFT OUTER JOIN BCLS D ON D.BSCLS=B.RABRH AND D.BSPAR='BPSNBRH'
|
|
LEFT OUTER JOIN BPJA E ON E.BPPYN=B.RAPYN AND E.BPJNO=B.RAJNO
|
|
LEFT OUTER JOIN BPJB F ON F.BPPYN=B.RAPYN AND F.BPJNO=B.RAJNO AND F.BPCNO=B.RACNO
|
|
LEFT OUTER JOIN RPIR G ON A2.BPENO=G.RPENO AND G.RPREN=(SELECT MAX(RPREN) FROM RPIR WHERE RPENO=A2.BPENO)
|
|
LEFT OUTER JOIN RPIB H ON G.RPREN=H.RPREN AND H.RPITM=(SELECT MAX(RPITM) FROM RPIB WHERE RPREN=H.RPREN) ";
|
|
|
|
/// <summary>
|
|
/// 人員資料 Grid 分頁查詢
|
|
/// </summary>
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable SelectPage(
|
|
int startRowIndex,
|
|
int maximumRows,
|
|
string BPENO,
|
|
string BPNME,
|
|
string BPENM,
|
|
string BPIDN,
|
|
string BPEDY_TYPE,
|
|
string BPDPT_TYPE,
|
|
string BPSDY1,
|
|
string BPSDY2,
|
|
string BPEDY1,
|
|
string BPEDY2,
|
|
bool canSearch,
|
|
string userId)
|
|
{
|
|
DbCommand cmd = Query.NewCommand();
|
|
|
|
StringBuilder filter = new StringBuilder();
|
|
BuildGridFilter(filter, cmd, BPENO, BPNME, BPENM, BPIDN, BPEDY_TYPE, BPDPT_TYPE, BPSDY1, BPSDY2, BPEDY1, BPEDY2, canSearch, userId);
|
|
|
|
// 先在窄集合 (BPSNView) 依 BPENO 排序計算 ROWID，取出該頁的 BPENO 後，
|
|
// 才 join 明細表，避免深頁時對整個 join 結果計算 ROW_NUMBER()。
|
|
cmd.CommandText = String.Format(
|
|
@"SELECT {0}
|
|
FROM
|
|
(
|
|
SELECT A.BPENO, ROW_NUMBER() OVER (ORDER BY A.BPENO) AS ROWID
|
|
FROM BPSNView A WHERE 1=1 {1}
|
|
) R
|
|
JOIN BPSNView A2 ON A2.BPENO = R.BPENO
|
|
{2}
|
|
WHERE R.ROWID BETWEEN @STARTROWINDEX + 1 AND @STARTROWINDEX + @MAXIMUMROWS
|
|
ORDER BY R.ROWID",
|
|
GridSelectColumns, filter.ToString(), GridDetailJoins);
|
|
cmd.Parameters.Add(Query.NewParameter("STARTROWINDEX", startRowIndex));
|
|
cmd.Parameters.Add(Query.NewParameter("MAXIMUMROWS", maximumRows));
|
|
|
|
return Query.Select(cmd);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 人員資料 Grid 查詢筆數
|
|
/// </summary>
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public int SelectCount(
|
|
string BPENO,
|
|
string BPNME,
|
|
string BPENM,
|
|
string BPIDN,
|
|
string BPEDY_TYPE,
|
|
string BPDPT_TYPE,
|
|
string BPSDY1,
|
|
string BPSDY2,
|
|
string BPEDY1,
|
|
string BPEDY2,
|
|
bool canSearch,
|
|
string userId)
|
|
{
|
|
DbCommand cmd = Query.NewCommand();
|
|
|
|
StringBuilder filter = new StringBuilder();
|
|
BuildGridFilter(filter, cmd, BPENO, BPNME, BPENM, BPIDN, BPEDY_TYPE, BPDPT_TYPE, BPSDY1, BPSDY2, BPEDY1, BPEDY2, canSearch, userId);
|
|
|
|
cmd.CommandText = String.Format("SELECT COUNT(*) FROM BPSNView A WHERE 1=1 {0}", filter.ToString());
|
|
return WDAC.GetInt32Value(Query.ExecuteScalar(cmd));
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得指定人員編號在查詢結果中的序號 (0 起算)
|
|
/// </summary>
|
|
public int SelectBPENOIndex(
|
|
string targetBPENO,
|
|
string BPENO,
|
|
string BPNME,
|
|
string BPENM,
|
|
string BPIDN,
|
|
string BPEDY_TYPE,
|
|
string BPDPT_TYPE,
|
|
string BPSDY1,
|
|
string BPSDY2,
|
|
string BPEDY1,
|
|
string BPEDY2,
|
|
bool canSearch,
|
|
string userId)
|
|
{
|
|
DbCommand cmd = Query.NewCommand();
|
|
|
|
StringBuilder filter = new StringBuilder();
|
|
BuildGridFilter(filter, cmd, BPENO, BPNME, BPENM, BPIDN, BPEDY_TYPE, BPDPT_TYPE, BPSDY1, BPSDY2, BPEDY1, BPEDY2, canSearch, userId);
|
|
|
|
cmd.CommandText = String.Format(
|
|
@"SELECT ROWID - 1 FROM
|
|
(
|
|
SELECT A.BPENO, ROW_NUMBER() OVER (ORDER BY A.BPENO) AS ROWID
|
|
FROM BPSNView A WHERE 1=1 {0}
|
|
) AS TMP WHERE BPENO = @TARGET",
|
|
filter.ToString());
|
|
cmd.Parameters.Add(Query.NewParameter("TARGET", targetBPENO));
|
|
|
|
object o = Query.ExecuteScalar(cmd);
|
|
return (o is Int32 || o is Int64) ? WDAC.GetInt32Value(o) : -1;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 組出人員資料 Grid 的查詢條件 (與舊 AddSearch 行為一致)
|
|
/// </summary>
|
|
private void BuildGridFilter(
|
|
StringBuilder sb,
|
|
DbCommand cmd,
|
|
string BPENO,
|
|
string BPNME,
|
|
string BPENM,
|
|
string BPIDN,
|
|
string BPEDY_TYPE,
|
|
string BPDPT_TYPE,
|
|
string BPSDY1,
|
|
string BPSDY2,
|
|
string BPEDY1,
|
|
string BPEDY2,
|
|
bool canSearch,
|
|
string userId)
|
|
{
|
|
cmd.Parameters.Add(Query.NewParameter("TODAY", DateTime.UtcNow.AddHours(8).Date));
|
|
|
|
if (!canSearch)
|
|
{
|
|
if (!string.IsNullOrEmpty(userId))
|
|
{
|
|
sb.Append(" AND BPENO LIKE @BPENO1 ");
|
|
cmd.Parameters.Add(Query.NewParameter("BPENO1", userId + "%"));
|
|
}
|
|
return;
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(BPENO))
|
|
{
|
|
sb.Append(" AND BPENO LIKE @BPENO1 ");
|
|
cmd.Parameters.Add(Query.NewParameter("BPENO1", BPENO + "%"));
|
|
}
|
|
if (!string.IsNullOrEmpty(BPNME))
|
|
{
|
|
sb.Append(" AND BPNME LIKE @BPNME1 ");
|
|
cmd.Parameters.Add(Query.NewParameter("BPNME1", BPNME + "%"));
|
|
}
|
|
if (!string.IsNullOrEmpty(BPENM))
|
|
{
|
|
sb.Append(" AND BPENM LIKE @BPENM1 ");
|
|
cmd.Parameters.Add(Query.NewParameter("BPENM1", BPENM + "%"));
|
|
}
|
|
if (!string.IsNullOrEmpty(BPIDN))
|
|
{
|
|
sb.Append(" AND BPIDN LIKE @BPIDN1 ");
|
|
cmd.Parameters.Add(Query.NewParameter("BPIDN1", BPIDN + "%"));
|
|
}
|
|
|
|
switch (BPEDY_TYPE)
|
|
{
|
|
case "1":
|
|
sb.Append(" AND (BPEDY IS NULL OR BPEDY >= @TODAY) ");
|
|
break;
|
|
case "2":
|
|
sb.Append(" AND (BPEDY < @TODAY) ");
|
|
break;
|
|
}
|
|
|
|
switch (BPDPT_TYPE)
|
|
{
|
|
case "1":
|
|
sb.AppendFormat(" AND BPDPT IN {0} ", PublicVariable.InnerDepartment);
|
|
break;
|
|
case "2":
|
|
sb.AppendFormat(" AND BPDPT NOT IN {0} ", PublicVariable.InnerDepartment);
|
|
break;
|
|
}
|
|
|
|
AppendRange(sb, cmd, "BPSDY", BPSDY1, BPSDY2);
|
|
AppendRange(sb, cmd, "BPEDY", BPEDY1, BPEDY2);
|
|
|
|
if (!IsManager(userId))
|
|
{
|
|
sb.Append(" AND BPENO NOT IN ('00001','00002','00003') ");
|
|
}
|
|
}
|
|
|
|
private void AppendRange(StringBuilder sb, DbCommand cmd, string field, string value1, string value2)
|
|
{
|
|
bool has1 = !string.IsNullOrEmpty(value1);
|
|
bool has2 = !string.IsNullOrEmpty(value2);
|
|
|
|
if (has1 && has2)
|
|
{
|
|
sb.AppendFormat(" AND {0} >= @{0}1 AND {0} <= @{0}2 ", field);
|
|
cmd.Parameters.Add(Query.NewParameter(field + "1", value1));
|
|
cmd.Parameters.Add(Query.NewParameter(field + "2", value2));
|
|
}
|
|
else if (has1)
|
|
{
|
|
sb.AppendFormat(" AND {0} LIKE @{0}1 ", field);
|
|
cmd.Parameters.Add(Query.NewParameter(field + "1", value1 + "%"));
|
|
}
|
|
else if (has2)
|
|
{
|
|
sb.AppendFormat(" AND {0} LIKE @{0}2 ", field);
|
|
cmd.Parameters.Add(Query.NewParameter(field + "2", value2 + "%"));
|
|
}
|
|
}
|
|
}
|