· 8 years ago · Oct 10, 2017, 03:58 PM
1<?php
2 $start_time = time();
3
4 $veneno_ip = $argv[1];
5 $veneno_db = 'veneno_data_details';
6
7 $cleanup_table = "voucher";
8
9 $username = $argv[2];
10 $password = $argv[3];
11
12 $meta_db_ip = $argv[4];
13
14 echo "connecting to intouch master shard $veneno_db db..\n";
15 $veneno_db_conn = new mysqli($veneno_ip, $username, $password, $veneno_db);
16
17 if($veneno_db_conn->connect_errno) {
18 printf("Connect to veneno $veneno_ip failed: %s", $mysqli->connect_error);
19 exit();
20 }
21
22 $populate_cd_cleanup_query = "CREATE TABLE IF NOT EXISTS data_cleanup.communication_details_cleanup AS (SELECT id, bucket_id, recipient_list_id as group_id, org_id, campaign_id, SUBSTR( message_properties, LOCATE( 'voucher_series', message_properties ) +17, LOCATE( 'task_id', message_properties )-17 - LOCATE ( 'voucher_series', message_properties ) -3 ) AS voucher_series_id, recieved_time FROM veneno.`communication_details` WHERE target_type = 'GROUPED' AND message_properties LIKE '%voucher_series%' AND recieved_time > '2017-08-03' AND recieved_time < '2017-09-01' GROUP BY id HAVING voucher_series_id >0 )";
23
24 echo "executing query $populate_cd_cleanup_query";
25
26 if(!$veneno_db_conn->query($populate_cd_cleanup_query)) {
27
28 echo ("Error description : ".mysqli_error($veneno_db_conn)."\n");
29
30 }
31
32 echo "successfully run populate query \n";
33
34 $shard_mapping_query = "SELECT T1.org_id, T2.name AS shard FROM shard_manager.shard T2 RIGHT JOIN (SELECT org_id, shard_id FROM shard_manager.org_shard_mapping WHERE policy_id = (SELECT id FROM shard_manager.shard_policy WHERE name = 'INTOUCH_SHARDING_POLICY') AND is_active = 1) T1 ON T1.shard_id = T2.id";
35
36 echo "shard mapping query sql $shard_mapping_query \n";
37
38 $meta_db_conn = new mysqli($meta_db_ip, $username, $password);
39
40 if(!$meta_db_conn->query($shard_mapping_query)) {
41
42 echo ("Error description : ".mysqli_error($meta_db_conn)."\n");
43
44 }
45
46 echo "successfully run shard_mapping_query \n";
47
48 $shard_mapping_result = $meta_db_conn->query($shard_mapping_query);
49
50 echo "result: ".print_r($shard_mapping_result, true);
51
52 $shard_org_mapping = array();
53
54 while($i = mysqli_fetch_assoc($shard_mapping_result)) {
55 if(!$shard_org_mapping[$i['shard']]) array_push($shard_org_mapping, array($i['shard'], $i['org_id']));
56 else array_push($shard_org_mapping[$i['shard']], $i['org_id']);
57
58 }
59
60 $vcu_table_structure = " (`message_id` int(11) NOT NULL,
61 `org_id` int(11) NOT NULL,
62 `campaign_id` int(11) NOT NULL,
63 `group_id` int(11) NOT NULL,
64 `user_id` int(11) NOT NULL,
65 `voucher_series_id` int(11) NOT NULL,
66 `voucher_code` varchar(20) NOT NULL,
67 `inbox_time` datetime NOT NULL,
68 `inbox_date` date NOT NULL,
69 KEY `campaign_id` (`org_id`,`campaign_id`,`group_id`,`user_id`),
70 KEY `user_id` (`user_id`,`voucher_series_id`,`inbox_date`)
71 ) ENGINE=InnoDB ";
72
73
74 echo "Creating voucher_campaign_users \n";
75
76 echo "create table sql in shard $veneno_ip $vcu_table_structure \n";
77
78 $table_name = 'data_cleanup.voucher_campaign_users';
79
80 echo "table name $table_name \n";
81
82 echo "number of shards ".sizeof($shard_org_mapping)."\n";
83
84 for ($shard=1; $shard <= sizeof($shard_org_mapping) - 1; $shard++) {
85
86 $sharded_vcu_table_name = $table_name."_".$shard;
87
88 echo "sharded_vcu_table_name $sharded_vcu_table_name\n";
89
90 $create_table_query_for_shard = "CREATE TABLE IF NOT EXISTS ".$sharded_vcu_table_name.$vcu_table_structure;
91
92 echo "create table sql $create_table_query_for_shard\n";
93
94 if(!$veneno_db_conn->query($create_table_query_for_shard)) {
95
96 echo("Error description: " . mysqli_error($veneno_db_conn)."\n");
97
98 }
99
100 echo "Created voucher_campaign_users table structure in shard $shard....\n";
101
102 // echo "orgs of org_shard_mapping ".print_r($shard_org_mapping[$shard], true)."\n";
103
104 foreach ($shard_org_mapping[$shard] as $index => $org_id) {
105
106 echo "executing for org_id $org_id\n";
107
108 $voucher_campaings_sql = "select * from data_cleanup.communication_details_cleanup WHERE org_id = ".$org_id;
109
110 echo "voucher_campaings_sql $voucher_campaings_sql\n";
111
112 $select_result = $veneno_db_conn->query($voucher_campaings_sql);
113
114 $num_rows = $select_result->num_rows;
115
116 echo "no of rows is $num_rows \n";
117
118 if($num_rows == 0)
119 {
120 echo "no campaigns for org $org_id .. continuing !!\n";
121 continue;
122 }
123
124 while($i = mysqli_fetch_assoc($select_result))
125 {
126
127 $id = $i['id'];
128 $bucket_id = $i['bucket_id'];
129 $group_id = $i['group_id'];
130 $campaign_id = $i['campaign_id'];
131 $voucher_series_id = $i['voucher_series_id'];
132 $recieved_time = "'".$i['recieved_time']."'";
133
134 $inbox_sql = "INSERT INTO data_cleanup.voucher_campaign_users_".$shard."(select $id, $org_id,$campaign_id,$group_id,recipient_id,$voucher_series_id,SUBSTR(resolved_tags, LOCATE('voucher', resolved_tags)+ 10 , 8), created_time, date(created_time) as created_date from veneno_data_details.inboxes_" . $bucket_id . " WHERE org_id = ".$org_id. " AND message_id = ".$id.")";
135
136 echo "insert recipients $inbox_sql \n";
137
138 if (!$veneno_db_conn->query($inbox_sql))
139 {
140 echo("Error description: " . mysqli_error($veneno_db_conn)."\n");
141 }
142
143 }
144
145 }
146
147 }
148
149 echo "DONE\n";
150 $end_time = time();
151 echo "\n". date('Y-m-d H:i:s'). "\n";
152 echo "\ntime taken: ". ($end_time - $start_time)."secs\n";
153
154?>