scj/apps/statistics/views.py

# coding=utf-8
import traceback
import json
import tablib

from _mysql_exceptions import IntegrityError
from django.db.models import ProtectedError, Count, Sum, Q
from django.utils import timezone
from django.views.decorators.csrf import csrf_exempt
from django.db import transaction, IntegrityError
from apps.account.decorators import token_required, permission_required
from apps.account.models import Department, User
from apps.base import Formater
from apps.foundation.models import BizLog, Option

from libs.http import JSONResponse, JSONError, DataGridJSONResponse
from libs import utils
from apps.exceptions import CustomError, ExportChange
from django.conf import settings

from apps.goods.models import GoodsGodownEntry, GoodsGodownEntryDetail
from apps.material.models import DeliverDetail, Deliver
from apps.product.models import ProductBase

@csrf_exempt
@permission_required('product.view_department_ledger')
def department_ledger_list(request):
    product_name = request.GET.get('product_name')
    product_model = request.GET.get('product_model')
    product_type = request.GET.get('product_type')
    date = request.GET.get('date')
    exclude_zero = request.GET.get('exclude_zero')

    rows = ProductBase.objects.filter()
    dep_rows = Department.objects.filter()

    if product_name:
        rows = rows.filter(name__icontains=product_name)
    if product_model:
        rows = rows.filter(model__icontains=product_model)
    if product_type:
        rows = rows.filter(type=int(product_type))

    date_begin = ''
    date_end = ''
    if date:
        date = date.split(' - ')
        date_begin = date[0]
        date_end = date[1]

    deliver_rows = DeliverDetail.objects.filter(main__status=settings.PASS)
    entry_rows = GoodsGodownEntryDetail.objects.filter(main__status=settings.PASS)
    if date_begin:
        deliver_rows = deliver_rows.filter(main__create_time__gte=date_begin)
        entry_rows = entry_rows.filter(main__create_time__gte=date_begin)
    if date_end:
        deliver_rows = deliver_rows.filter(main__create_time__lte=date_end + ' 23:59:59')
        entry_rows = entry_rows.filter(main__create_time__lte=date_end + ' 23:59:59')

    if exclude_zero:
        d_ids = deliver_rows.values_list('product_base_id')
        e_ids = entry_rows.values_list('goods__product_base_id')

        rows = rows.filter(Q(id__in=d_ids) | Q(id__in=e_ids))


    #计算合计
    more = {}
    for dep_row in dep_rows:
        d_sum = deliver_rows.filter(main__receiver_department_id=dep_row.id).aggregate(sum_count=Sum('count'), sum_amount=Sum('total_cost'))
        e_sum = entry_rows.filter(main__department_id=dep_row.id).aggregate(sum_count=Sum('count'), sum_amount=Sum('amount'))
        sum_count = (d_sum['sum_count'] or 0) + (e_sum['sum_count'] or 0)
        sum_amount = (d_sum['sum_amount'] or 0) + (e_sum['sum_amount'] or 0)
        more[str(dep_row.id) + '_count'] = Formater.formatCountShow(sum_count)
        more[str(dep_row.id) + '_amount'] = Formater.formatAmountShow(sum_amount)

    rows, total = utils.get_page_data(request, rows)

    data = []
    for row in rows:
        item = {
            'product_name': row.name,
            'product_model': row.model,
            'type_text': row.get_type_display()
        }
        for dep_row in dep_rows:
            if row.type == ProductBase.MATERIAL or row.type == ProductBase.CONSUMABLE:
                d_rows = DeliverDetail.objects.filter(main__status=settings.PASS, product_base_id=row.id,
                                                        main__receiver_department_id=dep_row.id)
                if date_begin:
                    d_rows = d_rows.filter(main__create_time__gte=date_begin)
                if date_end:
                    d_rows = d_rows.filter(main__create_time__lte=date_end + ' 23:59:59')
                sum_rows = d_rows.aggregate(sum_count=Sum('count'), sum_amount=Sum('total_cost'))
            else:
                g_rows = GoodsGodownEntryDetail.objects.filter(main__status=settings.PASS,main__department_id=dep_row.id,
                                                                 goods__product_base_id=row.id)
                if date_begin:
                    g_rows = g_rows.filter(main__create_time__gte=date_begin)
                if date_end:
                    g_rows = g_rows.filter(main__create_time__lte=date_end + ' 23:59:59')
                sum_rows = g_rows.aggregate(sum_count=Sum('count'), sum_amount=Sum('amount'))

            item[str(dep_row.id)+ '_count'] = Formater.formatCountShow(sum_rows['sum_count'] or 0)
            item[str(dep_row.id)+ '_amount'] = Formater.formatAmountShow(sum_rows['sum_amount'] or 0)
        data.append(item)

    return DataGridJSONResponse(data, total, more)

