private bool VerificarValores(List <int> precos, float ValorTotal)
{
try
{
if (precos.Count == 0)
{
return(true);
}
bd.BulkInsert(precos.Select(c => c).Distinct().ToList(), "#tmpPrecos", false, true);
string sql =
@"SELECT
fp.ID, fp.Valor
FROM tFilarmonicaPreco fp (NOLOCK)
INNER JOIN #tmpPrecos tm ON tm.ID = fp.ID";
List <EstruturaAssinaturaFilarmonicaPreco> lista = new List <EstruturaAssinaturaFilarmonicaPreco>();
bd.Consulta(sql);
while (bd.Consulta().Read())
{
lista.Add(new EstruturaAssinaturaFilarmonicaPreco()
{
ID = bd.LerInt("ID"),
Valor = bd.LerDecimal("Valor"),
});
}
decimal valorBD = 0;
foreach (int preco in precos)
{
var itemPreco = lista.Where(c => c.ID == preco).FirstOrDefault();
if (itemPreco == null)
{
return(false);
}
valorBD += itemPreco.Valor;
}
return(Convert.ToDecimal(ValorTotal) == valorBD);
}
finally
{
bd.Fechar();
}
}
public void InserirCodigos(BD bd, int ParceiroID, Enumerators.TipoParceiro tipo, Dictionary <int, string> listaInserir)
{
DataTable dttBulk = new DataTable();
DataRow dtr;
switch (tipo)
{
case Enumerators.TipoParceiro.Codigo:
dttBulk.Columns.Add("ID", typeof(int)).DefaultValue = 0;
dttBulk.Columns.Add("ParceiroID", typeof(int));
dttBulk.Columns.Add("Codigo", typeof(string));
dttBulk.Columns.Add("TamanhoCodigo", typeof(string));
foreach (var str in listaInserir)
{
dtr = dttBulk.NewRow();
dtr["ParceiroID"] = ParceiroID;
dtr["Codigo"] = str.Value;
dtr["TamanhoCodigo"] = str.Value.Length;
dttBulk.Rows.Add(dtr);
}
bd.BulkInsert(dttBulk, "tCodigoPromo", false, false);
break;
case Enumerators.TipoParceiro.Bin:
dttBulk.Columns.Add("ID", typeof(int)).DefaultValue = 0;
dttBulk.Columns.Add("BIN", typeof(string));
dttBulk.Columns.Add("ParceiroID", typeof(int)).DefaultValue = ParceiroID;
foreach (var str in listaInserir)
{
dtr = dttBulk.NewRow();
dtr["Bin"] = str.Value;
dtr["ParceiroID"] = ParceiroID;
dttBulk.Rows.Add(dtr);
}
bd.BulkInsert(dttBulk, "tBin", false, false);
break;
}
}
public void EncaminhaFeedback(List <EstruturaFeedback> lista, int respiro, int quantidade)
{
BD bd = new BD();
try
{
EstruturaFeedback feedBack = null;
EnviarEmailParalela enviarEmail = new EnviarEmailParalela();
string corpoEmail = new StreamReader(ConfigurationManager.AppSettings["CaminhoFisicoHTMLFeedBack"] + "\\Feedback.htm").ReadToEnd();
List <int> vendabilheterias = new List <int>();
while (lista.Count > 0)
{
for (int i = 0; i < quantidade; i++)
{
feedBack = lista.FirstOrDefault();
if (feedBack == null)
{
break;
}
enviarEmail.EnviarFeedback(corpoEmail, feedBack);
vendabilheterias.Add(feedBack.VendaBilheteriaID);
lista.Remove(feedBack);
}
Thread.Sleep(respiro);
}
bd.BulkInsert(vendabilheterias, "#VendasEnviadas", false, true);
bd.Executar(
@"
UPDATE vb SET vb.EmailEnviado = 'T'
FROM tVendaBilheteria vb
INNER JOIN #VendasEnviadas ON vb.ID = #VendasEnviadas.ID
" );
}
finally
{
bd.Fechar();
}
}
public void GerarHistoricalData(int quantidade, string de, string ate, string caminho)
{
try
{
bd.Consulta(string.Format(@"
SELECT {0}
vb.Senha AS Senha, vb.ID AS VendaBilheteriaID, vb.Senha, vb.Status, vb.DataVenda, vb.ValorTotal, vb.TaxaConvenienciaValorTotal, vb.TaxaEntregaValor, vb.IP, cn.Nome AS Canal,
c.ID AS ClienteID, c.CPF AS CPFCliente, c.DataCadastro, c.Nome AS NomeCliente, c.Email AS EmailCliente, c.DDDTelefone + c.Telefone AS TelefoneCliente, c.EnderecoCliente, c.CidadeCliente, c.EstadoCliente, c.CEPCliente, 'BR' AS PaisCliente,
ce.Nome, ce.CPF, ce.Endereco, ce.Cidade, ce.Estado, ce.CEP, e.Nome AS Entrega, e.PrazoEntrega, e.ID AS EntregaID,
CASE WHEN ea.ID IS NOT NULL
THEN ea.Data + '235959'
ELSE
CONVERT(VARCHAR,DateAdd(Day, e.PrazoEntrega, CONVERT(DATETIME, SUBSTRING(vb.DataVenda, 0 ,9), 112)), 112) + '235959'
END AS DataEntrega
FROM tVendaBilheteria vb (NOLOCK)
INNER JOIN tCaixa cx (NOLOCK) ON cx.ID = vb.CaixaID
INNER JOIN tLoja l (NOLOCK) ON l.ID = cx.LojaID
INNER JOIN tCanal cn (NOLOCK) ON cn.ID = l.CanalID
INNER JOIN tCliente c (NOLOCK) ON vb.ClienteID = c.ID
LEFT JOIN tClienteEndereco ce (NOLOCK) ON ce.ID = vb.ClienteEnderecoID
INNER JOIN tEntregaControle ec (NOLOCK) ON ec.ID = vb.EntregaControleID
INNER JOIN tEntrega e (NOLOCK) ON e.ID = ec.EntregaID
LEFT JOIN tEntregaAgenda ea (NOLOCK) ON ea.ID = vb.EntregaAgendaID
WHERE
vb.DataVenda >= '{1}' AND vb.DataVenda <= '{2}' AND vb.Status = 'F' AND cn.ID IN(1, 2)
--vb.ID IN (7796494, 7800386)
", quantidade > 0 ? "TOP " + quantidade : string.Empty, de, ate));
if (!bd.Consulta().Read())
{
throw new Exception("Derp! No results!");
}
var transaction = new List <order>();
var ids = new List <int>();
do
{
ids.Add(bd.LerInt("VendaBilheteriaID"));
transaction.Add(new order()
{
orderNumber = bd.LerString("Senha"),
orderType = "Sale",
orderDateTime = bd.LerDateTimeAccertify("DataVenda"),
totalAmount = bd.LerDecimal("ValorTotal").ToString("#0.00"),
totalSalesTax = bd.LerDecimal("TaxaConvenienciaValorTotal").ToString("#0.00"),
totalShippingCharges = bd.LerDecimal("TaxaEntregaValor").ToString("#0.00"),
ipAddress = bd.LerString("IP"),
salesChannel = bd.LerString("Canal").ToCleanString().ToUpper(),
paymentInformation = new paymentInformation()
{
},
failedCCInformation = new failedCCInformation()
{
},
memberInformation = new memberInformation()
{
memberID = bd.LerInt("ClienteID").ToString(),
memberCPF = bd.LerString("CPFCliente").ToString(),
membershipDate = bd.LerDateTimeAccertify("DataCadastro"),
memberFullName = bd.LerString("NomeCliente").ToCleanString().ToUpper(),
memberFirstName = bd.LerString("NomeCliente").Split(' ').FirstOrDefault().ToCleanString().ToUpper(),
memberLastName = bd.LerString("NomeCliente").Split(' ').LastOrDefault().ToCleanString().ToUpper(),
memberPostalCode = bd.LerString("CEPCliente").AsCEP(),
memberEmail = bd.LerString("EmailCliente").ToCleanString().ToUpper(),
memberPhone = bd.LerString("TelefoneCliente").ToCleanString().ToUpper(),
memberAddress = bd.LerString("EnderecoCliente").ToCleanString().ToUpper(),
memberCity = bd.LerString("CidadeCliente").ToCleanString().ToUpper(),
memberRegion = bd.LerString("EstadoCliente").ToCleanString().ToUpper(),
memberCountry = "BR",
registeredMember = "Y",
guest = "N",
},
shippingInformation = new shippingInformation()
{
shippingFullName = bd.LerString("Nome").ToUpper(),
shippingCPF = bd.LerString("CPF"),
shippingFirstName = bd.LerString("Nome").Split(' ').FirstOrDefault().ToCleanString().ToUpper(),
shippingLastName = bd.LerString("Nome").Split(' ').LastOrDefault().ToCleanString().ToUpper(),
shippingAddress = bd.LerString("Endereco").ToCleanString().ToUpper(),
shippingCity = bd.LerString("Cidade").ToCleanString().ToUpper(),
shippingRegion = bd.LerString("Estado").ToCleanString().ToUpper(),
shippingPostalCode = bd.LerString("CEP").AsCEP(),
shippingCountry = bd.LerInt("EntregaID") == 0 ? string.Empty : "BR",
shippingMethod = bd.LerString("Entrega").ToCleanString(),
shippingDeadline = bd.LerInt("EntregaID") == 0 ? string.Empty : bd.LerDateTimeAccertify("DataEntrega")
}
});
} while (bd.Consulta().Read());
bd.FecharConsulta();
bd.BulkInsert(ids, "#vb", false, true);
bd.Consulta(@"
SELECT
#vb.ID, e.ID AS EventoID, e.Nome AS Evento, ap.Horario, l.Nome AS Local, l.Logradouro, l.Cidade, l.Estado, l.CEP, p.ID AS PrecoID,
p.Valor, COUNT(DISTINCT i.ID) AS Quantidade, et.Nome AS Categoria, es.Descricao AS Genero, vbi.TaxaConvenienciaValor
FROM #vb (NOLOCK)
INNER JOIN tIngressoLog il (NOLOCK) ON il.VendaBilheteriaID = #vb.ID
INNER JOIN tIngresso i (NOLOCK) ON i.ID = il.IngressoID
INNER JOIN tEvento e (NOLOCK) ON e.ID = i.EventoID
INNER JOIN tApresentacao ap (NOLOCK) ON ap.ID = i.ApresentacaoID
INNER JOIN tLocal l (NOLOCK) ON l.ID = e.LocalID
INNER JOIN tPreco p (NOLOCK) ON p.ID = il.PrecoID
LEFT JOIN tEventoSubtipo es (NOLOCK) ON es.ID = e.EventoSubTipoID
LEFT JOIN tEventoTipo et (NOLOCK) ON et.ID = es.EventoTipoID
INNER JOIN tVendaBilheteriaItem vbi (NOLOCK) ON vbi.ID = il.VendaBilheteriaItemID
WHERE il.Acao = 'V' AND vbi.PacoteID = 0
GROUP BY #vb.ID, e.ID, e.Nome, ap.Horario, l.Nome, l.Logradouro, l.Cidade, l.Estado, l.CEP, p.ID, p.Valor, et.Nome, es.Descricao, vbi.TaxaConvenienciaValor
ORDER BY #vb.ID, p.ID");
int vendaID = 0;
var venda = new order();
while (bd.Consulta().Read())
{
if (vendaID != bd.LerInt("ID"))
{
vendaID = bd.LerInt("ID");
venda = transaction.Where(c => c.orderNumber.EndsWith(vendaID.ToString())).FirstOrDefault();
if (venda == null)
{
continue;
}
}
venda.eventDetails.Add(new eventinfo()
{
eventID = bd.LerInt("EventoID").ToString(),
eventDateTime = bd.LerDateTimeAccertify("Horario"),
eventLocation = bd.LerString("Local").ToCleanString().ToUpper(),
eventAddress = bd.LerString("Logradouro").ToCleanString().ToUpper(),
eventPostalCode = bd.LerString("CEP").AsCEP(),
eventCity = bd.LerString("Cidade").ToCleanString().ToUpper(),
eventState = bd.LerString("Estado").ToCleanString().ToUpper(),
eventCountry = "BR",
ticketPrice = bd.LerDecimal("Valor").ToString("#0.00"),
ticketQuantity = bd.LerInt("Quantidade").ToString(),
eventCategory = bd.LerString("Categoria").ToCleanString().ToUpper(),
eventGenre = bd.LerString("Genero").ToCleanString().ToUpper(),
eventTax = bd.LerDecimal("TaxaConvenienciaValor").ToString("#0.00"),
eventDescription = bd.LerString("Evento"),
});
}
XmlSerializer ser = new XmlSerializer(transaction.GetType());
XmlDocument xml = new XmlDocument();
using (MemoryStream fs = new MemoryStream())
{
ser.Serialize(fs, transaction);
fs.Position = 0;
var sr = new StreamReader(fs);
string myStr = sr.ReadToEnd().Replace("eventinfo", "event").Replace("ArrayOfOrder", "transactions");
xml.LoadXml(myStr);
}
xml.Save(caminho);
}
finally
{
bd.Fechar();
}
}
public void CarregarQuantidades()
{
BD bd = new BD();
try
{
using (DataTable dttBulk = new DataTable("Apresentacoes"))
{
dttBulk.Columns.Add("ID", typeof(int));
DataRow dtr;
foreach (int apresentacaoID in this.MapasEsquematicosInfo.Select(c => c.ApresentacaoID).Distinct())
{
if (apresentacaoID.Equals(0))
{
continue;
}
dtr = dttBulk.NewRow();
dtr["ID"] = apresentacaoID;
dttBulk.Rows.Add(dtr);
}
bd.BulkInsert(dttBulk, "#ApresentacoesMapaEsquematico", false, true);
}
bd.Consulta(
@"SELECT ApresentacaoID,
SetorID,
SUM(CASE
WHEN i.Status = 'D'
THEN 1
ELSE 0
END) AS QuantidadeDisponivel,
COUNT(i.ID) AS Lotacao
FROM tIngresso i (NOLOCK)
INNER JOIN #ApresentacoesMapaEsquematico ap ON ap.ID = i.ApresentacaoID
GROUP BY ApresentacaoID, SetorID
ORDER BY ApresentacaoID, SetorID");
while (bd.Consulta().Read())
{
try
{
foreach (SetorInfo setor in this.MapasEsquematicosInfo.
Where(c => c.ApresentacaoID == bd.LerInt("ApresentacaoID"))
.FirstOrDefault()
.Setores.Where(c => c.ID == bd.LerInt("SetorID")).ToList())
{
setor.Lotacao = bd.LerInt("Lotacao");
setor.QuantidadeDisponível = bd.LerInt("QuantidadeDisponivel");
setor.UltimaAtualizacao = DateTime.Now;
}
}
catch (Exception)
{
//Ignora, Apresentacao está nula
continue;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
bd.Fechar();
}
}
public void RegistrarEntrega(List <int> lstVendaBilheteriaID, int UsuarioID, int EmpresaID, int CaixaID, int LojaID, int CanalID)
{
//Auxiliar para consulta
BD bd = new BD();
BD bdAux = new BD();
IngressoLog log = null;
try
{
bd.IniciarTransacao();
DataTable dttBulk = new DataTable("VendaBilheteria");
dttBulk.Columns.Add("ID", typeof(int));
DataRow dtr;
foreach (int id in lstVendaBilheteriaID)
{
dtr = dttBulk.NewRow();
dtr["ID"] = id;
dttBulk.Rows.Add(dtr);
}
bdAux.BulkInsert(dttBulk, "#tmpVendaBilheteriaID", false, true);
bdAux.Consulta(string.Format(
@"SELECT i.ID, i.PrecoID, i.EventoID, i.BloqueioID, i.CortesiaID,
i.ClienteID, i.VendaBilheteriaID, vb.ClienteID
FROM tVendaBilheteria vb (NOLOCK)
INNER JOIN #tmpVendaBilheteriaID tmp ON tmp.ID = vb.ID
INNER JOIN tIngresso i (NOLOCK) ON i.VendaBilheteriaID = vb.ID
WHERE i.Status = '{0}' ", Ingresso.IMPRESSO));
while (bdAux.Consulta().Read())
{
bd.Executar(string.Format(
@"UPDATE tIngresso SET Status = '{0}'
WHERE tIngresso.ID = {1} ", Ingresso.ENTREGUE, bdAux.LerInt("ID")));
log = new IngressoLog();
log.IngressoID.Valor = bdAux.LerInt("ID");
log.UsuarioID.Valor = UsuarioID;
log.TimeStamp.Valor = DateTime.Now;
log.Acao.Valor = Ingresso.ENTREGUE;
log.PrecoID.Valor = bdAux.LerInt("PrecoID");
log.CortesiaID.Valor = bdAux.LerInt("CortesiaID");
log.BloqueioID.Valor = bdAux.LerInt("BloqueioID");
log.VendaBilheteriaItemID.Valor = 0;
log.Obs.Valor = string.Empty;
log.EmpresaID.Valor = EmpresaID;
log.VendaBilheteriaID.Valor = bdAux.LerInt("VendaBilheteriaID");
log.CaixaID.Valor = CaixaID;
log.LojaID.Valor = LojaID;
log.CanalID.Valor = CanalID;
log.ClienteID.Valor = bdAux.LerInt("ClienteID");
log.CodigoBarra.Valor = string.Empty;
log.CodigoImpressao.Valor = 0;
if (!Convert.ToBoolean(bd.Executar(log.StringInserir())))
{
throw new BilheteriaException("Log de impressão do ingresso não foi inserido.");
}
}
bd.FinalizarTransacao();
}
catch (Exception ex)
{
bd.DesfazerTransacao();
throw ex;
}
finally
{
bd.Fechar();
}
}
public List <EstruturaRegistroEntrega> PesquisarSenhas(List <string> senhas, string condicao)
{
BD bd = new BD();
try
{
DataTable dttBulk = new DataTable("Senhas");
dttBulk.Columns.Add("Senha", typeof(string));
DataRow dtr;
foreach (string senha in senhas)
{
dtr = dttBulk.NewRow();
dtr["Senha"] = senha;
dttBulk.Rows.Add(dtr);
}
string sql = string.Empty;
bd.BulkInsert(dttBulk, "#tmpVendas", true, "Senha", "varchar(20)", "Latin1_General_CI_AI");
if (condicao != "")
{
condicao = String.Concat("AND", " ", condicao);
sql = string.Format(@"SELECT DISTINCT vb.ID, vb.Senha, IsNull(e.Nome, 'Sem Taxa') AS TaxaEntrega, vb.DataVenda,
CASE WHEN c.CNPJ IS NOT NULL
THEN c.NomeFantasia
ELSE IsNull(c.Nome, 'Sem Cliente') COLLATE Latin1_General_CI_AI
END AS Cliente, vb.Status, c.Email
FROM tVendaBilheteria vb (NOLOCK)
INNER JOIN #tmpVendas ON vb.Senha = #tmpVendas.Senha
INNER JOIN tIngresso i (NOLOCK) ON i.VendaBilheteriaID = vb.ID
INNER JOIN tCliente c (NOLOCK) ON c.ID = vb.ClienteID
INNER JOIN tCaixa ca (NOLOCK) ON ca.ID = vb.CaixaID
INNER JOIN tLoja l (NOLOCK) on l.ID = ca.LojaID
LEFT JOIN tEntregaControle tc (NOLOCK) ON tc.ID = vb.EntregaControleID
LEFT JOIN tEntrega e (NOLOCK) ON tc.EntregaID = e.ID
WHERE vb.Status = '{0}' AND i.Status = '{1}' {2}
", VendaBilheteria.PAGO, Ingresso.IMPRESSO, condicao);
}
else
{
sql = string.Format(@"SELECT DISTINCT vb.ID, vb.Senha, IsNull(e.Nome, 'Sem Taxa') AS TaxaEntrega, vb.DataVenda,
CASE WHEN c.CNPJ IS NOT NULL
THEN c.NomeFantasia
ELSE IsNull(c.Nome, 'Sem Cliente') COLLATE Latin1_General_CI_AI
END AS Cliente, vb.Status, c.Email
FROM tVendaBilheteria vb (NOLOCK)
INNER JOIN #tmpVendas ON vb.Senha = #tmpVendas.Senha
INNER JOIN tIngresso i (NOLOCK) ON i.VendaBilheteriaID = vb.ID
INNER JOIN tCliente c (NOLOCK) ON c.ID = vb.ClienteID
INNER JOIN tCaixa ca (NOLOCK) ON ca.ID = vb.CaixaID
INNER JOIN tLoja l (NOLOCK) on l.ID = ca.LojaID
LEFT JOIN tEntregaControle tc (NOLOCK) ON tc.ID = vb.EntregaControleID
LEFT JOIN tEntrega e (NOLOCK) ON tc.EntregaID = e.ID
WHERE vb.Status = '{0}' AND i.Status = '{1}'",
VendaBilheteria.PAGO, Ingresso.IMPRESSO);
}
List <EstruturaRegistroEntrega> lista = new List <EstruturaRegistroEntrega>();
bd.Consulta(sql);
while (bd.Consulta().Read())
{
lista.Add(new EstruturaRegistroEntrega()
{
VendaBilheteriaID = bd.LerInt("ID"),
Cliente = bd.LerString("Cliente"),
DataVenda = bd.LerDateTime("DataVenda"),
Senha = bd.LerString("Senha"),
StatusVenda = bd.LerString("Status"),
TaxaEntrega = bd.LerString("TaxaEntrega"),
Email = bd.LerString("Email")
});
}
return(lista);
}
finally
{
bd.Fechar();
}
}