djangoをdockerコンテナで利用(15) - djangoでsql直接発行

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109

from django.db import models
from django.utils import timezone
from pymysql import NULL

from datetime import datetime,timedelta     ## テーブルの日付フィールドを扱うため

## マスターテーブル使うため
from gvisWebApp.models.GvisMaster import GvisMstKeihishubetsu,GvisMstKamoku

## マスターテーブル絞り込みのため
from django.db.models import Q

class GvisKeihi(models.Model):

    ## 参考URL https://note.nkmk.me/python-datetime-now-today/
    dt_year = datetime.now().year
    dt_mon = datetime.now().month
    dt_day = datetime.now().day
    dt_defdate = str(dt_year) + '-' + str(dt_mon) + '-' + str(dt_day) + ' ' + '00:00:00'

    workshubetsu_choicesDB=[(i.keihiname, i.keihisetsumei ) for i in GvisMstKeihishubetsu.objects.all().order_by('keihino')]

    kamoku_choicesDB=[
        ("%03d" % i.kamokuno + "-" + i.kamokuname ,
         "%03d" % i.kamokuno + "-" + i.kamokuname) for i in GvisMstKamoku.objects.all().order_by('hyojiorder')]

    workperiod = models.DateTimeField(verbose_name='業務年月',db_column='workPeriod', blank=True, null=True,
        default=datetime.strptime(dt_defdate,'%Y-%m-%d %H:%M:%S'))  # Field name made lowercase.
    def __str__(self):
        return self.workperiod.strftime('%c')

    workshubetsu = models.CharField(verbose_name='経費種別',db_column='workShubetsu', max_length=4,
        choices=workshubetsu_choicesDB ,blank=False, null=False)  # Field name made lowercase.
    def __str__(self):
        return self.workshubetsu

    workpriority = models.IntegerField(verbose_name='経費No-優先順位',db_column='workPriority', blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return self.workpriority

    keihi_date = models.DateTimeField(verbose_name='経費日付',db_column='Keihi_date', blank=True, null=True,
        default=datetime.strptime(dt_defdate,'%Y-%m-%d %H:%M:%S'))  # Field name made lowercase.
    def __str__(self):
        return self.keihi_date.strftime('%c')

    kamoku = models.CharField(verbose_name='科目',db_column='Kamoku', max_length=24,
        choices=kamoku_choicesDB ,blank=False, null=False)  # Field name made lowercase.
    def __str__(self):
        return self.kamoku

    tehai = models.CharField(verbose_name='手配方法・名称',db_column='Tehai', max_length=1000, blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return self.tehai

    kng = models.DecimalField(verbose_name='金額',db_column='Kng', max_digits=12, decimal_places=4, blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return self.kng

    biko = models.CharField(verbose_name='備考',db_column='Biko', max_length=100, blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return self.biko

    ins_date = models.DateTimeField(blank=True, null=True,
        default=datetime.strptime(dt_defdate,'%Y-%m-%d %H:%M:%S')
    )
    def __str__(self):
        return self.ins_date.strftime('%c')

    ins_user = models.CharField(max_length=100, blank=True, null=True)
    def __str__(self):
        return self.ins_user

    upd_date = models.DateTimeField(blank=True, null=True,
        default=timezone.datetime.min
    )
    def __str__(self):
        return self.upd_date.strftime('%c')

    upd_user = models.CharField(max_length=100, blank=True, null=True)
    def __str__(self):
        return self.upd_user

    def get_yyyymm(self):
        return self.workperiod.strftime('%Y/%m')

    def get_keihi_date(self):
        return self.keihi_date.strftime('%d(%a)')

    def get_keihi_date_dayofweek(self):
        return self.keihi_date.strftime('%a')

    def get_workshubetsu(self):

        findkey = self.workshubetsu
        keihisetsumei=[(i.keihisetsumei) for i in GvisMstKeihishubetsu.objects.filter(Q(keihiname=findkey))]

        return keihisetsumei[0]

    ### 定義してtemplatesには表示こそできても、もviewsの中でfilterには使えず・・・。
    def get_keihitaisho(self):

        findkey = self.kamoku[4:]
        keihitaisho=[(i.keihitaisho) for i in GvisMstKamoku.objects.filter(Q(kamokuname=findkey))]

        return keihitaisho[0]

    class Meta:
        managed = False
        db_table = 'GVIS_keihi'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

class GvisMstKeihishubetsu(models.Model):
    keihino = models.IntegerField(db_column='keihiNo')  # Field name made lowercase.
    def __str__(self):
        return self.keihino

    keihiname = models.CharField(db_column='keihiName', max_length=20, blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return self.keihiname

    keihisetsumei = models.CharField(db_column='keihiSetsumei', max_length=200, blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return self.keihisetsumei

    focolor = models.CharField(db_column='foColor', max_length=7, blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return self.focolor

    bgcolor = models.CharField(db_column='bgColor', max_length=7, blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return self.bgcolor

    class Meta:
        managed = False
        db_table = 'GVIS_mst_keihishubetsu'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

class GvisMstKamoku(models.Model):
    kamokuno = models.IntegerField(db_column='kamokuNo')  # Field name made lowercase.
    def __str__(self):
        return str(self.kamokuno)

    kamokuname = models.CharField(db_column='kamokuName', max_length=20, blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return self.kamokuname

    kamokusetsumei = models.CharField(db_column='kamokuSetsumei', max_length=200, blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return self.kamokusetsumei

    hyojiorder = models.IntegerField(db_column='hyojiOrder', blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return str(self.hyojiorder)

    keihitaisho = models.IntegerField(db_column='keihiTaisho', blank=True, null=True)  # Field name made lowercase.
    def __str__(self):
        return str(self.keihitaisho)

    class Meta:
        managed = False
        db_table = 'GVIS_mst_kamoku'

1

KingakuGokei = object_list.filter(Q(get_keihitaisho=1)).aggregate(sum=Sum('kng'))['sum']

1

KingakuGokei = gv_Func_GetKeihi_ofMonth(workperiodYYYY,workperiodMM,self.request)

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158

from django.contrib.auth.decorators import login_required
from django.http import request
from django.utils import timezone
                                            ## 関数でログイン必須にするため

from ..models import GvisKeihi               ## DB読み込みのため

from django.views.generic import (
                            ListView,       ## リスト表示のため
                            DetailView,     ## 単一レコードの詳細情報表示のため
                            CreateView,     ## レコードの作成のため
                            DeleteView,     ## レコードの削除のため
                            UpdateView,     ## レコードの更新のため
)
                                  
from django.db.models import (  Q,          ## 検索のため
                                Max,        ## 最大値取得のため      
                                Sum,        ## 合計表示させるため
)

from django.contrib.auth.mixins import LoginRequiredMixin
                                            ## クラスビューをログイン必須にするため
                                            ## 参考URL  https://e-tec-memo.herokuapp.com/article/62/
                                            ##          https://stackoverflow.com/questions/10275164/django-generic-views-using-decorator-login-required

from django.urls import reverse,reverse_lazy
                                            ## urlを返すreverse/reverse_lazyを使うため

from ..forms import GvisKeihiForm            ## 検索フォームを使うため

from django.contrib import messages         ## 通知メッセージを表示するため

from datetime import datetime               ## テーブルの日付フィールドを扱うため

from ..common.common import *               ## 共通関数

from django.shortcuts import redirect       ## ページ表示のため

class GvisKeihiList(LoginRequiredMixin, ListView):

    model = GvisKeihi
    template_name = 'gvisWebApp/gvis_500_KeihiKensaku.html'
    paginate_by = 60

    ## 参考URL https://intellectual-curiosity.tokyo/2019/02/27/djangoでlistviewを用いて検索画面を実装する方法/
    def post(self, request, *args, **kwargs):
        keihi_form_value = [
            self.request.POST.get('workperiodYYYY', None),
            self.request.POST.get('workperiodMM', None),
        ]
        request.session['keihi_form_value'] = keihi_form_value

        # 検索時にページネーションに関連したエラーを防ぐ
        self.request.GET = self.request.GET.copy()
        self.request.GET.clear()
        return self.get(request, *args, **kwargs)

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)

        # 年月指定のデフォルトを設定しておく(今日の年月)
        workperiodYYYY = gvis_today()[0:4]
        workperiodMM = gvis_today()[5:7]

        # sessionに値がある場合、その値をセットする。（ページングしてもform値が変わらないように）
        if 'keihi_form_value' in self.request.session:
            keihi_form_value = self.request.session['keihi_form_value']
            workperiodYYYY = keihi_form_value[0]
            workperiodMM = keihi_form_value[1]
        default_data = {'workperiodYYYY': workperiodYYYY,   # 経費年
                        'workperiodMM': workperiodMM,       # 経費月
                        }
        find_form = GvisKeihiForm(initial=default_data) # 検索フォーム
        context['find_form'] = find_form

        ## 集計結果をセットする

        ## 参考URL
        ## https://note.com/marketscience/n/nf02a49c65554

        ## 年月で対象を絞る
        object_list = GvisKeihi.objects.filter(
            workperiod__range = [   gvis_startdayOfMonth(workperiodYYYY,workperiodMM),
                                    gvis_finaldayOfMonth(workperiodYYYY,workperiodMM)
                                ]
        )

        ## 経費合計の取得がどうしてもうまく行かないので参照sql直接発行
        ### KingakuGokei = object_list.filter(Q(get_keihitaisho=1)).aggregate(sum=Sum('kng'))['sum']
        KingakuGokei = gv_Func_GetKeihi_ofMonth(workperiodYYYY,workperiodMM,self.request)

        ## 口座から現金移動(元入含)合計の取得
        GeninIdou = object_list.filter(Q(kamoku='002-元入金') | Q(kamoku='032-現金移動')).aggregate(sum=Sum('kng'))['sum']

        ## 医療費控除合計の取得
        iryo1 = object_list.filter(Q(kamoku='004-医療費控除')).aggregate(sum=Sum('kng'))['sum']

        ## 医療費控除(貯金切り崩し)合計の取得
        iryo2 = object_list.filter(Q(kamoku='033-医療費控除(貯金切り崩し)')).aggregate(sum=Sum('kng'))['sum']

        ## 国民健康保険合計の取得
        kokumin = object_list.filter(Q(kamoku='034-国民健康保険')).aggregate(sum=Sum('kng'))['sum']

        ## 市税合計の取得
        shizei = object_list.filter(Q(kamoku='035-市税')).aggregate(sum=Sum('kng'))['sum']

        ## 固定資産税合計の取得
        kotei = object_list.filter(Q(kamoku='036-固定資産税')).aggregate(sum=Sum('kng'))['sum']

        ## 売上合計の取得
        uriage = object_list.filter(Q(kamoku='101-現金売上') | Q(kamoku='102-預金売上')).aggregate(sum=Sum('kng'))['sum']

        context.update({
                'sum01': KingakuGokei,         # 経費合計
                'sum02': GeninIdou,            # 口座から現金移動(元入含)
                'sum03': iryo1,                # 医療費控除合計
                'sum04': iryo2,                # 医療費控除(貯金切り崩し)合計
                'sum05': kokumin,              # 国民健康保険合計
                'sum06': shizei,               # 市税
                'sum07': kotei,                # 固定資産税
                'sum08': uriage,               # 売上合計
                'sum09': object_list.count(),  # 件数
        })

        return context

    ## 参考URL  https://noumenon-th.net/programming/2019/12/18/django-search/
    ##          https://codelab.website/django-queryset-filter/
    ##          https://qiita.com/Hyperion13fleet/items/1a0369f4f5d523be5870
    def get_queryset(self):

        # sessionに値がある場合、その値でクエリ発行する。
        # シリアル種別はicontains（部分一致の大文字小文字区別なし）で検索する

        if 'keihi_form_value' in self.request.session:
            keihi_form_value = self.request.session['keihi_form_value']

            workperiodYYYY = keihi_form_value[0]
            workperiodMM = keihi_form_value[1]

            ## まずは全件取得
            object_list = GvisKeihi.objects.all()

            ## 年月で絞って日付が新しい順に並べ替え
            object_list = GvisKeihi.objects.filter(
                workperiod__range = [   gvis_startdayOfMonth(workperiodYYYY,workperiodMM),
                                        gvis_finaldayOfMonth(workperiodYYYY,workperiodMM)
                                    ]
            ).order_by('workshubetsu','keihi_date','kamoku','workpriority')

            ## ログ出力
            key = workperiodYYYY + "-" + workperiodMM
            gv_logwrite('info',self.request,'dj-経費検索','(' + key + ')')

            return object_list
        else:
            # 何も返さない
            return GvisKeihi.objects.none()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

select sum(Kng)
from (
    select 
        GVIS_keihi.Kamoku,
        GVIS_keihi.Tehai,
        GVIS_keihi.Kng,
        (
            select keihitaisho
            from GVIS_mst_kamoku
            where concat(concat(LPAD(GVIS_mst_kamoku.kamokuNo,3,'0'),'-'),GVIS_mst_kamoku.KamokuName) = GVIS_keihi.Kamoku
        ) as keihitaisho
    from GVIS_keihi
    WHERE year(GVIS_keihi.workPeriod) = 2022 
     and month(GVIS_keihi.workPeriod) = 1
) as q
where keihitaisho = 1 ;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

def gv_Func_GetKeihi_ofMonth(year, month, arg3):

    keihi = 0

    ## モデルを使わずにsql発行
    try:
        con = connections['default']
        cursor = con.cursor()

        SQLstr = ""
        SQLstr += "select sum(Kng) "
        SQLstr += "from ( "
        SQLstr += "    select "
        SQLstr += "        GVIS_keihi.Kamoku, "
        SQLstr += "        GVIS_keihi.Tehai, "
        SQLstr += "        GVIS_keihi.Kng, "
        SQLstr += "        ( "
        SQLstr += "            select keihitaisho "
        SQLstr += "            from GVIS_mst_kamoku "
        SQLstr += "            where concat(concat(LPAD(GVIS_mst_kamoku.kamokuNo,3,'0'),'-'),GVIS_mst_kamoku.KamokuName) "
        SQLstr += "                = GVIS_keihi.Kamoku "
        SQLstr += "        ) as keihitaisho "
        SQLstr += "    from GVIS_keihi "
        SQLstr += "    WHERE year(GVIS_keihi.workPeriod) = " + year

        if int(month) > 0 :
            SQLstr += "    and month(GVIS_keihi.workPeriod) = " + month

        SQLstr += ") as q "
        SQLstr += "where keihitaisho = 1 "

        cursor.execute(SQLstr)
        ret = cursor.fetchall()
        keihi = 0 if ret[0][0] is None else ret[0][0]

    except Exception as e:
        key = year + month
        gv_logwrite('error',arg3,'dj-経費検索','key=(' + key + ')' + 'exeption={' + str(e) + '}')


    ## 指定年月の経費を返す
    return keihi

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

{% extends 'gvisWebApp/base.html' %}
{% load static %}
{% load humanize %}   {# 数字の値を3桁のカンマ区切りで表示したいため追記 #}  
{% load gvis_tags %}  {# templatetagフォルダにカスタムタグ作って使うために追記 #}

{% block content %}
<center>

  <h1>検索条件</h1>
  <form method="POST">
    {% csrf_token %}

    <fieldset style="width:60%">
      <legend align="center">検索フォーム</legend>

      {% for field in find_form %}
        {{ field.label }}:{{ field }}
        <br>
      {% endfor %}
      <input class="gvis_button" type="submit" id="submit1" name="submit1" value="検索">
    </fieldset>
  </form>
  <br>

  {% if messages %}
    {% for message in messages %}
      <h2> {{ message }}</h2>
    {% endfor %}
  {% endif %}

  <h1>検索結果</h1>

    <fieldset style="width:90%">
    {% if object_list|length == 0 %}
        <p>検索結果が存在しません。</p>
    {% else %}
      {% if is_paginated %}
        {% include 'gvisWebApp/pagination.html' %}
      {% endif %}

      <table border=4 align=center width="100%">
        <tr>
          <th>年月</th>
          <th>編集</th>
          <th>操作</th>
          <th>経費種別</th>
          <th>経費No</th>
          <th>経費日付</th>
          <th>科目</th>
          <th>手配方法・名称</th>
          <th>金額</th>
          <th>備考</th>
        </tr>
        {% for GvisKeihi in object_list %}
          <tr>
            <td>{{GvisKeihi.get_yyyymm}}</td>
            <td>
              {# <a href="{% url 'gvisWebApp:gvis_404_KatsudoUpdate' GvisKeihi.pk %}">編集</a> #}
              <a><br><a>
              <a><br><a>
              {# <a href="{% url 'gvisWebApp:gvis_403_KatsudoDelete' GvisKeihi.pk %}">削除</a> #}
            </td>
            <td>
              {% comment %}
              <form id="CPform" name="CPform" method="GET" style="text-align:left" 
                action="{% url 'gvisWebApp:GvisKeihiCopy' %}" onSubmit="return copyPopup(); " >

                <input class="darkmode-ignore" type="submit" name="COPY_btn" value="行コピー">
                <input type="hidden" name="yyyymm" value="{{GvisKeihi.get_yyyymm|slice:"0:4"}}{{GvisKeihi.get_yyyymm|slice:"5:7"}}">
                <input type="hidden" name="pr" value="{{ GvisKeihi.workpriority }}">
              </form>
              {% endcomment %}
            </td>

            {% if GvisKeihi.workshubetsu|stringformat:"s" == "1yan" %}
              <td bgcolor="#E9967A"><font color="#ffffff">{{GvisKeihi.get_workshubetsu}}</font></td>
            {% elif GvisKeihi.workshubetsu|stringformat:"s" == "2kgc" %}
              <td bgcolor="#8FBC8F"><font color="#ffffff">{{GvisKeihi.get_workshubetsu}}</font></td>
            {% elif GvisKeihi.workshubetsu|stringformat:"s" == "3ikk" %}
              <td bgcolor="#00CED1"><font color="#ffffff">{{GvisKeihi.get_workshubetsu}}</font></td>
            {% else %}
              <td>{{GvisKeihi.workshubetsu}}{{GvisKeihi.get_workshubetsu}}</td>
            {% endif %}

            <td>{% gvis_zeropad4 GvisKeihi.workpriority %}</td>

            {% if GvisKeihi.get_keihi_date_dayofweek|stringformat:"s" == "Sat" %}
              <td bgcolor="#ff1493"><font color="#ffffff">{{GvisKeihi.get_keihi_date}}</font></td>
            {% elif GvisKeihi.get_keihi_date_dayofweek|stringformat:"s" == "Sun" %}
              <td bgcolor="#ff1493"><font color="#ffffff">{{GvisKeihi.get_keihi_date}}</font></td>
            {% else %}
              <td>{{GvisKeihi.get_keihi_date}}</td>
            {% endif %}

            <td>{{GvisKeihi.kamoku }}</td>
            <td>{{GvisKeihi.tehai}}</td>
            <td align=right>{{GvisKeihi.kng|floatformat|intcomma }}円</td>
            <td>{{GvisKeihi.biko}}</td>

          </tr>
        {% endfor %} 

        <tr align=right bgcolor="#cccccc">
          <td></td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
          <td>
            {% if sum01 %}
              経費合計={{sum01|floatformat|intcomma}}円 <br> 
            {% endif %}

            {% if sum02 %}
              口座から現金移動(元入含)={{sum02|floatformat|intcomma}}円 <br> 
            {% endif %}

            {% if sum03 %}
              医療費控除合計 ={{sum03|floatformat|intcomma}}円 <br> 
            {% endif %}

            {% if sum04 %}
              医療費控除(貯金切り崩し)合計 ={{sum04|floatformat|intcomma}}円 <br> 
            {% endif %}

            {% if sum05 %}
              国民健康保険 ={{sum05|floatformat|intcomma}}円 <br> 
            {% endif %}

            {% if sum06 %}
              市税 ={{sum06|floatformat|intcomma}}円 <br> 
            {% endif %}

            {% if sum07 %}
              固定資産税 ={{sum07|floatformat|intcomma}}円 <br> 
            {% endif %}

            {% if sum08 %}
              売上合計 ={{sum08|floatformat|intcomma}}円 <br> 
            {% endif %}

          </td>
          <td>件数={{sum09|intcomma}}件</td>
        </tr>

      </table>

      {% if is_paginated %}
        {% include 'gvisWebApp/pagination.html' %}
      {% endif %}

    {% endif %}
    </fieldset>