from django.shortcuts import render
import json

from django.views.decorators.csrf import csrf_exempt
from django.http import HttpResponse, HttpResponseRedirect, JsonResponse,FileResponse,StreamingHttpResponse
from django.contrib.auth.decorators import login_required

# Create your views here.
from django.conf import settings

import datetime,hashlib,math

from datacenter.settings import internal_Sk

from django.db import connection
import requests


import os
from tempfile import TemporaryFile, TemporaryDirectory
from django.utils.encoding import escape_uri_path


# 2023-11-01
# 用WinRAR为瓶盖厂准备带密码(用户帐号username+tjzip)的ZIP文件，以供其下载
pRAR='C:\"Program Files"\WinRAR\WinRAR.exe'
pZip='F:\\hzy\\log\\zip'  # zip文件存放路径

#from wsgiref.util import FileWrapper
#from system.rangefilewrapper import RangeFileWrapper
#import re
#import mimetypes


from bottlecap.models import *
import zipfile,pyzipper






#2020-10-27 11位解密
def decrypt_11(code):  
    c=''
    if len(code)==11:  
        s=code.lower()[:10]    
        code=s[9]+s[2]+s[1]+s[3:9]+s[0] 
        try:
            c=str(int('0x'+code,16))
        except Exception:
            c=code
    return c


def decrypt_11h(code):    
    if code[0].lower() in ('h','g'):
        return decrypt_11(code[1:])
    return ''



def format_name(code_type,dt,inform_id,qty, company_name=None):
    if company_name:
        fn=company_name+"_"+dt.strftime('%Y%m%d')+"_"+code_type+"_"+str(inform_id)+"_"+str(qty)+'枚'
    else:
        fn=dt.strftime('%Y%m%d')+"_"+code_type+"_"+str(inform_id)+"_"+str(qty)+'枚'
    return fn

# 2024-08-19
def format_zfile(opt,fcty_id,code_type,dt,inform_id,qty, company_name=None):
    '''
    生成或下载业务通知单盖码数据的zip文件名
    pZip \\company_id\\yyyymmdd_codetype_informid_qty枚.zip
    '''   
    fn=os.path.join(pZip, opt, str(fcty_id),
        format_name(code_type,dt,inform_id,qty, company_name=company_name)+".zip"
    ) 
    return fn


def inernal_key():
    t=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    k=internal_Sk
    m2=hashlib.md5((k+t).encode('utf8'))
    key=m2.hexdigest()
    return {'time':t,'key':key}



def create_encrypted_zip(file_to_zip, output_zip, password):
    with pyzipper.AESZipFile(output_zip, 'w', compression=pyzipper.ZIP_LZMA, encryption=pyzipper.WZ_AES) as zf:
        zf.setpassword(bytes(password,'utf8')) 
        zf.write(file_to_zip, arcname=os.path.basename(file_to_zip))
        zf.setencryption(pyzipper.WZ_AES, pwd=password.encode())
    

@login_required
def process_ajax(request,what,action=None):
    # 2024-05-15 一次写入超过200万条记录会导致内存溢出
    nMax=100000 # 一次写入或读取的最大行数


    # 2023-10-27目前 瓶盖码数据接口放在66服务器上
    inter_url='http://www.china315net.com:35088'
    
    sub='bottlecap'
    data={'e':'','data':[],'cnt':0} 
    cur=connection.cursor()
    if what=='inform':
        cnt=0
        e=[]
        cids=[]
        excs=[]
        if action=='list':
            # 列表显示盖码业务通知单信息
            data=[]            
            s="SELECT id,case when can_download then '是' else '否' end,case when is_delete then '是' else '否' end,"+\
                "case when downloaded then '是' else '否' end,product,inform_id,inform_code,segment,sn_fr,sn_to,qty,"+\
                "to_char(tm_inform,'yyyy-mm-dd hh24:mi:ss'),"+\
                "(select name from bottle_codetype where id=codetype_id),"+\
                "(select name from company_company where id=company_id),"+\
                "(select name from company_company where id=factory_id) "+\
                "FROM logistics.bottlecap_inform where is_delete=false"
            cur.execute(s)
            for r in cur.fetchall():
                data.append({
                    "id":r[0],"can_download":r[1],"is_delete":r[2],"downloaded":r[3],"product":r[4],"inform_id":r[5],"inform_code":r[6],
                    "segment":r[7],"sn_fr":r[8],"sn_to":r[9],"qty":r[10],"tm_inform":r[11],"codetype":r[12],"company":r[13],"factory":r[14]    
                })           
            
            return render(request,'bottlecap/inform_list.html',data)
        elif action=='sync':
            e=[]
            ee=[]             

            # 已同步过的业务通知单
            eids=[]
            #cids=[]

            # 已同步过的业务通知单
            for i in Inform.objects.all():
                eids.append(str(i.inform_id))
            
            iks=inernal_key()
            url=inter_url+"/"+sub+"/"+what+"/list/"
            d={'time':iks['time'],'key':iks['key'],'exclude_ids':eids}            
            r=requests.post(url=url,json=d) 

            

            if r.status_code==200:
                dd=json.loads(r.text)
                if dd['e']:
                    if dd['e'] not in e:
                        e.append(dd['e'])
                else:                    
                    for d in dd['data']:                       

                        p=d['product']
                        # 2024-05-16
                        cd=d['code'].upper()

                        t='未知'
                        if '内' in p   or 'PNM' in cd:
                            # 加密h码
                            t=1
                        elif "外" in p or 'PWM' in cd:
                            # 16进制码
                            t=2
                        elif "营销码" in p:                        
                            # 营销码些情况下会印在瓶盖外,但码值为加密h码
                            t=6
                        elif "过渡" in p:
                            t=7
                        else:
                            t=2
                        

                        # 业务通知单 客户id
                        cid=str(d['customer_id'])                        
                        # 2024-05-15
                        fid=d['bottlecapfcty_id']    
                         
                        # 2024-11-07 一个业务单会有多个企业加工，启用factories字段
                        fcts=[]
                        if 'bottlecapfcty_ids' in d:
                            s="select id,name from company_company where id in ("+d['bottlecapfcty_ids']+")"  
                        else:
                            s="select id,name from company_company where id="+str(fid)
                        cur.execute(s)
                        for r in cur.fetchall():
                            fcts.append({
                                "id":str(r[0]),
                                "name":r[1]
                            })  
                       
                        # 2024-05-15
                        s="select company_id from company_internalinfo where erp_customer_id="+cid                        
                        cur.execute(s)
                        rc=cur.fetchone()
                        if rc:
                            vData={
                                'company_id':rc[0],
                                'factory_id':fid,
                                'codetype_id':t,
                                'product':p,
                                'inform_id':d['id'],
                                'inform_code':d['code'],
                                'segment':d['data_code'],
                                'sn_fr':d['sn_fr'],
                                'sn_to':d['sn_to'],
                                'qty':d['qty'],
                                'dt_inform':d['tm'][:10],
                                'tm_inform':d['tm'],
                                
                                # 2024-11-07 一个业务单会有多个企业加工，启用factories字段
                                'factories':fcts
                            }
                            fs=Inform.objects.filter(inform_id=int(d['id']))
                            if fs:
                                o=fs[0]
                            else:
                                o=Inform()
                            for k,v in vData.items():
                                setattr(o,k,v)
                            o.save()
                            cnt=cnt+1
                        else:
                            s="单号:%s id:%i" % (d['code'],d['id'])
                            ee.append(s)
            else:
                if "内网取数据失败"  not in e:
                    e.append(rr[1]+" 内网取数据失败") 
            if e:
                data['e']=",".join(e)
            else:
                if ee:
                    data['e']="以下业务单同步失败:"+" ; ".join(ee)
            data['cnt']=cnt    
           

    elif what=='codes':
        if action=='sync':
            e=[]
            cnt=0    
            id=request.GET.get('inform_id','')
            if not id:
                data['e']='inform id missing'
                cur.close()
                return JsonResponse(data)
            objs=Inform.objects.filter(inform_id=int(id))
            if not objs:
                data['e']='inform id error'
                cur.close()
                return JsonResponse(data)
            o=objs[0]   
            if id:
                # 2024-03-05 根据号段分表
                rg=o.segment
                qty_tt=o.qty
                if rg:
                    # 号段必为长度4的数字
                    if len(rg)!=4 or not rg.isdigit():
                        data['e']='数据错误，业务通知单号段应为长度等于4的数字'
                        return JsonResponse(data)
                else:
                    data['e']='数据错误，业务通知单必需有号段信息'
                    return JsonResponse(data)
                
                cid=o.codetype_id 
                url=inter_url+'/'+sub+'/codes/download/'   
                iks=inernal_key()
                params={'inform_id':id,'ctype_id':cid,'time':iks['time'],'key':iks['key']}    
                r=requests.get(url,params=params)   
                if r.status_code==200:
                    dd=json.loads(r.text)  
                    if dd['e']:
                        if dd['e'] not in e:
                            e.append(dd['e'])
                    else:
                        ddd=dd['data']                        
                        tb=''
                        data['cnt_download']=len(ddd)
                        if cid in (1,6):
                            # 盖内码，营销码                            
                            tb_p="cap" 
                            tb="c"+rg
                            """
                            检查号段表是否存在，不存在就创建
                            """    
                            s="select count(*) from information_schema.tables where table_schema='bottlecap' "+\
                                "and table_type='BASE TABLE' and table_name='"+tb+"'"
                            cur=connection.cursor()
                            cur.execute(s)
                            r=cur.fetchone()
                            if not r[0]:
                                s="CREATE TABLE bottlecap."+tb+"("+\
                                        "CONSTRAINT "+tb+"_id_pk PRIMARY KEY(id),"+\
                                        "CONSTRAINT "+tb+"_code_unique UNIQUE(code),"\
                                        "CONSTRAINT "+tb+"_code_check CHECK(\"left\"(code::text,4)='"+tb[-4:]+"'::text)"+\
                                    ") INHERITS (bottlecap."+tb_p+");"+\
                                    "ALTER TABLE bottlecap."+tb+" OWNER TO acc;"+\
                                    "CREATE INDEX "+tb+"_code_idx    ON bottlecap."+tb+" USING btree(code COLLATE pg_catalog.\"default\");"+\
                                    "CREATE INDEX "+tb+"_label_idx   ON bottlecap."+tb+" USING btree(label);"+\
                                    "CREATE INDEX "+tb+"_inform_idx  ON bottlecap."+tb+" USING btree(inform_id);"+\
                                    "CREATE INDEX "+tb+"_dt_idx      ON bottlecap."+tb+" USING btree(dt);"
                                cur.execute(s)
                        else:
                            # 盖外码，过渡码                            
                            tb_p="outter" 
                            tb="o"+rg
                            """
                            检查号段表是否存在，不存在就创建
                            """    
                            s="select count(*) from information_schema.tables where table_schema='bottlecap' "+\
                                "and table_type='BASE TABLE' and table_name='"+tb+"'"
                            cur=connection.cursor()
                            cur.execute(s)
                            r=cur.fetchone()
                            if not r[0]:
                                s="CREATE TABLE bottlecap."+tb+"("+\
                                        "CONSTRAINT "+tb+"_id_pk PRIMARY KEY(id),"+\
                                        "CONSTRAINT "+tb+"_code_unique UNIQUE(code),"\
                                        "CONSTRAINT "+tb+"_code_check CHECK(\"left\"(code::text,4)='"+tb[-4:]+"'::text)"+\
                                    ") INHERITS (bottlecap."+tb_p+");"+\
                                    "ALTER TABLE bottlecap."+tb+" OWNER TO acc;"+\
                                    "CREATE INDEX "+tb+"_code_idx    ON bottlecap."+tb+" USING btree(code COLLATE pg_catalog.\"default\");"+\
                                    "CREATE INDEX "+tb+"_label_idx   ON bottlecap."+tb+" USING btree(label);"+\
                                    "CREATE INDEX "+tb+"_inform_idx  ON bottlecap."+tb+" USING btree(inform_id);"+\
                                    "CREATE INDEX "+tb+"_dt_idx      ON bottlecap."+tb+" USING btree(dt);"
                                cur.execute(s)

                        a=[]    
                        sIns="insert into bottlecap."+tb+"(inform_id,label,code,tm) "
                        
                        # 2024-05-15 一次写入超200万条记录会导致内存溢出
                        nMax=500000
                        i=0

                        # 码数据量大，各种判断要在for循环外面做
                        if cid in (1,6):  
                            # 内码为加密码
                            for d in dd['data']: 
                                c=decrypt_11h(d)                                        
                                a.append("("+id+",'"+d+"','"+c+"',now())")

                                # 2024-05-15
                                i=i+1
                                if i==nMax:
                                    s=sIns+" values "+",".join(a)+" on conflict(code) do nothing"   
                                    cur.execute(s)
                                    cnt=cnt+cur.rowcount
                                    i=0
                                    a=[]


                        elif cid==2:
                            # 外码为16进制
                            for d in dd['data']: 
                                c=str(int(d, 16))
                                a.append("("+id+",'"+d+"','"+c+"',now())")

                                # 2024-05-15
                                i=i+1
                                if i==nMax:
                                    s=sIns+" values "+",".join(a)+" on conflict(code) do nothing"   
                                    cur.execute(s)
                                    cnt=cnt+cur.rowcount
                                    i=0
                                    a=[]


                        elif cid==7:
                            # 过渡码外码为12位数字,盖内码为外码加密得到,内外码无需绑定
                            for d in dd['data']:                                            
                                a.append("("+id+",'"+d+"','"+d+"',now())")

                                # 2024-05-15
                                i=i+1
                                if i==nMax:
                                    s=sIns+" values "+",".join(a)+" on conflict(code) do nothing"   
                                    cur.execute(s)
                                    cnt=cnt+cur.rowcount
                                    i=0
                                    a=[]
                        
                        
                        if a:
                            s=sIns+" values "+",".join(a)+" on conflict(code) do nothing"   
                            cur.execute(s)
                            cnt=cnt+cur.rowcount  


                    # 2024-03-06 写入业务通知单码数据同步日志
                    s="INSERT INTO bottlecap_synclog(inform_id,qty_tt,qty_sync) "+\
                        "select "+id+","+str(qty_tt)+","+str(cnt)
                    cur.execute(s)

                else:
                    if "内网取数据失败" not in e:
                        e.append("内网取数据失败")     
        
        if e:
            data['e']=",".join(e)
        data['cnt']=cnt


    # 2023-11-01 码数据zip文件处理
    elif what=='zip':
        data={'e':'', 'data':{}}  
        if action=='generate':
            # 生成zip文件以供瓶盖厂下载
            id=request.GET.get('inform_id','')
            # 2024-02-22 是否需要生成两列的txt
            clmns=request.GET.get('clmns','1')
            if not id:
                data['e']='inform id missing'
                cur.close()
                return JsonResponse(data)
            s="select factory_id,(select name from bottlecap_codetype where id=codetype_id),"+\
                "dt_inform,qty,codetype_id,segment from bottlecap_inform where inform_id="+id

            # 2024-08-19
            s="select factory_id,(select name from bottlecap_codetype where id=codetype_id),"+\
                "dt_inform,qty,codetype_id,segment,"+\
                "(select name from company_company where id=company_id) "+\
                "from bottlecap_inform where inform_id="+id    
            cur.execute(s)
            r=cur.fetchone()
            if not r:
                data['e']='inform not exists'
                cur.close()
                return JsonResponse(data)            
            # 码类型 盖内码/ 盖外码
            
            #cid=r[0]
            # 2024-08-19            
            fid=r[0]
            cname=r[6]

            ct=r[1]
            dt=r[2]
            qty=r[3]   
            codetype_id=r[4]

            # 2024-03-05
            rg=r[5]

            fZip=format_zfile(
                opt='download',
                fcty_id=fid,
                code_type=ct,
                dt=dt,  
                inform_id=id,
                qty=qty,

                # 2024-08-22
                company_name=cname
            )

            if os.path.exists(fZip):
                data['e']='zip文件已经存在 '+fZip
                cur.close()
                return JsonResponse(data)  
            if codetype_id in [1,6]:                
                # 盖内码，营销码
                #tb='bottlecap.cap'
                tb='bottlecap.c'+rg
            elif codetype_id in [2,7]:
                # 盖外码，过渡码
                #tb="bottlecap.outter"
                tb="bottlecap.o"+rg
            else:
                data['e']="码类型错误"
                cur.close()
                return JsonResponse(data)

            # 天鉴标签前缀
            sUrl="http://tj1.me/?t="
            p = TemporaryDirectory()
            f=open(p.name+"\\"+format_name(ct,dt,id,qty)+".txt","w")
            s="select label from "+tb+" where inform_id="+id  
            cur.execute(s)
            cnt=0        
            # 2024-02-22 是否需要生成两列格式的txt文件 数据量大，判断要在循环外进行            
            if str(clmns)=='2':                
                # 形如 http://tj1.me/?t=1D08F623B4,1D08F623B4               
                rs=cur.fetchmany(nMax)
                while rs:
                    for r in rs:                    
                        f.write(sUrl+r[0]+","+r[0]+"\n")
                        cnt=cnt+1
                    rs=cur.fetchmany(nMax)
            else: 
                rs=cur.fetchmany(nMax)
                while rs:
                    for r in rs:
                        f.write(sUrl+r[0]+"\n")
                        cnt=cnt+1
                    rs=cur.fetchmany(nMax)
            f.close()
            
            # zip文件路径是否存在
            pp=os.path.dirname(fZip)            
            if not os.path.exists(pp):
                # 创建zip文件路径
                os.makedirs(pp)

            # zip文件密码
            #pwd="tjzip"+str(cid)
            # 2024-08-19
            pwd="tjzip"+str(fid)

            '''
            # 调用winrar生成加密zip文件 (不可靠，改用pyzipper)
            cmd = '%s a -p%s -ep1 %s %s' % (pRAR, pwd, fZip, f.name)
            os.system(cmd)
            '''
            # 2023-11-11 使用pyzipper。 浏览器端调用winrar.exe有问题
            create_encrypted_zip(f.name, fZip, pwd)

            os.remove(f.name)
            data['cnt']=cnt
            data['password']=pwd
        elif action=='list':
            # 返回指定企业的瓶盖码数据文件可下载的数据信息
            data={'e':'','data':[]}
            cid=request.GET.get('company_id','')
            fid=str(request.user.company_id)
            if not cid:
                data['e']='company id missing'
                return JsonReponse(data)
            # 检查当前company_id 是否属于当前瓶盖厂            
            cur=connection.cursor()
            s="select "+cid+" in (SELECT "+\
                    "(jsonb_array_elements(customers)->>'id')::int FROM bottlecap_factory where company_id="+fid+\
                ")"                
            cur.execute(s)
            r=cur.fetchone()
            if not r[0]:
                data['e']='没有当前企业的瓶盖加工业务'
                cur.close()
                return JsonResponse(data)
            
            # 2024-03-05 分表后的查询
            s="SELECT i.inform_id,product,qty,tm_inform,dt_inform,"+\
                "(select name from bottlecap_codetype where id=codetype_id),"+\
                "(select name from company_company where id=company_id),"+\
                "case when codetype_id=2 then (select sum(outter_qty_ok) from bottlecap_uploadlog where outter_inform_id=inform_id) "+\
                    "else (select sum(cap_qty_ok) from bottlecap_uploadlog where cap_inform_id=inform_id) "+\
                "end cnt,"+\
                "coalesce((select to_char(max(tm),'yyyy-mm-dd hh24:mi') from bottlecap_downloadlog where inform_id=i.inform_id),'') "+\
                "FROM "+\
                "(select inform_id,product,codetype_id,qty,tm_inform,dt_inform,company_id from bottlecap_inform where "+\
                    "company_id="+cid+" and is_delete=false and can_download=true"+\
                ") i "
            # 2024-08-19
            s="SELECT i.inform_id,product,qty,tm_inform,dt_inform,"+\
                "(select name from bottlecap_codetype where id=codetype_id),"+\
                "(select name from company_company where id=company_id),"+\
                "case when codetype_id=2 then (select sum(outter_qty_ok) from bottlecap_uploadlog where outter_inform_id=inform_id) "+\
                    "else (select sum(cap_qty_ok) from bottlecap_uploadlog where cap_inform_id=inform_id) "+\
                "end cnt,"+\
                "coalesce((select to_char(max(tm),'yyyy-mm-dd hh24:mi') from bottlecap_downloadlog where inform_id=i.inform_id),'') "+\
                ",factory_id FROM "+\
                "(select inform_id,product,codetype_id,qty,tm_inform,dt_inform,company_id,factory_id from bottlecap_inform where "+\
                    "company_id="+cid+" and is_delete=false and can_download=true"+\
                ") i "
        
            cur.execute(s)
            for r in cur.fetchall():
                fZip=format_zfile(
                    opt='download',
                    inform_id=r[0],
                    qty=r[2],
                    dt=r[4], 
                    code_type=r[5],

                    #company_id=fid
                    # 2024-08-19
                    fcty_id=r[9],
                    company_name=r[6]
                )
                zn=''
                sz=''
                if os.path.exists(fZip):
                    sz=str(math.ceil(os.path.getsize(fZip)/1024.0))+'KB'
                    zn=os.path.basename(fZip)
                data['data'].append({
                    'inform_id':r[0],            
                    'product':r[1],  
                    'codetype':r[5],
                    'qty':r[2],                                
                    'zipfile':zn,
                    'tm_inform':r[3].strftime('%Y-%m-%d %H:%M'),                    
                    'company':r[6],                 
                    'dt_download':r[8],            
                    'qty_upload':r[7],   
                    'sz':sz
                })  
            cur.close()
            return JsonResponse(data)
        elif action=='uploadlog':
            # 2024-04-19
            can_download=False

            # 2024-08-02 默认查询一周内的上传记录
            now=datetime.datetime.now()
            dt1= (now - datetime.timedelta(days=7)).strftime('%Y-%m-%d')
            dt2=now.strftime('%Y-%m-%d') 
            dt_fr=request.GET.get('dt_fr',dt1)
            dt_to=request.GET.get('dt_to',dt2) 
            sWhere=" where (dt between '"+dt_fr+"' and '"+dt_to+"') "            
            if request.user.id not in [3506]:   # 3506;"sys_admin"
                sWhere=sWhere+" and factory_id="+str(request.user.company_id)
                    
            # 2024-04-19
            else:
                can_download=True  

            # 2024-08-06
            s="SELECT id,to_char(tm,'yyyy-mm-dd hh24:mi'),file_name,to_char(file_size,'90.009'),"+\
                "lines,cnt,(select username from company_user where id=user_id),"+\
                "(select name from company_company where id=factory_id),coalesce(remark,''),factory_id,"+\
                "(select sum(qty) from bottlecap_uploadresult where batch_id=b.id and code_type_id=1),"+\
                "(select sum(qty) from bottlecap_uploadresult where batch_id=b.id and code_type_id=2) "+\
                "FROM bottlecap.fcty_binding_batch b "+sWhere+" order by dt"  

            cur.execute(s)
            d=[]
            for r in cur.fetchall():
                
                # 2024-04-18
                if r[5]==r[4]:
                    cnt=r[5]
                else:
                    cnt='<span class="r">'+str(r[5])+'</span>'

                # 2024-06-06
                if can_download:      
                    # 2024-06-08 文件是否存在
                    cid=str(r[9])
                    dt=r[1][:10]
                    f=r[2]
                    fZip=os.path.join("F:\\hzy\\log\\zip\\upload",cid,dt,f)                     
                    if os.path.exists(fZip):   
                        fn='<a style="cursor:pointer;" cid='+cid+' dt="'+dt+'">'+f+'</a>'
                    else:
                        fn='<span style="color:gray;font-style:italic">'+f+'</span>'
                else:
                    fn=r[2]               
                

                d.append({
                    "batch_id":r[0],                    
                    "tm":r[1],                    
                    #"file_name":r[2],
                    "file_name":fn,                    
                    "file_size":r[3],
                    "lines":r[4],  
                    "cnt":cnt,  #2024-04-18
                    "ok_cap":r[10],
                    "ok_outter":r[11],
                    "user":r[6],                     
                    "factory":r[7],
                    "remark":r[8]
                    
                })
            cur.close()
            data['data']=d
            
            # 2024-08-02
            data['dt_fr']=dt_fr
            data['dt_to']=dt_to

            return JsonResponse(data)

        elif action=='download':
            # 瓶盖厂下载指定zip文件
            id=request.GET.get('inform_id','')
            if not id:
                data['e']='infrom id missing'
                return JsonResponse(data)
            # 为瓶盖厂设定一个用户帐号
            uid=str(request.user.id)
            
            #cid=str(request.user.company_id)
            #s="select company_id,(select name from bottlecap_codetype where id=codetype_id),dt_inform,qty "+\
            #    "from bottlecap_inform where inform_id="+id+" and factory_id="+cid  
            # 2024-08-19  3506;"sys_admin"
            if uid=='3506':
                sAnd=''
            else:
                sAnd=" and factory_id="+str(rquest.user.company_id)
            s="select company_id,(select name from bottlecap_codetype where id=codetype_id),dt_inform,qty "+\
                ",(select name from company_company where id=company_id) "+\
                ",factory_id from bottlecap_inform where inform_id="+id+sAnd 
            
            cur.execute(s)
            r=cur.fetchone()
            if not r:
                data['e']='没有当前帐号可供下载的数据'
                cur.close()
                return JsonResponse(data)            
            # 码类型 盖内码/ 盖外码
            #cid=r[0]
            
            # 2024-08-19
            fid=str(r[5])

            ct=r[1]
            dt=r[2]
            qty=r[3]     

            # 2024-08-19
            cname=r[4]

            fZip=format_zfile(
                opt='download',

                # 2024-08-119
                #company_id=cid,
                fcty_id=fid,

                code_type=ct,
                dt=dt,  
                inform_id=id,
                qty=qty,

                # 2024-08-19
                company_name=cname
            )
            fn=os.path.basename(fZip)
            sz=os.path.getsize(fZip)  
            if os.path.exists(fZip):  
                file_stream = open(fZip,'rb')
                # 以文件流将zip传给盖瓶厂用户                
                response = FileResponse(file_stream)                
                response['Content-Type'] = 'application/octet-stream'
                response['Content-Length']=sz                
                response['Accept-Ranges'] = 'bytes'
                response['Content-Disposition']='attachment;filename="{0}"'.format(
                    escape_uri_path(fn.split('/')[-1])
                )               
                # 写入下载日志
                #s="INSERT INTO bottlecap_downloadlog(inform_id,file_name,user_id,factory_id,file_size) values "+\
                #    "("+id+",'"+fn+"',"+uid+","+cid+","+str(float(sz/(1024.0*1024)))+")"

                # 2024-08-19
                s="INSERT INTO bottlecap_downloadlog(inform_id,file_name,user_id,factory_id,file_size) values "+\
                    "("+id+",'"+fn+"',"+uid+","+str(fid)+","+str(float(sz/(1024.0*1024)))+")"
                
                cur.execute(s)  
                cur.close()
                return response    
            else:
                data['e']  
        # 2024-04-19
        elif action=='downloadfile':     
            d={'e':'','f':''}
            # 下载指定文件            
            #pZip='F:\\hzy\\log\\zip'
            p=pZip+"\\upload"
            sub=request.GET.get('sub','')
            fn= request.GET.get('fn','')
            e=''
            if not fn:
                e='文件名不能为空'
            # 文件是否存在
            fZip=os.path.join(p,sub,fn)            
            d['f']=fZip
            if os.path.exists(fZip):                 
                '''
                fn=os.path.basename(fZip)
                sz=os.path.getsize(fZip)
                file_stream = open(fZip,'rb')
                # 以文件流将zip传给前端                
                response = FileResponse(file_stream)                
                response['Content-Type'] = 'application/octet-stream'
                response['Content-Length']=sz                
                response['Accept-Ranges'] = 'bytes'
                response['Content-Disposition']='attachment;filename="{0}"'.format(
                    escape_uri_path(fn.split('/')[-1])
                )               
                return response
                '''

                # 2024-06-15
                file_path=fZip
                def file_iterator(file_path):
                    with open(file_path, 'rb') as f:
                        while True:
                            chunk = f.read(1024)
                            if not chunk:
                                break
                            yield chunk
                file_name=fn

                response = StreamingHttpResponse(file_iterator(file_path), content_type='application/octet-stream')
                response['Content-Disposition'] = 'attachment; filename="{}"'.format(escape_uri_path(fn.split('/')[-1]))
                return response


            else:
                d['e']='文件不存在'
                return JsonResponse(d)

        elif action=='upload':
            # 瓶盖厂上传瓶盖码zip文件      
            uid=str(request.user.id)
            cid=str(request.user.company_id)  
            cd=request.POST.get('decode','gbk')
            pwd=request.POST.get('password','1')  

            
            # 2024-09-21 上传的是否为两个盖外码对应关系 (双面双码瓶盖内小圆标，如华山论剑)
            twincode=request.POST.get('is_twincode','0')
            if twincode=='1':
                is_twincode=True
            else:
                is_twincode=False

            # 2024-08-16 客户ID(酒厂企业id)
            cust_id=request.POST.get('customer_id','')
            if not cust_id:
                cust_id='null'
            
            result=[]
            tt_ln=0             #zip文件总行数
            tt_cnt=0            #zip文件数据总数            
            tt_ok_cap=0         #内码实际总写入绑定表总数
            tt_ok_outter=0      #外码实际总写入绑定表总数
            # 2024-10-21 一次写入超120万条记录会导致内存溢出
            nMax=100000
           
            files = request.FILES.getlist('files')                         
            if files:
                f=files[0]    
                fn = f.name
                tp = fn.split('.')[-1].lower()
                if tp == 'zip':
                    dt=datetime.datetime.now().strftime('%Y-%m-%d')
                    p=pZip+"\\upload\\"+cid+"\\"+dt                     
                    if not os.path.exists(p):
                        # 创建zip文件路径
                        os.makedirs(p)
                    zf=p + '\\' + fn  
                    with open(zf, 'wb+') as destination:
                        for chunk in f.chunks():
                            destination.write(chunk)                       

                    # 2023-11-11 检查文件是否为zip压缩文件
                    tf = open(zf, "rb")
                    ct=tf.read(4).hex()
                    tf.close()
                    if ct!='504b0304':                        
                        cur.close()
                        data['e']='您上传的不是zip格式的压缩文件！'
                        return JsonResponse(data)
                    
                    sz=os.path.getsize(zf)  
                    
                    # 写入binding_batch
                    #s="INSERT INTO bottlecap.fcty_binding_batch("+\
                    #    "factory_id,user_id,pwd,file_name,file_size) VALUES ("+\
                    #        cid+","+uid+",'"+pwd+"','"+fn+"',"+str(float(sz/(1024.0*1024)))+\
                    #    ") returning id"
                    # 2024-08-16
                    s="INSERT INTO bottlecap.fcty_binding_batch("+\
                        "factory_id,user_id,pwd,file_name,file_size,company_id) VALUES ("+\
                            cid+","+uid+",'"+pwd+"','"+fn+"',"+str(float(sz/(1024.0*1024)))+","+cust_id+\
                        ") returning id"
                    
                    cur.execute(s)
                    r=cur.fetchone()                    
                    bid=str(r[0]) 
                    # 2024-06-21 创建临时表，用于原样保存TXT数据                   
                    cur.execute("create temp table t_codes("+\
                            "id serial PRIMARY KEY,"+\
                            "f_sn smallint,"+\
                            "f_ln int,"+\
                            "cap0    character varying(20),"+\
                            "outter0 character varying(20),"+\
                            "cap    character varying(20),"+\
                            "decap  character varying(20),"+\
                            "outter character varying(20),"+\
                            "deoutter  character varying(20)"+\
                        ");"+\
                        "CREATE INDEX decap_idx             on t_codes(decap);"+\
                        "CREATE INDEX deoutter_idx          on t_codes(deoutter);"+\
                        "CREATE INDEX f_sn_idx              on t_codes(f_sn);"+\
                        "CREATE INDEX f_ln_idx              on t_codes(f_ln);"
                    )                    
                    # 创建临时表，用于去重后的数据（内码唯一，外码唯一）                    
                    cur.execute("create temp table tt_codes("+\
                            "id serial PRIMARY KEY,"+\
                            "id_src int,"+\
                            "f_sn smallint,"+\
                            "f_ln int,"+\
                            "cap    character varying(20) UNIQUE,"+\
                            "rg_cap character varying(4),"+\
                            "decap  character varying(20) ,"+\
                            "cap_inform_id int DEFAULT 0,"+\
                            "outter  character varying(20) UNIQUE,"+\
                            "rg_outter character varying(4),"+\
                            "deoutter character varying(20),"+\
                            "outter_inform_id int DEFAULT 0,"+\
                            "batch_id_cap0 int,"+\
                            "batch_id_outter0 int,"+\
                            "cap0    character varying(20),"+\
                            "outter0 character varying(20),"+\
                            "tm_cap0     timestamp with time zone,"+\
                            "tm_outter0  timestamp with time zone"+\
                        ");"+\
                        "CREATE INDEX id_src_idx            on tt_codes(id_src);"+\
                        "CREATE INDEX cap_idx               on tt_codes(cap);"+\
                        "CREATE INDEX outter_idx            on tt_codes(outter);"+\
                        "CREATE INDEX rg_cap_idx            on tt_codes(rg_cap);"+\
                        "CREATE INDEX rg_outter_idx         on tt_codes(rg_outter);"+\
                        "CREATE INDEX outter_inform_idx     on tt_codes(outter_inform_id);"+\
                        "CREATE INDEX cap0_idx              on tt_codes(cap0);"+\
                        "CREATE INDEX outter0_idx           on tt_codes(outter0);"+\
                        "CREATE INDEX cap_inform_idx        on tt_codes(cap_inform_id);"                       
                    )
                    sIns="insert into t_codes(f_sn,f_ln,cap,decap,outter,deoutter) values "   

                    # 2024-09-21 
                    if is_twincode:
                        tb_twin='tt_twincode'
                        #create temp table tt_twincode("+\
                        cur.execute("create temp table tt_twincode("+\
                                "id serial PRIMARY KEY,"+\
                                "wm1    character varying(20),"+\
                                "wm2    character varying(20),"+\
                                "de_wm1    character varying(20),"+\
                                "de_wm2    character varying(20),"+\
                                "cap1   character varying(20),"+\
                                "cap2   character varying(20),"+\
                                "de_cap1   character varying(20),"+\
                                "de_cap2   character varying(20)"+\
                            ");"+\
                            "CREATE INDEX wm1_idx   on "+tb_twin+"(wm1);"+\
                            "CREATE INDEX wm2_idx   on "+tb_twin+"(wm2);"
                        )
                        sIns="insert into "+tb_twin+"(wm1,wm2,de_wm1,de_wm2) values " 
                    
                    with pyzipper.AESZipFile(zf, mode="r") as archive:

                        f_dict={} #txt文件可能为很长，为节省空间，生成 序号-文件名 字典
                        f_sn=1

                        archive.setpassword(bytes(pwd,"utf8"))
                        for f in archive.namelist():                            
                            try:
                                tfn=f.encode('cp437').decode('gbk',"ignore")
                            except Exception:
                                tfn=f

                            if tfn=='/':
                                # 可能把txt压缩在一个文件夹内?
                                #print(tfn)
                                continue                         
                            
                            f_dict[f_sn]=tfn
                            ln=0    #当前txt行数   
                            
                            aa=[]
                            err=''   
                            # 2024-09-21 
                            if is_twincode:    
                                for l in archive.read(f).split(b"\n"): 
                                    l=str(l.decode(cd)).strip().replace("，",",")                                   
                                    if l:
                                        # 2024-04-15 为方便上传方核对数据，当前行数和总行数去除空白行 
                                        ln=ln+1 
                                        tt_ln=tt_ln+1 # 总行数
                                        a=l.split(",")  
                                        if len(a)>1:  
                                            # txt文件可能会有表头,只简单判断第1列字符串长度  "内码","外码","批号","所在箱数","日期"
                                            if len(a[0])>9:  
                                                # 2024-06-05 内、外码只能在前两列
                                                # 不同瓶盖厂内外码位位置不同；同一个文件内外码位置也可能不同
                                                wm1=''
                                                wm2=''
                                                dwm1=''
                                                dwm2=''
                                                l1=a[0] # 第1列                                                
                                                l2=a[1] # 第2列                                               
                                                if "=" in l1:
                                                    l1=l1.split("=")[1]
                                                if "=" in l2:
                                                    l2=l2.split("=")[1]
                                                if len(l1)==10:
                                                    # 'BF5211B146'
                                                    wm1=l1
                                                    dwm1=str(int(wm1,16))
                                                if len(l2)==10:
                                                    # 'BF5211B146'
                                                    wm2=l2
                                                    dwm2=str(int(wm2,16))
                                                if wm1 and wm2:
                                                    aa.append("('"+wm1+"','"+wm2+"','"+dwm1+"','"+dwm2+"')")
                                                    tt_cnt=tt_cnt+1 # 数据总数
                                                    # 2024-10-21
                                                    if len(aa)==nMax:
                                                        s=sIns+",".join(aa)
                                                        cur.execute(s) 
                                                        aa=[]

                                        else:
                                            err='绑定数据的txt至少2列'
                                            break 
                            else:
                                for l in archive.read(f).split(b"\n"):    
                                    l=str(l.decode(cd)).strip().replace("，",",")                                   
                                    if l:                                        
                                        # 2024-04-15 为方便上传方核对数据，当前行数和总行数去除空白行 
                                        ln=ln+1 
                                        tt_ln=tt_ln+1 # 总行数

                                        a=l.split(",")                                           
                                        if len(a)>1:  
                                            # txt文件可能会有表头,只简单判断第1列字符串长度  "内码","外码","批号","所在箱数","日期"
                                            if len(a[0])>9:   
                                                # 2024-06-05 内、外码只能在前两列
                                                # 不同瓶盖厂内外码位位置不同；同一个文件内外码位置也可能不同
                                                nm=''
                                                wm=''                                                
                                                l1=a[0] # 第1列                                                
                                                l2=a[1] # 第2列                                               
                                                if "=" in l1:
                                                    l1=l1.split("=")[1]
                                                if "=" in l2:
                                                    l2=l2.split("=")[1]

                                                # 判断第1列
                                                if len(l1)==10:
                                                    # 'BF5211B146'
                                                    wm=l1
                                                elif len(l1)==12:
                                                    # 'h83ffba504bc'
                                                    nm=l1
                                                # 判断第2列
                                                if len(l2)==10:
                                                    # 'BF5211B146'
                                                    wm=l2
                                                elif len(l2)==12:
                                                    # 'h83ffba504bc'
                                                    nm=l2

                                                if nm and wm:   
                                                    dn=decrypt_11h(nm)  #盖内明码
                                                    dw=str(int(wm,16))  #盖外明码  
                                                    aa.append("("+str(f_sn)+","+str(ln)+",'"+nm+"','"+dn+"','"+wm+"','"+dw+"')") 
                                                    tt_cnt=tt_cnt+1 # 数据总数
                                                    # 2024-10-21
                                                    if len(aa)==nMax:
                                                        s=sIns+",".join(aa)
                                                        cur.execute(s) 
                                                        aa=[]

                                        else:
                                            err='绑定数据的txt至少2列'
                                            break 
                            if aa:
                                s=sIns+",".join(aa)
                                cur.execute(s) 

                            # 2024-09-21 
                            if is_twincode:
                                # 将两个外码，转换正常数据格式写入到t_codes     
                                s="update "+tb_twin+" set "+\
                                    "cap1=(select cap from bottlecap.twincode_binding where outter=wm1)"
                                cur.execute(s)                                
                                s="update "+tb_twin+" set "+\
                                    "cap2=(select cap from bottlecap.twincode_binding where outter=wm2)"
                                cur.execute(s)   
                                s="insert into t_codes(f_sn,f_ln,cap,decap,outter,deoutter) select "+\
                                    str(f_sn)+","+str(ln)+","+\
                                    "case when cap1 is not null then cap1 else cap2 end,"+\
                                    "case when cap1 is not null then public.de_promotion(cap1) else public.de_promotion(cap2) end,"+\
                                    "case when cap1 is not null then wm1 else wm2 end,"+\
                                    "case when cap1 is not null then de_wm1 else de_wm2 end "+\
                                    "from "+tb_twin
                                cur.execute(s)
                            
                            f_sn=f_sn+1 #2024-06-21 文件序号加1 


                    # 保存文件名字典
                    s="update bottlecap.fcty_binding_batch set f_dict='"+json.dumps(f_dict)+"' where id="+bid
                    cur.execute(s)

                    # 2024-06-21 将临时表的数据，保留原id写入内外码唯一的临时表
                    s="insert into tt_codes("+\
                            "id_src,f_sn,f_ln,cap,decap,outter,deoutter,rg_cap,rg_outter"+\
                        ") select "+\
                            "id,f_sn,f_ln,cap,decap,outter,deoutter,left(decap,4),left(deoutter,4) from t_codes "+\
                        "on conflict do nothing"                    
                    cur.execute(s)

                    rgs=[                                
                        {'tp':'outter','rg':'rg_outter','tb_i':'o','tb_f':'fo','de':'deoutter','tp_r':'cap'},
                        {'tp':'cap',   'rg':'rg_cap',   'tb_i':'c','tb_f':'fc','de':'decap',   'tp_r':'outter'}
                    ]   # 内外码执行的都是相同操作，这个数组只为减少重复代码                    

                    # 更新临时表 相关数据
                    for d in rgs:
                        #s="select rg_outter from tt_codes group by rg_outter"
                        s="select "+d['rg']+" from tt_codes group by "+d['rg']                        
                        cur.execute(s)
                        rs=cur.fetchall()
                        for r in rs:                     

                            rg=r[0]
                            # 业务通知单ID
                            tb="bottlecap."+d['tb_i']+rg
                            # 号段是否存在
                            s="select count(*) from information_schema.tables where table_schema='bottlecap' "+\
                                "and table_type='BASE TABLE' and table_name='"+d['tb_i']+rg+"'"                          

                            cur.execute(s)
                            rr=cur.fetchone()
                            if rr[0]: 
                                s="update tt_codes set "+d['tp']+"_inform_id=coalesce("+\
                                    "(select inform_id from "+tb+" where code="+d['de']+"),0) "+\
                                    "where "+d['rg']+"='"+rg+"'"                          
                                
                                cur.execute(s)
                            # 码是否上传过
                            tb="bottlecap."+d['tb_f']+rg
                            # 号段是否存在
                            s="select count(*) from information_schema.tables where table_schema='bottlecap' "+\
                                "and table_type='BASE TABLE' and table_name='"+d['tb_f']+rg+"'"                                
                            cur.execute(s)
                            rr=cur.fetchone()
                            if rr[0]:  

                                s="update tt_codes t set "+d['tp_r']+"0=f."+d['tp_r']+",tm_"+d['tp_r']+"0=f.tm,"+\
                                    "batch_id_"+d['tp_r']+"0=f.batch_id "\
                                    "from "+tb+" f where "+d['rg']+"='"+rg+"' and f."+d['tp']+"=t."+d['tp']                            

                                cur.execute(s)                   
                    # et:(0,_('无对应业务通知单')),(1,_('本批次内重复')),(2,_('本批次内不一致')),(10,_('与往批次重复')),(20,_('与往批次不一致')),(100,_('客户归属错误')),   
                    sW=' where not exists(select id from tt_codes where id_src=t.id)'
                    s="select count(*) from t_codes t "+sW
                    cur.execute(s)
                    r3=cur.fetchone()
                    if r3[0]:
                        # 当前ZIP内有重复数据
                        s="update t_codes t set cap0=(select cap from tt_codes where outter=t.outter)"+\
                            " where not exists(select id from tt_codes where id_src=t.id)"
                        cur.execute(s)
                        s="update t_codes t set outter0=(select outter from tt_codes where cap=t.cap)"+\
                            " where not exists(select id from tt_codes where id_src=t.id)"
                        cur.execute(s)                                              
                        et='1' # 本批次重复
                        s="select count(*) from t_codes t "+sW+" and cap=cap0 and outter=outter0"
                        cur.execute(s)
                        r4=cur.fetchone()                        
                        if r4[0]:                  
                            s="INSERT INTO bottlecap_fctyuploaderr(batch_id,qty,e_type,remark) values "+\
                                "("+bid+","+str(r4[0])+","+et+",'本批次重复') returning id"
                            cur.execute(s)
                            r5=cur.fetchone()
                            id=str(r5[0])
                            s="INSERT INTO bottlecap_fctyuploaderrdetails(f_sn,f_ln,fctyuploaderr_id,cap,outter,cap0,outter0,batch_id) "+\
                                "select f_sn_f_ln,"+id+",cap,outter,cap0,outter0,"+bid+" from t_codes "+\
                                sW+" and cap=cap0 and outter=outter0"
                            cur.execute(s)              
                        et='2' # 本批次数据不一致 
                        s="select count(*) from t_codes t "+sW+" and (cap<>cap0 or outter<>outter0)"
                        cur.execute(s)
                        r4=cur.fetchone()                        
                        if r4[0]:                  
                            s="INSERT INTO bottlecap_fctyuploaderr(batch_id,qty,e_type,remark) values "+\
                                "("+bid+","+str(r4[0])+","+et+",'本批次数据不一致') returning id"
                            cur.execute(s)
                            r5=cur.fetchone()
                            id=str(r5[0])
                            s="INSERT INTO bottlecap_fctyuploaderrdetails(f_sn,f_ln,fctyuploaderr_id,cap,outter,cap0,outter0,batch_id) "+\
                                "select f_sn,f_ln,"+id+",cap,outter,cap0,outter0,"+bid+" from t_codes t "+\
                                sW+" and ((cap is not null and cap<>cap0) or (outter0 is not null and outter<>outter0))"
                            cur.execute(s)
                    tb='bottlecap_fctyuploaderrdetails'

                    sFlds='f_ln,f_sn,cap,cap0,tm_cap0,batch_id_cap0,outter,outter0,tm_outter0,batch_id_outter0'
                    
                    et='10' #,_('与往批次重复'))  
                    sW="where cap=cap0 and outter=outter0"
                    s="select count(*) from tt_codes "+sW
                    cur.execute(s)
                    r3=cur.fetchone()
                    if r3[0]:
                        s="INSERT INTO bottlecap_fctyuploaderr(batch_id,qty,e_type,remark) values "+\
                            "("+bid+","+str(r3[0])+","+et+",'与往批次重复') returning id"
                        cur.execute(s)
                        r4=cur.fetchone()
                        id=str(r4[0])
                        s="INSERT INTO "+tb+"(fctyuploaderr_id,"+sFlds+",batch_id) "+\
                            "select "+id+","+sFlds+","+bid+" from tt_codes "+sW
                        cur.execute(s)                    
                    et='20' #(20,_('与往批次不一致'))
                    sW="where (cap0 is not null and cap<>cap0) or (outter0 is null and outter<>outter0)"
                    s="select count(*) from tt_codes "+sW
                    cur.execute(s)
                    r3=cur.fetchone()
                    if r3[0]:
                        s="INSERT INTO bottlecap_fctyuploaderr(batch_id,qty,e_type,remark) values "+\
                            "("+bid+","+str(r3[0])+","+et+",'与往批次不一致') returning id"
                        cur.execute(s)
                        r4=cur.fetchone()
                        id=str(r4[0])
                        s="INSERT INTO "+tb+"(fctyuploaderr_id,"+sFlds+",batch_id) "+\
                            "select "+id+","+sFlds+","+bid+" from tt_codes "+sW
                        cur.execute(s)                        

                    et='0' #(0,_('无对应业务通知单')),
                    sW=" where outter_inform_id=0 or cap_inform_id=0"
                    s="select count(*) from tt_codes "+sW
                    cur.execute(s)
                    r3=cur.fetchone()
                    if r3[0]:
                        s="INSERT INTO bottlecap_fctyuploaderr(batch_id,qty,e_type,remark) values "+\
                            "("+bid+","+str(r3[0])+","+et+",'无对应业务通知单') returning id"
                        cur.execute(s)
                        r4=cur.fetchone()
                        id=str(r4[0])
                        s="INSERT INTO "+tb+"(fctyuploaderr_id,"+sFlds+",batch_id) "+\
                            "select "+id+","+sFlds+","+bid+" from tt_codes "+sW
                        cur.execute(s) 

                    # 2024-08-17
                    et='100' #(100,_('客户归属错误')),    cust_id
                    sW=" where "+\
                        "(outter_inform_id>0 and (select company_id from bottlecap_inform where inform_id=outter_inform_id)<>"+cust_id+")"+\
                        " or "+\
                        "(cap_inform_id>0    and (select company_id from bottlecap_inform where inform_id=cap_inform_id   )<>"+cust_id+")"

                    s="select count(*) from tt_codes "+sW
                    cur.execute(s)
                    r3=cur.fetchone()
                    if r3[0]:
                        s="INSERT INTO bottlecap_fctyuploaderr(batch_id,qty,e_type,remark) values "+\
                            "("+bid+","+str(r3[0])+","+et+",'客户归属错误') returning id"
                        cur.execute(s)
                        r4=cur.fetchone()
                        id=str(r4[0])
                        s="INSERT INTO "+tb+"(fctyuploaderr_id,"+sFlds+",batch_id) "+\
                            "select "+id+","+sFlds+","+bid+" from tt_codes "+sW
                        cur.execute(s)
                    
                    # 2024-07-13 为提高日常查询速度，数据按内码、外码分表写入两次
                    # 2024-07-30 将要写入 bottlecap_uploadresult ra数组 
                    ra=[]
                    for d in rgs:     
                        s="select "+d['rg']+",outter_inform_id,cap_inform_id,count(*) cnt from tt_codes"+\
                            " where cap0 is null and outter0 is null "+\
                            " group by "+d['rg']+",outter_inform_id,cap_inform_id "
                        cur.execute(s)
                        rs=cur.fetchall()                            
                        for rr in rs:                                      
                            tb_p="fcty_binding_details"
                            rg=rr[0]                            
                            tb=d['tb_f']+rg
                            io_id=str(rr[1])
                            ic_id=str(rr[2])     
                            """
                            检查号段表是否存在，不存在就创建
                            """    
                            s="select count(*) from information_schema.tables where table_schema='bottlecap' "+\
                                "and table_type='BASE TABLE' and table_name='"+tb+"'"
                            cur=connection.cursor()
                            cur.execute(s)
                            r=cur.fetchone()                            
                            if not r[0]:                              
                                s="CREATE TABLE bottlecap."+tb+"("+\
                                    "CONSTRAINT "+tb+"_id_pk PRIMARY KEY(id),"+\
                                    "CONSTRAINT "+tb+"_decap_unique    UNIQUE(decap),"\
                                    "CONSTRAINT "+tb+"_deoutter_unique UNIQUE(deoutter),"\
                                    "CONSTRAINT "+tb+"_"+d['de']+"_check  CHECK(\"left\"("+d['de']+"::text,4)='"+tb[-4:]+"'::text)"+\
                                ") INHERITS (bottlecap."+tb_p+");"+\
                                    "CREATE INDEX "+tb+"_cap_idx    ON bottlecap."+tb+" USING btree(cap    COLLATE pg_catalog.\"default\");"+\
                                    "CREATE INDEX "+tb+"_outter_idx ON bottlecap."+tb+" USING btree(outter COLLATE pg_catalog.\"default\");"+\
                                    "CREATE INDEX "+tb+"_outter_inform_idx  ON bottlecap."+tb+" USING btree(outter_inform_id);"+\
                                    "CREATE INDEX "+tb+"_cap_inform_idx     ON bottlecap."+tb+" USING btree(cap_inform_id);"+\
                                    "CREATE INDEX "+tb+"_dt_idx             ON bottlecap."+tb+" USING btree(dt);"
                                cur.execute(s)

                            # 把当前文件数据写入分表                            
                            s="INSERT INTO bottlecap."+tb+"(company_id,batch_id,cap,decap,outter,cap_inform_id,outter_inform_id,deoutter) "+\
                                "select "+cid+","+bid+",cap,decap,outter,cap_inform_id,outter_inform_id,deoutter "+\
                                "from tt_codes where "+d['rg']+"='"+rg+"' and outter_inform_id="+io_id+" and cap_inform_id="+ic_id+\
                                    " and cap0 is null and outter0 is null"+\
                                " on conflict do nothing"
                            cur.execute(s)  
                            cur_cnt=cur.rowcount

                            
                            if d['tp']=='cap':
                                tt_ok_cap=tt_ok_cap+cur_cnt
                                
                                # 2024-07-30
                                ctype='1'   # 盖内码
                                iid=ic_id   # 盖内码所属业务通知单

                            elif d['tp']=='outter':                                
                                tt_ok_outter=tt_ok_outter+cur_cnt

                                # 2024-07-30
                                ctype='2'   # 盖外码
                                iid=io_id   # 盖外码所属业务通知单

                            # 2024-07-30                            
                            ra.append("("+bid+","+iid+",'"+rg+"',"+str(cur_cnt)+","+ctype+")")                            

                    # 2024-07-30 插入上传结果表
                    if ra:
                        s="INSERT into bottlecap_uploadresult(batch_id,inform_id,segment,qty,code_type_id) "+\
                            " values "+",".join(ra)
                        cur.execute(s)                    

                    # 2024-07-20
                    s="update bottlecap.fcty_binding_batch set "+\
                        "lines="+str(tt_ln)+",cnt="+str(tt_cnt)+","+\
                        "ok_cap="+str(tt_ok_cap)+",ok_outter="+str(tt_ok_outter)+","\
                        "f_dict='"+json.dumps(f_dict)+"' "+\
                        "where id="+bid
                    cur.execute(s)  
                    
                    if tt_ln==0:
                        if pwd:
                            ee='请确认密码是否正确'
                        else:
                            ee='请确认压缩格式是否正确,文件是否有损坏'
                        data['e']='处理失败,'+ee
                    
                    # 2024-07-20 未写入数据列表
                    errs=[]
                    cur.execute("SELECT remark,qty,id FROM bottlecap_fctyuploaderr where batch_id="+bid)
                    for r in cur.fetchall():
                        a=r[0]
                        if r[1]<10000:
                            a="<a href='/bottlecap/ajax/zip/errs_details/?errs_id="+str(r[2])+"' >"+str(r[0])+"</a>"
                        errs.append({'e_type':a,'qty':r[1]})   

                    
                    data['data']={
                        'tt_lns':tt_ln, 
                        'tt_cnt':tt_cnt,
                        'tt_ok_cap':tt_ok_cap,
                        'tt_ok_outter':tt_ok_outter,
                        'txt_cnt':len(f_dict),
                        'errs':errs
                    } 
                    
                else:
                    data['e']='请上传经过压缩后的zip文件'
            else:
                data['e']='没有上传任何文件'
              
        elif action=='errs_details':
            # 2024-07-22 返回瓶盖厂数据错误明细
            id=request.GET.get('errs_id','')
            d=[]
            s="select f_ln,cap,outter,cap0,outter0,"+\
                "coalesce(to_char(tm_cap0,'yyyy-mm-dd hh24:mi'),''),"+\
                "coalesce(to_char(tm_outter0,'yyyy-mm-dd hh24:mi'),''),"+\
                "(select f_dict->>text(f_sn) from bottlecap.fcty_binding_batch where id=batch_id) "+\
                "FROM bottlecap_fctyuploaderrdetails where fctyuploaderr_id="+id
            cur.execute(s)
            for r in cur.fetchall():
                d.append({
                    "txt_name":r[7],
                    "txt_line":r[0],
                    "cap":r[1],
                    "outter":r[2],
                    "cap0":r[3],
                    "outter0":r[4],
                    "tm_cap0":r[5],
                    "tm_outter0":r[6]
                })
            clmns=[
                {'title':'TXT文件名','data':'txt_name'},
                {'title':'所在行','data':'txt_line'},
                {'title':'盖内码','data':'cap'},
                {'title':'盖外码','data':'outter'},
                {'title':'已传盖内码','data':'cap0'},
                {'title':'上传时间','data':'tm_cap0'},                
                {'title':'已传盖外码','data':'outter0'},
                {'title':'上传时间','data':'tm_outter0'},                    
            ]
            data={'clmns':clmns,'data':d}  
        
        # 2024-08-01 询查显示近期瓶盖厂上传的ZIP文件
        elif action=='lastes':
            dt2=datetime.datetime.now().strftime()

        # 2024-09-20
        elif action=='twincodeupload':            
            # 瓶盖厂上传瓶盖码zip文件  
            cd=request.POST.get('decode','gbk')
            pwd=request.POST.get('password','1')           
            inform_id=request.POST.get('inform_id')                        
            ea=[]
            tt_ln=0             #zip文件总行数
            tt_cnt=0            #zip文件数据总数            
            tt_ok_cap=0         #内码实际总写入绑定表总数
            tt_ok_outter=0      #外码实际总写入绑定表总数

            nLmt=10000          #每nLmt写入一次
           
            files = request.FILES.getlist('files')                         
            if files:
                f=files[0]    
                fn = f.name
                tp = fn.split('.')[-1].lower()
                if tp == 'zip':
                    dt=datetime.datetime.now().strftime('%Y-%m-%d')
                    p=pZip+"\\upload\\twincode\\"+dt+"\\"+str(inform_id)                    
                    if not os.path.exists(p):
                        # 创建zip文件路径
                        os.makedirs(p)
                    zf=p + '\\' + fn  
                    with open(zf, 'wb+') as destination:
                        for chunk in f.chunks():
                            destination.write(chunk)                       

                    # 2023-11-11 检查文件是否为zip压缩文件
                    tf = open(zf, "rb")
                    ct=tf.read(4).hex()
                    tf.close()
                    if ct!='504b0304':                        
                        cur.close()
                        data['e']='您上传的不是zip格式的压缩文件！'
                        return JsonResponse(data)
                    
                    sz=os.path.getsize(zf)                   
                    
                    # 2024-06-21 创建临时表，用于原样保存TXT数据                   
                    cur.execute("create temp table t_codes("+\
                            "id serial PRIMARY KEY,"+\
                            "f_sn smallint,"+\
                            "f_ln int,"+\
                            "cap0    character varying(20),"+\
                            "outter0 character varying(20),"+\
                            "cap    character varying(20),"+\
                            "decap  character varying(20),"+\
                            "outter character varying(20),"+\
                            "deoutter  character varying(20)"+\
                        ");"+\
                        "CREATE INDEX decap_idx             on t_codes(decap);"+\
                        "CREATE INDEX deoutter_idx          on t_codes(deoutter);"+\
                        "CREATE INDEX f_sn_idx              on t_codes(f_sn);"+\
                        "CREATE INDEX f_ln_idx              on t_codes(f_ln);"
                    )                    
                    # 创建临时表，用于去重后的数据（内码唯一，外码唯一）                    
                    cur.execute("create temp table tt_codes("+\
                            "id serial PRIMARY KEY,"+\
                            "id_src int,"+\
                            "f_sn smallint,"+\
                            "f_ln int,"+\
                            "cap    character varying(20) UNIQUE,"+\
                            "rg_cap character varying(4),"+\
                            "decap  character varying(20) ,"+\
                            "cap_inform_id int DEFAULT 0,"+\
                            "outter  character varying(20) UNIQUE,"+\
                            "rg_outter character varying(4),"+\
                            "deoutter character varying(20),"+\
                            "outter_inform_id int DEFAULT 0,"+\
                            "batch_id_cap0 int,"+\
                            "batch_id_outter0 int,"+\
                            "cap0    character varying(20),"+\
                            "outter0 character varying(20),"+\
                            "tm_cap0     timestamp with time zone,"+\
                            "tm_outter0  timestamp with time zone"+\
                        ");"+\
                        "CREATE INDEX id_src_idx            on tt_codes(id_src);"+\
                        "CREATE INDEX cap_idx               on tt_codes(cap);"+\
                        "CREATE INDEX outter_idx            on tt_codes(outter);"+\
                        "CREATE INDEX rg_cap_idx            on tt_codes(rg_cap);"+\
                        "CREATE INDEX rg_outter_idx         on tt_codes(rg_outter);"+\
                        "CREATE INDEX outter_inform_idx     on tt_codes(outter_inform_id);"+\
                        "CREATE INDEX cap0_idx              on tt_codes(cap0);"+\
                        "CREATE INDEX outter0_idx           on tt_codes(outter0);"+\
                        "CREATE INDEX cap_inform_idx        on tt_codes(cap_inform_id);"                       
                    )
                    sIns="insert into t_codes(f_sn,f_ln,cap,decap,outter,deoutter) values " 
                    
                    with pyzipper.AESZipFile(zf, mode="r") as archive:
                        f_dict={} #txt文件可能为很长，为节省空间，生成 序号-文件名 字典
                        f_sn=1
                        archive.setpassword(bytes(pwd,"utf8"))
                        for f in archive.namelist():                            
                            try:
                                tfn=f.encode('cp437').decode('gbk',"ignore")
                            except Exception:
                                tfn=f

                            if tfn=='/':
                                # 可能把txt压缩在一个文件夹内?
                                #print(tfn)
                                continue                            
                            f_dict[f_sn]=tfn
                            ln=0    #当前txt行数                            
                            aa=[]
                            err=''                    
                            for l in archive.read(f).split(b"\n"):    
                                l=str(l.decode(cd)).strip().replace("，",",")                                   
                                if l:                                        
                                    # 2024-04-15 为方便上传方核对数据，当前行数和总行数去除空白行 
                                    ln=ln+1 
                                    tt_ln=tt_ln+1 # 总行数 
                                    a=l.split(",")                                           
                                    if len(a)>1:  
                                        # txt文件可能会有表头,只简单判断第1列字符串长度  "内码","外码","批号","所在箱数","日期"
                                        if len(a[0])>9:   
                                            # 2024-06-05 内、外码只能在前两列
                                            # 不同瓶盖厂内外码位位置不同；同一个文件内外码位置也可能不同
                                            nm=''
                                            wm=''                                                
                                            l1=a[0] # 第1列                                                
                                            l2=a[1] # 第2列                                               
                                            if "=" in l1:
                                                l1=l1.split("=")[1]
                                            if "=" in l2:
                                                l2=l2.split("=")[1]

                                            # 判断第1列
                                            if len(l1)==10:
                                                # 'BF5211B146'
                                                wm=l1
                                            elif len(l1)==12:
                                                # 'h83ffba504bc'
                                                nm=l1
                                            # 判断第2列
                                            if len(l2)==10:
                                                # 'BF5211B146'
                                                wm=l2
                                            elif len(l2)==12:
                                                # 'h83ffba504bc'
                                                nm=l2

                                            if nm and wm:   
                                                dn=decrypt_11h(nm)  #盖内明码
                                                dw=str(int(wm,16))  #盖外明码  
                                                aa.append("("+str(f_sn)+","+str(ln)+",'"+nm+"','"+dn+"','"+wm+"','"+dw+"')") 
                                                tt_cnt=tt_cnt+1 # 数据总数
                                                if ln % nLmt==0:
                                                    s=sIns+",".join(aa)
                                                    cur.execute(s) 
                                                    aa=[]  
                                    else:
                                        err='绑定数据的txt至少2列'
                                        break                            
                            if aa:
                                s=sIns+",".join(aa)
                                cur.execute(s)                                
                            
                            f_sn=f_sn+1 #2024-06-21 文件序号加1 

                    # 2024-06-21 将临时表的数据，保留原id写入内外码唯一的临时表
                    s="insert into tt_codes("+\
                            "id_src,f_sn,f_ln,cap,decap,outter,deoutter,rg_cap,rg_outter"+\
                        ") select "+\
                            "id,f_sn,f_ln,cap,decap,outter,deoutter,left(decap,4),left(deoutter,4) from t_codes "+\
                        "on conflict do nothing"                    
                    cur.execute(s)

                    # 取内码业务通知单id
                    cur.execute("select rg_cap from tt_codes group by rg_cap")
                    rs=cur.fetchall()
                    if len(rs)>1:
                        # 同上业务通知单，内码号段应只有一个
                        a=[]
                        for r in rs:
                            a.append(r[0])
                        ea.append('内码号段不唯一:'+",".join(a))
                    else:
                        tb='c'+rs[0][0]                        
                        s="select count(*) from information_schema.tables where table_schema='bottlecap' "+\
                            "and table_type='BASE TABLE' and table_name='"+tb+"'"
                        cur.execute(s)
                        r=cur.fetchone()
                        if r[0]:
                            s="update tt_codes set cap_inform_id=(select inform_id from bottlecap."+tb+" where cap=label)"
                            cur.execute(s)
                            s="select cap_inform_id,count(*) from tt_codes group by cap_inform_id"
                            cur.execute(s)
                            for rr in cur.fetchall():
                                if rr[0]:
                                    if rr[0]!=int(inform_id):
                                        cur.execute("select inform_code from bottlecap_inform where inform_id="+str(inform_id))
                                        r=cur.fetchone()
                                        ea.append(str(rr[1])+"个内码归属"+r[0])
                                else:
                                    ea.append(str(rr[1])+'个内码无对应业务通知单')
                        else:
                            ea.append('内码表bottlecap.'+tb+'不存在(业务单码数据未同步)')
                    
                    # 取外码业务通知单id
                    cur.execute("select rg_outter from tt_codes group by rg_outter")
                    rs=cur.fetchall()
                    if len(rs)>1:
                        # 同上业务通知单，内码号段应只有一个
                        a=[]
                        for r in rs:
                            a.append(r[0])
                        ea.append('外码号段不唯一:'+",".join(a))
                    else:
                        tb='o'+rs[0][0]                        
                        s="select count(*) from information_schema.tables where table_schema='bottlecap' "+\
                            "and table_type='BASE TABLE' and table_name='"+tb+"'"
                        cur.execute(s)
                        r=cur.fetchone()
                        if r[0]:
                            s="update tt_codes set outter_inform_id=(select inform_id from bottlecap."+tb+" where outter=label)"
                            cur.execute(s)
                            s="select outter_inform_id,count(*) from tt_codes group by outter_inform_id"
                            cur.execute(s)
                            for rr in cur.fetchall():
                                if rr[0]:
                                    if rr[0]!=int(inform_id):
                                        cur.execute("select inform_code from bottlecap_inform where inform_id="+str(inform_id))
                                        r=cur.fetchone()
                                        ea.append(str(rr[1])+"个外码归属"+r[0])
                                else:
                                    ea.append(str(rr[1])+'个外码无对应业务通知单')

                        else:
                            ea.append('外码表bottlecap.'+tb+'不存在(业务单码数据未同步)')

                    data['data']={
                        'tt_lns':tt_ln, 
                        'tt_cnt':tt_cnt,                        
                        'tt_ok':0,
                        'txt_cnt':len(f_dict),
                        'errs':[]
                    } 
                    if ea:
                        data['errs']=ea
                    else:
                        pass
                        # 写入
                        s="INSERT INTO bottlecap.twincode_binding(inform_id,cap,outter) "+\
                            "select "+str(inform_id)+",cap,outter from tt_codes "+\
                            "on conflict do nothing"
                        cur.execute(s)
                        data['tt_ok']=cur.rowcount                         
                else:
                    data['e']='请上传经过压缩后的zip文件'
            else:
                data['e']='没有上传任何文件'

    cur.close()         
    return JsonResponse(data)
            


