环企查询线上cloud门票入园记录

SELECT 
        CASE WHEN LEN(GFA.SREMARK) = 18 THEN GFA.SREMARK 
        WHEN LEN(GFTOD.BindCard) = 18 THEN GFTOD.BindCard 
        WHEN TWB.TWB_MSG = 'IDCARD' AND LEN(TWB.TWB_ID) = 18 THEN TWB.TWB_ID 
        ELSE NULL END AS 身份证号 ,
        CASE WHEN TWB.TWB_MSG='ADMIN' THEN DT.DROPUP_TYPENAME 
        WHEN TWB.TWB_MSG='OLINETICKET' AND (SELECT COUNT(*) FROM TWB_TRN_ONLINE WHERE ECODE=TWB.TWB_ID)>0 THEN  TTO.TICKETNAME
        WHEN  TWB.TWB_MSG='OLINETICKET' AND (SELECT COUNT(*) FROM TWB_TRN_ONLINE WHERE ECODE=TWB.TWB_ID)=0  THEN '未获取到名称'
        WHEN  TWB.TWB_MSG='AGINETICKET'  THEN '二次入园门票'
        WHEN  TWB.TWB_MSG='IDCARD'  THEN '身份证直接入园'
        ELSE TBI.STICKETNAMECH 
        END AS 项目名称,
        CONVERT(varchar(100),TWB.TWB_SYSTIME,23) 入园日期,
        CONVERT(varchar(100),TWB.TWB_SYSTIME,24) 入园时间,
        Convert(int,Datename(hour,TWB.TWB_SYSTIME))  AS  小时段,
         TWB.INPARKCOUNT 入园次数, TWB.PEOPLECOUNT 人数,
       datename(w,TWB.TWB_SYSTIME) 星期,
       DATENAME(YEAR,TWB.TWB_SYSTIME) AS 年,
       DATENAME(MONTH,TWB.TWB_SYSTIME) AS 月,
       DATENAME(DAY,TWB.TWB_SYSTIME) AS 日,

       CASE WHEN GFA.NINCOMETYPE=1 OR GFA.NINCOMETYPE=5 OR GFA.NINCOMETYPE=6 OR GFA.NINCOMETYPE=7 THEN TWB.TWB_ID  
        WHEN  GFA.NINCOMETYPE=2 THEN MFCRD_MANUALNO
        WHEN GFA.NINCOMETYPE= 3 OR GFA.NINCOMETYPE=4 THEN GFA.SREMARK
        WHEN TWB.TWB_MSG='ADMIN' THEN TWB.TWB_ID
        WHEN TWB.TWB_MSG='OLINETICKET' THEN TWB.TWB_ID
        WHEN TWB.TWB_MSG='AGINETICKET' THEN TWB.TWB_ID
        WHEN TWB.TWB_MSG='EMPCARD'  THEN TWB.TWB_ID
        WHEN TWB.TWB_MSG='IDCARD' THEN TWB.TWB_ID 
        END AS 项目编码,

        CASE WHEN GFA.NINCOMETYPE=1 THEN '门票' 
        WHEN GFA.NINCOMETYPE=2 THEN '年卡' 
        WHEN GFA.NINCOMETYPE=3 THEN
         (SELECT DICT_VALUE FROM DICT_TABLE
        WHERE DICT_TYPE_ID='MAKETICKETMODE' AND DICT_KEY=2)
        WHEN GFA.NINCOMETYPE=4 THEN 
         (SELECT DICT_VALUE FROM DICT_TABLE
        WHERE DICT_TYPE_ID='MAKETICKETMODE' AND DICT_KEY=3)
        WHEN GFA.NINCOMETYPE=5 THEN GFD.SPMODE
         WHEN GFA.NINCOMETYPE=6  THEN GFD.SPMODE
        WHEN TWB.TWB_MSG='ADMIN' THEN '管理卡'
        WHEN TWB.TWB_MSG='OLINETICKET' THEN '线上门票'   
        WHEN TWB.TWB_MSG='AGINETICKET' THEN '二次入园' 
        WHEN TWB.TWB_MSG='EMPCARD'  THEN '员工卡' 
        WHEN GFA.NINCOMETYPE=7 THEN 'TVM售票'
        WHEN TWB.TWB_MSG='IDCARD' THEN '身份证入园' 
        END AS 项目类型,
        GFA.NDEALID 交易号,
         CASE WHEN GFA.NINCOMETYPE=2 THEN CARD.MFCRD_NAME 
        WHEN DT.DROPUP_STATUS='ADMIN' THEN  EMP.EMP_NAME 
        WHEN DT.DROPUP_STATUS='Up' THEN  EMP.EMP_NAME 
        WHEN DT.DROPUP_STATUS='Drop' THEN  EMP.EMP_NAME 
        WHEN DT.DROPUP_STATUS='ACCREDIT' THEN  EMP.EMP_NAME 
        end 持卡人,
         TBI.NGENERALPRICE AS 价格,
         DEVICE.SDEVICENAME 检票终端,
        GZONE.SGZONENAME 检票点, A.SPARKNAME 景区, COM.SCOMPANYNAME 公司,
        ISNULL(GSFO.Travel,'线下窗口') 渠道, GSFO.OrderNo as 订单号

    FROM  TWB_TRN TWB  WITH(NOLOCK) 
    LEFT JOIN TWB_TRN_ONLINE TTO  WITH(NOLOCK) ON TWB.TWB_ID =TTO.ECODE
    LEFT JOIN GS_F_ACCESS GFA  WITH(NOLOCK) ON TWB.TWB_ID =GFA.SBARCODE
    LEFT JOIN GS_F_DEALINFO GFD  WITH(NOLOCK) ON GFD.NDEALID =GFA.NDEALID
    LEFT JOIN MFYEARCRD_TBL CARD  WITH(NOLOCK)  ON CARD.SBARCODE =GFA.SBARCODE
    LEFT JOIN GS_T_TICKETBASEINFO TBI  WITH(NOLOCK) ON TBI.NTICKETID=GFA.NTICKETID
    LEFT JOIN GS_C_DEVICE DEVICE  WITH(NOLOCK) ON DEVICE.NDEVICEID = TWB.TWB_GATE
    LEFT JOIN GS_C_GZONE GZONE  WITH(NOLOCK) ON GZONE.NGZONEID =DEVICE.NGZONEID
    LEFT JOIN GS_C_PARK A  WITH(NOLOCK) ON A.NPARKID = GZONE.NPARKID 
    LEFT JOIN GS_C_COMPANY COM  WITH(NOLOCK) ON COM.NCOMPANYID = A.NCOMPANYID
    LEFT JOIN DROPUP_TBL DT  WITH(NOLOCK) ON DT.DROPUP_ID=TWB.TWB_ID
    LEFT JOIN SAC_employee EMP  WITH(NOLOCK) ON EMP.EMP_ID=DT.DROPUP_USER
    LEFT JOIN GS_F_ThirdOnline GSFO  WITH(NOLOCK) on GSFO.NDEALID = GFD.NDEALID
    LEFT JOIN GS_F_ThirdOnlineDetail GFTOD  WITH(NOLOCK) ON GFTOD.SBARCODE = GFA.SBARCODE
    WHERE twb_msg <>'OUT' and TWB_MSG <> 'EMPCARD'  and   GSFO.OrderNo ='1052308190000001559'
landv / landv.github.io

环企查询线上cloud门票入园记录 #85
