<?xml version="1.0" encoding="utf-8"?>
<WorkOrder xmlns="http://Icod.Wod" xsi:schemaLocation="http://Icod.Wod \\someServer\Icod.Wod\schema0.xsd"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    jobName="SalesForce Data Pull"
    emailTo="[email protected], [email protected]"
>
    <!--
    emailTo="[email protected]"
    emailTo="[email protected], [email protected]"
    -->
    <steps>
        <fileOperation xsi:type="mkDir" path="\\someServer\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" />

        <dbOperation xsi:type="command" connectionStringName="SalesForce" commandText="delete from dbo.SF_DataLoad_ErrorLog" commandTimeout="180" />
        <dbOperation xsi:type="fileImport" connectionStringName="SalesForce" updateBatchSize="20" tableName="SF_DataLoad_ErrorLog" namespace="dbo" commandTimeout="180">
            <columnMapping>
                <map from="%wod:FileName%" to="FileName" />
                <map from="EMPLID__C" to="EmplId" />
                <map from="NETID__C" to="NetId" />
            </columnMapping>
            <source xsi:type="delimitedFile" path="\\someServer\SF_Downloads\Errors" name="*.Error.*" fieldSeperator="44" />
        </dbOperation>
        <dbOperation xsi:type="fileExport" connectionStringName="SalesForce"
            commandText="select WhenLoaded, [FileName], Count( [Error] ) as ErrorCount from dbo.SF_DataLoad_ErrorLog where ( ( [Error] is not null ) and ( '' != [Error] ) ) group by [FileName], [WhenLoaded]"
            commandTimeout="180"
        >
            <destination xsi:type="delimitedFile" path="\\someServer\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" name="ErrorSummary.csv" fieldSeperator="44" writeIfEmpty="false" >
                <columns>
                    <column name="WhenLoaded" formatString="{0:d}" />
                </columns>
            </destination>
        </dbOperation>
        <dbOperation xsi:type="fileExport" connectionStringName="SalesForce"
            commandText="select * from dbo.SF_DataLoad_ErrorLog where ( ( [Error] is not null ) and ( '' != [Error] ) )"
            commandTimeout="180"
        >
            <destination xsi:type="delimitedFile" path="\\someServer\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" name="ErrorDetail.csv" fieldSeperator="44" writeIfEmpty="false" >
                <columns>
                    <column name="WhenLoaded" formatString="{0:d}" />
                </columns>
            </destination>
        </dbOperation>
        <email
            subject="%wod:JobName%: error summary"
            sendIfEmpty="false"
            to="%wod:EmailTo%"
        >
            <attachments>
                <attach path="\\someServer\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" name="ErrorSummary.csv" />
                <attach path="\\someServer\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" name="ErrorDetail.csv" />
            </attachments>
        </email>
        <fileOperation xsi:type="deleteFile"
            path="\\someServer\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" name="ErrorSummary.csv"
        />
        <fileOperation xsi:type="deleteFile"
            path="\\someServer\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" name="ErrorDetail.csv"
        />
        <dbOperation xsi:type="command" connectionStringName="SalesForce" commandText="delete from dbo.SF_DataLoad_ErrorLog" commandTimeout="180" />

        <fileOperation xsi:type="mkZip" truncateEntryName="true" writeIfEmpty="false" move="true"
            path="\\someServer\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%"
            name="%wod:DateTime{yyyy-MM-dd}%T%wod:DateTime{HHmmss}%.zip"
        >
            <sources>
                <source path="\\someServer\SF_Downloads" name="debug.log" />
                <source path="\\someServer\SF_Downloads\Errors" name="*.Error.txt" />
                <source path="\\someServer\SF_Downloads\Errors" name="*.Error.csv" />
            </sources>
        </fileOperation>
    </steps>
</WorkOrder>