· 8 years ago · Nov 22, 2017, 04:26 AM
1IF NOT EXISTS ( SELECT *
2 FROM sys.objects
3 WHERE object_id = OBJECT_ID(N'DMSAimEmployeeList')
4 AND type IN ( N'U' ) )
5 CREATE TABLE [dbo].[DMSAimEmployeeList](
6 [EmployeeID] [INT] NOT NULL,
7 [EmployeeICD] [NVARCHAR](50) NULL,
8 [RouteCD] [NVARCHAR](50) NULL,
9 [GPSLock] [INT] NULL,
10 [Distance] [INT] NULL,
11 [FullVisitOutRoute] [INT] NULL,
12 [GPSOutRouteLock] [INT] NULL,
13 [RefNBR] [NVARCHAR](50) NULL,
14 [SellingCategoryID] [NVARCHAR](50) NULL,
15 CONSTRAINT [PK_DMSAimEmployeeList] PRIMARY KEY CLUSTERED
16 (
17 [EmployeeID] ASC
18 ))
19ELSE
20 BEGIN
21 IF NOT EXISTS ( SELECT *
22 FROM INFORMATION_SCHEMA.COLUMNS
23 WHERE TABLE_NAME = 'DMSAimEmployeeList'
24 AND COLUMN_NAME = 'SellingCategoryID' )
25 ALTER TABLE dbo.DMSAimEmployeeList ADD SellingCategoryID NVARCHAR(50) NULL;
26 END
27
28GO
29GO
30IF NOT EXISTS ( SELECT *
31 FROM sys.objects
32 WHERE object_id = OBJECT_ID(N'DMSAimNewCustomer')
33 AND type IN ( N'U' ) )
34 CREATE TABLE [dbo].[DMSAimNewCustomer](
35 [UserName] [NVARCHAR](50) NOT NULL,
36 [CustomerCD] [NVARCHAR](50) NOT NULL,
37 [CustomerName] [NVARCHAR](255) NOT NULL,
38 [LocationCD] [NVARCHAR](50) NOT NULL CONSTRAINT [DF__DMSAimNew__Locat__11F49EE0] DEFAULT (NULL),
39 [LocationName] [NVARCHAR](255) NOT NULL,
40 [DistributorCD] [NVARCHAR](50) NOT NULL CONSTRAINT [DF__DMSAimNew__Distr__12E8C319] DEFAULT (NULL),
41 [AddressLine1] [NVARCHAR](255) NOT NULL,
42 [AddressLine2] [NVARCHAR](255) NULL,
43 [AddressLine3] [NVARCHAR](255) NULL,
44 [AddressLine4] [NVARCHAR](255) NULL,
45 [StreetID] [NVARCHAR](50) NOT NULL,
46 [WardID] [NVARCHAR](50) NOT NULL,
47 [DistrictID] [NVARCHAR](50) NOT NULL,
48 [ProvinceID] [NVARCHAR](50) NOT NULL,
49 [RegionID] [NVARCHAR](50) NOT NULL,
50 [SuggestTimeVisit] [NVARCHAR](50) NULL,
51 [ContactPerson] [NVARCHAR](255) NULL,
52 [Phone] [NVARCHAR](50) NULL,
53 [Note] [NVARCHAR](255) NULL,
54 [LocationType] [INT] NULL CONSTRAINT [DF__DMSAimNew__Locat__13DCE752] DEFAULT ((0)),
55 [DayVisitInWeek] [NVARCHAR](7) NULL,
56 [SellingType] [INT] NULL CONSTRAINT [DF__DMSAimNew__Selli__14D10B8B] DEFAULT ((0)),
57 [Channel] [NVARCHAR](50) NULL CONSTRAINT [DF__DMSAimNew__Chann__15C52FC4] DEFAULT (NULL),
58 [CMNN] [NVARCHAR](50) NULL,
59 [Birthday] [NVARCHAR](50) NULL,
60 [BankAccNo] [NVARCHAR](50) NULL,
61 [BankAccOwner] [NVARCHAR](255) NULL,
62 [BankName] [NVARCHAR](255) NULL,
63 [BeginTime] [NVARCHAR](30) NOT NULL,
64 [EndTime] [NVARCHAR](30) NULL,
65 [GPSType] [NVARCHAR](50) NULL,
66 [TimeGetLocation] [NVARCHAR](50) NULL,
67 [Accuracy] [NVARCHAR](50) NULL,
68 [InfoVersion] [NVARCHAR](50) NULL,
69 [InfoSeri] [NVARCHAR](50) NULL,
70 [IsEnableAirPlaneMode] [NVARCHAR](15) NULL,
71 [IsEnableGPSMode] [NVARCHAR](15) NULL,
72 [IsEnableNetworkMode] [NVARCHAR](15) NULL,
73 [AvataFile] [NVARCHAR](255) NULL,
74 [CaptureDate] [DATETIME] NULL,
75 [Latitude] [FLOAT] NOT NULL CONSTRAINT [DF__DMSAimNew__Latit__16B953FD] DEFAULT ((0)),
76 [Longitude] [FLOAT] NOT NULL CONSTRAINT [DF__DMSAimNew__Longi__17AD7836] DEFAULT ((0)),
77 [IsUploaded] [INT] NULL CONSTRAINT [DF__DMSAimNew__IsUpl__18A19C6F] DEFAULT ((0)),
78 [IsSync] [INT] NULL CONSTRAINT [DF__DMSAimNew__IsSyn__1995C0A8] DEFAULT ((0)),
79 [isCoreSync] [INT] NULL CONSTRAINT [DF__DMSAimNew__isCor__3BEAD8AC] DEFAULT ((0)),
80 CONSTRAINT [PK__DMSAimNe__22204ACD3203C133] PRIMARY KEY CLUSTERED
81 (
82 [UserName] ASC,
83 [CustomerCD] ASC,
84 [LocationCD] ASC,
85 [DistributorCD] ASC
86 ))
87ELSE
88 BEGIN
89 IF NOT EXISTS ( SELECT *
90 FROM INFORMATION_SCHEMA.COLUMNS
91 WHERE TABLE_NAME = 'DMSAimNewCustomer'
92 AND COLUMN_NAME = 'Phone2' )
93 ALTER TABLE dbo.DMSAimNewCustomer ADD Phone2 NVARCHAR(20) NULL;
94
95 IF NOT EXISTS ( SELECT *
96 FROM INFORMATION_SCHEMA.COLUMNS
97 WHERE TABLE_NAME = 'DMSAimNewCustomer'
98 AND COLUMN_NAME = 'SubChannel' )
99 ALTER TABLE dbo.DMSAimNewCustomer ADD SubChannel INT NULL;
100
101 IF NOT EXISTS ( SELECT *
102 FROM INFORMATION_SCHEMA.COLUMNS
103 WHERE TABLE_NAME = 'DMSAimNewCustomer'
104 AND COLUMN_NAME = 'ShopFormat' )
105 ALTER TABLE dbo.DMSAimNewCustomer ADD ShopFormat INT NULL;
106
107 IF NOT EXISTS ( SELECT *
108 FROM INFORMATION_SCHEMA.COLUMNS
109 WHERE TABLE_NAME = 'DMSAimNewCustomer'
110 AND COLUMN_NAME = 'DisplayClass' )
111 ALTER TABLE dbo.DMSAimNewCustomer ADD DisplayClass INT NULL;
112
113 IF NOT EXISTS ( SELECT *
114 FROM INFORMATION_SCHEMA.COLUMNS
115 WHERE TABLE_NAME = 'DMSAimNewCustomer'
116 AND COLUMN_NAME = 'SalesLevel' )
117 ALTER TABLE dbo.DMSAimNewCustomer ADD SalesLevel INT NULL;
118
119 IF NOT EXISTS ( SELECT *
120 FROM INFORMATION_SCHEMA.COLUMNS
121 WHERE TABLE_NAME = 'DMSAimNewCustomer'
122 AND COLUMN_NAME = 'ShopArea' )
123 ALTER TABLE dbo.DMSAimNewCustomer ADD ShopArea INT NULL;
124
125 IF NOT EXISTS ( SELECT *
126 FROM INFORMATION_SCHEMA.COLUMNS
127 WHERE TABLE_NAME = 'DMSAimNewCustomer'
128 AND COLUMN_NAME = 'Email' )
129 ALTER TABLE dbo.DMSAimNewCustomer ADD Email NVARCHAR(50) NULL;
130 END
131
132GO
133GO
134IF NOT EXISTS ( SELECT *
135 FROM sys.objects
136 WHERE object_id = OBJECT_ID(N'DMSAimPricePriorityDistributor')
137 AND type IN ( N'U' ) )
138 CREATE TABLE [dbo].[DMSAimPricePriorityDistributor](
139 [CompanyID] [int] NOT NULL,
140 [RefNbr] [nvarchar](50) NOT NULL,
141 [DistributorID] [nvarchar](50) NOT NULL,
142 [DistributorCD] [nvarchar](50) NULL,
143 PRIMARY KEY
144 (
145 [CompanyID],
146 [RefNbr],
147 [DistributorID]
148 ))
149ELSE
150 BEGIN
151 IF NOT EXISTS ( SELECT *
152 FROM INFORMATION_SCHEMA.COLUMNS
153 WHERE TABLE_NAME = 'DMSAimPricePriorityDistributor'
154 AND COLUMN_NAME = 'DistributorCD' )
155 ALTER TABLE dbo.DMSAimPricePriorityDistributor ADD DistributorCD NVARCHAR(50) NULL;
156 END
157
158GO
159GO
160IF NOT EXISTS ( SELECT *
161 FROM sys.objects
162 WHERE object_id = OBJECT_ID(N'DMSAimPricePriorityInventory')
163 AND type IN ( N'U' ) )
164 CREATE TABLE [dbo].[DMSAimPricePriorityInventory](
165 [CompanyID] [int] NOT NULL,
166 [RefNbr] [nvarchar](50) NOT NULL,
167 [InventoryId] int NOT NULL,
168 [InventoryCD] [nvarchar](50) NULL,
169 [UOM] [nvarchar](50),
170 [Price] int,
171 [Priority] int,
172 [CreatedDateTime] datetime
173 PRIMARY KEY
174 (
175 [CompanyID],
176 [RefNbr],
177 [InventoryId]
178 ))
179ELSE
180 BEGIN
181 IF NOT EXISTS ( SELECT *
182 FROM INFORMATION_SCHEMA.COLUMNS
183 WHERE TABLE_NAME = 'DMSAimPricePriorityInventory'
184 AND COLUMN_NAME = 'InventoryCD' )
185 ALTER TABLE dbo.DMSAimPricePriorityInventory ADD InventoryCD NVARCHAR(50) NULL;
186 END
187
188GO
189GO
190IF NOT EXISTS ( SELECT *
191 FROM sys.objects
192 WHERE object_id = OBJECT_ID(N'DMSAimUploadedSMAvatar')
193 AND type IN ( N'U' ) )
194 CREATE TABLE [dbo].[DMSAimUploadedSMAvatar](
195 [UserName] [NVARCHAR](50) NOT NULL,
196 [AvatarFile] [NVARCHAR](255) NULL,
197 [MD5] [NVARCHAR](50) NULL,
198 CONSTRAINT [PK_DMSAimUploadedSMAvatar] PRIMARY KEY CLUSTERED
199 (
200 [UserName] ASC
201 ))
202
203GO
204GO
205IF OBJECT_ID('[vs_avgByCus]', 'V') IS NOT NULL
206 DROP VIEW dbo.[vs_avgByCus];
207GO
208CREATE VIEW [dbo].[vs_avgByCus]
209AS
210 SELECT CustomerID ,
211 CustomerLocationID ,
212 BranchID ,
213 SUM(ISNULL(avgcus.AVG3M, 0)) AS AVG3M ,
214 SUM(ISNULL(avgcus.AVG6M, 0)) AS AVG6M ,
215 SUM(ISNULL(avgcus.AVG12M, 0)) AS AVG12M ,
216 SUM(ISNULL(avgcus.REVQTD, 0)) AS REVQTD ,
217 SUM(ISNULL(avgcus.REVYTD, 0)) AS REVYTD
218 FROM ( SELECT CustomerID ,
219 CustomerLocationID ,
220 o.BranchID ,
221 SUM(( CASE WHEN o.OrderType IN ( 'CM' ) THEN -1
222 ELSE 1
223 END ) * o.OrderTotal) / 3 AS AVG3M ,
224 0 AS AVG6M ,
225 0 AS AVG12M ,
226 0 AS REVQTD ,
227 0 AS REVYTD
228 FROM PNCDMS..SOOrder o
229 INNER JOIN dbo.DMSAimStagingConfig w ON w.CompanyID = o.CompanyID
230 WHERE o.Status NOT IN ( 'V', 'L' )
231 AND o.OrderDate BETWEEN DATEADD(MONTH, -3,
232 DATEADD(DD, 1,
233 EOMONTH(GETDATE(),
234 -1)))
235 AND DATEADD(MONTH,
236 DATEDIFF(MONTH, -1,
237 GETDATE()) - 1,
238 -1)
239 GROUP BY CustomerID ,
240 CustomerLocationID ,
241 o.BranchID
242 UNION ALL
243 SELECT CustomerID ,
244 CustomerLocationID ,
245 o.BranchID ,
246 0 AS AVG3M ,
247 SUM(( CASE WHEN o.OrderType IN ( 'CM' ) THEN -1
248 ELSE 1
249 END ) * o.OrderTotal) / 6 AS AVG6M ,
250 0 AS AVG12M ,
251 0 AS REVQTD ,
252 0 AS REVYTD
253 FROM PNCDMS..SOOrder o
254 INNER JOIN dbo.DMSAimStagingConfig w ON w.CompanyID = o.CompanyID
255 WHERE o.Status NOT IN ( 'V', 'L' )
256 AND o.OrderDate BETWEEN DATEADD(MONTH, -6,
257 DATEADD(DD, 1,
258 EOMONTH(GETDATE(),
259 -1)))
260 AND DATEADD(MONTH,
261 DATEDIFF(MONTH, -1,
262 GETDATE()) - 1,
263 -1)
264 GROUP BY CustomerID ,
265 CustomerLocationID ,
266 o.BranchID
267 UNION ALL
268 SELECT CustomerID ,
269 CustomerLocationID ,
270 o.BranchID ,
271 0 AS AVG3M ,
272 0 AS AVG6M ,
273 SUM(( CASE WHEN o.OrderType IN ( 'CM' ) THEN -1
274 ELSE 1
275 END ) * o.OrderTotal) / 12 AS AVG12M ,
276 0 AS REVQTD ,
277 0 AS REVYTD
278 FROM PNCDMS..SOOrder o
279 INNER JOIN dbo.DMSAimStagingConfig w ON w.CompanyID = o.CompanyID
280 WHERE o.Status NOT IN ( 'V', 'L' )
281 AND o.OrderDate BETWEEN DATEADD(MONTH, -12,
282 DATEADD(DD, 1,
283 EOMONTH(GETDATE(),
284 -1)))
285 AND DATEADD(MONTH,
286 DATEDIFF(MONTH, -1,
287 GETDATE()) - 1,
288 -1)
289 GROUP BY CustomerID ,
290 CustomerLocationID ,
291 o.BranchID
292 UNION ALL
293 SELECT CustomerID ,
294 CustomerLocationID ,
295 o.BranchID ,
296 0 AS AVG3M ,
297 0 AS AVG6M ,
298 0 AS AVG12M ,
299 SUM(( CASE WHEN o.OrderType IN ( 'CM' ) THEN -1
300 ELSE 1
301 END ) * o.OrderTotal) AS REVQTD ,
302 0 AS REVYTD
303 FROM PNCDMS..SOOrder o
304 INNER JOIN dbo.DMSAimStagingConfig w ON w.CompanyID = o.CompanyID
305 WHERE o.Status NOT IN ( 'V', 'L' )
306 AND o.OrderDate BETWEEN DATEADD(DAY, 1,
307 EOMONTH(GETDATE(),
308 CASE
309 WHEN DATEPART(MONTH,
310 GETDATE()) % 3 = 1
311 THEN -1
312 WHEN DATEPART(MONTH,
313 GETDATE()) % 3 = 2
314 THEN -2
315 ELSE -3
316 END))
317 AND GETDATE()
318 GROUP BY CustomerID ,
319 CustomerLocationID ,
320 o.BranchID
321 UNION ALL
322 SELECT CustomerID ,
323 CustomerLocationID ,
324 o.BranchID ,
325 0 AS AVG3M ,
326 0 AS AVG6M ,
327 0 AS AVG12M ,
328 0 AS REVQTD ,
329 SUM(( CASE WHEN o.OrderType IN ( 'CM' ) THEN -1
330 ELSE 1
331 END ) * o.OrderTotal) AS REVYTD
332 FROM PNCDMS..SOOrder o
333 INNER JOIN dbo.DMSAimStagingConfig w ON w.CompanyID = o.CompanyID
334 WHERE o.Status NOT IN ( 'V', 'L' )
335 AND DATEPART(YEAR, o.OrderDate) = DATEPART(YEAR, GETDATE())
336 GROUP BY CustomerID ,
337 CustomerLocationID ,
338 o.BranchID
339 ) AS avgcus
340 GROUP BY CustomerID ,
341 CustomerLocationID ,
342 avgcus.BranchID
343GO
344
345GO
346IF OBJECT_ID( 'sp_NewCust_insert', 'P' ) IS NOT NULL
347 DROP PROCEDURE dbo.sp_NewCust_insert;
348GO
349CREATE PROCEDURE [dbo].[sp_NewCust_insert]
350AS
351 BEGIN
352 DECLARE @CompanyID INT
353 SELECT @CompanyID = CompanyID
354 FROM dbo.DMSAimStagingConfig
355
356
357 BEGIN TRANSACTION
358
359 BEGIN TRY
360
361 SELECT *
362 INTO #tempNewCust
363 FROM dbo.DMSAimNewCustomer
364 WHERE CustomerCD NOT IN (
365 SELECT ISNULL(CustomerID_PDA, '')
366 FROM PNCDMS..DMSCalculatorFrequency
367 WHERE CompanyID = @CompanyID )
368
369 INSERT INTO PNCDMS..DMSCalculatorFrequency
370 ( CompanyID ,
371 CustomerID_PDA ,
372 OutletName ,
373 ContactName ,
374 OutletType ,
375 DistributorID ,
376 Salesperson ,
377 CountryID ,
378 ProvinceID ,
379 DistrictID ,
380 WardID ,
381 StreetID ,
382 RouteCD ,
383 Email ,
384 ActionType ,
385 DMSCode ,
386 CustomerClassID ,
387 Phone1 ,
388 Phone2 ,
389 AddressLine1 ,
390 AddressLine2 ,
391 AddressLine3 ,
392 AddressLine4 ,
393 Lattitude ,
394 Longtitude ,
395 LocationCD ,
396 LocationName ,
397 SuggestTimeVisit ,
398 Note ,
399 IdentityCard ,
400 Birthday ,
401 BankAccNo ,
402 BankAccOwner ,
403 BankName ,
404 AvataFile ,
405 CaptureDate ,
406 Attribute0 ,
407 Attribute1 ,
408 Attribute2 ,
409 Attribute3 ,
410 Attribute4 ,
411 Attribute5 ,
412 Attribute6 ,
413 Attribute7 ,
414 Attribute8 ,
415 Attribute9 ,
416 CreatedByScreenID ,
417 CreatedDateTime ,
418 LastModifiedByScreenID ,
419 LastModifiedDateTime ,
420 Monday ,
421 Tuesday ,
422 Wednesday ,
423 Thursday ,
424 Friday ,
425 Saturday ,
426 Sunday
427 )
428 SELECT @CompanyID , -- CompanyID - int
429 CustomerCD , -- CustomerID_PDA - nvarchar(30)
430 CustomerName , -- OutletName - nvarchar(50)
431 ContactPerson , -- ContactName - nvarchar(50)
432 'O' , -- OutletType - varchar(15)
433 b.BranchID , -- DistributorID - int
434 e.EmployeeID , -- Salesperson - int
435 p.CountryID , -- CountryID - varchar(2)
436 c.ProvinceID , -- ProvinceID - int
437 DistrictID , -- DistrictID - int
438 WardID , -- WardID - int
439 StreetID , -- StreetID - int
440 e.RouteCD , -- RouteCD - varchar(20)
441 c.Email , -- Email - nvarchar(50)
442 'IN' , -- ActionType - varchar(2)
443 CustomerCD , -- DMSCode - varchar(20)
444 NULL , -- CustomerClassID - varchar(15)
445 Phone , -- Phone1 - nvarchar(12)
446 c.Phone2 , -- Phone2 - nvarchar(12)
447 AddressLine1 , -- AddressLine1 - nvarchar(50)
448 AddressLine2 , -- AddressLine2 - nvarchar(50)
449 AddressLine3 , -- AddressLine3 - nvarchar(50)
450 AddressLine4 , -- AddressLine4 - nvarchar(50)
451 Latitude , -- Lattitude - decimal
452 Longitude , -- Longtitude - decimal
453 NULL , -- LocationCD - nvarchar(50)
454 c.LocationName , -- LocationName - nvarchar(50)
455 SuggestTimeVisit , -- SuggestTimeVisit - nvarchar(50)
456 Note , -- Note - nvarchar(255)
457 CMNN , -- IdentityCard - nvarchar(50)
458 Birthday , -- Birthday - nvarchar(50)
459 BankAccNo , -- BankAccNo - nvarchar(50)
460 BankAccOwner , -- BankAccOwner - nvarchar(255)
461 BankName , -- BankName - nvarchar(255)
462 AvataFile , -- AvataFile - nvarchar(255)
463 CaptureDate , -- CaptureDate - datetime
464 Channel , -- Attribute0 - int
465 c.SubChannel , -- Attribute1 - int
466 c.ShopFormat , -- Attribute2 - int
467 c.DisplayClass , -- Attribute3 - int
468 NULL , -- Attribute4 - int
469 c.ShopArea , -- Attribute5 - int
470 LocationType , -- Attribute6 - int
471 c.SalesLevel , -- Attribute7 - int
472 SellingType , -- Attribute8 - int
473 NULL , -- Attribute9 - int
474 'PDA' , -- CreatedByScreenID - char(8)
475 GETDATE() , -- CreatedDateTime - datetime
476 'PDA' , -- LastModifiedByScreenID - char(8)
477 GETDATE() , -- LastModifiedDateTime - datetime
478 ( CASE WHEN LEN(DayVisitInWeek) >= 1
479 THEN SUBSTRING(DayVisitInWeek, 1, 1)
480 ELSE 0
481 END ) , -- Monday - bit
482 ( CASE WHEN LEN(DayVisitInWeek) >= 2
483 THEN SUBSTRING(DayVisitInWeek, 2, 1)
484 ELSE 0
485 END ) , -- Tuesday - bit
486 ( CASE WHEN LEN(DayVisitInWeek) >= 3
487 THEN SUBSTRING(DayVisitInWeek, 3, 1)
488 ELSE 0
489 END ) , -- Wednesday - bit
490 ( CASE WHEN LEN(DayVisitInWeek) >= 4
491 THEN SUBSTRING(DayVisitInWeek, 4, 1)
492 ELSE 0
493 END ) , -- Thursday - bit
494 ( CASE WHEN LEN(DayVisitInWeek) >= 5
495 THEN SUBSTRING(DayVisitInWeek, 5, 1)
496 ELSE 0
497 END ) , -- Friday - bit
498 ( CASE WHEN LEN(DayVisitInWeek) >= 6
499 THEN SUBSTRING(DayVisitInWeek, 6, 1)
500 ELSE 0
501 END ) , -- Saturday - bit
502 ( CASE WHEN LEN(DayVisitInWeek) >= 7
503 THEN SUBSTRING(DayVisitInWeek, 7, 1)
504 ELSE 0
505 END ) -- Sunday - bit
506 FROM #tempNewCust c
507 INNER JOIN dbo.DMSAimEmployeeList e ON c.UserName = e.EmployeeICD
508 INNER JOIN PNCDMS..DMSProvince p ON p.CompanyID = @CompanyID
509 AND p.ProvinceID = c.ProvinceID
510 LEFT JOIN PNCDMS..Branch b ON b.CompanyID = @CompanyID
511 AND c.DistributorCD = b.BranchCD
512
513 COMMIT TRANSACTION
514 END TRY
515 BEGIN CATCH
516 ROLLBACK TRANSACTION
517 END CATCH
518 END
519
520
521
522GO
523GO
524IF OBJECT_ID('sp_OES_DMSAimNewCustomer_Insert', 'P') IS NOT NULL
525 DROP PROCEDURE dbo.sp_OES_DMSAimNewCustomer_Insert;
526GO
527CREATE PROCEDURE [dbo].[sp_OES_DMSAimNewCustomer_Insert]
528 @UserName NVARCHAR(50) ,
529 @CustomerCD NVARCHAR(50) ,
530 @CustomerName NVARCHAR(255) ,
531 @LocationCD NVARCHAR(50) ,
532 @LocationName NVARCHAR(255) ,
533 @DistributorCD NVARCHAR(50) ,
534 @AddressLine1 NVARCHAR(255) ,
535 @AddressLine2 NVARCHAR(255) = '' ,
536 @AddressLine3 NVARCHAR(255) = '' ,
537 @AddressLine4 NVARCHAR(255) = '' ,
538 @StreetID NVARCHAR(50) = '' ,
539 @WardID NVARCHAR(50) = '' ,
540 @DistrictID NVARCHAR(50) = '' ,
541 @ProvinceID NVARCHAR(50) = '' ,
542 @RegionID NVARCHAR(50) = '' ,
543 @SuggestTimeVisit NVARCHAR(50) = '' ,
544 @ContactPerson NVARCHAR(255) = '' ,
545 @Phone NVARCHAR(50) = '' ,
546 @Note NVARCHAR(255) = '' ,
547 @LocationType INT = 0 ,
548 @DayVisitInWeek NVARCHAR(7) = '' ,
549 @SellingType INT = 0 ,
550 @Channel NVARCHAR(50) = '' ,
551 @CMNN NVARCHAR(50) = '' ,
552 @Birthday NVARCHAR(50) = '' ,
553 @BankAccNo NVARCHAR(50) = '' ,
554 @BankAccOwner NVARCHAR(255) = '' ,
555 @BankName NVARCHAR(255) = '' ,
556 @BeginTime NVARCHAR(30) = '' ,
557 @EndTime NVARCHAR(30) = '' ,
558 @GPSType NVARCHAR(50) = '' ,
559 @TimeGetLocation NVARCHAR(50) = '' ,
560 @Accuracy NVARCHAR(50) = '' ,
561 @InfoVersion NVARCHAR(50) = '' ,
562 @InfoSeri NVARCHAR(50) = '' ,
563 @IsEnableAirPlaneMode NVARCHAR(15) = '' ,
564 @IsEnableGPSMode NVARCHAR(15) = '' ,
565 @IsEnableNetworkMode NVARCHAR(15) = '' ,
566 @AvataFile NVARCHAR(255) = '' ,
567 @CaptureDate DATETIME ,
568 @Latitude FLOAT ,
569 @Longitude FLOAT ,
570 @IsUploaded INT ,
571 @IsSync INT ,
572 @Phone2 NVARCHAR(20) = '' ,
573 @SubChannel INT ,
574 @ShopFormat INT ,
575 @DisplayClass INT ,
576 @SalesLevel INT ,
577 @ShopArea INT ,
578 @Email NVARCHAR(50) = ''
579AS
580 BEGIN
581 IF NOT EXISTS ( SELECT *
582 FROM dbo.DMSAimNewCustomer
583 WHERE UserName = @UserName
584 AND CustomerCD = @CustomerCD
585 AND LocationCD = @LocationCD
586 AND DistributorCD = @DistributorCD )
587 INSERT INTO DMSAimNewCustomer
588 ( UserName ,
589 CustomerCD ,
590 CustomerName ,
591 LocationCD ,
592 LocationName ,
593 DistributorCD ,
594 AddressLine1 ,
595 AddressLine2 ,
596 AddressLine3 ,
597 AddressLine4 ,
598 StreetID ,
599 WardID ,
600 DistrictID ,
601 ProvinceID ,
602 RegionID ,
603 SuggestTimeVisit ,
604 ContactPerson ,
605 Phone ,
606 Note ,
607 LocationType ,
608 DayVisitInWeek ,
609 SellingType ,
610 Channel ,
611 CMNN ,
612 Birthday ,
613 BankAccNo ,
614 BankAccOwner ,
615 BankName ,
616 BeginTime ,
617 EndTime ,
618 GPSType ,
619 TimeGetLocation ,
620 Accuracy ,
621 InfoVersion ,
622 InfoSeri ,
623 IsEnableAirPlaneMode ,
624 IsEnableGPSMode ,
625 IsEnableNetworkMode ,
626 AvataFile ,
627 CaptureDate ,
628 Latitude ,
629 Longitude ,
630 IsUploaded ,
631 IsSync ,
632 Phone2 ,
633 SubChannel ,
634 ShopFormat ,
635 DisplayClass ,
636 SalesLevel ,
637 ShopArea ,
638 Email
639 )
640 VALUES ( @UserName ,
641 @CustomerCD ,
642 @CustomerName ,
643 @LocationCD ,
644 @LocationName ,
645 @DistributorCD ,
646 @AddressLine1 ,
647 @AddressLine2 ,
648 @AddressLine3 ,
649 @AddressLine4 ,
650 @StreetID ,
651 @WardID ,
652 @DistrictID ,
653 @ProvinceID ,
654 @RegionID ,
655 @SuggestTimeVisit ,
656 @ContactPerson ,
657 @Phone ,
658 @Note ,
659 @LocationType ,
660 @DayVisitInWeek ,
661 @SellingType ,
662 @Channel ,
663 @CMNN ,
664 @Birthday ,
665 @BankAccNo ,
666 @BankAccOwner ,
667 @BankName ,
668 @BeginTime ,
669 @EndTime ,
670 @GPSType ,
671 @TimeGetLocation ,
672 @Accuracy ,
673 @InfoVersion ,
674 @InfoSeri ,
675 @IsEnableAirPlaneMode ,
676 @IsEnableGPSMode ,
677 @IsEnableNetworkMode ,
678 @AvataFile ,
679 @CaptureDate ,
680 @Latitude ,
681 @Longitude ,
682 @IsUploaded ,
683 @IsSync ,
684 @Phone2 ,
685 @SubChannel ,
686 @ShopFormat ,
687 @DisplayClass ,
688 @SalesLevel ,
689 @ShopArea ,
690 @Email
691 )
692 END
693
694GO
695GO
696IF OBJECT_ID( 'sp_OES_DMSAimSMAvatar_Select', 'P' ) IS NOT NULL
697 DROP PROCEDURE dbo.sp_OES_DMSAimSMAvatar_Select;
698GO
699CREATE PROCEDURE [dbo].[sp_OES_DMSAimSMAvatar_Select]
700 -- Add the parameters for the stored procedure here
701 @UserName NVARCHAR(50)
702AS
703BEGIN
704 SELECT * FROM dbo.DMSAimSMAvatar WHERE UserName = @UserName
705END
706
707GO
708GO
709IF OBJECT_ID('sp_OES_DMSAimUploadedSMAvatar_Insert', 'P') IS NOT NULL
710 DROP PROCEDURE dbo.sp_OES_DMSAimUploadedSMAvatar_Insert;
711GO
712CREATE PROCEDURE [dbo].[sp_OES_DMSAimUploadedSMAvatar_Insert]
713 @UserName NVARCHAR(50) ,
714 @AvatarFile NVARCHAR(255) ,
715 @MD5 NVARCHAR(50)
716AS
717 BEGIN
718 UPDATE DMSAimUploadedSMAvatar
719 SET AvatarFile=@AvatarFile, MD5 = @MD5
720 WHERE UserName = @UserName
721
722 IF @@ROWCOUNT = 0
723 INSERT INTO DMSAimUploadedSMAvatar (UserName, AvatarFile, MD5)
724 VALUES (@UserName, @AvatarFile, @MD5)
725 END
726
727GO
728GO
729IF OBJECT_ID( 'sp_Sync_Calc_allEmployee', 'P' ) IS NOT NULL
730 DROP PROCEDURE dbo.sp_Sync_Calc_allEmployee;
731GO
732CREATE PROCEDURE [dbo].[sp_Sync_Calc_allEmployee] @CompanyID INT
733AS
734 BEGIN
735 TRUNCATE TABLE dbo.DMSAimEmployeeList;
736 INSERT INTO dbo.DMSAimEmployeeList
737 ( EmployeeID ,
738 EmployeeICD ,
739 RouteCD ,
740 GPSLock ,
741 Distance ,
742 GPSOutRouteLock ,
743 FullVisitOutRoute ,
744 RefNBR ,
745 SellingCategoryID
746 )
747 SELECT DISTINCT
748 s.EmployeeID , -- EmployeeID - int
749 s.EmployeeCD , -- EmployeeICD - nvarchar(50)
750 r.RouteCD , -- RouteCD - nvarchar(50)
751 ISNULL(r.GPSLock, 0) ,
752 ISNULL(r.Distance, 200) ,
753 ISNULL(r.GPSOutRouteLock, 0) ,
754 ISNULL(r.FullVisitOutRoute, 0) ,
755 r.RefNbr ,
756 r.SellingCategoryID
757 FROM PNCDMS..DMSSalesForce s
758 INNER JOIN PNCDMS..DMSSFHierachy h ON h.CompanyID = s.CompanyID
759 AND s.SFHierachyID = h.SFHierachyID
760 AND h.IsSalesperson = 1
761 INNER JOIN PNCDMS..DMSRouteSetting r ON r.CompanyID = s.CompanyID
762 AND r.SalespersonID = s.EmployeeID
763 AND r.Active = 1
764 AND r.Status = 'G'
765 WHERE DATEDIFF(dd, r.EffectiveDate, GETDATE()) >= 0
766 AND DATEDIFF(DD, ISNULL(r.EndDate, GETDATE()),
767 GETDATE()) <= 0
768 AND h.CompanyID = @CompanyID
769 AND s.Active = 1;
770
771
772--Load danh sach NPP ma SM dc gan vao
773 TRUNCATE TABLE dbo.DMSAimDistributor;
774 INSERT INTO dbo.DMSAimDistributor
775 ( UserName ,
776 DistributorCD ,
777 DistributorName ,
778 DistributorLat ,
779 DistributorLong ,
780 SalesOrg0_Value ,
781 SalesOrg0_Name ,
782 SalesOrg1_Value ,
783 SalesOrg1_Name ,
784 SalesOrg2_Value ,
785 SalesOrg2_Name ,
786 SalesOrg3_Value ,
787 SalesOrg3_Name ,
788 SalesOrg4_Value ,
789 SalesOrg4_Name ,
790 SalesOrg5_Value ,
791 SalesOrg5_Name ,
792 SalesOrg6_Value ,
793 SalesOrg6_Name ,
794 SalesOrg7_Value ,
795 SalesOrg7_Name ,
796 SalesOrg8_Value ,
797 SalesOrg8_Name ,
798 SalesOrg9_Value ,
799 SalesOrg9_Name
800 )
801 SELECT DISTINCT
802 s.EmployeeCD , -- UserName - nvarchar(50)
803 b.BranchCD , -- DistributorCD - nvarchar(50)
804 a.AcctName , -- DistributorName - nvarchar(255)
805 0.0 , -- DistributorLat - float
806 0.0 , -- DistributorLong - float
807 d.Attribute0ID , -- SalesOrg0_Value - varchar(15)
808 d.Attribute0Descr , -- SalesOrg0_Name - nvarchar(255)
809 d.Attribute1ID , -- SalesOrg1_Value - varchar(15)
810 d.Attribute1Descr , -- SalesOrg1_Name - nvarchar(255)
811 d.Attribute2ID , -- SalesOrg2_Value - varchar(15)
812 d.Attribute2Descr , -- SalesOrg2_Name - nvarchar(255)
813 d.Attribute3ID , -- SalesOrg3_Value - varchar(15)
814 d.Attribute3Descr , -- SalesOrg3_Name - nvarchar(255)
815 d.Attribute4ID , -- SalesOrg4_Value - varchar(15)
816 d.Attribute4Descr , -- SalesOrg4_Name - nvarchar(255)
817 d.Attribute5ID , -- SalesOrg5_Value - varchar(15)
818 d.Attribute5Descr , -- SalesOrg5_Name - nvarchar(255)
819 d.Attribute6ID , -- SalesOrg6_Value - varchar(15)
820 d.Attribute6Descr , -- SalesOrg6_Name - nvarchar(255)
821 d.Attribute7ID , -- SalesOrg7_Value - varchar(15)
822 d.Attribute7Descr , -- SalesOrg7_Name - nvarchar(255)
823 d.Attribute8ID , -- SalesOrg8_Value - varchar(15)
824 d.Attribute8Descr , -- SalesOrg8_Name - nvarchar(255)
825 d.Attribute9ID , -- SalesOrg9_Value - varchar(15)
826 d.Attribute9Descr -- SalesOrg9_Name - nvarchar(255)
827 FROM PNCDMS..DMSSalesForce s
828 INNER JOIN PNCDMS..DMSSFHierachy h ON h.CompanyID = s.CompanyID
829 AND s.SFHierachyID = h.SFHierachyID
830 AND h.IsSalesperson = 1
831 INNER JOIN PNCDMS..DMSRouteSetting r ON r.CompanyID = s.CompanyID
832 AND r.SalespersonID = s.EmployeeID
833 AND r.Active = 1
834 AND r.Status = 'G'
835 INNER JOIN PNCDMS..DMSMCPDetail p ON r.RefNbr = p.RefNbr
836 AND p.CompanyID = r.CompanyID
837 INNER JOIN PNCDMS..Branch b ON p.DistributorID = b.BranchID
838 AND p.CompanyID = b.CompanyID
839 INNER JOIN PNCDMS..BAccount a ON b.CompanyID = a.CompanyID
840 AND a.BAccountID = b.BAccountID
841 AND a.CompanyID = @CompanyID
842 INNER JOIN PNCDMS.dbo.DMSViewDistributorLocation d ON p.DistributorID = d.DistributorID
843 AND p.DistributorLocationID = d.LocationID
844 WHERE DATEDIFF(dd, r.EffectiveDate, GETDATE()) >= 0
845 AND DATEDIFF(DD, ISNULL(r.EndDate, GETDATE()),
846 GETDATE()) <= 0;
847
848 INSERT INTO dbo.DMSAimUser
849 ( UserName ,
850 Name ,
851 DataDate ,
852 WorkingDate ,
853 LastVisistID ,
854 LastOrderID ,
855 WorkingDay ,
856 TotalWorkingDay
857 )
858 SELECT EmployeeICD , -- UserName - nvarchar(50)
859 s.Descr , -- Name - nvarchar(255)
860 CONVERT(VARCHAR(30), GETDATE(), 121) , -- DataDate - datetime
861 CONVERT(VARCHAR(30), GETDATE(), 121) , -- WorkingDate - smalldatetime
862 0 , -- LastVisistID - int
863 0 , -- LastOrderID - int
864 w.MTDWorkingday , -- WorkingDay - int
865 w.TotalWorkingday -- TotalWorkingDay - int
866 FROM PNCDMS..DMSSalesForce s
867 INNER JOIN dbo.DMSAimEmployeeList e ON e.EmployeeID = s.EmployeeID
868 INNER JOIN dbo.vs_Salesman_workingday w ON e.EmployeeID = w.SalespersonID
869 WHERE s.CompanyID = @CompanyID;
870
871 INSERT INTO dbo.DMSAimParameter
872 ( ID ,
873 [Desc] ,
874 Value ,
875 UserName
876 )
877 --VALUES ( N'', -- ID - nvarchar(50)
878 -- N'', -- Desc - nvarchar(100)
879 -- N'', -- Value - nvarchar(50)
880 -- N'' -- UserName - nvarchar(50)
881 -- )
882 SELECT 'Distance' AS ID ,
883 N'Khoảng cách cho phép' AS [DESC] ,
884 Distance AS Value ,
885 EmployeeICD
886 FROM dbo.DMSAimEmployeeList
887 UNION ALL
888 SELECT 'GPSLock' AS ID ,
889 N'Khóa tá»a Ä‘á»™' AS [DESC] ,
890 GPSLock AS Value ,
891 EmployeeICD
892 FROM dbo.DMSAimEmployeeList
893 UNION ALL
894 SELECT 'FullVisitOutRoute' AS ID ,
895 N'Cho phép visit full trái tuyến' AS [DESC] ,
896 FullVisitOutRoute AS Value ,
897 EmployeeICD
898 FROM dbo.DMSAimEmployeeList
899 UNION ALL
900 SELECT 'GPSOutRouteLock' AS ID ,
901 N'Khóa tá»a Ä‘á»™ khi visit trái tuyến' AS [DESC] ,
902 GPSOutRouteLock AS Value ,
903 EmployeeICD
904 FROM dbo.DMSAimEmployeeList;
905
906 INSERT INTO dbo.DMSSyncSetting
907 ( UserName ,
908 SyncDistance ,
909 ByPass
910 )
911 SELECT u.UserName ,
912 200 , -- SyncDistance - float
913 1 -- ByPass - int
914 FROM dbo.DMSAimUser u
915 LEFT JOIN DMSSyncSetting s ON s.UserName = u.UserName
916 WHERE s.UserName IS NULL;
917
918 END;
919
920GO
921
922GO
923IF OBJECT_ID( 'sp_Sync_Calc_allInventory', 'P' ) IS NOT NULL
924 DROP PROCEDURE dbo.sp_Sync_Calc_allInventory;
925GO
926CREATE PROCEDURE [dbo].[sp_Sync_Calc_allInventory] @CompanyID INT
927AS
928 BEGIN
929 TRUNCATE TABLE dbo.DMSAimInventoryList;
930 INSERT INTO dbo.DMSAimInventoryList
931 ( InventoryID ,
932 InventoryCD ,
933 SalesUnit ,
934 UnitPerCase
935 )
936 SELECT i.InventoryID , -- InventoryID - int
937 InventoryCD , -- InventoryCD - nvarchar(50)
938 SalesUnit , -- SalesUnit - nvarchar(50),
939 CAST(ISNULL(u.UnitRate, 1) AS INT)
940 FROM PNCDMS..InventoryItem i
941 LEFT JOIN PNCDMS..INUnit u ON i.CompanyID = u.CompanyID
942 AND i.InventoryID = u.InventoryID
943 AND u.ToUnit = i.SalesUnit
944 AND u.FromUnit = 'CAR'
945 WHERE i.CompanyID = @CompanyID
946 AND ItemStatus NOT IN ( 'IN' );
947
948 -- Lấy danh sách đơn vị của sản phẩm
949 TRUNCATE TABLE dbo.DMSAimInventoryUnit
950 INSERT INTO dbo.DMSAimInventoryUnit
951 ( InventoryCD ,
952 SalesUnit ,
953 CaseUnit ,
954 UnitRate
955 )
956 SELECT i.InventoryCD , -- InventoryCD - nvarchar(50)
957 i.SalesUnit , -- SalesUnit - nvarchar(50),
958 u.FromUnit , -- CaseUnit - nvarchar(50)
959 CAST(ISNULL(u.UnitRate, 1) AS INT) -- UnitRate - int
960 FROM dbo.DMSAimInventoryList i
961 LEFT JOIN PNCDMS..INUnit u ON i.InventoryID = u.InventoryID
962 AND u.ToUnit <> u.FromUnit
963 AND u.ToUnit = i.SalesUnit
964 WHERE u.CompanyID = @CompanyID
965 AND u.UnitRate <> 0
966
967 -- Gen SP cho all SM
968 INSERT INTO dbo.DMSAimInventoryItem
969 ( UserName ,
970 InventoryCD ,
971 InventoryName ,
972 ShortName ,
973 UnitPerCase ,
974 SalesUnit ,
975 Kparam ,
976 AllowSales ,
977 Enddate ,
978 ProductKind ,
979 Volume ,
980 Weight ,
981 IsExclusive ,
982 PurchaseUnit
983 )
984 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
985 l.InventoryCD , -- InventoryCD - nvarchar(50)
986 i.Descr , -- InventoryName - nvarchar(255)
987 ISNULL(i.UsrShortName, i.Descr) , -- ShortName - nvarchar(50)
988 l.UnitPerCase , -- UnitPerCase - int
989 l.SalesUnit , -- SalesUnit - nvarchar(50)
990 0.0 , -- Kparam - float
991 CASE WHEN e.SellingCategoryID IS NOT NULL
992 THEN ( CASE WHEN i.UsrIsExclusive = 1
993 OR c.InventoryID IS NOT NULL
994 THEN 1
995 ELSE 0
996 END )
997 ELSE 1
998 END AllowSales , -- AllowSales - int
999 GETDATE() , -- Enddate - date
1000 0 , -- ProductKind - int
1001 i.BaseItemVolume ,
1002 i.BaseItemWeight ,
1003 ISNULL(i.UsrIsExclusive, 0) ,
1004 i.PurchaseUnit
1005 FROM PNCDMS..InventoryItem i
1006 INNER JOIN dbo.DMSAimInventoryList l ON i.InventoryID = l.InventoryID
1007 AND i.CompanyID = @CompanyID
1008 CROSS JOIN dbo.DMSAimEmployeeList e
1009 LEFT JOIN PNCDMS..DMSViewProductBySellingCat c ON i.CompanyID = c.CompanyID
1010 AND i.InventoryID = c.InventoryID
1011 AND c.SellingCategoryID = e.SellingCategoryID
1012
1013 -- Lay gia ban
1014 INSERT INTO dbo.DMSAimSalesPrice
1015 ( UserName ,
1016 DistrictID ,
1017 CustomerPriceClassID ,
1018 InventoryCD ,
1019 Price
1020 )
1021 SELECT d.UserName , -- UserName - nvarchar(50)
1022 s.DistrictID , -- DistrictID - nvarchar(50)
1023 s.CustomerPriceClassID , -- CustomerPriceClassID - nvarchar(50)
1024 l.InventoryCD , -- InventoryCD - nvarchar(50)
1025 s.Price -- Price - float
1026 FROM dbo.DMSTempSalesPrice s
1027 INNER JOIN dbo.DMSAimInventoryList l ON l.InventoryID = s.InventoryCD
1028 INNER JOIN ( SELECT DISTINCT
1029 UserName ,
1030 DistrictID
1031 FROM dbo.DMSAimCustomer
1032 ) AS d ON d.DistrictID = s.DistrictID;
1033
1034 END;
1035
1036GO
1037GO
1038IF OBJECT_ID('sp_Sync_calc_Customer_New', 'P') IS NOT NULL
1039 DROP PROCEDURE dbo.sp_Sync_calc_Customer_New;
1040GO
1041CREATE PROCEDURE [dbo].[sp_Sync_calc_Customer_New]
1042 -- Add the parameters for the stored procedure here
1043 @CompanyID INT
1044AS
1045 BEGIN
1046 SELECT *
1047 INTO #vs_Today_visitplan
1048 FROM vs_Today_visitplan;
1049 SELECT *
1050 INTO #vs_NumOfOrderInQuaterByCus
1051 FROM vs_NumOfOrderInQuaterByCus;
1052 SELECT *
1053 INTO #vs_NumOfVisitInQuaterByCust
1054 FROM vs_NumOfVisitInQuaterByCust;
1055 SELECT *
1056 INTO #vs_avgByCus
1057 FROM vs_avgByCus;
1058 SELECT *
1059 INTO #vs_sync_nextvisit
1060 FROM vs_sync_nextvisit;
1061 SELECT *
1062 INTO #vs_sync_lastvisit
1063 FROM vs_sync_lastvisit;
1064
1065
1066 INSERT INTO dbo.DMSAimCustomer
1067 ( UserName ,
1068 CustomerCD ,
1069 CustomerName ,
1070 LocationCD ,
1071 LocationName ,
1072 DistributorCD ,
1073 AddressLine1 ,
1074 AddressLine2 ,
1075 AddressLine3 ,
1076 AddressLine4 ,
1077 StreetID ,
1078 WardID ,
1079 DistrictID ,
1080 ProvinceID ,
1081 RegionID ,
1082 Latitude ,
1083 Longitude ,
1084 ActivityStatus ,
1085 CustomerPriceClassID ,
1086 DMSAttribute1 ,
1087 DMSAttribute2 ,
1088 DMSAttribute3 ,
1089 DMSAttribute4 ,
1090 DMSAttribute5 ,
1091 DMSAttribute6 ,
1092 DMSAttribute7 ,
1093 DMSAttribute8 ,
1094 DMSAttribute9 ,
1095 DMSAttribute10 ,
1096 SellingCat ,
1097 RouteCD ,
1098 VisitDate ,
1099 LastVisitDate ,
1100 NextVisitDate ,
1101 VisitOrder ,
1102 SiteID ,
1103 NoOfVisitToEndMonth ,
1104 SuggestTimeVisit ,
1105 ContactPerson ,
1106 Phone ,
1107 VisitFeq ,
1108 Avg3M ,
1109 Avg6M ,
1110 Avg12M ,
1111 DebtLimit ,
1112 CurrentDebt ,
1113 AvatarCaptureParam ,
1114 DebtParam ,
1115 Note ,
1116 FileName ,
1117 ExclusiveGroupCD ,
1118 LocationType ,
1119 DayVisitInWeek ,
1120 SellingType ,
1121 Channel ,
1122 CMNN ,
1123 Birthday ,
1124 BankAccNo ,
1125 BankAccOwner ,
1126 BankName ,
1127 UpdateParam ,
1128 WareHouseCD ,
1129 RevQTD ,
1130 RevYTD
1131 )
1132 SELECT DISTINCT
1133 e.EmployeeICD , -- UserName - nvarchar(50)
1134 c.CustomerCD , -- CustomerCD - nvarchar(50)
1135 c.CustomerName , -- CustomerName - nvarchar(255)
1136 c.LocationCD , -- LocationCD - nvarchar(50)
1137 c.LocationName , -- LocationName - nvarchar(255)
1138 b.BranchCD , -- DistributorCD - nvarchar(50)
1139 c.AddressLine1 , -- AddressLine1 - nvarchar(255)
1140 c.AddressLine2 , -- AddressLine2 - nvarchar(255)
1141 c.AddressLine3 , -- AddressLine3 - nvarchar(255)
1142 c.AddressLine4 , -- AddressLine4 - nvarchar(255)
1143 c.StreetID , -- StreetID - nvarchar(50)
1144 c.WardID , -- WardID - nvarchar(50)
1145 c.DistrictID , -- DistrictID - nvarchar(50)
1146 c.ProvinceID , -- ProvinceID - nvarchar(50)
1147 c.RegionID , -- RegionID - nvarchar(50)
1148 c.Latitude , -- Latitude - float
1149 c.Longitude , -- Longitude - float
1150 CAST(ISNULL(oc.NumOrder, 0) AS NVARCHAR) + '/'
1151 + CAST(ISNULL(vc.NumVisit, 0) AS NVARCHAR) , -- ActivityStatus - nvarchar(50)
1152 c.CustomerPriceClassID , -- CustomerPriceClassID - nvarchar(50)
1153 c.DMSAttribute1 , -- DMSAttribute1 - nvarchar(50)
1154 c.DMSAttribute2 , -- DMSAttribute2 - nvarchar(50)
1155 c.DMSAttribute3 , -- DMSAttribute3 - nvarchar(50)
1156 c.DMSAttribute4 , -- DMSAttribute4 - nvarchar(50)
1157 c.DMSAttribute5 , -- DMSAttribute5 - nvarchar(50)
1158 c.DMSAttribute6 , -- DMSAttribute6 - nvarchar(50)
1159 c.DMSAttribute7 , -- DMSAttribute7 - nvarchar(50)
1160 c.DMSAttribute8 , -- DMSAttribute8 - nvarchar(50)
1161 c.DMSAttribute9 , -- DMSAttribute9 - nvarchar(50)
1162 c.DMSAttribute10 , -- DMSAttribute10 - nvarchar(50)
1163 c.SellingCat , -- SellingCat - nvarchar(50)
1164 ISNULL(v.RouteCD, e.RouteCD + '_T') , -- RouteCD - nvarchar(50)
1165 ISNULL(v.VisitDate, CONVERT(NVARCHAR, GETDATE(), 101)) , -- VisitDate - date
1166 l.BeginTime LastVisitDate , -- LastVisitDate - date
1167 ISNULL(n.VisitDate, GETDATE()) NextVisitDate , -- NextVisitDate - date
1168 ISNULL(v.VisitOrder, 0) , -- VisitOrder - int
1169 ISNULL(v.SiteID, m.SiteID) , -- SiteID - int
1170 2 AS NoOfVisitToEndMonth , -- NoOfVisitToEndMonth - int
1171 c.SuggestTimeVisit , -- SuggestTimeVisit - nvarchar(50)
1172 c.ContactPerson , -- ContactPerson - nvarchar(255)
1173 c.Phone , -- Phone - nvarchar(50)
1174 'F4' , -- VisitFeq - nvarchar(50)
1175 ISNULL(ac.AVG3M, 0.0) , -- Avg3M - decimal
1176 ISNULL(ac.AVG6M, 0.0) , -- Avg6M - decimal
1177 ISNULL(ac.AVG12M, 0.0) , -- Avg12M - decimal
1178 c.DebtLimit , -- DebtLimit - decimal
1179 c.CurrentDebt , -- CurrentDebt - decimal
1180 1 , -- AvatarCaptureParam - int
1181 c.DebtParam , -- DebtParam - int
1182 N'' , -- Note - nvarchar(1000)
1183 ISNULL(av.AvatarName, '') ,
1184 c.ExclusiveGroupCD ,
1185 0 , -- LocationType - int
1186 N'' , -- DayVisitInWeek - nvarchar(50)
1187 0 , -- SellingType - int
1188 N'' , -- Channel - nvarchar(50)
1189 N'' , -- CMNN - nvarchar(50)
1190 N'' , -- Birthday - nvarchar(50)
1191 N'' , -- BankAccNo - nvarchar(50)
1192 N'' , -- BankAccOwner - nvarchar(50)
1193 N'' , -- BankName - nvarchar(50)
1194 0 , -- UpdateParam - int
1195 s.SiteCD ,
1196 ISNULL(ac.RevQTD, 0.0) , -- RevQTD - decimal
1197 ISNULL(ac.RevYTD, 0.0) -- RevYTD - decimal
1198 FROM dbo.DMSAimEmployeeList e
1199 INNER JOIN PNCDMS..DMSMCPDetail m ON m.RefNbr = e.RefNBR
1200 AND m.RouteCD = e.RouteCD
1201 AND m.Status = 'G'
1202 AND m.CompanyID = @CompanyID
1203 INNER JOIN dbo.DMSAllCustomer c ON c.BaccountID = m.CustomerID
1204 AND c.LocationID = m.CustomerLocationID
1205 INNER JOIN PNCDMS..INSite s ON m.CompanyID = s.CompanyID
1206 AND m.SiteID = s.SiteID
1207 INNER JOIN PNCDMS..Branch b ON b.BranchID = m.DistributorID
1208 AND m.CompanyID = b.CompanyID
1209 LEFT JOIN dbo.#vs_Today_visitplan v ON v.CompanyID = m.CompanyID
1210 AND m.CustomerID = v.CustomerID
1211 AND m.CustomerLocationID = v.CustomerLocationID
1212 AND v.RefNBR = e.RefNBR
1213 AND v.RouteCD = e.RouteCD
1214 AND v.SalespersonID = e.EmployeeID
1215 LEFT JOIN dbo.DMSAimCustomerAvatar av ON c.CustomerCD = av.CustomerCD
1216 AND c.LocationCD = av.LocationCD
1217 LEFT JOIN dbo.#vs_NumOfOrderInQuaterByCus oc ON c.BaccountID = oc.CustomerID
1218 AND c.LocationID = oc.CustomerLocationID
1219 LEFT JOIN dbo.#vs_NumOfVisitInQuaterByCust vc ON c.CustomerCD = vc.CustomerCD
1220 AND c.LocationCD = vc.LocationCD
1221 LEFT JOIN #vs_avgByCus ac ON c.BaccountID = ac.CustomerID
1222 AND c.LocationID = ac.CustomerLocationID
1223 AND ac.BranchID = b.BranchID
1224 LEFT JOIN dbo.#vs_sync_nextvisit n ON v.SalespersonID = n.SalespersonID
1225 AND v.CustomerID = n.CustomerID
1226 AND v.CustomerLocationID = n.CustomerLocationID
1227 LEFT JOIN dbo.#vs_sync_lastvisit l ON c.CustomerCD = l.CustomerCD
1228 AND c.LocationCD = l.LocationCD
1229 AND e.EmployeeICD = l.UserName;
1230
1231 DROP TABLE #vs_avgByCus;
1232 DROP TABLE #vs_NumOfOrderInQuaterByCus;
1233 DROP TABLE #vs_NumOfVisitInQuaterByCust;
1234 DROP TABLE #vs_sync_lastvisit;
1235 DROP TABLE #vs_sync_nextvisit;
1236 DROP TABLE #vs_Today_visitplan;
1237
1238 UPDATE dbo.DMSAimCustomer
1239 SET AvatarCaptureParam = 0 ,
1240 UpdateParam = 0
1241 WHERE FileName <> '';
1242 UPDATE dbo.DMSAimCustomer
1243 SET UpdateParam = 1
1244 WHERE FileName = '';
1245
1246 UPDATE dbo.DMSAimCustomer
1247 SET AvatarCaptureParam = 2
1248 WHERE FileName = ''
1249 AND RouteCD NOT LIKE '%_T';
1250
1251 -- Lấy những khách hà ng được giao bởi nhiá»u NPP
1252
1253 INSERT INTO dbo.DMSAimCustomerDistributor
1254 ( UserName ,
1255 CustomerCD ,
1256 LocationCD ,
1257 DistributorCD ,
1258 Ordered
1259 )
1260 SELECT u.UserName , -- UserName - nvarchar(50)
1261 c.CustomerCD , -- CustomerCD - nvarchar(50)
1262 c.LocationCD , -- LocationCD - nvarchar(50)
1263 b.BranchCD , -- DistributorCD - nvarchar(50)
1264 1 -- Ordered - int
1265 FROM PNCDMS..DMSCustomerDeliveryMapping m
1266 INNER JOIN dbo.DMSAllCustomer c ON m.CustomerID = c.BaccountID
1267 AND m.CustomerLocationID = c.LocationID
1268 AND m.CompanyID = @CompanyID
1269 INNER JOIN dbo.DMSAimCustomer u ON c.CustomerCD = u.CustomerCD
1270 AND c.LocationCD = u.LocationCD
1271 INNER JOIN PNCDMS..Branch b ON m.DistributorID = b.BranchID
1272 AND b.CompanyID = @CompanyID
1273 WHERE m.IsActive = 1;
1274
1275--Load danh sach NPP ma SM dc gan vao de giao hang
1276 INSERT INTO dbo.DMSAimDistributor
1277 ( UserName ,
1278 DistributorCD ,
1279 DistributorName ,
1280 DistributorLat ,
1281 DistributorLong
1282 )
1283 SELECT DISTINCT
1284 p.UserName , -- UserName - nvarchar(50)
1285 b.BranchCD , -- DistributorCD - nvarchar(50)
1286 a.AcctName , -- DistributorName - nvarchar(255)
1287 0.0 , -- DistributorLat - float
1288 0.0 -- DistributorLong - float
1289 FROM dbo.DMSAimCustomerDistributor p
1290 INNER JOIN PNCDMS..Branch b ON p.DistributorCD = b.BranchCD
1291 AND b.CompanyID = @CompanyID
1292 INNER JOIN PNCDMS..BAccount a ON b.CompanyID = a.CompanyID
1293 AND a.BAccountID = b.BAccountID
1294 LEFT JOIN dbo.DMSAimDistributor d ON d.DistributorCD = p.DistributorCD
1295 AND d.UserName = p.UserName
1296 WHERE d.UserName IS NULL;
1297
1298 END;
1299
1300
1301
1302GO
1303
1304GO
1305IF OBJECT_ID( 'sp_Sync_file_new', 'P' ) IS NOT NULL
1306 DROP PROCEDURE dbo.sp_Sync_file_new;
1307GO
1308CREATE PROCEDURE [dbo].[sp_Sync_file_new] @CompanyID INT
1309AS
1310 BEGIN
1311 DECLARE @StagingURL NVARCHAR(250);
1312 DECLARE @AcuURL NVARCHAR(250);
1313
1314 SELECT @StagingURL = StagingURL ,
1315 @AcuURL = AcuURL
1316 FROM dbo.DMSAimStagingConfig;
1317
1318 INSERT INTO dbo.DMSAimFileSync
1319 ( UserName ,
1320 Link ,
1321 MD5CheckSum ,
1322 SaveTo
1323 )
1324 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
1325 Link , -- Link - nvarchar(255)
1326 md5 , -- MD5CheckSum - nvarchar(50)
1327 saveto -- SaveTo - nvarchar(255)
1328 FROM dbo.DMSAimUploadFileList
1329 CROSS JOIN dbo.DMSAimEmployeeList e
1330
1331 INSERT INTO dbo.DMSAimPresentation
1332 ( UserName ,
1333 PresentationID ,
1334 [Type] ,
1335 Descr ,
1336 Startdate ,
1337 Enddate
1338 )
1339 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
1340 PresentationID , -- PresentationID - nvarchar(50)
1341 [Type] , -- Type - nvarchar(50)
1342 Descr , -- Descr - nvarchar(1000)
1343 Startdate , -- Startdate - date
1344 Enddate -- Enddate - date
1345 FROM dbo.DMSAimPresentationList
1346 CROSS JOIN dbo.DMSAimEmployeeList e;
1347
1348
1349 INSERT INTO dbo.DMSAimPresentationFile
1350 ( UserName ,
1351 PresentationID ,
1352 Filename ,
1353 FileLocation
1354 )
1355 SELECT DISTINCT
1356 e.EmployeeICD , -- UserName - nvarchar(50)
1357 PresentationID , -- PresentationID - nvarchar(50)
1358 Filename , -- Filename - nvarchar(255)
1359 FileLocation -- FileLocation - nvarchar(500)
1360 FROM dbo.DMSAimPresentationFileList
1361 CROSS JOIN dbo.DMSAimEmployeeList e;
1362
1363
1364 --SMAvatar
1365 TRUNCATE TABLE dbo.DMSAimSMAvatar
1366 INSERT INTO dbo.DMSAimSMAvatar
1367 ( UserName, AvatarFile, MD5 )
1368 SELECT UserName,
1369 REVERSE(LEFT(REVERSE(AvatarFile),
1370 CHARINDEX('/', REVERSE(AvatarFile)) - 1)) ,
1371 MD5
1372 FROM dbo.DMSAimUploadedSMAvatar
1373
1374 INSERT INTO dbo.DMSAimFileSync
1375 ( UserName ,
1376 Link ,
1377 MD5CheckSum ,
1378 SaveTo
1379 )
1380 SELECT UserName , -- UserName - nvarchar(50)
1381 @StagingURL + '/SalesmanAvatarImage/' + AvatarFile , -- Link - nvarchar(255)
1382 MD5 , -- MD5CheckSum - nvarchar(50)
1383 '/sdcard/NextGen/AimBG/sm_avatar.jpg' -- SaveTo - nvarchar(255)
1384 FROM dbo.DMSAimSMAvatar a
1385
1386 --Cust avatar
1387 INSERT INTO dbo.DMSAimFileSync
1388 ( UserName ,
1389 Link ,
1390 MD5CheckSum ,
1391 SaveTo
1392 )
1393 SELECT UserName , -- UserName - nvarchar(50)
1394 @StagingURL + '/AvartaImage/' + AvatarName , -- Link - nvarchar(255)
1395 r.MD5Checksum , -- MD5CheckSum - nvarchar(50)
1396 '/sdcard/NextGen/AimAvatar/' + a.AvatarName -- SaveTo - nvarchar(255)
1397 FROM dbo.DMSAimCustomer c
1398 INNER JOIN dbo.DMSAimCustomerAvatar a ON a.CustomerCD = c.CustomerCD
1399 AND a.LocationCD = c.LocationCD
1400 INNER JOIN dbo.DMSAimImageReceived r ON a.AvatarName = r.ImageName
1401 AND r.Type = 'A'
1402
1403 -- planogram
1404 UNION ALL
1405 SELECT DISTINCT
1406 e.EmployeeICD ,
1407 @AcuURL + ImageFile ,
1408 MD5 ,
1409 '/sdcard/NextGen/AimImage/' + ImageName
1410 FROM PNCDMS..DMSProgramPlanogramDetailImage
1411 CROSS JOIN dbo.DMSAimEmployeeList e
1412 WHERE CompanyID = @CompanyID
1413 AND IsActive = 1;
1414 END;
1415GO
1416IF OBJECT_ID('sp_Sync_Price_Priority_Distributor_Inventory_SalesOrg', 'P') IS NOT NULL
1417 DROP PROCEDURE dbo.sp_Sync_Price_Priority_Distributor_Inventory_SalesOrg;
1418GO
1419CREATE PROCEDURE [dbo].[sp_Sync_Price_Priority_Distributor_Inventory_SalesOrg]
1420 @CompanyID INT
1421AS
1422 BEGIN
1423
1424 TRUNCATE TABLE dbo.DMSAimPricePriorityDistributor;
1425 TRUNCATE TABLE dbo.DMSAimPricePriorityInventory;
1426 TRUNCATE TABLE dbo.DMSAimPricePrioritySalesOrg;
1427 --Get price with priority of distributor
1428 INSERT INTO dbo.DMSAimPricePriorityDistributor
1429 ( CompanyID ,
1430 RefNbr ,
1431 DistributorID ,
1432 DistributorCD
1433 )
1434 SELECT p.CompanyID ,
1435 p.RefNbr ,
1436 p.DistributorID ,
1437 b.BranchCD
1438 FROM PNCDMS..DMSPricePriorityDistributor p
1439 JOIN
1440 PNCDMS..Branch b
1441 ON b.CompanyID = p.CompanyID AND b.BranchID = p.DistributorID
1442 WHERE p.CompanyID = @CompanyID
1443
1444 --Get price with priority of Inventory
1445
1446 INSERT INTO dbo.DMSAimPricePriorityInventory
1447 ( CompanyID ,
1448 RefNbr ,
1449 InventoryId ,
1450 InventoryCD ,
1451 UOM ,
1452 Price ,
1453 Priority ,
1454 CreatedDateTime
1455 )
1456 SELECT p.CompanyID ,
1457 p.RefNbr ,
1458 p.InventoryID,
1459 i.InventoryCD,
1460 p.UOM,
1461 p.Price,
1462 p.Priority,
1463 p.CreatedDateTime
1464 FROM PNCDMS..DMSPricePriorityInventory p
1465 INNER JOIN
1466 dbo.DMSAimInventoryList i
1467 ON i.InventoryID = p.InventoryID
1468 WHERE CompanyID = @CompanyID
1469
1470 --Get price with priority of SalesOrg
1471 INSERT INTO dbo.DMSAimPricePrioritySalesOrg
1472 ( CompanyID ,
1473 RefNbr ,
1474 SalesOrgID ,
1475 ChannelID ,
1476 CreatedDateTime ,
1477 SalesOrgCD
1478 )
1479 SELECT CompanyID ,
1480 RefNbr ,
1481 SalesOrgID,
1482 ChannelID,
1483 CreatedDateTime,
1484 SalesOrgCD
1485
1486 FROM PNCDMS..DMSPricePrioritySalesOrg
1487 WHERE CompanyID = @CompanyID
1488
1489 END
1490
1491GO
1492GO
1493IF OBJECT_ID( 'sp_Sync_TMKT_New', 'P' ) IS NOT NULL
1494 DROP PROCEDURE dbo.sp_Sync_TMKT_New;
1495GO
1496CREATE PROCEDURE [dbo].[sp_Sync_TMKT_New] @CompanyID INT
1497AS
1498 BEGIN
1499 --Insert customer group define
1500 INSERT INTO dbo.DMSAimTMKTCustomerGroupHeader
1501 ( UserName ,
1502 GroupCD ,
1503 Descr ,
1504 CustomerType ,
1505 CustomerGroupType
1506 )
1507 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
1508 GroupCD , -- GroupCD - nvarchar(50)
1509 Descr , -- Descr - nvarchar(255)
1510 CustomerType , -- CustomerType - nvarchar(1)
1511 CustomerGroupType -- CustomerGroupType - nvarchar(1)
1512 FROM PNCDMS..DMSCustomerGroupHeader h
1513 CROSS JOIN dbo.DMSAimEmployeeList e
1514 WHERE Status = 'R'
1515 AND CompanyID = @CompanyID;
1516 INSERT INTO dbo.DMSAimTMKTCustomerGroupAttribute
1517 ( UserName ,
1518 GroupCD ,
1519 Attribute0 ,
1520 Attribute1 ,
1521 Attribute2 ,
1522 Attribute3 ,
1523 Attribute4 ,
1524 Attribute5 ,
1525 Attribute6 ,
1526 Attribute7 ,
1527 Attribute8 ,
1528 Attribute9
1529 )
1530 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
1531 h.GroupCD , -- GroupCD - nvarchar(50)
1532 Attribute0 , -- Attribute0 - int
1533 Attribute1 , -- Attribute1 - int
1534 Attribute2 , -- Attribute2 - int
1535 Attribute3 , -- Attribute3 - int
1536 Attribute4 , -- Attribute4 - int
1537 Attribute5 , -- Attribute5 - int
1538 Attribute6 , -- Attribute6 - int
1539 Attribute7 , -- Attribute7 - int
1540 Attribute8 , -- Attribute8 - int
1541 Attribute9 -- Attribute9 - int
1542 FROM PNCDMS..DMSCustomerGroupAttribute a
1543 INNER JOIN PNCDMS..DMSCustomerGroupHeader h ON h.CompanyID = a.CompanyID
1544 AND h.GroupID = a.GroupID
1545 CROSS JOIN dbo.DMSAimEmployeeList e
1546 WHERE h.Status = 'R'
1547 AND a.CompanyID = @CompanyID;
1548 INSERT INTO dbo.DMSAimTMKTCustomerGroupList
1549 ( UserName ,
1550 GroupCD ,
1551 CustomerCD ,
1552 LocationCD
1553 )
1554 SELECT DISTINCT
1555 u.UserName , -- UserName - nvarchar(50)
1556 h.GroupCD , -- GroupCD - nvarchar(50)
1557 c.CustomerCD , -- CustomerCD - nvarchar(50)
1558 c.LocationCD -- LocationCD - nvarchar(50)
1559 FROM PNCDMS..DMSCustomerGroupList l
1560 INNER JOIN PNCDMS..DMSCustomerGroupHeader h ON h.CompanyID = l.CompanyID
1561 AND h.GroupID = l.GroupID
1562 AND h.CompanyID = @CompanyID
1563 INNER JOIN dbo.DMSAllCustomer c ON c.LocationID = l.CustomerID
1564 INNER JOIN dbo.DMSAimCustomer u ON c.LocationCD = u.LocationCD
1565 AND c.CustomerCD = u.CustomerCD;
1566
1567 --DN TMKT program
1568 INSERT INTO dbo.DMSAimTMKTProgram
1569 ( UserName ,
1570 ProgramCD ,
1571 ProgramType ,
1572 Descr ,
1573 LoyaltyType ,
1574 IsRevenue ,
1575 Frequency ,
1576 IncentiveType ,
1577 ApplyTo ,
1578 ApplicableTo ,
1579 StartRegisterDate ,
1580 EndRegisterDate ,
1581 StartExecutionDate ,
1582 EndExecutionDate ,
1583 RegisterType ,
1584 EffectiveDateType ,
1585 IsAccumulate ,
1586 AccumulateBy ,
1587 IsRatio ,
1588 RatioBy
1589 )
1590 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
1591 ProgramCD , -- ProgramCD - nvarchar(50)
1592 ProgramType , -- ProgramType - nvarchar(1)
1593 Descr , -- Descr - nvarchar(255)
1594 '' , -- LoyaltyType - nvarchar(1)
1595 0 , -- IsRevenue - int
1596 Frequency , -- Frequency - nvarchar(1)
1597 IncentiveBy , -- IncentiveType - nvarchar(1)
1598 ApplyTo , -- ApplyTo - nvarchar(1)
1599 ApplicableTo , -- ApplicableTo - nvarchar(1)
1600 StartRegisterDate , -- StartRegisterDate - date
1601 EndRegisterDate , -- EndRegisterDate - date
1602 StartExecutionDate , -- StartExecutionDate - date
1603 EndExecutionDate , -- EndExecutionDate - date
1604 RegisterType , -- RegisterType - nvarchar(1)
1605 EffectiveDateType , -- EffectiveDateType - nvarchar(1)
1606 ISNULL(IsAccumulate, 0) , -- IsAccumulate - int
1607 ISNULL(AccumulateBy, '') , -- AccumulateBy - nvarchar(1)
1608 ISNULL(IsRatio, 0) , -- IsRatio - int
1609 ISNULL(RatioBy, '') -- RatioBy - nvarchar(1)
1610 FROM PNCDMS..DMSProgram
1611 CROSS JOIN dbo.DMSAimEmployeeList e
1612 WHERE Status = 'R'
1613 AND CompanyID = @CompanyID
1614 AND ( ( DATEDIFF(dd, StartRegisterDate, GETDATE()) >= 0
1615 AND DATEDIFF(DD, EndRegisterDate, GETDATE()) <= 0
1616 )
1617 OR ( DATEDIFF(dd, StartExecutionDate, GETDATE()) >= 0
1618 AND DATEDIFF(DD, EndExecutionDate,
1619 GETDATE()) <= 0
1620 )
1621 );
1622
1623
1624 INSERT INTO dbo.DMSAimTMKTProgramApplicableTo
1625 ( UserName ,
1626 ProgramCD ,
1627 GroupCD
1628 )
1629 SELECT m.UserName , -- UserName - nvarchar(50)
1630 p.ProgramCD , -- ProgramCD - nvarchar(50)
1631 h.GroupCD -- GroupCD - nvarchar(50)
1632 FROM PNCDMS..DMSProgramApplicableTo t
1633 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = t.CompanyID
1634 AND t.ProgramID = p.ProgramID
1635 AND t.CompanyID = @CompanyID
1636 INNER JOIN PNCDMS..DMSCustomerGroupHeader h ON h.CompanyID = t.CompanyID
1637 AND t.CustomerGroupID = h.GroupID
1638 INNER JOIN dbo.DMSAimTMKTProgram m ON m.ProgramCD = p.ProgramCD;
1639
1640 INSERT INTO dbo.DMSAimTMKTProgramApplyTo
1641 ( UserName ,
1642 ProgramCD ,
1643 ApplyToCD ,
1644 Type
1645 )
1646 SELECT m.UserName , -- UserName - nvarchar(50)
1647 p.ProgramCD , -- ProgramCD - nvarchar(50)
1648 t.ApplyToID , -- ApplyToCD - nvarchar(50)
1649 t.Type -- Type - nvarchar(1)
1650 FROM PNCDMS..DMSProgramApplyTo t
1651 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = t.CompanyID
1652 AND t.ProgramID = p.ProgramID
1653 AND t.CompanyID = @CompanyID
1654 INNER JOIN dbo.DMSAimTMKTProgram m ON p.ProgramCD = m.ProgramCD;
1655
1656
1657
1658 INSERT INTO dbo.DMSAimTMKProgramBreakDownDetail
1659 ( UserName ,
1660 ProgramCD ,
1661 BreakByLevelID ,
1662 BreakByLevelName ,
1663 MinAmtRegister ,
1664 DisplayClass ,
1665 Revenue ,
1666 IncentivePercent ,
1667 IncentiveAmt ,
1668 Weight,
1669 Volume,
1670 Quantity
1671 )
1672 SELECT m.UserName , -- UserName - nvarchar(50)
1673 p.ProgramCD , -- ProgramCD - nvarchar(50)
1674 b.BreakByLevelID , -- BreakByLevelID - int
1675 b.BreakByLevelName , -- BreakByLevelName - nvarchar(255)
1676 0 , -- MinAmtRegister - decimal
1677 DisplayClass , -- DisplayClass - nvarchar(50)
1678 ISNULL(b.Revenue, 0) , -- Revenue - decimal
1679 b.IncentivePercent , -- IncentivePercent - decimal
1680 b.IncentiveAmt , -- IncentiveAmt - decimal
1681 ISNULL(b.Weight, 0), -- Weight - int
1682 ISNULL(b.Volume, 0),
1683 ISNULL(b.Quantity, 0)
1684
1685 FROM PNCDMS..DMSProgramBreakDownDetail b
1686 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = b.CompanyID
1687 AND p.ProgramID = b.ProgramID
1688 AND b.CompanyID = @CompanyID
1689 INNER JOIN dbo.DMSAimTMKTProgram m ON p.ProgramCD = m.ProgramCD;
1690
1691 INSERT INTO dbo.DMSAimTMKTProgramFreeItem
1692 ( UserName ,
1693 ProgramCD ,
1694 BreakByLevelID ,
1695 InventoryCD ,
1696 Qty
1697 )
1698 SELECT m.UserName , -- UserName - nvarchar(50)
1699 p.ProgramCD , -- ProgramCD - nvarchar(50)
1700 f.BreakByLevelID , -- BreakByLevelID - int
1701 l.InventoryCD , -- InventoryCD - nvarchar(50)
1702 f.Qty -- Qty - decimal
1703 FROM PNCDMS..DMSProgramFreeItem f
1704 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = f.CompanyID
1705 AND p.ProgramID = f.ProgramID
1706 AND f.CompanyID = @CompanyID
1707 INNER JOIN dbo.DMSAimInventoryList l ON f.InventoryID = l.InventoryID
1708 INNER JOIN dbo.DMSAimTMKTProgram m ON p.ProgramCD = m.ProgramCD;
1709
1710 INSERT INTO dbo.DMSAimTMKTProgramRatioProduct
1711 ( UserName ,
1712 ProgramCD ,
1713 BreakByLevelID ,
1714 LineNbr ,
1715 InventoryCD ,
1716 IsGroup ,
1717 GroupItemCD ,
1718 Operators ,
1719 Ratio ,
1720 Target ,
1721 Qty
1722 )
1723 SELECT m.UserName , -- UserName - nvarchar(50)
1724 p.ProgramCD , -- ProgramCD - nvarchar(50)
1725 r.BreakByLevelID , -- BreakByLevelID - int
1726 ISNULL(r.LineNbr, 0) , -- LineNbr - int
1727 ISNULL(l.InventoryCD,'') , -- InventoryCD - nvarchar(50)
1728 ISNULL(r.IsGroup, 0) , -- IsGroup - int
1729 ISNULL(r.GroupItemID, '') , -- GroupItemCD - nvarchar(50)
1730 ISNULL(r.Operators, '') , -- Operators - nvarchar(2)
1731 r.[Percent] , -- Ratio - decimal
1732 r.Amount , -- Target - decimal
1733 r.Qty -- Qty - decimal
1734 FROM PNCDMS..DMSProgramRatioProduct r
1735 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = r.CompanyID
1736 AND p.ProgramID = r.ProgramID
1737 AND r.CompanyID = @CompanyID
1738 --INNER JOIN dbo.DMSAimInventoryList l ON r.InventoryID = l.InventoryID
1739 LEFT JOIN dbo.DMSAimInventoryList l ON r.InventoryID = l.InventoryID
1740 INNER JOIN dbo.DMSAimTMKTProgram m ON p.ProgramCD = m.ProgramCD;
1741
1742 INSERT INTO dbo.DMSAimTMKTProgramRegister
1743 ( UserName ,
1744 ProgramCD ,
1745 CustomerCD ,
1746 LocationCD ,
1747 LevelID ,
1748 RegisterDate ,
1749 RegisterAmt ,
1750 ResultImage ,
1751 IsRegister ,
1752 Status ,
1753 ReasonID ,
1754 Times
1755 )
1756 SELECT DISTINCT
1757 a.UserName , -- UserName - nvarchar(50)
1758 p.ProgramCD , -- ProgramCD - nvarchar(50)
1759 c.CustomerCD , -- CustomerCD - nvarchar(50)
1760 c.LocationCD , -- LocationCD - nvarchar(50)
1761 r.LevelID , -- LevelID - int
1762 ISNULL(r.RegisterDate, '2016-01-01') , -- RegisterDate - date
1763 r.RegisterAmt , -- RegisterAmt - decimal
1764 r.ResultImage , -- ResultImage - nvarchar(1)
1765 r.IsRegister , -- IsRegister - int
1766 r.Status , -- Status - int
1767 r.ReasonID , -- ReasonID - int
1768 r.Times -- Times - int
1769 FROM PNCDMS..DMSProgramRegister r
1770 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = r.CompanyID
1771 AND p.ProgramID = r.ProgramID
1772 AND r.CompanyID = @CompanyID
1773 INNER JOIN dbo.DMSAllCustomer c ON r.CustomerID = c.LocationID
1774 INNER JOIN dbo.DMSAimCustomer a ON a.CustomerCD = c.CustomerCD
1775 AND a.LocationCD = c.LocationCD
1776 INNER JOIN dbo.DMSAimTMKTProgram m ON p.ProgramCD = m.ProgramCD;
1777 --AND r.Status = 'A'
1778
1779
1780 INSERT INTO dbo.DMSAimTMKTProgramPlanogram
1781 ( UserName ,
1782 RefNbr ,
1783 Descr ,
1784 Type ,
1785 IsProgram ,
1786 ProgramCD ,
1787 Stage ,
1788 Status ,
1789 StartDate ,
1790 EndDate
1791 )
1792 SELECT DISTINCT
1793 e.EmployeeICD , -- UserName - nvarchar(50)
1794 p.RefNbr , -- RefNbr - nvarchar(50)
1795 p.Descr , -- Descr - nvarchar(255)
1796 p.Type , -- Type - nvarchar(1)
1797 p.IsProgram , -- IsProgram - int
1798 ISNULL(d.ProgramCD, '') , -- ProgramCD - nvarchar(50)
1799 p.Stage , -- Stage - nvarchar(1)
1800 p.Status , -- Status - nvarchar(1)
1801 p.StartDate , -- StartDate - date
1802 p.EndDate -- EndDate - date
1803 FROM PNCDMS..DMSProgramPlanogram p
1804 LEFT JOIN PNCDMS..DMSProgram d ON p.CompanyID = d.CompanyID
1805 AND p.ProgramID = d.ProgramID
1806 CROSS JOIN dbo.DMSAimEmployeeList e
1807 WHERE p.Status = 'R'
1808 AND p.CompanyID = @CompanyID
1809 AND DATEDIFF(dd, p.StartDate, GETDATE()) >= 0
1810 AND DATEDIFF(DD, p.EndDate, GETDATE()) <= 0;
1811
1812 INSERT INTO dbo.DMSAimTMKTProgramPlanogramDetail
1813 ( UserName ,
1814 RefNbr ,
1815 BreakByLevelID ,
1816 ShopFormat ,
1817 LineNBR
1818 )
1819 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
1820 d.RefNbr , -- RefNbr - nvarchar(50)
1821 d.BreakByLevelID , -- BreakByLevelID - int
1822 d.ShopFormat , -- ShopFormat - nvarchar(50)
1823 d.LineNbr -- LineNBR - int
1824 FROM PNCDMS..DMSProgramPlanogramDetail d
1825 INNER JOIN PNCDMS..DMSProgramPlanogram p ON p.CompanyID = d.CompanyID
1826 AND p.RefNbr = d.RefNbr
1827 CROSS JOIN dbo.DMSAimEmployeeList e
1828 WHERE p.Status = 'R'
1829 AND p.CompanyID = @CompanyID;
1830
1831 INSERT INTO dbo.DMSAimTMKTProgramPlanogramDetailImage
1832 ( UserName ,
1833 RefNbr ,
1834 LineNBR ,
1835 ImageID ,
1836 ImageName ,
1837 Descr
1838 )
1839 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
1840 i.RefNbr , -- RefNbr - nvarchar(50)
1841 i.LineNbr , -- LineNBR - int
1842 i.ImageID , -- ImageID - int
1843 i.ImageName , -- ImageName - nvarchar(255)
1844 i.Descr -- Descr - nvarchar(255)
1845 FROM PNCDMS..DMSProgramPlanogramDetailImage i
1846 INNER JOIN PNCDMS..DMSProgramPlanogram p ON p.CompanyID = i.CompanyID
1847 AND p.RefNbr = i.RefNbr
1848 CROSS JOIN dbo.DMSAimEmployeeList e
1849 WHERE p.Status = 'R'
1850 AND i.IsActive = 1
1851 AND p.CompanyID = @CompanyID;
1852
1853 -- Lay nhom SP TMKT
1854 INSERT INTO dbo.DMSAimTMKTPROItemsGroupHeader
1855 ( UserName ,
1856 GroupCD ,
1857 Status ,
1858 Description
1859 )
1860 SELECT DISTINCT
1861 e.EmployeeICD ,
1862 GroupID ,-- GroupCD - nvarchar(50)
1863 g.Status ,
1864 Description -- Desc - nvarchar(100)
1865 FROM PNCDMS..DMSPROItemsGroupHeader g
1866 CROSS JOIN dbo.DMSAimEmployeeList e
1867 WHERE Status = 'R'
1868 AND g.CompanyID = @CompanyID;
1869
1870 INSERT INTO dbo.DMSAimTMKTPROItemsGroupDetail
1871 ( UserName ,
1872 GroupCD ,
1873 InventoryCD ,
1874 Rate
1875 )
1876 SELECT e.EmployeeICD ,
1877 d.GroupID , -- GroupID - int
1878 i.InventoryCD , -- InventoryID - int
1879 ISNULL(d.Rate, 0) AS Rate -- Rate - Decimal 18,2
1880 FROM PNCDMS..DMSPROItemsGroupDetail d
1881 INNER JOIN dbo.DMSAimInventoryList i ON d.InventoryID = i.InventoryID
1882 AND d.CompanyID = @CompanyID
1883 INNER JOIN PNCDMS..DMSPROItemsGroupHeader g ON d.CompanyID = g.CompanyID
1884 AND d.GroupID = g.GroupID
1885 CROSS JOIN dbo.DMSAimEmployeeList e
1886 WHERE d.Active = 1
1887 AND g.Status = 'R';
1888
1889 --INSERT INTO dbo.DMSAimCustomerMTDProgram
1890 -- ( UserName ,
1891 -- CustomerCD ,
1892 -- LocationCD ,
1893 -- ProgramCD ,
1894 -- MTDQty ,
1895 -- MTDAmount ,
1896 -- MTDWeight ,
1897 -- MTDVolume
1898 -- )
1899 -- SELECT c.UserName , -- UserName - nvarchar(50)
1900 -- c.CustomerCD , -- CustomerCD - nvarchar(50)
1901 -- c.LocationCD , -- LocationCD - nvarchar(50)
1902 -- b.ProgramCD , -- ProgramCD - nvarchar(50)
1903 -- SUM(b.InventoryQty) , -- MTDQty - int
1904 -- SUM(b.InventoryRevenue) , -- MTDAmount - float
1905 -- 0.0 , -- MTDWeight - float
1906 -- 0.0 -- MTDVolume - float
1907 -- FROM dbo.DMSAimCustomer c
1908 -- INNER JOIN dbo.DMSAllCustomer a ON c.CustomerCD = a.CustomerCD
1909 -- AND c.LocationCD = a.LocationCD
1910 -- INNER JOIN PNCBaseline..DMSBLProgramTracking b ON a.BaccountID = b.CustomerID
1911 -- AND a.LocationID = b.CustomerLocationID
1912 -- AND b.CompanyID = @CompanyID
1913 -- AND b.Month = MONTH(GETDATE())
1914 -- AND b.Year = YEAR(GETDATE())
1915 -- GROUP BY c.UserName , -- UserName - nvarchar(50)
1916 -- c.CustomerCD , -- CustomerCD - nvarchar(50)
1917 -- c.LocationCD , -- LocationCD - nvarchar(50)
1918 -- b.ProgramCD;
1919 END;
1920
1921
1922
1923GO
1924GO