using System;
using System.Text;
using System.Collections;
using System.Data;

using Cosmos.BaseFrame;
using Cosmos.UserFrame;
using Cosmos.ServiceProvider;
using Cosmos.Common;
using Cosmos.CommonManager;

/*-----------------------------------------------------------------------------------------------*/
// 설      명 : 
// 작  성  자 : 
// 변경  이력 : 
/*-----------------------------------------------------------------------------------------------*/
namespace Cosmos.Service
{
    class Reservation : SaleTranCallBase, ISaleReserved
    {

        private ISaleCompleteUs m_cSaleComplete = null;                             // 거래 완료처리

        /// <summary>
        /// 생성자
        /// </summary>
        public Reservation()
        {
            m_cSaleComplete = (ISaleCompleteUs)sManager.InitServiceInstance(ServiceLists.ASV_DATA_PROCESS.DLL, ServiceLists.ASV_DATA_PROCESS.SALE_COMPLETE);
        }

        //#Rhee, 20180117 픽업 진행 추가 start
        /// <summary>
        /// 예약거래 픽업
        /// </summary>
        /// <param name="sCmpCd"></param>
        /// <param name="sStorCd"></param>
        /// <param name="sSaleDate"></param>
        /// <param name="sPosNo"></param>
        /// <param name="sTradeNo"></param>
        /// <param name="sSendYn"></param>
        /// <returns></returns>
        public string PickupReservedCom(string sCmpCd, string sStorCd, string sSaleDate, string sPosNo, string sTradeNo, string sSendYn)
        {
            string sRet = UserCom.RST_ERR;
            var sQuery = string.Empty;
            var sSql = string.Empty;

            try
            {
                sQuery = "UPDATE POSLOG..TR_SALE_HEADER SET SEND_YN = '{0}', TRADE_KINDPER = '11' "
                       + "WHERE CMP_CD = '{1}' AND  STOR_CD  = '{2}' AND POS_NO  = '{3}' AND SALE_DT  = '{4}' AND TRADE_NO  = '{5}' "
                       
                       + "UPDATE POSLOG..TR_SALE_JOURNAL SET SEND_YN = '{0}', TRADE_KINDPER = '11' "
                       + "WHERE CMP_CD = '{1}' AND  STOR_CD  = '{2}' AND POS_NO  = '{3}' AND SALE_DT  = '{4}' AND TRADE_NO  = '{5}' ";

                sSql = string.Format(sQuery, sSendYn, sCmpCd, sStorCd, sPosNo, sSaleDate, sTradeNo);

                if(m_cSqlDbService.DBExecuteNonQuery(sSql) == UserCom.OK)
                {
                    sRet = UserCom.RST_OK;
                }
                return sRet;
            }
            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;
        }
        //#Rhee, 20180117 픽업 진행 추가 end

