using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using Foresight.Data;

namespace IronIntel.Contractor.FilterQ
{
    public class FilterQManagememt
    {
        private static readonly string CLASSNAME = typeof(FilterQManagememt).FullName;

        private static string IronIntelDbString
        {
            get { return ConfigurationManager.AppSettings["JRE_IronIntelDb"]; }
        }

        private static void ChangeViewToTable()
        {
            const string SQL = @"insert into MACHINETASKS(TaskId,MACHINEID,TASKDESCRIPTION,TASKCOMPLETE,CUSTOMERPO,PRIORITY,SHIPNOTES,CURRENT_JOB,JOBCONTACT,JOBCONTACTNUMBER,
                JOBMAILSHIPADDRESS1,JOBMAILSHIPADDRESS2,JOBMAILSHIPADDRESSCITY,JOBMAILSHIPADDRESSSTATE,JOBMAILSHIPADDRESSZIP,ORGANIZATIONMACHINECODE,MANUFACTURERMAKE,
                MODEL,SN,CUMULATIVE_HOURS,MACHINETASKHOURCHECK,DIFFTOSERVICE,ESTSERVICENEEDEDBY,ESTSHIPDATE,ORGID,ORGANIZATIONNAME,MACHINETASKTYPEID)
                select v.TaskId,v.MACHINEID,v.TASKDESCRIPTION,v.TASKCOMPLETE,v.CUSTOMERPO,v.PRIORITY,v.ShipNotes,v.CURRENT_JOB,v.JOBCONTACT,
                v.JobContactNumber,v.JobMailShipAddress1,v.JobMailShipAddress2,v.JobMailShipAddressCity,v.JobMailShipAddressState,v.JobMailShipAddressZip,
                v.ORGANIZATIONMACHINECODE,v.MANUFACTURERMAKE,v.MODEL,v.SN,v.Cumulative_Hours,v.MachineTaskHourCheck,v.DIFFTOSERVICE,v.ESTSERVICENEEDEDBY,
                v.ESTSHIPDATE,v.ORGID,v.ORGANIZATIONNAME,m.MACHINETASKTYPEID from vwMACHINETASKSNOTIFICATION as v, vwMACHINETASKS as m  
                where v.taskid=m.TaskId and v.TaskId not in(select TaskId from MACHINETASKS)";
            const string SQL_V = @"update a set MACHINEID=b.MACHINEID,CURRENT_JOB=b.CURRENT_JOB,JOBCONTACT=b.JOBCONTACT,JOBCONTACTNUMBER=b.JOBCONTACTNUMBER,JOBMAILSHIPADDRESS1=b.JOBMAILSHIPADDRESS1,
                JOBMAILSHIPADDRESS2=b.JOBMAILSHIPADDRESS2,JOBMAILSHIPADDRESSCITY=b.JOBMAILSHIPADDRESSCITY,JOBMAILSHIPADDRESSSTATE=b.JOBMAILSHIPADDRESSSTATE,JOBMAILSHIPADDRESSZIP=b.JOBMAILSHIPADDRESSZIP,
                ORGANIZATIONMACHINECODE=b.ORGANIZATIONMACHINECODE,MANUFACTURERMAKE=b.MANUFACTURERMAKE,MODEL=b.MODEL,SN=b.SN,CUMULATIVE_HOURS=b.CUMULATIVE_HOURS,
                MACHINETASKHOURCHECK=b.MACHINETASKHOURCHECK,DIFFTOSERVICE=b.DIFFTOSERVICE,ESTSERVICENEEDEDBY=b.ESTSERVICENEEDEDBY,ESTSHIPDATE=b.ESTSHIPDATE,
                ORGID=b.ORGID,ORGANIZATIONNAME=b.ORGANIZATIONNAME,DATALOADEDFROMVIEW=1
                from MACHINETASKS a,vwMACHINETASKSNOTIFICATION b where a.TaskId = b.TaskId and isnull(a.DATALOADEDFROMVIEW,0)=0";
            FISqlConnection db = new FISqlConnection(IronIntelDbString);
            db.ExecSQL(SQL);
            db.ExecSQL(SQL_V);
        }

