440 lines
14 KiB
C#
440 lines
14 KiB
C#
//
|
|
// 這個檔案是 RPSB 資料表的資料存取類別 檔案一
|
|
// 請不要修改這個檔案，重新產生資料存取類別時，這個檔案會被覆蓋掉
|
|
//
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using Wellan.Data;
|
|
using System.Data.Common;
|
|
using Wellan.Common;
|
|
using Newtonsoft.Json;
|
|
|
|
///<summary>
|
|
/// RPSB 人員異動資料 資料存取類別
|
|
///</summary>
|
|
[DataObject]
|
|
public class RpsbDAC : Wellan.Data.WDAC
|
|
{
|
|
private const string _tableName = "[RPSB]";
|
|
private const string _allFields = "[RPENO], [RPITM], [RPDAT], [RPCLS], [RPODT], [RPDPT], [RPDOC], [DB_CRDAT], [DB_CRUSR], [DB_TRDAT], [DB_TRUSR]";
|
|
//private string _select = "select " + _allFields;
|
|
private string _insert = "[RPENO], [RPITM], [RPDAT], [RPCLS], [RPODT], [RPDPT], [RPDOC], [DB_CRDAT], [DB_CRUSR], [DB_TRDAT], [DB_TRUSR]";
|
|
private string _insertValue = "@RPENO, @RPITM, @RPDAT, @RPCLS, @RPODT, @RPDPT, @RPDOC, @DB_CRDAT, @DB_CRUSR, @DB_TRDAT, @DB_TRUSR";
|
|
//private string _from = " from " + _tableName + " WITH (NOLOCK) ";
|
|
//private string _where = " where 1=1 ";
|
|
//private string _order = " order by [RPENO], [RPITM] ";
|
|
|
|
public RpsbDAC()
|
|
{
|
|
}
|
|
|
|
public RpsbDAC(string connectionConfig, string connectionConfigName) : this()
|
|
{
|
|
this._connectionConfig = connectionConfig;
|
|
this._connectionConfigName = connectionConfigName;
|
|
}
|
|
|
|
public RpsbDAC(WQuery query) : this()
|
|
{
|
|
this._query = query;
|
|
}
|
|
|
|
///<summary>
|
|
/// 查詢
|
|
///</summary>
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public RPSBList Select(string RPENO)
|
|
{
|
|
RPSBList result = new RPSBList();
|
|
IDataList list = result as IDataList;
|
|
DbCommand cmdSelect = Query.NewCommand();
|
|
cmdSelect.CommandText = @"
|
|
SELECT A.*, B.BDNAM AS RPODT_NAME, C.BDNAM AS RPDPT_NAME
|
|
FROM RPSB A
|
|
LEFT JOIN BDPT B ON A.RPODT = B.BDNUM
|
|
LEFT JOIN BDPT C ON A.RPDPT = C.BDNUM
|
|
WHERE A.RPENO = @RPENO
|
|
ORDER BY A.RPITM";
|
|
AddParam(cmdSelect, "RPENO", RPENO);
|
|
Query.Select(cmdSelect, ref list);
|
|
return result;
|
|
}
|
|
|
|
///<summary>
|
|
/// 查詢
|
|
///</summary>
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public RPSBList SelectOne(string RPENO, int RPITM)
|
|
{
|
|
RPSBList result = new RPSBList();
|
|
IDataList list = result as IDataList;
|
|
DbCommand cmdSelect = Query.NewCommand();
|
|
cmdSelect.CommandText = @"
|
|
SELECT A.*, B.BDNAM AS RPODT_NAME, C.BDNAM AS RPDPT_NAME
|
|
FROM RPSB A
|
|
LEFT JOIN BDPT B ON A.RPODT = B.BDNUM
|
|
LEFT JOIN BDPT C ON A.RPDPT = C.BDNUM
|
|
WHERE A.RPENO = @RPENO
|
|
AND A.RPITM = @RPITM";
|
|
AddParam(cmdSelect, "RPENO", RPENO);
|
|
AddParam(cmdSelect, "RPITM", RPITM);
|
|
Query.Select(cmdSelect, ref list);
|
|
return result;
|
|
}
|
|
|
|
public RPSBItem SelectLast(string RPENO, int prevRBITM)
|
|
{
|
|
RPSBList result = new RPSBList();
|
|
IDataList list = result as IDataList;
|
|
DbCommand cmdSelect = Query.NewCommand();
|
|
cmdSelect.CommandText = @"
|
|
SELECT TOP 1 A.*, B.BDNAM AS RPODT_NAME, C.BDNAM AS RPDPT_NAME
|
|
FROM RPSB A
|
|
LEFT JOIN BDPT B ON A.RPODT = B.BDNUM
|
|
LEFT JOIN BDPT C ON A.RPDPT = C.BDNUM
|
|
WHERE A.RPENO = @RPENO
|
|
AND A.RPITM < @RPITM
|
|
ORDER BY RPITM DESC";
|
|
AddParam(cmdSelect, "RPENO", RPENO);
|
|
AddParam(cmdSelect, "RPITM", prevRBITM == 0 ? Int32.MaxValue : prevRBITM);
|
|
Query.Select(cmdSelect, ref list);
|
|
if (result.Count == 0)
|
|
return null;
|
|
else
|
|
return result[0];
|
|
}
|
|
|
|
public string PrevRPDPT(string RPENO, int RPITM)
|
|
{
|
|
DbCommand cmdSelect = Query.NewCommand();
|
|
cmdSelect.CommandText = @"
|
|
SELECT TOP 1 RPDPT
|
|
FROM RPSB
|
|
WHERE RPENO = @RPENO
|
|
AND RPITM < @RPITM
|
|
AND RPCLS = '部門調動'
|
|
ORDER BY RPITM DESC";
|
|
AddParam(cmdSelect, "RPENO", RPENO);
|
|
AddParam(cmdSelect, "RPITM", RPITM);
|
|
return WDAC.GetStringValue(Query.ExecuteScalar(cmdSelect));
|
|
}
|
|
|
|
public int NextRPITM(string RPENO)
|
|
{
|
|
DbCommand cmdSelect = Query.NewCommand();
|
|
cmdSelect.CommandText = @"
|
|
SELECT ISNULL(MAX(RPITM), 0) + 1
|
|
FROM RPSB
|
|
WHERE RPENO = @RPENO";
|
|
AddParam(cmdSelect, "RPENO", RPENO);
|
|
return WDAC.GetInt32Value(Query.ExecuteScalar(cmdSelect));
|
|
}
|
|
|
|
private bool BeforeInsertOne(RPSBItem value)
|
|
{
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// 新增一筆資料
|
|
///</summary>
|
|
[DataObjectMethod(DataObjectMethodType.Insert)]
|
|
public bool InsertOne(RPSBItem value)
|
|
{
|
|
try
|
|
{
|
|
if (!BeforeInsertOne(value))
|
|
return false;
|
|
value.DB_CRDAT = DateTime.UtcNow.AddHours(8);
|
|
value.DB_CRUSR = UserId;
|
|
value.DB_TRDAT = DateTime.UtcNow.AddHours(8);
|
|
value.DB_TRUSR = UserId;
|
|
DbCommand cmdInsert = Query.NewCommand();
|
|
cmdInsert.CommandText =
|
|
"insert into [RPSB]"
|
|
+ " (" + _insert + ") "
|
|
+ " values "
|
|
+ " (" + _insertValue + ") "
|
|
;
|
|
AddParam(cmdInsert, "RPENO", value.RPENO);
|
|
AddParam(cmdInsert, "RPITM", value.RPITM);
|
|
AddParam(cmdInsert, "RPDAT", value.RPDAT);
|
|
AddParam(cmdInsert, "RPCLS", value.RPCLS);
|
|
AddParam(cmdInsert, "RPODT", value.RPODT);
|
|
AddParam(cmdInsert, "RPDPT", value.RPDPT);
|
|
AddParam(cmdInsert, "RPDOC", value.RPDOC);
|
|
AddParam(cmdInsert, "DB_CRDAT", value.DB_CRDAT);
|
|
AddParam(cmdInsert, "DB_CRUSR", value.DB_CRUSR);
|
|
AddParam(cmdInsert, "DB_TRDAT", value.DB_TRDAT);
|
|
AddParam(cmdInsert, "DB_TRUSR", value.DB_TRUSR);
|
|
Query.ExecuteNonQuery(cmdInsert);
|
|
return true;
|
|
}
|
|
catch
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
|
|
private bool BeforeUpdateOne(RPSBItem value)
|
|
{
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// 修改一筆資料
|
|
///</summary>
|
|
[DataObjectMethod(DataObjectMethodType.Update)]
|
|
public bool UpdateOne(RPSBItem value)
|
|
{
|
|
try
|
|
{
|
|
if (!BeforeUpdateOne(value))
|
|
return false;
|
|
value.DB_TRDAT = DateTime.UtcNow.AddHours(8);
|
|
value.DB_TRUSR = UserId;
|
|
DbCommand cmdUpdate = Query.NewCommand();
|
|
cmdUpdate.CommandText = @"
|
|
update [RPSB]
|
|
set
|
|
[RPDAT] = @RPDAT ,
|
|
[RPCLS] = @RPCLS ,
|
|
[RPODT] = @RPODT ,
|
|
[RPDPT] = @RPDPT ,
|
|
[RPDOC] = @RPDOC ,
|
|
DB_TRDAT = @DB_TRDAT ,
|
|
DB_TRUSR = @DB_TRUSR
|
|
where 1=1
|
|
and [RPENO] = @original_RPENO
|
|
and [RPITM] = @original_RPITM
|
|
";
|
|
AddParam(cmdUpdate, "RPDAT", value.RPDAT);
|
|
AddParam(cmdUpdate, "RPCLS", value.RPCLS);
|
|
AddParam(cmdUpdate, "RPODT", value.RPODT);
|
|
AddParam(cmdUpdate, "RPDPT", value.RPDPT);
|
|
AddParam(cmdUpdate, "RPDOC", value.RPDOC);
|
|
AddParam(cmdUpdate, "DB_TRDAT", value.DB_TRDAT);
|
|
AddParam(cmdUpdate, "DB_TRUSR", value.DB_TRUSR);
|
|
AddParam(cmdUpdate, "original_RPENO", value.RPENO);
|
|
AddParam(cmdUpdate, "original_RPITM", value.RPITM);
|
|
Query.ExecuteNonQuery(cmdUpdate);
|
|
return true;
|
|
}
|
|
catch
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
|
|
private bool BeforeDeleteOne(string RPENO, int RPITM)
|
|
{
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// 刪除一筆資料
|
|
///</summary>
|
|
[DataObjectMethod(DataObjectMethodType.Delete)]
|
|
public bool DeleteOne(string RPENO, int RPITM)
|
|
{
|
|
try
|
|
{
|
|
if (!BeforeDeleteOne(RPENO, RPITM))
|
|
return false;
|
|
DbCommand cmdDelete = Query.NewCommand();
|
|
cmdDelete.CommandText = @"
|
|
delete from [RPSB]
|
|
where 1=1
|
|
and RPENO = @RPENO
|
|
and RPITM = @RPITM
|
|
";
|
|
AddParam(cmdDelete, "RPENO", RPENO);
|
|
AddParam(cmdDelete, "RPITM", RPITM);
|
|
Query.ExecuteNonQuery(cmdDelete);
|
|
return true;
|
|
}
|
|
catch
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
///<summary>
|
|
/// RPSB 資料集合類別
|
|
///</summary>
|
|
[Serializable]
|
|
public partial class RPSBList : List<RPSBItem>, IDataList
|
|
{
|
|
///<summary>
|
|
/// 從 DataReader 填入資料
|
|
///</summary>
|
|
public void Fill(DbDataReader reader)
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
RPSBItem newItem = new RPSBItem
|
|
{
|
|
RPENO = WDAC.GetStringValue(reader["RPENO"]),
|
|
RPITM = WDAC.GetInt32Value(reader["RPITM"]),
|
|
RPCLS = WDAC.GetStringValue(reader["RPCLS"]),
|
|
RPDAT = WDAC.GetDateTimeValue(reader["RPDAT"]),
|
|
RPDPT = WDAC.GetStringValue(reader["RPDPT"]),
|
|
RPODT = WDAC.GetStringValue(reader["RPODT"]),
|
|
RPDOC = WDAC.GetStringValue(reader["RPDOC"]),
|
|
RPDPT_NAME = WDAC.GetStringValue(reader["RPDPT_NAME"]),
|
|
RPODT_NAME = WDAC.GetStringValue(reader["RPODT_NAME"]),
|
|
DB_CRDAT = WDAC.GetDateTimeValue(reader["DB_CRDAT"]),
|
|
DB_CRUSR = WDAC.GetStringValue(reader["DB_CRUSR"]),
|
|
DB_TRDAT = WDAC.GetDateTimeValue(reader["DB_TRDAT"]),
|
|
DB_TRUSR = WDAC.GetStringValue(reader["DB_TRUSR"]),
|
|
};
|
|
Add(newItem);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
///<summary>
|
|
/// RPSB 資料紀錄類別
|
|
///</summary>
|
|
[Serializable]
|
|
[Description("人員異動資料")]
|
|
public class RPSBItem
|
|
{
|
|
public static string 到職 = "到職";
|
|
public static string 離職回任 = "離職回任";
|
|
public static string 離職 = "離職";
|
|
public static string 留職停薪 = "留職停薪";
|
|
public static string 留停復職 = "留停復職";
|
|
public static string 部門調動 = "部門調動";
|
|
|
|
///<summary>
|
|
/// 人員編號
|
|
///</summary>
|
|
public string RPENO { get; set; }
|
|
|
|
///<summary>
|
|
/// 異動項次
|
|
///</summary>
|
|
public int RPITM { get; set; }
|
|
|
|
///<summary>
|
|
/// 異動日期
|
|
///</summary>
|
|
[JsonIgnore]
|
|
public DateTime? RPDAT { get; set; }
|
|
|
|
[JsonProperty(PropertyName = "RPDAT")]
|
|
public string RPDAT_STR { get { return string.Format("{0:yyyyMMdd}", RPDAT); } }
|
|
///<summary>
|
|
/// 異動類別
|
|
/// 到職/離職/留職停薪/留停復職/部門調動
|
|
///</summary>
|
|
public string RPCLS { get; set; }
|
|
|
|
///<summary>
|
|
/// 原部門
|
|
///</summary>
|
|
public string RPODT { get; set; }
|
|
public string RPODT_NAME { get; set; }
|
|
|
|
///<summary>
|
|
/// 新部門
|
|
///</summary>
|
|
public string RPDPT { get; set; }
|
|
public string RPDPT_NAME { get; set; }
|
|
|
|
///<summary>
|
|
/// 備註
|
|
///</summary>
|
|
public string RPDOC { get; set; }
|
|
|
|
public DateTime? DB_CRDAT { get; set; }
|
|
public string DB_CRUSR { get; set; }
|
|
public DateTime? DB_TRDAT { get; set; }
|
|
public string DB_TRUSR { get; set; }
|
|
public bool OnBoard { get { return Array.IndexOf(RPSBSaveItem.OnBoardRPCLS, RPCLS) >= 0; } }
|
|
public bool OffBoard { get { return Array.IndexOf(RPSBSaveItem.OffBoardRPCLS, RPCLS) >= 0; } }
|
|
public bool Quit { get { return Array.IndexOf(RPSBSaveItem.QuitRPCLS, RPCLS) >= 0; } }
|
|
public bool DeptChange { get { return RPCLS == RPSBItem.部門調動; } }
|
|
}
|
|
|
|
|
|
public class RPSBSaveItem
|
|
{
|
|
public static string[] RPCLSOptions = { RPSBItem.到職, RPSBItem.離職回任, RPSBItem.離職, RPSBItem.留職停薪, RPSBItem.留停復職 };
|
|
public static string[] OnBoardRPCLS = { RPSBItem.到職, RPSBItem.離職回任, RPSBItem.留停復職 };
|
|
public static string[] OffBoardRPCLS = { RPSBItem.離職, RPSBItem.留職停薪 };
|
|
public static string[] QuitRPCLS = { RPSBItem.離職 };
|
|
|
|
private DateTime _RPDAT_DATE;
|
|
public string RPENO { get; set; }
|
|
public int RPITM { get; set; }
|
|
public string RPDAT { get; set; }
|
|
public string RPCLS { get; set; }
|
|
public string RPDPT { get; set; }
|
|
public string RPDOC { get; set; }
|
|
public DateTime RPDAT_DATE { get { return _RPDAT_DATE; } }
|
|
public string Message;
|
|
|
|
public bool IsValid
|
|
{
|
|
get
|
|
{
|
|
var valid = true;
|
|
var messages = new List<string>();
|
|
|
|
if (string.IsNullOrEmpty(RPCLS))
|
|
{
|
|
messages.Add("異動類別未輸入");
|
|
valid = false;
|
|
}
|
|
|
|
else if (Array.IndexOf(RPCLSOptions, RPCLS) < 0)
|
|
{
|
|
messages.Add("異動類別不正確");
|
|
valid = false;
|
|
}
|
|
|
|
if (string.IsNullOrEmpty(RPENO))
|
|
{
|
|
messages.Add("人員編號未輸入");
|
|
valid = false;
|
|
}
|
|
|
|
if (string.IsNullOrEmpty(RPDAT))
|
|
{
|
|
messages.Add("異動日期未輸入");
|
|
valid = false;
|
|
}
|
|
else if (!WGuard1.CheckDate(RPDAT, out _RPDAT_DATE) || _RPDAT_DATE.Year > 2112)
|
|
{
|
|
messages.Add("異動日期不正確");
|
|
valid = false;
|
|
}
|
|
|
|
//if (RPCLS == RPSBItem.部門調動 && string.IsNullOrEmpty(RPDPT))
|
|
//{
|
|
// messages.Add("新部門未輸入");
|
|
// valid = false;
|
|
//}
|
|
|
|
Message = string.Join(" / ", messages.ToArray());
|
|
return valid;
|
|
}
|
|
}
|
|
|
|
public bool OnBoard { get { return Array.IndexOf(OnBoardRPCLS, RPCLS) >= 0; } }
|
|
public bool OffBoard { get { return Array.IndexOf(OffBoardRPCLS, RPCLS) >= 0; } }
|
|
public bool Quit { get { return Array.IndexOf(QuitRPCLS, RPCLS) >= 0; } }
|
|
public bool DeptChange { get { return RPCLS == RPSBItem.部門調動; } }
|
|
|
|
}
|