def analyze_rk(sheet, titul: str, path: str):
"""Разбираем заибучее РК заключение,
это второй лист в экселевском файле
:param sheet: лист эксельки
:param titul: экземпляр Titul
:param path: путь до файла (для отладки)
"""
# ----------------
# Определяем линию
# ----------------
line_str = sheet['F8'].value
if not line_str or not 'Линия' in line_str:
line_str = sheet['F9'].value
if not line_str or not 'Линия' in line_str:
logger.info('--- LINE ABSENT %s---' % path)
line_str = line_str.replace('Линия', '').strip()
line_str = line_str.replace('0 H', '0H')
line_str = line_str.replace(' ', ' ')
try:
line_str = replace_rus2eng(line_str)
except Exception as e:
logger.info(e)
return
line = Line.objects.filter(name=line_str, titul=titul).first()
if not line:
logger.info('--- LINE NOT FOUND %s ---' % line_str)
# Создаем линию, если название адекватное
if len(line_str) < 5:
return
def get_object(row, i: int, model, cond: dict = None):
"""Создать/обновить простой объект (только название)
:param row: строка
:param i: номер ячейки
:param cond: условие для выборки
"""
if not cond:
cond = {}
obj = None
obj_name = row[i].value
if obj_name:
obj_name = str(obj_name).strip()
if '*' in obj_name:
return None
if model in (Line, ):
obj_name = replace_rus2eng(obj_name)
first4letters = obj_name[:6]
if not '-' in first4letters:
obj_name = obj_name.replace(' ', '-')
obj_name = '%s-%s' % (obj_name[:4], obj_name[4:])
obj_name = obj_name.replace('--', '-')
#print('obj_name', obj_name)
if len(obj_name) < 5:
return
obj = model.objects.filter(name=obj_name).filter(**cond).first()
if not obj:
obj = model(name=obj_name)
for k, v in cond.items():
setattr(obj, k, v)
obj.save()
return obj
def titul_parse_html_helper(tr, line_str:str, name: str, el: str = 'td'):
"""Вспомогательная функция для анализа строки таблицы
Парсинг хтмл tr (lxml)
:param table: таблица lxml
:param line_str: номер линии
:param name: название файла
:param el: td/th
"""
joint = {'line': line_str, 'fname': name, 'stigma': ''}
tds = tr.xpath('.//%s' % el)
number_joint = tds[1].xpath('.//p')
# Пустые строки не пишем
if not number_joint:
return
stigma = tds[2].xpath('.//p')
material = tds[3].xpath('.//p')
size = tds[4].xpath('.//p')
date = tds[5].xpath('.//p')
welding_type = tds[6].xpath('.//p')
# Допишем в эксель и неразобранные данные
raw_fields = (
('raw_number_joint', number_joint),
('raw_stigma', stigma),
('raw_material', material),
('raw_size', size),
('raw_date', date),
('raw_welding_type', welding_type),
)
for raw_field in raw_fields:
value = ' '.join([item.text for item in raw_field[1] if item.text])
joint[raw_field[0]] = value
if number_joint:
parts = number_joint[0].text.split(' ')
if len(parts) == 1: # Если 1 элемент - значит, пробел забыли там
parts = parts[0].split('.')
joint['number'] = parts[0]
conn_view = parts[1]
joint['conn_view'] = conn_view_finder(conn_view, parts)
if stigma:
for part in stigma:
stigma = part.text
if not stigma:
continue
stigma = replace_rus2eng(stigma, force_return=True)
s = search_stigma(stigma)
if s:
joint['stigma'] += ' ' + s
if material:
joint['material'] = material[0].text
if size:
data = search_elements([item.text.replace('\n', ' ') for item in size if item.text])
joint.update(data)
if date:
joint['date'] = date[0].text
if welding_type:
joint['welding_type'] = welding_type[0].text.strip()
return joint
def analyze_vik(sheet, titul: str, path: str):
"""Разбираем заибучее ВИК заключение,
это первый лист в экселевском файле
:param sheet: лист эксельки
:param titul: экземпляр Titul
:param path: путь до файла (для отладки)
"""
# Не всегда в титуле есть код титула,
# может быть просто название, например,
# Эстакада трубопроводов
# Может вообще не быть названия титула
titul_description = sheet['F6'].value or sheet['G6'].value
titul_name = None
if titul_description:
if not 'У' in titul_description:
titul_name = sheet['I6'].value
else:
titul_name = 'У%s' % titul_description.split('У')[1]
titul_description = titul_description.split('У')[0]
# --------------------------------------
# Захерачиваем описание титулу,
# если не совпало имя титула, то алертим
# --------------------------------------
if titul_name:
titul_name = titul_name.strip()
if titul_name != titul.name:
pass
#logger.info('TITUL HAS INCORRECT NAME %s vs %s' % (titul.name, titul_name))
#return
# ---------------------------------------
# Определяем описание титула накопительно
# ---------------------------------------
analyze_titul_description(titul, titul_description)
# -----------------------------------
# Определяем шифр титула накопительно
# -----------------------------------
titul_code = sheet['H7'].value or sheet['I7'].value
analyze_titul_code(titul, titul_code)
# ----------------
# Определяем линию
# ----------------
line_str = sheet['A8'].value or sheet['B8'].value or sheet['A7'].value
if not line_str:
logger.info('--- LINE ABSENT %s---' % path)
return
line_str = line_str.replace('Линия', '').strip()
line_str = line_str.replace('0 H', '0H')
line_str = line_str.replace(' ', ' ')
try:
line_str = replace_rus2eng(line_str)
except Exception as e:
logger.info(e)
return
line = Line.objects.filter(name=line_str, titul=titul).first()
if not line:
logger.info('--- LINE NOT FOUND %s ---' % line_str)
# Создаем линию, если название адекватное
if len(line_str) < 5:
return
line = Line.objects.create(name=line_str, titul=titul)
# -------------------
# Находим дату сварки
# -------------------
request_control_date = get_welding_date(sheet['G11'].value)
if not request_control_date:
request_control_date = get_welding_date(sheet['H11'].value)
if not request_control_date:
request_control_date = get_welding_date(sheet['G10'].value)
if not request_control_date:
logger.info('--- DATE INCORRENT %s ---' % path)
# --------------------------------
# Находим толщину стенки и диаметр
# --------------------------------
side_thickness = diameter = None
size_row_letter = 'I'
is_completed_row = (sheet['I16'].value and sheet['I16'].value.strip() == 'выполнен') or (sheet['I14'].value and sheet['I14'].value.strip() == 'выполнен')
if not sheet['A1'].value or is_completed_row:
size_row_letter = 'J'
size = None
for i in (16, 17, 18):
size_row_number = i
size_row_value = sheet['%s%s' % (size_row_letter, size_row_number)].value
if not size_row_value:
continue
#print('=', size_row_value)
size = get_size(size_row_value)
if size:
break
if not size:
logger.info('--- SIDE_THICKNESS or DIAMETER NOT FOUND %s, %s ---' % (size_row_number, path))
else:
diameter, side_thickness = size
# ------------------------
# Находим номера стыков
# У пидорасов на ВИК стыки
# разделены точкой,
# а на РК слешем
# ------------------------
joint_row_letter = 'B'
if not sheet['A1'].value:
joint_row_letter = 'C'
joints_str = sheet['%s%s' % (joint_row_letter, size_row_number)].value
if not joints_str:
logger.info('--- JOINTS NOT FOUND %s, %s ---' % (size, path))
return
# Если есть говносмещение - мы найдем ячейку с №
# вместо номеров стыков
if joints_str == '№':
if joint_row_letter == 'B':
joint_row_letter = 'C'
joints_str = sheet['%s%s' % (joint_row_letter, size_row_number)].value
joints_str = '%s' % joints_str
joints_arr = joints_str.split(',')
# ----------------------------
# Находим строчку с материалом
# и типом сварных соединений
# ----------------------------
material = welding_conn_view = None
material_str = None
material_row_letter = 'A'
if not sheet['A1'].value or (sheet['A18'].value is None and sheet['A19'].value is None and sheet['A20'].value is None and sheet['A21'].value is None):
material_row_letter = 'B'
material_q = ('Тип сварных соединений', 'материал')
for i in (18, 19, 20, 21):
material_row_number = i
value = sheet['%s%s' % (material_row_letter, material_row_number)].value
if not value:
continue
if material_q[0] in value and material_q[1] in value:
material_str = value
break
if not material_str:
logger.info('--- MATERIAL NOT FOUND %s ---' % path)
else:
material, welding_conn_view = get_material(material_str)
if not material or not welding_conn_view:
logger.info('--- MATERIAL NOT FOUND %s ---' % path)
# -----------------
# Поиск типа сварки
# -----------------
welding_type = None
welding_type_row_letter = material_row_letter
welding_type_row_number = material_row_number + 1
welding_type_str = sheet['%s%s' % (
welding_type_row_letter,
welding_type_row_number
)].value
if not welding_type_str:
logger.info('--- WELDING_TYPE NOT FOUND %s ---' % path)
else:
welding_type = get_welding_type(welding_type_str)
if not welding_type:
logger.info('--- WELDING_TYPE NOT FOUND %s ---' % path)
# -------------
# Ищем сварщика
# -------------
welders = []
welder_row_number = welding_type_row_number + 1
for letter in ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'):
welders_str = sheet['%s%s' % (
letter,
welder_row_number,
)].value
if not welders_str:
continue
welders += get_welders(welders_str)
# Следующую строчку ищем тоже
welder_row_number += 1
if not welders:
for letter in ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'):
welders_str = sheet['%s%s' % (
letter,
welder_row_number,
)].value
if not welders_str:
continue
welders += get_welders(welders_str)
if not welders:
logger.info('--- WELDERS NOT FOUND %s ---' % path)
# ---------------------
# Ищем дефектоскопистов
# Контроль выполнил:
# ---------------------
defectoscopists = []
letters = ('G', 'H', 'I', 'J')
for letter in letters:
for number in range(36, 46):
adr = '%s%s' % (letter, number)
value = sheet[adr].value
if not value:
continue
defectoscopists += get_defectoscopists(value)
if not defectoscopists:
logger.info('--- DEFECTOSCOPISTS NOT FOUND %s ---' % path)
for joint_str in joints_arr:
joint_str = joint_str.replace('.', '/').replace('\\', '/').strip()
if not joint_str:
continue
# Если нет цифр в номере стыка, тогда не создаем
digits = kill_quotes(joint_str, 'int')
if not digits:
logger.info('--- BAD JOINT %s, %s ---' % (joint_str, path))
continue
joint = Joint.objects.filter(line=line, name=joint_str).first()
if not joint:
logger.info('--- JOINT NOT FOUND %s, %s ---' % (joint_str, line.name))
# Ну создаем, блеать, раз линия найдена
joint = Joint.objects.create(line=line, name=joint_str)
# Стык найден - надо найти заключение,
# если нету - создать или обновить
welding_joints = WeldingJoint.objects.filter(joint=joint)
if len(welding_joints) > 1:
logger.info('--- MORE THAN 1 WeldingJoint %s' % joint)
return
if welding_joints:
welding_joint = welding_joints[0]
else:
welding_joint = WeldingJoint(joint=joint)
welding_joint.state = 3
welding_joint.diameter = diameter
welding_joint.side_thickness = side_thickness
welding_joint.request_control_date = request_control_date
welding_joint.material = material
welding_joint.welding_conn_view = welding_conn_view
welding_joint.welding_type = welding_type
welding_joint.save()
for i, welder in enumerate(welders):
if i > 3:
break
analog = JointWelder.objects.filter(
welder=welder,
welding_joint=welding_joint,
)
if not analog:
JointWelder.objects.create(
welder=welder,
welding_joint=welding_joint,
position=i + 1,
)
joint_conclusion = JointConclusion.objects.filter(welding_joint=welding_joint).first()
if not joint_conclusion:
joint_conclusion = JointConclusion(welding_joint=welding_joint)
joint_conclusion.date = welding_joint.request_control_date
joint_conclusion.vik_active = True
joint_conclusion.vik_state = 1
if defectoscopists:
joint_conclusion.vik_controller = defectoscopists[0]
if len(defectoscopists) > 1:
joint_conclusion.vik_director = defectoscopists[1]
joint_conclusion.save()
def more_lines_helper(path: str):
"""Загрузка дополнительных узлов в дополнительным линиям
:param path: путь до эксельки
"""
subject = Subject.objects.all().first()
wb = open_wb(path)
sheet = wb.active
rows = sheet.rows
cur_titul = None
for row in rows:
cell_values = [cell.value for cell in row]
# 1 - линия
# 2 - стык
# 3 - диаметр х тощина стенки
# 4 - дата сварки
# 5 - клеймо
# 6 - вид сварного соединения
# 7 - тип сварки
# 12 - статус (новый/готов)
digit = row[0].value
if not digit:
continue
digit = str(digit)
if 'Титул ' in digit:
titul_str = digit.split('Титул ')[1]
titul_str = titul_str.split(' ')[0]
titul_str = titul_str.split('.')[0]
titul = Titul.objects.filter(name=titul_str).first()
if not titul:
logger.info('[ERROR]: titul not found %s' % titul_str)
cur_titul = Titul.objects.create(name=titul_str,
subject=subject)
else:
cur_titul = titul
#logger.info('TITUL %s' % cur_titul.name)
diameter = side_thickness = date = stigma = welding_conn_view = welding_type = None
joint_str = row[2].value
size = row[3].value
if size and ('х' in size or 'ъ' in size):
size = str(size).strip()
if 'ъ' in size:
diameter, side_thickness = size.split('ъ')
elif 'х' in size:
diameter, side_thickness = size.split('х')
elif 'x' in size:
diameter, side_thickness = size.split('x')
diameter = diameter.strip().replace(',', '.')
side_thickness = side_thickness.strip().replace(',', '.')
if '/' in side_thickness:
side_thickness = side_thickness.split('/')[0]
if row[4].value:
date = str_to_date(str(row[4].value))
if row[5].value:
stigma = str(row[5].value).strip()
try:
stigma = replace_rus2eng(stigma)
except Exception as e:
print('[ERROR]: %s' % e)
stigma = None
if row[6].value:
welding_conn_view_str = replace_eng2rus(row[6].value)
welding_conn_view = get_choice(
welding_conn_view_str, WeldingJoint.welding_conn_view_choices)
if row[7].value:
welding_type = row[7].value.replace('+', ' - ')
welding_type = replace_eng2rus(welding_type)
welding_type = welding_type.replace(' ', ' ')
welding_type = get_choice(welding_type, WELDING_TYPES)
line_str = row[1].value
if not line_str or not 'Линия ' in line_str:
#print(digit, line_str)
continue
line_str = line_str.split('Линия ')[1]
line_str = line_str.strip()
try:
line_str = replace_rus2eng(line_str)
except Exception:
print('Русские буквы даже после всех замен %s, стык %s' %
(line_str, joint_str))
continue
analogs = Line.objects.filter(name__startswith=line_str,
titul=cur_titul)
for item in analogs:
if item.name.split('-')[0] == line_str:
analogs = [item]
break
repair = None
state = 1
state_str = row[12].value
if state_str:
if 'готов' in state_str:
state = 3
elif 'ремонт' in state_str:
repair = 1
#print(state_str)
if not analogs:
print('Линия не найдена %s в титуле %s, стык %s' %
(line_str, cur_titul.name, joint_str))
continue
elif len(analogs) > 1:
print(
'Найдено больше 1 линии по "%s", это %s в титуле %s, стык %s' %
(line_str, [item.name
for item in analogs], cur_titul.name, joint_str))
pass
else:
line = analogs[0]
#print('line %s' % line.name)
#print(diameter, side_thickness, date, stigma, welding_conn_view, welding_type)
joint = get_object(row, 2, Joint, {'line': line})
welding_joint_obj = {
'joint': joint,
'diameter': diameter,
'side_thickness': side_thickness,
'request_control_date': date,
'welding_conn_view': welding_conn_view,
'welding_type': welding_type,
'state': state,
'repair': repair,
}
#print(welding_joint_obj)
# Ищем аналог
analog = get_welding_joint_analog(joint, welding_joint_obj)
if analog:
continue
welding_joint = WeldingJoint.objects.create(**welding_joint_obj)
print(' --- добавлен в титул %s линию %s стык %s' %
(cur_titul.name, line.name, joint.name))
if not stigma:
continue
welder = Welder.objects.filter(stigma__icontains=stigma).first()
if not welder:
###print('welder not found %s' % stigma)
continue
JointWelder.objects.create(welding_joint=welding_joint,
welder=welder,
position=1)
def titul_docx2excel(html_path: str):
"""Запихиваем все в эксельку
:param html_path: путь до папки с html файлами
"""
dest_fname = 'result.xlsx'
html_files = ListDir(html_path)
result = []
rows = []
for html_file in html_files:
cur_html = os.path.join(html_path, html_file)
if not html_file.endswith('.html'):
drop_file(cur_html)
continue
content = ''
with open_file(cur_html, 'r') as f:
content = f.read()
# Поиск проблем
if search_problem(content, ''):
print(cur_html)
else:
drop_file(cur_html)
tree = lxml_html.fromstring(content)
tables = tree.xpath('//table')
line_str = None
# Поиск линии
for table in tables:
search_line = table.xpath('.//tr/td')
for item in search_line:
par = item.xpath('.//p')
if not par or not par[0].text:
continue
if 'Линия' in par[0].text:
if len(par) == 1:
line_str = par[0].text.replace('Линия', '').strip()
else:
line_str = par[1].text
break
for table in tables:
ths = table.xpath('.//thead/tr/th/p')
if ths:
th = ths[0]
if '№ п/п' in th.text:
name = html_file.replace('.html', '')
if '___' in name:
name = name.split('___')[1]
result += titul_parse_html(table, line_str, name)
dest = full_path(os.path.join(html_path, dest_fname))
book = xlsxwriter.Workbook(dest)
sheet = book.add_worksheet('Лист 1')
row_number = 0
titles = (
('Файл', ''),
('Линия', 'line'),
('№ стыка', 'joint'),
('Материал', 'material'),
('Диаметр,мм', 'diameter'),
('Толщина,мм', 'side_thickness'),
('Дата', 'welding_date'),
('Тип сварки', 'welding_type'),
('Вид соединения', 'welding_conn_view'),
('Свар элемент 1', 'join_type_from'),
('Свар элемент 2', 'join_type_to'),
('Клеймо', 'stigma'),
# Фуфел из вордовского файла баз разбора
('Без анализа - Стык', ''),
('Без анализа - Клеймо', ''),
('Без анализа - Материал', ''),
('Без анализа - Размер', ''),
('Без анализа - Дата', ''),
('Без анализа - Тип сварки', ''),
)
for i, title in enumerate(titles):
sheet.write(row_number, i, title[0])
sheet.write(row_number + 1, i, title[1])
row_number += 1
result_len = len(result)
for item in result:
row_number += 1
number = item.get('number')
if not number:
continue
if number.startswith('Ст'):
number = number[2:]
number = number.replace('.', '')
diameter = side_thickness = ''
size = item.get('size')
if size:
size = size.replace(',', '.')
diameter, side_thickness = size.split('x')
diameter = rega_digit.sub('', diameter)
side_thickness = rega_digit.sub('', side_thickness)
if not diameter:
size = item.get('alt_size')
if size:
size = size.replace(',', '.')
diameter, side_thickness = size.split('x')
diameter = rega_digit.sub('', diameter)
side_thickness = rega_digit.sub('', side_thickness)
welding_date = item.get('date')
if welding_date:
# Вставляем год правильно
welding_date = welding_date.strip()
if welding_date and len(welding_date.strip()) == 8:
welding_date = '%s20%s' % (welding_date[:6], welding_date[6:])
stigma = item.get('stigma')
try:
stigma = replace_rus2eng(stigma)
except Exception:
pass
row = (
item.get('fname'),
item.get('line'),
number,
item.get('material'),
diameter,
side_thickness,
welding_date,
item.get('welding_type'),
item.get('conn_view'),
item.get(0),
item.get(1),
stigma,
# Фуфел из вордовского файла без разбора
item.get('raw_number_joint'),
item.get('raw_stigma'),
item.get('raw_material'),
item.get('raw_size'),
item.get('raw_date'),
item.get('raw_welding_type'),
)
for i, field in enumerate(row):
sheet.write(row_number, i, field)
book.close()
result = {'errors': []}
if result_len < MAX_ROWS_PREVIEW:
welders = Welder.objects.all().only('id', 'stigma')
stigmas = {welder.stigma: welder for welder in welders}
result = prepare_data_from_excel(
full_path(os.path.join(html_path, dest_fname)),
stigmas=stigmas,
)
result['result'] = os.path.join('/media/', html_path, dest_fname)
return result