Gebruiker:Cbruyndoncx

Jump to: navigation, search

Inhoud

[bewerken]

Personal Notepad on osFinancials

[bewerken]

Reporting

Old style reports are hardcoded/compiled into osFinancials; they are called 'QuickReports'. New style reports are editable by end users, they are making use of the open source ReportMan suite. Some New Style ReportMan reports give errors, to workaround this, it is possible to disable ReportMan through the 'Data Parameters' settings screen.

[bewerken]

QuickReports

QuickReports are still used for the following:

  • tbd
  • tbd

all others have been migrated to the ReportMan format, any bugs should be reported and fixed in future.

[bewerken]

Report Manager

[bewerken]

How does it work

Still a pretty big mistery.

[bewerken]

Directory Structure

There are different kinds of reports, each having their own directory structure, while there appear other directories being created that are not used at all (anymore ?)

  • Document layouts are stored under [osF]\plugins\reports\DOCUMENTS\DOCUMENTS
  • Built-in Reports
    • BTW not working in beta 12
    • Journaal not working in beta 12
  • User Reports
[bewerken]

Tricks

Naming reports using LANG_numberlangstring convention to localise the name of the report eg barcode.rep

[bewerken]

Why is the documentation included

Probably to ensure the ReportMan documentation that goes with the particular release is included, to avoid having mismatch between online (more recent) doc and actual version delivered with osFinancials. Note, in the GUI the help points to the online documentation which could become misleading.

[bewerken]

How do I find out which rep is used for which report ?

[bewerken]

Useful reports

Report Manager | Extra | Open items

[bewerken]

Bugs in reports

Several reports do not show the complete debtor/creditor code and grouping ends up aggregating several debtors/creditors I created a post in the forum, but have not yet logged this as bugs. If I figure out where they are exactly, I might be able to fix it myself.

[bewerken]

My Customizations

[bewerken]

Custom document layouts

[bewerken]

Active filter

Previously had additional filtering for the osCommerce interface to only take active products (products status update stock status now, and all products are kept)

[bewerken]

Ensure osC Specials in sync with osF

New style relies on additional customization in osF and osC. Old style scripts can be used to force a synch of specials pricing if they are out of sync.

[bewerken]

New Style

New style relies on additional customization of the ZQproducts.txt file in osFinancials and modifications in the standard osCommerce specials timestamping, in particular the use of specialsbycategory code.

[bewerken]

Old style manual sync script

Store special price in the price 1 field.

  1. run sql script on osC to generate update script for osF
  2. update stock set price 1 = price 2
  3. run generated update script on osF

note, 2 and 3 should be combined to avoid specials disappearing temporarily


  • IN OSF 
 update stock set fsellingprice1 = fsellingprice2;
    • Commit


  • IN OSC 
 SELECT concat( "update stock set fsellingprice1 = ", specials_new_products_price, " where sexternalid = '", products_id, "';"  ) 
 FROM  `specials` where status = 1;
    • Set toon op hoger getal en header repeat op 1000
    • Gebruik print preview om gehele sql statements te hebben en Copy


  • IN OSF
    • Paste sql statements
    • Execute
    • Commit
[bewerken]

ProductsId as Barcode

Store the osC productsid in the barcode field, thus having own (short) unique code. Numbers < ?100 are not used online, and used as shortcuts for generic stockgroups (where no individual product stock is tracked (yet))


Catch - barcodes with less than 4 digits won't scan. So need to run this little script to fix this 

update stock 
   set sbarcodenumber = '0' || sbarcodenumber 
 where sexternalid >= 100 and sexternalid < 1000 and sbarcodenumber not like '0%'


and create a trigger to keep things tidy 

ALTER TRIGGER KL_STOCK_SBARCODENUMBER ACTIVE
BEFORE INSERT OR UPDATE POSITION 0
AS 
BEGIN

/* enter trigger code here */ 

IF (new.sexternalid >= 100 and new.sexternalid < 1000 and new.sbarcodenumber not like '0%' ) THEN new.sbarcodenumber = '0' || new.sbarcodenumber ;
IF (new.sexternalid >= 10 and new.sexternalid < 100 and new.sbarcodenumber not like '0%' ) THEN new.sbarcodenumber = '00' || new.sbarcodenumber ;

END
[bewerken]

STOCK force partial sync

Created a dummy dochead entry with 0 wdocid and 0 wtypeid and 0 waccountid and 0 bposted

Create docline for each stock item having been updated since the last forced sync date 

delete from DOCLINE where wdocid = 0;

insert into DOCLINE (wdocid, wdoclineid, wstockid) 
select 0, wstockid, wstockid 
  from STOCK s, DOCHEAD dh 
 where s.dsysdate > dh.dsysdate 
   and dh.wdocid = 0;

update DOCHEAD set dsysdate = current_timestamp where wdocid = 0 ;

Ideally, this would only trigger the update for stock where the qty has been updated, but for now, this will do.

[bewerken]

Belgische BTW Rapportage

TIP - use this online tool to copy from excel and paste into wiki format http://people.fas.harvard.edu/~sdouglas/table.cgi


' ' VAK
II UITGAANDE HANDELINGEN
2ABijzondere regeling00
2BOmzet
Verkoop 6%01
Verkoop 12%02
Verkoop 21%03
2CVerkoop MC45
2DVerkoop EU46
2EVerkoop Export47
2FVerkoop KN EU48
Overige Verkoop KN (niet EU)49
III INKOMENDE HANDELINGEN
3AAankopen Fakturen
Handelsgoederen81
Diensten & Diverse goederen82
Bedrijfsmiddelen83
3BAankoop Creditnotas
Aankoop KN EU84
Overige Aankoop KN (niet EU)85
3CAankoop Fakt EU86
3DAankoop Fakt MC87
IV VERSCHULDIGDE BELASTING
4ABTW op handelingen in
rooster 01,02 en 03 - verkopen54
rooster 86 - AF EU55
rooster 87 - AF MC56
4Binvoer57
4Cdiverse ivv staat61
4Dteruggave btw AC63
65
totaal:XX
V AFTREKBARE BELASTING
5AAftrekbare BTW59
5Bdiverse voordel aangever62
5CVerkoop KN64
66
totaal:YY
Te betalen BTW71
Terug te trekken BTW72

The following sql scripts are used to list all relevant records which can be visually inspected, copy/pasted from flamerobin to excel and then further totalled.

[bewerken]

Uitgaande Handelingen

Note: these examples assume you are reporting on period 18,19 and 20 - you'll need to change this for your specific reporting period

  • VAK 01 - SALES subject to 6% sales tax 
select a.sdescription, dh.sdocno, dl.fexclusiveamt, dl.ftaxamount 
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, TAX t, PERIODS P
 where dh.waccountid = a.waccountid and dh.WTYPEID = 10 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wtaxid = t.waccountid 
   and t.frate = 6


  • VAK 02

We are not selling anything with 12% tax rate.

  • VAK 03 - SALES subject to 21% sales tax 
select a.sdescription, dh.sdocno, dl.fexclusiveamt, dl.ftaxamount 
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, TAX t, PERIODS P
 where dh.waccountid = a.waccountid and dh.WTYPEID = 10 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wtaxid = t.waccountid 
   and t.frate = 21


  • VAK 45

We are not selling anything under MedeContractant rules

  • VAK 46 - SALES Within EU without tax (i.e. to business customers with VAT number) 
select a.sdescription, dh.sdocno, dl.fexclusiveamt, dl.ftaxamount 
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, STOCK s, PERIODS P, TAX t, TAX tc
 where dh.waccountid = a.waccountid and dh.WTYPEID = 10 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid and t.frate = 100 and tc.frate = 100


  • VAK 47

We are not exporting any goods outside of the EU.

  • VAK 48 - SALES CreditNotes for EU (i.e. to business customers with VAT number)

Nothing to report as yet.

  • VAK 49

We are not exporting any goods outside of the EU, thus also no credit notes to report upon.

[bewerken]

INKOMENDE HANDELINGEN

  • VAK 81 Handelsgoederen

Details 

select a.sdescription, dh.sdocno, s.sstockcode, dl.fexclusiveamt, dl.ftaxamount, aka.saccountcode, vka.saccountcode
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, STOCK s, ACCOUNT aka, ACCOUNT vka, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and aka.saccountcode like '60%'	
 order by sdocno

Totals 

select aka.saccountcode, vka.saccountcode , sum(dl.fexclusiveamt) as VAK81, sum(dl.ftaxamount) deelVAK59
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and aka.saccountcode like '60%' 
 group by  aka.saccountcode, vka.saccountcode


  • VAK 82 Diensten & Diverse goederen

Details 

select a.sdescription, dh.sdocno, s.sstockcode, dl.fexclusiveamt, dl.ftaxamount, aka.saccountcode, vka.saccountcode  
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, STOCK s, ACCOUNT aka, ACCOUNT vka, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and aka.saccountcode like '6%' and aka.saccountcode not like '60%'

Totals 

select aka.saccountcode, vka.saccountcode , sum(dl.fexclusiveamt) as VAK82, sum(dl.ftaxamount) deelVAK59
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and aka.saccountcode like '6%' and aka.saccountcode not like '60%'
 group by  aka.saccountcode, vka.saccountcode


  • VAK 83 Bedrijfsmiddelen

Similar to VAK82, need to filter on any items booked to the asset accounts.


  • VAK 84 Aankoop KN EU

DETAILS ALLE KN EU 

select a.sdescription, dh.sdocno, s.sstockcode, dl.fexclusiveamt, dl.ftaxamount, dl.wtaxid, aka.saccountcode, vka.saccountcode  
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 13 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid

Totals 

select aka.saccountcode, vka.saccountcode , sum(dl.fexclusiveamt) as VAK84, sum(dl.ftaxamount) MoetNulZijn, sum(dl.fexclusiveamt*t.frate/100)
  from DOCHEAD dh, DOCLINE dl, STOCK s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, PERIODS p
 where dh.WTYPEID = 13 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid
 group by aka.saccountcode, vka.saccountcode


  • VAK 85 Overige Aankoop KN (niet EU)

DETAILS alle creditnota's 

select a.sdescription, dh.sdocno, s.sstockcode, dl.fexclusiveamt, dl.ftaxamount, dl.wtaxid, aka.saccountcode, vka.saccountcode  
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 13 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and dl.wtaxid = t.waccountid and t.blinkcontra = 0 and t.wlinktaxid = 0

TOTALS 

select aka.saccountcode, vka.saccountcode , sum(dl.fexclusiveamt) as VAK85, sum(dl.ftaxamount) deelVAK59
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 13 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and dl.wtaxid = t.waccountid and t.blinkcontra = 0 and t.wlinktaxid = 0
 group by  aka.saccountcode, vka.saccountcode


  • VAK 86 Aankoop Fakt EU

DETAILS ALLE AF EU 

 select a.sdescription, dh.sdocno, s.sstockcode, dl.fexclusiveamt, dl.ftaxamount, dl.wtaxid, aka.saccountcode, vka.saccountcode  
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, ACCOUNT ta, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid
   and t.waccountid = ta.waccountid and ta.sdescription not like '%Medecontractant%'

TOTAAL 

select aka.saccountcode, vka.saccountcode , sum(dl.fexclusiveamt) as VAK86, sum(dl.ftaxamount) MoetNulZijn, sum(dl.fexclusiveamt*t.frate/100)
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, ACCOUNT ta, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid
   and t.waccountid = ta.waccountid and ta.sdescription not like '%Medecontractant%'
 group by  aka.saccountcode, vka.saccountcode


  • VAK 87 Aankoop Fakt MC

DETAILS ALLE AF MC 

select a.sdescription, dh.sdocno, s.sstockcode, dl.fexclusiveamt, dl.ftaxamount, dl.wtaxid, aka.saccountcode, vka.saccountcode  
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, ACCOUNT ta, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid
   and t.waccountid = ta.waccountid and ta.sdescription like '%Medecontractant%'

TOTAAL 

select aka.saccountcode, vka.saccountcode , sum(dl.fexclusiveamt) as VAK87, sum(dl.ftaxamount) MoetNulZijn, sum(dl.fexclusiveamt*t.frate/100)
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, ACCOUNT ta, PERIODS p
 where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
   and (cast(dochead.ddate as date) >= cast(p.DSTARTDATE as date) 
   and cast(dochead.ddate as date) <= cast(p.DENDDATE as date))
   and p.wperiodid in (18,19,20)
   and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
   and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
   and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid
   and t.waccountid = ta.waccountid and ta.sdescription like '%Medecontractant%'
 group by  aka.saccountcode, vka.saccountcode
[bewerken]

Verschuldigde Belasting

4ABTW op handelingen in
rooster 01,02 en 03 - verkopenVAK54 =sum of Tax amount in totals for VAK01+VAK02+VAK03
rooster 86 - AF EUVAK55 =sum of Tax amount in totals for VAK86
rooster 87 - AF MCVAK56 =sum of Tax amount in totals for VAK87
4BinvoerVAK57 ---
4Cdiverse ivv staatVAK61 ---
4Dteruggave btw ACVAK63 =sum of Tax amount in totals for VAK85
VAK65 ---
totaal:XX =sum above
[bewerken]

Aftrekbare Belasting

5AAftrekbare BTWVAK59 =sum of Tax amount in totals for VAK81+82+83+86+87
5Bdiverse voordel aangeverVAK62 ---
5CVerkoop KNVAK64 --- (no credit notes thusfar)
66---
totaal:YY = sum above
Te betalen BTWVAK71 if XX>YY = XX-YY
Terug te trekken BTWVAK72 if YY>XX = YY-XX
[bewerken]

All-In-One

één SQL query voor de courante BTW vakken select 'VAK01' as VAK, sum(dl.fexclusiveamt) 

from ACCOUNT a, DOCHEAD dh, DOCLINE dl, TAX t, PERIODS P

where dh.waccountid = a.waccountid and dh.WTYPEID = 10 and dh.wdocid = dl.wdocid 

 and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
 and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
 and dl.wtaxid = t.waccountid 
 and t.frate = 6

union select 'VAK54' as VAK , sum(dl.ftaxamount) 

from ACCOUNT a, DOCHEAD dh, DOCLINE dl, TAX t, PERIODS P

where dh.waccountid = a.waccountid and dh.WTYPEID = 10 and dh.wdocid = dl.wdocid 

 and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
 and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
 and dl.wtaxid = t.waccountid 
 and t.frate = 6

UNION select 'VAK03' as VAK, sum(dl.fexclusiveamt) 

from ACCOUNT a, DOCHEAD dh, DOCLINE dl, TAX t, PERIODS P

where dh.waccountid = a.waccountid and dh.WTYPEID = 10 and dh.wdocid = dl.wdocid 

 and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
 and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
 and dl.wtaxid = t.waccountid 
 and t.frate = 21

union select 'VAK54' as VAK, sum(dl.ftaxamount) 

from ACCOUNT a, DOCHEAD dh, DOCLINE dl, TAX t, PERIODS P

where dh.waccountid = a.waccountid and dh.WTYPEID = 10 and dh.wdocid = dl.wdocid 

 and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
 and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
 and dl.wtaxid = t.waccountid 
 and t.frate = 21

UNION select 'VAK46' as VAK, SUM(dl.fexclusiveamt) 

from ACCOUNT a, DOCHEAD dh, DOCLINE dl, STOCK s, PERIODS P, TAX t, TAX tc

where dh.waccountid = a.waccountid and dh.WTYPEID = 10 and dh.wdocid = dl.wdocid 

 and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
 and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
 and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid 
 and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid and t.frate = 100 and tc.frate = 100

UNION select 'VAK81' as VAK, sum(dl.fexclusiveamt) 

 from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, PERIODS p
where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
  and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
  and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
  and aka.saccountcode like '60%'

UNION select 'VAK59' as VAK, sum(dl.ftaxamount) 

 from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, PERIODS p
where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
  and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
  and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
  and aka.saccountcode like '60%'

UNION select 'VAK82' as VAK, sum(dl.fexclusiveamt) 

 from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, PERIODS p
where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
  and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
  and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
  and aka.saccountcode like '6%' and aka.saccountcode not like '60%'

UNION select 'VAK59' as VAK, sum(dl.ftaxamount) 

 from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, PERIODS p
where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
  and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
  and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
  and aka.saccountcode like '6%' and aka.saccountcode not like '60%'

UNION select 'VAK84' as VAK, sum(dl.fexclusiveamt) from DOCHEAD dh, DOCLINE dl, STOCK s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, PERIODS p where dh.WTYPEID = 13 and dh.wdocid = dl.wdocid 

  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)

and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid UNION 

select 'VAK85' as VAK, sum(dl.fexclusiveamt) 
from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, PERIODS p
where dh.waccountid = a.waccountid and dh.WTYPEID = 13 and dh.wdocid = dl.wdocid 
  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
  and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
  and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
  and dl.wtaxid = t.waccountid and t.blinkcontra = 0 and t.wlinktaxid = 0

UNION select 'VAK63' as VAK, sum(dl.ftaxamount) 

from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, PERIODS p
where dh.waccountid = a.waccountid and dh.WTYPEID = 13 and dh.wdocid = dl.wdocid 
  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
  and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
  and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
  and dl.wtaxid = t.waccountid and t.blinkcontra = 0 and t.wlinktaxid = 0

UNION select 'VAK86' as VAK, sum(dl.fexclusiveamt) 

 from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, ACCOUNT ta, PERIODS p
where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
  and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
  and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
  and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid
  and t.waccountid = ta.waccountid and ta.sdescription not like '%Medecontractant%'

UNION select 'VAK55' as VAK, sum(dl.fexclusiveamt*t.frate/100) 

 from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, ACCOUNT ta, PERIODS p
where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
  and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
  and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
  and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid
  and t.waccountid = ta.waccountid and ta.sdescription not like '%Medecontractant%'

UNION select 'VAK87' as VAK, sum(dl.fexclusiveamt) 

 from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, ACCOUNT ta, PERIODS p
where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
  and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
  and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
  and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid
  and t.waccountid = ta.waccountid and ta.sdescription like '%Medecontractant%'

UNION select 'VAK56' as VAK, sum(dl.fexclusiveamt*t.frate/100) 

 from ACCOUNT a, DOCHEAD dh, DOCLINE dl, stock s, ACCOUNT aka, ACCOUNT vka, TAX t, TAX tc, ACCOUNT ta, PERIODS p
where dh.waccountid = a.waccountid and dh.WTYPEID = 12 and dh.wdocid = dl.wdocid 
  and (cast(dh.ddate as date) >= cast(p.DSTARTDATE as date) 
  and cast(dh.ddate as date) <= cast(p.DENDDATE as date))
 and p.wperiodid in (21,22,23)
  and dl.wlinetypeid <> 91 and dl.wstockid = s.wstockid
  and dl.wstockid = s.wstockid and s.wsalesaccountid = vka.waccountid and s.wcostaccountid = aka.waccountid
  and dl.wtaxid = t.waccountid and t.blinkcontra = 1 and t.wlinktaxid = tc.waccountid
  and t.waccountid = ta.waccountid and ta.sdescription like '%Medecontractant%'
[bewerken]

Other SQL Goodies

[bewerken]

BTW Klanten Listing

Klanten met BTW nr en omzet meer dan 250 euro. Listing dient elk jaar voor 31 maart ingediend te worden voor het voorgaande jaar. Deze sql geeft de nodige details (en iets meer context) 

select a.sdescription, d.sreference, sum(dl.fexclusiveamt) as BTWExcl, sum(dl.ftaxamount) as BTW
  from ACCOUNT a, DOCHEAD dh, DOCLINE dl, DEBTOR d
 where dh.waccountid = a.waccountid and a.waccountid = d.waccountid and dh.WTYPEID = 10 and dh.wdocid = dl.wdocid and d.sreference like 'BE%'
   and dh.ddate between '1 jan 2007' and '1 jan 2008'
 group by sdescription, sreference having sum(fexclusiveamt) > 250
[bewerken]

Date Only

where (cast(dochead.ddate as date) >= cast(periods.DSTARTDATE as date)
  and cast(dochead.ddate as date) <= cast(periods.DENDDATE as date))


[bewerken]

Summary of sales since 

select stock.sdescription, sum(docline.fexclusiveamt)
  from DOCLINE, STOCK , DOCHEAD
 where dochead.wdocid = docline.wdocid and docline.wstockid = stock.wstockid 
   and stock.sdescription like '%Materiaal%'
   and dochead.ddate > '1 jan 2007'
 group by stock.sdescription
[bewerken]

Issues/Bugs/Features

[bewerken]

Outstanding issues

[bewerken]

Category report groups are duplicated

check first for results Note, can't run this yet, not all reportgroup descriptions are unique (Andere is duplicated in different categories) 

select a.sDescription, b.wgroupid, max(a.WGroupid) from groups a,groups b, stock   
 where b.wgroupid =  stock.WreportingGroup1id 
   and a.SDescription = b.SDescription
 group by a.sdescription, b.wgroupid


(Scripts provided by Pieter)

[bewerken]

Merge category report groups

Makes the groups the same for the same description 

update stock 
   set WreportingGroup1id = ( select max(a.WGroupid) 
                                from groups a, groups b
                               where b.wgroupid =  stock.WreportingGroup1id 
                                 and a.SDescription = b.SDescription
                            );
[bewerken]

Cleanup script to run regularly

Deletes the groups not used (could be a problem with nested ones but there is no recurive selecting like in oracle with connect by) 

Delete from groups 
 where WGroupTypeId = 26 
   and WGroupID not in ( select WreportingGroup1id  
                           from stock 
                          where WreportingGroup1id = groups.WGroupID
                       );
[bewerken]

Remove all stock groups

Removes all the stock groups 

delete from groups 
 where WGroupTypeId = 26
[bewerken]

Remove all references to any stock group from the stock table

update stock set WreportingGroup1id = null;
[bewerken]

Subaccounts not properly flagged

[bewerken]

List subaccounts 

select p.saccountcode, p.sdescription, p.SMAINACCOUNTCODE, p.ssubaccountcode, p.BSUBACCOUNTS, c.saccountcode 
  from account p, account c
 where c.wlinkaccount = p.WACCOUNTID
   and c.wlinkaccount <> c.waccountid
 order by p.saccountcode, c.saccountcode

alternative way of listing 

select p.saccountcode, p.sdescription, p.BSUBACCOUNTS, c.saccountcode
 from account p, account c
where c.smainaccountcode = p.smainaccountcode
  and c.wlinkaccount <> c.waccountid
  and p.ssubaccountcode = '000'
order by p.saccountcode, c.saccountcode

find out missing links 

select p.saccountcode, p.sdescription, p.BSUBACCOUNTS, c.saccountcode
 from account p, account c
where c.smainaccountcode = p.smainaccountcode
  and c.wlinkaccount <> c.waccountid
  and c.wlinkaccount <> p.waccountid
  and p.ssubaccountcode = '000'
order by p.saccountcode, c.saccountcode
[bewerken]

List accounts with subaccounts missing bsubaccounts flag

select p.saccountcode, p.sdescription, p.SMAINACCOUNTCODE, p.ssubaccountcode, p.BSUBACCOUNTS, c.saccountcode 
  from account p, account c
 where c.wlinkaccount = p.WACCOUNTID
   and c.wlinkaccount <> c.waccountid
   and p.bsubaccounts = 0
 order by p.saccountcode, c.saccountcode
[bewerken]

Correct the missing bsubaccounts flag

update account p
   set p.bsubaccounts = 1 
 where p.waccountid in ( select c.wlinkaccount from account c WHERE c.wlinkaccount = p.WACCOUNTID
   and c.wlinkaccount <> c.waccountid
   and p.bsubaccounts = 0)
[bewerken]

Stock linked to accounts with subaccounts

select s.* from STOCK s, account a
 where s.wcostaccountid = a.waccountid
   and a.bsubaccounts = 1
UNION
select s.* from STOCK s, account a
 where s.wsalesaccountid = a.waccountid
   and a.bsubaccounts = 1
UNION
select s.* from STOCK s, account a
 where s.wcostaccountid = a.waccountid
   and a.bsubaccounts = 1
[bewerken]

Outstanding bugs

[bewerken]

Feature Requests

[bewerken]

To Log

[bewerken]

Reports

  • barcodes.rep
    • trim the sql statement to only retrieve the necessary fields (no outerjoins needed)
    • show tax inclusive (retail) price
    • show special price if available
  • Standard Reports
    • only show KvK label when there is a KvK number entered
[bewerken]

osCommerce interface

[bewerken]
ExternalIds
  • ExternalId is not used to identify products in the interface, thus if a products model is changed in osCommerce, 2 records are created with the same externalid, while actually the sstockcode should have been updated. Unclear how this affects the stock update functionality, might give some unwanted side-effects.

20070602 - This is updated in version 1.0.2.15 - not sure exactly how this is further used. Need to doublecheck

[bewerken]
Insert vs Update data descrepancies
  • Documentation/Dataflow: it is not clear what edits in osC actually are reflected in osF.

Revision 40 of the import code for editing : 

 648           if DMOSCommerceImport.OSCLinks.EditExistingItems then
 649            begin
 650                QStockItems.edit ;
 651 
 652                QStockItems.FieldByName('SEXTERNALID').AsString := ZQProducts.FieldByName('products_id').AsString ;
 653 
 654                if DMOSCommerceImport.AlternateWay = 0 then
 655                QStockItems.FieldByName('SDescription').AsString := ZQProducts.FieldByName('products_name').AsString
 656                else
 657                QStockItems.FieldByName('SDescription').AsString := ZQProducts.FieldByName('products_model').AsString;
 658                if DMOSCommerceImport.AlternateWay = 0 then
 659                AItem :=  OSCLinks.FindTaxLink(ZQProducts.FieldByName('products_tax_class_id').AsInteger,true)
 660                else
 661                AItem :=  OSCLinks.FindTaxLink(ZQProducts.FieldByName('products_ext_tax_id').AsInteger,true);
 662                if AItem <> nil then
 663                begin
 664                  QStockItems.FieldByName('WCostAccountID').AsInteger:= AItem.FCostOfSaleAccount ;
 665                  QStockItems.FieldByName('WInputTaxID').AsInteger := AItem.FTCTaxInput ;
 666                  QStockItems.FieldByName('WOutputTaxID').AsInteger := AItem.FTCTaxID ;
 667                  QStockItems.FieldByName('WSalesAccountID').AsInteger := AItem.FSalesAccount ;
 668                  QStockItems.FieldByName('WStockAccountID').AsInteger := AItem.FStockAccount ;
 669                  end;
 670                if DMOSCommerceImport.AlternateWay = 1 then
 671                begin
 672                  QStockItems.FieldByName('FSellingPrice2').AsFloat := ZQProducts.FieldByName('products_price').AsFloat / (1+ GetTaxRate(AItem.FTCTaxID)) ;
 673                end else
 674                begin
 675                  QStockItems.FieldByName('FSellingPrice2').AsFloat := ZQProducts.FieldByName('products_price').AsFloat  ;
 676                end;
 677 
 678                  QStockItems.FieldByName('WREPORTINGGROUP1ID').AsInteger := 0 ;
 679                  QStockItems.FieldByName('WREPORTINGGROUP2ID').AsInteger := 0 ;
 680                if OSCLinks.ImportStockGroups then
 681                     begin
 682                        FillCDSWithQuery(ZQCatPerProduct,format(SQLCatPerProduct.text+' and p.products_id = %s' ,[intToStr(OSCLinks.CBProductLang),IntToStr(ZQProducts.FieldByName('products_id').AsInteger)]));
 683                        if not ZQCatPerProduct.IsEmpty then
 684                          if ZQAllCatProducts.Locate('categories_id',ZQCatPerProduct.fieldbyname('categories_id').asinteger,[]) then
 685                            QStockItems.FieldByName('WREPORTINGGROUP1ID').AsInteger := ZQAllCatProducts.fieldbyname('TCGroupID').asinteger
 686                     end;
 687 
 688                QStockItems.Post ;

In summary, when editing existing products in osCommerce with our (pretty standard) settings, the following happens in osF

  • The product is identified by the products_model
  • The external id is always updated with the products_id
  • The description is updated with the products_name (we did not choose to stuff the products_model in there)
  • As long as there is a valid tax id given in osCommerce, products cost, sales, stock and tax accounts are updated (no specific tax setup for us)
  • Sellingprice2 is always updated with the products_price from osC
  • ReportingGroup1 and 2 are always reset (to 0)
  • Only ReportingGroup1 is updated with category tree

Revision 40 of the code for insert of new products 

 691          begin
 692            QStockItems.Insert ;
 693            QStockItems.FieldByName('SEXTERNALID').AsString := ZQProducts.FieldByName('products_id').AsString ;
 694            QStockItems.FieldByName('WStockID').AsInteger := DMTCCoreLink.GetNewId(tcidNEWSTOCKID) ;
 695            QStockItems.FieldByName('SStockCode').AsString := Code;
 696            if DMOSCommerceImport.AlternateWay = 0 then
 697            QStockItems.FieldByName('SDescription').AsString := ZQProducts.FieldByName('products_name').AsString
 698            else
 699            QStockItems.FieldByName('SDescription').AsString := ZQProducts.FieldByName('products_model').AsString;
 700 
 701            if QStockItems.FindField('SExtraDesc') <> nil then
 702               QStockItems.FindField('SExtraDesc').AsString := ZQProducts.FieldByName('products_name').AsString ;
 703 
 704 
 705            if ZQProducts.FindField('barcode') <> nil then
 706               QStockItems.FindField('SBARCODENUMBER').AsString := ZQProducts.FindField('barcode').AsString ;
 707 
 708 
 709            if DMOSCommerceImport.AlternateWay = 0 then
 710            AItem :=  OSCLinks.FindTaxLink(ZQProducts.FieldByName('products_tax_class_id').AsInteger,true)
 711            else
 712            AItem :=  OSCLinks.FindTaxLink(ZQProducts.FieldByName('products_ext_tax_id').AsInteger,true);
 713 
 714 
 715            if  AItem <> nil then
 716             begin
 717               QStockItems.FieldByName('WCostAccountID').AsInteger:= AItem.FCostOfSaleAccount ;
 718               QStockItems.FieldByName('WInputTaxID').AsInteger := AItem.FTCTaxInput ;
 719               QStockItems.FieldByName('WOutputTaxID').AsInteger := AItem.FTCTaxID ;
 720               QStockItems.FieldByName('WSalesAccountID').AsInteger := AItem.FSalesAccount ;
 721               QStockItems.FieldByName('WStockAccountID').AsInteger := AItem.FStockAccount ;
 722             end;
 723 
 724            QStockItems.FieldByName('FQtyOnHand').AsFloat := ZQProducts.FieldByName('products_quantity').AsInteger ;
 725            if DMOSCommerceImport.AlternateWay = -1 then
 726            QStockItems.FieldByName('FQtyOnHand').AsFloat := -1 ;
 727            QStockItems.FieldByName('FOpeningQty').AsFloat := ZQProducts.FieldByName('products_quantity').AsInteger ;
 728 
 729            if DMOSCommerceImport.AlternateWay = 1 then
 730            begin
 731            QStockItems.FieldByName('FSellingPrice1').AsFloat := ZQProducts.FieldByName('products_price').AsFloat / (1+ GetTaxRate(AItem.FTCTaxID)) ;
 732            QStockItems.FieldByName('FSellingPrice2').AsFloat := ZQProducts.FieldByName('products_price').AsFloat / (1+ GetTaxRate(AItem.FTCTaxID)) ;
 733            QStockItems.FieldByName('FSellingPrice3').AsFloat := ZQProducts.FieldByName('products_price').AsFloat / (1+ GetTaxRate(AItem.FTCTaxID)) ;
 734 
 735            end else
 736            begin
 737            QStockItems.FieldByName('FSellingPrice1').AsFloat := ZQProducts.FieldByName('products_price').AsFloat  ;
 738            QStockItems.FieldByName('FSellingPrice2').AsFloat := ZQProducts.FieldByName('products_price').AsFloat  ;
 739            QStockItems.FieldByName('FSellingPrice3').AsFloat := ZQProducts.FieldByName('products_price').AsFloat  ;
 740            end;
 741            QStockItems.FieldByName('WStockTypeID').AsInteger := 0;
 742            QStockItems.FieldByName('WREPORTINGGROUP1ID').AsInteger := 0 ;
 743            QStockItems.FieldByName('WREPORTINGGROUP2ID').AsInteger := 0 ;
 744            if OSCLinks.ImportStockGroups then
 745                 begin
 746                    FillCDSWithQuery(ZQCatPerProduct,format(SQLCatPerProduct.text+' and p.products_id = %s' ,[intToStr(OSCLinks.CBProductLang),IntToStr(ZQProducts.FieldByName('products_id').AsInteger)]));
 747                    if not ZQCatPerProduct.IsEmpty then
 748                      if ZQAllCatProducts.Locate('categories_id',ZQCatPerProduct.fieldbyname('categories_id').asinteger,[]) then
 749                        QStockItems.FieldByName('WREPORTINGGROUP1ID').AsInteger := ZQAllCatProducts.fieldbyname('TCGroupID').asinteger
 750                 end;
 751            QStockItems.Post ;
 752           end;

In summary, when adding new products in osCommerce with our (pretty standard) settings, the following happens in osF

  • The product is identified by the products_model
  • The external id is populated with the products_id
  • The description is populated with the products_name (we did not choose to stuff the products_model in there)
  • The extradescription is populated with the products_name (always)
  • The barcode is populated by the field in the oscommerce data selected as barcode (though unclear how ZQProducts.FindField vs FieldByName works)
  • As long as there is a valid tax id given in osCommerce, products cost, sales, stock and tax accounts are populated (no specific tax setup for us)
  • The quantityonhand is populated by the quantity from osC (the openingqty is not set here ?)
  • Sellingprice1, 2 and 3 are all populated with the products_price from osC
  • StockTypeId is always set to 0
  • ReportingGroup2 is always set to 0
  • ReportingGroup1 is populated with category tree

Q: Altnerateway checks for -1, 0 and 1 in different places in the code - what does this really mean, where is it set ???

Pieter : This is the Excl mode (the -1 was a error i saw that) it means the prices from osC are inclusive for osF this was a special request from a client

To facilitate proper catalog maintenance, we need to get rid of the discrepancies between insert and update, thus the following should be added to the edit functionality:

  • The extradescription is populated with the products_name (always)
  • The barcode is populated by the field in the oscommerce data selected as barcode (though unclear how ZQProducts.FindField vs FieldByName works)

Pieter : Findfield does not give a error if the field is not found but just returns nil FieldByName raises a error if the field is not found

  • Sellingprice1, 2 and 3 are all populated with the products_price from osC

Secondly to have the proper prices in osFinancials in sync with osCommerce, the following needs doing:

  • in osCommerce, whenever a special price is added, modified or removed, the products_date_modified needs to be updated
  • in osFinancials, don't assume the products_price field goes to all 3 fields, decide on a best practice which allows for mapping of different prices. In our case, sellingprice 2 is the regular price in the shop, sellingprice 1 is the special/promo price , while price 3 is not actively used. Thus the sql query needs to get the special price (or full price if there isn't a special) as sellingprice 1.

Pieter : Cant this be done by modifieing the current sql ? or is it to hard. Carine : Yes, the solution is in 2 parts: 1) modify the SQL to select specials_price as sellingprice1 2) osF also needs to map the sellingprice1 field in the dataset to the SellingPrice1 in osF. Currently on insert the products_price is used to populate all 3 and on update only updating SellingPrice2.

Carine: Drafted the code updates and added files to the feature requests http://sourceforge.net/tracker/index.php?func=detail&aid=1692119&group_id=169037&atid=848824

[bewerken]
Stock Update

As of 1.0.2 beta 24 full sync is re-enabled. No longer an issue.

Retrieved from "http://www.osfinancials.org/mediawiki/index.php/Gebruiker:Cbruyndoncx"
Personal tools