class ManageSassChatDB(Singleton):
def __init__(self):
testbed = iTestFrameGetTestBed()
ip = Parser_Config(testbed, "Mysql", "database_ip")
user = Parser_Config(testbed, "Mysql", "user_saas_chat")
password = Parser_Config(testbed, "Mysql", "password_saas_chat")
dbName = Parser_Config(testbed, "Mysql", "dbname_saas_chat")
dbport = Parser_Config(testbed, "Mysql", "database_port")
self.MysqlSaas = ManageMysqlBase(ip, user, password, dbName, dbport)
def getChatKeyFromStatusAndProviderID(self,
key,
providerid,
status_str='new',
istype=True,
limit_count=1):
if istype:
select_str = "Select %s from module_talk_chat where status='%s' and provider_id=%s limit %s" % (
key, providerid, status_str, limit_count)
else:
select_str = "Select %s from module_talk_chat where status!='%s' and provider_id=%s limit %s" % (
key, providerid, status_str, limit_count)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
if result_list != []:
value = result_list[0][0]
if value == '\x00':
return 0
elif value == '\x01':
return 1
else:
return value
else:
return result_list
def getChatKeyFromChatid(self, key, chatid, istype=True):
if istype:
select_str = "Select {0} from moudle_talk_chat where id={1}".format(
key, chatid)
else:
select_str = "Select {0} from moudle_talk_chat where id!={1}".format(
key, chatid)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
if result_list != []:
value = result_list[0][0]
if value == '\x00':
return 0
elif value == '\x01':
return 1
else:
return value
else:
return result_list
def close(self):
print('**************** Close DB ****************')
self.MysqlSaas.Close()
def sql(cls,
sql,
methed='select',
table_name=None,
insert_dict=None,
condition_dict=None):
TestBed_file = Parser_Config('TestEngineConfig.ini', 'TestParams',
'TestBedName')
ip = Parser_Config(TestBed_file, "Mysql", 'database_ip')
db_name = Parser_Config(TestBed_file, 'Mysql', 'dbname_saas')
user = Parser_Config(TestBed_file, 'Mysql', 'user_saas')
passwd = Parser_Config(TestBed_file, 'Mysql', 'password_saas')
MysqlConnect = ManageMysqlBase(ip=ip,
db_name=db_name,
user=user,
pwd=passwd)
if methed == 'select':
data = MysqlConnect.Select_Mysql_Sql(sql)
MysqlConnect.Close()
elif methed == 'update':
data = MysqlConnect.Update_Mysql_Sql(table_name, insert_dict,
condition_dict)
MysqlConnect.Close()
return data
class ManageSassDB(Singleton):
connection_pool = 0 #连接池个数
def __init__(self):
testbed = iTestFrameGetTestBed()
ip = Parser_Config(testbed, "Mysql", "database_ip")
user = Parser_Config(testbed, "Mysql", "user_saas")
password = Parser_Config(testbed, "Mysql", "password_saas")
dbName = Parser_Config(testbed, "Mysql", "dbname_saas")
dbport = Parser_Config(testbed, "Mysql", "database_port")
if self.connection_pool == 0:
self.MysqlSaas = ManageMysqlBase(ip, user, password, dbName,
dbport)
self.connection_pool += 1
def getProviderIdFromDomain(self, domain_str):
domain = domain_str.split('.')[0]
select_str = 'Select id from provider where sub_domain="%s"' % (domain)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getProviderIdFromUser(self, useremail):
if "@" in useremail:
select_str = 'Select provider_id from user where email="%s"' % (
useremail)
else:
select_str = 'Select provider_id from user where mobile_phone="%s"' % (
useremail)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getServiceDeskFromID(self, id):
select_str = "Select id,name from service_desk where id=%s" % (id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getServiceDeskFromProviderid(self, providerID, limit_count=1):
select_str = "Select id,name from service_desk where provider_id=%s limit %s" % (
providerID, limit_count)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list
def getDefaultServiceDeskIDFromProviderid(self, providerID):
select_str = u'Select id from service_desk where (name="默认服务台" or name="DEFAULT_SERVICE_DESK_NAME") and provider_id=%s and deleted=0' % (
providerID)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getUserFromProviderid(self,
providerID,
type='customer',
limit_count=1,
valid=1):
if type == 'customer':
select_str = "Select id,name,nickname,mobile_phone,email from user where provider_id=%s and type='customer' and deleted=0 and valid=%s limit %s" % (
providerID, valid, limit_count)
else:
select_str = "Select id,name,nickname,mobile_phone,email from user where provider_id=%s and type='engineer' and deleted=0 and valid=%s limit %s" % (
providerID, valid, limit_count)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list
def getUserPwdFromID(self, id):
select_str = "Select password from user where id=%s" % (id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
if result_list != []:
if result_list[0][0] == None:
return "123456"
else:
return result_list[0][0]
def getUserFromID(self, id):
select_str = "Select id,name,nickname,mobile_phone,email from user where id=%s" % (
id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getUserFromName(self, Name):
select_str = "Select id,name,nickname,mobile_phone,email from user where name=%s" % (
Name)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getUserFromEmailPid(self, email, providerid):
if "@" in email:
select_str = 'Select id,name,nickname,mobile_phone,email from user where email="%s" and provider_id=%s' % (
email, providerid)
else:
select_str = 'Select id,name,nickname,mobile_phone,email from user where mobile_phone="%s" and provider_id=%s' % (
email, providerid)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getEngineerFromProviderid(self,
providerID,
default_service_desk=None,
limit_count=1):
if default_service_desk == None:
select_str = "Select id,nickname,user_id from engineer where provider_id=%s limit %s" % (
providerID, limit_count)
else:
select_str = "Select id,nickname,user_id from engineer where provider_id=%s and default_service_desk_id=%s limit %s" % (
providerID, default_service_desk, limit_count)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list
def getEengineerfromID(self, id):
select_str = "Select id,nickname,user_id from engineer where id=%s" % (
id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getEengineerfromNickname(self, Nickname):
select_str = "Select id,nickname,user_id from engineer where nickname=%s" % (
Nickname)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getEengineerIDfromServiceDesk(self, service_desk_id):
select_str = "Select engineer_id from engineer_r_service_desk where service_desk_id=%s" % (
service_desk_id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list
def getWebFormFromProviderid(self, providerID, limit_count=1):
select_str = "Select id,name,type,deleted from custom_webform where provider_id=%s limit %s" % (
providerID, limit_count)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list
def getServiceIdFromEngineerID(self, engineer_id):
select_str = "Select id from engineer_r_service_desk where engineer_id=%s" % (
engineer_id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getEngineerIDFromServiceID(self, service_id, limit_count=1):
select_str = "Select engineer_id from engineer_r_service_desk where id=%s limit %s" % (
service_id, limit_count)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list
def getChannelIDFromTypeName(self,
providerID,
type,
namelike=None,
enable=None,
delete=None,
limit_count=1):
select_str = "Select id from channel where provider_id={} and type='{}' and enabled={} and deleted={} and name like '{}%' limit {}".format(
providerID, type, enable, delete, namelike, limit_count)
if namelike == None:
select_str = select_str.replace(
" and name like '{}%'".format(namelike), '')
if delete == None:
select_str = select_str.replace(' and deleted={}'.format(delete),
'')
if enable == None:
select_str = select_str.replace(' and enabled={}'.format(enable),
'')
try:
select_str = select_str.decode('utf8')
except:
pass
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list
def getChannelUidFromId(self, id):
select_str = "Select uid from channel where id={}".format(id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getChannelNameFromId(self, id):
select_str = "Select name from channel where id={}".format(id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getChannelKeyFromId(self, id, key):
select_str = "Select {0} from channel where id={1}".format(key, id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
value = result_list[0][0]
if value == '\x00':
return 0
elif value == '\x01':
return 1
else:
return value
def getChannelChartUidFromUid(self, uid, type='web'):
select_str = "Select chat_form_uid from channel_{}_config where channel_uid='{}'".format(
type, uid)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getChannelTickeUidFromUid(self, uid, type='web'):
select_str = "Select ticket_form_uid from channel_{}_config where channel_uid='{}'".format(
type, uid)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getChannelConfigIDFromUid(self, uid, type='web'):
select_str = "Select id from channel_{}_config where channel_uid='{}'".format(
type, uid)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getChannelConfigOldIDFromUid(self, uid, type='web'):
select_str = "Select old_id from channel_{}_config where channel_uid='{}'".format(
type, uid)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getChannelConfigEmailFromUid(self, uid, type='web'):
select_str = "Select email from channel_{}_config where channel_uid='{}'".format(
type, uid)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getChannelConfigTargetKeyFromUid(self, uid, key='id', type='web'):
select_str = "Select {0} from channel_{1}_config where channel_uid='{2}'".format(
key, type, uid)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getFormIDFromTypeName(self,
providerID,
type,
namelike=None,
enable=None,
delete=None,
limit_count=1):
select_str = "Select id from form where provider_id={} and type='{}' and enabled={} and deleted={} and name like '{}%' limit {}".format(
providerID, type, enable, delete, namelike, limit_count)
if namelike == None:
select_str = select_str.replace(
" and name like '{}%'".format(namelike), '')
if delete == None:
select_str = select_str.replace(' and deleted={}'.format(delete),
'')
if enable == None:
select_str = select_str.replace(' and enabled={}'.format(enable),
'')
try:
select_str = select_str.decode('utf8')
except:
pass
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list
def getFormUidFromId(self, id):
select_str = "Select uid from form where id={}".format(id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getFormNameFromId(self, id):
select_str = "Select name from form where id={}".format(id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list[0]
def getFormKeyFromId(self, id, key):
select_str = "Select {0} from form where id={1}".format(key, id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
value = result_list[0][0]
if value == '\x00':
return 0
elif value == '\x01':
return 1
else:
return value
def getAssignRuleIDFromTypeName(self,
providerID,
type,
namelike=None,
active=None,
delete=None,
limit_count=1):
select_str = "Select id from assign_Rule where provider_id={} and type='{}' and active={} and deleted={} and name like '{}%' limit {}".format(
providerID, type, active, delete, namelike, limit_count)
if namelike == None:
select_str = select_str.replace(
" and name like '{}%'".format(namelike), '')
if delete == None:
select_str = select_str.replace(' and deleted={}'.format(delete),
'')
if active == None:
select_str = select_str.replace(' and active={}'.format(active),
'')
try:
select_str = select_str.decode('utf8')
except:
pass
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
return result_list
def getAssignRuleKeyFromId(self, id, key):
select_str = "Select {0} from assign_Rule where id={1}".format(key, id)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
value = result_list[0][0]
if value == '\x00':
return 0
elif value == '\x01':
return 1
else:
return value
def getTicketidFromProviderIdAndStatus(self,
providerId,
status='open',
limit_count=1,
delete=0):
select_str = "Select id from ticket where provider_id={} and status='{}' and deleted='{}' limit {}".format(
providerId, status, delete, limit_count)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
ticket_id_list = []
for one in result_list:
ticket_id_list.append(one[0])
return ticket_id_list
def getAttachmentid(self, providerId, deleted=0, limit_count=1):
select_str = "Select id from attachment where provider_id={} and deleted='{}' limit {}".format(
providerId, deleted, limit_count)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
attachment_id_list = []
for one in result_list:
attachment_id_list.append(one[0])
return attachment_id_list
def deleteChannelOnlyOneFromName(self, channel_name_like, providerId):
select_str = 'Select id from channel where provider_id={} and deleted=0 and name like "%{}%" ORDER BY created_at ASC'.format(
providerId, channel_name_like)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
if result_list != []:
channel_id_last = result_list[-1][0] #排除最后一条渠道
update_str = 'UPDATE channel SET deleted=1 WHERE provider_id={0} and id!={1} and name like "%{2}%"'.format(
providerId, channel_id_last, channel_name_like)
print("----> Udate CMD: %s" % update_str)
self.MysqlSaas.excute_sql(update_str)
def deleteAssignRuleOnlyOneFromName(self, rule_name_like, providerId):
select_str = "Select id from assign_rule where provider_id={} and deleted=0 and name like '%{}%' ORDER BY created_at ASC".format(
providerId, rule_name_like)
result_list = self.MysqlSaas.Select_Mysql_Sql(select_str)
if result_list != []:
rule_id_last = result_list[-1][0] # 排除最后一条规则
update_str = 'UPDATE assign_rule SET deleted=1 WHERE provider_id={0} and id!={1} and name like "%{2}%"'.format(
providerId, rule_id_last, rule_name_like)
print("----> Udate CMD: %s" % update_str)
self.MysqlSaas.excute_sql(update_str)
def close(self):
print('**************** Close DB ****************')
self.MysqlSaas.Close()
def dispose(self):
self.MysqlSaas.dispose()