Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- IF OBJECT_ID('p_ASM_Call_all', 'P') IS NOT NULL
- DROP PROCEDURE dbo.p_ASM_Call_all;
- GO
- CREATE 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
- 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'
- --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 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'
- --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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement