Example #1
0
/**
 * Export PDF document
 *
 * @param   string  $where  WHERE clause for SQL statement
 */
function xlsexport($WHERE)
{
    global $config, $lang;
    $text_length = 256 - 3;
    // videodb context dir
    $context_dir = preg_replace('/^(.*)\\/.*?$/', '\\1', $_SERVER["SCRIPT_FILENAME"]);
    // array of temp files wich have to be deleted if workbook is closed
    $del_list = array();
    // make shure we have list with extra fields, even if empty
    $extra_fields = array_map('trim', explode(",", $config['xls_extra_fields']));
    // Creating a workbook
    $workbook = new Spreadsheet_Excel_Writer();
    $workbook->setCustomColor(12, 192, 192, 192);
    // Headline
    $workbook->setCustomColor(13, 255, 255, 200);
    // Seen
    $workbook->setCustomColor(14, 255, 220, 220);
    // Lent
    //$workbook->setCustomColor(15, 0,0,0); // Test
    // sending HTTP headers
    $outputFilename = $config['xls_output_filename'] ? $config['xls_output_filename'] : 'VideoDB';
    $workbook->send($outputFilename . '.xls');
    // Creating a worksheet
    $sheetTitle = $config['xls_sheet_title'] ? $config['xls_sheet_title'] : 'VideoDB';
    $worksheet =& $workbook->addWorksheet($sheetTitle);
    // format templates
    $alignLeftFormatNormal =& $workbook->addFormat();
    $alignLeftFormatLent =& $workbook->addFormat(array('Pattern' => 1));
    $alignLeftFormatLent->setFGColor(14);
    $alignRightFormatNormal =& $workbook->addFormat(array('Align' => 'right'));
    $alignRightFormatLent =& $workbook->addFormat(array('Align' => 'right', 'Pattern' => 1));
    $alignRightFormatLent->setFgColor(14);
    $alignCenterFormatNormal =& $workbook->addFormat(array('Align' => 'center'));
    $alignCenterFormatLent =& $workbook->addFormat(array('Align' => 'center', 'Pattern' => 1));
    $alignCenterFormatLent->setFgColor(14);
    $titleFormatNormal =& $workbook->addFormat(array('Bold' => 1));
    $titleFormatUnseen =& $workbook->addFormat(array('Bold' => 1, 'Pattern' => 1));
    $titleFormatUnseen->setFgColor(13);
    $titleFormatLent =& $workbook->addFormat(array('Bold' => 1, 'Pattern' => 1));
    $titleFormatLent->setFgColor(14);
    $plotFormatNormal =& $workbook->addFormat();
    $plotFormatNormal->setTextWrap();
    $plotFormatLent =& $workbook->addFormat(array('Pattern' => 1));
    $plotFormatLent->setTextWrap();
    $plotFormatLent->setFgColor(14);
    $headlineFormat =& $workbook->addFormat(array('Bold' => 1, 'Align' => 'center', 'Pattern' => 1));
    $headlineFormat->setFgColor(12);
    $rowindex = 0;
    $columnindex = 0;
    if ($config['xls_show_headline']) {
        $worksheet->setRow(0, 30);
        $rowindex++;
    }
    // get data (see http://pear.php.net/bugs/bug.php?id=1572)
    $result = iconv_array('utf-8', 'iso-8859-1', exportData($WHERE));
    foreach ($result as $row) {
        $columnindex = 0;
        set_time_limit(300);
        // rise per movie execution timeout limit if safe_mode is not set in php.ini
        if (!empty($row['lentto']) && $config['xls_mark_lent']) {
            $alignLeftFormat = $alignLeftFormatLent;
            $alignCenterFormat = $alignLeftFormatLent;
            $alignRightFormat = $alignLeftFormatLent;
        } else {
            $alignLeftFormat = $alignLeftFormatNormal;
            $alignCenterFormat = $alignLeftFormatNormal;
            $alignRightFormat = $alignLeftFormatNormal;
        }
        $worksheet->setRow($rowindex, 15, $alignLeftFormat);
        foreach ($extra_fields as $field) {
            $isNote = false;
            $walks = 1;
            if (preg_match('/(.+)\\((.+)\\)/', $field, $matches)) {
                $field = trim($matches[1]);
                $note = trim($matches[2]);
                $walks = 2;
            }
            for ($walk = 0; $walk < $walks; $walk++) {
                if ($walk == 1) {
                    $isNote = true;
                    $field = $note;
                    $columnindex--;
                }
                // title
                if ($field == "title") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['title'], $headlineFormat);
                    }
                    $title = $row['title'];
                    if ($row['subtitle']) {
                        $title .= ' - ' . $row['subtitle'];
                    }
                    if ($isNote) {
                        $worksheet->writeNote($rowindex, $columnindex++, $lang['title'] . ":\n" . html_entity_decode($title));
                    } else {
                        if ($row['seen'] == '0' && $config['xls_mark_unseen']) {
                            $format = $titleFormatUnseen;
                        } elseif (!empty($row['lentto']) && $config['xls_mark_lent']) {
                            $format = $titleFormatLent;
                        } else {
                            $format = $titleFormatNormal;
                        }
                        if ($rowindex == 1) {
                            $worksheet->setColumn($columnindex, $columnindex, 50);
                        }
                        $imdb = $row['imdbID'];
                        $link = $imdb ? engineGetContentUrl($imdb, engineGetEngine($imdb)) : '';
                        $worksheet->writeUrl($rowindex, $columnindex, $link, html_entity_decode($title), $format);
                    }
                    $columnindex++;
                } elseif ($field == "plot") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['plot'], $headlineFormat);
                    }
                    if ($isNote) {
                        $worksheet->writeNote($rowindex, $columnindex++, leftString(html_entity_decode($row['plot']), $text_length));
                    } else {
                        if (!empty($row['lentto']) && $config['xls_mark_lent']) {
                            $format = $plotFormatLent;
                        } else {
                            $format = $plotFormatNormal;
                        }
                        if ($rowindex == 1) {
                            $worksheet->setColumn($columnindex, $columnindex, 50);
                        }
                        $worksheet->writeString($rowindex, $columnindex++, leftString(html_entity_decode($row['plot']), $text_length), $format);
                    }
                } elseif ($field == "diskid") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['diskid'], $headlineFormat);
                    }
                    if ($isNote) {
                        $worksheet->writeNote($rowindex, $columnindex++, $lang['diskid'] . ":\n" . html_entity_decode($row['diskid']));
                    } else {
                        if ($rowindex == 1) {
                            $worksheet->setColumn($columnindex, $columnindex, 7);
                        }
                        $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row['diskid']), $alignCenterFormat);
                    }
                } elseif ($field == "language") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['language'], $headlineFormat);
                    }
                    if ($isNote) {
                        $worksheet->writeNote($rowindex, $columnindex++, $lang['language'] . ":\n" . html_entity_decode($row['language']));
                    } else {
                        if ($rowindex == 1) {
                            $worksheet->setColumn($columnindex, $columnindex, 30);
                        }
                        $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row['language']), $alignLeftFormat);
                    }
                } elseif ($field == "mediatype") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['mediatype'], $headlineFormat);
                    }
                    if ($isNote) {
                        $worksheet->writeNote($rowindex, $columnindex++, $lang['mediatype'] . ":\n" . html_entity_decode($row['mediatype']));
                    } else {
                        if ($rowindex == 1) {
                            $worksheet->setColumn($columnindex, $columnindex, 7);
                        }
                        $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row['mediatype']), $alignLeftFormat);
                    }
                } elseif ($field == "genres") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['genres'], $headlineFormat);
                    }
                    if (count($row['genres'])) {
                        $output_genres = array();
                        foreach ($row['genres'] as $genre) {
                            $output_genres[] = html_entity_decode($genre['name']);
                        }
                        if ($isNote) {
                            $worksheet->writeNote($rowindex, $columnindex++, $lang['genres'] . ":\n" . join(", ", $output_genres));
                        } else {
                            if ($rowindex == 1) {
                                $worksheet->setColumn($columnindex, $columnindex, 20);
                            }
                            $worksheet->writeString($rowindex, $columnindex, join(", ", $output_genres), $alignCenterFormat);
                        }
                    }
                    $columnindex++;
                } elseif ($field == "runtime") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['runtime'], $headlineFormat);
                    }
                    if ($row['runtime']) {
                        if ($isNote) {
                            $worksheet->writeNote($rowindex, $columnindex++, $lang['runtime'] . ":\n" . html_entity_decode($row['runtime']) . ' min');
                        } else {
                            if ($rowindex == 1) {
                                $worksheet->setColumn($columnindex, $columnindex, 7);
                            }
                            $worksheet->writeString($rowindex, $columnindex, html_entity_decode($row['runtime']) . ' min', $alignRightFormat);
                        }
                    }
                    $columnindex++;
                } elseif ($field == "year") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['year'], $headlineFormat);
                    }
                    if ($row['year'] != '0000') {
                        if ($isNote) {
                            $worksheet->writeNote($rowindex, $columnindex++, $lang['year'] . ":\n" . html_entity_decode($row['year']));
                        } else {
                            if ($rowindex == 1) {
                                $worksheet->setColumn($columnindex, $columnindex, 7);
                            }
                            $worksheet->writeNumber($rowindex, $columnindex, html_entity_decode($row['year']), $alignCenterFormat);
                        }
                    }
                    $columnindex++;
                } elseif ($field == "owner") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['owner'], $headlineFormat);
                    }
                    if ($isNote) {
                        $worksheet->writeNote($rowindex, $columnindex++, $lang['owner'] . ":\n" . html_entity_decode($row['owner']));
                    } else {
                        if ($rowindex == 1) {
                            $worksheet->setColumn($columnindex, $columnindex, 15);
                        }
                        $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row['owner']), $alignCenterFormat);
                    }
                } elseif ($field == "lent") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['lentto'], $headlineFormat);
                    }
                    if ($isNote) {
                        $worksheet->writeNote($rowindex, $columnindex++, $lang['lentto'] . ":\n" . html_entity_decode($row['lentto']));
                    } else {
                        if ($rowindex == 1) {
                            $worksheet->setColumn($columnindex, $columnindex, 15);
                        }
                        $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row['lentto']), $alignCenterFormat);
                    }
                } elseif ($field == "seen") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['seen'], $headlineFormat);
                    }
                    if ($isNote) {
                        if ($row['seen'] == 1) {
                            $worksheet->writeNote($rowindex, $columnindex++, html_entity_decode($lang['seen']));
                        } else {
                            $columnindex++;
                        }
                    } else {
                        if ($rowindex == 1) {
                            $worksheet->setColumn($columnindex, $columnindex, 2);
                        }
                        if ($row['seen'] == 1) {
                            $worksheet->writeString($rowindex, $columnindex++, "X", $alignCenterFormat);
                        } else {
                            $columnindex++;
                        }
                    }
                } elseif ($field == "insertdate") {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $lang['date'], $headlineFormat);
                    }
                    if ($isNote) {
                        $worksheet->writeNote($rowindex, $columnindex++, $lang['date'] . ":\n" . html_entity_decode(preg_replace('/^([0-9]{4}\\-[0-9]{2}\\-[0-9]{2}).*/', '$1', $row['created'])));
                    } else {
                        if ($rowindex == 1) {
                            $worksheet->setColumn($columnindex, $columnindex, 10);
                        }
                        $worksheet->write($rowindex, $columnindex++, html_entity_decode(preg_replace('/^([0-9]{4}\\-[0-9]{2}\\-[0-9]{2}).*/', '$1', $row['created'])), $alignCenterFormat);
                    }
                } elseif (preg_match("/^custom[0-4]\$/", $field)) {
                    // headline
                    if ($config['xls_show_headline'] && $rowindex == 1 && !$isNote) {
                        $worksheet->writeString(0, $columnindex, $config[$field], $headlineFormat);
                    }
                    //$row[$field] = html_entity_decode($row[$field]);
                    switch ($config[$field . 'type']) {
                        case 'ed2k':
                            if ($isNote) {
                                $worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]));
                            } else {
                                if ($rowindex == 1) {
                                    $worksheet->setColumn($columnindex, $columnindex, 12);
                                }
                                $worksheet->writeUrl($rowindex, $columnindex++, html_entity_decode($row[$field]), 'ED2K-Link', $alignCenterFormat);
                            }
                            break;
                        case 'language':
                            if ($isNote) {
                                $worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]));
                            } else {
                                if ($rowindex == 1) {
                                    $worksheet->setColumn($columnindex, $columnindex, 30);
                                }
                                $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignLeftFormat);
                            }
                            break;
                        case 'rating':
                            if ($row[$field]) {
                                $rating = html_entity_decode($row[$field]) . '/10';
                            } else {
                                $rating = "";
                            }
                            if ($isNote) {
                                if ($row[$field]) {
                                    $worksheet->writeNote($rowindex, $columnindex, $config[$field] . ":\n" . $rating);
                                }
                                $columnindex++;
                            } else {
                                if ($rowindex == 1) {
                                    $worksheet->setColumn($columnindex, $columnindex, 7);
                                }
                                if ($row[$field]) {
                                    $worksheet->writeString($rowindex, $columnindex, $rating, $alignCenterFormat);
                                }
                                $columnindex++;
                            }
                            break;
                        case 'fsk':
                            if (preg_match("/[0-9]+/", $row[$field]) && !preg_match("/[^0-9]+/", $row[$field])) {
                                $fskstr = 'FSK' . html_entity_decode($row[$field]);
                            } else {
                                $fskstr = html_entity_decode($row[$field]);
                            }
                            if ($isNote) {
                                $worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . $fskstr);
                            } else {
                                if ($rowindex == 1) {
                                    $worksheet->setColumn($columnindex, $columnindex, 7);
                                }
                                $worksheet->writeString($rowindex, $columnindex++, $fskstr, $alignCenterFormat);
                            }
                            break;
                        case 'barcode':
                            if ($isNote) {
                                $worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]));
                            } else {
                                if ($rowindex == 1) {
                                    $worksheet->setColumn($columnindex, $columnindex, 15);
                                }
                                $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignCenterFormat);
                            }
                            break;
                        case 'orgtitle':
                            if ($isNote) {
                                if (!empty($row[$field])) {
                                    $worksheet->writeNote($rowindex, $columnindex, $config[$field] . ":\n" . html_entity_decode($row[$field]));
                                }
                                $columnindex++;
                            } else {
                                if ($rowindex == 1) {
                                    $worksheet->setColumn($columnindex, $columnindex, 50);
                                }
                                $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignLeftFormat);
                            }
                            break;
                        case 'movix':
                            if ($isNote) {
                                $worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n " . html_entity_decode($row[$field]));
                            } else {
                                if ($rowindex == 1) {
                                    $worksheet->setColumn($columnindex, $columnindex, 7);
                                }
                                $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignCenterFormat);
                            }
                            break;
                        case 'mpaa':
                            if ($isNote) {
                                $worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]), $alignCenterFormat);
                            } else {
                                if ($rowindex == 1) {
                                    $worksheet->setColumn($columnindex, $columnindex, 12);
                                }
                                $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignCenterFormat);
                            }
                            break;
                        case 'bbfc':
                            if ($isNote) {
                                $worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]));
                            } else {
                                if ($rowindex == 1) {
                                    $worksheet->setColumn($columnindex, $columnindex, 7);
                                }
                                $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignCenterFormat);
                            }
                            break;
                        default:
                            // unknown
                            if ($isNote) {
                                $worksheet->writeNote($rowindex, $columnindex++, $config[$field] . ":\n" . html_entity_decode($row[$field]));
                            } else {
                                $worksheet->writeString($rowindex, $columnindex++, html_entity_decode($row[$field]), $alignLeftFormat);
                            }
                            break;
                    }
                }
            }
            //End of walk
        }
        $rowindex++;
    }
    // Let's send the file
    $workbook->close();
}
Example #2
-1
/**
 * Export PDF document
 *
 * @param   string  $where  WHERE clause for SQL statement
 */
