#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from models import *
def warehouse_room_id_get_warehouse_room_info(db,id):
    query = db.query(ResourceProvisionWarehouseRoomInfo)
    query = query.filter_by(id = id,del_flag = '0')
    return query.first()
def warehouse_id_get_warehouse_info(db,id):
    query = db.query(ResourceProvisionWarehouseInfo)
    query = query.filter_by(warehouse_id = id,del_flag = '0')
    # query = query.filter(ResourceProvisionWarehouseInfo.warehouse_id== id)
    # query = query.filter(ResourceProvisionWarehouseInfo.del_flag != '2')
    return query.first()
def type_id_get_material_type_info(db,id):
    query = db.query(ResourceProvisionMaterialType)
    query = query.filter_by(id = id,del_flag = '0')
    return query.first()
def parent_id_get_type_info(db,parent_id):
    query = db.query(ResourceProvisionMaterialType)
    query = query.filter(ResourceProvisionMaterialType.del_flag != '2')
    query = query.filter(ResourceProvisionMaterialType.parent_id == parent_id)
    query = query.filter(ResourceProvisionMaterialType.display_status == '1')
    query = query.order_by(ResourceProvisionMaterialType.sort_order.asc())
    return query.all()
def count_warehouse(db):
    query = db.query(ResourceProvisionWarehouseInfo)
    query = query.filter_by(del_flag = '0')
    return query.count()
def count_material(db):
    query = db.query(func.sum(ResourceProvisionMaterialInfo.inventory).label('warehouse_num'))
    query = query.filter_by(del_flag = '0')
    result = query.first()
    warehouse_num = result.warehouse_num if result else 0
    return warehouse_num

def count_area_material(db):
    query = db.query(
        ResourceProvisionWarehouseInfo.area_name.label('name'),
        func.sum(ResourceProvisionMaterialInfo.inventory).label('value')
    ).select_from(
        ResourceProvisionMaterialInfo
    ).join(
        ResourceProvisionWarehouseInfo,
        ResourceProvisionMaterialInfo.warehouse_id == ResourceProvisionWarehouseInfo.warehouse_id
    ).filter(
        ResourceProvisionMaterialInfo.del_flag == '0',
        ResourceProvisionWarehouseInfo.del_flag == '0'
    ).group_by(
        ResourceProvisionWarehouseInfo.area_name
    )

    # 执行查询并获取结果
    results = query.all()
    data = {"茂南区":0,"电白区":0,"高州市":0,"化州市":0,"信宜市":0,"高新区":0,"滨海新区":0}
    # 打印结果
    for result in results:
        data[result.name]=result.value
    return [{"name": key, "value": value} for key, value in data.items()]
def count_warehouse_material(db):
    query = db.query(
        ResourceProvisionWarehouseInfo.warehouse_name.label('name'),
        func.sum(ResourceProvisionMaterialInfo.inventory).label('value')
    ).select_from(
        ResourceProvisionMaterialInfo
    ).join(
        ResourceProvisionWarehouseInfo,
        ResourceProvisionMaterialInfo.warehouse_id == ResourceProvisionWarehouseInfo.warehouse_id
    ).filter(
        ResourceProvisionMaterialInfo.del_flag == '0',
        ResourceProvisionWarehouseInfo.del_flag == '0'
    ).group_by(
        ResourceProvisionWarehouseInfo.warehouse_name
    )
    # 执行查询并获取结果
    results = query.all()
    data = []
    # 打印结果
    for result in results:
        data.append({"name":result.name,"value":result.value})
    return data
def count_type_material(db):
    query = db.query(
        ResourceProvisionMaterialType.material_category_name.label('name'),
        func.sum(ResourceProvisionMaterialInfo.inventory).label('value')
    ).select_from(
        ResourceProvisionMaterialInfo
    ).join(
        ResourceProvisionMaterialType,
        ResourceProvisionMaterialInfo.material_type == ResourceProvisionMaterialType.id
    ).filter(
        ResourceProvisionMaterialInfo.del_flag == '0',
        ResourceProvisionMaterialType.del_flag == '0'
    ).group_by(
        ResourceProvisionMaterialType.material_category_name
    )
    # 执行查询并获取结果
    results = query.all()
    data = []
    # 打印结果
    for result in results:
        data.append({"name":result.name,"value":result.value})
    return data
def count_room_material(db,warehouseId):
    room_list = db.query(ResourceProvisionWarehouseRoomInfo)\
        .filter(ResourceProvisionWarehouseRoomInfo.del_flag == '0',
                ResourceProvisionWarehouseRoomInfo.warehouse == warehouseId).all()
    data = []
    for room in room_list:
        inventory = db.query(func.sum(ResourceProvisionMaterialInfo.inventory).label('value'))\
            .filter( ResourceProvisionMaterialInfo.del_flag == '0',
                     ResourceProvisionMaterialInfo.room_id ==room.id).first()
        value = 0
        if inventory.value:
            value = inventory.value
        data.append({"name":room.room_name,"value":value})
    # query = db.query(
    #     ResourceProvisionWarehouseRoomInfo.room_name.label('name'),
    #     func.coalesce(func.sum(ResourceProvisionMaterialInfo.inventory), 0).label('value')
    # ).select_from(
    #     ResourceProvisionWarehouseRoomInfo
    # ).outerjoin(
    #     ResourceProvisionMaterialInfo,
    #     ResourceProvisionMaterialInfo.room_id == ResourceProvisionWarehouseRoomInfo.id
    # ).filter(
    #     ResourceProvisionMaterialInfo.del_flag == '0',
    #     ResourceProvisionWarehouseRoomInfo.del_flag == '0',
    # ResourceProvisionWarehouseRoomInfo.warehouse == warehouseId
    # ).group_by(
    #     ResourceProvisionWarehouseRoomInfo.room_name
    # )

    # 执行查询并获取结果
    # print(str(query))
    # results = query.all()



    # 打印结果
    # for result in results:
    #     data.append({"name":result.name,"value":result.value})
    return data
def material_id_get_material_info(db,id):
    query = db.query(ResourceProvisionMaterialInfo)
    query = query.filter_by(material_id = id,del_flag = '0')
    return query.first()
def warehouse_id_get_material_list(db,id):
    query = db.query(ResourceProvisionMaterialInfo)
    query = query.filter_by(warehouse_id = id,del_flag = '0')
    return query.all()
def material_id_get_material_barcode_info(db,id):
    query = db.query(ResourceProvisionMaterialBarcode)
    query = query.filter_by(material_code = id,del_flag = '0')
    return query.first()
def material_barcode_id_get_material_barcode_info(db,id):
    query = db.query(ResourceProvisionMaterialBarcode)
    query = query.filter_by(id = id,del_flag = '0')
    return query.first()
def delete_resource_provision_file(db,from_scenario,foreign_key):
    file_query = db.query(ResourceProvisionFile)
    file_query = file_query.filter(ResourceProvisionFile.del_flag != '2')
    file_query = file_query.filter(ResourceProvisionFile.from_scenario == from_scenario)
    file_query = file_query.filter(ResourceProvisionFile.foreign_key == foreign_key)
    files = file_query.all()
    for file in files:
        file.del_flag = '2'
def get_resource_provision_file_query_fun(db,from_scenario,foreign_key):
    file_query = db.query(ResourceProvisionFile)
    file_query = file_query.filter(ResourceProvisionFile.del_flag != '2')
    file_query = file_query.filter(ResourceProvisionFile.from_scenario == from_scenario)
    file_query = file_query.filter(ResourceProvisionFile.foreign_key == foreign_key)
    files = file_query.all()
    result = [{
        "uid": file.file_id,
        "status": file.status,
        "name": file.file_name_desc,
        "url":  file.file_name  #"/api/file/download/%s" %
    } for file in files]
    return result
