def add_customer(): 'take data from customer form block and put it into database' data_for_customer = self.read_form() data = (data_for_customer["ac_name"].strip(),\ data_for_customer["ac_phone"].strip(),\ data_for_customer["ac_address"].strip(),\ data_for_customer["ac_details"].strip()) db("databases/main.db").query_statment( """INSERT INTO customers (name , phone ,address , details) VALUES (? , ? , ? , ?)""", data)
def add_customer_project(): 'take data from customer and project form blocks and put it into database' add_customer() id = db("databases/main.db").query_statment( "SELECT id FROM customers WHERE phone = ?", (self.read_form()["ac_phone"], ), True)[0][0] data_for_project = self.read_form() data = (id,\ data_for_project["ap_description"].strip(),\ data_for_project["ap_price"].strip(),\ data_for_project["ap_deal_date"].strip(),\ data_for_project["ap_end_date"].strip(),\ data_for_project["ap_status"].strip(),\ data_for_project["ap_details"].strip()) db("databases/main.db").query_statment( """INSERT INTO projects (customer_id , description , paied , deal_date, deal_end , status ,details) VALUES (?,?,?,?,?,?,?)""", data)
def add_customer_project_transaction(): 'take data from customer , project and transaction form blocks and put it into database' add_customer_project() customer_id = db("databases/main.db").query_statment( "SELECT id FROM customers WHERE phone = ?", (self.read_form()["ac_phone"], ), True)[0][0] project_id = db("databases/main.db").query_statment( "SELECT id FROM projects WHERE customer_id = ?", (customer_id, ), True)[0][0] print(project_id) data_for_transaction = self.read_form() data = (project_id,\ data_for_transaction["at_trans_date"],\ data_for_transaction["at_paied"],\ data_for_transaction["at_notes"]) db("databases/main.db").query_statment( """INSERT INTO transactions (project_id , transaction_date ,paied ,details) VALUES (?,?,?,?)""", data)
def check_duplicate(self): 'check duplicated phone number in table ,return "boolean and phone value"' res = {"status": False, "phone": False} data = self.read_form() database = db("databases/main.db") check_phone = (data["ac_phone"], ) if database.query_statment("SELECT * FROM customers WHERE phone = ?", check_phone, True): res["phone"] = data["ac_phone"] res["status"] = True return res
def search(self): 'do every thing for searching , return list of results or false' res = {} #all data result will return from this dict read_data = self.criteria() #read_data for query ["data"] [0] = query [1]="search_by" [2]="sort" [3]="order" data = {\ "query":read_data["data"][0] ,\ "search_by":read_data["data"][1],\ "sort":read_data["data"][2],\ "order":read_data["data"][3],\ } print(data) if read_data["data"][0]: #to prevent result in empty entry #search depend on pattern if read_data["pattern"] == "contain": res["count"] = db("databases/main.db").query_statment( """ SELECT count(*) FROM customers WHERE {} LIKE ? ORDER BY {} {} """.format(data["search_by"], data["sort"], data["order"]), (f'%{data["query"]}%', ), True)[0][0] res["query"] = db("databases/main.db").query_statment( """ SELECT * FROM customers WHERE {} LIKE ? ORDER BY {} {} """.format(data["search_by"], data["sort"], data["order"]), (f'%{data["query"]}%', ), True) return res elif read_data["pattern"] == "start_with": res["count"] = db("databases/main.db").query_statment( """ SELECT count(*) FROM customers WHERE {} LIKE ? ORDER BY {} {} """.format(data["search_by"], data["sort"], data["order"]), (f'{data["query"]}%', ), True)[0][0] res["query"] = db("databases/main.db").query_statment( """ SELECT * FROM customers WHERE {} LIKE ? ORDER BY {} {} """.format(data["search_by"], data["sort"], data["order"]), (f'{data["query"]}%', ), True) return res elif read_data["pattern"] == "end_with": res["count"] = db("databases/main.db").query_statment( """ SELECT count(*) FROM customers WHERE {} LIKE ? ORDER BY {} {} """.format(data["search_by"], data["sort"], data["order"]), (f'%{data["query"]}', ), True)[0][0] res["query"] = db("databases/main.db").query_statment( """ SELECT * FROM customers WHERE {} LIKE ? ORDER BY {} {} """.format(data["search_by"], data["sort"], data["order"]), (f'%{data["query"]}', ), True) return res else: res["count"] = "" res["query"] = "" return res
def read_projects(self): res = {"project": "", "transaction": "", "remaining": ""} api = self.get_json() data = db("databases/main.db").query_statment(""" SELECT id ,deal_date , paied , deal_end , description, status , details FROM projects Where customer_id = ? """, (api["id_selected"], ), fetch=True) if not data: html = """ <tr id="project_table" class="table_head"> <td>رقم المشروع</td> <td>تاريخ الاتفاق</td> <td>المبلغ المتفق علية</td> <td>تاريخ التسليم</td> <td>وصف المشروع</td> <td>حالة المشروع</td> <td>تفاصيل اخرى</td> </tr><tr> """ else: html = """ <tr class="table_head"> <td>رقم المشروع</td> <td>تاريخ الاتفاق</td> <td>المبلغ المتفق علية</td> <td>تاريخ التسليم</td> <td>وصف المشروع</td> <td>حالة المشروع</td> <td>تفاصيل اخرى</td> </tr>""" for row in data: html += "<tr>" for cell in row: html += "<td>" + str(cell) + "</td>" html += "</tr>" res["project"] = html res["transaction"] = "" try: #query all transaction for specific project id #calculate remaining deal = db("databases/main.db").query_statment(""" SELECT paied FROM projects WHERE id = ? """, (api["project_id"], ), fetch=True)[0][0] try: paied = db("databases/main.db").query_statment( """ SELECT sum(paied) FROM transactions where project_id = ? """, (api["project_id"], ), fetch=True)[0][0] res["remaining"] = int(deal) - int(paied) except Exception as e: print(e) data = db("databases/main.db").query_statment(""" SELECT id , transaction_date , paied , details FROM transactions where project_id = ? """, (api["project_id"], ), fetch=True) if not data: html = """ <tr id="project_table" class="table_head"> <td>رقم المعاملة</td> <td>تاريخ المعاملة</td> <td>المبلغ المدفوع</td> <td>ملاحظات</td> </tr> """ else: html = """ <tr id="project_table" class="table_head"> <td>رقم المعاملة</td> <td>تاريخ المعاملة</td> <td>المبلغ المدفوع</td> <td>ملاحظات</td> </tr>""" for row in data: html += "<tr>" for cell in row: html += "<td>" + str(cell) + "</td>" html += "</tr>" res["transaction"] = html except Exception as e: print(e) return res
#decoment next line in pythonanywhere | change mysite to flask directory name. #os.chdir(os.getcwd()+"/mysite") from modules.database import db from views.home import home_ from views.about import about_ from views.new_customer import new_customer_ from views.setting import setting_ from views.search_customer import search_customer_ from views.search_project import search_project_ from views.help import help_ app = Flask(__name__) app.config.from_object("config.development") #check or creating database db("databases/main.db").create_main_db() db("databases/control.db").create_control_db() app.register_blueprint(home_) app.register_blueprint(about_) app.register_blueprint(new_customer_) app.register_blueprint(setting_) app.register_blueprint(search_customer_) app.register_blueprint(search_project_) app.register_blueprint(help_) if __name__ == "__main__": app.run()