Example #1
0
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)
Example #2
0
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
Example #3
0
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 = []
Example #4
0
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 = []
Example #5
0
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
Example #6
0
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 = {}
Example #7
0
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))
Example #8
0
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
Example #9
0
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()