automatic price & stock updates from Pastel Partner to MySQL website?

[Salad_Dressing]

Thanks guys for the encouragement through your replies!

I'm currently looking at setting up an ODBC driver on the Pastel server, linking that to the database, and then performing ODBC queries from PHP.

Any input here regarding this?

I spoke to a Pastel reseller who recommended the Pastel E-Business module. But I'm guessing this is not essential.

[Salad_Dressing]

It appears that Pastel runs the Pervasive database, formerly Btrieve, as mentioned by AndyD :-)

Part of a Pastel database installation should therefore include Pervasive's ODBC driver (as referenced from this source).

Once the ODBC driver is configured on the Pastel server, a remote PHP script should be able to connect to it using standard ODBC functions and the proper credentials (server address, database name, username, password).

Does anyone have experience with setting up ODBC on a Pastel server?

[Neville Bailey]

Does anyone have experience with setting up ODBC on a Pastel server?

Aha! Now this is where I can help you at last!

At the server, you need to open the Pervasive Control Centre - type pcc in the Run box, or go to Start...All Programs...Pervasive...PSQL 10...Control Centre.

Click on New Database (see screenshot below).



Click Next.

Type in your database name (I used DEMO for illustrative purposes below), then click on the browse icon.


Browse to the location of your Pastel database folder.


Click on OK.

Click on Finish.


Your ODBC database has been created.

[Salad_Dressing]

Thanks Neville!

I've now set up a demo OCDB database via the Pervasive Control Center called "DEMOFORODBC".

Now I need to connect the database to an ODBC driver and obtain a port for remote access.

What I understand I should do is: Control Panel --> Administrative Tools --> Data Sources (ODBC) --> System DSN --> Add New --> but here all I have on the list is "SQL Server/6.01.7600.16385/Microsoft Corporation/SQLSRV32.DLL"

As I understand from this PDF tutorial on select.co.za I need to be able to select "Pervasive ODBC Engine Interface" and this is what I do not know how to install / setup / access.

Can anyone please help me with this?

[Salad_Dressing]

I have it solved!

The Pervasive manual mentioned the following:

ODBC Administrator on 64-bit Platforms
Windows 64-bit operating systems contain two different executable files for ODBC Administrator. If you want to add a system data source name (DSN) for a Pervasive Engine or Client driver, invoke the ODBC Administrator from Pervasive PSQL Control Center.
If you start ODBC Administrator from the Control Panel, the Pervasive drivers are not listed.

The above is exactly what was happening. SOLUTION: I opened the Pervasive Control Center and under the Tools menu opened "ODBC Administrator" and suddenly it was all there!

Now I need to know which port to use when remotely connecting to ODBC on the server...

[Salad_Dressing]

Right-clicking server properties when inside Pervasive Control Center brings up a list of properties of which "Communication Protocols" contains the port number.

However, when I click on Communication Protocols the panel with the settings is nearly blank because of some redraw problem. After clicking repeatedly on the property list, the page showed properly and I was able to see the port number.

Now I need to set up the router's port forwarding to achieve remote access and then start testing the setup via PHP ODBC queries.

[Dave A]

Sounds like you're making good progress.

[Salad_Dressing]

After some interruption, this project is moving forward again, hoping to complete it soon.

I'm successfully accessing Pastel Partner remotely via PHP script. I can query the tables, view their columns and rows, etc.

However, I'm not understanding how to access product price lists. I'm not a Pastel expert. All I understand is that Pastel installations have similar data structures.

Here's a list of tables which I can access, but none of them appear to be a price list when I query its contents:

AccountUser
ActivityMaster
AnalysisCodes
BankDetails
BICUsers
BillingParameters
BOMHeader
BOMLines
CashBookCurrency
Category1099
CompanyParameters
CostCodeBudgets
CostCodeLevelLinks
CostCodeLevelNames
CostCodeLevelSetup
CostCodes
CostCodeTransactions
CurrencyFile
CustomerCategories
CustomerDiscount
CustomerMaster
CustSuppParameters
DeliveryAddresses
EmployeeActivities
EmployeeMaster
Euro
FinancialCategories
Fonts
GAAP
HistoryHeader
HistoryLines
HistoryTax
HistoryUserDesc
HistoryUserSetup
InternetBank
InternetBenf
Inventory
InventoryCategory
InventoryGroups
InventoryLink
InventoryParameters
JumpUserAccess
LedgerBalances
LedgerMaster
LedgerParameters
LedgerTransactions
LinkHeader
LinkLines
MenuAccess
MultiStore
MultiStoreTrn
Notes
OpenItem
POSAudit
POSCashUp
POSPaidOuts
POSPayments
POSSetup
PreferredSupplier
ProcessOptions
ReceiptTransactions
ReportCategories
ReportExport
ReportWriter
SalesmanMaster
SerialMaster
SerialTransaction
SupplierCategories
SupplierMaster
SystemParameters
TaxBox
TaxDescription
TaxParameters
TimesheetHead
TimesheetLines
Unposted
UserGroups
UserStore

