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):
    return dt.strftime('%Y%m%d')+"_"+code_type+"_"+str(inform_id)+"_"+str(qty)+'枚'

def format_zfile(opt,company_id,code_type,dt,inform_id,qty):
    '''
    生成或下载业务通知单盖码数据的zip文件名
    pZip \\company_id\\yyyymmdd_codetype_informid_qty枚.zip
    '''   
    fn=os.path.join(pZip, opt, str(company_id),
        format_name(code_type,dt,inform_id,qty)+".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=[]
            #s="select i.erp_customer_id,(select name from company_company where id=i.company_id),i.company_id,c.factory_id from "+\
            #    "(SELECT (jsonb_array_elements(customers)->>'id')::int company_id,company_id factory_id FROM bottlecap_factory) c "+\
            #    "left outer join "+\
            #    "(select erp_customer_id,company_id,remark from company_internalinfo where erp_customer_id is not null) i "+\
            #    "on c.company_id=i.company_id "+\
            #    "where i.erp_customer_id is not null"
            #cur.execute(s)   

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

            # 已同步过的业务通知单
            for i in Inform.objects.all():
                eids.append(str(i.inform_id))
            #for rr in cur.fetchall():
            #    cids.append(str(rr[0]))
            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']                        
                        #s="select erp_customer_id,company_id,"+\
                        #    "(select company_id from bottlecap_factory where i.company_id in ("+\
                        #        "select (jsonb_array_elements(customers)->>'id')::int)"+\
                        #    ") factory_id "+\
                        #    "from company_internalinfo i where erp_customer_id="+cid
                        
                        # 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']
                            }
                            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
            cur.execute(s)
            r=cur.fetchone()
            if not r:
                data['e']='inform not exists'
                cur.close()
                return JsonResponse(data)            
            # 码类型 盖内码/ 盖外码
            cid=r[0]
            ct=r[1]
            dt=r[2]
            qty=r[3]   
            codetype_id=r[4]

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

            fZip=format_zfile(
                opt='download',
                company_id=cid,
                code_type=ct,
                dt=dt,  
                inform_id=id,
                qty=qty
            )

            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)

            '''
            # 调用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)

            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),"+\
                "coalesce(c.cnt,0)+coalesce(o.cnt,0) cnt,"+\
                "coalesce((select to_char(max(tm),'yyyy-mm-dd hh24:mi:ss') 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 "+\
                 "left outer join "+\
                "(select outter_inform_id,count(*) cnt from bottlecap.fcty_binding_details group by outter_inform_id) o "+\
                "on i.inform_id=o.outter_inform_id "+\
                "left outer join "+\
                "(select cap_inform_id,count(*) cnt from bottlecap.fcty_binding_details group by cap_inform_id) c "+\
                "on i.inform_id=c.cap_inform_id "
            
            # 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),"+\
                "(select sum(qty_sync) from bottlecap_synclog where inform_id=i.inform_id) 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 "      
        
            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                    
                )
                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

            dt=datetime.datetime.now().strftime('%Y-%m-%d')
            dt_fr=request.GET.get('dt_fr',dt)
            dt_to=request.GET.get('dt_to',dt)
            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

            s="SELECT id,to_char(tm,'yyyy-mm-dd hh24:mi:ss'),file_name,to_char(file_size,'0.009'),"+\
                "lines,cnt,(select username from company_user where id=user_id),"+\
                "array_to_string(array(select jsonb_array_elements(txts)->>'name'),','),"+\
                "(select name from company_company where id=factory_id),coalesce(remark,''),factory_id "+\
                "FROM bottlecap.fcty_binding_batch "+sWhere   
            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-04-19
                if can_download:                    
                    #pZip+"\\upload\\"+cid+"\\"+dt                    
                    fn='<a style="cursor:pointer;" cid='+str(r[10])+' dt="'+r[1][:10]+'">'+r[2]+'</a>'
                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":r[5],
                    "cnt":cnt,  #2024-04-18

                    "user":r[6],
                    "txts":r[7],   
                    "factory":r[8],
                    "remark":r[9],
                })
            cur.close()
            data['data']=d
            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                
            cur.execute(s)
            r=cur.fetchone()
            if not r:
                data['e']='没有当前帐号可供下载的数据'
                cur.close()
                return JsonResponse(data)            
            # 码类型 盖内码/ 盖外码
            #cid=r[0]
            ct=r[1]
            dt=r[2]
            qty=r[3]       
            fZip=format_zfile(
                opt='download',
                company_id=cid,
                code_type=ct,
                dt=dt,  
                inform_id=id,
                qty=qty
            )
            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)))+")"
                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
            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')  

            # 1:第1列 内码 第2列 外码       2:第1列 外码 第2列 内码 
            pos=request.POST.get('pos','')
            if not pos:
                data['e']='没有指定数据格式'
                return JonsResponse(data)
            if int(pos)==1:
                # 第1列 内码            第2列 外码        
                pos_n=0
                pos_w=1
            elif int(pos)==2:
                # 第1列 外码            第2列 内码
                pos_n=1
                pos_w=0
            
            result=[]
            tt_ln=0     #zip文件总行数
            tt_cnt=0    #zip文件数据总数
            tt_ok=0     #实际总写入绑定表总数

            cap_inform_err=0            #盖内码没有业务通知单
            outter_inform_err=0         #盖外码没有业务通知单
            
            files = request.FILES.getlist('files')           
            
            # 临时表一次最多插入行数
            lmt=100000
            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"
                    cur.execute(s)
                    r=cur.fetchone()                    
                    bid=str(r[0])  
                    tb_n="bottlecap.cap"
                    tb_w="bottlecap.outter"
                    cur.execute("create temp table t_codes("+\
                            "id serial PRIMARY KEY,"+\
                            "cap    character varying(20),"+\
                            "rg_cap character varying(4),"+\
                            "decap  character varying(20),"+\
                            "cap_inform_id int,"+\
                            "outter character varying(20),"+\
                            "rg_outter character varying(4),"+\
                            "deoutter  character varying(20),"+\
                            "outter_inform_id int"+\
                        ");"+\
                        "CREATE INDEX decap_idx             on t_codes(decap);"+\
                        "CREATE INDEX deoutter_idx          on t_codes(deoutter);"+\
                        "CREATE INDEX rg_cap_idx            on t_codes(rg_cap);"+\
                        "CREATE INDEX rg_outter_idx         on t_codes(rg_outter);"+\
                        "CREATE INDEX outter_inform_idx     on t_codes(outter_inform_id);"+\
                        "CREATE INDEX cap_inform_idx        on t_codes(cap_inform_id);"
                    )       
                    sIns="insert into t_codes(cap,decap,rg_cap,outter,deoutter,rg_outter) values "                    
                    #with zipfile.ZipFile(zf, mode="r") as archive: 
                    with pyzipper.AESZipFile(zf, mode="r") as archive:  
                        archive.setpassword(bytes(pwd,"utf8"))
                        for f in archive.namelist():
                            # zip内每个txt文件写入前先清空临时表
                            cur.execute("truncate table t_codes")
                            
                            #tfn=f.encode('cp437').decode('gbk',"ignore") 
                            # 2024-05-24
                            tfn=str(f)  

                            ln=0    #当前txt总行数    
                            cnt=0   #当前txt数据总数
                            ok=0    #当前txt写入总数
                            aa=[]
                            err='' 

                            e_c=0   #当前文件 内 码没有业务通知单
                            e_o=0   #当前文件 外 码没有业务通知单
                            icnt=0  #aa数组长度计数器
                            
                            if True:
                            #try:                            
                                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:                                        
                                                # 内码
                                                if "=" in a[pos_n]:             
                                                    nm=a[pos_n].split("=")[1]
                                                else:
                                                    nm=a[pos_n] 
                                                # 外码
                                                if "=" in a[pos_w]:             
                                                    wm=a[pos_w].split("=")[1]
                                                else:
                                                    wm=a[pos_w]                                             
                                                
                                                # 2024-03-05 
                                                dc=decrypt_11h(nm)  #盖内明码
                                                dr=str(int(wm,16))  #盖外明码   
                                                rg_c=dc[:4]
                                                rg_o=dr[:4]
                                                aa.append("('"+nm+"','"+dc+"','"+rg_c+"','"+wm+"','"+dr+"','"+rg_o+"')")
                                                icnt=icnt+1
                                        else:
                                            err='绑定数据的txt至少2列'
                                            break                                    

                                        if icnt==lmt:    
                                            s=sIns+",".join(aa) 
                                            cur.execute(s) 
                                            aa=[] 
                                            icnt=0
                                            
                                            cnt=cnt+lmt
                                            tt_cnt=tt_cnt+lmt
                                if aa:
                                    s=sIns+",".join(aa)
                                    cur.execute(s)  
                                    n=cur.rowcount
                                    cnt=cnt+n
                                    tt_cnt=tt_cnt+n
                            #except Exception as e:
                            #    break                          


                            # 更新临时表 outter_inform_id
                            s="select rg_outter from t_codes group by rg_outter"
                            cur.execute(s)
                            rs=cur.fetchall()
                            for r in rs:
                                rg=r[0]
                                tb="bottlecap.o"+rg
                                # 盖外码号段是否存在
                                s="select count(*) from information_schema.tables where table_schema='bottlecap' "+\
                                    "and table_type='BASE TABLE' and table_name='o"+rg+"'"                                
                                cur.execute(s)
                                rr=cur.fetchone()
                                if rr[0]:
                                    s="update t_codes set outter_inform_id=coalesce("+\
                                        "(select inform_id from "+tb+" where code=deoutter),0) "+\
                                        "where rg_outter='"+rg+"'"
                                    cur.execute(s)

                            # 更新临时表 otter_inform_id
                            s="select rg_cap from t_codes group by rg_cap"
                            cur.execute(s)
                            rs=cur.fetchall()
                            for r in rs:
                                rg=r[0]
                                tb="bottlecap.c"+rg
                                # 盖外内码号段是否存在
                                s="select count(*) from information_schema.tables where table_schema='bottlecap' "+\
                                    "and table_type='BASE TABLE' and table_name='c"+rg+"'"                                
                                cur.execute(s)
                                rr=cur.fetchone()
                                if rr[0]:
                                    s="update t_codes set cap_inform_id=coalesce("+\
                                        "(select inform_id from "+tb+" where code=decap),0) "+\
                                        "where rg_cap='"+rg+"'"
                                    cur.execute(s)                               

                            # 内外码业务通知单为空的置0
                            cur.execute("update t_codes set cap_inform_id=0 where cap_inform_id is null")
                            cur.execute("update t_codes set outter_inform_id=0 where outter_inform_id is null")                            
                            
                            # 2024-05-22
                            # 若inform_id为空，也可能是业务通知单数据末同步。先全部写入，在指定页面显示这些空inform_id数据
                            s="select rg_cap,outter_inform_id,cap_inform_id,count(*) cnt from t_codes "+\
                                "group by rg_cap,outter_inform_id,cap_inform_id "
                            cur.execute(s)
                            rs=cur.fetchall()                            
                            for rr in rs:  
                                rg=rr[0]
                                tb_p="fcty_binding_details" 
                                tb="f"+rg                                
                                io_id=str(rr[1])
                                ic_id=str(rr[2])  
                                cur_cnt=rr[3]
                                if io_id=='0':
                                    outter_inform_err=outter_inform_err+cur_cnt   
                                    e_o=e_o+cur_cnt
                                if ic_id=='0':
                                    cap_inform_err=cap_inform_err+cur_cnt
                                    e_c=e_c+cur_cnt                                     

                                """
                                检查号段表是否存在，不存在就创建
                                """    
                                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+"_decap_check  CHECK(\"left\"(decap::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_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+"_decap_idx          ON bottlecap."+tb+" USING btree(decap);"+\
                                    "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 t_codes where rg_cap='"+rg+"' and outter_inform_id="+io_id+" and cap_inform_id="+ic_id+\
                                    " on conflict(decap) do nothing"
                                cur.execute(s)                             
                                cur_ok=cur.rowcount 

                                ok=ok+cur_ok
                                tt_ok=tt_ok+cur_ok    

                                # 处理结果写入日志
                                s="insert into bottlecap_uploadlog(batch_id,segment,cap_inform_id,cap_qty_tt,cap_qty_ok,"+\
                                    "outter_inform_id,outter_qty_tt,outter_qty_ok) values ("+\
                                        bid+",'"+rg+"',"+ic_id+","+str(cur_cnt)+","+str(cur_ok)+","+\
                                        io_id+","+str(cur_cnt)+","+str(cur_ok)+\
                                    ")"                                    
                                cur.execute(s)
                            result.append({'name':tfn,'lines':ln,'cnt':cnt,'ok':ok,'err':err,'tt_ok':tt_ok,'e_outter':e_o,'e_cap':e_c })                               
                    # 处理结果更新batch
                    s="update bottlecap.fcty_binding_batch set "+\
                        "lines="+str(tt_ln)+",cnt="+str(tt_cnt)+",cnt_ok="+str(tt_ok)+","+\
                        "cap_inform_err="+str(cap_inform_err)+",outter_inform_err="+str(outter_inform_err)+","\
                        "txts='"+json.dumps(result)+"' where id="+bid   
                    cur.execute(s)  
                    if tt_ln==0:
                        if pwd:
                            ee='请确认密码是否正确'
                        else:
                            ee='请确认压缩格式是否正确,文件是否有损坏'
                        data['e']='处理失败,'+ee
                    data['result']={'tt_lns':tt_ln, 'tt_cnt':tt_cnt,'txts':result,'tt_txts':len(result)}                    
                else:
                    data['e']='请上传经过压缩后的zip文件'
            else:
                data['e']='没有上传任何文件'

            data['cnt']=cnt
    cur.close()         
    return JsonResponse(data)
            


def factory_info(request,what,action):  
    if what=='inform':        
        cur=connection.cursor()
        if 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':'downloaded'}, 
                {'title':'码文件','data':'zipfile'}, 
                {'title':'已上传', 'data':'qty_upload'},                   
                {'title':'客户名称', 'data':'company'},
                {'title':'加工企业', 'data':'factory'},   
                              
            ]            

            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),"+\
                "(select name from company_company where id=factory_id), "+\
                "coalesce(ic.cnt,0)+coalesce(io.cnt,0) cnt,coalesce(o.cnt,0)+coalesce(c.cnt,0),dt_inform,factory_id "+\
                "FROM "+\
                "(select * from bottlecap_inform  where is_delete=false) i "+\
                "left outer join "+\
                "(select outter_inform_id,count(*) cnt from bottlecap.fcty_binding_details group by outter_inform_id) o "+\
                "on i.inform_id=o.outter_inform_id "+\
                "left outer join "+\
                "(select cap_inform_id,count(*) cnt from bottlecap.fcty_binding_details group by cap_inform_id) c "+\
                "on i.inform_id=c.cap_inform_id "+\
                "left outer join "+\
                "(select inform_id,count(*) cnt from bottlecap.cap group by inform_id) ic "+\
                "on i.inform_id=ic.inform_id "+\
                "left outer join "+\
                "(select inform_id,count(*) cnt from bottlecap.outter group by inform_id) io "+\
                "on i.inform_id=io.inform_id "   


            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),"+\
                "(select name from company_company where id=factory_id), "+\
                "(select coalesce(sum(qty_sync),0) from bottlecap_synclog where inform_id=i.inform_id),"+\
                "coalesce(case "+\
                    "when i.codetype_id in (1,6) then "+\
                        "(select sum(cap_qty_ok) from bottlecap_uploadlog where cap_inform_id=i.inform_id) "+\
                    "else "+\
                        "(select sum(outter_qty_ok) from bottlecap_uploadlog where outter_inform_id=i.inform_id) "+\
                "end,0),"+\
                "dt_inform,factory_id "+\
                "FROM "+\
                "(select * from bottlecap_inform  where is_delete=false) i "+\
                "left outer join "+\
                "(select outter_inform_id,count(*) cnt from bottlecap.fcty_binding_details group by outter_inform_id) o "+\
                "on i.inform_id=o.outter_inform_id "+\
                "left outer join "+\
                "(select cap_inform_id,count(*) cnt from bottlecap.fcty_binding_details group by cap_inform_id) c "+\
                "on i.inform_id=c.cap_inform_id"
                
                
                
                #"left outer join "+\
                #"(select outter_inform_id,count(*) cnt from bottlecap.fcty_binding_details group by outter_inform_id) o "+\
                #"on i.inform_id=o.outter_inform_id "+\
                #"left outer join "+\
                #"(select cap_inform_id,count(*) cnt from bottlecap.fcty_binding_details group by cap_inform_id) c "+\
                #"on i.inform_id=c.cap_inform_id"
                
            

            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]                    
                )                
                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})

    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':
            return render(request,'bottlecap/factory_upload.html')
        elif action=='uploadlog':
            return render(request,'bottlecap/factory_upload_log.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="f"+code[:4]
            sW=" where cap='"+c+"'"
        else:
            # 是盖外码，查询总表
            tb_f='fcty_binding_details'
            sW=" where outter='"+c+"'"
        s="select cap,outter,file_name,file_size,lines,cnt,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) "+\
            "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()
        if r:
            # 有瓶盖厂绑定绑定信息
            data['data']['fcty_binding']={
                'cap':r[0],'outter':r[1],'file':r[2],'size':r[3],'lines':r[4],'cnt':r[5],'tm':r[6],'company':r[7],'factory':r[8]
            }
        cur.close()
    return JsonResponse(data)

