from datetime import datetime as dt
from fastapi import APIRouter, Request, Body, status
from typing import Dict, List
from fastapi.encoders import jsonable_encoder
from fastapi.responses import JSONResponse
from lib.common import *
import pydash

router = APIRouter()

@router.post("/dash_po")
async def dash_po(request : Request, filter: Dict = Body(...)):
    filter = jsonable_encoder(filter)

    cursor = request.app.mssql.cursor()
    filter_items = []
    if filter['c1'] == 'PO':
        columns = ['po_number', 'vender_name', 'po_date', 'po_status']
        statement = "select POL.PurchaseOrderListId,VEN.VendorName,POL.CreatedDate, POL.POStatus from PurchaseOrderList as POL " \
                    "JOIN Vaaak.Vendor as VEN on VEN.VendorId = POL.VendorId where 1=1"

        if filter['c2']:
            statement = statement + " AND POL.PurchaseOrderListId = {}".format(filter['c2'])

        if filter['c3']:
            statement = statement + " AND VEN.VendorName = '{}'".format(filter['c3'])

        if filter['c4']:
            start_date = dt.strptime(filter['c4'].split("#")[0], '%d/%m/%Y')
            end_date = dt.strptime(filter['c4'].split("#")[1], '%d/%m/%Y')

            statement = statement + " AND POL.CreatedDate >= '{}' and POL.CreatedDate <= '{}'".format(start_date,end_date)

        rows = cursor.execute(statement)
        for row in rows:
            row = [*row]
            row[2] = arrow.get(row[2]).format("DD/MM/YYYY")
            filter_items.append(dict(zip(columns,row)))

        return filter_items

    if filter['c1'] == 'GRN':
        columns = ['grn_no', 'po_no','vender_name', 'grn_date', 'invoice_no','po_status']

        statement = "select GRN.GrnId,POL.PurchaseOrderListId,VEN.VendorName,GRN.LastUpdate,GRNL.InvoiceNumber, POL.POStatus from PurchaseOrderList as POL " \
                    "JOIN Vaaak.Vendor as VEN on VEN.VendorId = POL.VendorId " \
                    "JOIN Vaaak.GrnList as GRN on GRN.PurchaseOrderListId = POL.PurchaseOrderListId " \
                    "JOIN Vaaak.GrnItem as GRNL on GRNL.GrnId = GRN.GrnId " \
                    "JOIN (select count(PurchaseOrderListId) AS cnt,PurchaseOrderListId from Vaaak.GrnList as GRN1 group by PurchaseOrderListId having count(PurchaseOrderListId) = 1 ) as tem on tem.PurchaseOrderListId = POL.PurchaseOrderListId " \
                    "where 1 = 1 and POL.POStatus = 0 "

        if filter['c2']:
            statement = statement + " AND GRN.GrnId = {}".format(filter['c2'])

        if filter['c3']:
            statement = statement + " AND VEN.VendorName = '{}'".format(filter['c3'])

        if filter['c4']:
            start_date = dt.strptime(filter['c4'].split("#")[0], '%d/%m/%Y')
            end_date = dt.strptime(filter['c4'].split("#")[1], '%d/%m/%Y')

            statement = statement + " AND GRN.LastUpdate >= '{}' and GRN.LastUpdate <= '{}'".format(start_date,
                                                                                                      end_date)

        rows = cursor.execute(statement)
        for row in rows:
            row = [*row]
            row[3] = arrow.get(row[3]).format("DD/MM/YYYY")
            filter_items.append(dict(zip(columns, row)))

        return filter_items
    if filter['c1'] == 'RN':

        columns = ['rn_no', 'po_no', 'vender_name', 'rn_date', 'invoice_no', 'po_status']

        statement = "select GRN.GrnId,POL.PurchaseOrderListId,VEN.VendorName,GRN.LastUpdate,GRNL.InvoiceNumber, POL.POStatus from PurchaseOrderList as POL " \
                    "JOIN Vaaak.Vendor as VEN on VEN.VendorId = POL.VendorId " \
                    "JOIN Vaaak.GrnList as GRN on GRN.PurchaseOrderListId = POL.PurchaseOrderListId " \
                    "JOIN Vaaak.GrnItem as GRNL on GRNL.GrnId = GRN.GrnId " \
                    "where 1 = 1 "

        if filter['c2']:
            statement = statement + " AND GRN.GrnId = {}".format(filter['c2'])

        if filter['c3']:
            statement = statement + " AND VEN.VendorName = '{}'".format(filter['c3'])

        if filter['c4']:
            start_date = dt.strptime(filter['c4'].split("#")[0], '%d/%m/%Y')
            end_date = dt.strptime(filter['c4'].split("#")[1], '%d/%m/%Y')

            statement = statement + " AND GRN.LastUpdate >= '{}' and GRN.LastUpdate <= '{}'".format(start_date,
                                                                                                    end_date)

        rows = cursor.execute(statement)
        for row in rows:
            row = [*row]
            row[3] = arrow.get(row[3]).format("DD/MM/YYYY")
            filter_items.append(dict(zip(columns, row)))

        return filter_items