· 8 years ago · Dec 18, 2017, 06:24 PM
1__author__ = 'Johnny'
2import pyodbc
3import pandas as pd
4import sqlite3
5import sys
6import colorsys
7from datetime import datetime, timedelta, date
8from dateutil.relativedelta import *
9
10pd.set_option('display.height', 1000)
11pd.set_option('display.max_rows', 500)
12pd.set_option('display.max_columns', 500)
13pd.set_option('display.width', 300)
14
15
16start = datetime.now()
17
18class Banding():
19 def __init__(self, server="LOCALHOST", database='Retail_store', user='Johnny'):
20 self.server = server
21 self.database = database
22 self.user = user
23
24 def bands(self, percentages=[]):
25 # banding of customers based on the pareto's principle of 20-80
26 segments = []
27 for i in percentages:
28 if i < 0:
29 segments.append("Negatives")
30 elif i < 20:
31 segments.append("Class A TOP 20%")
32 elif i < 40:
33 segments.append("Class B 20% - 40%")
34 elif i < 60:
35 segments.append("Class C 40% - 60%")
36 elif i < 80:
37 segments.append("Class D 60% - 80%")
38 else:
39 segments.append("Class E OVER 80%")
40 return segments
41
42 def CreateandInsertBandingTable(self, to_db=[[0, 0, 'null', 'null'], [1, 1, 'null', 'null']], table_name='Cumulative_Banding' ):
43 # create a connection string and make connection to the server
44 connection_info="Driver={SQL Server Native Client 11.0};Server="+self.server+";Database="+self.database+";Trusted_Connection=yes; user="+self.user
45 cnxn = pyodbc.connect(connection_info)
46 cursor = cnxn.cursor()
47
48 # create the table to hold banding values if it does not exist
49 create = "IF NOT EXISTS (select * from sysobjects where name='"+table_name+"' and xtype='U')" \
50 "create table "+table_name+" (" \
51 "date_Period date," \
52 "ID nvarchar(20)," \
53 "TotalRevenue float," \
54 "TransCount BIGINT, " \
55 "cumulative_Bands_Revenue nvarchar(50)," \
56 "cumulative_Bands_Transactions nvarchar(50)," \
57 "cumulative_Bands_Customers nvarchar(50) )"
58 cursor.execute(create)
59
60 # Prepare and execute the insert statement to insert the relevant data
61 sql = " INSERT INTO "+table_name+" (date_Period, ID, TotalRevenue, TransCount, cumulative_Bands_Revenue, cumulative_Bands_Transactions, cumulative_Bands_Customers) VALUES (?,?,?,?,?,?,?)"
62
63 cursor.executemany(sql, to_db)
64 cursor.commit()
65
66 def cumulative_banding_onCustomersandTransactions(self, tblType ='daily', first=[2015, 1, 1], end=[2016, 1, 10]):
67 connection_info = "Driver={SQL Server Native Client 11.0};Server="+self.server+";Database="+self.database+";" \
68 "Trusted_Connection=yes; user="+self.user
69
70 cnxn = pyodbc.connect(connection_info)
71 cursor = cnxn.cursor()
72
73 # specify the start and end date where the banding should occur
74 qry_date = date(first[0], first[1], first[2])
75 end_date = date(end[0], end[1], end[2])
76 if tblType == 'monthly':
77 delta = relativedelta(months=1)
78 elif tblType == 'quarterly':
79 delta = relativedelta(year=1)
80 else:
81 delta = timedelta(days=1)
82
83 days_diff = 0
84
85 while qry_date <= end_date:
86 print(qry_date),
87 # Prepare the conditions necessary for each period
88 if tblType == 'daily':
89 days_diff = 0
90 Qry_date = str(qry_date.strftime("%Y-%m-%d"))
91 date_str = " cast(DateTxn as date) >= DATEADD(DAY, -"+str(days_diff)+", '"+Qry_date+"') AND cast(DateTxn as date) <= " \
92 "'"+Qry_date+"'"
93 table_Name = "Cumulative_banding_Daily"
94 pass
95 elif tblType == 'weekly':
96 days_diff = 6
97 Qry_date = str(qry_date.strftime("%Y-%m-%d"))
98 date_str = " cast(DateTxn as date) >= DATEADD(DAY, -"+str(days_diff)+", '"+Qry_date+"') AND cast(DateTxn as date) <= " \
99 "'"+Qry_date+"'"
100 table_Name = "Cumulative_banding_Weekly"
101 elif tblType == 'quarterly':
102 days_diff = 3
103 mnth = str(qry_date.strftime("%m"))
104 year = str(qry_date.strftime("%Y"))
105 quart = (int(mnth) - 1) // 3 + 1
106 quarter = "Q"+str(quart)+" "+str(year)
107 print(quarter)
108 date_str = "'Q'+convert(varchar(1), ((DATEPART(MONTH, cast(DateTxn as date))- 1)/3) + 1)+' '+convert(varchar(4), DATEPART(year, cast(DateTxn as date))) = 'Q"+str(quart)+" "+str(year)+"'"
109 table_Name = "Cumulative_banding_Quarterly"
110 Qry_date = quarter
111 else:
112 days_diff = 1
113 Qry_date = str(qry_date.strftime("%Y-%m-%d"))
114 q_date = str(qry_date.strftime("%Y-%m"))
115 date_str = " convert(varchar(7), cast(DateTxn as date)) = '"+q_date+"'"
116 table_Name = "Cumulative_banding_Monthly"
117
118 # print(Qry_date) # Necessary for DEBUGGING
119
120 # prepare statement to consider while banding
121 sql = "select '"+Qry_date+"' date_Period," \
122 " ID," \
123 " sum([TxnTotal]) TotalRevenue," \
124 " count(*) TransCount" \
125 " FROM SC_Txn_Master WHERE [TxnDescription] = 'EARNED POINTS' AND "+date_str+" group by ID"
126
127 # print(sql) # Necessary for DEBUGGING
128
129 try:
130 # read the data into a dataframe
131 df = pd.read_sql(sql, cnxn)
132
133 # Banding on the number of Transactions count
134 dFrame = df.sort_values(by=['TransCount'], ascending=False) # sort all values on count of transactions
135 dFrame['cumulative_sum_Transactions'] = dFrame['TransCount'].cumsum(axis=0) # do a cumulative sum on transaction count
136 cumulative_Percentage_Transactions = (dFrame['cumulative_sum_Transactions'] / sum(dFrame['TransCount'])) * 100
137 dFrame['cumulative_Percentage_Transactions'] = cumulative_Percentage_Transactions
138 dFrame['cumulative_Bands_Transactions'] = self.bands(percentages=cumulative_Percentage_Transactions)
139
140 # Band on Revenue earned or produced
141 dFrame = dFrame.sort_values(by=['TotalRevenue'], ascending=False) # sort on revenue column
142 dFrame['cumulative_sum_Revenue'] = dFrame['TotalRevenue'].cumsum(axis=0) # get a cumulative sum on revenue
143 cumulative_Percentage_Revenue = (dFrame['cumulative_sum_Revenue'] / sum(dFrame['TotalRevenue']))*100 # get cumulative % of revenue
144 # dFrame['cumulative_Percentage_Revenue'] = cumulative_Percentage_Transactions
145 dFrame['cumulative_Bands_Revenue'] = self.bands(percentages=cumulative_Percentage_Revenue) # band
146
147 # Band on Customers depending on revenue earnings to get top customers
148 dFrame['cust_count'] = [1 for i in range(len(dFrame))]
149 dFrame['cumulative_sum_customers'] = dFrame['cust_count'].cumsum(axis=0)
150 customer_Percentage = (dFrame['cumulative_sum_customers'] / sum(dFrame['cust_count'])) * 100
151 dFrame['cumulative_Bands_Customers'] = self.bands(percentages=customer_Percentage)
152 dFrame = dFrame[['date_Period', 'ID', 'TotalRevenue', 'TransCount', 'cumulative_Bands_Revenue', 'cumulative_Bands_Transactions', 'cumulative_Bands_Customers']]
153
154
155 # map the dataframe into a list and insert into the banding table
156 to_db = map(list, dFrame.values)
157 self.CreateandInsertBandingTable(to_db=to_db, table_name=table_Name)
158
159 qry_date += delta
160 print("Lap : "+str(datetime.now() - start))
161 except TypeError, e:
162 print("Error : " + str(e))
163 except pd.io.sql.DatabaseError, err:
164 print("unable to get the data from this statement \n\t\t\t" + str(err))
165 pass
166 except Exception, e:
167 print("General Error : " + str(e))
168 # print len(dFrame) * 0.2'''
169
170
171
172Bnd = Banding()
173
174# Band on a monthly basis
175Bnd.cumulative_banding_onCustomersandTransactions(tblType='monthly')