# -*- coding: utf-8 -*-
from django.shortcuts import render
from django.views.decorators.csrf import csrf_exempt
import datetime
import time
import hashlib
import json
import requests
from rest_framework.response import Response
from rest_framework import viewsets
from inventory.models import *
from company.models import *
from django.db import connection
from django.utils import timezone
# Create your views here.
from django.core import serializers
import pyodbc
from django.http import JsonResponse
from django.contrib.auth.decorators import login_required
from datacenter.settings import TY_WULIU_STR
import psycopg2

from django.contrib.auth import authenticate, login, logout

from functools import wraps
from django.contrib.auth import REDIRECT_FIELD_NAME
from urllib.parse import urlparse
from django.shortcuts import resolve_url
from cryptography.fernet import Fernet
from urllib import parse
from django.conf import settings


# 2022-09-27
import calendar

# 2022-10-07
from dateutil.relativedelta import relativedelta


ty_url = "https://top-api.sao.so/"
fmt = 'application/json'
def get_ty_token():
    url = ty_url + "sso-service/isv/oauth/token"
    usr = "userportal@sao.so"
    pwd = "Flzxsqc1234!"
    data = {'status': '', 'err_code': '', 'err_msg': '', 'data': ''}
    d = {'clientName': usr,'clientPassword': pwd}
    d = json.dumps(d)
    r = requests.post(url, data=d, headers={'Content-Type': fmt})
    data['status'] = r.status_code
    if data['status'] == 200:
        data['data'] = json.loads(r.text)
    else:
        d = json.loads(r.text)
        if 'code' in d:
            data['err_code'] = d['code']
        if 'message' in d:
            data['err_msg'] = d['message']
    return data

def ty_agent(method, agt):
    data = {'status': '', 'err_code': '', 'err_msg': ''}
    # 经销商是否已同步过
    cn = psycopg2.connect(TY_WULIU_STR)
    cur = cn.cursor()
    r = get_ty_token()
    if r['status'] != 200:
        data['err_code'] = r['err_code']
        data['err_msg'] = r['err_msg'] + '[获取透云token失败]'
        return data

    token = r['data']['accessToken']
    hdrs = {'Authorization': "Bearer " + token, 'Content-Type': fmt }
    url = ty_url + "data-service/isv/franchisers/" + method + "?companyCode=JGJ"
    if method == 'add':
        m = '添加'
        # 经销商是否已同步过
        if agt.ty_id:
            data['err_msg'] = '此经销商PG_TY内ID已经存在'
            return data
        cur.execute("select id from customer_company where name='"+agt.name+"' and parent_id=7 and parent_compid=99")
        r0 = cur.fetchone()
        if r0:
            ty_id = r0[0]
        else:
            add = 'null'
            if agt.address:
                add = "'" + agt.address + "'"
            cur.execute("insert into customer_company(name,address,type,parent_id,parent_compid) values " + \
                "('" + agt.name + "'," + add + ",47,7,99) returning id" )
            r0 = cur.fetchone()
            ty_id = r0[0]
            cn.commit()
        agt.ty_id = ty_id
        agt.save()
        # 同步到透云
        agt_data = [{'code': ty_id, 'name': agt.name}]
        d = json.dumps(agt_data)
        r = requests.post(url, data= d, headers=hdrs)
    elif method == 'edit':
        m = '修改'
        if not agt.ty_id:
            cur.execute("select id from customer_company where name='" + agt.name + "' and parent_id=7 and parent_compid=99")
            r0 = cur.fetchone()
            if r0:
                ty_id = r0[0]
            else:
                add = 'null'
                if agt.address:
                    add = "'" + agt.address + "'"
                cur.execute("insert into customer_company(name,address,type,parent_id,parent_compid) values " + \
                            "('" + agt.name + "'," + add + ",47,7,99) returning id")
                r0 = cur.fetchone()
                ty_id = r0[0]
                cn.commit()
                url = ty_url + "data-service/isv/franchisers/add?companyCode=JGJ"
            agt.ty_id = ty_id
            agt.save()
        else:
            cur.execute("update customer_company set name='"+agt.name+"' where id="+str(agt.ty_id))
            cn.commit()
        agt_data = [{'code': agt.ty_id, 'name': agt.name}]
        d = json.dumps(agt_data)
        r = requests.put(url, data=d, headers=hdrs)
    else:
        data['err_msg'] = '经销商只能做修改或添加操作'
        return data
    data['status'] = r.status_code
    if data['status'] not in [200, 201]:
        d = json.loads(r.text)
        if 'code' in d:
            data['err_code'] = d['code']
        if 'message' in d:
            data['err_msg'] = d['message'] + "[向透云" + m + "经销商失败]"
    return data


'''
import collections
class MyModelSerializer(serializers.ModelSerializer):

    def to_representation(self, instance):
    ret = collections.OrderedDict()
        fields = self._readable_fields
        for field in fields:
            try:
                attribute = field.get_attribute(instance)
            except SkipField:
                continue

            # We skip `to_representation` for `None` values so that fields do
            # not have to explicitly deal with that case.
            #
            # For related fields with `use_pk_only_optimization` we need to
            # resolve the pk value.
            check_for_none = attribute.pk if isinstance(attribute, PKOnlyObject) else attribute
            if check_for_none is None:
                value = None
            else:
                value = field.to_representation(attribute)

            ret[field.field_name] = {
                'value': value,
                # You can find more field attributes here
                # https://github.com/encode/django-rest-framework/blob/master/rest_framework/fields.py#L324
                'verbose_name': field.label,
                'read_only': field.read_only,
                'write_only': field.write_only,
                'help_text': field.help_text,
            }
'''


class TjViewSet(viewsets.ModelViewSet):
    def list(self, request, *args, **kwargs):
        queryset = self.queryset
        pg = self.paginate_queryset(queryset)
        serializer = self.get_serializer(pg, many=True)
        data = self.get_paginated_response(serializer.data).data
        m = self.serializer_class.Meta.model

        #serializer = self.get_serializer(pg, many=True)
        #fields = self.serializer_class.Meta.fields
        #fields = self.serializer_class.fields
        #print(self.serializer_class._get_declared_fields())
        #for field in fields:
        #    print(field)

        #print(fields)
        #for f in fields:
        #    print(f)
        data['test'] = 'test'
        return Response(data)






'''
class TJViewSet(viewsets.ModelViewSet):
    def list(self, request, *args, **kwargs):
        queryset = self.queryset
        pg = self.paginate_queryset(queryset)
        #serializer = self.get_serializer(pg, many=True)
        serializer = self.serializer_class
        if pg is not None:
            #fields = serializer._readable_fields
            fields = self.serializer_class.fields
            # print(serializer._get_declared_fields())
            for field in fields:
                try:
                    attribute = field.get_attribute(instance)
                except SkipField:
                    continue

                # We skip `to_representation` for `None` values so that fields do
                # not have to explicitly deal with that case.
                #
                # For related fields with `use_pk_only_optimization` we need to
                # resolve the pk value.
                check_for_none = attribute.pk if isinstance(attribute, PKOnlyObject) else attribute
                if check_for_none is None:
                    value = None
                else:
                    value = field.to_representation(attribute)

                #ret[field.field_name] = {
                #    'value': value,
                #    # You can find more field attributes here
                #    # https://github.com/encode/django-rest-framework/blob/master/rest_framework/fields.py#L324
                #    'verbose_name': field.label,
                #    'read_only': field.read_only,
                #    'write_only': field.write_only,
                #    'help_text': field.help_text,
                #}

                data = self.get_paginated_response(serializer.data).data
                data.update({
                    #'value': value,
                    # You can find more field attributes here
                    # https://github.com/encode/django-rest-framework/blob/master/rest_framework/fields.py#L324
                    'label': field.label,
                    'read_only': field.read_only,
                    'write_only': field.write_only,
                    'help_text': field.help_text,
                })
                return Response(data)

            serializer = self.get_serializer(queryset, many=True)
            return Response(serializer.data)



        
        queryset = self.queryset
        pg = self.paginate_queryset(queryset)
        if pg is not None:
            serializer = self.get_serializer(pg, many=True)
            m = self.get_serializer_class().Meta.model
            fields = []
            if hasattr(self.get_serializer_class().Meta, 'exclude'):
                for f in m._meta.fields:
                    if f.name not in self.get_serializer_class().Meta.exclude:
                        fields.append({
                            'name': f.name,
                            'label': f.verbose_name,
                            'type': type(f).__name__
                        })
            else:
                fs = self.get_serializer_class().Meta.fields
                if fs == '__all__':
                    for f in m._meta.fields:
                        fields.append({
                            'name': f.name,
                            'label': f.verbose_name,
                            'type': type(f).__name__
                        })
                else:
                    for f in m._meta.fields:
                        if f.name in fs:
                            fields.append({
                                'name': f.name,
                                'label': f.verbose_name,
                                'type': type(f).__name__
                            })         
'''