[Neville Bailey]

Selling prices are stored in the MultiStoreTrn data table, if I'm not mistaken.

[Salad_Dressing]

Selling prices are stored in the MultiStoreTrn data table, if I'm not mistaken.

Fantastic! Found the prices. Thanks Neville!

MultiStoreTrn turns out to be one of the few tables I had not yet searched in because the name just does not sound like it would hold item prices!

[Salad_Dressing]

I'm now also looking for the Pastel table where the stock on hand & stock available is stored.

Can anyone please help?

For those interested in this project: I've installed XAMPP onto the Pastel server, allowing me to remotely execute local PHP scripts which perform the queries and return the data to the web server via the HTML post feature, all via PHP. This technique reduces the query time from 9 minutes to 6 seconds. This makes it feasible to run fairly frequent price and stock update scripts.

[Neville Bailey]

I'm now also looking for the Pastel table where the stock on hand & stock available is stored.

You will find that in the MultiStoreTrn table as well, although you will need to do a calculation of a number of fields there in order to get the info you need.

Stock on Hand is OpeningQty, plus QtyBuyThis01 to QtyBuyThis12, plus QtyAdjustThis01 to QtyAdjustThis12, minus QtySellThis01 to QtySellThis12.

[Salad_Dressing]

You will find that in the MultiStoreTrn table as well, although you will need to do a calculation of a number of fields there in order to get the info you need.

Stock on Hand is OpeningQty, plus QtyBuyThis01 to QtyBuyThis12, plus QtyAdjustThis01 to QtyAdjustThis12, minus QtySellThis01 to QtySellThis12.

Thanks again Neville!

Honestly, without your inputs I'd be in the dark since none of this is obvious.

I'll run this calculation and have the Pastel lady check it.

[Salad_Dressing]

You will find that in the MultiStoreTrn table as well, although you will need to do a calculation of a number of fields there in order to get the info you need.

Stock on Hand is OpeningQty, plus QtyBuyThis01 to QtyBuyThis12, plus QtyAdjustThis01 to QtyAdjustThis12, minus QtySellThis01 to QtySellThis12.

I have played with the formula in all the ways I can think of, but nothing I have done so far matches the actual Stock-on-Hand reported in Pastel Partner.

Do QtyBuyThis01 to QtyBuyThis12 represent activity over the 12 months of the financial year?

If so, would the formula for Stock-on-Hand for the 4th month be: OpeningQty + QtyBuyThis04 + QtyAdjustThis04 - QtySellThis04

OR

Should the formula be inclusive of all 12 columns, something like this:

$tB = $qB01+$qB02+$qB03+$qB04+$qB05+$qB06+$qB07+$qB08+$q B09+$qB10+$qB11+$qB12; //QtyBuyThis
$tA = $qA01+$qA02+$qA03+$qA04+$qA05+$qA06+$qA07+$qA08+$q A09+$qA10+$qA11+$qA12; //QtyAdjustThis
$tS = -$qS01-$qS02-$qS03-$qS04-$qS05-$qS06-$qS07-$qS08-$qS09-$qS10-$qS11-$qS12; //QtySellThis
$qOH = $qO + $tB + $tA - $tS; //qO = OpeningQty

[Neville Bailey]

If so, would the formula for Stock-on-Hand for the 4th month be: OpeningQty + QtyBuyThis04 + QtyAdjustThis04 - QtySellThis04

No, it would be:

OpeningQty + QtyBuyLast + QtyBuyThis01 + QtyBuyThis02 + QtyBuyThis03 + QtyBuyThis04 + QtyAdjustLast + QtyAdjustThis01 + QtyAdjustThis02 + QtyAdjustThis03 + QtyAdjustThis04 - QtySellLast - QtySellThis01 - QtySellThis02 - QtySellThis03 - QtySellThis04

Sorry, in my previous post I forgot to mention that QtyBuyLast, QtyAdjustLast and QtySellLast must also be included. OpeningQty represents the quantity on hand at the beginning of the prior financial year.