def factory_info(request,what,action):  
    if what=='inform':        
        cur=connection.cursor()
        # 2024-11-07
        if action=='edit':
            # 修改业务单所属瓶盖加工厂
            if request.method=='GET':
                id=request.GET.get('inform_id','0')
                data={'inform':None}               
                s="select inform_id,inform_code,product,to_char(dt_inform,'yyyy-mm-dd'),factories::json "+\
                    "from bottlecap_inform where inform_id="+id
                cur.execute(s)
                r=cur.fetchone()
                if r:                   
                    fcts=[]
                    for f in r[4]:
                        fcts.append({
                            'id':f['id'],
                            'name':f['name'],
                        })
                    data={
                        "id":r[0],
                        "code":r[1],
                        "product":r[2],
                        "dt":r[3],
                        'factories':fcts
                    }
                cur.close()                
                return JsonResponse(data)
            else:
                # 保存   
                d=json.loads(request.body)
                id=str(d['inform_id'])
                fcts=d['factories']
                s='update bottlecap_inform set factories=\''+json.dumps(fcts)+'\' where inform_id='+id 

                #print(s)
                
                cur.execute(s)                
                cur.close()
                return JsonResponse({"e":""})
        elif action=='list':
            # 列表显示盖码业务通知单信息
            data=[]   
            clmns=[
                {'title':'id', 'data':'id'}, 
                {'title':'单据日期', 'data':'tm_inform'}, 
                {'title':'inform_id', 'data':'inform_id'},
                {'title':'业务号', 'data':'inform_code'},    
                {'title':'产品名', 'data':'product'},
                {'title':'类型', 'data':'codetype'},
                {'title':'号段', 'data':'segment'},
                {'title':'起始', 'data':'sn_fr'},
                {'title':'结束', 'data':'sn_to'},
                {'title':'数量', 'data':'qty'},
                {'title':'码同步', 'data':'qty_sync'},  
                {'title':'已上传', 'data':'qty_upload'},
                {'title':'下载', 'data':'downloaded'}, 
                {'title':'码文件','data':'zipfile'},                                    
                {'title':'客户名称', 'data':'company'},
                {'title':'加工企业', 'data':'factory'},   
                              
            ]     

            # 2024-11-07 一个业务单会有多个加工企业，启用factories字段
            factories=[]
            cur.execute("select id,name from company_company where category_id=11 order by name")
            for r in cur.fetchall():
                factories.append({
                    "id":str(r[0]),
                    "name":r[1]
                })

            # 2024-08-06
            s="SELECT id,coalesce((select to_char(max(tm),'yyyy-mm-dd hh24:mi') from bottlecap_downloadlog where inform_id=i.inform_id),''),"+\
                "product,i.inform_id,inform_code,segment,sn_fr,sn_to,qty,"+\
                "to_char(tm_inform,'yyyy-mm-dd hh24:mi'),"+\
                "(select name from bottlecap_codetype where id=codetype_id),"+\
                "(select name from company_company where id=company_id),"+\
                "array_to_string(array((SELECT (jsonb_array_elements(factories)->>'name'))),','),"+\
                "(select coalesce(sum(qty_sync),0) from bottlecap_synclog where inform_id=i.inform_id),"+\
                "coalesce((select sum(qty) from bottlecap_uploadresult where inform_id=i.inform_id),0),"+\
                "dt_inform,factory_id "+\
                "FROM "+\
                "(select * from bottlecap_inform  where is_delete=false) i "             

            cur.execute(s)
            for r in cur.fetchall():  
                fZip=format_zfile(
                    opt='download',
                    inform_id=r[3],
                    qty=r[8],
                    code_type=r[10],
                    dt=r[15], 

                    #company_id=r[16]
                    # 2024-08-19
                    fcty_id=r[16],
                    company_name=r[11]
                )                
                zn=''
                if os.path.exists(fZip):
                    sz=os.path.getsize(fZip)/1024.0
                    zn=os.path.basename(fZip)
                    #+" ("+str(math.ceil(sz))+"KB)"
                data.append({
                    "id":r[0],"downloaded":r[1],"product":r[2],"inform_id":r[3],"inform_code":r[4],
                    "segment":r[5],"sn_fr":r[6],"sn_to":r[7],"qty":r[8],"tm_inform":r[9],"codetype":r[10],"company":r[11],"factory":r[12],
                    "qty_sync":r[13],"qty_upload":r[14],"zipfile":zn
                })
            cur.close()            
            return render(request,'bottlecap/inform_list.html',{'data':data,'clmns':clmns,'factories':factories })

        # 2024-08-28
        elif action=='postpone':
            # 显示瓶盖厂延迟上传(或从未上传)的业务通知单
            data=[]
            dt_inform=request.GET.get('dt_inform','')
            interval=request.GET.get('interval','')
            percent=request.GET.get('percent','')
            days=request.GET.get('days','')
            if dt_inform and days and percent:
                # ajax返回json  
                if percent=='0':
                    sAnd=" and qty_upload=0"
                else:
                    sAnd=" and (round(qty_upload*100/qty,0))<="+percent 
                s="SELECT product,i.inform_id,inform_code,segment,qty,"+\
                    "to_char(tm_inform,'yyyy-mm-dd hh24:mi'),"+\
                    "(select name from bottlecap_codetype where id=codetype_id),"+\
                    "(select name from company_company where id=company_id),"+\
                    "(select name from company_company where id=factory_id), "+\
                    "coalesce(qty_upload,0) qty_upload,"+\
                    "case when tm_min is null then '' else "+\
                        "case when tm_max::date=tm_min::date then to_char(tm_min,'yyyy-mm-dd') else "+\
                            "to_char(tm_min,'yyyy-mm-dd')||'~'||to_char(tm_max,'yyyy-mm-dd') end "+\
                    "end,"+\
                    "coalesce(u.tm_min,now())::date-tm_inform::date days "+\
                    "from "+\
                    "(select * from bottlecap_inform where dt_inform>='"+dt_inform+"') i "+\
                    "left outer join "+\
                    "(select inform_id,sum(qty) qty_upload,min(tm) tm_min,max(tm) tm_max from bottlecap_uploadresult group by inform_id) u "+\
                    "on i.inform_id=u.inform_id "+\
                    "where coalesce(u.tm_min,now())::date-tm_inform::date>="+days+sAnd 
                cur.execute(s)
                rs=cur.fetchall()
                for r in rs:
                    p=''
                    if r[9]:
                        p=str(int(round(r[9]*100/r[4],0)))+"%"
                    '''
                    data.append({
                        'inform_id':r[1],
                        'tm_inform':r[5],                    
                        'inform_code':r[2], 
                        'product':r[0],
                        'codetype':r[6],
                        'segment':r[3],                               
                        'company':r[7],
                        'factory':r[8], 
                        'qty_inform':r[4], 
                        'qty_upload':r[9], 
                        'percent':p,
                        'days':r[11],
                        'dt_upload':r[10],                        
                    })
                    '''
                    data.append([
                        r[1],
                        r[5],                    
                        r[2], 
                        r[0],
                        #r[6],
                        #r[3],                                         
                        r[7],
                        r[8], 
                        r[4],
                        r[9], 
                        p, 
                        r[11],
                        r[10]
                    ])

                cur.close()     
                return JsonResponse({'data':data,'e':''})                   
            else:
                data=[]   
                clmns=[
                    {'title':'inform_id', 'data':'inform_id'},                    
                    {'title':'单据日期', 'data':'tm_inform'},                     
                    {'title':'业务号', 'data':'inform_code'},    
                    {'title':'产品名', 'data':'product'},
                    #{'title':'类型', 'data':'codetype'},
                    #{'title':'号段', 'data':'segment'},           
                    {'title':'客户名称', 'data':'company'},
                    {'title':'加工企业', 'data':'factory'},  
                    {'title':'生号数量', 'data':'qty_inform'},  
                    {'title':'上传数量', 'data':'qty_upload'},   
                    {'title':'百分比', 'data':'percent'},  
                    {'title':'延迟天数', 'data':'days'},  
                    {'title':'上传日期', 'data':'dt_upload'},  
                ]
                return render(request,'bottlecap/postpone_list.html',{'data':data,'clmns':clmns})


    elif what=='factory':
        if action=='view':
            # 瓶盖厂信息页面
            cur=connection.cursor()
            data={'factories':[]}
            cur.execute("select id,name from company_company where category_id=11")
            for r in cur.fetchall():
                data['factories'].append({"id":r[0],"name":r[1]})
            cur.close()
            return render(request,'bottlecap/factory_info.html',data)
        elif action=='get':
            # 获取指定瓶盖厂信息(与之关联的企业)
            company_id=request.GET.get('company_id','')
            data={'company_id':'','company_name':'','customers':[]} 
            if company_id:
                cur=connection.cursor()
                s="select company_id,customers::json,"+\
                    "(select name from company_company where id=company_id) "+\
                    "from bottlecap_factory where company_id="+company_id
                cur.execute(s)
                r=cur.fetchone()               
                if r:        
                    data['company_id']=r[0]
                    data['company_name']=r[2]
                    for c in r[1]:  
                        data['customers'].append({'id':c['id'],'name':c['name']})
                cur.close()
            else:
                data['e']='company id missing'
            return JsonResponse(data)
        elif action=='post':
            # 保存瓶盖厂信息 (设置瓶盖厂与企业关联)
            data=json.loads(request.body)         
            fs=Factory.objects.filter(company_id=int(data['company_id']))
            if fs:
                o=fs[0]
            else:
                o=Factory()
            for k,v in data.items():
                setattr(o,k,v)
            o.save()              
            return JsonResponse(data)     
    elif what=='zip':        
        if action=='download':           
            companys=[]
            # 瓶盖厂company_id
            id=request.user.company_id
            fs=Factory.objects.filter(company_id=id)
            if fs:
                f=fs[0]
                for c in f.customers:    
                    companys.append({'id':c['id'],'name':c['name']})  
            return render(request,'bottlecap/factory_download.html',{'companys':companys})
        elif action=='upload':
            # 2024-08-16
            cs=[]
            s="SELECT customers::json FROM bottlecap_factory where company_id="+str(request.user.company_id)
            cur=connection.cursor()
            cur.execute(s)
            r=cur.fetchone()
            for c in r[0]:   
                cs.append({'id':c['id'],'name':c['name']})
            cur.close()
            return render(request,'bottlecap/factory_upload.html',{'cs':cs})

        elif action=='uploadlog':
            # 2024-08-02 默认查询一周内的上传记录
            now=datetime.datetime.now()
            dt1= (now - datetime.timedelta(days=6)).strftime('%Y-%m-%d')
            dt2=now.strftime('%Y-%m-%d')

            return  render(request,'bottlecap/factory_upload_log.html', {'dt_fr':dt1, 'dt_to':dt2} )

        # 2024-09-20
        elif action=='twincode':
            # 上传面底绑定页面
            return  render(request,'bottlecap/twincode_upload.html')





    elif what=='account':
        if action=='list':
            clmns=[
                {'title':'用户帐号','data':'username'},
                {'title':'姓名','data':'last_name'},
                {'title':'企业名称','data':'company'}                    
            ]
            data=[]
            cur=connection.cursor()
            cid=str(request.user.company_id)
            s="SELECT username,last_name,"+\
                "(select name from company_company where id=company_id) "+\
                "FROM company_user where company_id="+cid
            cur.execute(s)
            for r in cur.fetchall():
                data.append({
                    "username":r[0],
                    "last_name":r[1],
                    "company":r[2]
                })
            cur.close()
            return render(request,'bottlecap/user_list.html',{'clmns':clmns,'data':data})