def is_valid_date(s):
    """判断是否是一个有效的日期字符串"""
    try:
        time.strptime(s, "%Y-%m-%d %H:%M:%S")
        return True
    except:
        return False


@csrf_exempt
def time_key_check(request, sK):
    tm = None
    key = None
    if request.method == 'GET':
        tm = request.GET.get('time', '')
        if not tm:
            tm = request.GET.get('TIME', '')
        key = request.GET.get('key', '')
        if not key:
            key = request.GET.get('KEY', '')
    else:
        if 'json' in request.META['CONTENT_TYPE']:
            r = json.loads(request.body)
            #2020-09-07
            #r = json.loads(request.body.decode('utf-8'))
            if 'time' in r:
                tm = r['time']
            if 'TIME' in r:
                tm = r['TIME']
            if 'key' in r:
                key = r['key']
            if 'KEY' in r:
                key = r['KEY']
        else:
            tm = request.POST.get('time', '')
            if not tm:
                tm = request.POST.get('TIME', '')
            key = request.POST.get('key', '')
            if not key:
                key = request.POST.get('KEY', '')
    e = []
    if not tm:
        e.append('缺少时间戳')
    else:
        if not is_valid_date(tm):
            e.append('时间戳格式不正确')
    if not key:
        e.append('非法调用')
    if e:        
        return ','.join(e)

    t0 = datetime.datetime.now()
    t1 = datetime.datetime.strptime(tm, '%Y-%m-%d %H:%M:%S')
    if abs((t1-t0).total_seconds()) > 3600:
        e.append('无效时间戳')        
        return ','.join(e)         
     
    m2 = hashlib.md5()
    # m2.update(s.encode(encoding='UTF-8'))
    m2.update((sK+tm).encode(encoding='UTF-8'))
    k = m2.hexdigest()
    if k != key:
        e.append('非法调用')        
        return ','.join(e)
    return e


'''
class TjModelSerializer(serializers.ModelSerializer):
    def to_representation(self, instance):
        """Object instance -> Dict of primitive datatypes."""
        ret = collections.OrderedDict()
        fields = self._readable_fields

        for field in fields:
            try:
                attribute = field.get_attribute(instance)
            except SkipField:
                continue

            # We skip `to_representation` for `None` values so that fields do
            # not have to explicitly deal with that case.
            #
            # For related fields with `use_pk_only_optimization` we need to
            # resolve the pk value.
            check_for_none = attribute.pk if isinstance(attribute, PKOnlyObject) else attribute
            if check_for_none is None:
                value = None
            else:
                value = field.to_representation(attribute)

            ret[field.field_name] = {
                'value': value,
                # You can find more field attributes here
                # https://github.com/encode/django-rest-framework/blob/master/rest_framework/fields.py#L324
                'label': field.label,
                # 'read_only': field.read_only,
                # 'write_only': field.write_only,
                # 'help_text': field.help_text,
            }

        return ret
'''

