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()