1. 변경 사항
- 기존의 데이터 연동 사항 중 판매수량을 받아오던 쿼리를 수정하여 매출(재고중량)을 받아오는 것으로 수정
2. 이전 코드
def performance_weight(date_standard, start_date, end_date):
ret_list = []
with connections['testdb'].cursor() as cursor:
if date_standard == '연':
datetime_formate = "%Y"
start_date = datetime.strptime(start_date, datetime_formate)
end_date = datetime.strptime(end_date, datetime_formate)
start_year = int(start_date.year)
end_year = int(end_date.year)
for i in list(range(start_year, end_year+1)):
d = dict()
d['기간'] = '{}년'.format(i)
cursor.execute(
"""
SELECT sum(case when p.proname = '절단품' then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYH%' or m.mach_name LIKE '%평면%') AND (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYV%' or m.mach_name LIKE '%측면%') and (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end)
from prod_m m join prod_p p on m.mcode = p.mcode and m.dcode = p.scode
WHERE deal_date BETWEEN '{}-01-01' and '{}-12-31';
""".format(i, i)
)
row1 = cursor.fetchall()
d['절단'] = round(row1[0][0]/1000, 0)
if row1[0][0] is None: d['절단'] = 0
d['평면'] = round(row1[0][1]/1000, 0)
if row1[0][1] is None: d['평면'] = 0
d['측면'] = round(row1[0][2]/1000, 0)
if row1[0][2] is None: d['측면'] = 0
cursor.execute(
"""
SELECT sum(qty) as '판매수량', sum(totamt) as '매출액'
from sales s
WHERE deal_date BETWEEN '{}-01-01' and '{}-12-31' and proname = '표준PLATE';
""".format(i, i)
)
row2 = cursor.fetchall()
d['판매수량'] = row2[0][0]
if row2[0][0] is None: d['판매수량'] = 0
d['매출액'] = row2[0][1]
if row2[0][1] is None: d['매출액'] = 0
ret_list.append(d)
elif date_standard == '월':
datetime_formate = "%Y-%m"
start_date = datetime.strptime(start_date, datetime_formate)
end_date = datetime.strptime(end_date, datetime_formate)
dates = month_list([start_date, end_date])
for date in dates:
d = dict()
year = date.year
month = str(date).split('-')[1]
d['기간'] = '{}년 {}월'.format(year, month)
cursor.execute(
"""
SELECT sum(case when p.proname = '절단품' then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYH%' or m.mach_name LIKE '%평면%') AND (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYV%' or m.mach_name LIKE '%측면%') and (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end)
from prod_m m join prod_p p on m.mcode = p.mcode and m.dcode = p.scode
WHERE deal_date BETWEEN '{}-{}-01' and '{}-{}-31';
""".format(year, month, year, month)
)
row1 = cursor.fetchall()
d['절단'] = round(row1[0][0]/1000, 0)
if row1[0][0] is None: d['절단'] = 0
d['평면'] = round(row1[0][1]/1000, 0)
if row1[0][1] is None: d['평면'] = 0
d['측면'] = round(row1[0][2]/1000, 0)
if row1[0][2] is None: d['측면'] = 0
cursor.execute(
"""
SELECT sum(qty) as '판매수량', sum(totamt) as '매출액'
from sales s
WHERE deal_date BETWEEN '{}-{}-01' and '{}-{}-31' and proname = '표준PLATE';
""".format(year, month, year, month)
)
row2 = cursor.fetchall()
d['판매수량'] = row2[0][0]
if row2[0][0] is None: d['판매수량'] = 0
d['매출액'] = row2[0][1]
if row2[0][1] is None: d['매출액'] = 0
# print(d)
ret_list.append(d)
elif date_standard == '주':
datetime_formate = "%Y-%m-%d"
start_date = datetime.strptime(start_date, datetime_formate)
end_date = datetime.strptime(end_date, datetime_formate)
startWeekEnd = GetWeekBeforeDate(start_date)
endWeekEnd = GetWeekBeforeDate(end_date)
date_list = date_range(startWeekEnd, endWeekEnd)
for date in date_list:
d = dict()
d['기간'] = '{}년 {}주차'.format(date.year, date.isocalendar()[1])
last_date = date + relativedelta.relativedelta(days=6)
date_month = str(date).split('-')[1]
date_day = str(date).split('-')[2]
lastDate_month = str(last_date).split('-')[1]
lastDate_day = str(last_date).split('-')[2]
cursor.execute(
"""
SELECT sum(case when p.proname = '절단품' then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYH%' or m.mach_name LIKE '%평면%') AND (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYV%' or m.mach_name LIKE '%측면%') and (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end)
from prod_m m join prod_p p on m.mcode = p.mcode and m.dcode = p.scode
WHERE deal_date BETWEEN '{}-{}-{}' and '{}-{}-{}';
""".format(date.year, date_month, date_day, last_date.year, lastDate_month, lastDate_day)
)
row1 = cursor.fetchall()
d['절단'] = round(row1[0][0]/1000, 0)
if row1[0][0] is None: d['절단'] = 0
d['평면'] = round(row1[0][1]/1000, 0)
if row1[0][1] is None: d['평면'] = 0
d['측면'] = round(row1[0][2]/1000, 0)
if row1[0][2] is None: d['측면'] = 0
cursor.execute(
"""
SELECT sum(qty) as '판매수량', sum(totamt) as '매출액'
from sales s
WHERE deal_date BETWEEN '{}-{}-{}' and '{}-{}-{}' and proname = '표준PLATE';
""".format(date.year, date_month, date_day, last_date.year, lastDate_month, lastDate_day)
)
row2 = cursor.fetchall()
d['판매수량'] = row2[0][0]
if row2[0][0] is None: d['판매수량'] = 0
d['매출액'] = row2[0][1]
if row2[0][1] is None: d['매출액'] = 0
# print(d)
ret_list.append(d)
return ret_list3. 변경 코드
def performance_weight(date_standard, start_date, end_date):
ret_list = []
with connections['testdb'].cursor() as cursor:
if date_standard == '연':
datetime_formate = "%Y"
start_date = datetime.strptime(start_date, datetime_formate)
end_date = datetime.strptime(end_date, datetime_formate)
start_year = int(start_date.year)
end_year = int(end_date.year)
for i in list(range(start_year, end_year+1)):
d = dict()
d['기간'] = '{}년'.format(i)
cursor.execute(
"""
SELECT sum(case when p.proname = '절단품' then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYH%' or m.mach_name LIKE '%평면%') AND (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYV%' or m.mach_name LIKE '%측면%') and (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end)
from prod_m m join prod_p p on m.mcode = p.mcode and m.dcode = p.scode
WHERE deal_date BETWEEN '{}-01-01' and '{}-12-31';
""".format(i, i)
)
row1 = cursor.fetchall()
try:d['절단'] = round(row1[0][0]/1000, 0)
except: d['절단'] = 0
try:d['평면'] = round(row1[0][1]/1000, 0)
except: d['평면'] = 0
try:d['측면'] = round(row1[0][2]/1000, 0)
except: d['측면'] = 0
cursor.execute(
"""
SELECT sum(dim1 * dim2 * dim3 * 7.85 * qty / 1000000) as '매출', sum(totamt) as '매출액'
from sales s
WHERE deal_date BETWEEN '{}-01-01' and '{}-12-31' and proname = '표준PLATE';
""".format(i, i)
)
row2 = cursor.fetchall()
try:d['매출'] = round(row2[0][0]/1000, 0)
except: d['매출'] = 0
try:d['매출액'] = row2[0][1]
except: d['매출액'] = 0
ret_list.append(d)
elif date_standard == '월':
datetime_formate = "%Y-%m"
start_date = datetime.strptime(start_date, datetime_formate)
end_date = datetime.strptime(end_date, datetime_formate)
dates = month_list([start_date, end_date])
for date in dates:
d = dict()
year = date.year
month = str(date).split('-')[1]
d['기간'] = '{}년 {}월'.format(year, month)
cursor.execute(
"""
SELECT sum(case when p.proname = '절단품' then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYH%' or m.mach_name LIKE '%평면%') AND (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYV%' or m.mach_name LIKE '%측면%') and (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end)
from prod_m m join prod_p p on m.mcode = p.mcode and m.dcode = p.scode
WHERE deal_date BETWEEN '{}-{}-01' and '{}-{}-31';
""".format(year, month, year, month)
)
row1 = cursor.fetchall()
try:d['절단'] = round(row1[0][0]/1000, 0)
except: d['절단'] = 0
try:d['평면'] = round(row1[0][1]/1000, 0)
except: d['평면'] = 0
try:d['측면'] = round(row1[0][2]/1000, 0)
except: d['측면'] = 0
cursor.execute(
"""
SELECT sum(dim1 * dim2 * dim3 * 7.85 * qty / 1000000) as '매출', sum(totamt) as '매출액'
from sales s
WHERE deal_date BETWEEN '{}-{}-01' and '{}-{}-31' and proname = '표준PLATE';
""".format(year, month, year, month)
)
row2 = cursor.fetchall()
try:d['매출'] = round(row2[0][0]/1000, 0)
except: d['매출'] = 0
try:d['매출액'] = row2[0][1]
except: d['매출액'] = 0
ret_list.append(d)
elif date_standard == '주':
datetime_formate = "%Y-%m-%d"
start_date = datetime.strptime(start_date, datetime_formate)
end_date = datetime.strptime(end_date, datetime_formate)
startWeekEnd = GetWeekBeforeDate(start_date)
endWeekEnd = GetWeekBeforeDate(end_date)
date_list = date_range(startWeekEnd, endWeekEnd)
for date in date_list:
d = dict()
d['기간'] = '{}년 {}주차'.format(date.year, date.isocalendar()[1])
last_date = date + relativedelta.relativedelta(days=6)
date_month = str(date).split('-')[1]
date_day = str(date).split('-')[2]
lastDate_month = str(last_date).split('-')[1]
lastDate_day = str(last_date).split('-')[2]
cursor.execute(
"""
SELECT sum(case when p.proname = '절단품' then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYH%' or m.mach_name LIKE '%평면%') AND (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end),
sum(case when ((m.mach_name LIKE '%DYV%' or m.mach_name LIKE '%측면%') and (p.proname = '표준PLATE' or p.proname = '평면품')) then p.dim1*p.dim2*p.dim3*7.85*p.qty/1000000 end)
from prod_m m join prod_p p on m.mcode = p.mcode and m.dcode = p.scode
WHERE deal_date BETWEEN '{}-{}-{}' and '{}-{}-{}';
""".format(date.year, date_month, date_day, last_date.year, lastDate_month, lastDate_day)
)
row1 = cursor.fetchall()
try:d['절단'] = round(row1[0][0]/1000, 0)
except: d['절단'] = 0
try:d['평면'] = round(row1[0][1]/1000, 0)
except: d['평면'] = 0
try:d['측면'] = round(row1[0][2]/1000, 0)
except: d['측면'] = 0
cursor.execute(
"""
SELECT sum(dim1 * dim2 * dim3 * 7.85 * qty / 1000000) as '매출', sum(totamt) as '매출액'
from sales s
WHERE deal_date BETWEEN '{}-{}-{}' and '{}-{}-{}' and proname = '표준PLATE';
""".format(date.year, date_month, date_day, last_date.year, lastDate_month, lastDate_day)
)
row2 = cursor.fetchall()
try:d['매출'] = round(row2[0][0]/1000, 0)
except: d['매출'] = 0
try:d['매출액'] = row2[0][1]
except: d['매출액'] = 0
ret_list.append(d)
return ret_list'Python > Django' 카테고리의 다른 글
| 권한 제어에 따른 "POST ... 403 45" 에러 해결 (0) | 2022.10.06 |
|---|---|
| 비로그인시 페이지 접근 권한 부여 (0) | 2022.10.05 |
| 데이터 수식 수정 (0) | 2022.10.04 |
| waitress deploy 후 에러 해결 (0) | 2022.09.16 |
| waitress API를 사용한 Django 배포 (0) | 2022.09.15 |