def post(self, item_id):
if item_id == None or flask.request.form[
'vendor'] == "" or flask.request.form['quantity'] == "":
return ""
item = session.query(Item).get(item_id)
vendor = session.query(Vendor).get(flask.request.form['vendor'])
lot = session.query(Lot).get(flask.request.form['lot'])
itemSnapshot = getNextSnapshot(item)
# Lot
lot.state = flask.request.form['lot_list']
itemSnapshot.lot = lot
# Vendors
if int(flask.request.form['vendor']) == int(
item.snapshots[0].primary_vendor.id):
itemSnapshot.primary_vendor_q = flask.request.form['quantity']
else:
itemSnapshot.secondary_vendor_q = flask.request.form['quantity']
item.snapshots.append(itemSnapshot)
session.commit()
return flask.redirect(flask.url_for('item', item_id=item_id))
def createItem(self):
if self.validateSubmission() != 0:
return None
item = Item()
itemSnapshot = ItemSnapshot(flask.request.form['name'],
flask.request.form['num'],
flask.request.form['quantity'],
flask.request.form['reorder_quantity'],
flask.request.form['reorder_point'])
primary_vendor = session.query(Vendor).get(flask.request.form['primary_vendor'])
itemSnapshot.primary_vendor_p = flask.request.form['primary_vendor_p']
itemSnapshot.primary_vendor = primary_vendor
if (flask.request.form['secondary_vendor'] != ""):
secondary_vendor = session.query(Vendor).get(flask.request.form['secondary_vendor'])
itemSnapshot.secondary_vendor = secondary_vendor
if (flask.request.form['secondary_vendor_p'] != ""):
itemSnapshot.secondary_vendor_p = flask.request.form['secondary_vendor_p']
item.snapshots.append(itemSnapshot)
session.add(item)
session.commit()
return item
def get(self, report_id):
item_list = []
filename = "test.csv"
if report_id == 0:
# Reorder Report
items = session.query(Item)
items = [x for x in items if x.snapshots[0].quantity_on_hand <= x.snapshots[0].reorder_point]
item_list = items
filename = "reorder_report.csv"
elif report_id == 1:
# Item Report
items = session.query(Item)
item_list = items
filename = "item_report.csv"
if flask.request.args["export"] and flask.request.args["export"] == "t":
f = csv.writer(open('csv/'+filename, 'wb+'))
f.writerow(['Name', 'Catalog Number', 'Quantity On Hand', 'Reorder Point', 'Primary Vendor', 'Secondary Vendor'])
for item in item_list:
sv = item.snapshots[0].secondary_vendor
svn = ""
if sv:
svn = sv.name
f.writerow([item.snapshots[0].name,
item.snapshots[0].num,
item.snapshots[0].quantity_on_hand,
item.snapshots[0].reorder_quantity,
item.snapshots[0].primary_vendor.name,
svn])
return open('csv/'+filename, 'rb+')
return flask.render_template('reports.html', items=items)
def get(self, vendor_id):
if vendor_id == None:
vendor_filter = request.args.get("q")
if not vendor_filter:
vendors = session.query(Vendor)
else:
vendors = session.query(Vendor).filter(
Vendor.name.contains(vendor_filter))
vendor_list = []
for vendor in vendors:
vendor_dict = {}
vendor_dict["id"] = str(vendor.id)
vendor_dict["text"] = vendor.name
vendor_list.append(vendor_dict)
return flask.jsonify(results=vendor_list)
else:
vendor = session.query(Vendor).get(vendor_id)
vendor_list = []
vendor_dict = {}
vendor_dict["id"] = str(vendor.id)
vendor_dict["text"] = vendor.name
vendor_list.append(vendor_dict)
return flask.jsonify(results=vendor_list)
def createItem(self):
if self.validateSubmission() != 0:
return None
item = Item()
itemSnapshot = ItemSnapshot(flask.request.form['name'],
flask.request.form['num'],
flask.request.form['quantity'],
flask.request.form['reorder_quantity'],
flask.request.form['reorder_point'])
primary_vendor = session.query(Vendor).get(
flask.request.form['primary_vendor'])
itemSnapshot.primary_vendor_p = flask.request.form['primary_vendor_p']
itemSnapshot.primary_vendor = primary_vendor
if (flask.request.form['secondary_vendor'] != ""):
secondary_vendor = session.query(Vendor).get(
flask.request.form['secondary_vendor'])
itemSnapshot.secondary_vendor = secondary_vendor
if (flask.request.form['secondary_vendor_p'] != ""):
itemSnapshot.secondary_vendor_p = flask.request.form[
'secondary_vendor_p']
item.snapshots.append(itemSnapshot)
session.add(item)
session.commit()
return item
def editLot(self, lot_id):
if self.validateSubmission() != 0:
return None
lot = session.query(Lot).get(lot_id)
lot.name = flask.request.form['name']
session.commit()
return lot
def editVendor(self, vendor_id):
if self.validateSubmission() != 0:
return None
vendor = session.query(Vendor).get(vendor_id)
vendor.name = flask.request.form['name']
session.commit()
return vendor
def post(self):
search_name = flask.request.form['search_name'].strip()
search_cat = flask.request.form['search_catalog_num'].strip()
search_vendor = flask.request.form['search_vendor'].strip()
search_q_from = flask.request.form['search_quantity_from'].strip()
search_q_to = flask.request.form['search_quantity_to'].strip()
items = session.query(Item)
if search_name:
items = [x for x in items if search_name in x.snapshots[0].name]
if search_cat:
items = [x for x in items if search_cat in x.snapshots[0].num]
if search_vendor:
items = [
x for x in items
if int(search_vendor) == x.snapshots[0].primary_vendor.id
]
if search_q_from:
items = [
x for x in items
if int(search_q_from) <= x.snapshots[0].quantity_on_hand
]
if search_q_to:
items = [
x for x in items
if int(search_q_to) >= x.snapshots[0].quantity_on_hand
]
return flask.render_template('home.html', items=items)
def editLot(self, lot_id):
if self.validateSubmission() != 0:
return None
lot = session.query(Lot).get(lot_id)
lot.name = flask.request.form['name']
session.commit()
return lot
def editVendor(self, vendor_id):
if self.validateSubmission() != 0:
return None
vendor = session.query(Vendor).get(vendor_id)
vendor.name = flask.request.form['name']
session.commit()
return vendor
def post(self, item_id):
if item_id == None or flask.request.form['quantity'] == "":
return flask.render_template('index.html')
item = session.query(Item).get(item_id)
itemSnapshot = getNextSnapshot(item)
itemSnapshot.quantity_on_hand = flask.request.form['quantity']
item.snapshots.append(itemSnapshot)
session.commit()
return flask.redirect(flask.url_for('item', item_id=item_id))
def post(self, item_id):
if item_id == None or flask.request.form['quantity'] == "":
return flask.render_template('index.html')
item = session.query(Item).get(item_id)
itemSnapshot = getNextSnapshot(item)
itemSnapshot.quantity_on_hand = flask.request.form['quantity']
item.snapshots.append(itemSnapshot)
session.commit()
return flask.redirect(flask.url_for('item', item_id=item_id))
def get(self, lot_id):
if lot_id == None:
lot_filter = request.args.get("q")
if not lot_filter:
lots = session.query(Lot)
else:
lots = session.query(Lot).filter(Lot.name.contains(lot_filter))
lot_list = []
for lot in lots:
lot_dict = {}
lot_dict["id"] = str(lot.id)
lot_dict["text"] = lot.name
lot_list.append(lot_dict)
return flask.jsonify(results=lot_list)
else:
lot = session.query(Lot).get(lot_id)
lot_list = []
lot_dict = {}
lot_dict["id"] = str(lot.id)
lot_dict["text"] = lot.name
lot_list.append(lot_dict)
return flask.jsonify(results=lot_list)
def get(self, lot_id):
if lot_id == None:
lot_filter = request.args.get("q")
if not lot_filter:
lots = session.query(Lot)
else:
lots = session.query(Lot).filter(Lot.name.contains(lot_filter))
lot_list = []
for lot in lots:
lot_dict = {}
lot_dict["id"] = str(lot.id)
lot_dict["text"] = lot.name
lot_list.append(lot_dict)
return flask.jsonify(results=lot_list)
else:
lot = session.query(Lot).get(lot_id)
lot_list = []
lot_dict = {}
lot_dict["id"] = str(lot.id)
lot_dict["text"] = lot.name
lot_list.append(lot_dict)
return flask.jsonify(results=lot_list)
def get(self, report_id):
item_list = []
filename = "test.csv"
if report_id == 0:
# Reorder Report
items = session.query(Item)
items = [
x for x in items if
x.snapshots[0].quantity_on_hand <= x.snapshots[0].reorder_point
]
item_list = items
filename = "reorder_report.csv"
elif report_id == 1:
# Item Report
items = session.query(Item)
item_list = items
filename = "item_report.csv"
if flask.request.args["export"] and flask.request.args["export"] == "t":
f = csv.writer(open('csv/' + filename, 'wb+'))
f.writerow([
'Name', 'Catalog Number', 'Quantity On Hand', 'Reorder Point',
'Primary Vendor', 'Secondary Vendor'
])
for item in item_list:
sv = item.snapshots[0].secondary_vendor
svn = ""
if sv:
svn = sv.name
f.writerow([
item.snapshots[0].name, item.snapshots[0].num,
item.snapshots[0].quantity_on_hand,
item.snapshots[0].reorder_quantity,
item.snapshots[0].primary_vendor.name, svn
])
return open('csv/' + filename, 'rb+')
return flask.render_template('reports.html', items=items)
def post(self, vendor_id):
if vendor_id == None:
vendor = self.createVendor()
if vendor == None:
return flask.redirect(flask.url_for('vendor_mod'))
else:
return flask.redirect(flask.url_for('vendor', vendor_id=vendor.id))
else:
vendor = self.editVendor(vendor_id)
if vendor == None:
vendor = session.query(Vendor).get(vendor_id)
return flask.redirect(flask.url_for('vendor_mod', vendor_id=vendor.id))
else:
return flask.redirect(flask.url_for('vendor', vendor_id=vendor.id))
def post(self, item_id):
if item_id == None:
item = self.createItem()
if item == None:
return flask.redirect(flask.url_for('item_mod'))
else:
return flask.redirect(flask.url_for('item', item_id=item_id))
else:
item = self.editItem(item_id)
if item == None:
item = session.query(Item).get(item_id)
return flask.redirect(flask.url_for('item_mod', item_id=item.id))
else:
return flask.redirect(flask.url_for('item', item_id=item.id))
def post(self, lot_id):
if lot_id == None:
lot = self.createLot()
if lot == None:
return flask.redirect(flask.url_for('lot_mod'))
else:
return flask.redirect(flask.url_for('lot', lot_id=lot.id))
else:
lot = self.editLot(lot_id)
if lot == None:
lot = session.query(Lot).get(lot_id)
return flask.redirect(flask.url_for('lot_mod', lot_id=lot.id))
else:
return flask.redirect(flask.url_for('lot', lot_id=lot.id))
def post(self, lot_id):
if lot_id == None:
lot = self.createLot()
if lot == None:
return flask.redirect(flask.url_for('lot_mod'))
else:
return flask.redirect(flask.url_for('lot', lot_id=lot.id))
else:
lot = self.editLot(lot_id)
if lot == None:
lot = session.query(Lot).get(lot_id)
return flask.redirect(flask.url_for('lot_mod', lot_id=lot.id))
else:
return flask.redirect(flask.url_for('lot', lot_id=lot.id))
def post(self, item_id):
if item_id == None:
item = self.createItem()
if item == None:
return flask.redirect(flask.url_for('item_mod'))
else:
return flask.redirect(flask.url_for('item', item_id=item_id))
else:
item = self.editItem(item_id)
if item == None:
item = session.query(Item).get(item_id)
return flask.redirect(
flask.url_for('item_mod', item_id=item.id))
else:
return flask.redirect(flask.url_for('item', item_id=item.id))
def get(self, vendor_id):
if vendor_id == None:
vendor_filter = request.args.get("q")
if not vendor_filter:
vendors = session.query(Vendor)
else:
vendors = session.query(Vendor).filter(Vendor.name.contains(vendor_filter))
vendor_list = []
for vendor in vendors:
vendor_dict = {}
vendor_dict["id"] = str(vendor.id)
vendor_dict["text"] = vendor.name
vendor_list.append(vendor_dict)
return flask.jsonify(results=vendor_list)
else:
vendor = session.query(Vendor).get(vendor_id)
vendor_list = []
vendor_dict = {}
vendor_dict["id"] = str(vendor.id)
vendor_dict["text"] = vendor.name
vendor_list.append(vendor_dict)
return flask.jsonify(results=vendor_list)
def post(self, item_id):
if item_id == None or flask.request.form['vendor'] == "" or flask.request.form['quantity'] == "":
return ""
item = session.query(Item).get(item_id)
vendor = session.query(Vendor).get(flask.request.form['vendor'])
lot = session.query(Lot).get(flask.request.form['lot'])
itemSnapshot = getNextSnapshot(item)
# Lot
lot.state = flask.request.form['lot_list']
itemSnapshot.lot = lot
# Vendors
if int(flask.request.form['vendor']) == int(item.snapshots[0].primary_vendor.id):
itemSnapshot.primary_vendor_q = flask.request.form['quantity']
else:
itemSnapshot.secondary_vendor_q = flask.request.form['quantity']
item.snapshots.append(itemSnapshot)
session.commit()
return flask.redirect(flask.url_for('item', item_id=item_id))
def post(self, vendor_id):
if vendor_id == None:
vendor = self.createVendor()
if vendor == None:
return flask.redirect(flask.url_for('vendor_mod'))
else:
return flask.redirect(
flask.url_for('vendor', vendor_id=vendor.id))
else:
vendor = self.editVendor(vendor_id)
if vendor == None:
vendor = session.query(Vendor).get(vendor_id)
return flask.redirect(
flask.url_for('vendor_mod', vendor_id=vendor.id))
else:
return flask.redirect(
flask.url_for('vendor', vendor_id=vendor.id))
def get(self, item_id):
if item_id == None:
return ""
quantity_list = []
date_list = []
item = session.query(Item).get(item_id)
uniqueQuantity = -1
for snap in reversed(item.snapshots):
if snap.quantity_on_hand != uniqueQuantity:
uniqueQuantity = snap.quantity_on_hand
quantity_list.append(snap.quantity_on_hand)
date_list.append(snap.timestamp.strftime('%b %d'))
data = getQuantityData(item, quantity_list)
return flask.jsonify(data=data, dates=date_list)
def get(self, item_id):
if item_id == None:
return ""
quantity_list = []
date_list = []
item = session.query(Item).get(item_id)
uniqueQuantity = -1
for snap in reversed(item.snapshots):
if snap.quantity_on_hand != uniqueQuantity:
uniqueQuantity = snap.quantity_on_hand
quantity_list.append(snap.quantity_on_hand)
date_list.append(snap.timestamp.strftime('%b %d'))
data = getQuantityData(item, quantity_list)
return flask.jsonify(data=data, dates=date_list)
def get(self, item_id):
item = session.query(Item).get(item_id)
vendor_list = []
p_vendor_dict = {}
s_vendor_dict = {}
# Primary
vendor = item.snapshots[0].primary_vendor
if vendor != None:
p_vendor_dict["id"] = vendor.id
p_vendor_dict["text"] = vendor.name
vendor_list.append(p_vendor_dict)
# Secondary
vendor = item.snapshots[0].secondary_vendor
if vendor != None:
s_vendor_dict["id"] = vendor.id
s_vendor_dict["text"] = vendor.name
vendor_list.append(s_vendor_dict)
return flask.jsonify(results=vendor_list)
def get(self, item_id):
item = session.query(Item).get(item_id)
vendor_list = []
p_vendor_dict = {}
s_vendor_dict = {}
# Primary
vendor = item.snapshots[0].primary_vendor
if vendor != None:
p_vendor_dict["id"] = vendor.id
p_vendor_dict["text"] = vendor.name
vendor_list.append(p_vendor_dict)
# Secondary
vendor = item.snapshots[0].secondary_vendor
if vendor != None:
s_vendor_dict["id"] = vendor.id
s_vendor_dict["text"] = vendor.name
vendor_list.append(s_vendor_dict)
return flask.jsonify(results=vendor_list)
def post(self):
search_name = flask.request.form['search_name'].strip()
search_cat = flask.request.form['search_catalog_num'].strip()
search_vendor = flask.request.form['search_vendor'].strip()
search_q_from = flask.request.form['search_quantity_from'].strip()
search_q_to = flask.request.form['search_quantity_to'].strip()
items = session.query(Item)
if search_name:
items = [x for x in items if search_name in x.snapshots[0].name]
if search_cat:
items = [x for x in items if search_cat in x.snapshots[0].num]
if search_vendor:
items = [x for x in items if int(search_vendor) == x.snapshots[0].primary_vendor.id]
if search_q_from:
items = [x for x in items if int(search_q_from) <= x.snapshots[0].quantity_on_hand]
if search_q_to:
items = [x for x in items if int(search_q_to) >= x.snapshots[0].quantity_on_hand]
return flask.render_template('home.html', items = items)
def get(self, lot_id):
if lot_id != None:
lot = session.query(Lot).get(lot_id)
return flask.render_template('lot.html', lot=lot)
else:
return flask.redirect(flask.url_for('index'))
def get(self):
items = session.query(Item)
return flask.render_template('home.html', items=items)
def get(self, item_id):
if item_id != None:
item = session.query(Item).get(item_id)
else:
item = None
return flask.render_template('new_item.html', item=item)
def get(self, item_id):
if item_id != None:
item = session.query(Item).get(item_id)
return flask.render_template('item.html', item=item)
else:
return flask.redirect(flask.url_for('index'))
from sqlalchemy import Column, Integer, String, func, case, and_
from sqlalchemy.orm import aliased, Query
from util import Base, session
class Project(Base):
__tablename__ = "projects"
workorder_id = Column(String(5), primary_key=True)
step_nbr = Column(Integer, nullable=False, primary_key=True)
step_status = Column(String(1), nullable=False)
print(session.query(Project).all())
# 素直に解く
work_order = session.query(Project.workorder_id).group_by(Project.workorder_id)\
.having(func.count() == func.sum(case(
[
(and_(Project.step_status == "C", Project.step_nbr == 0), 1),
(and_(Project.step_status == "W", Project.step_nbr != 0), 1)
],
else_=0
)))
print(work_order.all())
# TODO また自己参照
# 0以外はみんな待ち
"""
project1 = aliased(Project)
def get(self, lot_id):
if lot_id != None:
lot = session.query(Lot).get(lot_id)
return flask.render_template('lot.html', lot=lot)
else:
return flask.redirect(flask.url_for('index'))
seat = Column(Integer, primary_key=True)
# 空きの最初と最後を求めれば、間は空席
#TODO next_last_seatのfirst_seatが相関にならなくてエラー。後で治す
"""
first_seat = Query([Restaurant.seat.label("seat")]).filter(
not_(Restaurant.seat.in_(
Query([Restaurant.seat - 1]).subquery()
))
).subquery()
last_seat = Query([Restaurant.seat.label("seat")]).filter(
not_(Restaurant.seat.in_(
Query([Restaurant.seat + 1]).subquery()
))
).subquery()
next_last_seat = Query([func.min(last_seat.c.seat)]).filter(first_seat.c.seat + 1 <= last_seat.c.seat - 1).as_scalar()
seat = session.query(first_seat, last_seat).filter(last_seat.c.seat - 1 == next_last_seat)
[print(s) for s in seat]
"""
# 自己結合により、次の席を求める
first = aliased(Restaurant)
last = aliased(Restaurant)
seat = session.query(first.seat + 1, func.min(last.seat)).join(last, first.seat < last.seat)\
.group_by(first.seat).having(first.seat < func.min(last.seat - 1))
[print(s) for s in seat]
def get(self, vendor_id):
if vendor_id != None:
vendor = session.query(Vendor).get(vendor_id)
return flask.render_template('vendor.html', vendor=vendor)
else:
return flask.redirect(flask.url_for('index'))
def get(self):
items = session.query(Item)
return flask.render_template('home.html', items = items)
def get(self, vendor_id):
if vendor_id != None:
vendor = session.query(Vendor).get(vendor_id)
else:
vendor = None
return flask.render_template('new_vendor.html', vendor=vendor)
class Succession(Base):
__tablename__ = "succession"
chain = Column(Integer, nullable=False)
next = Column(Integer, nullable=False)
file_id = Column(Integer, ForeignKey(Portfolio.file_id))
suc_date = Column(Date, nullable=False)
__table_args__ = (PrimaryKeyConstraint("chain", "next"),)
# あるバージョンの先行を追跡できること
# 1
next_succession = aliased(Succession)
nexts = session.query(Succession, next_succession).join(
next_succession, and_(Succession.next + 1 == next_succession.next, Succession.chain == next_succession.chain)
)
[print(s.chain, s.file_id, n_s.file_id) for s, n_s in nexts]
# 2
nexts = session.query(Succession, next_succession).filter(
and_(Succession.next + 1 == next_succession.next, Succession.chain == next_succession.chain)
)
[print(s.chain, s.file_id, n_s.file_id) for s, n_s in nexts]
# あるバージョンの後続を追跡できること
prev_succession = aliased(Succession)
prevs = session.query(Succession, prev_succession).filter(
and_(Succession.next == prev_succession.next + 1, Succession.chain == prev_succession.chain)
)
def get(self, item_id):
item = session.query(Item).get(item_id)
return flask.render_template('item_history.html', item=item)
def get(self, item_id):
if item_id != None:
item = session.query(Item).get(item_id)
return flask.render_template('item.html', item=item)
else:
return flask.redirect(flask.url_for('index'))
def get(self, vendor_id):
if vendor_id != None:
vendor = session.query(Vendor).get(vendor_id)
return flask.render_template('vendor.html', vendor=vendor)
else:
return flask.redirect(flask.url_for('index'))
def get(self, vendor_id):
if vendor_id != None:
vendor = session.query(Vendor).get(vendor_id)
else:
vendor = None
return flask.render_template('new_vendor.html', vendor=vendor)
from util import Base, session
class Items(Base):
__tablename__ = "items"
item_nbr = Column(Integer, primary_key=True)
item_descr = Column(String)
class Estimates(Base):
__tablename__ = "estimates"
item_nbr = Column(Integer, primary_key=True)
estimated_amt = Column(Float)
class Actuals(Base):
__tablename__ = "actuals"
item_nbr = Column(Integer, primary_key=True)
actual_amt = Column(Float)
check_nbr = Column(String)
actual_sum = Query(func.sum(Actuals.actual_amt)).filter(Actuals.item_nbr == Items.item_nbr).as_scalar()
estimate_sum = Query(func.sum(Estimates.estimated_amt)).filter(Estimates.item_nbr == Items.item_nbr).as_scalar()
check_number = Query([case([(func.count(Actuals.item_nbr) == 1, func.max(Actuals.check_nbr))], else_="Mixed")])\
.filter(Actuals.item_nbr == Items.item_nbr).group_by(Actuals.item_nbr).as_scalar()
print("\r\nresult*****")
result = session.query(Items, actual_sum, estimate_sum, check_number).filter(or_(actual_sum != None, estimate_sum != None)).all()
print(result)
from sqlalchemy.orm import Query
from sqlalchemy.sql.functions import coalesce
from util import Base, session
class PrinterControl(Base):
__tablename__ = "printercontrol"
user_id = Column(String(10))
printer_name = Column(String(4), nullable=False, primary_key=True)
printer_description = Column(String(40), nullable=False)
user_name = "leea"
user_printer = Query([PrinterControl]).filter(PrinterControl.user_id == user_name)
s = session.query(PrinterControl).filter(case(
[(user_printer.exists(), PrinterControl.user_id == user_name)],
else_=(PrinterControl.user_id == None))
)
[print(s.printer_name) for s in s]
# 集計関数がNULLを返すことを利用する
anonymous_printer = Query([func.min(PrinterControl.printer_name)])\
.filter(PrinterControl.user_id == None).as_scalar()
s = session.query(coalesce(func.min(PrinterControl.printer_name), anonymous_printer))\
.filter(PrinterControl.user_id == user_name)
[print(s) for s in s]
badge_nbr = Column(Integer, primary_key=True)
emp_id = Column(Integer, nullable=False)
issued_date = Column(Date, nullable=False)
badge_seq = Column(Integer)
__table_args__ = (
UniqueConstraint('badge_seq'),
)
# JOIN型
available_badges_query = aliased(
Query([Badges.emp_id, func.max(Badges.issued_date).label("issued_date")]).group_by(Badges.emp_id).subquery()
)
available_badges = session.query(Badges).join(
available_badges_query,
and_(
Badges.emp_id == available_badges_query.c.emp_id,
Badges.issued_date == available_badges_query.c.issued_date,
)
).all()
print([(b.emp_id, b.issued_date) for b in available_badges])
# 相関サブクエリ
active_badges = aliased(Badges)
max_badge_seq = Query([func.max(active_badges.badge_seq)]).filter(active_badges.emp_id == Badges.emp_id)
available_badges = session.query(Badges).filter(Badges.badge_seq == max_badge_seq)
print([(b.emp_id, b.issued_date) for b in available_badges])
def get(self, lot_id):
if lot_id != None:
lot = session.query(Lot).get(lot_id)
else:
lot = None
return flask.render_template('new_lot.html', lot=lot)
from sqlalchemy.orm import Query
from sqlalchemy.sql.functions import coalesce
from util import Base, session
class PrinterControl(Base):
__tablename__ = "printercontrol"
user_id = Column(String(10))
printer_name = Column(String(4), nullable=False, primary_key=True)
printer_description = Column(String(40), nullable=False)
user_name = "leea"
user_printer = Query([PrinterControl
]).filter(PrinterControl.user_id == user_name)
s = session.query(PrinterControl).filter(
case([(user_printer.exists(), PrinterControl.user_id == user_name)],
else_=(PrinterControl.user_id == None)))
[print(s.printer_name) for s in s]
# 集計関数がNULLを返すことを利用する
anonymous_printer = Query([func.min(PrinterControl.printer_name)])\
.filter(PrinterControl.user_id == None).as_scalar()
s = session.query(coalesce(func.min(PrinterControl.printer_name), anonymous_printer))\
.filter(PrinterControl.user_id == user_name)
[print(s) for s in s]
seat = Column(Integer, primary_key=True)
# 空きの最初と最後を求めれば、間は空席
#TODO next_last_seatのfirst_seatが相関にならなくてエラー。後で治す
"""
first_seat = Query([Restaurant.seat.label("seat")]).filter(
not_(Restaurant.seat.in_(
Query([Restaurant.seat - 1]).subquery()
))
).subquery()
last_seat = Query([Restaurant.seat.label("seat")]).filter(
not_(Restaurant.seat.in_(
Query([Restaurant.seat + 1]).subquery()
))
).subquery()
next_last_seat = Query([func.min(last_seat.c.seat)]).filter(first_seat.c.seat + 1 <= last_seat.c.seat - 1).as_scalar()
seat = session.query(first_seat, last_seat).filter(last_seat.c.seat - 1 == next_last_seat)
[print(s) for s in seat]
"""
# 自己結合により、次の席を求める
first = aliased(Restaurant)
last = aliased(Restaurant)
seat = session.query(first.seat + 1, func.min(last.seat)).join(last, first.seat < last.seat)\
.group_by(first.seat).having(first.seat < func.min(last.seat - 1))
[print(s) for s in seat]
__tablename__ = "succession"
chain = Column(Integer, nullable=False)
next = Column(Integer, nullable=False)
file_id = Column(Integer, ForeignKey(Portfolio.file_id))
suc_date = Column(Date, nullable=False)
__table_args__ = (PrimaryKeyConstraint('chain', 'next'), )
# あるバージョンの先行を追跡できること
# 1
next_succession = aliased(Succession)
nexts = session.query(Succession, next_succession)\
.join(next_succession, and_(
Succession.next + 1 == next_succession.next,
Succession.chain == next_succession.chain)
)
[print(s.chain, s.file_id, n_s.file_id) for s, n_s in nexts]
# 2
nexts = session.query(Succession, next_succession).filter(
and_(Succession.next + 1 == next_succession.next,
Succession.chain == next_succession.chain))
[print(s.chain, s.file_id, n_s.file_id) for s, n_s in nexts]
# あるバージョンの後続を追跡できること
prev_succession = aliased(Succession)
prevs = session.query(Succession, prev_succession).filter(
and_(Succession.next == prev_succession.next + 1,
def get(self, item_id):
item = session.query(Item).get(item_id)
return flask.render_template('item_history.html', item=item)
from sqlalchemy import Column, Integer, String, func, case, and_
from sqlalchemy.orm import aliased, Query
from util import Base, session
class Project(Base):
__tablename__ = "projects"
workorder_id = Column(String(5), primary_key=True)
step_nbr = Column(Integer, nullable=False, primary_key=True)
step_status = Column(String(1), nullable=False)
print(session.query(Project).all())
# 素直に解く
work_order = session.query(Project.workorder_id).group_by(Project.workorder_id)\
.having(func.count() == func.sum(case(
[
(and_(Project.step_status == "C", Project.step_nbr == 0), 1),
(and_(Project.step_status == "W", Project.step_nbr != 0), 1)
],
else_=0
)))
print(work_order.all())
# TODO また自己参照
# 0以外はみんな待ち
"""
project1 = aliased(Project)
project2 = Query([project1.step_status]).filter(project1.step_status == Project.step_status).subquery()
emp_id = Column(Integer, nullable=False)
issued_date = Column(Date, nullable=False)
badge_seq = Column(Integer)
__table_args__ = (UniqueConstraint('badge_seq'), )
# JOIN型
available_badges_query = aliased(
Query([Badges.emp_id,
func.max(Badges.issued_date).label("issued_date")
]).group_by(Badges.emp_id).subquery())
available_badges = session.query(Badges).join(
available_badges_query,
and_(
Badges.emp_id == available_badges_query.c.emp_id,
Badges.issued_date == available_badges_query.c.issued_date,
)).all()
print([(b.emp_id, b.issued_date) for b in available_badges])
# 相関サブクエリ
active_badges = aliased(Badges)
max_badge_seq = Query([func.max(active_badges.badge_seq)
]).filter(active_badges.emp_id == Badges.emp_id)
available_badges = session.query(Badges).filter(
Badges.badge_seq == max_badge_seq)
print([(b.emp_id, b.issued_date) for b in available_badges])
__tablename__ = "items"
item_nbr = Column(Integer, primary_key=True)
item_descr = Column(String)
class Estimates(Base):
__tablename__ = "estimates"
item_nbr = Column(Integer, primary_key=True)
estimated_amt = Column(Float)
class Actuals(Base):
__tablename__ = "actuals"
item_nbr = Column(Integer, primary_key=True)
actual_amt = Column(Float)
check_nbr = Column(String)
actual_sum = Query(func.sum(Actuals.actual_amt)).filter(
Actuals.item_nbr == Items.item_nbr).as_scalar()
estimate_sum = Query(func.sum(Estimates.estimated_amt)).filter(
Estimates.item_nbr == Items.item_nbr).as_scalar()
check_number = Query([case([(func.count(Actuals.item_nbr) == 1, func.max(Actuals.check_nbr))], else_="Mixed")])\
.filter(Actuals.item_nbr == Items.item_nbr).group_by(Actuals.item_nbr).as_scalar()
print("\r\nresult*****")
result = session.query(Items, actual_sum, estimate_sum, check_number).filter(
or_(actual_sum != None, estimate_sum != None)).all()
print(result)
def get(self, item_id):
if item_id != None:
item = session.query(Item).get(item_id)
else:
item = None
return flask.render_template('new_item.html', item=item)
def get(self, lot_id):
if lot_id != None:
lot = session.query(Lot).get(lot_id)
else:
lot = None
return flask.render_template('new_lot.html', lot=lot)