----------------ACCOUNT MST MAIN
INSERT INTO accountsmst (id, mainac, typecode, chs_id, accountgroup, accountname, bsgroupcode, oldmainac, tdspercent)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id,CONCAT('J', FORMAT(ROW_NUMBER()OVER( ORDER BY mainac),'000')) AS newmainac, 
IIF(description IN ('Not Applicable','Assests','Liabilities','Expense','Income'),'NA',IIF(description = 'Customer','Member',IIF(description = 'Supplier','Vendor',
IIF(description = 'Bank / Cash',IIF(achead = 'PETTY CASH','Cash','Bank'),description))))
AS typecode,'J0001' AS chs_id, IIF(B.flag ='BL','Liability', IIF(B.flag ='BA','Asset', IIF(B.flag ='PE','Expenses', IIF(B.flag ='PI','Income','')))) AS accountgroup,
achead AS accountname, AC.bsgroup, mainac AS oldmainac, 0 AS tdspercent
FROM blazorchs..accounts AC
LEFT JOIN blazorchs..actypemst AT ON AC.actype = AT.actype
LEFT JOIN blazorchs..Bsplgroups B ON AC.Bsgroup = B.groupcode
WHERE B.Type = 'B';

UPDATE accountsmst SET  accountgroup = 'Liability' WHERE oldmainac IN ('030100', '035000', '040000', '041000');

CREATE TABLE #temp
(
    NewMainAc VARCHAR(20),
    OldMainAc VARCHAR(20)
);


INSERT INTO #temp (NewMainAc, OldMainAc)
VALUES
('0001','010000'),
('0100','019900'),
('0200','020000'),
('0300','020100'),
('0400','020300'),
('0500','020400'),
('1000','030100'),
('1100','035000'),
('1200','040000'),
('1300','041000'),
('2000','050000'),
('2100','051000'),
('2200','051100'),
('2300','051200'),
('2400','051300'),
('2500','052000'),
('2600','052100'),
('2700','052101'),
('3000','053204'),
('3100','053205'),
('3200','053206'),
('3800','054200'),
('5000','061100'),
('5001','061200'),
('5050','061700'),
('5051','061800'),
('5100','063800'),
('5101','063900'),
('5150','064000'),
('5200','064100'),
('5201','064200'),
('5250','064300'),
('5251','064400'),
('5300','064500'),
('5301','064600'),
('5350','065100'),
('5351','065200'),
('5400','065300'),
('5401','065400'),
('5450','065600'),
('5451','065700'),
('5500','066000'),
('5501','066100'),
('5550','066200'),
('5551','066300'),
('5600','066900'),
('5650','067000'),
('5700','067001'),
('5601','067400'),
('5651','067500'),
('6000','071000'),
('6400','071400'),
('6500','080000'),
('7000','082000'),
('7050','082100'),
('7100','082200'),
('7150','082300'),
('7500','082400'),
('7200','082500'),
('7250','082600'),
('7300','082700'),
('7400','083000'),
('6100','083900'),
('6200','084000'),
('6300','084400'),
('6550','084500'),
('6600','085000'),
('6650','085200'),
('6700','085300'),
('6750','086000'),
('6800','086001'),
('6850','087000'),
('6900','087100'),
('6950','087101'),
('6960','087200'),
('8000','090000'),
('8050','092200'),
('8100','092400'),
('8150','092500'),
('8200','092600'),
('9000','120000'),
('9010','120001'),
('9020','120050'),
('9030','120055'),
('9040','120056'),
('9050','120057'),
('9060','120060'),
('9070','120065'),
('9080','120070'),
('9090','120075'),
('9100','120080'),
('9110','120100'),
('9120','120200'),
('9130','120300'),
('9140','120400'),
('9150','120800'),
('9160','120801'),
('9170','120900'),
('9180','121100'),
('9190','121300'),
('9200','121400'),
('9210','121500'),
('9220','121600'),
('9230','121700'),
('9240','121900'),
('9250','122000'),
('9260','122300'),
('9270','123000'),
('9280','123100'),
('9290','123200'),
('9300','123300'),
('9310','124000'),
('9320','124200'),
('9330','124300'),
('9340','125100'),
('9350','125700'),
('9360','125900'),
('9370','126000'),
('9380','126900'),
('9390','126901'),
('9400','126950'),
('9410','127000'),
('9420','127400'),
('9430','127401'),
('9440','127500'),
('9450','127700'),
('9460','127800'),
('9470','127900'),
('9480','127901'),
('9490','128000'),
('9500','128100'),
('9510','129000'),
('9520','129100'),
('9530','130000'),
('9540','140000'),
('9550','170000'),
('9560','180000'),
('9570','899999'),
('9580','900000'),
('9590','900001')


UPDATE A
SET A.MainAc = M.NewMainAc
FROM AccountsMst A
JOIN #temp M
    ON A.OldMainAc = M.OldMainAc;

UPDATE accountsmst SET tdspercent = 2 WHERE oldmainac = '053205';
UPDATE accountsmst SET tdspercent = 1 WHERE oldmainac = '053204';
UPDATE accountsmst SET tdspercent = 10 WHERE oldmainac = '053206';


INSERT INTO accountsmst1 (id, mainac,oldmainac, subac, acname, isactive, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, A.mainac, AC1.mainac AS oldmainac,  RIGHT(subac,4) AS subac, acname, isactive,'J0001'
FROM accountsmst A
LEFT JOIN blazorchs.. accounts1 AC1 ON A.oldmainac = AC1.mainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.chs_id = 'J0001';

WITH A(mainac, subac)AS
(
    SELECT mainac, subac  FROM blazorchs..BillChs1 
    UNION ALL
    SELECT mainac, subac  FROM blazorchs..journalvoucher1 
    UNION ALL
    SELECT mainac, subac  FROM blazorchs..voucher1 
    UNION ALL
    SELECT mainac, subac  FROM blazorchs..opgbalances 
    UNION ALL
    SELECT controlac AS mainac, '000001' AS subac  FROM blazorchs..VendorMst 
    UNION ALL 
    SELECT controlac AS mainac, '000001' AS subac  FROM blazorchs..MemberMst 
    UNION ALL
    SELECT tdscode AS mainac, '000001' AS subac FROM blazorchs..BillChs WHERE tdscode != ''
)


DELETE FROM accountsmst1 WHERE  oldmainac+CONCAT('00',subac) NOT IN (SelECT mainac+subac COLLATE SQL_Latin1_General_CP1_CI_AS FROM A );

DELETE FROM accountsmst WHERE oldmainac NOT IN (SELECT oldmainac FROM accountsmst1 );

-- INSERT INTO societies(id, chs_id, chs_name, created_at) VALUES
-- (LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)),'J0001','Jasmine CHS',GETDATE())

--------------Members
INSERT INTO members (id, salutation, dob, gender, bldg_flat, area, email, member_type, doa, notes, member_id, first_name, last_name, mobile, servicegroupcode, updated_at, invoiceupto, controlac, telegram_id, is_active, telegramlinked, transferfrom, chs_id, role) 
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, IIF(title = '1','Mr.',IIF(title = '2','Ms.', IIF(title = '3','Dr.','Other')))  AS salutation, dob, IIF(gender = '1','Male',IIF(gender = '2','Female','Other')) AS gender, 

CONCAT(
        CASE 
            WHEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(wing, '-', ''), ',', ''), '/', ''))) <> '' 
            THEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(wing, '-', ''), ',', ''), '/', ''))) 
            ELSE '' 
        END,

        CASE 
            WHEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(bldg, '-', ''), ',', ''), '/', ''))) <> '' 
            THEN 
                CASE 
                    WHEN LTRIM(RTRIM(wing)) <> '' THEN '-' ELSE '' 
                END +
                LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(bldg, '-', ''), ',', ''), '/', '')))
            ELSE '' 
        END,

        CASE 
            WHEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(flatno, '-', ''), ',', ''), '/', ''))) <> '' 
            THEN 
                CASE 
                    WHEN LTRIM(RTRIM(wing)) <> '' OR LTRIM(RTRIM(bldg)) <> '' THEN '-' ELSE '' 
                END +
                LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(flatno, '-', ''), ',', ''), '/', '')))
            ELSE '' 
        END
    ) AS bldg_flat

