DROP TABLE IF EXISTS `systemuser`;
CREATE TABLE `systemuser` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `UserName` varchar(50) NOT NULL,
  `UserPassword` varchar(100) NOT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `FirstName` varchar(100) DEFAULT NULL,
  `LastName` varchar(100) DEFAULT NULL,
  `UserRegistrationNo` varchar(20) DEFAULT NULL,
  `Phone` varchar(20) DEFAULT NULL,
  `ParentUserID` int(11) DEFAULT NULL,
  `VehicleLimit` int(11) DEFAULT NULL,
  `Status` bit(1) DEFAULT NULL,
  `RuleID` int(11) DEFAULT NULL,
  `IsDeleted` bit(1) NOT NULL,
  `CreatedDate` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `CreatedUserID` int(11) NOT NULL,
  `ModifiedDate` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `ModifiedUserID` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `fk_User_Rule` (`RuleID`),
  KEY `fk_User_ParentUser` (`ParentUserID`),
  CONSTRAINT `fk_User_Rule` FOREIGN KEY (`RuleID`) REFERENCES `rule` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of systemuser
-- ----------------------------
INSERT 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);
INSERT 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);
INSERT 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);
INSERT 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);
INSERT 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);
INSERT 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);
INSERT 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);
INSERT 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);
INSERT 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);
INSERT 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);
INSERT 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);

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_RecursiveUser]
(
    @ID INT
)
RETURNS TABLE
AS
RETURN
(
    WITH boss (ID,ParentUserID,Username) AS
(SELECT ID, ParentUserID,Username FROM dbo.SystemUser
WHERE ParentUserID = @ID ),
bossChild (ID,ParentID,Username) AS
(
    SELECT ID, ParentUserID,Username FROM boss
   UNION ALL
    SELECT  u.ID, u.ParentUserID,u.Username FROM dbo.SystemUser u
    INNER JOIN bossChild b ON u.ParentUserID = b.ID
    WHERE u.ID NOT IN (SELECT ParentID FROM boss)
)
SELECT * FROM  bossChild
union
SELECT ID, ParentUserID,Username FROM dbo.SystemUser
 WHERE ID = @ID
)