Docker-Django-Mysqlで作る家計簿

Mysqlで作った家計簿(https://qiita.com/tuk19/items/49db62eacfa68d1a77e4) をアプリにしたかったので、Djangoでつくってみました。

## Docker
```yml:docker-compose.yml
version: '3'
services:
  db:
    build:
      context: .
      dockerfile: myapp/mysql/Dockerfile
    platform: linux/x86_64
    container_name: djangoapp_mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: djangoapp-db
      MYSQL_USER: djangoapp
      MYSQL_PASSWORD: djangoapp
      TZ: 'Asia/Tokyo'
    volumes:
      - data-volume:/var/lib/mysql
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
  web:
    build: .
    container_name: djangoapp
    command: python3 myapp/manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/code
    ports:
      - "8000:8000"
    depends_on:
      - db

volumes:
  data-volume:
```

```Dockerfile:Dcokerfile
FROM python:3
ENV PYTHONUNBUFFERED 1
RUN apt-get update && apt-get -y install default-mysql-client
RUN mkdir /code
WORKDIR /code
COPY requirements.txt /code/
RUN pip install --upgrade pip && pip install -r requirements.txt
COPY . /code/
```

```text:requirements.txt
Django==3.1
mysqlclient
django-pandas
numpy
pandas
```

### MySQLのDocker
```Dockerfile:mysql/Dockerfile
FROM mysql:5.7
COPY myapp/mysql/my.cnf /etc/mysql/conf.d/my.cnf

RUN apt-key adv --keyserver pgp.mit.edu --recv-keys 467B942D3A79BD29
RUN apt-get update
RUN apt-get install -y locales
RUN sed -i -e 's/# \(ja_JP.UTF-8\)/\1/' /etc/locale.gen
RUN locale-gen
RUN update-locale LANG=ja_JP.UTF-8

ENV LC_ALL ja_JP.UTF-8

ENV TZ Asia/Tokyo
ENV LANG=ja\_JP.UTF-8

CMD ["mysqld"]

EXPOSE 3306
```

```python:mysql/my.cnf
[mysqld]

character_set_server=utf8mb4
collation_server=utf8mb4_bin

default_time_zone=SYSTEM
log_timestamps=SYSTEM

default_authentication_plugin=mysql_native_password

[mysql]

default_character_set=utf8mb4

[client]

default_character_set=utf8mb4
```

## プロジェクトアプリケーションディレクトリ
```python:myapp/settings.py
# 家計簿アプリケーションを追加
INSTALLED_APPS = [
    'kakeibo',
    :
    :
]

# データベースにMySQLを設定
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'djangoapp-db',
        'USER': 'root',
        'PASSWORD': 'root',
        'HOST': 'djangoapp_mysql',
        'PORT': '3306',
        'OPTIONS': {
            'charset': 'utf8mb4',
        },
        'TEST': {
            'NAME': "test_djangoapp_db_",
            'MIRROR': "default",
        },
    }
}

# staticディレクトリを追加
STATICFILES_DIRS = (
    [
        os.path.join(BASE_DIR, "static"),
    ]

```

## 家計簿アプリ
```python:admin.py
from django.contrib import admin
from .models import Himoku, Kakeibo

admin.site.register(Himoku)
admin.site.register(Kakeibo)
```

```python:forms.py
from django import forms
from .models import Himoku, Kakeibo

class HimokuForm(forms.ModelForm):
    class Meta:
        model = Himoku
        fields = ['name']

class KakeiboForm(forms.ModelForm):
    class Meta:
        model = Kakeibo
        fields = ['item_id', 'amount', 'income', 'create_date', 'detail']
        widgets = {'create_date': forms.NumberInput(attrs={'type': 'date'})}

class PaginatorForm(forms.Form):
    def __init__(self, *args, **kwargs):
        super(PaginatorForm, self).__init__(*args, **kwargs)
        self.fields['pages'] = forms.ChoiceField(
            label='件数',
            choices=[('-', '-'), (10, 10), (50, 50), (100, 100)],
            widget=forms.Select(attrs={'class': 'form-control'}),
        )

class SearchForm(forms.Form):
    search_himoku = forms.ChoiceField(label='費目', choices=[('-', '-')] + [(item.name, item.name) for item in Himoku.objects.all()], widget=forms.Select(attrs={'class': 'form-control'}))
    search_sday = forms.DateField(label='開始日', required=False, widget=forms.NumberInput(attrs={'class':'form-control', 'type':'date'}))
    search_eday = forms.DateField(label='終了日', required=False, widget=forms.NumberInput(attrs={'class':'form-control', 'type':'date'}))

class CalcForm(forms.Form):
    calc_form = forms.CharField(widget=forms.TextInput(attrs={'class': 'form-control'}))
```

```python:models.py
from django.db import models

class Himoku(models.Model):
    name = models.CharField(max_length=20)
    def __str__(self):
        return self.name

class Kakeibo(models.Model):
    item_id = models.ForeignKey(Himoku, on_delete=models.PROTECT, related_name='himoku_id')
    amount = models.IntegerField(blank=True, null=True)
    income = models.IntegerField(blank=True, null=True)
    create_date = models.DateField()
    detail = models.CharField(max_length=200, blank=True, null=True)
```

```python:urls.py
from django.urls import path
from . import views

urlpatterns = [
    path('', views.kakeibo, name='kakeibo'),
    path('<int:num>', views.kakeibo, name='kakeibo'),
    path('himoku', views.himoku, name='himoku'),
    path('create', views.kakeibo_create, name='kakeibo_create'),
    path('himoku/create', views.himoku_create, name='himoku_create'),
    path('edit/<int:num>', views.kakeibo_edit, name='kakeibo_edit'),
    path('himoku/edit/<int:num>', views.himoku_edit, name='himoku_edit'),
]
```

```python:views.py
from django.shortcuts import render, redirect
from django.core.paginator import Paginator
from django.core.cache import cache
from django.db.models import Q, Count, Sum, Max, Min
from django_pandas.io import read_frame
from .models import Himoku, Kakeibo
from .forms import HimokuForm, KakeiboForm, PaginatorForm, SearchForm, CalcForm

import datetime

def himoku(request):
    data = Himoku.objects.all()
    params = {
        'title': '費目',
        'data': data,
    }
    return render(request, 'kakeibo/himoku.html', params)

def himoku_create(request):
    if (request.method == 'POST'):
        obj = Himoku()
        himoku = HimokuForm(request.POST, instance=obj)
        himoku.save()
        return redirect(to='/kakeibo/himoku')

    params = {
        'title': '費目追加',
        'form': HimokuForm(),
    }
    return render(request, 'kakeibo/himoku_create.html', params)

def kakeibo(request, num=1):
    page_form = PaginatorForm
    search_form = SearchForm

    page = Kakeibo.objects.all().count()
    if cache.get('data'):
        data = cache.get('data')
    else:
        data = Kakeibo.objects.all()

    if request.method == 'GET':
        page_form = PaginatorForm(request.session.get('page_form'))

    if request.method == 'POST':
        if request.POST['mode'] == '__page_form__':
            page = request.POST['pages']
            if page == '-':
                page = Kakeibo.objects.all().count()
            request.session['pages'] = page
            request.session['page_form'] = request.POST
            search_form = SearchForm
        elif request.POST['mode'] == '__search__':
            if "search" in request.POST:
                shimoku = Q()
                ssday = Q()
                seday = Q()
                if request.POST['search_himoku'] != '-':
                    shimoku = Q(item_id__name=request.POST['search_himoku'])

                if request.POST['search_sday']:
                    ssday = Q(create_date__gte=request.POST['search_sday'])

                if request.POST['search_eday']:
                    seday = Q(create_date__lte=request.POST['search_eday'])

                data = Kakeibo.objects.filter(shimoku, ssday, seday)
                data_sum = data.aggregate(Sum("amount"))
                data_max = data.aggregate(Max("amount"))
                data_aggregation = f'合計：{str(data_sum["amount__sum"])}円 \n 最大：{str(data_max["amount__max"])}円'

                request.session['search_form'] = request.POST
                cache.set('data', data)
                request.session['pages'] = data.count()
                request.session['data_aggregation'] = data_aggregation
            elif "CSV" in request.POST:
                data_csv = None
                if cache.get('data'):
                    data_csv = cache.get('data')
                else:
                    data_csv = Kakeibo.objects.all()
                df = read_frame(data_csv)
                now = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
                df.to_csv(f'myapp/kakeibo/tmp/csv/data_{now}.csv')
            page_form = PaginatorForm(request.session.get('page_form'))
            search_form = SearchForm(request.session.get('search_form'))

    if request.session.get('pages'):
        page = request.session.get('pages')

    page_message = f'{page}件表示します'
    paginator = Paginator(data.order_by('id').reverse(), page)

    params = {
        'title': '家計簿',
        'data': paginator.get_page(num),
        'page_form': page_form,
        'page_message': page_message,
        'search_form': search_form,
    }

    if request.session.get('data_aggregation'):
        params['data_aggregation'] = request.session['data_aggregation']

    return render(request, 'kakeibo/kakeibo.html', params)

def kakeibo_create(request):
    message = '最新のデータを表示します。'
    if (request.method == 'GET'):
        request.session['calc_sum'] = 0
    elif (request.method == 'POST'):
        if 'calc' in request.POST:
            calc_list = request.POST['calc_form'].split()
            calc_sum = sum(map(int, calc_list))
            request.session['calc_sum'] = calc_sum
        else:
            obj = Kakeibo()
            kakeibo = KakeiboForm(request.POST, instance=obj)
            kakeibo.save()
            message = '以下のデータを追加しました。'
            request.session['calc_sum'] = 0

    params = {
        'title': '家計簿追加',
        'form': KakeiboForm(),
        'item': Kakeibo.objects.last(),
        'message': message,
        'calc_form': CalcForm,
    }


    if request.session['calc_sum'] != 0:
        params['form'] = KakeiboForm({'amount': int(request.session['calc_sum'])})

    return render(request, 'kakeibo/kakeibo_create.html', params)

def kakeibo_edit(request, num):
    obj = Kakeibo.objects.get(id=num)
    if (request.method == 'GET'):
        request.session['edit_calc_sum'] = 0
    elif (request.method == 'POST'):
        if 'calc' in request.POST:
            edit_calc_list = request.POST['calc_form'].split()
            edit_calc_sum = sum(map(int, edit_calc_list))
            request.session['edit_calc_sum'] = edit_calc_sum
        else:
            kakeibo = KakeiboForm(request.POST, instance=obj)
            kakeibo.save()
            return redirect(to='/kakeibo')

    params = {
        'title': '家計簿修正',
        'id': num,
        'form': KakeiboForm(instance=obj),
        'calc_form': CalcForm,
    }

    if request.session['edit_calc_sum'] != 0:
        params['form'] = KakeiboForm({
                            'item_id': obj.item_id,
                            'amount': int(request.session['edit_calc_sum']),
                            'income': obj.income,
                            'create_date': obj.create_date,
                            'detail': obj.detail
                        })

    return render(request, 'kakeibo/kakeibo_edit.html', params)

def himoku_edit(request, num):
    obj = Himoku.objects.get(id=num)
    if (request.method == 'POST'):
        himoku = HimokuForm(request.POST, instance=obj)
        himoku.save()
        return redirect(to='/kakeibo/himoku')

    params = {
        'title': '費目修正',
        'id': num,
        'form': HimokuForm(instance=obj)
    }
    return render(request, 'kakeibo/himoku_edit.html', params)
```

### templates
```html:layout.html
{% load static %}
<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>{% block title %}{% endblock %}</title>
    <link rel="stylesheet" type="text/css" href="{% static 'kakeibo/css/kakeibo.css' %}">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
    <script type="text/javascript" src="{% static 'kakeibo/js/kakeibo.js' %}"></script>
</head>
<body>
    <div>
        {% block header %}
        {% endblock %}
    </div>
    <div>
        <nav>
            <ul class="link_nav flex">
                <li><a href="{% url 'kakeibo' %}">家計簿</a></li>
                <li><a href="{% url 'himoku' %}">費目</a></li>
                <li><a href="{% url 'kakeibo_create' %}">家計簿追加</a></li>
                <li><a href="{% url 'himoku_create' %}">費目追加</a></li>
            </ul>
        </nav>
    </div>

    <div>
        {% block content %}
        {% endblock %}
    </div>
    <div>
        <nav>
            <ul class="link_nav flex">
                <li><a href="{% url 'kakeibo' %}">家計簿</a></li>
                <li><a href="{% url 'himoku' %}">費目</a></li>
                <li><a href="{% url 'kakeibo_create' %}">家計簿追加</a></li>
                <li><a href="{% url 'himoku_create' %}">費目追加</a></li>
            </ul>
        </nav>
    </div>
</body>
</html>
```

```html:himoku.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<div>
    <table class="data_table">
        <tr>
            <th>ID</th>
            <th>名前</th>
            <th>修正</th>
        </tr>
        {% for item in data %}
        <tr>
            <td>{{ item.id }}</td>
            <td>{{ item.name }}</td>
            <td><a href="{% url 'himoku_edit' item.id %}">修正</a></td>
        </tr>
        {% endfor %}
    </table>
</div>
{% endblock %}
```

```himoku_create.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<form action="{% url 'himoku_create' %}", method="post">
    {% csrf_token %}
    <table>
        {{ form.as_table }}
        <tr>
            <th>
                <td><input type="submit" value="登録"></td>
            </th>
        </tr>
    </table>
</form>
{% endblock %}
```

```html:himoku_edit.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<form action="{% url 'himoku_edit' id %}", method="post">
    {% csrf_token %}
    <table>
        {{ form.as_table }}
        <tr>
            <th>
                <td><input type="submit" value="修正"></td>
            </th>
        </tr>
    </table>
</form>
{% endblock %}
```

```kakeibo.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<div>
    <form action="{% url 'kakeibo' %}", method="POST">
        {% csrf_token %}
        <input type="hidden" name="mode" value="__page_form__">
        {{ page_form.as_p }}
        <button type="submit">ページ変更</button>
    </form>
</div>
<div>
    <p>{{ page_message }}</p>
</div>
<div>
    <form action="{% url 'kakeibo' %}", method="POST">
        {% csrf_token %}
        <input type="hidden" name="mode" value="__search__">
        {{ search_form.as_p }}
        <button type="submit" name="search">検索</button>
        <button type="submit" name="CSV">CSV出力</button>
    </form>
</div>
{% if data_aggregation %}
<div>
    {{ data_aggregation }}
</div>
{% endif %}
<div>
    <table id="kakeibo_table" class="data_table">
        <tr>
            <!-- <th><span id="kakeibo_table_id" onclick="id_click()">ID</span></th> -->
            <th id="kakeibo_table_id">ID<span id="kakeibo_table_id_order"></span></th>
            <th id="kakeibo_table_himoku">費目名<span id="kakeibo_table_himoku_order"></span></th>
            <th id="kakiebo_table_amount">出費<span id="kakeibo_table_amount_order"></span></th>
            <th id="kakeibo_table_income">収入<span id="kakeibo_table_income_order"></span></th>
            <th id="kakeibo_table_createdate">作成日<span id="kakeibo_table_createdate_order"></span></th>
            <th>詳細</th>
            <th>修正</th>
        </tr>
        {% for item in data %}
        <tr>
            <td class="kakeibo_tr_id">{{ item.id }}</td>
            <td class="kakeibo_tr_himoku">{{ item.item_id.name }}</td>
            <td align="right"><span class="kakeibo_tr_amount" >{{ item.amount }}</span>円</td>
            {% if item.income %}
            <td class="kakeibo_tr_income">{{ item.income }}</td>
            {% else %}
            <td></td>
            {% endif %}
            <td class="kakeibo_tr_createdate">{{ item.create_date|date:"Y年m月d日" }}</td>
            {% if item.detail %}
            <td>{{ item.detail }}</td>
            {% else %}
            <td></td>
            {% endif %}
            <td><a href="{% url 'kakeibo_edit' item.id %}">修正</a></td>
        </tr>
        {% endfor %}
    </table>
    <div>
        <ul class="flex">
            {% if data.has_previous %}
            <li><a href="{% url 'kakeibo' %}">&laquo; first</a></li>
            <li><a href="{% url 'kakeibo' %}{{ data.previous_page_number }}">&laquo; prev</a></li>
            {% else %}
            <li><a>&laquo; first</a></li>
            <li><a>&laquo; prev</a></li>
            {% endif %}
            <li><a>{{ data.number }}/{{ data.paginator.num_pages }}</a></li>
            {% if data.has_next %}
            <li><a href="{% url 'kakeibo' %}{{ data.next_page_number }}">next &raquo;</a></li>
            <li><a href="{% url 'kakeibo' %}{{ data.paginator.num_pages }}">last &raquo;</a></li>
            {% else %}
            <li><a>next &raquo;</a></li>
            <li><a>last &laquo;</a></li>
            {% endif %}
        </ul>
    </div>
</div>
{% endblock %}
```

```html:kakeibo_create.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<form action="{% url 'kakeibo_create' %}", method="post">
    {% csrf_token %}
    <!-- <input type="hidden" name="mode" value="__calc_form__"> -->
        <div class="flex">
            {{ calc_form.as_p }}
            <p><input type="submit" name="calc" value="計算"></p>
        </div>
    </div>
</form>

<form action="{% url 'kakeibo_create' %}", method="post">
    {% csrf_token %}
    <!-- <input type="hidden" name="mode" value="__kakeibo_form__"> -->

        <table>
            {{ form.as_table }}
            <tr>
                <th>
                    <td><input type="submit" value="登録"></td>
                </th>
            </tr>
        </table>
</form>

<div>
    <p>{{ calc_sum }}</p>
</div>
<div>
    <p>{{ message }}</p>
    <table class="data_table">
        <tr>
            <th>ID</th>
            <th>費目名</th>
            <th>出費</th>
            <th>収入</th>
            <th>作成日</th>
            <th>詳細</th>
            <th>修正</th>
        </tr>
        <tr>
            <td>{{ item.id }}</td>
            <td>{{ item.item_id.name }}</td>
            <td>{{ item.amount }}</td>
            <td>{{ item.income }}</td>
            <td>{{ item.create_date|date:"Y年m月d日" }}</td>
            <td>{{ item.detail }}</td>
            <td><a href="{% url 'kakeibo_edit' item.id %}">修正</a></td>
        </tr>
    </table>
</div>
{% endblock %}
```

```html:kakeibo_edit.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<form action="{% url 'kakeibo_edit' id %}", method="post">
    {% csrf_token %}
    <!-- <input type="hidden" name="mode" value="__calc_form__"> -->
        <div class="flex">
            {{ calc_form.as_p }}
            <p><input type="submit" name="calc" value="計算"></p>
        </div>
    </div>
</form>

<form action="{% url 'kakeibo_edit' id %}", method="post">
    {% csrf_token %}
    <table>
        {{ form.as_table }}
        <tr>
            <th>
                <td><input type="submit" value="修正"></td>
            </th>
        </tr>
    </table>
</form>
{% endblock %}
```

## static
```css:kakeibo.css
h2 {
    color: red;
}

.data_table {
    width: 100%;
    font-size: larger;
    border-spacing: 0px;
}

.data_table th {
    color: white;
    background-color: blue;
}

.data_table td {
    background-color: aquamarine;
    padding-top: 10px;
}

.link_nav {
    list-style-type: none;
    font-size: larger;
}

.flex {
    display: flex;
    margin: 0;
    padding: 0;
}

.flex li {
    margin: 0;
    width: 100%;
}
```