const oo = require('openorange');
const cm = oo.classmanager;

let Description = {
    filename: __filename,
    name: 'OfidirectReport',
    title: 'Ofidirect Report',
    inherits: 'Report',
    params: {
        Date:{type: "period"},
        ItemGroup:{type: "string"},
        ItemsWithSales: {type: "boolean"},
    },
    form: [
        {type:"vuecomponent",component:"PeriodComponent",colspan:6,field:"Date",label:"Date"},
        {field: "ItemGroup", label: "Grupo de Articulos", pastewindow:"ItemGroupPasteWindow"},
        {field: "ItemsWithSales", name: "ItemsWithSales", label: "Items Without Sales", editor: "checkbox"},
    ]
};

let Parent = cm.SuperClass(Description);
class OfidirectReport extends Parent {

    async defaults() {
        let specs = this.getRecord();
        specs.Date[0] = null;
        specs.Date[1] = null;
        specs.ItemsWithSales = true;
    }

    async check() {
        let spec = this.getRecord();
        if (!spec.Date[0]) {
            return this.getRecord().fieldErrorResponse("NONBLANKERR", 'Date');
        }
        if (!spec.Date[1]) {
            return this.getRecord().fieldErrorResponse("NONBLANKERR", 'Date');
        }
        return true;
    }

    getQuery() {

        let specs = this.getRecord();

        let queryGR = oo.query.select(["i.Code AS Code", "i.Name AS Name", "SUM(gri.UnitQty) AS TotalReceived", "IFNULL(null,0) TotalSales", "i.Unit AS ArtUnit"])
            .from("Item", "i")
            .join(new oo.query.LeftJoinBuilder("GoodsReceiptItemRow","gri").on({"gri.ArtCode": new oo.query.Column('i.Code')}))
            .join(new oo.query.InnerJoinBuilder("GoodsReceipt","gr").on({"gri.masterId": new oo.query.Column('gr.internalId')}))
            .where({"gr.TransDate__between": [specs.Date[0].format("YYYY-MM-DD"), specs.Date[1].format("YYYY-MM-DD")]})
            .groupby(["i.Code"]);

        let querySO = oo.query.select(["i.Code AS Code", "i.Name AS Name", "IFNULL(null,0) TotalReceived", "SUM(soi.UnitQty) AS TotalSales", "i.Unit AS ArtUnit"])
            .from("Item", "i")
            .join(new oo.query.LeftJoinBuilder("SalesOrderItemRow","soi").on({"soi.ArtCode": new oo.query.Column('i.Code')}))
            .join(new oo.query.InnerJoinBuilder("SalesOrder","so").on({"soi.masterId": new oo.query.Column('so.internalId')}))
            .where({"so.TransDate__between": [specs.Date[0].format("YYYY-MM-DD"), specs.Date[1].format("YYYY-MM-DD")]})
            .groupby(["i.Code"]);

        let queryQty = queryGR.union(querySO);

        let tableQuery = oo.query.select(["tab.Code", "tab.Name AS Name", "SUM(TotalReceived) AS TotalReceived","SUM(tab.TotalSales) AS TotalSales","tab.ArtUnit"])
            .from(queryQty, "tab")
            .groupby(["tab.Code"]);
            console.log(tableQuery.previewSQL());
        return tableQuery;

    }

    async getProcessedData() {
        let query = await this.getQuery().fetch();
        return query;
    }

    buildHeader(builder){
        let header = [
            {col:'Code', datacol: 'Code', label: 'ArtCode'},
            {col:'Name', datacol: 'Name', label: 'Name'},
            {col:'TotalReceived', datacol: 'TotalReceived', label: 'TotalReceived'},
            {col:'TotalSales', datacol: 'TotalSales', label: 'TotalSales'},
            {col:'Unity', datacol: 'Unity', label: 'Unity'},
        ];
        builder.headerRow(header);
    }

