<?php

namespace frontend\controllers;

use Yii;
use yii\helpers\Html;
use yii\helpers\ArrayHelper; // load classes
use common\models\LoginForm;
use frontend\models\PasswordResetRequestForm;
use frontend\models\ResetPasswordForm;
use frontend\models\SignupForm;
use app\models\Mainsearch;
use app\models\Mainsearchltr;
use app\models\ReciveBarcode;
use app\models\ReciveBarcodeltr;
use app\models\Allsearch;
use app\models\BarcodeGen;
use app\models\Filerecact;
use app\models\FileHistory;
use app\models\LetterHistory;
use app\models\BanchHistory;
use frontend\models\ContactForm;
use yii\base\InvalidParamException;
use yii\web\BadRequestHttpException;
use yii\web\Controller;
use yii\filters\VerbFilter;
use yii\filters\AccessControl;
use app\models\LetterEntry;
use app\models\PasswordForm;
use app\models\User;
use app\models\DesignationMaster;
/**
 * Site controller
 */
class SiteController extends Controller
{

    /**
     * @inheritdoc
     */
    public function behaviors()
    {
        return [
            'access' => [
                'class' => AccessControl::className(),
                'only' => ['logout', 'signup'],
                'rules' => [
                    [
                        'actions' => [''],
                        'allow' => true,
                        'roles' => ['?'],
                    ],
                    [
                        'actions' => ['logout', 'signup'],
                        'allow' => true,
                        'roles' => ['@'],
                    ],
                ],
            ],
            'verbs' => [
                'class' => VerbFilter::className(),
                'actions' => [
                    'logout' => ['POST'],
                ],
            ],
        ];
    }

    /**
     * @inheritdoc
     */
    public function actions()
    {
        return [
            'error' => [
                'class' => 'yii\web\ErrorAction',
            ],
            'captcha' => [
                'class' => 'yii\captcha\CaptchaAction',
                'fixedVerifyCode' => YII_ENV_TEST ? 'testme' : null,
            ],
        ];
    }

    /**
     * Displays homepage.
     *
     * @return mixed
     */
    public function actionIndex()
    {
        
        if (!\Yii::$app->user->isGuest)
        {
//              if (Yii::$app->user->can('user'))
//        {
            return $this->render('index');
//        }
//         else
//        {
//            throw new ForbiddenHttpException;
//        }
        } else
        {
           
            return $this->redirect("site/login");
        }
        
    }

    /**
     * Logs in a user.
     *
     * @return mixed
     */
    public function actionLogin()
    {
        if (!\Yii::$app->user->isGuest)
        {
            return $this->render('index');
        }

        $model = new LoginForm();
        if ($model->load(Yii::$app->request->post()) && $model->login())
        {
            return $this->goBack();
        } else
        {
            return $this->render('login', [
                        'model' => $model,
            ]);
        }
    }

    /**
     * Logs out the current user.
     *
     * @return mixed
     */
    public function actionLogout()
    {
        Yii::$app->user->logout();

        return $this->goHome();
    }

    /**
     * Displays contact page.
     *
     * @return mixed
     */
    public function actionContact()
    {
        $model = new ContactForm();
        if ($model->load(Yii::$app->request->post()) && $model->validate())
        {
            if ($model->sendEmail(Yii::$app->params['adminEmail']))
            {
                Yii::$app->session->setFlash('success', 'Thank you for contacting us. We will respond to you as soon as possible.');
            } else
            {
                Yii::$app->session->setFlash('error', 'There was an error sending email.');
            }

            return $this->refresh();
        } else
        {
            return $this->render('contact', [
                        'model' => $model,
            ]);
        }
    }

