//This Function inserts comments to the FSTINVC Table
        private void InsertComments(iDB2Command db2Command, FSTINV inv)
        {
            st.insertLog(string.Format("Inserting notes for {0} to FSTINVC table.", inv.SIID), "Info", inv.SIID.ToString(), HillerServiceDataMigrator.LogId);

            string tableName = "FSTINVC";

            using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
            {
                db2Command.Transaction = db2Transaction;

                db2Command.CommandText = string.Format("INSERT INTO {0} (SCID, SCCMTS, SCCRDT) VALUES(@p1, @p2, @p3) WITH NONE",
                                                       tableName);
                db2Command.CommandText = db2Command.CommandText.Replace("@p1", string.Format("'{0}'", inv.SIID));
                db2Command.CommandText = db2Command.CommandText.Replace("@p2", string.Format("'{0}'", inv.Notes.Replace("'", "''")));
                db2Command.CommandText = db2Command.CommandText.Replace("@p3", string.Format("'{0}'", new iDB2TimeStamp(inv.CreatedDateTime.ToString("MM/dd/yyyy h:mm:ss.ffffff")).ToNativeFormat()));

                //  st.insertLog(db2Command.CommandText, "FSTINVC: Insert Query", "NA",LogId);

                var recordsAffected = db2Command.ExecuteNonQuery();

                db2Command.Transaction.Commit();

                st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId);
            }
        }
        private List <string> GetSalesNumberByLocation(FSTINV inv)
        {
            List <string> retVal = new List <string>();
            //Set cookie
            string c_settings = "PHPSESSID=" + authorization.AuthToken;

            //Create request and send to ServiceTrade
            var client = new RestClient();

            client.EndPoint      = @uriStr + "tag";
            client.Method        = HttpVerb.GET;
            client.ContentType   = "application/json";
            client.SessionCookie = c_settings;

            string param = string.Format("?entityId={0}&entityType=11", inv.ServicingOffice.OfficeId);

            using (HttpWebResponse response = client.MakeRequest(param))
            {
                JObject json = JObject.Parse(new StreamReader(response.GetResponseStream()).ReadToEnd());
                var     data = json["data"]["tags"].Value <JArray>().ToList <JToken>();

                var tags = new List <string>(from tag in data select tag["name"].Value <string>());

                if (tags.Count > 0)
                {
                    retVal = (from val in tags where val.Contains("SALESMAN") select val).ToList();
                }
            }

            return(retVal);
        }
        //10.12.16 JC: Determine if a given invoice item is a recurring service
        //This function returns true if there are more than one service requests
        public bool IsRecurringService(FSTINV _invoice, Item _invItem)
        {
            FSTINV invoice = _invoice;
            List <ServiceRequest> _serviceRequests;

            if (_invItem.ServiceLine == null)
            {
                //10.12.16 JC: Re-pull the invoice
                st.insertLog("ServiceLine is empty for " + invoice.SIID + ". Re-pull invoice", "Error", _invoice.SIID.ToString(), HillerServiceDataMigrator.LogId);
                invoice = this.GetInvoice(invoice.SIID);

                if (invoice.Items.Where(itm => itm.SIITEM == _invItem.SIITEM).First().ServiceLine == null)
                {
                    //10.12.16 JC: If we still Don't have a service line, log the error
                    st.insertLog(string.Format("Invoice {0} has item {1} that do not have a service line", invoice.SIID, _invItem.LibItem.Name), "Error", invoice.SIID.ToString(), HillerServiceDataMigrator.LogId);
                    return(false);
                }
            }

            //10.12.16 JC: Get a list of service requests for the invoice location in the last 18 months and for the given item service line id
            _serviceRequests = this.GetServiceRequests(invoice.Location.LocationId, invoice.Items.Where(itm => itm.SIITEM == _invItem.SIITEM).First().ServiceLine.Id);

            //10.12.16 JC: Remove service requests that has no service recurrence and has a status of void or canceled.
            _serviceRequests = _serviceRequests.Where(x => x.ServiceRecurrence != null && !(string.Equals(x.Status.ToLowerInvariant(), "void", StringComparison.InvariantCulture) || string.Equals(x.Status.ToLowerInvariant(), "canceled", StringComparison.InvariantCulture))).ToList();

            return(_serviceRequests.Count > 0);
        }
        public bool SetInvoiceToProcessed(FSTINV invoice)
        {
            if (invoice.Status.CompareTo("processed") != 0)
            {
                return(false);
            }

            bool success = false;

            //Set cookie
            string c_settings = "PHPSESSID=" + authorization.AuthToken;

            //Create request and send to ServiceTrade
            var client = new RestClient();

            client.EndPoint      = @uriStr + string.Format("invoice/{0}", invoice.SIID);
            client.Method        = HttpVerb.POST;
            client.ContentType   = "application/json";
            client.SessionCookie = c_settings;

            // st.insertLog("Contact Endpoint" + client.EndPoint, "Info", "NA", HillerServiceDataMigrator.LogId);

            client.PostData = string.Format(@"{{""status"":""{0}""}}", invoice.Status);

            using (var response = client.MakeRequest())
            {
                if (response != null && response.StatusCode == HttpStatusCode.OK)
                {
                    success = true;
                    response.Close();
                }
            }

            return(success);
        }
        //Get comments for the Invoices
        public CommentsList GetInvoiceComments(FSTINV inv)
        {
            st.insertLog("Get invoice public comments.", "Info", inv.SIID.ToString(), HillerServiceDataMigrator.LogId);

            CommentsList retVal = new CommentsList();

            //Set cookie
            string c_settings = "PHPSESSID=" + authorization.AuthToken;

            //Create request and send to ServiceTrade
            var client = new RestClient();

            client.EndPoint      = @uriStr + "comment";
            client.Method        = HttpVerb.GET;
            client.ContentType   = "application/json";
            client.SessionCookie = c_settings;

            string param = string.Format("?entityId={0}&entityType=6&visibility=public", inv.SIID);

            using (var response = client.MakeRequest(param))
            {
                if (response != null && response.StatusCode == HttpStatusCode.OK)
                {
                    try
                    {
                        DataContractJsonSerializer jsonSerializer = new DataContractJsonSerializer(typeof(CommentDataContainer));
                        object objResponse        = jsonSerializer.ReadObject(response.GetResponseStream());
                        CommentDataContainer data = objResponse as CommentDataContainer;
                        retVal = data.CommentsList;
                    }
                    catch (Exception ex)
                    {
                        st.insertLog(ex.Message, "Exception Error", inv.SIID.ToString(), HillerServiceDataMigrator.LogId);
                    }
                    finally
                    {
                        response.Close();
                    }
                }
            }

            return(retVal);
        }
        //Get single invoice using Service Traide Invoice ID
        public FSTINV GetInvoice(string _invoiceId)
        {
            FSTINV invoice = new FSTINV();;

            //Set cookie
            string c_settings = "PHPSESSID=" + authorization.AuthToken;

            //Create request and send to ServiceTrade
            var client = new RestClient();

            client.EndPoint      = @uriStr + "invoice/";
            client.Method        = HttpVerb.GET;
            client.ContentType   = "application/json";
            client.SessionCookie = c_settings;

            using (var response = client.MakeRequest(_invoiceId))
            {
                if (response != null && response.StatusCode == HttpStatusCode.OK)
                {
                    try
                    {
                        DataContractJsonSerializer jsonSerializer = new DataContractJsonSerializer(typeof(InvoiceDataContract));
                        object objResponse = jsonSerializer.ReadObject(response.GetResponseStream());
                        InvoiceDataContract jsonResponse = objResponse as InvoiceDataContract;
                        invoice = jsonResponse.IFSTINV;

                        //var result = JsonConvert.DeserializeObject(response.ToString());
                    }
                    catch (Exception ex)
                    {
                        st.insertLog(ex.Message, "Exception Error", _invoiceId.ToString(), HillerServiceDataMigrator.LogId);
                    }
                    finally
                    {
                        response.Close();
                    }
                }
            }

            return(invoice);
        }
        //This function inserts the invoice into the FSTINV Table
        private void InsertInvoiceToJDE(iDB2Command db2Command, FSTINV inv)
        {
            st.insertLog(string.Format("Inserting {0} to FSTINV table.", inv.Name), "Info", inv.SIID.ToString(), LogId);

            string tableName = inv.GetType().Name;

            if (inv.Items.Length > 0)
            {
                for (int x = 0; x < inv.Items.Length; x++)
                {
                    //10.12.16 JC: Check whether this is a recurring service (parameter send invoice and a invoice item)
                    // inv.Items[x].IsRecurringService = serviceTrade.IsRecurringService(inv, inv.Items[x]);

                    // st.insertLog(string.Format("New vs Recurring Check result: {0}, {1}, {2}", inv.SIID, inv.Items[x].LibItem.Name, inv.Items[x].IsRecurringService), "Info", "NA", LogId);

                    using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
                    {
                        db2Command.Transaction = db2Transaction;

                        db2Command.CommandText = string.Format("INSERT INTO {0} (SIID, SIAN8, SILAN8, SISSTS, SIINV, SITECH, SILOC, SITYPE, SITOTL, SIPO, SIITEM, SIITYP, SIDESC, SIQTY, SIPRIC, SICRDT, SIJOB, SISLSM, SISLS2, SISLS3, SIDFLG, SIUSER1) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22) WITH NONE",
                                                               tableName);

                        //Recurring service
                        // db2Command.CommandText = db2Command.CommandText.Replace("@p22", string.Format("'{0}'", inv.Items[x].IsRecurringService ? "Yes" : "No"));
                        db2Command.CommandText = db2Command.CommandText.Replace("@p22", string.Format("'{0}'", "NA"));

                        //Flag to Determine Service Type
                        db2Command.CommandText = db2Command.CommandText.Replace("@p21", string.Format("'{0}'", inv.FPMARActive ? "FPMAR" : ""));
                        //Salesman 3
                        db2Command.CommandText = db2Command.CommandText.Replace("@p20", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 1) ? inv.Technician[1].SITECH : null));
                        //Salesman 2
                        db2Command.CommandText = db2Command.CommandText.Replace("@p19", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 1) ? inv.Tags[1] : null));
                        //Salesman 1
                        db2Command.CommandText = db2Command.CommandText.Replace("@p18", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 0) ? inv.Tags[0] : null));
                        //Service Trade Job ID
                        db2Command.CommandText = db2Command.CommandText.Replace("@p17", string.Format("'{0}'", inv.Job.JobId));
                        //Created Date
                        db2Command.CommandText = db2Command.CommandText.Replace("@p16", string.Format("'{0}'", new iDB2TimeStamp(inv.CreatedDateTime.ToString("MM/dd/yyyy h:mm:ss.ffffff")).ToNativeFormat()));
                        //Item Price
                        db2Command.CommandText = db2Command.CommandText.Replace("@p15", string.Format("'{0}'", inv.Items[x].Price));
                        //Item Quanity
                        db2Command.CommandText = db2Command.CommandText.Replace("@p14", string.Format("'{0}'", Math.Round(inv.Items[x].Quantity, 2))); //6.23.16 JC - Hiller wants to limit the item qty to two decimal places.
                        //Service Trade Item Description
                        db2Command.CommandText = db2Command.CommandText.Replace("@p13", string.Format("'{0}'", inv.Items[x].Description.Length <= 50 ? (inv.Items[x].Description).Replace("'", "''") : (inv.Items[x].Description.Substring(0, 50)).Replace("'", "''")));
                        //Service Trade Item Type
                        db2Command.CommandText = db2Command.CommandText.Replace("@p12", string.Format("'{0}'", inv.Items[x].LibItem.Type));
                        //ITEM ID
                        db2Command.CommandText = db2Command.CommandText.Replace("@p11", string.Format("'{0}'", inv.Items[x].LibItem.Code));
                        //Customer PO
                        db2Command.CommandText = db2Command.CommandText.Replace("@p10", string.Format("'{0}'", string.IsNullOrEmpty(inv.CustomerPo) ? string.Empty : inv.CustomerPo.Replace("'", string.Empty)));
                        //Service Trade ID
                        db2Command.CommandText = db2Command.CommandText.Replace("@p1", string.Format("'{0}'", inv.SIID));
                        //JDE Address Book number for the Company
                        db2Command.CommandText = db2Command.CommandText.Replace("@p2", string.Format("'{0}'", inv.Customer.External.JdeId));
                        //JDE Address Book number for the Location
                        db2Command.CommandText = db2Command.CommandText.Replace("@p3", string.Format("'{0}'", inv.Location.External.JdeId));
                        //Service Trade invoice Status
                        db2Command.CommandText = db2Command.CommandText.Replace("@p4", string.Format("'{0}'", inv.Status));
                        //Service Trade Invoice number
                        db2Command.CommandText = db2Command.CommandText.Replace("@p5", string.Format("'{0}'", inv.InvoiceNumber));
                        //Service Trade Technician
                        db2Command.CommandText = db2Command.CommandText.Replace("@p6", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 0) ? inv.Technician[0].SITECH : null));
                        //Service Trade Location (e.g. FT. Walton 123, Pensacola 122)
                        db2Command.CommandText = db2Command.CommandText.Replace("@p7", string.Format("'{0}'", inv.ServicingOffice != null ? inv.ServicingOffice.OfficeId : null));
                        //Service Trade Invoice Type
                        db2Command.CommandText = db2Command.CommandText.Replace("@p8", string.Format("'{0}'", inv.Type));
                        //Inovice Total Amount
                        db2Command.CommandText = db2Command.CommandText.Replace("@p9", string.Format("'{0}'", inv.TotalPrice));

                        //  st.insertLog(db2Command.CommandText, "FSTINV: Insert Query multiple items", "NA", LogId);
                        var recordsAffected = db2Command.ExecuteNonQuery();

                        db2Command.Transaction.Commit();

                        st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId);
                    }
                }
            }
            else
            {
                using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
                {
                    db2Command.Transaction = db2Transaction;

                    db2Command.CommandText = string.Format("INSERT INTO {0} (SIID, SIAN8, SISSTS, SIINV, SITECH, SILOC, SITOTL, SIPO, SICRDT, SIJOB, SISLSM, SISLS2, SISLS3, SIDFLG) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14) WITH NONE",
                                                           tableName);

                    db2Command.CommandText = db2Command.CommandText.Replace("@p14", string.Format("'{0}'", inv.FPMARActive ? "FPMAR" : ""));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p13", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 1) ? inv.Technician[1].SITECH : null));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p12", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 1) ? inv.Tags[1] : null));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p11", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 0) ? inv.Tags[0] : null));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p10", string.Format("'{0}'", inv.Job.JobId));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p1", string.Format("'{0}'", inv.SIID));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p2", string.Format("'{0}'", inv.Customer.CompanyId));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p3", string.Format("'{0}'", inv.Status));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p4", string.Format("'{0}'", inv.InvoiceNumber));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p5", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 0) ? inv.Technician[0].SITECH : null));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p6", string.Format("'{0}'", inv.ServicingOffice != null ? inv.ServicingOffice.OfficeId : null));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p7", string.Format("'{0}'", inv.TotalPrice));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p8", string.Format("'{0}'", string.IsNullOrEmpty(inv.CustomerPo) ? string.Empty : inv.CustomerPo.Replace("'", string.Empty)));
                    db2Command.CommandText = db2Command.CommandText.Replace("@p9", string.Format("'{0}'", new iDB2TimeStamp(inv.CreatedDateTime.ToString("MM/dd/yyyy h:mm:ss.ffffff")).ToNativeFormat()));



                    // st.insertLog(db2Command.CommandText, "FSTINV: Insert Query no items", "NA", LogId);
                    var recordsAffected = db2Command.ExecuteNonQuery();

                    db2Command.Transaction.Commit();

                    st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId);
                }
            }

            //Onces inserted, change the status to processed for updating ServiceTrade
            inv.Status = "processed";
        }
        //Get Sales Number
        public List <string> GetSalesNumber(FSTINV inv)
        {
            st.insertLog("Get invoice sales number tag.", "Info", inv.SIID.ToString(), HillerServiceDataMigrator.LogId);

            List <string> retVal = new List <string>();
            //Set cookie
            string c_settings = "PHPSESSID=" + authorization.AuthToken;

            //Create request and send to ServiceTrade
            var client = new RestClient();

            client.EndPoint      = @uriStr + "tag";
            client.Method        = HttpVerb.GET;
            client.ContentType   = "application/json";
            client.SessionCookie = c_settings;

            //Looks for the SALESMAN Tag using the Job entity first.
            string param = string.Format("?entityId={0}&entityType=3", inv.Job.JobId);

            using (HttpWebResponse response = client.MakeRequest(param))
            {
                JObject json = JObject.Parse(new StreamReader(response.GetResponseStream()).ReadToEnd());
                var     data = json["data"]["tags"].Value <JArray>().ToList <JToken>();

                if (data.Count > 0)
                {
                    var tags = new List <string>(from tag in data select tag["name"].Value <string>());

                    if (tags.Count > 0)
                    {
                        retVal = (from val in tags where val.ToLower().Contains("salesman") select val).ToList();
                    }
                }
                else
                {
                    //Change request: If SALESMAN tag doesn't exists in the JOB entity, look up using Servicing Office Location
                    retVal = GetSalesNumberByLocation(inv);
                }
            }

            //Use defaults if no SALESMAN tag exists
            if (retVal.Count == 0)
            {
                switch (inv.ServicingOffice.OfficeId)
                {
                case 138619:
                    retVal.Add(Constants.SALESMAN984);
                    break;

                case 138620:
                    retVal.Add(Constants.SALESMAN209);
                    break;

                default:
                    break;
                }
            }


            return(retVal);
        }