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
    
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
cursor = db.cursor()

DataService.initialize(db_hostname="localhost") 
        
def main():
    
    sql = "SELECT GROUP_CONCAT( distinct u.id ) user_ids, GROUP_CONCAT(distinct u.mobile_number) mobileNos, d.imeinumber imeis, GROUP_CONCAT(distinct u.referrer) referrers, 'Imeis' Group_Basis FROM  users u LEFT OUTER JOIN devices d ON u.id = d.user_id where trim(coalesce(d.imeinumber, '')) <>'' GROUP BY d.imeinumber 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, 'Mobile_Nos' Group_Basis FROM users u LEFT OUTER JOIN devices d ON u.id = d.user_id where trim(coalesce(u.mobile_number, '')) <>'' GROUP BY u.mobile_number"
    
    cursor.execute(sql)
    result_data = cursor.fetchall()
    if result_data:
        for record_data in result_data:
            print "User Ids",record_data[0]
            userIds = []
            imeis = []
            mobileNos = []
            referrers = []
            groupbasis = []
            for x in record_data[0].split(','):
                userIds.append(int(x.strip()))
            if record_data[1]:
                for x in record_data[1].split(','):
                    mobileNos.append(str(x.strip()))
            if record_data[2]:
                for x in record_data[2].split(','):
                    imeis.append(str(x.strip()))
            if record_data[3]:
                for x in record_data[3].split(','):
                    referrers.append(str(x.strip()))
            for x in record_data[4].split(','):
                groupbasis.append(str(x.strip())) 
                
            users = Users.query.filter(Users.id.in_(tuple(userIds)))
            existingGroupIds = []
            for user in users:
                print user.usergroup_id
                if user.usergroup_id and user.usergroup_id >0:
                    existingGroupIds.append(user.usergroup_id)
            
            existingGroupIds = set(existingGroupIds)
            existingGroupIds = list(existingGroupIds)
                 
            print existingGroupIds
            if len(existingGroupIds) >0 and len(existingGroupIds)==1:
                existingGroupId = existingGroupIds[0] 
                checkForExistingUserGroup = "select * from usergroups where id=%d"%(existingGroupId)
                cursor.execute(checkForExistingUserGroup)
                existingUserGroup = cursor.fetchone()
                
                newUserIds = existingUserGroup[1]
                newMobileNos = existingUserGroup[2]
                newImeis = existingUserGroup[3]
                newReferrers = existingUserGroup[4]
                newGroupBasis = existingUserGroup[5]
                
                fguserIds = []
                for x in record_data[0].split(','):
                    fguserIds.append(int(x.strip()))
                
                for userId in userIds:
                    if not str(userId) in newUserIds:
                        newUserIds = newUserIds +','+str(userId)
                
                for mobileNo in mobileNos :
                    if not mobileNo in newMobileNos:
                        newMobileNos = newMobileNos +','+mobileNo
                    
                for imei in imeis:
                    if not imei in newImeis:
                        newImeis = newImeis+','+imei
                    
                for referrer in referrers:
                    if not referrer in newReferrers:
                        newReferrers = newReferrers +','+referrer
                
                if len(newGroupBasis)> 0:
                    if len(userIds) > 1 and len(fguserIds) < len(userIds):
                        newGroupBasis = str(record_data[4])
                else:
                    if len(userIds) > 1 and len(fguserIds) < len(userIds):
                        newGroupBasis = str(record_data[4])
                    else:
                        for groupbase in groupbasis:
                            if not groupbase in newGroupBasis:
                                newGroupBasis = newGroupBasis +','+groupbase
                
                newGroupBasis = newGroupBasis.strip(',')
                updateUserGroup = "update usergroups set userids ='%s', mobilenos ='%s', imeis ='%s', referrers ='%s', groupbasis ='%s' where id = %d"%(newUserIds, newMobileNos, newImeis, newReferrers, newGroupBasis, existingGroupId)
                print updateUserGroup
                try:
                    cursor.execute(updateUserGroup)
                    db.commit()
                except:
                    # Rollback in case there is any error
                    db.rollback()
                
                for user in users:
                    user.usergroup_id = existingGroupId
                    session.commit()
            elif len(existingGroupIds)>1:
                existingGroupIds.sort()
                stringGroupIds =[]
                for groupId in (existingGroupIds):
                    stringGroupIds.append(str(groupId))
                allUserGroupsSql = "select * from usergroups where id in %s"%(str(tuple(stringGroupIds)))
                print allUserGroupsSql
                cursor.execute(allUserGroupsSql)
                allUserGroups = cursor.fetchall()
                allUserGroups = list(allUserGroups)
                firstUserGroup = allUserGroups[0]
                
                newUserIds = firstUserGroup[1]
                newMobileNos = firstUserGroup[2]
                newImeis = firstUserGroup[3]
                newReferrers = firstUserGroup[4]
                newGroupBasis = firstUserGroup[5]
                moreUsersToConsider = userIds
                
                fguserIds = []
                for x in record_data[0].split(','):
                    fguserIds.append(int(x.strip()))
                
                for usergroup in allUserGroups:
                    print 'UserGroupId',usergroup[0]
                    
                    ug_userIds = []
                    ug_imeis = []
                    ug_mobileNos = []
                    ug_referrers = []
                    ug_groupbasis = []
                    
                    if usergroup[1]:
                        for x in usergroup[1].split(','):
                            ug_userIds.append(str(x.strip()))
                    if usergroup[2]:
                        for x in usergroup[2].split(','):
                            ug_mobileNos.append(str(x.strip()))
                    if usergroup[3]:
                        for x in usergroup[3].split(','):
                            ug_imeis.append(str(x.strip()))
                    if usergroup[4]:
                        for x in usergroup[4].split(','):
                            ug_referrers.append(str(x.strip())) 
                    for x in usergroup[5].split(','):
                        ug_groupbasis.append(str(x.strip()))
                        
                    ug_userIds = set(ug_userIds)
                    ug_userIds = list(ug_userIds)
                    ug_imeis = set(ug_imeis)
                    ug_imeis = list(ug_imeis)
                    ug_mobileNos = set(ug_mobileNos)
                    ug_mobileNos = list(ug_mobileNos)
                    ug_referrers = set(ug_referrers)
                    ug_referrers = list(ug_referrers)
                    ug_groupbasis = set(ug_groupbasis)
                    ug_groupbasis = list(ug_groupbasis)
                        
                    for userId in ug_userIds:
                        if not userId in newUserIds:
                            newUserIds = newUserIds +','+str(userId)
                            moreUsersToConsider.append(userId)
                    
                    for mobileNo in ug_mobileNos :
                        if not mobileNo in newMobileNos:
                            newMobileNos = newMobileNos +','+mobileNo
                        
                    for imei in ug_imeis:
                        if not imei in newImeis:
                            newImeis = newImeis+','+imei
                        
                    for referrer in ug_referrers:
                        if not referrer in newReferrers:
                            newReferrers = newReferrers +','+referrer
                    
                    if len(newGroupBasis) == 0:
                        if len(ug_userIds) > 1:
                            for groupbase in ug_groupbasis:
                                if not groupbase in newGroupBasis:
                                    newGroupBasis = newGroupBasis +','+groupbase
                    else:
                        for groupbase in ug_groupbasis:
                            if not groupbase in newGroupBasis:
                                newGroupBasis = newGroupBasis +','+groupbase
                                
                newGroupBasis = newGroupBasis.strip(',')
                
                for userId in userIds:
                    if not str(userId) in newUserIds:
                        newUserIds = newUserIds +','+str(userId)
                
                for mobileNo in mobileNos :
                    if not mobileNo in newMobileNos:
                        newMobileNos = newMobileNos +','+mobileNo
                    
                for imei in imeis:
                    if not imei in newImeis:
                        newImeis = newImeis+','+imei
                    
                for referrer in referrers:
                    if not referrer in newReferrers:
                        newReferrers = newReferrers +','+referrer
                        
                if len(newGroupBasis)> 0:
                    if len(userIds) > 1 and len(fguserIds) < len(userIds):
                        newGroupBasis = str(record_data[4])
                else:
                    if len(userIds) > 1 and len(fguserIds) < len(userIds):
                        newGroupBasis = str(record_data[4])
                    else:
                        for groupbase in groupbasis:
                            if not groupbase in newGroupBasis:
                                newGroupBasis = newGroupBasis +','+groupbase
                            
                newGroupBasis = newGroupBasis.strip(',')
                
                updateUserGroup = "update usergroups set userids ='%s', mobilenos ='%s', imeis ='%s', referrers ='%s', groupbasis ='%s' where id = %d"%(newUserIds, newMobileNos, newImeis, newReferrers, newGroupBasis, firstUserGroup[0])
                print updateUserGroup
                try:
                    cursor.execute(updateUserGroup)
                    db.commit()
                except:
                    # Rollback in case there is any error
                    db.rollback()
                
                usersToConsider = Users.query.filter(Users.id.in_(tuple(moreUsersToConsider)))
                
                for user in usersToConsider:
                    user.usergroup_id = firstUserGroup[0]
                    session.commit()        
            elif len(existingGroupIds)==0:
            
                sql = "insert into usergroups (userids, mobilenos, imeis, referrers, groupbasis) values('%s', '%s', '%s', '%s', '%s')"%(record_data[0], record_data[1], record_data[2], record_data[3], record_data[4])
                
                print sql
                try:
                    cursor.execute(sql)
                    db.commit()
                except:
                    # Rollback in case there is any error
                    db.rollback()
                
                userGroupRecordSql = "select * from usergroups where mobilenos like '%s' and imeis like '%s' and referrers like '%s' and groupbasis like '%s'"%(record_data[1], record_data[2], record_data[3], record_data[4])
                print userGroupRecordSql
                cursor.execute(userGroupRecordSql)
                
                userGroupRecord = cursor.fetchone()
                
                for user in users:
                    if not user.usergroup_id or user.usergroup_id==0:
                        user.usergroup_id = userGroupRecord[0]
                    session.commit()
    
    userGroupDetails = session.query(Users.usergroup_id, func.count(Users.id)).group_by(Users.usergroup_id).all()
    
    groupBasisUpdate = []
    for usergroupDetail in userGroupDetails:
        if usergroupDetail[1] ==1:
            groupBasisUpdate.append(str(usergroupDetail[0]))
         
    updateUserGroupBasis ="update usergroups set groupbasis='Not_Defined' where id in %s"%(str(tuple(groupBasisUpdate)))
    
    print updateUserGroupBasis
    
    try:
        cursor.execute(updateUserGroupBasis)
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()
                        
    db.close()
    if session.is_active:
        print "session is active. closing it."
        session.close()

if __name__=='__main__':
    main()