银行业务管理系统 系统设计与实现报告

class CreateBranches < ActiveRecord::Migration[6.0]
  def change
    create_table :branches, comment: '支行' do |t|
      t.string :name, limit: 64, unique: true, comment: '名称'
      t.string :city, limit: 64, comment: '城市'
      t.decimal :assets, precision: 12, scale: 2, default: 0.0, comment: '资产'

      t.timestamps
    end
  end
end

class CreateDepartments < ActiveRecord::Migration[6.0]
  def change
    create_table :departments, comment: '部门' do |t|
      t.string :name, limit: 64, unique: true, comment: '部门名称'
      t.string :kind, limit: 64, comment: '部门类型'

      t.timestamps
    end
  end
end

class CreateStaffs < ActiveRecord::Migration[6.0]
  def change
    create_table :staffs, comment: '员工' do |t|
      t.string :person_id, limit: 18, unique: true, null: false, comment: '身份证号'
      t.string :name, limit: 64, comment: '姓名'
      t.string :phone, limit: 64, comment: '电话'
      t.string :address, limit: 256, comment: '家庭地址'
      t.date :start_date, default: 'CURRENT_TIMESTAMP', comment: '开始工作日期'
      t.boolean :manager, default: -> { false }, comment: '经理'

      t.references :branch, index: true, foreign_key: { on_delete: :restrict }
      t.references :department, index: true, foreign_key: { on_delete: :restrict }

      t.timestamps
    end
  end
end

class CreateClients < ActiveRecord::Migration[6.0]
  def change
    create_table :clients, comment: '客户' do |t|
      t.string :person_id, limit: 18, unique: true, null: false, comment: '身份证号'
      t.string :name, limit: 64, comment: '姓名'
      t.string :phone, limit: 64, comment: '电话'
      t.string :address, limit: 256, comment: '地址'

      t.references :manager, null: true, index: true, foreign_key: { to_table: :staffs, on_delete: :restrict }
      t.integer :manager_type, limit: 1, default: 0, null: true

      t.timestamps
    end
  end
end

class CreateContacts < ActiveRecord::Migration[6.0]
  def change
    create_table :contacts, id: false, comment: '联系人' do |t|
      t.string :name, limit: 64, comment: '姓名'
      t.string :phone, limit: 64, comment: '电话'
      t.string :email, limit: 64, comment: 'Email'
      t.string :relationship, limit: 64, comment: '与客户关系'
      t.references :client, primary_key: true, index: true, foreign_key: { on_delete: :cascade }

      t.timestamps
    end
  end
end

class CreateAccounts < ActiveRecord::Migration[6.0]
  def change
    create_table :accounts, comment: '账户' do |t|
      t.references :branch, index: true, foreign_key: { on_delete: :restrict }, comment: '开户支行'
      t.references :accountable, null: false, polymorphic: true, comment: '类型账户ID'
      t.decimal :balance, precision: 12, scale: 2, default: 0.0, comment: '余额'
      t.date :open_date, default: 'CURRENT_TIMESTAMP', comment: '开户日期'

      t.timestamps
    end
  end
end

class CreateOwnerships < ActiveRecord::Migration[6.0]
  def change
    # (Client, Branch, AccountType) -> Account
    create_table :ownerships, comment: '客户账户关系' do |t|
      t.references :account, foreign_key: { on_delete: :cascade }
      t.references :client, foreign_key: { on_delete: :restrict }
      t.references :branch, foreign_key: { on_delete: :restrict }
      t.string :accountable_type
      t.datetime :last_access, default: -> { 'CURRENT_TIMESTAMP' }, comment: '最近访问'

      t.timestamps
    end
  end
end

class AddIndexToOwnerships < ActiveRecord::Migration[6.0]
  def change
    add_index :ownerships, %i[branch_id accountable_type client_id], unique: true
  end
end

