/// <summary>
/// 查詢EmpGuardWorkTime資料(修改頁)
/// </summary>
/// <param name="model">畫面model</param>
/// <param name="datas">回傳資料</param>
/// <param name="msg">回傳訊息</param>
/// <returns>bool</returns>
public static bool GetEmpGuardWorkTime(EmpGuardWorkTimeModel model, out List <EmpGuardWorkTimeBean> datas, out string msg)
{
bool result = false;
msg = "";
datas = new List <EmpGuardWorkTimeBean>();
try
{
using (var conn = new SqlConnection()
{
ConnectionString = DbHelper.getConnectionStrings(_attendantDBName).ConnectionString
})
{
try
{
datas = conn.Query <EmpGuardWorkTimeBean>(WorkTimeSql.GetEmpGuardWorkTime(), model).ToList();
}
catch (Exception)
{
throw;
}
}
result = true;
}
catch (Exception ex)
{
msg = ex.Message;
}
return(result);
}
/// <summary>
/// 取得單位工作地點
/// </summary>
/// <param name="model">畫面model</param>
/// <param name="datas">回傳資料</param>
/// <param name="msg">回傳訊息</param>
/// <returns>bool</returns>
public static bool LoadOrgWorkSite(EmpGuardWorkTimeModel model, out List <WorkSiteMobel> datas, out string msg)
{
bool result = false;
msg = "";
datas = new List <WorkSiteMobel>();
try
{
using (var conn = new SqlConnection()
{
ConnectionString = DbHelper.getConnectionStrings(_eHRMSDB_ITRD).ConnectionString
})
{
try
{
datas = conn.Query <WorkSiteMobel>(WorkTimeSql.LoadOrgWorkSite(), model).ToList();
}
catch (Exception)
{
throw;
}
}
result = true;
}
catch (Exception ex)
{
msg = ex.Message;
}
return(result);
}
/// <summary>
/// 取得值勤日期行事曆
/// </summary>
public static string LoadGuardCalendar(EmpGuardWorkTimeModel model)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT EW.DutyDate, P.EmpID, P.NameN");
sb.AppendLine(", ISNULL(W.WTID, '') WTID");
sb.AppendLine(", DutyTime = CASE WHEN EW.BranchFlag = '1' THEN LEFT(W.BeginTime, 2) + ':' + RIGHT(W.BeginTime, 2) + '~' + LEFT(W.EndTime, 2) + ':' + RIGHT(W.EndTime, 2)");
sb.AppendLine("ELSE LEFT(EW.WTBeginTime, 2) + ':' + RIGHT(EW.WTBeginTime, 2) + '~' + LEFT(EW.WTEndTime, 2) + ':' + RIGHT(EW.WTEndTime, 2) END");
sb.AppendLine(", LastChgID = ISNULL(LP.NameN, EW.LastChgID)");
sb.AppendLine(", LastChgDate = CASE WHEN CONVERT(VARCHAR, EW.LastChgDate, 111) = '1900/01/01' THEN '' ELSE CONVERT(VARCHAR, EW.LastChgDate, 120) END");
sb.AppendLine("FROM EmpGuardWorkTime EW");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Personal P ON EW.CompID = P.CompID AND EW.EmpID = P.EmpID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..WorkTime W ON EW.WTCompID = W.CompID AND EW.WTID = W.WTID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Personal LP ON EW.LastChgComp = LP.CompID AND EW.LastChgID = LP.EmpID");
sb.AppendLine("WHERE 1=1");
sb.AppendLine("AND EW.DutyCompID = @CompID");
//sb.AppendLine("AND EW.DutyDeptID = @DeptID");
sb.AppendLine("AND EW.DutyOrganID = @OrganID");
if (!String.IsNullOrEmpty(model.BranchFlag))
{
sb.AppendLine("AND EW.BranchFlag = @BranchFlag");
}
if (!String.IsNullOrEmpty(model.EmpID))
{
sb.AppendLine("AND UPPER(EW.EmpID) = UPPER(@EmpID)");
}
if (!String.IsNullOrEmpty(model.EmpName))
{
sb.AppendLine("AND P.NameN LIKE N'%" + model.EmpName + "%'");
}
if (!String.IsNullOrEmpty(model.DutyDate))
{
sb.AppendLine("AND DutyDate = @DutyDate");
}
if (!String.IsNullOrEmpty(model.DutyDateYear))
{
sb.AppendLine("AND YEAR(DutyDate) = @DutyDateYear");
}
if (!String.IsNullOrEmpty(model.DutyDateMonth))
{
sb.AppendLine("AND MONTH(DutyDate) = @DutyDateMonth");
}
return(sb.ToString());
}
/// <summary>
/// 查詢值班人數
/// </summary>
/// <param name="model"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int SelectDutyCnt(EmpGuardWorkTimeModel model, out string msg)
{
var result = 0;
msg = "";
var viewData = new EmpGuardWorkTimeBean()
{
DutyDate = model.DutyDate,
DutyCompID = model.CompID,
DutyDeptID = model.DeptID,
DutyOrganID = model.OrganID,
BranchFlag = model.BranchFlag,
WTCompID = model.CompID,
WTID = model.WTID
};
WorkTime.SelectDutyCnt(viewData, out result, out msg);
return(result);
}
/// <summary>
/// 查詢EmpGuardWorkTime資料
/// </summary>
public static string LoadEmpGuardWorkTimeGridData(EmpGuardWorkTimeModel model)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT CONVERT(VARCHAR, EW.DutyDate, 111) AS DutyDate");
sb.AppendLine(", EW.DutyCompID, C.CompName AS DutyCompName");
sb.AppendLine(", EW.DutyOrganID, O.OrganName AS DutyOrganName");
sb.AppendLine(", P.CompID, PC.CompName, P.EmpID, P.NameN");
sb.AppendLine(", O2.OrgType, O3.OrganName AS OrgTypeName");
sb.AppendLine(", P.DeptID, O1.OrganName AS DeptName");
sb.AppendLine(", P.OrganID, O2.OrganName");
sb.AppendLine(", ISNULL(W.WTID, '') WTID");
//sb.AppendLine(", DutyTime = CASE WHEN EW.BranchFlag = '1' THEN LEFT(W.BeginTime, 2) + ':' + RIGHT(W.BeginTime, 2) + '~' + LEFT(W.EndTime, 2) + ':' + RIGHT(W.EndTime, 2)");
//sb.AppendLine("ELSE LEFT(EW.WTBeginTime, 2) + ':' + RIGHT(EW.WTBeginTime, 2) + '~' + LEFT(EW.WTEndTime, 2) + ':' + RIGHT(EW.WTEndTime, 2) END");
sb.AppendLine(", DutyTime = LEFT(EW.WTBeginTime, 2) + ':' + RIGHT(EW.WTBeginTime, 2) + '~' + LEFT(EW.WTEndTime, 2) + ':' + RIGHT(EW.WTEndTime, 2)");
sb.AppendLine(", LastChgComp = ISNULL(LC.CompName, EW.LastChgComp)");
sb.AppendLine(", LastChgID = ISNULL(LP.NameN, EW.LastChgID)");
//sb.AppendLine(", LastChgDate = CASE WHEN CONVERT(VARCHAR, EW.LastChgDate, 111) = '1900/01/01' THEN '' ELSE CONVERT(VARCHAR, EW.LastChgDate, 120) END");
sb.AppendLine(", LastChgDate = CASE WHEN CONVERT(VARCHAR, EW.LastChgDate, 111) = '1900/01/01' THEN '' ELSE CONVERT(VARCHAR, EW.LastChgDate, 111) + ' ' + CONVERT(VARCHAR, EW.LastChgDate, 24) END");
sb.AppendLine("FROM EmpGuardWorkTime EW");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Organization O ON O.CompID = EW.DutyCompID AND O.DeptID = EW.DutyDeptID AND O.OrganID = EW.DutyOrganID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Company C ON EW.DutyCompID = C.CompID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Personal P ON EW.CompID = P.CompID AND EW.EmpID = P.EmpID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Company PC ON P.CompID = PC.CompID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Organization O1 ON O1.CompID = P.CompID AND O1.OrganID = P.DeptID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Organization O2 ON O2.CompID = P.CompID AND O2.OrganID = P.OrganID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Organization O3 ON O3.CompID = P.CompID AND O3.OrganID = O2.OrgType");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..WorkTime W ON EW.WTCompID = W.CompID AND EW.WTID = W.WTID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Company LC ON EW.LastChgComp = LC.CompID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..Personal LP ON EW.LastChgComp = LP.CompID AND EW.LastChgID = LP.EmpID");
sb.AppendLine("WHERE EW.DutyCompID = @CompID");
sb.AppendLine("AND EW.DutyOrganID = @OrganID");
sb.AppendLine("AND EW.DutyDate = @DutyDate");
sb.AppendLine("AND EW.BranchFlag = @BranchFlag");
sb.AppendLine("ORDER BY P.CompID, P.DeptID, P.OrganID, P.EmpID");
return(sb.ToString());
}