public function actionExp()
{
$msg = $this->msgcode();
$smonth = Yii::app()->getRequest()->getParam("smonth", date("Ym"));
//时间
$pmonth = Yii::app()->getRequest()->getParam("pmonth", date("Ym"));
//时间
$mcol_type = Yii::app()->getRequest()->getParam("mcol_type", 0);
//类型
$col = AppBsColi::model()->findByPk($smonth);
if (empty($col)) {
$msg['msg'] = sprintf("开始时间%s 该月数据为空，请重新选择开始时间", $smonth);
echo $msg['msg'];
} else {
$criteria = new CDbCriteria();
$criteria->addCondition("month>={$smonth} AND month<={$pmonth}");
$allList = AppBsCol::model()->findAll($criteria);
$head = json_decode($col->desc, true);
$ms = isset($head[$mcol_type]) ? $head[$mcol_type] : array();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="COL.csv"');
header('Cache-Control: max-age=0');
$fp = fopen('php://output', 'a');
// 输出Excel列名信息
$head1 = array("月份", "AM", "DM", "餐厅编号", "餐厅名");
foreach ($ms as $k => $val) {
if ($k === "min") {
continue;
}
if ($k === "max") {
continue;
}
array_push($head1, $val);
}
foreach ($head1 as $i => $v) {
// CSV的Excel支持GBK编码，一定要转换，否则乱码
$head1[$i] = iconv('utf-8', 'gbk', $v);
}
// 将数据通过fputcsv写到文件句柄
fputcsv($fp, $head1);
// 计数器
$cnt = 0;
// 每隔$limit行，刷新一下输出buffer，不要太大，也不要太小
$limit = 100000;
foreach ($allList as $value) {
$cnt++;
if ($limit == $cnt) {
//刷新一下输出buffer，防止由于数据过多造成问题
ob_flush();
flush();
$cnt = 0;
}
$mkl = explode("|!|", $value->desc);
if (isset($ms['min'])) {
$arrr = array($value->month, $value->am, $value->dm, $value->ct_id, $value->ct_name);
if (empty($ms['max'])) {
$mkl = array_merge($arrr, array_slice($mkl, $ms['min']));
} else {
$mkl = array_merge($arrr, array_slice($mkl, $ms['min'], $ms['max']));
}
}
$row = $mkl;
foreach ($row as $i => $v) {
// CSV的Excel支持GBK编码，一定要转换，否则乱码
$row[$i] = iconv('utf-8', 'gbk', $v);
}
fputcsv($fp, $row);
}
}
}
/**
* 将csv文件保存到数据库中
*
* @param string $loadPath csv文件的路径
* @return int 成功条数
*/
public function storeCsv($loadPath, $month)
{
$connection = Yii::app()->db;
$sql = sprintf("INSERT INTO %s(`month`,`am`,`dm`,`ct_id`,`desc`,`ct_name`) VALUES", $this->tableName());
//构造SQL
$file_handle = fopen($loadPath, "r");
$tp = fgetcsv($file_handle);
$i = 0;
$colAll = array();
foreach ($tp as $k => $val) {
if ($k !== 0 && trim($val) != "") {
$colAll[$i] = $k;
$i++;
}
}
$tp1 = fgetcsv($file_handle);
$sendArr = $this->getArr($tp1, $colAll);
$tp2 = fgetcsv($file_handle);
$sendArr2 = $this->getArr($tp2, $colAll);
foreach ($sendArr2 as $k => $val) {
foreach ($val as $k1 => $v1) {
if ($sendArr[$k][$k1] == "") {
$sendArr[$k][$k1] = isset($sendArr[$k][$k1 - 1]) ? $sendArr[$k][$k1 - 1] : $sendArr[$k][$k1];
}
$sendArr2[$k][$k1] = $sendArr[$k][$k1] . $v1;
}
if ($k == 0) {
$sendArr2[$k]['min'] = 4;
$sendArr2[$k]['max'] = $colAll[0] - 4;
} elseif ($k == 6) {
$sendArr2[$k]['min'] = $colAll[5];
$sendArr2[$k]['max'] = "";
} else {
$n = $k - 1;
$sendArr2[$k]['min'] = $colAll[$n];
$sendArr2[$k]['max'] = $colAll[$k] - $colAll[$n];
}
}
$dec = json_encode($sendArr2);
$model = AppBsColi::model()->findByPk($month);
if (empty($model)) {
$model = new AppBsColi();
$model->month = $month;
$model->type = 0;
}
$model->desc = $dec;
$model->save();
$str = "";
while (!feof($file_handle)) {
$arr = fgetcsv($file_handle);
if (empty($arr[0])) {
break;
}
foreach ($arr as $k => $val) {
$arr[$k] = trim(iconv("GBK", "UTF-8//IGNORE", $val));
}
$desca = implode("|!|", $arr);
if (isset($arr[3]) && !empty($arr[0])) {
$str .= sprintf("('%s','%s','%s','%s','%s','%s'),", $month, $arr[0], $arr[1], $arr[2], $desca, $arr[3]);
}
}
fclose($file_handle);
if (empty($str)) {
return "更新数据失败";
} else {
$this->deleteAll("month=:mh", array(":mh" => $month));
$sql .= rtrim($str, ",");
$sqlCom = $connection->createCommand($sql)->execute();
return "添加数据成功";
}
}