, area, email, 'Resident' AS membertype, admitted AS doa, notes, RIGHT(memberid,4) AS member_id, LEFT(Name, CHARINDEX(' ', Name + ' ') - 1) AS first_name, LTRIM(SUBSTRING(Name, CHARINDEX(' ', Name + ' '), LEN(Name))) AS last_name, Cell1 AS mobile, servicegroupcode, updatedon AS updated_at, invoiceupto, A.mainac AS  controlac, '' AS telegram_id, IIF(ceased = '31-Mar-49' AND typeofmember != '12',1,0), IIF('' = '',0,1) AS telegramlinked, RIGHT(transferinfo,6) AS transferfrom,'J0001', 'User' AS role FROM Blazorchs..Membermst M
LEFT JOIN accountsmst A ON M.controlac = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE chs_id = 'J0001';

------------StdTariff
INSERT INTO service_charges (id, service_code, service_name, amount, is_active, mainac, chs_id,  subac)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, tariffcode AS service_code, tariffname AS service_name, amount, IIF(S.isactive = 'Y','1','0') AS is_active, A.mainac AS account_code,
'J0001' AS chs_id,  A.subac
FROM blazorchs.. stdtariffmst S
LEFT JOIN accountsmst1 A ON S.mainac + S.subac = A.oldmainac + CONCAT('00',A.subac) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE tariffcode !='999997' AND chs_id = 'J0001';;

-----------Service Group
INSERT INTO service_groups (id, service_group_code, service_group_name, isactive, chs_id, service_charges_codes)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, RIGHT(s.GroupCode, 4) AS service_group_code, s.GroupName, s.isactive, 'J0001' AS chs_id,
       STRING_AGG(s1.TariffCode, ',') AS service_charges_codes
FROM blazorchs.. ServiceGroupMst s
LEFT JOIN blazorchs.. ServiceGroupMst1 s1 ON s.GroupCode = s1.GroupCode
GROUP BY s.id, s.GroupCode, s.GroupName, s.isactive;

-------------Invoices
INSERT INTO invoices (id, chs_id, invoice_no, fylabel, dated, memberid, notes, total_amount, gsttype, status, period)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id,'J0001' AS chs_id, invoiceno, 
LEFT(YearInvoiceNo, 4)+'-'+Substring(yearinvoiceno,5,2), dated, RIGHT(memberid,4) AS member_id, notes, amount, COALESCE(Description,'NA') AS gsttype,
IIF(createtype ='S','System','User') Status, period
FROM blazorchs.. invoicechs I
LEFT JOIN blazorchs..GstType G ON I.gsttype = G.Type;


-----------Invoice Line
INSERT INTO invoice_details (id, chs_id, invoiceno, fylabel, service_charges_code, amount)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id,'J0001' AS chs_id, RIGHT(yearinvoiceno,6),  LEFT(YearInvoiceNo, 4)+'-'+Substring(yearinvoiceno,5,2), TariffCode, amount
FROM blazorchs.. Invoicechs1;


