/// <summary>
/// 打卡查詢-個人
/// 取得Grid資料
/// </summary>
/// <param name="model">畫面model</param>
/// <param name="datas">回傳資料</param>
/// <param name="msg">回傳訊息</param>
/// <returns>bool</returns>
public static bool SelectPunchConfirmForAll(PunchConfirmModel model, string orgType, string searchType, out List <PunchConfirmBean> datas, out string msg)
{
bool result = false;
msg = "";
datas = new List <PunchConfirmBean>();
try
{
using (var conn = new SqlConnection()
{
ConnectionString = DbHelper.getConnectionStrings(_attendantDBName).ConnectionString
})
{
PunchConfirmBean dataBean = new PunchConfirmBean()
{
CompID = model.CompID,
EmpID = model.EmpID,
EmpNameN = model.EmpName,
OrganID = model.OrganID,
FlowOrganID = model.FlowOrganID,
PunchSDate = model.PunchSDate,
PunchEDate = model.PunchEDate,
PunchSTime = model.PunchSTime,
PunchETime = model.PunchETime,
ConfirmPunchFlag = model.ConfirmPunchFlag,
ConfirmStatus = model.ConfirmStatus,
Remedy_AbnormalFlag = model.Remedy_AbnormalFlag
};
StringBuilder sb = new StringBuilder();
SqlCommand.SelectPunchConfirmForAllSql(dataBean, orgType, searchType, ref sb);
try
{
datas = conn.Query <PunchConfirmBean>(sb.ToString(), dataBean).ToList();
}
catch (Exception)
{
throw;
}
if (datas == null || datas.Count == 0)
{
throw new Exception("查無資料!");
}
}
result = true;
}
catch (Exception ex)
{
msg = ex.Message;
}
return(result);
}
/// <summary>
/// SelectPunchConfirmSql
/// 打卡查詢--個人
/// </summary>
/// <param name="sb">傳入之前組好Command</param>
/// <param name="isReset">StringBuilder Reset or not</param>
public static void SelectPunchConfirmSql(PunchConfirmBean model, string searchType, ref StringBuilder sb, bool isReset = false)
{
if (isReset)
{
sb = new StringBuilder();
}
sb.Append(" SELECT ");
sb.Append(" AbnormalType,PunchDate AS PunchSDate,CONVERT(char(5),PunchTime) AS PunchTime,ConfirmPunchFlag,Source,Remedy_AbnormalReasonCN,Remedy_AbnormalDesc ");
sb.Append(" FROM PunchConfirm ");
sb.Append(" WHERE 0 = 0 ");
sb.Append(" AND CompID=@CompID ");
sb.Append(" AND EmpID=@EmpID ");
if (!String.IsNullOrEmpty(model.PunchSDate))
{
sb.Append(" AND PunchDate >= @PunchSDate ");
}
if (!String.IsNullOrEmpty(model.PunchEDate))
{
sb.Append(" AND PunchDate <= @PunchEDate ");
}
if (!String.IsNullOrEmpty(searchType))
{
switch (searchType)
{
case "1": { sb.Append(" AND AbnormalType = '4' "); break; }
case "2": { sb.Append(" AND PunchFlag = '0' "); break; }
case "3": { sb.Append(" AND AbnormalType IN ('5','6') "); break; }
case "4": { sb.Append(" AND Source = 'C' "); break; }
}
}
if (!String.IsNullOrEmpty(model.ConfirmPunchFlag))
{
sb.Append(" AND ConfirmPunchFlag = @ConfirmPunchFlag ");
}
if (!String.IsNullOrEmpty(model.ConfirmStatus))
{
sb.Append(" AND ConfirmStatus = @ConfirmStatus ");
}
if (!String.IsNullOrEmpty(model.Remedy_AbnormalFlag))
{
sb.Append(" AND Remedy_AbnormalFlag = @Remedy_AbnormalFlag ");
}
sb.Append(" ; ");
}
/// <summary>
/// 查詢人員資料
/// </summary>
private void QueryEmpInfo(string txtEmp)
{
var isSuccess = false;
var msg = "";
var datas = new PunchConfirmBean();
var viewData = new PunchConfirmModel()
{
CompID = UserInfo.getUserInfo().CompID,
EmpID = txtEmp
};
isSuccess = PunchLogInqForOrgan.SelectPerson(viewData, out datas, out msg);
if (isSuccess && datas != null)
{
txtEmpID.Text = datas.EmpID;
lblEmpID.Text = datas.EmpNameN;
}
}
/// <summary>
/// SelectPerson
/// 打卡查詢--單位
/// 取得公司人員資料
/// </summary>
/// <param name="model">畫面model</param>
/// <param name="datas">回傳資料</param>
/// <param name="msg">回傳訊息</param>
/// <returns>bool</returns>
public static bool SelectPerson(PunchConfirmModel model, out PunchConfirmBean datas, out string msg)
{
bool result = false;
msg = "";
datas = new PunchConfirmBean();
try
{
using (var conn = new SqlConnection()
{
ConnectionString = DbHelper.getConnectionStrings(_eHRMSDB_ITRD).ConnectionString
})
{
PunchConfirmBean dataBean = new PunchConfirmBean()
{
CompID = model.CompID,
EmpID = model.EmpID
};
StringBuilder sb = new StringBuilder();
SqlCommand.SelectPersonSql(ref sb);
try
{
datas = conn.Query <PunchConfirmBean>(sb.ToString(), dataBean).FirstOrDefault();
}
catch (Exception)
{
throw;
}
if (datas == null)
{
throw new Exception("查無資料!");
}
}
result = true;
}
catch (Exception ex)
{
msg = ex.Message;
}
return(result);
}
/// <summary>
/// SelectPunchConfirmForAllSql
/// 打卡查詢--單位
/// </summary>
/// <param name="sb">傳入之前組好Command</param>
/// <param name="isReset">StringBuilder Reset or not</param>
public static void SelectPunchConfirmForAllSql(PunchConfirmBean model, string orgType, string searchType, ref StringBuilder sb, bool isReset = false)
{
if (isReset)
{
sb = new StringBuilder();
}
sb.Append(" SELECT ");
sb.Append(" AbnormalType,PunchDate AS PunchSDate,CONVERT(char(5),PunchTime) AS PunchTime,ConfirmPunchFlag,Source,DeptName,OrganName,EmpID,EmpName AS EmpNameN,Remedy_AbnormalReasonCN,RotateFlag ");
sb.Append(" FROM PunchConfirm ");
sb.Append(" WHERE 0 = 0 ");
sb.Append(" AND CompID=@CompID ");
switch (orgType)
{
case "Organ":
{
sb.AppendFormat(" AND OrganID IN ('{0}') ", model.OrganID);
break;
}
case "FlowOrgan":
{
sb.AppendFormat(" AND FlowOrganID IN ('{0}') ", model.FlowOrganID);
break;
}
}
if (!String.IsNullOrEmpty(model.PunchSDate))
{
sb.Append(" AND PunchDate >= @PunchSDate ");
}
if (!String.IsNullOrEmpty(model.PunchEDate))
{
sb.Append(" AND PunchDate <= @PunchEDate ");
}
if (!String.IsNullOrEmpty(model.PunchSTime.Replace(":", "")))
{
sb.Append(" AND PunchTime >= @PunchSTime ");
}
if (!String.IsNullOrEmpty(model.PunchETime.Replace(":", "")))
{
sb.Append(" AND PunchTime <= @PunchETime ");
}
if (!String.IsNullOrEmpty(searchType))
{
switch (searchType)
{
case "1": { sb.Append(" AND AbnormalType = '4' "); break; }
case "2": { sb.Append(" AND PunchFlag = '0' "); break; }
case "3": { sb.Append(" AND AbnormalType IN ('5','6') "); break; }
case "4": { sb.Append(" AND Source = 'C' "); break; }
}
}
if (!String.IsNullOrEmpty(model.ConfirmPunchFlag))
{
sb.Append(" AND ConfirmPunchFlag = @ConfirmPunchFlag ");
}
if (!String.IsNullOrEmpty(model.ConfirmStatus))
{
sb.Append(" AND ConfirmStatus = @ConfirmStatus ");
}
if (!String.IsNullOrEmpty(model.Remedy_AbnormalFlag))
{
sb.Append(" AND Remedy_AbnormalFlag = @Remedy_AbnormalFlag ");
}
if (!String.IsNullOrEmpty(model.EmpID))
{
sb.Append(" AND EmpID=@EmpID ");
}
if (!String.IsNullOrEmpty(model.EmpNameN))
{
sb.AppendFormat(" AND EmpName LIKE '%{0}%' ", model.EmpNameN);
}
sb.Append(" ; ");
}