@login_required
def migrate_sql_pg(request, db,tb):
    data = {'e':'', 'cnt':0, 'tm1':'', 'tm2':'', 'tm':0}
    tb_pg = tb.lower()
    tm1 = datetime.datetime.now()
    dbs = {
        'TianJianWuLiuWebnew':'logistics',
        'TJMarketingSystemYin':'marketing'
    }
    db_pg = dbs[db]
    f_type = {
        'varchar': 'character varying',
        'nchar': 'character varying',
        'nvarchar': 'character varying',
        'text': 'text',
        'smallint': 'smallint',
        'int': 'integer',
        'bigint': 'bigint',
        'datetime': 'timestamp with time zone',
        'smalldatetime': 'timestamp with time zone',
        'date': 'date',
        'bit': 'boolean',
        'money': 'numeric',
        'numeric': 'numeric',
        'decimal': 'numeric',
        'real': 'numeric',
        'varbinary': 'bit',
    }
    c_lens = ['character varying', 'bit']
    n_lens = ['numeric', 'decimal']
    s = 'DRIVER={SQL Server};SERVER=117.34.70.23,33419;DATABASE=' + db + ';UID=sa;PWD=tianjian2016'
    cn = pyodbc.connect(s)
    cur_sql = cn.cursor()
    cur_pg = connection.cursor()
    # SQL SRV表是否存在
    cur_sql.execute("select name from sys.objects  where  name='" + tb + "'")
    r = cur_sql.fetchone()
    if not r:
        data['e']="SQL Server表 " + db + ".dbo." + tb + " 不存在"
        return JsonResponse(data)

    s = "SELECT " + \
        "TableName=CASE WHEN C.column_id=1 THEN O.name ELSE '' END, " + \
        "ColumnName=C.name, " + \
        "is_id=CASE WHEN C.is_identity=1 THEN '1' ELSE '0' END, " + \
        "is_pk=ISNULL(IDX.PrimaryKey,'0'), " + \
        "Type=T.name, " + \
        "Length=C.max_length, " + \
        "Scale=C.scale, " + \
        "[Default]=ISNULL(D.definition,''), " + \
        "IndexName=ISNULL(IDX.IndexName,''), " + \
        "Precision=C.precision, " + \
        "NullAble=CASE WHEN C.is_nullable=1 THEN '1' ELSE '0' END, " + \
        "IndexSort=ISNULL(IDX.Sort,''), " + \
        "Column_id=C.column_id, " + \
        "Computed=CASE WHEN C.is_computed=1 THEN '1' ELSE '0' END, " + \
        "Create_Date=O.Create_Date, " + \
        "Modify_Date=O.Modify_date " + \
        "FROM sys.columns C " + \
        "INNER JOIN (SELECT * FROM sys.objects WHERE name='" + tb + "') O " + \
        "ON C.[object_id]=O.[object_id] AND O.type='U' AND O.is_ms_shipped=0 " + \
        "INNER JOIN sys.types T " + \
        "ON C.user_type_id=T.user_type_id " + \
        "LEFT JOIN sys.default_constraints D " + \
        "ON C.object_id=D.parent_object_id AND C.column_id=D.parent_column_id AND C.default_object_id=D.object_id " + \
        "LEFT JOIN  " + \
        "(SELECT IDXC.[object_id],IDXC.column_id,Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id]," + \
            "IDXC.index_id,IDXC.index_column_id,'IsDescending') " + \
            "WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, " + \
        "PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN '1' ELSE '0' END, " + \
        "IndexName=IDX.Name " + \
        "FROM sys.indexes IDX " + \
        "INNER JOIN sys.index_columns IDXC " + \
        "ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id " + \
        "LEFT JOIN sys.key_constraints KC " + \
        "ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id " + \
        "INNER JOIN  " + \
        "(SELECT [object_id], Column_id, index_id=MIN(index_id) FROM sys.index_columns " +\
            "GROUP BY [object_id], Column_id ) IDXCUQ " + \
        "ON IDXC.[object_id]=IDXCUQ.[object_id] AND IDXC.Column_id=IDXCUQ.Column_id " + \
            "AND IDXC.index_id=IDXCUQ.index_id ) IDX " + \
        "ON C.[object_id]=IDX.[object_id] AND C.column_id=IDX.column_id " + \
        "ORDER BY O.name,C.column_id"
    cur_sql.execute(s)
    cur_pg.execute("select to_regclass('" + db_pg + "." + tb_pg + "')")
    r = cur_pg.fetchone()
    # 如果表已存在，删除表
    if r[0]:
        cur_pg.execute("drop table " + db_pg + "." + tb_pg + "  cascade")
    fields = []
    idxs = []
    fields_sql = []
    fields_pg = []
    fld_increment = None
    for r in  cur_sql.fetchall():
        fname = r[1].lower()
        if fname == 'ctid':
            fname = 'ctyid'
        f = {
            "name_pg": fname,
            "name_sql": r[1],
            "is_id": r[2],
            "is_pk": r[3],
            "type": r[4],
            "length": r[5],
            "decimal": r[6],
            "default": r[7]
        }
        fld =  fname + " " + f_type[r[4]]
        if r[2] == '1':
            fld_increment = fname
        # 字符串长度
        if f_type[f["type"]] in c_lens:
            fld = fld + "(" + str(f["length"]) + ")"
        # 小数位
        elif f_type[f["type"]] in n_lens:
            fld = fld + "(" + str(f["length"]) + "," + str(f["decimal"]) + ")"
        # 是否为主键
        if f['is_id'] == '1':
            fld = fld + " not null primary key"
            # 默认值
        if f["default"]:
            if f["default"] == '((0))':
                if f_type[f["type"]] == 'boolean':
                    fld = fld + " default false"
                else:
                    fld = fld + " default 0"
            elif f["default"] == '((1))':
                if f_type[f["type"]] == 'boolean':
                    fld = fld + " default true"
                else:
                    fld = fld + " default 1"
            elif f["default"] == '(getdate())':
                fld = fld + " default now()"
            elif f["default"] == '(datepart(hour,getdate()))':
                fld = fld + " default extract(hour from now())"
            elif f["default"] == '(datepart(month,getdate()))':
                fld = fld + " default extract(month from now())"
            elif f["default"] == '(datepart(year,getdate()))':
                fld = fld + " default extract(year from now())"
            elif f["default"] == "('photo')":
                fld = fld + " default 'photo'"
            elif f["default"] == "((10))":
                fld = fld + " default 10"
        fields.append(fld)
        if r[8]:
            idxs.append("CREATE INDEX " + tb_pg + "_" + fname + "_idx ON " + db_pg + "." + tb_pg + "(" + fname + ")")
        t = f['type']
        if t in ['int', 'bigint', 'smallint', 'money', 'numeric', 'decimal', 'real']:
            fields_sql.append("cast(isnull(" + f['name_sql'] + ",0) as varchar(50))")
        elif t in ['varchar', 'nchar', 'text', 'nvarchar']:
            if t == 'text':
                fields_sql.append("case when " + f['name_sql'] + " is null then 'null' else " + \
                                  "''''+replace(cast(" + f['name_sql'] + " as varchar(2000)),'''','''''')+'''' end"
                                  )
            else:
                fields_sql.append("case when " + f['name_sql'] + " is null then 'null' else " + \
                                  "''''+replace(" + f['name_sql'] + ",'''','''''')+'''' end"
                                  )
        elif t in ['datetime', 'date', 'smalldatetime']:
            fields_sql.append("case when " + f['name_sql'] + " is null then 'null' else " + \
                              "''''+CONVERT(varchar(100)," + f['name_sql'] + ", 120)+'''' end"
                              )
        elif t in ['bit', 'varbinary']:
            fields_sql.append("case " + f['name_sql'] + \
                              " when 0 then '''false''' " + \
                              " when 1 then '''true''' " + \
                              " else 'null' " + \
                              " end")
        fields_pg.append(fname)
    sql = 'create table ' + db_pg + '.' + tb_pg + '(' + ','.join(fields) + ')'
    cur_pg.execute(sql)

    # 添加索引
    if idxs:
        cur_pg.execute(";".join(idxs))

    # 拷贝数据
    cur_pg.execute("truncate table " + db_pg + "." + tb_pg)
    cnt = 0
    sz = 1000
    s = "select " + ",".join(fields_sql) + " from " + tb
    cur_sql.execute(s)
    result = cur_sql.fetchmany(sz)
    while result:
        vals = []
        for r in result:
            i = 0
            vs = []
            for t in fields_sql:
                vs.append(r[i])
                i = i + 1
            vals.append("(" + ",".join(vs) + ")")
        if vals:
            sql = "insert into " + db_pg + "." + tb_pg + " (" + ",".join(fields_pg) + ") values " + ",".join(vals)
            cur_pg.execute(sql)
            cnt = cnt + cur_pg.rowcount
        result = cur_sql.fetchmany(sz)
    data['cnt'] = cnt

    # 设置自增ID
    if fld_increment:
        s = "select max(" + fld_increment + ") from " + db_pg + "." + tb_pg
        cur_pg.execute(s)
        r = cur_pg.fetchone()
        if r:
            if r[0]:
                n = r[0]
            else:
                n = 1
        else:
            n = 1
        s = "ALTER TABLE " + db_pg + "." + tb_pg + " ALTER COLUMN " + fld_increment + " SET DEFAULT null;" + \
            "DROP SEQUENCE IF EXISTS " + db_pg + "." + tb_pg + "_" + fld_increment + "_seq"
        cur_pg.execute(s)
        s = "CREATE SEQUENCE " + db_pg + "." + tb_pg + "_" + fld_increment + "_seq" + \
            " START WITH " + str(n+1)
        cur_pg.execute(s)
        s = "alter table " + db_pg + "." + tb_pg + " alter column " + fld_increment + \
            "  set default nextval('" + db_pg + "." + tb_pg + "_" + fld_increment + "_seq'::regclass)"
        cur_pg.execute(s)

    cur_sql.close()
    cur_pg.close()
    cn.close()

    tm2 = datetime.datetime.now()
    data['tm1'] = tm1.strftime('%Y-%m-%d %H:%M:%S')
    data['tm2'] = tm2.strftime('%Y-%m-%d %H:%M:%S')
    data['tm'] = round((tm2-tm1).total_seconds(),2)
    return JsonResponse(data)


# 根据传入的密码，做MD5后返回
def md5_password(pwd):
    m2 = hashlib.md5()
    m2.update(pwd.encode(encoding='UTF-8'))
    pwd = m2.hexdigest()
    return pwd


def user_passes_test(test_func, login_url=None, redirect_field_name=REDIRECT_FIELD_NAME):
    """
    Decorator for views that checks that the user passes the given test,
    redirecting to the log-in page if necessary. The test should be a callable
    that takes the user object and returns True if the user passes.
    """
    def decorator(view_func):
        @wraps(view_func)
        def _wrapped_view(request, *args, **kwargs):
            # 2019-08-02
            os_user = None
            uid = request.COOKIES.get('user_id', '')
            if uid:
                #uid=urllib.unquote(uid).decode('utf-8', 'replace').encode('gbk', 'replace')
                uid=parse.unquote(uid)
                if not uid.isdigit():
                    uid=decrypt_p(uid)
                users = User.objects.filter(id=int(uid))
                if users:
                    os_user = users[0]
                    login(request, os_user)

            #if test_func(request.user):
            if test_func(request.user) or os_user:
                return view_func(request, *args, **kwargs)
            path = request.build_absolute_uri()
            resolved_login_url = resolve_url(login_url or settings.LOGIN_URL)
            # If the login url is the same scheme and net location then just
            # use the path as the "next" url.
            login_scheme, login_netloc = urlparse(resolved_login_url)[:2]
            current_scheme, current_netloc = urlparse(path)[:2]
            if ((not login_scheme or login_scheme == current_scheme) and
                    (not login_netloc or login_netloc == current_netloc)):
                path = request.get_full_path()
            from django.contrib.auth.views import redirect_to_login
            return redirect_to_login(
                path, resolved_login_url, redirect_field_name)
        return _wrapped_view
    return decorator


def tj_login_required(function=None, redirect_field_name=REDIRECT_FIELD_NAME, login_url=None):
    """
    Decorator for views that checks that the user is logged in, redirecting
    to the log-in page if necessary.
    """

    actual_decorator = user_passes_test(
        lambda u: u.is_authenticated,
        login_url=login_url,
        redirect_field_name=redirect_field_name
    )
    if function:
        return actual_decorator(function)
    return actual_decorator


def encrypt_p(password):
    f = Fernet('Ow2Qd11KeZS_ahNOMicpWUr3nu3RjOUYa0_GEuMDlOc=')
    p1 = password.encode()
    token = f.encrypt(p1)
    p2 = token.decode()
    return p2