-----------opgos
INSERT INTO opgbalances (id, mainac, subac, drcr, amount, fylabel, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, A.mainac AS accountcode, RIGHT(subac,4) AS subac, drcr, amount, yearlabel AS fylabel,'J0001' AS chs_id
FROM blazorchs..opgos O
LEFT JOIN accountsmst A ON O.mainac= A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE chs_id = 'J0001';

----------opgbal
INSERT INTO opgbalances (id, mainac, subac, drcr, amount, fylabel, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, A.mainac AS accountcode, A.subac, drcr, amount, yearlabel,'J0001'
FROM blazorchs..opgbalances O
LEFT JOIN accountsmst1 A ON O.mainac + O.subac = A.oldmainac + CONCAT('00',A.subac) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE chs_id = 'J0001';



INSERT INTO BillChs (id, billno, dated, vendor_id, fylabel, billamt, vendorbillamt, vendorbillno, vendorbilldate, tdsamount, tdscomputedon, tdscode, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, billno, dated, RIGHT(vendorid,4) AS vendor_id, LEFT(yearbillno, 4)+'-'+Substring(yearbillno,5,2), billamt, vendorbillamt, vendorbillno, vendorbilldate, 
tdsamount, computetds, COALESCE(A.mainac,'J000'),'J0001'
FROM blazorchs..BillChs O
LEFT JOIN accountsmst A ON O.tdscode= A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE chs_id = 'J0001';

INSERT INTO BillChs1 (id, fylabel, billno, amount, mainac, subac, notes, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, LEFT(yearbillno, 4)+'-'+Substring(yearbillno,5,2), RIGHT(yearbillno,6), amount, A.mainac, A.subac, notes, 'J0001'
FROM blazorchs.. BillChs1 O
LEFT JOIN accountsmst1 A ON O.mainac + O.subac= A.oldmainac + CONCAT('00',A.subac) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE chs_id = 'J0001';


------------------------vendors control ac is pending to handle & no. of records are not there
INSERT INTO voucher (id, voucherno, dated, vouchertype, cheqno, cheqdate, drawnon, amount, bankcode, oldbankcode, recodate, fylabel, refid, chs_id) 
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, voucherno, dated, IIF(vouchertype = 'P','Payment','Receipt') AS vouchertype,
CONCAT(IIF(TRIM(COALESCE(UtrNo,'')) IN ('0'),'', TRIM(COALESCE(UtrNo,''))), IIF(TRIM(COALESCE(cheqno,'')) IN ('0'),'', TRIM(COALESCE(cheqno,'')))) AS cheqno, 
cheqdate, drawnon, amount, A.mainac AS bankcode, bankcode, recodate, LEFT(yearvoucherno, 4)+'-'+Substring(yearvoucherno,5,2) AS fylabel, 0, 'J0001' FROM blazorchs..voucher V
LEFT JOIN accountsmst A ON V.bankcode = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.chs_id = 'J0001';

------------------------------
INSERT INTO voucher1 (id, voucherno, mainac, subac, oldmainac, drcr, amount, notes, fylabel, tdscode, tdsamount, interestamt, chs_id) 
(
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, voucherno, A1.mainac, A1.subac, V1.mainac AS oldmainac, drcr, amount, notes, LEFT(yearvoucherno, 4)+'-'+Substring(yearvoucherno,5,2) AS fylabel, 
'00' AS tdscode, 0 AS tdsamount, 0 AS interestamt, 'J0001' FROM blazorchs..voucher1 V1
LEFT JOIN accountsmst1 A1 ON V1.mainac + V1.subac = A1.oldmainac + CONCAT('00',A1.subac) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A1.chs_id = 'J0001' AND refdoc = 'GL'

UNION ALL

SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, voucherno, A1.controlac AS mainac, A1.member_id AS subac, V1.mainac AS oldmainac, drcr, amount, 
V1.notes, LEFT(yearvoucherno, 4)+'-'+Substring(yearvoucherno,5,2) AS fylabel, 
'00' AS tdscode, 0 AS tdsamount, 0 AS interestamt, 'J0001' FROM blazorchs..voucher1 V1
LEFT JOIN accountsmst A ON V1.mainac = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN members A1 ON A.mainac + V1.subac = A1.controlac + CONCAT('00',A1.member_id) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A1.chs_id = 'J0001' AND V1.refdoc IN('SI','OA','AD')

UNION ALL

SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, voucherno, '' AS mainac, A1.vendor_id AS subac, V1.mainac AS oldmainac, drcr, amount, 
V1.notes, LEFT(yearvoucherno, 4)+'-'+Substring(yearvoucherno,5,2) AS fylabel, 
'00' AS tdscode, 0 AS tdsamount, 0 AS interestamt, 'J0001' FROM blazorchs..voucher1 V1
LEFT JOIN accountsmst A ON V1.mainac = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN vendors A1 ON A.mainac + V1.subac = '' + CONCAT('00',A1.vendor_id) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A1.chs_id = 'J0001' AND V1.refdoc IN('PB','OA','AD')
)

----sysparameters 
INSERT INTO sysparameters (sysname, name, isactive, dated, chs_id)
SELECT S.sysname, S.name, S.isactive, S.dated, 'T0001'
FROM blazorchs..sysparameters S
WHERE sysname IN ('AIInsights', 'SendEmailCopy', 'InterestInvoicesGeneratedUpto')