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):

    # 2023-10-27目前 瓶盖码数据接口放在66服务器上
    inter_url='http://www.china315net.com:35088'
    
    sub='bottlecap'

    data={'e':'','data':[],'cnt':0}
    
    #t=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    #k=internal_Sk
    #m2=hashlib.md5((k+t).encode('utf8'))
    #key=m2.hexdigest()

    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"
            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=[]
            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)   

            for rr in cur.fetchall():
                iks=inernal_key()
                r=requests.get(inter_url+"/"+sub+"/"+what+"/list/",params={'customer_id':rr[0],'time':iks['time'],'key':iks['key']})
                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']
                            t='未知'                            
                            if '内' in p:
                                t=1
                            elif "外" in p:
                                t=2
                            vData={
                                'company_id':rr[2],
                                'factory_id':rr[3],
                                '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:
                    if "内网取数据失败" not in e:
                        e.append(rr[1]+" 内网取数据失败")                
            if e:
                data['e']=",".join(e)
            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]
            
            # 内网区分盖内外码： inner outer 
            if o.codetype_id==1:
                ct='inner'
            elif o.codetype_id==2:
                ct='outer' 
            # 内外码是算法绑定还是瓶盖厂扫码绑定后上传
            bt=str(o.bindingtype_id)
            if id and ct and bt:
                url=inter_url+'/'+sub+'/codes/download/'   
                iks=inernal_key()
                params={'inform_id':id,'code_type':ct,'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 bt=='2':
                            # 内外盖码在瓶盖厂扫码绑定
                            if ct=='inner':
                                # 内码
                                tb="bottlecap.cap"                                
                            elif ct=='outer':
                                # 外码 目前盖外码为16进制
                                tb="bottlecap.outter"                                
                        elif bt=='1':
                            # 给瓶盖厂提供版本2的加密算法dll，内外码用算法绑定
                            # 只会下载盖外明码数据
                            tb="bottlecap.outter" 
                        if tb:
                            a=[]    
                            sIns="insert into "+tb+"(inform_id,label,code,tm) "
                            # 码数据量大，各种判断要在for循环外面做
                            if bt=='1':
                                # 算法绑定 只会下载盖外明码数据 label与code相同                               
                                for d in dd['data']:
                                    a.append("("+id+",'"+d+"','"+d+"',now())")
                            elif bt=='2':
                                # 内外码由瓶盖厂扫码绑定
                                if ct=='outer':
                                    # 外码为16进制
                                    for d in dd['data']: 
                                        c=str(int(d, 16))
                                        a.append("("+id+",'"+d+"','"+c+"',now())")
                                elif ct=='inner':
                                    # 内码为加密码
                                    for d in dd['data']: 
                                        c=decrypt_11h(d)                                        
                                        a.append("("+id+",'"+d+"','"+c+"',now())")   
                            if a:
                                s=sIns+" values "+",".join(a)+" on conflict(label) do nothing"   
                                cur.execute(s)
                                cnt=cur.rowcount    
                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','')
            if not id:
                data['e']='inform id missing'
                cur.close()
                return JsonResponse(data)
            #cur.execute("select company_id,code_type,dt,qty from bottlecap.inform where inform_id="+id)
            s="select factory_id,(select name from bottlecap_codetype where id=codetype_id),"+\
                "dt_inform,qty 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]            
            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 ct=='盖内码':
                tb='bottlecap.cap'
            elif ct=='盖外码':
                tb='bottlecap.outter'
            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
            for r in cur.fetchall():
                f.write(sUrl+r[0]+"\n")
                cnt=cnt+1
            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文件 
            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 "
            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':
            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)   
            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,'') "+\
                "FROM bottlecap.fcty_binding_batch "+sWhere   
            cur.execute(s)
            d=[]
            for r in cur.fetchall():
                d.append({
                    "batch_id":r[0],                    
                    "tm":r[1],
                    "file_name":r[2],
                    "file_size":r[3],
                    "lines":r[4],
                    "cnt":r[5],
                    "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']  

        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
            tt_cnt=0   
            files = request.FILES.getlist('files')
            # 临时表一次最多插入行数
            lmt=10000
            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),"+\
                            "outter character varying(20),"+\
                            "fn     character varying(100)"+\
                        ");"+\
                        "CREATE INDEX cap_idx on t_codes(cap);"+\
                        "CREATE INDEX outter_idx on t_codes (outter);"+\
                        "CREATE INDEX fn_idx on t_codes (fn);"
                    )     
                    #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():                                  
                            tfn=f.encode('cp437').decode('gbk',"ignore")                            
                            ln=0
                            cnt=0                            
                            aa=[]
                            err='' 

                            #if True:
                            try:                            
                                for l in archive.read(f).split(b"\n"):  
                                    ln=ln+1
                                    tt_ln=tt_ln+1
                                    l=str(l.decode(cd)).strip().replace("，",",")                                   
                                    if l:
                                        a=l.split(",")
                                        if len(a)>1:  
                                            # 内码
                                            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]  
                                            aa.append("('"+nm+"','"+wm+"','"+tfn+"')")    
                                        else:
                                            err='绑定数据的txt至少2列'
                                            break 
                                        if ln==lmt:
                                            s="insert into t_codes(cap,outter,fn) values "+",".join(aa) 
                                            cur.execute(s)
                                            aa=[] 
                                if aa:
                                    s="insert into t_codes(cap,outter,fn) values "+",".join(aa)
                                    cur.execute(s)                                    
                            except Exception as e:
                                break
                            #print('len(aa):',len(aa))


                            # 把当前文件数据写入内外码绑定表
                            s="INSERT INTO bottlecap.fcty_binding_details("+\
                                "company_id,batch_id,cap,outter,cap_inform_id,outter_inform_id) select "+\
                                cid+","+bid+",cap,outter,"+\
                                "(select inform_id from bottlecap.cap where    label=cap),"+\
                                "(select inform_id from bottlecap.outter where label=outter) "+\
                                "from t_codes where fn='"+tfn+"' "+\
                                    "and (select inform_id from bottlecap.cap where    label=cap) is not null "+\
                                    "and (select inform_id from bottlecap.outter where label=outter) is not null "+\
                                "on conflict(cap) do nothing"                                
                            cur.execute(s)
                            cnt=cur.rowcount
                            tt_cnt=tt_cnt+cnt
                            result.append({ 'name':tfn, 'lines':ln,  'cnt':cnt, 'err':err })   

                    # 处理结果更新batch
                    s="update bottlecap.fcty_binding_batch set "+\
                        "lines="+str(tt_ln)+",cnt="+str(tt_cnt)+","+\
                        "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':'inform_code'},            
                {'title':'id', 'data':'inform_id'},            
                {'title':'产品名', 'data':'product'},
                {'title':'号段', 'data':'segment'},
                {'title':'起始', 'data':'sn_fr'},
                {'title':'结束', 'data':'sn_to'},
                {'title':'数量', 'data':'qty'},
                {'title':'码同步', 'data':'qty_sync'},                  
                {'title':'码文件','data':'zipfile'},
                {'title':'单据日期', 'data':'tm_inform'},
                {'title':'类型', 'data':'codetype'},
                {'title':'客户名称', 'data':'company'},
                {'title':'加工企业', 'data':'factory'},   
                {'title':'下载', 'data':'downloaded'},            
                {'title':'已上传', 'data':'qty_upload'},               
            ]            

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