using System;

using System.Collections.Generic;

using System.Linq.Expressions;

using System.Linq;

public class Program

{   

    // Just made this up to try and mimic syntax/feel of monarchRepo

    // Placed up here to easily see test data, and manipulate as wanted

    public class FakeMonarchRepo : IDisposable

    {   

        public Entity<job> Job = new Entity<job>(Jobs);

        public Entity<cust> Cust = new Entity<cust>(Custs);

        public static List<job> Jobs = new List<job>() 

        {

            new job() { job_id = "123456", system_id = "lagraphico", sub_job_id = "", cust_id_ordered_by = "def", job_desc = "test" },

            new job() { job_id = "123465", system_id = "LAgraphico", sub_job_id = "", cust_id_ordered_by = "abc", job_desc = "TEST" },

            new job() { job_id = "512346", system_id = "LAgraphico", sub_job_id = "", cust_id_ordered_by = "xyz", job_desc = "atest" },

            new job() { job_id = "654321", system_id = "lagraphico", sub_job_id = "", cust_id_ordered_by = "abc", job_desc = "aTEST" }

        };

        public static List<cust> Custs = new List<cust>() 

        {

            new cust() { cust_name = "Bob", cust_code = "def", system_id = "lagraphico" },

            new cust() { cust_name = "Jim", cust_code = "abc", system_id = "LAgraphico" },

            new cust() { cust_name = "Sue", cust_code = "xyz", system_id = "LAgraphico" },

            new cust() { cust_name = "Ann", cust_code = "123", system_id = "lagraphico" }

        };

        public void Dispose() { }

    }

    // Just made this up to try and mimic syntax/feel of huddleRepo

    // Placed up here to easily see test data, and manipulate as wanted

    public class FakeHuddleRepo : IDisposable

    {   

        public Entity<FTPLoad> FTPLoad = new Entity<FTPLoad>(FTPLoads);

        public static List<FTPLoad> FTPLoads = new List<FTPLoad>() 

        {

            new FTPLoad() { JobId = "123456", Pub = "PubA" },

            new FTPLoad() { JobId = "123456", Pub = "PubB" },

            new FTPLoad() { JobId = "123465", Pub = "PubA" },

            new FTPLoad() { JobId = "123465", Pub = "PubB" },

            new FTPLoad() { JobId = "512346", Pub = "PubB" },

            new FTPLoad() { JobId = "654321", Pub = "PubC" },

            new FTPLoad() { JobId = "789123", Pub = "PubC" }

        };

        public void Dispose() { }

    }

    // Used this to play/test model submissions, placed up here to easily see test data, and manipulate as wanted

    public static JobSearch model = new JobSearch() { Publishers = "Pub" };

    public static void Main()

    {

        var results = new List<JobSearchResult>();

        // key/string value is JobId and value/List<string> is the publishers associated with that JobId

        Dictionary<string, List<string>> publisherJobs = null;

        using (var fakeMonarchRepo = new FakeMonarchRepo()) 

        using (var fakeHuddleRepo = new FakeHuddleRepo()) 

        {

            // Determine JobIds to match based on model.Publishers

            if (!String.IsNullOrEmpty(model.Publishers))

            {

                publisherJobs = fakeHuddleRepo.FTPLoad.Get(f => !String.IsNullOrEmpty(f.Pub) && f.Pub.ToUpper().Contains(model.Publishers.ToUpper()))

                    .GroupBy(

                        f => f.JobId.Trim(), 

                        f => f.Pub.Trim(),

                        (jobId, pubs) => new { JobId = jobId.Trim(), Pubs = pubs.Distinct().ToList() })

                    .ToDictionary(

                        kv => kv.JobId.Trim(),

                        kv => kv.Pubs);

            }

            // Get the filter that goes into repo.Job.Get()

            var jobFilterLambda = GenerateJobFilterLambda(model, publisherJobs);

            var jobResults = fakeMonarchRepo.Job.Get(jobFilterLambda);

​

            // Get the filter that goes into repo.Cust.Get()

            var custFilterLambda = GenerateCustFilterLambda(model);

            var custResults = fakeMonarchRepo.Cust.Get(custFilterLambda);

​

            // Join the results together

            var joinedResults = jobResults.Join(custResults,

                                               j => j.cust_id_ordered_by,

                                               c => c.cust_code,

                                               (j, c) => new { JobInfo = j, CustInfo = c });

            if (joinedResults.Count() > 0) 

            {

                // Need to fill publisherJobs if model.Publishers wasn't passsed in

                if (publisherJobs == null) 

                {

                    var jobIdList = joinedResults.Select(j => j.JobInfo.job_id).ToList();

                    publisherJobs = fakeHuddleRepo.FTPLoad.Get(f => !String.IsNullOrEmpty(f.JobId) && jobIdList.Contains(f.JobId.Trim()))

                        .GroupBy(

                            f => f.JobId.Trim(), 

                            f => f.Pub.Trim(),

                            (jobId, pubs) => new { JobId = jobId.Trim(), Pubs = pubs.Distinct().ToList() })

                        .ToDictionary(

                            kv => kv.JobId.Trim(),

                            kv => kv.Pubs);

                }

                results = joinedResults.Select(combo => new JobSearchResult()

                                               {

                                                   JobId = combo.JobInfo.job_id,

                                                   CustomerName = combo.CustInfo.cust_name,

                                                   CustomerCode = combo.CustInfo.cust_code,

                                                   JobDescription = combo.JobInfo.job_desc,

                                                   ContactName = combo.JobInfo.contact_name,

                                                   LocationId = combo.JobInfo.last_locate_id,

                                                   CampaignTitle = combo.JobInfo.user_1,

                                                   EnteredDate = combo.JobInfo.date_entered,

                                                   Publishers = publisherJobs.ContainsKey(combo.JobInfo.job_id) ? publisherJobs[combo.JobInfo.job_id] : new List<string>()

                                               }).ToList();

            }

        }

    }