def decrypt_p(password):
    f = Fernet('Ow2Qd11KeZS_ahNOMicpWUr3nu3RjOUYa0_GEuMDlOc=')
    p1 = password.encode()
    token = f.decrypt(p1)
    p2 = token.decode()
    return p2




'''
@csrf_exempt
def send_sms(request):     
    phone=request.POST.get('phone','')
    msg=request.POST.get('msg','')
    sign=request.POST.get('sign','天鉴防伪')
    url='http://106.ihuyi.cn/webservice/sms.php?method=Submit'    
    sms={
        'account':'C75540914',
        'password':'74e762dae2ad1925a27e060bf19fdaad',
        
        #'password':'xyz',
        'sign':sign,
        'content':msg,
        'mobile':phone        
    }

        
    post_sms = urllib.urlencode(sms)

    response = None
    r=0
    try:
        response = urllib2.urlopen(url, post_sms)
    except urllib2.URLError as e:
        if hasattr(e, 'code'):
            r=e.code
        elif hasattr(e, 'reason'):
            r=e.reason
    finally:
        if response:
            response.close()

    return HttpResponse(r)
'''

#人民币大写
class cnumber:
    cdict = {}
    gdict = {}
    xdict = {}

    def __init__(self):
        self.cdict = {1: u'', 2: u'拾', 3: u'佰', 4: u'仟'}
        self.xdict = {1: u'元', 2: u'万', 3: u'亿', 4: u'兆'}  # 数字标识符
        self.gdict = {0: u'零', 1: u'壹', 2: u'贰', 3: u'叁', 4: u'肆', 5: u'伍', 6: u'陆', 7: u'柒', 8: u'捌', 9: u'玖'}

    def csplit(self, cdata):  # 拆分函数，将整数字符串拆分成[亿，万，仟]的list
        g = len(cdata) % 4
        csdata = []
        lx = len(cdata) - 1
        if g > 0:
            csdata.append(cdata[0:g])
        k = g
        while k <= lx:
            csdata.append(cdata[k:k + 4])
            k += 4
        return csdata

    def cschange(self, cki):  # 对[亿，万，仟]的list中每个字符串分组进行大写化再合并
        lenki = len(cki)
        i = 0
        lk = lenki
        chk = u''
        for i in range(lenki):
            if int(cki[i]) == 0:
                if i < lenki - 1:
                    if int(cki[i + 1]) != 0:
                        chk = chk + self.gdict[int(cki[i])]
            else:
                chk = chk + self.gdict[int(cki[i])] + self.cdict[lk]
            lk -= 1
        return chk

    def cwchange(self, data, dw=True):
        cdata = str(data).split('.')
        cki = cdata[0]
        if len(cdata) == 1:
            i = 0
            chk = u''
            cski = self.csplit(cki)  # 分解字符数组[亿，万，仟]三组List:['0000','0000','0000']
            ikl = len(cski)  # 获取拆分后的List长度
            # 大写合并
            for i in range(ikl):
                if self.cschange(cski[i]) == '':  # 有可能一个字符串全是0的情况
                    chk = chk + self.cschange(cski[i])  # 此时不需要将数字标识符引入
                else:
                    chk = chk + self.cschange(cski[i]) + self.xdict[ikl - i]  # 合并：前字符串大写+当前字符串大写+标识符
            if dw:
                chk = chk + u'整'
        else:
            i = 0
            chk = u''
            cski = self.csplit(cki)  # 分解字符数组[亿，万，仟]三组List:['0000','0000','0000']
            ikl = len(cski)  # 获取拆分后的List长度
            # 大写合并
            for i in range(ikl):
                if self.cschange(cski[i]) == '':  # 有可能一个字符串全是0的情况
                    chk = chk + self.cschange(cski[i])  # 此时不需要将数字标识符引入
                else:
                    chk = chk + self.cschange(cski[i]) + self.xdict[ikl - i]  # 合并：前字符串大写+当前字符串大写+标识符
            # 处理小数部分
            ckj = cdata[1]
            lenkj = len(ckj)
            if dw:
                if lenkj == 1:  # 若小数只有1位
                    if int(ckj[0]) == 0:
                        chk = chk + u'整'
                    else:
                        chk = chk + self.gdict[int(ckj[0])] + u'角整'
                else:  # 若小数有两位的四种情况
                    if int(ckj[0]) == 0 and int(ckj[1]) != 0:
                        chk = chk + u'零' + self.gdict[int(ckj[1])] + u'分'
                    elif int(ckj[0]) == 0 and int(ckj[1]) == 0:
                        chk = chk + u'整'
                    elif int(ckj[0]) != 0 and int(ckj[1]) != 0:
                        chk = chk + self.gdict[int(ckj[0])] + u'角' + self.gdict[int(ckj[1])] + u'分'
                    else:
                        chk = chk + self.gdict[int(ckj[0])] + u'角整'
        if not dw:
            chk=chk.replace(u'元','')
        return chk


def send_sms(phone,msg,sign=None):
    if not sign:
        sign='天鉴防伪'
    url='http://106.ihuyi.cn/webservice/sms.php?method=Submit'    
    sms={
        'account':'C75540914',
        'password':'74e762dae2ad1925a27e060bf19fdaad',      
        'sign':sign,
        'content':msg,
        'mobile':phone       
    }  
    headers = {"Content-type": "application/x-www-form-urlencoded", "Accept": "text/plain"}
    r=requests.post(url,sms,headers=headers)  
    
    return r.status_code   
    
  

#2021-07-26
@login_required
def get_baidu_address(request):
    d={'e':'','data':{}}
    lng=request.GET.get('lng','')
    lat=request.GET.get('lat','')
    if not lng or not lat:
        d['e']='lng or lat missing'
        return JsonResponse(d)
    #url="https://api.map.baidu.com/geocoder/v2/?coordtype=wgs84ll&location="+\
    #    str(lat)+ ","+str(lng)+"&output=json&ak=ASl2xmUaYpjwdWy3GPBQbpUK";
    
    url="https://api.map.baidu.com/geocoder/v2/?location="+\
        str(lat)+ ","+str(lng)+"&output=json&ak=ASl2xmUaYpjwdWy3GPBQbpUK";

   
    #url="https://api.map.baidu.com/reverse_geocoding/v3/?location="+\
    #    str(lat)+ ","+str(lng)+"&output=json&ak=ASl2xmUaYpjwdWy3GPBQbpUK";

    r=requests.get(url)
    try:
        result=json.loads(r.text)

       

        
        d['data']=result
    except Exception:
        d['e']='baidu api error'
    return JsonResponse(d)
    
    



def get_package_name(ancestor_id,suffix=''):
    package_3 = '箱'
    package_2 = '盒'
    package_1 = '瓶'
    rs = Nuit.objects.filter(company_id=ancestor_id)
    for r in rs:
        if r.package_id == 3:
            package_3 = r.name
        elif r.package_id == 2:
            package_2 = r.name
        elif r.package_id == 1:
            package_1 = r.name
    return [package_1+suffix,package_2+suffix,package_3+suffix]    




