12313

[mrzaizai2233]

public function actionTest(){

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', Yii::t('campaign_reports', 'Campaign'));
        $sheet->setCellValue('B1', Yii::t('campaign_reports', 'Event Start Date'));
        $sheet->setCellValue('C1', Yii::t('campaign_reports', 'Event Stop Date'));
        $sheet->setCellValue('D1', Yii::t('campaign_reports', 'Total Num. of emails'));
        $sheet->setCellValue('E1', Yii::t('campaign_reports', 'Num. of sent emails'));
        $sheet->setCellValue('F1', Yii::t('campaign_reports', 'Num. of viewed emails'));
        $sheet->setCellValue('G1', Yii::t('campaign_reports', 'Num. of failed emails'));
        $sql = "SELECT
            c.campaign_id, l.list_id, COUNT(ls.subscriber_id) as total_subcriber,c.name,date_format(c.started_at,'%d-%m-%Y %h:%i:%s') as started_at,date_format(c.finished_at,'%d-%m-%Y %h:%i:%s') as finished_at,
            (
                SELECT COUNT(DISTINCT cto.subscriber_id) FROM {{campaign_track_open}} cto  WHERE cto.campaign_id = c.campaign_id
            ) as viewed,
            (
                SELECT COUNT(*) FROM {{campaign_delivery_log}} cdl WHERE cdl.campaign_id = c.campaign_id
            ) as sent,
            (
                SELECT COUNT(*) FROM {{campaign_delivery_log}} cdl WHERE cdl.campaign_id = c.campaign_id AND cdl.status <> 'success'
            ) as failed
        FROM
            {{campaign}} c
        INNER JOIN {{list}} l ON c.list_id = l.list_id
        INNER JOIN {{list_subscriber}} ls ON ls.list_id = l.list_id
        WHERE
            c.status <> 'pending-delete' AND 
            c.campaign_id = :cid
            GROUP BY c.campaign_id";
        $models  =  Yii::app()->getDb()->createCommand($sql)->queryAll(true,array(
            ":cid"=>56
        ));
        foreach ($models as $index =>$model) {
            $sheet->setCellValue('A2',$models['name']);
            $sheet->setCellValue('B2',$models['started_at']);
            $sheet->setCellValue('C2',$models['finished_at']);
            $sheet->setCellValue('D2',$models['total_subcriber']);
            $sheet->setCellValue('E2',$models['sent']);
            $sheet->setCellValue('F2',$models['viewed']);
            $sheet->setCellValue('G2',$models['failed']);
        }
        $writer = new Xlsx($spreadsheet);
//        $writer->save('hello world.xlsx');
    }

[mrzaizai2233]

    public function actionTest(){

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', Yii::t('campaign_reports', 'Campaign'));
        $sheet->setCellValue('B1', Yii::t('campaign_reports', 'Event Start Date'));
        $sheet->setCellValue('C1', Yii::t('campaign_reports', 'Event Stop Date'));
        $sheet->setCellValue('D1', Yii::t('campaign_reports', 'Total Num. of emails'));
        $sheet->setCellValue('E1', Yii::t('campaign_reports', 'Num. of sent emails'));
        $sheet->setCellValue('F1', Yii::t('campaign_reports', 'Num. of viewed emails'));
        $sheet->setCellValue('G1', Yii::t('campaign_reports', 'Num. of failed emails'));
        $sql = "SELECT
            c.campaign_id, l.list_id, COUNT(ls.subscriber_id) as total_subcriber,c.name,date_format(c.started_at,'%d-%m-%Y %h:%i:%s') as started_at,date_format(c.finished_at,'%d-%m-%Y %h:%i:%s') as finished_at,
            (
                SELECT COUNT(DISTINCT cto.subscriber_id) FROM {{campaign_track_open}} cto  WHERE cto.campaign_id = c.campaign_id
            ) as viewed,
            (
                SELECT COUNT(*) FROM {{campaign_delivery_log}} cdl WHERE cdl.campaign_id = c.campaign_id
            ) as sent,
            (
                SELECT COUNT(*) FROM {{campaign_delivery_log}} cdl WHERE cdl.campaign_id = c.campaign_id AND cdl.status <> 'success'
            ) as failed
        FROM
            {{campaign}} c
        INNER JOIN {{list}} l ON c.list_id = l.list_id
        INNER JOIN {{list_subscriber}} ls ON ls.list_id = l.list_id
        WHERE
            c.status <> 'pending-delete' AND 
            c.campaign_id = :cid
            GROUP BY c.campaign_id";
        $models  =  Yii::app()->getDb()->createCommand($sql)->queryAll(true,array(
            ":cid"=>12
        ));
        foreach ($models as $index =>$model) {

            $sheet->setCellValue('A2',$model['name']);
            $sheet->setCellValue('B2',$model['started_at']);
            $sheet->setCellValue('C2',$model['finished_at']);
            $sheet->setCellValue('D2',$model['total_subcriber']);
            $sheet->setCellValue('E2',$model['sent']);
            $sheet->setCellValue('F2',$model['viewed']);
            $sheet->setCellValue('G2',$model['failed']);
        }
        $writer = new Xlsx($spreadsheet);
        $file_name = time();
        $writer->save($file_name.'.xlsx');

        ##### server
        $id=4;

        $email = 'kiepsongthuapc@gmail.com';
        $_server = DeliveryServer::model()->findByPk((int)$id);

        if (empty($_server)) {
            throw new CHttpException(404, Yii::t('app', 'The requested page does not exist.'));
        }

        if (!FilterVarHelper::email($email)) {
            throw new CHttpException(500, Yii::t('app', 'The email address you provided does not seem to be valid.'));
        }

        $mapping = DeliveryServer::getTypesMapping();
        if (!isset($mapping[$_server->type])) {
            throw new CHttpException(404, Yii::t('app', 'The requested page does not exist.'));
        }

        $server = DeliveryServer::model($mapping[$_server->type])->findByPk((int)$_server->server_id);

        $params = $server->getParamsArray(array(
            'to'        => $email,
            'subject'   => Yii::t('servers', 'Please validate this server.'),
            'body'      => "test thu cai nhe",
        ));
        $params['attachments'][] = Yii::getPathOfAlias('root') . '/customer/'.$file_name.'.xlsx';
        if ($server->sendEmail($params)) {
                echo "ok:0";
        } else {
            echo "no";
        }
    }