def getUpdateSpare(spare_id):
params = []
sql = """ select ha.id, ha.spare_pn, ha.spare_description, ha.spare_product_pn, ha.spare_substitue_pn,(select hpd.id from hsh_product_spare h inner join hsh_product hpd on h.product_id=hpd.id
where h.spare_id=ha.id) product_id , (select ty.id from hsh_spare_type ty
where ty.id = ha.spare_type_id)type_id from hsh_spare ha where ha.id = %s"""
params.append(spare_id)
return executeQuery(sql, params, "")
def SearchTipCount(search_text):
params = []
sql = " SELECT count(*)count from hsh_tip c where 1=1 "
if isblank(search_text):
sql = sql + " and c.tip_detail like %s "
params.append(search_text)
return executeQuery(sql, params, "")
def SearchDocCount(search_text):
params = []
sql = " SELECT count(*)count from hsh_doc where 1=1 "
if isblank(search_text):
sql = sql + " and doc_url like %s or doc_title like %s "
params.append(search_text)
params.append(search_text)
return executeQuery(sql, params, "json")
def getProductById(product_id):
params = []
sql = """ SELECT prop.id as property_id, prop.code_name, prop.ga_date, prop.max_cache,prop.product_hight,
prop.bandwidth, prop.system_architecture, prop.data_sheet, prop.is_show, prod.id as product_id, prod.product_name, prod.product_long_name,
prod.product_eol, prod.product_view_url, prod.hsh_category_id, prod.create_time
from hsh_property prop right join hsh_product prod on prop.id = prod.hsh_property_id
where prod.id =%s """
params.append(product_id)
return executeQuery(sql, params, "")
def getUserLogin(user_name, user_pass):
# 准备sql语句
sql = "select user_name,user_pass from hsh_user where user_name=%s and user_pass=%s"
value = []
value.append(user_name)
value.append(user_pass)
# 参数一: sql语句
# 参数二: 参数值
# 参数三: 返回的数据类型
return executeQuery(sql, value, "")
def SearchAlertCount(search_text):
params = []
sql = " SELECT count(*)count from hsh_alert c where 1=1 "
if isblank(search_text):
sql = sql + " and c.alert_detail like %s "
params.append(search_text)
if isblank(search_text):
sql = sql + " or c.alert_number like %s "
params.append(search_text)
return executeQuery(sql, params, "json")
def selectCurrentSystemByProductName(product_name):
params = []
sql = """ SELECT prop.id as property_id, prop.code_name, prop.ga_date,
prop.max_cache, prop.product_hight, prop.bandwidth, prop.system_architecture, prop.data_sheet,
prop.is_show, prod.id as product_id, prod.product_name, prod.product_long_name, prod.product_eol,
prod.product_view_url, prod.create_time
from hsh_property as prop RIGHT JOIN hsh_product as prod ON
prod.hsh_property_id = prop.id
where prop.id in
(select hsh_property_id from hsh_product where product_name=%s) """
params.append(product_name)
return executeQuery(sql, params, "")
def SearchDoc(search_text, page):
params = []
sql = """ SELECT ha.id, ha.doc_title, ha.doc_url, (SELECT GROUP_CONCAT(hpd.product_name) FROM hsh_product_doc h INNER JOIN hsh_product hpd
ON h.product_id=hpd.id WHERE h.doc_id=ha.id) product_name FROM hsh_doc ha """
if isblank(search_text):
sql = sql + "WHERE ha.doc_title like %s or ha.doc_url like %s "
params.append(search_text)
params.append(search_text)
sql = sql + "GROUP BY id limit %s, %s "
params.append((int(page) - 1) * Resp.PAGESIZE)
params.append(Resp.PAGESIZE)
return executeQuery(sql, params, "")
def SearchSpareCount(search_text):
params = []
sql = " SELECT count(*)count from hsh_spare s where 1=1 "
if isblank(search_text):
sql = sql + " and s.spare_pn like %s "
params.append(search_text)
if isblank(search_text):
sql = sql + " or s.spare_product_pn like %s "
params.append(search_text)
if isblank(search_text):
sql = sql + " or s.spare_substitue_pn like %s "
params.append(search_text)
return executeQuery(sql, params, "json")
def SearchEOLProductCount(search_text):
params = []
sql = " SELECT count(*) count from hsh_category c INNER JOIN hsh_product p ON c.id = p.hsh_category_id where p.product_eol=1 "
if isblank(search_text):
sql = sql + " and c.category_name like %s "
params.append(search_text)
if isblank(search_text):
sql = sql + " or p.product_name like %s "
params.append(search_text)
if isblank(search_text):
sql = sql + " or p.product_long_name like %s "
params.append(search_text)
return executeQuery(sql, params, "json")
def SearchTip(search_text, page):
params = []
sql = """ SELECT hsh_tip.`id` AS tip_id,hsh_tip.`tip_detail`,hsh_product.`product_name`
FROM hsh_tip
LEFT JOIN hsh_product_tip ON hsh_tip.`id`=hsh_product_tip.`tip_id`
LEFT JOIN hsh_product ON hsh_product.`id`= hsh_product_tip.`product_id`
where 1=1"""
if isblank(search_text):
sql = sql + " and hsh_tip.tip_detail like %s "
params.append(search_text)
sql = sql + " GROUP BY hsh_tip.id limit %s,%s"
params.append((int(page)-1) * Resp.PAGESIZE)
params.append(Resp.PAGESIZE)
return executeQuery(sql, params, "")
def SearchAlert(search_text, page):
params = []
sql = """ select *,(select GROUP_CONCAT(hpd.product_name) from hsh_product_alert h inner join hsh_product hpd on h.product_id=hpd.id where h.alert_id=ha.id) product_name
from hsh_alert ha """
if isblank(search_text):
sql = sql + " and ha.alert_detail like %s "
params.append(search_text)
if isblank(search_text):
sql = sql + " or ha.alert_number like %s "
params.append(search_text)
sql = sql + " GROUP BY ha.id limit %s,%s"
params.append((int(page) - 1) * Resp.PAGESIZE)
params.append(Resp.PAGESIZE)
return executeQuery(sql, params, "")
def SearchSpare(search_text, page):
params = []
sql = """ select ha.id, ha.spare_pn, ha.spare_description, ha.spare_product_pn, ha.spare_substitue_pn,(select GROUP_CONCAT(hpd.product_name) from hsh_product_spare h inner join hsh_product hpd on h.product_id=hpd.id
where h.spare_id=ha.id) product_name , (select GROUP_CONCAT(ty.spare_type_name) from hsh_spare_type ty
where ty.id = ha.spare_type_id)type_name from hsh_spare ha
"""
if isblank(search_text):
sql = sql + " and ha.spare_pn like %s "
params.append(search_text)
if isblank(search_text):
sql = sql + " or ha.spare_product_pn like %s "
params.append(search_text)
if isblank(search_text):
sql = sql + " or ha.spare_substitue_pn like %s "
params.append(search_text)
sql = sql + " GROUP BY ha.id limit %s,%s"
params.append((int(page) - 1) * Resp.PAGESIZE)
params.append(Resp.PAGESIZE)
return executeQuery(sql, params, "")
def SearchProduct(search_text, page):
params = []
sql = """ SELECT c.id as category_id, category_name, p.id as product_id
, p.product_name, p.product_long_name, p.hsh_property_id, p.product_eol
,p.product_view_url, p.create_time as create_time from hsh_category c INNER JOIN hsh_product p ON c.id = p.hsh_category_id
where p.product_eol=0 """
if isblank(search_text):
sql = sql + " and c.category_name like %s "
params.append(search_text)
if isblank(search_text):
sql = sql + " or p.product_name like %s "
params.append(search_text)
if isblank(search_text):
sql = sql + " or p.product_long_name like %s "
params.append(search_text)
sql = sql + " ORDER BY p.create_time DESC limit %s,%s"
params.append((int(page) - 1) * Resp.PAGESIZE)
params.append(Resp.PAGESIZE)
return executeQuery(sql, params, "")
def getPropertyIdByProductId(product_id):
params = []
sql = " select hsh_property_id from hsh_product where id = %s "
params.append(product_id)
return executeQuery(sql, params, "")
def getProductList():
params = []
sql = " SELECT id, product_name, product_long_name FROM hsh_product "
return executeQuery(sql, params, "")
def getDocid(doc_title, doc_url):
params = [doc_title, doc_url]
sql = " SELECT id, doc_title, doc_url FROM hsh_doc WHERE doc_title=%s AND doc_url=%s "
return executeQuery(sql, params, "")
def getUpdateDoc(doc_id):
params = [doc_id]
sql = """ SELECT id, doc_title, doc_url, (SELECT hpd.id FROM hsh_product_doc h INNER JOIN hsh_product hpd on h.product_id=hpd.id
WHERE h.doc_id=ha.id) AS product_id from hsh_doc ha where ha.id=%s """
return executeQuery(sql, params, "")
def getUserPermission(user_name):
sql = " select * from hsh_user u where u.user_name = %s "
return executeQuery(sql, [user_name], "")
def getModdByAlertId(alert_id):
params = []
sql = " SELECT product_id from hsh_product_alert where alert_id = %s"
params.append(alert_id)
return executeQuery(sql, params, "")
def getTheirProductTip():
params = []
sql = "SELECT id, product_name, product_long_name from hsh_product"
return executeQuery(sql, params, "")
def getCurrentSystemCategory():
sql = " SELECT * from hsh_category "
return executeQuery(sql, [], "JSON")
def getProductCount():
sql = """ select count(*)count
from hsh_category as c RIGHT JOIN hsh_product as p ON
c.id = p.hsh_category_id """
return executeQuery(sql, [], "json")
def getUpdateAlert(alert_id):
params = []
sql = """ select *,(select hpd.id from hsh_product_alert h inner join hsh_product hpd on h.product_id=hpd.id
where h.alert_id=ha.id) product_id from hsh_alert ha where ha.id = %s"""
params.append(alert_id)
return executeQuery(sql, params, "")
def getModdByDocId(doc_id):
params = [doc_id]
sql = " SELECT product_id from hsh_product_doc where doc_id=%s "
return executeQuery(sql, params, "")
def getCurrentSystemProduct():
sql = """ select c.id as category_id, c.category_name,p.id as product_id, GROUP_CONCAT(p.product_name)product_name,
p.product_long_name, p.product_eol, product_view_url, p.hsh_property_id, p.create_time
from hsh_category as c RIGHT JOIN hsh_product as p ON
c.id = p.hsh_category_id GROUP BY c.category_name, product_eol """
return executeQuery(sql, [], "")
def getModdByTipId(tip_id):
params = []
sql = " SELECT product_id from hsh_product_tip where tip_id = %s"
params.append(tip_id)
return executeQuery(sql, params, "")
def getModdBySpareId(spare_id):
params = []
sql = " SELECT product_id from hsh_product_spare where spare_id = %s"
params.append(spare_id)
return executeQuery(sql, params, "")
def getUpdateTip(tip_id):
params = []
sql = """ SELECT *,(SELECT hpd.id FROM hsh_product_tip h INNER JOIN hsh_product hpd ON h.product_id=hpd.id
WHERE h.tip_id=ha.id) product_id FROM hsh_tip ha WHERE ha.id = %s"""
params.append(tip_id)
return executeQuery(sql, params, "")
def getSpareTypeList():
params = []
sql = "SELECT id, spare_type_name from hsh_spare_type"
return executeQuery(sql, params, "")
