sabecs
asked on
PHP - modify class to include addition where clause?
At the moment the class getCaSeSt returns staff for all locations, the select statement below shows staff id's in a particular location/zip code
Can this be added to class getCaSeSt below as an additional where clause?
SELECT id FROM `wp_ab_staff` WHERE id IN (
SELECT staff_id FROM wp_ab_staff_locations WHERE location_id = (
SELECT id FROM `wp_ab_locations` WHERE `title` = '3109'
))Can this be added to class getCaSeSt below as an additional where clause?
abstract class Config
{
/**
* Get categories, services and staff members for drop down selects
* for the 1st step of booking wizard.
*
* @return array
*/
public static function getCaSeSt()
{
$result = array(
'categories' => array(),
'services' => array(),
'staff' => array(),
);
// This array is for collecting staff members for compound services.
$staff_members = array();
// Select all services (with categories and staff members)
// which have at least one staff member assigned.
$rows = Entities\Service::query( 's' )
->select( '`s`.`id` AS `service_id`,
IFNULL(`c`.`id`,0) AS `category_id`,
IFNULL(`c`.`name`, \'' . Query::escape( __( 'Uncategorized', 'bookly' ) ) . '\') AS `category_name`,
IFNULL(`s`.`title`, \'' . Query::escape( __( 'Untitled', 'bookly' ) ) . '\') AS `service_name`,
`c`.`position` AS `category_position`,
`s`.`position` AS `service_position`,
`s`.`type` AS `service_type`,
`s`.`visibility` AS `service_visibility`,
`st`.`id` AS `staff_id`,
`st`.`position` AS `staff_position`,
`st`.`full_name` AS `staff_name`,
`ss`.`capacity`,
`ss`.`price`' )
->innerJoin( 'StaffService', 'ss', 'ss.service_id = s.id' )
->leftJoin( 'Category', 'c', 'c.id = s.category_id' )
->leftJoin( 'Staff', 'st', 'st.id = ss.staff_id' )
->where( 's.type', Entities\Service::TYPE_SIMPLE )
->whereNot( 'st.visibility', 'private' )
->fetchArray();
foreach ( $rows as $row ) {
$category_id = intval( $row['category_id'] );
$service_id = intval( $row['service_id'] );
$staff_id = intval( $row['staff_id'] );
if ( ! isset( $result['services'][ $service_id ] ) ) {
$result['services'][ $service_id ] = array(
'id' => $service_id,
'name' => Utils\Common::getTranslatedString( 'service_' . $service_id, $row['service_name'] ),
'category_id' => $category_id,
'staff' => array(),
'max_capacity' => $row['capacity'],
'position' => $row['service_position'],
'visibility' => $row['service_visibility'],
);
} elseif ( $result['services'][ $service_id ]['max_capacity'] < $row['capacity'] ) {
// Detect the max capacity for each service
// (it is the max capacity from all staff members who provides this service).
$result['services'][ $service_id ]['max_capacity'] = $row['capacity'];
}
if ( ! isset( $result['staff'][ $staff_id ] ) ) {
$result['staff'][ $staff_id ] = array(
'id' => $staff_id,
'name' => Utils\Common::getTranslatedString( 'staff_' . $staff_id, $row['staff_name'] ),
'services' => array(),
'position' => $row['staff_position'],
);
}
if ( $row['category_id'] != '' && !isset( $result['categories'][ $category_id ] ) ) {
$result['categories'][ $category_id ] = array(
'id' => $category_id,
'name' => Utils\Common::getTranslatedString( 'category_' . $category_id, $row['category_name'] ),
'services' => array(),
'position' => $row['category_position'],
);
}
if ( ! isset ( $result['services'][ $service_id ]['staff'][ $staff_id ] ) ) {
$staff_member = $result['staff'][ $staff_id ];
unset ( $staff_member['services'] );
$staff_members[ $service_id ][ $staff_id ] = $staff_member;
if ( self::isPaymentDisabled() == false ) {
$staff_member['name'] .= ' (' . Utils\Common::formatPrice( $row['price'] ) . ')';
}
$result['services'][ $service_id ]['staff'][ $staff_id ] = $staff_member;
}
if ( ! isset ( $result['staff'][ $row['staff_id'] ]['services'][ $service_id ] ) ) {
$service = $result['services'][ $service_id ];
unset ( $service['staff'], $service['category_id'] );
$service['max_capacity'] = $row['capacity'];
$result['staff'][ $staff_id ]['services'][ $service_id ] = $service;
}
if ( ! isset ( $result['categories'][ $category_id ]['staff'][ $staff_id ] ) ) {
$staff_member = $result['staff'][ $staff_id ];
unset ( $staff_member['services'] );
$result['categories'][ $category_id ]['staff'][ $staff_id ] = $staff_member;
}
if ( ! isset ( $result['categories'][ $category_id ]['services'][ $service_id ] ) ) {
$service = $result['services'][ $service_id ];
unset ( $service['staff'], $service['max_capacity'], $service['category_id'] );
$result['categories'][ $category_id ]['services'][ $service_id ] = $service;
}
}
$result = apply_filters( 'bookly_prepare_casest', $result, $staff_members );
foreach ( $result['services'] as $service_id => $service ) {
if ( $service['visibility'] == 'private' ) {
unset( $result['services'][ $service_id ] );
}
}
foreach ( array( 'categories', 'staff' ) as $key ) {
foreach ( $result[ $key ] as $id => &$data ) {
foreach ( $data['services'] as $service_id => $service ) {
if ( $service['visibility'] == 'private' ) {
unset( $data['services'][ $service_id ] );
}
}
if ( empty( $data['services'] ) ) {
unset( $result[ $key ][ $id ] );
}
}
}
return $result;
}
}ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then just replace '759' with $customer_location.
ASKER
Thanks Steve, I am sure I tried that and it didn't work, but it's working now.
ASKER
Thanks for your help, very much appreciated.
ASKER
I created a staff locations class as below
Open in new window
and then added left join and where clause as per below, just need to work how to include variable $customer_location instead of hardcoded value of 759?
Open in new window