Path: C:\
<?php
ini_set('max_execution_time',259200);
ini_set ('memory_limit','-1');
require_once("API99_Conexao.php");
Class Mobile
{
public function getMobile($data1, $data2, $data3, $grupo, $empresa)
{ if($grupo<>'nulo')
{
$where1=' and H.id_GrupoCliente='.$grupo.' ';$where2=' and Q.id_GrupoCliente='.$grupo.' ';
}
else
{ if($empresa<>'nulo')
{
$where1=' and C.id_ClienteFaturamento='.$empresa.' ';$where2=' and H.id_Remetente='.$empresa.' ';
}
else
{
$where1=' ';$where2=' ';
}
}
$query = "SELECT DISTINCT
Convert(varchar(10), A.dt_Saida,103) as data
,Convert(varchar(8), A.dt_Saida,112) as datab
,A.id_Manifesto as manifesto
,rtrim(D.ds_Cliente) as transportadora
,rtrim(Convert(varchar(40), M.ds_Pessoa)) as motorista
,rtrim(K.cd_Placa) as placa
,rtrim(L.cd_Placa) as carreta
,rtrim(N.ds_TipoVeiculo) as veiculo
,'ENTREGA' as tipo
,rtrim(C.nr_NotaFiscal) as nrNF
,C.vl_NotaFiscal as vlrNF
,C.vl_Frete as vlrFrete
,rtrim(W1.ds_TipoMovimento) as tipomovimento
,'CTe: '+rtrim(C.nr_Conhecimento) as documento
,C.qt_Volume as volume
,rtrim(Convert(varchar(35), E.ds_Pessoa)) as cliente
,rtrim(Convert(varchar(35), F.ds_Pessoa)) as local1
,rtrim(Convert(varchar(35), P.ds_Pessoa)) as local2
,rtrim(F.ds_Endereco) as endereco1
,rtrim(P.ds_Endereco) as endereco2
,rtrim(F.ds_Bairro) as bairro1
,rtrim(P.ds_Bairro) as bairro2
,rtrim(G.ds_Cidade)+'-'+rtrim(G.UFE_SG) as cidade1
,rtrim(Q.ds_Cidade)+'-'+rtrim(Q.UFE_SG) as cidade2
,rtrim(F.cd_CEP) as cep1
,rtrim(P.cd_CEP) as cep2
,rtrim(G1.ds_Ocorrencia) as ocorrencia
,G1.id_Ocorrencia as id_Ocorre
,Convert(varchar(10),H1.dt_PrazoFechamento,103) as data2
,case
when H1.hr_PrazoFechamento<='24:00' and H1.hr_PrazoFechamento>='00:00'
then H1.hr_PrazoFechamento
else H1.hr_Abertura
end as hora
,rtrim(H1.ds_UsuarioAlteracao) as origem
,Convert(varchar(10), C.dt_PrazoEntrega,103) as limite
,'' as status1
,'' as status2
,'' as status3
,Convert(varchar(8),H1.dt_PrazoFechamento,112) as data3
,Convert(varchar(8),C.dt_Cadastro,112) as data4
,Convert(varchar(10),C.dt_Cadastro,103) as data4a
,rtrim(C.cm_Movimento) as coment
FROM tbdMovimento AS C
left JOIN tbdTipoMovimento AS W1 ON C.id_TipoMovimento = W1.id_TipoMovimento
right JOIN tbdOcorrenciaNota AS H1 on C.id_Movimento = H1.id_Movimento
left JOIN tbdOcorrencia AS G1 ON H1.id_Ocorrencia = G1.id_Ocorrencia
left JOIN tbdConfiguracaoCliente AS H on C.id_ClienteFaturamento = H.id_Pessoa
left JOIN tbdPessoa AS E on E.id_pessoa = C.id_Remetente
left JOIN tbdPessoa AS F on F.id_pessoa = C.id_Destinatario
left JOIN tbdCidade AS G ON G.id_Cidade = C.id_CidadeDestinatario
left JOIN tbdPessoa AS P on P.id_pessoa = C.id_Consignatario
left JOIN tbdCidade AS Q ON Q.id_Cidade = P.id_Cidade
left JOIN tbdManifestoMovimento AS B on (B.id_Manifesto = H1.id_Manifesto or B.id_Manifesto = C.id_Manifesto) and B.ds_Tipo='ENTREGA'
left JOIN tbdManifesto AS A on A.id_Manifesto = B.id_Manifesto or A.id_Manifesto = C.id_Manifesto
left JOIN tbdClienteSistema AS D ON D.id_Cliente = A.id_Cliente
left JOIN tbdVeiculo AS K on K.id_Veiculo = A.id_Veiculo
left JOIN tbdVeiculo AS L on L.id_Veiculo = A.id_Carreta
left JOIN tbdPessoa AS M on M.id_Pessoa = A.id_Motorista
left JOIN tbdTipoVeiculo AS N on N.id_TipoVeiculo = K.id_TipoVeiculo
WHERE C.id_Movimento>='640000' and (convert(varchar(8), C.dt_Cadastro, 112)>='$data1' $where1 )
UNION
SELECT DISTINCT
Convert(varchar(10), H.dt_PedidoColeta,103) as data
,Convert(varchar(8), H.dt_PedidoColeta,112) as datab
,A.id_Manifesto as manifesto
,rtrim(D.ds_Cliente) as transportadora
,rtrim(Convert(varchar(40), M.ds_Pessoa)) as motorista
,rtrim(K.cd_Placa) as placa
,rtrim(L.cd_Placa) as carreta
,rtrim(N.ds_TipoVeiculo) as veiculo
,'COLETA' as tipo
,rtrim(H.ds_NotaFiscal) as nrNF
,H.vl_NotaFiscal as vlrNF
,'0' as vlrFrete
,'Coleta' as tipomovimento
,'Ped: '+cast(H.id_PedidoColeta as varchar(15)) as documento
,H.qt_Volume as volume
,rtrim(Convert(varchar(35), I.ds_Pessoa)) as cliente
,rtrim(Convert(varchar(35), H.ds_LocalColeta)) as local1
,'' as local2
,rtrim(H.ds_EnderecoColeta) as endereco1
,'' as endereco2
,rtrim(O.ds_Bairro) as bairro1
,'' as bairro2
,rtrim(J.ds_Cidade)+'-'+rtrim(J.UFE_SG) as cidade1
,'' as cidade2
,rtrim(H.cd_CEPColeta) as cep1
,'' as cep2
,rtrim(G1.ds_Ocorrencia) as ocorrencia
,G1.id_Ocorrencia as id_Ocorre
,case H.dt_Coleta
when H.dt_Coleta
then Convert(varchar(10), H.dt_Coleta, 103)
else Convert(varchar(10), B.dt_Servico, 103)
end as data2
,case
when H.hr_Coleta<='24:00' and H.hr_Coleta>='00:00'
then H.hr_Coleta
else B.hr_Chegada
end as hora
,'' as origem
,Convert(varchar(10), H.dt_Limite,103) as limite
,H.tp_Coletada as status1
,H.tp_Liberado as status2
,H.tp_MovimentoVinculado as status3
,case H.dt_Coleta
when H.dt_Coleta
then Convert(varchar(8), H.dt_Coleta, 112)
else Convert(varchar(8), B.dt_Servico, 112)
end as data3
,case H.dt_Coleta
when H.dt_Coleta
then Convert(varchar(8), H.dt_Coleta, 112)
else Convert(varchar(8), A.dt_Saida, 112)
end as data4
,case H.dt_Coleta
when H.dt_Coleta
then Convert(varchar(10), H.dt_Coleta, 103)
else Convert(varchar(10), A.dt_Saida, 103)
end as data4a
,rtrim(H.cm_PedidoColeta) as coment
FROM tbdPedidoColeta AS H
left JOIN tbdConfiguracaoCliente AS Q on Q.id_Pessoa = H.id_Remetente
left JOIN tbdPessoa AS I on I.id_Pessoa = H.id_Remetente
left JOIN tbdPessoa AS O on O.id_Pessoa = H.id_Pessoa
left JOIN tbdCidade AS J on J.id_Cidade = H.id_CidadeColeta
left JOIN tbdManifestoMovimento AS B on B.id_PedidoColeta = H.id_PedidoColeta and B.ds_Tipo='COLETA'
left JOIN tbdManifesto AS A on A.id_Manifesto = B.id_Manifesto
left JOIN tbdOcorrencia AS G1 ON G1.id_Ocorrencia = B.id_Ocorrencia
left JOIN tbdClienteSistema AS D ON D.id_Cliente = A.id_Cliente
left JOIN tbdVeiculo AS K on K.id_Veiculo = A.id_Veiculo
left JOIN tbdVeiculo AS L on L.id_Veiculo = A.id_Carreta
left JOIN tbdPessoa AS M on M.id_Pessoa = A.id_Motorista
left JOIN tbdTipoVeiculo AS N on N.id_TipoVeiculo = K.id_TipoVeiculo
WHERE H.id_PedidoColeta>='220700' and (((convert(varchar(8), H.dt_PedidoColeta, 112) >= '$data3' and tp_Coletada<>'S' and tp_Coletada<>'C') $where2 ) or
((convert(varchar(8), H.dt_PedidoColeta, 112)>='$data1' and tp_Coletada ='S') $where2 ))
ORDER BY tipo ASC, nrNF ASC, documento ASC, data3 ASC, hora ASC ";
$conexao = new Conexao();
$this->mobiles = $conexao->executeSelectQuery($query);
return $this->mobiles;
}
}
?>