# -*- coding: utf-8 -*-
#
# Copyright (C) 2008-2013 <NAME> <<EMAIL>>
# All rights reserved.
#
# This software is licensed as described in the file COPYING, which
# you should have received as part of this distribution.
#

"""Model classes for objects persisted in the database."""

from trac.db import Column, Table

from utils import *


class TT_Template(object):
    """Represents a generated tt."""

    _schema = [
        Table('ticket_template_store')[
            Column('tt_time', type='int'),
            Column('tt_user'),
            Column('tt_name'),
            Column('tt_field'),
            Column('tt_value'),
        ]
    ]

    def __init__(self, env):
        """Initialize a new report with the specified attributes.

        To actually create this build log in the database, the `insert` method
        needs to be called.
        """
        self.env = env

    exists = property(fget=lambda self: self.id is not None,
                      doc="Whether this tt exists in the database")

    def deleteCustom(cls, env, data):
        """Remove the tt from the database."""
        with env.db_transaction as db:
            cursor = db.cursor()
            sqlString = """DELETE FROM ticket_template_store
                            WHERE tt_user=%s
                            AND tt_name=%s
                        """
            cursor.execute(sqlString, (data['tt_user'], data['tt_name'], ))

    deleteCustom = classmethod(deleteCustom)

    def insert(cls, env, record):
        """Insert a new tt into the database."""
        with env.db_transaction as db:

            cursor = db.cursor()
            sqlString = """INSERT INTO ticket_template_store
                           (tt_time,tt_user,tt_name,tt_field,tt_value)
                           VALUES (%s,%s,%s,%s,%s)"""
            cursor.execute(sqlString, record)

    insert = classmethod(insert)

    def fetchCurrent(cls, env, data):
        """Retrieve an existing tt from the database by ID."""
        with env.db_query as db:
            cursor = db.cursor()
            sqlString = """SELECT tt_field, tt_value
                            FROM ticket_template_store
                            WHERE tt_user = %s
                            AND tt_time =  (SELECT max(tt_time)
                                FROM ticket_template_store
                                WHERE tt_name=%s)
                        """
            cursor.execute(sqlString, (data['tt_user'], data['tt_name'], ))

            field_value_mapping = {}
            for tt_field, tt_value in cursor.fetchall():
                if tt_value:
                    field_value_mapping[tt_field] = tt_value

            return field_value_mapping

    fetchCurrent = classmethod(fetchCurrent)

    def fetchAll(cls, env, data):
        """Retrieve an existing tt from the database by ID.
            result:
                {
                    "field_value_mapping":{
                            "default":{
                                    "summary":"aaa",
                                    "description":"bbb",
                                },

                        },
                    "field_value_mapping_custom":{
                            "my_template":{
                                    "summary":"ccc",
                                    "description":"ddd",
                                },

                        },
                }

        """
        with env.db_query as db:
            cursor = db.cursor()

            real_user = data.get('tt_user')
            req_args = data.get('req_args')

            field_value_mapping = {}
            field_value_mapping_custom = {}

            # field_value_mapping_custom
            sqlString = """SELECT tt_name, tt_field, tt_value
                            FROM ticket_template_store
                            WHERE tt_user = %s
                        """

            cursor.execute(sqlString, (data['tt_user'], ))

            for tt_name, tt_field, tt_value in cursor.fetchall():
                if tt_name not in field_value_mapping_custom:
                    field_value_mapping_custom[tt_name] = {}
                if tt_value:
                    tt_value = formatField(env.config, tt_value, real_user,
                                           req_args)
                    field_value_mapping_custom[tt_name][tt_field] = tt_value

            # field_value_mapping
            sqlString = """SELECT DISTINCT tt_name
                            FROM ticket_template_store
                            WHERE tt_user = %s
                        """
            cursor.execute(sqlString, (SYSTEM_USER, ))

            tt_name_list = [row[0] for row in cursor.fetchall()]

            data['tt_user'] = SYSTEM_USER
            for tt_name in tt_name_list:
                data['tt_name'] = tt_name

                sqlString = """SELECT tt_field, tt_value
                                FROM ticket_template_store
                                WHERE tt_user = %s
                                AND tt_name = %s
                                AND tt_time =  (SELECT max(tt_time)
                                    FROM ticket_template_store
                                    WHERE tt_name = %s)
                            """
                cursor.execute(sqlString,(data['tt_user'], data['tt_name'],
                                           data['tt_name']))

                for tt_field, tt_value in cursor.fetchall():
                    if tt_name not in field_value_mapping:
                        field_value_mapping[tt_name] = {}
                    if tt_value:
                        tt_value = formatField(env.config, tt_value, real_user,
                                               req_args)
                        field_value_mapping[tt_name][tt_field] = tt_value

            result = {
                'field_value_mapping': field_value_mapping,
                'field_value_mapping_custom': field_value_mapping_custom
            }
            return result

    fetchAll = classmethod(fetchAll)

    def getCustomTemplate(cls, env, tt_user):
        """Retrieve from the database that match
        the specified criteria.
        """
        with env.db_query as db:
            cursor = db.cursor()

            sqlString = """SELECT DISTINCT tt_name
                           FROM ticket_template_store
                           WHERE tt_user = %s
                           ORDER BY tt_name
                        """

            cursor.execute(sqlString, (tt_user, ))

            return [row[0] for row in cursor.fetchall()]

    getCustomTemplate = classmethod(getCustomTemplate)

    def fetch(cls, env, tt_name, db=None):
        """Retrieve an existing tt from the database by ID."""
        with env.db_query as db:
            cursor = db.cursor()
            sqlString = """SELECT tt_value
                            FROM ticket_template_store
                            WHERE tt_time=(
                                SELECT max(tt_time)
                                    FROM ticket_template_store
                                    WHERE tt_name=%s and tt_field='description'
                                )
                        """

            cursor.execute(sqlString, (tt_name,))

            row = cursor.fetchone()
            if not row:
                return None
            else:
                return row[0]

    fetch = classmethod(fetch)

    def fetchNames(cls, env):
        """fetch a list of existing tt names from database"""
        with env.db_query as db:
            cursor = db.cursor()
            sqlString = "SELECT DISTINCT tt_name FROM ticket_template_store"
            cursor.execute(sqlString)
            # create a list of strings from the list of tuples
            # cursor.fetchall() ~= [(u'default',), (u'task',), ...]
            return [row[0] for row in cursor.fetchall()]

    fetchNames = classmethod(fetchNames)