function pdfexport($WHERE)
{
    global $config;
    $ypos = $config['pdf_font_size'];
    // Match the font size for proper vertical offset
    $page_width = $config['pdf_page_width'];
    $margin = $config['pdf_margin'];
    $left_margin = $config['pdf_left_margin'];
    $right_margin = $config['pdf_right_margin'];
    $mediaimg_width = $config['pdf_image_media_width'];
    $font_size = $config['pdf_font_size'];
    $image_height = $config['pdf_image_height'];
    $image_width = $config['pdf_image_width'];
    $font_title = $config['pdf_font_title'];
    $font_plot = $config['pdf_font_plot'];
    $text_length = $config['pdf_text_length'];
    $tempfolder = cache_get_folder('');
    if ($config['cache_pruning']) {
        cache_prune_folder($tempfolder, 3600, false, false, 'videodb*.pdf');
    }
    $filename = $tempfolder . 'videodb' . date('His', time()) . '.pdf';
    // setup pdf class
    $pdf = new PDF();
    $pdf->Open($filename);
    $pdf->VerifyFont($font_title);
    $pdf->VerifyFont($font_title, 'B');
    $pdf->VerifyFont($font_plot);
    $pdf->AddPage();
    $pdf->SetRightMargin($right_margin);
    // add downscaling
    if ($config['pdf_scale']) {
        $pdf->Scale = $config['pdf_scale'];
        $pdf->max_width = $config['pdf_image_max_width'];
        $pdf->max_height = $config['pdf_image_max_height'];
    }
    // get data
    $result = iconv_array('utf-8', 'iso-8859-1', exportData($WHERE));
    foreach ($result as $row) {
        set_time_limit(300);
        // rise per movie execution timeout limit if safe_mode is not set in php.ini
        $title = $row['title'];
        if ($row['subtitle']) {
            $title .= ' - ' . $row['subtitle'];
        }
        if ($row['diskid'] || $row['mediatype']) {
            $title .= ' [';
            if ($row['mediatype']) {
                $title .= $row['mediatype'] . ', ';
            }
            if ($row['diskid']) {
                $title .= $row['diskid'];
            }
            $title = preg_replace('/, $/', '', $title) . ']';
        }
        // get drilldown url for image
        $imdb = $row['imdbID'];
        $link = $imdb ? engineGetContentUrl($imdb, engineGetEngine($imdb)) : '';
        // title
        $pdf->SetFont($font_title, 'B', $font_size);
        $pdf->SetXY($left_margin + $image_width + $margin, $ypos);
        $pdf->Cell(0, 0, $title, 0, 1, 'L', 0, $link);
        // [muddle] technical details
        unset($tech['V']);
        if ($row['video_width'] and $row['video_height']) {
            $vw = $row['video_width'];
            $vh = $row['video_height'];
            $tech['V'] = "Video: ";
            if ($vw > 1920) {
                $tech['V'] .= "UHD " . $vw . "x" . $vh;
            } elseif ($vw > 1280) {
                $tech['V'] .= "HD 1080p";
            } elseif ($vw == 1280 or $vh == 720) {
                $tech['V'] .= "HD 720p";
            } elseif ($vw == 720 or $vw == 704) {
                $tech['V'] .= "SD ";
                if ($vh == 480) {
                    $tech['V'] .= "NTSC";
                } elseif ($vh == 576) {
                    $tech['V'] .= "PAL";
                } else {
                    $tech['V'] .= $vw . "x" . $vh;
                }
            } else {
                $tech['V'] .= "LORES " . $vw . "x" . $vh;
            }
        }
        unset($tech['A']);
        if ($row['audio_codec']) {
            $tech['A'] = "Audio: " . $row['audio_codec'];
        }
        unset($tech['D']);
        if ($row['created']) {
            $tech['D'] = "Date: " . $row['created'];
        }
        $techinfo = implode(", ", $tech);
        $pdf->SetFont($font_title, 'B', $font_size - 3);
        $pdf->SetXY($left_margin + $image_width + $margin, $ypos + 4);
        $pdf->Cell(0, 0, $techinfo, 0, 1, 'L', 0);
        // plot
        $plot = leftString($row['plot'], $text_length);
        $pdf->SetFont($font_plot, '', $font_size - 1);
        $pdf->SetXY($left_margin + $image_width + $margin, $ypos + 3 + 3);
        $pdf->SetLeftMargin($left_margin + $image_width + $margin);
        $pdf->WriteHTML($plot);
        // image
        $file = getThumbnail($row['imgurl']);
        if (preg_match('/^img.php/', $file)) {
            $file = img();
        }
        // image file present?
        if ($file) {
            $pdf->Image($file, $left_margin, $ypos - 2, $image_width, $image_height, '', $link);
        }
        // add mediatype image
        if ($type_image = getMediaImage($row['mediatype'])) {
            $pdf->Image('./images/media/' . $type_image, $page_width - $mediaimg_width - $right_margin, $ypos - 2, $mediaimg_width, 0, '', '');
        }
        // new position
        $ypos += $margin;
        if ($file or $plot) {
            $ypos += max($image_height, $font_size);
        } else {
            $ypos += $font_size;
        }
        if ($ypos > 250) {
            $ypos = $config['pdf_font_size'];
            $pdf->AddPage();
        }
    }
    $pdf->Output('videoDB.pdf', 'D');
    // get rid of temp file
    @unlink($filename);
}