Databasemodel Drupal 7: verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
 
(9 tussenliggende versies door dezelfde gebruiker niet weergegeven)
Regel 560: Regel 560:
 
</pre>
 
</pre>
  
−
== Drupal Commerce: Omzet per artikel uitlezen (maart 2020) ==
+
== Drupal Commerce: Omzet per artikel uitlezen (2020) ==
  
 
Ik heb weinig zin om dit via de API te doen: Elke seconde die ik aan Drupal besteed, is zonde van de tijd. Dus via SQL. Zal wel niet gemakkelijk zijn.
 
Ik heb weinig zin om dit via de API te doen: Elke seconde die ik aan Drupal besteed, is zonde van de tijd. Dus via SQL. Zal wel niet gemakkelijk zijn.
  
−
* Waarschijnlijk gaat dit via ''line_items''
+
Tjakka: In een half uurtje gepiept. Later (juli 2020) uitgebreid, en dat kostte ca. een uurtje. Die shit zakt weg. Vandaar:
−
* Ook orderstatus nodig, want alleen geslaagde orders meenemen.
+
 
 +
<pre>
 +
#
 +
# Retrieve revenue per product data from a Drupal 7-database
 +
####################################################################################################################################################
 +
#
 +
# Performed on a copy of such a database on my laptop
 +
#
 +
use kbo2;
 +
 
 +
 
 +
####################################################################################################################################################
 +
# Drop existing table "dvb_revenue"
 +
####################################################################################################################################################
 +
#
 +
drop table if exists dvb_revenue;
 +
 
 +
 
 +
####################################################################################################################################################
 +
# Create & fill table "dvb_revenue"
 +
####################################################################################################################################################
 +
#
 +
create table
 +
dvb_revenue
 +
select
 +
    commerce_order.order_id as order_id,
 +
    commerce_order.status as order_status,
 +
    commerce_line_item.line_item_id as line_item_id,
 +
    # commerce_line_item.type as line_item_type,
 +
    commerce_line_item.line_item_label as sku,
 +
    commerce_product.title as product_title,
 +
    field_data_field_pt_body.field_pt_body_summary as description_summary,
 +
    field_data_field_pt_body.field_pt_body_value as description,
 +
    commerce_line_item.quantity as quantity,
 +
    field_data_commerce_total.commerce_total_amount as amount_cents_incl_vat
 +
from
 +
    commerce_order
 +
join
 +
    commerce_line_item
 +
    on
 +
    commerce_order.order_id = commerce_line_item.order_id
 +
join
 +
    field_data_commerce_total
 +
    on
 +
    commerce_line_item.line_item_id = field_data_commerce_total.entity_id
 +
 
 +
 
 +
# Join with commerce_product to get product name
 +
########################################################################
 +
#
 +
join
 +
    commerce_product
 +
    on
 +
    commerce_product.sku = commerce_line_item.line_item_label
 +
 +
 +
# Join with field_data_field_pt_body for product descriptions
 +
########################################################################
 +
#
 +
join
 +
    field_data_field_pt_body
 +
    on
 +
    field_data_field_pt_body.entity_id = commerce_product.product_id
 +
 
 +
 +
# Where clause
 +
########################################################################
 +
#   
 +
where
 +
    commerce_order.status="Bedankt"
 +
or
 +
    commerce_order.status="completed"
 +
or
 +
    commerce_order.status="invoiced";
 +
</pre>
 +
 
 +
== Drupal Commerce: Omzet uit België (lente 2020) ==
 +
 
 +
De code uit een paar hoofdstukken hierboven, werkte niet: Sommige velden bestonden niet. Deze code werkt wel. Het gaat in twee stappen:
 +
 
 +
Eerste stap:
 +
 
 +
<pre>
 +
#
 +
# Calculate revenue from Belgium in 2019
 +
##############################################################################################################
 +
#
 +
# kbo2 is een locale kopie van de betreffende Drupal-productiesite
 +
#
 +
use kbo2;
 +
 
 +
 
 +
