detect date period range overlapping for the same product

user_1681247 0
Viewed 112

I have a table with the following data for an example

BNO   RNO    FromPeriod  EndPeriod
===   =====  ==========  ==========
01    0123   01/2010     05/2011
02    159    01/2010     01/2010
03    0525   01/2010     01/2010

And I want to throw an error message when the dates overlap for the same BNO and RNO by creating new rows

BNO   RNO    FromPeriod  EndPeriod
===   =====  ==========  ==========
01    0123   05/2010     01/2011

and ignore if the dates do not overlap.

BNO   RNO    FromPeriod  EndPeriod
===   =====  ==========  ==========
01    0123   05/2009     11/2009
01    0123   05/2015     01/2016

I have tried the following but couldn't achieve the result I am expecting.

function validatePeriodRange(BNO, RNO, FromPeriod, ToPeriod){
    for (gridRow = 2; gridRow <= $("tr",$("#GRID_BRP")).length; gridRow++){
        var rowBoxNumber= cellValue("BNUM",gridRow)
        var rowVatReg= cellValue("RNUM",gridRow)
        var rowFrom= cellValue("FROMPERIOD",gridRow)
        var rowTo= cellValue("TOPERIOD",gridRow)

        rowFrom= 20 +rowFrom.substring(3,5) + rowFrom.substring(0,2)
        rowTo= 20 +rowTo.substring(3,5) + rowTo.substring(0,2)
        currentFrom = 20 +currentFrom.substring(3,5) + currentFrom.substring(0,2)
        currentTo = 20 +currentTo.substring(3,5) + currentTo.substring(0,2)

        if(currentBoxNumber == rowBoxNumber && currentVatReg == rowVatReg){
            if(currentFrom >= rowFrom && currentFrom<= rowTo){
                return 1 // Error message
            }

            if(currentTo >= rowFrom && currentTo<= rowTo){
                return 1
            }

            if(currentFrom >= rowFrom && rowTo==""){
                return 1
            }

            if(currentTo >= rowFrom && rowTo==""){
                return 1
            }
        }
    }
    return 0 // No error message
}
javascript
1 Answers
rowFrom= 20 +rowFrom.substring(3,5) + rowFrom.substring(0,2)
rowTo= 20 +rowTo.substring(3,5) + rowTo.substring(0,2)
currentFrom = 20 +currentFrom.substring(3,5) + currentFrom.substring(0,2)
currentTo = 20 +currentTo.substring(3,5) + currentTo.substring(0,2)

what does 20 mean? I don't know what this is intended for.

maybe prefix of year(20xx)? if this is right, i think, substring index is wrong

tested the code with the sample data.

  • validation data: 01, 0123, 05/2009, 11/2009 <= must be ignored(0 result) like you said.

(changed some code for test) (deleted code. added on bottom)

origin code - substring(3,5) make 4 results. and these are not results of the intent you described.

currentFrom = 202005
rowFrom = 202001
currentTo = 202011
rowTo = 202005

so id changed substring(3,5) -> substring(5,7) and results are

currentFrom = 200905
rowFrom = 201001
currentTo = 200911
rowTo = 201005

and after that, 4 if conditions below are make sense

added fiddle

var data = [
    ["01","0123","01/2010","05/2011"],
    ["02","159","01/2010","01/2010"],
    ["03","0525","01/2010","01/2010"]
]

function validatePeriodRange(currentBoxNumber, currentVatReg, currentFrom, currentTo){
    currentFrom = 20 +currentFrom.substring(5,7) + currentFrom.substring(0,2)
    currentTo = 20 +currentTo.substring(5,7) + currentTo.substring(0,2)
    for (gridRow = 0; gridRow < data.length; gridRow++){
        var rowBoxNumber= data[gridRow][0]
        var rowVatReg= data[gridRow][1]
        var rowFrom= data[gridRow][2]
        var rowTo= data[gridRow][3]

        rowFrom= 20 +rowFrom.substring(5,7) + rowFrom.substring(0,2)
        rowTo= 20 +rowTo.substring(5,7) + rowTo.substring(0,2)
        
        
        if(currentBoxNumber == rowBoxNumber && currentVatReg == rowVatReg){

            if(currentFrom <= rowFrom && currentTo >= rowTo){
                return 1
            }

            if(currentFrom <= rowFrom && currentTo >= rowFrom && currentTo <= rowTo){
                return 2
            }

            if(currentFrom >= rowFrom && currentFrom <= rowTo && currentTo >= rowTo){
                return 3
            }

            if(rowFrom <= currentFrom && rowTo >= currentTo){
                return 4
            }

            // if(currentFrom >= rowFrom && rowTo==""){
            //     return 5
            // }

            // if(currentTo >= rowFrom && rowTo==""){
            //     return 6
            // }

        }
    }
    return 0 // No error message
}
console.log(validatePeriodRange('01', '0123', '05/2010', '01/2011'));
console.log(validatePeriodRange('01', '0123', '05/2009', '11/2009'));
console.log(validatePeriodRange('01', '0123', '05/2015', '01/2016'));
console.log(validatePeriodRange('03', '0525', '05/2009', '01/2016'));
/*
all overwrap cases. (except null value) 
cF | rF | cT | rT
rF | cF | rT | cT
cF | rF | fT | cT
rF | cF | cT | rT
*/

user_77259630
Related