
/*************************************************************************************************************************/
/*************************************************************************************************************************/
-- SET PARAMETERS IN THIS BLOCK

/*
 * ItemCode value from tbSalesItem
 */
declare @ItemCode varchar(50) = '10048'  --> 'Bagel, Plain'

/*
 * Type can be either ['Finished' or 'Raw']
 */
declare @Type varchar(50) = 'Finished' 

/*
 * Unit of Measure must currently be one of ['Each', 'Pound', 'Gallon']
 */
declare @DonateAs_UOM varchar(50) = 'Each'
declare @WastedAs_UOM varchar(50) = 'Pound'
declare @ExportAs_UOM varchar(50) = 'Each'


/*
 * Conversion rate from the Donation unit to the Waste unit.  Accurate to 4 decimal places.
 * ie. if Bagels are wasted as Pounds, but donated as 'Eaches', then this is the value that the donation
 * amount should be multiplied by to obtain the waste amount.
 *    eg. donate 5 bagels => 5 * 0.2057 = 1.0285 pounds wasted.
 */
declare @DonationToWaste decimal(18,4) = 0.2057
declare @DonationToExport decimal(18,4) = 1.00

/*
 * Items will appear on the list ordered by this value acsending (then by Description ascending for items with same SortOrder.)
 */
declare @SortOrder int = 10

/*
 * Zone
 */
declare @ZoneName varchar(50) = 'Houston, TX'


/*************************************************************************************************************************/
/*************************************************************************************************************************/

BEGIN TRY
	BEGIN TRANSACTION

	DECLARE @ItemID BIGINT = (select ItemID from tbItem with (nolock) where ItemCode = @ItemCode)
	DECLARE @SalesItemID BIGINT = (select SalesItemID from tbSalesItem with (nolock) where ItemCode = @ItemCode)
	DECLARE @DonationUomId SMALLINT = (select UnitOfMeasureID from tbUnitOfMeasure with (nolock) where UomName = @DonateAs_UOM)
	DECLARE @WasteUomId SMALLINT = (select UnitOfMeasureID from tbUnitOfMeasure with (nolock) where UomName = @WastedAs_UOM)
	DECLARE @ExportUomId SMALLINT = (select UnitOfMeasureID from tbUnitOfMeasure with (nolock) where UomName = @ExportAs_UOM)
	DECLARE @ZoneId int = (select ZoneID from tbZone with (nolock) where [Zone] = @ZoneName)

	-- some items exist as both Inventory Items and Sales Items...
	IF @Type = 'Finished' SET @ItemID = NULL
	ELSE SET @SalesItemID = NULL
	
	INSERT INTO [dbo].[tbFoodDonationItem]
		([ItemID]
		,[SalesItemID]
		,[DonationUomId]
		,[WasteUomId]
		,[ExportUomId]
		,[DonationToWaste]
		,[DonationToExport]
		,[SortOrder]
		,[ZoneId])
	VALUES (
		@ItemID, -- bigint,>
		@SalesItemID, -- bigint,>
		@DonationUomId, -- smallint,>
		@WasteUomId, -- smallint,>
		@ExportUomId, -- smallint,>
		@DonationToWaste, -- decimal(18,4),>
		@DonationToExport, -- decimal(18,4),>
		@SortOrder, -- int,>
		@ZoneId -- int,>
	)

	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	SELECT ERROR_MESSAGE() as ErrorMessage;

	IF(@@TRANCOUNT > 0)
		ROLLBACK TRANSACTION
END CATCH;
GO
