linche0859
10/2/2019 - 2:28 AM

EIP評核無法顯示

找出Null項目

DECLARE @WangNo  VARCHAR(10) = '143517', 
        @StroeNo VARCHAR(10) = '112011' 
DECLARE @AgentList TABLE 
  ( 
     wangno VARCHAR(10), 
     deptno VARCHAR(10) 
  ) 
DECLARE @RtnResult TABLE 
  ( 
     career         NVARCHAR(100), 
     storeno        VARCHAR(10), 
     storename      NVARCHAR(100), 
     trackcount     INT, 
     trackovercount INT 
  ) 

INSERT INTO @AgentList 
SELECT A.wangno, 
       B.deptno 
FROM   oms_agentlist A 
       LEFT JOIN v2_colleagues_view B 
              ON B.wangno = A.wangno 
WHERE  A.agent = @WangNo 
       AND B.workstateno = '00' 
       AND A.starttime <= Getdate() 
       AND A.endtime >= Getdate() 
UNION 
SELECT wangno, 
       deptno 
FROM   v2_colleagues_view 
WHERE  wangno = @WangNo 
       AND workstateno = '00' 

--Insert Into @RtnResult 
SELECT A.id, 
       B2.question + '-' + B.question     Question, 
       CONVERT(VARCHAR, A.starttime, 111) StartTime, 
       A.trackid, 
       B1.trackid                         ParentTrackId 
FROM   oms_questionnairestoretrackmaster A 
       LEFT JOIN oms_questionnairedetail B 
              ON B.questionnaireno = A.questionnaireno 
                 AND B.version = A.version 
                 AND B.seqno = A.seqno 
       LEFT JOIN oms_questionnairestoretrackmaster B1 
              ON B1.id = A.id 
                 AND B1.questionnaireno = B.questionnaireno 
                 AND B1.version = B.version 
                 AND B1.seqno = B.parentseqno 
       LEFT JOIN oms_questionnairedetail B2 
              ON B2.questionnaireno = B1.questionnaireno 
                 AND B2.version = B1.version 
                 AND B2.seqno = B1.seqno 
       LEFT JOIN oms_questionnairestorelist C 
              ON C.id = A.id 
       LEFT JOIN oms_questionnairestoretrackdetail D 
              ON D.trackid = A.trackid 
                 AND D.trackreid = 0 
       LEFT JOIN oms_questionnairestoretrackdetail E 
              ON E.trackid = A.trackid 
                 AND E.trackreid = 1 
WHERE  A.finishdate IS NULL 
       AND A.starttime IS NOT NULL 
       AND A.isdel = 0 
       AND A.starttime IS NOT NULL 
       AND B.scoretypeid NOT IN ( '000', '001' ) 
       AND A.storeno = @StroeNo 
       AND ( 
           --店經理 
           A.storeno IN (SELECT s1.storeno 
                         FROM   storemanagercontanctlist_view s1 
                         WHERE  s1.storeleader IN (SELECT wangno 
                                                   FROM   @AgentList)) 
            OR 
           --區經理 
           ( A.assessment = '003' 
             AND A.storeno IN (SELECT s1.storeno 
                               FROM   storemanagercontanctlist_view s1 
                               WHERE  s1.areamanager IN (SELECT wangno 
                                                         FROM   @AgentList)) ) 
            OR 
           --區督導 
           ( A.assessment = '002' 
             AND A.storeno IN (SELECT s1.storeno 
                               FROM   storemanagercontanctlist_view s1 
                               WHERE  s1.arealeader IN (SELECT wangno 
                                                        FROM   @AgentList)) ) 
            OR 
           --自己部門 
           ( A.assessmentdepartmentid = '000' 
             AND A.assessmentdepartmentid IN (SELECT deptno 
                                              FROM   @AgentList) ) ) 

找出評核主檔

SELECT TOP 1000 [TrackId]
      ,[Id]
      ,[StoreNo]
      ,[QuestionnaireNo]
      ,[Version]
      ,[SeqNo]
      ,[QuestionScore]
      ,[GetScore]
      ,[GetMemo]
      ,[GetYnAncer]
      ,[AssessmentDepartmentId]
      ,[Assessment]
      ,[StartTime]
      ,[EndTime]
      ,[FinishDate]
      ,[PreFinishDate]
      ,[IsDel]
      ,[Longitude]
      ,[Latitude]
      ,[CreateUser]
      ,[CreateDate]
      ,[UpdateUser]
      ,[UpdateDate]
  FROM [BMS].[dbo].[OMS_QuestionnaireStoreTrackMaster]
  where Id='12974' and TrackId='176404'

找出父項目

SELECT TOP 1000 [QuestionnaireNo]
      ,[Version]
      ,[SeqNo]
      ,[ScoreTypeId]
      ,[ParentSeqNo]
      ,[Question]
      ,[QuestionMemo]
      ,[QuestionScore]
      ,[Sort]
      ,[CreateUser]
      ,[CreateDate]
      ,[UpdateUser]
      ,[UpdateDate]
  FROM [BMS].[dbo].[OMS_QuestionnaireDetail]
  where [QuestionnaireNo] = '201801120001' and Version='002' and [SeqNo] = '42'

寫入評核主檔

INSERT INTO [dbo].[OMS_QuestionnaireStoreTrackMaster]
           ([Id]
           ,[StoreNo]
           ,[QuestionnaireNo]
           ,[Version]
           ,[SeqNo]
           ,[QuestionScore]
           ,[GetScore]
           ,[GetMemo]
           ,[GetYnAncer]
           ,[AssessmentDepartmentId]
           ,[Assessment]
           ,[StartTime]
           ,[EndTime]
           ,[FinishDate]
           ,[PreFinishDate]
           ,[IsDel]
           ,[Longitude]
           ,[Latitude]
           ,[CreateUser]
           ,[CreateDate]
           ,[UpdateUser]
           ,[UpdateDate])
     VALUES
           ('12974'
           ,'112011'
           ,'201801120001'
           ,'002'
           ,'35'
           ,0
           ,0
           ,''
           ,0
           ,'19'
           ,'002'
           ,'2019-09-27 20:09:38.737'
           ,'2019-09-27 20:09:38.737'
           ,null
           ,null
           ,0
           ,null
           ,null
           ,'047825'
           ,'2019-09-27 20:09:37.647'
           ,'047825'
           ,'2019-09-27 20:09:37.647')