se/SE/se-lib/ProjectKosztorysCennik.php

<?php

Lib::loadClass('ProjectKosztorysSchema');

class ProjectKosztorysCennik {

  /**
	 * @returns [ $id_zasob => [ 'price' => $price, 'ID', 'id_zasob', 'id_company', 'id_project', 'unit', 'quantity' ] ]
	 */
  public static function getCennik($idProject, $idCompany = 0) {
    $schema = ProjectKosztorysSchema::getSchema();
    $typeIdList = array_keys($schema['config']['type']);
    $sqlTypeIdList = implode(',', $typeIdList);
    $pdo = DB::getPDO();
    $sth = $pdo->prepare("
      select o.ID
        , o.CRM_LISTA_ZASOBOW_ID as id_zasob
        , o.COMPANIES_ID as id_company
        , o.ID_PROJECT as id_project
        , o.OFFER_PRICE_PER_RESOURCE_UNIT as price
        , o.RESOURCE_UNIT_TYPE as unit
    --		, o.OFFER_UNIT_TYPE as unit
        , o.REQUIRED_RESOURCE_UNITS as quantity
      from CRM_LISTA_ZASOBOW_OFFERS o
      where o.CRM_LISTA_ZASOBOW_ID in({$sqlTypeIdList})
        and (o.A_STATUS is null or o.A_STATUS not in ('DELETED'))
        and o.ID_PROJECT = :id_project
        and o.COMPANIES_ID = :id_company
        and o.RESOURCE_UNIT_TYPE != 'ROBOCIZNA'
        and o.OFFER_PRICE_PER_RESOURCE_UNIT >= 0
    ");
    $sth->bindValue(':id_project', $idProject, PDO::PARAM_INT);
    $sth->bindValue(':id_company', $idCompany, PDO::PARAM_INT);
    $sth->execute();
    $cennikRaw = $sth->fetchAll();
    $cennik = array();
    foreach ($cennikRaw as $itemRaw) {
      $item = $itemRaw;
      $item['price'] = round($item['price'], 2);
      $cennik[$itemRaw['id_zasob']] = $item;
    }
    return $cennik;
  }

  /**
	 * @returns [ $id_zasob => [ 'price' => $price, 'ID', 'id_zasob', 'id_company', 'id_project', 'unit', 'quantity' ] ]
	 */
  public static function getWorkCennik($idProject, $idCompany = 0) {
    $schema = ProjectKosztorysSchema::getSchema();
    $typeIdList = array_keys($schema['config']['type']);
    $sqlTypeIdList = implode(',', $typeIdList);
    $pdo = DB::getPDO();
    $sth = $pdo->prepare("
      select o.ID
        , o.CRM_LISTA_ZASOBOW_ID as id_zasob
        , o.COMPANIES_ID as id_company
        , o.ID_PROJECT as id_project
        , o.OFFER_PRICE_PER_RESOURCE_UNIT as price
        , o.RESOURCE_UNIT_TYPE as unit
    --		, o.OFFER_UNIT_TYPE as unit
        , o.REQUIRED_RESOURCE_UNITS as quantity
      from CRM_LISTA_ZASOBOW_OFFERS o
      where o.CRM_LISTA_ZASOBOW_ID in({$sqlTypeIdList})
        and (o.A_STATUS is null or o.A_STATUS not in ('DELETED'))
        and o.ID_PROJECT = :id_project
        and o.COMPANIES_ID = :id_company
        and o.RESOURCE_UNIT_TYPE = 'ROBOCIZNA'
        and o.OFFER_PRICE_PER_RESOURCE_UNIT >= 0
    ");
    $sth->bindValue(':id_project', $idProject, PDO::PARAM_INT);
    $sth->bindValue(':id_company', $idCompany, PDO::PARAM_INT);
    $sth->execute();
    $robociznaCennikRaw = $sth->fetchAll();
    $robociznaCennik = array();
    foreach ($robociznaCennikRaw as $itemRaw) {
      $item = $itemRaw;
      $item['price'] = round($item['price'], 2);
      $robociznaCennik[$itemRaw['id_zasob']] = $item;
    }
    return $robociznaCennik;
  }

  /**
	 * @returns [ $id_zasob => [ 'price' => $price, 'ID', 'id_zasob', 'id_company', 'id_project', 'unit', 'quantity' ] ]
	 */
	public static function getDefaultCennik($idCompany = 0) {
		$schema = ProjectKosztorysSchema::getSchema();
		$typeIdList = array_keys($schema['config']['type']);
		$sqlTypeIdList = implode(',', $typeIdList);
		$pdo = DB::getPDO();
		$sth = $pdo->prepare("
			select o.ID
				, o.CRM_LISTA_ZASOBOW_ID as id_zasob
				, o.COMPANIES_ID as id_company
				, o.ID_PROJECT as id_project
				, o.OFFER_PRICE_PER_RESOURCE_UNIT as price
				, o.RESOURCE_UNIT_TYPE as unit
    --		, o.OFFER_UNIT_TYPE as unit
				, o.REQUIRED_RESOURCE_UNITS as quantity
			from CRM_LISTA_ZASOBOW_OFFERS o
			where o.CRM_LISTA_ZASOBOW_ID in({$sqlTypeIdList})
        and (o.A_STATUS is null or o.A_STATUS not in ('DELETED'))
				and o.ID_PROJECT = 0
				and o.COMPANIES_ID = :id_company
		");
		$sth->bindValue(':id_company', $idCompany, PDO::PARAM_INT);
		$sth->execute();
		$cennikRaw = $sth->fetchAll();
		$cennik = array();
		foreach ($cennikRaw as $itemRaw) {
			$item = $itemRaw;
			$item['price'] = round($item['price'], 2);
			if (!empty($cennik[$itemRaw['id_zasob']])) {
				if ($itemRaw['ID'] < $cennik[$itemRaw['id_zasob']]['ID']) continue;
			}
			$cennik[$item['id_zasob']] = $item;
		}
		return $cennik;
	}

  public static function getTypeLabel($idType) {
    $schema = ProjectKosztorysSchema::getSchema();
    if (!array_key_exists($idType, $schema['config']['type'])) throw new Exception("Missing Type in schema ([{$idType}])");
    DBG::_('DBG', '>1', 'type', $schema['config']['type'][$idType], __CLASS__, __FUNCTION__, __LINE__);
    return V::get($idType, "[$idType]", $schema['config']['type']);
  }

  public static function updatePriceProjectCennik($idType, $idProject, $price, $jednostka) {
    $item = self::getItemProjectCennik($idType, $idProject, $jednostka);
    DBG::log($item, 'array', "item to update price '{$price}' for (\$idType: {$idType}, \$idProject: {$idProject}, \$jednostka: {$jednostka})");
    $usrLogin = User::getLogin();
    $sqlObj = array();
    $sqlObj['CRM_LISTA_ZASOBOW_ID'] = $idType;
    $sqlObj['OFFER_PRICE_PER_RESOURCE_UNIT'] = $price;
    $sqlObj['RESOURCE_UNIT_TYPE'] = $jednostka;
    $sqlObj['OFFER_UNIT_TYPE'] = $jednostka;
    if (empty($item)) {
      $sqlObj['COMPANIES_ID'] = 0;
      $sqlObj['ID_PROJECT'] = $idProject;
      $sqlObj['REQUIRED_RESOURCE_UNITS'] = 0;
      $idInserted = DB::getDB()->ADD_NEW_OBJ('CRM_LISTA_ZASOBOW_OFFERS', (object)$sqlObj);
      if ($idInserted <= 0) throw new Exception("Nie udało się utworzyć rekordu");
    } else {
      $sqlObj['ID'] = $item['ID'];
      $affected = DB::getDB()->UPDATE_OBJ('CRM_LISTA_ZASOBOW_OFFERS', (object)$sqlObj);
      if ($affected < 0) throw new Exception("Nie udało się zaktualizować rekordu");
    }
  }

  public static function updatePriceDefaultCennik($idType, $idCompany, $price, $jednostka) {
    $item = self::getItemDefaultCennik($idType, $idCompany, $jednostka);
    $usrLogin = User::getLogin();
    $sqlObj = array();
    $sqlObj['CRM_LISTA_ZASOBOW_ID'] = $idType;
    $sqlObj['OFFER_PRICE_PER_RESOURCE_UNIT'] = $price;
    $sqlObj['RESOURCE_UNIT_TYPE'] = $jednostka;
    $sqlObj['OFFER_UNIT_TYPE'] = $jednostka;
    if (empty($item)) {
      $sqlObj['COMPANIES_ID'] = $idCompany;
      $sqlObj['ID_PROJECT'] = 0;
      $sqlObj['REQUIRED_RESOURCE_UNITS'] = 0;
      $idInserted = DB::getDB()->ADD_NEW_OBJ('CRM_LISTA_ZASOBOW_OFFERS', (object)$sqlObj);
      if ($idInserted <= 0) throw new Exception("Nie udało się utworzyć rekordu");
    } else {
      $sqlObj['ID'] = $item['ID'];
      $affected = DB::getDB()->UPDATE_OBJ('CRM_LISTA_ZASOBOW_OFFERS', (object)$sqlObj);
      if ($affected < 0) throw new Exception("Nie udało się zaktualizować rekordu");
    }
  }

  public static function getPriceDefaultCennik($idType, $idCompany = 0, $jednostka = 'zasob') {
    $item = self::getItemDefaultCennik($idType, $idCompany, $jednostka);
    return (!empty($item)) ? round($item['price'], 2) : 0;
  }
  public static function getItemDefaultCennik($idType, $idCompany = 0, $jednostka = 'zasob') {
    $cennikRaw = self::_getPrices($idType);
    foreach ($cennikRaw as $item) {// fetch price from Oferta Admin
      if ($item['id_project']) continue;
      if (!empty($idCompany) && !empty($item['id_company']) && $idCompany != $item['id_company']) continue;
      if (!empty($idCompany) && empty($item['id_company'])) continue;
      if (empty($idCompany) && !empty($item['id_company'])) continue;
      if (empty($jednostka) || (!empty($jednostka) && $jednostka == $item['unit'])) {
        return $item;
      }
    }
    return null;
  }
  public static function getPriceProjectCennik($idType, $idProject, $jednostka) {
    $item = self::getItemProjectCennik($idType, $idProject, $jednostka);
    return (!empty($item)) ? round($item['price'], 2) : 0;
  }
  public static function getItemProjectCennik($idType, $idProject, $jednostka) {
    $cennikRaw = self::_getPrices($idType, $idProject);
    foreach ($cennikRaw as $item) {// fetch price from Project Oferta
      if ($idProject == $item['id_project'] && !$item['id_company']) {
        if (empty($jednostka) || (!empty($jednostka) && $jednostka == $item['unit'])) {
          return $item;
        }
      }
    }
    return null;
  }
  public static function getPrice($idType, $idProject = 0, $idCompany = 0, $jednostka = '') {
    $cennikRaw = self::_getPrices($idType);
    if (empty($cennikRaw)) return 0;
    DBG::_('DBG', '>1', 'prices cennikRaw', $cennikRaw, __CLASS__, __FUNCTION__, __LINE__);
    $price = 0;
    $filteredCennik = $cennikRaw;
    DBG::log($filteredCennik, 'array', "getPrice({$idType}, {$idProject}, {$idCompany}, {$jednostka}) all prices rows");

    $filteredCennik = array_filter($filteredCennik, function ($item) use ($jednostka) {
      return (empty($jednostka) || (!empty($jednostka) && $jednostka === $item['unit']));
    });
    DBG::log($filteredCennik, 'array', "getPrice({$idType}, {$idProject}, {$idCompany}, {$jednostka}) prices filtered by unit");
    if (empty($filteredCennik)) return 0;

    $adminCennik = array_filter($filteredCennik, function ($item) {
      return (!$item['id_project'] && !$item['id_company']);
    });
    DBG::log($adminCennik, 'array', "getPrice({$idType}, {$idProject}, {$idCompany}, {$jednostka}) filtered admin prices");
    if (!empty($adminCennik)) {
      foreach ($adminCennik as $item) {
        $price = round($item['price'], 2);
      }
    }

    if ($idProject === 0 && $idCompany === 0) {
      return $price; // shold be in $adminCennik
    }
    else if ($idProject > 0 && $idCompany === 0) {
      $filteredCennik = array_filter($filteredCennik, function ($item) {
        return ($item['id_project'] > 0 && !$item['id_company']);
      });
      DBG::log($filteredCennik, 'array', "getPrice({$idType}, {$idProject}, {$idCompany}, {$jednostka}) prices filtered by proj > 0 && com == 0");
    }
    else if ($idProject === 0 && $idCompany > 0) {
      $filteredCennik = array_filter($filteredCennik, function ($item) {
        return (!$item['id_project'] && $item['id_company'] > 0);
      });
      DBG::log($filteredCennik, 'array', "getPrice({$idType}, {$idProject}, {$idCompany}, {$jednostka}) prices filtered by proj == 0 && com > 0");
    }
    else if ($idProject > 0 && $idCompany > 0) {
      $filteredCennik = array_filter($filteredCennik, function ($item) {
        return ($item['id_project'] > 0 && $item['id_company'] > 0);
      });
      DBG::log($filteredCennik, 'array', "getPrice({$idType}, {$idProject}, {$idCompany}, {$jednostka}) prices filtered by proj > 0 && com > 0");
    }
    if (empty($filteredCennik)) return $price;
    foreach ($filteredCennik as $item) {
      $price = round($item['price'], 2);
    }
    DBG::log("getPrice({$idType}, {$idProject}, {$idCompany}, {$jednostka}) returns '{$price}'");
    return $price;
  }
  public static function _getPrices($idType, $idProject = 0, $idCompany = 0) {
    $schema = ProjectKosztorysSchema::getSchema();
    if (!array_key_exists($idType, $schema['config']['type'])) throw new Exception("Missing Type in schema ([{$idType}])");
    $sqlIdType = DB::getPDO()->quote($idType, PDO::PARAM_INT);
    $sqlFiltrProject = "";
    if ($idProject > 0) {
      $sqlIdProject = DB::getPDO()->quote($idProject, PDO::PARAM_INT);
      $sqlFiltrProject = "and (o.ID_PROJECT = 0 or o.ID_PROJECT = {$sqlIdProject})";
    }
    $cennikRaw = DB::getPDO()->fetchAll("
      select o.ID
        , o.CRM_LISTA_ZASOBOW_ID as id_zasob
        , o.COMPANIES_ID as id_company
        , o.ID_PROJECT as id_project
        , o.OFFER_PRICE_PER_RESOURCE_UNIT as price
        , o.RESOURCE_UNIT_TYPE as unit
        -- , o.OFFER_UNIT_TYPE as unit
        , o.REQUIRED_RESOURCE_UNITS as quantity
      from CRM_LISTA_ZASOBOW_OFFERS o
      where o.CRM_LISTA_ZASOBOW_ID = {$sqlIdType}
        and (o.A_STATUS is null or o.A_STATUS not in ('DELETED'))
        and o.OFFER_PRICE_PER_RESOURCE_UNIT >= 0
        {$sqlFiltrProject}
    ");
    DBG::_('DBG', '>1', 'cennikRaw', $cennikRaw, __CLASS__, __FUNCTION__, __LINE__);
    return $cennikRaw;
  }

}