
update CpTbl 
set isUnattached = 1, 
	AccountNumberAsInt = CASE WHEN isnumeric(Account)=1 THEN cast(Account AS int) ELSE NULL END,
	SerialNumberAsInt  = CASE 
						 WHEN SerialNumber IS NULL OR SerialNumber = '' THEN NULL
						 WHEN (LEN(serialnumber) = 8 AND SerialNumber NOT LIKE '%[g-z]%') THEN CONVERT(bigint, CONVERT(varbinary, '0x' + [SerialNumber], 1))
						 ELSE NULL END,
	MobileNumberAsInt  = CASE 
						 WHEN SimNum IS NULL THEN NULL
						 WHEN try_cast(stuff(SimNum, 1, patindex('%[0-9]%', SimNum) - 1, '') AS bigint) IS NULL THEN NULL 
						 ELSE cast(stuff(SimNum, 1, patindex('%[0-9]%', SimNum) - 1, '') AS bigint) END,
	FullCpVersionAsInt = CASE WHEN MajorSoftwareVersion IS NULL THEN NULL
                         ELSE MajorSoftwareVersion * 1000000 + MinorSoftwareVersion * 1000 + MicroSoftwareVersion END;

update c 
set c.VendorId = u.VendorId ,
	c.isUnattached = 0
from CpTbl c
	join CpUsrTbl us on c.CpId = us.iCpId
	join CmsUserTbl u on u.CmsUserId = us.IUSRID;


insert into PanelAttributes ([Id], [PanelId], [AttributeKey], [AttributeValue], [UpdatedUtc])
select newid(),pab.panelid,'BleSoftwareVersionAsInt',
        case when pab.AttributeValue is null or pab.AttributeValue='' then null
        else cast(PARSENAME(pab.AttributeValue,3) as int) * 1000000 +
                    cast(PARSENAME(pab.AttributeValue,2) as int) * 1000 +
                    cast(PARSENAME(pab.AttributeValue,1) as int) END,
		  GETDATE()
FROM cptbl cp
    join PanelAttributes pab on pab.PanelId=cp.CpId and pab.AttributeKey='BleSoftwareVersion'
where 0=(select count(1) from PanelAttributes b where b.PanelId=cp.cpid and AttributeKey='BleSoftwareVersionAsInt');


insert into PanelAttributes ([Id], [PanelId], [AttributeKey], [AttributeValue], [UpdatedUtc])
select newid(),pav.panelid,'VoiceFileVersionAsInt',
        case when pav.AttributeValue is null or pav.AttributeValue='' then null
        else cast(PARSENAME(pav.AttributeValue,3) as int) * 1000000 +
                    cast(PARSENAME(pav.AttributeValue,2) as int) * 1000 +
                    cast(PARSENAME(pav.AttributeValue,1) as int) END,
		  GETDATE()
FROM cptbl cp
    join PanelAttributes pav on pav.PanelId=cp.CpId and pav.AttributeKey='VoiceFileVersion'
where 0=(select count(1) from PanelAttributes b where b.PanelId=cp.cpid and AttributeKey='VoiceFileVersionAsInt');

	
update VendorTbl set ParentId = null where ParentId=0;
