/**
* 获取数据
* @param int $page 第几页数据
*/
public function getData($page)
{
$this->_getReadFileObj();
$phpExcel = $this->_instance->load($this->_filePath);
$currentSheet = $phpExcel->getSheet($page);
return $currentSheet->toArray();
}
/**
* 读取excel信息
* xls,xlsx,
*
* @param string $fileName 文件绝对路径
* @param bool $dataArea excel区域
*
* @return array() | string
*/
public static function readExcel($fileName = '', $contentType = false, $dataArea = false)
{
ini_set('memory_limit', '300M');
$ext = strtolower(substr(strrchr($fileName, '.'), 1));
//2003 excel
if ($ext == 'xls') {
$objReader = new PHPExcel_Reader_Excel5();
}
//2007 excel
if ($ext == 'xlsx') {
$objReader = new PHPExcel_Reader_Excel2007();
}
$objPHPExcel = $objReader->load($fileName);
if ($dataArea == true) {
$objPHPExcel->setActiveSheetIndex(3);
}
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
if ($contentType) {
//返回字符串
$str = '';
foreach ($sheetData as $k => $v) {
foreach ($v as $kk => $vv) {
$str .= $vv;
}
}
return $str;
}
return $sheetData;
}
/**
* @param $excelFileName
* @param $excelFileExts
* @return array
* @throws \PHPExcel_Exception
*/
private function _excelParse($excelFileName, $excelFileExts)
{
import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.Shared.Date");
$PHPDate = new \PHPExcel_Shared_Date();
if ($excelFileExts == 'xls') {
import("Org.Util.PHPExcel.Reader.Excel5");
$PHPReader = new \PHPExcel_Reader_Excel5();
} else {
if ($excelFileExts == 'xlsx') {
import("Org.Util.PHPExcel.Reader.Excel2007");
$PHPReader = new \PHPExcel_Reader_Excel2007();
}
}
$PHPExcel = $PHPReader->load($excelFileName);
// 获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
$currentSheet = $PHPExcel->getSheet(0);
// 获取总列数
$allColumn = $currentSheet->getHighestColumn();
// 获取总行数
$allRow = $currentSheet->getHighestRow();
// 循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
// 从哪列开始，A表示第一列
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
// 数据坐标
$address = $currentColumn . $currentRow;
$excelData[$currentRow][$currentColumn] = (string) $currentSheet->getCell($address)->getValue();
}
}
return ['path' => $excelFileName, 'data' => $excelData];
}
public function testSave()
{
$objReader = new \PHPExcel_Reader_Excel2007();
$excel = $objReader->load('tests/metadata/template/simple.xlsx');
$actual = $this->sut->save($excel, 'test.xlsx');
$this->assertEquals('tests/metadata/template/test.xlsx', $actual);
$this->assertFileExists('tests/metadata/template/test.xlsx');
}
public function getWorkbook($isExcelFile = false)
{
$data = $this->getReportData();
$xls_reader = new PHPExcel_Reader_Excel2007();
$workbook = $xls_reader->load(APPLICATION_PATH . 'modules/stat/templates/report6.xlsx');
$sheet = $workbook->getActiveSheet();
$sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(2, 4);
// В шаблоне пока не работает
// $sheet->getSheetView()->setZoomScale(120);
$guide_vuz = $this->getEstablishments('GUIDE_STRUCTURE', 'SORTORDER');
$guide_eduform = $this->getGuideItems('T_EDUFORM');
$guide_speciality = $this->getSpecialities(false, false, 'CT.SORTORDER', 'SORTORDER');
$eduFormStr = mb_strtolower($guide_eduform[$this->params['eduform']], 'utf8');
$endDate = new DateTime($this->getAddValueDate($this->params['period'], '_ADD_ENDDATE'));
$endDateStr = $endDate->format('d.m.Y');
$sheet->setCellValue('A1', sprintf($sheet->getCell('A1')->getValue(), $endDateStr, $eduFormStr));
$lastEstNum = -1;
$vuznum = 1;
$current_row = 5;
$sheet->insertNewRowBefore($current_row + 1, count($data));
foreach ($data as $datarow) {
$curEstNum = $datarow['ESTABLISHMENT_NUM'];
if ($lastEstNum != $curEstNum) {
$lastEstNum = $curEstNum;
if ($vuznum > 1) {
// Разделительная линия между вузами
$sheet->insertNewRowBefore($current_row + 1, 1);
$sheet->getRowDimension($current_row)->setRowHeight(0.75);
$current_row++;
}
$sheet->insertNewRowBefore($current_row + 1, 1);
$sheet->setCellValueByColumnAndRow(0, $current_row, $vuznum);
$sheet->setCellValueByColumnAndRow(1, $current_row, $guide_vuz[$curEstNum]);
$sheet->mergeCellsByColumnAndRow(1, $current_row, 9, $current_row);
$sheet->getStyle("B{$current_row}:J{$current_row}")->getFont()->setBold(true);
$sheet->getStyle("A{$current_row}:J{$current_row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($this->colorLevel1);
$current_row++;
$vuznum++;
}
// $sheet->getRowDimension($current_row)->setOutlineLevel(1);//->setVisible(false);
if (!$datarow['SPECIALITY_NUM']) {
$sheet->setCellValueByColumnAndRow(1, $current_row, 'Вакантних місць немає');
$sheet->mergeCellsByColumnAndRow(1, $current_row, 9, $current_row);
$sheet->getStyle("B{$current_row}:J{$current_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$current_row++;
continue;
}
$sheet->setCellValueByColumnAndRow(1, $current_row, $guide_speciality[$datarow['SPECIALITY_NUM']]);
$sheet->setCellValueByColumnAndRow(2, $current_row, "=SUM(D{$current_row}:J{$current_row})");
for ($col = 1; $col <= 7; $col++) {
$sheet->setCellValueByColumnAndRow($col + 2, $current_row, $datarow["C{$col}"]);
}
$current_row++;
}
$sheet->removeRow($current_row, 1);
return $workbook;
}
protected function import_data($parentFile, $childFile, $parentExts = 'xls', $childExts = 'xls')
{
//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
import("Org.Util.PHPExcel");
//创建PHPExcel对象，注意，不能少了\
$PHPExcel = new \PHPExcel();
//如果excel文件后缀名为.xls，导入这个Excel5类否则导入Excel2007
if ($parentExts == 'xls' && $childExts == 'xls') {
import("Org.Util.PHPExcel.Reader.Excel5");
$PHPReader = new \PHPExcel_Reader_Excel5();
} else {
if ($parentExts == 'xlsx' && $childExts == 'xlsx') {
import("Org.Util.PHPExcel.Reader.Excel2007");
$PHPReader = new \PHPExcel_Reader_Excel2007();
}
}
//载入上传的文件
$PHPExcel = $PHPReader->load($parentFile);
$ParentExcel = $PHPReader->load($parentFile);
$ChildExcel = $PHPReader->load($childFile);
//获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
$currentSheet = $PHPExcel->getSheet(0);
$parentCurrentSheet = $ParentExcel->getSheet(0);
$childCurrentSheet = $ChildExcel->getSheet(0);
//获取工作表总列数
$allColumn = $currentSheet->getHighestColumn();
$parentAllColumn = $parentCurrentSheet->getHighestColumn();
$childAllColumn = $childCurrentSheet->getHighestColumn();
//获取工作表总行数
$allRow = $currentSheet->getHighestRow();
$parentAllRow = $parentCurrentSheet->getHighestRow();
$childAllRow = $childCurrentSheet->getHighestRow();
//循环获取表中的数据，$j表示当前行，从哪行开始读取数据，索引值从0开始
for ($j = 2; $j <= $allRow; $j++) {
$str = "";
//从A列读取数据
for ($k = 'A'; $k <= $allColumn; $k++) {
$str .= $PHPExcel->getActiveSheet()->getCell("{$k}{$j}")->getValue() . ',';
}
//获取的字符串截取成数组
$strs = explode(",", $str);
$this->install_data($strs);
}
}
/**
* Reads the file into a PHPExcel object.
*
* @param string $filename The file to parse.
*
* @return \PHPExcel Returns the PHPExcel object for the file.
*
* @throws ParseException when the reader encounters an error.
*/
private static function readFile(string $filename) : \PHPExcel
{
try {
$reader = new \PHPExcel_Reader_Excel2007();
$reader->setReadDataOnly(true);
$xlsx = $reader->load($filename);
} catch (\Exception $e) {
throw new ParseException($e->getMessage());
}
return $xlsx;
}
/**
*
* @param string $filePath
* @return
*/
private function readExcel($filePath)
{
$filePath = $this->dir() . $filePath;
if (!is_file($filePath)) {
return FALSE;
}
$PHPReader = new \PHPExcel_Reader_Excel2007();
if ($PHPReader->canRead($filePath)) {
return $PHPReader->load($filePath);
}
return FALSE;
}
public function readExcel($inputFileName)
{
echo 'Loading file ', pathinfo($inputFileName, PATHINFO_BASENAME), ' using PHPExcel_Reader_Excel5<br />';
// $objReader = new PHPExcel_Reader_Excel5();
$objReader = new PHPExcel_Reader_Excel2007();
// $objReader = new PHPExcel_Reader_Excel2003XML();
// $objReader = new PHPExcel_Reader_OOCalc();
// $objReader = new PHPExcel_Reader_SYLK();
// $objReader = new PHPExcel_Reader_Gnumeric();
// $objReader = new PHPExcel_Reader_CSV();
$objPHPExcel = $objReader->load($inputFileName);
// echo '<hr />';
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
return $sheetData;
}
public function goods_import($filename, $exts = 'xls')
{
//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
require_once APP_PATH . 'PHPExcel/PHPExcel.php';
require_once APP_PATH . 'PHPExcel/PHPExcel/Reader/Excel5.php';
// 用于其他低版本xls
require_once APP_PATH . 'PHPExcel/PHPExcel/Reader/Excel2007.php';
// 用于 excel-2007 格式
//创建PHPExcel对象，注意，不能少了\
$PHPExcel = new PHPExcel();
//如果excel文件后缀名为.xls，导入这个类
if ($exts == 'xls') {
$PHPReader = new PHPExcel_Reader_Excel5();
} elseif ($exts == 'xlsx') {
$PHPReader = new PHPExcel_Reader_Excel2007();
} else {
exit('错误的格式');
}
//载入文件
$PHPExcel = $PHPReader->load($filename);
//获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
$currentSheet = $PHPExcel->getSheet(0);
//获取总列数
$allColumn = $currentSheet->getHighestColumn();
//获取总行数
$allRow = $currentSheet->getHighestRow();
//循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
//从哪列开始，A表示第一列
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
//数据坐标
$address = $currentColumn . $currentRow;
//读取到的数据，保存到数组$arr中
$data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
}
}
foreach ($data as $info) {
$order_id = $info['A'];
$name = $info['G'];
$num = $info['H'];
if (!empty($order_id) && !empty($name) && !empty($num)) {
$data['id'] = $order_id;
$data['order_info'] = json_encode(array('num' => $num, 'name' => $name));
$result = M("Order")->save($data);
$result = R("Api/Api/publish", array($order_id));
}
}
}
/**
* @dataProvider providerDump
*/
public function testDump(Config $config, $withDate, $generatedValues, $expectedFile, $expected)
{
$dumper = new ExcelDumper();
$dumper->initialize($config, self::$cacheDir, $withDate);
foreach ($generatedValues as $row) {
$dumper->dumpRow($row);
}
$filename = $dumper->finalize();
$this->assertFileExists($filename);
$ext = 'xlsx';
$this->assertRegExp('/' . basename($expectedFile, '.' . $ext) . '_\\d{4}-\\d{2}-\\d{2}_\\d{2}-\\d{2}-\\d{2}.' . $ext . '/', basename($filename));
$reader = new \PHPExcel_Reader_Excel2007();
$excel = $reader->load(self::$fixtures . '/' . $expectedFile);
$data = $excel->getActiveSheet()->toArray();
$this->assertEquals($expected, $data);
}
/**
* Loads source data from local file into memory.
*/
private function load_source_data_from_file()
{
require_once DOC_ROOT . '/vendor/PHPExcel/Classes/PHPExcel.php';
// NOTE: In development it takes 24 Seconds to load and uses 318 MB of memory.
$start = microtime(true);
debug('Loading source file.');
try {
$reader = new \PHPExcel_Reader_Excel2007();
$reader->setReadDataOnly(true);
$this->source_loaded = $reader->load($this->source_file_path);
unset($reader);
} catch (\Exception $e) {
throw new \Exception('Error loading source data from ' . "{$this->source_file_path}: {$e->getMessage()}");
}
$this->profile($start);
}
private function read_excel($filename, $extension = 'xls')
{
//导入PHPExcel类库
import('ORG.Util.PHPExcel');
$PHPExcel = new PHPExcel();
//根据扩展名判断所用的解析方式
if ($extension == 'xls') {
import("ORG.Util.PHPExcel.Reader.Excel5");
$PHPReader = new PHPExcel_Reader_Excel5();
} else {
if ($extension == 'xlsx') {
import("ORG.Util.PHPExcel.Reader.Excel2007");
$PHPReader = new PHPExcel_Reader_Excel2007();
}
}
//读取excel文件
$PHPExcel = $PHPReader->load($filename);
//获取Sheet0中的数据
$currentSheet = $PHPExcel->getSheet(0);
//获取行数、列数
$allColumn = $currentSheet->getHighestColumn();
$allRow = $currentSheet->getHighestRow();
//循环遍历excel表格，重点是getCell和getValue方法
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
$address = $currentColumn . $currentRow;
$data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
}
}
//循环所得数据插入数据库，至于从2开始嘛，执行var_dump($data)你就懂了
// echo "<pre>";
// var_dump($data);die;
for ($i = 2; $i < count($data); $i++) {
$school = array('school' => $data[$i]['B']);
$find = M('school')->where($school)->find();
if ($find) {
$school_id = $find['id'];
} else {
$school_id = M('school')->data($school)->add();
}
$joiner = array('username' => $data[$i]['C'], 'school_id' => $school_id);
M('joiner')->data($joiner)->add();
}
//然而我并没有想出该怎么判断成功和失败
$this->success('Excel导入成功');
}
public function anyExcel()
{
$pFile = iconv('utf-8', 'gbk', 'C:/Users/LJ/Desktop/北邮/灾难恢复方案评估表-关.xlsx');
$reader = new \PHPExcel_Reader_Excel2007();
$workbook = $reader->load($pFile);
$sheet = $workbook->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$table = [];
for ($row = 1; $row < $highestRow; $row++) {
$table[] = [];
for ($column = 'A'; $column < $highestColumn; $column++) {
$coordinate = $column . $row;
$table[$row - 1][] = $sheet->getCell($coordinate)->getValue();
}
}
return view('bupt.user.excel', ['table' => $table]);
}
public function getWorkbook()
{
$data = $this->getReportData();
$data = $this->formtable($data);
$xls_reader = new PHPExcel_Reader_Excel2007();
$workbook = $xls_reader->load(APPLICATION_PATH . 'modules/stat/templates/report1.xlsx');
$workbook->getActiveSheet()->setCellValueByColumnAndRow(0, 1, $this->getGuideNodeTitle($this->params['establishment']));
if (count($data) > 2) {
$workbook->getActiveSheet()->insertNewRowBefore(5, count($data) - 2);
}
foreach ($data as $rownum => $row) {
$workbook->getActiveSheet()->setCellValueByColumnAndRow(0, $rownum + 4, $row['title']);
foreach (array('CS', 'CS1', 'CS2', 'CS3', 'CS4', 'CS5', 'CS6', 'CS7', 'CIN', 'CIN1', 'CIN2', 'CIN3', 'CIN4', 'CIN5', 'CIN6', 'CIN7', 'COUT', 'COUT1', 'COUT2', 'COUT3', 'COUT4', 'COUT5', 'COUT6', 'COUT7') as $colnum => $column) {
$workbook->getActiveSheet()->setCellValueByColumnAndRow($colnum + 1, $rownum + 4, array_key_exists($column, $row) ? $row[$column] : 0);
}
}
return $workbook;
}
protected function goods_import($filename, $exts = 'xls')
{
//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
import("Org.Util.PHPExcel");
//创建PHPExcel对象，注意，不能少了\
$PHPExcel = new \PHPExcel();
//如果excel文件后缀名为.xls，导入这个类
if ($exts == 'xls') {
import("Org.Util.PHPExcel.Reader.Excel5");
$PHPReader = new \PHPExcel_Reader_Excel5();
} else {
if ($exts == 'xlsx') {
import("Org.Util.PHPExcel.Reader.Excel2007");
$PHPReader = new \PHPExcel_Reader_Excel2007();
}
}
//载入文件
$PHPExcel = $PHPReader->load($filename);
//获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
$currentSheet = $PHPExcel->getSheet(0);
//获取总列数
$allColumn = $currentSheet->getHighestColumn();
//获取总行数
$allRow = $currentSheet->getHighestRow();
//循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
//从哪列开始，A表示第一列
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
//数据坐标
$address = $currentColumn . $currentRow;
//读取到的数据，保存到数组$arr中
$data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
}
}
$this->save_import($data);
}
/**
* 读取Excel
*/
public static function reader($file)
{
import("Tools.Excel.PHPExcel");
if (self::_getExt($file) == 'xls') {
import("Tools.Excel.PHPExcel.Reader.Excel5");
$PHPReader = new \PHPExcel_Reader_Excel5();
} elseif (self::_getExt($file) == 'xlsx') {
import("Tools.Excel.PHPExcel.Reader.Excel2007");
$PHPReader = new \PHPExcel_Reader_Excel2007();
} else {
return false;
}
$PHPExcel = $PHPReader->load($file);
$currentSheet = $PHPExcel->getSheet(0);
$allColumn = $currentSheet->getHighestColumn();
$allRow = $currentSheet->getHighestRow();
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
$address = $currentColumn . $currentRow;
$arr[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
}
}
return $arr;
}
/**
* 生成订单附件
* @param $order_data 要写入订单模板的数据数组
* @param $attachment_path = '' 附件生成路径
* @return string|bool
*/
public static function create_order_attachment($order_data, $attachment_path = '')
{
if (!empty($order_data) && is_array($order_data)) {
// 引入PHPExcel扩展
Yii::createComponent('application.extensions.excel.PHPExcel');
// 租车订单Excel附件 - 模板目录
$templet_path = ASSETS_FILE . 'templet/tichedan.xls';
// 租车订单Excel附件 - 正式目录
$attachment_path = $attachment_path == '' ? SECRET_FILE_PATH . 'Carrental/' . date('Y-m-d', time()) . '/' : $attachment_path;
try {
// 正式目录不存在则创建
if (!file_exists($attachment_path)) {
if (!DirectoryFile::dirCreate($attachment_path)) {
throw new Exception('创建目录失败！');
}
}
// 如模板文件存在则复制到正式目录下
if (file_exists($templet_path)) {
// 生成附件名称
$attachment_name = 'order_' . time() . '_' . rand(100, 10000) . '.xls';
// 复制租车订单模板至正式目录下
$status = @copy($templet_path, $attachment_path . $attachment_name);
if (!$status) {
throw new Exception('模板文件移动失败！');
}
} else {
throw new Exception('订单模板文件不存在！');
}
// 实例化Excel读取类
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($attachment_path . $attachment_name)) {
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($attachment_path . $attachment_name)) {
throw new Exception('不是一个Excel文件！');
}
}
// 读取Excel工作表
$PHPExcel = $PHPReader->load($attachment_path . $attachment_name);
$currentSheet = $PHPExcel->getSheet(0);
// 重写Excel工作表 将单项信息写入Excel单元格
$currentSheet->setCellValue('B3', !empty($order_data[0]) ? $order_data[0] : '');
// 预定号[reservation number]
$currentSheet->setCellValue('D3', $order_data[1]);
// 确认号码[confirmation number]
$currentSheet->setCellValue('B5', $order_data[2]);
// 租车人姓名[Name]
$currentSheet->setCellValue('D5', $order_data[3]);
// 航班号码[Flight number]
$currentSheet->setCellValue('B19', $order_data[4]);
// 车辆代码[Car Code]
$currentSheet->setCellValue('D19', $order_data[5]);
// 车门数[Doors]
$currentSheet->setCellValue('D22', $order_data[6]);
// 空调[Air Con]
$currentSheet->setCellValue('D20', $order_data[7]);
// 座位数[Car Seats]
$currentSheet->setCellValue('B9', $order_data[8]);
// 取车日期
$currentSheet->setCellValue('B10', $order_data[9]);
// 取车时间
$currentSheet->setCellValue('B11', $order_data[10]);
// 取车门店[Location]
$currentSheet->setCellValue('B12', $order_data[11]);
// 取车地址[Address]
$currentSheet->setCellValue('B13', $order_data[12]);
// 取车地电话[Tel]
$currentSheet->setCellValue('D9', $order_data[13]);
// 还车日期
$currentSheet->setCellValue('D10', $order_data[14]);
// 还车时间
$currentSheet->setCellValue('D11', $order_data[15]);
// 还车门店[Location]
$currentSheet->setCellValue('D12', $order_data[16]);
// 还车地址[Address]
$currentSheet->setCellValue('D13', $order_data[17]);
// 还车地电话[Tel]
$currentSheet->setCellValue('B23', $order_data[18]);
// 确认号码[confirmation number]
$currentSheet->setCellValue('B18', $order_data[19]);
// 车行名称Supplier
$currentSheet->setCellValue('B20', $order_data[20]);
// 车辆类型 Car Type
$currentSheet->setCellValue('D21', $order_data[21]);
//变速器类型transmission
$currentSheet->setCellValueExplicit('B22', $order_data[22], PHPExcel_Cell_DataType::TYPE_STRING);
//车行的账户号transmission
$currentSheet->setCellValue('A26', $order_data[23]);
//里程限制
$currentSheet->setCellValue('A27', $order_data[24]);
//车辆保险
$currentSheet->setCellValue('A28', $order_data[25]);
//车辆包含的税费
$currentSheet->setCellValue('A30', $order_data[26]);
//预定的额外服务
// 执行Excel写入操作
$PHPWriter = new PHPExcel_Writer_Excel5($PHPExcel);
// 生成订单附件
// $PHPWriter->save(iconv('utf - 8', 'gbk', $attachment_path . $attachment_name));
$PHPWriter->save($attachment_path . $attachment_name);
} catch (Exception $e) {
echo $e->getMessage();
}
echo '<script language="javascript" type="text/javascript">window.top.window.stopUpload(1);</script >';
return $attachment_path . $attachment_name;
} else {
exit('参数不正确！');
}
}
if (copy($_FILES['excel']['tmp_name'], $destino)) {
?>
<!--
<label for=" ">Archivo Cargado Con Éxito</label>-->
<?php
} else {
echo "Error Al Cargar el Archivo";
}
if (file_exists("bak_" . $archivo)) {
/** Clases necesarias */
require_once '../Classes/PHPExcel.php';
require_once '../Classes/PHPExcel/Reader/Excel2007.php';
// Cargando la hoja de cálculo
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load("bak_" . $archivo);
$objFecha = new PHPExcel_Shared_Date();
// Asignar hoja de excel activa
$objPHPExcel->setActiveSheetIndex(0);
//conectamos con la base de datos
$cn = mssql_connect("192.168.1.4", "APLICACIONES", "APLICACIONES") or die("ERROR EN LA CONEXION");
$db = mssql_select_db("[020BDCOMUN]", $cn) or die("ERROR AL CONECTAR A LA BD");
// Llenamos el arreglo con los datos del archivo xlsx
for ($i = 1; $i <= 300; $i++) {
$_DATOS_EXCEL[$i]['IDDATOS_RQ'] = $objPHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue();
$_DATOS_EXCEL[$i]['CODIGODATOS_RQ'] = $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue();
$_DATOS_EXCEL[$i]['CANTDATOS_RQ'] = $objPHPExcel->getActiveSheet()->getCell('D' . $i)->getCalculatedValue();
$_DATOS_EXCEL[$i]['USUARIO'] = $_SESSION['id_usuario'];
}
} else {
echo "Necesitas primero importar el archivo";
public function upload_report_listing($file_name)
{
// Include PHPExcel_IOFactory
// include 'PHPExcel/IOFactory.php';
// include 'PHPExcel/PHPExcel.php';
// $inputFileName = 'excel_files/garissa_sms_recepients_updated.xlsx';
// echo $category;exit;
$inputFileName = 'print_docs/excel/uploaded_files/' . $file_name;
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
// echo "<pre>";print_r($inputFileName);exit;
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow() + 1;
$highestColumn = $sheet->getHighestColumn();
// echo "<pre>";print_r($highestRow);echo "</pre>";exit;
$rowData = array();
for ($row = 4; $row < $highestRow; $row++) {
// Read a row of data into an array
$rowData_ = $sheet->rangeToArray('A' . $row . ':F' . $row);
// echo "<pre>";print_r($rowData_);echo "</pre>";
array_push($rowData, $rowData_[0]);
// Insert row data array into your database of choice here
}
// echo "<pre>";print_r($rowData);exit;//echo's array
foreach ($rowData as $r_data) {
// echo "<pre>";print_r($r_data);echo "</pre>";
/*
Result array key
0 = name
1 = phone
2 = email
3 = county
4 = subcounty
5 = mfl
*/
$status = 1;
$county_id = $district_id = $facility_code = 0;
$new_county_id = $new_district_id = $new_facility_code = $usertype = null;
$county_name = strtolower($r_data[3]);
//lower case
$county_name = str_replace(" ", "", $county_name);
$county_name = str_replace("-", " ", $county_name);
$county_name = ucwords($county_name);
//upper first character
$district = strtolower($r_data[4]);
$district = ucfirst($district);
$phone = preg_replace('/\\s+/', '', $r_data[1]);
// echo "<pre>";print_r($phone);
// echo "<pre>";print_r($county_name);
$facility_code = !empty($r_data[5]) ? $r_data[5] : NULL;
$name = !empty($r_data[0]) ? $r_data[0] : NULL;
$email = !empty($r_data[2]) ? $r_data[2] : NULL;
$date_uploaded = date('Y-m-d h:i:s');
$fault_index = NULL;
// echo $district;
$query = "SELECT * FROM facilities WHERE facility_code = '{$facility_code}'";
$result = $this->db->query($query)->result_array();
//FACILITY CODE SEARCH
// echo "<pre>";print_r($result);echo "</pre>";
$sql = null;
if (empty($result)) {
//if no facility code then district
$queryy = "SELECT * FROM districts WHERE district = '{$district}'";
$resultt = $this->db->query($queryy)->result_array();
if (empty($resultt)) {
//no district then county
$queryyy = "SELECT * FROM counties WHERE county = '{$county_name}'";
$resulttt = $this->db->query($queryyy)->result_array();
if (empty($resulttt)) {
echo "Empty county,subcounty and facility";
} else {
$county_id = $resulttt[0]['id'];
// echo "<pre>\t Only County ".$phone;
$new_county_id = $county_id;
$usertype = 10;
}
} else {
//if district matches
$district_id = $resultt[0]['id'];
$county_id = $this->get_county_id_for_district($district_id);
// echo "<pre>\t District ".$phone;
$new_county_id = $county_id;
$new_district_id = $district_id;
$usertype = 3;
}
//district name match
} else {
//if facility_code_match
// echo "<pre>";print_r($result);exit;
$district_id = $result[0]['district'];
$county_id = $this->get_county_id_for_district($district_id);
// echo "<pre>\t Facility: ".$phone;
$new_county_id = $county_id;
$new_district_id = $district_id;
$new_facility_code = $facility_code;
$usertype = 5;
// echo "<pre>"; print_r($county_id);exit;
}
/*
//code for appending 254 to phone numbers
if (isset($phone)) {
$phone = preg_replace('/\s+/', '', $phone);
$phone = ltrim($phone, '0');
// echo "<pre>".substr($phone, 0,3);
if (substr($phone, 0,3) != '254') {
$phone = '254'.$phone;
}
}else{
$phone = NULL;
}
*/
$number_length = isset($phone) ? strlen($phone) : 0;
// echo "Number Length: ".$number_length;
if ($number_length != 12) {
if (isset($fault_index)) {
$fault_index = 3;
//both error in phone and district
// $status = 2;
} else {
$fault_index = 2;
}
$fault_index = 2;
//overriding both district and phone error as district is not necessarily necessary
$status = 2;
}
/*commented out insertion below*/
$listing = array();
$listing_data = array('name' => $name, 'email' => $email, 'phone_number' => $phone, 'facility_code' => $new_facility_code, 'sub_county' => $new_district_id, 'county' => $new_county_id, 'usertype' => $usertype, 'date_uploaded' => $date_uploaded, 'status' => '0');
array_push($listing, $listing_data);
// echo "<pre>";print_r($inv);
$similarity_query = "SELECT * FROM email_listing_new WHERE phone_number = '{$phone}'";
$similarity = $this->db->query($similarity_query)->result_array();
//FACILITY CODE SEARCH
if (empty($similarity)) {
$insertion = $this->db->insert_batch('email_listing_new', $listing);
// echo "QUERY SUCCESSFUL. ".$insertion." ".mysql_insert_id()."</br>";
} else {
// echo "<pre> Dab on em";
}
}
// echo "<pre>";print_r($inv);
// unlink($inputFileName);
// echo "QUERY SUCCESSFUL. LAST ID INSERTED: ".mysql_insert_id(); exit;
// redirect(base_url().'admin/report_listing');
// exit;
}
<?php
//INCLUIMOS LA LIBRERIA DEL EXCEL
require_once("PHPExcel/Classes/PHPExcel/Reader/Excel2007.php");
require_once 'PHPExcel/Classes/PHPExcel.php';
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load("excel.xls");
$objPHPExcel->setActiveSheetIndex(0);
$i=1;
while($objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue() != ′′) {
$nombre = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();
$email = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
$telefono = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
/*$conexion = mysql_connect("$dbhost", "$dbuser", "$dbpass");
$sql = "INSERT INTO usuarios (nombre, email, telefono) VALUES (′$nombre′, ′$email’, ′$telefono’)";
$rst=mysql_query($sql,$conexion);
if(!$rst) {
die("Error MySQL de Inserción de Datos"); $i++; }
*/
$i++;
}
<?php
if (!defined('isIncluded')) {
header('location: index.php');
}
if (!isset($_SESSION['uploadedFile'])) {
header('location: index.php');
}
set_time_limit(0);
date_default_timezone_set('Europe/Amsterdam');
$_SESSION['AUTOLOAD'] = false;
require_once APP_LIB_PATH . 'PHPExcel' . DS . 'IOFactory.php';
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load(APP_EXCEL_PATH . $_SESSION['uploadedFile']);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$_SESSION['AUTOLOAD'] = true;
function select($name, $data)
{
echo '<select name="' . $name . '">';
echo '<option value="0">Selecteer een kolom uit het Excel-bestand</option>';
foreach ($data['1'] as $char => $field) {
echo '<option value="' . $char . '">' . $field . '</option>';
}
echo '</select>';
}
function productlijstExists($naam)
{
$productlijsten = productlijst::getAll();
foreach ($productlijsten as $productlijst) {
if ($productlijst->getProperty('Naam') == $naam) {
return true;
public function data_upload()
{
if ($_POST['btn_save']) {
$objReader = new PHPExcel_Reader_Excel2007();
if ($_FILES['file']['tmp_name']) {
$objPHPExcel = $objReader->load($_FILES['file']['tmp_name']);
} else {
$this->session->set_userdata('upload_counter', '1');
redirect("fcdrr_management/index");
}
$arr = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$highestColumm = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
$highestRow = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
//Top Details
$facility_name = $arr[5]['B'] . $arr[5]['C'] . $arr[5]['D'] . $arr[5]['E'];
$province = $arr[6]['B'] . $arr[6]['C'] . $arr[6]['D'] . $arr[6]['E'];
$facility_code = $arr[5]['R'] . $arr[5]['S'] . $arr[5]['T'];
$district = $arr[6]['R'] . $arr[6]['S'] . $arr[6]['T'];
$type_of_service_art = $arr[8]['C'];
$type_of_service_pmtct = $arr[8]['E'];
$type_of_service_pep = $arr[8]['H'];
if ($type_of_service_art && $type_of_service_pmtct && $type_of_service_pep) {
$services_offered = "ART,PMTCT,PEP";
} else {
if ($type_of_service_pmtct && $type_of_service_art) {
$services_offered = "ART,PMTCT";
} else {
if ($type_of_service_pep && $type_of_service_art) {
$services_offered = "ART,PEP";
} else {
if ($type_of_service_pmtct && $type_of_service_pep) {
$services_offered = "PMTCT,PEP";
} else {
if ($type_of_service_art) {
$services_offered = "ART";
}
if ($type_of_service_pmtct) {
$services_offered = "PMTCT";
}
if ($type_of_service_pep) {
$services_offered = "PEP";
}
}
}
}
}
@$services_offered;
$programme_sponsor_gok = $arr[4]['D'];
$programme_sponsor_pepfar = $arr[4]['G'];
$programme_sponsor_msf = $arr[4]['L'];
$programme_sponsor = "";
if ($programme_sponsor_gok) {
$programme_sponsor = "GOK";
}
if ($programme_sponsor_pepfar) {
$programme_sponsor = "PEPFAR";
}
if ($programme_sponsor_msf) {
$programme_sponsor = "MSF";
}
$updated_on = date("U");
//Reporting Period
@($beginning = trim($arr[10]['D'] . $arr[10]['E']));
@($ending = $arr[10]['R'] . $arr[10]['S'] . $arr[10]['T']);
$start = explode("-", $beginning);
$day = $start[0];
$month = $start[1];
$year = $start[2];
$beginning = "20" . $year . "-" . $month . "-" . $day;
$beginning = date('Y-m-d', strtotime($beginning));
$ending = str_replace('/', '-', $ending);
$old_ending = strtotime($ending);
$ending = date('Y-m-d', $old_ending);
$central_facility = $this->session->userdata('facility');
$parent = Facilities::getParent($central_facility);
$central_site = $parent->parent;
//Comments
for ($i = 105; $i <= 109; $i++) {
for ($j = 1; $j <= $highestColumm; $j++) {
}
@($comments .= $arr[$i]['A'] . $arr[$i]['B'] . $arr[$i]['C'] . $arr[$i]['D'] . $arr[$i]['E'] . $arr[$i]['G'] . $arr[$i]['H'] . $arr[$i]['L']);
}
$unique_id = 0;
$this->load->database();
$facility_order_query = $this->db->query("SELECT MAX(id) AS id FROM facility_order");
$facility_order_results = $facility_order_query->result_array();
$facility_id = $facility_order_results[0]['id'];
$order_number = $facility_id + 1;
$unique_id = md5($order_number . $facility_code);
$query = $this->db->query("INSERT INTO facility_order (`id`, `status`, `created`, `updated`, `code`, `period_begin`, `period_end`, `comments`, `reports_expected`, `reports_actual`, `services`, `sponsors`, `delivery_note`, `order_id`, `facility_id`,`central_facility`,`unique_id`) VALUES ('{$order_number}', '0', CURDATE(), '{$updated_on}', '2', '{$beginning}', '{$ending}', '{$comments}', NULL, NULL, '{$services_offered}', '{$programme_sponsor}', NULL, NULL, '{$facility_code}','{$central_site}','{$unique_id}');");
$facility_id = $unique_id;
$user_id = $this->session->userdata('full_name');
$query = $this->db->query("SELECT MAX(id) AS id FROM order_comment");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id = $last_id + 1;
$last_id = md5($last_id . $facility_code);
//Adding comments
$order_comment = new Order_Comment();
$order_comment->Order_Number = $facility_id;
$order_comment->Timestamp = date('U');
$order_comment->User = $user_id;
$order_comment->Comment = $comments;
$order_comment->Unique_Id = $last_id;
$order_comment->save();
//Adult ARV Preparations
for ($i = 18; $i <= 42; $i++) {
for ($j = 1; $j <= $highestColumm; $j++) {
}
$quantity_required_for_supply = $arr[$i]['L'];
$drug_name = $arr[$i]['A'];
if ($quantity_required_for_supply != 0) {
$drug_id = $drug_name;
$basic_unit = $arr[$i]['B'];
$beginning_balance = $arr[$i]['C'];
$quantity_received_in_period = $arr[$i]['D'];
$quantity_dispensed_in_period = $arr[$i]['E'];
$adjustments_to_other_facilities = $arr[$i]['G'];
$end_of_month_physical_count = $arr[$i]['H'];
$quantity_required_for_supply = $arr[$i]['L'];
$query = $this->db->query("SELECT MAX(id) AS id FROM cdrr_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$cdrr_query = $this->db->query("INSERT INTO cdrr_item (`id`, `balance`, `received`, `dispensed_units`, `dispensed_packs`, `losses`, `adjustments`, `count`, `resupply`, `aggr_consumed`, `aggr_on_hand`, `publish`, `cdrr_id`, `drug_id`,`unique_id`) VALUES (NULL, '{$beginning_balance}', '{$quantity_received_in_period}', '{$quantity_dispensed_in_period}', NULL, NULL, '{$adjustments_to_other_facilities}', '{$end_of_month_physical_count}', '{$quantity_required_for_supply}', NULL, NULL, '0', '{$facility_id}', '{$drug_id}','{$last_id}');");
}
}
//Paediatric Preparations
for ($i = 44; $i <= 76; $i++) {
for ($j = 1; $j <= $highestColumm; $j++) {
}
$quantity_required_for_supply = $arr[$i]['L'];
$drug_name = $arr[$i]['A'];
if ($quantity_required_for_supply != 0) {
$drug_id = $drug_name;
$basic_unit = $arr[$i]['B'];
$beginning_balance = $arr[$i]['C'];
$quantity_received_in_period = $arr[$i]['D'];
$quantity_dispensed_in_period = $arr[$i]['E'];
$adjustments_to_other_facilities = $arr[$i]['G'];
$end_of_month_physical_count = $arr[$i]['H'];
$quantity_required_for_supply = $arr[$i]['L'];
$query = $this->db->query("SELECT MAX(id) AS id FROM cdrr_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$cdrr_query = $this->db->query("INSERT INTO cdrr_item (`id`, `balance`, `received`, `dispensed_units`, `dispensed_packs`, `losses`, `adjustments`, `count`, `resupply`, `aggr_consumed`, `aggr_on_hand`, `publish`, `cdrr_id`, `drug_id`,`unique_id`) VALUES (NULL, '{$beginning_balance}', '{$quantity_received_in_period}', '{$quantity_dispensed_in_period}', NULL, NULL, '{$adjustments_to_other_facilities}', '{$end_of_month_physical_count}', '{$quantity_required_for_supply}', NULL, NULL, '0', '{$facility_id}', '{$drug_id}','{$last_id}');");
}
}
//Drugs for IOs
for ($i = 78; $i <= 99; $i++) {
for ($j = 1; $j <= $highestColumm; $j++) {
}
$quantity_required_for_supply = $arr[$i]['L'];
$drug_name = $arr[$i]['A'];
if ($quantity_required_for_supply != 0) {
$drug_id = $drug_name;
$basic_unit = $arr[$i]['B'];
$beginning_balance = $arr[$i]['C'];
$quantity_received_in_period = $arr[$i]['D'];
$quantity_dispensed_in_period = $arr[$i]['E'];
$adjustments_to_other_facilities = $arr[$i]['G'];
$end_of_month_physical_count = $arr[$i]['H'];
$quantity_required_for_supply = $arr[$i]['L'];
$query = $this->db->query("SELECT MAX(id) AS id FROM cdrr_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$cdrr_query = $this->db->query("INSERT INTO cdrr_item (`id`, `balance`, `received`, `dispensed_units`, `dispensed_packs`, `losses`, `adjustments`, `count`, `resupply`, `aggr_consumed`, `aggr_on_hand`, `publish`, `cdrr_id`, `drug_id`,`unique_id`) VALUES (NULL, '{$beginning_balance}', '{$quantity_received_in_period}', '{$quantity_dispensed_in_period}', NULL, NULL, '{$adjustments_to_other_facilities}', '{$end_of_month_physical_count}', '{$quantity_required_for_supply}', NULL, NULL, '0', '{$facility_id}', '{$drug_id}','{$last_id}');");
}
}
//PMTCT Regimen 1.Pregnant Women
for ($i = 19; $i <= 21; $i++) {
for ($j = 19; $j <= $highestColumm; $j++) {
}
$regimen_code = $arr[$i]['S'];
$regimen_desc = $arr[$i]['T'];
$no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
if ($no_of_clients_dispensed_in_period) {
$regimen_id = $regimen_code . " | " . $regimen_desc;
$query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
}
}
//PMTCT Regimen 2.Infants
for ($i = 23; $i <= 27; $i++) {
for ($j = 19; $j <= $highestColumm; $j++) {
}
$regimen_code = $arr[$i]['S'];
$regimen_desc = $arr[$i]['T'];
$no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
if ($no_of_clients_dispensed_in_period) {
$this->load->database();
$regimen_id = $regimen_code . " | " . $regimen_desc;
$query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
}
}
//Adult ART First Line Regimens
for ($i = 33; $i <= 43; $i++) {
for ($j = 19; $j <= $highestColumm; $j++) {
}
$regimen_code = $arr[$i]['S'];
$regimen_desc = $arr[$i]['T'];
$no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
if ($no_of_clients_dispensed_in_period) {
$regimen_id = $regimen_code . " | " . $regimen_desc;
$query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
}
}
//Adult ART Second Line Regimens
for ($i = 45; $i <= 58; $i++) {
for ($j = 19; $j <= $highestColumm; $j++) {
}
$regimen_code = $arr[$i]['S'];
$regimen_desc = $arr[$i]['T'];
$no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
if ($no_of_clients_dispensed_in_period) {
$regimen_id = $regimen_code . " | " . $regimen_desc;
$query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
}
}
//Other Adult ART regimens
for ($i = 60; $i <= 62; $i++) {
for ($j = 19; $j <= $highestColumm; $j++) {
}
$regimen_code = $arr[$i]['S'];
$regimen_desc = $arr[$i]['T'];
$no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
if ($no_of_clients_dispensed_in_period) {
$regimen_id = $regimen_code . " | " . $regimen_desc;
$query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
}
}
//Paediatric ART First Line Regimens
for ($i = 64; $i <= 74; $i++) {
for ($j = 19; $j <= $highestColumm; $j++) {
}
$regimen_code = $arr[$i]['S'];
$regimen_desc = $arr[$i]['T'];
$no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
if ($no_of_clients_dispensed_in_period) {
$regimen_id = $regimen_code . " | " . $regimen_desc;
$query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
}
}
//Paediatric ART Second Line Regimens
for ($i = 76; $i <= 84; $i++) {
for ($j = 19; $j <= $highestColumm; $j++) {
}
$regimen_code = $arr[$i]['S'];
$regimen_desc = $arr[$i]['T'];
$no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
if ($no_of_clients_dispensed_in_period) {
$regimen_id = $regimen_code . " | " . $regimen_desc;
$query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
}
}
//Other Paediatric ART regimens
for ($i = 86; $i <= 87; $i++) {
for ($j = 19; $j <= $highestColumm; $j++) {
}
$regimen_code = $arr[$i]['S'];
$regimen_desc = $arr[$i]['T'];
$no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
if ($no_of_clients_dispensed_in_period) {
$regimen_id = $regimen_code . " | " . $regimen_desc;
$query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
}
}
//POST Exposure Prophylaxis(PEP)
for ($i = 91; $i <= 99; $i++) {
for ($j = 19; $j <= $highestColumm; $j++) {
}
$regimen_code = $arr[$i]['S'];
$regimen_desc = $arr[$i]['T'];
$no_of_clients_dispensed_in_period = $arr[$i]['V'] . $arr[$i]['W'];
if ($no_of_clients_dispensed_in_period) {
$regimen_id = $regimen_code . " | " . $regimen_desc;
$query = $this->db->query("SELECT MAX(id) AS id FROM maps_item");
$results = $query->result_array();
$last_id = $results[0]['id'];
$last_id++;
$last_id = md5($last_id . $facility_code);
$next_query = $this->db->query("INSERT INTO maps_item (`id`, `total`, `regimen_id`, `maps_id`,`unique_id`) VALUES (NULL, '{$no_of_clients_dispensed_in_period}', '{$regimen_id}', '{$facility_id}','{$last_id}');");
}
}
//ARV Data collection and Reporting Tools
//1.Name of Data-DAR
//a.ARVS Collection Tool
$fifty_arv_page_requested = $arr[116]['D'];
$three_hundred_arv_page_requested = $arr[116]['E'];
if ($fifty_arv_page_requested) {
$dar_arv_quantity_requested = $fifty_arv_page_requested;
}
if ($three_hundred_arv_page_requested) {
$dar_arv_quantity_requested = $three_hundred_arv_page_requested;
}
//a.OIs Collection Tool
$fifty_oi_page_requested = $arr[116]['G'];
$three_hundred_oi_page_requested = $arr[116]['H'];
if ($fifty_oi_page_requested) {
$dar_oi_quantity_requested = $fifty_oi_page_requested;
}
if ($three_hundred_oi_page_requested) {
$dar_oi_quantity_requested = $three_hundred_oi_page_requested;
}
//2.Name of Data-FCDRR
$fcdrr_quantity_requested = $arr[116]['L'];
//Prepared By details
$report_prepared_by = $arr[119]['B'] . $arr[119]['C'] . $arr[119]['D'];
$prepared_by_contact_telephone = $arr[121]['B'] . $arr[121]['C'] . $arr[121]['D'];
$signature_prepared_by = $arr[119]['G'] . $arr[119]['H'] . $arr[119]['L'];
$date_prepared_by_signature = $arr[121]['G'] . $arr[121]['H'];
//Approved By details
$report_approved_by = $arr[123]['B'] . $arr[123]['C'] . $arr[123]['D'];
$approved_by_contact_telephone = $arr[126]['B'] . $arr[126]['C'] . $arr[126]['D'];
$signature_approved_by = $arr[123]['G'] . $arr[123]['H'] . $arr[123]['L'];
$date_approved_by_signature = $arr[126]['G'] . $arr[126]['H'];
//$this -> session -> set_userdata('upload_counter','2');
redirect("order_management/edit_order/{$order_number}");
}
}
<?php
/** Incluir la ruta **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
/** Clases necesarias */
require_once 'PHPExcel.php';
require_once 'PHPExcel/Reader/Excel2007.php';
$VIEWDATA = array('Numero' => 0, 'Nombre' => 0, 'Nit' => 0);
// Cargando la hoja de cálculo
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load("Factura.xlsx");
// Asignar hoja de calculo activa
$objPHPExcel->setActiveSheetIndex(0);
$VIEWDATA['Numero'] = $objPHPExcel->getActiveSheet()->getCell('A2')->getCalculatedValue();
$VIEWDATA['Nombre'] = $objPHPExcel->getActiveSheet()->getCell('B2')->getCalculatedValue();
$VIEWDATA['Nit'] = $objPHPExcel->getActiveSheet()->getCell('C2')->getCalculatedValue();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<table width="200" border="1">
<tr>
<td> NOmbree: <?php
echo $VIEWDATA['Nombre'];
?>
</td>
<td><?php
public function import_data()
{
$this->load->database();
header("Content-type:text/html;charset=utf-8");
$this->load->model('Upfile');
$url = $this->Upfile->member_upload($this->userinfo['id'], 'user_data');
$url = substr($url, 1);
$this->load->library('Phpexcel');
$PHPReader = new PHPExcel_Reader_Excel2007();
$PHPExcel = $PHPReader->load($url);
$currentSheet = $PHPExcel->getSheet(0);
/*取得一共有多少列*/
$allColumn = $currentSheet->getHighestColumn();
/*取得一共有多少行*/
$allRow = $currentSheet->getHighestRow();
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
$insert['mark'] = $currentSheet->getCell('A' . $currentRow)->getValue();
$insert['name'] = $currentSheet->getCell('B' . $currentRow)->getValue();
$insert['mobile'] = $currentSheet->getCell('C' . $currentRow)->getValue();
$insert['address'] = $currentSheet->getCell('D' . $currentRow)->getValue();
$insert['email'] = $currentSheet->getCell('E' . $currentRow)->getValue();
$insert['point'] = $currentSheet->getCell('F' . $currentRow)->getValue();
$insert['updatetime'] = time();
$insert['clientid'] = $this->userinfo['id'];
$num += $this->db->insert('temp_user', $insert);
}
echo "<script>window.location.href='/member/vipmember/viplist';</script>";
}
$archivo = $_FILES['excel']['name'];
$tipo = $_FILES['excel']['type'];
$destino = "bak_" . $archivo;
if (copy($_FILES['excel']['tmp_name'], $destino)) {
echo "File loaded successfully";
echo "<br>";
} else {
echo "Error to load file";
}
if (file_exists("bak_" . $archivo)) {
/** Clases necesarias */
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/Reader/Excel2007.php';
// Cargando la hoja de cálculo
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load("bak_" . $archivo);
$objPlant = $objReader->load("bak_" . $archivo);
$objFree = $objReader->load("bak_" . $archivo);
$objFecha = new PHPExcel_Shared_Date();
// Asignar hoja de excel activa
$objPHPExcel->setActiveSheetIndex(0);
// Llenamos el arreglo con los datos del archivo xlsx
$Id_laboratorio = "";
$Id_user = "";
$Id_storage = "";
for ($i = 3; $i <= $totalReHost; $i++) {
$_DATOS_EXCEL[$i]['Name'] = $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue();
$_DATOS_EXCEL[$i]['Date_of_collection'] = $objPHPExcel->getActiveSheet()->getCell('C' . $i)->getCalculatedValue();
$_DATOS_EXCEL[$i]['Collected_by'] = $objPHPExcel->getActiveSheet()->getCell('D' . $i)->getCalculatedValue();
$_DATOS_EXCEL[$i]['Site_alias'] = $objPHPExcel->getActiveSheet()->getCell('E' . $i)->getCalculatedValue();
$_DATOS_EXCEL[$i]['id_storage'] = $objPHPExcel->getActiveSheet()->getCell('F' . $i)->getCalculatedValue();
function procesarFormulario()
{
$conexion = "inventarios";
$esteRecursoDB = $this->miConfigurador->fabricaConexiones->getRecursoDB($conexion);
$cadenaSql = $this->miSql->getCadenaSql('consultar_entrada_acta', $_REQUEST['entrada']);
$acta = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$acta = $acta[0][0];
$cadenaSql = $this->miSql->getCadenaSql('consultar_elementos_acta', $acta);
$elementos_acta = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$numero_elementos_acta = count($elementos_acta);
$cadenaSql = $this->miSql->getCadenaSql('consultar_elementos_entrada', $_REQUEST['entrada']);
$elementos_entrada = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$numero_elementos_entrada = count($elementos_entrada);
// if($numero_elementos_acta==$numero_elementos_entrada){
// redireccion::redireccionar ( 'noCargarElemento' );
// }
$fechaActual = date('Y-m-d');
$_REQUEST['bodega'] = 0;
$esteBloque = $this->miConfigurador->getVariableConfiguracion("esteBloque");
$rutaBloque = $this->miConfigurador->getVariableConfiguracion("raizDocumento") . "/blocks/inventarios/gestionElementos/";
$rutaBloque .= $esteBloque['nombre'];
$host = $this->miConfigurador->getVariableConfiguracion("host") . $this->miConfigurador->getVariableConfiguracion("site") . "/blocks/inventarios/gestionEntradas/" . $esteBloque['nombre'];
$_REQUEST['total_iva_con'] = round($_REQUEST['total_iva_con']);
$cadenaSql = $this->miSql->getCadenaSql('idElementoMax');
$elemento_id_max = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$elemento_id_max = $elemento_id_max[0][0] + 1;
switch ($_REQUEST['tipo_registro']) {
case '1':
if ($_REQUEST['id_tipo_bien'] == 1) {
$cadenaSql = $this->miSql->getCadenaSql('consultar_placa', '1');
$placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$arreglo = array($fechaActual, $_REQUEST['nivel'], $_REQUEST['id_tipo_bien'], $_REQUEST['descripcion'], $_REQUEST['cantidad'], $_REQUEST['unidad'], $_REQUEST['valor'], $_REQUEST['iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], $_REQUEST['subtotal_sin_iva'], $_REQUEST['total_iva'], $_REQUEST['total_iva_con'], $_REQUEST['marca'] != '' ? $_REQUEST['marca'] : 'null', $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : 'null', $_REQUEST['entrada'], $elemento_id_max);
$cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_1', $arreglo);
$elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
} else {
if ($_REQUEST['id_tipo_bien'] == 2) {
$arreglo = array($fechaActual, $_REQUEST['nivel'], $_REQUEST['id_tipo_bien'], $_REQUEST['descripcion'], $_REQUEST['cantidad'] = 1, $_REQUEST['unidad'], $_REQUEST['valor'], $_REQUEST['iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], $_REQUEST['subtotal_sin_iva'], $_REQUEST['total_iva'], $_REQUEST['total_iva_con'], $_REQUEST['marca'] != '' ? $_REQUEST['marca'] : 'null', $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : 'null', $_REQUEST['entrada'], $elemento_id_max);
$cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_1', $arreglo);
$elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
} else {
if ($_REQUEST['id_tipo_bien'] == 3) {
if ($_REQUEST['tipo_poliza'] == 0) {
$arreglo = array($fechaActual, $_REQUEST['nivel'], $_REQUEST['id_tipo_bien'], $_REQUEST['descripcion'], $_REQUEST['cantidad'] = 1, $_REQUEST['unidad'], $_REQUEST['valor'], $_REQUEST['iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], $_REQUEST['subtotal_sin_iva'], $_REQUEST['total_iva'], $_REQUEST['total_iva_con'], $_REQUEST['tipo_poliza'], '0001-01-01', '0001-01-01', $_REQUEST['marca'] != '' ? $_REQUEST['marca'] : 'null', $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : 'null', $_REQUEST['entrada'], $elemento_id_max);
} else {
if ($_REQUEST['tipo_poliza'] == 1) {
$arreglo = array($fechaActual, $_REQUEST['nivel'], $_REQUEST['id_tipo_bien'], $_REQUEST['descripcion'], $_REQUEST['cantidad'] = 1, $_REQUEST['unidad'], $_REQUEST['valor'], $_REQUEST['iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], $_REQUEST['subtotal_sin_iva'], $_REQUEST['total_iva'], $_REQUEST['total_iva_con'], $_REQUEST['tipo_poliza'], $_REQUEST['fecha_inicio'], $_REQUEST['fecha_final'], $_REQUEST['marca'] != '' ? $_REQUEST['marca'] : 'null', $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : 'null', $_REQUEST['entrada'], $elemento_id_max);
}
}
$cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_2', $arreglo);
$elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
}
}
}
$placa = date('Ymd') . "00000";
$cadenaSql = $this->miSql->getCadenaSql('buscar_repetida_placa', $placa);
$num_placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$cadenaSql = $this->miSql->getCadenaSql('idElementoMaxIndividual');
$elemento_id_max_indiv = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$elemento_id_max_indiv = $elemento_id_max_indiv[0][0] + 1;
$sumaplaca = 0;
if ($num_placa[0][0] == 0) {
for ($i = 0; $i < $_REQUEST['cantidad']; $i++) {
$arregloElementosInv = array($fechaActual, $_REQUEST['id_tipo_bien'] == 1 ? NULL : $placa + $sumaplaca, $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : null, $elemento[0][0], $elemento_id_max_indiv);
$sumaplaca = $_REQUEST['id_tipo_bien'] == 1 ? $sumaplaca : $sumaplaca++;
$cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_individual', $arregloElementosInv);
$elemento_id[$i] = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$elemento_id_max_indiv = $elemento_id_max_indiv + 1;
}
} else {
if ($num_placa[0][0] != 0) {
$cadenaSql = $this->miSql->getCadenaSql('buscar_placa_maxima', $placa);
$num_placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$placa = $num_placa[0][0];
$sumaplaca = 1;
for ($i = 1; $i <= $_REQUEST['cantidad']; $i++) {
$arregloElementosInv = array($fechaActual, $_REQUEST['id_tipo_bien'] == 1 ? NULL : $placa + $sumaplaca, $_REQUEST['serie'] != '' ? $_REQUEST['serie'] : null, $elemento[0][0], $elemento_id_max_indiv);
$sumaplaca = $_REQUEST['id_tipo_bien'] == 1 ? $sumaplaca : $sumaplaca++;
$cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_individual', $arregloElementosInv);
$elemento_id[$i] = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$elemento_id_max_indiv = $elemento_id_max_indiv + 1;
}
}
}
foreach ($_FILES as $key => $values) {
$archivo = $_FILES[$key];
}
if ($archivo['type'] == 'image/jpeg') {
$data = base64_encode(file_get_contents($archivo['tmp_name']));
// Prioridad 1 ('Principal') 0 ('No Principal')
$arreglo = array("elemento" => $elemento_id_max, "prioridad" => "1", "imagen" => $data);
$cadenaSql = $this->miSql->getCadenaSql('ElementoImagen', $arreglo);
$elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
}
$datos = array($elemento[0][0], $fechaActual, $_REQUEST['entrada']);
if ($elemento) {
redireccion::redireccionar('inserto', $datos, $_REQUEST['datosGenerales']);
exit;
} else {
redireccion::redireccionar('noInserto', $datos);
exit;
}
break;
case '2':
$ingreso = 0;
$ruta_eliminar_xlsx = $rutaBloque . "/archivo/*.xlsx";
$ruta_eliminar_xls = $rutaBloque . "/archivo/*.xls";
foreach (glob($ruta_eliminar_xlsx) as $filename) {
unlink($filename);
}
foreach (glob($ruta_eliminar_xls) as $filename) {
unlink($filename);
}
$i = 0;
foreach ($_FILES as $key => $values) {
$archivo[$i] = $_FILES[$key];
$i++;
}
$archivo = $archivo[0];
$trozos = explode(".", $archivo['name']);
$extension = end($trozos);
if ($extension == 'xlsx' || $extension == 'xls') {
if ($archivo) {
// obtenemos los datos del archivo
$tamano = $archivo['size'];
$tipo = $archivo['type'];
$archivo1 = $archivo['name'];
$prefijo = "archivo";
if ($archivo1 != "") {
// guardamos el archivo a la carpeta files
$ruta_absoluta = $rutaBloque . "/archivo/" . $prefijo . "_" . $archivo1;
if (copy($archivo['tmp_name'], $ruta_absoluta)) {
$status = "Archivo subido: <b>" . $archivo1 . "</b>";
$destino1 = $host . "/archivo/" . $prefijo . "_" . $archivo1;
} else {
$status = "Error al subir el archivo";
echo $status;
}
} else {
$status = "Error al subir archivo";
echo $status;
}
}
$arreglo = array($destino1, $archivo1);
if (file_exists($ruta_absoluta)) {
// Cargando la hoja de cálculo
$objReader = new \PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load($ruta_absoluta);
$objFecha = new \PHPExcel_Shared_Date();
// Asignar hoja de excel activa
$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
for ($i = 2; $i <= $highestRow; $i++) {
$datos[$i]['Nivel'] = $objPHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue();
$datos[$i]['Tipo_Bien'] = $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue();
$datos[$i]['Descripcion'] = $objPHPExcel->getActiveSheet()->getCell('C' . $i)->getCalculatedValue();
$datos[$i]['Cantidad'] = $objPHPExcel->getActiveSheet()->getCell('D' . $i)->getCalculatedValue();
$datos[$i]['Unidad_Medida'] = $objPHPExcel->getActiveSheet()->getCell('E' . $i)->getCalculatedValue();
$datos[$i]['Valor_Precio'] = $objPHPExcel->getActiveSheet()->getCell('F' . $i)->getCalculatedValue();
$datos[$i]['Iva'] = $objPHPExcel->getActiveSheet()->getCell('G' . $i)->getCalculatedValue();
$datos[$i]['Tipo_poliza'] = $objPHPExcel->getActiveSheet()->getCell('H' . $i)->getCalculatedValue();
$datos[$i]['Fecha_Inicio_Poliza'] = $objPHPExcel->getActiveSheet()->getCell('I' . $i)->getCalculatedValue();
$datos[$i]['Fecha_Final_Poliza'] = $objPHPExcel->getActiveSheet()->getCell('J' . $i)->getCalculatedValue();
$datos[$i]['Marca'] = $objPHPExcel->getActiveSheet()->getCell('K' . $i)->getCalculatedValue();
$datos[$i]['Serie'] = $objPHPExcel->getActiveSheet()->getCell('L' . $i)->getCalculatedValue();
}
for ($i = 2; $i <= $highestRow; $i++) {
switch ($datos[$i]['Iva']) {
case "1":
$IVA = 0;
break;
case "2":
$IVA = 0;
break;
case "3":
$IVA = 0.05;
break;
case "4":
$IVA = 0.04;
break;
case "5":
$IVA = 0.1;
break;
case "6":
$IVA = 0.16;
break;
}
$cadenaSql = $this->miSql->getCadenaSql('idElementoMax');
$elemento_id_max = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$elemento_id_max = $elemento_id_max[0][0] + 1;
// $arreglo = array (
// $fechaActual,
// $datos [$i] ['Nivel'],
// $datos [$i] ['Tipo_Bien'],
// trim ( $datos [$i] ['Descripcion'], "'" ),
// $datos [$i] ['Cantidad'],
// trim ( $datos [$i] ['Unidad_Medida'], "'" ),
// $datos [$i] ['Valor_Precio'],
// $datos [$i] ['Ajuste'],
// $datos [$i] ['Bodega'],
// $datos [$i] ['Cantidad'] * $datos [$i] ['Valor_Precio'],
// $datos [$i] ['Cantidad'] * $datos [$i] ['Valor_Precio'] * $datos [$i] ['Iva'],
// round ( $datos [$i] ['Cantidad'] * $datos [$i] ['Valor_Precio'] * $datos [$i] ['Iva'] ) + ($datos [$i] ['Cantidad'] * $datos [$i] ['Valor_Precio']),
// $datos [$i] ['Tipo_poliza'],
// trim ( $datos [$i] ['Fecha_Inicio_Poliza'], "'" ),
// trim ( $datos [$i] ['Fecha_Final_Poliza'], "'" ),
// trim ( $datos [$i] ['Marca'], "'" ),
// trim ( $datos [$i] ['Serie'], "'" ),
// $datos [$i] ['Entrada']
// );
if ($datos[$i]['Tipo_Bien'] == 1) {
$cadenaSql = $this->miSql->getCadenaSql('consultar_placa', '1');
$placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
// "1";0;"Exento";
// "2";0;"Tarifa de Cero";
// "3";0.05;"5%";
// "4";0.04;"4%";
// "5";0.1;"10%";
// "6";0.16;"16%";
$arreglo = array($fechaActual, $datos[$i]['Nivel'], $datos[$i]['Tipo_Bien'], trim($datos[$i]['Descripcion'], "'"), $datos[$i]['Cantidad'], trim($datos[$i]['Unidad_Medida'], "'"), $datos[$i]['Valor_Precio'], $datos[$i]['Iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], $datos[$i]['Cantidad'] * $datos[$i]['Valor_Precio'], $datos[$i]['Cantidad'] * $datos[$i]['Valor_Precio'] * $IVA, round($datos[$i]['Cantidad'] * $datos[$i]['Valor_Precio'] * $IVA) + $datos[$i]['Cantidad'] * $datos[$i]['Valor_Precio'], is_null($datos[$i]['Marca']) == true ? 'null' : trim($datos[$i]['Marca'], "'"), is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $_REQUEST['entrada'], $elemento_id_max);
$cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_1', $arreglo);
$elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
} else {
if ($datos[$i]['Tipo_Bien'] == 2) {
$datos[$i]['Cantidad'] = 1;
// $arreglo = array (
// $fechaActual,
// $_REQUEST ['nivel'],
// $_REQUEST ['id_tipo_bien'],
// $_REQUEST ['descripcion'],
// $_REQUEST ['cantidad'] = 1,
// $_REQUEST ['unidad'],
// $_REQUEST ['valor'],
// $_REQUEST ['iva'],
// $_REQUEST ['ajuste'] = 0,
// $_REQUEST ['bodega'],
// $_REQUEST ['subtotal_sin_iva'],
// $_REQUEST ['total_iva'],
// $_REQUEST ['total_iva_con'],
// ($_REQUEST ['marca'] != '') ? $_REQUEST ['marca'] : 'null',
// ($_REQUEST ['serie'] != '') ? $_REQUEST ['serie'] : 'null',
// $_REQUEST ['entrada'],
// $elemento_id_max
// );
$arreglo = array($fechaActual, $datos[$i]['Nivel'], $datos[$i]['Tipo_Bien'], trim($datos[$i]['Descripcion'], "'"), 1, trim($datos[$i]['Unidad_Medida'], "'"), $datos[$i]['Valor_Precio'], $datos[$i]['Iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], 1 * $datos[$i]['Valor_Precio'], 1 * $datos[$i]['Valor_Precio'] * $IVA, round(1 * $datos[$i]['Valor_Precio'] * $IVA) + 1 * $datos[$i]['Valor_Precio'], is_null($datos[$i]['Marca']) == true ? 'null' : trim($datos[$i]['Marca'], "'"), is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $_REQUEST['entrada'], $elemento_id_max);
$cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_1', $arreglo);
$elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
} else {
if ($datos[$i]['Tipo_Bien'] == 3) {
$datos[$i]['Cantidad'] = 1;
if ($datos[$i]['Tipo_poliza'] == 0) {
$arreglo = array($fechaActual, $datos[$i]['Nivel'], $datos[$i]['Tipo_Bien'], trim($datos[$i]['Descripcion'], "'"), 1, trim($datos[$i]['Unidad_Medida'], "'"), $datos[$i]['Valor_Precio'], $datos[$i]['Iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], 1 * $datos[$i]['Valor_Precio'], 1 * $datos[$i]['Valor_Precio'] * $IVA, round(1 * $datos[$i]['Valor_Precio'] * $IVA) + 1 * $datos[$i]['Valor_Precio'], $datos[$i]['Tipo_poliza'], 'NULL', 'NULL', is_null($datos[$i]['Marca']) == true ? 'null' : trim($datos[$i]['Marca'], "'"), is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $_REQUEST['entrada'], $elemento_id_max);
// $arreglo = array (
// $fechaActual,
// $_REQUEST ['nivel'],
// $_REQUEST ['id_tipo_bien'],
// $_REQUEST ['descripcion'],
// $_REQUEST ['cantidad'] = 1,
// $_REQUEST ['unidad'],
// $_REQUEST ['valor'],
// $_REQUEST ['iva'],
// $_REQUEST ['ajuste'] = 0,
// $_REQUEST ['bodega'],
// $_REQUEST ['subtotal_sin_iva'],
// $_REQUEST ['total_iva'],
// $_REQUEST ['total_iva_con'],
// $_REQUEST ['tipo_poliza'],
// 'NULL',
// 'NULL',
// ($_REQUEST ['marca'] != '') ? $_REQUEST ['marca'] : 'null',
// ($_REQUEST ['serie'] != '') ? $_REQUEST ['serie'] : 'null',
// $_REQUEST ['entrada'],
// $elemento_id_max
// );
} else {
if ($datos[$i]['Tipo_poliza'] == 1) {
$arreglo = array($fechaActual, $datos[$i]['Nivel'], $datos[$i]['Tipo_Bien'], trim($datos[$i]['Descripcion'], "'"), 1, trim($datos[$i]['Unidad_Medida'], "'"), $datos[$i]['Valor_Precio'], $datos[$i]['Iva'], $_REQUEST['ajuste'] = 0, $_REQUEST['bodega'], 1 * $datos[$i]['Valor_Precio'], 1 * $datos[$i]['Valor_Precio'] * $IVA, round(1 * $datos[$i]['Valor_Precio'] * $IVA) + 1 * $datos[$i]['Valor_Precio'], $datos[$i]['Tipo_poliza'], trim($datos[$i]['Fecha_Inicio_Poliza'], "'"), trim($datos[$i]['Fecha_Final_Poliza'], "'"), is_null($datos[$i]['Marca']) == true ? 'null' : trim($datos[$i]['Marca'], "'"), is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $_REQUEST['entrada'], $elemento_id_max);
// $arreglo = array (
// $fechaActual,
// $_REQUEST ['nivel'],
// $_REQUEST ['id_tipo_bien'],
// $_REQUEST ['descripcion'],
// $_REQUEST ['cantidad'] = 1,
// $_REQUEST ['unidad'],
// $_REQUEST ['valor'],
// $_REQUEST ['iva'],
// $_REQUEST ['ajuste'] = 0,
// $_REQUEST ['bodega'],
// $_REQUEST ['subtotal_sin_iva'],
// $_REQUEST ['total_iva'],
// $_REQUEST ['total_iva_con'],
// $_REQUEST ['tipo_poliza'],
// $_REQUEST ['fecha_inicio'],
// $_REQUEST ['fecha_final'],
// ($_REQUEST ['marca'] != '') ? $_REQUEST ['marca'] : 'null',
// ($_REQUEST ['serie'] != '') ? $_REQUEST ['serie'] : 'null',
// $_REQUEST ['entrada'],
// $elemento_id_max
// );
}
}
$cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_tipo_2', $arreglo);
$elemento = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
}
}
}
// $cadenaSql = $this->miSql->getCadenaSql ( 'ingresar_elemento_masivo', $arreglo );
// $elemento = $esteRecursoDB->ejecutarAcceso ( $cadenaSql, "busqueda" );
// $_REQUEST ['cantidad'] = $datos [$i] ['Cantidad'];
// $_REQUEST ['serie'] = trim ( $datos [$i] ['Serie'], "'" );
// $ingreso = 1;
$placa = date('Ymd') . "00000";
$cadenaSql = $this->miSql->getCadenaSql('buscar_repetida_placa', $placa);
$num_placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$cadenaSql = $this->miSql->getCadenaSql('idElementoMaxIndividual');
$elemento_id_max_indiv = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$elemento_id_max_indiv = $elemento_id_max_indiv[0][0] + 1;
$sumaplaca = 0;
switch ($datos[$i]['Tipo_Bien']) {
case '1':
$_REQUEST['cantidad'] = $datos[$i]['Cantidad'];
break;
case '2':
$_REQUEST['cantidad'] = 1;
break;
case '3':
$_REQUEST['cantidad'] = 1;
break;
}
if ($num_placa[0][0] == 0) {
for ($j = 0; $j < $_REQUEST['cantidad']; $j++) {
$arregloElementosInv = array($fechaActual, $datos[$i]['Tipo_Bien'] == 1 ? NULL : $placa + $sumaplaca, is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $elemento[0][0], $elemento_id_max_indiv);
$sumaplaca = $datos[$i]['Tipo_Bien'] == 1 ? $sumaplaca : $sumaplaca++;
$cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_individual', $arregloElementosInv);
$elemento_id[$j] = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$elemento_id_max_indiv = $elemento_id_max_indiv + 1;
}
} else {
if ($num_placa[0][0] != 0) {
$cadenaSql = $this->miSql->getCadenaSql('buscar_placa_maxima', $placa);
$num_placa = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$placa = $num_placa[0][0];
$sumaplaca = 1;
for ($j = 1; $j <= $_REQUEST['cantidad']; $j++) {
$arregloElementosInv = array($fechaActual, $datos[$i]['Tipo_Bien'] == 1 ? NULL : $placa + $sumaplaca, is_null($datos[$i]['Serie']) == true ? 'null' : trim($datos[$i]['Serie'], "'"), $elemento[0][0], $elemento_id_max_indiv);
$sumaplaca = $datos[$i]['Tipo_Bien'] == 1 ? $sumaplaca : $sumaplaca++;
$cadenaSql = $this->miSql->getCadenaSql('ingresar_elemento_individual', $arregloElementosInv);
$elemento_id[$j] = $esteRecursoDB->ejecutarAcceso($cadenaSql, "busqueda");
$elemento_id_max_indiv = $elemento_id_max_indiv + 1;
}
}
}
// $cadenaSql = $this->miSql->getCadenaSql ( 'buscar_repetida_placa', $placa );
// $num_placa = $esteRecursoDB->ejecutarAcceso ( $cadenaSql, "busqueda" );
// if ($num_placa [0] [0] == 0) {
// for($i = 0; $i < $_REQUEST ['cantidad']; $i ++) {
// $arregloElementosInv = array (
// $fechaActual,
// $placa + $i,
// $_REQUEST ['serie'],
// $elemento [0] [0]
// );
// $cadenaSql = $this->miSql->getCadenaSql ( 'ingresar_elemento_individual', $arregloElementosInv );
// $elemento_id [$i] = $esteRecursoDB->ejecutarAcceso ( $cadenaSql, "busqueda" );
// }
// } else if ($num_placa [0] [0] != 0) {
// $cadenaSql = $this->miSql->getCadenaSql ( 'buscar_placa_maxima', $placa );
// $num_placa = $esteRecursoDB->ejecutarAcceso ( $cadenaSql, "busqueda" );
// $placa = $num_placa [0] [0];
// for($i = 1; $i <= $_REQUEST ['cantidad']; $i ++) {
// $arregloElementosInv = array (
// $fechaActual,
// $placa + $i,
// $_REQUEST ['serie'],
// $elemento [0] [0]
// );
// $cadenaSql = $this->miSql->getCadenaSql ( 'ingresar_elemento_individual', $arregloElementosInv );
// $elemento_id [$i] = $esteRecursoDB->ejecutarAcceso ( $cadenaSql, "busqueda" );
// }
// }
}
if ($elemento) {
redireccion::redireccionar('inserto_M', $fechaActual);
exit;
} else {
redireccion::redireccionar('noInserto', $datos);
exit;
}
}
} else {
redireccion::redireccionar('noExtension');
}
break;
}
}
/**
* @param filename 上传的文件
* $param exts 文件类型
*/
public function goods_import($filename, $exts = 'xls')
{
//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
require_once APP_PATH . 'PHPExcel/PHPExcel.php';
require_once APP_PATH . 'PHPExcel/PHPExcel/Reader/Excel5.php';
// 用于其他低版本xls
require_once APP_PATH . 'PHPExcel/PHPExcel/Reader/Excel2007.php';
// 用于 excel-2007 格式
//创建PHPExcel对象，注意，不能少了\
$PHPExcel = new PHPExcel();
//如果excel文件后缀名为.xls，导入这个类
if ($exts == 'xls') {
$PHPReader = new PHPExcel_Reader_Excel5();
} elseif ($exts == 'xlsx') {
$PHPReader = new PHPExcel_Reader_Excel2007();
} else {
exit('错误的格式');
}
//载入文件
$PHPExcel = $PHPReader->load($filename);
//获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
$currentSheet = $PHPExcel->getSheet(0);
//获取总列数
$allColumn = $currentSheet->getHighestColumn();
//获取总行数
$allRow = $currentSheet->getHighestRow();
//循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
//从哪列开始，A表示第一列
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
//数据坐标
$address = $currentColumn . $currentRow;
//读取到的数据，保存到数组$arr中
$data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
}
}
foreach ($data as $info) {
$nickname = $info['A'];
$country = trim($info['C']);
$province = trim($info['D']);
$city = trim($info['E']);
$headimgurl = trim($info['F']);
$sex_text = trim($info['B']);
switch ($sex_text) {
case "男":
$sex = 1;
break;
case "女":
$sex = 2;
break;
case "保密":
$sex = 0;
break;
default:
$sex = 0;
break;
}
if (!empty($nickname) && !empty($headimgurl)) {
$data['nickname'] = $nickname;
$data['sex'] = $sex;
$data['country'] = $country;
$data['province'] = $province;
$data['city'] = $city;
$data['headimgurl'] = $headimgurl;
$result = M("dummy_user")->add($data);
}
}
}
private function read_excel($filename, $extension)
{
require "./././Class/PHPExcel.php";
$PHPExcel = new PHPExcel();
if ($extension == '.xls') {
require "./././Class/PHPExcel/Reader/Excel5.php";
$PHPReader = new PHPExcel_Reader_Excel5();
} elseif ($extension == '.xlsx') {
require "./././Class/PHPExcel/Reader/Excel2007.php";
$PHPReader = new PHPExcel_Reader_Excel2007();
}
//读取excel文件
$PHPExcel = $PHPReader->load($filename);
//获取Sheet0中的数据
$currentSheet = $PHPExcel->getSheet(0);
//获取行数、列数
$allColumn = $currentSheet->getHighestColumn();
$allRow = $currentSheet->getHighestRow();
//循环遍历excel表格，重点是getCell和getValue方法
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
$address = $currentColumn . $currentRow;
$data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
}
}
//循环所得数据插入数据库，至于从2开始嘛，执行var_dump($data)你就懂了
//echo "<pre>";
// var_dump($data);die;
$this->load->model('Admin_Model');
for ($i = 2; $i <= count($data); $i++) {
$school = $data[$i]['A'];
$find = $this->Admin_Model->check($school, 'school', 'school');
if (!empty($find)) {
$school_id = $find['id'];
} else {
$school_id = $this->Admin_Model->schooladd($school);
}
$name = $data[$i]['B'];
$this->Admin_Model->joineradd($name, $school_id);
}
redirect('/admin');
}
public function getWorkbook($isExcelFile = false)
{
$indpapers = @$this->params['indpapers'];
$vuzes = $this->getReportData();
$vuzes = $this->formtable($vuzes);
$xls_reader = new PHPExcel_Reader_Excel2007();
$workbook = $xls_reader->load(APPLICATION_PATH . 'modules/stat/templates/report5.xlsx');
$sheet = $workbook->getActiveSheet();
$sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd($indpapers ? 1 : 4, 8);
// В шаблоне пока не работает
$foreighnStr = $this->params['countrytype'] == 0 ? 'вітчизняних та іноземних ' : ($this->getAddValue($this->params['countrytype'], '_ADD_ORIGINID') == 'УКР' ? 'вітчизняних ' : 'іноземних ');
$eduBaseStr = $this->getAddValue($this->params['edubase'], '_ADD_ORIGINID') == 'Б' ? 'держзамовленням' : 'контрактом';
$eduForms = array('ДН' => 'що навчаються', 'ВЧ' => 'що навчаються вечірньо', 'ЗЧ' => 'що навчаються заочно', 'ДЦ' => 'що навчаються дистанційно', 'ДВ' => 'що отримують другу вищу освіту');
$eduFormOriginID = $this->getAddValue($this->params['eduform'], '_ADD_ORIGINID');
$eduFormStr = $eduForms[$eduFormOriginID] . ' ';
switch ($eduFormOriginID) {
case 'ЗЧ':
case 'ДВ':
$coefficient = 0.1;
break;
case 'ВЧ':
$coefficient = 0.25;
break;
default:
$coefficient = 1;
}
$endDate = new DateTime($this->getAddValueDate($this->params['period'], '_ADD_ENDDATE'));
if (@$this->params['reportplankind']) {
$selectedReportPlan = $this->getGuideNodeTitle($this->params['reportplankind']);
$planKindNum = $this->getAddValue($this->params['reportplankind'], '_ADD_ORIGINID') - 1;
$endDate->modify("+{$planKindNum} year");
} else {
$selectedReportPlan = 'Уточнений план';
}
$selectedYear = $endDate->format('Y');
$sheet->setCellValue('A1', sprintf($sheet->getCell('A1')->getValue(), $selectedReportPlan, $foreighnStr, $eduFormStr, $eduBaseStr, $selectedYear));
$current_row = 9;
// $sheet->getStyle("B17")->getNumberFormat()->setFormatCode('dd.mm.yyyy'); // Если просто задано в шаблоне, то не работает в HTML
$sheet->insertNewRowBefore($current_row + 1, count($vuzes) * 3);
// Название вуза + всего + в том числе
$allEduDirections = array();
$groupEduDirections = array();
$vuzezInGroupCount = 0;
$lastVuzGroup = null;
foreach ($vuzes as $vuznum => $vuz) {
if ($indpapers) {
if ($vuzezInGroupCount > 0) {
$sheet->setBreak("A" . ($current_row - 1), PHPExcel_Worksheet::BREAK_ROW);
}
} else {
if ($lastVuzGroup != $vuz['group']) {
if ($lastVuzGroup) {
$this->showTotal($sheet, $current_row, $groupEduDirections, false, $vuzezInGroupCount > 1);
}
$vuzezInGroupCount = 0;
$lastVuzGroup = $vuz['group'];
$groupEduDirections = array();
if (!@$this->params['establishment']) {
$sheet->insertNewRowBefore($current_row + 1, 1);
$sheet->setCellValueByColumnAndRow(0, $current_row, $lastVuzGroup == 1 ? 'ВНЗ 1-2 рівня (2301120)' : 'ВНЗ 3-4 рівня (2301070)');
$sheet->mergeCellsByColumnAndRow(0, $current_row, 19, $current_row);
$sheet->getStyle("A{$current_row}:T{$current_row}")->getFont()->setBold(true)->setSize(12);
$sheet->getStyle("A{$current_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$current_row++;
}
}
if ($vuzezInGroupCount > 0) {
// Разделительная линия между вузами внутри группы
$sheet->insertNewRowBefore($current_row + 1, 1);
$sheet->getRowDimension($current_row)->setRowHeight(0.75);
$current_row++;
}
}
$vuzezInGroupCount++;
$vuzrow = $current_row + 1;
// Запоминаем строку с суммами по вузу!!!
$sheet->setCellValueByColumnAndRow(0, $current_row, $vuznum + 1);
$sheet->setCellValueByColumnAndRow(1, $current_row, $vuz['title']);
$sheet->mergeCellsByColumnAndRow(1, $current_row, 19, $current_row);
$next_row = $current_row + 1;
$sheet->getStyle("A{$current_row}:T{$next_row}")->getFont()->setBold(true);
$sheet->getStyle("A{$current_row}:T{$next_row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($this->colorLevel1);
$sheet->getStyle("B{$current_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$current_row++;
$sheet->setCellValueByColumnAndRow(1, $current_row, $selectedYear);
$current_row++;
$sheet->setCellValueByColumnAndRow(1, $current_row, 'в тому числі');
$current_row++;
$dir_count = count($vuz['DATA']);
$sheet->insertNewRowBefore($current_row + 1, $dir_count);
// Направления подготовки
$dirrows = array();
foreach ($vuz['DATA'] as $dirnum => $dir) {
$dirrow = $current_row;
if (!@array_key_exists($dir['title'], $groupEduDirections)) {
$groupEduDirections[$dir['title']] = array();
}
$currentGroupEduDirection =& $groupEduDirections[$dir['title']];
if (!@array_key_exists($dir['title'], $allEduDirections)) {
$allEduDirections[$dir['title']] = array();
}
$currentAllEduDirection =& $allEduDirections[$dir['title']];
$dirrows[$dirnum] = $current_row;
$sheet->setCellValueByColumnAndRow(1, $current_row, 'напрям "' . $dir['title'] . '"');
$sheet->getStyle("A{$current_row}:T{$current_row}")->getFont()->setBold(true);
$sheet->getStyle("A{$current_row}:T{$current_row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($this->colorLevel2);
$current_row++;
$levels_count = count($dir['DATA']);
$sheet->insertNewRowBefore($current_row + 1, $levels_count);
// Уровни подготовки
$edulevelrows = array();
foreach ($dir['DATA'] as $edulevelnum => $edulevel) {
if (!@array_key_exists($edulevel['title'], $currentGroupEduDirection)) {
$currentGroupEduDirection[$edulevel['title']] = array();
}
$currentGroupEduLevel =& $currentGroupEduDirection[$edulevel['title']];
if (!@array_key_exists($edulevel['title'], $currentAllEduDirection)) {
$currentAllEduDirection[$edulevel['title']] = array();
}
$currentAllEduLevel =& $currentAllEduDirection[$edulevel['title']];
$edulevelrows[$edulevelnum] = $current_row;
$speciality_count = count($edulevel['DATA'], 0);
$sheet->insertNewRowBefore($current_row + 2, $speciality_count);
$formula_row1 = $current_row + 1;
$formula_row2 = $current_row + $speciality_count;
// Всего по уровню подготовки
for ($col = 2; $col <= 19; $col++) {
if ($col == 15) {
continue;
}
$colStr = PHPExcel_Cell::stringFromColumnIndex($col);
$sheet->setCellValueByColumnAndRow($col, $current_row, "=SUM({$colStr}{$formula_row1}:{$colStr}{$formula_row2})");
}
$sheet->setCellValueByColumnAndRow(1, $current_row, "рівень \"{$edulevel['title']}\"");
$sheet->getStyle("A{$current_row}:T{$current_row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($this->colorLevel3);
$current_row++;
foreach ($edulevel['DATA'] as $specialitynum => $speciality) {
if (!@array_key_exists($speciality['title'], $currentGroupEduLevel)) {
$currentGroupEduLevel[$speciality['title']] = array($current_row);
} else {
array_push($currentGroupEduLevel[$speciality['title']], $current_row);
}
if (!@array_key_exists($speciality['title'], $currentAllEduLevel)) {
$currentAllEduLevel[$speciality['title']] = array($current_row);
} else {
array_push($currentAllEduLevel[$speciality['title']], $current_row);
}
$sheet->setCellValueByColumnAndRow(1, $current_row, $speciality['title']);
$sheet->getStyle("B{$current_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
foreach (array('CS', null, 'CEXIT4', 'CEXIT3', 'CEXIT2', 'CEXIT1', null, 'CENTER4', 'CENTER3', 'CENTER2', 'CENTER1', null, 'MOVEOUT', null, 'MOVEIN') as $colnum => $column) {
if ($column) {
$cell = $sheet->setCellValueByColumnAndRow($colnum + 2, $current_row, array_key_exists($column, $speciality) ? $speciality[$column]['val'] : 0, true);
if (!$isExcelFile && @$speciality[$column]['kindid']) {
$ids = implode(',', $speciality[$column]['ids']);
// alert('datareportid = {$speciality[$column]['datareportid']}; kindid={$speciality[$column]['kindid']}; paramgroupids=$ids')
$cell->getHyperlink()->setUrl("javascript:showValidation({$speciality[$column]['datareportid']},{$speciality[$column]['kindid']},'{$ids}')");
}
}
}
$sheet->setCellValueByColumnAndRow(3, $current_row, "=SUM(E{$current_row}:H{$current_row})");
$sheet->setCellValueByColumnAndRow(8, $current_row, "=SUM(J{$current_row}:M{$current_row})");
$sheet->setCellValueByColumnAndRow(13, $current_row, "=C{$current_row}-D{$current_row}+I{$current_row}");
$sheet->setCellValueByColumnAndRow(17, $current_row, "=N{$current_row}-O{$current_row}+Q{$current_row}");
$sheet->setCellValueByColumnAndRow(18, $current_row, "=(C{$current_row}*8.5+R{$current_row}*3.5)/12");
$sheet->setCellValueByColumnAndRow(19, $current_row, "=S{$current_row}*{$coefficient}");
$current_row++;
}
}
for ($col = 2; $col <= 19; $col++) {
if ($col == 15) {
continue;
}
$colStr = PHPExcel_Cell::stringFromColumnIndex($col);
$sheet->setCellValueByColumnAndRow($col, $dirrow, "={$colStr}" . implode("+{$colStr}", $edulevelrows));
}
}
for ($col = 2; $col <= 19; $col++) {
if ($col == 15) {
continue;
}
$colStr = PHPExcel_Cell::stringFromColumnIndex($col);
$sheet->setCellValueByColumnAndRow($col, $vuzrow, "={$colStr}" . implode("+{$colStr}", $dirrows));
}
if ($indpapers) {
$sheet->insertNewRowBefore($current_row, 4);
$sheet->getStyle('A' . $current_row . ':T' . ($current_row + 3))->applyFromArray(array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_NONE))));
$sheet->mergeCellsByColumnAndRow(1, $current_row + 1, 8, $current_row + 1);
$sheet->setCellValueByColumnAndRow(1, $current_row + 1, @$vuz['STAT2_POST']);
$sheet->mergeCellsByColumnAndRow(1, $current_row + 2, 8, $current_row + 2);
$sheet->setCellValueByColumnAndRow(1, $current_row + 2, @$vuz['STAT2_DEGREE']);
$sheet->mergeCellsByColumnAndRow(9, $current_row + 1, 13, $current_row + 1);
$sheet->setCellValueByColumnAndRow(9, $current_row + 1, @$vuz['STAT2_FIO']);
$current_row += 4;
}
}
if (!$indpapers) {
$this->showTotal($sheet, $current_row, @$groupEduDirections, false, $vuzezInGroupCount > 1, count($vuzes) > 1);
//-------------- ИТОГ по всем ВУЗам --------------------------------------------
if (count($vuzes) > 1 && count($allEduDirections) > 0) {
$this->showTotal($sheet, $current_row, $allEduDirections, true);
}
//------------------------------------------------------------------------------
}
$sheet->removeRow($current_row, 1);
// Удаляем лишнюю строку, которая была в шаблоне
return $workbook;
}
