<?php

/**
 *
 */
class UsersClass extends Viewdata
{
  public function LoadUsers($request)
  {
    $name_table = "
    t_system_users ts
    INNER JOIN t_system_role_user tsru ON ts.Id = tsru.UserId
    INNER JOIN t_system_role tsr ON tsru.RoleId = tsr.Id
    ";

    $count = "ts.*, tsru.RoleId, tsru.UserId, tsr.RoleName";
    //$count = "trs.*, ts.UserName, ts.EmailAddress, ts.Title, ts.Name, ts.MiddleName, ts.Surname, tr.RoleName, tr.Remark ";
    $search = "ts.IsDeleted=0";
    if(@$request['User']!=''){
        $search = $search." AND ts.UserName = '".@$request['User']."'";
    }
    if(@$request['Email']!=''){
        $search = $search." AND ts.EmailAddress = '".@$request['Email']."'";
    }
    if(@$request['Name']!=''){
        $search = $search." AND ts.Name = '".@$request['Name']."'";
    }
    if(@$request['Surname']!=''){
        $search = $search." AND ts.Surname = '".@$request['Surname']."'";
    }
    if(@$request['Role']!=''){
        $search = $search." AND tsru.RoleId = '".@$request['Role']."'";
    }
    $order = '';

    if($request['OrderDirection']!=''){
      $order = " Order By ".$request['OrderBy'] ." ".$request['OrderDirection'];
    }

    $sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search);
    if($request['PageSize']!=''){
      $page_number = $request['PageNumber'] - 1;
      $max_rows = $request['PageSize'];
      $list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
      $start_count = $page_number * $max_rows;
      $view_count = $this->view($list_count);
      $result_count = $this->q_re($view_count);
      $respons_count = $this->q_nr($result_count);
      $respons_count_num = mysqli_num_rows($result_count);
      $respons_pages = ceil($respons_count/$max_rows)-1;
      $data_c['page_number'] = $page_number;
      $data_c['respons_pages'] = $respons_pages;

      $sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
    }
    $view = $this->view($sql);
    $result = $this->q_re($view);
    $i=0;$data=array();
    if($respons_count>0){
      while ($row = $this->q_ro($result)) {

        $data['Data'][$i]['UserName'] =$row['UserName'];
        $data['Data'][$i]['EmailAddress'] =$row['EmailAddress'];
        $data['Data'][$i]['Title'] =$row['Title'];
        $data['Data'][$i]['Name'] =$row['Name'];
        $data['Data'][$i]['MiddleName'] =$row['MiddleName'];
        $data['Data'][$i]['Surname'] =$row['Surname'];
        $data['Data'][$i]['RoleName'] =$row['RoleName'];
        $data['Data'][$i]['Id'] =$row['UserId'];
        $data['Data'][$i]['RoleId'] =$row['RoleId'];
        $data['Data'][$i]['IsDeleted'] =$row['IsDeleted'];
        $data['Data'][$i]['StatusActive'] =$row['StatusActive'];

        $data['Data'][$i]['CreationTime'] =$row['CreationTime'];
        $data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];

        $sql2 = array('table'=>"t_system_users",'where'=>"Id = '".$row['CreatorUserId']."'");
        $view2 = $this->view($sql2);
        $row2 = $this->q($view2);
        $data['Data'][$i]['CreateBy'] =$row2['Name']." ".$row2['Surname'];

        $sql3 = array('table'=>"t_system_users",'where'=>"Id = '".$row['LastUpdateBy']."'");
        $view3 = $this->view($sql3);
        $row3 = $this->q($view3);
        $data['Data'][$i]['LastUpdateBy'] =$row3['Name']." ".$row3['Surname'];

        $i++;
      }

    }else{
      $data['Data'] = '';
    }
    $data['PageNumber'] = $request['PageNumber'];
    $data['OrderBy'] = $request['OrderBy'];
    $data['OrderDirection'] = $request['OrderDirection'];
    $data['PageSize'] = $request['PageSize'];
    $data['numrows'] = @$respons_count_num;
    $data['TotalRecords'] = @$respons_count_num;
    $data['sql'] = @$view;


    return $data;
  }

  public function LoadUsers2($request)
  {
    $name_table = "
    t_system_role_user trs
    INNER JOIN t_system_users ts ON ts.Id = trs.UserId
    INNER JOIN t_system_role tr ON tr.Id = trs.RoleId
    ";

    $count = "";
    $count = "trs.*, ts.UserName, ts.EmailAddress, ts.Title, ts.Name, ts.MiddleName, ts.Surname, tr.RoleName, tr.Remark ";
    $search = "trs.IsDeleted = 0";
    if(@$request['User']!=''){
        $search = $search." AND ts.UserName = '".@$request['User']."'";
    }
    if(@$request['Email']!=''){
        $search = $search." AND ts.EmailAddress = '".@$request['Email']."'";
    }
    if(@$request['Name']!=''){
        $search = $search." AND ts.Name = '".@$request['Name']."'";
    }
    if(@$request['Surname']!=''){
        $search = $search." AND ts.Surname = '".@$request['Surname']."'";
    }
    if(@$request['Role']!=''){
        $search = $search." AND tr.Id = '".@$request['Role']."'";
    }
    $order = '';

    if($request['OrderDirection']!=''){
      $order = " Order By ts.".$request['OrderBy'] ." ".$request['OrderDirection'];
    }

    $sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search);
    if($request['PageSize']!=''){
      $page_number = $request['PageNumber'] - 1;
      $max_rows = $request['PageSize'];
      $list_count = array('table'=>$name_table,'count'=>$count,'where'=>$search); //,'order' => 'ORDER BY `QuotationId` DESC '
      $start_count = $page_number * $max_rows;
      $view_count = $this->view($list_count);
      $result_count = $this->q_re($view_count);
      $respons_count = $this->q_nr($result_count);
      $respons_count_num = mysqli_num_rows($result_count);
      $respons_pages = ceil($respons_count/$max_rows)-1;
      $data_c['page_number'] = $page_number;
      $data_c['respons_pages'] = $respons_pages;

      $sql = array('table'=>$name_table, 'count'=>$count,'where'=>$search, 'limit'=> "$start_count,$max_rows",'order'=> $order);
    }
    $view = $this->view($sql);
    $result = $this->q_re($view);
    $i=0;$data=array();
    while ($row = $this->q_ro($result)) {

     $data['Data'][$i]['Id'] =$row['Id'];
     $data['Data'][$i]['RoleId'] =$row['RoleId'];
     $data['Data'][$i]['UserId'] =$row['UserId'];
     $data['Data'][$i]['StatusActive'] =$row['StatusActive'];
     $data['Data'][$i]['CreateBy'] =$row['CreateBy'];
     $data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
     $data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
     $data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];

     $sql2 = array('table'=>"t_system_users",'where'=>"Id = '".$row['UserId']."'");
     $view2 = $this->view($sql2);
     $row2 = $this->q($view2);
     $data['Data'][$i]['UserName'] =$row2['UserName'];
     $data['Data'][$i]['EmailAddress'] =$row2['EmailAddress'];
     $data['Data'][$i]['Title'] =$row2['Title'];
     $data['Data'][$i]['Name'] =$row2['Name'];
     $data['Data'][$i]['MiddleName'] =$row2['MiddleName'];
     $data['Data'][$i]['Surname'] =$row2['Surname'];
     $data['Data'][$i]['Surname'] =$row2['Surname'];

     $sql3 = array('table'=>"t_system_role",'where'=>"Id = '".$row['RoleId']."'");
     $view3 = $this->view($sql3);
     $row3 = $this->q($view3);
     $data['Data'][$i]['RoleName'] =$row3['RoleName'];
     $data['Data'][$i]['Remark'] =$row3['Remark'];


     $i++;
    }
    $data['PageNumber'] = $request['PageNumber'];
    $data['OrderBy'] = $request['OrderBy'];
    $data['OrderDirection'] = $request['OrderDirection'];
    $data['PageSize'] = $request['PageSize'];
    $data['numrows'] = @$respons_count_num;
    $data['TotalRecords'] = @$respons_count_num;
    $data['sql'] = @$view;


    return $data;
  }

  public function LoadRoleSearch()
  {
    $count ="";
    $name_table = "t_system_role";

    $search = "IsDeleted=0";

    $order = "";
    $sql = array('table'=>$name_table,'where'=>$search);

    $view = $this->view($sql);
    $result = $this->q_re($view);
    $respons = $this->q_nr($result);
    $respons_count_num = mysqli_num_rows($result);

    $i=0;$data=array();
    if(@$respons_count_num > 0){
      while ($row = $this->q_ro($result)) {
        $data['Data'][$i]['Id'] =$row['Id'];
        $data['Data'][$i]['RoleName'] =$row['RoleName'];
        $data['Data'][$i]['Remark'] =$row['Remark'];
        $data['Data'][$i]['IsDeleted'] =$row['IsDeleted'];
        $data['Data'][$i]['StatusActive'] =$row['StatusActive'];
        $data['Data'][$i]['CreateBy'] =$row['CreateBy'];
        $data['Data'][$i]['LastUpdateBy'] =$row['LastUpdateBy'];
        $data['Data'][$i]['CreateDateTime'] =$row['CreateDateTime'];
        $data['Data'][$i]['LastUpdateDateTime'] =$row['LastUpdateDateTime'];
       $i++;
      }
    }else{
      $data['Data'] = '';
    }
    $data['PageNumber'] = @$request['PageNumber'];
    $data['OrderBy'] = @$request['OrderBy'];
    $data['OrderDirection'] = @$request['OrderDirection'];
    $data['PageSize'] = @$request['PageSize'];
    $data['numrows'] = @$respons_count_num;
    $data['TotalRecords'] = @$respons_count_num;
    $data['request'] = @$respons;
    $data['sql'] = @$view;
    return $data;
  }
}



 ?>