Reports

class Reports {
    constructor(sql) {
        this.sql = sql;
    }

    getContainers(user, cb) {
        if (!user.tenant) {
            return cb("Have no access");
        }
        this.sql.query(`select s.[Id] as [id], s.[Login] as [login], CASE WHEN [Password] is null THEN 0 ELSE 1 END as activated, s.[Detail] as supervisorDetail, c.[Id] as containerId, c.[Detail] as containerDetail, c.[Odometer] as odometer
                        from [dbo].[Container2Group] c2g
                        inner join [dbo].[Containers] c on c2g.[ContainerId] = c.[Id] and c2g.[Archive] = 0 and c2g.[GroupId] = @groupId
                        inner join [dbo].[Supervisors] s on s.[Id] = c.[OwnerId]
					    `,
            {
                "groupId": [user.groupId, this.sql.types.BigInt()],
            },
            (err, rows) => {
                if (err) {
                    return cb(err);
                }
                cb(null, rows.dataset[0]);
            });
    }

    getContainer(user, containerId, cb) {
        if (!user.tenant) {
            return cb("Have no access");
        }
        this.sql.query(`select s.[Id] as [id], s.[Login] as [login], s.[Detail] as supervisorDetail, CASE WHEN [Password] is null THEN 0 ELSE 1 END as activated, c.[Id] as containerId, c.[Odometer] as odometer, c.[Detail] as containerDetail, JSON_QUERY([Status],'$.km') AS [current], JSON_VALUE([Status],'$.argv.target') AS [target]
                        from [dbo].[Container2Group] c2g
                        inner join [dbo].[Containers] c on c2g.[ContainerId] = c.[Id] and c2g.[Archive] = 0 and c2g.[GroupId] = @groupId and c.[Id] = @containerId
                        inner join [dbo].[Supervisors] s on s.[Id] = c.[OwnerId]
                        left outer join [dbo].[Contracts] d on d.[ContainerId] = c.[Id]
					    `,
            {
                "groupId": [user.groupId, this.sql.types.BigInt()],
                "containerId": [containerId, this.sql.types.BigInt()]
            },
            (err, rows) => {
                if (err) {
                    return cb(err);
                }
                cb(null, rows.dataset[0].length ? rows.dataset[0][0] : []);
            });
    }

    getUsers(user, cb) {
        if (!user.tenant) {
            return cb("Have no access");
        }
        this.sql.query(`select  s.[Id] as userId, g.[Id] as groupId, s.[Login] as login, s.[Detail] as userDetail, s.[Active] as userActive, s.[Role] as userRole, g.[Detail] as groupDetail, g.[Archive] as groupArchive
                        from [dbo].[Supervisors] s
                        left join [dbo].[Groups] g on g.[Id]=s.[GroupId]
                        where s.[Tenant] = @tenantId`,
            {
                "tenantId": [user.tenant, this.sql.types.BigInt()]
            },
            (err, rows) => {
                if (err) {
                    return cb(err);
                }
                cb(null, rows.dataset[0]);
            });
    }

    getUser(user, userId, cb) {
        if (!user.tenant) {
            return cb("Have no access");
        }
        this.sql.query(`select  s.[Id] as userId, g.[Id] as groupId, s.[Login] as login, s.[Detail] as userDetail, s.[Active] as userActive, s.[Role] as userRole, g.[Detail] as groupDetail, g.[Archive] as groupArchive
                        from [dbo].[Supervisors] s
                        left join [dbo].[Groups] g on g.[Id]=s.[GroupId]
                        where s.[Tenant] = @tenantId and s.[Id] = @userId`,
            {
                "tenantId": [user.tenant, this.sql.types.BigInt()],
                "userId": [userId, this.sql.types.BigInt()]
            },
            (err, rows) => {
                if (err) {
                    return cb(err);
                }
                cb(null, rows.dataset[0].length ? rows.dataset[0][0] : []);
            });
    }

    getWeekDistance(user, containerId, begin, end, cb){
        this.sql.query(`SELECT avg([Routes]) as [Routes], ContainerId, [Day], DATENAME(WEEKDAY, [Day]) as [DayWeek], avg([Distance]) as [DayDist], avg([MaxDist]) as [MaxDist], avg([MinDist]) as [MinDist] 
                        FROM (
                            SELECT count(s.DeviceId) as [Routes], sum(s.Distance) as [Distance], max(s.Distance) as [MaxDist], min(s.Distance) as [MinDist], dateadd(DAY, 0, datediff(day, 0, s.[Begin])) as [Day], s.ContainerId, s.DeviceId 
                            FROM [dbo].[Sessions] s
                            inner join [dbo].[Supervisor2Container] c on s.ContainerId = c.[ContainerId] and c.[SupervisorId] = @userId and c.[Archive] = 0
                            WHERE s.ContainerId = @containerId
                            GROUP BY dateadd(DAY, 0, datediff(day, 0, s.[Begin])), s.ContainerId, s.DeviceId
                        ) as alldev
                        where [Day] between @begin and @end
                        GROUP BY ContainerId, [Day]
                        order by [Day]`,
            {
                "containerId": [containerId, this.sql.types.BigInt()],
                "userId": [user.id, this.sql.types.BigInt()],
                "begin": [begin, this.sql.types.VarChar()],
                "end": [end, this.sql.types.VarChar()]
            },
            (err, rows) => {
                if (err) {
                    return cb(err);
                }
                cb(null, rows.dataset[0]);
            });
    }
}
module.exports = Reports;