# 2022-10-28
def stkdetails_init(company_id):
    # 为提高反应(查询)速度，对stkdetails 按企业ID分表，继承于：, 表名都定义为  c+company_id
    cid=str(company_id)     
    ts='stkdetails'    
    cur=connection.cursor()    
    tb='c'+cid    
    s="select count(*) from information_schema.tables where table_schema='"+ts+"' and table_type='BASE TABLE' and table_name='"+tb+"'"     
    cur.execute(s)
    r=cur.fetchone()
    if not r[0]:
        s="CREATE TABLE "+ts+"."+tb+"("+\
                "CONSTRAINT "+tb+"_pkey PRIMARY KEY(id),"+\
                "CONSTRAINT ancestor_check"+tb+" CHECK (ancestor_id="+cid+"),"+\
                "CONSTRAINT stkdetails_batch_id_"+tb+" FOREIGN KEY (batch_id) "+\
                    "REFERENCES logistics.inventory_batch (id) MATCH SIMPLE "+\
                    "ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,"+\
                "CONSTRAINT stkdetails_company_id_"+tb+" FOREIGN KEY (company_id) "+\
                    "REFERENCES logistics.company_company (id) MATCH SIMPLE "+\
                    "ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,"+\
                "CONSTRAINT stkdetails_company_to_id_"+tb+" FOREIGN KEY (company_to_id) "+\
                    "REFERENCES logistics.company_company (id) MATCH SIMPLE "+\
                    "ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED, "+\
                "CONSTRAINT stkdetails_product_id_"+tb+" FOREIGN KEY (product_id) "+\
                    "REFERENCES logistics.product_product (id) MATCH SIMPLE "+\
                    "ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED, "+\
                "CONSTRAINT stkdetails_store_fr_id_"+tb+" FOREIGN KEY (store_fr_id) "+\
                    "REFERENCES logistics.inventory_storehouse (id) MATCH SIMPLE "+\
                    "ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED, "+\
                "CONSTRAINT stkdetails_store_id_"+tb+" FOREIGN KEY (store_id) "+\
                    "REFERENCES logistics.inventory_storehouse (id) MATCH SIMPLE "+\
                    "ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED, "+\
                "CONSTRAINT stkdetails_store_to_id_"+tb+" FOREIGN KEY (store_to_id) "+\
                    "REFERENCES logistics.inventory_storehouse (id) MATCH SIMPLE "+\
                    "ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED"+\
            ") INHERITS (logistics.inventory_stkdetails); "+\
            "CREATE INDEX "+tb+"_batch_id_idx ON        "+ts+"."+tb+" USING btree (batch_id);"+\
            "CREATE INDEX "+tb+"_company_id_idx ON      "+ts+"."+tb+" USING btree (company_id);"+\
            "CREATE INDEX "+tb+"_company_to_id_idx ON   "+ts+"."+tb+" USING btree (company_to_id);"+\
            "CREATE INDEX "+tb+"_parent_id_idx ON       "+ts+"."+tb+" USING btree (parent_id);"+\
            "CREATE INDEX "+tb+"_ancestor_id_idx ON     "+ts+"."+tb+" USING btree (ancestor_id);"+\
            "CREATE INDEX "+tb+"_store_fr_id_idx ON     "+ts+"."+tb+" USING btree (store_fr_id);"+\
            "CREATE INDEX "+tb+"_store_to_id_idx ON     "+ts+"."+tb+" USING btree (store_to_id);"+\
            "CREATE INDEX "+tb+"_store_id_idx ON        "+ts+"."+tb+" USING btree (store_id);"+\
            "CREATE INDEX "+tb+"_stack_id_idx ON        "+ts+"."+tb+" USING btree (stack_id);"+\
            "CREATE INDEX "+tb+"_product_idx ON         "+ts+"."+tb+" USING btree (product_id);"+\
            "CREATE INDEX "+tb+"_dt_idx ON              "+ts+"."+tb+" USING btree (dt);"+\
            "CREATE INDEX "+tb+"_is_delete_idx ON       "+ts+"."+tb+" USING btree (is_delete);"+\
            "CREATE INDEX "+tb+"_vtype_idx ON           "+ts+"."+tb+" USING btree (vtype_id);"+\
            "CREATE INDEX "+tb+"_label_code_idx ON      "+ts+"."+tb+" USING btree (label_code);"+\
            "CREATE INDEX "+tb+"_label_pk_idx ON        "+ts+"."+tb+" USING btree (label_pk_id);"+\
            "CREATE INDEX "+tb+"_label_id_idx ON        "+ts+"."+tb+" USING btree (label_id);"+\
            "CREATE INDEX "+tb+"_label_old_idx ON       "+ts+"."+tb+" USING btree (label_old);"+\
            "CREATE INDEX "+tb+"_label_pkg_idx ON       "+ts+"."+tb+" USING btree (label_pkg);"
        cur.execute(s)
    cur.close()





# 2022-09-23
def batchlot_init(company_id):
    # 对于启用产品批次(产品保质期)管理的企业，生成该企业有关批次管理的两个表，继承于：batchlot.batchlot,stock.stock, 表名都定义为  c+company_id
    cid=str(company_id)     
    tb='c'+cid
    cur=connection.cursor()    
    #batchlot 
    ts='batchlot'
    s="select count(*) from information_schema.tables where table_schema='batchlot' and table_type='BASE TABLE' and table_name='"+tb+"'"     
    cur.execute(s)
    r=cur.fetchone()
    if not r[0]:
        s="CREATE TABLE "+ts+"."+tb+"("+\
                "CONSTRAINT "+tb+"_id_pk PRIMARY KEY (id)"+\
            ") INHERITS ("+ts+"."+ts+"); "+\
            "CREATE INDEX "+tb+"_batch_idx ON       "+ts+"."+tb+" USING btree (batch_no);"+\
            "CREATE INDEX "+tb+"_product_idx ON     "+ts+"."+tb+" USING btree (product_id);"+\
            "CREATE INDEX "+tb+"_dt_idx ON          "+ts+"."+tb+" USING btree (dt);"+\
            "CREATE INDEX "+tb+"_vtype_idx ON       "+ts+"."+tb+" USING btree (vtype_id);"+\
            "CREATE INDEX "+tb+"_label_code_idx ON  "+ts+"."+tb+" USING btree (label_code);"+\
            "CREATE INDEX "+tb+"_label_pk_idx ON    "+ts+"."+tb+" USING btree (label_pk_id);"+\
            "CREATE INDEX "+tb+"_label_id_idx ON    "+ts+"."+tb+" USING btree (label_id);"+\
            "CREATE INDEX "+tb+"_label_pkg_idx ON   "+ts+"."+tb+" USING btree (label_pkg);"        
        cur.execute(s)
    #stock 
    ts='stock'
    s="select count(*) from information_schema.tables where table_schema='stock' and table_type='BASE TABLE' and table_name='"+tb+"'"     
    cur.execute(s)
    r=cur.fetchone()
    if not r[0]:
        s="CREATE TABLE "+ts+"."+tb+"("+\
                "CONSTRAINT "+tb+"_id_pk PRIMARY KEY (id)"+\
            ") INHERITS ("+ts+"."+ts+"); "+\
            "CREATE INDEX "+tb+"_product_idx ON     "+ts+"."+tb+" USING btree (product_id);"+\
            "CREATE INDEX "+tb+"_dt_idx ON          "+ts+"."+tb+" USING btree (dt);"+\
            "CREATE INDEX "+tb+"_label_code_idx ON  "+ts+"."+tb+" USING btree (label_code);"+\
            "CREATE INDEX "+tb+"_label_pk_idx ON    "+ts+"."+tb+" USING btree (label_pk_id);"+\
            "CREATE INDEX "+tb+"_label_id_idx ON    "+ts+"."+tb+" USING btree (label_id);"+\
            "CREATE INDEX "+tb+"_label_pkg_idx ON   "+ts+"."+tb+" USING btree (label_pkg);"+\
            "CREATE INDEX "+tb+"_store_idx ON       "+ts+"."+tb+" USING btree (store_id);"+\
            "CREATE INDEX "+tb+"_pderiod_idx ON     "+ts+"."+tb+" USING btree (period_id);"+\
            "CREATE INDEX "+tb+"_batchno_idx ON     "+ts+"."+tb+" USING btree (batch_no);"+\
            "CREATE INDEX "+tb+"_expiration_idx ON  "+ts+"."+tb+" USING btree (dt_expiration);"+\
            "CREATE INDEX "+tb+"_manufacture_idx ON "+ts+"."+tb+" USING btree (dt_manufacture);"
              
        cur.execute(s)    
    cur.close()
    return 





