def getFlowList():
"""
从数据库获取所有的工作流列表
:return:返回{"name":FlowName,"value":FlowID}
"""
raw = rawSql.Raw_sql()
raw.sql = "SELECT FlowID AS value, FlowName AS name FROM RMI_WORK_FLOW WITH(NOLOCK)"
res, columns = raw.query_all(needColumnName=True)
return CommonUtilities.translateQueryResIntoDict(columns, res)
def smartReturn(res, cols): """ 根据查询的字段数，如果只有一个字段，直接将结果存入一个列表中返回，否则返回一个键值对组成的列表 :param res:查询结果序列 :param cols:查询字段序列 :return: """ if len(cols) == 1: return [ row[0] for row in res ] else: return CommonUtilities.translateQueryResIntoDict(cols, res)
def getUserInfoByIdOrName(IdOrName): """ 根据ID或者工号模糊查询获取对应的员工信息 :param IdOrName: ID或者员工名称的信息 :return: """ raw = Raw_sql() raw.sql = """SELECT ID, Name, Password, DepartmentID, JobID, Permission, CONVERT(varchar(16), CreateTime, 20) CreateTime, CONVERT(varchar(16), LastModifiedTime, 20) LastModifiedTime FROM RMI_ACCOUNT_USER WHERE ID LIKE '%%%%%s%%%%' OR Name LIKE '%%%%%s%%%%'"""%(IdOrName, IdOrName) res, cols = raw.query_all(needColumnName=True) return CommonUtilities.translateQueryResIntoDict(res=res, columns=cols)
def getPagedInfo(self, pageNo, pageSize, columns, columnsAlternativeNames, whereColumns, whereValues, orderString):
"""
分页查询接口
:param pageNo:页码
:param pageSize:页面大小
:param columns:字段
:param columnsAlternativeNames:字段替代名
:param whereColumns:where语句的字段列表
:param whereValues:where语句的字段列表对应的值
:param orderString:排序语句
:return:{''}
"""
res, cols, count = self.raw.pagedQuery(pageNo, pageSize, self.dataSourceTable, self.primaryKey, self.formatColumnString(columns, columnsAlternativeNames),
self.formatFuzzyWhereString(whereColumns, whereValues), orderString, needCounts=True, needColumnName=True )
return {'listData':CommonUtilities.translateQueryResIntoDict(cols, res), 'count':count}
def getAllMaterialByName(fuzzyName):
"""
根据模糊输入获取所有材料的名称
:param fuzzyName:模糊输入
:return:{'id':材料名称ID,'name':材料名称,'cata':材料种类名称}
"""
raw = rawSql.Raw_sql()
raw.sql = """SELECT MaterialID AS id, MaterialName AS name, dbo.getMaterialTypeNameByID(MaterialTypeID) AS cata
FROM RMI_MATERIAL_NAME WITH(NOLOCK)"""
if fuzzyName:
raw.sql += """ WHERE MaterialName LIKE '%%%%%s%%%%'"""%fuzzyName
res, cols = raw.query_all(needColumnName=True)
return CommonUtilities.translateQueryResIntoDict(cols, res)
else: #如果为空返回空数据，否则前端卡顿
return [{"name":u'请输入关键字', "id":"", "cata":""}]
def getSuppliersAssessmentDataByDate(start, end):
"""
根据时间跨度范围来获取供应商评审数据
:param start:开始时间 e.g. 2016-01-01
:param end:结束时间 e.g. 2016-02-02
:return:[{"GongYingShangMingCheng": 供应商名称, "BuHeGeShuLiang": 统计期内不合格批次数量,
"TongJiQiNeiDaoHuoPiCi": 统计期内到货批次, "GongHuoShuLiang": 供货数量, "GongYingShangBianMa": 供应商编码,
"JinHuoJianYanHeGeLv": 进货检验合格率, "BuHeGePiCi": 不合格批次数量},...]
"""
raw = rawSql.Raw_sql()
start = "-".join([number.rjust(2,'0') if len(number) < 2 else number for number in start.split('-')])
end = "-".join([number.rjust(2,'0') if len(number) < 2 else number for number in end.split('-')])
raw.sql = """SELECT GongYingShangBianMa, GongYingShangMingCheng,
CAST(SUM(GongHuoShuLiang) as varchar(50)) + DaoHuoShuLiangDanWei GongHuoShuLiang, SUM(TongJiQiNeiDaoHuoPiCi) TongJiQiNeiDaoHuoPiCi,
SUM(BuHeGePiCi) BuHeGePiCi, SUM(BuHeGeShuLiang) BuHeGeShuLiang,
CAST(CAST( 1 - CAST(SUM(BuHeGePiCi) AS DECIMAL(9,2)) / CAST(SUM(TongJiQiNeiDaoHuoPiCi) AS DECIMAL(9,2)) AS DECIMAL(9,2)) * 100 AS INT) JinHuoJianYanHeGeLv
FROM dbo.SupplierInfoAnalysis
WHERE RiQi >= '%s' AND RiQi <= '%s'
GROUP BY GongYingShangBianMa, GongYingShangMingCheng, DaoHuoShuLiangDanWei"""%(start,end)
res, cols = raw.query_all(needColumnName=True)
return CommonUtilities.translateQueryResIntoDict(columns=cols, res=res)