    /**
     * Displays about page.
     *
     * @return mixed
     */
    public function actionAbout()
    {
        return $this->render('about');
    }
   public function actionRecive($id)
    {
      //$pid =   Yii::$app->getRequest()->getQueryParam('id');
                         date_default_timezone_set("Asia/Kolkata"); 
               $date = date('Y-m-d H:i:s');
               $zone = Yii::$app->user->identity->zone_id;
               $result1 = Yii::$app->db->createCommand("update entry set receive=1 where project_id='$id'")->execute();
               $result2 = Yii::$app->db->createCommand("update file_process set receive=1, receive_date='$date'  where project_id='$id' AND receive=0 AND zone_id='$zone'")->execute();
        return $this->redirect('recevief');
    }
    public function actionSearch()
    {
        $model = new Mainsearch;
        $mid = 0;
        if ($model->load(Yii::$app->request->post()) and $model->validate())
        {
            echo $id = $_POST['Mainsearch']['project_id'];
            echo $nasti = $_POST['nasti'];

            $did =  Yii::$app->user->identity->d_id;
            if(!empty($id) && empty($nasti))
            {
              //$result = Yii::$app->db->createCommand("select * from entry where project_id='$id' and d_id='$did' and close=0")->queryAll();  
              $result = Yii::$app->db->createCommand("select * from entry where project_id='$id' and close=0")->queryAll();  
              
            }
            elseif (!empty($nasti) && empty($id)) {
              $result = Yii::$app->db->createCommand("select project_id from entry where work_detail='$nasti' and close=0")->queryOne();   
              $id = $result['project_id'];
            }

            if (count($result) > 0)
            {
          return $this->redirect("../file-process/create?project_id=$id");
         } else
            {
                $mid = 1;
        return $this->render('search', ['model' => $model, 'result' => $mid]);
            }
        }
        return $this->render('search', ['model' => $model, 'result' => $mid]);
        //   return $this->render('file-entry/create');
    }
 public function actionSearchltr()
    {
        $model = new Mainsearchltr;
        $mid = 0;
//        $p_id = Yii::$app->getRequest()->getQueryParam('p_id');
        if ($model->load(Yii::$app->request->post()) and $model->validate())
        {
            $id = $_POST['Mainsearchltr']['letter_id'];
            //return $this->render('//FileProcessController/create'); 1
            $did =  Yii::$app->user->identity->d_id;
            $result = Yii::$app->db->createCommand("select * from letter_entry where letter_no='$id' and d_id='$did' and close=0")->queryAll();
            if (count($result) > 0)
            {
          return $this->redirect("../letter-process/create?project_id=$id");
         } else
            {
                $mid = 1;
        return $this->render('searchltr', ['model' => $model, 'result' => $mid]);
            }
        }
        return $this->render('searchltr', ['model' => $model, 'result' => $mid]);
        //   return $this->render('file-entry/create');
    }
     public function actionRecivebarcode()
    {
        $model = new ReciveBarcode;
        $mid = 0;
//        $p_id = Yii::$app->getRequest()->getQueryParam('p_id');
        if ($model->load(Yii::$app->request->post()) and $model->validate())
        { 
            $pid = $_POST['ReciveBarcode']['project_id'];
            //return $this->render('//FileProcessController/create'); 1
            $zone =  Yii::$app->user->identity->zone_id;
            $result = Yii::$app->db->createCommand("select * from entry where project_id='$pid' and d_id='$zone' and receive=0")->queryAll();
            if (count($result) > 0)
            {
            date_default_timezone_set("Asia/Kolkata"); 
             $date = date('Y-m-d');
               $result1 = Yii::$app->db->createCommand("update entry set receive=1 where project_id='$pid'")->execute();
               $result2 = Yii::$app->db->createCommand("update file_process set receive=1, receive_date='$date'  where project_id='$pid' AND receive=0 AND zone_id='$zone'")->execute();
            $mid=2;
               return $this->render('recivebarcode', ['model' => $model, 'result' => $mid]);
                } else
            {
                $mid = 1;
        return $this->render('recivebarcode', ['model' => $model, 'result' => $mid]);
            }
        }
        return $this->render('recivebarcode', ['model' => $model, 'result' => $mid]);
        //   return $this->render('file-entry/create');
    }
    
     public function actionRecivebarcodeltr()
    {
        $model = new ReciveBarcodeltr;
        $mid = 0;
//        $p_id = Yii::$app->getRequest()->getQueryParam('p_id');
        if ($model->load(Yii::$app->request->post()) and $model->validate())
        {
            $pid = $_POST['ReciveBarcodeltr']['letter_no'];
            //return $this->render('//FileProcessController/create'); 1
            $did =  Yii::$app->user->identity->d_id;
            $result = Yii::$app->db->createCommand("select * from letter_entry where letter_no='$pid' and d_id='$did' and receive=0")->queryAll();
            if (count($result) > 0)
            {
            date_default_timezone_set("Asia/Kolkata"); 
             $date = date('Y-m-d');
               $result1 = Yii::$app->db->createCommand("update letter_entry set receive=1 where letter_no='$pid'")->execute();
               
               $result2 = Yii::$app->db->createCommand("update letter_process set receive=1, receive_date='$date'  where project_id='$pid' AND receive=0 AND to_d_id='$did'")->execute();
            $mid=2;
               return $this->render('recivebarcodeltr', ['model' => $model, 'result' => $mid]);
                } else
            {
                $mid = 1;
        return $this->render('recivebarcodeltr', ['model' => $model, 'result' => $mid]);
            }
        }
        return $this->render('recivebarcodeltr', ['model' => $model, 'result' => $mid]);
        //   return $this->render('file-entry/create');
    }
    public function actionSearchall()
    {
        $model = new Allsearch;
        $d_id = 0;
        $t_id = 0;
        $v_id = 0;
        $year = 0;
        $patwari_hno = 0;
        if ($model->load(Yii::$app->request->post()) and $model->validate())
        {
            $d_id = $_POST['Allsearch']['d_id'];
            $t_id = $_POST['Allsearch']['t_id'];
            $v_id = $_POST['Allsearch']['v_id'];
            $year = $_POST['Allsearch']['year'];
            $patwari_hno = $_POST['Allsearch']['patwari_hno'];
            //  $result=FileEntry::find(['barcode'=>$id])->one();
        }
        return $this->render('searchall', ['model' => $model, 'd_id' => $d_id, 't_id' => $t_id, 'v_id' => $v_id, 'year' => $year, 'patwari_hno' => $patwari_hno]);
    }