##############################################################################################################
 +
# Form table "orders"
 +
##############################################################################################################
 +
#
 +
drop table if exists orders;
 +
 
 +
create table orders
 +
select
 +
    #
 +
    # commerce_order
 +
    ###################################################################
 +
    #
 +
    commerce_order.order_id as order_id,
 +
    commerce_order.mail                    as "order_mail",
 +
    commerce_order.uid as uid,
 +
    commerce_order.status                  as "order_status",
 +
    left(from_unixtime(commerce_order.created),4)      as "order_year",
 +
   
 +
   
 +
    # order_total_amount
 +
    ###################################################################
 +
    #
 +
    field_data_commerce_order_total.commerce_order_total_amount as "order_total_amount",
 +
 
 +
 
 +
    # Country
 +
    ###################################################################
 +
    #
 +
    field_data_commerce_customer_address.commerce_customer_address_country as "country"
 +
    # field_data_commerce_customer_address.*
 +
   
 +
from
 +
    commerce_order
 +
 
 +
 
 +
# Join: field_data_commerce_order_total
 +
###################################################################
 +
# 
 +
left join
 +
  field_data_commerce_order_total
 +
  on
 +
  field_data_commerce_order_total.entity_id = commerce_order.order_id   
 +
 
 +
 
 +
# Join: customer_address
 +
########################################################
 +
#
 +
left join
 +
  field_data_commerce_customer_address
 +
  on
 +
  field_data_commerce_customer_address.entity_id = commerce_order.uid
 +
 
 +
# Where clause
 +
###################################################################
 +
#
 +
where
 +
  (
 +
      commerce_order.status like "Bedankt"
 +
      or
 +
      commerce_order.status like "Completed"
 +
      or
 +
      commerce_order.status like "Invoiced"
 +
  )
 +
  and
 +
      left(from_unixtime(commerce_order.created),4)="2019"
 +
  and
 +
      field_data_commerce_customer_address.commerce_customer_address_country = "BE";
 +
 
 +
select * from orders;
 +
</pre>
 +
 
 +
Tweede stap:
 +
 
 +
<pre>
 +
use kbo2;
 +
 
 +
select distinct
 +
  order_status,
 +
  sum(order_total_amount)/100 as revenue_incl_btw_incl_transport,
 +
  sum(order_total_amount)/100/1.21 as revenue_incl_transport_ex_btw   
 +
from
 +
  orders
 +
group by
 +
  order_status
 +
</pre>
 +
 
 +
== Drupal Commerce: Assortiment uitlezen (dec. 2020) ==
 +
 
 +
Uit een niet-actieve Drupal Commerce-database wil ik het assortiment uitlezen, ivm. ''business intelligence''. Het ging me alleen om een paar basis-gegevens:
 +
 
 +
<pre>
 +
################################################################################
 +
# Find product data
 +
################################################################################
 +
#
 +
# * Drupal 7 makes a distinction between product_display and something-else
 +
# * product_display can be fount in table "node"
 +
# * Actual product data: "commerce_product"
 +
# * product_display doesn't add anything - commerce_product suffices
 +
#
 +
#
 +
# select * from node where type = "product_display";
 +
#
 +
select
 +
  sku,
 +
  title as product_title,
 +
  created as created
 +
from
 +
  commerce_product;
 +
</pre>
  
 
== Zie ook ==
 
== Zie ook ==

Huidige versie van 28 dec 2020 om 17:51

Drupal 7 Datamodel

Alle Drupal Commerce-tabellen

  • commerce_calculated_price
  • commerce_checkout_pane
  • commerce_customer_profile
  • commerce_customer_profile_revision
  • commerce_flat_rate_service
  • commerce_line_item
  • commerce_order
  • commerce_order_revision
  • commerce_payment_transaction
  • commerce_payment_transaction_revision
  • commerce_product
  • commerce_product_revision
  • commerce_product_type
  • commerce_tax_rate
  • commerce_tax_type
  • field_data_commerce_... - Maatwerk-velden
  • field_data_field_... - Maatwerk-velden
  • field_revision_commerce_... - Maatwerk-velden
  • field_revision_field_... - Maatwerk-velden.

