def get_partner_list():
with connection.cursor() as cursor:
cursor.callproc('sp_partner_list')
results = common.dictfetchall(cursor)
for row in results:
row['url'] = '/partner/{pk}'.format(pk=row.get('id'))
return results
def get_turnover_monthly_by_department_chart():
"""部署別、かつ月別の売上情報
:return:
"""
with connection.cursor() as cursor:
cursor.callproc('sp_turnover_monthly_by_division_chart')
data = common.dictfetchall(cursor)
labels = []
dict_division = dict()
first_month = None
for item in data:
ym = (item.get('year'), item.get('month'))
if ym not in labels:
if first_month is None:
first_month = datetime.date(int(ym[0]), int(ym[1]), 1)
labels.append(ym)
division_id = item.get('division_id')
if division_id not in dict_division:
interval = (int(ym[0]) * 12 + int(ym[1])) - (
first_month.year * 12 + first_month.month)
dict_division[division_id] = [None] * interval
division = dict_division[division_id]
division.append(item.get('turnover_amount', 0))
return {
'labels': [constants.DICT_MONTH_EN[m] for y, m in labels],
'series': dict_division.values(),
}
def get_salesperson_history(member_id):
"""社員の営業担当履歴
:param member_id:
:return:
"""
with connection.cursor() as cursor:
cursor.callproc('sp_salesperson_history', (member_id, ))
results = common.dictfetchall(cursor)
return results
def get_organization_history(member_id):
"""社員の所属部署履歴
:param member_id:
:return:
"""
with connection.cursor() as cursor:
cursor.callproc('sp_organization_dashboard', (member_id, ))
results = common.dictfetchall(cursor)
return results
def get_partner_division_all_by_month(partner_id, year, month):
with connection.cursor() as cursor:
cursor.callproc('sp_partner_contracts_by_month', (partner_id, year, month))
results = common.dictfetchall(cursor)
for row in results:
if row.get('id'):
row['member_url'] = '/partner/{partner_id}/members/{member_Id}/orders'.format(
partner_id=partner_id,
member_Id=row.get('id'),
)
return results
def get_partner_monthly_status(partner_id):
with connection.cursor() as cursor:
cursor.callproc('sp_partner_monthly_status', (partner_id,))
results = common.dictfetchall(cursor)
for row in results:
row['url'] = '/partner/{partner_id}/division/{year}/{month}'.format(
partner_id=partner_id,
year=row.get('year'),
month=row.get('month'),
)
return results
def get_project_attendance(project_id, year, month):
"""案件の指定年月の出勤明細
:param project_id:
:param year:
:param month:
:return:
"""
with connection.cursor() as cursor:
cursor.callproc('sp_project_attendance', (project_id, year, month))
data = common.dictfetchall(cursor)
return data
def get_partner_lump_contracts(partner_id, contract_id=None):
with connection.cursor() as cursor:
cursor.callproc('sp_partner_lump_orders', (partner_id,))
results = common.dictfetchall(cursor)
for row in results:
row['order_url'] = '/partner/{partner_id}/order/{order_id}'.format(
partner_id=partner_id,
order_id=row['order_id']
)
if contract_id is not None:
return row
return results
def get_organization_members(org_id):
with connection.cursor() as cursor:
cursor.callproc('sp_organization_members', (org_id, ))
results = common.dictfetchall(cursor)
for row in results:
if row.get('positions') is None:
continue
positions = row.get('positions').split(',')
row['positions'] = ",".join([
common.get_choice_name_by_key(constants.CHOICE_POSITION,
Decimal(pos)) for pos in positions
])
return results
def get_partner_members(partner_id):
"""協力会社の作業メンバー一覧を取得
:param partner_id: 協力会社ID
:return:
"""
with connection.cursor() as cursor:
cursor.callproc('sp_partner_members', (partner_id,))
results = common.dictfetchall(cursor)
for row in results:
row['url'] = '/partner/{partner_id}/members/{member_id}'.format(
partner_id=partner_id,
member_id=row.get('id')
)
return results
def get_partner_members_order_status(partner_id):
"""協力会社社員の注文状況
:param partner_id:
:return:
"""
with connection.cursor() as cursor:
cursor.callproc('sp_partner_member_orders', (partner_id, timezone.now().today()))
results = common.dictfetchall(cursor)
for row in results:
row['url'] = '/partner/{partner_id}/members/{member_id}/orders'.format(
partner_id=partner_id,
member_id=row.get('id')
)
return results
def get_member_list(date=timezone.now().date()):
"""基準日時の社員メンバー一覧を取得
:param date:
:return:
"""
def set_detail_url(x):
x['url'] = '/member/{pk}'.format(pk=x.get('id'))
return x
with connection.cursor() as cursor:
cursor.callproc('sp_member_list', (date, ))
results = common.dictfetchall(cursor)
members = list(map(set_detail_url, results))
return members
def get_project_attendance_list(project_id):
"""案件の月ごと出勤リスト
:param project_id: 案件ＩＤ
:return:
"""
with connection.cursor() as cursor:
cursor.callproc('sp_project_attendance_list', (project_id, ))
data = common.dictfetchall(cursor)
for row in data:
row['url'] = '/project/{pk}/attendance/{year}/{month}'.format(
pk=project_id,
year=row['year'],
month=row['month'],
)
return data
def search_member_by_name(keyword):
"""名前によってメンバーを検索する
:param keyword: 名前またはその一部
:return:
"""
members = []
if not keyword:
return members
with connection.cursor() as cursor:
cursor.callproc('sp_search_member', (keyword, ))
results = common.dictfetchall(cursor)
# ＩＤ重複したデータを消す
for item in results:
if len(members) > 0 and item.get('id') == members[-1].get('id'):
members.pop()
members.append(item)
return members
def get_project_order_list(project_id):
"""案件の注文書一覧
:param project_id: 案件ＩＤ
:return:
"""
with connection.cursor() as cursor:
cursor.callproc('sp_project_order_list', (project_id, ))
data = common.dictfetchall(cursor)
for row in data:
row['projects'] = json.loads(row['projects'])
if row['request_no']:
row['request_detail_url'] = '/project/{pk}/request/{request_no}'.format(
pk=project_id,
request_no=row['request_no'],
)
if row['uuid']:
row['request_download_url'] = '/master/attachment/download/{uuid}'.format(
uuid=row['uuid'], )
return data
def main():
qs = ProjectRequestDetail.objects.all()
for detail in qs:
project_member = detail.project_member
try:
member_attendance = MemberAttendance.objects.get(
project_member=project_member,
year=detail.year,
month=detail.month)
except ObjectDoesNotExist:
print u"出勤情報ありません：{}：アサインＩＤ：{}, {}年{}月".format(
project_member.member, project_member.pk, detail.year,
detail.month)
continue
try:
with connection.cursor() as cursor:
cursor.callproc('sp_project_member_cost', [
project_member.member.pk,
project_member.pk,
detail.year,
detail.month,
len(common.get_business_days(detail.year, detail.month)),
member_attendance.total_hours_bp
or member_attendance.total_hours,
member_attendance.allowance or 0,
member_attendance.night_days or 0,
member_attendance.traffic_cost or 0,
detail.expenses_price,
])
dict_cost = common.dictfetchall(cursor)[0]
detail.salary = dict_cost.get('salary', 0) or 0
detail.cost = dict_cost.get('cost', 0) or 0
detail.save()
except Exception as ex:
print ex
print u"エラー：{}：アサインＩＤ：{}, {}年{}月".format(project_member.member,
project_member.pk,
detail.year, detail.month)
def get_members_contracts():
with connection.cursor() as cursor:
cursor.execute("""
select c.id
, ct2.id as company_content_type_id
, c.company_id as company_object_id
, ct1.id as content_type_id
, c.member_id as object_id
, c.contract_no
, c.contract_date
, c.start_date
, c.end_date
, c.member_type
, c.endowment_insurance as insurance
, c.is_loan
, '01' as calculate_type
from eb_contract c
join django_content_type ct1 on ct1.app_label = 'eb' and ct1.model = 'member'
join django_content_type ct2 on ct2.app_label = 'eb' and ct2.model = 'company'
where c.status not in ('04', '05')
and c.is_deleted = 0
""")
return dictfetchall(cursor)
def get_project_history(member_id):
"""社員の案件履歴を取得
:param member_id:
:return:
"""
with connection.cursor() as cursor:
cursor.callproc('sp_project_dashboard', (member_id, ))
results = common.dictfetchall(cursor)
projects = []
prev_p = None
for p in results:
p['url'] = '/project/{pk}'.format(pk=p.get('id'))
if prev_p is None:
projects.append(p)
elif p.get('id') != prev_p.get('id'):
projects.append(p)
else:
if common.add_days(prev_p.get('end_date')) == p.get('start_date'):
prev_p['end_date'] = p.get('end_date')
else:
projects.append(p)
prev_p = p
return projects
def get_salesperson_status():
with connection.cursor() as cursor:
cursor.callproc('sp_salesperson_status')
results = common.dictfetchall(cursor)
return chart_salesperson_status(results)
def get_member_status():
with connection.cursor() as cursor:
cursor.callproc('sp_member_status')
results = common.dictfetchall(cursor)
return results[0] if results and len(results) > 0 else None
def get_partner_working_status():
with connection.cursor() as cursor:
cursor.callproc('sp_partner_working_status')
results = common.dictfetchall(cursor)
return chart_working_status(results)
def save(self, force_insert=False, force_update=False, using=None,
update_fields=None, other_data=None):
super(ProjectRequest, self).save(force_insert, force_update, using, update_fields)
# 請求書作成時、請求に関する全ての情報を履歴として保存する。
if other_data:
data = other_data
# 既存のデータを全部消す。
if hasattr(self, "projectrequestheading"):
self.projectrequestheading.delete()
self.projectrequestdetail_set.all().delete()
heading = ProjectRequestHeading(
project_request=self,
is_lump=self.project.is_lump,
lump_amount=self.project.lump_amount,
lump_comment=self.project.lump_comment,
is_hourly_pay=self.project.is_hourly_pay,
customer=self.project.customer,
customer_post_code=data['heading']['CUSTOMER_POST_CODE'],
customer_address=data['heading']['CUSTOMER_ADDRESS'],
customer_tel=data['heading']['CUSTOMER_TEL'],
customer_name=data['heading']['CUSTOMER_COMPANY_NAME'],
tax_rate=self.project.customer.tax_rate,
decimal_type=self.project.customer.decimal_type,
work_period_start=data['heading']['WORK_PERIOD_START'],
work_period_end=data['heading']['WORK_PERIOD_END'],
remit_date=data['heading']['REMIT_DATE_PURE'],
publish_date=data['heading']['PUBLISH_DATE_PURE'],
company_post_code=data['heading']['POST_CODE'],
company_address=data['heading']['ADDRESS'],
company_name=data['heading']['COMPANY_NAME'],
company_tel=data['heading']['TEL'],
company_master=data['heading']['MASTER'],
bank_account=data['heading']['BANK_ACCOUNT'],
bank_name=data['heading']['BANK_NAME'],
branch_no=data['heading']['BRANCH_NO'],
branch_name=data['heading']['BRANCH_NAME'],
account_type=data['heading']['ACCOUNT_TYPE_PURE'],
account_number=data['heading']['ACCOUNT_NUMBER'],
account_holder=data['heading']['BANK_ACCOUNT_HOLDER']
)
heading.save()
for i, item in enumerate(data['MEMBERS'], start=1):
project_member = item["EXTRA_PROJECT_MEMBER"]
total_hours = item['ITEM_WORK_HOURS'] if item['ITEM_WORK_HOURS'] else 0
expenses_price = item['ITEM_EXPENSES_PRICE']
try:
member_attendance = MemberAttendance.objects.get(
project_member=project_member, year=self.year, month=self.month
)
with connection.cursor() as cursor:
cursor.callproc('sp_project_member_cost', [
project_member.member.pk,
project_member.pk,
self.year,
self.month,
len(common.get_business_days(self.year, self.month)),
member_attendance.total_hours_bp or member_attendance.total_hours,
member_attendance.allowance or 0,
member_attendance.night_days or 0,
member_attendance.traffic_cost or 0,
expenses_price,
])
dict_cost = common.dictfetchall(cursor)[0]
except Exception as ex:
logger.error(ex)
logger.error(traceback.format_exc())
dict_cost = dict()
detail = ProjectRequestDetail(
project_request=self,
project_member=project_member,
year=self.year,
month=self.month,
organization_id=52, # TODO: 社員の部署取得
member_type='1', # TODO: 社員の契約形態
# salesperson_id=16, # TODO: 社員の営業員取得
partner_id=1, # TODO: 協力会社取得,
salary=dict_cost.get('salary', 0) or 0,
cost=dict_cost.get('cost', 0) or 0,
no=str(i),
hourly_pay=project_member.hourly_pay if project_member.hourly_pay else 0,
basic_price=project_member.price,
min_hours=project_member.min_hours,
max_hours=project_member.max_hours,
total_hours=total_hours,
extra_hours=item['ITEM_EXTRA_HOURS'] if item['ITEM_EXTRA_HOURS'] else 0,
rate=item['ITEM_RATE'],
plus_per_hour=project_member.plus_per_hour,
minus_per_hour=project_member.minus_per_hour,
total_price=item['ITEM_AMOUNT_TOTAL'],
expenses_price=expenses_price,
comment=item['ITEM_COMMENT']
)
detail.save()
