In [1]:
%matplotlib inline
import numpy as np
import matplotlib.pyplot as plt
In [2]:
from openpyxl import load_workbook
from openpyxl import Workbook
In [3]:
from openpyxl.styles import Color, PatternFill, Font, Border, Side
In [15]:
def buscaproob(text):
prov = 'P'
text = text.upper()
for char_num in range(len(text)-1):
char = text[char_num]
if char == 'P' :
ii = 1
while char_num+ii<len(text) and text[char_num+ii].isdigit():
prov += text[char_num+ii]
ii += 1
if prov != 'P':
return prov
return ''
def str_to_bool(text):
text = text.lower()
if text == 'sí' or text == 'si' or text =='s':
return True
elif text == 'no' or text == 'n':
return False
else:
error_msg = 'Encontrado error en booleano:' + str(text)
raise ValueError(error_msg)
In [5]:
wb_1 = load_workbook(filename='Informe de precios-1.xlsx', read_only=True)
wb_2 = load_workbook(filename='Informe de precios-2.xlsx', read_only=True)
wb_3 = load_workbook(filename='Informe de precios-3.xlsx', read_only=True)
wb_4 = load_workbook(filename='Informe de precios-4.xlsx', read_only=True)
ws_1 = wb_1[wb_1.sheetnames[0]]
ws_2 = wb_2[wb_2.sheetnames[0]]
ws_3 = wb_3[wb_3.sheetnames[0]]
ws_4 = wb_4[wb_4.sheetnames[0]]
In [6]:
ws_list = [ws_1, ws_2, ws_3,ws_4]
In [7]:
raw_data_matrix = []
for doc in range(4):
raw_data = []
ws = ws_list[doc]
for row in ws:
row_current = []
for cell in row:
row_current.append(cell.value)
raw_data_matrix.append(row_current)
raw_data_array = np.array(raw_data_matrix)
In [8]:
raw_data_array
Out[8]:
In [9]:
for row in range(raw_data_array.shape[0]):
raw_data_array[row,1] = str(raw_data_array[row,1])
In [10]:
p1_matrix = []
p2_matrix = []
p3_matrix = []
p4_matrix = []
last_matrix = []
prov_matrix = []
other_matrix =[]
for row_num in range(raw_data_array.shape[0]):
row = raw_data_array[row_num,:]
if row[0] == 'Lista PVP1':
p1_matrix.append(row)
elif row[0] == 'Lista PVP2':
p2_matrix.append(row)
elif row[0] == 'Lista PVP3':
p3_matrix.append(row)
elif row[0] == 'Lista PVP4':
p4_matrix.append(row)
elif row[0] == 'Último precio de compra':
last_matrix.append(row)
elif row[0][0] == 'P':
prov_matrix.append(row)
else:
other_matrix.append(row)
In [11]:
p1_matrix = np.array(p1_matrix)
p2_matrix = np.array(p2_matrix)
p3_matrix = np.array(p3_matrix)
p4_matrix = np.array(p4_matrix)
last_matrix = np.array(last_matrix)
prov_matrix = np.array(prov_matrix)
other_matrix = np.array(other_matrix)
In [12]:
wb_articulos = load_workbook(filename='Lista de Artículos.xlsx', read_only=True)
ws_articulos = wb_articulos[wb_articulos.sheetnames[0]]
In [13]:
articulos_data_matrix = []
for row in ws_articulos:
row_current = []
for cell in row:
row_current.append(cell.value)
articulos_data_matrix.append(row_current)
articulos_data_array = np.array(articulos_data_matrix)
In [16]:
bool_array = []
prov_array = []
for row in range(articulos_data_array.shape[0]):
articulos_data_array[row,0] = str(articulos_data_array[row,0])
articulos_data_array[row,2] = str(articulos_data_array[row,2])
bool_data = str_to_bool(articulos_data_array[row,1])
bool_array.append(bool_data)
prov_data = buscaproob(articulos_data_array[row,2])
prov_array.append(prov_data)
bool_array = np.array(bool_array)
prov_array = np.array(prov_array)
In [17]:
articulos_pvp_array = np.zeros((articulos_data_array.shape[0],5),dtype='O')
In [18]:
articulos_pvp_array[:,0] = articulos_data_array[:,0]
In [19]:
articulos_pvp_array[:20,]
Out[19]:
In [20]:
p1_errores = []
p2_errores = []
p3_errores = []
p4_errores = []
pvp_list = [p1_matrix,
p2_matrix,
p3_matrix,
p4_matrix]
pvp_error_list = [p1_errores,
p2_errores,
p3_errores,
p4_errores]
for pvp_num in range(4):
print('calculando PVP', pvp_num + 1, 'antiguo')
for row in range(pvp_list[pvp_num].shape[0]):
name = pvp_list[pvp_num][row, 1]
cost = pvp_list[pvp_num][row, 2]
if name in articulos_pvp_array[:,0]:
loc = np.where(articulos_pvp_array[:,0]==name)[0][0]
articulos_pvp_array[loc, pvp_num + 1] = cost
else:
pvp_error_list[pvp_num].append(pvp_list[pvp_num][row, :])
p1_errores = np.array(p1_errores)
p2_errores = np.array(p2_errores)
p3_errores = np.array(p3_errores)
p4_errores = np.array(p4_errores)
print('completado')
In [21]:
prov_cost_dict = {}
for row_num in range(prov_matrix.shape[0]):
row = prov_matrix[row_num,:]
name = row[1]
cost = row[2]
if name in prov_cost_dict :
prov_cost_dict[name].append(cost)
else:
prov_cost_dict[name] = [cost]
In [22]:
def calc_precio_base(lista_precios):
return np.round(np.mean(lista_precios), decimals=2)
In [23]:
precio_base_array = []
for name in prov_cost_dict :
cost = calc_precio_base(prov_cost_dict[name])
precio_base_array.append([name, cost])
precio_base_array = np.array(precio_base_array)
precio_compra_array = precio_base_array
In [24]:
last_matrix_new =[]
last_matrix_error = []
for row_num in range(last_matrix.shape[0]):
row = last_matrix[row_num,1:]
if row[1] != None:
last_matrix_new.append(row)
else:
last_matrix_error.append(row)
last_matrix_new=np.array(last_matrix_new)
last_matrix_error = np.array(last_matrix_error)
last_matrix = last_matrix_new
In [25]:
art_in_last_not_in_prov = []
for row_num in range(last_matrix_new.shape[0]):
row = last_matrix_new[row_num,:]
name = row[0]
cost = row[1]
if not(name in precio_base_array[:,0]):
if cost != 0:
art_in_last_not_in_prov.append(row)
art_in_last_not_in_prov = np.array(art_in_last_not_in_prov)
In [26]:
art_in_prov_not_in_last = []
for row_num in range(precio_base_array.shape[0]):
row = precio_base_array[row_num,:]
name = row[0]
cost = row[1]
if not(name in last_matrix_new[:,0]):
if cost != 0:
art_in_prov_not_in_last.append(row)
art_in_prov_not_in_last = np.array(art_in_prov_not_in_last)
In [27]:
alertas_dict = {}
text = 'artículo con precio de compra pero no de proveedor'
for row in range(art_in_last_not_in_prov.shape[0]):
name = art_in_last_not_in_prov[row,0]
alertas_dict[name] = [text]
In [28]:
text = 'artículo con precio de proovedor pero no de compra'
for row in range(art_in_prov_not_in_last.shape[0]):
name = art_in_prov_not_in_last[row,0]
alertas_dict[name] = [text]
In [29]:
last_matrix_new = np.concatenate((last_matrix_new, art_in_prov_not_in_last))
precio_base_array = np.concatenate((precio_base_array, art_in_last_not_in_prov))
Comparar precio medio de proveedores y ultimo precio de compra
In [30]:
precio_base_final_array = []
for row_num in range(precio_base_array.shape[0]):
row = precio_base_array[row_num,:]
name = row[0]
cost = float(row[1])
if name in last_matrix_new[:,0]:
loc = np.where(last_matrix_new[:,0]==name)[0][0]
cost_2 = float(last_matrix_new[loc,1])
cost_dif = abs((cost-cost_2)/np.min([cost, cost_2]))
if cost_dif > 5 :
alertas_dict[name] = 'diferencia grande entre precio de compra y precios de proveedor'
elif cost_dif > 0.5 :
alertas_dict[name] = 'diferencia pequeña entre precio de compra y precios de proveedor'
cost_final = np.mean([cost, cost_2])
precio_base_final_array.append([name, cost_final])
else:
print('ESTE ERROR NUNCA PUEDE APARECER. AVISAR A SIRO')
print('ERROR', name, 'NO ENCONTRADO')
precio_base_final_array = np.array(precio_base_final_array)
In [31]:
def factor_precio(f_01, f_1000):
slope = (f_1000-f_01)/4
f_0 = f_01 + slope
def tempfun(x):
fun = f_0 + slope * np.log10(x)
fun2 = np.maximum(fun, f_1000)
fun3 = np.minimum(fun2, f_01)
return fun3
return tempfun
In [32]:
def precios(precio_compra):
p3_factor = factor_precio(3, 1.3)(precio_compra)
p3 = precio_compra * p3_factor
p1 = 2.1 * p3
p2_factor = factor_precio(1.4, 1.15)(p1)
p2 = p1 * p2_factor
p4_factor = factor_precio(2, 1.4)(p1)
p4 = p1 * p4_factor
return [p1, p2, p3, p4]
In [33]:
precio_base_final = np.array(precio_base_final_array[:,1], dtype='float64')
In [34]:
bool_array.shape[0]
Out[34]:
In [35]:
precio_base_final.shape[0]
Out[35]:
In [36]:
articulos_pvp_nuevo_array = np.round(np.array(precios(precio_base_final)).T,decimals=2)
In [37]:
articulos_pvp_array_diferencias = np.zeros_like(articulos_pvp_array)
articulos_pvp_nuevo_array_coherent = np.zeros_like(articulos_pvp_array)
articulos_pvp_array_diferencias[:,0] = articulos_pvp_array[:,0]
articulos_pvp_nuevo_array_coherent[:,0] = articulos_pvp_array[:,0]
articulos_pvp_no_encontrado = []
alerta_grave_dict = {}
for row in range(precio_base_final_array.shape[0]):
name = precio_base_final_array[row,0]
pvp_nuevo = articulos_pvp_nuevo_array[row,:]
if name in articulos_pvp_array[:,0]:
loc = np.where(articulos_pvp_array[:,0]==name)[0][0]
pvp_viejo = np.array(articulos_pvp_array[loc,1:], dtype='float64')
diferencia = np.round((100 * (pvp_nuevo-pvp_viejo) / pvp_viejo), decimals=2)
articulos_pvp_array_diferencias[loc,1:] = diferencia
articulos_pvp_nuevo_array_coherent[loc,1:] = pvp_nuevo
else:
articulos_pvp_no_encontrado.append([name].append(list(pvp_nuevo)))
In [38]:
for row in range(articulos_pvp_array.shape[0]):
name = articulos_pvp_array[row,0]
pvp_nuevo = articulos_pvp_nuevo_array_coherent[row,1:]
pvp_viejo = articulos_pvp_array[row, 1:]
if (np.sum(pvp_nuevo) == 0) and (np.sum(pvp_viejo) != 0) :
alerta_grave_dict[name] = 'PRECIO NUEVO NO CALCULADO: PRECIO NO MODIFICADO'
articulos_pvp_nuevo_array_coherent[row,1:] = pvp_viejo
In [39]:
pvp_arrays_list = [articulos_pvp_array, articulos_pvp_nuevo_array_coherent, articulos_pvp_array_diferencias]
In [40]:
precios_dato_array = np.zeros([articulos_pvp_array.shape[0],3])
In [41]:
for row in range(articulos_pvp_array.shape[0]):
name = articulos_pvp_array[row,0]
if name in last_matrix[:,0]:
loc = np.where(last_matrix[:,0]==name)[0][0]
precios_dato_array[row,2] = last_matrix[loc,1]
if name in precio_compra_array[:,0]:
loc = np.where(precio_compra_array[:,0]==name)[0][0]
precios_dato_array[row,1] = precio_compra_array[loc,1]
if name in precio_base_final_array[:,0]:
loc = np.where(precio_base_final_array[:,0]==name)[0][0]
precios_dato_array[row,0] = precio_base_final_array[loc,1]
In [42]:
data_to_save = [['art',
'pvp1 ant', 'pvp1 nuev', '% difer',
'pvp2 ant', 'pvp2 nuev', '% difer',
'pvp3 ant', 'pvp3 nuev', '% difer',
'pvp4 ant', 'pvp4 nuev', '% difer',
'P Base', 'P Proveedor', 'P compra']]
for row in range(articulos_pvp_array.shape[0]):
#for row in range(20):
name = articulos_pvp_array[row, 0]
new_row = [name]
for pvp_num in range(4):
for table in range(3):
cost = pvp_arrays_list[table][row, pvp_num + 1]
new_row.append(cost)
for ii in range(3):
cost = precios_dato_array[row, ii]
new_row.append(cost)
data_to_save.append(new_row)
data_to_save_array = np.array(data_to_save[1:])
In [43]:
prov_temp = prov_matrix[np.where(prov_matrix[:,0] == 'P385' )[0]]
found_prov = prov_temp[np.where(prov_temp[:,1] == '0240D010BN4HC')[0]]
found_prov
Out[43]:
In [44]:
bool(found_prov.shape[0])
Out[44]:
In [45]:
found_prov[0,2]
Out[45]:
In [46]:
cost
Out[46]:
In [47]:
notas = []
error_array =['']
for row in range(bool_array.shape[0]):
name = articulos_pvp_array[row, 0]
if not bool_array[row]:
prov = prov_array[row]
if prov == '':
notas.append('Artículo de no Autoprecio sin proveedor asignado')
error_array.append(True)
continue
prov_temp = prov_matrix[np.where(prov_matrix[:,0] == prov )[0]]
found_prov = prov_temp[np.where(prov_temp[:,1] == name)[0]]
if not bool(found_prov.shape[0]):
notas.append('Artículo no encontrado en el catálogo del proveedor asignado: '+str(prov))
error_array.append(True)
continue
data_to_save_array[row, 1:7]=0
data_to_save_array[row, 10:13]=0
cost = found_prov[0,2]
cost_2 = float(data_to_save_array[row,7])
data_to_save_array[row,8] = cost
data_to_save_array[row,9] = np.round(100*(cost - cost_2)/cost_2, decimals=2)
notas.append('Precio de proveedor: '+str(prov))
else:
notas.append('')
error_array.append(False)
In [48]:
new_wb = Workbook()
In [49]:
ws = new_wb.active
In [50]:
data_to_save = [['art',
'pvp1 ant', 'pvp1 nuev', '% difer',
'pvp2 ant', 'pvp2 nuev', '% difer',
'pvp3 ant', 'pvp3 nuev', '% difer',
'pvp4 ant', 'pvp4 nuev', '% difer',
'P Base', 'P Proveedor', 'P compra']]
for row in range(bool_array.shape[0]):
line = []
for cell in data_to_save_array[row, :]:
line.append(cell)
line.append(notas[row])
data_to_save.append(line)
In [51]:
for row in range(len(data_to_save)):
ws.append(data_to_save[row])
In [52]:
def rgb(red, green, blue):
'''Crea código rgb a partir de porcentajes'''
if red < 0 : red = 0
if red > 100 : red = 100
if green < 0 : green = 0
if green > 100 : green = 100
if blue < 0 : blue = 0
if blue > 100 : blue = 100
r = round(red * 2.55)
g = round(green * 2.55)
b = round(blue * 2.55)
r = hex(r)[-2:]
g = hex(g)[-2:]
b = hex(b)[-2:]
return 'ff' + r + g + b
In [53]:
def relleno(r,g,b):
rell = PatternFill(start_color=rgb(r,g,b),
end_color=rgb(r,g,b),
fill_type='solid')
return rell
In [54]:
def coord_excel(row,col):
str_key ='ABCDEFGHIJKLMNOPQRSTUVW'
return str_key[col] + str(row+1)
In [73]:
rojo = relleno(100,60,60)
verde_claro = relleno(90,98,94)
naranja_claro = relleno(98, 90, 80)
naranja = relleno(98, 80, 60)
rojo_claro = relleno(100, 85, 85)
verde_oscuro = relleno(70,90,80)
gris_oscuro = relleno(70,70,70)
gris_claro = relleno(90,90,90)
verde_mas_oscuro = relleno(40,80,60)
azul_claro = relleno(90,95,100)
azul_oscuro = relleno(70,70,90)
marron = relleno(85,75,40)
In [56]:
ws.column_dimensions['A'].width = 30
str_key ='ABCDEFGHIJKLMNOPQRSTUVW'
for col in range(1, 16):
col_name = str_key[col]
ws.column_dimensions[col_name].width = 7
In [57]:
for pvp_num in range(4):
for row in range(1,len(data_to_save)):
col = 3 * (pvp_num + 1)
cell = ws[coord_excel(row,col)]
diff = float(cell.value)
color = verde_claro
if diff > 500 :
color = rojo
elif diff > 100 :
color = verde_mas_oscuro
elif diff > 20 :
color = verde_oscuro
elif diff < -50 :
color = naranja
elif diff < 0 :
color = naranja_claro
for ii in range(3):
cell = ws[coord_excel(row,col - ii)]
cell.fill = color
In [58]:
for row in range(1,len(data_to_save)):
compra =float( ws[coord_excel(row,14)].value)
prov = float(ws[coord_excel(row,15)].value)
if (compra == 0 or prov == 0):
for col in range(13, 16):
cell = ws[coord_excel(row,col)]
cell.fill = naranja_claro
else:
dif = abs(compra - prov)
minimo = min(compra, prov)
porcen = 100 * dif / minimo
if (dif > 0.05 and porcen > 50):
color = azul_oscuro
else:
color = azul_claro
for col in range(13, 16):
cell = ws[coord_excel(row,col)]
cell.fill = color
In [59]:
for row in range(1,len(data_to_save)):
name = articulos_pvp_array[row-1, 0]
if name in alerta_grave_dict:
for col in range(1, 16):
cell = ws[coord_excel(row,col)]
cell.fill = gris_oscuro
In [60]:
for row in range(1,len(data_to_save)):
nulo = True
for col in range(1, 16):
cell = ws[coord_excel(row,col)]
if float(cell.value) != 0 : nulo = False
if nulo:
for col in range(1, 16):
cell = ws[coord_excel(row,col)]
cell.fill = gris_claro
In [74]:
for row in range(1,len(data_to_save)):
if error_array[row]:
for col in range(1, 16):
cell = ws[coord_excel(row,col)]
cell.fill = marron
In [61]:
thick_border = Border(right=Side(style='thick'))
In [62]:
thick_bottom = Border(bottom=Side(style='thick'))
In [63]:
for pvp_num in range(5):
for row in range(1,len(data_to_save)):
col = pvp_num * 3
cell = ws[coord_excel(row,col)]
cell.border = thick_border
In [64]:
for col in range(13):
cell = ws[coord_excel(0,col)]
cell.border = thick_bottom
In [65]:
ws.freeze_panes = 'A2'
In [66]:
ws.auto_filter.ref = "A1:" + coord_excel(0, len(data_to_save[0]))
In [75]:
new_wb.save('resultado_excel.xlsx')
In [ ]:
In [ ]:
In [ ]: