class Employee:
"""
Employee class
"""
def __init__(self):
"""
Initialize Employee class
"""
self.model = {
"name":
"employees",
"id":
"employee_id",
"fields":
("employee_id", "salesrep", "fullname", "email", "country", "sas"),
"types": ("INTEGER PRIMARY KEY NOT NULL", "TEXT", "TEXT", "TEXT",
"TEXT", "INTEGER DEFAULT 0")
}
self._employee = {}
self.q = Query()
if not self.q.exist_table(self.model["name"]):
sql = self.q.build("create", self.model)
self.q.execute(sql)
self.s = Settings()
if rules.check_settings(self.s.settings):
self.load(self.s.settings["usermail"])
@property
def employee(self):
"""
Return current and only employeeid
"""
return self._employee
def insert(self, values):
"""
Insert employee in database
Args:
values:
"""
sql = self.q.build("insert", self.model)
self.q.execute(sql, values=values)
def load(self, email):
"""
Load the employee
"""
filters = [("email", "=")]
values = (email, )
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values)
# first check if employee is loaded
# second check is in exception handling
try:
_ = data[0]
self._employee = dict(zip(self.model["fields"], data[0]))
except IndexError:
if httpFn.inet_conn_check():
# load from http
self.load_from_http()
success, data = self.q.execute(sql, values)
try:
# second check after load_from_http
_ = data[0]
self._employee = dict(zip(self.model["fields"], data[0]))
except IndexError:
self._employee = {}
def load_from_http(self):
"""
Load employee from http
"""
self.s.get()
data = httpFn.get_employee_data(self.s)
if data:
data = list(data)
data[0:0] = [None]
self.insert(tuple(data))
def update(self):
"""
Update employee in database
"""
fields = list(self.model["fields"])[1:]
filters = [(self.model["id"], "=")]
values = self.q.values_to_update(self._employee.values())
sql = self.q.build("update",
self.model,
update=fields,
filters=filters)
self.q.execute(sql, values=values)
class Contact:
"""
Contact class
"""
def __init__(self):
"""Initialize contact class"""
self.model = {
"name":
"contacts",
"id":
"contact_id",
"fields": ("contact_id", "customer_id", "name", "department",
"email", "phone", "infotext"),
"types": ("INTEGER PRIMARY KEY NOT NULL", "INTEGER NOT NULL",
"TEXT", "TEXT", "TEXT", "TEXT", "TEXT")
}
self._contact = {}
self._contacts = []
self._csv_record_length = 8
self.q = Query()
if not self.q.exist_table(self.model["name"]):
sql = self.q.build("create", self.model)
self.q.execute(sql)
@property
def contact(self):
"""
Active contact
:return:
"""
return self._contact
@contact.setter
def contact(self, contact_id):
"""
Set contact
:return:
"""
self.find(contact_id)
@property
def list_(self):
return self._contacts
@list_.setter
def list_(self, customer_id):
self.load_for_customer(customer_id=customer_id)
@property
def csv_record_length(self):
"""The number of fields expected on csv import"""
return self._csv_record_length
def clear(self):
"""
Clear internal variables
"""
self._contact = {}
self._contacts = []
def add(self, name, department="", phone="", email="", info=""):
"""
Create a contact
Args:
name:
department:
phone:
email:
info:
"""
values = (None, name, department, email, phone, info)
new_id = self.insert(values)
return self.find(new_id)
def delete(self, contact_id):
"""
Delete contact
Args:
contact_id:
Returns:
bool
"""
filters = [("contact_id", "=")]
values = (contact_id, )
sql = self.q.build("delete", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success and data:
return True
return False
def find(self, contact_id):
"""
Load specific contact by id
Args:
contact_id:
Returns:
bool
"""
values = (contact_id, )
sql = self.q.build("select", self.model)
success, data = self.q.execute(sql, values=values)
if success:
try:
self._contact = dict(zip(self.model["fields"], data[0]))
except IndexError:
pass
if success and data:
return True
return False
def translate_row_insert(self, row):
"""
Translate a csv row
Args:
row:
"""
new_row = (row[0], row[1], row[2].strip(), row[3].strip(),
row[4].strip(), row[5].strip(), row[7].strip())
self.insert(new_row)
def insert(self, values):
"""
Insert items
Args:
values: contact data to insert in contact table
Returns:
the new rowid
"""
sql = self.q.build("insert", self.model)
success, data = self.q.execute(sql, values=values)
if success and data:
return data
return False
def load_for_customer(self, customer_id):
"""
Load contacts for current
Args:
customer_id:
Returns:
bool
"""
filters = [("customer_id", "=")]
values = (customer_id, )
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success:
try:
self._contacts = [
dict(zip(self.model["fields"], row)) for row in data
]
self._contact = self._contacts[0]
return True
except IndexError:
self._contact = {}
self._contacts = []
return False
def recreate_table(self):
"""
Drop and create table
"""
sql = self.q.build("drop", self.model)
self.q.execute(sql)
sql = self.q.build("create", self.model)
self.q.execute(sql)
self.clear()
def update(self):
"""
Update item
Returns:
bool
"""
fields = list(self.model["fields"])[1:]
filters = [(self.model["id"], "=")]
values = self.q.values_to_update(self._contact.values())
sql = self.q.build("update",
self.model,
update=fields,
filters=filters)
success, data = self.q.execute(sql, values=values)
if success and data:
return True
return False
class Visit:
"""
Visit class
"""
def __init__(self):
"""
Initialize current class
"""
self.model = {
"name": "visits",
"id": "visit_id",
"fields": ("visit_id", "report_id", "employee_id", "customer_id",
"visit_date", "po_sent",
"po_buyer", "po_number", "po_company", "po_address1", "po_address2",
"po_postcode", "po_postoffice", "po_country",
"po_note", "prod_demo", "prod_sale", "visit_type",
"po_sas", "po_sale", "po_total", "po_approved", "visit_note"),
"types": ("INTEGER PRIMARY KEY NOT NULL", "INTEGER NOT NULL", "INTEGER NOT NULL", "INTEGER NOT NULL",
"TEXT NOT NULL", "INTEGER DEFAULT 0",
"TEXT", "TEXT", "TEXT", "TEXT", "TEXT",
"TEXT", "TEXT", "TEXT",
"TEXT", "TEXT", "TEXT", "TEXT NOT NULL",
"REAL DEFAULT 0", "REAL DEFAULT 0", "REAL DEFAULT 0",
"INTEGER DEFAULT 0", "TEXT")
}
self._visit = {}
self._visits = []
self._visits = []
self._visits = []
self._csv_record_length = 22
self.q = Query()
if not self.q.exist_table(self.model["name"]):
sql = self.q.build("create", self.model)
self.q.execute(sql)
@property
def csv_record_length(self):
"""
The number of fields expected on csv import
"""
return self._csv_record_length
@property
def visit(self):
"""
Visit
Returns:
The current active visit
"""
return self._visit
@property
def visits(self):
"""
Report Visit List
Returns:
The list of visits for a report_id
"""
return self._visits
def list_by_customer(self, customer_id):
"""
Load the list of visits for a given customer
Args:
customer_id:
"""
self.__get_by_customer(customer_id)
def list_by_date(self, visit_date):
"""
Load the list of visits for a given date
Args:
visit_date:
"""
self.__get_by_date(visit_date)
def list_by_report_id(self, report_id):
"""
Load the list of visits for a given report
Args:
report_id:
"""
self.__get_by_report_id(report_id)
def load_visit(self, visit_id):
"""
Load a visit
Args:
visit_id:
"""
self.__get(visit_id)
def add(self, report_id, employee_id, customer_id, workdate):
"""
Create a new visit
Args:
report_id:
employee_id:
customer_id:
workdate:
Returns:
integer:
"""
values = (None, report_id, employee_id, customer_id, workdate, 0,
"", "", "", "", "", "", "", "", "", "", "", "", 0.0, 0.0, 0.0, 0, "")
new_id = self.insert(values)
self.__get(new_id)
return new_id
def clear(self):
"""
Clear internal variables
"""
self._visit = {}
self._visits = []
def delete(self, visit_id):
"""
Delete the specified visit
:param visit_id:
:return:
"""
filters = [(self.model["id"], "=")]
values = (visit_id,)
sql = self.q.build("delete", self.model, filters)
self.q.execute(sql, values)
def insert(self, values):
"""
Insert a new row in the database
:param values:
:return: rownumber on success or None
"""
sql = self.q.build("insert", self.model)
success, data = self.q.execute(sql, values=values)
if success and data:
return data
return None
def recreate_table(self):
"""
Recreate table
"""
sql = self.q.build("drop", self.model)
self.q.execute(sql)
sql = self.q.build("create", self.model)
self.q.execute(sql)
self.clear()
def translate_row_insert(self, row):
"""
Translate a csv row
:rtype: bool
:param row:
:return: True / False
"""
# translate bool text to integer col 5
field_5 = utils.bool2int(utils.arg2bool(row[5]))
new_row = (row[0], row[1], row[2], row[3], row[4].strip(),
field_5, row[6].strip(), row[7].strip(), row[8].strip(), row[9].strip(),
row[10].strip(), row[11].strip(), row[12].strip(), row[13].strip(), row[14].strip(),
row[15].strip(), row[16].strip(), row[17].strip(), row[18], row[19],
row[20], row[21], row[14].strip())
self.insert(new_row) # call insert function
def update(self):
"""
Write visit changes to database
:return: rownumber on success or None
"""
fields = list(self.model["fields"])[1:]
filters = [(self.model["id"], "=")]
values = self.q.values_to_update(self._visit.values())
sql = self.q.build("update", self.model, update=fields, filters=filters)
success, data = self.q.execute(sql, values=values)
if success and data:
return data
return None
def __get(self, visit_id):
"""
Find the specified visit
:param visit_id:
:return: True on success
"""
filters = [(self.model["id"], "=")]
values = (visit_id,)
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success:
try:
self._visit = dict(zip(self.model["fields"], data[0]))
return True
except IndexError:
self._visit = {}
return False
def __get_by_customer(self, customer_id, visit_date=None):
"""
Load visit_list_customer for specified customer
:param customer_id:
:param visit_date:
"""
if visit_date:
filters = [("customer_id", "=", "AND"), ("visit_date", "=")]
values = (customer_id, visit_date)
else:
filters = [("customer_id", "=")]
values = (customer_id,)
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success:
try:
self._visits = [dict(zip(self.model["fields"], row)) for row in data]
self._visit = self._visits[0]
except (IndexError, KeyError):
self._visit = {}
self._visits = []
def __get_by_date(self, visit_date):
"""
List visits by date
:param visit_date:
"""
filters = [("visit_date", "=")]
values = (visit_date,)
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success:
try:
self._visits = dict(zip(self.model["fields"], data[0]))
self._visit = self._visits[0]
except IndexError:
self._visit = {}
self._visits = []
def __get_by_report_id(self, report_id):
"""
Load visit_list_customer for specified report
:param: report_id
"""
filters = [("report_id", "=")]
values = (report_id,)
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success:
try:
self._visits = [dict(zip(self.model["fields"], row)) for row in data]
self._visit = self._visits[0]
except (IndexError, KeyError):
self._visit = {}
self._visits = []
class Report:
"""
Report
"""
def __init__(self):
"""
Initilize Report class
"""
self.model = {
"name":
"reports",
"id":
"report_id",
"fields":
("report_id", "employee_id", "rep_no", "rep_date", "timestamp",
"newvisitday", "newdemoday", "newsaleday", "newturnoverday",
"recallvisitday", "recalldemoday", "recallsaleday",
"recallturnoverday", "sasday", "sasturnoverday", "demoday",
"saleday", "kmmorning", "kmevening", "supervisor", "territory",
"workday", "infotext", "sent", "offday", "offtext", "kmprivate"),
"types":
("INTEGER PRIMARY KEY NOT NULL", "INTEGER NOT NULL",
"INTEGER NOT NULL", "TEXT NOT NULL", "TEXT NOT NULL",
"INTEGER DEFAULT 0", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0",
"REAL DEFAULT 0", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0",
"INTEGER DEFAULT 0", "REAL DEFAULT 0", "INTEGER DEFAULT 0",
"REAL DEFAULT 0", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0",
"INTEGER DEFAULT 0", "INTEGER DEFAULT 0", "TEXT", "TEXT",
"INTEGER DEFAULT 0", "TEXT", "INTEGER DEFAULT 0",
"INTEGER DEFAULT 0", "TEXT", "INTEGER DEFAULT 0")
}
self._reports = []
self._report = {}
self._csv_record_length = 25
self.q = Query()
self.c = ReportCalculator()
if not self.q.exist_table(self.model["name"]):
sql = self.q.build("create", self.model)
self.q.execute(sql)
@property
def csv_record_length(self):
"""The number of fields expected on csv import"""
return self._csv_record_length
@property
def report(self):
"""
Report
Returns:
Active report
"""
return self._report
@property
def reports(self):
"""
Report List
Returns:
Current list of reports
"""
try:
_ = self._reports[0]
except (IndexError, KeyError):
self.__get_by_period()
return self._reports
def clear(self):
"""
Clear internal variables
"""
self.c.clear()
self._report = {}
self._reports = []
def create(self, employee, workdate):
"""
Create reportid for employeeid and date supplied
Args:
:type employee: object
:type workdate: str iso formatted representing the work date
"""
# we need to find the number of reports for the month of the supplied date
# then init_detail 1 to that number
# we need to calculate the sums for the previous reportid for month
# those sums will be stored in seperate table
# creating a new table with
# sum demoes & sum sales
# | * | DAY | MONTH |
# | --- | ------------------------------ | ------------------------------ |
# | * | Visit | Demo | Sale | Turnover | Visit | Demo | Sale | Turnover |
# | --- | ------------------------------ | ------------------------------ |
# | N | sum sum sum sum sum sum sum sum
# | R | sum sum sum sum sum sum sum sum
# | SAS | sum sum sum sum
# | SUM | sum sum sum sum sum sum sum sum
# parameters for initial feed of ReportCalc
# aggregates
aggregates = [
"count(report_id) AS 'report_count'",
"sum(newvisitday) AS 'new_visit'", "sum(newdemoday) AS 'new_demo'",
"sum(newsaleday) AS 'new_sale'",
"sum(newturnoverday) AS 'new_turnover'",
"sum(recallvisitday) AS 'recall_visit'",
"sum(recalldemoday) AS 'recall_demo'",
"sum(recallsaleday) AS 'recall_sale'",
"sum(recallturnoverday) AS 'recall_turnover'",
"sum(sasday) AS 'sas'", "sum(sasturnoverday) AS 'sas_turnover'",
"(sum(newvisitday) + sum(recallvisitday)) AS 'current'",
"(sum(newdemoday) + sum(recalldemoday)) AS 'demo'",
"(sum(newsaleday) + sum(recallsaleday) + sum(sasday)) AS 'sale'",
"(sum(newturnoverday) + sum(recallturnoverday) + sum(sasturnoverday)) AS 'turnover'",
"(sum(kmevening - kmmorning)) AS 'kmwork'",
"(sum(kmprivate)) AS 'kmprivate'",
"(sum(workday = 1)) AS 'workdays'",
"(sum(offday = 1)) AS 'offdays'"
]
filters = [("rep_date", "LIKE", "and"), ("employee_id", "=", "and"),
("sent", "=")]
ym_filter = "{}%".format(workdate[:8])
employee_id = employee["employee_id"]
territory = employee["salesrep"]
values = (ym_filter, employee_id, 1)
sql = self.q.build("select",
self.model,
aggregates=aggregates,
filters=filters)
success, data = self.q.execute(sql, values)
if success and data:
# assign expected result from list item
try:
_ = data[0]
except IndexError:
return False
# temporary convert tuple to list
current_month_totals = list(data[0])
# extract report count from first column
report_count = int(current_month_totals[0])
# increment report count
next_report = report_count + 1
# init_detail a combined list with the identifiers and the totals
current_month_totals = [workdate, "None", employee_id
] + current_month_totals
timestamp = datetime.today()
# init_detail tuple with values to initialze the new report
new_report_values = (None, employee_id, next_report, workdate,
timestamp, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, "", territory, 1, "", 0, 0, "", 0)
# assign return value as new report_id
report_id = self.insert(new_report_values)
# insert report_id to identify for which report the totals was calculated
current_month_totals[1] = report_id
# revert to tuple
current_month_totals = tuple(current_month_totals)
# insert the values in the calculation table
self.c.insert(current_month_totals)
return True
else:
return False
def insert(self, values):
"""
Insert new reportid in table
Args:
:type values: iterable
"""
sql = self.q.build("insert", self.model)
success, data = self.q.execute(sql, values=values)
if success and data:
return data
return False
def load(self, workdate=None, year=None, month=None):
"""
Load reports for a given period
If none given load all
Args:
:type workdate: str
:type year: str
:type month: str
"""
self.__get_by_period(workdate, year, month)
def recreate_table(self):
"""
Drop and initialize reports table
"""
self.c.recreate_table()
sql = self.q.build("drop", self.model)
self.q.execute(sql)
sql = self.q.build("create", self.model)
self.q.execute(sql)
self.clear()
def translate_row_insert(self, row, employee_id):
"""
Translate a csv row
Args:
:type row: iterable
:type employee_id: int
"""
# translate bool text to integer for col 19, 21
field_19 = utils.bool2int(utils.arg2bool(row[19]))
field_21 = utils.bool2int(utils.arg2bool(row[21]))
# create timestamp
local_timestamp = datetime.today()
values = (row[0], employee_id, row[1], row[2].strip(), local_timestamp,
row[3], row[4], row[5], row[6], row[7], row[8], row[9],
row[10], row[11], row[12], row[13], row[14], row[15],
row[16], row[17].strip(), row[18].strip(), field_19,
row[20].strip(), field_21, row[22], row[23].strip(), row[24])
self.insert(values)
def update(self):
"""
Update reportid in database
"""
# update_list = list(self.model["fields"])[1:]
# update_where = [(self.model["id"], "=")]
# self.q.values_to_update(self._report.values())
# if DBG:
# printit(
# "{}\n ->update\n ->sql: {}\n ->values: {}".format(
# sql, values))
# if DBG:
# printit(
# "{}\n ->update\n ->success: {}\n ->data: {}".format(
# success, data))
pass
def __get_by_period(self, workdate=None, year=None, month=None):
"""
Load reports matching args or all if no args
Args:
:type workdate: str
:type year: str
:type month: str
"""
if workdate:
try:
_ = self._reports[0]
for report in self._reports:
if report["workdate"] == workdate:
self._report = report
return
except (IndexError, KeyError):
pass
filters = [("rep_date", "like")]
value = "{}-{}-{}".format("%", "%", "%")
if year:
value = "{}-{}-{}".format(year, "%", "%")
if year and month:
value = "{}-{}-{}".format(year, month, "%")
values = (value, )
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success and data:
try:
_ = data[0]
self._reports = [
dict(zip(self.model["fields"], row)) for row in data
]
self._reports = sorted(self._reports,
key=itemgetter("rep_date"),
reverse=True)
if workdate:
for report in self._reports:
if report["rep_date"] == workdate:
self._report = report
break
if not self._report:
self._report = self._reports[0]
except IndexError:
self._report = {}
self._reports = []
else:
self._report = {}
self._reports = []
class OrderLine:
"""
OrderLine class
"""
def __init__(self):
"""
Initialize OrderLine class
"""
self.model = {
"name": "orderlines",
"id": "line_id",
"fields": ("line_id", "visit_id",
"pcs", "sku", "text", "price", "sas", "discount",
"linetype", "linenote", "item"),
"types": ("INTEGER PRIMARY KEY NOT NULL", "INTEGER NOT NULL",
"INTEGER", "TEXT", "TEXT", "REAL", "INTEGER DEFAULT 0", "REAL DEFAULT 0",
"TEXT", "TEXT", "TEXT")
}
self._line = {}
self._lines = []
self._csv_record_length = 8
self.q = Query()
if not self.q.exist_table(self.model["name"]):
sql = self.q.build("create", self.model)
self.q.execute(sql)
@property
def line(self):
"""
Return the current focused purchase order line
Returns:
current
"""
return self._line
@line.setter
def line(self, line_id):
"""
Set the current focused purchase order line
Args:
line_id:
"""
try:
_ = self._line["line_id"]
if not _ == line_id:
self.find(line_id=line_id)
except KeyError:
self.find(line_id=line_id)
@property
def list_(self):
"""
All purchase order lines
Returns:
List of details for a purchase order line
"""
return self._lines
@list_.setter
def list_(self, visit_id):
"""
Orderlines setter. Load purchase order lines for visit_id
Args:
visit_id:
"""
try:
v_id = self._lines[0]
if not v_id == visit_id:
self.load_visit(visit_id=visit_id)
except (IndexError, KeyError):
self.load_visit(visit_id)
@property
def csv_record_length(self):
"""The number of fields expected on csv import"""
return self._csv_record_length
def add(self, visit_id, line_type):
"""
Initialize a new purchase order line with visitid
Args:
visit_id:
line_type:
"""
line_type = line_type.upper()
values = (None, visit_id, "", "", "", "", "", "", line_type, "", "")
new_id = self.insert(values)
self.find(new_id)
def clear(self):
"""
Clear internal variables
"""
self._line = {}
self._lines = []
def delete(self, orderline_id):
"""
Delete line with id
Args:
orderline_id:
Returns:
bool
"""
filters = [(self.model["id"], "=")]
values = (orderline_id,)
sql = self.q.build("delete", self.model, filters=filters)
success, data = self.q.execute(sql, values)
if success and data:
return True
return False
def find(self, line_id):
"""
Find the the order line with id
Args:
line_id:
Returns:
bool
"""
filters = [(self.model["id"], "=")]
values = (line_id,)
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success:
try:
self._line = dict(zip(self.model["fields"], data[0]))
return True
except IndexError:
self._line = {}
return False
def translate_row_insert(self, row):
"""
Translate a csv row
Args:
row:
"""
# translate bool text to integer col 6
field_6 = utils.bool2int(utils.arg2bool(row[6]))
new_row = (row[0], row[1], row[2], row[3].strip(), row[4].strip(), row[5], field_6, row[7], "S", "", "")
self.insert(new_row)
def insert(self, values):
"""
Insert a new line with values
Args:
values:
Returns:
rownumber or None
"""
sql = self.q.build("insert", self.model)
success, data = self.q.execute(sql, values=values)
if success and data:
return data
return None
def load_visit(self, visit_id):
"""
Load order lines for visit_id
Args:
visit_id:
Returns:
bool: True on success
"""
filters = [("visit_id", "=")]
values = (visit_id,)
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success:
try:
self._lines = [dict(zip(self.model["fields"], row)) for row in data]
self._line = self.list_[0]
return True
except (IndexError, KeyError):
self._line = {}
self._lines = []
return False
def recreate_table(self):
"""
Recrete table and clears internal variables
"""
sql = self.q.build("drop", self.model)
self.q.execute(sql)
sql = self.q.build("create", self.model)
self.q.execute(sql)
self.clear()
def save_all(self):
"""
Save the list of lines
"""
for line in self._lines:
if line[self.model["id"]] is None:
self.insert(line.values())
else:
self._line = line
self.update()
def update(self):
"""
Update line data in database
Returns:
rownumber or None
"""
fields = list(self.model["fields"])[1:]
filters = [(self.model["id"], "=")]
values = self.q.values_to_update(self._line.values())
sql = self.q.build("update", self.model, update=fields, filters=filters)
if sql.startswith("ERROR"):
return None
success, data = self.q.execute(sql, values=values)
if success and data:
return data
return None
class Product:
"""
Product
"""
def __init__(self):
"""
Initialize product class
"""
self.model = {
"name":
"products",
"id":
"product_id",
"fields": ("product_id", "sku", "name1", "name2", "name3", "item",
"price", "d2", "d4", "d6", "d8", "d12", "d24", "d48",
"d96", "min", "net", "groupid"),
"types":
("INTEGER PRIMARY KEY NOT NULL", "TEXT", "TEXT", "TEXT", "TEXT",
"TEXT", "REAL DEFAULT 0", "REAL DEFAULT 0", "REAL DEFAULT 0",
"REAL DEFAULT 0", "REAL DEFAULT 0", "REAL DEFAULT 0",
"REAL DEFAULT 0", "REAL DEFAULT 0", "REAL DEFAULT 0",
"REAL DEFAULT 0", "REAL DEFAULT 0", "TEXT")
}
self._product = {}
self._products = []
self.q = Query()
if not self.q.exist_table(self.model["name"]):
sql = self.q.build("create", self.model)
self.q.execute(sql)
@property
def product(self):
"""
Return active product
"""
return self._product
@product.setter
def product(self, product_id):
"""
Set current product
Args:
product_id:
"""
self.__get_by_id(product_id)
@property
def products(self):
"""
ProductList
Returns:
List of products
"""
try:
_ = self._products[0]
except IndexError:
self.__get_all()
return self._products
def clear(self):
"""
Clear internal variables
"""
self._product = {}
self._products = []
def drop_table(self):
"""
Drop the product table
The table can be safely recreated.
An internal pointer to a specific product id is not used as line will contain the product sku etc
This approach also eliminates a current issue with deprecated products
"""
self.recreate_table()
def insert(self, values):
"""
Insert a product in database
Args:
values:
"""
values = list(values)
values[0:0] = [None]
values = tuple(values)
sql = self.q.build("insert", self.model)
success, data = self.q.execute(sql, values=values)
if success and data:
return data
return False
def recreate_table(self):
"""
Drop and init_detail table
"""
sql = self.q.build("drop", self.model)
self.q.execute(sql)
sql = self.q.build("create", self.model)
self.q.execute(sql)
self.clear()
def __get_all(self):
"""
Load product list
"""
sql = self.q.build("select", self.model)
success, data = self.q.execute(sql)
if success and data:
self._products = [
dict(zip(self.model["fields"], row)) for row in data
]
self._product = self._products[0]
else:
self._product = {}
self._products = []
def __get_by_id(self, product_id):
"""
Set current product
:param product_id:
"""
filters = [("product_id", "=")]
values = (product_id, )
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values)
if success and data:
self._product = dict(zip(self.model["fields"], data[0]))
else:
self._product = {}
class Settings:
"""
settings class
"""
def __init__(self):
"""
Initialize the settings class
"""
self.model = {
"name":
"settings",
"id":
"settings_id",
"fields":
("settings_id", "usermail", "userpass", "usercountry", "pd", "pf",
"sf", "http", "smtp", "port", "mailto", "mailserver", "mailport",
"mailuser", "mailpass", "fc", "fp", "fe", "lsc", "lsp", "sac",
"sap", "sc", "cust_idx", "page_idx"),
"types":
("INTEGER PRIMARY KEY NOT NULL", "TEXT", "TEXT", "TEXT", "TEXT",
"TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT",
"TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT",
"TEXT", "INTEGER", "INTEGER", "INTEGER")
}
self._settings = {}
self.q = Query()
if not self.q.exist_table(self.model["name"]):
sql = self.q.build("create", self.model)
self.q.execute(sql)
@property
def settings(self):
"""
current
Returns:
The current settings
"""
try:
_ = self._settings["usermail"]
except KeyError:
self.get()
return self._settings
@settings.setter
def settings(self, settings):
"""
Pushing new current settings
Args:
settings:
"""
self._settings = settings
self.update()
def get(self):
"""
Load current
"""
sql = self.q.build("select", self.model)
success, data = self.q.execute(sql)
if success and not data:
values = (None, "", "", "", "_", "__", ".txt", "", "", "", "", "",
"", "", "", "customers", "invenprices", "employees", "",
"", "", "", 0, 0, 0)
self.__insert(values)
success, data = self.q.execute(sql)
if success and data:
self._settings = dict(zip(self.model["fields"], data[0]))
def update(self):
"""
Update current
"""
fields = list(self.model["fields"])[1:]
filters = [(self.model["id"], "=")]
values = self.q.values_to_update(self._settings.values())
sql = self.q.build("update",
self.model,
update=fields,
filters=filters)
self.q.execute(sql, values=values)
def __insert(self, values):
"""
Inserts in database and activates the current settings values
Args:
values:
Returns:
"""
sql = self.q.build("insert", self.model)
self.q.execute(sql, values=values)
self._settings = dict(zip(self.model["fields"], values))
class CustomerProducts:
"""
CustomerProduct class
"""
def __init__(self):
"""
Initialize CustomerProduct class
"""
self.model = {
"name":
"customerproducts",
"id":
"cp_id",
"fields": ("cp_id", "customer_id", "item", "sku", "pcs"),
"types": ("INTEGER PRIMARY KEY NOT NULL", "INTEGER NOT NULL",
"TEXT NOT NULL", "TEXT NOT NULL", "INTEGER DEFAULT 0")
}
self._products = []
self._product = {}
self.q = Query()
if not self.q.exist_table(self.model["name"]):
sql = self.q.build("create", self.model)
self.q.execute(sql)
@property
def list_(self):
"""
Customer products
:return:
"""
return self._products
@list_.setter
def list_(self, customer_id):
"""
Load customers into primary list
"""
self.__load(customer_id)
def clear(self):
"""
Clear internal variables
"""
self._products = []
def add(self, customer_id, item, sku, pcs):
"""
Create a new customer
Args:
customer_id:
item:
sku:
pcs:
Returns:
bool
"""
self.insert((None, customer_id, item, sku, pcs))
self.__load(customer_id)
def insert(self, values):
"""
Insert a new current
Args:
values:
Returns:
rowid
"""
sql = self.q.build("insert", self.model)
success, data = self.q.execute(sql, values=values)
if success and data:
return data
return False
def __load(self, customer_id):
"""
Load products
Args:
customer_id
Returns:
bool
"""
filters = [("customer_id", "=")]
values = (customer_id, )
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success:
try:
self._products = [
dict(zip(self.model["fields"], row)) for row in data
]
return True
except IndexError:
self._products = []
return False
def refresh(self, customer_id):
"""
Refresh customers product list
Args:
customer_id
Returns:
bool
"""
visit = Visit()
selection = ("customer_id", )
filters = [("customer_id", "=")]
values = (customer_id, )
sql = self.q.build("select",
visit.model,
selection=selection,
filters=filters)
success, data = self.q.execute(sql, values=values)
if success:
try:
v_ids = data[0]
sql = "SELECT lines.sku, sum(pcs) AS pcs, product.item " \
"FROM lines " \
"INNER JOIN product ON product.sku = lines.sku " \
"WHERE cust_id IN ? GROUP BY lines.sku"
success, data = self.q.execute(sql, v_ids)
if success:
try:
self._products = [
dict(zip(self.model["fields"], row))
for row in data
]
except IndexError:
pass
return True
except IndexError:
self._products = []
return False
def recreate_table(self):
"""
Drop and create table
"""
sql = self.q.build("drop", self.model)
self.q.execute(sql)
sql = self.q.build("create", self.model)
self.q.execute(sql)
self.clear()
def update(self):
"""
Update customer product list
Returns:
bool
"""
fields = list(self.model["fields"])[1:]
filters = [(self.model["id"], "=")]
values = self.q.values_to_update(self._product.values())
sql = self.q.build("update",
self.model,
update=fields,
filters=filters)
success, data = self.q.execute(sql, values=values)
if success and data:
return True
return False
class ReportCalculator:
"""
Calculator
"""
def __init__(self):
"""
Initialize Calculator
"""
self.model = {
"name":
"reportcalculations",
"id":
"calc_id",
"fields":
("calc_id", "calc_date", "report_id", "employee_id",
"reports_calculated", "new_visit", "new_demo", "new_sale",
"new_turnover", "recall_visit", "recall_demo", "recall_sale",
"recall_turnover", "sas", "sas_turnover", "current", "demo",
"sale", "turnover", "kmwork", "kmprivate", "workdays", "offdays"),
"types":
("INTEGER PRIMARY KEY NOT NULL", "TEXT NOT NULL",
"INTEGER NOT NULL", "INTEGER NOT NULL", "INTEGER DEFAULT 0",
"INTEGER DEFAULT 0", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0",
"REAL DEFAULT 0", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0",
"INTEGER DEFAULT 0", "REAL DEFAULT 0", "INTEGER DEFAULT 0",
"REAL DEFAULT 0", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0",
"INTEGER DEFAULT 0", "REAL DEFAULT 0", "INTEGER DEFAULT 0",
"INTEGER DEFAULT 0", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0")
}
self._totals = {}
self.q = Query()
if not self.q.exist_table(self.model["name"]):
sql = self.q.build("create", self.model)
self.q.execute(sql)
@property
def result(self):
"""
Totals
Returns:
The current current
"""
return self._totals
@result.setter
def result(self, date_employee):
"""
Sets the current totals
:param date_employee: tuple with date and employee
:return:
"""
try:
try:
_ = self._totals["workdate"]
if not _ == date_employee[0]:
self.get_by_date_employee(date_employee[0],
date_employee[1])
except KeyError:
self.get_by_date_employee(date_employee[0], date_employee[1])
except IndexError:
self.clear()
def clear(self):
"""
Clear internal variables
"""
self._totals = {}
def get_by_id(self, calc_id):
"""
Select by id
Returns:
bool indicating current has been set for the requested id
"""
filters = [(self.model["id"], "=")]
values = (calc_id, )
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success and data:
self._totals = dict(zip(self.model["fields"], data[0]))
return False
def get_by_date_employee(self, workdate, employee_id):
"""
Select current for employeeid and workdate
Args:
workdate:
employee_id:
Returns:
bool indicating current for the selected reportid is now set
"""
filters = [("workdate", "=", "and"), ("employee_id", "=")]
values = (workdate, employee_id)
sql = self.q.build("select", self.model, filters=filters)
success, data = self.q.execute(sql, values=values)
if success and data:
self._totals = dict(zip(self.model["fields"], data[0]))
return False
def insert(self, values):
"""
Save values to database and sets current with the supplied values
Args:
values:
"""
values = list(values)
values[0:0] = [None]
values = tuple(values)
sql = self.q.build("insert", self.model)
success, data = self.q.execute(sql, values=values)
if success and data:
return data
return False
def update(self):
"""
Update current in database if necessary
Returns:
bool indicating if update was a success
"""
fields = list(self.model["fields"])[1:]
filters = [(self.model["id"], "=")]
values = self.q.values_to_update(self._totals.values())
sql = self.q.build("update",
self.model,
update=fields,
filters=filters)
success, data = self.q.execute(sql, values=values)
if success and data:
return True
return False
def recreate_table(self):
"""
Drop and init_detail table
"""
sql = self.q.build("drop", self.model)
self.q.execute(sql)
sql = self.q.build("create", self.model)
self.q.execute(sql)
self.clear()