/// <summary>
/// 查詢EmpWorkTimeLog資料
/// </summary>
/// <param name="model">畫面model</param>
/// <param name="datas">回傳資料</param>
/// <param name="msg">回傳訊息</param>
/// <returns>bool</returns>
public static bool LoadEmpWorkTimeLogGridData(EmpWorkTimeModel model, out List <QueryListBean> datas, out string msg)
{
bool result = false;
msg = "";
datas = new List <QueryListBean>();
try
{
using (var conn = new SqlConnection()
{
ConnectionString = DbHelper.getConnectionStrings(_attendantDBName).ConnectionString
})
{
try
{
datas = conn.Query <QueryListBean>(WorkTimeSql.LoadEmpWorkTimeLogGridData(), model).ToList();
}
catch (Exception)
{
throw;
}
}
result = true;
}
catch (Exception ex)
{
msg = ex.Message;
}
return(result);
}
private void GetData(string CompID, string EmpID)
{
var isSuccess = false;
var msg = "";
var datas = new List <QueryListBean>();
var viewData = new EmpWorkTimeModel()
{
CompID = CompID,
EmpID = EmpID
};
isSuccess = WorkTime.LoadEmpWorkTimeLogGridData(viewData, out datas, out msg);
if (msg != "")
{
Util.MsgBox(msg);
gvMain.DataSource = null;
gvMain.DataBind();
return;
}
if (isSuccess && datas != null)
{
gvMain.DataSource = datas;
gvMain.DataBind();
}
}
/// <summary>
/// 取得值勤單位下拉選單
/// </summary>
/// <param name="model">畫面model</param>
/// <param name="datas">回傳資料</param>
/// <param name="msg">回傳訊息</param>
/// <returns>bool</returns>
public static bool LoadDutyOrgan(EmpWorkTimeModel model, out List <DropDownListMobel> datas, out string msg)
{
bool result = false;
msg = "";
datas = new List <DropDownListMobel>();
try
{
using (var conn = new SqlConnection()
{
ConnectionString = DbHelper.getConnectionStrings(_eHRMSDB_ITRD).ConnectionString
})
{
try
{
datas = conn.Query <DropDownListMobel>(WorkTimeSql.LoadDutyOrgan(model), model).ToList();
}
catch (Exception)
{
throw;
}
}
result = true;
}
catch (Exception ex)
{
msg = ex.Message;
}
return(result);
}
/// <summary>
/// 查詢EmpWorkTime資料(新增頁)
/// </summary>
public static string LoadEmpWorkTimeGridData_Add(EmpWorkTimeModel model)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT P.CompID, 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(", WorkTime = LEFT(W.BeginTime, 2) + ':' + RIGHT(W.BeginTime, 2) + '~' + LEFT(W.EndTime, 2) + ':' + RIGHT(W.EndTime, 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 " + _eHRMSDB_ITRD + "..Personal P");
sb.AppendLine("LEFT JOIN EmpWorkTime EW ON EW.CompID = P.CompID AND EW.EmpID = P.EmpID");
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 P.WorkStatus = '1'");
sb.AppendLine("AND P.CompID = @CompID");
if (!String.IsNullOrEmpty(model.OrganID))
{
sb.AppendLine("AND P.OrganID IN ('" + model.OrganID + "')");
}
sb.AppendLine("AND P.WorkStatus = '1'");
sb.AppendLine("ORDER BY P.CompID, P.DeptID, P.OrganID, P.EmpID");
return(sb.ToString());
}
/// <summary>
/// 查詢EmpWorkTime資料
/// </summary>
public static string LoadEmpWorkTimeGridData(EmpWorkTimeModel model)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT P.CompID, C.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(", WorkTime = LEFT(W.BeginTime, 2) + ':' + RIGHT(W.BeginTime, 2) + '~' + LEFT(W.EndTime, 2) + ':' + RIGHT(W.EndTime, 2)");
sb.AppendLine(", RotateFlag = CASE WHEN EW.RotateFlag = '1' THEN '是' ELSE '' END");
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 EmpWorkTime 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 + "..Company C ON P.CompID = C.CompID");
sb.AppendLine("LEFT JOIN " + _eHRMSDB_ITRD + "..EmpFlow E ON P.CompID = E.CompID AND P.EmpID = E.EmpID AND E.ActionID = '01'");
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 P.CompID = @CompID");
if (model.AllSearch)
{
sb.AppendLine("AND (P.OrganID IN ('" + model.OrganID + "') OR E.OrganID IN ('" + model.FlowOrganID + "'))");
}
else
{
if (!String.IsNullOrEmpty(model.OrganID))
{
sb.AppendLine("AND P.OrganID IN ('" + model.OrganID + "')");
}
if (!String.IsNullOrEmpty(model.FlowOrganID))
{
sb.AppendLine("AND E.OrganID IN ('" + model.FlowOrganID + "')");
}
}
if (!String.IsNullOrEmpty(model.WTID))
{
sb.AppendLine("AND OW.WTID = @WTID");
}
if (!String.IsNullOrEmpty(model.EmpID))
{
sb.AppendLine("AND UPPER(P.EmpID) = UPPER(@EmpID)");
}
if (!String.IsNullOrEmpty(model.EmpName))
{
sb.AppendLine("AND P.NameN LIKE N'%" + model.EmpName + "%'");
}
sb.AppendLine("AND P.WorkStatus = '1'");
sb.AppendLine("ORDER BY P.CompID, P.DeptID, P.OrganID, P.EmpID");
return(sb.ToString());
}
/// <summary>
/// 取得值勤單位下拉選單
/// </summary>
public static string LoadDutyOrgan(EmpWorkTimeModel model)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT DISTINCT RTRIM(O.OrganID) AS DataValue");
sb.AppendLine(", RTRIM(O.OrganID) + '-' + O.OrganName AS DataText");
sb.AppendLine("FROM EmpFlow E");
sb.AppendLine("JOIN Personal P ON P.CompID = E.CompID AND P.EmpID = E.EmpID");
sb.AppendLine("JOIN Organization O ON O.CompID = P.CompID AND O.OrganID = P.DeptID");
sb.AppendLine("WHERE E.ActionID = '01'");
sb.AppendLine("AND E.OrganID IN ('" + model.FlowOrganID + "')");
return(sb.ToString());
}
/// <summary>
/// 查詢邏輯
/// </summary>
private void DoQuery()
{
var isSuccess = false;
var msg = "";
var datas = new List <QueryListBean>();
var viewData = new EmpWorkTimeModel();
if (_IsBoss)
{
if (ddlCompID.SelectedValue == "")
{
gvMain.DataSource = null;
gvMain.DataBind();
return;
}
viewData.CompID = ddlCompID.SelectedValue;
viewData.OrganID = GetOrganWhere();
viewData.FlowOrganID = GetFlowOrganWhere();
viewData.EmpID = txtEmpID.Text.Trim();
viewData.EmpName = txtEmpName.Text.Trim();
//viewData.AllSearch = (ddlOrgType.SelectedValue == "" && ddlDeptID.SelectedValue == "" && ddlOrganID.SelectedValue == "" && ddlRoleCode40.SelectedValue == "" && ddlRoleCode30.SelectedValue == "" && ddlRoleCode20.SelectedValue == "" && ddlRoleCode10.SelectedValue == "");
}
else
{
viewData.CompID = UserInfo.getUserInfo().CompID;
viewData.EmpID = UserInfo.getUserInfo().UserID;
}
isSuccess = WorkTime.LoadEmpWorkTimeGridData(viewData, out datas, out msg);
if (msg != "")
{
Util.MsgBox(msg);
gvMain.DataSource = null;
gvMain.DataBind();
return;
}
if (isSuccess && datas != null)
{
gvMain.DataSource = datas;
gvMain.DataBind();
}
}
