yii2代码封装

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;
        }
    }