def registration(user_data): query = "INSERT INTO users (phone_no, \ password, \ first_name, \ last_name, \ email_id, \ address_line_1, \ address_line_2, \ gender, \ pin, \ dob, \ aadhar_no, \ online_status)" \ " VALUES(%s,%s, %s,%s,%s,%s,%s,%s,%s,STR_TO_DATE(%s,'%d-%m-%Y'),%s,%s)" print user_data values = (user_data['phone_no'], user_data['password'],user_data['first_name'],user_data['last_name'],\ user_data['email_id'], user_data['address_line_1'], user_data['address_line_2'],\ user_data['gender'],user_data['pin'],user_data['dob'],user_data['aadhar_no'],\ user_data['online_status']) isSuccess = True conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) conn.commit() else: isSuccess = False return isSuccess
def insertCropDetails(phone_no, land_size, land_pin, land_owner_phone, crop_var_id, \ plant_date, crop_harvest_date, organic_certified, weight_after_harvest, quality_certi,\ quality_certi_date): query = "INSERT INTO crop_plant_event (phone_no, \ land_size, \ land_pin, \ land_owner_phone, \ crop_var_id, \ plant_date, \ crop_harvest_date, \ organic_certified, \ weight_after_harvest, \ quality_certi, \ quality_certi_date)" \ " VALUES(%s,%s, %s,%s,%s,STR_TO_DATE(%s,'%d-%m-%Y'),\ STR_TO_DATE(%s,'%d-%m-%Y'),%s,%s,%s,STR_TO_DATE(%s,'%d-%m-%Y'))" values = (phone_no, land_size, land_pin, land_owner_phone, crop_var_id, \ plant_date, crop_harvest_date, organic_certified, weight_after_harvest, quality_certi,\ quality_certi_date) isSuccess = True conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) conn.commit() else: isSuccess = False return isSuccess
def insertLogisticsDetails(person_id, name, pin): query = "INSERT INTO logistics_company (company_person, \ company_name, \ company_pin)" \ " VALUES(%s,%s,%s)" values = (person_id, name, pin) query_2 = "INSERT INTO user_occupation (phone_no, \ occupation_id)" \ " VALUES(%s,%s)" values_2 = (person_id, '03') isSuccess = True conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) conn.commit() cursor.execute(query_2, values_2) conn.commit() else: isSuccess = False return isSuccess
def extract_crop(): query = "select * from crop_variety" conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query) l = cursor.fetchall() return l else: return []
def extract_area(): query = "select pin, area_name from area" conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query) l = cursor.fetchall() return l else: return []
def extract_state(): query = "select state_name from states" conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query) l = cursor.fetchall() return l else: return []
def extract_soldData(): query = "select phone_no, y.crop_request_entry, crop_name, crop_type, crop_weight, y.crop_harvest_date, reserve_price from (select crop_var_id, crop_name, crop_type from crop_variety) as x natural join (select phone_no, crop_var_id, c.crop_harvest_date, crop_weight, reserve_price, s.crop_request_entry from (select crop_id, crop_var_id, crop_harvest_date from crop_plant_event) as c join (select phone_no, crop_id, crop_weight, reserve_price, crop_request_entry from make_crop_purchase_sell where req_type='S') as s where c.crop_id=s.crop_id) as y" conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query) l = cursor.fetchall() return l else: return []
def extract_userdetails(username): query = "select * from users where phone_no=%s" values = (username, ) conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) l = cursor.fetchall() return l else: return []
def farmer_notification(username): query = "select x.phone_no, x.first_name, x.last_name, x.pin, y.accept_price, y.crop_request_entry from (select * from users) as x join (select req_accepter, accept_price, crop_request_entry from \ make_crop_purchase_sell where phone_no=%s and req_accepter is not null and req_type='S') as y where x.phone_no=y.req_accepter" values = (username, ) conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) l = cursor.fetchall() return l else: return []
def extract_occupations(phone_no): query = "select occupation_id,occupation_name from occupation natural join user_occupation \ where occupation.occupation_id=user_occupation.occupation_id and phone_no=%s" values = (phone_no, ) conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) l = cursor.fetchall() return l else: return []
def extract_hirvestedcrop(phone_no): query = "select a.crop_var_id, a.crop_name, a.crop_type from crop_variety as a \ join (select crop_id from crop_plant_event where phone_no=%s) \ as e where e.crop_id=a.crop_var_id" values = (phone_no, ) conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) l = cursor.fetchall() return l else: return []
def insertOccupation(phone_no, id): query = "INSERT INTO user_occupation (phone_no, \ occupation_id)" \ " VALUES(%s,%s)" values = (phone_no, id) isSuccess = True conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) conn.commit() else: isSuccess = False return isSuccess
def auth_login(phone_no, password): query = "select exists(select * from users where phone_no=%s and password=%s)" values = (phone_no, password) isSuccess = False conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) l = cursor.fetchone() if l[0] == 1: isSuccess = True else: isSuccess = False return isSuccess
def insertBid(crop_request_entry, trader_no, bid_amount): query = "INSERT INTO bids (crop_request_entry, \ trader_no, \ bid_amount)" \ " VALUES(%s,%s,%s)" values = (crop_request_entry, trader_no, bid_amount) isSuccess = True conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) conn.commit() else: isSuccess = False return isSuccess
def send_shipment_request(crop_request_entry, \ src_person, src_addr, src_pin, w_price, dest_person, dest_addr, dest_pin): query = "insert into crop_shipment_request (crop_request_entry, src_person, \ src_addr, src_pin, w_price, dest_person, dest_addr, dest_pin) \ values (%s, %s, %s, %s, %s, %s, %s, %s)" values = (crop_request_entry, src_person, src_addr, src_pin, w_price, dest_person, dest_addr, dest_pin) isSuccess = True conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) conn.commit() else: isSuccess = False return isSuccess
def sellCrop(phone_no, req_type, crop_id, req_pin, crop_weight, entry_time, notes): query = "INSERT INTO make_crop_purchase_sell (phone_no, \ req_type, \ crop_id, \ req_pin, \ crop_weight, \ entry_time, \ notes)" \ " VALUES(%s,%s, %s,%s,%s, STR_TO_DATE(%s,'%d-%m-%Y'),%s)" values = (phone_no, req_type, crop_id, req_pin, crop_weight, entry_time, notes) isSuccess = True conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query, values) conn.commit() else: isSuccess = False return isSuccess
def perform_AUCTION(): query = "select * from (bids natural join (select crop_request_entry, \ max(bid_amount) as bid_amount from bids group by crop_request_entry) as a)" isSuccess = False conn = connectionMYSQL() if conn.is_connected(): cursor = conn.cursor() cursor.execute(query) l = cursor.fetchall() conn.commit() for crop_request_entry, bid_amount, trader_no in l: sub_query = "update make_crop_purchase_sell set req_accepter=%s, \ accept_price=%s where crop_request_entry=%s" sub_values = (crop_request_entry, bid_amount, trader_no) cursor.execute(sub_query, sub_values) conn.commit() sub_query = "delete from bids where crop_request_entry=%s and trader_no=%s" sub_values = (crop_request_entry, trader_no) cursor.execute(sub_query, sub_values) conn.commit() sub_query = "select * from bids" cursor.execute(sub_query) l = cursor.fetchall() conn.commit() for crop_request_entry, trader_no, bid_amount in l: sub_query = "delete from bids where crop_request_entry=%s and trader_no=%s" sub_values = (crop_request_entry, trader_no) cursor.execute(sub_query, sub_values) conn.commit() isSuccess = True return isSuccess