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, "")