class Ticket(Base): __tablename__ = 'ticket' id = db.Column(db.Integer(), primary_key=True) date = db.Column(db.Date()) updated = db.Column(db.DateTime(timezone=True)) um = db.Column(db.String(10)) cost = db.Column(db.Float(scale=17, precision=3)) total = db.Column(db.Float(scale=17, precision=3)) total_tax_paid = db.Column(db.Float(scale=16, precision=2)) total_no_tax = total - total_tax_paid project_task_id = db.Column(db.Integer()) project_task_name = db.Column(db.String(200)) user_id = db.Column(db.Integer()) project_id = db.Column(db.Integer()) project_name = db.Column(db.String(200)) currency = db.Column(db.String(3)) city = db.Column(db.String(75)) quantity = db.Column(db.Float(scale=12, precision=2)) acct_date = db.Column(db.Date()) @staticmethod def get_my_tickets(user_email): query = '''SELECT DISTINCT t.id, t.date, t.updated, t.um, t.cost, t.total, t.total_tax_paid, t.project_task_id, pt.name AS project_task_name, t.user_id, t.project_id, p.name AS project_name, t.currency, t.city, t.quantity, t.acct_date FROM ticket t INNER JOIN envelope e ON t.envelope_id = e.id INNER JOIN project p ON e.project_id = p.id LEFT JOIN project_task pt ON t.project_task_id = t.id LEFT JOIN user u ON t.user_id=u.id WHERE u.email = :email OR p.id IN (SELECT DISTINCT p2.id FROM project p2 LEFT JOIN project_task pt2 ON pt2.project_id = p2.id LEFT JOIN project_task_assign pta ON pta.project_task_id = pt2.id LEFT JOIN booking b ON b.project_id = p2.id LEFT JOIN user u ON (p2.user_id = u.id OR pta.user_id = u.id OR b.user_id = u.id) WHERE u.email = :email AND p2.active="1");''' return Ticket.query.from_statement( text(query)).params(email=user_email).all() def __repr__(self): return '<Ticket %r>' % self.id
class Booking(Base): __tablename__ = 'booking' id = db.Column(db.Integer(), primary_key=True) owner_id = db.Column(db.Integer()) user_id = db.Column(db.Integer()) project_id = db.Column(db.Integer()) project_name = db.Column(db.String(200)) startdate = db.Column(db.Date()) enddate = db.Column(db.Date()) percentage = db.Column(db.Float(scale=12, precision=2)) hours = db.Column(db.Float(scale=12, precision=2)) project_task_id = db.Column(db.Integer()) project_task_name = db.Column(db.String(200)) as_percentage = db.Column(db.String(1)) approval_status = db.Column(db.String(1)) @staticmethod def get_my_bookings(user_email): query = '''SELECT DISTINCT b.id, b.owner_id, b.user_id, b.project_id, p.name AS "project_name", b.startdate, b.enddate, b.percentage, b.hours, b.project_task_id, pt.name AS "project_task_name", b.as_percentage, b.approval_status FROM booking b INNER JOIN project_task pt ON b.project_task_id = pt.id INNER JOIN project p ON b.project_id = p.id LEFT JOIN user u ON b.user_id = u.id WHERE b.approval_status = "A" AND (u.email = :email OR p.id IN (SELECT DISTINCT p2.id FROM project p2 LEFT JOIN project_task pt2 ON pt2.project_id = p2.id LEFT JOIN project_task_assign pta ON pta.project_task_id = pt2.id LEFT JOIN booking b ON b.project_id = p2.id LEFT JOIN user u ON (p2.user_id = u.id OR pta.user_id = u.id OR b.user_id = u.id) WHERE u.email = :email AND p2.active="1"));''' return Booking.query.from_statement( text(query)).params(email=user_email).all() def __repr__(self): return '<Booking %r>' % self.id
class Daily(Base): # Multiple binds: http://flask-sqlalchemy.pocoo.org/2.1/binds/ __bind_key__ = 'dailies' __tablename__ = 'timesheets_vs_bookings_daily' id = db.Column(db.Integer(), primary_key=True) timesheets_id = db.Column(db.Integer()) bookings_daily_id = db.Column(db.Integer()) associate = db.Column(db.String(200)) practice = db.Column(db.String(200)) client_name = db.Column(db.String(200)) project_name = db.Column(db.String(200)) task_name = db.Column(db.String(200)) date = db.Column(db.Date()) week_of_booking = db.Column(db.Integer()) week_of_year_iso = db.Column(db.String(3)) booking_hours = db.Column(db.Float(scale=12, precision=8)) booking_fees = db.Column(db.Float(scale=12, precision=2)) timesheet_hours = db.Column(db.Float(scale=12, precision=2)) associate_currency = db.Column(db.String(3)) @staticmethod def get_dailies(project_name, task_name, associate): query = '''SELECT DISTINCT id, timesheets_id, bookings_daily_id, associate, practice, client_name, project_name, task_name, date, week_of_booking, week_of_year_iso, booking_hours booking_fees, timesheet_hours, associate_currency FROM timesheets_vs_bookings_daily WHERE project_name=:project_name AND task_name=:task_name AND associate=:associate ORDER BY date ASC''' return Daily.query.from_statement(text(query)).params( project_name=project_name, task_name=task_name, associate=associate).all() def __repr__(self): return '<Daily %r>' % self.id
class Task(Base): __tablename__ = 'task' id = db.Column(db.Integer(), primary_key=True) project_id = db.Column(db.Integer()) project_name = db.Column(db.String(200)) project_task_id = db.Column(db.Integer()) project_task_name = db.Column(db.String(200)) user_id = db.Column(db.Integer()) date = db.Column(db.Date()) updated = db.Column(db.DateTime(timezone=True)) hour = db.Column(db.Float(scale=12, precision=2)) minute = db.Column(db.Float(scale=6, precision=0)) timesheet_id = db.Column(db.Integer()) cost_center_id = db.Column(db.Integer()) decimal_hours = hour + minute / 60.00 @staticmethod def get_my_tasks(user_email): query = '''SELECT DISTINCT t.id, t.project_id, p.name AS project_name, t.project_task_id, pt.name AS project_task_name, t.user_id, t.date, t.updated, t.hour, t.minute, t.timesheet_id, t.cost_center_id FROM task t INNER JOIN project_task pt ON t.project_task_id = pt.id INNER JOIN project p ON pt.project_id = p.id LEFT JOIN user u ON t.user_id = u.id WHERE u.email = :email OR p.id IN (SELECT DISTINCT p2.id FROM project p2 LEFT JOIN project_task pt2 ON pt2.project_id = p2.id LEFT JOIN project_task_assign pta ON pta.project_task_id = pt2.id LEFT JOIN booking b ON b.project_id = p2.id LEFT JOIN user u ON (p2.user_id = u.id OR pta.user_id = u.id OR b.user_id = u.id) WHERE u.email = :email AND p2.active="1");''' return Task.query.from_statement( text(query)).params(email=user_email).all() def __repr__(self): return '<Task %r>' % self.id
class Rate(Base): __tablename__ = 'up_rate' id = db.Column(db.Integer, primary_key=True) project_id = db.Column(db.Integer) project_name = db.Column(db.String(200)) user_id = db.Column(db.Integer) user_name = db.Column(db.String(200)) rate = db.Column(db.Float(scale=16, precision=2)) currency = db.Column(db.String(3)) @staticmethod def get_all_rates(): query = '''SELECT ur.id, ur.project_id, p.name AS project_name, ur.user_id, u.name AS user_name, ur.rate AS rate, ur.currency FROM up_rate ur LEFT JOIN user u ON ur.user_id = u.id LEFT JOIN project p ON ur.project_id = p.id WHERE ur.user_id IS NOT NULL;''' return Rate.query.from_statement(text(query)).all() def __repr__(self): return '<Rate %r>' % self.id
class Referal(db.Model, BaseModel): # table name __tablename__ = 'referals' # visible fields visible = ['id', 'owner', 'discount_amount', 'referal_code', 'created_at', 'updated_at'] # columns definitions id = db.Column(db.Integer, primary_key=True) owner = db.Column(db.String) discount_amount = db.Column(db.Float(precision=2)) referal_code = db.Column(db.String) created_at = db.Column(db.DateTime) updated_at = db.Column(db.DateTime) def __init__(self): self.created_at = datetime.datetime.now() self.updated_at = datetime.datetime.now()