def search():
text = request.args['text']
warehouseid = request.args['warehouseid']
warehouseid = int(warehouseid)
url = "https://uat.shopkirana.in/api/itemMaster/Getitemmasterscentral"
resp = requests.get(url)
json_data = resp.json()
if (json_data["Status"] == "OK"):
redisAesKey = datetime.today().strftime('%Y%m%d') + "1201"
jso = aes256().decrypt(json_data["Data"], redisAesKey)
js = json.loads(jso)
df = json_normalize(js)
df = df[df.WarehouseId == warehouseid]
# df=df[['itemname', 'ItemId','WarehouseId']]
df = df[[
'itemname', 'ItemId', 'CategoryName', 'SubsubcategoryName',
'WarehouseId'
]]
p = df.groupby('itemname').max()
p = p.reset_index()
df = p
Row_list = []
for index, rows in df.iterrows():
# my_list =[rows.itemname, rows.ItemId,rows.CategoryName,rows.SubsubcategoryName]
my_list = [rows.itemname, rows.ItemId]
Row_list.append(my_list)
lst = Row_list
choices = lst
# text=input("enter the text:")
t = process.extract(text, choices, limit=10)
l = list()
for j in range(len(t)):
if (t[j][1] > 50):
a = (t[j][0])
l.append(a)
item = list()
if len(l) >= 1:
for i in l:
a = (i[1])
item.append(a)
t = json.dumps(item)
return t
def read():
url = "https://uat.shopkirana.in/api/itemMaster/Getitemmasterscentral"
resp = requests.get(url)
json_data = resp.json()
if(json_data["Status"] =="OK"):
redisAesKey = datetime.today().strftime('%Y%m%d') + "1201"
jso = aes256().decrypt(json_data["Data"],redisAesKey)
js = json.loads(jso)
df = json_normalize(js)
return jsonify(js)
def ret(self, value, level, ulimit, llimit):
value = int(value)
level = int(level)
ulimit = float(ulimit)
llimit = float(llimit)
#band = int(band)
#url = "https://er15.xyz:4436/api/Customers/GetCustDetailLabel?month=10&year=2019"
url = "https://er15.xyz:4436/api/Customers/CRMLevelCustomerDetail?month=12&year=2019"
resp = requests.get(url)
json_data = resp.json()
if (json_data['Status'] == 'OK'):
redisAesKey = datetime.today().strftime('%Y%m%d') + "1201"
jso = aes256().decrypt(json_data['Data'], redisAesKey)
js = json.loads(jso)
df = json_normalize(js)
# df = df.loc[df['IsActive'] == True]
# #json_data = requests.get(url).json()
# json_data = resp.json()
# df=json_normalize(json_data)
#ACTIVE = df[(df.IsActive == True)]
#INACTIVE = df[(df.IsActive == False)]
#df=ACTIVE
#print(df.head(2))
df['SelfOrderPercentage'] = (
(df['Selfordercount']) /
(df['Selfordercount'] + df['Salespersonordercount']) * 100)
df['SalesOrderPercentage'] = (df['Salespersonordercount']) / (
df['Selfordercount'] + df['Salespersonordercount']) * 100
df.fillna(0)
df = df.replace(np.NaN, 0)
df.rename(columns={"kkVolumn": "KKvolume"}, inplace=True)
###### LEVEL DEFINITIONS BEING DEFINED
df.loc[df.Volume == 0, 'levels'] = 'Level 0'
df.loc[df.Volume >= 1, 'levels'] = 'Level 1'
df.loc[(df.Volume >= 10000) & (df.OrderCount >= 3) &
(df.BrandCount >= 5), 'levels'] = 'Level 2'
df.loc[(df.Volume >= 20000) & (df.OrderCount >= 5) &
(df.BrandCount >= 10) & (df.KKvolume >= 2000),
'levels'] = 'Level 3'
df.loc[(df.Volume >= 30000) & (df.OrderCount >= 8) &
(df.BrandCount >= 20) & (df.KKvolume >= 8000) &
((df.Selfordercount /
(df.Salespersonordercount + df.Selfordercount)) * 100 > 30),
'levels'] = 'Level 4'
df.loc[(df.Volume >= 75000) & (df.OrderCount >= 12) &
(df.BrandCount >= 40) & (df.KKvolume >= 15000) &
((df.Selfordercount /
(df.Salespersonordercount + df.Selfordercount)) * 100 > 60),
'levels'] = 'Level 5'
df.round(2)
#
l0 = df[df['levels'] == 'Level 0']
l1 = df[df['levels'] == 'Level 1']
l2 = df[df['levels'] == 'Level 2']
l3 = df[df['levels'] == 'Level 3']
l4 = df[df['levels'] == 'Level 4']
l5 = df[df['levels'] == 'Level 5']
l = [l0, l1, l2, l3, l4, l5]
i = level
if (value == 0):
l[i]['Target'] = 0
# elif (value != 0 & percentage == 0):
# l[i]['Target'] = [(int(j) + value) for j in l[i]['Volume']]
else:
l[i]['Target'] = [(int(j) + value) for j in l[i]['Volume']]
l[i].sort_values(by='Volume', ascending=False)
df1 = l[i][[
'SkCode', 'Cityid', 'WarehouseName', 'WarehouseId', 'levels',
'Volume', 'Target'
]]
df1 = df1[(df1['Volume'] < ulimit) & (df1['Volume'] >= llimit)]
df1 = df1.sort_values(by='Volume', ascending=False)
#df1['Band'] = band
#df1= df1.to_json(orient='records')
#df_list = df1.values.tolist()
df1 = df1.to_dict('records')
return df1
def allocation():
month = request.args['month']
year = request.args['year']
band = request.args['band']
amount = request.args['amount']
l0amount = request.args['l0amount']
levels = request.args['levels']
cityid = request.args['cityid']
warehouseid = request.args['warehouseid']
l0amount = int (l0amount)
band = int (band)
amount = int (amount)
level = int (levels)
warehouseid = int(warehouseid)
cityid = int(cityid)
## getting data:- reading data from the api in json format and converting it into python dataframe using json_normalize
# url = "http://111.118.252.170:8181/api/Customers/GetCustDetailLabel?month=%s&year=%s" % (month, year)
# url = "http://192.168.1.113/api/Customers/GetCustDetailLabel?month=%s&year=%s" % (month, year)
url = "https://uat.shopkirana.in/api/Customers/GetCustDetailLabel?month=%s&year=%s" % (month,year)
resp = requests.get(url)
json_data = resp.json()
if(json_data['Status'] =="OK"):
redisAesKey = datetime.today().strftime('%Y%m%d') + "1201"
jso = aes256().decrypt(json_data["Data"],redisAesKey)
js = json.loads(jso)
df = json_normalize(js)
df=df.loc[df['IsActive'] == True]
## levelling :- segregating the customers into levels using definition below and storing each level into the list
df.loc[df.Volume == 0, 'levels'] = 'level_0'
df.loc[df.Volume >= 1, 'levels'] = 'level_1'
df.loc[(df.Volume >= 10000) & (df.OrderCount >= 3) & (df.BrandCount >= 5), 'levels'] = 'level_2'
df.loc[(df.Volume >= 20000) & (df.OrderCount >= 5) & (df.BrandCount >= 10) & (df.kkVolumn >= 2000), 'levels'] = 'level_3'
df.loc[(df.Volume >= 30000) & (df.OrderCount >= 8) & (df.BrandCount >= 20) & (df.kkVolumn >= 8000) & ((df.Selfordercount/(df.Salespersonordercount+df.Selfordercount))*100 > 30), 'levels'] = 'level_4'
df.loc[(df.Volume >= 75000) & (df.OrderCount >= 12) & (df.BrandCount >= 40) & (df.kkVolumn >= 15000) & ((df.Selfordercount/(df.Salespersonordercount+df.Selfordercount))*100 > 60), 'levels'] = 'level_5'
dfL0 = df.loc[df.levels == 'level_0']
dfL1 = df.loc[df.levels == 'level_1']
dfL2 = df.loc[df.levels == 'level_2']
dfL3 = df.loc[df.levels == 'level_3']
dfL4 = df.loc[df.levels == 'level_4']
dfL5 = df.loc[df.levels == 'level_5']
l = (dfL0 , dfL1 , dfL2 , dfL3 , dfL4 ,dfL5)
## Status :- based on the bands giving status to the skcode (promotion , retention , consistent) . bands are based on percentile .
# for example : - in band1 above 90 percentile is promotion and below 10% is retention and between 90 - 10 percentile is consistent
b = band
l = list(l)
i = level
df1 = copy.deepcopy(l[i])
if (b == 1):
df1.loc[(df1.Volume >= df1.Volume.quantile(0.9)) , 'status'] = 'Promotion' # Above 90 percentile Promotion
df1.loc[(df1.Volume <= df1.Volume.quantile(0.1)) , 'status'] = 'Retention' # Below 10 percentile Retention
df1.loc[(df1.Volume > df1.Volume.quantile(0.1)) & (df1.Volume < df1.Volume.quantile(0.9)), 'status'] = 'Consistent' # between 90 and 10 percentile is consistent
elif (b == 2):
df1.loc[(df1.Volume >= df1.Volume.quantile(0.8)) , 'status'] = 'Promotion' # Above 80 percentile Promotion
df1.loc[(df1.Volume <= df1.Volume.quantile(0.2)) , 'status'] = 'Retention' # Below 20 percentile Retention
df1.loc[(df1.Volume > df1.Volume.quantile(0.2)) & (df1.Volume < df1.Volume.quantile(0.8)), 'status'] = 'Consistent' # between 80 and 20 percentile is consistent
elif (b == 3):
df1.loc[(df1.Volume >= df1.Volume.quantile(0.7)) , 'status'] = 'Promotion' # Above 70 percentile Promotion
df1.loc[(df1.Volume <= df1.Volume.quantile(0.3)) , 'status'] = 'Retention' # Below 30 percentile Retention
df1.loc[(df1.Volume > df1.Volume.quantile(0.3)) & (df1.Volume < df1.Volume.quantile(0.7)), 'status'] = 'Consistent' # between 70 and 30 percentile is consistent
elif(b == 4):
df1.loc[(df1.Volume >= df1.Volume.quantile(0.6)) , 'status'] = 'Promotion' # Above 60 percentile Promotion
df1.loc[(df1.Volume <= df1.Volume.quantile(0.4)) , 'status'] = 'Retention' # Above 40 percentile Promotion
df1.loc[(df1.Volume > df1.Volume.quantile(0.4)) & (df1.Volume < df1.Volume.quantile(0.6)), 'status'] = 'Consistent' # between 60 and 40 percentile is consistent
## allocation :- allocating the amount based on the proportion of share in total volume during the month
Total = 0
for i in range(0,6):
Total = Total + l[i]['Volume'].sum()
if (level != 0):
l_amount = (df1['Volume'].sum() / Total) * amount # each level will be allocated based on the proportion of share in total volume
pro_c=df1.loc[df1.status == 'Promotion']['SkCode'].count()
ret_c=df1.loc[df1.status == 'Retention']['SkCode'].count()
pro_am = l_amount / 2 # promotion amount and retention amount will be 50% i.e half of total volume
if ((pro_c != 0) & (ret_c != 0)):
pro_all=int(pro_am / pro_c) #promotion amount will be distributed equally among promotion customers similar is case with retention customers
ret_all=int(pro_am / ret_c)
else :
pro_all = 0
ret_all = 0
df1.loc[df1.status == 'Promotion', 'allocation'] = str(pro_all)
df1.loc[df1.status == 'Retention', 'allocation'] = str(ret_all)
df1.loc[df1.status == 'Consistent', 'allocation'] = str(0)
else:
pro_c0 = df['SkCode'].count() # for level0 (l0) there will be seperate allocation and it will be allocated equally among customers
l0_all = l0amount/pro_c0
df1.loc[df1.status == 'Promotion', 'allocation'] = str(l0_all)
df1.loc[df1.status == 'Retention', 'allocation'] = str(l0_all)
df1.loc[df1.status == 'Consistent', 'allocation'] = str(l0_all)
df1 = df1.loc[(df1.Cityid == cityid) & (df1.WarehouseId == warehouseid)]
df1= df1.to_json(orient='records')
return df1
def api_name():
# Check if an ID was provided as part of the URL.
# If ID is provided, assign it to a variable.
# If no ID is provided, display an error in the browser.
if 'itemname' in request.args:
itemname = request.args['itemname']
else:
return "Error: No id field provided. Please specify an itemname."
if 'warehouseid' in request.args:
warehouseid = int(request.args['warehouseid'])
else:
return "Error: No id field provided. Please specify an warehouseid."
url = "https://uat.shopkirana.in/api/itemMaster/Getitemmasterscentral"
resp = requests.get(url)
json_data = resp.json()
if(json_data["Status"] =="OK"):
redisAesKey = datetime.today().strftime('%Y%m%d') + "1201"
jso = aes256().decrypt(json_data["Data"],redisAesKey)
js = json.loads(jso)
df = json_normalize(js)
df=df[df.WarehouseId==warehouseid]
df=df[['itemname', 'ItemId','CategoryName','SubsubcategoryName','WarehouseId']]
p= df.groupby('itemname').max()
df=p.reset_index()
Row_list =[]
for index, rows in df.iterrows():
my_list =[rows.ItemId, rows.itemname]
Row_list.append(my_list)
choices = Row_list
t=process.extract(itemname, choices, limit=10)
l=list()
for j in range(len(t)):
if (t[j][1]>50):
a=(t[j][0])
l.append(a)
item=list()
if len(l)>=1:
for i in l:
a=(i[0])
item.append(a)
l=len(item)
if l<=2:
Row_list =[]
for index, rows in df.iterrows():
my_list =[rows.itemname,rows.CategoryName, rows.ItemId]
Row_list.append(my_list)
choices = Row_list
t=process.extract(itemname, choices, limit=10)
l1=list()
for j in range(len(t)):
if (t[j][1]>75):
a=(t[j][0])
l1.append(a)
item1=list()
if len(l1)>=1:
for i in l1:
a=(i[2])
item1.append(a)
item=item1
m=len(item1)
if l<2 and m<2:
Row_list2 =[]
# l='please check your spell and try again'
for index, rows in df.iterrows():
my_list =[rows.itemname,rows.SubsubcategoryName, rows.ItemId]
Row_list2.append(my_list)
choices = Row_list2
t=process.extract(itemname, choices, limit=10)
l2=list()
for j in range(len(t)):
if (t[j][1]>75):
a=(t[j][0])
l2.append(a)
item2=list()
if len(l2)>=1:
for i in l2:
a=(i[2])
item2.append(a)
a=len(item2)
item=item2
# item_set=(parle,maggi,vim,tasty-hasty,ata,kisan kirana,rice)
# # l=json.dumps(l)
if len(item)<5:
return Response(None)
else:
return jsonify(item)
def api(self, month, year, level):
month = int(month)
year = int(year)
level = int(level)
url = "https://er15.xyz:4436/api/Customers/CRMLevelCustomerDetail?month=%s&year=%s" % (
month, year)
resp = requests.get(url)
json_data = resp.json()
if (json_data['Status'] == "OK"):
redisAesKey = datetime.today().strftime('%Y%m%d') + "1201"
jso = aes256().decrypt(json_data["Data"], redisAesKey)
js = json.loads(jso)
df = json_normalize(js)
# df=df.loc[df['IsActive'] == True]
df.loc[df.Volume == 0, 'levels'] = 'level_0'
df.loc[(df.Volume >= 1), 'levels'] = 'level_1'
df.loc[(df.Volume >= 10000) & (df.OrderCount >= 3) &
(df.BrandCount >= 5), 'levels'] = 'level_2'
df.loc[(df.Volume >= 20000) & (df.OrderCount >= 5) &
(df.BrandCount >= 10) & (df.kkVolumn >= 2000),
'levels'] = 'level_3'
df.loc[(df.Volume >= 30000) & (df.OrderCount >= 8) &
(df.BrandCount >= 20) & (df.kkVolumn >= 8000) &
((df.Selfordercount / (df.OrderCount)) * 100 > 30),
'levels'] = 'level_4'
df.loc[(df.Volume >= 75000) & (df.OrderCount >= 12) &
(df.BrandCount >= 40) & (df.kkVolumn >= 15000) &
((df.Selfordercount / (df.OrderCount)) * 100 > 60),
'levels'] = 'level_5'
dfL0 = df.loc[df.levels == 'level_0']
dfL1 = df.loc[df.levels == 'level_1']
dfL2 = df.loc[df.levels == 'level_2']
dfL3 = df.loc[df.levels == 'level_3']
dfL4 = df.loc[df.levels == 'level_4']
dfL5 = df.loc[df.levels == 'level_5']
if (level == 0):
dfL0 = dfL0.to_dict('records')
return dfL0
elif (level == 1):
dfL1 = dfL1.to_dict('records')
return dfL1
elif (level == 2):
dfL2 = dfL2.to_dict('records')
return dfL2
elif (level == 3):
dfL3 = dfL3.to_dict('records')
return dfL3
elif (level == 4):
dfL4 = dfL4.to_dict('records')
return dfL4
elif (level == 5):
dfL5 = dfL5.to_dict('records')
return dfL5