public function ZoekTijd()
{
require_once "/Classes/databaseHandler.class.php";
$Database = new Database();
require_once "/Classes/calculatetime.php";
$calculatetime = new calculatetime();
$Database->query('SELECT fileName, name FROM wedstrijden WHERE name=?');
$Database->bind(1, $_GET['wedstrijd']);
$resultSet = $Database->single();
if (isset($_GET['startnummer']) || isset($_GET['naam'])) {
include 'Classes/PHPExcel/IOFactory.php';
$inputFileName = 'excel/' . $resultSet['fileName'];
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
die('Er is een fout opgetreden met bestand "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
for ($row = 1; $row <= $highestRow; $row++) {
$rowData[] = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE)[0];
}
if (isset($_GET['startnummer']) && $_GET['startnummer'] == !null) {
$Startnummer = $_GET['startnummer'];
$key = array_search($Startnummer, array_column($rowData, 0));
}
if (isset($_GET['naam']) && $_GET['naam'] == !null) {
$Naam = $_GET['naam'];
$key = array_search($Naam, array_column($rowData, 1));
}
$this->Startnummer = $rowData[$key][0];
$this->Naam = $rowData[$key][1];
$this->Woonplaats = $rowData[$key][2];
$this->Geboortedatum = $rowData[$key][3];
$this->Geslacht = $rowData[$key][4];
$this->Wedstrijdnaam = $resultSet['name'];
$cell = $sheet->getCellByColumnAndRow(5, $key + 1);
$cell_value = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'hh:mm:ss');
$this->VideoTijd = $calculatetime->GetPlayerTime($cell_value);
$_GET['startnummer'] = $this->Startnummer;
}
}
/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null)
{
// Fetch sheet
$sheet = $this->_phpExcel->getSheet($this->_sheetIndex);
// Open file
$fileHandle = fopen($pFilename, 'w');
if ($fileHandle === false) {
throw new Exception("Could not open file {$pFilename} for writing.");
}
// Get cell collection
$cellCollection = $sheet->getCellCollection();
// Get column count
$colCount = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
// Loop trough cells
$currentRow = -1;
$rowData = array();
foreach ($cellCollection as $cell) {
if ($currentRow != $cell->getRow()) {
// End previous row?
if ($currentRow != -1) {
$this->_writeLine($fileHandle, $rowData);
}
// Set current row
$currentRow = $cell->getRow();
// Start a new row
$rowData = array();
for ($i = 0; $i < $colCount; $i++) {
$rowData[$i] = '';
}
}
// Copy cell
$column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
if ($cell->getValue() instanceof PHPExcel_RichText) {
$rowData[$column] = $cell->getValue()->getPlainText();
} else {
if ($this->_preCalculateFormulas) {
$rowData[$column] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $sheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
} else {
$rowData[$column] = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $sheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
}
}
}
// End last row?
if ($currentRow != -1) {
$this->_writeLine($fileHandle, $rowData);
}
// Close file
fclose($fileHandle);
}
public static function convertCellData(\PHPExcel_Cell $cell)
{
$ret = array();
$datatype = $cell->getDataType();
if ($datatype === \excel2sql\Type::EXCEL_NUMERIC) {
$format = $cell->getStyle()->getNumberFormat()->getFormatCode();
if (array_key_exists($format, \excel2sql\Type::$numeric_convert_map)) {
$format = \excel2sql\Type::$numeric_convert_map[$format];
}
$ret['type'] = \excel2sql\Type::$numeric_to_sql_map[$format];
$ret['value'] = \PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), $format);
if ($format === \excel2sql\Type::EXCEL_DATE || $format === \excel2sql\Type::EXCEL_DATETIME || $format === \excel2sql\Type::EXCEL_TIME) {
$ret['value'] = "'{$ret['value']}'";
}
} else {
$ret['type'] = \excel2sql\Type::$excel_to_sql_map[$datatype];
if ($ret['value'] = $cell->getFormattedValue()) {
if ($datatype === \excel2sql\Type::EXCEL_STRING || $datatype === \excel2sql\Type::EXCEL_STRING2) {
$ret['value'] = "\"" . preg_replace("[\"]", "''", $ret['value']) . "\"";
}
} else {
$ret['value'] = "null";
}
}
return $ret;
}
private function formatDataSetLabels($groupID, $datasetLabels, $labelCount, $rotation = '')
{
$datasetLabelFormatCode = $this->chart->getPlotArea()->getPlotGroupByIndex($groupID)->getPlotCategoryByIndex(0)->getFormatCode();
if (!is_null($datasetLabelFormatCode)) {
// Retrieve any label formatting code
$datasetLabelFormatCode = stripslashes($datasetLabelFormatCode);
}
$testCurrentIndex = 0;
foreach ($datasetLabels as $i => $datasetLabel) {
if (is_array($datasetLabel)) {
if ($rotation == 'bar') {
$datasetLabels[$i] = implode(" ", $datasetLabel);
} else {
$datasetLabel = array_reverse($datasetLabel);
$datasetLabels[$i] = implode("\n", $datasetLabel);
}
} else {
// Format labels according to any formatting code
if (!is_null($datasetLabelFormatCode)) {
$datasetLabels[$i] = PHPExcel_Style_NumberFormat::toFormattedString($datasetLabel, $datasetLabelFormatCode);
}
}
++$testCurrentIndex;
}
return $datasetLabels;
}
/**
* PROCESA EL ARCHIVO EXCEL Y LO METE EN LA BASE DE DATOS
*/
public function ProcesaArchivo()
{
$archivo = $_FILES['archivo'];
//COGE EL ARCHIVO
$objPHPExcel = PHPExcel_IOFactory::load($archivo['tmp_name']);
//NOMBRE DEL ARCHIVO TEMPORAL EN EL SISTEMA
$objPHPExcel->setActiveSheetIndex(0);
//COGE EL PANEL 1 DEL EXEL
$numRows = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
//CUENTA LAS FILAS QUE TIENE EL EXCEL
$datos = array();
//ARRAY PARA GUARDAR LOS DATOS
$tipo = "";
//TIPO PARA VER SI ES CATEGORÍA O PRODUCTO
$idCat = 0;
//ID DE CATEGORÍA PARA EL PRODUCTO
for ($i = 1; $i <= $numRows; $i++) {
//POR CADA FILA
$celdaB = $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue();
//CALCULO LA CELDA A$i
$celdaA = $objPHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue();
//CALCULO LA CELDA B$i
if ($celdaB == "") {
//SI LA CELDA B ESTÁ VACÍA ES QUE ES UN TIPO
$tipo = $celdaA;
//SE LE ASIGNA EL TIPO A LA VARIABLE
} else {
//SI TIENE CONTENIDO
if ($celdaA == 'fec_ini' || $celdaA == 'fec_fin') {
//SI ES UNA FECHA, LA CONVIERTO A FORMATO YYYY/MM/DD Y LO METO EN EL ARRAY
$datos[$tipo][$celdaA] = PHPExcel_Style_NumberFormat::toFormattedString($celdaB, 'YYYY/MM/DD');
} else {
// SI NO LO METO EN EL ARRAY SIN MÁS
$datos[$tipo][$celdaA] = $celdaB;
}
}
if ($celdaA == "se_muestra" && $tipo == 'CATEGORIA') {
//SI LA ÚLTIMA CELDA ES se_muestra Y EL TIPO ES LA CATEGORÍA
$idCat = $this->xml->mas_categoria($datos['CATEGORIA']);
//AÑADE LA CATEGORÍA A LA BASE DE DATOS Y DEVUELVE LA ID
}
if ($celdaA == "se_muestra" && $tipo == 'PRODUCTOS') {
//SI LA ÚLTIMA CELDA ES se_muestra Y EL TIPO ES PRODUCTOS
$datos['PRODUCTOS']['Categoria_idCat'] = $idCat;
//AÑADE LA ID DE CATEGORÍA AL ARRAY
$this->xml->mas_productos($datos['PRODUCTOS']);
//METE EL PRODUCTO EN LA BASE DE DATOS
}
}
redirect('/Welcome/index', 'location', 301);
//MUESTRA INICIO
}
public function insertData($data)
{
// EVENT_INFO_ID serial NOT NULL,
// STATUS CHAR(1) DEFAULT '0' NOT NULL,
// EVENT_DATE TIMESTAMP,
// MEDICAL_INSTITION_NO CHAR(3),
// PARTICIPANT_NO NUMERIC(2,0) NOT NULL,
// BARCODE NUMERIC(9,0) NOT NULL,
// MEDICAL_INSTITION VARCHAR(100),
// DEPARTMENT VARCHAR(100),
// POST VARCHAR(100),
// NAME VARCHAR(100),
// TEL_NO1 VARCHAR(20),
// TEL_NO2 VARCHAR(20),
// FAX VARCHAR(20),
// MAIL_ADDRESS VARCHAR(256),
// POSTAL_CODE VARCHAR(10),
// ADDRESS VARCHAR(100),
// REMARKS VARCHAR(100)
$columns = array('event_info_id', 'event_date', 'medical_instition_no', 'medical_instition', 'participant_no', 'department', 'post', 'name', 'tel_no1', 'tel_no2', 'mail_address', 'postal_code', 'address', 'remarks');
foreach ($data as $k1 => $v1) {
// 先頭行はヘッダのため除く
if ($k1 < 4) {
continue;
}
$newData = null;
$this->create();
if (!isset($v1[0]) || $v1[0] == '') {
break;
}
foreach ($v1 as $k2 => $v2) {
if ($k2 >= 14) {
continue;
}
var_dump($v2);
if ('event_info_id' === $columns[$k2]) {
$newData[$columns[$k2]] = intval($v2);
} else {
if ('event_date' === $columns[$k2]) {
$display_date = PHPExcel_Style_NumberFormat::toFormattedString($v2, PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
// $newData[$columns[$k2]] = date('Y-M-D', $display_date);
$newData[$columns[$k2]] = $display_date;
} else {
// $newData[$columns[$k2]] = mb_convert_encoding($v2, "UTF-8", "SJIS");
$newData[$columns[$k2]] = $v2;
}
}
}
$this->save($newData);
}
}
$objPHPExcel = $objReader->load($inputFileName);
$rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
$sheet = $objPHPExcel->getActiveSheet();
$_SESSION['array_data'] = array();
$count = 0;
foreach ($rowIterator as $row) {
$rowIndex = $row->getRowIndex();
$_SESSION['array_data'][$rowIndex] = array('A' => '', 'B' => '', 'C' => '', 'D' => '', 'E' => '', 'F' => '', 'G' => '', 'H' => '', 'I' => '', 'J' => '', 'K' => '', 'L' => '', 'M' => '', 'N' => '', 'O' => '', 'P' => '');
$startA = 'A';
$endP = 'P';
for ($startA; $startA <= $endP; $startA++) {
$cell = $sheet->getCell($startA . $rowIndex);
$_SESSION['array_data'][$rowIndex][$startA] = $cell->getValue();
if ($startA == 'G' || $startA == 'O' || $startA == 'P') {
$cell = $sheet->getCell($startA . $rowIndex);
$_SESSION['array_data'][$rowIndex][$startA] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), 'M-DD-Y');
}
}
}
//print_r($_SESSION['array_data']);
}
?>
<html>
<head>
<title>Philhealth Membership Verification</title>
<style>
#tw-form-outer {
}
#tw-form{
/**
* @param 文件 $file
* @return Exel 内容 array
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
public function importExcel($file)
{
if (!file_exists($file)) {
return array("error" => 0, 'message' => 'file not found!');
}
chmod($file, 0777);
if (!is_readable($file)) {
return array("error" => 0, 'message' => 'file is not readable');
}
Vendor("PHPExcel.IOFactory");
//兼容多种版本的Excel
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$PHPReader = $objReader->load($file);
/* if(!$PHPReader){
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$PHPReader = $objReader->load($file);
if(!$PHPReader){
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$PHPReader = $objReader->load($file);
if(!$PHPReader){
return array("error"=>0,'message'=>'文件格式错误');
}
}
} */
if (!isset($PHPReader)) {
return array("error" => 0, 'message' => 'read error!');
}
$allWorksheets = $PHPReader->getAllSheets();
$i = 0;
foreach ($allWorksheets as $objWorksheet) {
$sheetname = $objWorksheet->getTitle();
$allRow = $objWorksheet->getHighestRow();
//how many rows
$highestColumn = $objWorksheet->getHighestColumn();
//how many columns
$allColumn = PHPExcel_Cell::columnIndexFromString($highestColumn);
$array[$i]["Title"] = $sheetname;
$array[$i]["Cols"] = $allColumn;
$array[$i]["Rows"] = $allRow;
$arr = array();
$isMergeCell = array();
foreach ($objWorksheet->getMergeCells() as $cells) {
//merge cells
foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
$isMergeCell[$cellReference] = true;
}
}
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
$row = array();
for ($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++) {
$cell = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
$afCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn + 1);
$bfCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn - 1);
$col = PHPExcel_Cell::stringFromColumnIndex($currentColumn);
$address = $col . $currentRow;
$value = $objWorksheet->getCell($address)->getValue();
if (substr($value, 0, 1) == '=') {
return array("error" => 0, 'message' => 'can not use the formula!');
exit;
}
if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_NUMERIC) {
$cellstyleformat = $cell->getStyle($cell->getCoordinate())->getNumberFormat();
$formatcode = $cellstyleformat->getFormatCode();
if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $formatcode)) {
$value = gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));
} else {
$value = PHPExcel_Style_NumberFormat::toFormattedString($value, $formatcode);
}
}
if ($isMergeCell[$col . $currentRow] && $isMergeCell[$afCol . $currentRow] && !empty($value)) {
$temp = $value;
} elseif ($isMergeCell[$col . $currentRow] && $isMergeCell[$col . ($currentRow - 1)] && empty($value)) {
$value = $arr[$currentRow - 1][$currentColumn];
} elseif ($isMergeCell[$col . $currentRow] && $isMergeCell[$bfCol . $currentRow] && empty($value)) {
$value = $temp;
}
//$value = iconv( "UTF-8","gb2312", $content);
$row[$currentColumn] = $value;
}
$arr[$currentRow] = $row;
}
$array[$i]["Content"] = $arr;
$i++;
}
spl_autoload_register(array('Think', 'autoload'));
//must, resolve ThinkPHP and PHPExcel conflicts
unset($objWorksheet);
unset($PHPReader);
unset($PHPExcel);
unlink($file);
return array("error" => 1, "data" => $array);
}
/**
* Create array from worksheet
*
* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
* @param boolean $calculateFormulas Should formulas be calculated?
* @param boolean $formatData Should formatting be applied to cell values?
* @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
* True - Return rows and columns indexed by their actual row and column IDs
* @return array
*/
public function toArray($nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
{
// Returnvalue
$returnValue = array();
// Garbage collect...
$this->garbageCollect();
// Loop through rows
$r = -1;
$rowIterator = $this->getRowIterator();
foreach ($rowIterator as $row) {
++$r;
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
// Loop through each cell in the current row
$c = -1;
foreach ($cellIterator as $cell) {
++$c;
$rRef = $returnCellRef ? $cell->getRow() : $r;
$cRef = $returnCellRef ? $cell->getColumn() : $c;
if (!is_null($cell)) {
// Cell exists?
if ($cell->getValue() instanceof PHPExcel_RichText) {
$returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
} else {
if ($calculateFormulas) {
$returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
} else {
$returnValue[$rRef][$cRef] = $cell->getValue();
}
}
if ($formatData) {
$style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
$returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style->getNumberFormat()->getFormatCode());
}
} else {
$returnValue[$rRef][$cRef] = $nullValue;
}
}
}
// Return
return $returnValue;
}
function excel_yukle()
{
$this->load->model('servis_model');
$yeniDosyaAdi = $this->servis_model->dosyaAdiOlustur($ozNetlik = FALSE, $_FILES['excel_dosyasi']['name']);
$ayar = array('upload_path' => 'dosyalar/upload', 'allowed_types' => 'xls|xlsx', 'file_name' => $yeniDosyaAdi);
$this->load->library('upload', $ayar);
if (!$this->upload->do_upload('excel_dosyasi')) {
$veri = array('formHatasi' => $this->upload->display_errors('<div class="formHatasi">', '</div>'), 'gosterilecekSayfa' => 'excel_oku');
$bilgi = array_merge($veri, $this->sistemSabit);
$this->load->view('taslak', $bilgi);
} else {
$this->load->library('Excel');
$this->excel = PHPExcel_IOFactory::load('dosyalar/upload/' . $yeniDosyaAdi);
$sayfa = $this->excel->getSheet(0);
$satirSayisi = $this->excel->setActiveSheetIndex(0)->getHighestRow();
$kolonAdi = $sayfa->getHighestColumn();
echo $this->servis_model->tarih2unix('20.02.2015');
$veri = array();
for ($satir = 2; $satir <= $satirSayisi; $satir++) {
$kolonVeri = array();
$satirData = $sayfa->rangeToArray('B' . $satir . ':' . $kolonAdi . $satir, NULL, TRUE, FALSE);
foreach ($satirData[0] as $kolonNo => $kolonBilgi) {
$kolon = $kolonBilgi;
array_push($kolonVeri, $kolon);
}
$yazVeri = array('is_numarasi' => $this->servis_model->yeniFisNoOlustur(), 'gelis_tarihi' => $this->servis_model->tarih2unix(PHPExcel_Style_NumberFormat::toFormattedString($kolonVeri[0], 'YYYY-MM-DD hh:mm:ss')), 'musteri_adi' => $kolonVeri[1], 'musteri_adresi' => $kolonVeri[2], 'posta_kodu' => $this->servis_model->postaKoduGetir($kolonVeri[4]), 'sehir' => $this->servis_model->plakaGetir($kolonVeri[4]), 'tel' => $kolonVeri[5], 'email' => $kolonVeri[6], 'durum' => 1, 'marka' => 2, 'cihaz_tur' => 11, 'urun_kodu' => strtoupper($kolonVeri[7]), 'urun_adi' => strtoupper($kolonVeri[8]), 'seri_no' => strtoupper($kolonVeri[9]), 'garanti_baslangic' => $this->servis_model->tarih2unix(PHPExcel_Style_NumberFormat::toFormattedString($kolonVeri[10], 'YYYY-MM-DD hh:mm:ss')), 'garanti_belge_turu' => $kolonVeri[11], 'bildirilen_ariza' => $kolonVeri[12], 'ariza_tanimi' => $kolonVeri[13], 'yapilan_islem' => $kolonVeri[14], 'sayac_durumu' => $kolonVeri[15], 'teslim_tarihi' => $this->servis_model->tarih2unix(PHPExcel_Style_NumberFormat::toFormattedString($kolonVeri[16], 'YYYY-MM-DD hh:mm:ss')), 'servis_sekli' => $kolonVeri[17], 'gonderi_sekli' => $kolonVeri[18], 'gonderi_dokuman' => $kolonVeri[19], 'gonderi_ucreti' => 0, 'servis_ucreti' => 0, 'pesinat' => '0.00', 'islemler' => NULL, 'sil' => 0);
$this->servis_model->exceldenVeriKaydet($yazVeri);
}
unlink('./dosyalar/upload/' . $yeni_dosya_adi);
redirect('servis/listele');
}
}
/**
*
*
* @param string $ps_source
* @param array $pa_options
* @return bool
*/
public function nextRow()
{
if (!$this->opo_rows) {
return false;
}
while (true) {
if ($this->opn_current_row > 0) {
$this->opo_rows->next();
}
$this->opn_current_row++;
if (!$this->opo_rows->valid()) {
return false;
}
if ($o_row = $this->opo_rows->current()) {
$this->opa_row_buf = array(null);
$o_cells = $o_row->getCellIterator();
$o_cells->setIterateOnlyExistingCells(false);
$va_row = array();
$vb_val_was_set = false;
$vn_col = 0;
$vn_last_col_set = null;
foreach ($o_cells as $o_cell) {
if (PHPExcel_Shared_Date::isDateTime($o_cell)) {
if (!($vs_val = caGetLocalizedDate(PHPExcel_Shared_Date::ExcelToPHP(trim((string) $o_cell->getValue()))))) {
if (!($vs_val = trim(PHPExcel_Style_NumberFormat::toFormattedString((string) $o_cell->getValue(), 'YYYY-MM-DD')))) {
$vs_val = trim((string) $o_cell->getValue());
}
}
$this->opa_row_buf[] = $vs_val;
} else {
$this->opa_row_buf[] = $vs_val = trim((string) $o_cell->getValue());
}
if (strlen($vs_val) > 0) {
$vb_val_was_set = true;
$vn_last_col_set = $vn_col;
}
$vn_col++;
if ($vn_col > 255) {
break;
}
// max 255 columns; some Excel files have *thousands* of "phantom" columns
}
//if (!$vb_val_was_set) {
//return $this->nextRow();
// continue;
//} // skip completely blank rows
return $o_row;
}
}
return false;
}
function oadueslookup_options()
{
global $wpdb;
$dbprefix = $wpdb->prefix . "oalm_";
$hidden_field_name = 'oalm_submit_hidden';
if (!current_user_can('manage_options')) {
wp_die(__('You do not have sufficient permissions to access this page.'));
}
// =========================
// form processing code here
// =========================
if (isset($_FILES['oalm_file'])) {
#echo "<h3>Processing file upload</h3>";
#echo "<strong>Processing File:</strong> " . esc_html($_FILES['oalm_file']['name']) . "<br>";
#echo "<strong>Type:</strong> " . esc_html($_FILES['oalm_file']['type']) . "<br>";
if (preg_match('/\\.xlsx$/', $_FILES['oalm_file']['name'])) {
/** PHPExcel */
include plugin_dir_path(__FILE__) . 'PHPExcel-1.8.0/Classes/PHPExcel.php';
/** PHPExcel_Writer_Excel2007 */
include plugin_dir_path(__FILE__) . 'PHPExcel-1.8.0/Classes/PHPExcel/Writer/Excel2007.php';
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objReader->setLoadSheetsOnly(array("All"));
$objPHPExcel = $objReader->load($_FILES["oalm_file"]["tmp_name"]);
$objWorksheet = $objPHPExcel->getActiveSheet();
$columnMap = array('BSA ID' => 'bsaid', 'Dues Yr.' => 'max_dues_year', 'Dues Pd. Dt.' => 'dues_paid_date', 'Level' => 'level', 'Reg. Audit Date' => 'reg_audit_date', 'Reg. Audit Result' => 'reg_audit_result');
$complete = 0;
$recordcount = 0;
$error_output = "";
foreach ($objWorksheet->getRowIterator() as $row) {
$rowData = array();
if ($row->getRowIndex() == 1) {
# this is the header row, grab the headings
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(FALSE);
foreach ($cellIterator as $cell) {
$cellValue = $cell->getValue();
if (isset($columnMap[$cellValue])) {
$rowData[$columnMap[$cellValue]] = 1;
#echo "Found column " . htmlspecialchars($cell->getColumn()) . " with title '" . htmlspecialchars($cellValue) . "'<br>" . PHP_EOL;
} else {
#echo "Discarding unknown column " . htmlspecialchars($cell->getColumn()) . " with title '" . htmlspecialchars($cellValue) . "'<br>" . PHP_EOL;
}
}
$missingColumns = array();
foreach ($columnMap as $key => $value) {
if (!isset($rowData[$value])) {
$missingColumns[] = $key;
}
}
if ($missingColumns) {
?>
<div class="error"><p><strong>Import failed.</strong></p><p>Missing required columns: <?php
esc_html_e(implode(", ", $missingColumns));
?>
</div><?php
$complete = 1;
# Don't show "may have failed" box at the bottom
break;
} else {
#echo "<strong>Data format validated:</strong> Importing new data...<br>" . PHP_EOL;
# we just validated that we have a good data file, nuke the existing data
$wpdb->show_errors();
ob_start();
$wpdb->query("TRUNCATE TABLE {$dbprefix}dues_data");
update_option('oadueslookup_last_import', $wpdb->get_var("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')"));
# re-insert the test data
oadueslookup_insert_sample_data();
# now we're ready for the incoming from the rest of the file.
}
} else {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(FALSE);
foreach ($cellIterator as $cell) {
$columnName = $objWorksheet->getCell($cell->getColumn() . "1")->getValue();
$value = "";
if ($columnName == "Dues Pd. Dt.") {
# this is a date field, and we have to work miracles to turn it into a mysql-compatible date
$date = $cell->getValue();
$dateint = intval($date);
$dateintVal = (int) $dateint;
$value = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY-MM-DD");
} else {
if ($columnName == "Reg. Audit Date") {
# this is also a date field, but can be empty
$date = $cell->getValue();
if (!$date) {
$value = get_option('oadueslookup_last_import');
} else {
$dateint = intval($date);
$dateintVal = (int) $dateint;
$value = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY-MM-DD");
}
} else {
$value = $cell->getValue();
}
}
if (isset($columnMap[$columnName])) {
$rowData[$columnMap[$columnName]] = $value;
}
}
if ($wpdb->insert($dbprefix . "dues_data", $rowData, array('%s', '%s', '%s', '%s', '%s'))) {
$recordcount++;
}
}
}
$error_output = ob_get_clean();
if (!$error_output) {
?>
<div class="updated"><p><strong>Import successful. Imported <?php
esc_html_e($recordcount);
?>
records.</strong></p></div><?php
} else {
?>
<div class="error"><p><strong>Import partially successful. Imported <?php
esc_html_e($recordcount);
?>
of <?php
esc_html_e($row->getRowIndex() - 2);
?>
records.</strong></p>
<p>Errors follow:</p>
<?php
echo $error_output;
?>
</div><?php
}
update_option('oadueslookup_last_update', $wpdb->get_var("SELECT DATE_FORMAT(MAX(dues_paid_date), '%Y-%m-%d') FROM {$dbprefix}dues_data"));
} else {
?>
<div class="error"><p><strong>Invalid file upload.</strong> Not an XLSX file.</p></div><?php
}
}
//
// HANDLE SETTINGS SCREEN UPDATES
//
if (isset($_POST[$hidden_field_name]) && $_POST[$hidden_field_name] == 'oadueslookup-settings') {
$slug = $_POST['oadueslookup_slug'];
$dues_url = $_POST['oadueslookup_dues_url'];
$dues_register = $_POST['oadueslookup_dues_register'];
$dues_register_msg = $_POST['oadueslookup_dues_register_msg'];
$update_url = $_POST['oadueslookup_update_url'];
$update_link_text = $_POST['oadueslookup_update_option_link_text'];
$update_option_text = $_POST['oadueslookup_update_option_text'];
$help_email = $_POST['oadueslookup_help_email'];
# $help_email is the only one that throws an error if it doesn't
# validate. The others we just silently fix so they're something
# valid. The user will see the result on the form.
if (!is_email($help_email)) {
?>
<div class="error"><p><strong>'<?php
esc_html_e($help_email);
?>
' is not a valid email address.</strong></p></div><?php
} else {
$foundchanges = 0;
$slug = sanitize_title($slug);
if ($slug != get_option('oadueslookup_slug')) {
update_option('oadueslookup_slug', $slug);
$foundchanges = 1;
}
$dues_url = esc_url_raw($dues_url);
if ($dues_url != get_option('oadueslookup_dues_url')) {
update_option('oadueslookup_dues_url', $dues_url);
$foundchanges = 1;
}
if ($dues_register != get_option('oadueslookup_dues_register')) {
update_option('oadueslookup_dues_register', $dues_register);
$foundchanges = 1;
}
$dues_register_msg = sanitize_text_field($dues_register_msg);
if ($dues_register_msg != get_option('oadueslookup_dues_register_msg')) {
update_option('oadueslookup_dues_register_msg', $dues_register_msg);
$foundchanges = 1;
}
$update_url = esc_url_raw($update_url);
if ($update_url != get_option('oadueslookup_update_url')) {
update_option('oadueslookup_update_url', $update_url);
$foundchanges = 1;
}
$update_link_text = sanitize_text_field($update_link_text);
if ($update_link_text != get_option('oadueslookup_update_option_link_text')) {
update_option('oadueslookup_update_option_link_text', $update_link_text);
$foundchanges = 1;
}
$update_option_text = sanitize_text_field($update_option_text);
if ($update_option_text != get_option('oadueslookup_update_option_text')) {
update_option('oadueslookup_update_option_text', $update_option_text);
$foundchanges = 1;
}
$help_email = sanitize_email($help_email);
if ($help_email != get_option('oadueslookup_help_email')) {
update_option('oadueslookup_help_email', $help_email);
$foundchanges = 1;
}
if ($foundchanges) {
?>
<div class="updated"><p><strong>Changes saved.</strong></p></div><?php
}
}
}
// ============================
// screens and forms start here
// ============================
//
// MAIN SETTINGS SCREEN
//
echo '<div class="wrap">';
// header
echo "<h2>" . __('OA Dues Lookup Settings', 'oadueslookup') . "</h2>";
// settings form
?>
<h3 class="oalm">Import data from OALM</h3>
<p>Export file from OALM Must contain at least the following columns:<br>
BSA ID, Dues Yr., Dues Pd. Dt., Level, Reg. Audit Date, Reg. Audit Result<br>
Any additional columns will be ignored.</p>
<p><a href="http://github.com/justdave/oadueslookup/wiki">How to create the export file in OALM</a></p>
<form action="" method="post" enctype="multipart/form-data">
<label for="oalm_file">Click Browse, then select the xlsx file exported from OALM's "Export Members", then click "Upload":</label><br>
<input type="file" name="oalm_file" id="oalm_file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">
<input type="submit" class="button button-primary" name="submit" value="Upload"><br>
<p><strong>Last import:</strong> <?php
$last_import = get_option('oadueslookup_last_import');
if ($last_import == '1900-01-01') {
echo "Never";
} else {
esc_html_e($last_import);
}
?>
</p>
</form>
<h3 class="oalm">Lookup Page Settings</h3>
<form name="oadueslookup-settings" method="post" action="">
<input type="hidden" name="<?php
echo $hidden_field_name;
?>
" value="oadueslookup-settings">
<table class="form-table">
<tbody>
<tr>
<th scope="row"><label for="oadueslookup_slug">Dues Page Slug</label></th>
<td><code><?php
echo esc_html(get_option("home"));
?>
/</code><input id="oadueslookup_slug" name="oadueslookup_slug" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_slug"));
?>
">
<p class="description">The name appended to your Site URL to reach the lookup page.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_dues_url">Dues Payment URL</label></th>
<td><input id="oadueslookup_dues_url" name="oadueslookup_dues_url" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_dues_url"));
?>
">
<p class="description">The URL to send members to for actually paying their dues.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_dues_register">Registration Required?</label></th>
<td><input id="oadueslookup_dues_register" name="oadueslookup_dues_register" class="code" type="checkbox" value="1"<?php
checked(1 == esc_html(get_option('oadueslookup_dues_register')));
?>
">
<p class="description">Does the dues payment site require the user to register before paying?</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_dues_register_msg">Registration Required Message</label></th>
<td><input id="oadueslookup_dues_register_msg" name="oadueslookup_dues_register_msg" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_dues_register_msg"));
?>
">
<p class="description">The instruction text to display informing the user that they need to register before paying dues.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_update_url">Update Contact Info URL</label></th>
<td><input id="oadueslookup_update_url" name="oadueslookup_update_url" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_update_url"));
?>
">
<p class="description">The URL to send members to for updating their contact information.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_update_option_link_text">Update Contact Link Info Text</label></th>
<td><input id="oadueslookup_update_option_link_text" name="oadueslookup_update_option_link_text" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_update_option_link_text"));
?>
">
<p class="description">The text to appear in the hyperlink to the Update Contact Information URL.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_update_option_text">Section Label</label></th>
<td><input id="oadueslookup_update_option_text" name="oadueslookup_update_option_text" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_update_option_text"));
?>
">
<p class="description">The label or option on the Update Contact Information page to direct the user to.</p>
</td>
</tr>
<tr>
<th scope="row"><label for="oadueslookup_help_email">Help Email</label></th>
<td><input id="oadueslookup_help_email" name="oadueslookup_help_email" class="regular-text code" type="text" value="<?php
echo esc_html(get_option("oadueslookup_help_email"));
?>
">
<p class="description">The email address for members to ask questions.</p>
</td>
</tr>
</tbody>
</table>
<p class="submit"><input id="submit" class="button button-primary" type="submit" value="Save Changes" name="submit"></p>
</form>
<?php
echo "</div>";
}
}
$col++;
}
$top_col = $col - 1;
//
$row = 4;
while ($name = $sheet->getCellByColumnAndRow(1,$row)->getValue())
{
$ncomp++;
//
$country = strict_query("SELECT id FROM countries WHERE name=?", array(($sheet->getCellByColumnAndRow(2,$row)->getValue())));
if (!sql_num_rows($country))
$errors .= "Country name \"".$line[$headers["country"]]."\" not found in database<br>";
else
{
$birthday = PHPExcel_Style_NumberFormat::toFormattedString (floor($sheet->getCellByColumnAndRow(5,$row)->getValue()), "YYYY-MM-DD");
//
$err = addCom(
$sheet->getCellByColumnAndRow(3,$row)->getValue(),
$name,
$birthday,
cased_mysql_result($country,0,"id"),
$sheet->getCellByColumnAndRow(4,$row)->getValue(),
true);
if (is_int($err))
{
$nimp++;
//
for ($col=9;$col<=$top_col;$col++)
if (isset($cats[$col]) && $sheet->getCellByColumnAndRow($col,$row)->getValue().""=="1")
toggleReg($err,$cats[$col]);
/**
* xlsxからサイトマップCSVを出力する。
*/
public function import($path_xlsx, $path_csv)
{
$this->path_xlsx = $path_xlsx;
$this->path_csv = $path_csv;
$path_toppage = '/';
if (strlen($this->px->conf()->path_top)) {
$path_toppage = $this->px->conf()->path_top;
}
$path_toppage = $this->regulize_path($path_toppage);
// サイトマップCSVの定義を取得
$sitemap_definition = $this->get_sitemap_definition();
$phpExcelHelper = $this->plugin->factory_PHPExcelHelper();
if (!$phpExcelHelper) {
return false;
}
set_time_limit(0);
$objPHPExcel = $phpExcelHelper->load($path_xlsx);
$table_definition = $this->parse_definition($objPHPExcel, 0);
//xlsxの構造定義を読み解く
$col_title = array();
foreach ($table_definition['col_define'] as $tmp_col_define) {
if (isset($col_title['start'])) {
$col_title['end'] = @$tmp_col_define['col'];
break;
}
if ($tmp_col_define['key'] == 'title') {
$col_title['start'] = @$tmp_col_define['col'];
}
}
unset($tmp_col_define);
$objPHPExcel->setActiveSheetIndex(0);
$objSheet = $objPHPExcel->getActiveSheet();
// xlsxにあってサイトマップ定義にないカスタムカラムを定義に反映
$xls_custom_column_definition = $table_definition['col_define'];
$tmp_last_elm_info = array();
foreach ($sitemap_definition as $tmp_row) {
unset($xls_custom_column_definition[$tmp_row['key']]);
$tmp_last_elm_info = $tmp_row;
}
foreach ($xls_custom_column_definition as $tmp_key => $tmp_row) {
@$tmp_last_elm_info['num']++;
@$tmp_last_elm_info['col']++;
$tmp_last_elm_info['key'] = $tmp_row['key'];
$tmp_last_elm_info['name'] = $tmp_row['key'];
$sitemap_definition[$tmp_last_elm_info['key']] = $tmp_last_elm_info;
}
$sitemap = array();
$page_info = array();
foreach ($sitemap_definition as $row) {
$page_info[$row['key']] = '* ' . $row['key'];
}
array_push($sitemap, $page_info);
$last_breadcrumb = array();
$last_page_id = null;
$logical_path_last_depth = 0;
$xlsx_row = $table_definition['row_data_start'];
$xlsx_row--;
while (1) {
set_time_limit(30);
$xlsx_row++;
if ($xlsx_row > $table_definition['tbl_highest_row']) {
// エクセルの最終行に達していたら、終了。
break;
}
if ($objSheet->getCell('A' . $xlsx_row)->getCalculatedValue() == 'EndOfData') {
// A列が 'EndOfData' だったら、終了。
break;
}
$page_info = array();
$tmp_page_info = array();
foreach ($sitemap_definition as $key => $row) {
$tmp_col_name = @$table_definition['col_define'][$row['key']]['col'];
if (strlen($tmp_col_name)) {
$tmp_page_info[$row['key']] = $objSheet->getCell($tmp_col_name . $xlsx_row)->getCalculatedValue();
// ユーザーが設定したセルフォーマットに従って文字列を復元する
$cell_format = $objSheet->getStyle($tmp_col_name . $xlsx_row)->getNumberFormat()->getFormatCode();
if ($cell_format !== 'General') {
$tmp_cell_value = \PHPExcel_Style_NumberFormat::toFormattedString($tmp_page_info[$row['key']], $cell_format);
if (!is_null($tmp_cell_value)) {
$tmp_page_info[$row['key']] = $tmp_cell_value;
}
}
unset($cell_format, $tmp_cell_value);
} else {
$tmp_page_info[$row['key']] = '';
}
}
if (@$tmp_page_info['**delete_flg']) {
// 削除フラグ
continue;
}
// タイトルだけ特別
$col_title_col = @$col_title['start'];
$tmp_page_info['title'] = '';
$logical_path_depth = 0;
$alias_title_list = array();
while (@strcmp($col_title_col, $col_title['end'])) {
$tmp_page_info['title'] .= trim($objSheet->getCell($col_title_col . $xlsx_row)->getCalculatedValue());
if (strlen($tmp_page_info['title'])) {
$col_title_col++;
while (strlen($tmp_alias_title = trim($objSheet->getCell($col_title_col . $xlsx_row)->getCalculatedValue()))) {
array_push($alias_title_list, $tmp_alias_title);
$col_title_col++;
}
break;
}
$col_title_col++;
$logical_path_depth++;
}
unset($col_title_col);
unset($tmp_alias_title);
if (!@strlen($tmp_page_info['path'])) {
if (!@strlen($tmp_page_info['title'])) {
// pathもtitleも空白なら終わったものと思う。
// →スキップする に変更
continue;
}
$tmp_page_info['path'] = 'alias:/_tbd.html';
//pathがなくてもtitleがあれば、仮の値を入れて通す。
}
if (!array_key_exists('list_flg', $table_definition['col_define'])) {
// エクセルの定義にlist_flg列がなかったら、
// 全ページにlist_flg=1をセット。
$tmp_page_info['list_flg'] = 1;
}
// 読み込んだパスを正規化
$tmp_page_info['path'] = $this->regulize_path($tmp_page_info['path']);
// 省略されたIDを自動的に付与
if (!strlen($tmp_page_info['id'])) {
if ($path_toppage != $tmp_page_info['path']) {
// トップページは空白でなければならない。
if (preg_match('/^alias\\:/', $tmp_page_info['path'])) {
// エイリアスだったら自動付与
$tmp_page_info['id'] = $this->generate_auto_page_id();
} elseif (count($alias_title_list)) {
// 隠れエイリアスだったら自動付与
$tmp_page_info['id'] = $this->generate_auto_page_id();
}
}
}
// トップページは空白でなければならない。
if ($path_toppage == $tmp_page_info['path']) {
$tmp_page_info['id'] = '';
}
// パンくずも特別
$tmp_breadcrumb = $last_breadcrumb;
if ($logical_path_last_depth === $logical_path_depth) {
// 前回と深さが変わっていなかったら
} elseif ($logical_path_last_depth < $logical_path_depth) {
// 前回の深さより深くなっていたら
$tmp_breadcrumb = $last_breadcrumb;
array_push($tmp_breadcrumb, $last_page_id);
} elseif ($logical_path_last_depth > $logical_path_depth) {
// 前回の深さより浅くなっていたら
$tmp_breadcrumb = array();
for ($i = 0; $i < $logical_path_depth; $i++) {
if (is_null($last_breadcrumb[$i])) {
break;
}
$tmp_breadcrumb[$i] = $last_breadcrumb[$i];
}
}
$tmp_page_info['logical_path'] = '';
if (count($tmp_breadcrumb) >= 2) {
$tmp_page_info['logical_path'] = implode('>', $tmp_breadcrumb);
$tmp_page_info['logical_path'] = preg_replace('/^(.*?)\\>/s', '', $tmp_page_info['logical_path']);
}
// 今回のパンくずとパンくずの深さを記録
$logical_path_last_depth = $logical_path_depth;
$last_breadcrumb = $tmp_breadcrumb;
$last_page_id = $tmp_page_info['path'];
if (preg_match('/^alias\\:/', $tmp_page_info['path'])) {
$last_page_id = $tmp_page_info['id'];
}
$page_info = array();
foreach ($sitemap_definition as $row) {
$page_info[$row['key']] = $tmp_page_info[$row['key']];
}
// サイトマップにページを追加する
if (count($alias_title_list)) {
// エイリアスが省略されている場合
$page_info_base = $page_info;
$page_info['path'] = 'alias:' . $page_info['path'];
array_push($sitemap, $page_info);
$tmp_last_page_id = $page_info['id'];
foreach ($alias_title_list as $key => $row) {
$page_info = $page_info_base;
if (count($alias_title_list) > $key + 1) {
// 最後の1件以外なら
$page_info['path'] = 'alias:' . $page_info['path'];
}
if ($page_info_base['category_top_flg']) {
$page_info['category_top_flg'] = null;
}
array_push($tmp_breadcrumb, $tmp_last_page_id);
$page_info['logical_path'] = '';
if (count($tmp_breadcrumb) >= 2) {
$page_info['logical_path'] = implode('>', $tmp_breadcrumb);
$page_info['logical_path'] = preg_replace('/^(.*?)\\>/s', '', $page_info['logical_path']);
}
$page_info['id'] = $this->generate_auto_page_id();
$page_info['title'] = $row;
// var_dump($page_info['title']);
array_push($sitemap, $page_info);
$tmp_last_page_id = $page_info['id'];
$logical_path_last_depth++;
$last_breadcrumb = $tmp_breadcrumb;
$last_page_id = $tmp_last_page_id;
}
unset($page_info_base);
unset($tmp_last_page_id);
continue;
} else {
// 通常のページの場合
array_push($sitemap, $page_info);
continue;
}
continue;
}
$this->px->fs()->mkdir(dirname($path_csv));
$this->px->fs()->save_file($path_csv, $this->px->fs()->mk_csv($sitemap, array('charset' => 'UTF-8')));
set_time_limit(30);
clearstatcache();
return $this;
}
/**
* Deprecated
*
* @throws PHPExcel_Exception
*/
public function warehouse_unused()
{
$auth = new Auth();
if ($auth->getPermission("warehouse")) {
ini_set("max_execution_time", 900);
ini_set("max_input_time", 60);
ini_set("memory_limit", "256M");
$this->goCake = true;
ob_implicit_flush(true);
@ob_end_flush();
$i = 0;
App::import('Vendor', 'PHPExcel/PHPExcel');
if (!empty($this->request->data)) {
$file_name = $this->request->data['shipping_upload_file']['tmp_name'];
$excelFileObj = PHPExcel_IOFactory::load($file_name);
$worksheet = $excelFileObj->getSheet(0);
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$validated = array();
$returnErrors = array();
$i = 0;
$pure_endicia = false;
for ($row = 1; $row <= $highestRow; $row++) {
$errors = array();
if ($row != 1) {
$rowData = $worksheet->rangeToArray("A{$row}" . ':' . "{$highestColumn}{$row}", NULL, true, false);
//make array easier to use
if ($row == 2 && $rowData[0][0] == 'Account Number') {
$pure_endicia = true;
continue;
}
if ($pure_endicia === true) {
$data = array('order_id' => preg_replace('/[^0-9]/', '', $rowData[0][14]), 'order_shipment_id' => 'LOOKUP', 'tracking_number' => $rowData[0][8], 'carrier' => 'USPS', 'date_created' => null, 'date_shipped' => $rowData[0][3], 'items_not_included' => null);
try {
$order_shipment_id = YouniqueAPI::call("/orders/getOrderShipmentId/" . $rowData[0][14]);
if ($order_shipment_id) {
$data['order_shipment_id'] = $order_shipment_id;
}
} catch (Exception $e) {
error_log('API: failed to get order_shipment_id');
}
} else {
$data = array('order_id' => $rowData[0][0], 'order_shipment_id' => $rowData[0][1], 'tracking_number' => $rowData[0][2], 'carrier' => $rowData[0][3], 'date_created' => $rowData[0][4], 'date_shipped' => $rowData[0][5], 'items_not_included' => $rowData[0][6]);
}
$split = array();
//validation rules
//need all fields
if (empty($data['order_id']) || empty($data['order_shipment_id']) || empty($data['tracking_number']) || empty($data['carrier']) || empty($data['date_shipped'])) {
$data['error'] = "Row {$row} is missing data";
$errors[] = $data;
}
//order id must be int or - separated ints or int-REP
if (!is_numeric($data['order_id'])) {
//it may be a list
$order_ids = explode('-', $data['order_id']);
foreach ($order_ids as $value) {
if (!is_numeric($value)) {
if (strtoupper($value) != "REP" || strtoupper($value) != "BO") {
$data['error'] = "Row {$row} has invalid order id(s)";
$errors[] = $data;
}
} else {
//split it
$split[] = $value;
}
}
}
//order shipment id must be int
if (!is_numeric($data['order_shipment_id'])) {
$data['error'] = "Row {$row} has invalid order shipment id";
$errors[] = $data;
}
//tracking number must be int or 'int'
if (!ctype_alnum($data['tracking_number'])) {
if (!ctype_alnum(str_replace("'", '', $data['tracking_number']))) {
$data['error'] = "Row {$row} has invalid tracking number";
$errors[] = $data;
} else {
$data['tracking_number'] = str_replace("'", '', $data['tracking_number']);
}
}
//carrier must be "USPS" or "Landmark Global"
if ($data['carrier'] != "USPS" && $data['carrier'] != "Landmark Global") {
$data['error'] = "Row {$row} has invalid carrier";
$errors[] = $data;
}
//date created is optional but must be a valid date
//in the excel format or in valid php date format
if (is_numeric($data['date_created'])) {
//excel format
$date = PHPExcel_Style_NumberFormat::toFormattedString($data['date_created'], "YYYY-MM-DD h:mm:ss");
if (!DateTime::createFromFormat('G-m-d H:i:s', $date)) {
$date_created = DateTime::createFromFormat('Y-m-d H:i:s', $date);
$data['date_created'] = $date_created->format("Y-m-d H:i:s");
} else {
$data['date_created'] = '';
}
} else {
$validate_date = date_parse($data['date_created']);
if (empty($validate_date['errors'])) {
$date = new DateTime($data['date_created']);
if ($date) {
$date_created = date_format($date, "Y-m-d H:i:s");
$data['date_created'] = $date_created;
} else {
$data['date_created'] = '';
}
}
}
//date shipped must be a valid date in the excel format or in valid php date format
if (is_numeric($data['date_shipped'])) {
//excel format
$date = PHPExcel_Style_NumberFormat::toFormattedString($data['date_shipped'], "YYYY-MM-DD h:mm:ss");
if (!DateTime::createFromFormat('G-m-d H:i:s', $date)) {
$date_shipped = DateTime::createFromFormat('Y-m-d H:i:s', $date);
$data['date_shipped'] = $date_shipped->format("Y-m-d H:i:s");
} else {
$data['error'] = "Row {$row} has invalid date";
$errors[] = $data;
}
} else {
$validate_date = date_parse($data['date_shipped']);
if (empty($validate_date['errors'])) {
$date = new DateTime($data['date_shipped']);
if ($date) {
$date_shipped = date_format($date, "Y-m-d H:i:s");
$data['date_shipped'] = $date_shipped;
} else {
$data['error'] = "Row {$row} has invalid date";
$errors[] = $data;
}
} else {
$data['error'] = "Row {$row} has invalid date";
$errors[] = $data;
}
}
if (!array_key_exists("error", $data)) {
$data['row'] = $row;
if (!empty($split)) {
foreach ($split as $value) {
$data['order_id'] = $value;
//need to get the right order shipment id
try {
$result = YouniqueAPI::call("/orders/getOrderShipmentId/" . $value);
if ($result) {
$data['order_shipment_id'] = $result;
$validated[] = $data;
}
} catch (Exception $e) {
error_log("API failed");
}
}
} else {
$validated[] = $data;
}
} else {
//consolidate errors
foreach ($errors as $error) {
$returnErrors[$row][] = $error['error'];
}
}
}
if ($row % 100 == 0) {
echo "<pre>{$row} Records Validated.</pre>";
}
}
echo "<pre>{$row} Total Records Validated. </pre>";
// chunk the array - this is necessary as there are php limits on
// how many variables can be sent to the api controlled by max_input_vars ini setting
// if the number of elements sent change, you may have to adjust
// the chunk size.
// the default limit is 1000 variables. the array currently has 7 elements, so chunk
// size of 100 should be safe
$chunks = array_chunk($validated, 100);
//process validated rows
//send to api
foreach ($chunks as $value) {
try {
$result = YouniqueAPI::call("/admin/orders/shipmentpackages/", $value);
if ($result) {
$i += $result->count;
}
echo "<pre>{$i} Records Processed. </pre>";
} catch (Exception $e) {
$returnErrors[] = (array) $result->errors;
}
}
//process errors
if (!empty($returnErrors)) {
echo "Errors:<br />";
foreach ($returnErrors as $errorValue) {
echo '<pre>';
foreach ($errorValue as $error) {
echo "{$error}" . "<br />";
}
echo '</pre>';
}
}
echo "<pre>Total Orders processed: " . $i . "</pre>";
}
ob_implicit_flush(false);
} else {
echo "Login Required";
}
}
/**
* Write row to HTML file
*
* @param mixed $pFileHandle PHP filehandle
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number
* @throws Exception
*/
private function _writeRow($pFileHandle = null, PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (!is_null($pFileHandle) && is_array($pValues)) {
// Write row start
fwrite($pFileHandle, ' <tr class="row' . $pRow . '">' . "\r\n");
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
$cellData = ' ';
$cssClass = 'column' . $colNum;
$colSpan = 1;
$rowSpan = 1;
$writeCell = true;
// Write cell
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop trough rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">';
}
$cellData .= $element->getText();
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
}
}
// Check value
if ($cellData == '') {
$cellData = ' ';
}
// Extend CSS class?
if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) {
$cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashCode();
}
} else {
$cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null);
}
// Hyperlink?
if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
$cellData = '<a href="' . $cell->getHyperlink()->getUrl() . '" title="' . $cell->getHyperlink()->getTooltip() . '">' . $cellData . '</a>';
}
// Column/rowspan
foreach ($pSheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first == $cell->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
} else {
$writeCell = false;
}
break;
}
}
// Write
if ($writeCell) {
// Column start
fwrite($pFileHandle, ' <td');
fwrite($pFileHandle, ' class="' . $cssClass . '"');
if ($colSpan > 1) {
fwrite($pFileHandle, ' colspan="' . $colSpan . '"');
}
if ($rowSpan > 1) {
fwrite($pFileHandle, ' rowspan="' . $rowSpan . '"');
}
fwrite($pFileHandle, '>');
// Image?
$this->_writeImageInCell($pFileHandle, $pSheet, $cell->getCoordinate());
// Cell data
fwrite($pFileHandle, $cellData);
// Column end
fwrite($pFileHandle, '</td>' . "\r\n");
}
// Next column
$colNum++;
}
// Write row end
fwrite($pFileHandle, ' </tr>' . "\r\n");
} else {
throw new Exception("Invalid parameters passed.");
}
}
/**
* Create array from a range of cells
*
* @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
* @param boolean $calculateFormulas Should formulas be calculated?
* @param boolean $formatData Should formatting be applied to cell values?
* @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
* True - Return rows and columns indexed by their actual row and column IDs
* @return array
*/
public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
{
// Returnvalue
$returnValue = array();
// Identify the range that we need to extract from the worksheet
list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange);
$minCol = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] - 1);
$minRow = $rangeStart[1];
$maxCol = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0] - 1);
$maxRow = $rangeEnd[1];
$maxCol++;
// Loop through rows
$r = -1;
for ($row = $minRow; $row <= $maxRow; ++$row) {
$rRef = $returnCellRef ? $row : ++$r;
$c = -1;
// Loop through columns in the current row
for ($col = $minCol; $col != $maxCol; ++$col) {
$cRef = $returnCellRef ? $col : ++$c;
// Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
// so we test and retrieve directly against _cellCollection
if ($this->_cellCollection->isDataSet($col . $row)) {
// Cell exists
$cell = $this->_cellCollection->getCacheData($col . $row);
if ($cell->getValue() !== null) {
if ($cell->getValue() instanceof PHPExcel_RichText) {
$returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
} else {
if ($calculateFormulas) {
$returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
} else {
$returnValue[$rRef][$cRef] = $cell->getValue();
}
}
if ($formatData) {
$style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
$returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style && $style->getNumberFormat() ? $style->getNumberFormat()->getFormatCode() : PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
}
} else {
// Cell holds a NULL
$returnValue[$rRef][$cRef] = $nullValue;
}
} else {
// Cell doesn't exist
$returnValue[$rRef][$cRef] = $nullValue;
}
}
}
// Return
return $returnValue;
}
/**
* Generate row
*
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number (0-based)
* @return string
* @throws PHPExcel_Writer_Exception
*/
private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (is_array($pValues)) {
// Construct HTML
$html = '';
// Sheet index
$sheetIndex = $pSheet->getParent()->getIndex($pSheet);
// DomPDF and breaks
if ($this->_isPdf && count($pSheet->getBreaks()) > 0) {
$breaks = $pSheet->getBreaks();
// check if a break is needed before this row
if (isset($breaks['A' . $pRow])) {
// close table: </table>
$html .= $this->_generateTableFooter();
// insert page break
$html .= '<div style="page-break-before:always" />';
// open table again: <table> + <col> etc.
$html .= $this->_generateTableHeader($pSheet);
}
}
// Write row start
if (!$this->_useInlineCss) {
$html .= ' <tr class="row' . $pRow . '">' . PHP_EOL;
} else {
$style = isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) ? $this->_assembleCSS($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) : '';
$html .= ' <tr style="' . $style . '">' . PHP_EOL;
}
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
$coordinate = PHPExcel_Cell::stringFromColumnIndex($colNum) . ($pRow + 1);
if (!$this->_useInlineCss) {
$cssClass = '';
$cssClass = 'column' . $colNum;
} else {
$cssClass = array();
if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum])) {
$this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum];
}
}
$colSpan = 1;
$rowSpan = 1;
// initialize
$cellData = ' ';
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
$cellData = '';
if (is_null($cell->getParent())) {
$cell->attach($pSheet);
}
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop through rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' . $this->_assembleCSS($this->_createCSSStyleFont($element->getFont())) . '">';
if ($element->getFont()->getSuperScript()) {
$cellData .= '<sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '<sub>';
}
}
}
// Convert UTF8 data to PCDATA
$cellText = $element->getText();
$cellData .= htmlspecialchars($cellText);
if ($element instanceof PHPExcel_RichText_Run) {
if ($element->getFont()->getSuperScript()) {
$cellData .= '</sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '</sub>';
}
}
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(), array($this, 'formatColor'));
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(), array($this, 'formatColor'));
}
$cellData = htmlspecialchars($cellData);
if ($pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont()->getSuperScript()) {
$cellData = '<sup>' . $cellData . '</sup>';
} elseif ($pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont()->getSubScript()) {
$cellData = '<sub>' . $cellData . '</sub>';
}
}
// Converts the cell content so that spaces occuring at beginning of each new line are replaced by
// Example: " Hello\n to the world" is converted to " Hello\n to the world"
$cellData = preg_replace("/(?m)(?:^|\\G) /", ' ', $cellData);
// convert newline "\n" to '<br>'
$cellData = nl2br($cellData);
// Extend CSS class?
if (!$this->_useInlineCss) {
$cssClass .= ' style' . $cell->getXfIndex();
$cssClass .= ' ' . $cell->getDataType();
} else {
if (isset($this->_cssStyles['td.style' . $cell->getXfIndex()])) {
$cssClass = array_merge($cssClass, $this->_cssStyles['td.style' . $cell->getXfIndex()]);
}
// General horizontal alignment: Actual horizontal alignment depends on dataType
$sharedStyle = $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex());
if ($sharedStyle->getAlignment()->getHorizontal() == PHPExcel_Style_Alignment::HORIZONTAL_GENERAL && isset($this->_cssStyles['.' . $cell->getDataType()]['text-align'])) {
$cssClass['text-align'] = $this->_cssStyles['.' . $cell->getDataType()]['text-align'];
}
}
}
// Hyperlink?
if ($pSheet->hyperlinkExists($coordinate) && !$pSheet->getHyperlink($coordinate)->isInternal()) {
$cellData = '<a href="' . htmlspecialchars($pSheet->getHyperlink($coordinate)->getUrl()) . '" title="' . htmlspecialchars($pSheet->getHyperlink($coordinate)->getTooltip()) . '">' . $cellData . '</a>';
}
// Should the cell be written or is it swallowed by a rowspan or colspan?
$writeCell = !(isset($this->_isSpannedCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]) && $this->_isSpannedCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]);
// Colspan and Rowspan
$colspan = 1;
$rowspan = 1;
if (isset($this->_isBaseCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum])) {
$spans = $this->_isBaseCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum];
$rowSpan = $spans['rowspan'];
$colSpan = $spans['colspan'];
// Also apply style from last cell in merge to fix borders -
// relies on !important for non-none border declarations in _createCSSStyleBorder
$endCellCoord = PHPExcel_Cell::stringFromColumnIndex($colNum + $colSpan - 1) . ($pRow + $rowSpan);
$cssClass .= ' style' . $pSheet->getCell($endCellCoord)->getXfIndex();
}
// Write
if ($writeCell) {
// Column start
$html .= ' <td';
if (!$this->_useInlineCss) {
$html .= ' class="' . $cssClass . '"';
} else {
//** Necessary redundant code for the sake of PHPExcel_Writer_PDF **
// We must explicitly write the width of the <td> element because TCPDF
// does not recognize e.g. <col style="width:42pt">
$width = 0;
$i = $colNum - 1;
$e = $colNum + $colSpan - 1;
while ($i++ < $e) {
if (isset($this->_columnWidths[$sheetIndex][$i])) {
$width += $this->_columnWidths[$sheetIndex][$i];
}
}
$cssClass['width'] = $width . 'pt';
// We must also explicitly write the height of the <td> element because TCPDF
// does not recognize e.g. <tr style="height:50pt">
if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'])) {
$height = $this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'];
$cssClass['height'] = $height;
}
//** end of redundant code **
$html .= ' style="' . $this->_assembleCSS($cssClass) . '"';
}
if ($colSpan > 1) {
$html .= ' colspan="' . $colSpan . '"';
}
if ($rowSpan > 1) {
$html .= ' rowspan="' . $rowSpan . '"';
}
$html .= '>';
// Image?
$html .= $this->_writeImageInCell($pSheet, $coordinate);
// Chart?
if ($this->_includeCharts) {
$html .= $this->_writeChartInCell($pSheet, $coordinate);
}
// Cell data
$html .= $cellData;
// Column end
$html .= '</td>' . PHP_EOL;
}
// Next column
++$colNum;
}
// Write row end
$html .= ' </tr>' . PHP_EOL;
// Return
return $html;
} else {
throw new PHPExcel_Writer_Exception("Invalid parameters passed.");
}
}
public function actionUploadexcel($fkperson, $idbp24, $name)
{
$uploadfile = "uploads/" . $name . "" . ".xlsx";
try {
$inputfiletype = \PHPExcel_IOFactory::identify($uploadfile);
$objreader = \PHPExcel_IOFactory::createReader($inputfiletype);
$objPHPExcel = $objreader->load($uploadfile);
} catch (Exception $e) {
die('Error');
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
for ($row = 1; $row <= $highestRow; $row++) {
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
if ($row == 1) {
continue;
}
$model = new \app\models\Monitoringbp24data();
$time = $rowData[0][0];
$model->time = \PHPExcel_Style_NumberFormat::toFormattedString($time, 'hh:mm:ss');
$model->SBPbr = $rowData[0][1];
$model->dia = $rowData[0][2];
$model->pulse = $rowData[0][3];
$model->SBPao = $rowData[0][4];
$model->AIXao = $rowData[0][5];
$model->AIXbr = $rowData[0][6];
$model->PWVao = $rowData[0][7];
$model->PWVsd = $rowData[0][8];
$model->date_creation = date("Y-m-d");
$model->fk_person = $fkperson;
$model->fk_id24h = $idbp24;
$model->altered = 1;
$model->save(FALSE);
}
if (file_exists($uploadfile)) {
unlink($uploadfile);
}
Yii::$app->session->setFlash('success', 'Records saved and excel uploaded successfully');
return $this->redirect(["bp24h/update/{$idbp24}"]);
}
/**
* Get cell value with formatting
*
* @return string
*/
public function getFormattedValue()
{
return (string) PHPExcel_Style_NumberFormat::toFormattedString($this->getCalculatedValue(), $this->_parent->getParent()->getCellXfByIndex($this->getXfIndex())->getNumberFormat()->getFormatCode());
}
/**
* Create array from worksheet
*
* @param mixed $nullValue Value treated as "null"
* @param boolean $calculateFormulas Should formulas be calculated?
* @return array
*/
public function toArray($nullValue = null, $calculateFormulas = true)
{
// Returnvalue
$returnValue = array();
// Garbage collect...
$this->garbageCollect();
// Get worksheet dimension
$dimension = explode(':', $this->calculateWorksheetDimension());
$dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]);
$dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1;
$dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]);
$dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1;
// Loop trough cells
for ($row = $dimension[0][1]; $row <= $dimension[1][1]; $row++) {
for ($column = $dimension[0][0]; $column <= $dimension[1][0]; $column++) {
// Cell exists?
if ($this->cellExistsByColumnAndRow($column, $row)) {
$cell = $this->getCellByColumnAndRow($column, $row);
if ($cell->getValue() instanceof PHPExcel_RichText) {
$returnValue[$row][$column] = $cell->getValue()->getPlainText();
} else {
if ($calculateFormulas) {
$returnValue[$row][$column] = $cell->getCalculatedValue();
} else {
$returnValue[$row][$column] = $cell->getValue();
}
}
$style = $this->getDefaultStyle();
if (isset($this->_styles[$cell->getCoordinate()])) {
$style = $this->getStyleByColumnAndRow($column, $row);
}
$returnValue[$row][$column] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$row][$column], $style->getNumberFormat()->getFormatCode());
} else {
$returnValue[$row][$column] = $nullValue;
}
}
}
// Return
return $returnValue;
}
function getSonucExcel($post, $get, $files)
{
$_db = JFactory::getOracleDBO();
$user =& JFactory::getUser();
$user_id = $user->getOracleUserId();
/////// EKLENEN FILENAME BIZDE KAYITLIMI DEGILMI ////////////////////7
// $filename = $files['upload']['name'];
//
// $paktetBul = explode('_', $filename);
// $paket_Id = explode('.',$paktetBul[2]);
$paket_Id = $post['paketId'];
// if(!file_exists(EK_FOLDER.'sinav_bildirimleri/'.$filename) || $paktetBul[0] != $user_id || $paktetBul[1] != $get['sinav']){
// $return['hataMesaji'][1] = 'Sonuç bildirimi gönderdiğiniz dosya sistemimizde yer almamaktadır. Lütfen sistemden indirdiğiniz dosyanın adını değiştirmeden yollayınız.';
// return $return;
// }
////////////////////////////////////////////////////////////////////777
if ($files['upload']['size'] > 0) {
if ($files['upload']['type'] == 'application/vnd.ms-excel') {
// $objPHPExcel = PHPExcel_IOFactory::load($files['upload']['tmp_name']);
$uzanti = "xls";
$excel = new Spreadsheet_Excel_Reader();
$excel->setOutputEncoding('windows-1254');
$excel->read($files['upload']['tmp_name']);
foreach ($excel->sheets[0]['cells'][1] as $key => $val) {
$cols[$key] = $val;
}
$keySql = array('1' => 'TC_KIMLIK', 'UYRUK', 'ADI', 'SOYADI', 'DOGUM_TARIHI', 'CINSIYETI', 'EGITIMI', 'CALISMA_DURUMU', 'EMAIL', 'TELEFON', 'IBAN', 'YETERLILIK_KODU', 'BIRIM_KODU', 'SINAV_TURU_KODU', 'SINAV_TARIHI', 'SINAV_SAATI', 'SINAV_YERI_ID', 'DEGERLENDIRICI_TC_KIMLIK', 'PUAN', 'BASARI_DURUMU');
$bossatir = 0;
$x = 4;
for ($i = 4; $i <= count($excel->sheets[0]['cells']); $i++) {
for ($y = 1; $y <= 20; $y++) {
if (!isset($excel->sheets[0]['cells'][$i][$y])) {
$excel->sheets[0]['cells'][$i][$y] = "";
}
}
foreach ($excel->sheets[0]['cells'][$i] as $key => $val) {
$data[$x][$keySql[$key]] = mb_convert_encoding($val, "UTF-8", "windows-1254");
if ($cols[$key] == "dogumtarihi" && $data[$x][$keySql[$key]] != "") {
$timestamp = trim($data[$x][$keySql[$key]]);
$mysqlDate = date('d/m/Y', strtotime('1899-12-31+' . ($timestamp - 1) . ' days'));
if (strpos($timestamp, ".") || strpos($timestamp, "/")) {
$data[$x][$keySql[$key]] = str_replace('.', '/', $timestamp);
} else {
$data[$x][$keySql[$key]] = $mysqlDate;
}
} else {
if ($cols[$key] == "sinavtarihi" && $data[$x][$keySql[$key]] != "") {
$timestamp = trim($data[$x][$keySql[$key]]);
$mysqlDate = date('d/m/Y', strtotime('1899-12-31+' . ($timestamp - 1) . ' days'));
if (strpos($timestamp, ".") || strpos($timestamp, "/")) {
$data[$x][$keySql[$key]] = str_replace('.', '/', $timestamp);
} else {
$data[$x][$keySql[$key]] = $mysqlDate;
}
} else {
if ($cols[$key] == "tckn" && $data[$x][$keySql[$key]] == '') {
$bossatir++;
break;
} else {
if ($data[$x][$keySql[$key]] != '') {
$data[$x][$keySql[$key]] = trim($data[$x][$keySql[$key]]);
}
}
}
}
}
$x++;
if ($bossatir > 0) {
break;
}
}
} else {
if ($files['upload']['type'] == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' || $files['upload']['type'] == "application/octet-stream") {
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($files['upload']['tmp_name']);
$uzanti = "xlsx";
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$data = array();
for ($col = 0; $col <= $highestColumnIndex - 1; ++$col) {
$key[] = $objWorksheet->getCellByColumnAndRow($col, 1)->getValue();
}
$keySql = array('0' => 'TC_KIMLIK', 'UYRUK', 'ADI', 'SOYADI', 'DOGUM_TARIHI', 'CINSIYETI', 'EGITIMI', 'CALISMA_DURUMU', 'YETERLILIK_KODU', 'BIRIM_KODU', 'SINAV_TURU_KODU', 'SINAV_TARIHI', 'SINAV_SAATI', 'SINAV_YERI_ID', 'DEGERLENDIRICI_TC_KIMLIK', 'PUAN', 'BASARI_DURUMU');
$bossatir = 0;
for ($row = 4; $row <= $highestRow; ++$row) {
for ($col = 0; $col <= $highestColumnIndex - 1; ++$col) {
if ($key[$col] == "dogumtarihi") {
if ($objWorksheet->getCellByColumnAndRow($col, $row)->getValue() != '') {
$timestamp = trim($objWorksheet->getCellByColumnAndRow($col, $row)->getValue());
$mysqlDate = date('d/m/Y', strtotime('1899-12-31+' . ($timestamp - 1) . ' days'));
if (strpos($timestamp, ".") || strpos($timestamp, "/")) {
$data[$row][$keySql[$col]] = str_replace('.', '/', $timestamp);
} else {
$data[$row][$keySql[$col]] = $mysqlDate;
}
}
} else {
if ($key[$col] == "sinavtarihi") {
if ($objWorksheet->getCellByColumnAndRow($col, $row)->getValue() != '') {
$timestamp = trim($objWorksheet->getCellByColumnAndRow($col, $row)->getValue());
$mysqlDate = date('d/m/Y', strtotime('1899-12-31+' . ($timestamp - 1) . ' days'));
if (strpos($timestamp, ".") || strpos($timestamp, "/")) {
$data[$row][$keySql[$col]] = str_replace('.', '/', $timestamp);
} else {
$data[$row][$keySql[$col]] = $mysqlDate;
}
}
} else {
if ($key[$col] == "sinavsaati") {
if ($objWorksheet->getCellByColumnAndRow($col, $row)->getValue() != '') {
$mysqlDate = PHPExcel_Style_NumberFormat::toFormattedString(trim($objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue()), 'hh:mm');
$data[$row][$keySql[$col]] = $mysqlDate;
}
} else {
if ($key[$col] == "tckn" && $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() == '') {
$bossatir++;
break;
} else {
if ($objWorksheet->getCellByColumnAndRow($col, $row)->getValue() != '') {
$data[$row][$keySql[$col]] = trim($objWorksheet->getCellByColumnAndRow($col, $row)->getValue());
}
}
}
}
}
}
if ($bossatir > 0) {
break;
}
}
} else {
print 'dosya formatı yanlış';
exit;
}
}
$sql = "select yeterlilik_id from m_belgelendirme_sinav where sinav_id=" . $get["sinav"];
$yeterlilik_id = $_db->prep_exec($sql, array());
$yeterlilik_id = $yeterlilik_id[0]["YETERLILIK_ID"];
$sinavYerleri = $this->sinavYeriKontrol($user_id, $yeterlilik_id);
$degerlendiriciler = $this->sinavDegerlendiriciKontrol($user_id, $yeterlilik_id);
$keyArray = array();
$dataTest = array();
$dataTest = $data;
$tcArray = array();
foreach ($dataTest as $key => $cow) {
$keyArray[] = $key;
unset($dataTest[$key]['PUAN']);
unset($dataTest[$key]['BASARI_DURUMU']);
unset($dataTest[$key]['SINAV_TARIHI']);
unset($dataTest[$key]['SINAV_SAATI']);
unset($dataTest[$key]['YETERLILIK_KODU']);
unset($dataTest[$key]['UYRUK']);
unset($dataTest[$key]['DEGERLENDIRICI_TC_KIMLIK']);
settype($dataTest[$key]['TC_KIMLIK'], "string");
$dataTest[$key]['SINAV_YERI_ID'] = (int) $dataTest[$key]['SINAV_YERI_ID'];
$dataTest[$key]['BIRIM_KODU'] = ucwords(trim(str_replace(' ', '', $dataTest[$key]['BIRIM_KODU'])));
$dataTest[$key]['SINAV_TURU_KODU'] = ucwords(trim(str_replace(' ', '', $dataTest[$key]['SINAV_TURU_KODU'])));
if (isset($dataTest[$key]['IBAN'])) {
//ALT+255 BOŞLUĞU YAKALAR
$dataTest[$key]['IBAN'] = str_replace(array(' ', chr(0xc2) . chr(0xa0)), array('', ''), $dataTest[$key]['IBAN']);
}
if (isset($dataTest[$key]['TELEFON'])) {
$dataTest[$key]['TELEFON'] = str_replace(array(' ', '(', ')', '-', '+90'), array('', '', '', '', ''), $dataTest[$key]['TELEFON']);
if (substr($dataTest[$key]['TELEFON'], 0, 1) == "0") {
$dataTest[$key]['TELEFON'] = substr($dataTest[$key]['TELEFON'], 1);
}
}
if (!in_array($dataTest[$key]['TC_KIMLIK'], $tcArray)) {
$tcArray[] = $dataTest[$key]['TC_KIMLIK'];
$dataTest[$key]['ADI'] = FormFactory::ucWordsTR($dataTest[$key]['ADI']);
$dataTest[$key]['SOYADI'] = FormFactory::toUpperCase($dataTest[$key]['SOYADI']);
$cinsiyet = FormFactory::toUpperCase($dataTest[$key]['CINSIYETI']);
if ($cinsiyet == 'ERKEK') {
$dataTest[$key]['CINSIYETI'] = 1;
$data[$key]['CINSIYETI'] = 1;
} else {
if ($cinsiyet == 'KADIN') {
$dataTest[$key]['CINSIYETI'] = 2;
$data[$key]['CINSIYETI'] = 2;
} else {
if ($cinsiyet == 'BELİRTİLMEMİŞ') {
$dataTest[$key]['CINSIYETI'] = 3;
$data[$key]['CINSIYETI'] = 3;
}
}
}
$egitimm = FormFactory::toUpperCase(trim(str_replace(' ', '', $dataTest[$key]['EGITIMI'])));
if ($egitimm == 'OKURYAZARDEĞİL') {
$dataTest[$key]['EGITIMI'] = 1;
$data[$key]['EGITIMI'] = 1;
} else {
if ($egitimm == 'OKURYAZAR') {
$dataTest[$key]['EGITIMI'] = 2;
$data[$key]['EGITIMI'] = 2;
} else {
if ($egitimm == 'İLKOKUL') {
$dataTest[$key]['EGITIMI'] = 3;
$data[$key]['EGITIMI'] = 3;
} else {
if ($egitimm == 'ORTAOKUL') {
$dataTest[$key]['EGITIMI'] = 4;
$data[$key]['EGITIMI'] = 4;
} else {
if ($egitimm == 'MESLEKLİSESİ') {
$dataTest[$key]['EGITIMI'] = 5;
$data[$key]['EGITIMI'] = 5;
} else {
if ($egitimm == 'GENELLİSE') {
$dataTest[$key]['EGITIMI'] = 6;
$data[$key]['EGITIMI'] = 6;
} else {
if ($egitimm == 'MESLEKYÜKSEKOKULU') {
$dataTest[$key]['EGITIMI'] = 7;
$data[$key]['EGITIMI'] = 7;
} else {
if ($egitimm == 'LİSANS') {
$dataTest[$key]['EGITIMI'] = 8;
$data[$key]['EGITIMI'] = 8;
} else {
if ($egitimm == 'YÜKSEKLİSANS') {
$dataTest[$key]['EGITIMI'] = 9;
$data[$key]['EGITIMI'] = 9;
} else {
if ($egitimm == 'DOKTORA') {
$dataTest[$key]['EGITIMI'] = 10;
$data[$key]['EGITIMI'] = 10;
}
}
}
}
}
}
}
}
}
}
$calismaDurum = FormFactory::toUpperCase(trim(str_replace(' ', '', $dataTest[$key]['CALISMA_DURUMU'])));
if ($calismaDurum == 'ÇALIŞIYOR') {
$dataTest[$key]['CALISMA_DURUMU'] = 1;
$data[$key]['CALISMA_DURUMU'] = 1;
} else {
if ($calismaDurum == 'ÇALIŞMIYOR') {
$dataTest[$key]['CALISMA_DURUMU'] = 2;
$data[$key]['CALISMA_DURUMU'] = 2;
} else {
if ($calismaDurum == 'STAJYAPIYOR') {
$dataTest[$key]['CALISMA_DURUMU'] = 3;
$data[$key]['CALISMA_DURUMU'] = 3;
}
}
}
} else {
unset($dataTest[$key]['ADI']);
unset($dataTest[$key]['SOYADI']);
unset($dataTest[$key]['DOGUM_TARIHI']);
unset($dataTest[$key]['CINSIYETI']);
unset($dataTest[$key]['EGITIMI']);
unset($dataTest[$key]['CALISMA_DURUMU']);
}
}
//array_splice($data[4], 5) ilk 5 degerden sonrasını siliyor. 5'degerden sonrasını sana döndürüyor.
$sql = "select * from m_yeterlilik where yeterlilik_id=" . $yeterlilik_id;
$yeterlilik = $_db->prep_exec($sql, array());
$yeni_mi = $yeterlilik[0]["YENI_MI"];
//Eski excel ve yeni excel format uyuşmazlıkları için eklendi
$sinavTarihi = $this->getSinavTarihi($get['sinav']);
$sql_add = "";
if (strtotime(str_replace('/', '-', $sinavTarihi)) > strtotime(str_replace('/', '-', '20-07-2015'))) {
$sql_add = ",M_BELGELENDIRME_OGRENCI.TELEFON,M_BELGELENDIRME_OGRENCI.EMAIL,M_BELGELENDIRME_OGRENCI.IBAN";
}
if ($yeni_mi == 1) {
$sql = "SELECT DISTINCT M_BELGELENDIRME_OGRENCI.TC_KIMLIK, M_BELGELENDIRME_OGRENCI.ADI, M_BELGELENDIRME_OGRENCI.SOYADI, M_BELGELENDIRME_OGRENCI.DOGUM_TARIHI, \n M_BELGELENDIRME_OGRENCI.CINSIYETI, M_BELGELENDIRME_OGRENCI.EGITIMI,\n M_BELGELENDIRME_OGRENCI.CALISMA_DURUMU,\n \t\t\t\t\t\tM_BIRIM.BIRIM_KODU, M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_TURU_KODU,\n M_BELGELENDIRME_ADAY_BILDIRIM.BILDIRIM_ID,M_BELGELENDIRME_ADAY_BILDIRIM.BIRIM_ID,M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_TURU_KODU,\n M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_YERI_ID, M_BELGELENDIRME_ADAY_BILDIRIM.DEGERLENDIRICI_TC_KIMLIK" . $sql_add . "\n FROM M_BELGELENDIRME_OGRENCI\n JOIN M_BELGELENDIRME_ADAY_BILDIRIM ON M_BELGELENDIRME_OGRENCI.TC_KIMLIK = M_BELGELENDIRME_ADAY_BILDIRIM.TC_KIMLIK\n JOIN M_BIRIM ON M_BELGELENDIRME_ADAY_BILDIRIM.BIRIM_ID = M_BIRIM.BIRIM_ID\n WHERE M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_ID = ? AND PAKET_ID=?\n ORDER BY M_BELGELENDIRME_ADAY_BILDIRIM.BILDIRIM_ID";
} else {
$sql = "SELECT DISTINCT M_BELGELENDIRME_OGRENCI.TC_KIMLIK, M_BELGELENDIRME_OGRENCI.ADI, M_BELGELENDIRME_OGRENCI.SOYADI, M_BELGELENDIRME_OGRENCI.DOGUM_TARIHI, \n M_BELGELENDIRME_OGRENCI.CINSIYETI, M_BELGELENDIRME_OGRENCI.EGITIMI,\n M_BELGELENDIRME_OGRENCI.CALISMA_DURUMU,\n \t\t\t\t\t\tM_YETERLILIK_ALT_BIRIM.YETERLILIK_ALT_BIRIM_NO AS BIRIM_KODU, M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_TURU_KODU,\n M_BELGELENDIRME_ADAY_BILDIRIM.BILDIRIM_ID,M_BELGELENDIRME_ADAY_BILDIRIM.BIRIM_ID,M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_TURU_KODU,\n M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_YERI_ID, M_BELGELENDIRME_ADAY_BILDIRIM.DEGERLENDIRICI_TC_KIMLIK" . $sql_add . "\n FROM M_BELGELENDIRME_OGRENCI\n JOIN M_BELGELENDIRME_ADAY_BILDIRIM ON M_BELGELENDIRME_OGRENCI.TC_KIMLIK = M_BELGELENDIRME_ADAY_BILDIRIM.TC_KIMLIK\n JOIN M_YETERLILIK_ALT_BIRIM ON M_BELGELENDIRME_ADAY_BILDIRIM.BIRIM_ID = M_YETERLILIK_ALT_BIRIM.YETERLILIK_ALT_BIRIM_ID\n WHERE M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_ID = ? AND PAKET_ID=?\n ORDER BY M_BELGELENDIRME_ADAY_BILDIRIM.BILDIRIM_ID";
}
$kayitliaday = $_db->prep_exec($sql, array($get['sinav'], $paket_Id));
$adayBirims = array();
$adaylar = array();
$tcArray = array();
foreach ($kayitliaday as $key => $row) {
$adayBirims[$keyArray[$key]][] = $kayitliaday[$key]['BIRIM_ID'];
$adayBirims[$keyArray[$key]][] = $kayitliaday[$key]['SINAV_TURU_KODU'];
unset($kayitliaday[$key]['BIRIM_ID']);
unset($kayitliaday[$key]['BILDIRIM_ID']);
unset($kayitliaday[$key]['DEGERLENDIRICI_TC_KIMLIK']);
if ($yeni_mi == 1) {
$kayitliaday[$key]['BIRIM_KODU'] = trim($kayitliaday[$key]['BIRIM_KODU']);
} else {
$kayitliaday[$key]['BIRIM_KODU'] = trim($yeterlilik[0]['YETERLILIK_KODU']) . '/' . trim($kayitliaday[$key]['BIRIM_KODU']);
}
$kayitliaday[$key]['BIRIM_KODU'] = str_replace(" ", "", $kayitliaday[$key]['BIRIM_KODU']);
$kayitliaday[$key]['SINAV_YERI_ID'] = (int) $kayitliaday[$key]['SINAV_YERI_ID'];
if (!in_array($kayitliaday[$key]['TC_KIMLIK'], $tcArray)) {
$tcArray[] = $kayitliaday[$key]['TC_KIMLIK'];
$kayitliaday[$key]['CINSIYETI'] = (int) $kayitliaday[$key]['CINSIYETI'];
$kayitliaday[$key]['EGITIMI'] = (int) $kayitliaday[$key]['EGITIMI'];
$kayitliaday[$key]['CALISMA_DURUMU'] = (int) $kayitliaday[$key]['CALISMA_DURUMU'];
} else {
unset($kayitliaday[$key]['ADI']);
unset($kayitliaday[$key]['SOYADI']);
unset($kayitliaday[$key]['DOGUM_TARIHI']);
unset($kayitliaday[$key]['CINSIYETI']);
unset($kayitliaday[$key]['EGITIMI']);
unset($kayitliaday[$key]['CALISMA_DURUMU']);
}
$adaylar[$keyArray[$key]] = $kayitliaday[$key];
}
$hataArray = array();
for ($i = 4; $i < count($dataTest) + 4; $i++) {
if ($adaylar[$i] != $dataTest[$i]) {
$hatalikolonlar = "";
for ($j = 0; $j < count($keySql); $j++) {
if ($adaylar[$i][$keySql[$j]] != $dataTest[$i][$keySql[$j]]) {
if ($keySql[$j] == "EMAIL" and $dataTest[$i][$keySql[$j]] == "" or $keySql[$j] == "TELEFON" and $dataTest[$i][$keySql[$j]] == "" or $keySql[$j] == "IBAN" and $dataTest[$i][$keySql[$j]] == "") {
} else {
$hatalikolonlar .= $keySql[$j] . " ";
}
}
}
if ($hatalikolonlar != "") {
$return["adayBilgisi"][] = $i . " (" . $hatalikolonlar . ") ";
}
}
}
if (count($return["adayBilgisi"]) == 0) {
$sinavTarihi = $this->getSinavTarihi($get['sinav']);
$sinavYerleri = $this->sinavYeriKontrol($user_id, $yeterlilik_id);
$degerlendiriciler = $this->sinavDegerlendiriciKontrol($user_id, $yeterlilik_id);
foreach ($data as $key => $satir) {
////////////// EXCEL'DEN GELEN SINAV TARİHİ KONTROLÜ ///////////////
if (strtotime($sinavTarihi) > strtotime(ereg_replace("/", "-", $satir["SINAV_TARIHI"]))) {
$return["hataMesaji"][4] = "Aşağıdaki satırlarda verilen Sınav Tarihleri, Sınav Programınızda belirttiğiniz Başlangıç Tarihinden (" . $sinavTarihi . ") öncedir. Düzeltip dosyanızı yeniden yükleyiniz.";
$return["sinavTarihi"][] = array($key, $satir["SINAV_TARIHI"]);
}
////////////// EXCEL'DEN GELEN SINAV TARİHİ 360 GÜN KONTROLÜ ///////////////
if (strtotime('+360 days', strtotime($sinavTarihi)) < strtotime(ereg_replace("/", "-", $satir["SINAV_TARIHI"]))) {
$return["hataMesaji"][2] = "Aşağıdaki satırlarda verilen Sınav Tarihleri, Sınav Programınızda belirttiğiniz Başlangıç Tarihinden (" . $sinavTarihi . ") 1 yıl içinde olmalıdır. Düzeltip dosyanızı yeniden yükleyiniz.";
$return["sinavTarihi360"][] = array($key, $satir["SINAV_TARIHI"]);
}
////////////// EXCEL'DEN GELEN SINAV YERİ KONTROLÜ ///////////////
// if (!in_array($satir["SINAV_YERI_ID"], $sinavYerleri)){
// $return["hataMesaji"][5]="Aşağıdaki satırlarda Verdiğiniz Sınav Yeri ID'leri, <a href='index.php?option=com_belgelendirme&view=belgelendirme_islemleri&layout=belgelendirme_sinav_yeri' style='color:red;'>Sınav Yerleriniz</a> arasında yoktur. Düzeltip dosyanızı yeniden yükleyiniz.";
// $return["sinavYeri"][]=array($key,$satir["SINAV_YERI_ID"]);
// }
////////////// EXCEL'DEN GELEN DEĞERLENDİRİCİLERİN KONTROLÜ ///////////////
$degTcKimliks = explode(",", $satir["DEGERLENDIRICI_TC_KIMLIK"]);
foreach ($degTcKimliks as $degTcKimlik) {
if (!in_array(trim($degTcKimlik), $degerlendiriciler)) {
$return["hataMesaji"][6] = "Aşağıdaki satırlarda verdiğiniz Değerlendirici/Gözetmen'ler, <a href='index.php?option=com_belgelendirme&view=belgelendirme_islemleri&layout=belgelendirme_degerlendirici' style='color:red;'>Değerlendiriciler</a> arasında yoktur. Düzeltip dosyanızı yeniden yükleyiniz.";
$return["degerlendirici"][] = array($key, $degTcKimlik);
}
}
////////////// EXCEL'DEN GELEN BASARI VE SONUC KONTROLÜ ///////////////
if ($satir["BASARI_DURUMU"] == '' || empty($satir["BASARI_DURUMU"]) || !isset($satir["BASARI_DURUMU"])) {
$return["hataMesaji"][7] = "Aşağıdaki satırlarda Sonuç bildirilmemiştir. Düzeltip dosyanızı yeniden yükleyiniz.";
$return["sonuc"][] = $key;
} else {
$sonuc = 0;
if (FormFactory::toUpperCase($satir["BASARI_DURUMU"]) == 'BAŞARILI') {
if ($satir['PUAN'] == '' || empty($satir["PUAN"]) || !isset($satir["PUAN"])) {
$return["hataMesaji"][8] = "Aşağıdaki satırlarda Sonuç bildirilmesine rağmen Puan bildirilmemiştir. Düzeltip dosyanızı yeniden yükleyiniz.";
$return["puan"][] = $key;
} else {
$adaySonuc[$key] = 1;
}
} else {
if (FormFactory::toUpperCase($satir["BASARI_DURUMU"]) == 'BAŞARISIZ') {
if ($satir['PUAN'] == '' || empty($satir["PUAN"]) || !isset($satir["PUAN"])) {
$satir['PUAN'] = 0;
$adaySonuc[$key] = 2;
} else {
$adaySonuc[$key] = 2;
}
} else {
if (FormFactory::toUpperCase($satir["BASARI_DURUMU"]) == 'GİRMEDİ') {
$adaySonuc[$key] = 3;
}
}
}
}
///////////// EXCEL'DEN GELEN VERIYI KAYDETME ////////////////
$sql = "UPDATE M_BELGELENDIRME_ADAY_BILDIRIM SET PUAN=?, BASARI_DURUMU = ?, DEGERLENDIRICI_TC_KIMLIK = ? WHERE TC_KIMLIK = ? AND SINAV_ID=? AND BIRIM_ID=? AND SINAV_TURU_KODU=? AND PAKET_ID=?";
$param = array($satir['PUAN'], $adaySonuc[$key], $satir['DEGERLENDIRICI_TC_KIMLIK'], $satir['TC_KIMLIK'], $get['sinav'], $adayBirims[$key][0], $adayBirims[$key][1], $paket_Id);
$durum = $_db->prep_exec_insert($sql, $param);
}
if (count($return) == 0) {
$sql = "DELETE FROM M_BELGELENDIRME_SINAV_DOSYA WHERE SINAV_ID=? AND PAKET_ID=?";
$_db->prep_exec_insert($sql, array($get['sinav'], $paket_Id));
$paket_id_yeni = $_db->getNextVal('SEQ_SINAV_DOSYA');
$sql = "INSERT INTO M_BELGELENDIRME_SINAV_DOSYA (PAKET_ID,SINAV_ID,TIP,UZANTI) VALUES(?,?,2,?)";
$_db->prep_exec_insert($sql, array($paket_id_yeni, $get['sinav'], $uzanti));
$sql = "UPDATE M_BELGELENDIRME_ADAY_BILDIRIM SET PAKET_ID = ? WHERE SINAV_ID=? AND PAKET_ID=?";
$_db->prep_exec_insert($sql, array($paket_id_yeni, $get['sinav'], $paket_Id));
move_uploaded_file($files['upload']['tmp_name'], EK_FOLDER . "sinav_bildirimleri/" . $user_id . "_" . $get["sinav"] . "_" . $paket_id_yeni . "." . $uzanti);
} else {
$sql = "UPDATE M_BELGELENDIRME_ADAY_BILDIRIM SET PUAN = null, BASARI_DURUMU = null WHERE PAKET_ID = ?";
$_db->prep_exec_insert($sql, array($paket_Id));
return $return;
}
} else {
$return["hataMesaji"][3] = "Yolladığınız dosyadaki veriler ile indirdiğiniz dosyadaki veriler uyuşmamaktadır. Kontrol ederek tekrar yükleyiniz.";
return $return;
}
}
}
/**
* TEXTFORMAT
*
* @param mixed $value Value to check
* @return boolean
*/
public static function TEXTFORMAT($value, $format)
{
$value = self::flattenSingleValue($value);
$format = self::flattenSingleValue($format);
return (string) PHPExcel_Style_NumberFormat::toFormattedString($value, $format);
}
function excel_date($xl_date)
{
return $displayDate = PHPExcel_Style_NumberFormat::toFormattedString($xl_date, 'YYYY-MM-DD hh:mm:ss');
}
/**
* TEXTFORMAT
*
* @param mixed $value Value to check
* @return boolean
*/
public static function TEXTFORMAT($value, $format)
{
$value = self::flattenSingleValue($value);
$format = self::flattenSingleValue($format);
if (is_string($value) && !is_numeric($value) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
$value = self::DATEVALUE($value);
}
return (string) PHPExcel_Style_NumberFormat::toFormattedString($value, $format);
}
/**
* Get cell value with formatting
*
* @return string
*/
public function getFormattedValue()
{
return (string) PHPExcel_Style_NumberFormat::toFormattedString($this->getCalculatedValue(), $this->getStyle()->getNumberFormat()->getFormatCode());
}
/**
* Generate row
*
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number
* @return string
* @throws Exception
*/
private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (is_array($pValues)) {
// Construct HTML
$html = '';
// Sheet index
$sheetIndex = $pSheet->getParent()->getIndex($pSheet);
// Write row start
if (!$this->_useInlineCss) {
$html .= ' <tr class="row' . $pRow . '">' . "\r\n";
} else {
$style = isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) ? $this->_assembleCSS($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) : '';
$html .= ' <tr style="' . $style . '">' . "\r\n";
}
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
if (!$this->_useInlineCss) {
$cssClass = '';
$cssClass = 'column' . $colNum;
} else {
$cssClass = array();
if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum])) {
$this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum];
}
}
$colSpan = 1;
$rowSpan = 1;
$writeCell = true;
// Write cell
// initialize
$cellData = '';
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop trough rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' . $this->_assembleCSS($this->_createCSSStyleFont($element->getFont())) . '">';
if ($element->getFont()->getSuperScript()) {
$cellData .= '<sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '<sub>';
}
}
}
// Convert UTF8 data to PCDATA
$cellText = $element->getText();
$cellData .= htmlspecialchars($cellText);
if ($element instanceof PHPExcel_RichText_Run) {
if ($element->getFont()->getSuperScript()) {
$cellData .= '</sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '</sub>';
}
}
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());
}
// Convert UTF8 data to PCDATA
$cellData = htmlspecialchars($cellData);
}
// replace leading spaces on each line with
$cellData = $this->_convertNbsp($cellData);
// convert newline "\n" to '<br>'
$cellData = str_replace("\n", '<br/>', $cellData);
// Check value
if ($cellData == '') {
$cellData = ' ';
}
// Extend CSS class?
if (!$this->_useInlineCss) {
$cssClass .= ' style' . $cell->getXfIndex();
$cssClass .= ' ' . $cell->getDataType();
} else {
if (isset($this->_cssStyles['td.style' . $cell->getXfIndex()])) {
$cssClass = array_merge($cssClass, $this->_cssStyles['td.style' . $cell->getXfIndex()]);
}
// General horizontal alignment: Actual horizontal alignment depends on dataType
$sharedStyle = $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex());
if ($sharedStyle->getAlignment()->getHorizontal() == PHPExcel_Style_Alignment::HORIZONTAL_GENERAL && isset($this->_cssStyles['.' . $cell->getDataType()]['text-align'])) {
$cssClass['text-align'] = $this->_cssStyles['.' . $cell->getDataType()]['text-align'];
}
}
} else {
$cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', PHPExcel_Cell_DataType::TYPE_NULL, $pSheet);
}
// Hyperlink?
if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
$cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>';
}
// Column/rowspan
foreach ($pSheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first[0] == $cell->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
} else {
$writeCell = false;
}
break;
}
}
// Write
if ($writeCell) {
// Column start
$html .= ' <td';
if (!$this->_useInlineCss) {
$html .= ' class="' . $cssClass . '"';
} else {
//** Necessary redundant code for the sake of PHPExcel_Writer_PDF **
// We must explicitly write the width of the <td> element because TCPDF
// does not recognize e.g. <col style="width:42pt">
$width = 0;
$columnIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
for ($i = $columnIndex; $i < $columnIndex + $colSpan; ++$i) {
if (isset($this->_columnWidths[$sheetIndex][$i])) {
$width += $this->_columnWidths[$sheetIndex][$i];
}
}
$cssClass['width'] = $width . 'pt';
// We must also explicitly write the height of the <td> element because TCPDF
// does not recognize e.g. <tr style="height:50pt">
if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'])) {
$height = $this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'];
$cssClass['height'] = $height;
}
//** end of redundant code **
$html .= ' style="' . $this->_assembleCSS($cssClass) . '"';
}
if ($colSpan > 1) {
$html .= ' colspan="' . $colSpan . '"';
}
if ($rowSpan > 1) {
$html .= ' rowspan="' . $rowSpan . '"';
}
$html .= '>';
// Image?
$html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate());
// Cell data
$html .= $cellData;
// Column end
$html .= '</td>' . "\r\n";
}
// Next column
++$colNum;
}
// Write row end
$html .= ' </tr>' . "\r\n";
// Return
return $html;
} else {
throw new Exception("Invalid parameters passed.");
}
}
public function format_date($date)
{
return PHPExcel_Style_NumberFormat::toFormattedString($date, 'YYYY-MM-DD');
}
/**
* Lee el archivo excel cargado y lo importa en la base de datos
*/
public function ProcesaArchivo()
{
$archivo = $_FILES['archivo'];
$objPHPExcel = PHPExcel_IOFactory::load($archivo['tmp_name']);
foreach ($objPHPExcel->getWorksheetIterator() as $hojaEstilo) {
$num_filas = $hojaEstilo->getHighestRow();
$cell = $hojaEstilo->getCellByColumnAndRow(0, 3);
$categoria['cod_categoria'] = $cell->getValue();
$cell = $hojaEstilo->getCellByColumnAndRow(1, 3);
$categoria['nombre_cat'] = $cell->getValue();
$cell = $hojaEstilo->getCellByColumnAndRow(2, 3);
$categoria['descripcion'] = $cell->getValue();
$cell = $hojaEstilo->getCellByColumnAndRow(3, 3);
$categoria['mostrar'] = $cell->getValue();
$categoria_id = $this->Mdl_xml->addCategoria($categoria);
//Guardamos su id para poder insertar las camisetas en esa categoría
//CREA e INSERTA CAMISETA DESDE EXCEL
for ($row = 7; $row <= $num_filas; ++$row) {
for ($col = 0; $col <= 11; ++$col) {
switch ($col) {
case 0:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$camiseta['cod_camiseta'] = $cell->getValue();
break;
case 1:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$camiseta['nombre_cam'] = $cell->getValue();
break;
case 2:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$camiseta['precio'] = $cell->getValue();
break;
case 3:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$camiseta['descuento'] = $cell->getValue();
break;
case 4:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$camiseta['imagen'] = $cell->getValue();
break;
case 5:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$camiseta['iva'] = $cell->getValue();
break;
case 6:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$camiseta['descripcion'] = $cell->getValue();
break;
case 7:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$camiseta['seleccionada'] = $cell->getValue();
break;
case 8:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$camiseta['mostrar'] = $cell->getValue();
break;
case 9:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$camiseta['fecha_inicio'] = PHPExcel_Style_NumberFormat::toFormattedString($val, 'YYYY-MM-DD');
break;
case 10:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$camiseta['fecha_fin'] = PHPExcel_Style_NumberFormat::toFormattedString($val, 'YYYY-MM-DD');
break;
case 11:
$cell = $hojaEstilo->getCellByColumnAndRow($col, $row);
$camiseta['stock'] = $cell->getValue();
break;
}
}
$camiseta['idCategoria'] = $categoria_id;
//Guardamos el id de su categoría
$this->Mdl_xml->AddCamiseta($camiseta);
// Inserta camiseta
}
}
$cuerpo = $this->load->view('View_importacionExcelCorrecta', '', true);
$this->load->view('View_plantilla', array('cuerpo' => $cuerpo, 'titulo' => 'Importación en Excel', 'homeactive' => 'active'));
}
/**
* Generate row
*
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number
* @return string
* @throws Exception
*/
private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (is_array($pValues)) {
// Construct HTML
$html = '';
// Sheet hashcode
$sheetHash = $pSheet->getHashCode();
// Write row start
if (!$this->_useInlineCss) {
$html .= ' <tr class="row' . $pRow . '">' . "\r\n";
} else {
$style = isset($this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow] : '';
$html .= ' <tr style="' . $style . '">' . "\r\n";
}
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
$cellData = ' ';
$cssClass = '';
if (!$this->_useInlineCss) {
$cssClass = 'column' . $colNum;
} else {
$cssClass = isset($this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum] : '';
}
$colSpan = 1;
$rowSpan = 1;
$writeCell = true;
// Write cell
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop trough rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">';
if ($element->getFont()->getSuperScript()) {
$cellData .= '<sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '<sub>';
}
}
}
// Convert UTF8 data to PCDATA
$cellText = $element->getText();
$cellData .= htmlspecialchars($cellText);
if ($element instanceof PHPExcel_RichText_Run) {
if ($element->getFont()->getSuperScript()) {
$cellData .= '</sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '</sub>';
}
}
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
}
// Convert UTF8 data to PCDATA
$cellData = htmlspecialchars($cellData);
}
// Check value
if ($cellData == '') {
$cellData = ' ';
}
// Extend CSS class?
if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) {
if (!$this->_useInlineCss) {
$cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex();
} else {
$cssClass .= isset($this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()]) ? $this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()] : '';
}
}
} else {
$cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null);
}
// Hyperlink?
if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
$cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>';
}
// Column/rowspan
foreach ($pSheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first == $cell->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
} else {
$writeCell = false;
}
break;
}
}
// Write
if ($writeCell) {
// Column start
$html .= ' <td';
if (!$this->_useInlineCss) {
$html .= ' class="' . $cssClass . '"';
} else {
$html .= ' style="' . $cssClass . '"';
}
if ($colSpan > 1) {
$html .= ' colspan="' . $colSpan . '"';
}
if ($rowSpan > 1) {
$html .= ' rowspan="' . $rowSpan . '"';
}
$html .= '>';
// Image?
$html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate());
// Cell data
if ($this->_useInlineCss) {
$html .= '<span style="' . $cssClass . '">';
}
$html .= $cellData;
if ($this->_useInlineCss) {
$html .= '</span>';
}
// Column end
$html .= '</td>' . "\r\n";
}
// Next column
++$colNum;
}
// Write row end
$html .= ' </tr>' . "\r\n";
// Return
return $html;
} else {
throw new Exception("Invalid parameters passed.");
}
}
