Database

<br />
Function NewestFile(Directory, FileSpec)<br />
' Returns the name of the most recent file in a Directory<br />
' That matches the FileSpec (e.g., "*.xls").<br />
' Returns an empty string if the directory does not exist or<br />
' it contains no matching files<br />
    Dim FileName As String<br />
    Dim MostRecentFile As String<br />
    Dim MostRecentDate As Date<br />
    If Right(Directory, 1) <> "\" Then Directory = Directory & "\"<br />
    FileName = Dir(Directory & FileSpec, 0)<br />
    If FileName <> "" Then<br />
        MostRecentFile = FileName<br />
        MostRecentDate = FileDateTime(Directory & FileName)<br />
        Do While FileName <> ""<br />
            If FileDateTime(Directory & FileName) > MostRecentDate Then<br />
                 MostRecentFile = FileName<br />
                 MostRecentDate = FileDateTime(Directory & FileName)<br />
             End If<br />
             FileName = Dir<br />
        Loop<br />
    End If<br />
    NewestFile = MostRecentFile<br />
    <br />
End Function<br />

CREATE PROCEDURE [dbo].[UpdatePricePoint]

            (@StoreID [int],
             @StoreDate [datetime] )
AS
BEGIN

UPDATE TicketItems
SET 
            Cost = 
		(CASE WHEN TicketItems.Qty = 0 
                        THEN 
		1 
		ELSE 
                           TicketItems.Qty 
           			 END ) 
                       * 
                       (CASE WHEN StoreItems.CurrentCasePack = 0 
                       THEN 
                           StoreItems.CurrentCaseCost 
                       ELSE 
                           StoreItems.CurrentCaseCost / StoreItems.CurrentCasePack 
                       END),
            NormalPrice = 
                        CASE WHEN StoreItems.NormalMSU = 0 
                        THEN 
                            StoreItems.NormalUnitSell 
                        ELSE 
                            StoreItems.NormalUnitSell / StoreItems.NormalMSU 
                        END,
            NormalAmount = 
                        (CASE WHEN StoreItems.NormalMSU = 0 
                        THEN 
                            StoreItems.NormalUnitSell 
                        ELSE 
                            StoreItems.NormalUnitSell / StoreItems.NormalMSU 
                        END)
                        * 
                        (CASE WHEN TicketItems.Qty = 0 
                        THEN 
                            1 
                        ELSE 
                            TicketItems.Qty 
                        END),
            PricePoint = 
                        (TicketItems.Amount - TicketItems.DiscountAmount) 
                        /
                        (CASE WHEN Qty = 0 
                        THEN 
                            1 
                        ELSE 
                            TicketItems.Qty 
                        END),
            Price = 
                        TicketItems.Amount
                        /
                        (CASE WHEN Qty = 0 
                        THEN 
                            1 
                        ELSE 
                            TicketItems.Qty 
                        END),
	Unit = (CASE WHEN TicketItems.Flag2 = 16 OR TicketItems.Flag2 = 32 OR TicketItems.Flag2 = 64
		THEN
			1
		ELSE
			TicketItems.Qty
		END),
	NormalMSU = StoreItems.NormalMSU,
	MSU = StoreItems.CurrentMSU,
	Profit = TicketItems.Amount - TicketItems.Cost
FROM TicketItems
INNER JOIN StoreItems 
ON 
            TicketItems.StoreID = StoreItems.StoreID AND 
            TicketItems.Plu = StoreItems.Plu
INNER JOIN TicketTotal
ON
            TicketItems.StoreID = TicketTotal.StoreID AND 
            TicketItems.TicketDate = TicketTotal.TicketDate AND 
            TicketItems.TicketTime = TicketTotal.TicketTime AND 
            TicketItems.POSID = TicketTotal.POSID AND 
            TicketItems.TicketID = TicketTotal.TicketID
WHERE
            TicketTotal.StoreID = @StoreID AND 
            TicketTotal.StoreDate = @StoreDate
END