USE [THPeRoute2]
GO
/****** Object:  StoredProcedure [dbo].[sp_Sync_E_ReportSurveyProgram]    Script Date: 12/11/2018 12:21:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<tuan.doan@dmspro.vn>
-- Create date: <6/12/2018>
-- Description:	<Lấy thông tin cho báo cáo kết quả khảo sát>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Sync_E_ReportSurveyProgram] @Date DATE
AS
BEGIN

    --------------- Sale Force(Doi ngu ban hang) [start]----------------
    SELECT RSMID = SF.RSMID,
           RSMName = SF.RSM,
           ASMID = SF.ASMID,
           ASMName = SF.ASM,
           SaleSupID = SF.SaleSupID,
           SaleSupName = SF.SalePersonName,
           SaleManID = SF.SalePersonID,
           SaleManCD = SF.SalePersonCD,
           SaleManName = SF.SalePersonName,
           DistributorID = SF.CompanyID,
           DistributorCD = SF.CompanyCD,
           DistributorName = SF.CompanyName,
           RegionID = SF.RegionID,
           RegionName = SF.RegionName,
           AreaID = SF.AreaID,
           AreaName = SF.AreaName,
           RouteID = SF.RouteID,
           BaselineDate = CONVERT(DATE, SF.BaselineDate)
    INTO #SaleForce
    FROM [THPDMSMasterData2].dbo.DMSBLSalesForce SF -- doi ngu ban hang
    WHERE CONVERT(DATE, SF.BaselineDate) = @Date;
    --------------- Sale Force(Doi ngu ban hang) [end]----------------

	select * into #DMSPOSMQuestion from [core].THPDMS2.dbo.DMSPOSMQuestion where companyid=3
	select * into #DMSPOSMQuestionAnswer from [core].THPDMS2.dbo.DMSPOSMQuestionAnswer where companyid=3
	select * into #DMSOutletSurvey from [core].THPDMS2.dbo.DMSOutletSurvey where companyid=3
    -----Tổng hợp chương trình khảo sát [start]--------------

    SELECT WorkingDate = ASA.WorkingDate, -- (1) Ngày khảo sát
           SO.RSMID,                      -- (2) Ma RSM
           SO.RSMName,                    -- (3) Ten RSM
           SO.ASMID,                      -- (4) Ma ASM
           SO.ASMName,                    -- (5) Ten ASM
           SO.SaleSupID,                  -- (6) ID Sale Sup
           SO.SaleSupName,                -- (7) Ten Sale Sup
           SO.SaleManID,                  -- (8) ID Sale man
           SO.SaleManCD,                  -- (9)  Ma Sale Man
           SO.SaleManName,                -- (10) Ten Sale Man
           CustomerCD = ASA.CustomerCD,   -- (11) Ma khach hang
           CustomerName = OL.OutletName,  -- (12) Ten khach hang
           SO.DistributorID,              -- (13) ID NPP
           SO.DistributorCD,              -- (14) Ma NPP
           SO.DistributorName,            -- (15) Ten NPP
           SO.RegionID,                   -- (16) ID Vung
           SO.RegionName,                 -- (17) Vung
           SO.AreaID,                     -- (18) ID Khu Vuc
           SO.AreaName,                   -- (19) Khu vuc
           SO.RouteID,                    -- (20) ID Route
           StartTime = ACVT.BeginTime,    -- (21) Thời gian bắt đầu khảo sát
           EndTime = ACVT.EndTime,        -- (22) Thời gian kết thúc khảo sát
           EmployeeCD = ASA.UserName,     -- (23) Mã nhân viên đi khảo sát
           QuestionID = ASA.QuestionID,   -- (24) ID câu hỏi
           QuestionCD = PQ.QuestionCD,    -- (25) Ma câu hỏi
           Question = PQ.Descr,           -- (26) Câu hỏi
           AnswerID = ASA.AnswerID,       -- (27) ID Câu hỏi
           AnswerCD = PQA.AnswerCD,       -- (28) Mã câu trả lời
           Answer = PQA.Descr,            -- (29) Câu trả lời
           Result = CASE
                        WHEN ASA.AnswerID = PQA.AnswerCD THEN
                            'X'
                        ELSE
                            ''
                    END,                  -- (30) Ket qua khao sat
           SurveyCD = OS.SurveyCD,        -- (31) Mã khảo sát
           SurveyName = OS.Descr,         -- (32) Chương trình khảo sát
           SurveyType = OS.Type           -- (33) Loại khảo sát

    INTO #SurveyProgram -- Tổng hợp những thông tin đầy đủ cho báo cáo
    FROM THPSFA2.dbo.DMSAimSurveyAnswer ASA -- kết quả khảo sát SFA (dung cho sale man)
        INNER JOIN #DMSPOSMQuestion PQ -- Định nghĩa câu hỏi
            ON ASA.QuestionID = PQ.QuestionCD
        INNER JOIN #DMSPOSMQuestionAnswer PQA -- Định nghĩa câu trả lời
            ON PQA.QuestionID = PQ.QuestionID
               AND PQA.CompanyID = PQ.CompanyID
        INNER JOIN #SaleForce SO --Doi ngu ban hang
            ON SO.RouteID = ASA.RouteCD
               AND SO.SaleManCD = ASA.UserName
               AND CONVERT(DATE, SO.BaselineDate) = CONVERT(DATE, ASA.WorkingDate)
        INNER JOIN THPDMSMasterData2.dbo.DMSOutlets OL
            ON ASA.CustomerCD = OL.OutletCD
               AND OL.RouteCD = ASA.RouteCD
        INNER JOIN #DMSOutletSurvey OS --- Định nghĩa CTKS(Chương trinh khảo sát)
            ON ASA.CampaignID = OS.SurveyCD
        INNER JOIN THPSFA2.dbo.DMSAimCustomerVisitedTime ACVT --Thời gian khảo sát
            ON ACVT.CustomerCD = ASA.CustomerCD
               AND ACVT.VisitID = ASA.VisitID
    WHERE CONVERT(DATE, ASA.WorkingDate) = @Date;
    -----Tổng hợp chương trình khảo sát [end]--------------

    DELETE FROM dbo.E_ReportSurveyProgram
    WHERE WorkingDate = @Date;


    INSERT INTO dbo.E_ReportSurveyProgram
    (
        WorkingDate,
        RSMID,
        RSMName,
        ASMID,
        ASMName,
        SaleSupID,
        SaleSupName,
        SaleManID,
        SaleManCD,
        SaleManName,
        CustomerCD,
        CustomerName,
        DistributorID,
        DistributorCD,
        DistributorName,
        RegionID,
        RegionName,
        AreaID,
        AreaName,
        RouteID,
        StartTime,
        EndTime,
        EmployeeCD,
        QuestionID,
        QuestionCD,
        Question,
        AnswerID,
        AnswerCD,
        Answer,
        Result,
        SurveyCD,
        SurveyName,
        SurveyType
    )
    SELECT *
    FROM #SurveyProgram;

    DROP TABLE #SaleForce;
    DROP TABLE #SurveyProgram;
END;