surendraelectronics/Datalab
0
1"""2This application contains the code related to the3new electric car application.4"""5 6__author__ = 'Surendra Reddy'7__version__ = '2.0'8__maintainer__ = 'Surendra Reddy'9__email__ = 'surendraelectronics@gmail.com'10__status__ = 'Prototype'11 12print('# ' + '=' * 78)13print('Author: ' + __author__)14print('Version: ' + __version__)15print('Maintainer: ' + __maintainer__)16print('Email: ' + __email__)17print('Status: ' + __status__)18print('# ' + '=' * 78)19 20# Required packages importing21import streamlit as st22#from annotated_text import annotated_text23#from streamlit_metrics import metric, metric_row24#from st_card import st_card25from st_aggrid import AgGrid,GridUpdateMode26from st_aggrid.grid_options_builder import GridOptionsBuilder27from streamlit_lottie import st_lottie28 29import datetime30import calendar31from datetime import date32import pandas as pd33import plotly.express as px34import plotly.graph_objects as go35import requests36 37# Database Creation & Connection38import sqlite339from sqlite3 import Connection40import hashlib41 42 43# Header template44html_temp = """45 <body style="background-color:red;">46 <div style="background-color:tomato;padding:10px">47 <h3 style="color:white;text-align:center;"> Your Electric Car🚗</h3>48 </div>49 </body>50"""51# Hide Styles52hide_streamlit_style = """53 <style>54 #MainMenu {visibility: hidden;}55 footer {visibility: hidden;}56 </style>57 """58# Page Config details59st.set_page_config(60 page_title = 'CarApp',61 page_icon = "🚗",62 layout = "wide",63 initial_sidebar_state = "expanded"64 )65 66contact_form = """67<form action="https://formsubmit.co/surendraelectronics@outlook.com" method="POST">68 <input type="hidden" name="_captcha" value="false">69 <input type="text" name="name" placeholder="Your name" required>70 <input type="email" name="email" placeholder="Your email" required>71 <textarea name="message" placeholder="Your message here"></textarea>72 <button type="submit">Send</button>73</form>74"""75 76URI_SQLITE_DB = "Bookings.db"77fridayList = []78 79def make_hashes(password):80 """Hashes function81 82 Args:83 password ([type]): password information84 85 Returns:86 [type]: hashkey value87 """88 return hashlib.sha256(str.encode(password)).hexdigest()89 90def check_hashes(password,hashed_text):91 if make_hashes(password) == hashed_text:92 return hashed_text93 return False94 95# Ag-Grid Implementation96def grid_table(df):97 gb = GridOptionsBuilder.from_dataframe(df)98 gb.configure_pagination(enabled=True)99 gb.configure_side_bar()100 gb.configure_selection('multiple', use_checkbox=True, groupSelectsChildren=True, groupSelectsFiltered=True)101 gb.configure_default_column(groupable=True, value=True, enableRowGroup=True, editable=True)102 gridOptions = gb.build()103 grid_response = AgGrid(df, 104 gridOptions = gridOptions, 105 enable_enterprise_modules = True,106 fit_columns_on_grid_load = False,107 width='100%',108 theme = "dark",109 update_mode = GridUpdateMode.SELECTION_CHANGED,110 allow_unsafe_jscode=True)111 df = grid_response['data']112 selected_rows = grid_response["selected_rows"]113 return df,selected_rows114 115# """116# check_same_thread = False is added to avoid same thread issue117# """118conn = sqlite3.connect(URI_SQLITE_DB, check_same_thread=False)119c = conn.cursor()120 121@st.cache(hash_funcs={Connection: id})122def get_connection(path: str):123 """[summary]124 125 Args:126 path (str): path of the sqllite db127 128 Returns:129 [type]: coonection value130 """131 return sqlite3.connect(path, check_same_thread=False)132 133def create_usertable(conn: Connection):134 """ Table creation function135 136 Args:137 conn (Connection): Connection138 """139 c.execute('CREATE TABLE IF NOT EXISTS userstable(username TEXT PRIMARY KEY,password TEXT)')140 141def add_userdata(conn: Connection,username,password):142 """Add User Infor to table143 144 Args:145 conn (Connection): Connection146 username ([type]): username147 password ([type]): password148 """149 c.execute('INSERT INTO userstable(username,password) VALUES (?,?)',(username,password))150 conn.commit()151 152def create_cardetailstable(conn: Connection):153 """ Table creation function154 155 Args:156 conn (Connection): Connection157 """158 c.execute('CREATE TABLE IF NOT EXISTS cardetailstable(username TEXT PRIMARY KEY,battery TEXT, model TEXT,tyre TEXT,basecost FLOAT,\159 totalcost FLOAT,purchase DATE, purchaseday TEXT)')160 161def add_cardetails(conn: Connection,username,battery,model,tyre,basecost,totalcost,purchase,purchaseday):162 c.execute('INSERT OR REPLACE INTO cardetailstable(username,battery,model,tyre,basecost,totalcost,purchase,purchaseday) \163 VALUES (?,?,?,?,?,?,?,?)',(username,battery,model,tyre,basecost,totalcost,purchase,purchaseday))164 conn.commit()165 166def login_user(conn: Connection,username,password):167 """User Login Function168 169 Args:170 conn (Connection): connection171 username ([type]): username172 password ([type]): password173 174 Returns:175 [type]: data from tabke176 """177 c.execute('SELECT * FROM userstable WHERE username =? AND password = ?',(username,password))178 data = c.fetchall()179 return data180 181def view_all_items(conn: Connection):182 c.execute('SELECT * FROM cardetailstable')183 data = c.fetchall()184 return data185 186 187# css function188def apply_css(file_name):189 """Apply the css to contact form190 """191 with open(file_name) as f:192 st.markdown(f"<style>{f.read()}</style>", unsafe_allow_html=True)193 194def fetchFriday():195 global fridayList196 today = date.today()197 year, month = today.year, today.month198 199 n_months = 12200 friday = calendar.FRIDAY201 202 for _ in range(n_months):203 # get last friday204 c = calendar.monthcalendar(year, month)205 day_number = c[-1][friday] or c[-2][friday]206 207 # display the found date208 #print(date(year, month, day_number).isoformat()) 209 210 fridayList.append(date(year, month, day_number).isoformat())211 212 # refine year and month213 if month < 12:214 month += 1215 else:216 month = 1217 year += 1218 219def load_lottieurl(url: str):220 r = requests.get(url)221 if r.status_code != 200:222 return None223 return r.json()224 225# main function226def main():227 """228 This function contains the streamlit code details229 """230 st.markdown(html_temp, unsafe_allow_html=True)231 #st.markdown(hide_streamlit_style, unsafe_allow_html=True)232 st.image('https://imgd.aeplcdn.com/0x0/cw/static/landing-banners/homepage-d-2021.jpg?v=07072021')233 st.sidebar.info("""234 ## Packages:235 **Streamlit, st-card, streamlit-aggrid, streamlit-lottie, requests**236 - Installation: 237 - **pip install -r requirements.txt**238 """)239 url = "https://assets7.lottiefiles.com/private_files/lf30_mn61zlcj.json"240 lottie_url = url241 lottie_json = load_lottieurl(lottie_url)242 st_cont = st.sidebar.container()243 #st.sidebar.title("📖 SciLit ")244 with st_cont:245 st_lottie(lottie_json,height =100,width =200)246 247 # Add a selectbox to the sidebar:248 add_selectbox = st.sidebar.selectbox(249 'Navigation👇', 250 ('👤Login','👥Signup','🅰About')251 )252 253 if add_selectbox == '🅰About':254 st.sidebar.info('Version 1.0')255 st.header(":mailbox: Get In Touch With Me!")256 st.markdown(contact_form,unsafe_allow_html=True)257 apply_css("style/style.css")258 259 # elif add_selectbox == '💿Configure':260 # # annotated_text(261 # # ("🙏Welcome", "to ⚡ electric", "#8ef" ),262 # # ("🏎car", "application.", "#afa" ),263 # # )264 # st.write('')265 # with st.expander('🚘Car Specifications', expanded=True):266 # # Using the "with" syntax267 # with st.form(key='car_form'):268 # text_input = st.text_input(label='Enter User Name')269 # cols = st.columns (3)270 # name = ['🔋Battery', '🎡Wheel', '☸Tires']271 # options = [['40kph','60kph','80kph'], ['model1','model2','model3'],['Eco','Performance','Racing']]272 # for i, col in enumerate(cols):273 # col.selectbox(name[i] + f'', options[i], key=i)274 # submit_button = st.form_submit_button(label='Submit')275 # #st.subheader('Choose your favourite features.')276 277 elif add_selectbox == '👥Signup':278 st.subheader("Create New Account")279 new_user = st.text_input("Username","") 280 new_password = st.text_input("Password",type='password')281 282 if st.button("Signup"):283 create_usertable(conn)284 add_userdata(conn,new_user,make_hashes(new_password))285 st.success("Account created successfully. Please Login to application.")286 287 elif add_selectbox == "👤Login":288 global fridayList289 fetchFriday()290 st.sidebar.info("Hint: admin,admin")291 st.subheader("**Please Login to the page !**")292 293 username = st.sidebar.text_input("Username","")294 password = st.sidebar.text_input("Password",type='password')295 # Create card details table296 create_cardetailstable(conn)297 298 if st.sidebar.checkbox("Login"):299 hashed_pswd = make_hashes(password)300 result = login_user(conn,username,check_hashes(password,hashed_pswd))301 if result:302 st.success("Logged In as {0!s}".format(username))303 st.subheader('')304 st.subheader('')305 st.subheader('')306 307 with st.expander('Electric Car Booking'):308 st.subheader("Please fill the below form 👇")309 # Using the "with" syntax310 with st.form(key='car_form',clear_on_submit=True):311 error = False312 today = datetime.date.today()313 #st.write(fridayList)314 name = ['🔋Battery', '🎡Wheel', '☸Tires']315 textOptions=['CarBattery', 'CarModel', 'CarTyre']316 options = [['select','40kph','60kph','80kph'], ['select','model1','model2','model3'],['select','Eco','Performance','Racing']]317 text_input = st.text_input('👥Enter User Name',help="UserName")318 battery_input = st.selectbox(name[0], options[0], key=1,help=textOptions[0])319 model_input = st.selectbox(name[1], options[1], key=2,help=textOptions[1])320 tyre_input = st.selectbox(name[2], options[2], key=3,help=textOptions[2])321 purchase_date = st.date_input('📅Planned Purchase Date', today,help='Purchase Date') 322 323 #cols = st.columns(3)324 #choice ={}325 326 # for i, col in enumerate(cols):327 # choice = col.selectbox(name[i], options[i], key=i,help=textOptions[i])328 # st.write(choice[0::][0::])329 330 submit_button = st.form_submit_button(label='Submit🚇')331 if submit_button:332 finalPrice =float(0.0)333 basePrice = float(12.0)334 battery60Price=float(0.0)335 battery80Price=float(0.0)336 model2Price=float(0.0)337 model3Price=float(0.0)338 tyrePPrice=float(0.0)339 tyreRPrice=float(0.0)340 341 # Check all the validations342 if text_input =='':343 st.error('User Name should not be blank')344 error = True345 if battery_input =='select' or model_input =='select' or tyre_input =='select':346 st.error('Please select valid battery or model or tyre values')347 error = True348 if model_input == 'model3':349 if battery_input =='40kph':350 st.error('Model 3 - Only available with 60 and 80 kwh batteries. Please change the battery size.')351 error = True352 if tyre_input == 'Performance':353 if model_input =='model1':354 st.error('Performance - Only available with wheel model2 and model3. Please change the whhel model.')355 error = True356 357 if not error:358 # Additional Price359 if battery_input == '60kph':360 battery60Price = float(2.5)361 if battery_input == '80kph':362 battery80Price = float(6.0)363 if model_input == 'model2':364 model2Price = float(150)365 if (battery_input == '60kph' or battery_input == '80kph') and model_input == 'model3':366 model3Price = float(350)367 if (tyre_input == 'Performance') and (model_input == 'model3' or model_input == 'model2'):368 tyrePPrice = float(80)369 if (tyre_input == 'Racing') and (model_input == 'model3'):370 tyreRPrice = float(150) 371 372 weekday = purchase_date.strftime("%A")373 374 # Convert in to time stamp values375 year, month, day = purchase_date.year, purchase_date.month, purchase_date.day376 377 # Apply the Friday Discount378 checkdate = date(year, month, day).isoformat()379 val = any(checkdate in x for x in fridayList)380 if val:381 st.write("Last Friday Discount applied to purchase")382 discountedPrice = float(2)383 finalPrice = basePrice + battery60Price + battery80Price + model2Price + model3Price + tyrePPrice + tyreRPrice - discountedPrice384 else:385 discountedPrice = float(0)386 finalPrice = basePrice + battery60Price + battery80Price + model2Price + model3Price + tyrePPrice + tyreRPrice + discountedPrice #st.write("no")387 388 # Add final details to the database389 add_cardetails(conn,text_input,battery_input,model_input,tyre_input,basePrice,finalPrice,checkdate,weekday)390 391 # st.markdown(f""" ##### Car Specification Details 👤UserName : {text_input}, \392 # 🔋Battery : {battery_input}, 🎡Wheel : {model_input}, ☸Tyre : {tyre_input}, \393 # TotalCost(Euro): {finalPrice}""")394 # st.info("👏Congratulations!! You booking is successful.")395 st.balloons()396 397 st.header('')398 with st.expander('📊Reports'):399 st.subheader("View Booking Details")400 #result = view_all_items(conn)401 dat = sqlite3.connect(URI_SQLITE_DB)402 query = dat.execute("SELECT * From cardetailstable")403 cols = [column[0] for column in query.description]404 results= pd.DataFrame.from_records(data = query.fetchall(), columns = cols)405 #st.dataframe(results)406 407 # fig = go.Figure(data=[go.Table(408 # columnwidth =[2,1,1,1,1,1,1,1],409 # header=dict(values=list(results.columns),410 # fill_color='#FD8E72',411 # align='center'),412 # cells=dict(values=[results.username, results.battery, results.model, results.tyre, results.basecost, results.totalcost, results.purchase,results.purchaseday],413 # fill_color='#E5ECF6',414 # align='left'))415 # ]) 416 # fig.update_layout(margin=dict(l=5,r=5,b=10,t=10),417 # paper_bgcolor='#F5F5F5')418 419 # st.write(fig)420 421 # labels = [results.columns[2],results.columns[1],results.columns[3]]422 # values = [results.model,results.battery, results.tyre]423 424 # fig = go.Figure(data=[go.Pie(labels=labels, values=values)])425 # st.write(fig)426 427 #AgGrid(results)428 res,sel = grid_table(results)429 430 st.header('')431 with st.expander('✨Metrics'):432 # Metric Details433 dat = sqlite3.connect(URI_SQLITE_DB)434 query = dat.execute("SELECT count(*) From cardetailstable")435 data = query.fetchall()436 #st.metric(label="Current🚗Bookings", value=data[0][0], delta=1)437 438 #Battery439 query40 = dat.execute("select count(*) FROM cardetailstable where battery='40kph'")440 data40 = query40.fetchall()441 query60 = dat.execute("select count(*) FROM cardetailstable where battery='60kph'")442 data60 = query60.fetchall()443 query80 = dat.execute("select count(*) FROM cardetailstable where battery='80kph'")444 data80 = query80.fetchall()445 446 # Wheels447 querym1 = dat.execute("select count(*) FROM cardetailstable where model='model1'")448 datam1 = querym1.fetchall()449 querym2 = dat.execute("select count(*) FROM cardetailstable where model='model2'")450 datam2 = querym2.fetchall()451 querym3 = dat.execute("select count(*) FROM cardetailstable where model='model3'")452 datam3 = querym3.fetchall() 453 454 # Tyre455 queryt1 = dat.execute("select count(*) FROM cardetailstable where tyre='Eco'")456 datat1 = queryt1.fetchall()457 queryt2 = dat.execute("select count(*) FROM cardetailstable where tyre='Performance'")458 datat2 = queryt2.fetchall()459 queryt3 = dat.execute("select count(*) FROM cardetailstable where tyre='Racing'")460 datat3 = queryt3.fetchall() 461 462 #st.write("## Here's a single figure")463 #metric("🚗Bookings", data[0][0])464 st.write("")465 st.write("")466 467 #st_card('🚗Bookings', value=data[0][0], delta=1)468 469 st.markdown("## Car Specification Details")470 st.write("")471 st.write("")472 473 first_kpi, second_kpi, third_kpi = st.columns(3)474 475 with first_kpi:476 st.markdown("**🔋Battery**")477 st.markdown(f"<h3 style='text-align: left; color: green;'>40kph: {data40[0][0]}</h3>", unsafe_allow_html=True)478 st.markdown(f"<h3 style='text-align: left; color: green;'>60kph: {data60[0][0]}</h3>", unsafe_allow_html=True)479 st.markdown(f"<h3 style='text-align: left; color: green;'>80kph: {data80[0][0]}</h3>", unsafe_allow_html=True)480 481 with second_kpi:482 st.markdown("**🎡Wheel**")483 st.markdown(f"<h3 style='text-align: left; color: blue;'>Model1: {datam1[0][0]}</h3>", unsafe_allow_html=True)484 st.markdown(f"<h3 style='text-align: left; color: blue;'>Model2: {datam2[0][0]}</h3>", unsafe_allow_html=True)485 st.markdown(f"<h3 style='text-align: left; color: blue;'>Model3: {datam3[0][0]}</h3>", unsafe_allow_html=True)486 487 with third_kpi:488 st.markdown("**☸Tyre**")489 st.markdown(f"<h3 style='text-align: left; color: grey;'>Eco: {datat1[0][0]}</h3>", unsafe_allow_html=True)490 st.markdown(f"<h3 style='text-align: left; color: grey;'>Performance: {datat2[0][0]}</h3>", unsafe_allow_html=True)491 st.markdown(f"<h3 style='text-align: left; color: grey;'>Racing: {datat3[0][0]}</h3>", unsafe_allow_html=True)492 493 # metric_row(494 # {495 # "🔋40Battery": data40[0][0],496 # "🔋60Battery": data60[0][0],497 # "🔋80Battery": data80[0][0],498 # "🎡Model1": datam1[0][0],499 # "🎡Model2": datam2[0][0],500 # "🎡Model3": datam3[0][0], 501 # }502 # ) 503 504 else:505 st.warning("Incorrect Userid/Password")506 507 else:508 st.write('Reporting - Cars Status.') 509 510# main function call511if __name__ == '__main__':512 main()513