# 2024-03-08
@login_required
def bottlecap_infor(request,action):
    data={'e':'', 'data':{} }
    if action=='view':
        return render(request,'bottlecap/bottlecap_infor.html')
    elif action=='ajax':    
        data['data']['inform']={}
        data['data']['fcty_binding']={}
        data['data']['label_type']=''
        c=request.GET.get('code','')
        if not c:
            data['e']='盖码不能为空'
            return JsonResponse
       
        # 判断是内码还是外码 "he38ad5ec3d1" c;  "D834E00DD6" o        
        t=''
        if len(c)==10:
            try:
                code=str(int(c,16))
                t='o'
            except Exception:
                pass
        else:
            if c.isdigit():
                t='o'
                code=c
            else:
                code=decrypt_11h(c)    
                if code:
                    t='c'
        if not t:
            data['e']='无法识别的码类型'
            return JsonResponse(data)
        
        cur=connection.cursor()       
        
        # 业务通知单信息
        s="select product,inform_code,segment,sn_fr,sn_to,qty,dt_inform,"+\
            "coalesce((select name from bottlecap_codetype where id=codetype_id),''),"+\
            "inform_id from bottlecap_inform where inform_id="+\
                "(select inform_id from bottlecap."+t+code[:4]+" where label='"+c+"')"    
        cur.execute(s)
        r=cur.fetchone()
        if r:
            data['data']['inform']={
                'product':r[0],'code':r[1],'segment':r[2],'sn_fr':r[3],'sn_to':r[4],'qty':r[5],'dt':r[6].strftime('%Y-%m-%d'),'id':r[8]
            }
            data['data']['label_type']=r[7]        
            
        
        # 瓶盖厂绑定信息
        if t=='c':      
            # 盖内码，查询分表
            tb_f="fc"+code[:4]
            sW=" where cap='"+c+"'"
        else:
            # 盖内码，查询分表
            tb_f="fo"+code[:4]
            sW=" where outter='"+c+"'"

        s="select count(*) from information_schema.tables where table_schema='bottlecap' "+\
            "and table_type='BASE TABLE' and table_name='"+tb_f+"'"
        cur.execute(s)
        r=cur.fetchone()
        if r[0]:
            # 2024-08-06
            s="select cap,outter,file_name,file_size,lines,'',to_char(tm,'yyyy-mm-dd hh24:mi:ss'),"+\
                "(select name from company_company where id=company_id),"+\
                "(select name from company_company where id=factory_id),"+\
                "(select sum(qty) from bottlecap_uploadresult where batch_id=b.id and code_type_id=1),"+\
                "(select sum(qty) from bottlecap_uploadresult where batch_id=b.id and code_type_id=2) "+\
                "from "+\
                "(select batch_id,cap,outter,dt from bottlecap."+tb_f+sW+") f "+\
                "left outer join "+\
                "(SELECT id,company_id,factory_id,file_name,lines,cnt,tm,file_size FROM bottlecap.fcty_binding_batch) b "+\
                "on f.batch_id=b.id"    
            cur.execute(s)
            r=cur.fetchone()
        else:
            r=None     
        if r:
            # 有瓶盖厂绑定绑定信息
            data['data']['fcty_binding']={
                'cap':r[0],'outter':r[1],'file':r[2],'size':r[3],'lines':r[4],'ok_cap':r[9],'ok_outter':r[10],'tm':r[6],'company':r[7],'factory':r[8]
            }
        cur.close()
    return JsonResponse(data)

