Coldbox itemBean & itemGateway

itemBean.cfc

<cfcomponent output="false" cache="true">

	<cfscript>
		_datasource = 0;
		_itemGW = 0;
		item.id = 0;
		item.code = "";
		item.name = "";
		item.BookXML = "";
		item.description = "";
		item.newDescription = "";
		item.showScaler = 0;
		item.kitContents = "";
		item.priceCode = 0;
		item.price = 0;
		item.UOM = "";
		item.notes = "";
		item.volume = 0;
		item.weight = 0;
		item.UPSShipStatId = 0;
		item.USPSShipStatId = 0;
		item.inventoryQty = 0;
		item.inventoryWarning = 0;
		item.bin = "";
		item.categoryName = "";
		item.categoryId = "";
		item.saleStatus = 0;
		item.status = 0;
		item.shippingDate = "";
		item.instructions = 0;
		item.video = 0;
		item.options = [];
		item.relatedItems = [];
		item.relatedCategories = [];
		item.productWarning = 0;
		item.productCompany = 1;
		item.prev = "";
		item.next = "";
		item = structNew();
	</cfscript>

	<cffunction	name="init"	output="false" returntype="itemBean" hint="I initialize the item bean.">
	  <cfargument name="datasource" type="any" _wireme="coldbox:datasource:beta" />
	  <cfargument name="itemGW" type="any" _wireme="model:itemGW" />

		<cfset _datasource = arguments.datasource>
		<cfset _itemGW = arguments.itemGW>
		<cfreturn this />
	</cffunction>

	<cffunction name="setCurrentItem" output="false">
		<cfargument name="selectedItemCode" required="false" default="0">

		<cfset setItem(_itemGW.getItemByCode(arguments.selectedItemCode)) />
	</cffunction>

	<cffunction name="setCurrentItemById" output="false">
		<cfargument name="selectedItemId" required="false" default="0">

		<cfset setItem(_itemGW.getItemById(arguments.selectedItemId)) />
	</cffunction>

	<cffunction name="setItemId" output="false">
		<cfargument name="itemId" required="true" />
		<cfset item.id = arguments.itemId />
	</cffunction>

	<cffunction name="getItemId" output="false">
		<cfreturn item.id />
	</cffunction>

	<cffunction name="setItemCode" output="false">
		<cfargument name="itemCode" required="true" />
		<cfset item.code = arguments.itemCode />
	</cffunction>

	<cffunction name="getItemCode" output="false">
		<cfreturn item.code />
	</cffunction>

	<cffunction name="setItemName" output="false">
		<cfargument name="itemName" required="true" />
		<cfset item.name = arguments.itemName />
	</cffunction>

	<cffunction name="getItemName" output="false">
		<cfreturn item.name />
	</cffunction>
    
    <cffunction name="setItemBookXML" output="false">
		<cfargument name="itemBookXML" required="true" />
		<cfset item.BookXML = arguments.itemBookXML />
	</cffunction>

	<cffunction name="getItemBookXML" output="false">
		<cfreturn item.BookXML />
	</cffunction>

	<cffunction name="setItemDescription" output="false">
		<cfargument name="itemDescription" required="true" />
		<cfset item.description = arguments.itemDescription />
	</cffunction>

	<cffunction name="getItemDescription" output="false">
		<cfreturn item.description />
	</cffunction>
    
    <cffunction name="setNewDescription" output="false">
		<cfargument name="newDescription" required="true" />
		<cfset item.newDescription = arguments.newDescription />
	</cffunction>

	<cffunction name="getNewDescription" output="false">
		<cfreturn item.newDescription />
	</cffunction>
    
    <cffunction name="setShowScaler" output="false">
		<cfargument name="showScaler" required="true" />
		<cfset item.showScaler = arguments.showScaler />
	</cffunction>

	<cffunction name="getShowScaler" output="false">
		<cfreturn item.showScaler />
	</cffunction>
    
    <cffunction name="setKitContents" output="false">
		<cfargument name="kitContents" required="true" />
		<cfset item.kitContents = arguments.kitContents />
	</cffunction>

	<cffunction name="getKitContents" output="false">
		<cfreturn item.kitContents />
	</cffunction>

	<cffunction name="setItemPriceCode" output="false">
		<cfargument name="itemPriceCode" required="true" />
		<cfset item.priceCode = arguments.itemPriceCode />
	</cffunction>

	<cffunction name="getItemPriceCode" output="false">
		<cfreturn item.priceCode />
	</cffunction>

	<cffunction name="setItemPrice" output="false">
		<cfargument name="itemPrice" required="true" />
		<cfset item.price = arguments.itemPrice />
	</cffunction>

	<cffunction name="getItemPrice" output="false">
		<cfreturn item.price />
	</cffunction>

	<cffunction name="setItemUOM" output="false">
		<cfargument name="itemUOM" required="true" />
		<cfset item.UOM = arguments.itemUOM />
	</cffunction>

	<cffunction name="getItemUOM" output="false">
		<cfreturn item.UOM />
	</cffunction>

	<cffunction name="setItemNotes" output="false">
		<cfargument name="itemNotes" required="true" />
		<cfset item.notes = arguments.itemNotes />
	</cffunction>

	<cffunction name="getItemNotes" output="false">
		<cfreturn item.notes />
	</cffunction>

	<cffunction name="setItemVolume" output="false">
		<cfargument name="itemVolume" required="true" />
		<cfset item.volume = arguments.itemVolume />
	</cffunction>

	<cffunction name="getItemVolume" output="false">
		<cfreturn item.volume />
	</cffunction>

	<cffunction name="setItemWeight" output="false">
		<cfargument name="itemWeight" required="true" />
		<cfset item.weight = arguments.itemWeight />
	</cffunction>

	<cffunction name="getItemWeight" output="false">
		<cfreturn item.weight />
	</cffunction>

	<cffunction name="setItemUPSShipStatId" output="false">
		<cfargument name="itemUPSShipStatId" required="true" />
		<cfset item.UPSShipStatId = arguments.itemUPSShipStatId />
	</cffunction>

	<cffunction name="getItemUPSShipStatId" output="false">
		<cfreturn item.UPSShipStatId />
	</cffunction>

	<cffunction name="setItemUSPSShipStatId" output="false">
		<cfargument name="itemUSPSShipStatId" required="true" />
		<cfset item.USPSShipStatId = arguments.itemUSPSShipStatId />
	</cffunction>

	<cffunction name="getItemUSPSShipStatId" output="false">
		<cfreturn item.USPSShipStatId />
	</cffunction>

	<cffunction name="setItemInventoryQty" output="false">
		<cfargument name="itemInventoryQty" required="true" />
		<cfset item.inventoryQty = arguments.itemInventoryQty />
	</cffunction>

	<cffunction name="getItemInventoryQty" output="false">
		<cfreturn item.inventoryQty />
	</cffunction>

	<cffunction name="setItemInventoryWarning" output="false">
		<cfargument name="itemInventoryWarning" required="true" />
		<cfset item.inventoryWarning = arguments.itemInventoryWarning />
	</cffunction>

	<cffunction name="getItemInventoryWarning" output="false">
		<cfreturn item.inventoryWarning />
	</cffunction>

	<cffunction name="setItemBin" output="false">
		<cfargument name="itemBin" required="true" />
		<cfset item.bin = arguments.itemBin />
	</cffunction>

	<cffunction name="getItemBin" output="false">
		<cfreturn item.bin />
	</cffunction>

	<cffunction name="setItemCategoryName" output="false">
		<cfargument name="categoryName" required="true" />
		<cfset item.categoryName = arguments.categoryName />
	</cffunction>

	<cffunction name="getItemCategoryName" output="false">
		<cfreturn item.categoryName />
	</cffunction>

	<cffunction name="setItemCategoryURL" output="false">
		<cfargument name="categoryURL" required="true" />
		<cfset item.categoryURL = arguments.categoryURL />
	</cffunction>

	<cffunction name="getItemCategoryURL" output="false">
		<cfreturn item.categoryURL />
	</cffunction>

	<cffunction name="setItemCategoryId" output="false">
		<cfargument name="categoryId" required="true" />
		<cfset item.categoryId = arguments.categoryId />
	</cffunction>

	<cffunction name="getItemCategoryId" output="false">
		<cfreturn item.categoryId />
	</cffunction>

	<cffunction name="setItemSaleStatusId" output="false">
		<cfargument name="saleStatusId" required="true" />
		<cfset item.saleStatusId = arguments.saleStatusId />
	</cffunction>

	<cffunction name="getItemSaleStatus" output="false">
		<cfreturn item.saleStatusId />
	</cffunction>

	<cffunction name="setItemStatus" output="false">
		<cfargument name="itemStatus" required="true" />
		<cfset var thisStatus = arguments.itemStatus />
		<cfset item.status = IIf(len(thisStatus),DE(listFirst(thisStatus)),DE("On Shelf")) />
		<cfif listLen(thisStatus) gt 1>
			<cfset setShippingDate(dateFormat(listLast(thisStatus),"m/d/yy"))>
		</cfif>
	</cffunction>

	<cffunction name="getItemStatus" output="false">
		<cfreturn item.status />
	</cffunction>

	<cffunction name="setShippingDate" output="false">
		<cfargument name="itemShippingDate" required="true" />
		<cfset item.shippingDate = arguments.itemShippingDate />
	</cffunction>

	<cffunction name="getShippingDate" output="false">
		<cfreturn item.shippingDate />
	</cffunction>

	<cffunction name="setItemInstructions" output="false">
		<cfargument name="instructions" required="false" default="" />
		<cfset item.instructions = arguments.instructions />
	</cffunction>

	<cffunction name="getItemInstructions" output="false">
		<cfreturn item.instructions />
	</cffunction>

	<cffunction name="setItemVideo" output="false">
		<cfargument name="video" required="false" default="" />
		<cfset item.video = arguments.video />
	</cffunction>

	<cffunction name="getItemVideo" output="false">
		<cfreturn item.video />
	</cffunction>

	<cffunction name="setItemRelatedItems" output="false">
		<cfargument name="relatedItems" required="false" default="" />
		<cfset item.relatedItems = arguments.relatedItems />
	</cffunction>

	<cffunction name="getItemRelatedItems" output="false">
		<cfreturn item.relatedItems />
	</cffunction>

	<cffunction name="setItemRelatedCategories" output="false">
		<cfargument name="relatedCategories" required="false" default="" />
		<cfset item.relatedCategories = arguments.relatedCategories />
	</cffunction>

	<cffunction name="getItemRelatedCategories" output="false">
		<cfreturn item.relatedCategories />
	</cffunction>

	<cffunction name="setItemOptions" output="false">
		<cfargument name="options" required="false" default="" />
		<cfset item.options = arguments.options />
	</cffunction>

	<cffunction name="getItemOptions" output="false">
		<cfreturn item.options />
	</cffunction>
    
	<cffunction name="setProductWarning" output="false">
		<cfargument name="productWarning" required="true" />
		<cfset item.productWarning = arguments.productWarning />
	</cffunction>

	<cffunction name="getProductWarning" output="false">
		<cfreturn item.productWarning />
	</cffunction>  
    
    <cffunction name="setProductCompany" output="false">
		<cfargument name="productCompany" required="true" />
		<cfset item.productCompany = arguments.productCompany />
	</cffunction>

	<cffunction name="getProductCompany" output="false">
		<cfreturn item.productCompany />
	</cffunction>

	<cffunction name="setItemPrev" output="false">
		<cfargument name="itemPrev" required="true" />
		<cfset item.prev = arguments.itemPrev />
	</cffunction>

	<cffunction name="getItemPrev" output="false">
		<cfreturn item.prev />
	</cffunction>

	<cffunction name="setItemNext" output="false">
		<cfargument name="itemNext" required="true" />
		<cfset item.next = arguments.itemNext />
	</cffunction>

	<cffunction name="getItemNext" output="false">
		<cfreturn item.next />
	</cffunction>

	<cffunction name="getItem" output="false" hint="I return a struct with all values pertaining to the current item">

		<cfreturn item />
	</cffunction>

	<cffunction name="setItem" output="false" access="private">
		<cfargument name="itemRecord" required="true" />

		<cfscript>
		var item = arguments.itemRecord;

			setItemId(item.itemId);
			setItemCode(item.itemCode);
			setItemName(item.itemName);
			setItemBookXML(item.itemBookXML);
			setItemDescription(item.itemDescription);
			setNewDescription(item.newDescription);
			setShowScaler(item.showScaler);
			setKitContents(item.kitContents);
			setItemPriceCode(item.itemPriceCode);
			setItemPrice(item.price);
			setItemUOM(item.itemUOM);
			setItemNotes(item.itemNotes);
			setItemVolume(item.itemVolume);
			setItemWeight(item.itemWeight);
			setItemUPSShipStatId(item.itemUPSShipStatId);
			setItemUSPSShipStatId(item.itemUSPSShipStatId);
			setItemInventoryQty(item.itemInventoryQty);
			setItemInventoryWarning(item.itemInventoryWarning);
			setItemBin(item.itemBin);
			setShippingDate(_itemGW.getShippingDate(getItemCode()));
			setItemCategoryName(item.categoryName);
			setItemCategoryURL(item.categoryURL);
			setItemCategoryId(item.categoryId);
			setItemSaleStatusId(item.saleStatusId);
			setItemStatus(_itemGW.getItemStatus(getItemCode()));
			setItemInstructions(_itemGW.getInstructions(getItemCode()));
			setItemVideo(_itemGW.getVideo(getItemCode()));
			setItemOptions(_itemGW.getOptions(getItemCode()));
			setItemRelatedItems(_itemGW.getRelatedItems(getItemCode()));
			setItemRelatedCategories(_itemGW.getRelatedCategories(getItemCode()));
			setProductWarning(_itemGW.getWarningsAndCautions(getItemCode()));
			setProductCompany(_itemGW.getProductCompany(getItemCode()));

		</cfscript>
	</cffunction>