    public function actionBarcode_gen()
    {

        return $this->render('barcode_gen');
    }
        public function actionBarcode_gen_ltr()
    {

        return $this->render('barcode_gen_ltr');
    }
    public function actionHistory()
    {
      $nasti="";
      $vishay="";
      $date="";

      if($a = Yii::$app->request->post())
        /*print_r($a);
        die;*/
      {
        if(!$a['FileHistory']['project_id']=='')
        {
          $id = $a['FileHistory']['project_id'];
        }
        if(!$a['nasti']=='')
        {  
          $nasti = $a['nasti'];
        }
        if(!$a['vishay']=='')
        {  
           $vishay = $a['vishay'];
        }
        if(!$a['date']=='')
        {  
           $date = $a['date'];
        }

      }
    
           if (!\Yii::$app->user->isGuest)
        {

        $model = new FileHistory;
        $id = 0;
        if ($model->load(Yii::$app->request->post()) and $model->validate())
        {
            $id = $_POST['FileHistory']['project_id'];
            //  $result=FileEntry::find(['barcode'=>$id])->one();
        }

        // echo $id;
        // die;

        return $this->render('history', [
                                    'model' => $model, 
                                    'result' => $id ,
                                    'result2'=>$nasti,
                                    'result3'=>$vishay,
                                    'result4'=>$date
                                        ]
                            );
     } else
        {
           
            return $this->redirect("login");
        }
    }
    
     public function actionHistoryltr()
    {
           if (!\Yii::$app->user->isGuest)
        {
        $model = new LetterHistory;
        $id = 0;
        if ($model->load(Yii::$app->request->post()) and $model->validate())
        {
            $id = $_POST['LetterHistory']['letter_no'];
            //  $result=FileEntry::find(['barcode'=>$id])->one();
        }


        return $this->render('historyltr', ['model' => $model, 'result' => $id]);
     } else
        {
           
            return $this->redirect("login");
        }
    }
         public function actionHistorybanch()
    {
           if (!\Yii::$app->user->isGuest)
        {
        $model = new BanchHistory;
        $id = 0;
        if ($model->load(Yii::$app->request->post()) and $model->validate())
        {
            $id = $_POST['BanchHistory']['file_no'];
            //  $result=FileEntry::find(['barcode'=>$id])->one();
        }


        return $this->render('historybanch', ['model' => $model, 'result' => $id]);
     } else
        {
           
            return $this->redirect("login");
        }
    }
        public function actionFileview()
    {
           if (!\Yii::$app->user->isGuest)
        {
       
        return $this->render('fileview');
     } else
        {
           
            return $this->redirect("login");
        }
    }
        public function actionRecevief()
    {
           if (!\Yii::$app->user->isGuest)
        {
      
        return $this->render('recevief');
     } else
        {
           
            return $this->redirect("login");
        }
    }
     public function actionRecevieltr()
    {
           if (!\Yii::$app->user->isGuest)
        {
      
        return $this->render('recevieltr');
     } else
        {
           
            return $this->redirect("login");
        }
    }
    
         public function actionUnrecevieltr()
    {
           if (!\Yii::$app->user->isGuest)
        {
      
        return $this->render('unrecevieltr');
     } else
        {
           
            return $this->redirect("login");
        }
    }
           public function actionMybunchfiles()
    {
           if (!\Yii::$app->user->isGuest)
        {
      
        return $this->render('mybunchfiles');
     } else
        {
           
            return $this->redirect("login");
        }
    }
               public function actionRecbunchfiles()
    {
           if (!\Yii::$app->user->isGuest)
        {
      
        return $this->render('recbunchfiles');
     } else
        {
           
            return $this->redirect("login");
        }
    }
         public function actionReciveactltr()
    {
           if (!\Yii::$app->user->isGuest)
        {
      
        return $this->render('reciveactltr');
     } else
        {
           
            return $this->redirect("login");
        }
    }
             public function actionReciveactbanch()
    {
        $model = new Filerecact;
        $mid = 0;
//        $p_id = Yii::$app->getRequest()->getQueryParam('p_id');
        if ($model->load(Yii::$app->request->post()) and $model->validate())
        {
            date_default_timezone_set("Asia/Kolkata"); 
             $date = date('Y-m-d');
             $d_id = Yii::$app->user->identity->d_id;
            $letters = $_POST['Filerecact']['letters'];
             $pid = $_POST['Filerecact']['file_no'];
            // die();
             $rec_ltrs = '';
             foreach ($letters as $value)
             {
             
              $result1 = Yii::$app->db->createCommand("update letter_entry set receive=1 where letter_no='$value'")->execute();
             $result2 = Yii::$app->db->createCommand("update letter_process set receive=1, receive_date='$date'  where project_id='$value' AND receive=0 AND to_d_id='$d_id'")->execute();
              $result_lll = Yii::$app->db->createCommand("select * from letter_entry where letter_no='$value'")->queryone();
              $rec_ltrs.=$result_lll['letter_id'].',';
             }
            $rec_ltrs =  rtrim($rec_ltrs,','); 
           
              $result_ff = Yii::$app->db->createCommand("select * from file where file_no='$pid'")->queryone();
              $letters_f  = $result_ff['letters'];
              $array_ltrs = explode(',', $letters_f);
              foreach ($array_ltrs as $value)
              {
             $result_ll = Yii::$app->db->createCommand("select * from letter_entry where receive=0 and letter_id='$value'")->queryone();
                if($result_ll>0)
                {
                     $letter_no = $result_ll['letter_no'];    
                    $result_lp = Yii::$app->db->createCommand("select * from letter_process where project_id='$letter_no' order by id desc")->queryone();
                  $fdid =  $result_lp['d_id'];
                  $fdesid =  $result_lp['des_id'];
              $result1 = Yii::$app->db->createCommand("update letter_entry set d_id='$fdid', des_id='$fdesid', receive=2 where letter_no='$letter_no'")->execute();

                }
             
              }
               $result1 = Yii::$app->db->createCommand("update file set recive=1, letters='$rec_ltrs' where file_no='$pid'")->execute();
               $result2 = Yii::$app->db->createCommand("update banch_process set receive=1, receive_date='$date'  where project_id='$pid' AND receive=0 AND to_d_id='$d_id'")->execute();
             
             //  die();
            return $this->redirect("recbunchfiles?id=$pid");
            
     //   return $this->render('reciveactbanch', ['model' => $model, 'result' => $mid]);
           
        } else {
        return $this->render('reciveactbanch', ['model' => $model]);
        }
    }
    public function actionFileallot()
    {
 echo $_REQUEST['id'];
    }

