/reseller.sql Secret
Created
June 30, 2017 12:03
Star
You must be signed in to star a gist
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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