531 lines
17 KiB
C#
531 lines
17 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Configuration;
|
|
using System.Data.Common;
|
|
using System.Collections;
|
|
using System.Text;
|
|
using System.ComponentModel;
|
|
using Wellan.Data;
|
|
|
|
/// <summary>
|
|
/// 薪資資料與薪資異動資料的資料處理類別
|
|
/// </summary>
|
|
[DataObject(true)]
|
|
public class BpslDAC : Wellan.Data.WDAC
|
|
{
|
|
private DbCommand cmdSelectBpslBpeno;
|
|
private DbCommand cmdSelectRpsl;
|
|
private DbCommand cmdSelectBpslOne;
|
|
private DbCommand cmdSelectRpslOne;
|
|
private DbCommand cmdSelectRpslAll;
|
|
private DbCommand cmdSelectBpsl2;
|
|
|
|
public BpslDAC()
|
|
{
|
|
// 取出最後一筆異動
|
|
cmdSelectRpsl = Query.NewCommand(
|
|
@"SELECT TOP 1 RPENO BPENO, RPITM BPITM, RPBDT BPBDT, RPSRM BPSRM, RPSRD BPSRD,
|
|
RPP01 BPP01, RPP02 BPP02, RPP03 BPP03, RPP04 BPP04, RPP05 BPP05, APPNO
|
|
FROM RPSL WHERE RPENO=@BPENO ORDER BY BPITM DESC");
|
|
cmdSelectRpsl.Parameters.Add(Query.NewParameter("BPENO", DbType.String));
|
|
|
|
// 取出員工薪資資料
|
|
cmdSelectBpslOne = Query.NewCommand(
|
|
@"SELECT *, 0 BPITM, NULL BPBDT FROM BPSL WHERE BPENO=@BPENO");
|
|
cmdSelectBpslOne.Parameters.Add(Query.NewParameter("BPENO", DbType.String));
|
|
|
|
// 取出某一筆薪資異動資料
|
|
cmdSelectRpslOne = Query.NewCommand(
|
|
@"SELECT RPENO BPENO, RPITM BPITM, RPBDT BPBDT, RPSRM BPSRM, RPSRD BPSRD,
|
|
RPP01 BPP01, RPP02 BPP02, RPP03 BPP03, RPP04 BPP04, RPP05 BPP05, APPNO
|
|
FROM RPSL WHERE RPENO=@BPENO AND RPITM=@BPITM");
|
|
cmdSelectRpslOne.Parameters.Add(Query.NewParameter("BPENO", DbType.String));
|
|
cmdSelectRpslOne.Parameters.Add(Query.NewParameter("BPITM", DbType.Int32));
|
|
|
|
// 取出某員工編號全部的紀錄，以異動次數排序
|
|
cmdSelectRpslAll = Query.NewCommand(
|
|
@"SELECT BPENO, 0 BPITM, NULL BPBDT, BPSRM, BPSRD,
|
|
BPP01, BPP02, BPP03, BPP04, BPP05, APPNO
|
|
FROM BPSL
|
|
WHERE BPENO=@BPENO
|
|
UNION
|
|
SELECT RPENO BPENO, RPITM BPITM, RPBDT BPBDT, RPSRM BPSRM, RPSRD BPSRD,
|
|
RPP01 BPP01, RPP02 BPP02, RPP03 BPP03, RPP04 BPP04, RPP05 BPP05, APPNO
|
|
FROM RPSL
|
|
WHERE RPENO=@BPENO
|
|
ORDER BY BPITM");
|
|
cmdSelectRpslAll.Parameters.Add(Query.NewParameter("BPENO", DbType.String));
|
|
|
|
// 取出某員工編號全部的紀錄，以生效日期排序
|
|
cmdSelectBpsl2 = Query.NewCommand(
|
|
@"SELECT BPENO, 0 BPITM, '1899/12/31' BPBDT, BPSRM, BPSRD,
|
|
BPP01, BPP02, BPP03, BPP04, BPP05, APPNO
|
|
FROM BPSL
|
|
WHERE BPENO=@BPENO
|
|
UNION
|
|
SELECT RPENO BPENO, RPITM BPITM, RPBDT BPBDT, RPSRM BPSRM, RPSRD BPSRD,
|
|
RPP01 BPP01, RPP02 BPP02, RPP03 BPP03, RPP04 BPP04, RPP05 BPP05, APPNO
|
|
FROM RPSL
|
|
WHERE RPENO=@BPENO
|
|
ORDER BY BPBDT");
|
|
cmdSelectBpsl2.Parameters.Add(Query.NewParameter("BPENO", DbType.String));
|
|
}
|
|
|
|
public BpslDAC(string connectionConfig, string connectionConfigName)
|
|
: this()
|
|
{
|
|
base._connectionConfig = connectionConfig;
|
|
base._connectionConfigName = connectionConfigName;
|
|
}
|
|
|
|
public BpslDAC(WQuery query)
|
|
: this()
|
|
{
|
|
base._query = query;
|
|
}
|
|
|
|
private DataTable NewResult()
|
|
{
|
|
// 準備輸出資料架構
|
|
DataTable Result = new DataTable();
|
|
Result.Columns.Add("BPENO", typeof(String));
|
|
Result.Columns.Add("BPITM", typeof(Int32));
|
|
Result.Columns.Add("BPBDT", typeof(DateTime));
|
|
Result.Columns.Add("BPSRM", typeof(Int32));
|
|
Result.Columns.Add("BPSRD", typeof(Int32));
|
|
Result.Columns.Add("BPP01", typeof(Int32));
|
|
Result.Columns.Add("BPP02", typeof(Int32));
|
|
Result.Columns.Add("BPP03", typeof(Int32));
|
|
Result.Columns.Add("BPP04", typeof(Int32));
|
|
Result.Columns.Add("BPP05", typeof(Int32));
|
|
Result.Columns.Add("APPNO", typeof(Int32));
|
|
Result.Columns.Add("BPSRM_Changed", typeof(Char));
|
|
Result.Columns.Add("BPSRD_Changed", typeof(Char));
|
|
Result.Columns.Add("BPP01_Changed", typeof(Char));
|
|
Result.Columns.Add("BPP02_Changed", typeof(Char));
|
|
Result.Columns.Add("BPP03_Changed", typeof(Char));
|
|
Result.Columns.Add("BPP04_Changed", typeof(Char));
|
|
Result.Columns.Add("BPP05_Changed", typeof(Char));
|
|
|
|
return Result;
|
|
}
|
|
|
|
private void FillResult(DataTable Result, DataRow source)
|
|
{
|
|
DataRow newRow = Result.NewRow();
|
|
newRow["BPENO"] = source["BPENO"];
|
|
newRow["BPITM"] = source["BPITM"];
|
|
newRow["BPBDT"] = source["BPBDT"];
|
|
newRow["BPSRM"] = source["BPSRM"];
|
|
newRow["BPSRD"] = source["BPSRD"];
|
|
newRow["BPP01"] = source["BPP01"];
|
|
newRow["BPP02"] = source["BPP02"];
|
|
newRow["BPP03"] = source["BPP03"];
|
|
newRow["BPP04"] = source["BPP04"];
|
|
newRow["BPP05"] = source["BPP05"];
|
|
newRow["APPNO"] = source["APPNO"];
|
|
newRow["BPSRM_Changed"] = 'N';
|
|
newRow["BPSRD_Changed"] = 'N';
|
|
newRow["BPP01_Changed"] = 'N';
|
|
newRow["BPP02_Changed"] = 'N';
|
|
newRow["BPP03_Changed"] = 'N';
|
|
newRow["BPP04_Changed"] = 'N';
|
|
newRow["BPP05_Changed"] = 'N';
|
|
Result.Rows.Add(newRow);
|
|
}
|
|
|
|
private Char FieldDiff(object obj1, object obj2)
|
|
{
|
|
string s1 = obj1 != DBNull.Value ? obj1.ToString() : "";
|
|
string s2 = obj2 != DBNull.Value ? obj2.ToString() : "";
|
|
if (s1.CompareTo(s2) != 0)
|
|
return 'Y';
|
|
else
|
|
return 'N';
|
|
}
|
|
|
|
private void FillResult(DataTable Result, DataRow current, DataRow prev)
|
|
{
|
|
DataRow newRow = Result.NewRow();
|
|
newRow["BPENO"] = current["BPENO"];
|
|
newRow["BPITM"] = current["BPITM"];
|
|
newRow["BPBDT"] = current["BPBDT"];
|
|
newRow["BPSRM"] = current["BPSRM"];
|
|
newRow["BPSRD"] = current["BPSRD"];
|
|
newRow["BPP01"] = current["BPP01"];
|
|
newRow["BPP02"] = current["BPP02"];
|
|
newRow["BPP03"] = current["BPP03"];
|
|
newRow["BPP04"] = current["BPP04"];
|
|
newRow["BPP05"] = current["BPP05"];
|
|
newRow["APPNO"] = current["APPNO"];
|
|
newRow["BPSRM_Changed"] = FieldDiff(current["BPSRM"], prev["BPSRM"]);
|
|
newRow["BPSRD_Changed"] = FieldDiff(current["BPSRD"], prev["BPSRD"]);
|
|
newRow["BPP01_Changed"] = FieldDiff(current["BPP01"], prev["BPP01"]);
|
|
newRow["BPP02_Changed"] = FieldDiff(current["BPP02"], prev["BPP02"]);
|
|
newRow["BPP03_Changed"] = FieldDiff(current["BPP03"], prev["BPP03"]);
|
|
newRow["BPP04_Changed"] = FieldDiff(current["BPP04"], prev["BPP04"]);
|
|
newRow["BPP05_Changed"] = FieldDiff(current["BPP05"], prev["BPP05"]);
|
|
Result.Rows.Add(newRow);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 依條件取出資料，給 GridView 的 Select 用的
|
|
/// </summary>
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select(string BPENO, string BPNME)
|
|
{
|
|
if (cmdSelectBpslBpeno == null)
|
|
cmdSelectBpslBpeno = Query.NewCommand();
|
|
cmdSelectBpslBpeno.Parameters.Clear();
|
|
|
|
// 清空輸出資料表
|
|
DataTable Result = NewResult();
|
|
|
|
// 依條件抓出員工編號
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("SELECT A.BPENO FROM BPSL A INNER JOIN BPSNView B ON A.BPENO=B.BPENO WHERE 1=1 ");
|
|
if (BPENO != null && BPENO.Length > 0)
|
|
{
|
|
sb.Append(" AND A.BPENO LIKE @BPENO ");
|
|
cmdSelectBpslBpeno.Parameters.Add(Query.NewParameter("BPENO", BPENO + "%"));
|
|
}
|
|
if (BPNME != null && BPNME.Length > 0)
|
|
{
|
|
sb.Append(" AND B.BPNME LIKE @BPNME ");
|
|
cmdSelectBpslBpeno.Parameters.Add(Query.NewParameter("BPNME", BPNME + "%"));
|
|
}
|
|
cmdSelectBpslBpeno.CommandText = sb.ToString();
|
|
DataTable bpenoTmp = Query.Select(cmdSelectBpslBpeno);
|
|
|
|
DataTable bpslTmp;
|
|
// 一筆一筆的抓出應該顯示的資料
|
|
foreach (DataRow bpenoRow in bpenoTmp.Rows)
|
|
{
|
|
// 依照員工編號，抓出最後一筆異動
|
|
cmdSelectRpsl.Parameters[0].Value = bpenoRow[0];
|
|
bpslTmp = Query.Select(cmdSelectRpsl);
|
|
|
|
// 有異動資料，填入異動資料
|
|
if (bpslTmp.Rows.Count != 0)
|
|
{
|
|
FillResult(Result, bpslTmp.Rows[0]);
|
|
}
|
|
// 沒有異動資料，填入原始資料
|
|
else
|
|
{
|
|
cmdSelectBpslOne.Parameters[0].Value = bpenoRow[0];
|
|
bpslTmp = Query.Select(cmdSelectBpslOne);
|
|
FillResult(Result, bpslTmp.Rows[0]);
|
|
}
|
|
}
|
|
|
|
return Result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取出最新資料，給 FormView 的 Select 用的
|
|
/// </summary>
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable SelectOne(string BPENO, int BPITM)
|
|
{
|
|
return SelectOne(BPENO, BPITM, null);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取出某一筆薪資異動資料
|
|
/// </summary>
|
|
public DataTable SelectOne(string BPENO, int BPITM, DbTransaction tran)
|
|
{
|
|
DataTable Result = NewResult();
|
|
|
|
if (BPENO != null && BPENO.Length > 0)
|
|
{
|
|
// 取原始資料
|
|
if (BPITM == 0)
|
|
{
|
|
cmdSelectBpslOne.Parameters[0].Value = BPENO;
|
|
DataTable tmp;
|
|
if (tran == null)
|
|
tmp = Query.Select(cmdSelectBpslOne);
|
|
else
|
|
tmp = Query.Select(cmdSelectBpslOne, tran);
|
|
if (tmp.Rows.Count > 0)
|
|
FillResult(Result, tmp.Rows[0]);
|
|
}
|
|
else
|
|
{
|
|
cmdSelectRpslOne.Parameters[0].Value = BPENO;
|
|
cmdSelectRpslOne.Parameters[1].Value = BPITM;
|
|
DataTable tmp;
|
|
if (tran == null)
|
|
tmp = Query.Select(cmdSelectRpslOne);
|
|
else
|
|
tmp = Query.Select(cmdSelectRpslOne, tran);
|
|
if (tmp.Rows.Count > 0)
|
|
FillResult(Result, tmp.Rows[0]);
|
|
}
|
|
}
|
|
return Result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取出所有薪資異動歷史資料，薪資異動查詢用
|
|
/// </summary>
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable SelectRPSL(string BPENO)
|
|
{
|
|
DataTable Result = NewResult();
|
|
|
|
if (BPENO != null && BPENO.Length > 0)
|
|
{
|
|
cmdSelectRpslAll.Parameters[0].Value = BPENO;
|
|
DataTable tmp = Query.Select(cmdSelectRpslAll);
|
|
for (int i = 0; i < tmp.Rows.Count; i++)
|
|
{
|
|
if (i > 0)
|
|
FillResult(Result, tmp.Rows[i], tmp.Rows[i - 1]);
|
|
else
|
|
FillResult(Result, tmp.Rows[i]);
|
|
}
|
|
}
|
|
return Result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取出日期區間內薪資基本資料以及薪資異動資料
|
|
/// </summary>
|
|
public DataTable SelectBPSLDateRange(string bpeno, DateTime startDay, DateTime endDay, DbTransaction tran)
|
|
{
|
|
DataTable Result = NewResult();
|
|
|
|
if (bpeno != null && bpeno.Length > 0)
|
|
{
|
|
cmdSelectBpsl2.Parameters[0].Value = bpeno;
|
|
DataTable tmp = Query.Select(cmdSelectBpsl2, tran);
|
|
int startIndex = -1;
|
|
int endIndex = -1;
|
|
for (int i = 0; i < tmp.Rows.Count; i++)
|
|
{
|
|
DateTime bpbdt = Convert.ToDateTime(tmp.Rows[i]["BPBDT"]);
|
|
if (startDay >= bpbdt)
|
|
startIndex = i;
|
|
if (endDay >= bpbdt)
|
|
endIndex = i;
|
|
}
|
|
|
|
if (startIndex != -1 && endIndex != -1)
|
|
{
|
|
for (int i = startIndex; i <= endIndex; i++)
|
|
{
|
|
FillResult(Result, tmp.Rows[i]);
|
|
}
|
|
}
|
|
}
|
|
return Result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取出某一筆薪資異動紀錄
|
|
/// </summary>
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable SelectRPSLOne(string BPENO, int BPITM)
|
|
{
|
|
DataTable Result = NewResult();
|
|
|
|
if (BPENO != null && BPENO.Length > 0)
|
|
{
|
|
cmdSelectRpslAll.Parameters[0].Value = BPENO;
|
|
DataTable tmp = Query.Select(cmdSelectRpslAll);
|
|
for (int i = 0; i < tmp.Rows.Count; i++)
|
|
{
|
|
if (Convert.ToInt32(tmp.Rows[i]["BPITM"]) == BPITM)
|
|
{
|
|
if (i > 0)
|
|
FillResult(Result, tmp.Rows[i], tmp.Rows[i - 1]);
|
|
else
|
|
FillResult(Result, tmp.Rows[i]);
|
|
}
|
|
}
|
|
}
|
|
return Result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得下一個薪資異動項次
|
|
/// </summary>
|
|
private int NextRPITM(string BPENO)
|
|
{
|
|
DbCommand cmd = Query.NewCommand("SELECT ISNULL(MAX(RPITM), 0) + 1 FROM RPSL WHERE RPENO=@RPENO");
|
|
cmd.Parameters.Add(Query.NewParameter("RPENO", BPENO));
|
|
int Result;
|
|
ExecuteScalar(cmd, out Result);
|
|
return Result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增一筆員工薪資資料，或薪資異動紀錄
|
|
/// </summary>
|
|
[DataObjectMethod(DataObjectMethodType.Insert)]
|
|
public int Insert(string BPENO, int BPITM, DateTime BPBDT, int BPSRM, int BPSRD, int BPP01, int BPP02,
|
|
int BPP03, int BPP04, int BPP05, string TRUSR)
|
|
{
|
|
DbCommand cmdInsert = Query.NewCommand();
|
|
|
|
if (BPITM == 0)
|
|
{
|
|
cmdInsert.CommandText =
|
|
@"INSERT INTO BPSL
|
|
(BPENO, BPSRM, BPSRD, BPP01, BPP02, BPP03, BPP04, BPP05, APPNO,
|
|
TRDAT, TRMOD, TRUSR)
|
|
VALUES
|
|
(@BPENO, @BPSRM, @BPSRD, @BPP01, @BPP02, @BPP03, @BPP04, @BPP05, 1,
|
|
GETDATE(), 'INSERT', @TRUSR)";
|
|
AddParam(cmdInsert, "BPENO", BPENO);
|
|
AddParam(cmdInsert, "BPSRM", BPSRM);
|
|
AddParam(cmdInsert, "BPSRD", BPSRD);
|
|
AddParam(cmdInsert, "BPP01", BPP01);
|
|
AddParam(cmdInsert, "BPP02", BPP02);
|
|
AddParam(cmdInsert, "BPP03", BPP03);
|
|
AddParam(cmdInsert, "BPP04", BPP04);
|
|
AddParam(cmdInsert, "BPP05", BPP05);
|
|
AddParam(cmdInsert, "TRUSR", TRUSR);
|
|
}
|
|
else
|
|
{
|
|
cmdInsert.CommandText =
|
|
@"INSERT INTO RPSL
|
|
(RPENO, RPITM, RPBDT, RPSRM, RPSRD, RPP01, RPP02, RPP03, RPP04, RPP05, APPNO,
|
|
TRDAT, TRMOD, TRUSR)
|
|
VALUES
|
|
(@RPENO, @RPITM, @RPBDT, @RPSRM, @RPSRD, @RPP01, @RPP02, @RPP03, @RPP04, @RPP05, 1,
|
|
GETDATE(), 'INSERT', @TRUSR)";
|
|
AddParam(cmdInsert, "RPENO", BPENO);
|
|
AddParam(cmdInsert, "RPITM", NextRPITM(BPENO));
|
|
AddParam(cmdInsert, "RPBDT", BPBDT);
|
|
AddParam(cmdInsert, "RPSRM", BPSRM);
|
|
AddParam(cmdInsert, "RPSRD", BPSRD);
|
|
AddParam(cmdInsert, "RPP01", BPP01);
|
|
AddParam(cmdInsert, "RPP02", BPP02);
|
|
AddParam(cmdInsert, "RPP03", BPP03);
|
|
AddParam(cmdInsert, "RPP04", BPP04);
|
|
AddParam(cmdInsert, "RPP05", BPP05);
|
|
AddParam(cmdInsert, "TRUSR", TRUSR);
|
|
}
|
|
|
|
return Query.ExecuteNonQuery(cmdInsert);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 更正員工薪資資料或薪資異動紀錄
|
|
/// </summary>
|
|
[DataObjectMethod(DataObjectMethodType.Update)]
|
|
public int Update(string BPENO, int BPITM, DateTime BPBDT, int BPSRM, int BPSRD, int BPP01, int BPP02,
|
|
int BPP03, int BPP04, int BPP05, string TRUSR, int APPNO,
|
|
string OLD_BPENO, int OLD_BPITM, int OLD_APPNO)
|
|
{
|
|
DbCommand cmdUpdate = Query.NewCommand();
|
|
|
|
if (OLD_BPITM == 0)
|
|
{
|
|
cmdUpdate.CommandText =
|
|
@"UPDATE BPSL SET
|
|
BPENO = @BPENO,
|
|
BPSRM = @BPSRM,
|
|
BPSRD = @BPSRD,
|
|
BPP01 = @BPP01,
|
|
BPP02 = @BPP02,
|
|
BPP03 = @BPP03,
|
|
BPP04 = @BPP04,
|
|
BPP05 = @BPP05,
|
|
APPNO = APPNO + 1,
|
|
TRDAT = GETDATE(),
|
|
TRMOD = 'UPDATE',
|
|
TRUSR = @TRUSR
|
|
WHERE BPENO = @OLD_BPENO
|
|
AND APPNO = @OLD_APPNO";
|
|
AddParam(cmdUpdate, "BPENO", BPENO);
|
|
AddParam(cmdUpdate, "BPSRM", BPSRM);
|
|
AddParam(cmdUpdate, "BPSRD", BPSRD);
|
|
AddParam(cmdUpdate, "BPP01", BPP01);
|
|
AddParam(cmdUpdate, "BPP02", BPP02);
|
|
AddParam(cmdUpdate, "BPP03", BPP03);
|
|
AddParam(cmdUpdate, "BPP04", BPP04);
|
|
AddParam(cmdUpdate, "BPP05", BPP05);
|
|
AddParam(cmdUpdate, "TRUSR", TRUSR);
|
|
AddParam(cmdUpdate, "OLD_BPENO", OLD_BPENO);
|
|
AddParam(cmdUpdate, "OLD_APPNO", OLD_APPNO);
|
|
}
|
|
else
|
|
{
|
|
cmdUpdate.CommandText =
|
|
@"UPDATE RPSL SET
|
|
RPENO = @RPENO,
|
|
RPITM = @RPITM,
|
|
RPBDT = @RPBDT,
|
|
RPSRM = @RPSRM,
|
|
RPSRD = @RPSRD,
|
|
RPP01 = @RPP01,
|
|
RPP02 = @RPP02,
|
|
RPP03 = @RPP03,
|
|
RPP04 = @RPP04,
|
|
RPP05 = @RPP05,
|
|
APPNO = APPNO + 1,
|
|
TRDAT = GETDATE(),
|
|
TRMOD = 'UPDATE',
|
|
TRUSR = @TRUSR
|
|
WHERE RPENO = @OLD_RPENO
|
|
AND RPITM = @OLD_RPITM
|
|
AND APPNO = @OLD_APPNO";
|
|
AddParam(cmdUpdate, "RPENO", BPENO);
|
|
AddParam(cmdUpdate, "RPITM", BPITM);
|
|
AddParam(cmdUpdate, "RPBDT", BPBDT);
|
|
AddParam(cmdUpdate, "RPSRM", BPSRM);
|
|
AddParam(cmdUpdate, "RPSRD", BPSRD);
|
|
AddParam(cmdUpdate, "RPP01", BPP01);
|
|
AddParam(cmdUpdate, "RPP02", BPP02);
|
|
AddParam(cmdUpdate, "RPP03", BPP03);
|
|
AddParam(cmdUpdate, "RPP04", BPP04);
|
|
AddParam(cmdUpdate, "RPP05", BPP05);
|
|
AddParam(cmdUpdate, "TRUSR", TRUSR);
|
|
AddParam(cmdUpdate, "OLD_RPENO", OLD_BPENO);
|
|
AddParam(cmdUpdate, "OLD_RPITM", OLD_BPITM);
|
|
AddParam(cmdUpdate, "OLD_APPNO", OLD_APPNO);
|
|
}
|
|
|
|
return Query.ExecuteNonQuery(cmdUpdate);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 刪除員工薪資資料或薪資異動紀錄
|
|
/// </summary>
|
|
[DataObjectMethod(DataObjectMethodType.Delete)]
|
|
public int Delete(string OLD_BPENO, int OLD_BPITM, int OLD_APPNO)
|
|
{
|
|
OLD_BPENO = OLD_BPENO != null ? OLD_BPENO : "";
|
|
|
|
DbCommand cmdDelete = Query.NewCommand();
|
|
|
|
if (OLD_BPITM == 0)
|
|
{
|
|
cmdDelete.CommandText =
|
|
@"DELETE FROM BPSL
|
|
WHERE BPENO = @OLD_BPENO
|
|
AND APPNO = @OLD_APPNO";
|
|
AddParam(cmdDelete, "OLD_BPENO", OLD_BPENO);
|
|
AddParam(cmdDelete, "OLD_APPNO", OLD_APPNO);
|
|
}
|
|
else
|
|
{
|
|
cmdDelete.CommandText =
|
|
@"DELETE FROM RPSL
|
|
WHERE RPENO = @OLD_BPENO
|
|
AND RPITM = @OLD_BPITM
|
|
AND APPNO = @OLD_APPNO";
|
|
AddParam(cmdDelete, "OLD_BPENO", OLD_BPENO);
|
|
AddParam(cmdDelete, "OLD_BPITM", OLD_BPITM);
|
|
AddParam(cmdDelete, "OLD_APPNO", OLD_APPNO);
|
|
}
|
|
|
|
return Query.ExecuteNonQuery(cmdDelete);
|
|
}
|
|
} |