    public function actionSignup()
    {
        $model = new SignupForm();
        if ($model->load(Yii::$app->request->post()))
        {
            //$a = Yii::$app->request->post();
           /* echo "<pre>";
            print_r($a);
            die;*/
            if ($user = $model->signup())
            {
//                if (Yii::$app->getUser()->login($user))
//                {
                   // $uid =  $sqlvalue = Yii::$app->user->identity->id;
          $uid_result = Yii::$app->db->createCommand("select max(id) as id from user")->queryOne();
               $uid2 = $uid_result['id'];
     $ins = Yii::$app->db->createCommand("insert into auth_assignment (item_name,user_id) values('user','$uid2')")->execute();

                    return $this->goHome();
              //  }
            }
        }

        return $this->render('signup', [
                    'model' => $model,
        ]);
    }

    /**
     * Requests password reset.
     *
     * @return mixed
     */
    public function actionRequestPasswordReset()
    {
        $model = new PasswordResetRequestForm();
        if ($model->load(Yii::$app->request->post()) && $model->validate())
        {
            if ($model->sendEmail())
            {
                Yii::$app->session->setFlash('success', 'Check your email for further instructions.');

                return $this->goHome();
            } else
            {
                Yii::$app->session->setFlash('error', 'Sorry, we are unable to reset password for email provided.');
            }
        }

        return $this->render('requestPasswordResetToken', [
                    'model' => $model,
        ]);
    }

    public function actionResetPassword($token)
    {
        try
        {
            $model = new ResetPasswordForm($token);
        } catch (InvalidParamException $e)
        {
            throw new BadRequestHttpException($e->getMessage());
        }

        if ($model->load(Yii::$app->request->post()) && $model->validate() && $model->resetPassword())
        {
            Yii::$app->session->setFlash('success', 'New password was saved.');

            return $this->goHome();
        }

        return $this->render('resetPassword', [
                    'model' => $model,
        ]);
    }
    
    //change password
    
