def create_companypermit_rows(request):
# --- create list of all userpermits of this company PR2021-10-02
logging_on = False # s.LOGGING_ON
if logging_on:
logger.debug(' ')
logger.debug(' ----- create_companypermit_rows -----')
sql_keys = {'comp_pk': request.user.company.pk}
sql_list = [
"SELECT cp.id, CONCAT('companypermit', cp.id) AS mapid, cp.company_id,",
"pa.page, pa.action, cp.usergroups,",
"cp.modifiedat AS modat, COALESCE(SUBSTRING (au.username, 7), '') AS modby_usr",
"FROM accounts_companypermit AS cp",
"INNER JOIN accounts_permitaction AS pa ON (pa.id = cp.permitaction_id)",
"LEFT JOIN accounts_user AS au ON (au.id = cp.modifiedby_id)",
"WHERE cp.company_id = %(comp_pk)s::INT", "ORDER BY cp.id"
]
sql = ' '.join(sql_list)
if logging_on:
logger.debug('sql: ' + str(sql))
logger.debug('connection.queries: ' + str(connection.queries))
with connection.cursor() as cursor:
cursor.execute(sql, sql_keys)
companypermit_rows = f.dictfetchall(cursor)
if logging_on:
for row in companypermit_rows:
logger.debug('companypermit_row: ' + str(row))
return companypermit_rows
def create_usergroup_rows(user_lang, request):
# --- create list of all usergroups of this company PR2021-09-22
logging_on = False # s.LOGGING_ON
if logging_on:
logger.debug(' ')
logger.debug(' ----- create_usergroup_rows -----')
code_lang = 'en' if user_lang == 'en' else 'nl'
sql_keys = {'comp_pk': request.user.company.pk, 'lang': user_lang}
sql_list = [
"SELECT ug.id, ug.company_id, CONCAT('usergroup_', ug.id) AS mapid,",
"ug.code_" + code_lang +
" AS code, ug.isdefault, ug.isdefault, ug.issysadmin, ug.inactive,"
"ug.modifiedat AS modat, COALESCE(SUBSTRING (au.username, 7), '') AS modby_usr",
"FROM accounts_usergroup AS ug",
"LEFT JOIN accounts_user AS au ON (au.id = ug.modifiedby_id)",
"WHERE ug.company_id = %(comp_pk)s::INT", "ORDER BY ug.id"
]
sql = ' '.join(sql_list)
if logging_on:
logger.debug('sql: ' + str(sql))
logger.debug('connection.queries: ' + str(connection.queries))
with connection.cursor() as cursor:
cursor.execute(sql, sql_keys)
usergroup_rows = f.dictfetchall(cursor)
if logging_on:
for row in usergroup_rows:
logger.debug('row: ' + str(row))
return usergroup_rows
def create_user_rows(request, user_pk=None):
# --- create list of all users of this company, or 1 user with user_pk PR2020-07-31 PR2021-09-24
#logger.debug(' =============== create_user_rows ============= ')
# PR2018-05-27 list of users in UserListView:
# - show only users of this company
# - only when permit is sysadmin
if request.user.company and request.user.is_perm_sysadmin:
company_pk = request.user.company.pk
sql_keys = {'compid': company_pk, 'userid': user_pk}
sql_list = [
"SELECT au.id, au.company_id, CONCAT('user_', au.id) AS mapid, 'user' AS table,",
"SUBSTRING(au.username, 7) AS username,",
"au.last_name, au.email, au.role, au.permits, au.usergroups, au.allowed_customers, au.allowed_orders,",
"(TRUNC(au.permits / 64) = 1) AS perm64_sysadmin, ",
"(TRUNC( MOD(au.permits, 64) / 32) = 1) AS perm32_accman, ",
"(TRUNC( MOD(au.permits, 32) / 16) = 1) AS perm16_hrman, ",
"(TRUNC( MOD(au.permits, 16) / 8) = 1) AS perm08_planner, ",
"(TRUNC( MOD(au.permits, 8) / 4) = 1) AS perm04_supervisor, ",
"(TRUNC( MOD(au.permits, 4) / 2) = 1) AS perm02_employee, ",
"(MOD(au.permits, 2) = 1) AS perm01_readonly, ",
"au.activated, au.activatedat, au.is_active, au.last_login, au.date_joined,",
"au.employee_id, e.code AS employee_code, au.lang, au.modifiedby_id, au.modifiedat",
"FROM accounts_user AS au",
"LEFT JOIN companies_employee AS e ON (e.id = au.employee_id)",
"WHERE ( au.company_id = %(compid)s::INT )",
"AND ( au.id = %(userid)s::INT OR %(userid)s IS NULL )",
"ORDER BY au.id"
]
sql = ' '.join(sql_list)
with connection.cursor() as cursor:
cursor.execute(sql, sql_keys)
user_rows = f.dictfetchall(cursor)
return user_rows
def create_billing_detail_list(period_dict, request):
#logger.debug(' ============= create_billing_detail_list ============= ')
period_datefirst, period_datelast, order_pk, customer_pk = None, None, None, None
if period_dict:
period_datefirst = period_dict.get('period_datefirst')
period_datelast = period_dict.get('period_datelast')
order_pk = period_dict.get('order_pk')
order_pk = order_pk if order_pk else None # this changes '0' into 'None'
if order_pk is None:
customer_pk = period_dict.get('customer_pk')
customer_pk = customer_pk if customer_pk else None # this changes '0' into 'None'
sql_keys = {'compid': request.user.company_id}
sql_list = [
"SELECT oh.id AS oh_id, o.id AS o_id, ",
"ARRAY_AGG(DISTINCT eh.employeecode ORDER BY eh.employeecode) AS e_code,",
"oh.rosterdate AS oh_rosterdate,",
"CONCAT(oh.customercode, ' - ', oh.ordercode) AS c_o_code,",
"oh.shiftcode AS oh_shiftcode,",
"SUM(eh.plannedduration) AS eh_plandur, ",
"SUM(eh.timeduration) AS eh_timedur, ",
"SUM(eh.billingduration) AS eh_billdur,",
"ARRAY_AGG(DISTINCT eh.pricerate ORDER BY eh.pricerate) AS eh_pricerate_arr,",
"ARRAY_AGG(DISTINCT oh.additionrate ORDER BY oh.additionrate) AS oh_additionrate_arr,",
"ARRAY_AGG(DISTINCT oh.taxrate ORDER BY oh.taxrate) AS oh_taxrate_arr,",
"SUM(eh.amount) AS eh_amount_sum, SUM(eh.addition) AS eh_add_sum,",
"SUM(eh.amount) + SUM(eh.addition) AS eh_total_sum, SUM(eh.tax) AS eh_tax_sum,",
"SUM(oh.isbillable::int) AS is_billable,",
"SUM((NOT oh.isbillable)::int) AS not_billable,",
"SUM(oh.nobill::int) AS is_nobill,",
"SUM((NOT oh.nobill)::int) AS not_nobill",
"FROM companies_emplhour AS eh",
"INNER JOIN companies_orderhour AS oh ON (oh.id = eh.orderhour_id)",
"INNER JOIN companies_order AS o ON (o.id = oh.order_id)",
"INNER JOIN companies_customer AS c ON (c.id = o.customer_id)",
"WHERE c.company_id = %(compid)s AND NOT c.isabsence AND NOT oh.isrestshift"
]
if period_datefirst:
sql_keys['df'] = period_datefirst
sql_list.append("AND eh.rosterdate >= %(df)s::DATE")
if period_datelast:
sql_keys['dl'] = period_datelast
sql_list.append("AND eh.rosterdate <= %(dl)s::DATE")
if order_pk:
sql_keys['oid'] = order_pk
sql_list.append("AND o.id = %(oid)s::INT")
if customer_pk:
sql_keys['cid'] = customer_pk
sql_list.append("AND c.id = %(cid)s::INT")
sql_list.append("GROUP BY oh.id, o.id, c.id, oh.rosterdate, oh.shiftcode")
sql_list.append("ORDER BY LOWER(oh.customercode), LOWER(oh.ordercode), oh.rosterdate, LOWER(oh.shiftcode)")
sql = ' '.join(sql_list)
with connection.cursor() as cursor:
cursor.execute(sql, sql_keys)
billing_detail_list = f.dictfetchall(cursor)
return billing_detail_list
def create_billing_agg_list(period_dict, request):
#logger.debug(' ============= create_billing_agg_list ============= ')
# create crosstab list of worked- and absence hours, of 1 paydateitem, grouped by employee PR2020-06-24
# see https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html
# see https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905
#logger.debug('period_dict: ' + str(period_dict))
period_datefirst, period_datelast, order_pk, customer_pk = None, None, None, None
if period_dict:
period_datefirst = period_dict.get('period_datefirst')
period_datelast = period_dict.get('period_datelast')
order_pk = period_dict.get('order_pk')
order_pk = order_pk if order_pk else None # this changes '0' into 'None'
if order_pk is None:
customer_pk = period_dict.get('customer_pk')
customer_pk = customer_pk if customer_pk else None # this changes '0' into 'None'
#logger.debug('period_datefirst: ' + str(period_datefirst))
#logger.debug('period_datelast: ' + str(period_datelast))
#logger.debug('order_pk: ' + str(order_pk))
sql_keys = {'compid': request.user.company_id}
sql_list = ["SELECT o.id AS o_id, COALESCE(oh.customercode,'-') AS c_code, COALESCE(oh.ordercode,'-') AS o_code,",
"SUM(eh.plannedduration) AS eh_plandur, SUM(eh.timeduration) AS eh_timedur, SUM(eh.billingduration) AS eh_billdur,",
"ARRAY_AGG(DISTINCT eh.pricerate ORDER BY eh.pricerate) AS eh_pricerate_arr,",
"ARRAY_AGG(DISTINCT oh.additionrate ORDER BY oh.additionrate) AS oh_additionrate_arr,",
"ARRAY_AGG(DISTINCT oh.taxrate ORDER BY oh.taxrate) AS oh_taxrate_arr,",
"SUM(eh.amount) AS eh_amount_sum, SUM(eh.addition) AS eh_add_sum,",
"SUM(eh.amount) + SUM(eh.addition) AS eh_total_sum, SUM(eh.tax) AS eh_tax_sum,",
"SUM(oh.isbillable::int) AS is_billable,",
"SUM((NOT oh.isbillable)::int) AS not_billable,",
"SUM(oh.nobill::int) AS is_nobill,",
"SUM((NOT oh.nobill)::int) AS not_nobill",
"FROM companies_emplhour AS eh",
"INNER JOIN companies_orderhour AS oh ON (oh.id = eh.orderhour_id)",
"INNER JOIN companies_order AS o ON (o.id = oh.order_id)",
"INNER JOIN companies_customer AS c ON (c.id = o.customer_id)",
"WHERE c.company_id = %(compid)s AND NOT c.isabsence AND NOT oh.isrestshift"
]
if period_datefirst:
sql_keys['df'] = period_datefirst
sql_list.append("AND eh.rosterdate >= %(df)s::DATE")
if period_datelast:
sql_keys['dl'] = period_datelast
sql_list.append("AND eh.rosterdate <= %(dl)s::DATE")
if order_pk:
sql_keys['oid'] = order_pk
sql_list.append("AND o.id = %(oid)s::INT")
if customer_pk:
sql_keys['cid'] = customer_pk
sql_list.append("AND c.id = %(cid)s::INT")
sql_list.append("GROUP BY o.id, oh.customercode, oh.ordercode")
sql_list.append("ORDER BY LOWER(oh.customercode), LOWER(oh.ordercode)")
sql = ' '.join(sql_list)
with connection.cursor() as cursor:
cursor.execute(sql, sql_keys)
billing_agg_list = f.dictfetchall(cursor)
return billing_agg_list
def create_order_rowsXXX(request, is_absence, is_inactive=None, order_pk=None):
# --- create list of all active absence categories of this company PR2019-06-25
# each absence category contains abscat_customer, abscat_order
# abscat_order_rows is used in employee page,
# PR2021-03-21 also used in page customer - to get orders
# TODO in scheme page replace abscat_order_list by abscat_order_rows
# Note: order_id must not have alias: 'id' is used in refresh_datamap
#logger.debug('inactive: ' + str(inactive))
sql_keys = {'compid': request.user.company.pk}
sql_list = ["SELECT o.id, c.id AS c_id, c.company_id AS comp_id,",
"CONCAT(CASE WHEN c.isabsence THEN 'abscat_' ELSE 'order_' END, o.id::TEXT) AS mapid,",
"COALESCE(REPLACE (o.code, '~', ''),'') AS o_code_notilde, COALESCE(REPLACE (c.code, '~', ''),'') AS c_code_notilde,",
"c.code AS c_code, c.name AS c_name, c.identifier AS c_identifier, c.inactive AS c_inactive, c.isabsence AS c_isabsence,",
"o.code AS o_code, o.name AS o_name, o.identifier AS o_identifier, o.sequence AS o_sequence, o.inactive AS o_inactive,",
"o.datefirst AS o_datefirst, o.datelast AS o_datelast,",
"o.wagefactorcode_id AS wfc_onwd, o.wagefactoronsat_id AS wfc_onsat,",
"o.wagefactoronsun_id AS wfc_onsun, o.wagefactoronph_id AS wfc_onph,",
"wfc_onwd.code AS wfc_onwd_code, wfc_onsat.code AS wfc_onsat_code,",
"wfc_onsun.code AS wfc_onsun_code, wfc_onph.code AS wfc_onph_code,",
"wfc_onwd.wagerate AS wfc_onwd_rate, wfc_onsat.wagerate AS wfc_onsat_rate,",
"wfc_onsun.wagerate AS wfc_onsun_rate, wfc_onph.wagerate AS wfc_onph_rate,",
"o.nohoursonweekday AS o_nowd, o.nohoursonsaturday AS o_nosat,",
"o.nohoursonsunday AS o_nosun, o.nohoursonpublicholiday AS o_noph",
"FROM companies_order AS o",
"INNER JOIN companies_customer AS c ON (c.id = o.customer_id)",
"LEFT JOIN companies_wagecode AS wfc_onwd ON (wfc_onwd.id = o.wagefactorcode_id)",
"LEFT JOIN companies_wagecode AS wfc_onsat ON (wfc_onsat.id = o.wagefactoronsat_id)",
"LEFT JOIN companies_wagecode AS wfc_onsun ON (wfc_onsun.id = o.wagefactoronsun_id)",
"LEFT JOIN companies_wagecode AS wfc_onph ON (wfc_onph.id = o.wagefactoronph_id)",
" WHERE c.company_id = %(compid)s::INT"
]
if order_pk:
sql_list.append('AND (o.id = %(oid)s)')
sql_keys['oid'] = order_pk
else:
if is_absence:
sql_list.append("AND c.isabsence")
else:
sql_list.append("AND NOT c.isabsence")
if is_inactive is not None and not is_inactive:
sql_list.append('AND NOT o.inactive')
sql_list.append("ORDER BY LOWER(REPLACE (o.code, '~', ''))")
sql = ' '.join(sql_list)
newcursor = connection.cursor()
newcursor.execute(sql, sql_keys)
order_rows = f.dictfetchall(newcursor)
return order_rows
def create_order_rows(request, order_pk=None, is_absence=None, is_template=None):
# PR2021-11-09
logging_on = False # s.LOGGING_ON
if logging_on:
logger.debug(' --- create_order_rows --- ')
logger.debug('order_pk: ' + str(order_pk))
logger.debug('is_absence: ' + str(is_absence))
logger.debug('is_template: ' + str(is_template))
# date filter not in use (yet)
period_datefirst, period_datelast = None, None
company_pk = request.user.company.pk
sql_keys = {'compid': company_pk}
sql_list = ["SELECT o.id, c.id AS c_id, c.company_id AS comp_id,",
"CONCAT(CASE WHEN c.isabsence THEN 'abscat_' ELSE 'order_' END, o.id::TEXT) AS mapid,",
"COALESCE(REPLACE (o.code, '~', ''),'') AS o_code_notilde, COALESCE(REPLACE (c.code, '~', ''),'') AS c_code_notilde,",
"COALESCE(CONCAT(REPLACE (c.code, '~', ''), ' - ', REPLACE (o.code, '~', '')),'') AS c_o_code,",
"c.code AS c_code, c.name AS c_name, c.identifier AS c_identifier,",
"c.isabsence AS c_isabsence, c.istemplate AS c_istemplate, c.inactive AS c_inactive,",
"o.code AS o_code, o.name AS o_name, o.identifier AS o_identifier, o.sequence AS o_sequence,",
"o.datefirst AS o_datefirst, o.datelast AS o_datelast, o.inactive AS o_inactive,",
"o.wagefactorcode_id AS wfc_onwd, o.wagefactoronsat_id AS wfc_onsat,",
"o.wagefactoronsun_id AS wfc_onsun, o.wagefactoronph_id AS wfc_onph,",
"wfc_onwd.code AS wfc_onwd_code, wfc_onsat.code AS wfc_onsat_code,",
"wfc_onsun.code AS wfc_onsun_code, wfc_onph.code AS wfc_onph_code,",
"wfc_onwd.wagerate AS wfc_onwd_rate, wfc_onsat.wagerate AS wfc_onsat_rate,",
"wfc_onsun.wagerate AS wfc_onsun_rate, wfc_onph.wagerate AS wfc_onph_rate,",
"o.nohoursonweekday AS o_nowd, o.nohoursonsaturday AS o_nosat,",
"o.nohoursonsunday AS o_nosun, o.nohoursonpublicholiday AS o_noph,",
"o.modifiedat AS modat, COALESCE(SUBSTRING (au.username, 7), '') AS modby_usr",
"FROM companies_order AS o",
"INNER JOIN companies_customer AS c ON (c.id = o.customer_id)",
"LEFT JOIN companies_wagecode AS wfc_onwd ON (wfc_onwd.id = o.wagefactorcode_id)",
"LEFT JOIN companies_wagecode AS wfc_onsat ON (wfc_onsat.id = o.wagefactoronsat_id)",
"LEFT JOIN companies_wagecode AS wfc_onsun ON (wfc_onsun.id = o.wagefactoronsun_id)",
"LEFT JOIN companies_wagecode AS wfc_onph ON (wfc_onph.id = o.wagefactoronph_id)",
"LEFT JOIN accounts_user AS au ON (au.id = o.modifiedby_id)",
" WHERE c.company_id = %(compid)s::INT"
]
# +++ filter allowed orders when user has 'allowed_customers' or 'allowed_orders'
# <PERMITS> PR2020-11-02
# # show only customers in request.user.allowed_customers or request.user.allowed_orders, show all when empty
allowed_customers_list = request.user.allowed_customers
allowed_orders_list = request.user.allowed_orders
if allowed_customers_list or allowed_orders_list:
if allowed_customers_list:
if allowed_orders_list:
sql_list.append(
"AND ( o.customer_id IN ( SELECT UNNEST( %(cid_arr)s::INT[])) OR o.id IN ( SELECT UNNEST( %(oid_arr)s::INT[] )))")
sql_keys['cid_arr'] = allowed_customers_list
sql_keys['oid_arr'] = allowed_orders_list
else:
sql_list.append("AND o.customer_id IN ( SELECT UNNEST( %(cid_arr)s::INT[]))")
sql_keys['cid_arr'] = allowed_customers_list
else:
if allowed_orders_list:
sql_list.append("AND o.id IN ( SELECT UNNEST( %(oid_arr)s::INT[]))")
sql_keys['oid_arr'] = allowed_orders_list
if order_pk:
sql_list.append("AND (o.id = %(oid)s)")
sql_keys['oid'] = order_pk
else:
if period_datefirst:
sql_list.append("AND (o.datelast >= CAST(%(rdf)s AS DATE) OR o.datelast IS NULL)")
sql_keys['rdf'] = period_datefirst
if period_datelast:
sql_list.append("AND (o.datefirst <= CAST(%(rdl)s AS DATE) OR o.datefirst IS NULL)")
sql_keys['rdl'] = period_datelast
if is_absence is not None:
if is_absence:
sql_list.append("AND c.isabsence")
else:
sql_list.append("AND NOT c.isabsence")
sql_keys['isabsence'] = is_absence
if is_template is not None:
if is_template:
sql_list.append("AND c.istemplate")
else:
sql_list.append("AND NOT c.istemplate")
sql_keys['istemplate'] = is_template
sql_list.append("ORDER BY o.id")
sql = ' '.join(sql_list)
with connection.cursor() as cursor:
cursor.execute(sql, sql_keys)
order_rows = f.dictfetchall(cursor)
if logging_on:
for row in order_rows:
logger.debug('order_rows: ' + str(row))
# if logging_on:
#logger.debug(' ')
#logger.debug('connection.queries:')
#for query in connection.queries:
# logger.debug(str(query))
return order_rows
def create_customer_rows(request, customer_pk=None, is_absence=None, is_template=None): # PR2021-03-21 PR2021-11-08
logging_on = False # s.LOGGING_ON
if logging_on:
logger.debug(' --- create_customer_rows --- ')
logger.debug('customer_pk: ' + str(customer_pk))
logger.debug('is_absence: ' + str(is_absence))
logger.debug('is_template: ' + str(is_template))
sql_keys = {'compid': request.user.company.pk}
sql_list = [
"SELECT c.id, c.company_id AS comp_id,",
"CONCAT('customer_', c.id::TEXT) AS mapid,",
"COALESCE(REPLACE (c.code, '~', ''),'') AS c_code_notilde,",
"c.code, c.name, c.identifier,",
# "c.contactname, c.address, c.zipcode, c.city, c.country, c.email, c.telephone,",
"c.interval, c.invoicecode_id AS c_invc_id, ",
"c.isabsence, c.istemplate, c.inactive,",
"c.modifiedat AS modat, COALESCE(SUBSTRING (au.username, 7), '') AS modby_usr",
"FROM companies_customer AS c",
"LEFT JOIN accounts_user AS au ON (au.id = c.modifiedby_id)",
"WHERE c.company_id = %(compid)s::INT",
]
if customer_pk:
sql_keys['cid'] = customer_pk
sql_list.append('AND c.id = %(cid)s::INT')
else:
if is_absence is not None:
sql_keys['isabsence'] = is_absence
if is_absence:
sql_list.append('AND c.isabsence')
else:
sql_list.append('AND NOT c.isabsence')
if is_template is not None:
sql_keys['istemplate'] = is_template
if is_template:
sql_list.append('AND c.istemplate')
else:
sql_list.append('AND NOT c.istemplate')
# +++ filter allowed customer when used has 'allowed_customers'
# <PERMITS> PR2020-11-02
# show only customers in request.user.allowed_customers or request.user.allowed_orders, show all when empty
# PR2020-11-03 debug: was: allowed_customers_list = request.user.allowed_customers
# but adding pk's to allowed_customers_list also adds them to request.user.allowed_customers
# this results in retrieving all orders from added customer_pk in create_order_rows
# Solution: create new list of customer_pk's
allowed_customers_list = []
if request.user.allowed_customers:
for c_id in request.user.allowed_customers:
allowed_customers_list.append(c_id)
# no items are added to allowed_orders_list, therefore 'allowed_orders_list = request.user.allowed_orders' can be used
allowed_orders_list = request.user.allowed_orders
if allowed_customers_list or allowed_orders_list:
# if allowed_orders exist: lookup their customers and add them to the list
if allowed_orders_list:
for order_pk in allowed_orders_list:
order = m.Order.objects.get_or_none(pk=order_pk)
if order:
c_id = order.customer_id
if c_id and c_id not in allowed_customers_list:
allowed_customers_list.append(c_id)
sql_list.append('AND c.id IN ( SELECT UNNEST( %(cid_arr)s::INT[]))')
sql_keys['cid_arr'] = allowed_customers_list
sql_list.append('ORDER BY c.id')
sql = ' '.join(sql_list)
with connection.cursor() as cursor:
cursor.execute(sql, sql_keys)
customer_rows = f.dictfetchall(cursor)
if logging_on:
for row in customer_rows:
logger.debug('customer_row: ' + str(row))
return customer_rows