def get_import_history_list(owner_id):
"""
Retrieve import history of user
@param owner_id: account login
@type owner_id: int
@return:
"""
query = "select ih.*, dp.meta_id as department_id, lt.meta_id as latest from import_history ih " \
"left join (select import_history_id, meta_id from biz_budget bp " \
"inner join base_data_auth_meta_rel bd on bd.meta_type_id=%s " \
"and bd.base_data_name=%s and bp.id=bd.base_data_id " \
"group by import_history_id, meta_id) lt " \
"on ih.id = lt.import_history_id " \
"left join (select base_data_id, meta_id from base_data_auth_meta_rel " \
"where meta_type_id=%s and base_data_name=%s) dp " \
"on dp.base_data_id=ih.id " \
"where ih.user_id=%s and ih.data_type=%s " \
"order by imported_datetime desc"
rs = DbAgent.get_record_set(query, [
AuthMetaTypeDefine.Department.code,
AuthBaseDataName.BizBudget.value,
AuthMetaTypeDefine.Department.code,
AuthBaseDataName.ImportHistory.value, owner_id,
ImportDataType.BizBudget.value
])
rs = rs[:5]
return rs
def get_term_import_history_list(term_number):
"""
Retrieve data from table by term_number:
1. import_history: user_id, owner_id, memo, last_import_date
2. user_filter: meta_type by user_id
3. auth_meta_type:
4. base_data_auth_meta_rel:
5. biz_prospect: target_id, type, opportunity_stage by owner_id
@param term_number: fiscal term
@type term_number: int
@return:
"""
user = LoginUser.get_login_user()
in_query = user.get_filtered_query_string(
ModuleName.BizProspect, AuthMetaTypeDefine.Department.code)
results_map = dict()
# len > 0
if in_query is not None and len(in_query) > 0:
query = "select ih.*, lt.meta_id as department_id from import_history ih " \
"inner join (select import_history_id, meta_id from biz_prospect bp " \
"inner join base_data_auth_meta_rel bd on bd.meta_type_id=%s " \
"and bd.base_data_name=%s and bp.id=bd.base_data_id and bd.meta_id in ({0})" \
"group by import_history_id, meta_id) lt " \
"on ih.id = lt.import_history_id " \
"where ih.target_term=%s and ih.data_type=%s " \
"order by lt.meta_id,imported_datetime desc".format(in_query)
# config_role is None
elif in_query is None:
query = "select ih.*, lt.meta_id as department_id from import_history ih " \
"inner join (select import_history_id, meta_id from biz_prospect bp " \
"inner join base_data_auth_meta_rel bd on bd.meta_type_id=%s " \
"and bd.base_data_name=%s and bp.id=bd.base_data_id " \
"group by import_history_id, meta_id) lt " \
"on ih.id = lt.import_history_id " \
"where ih.target_term=%s and ih.data_type=%s " \
"order by lt.meta_id,imported_datetime desc"
# len = 0
else:
return {}
results = DbAgent.get_record_set(query, [
AuthMetaTypeDefine.Department.code,
AuthBaseDataName.BizProspect.value,
int(term_number), ImportDataType.BizProspect.value
])
# map results to view in template
for result in results:
department_id = result['department_id']
if department_id in results_map:
results_map[department_id].append(result)
else:
department_value = []
results_map[department_id] = department_value
results_map[department_id].append(result)
return results_map
def get_term_import_history_list(target_term):
"""
Retrieve data from table by target term and filter by user
@param target_term: fiscal term
@type target_term: int
@return:
"""
# Filter
user = LoginUser.get_login_user()
in_query = user.get_filtered_query_string(
ModuleName.BizBudget,
AuthMetaTypeDefine.Department.code,
none_if_no_filter=False)
if in_query is not None and len(in_query) > 0:
query = "select ih.*, lt.meta_id as department_id from import_history ih " \
"inner join (select import_history_id, meta_id from biz_budget bp " \
"inner join base_data_auth_meta_rel bd on bd.meta_type_id=%s " \
"and bd.base_data_name=%s and bp.id=bd.base_data_id and bd.meta_id in ({0})" \
"group by import_history_id, meta_id) lt " \
"on ih.id = lt.import_history_id " \
"where ih.target_term=%s and ih.data_type=%s " \
"order by lt.meta_id,imported_datetime desc".format(in_query)
elif in_query is None:
query = "select ih.*, lt.meta_id as department_id from import_history ih " \
"inner join (select import_history_id, meta_id from biz_budget bp " \
"inner join base_data_auth_meta_rel bd on bd.meta_type_id=%s " \
"and bd.base_data_name=%s and bp.id=bd.base_data_id " \
"group by import_history_id, meta_id) lt " \
"on ih.id = lt.import_history_id " \
"where ih.target_term=%s and ih.data_type=%s " \
"order by lt.meta_id,imported_datetime desc"
else:
return []
rs = DbAgent.get_record_set(query, [
AuthMetaTypeDefine.Department.code,
AuthBaseDataName.BizBudget.value, target_term,
ImportDataType.BizBudget.value
])
# rs = rs[:5]
return rs
def get_detail_data(term_number, department_id, month_id):
"""
Retrieve data from table by term_number, department_id, month_id:
1. import_history: user_id, owner_id, memo, last_import_date
2. user_filter: meta_type by user_id
3. auth_meta_type:
4. base_data_auth_meta_rel:
5. biz_prospect: target_id, type, opportunity_stage by owner_id
@param term_number: fiscal term
@param department_id: auth meta type id
@param month_id: month
@type term_number: int
@type department_id: int
@type month_id: int
@return:
"""
periods = FiscalTermLogic.get_term_period_list(int(term_number))
period = periods.get(name=str(month_id) + '月')
auth_meta_type = AuthMetaType.objects.get(
id=AuthMetaTypeDefine.Department.code)
meta_types = MetaType.objects.all().order_by("meta_type")
from helper.util import StringBuilder
query = StringBuilder()
query_from = StringBuilder()
query_order_by = StringBuilder()
main_fields = "*"
query_from.append("\nfrom biz_prospect bz")
query.append("select {0}".format(main_fields))
if auth_meta_type.meta_type == AuthMetaTypeDefine.Department.title:
query.append(", {0}.meta_id as {0}".format(
auth_meta_type.meta_type))
query_from.append(
" inner join base_data_auth_meta_rel {0} on {0}.base_data_name='{1}' "
"and bz.id={0}.base_data_id and {0}.meta_type_id={2} and {0}.meta_id={3}"
.format(auth_meta_type.meta_type,
AuthBaseDataName.BizProspect.value, auth_meta_type.id,
int(department_id)))
query_order_by.append(", {0}.meta_id".format(
auth_meta_type.meta_type))
# Meta
for meta_type in meta_types:
# Only staff
if meta_type.id in [
MetaTypeDefine.Staff.code, MetaTypeDefine.Product.code,
MetaTypeDefine.Partner.code
]:
query.append(", {0}.meta_id as {0}".format(
meta_type.meta_type))
query_from.append(
" left join base_data_meta_rel {0} on {0}.base_data_name='{1}' "
"and bz.id={0}.base_data_id and {0}.meta_type_id={2}".
format(meta_type.meta_type,
AuthBaseDataName.BizProspect.value, meta_type.id))
query.append(", import_history.user_id as user_id")
query_from.append(
" inner join import_history on bz.import_history_id=import_history.id"
)
query_order_by.append(", {0}".format('target_id'))
query.append(query_from)
query.append(
" \nwhere accrual_date >= '{0}' and accrual_date <= '{1}'".format(
period.start_date, period.end_date))
results = DbAgent.get_record_set(str(query))
month_detail_final = []
# map results to view in template
for target in results:
target_id = target['target_id'] # target name and product name
prospect_type = target['type'] # account type
opportunity_stage = target['opportunity_stage']
try:
product_name = MetaInfo.objects.get(id=target['product']).value
except ObjectDoesNotExist:
product_name = ''
try:
partner_name = MetaInfo.objects.get(id=target['partner']).value
except ObjectDoesNotExist:
partner_name = ''
try:
staff_name = MetaInfo.objects.get(id=target['staff']).value
except ObjectDoesNotExist:
staff_name = ''
# retrieve target name
try:
target_name = Target.objects.get(id=target_id).name
except ObjectDoesNotExist:
target_name = ''
try:
username = User.objects.get(id=target['user_id']).user_name
except ObjectDoesNotExist:
username = ''
target_map = dict()
target_map['title'] = target['title']
target_map['target_name'] = target_name
# map prospect_type
if prospect_type == ProspectTypeDefine.Revenue.code:
target_map['prospect_type'] = ProspectTypeDefine.Revenue.value[
1]
elif prospect_type == ProspectTypeDefine.Outsourcing.code:
target_map[
'prospect_type'] = ProspectTypeDefine.Outsourcing.value[1]
else:
target_map['prospect_type'] = ''
# map opportunity_stage to A, B, C
target_map['opportunity_stage'] = \
Config.get_code_to_name_map('opportunity_stage')[opportunity_stage]
target_map['amount'] = round(target['amount'] / 1000)
target_map['accrual_date'] = target['accrual_date']
target_map['settlement_date'] = target['settlement_date']
target_map['staff_name'] = staff_name
target_map['product_name'] = product_name
target_map['partner_name'] = partner_name
target_map['username'] = username
month_detail_final.append(target_map)
return month_detail_final
def get_month_data(term_number, department_id):
"""
Retrieve data from table by term_number and department_id:
1. import_history: user_id, owner_id, memo, last_import_date
2. user_filter: meta_type by user_id
3. auth_meta_type:
4. base_data_auth_meta_rel:
5. biz_prospect: target_id, type, opportunity_stage by owner_id
@param term_number: fiscal term
@param department_id: auth meta type id
@type term_number: int
@type department_id: int
@return:
"""
periods = FiscalTermLogic.get_term_period_list(int(term_number))
auth_meta_type = AuthMetaType.objects.get(
id=AuthMetaTypeDefine.Department.code)
from helper.util import StringBuilder
query = StringBuilder()
query_from = StringBuilder()
query_group_by = StringBuilder()
query_order_by = StringBuilder()
main_fields = "target_id, type, opportunity_stage" # target_id
query_from.append("\nfrom biz_prospect bz")
query_group_by.append("\ngroup by {0}".format(main_fields))
query.append("select {0}".format(main_fields))
# Auth meta
if auth_meta_type.meta_type == AuthMetaTypeDefine.Department.title:
query.append(", {0}.meta_id as {0}".format(
auth_meta_type.meta_type))
query_from.append(
" inner join base_data_auth_meta_rel {0} on {0}.base_data_name='{1}' "
"and bz.id={0}.base_data_id and {0}.meta_type_id={2} and {0}.meta_id={3}"
.format(auth_meta_type.meta_type,
AuthBaseDataName.BizProspect.value, auth_meta_type.id,
int(department_id)))
query_group_by.append(", {0}.meta_id".format(
auth_meta_type.meta_type))
query_order_by.append(", {0}.meta_id".format(
auth_meta_type.meta_type))
# Sum by 12 month
for i in range(periods.count()):
query.append(
", sum(case when accrual_date >= '{0}' and accrual_date <= '{1}' "
"then amount else 0 end) as '{2}'".format(
periods[i].start_date, periods[i].end_date,
periods[i].start_date.month))
query_order_by.append(", {0}".format(main_fields))
query.append(query_from)
query.append(query_group_by)
query.append(" \norder by {0}".format(str(query_order_by)[1:]))
results = DbAgent.get_record_set(str(query))
depart_month_final = []
depart_month_sum_map = dict()
# map results to view in template
for target in results:
depart_month_map = OrderedDict({})
target_id = target['target_id']
prospect_type = target['type']
opportunity_stage = target['opportunity_stage']
try:
target_name = Target.objects.get(id=target_id).name
except ObjectDoesNotExist:
target_name = ''
depart_month_map['target_name'] = target_name
# map prospect type
if prospect_type == ProspectTypeDefine.Revenue.code:
depart_month_map[
'prospect_type'] = ProspectTypeDefine.Revenue.value[1]
elif prospect_type == ProspectTypeDefine.Outsourcing.code:
depart_month_map[
'prospect_type'] = ProspectTypeDefine.Outsourcing.value[1]
else:
depart_month_map['prospect_type'] = ''
# map opportunity stage
depart_month_map['opportunity_stage'] = \
Config.get_code_to_name_map('opportunity_stage')[opportunity_stage]
target_month_map = OrderedDict({})
# add mount to 12 months
for period in periods:
target_month_map[period.start_date.month] = round(
target[str(period.start_date.month)] / 1000)
if period.start_date.month in depart_month_sum_map:
depart_month_sum_map[period.start_date.month] += int(
target[str(period.start_date.month)])
else:
depart_month_sum_map[period.start_date.month] = int(
target[str(period.start_date.month)])
depart_month_map['month'] = target_month_map
depart_month_map['sum'] = sum(depart_month_map['month'].values())
if depart_month_map['sum'] != 0:
depart_month_final.append(depart_month_map)
return depart_month_final
def get_current_data(term_number):
"""
Retrieve data from table by term_number.
@param term_number: fiscal term
@type term_number: int
@return:
"""
user = LoginUser.get_login_user()
fiscal = FiscalTerm.objects.get(term_number=int(term_number))
periods = FiscalTermLogic.get_term_period_list(int(term_number))
auth_meta_types = AuthMetaType.objects.all().order_by("meta_type")
from helper.util import StringBuilder
query = StringBuilder()
query_from = StringBuilder()
query_group_by = StringBuilder()
query_order_by = StringBuilder()
main_fields = "target_id, type, opportunity_stage" # target_id
query_from.append("\nfrom biz_prospect bz")
query_group_by.append("\ngroup by {0}".format(main_fields))
query.append("select {0}".format(main_fields))
# Auth meta
for meta_type in auth_meta_types:
# Only department
if meta_type.meta_type == AuthMetaTypeDefine.Department.title:
in_query = user.get_filtered_query_string(
ModuleName.BizProspect, AuthMetaTypeDefine.Department.code)
# len > 0
if in_query is not None and len(in_query) > 0:
query.append(", {0}.meta_id as {0}".format(
meta_type.meta_type))
query_from.append(
" inner join base_data_auth_meta_rel {0} on {0}.base_data_name='{1}' "
"and bz.id={0}.base_data_id and {0}.meta_type_id={2} and {0}.meta_id in ({3})"
.format(meta_type.meta_type,
AuthBaseDataName.BizProspect.value,
meta_type.id, in_query))
query_group_by.append(", {0}.meta_id".format(
meta_type.meta_type))
query_order_by.append(", {0}.meta_id".format(
meta_type.meta_type))
# config_role is None
elif in_query is None:
query.append(", {0}.meta_id as {0}".format(
meta_type.meta_type))
query_from.append(
" inner join base_data_auth_meta_rel {0} on {0}.base_data_name='{1}' "
"and bz.id={0}.base_data_id and {0}.meta_type_id={2} ".
format(meta_type.meta_type,
AuthBaseDataName.BizProspect.value,
meta_type.id))
query_group_by.append(", {0}.meta_id".format(
meta_type.meta_type))
query_order_by.append(", {0}.meta_id".format(
meta_type.meta_type))
# len = 0
else:
return []
# Sum by 12 month
for i in range(periods.count()):
query.append(
", sum(case when accrual_date >= '{0}' and accrual_date <= '{1}' "
"then amount else 0 end) as '{2}'".format(
periods[i].start_date, periods[i].end_date,
periods[i].start_date.month))
query.append(", sum(amount) as summary")
query_order_by.append(", {0}".format(main_fields))
query.append(query_from)
query.append(
" \nwhere accrual_date >= '{0}' and accrual_date <= '{1}' ".format(
fiscal.start_date, fiscal.end_date))
query.append(query_group_by)
query.append(" \norder by {0}".format(str(query_order_by)[1:]))
results = DbAgent.get_record_set(str(query))
return results
def get_term_data(term_number, import_history_id=None):
"""
Retrieve data of current fiscal and filter by user
@param term_number: fiscal term
@param import_history_id: import history of user
@type term_number: int
@type import_history_id: int
@return:
"""
user = LoginUser.get_login_user()
periods = FiscalTermLogic.get_term_period_list(int(term_number))
fiscal_period_ids = FiscalTermLogic.create_period_id_in_cond(periods)
auth_meta_types = AuthMetaType.objects.all().order_by("meta_type")
# meta_types = MetaType.objects.all().order_by("meta_type")
from helper.util import StringBuilder
query = StringBuilder()
query_from = StringBuilder()
query_group_by = StringBuilder()
query_order_by = StringBuilder()
main_fields = "account_id, plan_type" # target_id
query_from.append("\nfrom biz_budget bz")
query_group_by.append("\ngroup by {0}".format(main_fields))
query.append("select {0}".format(main_fields))
# Auth meta
for meta_type in auth_meta_types:
# Only department
if meta_type.meta_type == AuthMetaTypeDefine.Department.title:
in_query = user.get_filtered_query_string(
ModuleName.BizBudget,
AuthMetaTypeDefine.Department.code,
none_if_no_filter=False)
if in_query is not None and len(in_query) > 0:
query.append(", {0}.meta_id as {0}".format(
meta_type.meta_type))
query_from.append(
" left join base_data_auth_meta_rel {0} on {0}.base_data_name='{1}' "
"and bz.id={0}.base_data_id and {0}.meta_type_id={2} and {0}.meta_id in ({3})"
.format(meta_type.meta_type,
AuthBaseDataName.BizBudget.value, meta_type.id,
in_query))
query_group_by.append(", {0}.meta_id".format(
meta_type.meta_type))
query_order_by.append(", {0}.meta_id".format(
meta_type.meta_type))
elif in_query is None:
query.append(", {0}.meta_id as {0}".format(
meta_type.meta_type))
query_from.append(
" left join base_data_auth_meta_rel {0} on {0}.base_data_name='{1}' "
"and bz.id={0}.base_data_id and {0}.meta_type_id={2} ".
format(meta_type.meta_type,
AuthBaseDataName.BizBudget.value, meta_type.id))
query_group_by.append(", {0}.meta_id".format(
meta_type.meta_type))
query_order_by.append(", {0}.meta_id".format(
meta_type.meta_type))
else:
return []
# # Meta
# for meta_type in meta_types:
# # Only staff
# if meta_type.id == MetaTypeDefine.Staff.code:
# query.append(", {0}.meta_id as {0}".format(meta_type.meta_type))
# query_from.append(" left join base_data_meta_rel {0} on {0}.base_data_name='{1}' "
# "and bz.id={0}.base_data_id and {0}.meta_type_id={2}".
# format(meta_type.meta_type, AuthBaseDataName.BizBudget.value, meta_type.id))
# query_group_by.append(", {0}.meta_id".format(meta_type.meta_type))
# Sum by 12 month
for i in range(periods.count()):
query.append(
", sum(case when fiscal_period_id = {0} then amount else 0 end) as amount{1}"
.format(periods[i].id, i + 1))
# summary
query.append(", sum(amount) as summary")
query_order_by.append(", {0}".format(main_fields))
query.append(query_from)
query.append(" \nwhere fiscal_period_id in ({0})".format(", ".join(
map(str, fiscal_period_ids))))
if import_history_id is not None:
query.append(
" and import_history_id={0} ".format(import_history_id))
query.append(query_group_by)
query.append(" \norder by {0}".format(str(query_order_by)[1:]))
results = DbAgent.get_record_set(str(query))
return results