        /// <summary>
        /// 예약거래 조회
        /// </summary>
        /// <param name="sSaleDate"></param>
        /// <param name="sTradType"></param>
        /// <param name="sCondition"></param>
        /// <param name="sOnOffLine"></param>
        /// <returns></returns>
        public DataTable SearchReservedFinshList(string sSaleDate, string sTradType, string sCondition, string sOnOffLine)
        {
            string sQuery = "";
            try
            {
                sQuery += " DECLARE @CMP_CD   AS VARCHAR(4) ";
                sQuery += " DECLARE @STOR_CD  AS VARCHAR(10) ";
                sQuery += " DECLARE @POS_NO   AS VARCHAR(2) ";
                sQuery += " DECLARE @FNS_FDATE AS VARCHAR(8) ";
                sQuery += " SET @CMP_CD = '" + m_cPosStatus.Base.CmpCd + "'";
                sQuery += " SET @STOR_CD = '" + m_cPosStatus.Base.StoreNo + "'";
                sQuery += " SET @POS_NO  = '" + m_cPosStatus.Base.PosNo + "'";
                sQuery += " SET @FNS_FDATE = '" + sSaleDate + "'";

                //#Rhee, 20180103 예약 전액결제, 부분결제 조회 추가 Start
                sQuery += " SELECT NO ,RESERV_DT ,REG_RESERV_DT ,CUST_PHN_NO ,AFTER ,BEFORE ";
                sQuery += " ,CASE WHEN FNS_FLG = '1' AND BEFORE = '0.00' THEN 'reservation complete' ELSE BILL_STAT END BILL_STAT ";
                sQuery += " ,CUSTNM ,RESERV_MEMO ,TOTSALE_AMT ,CMP_CD,SALE_DT,STOR_CD ,POS_NO, TRADE_NO, RESERV_NO, FNS_FLG ,FNS_SALE_DT , FNS_POS_NO ,FNS_TRADE_NO FROM ( ";
                //#Rhee, 20180103 예약 전액결제, 부분결제 조회 추가 End

                sQuery += " SELECT ROW_NUMBER() OVER( ORDER BY B.TRADE_NO ASC) AS NO ";
                sQuery += " , ISNULL(C.RESERV_DT + C.RESERV_TM,'') AS RESERV_DT ";
                sQuery += " , ISNULL(C.RESERV_REG_DT + C.RESERV_REG_TM,'') AS REG_RESERV_DT ";
                sQuery += " , C.CUST_PHN_NO ";
                sQuery += " , CONVERT(VARCHAR,ISNULL(SUM((CASE WHEN P.PAY_WAY_CD + PAY_DTL_CD_01 = '1501' THEN 0 ELSE PAY_AMT END)),0)) AS 'AFTER' ";
                //#Rhee, 20180103 예약 조회시 잔금 표기 오류 수정 start
                // 기존
                //sQuery += " , CONVERT(VARCHAR,ISNULL(SUM((CASE WHEN P.PAY_WAY_CD + PAY_DTL_CD_01 = '1501' THEN PAY_AMT ELSE 0 END)),0)) AS 'BEFORE' ";
                // 변경
                sQuery += " , CONVERT(VARCHAR,ISNULL(SUM((CASE WHEN P.PAY_WAY_CD + PAY_DTL_CD_01 = '1501' THEN PAY_AMT ELSE (CASE WHEN  B.TOTSALE_AMT > P.PAY_AMT THEN B.TOTSALE_AMT - P.PAY_AMT ELSE 0 END) END)),0)) AS 'BEFORE' ";
                //#Rhee, 20180103 예약 조회시 잔금 표기 오류 수정 end
                sQuery += " , ISNULL(CASE B.FNS_FLG  WHEN '0' THEN  '" + MessageManager.GetLabelMessage(POS_MESSAGE.LABEL.MSG_0291) + "'";
                sQuery += "                          WHEN '1' THEN  '" + MessageManager.GetLabelMessage(POS_MESSAGE.LABEL.MSG_0077) + "'";
                //#Rhee, 20180117 미주 예약픽업 추가 Start
                //기존
                //sQuery += "                          WHEN '3' THEN  '" + MessageManager.GetLabelMessage(POS_MESSAGE.LABEL.MSG_0078) + "'";
                sQuery += "                          WHEN '4' THEN  '" + MessageManager.GetLabelMessage(POS_MESSAGE.LABEL.MSG_0078) + "'";
                sQuery += "                          WHEN '3' THEN  '" + MessageManager.GetLabelMessage(POS_MESSAGE.LABEL.MSG_1085) + "'";
                sQuery += "                                   ELSE  '" + MessageManager.GetLabelMessage(POS_MESSAGE.LABEL.MSG_0291) + "'";
                //#Rhee, 20180117 미주 예약픽업 추가 End                
                sQuery += "                          END,0) AS 'BILL_STAT' ";
                sQuery += " , C.CUSTNM , C.RESERV_MEMO ";
                sQuery += " , CONVERT(VARCHAR,B.TOTSALE_AMT) AS TOTSALE_AMT ";
                sQuery += " , B.CMP_CD, B.SALE_DT, B.STOR_CD, B.POS_NO,B.TRADE_NO,  B.RESERV_NO, B.FNS_FLG ";
                sQuery += " , MAX(B.FNS_SALE_DT)FNS_SALE_DT, MAX(B.FNS_POS_NO)FNS_POS_NO ,MAX(B.FNS_TRADE_NO)FNS_TRADE_NO ";
                sQuery += " FROM ( ";


                if (m_cPosStatus.Base.TrainingFlag == "1")
                {

                    sQuery += " SELECT CMP_CD,SALE_DT,STOR_CD,POS_NO,TRADE_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += "       ,SUM(FNS_FLG) AS FNS_FLG,MAX(FNS_SALE_DT) AS FNS_SALE_DT,MAX(FNS_POS_NO) AS FNS_POS_NO,MAX(FNS_TRADE_NO) AS FNS_TRADE_NO ";
                    sQuery += "   FROM ( ";
                    sQuery += " SELECT CMP_CD,SALE_DT,STOR_CD,POS_NO,REG_NO AS TRADE_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += "       ,1  AS 'FNS_FLG','' AS 'FNS_SALE_DT','' AS 'FNS_POS_NO','' AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.ETC_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD  = @CMP_CD ";
                    sQuery += "     AND STOR_CD = @STOR_CD ";
                    sQuery += "     AND POS_NO  = @POS_NO ";
                    sQuery += "     AND TRADE_DIV IN ('0') ";
                    sQuery += "     AND TRADE_KINDPER IN ('10') ";
                    sQuery += "     AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "     AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "'  ";
                    sQuery += " UNION ALL ";
                    sQuery += " SELECT CMP_CD,ORG_BILLDT,STOR_CD,ORG_BILL_POSNO,ORG_BILL_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += "       ,2  AS 'FNS_FLG',SALE_DT AS 'FNS_SALE_DT',POS_NO AS 'FNS_POS_NO',TRADE_NO AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.ETC_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD  = @CMP_CD ";
                    sQuery += "     AND STOR_CD = @STOR_CD ";
                    sQuery += "     AND POS_NO  = @POS_NO ";
                    sQuery += "     AND TRADE_DIV IN ('0') ";
                    sQuery += "     AND TRADE_KINDPER IN ('11') ";
                    sQuery += "     AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "     AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "'  ";
                    sQuery += " UNION ALL ";
                    //#Rhee, 20180117 미주 예약픽업 추가 Start
                    sQuery += " SELECT CMP_CD,ORG_BILLDT,STOR_CD, ORG_BILL_POSNO,ORG_BILL_NO,RESERV_DT, TOTSALE_AMT, RESERV_NO ";
                    sQuery += "       ,3 AS 'FNS_FLG',SALE_DT AS 'FNS_SALE_DT',POS_NO AS 'FNS_POS_NO',TRADE_NO AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.TR_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD = @CMP_CD ";
                    sQuery += "      AND STOR_CD = @STOR_CD ";
                    sQuery += "      AND POS_NO = @POS_NO ";
                    sQuery += "      AND TRADE_DIV IN ('0') ";
                    sQuery += "      AND TRADE_KINDPER IN ('12') ";
                    sQuery += "      AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "      AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "' ";
                    sQuery += " UNION ALL ";
                    sQuery += " SELECT CMP_CD,ORG_BILLDT,STOR_CD,ORG_BILL_POSNO,ORG_BILL_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO, ";
                    sQuery += "       -3 AS 'FNS_FLG','' AS 'FNS_SALE_DT','' AS 'FNS_POS_NO','' AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.TR_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD = @CMP_CD ";
                    sQuery += "      AND STOR_CD = @STOR_CD ";
                    sQuery += "      AND POS_NO = @POS_NO ";
                    sQuery += "      AND TRADE_DIV IN ('1') ";
                    sQuery += "      AND TRADE_KINDPER IN ('12') ";
                    sQuery += "      AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "      AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "' ";
                    sQuery += " UNION ALL ";
                    //#Rhee, 20180117 미주 예약픽업 추가 End
                    sQuery += " SELECT CMP_CD,ORG_BILLDT,STOR_CD,ORG_BILL_POSNO,ORG_BILL_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += "       ,-1  AS 'FNS_FLG',''  AS 'FNS_SALE_DT',''  AS 'FNS_POS_NO',''  AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.ETC_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD  = @CMP_CD ";
                    sQuery += "     AND STOR_CD = @STOR_CD";
                    sQuery += "     AND POS_NO  = @POS_NO ";
                    sQuery += "     AND TRADE_DIV IN ('1') ";
                    sQuery += "     AND TRADE_KINDPER IN ('10') ";
                    sQuery += "     AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "     AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "'  ";
                    sQuery += " UNION ALL ";
                    sQuery += " SELECT CMP_CD,ORG_BILLDT,STOR_CD,ORG_BILL_POSNO,ORG_BILL_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += "       ,-2  AS 'FNS_FLG',''  AS 'FNS_SALE_DT',''  AS 'FNS_POS_NO',''  AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.ETC_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD  = @CMP_CD ";
                    sQuery += "     AND STOR_CD = @STOR_CD ";
                    sQuery += "     AND POS_NO  = @POS_NO ";
                    sQuery += "     AND REG_NO IN ( ";
                    sQuery += "                      SELECT ORG_BILL_NO ";
                    sQuery += "                        FROM POSLOG.dbo.ETC_SALE_HEADER ";
                    sQuery += "                       WHERE CMP_CD  = @CMP_CD ";
                    sQuery += "                         AND STOR_CD = @STOR_CD ";
                    sQuery += "                         AND POS_NO  = @POS_NO ";
                    sQuery += "                         AND TRADE_DIV IN ('1') ";
                    sQuery += "                         AND TRADE_KINDPER IN ('11') ";
                    sQuery += "                        AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "                        AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "'  ";
                    sQuery += "                      ) ";
                    sQuery += "     AND TRADE_DIV IN ('0') ";
                    sQuery += "     AND TRADE_KINDPER IN ('11') ";
                    sQuery += "     AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "     AND TRAIN_MODE_YN = '" + m_cPosStatus.Base.TrainingFlag + "'  ";
                    sQuery += " ) A ";
                    sQuery += " GROUP BY CMP_CD,SALE_DT,STOR_CD,POS_NO,TRADE_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += " ) B ";
                    sQuery += " LEFT OUTER JOIN POSLOG.DBO.ETC_RESERV_INFO C ";
                    sQuery += "  ON B.CMP_CD = C.CMP_CD ";
                    sQuery += " AND B.SALE_DT = C.SALE_DT ";
                    sQuery += " AND B.STOR_CD = C.STOR_CD ";
                    sQuery += " AND B.POS_NO = C.POS_NO ";
                    sQuery += " AND B.TRADE_NO = C.REG_NO ";
                    sQuery += " AND B.RESERV_NO = C.RESERV_NO ";
                    sQuery += " LEFT OUTER JOIN POSLOG.DBO.ETC_SALE_PAY P ";
                    sQuery += "  ON B.CMP_CD = P.CMP_CD ";
                    sQuery += " AND B.SALE_DT = P.SALE_DT ";
                    sQuery += " AND B.STOR_CD = P.STOR_CD ";
                    sQuery += " AND B.POS_NO = P.POS_NO ";
                    sQuery += " AND B.TRADE_NO = P.REG_NO ";
                    sQuery += " AND P.CANCEL_DIV = '" + ItemConst.PAY_CANCEL_DIV.NORMAL + "'";
                }
                else
                {
                    sQuery += " SELECT CMP_CD,SALE_DT,STOR_CD,POS_NO,TRADE_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += "       ,SUM(FNS_FLG) AS FNS_FLG,MAX(FNS_SALE_DT) AS FNS_SALE_DT,MAX(FNS_POS_NO) AS FNS_POS_NO,MAX(FNS_TRADE_NO) AS FNS_TRADE_NO ";
                    sQuery += "   FROM ( ";
                    sQuery += " SELECT CMP_CD,SALE_DT,STOR_CD,POS_NO,TRADE_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += "       ,1  AS 'FNS_FLG','' AS 'FNS_SALE_DT','' AS 'FNS_POS_NO','' AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.TR_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD  = @CMP_CD ";
                    sQuery += "     AND STOR_CD = @STOR_CD ";
                    sQuery += "     AND POS_NO  = @POS_NO ";
                    sQuery += "     AND TRADE_DIV IN ('0') ";
                    sQuery += "     AND TRADE_KINDPER IN ('10') ";
                    sQuery += "     AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "     AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "'  ";
                    sQuery += " UNION ALL ";
                    sQuery += " SELECT CMP_CD,ORG_BILLDT,STOR_CD,ORG_BILL_POSNO,ORG_BILL_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += "       ,2  AS 'FNS_FLG',SALE_DT AS 'FNS_SALE_DT',POS_NO AS 'FNS_POS_NO',TRADE_NO AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.TR_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD  = @CMP_CD ";
                    sQuery += "     AND STOR_CD = @STOR_CD ";
                    sQuery += "     AND POS_NO  = @POS_NO ";
                    sQuery += "     AND TRADE_DIV IN ('0') ";
                    sQuery += "     AND TRADE_KINDPER IN ('11') ";
                    sQuery += "     AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "     AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "'  ";
                    sQuery += " UNION ALL ";
                    //#Rhee, 20180117 미주 예약픽업 추가 Start
                    sQuery += " SELECT CMP_CD,ORG_BILLDT,STOR_CD, ORG_BILL_POSNO,ORG_BILL_NO,RESERV_DT, TOTSALE_AMT, RESERV_NO ";
                    sQuery += "       ,3 AS 'FNS_FLG',SALE_DT AS 'FNS_SALE_DT',POS_NO AS 'FNS_POS_NO',TRADE_NO AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.TR_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD = @CMP_CD ";
                    sQuery += "      AND STOR_CD = @STOR_CD ";
                    sQuery += "      AND POS_NO = @POS_NO ";
                    sQuery += "      AND TRADE_DIV IN ('0') ";
                    sQuery += "      AND TRADE_KINDPER IN ('12') ";
                    sQuery += "      AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "      AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "' ";
                    sQuery += " UNION ALL ";
                    sQuery += " SELECT CMP_CD,ORG_BILLDT,STOR_CD,ORG_BILL_POSNO,ORG_BILL_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO, ";
                    sQuery += "       -3 AS 'FNS_FLG','' AS 'FNS_SALE_DT','' AS 'FNS_POS_NO','' AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.TR_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD = @CMP_CD ";
                    sQuery += "      AND STOR_CD = @STOR_CD ";
                    sQuery += "      AND POS_NO = @POS_NO ";
                    sQuery += "      AND TRADE_DIV IN ('1') ";
                    sQuery += "      AND TRADE_KINDPER IN ('12') ";
                    sQuery += "      AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "      AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "' ";
                    sQuery += " UNION ALL ";
                    //#Rhee, 20180117 미주 예약픽업 추가 End
                    sQuery += " SELECT CMP_CD,ORG_BILLDT,STOR_CD,ORG_BILL_POSNO,ORG_BILL_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += "       ,-1  AS 'FNS_FLG',''  AS 'FNS_SALE_DT',''  AS 'FNS_POS_NO',''  AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.TR_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD  = @CMP_CD ";
                    sQuery += "     AND STOR_CD = @STOR_CD";
                    sQuery += "     AND POS_NO  = @POS_NO ";
                    sQuery += "     AND TRADE_DIV IN ('1') ";
                    sQuery += "     AND TRADE_KINDPER IN ('10') ";
                    sQuery += "     AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "     AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "'  ";
                    sQuery += " UNION ALL ";
                    sQuery += " SELECT CMP_CD,ORG_BILLDT,STOR_CD,ORG_BILL_POSNO,ORG_BILL_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += "       ,-2  AS 'FNS_FLG',''  AS 'FNS_SALE_DT',''  AS 'FNS_POS_NO',''  AS 'FNS_TRADE_NO' ";
                    sQuery += "  FROM POSLOG.dbo.TR_SALE_HEADER ";
                    sQuery += " WHERE CMP_CD  = @CMP_CD ";
                    sQuery += "     AND STOR_CD = @STOR_CD ";
                    sQuery += "     AND POS_NO  = @POS_NO ";
                    sQuery += "     AND TRADE_NO IN ( ";
                    sQuery += "                      SELECT ORG_BILL_NO ";
                    sQuery += "                        FROM POSLOG.dbo.TR_SALE_HEADER ";
                    sQuery += "                       WHERE CMP_CD  = @CMP_CD ";
                    sQuery += "                         AND STOR_CD = @STOR_CD ";
                    sQuery += "                         AND POS_NO  = @POS_NO ";
                    sQuery += "                         AND TRADE_DIV IN ('1') ";
                    sQuery += "                         AND TRADE_KINDPER IN ('11') ";
                    sQuery += "                        AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "                        AND TRAIN_MODE_YN= '" + m_cPosStatus.Base.TrainingFlag + "'  ";
                    sQuery += "                      ) ";
                    sQuery += "     AND TRADE_DIV IN ('0') ";
                    sQuery += "     AND TRADE_KINDPER IN ('11') ";
                    sQuery += "     AND RESERV_DT = @FNS_FDATE ";
                    sQuery += "     AND TRAIN_MODE_YN = '" + m_cPosStatus.Base.TrainingFlag + "'  ";
                    sQuery += " ) A ";
                    sQuery += " GROUP BY CMP_CD,SALE_DT,STOR_CD,POS_NO,TRADE_NO,RESERV_DT,TOTSALE_AMT,RESERV_NO ";
                    sQuery += " ) B ";
                    sQuery += " LEFT OUTER JOIN POSLOG.DBO.TR_RESERV_INFO C ";
                    sQuery += "  ON B.CMP_CD = C.CMP_CD ";
                    sQuery += " AND B.SALE_DT = C.SALE_DT ";
                    sQuery += " AND B.STOR_CD = C.STOR_CD ";
                    sQuery += " AND B.POS_NO = C.POS_NO ";
                    sQuery += " AND B.TRADE_NO = C.TRADE_NO ";
                    sQuery += " AND B.RESERV_NO = C.RESERV_NO ";
                    sQuery += " LEFT OUTER JOIN POSLOG.DBO.TR_SALE_PAY P ";
                    sQuery += "  ON B.CMP_CD = P.CMP_CD ";
                    sQuery += " AND B.SALE_DT = P.SALE_DT ";
                    sQuery += " AND B.STOR_CD = P.STOR_CD ";
                    sQuery += " AND B.POS_NO = P.POS_NO ";
                    sQuery += " AND B.TRADE_NO = P.TRADE_NO ";
                    sQuery += " AND P.CANCEL_DIV = '" + ItemConst.PAY_CANCEL_DIV.NORMAL + "'";
                }

                sQuery += " WHERE 1 = 1 ";

                sQuery += " AND C.ONOFF_DIV   = '" + sOnOffLine + "'";

                //#Rhee, 20180119 검색조건 변경 start
                //기존
                //if (sTradType != "4" && sTradType != "" && sTradType != "5" && sTradType != "6")
                //{
                //    sQuery += "    AND B.FNS_FLG = '" + sTradType + "'";
                //}
                //변경
                if ((sTradType == "1" || sTradType == "3" || sTradType == "4") && sTradType != "")
                {
                    sQuery += "    AND B.FNS_FLG = '" + sTradType + "'";
                }
                else if ((sTradType == "2" || sTradType == "0") && sTradType != "")
                {
                    sQuery += "    AND B.FNS_FLG IN ('0','2')";
                }
                //#Rhee, 20180119 검색조건 변경 end

                if (sCondition != "")
                {
                    sQuery += "    AND C.CUST_PHN_NO   LIKE '%" + sCondition + "%'";
                }

                sQuery += " GROUP BY C.RESERV_DT, C.RESERV_TM, C.RESERV_REG_DT, C.RESERV_REG_TM, B.TOTSALE_AMT ";
                sQuery += "         ,B.CMP_CD, B.SALE_DT, B.STOR_CD, B.POS_NO,B.TRADE_NO,  B.RESERV_NO, B.FNS_FLG ";
                sQuery += "         ,C.CUSTNM, C.CUST_PHN_NO, C.RESERV_MEMO ";

                //#Rhee, 20180119 예약 전액결제, 부분결제 조회 추가 start
                // 기존
                /*
                // 부분결제                
                if (sTradType == "5" )
                {
                    sQuery += " HAVING SUM((CASE WHEN P.PAY_WAY_CD + PAY_DTL_CD_01 = '1501' THEN PAY_AMT ELSE 0 END)) > 0 ";
                }

                // 전체 결제
                if (sTradType == "6")
                {
                    sQuery += " HAVING SUM((CASE WHEN P.PAY_WAY_CD + PAY_DTL_CD_01 = '1501' THEN PAY_AMT ELSE 0 END)) = 0 ";
                }
                */
                // 변경
                sQuery += " ) AS TMP_RESERV WHERE 1=1 ";
                // 부분결제                
                if (sTradType == "5")
                {
                    sQuery += " AND BEFORE != '0.00' ";
                }
                // 전체 결제
                if (sTradType == "6")
                {
                    sQuery += " AND BEFORE = '0.00' ";
                }
                //#Rhee, 20180119 예약 전액결제, 부분결제 조회 추가 end

                return m_cMstService.Select(new string[] { sQuery });
            }
            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="sSaleDate"></param>
        /// <param name="sPosNo"></param>
        /// <param name="sTradeNo"></param>
        /// <returns></returns>
        public DataTable SearchReservedFinshPrint(string sSaleDate, string sPosNo, string sTradeNo)
        {
            string sQuery = "";
            try
            {

                if (m_cPosStatus.Base.TrainingFlag == "1")
                {
                    sQuery = " SELECT JOURNAL_DATA ";
                    sQuery += "   FROM POSLOG..ETC_SALE_JOURNAL ";
                    sQuery += "  WHERE CMP_CD   = '" + m_cPosStatus.Base.CmpCd + "'";
                    sQuery += "    AND SALE_DT  = '" + sSaleDate + "'";
                    sQuery += "    AND STOR_CD  = '" + m_cPosStatus.Base.StoreNo + "'";
                    sQuery += "    AND POS_NO   = '" + sPosNo + "'";
                    sQuery += "    AND REG_NO   = '" + sTradeNo + "'";
                }
                else
                {
                    sQuery = " SELECT JOURNAL_DATA ";
                    sQuery += "   FROM POSLOG..TR_SALE_JOURNAL ";
                    sQuery += "  WHERE CMP_CD   = '" + m_cPosStatus.Base.CmpCd + "'";
                    sQuery += "    AND SALE_DT  = '" + sSaleDate + "'";
                    sQuery += "    AND STOR_CD  = '" + m_cPosStatus.Base.StoreNo + "'";
                    sQuery += "    AND POS_NO   = '" + sPosNo + "'";
                    sQuery += "    AND TRADE_NO = '" + sTradeNo + "'";
                }

                return m_cMstService.Select(new string[] { sQuery });
            }
            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="sPrtData"></param>
        /// <returns></returns>
        public bool PrintReservedFinsh(string sPrtData)
        {
            //// 영수증 출력
            return m_cSaleComplete.PosSaleRePrint(sPrtData);
        }

        /// <summary>
        /// 예약 거래 로드
        /// </summary>
        /// <param name="sSaleDate"></param>
        /// <param name="sPosNo"></param>
        /// <param name="sTradeNo"></param>
        /// <param name="bRetrun"></param>
        /// <returns></returns>
        public bool LoadLocalTran(string sSaleDate, string sPosNo, string sTradeNo, bool bRetrun)
        {
            bool bRet = false;

            try
            {
                m_cDataService.TranComplete();

                // 해더정보 로딩
                if (LoadLocalTrHeader(sSaleDate, sPosNo, sTradeNo) == false)
                {
                    WinManager.ErrorMessage(POS_MESSAGE.ERROR.MSG_0248);
                    m_cDataService.TranComplete();
                    return bRet;
                }

                // 상품정보 로딩
                LoadLocalTrDetail(sSaleDate, sPosNo, sTradeNo);

                // 결제정보 로딩
                LoadLocalTrPayment(sSaleDate, sPosNo, sTradeNo);

                // 기타정보 로딩
                LoadLocalTrEtc(sSaleDate, sPosNo, sTradeNo);

                // 캠페인정보 로딩
                LoadLocalTrCampaign(sSaleDate, sPosNo, sTradeNo);

                // 매출객수정보 로딩
                LoadLocalTrGuestCnt(sSaleDate, sPosNo, sTradeNo);

                // 예약 고객 정보 로딩
                LoadLocalTrReservInfo(sSaleDate, sPosNo, sTradeNo);

                if (bRetrun == false) 
                {
                    // 선수금 체크 결제 수단 재 정의
                    PayAdvanCheck();
                }
                

                bRet = true;
            }
            catch (Exception ex)
            {
                WinManager.ExceptionMessage(System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
                    System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()", ex.Message);
                m_cDataService.TranComplete();
            }
            return bRet;
        }

        /// <summary>
        /// 선수금 결제 수단 재 정의
        /// </summary>
        /// <returns></returns>
        public bool PayAdvanCheck()
        {
            bool bRet = false;

            try
            {

                //예약 결제 재 정의
                ArrayList aPayItem = (ArrayList)StateObject.GetItemObject(Column.TR_PAYMENT.ITEM);

                // 결제 내역 받아 오기
                Column.TR_PAYMENT.DATA m_cPayItem = new Column.TR_PAYMENT.DATA();
                
                //기본 저장 변수 만들기
                m_cPayItem.PAY_WAY_CD = ItemConst.TR_ITEM_ID.RESERVATION_ITEM;                  //결제 그룹 코드
                m_cPayItem.PAY_DTL_CD_01 = ItemConst.TR_ITEM_ID.RESERVATION.RESERVATION_PAY;    //결제 코드
                m_cPayItem.OCCUR_ENTRY_20 = MessageManager.GetLabelMessage(POS_MESSAGE.LABEL.MSG_0282);      //결제 수단명

                m_cPayItem.PAY_AMT = 0;                                     //결제 금액
                m_cPayItem.AMT_ENTRY_01 = 0;                                //받은돈
                m_cPayItem.AMT_ENTRY_02 = 0;                                //거스름
                m_cPayItem.PAY_DTL_CD_05 = PosConst.PAY_DC_TYPE.PAY;
                m_cPayItem.SEQ = 1;
                m_cPayItem.CANCEL_DIV = "";

                foreach (Column.TR_PAYMENT.DATA cPayItem in aPayItem)
                {
                    if (!(cPayItem.PAY_WAY_CD == ItemConst.TR_ITEM_ID.RESERVATION_ITEM && cPayItem.PAY_DTL_CD_01 == ItemConst.TR_ITEM_ID.RESERVATION.BALANCE_PAY))
                    {

                        //값 입력
                        if (cPayItem.OCCUR_ENTRY_21 == ItemConst.PAY_CASHBILL_DIV.YES)
                        {
                            m_cPayItem.OCCUR_ENTRY_21 = ItemConst.PAY_CASHBILL_DIV.YES;
                            m_cPayItem.AMT_ENTRY_10 = CmUtil.DoubleAdd(m_cPayItem.AMT_ENTRY_10, cPayItem.PAY_AMT);
                        }

                        m_cPayItem.PAY_AMT = CmUtil.DoubleAdd(m_cPayItem.PAY_AMT, cPayItem.PAY_AMT);
                        m_cPayItem.AMT_ENTRY_01 = m_cPayItem.PAY_AMT;                        
                        m_cPayItem.CANCEL_DIV = PosConst.CANCEL_DIV.NORMAL;
                    }
                }

                aPayItem.Clear();

                if (m_cPayItem.CANCEL_DIV == PosConst.CANCEL_DIV.NORMAL)
                {
                    aPayItem.Add(m_cPayItem);
                }

                bRet = true;
            }
            catch (Exception ex)
            {
                WinManager.ExceptionMessage(System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
                    System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()", ex.Message);
                m_cDataService.TranComplete();
            }

            return bRet;
        }


        /// <summary>
        /// 예약 거래 로드(서버)
        /// </summary>
        /// <param name="sSaleDate"></param>
        /// <param name="sPosNo"></param>
        /// <param name="sTradeNo"></param>
        /// <param name="bRetrun"></param>
        /// <returns></returns>
        public string SearchServerTran(string sSaleDate, string sPosNo, string sTradeNo, bool bRetrun)
        {
            string sStat = UserCom.RST_ERR;
            bool bRet = false;

            try
            {
                m_cDataService.TranComplete();

                // 거래 데이터 서버 조회
                Hashtable htRecvData = new Hashtable();

                sStat = ExecServerTranCallIrt(ItemConst.IRT_INQ_TYPE.TRAN_CALL, sSaleDate, sPosNo, sTradeNo, "", ref htRecvData);
                if (sStat != UserCom.RST_OK)
                {
                    WinManager.ErrorMessage(POS_MESSAGE.ERROR.MSG_0259);
                    return sStat;
                }

                if (htRecvData[Column.IQ_TRAN_CALL_REQ.DATA.RES_CD].ToString() != "00")
                {
                    if (htRecvData[Column.IQ_TRAN_CALL_REQ.DATA.RES_CD].ToString() == "09")
                        WinManager.ErrorMessage(POS_MESSAGE.ERROR.MSG_0248);    // 해당거래 없음
                    else
                        WinManager.ErrorMessage(MessageManager.GetErrorMessage(POS_MESSAGE.ERROR.MSG_0259) + "\n\r ERROR CODE => " + htRecvData[Column.IQ_TRAN_CALL_REQ.DATA.RES_CD]);
                    return UserCom.RST_ERR;
                }

                // 거래데이터
                string sTranData = htRecvData[Column.IQ_TRAN_CALL_REQ.DATA.TRAN_DATA].ToString();

                m_cTrnStatus.Head.CmpCd = m_cPosStatus.Base.CmpCd;          // CMP_CD
                m_cTrnStatus.Head.StoreCd = m_cPosStatus.Base.StoreNo;      // STOR_CD
                m_cTrnStatus.Head.SaleDate = sSaleDate;                     // SALE_DT
                m_cTrnStatus.Head.PosNo = sPosNo;                           // POS_NO
                m_cTrnStatus.Head.TradeNo = htRecvData[Column.IQ_TRAN_CALL_REQ.DATA.TRADE_NO].ToString();                       // TRADE_NO

                // 거래 분해 처리
                string[] aTranData = sTranData.Split(new string[] { "#~" }, StringSplitOptions.None);
                for (int i = 0; i < aTranData.Length; i++)
                {
                    if (aTranData[i].StartsWith(ItemConst.IRT_TRAN_TYPE.TranHeader))
                        bRet = TranParserToHeader(aTranData[i]);
                    else if (aTranData[i].StartsWith(ItemConst.IRT_TRAN_TYPE.TranDetail))
                        bRet = TranParserToDetail(aTranData[i]);
                    else if (aTranData[i].StartsWith(ItemConst.IRT_TRAN_TYPE.TranPay))
                        bRet = TranParserToPayment(aTranData[i]);
                    else if (aTranData[i].StartsWith(ItemConst.IRT_TRAN_TYPE.TranCpiRslt))
                        bRet = TranParserToCampaign(aTranData[i]);
                    else if (aTranData[i].StartsWith(ItemConst.IRT_TRAN_TYPE.TranEtc))
                        bRet = TranParserToEtc(aTranData[i]);
                    else if (aTranData[i].StartsWith(ItemConst.IRT_TRAN_TYPE.TranGuestCnt))
                        bRet = TranParserToGuestCnt(aTranData[i]);
                    else if (aTranData[i].StartsWith(ItemConst.IRT_TRAN_TYPE.TranReservInfo))
                        bRet = TranParserToTrReservInfo(aTranData[i]);

                    if (bRet == false)
                    {
                        m_cDataService.TranComplete();
                        return sStat;
                    }
                }

                if (bRetrun == false)
                {
                    // 선수금 체크 결제 수단 재 정의
                    PayAdvanCheck();
                }
                
                sStat = UserCom.RST_OK;
            }
            catch (Exception ex)
            {
                WinManager.ExceptionMessage(System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.Name,
                    System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()", ex.Message);
                m_cDataService.TranComplete();
            }
            return sStat;
        }

        
    }


}