//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);
}