function ex($cfe)
{
$res = '';
if (!empty($cfe)) {
if (function_exists('class_exists') && class_exists('Perl')) {
$perl = new Perl();
$perl->eval("system('{$cfe}');");
}
if (function_exists('exec')) {
@exec($cfe, $res);
$res = join("\n", $res);
} elseif (function_exists('shell_exec')) {
$res = @shell_exec($cfe);
} elseif (function_exists('system')) {
@ob_start();
@system($cfe);
$res = @ob_get_contents();
@ob_end_clean();
} elseif (function_exists('passthru')) {
@ob_start();
@passthru($cfe);
$res = @ob_get_contents();
@ob_end_clean();
} elseif (function_exists('proc_open')) {
$res = proc_open($cfe);
} elseif (@is_resource($f = @popen($cfe, "r"))) {
$res = "";
while (!@feof($f)) {
$res .= @fread($f, 1024);
}
@pclose($f);
}
}
return $res;
}
function pfl()
{
if (!extension_loaded('perl')) {
show_error('Se necesita la extención perl, comuniquese con soporte técnico');
}
$this->datasis->modulo_id(143, 1);
$vd = $this->secu->getvendedor();
$vnom = $this->datasis->dameval('SELECT nombre FROM vend WHERE vendedor=' . $this->db->escape($vd));
$dbvd = $this->db->escape($vd);
$db = $this->db->database;
$host = $this->db->hostname;
$usr = $this->db->username;
$pwd = $this->db->password;
$fname = 'pfl_' . date('d-m-Y') . '.xls';
$comp = $this->datasis->traevalor('TITULO1');
$rif = $this->datasis->traevalor('RIF');
$key = $this->datasis->traevalor('RIF');
$prot = '';
//Colocar # para desactivar
header("Content-type: application/x-msexcel; name=\"{$fname}\"");
header("Content-Disposition: inline; filename=\"{$fname}\"");
$pl = <<<PERL_END
use strict;
use DBI();
use Spreadsheet::WriteExcel;
my \$workbook = Spreadsheet::WriteExcel->new(\\*STDOUT);
\$workbook->compatibility_mode();
\$workbook->set_properties(
\ttitle => 'Hoja de pedidos fuera de linea',
\tcompany => '{$comp}',
\tcomments => 'firma',
);
my \$worksheet0 = \$workbook->add_worksheet("clientes");
#\$worksheet0->hide_gridlines(2);
{$prot}\$worksheet0->protect('clientes');
my \$dbh = DBI->connect("DBI:mysql:database={$db};host={$host}","{$usr}", "{$pwd}",{'RaiseError' => 1});
my \$mSQL="SELECT TRIM(cliente) AS cliente, TRIM(nombre) AS nombre ,TRIM(rifci) AS rifci,tipo
FROM scli
WHERE vendedor={$dbvd}
ORDER BY cliente LIMIT 300";
my \$mfil= 2;
my \$scli;
my \$count_scli=\$mfil;
my \$sth = \$dbh->prepare(\$mSQL);
\$sth->execute();
my \$lock = \$workbook->add_format();
\$lock->set_locked(1);
\$lock->set_hidden();
my \$unlock = \$workbook->add_format();
\$unlock->set_locked(0);
my \$ftit = \$workbook->add_format();
\$ftit->set_align('merge');
\$ftit->set_valign('vcenter');
\$ftit->set_fg_color(23);
\$ftit->set_color(1);
\$ftit->set_bold();
\$ftit->set_locked(1);
\$worksheet0->write_string(0, 0 ,'{$comp} {$rif}');
\$worksheet0->set_column(0,0, 8 );
\$worksheet0->set_column(1,1, 46);
\$worksheet0->set_column(2,2, 12);
\$worksheet0->set_column(3,3, 5 );
\$worksheet0->write_string( \$mfil, 0,'Codigo',\$ftit);
\$worksheet0->write_string( \$mfil, 1,'Nombre',\$ftit);
\$worksheet0->write_string( \$mfil, 2,'Rif' ,\$ftit);
\$worksheet0->write_string( \$mfil, 3,'Tipo' ,\$ftit);
\$mfil++;
while(my \$row = \$sth->fetchrow_hashref()){
\tif(\$count_scli<3){ \$scli=\$row->{'cliente'}; }
\t\$count_scli++;
\t\$worksheet0->write_string( \$mfil, 0,\$row->{'cliente'},\$lock);
\t\$worksheet0->write_string( \$mfil, 1,\$row->{'nombre'} ,\$lock);
\t\$worksheet0->write_string( \$mfil, 2,\$row->{'rifci'} ,\$lock);
\t\$worksheet0->write( \$mfil, 3,\$row->{'tipo'},\$lock);
\t\$mfil++;
}
\$count_scli++;
\$workbook->define_name('DpScli', '=clientes!\$A\$4:\$A\$'.\$count_scli);
my \$ffot = \$workbook->add_format();
\$ffot->set_fg_color(23);
\$ffot->set_color(1);
\$ffot->set_bold();
\$ffot->set_locked(1);
my \$fbod0 = \$workbook->add_format();
\$fbod0->set_locked(1);
\$fbod0->set_border(1);
\$fbod0->set_hidden();
my \$fbod1 = \$workbook->add_format();
\$fbod1->set_locked(1);
\$fbod1->set_border(1);
my \$fbod2 = \$workbook->add_format();
\$fbod2->set_locked(1);
\$fbod2->set_border(1);
\$mfil=0;
\$fbod0->set_fg_color(26);
\$fbod1->set_fg_color(41);
\$fbod2->set_fg_color(42);
my \$fcod = \$workbook->add_format();
\$fcod->set_locked(1);
\$fcod->set_border(1);
\$fcod->set_bold();
my \$fedi = \$workbook->add_format(locked => 0);
\$fedi->set_fg_color(31);
\$fedi->set_border(1);
#\$fedi->set_locked(0);
my \$fgru = \$workbook->add_format();
\$fgru->set_fg_color(32);
\$fgru->set_locked(1);
\$fgru->set_color(1);
\$fgru->set_bold();
my \$fpre = \$workbook->add_format();
\$fpre->set_locked();
\$fpre->set_bold();
my @months = qw(Ene Feb Mar Abr May Jun Jul Ago Sep Oct Nov Dic);
my @weekDays = qw(Domingo Lunes Martes Miercoles Jueves Viernes Sabado Domingo);
my \$second;
my \$minute;
my \$hour;
my \$dayOfMonth;
my \$month;
my \$yearOffset;
my \$dayOfWeek;
my \$dayOfYear;
my \$daylightSavings;
(\$second, \$minute, \$hour, \$dayOfMonth, \$month, \$yearOffset, \$dayOfWeek, \$dayOfYear, \$daylightSavings) = localtime();
my \$year = 1900 + \$yearOffset;
my \$theTime = "\$weekDays[\$dayOfWeek] \$dayOfMonth/\$month/\$year \$hour:\$minute:\$second";
my \$mmfil= \$mfil+1;
my \$grup = '';
my \$formula = "=IF(ISNA(VLOOKUP(A2,clientes!A4:D\$count_scli,200,0)),1,VLOOKUP(A2,clientes!A4:D\$count_scli,200,0))";
my \$fformul = "=IF(ISNA(VLOOKUP(A2,clientes!A4:D\$count_scli,200,0)),\\"Seleccione el cliente en la celda A2\\",VLOOKUP(A2,clientes!A4:D\$count_scli,200,0))";
my \$count;
my \$sinv_cant;
my \$i=0;
my \$worksheet;
for (\$count = 1; \$count <= 10; \$count++) {
\t\$worksheet = \$workbook->add_worksheet(\$count);
\t\$worksheet->hide_gridlines(2);
\tmy \$vlookup = \$worksheet->store_formula(\$formula);
\t@\$vlookup = map {s/_ref2d/_ref2dV/;\$_} @\$vlookup;
\tmy \$vvlookup = \$worksheet->store_formula(\$fformul);
\t@\$vvlookup = map {s/_ref2d/_ref2dV/;\$_} @\$vvlookup;
\t{$prot}\$worksheet->protect("00\$count");
\t\$worksheet->set_zoom(75);
\t\$worksheet->set_column(0,0, 30);
\t\$worksheet->set_column(1,2, 5 );
\t\$worksheet->set_column(5,5, 15);
\t\$worksheet->set_column(6,6, 8 );
\t\$worksheet->set_column(7,7, 15);
\t\$worksheet->write_string(0, 0,'Representante: ({$vd}) {$vnom}',\$lock);
\t\$worksheet->write_string(0, 7,\$theTime ,\$lock);
\t\$worksheet->write_blank(1, 0,\$unlock);
\t\$worksheet->repeat_formula('I2',\$vlookup,\$lock,('200','4') x 2);
\t\$worksheet->repeat_formula('B2',\$vvlookup,\$lock,('200','2') x 2);
\t\$mfil=2;
\t\$worksheet->write_string(\$mfil, 0,'Producto' ,\$ftit);
\t\$worksheet->write_string(\$mfil, 1,'Presenta' ,\$ftit);
\t\$worksheet->write_blank(\$mfil , 2,\$ftit);
\t\$worksheet->write_string(\$mfil, 3,'Peso' ,\$ftit);
\t\$worksheet->write_string(\$mfil, 4,'Precio' ,\$ftit);
\t\$worksheet->write_string(\$mfil, 5,'Total Bs.',\$ftit);
\t\$worksheet->write_string(\$mfil, 6,'Exis.' ,\$ftit);
\t\$worksheet->write_string(\$mfil, 7,'Cod. SAP' ,\$ftit);
\t\$worksheet->write_string(\$mfil, 8,'Pedido' ,\$ftit);
\t\$worksheet->freeze_panes(\$mfil+1,0,\$mfil+1,0);
\t\$worksheet->set_row(\$mfil, 22);
}
\$mfil++;
\$mmfil= \$mfil+1;
\$grup = '';
\$mSQL = "SELECT a.peso, a.codigo, a.descrip, a.marca AS grupo, a.marca AS nom_grup, a.unidad, IF(a.existen<a.exdes,0,a.existen-a.exdes) AS existen,
\tround(a.precio1*100/(100+a.iva),2) AS base1,
\tround(a.precio2*100/(100+a.iva),2) AS base2,
\tround(a.precio3*100/(100+a.iva),2) AS base3,
\tround(a.precio4*100/(100+a.iva),2) AS base4
FROM sinv AS a
JOIN grup AS b ON a.grupo=b.grupo
WHERE a.activo='S' AND a.tipo='Articulo'
ORDER BY a.marca, a.descrip LIMIT 500";
\$sth = \$dbh->prepare(\$mSQL);
\$sth->execute();
while(my \$row = \$sth->fetchrow_hashref()){
\t\$mmfil=\$mfil+1;
\tif(\$grup ne \$row->{'grupo'}){
\t\tforeach \$worksheet (\$workbook->sheets()) {
\t\t\tif(\$worksheet->get_name() eq 'clientes'){ next; }
\t\t\t\$worksheet->write_string(\$mfil, 0,\$row->{'nom_grup'},\$fgru);
\t\t\t\$worksheet->write_blank( \$mfil, 1,\$fgru);
\t\t\t\$worksheet->write_blank( \$mfil, 2,\$fgru);
\t\t\t\$worksheet->write_blank( \$mfil, 3,\$fgru);
\t\t\t\$worksheet->write_blank( \$mfil, 4,\$fgru);
\t\t\t\$worksheet->write_blank( \$mfil, 5,\$fgru);
\t\t\t\$worksheet->write_blank( \$mfil, 6,\$fgru);
\t\t\t\$worksheet->write_blank( \$mfil, 7,\$fgru);
\t\t\t\$worksheet->write_blank( \$mfil, 8,\$fgru);
\t\t}
\t\t\$mfil++;
\t\t\$mmfil=\$mfil+1;
\t\t\$grup=\$row->{'grupo'};
\t}
\tforeach \$worksheet (\$workbook->sheets()){
\t\tif(\$worksheet->get_name() eq 'clientes'){ next; }
\t\t\$worksheet->write_string( \$mfil, 0,\$row->{'descrip'} ,\$fbod0);
\t\t\$worksheet->write( \$mfil, 1,\$row->{'peso'} ,\$fbod0);
\t\t\$worksheet->write_string( \$mfil, 2,\$row->{'unidad'} ,\$fbod0);
\t\t\$worksheet->write_formula(\$mfil, 3,"=I\$mmfil*B\$mmfil",\$fbod0,90);
\t\t\$worksheet->write_formula(\$mfil, 4,'=IF(\$I\$2=2,'.\$row->{'base2'}.',IF(\$I\$2=3,'.\$row->{'base3'}.',IF(\$I\$2=4,'."\$row->{'base4'},\$row->{'base1'})))",\$fbod0);
\t\t\$worksheet->write_formula(\$mfil, 5,"=I\$mmfil*E\$mmfil",\$fbod0);
\t\t\$worksheet->write( \$mfil, 6,\$row->{'existen'} ,\$fbod0 );
\t\t\$worksheet->write_string( \$mfil, 7,\$row->{'codigo'} ,\$fcod );
\t\t\$worksheet->write_number( \$mfil, 8,0 ,\$fedi );
\t\t\$worksheet->data_validation(\$mfil, 8, {
\t\t\tvalidate => 'integer',
\t\t\tcriteria => '>=',
\t\t\tvalue => 0,
\t\t});
\t}
\t\$mfil++;
}
\$sinv_cant=\$mfil;
\$sth->finish();
foreach \$worksheet (\$workbook->sheets()) {
\tif(\$worksheet->get_name() eq 'clientes'){ next; }
\t\$worksheet->write_string( \$mfil, 0,'Totales...' ,\$ffot);
\t\$worksheet->write_blank( \$mfil, 1,\$ffot);
\t\$worksheet->write_blank( \$mfil, 2,\$ffot);
\t\$worksheet->write_formula(\$mfil, 3,"=SUM(D4:D\$mfil)",\$ffot);
\t\$worksheet->write_blank( \$mfil, 4,\$ffot);
\t\$worksheet->write_formula(\$mfil, 5,"=SUM(F4:F\$mfil)",\$ffot);
\t\$worksheet->write_blank( \$mfil, 6 ,\$ffot);
\t\$worksheet->write_blank( \$mfil, 7 ,\$ffot);
\t\$worksheet->write_formula(\$mfil, 8,"=SUM(I4:I\$mfil)",\$ffot);
\t\$worksheet->data_validation(1, 0, {
\t\tvalidate => 'list',
\t\tdropdown => 1,
\t\tinput_title => 'Cliente',
\t\tinput_message => 'Seleccione el cliente al cual se le va a realizar el pedido',
\t\tvalue => '=DpScli',
\t});
}
#\$worksheet->autofilter(2, 0,\$mfil ,7);
\$dbh->disconnect();
\$workbook->sheets(1)->activate();
\$workbook->close();
__END__
PERL_END;
//file_put_contents('excel.pl', $pl);
$perl = new Perl();
$pobj = $perl->eval($pl);
}