· 7 years ago · Sep 26, 2018, 02:44 PM
1
2SET ANSI_NULLS ON
3GO
4SET QUOTED_IDENTIFIER ON
5GO
6create PROCEDURE [dbo].[csp_infotiles_scriptfield_addtable]
7 @@tablename NVARCHAR(64) ,
8 @@localname_singular NVARCHAR(MAX) ,
9 @@localname_plural NVARCHAR(MAX) ,
10 @@failexistingtable INT = 1 ,
11 @@messagetext NVARCHAR(512) = N'' OUTPUT ,
12 @@idtable INT = NULL OUTPUT ,
13 @@iddescriptiveexpression INT = NULL OUTPUT
14AS
15 BEGIN
16
17
18 DECLARE @return_value INT
19 DECLARE @idstringlocalname INT
20 DECLARE @idstring INT
21
22 DECLARE @sql NVARCHAR(300)
23 DECLARE @currentPosition INT
24 DECLARE @nextOccurance INT
25 DECLARE @currentString NVARCHAR(256)
26 DECLARE @currentLanguage NVARCHAR(8)
27 DECLARE @currentLocalize NVARCHAR(256)
28 DECLARE @isFirstLocalize BIT
29 DECLARE @count INT
30
31 SET @return_value = 0 -- DEFAULT OK
32 SET @idstringlocalname = NULL
33 SET @idstring = NULL
34 SET @@idtable = NULL
35
36 SET @@iddescriptiveexpression = NULL
37 SET @sql = N''
38 SET @isFirstLocalize = 1
39 SET @@messagetext = N''
40
41 BEGIN TRY
42
43 --Check if table already exists
44 EXEC lsp_gettable @@name = @@tablename, @@count = @count OUTPUT
45
46 IF @count > 0 --Tablename already exists
47 BEGIN
48 SET @@idtable = -1
49 SET @@iddescriptiveexpression = -1
50 SET @@messagetext = N'Table ' + QUOTENAME(@@tablename)
51 + N' already exists.'
52 IF @@failexistingtable = 1
53 SET @return_value = -1
54 END
55 ELSE
56 BEGIN
57 EXEC @return_value = [dbo].[lsp_addtable] @@name = @@tablename,
58 @@idtable = @@idtable OUTPUT,
59 @@localname = @idstringlocalname OUTPUT,
60 @@descriptive = @@iddescriptiveexpression OUTPUT,
61 @@user = 1
62
63
64 --If return value is not 0, something went wrong and the table wasn't created
65 IF @return_value <> 0
66 BEGIN
67 SET @@idtable = -1
68 SET @@iddescriptiveexpression = -1
69 SET @@messagetext = N'Table '
70 + QUOTENAME(@@tablename)
71 + N' couldn''t be created'
72 END
73 ELSE
74 BEGIN
75
76 --Set localnames singular
77 IF CHARINDEX(':', @@localname_singular, 0) > 0
78 BEGIN
79 --Make sure @@localname_singular ends with ; in order to avoid infinite loop
80 IF RIGHT(@@localname_singular, 1) <> N';'
81 BEGIN
82 SET @@localname_singular = @@localname_singular
83 + N';'
84 END
85
86 --Make sure @@localname dont start with ;
87 WHILE LEFT(@@localname_singular, 1) = N';'
88 BEGIN
89 SET @@localname_singular = SUBSTRING(@@localname_singular,
90 2,
91 LEN(@@localname_singular))
92 END
93
94 SET @currentPosition = 0
95 --Loop through localnames
96 WHILE @currentPosition <= LEN(@@localname_singular)
97 AND @return_value = 0
98 BEGIN
99 SET @nextOccurance = CHARINDEX(';',
100 @@localname_singular,
101 @currentPosition)
102 IF @nextOccurance <> 0
103 BEGIN
104 SET @sql = N''
105 SET @currentString = SUBSTRING(@@localname_singular,
106 @currentPosition,
107 @nextOccurance
108 - @currentPosition)
109 SET @currentLanguage = SUBSTRING(@currentString,
110 0,
111 CHARINDEX(':',
112 @currentString))
113 SET @currentLocalize = SUBSTRING(@currentString,
114 CHARINDEX(':',
115 @currentString)
116 + 1,
117 LEN(@currentString)
118 - CHARINDEX(':',
119 @currentString))
120
121 --Set local names for field
122 SET @sql = N'UPDATE [string]
123 SET [' + @currentLanguage + N'] = '''
124 + @currentLocalize
125 + N''''
126 + N' WHERE [idstring] = '
127 + CONVERT(NVARCHAR(12), @idstringlocalname)
128 EXEC sp_executesql @sql
129
130 SET @currentPosition = @nextOccurance
131 + 1
132 END
133 END
134 END
135 --End localnames singular
136
137 --Set localnames plural
138 IF CHARINDEX(':', @@localname_plural, 0) > 0
139 BEGIN
140 --Make sure @@localname_plural ends with ; in order to avoid infinite loop
141 SET @currentPosition = 0
142 IF RIGHT(@@localname_plural, 1) <> N';'
143 BEGIN
144 SET @@localname_plural = @@localname_plural
145 + N';'
146 END
147
148 --Make sure @@localname dont start with ;
149 WHILE LEFT(@@localname_plural, 1) = N';'
150 BEGIN
151 SET @@localname_plural = SUBSTRING(@@localname_plural,
152 2,
153 LEN(@@localname_plural))
154 END
155
156 SET @currentPosition = 0
157 --Loop through localnames
158 WHILE @currentPosition <= LEN(@@localname_plural)
159 AND @return_value = 0
160 BEGIN
161 SET @nextOccurance = CHARINDEX(';',
162 @@localname_plural,
163 @currentPosition)
164 IF @nextOccurance <> 0
165 BEGIN
166 SET @currentString = SUBSTRING(@@localname_plural,
167 @currentPosition,
168 @nextOccurance
169 - @currentPosition)
170 SET @currentLanguage = SUBSTRING(@currentString,
171 0,
172 CHARINDEX(':',
173 @currentString))
174 SET @currentLocalize = SUBSTRING(@currentString,
175 CHARINDEX(':',
176 @currentString)
177 + 1,
178 LEN(@currentString)
179 - CHARINDEX(':',
180 @currentString))
181
182 IF @isFirstLocalize = 1
183 BEGIN
184 EXEC @return_value = [dbo].[lsp_addstring] @@idcategory = 17,
185 @@string = @currentLocalize,
186 @@lang = @currentLanguage,
187 @@idstring = @idstring OUTPUT
188 SET @isFirstLocalize = 0
189 END
190 ELSE
191 BEGIN
192 EXEC @return_value = dbo.lsp_setstring @@idstring = @idstring,
193 @@lang = @currentLanguage,
194 @@string = @currentLocalize
195 END
196
197 SET @currentPosition = @nextOccurance
198 + 1
199 END
200 END
201 END
202
203 EXEC @return_value = lsp_addattributedata @@owner = N'table',
204 @@idrecord = @@idtable, @@idrecord2 = NULL,
205 @@name = N'localnameplural',
206 @@value = @idstring
207 --End localnames plural
208
209
210 --If return value is not 0, something went wrong while setting table attributes
211 IF @return_value <> 0
212 BEGIN
213 SET @@messagetext = N'Something went wrong while setting localnames for table '
214 + QUOTENAME(@@tablename)
215 + N'. Please check that table properties are correct.'
216 END
217 IF @return_value = 0
218 BEGIN
219 SET @@messagetext = N'ADDED TABLE '
220 + QUOTENAME(@@tablename)
221 END
222 END
223 END
224
225
226
227 END TRY
228 BEGIN CATCH
229 SET @return_value = -99
230 SET @@messagetext = LEFT(ERROR_MESSAGE(), 512)
231 END CATCH
232
233 RETURN @return_value
234 END
235
236
237 GO
238
239 CREATE PROCEDURE [dbo].[csp_infotiles_scriptfield_addfield]
240 @@tablename NVARCHAR(64) ,
241 @@fieldname NVARCHAR(64) ,
242 @@type NVARCHAR(64) ,
243 @@localname NVARCHAR(MAX) -- N'lang:text;lang2:text2'
244 ,
245 @@separator NVARCHAR(MAX) = N'' -- N'lang:text;lang2:text2'
246 ,
247 @@defaultvalue NVARCHAR(64) = NULL ,
248 @@limedefaultvalue NVARCHAR(64) = NULL ,
249 @@limereadonly INT = 0 ,
250 @@invisible INT = 0 ,
251 @@required INT = NULL ,
252 @@width INT = NULL ,
253 @@height INT = NULL ,
254 @@length INT = NULL ,
255 @@isnullable INT = 0 ,
256 @@idcategoryshared NVARCHAR(32) = NULL ,
257 @@failexistingfield INT = 1 ,
258 @@messagetext NVARCHAR(512) = N'' OUTPUT ,
259 @@idfield INT = NULL OUTPUT,
260 @@addCategory INT = 0
261AS
262 BEGIN
263
264
265
266 DECLARE @return_value INT
267 DECLARE @idstringlocalname INT
268 DECLARE @idcategory INT
269 DECLARE @idstring INT
270 DECLARE @idfieldtype INT
271 DECLARE @count INT
272 DECLARE @sql NVARCHAR(300)
273 DECLARE @currentPosition INT
274 DECLARE @nextOccurance INT
275 DECLARE @currentString NVARCHAR(256)
276 DECLARE @currentLanguage NVARCHAR(8)
277 DECLARE @currentLocalize NVARCHAR(256)
278
279 SET @return_value = 0 -- DEFAULT OK
280 SET @@idfield = NULL
281 SET @idstringlocalname = NULL
282 SET @idcategory = NULL
283 SET @idstring = NULL
284 SET @@messagetext = N''
285 SET @sql = N''
286
287 BEGIN TRY
288
289 --Check if field already exists
290 EXEC lsp_getfield @@table = @@tablename, @@name = @@fieldname,
291 @@count = @count OUTPUT
292
293 IF @count > 0 --Fieldname already exists
294 BEGIN
295 SET @@messagetext = N'Field ' + QUOTENAME(@@tablename)
296 + N'.' + QUOTENAME(@@fieldname) + N' already exists.'
297 IF @@failexistingfield = 1
298 SET @return_value = -1
299 END
300 ELSE --Field doesn't exist
301 BEGIN
302 --Check if fieldtype exists
303 IF ( SELECT COUNT(*)
304 FROM fieldtype
305 WHERE name = @@type
306 AND active = 1
307 AND creatable = 1
308 ) <> 1
309 BEGIN
310 SET @@messagetext = N'''' + @@type
311 + N''' is not a valid fieldtype. Field '
312 + QUOTENAME(@@tablename) + N'.'
313 + QUOTENAME(@@fieldname)
314 + ' couldn''t be created'
315 SET @return_value = -2
316 END
317 ELSE
318 BEGIN
319 -- Get field type
320 SELECT @idfieldtype = idfieldtype
321 FROM fieldtype
322 WHERE name = @@type
323 AND active = 1
324 AND creatable = 1
325
326 EXEC @return_value = [dbo].[lsp_addfield] @@table = @@tablename,
327 @@name = @@fieldname,
328 @@fieldtype = @idfieldtype,
329 @@length = @@length,
330 @@isnullable = @@isnullable,
331 @@defaultvalue = @@defaultvalue OUTPUT,
332 @@idfield = @@idfield OUTPUT,
333 @@localname = @idstringlocalname OUTPUT,
334 @@idcategory = @idcategory OUTPUT
335
336 IF @@addCategory = 1 AND @idcategory = -1
337 BEGIN
338 EXECUTE @return_value = lsp_setfieldattributevalue @@idfield = @@idfield,
339 @@name = N'idcategory',
340 @@valueint = @idcategory OUTPUT--,
341 -- @@transactionid = @@transactionid,
342 -- @@user = @@user
343 END
344
345
346 --If return value is not 0, something went wrong and the field wasn't created
347 IF @return_value <> 0
348 BEGIN
349 SET @@messagetext = N'Field '
350 + QUOTENAME(@@tablename) + N'.'
351 + QUOTENAME(@@fieldname)
352 + N' couldn''t be created'
353 END
354 ELSE
355 BEGIN
356 SET @return_value = 0
357
358 IF CHARINDEX(':', @@localname, 0) > 0
359 BEGIN
360
361 --Make sure @@localname ends with ; in order to avoid infinite loop
362 IF RIGHT(@@localname, 1) <> N';'
363 BEGIN
364 SET @@localname = @@localname
365 + N';'
366 END
367 --Make sure @@localname dont start with ;
368 WHILE LEFT(@@localname, 1) = N';'
369 BEGIN
370 SET @@localname = SUBSTRING(@@localname,
371 2,
372 LEN(@@localname))
373 END
374
375
376 SET @currentPosition = 0
377 --Loop through localnames
378 WHILE @currentPosition <= LEN(@@localname)
379 AND @return_value = 0
380 BEGIN
381 SET @nextOccurance = CHARINDEX(';',
382 @@localname,
383 @currentPosition)
384 IF @nextOccurance <> 0
385 BEGIN
386 SET @sql = N''
387 SET @currentString = SUBSTRING(@@localname,
388 @currentPosition,
389 @nextOccurance
390 - @currentPosition)
391 SET @currentLanguage = SUBSTRING(@currentString,
392 0,
393 CHARINDEX(':',
394 @currentString))
395 SET @currentLocalize = SUBSTRING(@currentString,
396 CHARINDEX(':',
397 @currentString)
398 + 1,
399 LEN(@currentString)
400 - CHARINDEX(':',
401 @currentString))
402
403 --Set local names for field
404 SET @sql = N'UPDATE [string]
405 SET ' + QUOTENAME(@currentLanguage) + N' = '''
406 + @currentLocalize
407 + N''''
408 + N' WHERE [idstring] = '
409 + CONVERT(NVARCHAR(12), @idstringlocalname)
410 EXEC sp_executesql @sql
411
412 SET @currentPosition = @nextOccurance
413 + 1
414 END
415 END
416 END
417
418 -- SHARED IDCATEGORY
419
420 IF @return_value = 0
421 AND @@idcategoryshared IS NOT NULL
422 EXEC [dbo].[lsp_setattributevalue] @@owner = 'field',
423 @@idrecord = @@idfield,
424 @@name = 'idcategory',
425 @@value = @@idcategoryshared
426
427 --Set limereadonly attribute
428 IF @return_value = 0
429 EXEC @return_value = [dbo].[lsp_setfieldattributevalue] @@idfield = @@idfield,
430 @@name = N'limereadonly',
431 @@valueint = @@limereadonly
432
433 --Set Default value (interpreted by LIME)
434 IF @return_value = 0
435 AND @@limedefaultvalue IS NOT NULL
436 BEGIN
437 EXEC @return_value = [dbo].[lsp_setattributevalue] @@owner = N'field',
438 @@idrecord = @@idfield,
439 @@name = N'limedefaultvalue',
440 @@value = @@limedefaultvalue -- Default Value (interpreted by LIME Pro)
441 END
442
443 --Set invisible/visible
444 IF @return_value = 0
445 EXEC @return_value = [dbo].[lsp_setattributevalue] @@owner = N'field',
446 @@idrecord = @@idfield,
447 @@name = N'invisible',
448 @@valueint = @@invisible
449
450 --Set required attribute
451 IF @return_value = 0
452 AND @@required IS NOT NULL
453 BEGIN
454 EXEC @return_value = [dbo].[lsp_setfieldattributevalue] @@idfield = @@idfield,
455 @@name = N'required',
456 @@valueint = @@required
457 END
458
459 --Set width
460 IF @return_value = 0
461 AND @@width IS NOT NULL
462 BEGIN
463 EXEC @return_value = [dbo].[lsp_setfieldattributevalue] @@idfield = @@idfield,
464 @@name = N'width',
465 @@valueint = @@width
466 END
467
468 --Set height
469 IF @return_value = 0
470 AND @@height IS NOT NULL
471 BEGIN
472 EXEC @return_value = [dbo].[lsp_setfieldattributevalue] @@idfield = @@idfield,
473 @@name = N'height',
474 @@valueint = @@height
475 END
476
477
478 --Create separator
479 IF @return_value = 0
480 AND @@separator <> N''
481 AND CHARINDEX(':', @@separator, 0) > 0
482 BEGIN
483 SET @idstring = -1
484 EXEC @return_value = [dbo].[lsp_setattributevalue] @@owner = N'field',
485 @@idrecord = @@idfield,
486 @@name = 'separator',
487 @@value = 1
488 EXEC @return_value = [dbo].[lsp_setattributevalue] @@owner = N'field',
489 @@idrecord = @@idfield,
490 @@name = N'separatorlocalname',
491 @@value = @idstring OUTPUT
492
493 --Make sure @@@@separator ends with ; in order to avoid infinite loop
494 IF RIGHT(@@separator, 1) <> N';'
495 BEGIN
496 SET @@separator = @@separator
497 + N';'
498 END
499
500 --Make sure @@@@separator dont start with ;
501 WHILE LEFT(@@separator, 1) = N';'
502 BEGIN
503 SET @@separator = SUBSTRING(@@separator,
504 2,
505 LEN(@@separator))
506 END
507
508 SET @currentPosition = 0
509
510 --Loop through localnames
511 WHILE @currentPosition <= LEN(@@separator)
512 AND @return_value = 0
513 BEGIN
514 SET @nextOccurance = CHARINDEX(';',
515 @@separator,
516 @currentPosition)
517 IF @nextOccurance <> 0
518 BEGIN
519 SET @currentString = SUBSTRING(@@separator,
520 @currentPosition,
521 @nextOccurance
522 - @currentPosition)
523 SET @currentLanguage = SUBSTRING(@currentString,
524 0,
525 CHARINDEX(':',
526 @currentString))
527 SET @currentLocalize = SUBSTRING(@currentString,
528 CHARINDEX(':',
529 @currentString)
530 + 1,
531 LEN(@currentString)
532 - CHARINDEX(':',
533 @currentString))
534 EXEC @return_value = [dbo].[lsp_setattributevalue] @@owner = N'string',
535 @@idrecord = @idstring,
536 @@name = @currentLanguage,
537 @@value = @currentLocalize
538 SET @currentPosition = @nextOccurance
539 + 1
540 END
541 END
542 END
543 --End of creating separator
544
545
546
547 --If return value is not 0, something went wrong while setting field attributes
548 IF @return_value <> 0
549 BEGIN
550 SET @@messagetext = N'Something went wrong while setting attributes for field '
551 + QUOTENAME(@@tablename)
552 + N'.' + QUOTENAME(@@fieldname)
553 + N'. Please check that field properties are correct.'
554 END
555
556 IF @return_value = 0
557 BEGIN
558 SET @@messagetext = N'ADDING FIELD '
559 + QUOTENAME(@@tablename)
560 + N'.' + QUOTENAME(@@fieldname)
561 END
562
563 END
564 END
565 END
566 END TRY
567 BEGIN CATCH
568 SET @return_value = -99
569 SET @@messagetext = LEFT(ERROR_MESSAGE(), 512)
570 END CATCH
571
572 RETURN @return_value
573 END
574
575 GO
576
577create PROCEDURE [dbo].[csp_infotiles_scriptfield_addrelation_onetomany]
578 @@field_table NVARCHAR(128) ,
579 @@field_name NVARCHAR(128) ,
580 @@field_localname NVARCHAR(MAX) ,
581 @@tab_table NVARCHAR(128) ,
582 @@tab_name NVARCHAR(128) ,
583 @@tab_localname NVARCHAR(MAX) ,
584 @@failexistingfield INT = 1 ,
585 @@messagetext NVARCHAR(512) = N'' OUTPUT ,
586 @@idfieldfield INT = NULL OUTPUT ,
587 @@idfieldtab INT = NULL OUTPUT
588AS
589 BEGIN
590 SET NOCOUNT ON;
591
592 BEGIN TRY
593
594
595 /**********************
596 DECLARE vars
597 **********************/
598 DECLARE @return_value INT
599 --DECLARE @defaultvalue NVARCHAR(128)
600 --DECLARE @idfield INT
601 DECLARE @localname INT
602 DECLARE @idcategory INT
603 --DECLARE @idstring INT
604 --DECLARE @sqlcommand NVARCHAR(2048)
605
606 -- Special relation variables.
607 DECLARE @idtable1 INT --Table with field
608 DECLARE @idtable2 INT --Table with tab
609 DECLARE @idrelation INT
610 DECLARE @count_field INT
611 DECLARE @count_tab INT
612 DECLARE @sql NVARCHAR(300)
613 DECLARE @currentPosition INT
614 DECLARE @nextOccurance INT
615 DECLARE @currentString NVARCHAR(256)
616 DECLARE @currentLanguage NVARCHAR(8)
617 DECLARE @currentLocalize NVARCHAR(256)
618
619 -- init vars
620 SET @return_value = 0 -- DEFAULT OK
621 SET @@messagetext = N''
622 SET @sql = N''
623 SET @@idfieldfield = NULL
624 SET @@idfieldtab = NULL
625
626
627
628 -- CHECK IF ONE FIELD EXIST
629 SET @count_field = NULL
630 SET @count_tab = NULL
631 EXEC lsp_getfield @@table = @@field_table, @@name = @@field_name,
632 @@count = @count_field OUTPUT
633
634 EXEC lsp_getfield @@table = @@tab_table, @@name = @@tab_name,
635 @@count = @count_tab OUTPUT
636
637
638 IF @count_field + @count_tab > 0 --Fieldname already exists
639 BEGIN
640
641 IF @count_field > 0
642 BEGIN
643 SET @@messagetext = N'Field '
644 + QUOTENAME(@@field_table) + N'.'
645 + QUOTENAME(@@field_name)
646 + N' already exists.'
647 END
648 IF @count_tab > 0
649 BEGIN
650 SET @@messagetext = @@messagetext + CHAR(10)
651 + N'Field ' + QUOTENAME(@@tab_table) + N'.'
652 + QUOTENAME(@@tab_name) + N' already exists.'
653 END
654 IF @@failexistingfield = 1
655 SET @return_value = -1
656 END
657 ELSE
658 BEGIN
659 IF @return_value = 0
660 BEGIN
661 -- ADD FIELD 1 (FIELD)
662 SET @localname = NULL
663 SET @idcategory = NULL
664
665 -- create field
666 EXEC @return_value = [dbo].[lsp_addfield] @@table = @@field_table,
667 @@name = @@field_name, @@fieldtype = 16, --realationfield
668 @@idfield = @@idfieldField OUTPUT,
669 @@localname = @localname OUTPUT,
670 @@idcategory = @idcategory OUTPUT
671
672 IF @return_value <> 0
673 BEGIN
674 SET @@messagetext = N'Field '
675 + QUOTENAME(@@field_table) + N'.'
676 + QUOTENAME(@@field_name)
677 + N' couldn''t be created'
678 END
679 ELSE
680 BEGIN
681
682 -- fix localisation
683
684 IF CHARINDEX(':', @@field_localname, 0) > 0
685 BEGIN
686
687 --Make sure @@localname ends with ; in order to avoid infinite loop
688 IF RIGHT(@@field_localname, 1) <> N';'
689 BEGIN
690 SET @@field_localname = @@field_localname
691 + N';'
692 END
693 --Make sure @@localname dont start with ;
694 WHILE LEFT(@@field_localname, 1) = N';'
695 BEGIN
696 SET @@field_localname = SUBSTRING(@@field_localname,
697 2,
698 LEN(@@field_localname))
699 END
700
701
702 SET @currentPosition = 0
703 --Loop through localnames
704 WHILE @currentPosition <= LEN(@@field_localname)
705 AND @return_value = 0
706 BEGIN
707 SET @nextOccurance = CHARINDEX(';',
708 @@field_localname,
709 @currentPosition)
710 IF @nextOccurance <> 0
711 BEGIN
712 SET @sql = N''
713 SET @currentString = SUBSTRING(@@field_localname,
714 @currentPosition,
715 @nextOccurance
716 - @currentPosition)
717 SET @currentLanguage = SUBSTRING(@currentString,
718 0,
719 CHARINDEX(':',
720 @currentString))
721 SET @currentLocalize = SUBSTRING(@currentString,
722 CHARINDEX(':',
723 @currentString)
724 + 1,
725 LEN(@currentString)
726 - CHARINDEX(':',
727 @currentString))
728
729 --Set local names for field
730 SET @sql = N'UPDATE [string]
731 SET ' + QUOTENAME(@currentLanguage) + N' = '''
732 + @currentLocalize
733 + N''''
734 + N' WHERE [idstring] = '
735 + CONVERT(NVARCHAR(12), @localname)
736 EXEC sp_executesql @sql
737
738 SET @currentPosition = @nextOccurance
739 + 1
740 END
741 END
742 END
743
744 END
745
746 --set realtioncount
747 EXEC @return_value = [dbo].[lsp_setattributevalue] @@owner = 'field',
748 @@idrecord = @@idfieldfield,
749 @@name = 'relationmincount', @@value = 0
750
751 IF @return_value <> 0
752 BEGIN
753 SET @@messagetext = N'Something went wrong while setting attributes for field '
754 + QUOTENAME(@@field_table) + N'.'
755 + QUOTENAME(@@field_name)
756 + N'. Please check that field properties are correct.'
757 END
758
759
760
761 END
762
763 IF @return_value = 0
764 BEGIN
765 -- ADD FIELD 2 (TAB)
766 SET @localname = NULL
767 SET @idcategory = NULL
768
769 -- create field
770 EXEC @return_value = [dbo].[lsp_addfield] @@table = @@tab_table,
771 @@name = @@tab_name, @@fieldtype = 16, --realationfield
772 @@idfield = @@idfieldTab OUTPUT,
773 @@localname = @localname OUTPUT,
774 @@idcategory = @idcategory OUTPUT
775
776 IF @return_value <> 0
777 BEGIN
778 SET @@messagetext = N'Field '
779 + QUOTENAME(@@tab_table) + N'.'
780 + QUOTENAME(@@tab_name)
781 + N' couldn''t be created'
782 END
783 ELSE
784 BEGIN
785
786 -- fix localisation
787
788 IF CHARINDEX(':', @@tab_localname, 0) > 0
789 BEGIN
790
791 --Make sure @@localname ends with ; in order to avoid infinite loop
792 IF RIGHT(@@tab_localname, 1) <> N';'
793 BEGIN
794 SET @@tab_localname = @@tab_localname
795 + N';'
796 END
797 --Make sure @@localname dont start with ;
798 WHILE LEFT(@@tab_localname, 1) = N';'
799 BEGIN
800 SET @@tab_localname = SUBSTRING(@@tab_localname,
801 2,
802 LEN(@@tab_localname))
803 END
804
805
806 SET @currentPosition = 0
807 --Loop through localnames
808 WHILE @currentPosition <= LEN(@@tab_localname)
809 AND @return_value = 0
810 BEGIN
811 SET @nextOccurance = CHARINDEX(';',
812 @@tab_localname,
813 @currentPosition)
814 IF @nextOccurance <> 0
815 BEGIN
816 SET @sql = N''
817 SET @currentString = SUBSTRING(@@tab_localname,
818 @currentPosition,
819 @nextOccurance
820 - @currentPosition)
821 SET @currentLanguage = SUBSTRING(@currentString,
822 0,
823 CHARINDEX(':',
824 @currentString))
825 SET @currentLocalize = SUBSTRING(@currentString,
826 CHARINDEX(':',
827 @currentString)
828 + 1,
829 LEN(@currentString)
830 - CHARINDEX(':',
831 @currentString))
832
833 --Set local names for field
834 SET @sql = N'UPDATE [string]
835 SET ' + QUOTENAME(@currentLanguage) + N' = '''
836 + @currentLocalize
837 + N''''
838 + N' WHERE [idstring] = '
839 + CONVERT(NVARCHAR(12), @localname)
840 EXEC sp_executesql @sql
841
842 SET @currentPosition = @nextOccurance
843 + 1
844 END
845 END
846 END
847
848 END
849
850 --set realtioncount
851 EXEC @return_value = [dbo].[lsp_setattributevalue] @@owner = 'field',
852 @@idrecord = @@idfieldtab,
853 @@name = 'relationmincount', @@value = 0
854
855 IF @return_value = 0
856 BEGIN
857 EXEC @return_value = [dbo].[lsp_setattributevalue] @@owner = 'field',
858 @@idrecord = @@idfieldtab,
859 @@name = 'relationmaxcount',
860 @@value = 1
861 END
862 IF @return_value <> 0
863 BEGIN
864 SET @@messagetext = N'Something went wrong while setting attributes for field '
865 + QUOTENAME(@@tab_table) + N'.'
866 + QUOTENAME(@@tab_name)
867 + N'. Please check that field properties are correct.'
868 END
869
870
871
872 END
873
874 IF @return_value = 0
875 BEGIN
876 -- ADD RELATION
877 SET @idtable1 = NULL
878 SET @idtable2 = NULL
879 SET @idrelation = NULL
880
881 EXEC lsp_gettable @@name = @@field_table,
882 @@idtable = @idtable1 OUTPUT
883 EXEC lsp_gettable @@name = @@tab_table,
884 @@idtable = @idtable2 OUTPUT
885
886 EXEC @return_value = lsp_addrelation @@idfield1 = @@idfieldfield,
887 @@idtable1 = @idtable1,
888 @@idfield2 = @@idfieldtab,
889 @@idrelation = @idrelation OUTPUT
890
891
892 IF @return_value <> 0
893 BEGIN
894 SET @@messagetext = N'Something went wrong while adding relation between field '
895 + QUOTENAME(@@field_table) + N'.'
896 + QUOTENAME(@@field_name)
897 + N' and field '
898 + QUOTENAME(@@tab_table) + N'.'
899 + QUOTENAME(@@tab_name)
900 + N'. Please check that field properties are correct.'
901 END
902 END
903
904 IF @return_value = 0
905 BEGIN
906 SET @@messagetext = N'ADDED ONE TO MANY RELATION BETWEEN FIELD '
907 + QUOTENAME(@@field_table) + N'.'
908 + QUOTENAME(@@field_name) + N' AND FIELD '
909 + QUOTENAME(@@tab_table) + N'.'
910 + QUOTENAME(@@tab_name)
911
912 END
913
914 END
915
916
917 END TRY
918 BEGIN CATCH
919 SET @return_value = -99
920 SET @@messagetext = LEFT(ERROR_MESSAGE(), 512)
921 END CATCH
922 RETURN @return_value
923 END
924
925
926 GO
927
928create PROCEDURE [dbo].[csp_infotiles_scriptfield_validate_optiontext]
929 (
930 @@localname NVARCHAR(MAX) ,
931 @@idcategory INT,
932 @@noofmatches INT = 0 OUTPUT
933 )
934
935AS
936 BEGIN
937
938 DECLARE @returnvalue INT = 0
939 DECLARE @sql NVARCHAR(300) = N''
940 DECLARE @currentPosition INT
941 DECLARE @nextOccurance INT
942 DECLARE @currentString NVARCHAR(256)
943 DECLARE @currentLanguage NVARCHAR(8)
944 DECLARE @currentLocalize NVARCHAR(256)
945 IF CHARINDEX(':', @@localname, 0) > 0
946 BEGIN
947
948 --Make sure @@localname ends with ; in order to avoid infinite loop
949 IF RIGHT(@@localname, 1) <> N';'
950 BEGIN
951 SET @@localname = @@localname + N';'
952 END
953 --Make sure @@localname dont start with ;
954 WHILE LEFT(@@localname, 1) = N';'
955 BEGIN
956 SET @@localname = SUBSTRING(@@localname, 2,
957 LEN(@@localname))
958 END
959
960
961 SET @currentPosition = 0
962 --Loop through localnames
963 WHILE @currentPosition <= LEN(@@localname)
964 BEGIN
965 SET @nextOccurance = CHARINDEX(';', @@localname,
966 @currentPosition)
967 IF @nextOccurance <> 0
968 BEGIN
969
970 SET @currentString = SUBSTRING(@@localname,
971 @currentPosition,
972 @nextOccurance
973 - @currentPosition)
974 SET @currentLanguage = SUBSTRING(@currentString,
975 0,
976 CHARINDEX(':',
977 @currentString))
978 SET @currentLocalize = SUBSTRING(@currentString,
979 CHARINDEX(':',
980 @currentString)
981 + 1,
982 LEN(@currentString)
983 - CHARINDEX(':',
984 @currentString))
985
986 --Set local names for field
987 SET @sql = @sql
988 + CASE WHEN @sql != N'' THEN N' OR '
989 ELSE N''
990 END + QUOTENAME(@currentLanguage)
991 + N' = ''' + @currentLocalize + N''''
992
993
994
995
996 SET @currentPosition = @nextOccurance + 1
997 END
998 END
999 END
1000
1001 IF @sql != N''
1002 BEGIN
1003
1004
1005 DECLARE @sqlfull NVARCHAR(MAX)
1006 DECLARE @idcategory INT = 4401
1007
1008 SET @sqlfull = N'SELECT @count_sql = COUNT(*) FROM [dbo].[string] WHERE [idcategory] = @idcategory_sql AND ('
1009 + @sql + N')'
1010
1011
1012
1013 EXEC sp_executesql @sqlfull,
1014 N'@count_sql INT OUTPUT, @idcategory_sql INT',
1015 @count_sql = @@noofmatches OUTPUT,
1016 @idcategory_sql = @@idcategory
1017 END
1018
1019 END
1020
1021
1022 GO
1023
1024 CREATE PROCEDURE [dbo].[csp_infotiles_scriptfield_addoption]
1025 @@tablename NVARCHAR(64) ,
1026 @@fieldname NVARCHAR(64) ,
1027 @@localname NVARCHAR(MAX) , -- N'lang:text;lang2:text2'
1028 @@key NVARCHAR(256) = N'' ,
1029 @@failexistingoption INT = 1 ,
1030 @@idstring INT = NULL OUTPUT ,
1031 @@messagetext NVARCHAR(512) = N'' OUTPUT,
1032 @@default INT = 0,
1033 @@color INT = NULL
1034AS
1035 BEGIN
1036 SET NOCOUNT ON;
1037
1038 BEGIN TRY
1039
1040 /**********************
1041 DECLARE vars
1042 **********************/
1043 DECLARE @return_value INT
1044 DECLARE @idfield INT = NULL
1045 DECLARE @idcategory INT = NULL
1046 DECLARE @currentPosition INT
1047 DECLARE @nextOccurance INT
1048 DECLARE @currentString NVARCHAR(256)
1049 DECLARE @currentLanguage NVARCHAR(8)
1050 DECLARE @currentLocalize NVARCHAR(256)
1051 DECLARE @isFirstLocalize BIT
1052 DECLARE @keyAlreadyExist INT
1053 DECLARE @optionAlreadyExists INT
1054 DECLARE @defaultidstring INT
1055
1056 SET @isFirstLocalize = 1
1057 SET @@messagetext = N''
1058 SET @return_value = 0
1059 SET @keyAlreadyExist = 0
1060 SET @optionAlreadyExists = 0
1061 /**********************
1062 get field
1063 **********************/
1064 EXEC [dbo].[lsp_getfield] @@idfield = @idfield OUTPUT,
1065 @@name = @@fieldname, @@table = @@tablename
1066
1067 SELECT @defaultidstring = fv.defaultvalue
1068 FROM fieldview fv
1069 WHERE fv.idfield = @idfield
1070
1071 IF @idfield IS NULL
1072 BEGIN
1073 SET @@messagetext = N'MISSING FIELD '
1074 + QUOTENAME(@@tablename) + N'.'
1075 + QUOTENAME(@@fieldname) + N' FAILED TO ADD OPTION '
1076 + @@localname
1077 SET @return_value = -1
1078 END
1079 ELSE
1080 BEGIN
1081 -- GET idcategory for field
1082 EXEC [dbo].[lsp_getattributedata] @@owner = N'field',
1083 @@idrecord = @idfield, @@name = N'idcategory',
1084 @@value = @idcategory OUTPUT, @@count = 1
1085
1086 IF ISNULL(@idcategory, 0) = 0
1087 BEGIN
1088 SET @@messagetext = N'MISSING CATEGORY FOR FIELD '
1089 + QUOTENAME(@@tablename) + N'.'
1090 + QUOTENAME(@@fieldname)
1091 + N' FAILED TO ADD OPTION ' + @@localname
1092 SET @return_value = -2
1093 END
1094 END
1095
1096 IF @return_value = 0
1097 AND LEN(ISNULL(@@key, N'')) > 0
1098 BEGIN
1099 -- MAKE SURE [key] IS UNIQUE
1100 IF EXISTS ( SELECT [idstring]
1101 FROM [dbo].[string]
1102 WHERE [idcategory] = @idcategory
1103 AND [key] = @@key )
1104 BEGIN
1105 SET @@messagetext = N'OPTION WITH KEY ''' + @@key
1106 + N''' ALREADY EXISTS FOR FIELD '
1107 + QUOTENAME(@@tablename) + N'.'
1108 + QUOTENAME(@@fieldname)
1109 + N' FAILED TO ADD OPTION ' + @@localname
1110
1111 SET @keyAlreadyExist = 1
1112
1113 IF @@failexistingoption = 1
1114 SET @return_value = -3
1115 END
1116 END
1117
1118 IF @return_value = 0
1119 BEGIN
1120 -- MAKE SURE NOT DUPLICATE IN ANY LANGUAGE
1121 DECLARE @noOfHits INT
1122 EXECUTE [dbo].[csp_infotiles_scriptfield_validate_optiontext] @@localname = @@localname,
1123 @@idcategory = @idcategory,
1124 @@noofmatches = @noOfHits OUTPUT
1125
1126 IF @noOfHits > 0
1127 BEGIN
1128 SET @@messagetext = N'OPTION ''' + @@localname
1129 + N''' ALREADY EXISTS FOR FIELD '
1130 + QUOTENAME(@@tablename) + N'.'
1131 + QUOTENAME(@@fieldname)
1132 + N' FAILED TO ADD OPTION'
1133
1134 SET @optionAlreadyExists = 1
1135
1136 IF @@failexistingoption = 1
1137 SET @return_value = -4
1138 END
1139 END
1140
1141 -- VALIDATIONS OK --> Add Option
1142 IF ( @keyAlreadyExist + @optionAlreadyExists ) = 0
1143 BEGIN
1144 IF @return_value = 0
1145 BEGIN
1146 SET @@idstring = NULL
1147
1148 --Set localnames
1149 IF CHARINDEX(':', @@localname, 0) > 0
1150 BEGIN
1151 --Make sure @@localname_plural ends with ; in order to avoid infinite loop
1152 SET @currentPosition = 0
1153 IF RIGHT(@@localname, 1) <> N';'
1154 BEGIN
1155 SET @@localname = @@localname
1156 + N';'
1157 END
1158
1159 --Make sure @@localname dont start with ;
1160 WHILE LEFT(@@localname, 1) = N';'
1161 BEGIN
1162 SET @@localname = SUBSTRING(@@localname,
1163 2,
1164 LEN(@@localname))
1165 END
1166
1167 SET @currentPosition = 0
1168 --Loop through localnames
1169 WHILE @currentPosition <= LEN(@@localname)
1170 AND @return_value = 0
1171 BEGIN
1172 SET @nextOccurance = CHARINDEX(';',
1173 @@localname,
1174 @currentPosition)
1175 IF @nextOccurance <> 0
1176 BEGIN
1177 SET @currentString = SUBSTRING(@@localname,
1178 @currentPosition,
1179 @nextOccurance
1180 - @currentPosition)
1181 SET @currentLanguage = SUBSTRING(@currentString,
1182 0,
1183 CHARINDEX(':',
1184 @currentString))
1185 SET @currentLocalize = SUBSTRING(@currentString,
1186 CHARINDEX(':',
1187 @currentString)
1188 + 1,
1189 LEN(@currentString)
1190 - CHARINDEX(':',
1191 @currentString))
1192 IF @@default = 1
1193 BEGIN
1194 SET @@idstring = @defaultidstring
1195
1196 EXEC @return_value = dbo.lsp_setstring @@idstring = @@idstring,
1197 @@lang = @currentLanguage,
1198 @@string = @currentLocalize
1199 END
1200 ELSE
1201 BEGIN
1202 IF @isFirstLocalize = 1
1203 BEGIN
1204 EXEC @return_value = [dbo].[lsp_addstring] @@idcategory = @idcategory,
1205 @@string = @currentLocalize,
1206 @@lang = @currentLanguage,
1207 @@idstring = @@idstring OUTPUT
1208 SET @isFirstLocalize = 0
1209 END
1210 ELSE
1211 BEGIN
1212 EXEC @return_value = dbo.lsp_setstring @@idstring = @@idstring,
1213 @@lang = @currentLanguage,
1214 @@string = @currentLocalize
1215 END
1216
1217 --SET @currentPosition = @nextOccurance
1218 -- + 1
1219 END
1220
1221 SET @currentPosition = @nextOccurance + 1
1222 END
1223 END
1224 END
1225 IF @return_value != 0
1226 BEGIN
1227 SET @@messagetext = N'FAILED TO ADD OPTION '
1228 + @@localname + N' FOR FIELD '
1229 + QUOTENAME(@@tablename) + N'.'
1230 + QUOTENAME(@@fieldname)
1231 END
1232 ELSE
1233 BEGIN
1234 SET @@messagetext = N'ADDED OPTION '
1235 + @@localname + N' FOR FIELD '
1236 + QUOTENAME(@@tablename) + N'.'
1237 + QUOTENAME(@@fieldname)
1238 END
1239
1240 IF ISNULL(@@idstring, 0) > 0
1241 AND @return_value = 0
1242 BEGIN
1243 IF @@color IS NOT NULL
1244 BEGIN
1245 EXEC @return_value = [dbo].[lsp_addattributedata]
1246 @@owner= 'string',
1247 @@idrecord = @@idstring,
1248 @@idrecord2 = NULL,
1249 @@name = 'color' ,
1250 @@value = @@color
1251 END
1252 -- ADD key to string
1253 IF LEN(ISNULL(@@key, N'')) > 0
1254 BEGIN
1255 EXEC @return_value = dbo.lsp_setstring @@idstring = @@idstring,
1256 @@lang = N'key',
1257 @@string = @@key
1258 END
1259 IF @return_value != 0
1260 BEGIN
1261 SET @@messagetext = N'FAILED TO ADD KEY '
1262 + @@key + N' FOR OPTION '
1263 + @@localname + N' FOR FIELD '
1264 + QUOTENAME(@@tablename)
1265 + N'.' + QUOTENAME(@@fieldname)
1266 END
1267 END
1268 END
1269 END
1270 END TRY
1271 BEGIN CATCH
1272 SET @return_value = -99
1273 SET @@messagetext = LEFT(ERROR_MESSAGE(), 512)
1274 END CATCH
1275
1276 RETURN @return_value
1277 END