# 2022-09-26
def stock_init(company_id,period_id,user_id=14499):
    # 将盘点数据做产品批次库存 
    cid=str(company_id)
    pid=str(period_id)
    cur=connection.cursor()
    if cid=='19267':    #易臻
        # 如已有初始化数据，先删除
        cur.execute("delete from stock.c"+cid+" where period_id="+pid)

        # 工厂入库及仓库发货,统一用182 新包装仓库
        sto='182'
        s="INSERT INTO stock.c"+cid+"(period_id,user_id,product_id,dt_manufacture,store_id,"+\
            "qty_x,qty_h,qty_p,remark,spec,ratio_x,ratio_h,ratio_p) "+\
            "select "+pid+","+str(user_id)+",product_id,dt_inbound,"+sto+","+\
            "q_x,q_h,q_p,'2022年9月3号-5号盘点',"+\
            "(SELECT value FROM product_productprop where company_id="+cid+" "+\
                "and categoryprop_id=(SELECT id FROM company_categoryprop where name='package' "+\
                    "and  category_id=(select category_id from company_company where id="+cid+")"+\
                ") and id in (select prd_property_id from product_prdprops where product_id=g.product_id) "+\
                "limit 1"+\
            "),"+\
            "p.qty_x,p.qty_h,p.qty_p "+\
            "from "+\
            "(select product_id,dt_inbound,sum(x) q_x,sum(h) q_h,sum(p) q_p "+\
                "from "+\
                "(select product_id,dt_inbound,"+\
                    "case when label_pkg=3 then 1 else 0 end x,"+\
                    "case when label_pkg=2 then 1 else 0 end h,"+\
                    "case when label_pkg=1 then 1 else 0 end p "+\
                    "FROM batchlot.c"+cid+\
                ") d "+\
                "group by product_id,dt_inbound"+\
            ") g "+\
            "left outer join "+\
            "(select id,name,qty_x,qty_h,qty_p from product_product where company_id="+cid+") p "+\
            "on g.product_id=p.id"
        
        #print(s)

        cur.execute(s)       

        return 

       


# 2022-09-26
# def batch_stock_closing(company_id,yr,mn,user_id=None):
def batch_stock_closing(company_id,period_id,user_id=None):
    '''
    对启用产品生产批次管理的企业，做库存期结(月结)
    此方法只做指定 年/月 的生产批次期结
    '''   
    #now=datetime.datetime.now().date()  

    cur=connection.cursor()
    cid=str(company_id)    
    # 2022-11-08 
    cur.execute("select ancestor_id from company_company where id="+cid)
    r=cur.fetchone()
    tb_stk="stkdetails.c"+str(r[0])
    if not user_id:       
        user_id=14499   # 14499;"system";"";"系统管理员"  
    cur_id=str(period_id)    

    s="select id from inventory_stkclosingperiod where company_id="+cid+ " "+\
        "and id<"+str(period_id)+" order by id desc limit 1"
    cur.execute(s)
    r=cur.fetchone()
    if r:
        last_period_id=str(r[0])
    else:
        last_period_id=None
    
    if cid=='19267':
        # 易臻 工厂入库及仓库发货,统一用182 新包装仓库
        sto='182 store_id'
    else:
        sto="store_id"
    
    # 当期期结开始和结束日期   
    s="select to_char(dt_fr,'yyyy-mm-dd'),to_char(dt_to,'yyyy-mm-dd') from inventory_stkclosingperiod where company_id="+cid+ " and id="+cur_id
    cur.execute(s)
    r=cur.fetchone()
    dt1=r[0]
    dt2=r[1]    

    # 如果有当期期结数据，先删除
    cur.execute("delete from stock.c"+cid+" where period_id="+cur_id)
    
    # 上期期初数
    sOpen=""
    if last_period_id:
        sOpen=" union all "+\
            "select product_id,"+sto+",dt_manufacture,qty_x x,qty_h h,qty_p p from stock.c"+cid+\
                " where period_id="+last_period_id 
    # 当期库存变动    
    sStk="(select product_id,"+sto+",label_pk_id,label_code,label_pkg,"+\
            "(select direction from inventory_voucher where id=vtype_id) cnt,"+\
            "case when label_pkg=3 then 1 else 0 end x,"+\
            "case when label_pkg=2 then 1 else 0 end h,"+\
            "case when label_pkg=1 then 1 else 0 end p,"+\
            "(select dt_inbound from batchlot.c"+cid+" where label_pk_id=s.label_pk_id or label_code=s.label_code limit 1) "+\
            "from "+tb_stk+" s where company_id="+cid+" and is_delete=false "+\
                "and vtype_id<>3 and (dt between '"+dt1+"' and '"+dt2+"')  "+\
            "union all "+\
            "select product_id,store_id,label_pk_id,label_code,label_pkg,1 cnt,"+\
            "case when label_pkg=3 then 1 else 0 end x,"+\
            "case when label_pkg=2 then 1 else 0 end h,"+\
            "case when label_pkg=1 then 1 else 0 end p,"+\
            "(select dt_inbound from batchlot.c"+cid+" where label_pk_id=s.label_pk_id or label_code=s.label_code limit 1) "+\
            "from "+tb_stk+" s where company_id="+cid+" and is_delete=false "+\
                "and vtype_id=3 and (dt between '"+dt1+"' and '"+dt2+"')  "+\
            "union all "+\
            "select product_id,store_fr_id store_id,label_pk_id,label_code,label_pkg,-1 cnt,"+\
            "case when label_pkg=3 then 1 else 0 end x,"+\
            "case when label_pkg=2 then 1 else 0 end h,"+\
            "case when label_pkg=1 then 1 else 0 end p,"+\
            "(select dt_inbound from batchlot.c"+cid+" where label_pk_id=s.label_pk_id or label_code=s.label_code limit 1) "+\
            "from "+tb_stk+" s where company_id="+cid+" and is_delete=false "+\
                "and vtype_id=3 and (dt between '"+dt1+"' and '"+dt2+"') "+\
        ") d"           
    

    # 当期变动+上期期初
    sStk="(select product_id,store_id,dt_inbound,sum(x) x,sum(h) h,sum(p) p from "+\
            "("+\
                "select product_id,store_id,dt_inbound,sum(x*cnt) x,sum(h*cnt) h,sum(p*cnt) p "+\
                    "from "+sStk+" group by product_id,store_id,dt_inbound "+\
                sOpen+\
            ") ggg group by product_id,store_id,dt_inbound "+\
        ") gg "

    sSql="INSERT INTO stock.c"+cid+"(period_id,user_id,product_id,store_id,"+\
            "qty_x,qty_h,qty_p,dt_manufacture,spec,ratio_x,ratio_h,ratio_p"+\
        ") select "+cur_id+","+str(user_id)+",product_id,store_id,x,h,p,dt_inbound,"+\
        "(SELECT value FROM product_productprop where company_id="+cid+" "+\
            "and categoryprop_id=(SELECT id FROM company_categoryprop where name='package' "+\
                "and  category_id=(select category_id from company_company where id="+cid+")"+\
            ") and id in (select prd_property_id from product_prdprops where product_id=g.product_id) "+\
            "limit 1"+\
        "),p.qty_x,p.qty_h,p.qty_p "+\
        "from "+\
        "(select "+cur_id+","+str(user_id)+",product_id,store_id,dt_inbound,"+\
            "sum(x) x,sum(h) h,sum(p) p "+\
            "from "+sStk+" group by product_id,store_id,dt_inbound"+\
        ") g "+\
        "left outer join "+\
        "(select id,name,qty_x,qty_h,qty_p from product_product where company_id="+cid+") p "+\
        "on g.product_id=p.id "  

    cur.execute(sSql)       
    cur.close()
    return 





