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_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, 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(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