Subversion Repositories SmartDukaan

GROUP_SEGMENTATION_QUERY = """

select case  

when created >= date(now()) - interval 3 day and total > 0 then 'ABU'

when created <  date(now()) - interval 3 day and total > 0 then 'IBU'

when last_active >=  date(now()) - interval 3 day and total = 0 and date(last_active) <> date(ucreated) then 'ANBU'

when ucreated <  date(now()) - interval 3 day and total = 0 and last_active <  date(now()) - interval 3 day then 'INU'

when ucreated >=  date(now()) - interval 3 day and total = 0 and date(last_active) = date(ucreated) then 'FIU'

else 'OTH'

end

usersegment, final.*  

from (select u.id, u.username, u.first_name, u.mobile_number, u.usergroup_id, ug.groupbasis,

max(u.created) ucreated, min(u.created) minucreated, max(ua.last_active) last_active, max(ow.created) created, if(sum(ow.total) is null, 0, sum(ow.total)) as total

from users u left join useractive ua on ua.user_id=u.id left join usergroups ug on ug.id=u.usergroup_id 

left join (select *, count(*) as total from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') 

            union all (select user_id, created from flipkartorders where date(created) >'2015-03-22') order by created desc) as a1 group by user_id) as ow 

on ow.user_id = u.id  

where lower(u.referrer) not like 'emp%' or u.referrer is null group by usergroup_id) final;

"""

        generateGroupSegmentationReport(workbook)

        #sendmail([], "", TMP_FILE, SUBJECT)

    except:

        traceback.print_exc()

        print "Could not create report"

def generateGroupSegmentationReport(workbook):

    selectSql = GROUP_SEGMENTATION_QUERY

    conn = getDbConnection()

    try:

        # prepare a cursor object using cursor() method

        cursor = conn.cursor()

        # Execute the SQL command

        # Fetch source id.

        cursor.execute(selectSql)

        result = cursor.fetchall()

        worksheet = workbook.add_sheet("Segmented User Group")

        boldStyle = xlwt.XFStyle()

        f = xlwt.Font()

        f.bold = True

        boldStyle.font = f

        column = 0

        row = 0

        worksheet.write(row, 0, 'User Segment', boldStyle)

        worksheet.write(row, 1, 'User Id', boldStyle)

        worksheet.write(row, 2, 'Username', boldStyle)

        worksheet.write(row, 3, 'Name', boldStyle)

        worksheet.write(row, 4, 'Mobile', boldStyle)

        worksheet.write(row, 5, 'UserGroupId', boldStyle)

        worksheet.write(row, 6, 'Group basis', boldStyle)

        worksheet.write(row, 7, 'User last created', boldStyle)

        worksheet.write(row, 8, 'User first created', boldStyle)

        worksheet.write(row, 9, 'User Last Active On', boldStyle)

        worksheet.write(row, 10, 'Order Last Created On', boldStyle)

        worksheet.write(row, 11, 'Total Orders', boldStyle)

        for r in result:

            row += 1

            column = 0

            for data in r :

                worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else default_format)

                column += 1

        workbook.save(TMP_FILE)