[moka同学笔记]YII2.0 判断签约状态，sql的两种查询方法

方法一：

//判断签约状态
                                $signed = 0;
                                $sql="SELECT * from usho_community_sign_record WHERE com_id=$r->id AND
                                end_date = (SELECT MAX(end_date) AS Largest_End_date from usho_community_sign_record WHERE com_id=$r->id)";
                                $sign_model = Yii::$app->db->createCommand($sql)->queryAll();

                                if(!empty($sign_model)){
                                       foreach($sign_model as $s){
                                        $start_date = $s['start_date'];
                                        $end_date = $s['end_date'];
                                        $now_date = date("Y-m-d H:i:s",time());
                                        if($start_date >$now_date){
                                            $signed = 3;
                                        }
                                        if($start_date <$now_date && $end_date > $now_date){
                                            $signed = 1;
                                        }elseif($end_date < $now_date){
                                            $signed = 2;
                                        }
                                    }
                                }

                                switch($signed){
                                    case 0:
                                        echo '<span style="color: #FA031C">未签约</span>';
                                        break;
                                    case 1:
                                        echo '<span style="color: #0AD108">履行中</span>';
                                        break;
                                    case 2:
                                        echo '<span style="color: #999">已完成</span>';
                                        break;
                                    case 3:
                                        echo '<span style="color: #D48905">未开始</span>';
                                        break;
                                    default:
                                        echo '<span style="color: #FA031C">未知</span>';
                                }

 

方法二：

<?php
                                //判断签约状态
                                $signed = 0;
                                $sign_record = CommunitySignRecord::find()->where(['com_id'=>$r->id])->max('end_date');
                                $sign_model = CommunitySignRecord::find()->where(['com_id'=>$r->id,'end_date'=>$sign_record])->one();

                                if(!empty($sign_model)){
                                        $start_date = $sign_model->start_date;
                                        $end_date = $sign_model->end_date;
                                        $now_date = date("Y-m-d H:i:s",time());
                                        if($start_date >$now_date){
                                            $signed = 3;
                                        }
                                        if($start_date <$now_date && $end_date > $now_date){
                                            $signed = 1;
                                        }elseif($end_date < $now_date){
                                            $signed = 2;
                                        }
                                }

                                switch($signed){
                                    case 0:
                                        echo '<span style="color: #FA031C">未签约</span>';
                                        break;
                                    case 1:
                                        echo '<span style="color: #0AD108">履行中</span>';
                                        break;
                                    case 2:
                                        echo '<span style="color: #999">已完成</span>';
                                        break;
                                    case 3:
                                        echo '<span style="color: #D48905">未开始</span>';
                                        break;
                                    default:
                                        echo '<span style="color: #FA031C">未知</span>';
                                }
                                ?>