    // This function is needed to build what goes into the monarchRepo.Cust.Get() method

    // Because the different entities are separated so much by the repo, we have to search for ClientList in both Cust and Job, then JOIN after

    public static Expression<Func<cust, bool>> GenerateCustFilterLambda(JobSearch model) 

    {

        // Prepare cust parameter

        var custParam = Expression.Parameter(typeof(cust), "c");

        // Prepare reusable methods/constants

        var equalsIgnoreCaseMethod = typeof(string).GetMethod("Equals", new[] { typeof(string), typeof(StringComparison) });

        var listContainsMethod = typeof(List<string>).GetMethod("Contains", new[] { typeof(string) });

        var ignoreCaseConstant = Expression.Constant(StringComparison.OrdinalIgnoreCase);

​

        // Prepare List of Expressions to build up

        List<Expression> allExpressions = new List<Expression>();

        // Setup base/constant expressions for system_id and sub_job_id

        var lagraphicoConstant = Expression.Constant("lagraphico");  

        var efSystemIdProperty = Expression.Property(custParam, "system_id");

        var systemIdEqualsIgnoreCase = Expression.Call(lagraphicoConstant, equalsIgnoreCaseMethod, efSystemIdProperty, ignoreCaseConstant);

        allExpressions.Add(systemIdEqualsIgnoreCase);

        // Add expression for ClientList when values are present

        if (model.ClientList != null && model.ClientList.Count() > 0)

        {

            var efCustIdProperty = Expression.Property(custParam, "cust_code");

            var clientListValue = Expression.Constant(model.ClientList, typeof(List<string>));

            var clientFound = Expression.Call(clientListValue, listContainsMethod, efCustIdProperty);

            allExpressions.Add(clientFound);

        }

        // Build up all the expressions

        var finalExpression = allExpressions.First();

        if (allExpressions.Count > 1)

            for (var i = 1; i < allExpressions.Count; i++) 

                finalExpression = Expression.AndAlso(finalExpression, allExpressions[i]);

        var filterLambda = Expression.Lambda<Func<cust, bool>>(finalExpression, custParam);

​

        return filterLambda;

    }

    // This function is needed to build what goes into the monarchRepo.Job.Get() method

    // Because the different entities are separated so much by the repo, we have to search for ClientList in both Cust and Job, then JOIN after

    public static Expression<Func<job, bool>> GenerateJobFilterLambda(JobSearch model, Dictionary<string, List<string>> publisherJobs)

    {

        var jobParam = Expression.Parameter(typeof(job), "j");

        var equalsIgnoreCaseMethod = typeof(string).GetMethod("Equals", new[] { typeof(string), typeof(StringComparison) });

        var equalsMethod = typeof(string).GetMethod("Equals", new[] { typeof(string) });

        var toUpperMethod = typeof(string).GetMethod("ToUpper", System.Type.EmptyTypes);

        var stringContainsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });

        var listContainsMethod = typeof(List<string>).GetMethod("Contains", new[] { typeof(string) });

        var ignoreCaseConstant = Expression.Constant(StringComparison.OrdinalIgnoreCase);

        var negativeOneConstant = Expression.Constant(-1);

        var nullConstant = Expression.Constant(null);

​

        List<Expression> allExpressions = new List<Expression>();

​

        var lagraphicoConstant = Expression.Constant("lagraphico");

        var efSystemIdProperty = Expression.Property(jobParam, "system_id");

        var systemIdEqualsIgnoreCase = Expression.Call(lagraphicoConstant, equalsIgnoreCaseMethod, efSystemIdProperty, ignoreCaseConstant);

        allExpressions.Add(systemIdEqualsIgnoreCase);

