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()