Skip to content

Instantly share code, notes, and snippets.

@manjeshpv
Created June 30, 2017 12:03
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save manjeshpv/89bcf171ccccda6a393c892e2a27a7a2 to your computer and use it in GitHub Desktop.
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL,
`parent_id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`type` varchar(20) DEFAULT NULL
);
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
INSERT INTO `users` (`id`, `parent_id`, `name`, `type`) VALUES
(1, NULL, 'Head', 'reseller'),
(2, 1, 'Reseller1', 'reseller'),
(3, 1, 'Reseller2', 'reseller'),
(4, 2, 'EndUser1', 'user'),
(5, 2, 'EndUser2', 'user'),
(6, 3, 'EndUser3', 'user'),
(7, 3, 'EndUser4', 'user');
CREATE TABLE IF NOT EXISTS `credit` (
`id` int(11) NOT NULL,
`sold_by` int(11) DEFAULT NULL COMMENT 'parent_uid',
`sold_to` int(11) DEFAULT NULL,
`credit` int(11) DEFAULT NULL
);
ALTER TABLE `credit`
ADD PRIMARY KEY (`id`);
ALTER TABLE `credit`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
CREATE TABLE IF NOT EXISTS `billing` (
`id` int(11) NOT NULL,
`sent_by` int(11) NOT NULL,
`parent_id` int(11) NOT NULL,
`credit` int(11) NOT NULL
);
ALTER TABLE `billing`
ADD PRIMARY KEY (`id`);
ALTER TABLE `billing`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
-- adding credits for head 1000(max messages in whole ),
INSERT INTO `credit` (`id`, `sold_by`, `sold_to`, `credit`) VALUES
(1, NULL, 1, 1000),
(2, 1, 2, 2000),
(3, 1, 3, 5000),
(4, 2, 4, 2000),
(5, 2, 5, 2000),
(6, 2, 4, 8000);
-- SMS sent by EndUser1
INSERT INTO `billing` (`id`, `sent_by`, `parent_id`, `credit`) VALUES
(1, 4, 2, 500);
-- trying get full details
SELECT
T2.id,
T2.name,
(select sum(credit) from credit where sold_to= T2.id) as credits,
(select sum(credit) from billing where sent_by= T2.id) as my_billing,
(select sum(credit) as child_billing from billing where sent_by in (select Level FROM (
SELECT @Ids := (
SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
FROM `users`
WHERE FIND_IN_SET(`parent_id`, @Ids)
) Level
FROM `users`
JOIN (SELECT @Ids := T2.id) temp1
WHERE FIND_IN_SET(`parent_id`, @Ids)
) as x)) as child_billing
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM users WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 4, @l := 0) vars,
users m
WHERE @r <> 0) T1
JOIN users T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment