788 lines
30 KiB
C#
788 lines
30 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Configuration;
|
|
using System.Data.Common;
|
|
using System.Collections;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using Wellan.Data;
|
|
using Wellan.Common;
|
|
using NPOI.SS.Formula.Functions;
|
|
using System.IdentityModel.Claims;
|
|
using Microsoft.Win32;
|
|
|
|
/// <summary>
|
|
/// 年度特休
|
|
/// </summary>
|
|
[DataObject]
|
|
public class RyerDAC : Wellan.Data.WDAC
|
|
{
|
|
public RyerDAC()
|
|
{
|
|
}
|
|
|
|
public RyerDAC(string connectionConfig, string connectionConfigName)
|
|
: this()
|
|
{
|
|
this._connectionConfig = connectionConfig;
|
|
this._connectionConfigName = connectionConfigName;
|
|
}
|
|
|
|
public RyerDAC(WQuery query)
|
|
{
|
|
this._query = query;
|
|
}
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Select)]
|
|
public DataTable SelectProject(int RYYERFrom, int RYYERTo, string RAPYN, string RANUM)
|
|
{
|
|
string select = @"SELECT A.RANUM AS RYENO
|
|
, A.BPYER AS RYYER
|
|
, B.BPNME
|
|
, B.BPSDY
|
|
, B.BPEDY
|
|
, C.RYYST
|
|
, '' AS RYYST_H
|
|
, '' AS RYYST_M
|
|
, ISNULL(C.RYYXT, 0) AS RYYXT
|
|
, '' AS RYYXT_D
|
|
, ISNULL(C.RYTOT, 0) / 60 AS RYTOT
|
|
, ISNULL(C.RYH01, 0) / 60 AS RYH01
|
|
, ISNULL(C.RYH02, 0) / 60 AS RYH02
|
|
, ISNULL(C.RYH03, 0) / 60 AS RYH03
|
|
, ISNULL(C.RYH04, 0) / 60 AS RYH04
|
|
, ISNULL(C.RYH05, 0) / 60 AS RYH05
|
|
, ISNULL(C.RYH06, 0) / 60 AS RYH06
|
|
, ISNULL(C.RYH07, 0) / 60 AS RYH07
|
|
, ISNULL(C.RYH08, 0) / 60 AS RYH08
|
|
, ISNULL(C.RYH09, 0) / 60 AS RYH09
|
|
, ISNULL(C.RYH10, 0) / 60 AS RYH10
|
|
, ISNULL(C.RYH11, 0) / 60 AS RYH11
|
|
, ISNULL(C.RYH12, 0) / 60 AS RYH12
|
|
, ISNULL(C.RYD01, 1) AS RYD01
|
|
, ISNULL(C.RYD02, 1) AS RYD02
|
|
, ISNULL(C.RYD03, 1) AS RYD03
|
|
, ISNULL(C.RYD04, 1) AS RYD04
|
|
, ISNULL(C.RYD05, 1) AS RYD05
|
|
, ISNULL(C.RYD06, 1) AS RYD06
|
|
, ISNULL(C.RYD07, 1) AS RYD07
|
|
, ISNULL(C.RYD08, 1) AS RYD08
|
|
, ISNULL(C.RYD09, 1) AS RYD09
|
|
, ISNULL(C.RYD10, 1) AS RYD10
|
|
, ISNULL(C.RYD11, 1) AS RYD11
|
|
, ISNULL(C.RYD12, 1) AS RYD12
|
|
, '' AS RYVLD
|
|
, '' AS RYUST
|
|
, '' AS RYLFT
|
|
, '' AS RYPLF
|
|
, ISNULL(C.RYSLY, 0) AS RYSLY
|
|
, ISNULL(C.RYNXT, 0) AS RYNXT
|
|
, '' AS RYSLY_H
|
|
, '' AS RYSLY_M
|
|
, '' AS RYNXT_H
|
|
, '' AS RYNXT_M
|
|
, C.RYRMK";
|
|
|
|
if (RAPYN == "103JYB00001" || RAPYN == "103TYB00001")
|
|
{
|
|
RYYERTo = RYYERFrom;
|
|
}
|
|
|
|
DbCommand cmd = Query.NewCommand();
|
|
if (RYYERFrom != RYYERTo)
|
|
{
|
|
cmd.CommandText = string.Format(
|
|
@"{0}
|
|
FROM (SELECT DISTINCT RASN.RANUM, BPAA.BPYER + @CY11 BPYER
|
|
FROM RASN
|
|
JOIN BPAA on RASN.RAPYN = BPAA.BPPYN
|
|
WHERE (BPYER + @CY11 >= @RYYER_FROM AND BPYER + @CY11 <= @RYYER_TO)
|
|
AND (RASN.RAPYN = @RAPYN OR @RAPYN = '') -- 跨年度查詢忽略專案條件
|
|
AND (RASN.RANUM = @RANUM OR @RANUM = '')
|
|
) A
|
|
JOIN BPSNView B ON A.RANUM=B.BPENO -- AND (B.BPEDY IS NULL OR YEAR(B.BPEDY) = BPYER)
|
|
LEFT JOIN RYER C ON A.RANUM=C.RYENO AND A.BPYER = C.RYYER
|
|
ORDER BY (CASE WHEN BPEDY IS NULL THEN 0 ELSE 1 END), RYENO, BPYER"
|
|
, select);
|
|
}
|
|
else
|
|
{
|
|
cmd.CommandText = string.Format(
|
|
@"{0}
|
|
FROM (SELECT DISTINCT RASN.RANUM, @RYYER_FROM BPYER
|
|
FROM RASN
|
|
WHERE (RASN.RAPYN = @RAPYN OR @RAPYN = '')
|
|
AND (RASN.RANUM = @RANUM OR @RANUM = '')
|
|
-- AND (RASN.RAEDT IS NULL OR YEAR(RASN.RAEDT) = @RYYER_FROM) -- 加上專案結束日為空或年度=查詢年度
|
|
) A
|
|
JOIN BPSNView B ON A.RANUM=B.BPENO -- AND (B.BPEDY IS NULL OR YEAR(B.BPEDY) = BPYER)
|
|
LEFT JOIN RYER C ON A.RANUM=C.RYENO AND A.BPYER = C.RYYER
|
|
ORDER BY (CASE WHEN BPEDY IS NULL THEN 0 ELSE 1 END), RYENO, BPYER"
|
|
, select);
|
|
}
|
|
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "CY11", WGuard1.CY11);
|
|
Query.AddParam(cmd, "RANUM", RANUM);
|
|
Query.AddParam(cmd, "RAPYN", RAPYN);
|
|
Query.AddParam(cmd, "RYYER_FROM", RYYERFrom);
|
|
Query.AddParam(cmd, "RYYER_TO", RYYERTo);
|
|
DataTable result = Query.Select(cmd);
|
|
|
|
var nowtime = DateTime.UtcNow.AddHours(8);
|
|
int month = nowtime.Month;
|
|
int day = nowtime.Day;
|
|
foreach (DataRow r in result.Rows)
|
|
{
|
|
var ryyer = WDAC.GetInt32Value(r["RYYER"]);
|
|
var ryeno = WDAC.GetStringValue(r["RYENO"]);
|
|
if (ryyer < nowtime.Year)
|
|
{
|
|
month = 12;
|
|
day = 31;
|
|
}
|
|
|
|
r.BeginEdit();
|
|
int 年初特休 = Get年初特休(ryeno, ryyer);
|
|
int 可用特休 = Get可用特休(ryeno, ryyer, month, day);
|
|
int 至今生效特休 = Get至今生效特休(ryeno, ryyer, month, day);
|
|
int 已休時數 = 年初特休 + 至今生效特休 - 可用特休;
|
|
int 上年未休 = Get可用特休(ryeno, ryyer - 1, 12, 31);
|
|
|
|
r["RYYST_H"] = String.Format("{0}", GetInt32Value(GetInt32Value(r["RYYST"]) / 60));
|
|
r["RYYST_M"] = String.Format("{0:00}", GetInt32Value(GetInt32Value(r["RYYST"]) % 60));
|
|
r["RYYXT_D"] = String.Format("{0}:{1:00}", GetInt32Value(GetInt32Value(r["RYYXT"]) / 60), GetInt32Value(r["RYYXT"]) % 60);
|
|
r["RYVLD"] = String.Format("{0}:{1:00}", GetInt32Value(至今生效特休 / 60), 至今生效特休 % 60);
|
|
r["RYUST"] = String.Format("{0}:{1:00}", GetInt32Value(已休時數 / 60), 已休時數 % 60);
|
|
r["RYLFT"] = String.Format("{0}:{1:00}", GetInt32Value(可用特休 / 60), 可用特休 % 60);
|
|
r["RYPLF"] = String.Format("{0}:{1:00}", GetInt32Value(上年未休 / 60), 上年未休 % 60);
|
|
r["RYSLY_H"] = String.Format("{0}", GetInt32Value(GetInt32Value(r["RYSLY"]) / 60));
|
|
r["RYSLY_M"] = String.Format("{0:00}", GetInt32Value(r["RYSLY"]) % 60);
|
|
r["RYNXT_H"] = String.Format("{0}", GetInt32Value(GetInt32Value(r["RYNXT"]) / 60));
|
|
r["RYNXT_M"] = String.Format("{0:00}", GetInt32Value(r["RYNXT"]) % 60);
|
|
r.EndEdit();
|
|
r.AcceptChanges();
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得某人某年還有多少特休
|
|
/// </summary>
|
|
/// <param name="RYENO">員工編號</param>
|
|
/// <param name="year">年</param>
|
|
/// <param name="month">月</param>
|
|
/// <param name="day">日</param>
|
|
/// <param name="addYearBegin">加入年初已生效時數</param>
|
|
/// <param name="substrateOffHours">減去本年已休時數</param>
|
|
public int DoGet可用特休(string RYENO, int year, int month, int day, bool addYearBegin, bool substrateOffHours)
|
|
{
|
|
// 取得總時數
|
|
if (month < 1) month = 1;
|
|
if (month > 12) month = 12;
|
|
List<string> monthsql = new List<string>();
|
|
if (addYearBegin)
|
|
{
|
|
monthsql.Add("ISNULL(RYYST, 0)"); // 年初生效時數
|
|
monthsql.Add("ISNULL(RYYXT, 0)"); // 上年遞延時數
|
|
}
|
|
for (int i = 1; i < month; i++)
|
|
{
|
|
monthsql.Add(string.Format("ISNULL(RYH{0:00}, 0)", i));
|
|
}
|
|
monthsql.Add(string.Format("ISNULL(CASE WHEN @RYDAY >= ISNULL(RYD{0:00}, 1) THEN RYH{0:00} ELSE 0 END, 0)", month));
|
|
|
|
|
|
DbCommand cmd = Query.NewCommand();
|
|
cmd.CommandText =
|
|
string.Format(@"SELECT {0}
|
|
FROM RYER
|
|
WHERE RYYER=@RYYER
|
|
AND RYENO=@RYENO", string.Join("+", monthsql.ToArray()));
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "RYYER", year);
|
|
Query.AddParam(cmd, "RYENO", RYENO);
|
|
Query.AddParam(cmd, "RYDAY", day);
|
|
int 總時數 = GetInt32Value(Query.ExecuteScalar(cmd));
|
|
|
|
// 取得已休時數
|
|
int 已休時數 = 0;
|
|
if (substrateOffHours)
|
|
{
|
|
cmd.CommandText =
|
|
@"SELECT ISNULL(SUM(RPTCT), 0)
|
|
FROM RPAR
|
|
WHERE RPNUM = @RYENO
|
|
AND RPTYP = '01'
|
|
AND YEAR(RPRDT) = @RYYER
|
|
AND YEAR(RPEDT) = @RYYER
|
|
AND (MONTH(RPRDT) < @RYMON or MONTH(RPRDT) = @RYMON AND DAY(RPRDT) <= @RYDAY)
|
|
AND (RPAPN IS NULL OR RPAPN <> 'D' AND (RPAPP IS NULL OR RPAPP <> 'N'))";
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "RYENO", RYENO);
|
|
Query.AddParam(cmd, "RYYER", year);
|
|
Query.AddParam(cmd, "RYMON", month);
|
|
Query.AddParam(cmd, "RYDAY", day);
|
|
已休時數 += GetInt32Value(Query.ExecuteScalar(cmd));
|
|
}
|
|
|
|
return 總時數 - 已休時數;
|
|
}
|
|
|
|
public int Get可用特休(string RYENO, int year, int month, int day)
|
|
{
|
|
return DoGet可用特休(RYENO, year, month, day, true, true);
|
|
}
|
|
public int Get至今生效特休(string RYENO, int year, int month, int day)
|
|
{
|
|
return DoGet可用特休(RYENO, year, month, day, false, false);
|
|
}
|
|
public int Get年初特休(string RYENO, int year)
|
|
{
|
|
return DoGet可用特休(RYENO, year, 1, 0, true, false);
|
|
}
|
|
|
|
public int Get上年結轉(string RYENO, int year)
|
|
{
|
|
DbCommand cmd = Query.NewCommand();
|
|
cmd.CommandText =
|
|
@"SELECT RYNXT
|
|
FROM RYER
|
|
WHERE RYYER=@RYYER
|
|
AND RYENO=@RYENO";
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "RYYER", year - 1);
|
|
Query.AddParam(cmd, "RYENO", RYENO);
|
|
return GetInt32Value(Query.ExecuteScalar(cmd));
|
|
}
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Update)]
|
|
public void UpdateOne(
|
|
string old_ryeno
|
|
, int old_ryyer
|
|
, int rytot
|
|
, string trusr
|
|
, int ryyst
|
|
, int ryh01
|
|
, int ryh02
|
|
, int ryh03
|
|
, int ryh04
|
|
, int ryh05
|
|
, int ryh06
|
|
, int ryh07
|
|
, int ryh08
|
|
, int ryh09
|
|
, int ryh10
|
|
, int ryh11
|
|
, int ryh12
|
|
, int ryd01
|
|
, int ryd02
|
|
, int ryd03
|
|
, int ryd04
|
|
, int ryd05
|
|
, int ryd06
|
|
, int ryd07
|
|
, int ryd08
|
|
, int ryd09
|
|
, int ryd10
|
|
, int ryd11
|
|
, int ryd12
|
|
, int rysly
|
|
, int rynxt
|
|
, string ryrmk
|
|
)
|
|
{
|
|
DbCommand cmd = Query.NewCommand();
|
|
|
|
if (!CheckExists(old_ryeno, old_ryyer))
|
|
{
|
|
cmd.CommandText =
|
|
@"INSERT INTO RYER
|
|
(TRDAT, TRMOD, TRUSR, RYENO, RYYER, RYTOT, RYUST, RYYST,
|
|
RYH01, RYH02, RYH03, RYH04, RYH05, RYH06, RYH07, RYH08, RYH09, RYH10, RYH11, RYH12,
|
|
RYD01, RYD02, RYD03, RYD04, RYD05, RYD06, RYD07, RYD08, RYD09, RYD10, RYD11, RYD12,
|
|
RYSLY, RYNXT, RYRMK
|
|
)
|
|
VALUES
|
|
(GETDATE(), 'INSERT', @TRUSR, @RYENO, @RYYER, @RYTOT, 0, @RYYST,
|
|
@RYH01, @RYH02, @RYH03, @RYH04, @RYH05, @RYH06, @RYH07, @RYH08, @RYH09, @RYH10, @RYH11, @RYH12,
|
|
@RYD01, @RYD02, @RYD03, @RYD04, @RYD05, @RYD06, @RYD07, @RYD08, @RYD09, @RYD10, @RYD11, @RYD12,
|
|
@RYSLY, @RYNXT, @RYRMK
|
|
)";
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "TRUSR", trusr);
|
|
Query.AddParam(cmd, "RYENO", old_ryeno);
|
|
Query.AddParam(cmd, "RYYER", old_ryyer);
|
|
Query.AddParam(cmd, "RYTOT", rytot);
|
|
Query.AddParam(cmd, "RYYST", ryyst);
|
|
Query.AddParam(cmd, "RYH01", ryh01);
|
|
Query.AddParam(cmd, "RYH02", ryh02);
|
|
Query.AddParam(cmd, "RYH03", ryh03);
|
|
Query.AddParam(cmd, "RYH04", ryh04);
|
|
Query.AddParam(cmd, "RYH05", ryh05);
|
|
Query.AddParam(cmd, "RYH06", ryh06);
|
|
Query.AddParam(cmd, "RYH07", ryh07);
|
|
Query.AddParam(cmd, "RYH08", ryh08);
|
|
Query.AddParam(cmd, "RYH09", ryh09);
|
|
Query.AddParam(cmd, "RYH10", ryh10);
|
|
Query.AddParam(cmd, "RYH11", ryh11);
|
|
Query.AddParam(cmd, "RYH12", ryh12);
|
|
Query.AddParam(cmd, "RYD01", ryd01);
|
|
Query.AddParam(cmd, "RYD02", ryd02);
|
|
Query.AddParam(cmd, "RYD03", ryd03);
|
|
Query.AddParam(cmd, "RYD04", ryd04);
|
|
Query.AddParam(cmd, "RYD05", ryd05);
|
|
Query.AddParam(cmd, "RYD06", ryd06);
|
|
Query.AddParam(cmd, "RYD07", ryd07);
|
|
Query.AddParam(cmd, "RYD08", ryd08);
|
|
Query.AddParam(cmd, "RYD09", ryd09);
|
|
Query.AddParam(cmd, "RYD10", ryd10);
|
|
Query.AddParam(cmd, "RYD11", ryd11);
|
|
Query.AddParam(cmd, "RYD12", ryd12);
|
|
Query.AddParam(cmd, "RYSLY", rysly);
|
|
Query.AddParam(cmd, "RYNXT", rynxt);
|
|
Query.AddParam(cmd, "RYRMK", ryrmk);
|
|
Query.ExecuteNonQuery(cmd);
|
|
}
|
|
else
|
|
{
|
|
cmd.CommandText =
|
|
@"UPDATE RYER
|
|
SET TRDAT = GETDATE() ,
|
|
TRMOD = 'UPDATE' ,
|
|
TRUSR = @TRUSR ,
|
|
RYTOT = @RYTOT ,
|
|
RYYST = @RYYST ,
|
|
RYH01 = @RYH01 ,
|
|
RYH02 = @RYH02 ,
|
|
RYH03 = @RYH03 ,
|
|
RYH04 = @RYH04 ,
|
|
RYH05 = @RYH05 ,
|
|
RYH06 = @RYH06 ,
|
|
RYH07 = @RYH07 ,
|
|
RYH08 = @RYH08 ,
|
|
RYH09 = @RYH09 ,
|
|
RYH10 = @RYH10 ,
|
|
RYH11 = @RYH11 ,
|
|
RYH12 = @RYH12 ,
|
|
RYD01 = @RYD01 ,
|
|
RYD02 = @RYD02 ,
|
|
RYD03 = @RYD03 ,
|
|
RYD04 = @RYD04 ,
|
|
RYD05 = @RYD05 ,
|
|
RYD06 = @RYD06 ,
|
|
RYD07 = @RYD07 ,
|
|
RYD08 = @RYD08 ,
|
|
RYD09 = @RYD09 ,
|
|
RYD10 = @RYD10 ,
|
|
RYD11 = @RYD11 ,
|
|
RYD12 = @RYD12 ,
|
|
RYSLY = @RYSLY ,
|
|
RYNXT = @RYNXT ,
|
|
RYRMK = @RYRMK
|
|
WHERE RYENO = @OLD_RYENO
|
|
AND RYYER = @OLD_RYYER";
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "TRUSR", trusr);
|
|
Query.AddParam(cmd, "RYTOT", rytot);
|
|
Query.AddParam(cmd, "RYYST", ryyst);
|
|
Query.AddParam(cmd, "RYH01", ryh01);
|
|
Query.AddParam(cmd, "RYH02", ryh02);
|
|
Query.AddParam(cmd, "RYH03", ryh03);
|
|
Query.AddParam(cmd, "RYH04", ryh04);
|
|
Query.AddParam(cmd, "RYH05", ryh05);
|
|
Query.AddParam(cmd, "RYH06", ryh06);
|
|
Query.AddParam(cmd, "RYH07", ryh07);
|
|
Query.AddParam(cmd, "RYH08", ryh08);
|
|
Query.AddParam(cmd, "RYH09", ryh09);
|
|
Query.AddParam(cmd, "RYH10", ryh10);
|
|
Query.AddParam(cmd, "RYH11", ryh11);
|
|
Query.AddParam(cmd, "RYH12", ryh12);
|
|
Query.AddParam(cmd, "RYD01", ryd01);
|
|
Query.AddParam(cmd, "RYD02", ryd02);
|
|
Query.AddParam(cmd, "RYD03", ryd03);
|
|
Query.AddParam(cmd, "RYD04", ryd04);
|
|
Query.AddParam(cmd, "RYD05", ryd05);
|
|
Query.AddParam(cmd, "RYD06", ryd06);
|
|
Query.AddParam(cmd, "RYD07", ryd07);
|
|
Query.AddParam(cmd, "RYD08", ryd08);
|
|
Query.AddParam(cmd, "RYD09", ryd09);
|
|
Query.AddParam(cmd, "RYD10", ryd10);
|
|
Query.AddParam(cmd, "RYD11", ryd11);
|
|
Query.AddParam(cmd, "RYD12", ryd12);
|
|
Query.AddParam(cmd, "RYSLY", rysly);
|
|
Query.AddParam(cmd, "RYNXT", rynxt);
|
|
Query.AddParam(cmd, "RYRMK", ryrmk);
|
|
Query.AddParam(cmd, "OLD_RYENO", old_ryeno);
|
|
Query.AddParam(cmd, "OLD_RYYER", old_ryyer);
|
|
Query.ExecuteNonQuery(cmd);
|
|
}
|
|
}
|
|
|
|
[DataObjectMethod(DataObjectMethodType.Delete)]
|
|
public void DeleteOne(string OLD_RYENO, int OLD_RYYER)
|
|
{
|
|
DbCommand cmd = Query.NewCommand();
|
|
|
|
cmd.CommandText =
|
|
@"UPDATE FROM RYER
|
|
WHERE RYENO = @RYENO
|
|
AND RYYER = @RYYER";
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "RYENO", OLD_RYENO);
|
|
Query.AddParam(cmd, "RYYER", OLD_RYYER);
|
|
Query.ExecuteNonQuery(cmd);
|
|
}
|
|
|
|
public bool CheckExists(string RYENO, int RYYER)
|
|
{
|
|
DbCommand cmd = Query.NewCommand();
|
|
|
|
cmd.CommandText =
|
|
@"SELECT COUNT(1) FROM RYER
|
|
WHERE RYENO = @RYENO
|
|
AND RYYER = @RYYER";
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "RYENO", RYENO);
|
|
Query.AddParam(cmd, "RYYER", RYYER);
|
|
return GetInt32Value(Query.ExecuteScalar(cmd)) > 0;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 沖特休
|
|
/// </summary>
|
|
/// <param name="RYENO">員工編號</param>
|
|
/// <param name="RYYER">年</param>
|
|
/// <param name="特休">特休分鐘數</param>
|
|
/// <param name="tran"></param>
|
|
public void 沖特休(string RYENO, int RYYER, int 特休, DbTransaction tran)
|
|
{
|
|
DbCommand cmd = Query.NewCommand();
|
|
cmd.CommandText =
|
|
@"UPDATE RYER
|
|
SET RYUST = ISNULL(RYUST, 0) + @RYUST
|
|
WHERE RYENO=@RYENO AND RYYER=@RYYER";
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "RYENO", RYENO);
|
|
Query.AddParam(cmd, "RYYER", RYYER);
|
|
Query.AddParam(cmd, "RYUST", 特休);
|
|
|
|
if (tran != null)
|
|
{
|
|
cmd.Connection = tran.Connection;
|
|
cmd.Transaction = tran;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
else
|
|
{
|
|
Query.ExecuteNonQuery(cmd);
|
|
}
|
|
}
|
|
|
|
public void 取消沖特休(DbTransaction tran, string _RPREN)
|
|
{
|
|
DbCommand cmd = Query.NewCommand();
|
|
cmd.CommandText = @"SELECT RPNUM, RPRDT, RPTCT FROM RPAR WHERE RPREN=@RPREN";
|
|
Query.AddParam(cmd, "RPREN", _RPREN);
|
|
DataTable t = Query.Select(cmd, tran);
|
|
|
|
if (t.Rows.Count > 0)
|
|
{
|
|
string 舊User = WDAC.GetStringValue(t.Rows[0]["RPNUM"]);
|
|
DateTime 舊日期 = WDAC.GetDateTimeValue(t.Rows[0]["RPRDT"]);
|
|
int 舊時數 = WDAC.GetInt32Value(t.Rows[0]["RPTCT"]);
|
|
|
|
沖特休(舊User, 舊日期.Year, -舊時數, tran);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 結算特休
|
|
/// </summary>
|
|
/// <param name="ryeno">員工編號</param>
|
|
/// <param name="ryyer">年度</param>
|
|
/// <param name="trusr">操作人員</param>
|
|
public string Final(
|
|
string ryeno
|
|
, int ryyer
|
|
, string trusr
|
|
)
|
|
{
|
|
var rasnDao = new RasnDAC(Query);
|
|
var rsajDao = new RsajDAC(Query);
|
|
var bpaaDao = new BpaaDAC(Query);
|
|
MWEB unmweb = new MWEB();
|
|
DbCommand cmd = Query.NewCommand();
|
|
|
|
// 取出今年
|
|
cmd.CommandText = @"select * from RYER where RYENO=@RYENO AND RYYER=@RYYER";
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "RYENO", ryeno);
|
|
Query.AddParam(cmd, "RYYER", ryyer);
|
|
var thisYear = Query.Select(cmd);
|
|
|
|
if (thisYear.Rows.Count <= 0)
|
|
{
|
|
throw new Exception("找不到可結算的資料");
|
|
}
|
|
|
|
var thisRecord = thisYear.Rows[0];
|
|
if (GetInt32Value(thisRecord["RYNXT"]) + GetInt32Value(thisRecord["RYSLY"]) != Get可用特休(ryeno, ryyer, 12, 31))
|
|
{
|
|
throw new Exception("折算薪資時數 + 遞延下年時數 應等於 本年未休時數");
|
|
}
|
|
|
|
// 計算下年時數
|
|
|
|
// 取得派駐專案
|
|
var project = rasnDao.SelectRecentByDate(ryeno, new DateTime(ryyer, 12, 01));
|
|
var rapyn = GetStringValue(project.Rows[0]["RAPYN"]);
|
|
// 取得派駐專案的年初特休計算方式
|
|
var alt = bpaaDao.GetYearBeginAnnualLeaveType(rapyn);
|
|
|
|
// 年初時數
|
|
var ryyst = 0;
|
|
if (alt == "S")
|
|
{
|
|
ryyst = GetInt32Value(thisRecord["RYYST"]);
|
|
for (int m = 1; m <= 12; m++)
|
|
{
|
|
var ryhmm = GetInt32Value(thisRecord[string.Format("RYH{0:D2}", m)]);
|
|
ryyst += ryhmm > 0 ? ryhmm : 0;
|
|
}
|
|
}
|
|
|
|
var ryyxt = GetInt32Value(thisRecord["RYNXT"]); // 遞延下年時數
|
|
var rysly = GetInt32Value(thisRecord["RYSLY"]); // 結算薪資時數
|
|
|
|
// 若未輸入離職日期
|
|
var bpsnDao = new BpsnDAC(Query);
|
|
var bpedy = bpsnDao.GetBPEDY(ryeno);
|
|
if (bpedy.Ticks == 0)
|
|
{
|
|
if (!CheckExists(ryeno, ryyer + 1))
|
|
{
|
|
cmd.CommandText =
|
|
@"INSERT INTO RYER
|
|
(TRDAT, TRMOD, TRUSR, RYENO, RYYER, RYTOT, RYUST, RYYST,
|
|
RYH01, RYH02, RYH03, RYH04, RYH05, RYH06, RYH07, RYH08, RYH09, RYH10, RYH11, RYH12,
|
|
RYD01, RYD02, RYD03, RYD04, RYD05, RYD06, RYD07, RYD08, RYD09, RYD10, RYD11, RYD12,
|
|
RYSLY, RYNXT, RYRMK, RYYXT
|
|
)
|
|
VALUES
|
|
(GETDATE(), 'INSERT', @TRUSR, @RYENO, @RYYER, @RYTOT, 0, @RYYST,
|
|
@RYH01, @RYH02, @RYH03, @RYH04, @RYH05, @RYH06, @RYH07, @RYH08, @RYH09, @RYH10, @RYH11, @RYH12,
|
|
@RYD01, @RYD02, @RYD03, @RYD04, @RYD05, @RYD06, @RYD07, @RYD08, @RYD09, @RYD10, @RYD11, @RYD12,
|
|
@RYSLY, @RYNXT, @RYRMK, @RYYXT
|
|
)";
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "TRUSR", trusr);
|
|
Query.AddParam(cmd, "RYENO", ryeno);
|
|
Query.AddParam(cmd, "RYYER", ryyer + 1);
|
|
Query.AddParam(cmd, "RYTOT", ryyst);
|
|
Query.AddParam(cmd, "RYYST", ryyst);
|
|
Query.AddParam(cmd, "RYH01", 0);
|
|
Query.AddParam(cmd, "RYH02", 0);
|
|
Query.AddParam(cmd, "RYH03", 0);
|
|
Query.AddParam(cmd, "RYH04", 0);
|
|
Query.AddParam(cmd, "RYH05", 0);
|
|
Query.AddParam(cmd, "RYH06", 0);
|
|
Query.AddParam(cmd, "RYH07", 0);
|
|
Query.AddParam(cmd, "RYH08", 0);
|
|
Query.AddParam(cmd, "RYH09", 0);
|
|
Query.AddParam(cmd, "RYH10", 0);
|
|
Query.AddParam(cmd, "RYH11", 0);
|
|
Query.AddParam(cmd, "RYH12", 0);
|
|
Query.AddParam(cmd, "RYD01", 1);
|
|
Query.AddParam(cmd, "RYD02", 1);
|
|
Query.AddParam(cmd, "RYD03", 1);
|
|
Query.AddParam(cmd, "RYD04", 1);
|
|
Query.AddParam(cmd, "RYD05", 1);
|
|
Query.AddParam(cmd, "RYD06", 1);
|
|
Query.AddParam(cmd, "RYD07", 1);
|
|
Query.AddParam(cmd, "RYD08", 1);
|
|
Query.AddParam(cmd, "RYD09", 1);
|
|
Query.AddParam(cmd, "RYD10", 1);
|
|
Query.AddParam(cmd, "RYD11", 1);
|
|
Query.AddParam(cmd, "RYD12", 1);
|
|
Query.AddParam(cmd, "RYSLY", 0);
|
|
Query.AddParam(cmd, "RYNXT", 0);
|
|
Query.AddParam(cmd, "RYRMK", "");
|
|
Query.AddParam(cmd, "RYYXT", ryyxt);
|
|
Query.ExecuteNonQuery(cmd);
|
|
}
|
|
else
|
|
{
|
|
cmd.CommandText =
|
|
@"UPDATE RYER
|
|
SET TRDAT = GETDATE() ,
|
|
TRMOD = 'UPDATE' ,
|
|
TRUSR = @TRUSR ,
|
|
RYTOT = @RYTOT ,
|
|
RYYST = @RYYST ,
|
|
RYSLY = @RYSLY ,
|
|
RYNXT = @RYNXT ,
|
|
RYYXT = @RYYXT
|
|
WHERE RYENO = @OLD_RYENO
|
|
AND RYYER = @OLD_RYYER";
|
|
cmd.Parameters.Clear();
|
|
Query.AddParam(cmd, "TRUSR", trusr);
|
|
Query.AddParam(cmd, "RYTOT", ryyst);
|
|
Query.AddParam(cmd, "RYYST", ryyst);
|
|
Query.AddParam(cmd, "RYSLY", 0);
|
|
Query.AddParam(cmd, "RYNXT", 0);
|
|
Query.AddParam(cmd, "RYYXT", ryyxt);
|
|
Query.AddParam(cmd, "OLD_RYENO", ryeno);
|
|
Query.AddParam(cmd, "OLD_RYYER", ryyer + 1);
|
|
Query.ExecuteNonQuery(cmd);
|
|
}
|
|
|
|
var finalDate = new DateTime(ryyer, 12, 01);
|
|
|
|
// 取得派駐專案
|
|
project = rasnDao.SelectRecentByDate(ryeno, finalDate);
|
|
if (project.Rows.Count == 0)
|
|
{
|
|
throw new Exception("人員無派遣紀錄，無法結算");
|
|
}
|
|
rapyn = GetStringValue(project.Rows[0]["RAPYN"]);
|
|
// 取得結算單號
|
|
var finalREN = rsajDao.GetAnnualLeaveSalaryREN(finalDate, ryeno, rapyn);
|
|
// 若結算單號已存在，需要有權限的人員才可以執行
|
|
if (!string.IsNullOrEmpty(finalREN) && !unmweb.CheckRight("RYER_FINAL", WRightName.Execute, UserId))
|
|
{
|
|
throw new Exception("已經結算過，你沒有權限再次結算");
|
|
}
|
|
|
|
WriteAnnualLeaveSalary(ryeno, finalDate, rysly, false);
|
|
return "年度結算";
|
|
}
|
|
// 離職日期是今年才要結算
|
|
else if (bpedy.Year == ryyer)
|
|
{
|
|
var finalDate = new DateTime(bpedy.Year, bpedy.Month, 01);
|
|
|
|
// 取得派駐專案
|
|
project = rasnDao.SelectRecentByDate(ryeno, finalDate);
|
|
if (project.Rows.Count == 0)
|
|
{
|
|
throw new Exception("人員無派遣紀錄，無法結算");
|
|
}
|
|
rapyn = GetStringValue(project.Rows[0]["RAPYN"]);
|
|
// 取得結算單號
|
|
var finalREN = rsajDao.GetAnnualLeaveSalaryREN(finalDate, ryeno, rapyn);
|
|
// 若結算單號已存在，需要有權限的人員才可以執行
|
|
if (!string.IsNullOrEmpty(finalREN) && !unmweb.CheckRight("RYER_FINAL", WRightName.Execute, UserId))
|
|
{
|
|
throw new Exception("已經結算過，你沒有權限再次結算");
|
|
}
|
|
|
|
WriteAnnualLeaveSalary(ryeno, finalDate, rysly, true);
|
|
return "離職結算";
|
|
}
|
|
//
|
|
throw new Exception("離職日期年度不是本年，不予結算");
|
|
}
|
|
|
|
/// <summary>
|
|
/// 寫入特休薪資結算
|
|
/// </summary>
|
|
/// <param name="ryeno">人員編號</param>
|
|
/// <param name="date">結算日期</param>
|
|
/// <param name="rysly">結算時數</param>
|
|
/// <param name="isQuit">是否離職</param>
|
|
internal void WriteAnnualLeaveSalary(string ryeno, DateTime date, int rysly, bool isQuit)
|
|
{
|
|
var rasnDao = new RasnDAC(Query);
|
|
var rsajDao = new RsajDAC(Query);
|
|
GetData getData = new GetData();
|
|
Calculate calc = new Calculate();
|
|
getData.Query = Query;
|
|
|
|
// 取得派駐專案
|
|
var project = rasnDao.SelectRecentByDate(ryeno, date);
|
|
if (project.Rows.Count == 0)
|
|
{
|
|
throw new Exception("人員無派遣紀錄，無法結算");
|
|
}
|
|
var rapyn = GetStringValue(project.Rows[0]["RAPYN"]);
|
|
|
|
// 取得月薪
|
|
// 取得計算公式
|
|
int bpsrm; // 月薪
|
|
int bpp02; // 伙食津貼
|
|
double dumm1; // 前二加班比率
|
|
double dumm2; // 後二加班比率
|
|
double dumm3; // 假日加班比率
|
|
string bpovr; // 加班費公式
|
|
string dumm4; // 請假扣款公式
|
|
string dumm5; // 加班費計算方式
|
|
// 取得月薪、伙食津貼、前二加班比率、後二加班比率、假日加班比率、加班費公式、請假扣款公式
|
|
getData.GetRASN_Date_Bppyn_Bpeno_Overwork_Rate(
|
|
date, rapyn, ryeno, out bpsrm, out bpp02, out dumm1, out dumm2, out dumm3, out bpovr, out dumm4, out dumm5);
|
|
|
|
// 計算特休薪資
|
|
var rsamt = GetInt32Value(Math.Truncate(calc.CalcOverTime(bpsrm, bpp02, rysly, 1.0, 1.0, bpovr)));
|
|
|
|
// 寫入離職當月薪資加減項
|
|
var rsdoc = isQuit
|
|
? string.Format("{0}離職特休結算{1}小時", date.ToString("yyyyMMdd", WGuard1.TaiwanCulture), (int)(rysly / 60))
|
|
: string.Format("{0}年度特休結算{1}小時", date.ToString("yyyy", WGuard1.TaiwanCulture), (int)(rysly / 60));
|
|
var rsajRen = rsajDao.WriteAnnualLeaveSalary(date, ryeno, rapyn, rsamt, rsdoc);
|
|
}
|
|
|
|
}
|
|
|
|
public class RyerSaveItem
|
|
{
|
|
public int result;
|
|
public string message;
|
|
public string userid;
|
|
public string ryeno;
|
|
public int ryyer;
|
|
public int? ryyst;
|
|
public int? ryyst_h;
|
|
public int? ryyst_m;
|
|
public int ryyxt;
|
|
public string ryyxt_d;
|
|
public int ryh01;
|
|
public int ryh02;
|
|
public int ryh03;
|
|
public int ryh04;
|
|
public int ryh05;
|
|
public int ryh06;
|
|
public int ryh07;
|
|
public int ryh08;
|
|
public int ryh09;
|
|
public int ryh10;
|
|
public int ryh11;
|
|
public int ryh12;
|
|
public int ryd01;
|
|
public int ryd02;
|
|
public int ryd03;
|
|
public int ryd04;
|
|
public int ryd05;
|
|
public int ryd06;
|
|
public int ryd07;
|
|
public int ryd08;
|
|
public int ryd09;
|
|
public int ryd10;
|
|
public int ryd11;
|
|
public int ryd12;
|
|
public int? rysly;
|
|
public int? rysly_h;
|
|
public int? rysly_m;
|
|
public int? rynxt;
|
|
public int? rynxt_h;
|
|
public int? rynxt_m;
|
|
public string ryrmk;
|
|
public string ryvld;
|
|
public string ryust;
|
|
public string rylft;
|
|
public string ryeny;
|
|
public int rymmy;
|
|
public int ryslq;
|
|
public string ryenq;
|
|
public int rymmq;
|
|
} |