Esempio n. 1
0
    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))
Esempio n. 2
0
    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
Esempio n. 3
0
    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)
Esempio n. 4
0
    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)
Esempio n. 5
0
    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
Esempio n. 6
0
 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
Esempio n. 7
0
 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
Esempio n. 8
0
    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)
Esempio n. 9
0
 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
Esempio n. 10
0
 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
Esempio n. 11
0
    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))
Esempio n. 12
0
    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))
Esempio n. 13
0
 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)
Esempio n. 14
0
 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)
Esempio n. 15
0
    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)
Esempio n. 16
0
 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))
Esempio n. 17
0
 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))
Esempio n. 18
0
 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))
Esempio n. 19
0
 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))
Esempio n. 20
0
 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))
Esempio n. 21
0
    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)
Esempio n. 22
0
    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))
Esempio n. 23
0
 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))
Esempio n. 24
0
    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)
Esempio n. 25
0
    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)
Esempio n. 26
0
    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)
Esempio n. 27
0
    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)
Esempio n. 28
0
    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)
Esempio n. 29
0
 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'))
Esempio n. 30
0
 def get(self):
     items = session.query(Item)
     return flask.render_template('home.html', items=items)
Esempio n. 31
0
 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)
Esempio n. 32
0
 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'))
Esempio n. 33
0
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)
Esempio n. 34
0
 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'))
Esempio n. 35
0
    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]
Esempio n. 36
0
 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'))
Esempio n. 37
0
 def get(self):
     items = session.query(Item)
     return flask.render_template('home.html', items = items)
Esempio n. 38
0
 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)
Esempio n. 39
0
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)
)
Esempio n. 40
0
 def get(self, item_id):
     item = session.query(Item).get(item_id)
     return flask.render_template('item_history.html', item=item)
Esempio n. 41
0
 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'))
Esempio n. 42
0
 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'))
Esempio n. 43
0
 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)
Esempio n. 44
0
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)
Esempio n. 45
0
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]
Esempio n. 46
0
    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])
Esempio n. 47
0
 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)
Esempio n. 48
0
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]
Esempio n. 49
0
    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]
Esempio n. 50
0
    __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,
Esempio n. 51
0
 def get(self, item_id):
     item = session.query(Item).get(item_id)
     return flask.render_template('item_history.html', item=item)
Esempio n. 52
0
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()
Esempio n. 53
0
    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])
Esempio n. 54
0
    __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)
Esempio n. 55
0
 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)
Esempio n. 56
0
 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)