<?php

class DemandsMod extends DbMod{
	var $ArrMsSp = '';
  var $VarTableDemandsTypes = array('integer', 'integer', 'date', 'integer', 'text', 'integer', 'integer', 'integer', 'date', 'text', 'date');
	
  function DemandsMod($ArrMsSp){
		parent::__construct($ArrMsSp);
		return true;
	}

	function GetFromDb($VarSqlName = 'SqlAll', $VarSqlGet = 'GetAll', $VarWhere = '', $VarOrder = '', $VarLimit = ''){
    $ArrSqlArray['SqlAll']['select'] = 'a.*, b.sprstatus_name, b.sprstatus_color, c.svt_buxname, c.svt_name, d.svttype_name, c.svt_invnum, c.svt_serialnum, c.structureorganization_id, e.users_fio, e.users_tel, e.users_telint, f.structureorganization_title, g.users_fio AS master_fio, h.demandsclassif_name';
    $ArrSqlArray['SqlAll']['from'] = $this->VarTableSprStatus .' AS b, '. $this->VarTableSvt .' AS c, '. $this->VarTableSvtType .' AS d, '. $this->VarTableStructureOrganization .' AS f, '. $this->VarTableDemands .' AS a';
    $ArrSqlArray['SqlAll']['leftjoin'] = $this->VarTableUsers .' AS e ON a.users_id = e.users_id LEFT JOIN '. $this->VarTableUsers .' AS g ON a.demands_masterid = g.users_id LEFT JOIN ' . $this->VarTableDemandsClassif .' AS h ON a.demandsclassif_id = h.demandsclassif_id';
    $ArrSqlArray['SqlAll']['where'] = 'a.sprstatus_id = b.sprstatus_id AND a.svt_id = c.svt_id AND c.svttype_id = d.svttype_id AND c.structureorganization_id = f.structureorganization_id';
    if('' != $VarWhere){
      $ArrSqlArray['SqlAll']['where'] .= ' AND '. $VarWhere;
    }
    if('' != $VarOrder){
      $ArrSqlArray['SqlAll']['order'] = $VarOrder;
    }
    if('' != $VarLimit){
      $ArrSqlArray['SqlAll']['limit'] = $VarLimit;
    }
    /*** end default values ***/
    $ArrSqlArray['SqlCount']['select'] = 'count(*) AS vartotal';
    $ArrSqlArray['SqlCount']['from'] = $this->VarTableSprStatus .' AS b, '. $this->VarTableSvt .' AS c, '. $this->VarTableSvtType .' AS d, '. $this->VarTableStructureOrganization .' AS f, '. $this->VarTableDemands .' AS a';
    $ArrSqlArray['SqlCount']['leftjoin'] = $this->VarTableUsers .' AS e ON a.users_id = e.users_id LEFT JOIN '. $this->VarTableUsers .' AS g ON a.demands_masterid = g.users_id LEFT JOIN ' . $this->VarTableDemandsClassif .' AS h ON a.demandsclassif_id = h.demandsclassif_id';
    $ArrSqlArray['SqlCount']['where'] = 'a.sprstatus_id = b.sprstatus_id AND a.svt_id = c.svt_id AND c.svttype_id = d.svttype_id AND c.structureorganization_id = f.structureorganization_id';
    if('' != $VarWhere){
      $ArrSqlArray['SqlCount']['where'] .= ' AND '. $VarWhere;
    }
    ///
    $ArrSqlArray['SqlByEngineer']['select'] = 'e.*, f.structureorganization_title, count(a.demands_id) AS demands_count';
    $ArrSqlArray['SqlByEngineer']['from'] = $this->VarTableUsersServices .' AS b, '. $this->VarTableUsers .' AS e';
    $ArrSqlArray['SqlByEngineer']['leftjoin'] = $this->VarTableDemands .' AS a ON a.demands_masterid = e.users_id AND '. $VarWhere .' LEFT JOIN '. $this->VarTableStructureOrganization .' AS f ON e.structureorganization_id = f.structureorganization_id';
    $ArrSqlArray['SqlByEngineer']['where'] = 'b.users_id = e.users_id AND (b.webservices_id = 1 || b.webservices_id = 2)';
//    if('' != $VarWhere){
//      $ArrSqlArray['SqlByEngineer']['where'] .= ' AND '. $VarWhere;
//    }
    $ArrSqlArray['SqlByEngineer']['group'] = 'e.users_id';
    if('' != $VarOrder){
      $ArrSqlArray['SqlByEngineer']['order'] = $VarOrder;
    }
    if('' != $VarLimit){
      $ArrSqlArray['SqlByEngineer']['limit'] = $VarLimit;
    }
    ///
    $ArrSqlArray['SqlByEngineerCount']['select'] = 'count(DISTINCT e.users_id) AS vartotal';
    $ArrSqlArray['SqlByEngineerCount']['from'] = $this->VarTableUsersServices .' AS b, '. $this->VarTableUsers .' AS e';
    $ArrSqlArray['SqlByEngineerCount']['leftjoin'] = $this->VarTableDemands .' AS a ON a.demands_masterid = e.users_id AND '. $VarWhere;
    $ArrSqlArray['SqlByEngineerCount']['where'] = 'b.users_id = e.users_id AND (b.webservices_id = 1 || b.webservices_id = 2)';
    ///
    $ArrSqlArray['SqlByYearCount']['select'] = 'year(demands_date) AS year_demands_date, count(*) AS cnt';
    $ArrSqlArray['SqlByYearCount']['from'] = $this->VarTableDemands .' AS a';
    $ArrSqlArray['SqlByYearCount']['where'] = 'a.demands_date IS NOT NULL';
    if('' != $VarWhere){
      $ArrSqlArray['SqlByYearCount']['where'] .= ' AND '. $VarWhere;
    }
    $ArrSqlArray['SqlByYearCount']['group'] = 'year_demands_date';
    if('' != $VarOrder){
      $ArrSqlArray['SqlByYearCount']['order'] = $VarOrder;
    }
    ///
    $ArrSqlArray['SqlForFirstPageCount']['select'] = 'count(a.demands_id) AS cnt, b.sprstatus_id, b.sprstatus_name, b.sprstatus_color';
    $ArrSqlArray['SqlForFirstPageCount']['from'] = $this->VarTableSprStatus .' AS b, '. $this->VarTableDemands .' AS a';
    $ArrSqlArray['SqlForFirstPageCount']['where'] = 'a.sprstatus_id = b.sprstatus_id';
    if('' != $VarWhere){
      $ArrSqlArray['SqlForFirstPageCount']['where'] .= ' AND '. $VarWhere;
    }
    $ArrSqlArray['SqlForFirstPageCount']['group'] = 'a.sprstatus_id';
    if('' != $VarOrder){
      $ArrSqlArray['SqlForFirstPageCount']['order'] = $VarOrder;
    }
    if('' != $VarLimit){
      $ArrSqlArray['SqlForFirstPageCount']['limit'] = $VarLimit;
    }
    //
    $ArrSqlArray['SqlDemandsStatusMasterForFirstPageCount']['select'] = 'u.users_id, u.users_fio, s.sprstatus_id, s.sprstatus_name, s.sprstatus_color, count(*) as cnt';
    $ArrSqlArray['SqlDemandsStatusMasterForFirstPageCount']['from'] = $this->VarTableUsers .' AS u, '. $this->VarTableSprStatus .' AS s, '. $this->VarTableDemands .' AS d';
    $ArrSqlArray['SqlDemandsStatusMasterForFirstPageCount']['where'] = 'd.demands_masterid = u.users_id AND d.sprstatus_id = s.sprstatus_id';
    if('' != $VarWhere){
      $ArrSqlArray['SqlDemandsStatusMasterForFirstPageCount']['where'] .= ' AND '. $VarWhere;
    }
    $ArrSqlArray['SqlDemandsStatusMasterForFirstPageCount']['group'] = 'd.demands_masterid, d.sprstatus_id';
    if('' != $VarOrder){
      $ArrSqlArray['SqlDemandsStatusMasterForFirstPageCount']['order'] = $VarOrder;
    }
    if('' != $VarLimit){
      $ArrSqlArray['SqlDemandsStatusMasterForFirstPageCount']['limit'] = $VarLimit;
    }
    //
    $ArrSqlArray['SqlQuery']['query'] = $VarWhere;
    //
    $ArrResult = $this->DbGet($ArrSqlArray[$VarSqlName], $VarSqlGet);
  	return $ArrResult;
	}