@csrf_exempt
@permission_required('product.export_department_ledger')
def department_ledger_export(request):
    product_name = request.GET.get('product_name')
    product_model = request.GET.get('product_model')
    product_type = request.GET.get('product_type')
    date = request.GET.get('date')
    exclude_zero = request.GET.get('exclude_zero')

    rows = ProductBase.objects.filter()
    dep_rows = Department.objects.filter()

    if product_name:
        rows = rows.filter(name__icontains=product_name)
    if product_model:
        rows = rows.filter(model__icontains=product_model)
    if product_type:
        rows = rows.filter(type=int(product_type))

    date_begin = ''
    date_end = ''
    if date:
        date = date.split(' - ')
        date_begin = date[0]
        date_end = date[1]

    if exclude_zero:
        deliver_rows = DeliverDetail.objects.filter(main__status=settings.PASS)
        entry_rows = GoodsGodownEntryDetail.objects.filter(main__status=settings.PASS)
        if date_begin:
            deliver_rows = deliver_rows.filter(main__create_time__gte=date_begin)
            entry_rows = entry_rows.filter(main__create_time__gte=date_begin)
        if date_end:
            deliver_rows = deliver_rows.filter(main__create_time__lte=date_end + ' 23:59:59')
            entry_rows = entry_rows.filter(main__create_time__lte=date_end + ' 23:59:59')

        d_ids = deliver_rows.values_list('product_base_id')
        e_ids = entry_rows.values_list('goods__product_base_id')

        rows = rows.filter(Q(id__in=d_ids) | Q(id__in=e_ids))

    headers = [u'产品名称',u'产品代码',u'类别']

    for dep_row in dep_rows:
        headers.append(dep_row.name+u'数量')
        headers.append(dep_row.name+u'金额')

    data = []

    for row in rows:
        item = [row.name, row.model, row.get_type_display()]
        for dep_row in dep_rows:
            if row.type == ProductBase.MATERIAL or row.type == ProductBase.CONSUMABLE:
                d_rows = DeliverDetail.objects.filter(main__status=settings.PASS, product_base_id=row.id,
                                                        main__receiver_department_id=dep_row.id)
                if date_begin:
                    d_rows = d_rows.filter(main__create_time__gte=date_begin)
                if date_end:
                    d_rows = d_rows.filter(main__create_time__lte=date_end + ' 23:59:59')
                sum_rows = d_rows.aggregate(sum_count=Sum('count'), sum_amount=Sum('total_cost'))
            else:
                g_rows = GoodsGodownEntryDetail.objects.filter(main__status=settings.PASS,main__department_id=dep_row.id,
                                                                 goods__product_base_id=row.id)
                if date_begin:
                    g_rows = g_rows.filter(main__create_time__gte=date_begin)
                if date_end:
                    g_rows = g_rows.filter(main__create_time__lte=date_end + ' 23:59:59')
                sum_rows = g_rows.aggregate(sum_count=Sum('count'), sum_amount=Sum('amount'))

            item.append(Formater.formatCountShow(sum_rows['sum_count'] or 0))
            item.append(Formater.formatAmountShow(sum_rows['sum_amount'] or 0))
        data.append(item)

    data = tablib.Dataset(*data, headers=headers, title=u"数据")
    filename = utils.attachment_save(data)
    return JSONResponse({'filename': filename})