Imports System

Imports System.Runtime.CompilerServices

Imports System.Text

Imports Microsoft.VisualBasic

Imports ServiceStack.Text

​

Public Module Module1

​

    Public Sub Main()

        Console.WriteLine(New ProductQuery() With {.Supplier = 48, .PriceListMisMatches = True, .Limit = 20, .InRetailerStockOnly = true}.SearchSQl.Value)

    End Sub

​

    Public Enum Marketplace

        Undefined

        eBay

        Amazon

        Reverb

    End Enum

​

    Public Enum VoucherType

        ''' <summary>

        ''' Credit Vouchers are raised when a credit or refunded is processed. VAT is refunded when a refund is processed, therefore, when these vouchers are used, VAT must be applied in full.

        ''' </summary>

        ''' <remarks></remarks>

        Credit ' 0

        ''' <summary>

        ''' VAT is applied on purchase of purchased vouchers, therefore deducated when redeemed.

        ''' </summary>

        ''' <remarks></remarks>

        SOR_Credit ' 1

        ''' <summary>

        ''' VAT is applied on purchase of purchased vouchers, therefore deducated when redeemed.

        ''' </summary>

        ''' <remarks></remarks>

        Purchased ' 2  

        ''' <summary>

        ''' Loyalty Vouchers act as a discount on sale, therefore, VAT is deducted when redeemed

        ''' </summary>

        ''' <remarks></remarks>

        Loyalty ' 3

        ''' <summary>

        ''' Promotion Vouchers act as a discount on sale, therefore, VAT is deducted when redeemed

        ''' These are displayed on the website to any user

        ''' </summary>

        ''' <remarks></remarks>

        Promotions ' 4

        ''' <summary>

        ''' Price Match Vouchers act as a discount on sale, therefore, VAT is deducted when redeemed

        ''' These are not displayed on the website and are available only to the user specified

        ''' </summary>

        ''' <remarks></remarks>

        Price_Match ' 5  

        ''' <summary>

        ''' Linked promotion is the same as Promotion, but not displayed on the website at any time

        ''' </summary>

        Linked_Promotion ' 6

        None '7 

    End Enum

​

    Public Class ProductQuery

​

        Const MaxTitleLength As Int16 = 80

​

        Const MinDescriptionLength As Int16 = 250

​

        Const MinImageSize As Int16 = 500

​

        Const MinMetaDescLength As Int16 = 100 ' https://moz.com/learn/seo/meta-description

​

        Const MaxMetaDescLength As Int16 = 155

​

        ''' <summary>

        ''' This needs dealing with so that we can sort Product ID query effectively

        ''' Also check the Variation query which is supposed to query for Zoom, for MP3s etc...

        ''' There should be a media type field in Products database rather than using variations...?

        ''' Also need to add sorting to the Product Lookups which will require additional subqueries

        ''' Stock by location

        ''' Orders 

        ''' Stock Take

        ''' </summary>

        Class Sorting

​

            Enum QuerySort

                Recent_Deliveries

                Recent_Additions

                Best_Sellers

                FullName

                Brand

                Price

                StockValue

                StockUnit

                TitleLength

                DescriptionLength

            ' StockAge 'Stock Ages is just the same as Recent_Deliveries in reverse!

            'Special_Offers

            'Bulk_Offers

            End Enum

​

            Enum Order

                Desc

                Asc

            End Enum

​

            Property SortType As QuerySort

​

            Property SortOrder As Order

​

            Sub New()

            End Sub

        End Class

​

        Enum DuplicateLookup

            Undefined

            Description

            FullName

            MetaDescription

        End Enum

​

        Class WebsiteQuery

​

            Property WebsiteId As Integer

​

            Property DisplayedItems As TriState = TriState.UseDefault

​

            Sub New()

            End Sub

        End Class

​

        Class MarketPlaceQuery

​

            Property MarketPlace As Marketplace

​

            Property ReturnUnListedItems As Boolean

