DECLARE @StoredProc VARCHAR(200) = 'qryMMSGetActiveVendorEntityItemsByEntityId'
Declare @TaskName varchar(50) = 'Get Active VendorEntityItems By EntityId'
Declare @TaskDescription varchar(100) = 'Get Active VendorEntityItems By EntityId'
Declare @Destination varchar(100) = 'W:\MacromatiX\SiteData\JATesting\DataExports\InventoryAdjustExport'
Declare @ExportZone varchar(50) = ''
Declare @ExportFileName varchar(100) = 'ActiveVendorEntityItems_<storeNum>.csv'

IF NOT EXISTS(Select * from dbo.tbTaskname where taskname = @TaskName)
BEGIN
	Declare @taskId int
	
	DECLARE @XSLTManagerID BIGINT
	
	insert into dbo.tbTaskName (TaskName, TaskDescription)
	Values (@TaskName, @TaskDescription)

	set @taskId = Scope_Identity()


	INSERT INTO tbXSLTManager (
		[Name]
		,Description
		,StoredProcedure
		,UserAccess
		,Instructions
		,FileName
		,Frequency
		,Active
		,BusinessCalendar
		,CheckExportStatus
		,ServiceMethod
		)
	VALUES (
		@TaskName
		,@TaskDescription
		,@StoredProc
		,0
		,''
		,@ExportFileName
		,'D'
		,1
		,0
		,1
		,''
		)

	SET @XSLTManagerId = (
			SELECT @@IDENTITY
			)

	--PARAMETERS
	INSERT INTO tbXSLTManagerDetails (
		XsltManagerID
		,[Name]
		,[Type]
		,TestValue
		)
	VALUES (
		@XSLTManagerId
		,'EntityID'
		,'EntityID'
		,'269'
		)
		
	-- Phase 0
	INSERT INTO [dbo].[tbXSLTManagerScripts] (
		[XSLTManagerID]
		,[Script]
		)
	VALUES (
		@XSLTManagerID
		,
		'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:mxExtension="urn:xsltExtension-String"
                 exclude-result-prefixes="fo mxExtension" >

	<xsl:template match="/Root">
		<Root>
		    <xsl:call-template name="Header"/>	
			<xsl:apply-templates select="tbResults"/>
		</Root>
	</xsl:template>

	<xsl:template name="Header">
		<line>
			<field type="open" >ItemCode</field>
			<field type="open" >Entity</field>
			<field type="open" >VendorName</field>
			<field type="open" >VendorCode</field>
			<field type="open" >VendorDescription</field>
			<field type="open" >PurchaseUnit</field>
			<field type="open" >DiscontinueDateOverridden</field>

		</line>
	</xsl:template>

	<xsl:template match="tbResults">

		<line>
			<field type="open">
				<xsl:value-of select="ItemCode"/>
			</field>
			<field type="open">
				<xsl:value-of select="Entity"/>
			</field>
			<field type="open">
				<xsl:value-of select="VendorName" />
			</field>
			<field type="open">
				<xsl:value-of select="VendorCode" />
			</field>
			<field type="open">
				<xsl:value-of select="VendorDescription"/>
			</field>
			<field type="open">
				<xsl:value-of select="PurchaseUnit"/>
			</field>
			<field type="open">
				<xsl:value-of select="DiscontinueDateOverridden"/>
			</field>
		</line>

	</xsl:template>
</xsl:stylesheet>'
		)

	-- Phase 1
	INSERT INTO [dbo].[tbXSLTManagerScripts] (
		[XSLTManagerID]
		,[Script]
		)
	VALUES (
		@XSLTManagerID
		,
		'<xsl:stylesheet version = "1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output omit-xml-declaration="yes" method="xml" encoding="iso-8859-15"/>

  <xsl:variable name="DELIMITER" select="'',''"/>

  <xsl:variable name="EOL">
    <xsl:text>
</xsl:text>
  </xsl:variable>

  <xsl:variable name="EOF" select="''''"/>

  <xsl:template match="/Root">
    <xsl:apply-templates select="line"/>
    <xsl:value-of select="$EOF"/>
  </xsl:template>

  <xsl:template match="line">
    <xsl:for-each select="field">
      <xsl:apply-templates select="."/>
      <xsl:if test="not(position()=last())">
        <xsl:value-of select="$DELIMITER"/>
      </xsl:if>
    </xsl:for-each>
    <xsl:value-of select="$EOL"/>
  </xsl:template>

  <xsl:template match="field[@type=''open'']">
    <xsl:value-of select="text()"/>
  </xsl:template>

  <xsl:template match="field[@type=''spaced'']">
    <xsl:text> </xsl:text>
    <xsl:value-of select="text()"/>
    <xsl:text> </xsl:text>
  </xsl:template>

  <xsl:template match="field[@type=''num'']">
    <xsl:call-template name="padLeft">
      <xsl:with-param name="length" select="@length"/>
      <xsl:with-param name="char" select="''''"/>
    </xsl:call-template>
  </xsl:template>

  <xsl:template match="field[@type=''leftPad'']">
    <xsl:call-template name="padLeft">
      <xsl:with-param name="length" select="@length"/>
    </xsl:call-template>
  </xsl:template>

  <xsl:template match="field[@type=''txt'']">
    <xsl:call-template name="padRight">
      <xsl:with-param name="length" select="@length"/>
    </xsl:call-template>
  </xsl:template>

  <xsl:template match="field">
    <xsl:choose>
      <xsl:when test="not(@length=string-length(text()))">
        <xsl:message>
          <xsl:text>Invalid Field (Size not exact): </xsl:text>
          <xsl:value-of select="text()"/>
        </xsl:message>
        <xsl:call-template name="padRight">
          <xsl:with-param name="length" select="@length"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="text()"/>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <xsl:template name="padLeft">
    <xsl:param name="char" select="'' ''"/>
    <xsl:param name="length" select="0"/>
    <xsl:variable name="padding">
      <xsl:call-template name="padStr">
        <xsl:with-param name="char" select="$char"/>
        <xsl:with-param name="length" select="$length - string-length(text())"/>
      </xsl:call-template>
    </xsl:variable>
    <xsl:value-of select="substring(concat($padding,text()), 0, $length+1)"/>
  </xsl:template>

  <xsl:template name="padRight">
    <xsl:param name="char" select="'' ''"/>
    <xsl:param name="length" select="0"/>
    <xsl:variable name="padding">
      <xsl:call-template name="padStr">
        <xsl:with-param name="char" select="$char"/>
        <xsl:with-param name="length" select="$length - string-length(text())"/>
      </xsl:call-template>
    </xsl:variable>
    <xsl:value-of select="substring(concat(text(), $padding), 0, $length+1)"/>
  </xsl:template>

  <xsl:template name="padStr">
    <xsl:param name="char"/>
    <xsl:param name="length" select="0"/>
    <xsl:if test="$length>0">
      <xsl:call-template name="padStr">
        <xsl:with-param name="char" select="$char"/>
        <xsl:with-param name="length" select="number($length)-1"/>
      </xsl:call-template>
      <xsl:value-of select="$char"/>
    </xsl:if>
  </xsl:template>

</xsl:stylesheet>
'
		)
		
		
	Insert Into dbo.tbTaskXsltExportDetail(TaskNameId, XsltManagerId)
	Values (@taskId, @xsltManagerId)


	insert into dbo.tbTaskXsltExport (TaskNameId,
		ExportDestination,
		ExportMissingDays,
		ExportCurrentDay,
		ArchiveAfterSent,
		RunOnQueueNumber,
		OverwriteExistingFiles,
		AutoStart,
		CreateEmptyFiles,
		Concatenation,
		Compression,
		ConfirmationSuccess,
		ConfirmationError,
		QueueMessageType,
		OverwriteQueueMessage,
		ExportDateRangeType)
	values (@taskId, @Destination, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1)	
		
END
GO



