DECLARE @StoredProc VARCHAR(200)= 'qryExport_SuggestedCountUsageByStore'
DECLARE @TaskName VARCHAR(200) = 'Suggested Count Data'

IF NOT EXISTS (
		SELECT *
		FROM tbXSLTManager
		WHERE NAME = @TaskName
		)
BEGIN
	DECLARE @XSLTManagerID BIGINT

	INSERT INTO tbXSLTManager (
		[Name]
		,Description
		,StoredProcedure
		,UserAccess
		,Instructions
		,FileName
		,Frequency
		,Active
		,BusinessCalendar
		,CheckExportStatus
		,ServiceMethod
		)
	VALUES (
		@TaskName
		,@TaskName
		,@StoredProc
		,0
		,''
		,'SuggestCountData_<storeNum>.csv'
		,'D'
		,1
		,0
		,1
		,''
		)

	SET @XSLTManagerId = (
			SELECT @@IDENTITY
			)

	--PARAMETERS
	INSERT INTO tbXSLTManagerDetails (
		XsltManagerID
		,[Name]
		,[Type]
		,TestValue
		)
	VALUES (
		@XSLTManagerId
		,'EntityID'
		,'EntityID'
		,'1041'
		)
		
	INSERT INTO tbXSLTManagerDetails (
		XsltManagerID
		,[Name]
		,[Type]
		,TestValue
		)
	VALUES (
		@XSLTManagerId
		,'StartDate'
		,'CalendarDay'
		,'2021-4-01'
		)

	-- 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:apply-templates select="tbResults"/>
		</Root>
	</xsl:template>

	<xsl:template name="Header">
		<line>
			<field type="open" >Entity</field>
			<field type="open" >ItemName</field>
			<field type="open" >ItemCode</field>
			<field type="open" >SuggestedValue</field>
			<field type="open" >SubmittedValue</field>
			<field type="open" >UserEdited</field>
			<field type="open" >IndividualCosts</field>
			<field type="open" >ExtendedCosts</field>
		</line>
	</xsl:template>

	<xsl:template match="tbResults">

		<line>
			<field type="open">
				<xsl:value-of select="Entity"/>
			</field>
			<field type="open">
				<xsl:value-of select="ItemName"/>
			</field>
			<field type="open">
				<xsl:value-of select="ItemCode"/>
			</field>
			<field type="open">
				<xsl:value-of select="mxExtension:FormatNumber(SuggestedValue, ''0.00'')" />
			</field>
			<field type="open">
				<xsl:value-of select="mxExtension:FormatNumber(SubmittedValue, ''0.00'')" />
			</field>
			<field type="open">
				<xsl:value-of select="UserEdited"/>
			</field>
			<field type="open">
				<xsl:value-of select="IndividualCosts"/>
			</field>
			<field type="open">
				<xsl:value-of select="ExtendedCosts"/>
			</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"/>
  <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=''quote'']" >
    <xsl:text>"</xsl:text>
    <xsl:value-of select="text()"/>
    <xsl:text>"</xsl:text>
  </xsl:template>
</xsl:stylesheet>
'
		)
END
GO

DECLARE @ConcatenatorTaskName VARCHAR(200) = 'Suggested Count Data Concatenator'

IF NOT EXISTS (
		SELECT *
		FROM tbXSLTManager
		WHERE NAME = @ConcatenatorTaskName
		)
BEGIN
	DECLARE @XSLTManagerID BIGINT

	INSERT INTO tbXSLTManager (
		[Name]
		,Description
		,StoredProcedure
		,UserAccess
		,Instructions
		,FileName
		,Frequency
		,Active
		,BusinessCalendar
		,CheckExportStatus
		,ServiceMethod
		)
	VALUES (
		@ConcatenatorTaskName
		,@ConcatenatorTaskName
		,''
		,0
		,''
		,'SuggestCountData_<yyyy><mm><dd>.csv'
		,'D'
		,1
		,0
		,0
		,''
		)

	SET @XSLTManagerId = (
			SELECT @@IDENTITY
			)

	-- Phase 0
	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="text"  />

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

    <xsl:template match="mxExportData">
        <xsl:value-of select="."/>
    </xsl:template>

    <xsl:template name="Header">Entity,ItemName,ItemCode,SuggestedValue,SubmittedValue,UserEdited,IndividualCosts,ExtendedCosts
    </xsl:template>

</xsl:stylesheet>'
		)
END
GO

