class Contract(GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name("Contract")
__table_args__ = (UniqueConstraint("client_id", "start_date",
"room_type"), )
id = Column(Integer, primary_key=True)
type = Column(String(50), nullable=False)
client_id = Column(Integer, GrandCedreBase.fk("Client"))
start_date = Column(Date)
end_date = Column(Date, nullable=True)
room_type = Column(SQLEnum(RoomTypeEnum))
pricing_id = Column(Integer, GrandCedreBase.fk("FlatRatePricing"))
recurring_pricing_id = Column(Integer,
GrandCedreBase.fk("RecurringPricing"))
total_hours = Column(String, nullable=True)
remaining_hours = Column(String, nullable=True)
weekly_hours = Column(Integer, nullable=True)
client = relationship("Client", back_populates="contracts")
invoices = relationship("Invoice", back_populates="contract")
flat_rate_pricing = relationship("FlatRatePricing",
back_populates="contracts")
recurring_pricing = relationship("RecurringPricing",
back_populates="contracts")
def __str__(self):
return f"{str(self.client)}: {self.start_date}: {self.type}:{self.room_type}"
def filename(self, extension="odt"):
return (f"contrat-{self.client.full_name.replace(' ', '-')}-"
f"{datetime.date.today()}.{extension}")
def to_odt(self, jinja_env, locale="fr_FR"):
today = datetime.date.today()
template_variables = {}
template_variables["contract"] = self
template_variables["locale_today"] = format_date(today,
"dd MMMM YYYY",
locale=locale)
with open(os.path.join(template_dir, "contract.xml.j2")) as template_f:
template = jinja_env.from_string(template_f.read())
contract_content = template.render(**template_variables)
contract_odt_copy_path = create_temporary_copy(
os.path.join(static_dir, "contract.odt"))
with zipfile.ZipFile(contract_odt_copy_path, mode="a") as contract_odt:
contract_odt.writestr("content.xml", contract_content)
with open(contract_odt_copy_path, mode="rb") as out_file:
out = out_file.read()
os.unlink(contract_odt_copy_path)
return out
class Client(GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name("Client")
__table_args__ = (UniqueConstraint("first_name", "last_name", "email"), )
id = Column(Integer, primary_key=True)
first_name = Column(String)
last_name = Column(String)
address = Column(String)
zip_code = Column(String)
city = Column(String)
email = Column(String, unique=True, nullable=False)
phone_number = Column(String, unique=True)
is_owner = Column(Boolean, default=False)
contracts = relationship("Contract", back_populates="client")
daily_bookings = relationship("DailyBooking", back_populates="client")
def __repr__(self):
return f"<{self.__class__.__name__}: {str(self)}: {self.email}>"
def __str__(self):
if not all((self.first_name, self.last_name)):
return self.email
return f"{self.first_name.strip()} {self.last_name.strip()}"
@property
def full_name(self):
return f"{self.first_name.strip()} {self.last_name.strip()}"
def missing_details(self):
return not all((self.first_name, self.last_name, self.address,
self.zip_code, self.city))
class Expense(GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name("Expense")
id = Column(Integer, primary_key=True)
date = Column(Date, nullable=False)
label = Column(String(255), nullable=False)
price = Column(String(8), nullable=False)
class Pricing(HourlyPricing, GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name("Pricing")
id = Column(Integer, primary_key=True)
type = Column(String(50),
nullable=False,
default=PricingType.individual_modular)
duration_from = Column(Integer, nullable=False)
duration_to = Column(Integer, nullable=True)
valid_from = Column(Date, nullable=False)
valid_to = Column(Date, nullable=True)
hourly_price = Column(String(8), nullable=False)
class RecurringPricing(MonthlyPricing, FreePricing, GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name("RecurringPricing")
id = Column(Integer, primary_key=True)
type = Column(String(50), nullable=False, default=PricingType.recurring)
duration_from = Column(Integer, nullable=False)
duration_to = Column(Integer, nullable=True)
valid_from = Column(Date, nullable=False)
valid_to = Column(Date, nullable=True)
monthly_price = Column(String(8), nullable=False)
contracts = relationship("Contract", back_populates="recurring_pricing")
class CollectiveRoomOccasionalPricing(HourlyPricing, GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name(
"CollectiveRoomOccasionalPricing")
id = Column(Integer, primary_key=True)
type = Column(String(50),
nullable=False,
default=PricingType.collective_occasional)
duration_from = Column(Integer, nullable=False)
duration_to = Column(Integer, nullable=True)
valid_from = Column(Date, nullable=False)
valid_to = Column(Date, nullable=True)
hourly_price = Column(String(8), nullable=False)
class FlatRatePricing(FreePricing, GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name("FlatRatePricing")
id = Column(Integer, primary_key=True)
type = Column(String(50), nullable=False, default=PricingType.flat_rate)
valid_from = Column(Date, nullable=False)
valid_to = Column(Date, nullable=True)
flat_rate = Column(String(8), nullable=False)
prepaid_hours = Column(Integer, nullable=False)
contracts = relationship("Contract", back_populates="flat_rate_pricing")
def __str__(self):
return f"{self.prepaid_hours}h - {self.flat_rate}€"
class Room(GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name("Room")
id = Column(Integer, primary_key=True)
name = Column(String, unique=True)
individual = Column(Boolean)
calendar_id = Column(String, unique=True)
def __str__(self):
return self.name
def __repr__(self):
return (
f"<{self.__class__.__name__}: {(str(self))}: individual:{self.individual}>"
)
class DailyBooking(GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name("DailyBooking")
__table_args__ = (UniqueConstraint("client_id", "date", "individual"), )
id = Column(Integer, primary_key=True)
client_id = Column(Integer, GrandCedreBase.fk("Client"))
invoice_id = Column(Integer, GrandCedreBase.fk("Invoice"))
date = Column(Date)
duration_hours = Column(String)
price = Column(String)
individual = Column(Boolean)
frozen = Column(Boolean, default=False)
client = relationship("Client", back_populates="daily_bookings")
invoice = relationship("Invoice", back_populates="daily_bookings")
def __str__(self):
return (
f"[{self.client}] - {self.date} - "
f"{'individual' if self.individual else 'collective'} {self.duration_hours}h"
)
def __repr__(self):
return f"<{self.__class__.__name__}: {(str(self))}: price:{self.price}>"
@property
def contract(self):
room_type = RoomType.individual if self.individual else RoomType.collective
contracts = [
contract for contract in self.client.contracts
if contract.room_type.name == room_type
]
if not contracts:
# raise error?
pass
return contracts[0]
def _update_flat_rate_contract_remaining_hours(connection,
booking,
delete=True):
contract_table = GrandCedreBase.get_table_name("Contract")
remaining_hours = connection.execute(
(f"SELECT remaining_hours FROM {contract_table} "
f"WHERE id={booking.contract.id}")).first()[0]
if delete:
remaining_hours = Decimal(remaining_hours) + Decimal(
booking.duration_hours)
logger.info(
(f"Updating {booking.client}'s flat rate contract to "
f"remaining_hours: {str(remaining_hours)}h after deletion "
f"of booking {booking}"))
else:
remaining_hours = Decimal(remaining_hours) - Decimal(
booking.duration_hours)
logger.info(
(f"Updating {booking.client}'s flat rate contract to "
f"remaining_hours: {str(remaining_hours)}h after creation "
f"of booking {booking}"))
connection.execute((f"UPDATE {contract_table} "
f"SET remaining_hours={str(remaining_hours)} "
f"WHERE id={booking.contract.id}"))
class BalanceSheet(GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name("BalanceSheet")
__table_args__ = (UniqueConstraint("start_date", "end_date"), )
id = Column(Integer, primary_key=True)
start_date = Column(Date, nullable=False)
end_date = Column(Date, nullable=False)
def __repr__(self):
return f"<{self.__class__.__name__}: {(str(self))}>"
def __str__(self):
return f"{self.start_date} - {self.end_date}"
def filename(self, extension="csv"):
return f"bilan-{self.start_date}-{self.end_date}.{extension}"
def to_csv(self, session):
csvfile = StringIO()
invoices = (session.query(Invoice).filter(
Invoice.payed_at.isnot(None)).filter(
Invoice.payed_at >= self.start_date).filter(
Invoice.payed_at <= self.end_date)).all()
invoice_total = sum([invoice.total_price for invoice in invoices])
csvwriter = csv.writer(csvfile)
invoice_headers = [
"# Facture",
"Client",
"Période",
"Total",
"Date d'encaissement",
"# Chèque",
"# Virement",
]
csvwriter.writerow(invoice_headers)
for invoice in invoices:
csvwriter.writerow([
invoice.number,
invoice.contract.client.full_name,
invoice.period,
str(invoice.total_price),
invoice.payed_at,
invoice.check_number or "",
invoice.wire_transfer_number or "",
])
for i in range(2):
csvwriter.writerow([] * len(invoice_headers))
expense_headers = ["Dépense", "Date", "Montant"]
expenses = (session.query(Expense).filter(
Expense.date >= self.start_date).filter(
Expense.date <= self.end_date)).all()
if expenses:
expense_total = sum([
Decimal(expense.price) for expense in expenses
]).quantize(Decimal("1.00"))
else:
expense_total = Decimal("0.00")
csvwriter.writerow(expense_headers)
for expense in expenses:
csvwriter.writerow([expense.label, expense.date, expense.price])
for i in range(2):
csvwriter.writerow([] * len(expense_headers))
csvwriter.writerow(["Total facturé", invoice_total])
csvwriter.writerow(["Total dépensé", expense_total])
csvwriter.writerow(["Total", str(invoice_total - expense_total)])
csvfile.flush()
csvfile.seek(0)
return csvfile.read()
class Invoice(GrandCedreBase):
__tablename__ = GrandCedreBase.get_table_name("Invoice")
__table_args__ = (UniqueConstraint("contract_id", "period"), )
id = Column(Integer, primary_key=True)
contract_id = Column(Integer, GrandCedreBase.fk("Contract"))
period = Column(String)
issued_at = Column(Date)
currency = Column(String, default="EURO")
payed_at = Column(Date)
check_number = Column(String)
wire_transfer_number = Column(String)
daily_bookings = relationship("DailyBooking", back_populates="invoice")
contract = relationship("Contract", back_populates="invoices")
def __str__(self):
return f"{self.contract.client.full_name} {self.period}: {self.total_price}{self.symbol}"
@staticmethod
def format_period(date=None):
date = date or datetime.date.today()
return f"{date.year}-{str(date.month).zfill(2)}"
@property
def symbol(self):
return SYMBOLS[self.currency]
@property
def total_price(self):
if self.contract.type == ContractType.flat_rate:
if self.contract.flat_rate_pricing:
return Decimal(
self.contract.flat_rate_pricing.flat_rate) * Decimal(
self.contract.flat_rate_pricing.prepaid_hours)
elif self.contract.type == ContractType.recurring:
if self.contract.recurring_pricing is not None:
return Decimal(self.contract.recurring_pricing.monthly_price)
return sum([Decimal(booking.price) for booking in self.daily_bookings])
@property
def number(self):
shortened_year = self.period.split("-")[0][2:]
return f"GC {str(self.id).zfill(3)}-{shortened_year}"
@property
def year(self):
return int(self.period.split("-")[0])
@property
def month(self):
return int(self.period.split("-")[1])
@property
def is_valid(self):
return (not self.contract.client.missing_details()
and self.total_price is not None)
@property
def filename(self):
return f"{str(self.contract.client).lower()}-{self.period}.pdf".replace(
" ", "-").replace(",", " ")
def to_html(self, jinja_env, locale="fr_FR"):
d = datetime.date(self.year, self.month, 1)
template_variables = {}
template_variables["invoice"] = self
template_variables["locale_month"] = format_date(d,
"MMMM",
locale=locale)
template_variables["locale_year"] = d.year
template_variables["locale_issue_date"] = format_date(self.issued_at,
"dd MMMM YYYY",
locale=locale)
with open(os.path.join(template_dir, "invoice.html.j2")) as template_f:
template = jinja_env.from_string(template_f.read())
invoice_content = template.render(**template_variables)
return invoice_content
def to_pdf(self, jinja_env, locale="fr_FR"):
with tempfile.NamedTemporaryFile(mode="w",
encoding="utf-8",
suffix=".html",
dir=output_dir) as f:
html = self.to_html(jinja_env, locale)
f.write(html)
f.flush()
pdf = HTML(f.name).write_pdf()
return pdf
def to_mailto_link(self):
params = {}
params["title"] = f"Votre facture du Grand Cèdre de {self.period}"
with open(os.path.join(template_dir,
"invoice-email.j2")) as template_f:
template = Template(template_f.read())
params["body"] = template.render(invoice=self)
return f"mailto:{self.contract.client.email}?{urlencode(params)}"