Tabel - Node

Velden: 

  Naam        Omschrijving             Type           Default   Opmerkingen
  ----        ------------             -----------    -------   -----------
* nid         Node-ID                  Int(10)                  Primaire sleutel
* vid         Version-ID               Int(10)        NULL      Foreign key van tabel node_revisions
* type        Content-type             Varchar(32)    ''        Wat je vindt onder Structure > Content-types
* language                             Varchar(12)    ''
* title                                Varchar(255)   ''
* uid         User-ID                  Int(11)        0
* status      Published?               Int(11)        1
* created     Unix timestamp           Int(11)        0
* changed     Unix timestamp           Int(11)        0         'created' en 'changed' hebben vaak dezelfde waarde
* comment     Commentaar toegestaan?   Int(11)        0
* promote                              Int(11)        0
* sticky                               Int(11)        0
* tnid        Translation Node ID      Int(10)        0
* translate                            Int(11)        0

Tabel - node_revision

  Naam        Omschrijving             Datatype       Default
  ----        ------------             -----------    -------
* nid         Node-ID (FK)             INT(10)        '0'
* vid         Revision-ID (PK)         INT(10
* uid         User-ID                  INT(11)        '0'
* title       Node-title               VARCHAR(255)   ''
* log         ?                        LONGTEXT
* timestamp   Created                  INT(11)        '0'
* status      Published?               INT(11)        '1'
* comment     Commentaar toegestaan?   INT(11)        '0'
* promote     Promoted                 INT(11)        '0'
* sticky      Sticky on top?           INT(11)        '0'
* ds_switch   ?                        VARCHAR(255)   ''

Taxonomieën

Tabellen 

* taxonomy_index            (nid, tid, sticky, created)     Associatie tussen node & taxon
* taxonomy_term_data        (tid, vid, name, description)   Taxon
* taxonomy_term_hierarchy   (tid, parent)                   Hierarchie: ouder van een taxon
* taxonomy_vocabulary       (vid, name, machine_name, 
                             description, hierarchy,        Definitie van een vocabulaire
                             module, weight)

Veldnamen

Veldnamen zijn doorgaans consistent door de gehele database. Oftewel, veld 'nid' in de ene tabel, is dezelfde 'nid' in een andere tabel: 

* nid - Node ID
* tid - Term ID = Taxon ID
* vid - Vocabulary ID

Eigen velden - Voorbeeld

In Drupal 7 kun je aan een taxonomie eigen velden toevoegen. Elk veld vorm een eigen databasetabel. Dit is een voorbeeld waarbij een afbeelding is toegevoegd aan een taxonomie: 

Veldnaam: field_revision_field_taxonomie_afbeelding

Velden:

* entity_type
* bundle
* deleted
* entity_id
* revision_id
* language
* delta
* field_taxonomie_afbeelding_fid
* field_taxonomie_afbeelding_alt
* field_taxonomie_afbeelding_title
* field_taxonomie_afbeelding_width
* field_taxonomie_afbeelding_height

Omzet Drupal Commerce uitlezen

Net als bij Übercart voor Drupal 6, zou ik graag via SQL de omzet willen uitlezen uit Drupal Commerce in Drupal 7.

Tabellen

commerce_order

  • order_id - PK
  • order_number - Gebruikers-order-id
  • revision_id - FK voor tabel commerce_order_revision
  • created - Unix timestamp
  • status - Bv. completed.
  • data - Serialised dataveld (longblob) waarvan de inhoud in MySQL Workbench alleen te lezen is als je Open value in editor kiest , maar in de mysql-client prima prima is uit te lezen → Geen ordertotalen.

Voorbeeld data-veld: 

a:4:
{
	s:17:"last_cart_refresh";i:1420733641;
	s:14:"payment_method";
	s:38:"paypal_wps|commerce_payment_paypal_wps";
	s:20:"payment_redirect_key";
	s:43:"TGUOciUPKaZg3AU1237eLdwHNJdDqBLG0w5lCx9VEk";
	s:43:"commerce_payment_order_paid_in_full_invoked";
	b:1;
}

commerce_order_revision

Drupal slaat vaak veel info op in revision-velden. Elk record bevat daarbij de complete status van de onderhavige entiteit. Daarom is in dit geval alleen het laatste revision-veld relevant. Velden:

  • order_id - FK tabel commerce_order
  • order_number
  • revision_id
  • data (serialised).

Voorbeeld data-veld van de het laatste revisie-veld van een order met status completed: 

a:4:
{
   s:17:"last_cart_refresh";i:1420733641;
   s:14:"payment_method";
   s:38:"paypal_wps|commerce_payment_paypal_wps";
   s:20:"payment_redirect_key";
   s:43:"TGUOciUPKaZg3AU692c7eLdwHN123qBLG0w5lCx9VEk";
   s:43:"commerce_payment_order_paid_in_full_invoked";
   b:1;
}

field_data_commerce_order_total

  • commerce_order_total_amount - Inclusief BTW, uitgedrukt in centen
  • entity_id (FK) = order_id. Omdit dit een dynamisch gegenereerd veld is, is de benaming generiek. De waardes komen echter prima overeen met de order-PK.

Overige tabellen

  • commerce_order_revision - Ziet er niet relevant uit
  • commerce_line_item - Ziet er niet relevant uit.

API-calls

De koninklijke weg om deze data uit een webshop te peuteren, is via de vastgestelde API-calls te doen. Mocht ik dat ooit inderdaad doen: Heel moeilijk lijkt het niet te zijn. Nog een keer.

Commentaren verwijderen

Gevalletje spam-posting: Hoe verwijder ik 500 commentaren?

Structuur 

  Tabel                         Sleutel
  ---------------               ------------------------------
* comment                       PK: cid - Dit is de hoofdtabel
* users                         FK: uid
* node                          FK: nid
* role_permission               FK: role_permission_rid - ?
* field_data_comment_body       ?
* field_revision_comment_body   ?

Alle commentaar verwijderen

Maak voor de zekerheid eerst een backup - Ik ben slechts voor 95% zeker dat dit werkt. 

delete from field_revision_comment_body;
delete from field_data_comment_body;
delete from comment;

Drupal Commerce: Voorraadpositie op xyz-code wijzigen

Casus

De klant heeft een spreadsheet gemailed met producten + voorraadpositie. Echter: Niet de gebruikelijke SKU's worden gehanteerd, maar 'interne' xyz-codes (de codes van de logistieke partner om precies te zijn).

Datamodel

  • Tabel commerce_product: Dit betreft L1. Daar vind je product_id, sku en title. Verder weinig spannends

Drupal Commerce: Orders exporteren (feb. 2017)

Verrassend hoe ingewikkeld het bleek om 'gewoon' orders te exporteren. Volgende keer de API gebruiken (veel efficiënter + robuuster): 

-- =====================================================
-- Select
-- =====================================================
--
select
 
    -- commerce_order
    -- ==============================================================
    -- 
    commerce_order.order_id,
    commerce_order.mail                     as "order_mail",
    commerce_order.status                   as "order_status",
    from_unixtime(commerce_order.created)   as "order_created",
    from_unixtime(commerce_order.changed)   as "order_changed",
   
    -- commerce_order.order_number,
    -- commerce_order.type,
    -- commerce_order.uid,
    -- commerce_order.data,
    -- commerce_order.hostname,
 
    -- order_total
    -- =====================================================
    --
    field_data_commerce_order_total.commerce_order_total_amount        as "order_total_amount",
    field_data_commerce_order_total.commerce_order_total_currency_code as "order_currency_code",
 
    -- Users
    -- =====================================================
    --
    users.uid  as "user_id",
    users.name as "user_name",
 
    -- Shipping - salution
    -- =====================================================
    --
    field_data_field_salutation.field_salutation_value 
    as "shipping_salutation",
     
    -- shipping - first_name
    -- =====================================================
    --
    field_data_field_firstname.field_firstname_value 
    as "shipping_first_name",
 
    -- shipping - middle_name
    -- =====================================================
    --
    field_data_field_middlename.field_middlename_value 
    as "shipping_middle_name",
     
    -- shipping - last_name
    -- =====================================================
    --
    field_data_field_lastname.field_lastname_value 
    as "shipping_last_name",
 
    -- shipping_address_stree
    -- =====================================================
    --
    field_data_field_street.field_street_value 
    as "shipping_address_street",
 
    -- Housenumber
    -- ==============
    --
    field_data_field_housenumber.field_housenumber_value 
    as "shipping_address_housenumber",
 
    -- housenumberaddition
    -- ===================
    --
    field_data_field_housenumberaddition.field_housenumberaddition_value 
    as "shipping_address_housenumberaddition",
 
    -- city
    -- ====
    --
    field_data_field_cust_city.field_cust_city_value 
    as "shipping_address_city",
     
    -- zipcode
    -- =======
    --
    field_data_field_zipcode.field_zipcode_value 
    as "shipping_zipcode",
     
    -- Shipping country
    -- ===========================
    --
    field_data_field_country.field_country_iso2 
    as "shipping_address_country",
 
    -- Billing country
    -- =====================
    --
    -- field_data_field_country.field_country_iso2 
    -- as "shipping_address_country",
     
 
    -- order_line_item
    -- =====================================================
    -- 
    commerce_line_item.line_item_id     as "orderline_id",
    commerce_line_item.type             as "orderline_type",
    commerce_line_item.line_item_label  as "orderline_sku",
    commerce_line_item.quantity         as "orderline_quantity",
    
    -- commerce_line_item.created
    -- commerce_line_item.changed,
    -- commerce_line_item.data
 
    -- commerce_product - title
    -- ==========================================================
    --
    commerce_product.title as "orderline_title",
   
    -- orderline - unit price
    -- =========================================================
    --
    field_data_commerce_unit_price.commerce_unit_price_amount
    as "orderline_unit_price",

    field_data_commerce_unit_price.commerce_unit_price_currency_code
    as "orderline_unit_price_currency",
     
    -- orderline - total price
    -- ==========================================================
    --
    field_data_commerce_total.`commerce_total_amount`
    as "orderline_total_amount",

    `field_data_commerce_total`.`commerce_total_currency_code`
    as "orderline_currency"
     
-- =====================================================
-- Join
-- =====================================================
--
-- commerce_order - De basis
-- =========================
-- 
from commerce_order

-- commerce_order_revision - Niet nodig
-- ====================================
-- 
-- left join commerce_order_revision
-- on        commerce_order.revision_id = 
--           commerce_order_revision.revision_id

-- commerce_line_item
-- ==================
-- 
left join commerce_line_item
on        commerce_order.order_id = 
          commerce_line_item.order_id

-- users
-- =====
--
left join users
on   commerce_order.uid = 
     users.uid

-- field_revision_commerce_customer_address
-- ========================================
-- 
left join field_revision_commerce_customer_address
on        commerce_order.order_id = 
          field_revision_commerce_customer_address.entity_id
 
-- Shipping address
-- ======================================================
-- 
left join field_data_commerce_customer_shipping       
on        field_data_commerce_customer_shipping.entity_id = 
          commerce_order.order_id
 
-- Billing address
-- ======================================================
-- * Billing addresses worden in dezelfde tabel bijgehouden als shipping addresses.
--   Waarschijnlijk een alias defineren op die tabel om billing addresses te kunnen
--   extraheren
-- * In dit geval leek er geen gebruik te worden gemaakt van billing addresses
--
-- left join field_data_commerce_customer_billing
-- on        field_data_commerce_customer_shipping.entity_id = 
--           commerce_order.order_id

-- order_total
-- ======================================================
-- 
left join field_data_commerce_order_total
on        field_data_commerce_order_total.entity_id = 
          commerce_order.order_id
 
-- Shipping salutation
-- ===================
--
left join field_data_field_salutation
on        field_data_field_salutation.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping firstname
-- ==================
--
left join field_data_field_firstname 
on        field_data_field_firstname.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping middlename
-- ===================
--
left join field_data_field_middlename
on        field_data_field_middlename.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping lastname
-- =================
--
left join field_data_field_lastname
on        field_data_field_lastname.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping Street
-- ===============
--
left join field_data_field_street 
on        field_data_field_street.entity_id  =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping Housenumber
-- ====================
--
left join field_data_field_housenumber 
on        field_data_field_housenumber.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping housenumberaddition
-- ============================
-- 
left join field_data_field_housenumberaddition 
on        field_data_field_housenumberaddition.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping cust_city
-- ==================
--
left join field_data_field_cust_city
on        field_data_field_cust_city.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping zipcode
-- ================
--
left join field_data_field_zipcode
on        field_data_field_zipcode.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id

-- Shipping country
-- ================
--
left join field_data_field_country
on        field_data_field_country.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Billing country
-- ===============
--
-- left join    field_data_field_country
-- on           field_data_field_country.entity_id = 
--              field_data_commerce_customer_billing.commerce_customer_billing_profile_id
 
-- commerce_product
-- ========================
--
left join commerce_product
on        commerce_product.sku = 
          commerce_line_item.line_item_label
 
-- Order_line - Unit price
-- =============================
--
left join field_data_commerce_unit_price
on        field_data_commerce_unit_price.entity_id = 
          commerce_line_item.line_item_id
 
-- Order_line - Total
-- ===================================
--
left join field_data_commerce_total
on        field_data_commerce_total.entity_id = 
          commerce_line_item.line_item_id
 
-- =====================================================
-- Sort order
-- =====================================================
--
order by commerce_order.order_id asc;

Drupal Commerce: Populairste producten (aug. 2019)

Dat gaat dan toevallig weer heel gemakkelijk: 

select
	line_item_label as sku,
	sum(quantity)   as quant 

from commerce_line_item
group by line_item_label
order by quant desc;

Drupal Commerce: Prijzen uitlezen (dec. 2019)

Wéér een stap naar een Drupal-loze wereld: 

select
   sku,
   title,
   commerce_price_amount				as sell_price_eurocent_incl_vat,
   commerce_price_amount/100				as sell_price_euro_incl_vat,
   (commerce_price_amount/100)/1.21			as sell_price_euro_ex_vat,
   # cast(commerce_price_amount as decimal(4,2))	as price_04	# Werkt niet!
   round((commerce_price_amount/100)/1.21,2) 		as sell_price_euro_ex_vat_rounded_2_decimals
   # commerce_price_data
from
   commerce_product
join
   field_data_commerce_price
on
   commerce_product.product_id = field_data_commerce_price.entity_id
order by
   sku;

Drupal Commerce: Omzet per artikel uitlezen (2020)

Ik heb weinig zin om dit via de API te doen: Elke seconde die ik aan Drupal besteed, is zonde van de tijd. Dus via SQL. Zal wel niet gemakkelijk zijn.

Tjakka: In een half uurtje gepiept. Later (juli 2020) uitgebreid, en dat kostte ca. een uurtje. Die shit zakt weg. Vandaar: 

#
# Retrieve revenue per product data from a Drupal 7-database
####################################################################################################################################################
#
# Performed on a copy of such a database on my laptop
#
use kbo2;


####################################################################################################################################################
# Drop existing table "dvb_revenue"
####################################################################################################################################################
#
drop table if exists dvb_revenue;


####################################################################################################################################################
# Create & fill table "dvb_revenue"
####################################################################################################################################################
#
create table 
	dvb_revenue
select
    commerce_order.order_id				as order_id,
    commerce_order.status				as order_status,
    commerce_line_item.line_item_id			as line_item_id,
    # commerce_line_item.type				as line_item_type,
    commerce_line_item.line_item_label			as sku,
    commerce_product.title				as product_title,
    field_data_field_pt_body.field_pt_body_summary	as description_summary,
    field_data_field_pt_body.field_pt_body_value	as description,
    commerce_line_item.quantity				as quantity,
    field_data_commerce_total.commerce_total_amount	as amount_cents_incl_vat
from 
    commerce_order
join
    commerce_line_item 
    on 
    commerce_order.order_id = commerce_line_item.order_id
join
    field_data_commerce_total
    on
    commerce_line_item.line_item_id = field_data_commerce_total.entity_id


# Join with commerce_product to get product name
########################################################################
#
join
    commerce_product
    on
    commerce_product.sku = commerce_line_item.line_item_label
 
 
# Join with field_data_field_pt_body for product descriptions
########################################################################
#
join
    field_data_field_pt_body
    on
    field_data_field_pt_body.entity_id = commerce_product.product_id

 
# Where clause
########################################################################
#    
where
    commerce_order.status="Bedankt"
or
    commerce_order.status="completed"
or
    commerce_order.status="invoiced";

Drupal Commerce: Omzet uit België (lente 2020)

De code uit een paar hoofdstukken hierboven, werkte niet: Sommige velden bestonden niet. Deze code werkt wel. Het gaat in twee stappen:

Eerste stap: 

#
# Calculate revenue from Belgium in 2019
##############################################################################################################
#
# kbo2 is een locale kopie van de betreffende Drupal-productiesite
#
use kbo2;


##############################################################################################################
# Form table "orders"
##############################################################################################################
#
drop table if exists orders;

create table orders
select
    #
    # commerce_order
    ###################################################################
    #
    commerce_order.order_id				as order_id,
    commerce_order.mail                     		as "order_mail",
    commerce_order.uid					as uid,
    commerce_order.status                   		as "order_status",
    left(from_unixtime(commerce_order.created),4)       as "order_year",
    
    
    # order_total_amount
    ###################################################################
    #
    field_data_commerce_order_total.commerce_order_total_amount	as "order_total_amount",


    # Country
    ###################################################################
    #
    field_data_commerce_customer_address.commerce_customer_address_country as "country"
    # field_data_commerce_customer_address.*
    
from
    commerce_order


# Join: field_data_commerce_order_total
###################################################################
#   
left join
   field_data_commerce_order_total
   on
   field_data_commerce_order_total.entity_id = commerce_order.order_id    


# Join: customer_address
########################################################
# 
left join 
   field_data_commerce_customer_address
   on
   field_data_commerce_customer_address.entity_id = commerce_order.uid

# Where clause
###################################################################
#
where
   (
      commerce_order.status like "Bedankt"
      or
      commerce_order.status like "Completed"
      or
      commerce_order.status like "Invoiced"
   )
   and
      left(from_unixtime(commerce_order.created),4)="2019"
   and
      field_data_commerce_customer_address.commerce_customer_address_country = "BE";

select * from orders;

Tweede stap: 

use kbo2;

select distinct
   order_status,
   sum(order_total_amount)/100	as revenue_incl_btw_incl_transport,
   sum(order_total_amount)/100/1.21 as revenue_incl_transport_ex_btw    
from
   orders
group by
   order_status

Drupal Commerce: Assortiment uitlezen (dec. 2020)

Uit een niet-actieve Drupal Commerce-database wil ik het assortiment uitlezen, ivm. business intelligence. Het ging me alleen om een paar basis-gegevens: 

################################################################################
# Find product data
################################################################################
#
# * Drupal 7 makes a distinction between product_display and something-else
# * product_display can be fount in table "node"
# * Actual product data: "commerce_product"
# * product_display doesn't add anything - commerce_product suffices
#
#
# select * from node where type = "product_display";
#
select
   sku,
   title	as product_title,
   created	as created
from
   commerce_product;

Zie ook

Bronnen

Orderbedrag Drupal Commerce uitlezen

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Databasemodel_Drupal_7&oldid=25061"