def click_pbAccept(self): # Обновить/добавить
a = self.leFilter.text().strip()
if lenl(a) < 10 or lenl(a) > 11:
return None
elif lenl(a) == 10:
a = l('7' + self.leFilter.text())
else:
if str(l(a))[0] == '8':
a = '7' + str(l(a))[1:]
elif str(l(a))[0] == '7':
q = 0
else:
return None
self.dbconn.connect()
dbcursor = self.dbconn.cursor()
dbcursor.execute('SELECT count(*) FROM contacts WHERE phone = %s', (a,))
rows = dbcursor.fetchall()
if rows[0][0] == 0:
dbcursor.execute('INSERT contacts (phone, about, edit_date) VALUES(%s,%s,%s)',(a, self.leAbout.text().strip(),
datetime.now()))
else:
dbcursor.execute('UPDATE contacts SET about = %s, edit_date = %s WHERE phone = %s',(self.leAbout.text().strip(),
datetime.now(), a))
self.dbconn.commit()
self.leFilter.setText('')
self.setup_tableWidget()
return
def first_setup_tableWidget(self):
self.tableWidget.setColumnCount(0)
self.tableWidget.setRowCount(0) # Кол-во строк из таблицы
sql = 'SELECT phone, tip, about, DATE_FORMAT(edit_date,"%d.%m %H:%i") FROM contacts'
if lenl(self.leFilter.text()) > 0:
sql += ' WHERE phone LIKE "%' + str(l(self.leFilter.text())) + '%"'
self.dbconn.connect()
read_cursor = self.dbconn.cursor()
read_cursor.execute(sql)
rows = read_cursor.fetchall()
self.tableWidget.setColumnCount(4) # Устанавливаем кол-во колонок
self.tableWidget.setRowCount(len(rows)) # Кол-во строк из таблицы
for i, row in enumerate(rows):
for j, cell in enumerate(row):
label = QLabel()
if j == 0:
label.setText(fine_phone(str(cell)))
else:
label.setText(str(cell))
label.setAlignment(Qt.AlignCenter)
self.tableWidget.setCellWidget(i, j, label)
# self.tableWidget.setItem(i, j, QTableWidgetItem(str(cell)))
# Устанавливаем заголовки таблицы
self.tableWidget.setHorizontalHeaderLabels(["телефон", "тип", "описание","дата"])
# Устанавливаем выравнивание на заголовки
self.tableWidget.horizontalHeaderItem(0).setTextAlignment(Qt.AlignCenter)
self.tableWidget.horizontalHeaderItem(1).setTextAlignment(Qt.AlignCenter)
self.tableWidget.horizontalHeaderItem(2).setTextAlignment(Qt.AlignCenter)
# делаем ресайз колонок по содержимому
self.tableWidget.resizeColumnsToContents()
return
elem = p(d=driver, f='c', **clicktity['cAddrFACTtoo']
) # Адреса регистрации и проживания всегда отличаются
wj(driver)
elem.click()
wj(driver)
probel = ''
if s(res_inp['РайонРЕГ']) != '' and s(res_inp['ГородРЕГ']) != '':
probel = ' '
res_inp['РайонРЕГ'] = s(res_inp['РайонРЕГ']) + probel + s(
res_inp['ГородРЕГ'])
if s(res_inp['НасПунктРЕГ']) == '':
res_inp['НасПунктРЕГ'] = s(res_inp['РайонРЕГ'])
if s(res_inp['УлицаРЕГ']) == '':
res_inp['УлицаРЕГ'] = s(res_inp['НасПунктРЕГ'])
if lenl(res_inp['ИндексРЕГ']) != 0:
my_input(driver, ['ИндексРЕГ'], res_inp, inputtity)
if chk(d=driver, f='p', **clicktity['ПроверкаИндекса']):
elem = p(d=driver, f='p', **inputtity['ИндексРЕГ'])
wj(driver)
elem.clear()
wj(driver)
elem.send_keys(' ')
wj(driver)
if chk(d=driver, f='p', **inputtity['РегионРЕГ']):
if p(d=driver, f='p', **inputtity['РегионРЕГзнач']) == '':
my_input(driver, ['РегионРЕГ'], res_inp, inputtity)
if chk(d=driver, f='p', **inputtity['РайонРЕГ']):
if p(d=driver, f='p', **inputtity['РайонРЕГзнач']) == '':
my_input(driver, ['РайонРЕГ'], res_inp, inputtity)
if chk(d=driver, f='p', **inputtity['НасПунктРЕГ']):
write_row = (xlsx_file_cut[0:xlsx_file_cut.rfind('.xlsx')], )
for k, cell in enumerate(row):
if fields[k][2:] == 'date':
try:
write_row += (datetime.strptime(cell.value,
"%d.%m.%Y").date(), )
except:
write_row += (datetime.strptime('11.11.1111',
"%d.%m.%Y").date(), )
print(datetime.now().strftime("%H:%M:%S") + ' В файле ' +
xlsx_file_cut + ' в строке ' + str(j + 1) +
' в поле ' + fields[k] + ' значение ' +
str(cell.value) + ' сброшено до 11.11.1111')
elif fields[k] == 'snils':
write_row += (l(cell.value), )
if not (lenl(cell.value) < 12 and l(cell.value) > 100):
omit = True
print('СНИЛС ' + str(cell.value) + ' пропущен')
elif fields[k] == 'from_tbl':
write_row += (xlsx_file_cut[0:xlsx_file_cut.rfind('.xlsx')], )
elif fields[k] == 'id':
q = 0
else:
write_row += (cell.value, )
if not omit:
write_rows.append(write_row)
if j % 10000 == 0:
write_cursor = dbconn.cursor()
write_cursor.executemany(sql, write_rows)
dbconn.commit()
write_rows = []
region = chuvak(row[headers['p_region']])
region_ch = 'p'
if not region:
region = row[headers['p_place']]
kladr_ok = False
region_ch = 'p'
if not region:
region = 'РЕГИОН НЕ УКАЗАН'
region_id = -1
for j, alfa_region in enumerate(ALFA_REGIONS):
if alfa_region.upper().find(region) > -1:
region_id = ALFA_REGIONS[alfa_region]
break
b_country = ''
if lenl(row[headers['b_country']]) > 0:
b_country = row[headers['b_country']]
b_region = ''
if lenl(row[headers['b_region']]) > 0:
b_region = row[headers['b_region']]
b_district = ''
if lenl(row[headers['b_district']]) > 0:
b_district = row[headers['b_district']]
b_place = ''
if lenl(row[headers['b_place']]) > 0:
b_place = row[headers['b_place']]
if b_country.upper().strip() == 'РОССИЯ' or b_country.upper().strip(
) == 'РФ':
b_country = ''
if len(b_place) > 35:
path = get_path(sys.argv[1])
print('\n' + datetime.now().strftime("%H:%M:%S") +
' Начинаем преобразование и нарезку xlsx файлов \n')
k = 1 # Счетчик строк в csv
file_number = 1
cl_csvs = []
for i, sheet in enumerate(
sheets): # Загружаем все xlsx файлы по мере сохранения в БД
for j, row in enumerate(sheet.rows): # Теперь строки
if j == 0:
continue
cl_csv = {}
if lenl(row[keys[IN_SNILS[0]]].value
) < 12: # and l(row[keys[IN_SNILS[0]]].value) > 100:
cl_csv[IN_SNILS[0]] = '{:=011d}'.format(
l(row[keys[IN_SNILS[0]]].value))
cl_csvs.append(cl_csv)
else:
print('\nСНИЛС ' + str(row[keys[IN_SNILS[0]]].value) +
' пропущен\n')
if k % 15000 == 0:
with open(path + '{:=03d}'.format(file_number) + '.csv',
'w',
encoding='cp1251') as output_file:
dict_writer = csv.DictWriter(
output_file, [IN_SNILS[0]],
delimiter=';') #, quoting=csv.QUOTE_NONNUMERIC)
dict_writer.writeheader()
dict_writer.writerows(cl_csvs)