public function importsiswa()
{
require_once 'application/controllers/base/excel_reader2.php';
echo '<html><head><title>Loading...</title></head><body></body></html>';
$xls = $_FILES['data-import'];
if (!empty($_FILES['data-import']['name'])) {
$this->load->library('upload');
//load upload lobrary
$xls_name = str_replace(' ', '_', $xls['name']);
$config['upload_path'] = './assets/xls/';
$config['allowed_types'] = '*';
$config['max_size'] = '2000000';
//2MB
$config['overwrite'] = true;
$this->upload->initialize($config);
if (!$this->upload->do_upload('data-import')) {
//Upload Failed
echo $this->upload->display_errors();
} else {
//Upload Complete
$siswa = new Spreadsheet_Excel_Reader('./assets/xls/' . $xls_name);
$rows = $siswa->rowcount(0);
// echo $rows;
//looping to insert database
for ($i = 2; $i < $rows; $i++) {
$nis = $siswa->val($i, 1, 0);
//baris ke $i, kolom 1, sheet 1
$angkatan = $siswa->val($i, 2, 0);
$nama = $siswa->val($i, 3, 0);
$subkelas1 = $siswa->val($i, 4, 0);
$subkelas2 = $siswa->val($i, 5, 0);
$subkelas3 = $siswa->val($i, 6, 0);
if (empty($subkelas1)) {
$subkelas1 = null;
}
if (empty($subkelas2)) {
$subkelas2 = null;
}
if (empty($subkelas3)) {
$subkelas3 = null;
}
$status = $siswa->val($i, 7, 0);
$kelamin = $siswa->val($i, 8, 0);
$password = md5($nis);
$data = array('nis' => $nis, 'angkatan' => $angkatan, 'nama_lengkap' => $nama, 'subkelas1' => $subkelas1, 'subkelas2' => $subkelas2, 'subkelas3' => $subkelas3, 'status' => $status, 'password' => $password, 'kelamin' => $kelamin);
// echo '<br/>';
// print_r($data);
//insert to table
$this->db->insert('siswa', $data);
}
echo 'Berhasil Import Data Siswa <a href="' . site_url("admin/siswa") . '">kembali</a>';
}
} else {
echo 'XLS Not Found!';
}
}
public function import($file)
{
try {
$users = array();
$params = array(1 => 'user_name', 'user_password', 'user_first_name', 'user_last_name', 'user_email', 'user_group', 'user_vhost');
$excel = new Spreadsheet_Excel_Reader($file);
$rows = $excel->rowcount($sheet_index = 0);
$cols = $excel->colcount($sheet_index = 0);
for ($row = 2; $row <= $rows; $row++) {
if ($cols == 7) {
for ($col = 1; $col <= $cols; $col++) {
$users[$row][$params[$col]] = $excel->val($row, $col);
$users[$row]['user_vhost'] = explode(',', $excel->val($row, 7));
$users[$row]['user_group'] = '';
}
}
}
$this->userimport = new userimport();
$users = $this->userimport->import($users);
$_SESSION['message'] = $this->userimport->get_message();
return $users;
} catch (Exception $e) {
display_page_error();
}
}
public function FetchContacts($credential = null)
{
$filename = OPENBIZ_APP_PATH . $this->_data['file'];
$data = new Spreadsheet_Excel_Reader($filename);
$results = array();
$row_num = $data->rowcount();
if ($row_num >= 3) {
for ($i = 3; $i <= $row_num; $i++) {
$results[] = $this->RowtoContact($data, $i);
}
}
unlink($filename);
return $results;
}
/**
* Method untuk membaca data pada file excel
*
* @param string $path_file path file excel
* @param integer $baris_mulai_data
* @return array
*/
function data_excel($path_file, $baris_mulai_data = 2)
{
include 'excel_reader2.php';
$file_excel = new Spreadsheet_Excel_Reader($path_file);
# membaca jumlah baris dari data excel
$baris = $file_excel->rowcount($sheet_index = 0);
$kolom = $file_excel->colcount($sheet_index = 0);
$data_return = array();
for ($i = $baris_mulai_data; $i <= $baris; $i++) {
$row_data = array();
for ($k = 1; $k <= $kolom; $k++) {
$row_data[] = $file_excel->val($i, $k);
}
$data_return[] = $row_data;
}
return $data_return;
}
function proses()
{
require 'excel_reader.php';
if (isset($_POST['submit'])) {
$target = basename($_FILES['filepegawaiall']['name']);
move_uploaded_file($_FILES['filepegawaiall']['tmp_name'], $target);
$data = new Spreadsheet_Excel_Reader($_FILES['filepegawaiall']['name'], false);
// menghitung jumlah baris file xls
$baris = $data->rowcount($sheet_index = 0);
// jika kosongkan data dicentang jalankan kode berikut
if ($_POST['drop'] == 1) {
// kosongkan tabel pegawai
$truncate = "TRUNCATE TABLE pegawai";
$this->db->query($truncate);
}
// import data excel mulai baris ke-2 (karena tabel xls ada header pada baris 1)
for ($i = 2; $i <= $baris; $i++) {
// membaca data (kolom ke-1 sd terakhir)
$nama = $data->val($i, 1);
$tempat_lahir = $data->val($i, 2);
$tanggal_lahir = $data->val($i, 3);
echo $nama;
echo " ";
echo $tempat_lahir;
echo " ";
echo $tanggal_lahir;
echo " ";
echo "<br>";
// setelah data dibaca, masukkan ke tabel pegawai sql
// $query = "INSERT into pegawai (nama,tempat_lahir,tanggal_lahir)values('$nama','$tempat_lahir','$tanggal_lahir')";
// $hasil = $this->db->query($query);
}
// if(!$hasil){
// jika import gagal
// die(mysql_error());
// }else{
// jika impor berhasil
// echo "Data berhasil diimpor.";
// }
// hapus file xls yang udah dibaca
unlink($_FILES['filepegawaiall']['name']);
}
}
public function import($file)
{
try {
$users = array();
$params = array(1 => 'username', 'password', 'first_name', 'last_name', 'email', 'admin', 'active');
$excel = new Spreadsheet_Excel_Reader($file);
$rows = $excel->rowcount($sheet_index = 0);
$cols = $excel->colcount($sheet_index = 0);
for ($row = 2; $row <= $rows; $row++) {
for ($col = 1; $col <= $cols; $col++) {
$users[$row][$params[$col]] = $excel->val($row, $col);
}
}
$this->userimport = new userimport();
$users = $this->userimport->import($users);
$_SESSION['message'] = $this->userimport->get_message();
return $users;
} catch (Exception $e) {
display_page_error();
}
}
function importToExistingTable($file, $tableName)
{
$data = new Spreadsheet_Excel_Reader($file);
$colcount = $data->colcount();
$rowcount = $data->rowcount();
//This gets the field names in the database so that they can be confirmed as matching those in the spreadsheet:
$sql = mysql_query("SELECT * FROM `{$tableName}`");
for ($no = 0; $no <= $colcount; $no++) {
$tableColsArray[$no] = mysql_field_name($sql, $no);
}
//This goes through the spreadsheet and compares the columns with those in the database:
for ($colno = 1; $colno <= $colcount; $colno++) {
$cell = $data->value(1, $colno);
if ($tableColsArray[$colno] != $cell) {
die("Column {$colno} does not match its counterpart in the database");
} else {
echo "spreadsheet part ({$cell}) perfectly matches db part ({$tableColsArray[$colno]})<br />";
}
}
//This goes through the whole sheet building the insert statement from the data:
$actualInserts = 0;
for ($rowno = 2; $rowno <= $rowcount; $rowno++) {
$sql = "INSERT INTO `{$tableName}` (";
for ($no = 1; $no < count($tableColsArray); $no++) {
$sql = $sql . "`{$tableColsArray[$no]}`,";
}
$sql = substr($sql, 0, -1) . ") VALUES (";
for ($colno = 1; $colno <= $colcount; $colno++) {
$sql = $sql . "'" . mysql_real_escape_string($data->value($rowno, $colno)) . "',";
}
$sql = substr($sql, 0, -1) . ")";
//This executes the insert and counts the successful inserts:
if (!mysql_query($sql)) {
echo mysql_error();
} else {
$actualInserts++;
}
}
echo "<br />" . $actualInserts . " rows inserted";
}
$error = 'Failed to write file to disk';
break;
case '8':
$error = 'File upload stopped by extension';
break;
case '999':
default:
$error = 'No error code avaiable';
}
} elseif (empty($_FILES[$element]['tmp_name']) || $_FILES[$element]['tmp_name'] == 'none') {
$error = 'No file was uploaded..';
} else {
$filename = $_FILES[$element]['tmp_name'];
//echo $filename."f";
$data = new Spreadsheet_Excel_Reader($filename);
$r = $data->rowcount($sheet_index = 0);
$i = 0;
echo $r;
while (1 != $r) {
mysql_query("INSERT INTO `task_detail`\r\n\t\t\t\t\t\t\t (`task_id`, `user_id`, `status`, `person_n`, `first_name`, `last_name`, `person_status`,`phone`, `mail`, `addres`, `city_id`, `family_id`, `b_day`, `profesion`)\r\n\t\t\t\t\t\t\t VALUES ( '" . $_REQUEST['task_id'] . "', '" . $_SESSION['USERID'] . "', '1',\r\n\t\t\t\t\t\t\t\t\t\t '" . $data->val($r, 'A') . "', '" . $data->val($r, 'B') . "',\r\n\t\t\t\t\t\t\t\t\t \t '" . $data->val($r, 'C') . "', '" . $data->val($r, 'D') . "',\r\n\t\t\t\t\t\t\t\t\t \t '" . $data->val($r, 'E') . "', '" . $data->val($r, 'F') . "',\r\n\t\t\t\t\t\t\t\t\t \t '" . $data->val($r, 'G') . "', '" . $data->val($r, 'H') . "',\r\n\t\t\t\t\t\t\t\t\t \t '" . $data->val($r, 'I') . "', '" . $data->val($r, 'J') . "',\r\n\t\t\t\t\t\t\t\t\t \t '" . $data->val($r, 'K') . "')") or die(err);
$r--;
//return 0;
}
echo "xls File has been successfully Imported";
if (file_exists($path)) {
unlink($path);
}
// move_uploaded_file ( $_FILES [$element] ['tmp_name'], $path);
//
// for security reason, we force to remove all uploaded file
// @unlink ( $_FILES [$element] );
<?php
if (isset($_GET['email'])) {
$email = $_GET['email'];
}
require_once 'lib/excel_reader2.php';
define('MPDF_PATH', 'lib/mpdf');
include MPDF_PATH . '/mpdf.php';
require_once 'lib/mpdf/mpdf.php';
$excel = new Spreadsheet_Excel_Reader('docs/participantes.xls');
$mpdf = new mPDF('utf-8', 'A4-L', 0, '', 0, 0, 3, 0, 0, 0);
$certificate = array();
$date = getdate();
$count = $excel->rowcount();
for ($i = 0; $i < $count + 1; $i++) {
$current_email = $excel->val($i, 3);
if ($current_email == $email) {
$first_name = $excel->val($i, 1);
$last_name = $excel->val($i, 2);
$certificate['name'] = utf8_encode($first_name . ' ' . $last_name);
$certificate['type'] = $excel->val($i, 4);
break;
}
}
$html = '<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8">
<title>Certificado - WordCamp RJ 2015</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto:400,100,100italic,300,300italic,400italic,500,500italic,700,700italic,900,900italic">
<link rel="stylesheet" href="css/certificate.css">
</head>
<body>';
if (!empty($email) && filter_var($email, FILTER_VALIDATE_EMAIL)) {
<div class="row">
<div class="col-md-12">
<div class="box-header">
<h3 class="box-title">Kết quả Import Excel</h3>
</div><!-- /.box-header -->
<?php
$arrColumn = array('product_code', 'product_name', 'cate_type_id', 'cate_id', 'trangthai', 'price', 'price_saleoff', 'deal_amount', 'start_date', 'end_date', 'da_ban', 'meta_title', 'meta_description', 'meta_keyword');
require_once 'controller/ExcelReader.php';
$dataArr = array();
if ($_FILES['file']['tmp_name']) {
$data = new Spreadsheet_Excel_Reader($_FILES['file']['tmp_name'], true, "UTF-8");
$numCol = $data->colcount();
$rowCol = $data->rowcount();
for ($j = 1; $j <= $numCol; $j++) {
for ($i = 2; $i <= $rowCol; $i++) {
$dataArr[$arrColumn[$j - 1]][] = $data->val($i, $j);
}
}
}
if (!empty($dataArr)) {
$count = count($dataArr['product_code']);
for ($k = 0; $k <= $count; $k++) {
foreach ($arrColumn as $value) {
$dataInsert[$value] = $dataArr[$value][$k];
$dataInsert['name_en'] = $model->stripUnicode($dataArr['product_name'][$k]);
$dataInsert['product_alias'] = $model->changeTitle($dataArr['product_name'][$k]);
$id = $model->getProductIdByCode($dataArr['product_code'][$k]);
if ($id > 0) {
$dataInsert['id'] = $id;
}
}
<?php
$xls = new Spreadsheet_Excel_Reader($uploaded_file, false);
$baris = $xls->rowcount($sheet_index = 0);
$x_tgl_bayar_bank = clean($xls->val(2, 2));
$spl = explode('-', $x_tgl_bayar_bank);
if (count($spl) != 3 || !checkdate($spl[1], $spl[0], $spl[2])) {
echo '<tr><td colspan="14">Error. Format tanggal tidak valid. ' . $x_tgl_bayar_bank . '</td></tr>';
exit;
}
$tbb = $spl[0] . '-' . $spl[1] . '-' . $spl[2] . ' 00:00:00';
# LIST KODE_BLOK
$q_kode_blok = array();
for ($ix = 4; $ix <= $baris; $ix++) {
$x_kode_blok = (string) clean($xls->val($ix, 2));
if ($x_kode_blok != "") {
$q_kode_blok[] = $x_kode_blok;
}
}
# LIST NO_PELANGGAN
$in_q_kode_blok = implode("', '", $q_kode_blok);
$query = "\nSELECT \n\tKODE_BLOK, NO_PELANGGAN\nFROM\n\tKWT_PELANGGAN \nWHERE\n\tKODE_BLOK IN ('{$in_q_kode_blok}') \n";
$q_np = array();
$obj = $conn->Execute($query);
while (!$obj->EOF) {
$q_kb = $obj->fields['KODE_BLOK'];
$q_np["{$q_kb}"] = $obj->fields['NO_PELANGGAN'];
$obj->movenext();
}
# GET VALUES
for ($ix = 4; $ix <= $baris; $ix++) {
/**
* Parses xls import file
*
* @access private
* @param object $subnet
* @param array $custom_address_fields
* @return mixed
*/
private function parse_import_file_xls($subnet, $custom_address_fields)
{
# get excel object
require_once dirname(__FILE__) . '/../../functions/php-excel-reader/excel_reader2.php';
//excel reader 2.21
$data = new Spreadsheet_Excel_Reader(dirname(__FILE__) . '/../../app/subnets/import-subnet/upload/import.xls', false);
//get number of rows
$numRows = $data->rowcount(0);
$numRows++;
$outFile = array();
//get all to array!
for ($m = 0; $m < $numRows; $m++) {
//IP must be present!
if (filter_var($data->val($m, 'A'), FILTER_VALIDATE_IP)) {
//for multicast
if ($this->settings - enableMulticast == "1") {
if (strlen($data->val($m, 'F')) == 0 && $this->Subnets->is_multicast($data->val($m, 'A'))) {
$mac = $this->Subnets->create_multicast_mac($data->val($m, 'A'));
} else {
$mac = $data->val($m, 'F');
}
}
$outFile[$m] = $data->val($m, 'A') . ',' . $data->val($m, 'B') . ',' . $data->val($m, 'C') . ',' . $data->val($m, 'D') . ',';
$outFile[$m] .= $data->val($m, 'E') . ',' . $mac . ',' . $data->val($m, 'G') . ',' . $data->val($m, 'H') . ',';
$outFile[$m] .= $data->val($m, 'I') . ',' . $data->val($m, 'J');
//add custom fields
if (sizeof($custom_address_fields) > 0) {
$currLett = "K";
foreach ($custom_address_fields as $field) {
$outFile[$m] .= "," . $data->val($m, $currLett++);
}
}
}
}
// return
return $outFile;
}
public function importexcel()
{
$fileupload = $_FILES['fileexcel']['tmp_name'];
$namafile = $_FILES['fileexcel']['name'];
$path = 'assets/resources/data/';
$pathfile = $path . $namafile;
$orgid = $this->my_usession->userdata('user_dept') != '' ? $this->employe_model->deptonall(explode(',', $this->my_usession->userdata('user_dept'))) : array();
$holiday = $this->roster_model->holiday($orgid);
$holarray = array();
foreach ($holiday->result() as $hol) {
$tglmulai = strtotime($hol->startdate);
$tglselesai = strtotime($hol->enddate);
$selisih = $tglselesai - $tglmulai;
if ($selisih == 0) {
$holarray[] = $hol->startdate;
} else {
$jarak = $selisih / 86400;
for ($k = 0; $k <= $jarak; $k++) {
$holarray[] = date('Y-m-d', strtotime($hol->startdate) + $k * 86400);
}
}
}
if (move_uploaded_file($fileupload, $pathfile)) {
include_once APPPATH . "libraries/excelreader.php";
$data = new Spreadsheet_Excel_Reader($pathfile);
$blth = $data->val(2, 2) . '-' . date('m', strtotime($data->val(1, 2)));
$usernik = $this->roster_model->getuseridfromnik();
foreach ($usernik->result() as $useridfromnik) {
$array[$useridfromnik->userid] = array('name' => $useridfromnik->name, 'group' => $useridfromnik->deptid);
}
$a = array();
$datagagal = '';
$nonik = 0;
for ($i = 5; $i <= $data->rowcount($sheet_index = 0); $i++) {
$nik = $data->val($i, 1);
if ($nik != '') {
if (isset($array[$nik])) {
$userid = $nik;
$name = $array[$nik]['name'];
$group = $array[$nik]['group'];
$data_arr = array('userid' => $userid, 'name' => $name, 'group' => $group);
$tgla = '';
$tanggal = strtotime($blth . '-' . $data->val(3, 3));
for ($j = 3; $j <= $data->colcount($sheet_index = 0); $j++) {
$tgla .= $tanggal + ($j - 3) * 86400 . "\t";
$tglan = $tanggal + ($j - 3) * 86400;
$shift[$j] = $data->val($i, $j);
if ($shift[$j] != '') {
$savedata = array('userid' => $userid, 'rosterdate' => date('Y-m-d', $tglan), 'absence' => $shift[$j]);
$data_arr[$tglan] = $shift[$j];
} else {
$data_arr[$tglan] = $shift[$j];
}
}
$fieldarr[] = $data_arr;
} else {
$datagagal = $datagagal . $nik . ', ';
}
} else {
$nonik++;
}
}
$fp = fopen('assets/js/template/roster_' . $this->my_usession->userdata('user_id') . '.json', 'w');
fwrite($fp, json_encode(array('data' => $fieldarr)));
fclose($fp);
if (strpos($datagagal, ',') !== false) {
$datagagal = substr($datagagal, 0, -2);
}
$tgl = "userid\tname\t";
$tglan = explode("\t", $tgla);
$count = count($tglan);
for ($i = 0; $i < $count - 1; $i++) {
if (in_array(date('Y-m-d', $tglan[$i]), $holarray)) {
$tgl .= $tglan[$i] . "#\t";
} else {
$tgl .= $tglan[$i] . "\t";
}
}
$actionlog = array('user' => $this->my_usession->userdata('username'), 'ipadd' => $this->ipaddress->get_ip(), 'logtime' => date("Y-m-d H:i:s"), 'logdetail' => 'Import roster', 'info' => $this->lang->line('message_success'));
$this->db->insert('actionlog', $actionlog);
$hasil = array("responseText" => "success", "success" => true, "tglawal" => $tglan[0], "tglakhir" => $tglan[$count - 2], "datagagal" => $datagagal, "nonik" => $nonik, "field" => $tgl);
echo json_encode($hasil);
} else {
$hasil = array("responseText" => $this->lang->line('message_failimport'), "success" => false);
echo json_encode($hasil);
}
}
<?php
ini_set("memory_limit", "10000M");
$conn = mysql_connect("localhost", "mahimagroup", "Mahima123") or die("Couldn't connect to server.");
$db = mysql_select_db("mahimagroup", $conn) or die("Couldn't select database.");
ini_set("display_errors", 1);
error_reporting(E_ALL);
require_once 'excel/excel_reader2.php';
$data = new Spreadsheet_Excel_Reader("itemMaster.xls");
for ($row = 2; $row <= $data->rowcount(); $row++) {
$sql = "SELECT uom_id FROM ms_uom where name = '" . $data->value($row, 6) . "'";
$result = mysql_query($sql) or die("Error in : " . $sql . "<br>" . mysql_errno() . " : " . mysql_error());
if (mysql_num_rows($result) > 0) {
$row_1 = mysql_fetch_array($result);
$unit_id = $row_1['uom_id'];
}
$sql = "SELECT department_id FROM ms_department where name = '" . $data->value($row, 2) . "'";
$result = mysql_query($sql) or die("Error in : " . $sql . "<br>" . mysql_errno() . " : " . mysql_error());
if (mysql_num_rows($result) > 0) {
$row_1 = mysql_fetch_array($result);
$department_id = $row_1['department_id'];
}
$sql = "SELECT machinary_id FROM ms_machinary where name = '" . $data->value($row, 3) . "'";
$result = mysql_query($sql) or die("Error in : " . $sql . "<br>" . mysql_errno() . " : " . mysql_error());
if (mysql_num_rows($result) > 0) {
$row_1 = mysql_fetch_array($result);
$machinary_id = $row_1['machinary_id'];
}
echo $sql_item = "insert into ms_item_master set\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tname = '" . addslashes($data->value($row, 1)) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdepartment_id = '" . $department_id . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tuom_id = '" . $unit_id . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tmachinary_id = '" . $machinary_id . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdrawing_number ='" . $data->value($row, 4) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tcatelog_number = '" . $data->value($row, 5) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ttype_of_item = '" . $data->value($row, 8) . "'";
//$result_ins = mysql_query($sql_item) or die("Error in query:".$sql_item."<br>".mysql_error().":".mysql_errno());
}
src="jquery-1.5.1.min.js"></script>-->
<script type="text/javascript"
src="../lib/js/jquery-ui/js/jquery-ui-1.8.11.custom.min.js"></script>
</head>
<body>
<?php
// menggunakan class phpExcelReader
include "../lib/excel_reader2.php";
include "../lib/koneksi.php";
include "../lib/function.php";
// membaca file excel yang diupload
$data = new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
// membaca jumlah baris dari data excel
$baris = $data->rowcount($sheet_index = 0);
// nilai awal counter untuk jumlah data yang sukses dan yang gagal diimport
$sukses = 0;
$gagal = 0;
// import data excel mulai baris ke-2 (karena baris pertama adalah nama kolom)
for ($i = 2; $i <= $baris; $i++) {
// membaca data bcf15 (kolom ke-1)
$bmnno = $data->val($i, 1);
$bmntgl = $data->val($i, 2);
$bmntglsql = sql($bmntgl);
$tahunkep = substr($bmntglsql, 0, 4);
$dok_asal = $data->val($i, 3);
$nomordokasal = $data->val($i, 4);
$tglmordokasal = $data->val($i, 5);
$tglmordokasalsql = sql($tglmordokasal);
$tahunmordokasalsql = substr($tglmordokasalsql, 0, 4);
<?php
require_once "koneks.php";
error_reporting(E_ALL ^ E_NOTICE);
require_once 'excel_reader2.php';
$baris = $data->rowcount($sheet_index = 0);
// proses assigning baca data file 'data.xls'
//$data = new Spreadsheet_Excel_Reader("data.xls");
$data = new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
//-------- import dari sheet 1 ----------
// baca jumlah baris dalam sheet 1
$jmlbaris = $data->rowcount(0);
for ($i = 2; $i <= $jmlbaris; $i++) {
// baca data pada baris ke-i, kolom ke-1, pada sheet 1
$datakolom1 = $data->val($i, 1, 0);
// baca data pada baris ke-i, kolom ke-2, pada sheet 1
$datakolom2 = $data->val($i, 2, 0);
// insert data ke tabel mhs
$query = "INSERT INTO mhs (nim, namamhs) VALUES ('{$datakolom1}', '{$datakolom2}')";
mysql_query($query);
}
//-------- import dari sheet 2 ----------
// baca jumlah baris dalam sheet 2
$jmlbaris = $data->rowcount(1);
for ($i = 2; $i <= $jmlbaris; $i++) {
// baca data pada baris ke-i, kolom ke-1, pada sheet 2
$datakolom1 = $data->val($i, 1, 1);
// baca data pada baris ke-i, kolom ke-2, pada sheet 2
$datakolom2 = $data->val($i, 2, 1);
// insert data ke tabel dosen
$query = "INSERT INTO dosen (kodedosen, namadosen) VALUES ('{$datakolom1}', '{$datakolom2}')";
function importarTrabajadores() {
global $conn;
try {
if ($_FILES["archivo"]["name"] == "")
throw new Exception("Debe elegir el Archivo a subir.");
if (!validarExtension($_FILES["archivo"]["name"], array("xls")))
throw new Exception("El Archivo a subir debe ser de extensión \".xls\".");
// Borro los registros temporales que se pudieran haber generado en otra oportunidad..
$params = array(":idusuario" => $_SESSION["idUsuario"], ":ipusuario" => $_SERVER["REMOTE_ADDR"]);
$sql =
"DELETE FROM tmp.tcm_cargamasivatrabajadoresweb
WHERE cm_idusuario = :idusuario
AND cm_ipusuario = :ipusuario";
DBExecSql($conn, $sql, $params, OCI_DEFAULT);
error_reporting(E_ALL ^ E_NOTICE);
$excel = new Spreadsheet_Excel_Reader($_FILES["archivo"]["tmp_name"]);
for ($row=2; $row<=$excel->rowcount(); $row++) { // Empiezo desde la 2, porque en la 1 viene la cabecera..
// Meto los valores de las columnas en un array..
$cols = array();
for ($col=65; $col<=87; $col++)
$cols[chr($col)] = trim($excel->val($row, chr($col)));
// Si todas las columnas estan vacías lo tomo como un EOF y salgo del loop principal..
$existeValor = false;
foreach ($cols as $key => $value)
if ($value != "")
$existeValor = true;
if (!$existeValor)
break;
// *** - INICIO VALIDACIONES..
$errores = "11111111111111111111111";
// Columna A - CUIL..
if (!validarCuit($cols["A"]))
$errores[0] = "0";
// Columna B - Nombre..
if ($cols["B"] == "")
$errores[1] = "0";
// Columna C - Sexo..
if (($cols["C"] != "F") and ($cols["C"] != "M"))
$errores[2] = "0";
// Columna D - Nacionalidad..
if ($cols["D"] != "") {
$params = array(":descripcion" => $cols["D"]);
$sql =
"SELECT 1
FROM cna_nacionalidad
WHERE na_fechabaja IS NULL
AND UPPER(na_descripcion) = UPPER(:descripcion)";
if (!existeSql($sql, $params))
$errores[3] = "0";
}
// Columna E - Otra nacionalidad..
$errores[4] = "1";
// Columna F - Fecha de nacimiento..
try {
if (isFechaValida($cols["F"])) {
$edad = dateDiff($cols["F"], date("d/m/Y"), "A");
if (($edad < 16) or ($edad > 90))
$errores[5] = "0";
}
else
$errores[5] = "0";
}
catch (Exception $e) {
$errores[5] = "0";
}
// Columna G - Estado Civil..
if ($cols["G"] != "") {
$params = array(":descripcion" => $cols["G"]);
$sql =
"SELECT 1
FROM ctb_tablas
WHERE tb_clave = 'ESTAD'
AND tb_fechabaja IS NULL
AND UPPER(tb_descripcion) = UPPER(:descripcion)";
if (!existeSql($sql, $params))
$errores[6] = "0";
}
// Columna H - Fecha de ingreso..
if (!isFechaValida($cols["H"]))
$errores[7] = "0";
// Columna I - Establecimiento..
$errores[8] = "1";
// Columna J - Tipo contrato..
if ($cols["J"] != "") {
$params = array(":descripcion" => $cols["J"]);
$sql =
"SELECT 1
FROM cmc_modalidadcontratacion
WHERE mc_fechabaja IS NULL
AND UPPER(mc_descripcion) = UPPER(:descripcion)";
if (!existeSql($sql, $params))
$errores[9] = "0";
}
// Columna K - Tarea..
$errores[10] = "1";
// Columna L - Sector..
$errores[11] = "1";
// Columna M - Código CIUO..
if ($cols["M"] != "") {
$params = array(":codigo" => $cols["M"]);
$sql =
"SELECT 1
FROM cci_ciuo
WHERE ci_codigo = :codigo";
if (!existeSql($sql, $params))
$errores[12] = "0";
}
// Columna N - Remuneración..
if ($cols["N"] != "")
if (!validarNumero($cols["N"], true))
$errores[13] = "0";
// Columna O - Calle..
if ($cols["O"] == "")
$errores[14] = "0";
// Columna P - Número..
$errores[15] = "1";
// Columna Q - Piso..
$errores[16] = "1";
// Columna R - Departamento..
$errores[17] = "1";
// Columna S - Código postal..
if ($cols["S"] == "")
$errores[18] = "0";
else {
$params = array(":codigopostal" => $cols["S"]);
$sql =
"SELECT 1
FROM cub_ubicacion
WHERE ub_cpostal = :codigopostal";
if (!existeSql($sql, $params))
$errores[18] = "0";
}
// Columna T - Localidad..
if (($cols["T"] != "") and ($cols["S"] != "")) {
$params = array(":codigopostal" => $cols["S"], ":localidad" => $cols["T"]);
$sql =
"SELECT 1
FROM cub_ubicacion
WHERE ub_cpostal = :codigopostal
AND UPPER(ub_localidad) = UPPER(:localidad)";
if (!existeSql($sql, $params))
$errores[19] = "0";
}
// Columna U - Provincia..
if (($cols["U"] != "") and ($cols["T"] != "") and ($cols["S"] != "")) {
$params = array(":codigopostal" => $cols["S"], ":localidad" => $cols["T"], ":provincia" => $cols["U"]);
$sql =
"SELECT 1
FROM cub_ubicacion, cpv_provincias
WHERE ub_provincia = pv_codigo
AND ub_cpostal = :codigopostal
AND UPPER(ub_localidad) = UPPER(:localidad)
AND UPPER(pv_descripcion) = UPPER(:provincia)";
if (!existeSql($sql, $params))
$errores[20] = "0";
}
// Columna V - Fecha de baja..
if ($cols["V"] != "")
if (!isFechaValida($cols["V"]))
$errores[21] = "0";
// Columna W - No confirmado al puesto..
// $errores[22] = "1";
// *** - FIN VALIDACIONES..
$params = array(":calle" => substr($cols["O"], 0, 60),
":ciuo" => substr($cols["M"], 0, 4),
":codigopostal" => substr($cols["S"], 0, 5),
/* ":confirmapuesto" => substr($cols["W"], 0, 1),*/
":cuil" => substr($cols["A"], 0, 11),
":departamento" => substr($cols["R"], 0, 20),
":errores" => $errores,
":establecimiento" => substr($cols["I"], 0, 100),
":estadocivil" => $cols["G"],
":fechabaja" => substr($cols["V"], 0, 10),
":fechaingreso" => substr($cols["H"], 0, 10),
":fechanacimiento" => substr($cols["F"], 0, 10),
":fila" => $row,
":idusuario" => $_SESSION["idUsuario"],
":ipusuario" => $_SERVER["REMOTE_ADDR"],
":localidad" => substr($cols["T"], 0, 60),
":nacionalidad" => $cols["D"],
":nombre" => substr($cols["B"], 0, 60),
":numero" => substr($cols["P"], 0, 20),
":otranacionalidad" => substr($cols["E"], 0, 30),
":piso" => substr($cols["Q"], 0, 20),
":provincia" => $cols["U"],
":sector" => substr($cols["L"], 0, 150),
":sexo" => substr($cols["C"], 0, 1),
":sueldo" => substr($cols["N"], 0, 15),
":tarea" => substr($cols["K"], 0, 150),
":tipocontrato" => substr($cols["J"], 0, 100));
$sql =
"INSERT INTO tmp.tcm_cargamasivatrabajadoresweb
(cm_idusuario, cm_ipusuario, cm_fila, cm_cuil, cm_nombre, cm_sexo, cm_nacionalidad, cm_otranacionalidad, cm_fechanacimiento, cm_estadocivil, cm_fechaingreso,
cm_establecimiento, cm_tipocontrato, cm_tarea, cm_sector, cm_ciuo, cm_sueldo, cm_calle, cm_numero, cm_piso, cm_departamento, cm_codigopostal, cm_localidad,
cm_provincia, cm_fechabaja, cm_errores)
VALUES (:idusuario, :ipusuario, :fila, :cuil, :nombre, :sexo, :nacionalidad, :otranacionalidad, :fechanacimiento, :estadocivil, :fechaingreso,
:establecimiento, :tipocontrato, :tarea, :sector, :ciuo, :sueldo, :calle, :numero, :piso, :departamento, :codigopostal, :localidad,
:provincia, :fechabaja, :errores)";
DBExecSql($conn, $sql, $params, OCI_DEFAULT);
}
DBCommit($conn);
}
catch (Exception $e) {
DBRollback($conn);
echo "<script type='text/javascript'>history.back(); alert(unescape('".rawurlencode($e->getMessage())."'));</script>";
exit;
}
}
<input type="submit" value="Simpan" name="submit"class="btn btn-success"></td>
</tr>
</table>
</form>';
$admin .= '</div>';
}
if($_GET['aksi']=="import"){
if(isset($_POST['submit'])){
$jenis = $_POST['jenis'];
//nilai awal counter jumlah data yang sukses dan yang gagal diimport
$sukses = 0;
$gagal = 0;
$cell = new Spreadsheet_Excel_Reader($_FILES['upfile']['tmp_name']);
$jum = $cell->rowcount($sheet_index=0);
$i = 2; // dimulai dari ke dua karena baris pertama berisi title
while( $i<=$jum ){
//$cell->val( baris,kolom )
$kode = $cell->val( $i,1 );
$nama = $cell->val( $i,2 );
$jumlah = $cell->val( $i,3 );
$hargabeli = $cell->val( $i,4 );
$hargajual = $cell->val( $i,5 );
$sql ="INSERT INTO `po_produk` (`jenis`,`kode`,`nama`,`jumlah`,`hargabeli`,`hargajual`) VALUES ('$jenis','$kode','$nama','$jumlah','$hargabeli','$hargajual')";
$hasil = mysql_query( $sql );
setsaldoawal($kode);
div { display:none; color:#aaa; }
</style>
</head>
<body style="width:1250px">
<?
include("../inc/store_function.php");
include("../inc/dbconnection.php");
//$resCpunt=mysql_query("select * from maint_job");
//echo mysql_num_rows($resCpunt);
/*for($col=1;$col<=$xls->colcount();$col++)
{
echo $xls->val(1,$col).'<br />';
}
$xls->rowcount();*/
for ($row=2;$row<=$xls->rowcount();$row++)
{
$arr=array();
$job_code=(int)($xls->val($row,1));
$service_id=(int)$xls->val($row,4);
$machine_id=(int)$xls->val($row,3);
$status=trim($xls->val($row,9));
$remark=addslashes($xls->val($row,7)).' '.addslashes($xls->val($row,8));
$maint_date='';
if($xls->val($row,6)!="")
{
$maint_date=explode('-',$xls->val($row,6));
if(sizeof($maint_date)>0)
$maint_date=date("Y-m-d", mktime(0, 0, 0, (int)$maint_date[1], ((int)$maint_date[0]-1), (int)$maint_date[2]));
else
$maint_date='';
function xls_import($id, $returl = '')
{
global $_CAMILA;
require_once CAMILA_DIR . 'datagrid/form.class.php';
require_once CAMILA_DIR . 'datagrid/elements/form/hidden.php';
require_once CAMILA_DIR . 'datagrid/elements/form/filebox.php';
require_once CAMILA_DIR . 'datagrid/elements/form/static_listbox.php';
//if ($returl != '')
// $form3 = new phpform('camilastep4', $returl);
//else
$form3 = new phpform('camilastep4', 'cf_worktable_wizard_step4.php');
$form3->submitbutton = camila_get_translation('camila.wizard.next');
$form3->drawrules = false;
new form_hidden($form3, 'custom', $id);
if ($returl != '') {
new form_hidden($form3, 'returl', $_REQUEST['camila_returl']);
}
new form_filebox($form3, 'filename', camila_get_translation('camila.worktable.xls.choose'), 50, CAMILA_TMP_DIR);
$sheet_list = '';
for ($i = 0; $i < 10; $i++) {
if ($i > 0) {
$sheet_list .= ',';
}
$sheet_list .= $i . ';' . ($i + 1);
}
new form_static_listbox($form3, 'sheetnum', camila_get_translation('camila.worktable.xls.sheetnum'), $sheet_list);
$success = true;
if ($form3->process()) {
$filename = $form3->fields['filename']->value[0];
$sheetnum = $form3->fields['sheetnum']->value;
$result = $_CAMILA['db']->Execute('select short_title, scriptname, tablename, filename, sheetnum from ' . CAMILA_TABLE_WORKT . ' where id=' . $_CAMILA['db']->qstr($id));
if ($result === false) {
camila_error_page(camila_get_translation('camila.sqlerror') . ' ' . $_CAMILA['db']->ErrorMsg());
}
$table = $result->fields['tablename'];
$worktablename = $result->fields['short_title'];
$worktablescript = $result->fields['scriptname'];
if ($filename == '' && $result->fields['filename'] != '') {
$filename = $result->fields['filename'];
$sheetnum = $result->fields['sheetnum'];
}
if ($filename != '') {
require_once CAMILA_LIB_DIR . 'php-excel-reader/excel_reader2.php';
$data = new Spreadsheet_Excel_Reader(CAMILA_TMP_DIR . '/' . $filename);
$excelColNames = array();
$i = 0;
while ($data->val(1, $i + 1, $sheetnum) != '') {
$name = $data->val(1, $i + 1, $sheetnum);
$excelColNames[$i] = camila_strtoupper_utf8(isUTF8($name) ? $name : utf8_encode($name));
$i++;
}
$result = $_CAMILA['db']->Execute('select * from ' . CAMILA_TABLE_WORKC . ' where (wt_id=' . $_CAMILA['db']->qstr($id) . ' and is_deleted<>' . $_CAMILA['db']->qstr('y') . ') order by sequence');
if ($result === false) {
camila_error_page(camila_get_translation('camila.sqlerror') . ' ' . $_CAMILA['db']->ErrorMsg());
}
$fields = array();
$types = array();
$defVals = array();
$forceCase = array();
$orig_types = array();
$fieldMapping = array();
$forceArr = camila_get_translation_array('camila.worktable.options.force');
$count = 0;
while (!$result->EOF) {
$colName = $result->fields['col_name'];
$name = camila_strtoupper_utf8($result->fields['name']);
$fieldMapping[$colName] = isUTF8($name) ? $name : utf8_encode($name);
$fields[$count] = $colName;
$types[$count] = $result->fields['type'];
$orig_types[$count] = $result->fields['orig_type'];
$defVals[$count] = $result->fields['default_value'];
$forceCase[$count] = $result->fields['force_case'];
$count++;
$result->MoveNext();
}
$successCount = 0;
$failCount = 0;
//db fields
for ($i = 2; $i <= $data->rowcount($sheetnum); $i++) {
$record = array();
$emptyrow = true;
//db fields
reset($fields);
foreach ($fields as $k => $v) {
//k Field position into database
//k2 Position in Excel file
$k2 = array_search($fieldMapping[$v], $excelColNames);
//Is it in Excel file?
if ($k2 !== false) {
$excelColName = camila_strtoupper_utf8($data->value(1, $k2 + 1, $sheetnum));
//$excelColName = $v;
$worktableColName = array_search($excelColName, $fieldMapping);
$worktableColName = $v;
if ($worktableColName != '') {
if ($types[$k] == 'date' && $data->val($i, $k2 + 1, $sheetnum) != '') {
$numValue = $data->sheets[$sheetnum]['cellsInfo'][$i][$k2 + 1]['raw'];
$utcDays = floor($numValue - ($data->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS));
$utcValue = $utcDays * SPREADSHEET_EXCEL_READER_MSINADAY;
$dateinfo = gmgetdate($utcValue);
$fractionalDay = $numValue - floor($numValue) + 1.0E-7;
// The .0000001 is to fix for php/excel fractional diffs
$totalseconds = floor(SPREADSHEET_EXCEL_READER_MSINADAY * $fractionalDay);
$secs = $totalseconds % 60;
$totalseconds -= $secs;
$hours = floor($totalseconds / (60 * 60));
$mins = floor($totalseconds / 60) % 60;
$dt = date('Y-m-d', mktime($hours, $mins, $secs, $dateinfo["mon"], $dateinfo["mday"], $dateinfo["year"]));
$record[$worktableColName] = $_CAMILA['db']->BindDate($dt);
} elseif ($orig_types[$k] == 'number' && $data->sheets[$sheetnum]['cellsInfo'][$i][$k2 + 1]['raw'] != '') {
$record[$worktableColName] = $data->sheets[$sheetnum]['cellsInfo'][$i][$k2 + 1]['raw'];
} elseif ($types[$k] == 'hyperlink' && $data->hyperlink($i, $k2 + 1, $sheetnum) != '') {
//$record[$worktableColName] = '<a href="' . $data->hyperlink($i, $k2+1, $sheetnum) . '" target="_blank">' . $data->value($i, $k2+1, $sheetnum) . '</a>';
$record[$worktableColName] = $data->hyperlink($i, $k2 + 1, $sheetnum);
} else {
$record[$worktableColName] = $data->value($i, $k2 + 1, $sheetnum);
}
if ($defVals[$k] != '' && $record[$worktableColName] == '') {
$record[$worktableColName] = camila_parse_default_expression($defVals[$k], '_camila_seq_num_', true);
}
if ($record[$worktableColName] != '') {
if ($forceCase[$k] == 'upper') {
$record[$worktableColName] = mb_strtoupper($record[$worktableColName], 'UTF-8');
}
if ($forceCase[$k] == 'lower') {
$record[$worktableColName] = mb_strtolower($record[$worktableColName], 'UTF-8');
}
$emptyrow = false;
}
}
} else {
if ($defVals[$k] != '') {
$record[$fields[$k]] = camila_parse_default_expression($defVals[$k], '_camila_seq_num_', true);
}
}
}
if (!$emptyrow) {
$now = $_CAMILA['db']->BindTimeStamp(gmdate("Y-m-d H:i:s", time()));
$id = $_CAMILA['db']->GenID('worktableseq', 100000);
foreach ($record as $k => $v) {
$record[$k] = str_replace('_camila_seq_num_', $id, $v);
}
$record['id'] = $id;
$record['created'] = $now;
$record['created_by'] = $_CAMILA['user'];
$record['created_src'] = 'import';
$record['created_by_surname'] = $_CAMILA['user_surname'];
$record['created_by_name'] = $_CAMILA['user_name'];
$record['last_upd'] = $now;
$record['last_upd_by'] = $_CAMILA['user'];
$record['last_upd_src'] = 'import';
$record['last_upd_by_surname'] = $_CAMILA['user_surname'];
$record['last_upd_by_name'] = $_CAMILA['user_name'];
$record['mod_num'] = 0;
$insertSQL = $_CAMILA['db']->AutoExecute($table, $record, 'INSERT');
if (!$insertSQL) {
//camila_information_text(camila_get_translation('camila.worktable.db.importerror'));
$failCount++;
$success = false;
} else {
$successCount++;
}
}
}
}
camila_information_text(camila_get_translation('camila.worktable.db.importedrows') . ': ' . $successCount);
camila_information_text(camila_get_translation('camila.worktable.db.skippedrows') . ': ' . $failCount);
@unlink(CAMILA_TMP_DIR . '/' . $filename);
} else {
$result = $_CAMILA['db']->Execute('select tablename, filename, sheetnum from ' . CAMILA_TABLE_WORKT . ' where id=' . $_CAMILA['db']->qstr($id));
if ($result === false) {
camila_error_page(camila_get_translation('camila.sqlerror') . ' ' . $_CAMILA['db']->ErrorMsg());
}
$filename = $result->fields['filename'];
// if ($filename != '') {
$myText = new CHAW_text(camila_get_translation('camila.wizard.choosefileforimport'));
$_CAMILA['page']->add_text($myText);
$form3->draw();
$success = false;
// }
}
if ($success) {
if ($worktablename != '') {
$myLink = new CHAW_link($worktablename, $worktablescript);
$myLink->set_br(0);
$_CAMILA['page']->add_link($myLink);
$myText = new CHAW_text(' - ' . camila_get_translation('camila.worktable.db.importok'));
$_CAMILA['page']->add_text($myText);
} else {
$myText = new CHAW_text(camila_get_translation('camila.wizard.configurationapplied'));
$_CAMILA['page']->add_text($myText);
}
}
}
/**
* Import records from Excel file
*
* @param $file
* @param bool $simulate
*/
private function importRecords($file, $simulate = false)
{
global $ilUser, $lng;
include_once "./Modules/DataCollection/libs/ExcelReader/excel_reader2.php";
$warnings = array();
try {
$excel = new Spreadsheet_Excel_Reader($file);
} catch (Exception $e) {
$warnings[] = $lng->txt("dcl_file_not_readable");
}
if (count($warnings)) {
$this->endImport(0, $warnings);
return;
}
$field_names = array();
for ($i = 1; $i <= $excel->colcount(); $i++) {
$field_names[$i] = $excel->val(1, $i);
}
$fields = $this->getImportFieldsFromTitles($field_names, $warnings);
for ($i = 2; $i <= $excel->rowcount(); $i++) {
$record = new ilDataCollectionRecord();
$record->setTableId($this->table_obj->getId());
$record->setOwner($ilUser->getId());
$date_obj = new ilDateTime(time(), IL_CAL_UNIX);
$record->setCreateDate($date_obj->get(IL_CAL_DATETIME));
$record->setTableId($this->table_id);
if (!$simulate) {
$record->doCreate();
}
foreach ($fields as $col => $field) {
$value = $excel->val($i, $col);
$value = utf8_encode($value);
try {
if ($field->getDatatypeId() == ilDataCollectionDatatype::INPUTFORMAT_REFERENCE) {
$old = $value;
$value = $this->getReferenceFromValue($field, $value);
if (!$value) {
$warnings[] = "(" . $i . ", " . $this->getExcelCharForInteger($col) . ") " . $lng->txt("dcl_no_such_reference") . " " . $old;
}
} else {
if ($field->getDatatypeId() == ilDataCollectionDatatype::INPUTFORMAT_DATETIME) {
$value = array('date' => date('Y-m-d', strtotime($value)), 'time' => '00:00:00');
}
}
$field->checkValidity($value, $record->getId());
if (!$simulate) {
$record->setRecordFieldValue($field->getId(), $value);
}
} catch (ilDataCollectionInputException $e) {
$warnings[] = "(" . $i . ", " . $this->getExcelCharForInteger($col) . ") " . $e;
}
}
if (!$simulate) {
$record->doUpdate();
}
if ($i - 1 > $this->max_imports) {
$warnings[] = $lng->txt("dcl_max_import") . ($excel->rowcount() - 1) . " > " . $this->max_imports;
break;
}
}
$this->endImport($i - 2, $warnings);
}
public function actionImport()
{
$model = new A();
if (isset($_POST['A'])) {
Yii::import('ext.phpexcelreader.excel_reader2', true);
$model->attributes = $_POST['A'];
$import = CUploadedFile::getInstance($model, 'filee');
if ($import == null) {
Yii::app()->user->setFlash('error', 'File Kosong');
$this->redirect(array(''));
} else {
$import->saveAs('coba/' . $import);
}
if ($import->type == "application/ynd.ms.excel") {
$data = new Spreadsheet_Excel_Reader('/../controller/coba1.xls/');
echo $data->dump(true, true);
$id = array();
$nama = array();
for ($j = 2; $j <= $data->rowcount(); $j++) {
if (empty($data->sheets[0]['cells'][$j][1]) || empty($data->sheets[0]['cells'][$j][2])) {
Yii::app()->user->setFlash('error', 'Data Gagal di Import (File excel harus diisi semua)');
$id[$j] = null;
$nama[$j] = null;
} else {
$id[$j] = $data->sheets[0]['cells'][$j][1];
$nama[$j] = $data->sheets[0]['cells'][$j][2];
}
}
$niki = $data->rowcount(0);
for ($i = 1; $i < $niki; $i++) {
$model = new A();
$model->id = $id[$i];
$model->nama = $nama[$i];
$model2 = A::model()->findByPk($id[$i]);
if ($model2 != null) {
Yii::app()->user->setFlash('error', 'Data Gagal di Import (NIDN sudah ada sebelumnya)');
$this->redirect(array('import'));
} else {
$model->save();
}
}
$this->redirect(array('index'));
} else {
Yii::app()->user->setFlash('error', 'Format file tidak dikenali (format file harus .xls)');
$this->redirect(array('import'));
}
}
$this->render('import', array('model' => $model));
/* $model = new A();
if (isset($_POST['A'])) {
$model->attributes = $_POST['A'];
$itu = CUploadedFile::getInstance($model, 'filee');
$path = 'coba1.xls';
//$itu->saveAs($path);
$data = new Spreadsheet_Excel_Reader('coba1.xls');
}*/
}
$extension = array_pop($file);
/* echo "<script type='text/javascript'> alert('".$extension."'); </script>"; */
if (in_array($extension, $allowed_extensions)) {
$source = $_FILES['file']['tmp_name'];
$t = "Subjective" . time() . "" . date('Ymd') . ".xls";
$target = $upload_path . "/" . $t;
$tempUploadedValue = move_uploaded_file($source, $target);
if ($tempUploadedValue) {
/* echo "<script type='text/javascript'> alert('test1'); </script>"; */
if (($handle = fopen($upload_path . '/' . $t, "r")) !== false) {
$_FILES['file']['name'];
$expiryDate = $_POST['expiryDate'];
$data = new Spreadsheet_Excel_Reader($upload_path . '/' . $t);
$data->dump(true, true);
$data_array = array();
for ($i = 1; $i <= $data->rowcount(); $i++) {
$data_array[$i] = array();
for ($j = 1; $j <= $data->colcount(); $j++) {
$data_array[$i][$j] = $data->val($i, $j);
}
// inner For
}
// outer For
$myFile = $t;
$url = "./excelfiles/" . $t;
$newUploadedQuizURL = $newBaseURL . "/excelfiles/" . str_replace(' ', '%20', $myFile);
$newQuizName = $_POST['questionFileName'];
$newQuizFileName = $myFile;
$duration = $_POST['hours'] * 3600 + $_POST['minutes'] * 60;
$questionWeightage = $_POST['questionWeightage'];
$questionQty = $_POST['questionQty'];
$mysqli = new mysqli("localhost", "address_api", "address_api", "address_api");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DELETE FROM jd_wp")) {
die("Table delete failed: (" . $mysqli->errno . ") " . $mysqli->error);
}
/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO jd_wp (`jrd_1`, `jrd_sheet`, `order`, `st_num`, `street`, `jrd_block`, `jrd_address`, `short_own`, `absentee_owner`, `kiva_pin`, `county_apn_link`, `sub_division`, `block`, `lot`, `owner`, `owner_2`, `owner_address`, `owner_city_zip`, `site_address`, `zip_code`, `council_district`, `trash_day`, `school_distrct`, `census_neigh_borhood`, `park_region`, `pw_maintenance_district`, `zoning`, `land_use`, `blvd_front_footage`, `effective_date`, `assessed_land`, `assessed_improve`, `exempt_land`, `exempt_improve`, `square_feet`, `acres`, `perimeter`, `year_built`, `living_area`, `tax_neighborhood_code`, `parcel_area_sf`, `propert_class_pca_code`, `landuse_type`, `market_value`, `taxabl_evalue`, `assessed_value`, `tax_status`, `legal_description`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"))) {
die("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
}
if (!$stmt->bind_param('ssssssssssssssssssssssssssssssssssssssssssssssss', $v[1], $v[2], $v[3], $v[4], $v[5], $v[6], $v[7], $v[8], $v[9], $v[10], $v[11], $v[12], $v[13], $v[14], $v[15], $v[16], $v[17], $v[18], $v[19], $v[20], $v[21], $v[22], $v[23], $v[24], $v[25], $v[26], $v[27], $v[28], $v[29], $v[30], $v[31], $v[32], $v[33], $v[34], $v[35], $v[36], $v[37], $v[38], $v[39], $v[40], $v[41], $v[42], $v[43], $v[44], $v[45], $v[46], $v[47], $v[48])) {
die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
}
$data = new Spreadsheet_Excel_Reader("wp.xls");
$num_rows = $data->rowcount($sheet_index = 0);
$num_cols = $data->colcount($sheet_index = 0);
print "{$num_rows},{$num_cols}\n\n";
$types = '';
$vars = '';
for ($r = 2; $r < $num_rows; $r++) {
print "row={$r} \n";
for ($i = 1; $i < $num_cols; $i++) {
$v[$i] = $data->val($r, $i);
print "\$v[{$i}] = |" . $v[$i] . "|\n";
}
$v[48] = substr($v[48], 0, 200);
$v[28] = substr($v[28], 0, 5);
if (!$stmt->execute()) {
die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
}
if ($filetype == "csv") {
/* get file to string */
$outFile = file_get_contents('csvupload/import.csv') or die(_('<div class="alert alert alert-danger">Cannot open csvupload/import.csv</div>'));
/* format file */
$outFile = str_replace(array("\r\n", "\r"), "\n", $outFile);
//replace windows and Mac line break
$outFile = explode("\n", $outFile);
} else {
/* include functions */
require_once '../../functions/functions.php';
/* get excel file */
require_once '../../functions/excel_reader2.php';
//excel reader 2.21
$data = new Spreadsheet_Excel_Reader('csvupload/import.xls', false);
//get number of rows
$numRows = $data->rowcount(0);
$numRows++;
//get custom fields
$myFields = getCustomFields('ipaddresses');
$myFieldsSize = sizeof($myFields);
//add custom fields
$numRows = $numRows + $myFieldsSize;
//get all to array!
for ($m = 0; $m < $numRows; $m++) {
$outFile[$m] = $data->val($m, 'A') . ',' . $data->val($m, 'B') . ',' . $data->val($m, 'C') . ',' . $data->val($m, 'D') . ',
' . $data->val($m, 'E') . ',' . $data->val($m, 'F') . ',' . $data->val($m, 'G') . ',' . $data->val($m, 'H') . ',
' . $data->val($m, 'I');
//add custom fields
if (sizeof($myFields) > 0) {
$currLett = "J";
foreach ($myFields as $field) {
require_once 'excel_reader.php';
if (mysql_select_db("home365_ios", $useradmin)) {
} else {
echo "Error selecting database, exited.";
exit;
}
function null_2_default($string, $default)
{
return isset($string) && $string != '' ? $string : $default;
}
$current_timestamp = get_GMT(microtime(true));
$current_time = date("Y-m-d H:i:s", $current_timestamp);
$data = new Spreadsheet_Excel_Reader("mls.xls");
$sheet_count = $data->sheetcount();
for ($sheet_index = 0; $sheet_index < $sheet_count; $sheet_index++) {
$row_count = $data->rowcount($sheet_index);
$col_count = $data->colcount($sheet_index);
echo "Sheet:{$sheet_index}<br/>";
echo "Row Count:" . $row_count . "<br/>";
echo "Column Count:" . $col_count . "<br/>";
for ($row_index = 1; $row_index <= $row_count; $row_index++) {
for ($col_index = 1; $col_index <= $col_count; $col_index++) {
if ($row_index > 1 && $col_index <= 3) {
if ($col_index == 1) {
$month = $data->val($row_index, $col_index, $sheet_index) === '' ? $month : $data->val($row_index, $col_index, $sheet_index);
}
if ($col_index == 2) {
$year = $data->val($row_index, $col_index, $sheet_index) === '' ? $year : $data->val($row_index, $col_index, $sheet_index);
}
if ($col_index == 3) {
$property_type = $data->val($row_index, $col_index, $sheet_index) === '' ? $property_type : $data->val($row_index, $col_index, $sheet_index);
/**
* Read Excel file
*
* @param string $file_name
* @return array
*/
public static function readXlsFile($file_name)
{
$excel = new Spreadsheet_Excel_Reader(self::TEMP_FOLDER . $file_name);
return array($excel->rowcount(), $excel->colcount());
}
public function importexcel()
{
if ($this->my_usession->logged_in) {
$fileupload = $_FILES['fileexcel']['tmp_name'];
$namafile = $_FILES['fileexcel']['name'];
$path = 'assets/resources/data/';
$pathfile = $path . $namafile;
if (move_uploaded_file($fileupload, $pathfile)) {
include_once APPPATH . "libraries/excelreader.php";
$data = new Spreadsheet_Excel_Reader($pathfile);
$baris = $data->rowcount($sheet_index = 0) + 1;
$temp_query = array();
for ($a = 2; $a < $baris; $a++) {
$area = array();
if ($data->val($a, 14) == '') {
$area[0] = '1';
} else {
$area = explode(',', $data->val($a, 14));
}
$areaid = $this->device_model->areaonall($area);
$jumar = count($area) - 1;
$savear = array();
$useride = $data->val($a, 1);
if ($useride != '') {
$SN = $this->employe_model->getsn($areaid);
$command = array();
if (isset($SN)) {
foreach ($SN->result() as $esen) {
$command = array('sn' => $esen->sn, 'cmd' => 'DATA USER PIN=' . $data->val($a, 1) . ' Name=' . $data->val($a, 4) . ' Passwd=' . $data->val($a, 5) . ' Card=' . $data->val($a, 6) . ' Grp= TZ= Pri=' . $data->val($a, 7), 'status' => 1, 'submittime' => date("Y-m-d H:i:s"));
$this->db->insert('command', $command);
}
}
$tempaa = array('userid' => (string) $data->val($a, 1), 'badgenumber' => (string) $data->val($a, 2), 'name' => $data->val($a, 3), 'nickname' => $data->val($a, 4), 'password' => $data->val($a, 5), 'card' => $data->val($a, 6), 'privilege' => $data->val($a, 7) != '' ? $data->val($a, 7) : 0, 'deptid' => $data->val($a, 8) != '' ? $data->val($a, 8) : '1', 'title' => $data->val($a, 9), 'gender' => (int) $data->val($a, 10), 'birthdate' => $data->val($a, 11) != '' ? date('Y-m-d', strtotime($data->val($a, 11))) : null, 'hireddate' => $data->val($a, 12) != '' ? date('Y-m-d', strtotime($data->val($a, 12))) : null, 'email' => $data->val($a, 13));
$tempaaupdate = array('badgenumber' => (string) $data->val($a, 2), 'name' => (string) $data->val($a, 3), 'nickname' => $data->val($a, 4), 'password' => $data->val($a, 5), 'card' => $data->val($a, 6), 'privilege' => $data->val($a, 7) != '' ? $data->val($a, 7) : 0, 'deptid' => $data->val($a, 8) != '' ? $data->val($a, 8) : '1', 'title' => $data->val($a, 9), 'gender' => (int) $data->val($a, 10), 'birthdate' => $data->val($a, 11) != '' ? date('Y-m-d', strtotime($data->val($a, 11))) : null, 'hireddate' => $data->val($a, 12) != '' ? date('Y-m-d', strtotime($data->val($a, 12))) : null, 'email' => $data->val($a, 13));
if ($this->db->insert('userinfo', $tempaa)) {
foreach ($area as $aarreeaa) {
$savear = array('userid' => (string) $data->val($a, 1), 'areaid' => $aarreeaa);
if ($this->db->insert('userinfo_attarea', $savear)) {
}
}
} else {
$this->db->where('userid', (string) $data->val($a, 1));
$this->db->update('userinfo', $tempaaupdate);
$userareaactive = array();
$userarea = $this->employe_model->getareauserinfo((string) $data->val($a, 1));
foreach ($userarea->result() as $usea) {
$userareaactive[] = $usea->areaid;
}
$del = array_diff($userareaactive, $area);
$add = array_diff($area, $userareaactive);
foreach ($del as $dele) {
$serialno = $this->employe_model->getsnarea($dele);
if (isset($serialno)) {
foreach ($serialno->result() as $serno) {
$comdev = array('sn' => $serno->sn, 'cmd' => 'DATA DEL_USER PIN=' . $data->val($a, 1), 'status' => 1, 'submittime' => date("Y-m-d H:i:s"));
$this->db->insert('command', $comdev);
}
}
$this->db->delete('userinfo_attarea', array('userid' => (string) $data->val($a, 1), 'areaid' => $dele));
}
foreach ($add as $aarreeaa) {
$savear = array('userid' => (string) $data->val($a, 1), 'areaid' => $aarreeaa);
if ($this->db->insert('userinfo_attarea', $savear)) {
}
}
}
}
}
$actionlog = array('user' => $this->my_usession->userdata('username'), 'ipadd' => $this->ipaddress->get_ip(), 'logtime' => date("Y-m-d H:i:s"), 'logdetail' => 'Import employee data', 'info' => $this->lang->line('message_success'));
$this->db->insert('actionlog', $actionlog);
$hasil = array("responseText" => "success", "success" => true);
echo json_encode($hasil);
} else {
$actionlog = array('user' => $this->my_usession->userdata('username'), 'ipadd' => $this->ipaddress->get_ip(), 'logtime' => date("Y-m-d H:i:s"), 'logdetail' => 'Import employee data', 'info' => 'Failed');
$this->db->insert('actionlog', $actionlog);
$hasil = array("responseText" => "Import failed!", "success" => false);
echo json_encode($hasil);
}
} else {
$hasil = array("success" => false, "responseText" => "notlogin");
echo json_encode($hasil);
}
}
/**
* Creates a new model.
* If creation is successful, the browser will be redirected to the 'view' page.
*/
public function actionCreate()
{
$model = new PhoneModel();
$message = "";
$errorList = array();
// Uncomment the following line if AJAX validation is needed
// $this->performAjaxValidation($model);
if (isset($_POST['source_name'], $_POST['group_id'])) {
$fileName = _APP_PATH_ . DS . "data" . DS . "tmp" . DS . $_POST['source_name'];
$group_id = $_POST['group_id'];
//$fileName = "D:\\chacha_cloud\\src\\trunk\chacha\data\\tmp\\20120713170547_phone_list.xls";
try {
require_once 'excel_reader2.php';
$data = new Spreadsheet_Excel_Reader($fileName, true, "UTF-8");
// khoi tao doi tuong doc file excel
$rowsnum = $data->rowcount($sheet_index = 0);
// lay so hang cua sheet
$colsnum = $data->colcount($sheet_index = 0);
// lay so cot cua sheet
for ($i = 2; $i <= $rowsnum; $i++) {
// doc tu hang so 2 vi hang 1 la tieu de roi!
$phoneNum = $data->val($i, 1);
// xuat cot so 1 va cot so 2 tren cung 1 hang
// check so dien thoai xem co dung cua Vinaphone ko
try {
$phoneNum = Formatter::formatPhone($phoneNum);
if (Formatter::isVinaphoneNumber($phoneNum)) {
$model->phone = "{$phoneNum}";
$model->group_id = $group_id;
$model->status = 0;
$model->created_time = date("Y-m-d H:i:s");
var_dump($model->phone);
try {
if ($model->save()) {
$message = yii::t('SpamModule', 'Upload thành công');
} else {
print_r($model->getErrors());
exit;
}
} catch (Exception $exc) {
echo $exc->getTrace();
}
} else {
//echo so dien thoai ko dung
$errorList[] = $phoneNum;
}
} catch (Exception $exc) {
echo $exc->getMessage();
}
}
} catch (Exception $exc) {
echo $exc->getMessage();
}
}
$uploadModel = new XUploadForm();
$tmpArr = GroupModel::model()->findAll();
$smsGroup = array();
foreach ($tmpArr as $smsG) {
$smsGroup[$smsG->id] = $smsG->name;
}
$this->render('create', array('model' => $model, 'uploadModel' => $uploadModel, 'message' => $message, 'smsGroup' => $smsGroup, 'errorList' => $errorList));
}
public function importexcel()
{
$fileupload = $_FILES['fileexcel']['tmp_name'];
$namafile = $_FILES['fileexcel']['name'];
$path = 'assets/resources/data/';
$pathfile = $path . $namafile;
if (move_uploaded_file($fileupload, $pathfile)) {
include_once APPPATH . "libraries/excelreader.php";
$data = new Spreadsheet_Excel_Reader($pathfile);
$baris = $data->rowcount($sheet_index = 0) + 1;
$temp_query = array();
for ($a = 2; $a < $baris; $a++) {
$temp_query = array('code_shift' => $data->val($a, 1), 'name_shift' => $data->val($a, 2), 'start_in' => $data->val($a, 3), 'check_in' => $data->val($a, 4), 'end_check_in' => $data->val($a, 5), 'start_out' => $data->val($a, 10), 'check_out' => $data->val($a, 11), 'end_check_out' => $data->val($a, 12), 'start_break' => $data->val($a, 6) != '' ? $data->val($a, 6) : '00:00:00', 'break_out' => $data->val($a, 7) != '' ? $data->val($a, 7) : '00:00:00', 'break_in' => $data->val($a, 8) != '' ? $data->val($a, 8) : '00:00:00', 'end_break' => $data->val($a, 9) != '' ? $data->val($a, 9) : '00:00:00', 'ot_tolerance' => $data->val($a, 13), 'late_tolerance' => $data->val($a, 14), 'early_departure' => $data->val($a, 15), 'shift_in' => $data->val($a, 16), 'shift_out' => $data->val($a, 17), 'in_ot_tolerance' => $data->val($a, 18), 'out_ot_tolerance' => $data->val($a, 19), 'colour_shift' => $data->val($a, 20));
$this->db->insert('master_shift', $temp_query);
$fp = fopen('assets/js/template/shiftcolor.css', 'a');
fwrite($fp, "." . $data->val($a, 1) . " .shiftcolor { background-color: " . $data->val($a, 20) . " !important;} .x-grid-row ." . $data->val($a, 1) . " { background-color: " . $data->val($a, 20) . " !important;} .x-grid-row ." . $data->val($a, 1) . "-AB { background-image: url('images/icons/absence.png'); background-color: " . $data->val($a, 20) . " !important;} .x-grid-row ." . $data->val($a, 1) . "-AT { background: url('images/icons/attendance.png'); background-color: " . $data->val($a, 20) . " !important;} .x-grid-row ." . $data->val($a, 1) . "-NWDS { color: red; background-color: " . $data->val($a, 20) . " !important;} .x-grid-row ." . $data->val($a, 1) . "-NWDSAB { background-image: url('images/icons/absence.png'); color: red; background-color: " . $data->val($a, 20) . " !important;} .x-grid-row ." . $data->val($a, 1) . "-NWDSAT { background: url('images/icons/attendance.png'); color: red; background-color: " . $data->val($a, 20) . " !important;}");
fclose($fp);
}
$hasil = array("responseText" => "success", "success" => true);
echo json_encode($hasil);
} else {
$hasil = array("responseText" => $this->lang->line('message_failimport'), "success" => false);
echo json_encode($hasil);
}
}