class CreateDepositAccounts < ActiveRecord::Migration[6.0]
  def change
    create_table :deposit_accounts, comment: '储蓄账户' do |t|
      t.float :interest_rate, default: 1.0, comment: '利率'
      t.string :currency, limit: 3, default: 'BTC', comment: '货币类型'
    end
  end
end

class CreateCheckAccounts < ActiveRecord::Migration[6.0]
  def change
    create_table :check_accounts, comment: '支票账户' do |t|
      t.decimal :withdraw_amount, precision: 12, scale: 2, default: 0.0, comment: '透支额'
    end
  end
end

class CreateLoans < ActiveRecord::Migration[6.0]
  def change
    create_table :loans, comment: '贷款' do |t|
      t.decimal :amount, precision: 12, scale: 2, default: 0.0, comment: '金额'
      t.references :branch, index: true, foreign_key: { on_delete: :restrict }

      t.timestamps
    end
  end
end

class CreateClientsLoans < ActiveRecord::Migration[6.0]
  def change
    create_table :clients_loans do |t|
      t.references :loan, index: true, foreign_key: { on_delete: :cascade }
      t.references :client, index: true, foreign_key: { on_delete: :restrict }
    end
  end
end

class CreateIssues < ActiveRecord::Migration[6.0]
  def change
    create_table :issues, comment: '逐次支付' do |t|
      t.references :loan, index: true, foreign_key: { on_delete: :cascade }
      t.date :date, default: 'CURRENT_TIMESTAMP', comment: '日期'
      t.decimal :amount, precision: 12, scale: 2, default: 0.0, comment: '金额'
    end
  end
end

class Branch < ApplicationRecord
  has_many :staffs, dependent: :restrict_with_error
  has_many :ownerships, dependent: :restrict_with_error
  has_many :accounts, through: :ownerships
  has_many :loans, dependent: :restrict_with_error

  validates_uniqueness_of :name
  validates_presence_of :city
  validates_numericality_of :assets, greater_than_or_equal_to: 0.0
end

class Department < ApplicationRecord
  has_many :staffs, dependent: :restrict_with_error

  validates_presence_of :name
  validates_presence_of :kind
end

class Person < ApplicationRecord
  self.abstract_class = true

  validates_uniqueness_of :person_id
  #validate :valid_person_id?
  validates_presence_of :name
  validates_presence_of :phone
  validates_presence_of :address

  def valid_person_id?
    errors.add :person_id, 'Invalid personal ID' unless person_id =~ %r![0-9]{17}[0-9xX]!
  end
end

class Staff < Person
  belongs_to :branch
  belongs_to :department

  has_many :clients, foreign_key: :manager_id, dependent: :restrict_with_error
end

class Client < Person
  belongs_to :manager, class_name: :Staff
  has_one :contact, dependent: :destroy
  has_many :ownerships, dependent: :restrict_with_error
  has_and_belongs_to_many :accounts, through: :ownerships
  has_and_belongs_to_many :loans, dependent: :restrict_with_error

  accepts_nested_attributes_for :contact, update_only: true, allow_destroy: true
end

class Contact < ApplicationRecord
  belongs_to :client

  validates_presence_of :name
  validates_presence_of :phone
  validates_presence_of :email
  validates_presence_of :relationship
end

class Account < ApplicationRecord
  belongs_to :branch
  belongs_to :accountable, polymorphic: true
  has_many :ownerships, dependent: :destroy
  #has_and_belongs_to_many :clients, through: :ownerships

  accepts_nested_attributes_for :accountable, :ownerships, update_only: true

  validate :check_balance
  validate :validate_owners

  # Credits: https://stackoverflow.com/a/32915379/5958455
  def build_accountable(params)
    self.accountable = accountable_type.safe_constantize.new params
  end

  def validate_owners
    errors.add :base, '关联客户至少有一位' if ownerships.empty?
  end

  def check_balance
    case accountable_type
    when 'DepositAccount'
      errors.add :base, '储蓄账户不允许欠款' if balance < 0.0
    when 'CheckAccount'
      errors.add :base, '支票账户欠款不允许超过透支额' if balance + accountable.withdraw_amount < 0.0
    end
  end
