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: 案件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) # ID重複したデータを消す 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: 案件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"出勤情報ありません:{}:アサインID:{}, {}年{}月".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"エラー:{}:アサインID:{}, {}年{}月".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()