# 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})