def gethscinfo(hsc_id):
hsc_sql = '''SELECT h.HSC_ID, h.RESOURCE_CONFIG_ID, h.ROUTER_ID, h.HSC_NAME, h.CIRCLE_NUM, h.CIRCLE_ID, CASE
`STATUS` WHEN 0 THEN '待受理' WHEN 1 THEN '已完工' WHEN 2 THEN '审核不通过' WHEN 3 THEN '待退订' WHEN 4 THEN
'已退订' WHEN 5 THEN '开通配置中' ELSE 'undefined' END AS 'STATUS', h.IS_DELETE, h.USER_ID, h.CUSTOMER_ID,
h.CREATED_TIME, h.MODIFIED_TIME, h.REGION, hc.CIRCLE_ID, hc.ECLOUD_CIDR, hc.DEST_CIDR, hc.BANDWIDTH, hc.IS_DELETE
AS CIRCLE_IS_DELETE, hc.PROVINCE_ADDR, hc.CITY_ADDR, hc.DIST_ADDR, hc.ADDR_DETAIL, hc.USER_ID, hc.CUSTOMER_ID
FROM g_biz_hsc h LEFT JOIN g_biz_hsc_circle hc ON h.HSC_ID = hc.HSC_ID WHERE h.HSC_ID = "%s"''' % hsc_id
hsc_info = utils.execsql(hsc_sql, opconfig['OP_CLOUDMASTER_CONFIG'])
return hsc_info
def getFipinfo_by_port(ippb_id):
sql1 = '''SELECT ippb.ID, ippb.`NAME`, ippb.CREATED_TIME, ippb.PROPOSER, ippb.CUSTOMER_ID,
ippb.IS_DELETE, bw.ID AS 'BANDWIDTH_ID',
bw.BANDWIDTH_SIZE,case ippb.ICP_STATUS WHEN 0 THEN '未申请' WHEN 1 THEN 'IP备案中' WHEN 2 THEN 'IP备案成功'
WHEN 3 THEN 'IP备案失败' WHEN 4 THEN 'ICP备案中' WHEN 5 THEN 'ICP备案成功' WHEN 6 THEN 'ICP备案失败 ' WHEN 7
THEN '注销成功' WHEN 8 THEN '注销失败' ELSE 'UNDEFINED' END AS 'ICP_STATUS', case ippb.`STATUS` WHEN 0
THEN '未绑定' WHEN 1 THEN '绑定' ELSE 'UNDEFINED' END AS 'STATUS', ippb.MODIFIED_TIME
FROM os_biz_ippb ippb LEFT JOIN os_biz_bandwidth bw on bw.PUBLIC_IP = ippb.`NAME`
where ippb.ID = "%s" AND bw.IS_DELETE =0''' % (ippb_id)
ipinfo = utils.execsql(sql1, opconfig['OP_OPENSTACK_CONFIG'])
return ipinfo
def getVMInfo(vm_id):
sql1 = '''SELECT ID, `NAME`, VCPU, VMEMORY, VDISK, HW_HOST_ID, CASE `STATUS` WHEN 1 THEN 'ACTIVE' WHEN 2 THEN
'BUILD' WHEN 3 THEN 'REBUILD' WHEN 4 THEN 'SUSPENDED' WHEN 5 THEN 'PAUSED' WHEN 6 THEN 'RESIZE' WHEN 7 THEN
'VERIFY_RESIZE' WHEN 8 THEN 'REVERT_RESIZE' WHEN 9 THEN 'PASSWORD' WHEN 10 THEN 'REBOOT' WHEN 11 THEN 'HARD_REBOOT'
WHEN 12 THEN 'DELETED' WHEN 13 THEN 'UNKNOWN' WHEN 14 THEN 'ERROR' WHEN 15 THEN 'STOPPED' WHEN 16 THEN 'SHUTOFF'
WHEN 17 THEN 'MIGRATING' WHEN 18 THEN 'SHELVED' ELSE 'UNDEFINED' END AS 'STATUS', IMAGE_NAME, IMAGE_REF,
FLAVOR_NAME, FLAVOR_REF, TASK, CASE OPERATION_FLAG WHEN 0 THEN '重装' WHEN 1 THEN '备份恢复' ELSE 'UNDEFINED'
END AS 'OPERATION_FLAG', PROPOSER, CUSTOMER_ID, POOL_ID, CREATED_BY, CREATED_TIME, MODIFIED_BY, MODIFIED_TIME,
PWD, IS_DELETE, AVAILABILITY_ZONE FROM os_biz_vm_host WHERE id = '%s' ''' % vm_id
vminfo = utils.execsql(sql1, opconfig['OP_OPENSTACK_CONFIG'])
return vminfo
def getOrderInfo(order_id):
"""get order information according to order_id"""
ordersql = """ SELECT ORDER_ID, CASE ORDER_TYPE WHEN 1 THEN '新建' WHEN 2 THEN '取消' WHEN 3 THEN '暂停' WHEN
4 THEN '恢复' WHEN 5 THEN '变更' WHEN 6 THEN '续订' WHEN 7 THEN '试用创建' WHEN 8 THEN '试用转商用' WHEN 9 THEN
'成员开通' WHEN 10 THEN 'PAAS、SAAS取消' WHEN 12 THEN '内部优惠审批订单'
ELSE 'UNDEFINED' END AS 'ORDER_TYPE', CASE ORDER_STATUS WHEN 1 THEN
'支付成功' WHEN 2 THEN '等待支付' WHEN 3 THEN '已经取消' WHEN 4 THEN '已关闭' ELSE 'UNDEFINED' END AS
'ORDER_STATUS', PARENT_ID, ORDER_TIME, PROPOSER, PROPOSER_NAME, CMP_CUSTOMER_ID, CMP_CUSTOMER_NAME, ORDER_MEMO,
ORDER_SOURCE, CONFIRM_BY, APPROVAL_MSG, CASE OPEN_TYPE WHEN 0 THEN '离线开通' WHEN 1 THEN '在线开通' WHEN 2
THEN 'ITIL流程' ELSE 'UNDEFINED' END AS 'OPEN_TYPE', ORDER_JSON, CONFIRM_TIME, TOTAL FROM opm_order WHERE
ORDER_ID = "%s" """ % order_id
return utils.execsql(ordersql, opconfig['OP_CLOUDMASTER_CONFIG'])
def geticpinfo(icp_public_ip):
icp_sql = '''SELECT ID, ICP_ID, USER_ID, ICP_CERT_CODE, CASE ICP_STATUS WHEN - 2 THEN '服务台审批不通过'
WHEN - 1 THEN '服务台审批' WHEN 0 THEN '未申请备案' WHEN 1 THEN '初审中' WHEN 2 THEN '初审未通过' WHEN 5 THEN
'管局审核中' WHEN 6 THEN '管局审核通过' WHEN 7 THEN '管局审核未通过' WHEN 8 THEN '注销待审核' WHEN 9 THEN
'注销初审不通过' WHEN 12 THEN '注销初审通过' WHEN 13 THEN '已注销' WHEN 14 THEN '注销失败' WHEN 15 THEN
'申请失败' WHEN 16 THEN '变更初审中' WHEN 17 THEN '变更未通过' WHEN 18 THEN '变更管局审核中' WHEN 19 THEN
'变更管局审核未通过' WHEN 25 THEN '二级域名备案启用' WHEN 26 THEN '二级域名备案禁止' ELSE 'undefined'
END AS 'ICP_STATUS', CASE ICP_TYPE WHEN 1 THEN '首次备案' WHEN 2 THEN '接入备案' WHEN 3 THEN '备案登记' ELSE
'undefined' END AS 'ICP_TYPE', ICP_REGISTER_TIME, ICP_UPDATE_TIME, ICP_PUBLIC_IP, ICP_DOMAIN, ICP_PROVINCE,
ICP_CITY, ICP_SUGGESTION, ICP_AUDITOR, ICP_CONTACTOR, ICP_CONTACTOR_PHONE, POOL_ID, CASE ICP_BBFS WHEN 0 THEN
'自行报备' WHEN 1 THEN '代为报备' ELSE 'UNDEFINED' END AS 'ICP_BBFS', CASE ICP_RETURN_CODE WHEN 0 THEN '备案系统退回'
WHEN 1 THEN '备案系统管理员核实退回' WHEN 2 THEN '管局审核中' WHEN 3 THEN '管局审核通过' WHEN 4 THEN '管局退回' ELSE
'UNDEFINED' END AS 'ICP_RETURN_CODE', ICP_RETURN_MSG, IP_ID, ICP_IS_SUCCESS FROM opm_icp_info WHERE
ICP_PUBLIC_IP like '%%%s%%' ''' % icp_public_ip
icp_info = utils.execsql(icp_sql, opconfig['OP_CLOUDMASTER_CONFIG'])
return icp_info
def mysqlExecSql(self, pool):
"""
查询数据库通过project_id获取nova.quotas中信息（deleted=0,用户配额信息）
"""
dataconf = database_conf.ec_conf(pool)
# hosts = str_input
if len(pool) > 0:
ec_uuid = SQL.ec_hosts_get_nodes_info()
self.sql_result['result'] = utils.execsql(sql=ec_uuid,
databaseconf=dataconf)
for i in self.sql_result['result']:
print '\n############################'
for key, value in i.items():
if str(key) == '规格':
f = json.loads(str(value))
flavor = f['cur']['nova_object.data']['name']
print '%-20s' % str(key), ' : ', str(flavor)
else:
print '%-20s' % str(key), ' : ', str(value)
def getuserinfo_email(self):
"""get user information according to email"""
usersql = """ select u.id user_id,u.user_name,u.email,u.phone,case u.IS_CUSTOMER when 0 then '用户' when 1
then '客户' end 'is_customer',u.LOGIN_FAILED_COUNT,u.create_time ucreate_time,u.password,case u.user_status
when 1 then '正常' when 2 then '暂停' when 3 then '非法' when 4 then '非法及暂停' when 5 then '锁定' when 6
then '已注销' end as user_status, case u.type when 1 then '部门管理员' when 2 then '部门普通用户'
end as type,u.creator,u.modified_by,u.modify_time umodify_time,case inform_type when 0 then '不通知'
when 1 then '短信' when 2 then '邮件' when 3 then '短信+邮件' end as inform_type,u.is_delete,
case u.is_first_login when 1 then '否' when 0 then '是' end as 'is_first_login',u.nick_name,
c.id customer_id,c.boss_cust_id,case c.cust_status WHEN 1 THEN '正常' WHEN 2 THEN '暂停' WHEN 3 THEN '待审批'
WHEN 4 THEN '审批未通过' WHEN 5 THEN '欠费' WHEN 6 THEN '已注销' WHEN 7 THEN '等待删除' WHEN 11 THEN '注册待归档'
WHEN 12 THEN '待基础产品订单同步' WHEN 13 THEN '待基础产品反馈' WHEN 8 THEN '预注销'
ELSE 'undefined' END AS 'CUST_STATUS',case
c.REG_SOURCE when 0 then '互联网客户' when 1 then 'bboss客户' when 2 then '政企客户' when 3 then 'SaaS客户'end
as 'reg_source', case c.is_huawei WHEN 0 THEN '四期客户' WHEN 1 THEN '三期客户' WHEN 2 THEN
'三期无订单老客户' ELSE 'undefied' END as 'IS_HUAWEI',c.boss_pro_order_code,c.global_discount,
case c.payment_type when 1 then '预付费' when 2 then '后付费' end as 'payment_type' , c.contactor ,
c.mng_phone,c.name as cust_name,c.email as cust_email,c.mobile as cust_phone,c.province,c.cont_phone,
c.cont_email,c.cmcc_cust_mng,c.modify_time,c.create_time,c.boss_order_id from opm_user_user u left join
opm_customer c on u.customer_id = c.id where u.email = "%s" """ % self.email.encode(
'utf-8')
return utils.execsql(usersql, opconfig['OP_CLOUDMASTER_CONFIG'])
def getVdiskinfo(id):
sql1 = '''SELECT id,name,size,host_id,volume_type type,created_time,proposer,status FROM openstack.os_biz_ebs
where id = '%s' and is_delete=0''' % id
vdiskinfo = utils.execsql(sql1, opconfig['OP_OPENSTACK_CONFIG'])
return vdiskinfo
def getOperalog(user_id, begin_time, end_time):
"""get operation log according to user_id"""
operalogsql = """SELECT ID, `ACTION`, OPERATOR_TIME, CUSTOMER_ID, USER_ID, EXTRA, POOL_ID, RESULT_CODE, COST_TIME,
RESULT_MSG FROM os_biz_operator_log WHERE USER_ID = "%s" AND OPERATOR_TIME BETWEEN "%s" AND "%s" """ \
% (user_id, begin_time, end_time)
return utils.execsql(operalogsql, opconfig['OP_OPENSTACK_CONFIG'])
def getUserId(username):
"""get user_id according to username"""
usersql = """ SELECT ID from opm_user_user WHERE USER_NAME = "%s" """ % username
return utils.execsql(usersql, opconfig['OP_CLOUDMASTER_CONFIG'])
def getbosslog_id(self):
"""get user information according to boss_order_id"""
usersql = """select * from opm_bossagent_log where boss_order_id = "%s" order by id """ % self.boss_order_id
return utils.execsql(usersql, opconfig['OP_CLOUDMASTER_CONFIG'])
def getbossloginfo(self):
"""get user information according to op_order_number"""
usersql = """select * from opm_bossagent_log where op_order_number = "%s" order by id """ % self.op_order_number
return utils.execsql(usersql, opconfig['OP_CLOUDMASTER_CONFIG'])
def getPromotionInfo(order_id):
"""get promotion information according to order_id"""
promotionsql = """SELECT PROMOTION_ID, STATUS, ORDER_ID FROM opm_promotion_flow WHERE ORDER_ID = "%s" """ % order_id
return utils.execsql(promotionsql, opconfig['OP_CLOUDMASTER_CONFIG'])
def getBosslogInfo(op_order_num):
"""get bossagent log information according to ext_id"""
bosslogsql = """SELECT TYPE, ORDERNUMBER, BOSS_ORDER_ID, OP_ORDER_NUMBER, REQUESR_MSG, RESPONSE_MSG, IS_SUCCESS,
DIRECTION, DATE, ERROR_DESC FROM opm_bossagent_log WHERE OP_ORDER_NUMBER = "%s" """ % op_order_num
return utils.execsql(bosslogsql, opconfig['OP_CLOUDMASTER_CONFIG'])