AHDSystem/SQLQuery5.sql at master · islomkhon/AHDSystem · GitHub

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
--CREATE TABLE [NueRequestType]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--    [RequestType] VARCHAR(250),
--    [AddedOn] DATE NOT NULL DEFAULT GETDATE()
--)

--CREATE TABLE [NueRequestSubType]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--    [RequestSubType] VARCHAR(250),
--    [RequestType] INT FOREIGN KEY REFERENCES NueRequestType(Id),
--    [AddedOn] DATE NOT NULL DEFAULT GETDATE()
--)

--CREATE TABLE [NueRequestStatus]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--    [RequestStatus] VARCHAR(350),
--    [AddedOn] DATE NOT NULL DEFAULT GETDATE()
--)

--CREATE TABLE [NueUserOrgMapper]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--    [UserId] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--	[OrgUserId] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--	[OrgUserType] INT FOREIGN KEY REFERENCES Designation(Id),
--    [AddedOn] DATE NOT NULL DEFAULT GETDATE(),
--)


--CREATE TABLE [NueRequestMaster]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--    [RequestId] VARCHAR(350),
--	[IsApprovalProcess] INT DEFAULT 0,
--	[CreatedBy] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--	[RequestStatus] INT FOREIGN KEY REFERENCES NueRequestStatus(Id),
--	[PayloadId] INT,
--    [RequestCatType] INT FOREIGN KEY REFERENCES NueRequestSubType(Id),
--    [AddedOn] DATE NOT NULL DEFAULT GETDATE(),
--  [ModifiedOn] DATE NOT NULL DEFAULT GETDATE()
--)

--CREATE TABLE [NueRequestAceessLog]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--    [RequestId] INT FOREIGN KEY REFERENCES NueRequestMaster(Id),
--	[UserId] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--	[Completed] INT DEFAULT 0,
--	[OwnerId] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--    [AddedOn] DATE NOT NULL DEFAULT GETDATE(),
--	[ModifiedOn] DATE NOT NULL DEFAULT GETDATE()
--)

--CREATE TABLE [NueLeaveCancelationRequest]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--    [RequestId] VARCHAR(350),
--	[UserId] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--	[StartDate] VARCHAR(250),
--	[EndDate] VARCHAR(250),
--	[Message] TEXT,
--  [AddedOn] DATE NOT NULL DEFAULT GETDATE(),
--	[ModifiedOn] DATE NOT NULL DEFAULT GETDATE()
--)

--TRUNCATE TABLE NueRequestAceessLog;
--TRUNCATE TABLE NueLeaveCancelationRequest;
--TRUNCATE TABLE NueRequestMaster;

--CREATE TABLE [NueManagerMapper]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--	[UserId] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--	[ManagerId] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--	[IsConsultentManager] INT DEFAULT 0,
--    [AddedOn] DATE NOT NULL DEFAULT GETDATE(),
--	[ModifiedOn] DATE NOT NULL DEFAULT GETDATE()
--)


--CREATE TABLE [NueRequestActivityMaster]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--    [ActivityDesc] VARCHAR(350),
--    [AddedOn] DATE NOT NULL DEFAULT GETDATE()
--)

--CREATE TABLE [NueRequestActivity]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--	[Payload] TEXT,
--	[PayloadType] INT FOREIGN KEY REFERENCES NueRequestActivityMaster(Id),
--	[UserId] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--	[RequestId] INT FOREIGN KEY REFERENCES NueRequestMaster(Id),
--	[Request] varchar(350),
--    [AddedOn] DATE NOT NULL DEFAULT GETDATE(),
--	[ModifiedOn] DATE NOT NULL DEFAULT GETDATE()
--)

--CREATE TABLE [NueRequestAttachmentLog]
--(
--    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
--	[RequestId] INT FOREIGN KEY REFERENCES NueRequestMaster(Id),
--	[Request] varchar(350),
--	[UserId] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--	[OwnerId] INT FOREIGN KEY REFERENCES NueUserProfile(Id),
--	[FileName] varchar(1050),
--	[FileExt] varchar(350),
--	[VFileName] varchar(2050),
--    [AddedOn] datetime,
--	[ModifiedOn] datetime
--)

select nrm.Id as NueRequestMasterId, nrm.RequestId as RequestId, nrst.Id as NueRequestSubTypeId, nrst.RequestSubType as RequestSubType,
nrs.Id as NueRequestStatusId, nrs.RequestStatus as RequestStatus, nrm.AddedOn as AddedOn, nrm.ModifiedOn as ModifiedOn from
NueRequestMaster nrm
join NueRequestAceessLog nral on nrm.Id = nral.RequestId
join NueRequestSubType nrst on nrm.RequestCatType = nrst.id
join NueRequestType nrt on nrst.RequestType = nrt.Id
join NueRequestStatus nrs on nrm.RequestStatus = nrs.Id
where nral.Completed = 0
and nral.UserId = 1
and nral.OwnerId=1
and nrt.RequestType='HCM'