schema = TT_Template._schema
schema_version = 4

# -*- coding: utf-8 -*-
#
# Copyright (C) 2008-2013 <NAME> <<EMAIL>>
# All rights reserved.
#
# This software is licensed as described in the file COPYING, which
# you should have received as part of this distribution.
#

"""Model classes for objects persisted in the database."""

from trac.db import Column, Table

from utils import *


class TT_Template(object):
    """Represents a generated tt."""

    _schema = [
        Table('ticket_template_store')[
            Column('tt_time', type='int'),
            Column('tt_user'),
            Column('tt_name'),
            Column('tt_field'),
            Column('tt_value'),
        ]
    ]

    def __init__(self, env):
        """Initialize a new report with the specified attributes.

        To actually create this build log in the database, the `insert` method
        needs to be called.
        """
        self.env = env

    exists = property(fget=lambda self: self.id is not None,
                      doc="Whether this tt exists in the database")

    def deleteCustom(cls, env, data):
        """Remove the tt from the database."""
        with env.db_transaction as db:
            cursor = db.cursor()
            sqlString = """DELETE FROM ticket_template_store
                            WHERE tt_user=%s
                            AND tt_name=%s
                        """
            cursor.execute(sqlString, (data['tt_user'], data['tt_name'], ))

    deleteCustom = classmethod(deleteCustom)

    def insert(cls, env, record):
        """Insert a new tt into the database."""
        with env.db_transaction as db:

            cursor = db.cursor()
            sqlString = """INSERT INTO ticket_template_store
                           (tt_time,tt_user,tt_name,tt_field,tt_value)
                           VALUES (%s,%s,%s,%s,%s)"""
            cursor.execute(sqlString, record)

    insert = classmethod(insert)

    def fetchCurrent(cls, env, data):
        """Retrieve an existing tt from the database by ID."""
        with env.db_query as db:
            cursor = db.cursor()
            sqlString = """SELECT tt_field, tt_value
                            FROM ticket_template_store
                            WHERE tt_user = %s
                            AND tt_time =  (SELECT max(tt_time)
                                FROM ticket_template_store
                                WHERE tt_name=%s)
                        """
            cursor.execute(sqlString, (data['tt_user'], data['tt_name'], ))

            field_value_mapping = {}
            for tt_field, tt_value in cursor.fetchall():
                if tt_value:
                    field_value_mapping[tt_field] = tt_value

            return field_value_mapping

    fetchCurrent = classmethod(fetchCurrent)

    def fetchAll(cls, env, data):
        """Retrieve an existing tt from the database by ID.
            result:
                {
                    "field_value_mapping":{
                            "default":{
                                    "summary":"aaa",
                                    "description":"bbb",
                                },

                        },
                    "field_value_mapping_custom":{
                            "my_template":{
                                    "summary":"ccc",
                                    "description":"ddd",
                                },

                        },
                }

        """
        with env.db_query as db:
            cursor = db.cursor()

            real_user = data.get('tt_user')
            req_args = data.get('req_args')

            field_value_mapping = {}
            field_value_mapping_custom = {}

            # field_value_mapping_custom
            sqlString = """SELECT tt_name, tt_field, tt_value
                            FROM ticket_template_store
                            WHERE tt_user = %s
                        """

            cursor.execute(sqlString, (data['tt_user'], ))

            for tt_name, tt_field, tt_value in cursor.fetchall():
                if tt_name not in field_value_mapping_custom:
                    field_value_mapping_custom[tt_name] = {}
                if tt_value:
                    tt_value = formatField(env.config, tt_value, real_user,
                                           req_args)
                    field_value_mapping_custom[tt_name][tt_field] = tt_value

            # field_value_mapping
            sqlString = """SELECT DISTINCT tt_name
                            FROM ticket_template_store
                            WHERE tt_user = %s
                        """
            cursor.execute(sqlString, (SYSTEM_USER, ))

            tt_name_list = [row[0] for row in cursor.fetchall()]

            data['tt_user'] = SYSTEM_USER
            for tt_name in tt_name_list:
                data['tt_name'] = tt_name

                sqlString = """SELECT tt_field, tt_value
                                FROM ticket_template_store
                                WHERE tt_user = %s
                                AND tt_name = %s
                                AND tt_time =  (SELECT max(tt_time)
                                    FROM ticket_template_store
                                    WHERE tt_name = %s)
                            """
                cursor.execute(sqlString,(data['tt_user'], data['tt_name'],
                                           data['tt_name']))

                for tt_field, tt_value in cursor.fetchall():
                    if tt_name not in field_value_mapping:
                        field_value_mapping[tt_name] = {}
                    if tt_value:
                        tt_value = formatField(env.config, tt_value, real_user,
                                               req_args)
                        field_value_mapping[tt_name][tt_field] = tt_value

            result = {
                'field_value_mapping': field_value_mapping,
                'field_value_mapping_custom': field_value_mapping_custom
            }
            return result

    fetchAll = classmethod(fetchAll)

    def getCustomTemplate(cls, env, tt_user):
        """Retrieve from the database that match
        the specified criteria.
        """
        with env.db_query as db:
            cursor = db.cursor()

            sqlString = """SELECT DISTINCT tt_name
                           FROM ticket_template_store
                           WHERE tt_user = %s
                           ORDER BY tt_name
                        """

            cursor.execute(sqlString, (tt_user, ))

            return [row[0] for row in cursor.fetchall()]

    getCustomTemplate = classmethod(getCustomTemplate)

    def fetch(cls, env, tt_name, db=None):
        """Retrieve an existing tt from the database by ID."""
        with env.db_query as db:
            cursor = db.cursor()
            sqlString = """SELECT tt_value
                            FROM ticket_template_store
                            WHERE tt_time=(
                                SELECT max(tt_time)
                                    FROM ticket_template_store
                                    WHERE tt_name=%s and tt_field='description'
                                )
                        """

            cursor.execute(sqlString, (tt_name,))

            row = cursor.fetchone()
            if not row:
                return None
            else:
                return row[0]

    fetch = classmethod(fetch)

    def fetchNames(cls, env):
        """fetch a list of existing tt names from database"""
        with env.db_query as db:
            cursor = db.cursor()
            sqlString = "SELECT DISTINCT tt_name FROM ticket_template_store"
            cursor.execute(sqlString)
            # create a list of strings from the list of tuples
            # cursor.fetchall() ~= [(u'default',), (u'task',), ...]
            return [row[0] for row in cursor.fetchall()]

    fetchNames = classmethod(fetchNames)


