Subversion Repositories SmartDukaan

import time
from datetime import datetime, timedelta

import MySQLdb
from dtr.storage import DataService
from dtr.storage.DataService import Pushnotifications
from elixir import *
from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound
from sqlalchemy.sql import func
from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, \
    between

PUSH_NOTIFICATIONS_RETRY_SQL ="select  x.user_id, x.id, x.created, x.notification_campaign_id, x.name, TIMESTAMPDIFF(HOUR, x.created, x.expiresat) tdiff, x.sent-IF (y.received is null, 0, y.received) failureCount, x.last_active, x.expiresat from (select p.id,p.user_id, p.created, p.notification_campaign_id, n.name, count(*) sent, ua.last_active, n.expiresat from pushnotifications p join notification_campaigns n on p.notification_campaign_id = n.id join useractive ua on p.user_id = ua.user_id join (select * from (select * from devices order by id desc) dev group by user_id) d on p.user_id = d.user_id where p.notification_campaign_id in (select id from notification_campaigns where TIMESTAMPDIFF(HOUR, created, NOW())<=2 and (name not like 'Batch Credit%' and name not like '%copy%')) and p.type='sent' and p.message = 'success' and d.versioncode >=14 and ua.last_active >= p.created and ua.last_active <= n.expiresat group by p.id) as x left join (select p.id, p.user_id, p.created, p.notification_campaign_id, n.name, count(*) received, ua.last_active, n.expiresat from pushnotifications p join notification_campaigns n on p.notification_campaign_id = n.id join useractive ua on p.user_id = ua.user_id join (select * from (select * from devices order by id desc) dev group by user_id) d on p.user_id = d.user_id where p.notification_campaign_id in (select id from notification_campaigns where TIMESTAMPDIFF(HOUR, created, NOW())<=2 and (name not like 'Batch Credit%' and name not like '%copy%')) and d.versioncode >=14 and p.type='recieved' group by p.id) as y on (x.user_id = y.user_id and x.notification_campaign_id = y.notification_campaign_id) having failureCount =2"

db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
cursor = db.cursor()
DataService.initialize(db_hostname="localhost")

cursor.execute(PUSH_NOTIFICATIONS_RETRY_SQL)
retry_records_data = cursor.fetchall()

campaignUsersMap = {}
campaignNamesMap = {}
campaignTimeDiffMap = {}

if retry_records_data:
    for record in retry_records_data:
        if campaignUsersMap.has_key(long(record[3])):
            users = campaignUsersMap.get(long(record[3]))
            users.append(long(record[0]))
            campaignUsersMap[long(record[3])] = users
        else:
            users = []
            users.append(long(record[0]))
            campaignUsersMap[long(record[3])] = users
            
        if not campaignNamesMap.has_key(long(record[3])):
            name = str(record[4])
            campaignNamesMap[long(record[3])] = name
        
        if not campaignTimeDiffMap.has_key(long(record[3])):
            diff = long(record[5])
            campaignTimeDiffMap[long(record[3])] = diff

print campaignUsersMap

for campaignId, users in campaignUsersMap.iteritems():
    name = campaignNamesMap.get(campaignId)+"- copy"
    created = datetime.now()
    expiresat = created+timedelta(hours = campaignTimeDiffMap.get(campaignId))
    print created, expiresat
    campaignInsertSql = "insert into notification_campaigns(name, title, message, type, url, notification_campaigns.sql, expiresat, status, created) select '%s', title, message, type, url, notification_campaigns.sql, '%s', status, '%s' from notification_campaigns where id = %d"%(name, expiresat.strftime('%Y-%m-%d %H:%M:%S'), created.strftime('%Y-%m-%d %H:%M:%S'), campaignId)
    print campaignInsertSql
    cursor.execute(campaignInsertSql)
    db.commit()
    insertedCampaignId = cursor.lastrowid
    
    for user in users:
        pushnotification = Pushnotifications()
        pushnotification.user_id = user
        pushnotification.notification_campaign_id = insertedCampaignId
        pushnotification.type = 'pending'
        pushnotification.status = False
        pushnotification.created = datetime.now()
        session.commit()
        
db.close()
if session.is_active:
    print "session is active. closing it."
    session.close()