end

module Accountable
  extend ActiveSupport::Concern

  included do
    has_one :account, as: :accountable, dependent: :destroy

    accepts_nested_attributes_for :account

    validates_presence_of :account
  end
end

class DepositAccount < ApplicationRecord
  include Accountable

  validates_numericality_of :interest_rate, greater_than_or_equal_to: 0.0
end

class CheckAccount < ApplicationRecord
  include Accountable
end

class Ownership < ApplicationRecord
  belongs_to :branch
  belongs_to :client
  belongs_to :account

  validates_uniqueness_of :client, scope: %i[branch accountable_type]

  before_save :update_access_time
  before_destroy :check_owners_count

  def update_access_time
    self.last_access = Time.now
  end

  def check_owners_count
    return if account.ownerships.count > 1
    errors.add :base, '关联客户至少有一位'
    throw :abort
  end
end

class Loan < ApplicationRecord
  belongs_to :branch
  has_and_belongs_to_many :clients
  has_many :issues, dependent: :destroy

  validates_presence_of :clients
  validates_numericality_of :amount, greater_than: 0.0

  before_destroy :check_issuing

  def check_issuing
    if status == :issuing
      errors.add :base, '发放中的贷款不允许删除'
      throw :abort
    end
  end

  def status
    if issues.empty?
      :unissued
    elsif issues.sum(:amount) == amount
      :issued
    else
      :issuing
    end
  end

  def remaining
    @remaining ||= amount - issues.sum(:amount)
  end

  def reset_remaining
    @remaining = nil
  end
end

class Issue < ApplicationRecord
  belongs_to :loan

  validates_numericality_of :amount, greater_than: 0.0
  validate :check_amount, on: :create

  def check_amount
    if amount > loan.remaining
      errors.add :base, '支付不能超出贷款总额'
    else
      loan.reset_remaining
    end
  end
end

class DepartmentsController < ApplicationController
  before_action :set_department, only: %i[show edit update destroy]

  # GET /departments
  def index
    @departments = Department.order(:id)
  end

  # GET /departments/1
  def show
  end

  # GET /departments/new
  def new
    @department = Department.new
  end

  # GET /departments/1/edit
  def edit
  end

  # POST /departments
  def create
    @department = Department.new(department_params)

    if @department.save
      redirect_to @department, success: '成功创建部门'
    else
      render :new
    end
  end

  # PATCH/PUT /departments/1
  def update
    if @department.update(department_params)
      redirect_to @department, success: '成功更新部门'
    else
      render :edit
    end
  end

  # DELETE /departments/1
  def destroy
    if @department.destroy
      redirect_to departments_url, success: '部门已删除'
    else
      redirect_back fallback_location: departments_url, alert: '部门删除失败'
    end
  end

  private
    # Use callbacks to share common setup or constraints between actions.
    def set_department
      @department = Department.find(params[:id])
    end

    # Only allow a list of trusted parameters through.
    def department_params
      params.require(:department).permit(%i[name kind])
    end
end

class BranchesController < ApplicationController
  before_action :set_branch, only: %i[show accounts loans staffs edit update destroy]
  before_action :set_staffs, only: %i[show staffs]
  before_action :set_accounts, only: %i[show accounts]
  before_action :set_loans, only: %i[show loans]

  private
    def set_branch
      @branch = Branch.find(params[:id])
    end

    def set_staffs
      @staffs = Staff.where(branch: @branch)
    end

    def set_accounts
      @accounts = Account.where(branch: @branch)
    end

    def set_loans
      @loans = Loan.where(branch: @branch)
    end
end

class DepartmentsController < ApplicationController
  before_action :set_department, only: %i[show staffs edit update destroy]
  before_action :set_staffs, only: %i[show staffs]

  private
    def set_department
      @department = Department.find(params[:id])
    end

    def set_staffs
      @staffs = Staff.where(department: @department)
    end
end

