scj/apps/warehouse/models.py

#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