def __init__(self, category=1, statistics_type='sum'):
super(ConsumeMonthStudent, self).__init__(category, statistics_type)
self.filename = 'data/consumemonth_student.pkl'
self.bill_conn = get_bills_connection()
self.legacy_conn = get_product_connection()
self.cash_bill_conn = get_cash_billing_connection()
def get_product_ids_from_database(t):
bill_conn = get_bills_connection()
sql = "select group_concat(id) from products where billing_type = %d" % t
with bill_conn.cursor() as cur:
cur.execute(sql)
data = cur.fetchone()
return tuple(int(i) for i in data[0].split(','))
def __init__(self, date):
self.conn = get_bills_connection()
self.cash_bill_conn = get_cash_billing_connection()
self.cur = self.conn.cursor()
self.output_file = 'data/user_recharge.csv'
self.small_product_id = (4,8,10, 11, 12, 20, 21, 22, 23, 24, 25, 26, 28, 27, 29,
1003, 1004, 1005, 1006, 1024, 1025, 1026, 1027, 1028, 1029, 1030, 1031, 1032,
1033, 1034, 1035, 1036, 1037, 1038)
self.big_product_id = (5, 6, 13, 19)
self.all_product_id = self.big_product_id + self.small_product_id
self.month_index = MonthIndexFactroy()
def __init__(self, category=3):
super(BillDataFrame, self).__init__()
self.bill_conn = get_bills_connection()
self.cash_bill_conn = get_cash_billing_connection()
self.product_conn = get_product_connection()
self.category = category
if category == 1:
self.product_ids = self.format_product_id
self.cash_product_ids = (2, 3, 4)
elif category == 2:
self.product_ids = self.experience_product_id
self.cash_product_ids = (1, 999)
else:
self.product_ids = self.all_product_id
self.cash_product_ids = (1, 2, 3, 4)
def __init__(self):
months = MonthIndexFactroy('2018-05')
self.bill_conn = get_bills_connection()
self.cash_bill_conn = get_cash_billing_connection()
self.legacy_conn = get_product_connection()
self.students = dict()
self.all_students = set()
self.months = months
self.user_df = pd.DataFrame(0,
index=months.index,
columns=['increase_student'] +
months.index)
self.consumer_df = pd.DataFrame(0,
index=months.index,
columns=['increase_student'] +
months.index)
self.nil_students()
def mission_7(refresh):
month_list = [
'2015-12', '2016-01', '2016-02', '2016-03', '2017-03', '2016-04',
'2016-05', '2016-06', '2016-07', '2016-08', '2016-09', '2016-10',
'2016-11', '2016-12', '2017-01', '2017-02', '2017-03', '2017-04',
'2017-05', '2017-06', '2017-07', '2017-08', '2017-09', '2017-10',
'2017-11', '2017-12', '2018-01', '2018-02', '2018-03', '2018-04',
'2018-05', '2018-06'
]
df = pd.DataFrame(0, index=month_list, columns=month_list, dtype='uint8')
student_set = set()
bills = dict()
conn = get_bills_connection()
with conn.cursor() as cur:
sql = "select id, student_id, paid_at from bills where product_id \
in (5,6,13,19) and status in (20, 70, 80) and student_id > 0 and deleted_at \
is null"
cur.execute(sql)
print(cur.rowcount)
while cur.rownumber < cur.rowcount:
r = cur.fetchone()
if r[1] not in student_set:
student_set.add(r[1])
month = r[2].strftime('%Y-%m')
if month in bills.keys():
bills[month].append(r[0])
else:
bills[month] = [
r[0],
]
with conn.cursor() as cur:
sql = "select bill_id, updated_at from refunds where status = 3 and type = 0 and deleted_at is null"
cur.execute(sql)
print(cur.rowcount)
while cur.rownumber < cur.rowcount:
r = cur.fetchone()
for m, ml in bills.items():
if r[0] in ml:
df.loc[m, r[1].strftime('%Y-%m')] += 1
conn = get_product_connection()
with conn.cursor() as cur:
sql = "select bill_id, updated_at from refund where deleted_at is null and status_id = 20 "
cur.execute(sql)
print(cur.rowcount)
while cur.rownumber < cur.rowcount:
r = cur.fetchone()
for m, ml in bills.items():
if r[0] in ml:
df.loc[m, r[1].strftime('%Y-%m')] += 1
cash_bills = dict()
conn = get_cash_billing_connection()
with conn.cursor() as cur:
sql = "select id, student_id, paid_at from bills where product_id in (2,3,4) and \
status in (20, 70, 80) and deleted_at is null"
cur.execute(sql)
print(cur.rowcount)
while cur.rownumber < cur.rowcount:
r = cur.fetchone()
if r[1] not in student_set:
student_set.add(r[1])
month = r[2].strftime('%Y-%m')
if month in cash_bills.keys():
cash_bills[month].append(r[0])
else:
cash_bills[month] = [
r[0],
]
with conn.cursor() as cur:
sql = "select bill_id, updated_at from refunds where status = 3 and type = 0 and deleted_at is null"
cur.execute(sql)
print(cur.rowcount)
while cur.rownumber < cur.rowcount:
r = cur.fetchone()
for m, ml in cash_bills.items():
if r[0] in ml:
df.loc[m, r[1].strftime('%Y-%m')] += 1
print(df)
df.insert(0, 'count', 0)
for m in month_list:
lb = len(bills[m]) if m in bills.keys() else 0
lcb = len(cash_bills[m]) if m in cash_bills.keys() else 0
df.loc[m, 'count'] = lb + lcb
df.to_csv('data/first_format_bills_refund.csv')
def __init__(self, category=1, statistics_type='sum'):
super(FisrtBuyMonthStudent, self).__init__(category, statistics_type)
self.filename = 'data/firstbuymonth_student.pkl'
self.conn = get_bills_connection()
self.cash_bill_conn = get_cash_billing_connection()