        public static MachineTasksNotificationInfo[] GetTasksNotofications()
        {
            const string SQL_1 = @"select TASKID,MACHINEID,CURRENT_JOB,JOBCONTACT,JOBCONTACTNUMBER,JOBMAILSHIPADDRESS1,JOBMAILSHIPADDRESS2,JOBMAILSHIPADDRESSCITY,JOBMAILSHIPADDRESSSTATE,JOBMAILSHIPADDRESSZIP,
                ORGANIZATIONMACHINECODE,MANUFACTURERMAKE,MODEL,SN,CUMULATIVE_HOURS,MACHINETASKHOURCHECK,DIFFTOSERVICE,ESTSERVICENEEDEDBY,ESTSHIPDATE,UPSTRACKINGNUMBER,REQUESTJRETOSERVICE,
                APPROVEDBY,PRIORITY,CUSTOMERPO,CUSTOMERWO,ALTERNATEMAILSHIPADDRESS1,ALTERNATEMAILSHIPADDRESS2,ALTERNATEMAILSHIPADDRESSCITY,ALTERNATEMAILSHIPADDRESSSTATE,ALTERNATEMAILSHIPADDRESSZIP,
                SHIPNOTES,ORGANIZATIONNAME,TASKCOMPLETE, TASKCOMPLETEDHOURS,ALTJOB,ALTJOBSITECONTACT from MACHINETASKS where ESTSHIPDATE is not null";
            const string SQL_2 = SQL_1 + " and ORGID in(select ORGID from ORGANIZATION o where o.CONTRACTORSITEID={0})";

            ChangeViewToTable();
            var company = SystemParams.CustomerDetail;
            FISqlConnection db = new FISqlConnection(IronIntelDbString);
            DataTable dt = null;
            if (company.IsDealer)
            {
                dt = db.GetDataTableBySQL(SQL_1 + " order by CURRENT_JOB,JOBCONTACT");
            }
            else
            {
                dt = db.GetDataTableBySQL(SQL_2 + " order by CURRENT_JOB,JOBCONTACT", company.ID);
            }

            List<MachineTasksNotificationInfo> list = new List<MachineTasksNotificationInfo>();
            foreach (DataRow dr in dt.Rows)
            {
                MachineTasksNotificationInfo tt = new MachineTasksNotificationInfo();
                tt.TaskID = FIDbAccess.GetFieldString(dr["TASKID"], string.Empty);
                tt.MachineID = FIDbAccess.GetFieldString(dr["MACHINEID"], string.Empty);
                tt.CurrentJob = FIDbAccess.GetFieldString(dr["CURRENT_JOB"], string.Empty);
                tt.JobContact = FIDbAccess.GetFieldString(dr["JOBCONTACT"], string.Empty);
                tt.JobContactNumber = FIDbAccess.GetFieldString(dr["JOBCONTACTNUMBER"], string.Empty);
                tt.JobAddress1 = FIDbAccess.GetFieldString(dr["JOBMAILSHIPADDRESS1"], string.Empty);
                tt.JobAddress2 = FIDbAccess.GetFieldString(dr["JOBMAILSHIPADDRESS2"], string.Empty);
                tt.JobAddressCity = FIDbAccess.GetFieldString(dr["JOBMAILSHIPADDRESSCITY"], string.Empty);
                tt.JobAddressState = FIDbAccess.GetFieldString(dr["JOBMAILSHIPADDRESSSTATE"], string.Empty);
                tt.JobAddressZip = FIDbAccess.GetFieldString(dr["JOBMAILSHIPADDRESSZIP"], string.Empty);
                tt.OrgMachineCode = FIDbAccess.GetFieldString(dr["ORGANIZATIONMACHINECODE"], string.Empty);
                tt.ManufactuerMake = FIDbAccess.GetFieldString(dr["MANUFACTURERMAKE"], string.Empty);
                tt.Model = FIDbAccess.GetFieldString(dr["MODEL"], string.Empty);
                tt.SN = FIDbAccess.GetFieldString(dr["SN"], string.Empty);
                tt.CumulativeHours = FIDbAccess.GetFieldDouble(dr["CUMULATIVE_HOURS"], 0);
                tt.MachineTaskHourCheck = FIDbAccess.GetFieldInt(dr["MACHINETASKHOURCHECK"], 0);
                tt.DiffToService = FIDbAccess.GetFieldDouble(dr["DIFFTOSERVICE"], 0);
                tt.ESTServiceNeededBy = FIDbAccess.GetFieldDateTime(dr["ESTSERVICENEEDEDBY"], DateTime.MinValue);
                tt.ESTShopDate = FIDbAccess.GetFieldDateTime(dr["ESTSHIPDATE"], DateTime.MinValue);
                tt.UPSTrackingNumber = FIDbAccess.GetFieldString(dr["UPSTRACKINGNUMBER"], string.Empty);
                tt.RequestJREToService = FIDbAccess.GetFieldInt(dr["REQUESTJRETOSERVICE"], 0) == 1;
                string by = FIDbAccess.GetFieldString(dr["APPROVEDBY"], string.Empty);
                tt.Approved = (!string.IsNullOrWhiteSpace(by) && by != Guid.Empty.ToString() ? true : false);
                tt.Priority = FIDbAccess.GetFieldString(dr["PRIORITY"], string.Empty);
                tt.CustomerPO = FIDbAccess.GetFieldString(dr["CUSTOMERPO"], string.Empty);
                tt.CustomerWO = FIDbAccess.GetFieldString(dr["CUSTOMERWO"], string.Empty);
                tt.AlternateAddress1 = FIDbAccess.GetFieldString(dr["ALTERNATEMAILSHIPADDRESS1"], string.Empty);
                tt.AlternateAddress2 = FIDbAccess.GetFieldString(dr["ALTERNATEMAILSHIPADDRESS2"], string.Empty);
                tt.AlternateAddressCity = FIDbAccess.GetFieldString(dr["ALTERNATEMAILSHIPADDRESSCITY"], string.Empty);
                tt.AlternateAddressState = FIDbAccess.GetFieldString(dr["ALTERNATEMAILSHIPADDRESSSTATE"], string.Empty);
                tt.AlternateAddressZip = FIDbAccess.GetFieldString(dr["ALTERNATEMAILSHIPADDRESSZIP"], string.Empty);
                tt.ShipNotes = FIDbAccess.GetFieldString(dr["SHIPNOTES"], string.Empty);
                tt.OrganizationName = FIDbAccess.GetFieldString(dr["ORGANIZATIONNAME"], string.Empty);
                tt.TaskComplete = FIDbAccess.GetFieldInt(dr["TASKCOMPLETE"], 0) == 1;
                tt.TaskCompletedHours = FIDbAccess.GetFieldDouble(dr["TASKCOMPLETEDHOURS"], 0);
                tt.AltJob = FIDbAccess.GetFieldString(dr["ALTJOB"], string.Empty);
                tt.AltJobSiteContact = FIDbAccess.GetFieldString(dr["ALTJOBSITECONTACT"], string.Empty);
                list.Add(tt);
            }
            return list.ToArray();
        }

