How to aggregate transactional records by month year but based on subscription period not calendar months?

I have a table containing transactional events for customers:

table: events
| id   | cust_id  | date                       | amount | 
|------|----------|----------------------------|--------| 
| 78   | 211      | 2010-06-18 20:10:00.000000 | 20     | 
| 84   | 211      | 2010-07-18 05:53:00.000000 | 35     | 
| 91   | 211      | 2010-07-27 05:45:00.000000 | 25     | 
| 2136 | 211      | 2011-02-14 20:07:00.000000 | 5000   | 
| 2947 | 211      | 2011-02-21 20:29:25.000000 | 22     | 
| 2945 | 211      | 2011-03-14 20:48:26.000000 | 22     | 

I want to produce a year and month aggregation of the sum of the amounts, but where the month periods correspond to the customer’s subscription period. This period has a specific start date, for example the 20th of the month.

The query can take the inputs:

  • customer id – the customer to generate the report for
  • start day of period – the day of the month on which the “month” should start

Here is a query that naively groups by month without the custom period:

SELECT
  extract(YEAR FROM date)  AS year,
  extract(MONTH FROM date) AS month,
  sum(amount)              AS amount
FROM events
WHERE cust_id = 211
GROUP BY 1, 2
ORDER BY year, month;

Example output of this naive query:

| year | month | amount | 
|------|-------|--------| 
| 2010 | 6     | 20     | 
| 2010 | 7     | 60     | 
| 2011 | 2     | 5022   | 
| 2011 | 3     | 22     | 

But, given that start day for customer 211 is the 20th of the month, I want the actual output to be:

| year | month | amount | 
|------|-------|--------| 
| 2010 | 5     | 20     |  from May 20 - June 19
| 2010 | 6     | 35     |  from June 20 - July 19
| 2010 | 7     | 25     |  from July 20 - Aug 19
| 2011 | 1     | 5000   |  from Jan 20 - Feb 19
| 2011 | 2     | 44     |  from Feb 20 - March 19

I’m at a loss for how to go from my naive query to the query I need to generate this output.

How to use ajax to display database records using a select list?

I have used a select list in my custom form to select a product code and display data corresponding to that code. I am using AJAX for this task but it does not display any record.

function webform_module_menu() {
  $items = array();

  // Change the description of a form element.
  $items['examples/webform_module/report'] = array(
    'title' => 'Report',
    'page callback' => 'drupal_get_form',
    'page arguments' => array('webform_module_report'),
    'access callback' => TRUE,
    'weight' => 0,
  );
   return $items;
}

