# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.shortcuts import render
from django.http import HttpResponse,JsonResponse
from django.views.decorators.csrf import csrf_exempt
import os
#import psycopg2
try:
    import simplejson as json
except ImportError:
    import json 

import pyodbc
import datetime
import sys
from django.db import connection


#gis_db='gis'
#gis_usr='acc'
#gis_pwd='123'
#gis_host='localhost'
#gis_port='5432'

#直辖市 行政区划编码前2位
zxs=["50","11","12","31"]



# Create your views here.
def ajax_adm_center(request,id):
    #cn =psycopg2.connect(
    #    database=gis_db, 
    #    user=gis_usr, 
    #    password=gis_pwd, 
    #    host=gis_host, 
    #    port=gis_port
    #)
    
    cur=connection.cursor()
    s="SELECT public.ST_X(public.ST_Centroid(geom)),public.ST_Y(ST_Centroid(geom)) FROM gis.adm where id="+id
    cur.execute(s)
    r=cur.fetchone()
    data={'lng':r[0],'lat':r[1]}
    response=json.dumps(data,ensure_ascii=False)
    c=request.GET.get('callback','')
    if c:
        response=c+'('+response+')'
        return HttpResponse(response)
    else:
        return HttpResponse(response,content_type='application/json')


def ajax_adm_desc(request,id):
    data={'name_1':'','name_2':'','name_3':'','name':'','id':id}
    #cn =psycopg2.connect(
    #    database=gis_db, 
    #    user=gis_usr, 
    #    password=gis_pwd, 
    #    host=gis_host, 
    #    port=gis_port
    #)
    cur=connection.cursor()
    s="select " + \
        "case when level=1 then name else (select name from gis.adm where id=a.id_1 limit 1) end," + \
        "coalesce(case when level=2 then name else (select name from gis.adm where id=a.id_2 limit 1) end,'')," + \
        "coalesce(case when level=3 then name else (select name from gis.adm where id=a.id_3 limit 1) end,'')," + \
        "name,id FROM gis.adm a where id="+id
    cur.execute(s)
    r=cur.fetchone()
    if r:
        data['name_1']=r[0]
        data['name_2']=r[1]
        data['name_3']=r[2]
        data['name']=r[3]
        data['id']=r[4]    
    response=json.dumps(data,ensure_ascii=False)
    c=request.GET.get('callback','')
    if c:
        response=c+'('+response+')'
        return HttpResponse(response)
    else:
        return HttpResponse(response,content_type='application/json')

@csrf_exempt
def ajax_adm_info(request,level=1,parent_id=None):
    #cn =psycopg2.connect(
    #    database=gis_db, 
    #    user=gis_usr, 
    #    password=gis_pwd, 
    #    host=gis_host, 
    #    port=gis_port
    #)
    cur=connection.cursor()    
    data=[] 
    if not parent_id:
        parent_id='0'
    #是否直辖市 LEVEL 和 ID_要加1
    is_zx=False    
    cur.execute("select code from gis.adm where id="+parent_id)    
    r=cur.fetchone()
    if r:
        if r[0][:2] in zxs:
            is_zx=True
    s=''
    if level=='1':
        s="select id,name from gis.adm where level=1"
    elif level=='2':
        if is_zx:
            s="select id,name from gis.adm where level=3 and id_1="+parent_id+\
                " and name not in ('省直辖县级行政区划','市辖区')"
        else:
            s="select id,name from gis.adm where level=2 and id_1="+parent_id+\
                " and name not in ('省直辖县级行政区划','市辖区')"
    elif level=='3':
        if is_zx:
            s="select id,name from gis.adm where level=4 and id_3="+parent_id+\
                " and name not in ('市辖区')"
        else:
            s="select id,name from gis.adm where level=3 and id_2="+parent_id+\
                " and name not in ('市辖区')"    
    if s:
        s=s+" order by name"
        cur.execute(s)
        rs=cur.fetchall()
        for r in rs:
            data.append([r[0],r[1]])
    response=json.dumps(data,ensure_ascii=False)
    c=request.GET.get('callback','')
    if c:
        response=c+'('+response+')'
        return HttpResponse(response)
    else:
        return HttpResponse(response,content_type='application/json')