​

        // When publisherJobs is null, model.Publishers was empty, so "" sub_job_id

        if (publisherJobs == null)

        {

            var emptyStringConstant = Expression.Constant(String.Empty);

            var efSubJobIdProperty = Expression.Property(jobParam, "sub_job_id");

            var subJobIdEquals = Expression.Call(emptyStringConstant, equalsMethod, efSubJobIdProperty);

            allExpressions.Add(subJobIdEquals);

        }

        // Otherwise, all sub_job_ids are fine, but must match job_id from FTPLoad

        else

        {

            var efJobIdProperty = Expression.Property(jobParam, "job_id");

            var jobIdListValue = Expression.Constant(publisherJobs.Keys.ToList(), typeof(List<string>));

            var jobIdFound = Expression.Call(jobIdListValue, listContainsMethod, efJobIdProperty);

            allExpressions.Add(jobIdFound);

        }

​

        if (!String.IsNullOrEmpty(model.JobNumberString))

        {

            var efJobIdProperty = Expression.Property(jobParam, "job_id");

            var efJobIdExists = Expression.NotEqual(efJobIdProperty, nullConstant);

            var startsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });

            var jobNumberStringValue = Expression.Constant(model.JobNumberString, typeof(string));

            var jobIdStartsWith = Expression.Call(efJobIdProperty, startsWithMethod, jobNumberStringValue);

            var existsAndStartsWith = Expression.AndAlso(efJobIdExists, jobIdStartsWith);

            allExpressions.Add(existsAndStartsWith);

        }

​

        if (!String.IsNullOrEmpty(model.JobDescString))

        {

            var efJobDescProperty = Expression.Property(jobParam, "job_desc");

            var efJobDescExists = Expression.NotEqual(efJobDescProperty, nullConstant);

            var jobDescStringValue = Expression.Constant(model.JobDescString, typeof(string));

            var efJobDescUpper = Expression.Call(efJobDescProperty, toUpperMethod);

            var jobDescStringUpper = Expression.Call(jobDescStringValue, toUpperMethod);

            var jobDescFound = Expression.Call(efJobDescUpper, stringContainsMethod, jobDescStringUpper);

            var existsAndFound = Expression.AndAlso(efJobDescExists, jobDescFound);

            allExpressions.Add(existsAndFound);

        }

​

        if (!String.IsNullOrEmpty(model.Contact))

        {

            var efContactNameProperty = Expression.Property(jobParam, "contact_name");

            var efContactNameExists = Expression.NotEqual(efContactNameProperty, nullConstant);

            var contactStringValue = Expression.Constant(model.Contact, typeof(string));

            var efContactNameUpper = Expression.Call(efContactNameProperty, toUpperMethod);

            var contactStringUpper = Expression.Call(contactStringValue, toUpperMethod);

            var contactFound = Expression.Call(efContactNameUpper, stringContainsMethod, contactStringUpper);

            var existsAndFound = Expression.AndAlso(efContactNameExists, contactFound);

            allExpressions.Add(existsAndFound);

        }

​

        if (model.Active.HasValue)

        {

            var efRecordActiveProperty = Expression.Property(jobParam, "record_active");

            var efRecordActiveHasValue = Expression.Property(efRecordActiveProperty, "HasValue");

            var efRecordActiveValue = Expression.Property(efRecordActiveProperty, "Value");

            var activeValue = Expression.Constant(model.Active.Value, typeof(byte));

            var activeMatch = Expression.Equal(efRecordActiveValue, activeValue);

            var hasValueAndActiveMatch = Expression.AndAlso(efRecordActiveHasValue, activeMatch);

            allExpressions.Add(hasValueAndActiveMatch);

        }

​

        if (model.OpenStartDate.HasValue || model.OpenEndDate.HasValue)

        {

            var efEnteredDateProperty = Expression.Property(jobParam, "date_entered");

            var efEnteredDateHasValue = Expression.Property(efEnteredDateProperty, "HasValue");

            var efEnteredDateValue = Expression.Property(efEnteredDateProperty, "Value");

            allExpressions.Add(efEnteredDateHasValue);

​

            if (model.OpenStartDate.HasValue)

            {

                var startDate = Expression.Constant(model.OpenStartDate.Value, typeof(DateTime));

                var efGreaterThanConstant = Expression.GreaterThanOrEqual(efEnteredDateValue, startDate);

                allExpressions.Add(efGreaterThanConstant);

            }

            if (model.OpenEndDate.HasValue)

            {

                var endDate = Expression.Constant(model.OpenEndDate.Value, typeof(DateTime));

                var efLessThanConstant = Expression.LessThanOrEqual(efEnteredDateValue, endDate);

                allExpressions.Add(efLessThanConstant);

            }

        }

