#coding=utf-8 from django.db import models,connection from django.db.models import Q, Sum from django.utils import timezone from libs.utils import strftime, strfdate from apps.exceptions import CustomError from apps.product.models import ProductBase from apps.supplier.models import Supplier from apps.account.models import User, Department from django.conf import settings from apps.base import Formater class Warehouse(models.Model): type = models.PositiveSmallIntegerField(choices=ProductBase.TYPE_CHOICES, verbose_name=u"类型",blank=True) name = models.CharField(max_length=100, verbose_name=u"名称") notes = models.CharField(max_length=500, verbose_name=u"备注",blank=True, null=True) enabled = models.BooleanField(verbose_name=u"在用", default=True) is_default = models.BooleanField(verbose_name=u"默认仓别", default=False) @staticmethod def getManagerWarehouses(user): return WarehouseAdmin.objects.filter(user=user).values_list('warehouse_id', flat=True) @staticmethod def getById(id): instance = Warehouse.objects.filter(pk=id).first() if not instance: raise CustomError(u'未找到相应的仓别') return instance @staticmethod def updateDefault(instance): if not instance.is_default: return Warehouse.objects.filter(Q(type=instance.type),~Q(id=instance.id)).update(is_default=False) @staticmethod def getPermissionByType(type, action): permissions = Warehouse.getPermissionMap() type = Warehouse.getValidType(type) return permissions[type][action] @staticmethod def getPermissionMap(): permissions = { ProductBase.MATERIAL: {'view': 'warehouse.view_material_warehouse', 'add': 'warehouse.add_material_warehouse', 'delete': 'warehouse.delete_material_warehouse'}, ProductBase.CONSUMABLE: {'view': 'warehouse.view_consumable_warehouse', 'add': 'warehouse.add_consumable_warehouse', 'delete': 'warehouse.delete_consumable_warehouse'}, ProductBase.GOODS: {'view': 'warehouse.view_goods_warehouse', 'add': 'warehouse.add_goods_warehouse', 'delete': 'warehouse.delete_goods_warehouse'}, } return permissions @staticmethod def getValidType(type): try: type = int(type) except: raise CustomError(u'错误的仓别类型') types = (r[0] for r in ProductBase.TYPE_CHOICES) if type not in types: raise CustomError(u'无效的仓别类型') return type def getPermission(self, action): permissions = Warehouse.getPermissionMap() return permissions[self.type][action] def removeStock(self): WarehouseStock.objects.filter(warehouse=self).delete() def removeAdmins(self): WarehouseAdmin.objects.filter(warehouse=self).delete() def addAdminByUserId(self,userid): WarehouseAdmin.objects.create(warehouse=self,user_id=userid) class Meta: db_table = "product_warehouse" verbose_name = u"原料仓别管理" ordering = ('-id',) index_together = ( 'name', ) default_permissions = () permissions = ( ("view_material_warehouse", u"浏览"), ("add_material_warehouse", u"添加"), ("delete_material_warehouse", u"删除"), ) class WarehouseAdmin(models.Model): warehouse = models.ForeignKey(Warehouse, verbose_name=u"仓别", on_delete=models.PROTECT) user = models.ForeignKey(User, verbose_name=u"管理员", on_delete=models.PROTECT) class Meta: db_table = "product_warehouse_admin" verbose_name = u"耗材仓别管理" ordering = ('id',) default_permissions = () permissions = ( ("view_consumable_warehouse", u"浏览"), ("add_consumable_warehouse", u"添加"), ("delete_consumable_warehouse", u"删除"), ) class WarehouseStockManager(models.Manager): def fetch_stock(self, user, time, product_name_model, query_warehouse, last_entry_date, last_deliver_date, type, warehouse_place, enable, page, page_size,empty,source,export=False): warehouse_ids = Warehouse.getManagerWarehouses(user) if not warehouse_ids: return [], 0, 0, 0 if warehouse_ids.count() == 1: warehouse_ids = '(' + str(warehouse_ids[0]) + ')' else: warehouse_ids = tuple(int(item) for item in warehouse_ids) where_sql = ' AND product_warehouse.id in %s ' % str(warehouse_ids) if product_name_model: where_sql += ' AND (product_base.model LIKE "%%%s%%" or product_base.name LIKE "%%%s%%") '\ % (product_name_model,product_name_model) if query_warehouse: where_sql += ' AND product_warehouse.id = %d ' % int(query_warehouse) if warehouse_place: where_sql += ' AND product_warehouse_stock.warehouse_place LIKE "%%%s%%" ' % warehouse_place where_sql += ' AND product_base.type = %d ' % int(type) if last_entry_date: if source: last_entry_date = last_entry_date.split('T') where_sql += ' AND (product_warehouse_stock.last_entry_time >= "%s 00:00:00" AND product_warehouse_stock.last_entry_time <= "%s 23:59:59") ' % ( last_entry_date[0], last_entry_date[0]) else: last_entry_date = last_entry_date.split(' - ') where_sql += ' AND (product_warehouse_stock.last_entry_time >= "%s 00:00:00" AND product_warehouse_stock.last_entry_time <= "%s 23:59:59") ' % ( last_entry_date[0], last_entry_date[1]) if last_deliver_date: if source: last_deliver_date = last_deliver_date.split('T') where_sql += ' AND (product_warehouse_stock.last_deliverd_time >= "%s 00:00:00" AND product_warehouse_stock.last_deliverd_time <= "%s 23:59:59") ' % ( last_deliver_date[0], last_deliver_date[0]) else: last_deliver_date = last_deliver_date.split(' - ') where_sql += ' AND (product_warehouse_stock.last_deliverd_time >= "%s 00:00:00" AND product_warehouse_stock.last_deliverd_time <= "%s 23:59:59") ' % ( last_deliver_date[0], last_deliver_date[1]) if time: where_sql += ' AND product_warehouse_record.happen_time <= "%s" ' % time if enable: if enable == '1': where_sql += ' AND product_base.enabled = 1 ' elif enable == '0': where_sql += ' AND product_base.enabled = 0 ' if export: page_sql = '' else: page_sql = ' LIMIT %d OFFSET %d ' % (page_size, page * page_size) search_empty = '' search_data = '' left_sql = '' if empty == '0': search_empty = ' WHERE count = 0 OR ISNULL(count) ' elif empty == '1': search_empty = ' WHERE count > 0 ' elif empty == '4': search_empty = ' WHERE count < 0 ' if int(type) in [ProductBase.MATERIAL, ProductBase.CONSUMABLE] and (empty == '2' or empty == '3'): if int(type) == ProductBase.MATERIAL: search_data = ',material.stock_upper_limit as stock_upper_limit, material.stock_lower_limit as stock_lower_limit' left_sql = ' left join material on material.product_base_id=product_base.id' elif int(type) == ProductBase.CONSUMABLE: search_data = ',consumable.stock_upper_limit as stock_upper_limit, consumable.stock_lower_limit as stock_lower_limit' left_sql = ' left join consumable on consumable.product_base_id=product_base.id' if empty == '2': search_empty = ' WHERE count > stock_upper_limit ' elif empty == '3': search_empty = ' WHERE (ISNULL(count) AND stock_lower_limit > 0) OR (count < stock_lower_limit) ' sql = """ SELECT product_base.name AS product_name, product_base.model, product_base.standard, product_base.code, product_base.unit, product_base.notes, product_base.name AS name1, product_warehouse.id AS warehouse_id, product_warehouse.name AS warehouse_name, product_warehouse_stock.warehouse_place, product_warehouse_stock.last_entry_time, product_warehouse_stock.last_deliverd_time, system_option.name AS product_type_text, product_base.id AS product_base_id, SUM(product_warehouse_record.count) AS count, SUM(product_warehouse_record.amount) AS amount, SUM(product_warehouse_record.amount2) AS amount2, product_warehouse_stock.id %(search_data)s FROM product_warehouse_stock LEFT JOIN product_warehouse_record ON product_warehouse_record.product_id = product_warehouse_stock.product_id AND product_warehouse_record.warehouse_id = product_warehouse_stock.warehouse_id LEFT JOIN product_base ON product_warehouse_stock.product_id = product_base.id LEFT JOIN product_warehouse ON product_warehouse_stock.warehouse_id = product_warehouse.id LEFT JOIN system_option on system_option.id = product_base.option_type_id %(left_sql)s WHERE 1=1 %(where_sql)s GROUP BY product_warehouse_stock.id """ % {'where_sql': where_sql, 'left_sql': left_sql, 'search_data': search_data} total_count_sql = 'SELECT COUNT(0),sum(count),sum(amount) FROM (%s) AS t %s' % (sql, search_empty) sql = 'SELECT * FROM (%(sql)s ) AS t %(search_empty)s %(page_sql)s' % { 'sql': sql, 'page_sql': page_sql, 'search_empty': search_empty} items = [] cursor = connection.cursor() cursor.execute(sql) row = cursor.fetchone() while row: if not row[11]: if not row[10]: days = '' else: days = (timezone.now() - row[10]).days else: days = (timezone.now() - row[11]).days avg_cost_price = 0 avg_cost_price2 = 0 if row[14]: avg_cost_price = row[15] / row[14] avg_cost_price2 = row[16] / row[14] item = { 'product_name': row[0], 'product_model': row[1], 'product_standard': row[2], 'product_code': row[3], 'product_unit': row[4], 'product_notes': row[5], 'warehouse_id': row[7], 'warehouse_text': row[8], 'warehouse_place': row[9], 'last_entry_time': strftime(row[10]), 'last_deliverd_time': strftime(row[11]), 'option_type_text': row[12], 'product_id': row[13], 'days': days, 'stock': Formater.formatCountShow(row[14]), 'avg_cost_price': Formater.formatPriceShow(avg_cost_price), 'avg_cost_price2': Formater.formatPriceShow(avg_cost_price2), 'warehouse_amount': Formater.formatAmountShow(row[15]), 'product_warehouse_stock_id':row[17] or '', } items.append(item) row = cursor.fetchone() cursor.execute(total_count_sql) row = cursor.fetchone() if row: total_count = row[0] stock_count = Formater.formatCountShow(row[1]) warehouse_amount = Formater.formatAmountShow(row[2]) else: total_count = 0 stock_count = 0 warehouse_amount = 0 return items, total_count, stock_count, warehouse_amount def fetch_stock_ledger(self, user, product_type, product_name, product_model, date, warehouse,exclude_zero, page, page_size, export=False): more = { 'prev_count': 0, 'prev_amount': 0, 'remain_count': 0, 'remain_amount': 0, 'rk_count': 0, 'rk_amount': 0, 'tl_count': 0, 'tl_amount': 0, 'py_count': 0, 'py_amount': 0, 'ck_count': 0, 'ck_amount': 0, 'th_count': 0, 'th_amount': 0, 'pk_count': 0, 'pk_amount': 0, 'in_count': 0, 'in_amount': 0, 'out_count': 0, 'out_amount': 0 } in_types = [] out_types = [] rk_types = (WarehouseRecord.RK_ZJ, WarehouseRecord.RK_CG) tl_types = (WarehouseRecord.TL,) py_types = (WarehouseRecord.RK_PY,) ck_types = (WarehouseRecord.CK_ZJ, WarehouseRecord.CK_XS) th_types = (WarehouseRecord.TH,) pk_types = (WarehouseRecord.CK_PK,) rk_types = [str(item) for item in rk_types] tl_types = [str(item) for item in tl_types] py_types = [str(item) for item in py_types] ck_types = [str(item) for item in ck_types] th_types = [str(item) for item in th_types] pk_types = [str(item) for item in pk_types] in_types.extend(rk_types) in_types.extend(th_types) in_types.extend(py_types) out_types.extend(ck_types) out_types.extend(tl_types) out_types.extend(pk_types) prev_where_sql = '' detail_where_sql = '' date_begin = '0000-00-00' date_end = timezone.now().date() if date: date = date.split(' - ') date_begin = date[0] date_end = date[1] prev_where_sql += ' AND product_warehouse_record.happen_time < "%s" ' % date_begin detail_where_sql += ' AND product_warehouse_record.happen_time >= "%s" ' % date_begin detail_where_sql += ' AND product_warehouse_record.happen_time <= "%s 23:59:59" ' % date_end where_sql = ' product_base.type = %d ' % int(product_type) warehouse_ids = Warehouse.getManagerWarehouses(user) if not warehouse_ids: return [], 0, more if warehouse_ids.count() == 1: warehouse_ids = '(' + str(warehouse_ids[0]) + ')' else: warehouse_ids = tuple(int(item) for item in warehouse_ids) where_sql += ' AND t.warehouse_id in %s ' % str(warehouse_ids) if product_name: where_sql += ' AND product_base.name LIKE "%%%s%%" ' % product_name if product_model: where_sql += ' AND product_base.model LIKE "%%%s%%" ' % product_model if warehouse: where_sql += ' AND t.warehouse_id = %d ' % int(warehouse) page_sql = ' LIMIT %d OFFSET %d ' % (page_size, page * page_size) sql = """ SELECT product_base.name AS product_name, product_base.model AS product_model, product_base.unit AS product_unit, SUM(t.prev_count) AS prev_count, SUM(t.prev_amount) AS prev_amount, SUM(t.prev_count + t.in_count + t.out_count) AS remain_count, SUM(t.prev_amount + t.in_amount + t.out_amount) AS remain_amount, SUM(t.in_count) AS in_count, SUM(t.in_amount) AS in_amount, SUM(t.rk_count) AS rk_count, SUM(t.rk_amount) AS rk_amount, SUM(t.tl_count) AS tl_count, SUM(t.tl_amount) AS tl_amount, SUM(t.py_count) AS py_count, SUM(t.py_amount) AS py_amount, SUM(t.out_count) AS out_count, SUM(t.out_amount) AS out_amount, SUM(t.ck_count) AS ck_count, SUM(t.ck_amount) AS ck_amount, SUM(t.th_count) AS th_count, SUM(t.th_amount) AS th_amount, SUM(t.pk_count) AS pk_count, SUM(t.pk_amount) AS pk_amount FROM ( SELECT product_warehouse_record.product_id AS product_id, product_warehouse_record.warehouse_id AS warehouse_id, SUM(product_warehouse_record.count) AS prev_count, SUM(product_warehouse_record.amount) AS prev_amount, 0 AS in_count, 0 AS in_amount, 0 AS rk_count, 0 AS rk_amount, 0 AS tl_count, 0 AS tl_amount, 0 AS py_count, 0 AS py_amount, 0 AS out_count, 0 AS out_amount, 0 AS ck_count, 0 AS ck_amount, 0 AS th_count, 0 AS th_amount, 0 AS pk_count, 0 AS pk_amount FROM product_warehouse_record WHERE 1=1 %(prev_where_sql)s GROUP BY product_warehouse_record.product_id, product_warehouse_record.warehouse_id UNION ALL SELECT product_warehouse_record.product_id AS product_id, product_warehouse_record.warehouse_id AS warehouse_id, 0 AS prev_count, 0 AS prev_amount, SUM(IF(product_warehouse_record.type IN (%(in_types)s),product_warehouse_record.count,0)) AS in_count, SUM(IF(product_warehouse_record.type IN (%(in_types)s),product_warehouse_record.amount,0)) AS in_amount, SUM(IF(product_warehouse_record.type IN (%(rk_types)s),product_warehouse_record.count,0)) AS rk_count, SUM(IF(product_warehouse_record.type IN (%(rk_types)s),product_warehouse_record.amount,0)) AS rk_amount, SUM(IF(product_warehouse_record.type IN (%(tl_types)s),product_warehouse_record.count,0)) AS tl_count, SUM(IF(product_warehouse_record.type IN (%(tl_types)s),product_warehouse_record.amount,0)) AS tl_amount, SUM(IF(product_warehouse_record.type IN (%(py_types)s),product_warehouse_record.count,0)) AS py_count, SUM(IF(product_warehouse_record.type IN (%(py_types)s),product_warehouse_record.amount,0)) AS py_amount, SUM(IF(product_warehouse_record.type IN (%(out_types)s),product_warehouse_record.count,0)) AS out_count, SUM(IF(product_warehouse_record.type IN (%(out_types)s),product_warehouse_record.amount,0)) AS out_amount, SUM(IF(product_warehouse_record.type IN (%(ck_types)s),product_warehouse_record.count,0)) AS ck_count, SUM(IF(product_warehouse_record.type IN (%(ck_types)s),product_warehouse_record.amount,0)) AS ck_amount, SUM(IF(product_warehouse_record.type IN (%(th_types)s),product_warehouse_record.count,0)) AS th_count, SUM(IF(product_warehouse_record.type IN (%(th_types)s),product_warehouse_record.amount,0)) AS th_amount, SUM(IF(product_warehouse_record.type IN (%(pk_types)s),product_warehouse_record.count,0)) AS pk_count, SUM(IF(product_warehouse_record.type IN (%(pk_types)s),product_warehouse_record.amount,0)) AS pk_amount FROM product_warehouse_record WHERE 1=1 %(detail_where_sql)s GROUP BY product_warehouse_record.product_id , product_warehouse_record.warehouse_id ) AS t INNER JOIN product_base ON t.product_id = product_base.id WHERE %(where_sql)s GROUP BY t.product_id,t.warehouse_id ORDER BY product_base.id """ % { 'prev_where_sql': prev_where_sql, 'detail_where_sql': detail_where_sql, 'where_sql': where_sql, 'in_types': ','.join(in_types), 'rk_types': ','.join(rk_types), 'tl_types': ','.join(tl_types), 'py_types': ','.join(py_types), 'out_types': ','.join(out_types), 'ck_types': ','.join(ck_types), 'th_types': ','.join(th_types), 'pk_types': ','.join(pk_types), } where_zero_sql = '' if exclude_zero: where_zero_sql = """ AND ( tt.prev_count != 0 OR tt.prev_amount != 0 OR tt.remain_count != 0 OR tt.remain_amount != 0 OR tt.rk_count != 0 OR tt.rk_amount != 0 OR tt.tl_count != 0 OR tt.tl_amount != 0 OR tt.py_count != 0 OR tt.py_amount != 0 OR tt.ck_count != 0 OR tt.ck_amount != 0 OR tt.th_count != 0 OR tt.th_amount != 0 OR tt.pk_count != 0 OR tt.pk_amount != 0 ) """ total = 0 cursor = connection.cursor() if export: if where_zero_sql: sql = 'SELECT * FROM (%s) AS tt WHERE 1=1 %s' % (sql, where_zero_sql) else: if where_zero_sql: sum_sql = """ SELECT SUM(tt.prev_count), SUM(tt.prev_amount), SUM(tt.remain_count), SUM(tt.remain_amount), SUM(tt.rk_count), SUM(tt.rk_amount), SUM(tt.tl_count), SUM(tt.tl_amount), SUM(tt.py_count), SUM(tt.py_amount), SUM(tt.ck_count), SUM(tt.ck_amount), SUM(tt.th_count), SUM(tt.th_amount), SUM(tt.pk_count), SUM(tt.pk_amount), SUM(tt.in_count), SUM(tt.in_amount), SUM(tt.out_count), SUM(tt.out_amount), COUNT(0) FROM (%s) AS tt WHERE 1=1 %s """ % (sql, where_zero_sql) sql = 'SELECT * FROM (%s) AS tt WHERE 1=1 %s %s' % (sql, where_zero_sql, page_sql) else: sum_sql = """ SELECT SUM(tt.prev_count), SUM(tt.prev_amount), SUM(tt.remain_count), SUM(tt.remain_amount), SUM(tt.rk_count), SUM(tt.rk_amount), SUM(tt.tl_count), SUM(tt.tl_amount), SUM(tt.py_count), SUM(tt.py_amount), SUM(tt.ck_count), SUM(tt.ck_amount), SUM(tt.th_count), SUM(tt.th_amount), SUM(tt.pk_count), SUM(tt.pk_amount), SUM(tt.in_count), SUM(tt.in_amount), SUM(tt.out_count), SUM(tt.out_amount), COUNT(0) FROM (%s) AS tt """ % sql sql = '%s %s' % (sql, page_sql) cursor.execute(sum_sql) row = cursor.fetchone() if row: total = row[20] more = { 'prev_count': Formater.formatCountShow(row[0]), 'prev_amount': Formater.formatAmountShow(row[1]), 'remain_count': Formater.formatCountShow(row[2]), 'remain_amount': Formater.formatAmountShow(row[3]), 'rk_count': Formater.formatCountShow(row[4]), 'rk_amount': Formater.formatAmountShow(row[5]), 'tl_count': Formater.formatCountShow(row[6]), 'tl_amount': Formater.formatAmountShow(row[7]), 'py_count': Formater.formatCountShow(row[8]), 'py_amount': Formater.formatAmountShow(row[9]), 'ck_count': Formater.formatCountShow(-(row[10] or 0)), 'ck_amount': Formater.formatAmountShow(-(row[11] or 0)), 'th_count': Formater.formatCountShow(-(row[12] or 0)), 'th_amount': Formater.formatAmountShow(-(row[13] or 0)), 'pk_count': Formater.formatCountShow(-(row[14] or 0)), 'pk_amount': Formater.formatAmountShow(-(row[15] or 0)), 'in_count': Formater.formatCountShow(row[16]), 'in_amount': Formater.formatAmountShow(row[17]), 'out_count': Formater.formatCountShow(-(row[18] or 0)), 'out_amount': Formater.formatAmountShow(-(row[19] or 0)) } else: total = 0 more = { 'prev_count': 0, 'prev_amount': 0, 'remain_count': 0, 'remain_amount':0, 'rk_count': 0, 'rk_amount': 0, 'tl_count': 0, 'tl_amount': 0, 'py_count': 0, 'py_amount': 0, 'ck_count': 0, 'ck_amount':0, 'th_count': 0, 'th_amount': 0, 'pk_count': 0, 'pk_amount': 0, 'in_count': 0, 'in_amount': 0, 'out_count': 0, 'out_amount':0 } data = [] cursor.execute(sql) row = cursor.fetchone() while row: prev_avg = 0 in_avg = 0 out_avg = 0 remain_avg = 0 if row[6]: prev_avg = row[7] / row[6] if row[7]: in_avg = row[8] / row[7] if row[15]: out_avg = row[16] / row[15] if row[5]: remain_avg = row[6] / row[5] item = { 'product_name': row[0], 'product_model': row[1], 'unit': row[2], 'prev_count': Formater.formatCountShow(row[3]), 'prev_avg': Formater.formatPriceShow(prev_avg), 'prev_amount': Formater.formatAmountShow(row[4]), 'in_count': Formater.formatCountShow(row[7]), 'in_avg': Formater.formatPriceShow(in_avg), 'in_amount': Formater.formatAmountShow(row[8]), 'rk_count': Formater.formatCountShow(row[9]), 'rk_amount': Formater.formatAmountShow(row[10]), 'tl_count': Formater.formatCountShow(row[11]), 'tl_amount': Formater.formatAmountShow(row[12]), 'py_count': Formater.formatCountShow(row[13]), 'py_amount': Formater.formatAmountShow(row[14]), 'out_count': Formater.formatCountShow(-(row[15] or 0)), 'out_avg': Formater.formatPriceShow(out_avg), 'out_amount': Formater.formatAmountShow(-(row[16] or 0)), 'ck_count': Formater.formatCountShow(-(row[17] or 0)), 'ck_amount': Formater.formatAmountShow(-(row[18] or 0)), 'th_count': Formater.formatCountShow(-(row[19] or 0)), 'th_amount': Formater.formatAmountShow(-(row[20] or 0)), 'pk_count': Formater.formatCountShow(-(row[21] or 0)), 'pk_amount': Formater.formatAmountShow(-(row[22] or 0)), 'remain_count': Formater.formatCountShow(row[5]), 'remain_avg': Formater.formatPriceShow(remain_avg), 'remain_amount': Formater.formatAmountShow(row[6]), } data.append(item) row = cursor.fetchone() return data, total, more class WarehouseStock(models.Model): product = models.ForeignKey(ProductBase, verbose_name=u"产品", on_delete=models.PROTECT) warehouse = models.ForeignKey(Warehouse, verbose_name=u"仓别", on_delete=models.PROTECT) warehouse_place = models.CharField(max_length=200, verbose_name=u"库位", null=True) count = models.BigIntegerField(verbose_name=u"库存数量", default=0) amount = models.BigIntegerField(verbose_name=u"库存金额", default=0) amount2 = models.BigIntegerField(verbose_name=u"库存金额2", default=0) avg_cost_price = models.BigIntegerField(verbose_name=u"平均成本价", default=0) avg_cost_price2 = models.BigIntegerField(verbose_name=u"平均成本价2", default=0) last_entry_price = models.BigIntegerField(verbose_name=u"最新入库价", default=0) last_entry_time = models.DateTimeField(verbose_name=u"最后入库时间", null=True) last_deliverd_time = models.DateTimeField(verbose_name=u"最后出库时间", null=True) objects = WarehouseStockManager() @staticmethod def getByWarehouseAndProduct(warehouse,product): warehouse_stock = WarehouseStock.objects.filter(product=product, warehouse=warehouse).first() if not warehouse_stock: raise CustomError(u'仓别[%s]中没有[%s]的库存' % (warehouse.name, product.name)) return warehouse_stock @staticmethod def createByWarehouse(warehouse): exist_products = WarehouseStock.objects.filter(warehouse_id=warehouse.id).values_list('product_id',flat=True) ids = ProductBase.objects.filter(Q(type=warehouse.type),~Q(id__in=exist_products)).values('id') if not ids: return sql = ''' INSERT INTO product_warehouse_stock ( product_id, warehouse_id, count, amount, amount2, avg_cost_price, avg_cost_price2, last_entry_price) SELECT id, %d, 0, 0, 0, 0, 0, 0 FROM product_base where id in (%s) ''' % (warehouse.id, ','.join([str(item['id']) for item in ids])) cur = connection.cursor() cur.execute(sql) @staticmethod def createByProduct(product): exist_warehouses = WarehouseStock.objects.filter(product_id=product.id).values_list('warehouse_id', flat=True) ids = Warehouse.objects.filter(Q(type=product.type), ~Q(id__in=exist_warehouses)).values('id') if not ids: return sql = ''' INSERT INTO product_warehouse_stock ( product_id, warehouse_id, count, amount, amount2, avg_cost_price, avg_cost_price2, last_entry_price) SELECT %d, id, 0, 0, 0, 0, 0, 0 FROM product_warehouse where id in (%s) ''' % (product.id, ','.join([str(item['id']) for item in ids])) cur = connection.cursor() cur.execute(sql) @staticmethod def removeStockByProduct(product): WarehouseStock.objects.filter(product=product).delete() @staticmethod def getPermissionByType(type, action): permissions = WarehouseStock.getPermissionMap() type = WarehouseStock.getValidType(type) return permissions[type][action] @staticmethod def getPermissionMap(): permissions = { ProductBase.MATERIAL: {'view': 'warehouse.view_material_stock', 'export': 'warehouse.export_material_stock', 'log': 'warehouse.view_material_stock_log' }, ProductBase.CONSUMABLE: {'view': 'warehouse.view_consumable_stock', 'export': 'warehouse.export_consumable_stock', 'log': 'warehouse.view_consumable_stock_log' }, ProductBase.GOODS: {'view': 'warehouse.view_goods_stock', 'export': 'warehouse.export_goods_stock', 'log': 'warehouse.view_goods_stock_log' }, } return permissions @staticmethod def getValidType(type): try: type = int(type) except: raise CustomError(u'错误的类型') types = (r[0] for r in ProductBase.TYPE_CHOICES) if type not in types: raise CustomError(u'无效的类型') return type class Meta: db_table = "product_warehouse_stock" verbose_name = u"成品仓别管理" ordering = ('-id',) default_permissions = () permissions = ( ("view_goods_warehouse", u"浏览"), ("add_goods_warehouse", u"添加"), ("delete_goods_warehouse", u"删除"), ) class WarehouseRecord(models.Model): RK_ZJ = 0 RK_CG = 1 RK_PY = 2 CK_ZJ = 3 CK_XS = 4 CK_PK = 5 TH = 6 TL = 7 TYPE_CHOICES = ( (RK_ZJ, u'直接入库'), (RK_CG, u'采购入库'), (RK_PY, u'盘盈入库'), (CK_ZJ, u'直接出库'), (CK_XS, u'销售出库'), (CK_PK, u'盘亏出库'), (TH, u'退货'), (TL, u'退料'), ) type = models.IntegerField(choices=TYPE_CHOICES, verbose_name=u"类型") happen_time = models.DateTimeField(verbose_name=u"发生时间", default=timezone.now) product = models.ForeignKey(ProductBase, verbose_name=u"产品", on_delete=models.PROTECT) warehouse = models.ForeignKey(Warehouse, verbose_name=u"仓别", on_delete=models.PROTECT) count = models.BigIntegerField(verbose_name=u"数量") amount = models.BigIntegerField(verbose_name=u"总金额") amount2 = models.BigIntegerField(verbose_name=u"总金额2") cur_count = models.BigIntegerField(verbose_name=u"剩余数量", default=0) cur_amount = models.BigIntegerField(verbose_name=u"剩余金额", default=0) cur_amount2 = models.BigIntegerField(verbose_name=u"剩余金额2", default=0) @staticmethod def updateCurStockByProduct(warehouse_id, product_id): front_row = None rows = WarehouseRecord.objects.filter(warehouse_id=warehouse_id, product_id=product_id).order_by('happen_time','-id') for row in rows: WarehouseRecord.updateCurStockByFront(row, front_row) front_row = row @staticmethod def updateCurStockByFront(warehouse_record, front_row): if not front_row: warehouse_record.cur_count = warehouse_record.count warehouse_record.cur_amount = warehouse_record.amount warehouse_record.cur_amount2 = warehouse_record.amount2 else: warehouse_record.cur_count = front_row.cur_count + warehouse_record.count warehouse_record.cur_amount = front_row.cur_amount + warehouse_record.amount warehouse_record.cur_amount2 = front_row.cur_amount2 + warehouse_record.amount2 warehouse_record.save() @staticmethod def getPermissionByType(type, action): permissions = WarehouseRecord.getPermissionMap() type = WarehouseRecord.getValidType(type) return permissions[type][action] @staticmethod def getPermissionMap(): permissions = { ProductBase.MATERIAL: {'view': 'product.view_material_stock_ledger', 'export': 'product.export_material_stock_ledger' }, ProductBase.CONSUMABLE: {'view': 'product.view_consumable_stock_ledger', 'export': 'product.export_consumable_stock_ledger' }, ProductBase.GOODS: {'view': 'product.view_goods_stock_ledger', 'export': 'product.export_goods_stock_ledger' }, } return permissions @staticmethod def getValidType(type): try: type = int(type) except: raise CustomError(u'错误的类型') types = (r[0] for r in ProductBase.TYPE_CHOICES) if type not in types: raise CustomError(u'无效的类型') return type class Meta: db_table = "product_warehouse_record" verbose_name = u"退料查询" ordering = ('-id',) index_together = ( 'type', 'happen_time', ) default_permissions = () permissions = (# 产品仓别出入库记录 ("view_material_deliver_return_query", u"浏览"), ("export_material_deliver_return_query", u"导出"), ("print_material_deliver_return_query", u"打印"), ) class WarehouseStockRecord(models.Model): product = models.ForeignKey(ProductBase, verbose_name=u"产品", on_delete=models.PROTECT) warehouse = models.ForeignKey(Warehouse, verbose_name=u"仓别", on_delete=models.PROTECT) supplier = models.ForeignKey(Supplier, verbose_name=u"供应商", null=True, on_delete=models.PROTECT) entry_time = models.DateTimeField(verbose_name=u"入库时间", default=timezone.now) entry_count = models.BigIntegerField(verbose_name=u"入库数量") entry_price = models.BigIntegerField(verbose_name=u"入库成本价") entry_price2 = models.BigIntegerField(verbose_name=u"入库成本价2") surplus_count = models.BigIntegerField(u'剩余数量') class Meta: db_table = "product_warehouse_stock_record" verbose_name = u"库存查询" ordering = ('-id',) index_together = ( 'entry_time', 'surplus_count', ) default_permissions = () permissions = (# 产品仓别库存记录 ("view_material_stock", u"浏览"), ("export_material_stock", u"导出"), ("view_material_stock_log", u"查看动态"), ) class WarehouseRecordDetail(models.Model): warehouse_record = models.ForeignKey(WarehouseRecord, verbose_name=u"出入库记录",related_name='warehouse_record_detail_ref_warehouse_record', on_delete=models.PROTECT) warehouse_stock_record = models.ForeignKey(WarehouseStockRecord, verbose_name=u"库存记录", related_name='warehouse_record_detail_ref_stock_record',on_delete=models.PROTECT) count = models.BigIntegerField(verbose_name=u"数量") class Meta: db_table = "product_warehouse_record_detail" verbose_name = u"库存查询" ordering = ('-id',) default_permissions = () permissions = (# 产品仓别出入库记录明细 ("view_consumable_stock", u"浏览"), ("export_consumable_stock", u"导出"), ("view_consumable_stock_log", u"查看动态"), ) class WarehouseBackMap(models.Model): back_detail = models.ForeignKey(WarehouseRecordDetail, verbose_name=u"退库明细", related_name="product_warehouse_back_map_ref_back_detail",on_delete=models.PROTECT) delivered_detail = models.ForeignKey(WarehouseRecordDetail, verbose_name=u"出库明细", related_name="product_warehouse_back_map_ref_delivered_detail",on_delete=models.PROTECT) back_count = models.BigIntegerField(verbose_name=u"退库数量") class Meta: db_table = "product_warehouse_back_map" verbose_name = u"库存查询" ordering = ('-id',) default_permissions = () permissions = (# 产品仓别退库映射 ("view_goods_stock", u"浏览"), ("export_goods_stock", u"导出"), ("view_goods_stock_log", u"查看动态"), ) class Inventory(models.Model): MATERIAL = 0 CONSUMABLE = 1 GOODS = 2 PRODUCT_TYPE_CHOICES = ( (MATERIAL, u'原料'), (CONSUMABLE, u'耗材'), (GOODS, u'成品'), ) SURPLUS = 0 LOSS = 1 TYPE_CHOICES = ( (SURPLUS, u'盘盈'), (LOSS, u'盘亏'), ) TYPE_PREFIX = ('MAPC', 'COPC', 'GOPC') no = models.CharField(max_length=50, verbose_name=u"盘存单号", editable=False) product_type = models.PositiveSmallIntegerField(choices=PRODUCT_TYPE_CHOICES, verbose_name=u"产品类型") warehouse = models.ForeignKey(Warehouse, verbose_name=u"仓别", on_delete=models.PROTECT) type = models.PositiveSmallIntegerField(choices=TYPE_CHOICES, verbose_name=u"单据类型") create_time = models.DateTimeField(verbose_name=u"创建时间", default=timezone.now) create_user = models.ForeignKey(User, verbose_name=u"创建人", on_delete=models.PROTECT, editable=False) department = models.ForeignKey(Department, verbose_name=u"创建部门", editable=False, on_delete=models.PROTECT) check_time = models.DateTimeField(verbose_name=u"审核时间", editable=False, blank=True, null=True) check_user = models.ForeignKey(User, related_name='inventory_checkuser', verbose_name=u"审核人", blank=True, null=True,on_delete=models.PROTECT) check_status = models.PositiveSmallIntegerField(choices=settings.CHECK_STATUS_CHOICES, verbose_name=u"审核状态",default=settings.DEFAULT) notes = models.CharField(max_length=500, verbose_name=u"备注", blank=True, null=True) total_count = models.BigIntegerField(verbose_name=u"合计数量", default=0) total_amount = models.BigIntegerField(verbose_name=u"合计金额", default=0) total_return_count = models.BigIntegerField(verbose_name=u"退货数量合计", default=0) total_return_amount = models.BigIntegerField(verbose_name=u"退货金额合计", default=0) total_deliver_count = models.BigIntegerField(verbose_name=u"出库数量合计", default=0) total_deliver_amount = models.BigIntegerField(verbose_name=u"出库金额合计", default=0) @staticmethod def getById(id): instance = Inventory.objects.filter(pk=id).first() if not instance: raise CustomError(u'未找到相应的盘存单') return instance def updateAmount(self): sum_count = 0 sum_amount = 0 sum_rows = InventoryDetail.objects.filter(main=self).aggregate(sum_count=Sum('count'), sum_amount=Sum('amount')) if sum_rows: sum_count = sum_rows['sum_count'] or 0 sum_amount = sum_rows['sum_amount'] or 0 self.total_count = sum_count self.total_amount = sum_amount self.save() def update_redundant(self): total_return_count = 0 total_return_amount = 0 total_deliver_count = 0 total_deliver_amount = 0 sum_row = InventoryDetail.objects.filter(main=self).aggregate(sum_return_count=Sum('return_count'), sum_return_amount=Sum('return_amount'), sum_deliver_count=Sum('deliver_count'), sum_deliver_amount=Sum('deliver_amount')) if sum_row: total_return_count = sum_row['sum_return_count'] or 0 total_return_amount = sum_row['sum_return_amount'] or 0 total_deliver_count = sum_row['sum_deliver_count'] or 0 total_deliver_amount = sum_row['sum_deliver_amount'] or 0 self.total_return_count = total_return_count self.total_return_amount = total_return_amount self.total_deliver_count = total_deliver_count self.total_deliver_amount = total_deliver_amount self.save() def save(self, *args, **kwargs): if self.no == None or self.no == '': prefix = Inventory.TYPE_PREFIX[self.product_type] now = timezone.now() rows = Inventory.objects.filter(create_time__gte=now.strftime('%Y-%m-%d')).order_by('-no') count = rows.count() if count == 0: self.no = '%s%s%03d' % (prefix, now.strftime('%Y%m%d'), count + 1) else: self.no = rows[0].no[:4] + str(int(rows[0].no[4:]) + 1) super(Inventory, self).save(*args, **kwargs) def getPermission(self, action): permissions = Inventory.getPermissionMap() return permissions[self.product_type][action] @staticmethod def getPermissionByType(type, action): permissions = Inventory.getPermissionMap() type = Inventory.getValidType(type) return permissions[type][action] @staticmethod def getPermissionMap(): permissions = { Inventory.MATERIAL: {'view': 'warehouse.view_material_inventory', 'add': 'warehouse.add_material_inventory', 'check': 'warehouse.check_material_inventory', 'delete': 'warehouse.delete_material_inventory', 'export': 'warehouse.export_material_inventory', 'print': 'warehouse.print_material_inventory' }, Inventory.CONSUMABLE: {'view': 'warehouse.view_consumable_inventory', 'add': 'warehouse.add_consumable_inventory', 'check': 'warehouse.check_consumable_inventory', 'delete': 'warehouse.delete_consumable_inventory', 'export': 'warehouse.export_consumable_inventory', 'print': 'warehouse.print_consumable_inventory' }, Inventory.GOODS: {'view': 'goods.view_goods_inventory', 'add': 'goods.add_goods_inventory', 'check': 'goods.check_goods_inventory', 'delete': 'goods.delete_goods_inventory', 'export': 'goods.export_goods_inventory', 'print': 'goods.print_goods_inventory' }, } return permissions @staticmethod def getValidType(type): try: type = int(type) except: raise CustomError(u'错误的盘存类型') types = (r[0] for r in Inventory.PRODUCT_TYPE_CHOICES) if type not in types: raise CustomError(u'无效的盘存类型') return type class Meta: db_table = "inventory" ordering = ('-id',) verbose_name = u"盘存管理" index_together = ( 'create_time', 'check_time' ) unique_together = ( 'no', ) default_permissions = () permissions = ( ("view_material_inventory", u"浏览"), ("add_material_inventory", u"添加"), ("check_material_inventory", u"审核"), ("delete_material_inventory", u"删除"), ("export_material_inventory", u"导出"), ("print_material_inventory", u"打印"), ) class InventoryDetail(models.Model): main = models.ForeignKey(Inventory, related_name='inventory_details', on_delete=models.PROTECT) product = models.ForeignKey(ProductBase, verbose_name=u"产品", on_delete=models.PROTECT) warehouse_stock = models.ForeignKey(WarehouseStock, verbose_name=u'仓别库存', on_delete=models.PROTECT, blank=True, editable=False) count = models.BigIntegerField(verbose_name=u"数量", default=0) price = models.BigIntegerField(verbose_name=u"单价", default=0) amount = models.BigIntegerField(verbose_name=u"金额", default=0) warehouse_record = models.ForeignKey(WarehouseRecord,related_name='inventory_details_ref_warehouse_record', verbose_name=u'出入库记录', on_delete=models.PROTECT, blank=True, null=True) warehouse_stock_record = models.ForeignKey(WarehouseStockRecord, related_name='inventory_details_ref_warehouse_stock_record', verbose_name=u'库存记录', on_delete=models.PROTECT, blank=True, null=True) loss_stock_record = models.ForeignKey(WarehouseStockRecord, related_name='inventory_details_ref_loss_stock_record', verbose_name=u'盘亏库存记录', on_delete=models.PROTECT, blank=True, null=True) return_count = models.BigIntegerField(verbose_name=u"退货数量", default=0) return_amount = models.BigIntegerField(verbose_name=u"退货金额", default=0) deliver_count = models.BigIntegerField(verbose_name=u"出库数量", default=0) deliver_amount = models.BigIntegerField(verbose_name=u"出库金额", default=0) notes = models.CharField(max_length=200, verbose_name=u"备注", blank=True, null=True) class Meta: verbose_name = u"盘存管理" db_table = "inventory_detail" ordering = ("-id",) default_permissions = () permissions = (# 盘存明细 ("view_consumable_inventory", u"浏览"), ("add_consumable_inventory", u"添加"), ("check_consumable_inventory", u"审核"), ("delete_consumable_inventory", u"删除"), ("export_consumable_inventory", u"导出"), ("print_consumable_inventory", u"打印"), ) @staticmethod def getById(id): instance = InventoryDetail.objects.filter(pk=id).first() if not instance: raise CustomError(u'未找到相应的盘存明细') return instance