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()