Example #1
0
 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;
 }
Example #2
0
    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);
    }