class StaffsController < ApplicationController
  before_action :set_staff, only: %i[show clients edit update destroy]
  before_action :set_clients, only: %i[show clients]

  private
    def staffs
      @staffs ||= Staff.joins(:branch, :department).select('staffs.*', 'branches.name AS branch_name', 'departments.name AS department_name')
    end

    def set_clients
      @clients = Client.joins(:manager).where(manager: @staff)
    end
end

class ClientsController < ApplicationController
  include AccountsHelper

  before_action :set_client, only: %i[edit update destroy accounts loans]

  # GET /clients/1/accounts
  def accounts
    @accounts = Ownership.joins(:account, :branch).where(client: @client).select('ownerships.*', 'accounts.balance AS balance', 'branches.name AS branch_name')
  end

  # GET /clients/1/loans
  def loans
    @loans = Loan.joins(:clients, :branch).where('clients.id = ?', @client.id).select('loans.*', 'branches.name AS branch_name')
  end
end

# GET /clients/new
def new
  @client = Client.new
  @client.build_contact
end

class AccountsController < ApplicationController
  before_action :set_account, only: %i[show edit owners update destroy]

  # GET /accounts/1/owners
  def owners
    @ownership = Ownership.new
    @owners = @account.ownerships.joins(:client).select('ownerships.*', 'clients.name AS client_name')
    @available_clients = Client.where.not(id: Ownership.where(branch_id: @account.branch_id, accountable_type: @account.accountable_type).select(:client_id)).select(:id, :name)
  end
end

# POST /accounts
def create
  params = account_params
  accountable_type = params[:accountable_type]
  @typed_account = accountable_type.safe_constantize.new(params[:accountable_attributes])
  # Insert account and branch info into ownership
  params[:ownerships_attributes]&.each_value do |attr|
    attr.merge!(accountable_type: accountable_type, branch_id: params[:branch_id])
  end
  @account = @typed_account.build_account(params)

  if @account.save
    redirect_to @account, success: '成功创建账户'
  else
    render :new
  end
end

# PATCH/PUT /accounts/1
def update
  if update_params[:branch_id].to_i != @account.branch_id
    client_ids = Ownership.where(account: @account).select(:client_id)
    target_client_ids = Ownership.where(branch_id: update_params[:branch_id], client_id: client_ids)
    unless target_client_ids.empty?
      target_client_ids.joins(:branch, :client).select('branches.name AS branch_name', 'clients.name AS client_name').each do |target|
        errors << "客户 #{target.client_name} 在支行 #{target.branch_name} 已有账户"
      end
      render :edit and return
    end

    Account.transaction do
      Ownership.where(account: @account).update_all(branch_id: update_params[:branch_id])
      @account.update! update_params
    end
  else
    @account.update! update_params
  end
  redirect_to @account, success: '成功更新账户'
rescue ActiveRecord::ActiveRecordError
  render :edit, alert: '账户更新失败'
end

# POST /accounts/1/owners
def create
  owner = Ownership.new ownership_params

  if owner.save
    redirect_to account_owners_url(@account), success: '成功为账户添加客户'
  else
    render 'accounts/owners'
  end
end

class LoansController < ApplicationController
  before_action :set_loan, only: %i[show issues clients add_client destroy_client destroy]
  before_action :set_issues, only: %i[issues]

  # GET /loans/1/issues
  def issues
    @issue = Issue.new
  end

  private
    # Use callbacks to share common setup or constraints between actions.
    def set_loan
      id = params[:id]
      @loan = Loan.left_outer_joins(:branch, :issues).group(:id).select('loans.*', 'branches.name AS branch_name', 'IFNULL(SUM(issues.amount), 0.0) AS amount_issued', 'loans.amount - IFNULL(SUM(issues.amount), 0.0) AS remaining').find(id)
      @clients = Loan.left_outer_joins(:clients).where('loans.id = ?', id)
      @status = status_of @loan
    end

    def set_issues
      @issues = @loan.issues.order(date: :asc, id: :asc)
    end
