奶牛日糧配方設計

參考：https://blog.csdn.net/qq_20448859/article/details/72330362

需要看明白國家的奶牛飼養標準（2004版本），里面提供各種資料，就是價格難找，

"""
@author: yanglv
2020年11月1日08:59:16
日糧配方：
妊娠7個月，體重500kg,日平均產乳量20kg，乳脂率3.5%的奶牛的營養需要量
"""

from pulp import *
import xlrd


def get_Ingredients():
    # 得到原料串列
    feeddata = xlrd.open_workbook('粗飼料到R表.xlsx')
    Ingredients_sheet = feeddata.sheet_by_index(0)
    Ingredients = Ingredients_sheet.col_values(0)
    return Ingredients


def get_costs():
    # 價格
    feeddata = xlrd.open_workbook('粗飼料到R表.xlsx')
    Ingredients_sheet = feeddata.sheet_by_index(0)
    Cost1 = Ingredients_sheet.col_values(1)  # 待寫入的，瞎寫了一個進去！！！
    ingredients = get_Ingredients()
    Cost = {}
    for i in range(0, len(ingredients)):
        Cost[ingredients[i]] = Cost1[i]
    return Cost


def get_DM():
    # 得到干物質字典
    feeddata = xlrd.open_workbook('粗飼料到R表.xlsx')
    Ingredients_sheet = feeddata.sheet_by_index(0)
    DM1 = Ingredients_sheet.col_values(2)
    DM2 = []  # 干物質串列
    ingredients = get_Ingredients()
    for iterm in DM1:
        iterm1 = iterm / 100
        DM2.append(iterm1)
    DM = {}
    for i in range(0, len(DM2)):
        DM[ingredients[i]] = DM2[i]
    return DM


def get_Nel():
    # 得到殘奶凈能字典，MJ/kg
    feeddata = xlrd.open_workbook('粗飼料到R表.xlsx')
    Ingredients_sheet = feeddata.sheet_by_index(0)
    Ne1 = Ingredients_sheet.col_values(11)
    ingredients = get_Ingredients()
    Nel = {}
    for i in range(0, len(ingredients)):
        Nel[ingredients[i]] = Ne1[i]
    return Nel


def get_CP():
    # 可消化粗蛋白：g
    feeddata = xlrd.open_workbook('粗飼料到R表.xlsx')
    Ingredients_sheet = feeddata.sheet_by_index(0)
    CP1 = Ingredients_sheet.col_values(8)
    ingredients = get_Ingredients()
    CP = {}
    for i in range(0, len(ingredients)):
        CP[ingredients[i]] = CP1[i]
    return CP


def get_UIP():
    # 可小腸消化除蛋白質，單位g
    feeddata = xlrd.open_workbook('粗飼料到R表.xlsx')
    Ingredients_sheet = feeddata.sheet_by_index(0)
    UIP1 = Ingredients_sheet.col_values(15)
    ingredients = get_Ingredients()
    UIP = {}
    for i in range(0, len(ingredients)):
        UIP[ingredients[i]] = UIP1[i]
    return UIP


def get_ca():
    # 得到鈣含量，單位
    feeddata = xlrd.open_workbook('粗飼料到R表.xlsx')
    Ingredients_sheet = feeddata.sheet_by_index(0)
    Ca1 = Ingredients_sheet.col_values(4)
    Ca2 = []
    ingredients = get_Ingredients()
    for iterm in Ca1:
        iterm1 = iterm / 100.0
        Ca2.append(iterm1)
    Ca = {}
    for i in range(0, len(ingredients)):
        Ca[ingredients[i]] = Ca2[i]
    return Ca


def get_P():
    # 得到鈣含量
    feeddata = xlrd.open_workbook('粗飼料到R表.xlsx')
    Ingredients_sheet = feeddata.sheet_by_index(0)
    P1 = Ingredients_sheet.col_values(5)
    P2 = []
    ingredients = get_Ingredients()
    for iterm in P1:
        iterm1 = iterm / 100.0
        P2.append(iterm1)
    P = {}
    for i in range(0, len(ingredients)):
        P[ingredients[i]] = P2[i]
    return P

def get_Hu():
    # 得到胡蘿卜素字典,單位mg
    feeddata = xlrd.open_workbook('粗飼料到R表.xlsx')
    Ingredients_sheet = feeddata.sheet_by_index(0)
    Hu1 = Ingredients_sheet.col_values(22)
    ingredients = get_Ingredients()
    Hu = {}
    for i in range(0, len(ingredients)):
        Hu[ingredients[i]] = Hu1[i]
    return  Hu

def get_Va():
    # 得到VA素字典,單位mg/Kg
    feeddata = xlrd.open_workbook('粗飼料到R表.xlsx')
    Ingredients_sheet = feeddata.sheet_by_index(0)
    Va1 = Ingredients_sheet.col_values(23)
    ingredients = get_Ingredients()
    VA = {}
    for i in range(0, len(ingredients)):
        VA[ingredients[i]] = Va1[i]
    return VA

if __name__ == "__main__":
    ingredients = get_Ingredients()
    dm = get_DM()
    nel = get_Nel()
    cp = get_CP()
    # uip = get_UIP()  # 飼料中缺乏該資料
    ca = get_ca()
    p = get_P()
    costs = get_costs()
    hu = get_Hu()
    va = get_Va()  # 飼料原料中缺乏該資料

    prob = LpProblem("The feed Problem", LpMinimize)
    ingredient_vars = LpVariable.dicts("原料",ingredients, lowBound=0)  # 構建Lp變數字典，變數名以Ingr開頭，如Ingr_CHICKEN，下界是0


    prob += lpSum([costs[i] * ingredient_vars[i] for i in ingredients])  # 目標方程:求最低價格
    # 添加約束條件
    prob += lpSum([ingredient_vars[i] for i in ingredients]) == 43  # 設定飼料原料用量之和為40kg
    prob += lpSum([dm[i] * ingredient_vars[i] for i in ingredients]) >= 15.82  # 設定日糧干物質的約束條件為大于15.82
    prob += lpSum([dm[i] * ingredient_vars[i] for i in ingredients]) <= 25
    prob += lpSum([dm[i] * nel[i] * ingredient_vars[i] for i in ingredients]) >= 103.33  # 設定產奶凈能為103.33Mj，dm*Nel
    prob += lpSum([cp[i] * ingredient_vars[i] for i in ingredients]) >= 1461  # 設定cp為至少1461g
    # prob += lpSum([uip[i] * ingredient_vars[i] for i in ingredients]) >= 1257  # 小腸設定最小為1257g，缺乏！
    prob += lpSum([ca[i] * ingredient_vars[i] for i in ingredients]) >= 0.124  # 設定ca最小為124g
    prob += lpSum([ca[i] * ingredient_vars[i] for i in ingredients]) <= 0.15
    prob += lpSum([p[i] * ingredient_vars[i] for i in ingredients]) >= 0.083  # 設定p最小83g
    prob += lpSum([dm[i] * hu[i] * ingredient_vars[i] for i in ingredients]) >= 119.4  # 胡蘿卜素,單位mg
    # prob += lpSum([dm[i] * va[i] * ingredient_vars[i] for i in ingredients]) >= 15.75  # 維生素A，單位mg 缺乏！
    prob.solve()  # 求解
    print("Status:", LpStatus[prob.status]) # 查看解的狀態
    for v in prob.variables(): # 查看解
        print(v.name, "=", v.varValue)