mysql load data 更新_MySQL LOAD DATA

* @Author: Awe

* @Date: 2016-10-26 17:26:54

* @Last Modified by: Awe

* @Last Modified time: 2017-05-30 12:57:18*/

classEmailBusiness extends AbstractModel{publicfunction sendEmail(){

$sql= "SELECT email,body,`subject`,name FROM hr_send_email_all_task WHERE is_lock=0 AND (send_status=0 OR send_status=2);";

$rows= $this->db(0)->find($sql);foreach ($rows as$val) {

$accepter= $val['email'];

$tite= $val['subject'];

$bodys= $val['body'];

$result=Common::Postmail($accepter ,$tite , $bodys);

$this->setsendemailalltask($result);

}

echo"success";

}//更新邮件发送状态

privatefunction setsendemailalltask($result) {

$now= date('Y-m-d H:i:s');if($result) {

$sql= "UPDATE hr_send_email_all_task SET is_lock=1,lock_date='{$now}',send_status=1,send_date='{$now} '";

}else{

$sql= "UPDATE hr_send_email_all_task SET is_lock=1,lock_date='{$now}',send_status=2";

}

$this->db(0)->Exec($sql);

}publicfunction addMailQueue(){

$file= APP_PATH . "/log/tmp/mailQueue.lock.txt";

$fp= fopen($file,'a+');if(flock($fp, LOCK_EX)) {

$logFile= Log_file::getInstance(array('filename' => "addMailQueueLog"));

$lock_message= "file is locked time is :".date("Y-m-d H:i:s", time())." ".PHP_EOL;

fwrite($fp, $lock_message);

$result= $this->doAddMailQueue();

$logFile->Write("info", $result );

echo $result ;

flock($fp,LOCK_UN);

}else{

echo"其他的进程正在处理。。。";

}

fclose($fp);

}publicfunction doAddMailQueue(){

$sql= "select * from mail_queue where status = 0 and ischeck = '1' order by create_date desc limit 1";

$info= $this->db(4)->findOne($sql);if(empty($info)){return "no data";

}

$file= $info['file'];

$body= $info['content'];

$subject= $info['title'];

$emailType= "queue_{$info['id']}";

$pre_count= 1000;if(!file_exists($file)){return "file $file is not exists";

}

$csvreader= newCsvReader( $file);

$line_number= $csvreader->get_lines();

$date= date("Y-m-d H:i:s");

$sqlFile= str_replace("\\", "/", APP_PATH) . "/tmp/"."sql_" . date("Y_m_d_H_i_s",time()) . ".txt";

$page= intval($line_number/$pre_count)+1;

$successNum= 0;for ($i = 0;$i < $page;$i++){

$str= '';

$data= $csvreader->get_data($pre_count , $i *$pre_count);if(empty($data)){continue;

}foreach ($data as $key =>$value) {

$email= isset($value[0]) ? trim($value[0]) : '';if(!filter_var($email, FILTER_VALIDATE_EMAIL)){continue;

}

$bodys= $this->replaceEmailBody($email , $body , $emailType );

$str.= $email . "$#####$$" . $bodys . "$#####$$" . $date ."$#####$$" . $subject ."v_@rdasd32eplace@";

$successNum++;

}

$str= rtrim($str , "v_@rdasd32eplace@");

file_put_contents($sqlFile , $str , FILE_APPEND );//unset($psql);

unset($data);

}

$sql= "LOAD DATA INFILE '{$sqlFile}' INTO TABLE email_all_task CHARACTER SET utf8 FIELDS TERMINATED BY '$#####$$' LINES TERMINATED BY 'v_@rdasd32eplace@' (email,body,create_date,subject)";

$num= $this->db(4)->Exec($sql);if($num > 0){

$now=date("Y-m-d H:i:s") ;

$this->db(4)->Exec("update mail_queue set status = 1 ,success_num = '{$successNum}' , operate_date = '{$now}' where id = '{$info['id']}'");//删除csv文件

@unlink($file);return "success---编号id为:{$info['id']}---标题是：{$subject}";

}else{return "fail";

}

}

function replaceEmailBody($email , $content , $emailType= ''){

$url= "http://testwww.shixiba.com/analyze.php?email={$email}&emailType={$emailType}";

$content .= '';return$content ;

}

}