def getTopTenPCode(self, req):
vendor = req.vendor
reimburseTag = req.reimburseTag
bg = req.bg
site = req.site
plant = req.plant
startdate = req.startdate
enddate = req.enddate
organization = Organization(bg, site, plant)
key_list = organization.get_redis_format()
if ServiceUtils.isMoreThanOneYear(startdate, enddate):
logger.info("超過一年內查詢區間 DB")
# TODO(Christine) 撈取DB資料
else:
## go to redis
logger.info("一年內查詢區間 redis")
'''redis'''
service = RedisGetPCodeTop10BarService(
key_list, startdate, enddate, vendor,
reimburseTag) # 1598889600 ~ 1609171200
json_data = service.serialize()
if json_data is not None:
dataJsonArray = json.loads(json_data) # JSON字串轉Python dict
result = ApiResponse.emitSuccessOutput(
dataJsonArray, msg="get data successfully.")
else:
result = ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
return result
def postPaymentTermDistribution(self, req):
vendor = req.vendor
reimburseTag = req.reimburseTag
bg = req.bg
site = req.site
plan = req.plant
startdate = req.startdate
enddate = req.enddate
if ServiceUtils.isMoreThanOneYear(startdate, enddate):
service = DatabasePaymentTermService(req)
return service.serialize()
else:
organization = Organization(bg, site, plan)
key_list = organization.get_redis_format()
service = RedisGetPaymentTermDistributionService(
key_list, startdate, enddate, vendor, reimburseTag)
json_data = service.serialize()
if json_data is not None:
dataJsonArray = json.loads(json_data) # JSON字串轉Python dict
result = ApiResponse.emitSuccessOutput(
dataJsonArray, msg="get data successfully.")
else:
result = ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
return result
def getVendorPurchase(self, vendor, reimburseTag, bg, site, plant,
startdate, enddate, vendorName):
# vendor = self.vendor
# reimburseTag = self.reimburseTag
# bg = self.bg
# site = self.site
# plant = self.plant
# startdate = self.startdate
# enddate = self.enddate
organization = Organization(bg, site, plant)
bg = organization.bg
site = organization.site
plant = organization.plant
key_list = organization.get_redis_format()
logger.info("超過一年內查詢區間 pass")
# TODO() 撈取DB資料
service = DatabaseGetVendorPurchaseListService(
bg, site, plant, startdate, enddate, vendor, reimburseTag,
vendorName) # 1598889600 ~ 1609171200
json_data = service.serialize()
if json_data is not None:
dataJsonArray = json.loads(json_data) # JSON字串轉Python dict
result = ApiResponse.emitSuccessOutput(
dataJsonArray, msg="get data successfully.")
else:
result = ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
return result
def getTopTenVendorChart(self, req):
vendor = req.vendor
reimburseTag = req.reimburseTag
bg = req.bg
site = req.site
plant = req.plant
startdate = req.startdate
enddate = req.enddate
organization = Organization(bg, site, plant)
bg = organization.bg
site = organization.site
plant = organization.plant
key_list = organization.get_redis_format()
if ServiceUtils.isMoreThanOneYear(startdate, enddate):
logger.info("超過一年內查詢區間 DB")
# TODO() 撈取DB資料
service = DatabaseGetTopTenVendorChartService(
bg, site, plant, startdate, enddate, vendor,
reimburseTag) # 1598889600 ~ 1609171200
json_data = service.serialize()
if json_data is not None:
dataJsonArray = json.loads(json_data) # JSON字串轉Python dict
resultArray = []
dataJsonArray.sort(key=itemgetter('site'))
groupArray = groupby(dataJsonArray, itemgetter('site'))
for key, group in groupArray:
vendorList = []
for g in group:
vendorList.append(g)
resultDic = {"site": key, "vendorList": vendorList}
resultArray.append(resultDic)
result = ApiResponse.emitSuccessOutput(
resultArray, msg="get data successfully.")
else:
result = ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
return result
else:
## go to redis
logger.info("一年內查詢區間 redis")
'''redis'''
service = RedisGetVendorTop10Service(
key_list, startdate, enddate, vendor,
reimburseTag) # 1598889600 ~ 1609171200
json_data = service.serialize()
if json_data is not None:
dataJsonArray = json.loads(json_data) # JSON字串轉Python dict
result = ApiResponse.emitSuccessOutput(
dataJsonArray, msg="get data successfully.")
else:
result = ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
return result
def getTopTenVendor(self, req):
vendor = req.vendor
reimburseTag = req.reimburseTag
bg = req.bg
site = req.site
plan = req.plant
startdate = req.startdate
enddate = req.enddate
datetime_start = datetime.fromtimestamp(startdate)
datetime_end = datetime.fromtimestamp(enddate)
dtStartStr = datetime_start.strftime('%Y-%m-%d')
dtEndStr = datetime_end.strftime('%Y-%m-%d')
if ServiceUtils.isMoreThanOneYear(startdate, enddate):
## go to sql
'''sql'''
sqlquery = '''select po.vendor_code as vandercode, po.vendor_name as vendername, sum(po.po_amount) as amount
from public."PURCHASE_ORDER" po
where po.vendor_code in (select vendor_code from public."PURCHASE_ORDER"
group by vendor_code order by sum(po_amount) desc limit 10)
group by po.vendor_code ,po.vendor_name
order by po.vendor_code'''
data = GetDataService.getwistrondata(self, sqlquery)
result = ApiResponse.emitSuccessOutput(
data, msg="get data successfully.")
return result
else:
## go to redis
'''redis'''
organization = Organization(bg, site, plan)
key_list = organization.get_redis_format()
service = RedisGetVendorTop10Service(key_list, startdate, enddate,
vendor, reimburseTag)
json_data = service.serialize()
if json_data is not None:
dataJsonArray = json.loads(json_data) # JSON字串轉Python dict
result = ApiResponse.emitSuccessOutput(
dataJsonArray, msg="get data successfully.")
else:
result = ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
return result
testdata = []
site = ['wks', 'wmy', 'wih', 'wmx', 'wok', 'wtz']
testdata = []
for sitename in site:
vendorList = []
for i in range(1, 11):
vendorList.append({
"vendorCode": "vendorCode" + str(i),
"vendorName": "vendorName" + str(i),
"amount": random.randint(100, 2000)
})
testdata.append({"site": sitename, "vendorList": vendorList})
result = json.dumps(testdata)
result = ApiResponse.emitSuccessOutput(json.loads(result))
return result
def get8Cate(self, req):
vendor = req.vendor
reimburseTag = req.reimburseTag
bg = req.bg
site = req.site
plan = req.plant
startdate = req.startdate
enddate = req.enddate
datetime_start = datetime.fromtimestamp(startdate)
datetime_end = datetime.fromtimestamp(enddate)
dtStartStr = datetime_start.strftime('%Y-%m-%d')
dtEndStr = datetime_end.strftime('%Y-%m-%d')
#if False:
if ServiceUtils.isMoreThanOneYear(startdate, enddate):
sqlquery = '''SELECT paym as category ,sum(amnt) as amount FROM public.mmp_impc_wks
GROUP BY paym
order by sum(amnt) desc'''
data = GetDataService.getwistrondata(self, sqlquery)
print(data)
if data is None:
return ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
result = []
column = ('category', 'amount')
for row in data:
print(row)
result.append(dict(zip(column, row)))
print(result)
result = json.dumps(result)
json_data = json.loads(result)
return ApiResponse.emitSuccessOutput(json_data)
else:
organization = Organization(bg, site, plan)
key_list = organization.get_redis_format()
service = RedisGet8CategorysDistributionService(
key_list, startdate, enddate, vendor, reimburseTag)
json_data = service.serialize()
if json_data is not None:
dataJsonArray = json.loads(json_data)
result = ApiResponse.emitSuccessOutput(
dataJsonArray, msg="get data successfully.")
else:
result = ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
return result
def postVenderTopTen(self, req):
vendor = req.vendor
reimburseTag = req.reimburseTag
bg = req.bg
site = req.site
plan = req.plant
startdate = req.startdate
enddate = req.enddate
if ServiceUtils.isMoreThanOneYear(startdate, enddate):
service = DatabaseVenderTop10Service(req)
return service.serialize()
else:
## go to redis
'''redis'''
organization = Organization(bg, site, plan)
key_list = organization.get_redis_format()
service = RedisGetVendorTop10Service(key_list, startdate, enddate,
vendor, reimburseTag)
json_data = service.serialize()
if json_data is not None:
dataJsonArray = json.loads(json_data) # JSON字串轉Python dict
result = ApiResponse.emitSuccessOutput(
dataJsonArray, msg="get data successfully.")
else:
result = ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
return result
testdata = []
site = ['wks', 'wmy', 'wih', 'wmx', 'wok', 'wtz']
testdata = []
for sitename in site:
vendorList = []
for i in range(1, 11):
vendorList.append({
"vendorCode": "vendorCode" + str(i),
"vendorName": "vendorName" + str(i),
"amount": random.randint(100, 2000)
})
testdata.append({"site": sitename, "vendorList": vendorList})
result = json.dumps(testdata)
result = ApiResponse.emitSuccessOutput(json.loads(result))
return result
def getTopTenPO(self, req):
vendor = req.vendor
reimburseTag = req.reimburseTag
bg = req.bg
site = req.site
plan = req.plant
startdate = req.startdate
enddate = req.enddate
datetime_start = datetime.fromtimestamp(startdate)
datetime_end = datetime.fromtimestamp(enddate)
dtStartStr = datetime_start.strftime('%Y-%m-%d')
dtEndStr = datetime_end.strftime('%Y-%m-%d')
if ServiceUtils.isMoreThanOneYear(startdate, enddate):
sqlquery = '''
select po.vendor_code as vandercode, po.vendor_name as vendername, sum(po.po_amount) as amount
from public."PURCHASE_ORDER" po
where po.vendor_code in (select vendor_code from public."PURCHASE_ORDER"
group by vendor_code order by sum(po_amount) desc limit 10)
group by po.vendor_code ,po.vendor_name
order by po.vendor_code
'''
data = GetDataService.getwistrondata(self, sqlquery)
if data is None or data == []:
return ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
result = []
format_string = ''
column = ('poNo', 'poAmount', 'PCodeOrDeptCode', 'vendorName',
'prRemark')
for row in data:
result.append(dict(zip(column, row)))
count = 1
for amountdata in result:
print(type(amountdata))
if amountdata.get('prRemark') is None:
amountdata['prRemark'] = ''
format_string = format_string + '\"' + str(
count) + '\":' + str(amountdata) + ','
count += 1
format_string = format_string[:-1]
format_string = format_string.replace("\'", "\"")
format_string = '{' + format_string + '}'
print(format_string)
json_data = json.loads(format_string)
return ApiResponse.emitSuccessOutput(json_data)
else:
organization = Organization(bg, site, plan)
key_list = organization.get_redis_format()
service = RedisGetPOTop10Service(key_list, startdate, enddate,
vendor, reimburseTag)
json_data = service.serialize()
if json_data is not None:
dataJsonArray = json.loads(json_data) # JSON字串轉Python dict
result = ApiResponse.emitSuccessOutput(
dataJsonArray, msg="get data successfully.")
else:
result = ApiResponse.emitErrorOutput(E_QUERY_FAIL, "查無資料",
"no data")
return result