# -*- coding: utf-8 -*-
from __future__ import unicode_literals
from django.db import models
# Create your models here.
from django.utils.translation import gettext_lazy as _
import pyodbc
from django.conf import settings
from django.db import connection
import os


#产品图像上传路径
def product_img_upload(instance,filename):    
    return os.path.join(instance.company.name,'product', filename)  




class ProductProp(models.Model):
    company = models.ForeignKey('company.Company', verbose_name=_('公司名称'), on_delete=models.CASCADE)
    categoryprop = models.ForeignKey('company.CategoryProp', verbose_name=_('产品属性'), on_delete=models.CASCADE)
    value = models.CharField(_('属性值'), max_length=50, db_index=True)
    remark = models.CharField(_('说明'), max_length=5000, db_index=True)
    is_delete = models.BooleanField(_('已删除'), default=False)

    class Meta:
        verbose_name =_('产品属性')
        verbose_name_plural =_('产品属性')
        unique_together = ('company', 'categoryprop', 'value',)
        
    def __str__(self):
        # return self.value
        return self.categoryprop.name+': '+self.value




#2022-03-03
class ProductCategory(models.Model):
    company = models.ForeignKey('company.Company', verbose_name=_('公司名称'), on_delete=models.CASCADE)
    name = models.CharField(_('大类名称'), max_length=100)
    remark = models.CharField(_('备注说明'), max_length=100, blank=True, null=True)

    class Meta:
        verbose_name = _('产品大类')
        verbose_name_plural = _('产品大类')
        unique_together = ('company', 'name')
        

    def __str__(self):
        return self.name
    




#2020-04-01
class Series(models.Model):
    company = models.ForeignKey('company.Company', verbose_name=_('所属公司'), related_name='series_company',on_delete=models.CASCADE)
    
    #2022-03-03
    category=models.ForeignKey(ProductCategory,verbose_name=_('产品大类'),blank=True,null=True,on_delete=models.CASCADE)
    external_code = models.CharField(_('外部编码'), max_length=30, blank=True, null=True)
    name = models.CharField(_('系列名称'), max_length=100)    
    icon=models.CharField(_('图标'),max_length=50,blank=True,null=True)
    sn=models.SmallIntegerField(_('排序'),blank=True,null=True,default=1)
    remark = models.CharField(_('备注说明'), max_length=1000, blank=True, null=True)
    is_delete = models.BooleanField(_('已删除'), default=False)
    photo=models.ImageField(_('产品图片'), upload_to=product_img_upload, blank=True,null=True)
    banner=models.ImageField(_('产品banner'), upload_to=product_img_upload, blank=True,null=True)

    class Meta:
        verbose_name = _('产品系列')
        verbose_name_plural = _('产品系列')
        ordering = ('sn',)

    def __str__(self):
        return self.name
    
    