    public function actionChangepassword(){
        $model = new PasswordForm;
        $msg=0;
    
        if($model->load(Yii::$app->request->post())){
            if($model->validate()){
                
              $newpass =   Yii::$app->security->generatePasswordHash($_POST['PasswordForm']['newpass']);
                            $uid =  Yii::$app->user->identity->id;   
                $ins = Yii::$app->db->createCommand("update user set  password_hash='$newpass' where id='$uid'")->execute();

                $msg=1;
                  return $this->render('changepassword',[
                'model'=>$model, 'msg' => $msg
            ]);
            }else{
                 $msg=0;
                return $this->render('changepassword',[
                    'model'=>$model, 'msg' => $msg
                ]);
            }
        }else{
            return $this->render('changepassword',[
                'model'=>$model, 'msg' => $msg
            ]);
        }
    }

public function actionRecevefiles()
    {
        $requestData = $_REQUEST;
        $columns = array(
            0 => 'e.project_id',
            1 => 'e.work_name',
            2 => 'z.zone_name'
        );
        $sql = "SELECT e.project_id, e.work_name,e.work_detail, z.zone_name ";
        $sql.=" FROM entry as e INNER JOIN zone as z ON z.zone_no = e.jon_no WHERE 1=1 AND e.receive=0 AND e.close=0 AND e.jon_no = '".Yii::$app->user->identity->zone_id."'";
        if(Yii::$app->user->identity->role == 3){
            $sql .= " AND e.d_id = '".Yii::$app->user->identity->department."'";
        }
       
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalData = count($data);
        $totalFiltered = $totalData;
        if (!empty($requestData['search']['value']))
        {
            $sql.=" AND (e.project_id LIKE '" . $requestData['search']['value'] . "%' ";
            $sql.=" OR e.work_name LIKE '" . $requestData['search']['value'] . "%' ";

            $sql.=" OR e.yojana_mad LIKE '" . $requestData['search']['value'] . "%' )";
        }
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalFiltered = count($data);
        
        //order by column column array given above
        $sql.=" ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "  LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
        //echo $sql;

        $result = Yii::$app->db->createCommand($sql)->queryAll();
        
        $data = array();
        foreach ($result as $key => $row)
        {
          
            $p_id = $row["project_id"];
         $process  = Yii::$app->db->createCommand("select * FROM file_process as f  inner join department_master as d ON d.id=f.d_id WHERE f.project_id='$p_id' and f.receive=0")->queryOne();
         

         $jh = strtotime($process['datetime']);
         $nestedData = array();
         //   $nestedData[] = $row["project_id"];
         //$nestedData[] =     Html::a($row["project_id"], ['site/fileview?id='.$row["project_id"]]);
         $nestedData[] =     Html::a($row["work_detail"], ['site/fileview?id='.$row["project_id"]]);
            //$nestedData[] = $row["work_detail"];
            $nestedData[] = $row["work_name"];
            $nestedData[] = $row["zone_name"];
            $nestedData[] = $process["d_name"];
            $nestedData[] = date("d F Y H:i:s", $jh);
          $nestedData[] =     Html::a('प्राप्त करे', ['site/recive?id='.$row["project_id"]], ['class' => 'btn btn-success']);

            $data[] = $nestedData;
        }
        
        $json_data = array(
            "draw" => intval($requestData['draw']), 
            "recordsTotal" => intval($totalData),
            "recordsFiltered" => intval($totalFiltered),
            "data" => $data   // total data array
        );

        echo json_encode($json_data);
    }
    
