def get_closed_tickets_dict(self):
settings = frappe.get_doc("OTRSConnect Settings")
print(settings.otrs_host)
print(settings.db_user)
print(settings.db_password)
otrsdb = get_db(host=settings.otrs_host,
user=settings.db_user,
password=settings.db_password)
otrsdb.connect()
otrsdb.use(settings.db_name)
sql = (
"SELECT DISTINCT ticket.id, ticket.tn, ticket.title, "
"ticket.queue_id, ticket.user_id, ticket.responsible_user_id, "
"ticket.ticket_priority_id, ticket.customer_id, ticket.customer_user_id, "
"ticket.ticket_state_id, ticket.create_time, ticket.create_by, "
"ticket.change_time, ticket.change_by "
"FROM ticket "
"LEFT JOIN users ON ticket.user_id=users.id "
"LEFT JOIN customer_company ON customer_company.customer_id=ticket.customer_id "
"LEFT JOIN time_accounting ON time_accounting.ticket_id=ticket.id "
"WHERE ticket_state_id = '2' "
"AND time_accounting.time_unit > 0 "
";")
return otrsdb.sql(sql, as_dict=1)
def connect_read_only(): from frappe.database import get_db local.read_only_db = get_db(local.conf.slave_host, local.conf.slave_db_name, local.conf.slave_db_password) # swap db connections local.master_db = local.db local.db = local.read_only_db
def connect(site=None, db_name=None):
"""Connect to site database instance.
:param site: If site is given, calls `frappe.init`.
:param db_name: Optional. Will use from `site_config.json`."""
from frappe.database import get_db
if site:
init(site)
local.db = get_db(user=db_name or local.conf.db_name)
set_user("Administrator")
def connect_replica(): from frappe.database import get_db user = local.conf.db_name password = local.conf.db_password if local.conf.different_credentials_for_replica: user = local.conf.replica_db_name password = local.conf.replica_db_password local.replica_db = get_db(host=local.conf.replica_host, user=user, password=password) # swap db connections local.primary_db = local.db local.db = local.replica_db
def get_Articles_for_Ticket_dict(self, OTRSConnect_Ticket):
settings = frappe.get_doc("OTRSConnect Settings")
otrsdb = get_db(host=settings.otrs_host,
user=settings.db_user,
password=settings.db_password)
otrsdb.begin()
otrsdb.use(settings.db_name)
sql = (
"SELECT article.id, article.ticket_id, article.create_time, "
"article.create_by, article_data_mime.a_from, article_data_mime.a_to, "
"article_data_mime.a_subject, article_data_mime.a_body, "
"time_accounting.time_unit "
"FROM article "
"LEFT JOIN article_data_mime ON article.id=article_data_mime.id "
"LEFT JOIN time_accounting time_accounting ON article.id=time_accounting.article_id "
"WHERE time_accounting.time_unit > 0 "
"AND article.ticket_id = " + str(OTRSConnect_Ticket.id) + "; ")
return otrsdb.sql(sql, as_dict=1)