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_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_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 ace_bread_crumbs_generate(screen_name):
"""
Render form title bar.
@type screen_name: ScreenName
@param screen_name:
@return:
"""
sb = StringBuilder()
for menu_info in config_menus:
if 'screen_name' in menu_info and screen_name == menu_info[
'screen_name']:
sb.append('<ul class="breadcrumb">')
sb.append('<li class="active">')
sb.append('<i class="ace-icon fa fa-home home-icon"></i>')
sb.append('<a href="/">{0}</a>'.format(menu_info['name']))
sb.append('</li>')
break
if 'sub_menu' not in menu_info:
continue
for sub_menu_info in menu_info['sub_menu']:
if 'screen_name' in sub_menu_info and screen_name == sub_menu_info[
'screen_name']:
sb.append('<ul class="breadcrumb">')
sb.append('<li>')
sb.append('<i class="ace-icon fa fa-home home-icon"></i>')
sb.append('<a href="/">ホーム</a>')
sb.append('</li>')
sb.append('<li><a href="#">{0}</a></li>'.format(
menu_info['name']))
sb.append('<li class="active">{0}</li>'.format(
sub_menu_info['name']))
break
sb.append('</ul>')
return str(sb)
def ace_menu_generate(screen_name):
"""
Create top menu. Check roles and show only valid menu
@type screen_name: ScreenName
@param screen_name:
@type user: LoginUser
@param user:
@return:
"""
sb = StringBuilder()
sidebars = []
user = LoginUser.get_login_user()
menu_idx = 0
active_menu_idx = ""
for menu_info in config_menus:
if 'screen_name' in menu_info and not user.is_menu_available(
menu_info['screen_name']):
continue
menu_idx += 1
sub_menu_idx = 0
if 'screen_name' in menu_info and screen_name == menu_info[
'screen_name']:
active_menu_idx = str(menu_idx)
menu = create_menu(menu_info['icon'], menu_info['url'],
menu_info['name'])
sidebars.append(menu)
if 'sub_menu' not in menu_info:
continue
for sub_menu_info in menu_info['sub_menu']:
if 'screen_name' in sub_menu_info and not user.is_menu_available(
sub_menu_info['screen_name']):
continue
sub_menu_idx += 1
if 'screen_name' in sub_menu_info and screen_name == sub_menu_info[
'screen_name']:
active_menu_idx = str(menu_idx) + "." + str(sub_menu_idx)
append_submenu(
menu,
create_menu(sub_menu_info['icon'], sub_menu_info['url'],
sub_menu_info['name']))
sb.append('<ul class="nav nav-list">\n')
menu_idx = 0
for menu in sidebars:
menu_idx += 1
sb.append(render_menu(menu, active_menu_idx, "{0}".format(menu_idx)))
# sb.append(' <li class="">\n')
# sb.append(' <a href="/course_list">\n')
# sb.append(' <i class="menu-icon fa fa-caret-right"></i>\n')
# sb.append(' <span class="menu-text">\n')
# sb.append(' <div class="center">\n')
# sb.append(' <img src="/static/images/msmy/MsMy.jpg" style="height: 10vh"/>\n')
# sb.append(' <h6><span class="red">REMI ENGLISH</span><span class="white" id="id-text2"></span></h6>\n')
# sb.append(' <h6 class="blue" id="id-company-text"><i class="fa fa-mobile"></i> 0873.099.599</h6>\n')
# sb.append(' <h6 class="blue" id="id-company-text"><i class="fa fa-mobile"></i> 09.03.03.2014</h6>\n')
# sb.append(' <h6 class="blue" id="id-company-text"><i class=""></i> http://remienglish.com</h6>\n')
# sb.append(' </div>\n')
# sb.append(' </span>\n')
# sb.append(' </a>\n')
# sb.append(' <b class="arrow"></b>\n')
# sb.append(' </li>\n')
sb.append('</ul><!-- /.nav-list -->\n')
return str(sb)
def render_menu(menu, active_id, menu_id):
"""
Render html of a item of config menu
@param menu:
@param active_id:
@param menu_id:
@return:
"""
sb = StringBuilder()
menu_class = ""
if active_id == menu_id:
menu_class = "active"
elif active_id.startswith("{0}.".format(menu_id)):
menu_class = "active open"
class_dropdown_toggle = ''
if 'children' in menu:
class_dropdown_toggle = 'class="dropdown-toggle"'
sb.append(' <li class="{0}">\n'.format(menu_class))
sb.append(' <a href="{0}" {1}>\n'.format(menu['href'],
class_dropdown_toggle))
sb.append(' <i class="menu-icon fa {0}"></i>\n'.format(
menu['icon']))
sb.append(' <span class="menu-text">{0}</span>\n'.format(
menu['text']))
sb.append(' \n')
sb.append(' </a>\n')
sb.append(' <b class="arrow"></b>\n')
if "children" in menu:
i = 0
sb.append(' <ul class="submenu">\n')
for child in menu['children']:
i = i + 1
sb.append(
render_menu(child, active_id, "{0}.{1}".format(menu_id, i)))
sb.append(' </ul>\n')
sb.append(' </li>\n')
return str(sb)
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