# 2022-10-07
def retrieve_batchlot(cid,current_period_id,span_name,span_value):
    '''
    按指定会计期库存产品按指定步长返回产品库龄（以产品生产入库时间为准）明细    
    current_period_id不为0:     取指定的已期结的会计期期末产品库龄明细
    current_period_id为0:       取到当前时间为止产品库存库龄明细
    '''   

    data={'clmns':[],'data':[]}
    cur=connection.cursor()            
    span_flds=[]
    span_gflds=[]
    span_ggflds=[]
    span_heads=[] 

    # 生产入库到现在的时间差（月数或天数）
    if span_name=='months':
        sSpan="public.months_between(to_timestamp(EXTRACT(epoch FROM CAST(dt_manufacture-1 AS TIMESTAMPTZ))),now()) span"       
    elif span_name=='days':
        sSpan="current_date-dt_manufacture span"      


    cur.execute("select ancestor_id from company_company where id="+str(cid))
    r=cur.fetchone()
    aid=str(r[0])
    cid=str(cid)
    tb_stk="stkdetails.c"+aid
    tb_lot="batchlot.c"+aid


    # 取最早生产日期
    if int(current_period_id)>0:
        cur.execute("select min(dt_manufacture) from stock.c"+cid+" where period_id="+current_period_id)
    else:
        cur.execute("select min(dt_inbound) from batchlot.c"+cid)
    r=cur.fetchone()
    dt=r[0]
    
    d=datetime.date.today()
    val=int(span_value)
    n=0
    # 生成库龄步长表头和sql语句字段
    while dt<=d:            
        head=str(n)+'-'+str(n+int(span_value))
        fld="case when span>="+str(n)+" and span<"+str(n+int(span_value))+" then qty else 0 end as span"+str(n)
        if   span_name=='months':
            dt=dt+relativedelta(months=val)
            head=head+'个月'
        elif span_name=='days':
            dt=dt+datetime.timedelta(days=val)
            head=head+'天'         
        span_flds.append(fld)
        span_heads.append(head)
        span_gflds.append('sum(span'+str(n)+") as span"+str(n))
        span_ggflds.append("span"+str(n))
        n=n+int(span_value)  

    # 可能会有库龄为空（没有找到具体入库时间）的产品存在
    span_flds.append('case when span is null then qty else 0 end span'+str(n))
    span_heads.append('未知')
    span_gflds.append("sum(span"+str(n)+") as span"+str(n))
    span_ggflds.append('span'+str(n))  


    if cid == '19267':
        # 易臻 工厂入库及仓库发货,统一用182 新包装仓库
        sto = '182 store_id'
    else:
        sto = "store_id"

    if int(current_period_id)>0:
        # 取指定会计期期末产品库存库龄(生产入库时间为准)明细
        sDetails="SELECT product_id,"+sSpan+","+\
            "qty_x*(case when ratio_h>0 then ratio_h else case when ratio_p>0 then ratio_p else 1 end end)+qty_h+qty_p qty "+\
            "FROM stock.c"+cid+" where period_id="+current_period_id        
    else:
        # 取上最后一次期结(如果有)+最后一次期结日到现在为止出入库明细合并后的产品库存库龄(以生产入库时间为准)明细
        cur.execute("select id,dt_to from inventory_stkclosingperiod where company_id="+cid+" order by id desc limit 1")
        rr=cur.fetchone()
        if rr:
            # 有期结记录
            sWhereDt=" and dt>'"+rr[1].strftime('%Y-%m-%d')+"' "           
            sOpening=" union all "+"SELECT product_id,"+sto+","+\
                "1 cnt,"+\
                "qty_x x, qty_h h, qty_p p,"+\
                "dt_manufacture "+\
                "FROM stock.c"+cid+" where period_id="+str(rr[0])
        else:
            # 无期结记录
            sWhereDt=" "
            sLast=" "   
        sDetails="SELECT product_id,"+sSpan+","+\
            "x*(select coalesce(qty_h,qty_p) from product_product where id=product_id)+h qty "+\
            "from "+\
            "(select product_id,store_id,dt_manufacture,sum(x*cnt) x,sum(h*cnt) h,sum(p*cnt) p from "+\
                "(select product_id,"+sto+","+\
                        "(select direction from inventory_voucher where id=vtype_id) cnt,"+\
                        "case when label_pkg=3 then 1 else 0 end x,case when label_pkg=2 then 1 else 0 end h,case when label_pkg=1 then 1 else 0 end p,"+\
                        "(select dt_inbound from "+tb_lot+" where label_pk_id=s.label_pk_id or label_code=s.label_code limit 1) dt_manufacture "+\
                    "from "+tb_stk+" s where company_id="+cid+" and is_delete=false and vtype_id<>3 "+sWhereDt+\
                    "union all "+\
                    "select product_id,"+sto+","+\
                        "1 cnt,"+\
                        "case when label_pkg=3 then 1 else 0 end x,case when label_pkg=2 then 1 else 0 end h,case when label_pkg=1 then 1 else 0 end p,"+\
                        "(select dt_inbound from "+tb_lot+" where label_pk_id=s.label_pk_id or label_code=s.label_code limit 1) dt_manufacture "+\
                    "from "+tb_stk+" s where company_id="+cid+" and is_delete=false and vtype_id=3 "+sWhereDt+\
                    "union all "+\
                    "select product_id,"+sto+","+\
                        "-1 cnt,"+\
                        "case when label_pkg=3 then 1 else 0 end x,case when label_pkg=2 then 1 else 0 end h,case when label_pkg=1 then 1 else 0 end p,"+\
                        "(select dt_inbound from "+tb_lot+" where label_pk_id=s.label_pk_id or label_code=s.label_code limit 1) dt_manufacture "+\
                        "from "+tb_stk+" s where company_id="+cid+" and is_delete=false and vtype_id=3 "+sWhereDt+\
                    sOpening+\
                ") d group by product_id,store_id,dt_manufacture "+\
            ") g"  
    s="select p.name,p.shelf_life,"+\
        "coalesce((SELECT value FROM product_productprop where company_id="+cid+" "+\
            "and categoryprop_id=(SELECT id FROM company_categoryprop where name='package' "+\
                "and  category_id=(select category_id from company_company where id="+cid+")"+\
                ") and id in (select prd_property_id from product_prdprops where product_id=p.id) "+\
            "limit 1"+\
        "),''),"+",".join(span_ggflds)+\
        " from "+\
        "(select product_id,"+",".join(span_ggflds)+" from "+\
            "(select product_id,"+",".join(span_gflds)+" from "+\
                "(select product_id,"+",".join(span_flds)+" from "+\
                    "("+sDetails+") d"+\
                ") s "+\
                "group by product_id"+\
            ") g "+\
        ") gg "+\
        "left outer join "+\
        "(select id,name,shelf_life from product_product where company_id="+cid+") p "+\
        "on gg.product_id=p.id "+\
        "order by p.id"


    cur.execute(s)
    for r in cur.fetchall():
        
        lf=''
        un=''
        if r[1]!='{}':
            jn=json.loads(r[1])
            if jn['period']=='year':
                un='年'
            elif jn['period']=='day':
                un='天'
            lf=str(jn['value'])+un
        a={'product':r[0],'spec':r[2],'shelflife':lf}        
        i=0
        qty=0            
        while i<len(span_ggflds):
            a[span_ggflds[i]]=r[i+3]
            qty=qty+r[i+3]
            i=i+1
        a['total']=qty
        data['data'].append(a)
    data['clmns']=[{'title':'产品名称','data':'product'},{'title':'包装规格','data':'spec'},{'title':'保质期','data':'shelflife'},{'title':'合计','data':'total'}]  
    i=0
    while i<len(span_heads):        
        data['clmns'].append({'title':span_heads[i],'data':span_ggflds[i]})  
        i=i+1    
    cur.close()        
    return data