# 申报采购部分
def declaration_id_get_declaration_info(db,id):
    query = db.query(ResourceProvisionProcurementDeclaration)
    query = query.filter_by(id = id,del_flag = '0')
    return query.first()
def declaration_id_get_declaration_detail_list(db,id):
    query = db.query(ResourceProvisionProcurementDeclarationDetail)
    query = query.filter_by(declaration_id = id,del_flag = '0')
    query = query.order_by(ResourceProvisionProcurementDeclarationDetail.serial_number)
    return query.all()
def detail_id_get_declaration_detail_info(db,id):
    query = db.query(ResourceProvisionProcurementDeclarationDetail)
    query = query.filter_by(id = id,del_flag = '0')
    return query.first()

def declaration_id_get_declaration_review_info(db,id):
    query = db.query(ResourceProvisionProcurementReview)
    query = query.filter_by(declaration_id = id,del_flag = '0')
    return query.first()
def get_declaration_review_list(db):
    query = db.query(ResourceProvisionProcurementReview)
    query = query.filter_by(del_flag = '0')
    return query.all()
# 申请调度部分
def dispatch_id_get_dispatch_info(db,id):
    query = db.query(ResourceProvisionDispatch)
    query = query.filter_by(id = id,del_flag = '0')
    return query.first()
def dispatch_id_get_dispatch_detail_list(db,id):
    query = db.query(ResourceProvisionDispatchDetail)
    query = query.filter_by(dispatch_id = id,del_flag = '0')
    query = query.order_by(ResourceProvisionDispatchDetail.serial_number)
    return query.all()
def detail_id_get_dispatch_detail_info(db,id):
    query = db.query(ResourceProvisionDispatchDetail)
    query = query.filter_by(id = id,del_flag = '0')
    return query.first()

def dispatch_id_get_dispatch_review_info(db,id):
    query = db.query(ResourceProvisionDispatchReview)
    query = query.filter_by(dispatch_id = id,del_flag = '0')
    return query.first()
def get_dispatch_review_list(db):
    query = db.query(ResourceProvisionDispatchReview)
    query = query.filter_by(del_flag = '0')
    return query.all()
def inbound_id_get_inbound_info(db,id):
    query = db.query(ResourceProvisionInboundBasic)
    query = query.filter_by(id = id,del_flag = '0')
    return query.first()
def outbound_id_get_outbound_info(db,id):
    query = db.query(ResourceProvisionOutboundBasic)
    query = query.filter_by(id = id,del_flag = '0')
    return query.first()
def create_movement(db,movement:list,io_number,io_flag,user_id):
    for data in movement:
        material_id= data['物资编码']
        changed_stock = data['数量']
        remaining_stock = 0
        query = db.query(ResourceProvisionWarehouseMovement)
        query = query.filter_by(del_flag='0')
        if material_id:
            query = query.filter(ResourceProvisionWarehouseMovement.material_id==material_id)
        material_info = query.order_by(ResourceProvisionWarehouseMovement.create_time.desc()).first()
        if material_info:
            if io_flag=='I':
                remaining_stock = material_info.remaining_stock+changed_stock
            else:
                if material_info.remaining_stock<changed_stock:

                    return '剩余库存小于出库量'
                remaining_stock =  material_info.remaining_stock-changed_stock

        new_movement = ResourceProvisionWarehouseMovement(
            material_id=material_id,#Column(String(255), nullable=False, comment='物资id')
            io_number = io_number,#Column(String(255), nullable=False, comment='出入库单号')
            io_flag = io_flag,#Column(String(10), nullable=False, comment='出入库标识')
            changed_stock = changed_stock,#Column(Integer, nullable=False, comment='变动库存')
            remaining_stock = remaining_stock,#Column(Integer, nullable=False, comment='剩余库存')
            create_by=user_id
        )
        db.add(new_movement)
        db.commit()
    return '成功'