class Product(models.Model):
    company = models.ForeignKey('company.Company', verbose_name=_('所属公司'), on_delete=models.CASCADE)
    series = models.ForeignKey(Series, verbose_name=_('产品系列'), on_delete=models.CASCADE,blank=True,null=True)
    external_series=models.CharField(_('产品系列外部编码'),max_length=20,blank=True,null=True)
    name = models.CharField(_('产品名称'), max_length=100)
    price=models.DecimalField(_('产品价格'),max_digits=10,decimal_places=2,blank=True,null=True)
    photo=models.ImageField(_('产品图片'), upload_to=product_img_upload, blank=True,null=True)
    external_id = models.IntegerField(_('外部ID'), blank=True, null=True, unique=True)
    legacy_id = models.IntegerField(_('内部ID'), blank=True, null=True)
    external_code = models.CharField(_('外部编码'), max_length=30, blank=True, null=True)
    is_active = models.BooleanField(_('已激活'), default=True)
    is_delete = models.BooleanField(_('已删除'), default=False)
    tm = models.DateTimeField(_('时间'), auto_now_add=True)
    qty_x = models.SmallIntegerField(_('单件箱数'), default=0, blank=True, null=True)
    qty_h = models.SmallIntegerField(_('单件盒数'), default=0, blank=True, null=True)
    qty_p = models.IntegerField(_('单件瓶/支数'), default=0, blank=True, null=True)
    ty_id = models.IntegerField(_('酒鬼透云ID'), unique=True, blank=True, null=True)
    remark = models.CharField(_('备注说明'), max_length=200, blank=True, null=True)
    barcode = models.CharField(_('产品条型码'), max_length=20, blank=True, null=True)   #茗酒荟产品条码不唯一
    #新增品牌ID
    brand = models.ForeignKey('company.Brands', verbose_name=_('所属品牌'), blank=True, null=True, on_delete=models.SET_NULL)
    #2021-04-26 美颜秘笈
    specs=models.CharField(_('产品规格'),max_length=200,blank=True,null=True)
    plant_code=models.CharField(_('工厂代码'),max_length=10,blank=True,null=True)
    contents = models.TextField(_('介绍'), blank=True, null=True)
    is_banquet = models.BooleanField(_('是否为宴会产品'), default=False, blank=True, null=True)
    pl = models.CharField(_('生产许可'),max_length=50,blank=True,null=True)
    standard = models.CharField(_('执行标准'),max_length=50,blank=True,null=True)
    
    processflow = models.CharField(_('加工流程'),max_length=200,blank=True,null=True)
    shelf_life=models.JSONField(_('保质期'),blank=True,null=True,default=dict)
    desc = models.TextField(_('产品描述'), blank=True, null=True)
    # 2022-12-08 组合包装产品，如一个大礼包含两种以上产品 [{"id":"1","name":"品名","qty":"1"}]    
    components=models.JSONField(_('组合包装产品明细'),blank=True,null=True,default=dict)
    # 2023-10-31
    bottlecap=models.SmallIntegerField(_('瓶盖码'),blank=True,null=True,default=0)
    scpc = models.TextField(_('产品规格JSON格式'), blank=True, null=True)
    
    class Meta:
        verbose_name = _('产品信息')
        verbose_name_plural = _('产品信息')
        unique_together = ('company', 'name')

    def __str__(self):
        return self.name

    def get_props_cn(self):
        a = []
        if self.id:
            for p in self.prdprops_set.all():
                u = ''
                if p.prd_property.categoryprop.unit:
                    u = ' (' + p.prd_property.categoryprop.unit + ')'
                a.append(p.prd_property.categoryprop.desc_cn + ":" + p.prd_property.value + u)
        return "; ".join(a)
    def get_property(self,prop_name,category_id=1):
        s="select value from product_productprop p where id="+\
            "(SELECT prd_property_id  FROM product_prdprops where product_id="+str(self.id)+" "+\
                "and (SELECT categoryprop_id FROM product_productprop where id=prd_property_id)="+\
		    "(select id from company_categoryprop where name='"+prop_name+"' and category_id="+\
                        str(category_id)+" limit 1)"+\
            ")"   
        cur=connection.cursor()
        cur.execute(s)
        r=cur.fetchone()
        p=''
        if r:
            p=r[0]
        cur.close()
        return p

    '''
    # 保存到SQL SRV
    def save(self, *args, **kwargs):
        cn = pyodbc.connect(settings.WULIU_STR)
        cur = cn.cursor()
        l_id = 0
        if not self.id:
            c_id = self.company.legacy_id
            if c_id:
                s = "insert into TianJianWuLiuWebnew.dbo.TB_Products_Infor(Products_Name,CompID,Products_date)" + \
                    " values ('" + self.name + "'," + str(c_id) + ",getdate())"
                cur.execute(s)
                cur.execute('SELECT @@IDENTITY')
                r = cur.fetchone()
                if r:
                    l_id = r[0]
                    self.legacy_id = l_id
                    cn.commit()
        else:
            cur_pg = connection.cursor()
            cur_pg.execute("select legacy_id from product_product where id=" + str(self.id))
            r = cur_pg.fetchone()
            if r[0]:
                self.legacy_id = r[0]
                cur.execute("update TianJianWuLiuWebnew.dbo.TB_Products_Infor set Products_Name='" + self.name + "' " + \
                    " where Infor_ID=" + str(r[0]))
                cn.commit()
        super(Product, self).save(*args, **kwargs)
        if l_id:
            cur.execute("update TianJianWuLiuWebnew.dbo.TB_Products_Infor set pg_stk_id=" + str(self.id) + \
                        " where Infor_ID="+str(l_id))
            cn.commit()
        cur.close()
        cn.close()
    '''


