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