데이터 수식 수정

1. 수정 사항

 - 기존에 파악한 데이터 수식의 변경으로 데이터 연동 부분 쿼리문 or 이후 수식 변경

2. 이전 코드

 - serializers_produce.py

def equipment(date_standard, start_date, end_date, standard):
    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)
        period_standar = "'{}-01-01' and '{}-12-31'".format(start_year, end_year)
    elif date_standard == '월':
        datetime_formate = "%Y-%m"
        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)
        start_month = str(start_date).split('-')[1]
        end_month = str(end_date).split('-')[1]
        period_standar = "'{}-{}-01' and '{}-{}-31'".format(start_year, start_month, end_year, end_month)
    elif date_standard == '주':
        datetime_formate = "%Y-%m-%d"
        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)
        start_month = str(start_date).split('-')[1]
        end_month = str(end_date).split('-')[1]
        start_day = str(start_date).split('-')[2]
        end_day = str(end_date).split('-')[2]
        period_standar = "'{}-{}-{}' and '{}-{}-{}'".format(start_year, start_month, end_year, end_month)
    ret_list = []
    with connections['testdb'].cursor() as cursor:
        cursor.execute(
            """
            select sum(case when p.proname='절단품' and m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '360 제품중량',
            sum(case when p.proname='절단품' and m.mach_name in {0} then p.qty end) as '360 수량',
            sum(case when p.proname='절단품' and m.mach_name in {1} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '315 제품중량',
            sum(case when p.proname='절단품' and m.mach_name in {1} then p.qty end) as '315 수량',
            sum(case when m.mach_name in {2} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '평면 벨트 제품중량',
            sum(case when m.mach_name in {2} then p.qty end) as '평면 벨트 수량',
            sum(case when m.mach_name in {3} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '평면 NC 제품중량',
            sum(case when m.mach_name in {3} then p.qty end) as '평면 NC 수량',
            sum(case when m.mach_name in {4} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '측면 로봇 제품중량',
            sum(case when m.mach_name in {4} then p.qty end) as '측면 로봇 수량',
            sum(case when m.mach_name in {5} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '측면 수동 제품중량',
            sum(case when m.mach_name in {5} then p.qty end) as '측면 수동 수량',
            count(DISTINCT m.deal_date),
            max(m.deal_date)
            from prod_m m join prod_p p on m.mcode = p.mcode and m.dcode = p.scode
            where m.deal_date BETWEEN {6};
            """.format(p_360, p_315, b_mach, n_mach, r_mach, m_mach, period_standar)
        )
        spec_row = cursor.fetchall()
        cursor.execute(
            """
            select sum(case when proname='표준PLATE' then amt end) as '표준P-합계-금액',
            sum(case when proname='표준PLATE' then dim1 * dim2 * dim3 * 7.85 * qty / 1000000 end) as '표준P-합계-제품중량',
            count(DISTINCT deal_date)
            from sales
            where deal_date BETWEEN {};
            """.format(period_standar)
        )
        shipment_row = cursor.fetchall()
    conditions = [["cm.case = '절단'", "절단capa"], 
    ["sum(case when m.mach_name in {0} then p.qty end), sum(case when m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(c_mach), "절단실적"], 
    ["cm.case = '평면'", "평면capa"], 
    ["sum(case when (m.mach_name in {0}) or (m.mach_name in {1}) then p.qty end), sum(case when (m.mach_name in {0}) or (m.mach_name in {1}) then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(op_mach, pp_mach), "평면실적"],
    ["cm.location = '오산' and cm.case = '평면'", "O평면capa"],
    ["sum(case when m.mach_name in {0} then p.qty end), sum(case when m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(op_mach), "O평면실적"],
    ["cm.location = '포항' and cm.case = '평면'", "P평면capa"],
    ["sum(case when m.mach_name in {0} then p.qty end), sum(case when m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(pp_mach), "P평면실적"],
    ["cm.case = '측면'", "측면capa"],
    ["sum(case when (m.mach_name in {0}) or (m.mach_name in {1}) then p.qty end), sum(case when (m.mach_name in {0}) or (m.mach_name in {1}) then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(os_mach, ps_mach), "측면실적"],
    ["cm.case = '측면' and cm.location = '오산'", "O측면capa"],
    ["sum(case when m.mach_name in {0} then p.qty end), sum(case when m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(os_mach), "O측면실적"],
    ["cm.case = '측면' and cm.location = '포항'", "P측면capa"],
    ["sum(case when m.mach_name in {0} then p.qty end), sum(case when m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(ps_mach), "P측면실적"],
    ["", "매출"]]
    for condition in conditions:
        d = dict()
        if "capa" in condition[1]:
            with connections['default'].cursor() as cursor:
                wh = condition[0]
                total = 0
                cursor.execute(
                    """
                    select name, day_time, night_time, cycle_time, oper_rate, avg_load, model
                    from common_machine cm
                    where {};
                    """.format(wh)
                )
                mach_row = cursor.fetchall()
            for i in mach_row:
                try:
                    part = (i[1] + i[2]) * 60 / i[3] * i[4] * i[5]
                except:
                    part = 0
                if standard == '수량':
                    if i[6] == 'OR' or i[6] == 'PR':
                elif standard == '중량':
                    if i[6] == 'P360':
                        part *= (float(spec_row[0][0])/float(spec_row[0][1]))
                    elif i[6] == 'P315':
                        part *= (float(spec_row[0][2])/float(spec_row[0][3]))
                    elif i[6] == 'OB' or i[6] == 'PB':
                        part *= (float(spec_row[0][4])/float(spec_row[0][5]))
                    elif i[6] == 'ON' or i[6] == 'PN':
                        part *= (float(spec_row[0][6])/float(spec_row[0][7]))
                    elif i[6] == 'OR' or i[6] == 'PR':
                        part *= (float(spec_row[0][8])/float(spec_row[0][9]))
                    elif i[6] == 'OM' or i[6] == 'PM':
                        part *= (float(spec_row[0][10])/float(spec_row[0][11]))
                elif standard == '금액':
                    if i[6] == 'P360':  
                        part *= ((float(spec_row[0][0])/float(spec_row[0][1])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                    elif i[6] == 'P315':
                        part *= ((float(spec_row[0][2])/float(spec_row[0][3])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                    elif i[6] == 'OB' or i[6] == 'PB':
                        part *= ((float(spec_row[0][4])/float(spec_row[0][5])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                    elif i[6] == 'ON' or i[6] == 'PN':
                        part *= ((float(spec_row[0][6])/float(spec_row[0][7])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                    elif i[6] == 'OR' or i[6] == 'PR':
                        part *= ((float(spec_row[0][8])/float(spec_row[0][9])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                    elif i[6] == 'OM' or i[6] == 'PM':
                        part *= ((float(spec_row[0][10])/float(spec_row[0][11])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                total += part
            if date_standard == '연':
                total *= 264
            elif date_standard == '월':
                total *= 22
            elif date_standard == '주':
                total *= 5
            d['구분'] = condition[1]
            d['수치'] = round(total, 0)
            ret_list.append(d)
        elif condition[1] == "매출":
            total = 0
            if standard == '수량':
                wh = "sum(qty)"
            elif standard == '중량':
                wh = "sum(dim1 * dim2 * dim3 * 7.85 * qty / 1000000)"
            elif standard == '금액':
                wh = "sum(amt)"
            with connections['testdb'].cursor() as cursor:
                cursor.execute(
                    """
                    select {}
                    from sales
                    where deal_date BETWEEN {} and proname = '표준PLATE';
                    """.format(wh, period_standar)
                )
                sales_row = cursor.fetchall()
            d['구분'] = condition[1]
            total += sales_row[0][0]
            total = total / int(str(spec_row[0][13]).split('-')[1]) * 12
            if date_standard == '연':
                pass
            elif date_standard == '월':
                total /= 12
            elif date_standard == '주':
                total = total / 264 * 5
            d['수치'] = round(total, 0)
            ret_list.append(d)
        else:
            wh = condition[0]
            total = 0
            with connections['testdb'].cursor() as cursor:
                cursor.execute(
                    """
                    select {}
                    from prod_m m join prod_p p on m.mcode = p.mcode and m.dcode = p.scode
                    where m.deal_date BETWEEN {};
                    """.format(wh, period_standar)
                )
                perform_row = cursor.fetchall()
            d['구분'] = condition[1]
            if standard == '수량':
                total += int(perform_row[0][0])
            elif standard == '중량':
                total += int(perform_row[0][1])
            elif standard == '금액':
                total += (int(perform_row[0][1]) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
            total = total / int(str(spec_row[0][13]).split('-')[1]) * 12
            if date_standard == '연':
                pass
            elif date_standard == '월':
                total /= 12
            elif date_standard == '주':
                total = total / 264 * 5
            d['수치'] = round(total, 0)
            ret_list.append(d)
    return ret_list

3. 변경 코드

 - serializers_produce.py

def equipment(date_standard, start_date, end_date, standard):
    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)
        period_standar = "'{}-01-01' and '{}-12-31'".format(start_year, end_year)
    elif date_standard == '월':
        datetime_formate = "%Y-%m"
        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)
        start_month = str(start_date).split('-')[1]
        end_month = str(end_date).split('-')[1]
        period_standar = "'{}-{}-01' and '{}-{}-31'".format(start_year, start_month, end_year, end_month)
    elif date_standard == '주':
        datetime_formate = "%Y-%m-%d"
        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)
        start_month = str(start_date).split('-')[1]
        end_month = str(end_date).split('-')[1]
        start_day = str(start_date).split('-')[2]
        end_day = str(end_date).split('-')[2]
        period_standar = "'{}-{}-{}' and '{}-{}-{}'".format(start_year, start_month, end_year, end_month)
    ret_list = []
    with connections['testdb'].cursor() as cursor:
        cursor.execute(
            """
            select sum(case when p.proname='절단품' and m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '360 제품중량',
            sum(case when p.proname='절단품' and m.mach_name in {0} then p.qty end) as '360 수량',
            sum(case when p.proname='절단품' and m.mach_name in {1} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '315 제품중량',
            sum(case when p.proname='절단품' and m.mach_name in {1} then p.qty end) as '315 수량',
            sum(case when m.mach_name in {2} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '평면 벨트 제품중량',
            sum(case when m.mach_name in {2} then p.qty end) as '평면 벨트 수량',
            sum(case when m.mach_name in {3} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '평면 NC 제품중량',
            sum(case when m.mach_name in {3} then p.qty end) as '평면 NC 수량',
            sum(case when m.mach_name in {4} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '측면 로봇 제품중량',
            sum(case when m.mach_name in {4} then p.qty end) as '측면 로봇 수량',
            sum(case when m.mach_name in {5} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end) as '측면 수동 제품중량',
            sum(case when m.mach_name in {5} then p.qty end) as '측면 수동 수량',
            count(DISTINCT m.deal_date),
            max(m.deal_date)
            from prod_m m join prod_p p on m.mcode = p.mcode and m.dcode = p.scode
            where m.deal_date BETWEEN {6};
            """.format(p_360, p_315, b_mach, n_mach, r_mach, m_mach, period_standar)
        )
        spec_row = cursor.fetchall()
        cursor.execute(
            """
            select sum(case when proname='표준PLATE' then amt end) as '표준P-합계-금액',
            sum(case when proname='표준PLATE' then dim1 * dim2 * dim3 * 7.85 * qty / 1000000 end) as '표준P-합계-제품중량',
            count(DISTINCT deal_date)
            from sales
            where deal_date BETWEEN {};
            """.format(period_standar)
        )
        shipment_row = cursor.fetchall()
    conditions = [["cm.case = '절단'", "절단capa"], 
    ["sum(case when m.mach_name in {0} then p.qty end), sum(case when m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(c_mach), "절단실적"], 
    ["cm.case = '평면'", "평면capa"], 
    ["sum(case when (m.mach_name in {0}) or (m.mach_name in {1}) then p.qty end), sum(case when (m.mach_name in {0}) or (m.mach_name in {1}) then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(op_mach, pp_mach), "평면실적"],
    ["cm.location = '오산' and cm.case = '평면'", "O평면capa"],
    ["sum(case when m.mach_name in {0} then p.qty end), sum(case when m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(op_mach), "O평면실적"],
    ["cm.location = '포항' and cm.case = '평면'", "P평면capa"],
    ["sum(case when m.mach_name in {0} then p.qty end), sum(case when m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(pp_mach), "P평면실적"],
    ["cm.case = '측면'", "측면capa"],
    ["sum(case when (m.mach_name in {0}) or (m.mach_name in {1}) then p.qty end), sum(case when (m.mach_name in {0}) or (m.mach_name in {1}) then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(os_mach, ps_mach), "측면실적"],
    ["cm.case = '측면' and cm.location = '오산'", "O측면capa"],
    ["sum(case when m.mach_name in {0} then p.qty end), sum(case when m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(os_mach), "O측면실적"],
    ["cm.case = '측면' and cm.location = '포항'", "P측면capa"],
    ["sum(case when m.mach_name in {0} then p.qty end), sum(case when m.mach_name in {0} then p.dim1 * p.dim2 * p.dim3 * 7.85 * p.qty / 1000000 end)".format(ps_mach), "P측면실적"],
    ["", "매출"]]
    for condition in conditions:
        d = dict()
        if "capa" in condition[1]:
            with connections['default'].cursor() as cursor:
                wh = condition[0]
                total = 0
                cursor.execute(
                    """
                    select name, day_time, night_time, cycle_time, oper_rate, avg_load, model
                    from common_machine cm
                    where {};
                    """.format(wh)
                )
                mach_row = cursor.fetchall()
            for i in mach_row:
                try:
                    part = (i[1] + i[2]) * 60 / i[3] * i[4] * i[5]
                except:
                    part = 0
                if standard == '수량':
                    if i[6] == 'OR' or i[6] == 'PR':
                        part *= 4
                elif standard == '중량':
                    if i[6] == 'P360':
                        part *= (float(spec_row[0][0])/float(spec_row[0][1]))
                    elif i[6] == 'P315':
                        part *= (float(spec_row[0][2])/float(spec_row[0][3]))
                    elif i[6] == 'OB' or i[6] == 'PB':
                        part *= (float(spec_row[0][4])/float(spec_row[0][5]))
                    elif i[6] == 'ON' or i[6] == 'PN':
                        part *= (float(spec_row[0][6])/float(spec_row[0][7]))
                    elif i[6] == 'OR' or i[6] == 'PR':
                        part *= 4
                        part *= (float(spec_row[0][8])/float(spec_row[0][9]))
                    elif i[6] == 'OM' or i[6] == 'PM':
                        part *= (float(spec_row[0][10])/float(spec_row[0][11]))
                elif standard == '금액':
                    if i[6] == 'P360':  
                        part *= ((float(spec_row[0][0])/float(spec_row[0][1])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                    elif i[6] == 'P315':
                        part *= ((float(spec_row[0][2])/float(spec_row[0][3])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                    elif i[6] == 'OB' or i[6] == 'PB':
                        part *= ((float(spec_row[0][4])/float(spec_row[0][5])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                    elif i[6] == 'ON' or i[6] == 'PN':
                        part *= ((float(spec_row[0][6])/float(spec_row[0][7])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                    elif i[6] == 'OR' or i[6] == 'PR':
                        part *= 4
                        part *= ((float(spec_row[0][8])/float(spec_row[0][9])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                    elif i[6] == 'OM' or i[6] == 'PM':
                        part *= ((float(spec_row[0][10])/float(spec_row[0][11])) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
                total += part
            if date_standard == '연':
                total *= 264
            elif date_standard == '월':
                total *= 22
            elif date_standard == '주':
                total *= 5
            d['구분'] = condition[1]
            d['수치'] = round(total, 0)
            ret_list.append(d)
        elif condition[1] == "매출":
            total = 0
            if standard == '수량':
                wh = "sum(qty)"
            elif standard == '중량':
                wh = "sum(dim1 * dim2 * dim3 * 7.85 * qty / 1000000)"
            elif standard == '금액':
                wh = "sum(amt)"
            with connections['testdb'].cursor() as cursor:
                cursor.execute(
                    """
                    select {}
                    from sales
                    where deal_date BETWEEN {} and proname = '표준PLATE';
                    """.format(wh, period_standar)
                )
                sales_row = cursor.fetchall()
            d['구분'] = condition[1]
            total += sales_row[0][0]
            total = total / int(str(spec_row[0][13]).split('-')[1]) * 12
            if date_standard == '연':
                pass
            elif date_standard == '월':
                total /= 12
            elif date_standard == '주':
                total = total / 264 * 5
            d['수치'] = round(total, 0)
            ret_list.append(d)
        else:
            wh = condition[0]
            total = 0
            with connections['testdb'].cursor() as cursor:
                cursor.execute(
                    """
                    select {}
                    from prod_m m join prod_p p on m.mcode = p.mcode and m.dcode = p.scode
                    where m.deal_date BETWEEN {};
                    """.format(wh, period_standar)
                )
                perform_row = cursor.fetchall()
            d['구분'] = condition[1]
            if standard == '수량':
                total += int(perform_row[0][0])
            elif standard == '중량':
                total += int(perform_row[0][1])
            elif standard == '금액':
                total += (int(perform_row[0][1]) * (float(shipment_row[0][0])/float(shipment_row[0][1])))
            total = total / int(str(spec_row[0][13]).split('-')[1]) * 12
            if date_standard == '연':
                pass
            elif date_standard == '월':
                total /= 12
            elif date_standard == '주':
                total = total / 264 * 5
            d['수치'] = round(total, 0)
            ret_list.append(d)
    return ret_list