​

            Sub New()

            End Sub

        End Class

                                    Class QueryByExternalId

        Property MarketPlace As Marketplace

                                        Property IdList As String()

        Sub New()

​

        End Sub

    End Class

    Property ExternalIdQuery As QueryByExternalId

​

        Property Sort As Sorting

            Get

                If Me.LongTitles = TriState.True Then

                    Return New Sorting With {.SortType = Sorting.QuerySort.TitleLength, .SortOrder = Sorting.Order.Desc}

                ElseIf Me.LongTitles = TriState.False Then

                    Return New Sorting With {.SortType = Sorting.QuerySort.TitleLength, .SortOrder = Sorting.Order.Asc}

                End If

​

                If Me.ShortDescriptions = TriState.True Then

                    Return New Sorting With {.SortType = Sorting.QuerySort.DescriptionLength, .SortOrder = Sorting.Order.Asc}

                ElseIf Me.ShortDescriptions = TriState.False Then

                    Return New Sorting With {.SortType = Sorting.QuerySort.DescriptionLength, .SortOrder = Sorting.Order.Desc}

                End If

            End Get

​

            Set(value As Sorting)

            End Set

        End Property

​

        Property StockAgeInterval As Nullable(Of DateInterval)

​

        Property WebsiteQ As WebsiteQuery

​

        Property MPQuery As MarketPlaceQuery

​

        Property Find As String

​

        Property MasterCat As Nullable(Of Integer)

​

        Property Category As Nullable(Of Integer)

​

        Property Subcat As Nullable(Of Integer)

​

        Property Brand As Nullable(Of Integer)

​

        Property Supplier As Nullable(Of Integer)

​

        Property LowPrice As Nullable(Of Decimal)

​

        Property HighPrice As Nullable(Of Decimal)

​

        Property CustomerId As Nullable(Of Integer)

​

        Property GroupVariations As Boolean = True

​

        Property InRetailerStockOnly As Boolean

​

        Property InStockOnly As Boolean

​

        Property OutOfStock As Boolean

​

        Property ReservedStock As Boolean

​

        Property ItemsOnOrder As TriState = TriState.UseDefault

​

        Property NeverSold As Boolean

​

        ''' <summary>

        ''' For Part exchanges, if no StockAgeInterval is passed in, we get stock more than 3 months old.

        ''' </summary>

        ''' <returns></returns>

        Property OldPartExchanges As Boolean

​

        Property ArchivedItems As TriState = TriState.UseDefault

​

        Property RestrictedItems As TriState = TriState.UseDefault

​

        Property DiscontinuedItems As TriState = TriState.UseDefault

​

        Property ItemsBlockedFromOrdering As TriState = TriState.UseDefault

​

        Property SaleOrReturnItems As TriState = TriState.UseDefault

​

        Property PriceListMisMatches As TriState = TriState.UseDefault

​

        Property PriceWasSyncedInThePastFortnight As TriState = TriState.UseDefault

​

            Private Property _ebayincludes As Boolean

    Property IncludeEbayIdIfReturningProductData As Boolean

        Get

            If Me.ExternalIdQuery IsNot Nothing AndAlso Me.ExternalIdQuery.MarketPlace = Marketplace.eBay Then

                Return True

            End If

            Return _ebayincludes

        End Get

        Set(value As Boolean)

            Me._ebayincludes = value

        End Set

    End Property

​

        Property IncludeReverbIdIfReturningProductData As Boolean = False

​

        ''' <summary>

        ''' Set to false to get items with invalid GTINs

        ''' </summary>

        ''' <returns></returns>

        Property ValidGtins As TriState = TriState.UseDefault

​

        Property Limit As String

​

        Property ItemIdList As String

​

#Region "SEO Checks"

        Property ShortDescriptions As TriState = TriState.UseDefault

