-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathTIROSchemaScript.sql
7201 lines (6276 loc) · 540 KB
/
TIROSchemaScript.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
USE [master]
GO
/****** Object: Database [ArbabTravelsERP] Script Date: 27-Jan-17 1:15:32 AM ******/
CREATE DATABASE [ArbabTravelsERP] ON PRIMARY
( NAME = N'ArbabTravelsERP', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ArbabTravelsERP.mdf' , SIZE = 6144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ArbabTravelsERP_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ArbabTravelsERP_log.ldf' , SIZE = 1280KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [ArbabTravelsERP] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ArbabTravelsERP].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [ArbabTravelsERP] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET ANSI_NULLS OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET ANSI_PADDING OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET ARITHABORT OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [ArbabTravelsERP] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [ArbabTravelsERP] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [ArbabTravelsERP] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET DISABLE_BROKER
GO
ALTER DATABASE [ArbabTravelsERP] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [ArbabTravelsERP] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [ArbabTravelsERP] SET RECOVERY SIMPLE
GO
ALTER DATABASE [ArbabTravelsERP] SET MULTI_USER
GO
ALTER DATABASE [ArbabTravelsERP] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [ArbabTravelsERP] SET DB_CHAINING OFF
GO
USE [ArbabTravelsERP]
GO
/****** Object: UserDefinedTableType [dbo].[UDT_TICKET_DETAILS] Script Date: 27-Jan-17 1:15:32 AM ******/
CREATE TYPE [dbo].[UDT_TICKET_DETAILS] AS TABLE(
[PassportId] [int] NOT NULL,
[AirlinesID] [int] NOT NULL,
[OtherAirlines] [varchar](100) NULL,
[IsDirect] [bit] NULL,
[PnrNumber] [varchar](20) NULL,
[TicketNumber] [varchar](20) NULL,
[FlightNumber] [varchar](20) NULL,
[IsBooked] [bit] NULL,
[IsCancelled] [bit] NULL,
[DepartureCityCode] [varchar](20) NULL,
[DepartureDate] [date] NULL,
[DepartureTime] [varchar](20) NULL,
[DestinationCityCode] [varchar](20) NULL,
[ArivalDate] [date] NULL,
[ArrivalTime] [varchar](20) NULL,
[ReportPath] [varchar](100) NULL,
[Remark] [varchar](250) NULL,
[createdBy] [varchar](20) NULL,
[CreatedDate] [datetime] NULL,
[Conn_PnrNumber] [varchar](20) NULL,
[Conn_TicketNumber] [varchar](20) NULL,
[Conn_FlightNumber] [varchar](20) NULL,
[Conn_IsBooked] [bit] NULL,
[Conn_IsCancelled] [bit] NULL,
[Conn_DepartureCityCode] [varchar](20) NULL,
[Conn_DepartureDate] [date] NULL,
[Conn_DepartureTime] [varchar](20) NULL,
[Conn_DestinationCityCode] [varchar](20) NULL,
[Conn_ArivalDate] [date] NULL,
[Conn_ArrivalTime] [varchar](20) NULL,
[Conn_createdBy] [varchar](20) NULL,
[Conn_CreatedDate] [datetime] NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_ADDRESS] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_ADDRESS] AS TABLE(
[ADDRESS_TYPE_ID] [int] NULL,
[ADDRESS] [varchar](80) NULL,
[CITY_CODE] [varchar](10) NULL,
[USER_VILLAGE] [varchar](50) NULL,
[USER_PINCODE] [varchar](20) NULL,
[CREATED_BY] [varchar](20) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_CERTIFICATION] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_CERTIFICATION] AS TABLE(
[CERTIFICATION] [varchar](70) NULL,
[CERTIFICATION_LEVEL] [int] NULL,
[INSTITUTE] [varchar](80) NULL,
[YEAR_OF_PASSING] [int] NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_CONTACT] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_CONTACT] AS TABLE(
[CONTACT_TYPE_ID] [int] NULL,
[CONTACT_NO] [varchar](50) NULL,
[CREATED_BY] [varchar](20) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_DETAILS] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_DETAILS] AS TABLE(
[REGISTRATION_NO] [varchar](20) NULL,
[FIRST_NAME] [varchar](50) NULL,
[MIDDLE_NAME] [varchar](50) NULL,
[LAST_NAME] [varchar](50) NULL,
[GAMCA_NO] [varchar](20) NULL,
[FILE_NAME] [varchar](50) NULL,
[FILE_PATH] [varchar](50) NULL,
[WEBSITE] [varchar](50) NULL,
[SKYPE] [varchar](50) NULL,
[REMARK] [varchar](1000) NULL,
[CONTACT_REMARK] [varchar](1000) NULL,
[CREATED_BY] [varchar](50) NULL,
[AVAILING_TYPE_ID] [int] NULL,
[SOURCE_ID] [int] NULL,
[OTHER_SOURCE] [varchar](20) NULL,
[REFERRER_NAME] [varchar](50) NULL,
[STATUS_ID] [int] NULL,
[REQUIREMENT_ID] [int] NULL,
[LOGIN_ACCESS] [bit] NULL,
[LOGIN_PASSWORD] [varchar](50) NULL,
[MODIFIED_BY] [varchar](20) NULL,
[IS_EXPERIENCED] [bit] NULL,
[EDUCATION_REMARK] [varchar](1000) NULL,
[WORK_REMARK] [varchar](1000) NULL,
[BRANCH_CODE] [varchar](20) NULL,
[LOCATION_CODE] [varchar](20) NULL,
[COMPANY_NAME] [varchar](70) NULL,
[TOTAL_WORK_EXPERIENCE] [varchar](50) NULL,
[TOTAL_GULF_EXPERIENCE] [varchar](50) NULL,
[SKILLS] [varchar](100) NULL,
[FATHER_NAME] [varchar](80) NULL,
[MOTHER_NAME] [varchar](80) NULL,
[GENDER_CODE] [char](1) NULL,
[DATE_OF_BIRTH] [datetime] NULL,
[PLACE_OF_BIRTH] [varchar](50) NULL,
[MARITAL_STATUS_ID] [int] NULL,
[NATIONALITY_ID] [int] NULL,
[CURRENT_LOCATION] [varchar](50) NULL,
[RELIGION_ID] [int] NULL,
[DESIGNATION] [varchar](100) NULL,
[INDUSTRY] [varchar](100) NULL,
[REFERENCE] [varchar](20) NULL,
[POST_APPLIED_FOR] [varchar](50) NULL,
[CIVILIAN_NO] [varchar](20) NULL,
[CLINIC_NAME] [varchar](100) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_DOCUMENT] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_DOCUMENT] AS TABLE(
[DOCUMENT_TYPE_ID] [varchar](50) NULL,
[DOCUMENT_PATH] [varchar](200) NULL,
[CREATED_BY] [varchar](20) NULL,
[MODIFIED_BY] [varchar](20) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_DRIVING] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_DRIVING] AS TABLE(
[DRIVING_LICENCE_NUMBER] [varchar](20) NULL,
[DATE_OF_ISSUE] [datetime] NULL,
[PLACE_OF_ISSUE] [varchar](20) NULL,
[EXPIRY_DATE] [datetime] NULL,
[VEHICLE_TYPE_ID] [int] NULL,
[CREATED_BY] [varchar](20) NULL,
[MODIFIED_BY] [varchar](20) NULL,
[REMARK] [varchar](100) NULL,
[REGISTRATION_NO] [varchar](20) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_EDUCATION] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_EDUCATION] AS TABLE(
[USER_EDUCATION_ID] [int] NULL,
[REGISTRATION_NO] [varchar](12) NOT NULL,
[EDUCATION_TYPE_ID] [int] NULL,
[SPECIALIZATION_TYPE_ID] [int] NULL,
[UNIVERSITY_ID] [varchar](70) NULL,
[UNIVERSITY_YEAR_OF_PASSING] [varchar](8) NULL,
[IS_HIGHEST_QUALIFICATION] [bit] NULL,
[CREATED_BY] [varchar](20) NULL,
[MODIFIED_BY] [varchar](20) NULL,
[IsNEW] [bit] NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_EMAIL] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_EMAIL] AS TABLE(
[USER_EMAIL] [varchar](70) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_EXPERIENCE] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_EXPERIENCE] AS TABLE(
[USER_COMPANY_NAME] [varchar](80) NULL,
[IS_CURRENT_COMPANY] [bit] NULL,
[DESIGNATION_ID] [int] NULL,
[INDUSTRY_ID] [int] NULL,
[CITY_CODE] [varchar](20) NULL,
[WORK_PERIOD_FROM] [datetime] NULL,
[WORK_PERIOD_TO] [datetime] NULL,
[TOTAL_WORK_EXPERIENCE] [decimal](18, 0) NULL,
[REMARK] [varchar](100) NULL,
[CREATED_BY] [varchar](20) NULL,
[MODIFIED_BY] [varchar](20) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_LANGUAGE] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_LANGUAGE] AS TABLE(
[LANGUAGE_ID] [int] NULL,
[IS_READ] [bit] NULL,
[IS_WRITE] [bit] NULL,
[IS_SPEAK] [bit] NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[UDT_USER_PASSPORT] Script Date: 27-Jan-17 1:15:33 AM ******/
CREATE TYPE [dbo].[UDT_USER_PASSPORT] AS TABLE(
[PASSPORT_NUMBER] [varchar](20) NULL,
[DATE_OF_ISSUE] [datetime] NULL,
[PLACE_OF_ISSUE] [varchar](50) NULL,
[DATE_OF_EXPIRY] [datetime] NULL,
[EMIGRATION_CLEARANCE_REQUIRED] [bit] NULL,
[CREATED_BY] [varchar](20) NULL,
[MODIFIED_BY] [varchar](20) NULL,
[REGISTRATION_NO] [varchar](20) NULL
)
GO
/****** Object: StoredProcedure [dbo].[GET_COUNTRY_STATE_CITY] Script Date: 27-Jan-17 1:15:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROC [dbo].[GET_COUNTRY_STATE_CITY]
AS
BEGIN
SELECT TCM.CITY_CODE,TCM.CITY_NAME,TSM.STATE_CODE,TSM.STATE_NAME,TCUM.COUNTRY_CODE,TCUM.COUNTRY_NAME,tcm.IS_ACTIVE
FROM TBL_CITY_MASTER TCM WITH (NOLOCK)
LEFT JOIN TBL_STATE_MASTER TSM WITH (NOLOCK) ON TCM.STATE_CODE = TSM.STATE_CODE
LEFT JOIN TBL_COUNTRY_MASTER TCUM WITH (NOLOCK) ON TSM.COUNTRY_CODE = TCUM.COUNTRY_CODE
ORDER BY TCUM.COUNTRY_NAME,TSM.STATE_NAME,TCM.CITY_NAME
END
GO
/****** Object: StoredProcedure [dbo].[GET_DESIGNATION_BYINDUSTRY] Script Date: 27-Jan-17 1:15:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GET_DESIGNATION_BYINDUSTRY]
(
@INDUSTRY_ID VARCHAR(100) = NULL
)
AS
BEGIN
SELECT DESIGNATION_ID,DESIGNATION_NAME, TRT.INDUSTRY_ID,TRT.INDUSTRY_TYPE
FROM TBL_DESIGNATION_MASTER TDM WITH (NOLOCK)
JOIN TBL_INDUSTRY_MASTER TRT WITH (NOLOCK) ON TDM.INDUSTRY_ID=TRT.INDUSTRY_ID
WHERE TDM.INDUSTRY_ID IN (SELECT * FROM SPLIT(@INDUSTRY_ID,','))
END
GO
/****** Object: StoredProcedure [dbo].[GET_SPECIALIZATION_BYEDUCATION] Script Date: 27-Jan-17 1:15:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GET_SPECIALIZATION_BYEDUCATION]
(
@EDUCATION_TYPE_ID VARCHAR(50) = null
)
AS
BEGIN
SELECT TEM.EDUCATION_TYPE_ID,TEM.EDUCATION_TYPE,TSM.SPECIALIZATION_TYPE,TSM.SPECIALIZATION_ID
FROM TBL_EDUCATION_TYPE_MASTER TEM WITH (NOLOCK)
JOIN TBL_SPECIALIZATION_MASTER TSM WITH (NOLOCK) ON TEM.EDUCATION_TYPE_ID = TSM.EDUCATION_TYPE_ID
WHERE TEM.EDUCATION_TYPE_ID IN (SELECT * FROM Split(@EDUCATION_TYPE_ID,','))
ORDER BY EDUCATION_TYPE,TSM.SPECIALIZATION_TYPE
END
GO
/****** Object: StoredProcedure [dbo].[PROC_ADD_DELETE_MENU_FOR_USERTYPE] Script Date: 27-Jan-17 1:15:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[PROC_ADD_DELETE_MENU_FOR_USERTYPE]
@MENU_ID int,
@USER_TYPE_ID VARCHAR(20),
@CREATED_BY varchar(50) ,
@CONDITIONAL_OPERATOR varchar(50)
AS
BEGIN
IF @CONDITIONAL_OPERATOR ='ADD_MENU_FOR_USERTYPE'
BEGIN
IF NOT EXISTS (SELECT * from TBL_USER_MENU_MAPPING WITH(NOLOCK) WHERE MENU_ID=@MENU_ID AND REGISTRATION_NUMBER = @USER_TYPE_ID)
BEGIN
INSERT INTO TBL_USER_MENU_MAPPING
(
MENU_ID,
REGISTRATION_NUMBER,
CREATED_DATE,
CREATED_BY
)
VALUES
(
@MENU_ID,
@USER_TYPE_ID,
GETDATE(),
@CREATED_BY
)
END
END
ELSE IF @CONDITIONAL_OPERATOR ='DEL_MENU_FOR_USERTYPE'
BEGIN
DELETE FROM TBL_USER_MENU_MAPPING WHERE MENU_ID=@MENU_ID AND REGISTRATION_NUMBER = @USER_TYPE_ID
END
END
GO
/****** Object: StoredProcedure [dbo].[PROC_ADVERTIZEMENT_MASTER] Script Date: 27-Jan-17 1:15:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[PROC_ADVERTIZEMENT_MASTER]
(
@CONDITIONAL_OPERATOR VARCHAR(30) = NULL
)
AS
BEGIN
IF @CONDITIONAL_OPERATOR = 'REQUIRMENT'
BEGIN
SELECT REQUIREMENT_ID ,(ISNULL(TCM.COMPANY_NAME,CLIENT.NAME) + ' | ' + ISNULL(TRD.JOB_TITLE,''))REQUIREMENT
FROM TBL_REQUIREMENT_DETAILS TRD WITH (NOLOCK)
LEFT JOIN TBL_COMPANY_MASTER TCM WITH(NOLOCK) ON TCM.COMPANY_ID=TRD.COMPANY_ID
LEFT JOIN
(
SELECT TUD.REGISTRATION_NO,(ISNULL(TUPD.FIRST_NAME,'') +' ' + ISNULL(TUPD.MIDDLE_NAME,'') +' '+ ISNULL(TUPD.LAST_NAME,''))NAME
FROM TBL_USER_DETAILS TUD WITH(NOLOCK)
JOIN TBL_USER_PERSONAL_DETAILS TUPD WITH(NOLOCK) ON TUD.REGISTRATION_NO=TUPD.REGISTRATION_NO
WHERE TUD.USER_TYPE_ID = 5
)CLIENT ON TRD.CONTACT_PERSON = CLIENT.REGISTRATION_NO
WHERE TRD.IS_ACTIVE=1
return
END
IF @CONDITIONAL_OPERATOR = 'SELECT'
BEGIN
--SELECT TAM.ADV_ID,TAM.PAPER_NAME,TAM.AD_AGENCY_NAME,TAM.EXPENSES,FILE_PATH,ADV_DATE,
--ISNULL(TCM.COMPANY_NAME,
--ISNULL(TUPD.FIRST_NAME,'') + SPACE(1) +
--ISNULL(TUPD.MIDDLE_NAME,'') + SPACE(1)
--+ ISNULL(TUPD.LAST_NAME,'')
--)REQUIREMENTs
--FROM TBL_ADVERTISEMENT_MASTER TAM
--JOIN TBL_REQUIREMENT_DETAILS TRD on TAM.REQUIREMENTS = TRD.requirement_id
--LEFT join TBL_COMPANY_MASTER TCM ON TRD.COMPANY_ID = TCM.COMPANY_ID
----LEFT JOIN TBL_USER_DETAILS TUD ON TRD.CONTACT_PERSON = TUD.REGISTRATION_NO
--LEFT JOIN TBL_USER_PERSONAL_DETAILS TUPD ON TRD.CONTACT_PERSON = TUPD.REGISTRATION_NO
SELECT TAM.ADV_ID,TAM.PAPER_NAME,TAM.AD_AGENCY_NAME,TAM.EXPENSES,FILE_PATH,ADV_DATE,
REQUIREMENT_ID ,(ISNULL(TCM.COMPANY_NAME,CLIENT.NAME) + ' | ' + ISNULL(TRD.JOB_TITLE,''))REQUIREMENTs
FROM TBL_ADVERTISEMENT_MASTER TAM
JOIN TBL_REQUIREMENT_DETAILS TRD WITH (NOLOCK) ON TAM.REQUIREMENTS = TRD.REQUIREMENT_ID
LEFT JOIN TBL_COMPANY_MASTER TCM WITH(NOLOCK) ON TCM.COMPANY_ID=TRD.COMPANY_ID
LEFT JOIN
(
SELECT TUD.REGISTRATION_NO,(ISNULL(TUPD.FIRST_NAME,'') +' ' + ISNULL(TUPD.MIDDLE_NAME,'') +' '+ ISNULL(TUPD.LAST_NAME,''))NAME
FROM TBL_USER_DETAILS TUD WITH(NOLOCK)
JOIN TBL_USER_PERSONAL_DETAILS TUPD WITH(NOLOCK) ON TUD.REGISTRATION_NO=TUPD.REGISTRATION_NO
WHERE TUD.USER_TYPE_ID = 5
)CLIENT ON TRD.CONTACT_PERSON = CLIENT.REGISTRATION_NO
WHERE TRD.IS_ACTIVE=1
END
END
GO
/****** Object: StoredProcedure [dbo].[PROC_ALL_MASTER] Script Date: 27-Jan-17 1:15:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[PROC_ALL_MASTER]
(
@CONDITIONAL_OPERATOR VARCHAR(50) =NULL,
@REGISTRATION_NO VARCHAR(12) =NULL
)
AS
BEGIN
IF @CONDITIONAL_OPERATOR='COUNTRY_STATE_CITY_MASTER'
BEGIN
SELECT TCM.CITY_CODE,TCM.CITY_NAME,TSM.STATE_CODE,TSM.STATE_NAME,TCUM.COUNTRY_CODE,TCUM.COUNTRY_NAME,tcm.IS_ACTIVE
FROM TBL_CITY_MASTER TCM WITH (NOLOCK)
LEFT JOIN TBL_STATE_MASTER TSM WITH (NOLOCK) ON TCM.STATE_CODE = TSM.STATE_CODE
LEFT JOIN TBL_COUNTRY_MASTER TCUM WITH (NOLOCK) ON TSM.COUNTRY_CODE = TCUM.COUNTRY_CODE
ORDER BY TCUM.COUNTRY_NAME,TSM.STATE_NAME,TCM.CITY_NAME
END
IF @CONDITIONAL_OPERATOR='AvailingType'
BEGIN
SELECT AVAILING_TYPE_ID,AVAILING_TYPE,TYPE_ORDER,IS_ACTIVE
FROM TBL_AVAILING_TYPE_MASTER WITH (NOLOCK)
ORDER BY AVAILING_TYPE
END
IF @CONDITIONAL_OPERATOR='Source'
BEGIN
SELECT SOURCE_ID,SOURCE_NAME,SOURCE_ORDER
FROM TBL_SOURCE_MASTER WITH (NOLOCK)
ORDER BY SOURCE_NAME
END
IF @CONDITIONAL_OPERATOR='Status'
BEGIN
SELECT STATUS_ID,STATUS_NAME,DESCRIPTION,STATUS_ORDER
FROM TBL_STATUS_MASTER WITH (NOLOCK)
WHERE IS_ACTIVE=1
ORDER BY STATUS_NAME
END
IF @CONDITIONAL_OPERATOR='Requirement'
BEGIN
select TRD.REQUIREMENT_ID,TCM.COMPANY_NAME
from TBL_REQUIREMENT_DETAILS TRD WITH (NOLOCK)
JOIN TBL_COMPANY_MASTER TCM WITH (NOLOCK) ON TRD.COMPANY_ID=TCM.COMPANY_ID
ORDER BY TCM.COMPANY_NAME
END
IF @CONDITIONAL_OPERATOR='VisaType'
BEGIN
SELECT VISA_ID,VISA_NUMBER
FROM TBL_VISA_MASTER WITH (NOLOCK)
END
IF @CONDITIONAL_OPERATOR='MaritalStatus'
BEGIN
SELECT MARITAL_STATUS_ID,MARITAL_STATUS,MARITAL_STATUS_ORDER
FROM TBL_MARITAL_STATUS_MASTER WITH (NOLOCK)
ORDER BY MARITAL_STATUS
END
IF @CONDITIONAL_OPERATOR='Nationality'
BEGIN
SELECT NATIONALITY_ID,NATIONALITY
FROM TBL_NATIONALITY_MASTER WITH (NOLOCK)
ORDER BY NATIONALITY
END
IF @CONDITIONAL_OPERATOR='Location'
BEGIN
SELECT CITY_CODE,CITY_NAME
FROM TBL_CITY_MASTER WITH (NOLOCK)
ORDER BY CITY_NAME
END
IF @CONDITIONAL_OPERATOR='Religion'
BEGIN
SELECT RELIGION_ID,RELIGION_NAME
FROM TBL_RELIGION_MASTER WITH (NOLOCK)
ORDER BY RELIGION_NAME
END
IF @CONDITIONAL_OPERATOR='Languages'
BEGIN
SELECT LANGUAGE_ID,LANGUAGE_NAME
FROM TBL_LANGUAGE_MASTER WITH (NOLOCK)
ORDER BY LANGUAGE_NAME
END
IF @CONDITIONAL_OPERATOR='AddressType'
BEGIN
SELECT ADDRESS_TYPE_ID,ADDRESS_TYPE,TYPE_FOR
FROM TBL_ADDRESS_TYPE_MASTER WITH (NOLOCK)
ORDER BY ADDRESS_TYPE
END
IF @CONDITIONAL_OPERATOR='University'
BEGIN
SELECT UNIVERSITY_ID,UNIVERSITY_NAME
FROM TBL_UNIVERSITY_MASTER WITH (NOLOCK)
END
IF @CONDITIONAL_OPERATOR='Company'
BEGIN
SELECT COMPANY_ID,COMPANY_NAME
FROM TBL_COMPANY_MASTER WITH (NOLOCK)
ORDER BY COMPANY_NAME
END
IF @CONDITIONAL_OPERATOR='Designation'
BEGIN
SELECT DESIGNATION_ID,DESIGNATION_NAME, TRT.INDUSTRY_ID,TRT.INDUSTRY_TYPE
FROM TBL_DESIGNATION_MASTER TDM WITH (NOLOCK)
JOIN TBL_INDUSTRY_MASTER TRT WITH (NOLOCK) ON TDM.INDUSTRY_ID=TRT.INDUSTRY_ID
ORDER BY TRT.INDUSTRY_TYPE,DESIGNATION_NAME
END
IF @CONDITIONAL_OPERATOR='Industry'
BEGIN
SELECT INDUSTRY_ID,INDUSTRY_TYPE
FROM TBL_INDUSTRY_MASTER WITH (NOLOCK)
ORDER BY INDUSTRY_TYPE
END
IF @CONDITIONAL_OPERATOR='GetAddressDetails'
BEGIN
--SELECT '1' USER_ADDRESS_ID,'Add'ADDRESS,'C001'CITY_CODE,'Mumbai'USER_VILLAGE,'400099'USER_PINCODE,'India'COUNTRY_NAME,'StateCity'STATECITY
SELECT USER_ADDRESS_ID,(TATM.ADDRESS_TYPE+'-'+ADDRESS)ADDRESS,TCM.CITY_CODE,USER_VILLAGE,USER_PINCODE,TCTM.COUNTRY_NAME,(TSM.STATE_NAME+' '+TCM.CITY_NAME)STATECITY
FROM TBL_USER_ADDRESS TUA WITH(NOLOCK)
JOIN TBL_ADDRESS_TYPE_MASTER TATM WITH(NOLOCK) ON TUA.ADDRESS_TYPE_ID = TATM.ADDRESS_TYPE_ID
JOIN TBL_CITY_MASTER TCM WITH(NOLOCK) ON TUA.CITY_CODE = TCM.CITY_CODE
LEFT JOIN TBL_STATE_MASTER TSM WITH(NOLOCK) ON TCM.STATE_CODE =TSM.STATE_CODE
LEFT JOIN TBL_COUNTRY_MASTER TCTM WITH(NOLOCK) ON TSM.COUNTRY_CODE =TCTM.COUNTRY_CODE
WHERE REGISTRATION_NUMBER = @REGISTRATION_NO
END
IF @CONDITIONAL_OPERATOR='Education'
BEGIN
SELECT TEM.EDUCATION_TYPE_ID,TEM.EDUCATION_TYPE,TSM.SPECIALIZATION_TYPE,TSM.SPECIALIZATION_ID
FROM TBL_EDUCATION_TYPE_MASTER TEM WITH (NOLOCK)
JOIN TBL_SPECIALIZATION_MASTER TSM WITH (NOLOCK) ON TEM.EDUCATION_TYPE_ID = TSM.EDUCATION_TYPE_ID
ORDER BY EDUCATION_TYPE,TSM.SPECIALIZATION_TYPE
END
IF @CONDITIONAL_OPERATOR='Specialization'
BEGIN
SELECT SPECIALIZATION_ID,SPECIALIZATION_TYPE
FROM TBL_SPECIALIZATION_MASTER WITH (NOLOCK)
ORDER BY SPECIALIZATION_TYPE
END
IF @CONDITIONAL_OPERATOR='Vehicle_type'
BEGIN
SELECT VEHICLE_TYPE_ID,VEHICLE_TYPE
FROM TBL_VEHICLE_TYPE WITH (NOLOCK)
ORDER BY VEHICLE_TYPE
END
IF @CONDITIONAL_OPERATOR='Branch'
BEGIN
SELECT BRANCH_CODE,BRANCH_NAME
FROM TBL_BRANCH_MASTER WITH (NOLOCK)
ORDER BY BRANCH_NAME
END
IF @CONDITIONAL_OPERATOR='UserType'
BEGIN
--SELECT USER_TYPE,USER_TYPE_ID
--FROM TBL_USER_TYPE WITH (NOLOCK)
--WHERE IS_ACTIVE=1
SELECT TUD.REGISTRATION_NO AS USER_TYPE_ID,
ISNULL(FIRST_NAME + SPACE(1),'') +
isnull(MIDDLE_NAME + SPACE(1),'')+ isnull(LAST_NAME,'')USER_TYPE
FROM TBL_USER_PERSONAL_DETAILS TUP WITH(NOLOCK)
JOIN TBL_USER_DETAILS TUD WITH(NOLOCK) ON TUP.REGISTRATION_NO = TUD.REGISTRATION_NO
WHERE USER_TYPE_ID =2
END
IF @CONDITIONAL_OPERATOR='MODE_OF_INTERVIEW'
BEGIN
SELECT INTERVIEW_MODE,INTERVIEW_MODE_ID
FROM TBL_INTERVIEW_MODE_MASTER WITH (NOLOCK)
WHERE IS_ACTIVE=1
ORDER BY INTERVIEW_MODE
END
IF @CONDITIONAL_OPERATOR='CURRENCY'
BEGIN
SELECT CURRENCY_ID,CURRENCY_SYMBOL AS CURRENCY_NAME
FROM TBL_CURRENCY_MASTER WITH (NOLOCK)
WHERE IS_ACTIVE=1
END
IF @CONDITIONAL_OPERATOR='Agent'
BEGIN
SELECT TUD.REGISTRATION_NO,(ISNULL(TUPD.FIRST_NAME,'') +' '+ISNULL(TUPD.LAST_NAME,''))COMPANY_NAME
FROM TBL_USER_DETAILS TUD WITH (NOLOCK)
JOIN TBL_USER_PERSONAL_DETAILS TUPD WITH(NOLOCK) ON TUD.REGISTRATION_NO=TUPD.REGISTRATION_NO
WHERE USER_TYPE_ID=3
END
IF @CONDITIONAL_OPERATOR='CERTIFICATION'
BEGIN
SELECT CERTIFICATION_ID,CERTIFICATION_NAME
FROM TBL_CERTIFICATION_MASTER WITH (NOLOCK)
WHERE IS_ACTIVE=1
ORDER BY CERTIFICATION_NAME
END
IF @CONDITIONAL_OPERATOR='GETCLIENT'
BEGIN
SELECT TUD.REGISTRATION_NO,(ISNULL(TUPD.FIRST_NAME,'') +' ' + ISNULL(TUPD.MIDDLE_NAME,'') +' '+ ISNULL(TUPD.LAST_NAME,''))NAME
FROM TBL_USER_DETAILS TUD WITH (NOLOCK)
JOIN TBL_USER_PERSONAL_DETAILS TUPD WITH (NOLOCK) ON TUD.REGISTRATION_NO = TUPD.REGISTRATION_NO
WHERE USER_TYPE_ID = 5 AND IS_ACTIVE =1
ORDER BY (ISNULL(TUPD.FIRST_NAME,'') +' ' + ISNULL(TUPD.MIDDLE_NAME,'') +' '+ ISNULL(TUPD.LAST_NAME,''))
END
IF @CONDITIONAL_OPERATOR='GETCANDIDATE'
BEGIN
SELECT TPD.PASSPORT_ID,(ISNULL(TPD.PASSPORT_NUMBER,'') +' | '+ ISNULL(TUPD.FIRST_NAME,'') +' ' + ISNULL(TUPD.MIDDLE_NAME,'') +' '+ ISNULL(TUPD.LAST_NAME,''))NAME
FROM TBL_USER_DETAILS TUD WITH (NOLOCK)
JOIN TBL_USER_PERSONAL_DETAILS TUPD WITH (NOLOCK) ON TUD.REGISTRATION_NO = TUPD.REGISTRATION_NO
JOIN TBL_PASSPORT_DETAILS TPD WITH(NOLOCK) ON TUD.REGISTRATION_NO = TPD.REGISTATION_NUMBER
WHERE USER_TYPE_ID = 1 AND TPD.PASSPORT_NUMBER IS NOT NULL AND STATUS_ID=6
ORDER BY (ISNULL(TUPD.FIRST_NAME,'') +' ' + ISNULL(TUPD.MIDDLE_NAME,'') +' '+ ISNULL(TUPD.LAST_NAME,''))
END
IF @CONDITIONAL_OPERATOR='GETDOCTOR'
BEGIN
SELECT TUD.REGISTRATION_NO,(ISNULL(TUPD.FIRST_NAME,'') +' ' + ISNULL(TUPD.MIDDLE_NAME,'') +' '+ ISNULL(TUPD.LAST_NAME,''))NAME
FROM TBL_USER_DETAILS TUD WITH (NOLOCK)
JOIN TBL_USER_PERSONAL_DETAILS TUPD WITH (NOLOCK) ON TUD.REGISTRATION_NO = TUPD.REGISTRATION_NO
WHERE USER_TYPE_ID = 4
ORDER BY (ISNULL(TUPD.FIRST_NAME,'') +' ' + ISNULL(TUPD.MIDDLE_NAME,'') +' '+ ISNULL(TUPD.LAST_NAME,''))
END
IF @CONDITIONAL_OPERATOR='GETCANDIDATEFOR_VISA_ENDORSEMENT'
BEGIN
SELECT TPD.PASSPORT_ID,(ISNULL(TPD.PASSPORT_NUMBER,'') +' | '+ ISNULL(TUPD.FIRST_NAME,'') +' ' + ISNULL(TUPD.MIDDLE_NAME,'') +' '+ ISNULL(TUPD.LAST_NAME,''))NAME
FROM TBL_USER_DETAILS TUD WITH (NOLOCK)
JOIN TBL_USER_PERSONAL_DETAILS TUPD WITH (NOLOCK) ON TUD.REGISTRATION_NO = TUPD.REGISTRATION_NO
JOIN TBL_PASSPORT_DETAILS TPD WITH(NOLOCK) ON TUD.REGISTRATION_NO = TPD.REGISTATION_NUMBER
JOIN TBL_MOFA TMO WITH(NOLOCK) ON TPD.PASSPORT_ID = TMO.PassportID
WHERE USER_TYPE_ID = 1 AND TPD.PASSPORT_NUMBER IS NOT NULL AND DDDate IS NOT NULL--AND STATUS_ID=6
ORDER BY (ISNULL(TUPD.FIRST_NAME,'') +' ' + ISNULL(TUPD.MIDDLE_NAME,'') +' '+ ISNULL(TUPD.LAST_NAME,''))
END
IF @CONDITIONAL_OPERATOR='GETAIRLINES'
BEGIN
SELECT AirlinesId,AirlinesName
from TBL_AIRLINES_MASTER
ORDER BY AirlinesName
END
IF @CONDITIONAL_OPERATOR='PORTAL'
BEGIN
SELECT PORTAL_ID,PORTAL_NAME
FROM TBL_PORTAL_MASTER WITH(NOLOCK)
ORDER BY PORTAL_NAME
END
IF @CONDITIONAL_OPERATOR='FOLDER_NAME'
BEGIN
SELECT * FROM TBL_SAVE_TO_FOLDER_DETAILS
END
END
GO
/****** Object: StoredProcedure [dbo].[PROC_CANDIDATE_STATUS_SEARCH] Script Date: 27-Jan-17 1:15:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[PROC_CANDIDATE_STATUS_SEARCH]
(
@REGISTRATION_NO VARCHAR(20) = NULL
)AS
BEGIN
--Personal Details--
SELECT top 1 TUD.REGISTRATION_NO,TUD.CREATED_DATE,ltrim(rtrim(isnull(FIRST_NAME + ' ',' '))) +' ' + ltrim(rtrim(isnull(MIDDLE_NAME + ' ','')))+ ' ' +ltrim(rtrim(isnull(LAST_NAME,''))) AS CANDIDATE_NAME,
TPD.FIRST_NAME,TPD.MIDDLE_NAME,TPD.LAST_NAME,TPD.FATHER_NAME,(select GENDER_NAME from TBL_GENDER_MASTER where GENDER_CODE = TPD.GENDER_CODE) as GENDER_NAME,TPD.GENDER_CODE,
TPD.DATE_OF_BIRTH,DATEDIFF(YEAR,DATE_OF_BIRTH,GETDATE()) -(CASE WHEN DATEADD(YY,DATEDIFF(YEAR,DATE_OF_BIRTH,GETDATE()),DATE_OF_BIRTH)> GETDATE() THEN 1 ELSE 0 END) AGE,TPAD.PASSPORT_NUMBER as PASSPORT_NUMBER,
TPD.PLACE_OF_BIRTH,TBL_MARITAL.MARITAL_STATUS,TPD.MARITAL_STATUS_ID,TBL_NATION.NATIONALITY,TPD.NATIONALITY_ID,
TBL_RELIGION.RELIGION_NAME,TPD.RELIGION_ID,TUD.USER_IMAGE_PATH as FILE_PATH,
CONTACT.CONTACT_NO,USER_EMAIL.USER_EMAIL USER_EMAIL,
ISNULL(TSM.SOURCE_NAME,OTHER_SOURCE) AS SOURCE_NAME,AVAILING_TYPE,
TCM.CITY_NAME AS CURRENT_LOCATION,
TPAD.EMIGRATION_CLEARANCE_REQUIRED as EMIGRATION_CLEARANCE_REQUIRED
FROM TBL_USER_DETAILS TUD WITH(NOLOCK)
JOIN TBL_USER_PERSONAL_DETAILS TPD WITH(NOLOCK) ON TUD.REGISTRATION_NO = TPD.REGISTRATION_NO
LEFT Join TBL_MARITAL_STATUS_MASTER TBL_MARITAL With(NOLOCK) ON TPD.MARITAL_STATUS_ID = TBL_MARITAL.MARITAL_STATUS_ID
LEFT Join TBL_NATIONALITY_MASTER TBL_NATION With(NOLOCK) ON TPD.NATIONALITY_ID = TPD.NATIONALITY_ID
LEFT Join TBL_RELIGION_MASTER TBL_RELIGION With(NOLOCK) ON TPD.RELIGION_ID = TBL_RELIGION.RELIGION_ID
LEFT JOIN TBL_PASSPORT_DETAILS TPAD WITH (NOLOCK)ON TUD.REGISTRATION_NO = TPAD.REGISTATION_NUMBER
LEFT JOIN TBL_SOURCE_MASTER TSM WITH (NOLOCK) ON TUD.SOURCE_ID = TSM.SOURCE_ID
LEFT JOIN TBL_AVAILING_TYPE_MASTER TAM WITH (NOLOCK) ON TUD.AVAILING_TYPE_ID = TAM.AVAILING_TYPE_ID
LEFT JOIN TBL_CITY_MASTER TCM WITH (NOLOCK) ON TUD.LOCATION_CODE = TCM.CITY_CODE
LEFT JOIN
(
SELECT DISTINCT p.REGISTRATION_NUMBER,
(
SELECT SUBSTRING((SELECT ',' + TUC.CONTACT_NO
FROM TBL_USER_CONTACTS TUC
WHERE P.REGISTRATION_NUMBER = TUC.REGISTRATION_NUMBER
ORDER BY TUC.USER_CONTACT_ID
FOR XML PATH('')),2,200000)
) AS CONTACT_NO
FROM TBL_USER_CONTACTS p WITH (NOLOCK)
)CONTACT ON TUD.REGISTRATION_NO = CONTACT.REGISTRATION_NUMBER
LEFT JOIN
(
SELECT DISTINCT p.REGISTRATION_NUMBER,
(
SELECT SUBSTRING((SELECT ',' + TEUA.USER_EMAIL
FROM TBL_USER_EMAIL_ADDRESS TEUA
WHERE P.REGISTRATION_NUMBER = TEUA.REGISTRATION_NUMBER
ORDER BY TEUA.USER_EMAIL
FOR XML PATH('')),2,200000)
)AS USER_EMAIL
FROM TBL_USER_EMAIL_ADDRESS p WITH(NOLOCK)
)USER_EMAIL ON TUD.REGISTRATION_NO = user_email.REGISTRATION_NUMBER
WHERE TUD.REGISTRATION_NO = @REGISTRATION_NO
-- SECTION 2 EDUCATION DETAILS
SELECT EDUCATION_TYPE,SPECIALIZATION_TYPE,TUED.UNIVERSITY_ID AS UNIVERSITY_NAME,REGISTRATION_NO,
UNIVERSITY_YEAR_OF_PASSING FROM TBL_USER_EDUCATION_DETAILS TUED WITH (NOLOCK)
JOIN TBL_EDUCATION_TYPE_MASTER TETM WITH (NOLOCK)ON TUED.EDUCATION_TYPE_ID = TETM.EDUCATION_TYPE_ID
JOIN TBL_SPECIALIZATION_MASTER TSM WITH (NOLOCK)ON TUED.SPECIALIZATION_TYPE_ID = TSM.SPECIALIZATION_ID
WHERE REGISTRATION_NO = @REGISTRATION_NO
--Certification DETAILS
SELECT REGISTRATION_NUMBER,CERTIFICATION_NAME as CERTIFICATION,CERTIFICATION_LEVEL,
INSTITUTE, YEAR_OF_PASSING FROM TBL_USER_CERTIFICATION TUC WITH (NOLOCK)
JOIN TBL_CERTIFICATION_MASTER TCM WITH (NOLOCK)ON TUC.USER_CERTIFICATION_ID = TCM.CERTIFICATION_ID
WHERE REGISTRATION_NUMBER = @REGISTRATION_NO
----Industry Details
--SELECT TUE.REGISTRATION_NO,TIM.INDUSTRY_TYPE,TDM.DESIGNATION_NAME,TUD.TOTAL_WORK_EXPERIENCE,TUD.TOTAL_GULF_EXPERIENCE
--FROM TBL_USER_EXPERIENCE TUE WITH(NOLOCK)
--JOIN TBL_INDUSTRY_MASTER TIM WITH(NOLOCK) ON TUE.INDUSTRY_ID=TIM.INDUSTRY_ID
--JOIN TBL_DESIGNATION_MASTER TDM WITH (NOLOCK) ON TUE.DESIGNATION_ID=TDM.DESIGNATION_ID
--JOIN TBL_USER_DETAILS TUD WITH(NOLOCK) ON TUD.REGISTRATION_NO = TUE.REGISTRATION_NO
--WHERE TUE.REGISTRATION_NO = @REGISTRATION_NO
----Mofa Details
--select top 1 tbl_mofa.MofaNumber,tbl_mofa.MofaDate,tbl_mofa.ApplicationNumber,tbl_mofa.ApplicationDate,
--tbl_mofa.HealthNumber,tbl_mofa.HealthDate,tbl_mofa.DDDate,tbl_mofa.DDNumber
--from TBL_MOFA tbl_mofa
--join TBL_PASSPORT_DETAILS tbl_passport
--on tbl_mofa.PassportID = tbl_passport.PASSPORT_ID
--where tbl_passport.REGISTATION_NUMBER = @REGISTRATION_NO
--ORDER BY CREATED_DATE DESC
----Medical Details
--select top 1 tbl_status.STATUS_NAME,tbl_medical.CheckupDate,
--(select FIRST_NAME + ' ' + LAST_NAME from TBL_USER_PERSONAL_DETAILS tbl_personal where tbl_personal.REGISTRATION_NO = tbl_medical.DoctorID) as Doctor
--from TBL_MEDICAL tbl_medical
--Join TBL_STATUS_MASTER tbl_status
--on tbl_status.STATUS_ID = tbl_medical.MedicalStatus
--Join TBL_PASSPORT_DETAILS tbl_passport
--on tbl_passport.PASSPORT_ID = tbl_medical.PassportID
--where tbl_passport.REGISTATION_NUMBER = @REGISTRATION_NO
--ORDER BY tbl_medical.CreatedDate DESC
----Visa Endorsement
--select top 1 tbl_status.STATUS_NAME, tbl_visa_endorsement.SubmissionDate
--from TBL_VISA_ENDORSEMENT tbl_visa_endorsement
--join TBL_STATUS_MASTER tbl_status
--on tbl_status.STATUS_ID = tbl_visa_endorsement.SubmissionStatusID
--join TBL_USER_DETAILS tbl_user_details
--on tbl_user_details.REGISTRATION_NO = @REGISTRATION_NO
--ORDER BY tbl_visa_endorsement.CreatedDate DESC
----Policy Details
--select top 1 tbl_policy.POLICYID, tbl_policy.PolicyDate,tbl_policy.Policy
--from TBL_POLICY tbl_policy
--join TBL_PASSPORT_DETAILS tbl_passport
--on tbl_passport.PASSPORT_ID = tbl_policy.PassportID
--where tbl_passport.REGISTATION_NUMBER = @REGISTRATION_NO
--ORDER BY CreatedDate DESC
----Travel Details
--select top 1 CASE WHEN tbl_ticket.IsBooked=1 THEN 'BOOKED' ELSE 'CANCELLED' end TRAVEL_STATUS,
--tbl_ticket.PnrNumber,tbl_ticket.DepartureDate,
--tbl_airlines.AirlinesName,tbl_city.CITY_NAME as DepartureCity,
--tbl_destination_city.CITY_NAME as DestinationCity,
--tbl_ticket.DepartureTime ,
--tbl_ticket.ArrivalTime
--from TBL_TICKET tbl_ticket
--join TBL_AIRLINES_MASTER tbl_airlines
--on tbl_airlines.AirlinesId = tbl_ticket.AirlinesID
--join TBL_CITY_MASTER tbl_city
--on tbl_city.CITY_CODE = tbl_ticket.DepartureCityCode
--join TBL_CITY_MASTER tbl_destination_city
--on tbl_destination_city.CITY_CODE = tbl_ticket.DestinationCityCode
--join TBL_PASSPORT_DETAILS tbl_passport
--on tbl_passport.PASSPORT_ID = tbl_ticket.PassportId
--where tbl_passport.REGISTATION_NUMBER = @REGISTRATION_NO
--ORDER BY tbl_ticket.CreatedDate DESC
--USER REQUIREMENT DETAILS
SELECT REGISTRATION_NO,REQUIREMENT_ID,CANDIDATE_STATUS,CURRENT_STATUS,CREATED_BY
FROM TBL_USER_REQUIREMENT
WHERE REGISTRATION_NO=@REGISTRATION_NO
ORDER BY CREATED_DATE DESC
END
GO
/****** Object: StoredProcedure [dbo].[PROC_CANDIDATE_STATUS_SEARCH_1] Script Date: 27-Jan-17 1:15:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[PROC_CANDIDATE_STATUS_SEARCH_1]
(
@REGISTRATION_NO VARCHAR(20) ='C1020160004'
)AS
--- section 1
BEGIN
SELECT TUD.REGISTRATION_NO, CONVERT(VARCHAR(12),TUD.CREATED_DATE,103) REGISTRATION_DATE ,
ltrim(rtrim(isnull(FIRST_NAME + ' ',' '))) +' ' + ltrim(rtrim(isnull(MIDDLE_NAME + ' ','')))+ ' ' +
ltrim(rtrim(isnull(LAST_NAME,''))) AS CANDIDATE_NAME, CONVERT(VARCHAR(12),DATE_OF_BIRTH,103)DATE_OF_BIRTH,
DATEDIFF(YEAR,DATE_OF_BIRTH,GETDATE()) -(CASE WHEN DATEADD(YY,DATEDIFF(YEAR,DATE_OF_BIRTH,GETDATE()),DATE_OF_BIRTH)> GETDATE() THEN 1 ELSE 0 END) AGE,
PASSPORT_NUMBER,CONTACT.CONTACT_NO,user_email.user_email USER_EMAIL--,DESIGNATION_NAME,ROLE_TYPE
,ISNULL(TSM.SOURCE_NAME,OTHER_SOURCE) AS SOURCE_NAME,AVAILING_TYPE,
CASE WHEN ISNULL(EMIGRATION_CLEARANCE_REQUIRED,0) = 0 THEN 'NO' ELSE 'YES' END AS EMIGRATION_CLEARANCE_REQUIRED,
CONVERT(VARCHAR,TOTAL_WORK_EXPERIENCE)TOTAL_WORK_EXPERIENCE,CONVERT(VARCHAR,TOTAL_GULF_EXPERIENCE)TOTAL_GULF_EXPERIENCE,
CITY_NAME AS LOCATION,
MOFANUMBER,CONVERT(VARCHAR(12),MOFADATE,103)MOFA_DATE, TM.ApplicationNumber APPLICATION_NO,CONVERT(VARCHAR(12),TM.ApplicationDate,103) APPLICATION_DATE,
HEALTHNUMBER, CONVERT(VARCHAR(12),HEALTHDATE,103) HEALTHDATE, DDNUMBER, CONVERT(VARCHAR(12),DDDATE,103)DDDATE,
TSMMED.STATUS_NAME AS MEDICALSTATUS, DOC.DOCTOR_NAME,
CONVERT(VARCHAR(12),MED.CREATEDDATE,103) AS MEDICAL_DATE, CONVERT(VARCHAR(12),TVE.SUBMISSIONDATE,103) VISA_SUBMISSION_DATE ,
TVSS.STATUS_NAME VISA_SUBMISSION_STATUS,TP.Policy POLICY_NO,CONVERT(VARCHAR(12),TP.PolicyDate,103) POLICY_DATE
FROM TBL_USER_DETAILS TUD WITH (NOLOCK)
JOIN TBL_USER_PERSONAL_DETAILS TPD WITH(NOLOCK) ON TUD.REGISTRATION_NO = TPD.REGISTRATION_NO
LEFT JOIN TBL_PASSPORT_DETAILS TPAD WITH (NOLOCK)ON TUD.REGISTRATION_NO = TPAD.REGISTATION_NUMBER
--LEFT JOIN TBL_INDUSTRY_MASTER TIM WITH (NOLOCK)ON TUD.INDUSTRY_ID = TIM.INDUSTRY_ID
--LEFT JOIN TBL_DESIGNATION_MASTER TDM WITH (NOLOCK)ON TUD.DESIGNATION_ID = TDM.DESIGNATION_ID
--LEFT JOIN TBL_ROLE_MASTER TRM WITH (NOLOCK)ON TDM.ROLE_ID = TRM.ROLE_ID
LEFT JOIN TBL_SOURCE_MASTER TSM WITH (NOLOCK) ON TUD.SOURCE_ID = TSM.SOURCE_ID
LEFT JOIN TBL_CITY_MASTER TCM WITH (NOLOCK) ON TUD.LOCATION_CODE = TCM.CITY_CODE
LEFT JOIN TBL_AVAILING_TYPE_MASTER TAM WITH (NOLOCK) ON TUD.AVAILING_TYPE_ID = TAM.AVAILING_TYPE_ID
LEFT JOIN TBL_MOFA TM WITH (NOLOCK) ON TPAD.PASSPORT_ID = TM.PASSPORTID
LEFT JOIN TBL_MEDICAL MED ON TPAD.PASSPORT_ID = MED.PassportID
LEFT JOIN TBL_STATUS_MASTER TSMMED ON MED.MedicalStatus=TSMMED.STATUS_ID
LEFT JOIN TBL_POLICY TP on tpAd.PASSPORT_ID =TP.PassportID
LEFT JOIN
(
SELECT (ISNULL(TUPD.FIRST_NAME,'') +' '+ISNULL(TUPD.LAST_NAME,'')) AS DOCTOR_NAME, TUD.REGISTRATION_NO
FROM TBL_USER_DETAILS TUD WITH(NOLOCK)
JOIN TBL_USER_PERSONAL_DETAILS TUPD WITH (NOLOCK) ON TUD.REGISTRATION_NO = TUPD.REGISTRATION_NO
WHERE USER_TYPE_ID =4
)DOC ON MED.DoctorID = DOC.REGISTRATION_NO
LEFT JOIN TBL_VISA_MASTER TVM ON TUD.REGISTRATION_NO = TVM.REGISTRATION_NUMBER
LEFT JOIN TBL_VISA_ENDORSEMENT TVE ON TPAD.PASSPORT_ID = TVE.PassportId
LEFT JOIN TBL_STATUS_MASTER TVSS ON TVE.SubmissionStatusID = TVSS.STATUS_ID
LEFT JOIN
(
SELECT DISTINCT p.REGISTRATION_NUMBER,
(SELECT SUBSTRING(
(SELECT ',' + TUC.CONTACT_NO
FROM TBL_USER_CONTACTS TUC
WHERE P.REGISTRATION_NUMBER = TUC.REGISTRATION_NUMBER
ORDER BY TUC.USER_CONTACT_ID
FOR XML PATH('')),2,200000)) AS CONTACT_NO
FROM TBL_USER_CONTACTS p WITH (NOLOCK)
)CONTACT ON TUD.REGISTRATION_NO = CONTACT.REGISTRATION_NUMBER
LEFT JOIN
(
SELECT DISTINCT p.REGISTRATION_NUMBER,
(SELECT SUBSTRING(
(SELECT ',' + TEUA.USER_EMAIL
FROM TBL_USER_EMAIL_ADDRESS TEUA
WHERE P.REGISTRATION_NUMBER = TEUA.REGISTRATION_NUMBER
ORDER BY TEUA.USER_EMAIL
FOR XML PATH('')),2,200000)) AS user_email
FROM TBL_USER_EMAIL_ADDRESS p WITH(NOLOCK)
)user_email ON TUD.REGISTRATION_NO = user_email.REGISTRATION_NUMBER
WHERE TUD.REGISTRATION_NO = @REGISTRATION_NO
-- SECTION 2 EDUCATION DETAILS
SELECT EDUCATION_TYPE,SPECIALIZATION_TYPE,TUED.UNIVERSITY_ID AS UNIVERSITY_NAME,REGISTRATION_NO,
UNIVERSITY_YEAR_OF_PASSING FROM TBL_USER_EDUCATION_DETAILS TUED WITH (NOLOCK)