  function Add($values){
    if(true === is_array($values) && 1 <= count($values)){
      $VarSql = 'INSERT INTO '. $this->VarTableDemands .'
                (demands_id, svt_id, demands_date, users_id, demands_ip, demands_masterid, sprstatus_id, demandsclassif_id, demands_statusdate, demands_intdocnum, demands_intdocdate)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
      $values = $this->PrepareData($values);
      $ArrData = array(
        '',
        $values['svt_id'],
        $values['demands_date'],
        $values['users_id'],
        $values['demands_ip'],
        $values['demands_masterid'],
        $values['sprstatus_id'],
        $values['demandsclassif_id'],
        $values['demands_statusdate'],
        $values['demands_intdocnum'],
        $values['demands_intdocdate']
      );

      $sth = $this->ArrMsSp->SP_DbConnect->prepare($VarSql, $this->VarTableDemandsTypes, MDB2_PREPARE_MANIP);
      if (PEAR::isError($sth)) {
        return array('error' => $sth->getMessage());
      }
      $VarQueryResult = $sth->execute($ArrData);
      if (PEAR::isError($VarQueryResult)) {
        return array('error' => $VarQueryResult->getMessage());
      }
      $VarId = $this->ArrMsSp->SP_DbConnect->lastInsertID($this->VarTableDemands, 'demands_id');
      if (PEAR::isError($VarId)) {
        return array('error' => $VarId->getMessage());
      }
      return $VarId;
    }
    else{
      return array('error' => $this->ArrMsSp->Sp_LangMessage['error']['ErrSettingWrong']);
    }
  }

  function Edit($values){
    if(true === is_array($values) && 1 <= count($values)){
      $VarSql = "UPDATE ". $this->VarTableDemands ." SET
                  svt_id = ?,
                  users_id = ?,
                  demands_masterid = ?,
                  sprstatus_id = ?,
                  demandsclassif_id = ?,
                  demands_statusdate = ?,
                  demands_intdocnum = ?,
                  demands_intdocdate = ?
                WHERE demands_id ='".  $values['demands_id'] ."'";
      $values = $this->PrepareData($values);
      $ArrData = array(
                  $values['svt_id'],
                  $values['users_id'],
                  $values['demands_masterid'],
                  $values['sprstatus_id'],
                  $values['demandsclassif_id'],
                  $values['demands_statusdate'],
                  $values['demands_intdocnum'],
                  $values['demands_intdocdate']
                );
      $sth = $this->ArrMsSp->SP_DbConnect->prepare($VarSql);
      if (PEAR::isError($sth)) {
        return array('error' => $sth->getMessage());
      }
      $VarQueryResult = $sth->execute($ArrData);
      if (PEAR::isError($VarQueryResult)) {
        return array('error' => $VarQueryResult->getMessage());
      }
      return $values['demands_id'];
    }
    else{
      return array('error' => $this->ArrMsSp->Sp_LangMessage['error']['ErrSettingWrong']);
    }
  }

  function Del($VarId = '', $VarFiledName = 'demands_id'){
    if('' != $VarId && true === is_numeric($VarId)){
      $sql = "DELETE FROM ". $this->VarTableDemands ." WHERE ". $VarFiledName ." = '". $VarId ."'";
      //echo "sql: ". $sql ."<br />";
      $VarQueryResult = $this->ArrMsSp->SP_DbConnect->exec($sql);
      if (PEAR::isError($VarQueryResult)) {
        return array('error' => $VarQueryResult->getMessage());
      }
      return true;
    }
    else {
      return array('error' => $this->ArrMsSp->Sp_LangMessage['error']['ErrSettingWrong']);
    }
	}
  
  function PrepareData($values){
    if(true === is_array($values) && 0 < count($values)){
      $values['svt_id'] = NULL;
      if(true === isset($values['FrmAgreeSvtCheckboxes']) && '' != $values['FrmAgreeSvtCheckboxes']){
        $values['svt_id'] = current($values['FrmAgreeSvtCheckboxes']);
      }
      $values['users_id'] = NULL;
      if(true === isset($values['FrmAgreeUsersCheckboxes']) && '' != $values['FrmAgreeUsersCheckboxes']){
        $values['users_id'] = current($values['FrmAgreeUsersCheckboxes']);
      }
      $values['demands_masterid'] = NULL;
      if(true === isset($values['FrmAgreeMasterCheckboxes']) && '' != $values['FrmAgreeMasterCheckboxes']){
        $values['demands_masterid'] = current($values['FrmAgreeMasterCheckboxes']);
      }
      $values['sprstatus_id'] = NULL;
      if(true === isset($values['FrmAgreeSprStatusCheckboxes']) && '' != $values['FrmAgreeSprStatusCheckboxes']){
        $values['sprstatus_id'] = current($values['FrmAgreeSprStatusCheckboxes']);
      }
      $values['demandsclassif_id'] = NULL;
      if(true === isset($values['FrmAgreeDemandsClassifCheckboxes']) && '' != $values['FrmAgreeDemandsClassifCheckboxes']){
        $values['demandsclassif_id'] = current($values['FrmAgreeDemandsClassifCheckboxes']);
      }
      $values['demands_ip'] = $_SERVER['REMOTE_ADDR'];
      $values['demands_date'] = date('YmdHis');
      $values['demands_statusdate'] = date('YmdHis');
      $values['demands_intdocdate'] = SiteLibs::ConvertSqlDateInsert($values['demands_intdocdate']);
    }
    return $values;
  }
}
?>