124 lines
3.6 KiB
C#
124 lines
3.6 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_BPSN : DAC
|
|
{
|
|
public DAC_BPSN()
|
|
{ }
|
|
|
|
public DAC_BPSN(DbConnection conn)
|
|
: base(conn)
|
|
{ }
|
|
|
|
public string Get員工姓名(object BPENO)
|
|
{
|
|
if (BPENO == null)
|
|
BPENO = DBNull.Value;
|
|
|
|
DbCommand cmd = NewCommand();
|
|
cmd.CommandText = @"SELECT BPNME FROM BPSNView WHERE BPENO=@BPENO";
|
|
AddParam(cmd, "BPENO", BPENO);
|
|
string result;
|
|
ExecuteScalar(cmd, out result);
|
|
return result;
|
|
}
|
|
|
|
public string Get電子郵件(object BPENO)
|
|
{
|
|
if (BPENO == null)
|
|
BPENO = DBNull.Value;
|
|
|
|
DbCommand cmd = NewCommand();
|
|
cmd.CommandText = @"SELECT BPEML FROM BPSNView WHERE BPENO=@BPENO";
|
|
AddParam(cmd, "BPENO", BPENO);
|
|
string result;
|
|
ExecuteScalar(cmd, out result);
|
|
return result;
|
|
}
|
|
|
|
public string Get公司電話(object BPENO)
|
|
{
|
|
if (BPENO == null)
|
|
BPENO = DBNull.Value;
|
|
|
|
DbCommand cmd = NewCommand();
|
|
cmd.CommandText = @"SELECT BPOTL FROM BPSNView WHERE BPENO=@BPENO";
|
|
AddParam(cmd, "BPENO", BPENO);
|
|
string result;
|
|
ExecuteScalar(cmd, out result);
|
|
return result;
|
|
}
|
|
|
|
public bool Is管理群組(object BPENO)
|
|
{
|
|
if (BPENO == null)
|
|
return false;
|
|
|
|
DbCommand cmd = NewCommand();
|
|
cmd.CommandText =
|
|
String.Format(@"SELECT COUNT(1) FROM BWEH WHERE BPENO=@BPENO AND BPGLP IN ({0})", PublicVariable.ManagerGroup);
|
|
AddParam(cmd, "BPENO", BPENO);
|
|
int i;
|
|
ExecuteScalar(cmd, out i);
|
|
return i > 0;
|
|
}
|
|
|
|
public bool Is承辦人群組(object BPENO)
|
|
{
|
|
if (BPENO == null)
|
|
return false;
|
|
|
|
DbCommand cmd = NewCommand();
|
|
cmd.CommandText =
|
|
String.Format(@"SELECT COUNT(1) FROM BWEH WHERE BPENO=@BPENO AND BPGLP IN ({0})", PublicVariable.WorkerGroup);
|
|
AddParam(cmd, "BPENO", BPENO);
|
|
int i;
|
|
ExecuteScalar(cmd, out i);
|
|
return i > 0;
|
|
}
|
|
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select(string BPENO, string BPNME)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select C.BPENO AS 員工編號, C.BPNME as 員工姓名, C.BPEML as 電子郵件,
|
|
D.RAPYN AS 計畫年度編號, ISNULL('【' + I.BSSCR + '】', '') + E.BPPYM AS 計畫年度名稱,
|
|
D.RABRH AS 站別編號, F.BSSCR AS 站別名稱,
|
|
D.RAJNO AS 職務編號, G.BPJME AS 職務名稱,
|
|
D.RACNO AS 等級編號, H.BPCME AS 等級名稱
|
|
from BPSNView C
|
|
left outer join RASN D on C.BPENO = D.RANUM and D.RAEDT is NULL
|
|
left outer join BPAA E on D.RAPYN = E.BPPYN
|
|
left outer join BCLS F on F.BSCLS = D.RABRH and F.BSPAR = 'BPSNBRH'
|
|
left outer join BPJA G on G.BPPYN = D.RAPYN and G.BPJNO = D.RAJNO
|
|
left outer join BPJB H on H.BPPYN = D.RAPYN and H.BPJNO = D.RAJNO and H.BPCNO = D.RACNO
|
|
left outer join BCLS I on I.BSCLS = SUBSTRING(D.RAPYN, LEN(D.RAPYN)-5,1) and I.BSPAR = 'BCUSZON'
|
|
where C.BPEDY is null ";
|
|
|
|
if (BPENO != null && BPENO.Length > 0)
|
|
{
|
|
cmdSelect.CommandText += " and C.BPENO like @BPENO ";
|
|
AddParam(cmdSelect, "BPENO", BPENO + "%");
|
|
}
|
|
|
|
if (BPNME != null && BPNME.Length > 0)
|
|
{
|
|
cmdSelect.CommandText += " and C.BPNME like @BPNME ";
|
|
AddParam(cmdSelect, "BPNME", BPNME + "%");
|
|
}
|
|
|
|
cmdSelect.CommandText += " order by C.BPENO";
|
|
return Select(cmdSelect);
|
|
}
|
|
}
|
|
} |