forked from SimplesIP/pabx-app
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
80 lines
3.2 KiB
80 lines
3.2 KiB
#!/usr/bin/php -q |
|
<?php |
|
|
|
/** |
|
* COLUNAS PARA GERAR A LISTA A PARTIR DE UMA QUERY |
|
* |
|
* CGC_CPF -> DOCUMENTO A SER APRESENTADO |
|
* client_tipo_documento -> TIPO DO DOCUMENTO |
|
* RazaoSocial -> RAZAO SOCIAL DA EMPRESA |
|
* Fantasia -> NOME FANTASIA DA EMPRESA |
|
* Endereco |
|
* Cidade |
|
* UF |
|
* TelofoneContato | TelefoneEmpresa | CelularContato -> PODE TRAZER ESSES 3 CAMPOS PARA ADICIONAR O TELEFONE |
|
* Celular |
|
* EmailContato |
|
* NomeContato |
|
*/ |
|
|
|
$query = "SELECT DISTINCT a.ClienteId, a.Fantasia, a.RazaoSocial, 'CNPJ' AS client_tipo_documento,a.CGC_CPF, p.AddedDate as DataProposta, |
|
a.Telefone as TelefoneEmpresa, |
|
b.Cep, b.UF, b.Cidade, b.Bairro, b.Endereco, b.Numero, b.Complemento, c.PrimeiroNome + ' ' + isnull(c.SobreNome, '') as NomeContato, |
|
c.TelefoneComercial as TelofoneContato, c.Celular as CelularContato, c.Email1 as EmailContato |
|
FROM Clientes a |
|
INNER JOIN Propostas p on p.ClienteId = a.ClienteId and p.StatusId = 2 |
|
LEFT JOIN Contatos c on c.ClienteId = a.ClienteId and c.ContatoPrincipal = 1 |
|
LEFT JOIN sip_clientes_enderecos b on b.ClienteId = a.ClienteId |
|
WHERE cast(p.AddedDate as date) > '2019-01-01' |
|
AND p.AddedById not in(95,107) -- select * from Usuarios order by Login |
|
AND a.IsCliente = 0 |
|
AND not exists(select top 10 * from Baixas x, TitulosParcelas y, Titulos z where y.ParcelaId = x.TituloParcelaId and z.TituloId = y.TituloId and z.ClienteId = a.ClienteId ) |
|
AND p.AddedDate = (select max(AddedDate) from Propostas where ClienteId = a.ClienteId) |
|
ORDER BY a.RazaoSocial"; |
|
|
|
GetClientes($query); |
|
|
|
function GetClientes($query) { |
|
$conn = ConectaMssql(); |
|
|
|
if (!$result = mssql_query($query, $conn)) { |
|
return false; |
|
} |
|
|
|
$data = array(); |
|
while ($array = mssql_fetch_array($result)) { |
|
$data[] = $array; |
|
} |
|
|
|
$file = "clientes_" . date('YmdHis') . ".csv"; |
|
foreach ($data as $value) { |
|
$telefone = $value['TelofoneContato'] ? $value['TelofoneContato'] : ($value['TelefoneEmpresa'] ? $value['TelofoneContato'] : $value['CelularContato']); |
|
$razaoSocial = trim($value['RazaoSocial']); |
|
$nomeFantasia = trim($value['Fantasia']); |
|
file_put_contents($file, sprintf("%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s \n", |
|
$value['CGC_CPF'], |
|
$value['client_tipo_documento'], |
|
$razaoSocial, |
|
$nomeFantasia, |
|
$value['Endereco'], |
|
$value['Cidade'], |
|
$value['UF'], |
|
$telefone, |
|
$value['Celular'], |
|
$value['EmailContato'], |
|
$value['NomeContato']), FILE_APPEND); |
|
} |
|
|
|
} |
|
|
|
function ConectaMssql() { |
|
$dbhost = "192.168.115.28"; |
|
$dbname = "VendaMaisDB_simplesip"; |
|
$user = "sa"; |
|
$passwd = "SimpleS_root"; |
|
$conn = mssql_connect($dbhost, $user, $passwd); |
|
mssql_select_db($dbname); |
|
|
|
return $conn; |
|
} |
|
|