    public function actionRecevefileslist()
    {
        $requestData = $_REQUEST;
        $columns = array(
            0 => 'project_id',
            1 => 'work_name',
            2 => 'yojana_mad'
        );
         
        $sql = "SELECT project_id, work_name, yojana_mad, vibhag_name,vitiya_year ";
        $sql.=" FROM entry WHERE 1=1 AND receive=1 AND close=0  ";
        if(Yii::$app->user->identity->role == 2){
            $sql .= " AND jon_no != original AND jon_no= '".Yii::$app->user->identity->zone_id."' ";
        } 
        
        if(Yii::$app->user->identity->role == 1){
            $sql .= " AND jon_no = '".Yii::$app->user->identity->zone_id."' ";
       }
        if(Yii::$app->user->identity->role == 3){
           $sql.= " AND d_id != original_department AND d_id = '".Yii::$app->user->identity->department."' AND jon_no = '".Yii::$app->user->identity->zone_id."'"; 
        }
       
        $data = Yii::$app->db->createCommand($sql)->queryAll();
     
        $totalData = count($data);
        $totalFiltered = $totalData;
        
        
        //search by specific which is typed in searchbox in datatable
        
        if (!empty($requestData['search']['value']))
        {
            $sql.=" AND (project_id = '" . $requestData['search']['value'] . "%' ";
            $sql.=" OR work_name LIKE '" . $requestData['search']['value'] . "%' ";

            $sql.=" OR yojana_mad LIKE '" . $requestData['search']['value'] . "%' )";
        }
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalFiltered = count($data);
        
        //order by column column array given above
        $sql.=" ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "  LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
        
        $result = Yii::$app->db->createCommand($sql)->queryAll();
        
        $data = array();
        foreach ($result as $key => $row)
        {
          
            $p_id = $row["project_id"];
          $get_nasti  = Yii::$app->db->createCommand("SELECT work_detail from entry where project_id='$p_id'")->queryOne();
          $nasti = $get_nasti['work_detail'];

         $process  = Yii::$app->db->createCommand("select * FROM file_process as f inner join designation_master as des ON des.des_id=f.des_id inner join department_master as d ON d.id=f.d_id WHERE f.project_id='$p_id' and f.receive=0")->queryOne();
         

         $jh = strtotime($process['datetime']);
         $nestedData = array();
         //   $nestedData[] = $row["project_id"];
            //$nestedData[] = Html::a($row["project_id"], ['site/fileview?id='.$row["project_id"]]);
              $nestedData[] = Html::a($nasti, ['site/fileview?id='.$row["project_id"]]);
            $nestedData[] = $row["work_name"];
            //$nestedData[] = $row["vitiya_year"];
            
            $nestedData[] = $row['vibhag_name'];
            $nestedData[] =     Html::a('<i class="btn btn-success">ट्रान्स्फ़र करे</i>', ['file-process/create', 'project_id'=>$row['project_id']]); 

            $data[] = $nestedData;
        }
        
        $json_data = array(
            "draw" => intval($requestData['draw']), 
            "recordsTotal" => intval($totalData),
            "recordsFiltered" => intval($totalFiltered),
            "data" => $data   // total data array
        );

        echo json_encode($json_data);
    }
    
public function actionReceveletters()
   {
        $requestData = $_REQUEST;
        $columns = array(
            0 => 'letter_no',
            1 => 'letter_name',
            2 => 'letter_details'
        );
         $d_id = Yii::$app->user->identity->d_id;
          $des_id =  Yii::$app->user->identity->des_id;
        $sql = "SELECT letter_no, letter_name, letter_details ";
        $sql.=" FROM letter_entry WHERE 1=1 AND in_file=0 and  receive=0 AND close=0 AND d_id='$d_id' and des_id='$des_id'";
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalData = count($data);
        $totalFiltered = $totalData;
        
        
        //search by specific which is typed in searchbox in datatable
        
        if (!empty($requestData['search']['value']))
        {
            $sql.=" AND (letter_no LIKE '" . $requestData['search']['value'] . "%' ";
            $sql.=" OR letter_name LIKE '" . $requestData['search']['value'] . "%' ";

            $sql.=" OR letter_details LIKE '" . $requestData['search']['value'] . "%' )";
        }
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalFiltered = count($data);
        
        //order by column column array given above
        $sql.=" ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "  LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
        $result = Yii::$app->db->createCommand($sql)->queryAll();
        
        $data = array();
        foreach ($result as $key => $row)
        {
          
            $p_id = $row["letter_no"];
         $process  = Yii::$app->db->createCommand("select * FROM file_process as f inner join designation_master as des ON des.des_id=f.des_id inner join department_master as d ON d.id=f.d_id WHERE f.project_id='$p_id' and f.receive=0")->queryOne();
         $jh = strtotime($process['datetime']);
         $nestedData = array();
         //   $nestedData[] = $row["project_id"];
           $nestedData[] =     Html::a($row["letter_no"], ['site/fileview?id='.$row["letter_no"]]);
            $nestedData[] = $row["letter_name"];
            $nestedData[] = $row["letter_details"];
           $nestedData[] = $process["d_name"].'<br>('.$process["des_name"].')';
            $nestedData[] = date("d F Y H:i:s", $jh);
         $nestedData[] =     Html::a('प्राप्त करे', ['site/reciveactltr?id='.$row["letter_no"]], ['class' => 'btn btn-success']);

            $data[] = $nestedData;
        }
        
        $json_data = array(
            "draw" => intval($requestData['draw']), 
            "recordsTotal" => intval($totalData),
            "recordsFiltered" => intval($totalFiltered),
            "data" => $data   // total data array
        );

        echo json_encode($json_data);
    }
    public function actionUnreceveletters()
  {
        $requestData = $_REQUEST;
        $columns = array(
            0 => 'letter_no',
            1 => 'letter_name',
            2 => 'letter_details'
        );
         $d_id = Yii::$app->user->identity->d_id;
          $des_id =  Yii::$app->user->identity->des_id;
        $sql = "SELECT letter_no, letter_name, letter_details ";
        $sql.=" FROM letter_entry WHERE 1=1 AND   receive=2 AND close=0 AND d_id='$d_id' and des_id='$des_id'";
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalData = count($data);
        $totalFiltered = $totalData;
        
        
        //search by specific which is typed in searchbox in datatable
        
        if (!empty($requestData['search']['value']))
        {
            $sql.=" AND (letter_no LIKE '" . $requestData['search']['value'] . "%' ";
            $sql.=" OR letter_name LIKE '" . $requestData['search']['value'] . "%' ";

            $sql.=" OR letter_details LIKE '" . $requestData['search']['value'] . "%' )";
        }
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalFiltered = count($data);
        
        //order by column column array given above
        $sql.=" ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "  LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
        $result = Yii::$app->db->createCommand($sql)->queryAll();
        
        $data = array();
        foreach ($result as $key => $row)
        {
          
            $p_id = $row["letter_no"];
         $process  = Yii::$app->db->createCommand("select * FROM file_process as f inner join designation_master as des ON des.des_id=f.des_id inner join department_master as d ON d.id=f.d_id WHERE f.project_id='$p_id' and f.receive=0")->queryOne();
         $jh = strtotime($process['datetime']);
         $nestedData = array();
         //   $nestedData[] = $row["project_id"];
           $nestedData[] =     Html::a($row["letter_no"], ['site/fileview?id='.$row["letter_no"]]);
            $nestedData[] = $row["letter_name"];
            $nestedData[] = $row["letter_details"];
           $nestedData[] = $process["d_name"].'<br>('.$process["des_name"].')';
            $nestedData[] = date("d F Y H:i:s", $jh);
         $nestedData[] =     Html::a('प्राप्त करे', ['site/reciveactltr?id='.$row["letter_no"]], ['class' => 'btn btn-success']);

            $data[] = $nestedData;
        }
        
        $json_data = array(
            "draw" => intval($requestData['draw']), 
            "recordsTotal" => intval($totalData),
            "recordsFiltered" => intval($totalFiltered),
            "data" => $data   // total data array
        );

        echo json_encode($json_data);
    }
    public function actionMybunchsf()
     {
        $requestData = $_REQUEST;
        $columns = array(
            0 => 'file_no'
        );
         $d_id = Yii::$app->user->identity->d_id;
          $des_id =  Yii::$app->user->identity->des_id;
        $sql = "SELECT * ";
        $sql.=" FROM file WHERE 1=1 AND recive=1 AND open_status=0 AND d_id='$d_id' and des_id='$des_id'";
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalData = count($data);
        $totalFiltered = $totalData;
        
        
        //search by specific which is typed in searchbox in datatable
        
        if (!empty($requestData['search']['value']))
        {
            $sql.=" AND (file_no LIKE '" . $requestData['search']['value'] . "%' ";
        }
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalFiltered = count($data);
        
        //order by column column array given above
    $sql.=" ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "  LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
     // echo $sql;
     // die();
        $result = Yii::$app->db->createCommand($sql)->queryAll();
        
        $data = array();
        foreach ($result as $key => $row)
        {
          
            $p_id = $row["file_id"];
         $process  = Yii::$app->db->createCommand("select * FROM file as f inner join designation_master as des ON des.des_id=f.des_id inner join department_master as d ON d.id=f.d_id WHERE f.file_id='$p_id'")->queryOne();
                        $letters =  $row['letters'];
                           $exp_letters  = explode(",", $letters);
                           $tletters = "";
                           foreach ($exp_letters as $value)
                           {
                              $result_ll = Yii::$app->db->createCommand("select * from letter_entry  where letter_id='$value'")->queryOne();
                                    $tletters.=$result_ll['letter_no'].'<br/>';  
                           }
         //    $jh = strtotime($process['datetime']);
         $nestedData = array();
         //   $nestedData[] = $row["project_id"];
         //  $nestedData[] =     Html::a($row["letter_no"], ['site/fileview?id='.$row["letter_no"]]);
            $nestedData[] = $row["file_no"];
          
           $nestedData[] = $process["d_name"].'<br>('.$process["des_name"].')';
              $nestedData[] = $tletters;
              
           // $nestedData[] = date("d F Y H:i:s", $jh);
      $nestedData[] =     Html::a('View', ['fileview/view?id='.$row["file_id"]], ['class' => 'btn btn-info']).'&nbsp;'.Html::a('Open Bunch', ['site/filebunchopen?id='.$row["file_id"].'&ltrs='.$row["letters"]],['onclick'=>'return confirm("Are you sure want to Open this Bunch file?")','class' => 'btn btn-primary']).'&nbsp'.Html::a('ट्रान्स्फ़र करे', ['banch-process/create?project_id='.$row["file_no"]], ['class' => 'btn btn-success']);

            $data[] = $nestedData;
        }
        
        $json_data = array(
            "draw" => intval($requestData['draw']), 
            "recordsTotal" => intval($totalData),
            "recordsFiltered" => intval($totalFiltered),
            "data" => $data   // total data array
        );

        echo json_encode($json_data);
    }
     public function actionRecbunchsf()
     {
        $requestData = $_REQUEST;
        $columns = array(
            0 => 'file_no'
        );
         $d_id = Yii::$app->user->identity->d_id;
          $des_id =  Yii::$app->user->identity->des_id;
        $sql = "SELECT * ";
        $sql.=" FROM file WHERE 1=1 AND recive=0 AND open_status=0 AND d_id='$d_id' and des_id='$des_id'";
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalData = count($data);
        $totalFiltered = $totalData;
        
        
        //search by specific which is typed in searchbox in datatable
        
        if (!empty($requestData['search']['value']))
        {
            $sql.=" AND (file_no LIKE '" . $requestData['search']['value'] . "%' ";
        }
        $data = Yii::$app->db->createCommand($sql)->queryAll();
        $totalFiltered = count($data);
        
        //order by column column array given above
    $sql.=" ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "  LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
     // echo $sql;
     // die();
        $result = Yii::$app->db->createCommand($sql)->queryAll();
        
        $data = array();
        foreach ($result as $key => $row)
        {
          
            $p_id = $row["file_id"];
         $process  = Yii::$app->db->createCommand("select * FROM file as f inner join designation_master as des ON des.des_id=f.des_id inner join department_master as d ON d.id=f.d_id WHERE f.file_id='$p_id'")->queryOne();
                        $letters =  $row['letters'];
                           $exp_letters  = explode(",", $letters);
                           $tletters = "";
                           foreach ($exp_letters as $value)
                           {
                              $result_ll = Yii::$app->db->createCommand("select * from letter_entry  where letter_id='$value'")->queryOne();
                                    $tletters.=$result_ll['letter_no'].'<br/>';  
                           }
         //    $jh = strtotime($process['datetime']);
         $nestedData = array();
         //   $nestedData[] = $row["project_id"];
         //  $nestedData[] =     Html::a($row["letter_no"], ['site/fileview?id='.$row["letter_no"]]);
            $nestedData[] = $row["file_no"];
          
           $nestedData[] = $process["d_name"].'<br>('.$process["des_name"].')';
              $nestedData[] = $tletters;
              
           // $nestedData[] = date("d F Y H:i:s", $jh);
      $nestedData[] =     Html::a('प्राप्त करे', ['site/reciveactbanch?id='.$row["file_no"].'&act=1'],['onclick'=>'return confirm("Are you sure want to Receive this Bunch file?")','class' => 'btn btn-success']);

            $data[] = $nestedData;
        }
        
        $json_data = array(
            "draw" => intval($requestData['draw']), 
            "recordsTotal" => intval($totalData),
            "recordsFiltered" => intval($totalFiltered),
            "data" => $data   // total data array
        );

        echo json_encode($json_data);
    }
    public function actionGetdesignation()
    {
         $did=$_REQUEST['id'];
        // echo "SELECT * FROM designation_master WHERE d_id=$did";
    $result = Yii::$app->db->createCommand("SELECT * FROM designation_master WHERE d_id in ($did,1)")->queryAll();
 //  echo "<select>";
    foreach($result as $post){

     echo "<option value='".$post['des_id']."'>".$post['des_name']."</option>";
         }
//echo "</select>";
 }
        public function actionGetdesignation_trans()
    {
        $did=$_REQUEST['id'];
         $result = Yii::$app->db->createCommand("SELECT * FROM designation_master WHERE d_id = '$did' and active=1")->queryAll();
       $a = "<option value=''>पद का नाम चुनें</option>";;
    foreach($result as $post){

      $a .= "<option value='".$post['des_id']."'>".$post['des_name']."</option>";
      
      }
      echo $a; 

 }
   public function actionGetdesignation_transltr()
    {
         $did=$_REQUEST['id'];
         $desid= Yii::$app->user->identity->des_id;
          $ddid=  Yii::$app->user->identity->d_id;
         if($ddid==2)
         {
          
    $result = Yii::$app->db->createCommand("SELECT * FROM designation_master WHERE d_id =$did and active=1 and des_id not in('$desid','19','20')")->queryAll();
         }else {
    $result = Yii::$app->db->createCommand("SELECT * FROM designation_master WHERE d_id =$did and active=1 and des_id not in('$desid')")->queryAll();
         }
    foreach($result as $post){

     echo "<option value='".$post['des_id']."'>".$post['des_name']."</option>";
         }
//echo "</select>";
 }
         public function actionFileclose($id=NULL)
          {
               if(Yii::$app->request->post()){
           $remark = Yii::$app->request->post();   
           $remarks =  $remark['remark'];

           $deleteby = Yii::$app->user->identity->id;
           
  $result1 = Yii::$app->db->createCommand("update entry set close=1,close_remark='$remarks' where project_id='$id'")->execute();

  $max_q = Yii::$app->db->createCommand("select max(running_status) as max from file_process where project_id='$id'")->queryOne();
  $max = $max_q['max'];

   $q1 = Yii::$app->db->createCommand("INSERT INTO `close_file` (`pid`, `close_remark`, `close_by`,`running_status`) VALUES ('$id', '$remarks', '$deleteby','$max')")->execute();

return $this->redirect("../my-rec-entry/index?msg=1&id=$id");
                } else {
                  return $this->render('fileclose');
                }
          }
          public function actionFileopen($id=NULL)
          {
               if(Yii::$app->request->get()){

              /*$q1 = Yii::$app->db->createCommand("select count(id) as tot from close_file where pid='$id'")->queryOne();
              $tot= $q1['tot'];
              if($tot==0)
              {

              }
              die;*/

  $result1 = Yii::$app->db->createCommand("update entry set close=0 where project_id='$id'")->execute();

                } 
                return $this->redirect(['my-rec-entry/index']);
          }

