2023-07-12.org

#+begin_ai :noweb yes :temperature 0.2
[SYS]: You are an expert programmer specialist in PHP. You give concise answers for expert users.
You use php 8.2 with psalm annotations, phpunit tests and PSR12.
You use readonly dataclasses and type annotations.

[ME]: <<sqlbuilder>>

Given the following table: <<giftcardsSQL>>, build a Find method to search the table.
If the query is numeric, search for the id, otherwise search against the code.

Return the customer email by joining order_id. This is a woocommerce system, so the customer's billing email should be used. Also return the order date and the order number (which is stored as the order's postmeta '_order_number' value).

Add filters for balance (numeric), expired (boolean), active (boolean), amount (numeric), note (enum of Warranty claim, Coupon page, Automatic). The keys for filters in $args always start with `filter`.

Add range filters for created_at and the order's date (the wp_posts creation date).

[AI]: Here is an example of how you can implement the `Find` method to search the `gift_cards` table:

```php
public static function Find(array $args): array
{
    global $wpdb;
    $query = stripslashes_deep($args['query'] ?? '');
    $options = Request::getTableSqlOptions($args);
    $options['select'] = [
        'gc.id',
        'gc.code',
        'gc.sku',
        'gc.order_id',
        'gc.amount',
        'gc.balance',
        'gc.active',
        'gc.created_at',
        'gc.expires_at',
        'gc.note',
        'gc.free_shipping',
        'pm.meta_value as order_number',
        'o.post_date as order_date',
        'u.user_email as customer_email',
    ];
    $options['from'] = 'gift_cards gc';
    $options['join'] = [
        'LEFT JOIN wp_posts o ON o.ID = gc.order_id',
        'LEFT JOIN wp_postmeta pm ON pm.post_id = gc.order_id AND pm.meta_key = "_order_number"',
        'LEFT JOIN wp_woocommerce_order_items oi ON oi.order_id = gc.order_id',
        'LEFT JOIN wp_woocommerce_order_itemmeta oim ON oim.order_item_id = oi.order_item_id AND oim.meta_key = "_billing_email"',
        'LEFT JOIN wp_users u ON u.user_login = oim.meta_value',
    ];
    $options['where'] = ['gc.id IS NOT NULL'];
    $options['groupBy'] = 'gc.id';
    if (empty($options['orderBy'])) {
        $options['orderBy'] = 'gc.id DESC';
    }
    if (is_numeric($query)) {
        $options['where'][] = "gc.id = $query";
    } else {
        $options['where'][] = $wpdb->prepare("gc.code LIKE %s", '%' . $wpdb->esc_like($query) . '%');
    }
    // Add filters
    foreach ($args as $key => $value) {
        if (str_starts_with($key, 'filter')) {
            $field = str_replace('filter', '', $key);
            if (in_array($field, ['balance', 'amount'])) {
                $options['where'][] = $wpdb->prepare("gc.$field = %d", $value);
            } elseif (in_array($field, ['expired', 'active'])) {
                $options['where'][] = $wpdb->prepare("gc.$field = %d", $value ? 1 : 0);
            } elseif ($field === 'note') {
                $options['where'][] = $wpdb->prepare("gc.$field = %s", $value);
            } elseif ($field === 'createdAt') {
                $options['where'][] = Sql::ConvertFilterDateRangeToMysqlWhereClause($value, 'gc.created_at');
            } elseif ($field === 'orderDate') {
                $options['where'][] = Sql::ConvertFilterDateRangeToMysqlWhereClause($value, 'o.post_date');
            }
        }
    }
    $sql = Sql::select($options);
    $results = $wpdb->get_results(
        count($options['binds']) ? $wpdb->prepare($sql, $options['binds']) : $sql,
        ARRAY_A
    ) ?? [];
    return $results;
}
```

This method will return an array of results from the `gift_cards` table based on the provided query and filters. The query can be either a numeric ID or a string code. The method also supports filtering by balance, expired status, active status, amount, and note. Additionally, it supports range filters for the `created_at` and `order_date` fields. The results will include the customer email, order date, and order number.

[ME]: 
#+end_ai