extra_separate_devices_from_customers

1_create_ApplicationDevices_table.sql

create table ApplicationDevices
(
    Id                  int identity
        constraint ApplicationDevices_pk
            primary key nonclustered,
    SerialNumber        varchar(64) not null,
    OperationSystemType int,
    CreationDate        datetime2,
    ModifiedDate        datetime2,
    Registered          bit
)
go

create unique index ApplicationDevices_Id_uindex
    on ApplicationDevices (Id)
go

create unique index ApplicationDevices_SerialNumber_uindex
    on ApplicationDevices (SerialNumber)
go

2_migrate_customerStatus_to_ApplicationDevices_with_seeding.sql

-- WARNING: to use this file you must create ApplicationDevices, and must have a valid date in CustomerStatus table
-- Since we will only depends on CustomerStatus table for data migration


-- insert into ApplicationDevices Table

insert into ApplicationDevices(SerialNumber, OperationSystemType, CreationDate, Registered)
select rp.DeviceId, rp.OperationSystemType, rp.CreationDate, rp.Registered from (
-- :-- get all devices
select distinct DeviceId ,
                (select top 1 c.OperationSystemType from CustomerStatus c where c.DeviceId = cs.DeviceId) as OperationSystemType,
                (select top 1 c.CreationDate from CustomerStatus c where c.DeviceId = cs.DeviceId) as CreationDate,
                Registered
from CustomerStatus cs where DeviceId is not null
) rp

-- connect to ApplicationDeviceId

update CustomerStatus
set ApplicationDeviceId = (select top 1 ad.Id from ApplicationDevices ad where ad.SerialNumber = DeviceId)
where DeviceId is not null

-- Remove any device that have no ApplicationDeviceId : [BE AWARE OF THIS STEP]

-- delete CustomerStatus
-- where ApplicationDeviceId is null

-- Delete DeviceId, and Registered Columns from CustomerStatus

drop index CustomerStatus_Registered_index on CustomerStatus
go

alter table CustomerStatus drop column Registered
go

drop index CustomerStatus_DeviceId_Index on CustomerStatus
go

alter table CustomerStatus drop column DeviceId
go

create table ApplicationDevices
(
Id int identity
constraint ApplicationDevices_pk
primary key nonclustered,
SerialNumber varchar(64) not null,
OperationSystemType int,
CustomerId varchar(50),
CreationDate datetime2,
ModifiedDate datetime2,
Registered bit
)
go

create unique index ApplicationDevices_Id_uindex
on ApplicationDevices (Id)
go

create unique index ApplicationDevices_SerialNumber_uindex
on ApplicationDevices (SerialNumber)
go

-- add customer id to table to be able to check how many devices for each customer

WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY CreationDate DESC) AS rn
FROM Logs

), y as
(select * from cte
where cte.rn=1 )

INSERT INTO [dbo].[CustomerStatus]
([CustomerId]
,[CreationDate]
,[CompletedRequestsCount]
,[OperationSystemType]
,[ModifiedDate]
,[ApplicationDeviceId],
DeviceId
)

select cus.Id as CustomerId,
cus.CreationDate as CreationDate,
(
select Count(prs1.Id)
from ServiceRequests as prs1
where prs1.CustomerId = cus.Id AND prs1.Status = 2
)
as CompletedRequestsCount,
ISNULL(y.OSTypeId, 1) as OperationSystemType,
NULL,
NULL,
y.MobileSerialNumber

from Customers cus left outer join y on cus.Id=y.CustomerId

---

WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY MobileSerialNumber ORDER BY CreationDate DESC) AS rn
FROM Logs

), y as
(select * from cte
where cte.rn=1 )
insert into ApplicationDevices(SerialNumber, OperationSystemType,CustomerId, CreationDate, Registered)
select MobileSerialNumber,OperationType,CustomerId,CreationDate,1 from y

---

update CustomerStatus
set ApplicationDeviceId = (select top 1 ad.Id from ApplicationDevices ad where ad.SerialNumber = DeviceId)
where DeviceId is not null

---

update dbo.[Statistics] set StatisticCount =
(select count(Id) from [dbo].[ApplicationDevices] ) where StatisticType=3 -- total download

update dbo.[Statistics] set StatisticCount =
(select count(Id) from CustomerStatus where OperationSystemType=2) where StatisticType=2 -- android devices

update dbo.[Statistics] set StatisticCount =
(select count(Id) from CustomerStatus where OperationSystemType=1) where StatisticType=1 -- ios devices

---