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);
}
///
/// 직원방문등록 조회
///
///
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;
}
///
/// 없음!
///
///
///
public string SetData(object oParam)
{
throw new NotImplementedException();
}
///
/// 등록 저장
///
///
///
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;
}
}
}