Zur Datenbankverwaltung und Informationen-Extraktion nutzen wir das Programm DBeaver.
Kostenloser Download ist unter diesem Link verfügbar: https://dbeaver.io/download/
Hier ist eine Anleitung wie das Programm mit unserer Datenbank eingestellt wird
Hier ist eine Anleitung wie das Programm mit unserer Datenbank eingestellt wird
Schritt 1: DBeaver herunterladen
Gehe zur DBeaver-Website:
Öffne einen Webbrowser und gehe zur offiziellen DBeaver-Website: https://dbeaver.io/download/
Wähle die Version aus:
Klicke auf den "Download" Button.
Wähle dein Betriebssystem:
Wähle dein Betriebssystem (Windows, macOS, oder Linux) und lade die entsprechende Installationsdatei herunter.
Schritt 2: DBeaver einrichten (Video dazu)
Bitte unbedingt VPN checken und aktivieren
DBeaver öffnen
Neue Verbindung einrichten:
Klicke auf das Symbol für eine neue Verbindung (meistens ein Plug-Symbol oder "+")
3. Datenbanktyp auswählen:
Wähle den Typ der Datenbank aus, mit der du dich verbinden möchtest (z.B. MySQL, PostgreSQL, SQLite, etc.)
4. Verbindungsdetails eingeben:
Gib die Verbindungsdetails ein, wie Hostname, Port, Datenbankname, Benutzername und Passwort.
Die Zugangsdaten sind Folgende:
prod-copecart-read-replica-db.cj4czl7mymoi.eu-central-1.rds.amazonaws.com
database: copecart_production
username: copecart
password: UhL8kLHQxVXfGVgdbGC3
US Copecart
US Copecart
USA Database
host - prod-usa-copecart-read-replica-db.c8x6yaa4anoo.us-east-1.rds.amazonaws.com
port: 5432
database: copecart_production
username: copecart
password: rz7L8VsQ6nDHmWtB
adapter: postgresql
Klicke auf "Test Connection", um zu überprüfen, ob die Verbindung korrekt ist.
5. Verbindung speichern und verwenden:
Wenn die Verbindung erfolgreich ist, klicke auf "Finish". Die Verbindung wird in der linken Seitenleiste unter "Database Navigator" angezeigt.
bitte die Verbindung dann aktivieren:
Click auf SQL Taste
falls nicht vorhanden, hier:
Folgend siehst du das neue SQL-Editor-Fenster:
Schritt 3: mit DBeaver Commands arbeiten
SQL-Abfragen öffnen
Entsprechende Commands nutzen (Immer darauf Achten, dass die Anführungszeichen um die Bestellungs-ID nicht doppelt sind)
Datenbankobjekte verwalten:
Verwende die Navigationsleiste, um Tabellen, Ansichten, Prozeduren und andere Datenbankobjekte zu durchsuchen und zu verwalten.
Schritt 4: Speichern in DBeaver
gewünschte Info oder nötigen Daten finden (zB. eine IPN Adresse vom einem Vendor)
Ergebnisse exportieren:
Klicke mit der rechten Maustaste auf die Ergebnistabelle und wähle "Export Data" (Daten exportieren).
Ein Dialogfenster öffnet sich, in dem du das Exportformat (z.B. CSV, Excel, JSON, SQL, XML) auswählen und dann speichern kannst.
Commands
IPN Command:
select * from ipn_messages where event like '%XXX%'
Für Kajabi (!):
select i.created_at, i."event", i.url, i.secret, i.status, i.error_response, i.vendor_id
from orders as o inner join payments as p on o.id = p.order_id inner join ipn_messages as i on p.id = i.target_object_id where o.uuid = 'XXX'
(XXX=Bestell-ID)
3. IPN zum Produkt
select * from integrations where product_id = XXX (Video Tutorial Hier)
select response from payments p where id = XXX (Video Tutorial Hier)
(XXX= product_id)
4. PayPal Transaktion:
select distinct p.uuid, o.uuid from payments as p inner join acquirer_responses on (acquirer_responses.payment_id = p.id) inner join orders as o on p.order_id = o.id where acquirer_responses.acquirer_id like '%XXX%'
Das daraus folgende Resultat sieht so aus:
Das hier ist die Bestell ID:
(XXX= PayPal Transaction Code)
5. Command für die Nachprüfung des Zahlungseingang und PayOuts
select ds.amount as transaction_net, ds.state as is_net_paid,ds.distribution_percent as transaction_percent, ds.available_at as transaction_available,
p.state as transaction_state, p.amount as transaction_amount, p.vat_amount as transaction_vat, p.created_at as transaction_created, p.uuid as payment_id,
p.payment_method, o.uuid as order_uuid, b.first_name, b.last_name, b.email
from distributed_payouts as ds
inner join payments as p on ds.payment_id = p.id
inner join orders as o on p.order_id = o.id
inner join buyer_profiles as b on o.buyer_profile_id = b.id
where ds.user_id = XXX order by p.id desc
Plain Text
(XXX=User-ID)
6. Command für die Auszahlung der einzelnen Bestellung
select dp.user_id, dp.amount as payout_transaction_net, dp.vat_amount as payout_transaction_vat, dp.distribution_percent as payout_tranasaction_percent, dp.state, pa.sequence_number ,dp.available_at, p.uuid, p.amount as transaction_amount, p.vat_amount as transaction_vat, o.uuid from payments as p inner join distributed_payouts as dp on p.id = dp.payment_id inner join orders as o on p.order_id = o.id inner join payouts as pa on dp.payout_id = pa.id where o.uuid = 'XXX'
Plain Text
(XXX=Bestell-ID)
7. Die bestimmte Zahlung in PayPal oder Stripe finden:
select a.response from payments as p inner join acquirer_responses as a on p.id = a.payment_id
where p.uuid = 'xxx'
(XXX = Transaktions-ID)
Folgend bitte http://jsonviewer.stack.hu/ für die Klärung benutzen.
8. Liste von Joint Venture Transaktionen:
select o.created_at as order_created, o.uuid as order_id, p.uuid as transaction_id, pr."name" as product_name, pr.slug as product_id, pr.product_type, p.amount as gross, (p.amount - p.vat_amount) as net,
jvp.comission_percentage as jvp_comission_percentage, jv.amount as jvp_amount, u.email as vendor_email, p.payment_method as payment_method, p.sequence_number as payment_number, bp.country as buyer_country
from orders as o inner join payments as p on o.id = p.order_id inner join payment_chunks as pc on p.id = pc.payment_id inner join jvp_amounts as jv on pc.id = jv.payment_chunk_id inner join line_items as li
on o.id = li.order_id inner join products as pr on li.product_id = pr.id inner join jvp_snapshots as jvp on li.id = jvp.line_item_id inner join buyer_profiles as bp on o.buyer_profile_id = bp.id inner join
users as u on jvp.vendor_id = u.id where jvp.vendor_id = XXX
(XXX = User-ID)
9. Liste von Vendor Transaktionen:
select o.created_at as order_created, o.uuid as order_id, p.uuid as transaction_id, pr."name" as product_name, pr.slug as product_id, pr.product_type, p.amount as gross, (p.amount - p.vat_amount) as net,
jvp.comission_percentage as jvp_comission_percentage, jv.amount as jvp_amount, u.email as vendor_email, p.payment_method as payment_method, p.sequence_number as payment_number, bp.country as buyer_country
from orders as o inner join payments as p on o.id = p.order_id inner join payment_chunks as pc on p.id = pc.payment_id inner join jvp_amounts as jv on pc.id = jv.payment_chunk_id inner join line_items as li
on o.id = li.order_id inner join products as pr on li.product_id = pr.id inner join jvp_snapshots as jvp on li.id = jvp.line_item_id inner join buyer_profiles as bp on o.buyer_profile_id = bp.id inner join
users as u on jvp.vendor_id = u.id where o.vendor_id = XXX
(XXX = User-ID)
10. Liste von Affiliates (welches Produkt, wie oft von einem Affiliate verkauft wurde):
select p."name", p.slug, u.nickname, count(u.nickname ) from orders as o inner join line_items as li on o.id = li.order_id
inner join products as p on li.product_id = p.id inner join users as u on (li.affiliate_info -> 'affiliate_id')::int = u.id
where o.vendor_id = XXX and o.state = 'success' group by (p."name", p.slug, u.nickname )
(XXX = User-ID)
11. Alles über Payout-Rechnung erfahren (wer und wann hat die Rechnung bekommen hat, wann wurde sie ausgezahlt und als paid markiert):
select user_id, payout_date, created_at, amount, vat_amount, uuid from payouts where sequence_number = 'XXX'
(XXX = Gutschtiftsnummer)
12. Änderungen im Account feststellen:
select a.created_at, a.user_id as who_changed_it, a."action", a.audited_changes, a.auditable_type as changed_table
from audits as a where (a.auditable_id = (select id from users where id = xxxxx) and a.auditable_type = 'User')
or (a.auditable_id = (select id from accounts where user_id = xxxxx) and a.auditable_type = 'UserAccount')
or (a.auditable_id = (select id from payout_rules as pr where account_id = (select id from accounts a2 where user_id = xxxxx))
and a.auditable_type = 'PayoutRule')
or (a.auditable_id = (select bank_account_id from payout_rules
where account_id = (select id from accounts a2 where user_id = xxxxx)) and a.auditable_type =
(select bank_account_type from payout_rules as pr where account_id = (select id from accounts a2 where user_id = xxx))) order by a.id desc;
(XXX = User-ID)
13. Eine Auflistung von CC Gebühren für einen bestimmten Vendor bekommen:
select p.uuid, p.state, p.amount, pc.copecart_fee from payments as p internal join payment_chunks as pc on p.id = pc.payment_id where vendor_id = xxx
(XXX = User-ID)
14. Status des Abonnements oder des Zahlungsplanes:
select a.created_at, a.user_id, a.audited_changes, a.created_at from audits as a inner join product_subscriptions as ps on a.auditable_id = ps.id
inner join line_items as li on ps.line_item_id = li.id inner join orders as o on li.order_id = o.id where
auditable_type = 'ProductSubscription' and "action" = 'update' and o.uuid = 'XXX'
(XXX = Bestell-ID)
15. Die Affiliate/Vendor Login IP Adresse feststellen:
select current_sign_in_ip, last_sign_in_ip, email
from users
where email = 'xxx';
(xxx - Email Adresse vom Vendor)
16. Die IP Adresse vom Kunden feststellen:
select remote_address
from audits as a
where auditable_type = 'Order'
and "action" = 'create'
and auditable_id = (select id from orders where uuid = 'xxxx');
(XXX = Bestell-ID)
17. Mit dieser Abfrage kann man die Liste erstellen, für welche Bestellungen (Bestell-ID, Transaktions-ID, Zahlungsmethode, Name und E-Mail-Adresse des Kunden) und Produkte der Vendor seine Auszahlungen erhalten hat:
select
pa.sequence_number,
pa.payout_date,
pa.amount as payout_amount,
pa.vat_amount as payout_vat_amount,
dp.amount as payout_transaction_amount,
dp.distribution_percent as tranasaction_percent,
p.state as payment_state,
p.amount as payment_amount,
p.vat_amount as payment_vat,
p.uuid as payment_id,
p.payment_method as payment_method,
o.uuid as order_id,
b.first_name,
b.last_name,
b.email,
pr.name as product_name,
pr.slug as product_id
from payouts as pa
inner join distributed_payouts as dp on pa.id = dp.payout_id
inner join payments as p on dp.payment_id = p.id
inner join orders as o on p.order_id = o.id
inner join buyer_profiles as b on o.buyer_profile_id = b.id
inner join products as pr on o.product_id = pr.id
where pa.user_id = XXX order by pa.created_at desc
Plain Text
(XXX = User-ID)
18. Ratenzahlungsplan checken:
select o.uuid, li.plan_type, pps.first_payment, pps.next_payments, pps.frequency, pps.payments_count from orders as o inner join line_items as li on o.id = li.order_id inner join payment_plan_snapshots as pps o li.payment_plan_snapshot_id = pps.id where o.uuid = 'XXX'
Plain Text
(XXX = Bestell-ID)
19. Subscription/Breakdown payment checken:
select product_subscriptions.*, (line_items.remote_subscription_info -> 'next_payments_subscription_id') as subscription_id, line_items.remote_subscription_info -> 'trial_subscription_id' as trial_subscription_id
from payments
join line_items on (payments.uuid = 'XXX' and line_items.order_id = payments.order_id)
join product_subscriptions on (line_items.id = product_subscriptions.line_item_id)
Plain Text
(XXX = payment-ID)
Achtung! Wenn es einen Trial gibt, wird "select response from payments p where uuid = 'XXXX'" nicht funktionieren und möglicherweise "{}" als Antwort geben.
20. Liste der neuen Vendoren erhalten, die sich über den Link des Vendors bei CopeCart registriert haben:
wenn bei uns ein Vendor meldet, weil er eine Benachrichtigung "WICHTIG! Wenn du eine Liste der neuen Vendoren erhalten möchtest, die sich über deinen Link bei CopeCart registriert haben, wende dich bitte an unser Support-Team. Dieses findest du in der Chatbox unten rechts, in welcher du eine Anfrage für den Export der Datensätze stellen kannst." in seinem Account erhalten hat, erstelle bitte keine JIRAs, sondern benutze gerne folgende Query, um eine csv-Datei im DBeaver erstellt bekommen zu können:
select email, full_name from users u where promoter_id = XXX
(XXX = User-ID)
21. Eine csv-datei von allen JVP Vendors erstellen:
select pr.slug, jv.created_at, jv.comission_percentage, jv.active from joint_venture_partnerships as jv
inner join products as pr on jv.product_id = pr.id where jv.vendor_id = XXX and jv.deleted_at is null and pr.deleted_at is null
(XXX = User-ID)
22. Eine csv-datei mit allen bezahlten Transaktionen (für bestimmten Zeitraum) erstellen:
select p."uuid" as transaction_id, p.amount, p.vat_amount, o."uuid" as order_id, bp.first_name, bp.last_name, bp.email, pr."name" from payments as p inner join orders as o on p.order_id = o.id inner join buyer_profiles as bp on o.buyer_profile_id = bp.id inner join products as pr on o.product_id = pr.id where p.vendor_id = XXX and p.updated_at > '2024-06-30' and p.state = 'paid'
(XXX = User-ID)
2024-06-30 das passende Datum angeben
23. Aktive Ratenzahlungen und Abos des Vendors checken:
select o."uuid" from orders as o inner join line_items as li on o.id = li.order_id left join product_subscriptions as ps on li.id = ps.line_item_id and o.payment_method != 'test' where o.vendor_id = xxxxx and ps.state = 'in_progress' AND (EXISTS(SELECT 1 FROM payments p WHERE p.order_id = o.id and amount IS NOT NULL))
(XXX = User-ID)
24. Eine Abfrage zur Bestimmung, wohin der physische Service (Sonstige Dienstleistung) verkauft wurde:
select distinct(pr.product_type), pr.fulfillment_location from payouts as p
inner join distributed_payouts as dp on dp.payout_id = p.id inner join payment_chunks as pc on dp.payment_chunk_id = pc.id inner join line_items as li on pc.line_item_id = li.id inner join products as pr on li.product_id = pr.id where sequence_number = 'XXX'
(XXX = PayOutsnummer/Payout-Referenznummer (auf der Rechnung links)
25. Prüfen, ob PayPal manuell ausgeschaltet wurde:
select "type" from feature_flags ff where user_id = XXXXX
(XXX = User-ID)
Hinweis:
Response von der IPN command, kann mit dem JSON Viewer gestackt werden, sodass es lesbarer ist: http://jsonviewer.stack.hu/
Konvertieren CSV in XLSX https://convertio.co/ru/csv-xlsx/