# 2022-09-30
@login_required
def batchlot_stock_rpt(request,action,current_period_id=None):
    # 产品批次管理企业进出存报表，及库存产品批次明细
    cid=str(request.user.company_id)
    cur=connection.cursor()
    if action=='report':
        data={'periods':[]}
        s="select id,yr,mn from inventory_stkclosingperiod where company_id="+cid+\
            " and is_stocktake=false order by id desc limit 12"
        cur.execute(s)
        for r in cur.fetchall():
            data['periods'].append({
                'id':r[0],
                'name':str(r[1])+'年'+str(r[2])+'月'
            })
        return render(request,'inventory/stock/batchlot_report.html',data)  
    elif action=='data':     
        data={
            'title':'',
            'clmns':{
                'stock':['产品名称','包装规格', '期初箱','期初盒', '入库箱','入库盒', '出库箱','出库盒', '退库箱','退库盒', '期末箱','期末盒'],
                'batchlot':[],
            },
            'data':{
                'stock':[],
                'batchlot':[]
            }
        }    
        span_name=request.GET.get('span_name','')
        span_value=request.GET.get('span_value','')
        batchlot_only=request.GET.get('batchlot_only','')      

        if batchlot_only:   
            # 前修改了库龄步长，只取产品生产批次明细              
            dd=retrieve_batchlot(cid,current_period_id,span_name,span_value)       
            data['clmns']['batchlot']=dd['clmns']
            data['data']['batchlot']=dd['data']             
            return JsonResponse(data)          

        
        last_period_id=None
        #last_dt=None
        sDt=''        
        
        if int(current_period_id)>0:
            s="select (select id from inventory_stkclosingperiod where id<"+current_period_id+" "+\
                    "and company_id="+cid+" order by id desc limit 1),"+\
                "to_char(dt_fr,'yyyy-mm-dd'),to_char(dt_to,'yyyy-mm-dd'),yr,mn "+\
                "from inventory_stkclosingperiod where id="+current_period_id                
            cur.execute(s)
            r=cur.fetchone()
            if r:
                last_period_id=str(r[0])
                sDt=" and (dt between '"+r[1]+"' and '"+r[2]+"')"
                data['title']=str(r[3])+'年'+str(r[4])+'月 ('+r[1]+' 至 '+r[2]+')'
            else:
                s="select id,to_char(dt_to,'yyyy-mm-dd') from inventory_stkclosingperiod where company_id="+cid+" "+\
                    "order by id desc limit 1"
                cur.execute(s)
                r=cur.fetchone()
                if r:
                    last_period_id=str(r[0])                                
                    sDt=" and dt>'"+r[1]+"'"
                    data['title']='现有库存( '+r[1]+' 至 '+datetime.datetime.now().strftime('%Y-%m-%d')+')'
        else:
            cur.execute("select id,dt_fr,dt_to from inventory_stkclosingperiod where company_id="+cid+" order by id desc limit 1")
            r0=cur.fetchone()
            if r0:
                last_period_id=str(r0[0])
                sDt=" and dt>'"+r0[2].strftime('%Y-%m-%d')+"'"
            data['title']='当前产品实时库存'

        # 现阶段，报表先不区分仓库       
        # 上期期初数
        sOpen=""
        sSelO=""
        if last_period_id:
            sSelO="union all "+\
                "select product_id from stock.c"+cid+" where period_id="+last_period_id+" "
            sOpen="left outer join "+\
                "(select product_id,sum(qty_x) x,sum(qty_h) h,sum(qty_p) p from stock.c"+cid+" "+\
                    "where period_id="+last_period_id+" group by product_id"+\
                ") b "\
                "on p.product_id=b.product_id "
            
        else:
            #data['title']='当前产品库存'
            sOpen="left outer join "+\
                "(0 product_id,0 x,0 h,0 p) b "+\
                "on p.product_id=b.product_id"
        # 当期库存变动
        sStk="left outer join "+\
            "(select product_id,'inbound',"+\
                "sum(case when label_pkg=3 then 1 else 0 end) x,"+\
                "sum(case when label_pkg=2 then 1 else 0 end) h,"+\
                "sum(case when label_pkg=1 then 1 else 0 end) p "+\
                "from inventory_stkdetails s where company_id="+cid+" and is_delete=false "+\
                    "and vtype_id=1 "+sDt+" group by product_id"+\
            ") i "+\
            "on p.product_id=i.product_id "+\
            "left outer join "+\
            "(select product_id,'outbound',"+\
                "sum(case when label_pkg=3 then 1 else 0 end) x,"+\
                "sum(case when label_pkg=2 then 1 else 0 end) h,"+\
                "sum(case when label_pkg=1 then 1 else 0 end) p "+\
                "from inventory_stkdetails s where company_id="+cid+" and is_delete=false "+\
                    "and vtype_id=2 "+sDt+" group by product_id"+\
            ") o "+\
            "on p.product_id=o.product_id "+\
            "left outer join  "+\
            "(select product_id,'returning',"+\
                "sum(case when label_pkg=3 then 1 else 0 end) x,"+\
                "sum(case when label_pkg=2 then 1 else 0 end) h,"+\
                "sum(case when label_pkg=1 then 1 else 0 end) p "+\
                "from inventory_stkdetails s where company_id="+cid+" and is_delete=false "+\
                    "and vtype_id=4 "+sDt+" group by product_id "+\
            ") r "+\
            "on p.product_id=r.product_id "
        sStk="(select product_id from "+\
                "(select product_id from inventory_stkdetails where company_id="+cid+" and is_delete=false "+sDt+" "+\
                    sSelO+" "+\
                ") u group by product_id"+\
            ") p "+ sOpen+sStk

        # 产品批次库存最小单位 一律为盒
        # 当产品箱-盒转换比: 如果产品每箱盒数未定义，取每箱瓶数;如果每箱瓶数也未定义，比率为1
        sStk="select p.product_id,pp.name,pp.qty_x,pp.qty_h,pp.qty_p,"+\
            "coalesce((SELECT value FROM product_productprop where company_id="+cid+" "+\
                "and categoryprop_id=(SELECT id FROM company_categoryprop where name='package' "+\
                    "and  category_id=(select category_id from company_company where id="+cid+")"+\
                    ") and id in (select prd_property_id from product_prdprops where product_id=p.product_id) "+\
                "limit 1"+\
            "),''),"+\
            "coalesce(b.x,0),coalesce(b.h,b.p,0),"+\
            "coalesce(i.x,0),coalesce(i.h,i.p,0),"+\
            "coalesce(o.x,0),coalesce(o.h,o.p,0),"+\
            "coalesce(r.x,0),coalesce(r.h,r.p,0),"+\
            "((case when qty_h>0 then qty_h else case when qty_p>0 then qty_p else 1 end end*coalesce(b.x,0)+coalesce(b.h,b.p,0))+"+\
                "(case when qty_h>0 then qty_h  else case when qty_p>0 then qty_p else 1 end end*coalesce(i.x,0)+coalesce(i.h,i.p,0))-"+\
                "(case when qty_h>0 then qty_h  else case when qty_p>0 then qty_p else 1 end end*coalesce(o.x,0)+coalesce(o.h,o.p,0))+"+\
                "(case when qty_h>0 then qty_h  else case when qty_p>0 then qty_p else 1 end end*coalesce(r.x,0)+coalesce(r.h,r.p,0)))"+\
                    "/case when pp.qty_h=0 then pp.qty_p else pp.qty_h end,"+\
            "mod((case when qty_h>0 then qty_h else case when qty_p>0 then qty_p else 1 end end*coalesce(b.x,0)+coalesce(b.h,b.p,0))+"+\
                "(case when qty_h>0 then qty_h else case when qty_p>0 then qty_p else 1 end end*coalesce(i.x,0)+coalesce(i.h,i.p,0))-"+\
                "(case when qty_h>0 then qty_h else case when qty_p>0 then qty_p else 1 end end*coalesce(o.x,0)+coalesce(o.h,o.p,0))+"+\
                "(case when qty_h>0 then qty_h else case when qty_p>0 then qty_p else 1 end end*coalesce(r.x,0)+coalesce(r.h,r.p,0))"+\
                    ",case when pp.qty_h=0 then pp.qty_p else pp.qty_h end),"+\
            "(case when qty_h>0 then qty_h else case when qty_p>0 then qty_p else 1 end end*coalesce(b.x,0)+coalesce(b.h,b.p,0))"+\
                "/case when pp.qty_h=0 then pp.qty_p else pp.qty_h end,"+\
            "mod(case when qty_h>0 then qty_h else case when qty_p>0 then qty_p else 1 end end*coalesce(b.x,0)+coalesce(b.h,b.p,0)"+\
                ",case when pp.qty_h=0 then pp.qty_p else pp.qty_h end) "+\
            "from "+sStk+\
            "left outer join "+\
            "(select id,name,qty_x,qty_h,qty_p from product_product where company_id="+cid+") pp "+\
            "on p.product_id=pp.id "+\
            "order by pp.id"
        cur.execute(sStk)
        for r in cur.fetchall():  
            data['data']['stock'].append([ r[1],r[5], r[16],r[17], r[8],r[9],r[10],r[11], r[12],r[13], r[14],r[15] ])  


        # 当前会计期库存产品生产批次人（保质期）明细        
        d=retrieve_batchlot(cid,current_period_id,span_name,span_value)
        data['clmns']['batchlot']=d['clmns']
        data['data']['batchlot']=d['data']         

        return JsonResponse(data)



def get_ancestorId(company_id):
    cur=connection.cursor()
    cur.execute("select ancestor_id from logistics.company_company where id = " + str(company_id))
    ancestor_id = str(cur.fetchone()[0])
    return ancestor_id