​

        if (model.ClientList != null && model.ClientList.Count() > 0)

        {

            var efCustIdProperty = Expression.Property(jobParam, "cust_id_ordered_by");

            var clientListValue = Expression.Constant(model.ClientList, typeof(List<string>));

            var clientFound = Expression.Call(clientListValue, listContainsMethod, efCustIdProperty);

            allExpressions.Add(clientFound);

        }

​

        if (model.Campaigns != null && model.Campaigns.Count() > 0)

        {

            var efUser1Property = Expression.Property(jobParam, "user_1");

            var campaignListValue = Expression.Constant(model.Campaigns, typeof(List<string>));

            var campaignFound = Expression.Call(campaignListValue, listContainsMethod, efUser1Property);

            allExpressions.Add(campaignFound);

        }

​

        if (model.Locations != null && model.Locations.Count() > 0)

        {

            var efLastLocateProperty = Expression.Property(jobParam, "last_locate_id");

            var locationListValue = Expression.Constant(model.Locations, typeof(List<string>));

            var locationFound = Expression.Call(locationListValue, listContainsMethod, efLastLocateProperty);

            allExpressions.Add(locationFound);

        }

​

        var finalExpression = allExpressions.First();

        if (allExpressions.Count > 1)

            for (var i = 1; i < allExpressions.Count; i++)

                finalExpression = Expression.AndAlso(finalExpression, allExpressions[i]);

​

        var filterLambda = Expression.Lambda<Func<job, bool>>(finalExpression, jobParam);

​

        return filterLambda;

    }

}

​

// NOTE: In order to make the expression trees easier, made string[] into List<string>

// Make sure this doesn't break anything!!!

public class JobSearch

{

    public string Publishers { get; set; }

    public byte? Active { get; set; }

    public string LoadPrinter { get; set; }

    public string Contact { get; set; }

    public string JobDescString { get; set; }

    public string JobNumberString { get; set; }

    public List<string> Locations { get; set; }

    public List<string> ClientList { get; set; }

    public DateTime? OpenStartDate { get; set; }

    public DateTime? OpenEndDate { get; set; }

    public DateTime? JobCloseStartDate { get; set; }

    public DateTime? JobCloseEndDate { get; set; }

    public bool? JobArchived { get; set; }

    public List<string> Campaigns { get; set; }

}

​

// EF class for jobs

public class job

{

    public string created_by { get; set; }

    public Nullable<System.DateTime> created_date { get; set; }

    public string csr_id { get; set; }

    public string cust_id_bill_to { get; set; }

    public string cust_id_ordered_by { get; set; }

    public Nullable<System.DateTime> date_entered { get; set; }

    public string job_desc { get; set; }

    public string job_id { get; set; }

    public Nullable<byte> record_active { get; set; }

    public string sub_job_id { get; set; }

    public string system_id { get; set; }

    public string time_promised { get; set; }

    public string update_by { get; set; }

    public Nullable<System.DateTime> update_date { get; set; }

    public string update_time { get; set; }

    public string job_desc_sort { get; set; }

    public string user_1 { get; set; }

    public Nullable<System.DateTime> earliest_start_date { get; set; }

    public string last_locate_id { get; set; }

    public string contact_name { get; set; }

    public string job_title { get; set; }

    public string created_time { get; set; }

}

​

// EF class for custs (made it up by hand)

public class cust

{

    public string cust_code { get; set; }

    public string system_id { get; set; }

    public string cust_name { get; set; }

}

​

// EF class for FTPLoads (made it up by hand)

public class FTPLoad

{

    public string Pub { get; set; }

    public string JobId { get; set; }

}

​

public class JobSearchResult

{

    public string JobId { get; set; }

    public string CustomerName { get; set; }

    public string CustomerCode { get; set; }

    public string JobDescription { get; set; }

    public string ContactName { get; set; }

    public string LocationId { get; set; }

    public string CampaignTitle { get; set; }

    public DateTime? EnteredDate { get; set; }

    public List<string> Publishers { get; set; }

}

​

// Just made this up to make the fake repo work

public class Entity<T>

{

    public IQueryable<T> dbSet { get; set; }

    public Entity(List<T> mockData) 

    {

        dbSet = mockData.AsQueryable();

    }

​

    public List<T> Get(Expression<Func<T, bool>> filter = null, Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null, string includeProperties = "")

    {

        IQueryable<T> query = dbSet;

        if (filter != null)

            query = query.Where(filter);

        if (orderBy != null)

            return orderBy(query).ToList();

        else

            return query.ToList();

    }

}