public function actionToCookbook()
{
sleep(2);
Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
$request = Yii::$app->request;
$fileB64 = $request->post('fileB64');
$file = base64_decode(substr($fileB64, strpos($fileB64, ";base64,") + 8));
$filePath = Yii::getAlias('@runtime') . '/cookbook' . date('his');
file_put_contents($filePath, $file);
$phpReader = new \PHPExcel_Reader_Excel2007();
if (!$phpReader->canRead($filePath)) {
$phpReader = new \PHPExcel_Reader_Excel5();
if (!$phpReader->canRead($filePath)) {
$phpReader = new \PHPExcel_Reader_CSV();
if (!$phpReader->canRead($filePath)) {
unlink($filePath);
return ['fileError' => true];
}
}
}
$phpExcel = $phpReader->load($filePath);
$sheets = $phpExcel->getAllSheets();
$cookbookTitles = [];
for ($si = 0; $si < sizeof($sheets); $si++) {
$sheet = $sheets[$si];
$rowTemp = [];
$cowTemp = [];
$ingredientFinished = false;
$rowCount = $sheet->getHighestRow();
$highestCol = $sheet->getHighestColumn();
$colCount = ord($highestCol) - 65;
$cookbook = [];
//There has a bug
//When the 'cuisineType' row does not exist, the $rowCount will be infinity
//The code blow can avoid this bug
$rowCount = $rowCount > 100 ? 100 : $rowCount;
for ($row = 1; $row <= $rowCount; $row++) {
for ($col = 0; $col <= $colCount; $col++) {
$val = $sheet->getCellByColumnAndRow($col, $row)->getValue();
$val = trim((string) $val);
if ($val === '') {
continue;
}
// Fill title and image
if (!isset($cookbook['title'])) {
$arr = explode('-', $val, 2);
if (empty($arr) || sizeof($arr) < 2) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: title is required');
return ['contentError' => true];
}
if (mb_strlen(trim(trim($arr[1])), 'utf-8') > 30) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: title should less than 30 words');
return ['titleLengthError' => true];
}
$cookbook['image'] = Yii::$app->qiniu->domain . '/' . trim($arr[0]) . '.jpg';
$cookbook['title'] = trim(trim($arr[1]));
unset($arr);
continue;
}
// Find category row
if (!isset($rowTemp['category'])) {
if (!preg_match('/^category$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: category is required');
return ['contentError' => true];
}
$rowTemp['category'] = $row;
continue;
}
// Fill category
if ($rowTemp['category'] === $row) {
//The first sheet's category row will leads to a bug
if ($si == 0) {
$firstCate = $val;
}
$arr = $this->_spiltByComma($val);
// $arr = preg_split('/[,，]/', $val);
$cookbook['category'] = [];
foreach ($arr as $v) {
$v = trim($v);
if ($v != '') {
$cookbook['category'][] = trim($v);
}
}
$row++;
$col = -1;
unset($arr);
continue;
}
// Find subCategory row
if (!isset($rowTemp['subCategory'])) {
if (!preg_match('/^sub[\\s\\n]*category$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: subCategory is required');
return ['contentError' => true];
}
$rowTemp['subCategory'] = $row;
continue;
}
// Fill subCategory
if ($rowTemp['subCategory'] === $row) {
// $arr = preg_split('/[,，]/', $val);
$arr = $this->_spiltByComma($val);
$cookbook['subCategory'] = [];
foreach ($arr as $v) {
$v = trim($v);
if ($v != '') {
$cookbook['subCategory'][] = trim($v);
}
}
$row++;
$col = -1;
unset($arr);
continue;
}
// Find cuisineType row
if (!isset($rowTemp['cuisineType'])) {
if (preg_match('/^cuisine[\\s\\n]*type$/i', $val)) {
$rowTemp['cuisineType'] = $row;
continue;
} else {
$rowTemp['cuisineType'] = '';
}
}
// Fill cuisineType
if ($rowTemp['cuisineType'] === $row) {
// $arr = preg_split('/[,，]/', $val);
$arr = $this->_spiltByComma($val);
$cookbook['cuisineType'] = [];
foreach ($arr as $v) {
$v = trim($v);
if ($v != '') {
$cookbook['cuisineType'][] = trim($v);
}
}
$row++;
$col = -1;
unset($arr);
continue;
}
// Find yield row
if (!isset($rowTemp['yield'])) {
if (!preg_match('/^yield$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: yield is required');
return ['contentError' => true];
}
$rowTemp['yield'] = $row;
continue;
}
// Fill yield
if ($rowTemp['yield'] === $row) {
if (!isset($cookbook['yield'])) {
$cookbook['yield'] = [];
$cookbook['yield']['Quantity'] = $val;
} else {
$cookbook['yield']['unit'] = $val;
}
continue;
}
// Find portionSize row
if (!isset($rowTemp['portionSize'])) {
if (!preg_match('/^portion[\\s\\n]*size$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: portionSize is required');
return ['contentError' => true];
}
$rowTemp['portionSize'] = $row;
continue;
}
// Fill portionSize
if ($rowTemp['portionSize'] === $row) {
$cookbook['portionSize'] = $val;
$row++;
$col = -1;
continue;
}
//Find ingredient quantity colume
if (!isset($colTemp['idtQuantity'])) {
if (!preg_match('/^quantity$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: ingredient quantity is required');
return ['contentError' => true];
}
$colTemp['idtQuantity'] = $col;
continue;
}
//Find ingredient unit colume
if (!isset($colTemp['idtUnit'])) {
if (!preg_match('/^unit$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: ingredient unit is required');
return ['contentError' => true];
}
$colTemp['idtUnit'] = $col;
continue;
}
//Find ingredient name colume
if (!isset($colTemp['idtName'])) {
if (!preg_match('/^ingredient[\\s\\n]*name$/i', $val)) {
unlink($filePath);
LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: ingredient name is required');
return ['contentError' => true];
}
$colTemp['idtName'] = $col;
continue;
}
//Fill ingredient
if (!isset($cookbook['ingredient'])) {
$cookbook['ingredient'] = [];
}
if (!$ingredientFinished) {
// Fill ingredient quantity
if ($col === $colTemp['idtQuantity']) {
$cookbook['ingredient'][$row]['quantity'] = $val;
}
// Fill ingredient unit
if ($col === $colTemp['idtUnit']) {
$cookbook['ingredient'][$row]['unit'] = $val;
}
// Fill ingredient name
if ($col === $colTemp['idtName']) {
$cookbook['ingredient'][$row]['name'] = $val;
}
$ingredientFinished = preg_match('/^preparation[\\s\\n]*method$/i', $val);
if ($ingredientFinished) {
array_pop($cookbook['ingredient']);
}
continue;
}
// Find preparation method description colume
if (!isset($colTemp['ptnDescription'])) {
if (preg_match('/^description$/i', $val)) {
$colTemp['ptnDescription'] = $col;
}
continue;
}
//Fill preparation method
if (!isset($cookbook['preparationMethod'])) {
$cookbook['preparationMethod'] = [];
}
// Fill preparation method description
if ($col === $colTemp['ptnDescription']) {
$cookbook['preparationMethod'][$row]['description'] = [];
$arr = preg_split('/靈感來源\\s*or\\s*貼心小提示/i', $val);
if (empty($arr) || sizeof($arr) !== 2) {
$cookbook['preparationMethod'][$row]['description']['step'] = $val;
$cookbook['preparationMethod'][$row]['description']['creativeExperience'] = '';
} else {
$cookbook['preparationMethod'][$row]['description']['step'] = trim($arr[0]);
$cookbook['preparationMethod'][$row]['description']['creativeExperience'] = trim($arr[1]);
}
unset($arr);
}
}
}
if (!isset($cookbook['ingredient']) || !isset($cookbook['preparationMethod'])) {
unlink($filePath);
return ['contentError' => true];
}
$cookbook['ingredient'] = array_values($cookbook['ingredient']);
$tmpInfo = $this->_findProductUrlAndSave($cookbook['ingredient']);
$cookbook['ingredient'] = $tmpInfo['ingredients'];
unset($tmpInfo);
$cookbook['preparationMethod'] = array_values($cookbook['preparationMethod']);
for ($i = 0; $i < sizeof($cookbook['ingredient']); $i++) {
$cookbook['ingredient'][$i]['id'] = $this->_getRandomId();
}
$cookbook['content'] = $cookbook['preparationMethod'][0]['description']['step'];
$cookbook['creativeExperience'] = $cookbook['preparationMethod'][0]['description']['creativeExperience'];
$cookbooks[] = $cookbook;
unset($rowTemp);
unset($colTemp);
unset($cookbook);
}
unlink($filePath);
if (empty($cookbooks)) {
return [];
}
$results = Cookbook::saveImportedCookbooks($cookbooks, $this->getAccountId());
$cookbookBatch = new CookbookBatch();
$accessToken = Token::getToken();
$user = User::findOne(['_id' => $accessToken->userId]);
$cookbookBatch->operator = $user->name;
$cookbookBatch->cookbooks = $results;
$cookbookBatch->hasImages = false;
$cookbookBatch->accountId = $this->getAccountId();
$cookbookBatch->createdTime = new \MongoDate();
$cookbookBatch->insert();
return sizeof($results);
}
public function actionReadNumberExcel()
{
sleep(2);
Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
$request = Yii::$app->request;
$fileB64 = $request->post('fileB64');
$content = $request->post('content');
$modelGroupId = $request->post('modelGroupId');
//delete all sms model by groupId
//根据groupid删除各自短信模板 但是导入之后前台刷新会丢失groupid 会造成数据库数据累积
//表中含有accountid字段可以根据该字段删除所有模板
if ($modelGroupId != -1) {
SmsModel::deleteAll(['groupId' => $modelGroupId]);
}
$file = base64_decode(substr($fileB64, strpos($fileB64, ";base64,") + 8));
$filePath = Yii::getAlias('@runtime') . '/numberExcel' . date('his') . '.csv';
file_put_contents($filePath, $file);
//csv 由xlsx转换 第一列为号码 第二列替换param1 第三列param2
$phpReader = new \PHPExcel_Reader_Excel2007();
if (!$phpReader->canRead($filePath)) {
$phpReader = new \PHPExcel_Reader_Excel5();
if (!$phpReader->canRead($filePath)) {
$phpReader = new \PHPExcel_Reader_CSV();
if (!$phpReader->canRead($filePath)) {
unlink($filePath);
return ['fileError' => true];
}
}
}
$phpExcel = $phpReader->load($filePath);
$sheets = $phpExcel->getAllSheets();
$recordCount = -1;
for ($si = 0; $si < sizeof($sheets); $si++) {
$sheet = $sheets[$si];
$ingredientFinished = false;
$rowCount = $sheet->getHighestRow();
$recordCount = $rowCount;
$highestCol = $sheet->getHighestColumn();
// LogUtil::error(date('Y-m-d h:i:s') . ' $rowCount: ' . $rowCount . ' $highestCol: ' . $highestCol);
$colCount = ord($highestCol) - 65;
$currentGroupId = $this->guid();
// LogUtil::error(date('Y-m-d h:i:s') . ' $getAccountId: ' . $this->getAccountId());
for ($row = 1; $row <= $rowCount; $row++) {
$realModel = $content;
$number = '';
for ($col = 0; $col <= $colCount; $col++) {
$val = $sheet->getCellByColumnAndRow($col, $row)->getValue();
$val = trim((string) $val);
if ($val === '') {
continue;
}
if ($col == 0) {
$number = $val;
}
if ($col > 0) {
$realModel = str_replace('%param' . $col . '%', $val, $realModel);
}
}
//如果存在空白行则会把模板存入
$SmsModel = new SmsModel();
$SmsModel->groupId = $currentGroupId;
$SmsModel->mobile = $number;
$SmsModel->content = $realModel;
$SmsModel->accountId = $this->getAccountId();
$SmsModel->save();
}
}
LogUtil::error(date('Y-m-d h:i:s') . ' $modelGroupId: ' . $currentGroupId);
return ['recordCount' => $recordCount, 'modelGroupId' => $currentGroupId];
}
private static function prepare_reader($path_to_spreadsheet)
{
$info = pathinfo($path_to_spreadsheet);
$extension = $info['extension'];
if ($extension == "xls") {
$excel_reader = \PHPExcel_IOFactory::createReader('Excel5');
} elseif ($extension == "xlsx") {
$excel_reader = \PHPExcel_IOFactory::createReader('Excel2007');
} elseif ($extension == "zip") {
$excel_reader = \PHPExcel_IOFactory::createReader('Excel2007');
} elseif ($extension == "csv") {
$excel_reader = new \PHPExcel_Reader_CSV();
}
if (!$excel_reader->canRead($path_to_spreadsheet)) {
throw new \Exception('Cannot read this file');
}
if ($extension != "csv") {
$excel_reader->setReadDataOnly(true);
}
$objPHPExcel = $excel_reader->load($path_to_spreadsheet);
return $objPHPExcel;
}
