For getting important data date wise SELECT IFNULL(t1.date,t2.date) as date, IFNULL(SentMessages,0) as sentMessage , IFNULL(ReceivedMessages,0) as receivedMessage, IFNULL(BoughtNumbers,0) as BoughtNumbers, IFNULL(ReleasedNumbers,0) as ReleasedNumbers FROM (SELECT DATE(uh.createdDate) as date, SUM(case when STRCMP(`direction`, 'outbound-api') = 0 then 1 else 0 end) as SentMessages, SUM(case when STRCMP(`direction`, 'Inbound') = 0 then 1 else 0 end) as ReceivedMessages FROM `user_history` uh GROUP BY date) t1 LEFT JOIN (SELECT DATE(pn.createdDate) as date, SUM(case when pn.`status` = 1 then 1 else 0 end) as BoughtNumbers, SUM(case when pn.`status` = 9 then 1 else 0 end) as ReleasedNumbers FROM phone_number pn GROUP BY date) t2 ON t1.date=t2.date UNION SELECT IFNULL(t3.date,t4.date) as date, IFNULL(SentMessages,0) as sentMessage , IFNULL(ReceivedMessages,0) as receivedMessage, IFNULL(BoughtNumbers,0) as BoughtNumbers, IFNULL(ReleasedNumbers,0) as ReleasedNumbers FROM (SELECT DATE(uh.createdDate) as date, SUM(case when STRCMP(`direction`, 'outbound-api') = 0 then 1 else 0 end) as SentMessages, SUM(case when STRCMP(`direction`, 'Inbound') = 0 then 1 else 0 end) as ReceivedMessages FROM `user_history` uh GROUP BY date) t3 RIGHT JOIN (SELECT DATE(pn.createdDate) as date, SUM(case when pn.`status` = 1 then 1 else 0 end) as BoughtNumbers, SUM(case when pn.`status` = 9 then 1 else 0 end) as ReleasedNumbers FROM phone_number pn GROUP BY date) t4 ON t3.date=t4.date ORDER BY `date` DESC //22-apr-2020 Added one column inActiveTill (type - datetime) When user's subscription will expire, yearly and monthly subscribers will have grace period of 7days for reserving same number and all histories. So status of number will become - 2 (inactive for 7 days) If in 7 days user resubscribes, then same number will be given to him and status of number become 1(active). //24-apr-2020 added one table master_users ALTER TABLE users add FOREIGN KEY (masterUserId) REFERENCES master_users (id) added one field master_user_id to users table. IOS - 4300 Android - 153 Database - 4424 340 321 83 ALTER TABLE `subscription_receipts` ADD `is_android` BOOLEAN NOT NULL DEFAULT FALSE AFTER `is_expired`; Create master users for each user and map master user id to users table. CREATE VIEW master_user_mapping AS SELECT m.id as masterUserId, GROUP_CONCAT(u.id) as userId from master_users m, users u where u.masterUserId=m.id group by m.id ALTER TABLE `master_users` ADD `email` VARCHAR(255) NULL AFTER `id`, ADD `socialID` VARCHAR(255) NULL AFTER `email`, ADD `loginType` VARCHAR(255) NULL COMMENT ' 1-google,2-apple,3-facebook ' AFTER `socialID`; ALTER TABLE `users` CHANGE `password` `profilePicture` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; ALTER TABLE `users` CHANGE `email` `userName` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; -------------------------------------------------------------------------------------------------------------------------------------------- 23-08-2021 ALTER TABLE `users` ADD `isAnonymousCall` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0:In-Active,1:Active'; ALTER TABLE `user_call` ADD `AnonymousCall` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0:In-Active,1:Active'; -------------------------------------------------------------------------------------------------------------------------------------------- 11-08-2021 Mitesh ALTER TABLE `users` ADD `IsCallRecord` TINYINT(1) NOT NULL DEFAULT '0' +14506001096 851398 https://api.twilio.com/2010-04-01/Accounts/ACe5fc0d21a50f7408c6df10542319c52b/IncomingPhoneNumbers.json?PageSize=5&page=2300 ALTER TABLE `users` ADD `isVerified` TINYINT NOT NULL DEFAULT '0' AFTER `isAnonymousCall`; ALTER TABLE `blocked_users` ADD `isHide` TINYINT(1) NOT NULL DEFAULT '0' AFTER `isMute`; UPDATE phone_number set countryCode=LEFT(phoneNumber,2) UPDATE phone_number set countryCode='+44' where countryCode='+4' k4b5@46HZ6tfkr45cfd If you lose your phone, or don't have access to your verification device, this code is your failsafe to access your account. 1HEK7MxwbKWikDmTt9irHm_8h7qNSwgyotddr4aD 763876986583 2ndphonenumber@gmail.com svByZREzUc998hZ9 AKIAUD4X3VXA54HNZRHP aiUc3b/X6uJOYYbxUNd3AbXOOmrxTZtZbkfYC9EE { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:ListBucket", "s3:GetObject" ], "Resource": [ "arn:aws:s3:::secret-text", "arn:aws:s3:::secret-text/*" ] }, { "Effect": "Allow", "Action": [ "s3:ListBucket", "s3:PutObject", "s3:PutObjectAcl" ], "Resource": [ "arn:aws:s3:::second-phone-number", "arn:aws:s3:::second-phone-number/*" ] } ] } { "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": "s3:ListAllMyBuckets", "Resource": "*" }, { "Sid": "VisualEditor1", "Effect": "Allow", "Action": "s3:*", "Resource": [ "arn:aws:s3:::secret-text", "arn:aws:s3:::secret-text/*" ] } ] } SELECT t1.*, IFNULL(bu.isBlock,0) as isBlock FROM (SELECT id, IF(direction = 'out-bound' and FromPhoneNumber = '+15878483274', toPhoneNumber, fromPhoneNumber) as toPhoneNumber, IF(direction = 'out-bound' and FromPhoneNumber = '+15878483274', direction, 'in-bound') as direction, IFNULL(duration,0) as duration, status, createdDate as startTime, IFNULL(AnonymousCall,0) as AnonymousCall FROM user_call WHERE ( (ToPhoneNumber = '+15878483274' AND isDeletedForToNumber = 0) OR (FromPhoneNumber = '+15878483274' AND isDeletedForFromNumber = 0) ) ) t1 LEFT JOIN blocked_users bu ON(bu.masterUserId = $masterUserId AND bu.phoneNumber = t1.toPhoneNumber) ORDER BY t1.startTime DESC { "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": "s3:ListAllMyBuckets", "Resource": "*" }, { "Sid": "VisualEditor1", "Effect": "Allow", "Action": "s3:*", "Resource": [ "arn:aws:s3:::secret-text", "arn:aws:s3:::secret-text/*" ] } ] } { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:ListBucket", "s3:GetObject" ], "Resource": [ "arn:aws:s3:::second-phone-number", "arn:aws:s3:::second-phone-number/*" ] }, { "Effect": "Allow", "Action": [ "s3:ListBucket", "s3:PutObject", "s3:PutObjectAcl" ], "Resource": [ "arn:aws:s3:::secret-text", "arn:aws:s3:::secret-text/*" ] } ] } { "Version": "2012-10-17", "Id": "Policy1611277539797", "Statement": [ { "Sid": "Stmt1611277535086", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::478416893069:user/download-manager-1175" }, "Action": "s3:PutObject", "Resource": "arn:aws:s3:::1175-download-manager/*", "Condition": { "StringEquals": { "s3:x-amz-acl": "bucket-owner-full-control" } } }, { "Sid": "Stmt1611277877767", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::478416893069:user/download-manager-1175" }, "Action": "s3:ListBucket", "Resource": "arn:aws:s3:::1175-download-manager" } ] } Access key ID - AKIA3DWU5Z3LTPE4R36O Secret access key - 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 ALTER TABLE `user_history` ADD `isFavForFrom` INT NOT NULL DEFAULT '0' AFTER `isChatThreadReadForTo`, ADD `isFavForTo` INT NOT NULL DEFAULT '0' AFTER `isFavForFrom`; [ SELECT ucr.id, uc.parentCallSid, uc.callSid, ucr.phoneNumber, ucr.recording_url, ucr.duration, ucr.timestamp, ucr.recording_name, ucr.createdDate, ucr.modifiedDate, IF(uc.direction = 'out-bound' and uc.FromPhoneNumber = '+12267024548', uc.direction, 'in-bound') as direction, IFNULL(uc.AnonymousCall,0) as AnonymousCall FROM user_call uc LEFT JOIN user_call_recording ucr ON (ucr.user_call_id = uc.id) WHERE ucr.isDeleted = 0 AND ucr.user_id=1075010 AND ( (uc.FromUserId = 1075010 AND uc.fromPhoneNumber = '+12267024548' ) OR (uc.ToUserId = 1075010 AND uc.toPhoneNumber = '+12267024548' ) ) ORDER BY ucr.id DESC ALTER TABLE `phone_number` ADD `transaction_id` VARCHAR(255) NULL AFTER `countryCode`; ALTER TABLE `credit_history` ADD `transaction_id` VARCHAR(255) NULL AFTER `credits`; DELETE FROM user_history WHERE isDeletedForFrom = 9 and fromUserId is NOT null and toUserId is null DELETE FROM user_history WHERE isDeletedForTo = 9 and toUserId is NOT null and fromUserId is null DELETE FROM user_call WHERE isDeletedForFromNumber = 1 AND FromUserId is NOT null and ToUserId is null DELETE FROM user_call WHERE isDeletedForToNumber = 1 AND ToUserId is NOT null and FromUserId is null