· 10 years ago · May 15, 2016, 10:30 AM
1DROP TABLE IF EXISTS `systemuser`;
2CREATE TABLE `systemuser` (
3 `Id` int(11) NOT NULL AUTO_INCREMENT,
4 `UserName` varchar(50) NOT NULL,
5 `UserPassword` varchar(100) NOT NULL,
6 `Email` varchar(100) DEFAULT NULL,
7 `FirstName` varchar(100) DEFAULT NULL,
8 `LastName` varchar(100) DEFAULT NULL,
9 `UserRegistrationNo` varchar(20) DEFAULT NULL,
10 `Phone` varchar(20) DEFAULT NULL,
11 `ParentUserID` int(11) DEFAULT NULL,
12 `VehicleLimit` int(11) DEFAULT NULL,
13 `Status` bit(1) DEFAULT NULL,
14 `RuleID` int(11) DEFAULT NULL,
15 `IsDeleted` bit(1) NOT NULL,
16 `CreatedDate` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
17 `CreatedUserID` int(11) NOT NULL,
18 `ModifiedDate` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
19 `ModifiedUserID` int(11) DEFAULT NULL,
20 PRIMARY KEY (`Id`),
21 KEY `fk_User_Rule` (`RuleID`),
22 KEY `fk_User_ParentUser` (`ParentUserID`),
23 CONSTRAINT `fk_User_Rule` FOREIGN KEY (`RuleID`) REFERENCES `rule` (`Id`)
24) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
25
26-- ----------------------------
27-- Records of systemuser
28-- ----------------------------
29INSERT INTO `systemuser` VALUES ('1', 'systemadmin', '1', 'mail@mail.com', '20', 'Admin', '11111', '111111', null, '5', '', null, ' ', '2016-05-15 09:08:43', '1', '2016-05-15 09:08:43', null);
30INSERT INTO `systemuser` VALUES ('2', 'systemadmin', '1', 'mail@mail.com', 'System', 'Admin', '11111', '111111', '1', '5', '', null, ' ', '2016-05-15 09:09:01', '1', '2016-05-15 09:09:01', null);
31INSERT INTO `systemuser` VALUES ('3', 'systemadmin', '1', 'mail@mail.com', '3', 'Admin', '11111', '111111', '2', '5', '', null, ' ', '2016-05-15 12:29:50', '1', '2016-05-15 12:29:50', null);
32INSERT INTO `systemuser` VALUES ('4', 'systemadmin', '1', 'mail@mail.com', '4', 'Admin', '11111', '111111', '3', '5', '', null, ' ', '2016-05-15 09:09:31', '1', '2016-05-15 09:09:31', null);
33INSERT INTO `systemuser` VALUES ('13', 'systemadmin', '1', 'mail@mail.com', '5', 'Admin', '11111', '111111', '3', '5', '', null, ' ', '2016-05-15 12:29:09', '1', '2016-05-15 12:29:09', null);
34INSERT INTO `systemuser` VALUES ('14', 'systemadmin', '1', 'mail@mail.com', '6', 'Admin', '11111', '111111', '1', '5', '', null, ' ', '2016-05-15 12:30:01', '1', '2016-05-15 12:30:01', null);
35INSERT INTO `systemuser` VALUES ('15', 'systemadmin', '1', 'mail@mail.com', '7', 'Admin', '11111', '111111', '2', '5', '', null, ' ', '2016-05-15 12:29:15', '1', '2016-05-15 12:29:15', null);
36INSERT INTO `systemuser` VALUES ('16', 'systemadmin', '1', 'mail@mail.com', '8', 'Admin', '11111', '111111', '6', '5', '', null, ' ', '2016-05-15 12:30:10', '1', '2016-05-15 12:30:10', null);
37INSERT INTO `systemuser` VALUES ('19', 'systemadmin', '1', 'mail@mail.com', '9', 'Admin', '11111', '111111', '4', '5', '', null, ' ', '2016-05-15 12:29:23', '1', '2016-05-15 12:29:23', null);
38INSERT INTO `systemuser` VALUES ('20', 'systemadmin', '1', 'mail@mail.com', '10', 'Admin', '11111', '111111', '2', '5', '', null, ' ', '2016-05-15 12:30:14', '1', '2016-05-15 12:30:14', null);
39INSERT INTO `systemuser` VALUES ('24', 'systemadmin', '1', 'mail@mail.com', '11', 'Admin', '11111', '111111', '4', '5', '', null, ' ', '2016-05-15 12:30:18', '1', '2016-05-15 12:30:18', null);
40
41SET ANSI_NULLS ON
42GO
43SET QUOTED_IDENTIFIER ON
44GO
45ALTER FUNCTION [dbo].[fn_RecursiveUser]
46(
47 @ID INT
48)
49RETURNS TABLE
50AS
51RETURN
52(
53 WITH boss (ID,ParentUserID,Username) AS
54(SELECT ID, ParentUserID,Username FROM dbo.SystemUser
55WHERE ParentUserID = @ID ),
56bossChild (ID,ParentID,Username) AS
57(
58 SELECT ID, ParentUserID,Username FROM boss
59 UNION ALL
60 SELECT u.ID, u.ParentUserID,u.Username FROM dbo.SystemUser u
61 INNER JOIN bossChild b ON u.ParentUserID = b.ID
62 WHERE u.ID NOT IN (SELECT ParentID FROM boss)
63)
64SELECT * FROM bossChild
65union
66SELECT ID, ParentUserID,Username FROM dbo.SystemUser
67 WHERE ID = @ID
68)