public int CreateProcesoEjecucion(DBProcesoEjecucion detalle)
{
string sql = "[dbo].[SP_HS_INSERT_PROCESS_DETAIL]";
SqlCommand command = new SqlCommand(sql, cnn);
SqlDataReader rdr = null;
try
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@procesoId", SqlDbType.Int).Value = detalle.ProcesoId;
command.Parameters.Add("@estatus", SqlDbType.Int).Value = detalle.Estatus;
command.Parameters.Add("@resultado", SqlDbType.VarChar).Value = detalle.Resultado;
var returnParameter = command.Parameters.Add("@ProcesoDetalleId", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
detalle.ProcesoEjecucionId = Convert.ToInt32(returnParameter.Value);
command.Dispose();
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
return(detalle.ProcesoEjecucionId);
}
public void ActualizarProcesoEjecucion(DBProcesoEjecucion detalle)
{
string sql = "UPDATE dbo.ProcesoEjecucion_Herramental SET Estatus = @Estatus, FechaFin = @FechaFin, Resultado = @Resultado WHERE ProcesoEjecucionHerramentalId = @id";
SqlCommand cmd = new SqlCommand(sql, cnn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = detalle.ProcesoEjecucionId;
cmd.Parameters.Add("@Estatus", SqlDbType.Bit).Value = detalle.Estatus;
cmd.Parameters.Add("@FechaFin", SqlDbType.DateTime2).Value = detalle.FechaFin;
cmd.Parameters.Add("@Resultado", SqlDbType.VarChar).Value = detalle.Resultado;
cmd.ExecuteNonQuery();
}
public static void SyncTickets()
{
bool ProcessEnabled = true;
Boolean.TryParse(System.Configuration.ConfigurationManager.AppSettings["Jobs.EnabledJobs"], out ProcessEnabled);
int definitionId = 0;
if (!Int32.TryParse(System.Configuration.ConfigurationManager.AppSettings["DefinitionId"], out definitionId))
{
throw new Exception("DefinitionId no existe en el web config");
}
string pipelineId = System.Configuration.ConfigurationManager.AppSettings["PipelineId"];
string pipelineStageId = System.Configuration.ConfigurationManager.AppSettings["PipelineStageId"];
IHubspotService apiService = new HubspotService();
Trace.TraceInformation(string.Format("[TicketsSyncJob.SyncTickets] Executing at {0}", DateTime.Now));
IDBService dbService = new DBService();
DBProceso procesoInfo = dbService.GetProcessInfo("SINCRONIZACION_TICKETS");
StringBuilder strResultado = new StringBuilder("Iniciando proceso...");
if (Monitor.TryEnter(thisLock))
{
try
{
if (!executing && ProcessEnabled)
{
executing = true;
//SI ESTA HABILITADO Y NO SE ESTA EJECUTANDO
if (!procesoInfo.EstatusEjecucion && procesoInfo.EstatusProceso)
{
procesoInfo.EstatusEjecucion = true;
procesoInfo.UltimaEjecucion = DateTime.Now;
procesoInfo.Resultado = strResultado.ToString();
dbService.ActualizarEstatusProceso(procesoInfo);
DBProcesoEjecucion procesoDetalle = new DBProcesoEjecucion()
{
ProcesoId = procesoInfo.ProcesoId,
Estatus = true,
Resultado = "Procesando..."
};
int ProcesoDetalleId = dbService.CreateProcesoEjecucion(procesoDetalle);
#region SINCRONIZACION DE COMPANIAS EN MEMORIA
Dictionary <string, long> CompanyDictionary = new Dictionary <string, long>();
long offset = 0;
bool hasMoreCompanies = true;
int totalCompanies = 0;
strResultado.Append(" * Paso 1 ");
while (hasMoreCompanies)
{
strResultado.Append(" * Paso 1.1 ");
CompaniesHubSpotResult companiesHubSpotResult = apiService.GetAllCompanies(250, offset);
Trace.TraceInformation(string.Format("HasMore: {0} Offset: {1}", companiesHubSpotResult.HasMore, companiesHubSpotResult.Offset));
hasMoreCompanies = companiesHubSpotResult.HasMore;
offset = companiesHubSpotResult.Offset;
strResultado.Append(" * Paso 1.2 ");
totalCompanies += companiesHubSpotResult.Companies.Count();
strResultado.Append(" * Paso 1.3 ");
foreach (Company company in companiesHubSpotResult.Companies)
{
//TODO: Cambiar por RFC
if (company.Properties.RFC != null && !string.IsNullOrEmpty(company.Properties.RFC.Value))
{
string rfcCompany = company.Properties.RFC.Value.Trim().ToUpper();
if (!CompanyDictionary.ContainsKey(rfcCompany))
{
CompanyDictionary.Add(rfcCompany, company.CompanyId);
}
}
}
}
Trace.TraceInformation(string.Format("Total Companies: {0}", totalCompanies));
Trace.TraceInformation(string.Format("Total Companies in Dic: {0}", CompanyDictionary.Count()));
#endregion
strResultado.Append(" * Paso 2 ");
dbService.UpdateTicketsToProcess();
strResultado.Append(" * Paso 3 ");
IEnumerable <DBTicketModel> tickets = dbService.GetTickets();
strResultado.Append(" * Paso 4 ");
int ticketsSyncronized = 0;
foreach (DBTicketModel ticket in tickets)
{
long companyId = 0;
strResultado.Append(" * Paso 4.1 ");
string rfc = ticket.RFC.Trim().ToUpper();
if (!CompanyDictionary.TryGetValue(rfc, out companyId))
{
strResultado.Append(String.Format("No existe el rfc {0} en el hubspot", rfc));
continue;
}
strResultado.Append(" * Paso 4.2 ");
CompanyTicketHubspotSave ticketHubspotSave = new CompanyTicketHubspotSave
{
CompanyId = companyId,
Content = ticket.Descripcion,
Monto = ticket.Monto,
NumeroOperacion = ticket.NumeroOperacion,
Subject = ticket.TipoActividad,
DefinitionId = definitionId,
PipelineId = pipelineId,
PipelineStageId = pipelineStageId
};
strResultado.Append(" * Paso 4.3 ");
CompanyTicketCreateHubspotResponse apiResponse = apiService.CreateTicketToCompany(ticketHubspotSave);
strResultado.Append(" * Paso 4.4 ");
if (apiResponse.IsCreated)
{
strResultado.Append(" * Paso 4.5 ");
ticket.TicketId = apiResponse.TicketId;
dbService.UpdateSyncTicket(ticket);
ticketsSyncronized++;
}
}
Trace.TraceInformation(string.Format("[TicketsSyncJob.SyncTickets] Finishing at {0}", DateTime.Now));
int total = tickets.Count();
procesoDetalle.FechaFin = DateTime.Now;
procesoDetalle.Estatus = false;
procesoDetalle.Resultado = string.Format("Se sincronizaron {0} de {1} tickets", ticketsSyncronized, total);
strResultado.Append(string.Format("|Se sincronizaron {0} de {1} tickets", ticketsSyncronized, total));
dbService.ActualizarProcesoEjecucion(procesoDetalle);
}
}
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Trace.TraceInformation(exception.Message);
strResultado.Append("|" + exception.Message);
if (exception.Source != null)
{
strResultado.Append("|" + exception.Source);
}
if (exception.StackTrace != null)
{
strResultado.Append("|" + exception.StackTrace);
}
try
{
System.Diagnostics.StackTrace trace = new System.Diagnostics.StackTrace(exception, true);
strResultado.Append("|" + String.Format("<p>Error Detail Message :{0} => Error In :{1} => Line Number :{2} => Error Method:{3}</p>",
HttpUtility.HtmlEncode(exception.Message),
trace.GetFrame(0).GetFileName(),
trace.GetFrame(0).GetFileLineNumber(),
trace.GetFrame(0).GetMethod().Name));
}
catch (Exception ex) { }
}
finally
{
procesoInfo.EstatusEjecucion = false;
procesoInfo.UltimaEjecucion = DateTime.Now;
procesoInfo.Resultado = strResultado.ToString();
dbService.ActualizarEstatusProceso(procesoInfo);
executing = false;
Monitor.Exit(thisLock);
}
}
Trace.TraceInformation(string.Format("[TicketsSyncJob.SyncTickets] Finishing at {0}", DateTime.Now));
}
public static void SyncDeals()
{
bool ProcessEnabled = true;
Boolean.TryParse(System.Configuration.ConfigurationManager.AppSettings["Jobs.EnabledJobs"], out ProcessEnabled);
string FiltroDeal = System.Configuration.ConfigurationManager.AppSettings["Jobs.SincronizarDeals.Filtro"].ToString();
string FiltroDealPipeline = System.Configuration.ConfigurationManager.AppSettings["Jobs.SincronizarDeals.FiltroPipeline"].ToString();
IDBService dbService = new DBService();
DBProceso procesoInfo = dbService.GetProcessInfo("SINCRONIZACION_DEALS");
StringBuilder strResultado = new StringBuilder("Iniciando proceso...");
if (Monitor.TryEnter(thisLock))
{
try
{
if (!executing && ProcessEnabled)
{
executing = true;
int syncedDeals = 0;
IHubspotService apiService = new HubspotService();
Trace.TraceInformation(string.Format("[DealsSyncJob.SyncDeals] Get owners {0}", DateTime.Now));
IDictionary <int, string> ownersEmails = apiService.GetDisctionaryEmailsOwner();
Trace.TraceInformation(string.Format("[DealsSyncJob.SyncDeals] Executing at {0}", DateTime.Now));
//SI ESTA HABILITADO Y NO SE ESTA EJECUTANDO
if (!procesoInfo.EstatusEjecucion && procesoInfo.EstatusProceso)
{
procesoInfo.EstatusEjecucion = true;
procesoInfo.UltimaEjecucion = DateTime.Now;
procesoInfo.Resultado = strResultado.ToString();
dbService.ActualizarEstatusProceso(procesoInfo);
DBProcesoEjecucion procesoDetalle = new DBProcesoEjecucion()
{
ProcesoId = procesoInfo.ProcesoId,
Estatus = true,
Resultado = "Procesando..."
};
int ProcesoDetalleId = dbService.CreateProcesoEjecucion(procesoDetalle);
//LIMPIA LA TABLA
dbService.ClearDeals();
//INICIA SINCRONIZACION
long offset = 0;
bool hasMoreDeals = true;
IDictionary <string, IDictionary <string, PipelineState> > pipelineStages = apiService.GetDealsPipelinesStages();
while (hasMoreDeals)
{
var dealsObj = apiService.ReadDeals(250, offset);
Trace.TraceInformation(string.Format("HasMore: {0} Offset: {1}", dealsObj.HasMore, dealsObj.Offset));
hasMoreDeals = dealsObj.HasMore;
offset = dealsObj.Offset;
foreach (Deal deal in dealsObj.Deals)
{
strResultado.Append(" | " + JsonUtil.ConvertToString(deal));
var associations = deal.Associations;
long? contactId = null;
string CompanyRFC = string.Empty;
string CompanyName = string.Empty;
long? companyId = null;
decimal amount = 0;
string ContactName = string.Empty;
string DealStage = deal.Properties.DealStage.Value;
string linea = string.Empty;
string factor = string.Empty;
string Owner = string.Empty;
string statusMessage = "SUCCESS";
bool hasError = false;
StringBuilder errorMessage = new StringBuilder();
if (!FiltroDeal.Contains(DealStage))
{
strResultado.Append(" * Paso 1 ");
if (associations.AssociatedVids != null && associations.AssociatedVids.Any())
{
strResultado.Append(" * Paso 1.1 ");
contactId = associations.AssociatedVids.First();
strResultado.Append(" * Paso 1.2 ");
if (contactId.HasValue)
{
try
{
ContactHubSpotResult contactObj = apiService.GetContactById(contactId.Value);
strResultado.Append(" * Paso 1.3 ");
if (contactObj != null && contactObj.Properties.Email != null && !string.IsNullOrEmpty(contactObj.Properties.Email.Value))
{
ContactName = contactObj.Properties.Email.Value;
}
}catch (Exception e)
{
errorMessage.Append("Error al realizar la consulta de la api de contactos");
errorMessage.Append(Environment.NewLine);
ExceptionUtil.AppendMessage(e, errorMessage);
hasError = true;
}
}
}
strResultado.Append(" * Paso 2 ");
if (associations.associatedCompanyIds != null && associations.associatedCompanyIds.Any())
{
companyId = associations.associatedCompanyIds.First();
try
{
CompanyHubSpotResult companyObj = apiService.GetCompanyById(companyId.Value);
CompanyName = companyObj.Properties.Name != null?string.Format("{0}", companyObj.Properties.Name.Value) : string.Empty;
strResultado.Append(" * Paso 2.1 ");
if (/*companyObj!=null && companyObj.Properties!=null &&*/ companyObj.Properties.RFC != null && !string.IsNullOrEmpty(companyObj.Properties.RFC.Value))
{
CompanyRFC = companyObj.Properties.RFC.Value;
}
}catch (Exception e)
{
errorMessage.Append("Error al realizar la consulta de la api de compania");
errorMessage.Append(Environment.NewLine);
ExceptionUtil.AppendMessage(e, errorMessage);
hasError = true;
}
}
if (hasError)
{
statusMessage = errorMessage.ToString();
}
strResultado.Append(" * Paso 3 ");
if (deal.Properties.Amount != null && !string.IsNullOrEmpty(deal.Properties.Amount.Value))
{
amount = Convert.ToDecimal(deal.Properties.Amount.Value);
}
strResultado.Append(" * Paso 4 ");
if (deal.Properties.LineaDeNegocio != null && !string.IsNullOrEmpty(deal.Properties.LineaDeNegocio.Value))
{
linea = deal.Properties.LineaDeNegocio.Value;
}
if (deal.Properties.Factor != null && !string.IsNullOrEmpty(deal.Properties.Factor.Value))
{
factor = deal.Properties.Factor.Value;
}
strResultado.Append(" * Paso 5 ");
if (deal.Properties.HubspotOwnerId != null && !string.IsNullOrEmpty(deal.Properties.HubspotOwnerId.SourceId))
{
int ownerId = 0;
if (Int32.TryParse(deal.Properties.HubspotOwnerId.Value, out ownerId) && ownersEmails != null)
{
ownersEmails.TryGetValue(ownerId, out Owner);
}
}
strResultado.Append(" * Paso 6 ");
//INSERCION A BD
DBDealModel dealBD = new DBDealModel()
{
ProcesoDetalleId = ProcesoDetalleId,
DealId = deal.DealId,
DealName = deal.Properties.Dealname.Value,
CompanyRFC = CompanyRFC,
Stage = DealStage,
Amount = amount,
CompanyName = CompanyName,
ContactName = ContactName,
ProductLine = linea,
OwnerName = Owner,
Factor = factor,
StatusMessage = statusMessage
};
long closeDateUnixTimeStamp = 0;
if (deal.Properties.CloseDate != null && Int64.TryParse(deal.Properties.CloseDate.Value, out closeDateUnixTimeStamp))
{
dealBD.CloseDate = DateUtil.ConvertMilisecondsToDateTime(closeDateUnixTimeStamp);
}
if (deal.Properties.NumFacturaEpicor != null)
{
dealBD.NumFacturaEpicor = deal.Properties.NumFacturaEpicor.Value;
}
strResultado.Append(" * Paso 7 ");
IDictionary <string, PipelineState> stages = new Dictionary <string, PipelineState>();
if (deal.Properties.Pipeline != null &&
pipelineStages.TryGetValue(deal.Properties.Pipeline.Value, out stages))
{
PipelineState pipelineState = null;
if (deal.Properties.DealStage != null && stages.TryGetValue(deal.Properties.DealStage.Value, out pipelineState))
{
dealBD.StageProbability = pipelineState.Metadata.Probability;
dealBD.StageName = pipelineState.Label;
}
}
strResultado.Append(" * Paso 8 ");
//FILTRO DE PIPELINE
if (deal.Properties.Pipeline != null && deal.Properties.Pipeline.Value == FiltroDealPipeline)
{
dbService.CreateDeal(dealBD);
syncedDeals++;
}
} //END IF
Thread.Sleep(150);
} //END FOR
} //END WHILE
Trace.TraceInformation(string.Format("[DealsSyncJob.SyncDeals] Finishing at {0}", DateTime.Now));
procesoDetalle.FechaFin = DateTime.Now;
procesoDetalle.Estatus = false;
procesoDetalle.Resultado = string.Format("Se sincronizaron {0} deals", syncedDeals);
strResultado.Append(string.Format("|Se sincronizaron {0} deals", syncedDeals));
dbService.ActualizarProcesoEjecucion(procesoDetalle);
} //FIN DE PROCESO INFO
}
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Trace.TraceInformation(exception.Message);
strResultado.Append("|" + exception.Message);
if (exception.Source != null)
{
strResultado.Append("|" + exception.Source);
}
if (exception.StackTrace != null)
{
strResultado.Append("|" + exception.StackTrace);
}
try
{
System.Diagnostics.StackTrace trace = new System.Diagnostics.StackTrace(exception, true);
strResultado.Append("|" + String.Format("<p>Error Detail Message :{0} => Error In :{1} => Line Number :{2} => Error Method:{3}</p>",
HttpUtility.HtmlEncode(exception.Message),
trace.GetFrame(0).GetFileName(),
trace.GetFrame(0).GetFileLineNumber(),
trace.GetFrame(0).GetMethod().Name));
}
catch (Exception ex) { }
}
finally
{
procesoInfo.EstatusEjecucion = false;
procesoInfo.UltimaEjecucion = DateTime.Now;
procesoInfo.Resultado = strResultado.ToString();
dbService.ActualizarEstatusProceso(procesoInfo);
executing = false;
Monitor.Exit(thisLock);
}
}
}
