def get_form_questions(form):
query = session.query(Assessment.assessment_id,Assessment.description,Forms.form_title). \
from_statement(text("SELECT assessment_id, description, forms.form_title FROM assessment, forms "
"WHERE assessment.category = forms.form_title "
"AND forms.form_id = {};".format(form))).all()
titles = session.query(Forms.form_title)
questions = []
for item in query:
questions.append({"questionId": item[0], "question": item[1]})
res = {"formId": form, "title": query[-1][-1], "questions": questions}
return formatResponse(res)
def submit_form(user_id, form_id, data):
current_pending = session.query(
Users.pending_forms).filter(Users.uid == user_id).one()
current_completed = session.query(
Users.completed_forms).filter(Users.uid == user_id).one()
new_completed = [int(form_id)]
for form in current_completed[0]:
if int(form) not in new_completed and int(form_id) != int(form):
new_completed.append(int(form))
new_pending = []
for form in current_pending[0]:
if int(form) not in new_pending and int(form_id) != int(form):
new_pending.append(int(form))
session.query(Users) \
.filter(Users.uid == user_id) \
.update(dict(completed_forms=new_completed,pending_forms=new_pending))
session.commit()
# insert a result for each assessment question
for question in data["results"]:
value = (10 / question["answer"])
answer_type = 3
if question["answer"] > 7:
answer_type = 4
elif question["answer"] < 4:
answer_type = 2
extra_data = session \
.query(Companies.company_name,
Assessment.title,
Users.uid,
Assessment.assessment_id,
Companies.company_id) \
.filter(Users.uid == user_id,Users.company_id == Companies.company_id,
Assessment.assessment_id == question["questionId"]) \
.one()
results = Results()
results.user_uid = extra_data[2]
results.category = extra_data[1]
results.parent_company = extra_data[0]
results.value = int(round(value))
results.assessment_id = int(question["questionId"])
results.answer_type_id = answer_type
session.add(results)
session.commit()
return get_user(user_id)
def close_orders():
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
date = datetime.date.today()
date_today = date.strftime("%Y-%m-%d")
if request.args.get('st'):
status = request.args.get('st')
id = request.args.get('id')
stmt = update(Close_orders).where(Close_orders.id == id).values(status = status)
session.execute(stmt)
logs = Logs(user=namesd, time=date_today, message='Изменен статус завершенного заказа №: ' + id)
session.add(logs)
session.commit()
session.flush()
flash('Статус заказа успешно изменен')
return redirect(url_for('close_orders'))
if request.args.get('del') == "yes":
id = request.args.get('id')
yes = session.query(Close_orders).filter(Close_orders.id == id).first()
if yes.toner == 0 and yes.opc == 0 and yes.pcr == 0 and yes.wiper_blade == 0 and yes.recovery_blade == 0 and yes.develop_blade == 0 and yes.doctor_blade == 0:
session.query(Close_orders).filter(Close_orders.id == id).delete()
logs = Logs(user=namesd, time=date_today, message='Удален завершенный заказ №: ' + id)
session.add(logs)
session.commit()
session.flush()
flash ('Заказ был успешно удален')
return redirect(url_for('close_orders'))
else:
flash ('Заказ не был удален')
return redirect(url_for('close_orders'))
full = session.query(Close_orders.id, Cartridges.name, Customers.name, Close_orders.toner, Close_orders.opc,
Close_orders.pcr, Close_orders.wiper_blade, Close_orders.recovery_blade, Close_orders.develop_blade,
Close_orders.doctor_blade, Close_orders.barcode, Close_orders.mark,Close_orders.user_close,
Close_orders.date, Close_orders.date_close, Close_orders.status)\
.order_by(desc(Close_orders.id))\
.join(Cartridges, Close_orders.cartridge == Cartridges.id)\
.join(Customers, Close_orders.customer == Customers.id)
tmp_orders = {}
for order in full:
if order.name not in tmp_orders:
tmp_orders[order.name] = []
tmp_orders[order.name].append(order)
return render_template('close_orders.html', full=tmp_orders, fullnames = namesd)
def full_order():
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
id = request.args['id']
user = Users.query.all()
bar = session.query(Orders.id, Orders.barcode).filter_by(id=id).first()
full = session.query(Orders.id, Cartridges.name, Customers.name, Tow.fullname, Orders.barcode, Orders.mark, Orders.date)\
.filter_by(id=id)\
.join(Cartridges, Orders.cartridge == Cartridges.id)\
.join(Customers, Orders.customer == Customers.id)\
.join(Tow, Orders.tow == Tow.id)
tmp_orders = {}
for order in full:
if order.name not in tmp_orders:
tmp_orders[order.name] = []
tmp_orders[order.name].append(order)
barcc = bar.barcode
fuls = session.query(Close_orders.id, Cartridges.name, Customers.name, Close_orders.toner, Close_orders.opc,
Close_orders.pcr, Close_orders.wiper_blade, Close_orders.recovery_blade, Close_orders.develop_blade,
Close_orders.doctor_blade, Close_orders.barcode, Close_orders.mark,Close_orders.user_close,
Close_orders.date, Close_orders.date_close, Close_orders.status, Close_orders.comment)\
.filter_by(barcode=barcc)\
.join(Cartridges, Close_orders.cartridge == Cartridges.id)\
.join(Customers, Close_orders.customer == Customers.id)
tmpp_orders = {}
for order in fuls:
if order.barcode not in tmpp_orders:
tmpp_orders[order.barcode] = []
tmpp_orders[order.barcode].append(order)
fulss = session.query(Archive_orders.id, Cartridges.name, Customers.name, Archive_orders.toner, Archive_orders.opc,
Archive_orders.pcr, Archive_orders.wiper_blade, Archive_orders.recovery_blade, Archive_orders.develop_blade,
Archive_orders.doctor_blade, Archive_orders.barcode, Archive_orders.mark,Archive_orders.user_close,
Archive_orders.date, Archive_orders.date_close, Archive_orders.status, Archive_orders.comment)\
.filter_by(barcode=barcc)\
.join(Cartridges, Archive_orders.cartridge == Cartridges.id)\
.join(Customers, Archive_orders.customer == Customers.id)
tmppp_orders = {}
for order in fulss:
if order.barcode not in tmppp_orders:
tmppp_orders[order.barcode] = []
tmppp_orders[order.barcode].append(order)
return render_template('full_order.html', full=tmp_orders, user=user, fuls=tmpp_orders, fuls1=tmppp_orders, fullnames = namesd)
def send_form(user_id, form_id):
current_pending = session.query(
Users.pending_forms).filter(Users.uid == user_id).one()
new_pending = [int(form_id)]
for form in current_pending[0]:
if int(form) not in new_pending:
new_pending.append(int(form))
session.query(Users) \
.filter(Users.uid == user_id) \
.update(dict(pending_forms=new_pending))
session.commit()
return get_user(user_id)
def get_institutions():
query = session.query(Companies.company_name, Companies.img_url).all()
results = []
for item in query:
results.append({"name": item[0], "img_url": item[1]})
return formatResponse(results)
def create_user(name):
count_users_by_name = session.query(User).filter(User.username==name).count()
if count_users_by_name > 0:
return 'User Already Exists'
user = User(username=name)
session.add(user)
session.commit()
return f'Successfully Added user {name}'
def check_auth(username, password):
"""This function is called to check if a username /
password combination is valid.
"""
user = session.query(Users).filter_by(name=username).first()
pass_hash = hashlib.md5(password.encode())
passwd = pass_hash.hexdigest()
return username == user.name and passwd == user.password
def updateclear(name, photo, method, recid, date):
update = session.query(Cleardata).filter_by(recid=recid).first()
update.name = name
update.photo = photo
update.method = method
update.upload = date
session.commit()
session.close()
def removeclear(date):
ondelete = session.query(Cleardata).filter(Cleardata.upload < date)
if ondelete is not None:
for data in ondelete:
print('delete '+data.recid)
session.delete(data)
session.commit()
session.close()
else:
print('delete is none')
def delete_file():
now = datetime.now()
shares = session.query(Himitsu_lun).filter(
Himitsu_lun.delete_at < now).all()
delete_file_list = []
for share in shares:
delete_file_list.append(f"{share.enc_filename}")
for enc_filename in delete_file_list:
count = 0
while count < 2:
print(count)
if os.path.exists(PATH + enc_filename):
os.remove(PATH + enc_filename)
break
else:
count += 1
session.query(Himitsu_lun).filter(
Himitsu_lun.enc_filename == enc_filename).delete()
session.commit()
def create_shares_for_decrypt(code, share):
try:
db_share = (session.query(Himitsu_lun).filter(
Himitsu_lun.enc_filename == code).first())
db_dec_share = dec_db_share(f"{db_share.nonce}", f"{db_share.share}")
if db_dec_share == "Incorrect decryption":
return "error"
shares = [(1, unhexlify(share)), (3, unhexlify(db_dec_share))]
return shares
except binascii.Error:
return "error"
def archive_all():
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
date = datetime.date.today()
date_today = date.strftime("%Y-%m-%d")
query = session.query(Close_orders).filter(Close_orders.status == 'success').all()
for order in query:
cartridge = order.cartridge
customer = order.customer
toner = order.toner
opc = order.opc
pcr = order.pcr
wiper_blade = order.wiper_blade
recovery_blade = order.recovery_blade
develop_blade = order.develop_blade
doctor_blade = order.doctor_blade
barcode = order.barcode
mark = order.mark
user_close = order.user_close
date = order.date
date_close = order.date_close
status = order.status
comment = order.comment
archive = Archive_orders(cartridge=cartridge,customer=customer,toner=toner,opc=opc,pcr=pcr,wiper_blade=wiper_blade,
recovery_blade=recovery_blade,develop_blade=develop_blade,doctor_blade=doctor_blade,
user_close=user_close,barcode=barcode,mark=mark,date=date,date_close=date_close,status=status,
comment=comment)
session.add(archive)
logs = Logs(user=namesd, time=date_today, message='Завершенные заказы перемещены в архив')
session.add(logs)
session.query(Close_orders).filter(Close_orders.id == order.id).delete()
session.commit()
session.flush()
flash ('Заказы перемещены в архив')
return redirect(url_for('close_orders'))
def get_policy_holders_by_company(company):
query = session.query(Users.username,Users.uid,Users.email,Companies.company_name,Users.img_url) \
.join(Companies,Companies.company_id == Users.company_id) \
.filter(Users.user_type_id == 2,Companies.company_name.like("{}".format(company))).all()
results = []
for item in query:
results.append({
"username": item[0],
"uid": item[1],
"email": item[2],
"company": item[3],
"img_url": item[4]
})
return formatResponse(results)
def search_policy_holders(search):
query = session.query(Users.username,Users.uid,Users.email,Companies.company_name,Users.img_url) \
.join(Companies,Companies.company_id == Users.company_id) \
.filter(Users.email.like("%{}%".format(search))).all()
results = []
for item in query:
results.append({
"username": item[0],
"uid": item[1],
"email": item[2],
"company": item[3],
"img_url": item[4]
})
return formatResponse(results)
def decrypt_file(code, shares):
key = Shamir.combine(shares)
db_share = (session.query(Himitsu_lun).filter(
Himitsu_lun.enc_filename == code).first())
filename = f"{db_share.filename}"
with open(PATH + code, "rb") as fi:
nonce, tag = [fi.read(16) for i in range(2)]
cipher = AES.new(key, AES.MODE_EAX, nonce)
try:
result = cipher.decrypt(fi.read())
cipher.verify(tag)
return result, filename
except ValueError:
return "error", ""
def get_question_answers():
query = session.query(
Questions.questions_id, Questions.question,
Answer_Type.answer_type).filter(
Questions.questions_id == Question_Answer.question_id,
Answer_Type.answer_type_id ==
Question_Answer.answer_type_id).all()
response = []
for item in query:
response.append({
"questionId": item[0],
"question": item[1],
"answerType": item[2]
})
return formatResponse(response)
def tow(rows=None):
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
if request.method == 'POST':
if request.form['action'] == 'Add':
return render_template('tow_add.html', fullnames = namesd)
if request.form['action'] == 'Full':
return render_template('full_tow.html', fullnames = namesd)
if request.form['action'] == 'Cancel':
return redirect(url_for('tow'))
if request.form['action'] == 'Adds':
category = Category(name=request.form['name'])
session.add(category)
flash ('Категория успешно добалена')
return redirect(url_for('tow'))
tow = Tow.query.all()
return render_template('tow.html',rows=tow, fullnames = namesd)
def decorated(*args, **kwargs):
auth = request.authorization
user = session.query(Users).filter_by(name=auth.username).first()
if user.privilegies == 0 or user.privilegies == 1 or user.privilegies == 2:
return authenticate()
return f(*args, **kwargs)
def customers(rows=None):
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
date = datetime.date.today()
date_today = date.strftime("%d.%m.%Y")
if request.args.get('del') == "yes":
id = request.args.get('id')
yes = Archive_orders.query.filter_by(customer=id).count()
yess = Close_orders.query.filter_by(customer=id).count()
cust = session.query(Customers).filter(Customers.id == id).one()
if yes == 0 and yess == 0:
session.query(Customers).filter(Customers.id == id).delete()
logs = Logs(user=namesd, time=date_today, message='Клиент ' + cust.name + ' удален')
session.add(logs)
session.commit()
session.flush()
flash('Клиент успешно удален')
return redirect(url_for('customers'))
else:
flash('Клиент не удален')
return redirect(url_for('customers'))
if request.method == 'POST':
if request.form['action'] == 'Add':
return render_template('customer_add.html', fullnames = namesd)
if request.form['action'] == 'Full':
return render_template('full_customers.html', fullnames = namesd)
if request.form['action'] == 'Cancel':
return redirect(url_for('customers'))
if request.form['action'] == 'Find':
if request.form['name']:
name = request.form['name']
customers = session.query(Customers).filter(Customers.name.like('%' + name + '%')).all()
customerss = session.query(Customers).filter(Customers.inn.like('%' + name + '%')).all()
return render_template('customers.html', rows=customers, rowss=customerss, fullnames = namesd)
else:
flash('Картридж не найден')
if request.form['action'] == 'Edit':
id = request.form['id']
name = request.form['name']
inn = request.form['inn']
try:
inn = request.form['inn']
except:
inn = ""
session.query(Customers).filter(Customers.id == id).update(
{'name': name, 'inn': inn})
logs = Logs(user=namesd, time=date_today, message='Клиент ' + name + ' изменен')
session.add(logs)
session.commit()
session.flush()
flash('Клиент успешно изменён')
return redirect(url_for('customers'))
if request.form['action'] == 'Adds':
name = request.form['name']
try:
inn = request.form['inn']
except:
inn = ""
customer = Customers(name=name,inn=inn)
session.add(customer)
logs = Logs(user=namesd, time=date_today, message='Клиент ' + name + ' добавлен')
session.add(logs)
session.commit()
session.flush()
flash ('Клиент успешно добавлен')
return redirect(url_for('customers'))
customers = Customers.query.order_by('name').all()
return render_template('customers.html',rows=customers, fullnames = namesd)
def settings():
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
return render_template('settings.html', fullnames = namesd)
def full_close_order():
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
date = datetime.date.today()
date_today = date.strftime("%d.%m.%Y")
if request.args.get('barcode'):
barcode = request.args['barcode']
user = Users.query.all()
full = session.query(Close_orders, Cartridges, Customers)\
.filter_by(barcode=barcode)\
.join(Cartridges, Close_orders.cartridge == Cartridges.id)\
.join(Customers, Close_orders.customer == Customers.id)
full1 = session.query(Archive_orders, Cartridges, Customers)\
.filter_by(barcode=barcode)\
.join(Cartridges, Archive_orders.cartridge == Cartridges.id)\
.join(Customers, Archive_orders.customer == Customers.id)
return render_template('full_close_order.html', user=user, full=full, full1=full1, fullnames = namesd)
if request.form['action'] == 'Edit':
id = request.form['id']
user_close = request.form['user_close']
mark = request.form['mark']
update = session.query(Close_orders).filter_by(id=id).first()
comment = request.form['comment']
try:
toner = request.form['toner']
except:
toner = 0
try:
opc = request.form['opc']
except:
opc = 0
try:
pcr = request.form['pcr']
except:
pcr = 0
try:
wiper_blade = request.form['wiper_blade']
except:
wiper_blade = 0
try:
recovery_blade = request.form['recovery_blade']
except:
recovery_blade = 0
try:
develop_blade = request.form['develop_blade']
except:
develop_blade = 0
try:
doctor_blade = request.form['doctor_blade']
except:
doctor_blade = 0
session.query(Close_orders).filter(Close_orders.id == id).update({'cartridge' : update.cartridge,
'customer' : update.customer,'toner' : toner,'opc' : opc,'pcr' : pcr,'wiper_blade' : wiper_blade,
'recovery_blade' : recovery_blade,'develop_blade' : develop_blade,'doctor_blade' : doctor_blade,
'barcode' : update.barcode,'mark' : mark,'user_close' : user_close,'date' : update.date,
'date_close' : date_today, 'comment' : comment})
logs = Logs(user=namesd, time=date_today, message='Завершенный заказ изменен. Штрихкод: '+update.barcode)
session.add(logs)
session.commit()
session.flush()
flash('Заказ успешно изменен')
return redirect(url_for('close_orders'))
]
for user in users_list:
print(create_user(user))
user_obj = get_user_object('petya')
if user_obj:
print(create_posts(posts, user_obj.id))
tags = ['sun', 'space', 'solar_system']
for tag in tags:
print(create_tag_post(tag, 'Solar Echoes'))
# Direct keys
p = session.query(Post).join(User, Post.user_id == User.id).first()
print(p.user.username)
# Indirect keys
p = session.query(Post).join(User).first()
print(p.user.username)
p = session.query(Tag).join(User, Tag.id == User.id).first()
print(p)
p = session.query(Tag, User).join(User, Tag.id == User.id).first()
print(p)
# Counting
p = session.query(Tag, User).count()
print(p)
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.cell import get_column_letter
from models import Customers
from settings import session
customers = session.query(Customers).all()
count = session.query(Customers).count()
count1 = session.query(Customers).get_column.count()
print (count1)
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="%s" % get_column_letter(col))
print(ws3['AA10'].value)
wb.save(filename = dest_filename)
images=json.dumps(images),
proper_price=proper_price,
sales_price=sales_price,
source_url=url,
source='base-komai0526',
)
categories = get_categories()
for category in categories:
print(category.name)
resp = requests.get(category.source_url)
soup = BeautifulSoup(resp.content, 'html.parser')
product_links = soup.select('li.itemListBox > a')
for link in product_links:
url = link.get('href')
product_id = url.split('/')[-1]
product = session.query(models.Product).filter(
models.Product.product_id == product_id).first()
if not product:
product_params = get_product(url)
product = models.Product(category_id=category.id, **product_params)
product = get_or_create(session, models.Product, product,
'product_id')
# with open('{}.csv'.format(category_name), 'w+') as f:
# writer = csv.DictWriter(f, header)
# writer.writeheader()
# for row in products:
# writer.writerow(row)
def searchclear(recid):
answer = session.query(Cleardata).filter_by(recid=recid).first()
return answer
def get_users():
query = session.query(Users.username,Users.email,User_Type.user_type_id) \
.filter(Users.user_type_id == User_Type.user_type_id).all()
return formatResponse(query)
def export_product_csv():
# Qoo10のフォーマットの商品CSVを作成する
with open('qoo10_products.csv', 'w+') as f:
writer = csv.writer(f)
headers = [
'Item Code', 'Seller Code', 'Status', '2nd Cat Code', 'Item Name',
'Item Description', 'Short Title', 'Item Detail Header',
'Item Detail Footer', 'Brief Description', 'Image URL',
'Sell Price', 'Sell Qty', 'Shipping Group No', 'Item Weight',
'Option Info', 'Inventory Info', 'Maker No', 'Brand No',
'Product Model Name', 'Retail Price', 'Origin Type',
'Place of Origin', 'Industrial Code', 'Item Condition',
'Manufacture Date', 'Adult Product Y/N', 'A/S Info',
'Available Date', 'Gift', 'Additional Item Image',
'Inventory Cover Image', 'Multi Shipping Rate'
]
writer.writerow(headers)
for product in session.query(models.Product).all():
# Qoo10商品コード
item_code = ''
# 販売者商品コード
seller_code = product.id
# 取引待ち : S1
# 取引可能 : S2
# 取引廃止 : S4
status = 'S1'
# カテゴリーコード
cat_code = product.category.qoo10_categories[0].s_category_id
# 商品名
item_name = product.name
# 商品詳細
item_description = product.description.replace('\n', '')
# 短絡商品名
short_title = product.name[:20]
item_detail_header = product.name
item_detail_footer = product.name
# 商品説明
brief_description = product.name
image_url = json.loads(product.images)[0]
sell_price = product.sales_price
sell_qty = product.proper_price
# 送料無料
shipping_group_no = ''
item_weight = ''
option_info = ''
inventory_info = ''
maker_no = ''
brand_no = ''
product_model_name = ''
retail_price = product.proper_price
# 国内
origin_type = 1
# 国家名
place_of_origin = ''
# JANコード
industrial_code = ''
# 新品 : 1
# 中古品 (未使用) : 2
# 中古品 (新古品) : 3
# 中古品 (ほぼ新品) : 4
# 中古品 (状態良好) : 5
# 中古品 (少々古め) : 6
# 中古品 (使用不可(収集家用)) : 7
item_condition = 1
manufacture_date = ''
adult_product = 'N'
as_info = ''
# - 数字を入力する場合、商品の準備日となります。 (商品の準備日の入力 ex: 1)
# - 日時の形式で入力する場合、発売日となります。 (発売日の入力 ex: 2013-09-26)
# - 入力しない場合はすぐに配送できる一般の商品となります。
available_date = ''
# おまけ
gift = ''
# 商品の追加イメージ
# 最大 11個のイメージを追加することが可能
# 例 ) http://gd.image-qoo10.jp/mi/307/990/422990307.jpg$$http://gd.image-qoo10.jp/mi/905/046/422046905.jpg
additional_item_image = '$$'.join(json.loads(product.images)[0:])
inventory_cover_image = ''
# オプション送料の登録
# 最大2個のコード登録機能
# 例 ) 233982$$228507
multi_shipping_rate = ''
row = [
item_code,
seller_code,
status,
cat_code,
item_name,
item_description,
short_title,
item_detail_header,
item_detail_footer,
brief_description,
image_url,
sell_price,
sell_qty,
shipping_group_no,
item_weight,
option_info,
inventory_info,
maker_no,
brand_no,
product_model_name,
retail_price,
origin_type,
place_of_origin,
industrial_code,
item_condition,
manufacture_date,
adult_product,
as_info,
available_date,
gift,
additional_item_image,
inventory_cover_image,
multi_shipping_rate,
]
writer.writerow(row)
def get_forms():
query = session.query(Forms.form_id, Forms.form_title).all()
results = []
for item in query:
results.append({"id": item[0], "title": item[1]})
return formatResponse(results)
def barcode(rows=None):
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
date = datetime.date.today()
date_today = date.strftime("%d.%m.%Y")
full = session.query(Barcode, Cartridges,Customers).join(Cartridges, Barcode.cartridge == Cartridges.id).join(Customers, Barcode.customer == Customers.id).order_by(Barcode.id)
cartridges = Cartridges.query.order_by(Cartridges.name).all()
customers = Customers.query.order_by(Customers.name).all()
if request.method == 'POST':
if request.form['action'] == 'Find':
if request.form['barcode']:
barcod = request.form['barcode']
full = session.query(Barcode, Cartridges, Customers).join(Cartridges,
Barcode.cartridge == Cartridges.id).join(
Customers, Barcode.customer == Customers.id).filter(Barcode.barcode.like('%'+barcod+'%')).all()
return render_template('barcode.html', full=full, fullnames = namesd)
else:
flash('Штрихкод не найден')
if request.form['action'] == 'Edit':
barcode = request.form['barcod']
cartridge = request.form['cartridge']
customer = request.form['customer']
session.query(Barcode).filter(Barcode.barcode == barcode).update({'cartridge': cartridge, 'customer': customer, 'barcode': barcode})
session.query(Close_orders).filter(Close_orders.barcode == barcode).update({'cartridge': cartridge, 'customer': customer})
session.query(Archive_orders).filter(Archive_orders.barcode == barcode).update({'cartridge': cartridge, 'customer': customer})
session.query(Orders).filter(Orders.barcode == barcode).update({'cartridge': cartridge, 'customer': customer})
logs = Logs(user=namesd, time=date_today, message='Штрихкод № ' + barcode + ' изменен')
session.add(logs)
session.commit()
session.flush()
flash('Штрихкод был успешно изменён')
return redirect(url_for('barcode'))
if request.args.get('edit') == "yes":
barcode = request.args.get('barcode')
barcod = Barcode.query.filter_by(barcode = barcode).first()
cartridgee = Cartridges.query.filter_by(id = barcod.cartridge).first()
customerr = Customers.query.filter_by(id = barcod.customer).first()
return render_template('barcode_edit.html', cartridges=cartridges, customers=customers,barcode=barcode,customerr=customerr,cartridgee=cartridgee, fullnames = namesd)
if request.args.get('del') == "yes":
barcode = request.args.get('barcode')
yes = Archive_orders.query.filter_by(barcode = barcode).count()
yess = Close_orders.query.filter_by(barcode = barcode).count()
if yes == 0 and yess == 0:
session.query(Barcode).filter(Barcode.barcode == barcode).delete()
logs = Logs(user=namesd, time=date_today, message='Штрихкод № ' + barcode + ' удален')
session.add(logs)
session.commit()
session.flush()
flash('Штрихкод был успешно удален')
return redirect(url_for('barcode'))
else:
flash('Штрихкод не был удален')
return redirect(url_for('barcode'))
return render_template('barcode.html', full=full, cartridges=cartridges, customers=customers, fullnames = namesd)
def statistics():
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
customers = Customers.query.order_by(Customers.name).all()
full = session.query(Archive_orders.id, Customers.id, Customers.name) \
.join(Customers, Archive_orders.customer == Customers.id)\
.order_by(Customers.name)
tmp_orders1 = {}
for order in full:
if order.name not in tmp_orders1:
tmp_orders1[order.name] = []
full_work = session.query(func.min(Archive_orders.cartridge), func.min(Customers.name), Cartridges.name,
func.sum(Archive_orders.toner).label('toner'),
func.sum(Archive_orders.opc).label('opc'),
func.sum(Archive_orders.pcr).label('pcr'),
func.sum(Archive_orders.wiper_blade).label('wiper_blade'),
func.sum(Archive_orders.recovery_blade).label('recovery_blade'),
func.sum(Archive_orders.develop_blade).label('develop_blade'),
func.sum(Archive_orders.doctor_blade).label('doctor_blade')) \
.join(Customers, Archive_orders.customer == Customers.id) \
.join(Cartridges, Archive_orders.cartridge == Cartridges.id) \
.group_by(Cartridges.name) \
.filter(Customers.name == order.name).subquery()
full_cartridges = session.query(func.count(Barcode.barcode), Cartridges.name, func.min(full_work.c.toner),
func.min(full_work.c.opc), func.min(full_work.c.pcr), func.min(full_work.c.wiper_blade),
func.min(full_work.c.recovery_blade), func.min(full_work.c.develop_blade),
func.min(full_work.c.doctor_blade)) \
.join(Customers,Barcode.customer == Customers.id) \
.join(Cartridges, Barcode.cartridge == Cartridges.id) \
.outerjoin(full_work, Cartridges.name == full_work.c.name)\
.group_by(Cartridges.name)\
.filter(Customers.name == order.name).all()
tmp_orders1[order.name].append(full_cartridges)
if request.method == 'POST':
if request.form['action'] == 'Sort':
filters = []
customer = request.form['customer']
#if customer != '*':
#filters.append(Archive_orders.customer == customer)
date1 = request.form['date1']
if date1 != '':
filters.append(Archive_orders.date_close >= date1)
date2 = request.form['date2']
if date2 != '':
filters.append(Archive_orders.date_close <= date2)
full = session.query(Archive_orders.id, Customers.id, Customers.name) \
.join(Customers, Archive_orders.customer == Customers.id) \
.filter(Customers.id == customer)\
.order_by(Customers.name)
tmp_orders1 = {}
for order in full:
if order.name not in tmp_orders1:
tmp_orders1[order.name] = []
full_work = session.query(func.min(Archive_orders.cartridge), func.min(Customers.name),
Cartridges.name,
func.sum(Archive_orders.toner).label('toner'),
func.sum(Archive_orders.opc).label('opc'),
func.sum(Archive_orders.pcr).label('pcr'),
func.sum(Archive_orders.wiper_blade).label('wiper_blade'),
func.sum(Archive_orders.recovery_blade).label('recovery_blade'),
func.sum(Archive_orders.develop_blade).label('develop_blade'),
func.sum(Archive_orders.doctor_blade).label('doctor_blade')) \
.join(Customers, Archive_orders.customer == Customers.id) \
.join(Cartridges, Archive_orders.cartridge == Cartridges.id) \
.group_by(Cartridges.name) \
.filter(Customers.name == order.name,*filters).subquery()
full_cartridges = session.query(func.count(Barcode.barcode), Cartridges.name,
func.min(full_work.c.toner),
func.min(full_work.c.opc), func.min(full_work.c.pcr),
func.min(full_work.c.wiper_blade),
func.min(full_work.c.recovery_blade),
func.min(full_work.c.develop_blade),
func.min(full_work.c.doctor_blade)) \
.join(Customers, Barcode.customer == Customers.id) \
.join(Cartridges, Barcode.cartridge == Cartridges.id) \
.outerjoin(full_work, Cartridges.name == full_work.c.name) \
.group_by(Cartridges.name) \
.filter(Customers.name == order.name).all()
tmp_orders1[order.name].append(full_cartridges)
return render_template('statistics.html', full1=tmp_orders1, customers=customers, fullnames=namesd)
return render_template('statistics.html', full1=tmp_orders1, customers=customers, fullnames = namesd)
from settings import session
import re
from models import Barcode
from bar_gen import Code128
from settings import path_barcode
barcodes = session.query(Barcode.barcode).all()
for barcode in barcodes:
barc = str(barcode)
code = re.sub("[^\w\s]+", '', barc).strip()
full_barcode = path_barcode
Code128(code).save(formats=['gif'],outDir=full_barcode, fnRoot=code )
print (code)
def cartridges(rows=None):
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
date = datetime.date.today()
date_today = date.strftime("%d.%m.%Y")
if request.args.get('del') == "yes":
id = request.args.get('id')
yes = Archive_orders.query.filter_by(cartridge=id).count()
yess = Close_orders.query.filter_by(cartridge=id).count()
cart = session.query(Cartridges).filter(Cartridges.id == id).one()
if yes == 0 and yess == 0:
session.query(Cartridges).filter(Cartridges.id == id).delete()
logs = Logs(user=namesd, time=date_today, message='Картридж ' + cart.name + ' удален')
session.add(logs)
session.commit()
session.flush()
flash('Картридж успешно удален')
return redirect(url_for('cartridges'))
else:
flash('Картридж не удален')
return redirect(url_for('cartridges'))
if request.method == 'POST':
if request.form['action'] == 'Add':
return render_template('cartridge_add.html', fullnames = namesd)
if request.form['action'] == 'Cancel':
return redirect(url_for('cartridges'))
if request.form['action'] == 'Find':
if request.form['name']:
name = request.form['name']
cartridges = session.query(Cartridges).filter(Cartridges.name.like('%'+name+'%')).all()
return render_template('cartridges.html', rows=cartridges, fullnames = namesd)
else:
flash('Картридж не найден')
if request.form['action'] == 'Edit':
id = request.form['id']
name = request.form['name']
printer = request.form['printer']
try:
toner = request.form['toner']
except:
toner = 0
try:
opc = request.form['opc']
except:
opc = 0
try:
pcr = request.form['pcr']
except:
pcr = 0
try:
wiper_blade = request.form['wiper_blade']
except:
wiper_blade = 0
try:
recovery_blade = request.form['recovery_blade']
except:
recovery_blade = 0
try:
develop_blade = request.form['develop_blade']
except:
develop_blade = 0
try:
doctor_blade = request.form['doctor_blade']
except:
doctor_blade = 0
try:
printer = request.form['printer']
except:
printer = ""
session.query(Cartridges).filter(Cartridges.id == id).update(
{'name': name, 'toner': toner, 'opc': opc, 'pcr': pcr, 'wiper_blade': wiper_blade, 'recovery_blade': recovery_blade,
'develop_blade': develop_blade, 'doctor_blade': doctor_blade, 'printers': printer})
logs = Logs(user=namesd, time=date_today, message='Картридж ' + name + ' изменен')
session.add(logs)
session.commit()
session.flush()
flash('Картридж успешно изменён')
return redirect(url_for('cartridges'))
if request.form['action'] == 'Adds':
name = request.form['name']
try:
toner = request.form['toner']
except:
toner = 0
try:
opc = request.form['opc']
except:
opc = 0
try:
pcr = request.form['pcr']
except:
pcr = 0
try:
wiper_blade = request.form['wiper_blade']
except:
wiper_blade = 0
try:
recovery_blade = request.form['recovery_blade']
except:
recovery_blade = 0
try:
develop_blade = request.form['develop_blade']
except:
develop_blade = 0
try:
doctor_blade = request.form['doctor_blade']
except:
doctor_blade = 0
try:
printer = request.form['printer']
except:
printer = ""
cartridge = Cartridges(name=name,toner=toner,opc=opc,pcr=pcr,wiper_blade=wiper_blade,recovery_blade=recovery_blade,
develop_blade=develop_blade,doctor_blade=doctor_blade,printers=printer)
session.add(cartridge)
logs = Logs(user=namesd, time=date_today, message='Картридж ' + name + ' добавлен')
session.add(logs)
session.commit()
session.flush()
flash ('Картридж успешно добавлен')
return redirect(url_for('cartridges'))
cartridges = Cartridges.query.order_by('name').all()
return render_template('cartridges.html',rows=cartridges, fullnames = namesd)
def archive_orders():
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
full = session.query(Archive_orders.id, Cartridges.name, Customers.name, Archive_orders.toner, Archive_orders.opc,
Archive_orders.pcr, Archive_orders.wiper_blade, Archive_orders.recovery_blade, Archive_orders.develop_blade,
Archive_orders.doctor_blade, Archive_orders.barcode, Archive_orders.mark, Archive_orders.user_close,
Archive_orders.date, Archive_orders.date_close, Archive_orders.status)\
.order_by(desc(Archive_orders.id))\
.join(Cartridges, Archive_orders.cartridge == Cartridges.id)\
.join(Customers, Archive_orders.customer == Customers.id)
tmp_orders = {}
for order in full:
if order.name not in tmp_orders:
tmp_orders[order.name] = []
tmp_orders[order.name].append(order)
full0 = full.count()
cartridges = Cartridges.query.order_by(Cartridges.name).all()
customers = Customers.query.order_by(Customers.name).all()
users = Users.query.order_by(desc(Users.id)).all()
if request.method == 'POST':
if request.form['action'] == 'Sort':
if 'toner' in request.form or 'opc' in request.form or 'pcr' in request.form or 'wiper_blade' in request.form or \
'recovery_blade' in request.form or 'develop_blade' in request.form or 'doctor_blade' in request.form:
filters = []
cartridge = request.form['cartridge']
if cartridge != '*':
filters.append(Archive_orders.cartridge == cartridge)
customer = request.form['customer']
if customer != '*':
filters.append(Archive_orders.customer == customer)
mark = request.form['mark']
if mark != '*':
filters.append(Archive_orders.mark == mark)
user = request.form['user']
if user != '*':
filters.append(Archive_orders.user_close == user)
date1 = request.form['date1']
if date1 != '':
filters.append(Archive_orders.date_close >= date1)
date2 = request.form['date2']
if date2 != '':
filters.append(Archive_orders.date_close <= date2)
try :
request.form['toner']
filters.append(Archive_orders.toner == request.form['toner'])
except:
filters.append(Archive_orders.toner == 0)
try :
request.form['opc']
filters.append(Archive_orders.opc == request.form['opc'])
except:
filters.append(Archive_orders.opc == 0)
try :
request.form['pcr']
filters.append(Archive_orders.pcr == request.form['pcr'])
except:
filters.append(Archive_orders.pcr == 0)
try :
request.form['wiper_blade']
filters.append(Archive_orders.wiper_blade == request.form['wiper_blade'])
except:
filters.append(Archive_orders.wiper_blade == 0)
try :
request.form['recovery_blade']
filters.append(Archive_orders.recovery_blade == request.form['recovery_blade'])
except:
filters.append(Archive_orders.recovery_blade == 0)
try :
request.form['develop_blade']
filters.append(Archive_orders.develop_blade == request.form['develop_blade'])
except:
filters.append(Archive_orders.develop_blade == 0)
try :
request.form['doctor_blade']
filters.append(Archive_orders.doctor_blade == request.form['doctor_blade'])
except:
filters.append(Archive_orders.doctor_blade == 0)
else:
filters = []
cartridge = request.form['cartridge']
if cartridge != '*':
filters.append(Archive_orders.cartridge == cartridge)
customer = request.form['customer']
if customer != '*':
filters.append(Archive_orders.customer == customer)
mark = request.form['mark']
if mark != '*':
filters.append(Archive_orders.mark == mark)
user = request.form['user']
if user != '*':
filters.append(Archive_orders.user_close == user)
date1 = request.form['date1']
if date1 != '':
filters.append(Archive_orders.date_close >= date1)
date2 = request.form['date2']
if date2 != '':
filters.append(Archive_orders.date_close <= date2)
full1 = session.query(Archive_orders.id, Cartridges.name,
Customers.name, Archive_orders.toner, Archive_orders.opc,
Archive_orders.pcr, Archive_orders.wiper_blade, Archive_orders.recovery_blade,
Archive_orders.develop_blade,
Archive_orders.doctor_blade, Archive_orders.barcode, Archive_orders.mark,
Archive_orders.user_close,
Archive_orders.date, Archive_orders.date_close, Archive_orders.status)\
.filter(*filters)\
.join(Cartridges, Archive_orders.cartridge == Cartridges.id)\
.join(Customers, Archive_orders.customer == Customers.id)
full2 = full1.count()
tmp_orders1 = {}
for order in full1:
if order.name not in tmp_orders1:
tmp_orders1[order.name] = []
tmp_orders1[order.name].append(order)
return render_template('archive_orders.html',count=full2, full=tmp_orders1, cartridges=cartridges, customers=customers,
users=users, fullnames = namesd)
return render_template('archive_orders.html', count=full0, full=tmp_orders, cartridges=cartridges, customers=customers,
users=users, fullnames = namesd)
def logs(logs=None):
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
logs = session.query(Logs).all()
return render_template('logs.html', logs=logs, fullnames = namesd)
def orders(rows=None,cartridges=None,customers=None,tows=None,full=None):
auth = request.authorization
user1 = session.query(Users).filter_by(name=auth.username).first()
namesd = user1.fullname
date = datetime.date.today()
date_today = date.strftime("%Y-%m-%d")
cartridges = Cartridges.query.order_by(Cartridges.name).all()
customers = Customers.query.order_by(Customers.name).all()
tows = Tow.query.all()
user = Users.query.filter_by(vision='1').order_by(desc(Users.id)).all()
full = session.query(Orders.id, Cartridges.name, Customers.name, Tow.fullname, Orders.barcode, Orders.mark, Orders.date)\
.order_by(desc(Orders.id))\
.join(Cartridges, Orders.cartridge == Cartridges.id)\
.join(Customers, Orders.customer == Customers.id)\
.join(Tow, Orders.tow == Tow.id)
tmp_orders = {}
for order in full:
if order.name not in tmp_orders:
tmp_orders[order.name] = []
tmp_orders[order.name].append(order)
if request.method == 'POST':
if request.form['action'] == 'Add':
if request.form['barcode']:
barcod = request.form['barcode']
orderss = Orders.query.filter_by(barcode=barcod).count()
if orderss != 0:
barcodd = request.form['barcode']
full = session.query(Orders.id, Cartridges.name, Customers.name, Tow.fullname, Orders.barcode,
Orders.mark, Orders.date)\
.filter_by(barcode=barcodd).join(Cartridges, Orders.cartridge == Cartridges.id)\
.join(Customers, Orders.customer == Customers.id).join(Tow, Orders.tow == Tow.id)
tmps_orders = {}
for order in full:
if order.name not in tmps_orders:
tmps_orders[order.name] = []
tmps_orders[order.name].append(order)
return render_template('orders.html', tows=tows, full=tmps_orders, user=user)
else:
barcode = Barcode.query.filter_by(barcode=barcod).count()
if barcode == 0:
flash ('Штрих-код не найден')
else:
barcode = session.query(Barcode).filter_by(barcode=barcod).first()
cartridge = barcode.cartridge
customer = barcode.customer
mark = request.form['mark']
tow = request.form['tow']
barcod = barcode.barcode
order = Orders(cartridge=cartridge,customer=customer,tow=tow,barcode=barcod,mark=mark,date=date_today)
session.add(order)
logs = Logs(user=namesd, time=date_today, message='Добавлен заказ. Штрихкод: '+str(barcod))
session.add(logs)
session.commit()
session.flush()
flash ('Заказ успешно добавлен')
return redirect(url_for('orders'))
else:
return render_template('orders_add.html',cartridges=cartridges,customers=customers,tows=tows, date=date_today, fullnames = namesd)
if request.form['action'] == 'Full':
return render_template('full_orders.html')
if request.form['action'] == 'Chancel':
return redirect(url_for('orders'))
if request.form['action'] == 'cst_ord_add':
cst = request.form['cst_ord']
customerr = Customers.query.filter_by(name=cst).first()
return render_template('orders_add_cust.html',cartridges=cartridges,customerr=customerr,tows=tows, date=date_today, fullnames = namesd)
if request.form['action'] == 'Adds':
if not request.form['barcode']:
barcod = random.randint(10000000, 99999999 )
barcod = str(barcod)
barcode = Barcode.query.filter_by(barcode=barcod).count()
while barcode != 0:
barcod = random.randint(10000000, 99999999 )
barcod = str(barcod)
barcode = Barcode.query.filter_by(barcode=barcod).count()
elif request.form['barcode']:
barcod = request.form['barcode']
barcode = Barcode.query.filter_by(barcode=barcod).count()
if barcode == 0:
barcod = random.randint(10000000, 99999999 )
barcod = str(barcod)
barcode = Barcode.query.filter_by(barcode=barcod).count()
else:
barcod = request.form['barcode']
barcode = Barcode.query.filter_by(barcode=barcod).count()
if barcode == 0:
from bar_gen import Code128
code = str(barcod)
full_barcode = path_barcode
Code128(code).save(formats=['gif'],outDir=full_barcode, fnRoot=code )
barcode_add = Barcode(cartridge=request.form['cartridge'],customer=request.form['customer'],barcode=barcod)
session.add(barcode_add)
order = Orders(cartridge=request.form['cartridge'],customer=request.form['customer'],tow=request.form['tow'],barcode=barcod,mark=request.form['mark'],date=request.form['date'])
session.add(order)
logs = Logs(user=namesd, time=date_today, message='Добавлен заказ. Штрихкод: '+str(barcod))
session.add(logs)
session.commit()
session.flush()
flash ('Заказ успешно добавлен')
return redirect(url_for('orders'))
if request.form['action'] == 'Close':
id = request.form['id']
user_close = request.form['user_close']
mark = request.form['mark']
comment = request.form['comment']
delete = session.query(Orders).filter_by(id=id).first()
try:
toner = request.form['toner']
except:
toner = 0
try:
opc = request.form['opc']
except:
opc = 0
try:
pcr = request.form['pcr']
except:
pcr = 0
try:
wiper_blade = request.form['wiper_blade']
except:
wiper_blade = 0
try:
recovery_blade = request.form['recovery_blade']
except:
recovery_blade = 0
try:
develop_blade = request.form['develop_blade']
except:
develop_blade = 0
try:
doctor_blade = request.form['doctor_blade']
except:
doctor_blade = 0
add = Close_orders(cartridge=delete.cartridge,customer=delete.customer,toner=toner,opc=opc,pcr=pcr,
wiper_blade=wiper_blade,recovery_blade=recovery_blade,develop_blade=develop_blade,
doctor_blade=doctor_blade,barcode=delete.barcode,mark=mark,user_close=user_close,
date = delete.date,date_close = date_today,comment = comment)
session.add(add)
session.delete(delete)
logs = Logs(user=namesd, time=date_today, message='Закрыт заказ. Штрихкод: '+str(delete.barcode))
session.add(logs)
session.commit()
session.flush()
flash('Заказ успешно закрыт')
return redirect(url_for('orders'))
return render_template('orders.html',tows=tows, full=tmp_orders, user=user, fullnames = namesd)
def get_tag_object(name):
return session.query(Tag).filter(Tag.name==name).first()
#-*- coding: utf-8 -*-
from settings import session
from models import Pais
# p = Pais('México DF', 'México', 65, 'Peso')
# session.add(p)
# session.commit()
paises = session.query(Pais).all()
for pais in paises:
pais.continente = 'América'
session.commit()
def get_posts_by_user(username):
return session.query(Post).join(User).filter(User.username == username).all()
def get_user_object(name):
return session.query(User).filter(User.username==name).first()
def get_assessments():
query = session.query(Assessment.description, Assessment.title,
Assessment.category).all()
return formatResponse(query)
def get_questions():
query = session.query(Questions.questions_id, Questions.question).all()
return formatResponse(query)
def get_post_object(title):
return session.query(Post).filter(Post.title==title).first()
