def get_allowance_time_min(self, year, month):
if self.is_hourly_pay or self.is_fixed_cost:
return 0
elif self.calculate_type == '01':
return 160
elif self.calculate_type == '02':
return len(common.get_business_days(year, month)) * 8
elif self.calculate_type == '03':
return len(common.get_business_days(year, month)) * 7.9
elif self.calculate_type == '04':
return len(common.get_business_days(year, month)) * 7.75
else:
return self.allowance_time_min
def get_allowance_time_memo(self, year, month):
allowance_time_min = self.get_allowance_time_min(year, month)
float_part, int_part = math.modf(self.allowance_time_max)
if float_part == 0.0:
allowance_time_max = int(int_part)
else:
allowance_time_max = self.allowance_time_max
if self.is_hourly_pay or self.is_fixed_cost:
allowance_time_memo = ''
elif self.calculate_type in ('01', '02', '03', '04'):
allowance_time_memo = "※基準時間：%s～%sh/月" % (allowance_time_min,
allowance_time_max)
else:
if self.allowance_time_memo:
allowance_time_memo = self.allowance_time_memo
else:
allowance_time_memo = "※基準時間：%s～%sh/月" % (allowance_time_min,
allowance_time_max)
# 営業日数 × ８または営業日数 × ７.９の説明
if self.calculate_type in ('02', '03', '04'):
hours = 8
if self.calculate_type == '02':
hours = 8
elif self.calculate_type == '03':
hours = 7.9
elif self.calculate_type == '04':
hours = 7.75
allowance_time_memo += " （%s＝%s月の営業日数(%s)×%s）" % (
allowance_time_min, month,
len(common.get_business_days(year, month)), hours)
return allowance_time_memo
def download_attendance_list(request, year, month):
attendance_list = biz.get_attendance_by_month(year, month)
# ＥＢの休日を取得する。
eb_holidays = biz.get_eb_holiday_list()
# 就業日数
business_days = common.get_business_days(year, month, eb_holidays)
response = HttpResponse(content_type='text/csv')
filename = u"%s年%s月出勤一覧_%s" % (year, month, datetime.datetime.now().strftime("%Y%m%d%H%M%S"))
response['Content-Disposition'] = 'attachment; filename=' + urllib.quote(filename.encode('utf-8')) + '.csv'
writer = csv.writer(response) # , quoting=csv.QUOTE_ALL
for attendance in attendance_list:
# 欠勤日数
absence_days = (business_days - attendance.totalday) if (business_days - attendance.totalday) > 0 else 0
# 有休日数
used_holidays = biz.get_user_holidays_by_month(attendance.applicant, year, month)
# 社員コード
employee_code = attendance.applicant.ebemployee.code \
if attendance.applicant and attendance.applicant.ebemployee else ''
writer.writerow([employee_code, # 社員コード
business_days, # 就業日数
attendance.totalday if attendance.totalday else 0, # 出勤日数
absence_days, # 欠勤日数
used_holidays, # 有休日数
0, # 特休日数
0, # 休出日数
0, # 代休日数
0, # 遅早回数
attendance.totaltime if attendance.totaltime else 0, # 出勤時間
0, # 遅早時間
0, # 平日普通残業時間
attendance.nightcount if attendance.nightcount else 0, # 平日深夜残業時間
0, # 休日残業時間
0, # 休日深夜残業時間
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'', # 予備項目
'' # 予備項目
])
return response
def get_deadline(self):
"""支払通知書とＢＰ注文書をメール送信時の支払締切日を取得する
来月の第六営業日
:return:
"""
date = common.get_first_day_from_ym(self.year + self.month)
next_month = common.add_months(date, 1)
business_days = common.get_business_days(next_month.year,
next_month.month)
if len(business_days) > 5:
return business_days[5]
else:
return next_month
def get_partner_member_orders(member_id):
"""協力社員の注文書を取得する
:param member_id:
:return:
"""
member = models.Member.objects.get(pk=member_id)
qs_project_members = member.projectmember_set.all().order_by('-start_date', '-end_date')
results = []
for project_member in qs_project_members:
results.append({
'id': project_member.pk,
'name': project_member.project.name,
'business_days': None,
'order_no': '{}～{}'.format(project_member.start_date, project_member.end_date),
'order_file': None,
'order_request_file': None,
'is_sent': None,
'parent': None,
})
orders = []
for order in project_member.bpmemberorder_set.filter(is_deleted=False):
orders.append(serializers.BpMemberOrderDisplaySerializer(order).data)
for year, month in common.get_year_month_list(project_member.start_date, project_member.end_date, True):
tmp_ls = list(filter(
lambda i: (i['year'] + i['month']) <= (year + month) <= (i['end_year'] + i['end_month']),
orders
))
if tmp_ls:
results.extend(tmp_ls)
else:
results.append({
'id': '{}_{}{}'.format(project_member.pk, year, month),
'name': '{}年{}月'.format(year, month),
'year': year,
'month': month,
'end_year': year,
'end_month': month,
'business_days': len(common.get_business_days(year, month)),
'parent': project_member.pk,
})
return results
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_turnover(year, month, param_dict=None, order_list=None):
"""指定年月の請求売上を取得する。
:param year:
:param month:
:param param_dict:
:param order_list:
:return:
"""
days = common.get_business_days(year, month)
df = pd.read_sql(
"call sp_organization_turnover('%s%s', %s)" % (year, month, len(days)),
connection)
# 出向の契約を洗い出す
loan_df = df[df.is_loan == 1]
for index, row in loan_df.iterrows():
related_row = df.loc[(df.projectmember_id == row.projectmember_id)
& (df.is_loan == 0)]
if related_row.empty:
# 完全出向の場合は何もしない。
continue
# # ＥＢ契約の月給を再設定する。
# df.set_value(index, 'salary', df.loc[index]['salary'] + related_row.iloc[0].salary)
# ＢＰ契約に値を再設定する。
df.set_value(related_row.index[0], 'is_loan', row.is_loan)
df.set_value(related_row.index[0], 'salary',
df.loc[index]['salary'] + related_row.iloc[0].salary)
df.set_value(
related_row.index[0], 'allowance',
df.loc[index]['allowance'] + related_row.iloc[0].allowance)
df.set_value(
related_row.index[0], 'night_allowance',
df.loc[index]['night_allowance'] +
related_row.iloc[0].night_allowance)
df.set_value(related_row.index[0], 'expenses',
df.loc[index]['expenses'] + related_row.iloc[0].expenses)
df.set_value(
related_row.index[0], 'employment_insurance',
df.loc[index]['employment_insurance'] +
related_row.iloc[0].employment_insurance)
df.set_value(
related_row.index[0], 'health_insurance',
df.loc[index]['health_insurance'] +
related_row.iloc[0].health_insurance)
# ＥＢの出向契約は非表示
df = df.iloc[df.index != index]
# 重複した請求情報を削除
df = df.drop_duplicates(
subset=['projectrequestdetail_id', 'projectrequest_id'])
# 原価合計を計算する。
df['total_cost'] = df['salary'] + df['allowance'] + df[
'night_allowance'] + df['overtime_cost'] + df['traffic_cost'] + df[
'expenses'] + df['employment_insurance'] + df['health_insurance']
# 利益
df['profit'] = df['total_price'] - df['total_cost']
df['profit_rate'] = df['profit'] / df['total_price'] * 100
# 税込
df['all_price'] = df['total_price'] + df['expenses_price'] + df['tax_price']
if param_dict and isinstance(param_dict, dict):
for k, v in param_dict.items():
if k == 'organization_id':
organization = models.Section.objects.get(pk=v)
org_pk_list = common.get_organization_children(organization)
df = df[(df.division_id.isin(org_pk_list)) |
(df.section_id.isin(org_pk_list)) |
(df.subsection_id.isin(org_pk_list))]
elif k.endswith('_id'):
df = df[df[k] == int(v)]
else:
df = df[df[k].str.contains(v, na=False)]
if order_list:
name = order_list[0].strip('-')
ascending = not order_list[0].startswith('-')
df = df.sort_values(by=name, ascending=ascending)
return df
def get_business_days(self, obj):
if obj.business_days:
return obj.business_days
else:
return len(common.get_business_days(obj.year, obj.month))
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()