</cfcomponent>

itemGateway.cfc

<cfcomponent output="false" cache="false">

	<cfset _datasource = 0>

	<cffunction	name="init"	output="false">
	  <cfargument name="datasource" type="any" _wireme="coldbox:datasource:beta" />
		<cfset _datasource = arguments.datasource>
		<cfreturn this />
	</cffunction>

	<cffunction name="getItemByCode" output="false" hint="Returns item record from item code">
		<cfargument name="itemCode" required="yes">

		<cfset var q=0 />
		<cfquery name="z" datasource="#_datasource.getname()#">
			select itemId from item
				where item.itemCode = <cfqueryparam value="#arguments.itemCode#" cfsqltype="CF_SQL_STRING">
		</cfquery>
		<cfset q = getItemById(z.itemId) />

		<cfreturn q>
	</cffunction>

	<cffunction name="getItemById" output="false" hint="Returns item record from item id">
		<cfargument name="itemId" required="yes">

		<cfset var z = arrayNew(1) />
		<cfquery name="q" datasource="#_datasource.getname()#">
			select top 1 * from item,item_price,item_category
				where item.itemId = <cfqueryparam value="#arguments.itemId#" cfsqltype="CF_SQL_INTEGER">
				and item_price.itemId = <cfqueryparam value="#arguments.itemId#" cfsqltype="CF_SQL_INTEGER">
				and item_category.itemId = <cfqueryparam value="#arguments.itemId#" cfsqltype="CF_SQL_INTEGER">
		</cfquery>

		<cfquery name="r" datasource="#_datasource.getname()#">
			select categoryName,categoryURL from category
				where categoryId = <cfqueryparam value="#q.categoryId#" cfsqltype="CF_SQL_STRING">
		</cfquery>
		<cfif not ListFindNoCase(q.columnList,"categoryName")>
			<cfset z[1] = r.categoryName />
			<cfset queryAddColumn(q,"categoryName","varchar",z) />
			<cfset z[1] = r.categoryURL />
			<cfset queryAddColumn(q,"categoryURL","varchar",z) />
		</cfif>

		<cfquery name="r" datasource="#_datasource.getname()#">
			select saleStatusId from item_saleStatus
				where itemId = <cfqueryparam value="#q.categoryId#" cfsqltype="CF_SQL_STRING">
		</cfquery>
		<cfif not ListFindNoCase(q.columnList,"saleStatusId")> 
			<cfif r.recordCount>
				<cfset z[1] = r.saleStatusId />
			<cfelse>
				<cfset z[1] = 1 />
			</cfif>
			<cfset queryAddColumn(q,"saleStatusId","integer",z) />
		<cfelse>
			<cfdump var="#q#" output="c:/temp/ItemGW_getItemById.html" format="html" expand="no">
		</cfif>
		<cfreturn q>
	</cffunction>

	<cffunction name="getWarningsAndCautions" output="false" hint="I return product warning information for the item code provided">
		<cfargument name="itemCode" required="yes" />
		<cfquery name="r" datasource="#_datasource.getname()#">
			select text from warningsAndCautions
				where stockCode = <cfqueryparam value="#arguments.itemCode#" cfsqltype="CF_SQL_STRING">
				and line <> 1 order by line
		</cfquery>

		<cfreturn r>
	</cffunction>
    
        <cffunction name="getProductCompany" output="false" hint="I return product company information for the item code provided">
		<cfargument name="itemCode" required="yes" />
		<cfquery name="r" datasource="#_datasource.getname()#">
			select companyId from item_company_join
				where itemCode = <cfqueryparam value="#arguments.itemCode#" cfsqltype="CF_SQL_STRING">
		</cfquery>
        
        		<cfreturn r>
	</cffunction>

	<cffunction name="getInstructions" output="false" hint="I return an product warning information for the item code provided">
		<cfargument name="itemCode" required="yes" />
		<cfquery name="instruction" datasource="#_datasource.getname()#">
			select name from instructions
				where id in (
					select instructionId from item_instructions
						where itemCode = <cfqueryparam value="#arguments.itemCode#" cfsqltype="CF_SQL_STRING">
				)
		</cfquery>

		<cfif instruction.recordCount>
			<cfreturn '/images/instructions/#instruction.name#' />
		</cfif>
	</cffunction>

	<cffunction name="getVideo" output="false" hint="Return a query containing the types and paths to assigned videos for view links, as applicable">
		<cfargument name="itemCode" required="yes" />

		<cfset var videoList = arrayNew(2)>

		<cfquery name="video" datasource="#_datasource.getname()#">
			select v.videoId, sortOrder, buttonTitle,
            (select videoHeader from videos
				where videoId = v.videoId) as 'videoHeader',
            (select videoEmbed from videos
				where videoId = v.videoId) as 'videoEmbed',
			(select videoName from videos
				where videoId = v.videoId) as 'videoName',
			(select videoType from videoTypes
				where videoTypeId =
				(select videoTypeId from video_type
					where videoId = v.videoId)) as 'videoType'
			from item_video v
			where itemcode = <cfqueryparam value="#arguments.itemCode#" cfsqltype="CF_SQL_STRING">
            order by sortOrder ASC
		</cfquery>
		<cfquery name="eh" datasource="#_datasource.getname()#">
			select handler,method from categoryType where description = 'video'
		</cfquery>

		<cfloop query="video">
			<cfset videoList[currentRow][1] = videoType />
			<cfset videoList[currentRow][2] = eh.handler & eh.method & '/' & videoName />
            <cfset videoList[currentRow][3] = buttonTitle />
            <cfset videoList[currentRow][4] = videoHeader />
            <cfset videoList[currentRow][5] = videoName />
		</cfloop>
		<cfreturn videoList />
	</cffunction>

	<cffunction name="getRelatedItems" output="false" hint="I return a list of add on items.">
		<cfargument name="itemCode" required="yes" />
		<cfset var result=arrayNew(1) />
		<cfset var x=0 />

		<cfquery name="r" datasource="#_datasource.getname()#">
			select relatedItemCode from item_relatedItems where ItemCode = <cfqueryparam value="#arguments.itemCode#" CFSQLType="CF_SQL_VARCHAR" maxlength="15">
		</cfquery>
		<cfloop query="r">
			<cfset x++ />
			<cfset result[x] = getItemByCode(relatedItemCode) />
		</cfloop>
		<cfreturn result>
	</cffunction>

	<cffunction name="getRelatedCategories" output="false" hint="I return a list of related categories (category IDs).">
		<cfargument name="itemCode" required="yes" />

		<cfquery name="r" datasource="#_datasource.getname()#">
		select relatedCategoryId from item_relatedCategories where itemCode = <cfqueryparam value="#arguments.itemCode#" CFSQLType="CF_SQL_VARCHAR" maxlength="15">
		</cfquery>
		<cfreturn r>
	</cffunction>

	<cffunction name="getOptions" output="false" hint="I return a options.">
		<cfargument name="itemCode" required="yes" />
		<cfset var result=arrayNew(1) />
		<cfset var x=0 />

		<cfquery name="r" datasource="#_datasource.getname()#">
			select optionCode from item_options where itemCode = <cfqueryparam value="#arguments.itemCode#" CFSQLType="CF_SQL_VARCHAR" maxlength="15">
		</cfquery>
		<cfloop query="r">
			<cfset x++ />
			<cfset result[x] = getItemByCode(optionCode) />
		</cfloop>
		<cfreturn result>
	</cffunction>

	<cffunction name="getItemStatus" output="false" hint="I return info on whether the product is New, Coming Soon, or nothing">
		<cfargument name="itemCode" required="yes" />
		<cfquery name="r" datasource="#_datasource.getname()#">
			select itemStatusDesc from itemStatusType
				where itemStatusId =
				(
					select itemStatus from item_status
						where itemCode = <cfqueryparam value="#arguments.itemCode#" cfsqltype="CF_SQL_STRING">
				)
		</cfquery>
		<cfif len(r.itemStatusDesc)>
			<cfquery name="r2" datasource="#_datasource.getname()#">
				select shippingDate from item_status
					where itemCode = <cfqueryparam value="#arguments.itemCode#" cfsqltype="CF_SQL_STRING">
			</cfquery>
            <cfparam default="#DateFormat(now(), 'm/d/yy')#" name="r2.shippingDate">
		</cfif>
		<cfif not isDefined("r.itemStatusDesc")>
			<cfset r = queryNew("itemStatusDesc","VarChar")>
		</cfif>
		<cfreturn r.itemStatusDesc>
	</cffunction>
    
    <cffunction name="getShippingDate" output="false" hint="I return ShippingDate">
		<cfargument name="itemCode" required="yes" />
        
        <cfquery name="r" datasource="#_datasource.getname()#">
            select shippingDate from item_status
            where itemCode = <cfqueryparam value="#arguments.itemCode#" cfsqltype="CF_SQL_STRING">
        </cfquery>
        
		<cfreturn r.shippingDate>
	</cffunction>

	<cffunction name="getPrevious" output="false" hint="Returns item code of previous item in same category or empty string if at beginning of category">
		<cfargument name="itemCode" required="yes">
		<cfargument name="categoryId" required="yes">

		<cfquery name="q" datasource="#_datasource.getname()#">
			select max(itemCode) as prev from item
				where itemCode < <cfqueryparam value="#arguments.itemCode#" cfsqltype="CF_SQL_STRING">
				and itemid in (select itemid from item_category where categoryId = <cfqueryparam value="#arguments.categoryId#" cfsqltype="CF_SQL_STRING">)
		</cfquery>

		<cfif q.prev is ''><cfset q.prev = arguments.itemCode></cfif> 

		<cfreturn q.prev>
	</cffunction>

	<cffunction name="getNext" output="false" hint="Returns item code of next item in same category or empty string if at end of category">
		<cfargument name="itemCode" required="yes">
		<cfargument name="categoryId" required="yes">

		<cfquery name="q" datasource="#_datasource.getname()#">
			select min(itemCode) as next from item
				where itemCode > <cfqueryparam value="#arguments.itemCode#" cfsqltype="CF_SQL_STRING">
				and itemid in (select itemid from item_category where categoryId = <cfqueryparam value="#arguments.categoryId#" cfsqltype="CF_SQL_STRING">)
		</cfquery>

		<cfif q.next is ''><cfset q.next = arguments.itemCode></cfif>

		<cfreturn q.next>
	</cffunction>

	<cffunction name="getDatasource" output="false" access="private">
		<cfreturn _datasource>
	</cffunction>

</cfcomponent>