eXtra_update_statistics

main.sql

/*
    public enum StatisticsType
    {
        IosDevices = 1,
        AndroidDevices = 2,
        TotalDownloads = 3,
        ServiceRequests = 4,
        Sharing = 5
    }

    public enum OperationSystemType
    {
      Android = 1,
      Ios = 2,
      Both = 3,
    }

    public enum OperationType
    {
        ApplicationStart = 1,
        ApplicationEnd = 2,
        ServiceRequestStart = 3,
        ServiceRequestSubmit = 4,
        NewDevice = 5,
        Sharing = 6,
    }
 */


-- update android devices
update [Statistics]
SET StatisticCount = (select Count(*) from CustomerStatus where OperationSystemType = 1), CreationDate = GETDATE()
where Id = 2

-- update ios devices
update [Statistics]
SET StatisticCount = (select Count(*) from CustomerStatus where OperationSystemType = 2), CreationDate = GETDATE()
where Id = 1



/* === Update Total Downloads: we should depend on OperationType=5 :[NewDevice], but this implementation
not done yet, in the mobile app, but we must populate the old data
so the only way to solve this issue, is to depend on MobileSerialNumber, some devices will not provide it,
But we must do that
 */

-- this data may appear as a not real, in the test DB, because the total of android + ios, devices, will be more than
-- the total of downloads, which is not possible, but this problem related to logs system
-- and there is no way to solve it here
-- we can use another solution, and this is to get the current state from GooglePlay | AppleStore Dashboards
-- and manually set it here, as an initial value, and IMHO: this is the best solution
-- but for now, have fun with the next solution :)

update [Statistics]
SET StatisticCount = (select Count(distinct MobileSerialNumber) from Logs as logs)
--                          +
                     -- ios
--                      (select StatisticCount from [Statistics] where Id = 1) +
                     -- android
--                      (select StatisticCount from [Statistics] where Id = 2)
, CreationDate = GETDATE()
where Id = 3

-- ServiceRequests Count
update [Statistics]
SET StatisticCount = (select COUNT(*) from ServiceRequests), CreationDate = GETDATE()
where Id = 4

-- Set Date Of Share Statistics

update [Statistics]
SET CreationDate = GETDATE()
where Id = 5