def query(self, query_params_dict):
"""
Retrieve the exports we want to export
"""
query = Task.query().with_polymorphic([Invoice, CancelInvoice])
query = self._filter_valid(query)
if 'start_date' in query_params_dict:
start_date = query_params_dict['start_date']
end_date = query_params_dict['end_date']
query = self._filter_date(query, start_date, end_date)
elif 'official_number' in query_params_dict:
official_number = query_params_dict['official_number']
financial_year = query_params_dict['financial_year']
query = self._filter_number(query,
official_number,
financial_year,
strict=True)
elif 'start_official_number' in query_params_dict:
official_number = query_params_dict['start_official_number']
financial_year = query_params_dict['financial_year']
query = self._filter_number(query,
official_number,
financial_year,
strict=False)
if not 'exported' in query_params_dict or \
not query_params_dict.get('exported'):
query = query.filter(
or_(Invoice.exported == False,
CancelInvoice.exported == False))
return query
def refresh_cache(arguments, env):
logger = logging.getLogger(__name__)
if not arguments['refresh']:
logger.exception(u"Unknown error")
logger.debug(u"Refreshing cache")
session = db()
index = 0
types = get_value(arguments, '--type')
if types is None:
types = ['invoice', 'estimation', 'cancelinvoice']
this_year = datetime.date.today().year
for task in Task.query().filter(Task.type_.in_(types)).filter(
extract('year', Task.date) == this_year):
try:
cache_amounts(None, None, task)
session.merge(task)
index += 1
if index % 200 == 0:
logger.debug('flushing')
session.flush()
except:
logger.exception(u"Error while caching total : {0}".format(
task.id))
def refresh_cache(arguments, env):
logger = logging.getLogger(__name__)
if not arguments['refresh']:
logger.exception(u"Unknown error")
logger.debug(u"Refreshing cache")
session = db()
index = 0
types = get_value(arguments, '--type')
if types is None:
types = ['invoice', 'estimation', 'cancelinvoice']
this_year = datetime.date.today().year
for task in Task.query().filter(
Task.type_.in_(types)
).filter(extract('year', Task.date) == this_year):
try:
cache_amounts(None, None, task)
session.merge(task)
index += 1
if index % 200 == 0:
logger.debug('flushing')
session.flush()
except:
logger.exception(u"Error while caching total : {0}".format(task.id))
def add_internal_number(session, logger):
logger.warn("Adding internal_number to Task")
NUMBER_TMPLS = {
'estimation': u"{s.project.code}_{s.customer.code}_D{s.project_index}\
_{s.date:%m%y}",
'invoice': u"{s.project.code}_{s.customer.code}_F{s.project_index}\
_{s.date:%m%y}",
'cancelinvoice': u"{s.project.code}_{s.customer.code}_A{s.project_index}\
_{s.date:%m%y}"
}
from autonomie.models.task import Task
from autonomie.models.customer import Customer
from autonomie.models.project import Project
from autonomie.models.project import Phase
from sqlalchemy.orm import joinedload
from sqlalchemy.orm import load_only
query = Task.query().options(
load_only("project_index", "company_index", "date", "phase_id", 'type_')
)
query = query.filter(
Task.type_.in_(['invoice', 'estimation', 'cancelinvoice'])
)
query = query.options(joinedload(Task.customer).load_only(Customer.code))
query = query.options(joinedload(Task.project).load_only(Project.code))
for task in query:
tmpl = NUMBER_TMPLS[task.type_]
if Phase.get(task.phase_id) is None:
session.delete(task)
else:
task.internal_number = tmpl.format(s=task).upper()
session.merge(task)
def add_internal_number(session, logger):
logger.warn("Adding internal_number to Task")
NUMBER_TMPLS = {
'estimation': u"{s.project.code}_{s.customer.code}_D{s.project_index}\
_{s.date:%m%y}",
'invoice': u"{s.project.code}_{s.customer.code}_F{s.project_index}\
_{s.date:%m%y}",
'cancelinvoice': u"{s.project.code}_{s.customer.code}_A{s.project_index}\
_{s.date:%m%y}"
}
from autonomie.models.task import Task
from autonomie.models.customer import Customer
from autonomie.models.project import Project
from autonomie.models.project import Phase
from sqlalchemy.orm import joinedload
from sqlalchemy.orm import load_only
query = Task.query().options(
load_only("project_index", "company_index", "date", "phase_id", 'type_')
)
query = query.filter(
Task.type_.in_(['invoice', 'estimation', 'cancelinvoice'])
)
query = query.options(joinedload(Task.customer).load_only(Customer.code))
query = query.options(joinedload(Task.project).load_only(Project.code))
for task in query:
tmpl = NUMBER_TMPLS[task.type_]
if Phase.get(task.phase_id) is None:
session.delete(task)
else:
task.internal_number = tmpl.format(s=task).upper()
session.merge(task)
def export_task_totals(arguments, env):
action = get_value(arguments, 'a')
if action == "check":
check_totals(arguments, env)
return
print(arguments)
logger = logging.getLogger(__name__)
filename = get_value(arguments, 'f')
if filename:
print("Generating a csv filename %s" % filename)
gen_csv(filename)
if action == 'cache':
print(u"Caching amounts")
session = db()
index = 0
for task in Task.query().filter(
Task.type_.in_(['invoice', 'estimation', 'cancelinvoice'])
):
try:
cache_amounts(None, None, task)
session.merge(task)
index += 1
if index % 50 == 0:
print('flushing')
session.flush()
except:
logger.exception(
u"Erreur avec un cache_amount : %s" % task.id
)
def query(self, query_params_dict):
"""
Retrieve the exports we want to export
"""
query = Task.query().with_polymorphic([Invoice, CancelInvoice])
query = self._filter_valid(query)
if 'start_date' in query_params_dict:
start_date = query_params_dict['start_date']
end_date = query_params_dict['end_date']
query = self._filter_date(query, start_date, end_date)
elif 'official_number' in query_params_dict:
official_number = query_params_dict['official_number']
financial_year = query_params_dict['financial_year']
query = self._filter_number(query, official_number, financial_year,
strict=True)
elif 'start_official_number' in query_params_dict:
official_number = query_params_dict['start_official_number']
financial_year = query_params_dict['financial_year']
query = self._filter_number(query, official_number, financial_year,
strict=False)
if not 'exported' in query_params_dict or \
not query_params_dict.get('exported'):
query = query.filter(or_(Invoice.exported == False,
CancelInvoice.exported == False))
return query
def export_task_totals(arguments, env):
action = get_value(arguments, 'a')
if action == "check":
check_totals(arguments, env)
return
print(arguments)
logger = logging.getLogger(__name__)
filename = get_value(arguments, 'f')
if filename:
print("Generating a csv filename %s" % filename)
gen_csv(filename)
if action == 'cache':
print(u"Caching amounts")
session = db()
index = 0
for task in Task.query().filter(
Task.type_.in_(['invoice', 'estimation', 'cancelinvoice'])):
try:
cache_amounts(None, None, task)
session.merge(task)
index += 1
if index % 50 == 0:
print('flushing')
session.flush()
except:
logger.exception(u"Erreur avec un cache_amount : %s" % task.id)
def query(self):
query = Task.query()
query = query.with_polymorphic([Invoice, CancelInvoice])
query = query.outerjoin(Task.project)
query = query.outerjoin(Project.company)
query = query.outerjoin(Task.customer)
query = query.outerjoin(Task.payments)
if self.request.context == 'company':
company_id = self.request.context.id
query = query.filter(Project.company_id == company_id)
return query
def query(self):
query = Task.query()
query = query.with_polymorphic([Invoice, CancelInvoice])
query = query.outerjoin(Task.project)
query = query.outerjoin(Project.company)
query = query.outerjoin(Task.customer)
query = query.outerjoin(Task.payments)
if self.request.context == 'company':
company_id = self.request.context.id
query = query.filter(Project.company_id==company_id)
return query
def query_documents_for_export(from_number, to_number, year):
"""
Query the database to retrieve the documents for the pdf export
"""
# querying the database
query = Task.query().with_polymorphic([Invoice, CancelInvoice])
query = query.filter(Task.official_number >= from_number)
# Default provided in the form schema is -1
if to_number > 0:
query = query.filter(Task.official_number <= to_number)
query = query.filter(or_(Invoice.financial_year == year, CancelInvoice.financial_year == year))
records = query.order_by(Task.official_number).all()
return records
def upgrade():
from autonomie.models.task import (
TaskLine,
TaskLineGroup,
Task,
Estimation,
CancelInvoice,
Invoice,
)
from autonomie_base.models.base import (
DBSESSION,
)
session = DBSESSION()
index = 0
query = Task.query()
query = query.with_polymorphic([Invoice, CancelInvoice, Estimation])
query = query.filter(
Task.type_.in_(['invoice', 'estimation', 'cancelinvoice'])
)
for task in query:
group = TaskLineGroup(task_id=task.id, order=0)
for line in task.lines:
tline = TaskLine(
group=group,
order=line.rowIndex,
description=line.description,
cost=line.cost,
tva=line.tva,
quantity=line.quantity,
)
if hasattr(line, 'product_id'):
tline.product_id = line.product_id
session.add(tline)
if index % 100 == 0:
session.flush()
op.alter_column(
table_name='estimation_payment',
column_name='rowIndex',
new_column_name='order',
type_=sa.Integer,
)
def upgrade():
from autonomie.models.task import (
TaskLine,
TaskLineGroup,
Task,
Estimation,
CancelInvoice,
Invoice,
)
from autonomie.models.base import (
DBSESSION,
)
session = DBSESSION()
index = 0
query = Task.query()
query = query.with_polymorphic([Invoice, CancelInvoice, Estimation])
query = query.filter(
Task.type_.in_(['invoice', 'estimation', 'cancelinvoice'])
)
for task in query:
group = TaskLineGroup(task_id=task.id, order=0)
for line in task.lines:
tline = TaskLine(
group=group,
order=line.rowIndex,
description=line.description,
cost=line.cost,
tva=line.tva,
quantity=line.quantity,
)
if hasattr(line, 'product_id'):
tline.product_id = line.product_id
session.add(tline)
if index % 100 == 0:
session.flush()
op.alter_column(
table_name='estimation_payment',
column_name='rowIndex',
new_column_name='order',
type_=sa.Integer,
)
def add_company_index(session, logger):
logger.warn("Adding company_index to Task")
from autonomie.models.company import Company
from autonomie.models.task import Task
for datas in session.query(Company.id):
query = Task.query()
query = query.options(
sa.orm.load_only('id', 'company_id', "company_index", 'type_'))
query = query.filter(Task.company_id == datas[0])
for type_ in ('estimation', 'invoice', 'cancelinvoice'):
index = 1
typequery = query.filter(Task.type_ == type_)
for task in typequery:
task.company_index = index
index += 1
session.merge(task)
def _company_tasks_query(company_id):
"""
Build sqlalchemy query to all tasks of a company, in reverse statusDate
order.
"""
query = Task.query()
query = query.with_polymorphic([Invoice, Estimation, CancelInvoice])
query = query.order_by(desc(Task.statusDate))
query = query.outerjoin(_p1, Invoice.project)
query = query.outerjoin(_p2, Estimation.project)
query = query.outerjoin(_p3, CancelInvoice.project)
return query.filter(
or_(_p1.company_id == company_id, _p2.company_id == company_id,
_p3.company_id == company_id))
def get_tasks(financial_year, from_number):
from autonomie.models.task import Task, Invoice, CancelInvoice
from autonomie.models.project import Project
from sqlalchemy import or_
query = Task.query()
query = query.with_polymorphic([Invoice, CancelInvoice])
query = query.outerjoin(Task.project)
query = query.outerjoin(Project.company)
query = query.outerjoin(Task.customer)
query = query.filter(Task.status == 'valid')
query = query.filter(
or_(
Invoice.financial_year == financial_year,
CancelInvoice.financial_year == financial_year,
))
query = query.filter(Task.official_number >= from_number)
return query
def add_company_index(session, logger):
logger.warn("Adding company_index to Task")
from autonomie.models.company import Company
from autonomie.models.task import Task
for datas in session.query(Company.id):
query = Task.query()
query = query.options(
sa.orm.load_only('id', 'company_id', "company_index", 'type_')
)
query = query.filter(Task.company_id == datas[0])
for type_ in ('estimation', 'invoice', 'cancelinvoice'):
index = 1
typequery = query.filter(Task.type_ == type_)
for task in typequery:
task.company_index = index
index += 1
session.merge(task)
def assign_number(cls, invoice, template):
"""
This function should be run within an SQL transaction to enforce
sequence index unicity.
"""
if invoice.official_number:
raise ValueError('This invoice already have an official number')
db = DBSESSION()
formatter = InvoiceNumberFormatter(invoice, cls.SEQUENCES_MAP)
invoice_number = formatter.format(template)
involved_sequences = cls.get_involved_sequences(invoice, template)
with db.begin_nested():
# Create SequenceNumber objects (the index useages have not been
# booked until now).
for sequence, next_index in involved_sequences:
sn = SequenceNumber(
sequence=sequence.db_key,
index=next_index,
task_id=invoice.id,
)
db.add(sn)
invoice.official_number = invoice_number
db.merge(invoice)
# Imported here to avoid circular dependencies
from autonomie.models.task import Task, Invoice, CancelInvoice
query = Task.query().with_polymorphic([Invoice, CancelInvoice])
query = query.filter(
Task.official_number == invoice_number,
Task.id != invoice.id,
Task.legacy_number == False,
).scalar()
if query is not None:
# This case is exceptionnal, we can afford a crash here
# Context manager will take care of rolling back
# subtransaction.
raise ValueError(
'Invoice number collision, rolling back to avoid it'
)
return invoice_number
def query_documents_for_export(from_number, to_number, year):
"""
Query the database to retrieve the documents for the pdf export
"""
# querying the database
query = Task.query().with_polymorphic([Invoice, CancelInvoice])
query = query.filter(Task.official_number >= from_number)
# Default provided in the form schema is -1
if to_number > 0:
query = query.filter(Task.official_number <= to_number)
query = query.filter(
or_(
Invoice.financial_year == year,
CancelInvoice.financial_year == year,
))
records = query.order_by(Task.official_number).all()
return records
def get_tasks(financial_year, from_number):
from autonomie.models.task import Task, Invoice, CancelInvoice
from autonomie.models.project import Project
from sqlalchemy import or_
query = Task.query()
query = query.with_polymorphic([Invoice, CancelInvoice])
query = query.outerjoin(Task.project)
query = query.outerjoin(Project.company)
query = query.outerjoin(Task.customer)
query = query.filter(Task.status == 'valid')
query = query.filter(
or_(
Invoice.financial_year == financial_year,
CancelInvoice.financial_year == financial_year,
)
)
query = query.filter(Task.official_number >= from_number)
return query
def _company_tasks_query(company_id):
"""
Build sqlalchemy query to all tasks of a company, in reverse statusDate
order.
"""
query = Task.query()
query = query.with_polymorphic([Invoice, Estimation, CancelInvoice])
query = query.order_by(desc(Task.statusDate))
query = query.outerjoin(_p1, Invoice.project)
query = query.outerjoin(_p2, Estimation.project)
query = query.outerjoin(_p3, CancelInvoice.project)
return query.filter(or_(
_p1.company_id==company_id,
_p2.company_id==company_id,
_p3.company_id==company_id
))
def upgrade():
from autonomie.models.task import (
TaskLine,
TaskLineGroup,
Task,
Estimation,
CancelInvoice,
Invoice,
)
from autonomie.models.base import (
DBSESSION,
)
session = DBSESSION()
index = 0
query = Task.query()
query = query.with_polymorphic([Invoice, CancelInvoice, Estimation])
query = query.filter(
Task.type_.in_(['invoice', 'estimation', 'cancelinvoice'])
)
for task in query:
try:
task_lines = task.default_line_group.lines
except:
continue
for index, line in enumerate(task.lines):
try:
task_line = task_lines[index]
task_line.unity = line.unity
session.merge(task_line)
except:
pass
index += 1
if index % 100 == 0:
session.flush()
def upgrade():
from autonomie.models.task import (
TaskLine,
TaskLineGroup,
Task,
Estimation,
CancelInvoice,
Invoice,
)
from autonomie_base.models.base import (
DBSESSION,
)
session = DBSESSION()
index = 0
query = Task.query()
query = query.with_polymorphic([Invoice, CancelInvoice, Estimation])
query = query.filter(
Task.type_.in_(['invoice', 'estimation', 'cancelinvoice'])
)
for task in query:
try:
task_lines = task.default_line_group.lines
except:
continue
for index, line in enumerate(task.lines):
try:
task_line = task_lines[index]
task_line.unity = line.unity
session.merge(task_line)
except:
pass
index += 1
if index % 100 == 0:
session.flush()