PHP Ticket::find_by_sql方法代码示例

 public function getWorkSheetList($id = "", $pg)
 {
     $purl = array();
     if (isset($_GET['url'])) {
         $purl = $_GET['url'];
         $purl = rtrim($purl);
         $purl = explode('/', $_GET['url']);
     } else {
         $purl = null;
     }
     if (!isset($purl['2'])) {
         $pn = 1;
     } else {
         $pn = $purl['2'];
     }
     global $database;
     $resultWorksheet = $database->db_query("SELECT * FROM work_sheet_form ");
     $pagin = new Pagination();
     //create the pagination object;
     $pagin->nr = $database->dbNumRows($resultWorksheet);
     $pagin->itemsPerPage = 20;
     $myworksheets = Ticket::find_by_sql("SELECT * FROM work_sheet_form " . $pagin->pgLimit($pn));
     $index_array = array("worksheet" => $myworksheets, "mypagin" => $pagin->render($pg));
     return $index_array;
     return $index_array;
 }

 function __construct()
 {
     parent::__construct();
     $this->view_data['core_settings'] = Setting::first();
     if ($this->input->cookie('language') != "") {
         $language = $this->input->cookie('language');
     } else {
         if (isset($this->view_data['language'])) {
             $language = $this->view_data['language'];
         } else {
             if (!empty($this->view_data['core_settings']->language)) {
                 $language = $this->view_data['core_settings']->language;
             } else {
                 $language = "english";
             }
         }
     }
     $this->lang->load('application', $language);
     $this->lang->load('messages', $language);
     $this->lang->load('event', $language);
     $this->user = $this->session->userdata('user_id') ? User::find_by_id($this->session->userdata('user_id')) : FALSE;
     $this->client = $this->session->userdata('client_id') ? Client::find_by_id($this->session->userdata('client_id')) : FALSE;
     if ($this->client) {
         $this->theme_view = 'application_client';
     }
     $this->view_data['datetime'] = date('Y-m-d H:i', time());
     $this->view_data['sticky'] = Project::all(array('conditions' => 'sticky = 1'));
     $this->view_data['quotations_new'] = Quote::find_by_sql("select count(id) as amount from quotations where status='New'");
     if ($this->user || $this->client) {
         $access = $this->user ? $this->user->access : $this->client->access;
         $access = explode(",", $access);
         if ($this->user) {
             $this->view_data['menu'] = Module::find('all', array('order' => 'sort asc', 'conditions' => array('id in (?) AND type = ?', $access, 'main')));
             $this->view_data['widgets'] = Module::find('all', array('conditions' => array('id in (?) AND type = ?', $access, 'widget')));
         } else {
             $this->view_data['menu'] = Module::find('all', array('order' => 'sort asc', 'conditions' => array('id in (?) AND type = ?', $access, 'client')));
         }
         if ($this->user) {
             $update = User::find($this->user->id);
         } else {
             $update = Client::find($this->client->id);
         }
         $update->last_active = time();
         $update->save();
         if ($this->user) {
             $this->view_data['user_online'] = User::all(array('conditions' => array('last_active+(30 * 60) > ? AND status = ?', time(), "active")));
             $this->view_data['client_online'] = Client::all(array('conditions' => array('last_active+(30 * 60) > ? AND inactive = ?', time(), "0")));
         }
         $email = $this->user ? 'u' . $this->user->id : 'c' . $this->client->id;
         $this->view_data['messages_new'] = Privatemessage::find_by_sql("select count(id) as amount from privatemessages where `status`='New' AND recipient = '" . $email . "'");
         $this->view_data['tickets_new'] = Ticket::find_by_sql("select count(id) as amount from tickets where `status`='New'");
     }
     /*$this->load->database();
     		$sql = "select * FROM templates WHERE type='notes'";
     		$query = $this->db->query($sql); */
     $this->view_data["note_templates"] = "";
     //$query->result();
 }

 /**
  * Dashboard statistics section
  */
 public function getDashboardStat()
 {
     global $database;
     $darray = array();
     $open_ticket_count = $database->dbNumRows($database->db_query("SELECT * FROM support_ticket WHERE status ='Open' "));
     $open_schedule_count = $database->dbNumRows($database->db_query("SELECT * FROM schedule WHERE status ='Open' "));
     $open_worksheet_count = $database->dbNumRows($database->db_query("SELECT * FROM work_sheet_form WHERE status ='Open' "));
     $awaiting_ticket_count = $database->dbNumRows($database->db_query("SELECT * FROM support_ticket WHERE status ='Customer Reply' "));
     $closed_worksheet_count = $database->dbNumRows($database->db_query("SELECT * FROM work_sheet_form WHERE status ='Closed' "));
     $client_count = $database->dbNumRows($database->db_query("SELECT * FROM tbl_client"));
     $client_products = $database->dbNumRows($database->db_query("SELECT * FROM client_product"));
     //$schedule
     $openPendings = Ticket::find_by_sql("SELECT * FROM support_ticket WHERE (status ='Admin Reply' OR status='Customer Reply') OR  status ='Open' ");
     // print_r($openPendings);
     $clients = Client::find_all();
     $darray = array("cproducts" => $client_products, "clients" => $clients, "oworksheet" => $open_worksheet_count, "oschedule" => $open_schedule_count, "otcount" => $open_ticket_count, "atcount" => $awaiting_ticket_count, "openPend" => $openPendings);
     return $darray;
 }

 public function getData()
 {
     global $database;
     $depts = Department::find_all();
     $role = Roles::find_all();
     $country = Country::find_all();
     $vendors = Vendor::find_all();
     $countAcc = count(Cproduct::find_by_client($_SESSION["client_ident"]));
     $schedule = Cproduct::getNextSchedule($_SESSION["client_ident"]);
     $OnSchedule = Schedule::find_by_sql("SELECT * FROM schedule WHERE client_id='" . $_SESSION['client_ident'] . "' AND maint_type !='Activation'  AND (status='Open' OR status='In Progress') ORDER BY id DESC LIMIT 10");
     $OnScheduleAct = Schedule::find_by_sql("SELECT * FROM schedule WHERE client_id='" . $_SESSION['client_ident'] . "' AND maint_type='Activation' AND  (status='Open' OR status='In Progress') ORDER BY id DESC LIMIT 10");
     $countTicketOpen = count(Ticket::find_by_sql("SELECT * FROM support_ticket WHERE client_id ='" . $_SESSION['client_ident'] . "' AND status ='Open'"));
     $countTicketClose = count(Ticket::find_by_sql("SELECT * FROM support_ticket WHERE client_id ='" . $_SESSION['client_ident'] . "' AND status ='Closed'"));
     $countTicketPending = count(Ticket::find_by_sql("SELECT * FROM support_ticket WHERE client_id ='" . $_SESSION['client_ident'] . "' AND (status ='Admin Reply' OR status='Customer Reply')"));
     $countTic = count(Ticket::find_by_client($_SESSION['client_ident']));
     $countuser = count(Clientuser::find_by_client($_SESSION['client_ident']));
     $zone = Zone::find_by_sql("SELECT * FROM zone");
     $startups = array("cschedule" => $OnSchedule, "departs" => $depts, "country" => $country, "zone" => $zone, "vendors" => $vendors, "role" => $role, "countProd" => $countAcc, "countTick" => $countTic, "Schel" => $schedule, "SchelAct" => $OnScheduleAct, "CountPending" => $countTicketPending, "CountOpent" => $countTicketOpen, "CountClosed" => $countTicketClose, "CountUsers" => $countuser);
     return $startups;
 }

 public function getListClient($id = "", $pg)
 {
     $purl = array();
     if (isset($_GET['url'])) {
         $purl = $_GET['url'];
         $purl = rtrim($purl);
         $purl = explode('/', $_GET['url']);
     } else {
         $purl = null;
     }
     if (!isset($purl['2'])) {
         $pn = 1;
     } else {
         $pn = $purl['2'];
     }
     $statusfield = "";
     /**
      * of all the filter fields if only one field is set
      */
     $filterResult = "";
     if (isset($_REQUEST['status']) && !empty($_REQUEST['status'])) {
         if ($_REQUEST['status'] != "Pending") {
             $statusfield .= " AND status = '" . $_REQUEST['status'] . "' ";
         } else {
             $statusfield .= " AND status = 'Admin Reply' OR status = 'Customer Reply'";
         }
     }
     $filterResult .= " WHERE client_id ='" . $_SESSION['client_ident'] . "' " . $statusfield;
     global $database;
     $resultEmployee = $database->db_query("SELECT * FROM support_ticket WHERE client_id='" . $_SESSION["client_ident"] . "'");
     $pagin = new Pagination();
     //create the pagination object;
     $pagin->nr = $database->dbNumRows($resultEmployee);
     $pagin->itemsPerPage = 20;
     $myitems = Ticket::find_by_sql("SELECT * FROM support_ticket " . $filterResult . "  ORDER BY datemodified DESC " . $pagin->pgLimit($pn) . "");
     $index_array = array("supportticket" => $myitems, "mypagin" => $pagin->render($pg));
     return $index_array;
     return $index_array;
 }

 function __construct()
 {
     parent::__construct();
     $access = FALSE;
     $link = '/' . $this->uri->uri_string();
     if ($this->client) {
         if ($this->input->cookie('fc2_link') != "") {
             $link = str_replace("/tickets/", "/ctickets/", $link);
             redirect($link);
         }
         redirect('ctickets');
     } elseif ($this->user) {
         foreach ($this->view_data['menu'] as $key => $value) {
             if ($value->link == "tickets") {
                 $access = TRUE;
             }
         }
         if (!$access) {
             redirect('login');
         }
     } else {
         $cookie = array('name' => 'fc2_link', 'value' => $link, 'expire' => '500');
         $this->input->set_cookie($cookie);
         redirect('login');
     }
     $this->view_data['submenu'] = array($this->lang->line('application_open') => 'tickets', $this->lang->line('application_my_tickets') => 'tickets/filter/assigned', $this->lang->line('application_closed') => 'tickets/filter/closed');
     $this->load->database();
     $this->view_data['tickets_assigned_to_me'] = Ticket::count(array('conditions' => 'user_id = ' . $this->user->id . ' and status != "closed"'));
     $this->view_data['tickets_in_my_queue'] = Ticket::count(array('conditions' => 'queue_id <= ' . $this->user->queue . ' and status != "closed"'));
     $now = time();
     $beginning_of_week = strtotime('last Monday', $now);
     // BEGINNING of the week
     $end_of_week = strtotime('next Sunday', $now) + 86400;
     // END of the last day of the week
     $this->view_data['tickets_opened_this_week'] = Ticket::find_by_sql('select count(id) AS "amount", DATE_FORMAT(FROM_UNIXTIME(`created`), "%w") AS "date_day", DATE_FORMAT(FROM_UNIXTIME(`created`), "%Y-%m-%d") AS "date_formatted" from tickets where created >= "' . $beginning_of_week . '" AND created <= "' . $end_of_week . '" ');
     //$this->view_data['tickets_closed_this_week'] = Ticket::find_by_sql('select count(id) AS "amount", DATE_FORMAT(FROM_UNIXTIME(`created`), "%w") AS "date_day", DATE_FORMAT(FROM_UNIXTIME(`created`), "%Y-%m-%d") AS "date_formatted" from tickets where created >= "'.$beginning_of_week.'" AND created <= "'.$end_of_week.'" ');
 }

 public function getWorkSheetList($id = "", $pg)
 {
     $purl = array();
     if (isset($_GET['url'])) {
         $purl = $_GET['url'];
         $purl = rtrim($purl);
         $purl = explode('/', $_GET['url']);
     } else {
         $purl = null;
     }
     if (!isset($purl['2'])) {
         $pn = 1;
     } else {
         $pn = $purl['2'];
     }
     $statusfield = "";
     $clientidfield = "";
     $issuefield = "";
     // $productidfield ="";
     $datefield = "";
     /**
      * of all the filter fields if only one field is set
      */
     $filterResult = "";
     if (isset($_REQUEST['status']) && !empty($_REQUEST['status'])) {
         $statusfield .= " AND status = '" . $_REQUEST['status'] . "' ";
     }
     if (isset($_REQUEST['clientid']) && !empty($_REQUEST['clientid'])) {
         $clientidfield .= " AND client_id=" . $_REQUEST['clientid'];
     }
     if (isset($_REQUEST['issue']) && !empty($_REQUEST['issue'])) {
         $issuefield .= " AND problem LIKE '%" . $_REQUEST['issue'] . "%' ";
     }
     //if(isset($_REQUEST['prodid']) && !empty($_REQUEST['prodid'])){
     //  $productidfield .= " AND prod_id = '".$_REQUEST['prodid']."' ";
     //}
     /*if(!empty($_REQUEST['fdate']) && !empty($_REQUEST['tdate'])){
          $datefield .= " AND datecreated BETWEEN  '".$_REQUEST['fdate']."' AND '".$_REQUEST['tdate']."' ";
       }
        if(empty($_REQUEST['fdate']) && !empty($_REQUEST['tdate'])){
          $datefield .= " AND datecreated < '".$_REQUEST['tdate']."' ";
       }
        if(!empty($_REQUEST['fdate']) && empty($_REQUEST['tdate'])){
          $datefield .= " AND datecreated >  '".$_REQUEST['fdate']."'  ";
       }*/
     $filterResult .= " WHERE id !=''" . $clientidfield . $issuefield . $statusfield;
     global $database;
     $resultWorksheet = $database->db_query("SELECT * FROM work_sheet_form ");
     $pagin = new Pagination();
     //create the pagination object;
     $pagin->nr = $database->dbNumRows($resultWorksheet);
     $pagin->itemsPerPage = 20;
     $myworksheets = Ticket::find_by_sql("SELECT * FROM work_sheet_form " . $filterResult . " ORDER BY id DESC " . $pagin->pgLimit($pn));
     $index_array = array("worksheet" => $myworksheets, "mypagin" => $pagin->render($pg, "worksheetlist"));
     return $index_array;
 }