Subversion Repositories SmartDukaan

import os
import re
import smtplib
import csv
import MySQLdb
from dtr.storage import DataService
from dtr.storage.DataService import Users
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, in_
    
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
cursor = db.cursor()

DataService.initialize(db_hostname="localhost") 
        
def main():
    sql = "select GROUP_CONCAT( distinct x.user_ids ) user_ids, x.mobileNos, GROUP_CONCAT( distinct x.imeis) imeis, GROUP_CONCAT(distinct x.referrers) referrers from (SELECT GROUP_CONCAT( distinct u.id ) user_ids, GROUP_CONCAT(distinct u.mobile_number) mobileNos, d.imeinumber imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM  `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is not null and u.mobile_number is not null GROUP BY d.imeinumber) as x group by x.mobileNos UNION SELECT GROUP_CONCAT( distinct u.id ) user_ids, u.mobile_number mobileNos, GROUP_CONCAT( distinct d.imeinumber) imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM  `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is null and u.mobile_number is not null GROUP BY u.mobile_number UNION SELECT GROUP_CONCAT( distinct u.id ) user_ids, u.mobile_number mobileNos, GROUP_CONCAT( distinct d.imeinumber) imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM  `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is null and u.mobile_number is null and LOWER(u.referrer) not like 'emp%' and LOWER(u.referrer) not like 'crm%' and LOWER(u.referrer) not like 'fos%' GROUP BY u.referrer"
    
    cursor.execute(sql)
    result_data = cursor.fetchall()
    if result_data:
        for record_data in result_data:
            users = Users.query.filter(Users.id.in_(record_data[0]))
            existingGroupId = 0
            for user in users:
                if user.userGroupId and user.userGroupId >0:
                    existingGroupId = user.userGroupId
                    
            if existingGroupId >0:
                checkForExistingUserGroup = "select * from usergroup where id=%d"%(existingGroupId)
                cursor.execute(checkForExistingUserGroup)
                existingUserGroup = cursor.fetchone()
                updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], record_data[2], record_data[3], existingUserGroup[0])
                try:
                    cursor.execute(updateUserGroup)
                    db.commit()
                except:
                    # Rollback in case there is any error
                    db.rollback()
                
                for user in users:
                    if not user.userGroupId or user.userGroupId==0:
                        user.userGroupId = existingGroupId
                    session.commit()
            else:
                updateUserGroupUserIds = False
                updateUserGroupMobileNos = False
                updateUserGroupImeis = False
                updateUserGroupReferrers = False
                print record_data[0]
                checkForCurrentRecord = "select * from usergroup where mobileNos like '%%%s%%' or imeis like '%%%s%%' or referrers like '%%%s%%'"%(record_data[1], record_data[2], record_data[3])
                print checkForCurrentRecord
                
                cursor.execute(checkForCurrentRecord)
                currentRecord = cursor.fetchone()
                if currentRecord is not None:
                    if currentRecord[1] != record_data[0]:
                        updateUserGroupUserIds = True
                    if currentRecord[2] != record_data[1]:
                        updateUserGroupMobileNos = True
                    if currentRecord[3] != record_data[2]:
                        updateUserGroupImeis = True
                    if currentRecord[4] != record_data[3]:
                        updateUserGroupReferrers = True
                    updateUserGroup = None
                    if updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], record_data[2], record_data[3], currentRecord[0])
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
                        updateUserGroup =None
                    elif updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], record_data[2], currentRecord[4], currentRecord[0])
                    elif updateUserGroupUserIds and updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], currentRecord[3], currentRecord[4], currentRecord[0])
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], currentRecord[3], currentRecord[4], currentRecord[0])
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], currentRecord[3], currentRecord[4], currentRecord[0])   
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], record_data[2], currentRecord[4], currentRecord[0])
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and not updateUserGroupImeis and updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], currentRecord[3], record_data[3], currentRecord[0])
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], currentRecord[2], record_data[2], record_data[3], currentRecord[0])
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], currentRecord[2], record_data[2], currentRecord[4], currentRecord[0])
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], currentRecord[2], currentRecord[3], record_data[3], currentRecord[0])
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], record_data[2], record_data[3], currentRecord[0])
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], record_data[2], currentRecord[4], currentRecord[0])
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], record_data[2], record_data[3], currentRecord[0])
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and updateUserGroupReferrers:
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], currentRecord[3], record_data[3], currentRecord[0])
                        
                    if updateUserGroup is not None:
                        try:
                            cursor.execute(updateUserGroup)
                            db.commit()
                        except:
                            # Rollback in case there is any error
                            db.rollback()
                            
                    for user in users:
                        if not user.userGroupId or user.userGroupId==0:
                            user.userGroupId = currentRecord[0]
                        session.commit()
                else:
                    sql = "insert into usergroup (user_ids, mobileNos, imeis, referrers) values('%s', '%s', '%s', '%s')"%(record_data[0], record_data[1], record_data[2], record_data[3])
                    print sql
    
                    try:
                        cursor.execute(sql)
                        db.commit()
                    except:
                        # Rollback in case there is any error
                        db.rollback()
                    
                    userGroupRecordSql = "select * from usergroup where mobileNos like '%%%s%%' or imeis like '%%%s%%' or referrers like '%%%s%%'"%(record_data[1], record_data[2], record_data[3])
                    cursor.execute(userGroupRecordSql)
                    
                    userGroupRecord = cursor.fetchone()
                    
                    for user in users:
                        if not user.userGroupId or user.userGroupId==0:
                            user.userGroupId = userGroupRecord[0]
                        session.commit()
                        
    db.close()
    if session.is_active:
        print "session is active. closing it."
        session.close()

if __name__=='__main__':
    main()