​

        ''' <summary>

        ''' If true, results have long titles, if false, results have short titles.

        ''' </summary>

        ''' <returns></returns>

        Property LongTitles As TriState = TriState.UseDefault

​

        Property NoImages As Boolean

​

        ''' <summary>

        ''' This doesn't return no image products, but products which have (any) images that are too small

        ''' </summary>

        ''' <returns></returns>

        Property SmallImages As Boolean

​

        Property AdviseMetaDescription As Boolean

​

        Property IncludesH1Tag As Boolean

​

        Property IncludesH2Tag As Boolean

​

        Property IncludesH3Tag As Boolean

​

        Property DuplicateCheck As DuplicateLookup

​

#End Region

#Region "Setup fields"

        Property IncludeDeliveries As Boolean

​

        Property IncludeSales As Boolean

​

        Property PriceFieldToUse As String = "price"

​

        ''' <summary>

        ''' Should return True for queries which are inefficient to get the whole ID list. 

        ''' In this case, the SQL should be spliced with SingleProductSql to fetch the full product data in batches.

        ''' </summary>

        ''' <returns></returns>

        Private Property _GetAll As Boolean = False

​

        Property GetFullProducts As Boolean

            Get

                Dim GetFull As Boolean = Me._GetAll Or Me.PriceListMisMatches <> TriState.UseDefault Or Me.ReservedStock

                If GetFull AndAlso Me.Limit Is Nothing Then

                    Throw New Exception("An SQL Limit Should be supplied when using resource hungry queries.")

                End If

​

                Return GetFull

            End Get

​

            Set(value As Boolean)

                Me._GetAll = value

            End Set

        End Property

​

#End Region

        ReadOnly Property SingleProductSql() As Lazy(Of StringBuilder)

            Get

                Dim s As New StringBuilder()

                s.AppendLine("SELECT p.*, {fields} ")

                If Me.GroupVariations Then

                    s.AppendLine(" SUM(p.totalstock) as totalGroupedStock, SUM(p.thirdpartystock) as totalGroupedThirdPartyStock, ")

                End If

​

                s.AppendLine(" (SELECT COUNT(id) FROM products p2 WHERE p2.ParentId=p.ParentId) as variationcount,")

                s.AppendLine(" SUBSTRING_INDEX(Canonical_Link , ',', 1) as canonical_parent, ")

                s.AppendLine(" (IFNULL(CASE WHEN p.sor=1 THEN (SELECT id FROM bk_payments bk WHERE bk.invNo=CONCAT(sor, p.id)) ELSE 0 END,0)) sorpayment, ")

                s.AppendLine(" (SELECT name FROM  mastercat m WHERE m.id=p.mastercat) as mastercatname, (SELECT name FROM  category m WHERE m.id=p.category) as categoryname, (SELECT name FROM  subcat m WHERE m.id=p.subcat) as subcatname, ")

                s.AppendLine(" (SELECT name FROM brands b WHERE b.id=p.brand) as brandname, ")

                s.AppendLine(" sp.name suppliername, sp.id supplierid, sp.dropShipEnabled, sp.dropShipCost, sp.minDropShipWeight, sp.minDropShipMargin,")

                s.AppendLine(" IFNULL(cu.tradeMarkup,0) as tradeMarkup, IFNULL(cu.BasePrice,0) as BasePrice, (IFNULL(cu.cardType,'Standard')='Trade') as isTrade, cu.TradeType, cu.id customerid, ")

                s.AppendLine(" (CASE WHEN p.ProductType='Product_Bundle' THEN IFNULL((SELECT GROUP_CONCAT(CONCAT(b.itemId, '||', p2.fullName, '||', b.images, '||', p2.vatRate, '||', p2.cost, '||', b.quantity, '||', CASE WHEN l3.usePrice='price' THEN p2.price ELSE p2.altPrice END, '||', p2.producttype, '||', p2.item, '||', p2.`option`, '||', p2.colour, '||', p2.Variant3, '||', p2.Variant4, '||', bd.name, '||', p2.Special) SEPARATOR '|-|')  FROM productbundles b LEFT JOIN products p2 ON p2.id=b.itemId LEFT JOIN brands bd ON bd.id=p2.brand LEFT JOIN Locations l3 on l3.id=0 WHERE b.bundleitemid=p.id AND p.`restrict`=0 GROUP BY bundleitemid),'') ELSE '' END) bundleItems, ")

                s.AppendLine(" IFNULL(v.barcode, '') discountCode, IFNULL(v.type," & VoucherType.None & ") codetype, IFNULL(v.value, 0) discountvalue, v.discount v_discount, v.instockonly, ")

                If Me.IncludeEbayIdIfReturningProductData Then s.AppendLine(" IFNULL((SELECT ebay_id FROM ebaylistings e WHERE e.sam_id=p.id AND e.qtyAvailable>0 AND e.endedTime=0 AND e.ebay_id>0 ORDER BY id DESC LIMIT 1),'') ebayitemid, ")

                If Me.IncludeReverbIdIfReturningProductData Then s.AppendLine(" IFNULL((SELECT reverbid FROM reverblistings r WHERE r.samid=p.id AND r.inventory>0 AND r.state='live' ORDER BY id DESC LIMIT 1),'') reverbitemid, ")

                s.AppendLine(" (SELECT CONCAT(name, '.', file_extension) FROM images WHERE product=p.id AND sortorder=1 ORDER BY FileSize DESC LIMIT 1) primaryimage ")

                s.AppendLine(" {bundlefields}")

                s.AppendLine(" FROM products p ")

                s.AppendLine(" {bundlejoin}")

                s.AppendLine(" LEFT JOIN suppliers sp ON sp.id=p.supplier")

                s.AppendLine(" LEFT JOIN customers cu ON cu.id={customer}")

                s.AppendLine(" LEFT JOIN vouchers v ON v.item=p.id  AND v.void='0' AND v.expiry >= '" & Date.Now.ToUnixTime & "' AND (v.user={customer} OR v.user=0) AND v.type<>" & VoucherType.Price_Match)

                ' if we group variations we are only interested in the parent variation.

                ' EXCEPT, this is not correct if a product has been specifically set to a different canonicalisaton GROUP... Check... SUBSTRING_INDEX(Canonical_Link , ',', 1) should do it.

                If Me.GroupVariations Then

                    s.AppendLine(" WHERE (p.parentid=p.id OR p.parentid=0 OR p.id=SUBSTRING_INDEX(Canonical_Link , ',', 1)) AND {where}")

                    s.AppendLine(" GROUP BY p.ParentId")

                Else

                    s.AppendLine(" WHERE {where} ")

                End If

​

                If Me.Sort IsNot Nothing Then

                    Dim OrderField As String = " p.id " ' This is correct for Recent Additions...

                    If Me.Sort.SortType = Sorting.QuerySort.Best_Sellers Then

                        OrderField = " (SELECT COUNT(id) FROM salesrecords WHERE itemno=p.id) "

                    ElseIf Me.Sort.SortType = Sorting.QuerySort.Brand Then

                        OrderField = " p.brand "

                    ElseIf Me.Sort.SortType = Sorting.QuerySort.FullName Then

                        OrderField = " p.fullname "

                    ElseIf Me.Sort.SortType = Sorting.QuerySort.Price Then

                        OrderField = " p." & Me.PriceFieldToUse

                    ElseIf Me.Sort.SortType = Sorting.QuerySort.Recent_Additions Then

                        OrderField = " p.id "

                    ElseIf Me.Sort.SortType = Sorting.QuerySort.Recent_Deliveries Then

                        OrderField = " (SELECT MAX(lastdeliverydate) FROM stocklevels s WHERE s.id=p.id) "

                    ElseIf Me.Sort.SortType = Sorting.QuerySort.StockUnit Then

                        OrderField = " (p.totalstock-p.thirdpartystock) "

                    ElseIf Me.Sort.SortType = Sorting.QuerySort.StockValue Then

                        OrderField = " ((p.totalstock-p.thirdpartystock)*p.cost) "

                    ElseIf Me.Sort.SortType = Sorting.QuerySort.TitleLength Then

                        OrderField = String.Concat(" LENGTH(", "p.fullname", ") ")

                    ElseIf Me.Sort.SortType = Sorting.QuerySort.DescriptionLength Then

                        OrderField = String.Concat(" LENGTH(", "p.description", ") ")

                    End If

​

                    s.AppendLine(String.Concat("ORDER BY ", OrderField, [Enum].GetName(GetType(Sorting.Order), Me.Sort.SortOrder)))

                End If

​

                If Me.Limit IsNot Nothing Then

                    s.AppendLine(String.Concat(" LIMIT ", Me.Limit.Replace("limit", "")))

                End If

​

                s.Replace("{customer}", If(Me.CustomerId IsNot Nothing, Me.CustomerId, 0))

                Return New Lazy(Of StringBuilder)(Function() s)

            End Get

        End Property

​

        ReadOnly Property SearchSQl As Lazy(Of String)

            Get

                Dim NowUnix As Long = Date.Now.ToUnixTime()

                If Me.OldPartExchanges Then

                    ' For Part exchanges, if no StockAgeInterval is passed in, we get stock more than 3 months old.

                    Me.StockAgeInterval = If(Me.StockAgeInterval IsNot Nothing, Me.StockAgeInterval, DateInterval.Quarter)

                End If

​

                If Me.Sort IsNot Nothing AndAlso Me.Sort.SortType = Sorting.QuerySort.Recent_Deliveries Then

                    Me.IncludeDeliveries = True

                End If

​

                Dim SqlWhere As New StringBuilder()

                If Me.OldPartExchanges Then SqlWhere.AppendLine(" p.pxacquired != 0 AND ")

                SqlWhere.AppendLine(Me.Find.Sql("p.fullname", "like"))

                SqlWhere.AppendLine(Me.MasterCat.Sql("p.mastercat"))

                SqlWhere.AppendLine(Me.Category.Sql("p.category"))

                SqlWhere.AppendLine(Me.Subcat.Sql("p.subcat"))

                SqlWhere.AppendLine(Me.Brand.Sql("p.brand"))

                SqlWhere.AppendLine(Me.Supplier.Sql("p.supplier"))

                If Me.LowPrice IsNot Nothing AndAlso Me.HighPrice IsNot Nothing Then

                    SqlWhere.AppendLine(" p." & Me.PriceFieldToUse & " BETWEEN " & Me.LowPrice & " AND " & Me.HighPrice & " AND ")

                ElseIf Me.LowPrice IsNot Nothing AndAlso Me.HighPrice Is Nothing Then

                    SqlWhere.AppendLine(" p." & Me.PriceFieldToUse & " >= " & Me.LowPrice & " AND ")

                ElseIf Me.LowPrice Is Nothing AndAlso Me.HighPrice IsNot Nothing Then

                    SqlWhere.AppendLine(" p." & Me.PriceFieldToUse & " <= " & Me.LowPrice & " AND ")

                End If

​

                If Me.InRetailerStockOnly Then

                    SqlWhere.AppendLine(" (p.totalstock-p.thirdpartystock) > 0 AND ")

                ElseIf Me.InStockOnly Then

                    SqlWhere.AppendLine(" p.totalstock > 0 AND ")

                ElseIf Me.OutOfStock Then

                    SqlWhere.AppendLine(" p.totalstock <= 0 AND ")

                End If

​

                If Me.WebsiteQ IsNot Nothing AndAlso Me.WebsiteQ.DisplayedItems <> TriState.UseDefault Then

                    SqlWhere.AppendLine(String.Concat(" p.display", Me.WebsiteQ.WebsiteId, " = ", If(Me.WebsiteQ.DisplayedItems = TriState.True, 1, 0), " AND "))

                End If

​

                If Me.MPQuery IsNot Nothing AndAlso Me.MPQuery.MarketPlace <> Marketplace.Undefined Then

                    If Me.MPQuery.MarketPlace = Marketplace.Amazon Then

                        SqlWhere.AppendLine(String.Concat(" p.AvailableOnAmazon", If(Me.MPQuery.ReturnUnListedItems, " = 0", " = 1"), " AND "))

                    ElseIf Me.MPQuery.MarketPlace = Marketplace.eBay Then

                        SqlWhere.AppendLine(String.Concat(" IFNULL((SELECT 1 FROM ebaylistings WHERE Sam_Id=p.id AND endedtime < ", NowUnix, " ), 0) ", If(Me.MPQuery.ReturnUnListedItems, " = 0", " = 1"), " AND "))

                    ElseIf Me.MPQuery.MarketPlace = Marketplace.Reverb Then

                        SqlWhere.AppendLine(String.Concat(" IFNULL((SELECT 1 FROM reverblistings WHERE SamId=p.id), 0) ", If(Me.MPQuery.ReturnUnListedItems, " = 0", " = 1"), " AND "))

                    End If

                End If

​

                If Me.NeverSold Then SqlWhere.AppendLine(" (SELECT COUNT(id) FROM salesrecords WHERE itemno=p.id) = 0 AND ")

                If Me.ItemsBlockedFromOrdering <> TriState.UseDefault Then

                    SqlWhere.AppendLine(String.Concat(" p.blockreorder=", If(Me.ItemsBlockedFromOrdering = TriState.True, 1, 0), " AND "))

                End If

​

                If Me.DiscontinuedItems <> TriState.UseDefault Then

                    SqlWhere.AppendLine(String.Concat(" p.blockreorder=", If(Me.DiscontinuedItems = TriState.True, 1, 0), " AND "))

                End If

​

                If Me.RestrictedItems <> TriState.UseDefault Then

                    SqlWhere.AppendLine(String.Concat(" p.blockreorder=", If(Me.DiscontinuedItems = TriState.True, 1, 0), " AND "))

                End If

​

                If Me.SaleOrReturnItems <> TriState.UseDefault Then

                    SqlWhere.AppendLine(String.Concat(" p.sor=", If(Me.SaleOrReturnItems = TriState.True, 1, 0), " AND "))

                End If

​

                If Me.PriceWasSyncedInThePastFortnight <> TriState.UseDefault Then

                    SqlWhere.AppendLine(String.Concat(" p.lastpricesync", If(Me.PriceWasSyncedInThePastFortnight = TriState.True, ">=", "<"), Date.Now.AddDays(-14).ToUnixTime(), " AND "))

                End If

​

                If Me.ValidGtins <> TriState.UseDefault Then

                    SqlWhere.AppendLine(String.Concat(" p.gtinisvalid=", If(Me.ValidGtins = TriState.True, 1, 0), " AND "))

                End If

​

                If Me.PriceListMisMatches <> TriState.UseDefault Then

                    SqlWhere.AppendLine(String.Concat(" IFNULL((SELECT 1 FROM pricelists pl WHERE (pl.sku=p.sku OR pl.upc=p.upc) AND pl.supplierid=p.supplier LIMIT 1),0) =", If(Me.PriceListMisMatches = TriState.True, 0, 1), " AND "))

                End If

​

                If Me.ItemsOnOrder <> TriState.UseDefault Then

                    SqlWhere.AppendLine(String.Concat(" IFNULL((SELECT 1 FROM stockordersplaced WHERE itemnumber=p.id AND qty>qtydelivered LIMIT 1),0)", If(Me.ItemsOnOrder = TriState.True, 1, 0), " AND "))

                End If

​

                Me.ItemIdList = String.Concat(Me.ItemIdList, ",", GetDuplicateContent()).Trim(",") ' fetch IDs of any records which have duplicate content

                If Me.ItemIdList <> "" Then

                    SqlWhere.AppendLine(String.Concat(" p.id IN(", ItemIdList, ") AND"))

                End If

​

                If Me.LongTitles <> TriState.UseDefault Then

                    ' Find titles more than MaxTitleLength long (if tristate.true)

                    ' OR less than 10 characters Long

                    SqlWhere.AppendLine(String.Concat(" LENGTH(", "p.fullname", ") ", If(Me.LongTitles = TriState.True, ">", "<="), If(Me.LongTitles = TriState.True, MaxTitleLength, 10), " AND "))

                End If

​

                If Me.ShortDescriptions <> TriState.UseDefault Then

                    ' Find titles more than MaxTitleLength long (if tristate.true)

                    ' OR less than 10 characters Long

                    SqlWhere.AppendLine(String.Concat(" LENGTH(", "p.description", ") ", If(Me.ShortDescriptions = TriState.True, "<", ">="), If(Me.ShortDescriptions = TriState.True, MinDescriptionLength, 1000), " AND "))

                End If

​

                If Me.AdviseMetaDescription Then

                    SqlWhere.AppendLine(String.Concat(" (Length(, ", "p.metadescription", ")) NOT BETWEEN ", MinMetaDescLength, " AND ", MaxMetaDescLength, " AND "))

                End If

​

                If Me.NoImages Then

                    SqlWhere.AppendLine(" p.mainimage ='' AND ")

                End If

​

                If Me.SmallImages Then

                    SqlWhere.AppendLine(String.Concat(" (SELECT id FROM images WHERE product=p.id AND type_extension=1 AND GREATEST(width, height)<", MinImageSize, " LIMIT 1) IS NOT NULL AND "))

                End If

​

                If Me.IncludesH1Tag Then SqlWhere.AppendLine(" (p.description LIKE '%<h1>% ' AND p.description LIKE '%</h1>%') AND ")

                If Me.IncludesH2Tag Then SqlWhere.AppendLine(" (p.description LIKE '%<h2>% ' AND p.description LIKE '%</h2>%') AND ")

                If Me.IncludesH3Tag Then SqlWhere.AppendLine(" (p.description LIKE '%<h3>% ' AND p.description LIKE '%</h3>%') AND ")

            If Me.ExternalIdQuery IsNot Nothing AndAlso Me.ExternalIdQuery.MarketPlace = Marketplace.eBay Then

                ' p.id IN((SELECT sam_id FROM ebaylistings WHERE ebay_id IN ( 131349670613,'131375180340')))

                SqlWhere.AppendLine(String.Concat(" p.id IN ((SELECT sam_id FROM ebaylistings WHERE ebay_id IN (", Me.ExternalIdQuery.IdList.Join(","), "))) AND "))

            End If

                ' THIS IS THE FINAL LINE with p.id>0 to top off the "ANDS" or the archive modifier

                If Me.ArchivedItems <> TriState.UseDefault Then

                    SqlWhere.AppendLine(String.Concat(" p.archive=", If(Me.ArchivedItems = TriState.True, 1, 0)))

                Else

                    SqlWhere.AppendLine(" p.id > 0")

                End If

​

                Dim sql As New StringBuilder()

                sql.AppendLine("SELECT * FROM (")

                If Me.GetFullProducts Then

                    sql.Append(SingleProductSql.Value.Replace("{where}", SqlWhere.ToString()).Replace("{bundlefields}", "").Replace("{bundlejoin}", ""))

                Else

                    If Me.Limit IsNot Nothing Then

                        Throw New Exception("An SQL Limit is only applicable to Queries returning full product data.")

                    End If

​

                    sql.AppendLine("SELECT p.id {fields} FROM products p WHERE ")

                    sql.Append(SqlWhere)

                End If

​

                sql.AppendLine(") allids {outerwhere}")

                sql.Replace("{customer}", If(Me.CustomerId IsNot Nothing, Me.CustomerId, 0))

                Dim Fields As New StringBuilder()

                If Me.IncludeSales Then

                    ' these includes should have optional dates

                    Fields.AppendLine(", (SELECT COUNT(id) FROM salesrecords WHERE itemno=p.id) as sales ")

                End If

​

                If Me.IncludeDeliveries Or Me.StockAgeInterval IsNot Nothing Then

                    ' these includes should have optional dates

                    Fields.AppendLine(", (SELECT MAX(lastdeliverydate) FROM stocklevels s WHERE s.id=p.id) as lastdelivery ")

                End If

​

                If Me.ReservedStock Then

                    ' There is no absolute way to highly optimise this. 

                    ' Throwing this sub query in makes this SQL as slow as the old queries, however it is rarely used so considered acceptable. 

                    Fields.AppendLine(", IFNULL((SELECT (CASE WHEN (i.shippingmethod) <> '' THEN SUM(sr.pickQty)-SUM(sr.shippedQty) ELSE sr.quantity END) ")

                    Fields.AppendLine(" FROM salesrecords sr  ")

                    Fields.AppendLine(" LEFT JOIN invoices i ON i.id=sr.invno ")

                    Fields.AppendLine(" LEFT JOIN customers c ON c.id=i.customerid")

                    Fields.AppendLine(" WHERE p.managestock = 1 AND sr.itemno=p.id AND (sr.pickQty > sr.shippedQty OR (IFNULL(c.isCreditCustomer,0)=0 AND i.status='Deposit')) GROUP BY sr.itemno),0) reserved")

                End If

​

                sql.Replace("{fields}", Fields.ToString())

                Dim OuterWhere As New StringBuilder()

                If Me.IncludeDeliveries AndAlso Me.StockAgeInterval Is Nothing Then

                    OuterWhere.AppendLine("lastdelivery > 0 AND ")

                ElseIf Me.StockAgeInterval IsNot Nothing Then

                    OuterWhere.AppendLine(String.Concat("lastdelivery < ", DateAdd(Me.StockAgeInterval.Value, -1, Today).ToUnixTime(), " AND "))

                End If

​

                If Me.ReservedStock Then

                    OuterWhere.AppendLine("reserved > 0 AND ")

                End If

​

                sql.Replace("{outerwhere}", If(OuterWhere.Length > 0, String.Concat("WHERE " & OuterWhere.ToString(), " id > 0"), ""))

                If Me.Sort IsNot Nothing AndAlso Me.Sort.SortType = Sorting.QuerySort.Recent_Deliveries Then

                    sql.AppendLine(String.Concat(" ORDER BY lastdelivery ", [Enum].GetName(GetType(Sorting.Order), Me.Sort.SortOrder)))

                End If

​

                Return New Lazy(Of String)(Function() sql.ToString())

            End Get

        End Property

​

        ''' <summary>

        ''' Runs a subquery and returns a list of items which duplicate the content requested

        ''' </summary>

        ''' <returns></returns>

        Private Function GetDuplicateContent() As String

            If Not Me.DuplicateCheck = Nothing OrElse Me.DuplicateCheck = DuplicateLookup.Undefined Then

                Return ""

            Else

            End If

​

            Return ""

        End Function

    End Class

​

    <Extension>

    Public Function Sql(Of t)(s As t, field As String, Optional Evaluator As String = "=") As String

        If s IsNot Nothing AndAlso Not Evaluator.Equals("like") Then

            Return String.Concat(field, Evaluator, "'", s.ToString().Trim(), "'", " AND ")

        ElseIf s IsNot Nothing AndAlso Evaluator.Equals("like") Then

            Return String.Concat(field, " LIKE ", "'%", s.ToString().Trim().Replace(" ", "%"), "%'", " AND ")

        End If

​

        Return ""

    End Function

End Module

​