gistfile1.txt

using AddWaterMark;
using AugustaReport.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


// Daemon to move files between JDEdwards and SQL Server Data Stores
// Runs on a one year moving target.
// Updates if present creates if not available within the window
// Applies watermarks.

namespace AddWatermarker
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var source = new SourceDB())
            using (var destination = new DestinationDB())
            {
                // Get a list of Reports configured on the system
                var config = destination.Database.SqlQuery<ReportsConfiguration>(@"
                    SELECT  [ReportID]   ,[ReportName]   ,[Description] ,[Version]    ,[WaterMark]
                      FROM [augustareports].[dbo].[ReportsConfiguration]
                    ").ToList();

                //Generate dynamic SQL based upon Report Names in system
                var ReportLista = from x in config
                                  select new { Description=x.Description, ReportName = x.ReportName, ReportVersion = x.Version};
                var searchStr = "";
                var searchStrB = "";
                foreach (var a in ReportLista)
                {
                    if (searchStr != "") searchStr += " or ";
                    searchStr += " LEFT(JCFNDFUF2, CHARINDEX('_', JCFNDFUF2, 0) - 1) like '" + a.ReportName.ToString() + "' ";
                    if (searchStrB != "") searchStrB += " or ";
                    searchStrB += " SUBSTRING(JCFNDFUF2, CHARINDEX('_', JCFNDFUF2, CHARINDEX('_', JCFNDFUF2, 0)) + 1, CHARINDEX('_', JCFNDFUF2, CHARINDEX('_', JCFNDFUF2, 0) + 1) - CHARINDEX('_', JCFNDFUF2, 0) - 1)  like '" + a.ReportVersion.ToString() + "' ";
                }
                // Retrieve all reports within the past year and re-apply watermark
                // This will ensure any newly added reports have been add
                var str = String.Format(@"SELECT
                       LTRIM(RTRIM(JCUSER)) as 'RequestUser',
                    RTRIM(jcfndfuf2) + '.pdf' as 'FileName',
                  LEFT(JCFNDFUF2, CHARINDEX('_', JCFNDFUF2, 0) - 1) as 'ReportName',
                SUBSTRING(JCFNDFUF2, CHARINDEX('_', JCFNDFUF2, CHARINDEX('_', JCFNDFUF2, 0)) + 1, CHARINDEX('_', JCFNDFUF2, CHARINDEX('_', JCFNDFUF2, 0) + 1) - CHARINDEX('_', JCFNDFUF2, 0) - 1) as 'ReportVersion',
            JDE910.dbo.JDEDateConvert(JCACTDATE) as 'ExecutionDate'
                FROM JDE910.SVM910.F986110
                WHERE RTRIM(jcjobsts)= 'D'
                AND RTRIM(jcenhv)IN('PD910','JPD910')
                AND({0})  AND({1}) AND
            JDE910.dbo.JDEDateConvert(JCACTDATE) between DateAdd(Year,-1,GetDate()) and GetDate()", searchStr,searchStrB);
                var results = source.Database.SqlQuery<F986110>(str).ToList();

                foreach (var r in results)
                {
                    // Retrieve report, update if it does not exist
                    ReportsStore rs = destination.ReportsStores.Where(x => x.ReportName == r.ReportName && x.ReportVersion == r.ReportVersion && x.RequestUser == r.RequestUser && x.ExecutionDate == r.ExecutionDate).SingleOrDefault();
                    if (rs == null)
                    {
                        rs = new ReportsStore();
                        rs.Description = ReportLista.Where(x => x.ReportName == r.ReportName && r.ReportVersion == r.ReportVersion).First().Description;
                        rs.ReportName = r.ReportName;
                        rs.ReportVersion = r.ReportVersion;
                        rs.RequestUser = r.RequestUser;
                        rs.ExecutionDate = r.ExecutionDate;
                        rs.FileName = r.FileName;
                        destination.ReportsStores.Add(rs);
                    }

                    // WaterMark any files and move to SQL Data Store
                    if (File.Exists("\\\\usgaatleoa02hv\\printqueue\\" + r.FileName))
                    {
                        rs.WithoutWaterMarket = Util.ReadFile(new FileStream("\\\\usgaatleoa02hv\\printqueue\\" + r.FileName, FileMode.Open));
                        var wm = config.Where(x => x.ReportName == r.ReportName).First().WaterMark;
                        if (wm == null) wm = "Set Watermark";
                        rs.WithWaterMarket = Util.ReadFileWithWaterMark(new FileStream("\\\\usgaatleoa02hv\\printqueue\\" + r.FileName, FileMode.Open), wm);
                    }
                    rs.Description = ReportLista.Where(x => x.ReportName == r.ReportName && r.ReportVersion == r.ReportVersion).First().Description;
                    destination.SaveChanges();
                }

            }
        }
    }
}