USE [SFS2]
GO
/****** Object:  StoredProcedure [dbo].[p_ASM_Call_all]    Script Date: 7/12/2018 8:38:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_ASM_Call_all]

AS
    BEGIN
        DECLARE @CompanyID INT;
        SELECT  @CompanyID = CompanyID
        FROM    dbo.DMSAimStagingConfig;
    --Insert danh sach User :
        DELETE  dbo.asmUser

        INSERT  INTO dbo.asmUser
                ( Username ,
                  Password ,
                  Name ,
                  Role ,
                  LastSync ,
                  Type ,
                  Latitude ,
                  Longtitude ,
                  Distance ,
                  CheckDistance ,
                  ByPass ,
                  ByPassStartTime ,
                  ByPassEndTime ,
                  SyncStartTime ,
                  SyncEndTime ,
                  RouteCD ,
                  DistributorID ,
                  PlanCheck
                )
                SELECT  DISTINCT
                        sf.EmployeeCD , -- Username - nvarchar(50)
                        --'12345' ,--s.Password , -- Password - nvarchar(50)
                        sf.EmployeeCD , -- Username - nvarchar(50)--tạm thòi thay password
                        sf.Descr , -- Name - nvarchar(100)
                        CASE h.SFHierachyCD
                          WHEN 'SCS' THEN 'SUP'
                          WHEN 'ASCM' THEN 'ASM'
                          ELSE h.SFHierachyCD --ko xay ra
                        END , -- Role - nvarchar(50)
                        CONVERT(VARCHAR(30), GETDATE(), 121) LastSync , -- LastSync - nvarchar(100)
                        'WW' ,
                        '1.0' ,
                        '1.0' ,
                        ISNULL(DistSyncDistance, 25) ,
                        ISNULL(DistSyncGPSLock, 1) ,
                        ( CASE ISNULL(DistDistSyncByPass, 0)
                            WHEN 1 THEN 'Normal'
                            ELSE 'NotByPass'
                          END ) ,
                        '00:00:01' ,--'00:00:00'
                        '23:59:00' ,--'00:00:00'
                        '06:00:00' ,
                        ISNULL(sf.TimeSync, '00:00') + ':00' ,
                        '' AS RouteCD ,
                        '0' AS DistributorID ,
                        ISNULL(PlanCheck, 0)  --0|1|2: ko bat buoc|bat buoc|byPass (Acu 2 giá trị 0|1 :bypass(2) được update = cách khác)
                FROM    [10.86.67.15].THPDMS2.dbo.DMSSalesForce sf
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.DMSSFHierachy h ON sf.CompanyID = h.CompanyID
                                                              AND sf.SFHierachyID = h.SFHierachyID
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.Users s ON sf.LoginID = s.Username
                                                          AND sf.CompanyID = s.CompanyID
                                                          AND s.Username IS NOT NULL
                WHERE   sf.CompanyID = @CompanyID
                        AND h.SFHierachyCD IN ( 'SCS', 'ASCM' )
                        AND Active = 1




    --Insert danh sach SalesSUP
        DELETE  dbo.asmSup

        INSERT  INTO dbo.asmSup
                ( ASMCode ,
                  SupCode ,
                  SupName
                )
                SELECT DISTINCT
                        asm.Username ,
                        ss.Username ,
                        ss.Name
                FROM    dbo.asmUser ss
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.DMSSalesForce sf ON ss.Username = sf.EmployeeCD
                                                              AND sf.Active = 1 --CHi lay SS active
                                                              AND ss.Role = 'SUP'
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.DMSRoute r ON sf.EmployeeID = r.SalesForceID
                                                             AND r.CompanyID = sf.CompanyID
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.DMSViewSFWithAssignment assAsm ON assAsm.CompanyID = r.CompanyID
                                                              AND assAsm.SalesOrgValueID = r.SalesAreaID --lay asm ql r.SalesAreaID
                                                              AND r.CompanyID = @CompanyID
                        INNER JOIN dbo.asmUser asm ON asm.Username = assAsm.LoginID --salesarea gan cho loginId, nen Asm phai co EmployeeCD = LoginID
                                                      AND asm.Role = 'ASM'
                ORDER BY asm.Username




    --Lay danh sach Salesmen :

        TRUNCATE TABLE dbo.asmSalesman
        INSERT  INTO dbo.asmSalesman
                ( SalesmanCode ,
                  SalesmanName ,
                  DistributorCode ,
                  SupCode ,
                  Visit ,
                  Username ,
                  DistributorName ,
                  DistributorCD ,
                  LastSync
                )
                SELECT  DISTINCT
                        usm.UserName , -- SalesmanCode - nvarchar(20)
                        usm.Name , -- SalesmanName - nvarchar(50)
                        '0' , -- DistributorCode - nvarchar(20)
                        sup.UserName , -- SupCode - nvarchar(20)
                        N'' , -- Visit
                        sup.UserName ,
                        N'' ,  -- DistributorName - nvarchar(255)
                        N'' ,
                        N''-- LastSync - nvarchar(50)
                FROM    [10.86.67.15].THPDMS2.dbo.DMSSalesForce sfsm
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.DMSRoute r ON sfsm.EmployeeID = r.SalespersonID
                                                             AND r.CompanyID = sfsm.CompanyID
                                                             AND sfsm.Active = 1
															 and r.active=1 -- Thach add
                        INNER JOIN THPSFA2.dbo.DMSAimUser usm ON sfsm.EmployeeCD = usm.UserName
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.DMSSalesForce sfsup ON sfsup.EmployeeID = r.SalesForceID
                                                              AND r.CompanyID = sfsup.CompanyID
                                                              AND sfsup.Active = 1
                                                              AND r.CompanyID = @CompanyID
                        INNER JOIN dbo.asmUser sup ON sup.UserName = sfsup.EmployeeCD
                                                      AND sup.Role = 'SUP'
						 inner join [10.86.67.15].THPDMS2.dbo.dmsroutesetting rst on r.routecd=rst.routecd and rst.enddate is null -- anh.nguyen 20180712 chi lay danh sach cac route co mcp hieu luc trong ngay

    --Lay danh sach Customer
        DELETE  dbo.asmCustomer

        INSERT  INTO dbo.asmCustomer
                ( CustomerCode ,
                  LocationCD ,
                  CustomerName ,
                  Address ,
                  Latitude ,
                  Longtitude ,
                  SalesmanCode ,
                  RouteCode ,
                  Visit ,
                  VisitOrder ,
                  DistributorCode ,
                  UserName ,
                  ShopFormatID ,
                  ShopTypeID ,
                  FileName ,
                  SampleFileName ,
                  DMSAttribute1 ,
                  DMSAttribute2 ,
                  DMSAttribute3 ,
                  DMSAttribute4 ,
                  DMSAttribute5 ,
                  DMSAttribute6 ,
                  DMSAttribute7 ,
                  DMSAttribute8 ,
                  DMSAttribute9 ,
                  DMSAttribute10 ,
                  DistrictID ,
                  ProvinceID ,
                  RegionID
                )
                SELECT  CustomerCD , -- CustomerCode - nvarchar(20)
                        aimC.LocationCD ,
                        CustomerName , -- CustomerName - nvarchar(50)
                        aimC.FullAddress ,--Address , -- Address - nvarchar(50)
                        CAST(aimC.Latitude AS FLOAT) , -- Latitude - float
                        CAST(aimC.Longitude AS FLOAT) , -- Longtitude - float
                        aimC.UserName , -- SalesmanCode - nvarchar(20)
                        aimC.RouteCD , -- RouteCode - nvarchar(20)
                        0 ,  -- Visit - int
                        ISNULL(aimC.VisitOrder, 0) , --VisitOrder int
                        aimC.DistributorCD ,
                        N'' ,
                        ISNULL(aimC.DMSAttribute3, 0) ,
                        '' ,
                        ISNULL(aimC.FileName, '') ,
                        ISNULL(aimC.SampleFile, '') ,
                        DMSAttribute1 ,
                        DMSAttribute2 ,
                        DMSAttribute3 ,
                        DMSAttribute4 ,
                        DMSAttribute5 ,
                        DMSAttribute6 ,
                        DMSAttribute7 ,
                        DMSAttribute8 ,
                        DMSAttribute9 ,
                        DMSAttribute10 ,
                        DistrictID ,
                        ProvinceID ,
                        RegionID
                FROM    THPSFA2.dbo.DMSAimCustomer aimC
                        INNER JOIN dbo.asmSalesman sm ON aimC.UserName = sm.SalesmanCode

			    --Lay danh sach route
        DELETE  dbo.asmRoute

        SET DATEFIRST 7 --Chu nhat se la 1
        DECLARE @VisitDate SMALLDATETIME = GETDATE()
        DECLARE @Dayofweek INT
        SET @Dayofweek = DATEPART(dw, @VisitDate)


        INSERT  INTO dbo.asmRoute
                ( UserName ,
                  RouteCode ,
                  RouteName ,
                  SalesmanCode ,
                  SupCode ,
                  DistributorCode
                )
                SELECT DISTINCT
                        sm.SupCode ,
                        cr.RouteCode ,
                        CASE WHEN cr.RouteCode NOT LIKE '%_T'
                             THEN RTRIM(LEFT(r.Descr, 90)) + ' - T'
                                  + RTRIM(CAST(@Dayofweek AS CHAR(1)))
                             ELSE RTRIM(LEFT(r.Descr, 90))
                                  + ' - Cac ngay con lai'
                        END AS RouteName ,--nvarchar(100)
                        cr.SalesmanCode ,
                        sm.SupCode ,
                        N''
                FROM    ( SELECT DISTINCT
                                    SalesmanCode ,
                                    RouteCode
                          FROM      dbo.asmCustomer
                        ) cr
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.DMSRoute r ON REPLACE(cr.RouteCode,
                                                              '_T', '') = r.RouteCD
                                                             AND r.CompanyID = @CompanyID
                        INNER JOIN dbo.asmSalesman sm ON sm.SalesmanCode = cr.SalesmanCode

		--Lay Route cua SUP cho ASM
        INSERT  INTO dbo.asmRoute
                ( UserName ,
                  RouteCode ,
                  RouteName ,
                  SalesmanCode ,
                  SupCode ,
                  DistributorCode
                )
                SELECT DISTINCT
                        asm.ASMCode ,
                        r.RouteCode ,
                        r.RouteName ,
                        r.SalesmanCode ,
                        r.SupCode ,
                        r.DistributorCode
                FROM    dbo.asmRoute r
                        INNER JOIN dbo.asmSup asm ON r.SupCode = asm.SupCode


 --Cap nhat lai neu ASM thi type la Hop vung de ko check dong bo o dau :
        --UPDATE  asmUser
        --SET     Type = 'HV'
        --WHERE   Role <> 'SUP'
    --Cap nhat lai danh sach SS khong di WW
        UPDATE  dbo.asmUser
        SET     Type = 'NWW'
        WHERE   Role IN ( 'SUP', 'ASM' )
                AND Username IN (
                SELECT  UserLogin
                FROM    THPHammer2.dbo.Appointments
                WHERE   ( Employees IS NULL
                          OR Employees = ''
                        )
                        AND IsWW = 0
                        AND IsMeeting = 0
                        AND DATEDIFF(dd, StartDate, GETDATE()) = 0
                        AND IsDelete = 0
                        AND ShiftID = 'AM' )




        UPDATE  dbo.asmUser
        SET     Type = 'HV'
        WHERE   Username IN (
                SELECT  UserLogin
                FROM    THPHammer2.dbo.Appointments
                WHERE   IsMeeting = 1
                        AND IsDelete = 0
						and  ShiftID = 'AM'
                        AND DATEDIFF(dd, StartDate, GETDATE()) = 0 )




        DELETE  dbo.asmParameter
        INSERT  INTO dbo.asmParameter
                ( ID ,
                  [Desc] ,
                  Value ,
                  UserName
                )
                SELECT  N'EndTime' , -- ID - nvarchar(50)
                        N'EndTime' , -- Desc - nvarchar(50)
                        N'23:00:00' , -- Value - nvarchar(50)
                        Username  -- UserName - nvarchar(50)
                FROM    dbo.asmUser
                UNION ALL
                SELECT  N'StartTime' , -- ID - nvarchar(50)
                        N'StartTime' , -- Desc - nvarchar(50)
                        N'07:30:00' , -- Value - nvarchar(50) yeu cau tu Danh 27/12/2017  N'08:00:00'
                        Username  -- UserName - nvarchar(50)
                FROM    dbo.asmUser
                UNION ALL
                SELECT  N'Interval' , -- ID - nvarchar(50)
                        N'Interval' , -- Desc - nvarchar(50)
                        N'15' , -- Value - nvarchar(50)
                        Username  -- UserName - nvarchar(50)
                FROM    dbo.asmUser
                UNION ALL
                SELECT  N'EvalWarningEnable' , -- ID - nvarchar(50)
                        N'Hiện thông báo nhắt nhở đánh giá cuối ngày' , -- Desc - nvarchar(50)
                        N'1' , -- Value - nvarchar(50)
                        Username  -- UserName - nvarchar(50)
                FROM    dbo.asmUser
                UNION ALL
                SELECT  N'EvalWarningTime' , -- ID - nvarchar(50)
                        N'Thời gian nhắt nhở đánh giá cuối ngày' , -- Desc - nvarchar(50)
                        N'16:30' , -- Value - nvarchar(50)
                        Username  -- UserName - nvarchar(50)
                FROM    dbo.asmUser

        DELETE  dbo.asmParameterRoute
        INSERT  INTO dbo.asmParameterRoute
                ( Username ,
                  ID ,
                  [Desc] ,
                  Value ,
                  RouteCode
                )
                SELECT  DISTINCT
                        l.UserName , -- Username - nvarchar(50)
                        p.ID , -- ID - nvarchar(50)
                        p.[Desc] , -- Desc - nvarchar(50)
                        p.Value , -- Value - nvarchar(50)
                        r.RouteCode  -- RouteCode - nvarchar(50)
                FROM    dbo.VS_ASM_GETSMBYUSENAME l
                        INNER JOIN dbo.asmRoute r ON l.SalesmanCode = r.SalesmanCode
                                                     AND l.UserName = r.UserName
                        INNER JOIN THPSFA2.dbo.DMSAimParameter p ON r.SalesmanCode = p.UserName
                WHERE   p.ID IN ( 'Distance', 'GPSLock', 'GPSByPass',
                                  'GPSLockImage', 'DistanceImage',
                                  'GPSByPassImage', 'DistanceAvatar',
                                  'GPSByPassAvatar', 'GPSLockAvatar' )
        --UPDATE asmParameterRoute SET VALUE = '200' WHERE ID = 'Distance' -- Danh yeu cau bo 20171222




        --Lay master Distributor location
        TRUNCATE TABLE dbo.AsmMasterDistributorLocation

        INSERT  INTO dbo.AsmMasterDistributorLocation
                ( DistributorCD ,
                  LocationCD ,
                  Descr ,
                  SiteID ,
                  Latitude ,
				  Longitude ,
                  IsBaseLocation
                )
                SELECT  DISTINCT
                        d.DistributorCD ,
                        l.LocationCD ,
                        d.DistributorName ,
                        '' ,
                        ISNULL(c.UsrLattitude, 0) ,
                        ISNULL(c.UsrLongtitude, 0) ,
                        '0'
                FROM    THPSFA2.dbo.DMSAimDistributor d
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.Branch b ON d.DistributorCD = b.BranchCD
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.BAccount a ON a.BAccountID = b.BAccountID
                                                             AND a.CompanyID = b.CompanyID
                                                             AND a.BAccountID = b.BAccountID
                                                             AND Type = 'CP'
                                                             AND a.DeletedDatabaseRecord <> 1
                                                             AND b.DeletedDatabaseRecord <> 1
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.Location l ON a.BAccountID = l.BAccountID
                                                             AND l.CompanyID = a.CompanyID
                                                             AND l.LocationID = a.DefLocationID
                        INNER JOIN [10.86.67.15].THPDMS2.dbo.Address c ON l.DefAddressID = c.AddressID
                                                            AND l.CompanyID = c.CompanyID
                                                            AND l.CompanyID = @CompanyID

		-- asmSupDistributor insert khi co KH
        TRUNCATE TABLE asmSupDistributor
        INSERT  INTO dbo.asmSupDistributor
                ( SupCode ,
                  DistributorCode ,
                  DistributorName ,
                  Latitude ,
                  Longtitude
                )
                SELECT DISTINCT
                        u.UserName ,
                        smDist.DistributorCD ,
                        smDist.DistributorName ,
                        masterLoc.Latitude ,
                        masterLoc.Longitude
                FROM    ( SELECT DISTINCT
                                    UserName ,
                                    DistributorCD ,
                                    DistributorName
                          FROM      THPSFA2.dbo.DMSAimDistributor
                        ) smDist
                        INNER JOIN dbo.asmSalesman sm ON sm.SalesmanCode = smDist.UserName
                        INNER JOIN dbo.asmUser u ON u.UserName = sm.SupCode
                                                    AND u.Role = 'SUP'
						--masterLoc chi lấy defaultLocation nên 1 NPP 1 masterLoc
                        INNER  JOIN AsmMasterDistributorLocation masterLoc ON masterLoc.DistributorCD = smDist.DistributorCD

        EXEC dbo.p_Asm_competitorStockCount
        EXEC dbo.p_asm_Customersurvey @CompanyID

        EXEC dbo.p_asm_UserTrainingMaterial

        EXEC dbo.sp_asm_SyncSchedule
        EXEC dbo.p_asm_survey_campaign_CAMP002
        EXEC dbo.p_asm_survey_campaign_CAMP003
        EXEC p_asm_asmVisitPlan @CompanyID
        EXEC p_asm_sync_geogrphy @CompanyID
        EXEC dbo.sp_Sync_TMKT @CompanyID
        EXEC dbo.sp_Sync_file @CompanyID
		--EXEC dbo.p_Asm_Evaluation: không su dung, add để nhớ
		--EXEC p_asm_Display_syncRegisterOutletToAcu	 Chưa xác định giải pháp, add để nhớ

        UPDATE  dbo.asmUser
        SET     ByPass = 'Normal'
        WHERE   Type = 'NWW'
                AND Role = 'ASM'
		UPDATE  dbo.asmUser
        SET     ByPass = 'Normal'
        WHERE   Type = 'HV'

		--Lay tọa độ đồng bộ của SM làm tọa độ đồng bộ của SUP/ASM trong trường hợp Lịch buổi sáng là lich WW với SM
        EXEC sp_Sync_CallSyncLocation
    END