'''
def bugsell_distance(compid_auth_area,lng,lat,code,compid,compid_fr,compid_to,cpid,userid,province,city,add,pkg):    
    
    #2018-11-29:计算扫码地点与授权区域的最短距离 
    #所有参数为字符串型
    
    cn =psycopg2.connect(
        database=gis_db, 
        user=gis_usr, 
        password=gis_pwd, 
        host=gis_host, 
        port=gis_port
    )
    cur=cn.cursor()
    data={'e':'','distance':0}    
    c='compid'
    is_legacy=request.GET.get('is_legacy','1')
    if is_legacy=='0':
        c='company_id'
    
    #2018-11-29:西凤经销商极权区域,定期(年)修改
    s="select adm_id from authorized_area where "+c+"="+compid_auth_area+\
        " and dt_to=(select max(dt_to) from authorized_area where "+c+"="+compid_auth_area+")"
    cur.execute(s)
    rs=cur.fetchall()   
    a=[]
    for r in rs:        
        s="(select st_distance(st_geogfromtext('SRID=4326;POINT("+lng+" "+lat+")'),"+\
            "(select geom from adm where id="+str(r[0])+")))::int"    
        a.append(s)
    if not a:
        data['e']=compid_auth_area+u'未指定授权区域'
    else:                
        s="select LEAST("+",".join(a)+")"
        cur.execute(s)
        r=cur.fetchone()
        d=r[0]
        data['distance']=d
        #写入假货预警表
        s="INSERT INTO bugsell("+\
                "code, "+c+", "+c+"_fr, "+c+"_to, "+c+"_auth_area,"+\
                "cpid,tm,baidu_lnglat, userid,distance,province,city,address,pkg"+\
            ") VALUES ("+\
                "'"+code+"',"+compid+","+compid_fr+","+compid_to+","+compid_auth_area+","+\
                cpid+",now(),'"+lng+","+lat+"',"+userid+","+str(d)+",'"+province+"','"+city+"','"+add+"','"+pkg+"'"+\
            ")"           
        cur.execute(s)
        cn.commit()     
    return data


def bugsell_check(request,code):  
    data={'msg':'','distance':0}    
    compid=request.GET.get('compid','')
    compid_fr=request.GET.get('compid_fr','')
    compid_to=request.GET.get('compid_to','')
    compid_auth_area=request.GET.get('compid_auth_area','')
    userid=request.GET.get('userid','null')
    cpid=request.GET.get('cpid','null')
    lat=request.GET.get('lat','')
    lng=request.GET.get('lng','')
    province=request.GET.get('province','')
    city=request.GET.get('city','')
    add=request.GET.get('address','')
    pkg=request.GET.get('package','')
    baidu_point='null'
    if lat and lng:        
        baidu_point="'"+lng+","+lat+"'"
    if not compid_auth_area or baidu_point=='null':      
        data['msg']=u'缺少经纬度或区域授权公司ID'
    else:
        d=bugsell_distance(compid_auth_area,lng,lat,code,compid,compid_fr,compid_to,cpid,userid,province,city,add,pkg) 
        data['msg']=d['e']
        data['distance']=d['distance'] 
    response=json.dumps(data,ensure_ascii=False)   
    return HttpResponse(response,content_type='application/json') 


def fake_alert(request):  
    data={'e':''}    
    code=request.GET.get('code','')
    ecode=request.GET.get('ecode','')
    if not code and not ecode:
        data['e']=u'标签号码不能为空！'
        response=json.dumps(data,ensure_ascii=False)   
        return HttpResponse(response,content_type='application/json') 
    is_qr3d='true'
    if not ecode:
        is_qr3d='false'
    compid=request.GET.get('compid','0')
    compid_fr=request.GET.get('compid_fr','0')
    compid_to=request.GET.get('compid_to','0')
    compid_auth_area=request.GET.get('compid_auth_area','0')
    userid=request.GET.get('userid','0')
    cpid=request.GET.get('cpid','0')
    lat=request.GET.get('lat','')
    lng=request.GET.get('lng','')
    province=request.GET.get('province','')
    city=request.GET.get('city','')
    add=request.GET.get('address','')
    pkg=request.GET.get('package','')
    baidu_point='null'    
    if lat and lng:        
        baidu_point="'"+lng+","+lat+"'"
    if baidu_point=='null':      
        data['msg']=u'缺少扫码地点经纬度'
    else:
        cn =psycopg2.connect(
            database=gis_db, 
            user=gis_usr, 
            password=gis_pwd, 
            host=gis_host, 
            port=gis_port
        )
        cur=cn.cursor()
        s="INSERT INTO fakelabel("+\
                "code,ecode,compid,compid_fr,compid_to,compid_auth_area,is_qr3d,"+\
                "cpid,baidu_lnglat, userid,province,city,address,pkg"+\
            ") VALUES ('"+\
                code+"','"+ecode+"',"+compid+","+compid_fr+","+compid_to+","+compid_auth_area+","+is_qr3d+","+\
                cpid+",'"+lng+","+lat+"',"+userid+",'"+province+"','"+city+"','"+add+"','"+pkg+"'"+\
            ")"           
        cur.execute(s)
        cn.commit()     
    response=json.dumps(data,ensure_ascii=False)   
    return HttpResponse(response,content_type='application/json') 





def ajax_seller_distance(request,sellerid):
    cn =psycopg2.connect(
        database=gis_db, 
        user=gis_usr, 
        password=gis_pwd, 
        host=gis_host, 
        port=gis_port
    )
    cur=cn.cursor()

    data={'msg':'','distance':0}
    a=[]    
    lat=request.GET.get('lat',0)
    lng=request.GET.get('lng',0)
    if not lat or not lng:
        data['msg']=u'缺少经度或纬度'
    else:
        #2018-11-29:西凤经销商授权区域定期(年)修改
        #s="select adm_id from authorized_area where compid="+sellerid
        s="select adm_id from authorized_area where compid="+sellerid+\
            " and dt_to=(select max(dt_to) from authorized_area where compid="+sellerid+")"

        cur.execute(s)
        rs=cur.fetchall()       
        for r in rs:        
            s="(select st_distance(st_geogfromtext('SRID=4326;POINT("+lng+" "+lat+")'),"+\
                    "(select geom from adm where id="+str(r[0])+")))::int"    
            a.append(s)
        if not a:
                data['msg']=u'经销商未指定授权区域'
        else:                
            s="select LEAST("+",".join(a)+")"
            cur.execute(s)
            r=cur.fetchone()
            data['distance']=r[0]
        

    
    response=json.dumps(data,ensure_ascii=False)
    c=request.GET.get('callback','')
    if c:
        response=c+'('+response+')'
        return HttpResponse(response)
    else:
        return HttpResponse(response,content_type='application/json')




def ajax_bugsell_statistics(request,compid):
    src=request.META.get('HTTP_REFERER', '')
    #if not 'china315net.com' in src:
    #    return HttpResponse(status=403)


    cn =psycopg2.connect(
        database=gis_db, 
        user=gis_usr, 
        password=gis_pwd, 
        host=gis_host, 
        port=gis_port
    )
    cur=cn.cursor()
    reload(sys)
    sys.setdefaultencoding("utf-8") 

    dt=request.GET.get('date',datetime.datetime.now().strftime("%Y-%m-%d")) 
    dt_from=request.GET.get('date_from','')
    dt_to=request.GET.get('date_to','')
    is_simulation=request.GET.get('mode','0')
    if dt_from and dt_to:
        sWhere=" where compid="+compid+" and dt>='"+dt_from+"' and dt<='"+dt_to+"' and compid_to>0"
    else:
        sWhere=" where compid="+compid+" and dt='"+dt+"' and compid_to>0"
    statistics=[]

    tt=[]
    s="select compid_to,count(*),"+\
        "array_to_string(array(select name from adm where id in (select adm_id from authorized_area where compid=compid_to)),',') "+\
        "from bugsell"+sWhere+" group by compid_to"
    cur.execute(s)
    rs=cur.fetchall()
    if rs:
        for r in rs:
            tt.append(
                "("+str(r[0])+","+str(r[1])+",'"+r[2]+"')"        
            )   

        cn=pyodbc.connect(sql_srv)
        cur=cn.cursor()    

        #汇总
        s="CREATE TABLE #t("+\
            "[agt_id] [int] NULL,"+\
            "[cnt] [int] NULL,"+\
            "[area] [varchar](200) NULL,"+\
        ")"
        cur.execute(s) 
        cn.commit()
        s="insert into #t(agt_id,cnt,area) values "+",".join(tt)
        cur.execute(s)
        cn.commit()
        s=u"select "+\
            "(select CompName from TJMarketingSystemYin.dbo.TJ_RegisterCompanys where CompID=agt_id),"+\
            "cnt,agt_id,area from #t"
        cur.execute(s)
        rs=cur.fetchall()
        n=1
        for r in rs:
            if is_simulation=='1':
                statistics.append({
                    'agtid':r[2],
                    'agt':u'经销商'+str(n),
                    'cnt':r[1],
                    'area':r[3]
                })
                n=n+1
            else:
                statistics.append({
                    'agtid':r[2],
                    'agt':r[0],
                    'cnt':r[1],
                    'area':r[3]
                })  
    response=json.dumps(statistics,ensure_ascii=False)
    c=request.GET.get('callback','')
    if c:
        response=c+'('+response+')'
        return HttpResponse(response)
    else:
        return HttpResponse(response,content_type='application/json')



def ajax_bugsell_points(request,compid,agtid=None):
    src=request.META.get('HTTP_REFERER', '')
    #if not 'china315net.com' in src: 
    #    return HttpResponse(status=403)

    data=[]
    cn =psycopg2.connect(
        database=gis_db, 
        user=gis_usr, 
        password=gis_pwd, 
        host=gis_host, 
        port=gis_port
    )
    cur=cn.cursor()
    reload(sys)
    sys.setdefaultencoding("utf-8") 

    dt=request.GET.get('date',datetime.datetime.now().strftime("%Y-%m-%d")) 
    dt_from=request.GET.get('date_from','')
    dt_to=request.GET.get('date_to','')
    if dt_from and dt_to:
        sWhere=" where compid="+compid+" and dt>='"+dt_from+"' and dt<='"+dt_to+"' and compid_to>0"
    else:
        sWhere=" where compid="+compid+" and dt='"+dt+"' and compid_to>0"
        
    sAnd=''
    if agtid:
        sAnd=" and compid_to="+agtid
    is_simulation=request.GET.get('mode','0')

   
    s="select compid_to,COALESCE(cpid,0),code,tm,baidu_lnglat,"+\
        "array_to_string(array(select name from adm where id in (select adm_id from authorized_area where compid=compid_to)),','), "+\
        "COALESCE(address,'未知'),distance from bugsell"+sWhere+sAnd 
    
    cur.execute(s)
    rs=cur.fetchall()
    if rs:
    
   
        temp=[]    
        for r in rs:        
            temp.append(
                "("+str(r[0])+","+str(r[1])+",'"+r[2]+"','"+r[3].strftime("%Y-%m-%d %H:%M:%S")+"','"+r[4].replace("(","[").replace(")","]")+\
                    "','"+r[5]+"','"+r[6]+"',"+str(r[7])+")"
            )

       

        cn=pyodbc.connect(sql_srv)
        cur=cn.cursor()
        #坐标明细
        s="CREATE TABLE #temp("+\
            "[agt_id] [int] NULL,"+\
            "[prd_id] [int] NULL,"+\
	    "[code] [varchar](20) NULL,"+\
	    "[tm] [varchar](50) NULL,"+\
	    "[pos] [varchar](100) NULL,"+\
            "[area] [varchar](200) NULL,"+\
            "[address] [varchar](300) NULL,"+\
            "[distance] [int] NULL"+\
        ")"
        cur.execute(s) 
        cn.commit()
        s="insert into #temp(agt_id,prd_id,code,tm,pos,area,address,distance) values "+",".join(temp)
        
        cur.execute(s)
        cn.commit()
        s=u"select code,tm,pos,area,"+\
            "(select CompName from TJMarketingSystemYin.dbo.TJ_RegisterCompanys where CompID=agt_id),"+\
            "isnull((select Products_Name from TianJianWuLiuWebnew.dbo.TB_Products_Infor where Infor_ID=prd_id),'未知'),"+\
            "agt_id,isnull(address,'未知'),distance from #temp"
        cur.execute(s)
        rs=cur.fetchall()
        
        sn=1
        for r in rs:
            if is_simulation=='1':
                data.append({
                    'code':r[0],
                    'time':r[1],
                    'lnglat':r[2],
                    'area':r[3],
                    'agent':u'经销商'+str(sn),
                    'product':u'产品'+str(sn),
                    'agtid':r[6],
                    'address':r[7],
                    'distance':r[8]
                })
                sn=sn+1
            else:
                data.append({
                    'code':r[0],
                    'time':r[1],
                    'lnglat':r[2],
                    'area':r[3],
                    'agent':r[4],
                    'product':r[5],
                    'agtid':r[6],
                    'address':r[7],
                    'distance':r[8]
                })    

    response=json.dumps(data,ensure_ascii=False)
    c=request.GET.get('callback','')
    if c:
        response=c+'('+response+')'
        return HttpResponse(response)
    else:
        return HttpResponse(response,content_type='application/json')



def ajax_fakelabel_points(request,compid,agtid=None):
    src=request.META.get('HTTP_REFERER', '')
    #if not 'china315net.com' in src: 
    #    return HttpResponse(status=403)

    data=[]
    cn =psycopg2.connect(
        database=gis_db, 
        user=gis_usr, 
        password=gis_pwd, 
        host=gis_host, 
        port=gis_port
    )
    cur=cn.cursor()
    reload(sys)
    sys.setdefaultencoding("utf-8") 

    dt=request.GET.get('date',datetime.datetime.now().strftime("%Y-%m-%d")) 
    dt_from=request.GET.get('date_from','')
    dt_to=request.GET.get('date_to','')
    #if dt_from and dt_to:
    #    sWhere=" where compid="+compid+" and dt>='"+dt_from+"' and dt<='"+dt_to+"' and compid_to>0"
    #else:
    #    sWhere=" where compid="+compid+" and dt='"+dt+"' and compid_to>0"
        
    if dt_from and dt_to:
        sWhere=" where compid="+compid+" and dt>='"+dt_from+"' and dt<='"+dt_to+"'"
    else:
        sWhere=" where compid="+compid+" and dt='"+dt+"'"


    sAnd=''
    if agtid:
        sAnd=" and compid_to="+agtid
    is_simulation=request.GET.get('mode','0')

   
    s="select compid_to,COALESCE(cpid,0),code,tm,baidu_lnglat,"+\
        "array_to_string(array(select name from adm where id in (select adm_id from authorized_area where compid=compid_to)),','), "+\
        "COALESCE(address,'未知'),ecode from fakelabel"+sWhere+sAnd 
    
    cur.execute(s)
    rs=cur.fetchall()
    if rs:
    
   
        temp=[]    
        for r in rs:        
            temp.append(
                "("+str(r[0])+","+str(r[1])+",'"+r[2]+"','"+r[3].strftime("%Y-%m-%d %H:%M:%S")+"','"+r[4].replace("(","[").replace(")","]")+\
                    "','"+r[5]+"','"+r[6]+"','"+r[7]+"')"
            )

       

        cn=pyodbc.connect(sql_srv)
        cur=cn.cursor()
        #坐标明细
        s="CREATE TABLE #temp("+\
            "[agt_id] [int] NULL,"+\
            "[prd_id] [int] NULL,"+\
	    "[code] [varchar](20) NULL,"+\
            "[ecode] [varchar](20) NULL,"+\
	    "[tm] [varchar](50) NULL,"+\
	    "[pos] [varchar](100) NULL,"+\
            "[area] [varchar](200) NULL,"+\
            "[address] [varchar](300) NULL,"+\
            "[distance] [int] NULL"+\
        ")"
        cur.execute(s) 
        cn.commit()
        s="insert into #temp(agt_id,prd_id,code,tm,pos,area,address,ecode) values "+",".join(temp)
        
        cur.execute(s)
        cn.commit()
        s=u"select code,tm,pos,area,"+\
            "(select CompName from TJMarketingSystemYin.dbo.TJ_RegisterCompanys where CompID=agt_id),"+\
            "isnull((select Products_Name from TianJianWuLiuWebnew.dbo.TB_Products_Infor where Infor_ID=prd_id),'未知'),"+\
            "agt_id,isnull(address,'未知'),ecode from #temp"
        cur.execute(s)
        rs=cur.fetchall()
        
        sn=1
        for r in rs:
            if is_simulation=='1':
                data.append({
                    'code':r[0],
                    'time':r[1],
                    'lnglat':r[2],
                    'area':r[3],
                    'agent':u'经销商'+str(sn),
                    'product':u'产品'+str(sn),
                    'agtid':r[6],
                    'address':r[7],
                    'ecode':r[8]
                })
                sn=sn+1
            else:
                data.append({
                    'code':r[0],
                    'time':r[1],
                    'lnglat':r[2],
                    'area':r[3],
                    'agent':r[4],
                    'product':r[5],
                    'agtid':r[6],
                    'address':r[7],
                    'ecode':r[8]
                })    

    response=json.dumps(data,ensure_ascii=False)
    c=request.GET.get('callback','')
    if c:
        response=c+'('+response+')'
        return HttpResponse(response)
    else:
        return HttpResponse(response,content_type='application/json')    




def ajax_fakelabel_statistics(request,compid):
    src=request.META.get('HTTP_REFERER', '')
    #if not 'china315net.com' in src:
    #    return HttpResponse(status=403)


    cn =psycopg2.connect(
        database=gis_db, 
        user=gis_usr, 
        password=gis_pwd, 
        host=gis_host, 
        port=gis_port
    )
    cur=cn.cursor()
    reload(sys)
    sys.setdefaultencoding("utf-8") 

    dt=request.GET.get('date',datetime.datetime.now().strftime("%Y-%m-%d")) 
    dt_from=request.GET.get('date_from','')
    dt_to=request.GET.get('date_to','')
    is_simulation=request.GET.get('mode','0')
    #if dt_from and dt_to:
    #    sWhere=" where compid="+compid+" and dt>='"+dt_from+"' and dt<='"+dt_to+"' and compid_to>0"
    #else:
    #    sWhere=" where compid="+compid+" and dt='"+dt+"' and compid_to>0"

    if dt_from and dt_to:
        sWhere=" where compid="+compid+" and dt>='"+dt_from+"' and dt<='"+dt_to+"'"
    else:
        sWhere=" where compid="+compid+" and dt='"+dt+"'"
    statistics=[]

    tt=[]
    s="select compid_to,count(*),"+\
        "array_to_string(array(select name from adm where id in (select adm_id from authorized_area where compid=compid_to)),',') "+\
        "from fakelabel"+sWhere+" group by compid_to"
    cur.execute(s)
    rs=cur.fetchall()
    if rs:
        for r in rs:
            tt.append(
                "("+str(r[0])+","+str(r[1])+",'"+r[2]+"')"        
            )   

        cn=pyodbc.connect(sql_srv)
        cur=cn.cursor()    

        #汇总
        s="CREATE TABLE #t("+\
            "[agt_id] [int] NULL,"+\
            "[cnt] [int] NULL,"+\
            "[area] [varchar](200) NULL,"+\
        ")"
        cur.execute(s) 
        cn.commit()
        s="insert into #t(agt_id,cnt,area) values "+",".join(tt)
        cur.execute(s)
        cn.commit()
        s=u"select "+\
            "(select CompName from TJMarketingSystemYin.dbo.TJ_RegisterCompanys where CompID=agt_id),"+\
            "cnt,agt_id,area from #t"
        cur.execute(s)
        rs=cur.fetchall()
        n=1
        for r in rs:
            if is_simulation=='1':
                statistics.append({
                    'agtid':r[2],
                    'agt':u'经销商'+str(n),
                    'cnt':r[1],
                    'area':r[3]
                })
                n=n+1
            else:
                statistics.append({
                    'agtid':r[2],
                    'agt':r[0],
                    'cnt':r[1],
                    'area':r[3]
                })  
    response=json.dumps(statistics,ensure_ascii=False)
    c=request.GET.get('callback','')
    if c:
        response=c+'('+response+')'
        return HttpResponse(response)
    else:
        return HttpResponse(response,content_type='application/json')    
'''
