Skip to content

Instantly share code, notes, and snippets.

@rainbat

rainbat/odbc.php

Last active Mar 24, 2021
Embed
What would you like to do?
<?php
/*
-download php (7.4 zip):
https://windows.php.net/download/
unzip in subfolder, rename folder to php:
- rename php.ini-development.ini to php.ini
- uncomment extension dir for windows in php.ini
- enable curl, fileinfo, gd2, mbstring, odbc, openssl extensions by uncommenting in php.ini
- get php composer:
https://getcomposer.org/download/
composer.json
{
"require": {
"phpoffice/phpspreadsheet": "^1.17"
}
}
- install composer dependencies
\php\php.exe composer.phar install
make a template.xlsx with the headers of your sql statements
*/
require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use \PhpOffice\PhpSpreadsheet\IOFactory;
writeExcelFile();
updateWooWithExcel();
function updateWooWithExcel(){
$objPHPExcel = IOFactory::load( "artikel.xlsx" );
$row = 1;
do{
$row++;
$sku = $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow( 1, $row) ;
$stock_quantity = $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow( 2, $row );
echo $sku . " " . $stock_quantity . "\n";
if( $sku != "" && $stock_quantity != "" ) updateWooCommerceBySKU( $sku, $stock_quantity);
if( $sku == "") break;
} while ( $sku != "" );
}
function writeExcelFile(){
//$archivdate = date("Y_m_d_H_i_s");
//if( file_exists("artikel.xlsx") ) copy("artikel.xlsx","$archivdate_artikel.xlsx");
//$objPHPExcel = IOFactory::load( "template.xlsx" );
$objPHPExcel = new Spreadsheet();
$res = doSQL("select top 100 * from SL_vGetFreierArtikelBestand");
$head_row = $res[0];
var_dump( $head_row );
$col_index = 0;
foreach($head_row as $key => $val){
$col_index++;
$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow( $col_index, 1, $key );
}
$row_index = 1;
foreach($res as $row){
$row_index++;
$col_index = 0;
foreach($row as $key => $val){
$col_index++;
$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow( $col_index, $row_index, $val );
}
}
$objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($objPHPExcel);
$objWriter->save("artikel.xlsx");
}
// more attributes available check
// https://woocommerce.github.io/woocommerce-rest-api-docs/#retrieve-a-product
function updateWooCommerceBySKU( $sku, $stock_quantity ){
$ch = curl_init();
//curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
curl_setopt($ch, CURLOPT_USERPWD, "woo_client" . ":" . "woo_secret" );
curl_setopt($ch, CURLOPT_URL, "https://www.domain.ch/wp-json/wc/v3/products/?sku=$sku");
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json'] );
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
//curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'OPTIONS');
$response = curl_exec($ch);
$responseCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
$response_json = json_decode( $response );
//var_dump( $response_json ); die();
if( count($response_json) > 0) {
$product_id = utf8_decode( $response_json[0]->id );
echo "... product found updating \n";
}else{
$product_id = "";
echo "... product not found skipping \n";
}
curl_close($ch);
if( $product_id != "" ) {
$payload = json_encode([
"stock_quantity" => $stock_quantity
]);
$ch = curl_init();
curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
curl_setopt($ch, CURLOPT_USERPWD, "woo_client" . ":" . "woo_secret" );
curl_setopt($ch, CURLOPT_URL, "https://www.domain.ch/wp-json/wc/v3/products/$product_id" );
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json'] );
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
//curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'OPTIONS');
$response = curl_exec($ch);
$responseCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
$response_json = json_decode($response);
curl_close($ch);
return "updated product";
}else{
return "no product";
}
}
function doSQL( $sql) {
$result = null;
$data = null;
$conn = odbc_connect(
'selectline', //ODBC Name
'user', //Don't use the Super Admin User please :)
'pw' );
$result = odbc_exec($conn, $sql);
$data = array();
while ($row = odbc_fetch_array($result)) {
foreach ($row as $key => $val) {
$row[$key] = utf8_encode($val);
}
$data[] = (array)$row;
}
return $data;
}
function doSQLValue($id, $sql) {
$data = doSQL($id, $sql);
if (isset($data[0]['VAL'])) {
return $data[0]['VAL'];
}
return $data[0]['val'];
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment