Εξαγωγή και αποστολή πωλήσεων στη 3ε

Για την αποστολή πωλήσεων στη 3ε απαιτείται η σύνδεση με το λογισμικό τους μέσω σύνδεσης ODBC.

Πρώτα από όλα εγκαθιστούμε τον ODBC driver, ακολουθώντας τις οδηγίες εδώ.

*Προσοχή υπάρχει περίπτωση να μην τρέχει σε 32bit και να πρέπει να ρυθμιστεί σε 64 bit
Στη συνέχεια πρέπει να δημιουργήσουμε τα παρακάτω views στη βάση δεδομένων:

Παραστατικά πωλήσεων

CREATE OR ALTER VIEW CC_DOCUMENT_HEADER(
    DOCUMENTID,
    REF_NUMBER,
    CREATIONDATE,
    MODIFIEDDATE,
    DISTRIBUTORAFM,
    GLN_RETAILER,
    AFM_RETAILER,
    NAME_RETAILER,
    ADDRESS_RETAILER,
    CITY_RETAILER,
    REGION_NAME_DELIVERY,
    ZIPCODE_RETAILER,
    MAIN_ACTIVITY,
    GLN_DELIVERYID,
    GLN_DELIVERY,
    ADDRESS_DELIVERY,
    CITY_DELIVERY,
    REGION_DELIVERY,
    ZIPCODE_DELIVERY,
    INV_TYPE,
    INV_SEC,
    INV_NUMBER,
    INV_DATE,
    CANCELATION,
    DOCUMENTID_CANCELING_INVOICE)
AS
select 
"pvlhseis"."Aa" DocumentID,

doc_abbreviation(eid."Parastatiko",coalesce( "pvlhseis"."Seira",'')||"Ariumospar") ref_number,
"pvlhseis"."Hmeromhnia" CreationDate,
"pvlhseis"."modified" ModifiedDate,
'000000000' DistributorAfm,
pel."Aa" GLN_retailer,
replace(pel."Afm",'EL','') afm_retailer,
coalesce(case when pel."Epvnymia"='' then null else pel."Epvnymia" end,coalesce(pel."Onoma"||' ','')||coalesce(pel."Epiueto",'')) name_retailer,
pel."Dieyuynsh" address_retailer,
pol."Onomasia" city_retailer,
pel."Perioxh" Region_Name_delivery,
pel."Tk" zipCode_retailer,
(select "epaggelmata"."Epaggelmata" from "epaggelmata" where "epaggelmata"."Aa"=pel."Epaggelma") Main_Activity,


case when "pvlhseis"."Paradosh" is null then "pvlhseis"."Kvdikospelath" else "pvlhseis"."Paradosh" end GLN_deliveryID,
case when "pvlhseis"."Paradosh" is null then pel."Aa" else (select "pelates"."Kvdikos" from "pelates" where "pelates"."Aa"="pvlhseis"."Paradosh") end GLN_delivery,
case when "pvlhseis"."Paradosh" is null then pel."Dieyuynsh" else (select "pelates"."Dieyuynsh" from "pelates" where "pelates"."Aa"="pvlhseis"."Paradosh") end address_delivery,


null city_delivery,
null region_delivery,
null zipCode_delivery,
eid."Parastatiko" inv_type,
"pvlhseis"."Seira" inv_sec,
"Ariumospar"  inv_number,
"pvlhseis"."Hmeromhnia" inv_date,
0 Cancelation,
null DocumentID_canceling_invoice

 
from "pvlhseis"
left join "pelates" as pel
on "pvlhseis"."Kvdikospelath"=pel."Aa"
left join "poleis" as pol
on pol."Aa"=pel."Polh"
left join "eidhpar" as eid
on eid."Aa"="pvlhseis"."Parastatiko"
 
where
("pvlhseis"."Ariumospar">-1)
and ((eid."Xrevstiko" in (1,6)  and eid."Emf_timvn"=1)
or (eid."Xrevstiko" in (0,5)  and eid."Emf_timvn"=2))


and exists (select first 1 apoi."Aa" from "apouhkh" apoi,"grammes" gri where apoi."Aa"=gri."Eidos" and apoi."custom1"='3E' and gri."Aapar"="pvlhseis"."Aa")
--and "pvlhseis"."Parastatiko"=6
--and    ("pvlhseis"."Hmeromhnia" >= '4/1/2022 00:00:00' and "pvlhseis"."Hmeromhnia" <= '4/30/2022 23:59:59')
;

CREATE OR ALTER VIEW CC_DOCUMENT_HEADER(

DOCUMENTID,

REF_NUMBER,

CREATIONDATE,

MODIFIEDDATE,

DISTRIBUTORAFM,

GLN_RETAILER,

AFM_RETAILER,

NAME_RETAILER,

ADDRESS_RETAILER,

CITY_RETAILER,

REGION_NAME_DELIVERY,

ZIPCODE_RETAILER,

MAIN_ACTIVITY,

GLN_DELIVERYID,

GLN_DELIVERY,

ADDRESS_DELIVERY,

CITY_DELIVERY,

REGION_DELIVERY,

ZIPCODE_DELIVERY,

INV_TYPE,

INV_SEC,

INV_NUMBER,

INV_DATE,

CANCELATION,

DOCUMENTID_CANCELING_INVOICE)

select

"pvlhseis"."Aa" DocumentID,

doc_abbreviation(eid."Parastatiko",coalesce( "pvlhseis"."Seira",'')||"Ariumospar") ref_number,

"pvlhseis"."Hmeromhnia" CreationDate,

"pvlhseis"."modified" ModifiedDate,

'000000000' DistributorAfm,

pel."Aa" GLN_retailer,

replace(pel."Afm",'EL','') afm_retailer,

coalesce(case when pel."Epvnymia"='' then null else pel."Epvnymia" end,coalesce(pel."Onoma"||' ','')||coalesce(pel."Epiueto",'')) name_retailer,

pel."Dieyuynsh" address_retailer,

pol."Onomasia" city_retailer,

pel."Perioxh" Region_Name_delivery,

pel."Tk" zipCode_retailer,

(select "epaggelmata"."Epaggelmata" from "epaggelmata" where "epaggelmata"."Aa"=pel."Epaggelma") Main_Activity,

case when "pvlhseis"."Paradosh" is null then "pvlhseis"."Kvdikospelath" else "pvlhseis"."Paradosh" end GLN_deliveryID,

case when "pvlhseis"."Paradosh" is null then pel."Aa" else (select "pelates"."Kvdikos" from "pelates" where "pelates"."Aa"="pvlhseis"."Paradosh") end GLN_delivery,

case when "pvlhseis"."Paradosh" is null then pel."Dieyuynsh" else (select "pelates"."Dieyuynsh" from "pelates" where "pelates"."Aa"="pvlhseis"."Paradosh") end address_delivery,

null city_delivery,

null region_delivery,

null zipCode_delivery,

eid."Parastatiko" inv_type,

"pvlhseis"."Seira" inv_sec,

"Ariumospar" inv_number,

"pvlhseis"."Hmeromhnia" inv_date,

0 Cancelation,

null DocumentID_canceling_invoice

from "pvlhseis"

left join "pelates" as pel

on "pvlhseis"."Kvdikospelath"=pel."Aa"

left join "poleis" as pol

on pol."Aa"=pel."Polh"

left join "eidhpar" as eid

on eid."Aa"="pvlhseis"."Parastatiko"

where

("pvlhseis"."Ariumospar">-1)

and ((eid."Xrevstiko" in (1,6) and eid."Emf_timvn"=1)

or (eid."Xrevstiko" in (0,5) and eid."Emf_timvn"=2))

and exists (select first 1 apoi."Aa" from "apouhkh" apoi,"grammes" gri where apoi."Aa"=gri."Eidos" and apoi."custom1"='3E' and gri."Aapar"="pvlhseis"."Aa")

--and "pvlhseis"."Parastatiko"=6

--and ("pvlhseis"."Hmeromhnia" >= '4/1/2022 00:00:00' and "pvlhseis"."Hmeromhnia" <= '4/30/2022 23:59:59')

;

Γραμμές παραστατικών πωλήσεων

CREATE OR ALTER VIEW CC_DOCUMENT_LINES(
    DOCUMENTID,
    LINEID,
    LINENUMBER,
    SUPPLIERAFM,
    PRODUCTID,
    PROD_DESCRIPTION,
    SKU,
    DELIVERY_MU,
    PRICE,
    DELIVERY_QTY,
    QTYBASEMU,
    NET_AMOUNT,
    ALLOW_PERCENT1,
    ALLOW_AMOUNT1,
    ALLOW_PERCENT2,
    ALLOW_AMOUNT2,
    ALLOW_PERCENT3,
    ALLOW_AMOUNT3,
    TOTAL_ALLOWS,
    ALLOWQTY,
    ALLOWCUSTOMER,
    ALLOWPAYMENTTYPE,
    TAXABLE_AMOUNT,
    VAT_PERCENT,
    VAT_AMOUNT,
    TOTALVALUE,
    CREATIONDATE,
    MODIFIEDDATE)
AS
select 

"pvlhseis"."Aa" DocumentID,
"grammes"."Aa" LineID,
ROW_NUMBER() OVER (PARTITION BY "Aapar" ORDER BY "Aapar") AS LineNumber,
null SupplierAfm,
"grammes"."Eidos" ProductID,
"grammes"."PerigrafhEidoys" prod_description,
"grammes"."KvdikosEidoys" sku,
(select "monades"."Monades" from "monades" where "monades"."Aa"="grammes"."Monada") delivery_mu,
(case eid."Emf_timvn" when 2 then -1 when 0 then 0 else 1 end)*"grammes"."Timh" price,
(case eid."Emf_timvn" when 2 then -1 else 1 end)*"Posothta" delivery_qty,
(case eid."Emf_timvn" when 2 then -1 else 1 end)*"Posothta" QtyBaseMU,
null net_amount,
null allow_percent1,
null allow_amount1,
null allow_percent2,
null allow_amount2,
null allow_percent3,
null allow_amount3,
null total_allows,
null AllowQty,
null AllowCustomer,
null AllowPaymentType,
(case eid."Emf_timvn" when 2 then -1 when 0 then 0 else 1 end)*"grammes"."Posothta"*("grammes"."Timh"-((cast("grammes"."Timh"*"grammes"."Ekptvsh" as double precision))/100)) taxable_amount,
round("grammes"."fpa_",2) vat_percent,
"grammes"."Timh"*"grammes"."fpa_"/100 vat_amount,
(case eid."Emf_timvn" when 2 then -1 when 0 then 0 else 1 end)*"grammes"."Posothta"*("grammes"."Timh"-((cast("grammes"."Timh"*"grammes"."Ekptvsh" as double precision))/100)+

(("grammes"."Timh"-((cast("grammes"."Timh"*"grammes"."Ekptvsh" as double precision))/100))* "grammes"."fpa_"  /100)

) TotalValue,

null CreationDate,
null ModifiedDate


from "grammes", "pvlhseis"

left join "pelates" as pel on "pvlhseis"."Kvdikospelath"=pel."Aa"
left join "poleis" as pol on pol."Aa"=pel."Polh"
left join "eidhpar" as eid on eid."Aa"="pvlhseis"."Parastatiko"
 
where
"grammes"."Aapar"="pvlhseis"."Aa"  and
("pvlhseis"."Ariumospar">-1)
and ((eid."Xrevstiko" in (1,6) and eid."Emf_timvn"=1)
or (eid."Xrevstiko" in (0,5) and eid."Emf_timvn"=2))

and exists (select first 1 apoi."Aa" from "apouhkh" apoi where apoi."Aa"="grammes"."Eidos" and apoi."custom1"='3E')
;

CREATE OR ALTER VIEW CC_DOCUMENT_LINES(

DOCUMENTID,

LINEID,

LINENUMBER,

SUPPLIERAFM,

PRODUCTID,

PROD_DESCRIPTION,

SKU,

DELIVERY_MU,

PRICE,

DELIVERY_QTY,

QTYBASEMU,

NET_AMOUNT,

ALLOW_PERCENT1,

ALLOW_AMOUNT1,

ALLOW_PERCENT2,

ALLOW_AMOUNT2,

ALLOW_PERCENT3,

ALLOW_AMOUNT3,

TOTAL_ALLOWS,

ALLOWQTY,

ALLOWCUSTOMER,

ALLOWPAYMENTTYPE,

TAXABLE_AMOUNT,

VAT_PERCENT,

VAT_AMOUNT,

TOTALVALUE,

CREATIONDATE,

MODIFIEDDATE)

select

"pvlhseis"."Aa" DocumentID,

"grammes"."Aa" LineID,

ROW_NUMBER() OVER (PARTITION BY "Aapar" ORDER BY "Aapar") AS LineNumber,

null SupplierAfm,

"grammes"."Eidos" ProductID,

"grammes"."PerigrafhEidoys" prod_description,

"grammes"."KvdikosEidoys" sku,

(select "monades"."Monades" from "monades" where "monades"."Aa"="grammes"."Monada") delivery_mu,

(case eid."Emf_timvn" when 2 then -1 when 0 then 0 else 1 end)*"grammes"."Timh" price,

(case eid."Emf_timvn" when 2 then -1 else 1 end)*"Posothta" delivery_qty,

(case eid."Emf_timvn" when 2 then -1 else 1 end)*"Posothta" QtyBaseMU,

null net_amount,

null allow_percent1,

null allow_amount1,

null allow_percent2,

null allow_amount2,

null allow_percent3,

null allow_amount3,

null total_allows,

null AllowQty,

null AllowCustomer,

null AllowPaymentType,

(case eid."Emf_timvn" when 2 then -1 when 0 then 0 else 1 end)*"grammes"."Posothta"*("grammes"."Timh"-((cast("grammes"."Timh"*"grammes"."Ekptvsh" as double precision))/100)) taxable_amount,

round("grammes"."fpa_",2) vat_percent,

"grammes"."Timh"*"grammes"."fpa_"/100 vat_amount,

(case eid."Emf_timvn" when 2 then -1 when 0 then 0 else 1 end)*"grammes"."Posothta"*("grammes"."Timh"-((cast("grammes"."Timh"*"grammes"."Ekptvsh" as double precision))/100)+

(("grammes"."Timh"-((cast("grammes"."Timh"*"grammes"."Ekptvsh" as double precision))/100))* "grammes"."fpa_" /100)

) TotalValue,

null CreationDate,

null ModifiedDate

from "grammes", "pvlhseis"

left join "pelates" as pel on "pvlhseis"."Kvdikospelath"=pel."Aa"

left join "poleis" as pol on pol."Aa"=pel."Polh"

left join "eidhpar" as eid on eid."Aa"="pvlhseis"."Parastatiko"

where

"grammes"."Aapar"="pvlhseis"."Aa" and

("pvlhseis"."Ariumospar">-1)

and ((eid."Xrevstiko" in (1,6) and eid."Emf_timvn"=1)

or (eid."Xrevstiko" in (0,5) and eid."Emf_timvn"=2))

and exists (select first 1 apoi."Aa" from "apouhkh" apoi where apoi."Aa"="grammes"."Eidos" and apoi."custom1"='3E')

;

Είδη

CREATE OR ALTER VIEW CC_PRODUCTS(
    PRODUCTID,
    SUPPLIERAFM,
    CREATIONDATE,
    MODIFIEDDATE,
    DISTRIBUTORAFM,
    SKU,
    PROD_DESCRIPTION,
    DELIVERY_MU,
    DELIVERY_MU2,
    DELIVERY_CAPACITY,
    SUPPLIER_SKU,
    EAN_CU)
AS
select
"grammes"."Eidos" ProductID,
null SupplierAfm,
(select "apouhkh"."Hmeromhnia" from "apouhkh" where "apouhkh"."Aa"="grammes"."Eidos") CreationDate,
(select "apouhkh"."modified" from "apouhkh" where "apouhkh"."Aa"="grammes"."Eidos") ModifiedDate,
'0000000' DistributorAfm,
max("grammes"."KvdikosEidoys") sku,
max("grammes"."PerigrafhEidoys") prod_description,
(select "monades"."Monades" from "monades" where "monades"."Aa"=max("grammes"."Monada")) delivery_mu,
null delivery_mu2,
null delivery_capacity,
null supplier_sku,
null EAN_cu


from "grammes", "pvlhseis"

left join "pelates" as pel on "pvlhseis"."Kvdikospelath"=pel."Aa"
left join "poleis" as pol on pol."Aa"=pel."Polh"
left join "eidhpar" as eid on eid."Aa"="pvlhseis"."Parastatiko"
 
where
"grammes"."Eidos" is not null and
"grammes"."Aapar"="pvlhseis"."Aa"  and
("pvlhseis"."Ariumospar">-1)
and ((eid."Xrevstiko" in (1,6) and eid."Emf_timvn"=1)
or (eid."Xrevstiko" in (0,5) and eid."Emf_timvn"=2))


and exists (select first 1 apoi."Aa" from "apouhkh" apoi where apoi."Aa"="grammes"."Eidos" and apoi."custom1"='3E')
group by "grammes"."Eidos"
;

CREATE OR ALTER VIEW CC_PRODUCTS(

PRODUCTID,

SUPPLIERAFM,

CREATIONDATE,

MODIFIEDDATE,

DISTRIBUTORAFM,

SKU,

PROD_DESCRIPTION,

DELIVERY_MU,

DELIVERY_MU2,

DELIVERY_CAPACITY,

SUPPLIER_SKU,

EAN_CU)

select

"grammes"."Eidos" ProductID,

null SupplierAfm,

(select "apouhkh"."Hmeromhnia" from "apouhkh" where "apouhkh"."Aa"="grammes"."Eidos") CreationDate,

(select "apouhkh"."modified" from "apouhkh" where "apouhkh"."Aa"="grammes"."Eidos") ModifiedDate,

'0000000' DistributorAfm,

max("grammes"."KvdikosEidoys") sku,

max("grammes"."PerigrafhEidoys") prod_description,

(select "monades"."Monades" from "monades" where "monades"."Aa"=max("grammes"."Monada")) delivery_mu,

null delivery_mu2,

null delivery_capacity,

null supplier_sku,

null EAN_cu

from "grammes", "pvlhseis"

left join "pelates" as pel on "pvlhseis"."Kvdikospelath"=pel."Aa"

left join "poleis" as pol on pol."Aa"=pel."Polh"

left join "eidhpar" as eid on eid."Aa"="pvlhseis"."Parastatiko"

where

"grammes"."Eidos" is not null and

"grammes"."Aapar"="pvlhseis"."Aa" and

("pvlhseis"."Ariumospar">-1)

and ((eid."Xrevstiko" in (1,6) and eid."Emf_timvn"=1)

or (eid."Xrevstiko" in (0,5) and eid."Emf_timvn"=2))

and exists (select first 1 apoi."Aa" from "apouhkh" apoi where apoi."Aa"="grammes"."Eidos" and apoi."custom1"='3E')

group by "grammes"."Eidos"

;

Πελάτες

CREATE OR ALTER VIEW CC_CUSTOMERS(
    CUSTOMERID,
    CREATIONDATE,
    MODIFIEDDATE,
    DISTRIBUTORAFM,
    GLN_RETAILER,
    AFM_RETAILER,
    NAME_RETAILER,
    ADDRESS_RETAILER,
    CITY_RETAILER,
    REGION_NAME_DELIVERY,
    ZIPCODE_RETAILER,
    MAIN_ACTIVITY,
    RECCATEGORY1,
    RECCATEGORY2,
    RECCATEGORY3)
AS
select

pel."Aa" CustomerID,
pel."Hmeromhnia" CreationDate,
pel."modified" ModifiedDate,
'000000000' DistributorAfm,
pel."Aa" GLN_retailer,
replace(pel."Afm",'EL','') afm_retailer,
coalesce(case when pel."Epvnymia"='' then null else pel."Epvnymia" end,coalesce(pel."Onoma"||' ','')||coalesce(pel."Epiueto",'')) name_retailer,
pel."Dieyuynsh" address_retailer,
pol."Onomasia" city_retailer,
pel."Perioxh" Region_Name_delivery,
pel."Tk" zipCode_retailer,
(select "epaggelmata"."Epaggelmata" from "epaggelmata" where "epaggelmata"."Aa"=pel."Epaggelma") Main_Activity,
null recCategory1,
null recCategory2,
null recCategory3

from "pvlhseis"
left join "pelates" as pel
on "pvlhseis"."Kvdikospelath"=pel."Aa"
left join "poleis" as pol
on pol."Aa"=pel."Polh"
left join "eidhpar" as eid
on eid."Aa"="pvlhseis"."Parastatiko"
 
where
("pvlhseis"."Ariumospar">-1)
and ((eid."Xrevstiko" in (1,6)  and eid."Emf_timvn"=1)
or (eid."Xrevstiko" in (0,5)  and eid."Emf_timvn"=2))

and exists (select first 1 apoi."Aa" from "apouhkh" apoi,"grammes" gri where apoi."Aa"=gri."Eidos" and apoi."custom1"='3E' and gri."Aapar"="pvlhseis"."Aa")

group by pel."Aa",pel."Hmeromhnia",pel."modified",pel."Afm",pel."Epvnymia",pel."Onoma",pel."Epiueto",pel."Dieyuynsh",pol."Onomasia",pel."Perioxh",pel."Tk",pel."Epaggelma",pel."Kvdikos"
;

CREATE OR ALTER VIEW CC_CUSTOMERS(

CUSTOMERID,

CREATIONDATE,

MODIFIEDDATE,

DISTRIBUTORAFM,

GLN_RETAILER,

AFM_RETAILER,

NAME_RETAILER,

ADDRESS_RETAILER,

CITY_RETAILER,

REGION_NAME_DELIVERY,

ZIPCODE_RETAILER,

MAIN_ACTIVITY,

RECCATEGORY1,

RECCATEGORY2,

RECCATEGORY3)

select

pel."Aa" CustomerID,

pel."Hmeromhnia" CreationDate,

pel."modified" ModifiedDate,

'000000000' DistributorAfm,

pel."Aa" GLN_retailer,

replace(pel."Afm",'EL','') afm_retailer,

coalesce(case when pel."Epvnymia"='' then null else pel."Epvnymia" end,coalesce(pel."Onoma"||' ','')||coalesce(pel."Epiueto",'')) name_retailer,

pel."Dieyuynsh" address_retailer,

pol."Onomasia" city_retailer,

pel."Perioxh" Region_Name_delivery,

pel."Tk" zipCode_retailer,

(select "epaggelmata"."Epaggelmata" from "epaggelmata" where "epaggelmata"."Aa"=pel."Epaggelma") Main_Activity,

null recCategory1,

null recCategory2,

null recCategory3

from "pvlhseis"

left join "pelates" as pel

on "pvlhseis"."Kvdikospelath"=pel."Aa"

left join "poleis" as pol

on pol."Aa"=pel."Polh"

left join "eidhpar" as eid

on eid."Aa"="pvlhseis"."Parastatiko"

where

("pvlhseis"."Ariumospar">-1)

and ((eid."Xrevstiko" in (1,6) and eid."Emf_timvn"=1)

or (eid."Xrevstiko" in (0,5) and eid."Emf_timvn"=2))

and exists (select first 1 apoi."Aa" from "apouhkh" apoi,"grammes" gri where apoi."Aa"=gri."Eidos" and apoi."custom1"='3E' and gri."Aapar"="pvlhseis"."Aa")

group by pel."Aa",pel."Hmeromhnia",pel."modified",pel."Afm",pel."Epvnymia",pel."Onoma",pel."Epiueto",pel."Dieyuynsh",pol."Onomasia",pel."Perioxh",pel."Tk",pel."Epaggelma",pel."Kvdikos"

;

Τα αποτελέσματα αποτελλούνται από παραστατικά που περιλαμβάνουν είδη που στο προσαρμοσμένο πεδίο 1 έχουν τη φράση “3E”.

Για να ελέγξουμε ότι δουλεύει σωστά μπορούμε να κατεβάσουμε το libre office και να δούμε αν συνδέεται με το libre base.Παρακάτω οι ρυθμίσεις σε libre base .