end

# POST /loans/1/issues
def create
  @issue = Issue.new issue_params

  if @issue.save
    redirect_to loan_issues_url(@loan), success: '成功添加支付'
  else
    redirect_to loan_issues_url(@loan), alert: @issue.errors.full_messages.first
  end
end

class StatsController < ApplicationController
  # GET /
  def home
    @cards = [Branch, Department, Staff, Client, Account, Loan].zip \
      %w[safe department manager user credit-card debt]
  end
end

class StatsController < ApplicationController
  # GET /stats
  def index
    @branches_count = Branch.count
    @accounts_count, @accounts_amount = Account.pluck('COUNT(id)', 'SUM(balance)').first
    Account.group(:accountable_type).pluck('COUNT(id)', :accountable_type).each do |count, type|
      @deposit_accounts_count = count if type == 'DepositAccount'
      @check_accounts_count = count if type == 'CheckAccount'
    end
    @loans_count, @loans_amount = Loan.pluck('COUNT(id)', 'SUM(amount)').first
    loan_statuses = Loan.left_outer_joins(:issues).group(:id).select(:id, %[
      CASE
        WHEN IFNULL(SUM(issues.amount), 0) = 0 THEN 0
        WHEN SUM(issues.amount) = loans.amount THEN 2
        ELSE 1
      END AS status
    ].squish)
    Loan.from(loan_statuses, :statuses).group(:status).select('COUNT(id) AS count', :status).each do |row|
      case row.status
      when 0
        @unissued = row.count
      when 2
        @issued = row.count
      else
        @issuing = row.count
      end
    end
    @issues_amount = Issue.sum(:amount)

    @deposit_card_content = %w[支行 账户 总金额 储蓄账户 支票账户].zip [
      @branches_count, @accounts_count, helpers.currency_value(@accounts_amount),
      (@deposit_accounts_count ||= 0), (@check_accounts_count ||= 0),
    ]
    @loan_card_content = %w[支行 贷款 总金额 已支付 未发放 发放中 已发放].zip [
      @branches_count, @loans_count,
      helpers.currency_value(@loans_amount), helpers.currency_value(@issues_amount),
      (@unissued ||= 0), (@issuing ||= 0), (@issued ||= 0),
    ]
  end
end

class StatsController < ApplicationController
  before_action :set_form, only: %i[deposit loan]

  private

  def search_params
    @url_params ||= request.GET
  end

  def set_form
    @action = search_params[:action]
    @branches = (search_params[:branch] || '').split(' ').map(&:to_i)
    @start_date = Date.parse search_params[:start_date] rescue Date.today.at_beginning_of_month
    @end_date = Date.parse search_params[:end_date] rescue Date.today
    @end_year = Date.today.year
    valid_time_spans = %i[none month quarter year]
    @time_spans = %w[无 月 季度 年].zip valid_time_spans
    @time_span = (search_params[:time_span] || :none).to_sym
    @time_span = :none unless valid_time_spans.include? @time_span
  end
end

