1、批量更新某个字段
/** * @throws CDbException * @throws CException * update xxxTable set column1 = case pk * when whenData1 then caseData1 * ... * END * where id in (1,2,3...) */ public function batchUpdateColumnByPK($pk, $params) { if (!$params || !$pk) { return false; } $sql = ' UPDATE ' . $this->tableName() . ' SET '; $count = 0; $pks = []; foreach ($params as $column => $whenCaseMaps) { $totalCount = count($whenCaseMaps); $sql .= ' `' . $column . '` ' . ' = CASE ' . $pk; foreach ($whenCaseMaps as $when => $then) { $sql .= ' WHEN \'' . $when . '\' THEN \'' . $then . '\' '; if (!in_array($when, $pks)) { $pks[] = $when; } ++$count; } if ($count == $totalCount) { $sql .= ' END '; } else { $sql .= ' END, '; } } $sql .= ' WHERE ' . $pk . ' IN (' . implode(',', $pks) . ' );'; return $this->getDbConnection()->createCommand($sql)->execute(); }
2、批量更新或插入
/** * @throws CDbException * @throws CException */ public function saveAll($arrays){ $arrayMap = []; $values = ""; foreach ($arrays as $key => $array){ if (!isset($array['OvertimeTotalDays'])) { $array['OvertimeTotalDays'] = 0; } if (!isset($array['DutyTotalDays'])) { $array['DutyTotalDays'] = 0; } if (!isset($array['ConvertedDays'])) { $array['ConvertedDays'] = 0; } if (!isset($array['OvertimeMemo'])) { $array['OvertimeMemo'] = ''; } if (!isset($array['DutyMemo'])) { $array['DutyMemo'] = ''; } foreach ($array as $k => $column){ $arrayMap[$k.$key] = $column; } if ($key != sizeof($arrays)-1){ $values.="(:StaffID".$key.",:Month".$key.",:Workdays".$key.",:LeaveDays".$key.",:LeaveInfo".$key.",:PaidLeave".$key.",:FullAttendancePrice".$key.",:OvertimePrice".$key.",:LateNumbers".$key."," . ":LeaveEarlyNumbers".$key.",:NoSignNumbers".$key.",:AbsentNumbers".$key.",:NoWorkNumbers".$key.",:Memo".$key.",:Status".$key.",:Islatest".$key.",:TiaoxiuDays".$key.",:TiaoxiuInfo".$key."," .":SickDays".$key.",:SickInfo".$key.", :PaidSick".$key.", :CommonSick".$key.", :LateTimes".$key.",:EarlyTimes".$key.",:Foodsubsidy".$key.",:Carsubsidy".$key.",:Suppersubsidy".$key.",:OvertimeTotalDays".$key.",:DutyTotalDays".$key.",:ConvertedDays".$key.",:OvertimeMemo".$key.",:DutyMemo".$key."),"; }else{ $values.="(:StaffID".$key.",:Month".$key.",:Workdays".$key.",:LeaveDays".$key.",:LeaveInfo".$key.",:PaidLeave".$key.",:FullAttendancePrice".$key.",:OvertimePrice".$key.",:LateNumbers".$key."," . ":LeaveEarlyNumbers".$key.",:NoSignNumbers".$key.",:AbsentNumbers".$key.",:NoWorkNumbers".$key.",:Memo".$key.",:Status".$key.",:Islatest".$key.",:TiaoxiuDays".$key.",:TiaoxiuInfo".$key."," .":SickDays".$key.",:SickInfo".$key.", :PaidSick".$key.", :CommonSick".$key.", :LateTimes".$key.",:EarlyTimes".$key.",:Foodsubsidy".$key.",:Carsubsidy".$key.",:Suppersubsidy".$key.",:OvertimeTotalDays".$key.",:DutyTotalDays".$key.",:ConvertedDays".$key.",:OvertimeMemo".$key.",:DutyMemo".$key.") "; } } $sql = "insert into V2_OADB.tblAttendanceMonthInfo(StaffID,Month,Workdays,LeaveDays,LeaveInfo,PaidLeave,FullAttendancePrice,OvertimePrice," ."LateNumbers,LeaveEarlyNumbers,NoSignNumbers,AbsentNumbers,NoWorkNumbers,Memo,Status,Islatest,TiaoxiuDays,TiaoxiuInfo,SickDays," ."SickInfo,PaidSick, CommonSick, LateTimes,EarlyTimes,Foodsubsidy,Carsubsidy,Suppersubsidy,OvertimeTotalDays,DutyTotalDays,ConvertedDays,OvertimeMemo,DutyMemo) " . "values".$values." on duplicate key update " . "Workdays=values(Workdays),LeaveDays=values(LeaveDays),LeaveInfo=values(LeaveInfo),PaidLeave=values(PaidLeave),FullAttendancePrice=values(FullAttendancePrice)," . "OvertimePrice=values(OvertimePrice),LateNumbers=values(LateNumbers),LeaveEarlyNumbers=values(LeaveEarlyNumbers),NoSignNumbers=values(NoSignNumbers)," . "AbsentNumbers=values(AbsentNumbers),NoWorkNumbers=values(NoWorkNumbers),Memo=values(Memo),Status=values(Status),Islatest=values(Islatest)," ."TiaoxiuDays=values(TiaoxiuDays),TiaoxiuInfo=values(TiaoxiuInfo),SickDays=values(SickDays),PaidSick=values(PaidSick), CommonSick=values(CommonSick), SickInfo=values(SickInfo),IsManualEdit=values(IsManualEdit)," ."LateTimes=values(LateTimes),EarlyTimes=values(EarlyTimes),Foodsubsidy=values(Foodsubsidy),Carsubsidy=values(Carsubsidy),OvertimeTotalDays=values(OvertimeTotalDays), DutyTotalDays=values(DutyTotalDays),ConvertedDays=values(ConvertedDays),OvertimeMemo=values(OvertimeMemo),DutyMemo=values(DutyMemo) "; return ModAttendanceMonthInfo::model()->getDbConnection()->createCommand($sql)->execute($arrayMap); }
3、自动更新数据库字段
public function beforeSave($insert){ if(parent::beforeSave($insert)){ if($this->isNewRecord){ //判断是更新还是插入 $this->AddTime = TimeUtil::time(); $this ->AddBy = 'xxxdsasx'; $this->EditTime = TimeUtil::time(); $this->EditBy = 'xxasdxx'; }else{ $this->EditTime = TimeUtil::time(); $this->EditBy = 'sdasdasd'; } return true; }else{ return false; } }
标签:DutyTotalDays,OvertimeMemo,封装,代码,values,key,sql,array,yii2 From: https://www.cnblogs.com/Adam-Ye/p/15321438.html