Databasemodel Drupal 7: verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
(2 tussenliggende versies door dezelfde gebruiker niet weergegeven)
Regel 558: Regel 558:
 
order by
 
order by
 
   sku;
 
   sku;
 +
</pre>
 +
 +
== Drupal Commerce: Omzet per artikel uitlezen (maart 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:
 +
 +
<pre>
 +
use kbo2;
 +
 +
drop table if exists kbo2_raw;
 +
 +
create table kbo2_raw
 +
 +
select
 +
    commerce_order.order_id,
 +
    commerce_order.status,
 +
    commerce_line_item.line_item_id,
 +
    commerce_line_item.type,
 +
    commerce_line_item.line_item_label,
 +
    commerce_line_item.quantity,
 +
    field_data_commerce_total.commerce_total_amount  # In centen, incl. BTW
 +
from
 +
    commerce_order
 +
 +
 +
####################################################################################
 +
# joins
 +
####################################################################################
 +
#
 +
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
 +
 +
 +
####################################################################################
 +
# where
 +
####################################################################################
 +
#
 +
where
 +
commerce_order.status="Bedankt"
 +
or
 +
commerce_order.status="completed"
 +
or
 +
commerce_order.status="invoiced";
 
</pre>
 
</pre>
  

Versie van 30 mrt 2020 13:03

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 (maart 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: 

use kbo2;

drop table if exists kbo2_raw;

create table kbo2_raw

select
    commerce_order.order_id,
    commerce_order.status,
    commerce_line_item.line_item_id,
    commerce_line_item.type,
    commerce_line_item.line_item_label,
    commerce_line_item.quantity,
    field_data_commerce_total.commerce_total_amount   # In centen, incl. BTW
from 
    commerce_order


####################################################################################
# joins
####################################################################################
#
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


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

Zie ook

Bronnen

Orderbedrag Drupal Commerce uitlezen

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