83 lines
3.1 KiB
C#
83 lines
3.1 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Web;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.ComponentModel;
|
|
using WebLib;
|
|
|
|
namespace Education
|
|
{
|
|
[DataObject]
|
|
public class DAC_RASN : DAC
|
|
{
|
|
public DAC_RASN()
|
|
{ }
|
|
|
|
public DAC_RASN(DbConnection conn)
|
|
: base(conn)
|
|
{ }
|
|
|
|
/*
|
|
SELECT B.BPENO AS 員工編號, B.BPENO + ' ' + B.BPNME AS 員工姓名,
|
|
ISNULL(D.RPCPY, C.RPCPY) AS 公司別,
|
|
A.RAPYN AS 專案編號, F.BPPYM AS 專案名稱,
|
|
A.RAJNO AS 職稱編號, E.BPJME AS 職稱名稱
|
|
FROM BPSN B
|
|
INNER JOIN RASN A ON A.RANUM=B.BPENO AND B.BPEDY IS NULL -- 派遣紀錄
|
|
LEFT JOIN RPIR C ON A.RANUM=C.RPENO AND C.RPREN=(SELECT MAX(RPREN) FROM RPIR WHERE RPENO=A.RANUM) -- 勞健保紀錄
|
|
LEFT JOIN RPIB D ON C.RPREN=D.RPREN AND D.RPITM=(SELECT MAX(RPITM) FROM RPIB WHERE RPREN=D.RPREN) -- 勞健保異動紀錄
|
|
LEFT JOIN BPJA E ON E.BPPYN=A.RAPYN AND E.BPJNO=A.RAJNO -- 專案年度職稱
|
|
LEFT JOIN BPAA F ON F.BPPYN=A.RAPYN -- 專案年度接單
|
|
WHERE RAEDT IS NULL
|
|
ORDER BY BPENO
|
|
*/
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public NameValueList Select專案人員(string 專案年度編號, string 公司別)
|
|
{
|
|
NameValueList result = new NameValueList();
|
|
|
|
DbCommand cmd = NewCommand();
|
|
cmd.CommandText =
|
|
@"SELECT 員工編號, 員工姓名 FROM
|
|
(
|
|
SELECT A.RANUM AS 員工編號, A.RANUM + ' ' + B.BPNME AS 員工姓名, ISNULL(D.RPCPY, C.RPCPY) AS 公司別
|
|
FROM RASN A
|
|
INNER JOIN BPSNView B ON A.RANUM=B.BPENO AND B.BPEDY IS NULL
|
|
LEFT JOIN RPIR C ON A.RANUM=C.RPENO AND C.RPREN=(SELECT MAX(RPREN) FROM RPIR WHERE RPENO=A.RANUM)
|
|
LEFT JOIN RPIB D ON C.RPREN=D.RPREN AND D.RPITM=(SELECT MAX(RPITM) FROM RPIB WHERE RPREN=D.RPREN)
|
|
WHERE A.RAPYN=@專案年度編號
|
|
AND RAEDT IS NULL
|
|
) AS TMP1
|
|
WHERE 公司別=@公司別
|
|
ORDER BY 員工編號";
|
|
|
|
// @"SELECT A.RANUM AS 員工編號, A.RANUM + ' ' + B.BPNME AS 員工姓名
|
|
// FROM RASN A
|
|
// INNER JOIN BPSN B ON A.RANUM=B.BPENO AND B.BPEDY IS NULL
|
|
// WHERE A.RAPYN=@RAPYN
|
|
// AND RAEDT IS NULL
|
|
// ORDER BY A.RANUM";
|
|
AddParam(cmd, "專案年度編號", 專案年度編號);
|
|
AddParam(cmd, "公司別", 公司別);
|
|
|
|
bool wantClose;
|
|
IDataReader reader = ExecuteReader(cmd, out wantClose);
|
|
try
|
|
{
|
|
result.Fill(reader, 1, 0);
|
|
cmd.Cancel();
|
|
reader.Close();
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
reader.Dispose();
|
|
CloseConnection(wantClose);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
}
|
|
} |