Subversion Repositories SmartDukaan

import time
import datetime

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, 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 where p.notification_campaign_id in (select id from notification_campaigns where TIMESTAMPDIFF(HOUR, created, NOW())<=2) and p.type='sent' and p.message = 'success' 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 where p.notification_campaign_id in (select id from notification_campaigns where TIMESTAMPDIFF(HOUR, created, NOW())<=2) 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 =1"

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 = {}

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
            

print campaignUsersMap

for campaignId, users in campaignUsersMap.iteritems():
    name = campaignNamesMap.get(campaignId)+"- copy"
    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, expiresat, status, created from notification_campaigns where id = %d"%(name,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.datetime.now()
        session.commit()
        
db.close()
if session.is_active:
    print "session is active. closing it."
    session.close()