def main():
# 0:00:00.063795 - without select
# 0:00:00.034555 - with select
start_d = datetime.combine(date(year=2020, month=5, day=1), datetime.min.time())
end_d = start_d + timedelta(days=7)
# список заказов с сайта
order_list = session.query(Order).filter(Order.date >= start_d).filter(Order.date <= end_d)
# список чеков +- неделя
ord_start_d = start_d - timedelta(days=7)
ord_end_d = end_d + timedelta(days=7)
recipe_list = recipe_by_period(ord_start_d, ord_end_d)
# список ОПК +- неделя
opk_start_d = start_d - timedelta(days=7)
opk_end_d = end_d + timedelta(days=7)
opk_list = opk_by_period(opk_start_d, opk_end_d)
# список заказов 1С день в день
order_1c_list = orders_by_period(start_d, end_d)
# для каждого заказа с сайта проверяем и записываем
for order in order_list:
if order.order_status == 2:
pass
def recheck_all_customers():
customers = session.query(Customer).all()
for customer in customers:
new1c_customer = compare_customers_by_phone(customer.phone)
if new1c_customer is not None:
customer.customer1c = new1c_customer
customer.is_1C_resident = True
session.add(customer)
session.commit()
def recheck_last_10_customers():
# query = users.select().order_by(users.c.id.desc()).limit(5)
customers = session.query(Customer).order_by(Customer.id.desc()).limit(10)
for customer in customers:
new1c_customer = compare_customers_by_phone(customer.phone)
if new1c_customer is not None:
customer.customer1c = new1c_customer
customer.is_1C_resident = True
session.add(customer)
session.commit()
def main(year, month, day, steps):
for i in range(steps):
d = date(year=year, month=month, day=day) + timedelta(days=i)
start_date = f"{d.year}-{('%02d' % d.month)}-{('%02d' % d.day)}T00:00:00"
end_date = f"{d.year}-{('%02d' % d.month)}-{('%02d' % d.day)}T23:59:59"
print(start_date)
for depart in dep_name_list.values():
print(depart)
# номера карт по чекам
inf_card_nums = get_recipe_by_period(
start_date, end_date, get_department_id_by_code(depart))
# номера клиентов из карт
client_number_list = []
for card_num in inf_card_nums:
client_number_list.append(get_client_by_inf_card(card_num))
# Получаем клиента, если нет в базе, получаем контактную инфу, записываем
for client_number in client_number_list:
# toDo !!! Ошибочные карты !!!!
if client_number == '00000000-0000-0000-0000-000000000000':
break
client_info = get_client_by_code(client_number)
try:
customer1c = session.query(Customer1c).filter(
Customer1c.code_1c == client_info['Code']).scalar()
except MultipleResultsFound:
print('Error client code: ', client_info['Code'])
sys.exit('Error message')
if customer1c is None:
reg_info = get_contact_reg_info(client_number)
new_customer = Customer1c(
code_1c=client_info['Code'],
id_1c=client_number,
name=client_info['НаименованиеПолное'],
dateCreate=client_info['ДатаСоздания'])
try:
new_customer.phone = reg_info[0][:25]
new_customer.phone_for_search = reg_info[1]
except TypeError:
new_customer.phone = ''
new_customer.phone_for_search = ''
session.add(new_customer)
session.commit()
for error in total_list:
print(error)
def compare_customers_by_phone(phone_number):
global total_find
global doubles
global find_list
if phone_number is not None and phone_number not in bad_phone_list:
clear_phone_num = ''.join(x for x in phone_number if x.isdigit())
search = "%{}%".format(clear_phone_num[1:])
customer1c = session.query(Customer1c).filter(
Customer1c.phone_for_search.like(search)).all()
if len(customer1c) > 1:
doubles.append([phone_number, clear_phone_num])
if customer1c is not None:
cur_customer1c: Customer1c = None
for cust in customer1c:
# Проверка и возврат того, кто свежее :)
if cur_customer1c is None or cur_customer1c.dateCreate < cust.dateCreate:
cur_customer1c = cust
total_find = total_find + 1
return cur_customer1c
return None
def get_products(order_id):
res = session.query(Product).filter(Product.order_id == order_id)
print(res)
return res
def get_list(startDate, endDate):
sDate = datetime.combine(startDate, datetime.min.time())
eDate = datetime.combine(endDate, datetime.max.time())
return session.query(Order).filter(Order.date >= sDate).filter(
Order.date <= eDate).order_by(Order.number.desc())
def collect_write_norm_order(f_user, order_id, count):
"""
from b_sale_order: DATE_INSERT
from b_user: (if not exist)
from b_sale_basket: products
:param f_user:
:param order_id:
:param count:
:return:
"""
# toDo update order if already in DB
order = session.query(Order).filter(Order.number == order_id).scalar()
if order is not None:
print('Here it is! Order: ', order_id)
return
q_string = f"SELECT DATE_INSERT, USER_ID, PRICE, PRICE_DELIVERY, CANCELED, STATUS_ID " \
f"FROM b_sale_order where ID = {order_id}"
db_cursor.execute(q_string)
# print('Good', order_id, f_user)
bso_res = db_cursor.fetchall()[0]
correct_status = 1
if bso_res[4] == 'Y' or bso_res[5] == 'CF':
correct_status = 2
correct_date = bso_res[0] # + timedelta(hours=4)
q_string = f"SELECT NAME, LAST_NAME, EMAIL, PERSONAL_PHONE FROM b_user WHERE ID = {bso_res[1]}"
db_cursor.execute(q_string)
bu_res = db_cursor.fetchall()[0]
c_order = Order(date=correct_date,
b_fuser_id=f_user,
number=order_id,
delivery_price=bso_res[3],
order_status=correct_status,
b_status_id=bso_res[5],
total_sum=bso_res[2],
total_quantity=3)
customer = session.query(Customer).filter(
Customer.bit_id == bso_res[1]).scalar()
if customer is not None:
c_order.customer = customer
else:
c_order.customer = Customer(bit_id=bso_res[1],
name=bu_res[0],
last_name=bu_res[1],
e_mail=bu_res[2],
phone=bu_res[3],
is_1C_resident=False)
q_string = f"SELECT NAME, QUANTITY, PRICE FROM b_sale_basket WHERE ORDER_ID = {order_id}"
db_cursor.execute(q_string)
total_quantity = 0
for prd in db_cursor.fetchall():
c_order.products.append(
Product(name=prd[0], quantity=prd[1], price=prd[2]))
total_quantity += prd[1]
c_order.total_quantity = total_quantity
session.add(c_order)
session.commit()