def get_sql_data(query, brand):
conn = connections[brand]
cursor = conn.cursor()
cursor.execute(query)
data = dictfetchall(cursor)
conn.close()
return data
def update_coupon_history_table():
logger.info("updating_coupon_history_data")
# query_coupon = "select status,count(*) as count from gm_coupondata c where \
# DATE(c.created_date)=DATE_SUB(CURDATE(), INTERVAL 1 DAY) group by status;"
query_coupon = "select status,count(*) as count from gm_coupondata c group by status"
# query_coupon_history_daily = 'select * from gm_couponfact c inner join gm_datedimension d on \
# c.date_id=d.date_id where d.date=DATE_SUB(CURDATE(), INTERVAL 2 DAY) and data_type="DAILY"'
# query_coupon_history_total = 'select * from gm_couponfact c inner join gm_datedimension d on \
# c.date_id=d.date_id where d.date=DATE_SUB(CURDATE(), INTERVAL 2 DAY) and data_type!="DAILY"'
date_query = "select date_id from gm_datedimension d where d.date=DATE_SUB(CURDATE(), INTERVAL 1 DAY)"
conn = connections['bajaj']
cursor = conn.cursor()
cursor.execute(query_coupon)
data_coupon = dictfetchall(cursor)
cursor.execute(date_query)
date_data = dictfetchall(cursor)
coupon_count = {}
for data in data_coupon:
coupon_count[data['status']] = data['count']
# cursor.execute(query_coupon_history_daily)
# data_coupon_history_daily = dictfetchall(cursor)
# cursor.execute(query_coupon_history_total)
# data_coupon_history_total = dictfetchall(cursor)
params = {}
params['inprogress'] = coupon_count.get(CouponStatus.IN_PROGRESS, 0)
params['closed'] = coupon_count.get(CouponStatus.CLOSED, 0)
params['unused'] = coupon_count.get(CouponStatus.UNUSED, 0)
params['exceeds'] = coupon_count.get(CouponStatus.EXCEEDS_LIMIT, 0)
params['expired'] = coupon_count.get(CouponStatus.EXPIRED, 0)
params['data_type'] = 'TOTAL'
params['date_id'] = date_data[0]['date_id']
insert_query = "insert into gm_couponfact(date_id, inprogress, closed, expired, unused, exceeds, data_type) \
values(%(date_id)s, %(inprogress)s,%(closed)s,%(expired)s,%(unused)s,%(exceeds)s, %(data_type)s)"
delete_query = 'delete from gm_couponfact where date_id = %(date_id)s and data_type=%(data_type)s'
update_query = "UPDATE gm_couponfact SET inprogress=%(inprogress)s, closed=%(closed)s, \
expired=%(expired)s, unused=%(unused)s, exceeds=%(exceeds)s WHERE date_id=%(date_id)s and data_type=%(data_type)s"
try:
cursor.execute(insert_query, params)
except:
cursor.execute(update_query, params)
conn.close()
def get_tat(self, request, **kwargs):
try:
self.is_authenticated(request)
conn = connections[settings.BRAND]
cursor = conn.cursor()
query = ("select sum(f2.dt) as sums ,count(*) as c , avg(f2.dt) as tat, YEAR(f1.created_date) as year, \
MONTH(f1.created_date) as month from gm_feedback f1 inner join (select f2.id, TIMEDIFF(f2.resolved_date,f2.created_date)\
as dt , f2.created_date from gm_feedback f2 where status= 'resolved' and \
((f2.created_date) > date_sub(curdate(), interval 6 month))) f2 on f2.id=f1.id group by \
YEAR(f1.created_date), MONTH(f1.created_date)")
cursor.execute(query)
details = dictfetchall(cursor)
reports = {}
result = []
for data in details:
tat = {}
minutes, seconds = divmod(data['tat'], 60)
tat['tat'] = minutes
tat['month_of_year'] = str(data['year'])+"-"+ str(data['month'])
result.append(tat)
reports['TAT'] = result
query = ("select count(*) as total, concat (YEAR(resolved_date),'-', MONTH(resolved_date)) \
as month_of_year from gm_feedback where resolved_date is not null and \
(resolved_date > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) group by YEAR(resolved_date), MONTH(resolved_date)")
cursor.execute(query)
fcr_total = dictfetchall(cursor)
query = ("select count(*) as cnt, concat(YEAR(resolved_date), '-', MONTH(resolved_date))\
as month_of_year from gm_feedback where fcr=1 and resolved_date > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) \
group by(fcr),YEAR(resolved_date), MONTH(resolved_date)")
cursor.execute(query)
fcr_count = dictfetchall(cursor)
result = []
for data in fcr_total:
fcr = {}
fcr['month_of_year'] = data['month_of_year']
fcrs = filter(lambda fc: fc['month_of_year'] == data['month_of_year'], fcr_count)
if fcrs:
fcr['fcr'] = (fcrs[0]['cnt']/float(data['total'])) * 100
result.append(fcr)
reports['FCR'] = result
query = ("select count(*) as cnt, concat(YEAR(created_date), '-', MONTH(created_date))\
as month_of_year from gm_activity where new_value='Open' and (original_value ='Resolved' or \
original_value='Closed') and created_date > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) \
group by YEAR(created_date), MONTH(created_date)")
cursor.execute(query)
reopen_count = dictfetchall(cursor)
query = ("select count(*) as total, concat(YEAR(created_date), '-', \
MONTH(created_date)) as month_of_year from gm_feedback where \
(created_date > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) group by YEAR(created_date), MONTH(created_date)")
cursor.execute(query)
reopen_total = dictfetchall(cursor)
result = []
for data in reopen_total:
reopened = {}
reopened['month_of_year'] = data['month_of_year']
reopens = filter(lambda reopen : reopen['month_of_year'] == data['month_of_year'], reopen_count)
if reopens:
reopened['re-open'] = (reopens[0]['cnt']/float(data['total'])) * 100
result.append(reopened)
reports['RE-OPENED'] = result
conn.close()
return HttpResponse(content=json.dumps(reports),
content_type='application/json')
except Exception as ex:
conn.close()
LOG.error('Exception while generating TAT and FCR report : {0}'.format(ex))
return HttpResponseBadRequest()
def get_sql_data(self, query):
conn = connections[settings.BRAND]
cursor = conn.cursor()
cursor.execute(query)
data = dictfetchall(cursor)
conn.close()
return data
