In the previous post, I created overall structure and the module for add Merchant. In this post, I will cover Module that will help us create or update an opportunity. Let’s say we have a merchant “SampleMerchant” we have added using “Add Merchant” module, and now we want to create an opportunity under the merchant.
On selection of “Add/Update Opportunity”, we invoke a function SalesUpdate() that shall handle the rest for us.
def SalesUpdate():
cust_list = getCustList()
conn = sqlite3.connect('MerchantDB.db')
df = pd.read_sql_query("""SELECT "Merchant Name" from MERCHANTS""", conn)
conn.close()
cust_list = df['Merchant Name'].tolist()
mname = st.selectbox("Select Customer", cust_list)
M_id = FindMerchantIDfromName(mname)
M_currentstatus = CheckinPipeline(M_id)
if M_currentstatus[0] == -2:
st.info("This is 1st entry into Pipeline. Creating Table")
RecordUserUpdate(M_id)
elif M_currentstatus[0] == -1:
st.info("Selected Merchant not in current pipe. Now adding")
RecordUserUpdate(M_id)
else:
st.info("Selected Merchant in Pipeline. Fetching details")
RecordUserUpdate(M_id, M_currentstatus[1:]) SalesUpdate() checks whether the merchant already has opportunity created against it or not, to begin with using CheckinPipeline(). In case it doesn’t, it prompts the user to add opportunity. In case it does, it loads the opportunity status and allows the user to update. If the merchant is not there in the database at all (FYI: this wouldn’t happen in my implementation since I have restricted the merchant input to the existing list), it returns appropriate code.
def CheckinPipeline(M_id):
conn = sqlite3.connect('MerchantDB.db')
c = conn.cursor()
c.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='PIPELINE' ''')
if c.fetchone()[0] == 1: #this means that table exists
cursor = conn.execute("""SELECT * FROM PIPELINE where "Merchant ID"=?;""", (M_id,))
m_record = cursor.fetchall()
if len(m_record) == 0:
return([-1]) #table exists but not merchant record
else:
return([1] + m_record)
else: #table doesn't exist
return([-2])Figure 2.1 shows scenario wherein for the selected merchant, we are creating any opportunity for the first time. In case the sales team discovers new opportunities with the same merchant, they can update the same in the Sales Pitch(es) section. It is therefore, a multiselect frame. Additionally, owner, sales stage, comments, engagement start date, expected closure date can be added as well. Sales stage and Comments carry timestamps to allow assessment of pipeline movement, and to also provide handy notes to the sales team. While comments auto-picks date (of today), sales stage date is configurable with today as default. Commercials can also be added during the sales stages or at closure. You can customize this field in Streamlit to suit your usage. For example, in our case, revenue is a % of transaction so I have configured it as a % field, and defined a floor which ensures no one is making a loss making sale :). In case the opportunity is being created afresh or is being updated, we invoke function RecordUserUpdate() to handle the updation.
Please note: I have not used the field “Addl Info” in my production script, and have instead used few other ones as that’s what our use case demanded. I have hidden those fields since those are specific and confidential to our business. However, I hope the idea is clear.
def RecordUserUpdate(M_id, *args):
df = pd.read_csv('Validations.csv')
M_owners = df['Owners'].dropna()
M_pitches = df['Pitch'].dropna()
M_stages = df['Stage'].dropna()
cols = ['Merchant ID', 'Owner','Pitch', 'Stage', 'Stage_timelines','Comments', 'Engagement_Start', 'Expected Closure', 'Commercials','Addl Info']
M_owners = M_owners.tolist()
M_pitches = M_pitches.tolist()
M_stages = M_stages.tolist()
M_newstatus = [] * len(cols)
if len(args) > 0:
M_currentstatus = args[0][0]
st.markdown("_**Current Status**_")
st.table(pd.DataFrame([M_currentstatus], index = [M_id], columns = cols).T)
owner_current, pitch_current, stage_current, stage_timelines, comments = M_owners.index(M_currentstatus[1]), M_currentstatus[2], M_currentstatus[3], M_currentstatus[4],M_currentstatus[5]
pitch_current = re.findall(r'[A-Z][A-Za-z\s\-\"]{5,}',pitch_current)
stage_current_index = M_stages.index(re.findall(r'[1-9][A-Za-z\s/\-]{3,}',stage_current)[0])
engagement_start_date = M_currentstatus[6]
expected_closure_date = M_currentstatus[7]
commercials = M_currentstatus[8]
else:
owner_current, pitch_current, stage_current_index, comments = 0, M_pitches[0], 0, ""
engagement_start_date = date.today()
expected_closure_date = None
commercials = None
stage_current = M_stages[stage_current_index]
stage_timelines = str(date.today()) + ": " + M_stages[stage_current_index] + ";" + "\n"
st.write("-----------------------------------------------------------------------------------------------")
st.markdown("_**Update Status**_")
owner = st.selectbox("Select owner", M_owners, owner_current)
pitch = st.multiselect("Sales Pitch(es)", M_pitches, default = pitch_current)
stage_new = st.selectbox("Sales Stage", M_stages, stage_current_index)
if M_stages.index(stage_new) != stage_current_index:
stage_date = st.date_input("Date of change to stage: " + stage_new)
stage_timelines = str(stage_date) + ": " + stage_new + ";" + "\n" + str(stage_timelines or '')
comment = st.text_area("Enter merchant update")
if comment:
comments = str(date.today()) + ": " + comment + ";" + "\n" + str(comments or '')
expected_closure_date = st.date_input("Expected Closure")
commercials = st.number_input("Commercials")
M_newstatus[0:8] = [M_id, owner, pitch, stage_new, stage_timelines, comments, engagement_start_date, expected_closure_date, commercials ]
if len(args) == 0:
M_newstatus[9:] = [None] * (len(cols) - len(M_newstatus))
else:
M_newstatus[9:] = M_currentstatus[9:]
df = pd.DataFrame([M_newstatus], index = [M_id], columns = cols)
st.write("-----------------------------------------------------------------------------------------------")
st.markdown("_**Update Summary**_")
st.table(df.T)
st.write("-----------------------------------------------------------------------------------------------")
if st.button("Update to DB"):
for col in df.columns:
df[col] = df[col].astype('str')
if len(args) == 0:
PipelineUpdatetoDB(df, 'Insert')
else:
PipelineUpdatetoDB(df, 'Update')In case you are not familiar with Python, *args is used to send a variable count of parameters to a function. It creates a list, and I have used the length check to determine if it is a new opportunity or an update (if length = 0 => new opportunity else update). Since Stage is a selectbox, it takes just one input at a time. Since I want to show existing stage in case of opportunity stage, I have used regex to pull out index of current stage (it follows a LIFO, so 1st element, as referenced by [0] gives us current stage. Have used regex for finding the various product pitches that are active, and used the list output to pre-load the Pitch field. As mentioned earlier, Comments and Stage_timelines carry timestamps of successive updates.
Once done, I have created a quick preview just in case user wants to make changes before pushing the update to Database.
On the click of “Update to DB” button, I call PipelineUpdatetoDB() function which handles the update into database.
def PipelineUpdatetoDB(df, action):
conn = sqlite3.connect('MerchantDB.db')
cursor = conn.cursor()
if action == 'Insert':
df.to_sql('PIPELINE', conn, if_exists = 'append', index = False)
st.info("Successfully added record")
elif action == 'Update':
cols = ['Merchant ID', 'Owner', 'Pitch', 'Stage', 'Stage_timelines','Comments', 'Engagement_Start', 'Expected Closure', 'Commercials','Addl Info']
query = """UPDATE PIPELINE SET "Merchant ID" = ?, Owner = ?,Pitch = ?, Stage = ?, Stage_timelines = ?,Comments = ?, Engagement_Start = ?, "Expected Closure" = ?, Commercials = ?, Addl_Info = ? WHERE "Merchant ID" = ?"""
columnValues = (*df.iloc[0].tolist(), df.iloc[0,0])
cursor.execute(query, columnValues)
st.info("Record Updated")
conn.commit()
conn.close()
Since we are on the topic, here’s the function which reads from database table with Pipeline information. It shall be used at the time of creating scorecard and few other places.
def ReadPipelineDB():
conn = sqlite3.connect('MerchantDB.db')
df = pd.read_sql_query("""SELECT * from PIPELINE""", conn)
st.write(df)
conn.close()Cool, we can now add merchants and add/update opportunities. With this in place, focus now shifts to reports.