class PrdProps(models.Model):
    product = models.ForeignKey(Product, verbose_name=_('产品名称'), on_delete=models.CASCADE)
    prd_property = models.ForeignKey(ProductProp, verbose_name=_('属性值'), on_delete=models.CASCADE)
    remark = models.CharField(_('备注说明'), max_length=100, blank=True, null=True)
    is_delete = models.BooleanField(_('已删除'), default=False)

    class Meta:
        verbose_name = _('产品属性列表')
        verbose_name_plural = _('产品属性列表')
        unique_together = ('product', 'prd_property',)

    # def __str__(self):
    #    return self.prd_category.name
    #    return ''




class AuthQuery(models.Model):
    user= models.ForeignKey('company.User', verbose_name=_('用户'), on_delete=models.CASCADE)
    company = models.ForeignKey('company.Company', verbose_name=_('所属公司'), on_delete=models.CASCADE,related_name='authquery_company',default=1)
    what=models.CharField(_('授权方式'),max_length=20,choices=(('series',_('产品系列')),('product',_('产品名称'))))
    content=models.CharField(_('授权内容'),max_length=100,blank=True,null=True)
    auth_name=models.CharField(_('授权名称'),max_length=500,blank=True,null=True)
    condition=models.CharField(_('查询条件'),max_length=200,blank=True,null=True)

    class Meta:
        verbose_name = _('市场稽察产品授权')
        verbose_name_plural = _('市场稽察产品授权')
        

class Test(models.Model):
    name=models.CharField(max_length=10)
    address=models.CharField(max_length=20)

class Images(models.Model):
    company = models.ForeignKey('company.Company', verbose_name=_('所属公司'), on_delete=models.CASCADE)
    product = models.ForeignKey(Product, verbose_name=_('产品'), on_delete=models.CASCADE)
    photo=models.CharField(_('产品图片'), max_length=500, blank=True, null=True)
    show_order = models.SmallIntegerField(_('显示排序'), default=0, blank=True, null=True)
    remark = models.CharField(_('备注说明'), max_length=50, blank=True, null=True)
    
    class Meta:
        verbose_name = _('产品图片')
        verbose_name_plural = _('产品图片')


#2021-09-29 产品生产批次
class Batch(models.Model):
    company = models.ForeignKey('company.Company', verbose_name=_('所属公司'), on_delete=models.CASCADE)
    product=models.ForeignKey(Product,verbose_name=_('产品'),on_delete=models.CASCADE)
    number=models.CharField(_('批次号'),max_length=100)
    label_pk_id=models.BigIntegerField(_('主箱标ID'),unique=True)
    dt=models.DateField(_('日期'),auto_now_add=True)
    tm=models.DateTimeField(_('时间'),auto_now_add=True)

    class Meta:
        verbose_name=_('生产批次')
        verbose_name_plural=_('生产批次')


#class LabelBatch(models.Model):
#    company = models.ForeignKey('company.Company', verbose_name=_('所属公司'), on_delete=models.CASCADE)
#    batch=models.ForeignKey(Batch,verbose_name=_('生产批次'),on_delete=models.CASCADE)
#    label_pk_id=models.BigIntegerField(_('主箱标ID'))
#    tm=models.DateTimeField(_('时间'),auto_now_add=True)

#    class Meta:
#        verbose_name=_('标签生产批次')
#        verbose_name_plural=_('标签生产批次')




# 2024-06-24 特例产品表
class Excemptions(models.Model):
    company = models.ForeignKey('company.Company', verbose_name=_('所属公司'), on_delete=models.CASCADE,related_name='excemptions_company')    
    category_id=models.SmallIntegerField(_('类别'),choices=(
            (1,_('包装规格检查')),
        ), default=1)
    products=models.JSONField(_('产品列表'),default=list)
    tm=models.DateTimeField(_('时间'),blank=True,null=True,auto_now_add=True) 
    remark=models.CharField(_('备注'),blank=True,null=True,max_length=64)

    class Meta:
        unique_together=('company','category_id')
        verbose_name = _('特例产品表')
        verbose_name_plural = _('特例产品表')


