# coding=utf-8 import traceback import json from django.db import models,connection from libs.utils import strftime, strfdate from django.conf import settings from django.utils import timezone from apps.base import Formater from django.db.models import Q, Sum, F from django.views.decorators.csrf import csrf_exempt from django.db import transaction,IntegrityError from django.db.models import ProtectedError from apps.account.decorators import token_required, permission_required, isHasPermissions from apps.foundation.models import BizLog, Option from apps.goods.filters import GoodsFilter, GoodsGodownEntryFilter, GoodsProductFilter from apps.goods.models import Goods, GoodsGodownEntry, GoodsGodownEntryDetail from apps.goods.resources import GoodsResource, GoodsImporter, GoodsGodownEntryResource, GoodsGodownEntryDetailResource, \ GoodsGodownEntryImporter, GoodsProductResource, GodownEntryQueryResource from apps.goods.serializers import GoodsSerializer, GoodsGodownEntrySerializer, GoodsGodownEntryDetailSerializer, GoodsProductSerializer from apps.product.models import ProductBase from apps.product.serializers import ProductBaseSerializer from apps.warehouse.biz import BizWarehouse, GetWarehouseSrockRecord from apps.warehouse.models import WarehouseStock, Warehouse, WarehouseRecord, WarehouseStockRecord, InventoryDetail, WarehouseRecordDetail from apps.order.models import GoodsDeliver, GoodsDeliverReturnDetail, GoodsDeliverDetail, GoodsDeliverReturn from libs.http import JSONResponse, JSONError, DataGridJSONResponse from libs import utils from apps.exceptions import CustomError, ExportChange @permission_required('goods.view_goods') def goods_list(request): f = GoodsFilter(request.GET, queryset=Goods.objects.filter()) rows, total = utils.get_page_data(request, f.qs) serializer = GoodsSerializer(rows, many=True) return DataGridJSONResponse(serializer.data, total) @permission_required('goods.export_goods') def goods_export(request): f = GoodsFilter(request.GET, queryset=Goods.objects.filter()) serializer = GoodsSerializer(f.qs, many=True) export_data = ExportChange.dict_to_obj(serializer) export_data = GoodsResource().export(export_data) filename = utils.attachment_save(export_data) return JSONResponse({'filename': filename}) @csrf_exempt @permission_required('goods.add_goods') def goods_save(request): id = request.GET.get('id') data = json.loads(request.body) try: with transaction.atomic(): product_base_id = None data['type'] = ProductBase.GOODS serializer = GoodsSerializer.factory(request.user, data, id) if serializer.instance: product_base_id = serializer.instance.product_base_id data['standard'] = data['base_standard'] pb = ProductBaseSerializer.factory(request.user,data,product_base_id) pb = pb.validSave() data['product_base'] = pb.id serializer.validSave() except CustomError, e: return JSONError(e.get_error_msg()) except Exception, e: traceback.print_exc() return JSONError(u'保存失败！') return JSONResponse() @permission_required('goods.delete_goods') def goods_delete(request): id = int(request.GET.get('id')) try: with transaction.atomic(): goods = Goods.getById(id) product_base_id = goods.product_base.id BizLog.objects.addnew(request.user, BizLog.DELETE, u"删除成品[%s]，id=%d" % (goods.product_base.name, goods.id)) goods.delete() WarehouseStock.removeStockByProduct(goods.product_base) ProductBase.objects.filter(id=product_base_id).delete() except CustomError, e: return JSONError(e.get_error_msg()) except ProtectedError: return JSONError(u'该成品已被使用，禁止删除！') except IntegrityError: return JSONError(u'该成品已被使用，禁止删除！') except Exception, e: traceback.print_exc() return JSONError(u'删除失败！') return JSONResponse({}) @csrf_exempt @permission_required('goods.import_goods') def goods_import(request): file = request.FILES.get('excel_file') try: line = 2 importer = GoodsImporter() excel_rows = importer.getExcelData(file) with transaction.atomic(): for excel_row in excel_rows: try: row = importer.validRow(excel_row) option = Option.getByName(row[u'类别'], Option.GOODS_MODE) data = { 'name': row[u'名称'], 'model': row[u'代码'], 'option_type': option.id, 'standard': row[u'标准'], 'goods_pack': row[u'包装'], 'warehouse_place': row[u'库位'], 'retail_price': row[u'销售价'], 'notes': row[u'备注'], 'enabled': 1, } data['type'] = ProductBase.GOODS pb = ProductBaseSerializer.factory(request.user,data) pb = pb.validSave() data['product_base'] = pb.id serializer = GoodsSerializer.factory(request.user,data) serializer.validSave() except CustomError,e: raise CustomError(u'第%d行:%s' % (line,e.get_error_msg())) except Exception,e: raise CustomError(u'第%d行:%s' %(line,unicode(e))) line += 1 BizLog.objects.addnew(request.user, BizLog.IMPORT, u"导入成品数据[%s]条" % (line - 2)) except CustomError, e: return JSONError(e.get_error_msg()) except Exception, e: traceback.print_exc() return JSONError(u'导入失败！') return JSONResponse() @csrf_exempt @token_required def goods_select(request): param = request.GET.get('param') warehouse_id = request.GET.get('warehouse') rows = Goods.objects.filter(product_base__enabled=True) if param: rows = rows.filter( Q(product_base__name__icontains=param) | Q(product_base__model__icontains=param) ) rows, total = utils.get_page_data(request, rows) data = [] for row in rows: record_data = [] if warehouse_id: record_data = GetWarehouseSrockRecord.getRecord(row.product_base.id, warehouse_id) item = { 'id': row.id, 'product_id': row.product_base.id, 'name': row.product_base.name, 'model': row.product_base.model, 'unit': row.product_base.unit, 'base_standard': row.product_base.standard, 'type_text': row.product_base.get_type_display(), 'option_type': row.product_base.option_type.name, 'retail_price': Formater.formatPriceShow(row.retail_price), 'standard': row.standard, 'goods_pack': row.goods_pack, # 'entry_price': entry_price, # 'total_cost': total_cost, 'record_data': record_data } data.append(item) return DataGridJSONResponse(data, total) @permission_required('goods.view_goods_godown_entry') def godownentry_list(request): product_notes = request.GET.get('product_notes') warehouses_ids = Warehouse.getManagerWarehouses(request.user) department_ids = request.user.getSubDepartmentIds() user_ids = request.user.getSubEmployeeIds() rows = GoodsGodownEntry.objects.filter(warehouse_id__in=warehouses_ids) rows = rows.filter( Q(department_id__in=department_ids) | Q(create_user_id__in=user_ids) | Q(create_user=request.user)) if product_notes: g_ids = rows.values_list('id') d_ids = GoodsGodownEntryDetail.objects.filter(main_id__in=g_ids, notes__icontains=product_notes).values_list('main_id') rows = rows.filter(id__in=d_ids) f = GoodsGodownEntryFilter(request.GET, queryset=rows) total_row = f.qs.aggregate(total_count=Sum('total_count'), total_amount=Sum('total_amount')) more = { 'total_count': Formater.formatCountShow(total_row['total_count']), 'total_amount': Formater.formatAmountShow(total_row['total_amount']) } rows, total = utils.get_page_data(request, f.qs) serializer = GoodsGodownEntrySerializer(rows, many=True) return DataGridJSONResponse(serializer.data, total, more) @csrf_exempt @permission_required('goods.add_goods_godown_entry') def godownentry_save(request): id = request.GET.get('id') main_data = json.loads(request.POST.get('main')) items_data = json.loads(request.POST.get('item')) try: with transaction.atomic(): serializer = GoodsGodownEntrySerializer.factory(request.user, main_data, id) if serializer.instance and serializer.instance.status == settings.PASS: raise CustomError(u'该成品入库单已审核，禁止修改！') serializer = serializer.validSave() GoodsGodownEntryDetail.objects.filter(main=serializer).delete() for item in items_data: item['main'] = serializer.id detail_serializer = GoodsGodownEntryDetailSerializer.factory(request.user, data=item) detail_serializer.validSave() serializer.update_total() except CustomError, e: return JSONError(e.get_error_msg()) except Exception, e: traceback.print_exc() return JSONError(u'保存失败！') return JSONResponse() @token_required def godownentry_detail(request): id = request.GET.get('id') instance = GoodsGodownEntry.getById(id) company = instance.department.getCompany() if instance.status == settings.PASS: status_text = u'已审核' else: status_text = u'待审核' main_data = { 'id': instance.id, 'warehouse_id': instance.warehouse_id, 'warehouse_name': instance.warehouse.name, 'create_user': instance.create_user.name, 'create_time': Formater.formatStrTime(instance.create_time), 'total_count': Formater.formatCountShow(instance.total_count), 'total_amount': Formater.formatAmountShow(instance.total_amount), 'total_invoice_amount': Formater.formatAmountShow(instance.total_invoice_amount), 'status_text': status_text, 'check_user_text': instance.check_user and instance.check_user.name or ' ', 'check_time': Formater.formatStrTime(instance.create_time), 'notes': instance.notes, 'company': company.name, 'no': instance.no } data = { 'main_data': main_data, 'items_data': [] } detail_rows = GoodsGodownEntryDetail.objects.filter(main=instance) for detail_row in detail_rows: item_data = { 'id': detail_row.id, 'goods_id': detail_row.goods_id, 'goods_name': detail_row.goods.product_base.name, 'goods_model': detail_row.goods.product_base.model, 'price': Formater.formatPriceShow(detail_row.price), 'count': Formater.formatCountShow(detail_row.count), 'amount': Formater.formatAmountShow(detail_row.amount), 'invoice_amount': Formater.formatAmountShow(detail_row.invoice_amount), 'unit': detail_row.goods.product_base.unit or '', 'warehouse_place': detail_row.goods.product_base.warehouse_place or '', 'notes': detail_row.notes or '' } data['items_data'].append(item_data) return JSONResponse(data) @permission_required('goods.delete_goods_godown_entry') def godownentry_delete(request): id = request.GET.get('id') try: with transaction.atomic(): instance = GoodsGodownEntry.getById(id) if instance.status == settings.PASS: raise CustomError(u'该成品入库单已审核，禁止删除！') BizLog.objects.addnew(request.user, BizLog.DELETE, u"删除成品入库单[%s]，id=%d" % (instance.no, instance.id)) GoodsGodownEntryDetail.objects.filter(main=instance).delete() instance.delete() except CustomError, e: return JSONError(e.get_error_msg()) except ProtectedError: return JSONError(u'该成品入库单已被引用，禁止删除！') except IntegrityError: return JSONError(u'该成品入库单已被引用，禁止删除！') except Exception, e: traceback.print_exc() return JSONError(u'删除失败！') return JSONResponse({}) @permission_required('goods.export_goods_godown_entry') def godownentry_export(request): try: warehouses_ids = Warehouse.getManagerWarehouses(request.user) department_ids = request.user.getSubDepartmentIds() user_ids = request.user.getSubEmployeeIds() rows = GoodsGodownEntry.objects.filter(warehouse_id__in=warehouses_ids) rows = rows.filter( Q(department_id__in=department_ids) | Q(create_user_id__in=user_ids) | Q(create_user=request.user)) f = GoodsGodownEntryFilter(request.GET, queryset=rows) serializer = GoodsGodownEntrySerializer(f.qs, many=True) export_data = ExportChange.dict_to_obj(serializer) is_show_cost = isHasPermissions(request.user, 'goods.view_goods_cost') export_data = GoodsGodownEntryResource(is_show_cost).export(export_data) filename = utils.attachment_save(export_data) BizLog.objects.addnew(request.user, BizLog.EXPORT, u"导出成品入库单" ) except CustomError, e: return JSONError(e.get_error_msg()) except Exception, e: traceback.print_exc() return JSONError(u'导出列表失败！') return JSONResponse({'filename': filename}) @permission_required('goods.export_goods_godown_entry') def godownentry_export_detail(request): id = request.GET.get('id') try: instance = GoodsGodownEntry.getById(id) godown_entry_detail = GoodsGodownEntryDetail.objects.filter(main=instance) serializer = GoodsGodownEntryDetailSerializer(godown_entry_detail, many=True) export_data = ExportChange.dict_to_obj(serializer) is_show_cost = isHasPermissions(request.user, 'goods.view_goods_cost') export_data = GoodsGodownEntryDetailResource(is_show_cost).export(export_data) filename = utils.attachment_save(export_data) BizLog.objects.addnew(request.user, BizLog.EXPORT, u"导出成品入库单[%s]明细，id=%d" % (instance.no, instance.id)) except CustomError, e: return JSONError(e.get_error_msg()) except Exception, e: traceback.print_exc() return JSONError(u'导出明细失败！') return JSONResponse({'filename': filename}) @csrf_exempt @permission_required('goods.import_goods_godown_entry') def godownentry_import(request): file = request.FILES.get('excel_file') main_data = json.loads(request.POST.get('main_data')) try: line = 2 importer = GoodsGodownEntryImporter() excel_rows = importer.getExcelData(file) with transaction.atomic(): serializer = GoodsGodownEntrySerializer.factory(request.user, main_data) serializer = serializer.validSave() for excel_row in excel_rows: try: row = importer.validRow(excel_row) model = row[u'成品代码'] goods = Goods.objects.filter(product_base__model=model, product_base__type=ProductBase.GOODS) if goods.count() == 0: raise CustomError(u'成品代码不存在') elif goods.count() > 1: raise CustomError(u'成品代码重复，前往基础数据设置修改') else: goods = goods.first() items_data = {} items_data['goods'] = goods.id items_data['main'] = serializer.id items_data['price'] = row[u'单价'] items_data['count'] = row[u'数量'] items_data['invoice_amount'] = row[u'发票金额'] items_data['notes'] = row[u'备注'] detail_serializer = GoodsGodownEntryDetailSerializer.factory(request.user, items_data) detail_serializer.validSave() except CustomError, e: raise CustomError(u'第%d行:%s' % (line, e.get_error_msg())) except Exception, e: raise CustomError(u'第%d行:%s' % (line, unicode(e))) line += 1 serializer.update_total() BizLog.objects.addnew(request.user, BizLog.IMPORT, u"导入成品入库单[%s]，id=%d" % (serializer.no, serializer.id)) except CustomError, e: return JSONError(e.get_error_msg()) except Exception, e: traceback.print_exc() return JSONError(u'导入失败！') return JSONResponse() @permission_required('goods.check_goods_godown_entry') def godownentry_check(request): id = request.GET.get('id') try: with transaction.atomic(): instance = GoodsGodownEntry.getById(id) if instance.status == settings.PASS: raise CustomError(u'该成品入库单已审核') godownentry_details = GoodsGodownEntryDetail.objects.filter(main=instance) for godownentry_detail in godownentry_details: stock_record = BizWarehouse.entry(type=WarehouseRecord.RK_ZJ, product=godownentry_detail.goods.product_base, warehouse=instance.warehouse, supplier=None, entry_count=godownentry_detail.count, entry_price=godownentry_detail.price, entry_price2=godownentry_detail.price) godownentry_detail.stock_record = stock_record godownentry_detail.save() instance.status = settings.PASS instance.check_user = request.user instance.check_time = timezone.now() BizLog.objects.addnew( request.user, BizLog.CHECK, u"审核原料入库[%s]，id=%d" % (instance.no, instance.id), ) instance.save() except CustomError, e: return JSONError(e.get_error_msg()) except Exception, e: traceback.print_exc() return JSONError(u'审核失败') return JSONResponse({}) @csrf_exempt @permission_required('goods.edit_goods_godown_entry') def godownentry_senior_edit(request): def updateStock(product_base, warehouse): sum_row = WarehouseRecord.objects.filter( warehouse=warehouse, product=product_base ).aggregate( count=Sum('count'), amount=Sum('amount'), amount2=Sum('amount2') ) warehouse_stock = WarehouseStock.objects.filter(product=product_base, warehouse=warehouse).first() if warehouse_stock: warehouse_stock.count = sum_row['count'] or 0 warehouse_stock.amount = sum_row['amount'] or 0 warehouse_stock.amount2 = sum_row['amount2'] or 0 if warehouse_stock.count != 0: warehouse_stock.avg_cost_price = warehouse_stock.amount / warehouse_stock.count warehouse_stock.avg_cost_price2 = warehouse_stock.amount2 / warehouse_stock.count warehouse_stock.save() sum_row = WarehouseStock.objects.filter( product=product_base ).aggregate( count=Sum('count'), amount=Sum('amount'), amount2=Sum('amount2') ) product_base.stock_count = sum_row['count'] or 0 product_base.stock_amount = sum_row['amount'] or 0 product_base.stock_amount2 = sum_row['amount2'] or 0 if product_base.stock_count > 0: product_base.avg_cost_price = product_base.stock_amount / product_base.stock_count product_base.avg_cost_price2 = product_base.stock_amount2 / product_base.stock_count product_base.save() def updateWarehouseRecord(warehouse_record): sum_row = WarehouseRecordDetail.objects.filter( warehouse_record=warehouse_record ).aggregate( sum_amount=Sum(F('count') * F('warehouse_stock_record__entry_price')), sum_amount2=Sum(F('count') * F('warehouse_stock_record__entry_price2')), sum_count=Sum('count') ) warehouse_record.amount = sum_row['sum_amount'] or 0 warehouse_record.amount2 = sum_row['sum_amount2'] or 0 warehouse_record.count = sum_row['sum_count'] or 0 warehouse_record.save() def changeEntryPrice(entry_detail,entry_price,entry_price2): changeEntryPriceBase(entry_detail, entry_price, entry_price2) rows = WarehouseRecordDetail.objects.filter(warehouse_stock_record=entry_detail.stock_record) ck_type = (WarehouseRecord.CK_ZJ, WarehouseRecord.CK_XS,) pk_type = (WarehouseRecord.CK_PK,) tl_type = (WarehouseRecord.TL,) for row in rows: warehouse_record = row.warehouse_record updateWarehouseRecord(warehouse_record) if warehouse_record.type in ck_type: deliver_detail = GoodsDeliverDetail.objects.filter(warehouse_record=warehouse_record).first() if deliver_detail: #更新出库明细的合计成本 deliver_detail.total_cost = warehouse_record.amount deliver_detail.save() # 更新出库单的合计成本 order = deliver_detail.main sum_row = GoodsDeliverDetail.objects.filter( main=order ).aggregate( sum_cost=Sum('total_cost') ) order.total_cost = sum_row['sum_cost'] or 0 order.save() elif warehouse_record.type in tl_type: detail = GoodsDeliverReturnDetail.objects.filter(warehouse_record=warehouse_record).first() if detail: #更新退料明细的合计成本 old_amount = detail.return_cost detail.return_cost = warehouse_record.amount detail.save() # 更新出库明细的退料合计成本 new_amount = detail.return_cost detail.deliver_detail.return_cost += new_amount - old_amount detail.deliver_detail.save() #更新出库单的退料合计成本 detail.deliver_detail.main.return_cost += new_amount - old_amount detail.deliver_detail.main.save() #更新退料单的合计成本 order = detail.main sum_row = GoodsDeliverReturnDetail.objects.filter( main=order ).aggregate( sum_cost=Sum('return_cost') ) order.return_cost = sum_row['sum_cost'] or 0 order.save() elif warehouse_record.type in pk_type: detail = InventoryDetail.objects.filter(warehouse_record=warehouse_record).first() if detail: #更新盘存明细 detail.amount = warehouse_record.amount detail.price = 0 if detail.count: detail.price = detail.amount / detail.count detail.save() # 更新盘存单 order = detail.main sum_row = InventoryDetail.objects.filter( main=order ).aggregate( sum_amount=Sum('amount') ) order.total_amount = sum_row['sum_amount'] or 0 order.save() def changeEntryPriceBase(entry_detail,entry_price,entry_price2): entry_detail.price = entry_price entry_detail.amount = entry_detail.count * entry_price entry_detail.save() if entry_detail.stock_record: entry_detail.stock_record.entry_price = entry_price entry_detail.stock_record.entry_price2 = entry_price2 entry_detail.stock_record.save() def changePrice(godownentry_detail,new_entry_price): changeEntryPrice(godownentry_detail, new_entry_price, new_entry_price) updateStock(godownentry_detail.goods.product_base, godownentry_detail.main.warehouse) def addCount(godownentry_detail,add_count): godownentry_detail.count += add_count godownentry_detail.amount = godownentry_detail.count * godownentry_detail.price godownentry_detail.save() if godownentry_detail.stock_record: godownentry_detail.stock_record.entry_count += add_count godownentry_detail.stock_record.surplus_count += add_count godownentry_detail.stock_record.save() record_detail = WarehouseRecordDetail.objects.filter( warehouse_stock_record=godownentry_detail.stock_record, warehouse_record__type__in=(WarehouseRecord.RK_CG, WarehouseRecord.RK_ZJ) ).first() if record_detail: record_detail.count += add_count record_detail.save() updateWarehouseRecord(record_detail.warehouse_record) updateStock(godownentry_detail.goods.product_base, godownentry_detail.main.warehouse) def decCount(godownentry_detail,red_count): godownentry_detail.count -= red_count godownentry_detail.amount = godownentry_detail.count * godownentry_detail.price godownentry_detail.save() if godownentry_detail.stock_record: if red_count > godownentry_detail.stock_record.surplus_count: raise CustomError(u'该入库单中的配件[%s]，剩余%s，不能减少%s，请使用退货减少库存' % ( godownentry_detail.goods.product_base.name, Formater.formatCountShow(godownentry_detail.stock_record.surplus_count), Formater.formatCountShow(red_count) )) godownentry_detail.stock_record.entry_count -= red_count godownentry_detail.stock_record.surplus_count -= red_count godownentry_detail.stock_record.save() record_detail = WarehouseRecordDetail.objects.filter( warehouse_stock_record=godownentry_detail.stock_record, warehouse_record__type__in=(WarehouseRecord.RK_CG, WarehouseRecord.RK_ZJ) ).first() if record_detail: record_detail.count -= red_count record_detail.save() updateWarehouseRecord(record_detail.warehouse_record) updateStock(godownentry_detail.goods.product_base, godownentry_detail.main.warehouse) new_rows = json.loads(request.POST.get('item')) id = request.GET.get('id') try: with transaction.atomic(): godownentry = GoodsGodownEntry.objects.filter(pk=int(id)).first() if not godownentry: raise CustomError(u'未找到相应的入库单') if godownentry.status != settings.PASS: raise CustomError(u'未通过审核的入库单不允许高级修改') for row in new_rows: new_entry_count = Formater.formatCount(row['new_count']) new_entry_price = Formater.formatPrice(row['new_price']) if new_entry_count < 0: raise CustomError(u'入库数量不能小于0') if new_entry_price < 0: raise CustomError(u'入库价不能小于0') detail = GoodsGodownEntryDetail.objects.filter(id=int(row['new_detail_id'])).first() if not detail: continue if detail.price != new_entry_price: changePrice(detail,new_entry_price) if detail.count < new_entry_count: addCount(detail,new_entry_count-detail.count) if detail.count > new_entry_count: decCount(detail, detail.count-new_entry_count) WarehouseRecord.updateCurStockByProduct(detail.main.warehouse_id, detail.goods.product_base_id) count = 0 amount = 0 sum_row = GoodsGodownEntryDetail.objects.filter(main=godownentry).aggregate( count_sum=Sum('count'), amount_sum=Sum('amount') ) if sum_row: count = sum_row['count_sum'] or 0 amount = sum_row['amount_sum'] or 0 godownentry.total_count = count godownentry.total_amount = amount godownentry.save() BizLog.objects.addnew( request.user, BizLog.UPDATE, u"原料高级修改[单号=%s],id=%d" % (godownentry.no, godownentry.id) ) except CustomError, e: return JSONError(e.get_error_msg()) except Exception, e: traceback.print_exc() return JSONError(u'修改失败') return JSONResponse({}) @csrf_exempt @permission_required('warehouse.view_goods_stock_log') def goods_stock_log(request): keyword = request.GET.get('keyword') action = request.GET.get('action') happen_time = request.GET.get('happen_time') warehouse_id = request.GET.get('warehouse_id') product_id = request.GET.get('product_id') page, page_size = utils.get_page_info(request) product_id = int(product_id) warehouse_id = int(warehouse_id) where = '' if happen_time: happen_time = happen_time.split(' - ') where += " AND m.happen_time >= '%s' " % happen_time[0] where += " AND m.happen_time <= '%s' " % (happen_time[1] + ' 23:59:59') if action: where += ' AND m.type = %d' % int(action) if keyword and len(keyword) > 0: where += " AND (m.order_no like '%" + keyword + "%' " \ " or m.warehouse_name like '%" + keyword + "%' )" page_sql = ' LIMIT %d OFFSET %d ' % (page_size, page * page_size) sql = """SELECT * FROM( SELECT pwr.type, pwr.happen_time, (CASE WHEN pwr.type in (3, 4) THEN gd.`no` WHEN pwr.type = 5 THEN iv.`no` WHEN pwr.type = 7 THEN gdr.`no` else '' END) AS order_no, (CASE WHEN pwr.type in (3, 4) THEN gdd.price WHEN pwr.type = 5 THEN ind.price WHEN pwr.type = 7 THEN rdgd.price else '' END) AS order_price, pwr.count, pwr.amount, pwr.amount/pwr.count as cost, pw.name as warehouse_name, pwr.warehouse_id AS warehouse_id, pwr.product_id AS product_id, pwr.id AS id, pwr.cur_count as cur_count, pwr.cur_amount as cur_amount FROM product_warehouse_record pwr LEFT JOIN product_warehouse pw ON pwr.warehouse_id = pw.id LEFT JOIN product_warehouse_stock pws ON pwr.product_id = pws.product_id AND pwr.warehouse_id=pws.warehouse_id LEFT JOIN goods_deliver_detail gdd ON pwr.id=gdd.warehouse_record_id LEFT JOIN goods_deliver gd ON gdd.main_id=gd.id LEFT JOIN goods_deliver_detail_return gdrd ON pwr.id = gdrd.warehouse_record_id LEFT JOIN goods_deliver_detail rdgd ON rdgd.id = gdrd.deliver_detail_id LEFT JOIN goods_deliver_return gdr ON gdrd.main_id = gdr.id LEFT JOIN inventory_detail ind on ind.warehouse_record_id=pwr.id left join inventory iv ON iv.id=ind.main_id WHERE pwr.warehouse_id = %(warehose)d AND pwr.product_id = %(product)d AND pwr.type >=3 UNION ALL SELECT pwr.type, pwr.happen_time, (CASE WHEN pwr.type in (0, 1) THEN gge.`no` WHEN pwr.type = 2 THEN iv.`no` else '' END) AS order_no, (CASE WHEN pwr.type in (0, 1) THEN gded.price WHEN pwr.type = 2 THEN ind.price else '' END) AS order_price, pwr.count, pwr.amount, pwr.amount/pwr.count as cost, pw.name as warehouse_name, pwr.warehouse_id AS warehouse_id, pwr.product_id AS product_id, pwr.id AS id, pwr.cur_count as cur_count, pwr.cur_amount as cur_amount FROM product_warehouse_record pwr LEFT JOIN product_warehouse pw ON pwr.warehouse_id = pw.id LEFT JOIN product_warehouse_stock pws ON pwr.product_id = pws.product_id AND pwr.warehouse_id=pws.warehouse_id LEFT JOIN product_warehouse_record_detail pwrd ON pwr.id=pwrd.warehouse_record_id LEFT JOIN goods_godown_entry_detail gded ON pwrd.warehouse_stock_record_id=gded.stock_record_id LEFT JOIN goods_godown_entry gge ON gded.main_id=gge.id LEFT JOIN inventory_detail ind on ind.warehouse_stock_record_id = pwrd.warehouse_stock_record_id left join inventory iv ON iv.id = ind.main_id WHERE pwr.warehouse_id = %(warehose)d AND pwr.product_id = %(product)d AND (pwr.type <= 2) ) as m WHERE 1=1 %(where)s ORDER BY m.happen_time DESC """ % {'warehose': warehouse_id, 'product': product_id, 'where': where} sum_sql = """ SELECT COUNT(0) FROM (%s) AS t """ % sql items = [] sql = '%s %s' % (sql, page_sql) cursor = connection.cursor() cursor.execute(sql) row = cursor.fetchone() total = 0 while row: action_text = WarehouseRecord.TYPE_CHOICES[row[0]][1] text = '' if row[0] == WarehouseRecord.RK_ZJ: text = u'入库单[' + row[2] + u']' elif row[0] == WarehouseRecord.RK_CG: text = u'采购转入库,入库单[' + row[2] + u']' elif row[0] == WarehouseRecord.RK_PY: text = u'盘盈单[' + row[2] + u']' elif row[0] == WarehouseRecord.CK_ZJ: text = u'出库单[' + row[2] + ']' elif row[0] == WarehouseRecord.CK_XS: text = u'订单转出库,出库单[' + row[2] + u']' elif row[0] == WarehouseRecord.CK_PK: text = u'盘亏单[' + row[2] + u']' elif row[0] == WarehouseRecord.TH: text = u'退货单[' + row[2] + u']' elif row[0] == WarehouseRecord.TL: text = u'退料单[' + row[2] + u']' item = { 'action': row[0], 'action_text': action_text, 'create_time': strftime(row[1]), 'order_no': row[2], 'count': Formater.formatCountShow(row[4]), 'cost': Formater.formatPriceShow(row[6]), 'price': Formater.formatPriceShow(row[3]), 'warehouse_text': row[7], 'text': text, 'cur_count': Formater.formatCountShow(row[11]), 'cur_amount': Formater.formatAmountShow(row[12]), } items.append(item) row = cursor.fetchone() cursor.execute(sum_sql) row = cursor.fetchone() if row: total = row[0] return DataGridJSONResponse(items, total) @csrf_exempt @permission_required('product.view_goods_product') def goods_product_list(request): empty = request.GET.get('empty') rows = ProductBase.objects.filter(type=ProductBase.GOODS) if empty == '0': rows = rows.filter(stock_count=0) elif empty == '1': rows = rows.filter(stock_count__gt=0) f = GoodsProductFilter(request.GET, queryset=rows) total_row = f.qs.aggregate(sum_count=Sum('stock_count')) more = { 'sum_count': Formater.formatCountShow(total_row['sum_count']) } rows, total = utils.get_page_data(request, f.qs) serializer = GoodsProductSerializer(rows, many=True) return DataGridJSONResponse(serializer.data, total, more) @csrf_exempt @permission_required('product.export_goods_product') def goods_product_export(request): empty = request.GET.get('empty') rows = ProductBase.objects.filter(type=ProductBase.GOODS) if empty == '0': rows = rows.filter(stock_count=0) elif empty == '1': rows = rows.filter(stock_count__gt=0) f = GoodsProductFilter(request.GET, queryset=rows) serializer = GoodsProductSerializer(f.qs, many=True) export_data = ExportChange.dict_to_obj(serializer) export_data = GoodsProductResource().export(export_data) filename = utils.attachment_save(export_data) return JSONResponse({'filename': filename}) @token_required @permission_required('order.view_goods_godownentry_query') def godownentry_query_list(request): rows = get_filter_data(request) total_row = rows.aggregate(godownentry_total_count=Sum('goods_godown_entry_detail_ref_stock_record__count'), inventory_total_count=Sum('inventory_details_ref_warehouse_stock_record__count'), godownentry_total_amount=Sum('goods_godown_entry_detail_ref_stock_record__amount'), inventory_total_amount=Sum('inventory_details_ref_warehouse_stock_record__amount'), godownentry_total_deliver_count=Sum( 'goods_godown_entry_detail_ref_stock_record__deliver_count'), inventory_total_deliver_count=Sum( 'inventory_details_ref_warehouse_stock_record__deliver_count'), total_surplus_count=Sum('surplus_count') ) more = { 'total_count': Formater.formatCountShow( (total_row['godownentry_total_count'] or 0) + (total_row['inventory_total_count'] or 0)), 'total_amount': Formater.formatAmountShow( (total_row['godownentry_total_amount'] or 0) + (total_row['inventory_total_amount'] or 0)), 'total_deliver_count': Formater.formatCountShow( (total_row['godownentry_total_deliver_count'] or 0) + (total_row['inventory_total_deliver_count'] or 0)), 'total_surplus_count': Formater.formatCountShow(total_row['total_surplus_count'] or 0) } rows, total = utils.get_page_data(request, rows) data = get_godownentry_query_data(rows) return DataGridJSONResponse(data, total, more) @token_required @permission_required('order.export_goods_godownentry_query') def godownentry_query_export(request): try: rows = get_filter_data(request) data = get_godownentry_query_data(rows) export_data = ExportChange.dict_to_obj2(data) is_show_cost = isHasPermissions(request.user, 'goods.view_goods_cost') export_data = GodownEntryQueryResource(is_show_cost).export(export_data) filename = utils.attachment_save(export_data) BizLog.objects.addnew(request.user, BizLog.EXPORT, u"导出成品入库查询") except Exception, e: traceback.print_exc() return JSONError(u'导出成品入库查询失败！') return JSONResponse({'filename': filename}) @token_required def godownentry_query_detail(request): rows = get_filter_data(request) data = get_godownentry_query_data(rows) return JSONResponse(data) def get_filter_data(request): entry_time = request.GET.get('entry_time') no = request.GET.get('no') create_user = request.GET.get('create_user') product_text = request.GET.get('product_text') product_model = request.GET.get('product_model') warehouse = request.GET.get('warehouse') entry_type = request.GET.get('entry_type') notes = request.GET.get('notes') rows = WarehouseStockRecord.objects.filter( product__type=ProductBase.GOODS, warehouse_record_detail_ref_stock_record__warehouse_record__type__in=[0,1,2]).order_by( '-id' ) if entry_type: rows = rows.filter(warehouse_record_detail_ref_stock_record__warehouse_record__type=int(entry_type)) if notes: rows = rows.filter(Q(goods_godown_entry_detail_ref_stock_record__notes__icontains=notes) | Q(inventory_details_ref_warehouse_stock_record__notes__icontains=notes)) if product_text: rows = rows.filter(product__name__icontains=product_text) if product_model: rows = rows.filter(product__model__icontains=product_model) if warehouse: rows = rows.filter(warehouse__name__icontains=warehouse) if entry_time: entry_time_begin = entry_time.split(' - ')[0] entry_time_end = entry_time.split(' - ')[1] + ' 23:59:59' rows = rows.filter(entry_time__gt=entry_time_begin, entry_time__lt=entry_time_end) if no: rows = rows.filter(Q(goods_godown_entry_detail_ref_stock_record__main__no__icontains=no) | Q(inventory_details_ref_warehouse_stock_record__main__no__icontains=no)) if create_user: rows = rows.filter(Q(goods_godown_entry_detail_ref_stock_record__main__create_user__name__icontains=create_user) | Q(inventory_details_ref_warehouse_stock_record__main__create_user__name__icontains=create_user)) warehouses_ids = Warehouse.getManagerWarehouses(request.user) department_ids = request.user.getSubDepartmentIds() user_ids = request.user.getSubEmployeeIds() rows = rows.filter(warehouse_id__in=warehouses_ids) rows = rows.filter(Q( Q(goods_godown_entry_detail_ref_stock_record__main__department_id__in=department_ids) | Q(goods_godown_entry_detail_ref_stock_record__main__create_user_id__in=user_ids) | Q(goods_godown_entry_detail_ref_stock_record__main__create_user=request.user)) | Q( Q(inventory_details_ref_warehouse_stock_record__main__department_id__in=department_ids) | Q(inventory_details_ref_warehouse_stock_record__main__create_user_id__in=user_ids) | Q(inventory_details_ref_warehouse_stock_record__main__create_user=request.user) )) return rows def get_godownentry_query_data(rows): rows = rows.values( 'id', 'goods_godown_entry_detail_ref_stock_record__goods__product_base__name', 'goods_godown_entry_detail_ref_stock_record__goods__product_base__model', 'goods_godown_entry_detail_ref_stock_record__goods__product_base__unit', 'goods_godown_entry_detail_ref_stock_record__goods__product_base__type', 'goods_godown_entry_detail_ref_stock_record__goods__product_base__warehouse_place', 'inventory_details_ref_warehouse_stock_record__product__name', 'inventory_details_ref_warehouse_stock_record__product__model', 'inventory_details_ref_warehouse_stock_record__product__unit', 'inventory_details_ref_warehouse_stock_record__product__type', 'inventory_details_ref_warehouse_stock_record__product__warehouse_place', 'warehouse__name', 'warehouse_record_detail_ref_stock_record__warehouse_record__type', 'goods_godown_entry_detail_ref_stock_record__id', 'goods_godown_entry_detail_ref_stock_record__price', 'goods_godown_entry_detail_ref_stock_record__count', 'goods_godown_entry_detail_ref_stock_record__amount', 'goods_godown_entry_detail_ref_stock_record__deliver_count', 'goods_godown_entry_detail_ref_stock_record__notes', 'goods_godown_entry_detail_ref_stock_record__main__create_user__name', 'goods_godown_entry_detail_ref_stock_record__main__check_time', 'goods_godown_entry_detail_ref_stock_record__main__no', 'inventory_details_ref_warehouse_stock_record__price', 'inventory_details_ref_warehouse_stock_record__count', 'inventory_details_ref_warehouse_stock_record__amount', 'inventory_details_ref_warehouse_stock_record__notes', 'inventory_details_ref_warehouse_stock_record__deliver_count', 'inventory_details_ref_warehouse_stock_record__main__create_user__name', 'inventory_details_ref_warehouse_stock_record__main__check_time', 'inventory_details_ref_warehouse_stock_record__main__no', ) data = [] for row in rows: warehouse_record_type = WarehouseRecord.TYPE_CHOICES[row['warehouse_record_detail_ref_stock_record__warehouse_record__type']][1] if row['warehouse_record_detail_ref_stock_record__warehouse_record__type'] == WarehouseRecord.RK_PY: product_type_text = ProductBase.TYPE_CHOICES[row['inventory_details_ref_warehouse_stock_record__product__type']][1] surplus_count = row['inventory_details_ref_warehouse_stock_record__count'] - row['inventory_details_ref_warehouse_stock_record__deliver_count'] item = { 'id': row['id'], 'type': warehouse_record_type, 'product_type': product_type_text, 'product_name': row['inventory_details_ref_warehouse_stock_record__product__name'], 'product_model': row['inventory_details_ref_warehouse_stock_record__product__model'], 'product_unit': row['inventory_details_ref_warehouse_stock_record__product__unit'], 'warehouse_place': row['inventory_details_ref_warehouse_stock_record__product__warehouse_place'], 'warehouse': row['warehouse__name'], 'price': Formater.formatPriceShow(row['inventory_details_ref_warehouse_stock_record__price']), 'count': Formater.formatCountShow(row['inventory_details_ref_warehouse_stock_record__count']), 'amount': Formater.formatAmountShow(row['inventory_details_ref_warehouse_stock_record__amount']), 'deliver_count': Formater.formatCountShow(row['inventory_details_ref_warehouse_stock_record__deliver_count']), 'surplus_count': Formater.formatCountShow(surplus_count), 'create_user': row['inventory_details_ref_warehouse_stock_record__main__create_user__name'], 'check_time': Formater.formatStrTime(row['inventory_details_ref_warehouse_stock_record__main__check_time']), 'notes': row['inventory_details_ref_warehouse_stock_record__notes'], 'no': row['inventory_details_ref_warehouse_stock_record__main__no'], } else: product_type_text = ProductBase.TYPE_CHOICES[row['goods_godown_entry_detail_ref_stock_record__goods__product_base__type']][1] surplus_count = row['goods_godown_entry_detail_ref_stock_record__count'] - row['goods_godown_entry_detail_ref_stock_record__deliver_count'] item = { 'id': row['id'], 'godownentry_detail_id': row['goods_godown_entry_detail_ref_stock_record__id'], 'type': warehouse_record_type, 'product_type': product_type_text, 'product_name': row['goods_godown_entry_detail_ref_stock_record__goods__product_base__name'], 'product_model': row['goods_godown_entry_detail_ref_stock_record__goods__product_base__model'], 'product_unit': row['goods_godown_entry_detail_ref_stock_record__goods__product_base__unit'], 'warehouse_place': row['goods_godown_entry_detail_ref_stock_record__goods__product_base__warehouse_place'], 'warehouse': row['warehouse__name'], 'price': Formater.formatPriceShow(row['goods_godown_entry_detail_ref_stock_record__price']), 'count': Formater.formatCountShow(row['goods_godown_entry_detail_ref_stock_record__count']), 'amount': Formater.formatAmountShow(row['goods_godown_entry_detail_ref_stock_record__amount']), 'deliver_count': Formater.formatCountShow(row['goods_godown_entry_detail_ref_stock_record__deliver_count']), 'surplus_count': Formater.formatCountShow(surplus_count), 'create_user': row['goods_godown_entry_detail_ref_stock_record__main__create_user__name'], 'check_time': Formater.formatStrTime(row['goods_godown_entry_detail_ref_stock_record__main__check_time']), 'notes': row['goods_godown_entry_detail_ref_stock_record__notes'], 'no': row['goods_godown_entry_detail_ref_stock_record__main__no'], } data.append(item) return data