Classe programada para criar crm ocorrência de forma automática sendo:
Somente se não existir uma ocorrência aberta para o cliente
Existir 2 ou mais ocorrência geradas automaticamente
Se não houver mais de 1 venda realizada para o cliente
O sistema processará a criação da ocorrência conforme a média de intervalo entre as vendas (compra do cliente) para o cliente e caso não haja nenhumas das citações acima, pode ser que haja mais regras no código conforme evolução do código.
using System.Data;
using Atl.Bll.Core;
using Atl.Dal.Core;
using Atl.Dal.Entities;
using Atl.Dto.Entities;
using Atl.Dto.Extensions;
using System;
using System.Collections.Generic;
using System.Text;
/// <summary>
/// A classe cria uma CRM Ocorrência para cada cliente que:
/// 1 - Não tenha nenhuma ocorrência em aberto
/// 2 - Não tenha nenhuma ocorrência fechada dentro do período da última venda até o a soma da média de venda.
/// </summary>
public class RecalcularEstoqueRecebimento : AutomacaoBase
{
//Variável para calcular a média, por padrão pega os últimos 12 meses
//esta variável é utilizada na geração dos registros para pegar a média de intervalo entre as vendas.
private string intervalDate = "DATE( now() - INTERVAL 12 MONTH )";
public override void Run()
{
var dtClient = GetClients();
var listClient = new List<ClientResult>();
foreach (DataRow item in dtClient.Rows)
{
var clientID = int.Parse(item["CodigoCliente"].ToString());
if (clientID == 0) continue;
if (!CreateTableSalesResult(clientID)) continue;
var dtResult = GetSalesResult(clientID);
foreach (DataRow itemResult in dtResult.Rows)
{
DateTime lastSale;
if (!DateTime.TryParse(itemResult["UltimaVenda"].ToString(), out lastSale))
{
continue;
}
listClient.Add(new ClientResult(Session)
{
ClientID = clientID,
DaysAverage = int.Parse(itemResult["DiasEntreCompras"].ToString()),
LastSale = lastSale
});
}
}
listClient.ForEach(x => x.ProcessSchedule());
}
private DataTable GetClients()
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT T.codigo AS CodigoCliente FROM ( ");
sql.Append("(SELECT COUNT(dav.codigo) AS Qtde, cliente.codigo FROM dav ");
sql.Append("INNER JOIN modelodav ON modelodav.codigo = dav.codigomodelodav AND modelodav.TipoDav <> 4 ");
sql.Append("INNER JOIN cliente ON cliente.codigo=dav.CodigoCF ");
sql.Append($"WHERE dav.cancelado <> 'Sim' AND dav.`data`>={intervalDate} AND cliente.desativadosn='N' ");
sql.Append("GROUP BY cliente.codigo) ");
sql.Append("UNION ");
sql.Append("(SELECT COUNT(cupom.codigo) AS Qtde, cliente.codigo FROM cupom ");
sql.Append("INNER JOIN cliente ON cliente.codigo=cupom.CodigoCliente ");
sql.Append($"WHERE cupom.cancelado <> 'Sim' AND cupom.`data`>={intervalDate} AND cliente.desativadosn='N' ");
sql.Append("GROUP BY cliente.codigo) ");
sql.Append("UNION ");
sql.Append("(SELECT COUNT(nf.codigo) AS Qtde, cliente.codigo FROM nf ");
sql.Append("INNER JOIN cliente ON cliente.codigo=nf.codigocf ");
sql.Append($"WHERE nf.TipoNF = 1 AND nf.cancelada <> 'Sim' AND nf.dataemissao >= {intervalDate} AND cliente.desativadosn='N' ");
sql.Append("GROUP BY cliente.codigo) ");
sql.Append("UNION ");
sql.Append("(SELECT COUNT(nfce.codigo) AS Qtde, cliente.codigo FROM nfce ");
sql.Append("INNER JOIN cliente ON cliente.codigo=nfce.codigocf ");
sql.Append($"WHERE nfce.cancelada <> 'Sim' AND nfce.dataemissao >= {intervalDate} AND cliente.desativadosn='N' ");
sql.Append("GROUP BY cliente.codigo) ");
sql.Append(") AS T ");
sql.Append("GROUP BY T.codigo HAVING SUM(T.Qtde) > 1 ");
return new DbHelper(Session).ExecuteDataTable(sql.ToString());
}
private bool CreateTableSalesResult(int codigoCliente)
{
//var temporaryTable = "TEMPORARY";
var temporaryTable = "";
var dbHelper = new DbHelper(Session);
dbHelper.ExecuteNonQuery($"DROP {temporaryTable} TABLE IF EXISTS QueryAgendamentoData");
dbHelper.ExecuteNonQuery($"CREATE {temporaryTable} TABLE QueryAgendamentoData (Data DATE, KEY Data(Data));");
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO QueryAgendamentoData ");
sql.Append("(SELECT dav.DATA AS DATA FROM dav ");
sql.Append("INNER JOIN modelodav ON modelodav.codigo = dav.codigomodelodav AND modelodav.TipoDav <> 4 ");
sql.Append($"WHERE dav.CodigoCF = {codigoCliente} AND dav.cancelado <> 'Sim' AND dav.`data` >= {intervalDate} ");
sql.Append("GROUP BY dav.`data`) ");
sql.Append("UNION ");
sql.Append("(SELECT cupom.`Data` AS DATA FROM cupom ");
sql.Append($"WHERE cupom.CodigoCliente = {codigoCliente} AND cupom.cancelado <> 'Sim' AND cupom.`data` >= {intervalDate} ");
sql.Append("GROUP BY cupom.`data`) ");
sql.Append("UNION ");
sql.Append("(SELECT nf.dataemissao AS DATA FROM nf ");
sql.Append($"WHERE nf.TipoNF = 1 AND nf.Codigocf = {codigoCliente} AND nf.cancelada <> 'Sim' AND nf.dataemissao >= {intervalDate} ");
sql.Append("GROUP BY nf.dataemissao) ");
sql.Append("UNION ");
sql.Append("(SELECT nfce.dataemissao AS DATA FROM nfce ");
sql.Append($"WHERE nfce.Codigocf = {codigoCliente} AND nfce.cancelada <> 'Sim' AND nfce.dataemissao >= {intervalDate} ");
sql.Append("GROUP BY nfce.dataemissao) ");
var result = dbHelper.ExecuteNonQuery(sql.ToString());
return result > 0;
}
private DataTable GetSalesResult(int codigoCliente)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT IFNULL(T.UltimaVenda,'0000-00-00') AS UltimaVenda, ROUND(AVG(IFNULL(T.DiasEntreCompras,0))) AS DiasEntreCompras ");
sql.Append("FROM (SELECT MAX(QueryAgendamentoData.DATA) as UltimaVenda, ");
sql.Append("DATEDIFF((SELECT min(d.DATA) FROM QueryAgendamentoData AS d WHERE d.DATA > QueryAgendamentoData.DATA limit 1 ), QueryAgendamentoData.DATA ) AS DiasEntreCompras ");
sql.Append("FROM ");
sql.Append("QueryAgendamentoData ");
sql.Append("GROUP BY QueryAgendamentoData.Data) AS T ");
return new DbHelper(Session).ExecuteDataTable(sql.ToString());
}
}
class ClientResult
{
private SessionDal _session { get; }
//Variável utiliza para indicar qual é o limite de ocorrência automatizada que será gerada durante o mês
//neste caso pode ser que tenha mês que gere 2 ou 3 ocorrência devido os cálculo, aí não aprofundei não.
private int crmCountLimitForMonth = 2;
public int ClientID { get; set; }
public int DaysAverage { get; set; }
public DateTime LastSale { get; set; }
public ClientResult(SessionDal session)
{
this._session = session;
}
public void ProcessSchedule()
{
int durationOcorrencia = 30;
string defaultDescription = "Ocorrência gerada automaticamente";
bool isDateValid = false;
if (!IsValidate()) return;
var crmConfiguracao = new CrmConfiguracaoGeralDal(this._session).GetFirst();
if (crmConfiguracao != null)
{
if (!crmConfiguracao.Descricao.Trim().IsNullOrEmpty())
{
defaultDescription = crmConfiguracao.Descricao;
}
durationOcorrencia = crmConfiguracao.DuracaoPadraoOcorenciaEmMinutos ?? 30;
if (durationOcorrencia == 0) durationOcorrencia = 30;
if (crmConfiguracao.DuracaoMaximaOcorenciaEmMinutos > 0 && durationOcorrencia > crmConfiguracao.DuracaoMaximaOcorenciaEmMinutos)
{
durationOcorrencia = crmConfiguracao.DuracaoMaximaOcorenciaEmMinutos;
}
}
int userID = 1;
var client = new ClienteDal(this._session).Find(x => x.Codigo == ClientID);
UsuarioDal userDal = new UsuarioDal(this._session);
CrmColaboradorDal crmColaboradorDal = new CrmColaboradorDal(this._session);
if (client != null)
{
if (client.CodigoVendedorTablete > 0)
{
userDal.Join(crmColaboradorDal, u => u.Codigo, c => c.CodigoUsuario)
.Where((u, c) => u.CodigoVendedor == (int)client.CodigoVendedorTablete &&
c.Ativo == true);
var user = userDal.Select(u => u.Codigo).GetFirst();
if (user != null)
{
userID = user.Codigo;
}
}
}
//Captura a data atual ou a ultima data usada pelo usuario
DateTime startDate = GetDateUser(userID);
DateTime endDate;
do
{
startDate = GetWorkingDayDate(startDate);
endDate = startDate.AddMinutes(durationOcorrencia);
var crmOcorrencia = new CrmOcorrenciaDal(this._session).Find(e =>
e.CodigoUsuario == userID &&
e.Status == Atl.Dto.Enumerados.CrmOcorrenciaStatus.EmAberto &&
((e.DaraHoraInicio >= startDate && e.DaraHoraInicio <= endDate) ||
(e.DaraHoraFinal >= startDate && e.DaraHoraFinal <= endDate) ||
((startDate >= e.DaraHoraInicio && startDate <= e.DaraHoraFinal) &&
(endDate >= e.DaraHoraInicio && endDate <= e.DaraHoraFinal))));
if (crmOcorrencia == null)
{
isDateValid = true;
}
else
{
DateTime dateOcorrencia;
if (crmOcorrencia.DaraHoraFinal != null && crmOcorrencia.DaraHoraFinal >= startDate)
{
dateOcorrencia = crmOcorrencia.DaraHoraFinal ?? startDate.AddMinutes(durationOcorrencia);
startDate = dateOcorrencia.AddMinutes(1);
}
else
{
startDate = startDate.AddMinutes(durationOcorrencia + 1);
}
///Verificar se vai alterar o dia ou só a hora
if (startDate.TimeOfDay >= DateTime.Now.Date.AddHours(18).TimeOfDay ||
startDate.AddMinutes(durationOcorrencia).TimeOfDay > DateTime.Now.Date.AddHours(18).TimeOfDay
)
{
startDate = startDate.Date.AddDays(1).AddHours(8);
}
}
} while (!isDateValid);
if (!IsValidateCRMDate(startDate))
{
return;
}
CrmOcorrencia crmOcorrenciaToSave = new CrmOcorrencia()
{
CodigoCliente = ClientID,
CodigoUsuario = userID,
CodigoCrmCategoriaOcorrencia = 5,
CodigoCrmMotivoOcorrencia = 7,
DaraHoraInicio = startDate,
DaraHoraFinal = endDate,
Descricao = defaultDescription,
Notas = "Ocorrência gerada automaticamente pelo fato do cliente ter excedido o prazo médio de compra",
GeradaPelaAutomatizacao = true
};
SaveCRM(crmOcorrenciaToSave);
LastDateUser.AddUserLastDate(userID, endDate);
}
private bool IsValidate()
{
if (ClientID == 0 || DaysAverage == 0 || LastSale > DateTime.Now)
{
return false;
}
var endDate = LastSale.AddDays(DaysAverage);
var crmOcorrencia = new CrmOcorrenciaDal(_session).Find(
x => x.CodigoCliente == ClientID &&
(x.Status == Atl.Dto.Enumerados.CrmOcorrenciaStatus.EmAberto ||
(x.DaraHoraInicio > LastSale &&
x.DaraHoraInicio <= endDate &&
x.Status != Atl.Dto.Enumerados.CrmOcorrenciaStatus.Cancelada)
));
if (crmOcorrencia != null)
{
return false;
}
var countDataTable = GetGeneratedCountCrm();
foreach (DataRow item in countDataTable.Rows)
{
int qtde;
if (int.TryParse(item[0].ToString(), out qtde))
{
if (qtde >= crmCountLimitForMonth) return false;
}
}
return true;
}
private DataTable GetGeneratedCountCrm()
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT COUNT(crmocorrencia.Codigo) AS Qtde ");
sql.Append("FROM crmocorrencia ");
sql.Append($"WHERE crmocorrencia.CodigoCliente = {ClientID} ");
sql.Append("and crmocorrencia.GeradaPelaAutomatizacao = 1 ");
sql.Append($"AND crmocorrencia.`Status` <> {((int)Atl.Dto.Enumerados.CrmOcorrenciaStatus.Cancelada)} ");
sql.Append("AND YEAR(crmocorrencia.DaraHoraInicio) = YEAR(CURRENT_DATE) ");
sql.Append("AND MONTH(crmocorrencia.DaraHoraInicio) = MONTH(CURRENT_DATE) ");
return new DbHelper(this._session).ExecuteDataTable(sql.ToString());
}
private bool IsValidateCRMDate(DateTime date)
{
date = date.AddDays(-this.DaysAverage);
var crmOcorrencia = new CrmOcorrenciaDal(_session).Find(
x => x.CodigoCliente == ClientID &&
x.Status != Atl.Dto.Enumerados.CrmOcorrenciaStatus.EmAberto &&
x.Status != Atl.Dto.Enumerados.CrmOcorrenciaStatus.Cancelada &&
x.DaraHoraFinal >= date);
return crmOcorrencia == null;
}
private DateTime GetWorkingDayDate(DateTime calculatedStartDate)
{
bool isDateValid = false;
DateTime date = GetNextWorkingDay(calculatedStartDate);
var listHolidayDate = GetHolidays();
while (!isDateValid)
{
var ret = listHolidayDate.FindIndex(x => x == date);
if (ret == -1)
{
isDateValid = true;
break;
}
date = date.AddDays(1);
date = GetNextWorkingDay(date);
}
return date;
}
private DateTime GetNextWorkingDay(DateTime date)
{
while (IsWeekend(date))
{
date = date.AddDays(1);
}
return date;
}
private bool IsWeekend(DateTime date)
{
return date.DayOfWeek == DayOfWeek.Sunday || date.DayOfWeek == DayOfWeek.Saturday;
}
private List<DateTime> GetHolidays()
{
List<DateTime> listReturn = new List<DateTime>();
var sql = "SELECT feriado.data FROM feriado";
var holidaysTable = new DbHelper(this._session).ExecuteDataTable(sql);
foreach (DataRow item in holidaysTable.Rows)
{
DateTime date;
if (DateTime.TryParse(item["data"].ToString(), out date))
{
listReturn.Add(date);
}
}
return listReturn;
}
private void SaveCRM(CrmOcorrencia crmOcorrencia)
{
var sql = new StringBuilder();
sql.Append("INSERT INTO crmocorrencia (CodigoCliente, ");
sql.Append("CodigoUsuario, CodigoCrmCategoriaOcorrencia, CodigoCrmMotivoOcorrencia, ");
sql.Append("DaraHoraInicio, DaraHoraFinal, Descricao, Notas, GeradaPelaAutomatizacao) ");
sql.Append("Values(");
sql.Append(crmOcorrencia.CodigoCliente.ToString() + ", ");
sql.Append(crmOcorrencia.CodigoUsuario.ToString() + ", ");
sql.Append(crmOcorrencia.CodigoCrmCategoriaOcorrencia.ToString() + ", ");
sql.Append(crmOcorrencia.CodigoCrmMotivoOcorrencia.ToString() + ", ");
sql.Append("'" + crmOcorrencia.DaraHoraInicio?.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss") + "', ");
sql.Append("'" + crmOcorrencia.DaraHoraFinal?.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss") + "', ");
sql.Append("'" + crmOcorrencia.Descricao + "', ");
sql.Append("'" + crmOcorrencia.Notas + "', ");
sql.Append(1);
sql.Append(")");
new DbHelper(this._session).ExecuteNonQuery(sql.ToString());
}
private DateTime GetDateUser(int userID)
{
var lastDateUser = LastDateUser.GetUserLastDate(userID);
if (lastDateUser == null)
{
return DateTime.Now.Date.AddDays(1).AddHours(8);
}
return lastDateUser.Date;
}
}
///Para evitar o processo de loop da data atual até achar uma data disponível, foi criado essa classe
///Assim sempre armazena a última data usada e prossegue da li pra frente
class LastDateUser
{
public int UserID { get; set; }
public DateTime Date { get; set; }
static public List<LastDateUser> ListDate = new List<LastDateUser>();
static public void AddUserLastDate(int userID, DateTime date)
{
var lastDateUser = GetUserLastDate(userID);
if (lastDateUser == null)
{
LastDateUser.ListDate.Add(new
LastDateUser
{
UserID = userID,
Date = date
});
return;
}
lastDateUser.Date = date;
}
static public LastDateUser GetUserLastDate(int userID)
{
var index = LastDateUser.ListDate.FindIndex(x => x.UserID == userID);
if (index > -1)
{
return LastDateUser.ListDate[index];
}
return null;
}
}
