240 lines
12 KiB
C#
240 lines
12 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Data;
|
|
|
|
using Cosmos.Common;
|
|
using Cosmos.UserFrame;
|
|
using Cosmos.CommonManager;
|
|
using Cosmos.ServiceProvider;
|
|
|
|
/*-----------------------------------------------------------------------------------------------*/
|
|
// 설 명 : 출/퇴근 등록
|
|
// 작 성 자 :
|
|
// 변경 이력 :
|
|
/*-----------------------------------------------------------------------------------------------*/
|
|
namespace Cosmos.Service
|
|
{
|
|
class EmpCommute : IDataServiceUs
|
|
{
|
|
private SManager sManager = new SManager(); // 이 객체를 통해 업무 Service 호출
|
|
private PosStatus m_cPosStatus = new PosStatus(); //기본정보 참조
|
|
private StateServer StateObject = (StateServer)StateServer.GetInstance(); // StateObject : StateServer Object (객체)
|
|
|
|
private IDatabaseSQL m_cSqlDbService = null; // 데이터베이스 관리
|
|
|
|
public EmpCommute()
|
|
{
|
|
m_cPosStatus = (PosStatus)StateObject.POS; // POS 기본정보
|
|
m_cSqlDbService = (IDatabaseSQL)sManager.InitServiceInstance(ServiceLists.AGENT_DATABASE.DLL, ServiceLists.AGENT_DATABASE.DATABASE_MSSQL);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 직원방문등록 조회
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public object GetData(string[] aParam)
|
|
{
|
|
|
|
string sQuery = "";
|
|
DataTable dt = null;
|
|
|
|
try
|
|
{
|
|
|
|
if (aParam[0] == "List")
|
|
{
|
|
sQuery = " DECLARE @CMP_CD VARCHAR(4) ";
|
|
sQuery += " DECLARE @STOR_CD VARCHAR(10) ";
|
|
sQuery += " DECLARE @WORK_DT VARCHAR(8) ";
|
|
sQuery += " SET @CMP_CD = '" + m_cPosStatus.Base.CmpCd + "'";
|
|
sQuery += " SET @STOR_CD = '" + m_cPosStatus.Base.StoreNo + "'";
|
|
sQuery += " SET @WORK_DT = '" + aParam[1] + "'";
|
|
sQuery += " SELECT '' AS TOT_STAT ";
|
|
sQuery += " ,'' AS REG_TIME ";
|
|
sQuery += " ,A.CASHIER_NM ";
|
|
sQuery += " ,A.CMP_CD ";
|
|
sQuery += " ,A.STOR_CD ";
|
|
sQuery += " ,A.CASHIER_ID ";
|
|
sQuery += " ,ISNULL(B.WORK_DT,'') WORK_DT ";
|
|
sQuery += " ,ISNULL(B.WORK_STAT,-1) WORK_STAT ";
|
|
sQuery += " ,ISNULL(B.WORK_TIME,'') WORK_TIME ";
|
|
sQuery += " ,ISNULL(B.REST_STAT,-1) REST_STAT ";
|
|
sQuery += " ,ISNULL(B.REST_TIME,'') REST_TIME ";
|
|
sQuery += " ,ISNULL(B.GOOUT_STAT,-1) GOOUT_STAT ";
|
|
sQuery += " ,ISNULL(B.GOOUT_TIME,'')GOOUT_TIME ";
|
|
sQuery += " FROM POSMST..MST_USER A ";
|
|
sQuery += " LEFT JOIN ( ";
|
|
sQuery += " SELECT CMP_CD ";
|
|
sQuery += " ,STOR_CD ";
|
|
sQuery += " ,EMP_NO ";
|
|
sQuery += " ,WORK_DT ";
|
|
sQuery += " ,SUM(CASE WHEN ROS_CD IN ('1','2') THEN CASE WHEN ROS_CD = '1' THEN 1 ELSE -1 END END) AS 'WORK_STAT' ";
|
|
sQuery += " ,MAX(CASE WHEN ROS_CD IN ('1','2') THEN HOUR_STAMP ELSE '' END) AS 'WORK_TIME' ";
|
|
sQuery += " ,SUM(CASE WHEN ROS_CD IN ('3','4') THEN CASE WHEN ROS_CD = '3' THEN 1 ELSE -1 END END) AS 'REST_STAT' ";
|
|
sQuery += " ,MAX(CASE WHEN ROS_CD IN ('3','4') THEN HOUR_STAMP ELSE '' END) AS 'REST_TIME' ";
|
|
sQuery += " ,SUM(CASE WHEN ROS_CD IN ('5','6') THEN CASE WHEN ROS_CD = '5' THEN 1 ELSE -1 END END) AS 'GOOUT_STAT' ";
|
|
sQuery += " ,MAX(CASE WHEN ROS_CD IN ('5','6') THEN HOUR_STAMP ELSE '' END) AS 'GOOUT_TIME' ";
|
|
sQuery += " FROM POSLOG.dbo.TR_STOR_EMP ";
|
|
sQuery += " WHERE CMP_CD = @CMP_CD ";
|
|
sQuery += " AND STOR_CD = @STOR_CD ";
|
|
sQuery += " AND WORK_DT = @WORK_DT ";
|
|
sQuery += " GROUP BY CMP_CD ";
|
|
sQuery += " ,STOR_CD ";
|
|
sQuery += " ,EMP_NO ";
|
|
sQuery += " ,WORK_DT ";
|
|
sQuery += " ) B ";
|
|
sQuery += " ON A.CMP_CD = B.CMP_CD ";
|
|
sQuery += " AND A.STOR_CD = B.STOR_CD ";
|
|
sQuery += " AND A.CASHIER_ID = B.EMP_NO ";
|
|
sQuery += " WHERE A.CMP_CD = @CMP_CD ";
|
|
sQuery += " AND A.STOR_CD = @STOR_CD ";
|
|
sQuery += " AND A.USE_YN = '1' ";
|
|
|
|
if (aParam[2] != "")
|
|
{
|
|
sQuery += " AND A.CASHIER_ID = '" + aParam[2] + "' ";
|
|
}
|
|
|
|
|
|
}
|
|
else if (aParam[0] == "Time")
|
|
{
|
|
sQuery = string.Format(" SELECT DATEDIFF(SECOND,CONVERT(DATETIME,'{0}'),GETDATE()) AS IGAP", aParam[1]);
|
|
}
|
|
|
|
|
|
if (sQuery != "")
|
|
{
|
|
int iRet = m_cSqlDbService.DBSelect(sQuery, out dt);
|
|
|
|
if (iRet == 1) return dt;
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
WinManager.ExceptionMessage(System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()", ex.Message);
|
|
}
|
|
return null;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 없음!
|
|
/// </summary>
|
|
/// <param name="oParam"></param>
|
|
/// <returns></returns>
|
|
public string SetData(object oParam)
|
|
{
|
|
throw new NotImplementedException();
|
|
}
|
|
|
|
/// <summary>
|
|
/// 등록 저장
|
|
/// </summary>
|
|
/// <param name="aParam"></param>
|
|
/// <returns></returns>
|
|
public string Execute(string[] aParam)
|
|
{
|
|
string sQuery = "";
|
|
string sRet = UserCom.RST_ERR;
|
|
|
|
try
|
|
{
|
|
|
|
//0:사원번호, 1:근무일자, 2:근태코드
|
|
|
|
string sRegDateTime = DateTime.Now.ToString("yyyyMMddHHmmss");
|
|
string sRegTime = CmUtil.MidH(sRegDateTime, 8, 4);
|
|
|
|
sQuery = " DECLARE @SEQ INT ";
|
|
sQuery += " SELECT @SEQ = ISNULL(MAX(SEQ),0) + 1 ";
|
|
sQuery += " FROM POSLOG.dbo.TR_STOR_EMP ";
|
|
sQuery += " WHERE CMP_CD = '{0}' ";
|
|
sQuery += " AND STOR_CD = '{1}' ";
|
|
sQuery += " AND EMP_NO = '{2}' ";
|
|
sQuery += " AND WORK_DT = '{3}' ";
|
|
sQuery += " AND ROS_CD = '{4}' ";
|
|
sQuery += " INSERT INTO POSLOG.dbo.TR_STOR_EMP( ";
|
|
sQuery += " CMP_CD, STOR_CD, EMP_NO, WORK_DT, ROS_CD, SEQ, HOUR_STAMP, ROS_HOUR, SEND_YN, REG_DATE ";
|
|
sQuery += " ,UPD_DATE ";
|
|
sQuery += " ) VALUES ( '{0}', '{1}', '{2}', '{3}', '{4}', @SEQ, '{5}', '{6}', '{7}' , '{8}', '{9}' ";
|
|
sQuery += " )";
|
|
|
|
sQuery = string.Format(sQuery
|
|
, m_cPosStatus.Base.CmpCd // CMP_CD
|
|
, m_cPosStatus.Base.StoreNo // STOR_CD
|
|
, aParam[0] // EMP_NO
|
|
, aParam[1] // WORK_DT
|
|
, aParam[2] // ROS_CD
|
|
, sRegDateTime // HOUR_STAMP
|
|
, sRegTime // ROS_HOUR
|
|
, "0" // SEND_YN
|
|
, sRegDateTime // REG_DATE
|
|
, sRegDateTime // UPD_DATE
|
|
);
|
|
|
|
// Add, 2017.09.04, 퇴근시 근무시간 확인서 출력 추가
|
|
//if (m_cSqlDbService.DBExecuteNonQuery(new string[] { sQuery }) == UserCom.OK) return UserCom.RST_OK;
|
|
if (m_cSqlDbService.DBExecuteNonQuery(new string[] { sQuery }) != UserCom.OK) return UserCom.RST_ERR;
|
|
|
|
sRet = UserCom.RST_OK;
|
|
|
|
// 퇴근이면 ...
|
|
if (aParam[2] == "2")
|
|
{
|
|
// 근무시간 조회
|
|
sQuery = "SELECT A.CMP_CD \n"
|
|
+ " , A.STOR_CD \n"
|
|
+ " , A.EMP_NO \n"
|
|
+ " , B.CASHIER_NM \n"
|
|
+ " , A.WORK_DT \n"
|
|
+ " , MAX(CASE A.ROS_CD WHEN '1' THEN A.HOUR_STAMP ELSE NULL END) STR_STAMP \n"
|
|
+ " , MAX(CASE A.ROS_CD WHEN '2' THEN A.HOUR_STAMP ELSE NULL END) END_STAMP \n"
|
|
+ " FROM POSLOG..TR_STOR_EMP A LEFT JOIN POSMST..MST_USER B \n"
|
|
+ " ON A.CMP_CD = B.CMP_CD \n"
|
|
+ " AND A.STOR_CD = B.STOR_CD \n"
|
|
+ " AND A.EMP_NO = B.CASHIER_ID \n"
|
|
+ " WHERE A.CMP_CD = '" + m_cPosStatus.Base.CmpCd + "' \n"
|
|
+ " AND A.STOR_CD = '" + m_cPosStatus.Base.StoreNo + "' \n"
|
|
+ " AND A.EMP_NO = '" + aParam[0] + "' \n"
|
|
+ " AND A.WORK_DT = '" + aParam[1] + "' \n"
|
|
+ " AND A.ROS_CD IN ('1','2') \n"
|
|
+ " GROUP BY A.CMP_CD, A.STOR_CD, A.EMP_NO, B.CASHIER_NM, A.WORK_DT \n"
|
|
;
|
|
DataTable dt = null;
|
|
if (m_cSqlDbService.DBSelect(sQuery, out dt) == UserCom.OK)
|
|
{
|
|
if (dt != null && dt.Rows.Count > 0)
|
|
{
|
|
var empNo = CmUtil.GetDataRowStr(dt.Rows[0], "EMP_NO");
|
|
var empNm = CmUtil.GetDataRowStr(dt.Rows[0], "CASHIER_NM");
|
|
var strTime = CmUtil.GetDataRowStr(dt.Rows[0], "STR_STAMP");
|
|
var endTime = CmUtil.GetDataRowStr(dt.Rows[0], "END_STAMP");
|
|
|
|
if (string.IsNullOrWhiteSpace(strTime) == false && string.IsNullOrWhiteSpace(endTime) == false)
|
|
{
|
|
// 근무시간 확인서 출력
|
|
ISaleCompleteUs cSaleComplete = (ISaleCompleteUs)sManager.InitServiceInstance(ServiceLists.ASV_DATA_PROCESS.DLL, ServiceLists.ASV_DATA_PROCESS.SALE_COMPLETE);
|
|
cSaleComplete.ExecuteWorkTimeReceipt(empNo, empNm, strTime, endTime);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
WinManager.ExceptionMessage(System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
|
|
System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()", ex.Message);
|
|
}
|
|
|
|
return sRet;
|
|
}
|
|
|
|
}
|
|
}
|