                   public function actionFilebunchopen($id=NULL,$ltrs=NULL)
          {
                       $desid= Yii::$app->user->identity->des_id;
                       $did= Yii::$app->user->identity->d_id;
  $result1 = Yii::$app->db->createCommand("update file  set open_status=1,open_by_des='$desid' where file_id='$id'")->execute();
     $letters = explode(',', $ltrs);
     foreach($letters as $lt)
     {
        $result1 = Yii::$app->db->createCommand("update letter_entry  set d_id=$did,des_id='$desid',receive=1,in_file=0,file_id=0 where letter_id='$lt'")->execute();   
     }
  return $this->redirect("../letter-entrymy/index");
          }
            public function actionSelectedfiles()
   {
       $action=Yii::$app->request->post('action');
    $selection=(array)Yii::$app->request->post('selection');//typecasting
    if(!empty($selection))
    {
    $letters = implode(",", $selection);
        $maxb = Yii::$app->db->createCommand('select * from file order by file_id  DESC limit 1')->queryOne();
$mpid =  $maxb['file_id']+1;
$y = date('Y');
$pid=$y.'/FILEDRG/'.$mpid;
    foreach($selection as $id){
Yii::$app->db->createCommand("update letter_entry set in_file=1,file_id='$mpid' where letter_id='$id'")->execute();
    } 

     $desid= Yii::$app->user->identity->des_id;
          $ddid=  Yii::$app->user->identity->d_id;
  $result1 = Yii::$app->db->createCommand("insert into file (file_no,letters,d_id,des_id) values('$pid','$letters','$ddid','$desid')")->execute();
return $this->redirect("mybunchfiles");
    } else 
    {
        return $this->redirect("../letter-entrymy/index");
    }
          }
        }