        public static void SaveMachineTasks(MachineTasksNotificationInfo[] tasks, string useriid)
        {
            var company = SystemParams.CustomerDetail;
            if (company.IsContractor)
            {
                SaveByContractor(tasks, useriid);
            }
            else
            {
                SaveByDealer(tasks, useriid);
            }
        }

        private static void SaveTaskHistory(FIDbTransaction tran, string taskid)
        {
            const string SQL_H = @"insert into MACHINETASKS_HISTORY(TaskId,MACHINEID,MACHINETASKTYPEID,TASKDESCRIPTION,TASKDATE,TASKCOMPLETE,NOTIFICATIONSENT,TASKCOMPLETEDATE
                              ,LASTUPDATEDATE,CUSTOMERPO,PRIORITY,SHIPDATE,SHIPNOTES,ALTERNATIVESHIPCONTACT,OVERRIDEJOBID,ALTERNATEMAILSHIPADDRESS1
                              ,ALTERNATEMAILSHIPADDRESS2 ,ALTERNATEMAILSHIPADDRESSSTATE,ALTERNATEMAILSHIPADDRESSZIP,ALTERNATEMAILSHIPADDRESSCITY,APPROVEDBY
                              ,CUSTOMERWO ,ALTERNATESHIPCONTACTNUMBER ,REQUESTDEALERTOSERVICE ,SHIPTRACKINGNO,SHIPTRACKINGSTATUS,UPSTRACKINGNUMBER ,REQUESTJRETOSERVICE
                              ,CURRENT_JOB ,JOBCONTACT,JOBCONTACTNUMBER,JOBMAILSHIPADDRESS1 ,JOBMAILSHIPADDRESS2,JOBMAILSHIPADDRESSCITY,JOBMAILSHIPADDRESSSTATE
                              ,JOBMAILSHIPADDRESSZIP,ORGANIZATIONMACHINECODE  ,MANUFACTURERMAKE,MODEL,SN,CUMULATIVE_HOURS ,MACHINETASKHOURCHECK ,DIFFTOSERVICE
                              ,ESTSERVICENEEDEDBY,ESTSHIPDATE,LASTUPDATEDON ,LASTUPDATEDBY,RECVER,APPROVEDDATETIME,INSERTDATE,TASKCOMPLETEDHOURS,ORGID,ORGANIZATIONNAME,DATALOADEDFROMVIEW
                              ,ALTJOB,ALTJOBSITECONTACT) 
	                          select TaskId,MACHINEID,MACHINETASKTYPEID,TASKDESCRIPTION,TASKDATE,TASKCOMPLETE,NOTIFICATIONSENT,TASKCOMPLETEDATE
                              ,LASTUPDATEDATE,CUSTOMERPO,PRIORITY,SHIPDATE,SHIPNOTES,ALTERNATIVESHIPCONTACT,OVERRIDEJOBID,ALTERNATEMAILSHIPADDRESS1
                              ,ALTERNATEMAILSHIPADDRESS2 ,ALTERNATEMAILSHIPADDRESSSTATE,ALTERNATEMAILSHIPADDRESSZIP,ALTERNATEMAILSHIPADDRESSCITY,APPROVEDBY
                              ,CUSTOMERWO ,ALTERNATESHIPCONTACTNUMBER ,REQUESTDEALERTOSERVICE ,SHIPTRACKINGNO,SHIPTRACKINGSTATUS,UPSTRACKINGNUMBER ,REQUESTJRETOSERVICE
                              ,CURRENT_JOB ,JOBCONTACT,JOBCONTACTNUMBER,JOBMAILSHIPADDRESS1 ,JOBMAILSHIPADDRESS2,JOBMAILSHIPADDRESSCITY,JOBMAILSHIPADDRESSSTATE
                              ,JOBMAILSHIPADDRESSZIP,ORGANIZATIONMACHINECODE  ,MANUFACTURERMAKE,MODEL,SN,CUMULATIVE_HOURS ,MACHINETASKHOURCHECK ,DIFFTOSERVICE
                              ,ESTSERVICENEEDEDBY,ESTSHIPDATE,LASTUPDATEDON ,LASTUPDATEDBY,RECVER,APPROVEDDATETIME,GETUTCDATE(),TASKCOMPLETEDHOURS,ORGID,ORGANIZATIONNAME,DATALOADEDFROMVIEW
                              ,ALTJOB,ALTJOBSITECONTACT 
                              from MACHINETASKS where TaskId={0}";
            tran.ExecSQL(SQL_H, taskid);
        }


        private static void SaveByDealer(MachineTasksNotificationInfo[] tasks, string useriid)
        {
            const string SQL_D = @"update MACHINETASKS set REQUESTJRETOSERVICE={0},LASTUPDATEDBY={1},PRIORITY={2},CUSTOMERPO={3},CUSTOMERWO={4},ALTERNATEMAILSHIPADDRESS1={5},
                ALTERNATEMAILSHIPADDRESS2 ={6},ALTERNATEMAILSHIPADDRESSCITY ={7},ALTERNATEMAILSHIPADDRESSSTATE ={8},ALTERNATEMAILSHIPADDRESSZIP ={9},SHIPNOTES ={10}, LASTUPDATEDON = GETUTCDATE(), 
                RECVER = isnull(RECVER, 0) + 1,UPSTRACKINGNUMBER ={11},TASKCOMPLETE ={12},TASKCOMPLETEDHOURS ={13},JOBMAILSHIPADDRESS1 ={14},JOBMAILSHIPADDRESS2 ={15},
                JOBMAILSHIPADDRESSCITY ={16},JOBMAILSHIPADDRESSSTATE ={17},JOBMAILSHIPADDRESSZIP ={18},ORGANIZATIONMACHINECODE ={19},MANUFACTURERMAKE ={20},MODEL ={21},
                CUMULATIVE_HOURS ={22},MACHINETASKHOURCHECK ={23},DIFFTOSERVICE ={24},ESTSERVICENEEDEDBY ={25},ESTSHIPDATE ={26},JOBCONTACTNUMBER ={27},JOBCONTACT={28},ORGANIZATIONNAME={29},
                ALTJOB={31},ALTJOBSITECONTACT={32},APPROVEDBY={33} where TaskId = {30}";
            foreach (MachineTasksNotificationInfo ts in tasks)
            {
                try
                {
                    using (FISqlTransaction tran = new FISqlTransaction(IronIntelDbString))
                    {
                        SaveTaskHistory(tran, ts.TaskID);
                        tran.ExecSQL(SQL_D, ts.RequestJREToService, useriid, ts.Priority, ts.CustomerPO, ts.CustomerWO, ts.AlternateAddress1, ts.AlternateAddress2, ts.AlternateAddressCity, ts.AlternateAddressState,
                            ts.AlternateAddressZip, ts.ShipNotes, ts.UPSTrackingNumber, ts.TaskComplete, ts.TaskCompletedHours, ts.JobAddress1, ts.JobAddress2, ts.JobAddressCity, ts.JobAddressState, ts.JobAddressZip,
                            ts.OrgMachineCode, ts.ManufactuerMake, ts.Model, ts.CumulativeHours, ts.MachineTaskHourCheck, ts.DiffToService, ts.ESTServiceNeededBy, ts.ESTShopDate, ts.JobContactNumber, ts.JobContact,
                            ts.OrganizationName, ts.TaskID, ts.AltJob, ts.AltJobSiteContact, ts.Approved ? useriid : Guid.Empty.ToString());
                        tran.Commit();
                    }
                }
                catch (Exception ex)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine(ts.ToString());
                    sb.AppendLine();
                    sb.AppendLine(ex.ToString());
                    SystemParams.WriteLog("Error", CLASSNAME + ".SaveByDealer", ex.Message, sb.ToString());
                    throw;
                }
            }

        }
        private static void SaveByContractor(MachineTasksNotificationInfo[] tasks, string useriid)
        {
            const string SQL_C1 = @"update MACHINETASKS set REQUESTJRETOSERVICE ={0},LASTUPDATEDBY ={1},PRIORITY ={2},CUSTOMERPO={3},CUSTOMERWO={4},ALTERNATEMAILSHIPADDRESS1={5},
                ALTERNATEMAILSHIPADDRESS2={6},ALTERNATEMAILSHIPADDRESSCITY={7},ALTERNATEMAILSHIPADDRESSSTATE ={8}, ALTERNATEMAILSHIPADDRESSZIP ={9},SHIPNOTES ={10},LASTUPDATEDON=GETUTCDATE(),
                RECVER=isnull(RECVER,0)+1,APPROVEDBY=NULL,APPROVEDDATETIME=NULL,ALTJOB={12},ALTJOBSITECONTACT={13},TASKCOMPLETEDHOURS={14} where TaskId={11}";
            const string SQL_C2 = @"update MACHINETASKS set REQUESTJRETOSERVICE={0},LASTUPDATEDBY={1},PRIORITY={2},CUSTOMERPO={3},CUSTOMERWO={4},ALTERNATEMAILSHIPADDRESS1={5},ALTERNATEMAILSHIPADDRESS2={6},
                ALTERNATEMAILSHIPADDRESSCITY={7},ALTERNATEMAILSHIPADDRESSSTATE={8},ALTERNATEMAILSHIPADDRESSZIP={9},SHIPNOTES={10}, LASTUPDATEDON=GETUTCDATE(), RECVER=isnull(RECVER,0)+1,APPROVEDBY={1},
                APPROVEDDATETIME=GETUTCDATE() ,ALTJOB={12},ALTJOBSITECONTACT={13},TASKCOMPLETEDHOURS={14} where TaskId={11}";
            const string SQL_C3 = @"update MACHINETASKS set REQUESTJRETOSERVICE={0},LASTUPDATEDBY={1},PRIORITY={2},CUSTOMERPO={3},CUSTOMERWO={4},ALTERNATEMAILSHIPADDRESS1={5},
                ALTERNATEMAILSHIPADDRESS2={6},ALTERNATEMAILSHIPADDRESSCITY={7},ALTERNATEMAILSHIPADDRESSSTATE={8},ALTERNATEMAILSHIPADDRESSZIP={9},SHIPNOTES={10}, LASTUPDATEDON=GETUTCDATE(), 
                RECVER=isnull(RECVER,0)+1,ALTJOB={12},ALTJOBSITECONTACT={13},TASKCOMPLETEDHOURS={14} where TaskId={11}";
            const string SQL_sel = "select APPROVEDBY from MACHINETASKS where TaskId={0}";

            FISqlConnection db = new FISqlConnection(IronIntelDbString);
            foreach (MachineTasksNotificationInfo ts in tasks)
            {
                try
                {
                    string approvedby = FIDbAccess.GetFieldString(db.GetRC1BySQL(SQL_sel, ts.TaskID), string.Empty);
                    using (FISqlTransaction tran = new FISqlTransaction(IronIntelDbString))
                    {
                        SaveTaskHistory(tran, ts.TaskID);
                        if (ts.Approved)
                        {
                            if (string.IsNullOrWhiteSpace(approvedby))
                            {
                                tran.ExecSQL(SQL_C2, ts.RequestJREToService, useriid, ts.Priority, ts.CustomerPO, ts.CustomerWO, ts.AlternateAddress1, ts.AlternateAddress2, ts.AlternateAddressCity, ts.AlternateAddressState,
                                    ts.AlternateAddressZip, ts.ShipNotes, ts.TaskID, ts.AltJob, ts.AltJobSiteContact, ts.TaskCompletedHours);
                            }
                            else
                            {
                                tran.ExecSQL(SQL_C3, ts.RequestJREToService, useriid, ts.Priority, ts.CustomerPO, ts.CustomerWO, ts.AlternateAddress1, ts.AlternateAddress2, ts.AlternateAddressCity, ts.AlternateAddressState,
                                    ts.AlternateAddressZip, ts.ShipNotes, ts.TaskID, ts.AltJob, ts.AltJobSiteContact, ts.TaskCompletedHours);
                            }
                        }
                        else
                        {
                            tran.ExecSQL(SQL_C1, ts.RequestJREToService, useriid, ts.Priority, ts.CustomerPO, ts.CustomerWO, ts.AlternateAddress1, ts.AlternateAddress2, ts.AlternateAddressCity, ts.AlternateAddressState,
                                ts.AlternateAddressZip, ts.ShipNotes, ts.TaskID, ts.AltJob, ts.AltJobSiteContact, ts.TaskCompletedHours);
                        }
                        tran.Commit();
                    }
                }
                catch (Exception ex)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine(ts.ToString());
                    sb.AppendLine();
                    sb.AppendLine(ex.ToString());
                    SystemParams.WriteLog("Error", CLASSNAME + ".SaveByContractor", ex.Message, sb.ToString());
                    throw;
                }
            }
        }

    }
}
