using System;

public class Program

{

    public static void Main()

    {

        Console.WriteLine(GetSparePartQuery(""));

    }

    private static string GetSparePartQuery(string vesselID)

{

var vesselName = "Vessel";

return $"SELECT I.MarkDeleted, I.ItemName AS 'Name', " +

$"G.[GroupName] AS 'GroupName', " +

$"S.[StorageName] AS 'DefaultStorageName', " +

$"CAST(I.MinStockLevel as int) AS MinQ, " +

$"CAST(I.MaxStockLevel as int) AS MaxQ, " +

$"CAST(I.Qty as int) AS 'StartingQuantity', " +

$" ISNULL(I.Description, 'Not set') AS 'Description', " +

$"I.ItemNo AS 'Serial', " +

$"I.Model AS 'Specification', " +

$"'' AS 'Remarks', " +

$"CAST(I.IsCritical as bit) AS 'Critical', " +

$" A.Company AS 'ManufacturerName', " +

$"I.PartNo1 AS 'ManufacturePart'," +

$"I.PartNo2 AS 'SupplierPart'," +

$" A2.Company AS 'SupplierName', " +

$" I.LASTUPDATED AS 'ExternalTimestamp', " +

$" cast(I.ID as varchar(MAX)) + '@' + '{vesselID}' AS 'ExternalID', " +

$" I.Code AS 'Barcode', " +

$"Stuff((SELECT Cast(CC.id AS VARCHAR(max)) + '@' + @vesselID AS ExternalID " +

$" , " +

$" Cast(CC.Qty AS INT) " +

$" AS Quantity, " +

$" cc.StorageID, " +

$" ISNULL(ss.StorageName, '[not set]') As StorageName, " +

$" cc.MasterStorage AS Master, " +

$" cc.MarkDeleted AS MarkDeleted " +

$" FROM spectwosuite.ItemsStorages CC " +

$" INNER JOIN spectwosuite.items IT ON IT.id = CC.itemid " +

​

$" INNER JOIN spectwosuite.Storages ss ON ss.id = CC.StorageID " +

$" AND CC.ItemID = I.ID " +

$" FOR xml path('')), 1, 0, '') AS 'SpareStorageJsonString' " +

$"FROM spectwosuite.Items I " +

$"INNER JOIN spectwosuite.Groups G ON G.ID = I.GroupID " +

$"LEFT JOIN spectwosuite.ItemsStorages IT ON IT.ItemID = I.ID AND IT.MasterStorage = 1 AND IT.MarkDeleted = 0" +

$"LEFT JOIN spectwosuite.Storages S ON S.ID = IT.StorageID AND S.MarkDeleted = 0 " +

$"LEFT JOIN spectwosuite.Addresses A ON A.ID = I.ManufacturerID AND A.MarkDeleted = 0 " +

$"LEFT JOIN spectwosuite.ItemsSuppliers IP ON IP.ItemID = I.ID AND IP.MasterSupplier = 1 AND IP.MarkDeleted = 0" +

$"LEFT JOIN spectwosuite.Addresses A2 ON A2.ID = IP.AddressID AND A2.MarkDeleted = 0 " +

$"WHERE (I.LASTUPDATED > @LastUpdate " +

$"or G.LASTUPDATED > @LastUpdate " +

$"or IT.LASTUPDATED > @LastUpdate " +

$"or S.LASTUPDATED > @LastUpdate " +

$"or A.LASTUPDATED > @LastUpdate " +

$"or IP.LASTUPDATED > @LastUpdate " +

$"or A2.LASTUPDATED > @LastUpdate) AND I.LASTUPDATED IS NOT NULL";

}

​

}