def draw_form_factors(wb, form_factors):
excel_client = ExcelBlock(wb["Форм фактор плавления"])
cur_i = 1
for value in sorted(form_factors, key=lambda x: x.weight_with_line):
excel_client.draw_row(cur_i, [value.weight_with_line],
set_border=False)
cur_i += 1
def draw_boiling_names(wb):
excel_client = ExcelBlock(wb["Группы"])
boiling_names = ["Кавказский", "Черкесский", "Качорикотта"]
excel_client.draw_row(1, ["-"])
cur_i = 2
for boiling_name in boiling_names:
excel_client.draw_row(cur_i, [boiling_name], set_border=False)
cur_i += 1
def draw_boiling_names(wb):
excel_client = ExcelBlock(wb["Типы варок"])
boiling_names = list(
set([x.to_str() for x in db.session.query(RicottaBoiling).all()]))
excel_client.draw_row(1, ["-"])
cur_i = 2
for boiling_name in boiling_names:
excel_client.draw_row(cur_i, [boiling_name], set_border=False)
cur_i += 1
def draw_extra_packing(wb, df, skus):
excel_client = ExcelBlock(wb["Дополнительная фасовка"])
cur_i = 2
for value in df.values:
if value[0] in [
sku.name for sku in skus if not sku.packing_by_request
]:
excel_client.draw_row(cur_i, value, set_border=False)
cur_i += 1
def draw_boiling_sheet(wb, df, skus, sheet_name, cur_row=None, normalize=True):
print(df)
if not cur_row:
cur_row = 3
if not df.empty:
excel_client = ExcelBlock(wb[sheet_name])
values = []
sku_names = [x.name for x in skus]
df_filter = df[df["name"].isin(sku_names)].copy()
for id, grp in df_filter.groupby("id", sort=False):
for i, row in grp.iterrows():
columns = [x for x in row.index if x in COLUMNS.keys()]
v = [row[column] for column in columns]
c = [COLUMNS[column] for column in columns]
values.append(dict(zip(c, v)))
empty_columns = [
COLUMNS["name"],
COLUMNS["output"],
COLUMNS["delimiter"],
]
values.append(dict(zip(empty_columns, ["-"] * len(empty_columns))))
for v in values:
value = v.values()
column = [x.col for x in v.keys()]
formula = '=IF({1}{0}="-", "", 1 + SUM(INDIRECT(ADDRESS(2,COLUMN({2}{0})) & ":" & ADDRESS(ROW(),COLUMN({2}{0})))))'.format(
cur_row,
COLUMNS["delimiter"].col_name,
COLUMNS["delimiter_int"].col_name,
)
colour = get_colour_by_name(v[COLUMNS["name"]], skus)
excel_client.colour = colour[1:]
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["boiling_number"].col,
value=formula,
set_border=False,
)
excel_client.draw_row(row=cur_row,
values=value,
cols=column,
set_border=False)
excel_client.color_cell(row=cur_row,
col=COLUMNS["boiling_type"].col)
cur_row += 1
return wb, cur_row
def draw_skus(wb, skus):
skus.sort(key=lambda x: x.name, reverse=False)
excel_client = ExcelBlock(wb["Вода SKU"])
excel_client.draw_row(1, ["-", "-"], set_border=False)
cur_i = 2
for group_sku in skus:
excel_client.draw_row(
cur_i,
[group_sku.name, group_sku.made_from_boilings[0].to_str()],
set_border=False,
)
cur_i += 1
def draw_skus_sheet(wb, type_sku, data_sku):
grouped_skus = data_sku[type_sku]
grouped_skus.sort(key=lambda x: x.name, reverse=False)
excel_client = ExcelBlock(wb["{} SKU".format(type_sku)])
excel_client.draw_row(1, ["-", "-"], set_border=False)
cur_i = 2
for group_sku in grouped_skus:
excel_client.draw_row(
cur_i,
[group_sku.name, group_sku.made_from_boilings[0].to_str()],
set_border=False,
)
cur_i += 1
def draw_additional_packing(wb, packing_df):
sheet_name = "Дополнительная фасовка"
if sheet_name not in wb.sheetnames:
wb.create_sheet(sheet_name)
excel_client = ExcelBlock(wb[sheet_name])
cur_row = 2
packing_df["kg_min"] = -packing_df["kg"]
for _, row in packing_df[['sku', 'kg_min']].iterrows():
excel_client.draw_row(cur_row, row.values)
cur_row += 1
return wb
def draw_skus(wb, data_sku):
grouped_skus = data_sku
grouped_skus.sort(key=lambda x: x.name, reverse=False)
excel_client = ExcelBlock(wb["SKU"])
excel_client.draw_row(1, ["-", "-", "-", "-"], set_border=False)
cur_i = 2
for group_sku in grouped_skus:
excel_client.draw_row(
cur_i,
[
group_sku.name,
group_sku.made_from_boilings[0].to_str(),
group_sku.made_from_boilings[0].output_kg,
],
set_border=False,
)
cur_i += 1
def draw_fermentators(wb):
sheet_name = "Заквасочники"
excel_client = ExcelBlock(wb[sheet_name])
cur_i = 2
for fermentators in [
["1-2"],
["3-4"],
["1"],
["2"],
["3"],
["4"],
["5"],
["6"],
["7"],
]:
excel_client.draw_row(
cur_i,
fermentators,
set_border=False,
)
cur_i += 1
def draw_skus(wb, data_sku, sheet_name, cur_i=None):
grouped_skus = data_sku
grouped_skus.sort(key=lambda x: x.name, reverse=False)
excel_client = ExcelBlock(wb[sheet_name])
excel_client.draw_row(1, ["-", "-", "-"], set_border=False)
if not cur_i:
cur_i = 2
for group_sku in grouped_skus:
excel_client.draw_row(
cur_i,
[
group_sku.name,
group_sku.made_from_boilings[0].to_str(),
group_sku.made_from_boilings[0].output_coeff,
],
set_border=False,
)
cur_i += 1
return cur_i
def draw_skus_fermentator(wb):
sheet_name = "SKU заквасочник"
excel_client = ExcelBlock(wb[sheet_name])
cur_i = 1
cur_i += 1
excel_client.draw_row(
cur_i,
["-"],
set_border=False,
)
cur_i += 1
skus_mascarpone = db.session.query(MascarponeSKU).all()
for sku in skus_mascarpone:
if sku.group.name == "Маскарпоне":
sku_fermentator = [sku.name, 480, 450, 255] + [225] * 3 + [""] * 4
else:
sku_fermentator = [sku.name] + [""] * 9 + [250]
excel_client.draw_row(
cur_i,
sku_fermentator,
set_border=False,
)
cur_i += 1
skus_cream_cheese = db.session.query(CreamCheeseSKU).all()
for sku in skus_cream_cheese:
sku_fermentator = [sku.name] + [""] * 2 + [450] * 8
excel_client.draw_row(
cur_i,
sku_fermentator,
set_border=False,
)
cur_i += 1
def draw_boiling_plan(df, df_extra, wb, total_volume=0):
skus = db.session.query(RicottaSKU).all()
draw_boiling_names(wb=wb)
sheet_name = "План варок"
draw_skus(wb, skus)
values = []
excel_client = ExcelBlock(wb[sheet_name])
sku_names = [x.name for x in skus]
df_filter = df[df["name"].isin(sku_names)].copy()
for id, grp in df_filter.groupby("id", sort=False):
for i, row in grp.iterrows():
columns = [x for x in row.index if x in COLUMNS.keys()]
v = [row[column] for column in columns]
c = [COLUMNS[column] for column in columns]
values.append(dict(zip(c, v)))
empty_columns = [
COLUMNS["name"],
COLUMNS["output"],
COLUMNS["number_of_tanks"],
COLUMNS["boiling_count"],
COLUMNS["delimiter"],
]
values.append(dict(zip(empty_columns, ["-"] * len(empty_columns))))
cur_row = 3
boiling_count = 1
number_of_tanks = 0
for v in values:
current_boiling_count = v[COLUMNS["boiling_count"]]
current_number_of_tanks = v[COLUMNS["number_of_tanks"]]
print(current_boiling_count, current_number_of_tanks)
del v[COLUMNS["boiling_count"]]
del v[COLUMNS["output"]]
del v[COLUMNS["number_of_tanks"]]
value = v.values()
column = [x.col for x in v.keys()]
formula = '=IF({1}{0}="-", "", 1 + SUM(INDIRECT(ADDRESS(2,COLUMN({2}{0})) & ":" & ADDRESS(ROW(),COLUMN({2}{0})))))'.format(
cur_row,
COLUMNS["delimiter"].col_name,
COLUMNS["delimiter_int"].col_name,
)
colour = get_colour_by_name(v[COLUMNS["name"]], skus)
excel_client.colour = colour[1:]
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["boiling_number"].col,
value=formula,
set_border=False,
)
excel_client.draw_row(row=cur_row,
values=value,
cols=column,
set_border=False)
if v[COLUMNS["name"]] == "-":
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["boiling_count"].col,
value=int(number_of_tanks),
set_border=False,
)
else:
excel_client.color_cell(col=COLUMNS["boiling_type"].col,
row=cur_row)
excel_client.color_cell(col=COLUMNS["output"].col, row=cur_row)
excel_client.color_cell(col=COLUMNS["number_of_tanks"].col,
row=cur_row)
number_of_tanks = current_number_of_tanks
boiling_count = current_boiling_count
cur_row += 1
excel_client.font_size = 10
excel_client.draw_cell(
row=ROWS["total_volume"],
col=COLUMNS["total_volume"].col,
value=total_volume,
set_border=False,
)
for sheet in wb.sheetnames:
wb[sheet].views.sheetView[0].tabSelected = False
wb.active = 2
return wb
def draw_boiling_plan_merged(df, wb):
skus = db.session.query(MozzarellaSKU).all()
sheet_name = 'План варок'
values = []
excel_client = ExcelBlock(wb[sheet_name])
draw_boiling_names(wb=wb)
draw_skus(wb, skus)
for id, grp in df.groupby("id", sort=False):
for i, row in grp.iterrows():
columns = [x for x in row.index if x in COLUMNS.keys()]
v = [row[column] for column in columns]
c = [COLUMNS[column] for column in columns]
values.append(dict(zip(c, v)))
empty_columns = [
COLUMNS["boiling_name"], COLUMNS["boiling_volume"],
COLUMNS["group"], COLUMNS["form_factor"],
COLUMNS["boiling_form_factor"], COLUMNS["packer"], COLUMNS["name"],
COLUMNS["delimiter"], COLUMNS["boiling_configuration"]
]
values.append(dict(zip(empty_columns, ["-"] * len(empty_columns))))
cur_row = 2
configuration = 0
for v in values:
cur_configuration = v[COLUMNS["boiling_configuration"]]
del v[COLUMNS["boiling_configuration"]]
value = v.values()
column = [x.col for x in v.keys()]
formula = '=IF({1}{0}="-", "", 1 + SUM(INDIRECT(ADDRESS(2,COLUMN({2}{0})) & ":" & ADDRESS(ROW(),COLUMN({2}{0})))))'.format(
cur_row,
COLUMNS["delimiter"].col_name,
COLUMNS["delimiter_int"].col_name,
)
colour = get_colour_by_name(v[COLUMNS["name"]], skus)
excel_client.colour = colour[1:]
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["boiling_number"].col,
value=formula,
set_border=False,
)
excel_client.draw_row(row=cur_row,
values=value,
cols=column,
set_border=False)
if v[COLUMNS["name"]] == "-":
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["boiling_configuration"].col,
value=configuration,
set_border=False,
)
else:
configuration = int(8000 * cur_configuration /
v[COLUMNS["boiling_volume"]])
cur_row += 1
return wb
def draw_boiling_plan(df, df_extra, wb):
skus = db.session.query(MozzarellaSKU).all()
form_factors = db.session.query(FormFactor).all()
data_sku = {
"Вода":
[x for x in skus if x.made_from_boilings[0].boiling_type == "water"],
"Соль":
[x for x in skus if x.made_from_boilings[0].boiling_type == "salt"],
}
draw_boiling_names(wb=wb)
draw_extra_packing(wb=wb, df=df_extra, skus=skus)
draw_form_factors(wb=wb, form_factors=form_factors)
for sheet_name in ["Соль", "Вода"]:
draw_skus_sheet(wb, sheet_name, data_sku)
values = []
excel_client = ExcelBlock(wb[sheet_name])
sku_names = [x.name for x in data_sku[sheet_name]]
df_filter = df[df["name"].isin(sku_names)].copy()
for id, grp in df_filter.groupby("id", sort=False):
for i, row in grp.iterrows():
columns = [x for x in row.index if x in COLUMNS.keys()]
v = [row[column] for column in columns]
c = [COLUMNS[column] for column in columns]
values.append(dict(zip(c, v)))
empty_columns = [
COLUMNS["boiling_name"],
COLUMNS["boiling_volume"],
COLUMNS["group"],
COLUMNS["form_factor"],
COLUMNS["boiling_form_factor"],
COLUMNS["packer"],
COLUMNS["name"],
COLUMNS["delimiter"],
]
values.append(dict(zip(empty_columns, ["-"] * len(empty_columns))))
cur_row = 2
for v in values:
value = v.values()
column = [x.col for x in v.keys()]
if sheet_name == "Вода":
formula = '=IF({1}{0}="-", "", 1 + SUM(INDIRECT(ADDRESS(2,COLUMN({2}{0})) & ":" & ADDRESS(ROW(),COLUMN({2}{0})))))'.format(
cur_row,
COLUMNS["delimiter"].col_name,
COLUMNS["delimiter_int"].col_name,
)
else:
formula = '=IF({1}{0}="-", "-", 1 + MAX(\'Вода\'!$A$2:$A$100) + SUM(INDIRECT(ADDRESS(2,COLUMN({2}{0})) & ":" & ADDRESS(ROW(),COLUMN({2}{0})))))'.format(
cur_row,
COLUMNS["delimiter"].col_name,
COLUMNS["delimiter_int"].col_name,
)
colour = get_colour_by_name(v[COLUMNS["name"]], skus)
excel_client.colour = colour[1:]
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["boiling_number"].col,
value=formula,
set_border=False,
)
excel_client.draw_row(row=cur_row,
values=value,
cols=column,
set_border=False)
if sku_is_rubber(skus, v[COLUMNS["name"]]):
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["team_number"].col,
value=2,
set_border=False,
set_colour=False,
)
elif v[COLUMNS["name"]] == "-":
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["team_number"].col,
value="",
set_border=False,
)
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["boiling_configuration"].col,
value=8000,
set_border=False,
)
else:
excel_client.draw_cell(
row=cur_row,
col=COLUMNS["team_number"].col,
value=1,
set_border=False,
set_colour=False,
)
cur_row += 1
for sheet in wb.sheetnames:
wb[sheet].views.sheetView[0].tabSelected = False
wb.active = 2
return wb