· 9 years ago · Dec 14, 2016, 09:04 PM
1CREATE TABLE `transaction` (
2 `id` int(11) NOT NULL AUTO_INCREMENT,
3 `file_id` int(11) DEFAULT NULL,
4 `countid` int(11) NOT NULL,
5 `txn_date` datetime NOT NULL,
6 `txn_id` int(11) NOT NULL,
7 `user_id` int(11) NOT NULL,
8 `user_rmn` bigint(20) NOT NULL,
9 `customer_no` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
10 `aggregator_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
11 `trans_amount` decimal(15,4) NOT NULL,
12 `incoming_commission` decimal(15,4) NOT NULL,
13 `mmplt_txn_id` int(11) NOT NULL,
14 `product_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
15 `txn_category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
16 `circle` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
17 `status` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
18 `role` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
19 `number` int(11) DEFAULT NULL,
20 `user_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
21 `city_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
22 `state_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
23 `retailer_commission` decimal(15,4) NOT NULL,
24 `total_commission` decimal(15,4) NOT NULL,
25 `net_revenue` decimal(15,4) NOT NULL,
26 `ad_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
27 `ad_commission` decimal(15,4) DEFAULT NULL,
28 `md_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
29 `md_commission` decimal(15,4) DEFAULT NULL,
30 `cnf_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
31 `cnf_commission` decimal(15,4) DEFAULT NULL,
32 `ad_id` bigint(20) DEFAULT NULL,
33 `md_id` bigint(20) DEFAULT NULL,
34 `cnf_id` bigint(20) DEFAULT NULL,
35 `operator_id` int(11) NOT NULL,
36 PRIMARY KEY (`id`),
37 UNIQUE KEY `txnId` (`txn_id`),
38 KEY `IDX_723705D193CB796C` (`file_id`),
39 KEY `date_idx` (`txn_date`),
40 KEY `user_idx` (`user_id`),
41 KEY `cnf_idx` (`cnf_id`),
42 KEY `md_idx` (`md_id`),
43 KEY `ad_idx` (`ad_id`),
44 KEY `user_rmn_idx` (`user_rmn`),
45 KEY `trans_amount_idx` (`trans_amount`),
46 KEY `incoming_commission_idx` (`incoming_commission`),
47 KEY `retailer_commission_idx` (`retailer_commission`),
48 KEY `ad_commission_idx` (`ad_commission`),
49 KEY `md_commission_idx` (`md_commission`),
50 KEY `cnf_commission_idx` (`cnf_commission`),
51 KEY `cnf_date_idx` (`txn_date`,`cnf_id`),
52 KEY `md_date_idx` (`txn_date`,`md_id`),
53 KEY `ad_date_idx` (`txn_date`,`ad_id`),
54 KEY `user_rmn_date_idx` (`txn_date`,`user_rmn`),
55 CONSTRAINT `FK_723705D193CB796C` FOREIGN KEY (`file_id`) REFERENCES `file_to_sync` (`id`)
56) ENGINE=InnoDB AUTO_INCREMENT=11370410 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
57
58CREATE TABLE `operator` (
59 `id` int(11) NOT NULL AUTO_INCREMENT,
60 `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
61 `category_low_id` int(11) DEFAULT NULL,
62 `category_medium_id` int(11) DEFAULT NULL,
63 `category_high_id` int(11) DEFAULT NULL,
64 PRIMARY KEY (`id`),
65 KEY `IDX_D7A6A781B596C062` (`category_low_id`),
66 KEY `IDX_D7A6A78125326495` (`category_medium_id`),
67 KEY `IDX_D7A6A7818196AB83` (`category_high_id`),
68 CONSTRAINT `FK_D7A6A78125326495` FOREIGN KEY (`category_medium_id`) REFERENCES `operator_category_medium` (`id`),
69 CONSTRAINT `FK_D7A6A7818196AB83` FOREIGN KEY (`category_high_id`) REFERENCES `operator_category_high` (`id`),
70 CONSTRAINT `FK_D7A6A781B596C062` FOREIGN KEY (`category_low_id`) REFERENCES `operator_category_low` (`id`)
71) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
72
73CREATE TABLE `operator` (
74 `id` int(11) NOT NULL AUTO_INCREMENT,
75 `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
76 `category_low_id` int(11) DEFAULT NULL,
77 `category_medium_id` int(11) DEFAULT NULL,
78 `category_high_id` int(11) DEFAULT NULL,
79 PRIMARY KEY (`id`),
80 KEY `IDX_D7A6A781B596C062` (`category_low_id`),
81 KEY `IDX_D7A6A78125326495` (`category_medium_id`),
82 KEY `IDX_D7A6A7818196AB83` (`category_high_id`),
83 CONSTRAINT `FK_D7A6A78125326495` FOREIGN KEY (`category_medium_id`) REFERENCES `operator_category_medium` (`id`),
84 CONSTRAINT `FK_D7A6A7818196AB83` FOREIGN KEY (`category_high_id`) REFERENCES `operator_category_high` (`id`),
85 CONSTRAINT `FK_D7A6A781B596C062` FOREIGN KEY (`category_low_id`) REFERENCES `operator_category_low` (`id`)
86) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
87
88
89CREATE TABLE `operator_category_medium` (
90 `id` int(11) NOT NULL AUTO_INCREMENT,
91 `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
92 PRIMARY KEY (`id`)
93) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
94
95
96CREATE TABLE `operator_category_high` (
97 `id` int(11) NOT NULL AUTO_INCREMENT,
98 `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
99 PRIMARY KEY (`id`)
100) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
101
102
103CREATE TABLE `depositor` (
104 `id` int(11) NOT NULL AUTO_INCREMENT,
105 `depositor_id` bigint(20) NOT NULL,
106 `name` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
107 `amount` decimal(15,4) NOT NULL,
108 `deposited` datetime NOT NULL,
109 `details` longtext COLLATE utf8_unicode_ci NOT NULL,
110 `netsuite_id` int(11) NOT NULL,
111 PRIMARY KEY (`id`),
112 KEY `depositor_idx` (`depositor_id`),
113 KEY `netsuite_id_idx` (`netsuite_id`),
114 KEY `deposited_idx` (`deposited`)
115) ENGINE=InnoDB AUTO_INCREMENT=62650 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
116
117DROP temporary TABLE IF EXISTS `depositor_type`;
118
119CREATE temporary TABLE `depositor_type` (
120 `depositor_id` bigint(20) NOT NULL,
121 `type` varchar(4) NOT NULL,
122 amount decimal(20,4) NULL,
123 count_deposit int(11) NULL,
124 PRIMARY KEY (depositor_id, type),
125 KEY type_idx (type),
126 KEY amount_idx (amount)
127) ENGINE=InnoDB DEFAULT CHARSET=latin1;
128
129insert into depositor_type (type, depositor_id ) select distinct 'u',user_rmn as depositor_id from transaction where txn_date between :from and :to and user_rmn is not null union DISTINCT select distinct 'a',ad_id as depositor_id from transaction where txn_date between :from and :to and ad_id is not null union DISTINCT select distinct 'm',md_id as depositor_id from transaction where txn_date between :from and :to and md_id is not null union DISTINCT select distinct 'c',cnf_id as depositor_id from transaction where txn_date between :from and :to and cnf_id is not null;
130
131update depositor_type set amount=(select sum(amount) from depositor d where d.depositor_id=depositor_type.depositor_id), count_deposit=(select count(amount) from depositor d where d.depositor_id=depositor_type.depositor_id) ;
132
133DROP TABLE IF EXISTS `9bf92fsums`;
134CREATE TABLE `9bf92fsums` ( `cnf_id` bigint(20) NOT NULL,
135 `md_id` bigint(20) NOT NULL,
136 `ad_id` bigint(20) NOT NULL,
137 `user_rmn` bigint(20) NOT NULL,
138 `operator_id` int(11) not null,
139 `trans_amount` decimal(20,4) NOT NULL,
140 `incoming_commission` decimal(20,4) NOT NULL,
141 `retailer_commission` decimal(20,4) NOT NULL,
142 `ad_commission` decimal(20,4) NOT NULL,
143 `md_commission` decimal(20,4) NOT NULL,
144 `cnf_commission` decimal(20,4) NOT NULL,
145 `count_trans` int(11) not null,
146
147 PRIMARY KEY (`cnf_id`,`md_id`,`ad_id`,`user_rmn`, `operator_id`),
148 KEY `md_id_idx` (`cnf_id`),
149 KEY `ad_id_idx` (`ad_id`) USING BTREE,
150 KEY `user_rmn_idx` (`user_rmn`) USING BTREE,
151 KEY `operator_id_idx` (`operator_id`) USING BTREE
152) ENGINE=InnoDB DEFAULT CHARSET=latin1;
153
154insert into 9bf92fsums select distinct coalesce(cnf_id,0), coalesce(md_id,0), coalesce(ad_id,0), coalesce(user_rmn,0), operator_id, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), count(txn_id) as count_trans from transaction where txn_date between :from and :to group by coalesce(cnf_id,0), coalesce(md_id,0), coalesce(ad_id,0), coalesce(user_rmn,0), operator_id
155
156select 'User' as type, user_rmn as phone, t.amount, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s inner join depositor_type t on s.user_rmn=t.depositor_id and t.type='u' inner join operator o on s.operator_id=o.id left join operator_category_high och on (o.category_high_id=och.id) left join operator_category_medium ocm on (o.category_medium_id=ocm.id) left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0 group by user_rmn, och.name, ocm.name, ocl.name
157
158select 'AD' as type, ad_id as phone, t.amount, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s inner join depositor_type t on s.ad_id=t.depositor_id and t.type='a' inner join operator o on s.operator_id=o.id left join operator_category_high och on (o.category_high_id=och.id) left join operator_category_medium ocm on (o.category_medium_id=ocm.id) left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0 group by ad_id, och.name, ocm.name, ocl.name
159
160select 'MD' as type, md_id as phone, t.amount, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s inner join depositor_type t on s.md_id=t.depositor_id and t.type='m' inner join operator o on s.operator_id=o.id left join operator_category_high och on (o.category_high_id=och.id) left join operator_category_medium ocm on (o.category_medium_id=ocm.id) left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0 group by md_id, och.name, ocm.name, ocl.name
161
162select 'CNF' as type, cnf_id as phone, t.amount, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s inner join depositor_type t on s.cnf_id=t.depositor_id and t.type='c' inner join operator o on s.operator_id=o.id left join operator_category_high och on (o.category_high_id=och.id) left join operator_category_medium ocm on (o.category_medium_id=ocm.id) left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0 group by cnf_id, och.name, ocm.name, ocl.name
163
164select distinct 'no deposits' as type, null as depositor_id, 0 as sum_dep_amount, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), 0 as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s inner join operator o on s.operator_id=o.id left join operator_category_high och on (o.category_high_id=och.id) left join operator_category_medium ocm on (o.category_medium_id=ocm.id) left join operator_category_low ocl on (o.category_low_id=ocl.id) group by 'no deposits', och.name, ocm.name, ocl.name
165
166select distinct 'no transactions' as type, depositor_id, sum(amount) as sum_dep_amount, 0 as trans_amount, 0 as incoming_commission, 0 as retailer_commission, 0 as ad_commission, 0 as md_commission, 0 as cnf_commission, count(d.id) as cnt_depositors, 0 as cnt_txn_id, 'n/a' as operator_category_high, 'n/a' as operator_category_medium, 'n/a' as operator_category_low from depositor d where depositor_id not in (select user_rmn from 9bf92fsums) and depositor_id not in (select ad_id from 9bf92fsums) and depositor_id not in (select md_id from 9bf92fsums) and depositor_id not in (select cnf_id from 9bf92fsums) group by 'no transactions', depositor_id