function webform_module_report($form, &$form_state) {
  $result = db_query("SELECT o.nid, o.sku FROM {formmodule_order} o ");
  $options = array();

  foreach($result as $row) {
    $options[$row->nid] = $row->sku;  
  }

  $form['select'] = array(
    '#title' => 'Option',
    '#type' => 'select',
    '#options' => $options,
    '#ajax' => array(
      'wrapper' => 'some-form-results-wrapper',
      'callback' => 'webform_module_display'
    )
  );

  $form['results'] = array(
    '#prefix' => '
', '#suffix' => '
' ); // If the form has been submitted, build up your results and display them if (isset($form_state['values']['select'])) { $rows = db_select('formmodule_order', 't') ->fields('t') ->condition('t.sku', $form_state['values']['select']) ->execute() ->fetchAll(); $header = array(t('Product ID'), t('Product Code'), t('Product Stock'), t('Product Purchased'), t('Created')); $form['results']['#markup'] = theme('table', array('header' => $header, 'rows' => $rows)); } else { $form['results']['#markup'] = '

The results will appear here when you change the dropdown option.

'; } return $form; } function webform_module_display($form, $form_state) { return $form['results']; }

Update: after debugging the query under if statement $rows = db_select('formmodule_order', 't') ->fields('t') ->condition('t.sku', $form_state['values']['select']) ->execute() ->fetchAll(); does not print any result. It just says array(). What does that mean?

Visualforce page records not linking to their detail pages

I have create a Visualforce page which displays a list of Accounts with links to their respective record detail pages using standard list controller. I have used the following code:


    
     
  • {!a.name}

  • But unfortunately, I get an error when I check this challenge in Trailhead:
    the page does not bind to the record ID value (in order to link to the record detail page). Where am I going wrong?

    Using multiple A-records for my domain – do web browsers ever try more than one?

    If I add multiple A-records for my domain, they are returned in a round robin order by DNS servers.

    Example:

    192.0.2.1 A example.com
    192.0.2.2 A example.com
    192.0.2.3 A example.com
    

    But how does web browsers react if the first host (192.0.2.1) is down (unreachable)? Do they try the second host (192.0.2.2) or do they return a error message to the user? Are there any difference between the most popular browsers?

    If I implement my own application, I can implement so that the second is used in case the first is down, so it’s possible. And this would be very helpful to create a fault tolerant website.

    Get the average of the last X records for each foreign key

    My table is a list of yacht handicaps for yacht racing. After each race a new handicap is calculated based on how the yacht performed which is added to the table. The actual handicap used to modify the finish time of a yacht is the average of the last 3 races. I am having trouble getting my head around the sql to retrieve the race modifier of all yachts.

    My table data (I have removed extraneous columns):

    id | hcpval | yacht_id 
    ----------------------
     1 | 0.9    |  1
     2 | 0.75   |  2
     3 | 0.84   |  1
     4 | 0.71   |  2
     5 | 0.88   |  1
     6 | 0.73   |  2
     7 | 0.81   |  1
     8 | 0.7    |  2
    

    Obviously, the most recent results have the higher id. After the query has finished I’m hoping to get the following results:

    yacht_id | hcpval
    -----------------
        1    | 0.843
        2    | 0.713
    

    with relation retrieve all the records- Laravel

    I want to retrieve only the active organisations but the the database return all the records.Here is my code.you can see below a record should not be returned as a listed organisation I include below the code and my Model and The result I got from the database

          $organisations = Organisation::with(['year_status' => function ($query) use ($year_id) {
                        $query->where(['year_id' => $year_id, 'is_listed' => 1]);
                    }])->get();
    
    
    
    public function year_status()
        {
            return $this->hasMany('AppOrganisationYearStatus');
        } 
    
     and what I got :
    
    11 => Organisation {#630 ▼
          #fillable: array:8 [▶]
          #connection: "mysql"
          #table: null
          #primaryKey: "id"
          #keyType: "int"
          +incrementing: true
          #with: []
          #withCount: []
          #perPage: 15
          +exists: true
          +wasRecentlyCreated: false
          #attributes: array:14 [▼
            "id" => 39
            "organisation" => "Test Organisation"
            "sector_id" => 1
            "country_id" => 1
             ]
          #original: array:14 [▶]
          #changes: []
          #casts: []
          #dates: []
          #dateFormat: null
          #appends: []
          #dispatchesEvents: []
          #observables: []
          #relations: array:1 [▼
            "year_status" => Collection {#632 ▼
              #items: []
            }
          ]
          #touches: []
          +timestamps: true
          #hidden: []
          #visible: []
          #guarded: array:1 [▶]
        }
    

    MYSQL RECORDS MOSTRAM MULTIPLOs [pendente]

    Eu criei um código que mostra uma categoria do ITEMS no registro.

    Se o ITEM 1 for Telefones, o código mostrará os telefones. Mas o problema é quando tem mais de 2 com a mesma categoria, o código mostra 2 vezes ou mais a mesma categoria
    https://i.imgur.com/Zq5oahQ.png

     $select_parent = "SELECT ads_id, ads_title, category_id FROM public_ads WHERE ads_active = 1 AND ads_genre = 'Loja' AND ads_end = 0";
     $parent_query = $con->query($select_parent);
    
    if($parent_query->num_rows > 0) {
    
    while($parents = $parent_query->fetch_assoc()) {
    
        $select_categories = "SELECT category_id, category_title FROM public_categories WHERE category_id = '".$parents['category_id']."' ORDER BY category_id Limit 1";
        $category_query = $con->query($select_categories);            
    
        while($options = $category_query->fetch_assoc()) {
    
           echo '
  • '. $options['category_title'] .'
  • '; } } }

    Nota: Sabendo que poderão existir 1000x items da mesma categoria, e sabendo tambem que irá fazer loop de 1000x categorias iguais, pretendo que apenas mostre 1x.