class StatsController < ApplicationController
  # GET /stats/deposit
  def deposit
    @start_year = Account.order(open_date: :asc).select(:open_date).first&.open_date&.year || Date.today.year
    return unless @action

    wheres = {}
    selects = ['accounts.*',
               'COUNT(DISTINCT ownerships.client_id) AS clients_count',
               'SUM(balance) AS total_amount',
               'branches.name AS branch_name']
    groups = ['accounts.branch_id']
    orders = {}

    wheres[:branch_id] = @branches unless @branches.empty?

    # Apparently this is MySQL-specific
    case @time_span
    when :year
      selects << 'YEAR(open_date) AS open_year'
      selects << 'YEAR(open_date) AS display_time'
      groups << 'open_year'
      orders = { open_year: :asc, branch_id: :asc }
    when :quarter
      selects << 'YEAR(open_date) AS open_year'
      selects << '((MONTH(open_date) + 2) DIV 3) AS open_quarter'
      selects << 'CONCAT(YEAR(open_date), " Q", (MONTH(open_date) + 2) DIV 3) AS display_time'
      groups << 'open_quarter'
      orders = { open_year: :asc, open_quarter: :asc, branch_id: :asc }
    when :month
      selects << 'YEAR(open_date) AS open_year'
      selects << 'MONTH(open_date) AS open_month'
      selects << 'CONCAT(YEAR(open_date), "-", LPAD(MONTH(open_date), 2, "0")) AS display_time'
      groups << 'open_month'
      orders = { open_year: :asc, open_month: :asc, branch_id: :asc }
    else
      selects << 'open_date'
      selects << 'open_date AS display_time'
      groups << 'open_date'
      orders = { open_date: :asc, branch_id: :asc }
    end

    @query = Account.select(selects).joins(:branch).where(wheres).group(groups).order(orders)
    @data_branches = @query.except(:select, :group, :order).select('DISTINCT branch_id', 'branches.name AS branch_name').order(branch_id: :ASC)
    @query = @query.joins(:ownerships)
    @record_groups = @query.group_by(&:branch_id).sort_by { |k, v| k }
  end

  # GET /stats/loan
  def loan
    @start_year = Issue.order(date: :asc).select(:date).first&.date&.year || Date.today.year
    return unless @action

    wheres = {}
    selects = ['loans.*', 'issues.date AS date',
               '@clients_count := COUNT(DISTINCT clients.id) AS clients_count',
               'SUM(issues.amount / @clients_count) AS total_amount',
               'branches.name AS branch_name']
    groups = ['loans.branch_id', 'loans.id']
    orders = {}

    wheres[:branch_id] = @branches unless @branches.empty?

    case @time_span
    when :year
      selects << 'YEAR(date) AS year'
      selects << 'YEAR(date) AS display_time'
      groups << 'year'
      orders = { year: :asc, branch_id: :asc }
    when :quarter
      selects << 'YEAR(date) AS year'
      selects << '((MONTH(date) + 2) DIV 3) AS quarter'
      selects << 'CONCAT(YEAR(date), " Q", (MONTH(date) + 2) DIV 3) AS display_time'
      groups << 'quarter'
      orders = { year: :asc, quarter: :asc, branch_id: :asc }
    when :month
      selects << 'YEAR(date) AS year'
      selects << 'MONTH(date) AS month'
      selects << 'CONCAT(YEAR(date), "-", LPAD(MONTH(date), 2, "0")) AS display_time'
      groups << 'month'
      orders = { year: :asc, month: :asc, branch_id: :asc }
    else
      selects << 'date'
      selects << 'date AS display_time'
      groups << 'date'
      orders = { date: :asc, branch_id: :asc }
    end

    @query = Loan.select(selects).joins(:branch).where(wheres).group(groups).order(orders)
    @data_branches = @query.except(:select, :group, :order).select('DISTINCT branch_id', 'branches.name AS branch_name').order(branch_id: :ASC)
    @query = @query.joins(:clients, :issues)
    @record_groups = @query.group_by(&:branch_id).sort_by { |k, v| k }
  end
end

module ApplicationHelper
  def active_page(page)
    current_page?(page) ? 'active' : ''
  end
end

module ApplicationHelper
  def title(text)
    content_for :title, text
    text
  end
end

module ApplicationHelper
  def list_items(items, options = {})
    render partial: 'inline_listing', locals: options.merge(items: items)
  end
end

Rails.application.routes.draw do
  root 'stats#home'
  get '/about', to: 'stats#about', as: :about

  resources :branches
  resources :departments
  resources :staffs
  resources :clients
  resources :accounts
  resources :loans, except: %i[edit update]

  scope :branches do
    get ':id/staffs', to: 'branches#staffs', as: :branch_staffs
    get ':id/accounts', to: 'branches#accounts', as: :branch_accounts
    get ':id/loans', to: 'branches#loans', as: :branch_loans
  end

  # ...
end