749 lines
36 KiB
C#
749 lines
36 KiB
C#
//
|
|
// 這個檔案是 課程報名 資料表的資料存取類別 檔案二
|
|
// 您可以修改這個檔案，加入您自訂的屬性與方法
|
|
//
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using System.ComponentModel;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using WebLib;
|
|
using System.Data.SqlClient;
|
|
|
|
namespace Education
|
|
{
|
|
///<summary>
|
|
/// 課程報名 資料存取類別
|
|
///</summary>
|
|
public partial class DAC_課程報名 : WebLib.DAC
|
|
{
|
|
#region 自動產生的資料存取方法
|
|
///<summary>
|
|
/// InsertOne 之前會執行此方法
|
|
/// 在這裡可以做一些檢查或是自動編號等動作
|
|
/// 回傳 false 會打斷 InsertOne 的執行
|
|
///</summary>
|
|
protected virtual bool BeforeInsertOne(課程報名 value)
|
|
{
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// InsertOne 之後會執行此方法
|
|
///</summary>
|
|
protected virtual void AfterInsertOne(課程報名 value)
|
|
{
|
|
}
|
|
|
|
///<summary>
|
|
/// InsertOne 發生錯誤時會執行此方法
|
|
///</summary>
|
|
///<param name="handled">錯誤是否已經處理妥當</param>
|
|
protected virtual void OnInsertOneError(Exception error, out bool handled)
|
|
{
|
|
handled = false;
|
|
}
|
|
|
|
///<summary>
|
|
/// UpdateOne 之前會執行此方法
|
|
/// 在這裡可以做一些檢查
|
|
/// 回傳 false 會打斷 UpdateOne 的執行
|
|
///</summary>
|
|
protected virtual bool BeforeUpdateOne(課程報名 value)
|
|
{
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// UpdateOne 之後會執行此方法
|
|
///</summary>
|
|
protected virtual void AfterUpdateOne(課程報名 value)
|
|
{
|
|
}
|
|
|
|
///<summary>
|
|
/// UpdateOne 發生錯誤時會執行此方法
|
|
///</summary>
|
|
///<param name="handled">錯誤是否已經處理妥當</param>
|
|
protected virtual void OnUpdateOneError(Exception error, out bool handled)
|
|
{
|
|
handled = false;
|
|
}
|
|
|
|
///<summary>
|
|
/// DeleteOne 之前會執行此方法
|
|
/// 在這裡可以做一些檢查
|
|
/// 回傳 false 會打斷 DeleteOne 的執行
|
|
///</summary>
|
|
protected virtual bool BeforeDeleteOne(課程報名 value)
|
|
{
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// DeleteOne 之後會執行此方法
|
|
///</summary>
|
|
protected virtual void AfterDeleteOne(課程報名 value)
|
|
{
|
|
}
|
|
|
|
///<summary>
|
|
/// DeleteOne 之前會執行此方法
|
|
/// 在這裡可以做一些檢查
|
|
/// 回傳 false 會打斷 DeleteOne 的執行
|
|
///</summary>
|
|
protected virtual bool BeforeDeleteOne(string 學員編號, int 訓練課程編號, int DB_APPNO)
|
|
{
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// DeleteOne 之後會執行此方法
|
|
///</summary>
|
|
protected virtual void AfterDeleteOne(string 學員編號, int 訓練課程編號, int DB_APPNO)
|
|
{
|
|
}
|
|
|
|
///<summary>
|
|
/// DeleteOne 發生錯誤時會執行此方法
|
|
///</summary>
|
|
///<param name="handled">錯誤是否已經處理妥當</param>
|
|
protected virtual void OnDeleteOneError(Exception error, out bool handled)
|
|
{
|
|
handled = false;
|
|
}
|
|
|
|
#endregion
|
|
|
|
// 有另外加上去的屬性及方法，請加在這裡
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select選課紀錄(string 學員編號, bool 僅已簽核, bool 僅必修, bool 僅已上課, bool 僅已出席)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select * from (
|
|
select A.學員編號, A.訓練課程編號, A.報名狀態, A.報名日期, A.取消報名日期, A.出席狀況,
|
|
(select count(1) from 學員出席 where 學員出席.訓練課程編號=A.訓練課程編號 and 學員出席.學員編號=A.學員編號) AS 出席次數,
|
|
B.顯示編號, B.訓練名稱, B.訓練日期, B.訓練日期迄, B.取消報名截止期限,
|
|
(select count(1) from 滿意度調查 C where A.訓練課程編號=C.訓練課程企劃編號 and A.學員編號=C.學員編號) as 填寫數,
|
|
J.心得報告編號, J.上架 as 心得上架
|
|
from 課程報名 A
|
|
inner join 訓練課程企劃 B on A.訓練課程編號 = B.編號
|
|
left outer join 心得報告 J on A.訓練課程編號=J.訓練課程企劃編號 and A.學員編號=J.學員編號
|
|
where A.學員編號 = @學員編號";
|
|
|
|
if (僅已簽核)
|
|
cmdSelect.CommandText +=
|
|
@" and exists ( select 1 from 表單簽核主檔
|
|
where 表單類別ID = N'課程企劃' and 表單ID = B.編號
|
|
and 應簽關數 = 已簽關數
|
|
and 應簽關數 <> 'D'
|
|
and 最終結果 = 'Y' ) ";
|
|
|
|
if (僅已上課)
|
|
cmdSelect.CommandText +=
|
|
@" and B.訓練日期 <= GetDate() ";
|
|
|
|
cmdSelect.CommandText += " ) as TMP1 where 1=1 ";
|
|
|
|
if (僅已出席)
|
|
cmdSelect.CommandText +=
|
|
@" and (出席次數 > 0) or (出席狀況 is not null and 出席狀況 <> '') ";
|
|
|
|
cmdSelect.CommandText += " order by 訓練日期 desc";
|
|
AddParam(cmdSelect, "學員編號", 學員編號);
|
|
return Select(cmdSelect);
|
|
}
|
|
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select選課紀錄(int 訓練年度, string 計畫別, string 課程名稱, string 公司別)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select '外訓' AS 開課別, CONVERT(NVARCHAR(10), A.訓練年度) as 訓練日期, '' as 訓練日期迄,
|
|
A.訓練名稱, A.訓練時數, A.員工編號, C.BPNME as 員工姓名,
|
|
D.RAPYN AS 計畫年度編號,
|
|
'【' + (SELECT TOP 1 BSSCR FROM BCLS WHERE BSPAR='BCUSZON' AND BSCLS=SUBSTRING(E.BPPYN,LEN(E.BPPYN)-5,1)) + '】' + E.BPPYM AS 計畫年度名稱,
|
|
D.RABRH AS 站別編號, F.BSRMK AS 站別名稱,
|
|
D.RAJNO AS 職務編號, G.BPJME AS 職務名稱,
|
|
D.RACNO AS 等級編號, H.BPCME AS 等級名稱,
|
|
1 as 填寫數
|
|
from 外部受訓紀錄 A
|
|
inner join BPSNView C on A.員工編號=C.BPENO
|
|
left outer join RASN D on C.BPENO = D.RANUM and D.RAITM = (SELECT MAX(RASN.RAITM) FROM RASN WHERE RASN.RANUM=D.RANUM)
|
|
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
|
|
where 1=1 ";
|
|
|
|
if (訓練年度 > 0)
|
|
{
|
|
cmdSelect.CommandText += " and A.訓練年度 = @訓練年度 ";
|
|
AddParam(cmdSelect, "訓練年度", 訓練年度);
|
|
}
|
|
|
|
if (計畫別 != null && 計畫別.Length > 0)
|
|
{
|
|
cmdSelect.CommandText += " and D.RAPYN = @計畫別 ";
|
|
}
|
|
|
|
if (課程名稱 != null && 課程名稱.Length > 0)
|
|
{
|
|
cmdSelect.CommandText += " and A.訓練名稱 like @課程名稱 ";
|
|
}
|
|
|
|
cmdSelect.CommandText +=
|
|
@"
|
|
union all
|
|
select '內訓' AS 開課別, CONVERT(NVARCHAR(10), B.訓練日期, 120) as 訓練日期, CONVERT(NVARCHAR(10), B.訓練日期迄, 120) as 訓練日期迄,
|
|
B.訓練名稱, B.訓練時數, A.學員編號 as 員工編號, C.BPNME as 學員姓名,
|
|
D.RAPYN AS 計畫年度編號,
|
|
'【' + (SELECT TOP 1 BSSCR FROM BCLS WHERE BSPAR='BCUSZON' AND BSCLS=SUBSTRING(E.BPPYN,LEN(E.BPPYN)-5,1)) + '】' + E.BPPYM AS 計畫年度名稱,
|
|
D.RABRH AS 站別編號, F.BSRMK AS 站別名稱,
|
|
D.RAJNO AS 職務編號, G.BPJME AS 職務名稱,
|
|
D.RACNO AS 等級編號, H.BPCME AS 等級名稱,
|
|
(select count(1) from 滿意度調查 I where A.訓練課程編號=I.訓練課程企劃編號 and A.學員編號=I.學員編號) as 填寫數
|
|
from 課程報名 A
|
|
inner join 訓練課程企劃 B on A.訓練課程編號 = B.編號
|
|
inner join BPSNView C on A.學員編號=C.BPENO
|
|
left outer join RASN D on C.BPENO = D.RANUM and D.RAITM = (SELECT MAX(RASN.RAITM) FROM RASN WHERE RASN.RANUM=D.RANUM)
|
|
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
|
|
where exists ( select 1 from 表單簽核主檔
|
|
where 表單類別ID = N'課程企劃' and 表單ID = B.編號
|
|
and 應簽關數 = 已簽關數
|
|
and 應簽關數 <> 'D'
|
|
and 最終結果 = 'Y' )
|
|
and B.公司別=@公司別 ";
|
|
|
|
if (訓練年度 > 0)
|
|
{
|
|
cmdSelect.CommandText += " and B.訓練日期 >= @訓練日期1 and B.訓練日期 < @訓練日期2 ";
|
|
AddParam(cmdSelect, "訓練日期1", new DateTime(訓練年度, 1, 1));
|
|
AddParam(cmdSelect, "訓練日期2", new DateTime(訓練年度 + 1, 1, 1));
|
|
}
|
|
|
|
if (計畫別 != null && 計畫別.Length > 0)
|
|
{
|
|
cmdSelect.CommandText += " and D.RAPYN = @計畫別 ";
|
|
}
|
|
|
|
if (課程名稱 != null && 課程名稱.Length > 0)
|
|
{
|
|
cmdSelect.CommandText += " and B.訓練名稱 like @課程名稱 ";
|
|
}
|
|
|
|
cmdSelect.CommandText += " order by 訓練日期 desc";
|
|
|
|
AddParam(cmdSelect, "公司別", 公司別);
|
|
AddParam(cmdSelect, "計畫別", 計畫別);
|
|
AddParam(cmdSelect, "課程名稱", "%" + 課程名稱 + "%");
|
|
|
|
return Select(cmdSelect);
|
|
}
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select缺席紀錄(int 年度, string 計畫別, string 訓練名稱, string 公司別, List<string> 訓練企劃編號, List<string> 計畫年度編號)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select A.學員編號, C.BPNME as 學員姓名, A.訓練課程編號, A.報名狀態, A.報名日期, A.取消報名日期, A.出席狀況, A.請假事由,
|
|
B.顯示編號, B.訓練名稱, B.訓練日期, B.訓練日期迄, B.取消報名截止期限, B.計畫別,
|
|
D.RAPYN AS 計畫年度編號,
|
|
'【' + (SELECT TOP 1 BSSCR FROM BCLS WHERE BSPAR='BCUSZON' AND BSCLS=SUBSTRING(E.BPPYN,LEN(E.BPPYN)-5,1)) + '】' + E.BPPYM AS 計畫年度名稱,
|
|
D.RABRH AS 站別編號, F.BSRMK AS 站別名稱,
|
|
D.RAJNO AS 職務編號, G.BPJME AS 職務名稱,
|
|
D.RACNO AS 等級編號, H.BPCME AS 等級名稱,
|
|
|
|
(select count(1) from 滿意度調查 C where A.訓練課程編號=C.訓練課程企劃編號 and A.學員編號=C.學員編號) as 填寫數,
|
|
J.心得報告編號, J.上架 as 心得上架
|
|
from 課程報名 A
|
|
inner join 訓練課程企劃 B on A.訓練課程編號 = B.編號
|
|
inner join BPSNView C on A.學員編號=C.BPENO
|
|
left outer join RASN D on C.BPENO = D.RANUM and D.RAITM = (SELECT MAX(RASN.RAITM) FROM RASN WHERE RASN.RANUM=D.RANUM)
|
|
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 心得報告 J on A.訓練課程編號=J.訓練課程企劃編號 and A.學員編號=J.學員編號
|
|
where exists ( select 1 from 表單簽核主檔
|
|
where 表單類別ID = N'課程企劃' and 表單ID = B.編號
|
|
and 應簽關數 = 已簽關數
|
|
and 應簽關數 <> 'D'
|
|
and 最終結果 = 'Y' )
|
|
and (SELECT COUNT(1) FROM 學員出席 K WHERE A.訓練課程編號=K.訓練課程編號 and A.學員編號=K.學員編號 AND K.出席狀況='Y') = 0 ";
|
|
|
|
if (年度 > 0)
|
|
{
|
|
cmdSelect.CommandText += " and B.訓練日期 >= @訓練日期1 and B.訓練日期 < @訓練日期2 ";
|
|
AddParam(cmdSelect, "訓練日期1", new DateTime(年度, 1, 1));
|
|
AddParam(cmdSelect, "訓練日期2", new DateTime(年度 + 1, 1, 1));
|
|
}
|
|
|
|
if (計畫別 != null && 計畫別.Length > 0)
|
|
{
|
|
cmdSelect.CommandText += " and B.計畫別 = @計畫別 ";
|
|
AddParam(cmdSelect, "計畫別", 計畫別);
|
|
}
|
|
|
|
if (訓練名稱 != null && 訓練名稱.Length > 0)
|
|
{
|
|
cmdSelect.CommandText += " and B.訓練名稱 like @訓練名稱 ";
|
|
AddParam(cmdSelect, "訓練名稱", "%" + 訓練名稱 + "%");
|
|
}
|
|
|
|
if (公司別 != null && 公司別.Length > 0)
|
|
{
|
|
cmdSelect.CommandText += " and B.公司別 = @公司別 ";
|
|
AddParam(cmdSelect, "公司別", 公司別);
|
|
}
|
|
|
|
if (訓練企劃編號 != null && 訓練企劃編號.Count > 0)
|
|
{
|
|
cmdSelect.CommandText += " and B.編號 in (" + String.Join(",", 訓練企劃編號.ToArray()) + ")";
|
|
}
|
|
|
|
if (計畫年度編號 != null && 計畫年度編號.Count > 0)
|
|
{
|
|
cmdSelect.CommandText += " and D.RAPYN in (" + String.Join(",", 計畫年度編號.ToArray()) + ")";
|
|
}
|
|
|
|
cmdSelect.CommandText += " order by B.訓練日期 desc";
|
|
|
|
return Select(cmdSelect);
|
|
}
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select確定報名學員名單(int 訓練課程編號, string 計畫年度編號 = "")
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select A.學員編號, C.BPNME as 學員姓名, C.BPEML as 電子郵件, A.訓練課程編號,
|
|
A.報名狀態, A.報名日期, A.取消報名日期, A.出席狀況, A.請假事由, A.通知發送狀態,
|
|
B.顯示編號, B.訓練名稱, B.訓練日期, B.訓練日期迄, B.取消報名截止期限,
|
|
D.RAPYN AS 計畫年度編號,
|
|
'【' + (SELECT TOP 1 BSSCR FROM BCLS WHERE BSPAR='BCUSZON' AND BSCLS=SUBSTRING(E.BPPYN,LEN(E.BPPYN)-5,1)) + '】' + E.BPPYM AS 計畫年度名稱,
|
|
D.RABRH AS 站別編號, F.BSRMK AS 站別名稱,
|
|
D.RAJNO AS 職務編號, G.BPJME AS 職務名稱,
|
|
D.RACNO AS 等級編號, H.BPCME AS 等級名稱,
|
|
(select count(1) from 滿意度調查 I where A.訓練課程編號=I.訓練課程企劃編號 and A.學員編號=I.學員編號) as 填寫數,
|
|
J.心得報告編號, J.上架 as 心得上架
|
|
from 課程報名 A
|
|
inner join 訓練課程企劃 B on A.訓練課程編號 = B.編號
|
|
inner join BPSNView C on A.學員編號=C.BPENO
|
|
left outer join RASN D on C.BPENO = D.RANUM and D.RAITM = (SELECT MAX(RASN.RAITM) FROM RASN WHERE RASN.RANUM=D.RANUM)
|
|
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 心得報告 J on A.訓練課程編號=J.訓練課程企劃編號 and A.學員編號=J.學員編號
|
|
where A.訓練課程編號 = @訓練課程編號
|
|
and (A.報名狀態 = 'Y' or A.報名狀態 = 'R') ";
|
|
AddParam(cmdSelect, "訓練課程編號", 訓練課程編號);
|
|
|
|
if (計畫年度編號 != null && 計畫年度編號.Length > 0)
|
|
{
|
|
cmdSelect.CommandText += " and D.RAPYN = @計畫年度編號 ";
|
|
AddParam(cmdSelect, "計畫年度編號", 計畫年度編號);
|
|
}
|
|
cmdSelect.CommandText += " order by 計畫年度編號, 站別編號, 職務編號, 等級編號, A.學員編號";
|
|
return Select(cmdSelect);
|
|
}
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select效益追蹤名單(int 訓練課程編號, string 站別編號)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select A.學員編號, C.BPNME as 學員姓名, C.BPEML as 電子郵件, A.訓練課程編號,
|
|
A.報名狀態, A.報名日期, A.取消報名日期, A.出席狀況, A.請假事由, A.通知發送狀態,
|
|
B.顯示編號, B.訓練名稱, B.訓練日期, B.訓練日期迄, B.取消報名截止期限,
|
|
D.RAPYN AS 計畫年度編號,
|
|
'【' + (SELECT TOP 1 BSSCR FROM BCLS WHERE BSPAR='BCUSZON' AND BSCLS=SUBSTRING(E.BPPYN,LEN(E.BPPYN)-5,1)) + '】' + E.BPPYM AS 計畫年度名稱,
|
|
D.RABRH AS 站別編號, F.BSRMK AS 站別名稱,
|
|
D.RAJNO AS 職務編號, G.BPJME AS 職務名稱,
|
|
D.RACNO AS 等級編號, H.BPCME AS 等級名稱,
|
|
(select count(1) from 滿意度調查 I where A.訓練課程編號=I.訓練課程企劃編號 and A.學員編號=I.學員編號) as 填寫數,
|
|
J.心得報告編號, J.上架 as 心得上架
|
|
from 課程報名 A
|
|
inner join 訓練課程企劃 B on A.訓練課程編號 = B.編號
|
|
inner join BPSNView C on A.學員編號=C.BPENO
|
|
left outer join RASN D on C.BPENO = D.RANUM and D.RAITM = (SELECT MAX(RASN.RAITM) FROM RASN WHERE RASN.RANUM=D.RANUM)
|
|
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 心得報告 J on A.訓練課程編號=J.訓練課程企劃編號 and A.學員編號=J.學員編號
|
|
where A.訓練課程編號 = @訓練課程編號
|
|
and (A.報名狀態 = 'Y' or A.報名狀態 = 'R')
|
|
and (SELECT COUNT(1) FROM 學員出席 K WHERE A.訓練課程編號=K.訓練課程編號 and A.學員編號=K.學員編號 AND K.出席狀況='Y') > 0";
|
|
AddParam(cmdSelect, "訓練課程編號", 訓練課程編號);
|
|
|
|
if (站別編號 != null && 站別編號.Length > 0)
|
|
{
|
|
string[] IDs = 站別編號.Split('|');
|
|
if (IDs.Length > 1)
|
|
{
|
|
// 部門
|
|
if (IDs[0] == "A")
|
|
{
|
|
string str組織 = "";
|
|
|
|
DAC_組織圖 Dao組織圖 = new DAC_組織圖(conn);
|
|
組織圖List d = Dao組織圖.Select子樹(1, DAC.GetInt32(IDs[1]));
|
|
foreach (組織圖 item in d)
|
|
{
|
|
str組織 += String.Format("{0},", item.組織ID);
|
|
}
|
|
if (str組織.Length > 0)
|
|
{
|
|
str組織 = str組織.Remove(str組織.Length - 1);
|
|
cmdSelect.CommandText += " and exists (select 1 from 組織人員 X where A.學員編號=X.人員ID and X.組織ID IN (" + str組織 + ") ) ";
|
|
}
|
|
}
|
|
// 站別
|
|
else
|
|
{
|
|
cmdSelect.CommandText += " and D.RABRH = @站別編號 ";
|
|
AddParam(cmdSelect, "站別編號", IDs[1]);
|
|
}
|
|
}
|
|
}
|
|
|
|
// cmdSelect.CommandText += " order by 計畫年度編號, 站別編號, 職務編號, 等級編號, A.學員編號";
|
|
cmdSelect.CommandText += " order by A.學員編號";
|
|
|
|
return Select(cmdSelect);
|
|
}
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select選課狀況(int 訓練課程編號, bool 必修)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select A.學員編號, C.BPNME as 學員姓名, C.BPEML as 電子郵件, A.訓練課程編號,
|
|
A.報名狀態, A.報名日期, A.取消報名日期, A.出席狀況, A.請假事由, A.通知發送狀態,
|
|
B.顯示編號, B.訓練名稱, B.訓練日期, B.訓練日期迄, B.取消報名截止期限,
|
|
D.RAPYN AS 計畫年度編號,
|
|
'【' + (SELECT TOP 1 BSSCR FROM BCLS WHERE BSPAR='BCUSZON' AND BSCLS=SUBSTRING(E.BPPYN,LEN(E.BPPYN)-5,1)) + '】' + E.BPPYM AS 計畫年度名稱,
|
|
D.RABRH AS 站別編號, F.BSRMK AS 站別名稱,
|
|
D.RAJNO AS 職務編號, G.BPJME AS 職務名稱,
|
|
D.RACNO AS 等級編號, H.BPCME AS 等級名稱
|
|
from 課程報名 A
|
|
inner join 訓練課程企劃 B on A.訓練課程編號 = B.編號
|
|
inner join BPSNView C on A.學員編號=C.BPENO
|
|
left outer join RASN D on C.BPENO = D.RANUM and D.RAITM = (SELECT MAX(RASN.RAITM) FROM RASN WHERE RASN.RANUM=D.RANUM)
|
|
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 心得報告 J on A.訓練課程編號=J.訓練課程企劃編號 and A.學員編號=J.學員編號
|
|
where A.訓練課程編號 = @訓練課程編號";
|
|
if (必修)
|
|
cmdSelect.CommandText += " and A.報名狀態 = 'R' ";
|
|
else
|
|
cmdSelect.CommandText += " and A.報名狀態 <> 'R' AND A.報名狀態 <> 'N' ";
|
|
AddParam(cmdSelect, "訓練課程編號", 訓練課程編號);
|
|
|
|
cmdSelect.CommandText += " order by A.學員編號";
|
|
return Select(cmdSelect);
|
|
}
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select確定報名學員計畫(int 訓練課程編號)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select distinct 計畫年度編號, ISNULL('【' + I.BSSCR + '】', '') + 計畫年度名稱 as 計畫年度名稱 from
|
|
(select A.學員編號, C.BPNME as 學員姓名, C.BPEML as 電子郵件, A.訓練課程編號,
|
|
A.報名狀態, A.報名日期, A.取消報名日期, A.出席狀況, A.請假事由, A.通知發送狀態,
|
|
B.顯示編號, B.訓練名稱, B.訓練日期, B.訓練日期迄, B.取消報名截止期限,
|
|
D.RAPYN AS 計畫年度編號, E.BPPYM AS 計畫年度名稱,
|
|
D.RABRH AS 站別編號, F.BSRMK AS 站別名稱,
|
|
D.RAJNO AS 職務編號, G.BPJME AS 職務名稱,
|
|
D.RACNO AS 等級編號, H.BPCME AS 等級名稱
|
|
from 課程報名 A
|
|
inner join 訓練課程企劃 B on A.訓練課程編號 = B.編號
|
|
inner join BPSNView C on A.學員編號=C.BPENO
|
|
left outer join RASN D on C.BPENO = D.RANUM and D.RAITM = (SELECT MAX(RASN.RAITM) FROM RASN WHERE RASN.RANUM=D.RANUM)
|
|
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
|
|
where A.訓練課程編號 = @訓練課程編號
|
|
and (A.報名狀態 = 'Y' or A.報名狀態 = 'R')) as tmp1
|
|
left outer join BCLS I on I.BSCLS = SUBSTRING(計畫年度編號, LEN(計畫年度編號)-5,1) and I.BSPAR = 'BCUSZON'
|
|
order by 計畫年度編號";
|
|
AddParam(cmdSelect, "訓練課程編號", 訓練課程編號);
|
|
return Select(cmdSelect);
|
|
}
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select必修學員(int 訓練課程編號)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select A.學員編號, A.學員編號 + ' ' + B.BPNME as 學員姓名
|
|
from 課程報名 A
|
|
inner join BPSNView B on A.學員編號=B.BPENO
|
|
where A.訓練課程編號 = @訓練課程編號
|
|
and A.報名狀態 = 'R'
|
|
order by A.學員編號";
|
|
AddParam(cmdSelect, "訓練課程編號", 訓練課程編號);
|
|
return Select(cmdSelect);
|
|
}
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable Select選修學員(int 訓練課程編號)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select A.學員編號, A.學員編號 + ' ' + B.BPNME as 學員姓名
|
|
from 課程報名 A
|
|
inner join BPSNView B on A.學員編號=B.BPENO
|
|
where A.訓練課程編號 = @訓練課程編號
|
|
and (A.報名狀態 = 'S' or A.報名狀態 = 'Y')
|
|
order by A.學員編號";
|
|
AddParam(cmdSelect, "訓練課程編號", 訓練課程編號);
|
|
return Select(cmdSelect);
|
|
}
|
|
|
|
public int Get報名人數(int 訓練課程編號)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select count(1) from 課程報名
|
|
where 訓練課程編號 = @訓練課程編號
|
|
and (報名狀態 = 'Y' or 報名狀態 = 'R')";
|
|
AddParam(cmdSelect, "訓練課程編號", 訓練課程編號);
|
|
int i;
|
|
ExecuteScalar(cmdSelect, out i);
|
|
return i;
|
|
}
|
|
|
|
/*
|
|
public int Get出席人數(int 訓練課程編號)
|
|
{
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"select count(1) from 課程報名
|
|
where 訓練課程編號 = @訓練課程編號
|
|
and (報名狀態 = 'Y' or 報名狀態 = 'R') and 出席狀況='Y'";
|
|
AddParam(cmdSelect, "訓練課程編號", 訓練課程編號);
|
|
int i;
|
|
ExecuteScalar(cmdSelect, out i);
|
|
return i;
|
|
}
|
|
*/
|
|
|
|
public int Get出席人數(int 訓練課程編號)
|
|
{
|
|
return new DAC_學員出席(conn).Get出席人次(訓練課程編號);
|
|
}
|
|
|
|
public decimal Get出席率(int 訓練課程編號)
|
|
{
|
|
// 出席率 = 出席人次 / 報名人數 * 課程天數
|
|
|
|
if (訓練課程編號 == 0)
|
|
return 0;
|
|
|
|
int 出席人次 = new DAC_學員出席(conn).Get出席人次(訓練課程編號);
|
|
int 報名人數 = Get報名人數(訓練課程編號);
|
|
int 課程天數 = new DAC_訓練課程內容(conn).Select訓練日期List(訓練課程編號).Count;
|
|
if (課程天數 == 0 || 報名人數 == 0)
|
|
return 0;
|
|
else
|
|
return (decimal)出席人次 / ((decimal)報名人數 * (decimal)課程天數) * 100m;
|
|
|
|
/*
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText =
|
|
@"declare @totalcount int
|
|
select @totalcount = count(1) from 課程報名
|
|
where 訓練課程編號 = @訓練課程編號
|
|
and (報名狀態 = 'Y' or 報名狀態 = 'R')
|
|
|
|
if @totalcount > 0
|
|
select
|
|
cast((select count(1) from 課程報名
|
|
where 訓練課程編號 = @訓練課程編號
|
|
and (報名狀態 = 'Y' or 報名狀態 = 'R') and 出席狀況='Y') as float)
|
|
/
|
|
cast((select count(1) from 課程報名
|
|
where 訓練課程編號 = @訓練課程編號
|
|
and (報名狀態 = 'Y' or 報名狀態 = 'R')) as float) * 100
|
|
else
|
|
select 0";
|
|
AddParam(cmdSelect, "訓練課程編號", 訓練課程編號);
|
|
decimal i;
|
|
ExecuteScalar(cmdSelect, out i);
|
|
return i;
|
|
*/
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得某學員是否報名某課程
|
|
/// </summary>
|
|
public string Get報名狀態(string 學員編號, int 訓練課程編號)
|
|
{
|
|
課程報名List d = SelectOne(學員編號, 訓練課程編號);
|
|
if (d.Count == 0)
|
|
return "";
|
|
return d[0].報名狀態;
|
|
}
|
|
|
|
public void 必修(string 學員編號, int 訓練課程編號, string userId)
|
|
{
|
|
課程報名List values = SelectOne(學員編號, 訓練課程編號);
|
|
if (values.Count == 0)
|
|
{
|
|
課程報名 newValue = new 課程報名();
|
|
newValue.學員編號 = 學員編號;
|
|
newValue.訓練課程編號 = 訓練課程編號;
|
|
newValue.報名日期 = DateTime.UtcNow.AddHours(8);
|
|
newValue.報名狀態 = "R";
|
|
newValue.DB_CRUSR = userId;
|
|
InsertOne(newValue);
|
|
}
|
|
else
|
|
{
|
|
// 已經是必修或是選修，不管他
|
|
if (values[0].報名狀態 == "R" || values[0].報名狀態 == "Y" || values[0].報名狀態 == "S")
|
|
return;
|
|
|
|
values[0].報名日期 = DateTime.UtcNow.AddHours(8);
|
|
values[0].取消報名日期 = new DateTime();
|
|
values[0].報名狀態 = "R";
|
|
values[0].DB_TRUSR = userId;
|
|
UpdateOne(values[0]);
|
|
}
|
|
}
|
|
|
|
public void 取消必修(string 學員編號, int 訓練課程編號, string userId)
|
|
{
|
|
課程報名List values = SelectOne(學員編號, 訓練課程編號);
|
|
if (values.Count > 0 && values[0].報名狀態 == "R")
|
|
{
|
|
values[0].報名日期 = DateTime.UtcNow.AddHours(8);
|
|
values[0].取消報名日期 = new DateTime();
|
|
values[0].報名狀態 = "N";
|
|
values[0].DB_TRUSR = userId;
|
|
UpdateOne(values[0]);
|
|
}
|
|
}
|
|
|
|
public void 選修(string 學員編號, int 訓練課程編號, string userId)
|
|
{
|
|
課程報名List values = SelectOne(學員編號, 訓練課程編號);
|
|
if (values.Count == 0)
|
|
{
|
|
課程報名 newValue = new 課程報名();
|
|
newValue.學員編號 = 學員編號;
|
|
newValue.訓練課程編號 = 訓練課程編號;
|
|
newValue.報名日期 = DateTime.UtcNow.AddHours(8);
|
|
newValue.報名狀態 = "S";
|
|
newValue.DB_CRUSR = userId;
|
|
InsertOne(newValue);
|
|
}
|
|
else
|
|
{
|
|
// 已經是必修或是選修，不管他
|
|
if (values[0].報名狀態 == "R" || values[0].報名狀態 == "Y" || values[0].報名狀態 == "S")
|
|
return;
|
|
|
|
values[0].報名日期 = DateTime.UtcNow.AddHours(8);
|
|
values[0].取消報名日期 = new DateTime();
|
|
values[0].報名狀態 = "S";
|
|
values[0].DB_TRUSR = userId;
|
|
UpdateOne(values[0]);
|
|
}
|
|
}
|
|
|
|
public void 取消選修(string 學員編號, int 訓練課程編號, string userId)
|
|
{
|
|
課程報名List values = SelectOne(學員編號, 訓練課程編號);
|
|
if (values.Count > 0 && (values[0].報名狀態 == "S" || values[0].報名狀態 == "Y"))
|
|
{
|
|
values[0].報名日期 = DateTime.UtcNow.AddHours(8);
|
|
values[0].取消報名日期 = new DateTime();
|
|
values[0].報名狀態 = "N";
|
|
values[0].DB_TRUSR = userId;
|
|
UpdateOne(values[0]);
|
|
}
|
|
}
|
|
|
|
public void 選修報名(string 學員編號, int 訓練課程編號, string userId)
|
|
{
|
|
課程報名List values = SelectOne(學員編號, 訓練課程編號);
|
|
if (values.Count > 0 && values[0].報名狀態 == "S")
|
|
{
|
|
values[0].報名日期 = DateTime.UtcNow.AddHours(8);
|
|
values[0].取消報名日期 = new DateTime();
|
|
values[0].報名狀態 = "Y";
|
|
values[0].DB_TRUSR = userId;
|
|
UpdateOne(values[0]);
|
|
}
|
|
}
|
|
|
|
public void 取消報名(string 學員編號, int 訓練課程編號, string userId)
|
|
{
|
|
課程報名List values = SelectOne(學員編號, 訓練課程編號);
|
|
if (values.Count > 0 && values[0].報名狀態 == "Y")
|
|
{
|
|
values[0].報名日期 = new DateTime();
|
|
values[0].取消報名日期 = DateTime.UtcNow.AddHours(8);
|
|
values[0].報名狀態 = "S";
|
|
values[0].DB_TRUSR = userId;
|
|
UpdateOne(values[0]);
|
|
}
|
|
}
|
|
|
|
public void 標記已發送學員通知(string 學員編號, int 訓練課程編號, string UserId)
|
|
{
|
|
課程報名List values = SelectOne(學員編號, 訓練課程編號);
|
|
if (values.Count > 0)
|
|
{
|
|
values[0].通知發送狀態 = "Y";
|
|
values[0].DB_TRUSR = UserId;
|
|
UpdateOne(values[0]);
|
|
}
|
|
}
|
|
|
|
public void Update出席狀況(string 學員編號, int 訓練課程編號, string 出席狀況, string 請假事由, string UserId)
|
|
{
|
|
課程報名List values = SelectOne(學員編號, 訓練課程編號);
|
|
if (values.Count > 0)
|
|
{
|
|
values[0].出席狀況 = 出席狀況;
|
|
values[0].請假事由 = 請假事由;
|
|
values[0].DB_TRUSR = UserId;
|
|
UpdateOne(values[0]);
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
///<summary>
|
|
/// 課程報名 資料集合類別
|
|
///</summary>
|
|
public partial class 課程報名List : List<課程報名>
|
|
{
|
|
// 有另外加上去的屬性及方法，請加在這裡
|
|
}
|
|
|
|
///<summary>
|
|
/// 課程報名 資料紀錄類別
|
|
///</summary>
|
|
public partial class 課程報名
|
|
{
|
|
// 有另外加上去的屬性及方法，請加在這裡
|
|
}
|
|
}
|