    buildColumns(builder){
        builder
            .column("Code" , {linkto: {record: 'Item', window: 'ItemWindow'}})
            .column("Name")
            .column("TotalReceived", {
                cssClass: (row) => 'align-left'
            })
            .column("TotalSales", {
                cssClass: (row) => 'align-left'
            })
            .column("Unity");
    }

    async getData(){
        let builder =  this.newDataBuilder();
        builder.table(OfidirectReport);
        let data = await this.getProcessedData();
        this.buildColumns(builder);
        this.buildHeader(builder);
        for (let row of data){
            builder.row(
                {
                    Code: row.Code,
                    Name: row.Name,
                    TotalReceived: row.TotalReceived,
                    TotalSales: row.TotalSales,
                    Unity: row.ArtUnit,
                }
            );
        }
        return builder;
    }
}

module.exports = OfidirectReport.initClass(Description);


// getQuery(){
//     const specs = this.getRecord();
//     let
//     let query  = oo.query.raw();
//     query.sql = "SELECT i.Code AS Code, i.Name AS Name, IFNULL(SUM(gri.Qty), 0) TotalReceived, i.Unit,"
//     query.sql += "IFNULL((SELECT SUM(soi.Qty)"
//     query.sql += "FROM SalesOrderItemRow soi"
//     query.sql += "INNER JOIN SalesOrder so on soi.masterId = so.internalId  "
//     query.sql += "WHERE soi.ArtCode = i.Code  "
//     query.sql += `AND so.TransDate BETWEEN  '${record.Date[0].format('YYYY-MM-DD')}' AND  '${record.Date[1].format('YYYY-MM-DD')}' `
//     query.sql += "AND so.Status = 1),0) TotalSales"
//     query.sql += "FROM Item i  "
//     query.sql += "LEFT JOIN GoodsReceiptItemRow gri on gri.ArtCode = i.Code  "
//     query.sql += "LEFT JOIN GoodsReceipt gr on gri.masterId = gr.internalId"
//     if query.sql += `WHERE gr.TransDate BETWEEN  '${record.Date[0].format('YYYY-MM-DD')}' AND  '${record.Date[1].format('YYYY-MM-DD')}' `
//     query.sql += "AND gr.Status = 1 AND gr.Invalid = 0   "
//     if (specs.ItemGroup) query.sql += `AND i.ItemGroup = '${record.ItemGroup}' `
//     query.sql += "GROUP BY i.Code"
//     query.sql += "HAVING TotalSales = 0"

//     // let query = oo.query
//     //     .select(["so.TransDate AS Fecha","i.Code as Code", "i.Name AS Name", "IFNULL(SUM(soi.Qty), 0) TotalSales",
//     //         "IFNULL(SUM(gri.Qty), 0) TotalReceived", "i.Unit AS Unity"])
//     //     .from("Item", "i")
//     //     .join(new oo.query.LeftJoinBuilder("SalesOrderItemRow", "soi").on({"soi.ArtCode": new oo.query.Column("i.Code")}))
//     //     .join(new oo.query.LeftJoinBuilder("SalesOrder", "so").on({"so.internalId": new oo.query.Column("soi.masterId")}))
//     //     .join(new oo.query.LeftJoinBuilder("GoodsReceiptItemRow", "gri").on({"gri.ArtCode": new oo.query.Column("i.Code")}))
//     //     .join(new oo.query.LeftJoinBuilder("GoodsReceipt", "gr").on({"gr.internalId": new oo.query.Column("gri.masterId")}))
//     //     .where(new oo.query.ConditionBuilder({'so.Status': 1}))
//     // //if(Date) query.and({"so.TransDate__between": [specs.Date[0].format("YYYY-MM-DD"), specs.Date[1].format("YYYY-MM-DD")]})
//     // if(specs.ItemsWithSales) query.and(new oo.query.ConditionBuilder({'soi.Qty': null}));
//     // if(specs.ItemGroup) query.and({"i.ItemGroup": specs.ItemGroup});
//     // query.or(new oo.query.ConditionBuilder({'gr.Status': 1}));
//     // query.groupby("i.Code");
//     // query.orderby("i.Code");
//     // return query;
// }