USE [MRCSFA]
GO
/****** Object:  StoredProcedure [dbo].[sp_Sync_Calc_allInventory]    Script Date: 7/17/2019 10:46:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Sync_Calc_allInventory] @CompanyID INT
AS
BEGIN
    TRUNCATE TABLE dbo.DMSAimInventoryList;
    --Insert vô bảng tạm chờ Acu update later
    DECLARE @TempInventoryList TABLE (InventoryCD NVARCHAR(50) NOT NULL,
                                      MD5 NVARCHAR(50) NOT NULL);

    INSERT INTO @TempInventoryList
    VALUES (N'710458', N'ca84a7a7937d91788dee50fd47d45574'),
           (N'708356', N'71efaaef1c6c40f8708a1c5a1a32697c'),
           (N'708357', N'e9d3d1bfe732a4f8a805e2694db473ba'),
           (N'708358', N'ddeef6a100c9184f7e1b7fa4b26ec5ef'),
           (N'710420', N'd61cc6cbea68990ecf87858d28cbf1c9'),
           (N'710421', N'743bd5e0e9b33fa507695e26de054870'),
           (N'710422', N'1f725bd4083e5683cd61bb3056864de8'),
           (N'710459', N'fa5fef1405b3a8677a76d7563fa04624'),
           (N'710457', N'00605dde4bbca50eb3de23220205a597'),
           (N'710538', N'813114baee9fe3e99056da163c0930b4'),
           (N'710456', N'fa5fef1405b3a8677a76d7563fa04624'),
           (N'710445', N'00605dde4bbca50eb3de23220205a597'),
           (N'710537', N'159d9369aea2c93d092e366e61d3fa2b'),
           (N'710444', N'2d64de5bd86c235c88d2b0a980a05769'),
           (N'710546', N'9f616d8a1f3c21e052cdebc0a8638b19'),
           (N'710550', N'fefa595287f4c71f16da32ebcfc73bb6'),
           (N'710554', N'b8d29d4d9f66cb330631eba2051dc475'),
           (N'710548', N'ccdfe33356af6125903cb9b58ae9f05f'),
           (N'710552', N'fefa595287f4c71f16da32ebcfc73bb6'),
           (N'710608', N'096e287e9be1ad196262f78fb2a4e131'),
           (N'710543', N'5e731754437a5672092e6b00999b2eac'),
           (N'710555', N'1ea771f76f9834ff6ad97a7c4680ecc9'),
           (N'710545', N'd5ea2ef4c916597bf4f33569b88fe28c'),
           (N'710544', N'0607c40e54d1a64ecd7b44a615b6977b'),
           (N'710853', N'a03abe4636a4c7548fe1f47ab792ad87'),
           (N'710855', N'e4a73e7fbe0a327166244973fdeb2dd1'),
           (N'710859', N'e0dfdaafb391f4a86805f240e31a6d15'),
           (N'710865', N'0772245f5224440dcdfe8e8c787308ec'),
           (N'710854', N'1eb32fe0af3f6a76ed2cee75cbbce6c6'),
           (N'710858', N'1930b81deecb1bea296e720437b185a2'),
           (N'710860', N'95dcaf4ea0014ce66d7406099abb4274'),
           (N'710864', N'37882fad8c732433a2f9b698e1da6dc5'),
           (N'711009', N'04052868d9da5aed9893a77797b25b42'),
           (N'711010', N'48d81c6a5262562f6ad69b5a76602154'),
           (N'711008', N'714487812ede71ff5dbd1e3606904b07'),
           (N'710851', N'435644f96574247fb947284a82f3a100'),
           (N'710852', N'e5d6b8f1d13161fd73a986bf3c650c6b'),
           (N'711420', N'5f9b6f42c629f53de9a9c7c2dd2f9024'),
           (N'711440', N'025068e8a8bbf41286cfd82c9cb46bda'),
           (N'711417', N'f846c0304ebed32f2d7e2dc7ec0ee8f8'),
           (N'711418', N'5f9b6f42c629f53de9a9c7c2dd2f9024'),
           (N'711421', N'e5b234d6b93fae86dd1d3e02d0fff788'),
           (N'711422', N'4f74e5948a459617aec38d67bd9325bd'),
           (N'711423', N'80afc587ed57d1b6ce5893fd942c191f'),
           (N'711424', N'0a072b04ab5e6e93c6d844a1aa6337e9'),
           (N'711426', N'a8dfdb29fe5740f58a4ed10ba18df524'),
           (N'711427', N'e39ad0234d2236e544f0729ea0f21e17'),
           (N'711429', N'd4c3ae93b23894b93634ebe1187c8fe0'),
           (N'711430', N'27a1e3bc323f58c903181735826fea88'),
           (N'711431', N'172411f9e326feee931c7f2b5175b59f'),
           (N'711433', N'469ed4d49adb8cd52fc9e9123f1f0d0a'),
           (N'711435', N'469ed4d49adb8cd52fc9e9123f1f0d0a'),
           (N'711437', N'a8e817b8b458f4db0a1a9a4ee5cff400'),
           (N'711438', N'0f76623952509d45ee88efc6de6b354a'),
           (N'711439', N'f28cb69dcbe3fa2e7d0bcccc869d10fc'),
           (N'711442', N'f47a1b4c8b1b7d571c96f0c2c2a35551'),
           (N'711446', N'c134f2905e9ea77b3e347d044c25d6fb'),
           (N'711448', N'5b67189ca9dc60b1e00571ea3a4eac80'),
           (N'711449', N'e04c07f9bdff9bc3c3fbe8dcf8620c0f'),
           (N'712379', N'ad30ae70392e1afe234e5667b1969c40'),
           (N'712380', N'c46ca52ac489826226e403dc24b1c515'),
           (N'712381', N'8aa2f2a45a1ee4ce4b58a9fd4b21a711'),
           (N'712386', N'5d7db09ed8dbfc77d157cdb04bb13bea'),
           (N'711532', N'33dc208e1c8e0367f674901fcee6952e'),
           (N'711533', N'a2efd37dee20fb57fd04047b1c87909b'),
           (N'711663', N'b166fb95b1bc1334fe6ac204f1fc6d00'),
           (N'711443', N'b33e7f84d2baf9f99428e88abb53c1c5'),
           (N'711531', N'b6a063d23e08cff330033cfd83279273'),
           (N'711927', N'fbf0537b554d7c338890cf53d2c3b4bf'),
           (N'711928', N'1e11e5d723489ac0245d83d173c1e3d8'),
           (N'708762', N'8c09d979a760212977f305d608067d15'),
           (N'708763', N'f65e8ca213d68984f953894384dfedf7'),
           (N'708764', N'faa5dd4b7a85f34c09ddfadaf768c05f'),
           (N'708769', N'66e38156de36d148bb9879c2f8a651d6'),
           (N'708770', N'28f2f5a3e2c4e01141fbb0a967d3529a'),
           (N'708775', N'c1d6402ac70b987ef9a9fcf6ba415489'),
           (N'708776', N'9dc775e5eb6e63548fbfa495655d7ae5'),
           (N'708777', N'5da58725e2b6e30e0d155ff8ab328dfc'),
           (N'708778', N'5da58725e2b6e30e0d155ff8ab328dfc'),
           (N'708784', N'756148b31f96ef77cc45af2f8c1d6ef8'),
           (N'708785', N'9db980c97d00fb5fd3cda21229bdbb08'),
           (N'708813', N'637ff0f37b2a02151ff853d14857012b'),
           (N'708814', N'8180c111c669b7c0e810b28b1c3f6300'),
           (N'708815', N'07c96cf798d23a71d32ea66f5a46d191'),
           (N'708818', N'38742fcb167a8f17f715f3b9799d513a'),
           (N'708837', N'c7d45b22e8bbe0e599994ee4c62a2a56'),
           (N'712052', N'276450ebaaa6687565c26b473cc5ae51'),
           (N'712136', N'92fb77698e666c4d3577a693ebcce114'),
           (N'712138', N'548b053f0b383f4287f71638137a3903'),
           (N'712139', N'5b32d2d2d5196940fadbb995f27adc39'),
           (N'712184', N'4bf3fa08ea169118e90b3ee7fbb1f20a'),
           (N'711658', N'af3f133952acd9682937d5b39161efe3'),
           (N'711659', N'448f438c9da05b60e6d63b709af68256'),
           --Add more packsize date: 11/15/2018
           -- Please don't rollback data
           (N'713179', N'1bd824e5b5459b8cae910c1f910c1e9a'),
           (N'713180', N'7db92719d684392741317da1c00a1d10'),
           (N'713181', N'e586b8fc1f22325a16d6a8d3d91f71a8'),
           (N'713182', N'3c2e74cc2db51f85f085039838c9ef8c'),
           (N'713183', N'dce6d342a31a009efb2140538c0b1360'),
           (N'713184', N'53f84c02c7b4ac8a9ee49a94fe2196db'),
           (N'713185', N'f7d67d524d29ff6e3367ff74bb444833'),
           (N'713186', N'c45f329c9d6fa3678eac9dafd25a1fb8'),
           (N'713187', N'fd71e0e55fba40bcb06419452a71cbd9'),
           (N'713983', N'f7d67d524d29ff6e3367ff74bb444833'),
           (N'713984', N'c45f329c9d6fa3678eac9dafd25a1fb8'),
           (N'713985', N'fd71e0e55fba40bcb06419452a71cbd9'),
           (N'712250', N'740ce970b48ee1d57675d8617e61a3d0'),
           (N'712251', N'4521cf6de8770212090015bd1153cd95'),
           (N'713837', N'944677d1661c79ee7d837420cd96be56'),
           (N'713838', N'740ce970b48ee1d57675d8617e61a3d0'),
           (N'713839', N'740ce970b48ee1d57675d8617e61a3d0'),
           (N'713840', N'4521cf6de8770212090015bd1153cd95'),
           (N'714190', N'21adf384a571f5092a65e6c5ea8b87d7'),
           (N'712246', N'944677d1661c79ee7d837420cd96be56'),
           (N'712247', N'740ce970b48ee1d57675d8617e61a3d0'),
           (N'712249', N'68b15d411d145bc9c5befe4b5de9ea71'),
		       --Add more packsize date: 03/06/2019
			(N'715280', N'6b0fd241fc50f6d2aba88cc05011eb0e'),
			(N'715405', N'77b9ee9b40bdbb88cf02a4b9e8b322c0'),
			(N'715430', N'eefecb81a357a8b8d0decbb3d13e3146'),
			(N'715672', N'133c3d3fe46d86c28065cebebe3814a2'),
			(N'715673', N'aabfa4790654f4d1b2685e16d4f3ee66'),
			(N'715221', N'd6829ed0d85c9dd0c19dfae9566109f0'),
			(N'715222', N'3ebd0a20c23d97f9c1b56979a1b03e1e'),
			(N'715225', N'1133f980b10a658d7591e978f65362c0');



    -- End insert chờ Acu

    INSERT INTO dbo.DMSAimInventoryList (InventoryID,
                                         InventoryCD,
                                         SalesUnit,
                                         UnitPerCase,
                                         PackageSize,
                                         MD5,
                                         Image)
    SELECT      i.InventoryID, -- InventoryID - int
                i.InventoryCD, -- InventoryCD - nvarchar(50)
                i.SalesUnit, -- SalesUnit - nvarchar(50),
                CAST(ISNULL(u.UnitRate, 1) AS INT),
                ISNULL(iv.Hierachy4CD, ''),
                temp.MD5,
                REPLACE(i.InventoryCD, ' ', '') + '.png'
      FROM       [172.17.0.235].MRCDMS.dbo.InventoryItem i
      LEFT JOIN  [172.17.0.235].MRCDMS.dbo.INUnit u
        ON i.CompanyID      = u.CompanyID
       AND i.InventoryID    = u.InventoryID
       AND u.FromUnit       <> u.ToUnit
       AND u.FromUnit       = i.SalesUnit
      LEFT JOIN  [172.17.0.235].[MRCDMS].[dbo].[DMSViewInventoryItem] iv
        ON i.CompanyID      = iv.CompanyID
       AND i.InventoryID    = iv.InventoryID
      LEFT JOIN @TempInventoryList temp
        ON temp.InventoryCD = i.InventoryCD
     WHERE      i.CompanyID = @CompanyID
       AND      i.ItemStatus NOT IN ( 'IN' );

    -- Gen SP cho all SM
    INSERT INTO dbo.DMSAimInventoryItem (UserName,
                                         InventoryID,
                                         InventoryCD,
                                         InventoryName,
                                         ShortName,
                                         UnitPerCase,
                                         SalesUnit,
                                         Kparam,
                                         AllowSales,
                                         Enddate,
                                         ProductKind,
                                         Volume,
                                         Weight,
                                         IsExclusive,
                                         PurchaseUnit,
                                         PackageSize,
                                         Properties,
                                         Image,
                                         SubCategoryCode,
                                         MD5)
    SELECT      e.EmployeeICD, -- UserName - nvarchar(50)
                l.InventoryID, -- InventoryID - int
                l.InventoryCD, -- InventoryCD - nvarchar(50)
                ISNULL(i.Descr, ''), -- InventoryName - nvarchar(255)
                ISNULL(i.UsrShortName, ISNULL(i.Descr, '')), -- ShortName - nvarchar(50)
                l.UnitPerCase, -- UnitPerCase - int
                l.SalesUnit, -- SalesUnit - nvarchar(50)
                0.0, -- Kparam - float
                CASE
                     WHEN e.SellingCategoryID IS NOT NULL THEN (CASE
                                                                     WHEN i.UsrIsExclusive = 1
                                                                       OR c.InventoryID IS NOT NULL THEN 1
                                                                     ELSE 0 END)
                     WHEN i.UsrPOSMType IS NOT NULL THEN 0
                     ELSE 1 END AllowSales, -- AllowSales - int
                GETDATE(), -- Enddate - date
                0, -- ProductKind - int
                i.BaseItemVolume,
                i.BaseItemWeight,
                ISNULL(i.UsrIsExclusive, 0),
                i.PurchaseUnit,
                ISNULL(l.PackageSize, ''), --(Trieu)
                ISNULL(i.UsrFeatureBenefit, ''), --(Trieu) Get Properties from Acu
                REPLACE(l.InventoryCD, ' ', '') + '.png', --(Trieu) Get Image file
                iv.Hierachy3CD AS SubCategory,
                l.MD5
      FROM      [172.17.0.235].MRCDMS.dbo.InventoryItem i
     INNER JOIN dbo.DMSAimInventoryList l
        ON i.InventoryID = l.InventoryID
       AND i.CompanyID = @CompanyID
      LEFT JOIN [172.17.0.235].[MRCDMS].[dbo].[DMSViewInventoryItem] iv -- 2019-06-12 Nam NN them [172.17.0.235].
        ON i.CompanyID = iv.CompanyID
       AND i.InventoryID = iv.InventoryID
     CROSS JOIN dbo.DMSAimEmployeeList e
      LEFT JOIN  [172.17.0.235].MRCDMS.dbo.DMSViewProductBySellingCat c
        ON i.CompanyID         = c.CompanyID
       AND i.InventoryID       = c.InventoryID
       AND c.SellingCategoryID = e.SellingCategoryID
     WHERE      l.InventoryCD <> 'TEST';

    -- trieu.dump dump item must have
    SELECT InventoryCD,
           SequenceMustHave
      INTO #TempMustHave
      FROM dbo.DMSAimInventoryItem
     WHERE 1 = 2;
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('710457', 1);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('710459', 2);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('710538', 3);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('710458', 4);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('710445', 5);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('710456', 6);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('710442', 7);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('710444', 8);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('710537', 9);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('710443', 10);

    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('712139', 11);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('712138', 12);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('712184', 13);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('712052', 14);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('708818', 15);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('708784', 16);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('708930', 17);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('708783', 18);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('708813', 19);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('708814', 20);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('708837', 21);
    INSERT INTO #TempMustHave (InventoryCD,
                               SequenceMustHave)
    VALUES ('708815', 22);

    UPDATE i
       SET i.IsMustHave = 1,
           i.SequenceMustHave = tmh.SequenceMustHave
      FROM dbo.DMSAimInventoryItem i
      JOIN #TempMustHave tmh
        ON tmh.InventoryCD = i.InventoryCD
     WHERE i.AllowSales = 1;
    DROP TABLE #TempMustHave;


    -- trieu.dump dump item focus
    UPDATE dbo.DMSAimInventoryItem
       SET IsFocus = 1
     WHERE InventoryCD IN ( '711421', '711423', '711426', '711418', '711422', '711424', '711420', '710460', '710461',
                            '710462', '710539', '710556', '710557', '710558', '710559', '711366', '711367', '711368',
                            '711369', '712379', '712380', '712381', '712386', '708777', '708775', '708778', '708776',
                            '712052', '712184', '712138', '712139' );

    -- Lay gia ban
    INSERT INTO dbo.DMSAimSalesPrice (UserName,
                                      DistrictID,
                                      CustomerPriceClassID,
                                      InventoryCD,
                                      Price,
                                      WholeSalePrice,
                                      ConsumerPrice)
    SELECT      price.UserName, -- UserName - nvarchar(50)
                s.DistrictID, -- DistrictID - nvarchar(50)
                s.CustomerPriceClassID, -- CustomerPriceClassID - nvarchar(50)
                ii.InventoryCD, -- InventoryCD - nvarchar(50)
                s.Price, -- Price - float
                '0', -- Insert later (Trieu)
                '0' --Insert later (Minh)
      FROM      dbo.DMSTempSalesPrice s
     INNER JOIN (   SELECT      el.EmployeeICD AS UserName,
                                pd.ProvinceID AS DistrictID
                      FROM      dbo.DMSAimEmployeeList el
                     INNER JOIN  [172.17.0.235].MRCDMS.dbo.DMSRouteSetting r
                        ON el.EmployeeID              = r.SalespersonID
                       AND el.RouteCD                 = r.RouteCD
                     INNER JOIN  [172.17.0.235].MRCDMS.dbo.DMSSellingProvinceHeader ph
                        ON ph.CompanyID               = r.CompanyID
                       AND ph.SellingProvinceHeaderID = r.SalesAreaID
                     INNER JOIN  [172.17.0.235].MRCDMS.dbo.DMSSellingProvinceDetail pd
                        ON pd.CompanyID               = ph.CompanyID
                       AND pd.RefNbr                  = ph.RefNbr
                     GROUP BY el.EmployeeICD,
                              pd.ProvinceID) price
        ON price.DistrictID = s.DistrictID
     INNER JOIN DMSAimInventoryItem ii
        ON ii.UserName      = price.UserName
       AND ii.AllowSales    = 1
       AND ii.InventoryID   = s.InventoryCD;

END;