<?php
require_once "../clases/config.php";
require_once '../clases/PHPExcel.php';
$conexion= mysqli_connect(HOST,USER,PASS, DBNAME) or die ("could not connect to mysql");
$conexion->set_charset("utf8");
$vendedor='';
$fini='';
$fterm='';
if(isset($_GET['fini_excel'])){$fini=$_GET['fini_excel'];}
if(isset($_GET['fter_excel'])){$fterm=$_GET['fter_excel'];}
if(isset($_GET['vende_excel'])){$vendedor=$_GET['vende_excel'];}
$buscar ="";
if($vendedor!=""){
$buscar = $buscar."f.id=".$vendedor." AND ";//OK
}
if($fini!='' && $fterm!=''){
$buscar = $buscar."a.f_ingreso_neg BETWEEN DATE('".$fini."') AND DATE('".$fterm."') AND ";//OK
}
if($fini!='' && $fterm==''){
$buscar = $buscar."a.f_ingreso_neg>=DATE('".$fini."') AND ";
}
if($fini=='' && $fterm!=''){
$buscar = $buscar."a.f_termino<=DATE('".$fterm."') AND ";
}
$sql="SELECT CONCAT(YEAR(a.f_ingreso_neg),'-',MONTH(a.f_ingreso_neg)) as mesano,a.id,CONCAT(f.nombre,' ',f.apepat) as vendedor,CONCAT(b.nombre,' ',b.apepat,' ',b.rut) as cliente,a.destino,a.tipo_cuenta,a.tipo_cliente,a.f_ingreso_neg,if(a.tipo_cambio='DOLAR',a.precio*a.valor_tipo_cambio,a.precio) as precio_viaje,e.valor_servicios,c.pago_servicios,d.pago_cliente,g.pago_cliente_contrans,h.cargo_transbank FROM negocios as a LEFT JOIN usuarios as f ON a.id_us_crea=f.id LEFT JOIN clientes as b ON a.id_cliente=b.id LEFT JOIN (SELECT aa.id_servicio,bb.id_negocio as id_negocio ,SUM(if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto)) as pago_servicios FROM pagos_serv as aa RIGHT JOIN servicios_negocios as bb ON aa.id_servicio=bb.id GROUP BY bb.id_negocio) as c ON c.id_negocio = a.id LEFT JOIN (SELECT aa.id_negocio,SUM(if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto)) as pago_cliente FROM pagos_negocio_cli as aa LEFT JOIN negocios as bb ON aa.id_negocio=bb.id GROUP BY aa.id_negocio) as d ON d.id_negocio = a.id LEFT JOIN (SELECT id_negocio,SUM(if(tipo_cambio='DOLAR',valor*valor_tipo_cambio,valor)) as valor_servicios FROM servicios_negocios GROUP BY id_negocio) as e ON e.id_negocio = a.id LEFT JOIN (SELECT aa.id_negocio,SUM(if(aa.n_cuotas='' OR aa.n_cuotas IS NOT NULL, if(aa.tipo_moneda='DOLAR',(aa.total_cuota*aa.n_cuotas)*bb.valor_tipo_cambio,aa.total_cuota*aa.n_cuotas),if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto))) as pago_cliente_contrans FROM pagos_negocio_cli as aa LEFT JOIN negocios as bb ON aa.id_negocio=bb.id GROUP BY aa.id_negocio) as g ON g.id_negocio = a.id LEFT JOIN (SELECT aa.id_negocio,SUM(if(aa.n_cuotas='' OR aa.n_cuotas IS NOT NULL,if(aa.tipo_moneda='DOLAR',aa.cargo_transbank*bb.valor_tipo_cambio,aa.cargo_transbank),'')) as cargo_transbank FROM pagos_negocio_cli as aa LEFT JOIN negocios as bb ON aa.id_negocio=bb.id GROUP BY aa.id_negocio) as h ON h.id_negocio = a.id WHERE ".$buscar." a.id IS NOT NULL GROUP BY a.id ORDER BY a.id DESC;";
//$sql="SELECT CONCAT(YEAR(a.f_ingreso_neg),'-',MONTH(a.f_ingreso_neg)) as mesano,a.id,CONCAT(f.nombre,' ',f.apepat) as vendedor,CONCAT(b.nombre,' ',b.apepat,' ',b.rut) as cliente,a.destino,a.tipo_cuenta,a.tipo_cliente,a.f_ingreso_neg,if(a.tipo_cambio='DOLAR',a.precio*a.valor_tipo_cambio,a.precio) as precio_viaje,e.valor_servicios,c.pago_servicios,d.pago_cliente FROM negocios as a LEFT JOIN usuarios as f ON a.id_us_crea=f.id LEFT JOIN clientes as b ON a.id_cliente=b.id LEFT JOIN (SELECT aa.id_servicio,bb.id_negocio as id_negocio ,SUM(if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto)) as pago_servicios FROM pagos_serv as aa LEFT JOIN servicios_negocios as bb ON aa.id_servicio=bb.id GROUP BY aa.id_servicio) as c ON c.id_negocio = a.id LEFT JOIN (SELECT aa.id_negocio,SUM(if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto)) as pago_cliente FROM pagos_negocio_cli as aa LEFT JOIN negocios as bb ON aa.id_negocio=bb.id GROUP BY aa.id_negocio) as d ON d.id_negocio = a.id LEFT JOIN (SELECT id_negocio,SUM(if(tipo_cambio='DOLAR',valor*valor_tipo_cambio,valor)) as valor_servicios FROM servicios_negocios GROUP BY id_negocio) as e ON e.id_negocio = a.id WHERE ".$buscar." a.id IS NOT NULL GROUP BY a.id ORDER BY a.id DESC;";
//$sql = "SELECT a.f_inicio,a.f_termino,CONCAT(YEAR(a.f_crea),'-',MONTH(a.f_crea)) as mesano,a.id,f.id,f.nombre,f.apepat,b.nombre,b.apepat,b.rut,a.destino,a.tipo_cuenta,a.tipo_cliente,a.f_crea,if(a.tipo_cambio='DOLAR',a.precio*a.valor_tipo_cambio,a.precio) as precio_viaje,e.valor_servicios,c.pago_servicios,d.pago_cliente FROM negocios as a LEFT JOIN usuarios as f ON a.id_us_crea=f.id LEFT JOIN clientes as b ON a.id_cliente=b.id LEFT JOIN (SELECT aa.id_servicio,bb.id_negocio as id_negocio ,SUM(if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto)) as pago_servicios FROM pagos_serv as aa LEFT JOIN servicios_negocios as bb ON aa.id_servicio=bb.id GROUP BY bb.id_negocio) as c ON c.id_negocio = a.id LEFT JOIN (SELECT aa.id_negocio,SUM(if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto)) as pago_cliente FROM pagos_negocio_cli as aa LEFT JOIN negocios as bb ON aa.id_negocio=bb.id GROUP BY aa.id_negocio) as d ON d.id_negocio = a.id LEFT JOIN (SELECT id_negocio,SUM(if(tipo_cambio='DOLAR',valor*valor_tipo_cambio,valor)) as valor_servicios FROM servicios_negocios GROUP BY id_negocio) as e ON e.id_negocio = a.id WHERE ".$buscar." a.id IS NOT NULL GROUP BY a.id ORDER BY a.id DESC;";
$resultado=mysqli_query($conexion, $sql) or die(mysql_error());
$registros = mysqli_num_rows ($resultado);
$fecha=date('d-m-Y');
if ($registros > 0) {
$objPHPExcel = new PHPExcel();
//Informacion del excel
$objPHPExcel->
getProperties()
->setCreator("I-travel Administrador")
->setLastModifiedBy("I-travel Administrador")
->setTitle("UTILIDADES")
->setSubject("Hoja 1")
->setDescription("Documento generado con PHPExcel")
->setKeywords("phpexcel")
->setCategory("utilidades");
$i = 1;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i, 'ID');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, 'FECHA');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$i, 'VENDEDOR');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$i, 'CLIENTE');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$i, 'DESTINO');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$i, 'TIPO CUENTA');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$i, 'TIPO_CLIENTE');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$i, 'PRECIO');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$i, 'TOTAL SERVICIOS');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$i, 'TOTAL PAGO SERVICIOS');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$i, 'TOTAL PAGO CLIENTE');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L'.$i, 'CARGO TRANSBANK');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M'.$i, 'UTILIDAD SISTEMA');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N'.$i, 'UTILIDAD CON CARGO TRANS.');
$i++;
while ($registro = mysqli_fetch_object ($resultado)) {
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i, $registro->nombre);
$utilidad=$registro->precio_viaje-$registro->valor_servicios;
$utilidadcontrans=($registro->precio_viaje-$registro->valor_servicios)-$registro->cargo_transbank;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i, strtoupper($registro->id));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, strtoupper($registro->f_ingreso_neg));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$i, strtoupper($registro->vendedor));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$i, strtoupper($registro->cliente));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$i, strtoupper($registro->destino));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$i, strtoupper($registro->tipo_cuenta));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$i, strtoupper($registro->tipo_cliente));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$i, strtoupper($registro->precio_viaje));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$i, strtoupper($registro->valor_servicios));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$i, strtoupper($registro->pago_servicios));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$i, strtoupper($registro->pago_cliente));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L'.$i, strtoupper($registro->cargo_transbank));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M'.$i, strtoupper($utilidad));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N'.$i, strtoupper($utilidadcontrans));
$i++;
}
}
foreach(range('A','N') as $columnID) {
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID) ->setAutoSize(true);
};
$objPHPExcel->getActiveSheet()->setTitle('DETALLE');
$sql = "";
$sql=$sql." SELECT";
$sql=$sql." CONCAT(YEAR(a.f_ingreso_neg),'-',LPAD(MONTH(a.f_ingreso_neg), 2, '0')) as mesano,";
$sql=$sql." SUM(if(a.tipo_cambio='DOLAR',a.precio*a.valor_tipo_cambio,a.precio)) as precio_viaje,";
$sql=$sql." SUM(e.valor_servicios) as valor_servicios,";
$sql=$sql." SUM(c.pago_servicios) as pago_servicio,";
$sql=$sql." SUM(d.pago_cliente) as pago_cliente,";
$sql=$sql." SUM(g.pago_cliente_contrans) as pago_cliente_con_trans,";
$sql=$sql." SUM(h.cargo_transbank) as cargo_trans";
$sql=$sql." FROM negocios as a";
$sql=$sql." LEFT JOIN usuarios as f ON a.id_us_crea=f.id";
$sql=$sql." LEFT JOIN clientes as b ON a.id_cliente=b.id";
$sql=$sql." LEFT JOIN (SELECT aa.id_servicio,bb.id_negocio as id_negocio ,SUM(if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto)) as pago_servicios FROM pagos_serv as aa";
$sql=$sql." LEFT JOIN servicios_negocios as bb ON aa.id_servicio=bb.id WHERE bb.id_negocio is not null GROUP BY bb.id_negocio) as c ON c.id_negocio = a.id";
$sql=$sql." LEFT JOIN (SELECT aa.id_negocio,SUM(if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto)) as pago_cliente";
$sql=$sql." FROM pagos_negocio_cli as aa LEFT JOIN negocios as bb ON aa.id_negocio=bb.id GROUP BY aa.id_negocio) as d ON d.id_negocio = a.id";
$sql=$sql." LEFT JOIN (SELECT id_negocio,SUM(if(tipo_cambio='DOLAR',valor*valor_tipo_cambio,valor)) as valor_servicios";
$sql=$sql." FROM servicios_negocios GROUP BY id_negocio) as e ON e.id_negocio = a.id";
$sql=$sql." LEFT JOIN (SELECT aa.id_negocio,SUM(if(aa.n_cuotas='' OR aa.n_cuotas IS NOT NULL, if(aa.tipo_moneda='DOLAR',(aa.total_cuota*aa.n_cuotas)*bb.valor_tipo_cambio,aa.total_cuota*aa.n_cuotas),if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto))) as pago_cliente_contrans FROM pagos_negocio_cli as aa LEFT JOIN negocios as bb ON aa.id_negocio=bb.id GROUP BY aa.id_negocio) as g ON g.id_negocio = a.id";
$sql=$sql." LEFT JOIN (SELECT aa.id_negocio,SUM(if(aa.n_cuotas='' OR aa.n_cuotas IS NOT NULL,if(aa.tipo_moneda='DOLAR',aa.cargo_transbank*bb.valor_tipo_cambio,aa.cargo_transbank),'')) as cargo_transbank FROM pagos_negocio_cli as aa LEFT JOIN negocios as bb ON aa.id_negocio=bb.id GROUP BY aa.id_negocio) as h ON h.id_negocio = a.id";
$sql=$sql." WHERE ".$buscar." a.id IS NOT NULL";
$sql=$sql." GROUP BY YEAR(a.f_ingreso_neg),MONTH(a.f_ingreso_neg)";
$sql=$sql." ORDER BY a.f_ingreso_neg DESC;";
//$sql = "SELECT a.f_inicio,a.f_termino,CONCAT(YEAR(a.f_crea),'-',MONTH(a.f_crea)) as mesano,a.id,f.id,f.nombre,f.apepat,b.nombre,b.apepat,b.rut,a.destino,a.tipo_cuenta,a.tipo_cliente,a.f_crea,if(a.tipo_cambio='DOLAR',a.precio*a.valor_tipo_cambio,a.precio) as precio_viaje,e.valor_servicios,c.pago_servicios,d.pago_cliente FROM negocios as a LEFT JOIN usuarios as f ON a.id_us_crea=f.id LEFT JOIN clientes as b ON a.id_cliente=b.id LEFT JOIN (SELECT aa.id_servicio,bb.id_negocio as id_negocio ,SUM(if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto)) as pago_servicios FROM pagos_serv as aa LEFT JOIN servicios_negocios as bb ON aa.id_servicio=bb.id GROUP BY bb.id_negocio) as c ON c.id_negocio = a.id LEFT JOIN (SELECT aa.id_negocio,SUM(if(aa.tipo_moneda='DOLAR',aa.monto*bb.valor_tipo_cambio,aa.monto)) as pago_cliente FROM pagos_negocio_cli as aa LEFT JOIN negocios as bb ON aa.id_negocio=bb.id GROUP BY aa.id_negocio) as d ON d.id_negocio = a.id LEFT JOIN (SELECT id_negocio,SUM(if(tipo_cambio='DOLAR',valor*valor_tipo_cambio,valor)) as valor_servicios FROM servicios_negocios GROUP BY id_negocio) as e ON e.id_negocio = a.id WHERE ".$buscar." a.id IS NOT NULL GROUP BY a.id ORDER BY a.id DESC;";
$resultado=mysqli_query($conexion, $sql) or die(mysql_error());
$registros = mysqli_num_rows ($resultado);
$fecha=date('d-m-Y');
$objPHPExcel->createSheet(1);
if ($registros > 0) {
//Informacion del excel
$objPHPExcel->
getProperties()
->setCreator("I-travel Administrador")
->setLastModifiedBy("I-travel Administrador")
->setTitle("UTILIDADES")
->setSubject("Hoja 2")
->setDescription("Documento generado con PHPExcel")
->setKeywords("phpexcel")
->setCategory("utilidades");
$i = 1;
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$i, 'Fecha');
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('B'.$i, 'PAGOS CLIENTES');
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('C'.$i, 'VALOR SERVICIOS');
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('D'.$i, 'SERVICIOS PAGADOS');
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('E'.$i, 'UTILIDADES SISTEMA');
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('F'.$i, 'CARGO TRANSBANK');
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('G'.$i, 'UTILIDADES CON CARGO TRANSBANK');
$i++;
while ($registro = mysqli_fetch_object ($resultado)) {
$total_utilidades=$registro->precio_viaje-$registro->valor_servicios-$registro->cargo_trans;
$util=$registro->precio_viaje-$registro->valor_servicios;
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A'.$i, strtoupper($registro->mesano));
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('B'.$i, strtoupper($registro->pago_cliente));
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('C'.$i, strtoupper($registro->valor_servicios));
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('D'.$i, strtoupper($registro->pago_servicio));
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('E'.$i, strtoupper($util));
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('F'.$i, strtoupper($registro->cargo_trans));
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('G'.$i, strtoupper($total_utilidades));
$i++;
}
}
foreach(range('A','G') as $columnID) {
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID) ->setAutoSize(true);
};
$objPHPExcel->getActiveSheet()->setTitle('POR RANGO FECHA');
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="utilidades'.$fecha.'.xlsx"');
header('Cache-Control: max-age=0');
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');
$objWriter->save('php://output');
exit;
mysqli_close ();
?>