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