schema = TT_Template._schema
schema_version = 4

# -*- coding: utf-8 -*- # # Copyright (C) 2008-2013 <NAME> <<EMAIL>> # All rights reserved. # # This software is licensed as described in the file COPYING, which # you should have received as part of this distribution. # Model classes for objects persisted in the database. Represents a generated tt. Initialize a new report with the specified attributes.          To actually create this build log in the database, the `insert` method         needs to be called. Remove the tt from the database. DELETE FROM ticket_template_store                             WHERE tt_user=%s                             AND tt_name=%s Insert a new tt into the database. INSERT INTO ticket_template_store                            (tt_time,tt_user,tt_name,tt_field,tt_value)                            VALUES (%s,%s,%s,%s,%s) Retrieve an existing tt from the database by ID. SELECT tt_field, tt_value                             FROM ticket_template_store                             WHERE tt_user = %s                             AND tt_time =  (SELECT max(tt_time)                                 FROM ticket_template_store                                 WHERE tt_name=%s) Retrieve an existing tt from the database by ID.             result:                 {                     "field_value_mapping":{                             "default":{                                     "summary":"aaa",                                     "description":"bbb",                                 },                          },                     "field_value_mapping_custom":{                             "my_template":{                                     "summary":"ccc",                                     "description":"ddd",                                 },                          },                 } # field_value_mapping_custom SELECT tt_name, tt_field, tt_value                             FROM ticket_template_store                             WHERE tt_user = %s # field_value_mapping SELECT DISTINCT tt_name                             FROM ticket_template_store                             WHERE tt_user = %s SELECT tt_field, tt_value                                 FROM ticket_template_store                                 WHERE tt_user = %s                                 AND tt_name = %s                                 AND tt_time =  (SELECT max(tt_time)                                     FROM ticket_template_store                                     WHERE tt_name = %s) Retrieve from the database that match         the specified criteria. SELECT DISTINCT tt_name                            FROM ticket_template_store                            WHERE tt_user = %s                            ORDER BY tt_name Retrieve an existing tt from the database by ID. SELECT tt_value                             FROM ticket_template_store                             WHERE tt_time=(                                 SELECT max(tt_time)                                     FROM ticket_template_store                                     WHERE tt_name=%s and tt_field='description'                                 ) fetch a list of existing tt names from database # create a list of strings from the list of tuples # cursor.fetchall() ~= [(u'default',), (u'task',), ...]