0 - maanie //20180619 - createUser(): set strSetting:Language field - maanie //20180816 - UTF-8 checks - pj //20180823 - UTF-8 checks - maanie //20180823 - update updateUser().strUser - maanie //20180823 - setPassword(): Changed password warning message - maanie //20180926 - renamed field to encArgs - maanie //20180927 - cleanVendorRST:: Inverted to include fields as opposed to excluding them - maanie //HDP - 23/10/2018 - Add new fields [20181019 - WOCode Change vc11 >> vc21 - pj [201810.17 - WINE OF ORIGIN (WO) CODE change]] //20181106 - v1.7 - LMS v1.2: getMemberProfileLMS(MemberID) - maanie //20181205 - getMemberProfile(): added MR.Status for WIETA only - maanie //20181211 - getWS(): added tblHarvestItem.refMemberID and tblMember.strMember - maanie //20190118 - v1.6 - WMS API - maanie //20190118 - v1.6 - WMS API: getWineMSFarmList - maanie //20190121 - v1.6 - WMS API: getWineMSBlockGUID - maanie //20190121 - v1.6 - WMS API: setWineMSBlockGUID - maanie //20190124 - v1.6 - WMS API: getWineMSMemberList - maanie //20190125 - v1.6 - WMS API: getWineMSFarm - maanie //20190129 - v1.6 - WMS API: getWineMSMember - maanie //20190201 - v1.6 - WMS API: getWineMSSawis1 - maanie //20190201 - v1.6 - WMS API: setWineMSCellar - maanie //20190205 - v1.6 - WMS API: setWineMSBlockActivity - maanie //20190408 - added #% to password check - maanie //20190613 - v1.0 - CIS API: getCGABlockList - maanie //20200113 - setCertificationResultsRecord() - changed to set TransactionID from xml - maanie //20190219 - getMemberProfileLMS: reworked the limit for this call: made it a tenth of the normal limit - maanie //20190221 - v1.7 - LMS v1.2: getMemberProfileLMS(MemberID) - reworked queries - maanie //20200122 dont unset ID if it has not been set, we get the id from the transaction ID -jacques //20200304 - LMS: getMemberIDListLMS() - maanie /*INSERT INTO `apiVendorField`(`VendorKey`, `strCall`, `strField`, `strLastUser`) SELECT 'WOLINE', `strCall`, `strField`, `strLastUser` FROM `apiVendorField` WHERE Vendorkey = 'WIETA1' AND strCall = 'getMemberList' AND strlastUser = 'INCLUDE'*/ /*INSERT INTO `apiVendorField` (`ID`, `VendorKey`, `strCall`, `strField`, `strLastUser`, `dtLastEdit`) VALUES (NULL, 'WIETA1', 'getWS', 'refMemberID', 'INCLUDE', CURRENT_TIMESTAMP), (NULL, 'WIETA1', 'getWS', 'strMember', 'INCLUDE', CURRENT_TIMESTAMP);*/ //20200330 - getCGABlockList Add farm name to the results - jacques include_once("Rest.inc.php"); include_once("_framework/_nemo.password.cls.php"); class NemoAPI extends REST { //error messages & recordings public static $INVALID_CALL = "INVALID CALL"; public static $INVALID_VENDOR_KEY = "INVALID VENDOR KEY"; public static $INVALID_SESSION_TOKEN = "INVALID TOKEN"; public static $AUTHENTICATION_FAILED = "AUTHENTICATION FAILED"; //public static $INVALID_MEMBER = "INVALID MEMBER DETAILS"; //public static $INVALID_FARM_MEMBER = "FARM NOT LINKED TO THIS MEMBER"; //public static $INVALID_VENDOR_MEMBER = "MEMBER NOT LINKED TO THIS VENDOR"; //public static $NO_FARM_BLOCKS = "NO VINYARD BLOCKS LINKED TO THIS FARM"; //public static $NO_MEMBER_FARMS = "NO FARMS LINKED TO THIS MEMBER"; //public static $INVALID_FIELD = "INVALID FIELD SUPPLIED"; public static $VALID_CALL = "Great Success!"; public static $HTTP_ERROR_CODE = "202"; //20160512 public static $INVALID_STRUCTURE = "INVALID DATA STRUCTURE"; private $VendorKey = null; private $blnVendorKeyCheck = false; private $blnIgnoreVendorKey = false; private $VendorSite = ""; private $Call = null; private $blnMethodExist = false; private $SessionToken = null; private $SessionData = null; private $blnIgnoreSessionToken = false; private $blnIgnorePassword = false; private $ReturnType = "xml"; private $xmlData = ""; // how long per hour must a session last private $SessionExpire = "3 DAY"; //"30 MINUTE"; CHANGE BACK AFTER TESTING!!!!! private $pagination = array(); public $args; private $Page = null; private $limit = ""; // 20160621 - Implemented paging variable with all other get calls + added new call (getListPages([call]) )- maanie private $arrCalls = array("getCultivarList" , "getWineTypeList" , "getIrrigationList" , "getTrellisList" , "getLocationList" , "getMemberTypeList" , "getMemberList" , "getFarmList" , "getVineyardBlockList" , "getMemberAddressList" , "getMemberContactList" , "getUser" , "getPackingMaterialList" //20170322 - v1.3 added getPackingMaterialList() - maanie , "getIncotermList" //20170323 - v1.3 added getIncotermList() - maanie , "getCountryCodesList" //20170324 - v1.3 added getCountryCodesList() - maanie , "getBg9_11_TypeList" //20170327 - v1.3 added getBg9_11_TypeList() - maanie , "getCertificateTransationCodeList" //20170327 - v1.3 added getCertificateTransationCodeList() - maanie , "getRejectCodesList" //20170327 - v1.3 added getRejectCodesList() - maanie , "getPackingCodesList" //20170328 - v1.3 added getPackingCodesList() - maanie , "getCertificateStatusList" //20170328 - v1.3 added getCertificateStatusList() - maanie , "getCertificateResultsList" //20170328 - v1.3 added getCertificateResultsList() - maanie , "getPackVolumeColourList" //20170329 - v1.3 added getPackVolumeColourList() - maanie , "getPackVolumeClosureTypeList" //20170329 - v1.3 added getPackVolumeClosureTypeList() - maanie , "getBottleDescriptionList" //20170330 - v1.3 added getBottleDescriptionList() - maanie , "getManufacturerList" //20170330 - v1.3 added getManufacturerList() - maanie , "getCertificationList" //20170408 - v1.3 added getCertificationList() - maanie , "getCertificationResultsList" //20170412 - v1.3 added getCertificationResultsList() - maanie , "getTitleDeedsList" //20170516 - v1.3 implemented getTitleDeedsList() - maanie , "getMagisterialDistrictList" //20170526 - v1.3 implemented getMagisterialDistrictList() - maanie , "getWIETAFarmList" //20170929 - Added new call getWIETAFarmList() - maanie , "getWietaMembershipList" //20170929 - Added new call getWietaMembershipList() - maanie , "getBlendMaster" //20180306 - v1.3 - added getBlendMaster() - maanie , "getWSB" //20180308 - v1.3 - added getWSB() - maanie , "getWS" //20180309 - v1.3 - added getWS() - maanie , "getMemberProfileLMS"//20181106 - v1.7 - LMS v1.2: getMemberProfileLMS(MemberID) - maanie , "getMemberIDListLMS" //20200304 - LMS: getMemberIDListLMS() - maanie , "getWineMSFarmList" //20190118 - v1.6 - WMS API: getWineMSFarmList - maanie , "getWineMSBlockGUID" //20190121 - v1.6 - WMS API: getWineMSBlockGUID - maanie , "getWineMSMemberList" //20190124 - v1.6 - WMS API: getWineMSMemberList - maanie , "getWineMSFarm" //20190125 - v1.6 - WMS API: getWineMSFarm - maanie , "getWineMSMember" //20190129 - v1.6 - WMS API: getWineMSMember - maanie , "getWineMSSawis1" //20190201 - v1.6 - WMS API: getWineMSSawis1 - maanie , "getCGABlockList" //20190613 - v1.0 - CIS API: getCGABlockList - maanie , "authenticate"); private $arrCallFROM = array("getCultivarList" => "FROM tblCultivar WHERE 1=1" , "getWineTypeList" => "FROM tblWineType WHERE 1=1" , "getIrrigationList" => "FROM tblIrrigation WHERE 1=1" , "getTrellisList" => "FROM tblTrellis WHERE 1=1" , "getLocationList" => "FROM dwdLocation WHERE 1=1" , "getMemberTypeList" => "FROM tblMemberType WHERE 1=1" , "getMemberList" => "FROM tblMember INNER JOIN tblMemberType ON refMemberTypeID = MemberTypeID WHERE 1=1" , "getFarmList" => "FROM tblFarm INNER JOIN ( SELECT tblMemberRelationship.refEntityID AS refFarmID, tblMemberRelationship.refMemberID, Max(tblMemberRelationship.refMemberTypeID) AS refMemberTypeID FROM tblMemberRelationship INNER JOIN tblMemberType ON tblMemberRelationship.refMemberTypeID = tblMemberType.MemberTypeID WHERE (((tblMemberRelationship.strType)='Farm to Member') AND ((tblMemberType.strMemberTypeCode) In ('FRM000','FRMEST','FRMOWN'))) GROUP BY tblMemberRelationship.refEntityID, tblMemberRelationship.refMemberID ) AS derFMR ON tblFarm.FarmID = derFMR.refFarmID WHERE 1=1" , "getVineyardBlockList" => "FROM tblBlock WHERE 1=1" , "getMemberAddressList" => "FROM tblMember_Address INNER JOIN (tblMember INNER JOIN tblMemberType ON refMemberTypeID = MemberTypeID) ON tblMember.MemberID = tblMember_Address.MemberID WHERE 1=1" , "getMemberContactList" => "FROM tblMember_Contact INNER JOIN (tblMember INNER JOIN tblMemberType ON refMemberTypeID = MemberTypeID) ON tblMember.MemberID = tblMember_Contact.MemberID WHERE 1=1" , "getMemberProfile" => "FROM tblMemberType INNER JOIN (tblMember LEFT JOIN tblMemberRelationship ON tblMember.MemberID = tblMemberRelationship.refMemberID) ON tblMemberType.MemberTypeID = tblMemberRelationship.refMemberTypeID WHERE 1=1" //20170313 - implemented getMemberProfile() - maanie , "getPackingMaterialList" => "FROM tblPackingMaterial WHERE 1=1" //20170322 - v1.3 added getPackingMaterialList() - maanie , "getIncotermList" => "FROM tblIncoterm WHERE 1=1" //20170323 - v1.3 added getIncotermList() - maanie , "getCountryCodesList" => "FROM tblCountryCode WHERE 1=1" //20170324 - v1.3 added getCountryCodesList() - maanie , "getBg9_11_TypeList" => "FROM tblBG911Type WHERE 1=1" //20170327 - v1.3 added getBg9_11_TypeList() - maanie , "getCertificateTransationCodeList" => "FROM tblCertificateTransationCode WHERE 1=1" //20170327 - v1.3 added getCertificateTransationCodeLiFst() - maanie , "getRejectCodesList" => "FROM tblCertificateRejectionCode WHERE 1=1" //20170327 - v1.3 added getRejectCodesList() - maanie , "getPackingCodesList" => "FROM tblCertificatePackingCode WHERE 1=1" //20170328 - v1.3 added getPackingCodesList() - maanie , "getCertificateStatusList" => "FROM tblCertificateStatus WHERE 1=1" //20170328 - v1.3 added getCertificateStatusList() - maanie , "getCertificateResultsList" => "FROM tblCertificateResult WHERE 1=1" //20170328 - v1.3 added getCertificateResultsList() - maanie , "getPackVolumeColourList" => "FROM tblPackVolumeColour WHERE 1=1" //20170329 - v1.3 added getPackVolumeColourList() - maanie , "getPackVolumeClosureTypeList" => "FROM tblPackVolumeClosureType WHERE 1=1" //20170329 - v1.3 added getPackVolumeClosureTypeList() - maanie , "getBottleDescriptionList" => "FROM tblBottleDescription WHERE 1=1" //20170330 - v1.3 added getBottleDescriptionList() - maanie , "getManufacturerList" => "FROM tblManufacturer WHERE 1=1" //20170330 - v1.3 added getManufacturerList() - maanie , "getCertificationList" => "FROM tblCertification WHERE 1=1" //20170408 - v1.3 added getCertificationList() - maanie , "getCertificationResultsList" => "FROM tblLabAnalysis WHERE 1=1" //20170412 - v1.3 added getCertificationResultsList() - maanie , "getTitleDeedsList" => "FROM tblTitleDeed WHERE 1=1" //20170516 - v1.3 implemented getTitleDeedsList() - maanie , "getMagisterialDistrictList" => "FROM tblMagisterialDistrict WHERE 1=1" //20170526 - v1.3 implemented getMagisterialDistrictList() - maanie , "getWIETAFarmList" => "FROM tblFarm LEFT JOIN tblBlock ON tblFarm.FarmID = tblBlock.refFarmID WHERE 1=1" //20170929 - Added new call getWIETAFarmList() - maanie , "getWietaMembershipList" => "FROM tblWietaCertification WHERE 1=1" //20170929 - Added new call getWietaMembershipList() - maanie , "getBlendMaster" => "FROM tblBlendMaster LEFT JOIN tblMember ON MemberID = refMemberID WHERE 1=1" //20180306 - v1.3 - added getBlendMaster() - maanie //, "getWSB" => "FROM (((tblFarm INNER JOIN tblHarvestItem ON tblFarm.FarmID = tblHarvestItem.refFarmID) INNER JOIN tblBlendMaster ON tblHarvestItem.refHarvestID = tblBlendMaster.refWSID) LEFT JOIN tblMember ON tblBlendMaster.refMemberID = tblMember.MemberID) LEFT JOIN tblMemberType ON tblMember.refMemberTypeID = tblMemberType.MemberTypeID WHERE 1=1" //20180308 - v1.3 - added getWSB() - maanie , "getWSB" => "FROM (tblBlendMaster LEFT JOIN tblMember ON tblBlendMaster.refMemberID = tblMember.MemberID) LEFT JOIN tblMemberType ON tblMember.refMemberTypeID = tblMemberType.MemberTypeID WHERE 1=1" //20180308 - v1.3 - added getWSB() - maanie , "getWS" => "FROM (((tblHarvest INNER JOIN (tblMember AS tblCellar INNER JOIN tblMemberType ON tblCellar.refMemberTypeID = tblMemberType.MemberTypeID) ON tblHarvest.refCellarID = tblCellar.MemberID) INNER JOIN tblHarvestItem ON tblHarvest.HarvestID = tblHarvestItem.refHarvestID) INNER JOIN tblFarm ON tblHarvestItem.refFarmID = tblFarm.FarmID) LEFT JOIN tblMember ON tblHarvestItem.refMemberID = tblMember.MemberID WHERE 1=1" //20180309 - v1.3 - added getWS() - maanie //20170302 - v1.4 - User API - pj , "getUser" => "FROM sysUser WHERE 1=1" , "getUserLogin" => "FROM sysUser INNER JOIN sysLogin ON strEmail = strUsername WHERE 1=1" , "getLogin" => "FROM sysLogin WHERE 1=1" //20181106 - v1.7 - LMS v1.2: getMemberProfileLMS(MemberID) - maanie , "getMemberProfileLMS" => "FROM tblMember INNER JOIN tblMemberType ON tblMember.refMemberTypeID = tblMemberType.MemberTypeID WHERE 1=1 " //20200304 - LMS: getMemberIDListLMS() - maanie , "getMemberIDListLMS" => "FROM tblMemberRelationship LEFT JOIN tblMemberType ON tblMemberRelationship.refMemberTypeID = tblMemberType.MembertypeID WHERE 1=1 " //20190118 - v1.6 - WMS API - maanie ,"getWineMSFarmList" => "FROM (vieWMS LEFT JOIN dwdLocation ON vieWMS.WOCode = dwdLocation.WOCode) LEFT JOIN dwdSawisLocation ON (vieWMS.SawisDistrictCode = dwdSawisLocation.DistrictCode) AND (vieWMS.SawisRegionCode = dwdSawisLocation.RegionCode) WHERE 1=1"//20190118 - v1.6 - WMS API: getWineMSFarmList - maanie ,"getWineMSBlockGUID" => "FROM vieWMS WHERE 1=1" //20190121 - v1.6 - WMS API: getWineMSBlockGUID - maanie ,"getWineMSMemberList" => "FROM vieWMS WHERE 1=1" //20190124 - v1.6 - WMS API: getWineMSMemberList - maanie ,"getWineMSFarm" => "FROM ((vieWMS LEFT JOIN dwdSawisLocation ON (vieWMS.SawisDistrictCode = dwdSawisLocation.DistrictCode) AND (vieWMS.SawisRegionCode = dwdSawisLocation.RegionCode)) INNER JOIN tblMemberType ON vieWMS.refMemberTypeID = tblMemberType.MemberTypeID) INNER JOIN dwdLocation ON vieWMS.WOCode = dwdLocation.WOCode WHERE 1=1" //20190125 - v1.6 - WMS API: getWineMSFarm - maanie ,"getWineMSMember" => "FROM (((vieWMS LEFT JOIN dwdSawisLocation ON (vieWMS.SawisDistrictCode = dwdSawisLocation.DistrictCode) AND (vieWMS.SawisRegionCode = dwdSawisLocation.RegionCode)) INNER JOIN tblMemberType ON vieWMS.refMemberTypeID = tblMemberType.MemberTypeID) INNER JOIN dwdLocation ON vieWMS.WOCode = dwdLocation.WOCode) LEFT JOIN tblMember AS opsMember ON vieWMS.OpsID = opsMember.MemberID WHERE 1=1" //20190129 - v1.6 - WMS API: getWineMSMember - maanie ,"getWineMSSawis1" => "FROM vieWMS INNER JOIN tblMemberType ON vieWMS.refMemberTypeID = tblMemberType.MemberTypeID WHERE 1=1" //20190201 - v1.6 - WMS API: getWineMSSawis1 - maanie //20200330 - getCGABlockList Add farm name to the results - jacques , "getCGABlockList" => "FROM tblBlock LEFT JOIN tblFarm ON tblBlock.refFarmID = tblFarm.FarmID WHERE 1=1" //20190613 - v1.0 - CIS API: getCGABlockList - maanie ); //must end in "WHERE 1=1" private $arrCallWHERE_Additional = array( "getCultivarList" => null , "getWineTypeList" => null , "getIrrigationList" => null , "getTrellisList" => null , "getLocationList" => null , "getMemberTypeList" => " AND tblMemberType.MemberTypeID > 0 " //20180405 - getMemberTypeList(): only show MemberTypeIDs > 0 - maanie , "getMemberList" => " AND tblMember.MemberID > 0 " , "getFarmList" => null , "getVineyardBlockList" => null , "getMemberAddressList" => " AND tblMember.MemberID > 0 " , "getMemberContactList" => " AND tblMember.MemberID > 0 " , "getMemberProfile" => " AND tblMember.MemberID > 0 " //" AND 1=0" //NB! return nothing by default, WOL only and set in setVendor_CallWHERE_Additional() , "getPackingMaterialList" => null //20170322 - v1.3 added getPackingMaterialList() - maanie , "getIncotermList" => null //20170323 - v1.3 added getIncotermList() - maanie , "getCountryCodesList" => null //20170324 - v1.3 added getCountryCodesList() - maanie , "getBg9_11_TypeList" => null //20170327 - v1.3 added getBg9_11_TypeList() - maanie , "getCertificateTransationCodeList" => null //20170327 - v1.3 added getCertificateTransationCodeList() - maanie , "getRejectCodesList" => null //20170327 - v1.3 added getRejectCodesList() - maanie , "getPackingCodesList" => null //20170328 - v1.3 added getPackingCodesList() - maanie , "getCertificateStatusList" => null //20170328 - v1.3 added getCertificateStatusList() - maanie , "getCertificateResultsList" => null //20170328 - v1.3 added getCertificateResultsList() - maanie , "getPackVolumeColourList" => null //20170329 - v1.3 added getPackVolumeColourList() - maanie , "getPackVolumeClosureTypeList" => null //20170329 - v1.3 added getPackVolumeClosureTypeList() - maanie , "getBottleDescriptionList" => null //20170330 - v1.3 added getBottleDescriptionList() - maanie , "getManufacturerList" => null //20170330 - v1.3 added getManufacturerList() - maanie , "getCertificationList" => null //20170408 - v1.3 added getCertificationList() - maanie , "getCertificationResultsList" => null //20170412 - v1.3 added getCertificationResultsList() - maanie //20170920 - v1.3 - getTitleDeedsList: exclude EntityType like %TMP%: - maanie , "getTitleDeedsList" => " AND EntityType NOT LIKE '%TMP%'" //20170516 - v1.3 implemented getTitleDeedsList() - maanie , "getMagisterialDistrictList" => null //20170526 - v1.3 implemented getMagisterialDistrictList() - maanie , "getWIETAFarmList" => null //20170929 - Added new call getWIETAFarmList() - maanie , "getWietaMembershipList" => null //20170929 - Added new call getWietaMembershipList() - maanie , "getBlendMaster" => null //20180306 - v1.3 - added getBlendMaster() - maanie , "getWSB" => null //n/a: " AND tblHarvestItem.blnDeleted = 0" //20180308 - v1.3 - added getWSB() - maanie , "getWS" => " AND tblHarvestItem.blnDeleted = 0" //20180309 - v1.3 - added getWS() - maanie //20170302 - v1.4 - User API - pj , "getUser" => null , "getUserLogin" => null , "getLogin" => null //20181106 - v1.7 - LMS v1.2: getMemberProfileLMS(MemberID) - maanie , "getMemberProfileLMS" => "AND ( ( (tblMember.strStatus like 'Inactive' OR tblMember.strStatus like 'Active') AND tblMemberType.refLMS <> 9) OR (tblMember.strStatus like 'Active' AND tblMemberType.refLMS IS NULL) ) AND tblMember.MemberID > 0" //20200304 - LMS: getMemberIDListLMS() - maanie , "getMemberIDListLMS" => "AND strMemberTypeCode IN ('FRMOWN', 'FRMTNT', 'FRMSCP')" //20190118 - v1.6 - WMS API - maanie ,"getWineMSFarmList" => " AND vieWMS.FarmStatus like 'Active'" //20190118 - v1.6 - WMS API: getWineMSFarmList - maanie ,"getWineMSBlockGUID" => null //20190121 - v1.6 - WMS API: getWineMSBlockGUID - maanie ,"getWineMSMemberList" => null //20190124 - v1.6 - WMS API: getWineMSMemberList - maanie ,"getWineMSFarm" => " AND (blnWineMS = 1) AND (tblMemberType.refWineMS IN('1', '3')) AND (MRStatus like 'Active') AND (FarmStatus like 'Active')" //20190125 - v1.6 - WMS API: getWineMSFarm - maanie ,"getWineMSMember" => " AND (vieWMS.blnWineMS = 1) AND (FarmStatus like 'Active') AND (MRStatus like 'Active') AND (tblMemberType.refWineMS IN('1', '3'))" //20190129 - v1.6 - WMS API: getWineMSMember - maanie ,"getWineMSSawis1" => " AND (blnWineMS = 1)" //20190201 - v1.6 - WMS API: getWineMSSawis1 - maanie , "getCGABlockList" => null //20190613 - v1.0 - CIS API: getCGABlockList - maanie ); //public function __construct($VendorKey, $Call, $Page, $SessionToken=null) public function __construct($VendorKey, $Call, $Page=1, $xmlData=null, $SessionToken=null) { global $xdb, $SystemSettings; if (!isset($Page)) { $Page = 1; //default to 1 } $this->VendorKey = qs($VendorKey); $this->Call = qs($Call); $this->Page = $Page; $this->SessionToken = qs($SessionToken); $this->xmlData = $xmlData; $this->args = $_GET; if($this->args[type] == "json"){ $this->ReturnType = "json"; } elseif($this->args[type] == "text"){ $this->ReturnType = "text"; } //Sort array ascending sort($this->arrCalls); $this->checkVendor(); $this->setVendor_CallWHERE_Additional(); //20160928 - set wheres per vendor - pj $this->limit = "LIMIT ". ($this->Page - 1) * $SystemSettings["PageSize"] .", ". $SystemSettings["PageSize"]; //print_rr($this->limit); die; parent::__construct(); // Init parent contructor } private function checkVendor() { global $xdb, $SystemSettings; if($this->blnIgnoreVendorKey == true) { $this->VendorKey = "PON135"; return true; } //check sysVendor if key exists $row = $xdb->getRowSQL("SELECT * FROM apiVendor WHERE VendorKey=\"$this->VendorKey\" AND blnActive = 1"); if($row){ $this->Password = $row->strPassword; $this->VendorSite = $row->strSite; return true; }else{ //record call $this->record(self::$INVALID_VENDOR_KEY); $this->_content_type = "text/html"; $this->response(self::$INVALID_VENDOR_KEY, self::$HTTP_ERROR_CODE); die; return false; } } //20170314 - v1.3 Added Additional getMemberProfile WHERE clauses for WOLINE/VINPRO/WIETA - maanie private function setVendor_CallWHERE_Additional() { global $xdb, $SystemSettings; switch($this->VendorKey) { case "VINPRO": /* Members that VINPRO should have access to must meet with the following criteria – • status = ACTIVE • status = INACTIVE with VINPRO member = Y • member type in table tblMemberType with field ‘VP’ not 9 • member profile data must be sorted according to field ‘VP’ – member will be classed as the type with the lowest ‘VP’ number and that is the record to pass on • excluded are status = CLOSED and status = INACTIVE with VINPRO member = N */ $this->arrCallWHERE_Additional["getMemberList"] = $this->arrCallWHERE_Additional["getMemberAddressList"] = $this->arrCallWHERE_Additional["getMemberContactList"] = $this->arrCallWHERE_Additional["getMemberProfile"] .= "AND ((tblMember.strStatus like 'Inactive' AND tblMember.blnVinPro = 1) OR (tblMember.strStatus like 'Active')) AND tblMemberType.refVinPro <> 9"; break; case "WOLINE": /* Members that WOL should have access to must meet with the following criteria – • member type in table tblMemberType with field ‘WO’ not = 9 The unique key for the Member profile consists of – SAWIS_MEMBER_NO (1), WOL_CODE(2desending), SAWIS_FARM_NO(3), MEMBER_TYPE(4), STATUS(5), MEMBER PRODCELLAR (6). */ $this->arrCallWHERE_Additional["getMemberList"] = $this->arrCallWHERE_Additional["getMemberAddressList"] = $this->arrCallWHERE_Additional["getMemberContactList"] = $this->arrCallWHERE_Additional["getMemberProfile"] .= "AND tblMember.strStatus like 'Active' AND tblMemberType.refWineOnline <> 9"; break; case "WIETA1": /* Members that WIETA should have access to must meet with the following criteria – • member type in table tblMemberType with field ‘WT’ not = 9 The unique key for the Member profile consists of – SAWIS_MEMBER_NO (1), WOL_CODE(2desending), SAWIS_FARM_NO(3), MEMBER_TYPE(4), STATUS(5), MEMBER PRODCELLAR (6). */ //$this->arrCallWHERE_Additional["getMemberProfile"] = " AND tblMemberType.??? <> 9"; //$this->arrCallWHERE_Additional["getWIETAFarmList"] = //n/a - not in the specs $this->arrCallWHERE_Additional["getMemberList"] = $this->arrCallWHERE_Additional["getMemberAddressList"] = $this->arrCallWHERE_Additional["getMemberContactList"] = $this->arrCallWHERE_Additional["getMemberProfile"] .= "AND tblMemberType.refWieta <> 9"; //20171101 - v1.3 getMemberTypeList: excluding fields & limiting to WT=9 - maanie $this->arrCallWHERE_Additional["getMemberTypeList"] .= "AND tblMemberType.refWieta <> 9"; break; case "EZW1NE": $this->arrCallWHERE_Additional["getMemberList"] = $this->arrCallWHERE_Additional["getMemberAddressList"] = $this->arrCallWHERE_Additional["getMemberContactList"] = $this->arrCallWHERE_Additional["getMemberProfile"] .= "AND 1=0"; break; case "LMS001": break; } } public function record($strLog, $strLogType=null) { global $xdb, $SystemSettings; //insert into api log() if($strLogType == null){ switch($strLog) { case self::$VALID_CALL: $strLogType = "info"; break; default: $strLogType = "warning"; break; } } $ldb = new NemoDatabase("apiLog", null, null, 0); $ldb->Fields[VendorKey] = $this->VendorKey; $ldb->Fields[SessionToken] = $this->SessionToken; //ph2 tokens $ldb->Fields[strLogType] = $strLogType; $ldb->Fields[strLog] = $strLog; $ldb->Fields[strRequest] = $this->Call; $ldb->Fields[Page] = $this->Page; //20160622 - Added page to api log/ record function - maanie $ldb->Fields[ip] = $_SERVER['REMOTE_ADDR']; $ldb->Fields[encArgs] = serialize($this->args); //20180926 - renamed field to encArgs - maanie $ldb->Save(0,0); $xdb->doQuery("DELETE FROM apiLog WHERE dtLog < Now()-INTERVAL 3 MONTH", 0); return true; } //*********TOKENS*********************************************************** //20161020 - implemented Authentication - pj private function authenticate() { global $xdb, $SystemSettings; //check sysVendor if key exists if($_REQUEST["Password"] == $this->Password) return true; else{ // //record call // $this->record(self::$AUTHENTICATION_FAILED); // $this->_content_type = "text/html"; // $this->response(self::$AUTHENTICATION_FAILED, self::$HTTP_ERROR_CODE); // die; return false; } } private function generateToken() { global $xdb, $SystemSettings; $this->SessionData = serialize($this->args); $this->SessionToken = $xdb->getRowSQL("SELECT UUID() as SessionToken")->SessionToken; $xdb->doQuery(" INSERT INTO apiToken SET strToken = \"$this->SessionToken\" , VendorKey = \"$this->VendorKey\" , data = '$this->SessionData'", 0); //die(); return $this->Token; } private function checkToken() { global $xdb, $SystemSettings; $rowToken = $xdb->getRowSQL("SELECT * FROM apiToken WHERE strToken =\"$this->SessionToken\" AND VendorKey = \"$this->VendorKey\" ORDER BY ID DESC", 0); if($rowToken){ $this->SessionToken = $rowToken->strToken; $this->SessionData = unserialize($rowToken->data); //print_rr($this->SessionData); //refresh token $xdb->doQuery("UPDATE apiToken SET dtToken = NOW() WHERE ID = $rowToken->ID", 0); return true; }else{ return false; } } private function clearTokenCache(){ global $xdb, $SystemSettings; $xdb->doQuery("DELETE FROM apiToken WHERE dtToken < Now()-INTERVAL $this->SessionExpire", 0); } /* * Dynmically call the method based on the query string 1.1 - IF TOKEN, CHECK TOKEN() 1.2 - CLEAR TOKENS() 3 - CALL() */ public function processApi() { global $xdb, $SystemSettings; //20161020 - implemented Authentication - pj if($_GET["IGNORE_TOKENZ"] == $this->Password){ $this->blnIgnoreSessionToken = true; } if($this->blnIgnoreSessionToken == true) { $this->SessionToken = "Poniess-will-pawn-your-00240101a0ad"; }else{ $this->clearTokenCache(); switch($this->Call) { case "authenticate": if($this->authenticate()){ $this->generateToken(); //record $this->record($this->SessionToken,"info"); //response $this->_content_type = "text/html"; $this->response($this->SessionToken); }else{ //record $this->record(self::$AUTHENTICATION_FAILED); //response $this->_content_type = "text/html"; $this->response(self::$AUTHENTICATION_FAILED); } break; default: //check session token if(!$this->checkToken($this->SessionToken)) { //record $this->record(self::$INVALID_SESSION_TOKEN); //response $this->_content_type = "text/html"; $this->response(self::$INVALID_SESSION_TOKEN, self::$HTTP_ERROR_CODE); return false; } break; } } //check method exist $func = trim(str_replace("/","",$this->Call)); if(($this->blnMethodExist = method_exists($this,$func) ) === true){ //record vendor-access to call $this->record(self::$VALID_CALL); //make the call $rst = $this->$func($this->args); //print_rr($xdb->fetch($rst)); $rst = $this->cleanVendorRST($rst); //20160927 - remove fields from the rst based on vendor - pj //print_rr($xdb->fetch($rst)); die; switch($this->ReturnType) { case "xml": $this->_content_type = "application/xml"; $this->response(NemoAPI::xml($rst)); break; case "json": $this->_content_type = "application/json"; $this->response(NemoAPI::json($rst)); break; case "text": $this->_content_type = "text/html"; $this->response($rst); break; } return true; }else{ //record call $this->record(self::$INVALID_CALL); //response $this->_content_type = "text/html"; $this->response(self::$INVALID_CALL, self::$HTTP_ERROR_CODE); // If the method not exist with in this class "Page not found". return false; } } //*********PUBLIC CAllS START************************************************* //20170302 - v1.4 - User API - pj private function getUser($args) { global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getUser] ." ". $this->arrCallWHERE_Additional[getUser] ." AND (UserID = ". $xdb->qs($args["UserID"]) ." OR strEmail = ". $xdb->qs($args["Username"]) .") LIMIT 0,1", 0); if($rst->num_rows > 0) { return $rst; } else { $this->ReturnType = "text"; return "Invalid User"; } } //20170628 - v1.4 - User API: createUser() - maanie private function createUser() { global $xdb, $SystemSettings; /* PROCESS STEPS: 1. get Fields excluding UserID 2. check for required fields 3. save new user 4. send password reset email to user 5. return xmlUser on true */ //var_dump($this->xmlData); die; //get xml data + check format $xml = simplexml_load_string($this->xmlData); //var_dump($xml); //var_dump($this->xmlData); die; if($this->xmlData == null || $xml == null){ $this->record(self::$INVALID_STRUCTURE); $this->response(self::$INVALID_STRUCTURE, self::$HTTP_ERROR_CODE); return false; } $rowData = (array) $xml->rows->row; //print_rr($rowData); die; //20170424 - v1.3 added check for required fields - maanie //validate required fields $requiredFields = array("strEmail", "strPassword"); for($i = 0; $i < count($requiredFields); $i++) { if($rowData[$requiredFields[$i]] == "" || $rowData[$requiredFields[$i]] == null) { $this->record(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]"); //$this->response(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]", self::$HTTP_ERROR_CODE); $this->response("Please make sure you have filled in all required fields", self::$HTTP_ERROR_CODE); //20171220 - createUser(): changed response on missing required fields - maanie } } //insert $cdb = new NemoDatabase("sysUser", null, null, 0); //$cdb->blnCustomID = true; $cdb->SetValues($rowData); //20180619 - createUser(): set strSetting:Language field - maanie $cdb->Fields["strSetting:Language"] = $rowData["strSetting_Language"]; //print_rr($cdb->FieldList); //unset missing fields foreach($cdb->FieldList as $key => $column) { if(!array_key_exists($column->name, $rowData)){ if($column->name != "refSecurityGroupID" && $column->name != "strSetting:Language") // mandatory field, cannot unset unset($cdb->FieldList[$key]); } } //print_rr($cdb->FieldList); //20170928 - v1.4 - createUser: check vendor to set SG - maanie if($this->VendorKey == "SAWIS3") { //print_rr($cdb->Fields[refSecurityGroupID]); die; if($cdb->Fields[refSecurityGroupID] == 0) { $cdb->Fields[refSecurityGroupID] = $xdb->getRowSQL("SELECT SecurityGroupID FROM sysSecurityGroup WHERE strSecurityGroup = 'Member Superuser [Unapproved]'")->SecurityGroupID; } } else //use special security group for new users created by external vendors { $cdb->Fields[refSecurityGroupID] = $xdb->getRowSQL("SELECT SecurityGroupID FROM sysSecurityGroup WHERE strSecurityGroup = 'External Site User'")->SecurityGroupID; } //print_rr($cdb->FieldList); //save $result = $cdb->Save(0,0); $UserID = $cdb->getLastID(); if($result->Error == 1) { if (strpos($result->Message, 'Unique key Violation') !== false) $error = "Cannot add User, Email already in use"; else $error = $result->Message; } else { $args["Username"] = $rowData[strEmail]; $args["Password"] = $rowData[strPassword]; $api = new NemoAPI($this->VendorKey, $this->call, $this->Page, $this->xmlData, $this->Token); $response = $api->setPassword($args); //var_dump($response); if($response == 1) //must be 1 NOT TRUE as it returns text!!! { $this->ReturnType = "xml"; $rst = $xdb->doQuery("SELECT * FROM sysUser WHERE UserID = ". $UserID ."", 0); return $rst; } else //return error { $error = $response; } } $this->ReturnType = "text"; return $error; } //20170630 - v1.4 - User API: updateUser() - maanie private function updateUser() { global $xdb, $SystemSettings; //get xml data + check format $xml = simplexml_load_string($this->xmlData); //print_rr($xml); if($this->xmlData == null || $xml == null){ $this->record(self::$INVALID_STRUCTURE); $this->response(self::$INVALID_STRUCTURE, self::$HTTP_ERROR_CODE); return false; } $rowData = (array) $xml->rows->row; //set unique key $key = "UserID"; //check if key exists if(!array_key_exists($key, $rowData)) { $this->record(self::$INVALID_STRUCTURE ." [Missing $key]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $key]", self::$HTTP_ERROR_CODE); } //update $rowUser = $xdb->getRowSQL("SELECT * FROM sysUser WHERE UserID = ". $rowData["UserID"], 0); if($rowUser) { $cdb = new NemoDatabase("sysUser", $rowData["UserID"], null, 0); $cdb->blnCustomID = true; $cdb->SetValues($rowData); //20180316 - updateUser(): set strSetting:Language field - maanie $cdb->Fields["strSetting:Language"] = $rowData["strSetting_Language"]; $cdb->Fields["strUser"] = $rowData["strName"] . " " . $rowData["strSurname"]; //20180823 - update updateUser().strUser - maanie //print_rr($cdb->FieldList); die; //unset missing fields foreach($cdb->FieldList as $key => $column) { if(!array_key_exists($column->name, $rowData)){ if($column->name != "strSetting:Language" && $column->name != "strUser") { unset($cdb->FieldList[$key]); } } } //save $result = $cdb->Save(0,0); if($result->Error == 1) { if (strpos($result->Message, 'Unique key Violation') !== false) $error = "Cannot change Email, Email already in use"; else $error = $result->Message; } else // return xmlUser if no errors { $rst = $xdb->doQuery("SELECT * FROM sysUser WHERE UserID = ". $rowData["UserID"], 0); //print_rr($xdb->fetch($rst)); return $rst; } } else { $error = "Invalid user data: User does not exist"; } $this->ReturnType = "text"; return $error; } //20170630 - v1.4 - User API: setPassword() - maanie //20170830 - v1.4 - setPassword(): Implemented same password checks as SAWIS - maanie private function setPassword($args) { global $xdb, $SystemSettings; $this->ReturnType = "text"; $row = $xdb->getRowSQL("SELECT * FROM sysUser WHERE strEmail = ". $xdb->qs($args["Username"]) ."", 0); if($row) { $np = new NemoPassword(); $passwordlength = strlen($args["Password"]); $strPassword = str_replace("&", "", $args["Password"]); $strPassword = str_replace(" ", "", $strPassword); //check password strength $blnWeakPassword = false; //if (!preg_match('/^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9]).+$/', $strPassword))//(!preg_match("~^(?=.*[A-Z])[A-Za-z0-9\d=!\-@._*]{8,14}$~", $args["Password"])) "^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[,.;:&\-@_*]).+$" //20180323 - amended password check to match SAWIS - maanie //20190408 - added #% to password check - maanie $verdict = preg_match("/^[A-Za-z0-9\d=!\-@#%._*]*$/", $strPassword) && preg_match("/[A-Z]/", $strPassword) && preg_match("/\d/", $strPassword); if ($verdict == 0) { $blnWeakPassword = true; } if($passwordlength < 8 || $passwordlength > 14) { $blnWeakPassword = true; } //check restricted words /*$blnContainsRestrictedWords = false; foreach($np->PASSWORD_Restricted_Words as $restricted_word) { if (strpos($args["Password"], $restricted_word) !== false) { $blnContainsRestrictedWords = true; break; } }*/ if($blnWeakPassword == false) // && $blnContainsRestrictedWords == false) { $np->createPassword($args["Password"]); $np->savePassword("User", $row->UserID, date('Y-m-d'), 0); return true; } else { //20180823 - setPassword(): Changed password warning message - maanie $error .= "Your password must conform to the following security standard of (1) minimum 8 characters, (2) contains at least one capital letter, (3) one number, (4) only standard letters, Aa to Zz and special characters allowed !@#$%^&*()_+=- E.g. L0veW1ne"; } } else { tblLoginInsert("Login failed: User not found or SG not active"); $error = NemoPassword::$PASSWORD_Invalid_Login_Details; } return $error; } //20170703 - v1.4 - User API: resetPassword() - maanie private function resetPassword($args) { global $xdb, $SystemSettings; $this->ReturnType = "text"; $row = $xdb->getRowSQL("SELECT * FROM sysUser WHERE strEmail = ". $xdb->qs($args["Username"]) ."", 0); if($row) { //send password reset email include_once("_framework/_nemo.email.cls.php"); $np = new NemoPassword(); $strRandomPassword = Obfuscate(); $np->createPassword($strRandomPassword); $np->savePassword("User", $row->UserID, '2010-01-01'); // 2010-01-01 = Default Aged date ## ADD RECORD TO RESET PASSWORD TABLE $nemoEmail = new NemoEmail($args["Username"], "" , 0); $nemoEmail->LoadEmailTemplate("Reset Password"); $arrValues[DisplayName] = $row->strUser; //20171005 - determine base url based on vendorkey - maanie if($this->VendorKey == "SAW3QA") { $strBaseURL = "http://10.0.0.149/html"; //sawis } elseif($this->VendorKey == "S3PROD") { $strBaseURL = "https://www.sawisonline.co.za"; //"10.0.0.60"; } else //LAMP/GIT { $strBaseURL = $SystemSettings[SERVER_NAME] . "/sawis"; } $arrValues[Link] = "Reset Password"; $arrValues[Logo] = ""; $nemoEmail->Substitute($arrValues); $nemoEmail->addHeader("FROM", $SystemSettings["SMTP Send As Sawis3"]); //20171125 - Process specific emails - pj $nemoEmail->addHeader("BCC", $SystemSettings["SMTP BCC"]); $nemoEmail->Bcc = $SystemSettings["SMTP BCC"]; $nemoEmail->From = $SystemSettings["SMTP Send As Sawis3"]; $nemoEmail->Send(); return true; } else { return "Invalid username"; } } //20170629 - v1.4 - User API: loginUser() - maanie //20171211 - added urldecode to args - maanie private function loginUser($args) { global $xdb, $SystemSettings; /**** PROCESS: 1.check if site is valid ??? 2 check if access restricted ??? 3.login user get user record if username exits if user is active if password correct (hash + salt password) write sysLogin record (strToken = $Token) return xmlUser (incl LoginToken) else write sysLogin record (strToken = $Token) set error = INVALID PASSWORD else write sysLogin record (strToken = $Token) set error = USER INACTIVE else write sysLogin record (strToken = $Token) set error = INVALID USER 4. return error *****/ //$row = $xdb->getRowSQL("SELECT * FROM sysUser WHERE strEmail = ". $xdb->qs($args["Username"]) ."", 0); $row = $xdb->getRowSQL("SELECT * FROM sysUser WHERE strEmail = ". $xdb->qs(urldecode($args["Username"])) ."", 0); if($row) { //20170731 - checks whether user can log into the system - maanie if($this->checkAccess($row)) { if($row->blnActive == 1) { $np = new NemoPassword(); if($np->checkPassword("User", $row->UserID, urldecode($args["Password"]), 0)) {//print_rr("sdf"); tblLoginInsert("Login successful"); // create and inserts login token too $rst = $xdb->doQuery("SELECT a.strToken, sysUser.* FROM (sysLogin AS a INNER JOIN (SELECT strUsername, MAX(dtLastEdit) AS strMaxDateTime FROM sysLogin GROUP BY strUsername ) AS b ON (a.dtLastEdit = b.strMaxDateTime) AND (a.strUsername = b.strUsername)) INNER JOIN sysUser ON b.strUsername = sysUser.strEmail WHERE UserID = ". $row->UserID . "", 0); return $rst; } else { tblLoginInsert("Login failed: Incorrect password"); $error = NemoPassword::$PASSWORD_Check_Fail; //$error .= CheckSecurityLock($row->UserID); } } else { tblLoginInsert("Login failed: Inactive User"); $error = NemoPassword::$PASSWORD_User_Inactive; } } else { tblLoginInsert("Login failed: Access restricted"); $error = "Access Restricted: User cannot log into this system"; } } else { tblLoginInsert("Login failed: User not found or SG not active"); $error = NemoPassword::$PASSWORD_Invalid_Login_Details; } $this->ReturnType = "text"; return $error; } //20170630 - v1.4 - User API: loginToken() - maanie private function loginToken($args) { global $xdb, $SystemSettings; /**** PROCESS: 1.check if site is valid ??? 2 check if access restricted ??? 2 login using token get login details if token exists if token has not expired return xmlUser else return token expire error else return invalid token error *****/ $row = $xdb->getRowSQL("SELECT UserID, dtTokenExpiration, strUsername, blnSite_SawisOnline, blnSite_Sawis_co_za FROM sysLogin INNER JOIN sysUser ON strUsername = strEmail WHERE strToken = ". $xdb->qs($args["LoginToken"]) ."", 0); if($row) { //20170731 - checks whether user can log into the system - maanie if($this->checkAccess($row)) { if($row->dtTokenExpiration < date("Y-m-d H:i:s")) { tblLoginInsert("Login successful"); // create and inserts login token $rst = $xdb->doQuery("SELECT a.strToken, sysUser.* FROM (sysLogin AS a INNER JOIN (SELECT strUsername, MAX(dtLastEdit) AS strMaxDateTime FROM sysLogin GROUP BY strUsername ) AS b ON (a.dtLastEdit = b.strMaxDateTime) AND (a.strUsername = b.strUsername)) INNER JOIN sysUser ON b.strUsername = sysUser.strEmail WHERE UserID = ". $row->UserID . "", 0); return $rst; } else { tblLoginInsert("Login failed: Login Token has expired"); $error = "Login Token has expired"; } } else { tblLoginInsert("Login failed: Access restricted"); $error = "Access Restricted: User cannot log into this system"; } } else { tblLoginInsert("Login failed: Invalid Login Token"); $error = "Invalid Login Token"; } $this->ReturnType = "text"; return $error; } //2017???? - v1.3 - WOL/WIETA API - pj // 20160621 - Implemented paging variable with all other get calls + added new call (getListPages([call]) )- maanie private function getListPages() { global $xdb, $SystemSettings; if($this->args["List"]) { $this->arrCalls = array($this->args["List"]); } //make call foreach($this->arrCalls as $call) { if($call == "authenticate"){//EXCLUDES }else{ //20190219 - getMemberProfileLMS: reworked the limit for this call: made it a tenth of the normal limit - maanie switch($call) { /*case "getMemberProfileLMS": $PageSize = $SystemSettings["PageSize"] / 10; break;*/ default: $PageSize = $SystemSettings["PageSize"]; break; } $sql .= $UNION ."SELECT '$call' as 'Call', ceil(COUNT(*)/". $PageSize .") as intPages ". $this->arrCallFROM[$call] . " ". $this->arrCallWHERE_Additional[$call]; $UNION = " UNION ALL "; } } //execute sql, return rst $rst = $xdb->doQuery($sql ." ORDER BY `Call`", 0); //die; return $rst; } //20170313 - v1.3 Dev: Tested getCultivarList - maanie private function getCultivarList($args) { global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getCultivarList] ." ". $this->arrCallWHERE_Additional[getCultivarList] ." ORDER BY CultivarID $this->limit", 0); return $rst; } //20170313 - v1.3 Dev: Tested getWineTypeList - maanie private function getWineTypeList() { global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT WineTypeID,WineTypeCode,strCategoryCode,EN_strCategory,AF_strCategory,strSubCategoryCode,EN_strSubCategory,AF_strSubCategory,strAlcoholContentCode, EN_strAlcoholContent,AF_strAlcoholContent,strDescriptionCode,EN_strDescription,AF_strDescription,TypeCode,EN_strType,AF_strType,strSubTypeCode, EN_strSubType,AF_strSubType,strSubDescriptionCode,EN_strSubDescription,AF_strSubDescription,txtNotes,blnCertification,blnActive,strLastUser,dtLastEdit ". $this->arrCallFROM[getWineTypeList] ." ". $this->arrCallWHERE_Additional[getWineTypeList] ." ORDER BY WineTypeID $this->limit", 0); return $rst; } private function getIrrigationList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getIrrigationList] ." ". $this->arrCallWHERE_Additional[getIrrigationList] ." ORDER BY IrrigationID $this->limit"); return $rst; } private function getTrellisList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getTrellisList] ." ". $this->arrCallWHERE_Additional[getTrellisList] ." ORDER BY TrellisID $this->limit"); return $rst; } //20170313 - v1.3 Dev: Tested getLocationList - maanie private function getLocationList(){ global $xdb, $SystemSettings; //HDP - 23/10/2018 - Add new fields [20181019 - WOCode Change vc11 >> vc21 - pj [201810.17 - WINE OF ORIGIN (WO) CODE change]] $rst = $xdb->doQuery("SELECT LocationID, WOCode, CountryCode , strCountry, EN_strCountry, AF_strCountry , OverGeoCode, strOverGeo, EN_strOverGeo, AF_strOverGeo , GeoCode, strGeo, EN_strGeo, AF_strGeo , OverRegionCode, strOverRegion, EN_strOverRegion, AF_strOverRegion , RegionCode, strRegion, EN_strRegion, AF_strRegion , SubRegionCode, strSubRegion, EN_strSubRegion, AF_strSubRegion , DistrictCode, strDistrict, EN_strDistrict, AF_strDistrict , WardCode, strWard, EN_strWard, AF_strWard , strCertifyBlendAs, EN_strCertifyBlendAs, AF_strCertifyBlendAs, intStartVintage, intEndVintage, blnLockLocationID, blnActive, strLastUser, dtLastEdit ". $this->arrCallFROM[getLocationList] ." ". $this->arrCallWHERE_Additional[getLocationList] ." ORDER BY LocationID $this->limit", 0); return $rst; } private function getMemberTypeList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getMemberTypeList] ." ". $this->arrCallWHERE_Additional[getMemberTypeList] ." ORDER BY MemberTypeID $this->limit"); return $rst; } //20170314 - v1.3 Dev: Tested getMemberList - maanie private function getMemberList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT tblMember.* ". $this->arrCallFROM[getMemberList] ." ". $this->arrCallWHERE_Additional[getMemberList] ." ORDER BY MemberID $this->limit", 0); return $rst; } //20170216 - added getMemberProfile() - pj //20170313 - v1.3 implemented getMemberProfile() - maanie //20181205 - getMemberProfile(): added MR.Status for WIETA only - maanie private function getMemberProfile(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT tblMember.MemberID, tblMemberRelationship.refEntityID AS FarmID, tblMember.strWOLCode, tblMemberType.strMemberTypeCode, tblMemberType.strMemberType, tblMember.strStatus, tblMemberRelationship.syncProductionCellarID, tblMember.strEstateName, tblMemberRelationship.syncPreviousMemberID, tblMemberRelationship.syncNewMemberID, tblMember.dtRegistration, tblMemberRelationship.syncOldWOLCode, tblMemberRelationship.syncNewWOLCode, tblMemberRelationship.strStatus AS 'strRelationshipStatus', tblMember.dtLastEdit, tblMember.blnVinPro,tblMemberRelationship.syncBEE ". $this->arrCallFROM[getMemberProfile] ." ". $this->arrCallWHERE_Additional[getMemberProfile] ." ORDER BY MemberID ASC, strWOLCode DESC, refEntityID, strMemberType, tblMember.strStatus, tblMemberRelationship.syncProductionCellarID $this->limit", 0); return $rst; } //20170314 - v1.3 Dev: Tested getMemberAdressList - maanie private function getMemberAddressList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT tblMember_Address.* ". $this->arrCallFROM[getMemberAddressList] ." ". $this->arrCallWHERE_Additional[getMemberAddressList] ." ORDER BY MemberID $this->limit"); return $rst; } private function getMemberContactList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT tblMember_Contact.* ". $this->arrCallFROM[getMemberContactList] ." ". $this->arrCallWHERE_Additional[getMemberContactList] ." ORDER BY MemberID $this->limit" , 0); return $rst; } private function getVineyardBlockList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getVineyardBlockList] ." ". $this->arrCallWHERE_Additional[getVineyardBlockList] ." ORDER BY strBlock $this->limit"); return $rst; } private function getFarmList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery(" SELECT tblFarm.* ". $this->arrCallFROM[getFarmList] ." ". $this->arrCallWHERE_Additional[getFarmList] ." GROUP BY tblFarm.FarmID ORDER BY tblFarm.FarmID $this->limit"); return $rst; } //20170322 - v1.3 added getPackingMaterialList() - maanie //20170322 - v1.3 DEV: Tested getPackingMaterialList() - maanie private function getPackingMaterialList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getPackingMaterialList] ." ". $this->arrCallWHERE_Additional[getPackingMaterialList] ." ORDER BY PackingMaterialID $this->limit"); return $rst; } //20170323 - v1.3 added getIncotermList() - maanie //20170323 - v1.3 DEV: Tested getIncotermList() - maanie private function getIncotermList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getIncotermList] ." ". $this->arrCallWHERE_Additional[getIncotermList] ." ORDER BY IncotermID $this->limit"); return $rst; } //20170324 - v1.3 added getCountryCodesList() - maanie //20170324 - v1.3 DEV: Tested getCountryCodesList() - maanie private function getCountryCodesList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getCountryCodesList] ." ". $this->arrCallWHERE_Additional[getCountryCodesList] ." ORDER BY CountryCodeID $this->limit"); return $rst; } //20170327 - v1.3 added getBg9_11_TypeList() - maanie //20170327 - v1.3 DEV: Tested getBg9_11_TypeList() - maanie private function getBg9_11_TypeList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getBg9_11_TypeList] ." ". $this->arrCallWHERE_Additional[getBg9_11_TypeList] ." ORDER BY BG911TypeID $this->limit"); return $rst; } //20170327 - v1.3 added getCertificateTransationCodeList() - maanie //20170327 - v1.3 DEV: Tested getCertificateTransationCodeList() - maanie private function getCertificateTransationCodeList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getCertificateTransationCodeList] ." ". $this->arrCallWHERE_Additional[getCertificateTransationCodeList] ." ORDER BY CertificateTransationCodeID $this->limit"); return $rst; } //20170327 - v1.3 added getRejectCodesList() - maanie //20170327 - v1.3 DEV: Tested getRejectCodesList() - maanie private function getRejectCodesList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getRejectCodesList] ." ". $this->arrCallWHERE_Additional[getRejectCodesList] ." ORDER BY CertificateRejectionID $this->limit"); return $rst; } //20170328 - v1.3 added getPackingCodesList() - maanie //20170328 - v1.3 DEV: Tested getPackingCodesList() - maanie private function getPackingCodesList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getPackingCodesList] ." ". $this->arrCallWHERE_Additional[getPackingCodesList] ." ORDER BY CertificatePackingCodeID $this->limit"); return $rst; } //20170328 - v1.3 added getCertificateStatusList() - maanie //20170328 - v1.3 DEV: Tested getCertificateStatusList() - maanie private function getCertificateStatusList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getCertificateStatusList] ." ". $this->arrCallWHERE_Additional[getCertificateStatusList] ." ORDER BY CertificateStatusID $this->limit"); return $rst; } //20170328 - v1.3 added getCertificateResultsList() - maanie //20170328 - v1.3 DEV: Tested getCertificateResultsList() - maanie private function getCertificateResultsList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getCertificateResultsList] ." ". $this->arrCallWHERE_Additional[getCertificateResultsList] ." ORDER BY CertificateResultID $this->limit"); return $rst; } //20170329 - v1.3 added getPackVolumeColourList() - maanie //20170329 - v1.3 DEV: Tested getPackVolumeColourList() - maanie private function getPackVolumeColourList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getPackVolumeColourList] ." ". $this->arrCallWHERE_Additional[getPackVolumeColourList] ." ORDER BY PackVolumeColourID $this->limit"); return $rst; } //20170329 - v1.3 added getPackVolumeClosureTypeList() - maanie //20170329 - v1.3 DEV: Tested getPackVolumeClosureTypeList() - maanie private function getPackVolumeClosureTypeList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getPackVolumeClosureTypeList] ." ". $this->arrCallWHERE_Additional[getPackVolumeClosureTypeList] ." ORDER BY PackVolumeClosureTypeID $this->limit"); return $rst; } //20170330 - v1.3 added getBottleDescriptionList() - maanie //20170330 - v1.3 DEV: Tested getBottleDescriptionList() - maanie private function getBottleDescriptionList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getBottleDescriptionList] ." ". $this->arrCallWHERE_Additional[getBottleDescriptionList] ." ORDER BY BottleDescriptionID $this->limit"); return $rst; } //20170330 - v1.3 added getManufacturerList() - maanie //20170330 - v1.3 DEV: Tested getManufacturerList() - maanie private function getManufacturerList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getManufacturerList] ." ". $this->arrCallWHERE_Additional[getManufacturerList] ." ORDER BY ManufacturerID $this->limit"); return $rst; } //20170408 - v1.3 added getCertificationList() - maanie //20170408 - v1.3 DEV: Tested getCertificationList() - maanie private function getCertificationList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getCertificationList] ." ". $this->arrCallWHERE_Additional[getCertificationList] ." ORDER BY TransactionID $this->limit"); return $rst; } //20170412 - v1.3 added getCertificationResultsList() - maanie //20170412 - v1.3 DEV: Tested getCertificationResultsList() - maanie //20200127 - added search parameters to getCertificationResultsList() - maanie private function getCertificationResultsList(){ global $xdb, $SystemSettings; if(isset($this->args["TransactionID"])) { $Where .= " AND TransactionID = " . $xdb->qs($this->args["TransactionID"]); } if(isset($this->args["LabID"])) { $Where .= " AND LabID = " . $xdb->qs($this->args["LabID"]); } if(isset($this->args["dtLabAnalysis"])) { $Where .= " AND dtLabAnalysis LIKE " . $xdb->qs("%".$this->args["dtLabAnalysis"] . "%"); } if(isset($this->args["strStatus"])) { $Where .= " AND strStatus = " . $xdb->qs($this->args["strStatus"]); } $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getCertificationResultsList] ." ". $this->arrCallWHERE_Additional[getCertificationResultsList] ." $Where ORDER BY TransactionID $this->limit"); return $rst; } //20170516 - v1.3 implemented getTitleDeedsList() - maanie //20170516 - v1.3 DEV: Tested getTitleDeedsList() - maanie private function getTitleDeedsList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getTitleDeedsList] ." ". $this->arrCallWHERE_Additional[getTitleDeedsList] ." ORDER BY TitleDeedID $this->limit"); return $rst; } //20170526 - v1.3 implemented getMagisterialDistrictList() - maanie //20170526 - v1.3 DEV: Tested getMagisterialDistrictList() - maanie private function getMagisterialDistrictList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getMagisterialDistrictList] ." ". $this->arrCallWHERE_Additional[getMagisterialDistrictList] ." ORDER BY MagisterialDistrictID $this->limit"); return $rst; } //20170929 - Added new call getWIETAFarmList() - maanie //20170929 - DEV: Tested getWIETAFarmList() - maanie private function getWIETAFarmList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT FarmID, strFarm, syncHectare, strVineStatus, tblFarm.strStatus , CONCAT(LPAD(SawisRegionCode , 2, '0'),LPAD(SawisDistrictCode , 2, '0')) AS SawisLocationCode, refLocationID , SUM(tblBlock.dblHectare) as dblHectare , CASE WHEN tblBlock.strGPS <> 'N' AND tblBlock.strGPS <> 0 AND tblBlock.strGPS <> NULL THEN 1 ELSE 0 END as blnGPSHectare , tblFarm.dtLastEdit ". $this->arrCallFROM[getWIETAFarmList] ." ". $this->arrCallWHERE_Additional[getWIETAFarmList] ." GROUP BY tblFarm.FarmID ORDER BY FarmID $this->limit", 0); return $rst; } //20170929 - Added new call getWietaMembershipList() - maanie //20170929 - DEV: Tested getWietaMembershipList() - maanie private function getWietaMembershipList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getWietaMembershipList] ." ". $this->arrCallWHERE_Additional[getWietaMembershipList] ." ORDER BY MemberID $this->limit"); return $rst; } //20180306 - v1.3 - added getBlendMaster() - maanie private function getBlendMaster(){ global $xdb, $SystemSettings; //ini $db = new NemoDatabase("tblBlendMaster", null, null, 0); $db->setValues($this->args); //print_rr($this->args); $args = $db->Fields; $or = ""; //Build where foreach($args as $key => $value) {//vd($value); if($value != "" || $value != 0 || $value != null) {//vd($value); $where .= "$or `$key` = ". $xdb->qs($value) ." "; $or = " OR "; } } if($where != "") $where = " AND ( $where ) "; //print_rr($args); $rst = $xdb->doQuery("SELECT WSBID, refMemberID, tblBlendMaster.strWOLCode, dtWSBIssueDate, intVintage, CultivarCode, ProductTypeCode, WOCode, dtActionDate, CertificateTransactionCode, BlendNumber, refWSID, refWSBID, dblLitresIn, dblLitresOut, dblLitresBalance, strBrandName, txtBlendLabelDetail, blnEstate, blnIPWStatus, refCellarID, tblMember.strMember AS strServicesCellar, strServicesFor, refSingleVineyardID, blnOrganic, ArchiveRID, txtComments, blnAlcoholRemoved, tblBlendMaster.strStatus, exCertificateTransactionCode, refBlendNumber, tblBlendMaster.strLastUser, strUpdateDate, strUpdateTime, tblBlendMaster.dtLastEdit " . $this->arrCallFROM[getBlendMaster] . $this->arrCallWHERE_Additional[getBlendMaster] ." ". $where ." ORDER BY BlendMasterID ASC $this->limit", 0); return $rst; } //20180308 - v1.3 - added getWSB() - maanie //20180404 - getWSB(): added new fields - maanie private function getWSB(){ global $xdb, $SystemSettings; //print_rr($args); $rst = $xdb->doQuery("SELECT WSBID, refMemberID, tblBlendMaster.strWOLCode, strCategory, dtWSBIssueDate, intVintage, CultivarCode, ProductTypeCode, WOCode, dtActionDate, CertificateTransactionCode, BlendNumber, refWSID, refWSBID, dblLitresIn, dblLitresOut, dblLitresBalance, strBrandName, txtBlendLabelDetail, tblBlendMaster.blnEstate, blnIPWStatus, refCellarID, tblMember.strMember AS strServicesCellar, strServicesFor, tblBlendMaster.refSingleVineyardID, blnOrganic, ArchiveRID, txtComments, blnAlcoholRemoved, tblBlendMaster.strStatus, exCertificateTransactionCode, refBlendNumber, tblBlendMaster.strLastUser, strUpdateDate, strUpdateTime, tblBlendMaster.dtLastEdit " . $this->arrCallFROM[getWSB] . $this->arrCallWHERE_Additional[getWSB] ." AND WSBID =" . $xdb->qs($this->args[WSBID]) . " ORDER BY BlendMasterID ASC $this->limit", 0); //SELECT tblBlendMaster.WSBID, tblBlendMaster.refWSID, tblBlendMaster.intVintage, tblMemberType.strMemberType, tblBlendMaster.refMemberID, tblMember.strMember, tblHarvestItem.refFarmID, tblFarm.strFarm, tblBlendMaster.strWOLCode return $rst; } //20180309 - v1.3 - added getWS() - maanie //20181211 - getWS(): added tblHarvestItem.refMemberID and tblMember.strMember - maanie private function getWS(){ global $xdb, $SystemSettings; //print_rr($args); $rst = $xdb->doQuery("SELECT tblHarvest.HarvestID AS WSID, '' AS strWietaCertificateNumber, '' AS strCertificateStatus, tblMemberType.strCategory, tblCellar.MemberID AS refCellarID, tblCellar.strMember AS strCellar, tblHarvestItem.refFarmID, tblFarm.strFarm, tblHarvestItem.refMemberID, tblMember.strMember ". $this->arrCallFROM[getWS] . $this->arrCallWHERE_Additional[getWS] ." AND HarvestID =" . $xdb->qs($this->args[WSID]) . " ORDER BY HarvestID $this->limit", 0); return $rst; } //20181106 - v1.7 - LMS v1.2: getMemberProfileLMS(MemberID) - maanie //20190221 - v1.7 - LMS v1.2: getMemberProfileLMS(MemberID) - reworked queries - maanie private function getMemberProfileLMS(){ global $xdb, $SystemSettings; //20190219 - getMemberProfileLMS: reworked the limit for this call: made it a tenth of the normal limit - maanie //$PageSize = $SystemSettings["PageSize"] / 10; //$this->limit = "LIMIT ". ($this->Page - 1) * $PageSize .", ". $PageSize; //print_rr($this->limit); die; if(isset($this->args[MemberID])) { $memberWhere = " AND tblMember.MemberID =" . $xdb->qs($this->args[MemberID]); } //print_rr($args); $rstMember = $xdb->doQuery(" SELECT tblMember.MemberID, strWOLCode, tblMember.strStatus AS strMemberStatus, strMemberTypeCode, strMemberType , 0 AS PreviousMemberID, 0 AS NewMemberID, strMember, strContactPosition, blnVinPro , strTel, strTel2, strFax, strCell, strEmail , strLegalEntityType, `strSetting:CorrespondenceLanguage` AS strLanguage , strPostalAddress1, strPostalAddress2, strPostalAddress3, strPostalAddress4, strPostalAddress5, strPostalAddressCode , strPhysicalAddress1, strPhysicalAddress2, strPhysicalAddress3, strPhysicalAddress4, strPhysicalAddress5, strPhysicalAddressCode , tblMember.dtLastEdit , '' as Farms ". $this->arrCallFROM[getMemberProfileLMS] ." ". $this->arrCallWHERE_Additional[getMemberProfileLMS] . $memberWhere . " ORDER BY tblMember.MemberID $this->limit", 0); //die; //$this->limit $MemberIDs_IN = ""; //while rstMember while($rowMember = $xdb->fetch_object($rstMember)) { //get Member-Farms $MemberIDs_IN .= $rowMember->MemberID . ","; //$arrFarms[$rowMember->MemberID] = $rstFarms; } //die; if($MemberIDs_IN <> "") { $MemberIDs_IN = rtrim($MemberIDs_IN, ','); $rstFarms = $xdb->doQuery("SELECT MemberID, tblFarm.FarmID, tblFarm.strFarm, tblFarm.strStatus AS strFarmStatus , CONCAT(LPAD(SawisRegionCode , 2, '0'), LPAD(SawisDistrictCode , 2, '0')) AS SawisLocationCode , CONCAT(strSawisRegion, ' \\\ ', strSawisDistrict) AS SawisLocation , WOCode, strCertifyBlendAs , CountryCode, strCountry , OverGeoCode, strOverGeo , GeoCode, strGeo , OverRegionCode, strOverRegion , vieLocation.RegionCode, strRegion , SubRegionCode, strSubRegion , vieLocation.DistrictCode, strDistrict , WardCode, strWard FROM vieMyFarms INNER JOIN ((tblFarm LEFT JOIN dwdSawisLocation ON (tblFarm.SawisDistrictCode = dwdSawisLocation.DistrictCode) AND (tblFarm.SawisRegionCode = dwdSawisLocation.RegionCode)) LEFT JOIN vieLocation ON tblFarm.refLocationID = vieLocation.LocationID) ON vieMyFarms.FarmID = tblFarm.FarmID WHERE MemberID IN ($MemberIDs_IN) AND vieMyFarms.blnOps = 1 GROUP BY tblFarm.FarmID, tblFarm.strFarm, tblFarm.strStatus, SawisLocationCode, SawisLocation, WOCode, strCertifyBlendAs, CountryCode, strCountry, OverGeoCode, strOverGeo, GeoCode, strGeo, OverRegionCode, strOverRegion, dwdSawisLocation.RegionCode, strRegion, SubRegionCode, strSubRegion, dwdSawisLocation.DistrictCode, strDistrict, WardCode, strWard ORDER BY FarmID ", 0); //die; $i = 0; while($rowFarm = $xdb->fetch_object($rstFarms)) { $MemberID = $rowFarm->MemberID; unset($rowFarm->MemberID); $arrFarms[$MemberID][$i] = $rowFarm; $i++; } //die; //print_rr($arrFarms); die; } //reset rstMember mysqli_data_seek($rstMember, 0); //if xml if($this->ReturnType == "xml") { $xmlMembers = ""; //while rstMember while($rowMember = $xdb->fetch_object($rstMember)) { //xmlFarms = NemoAPI::xml($rstFarms); $xmlFarms = NemoAPI::xml($arrFarms[$rowMember->MemberID]); //strip/replace Farm xml tags $xmlFarms = str_replace("", "", $xmlFarms); $xmlFarms = str_replace("", "", $xmlFarms); $xmlFarms = str_replace("", "", $xmlFarms); $xmlFarms = str_replace("", "", $xmlFarms); //echo htmlspecialchars($xmlFarms); $arrMember[$rowMember->MemberID] = $rowMember; $xmlMember = NemoAPI::xml($arrMember); //strip Member xml tags $xmlMember = str_replace("", "", $xmlMember); $xmlMember = str_replace("", "", $xmlMember); //echo htmlspecialchars($xmlMember); //str replace rowMember.Farms with xmlFarms $xmlMembers .= str_replace("", "" . $xmlFarms . "", $xmlMember); //print_rr( $xmlMembers); //clean vars $xmlMember = ""; unset($arrMember); } $xmlMembers .= ""; //OUTPUT $this->_content_type = "application/xml"; $this->response($xmlMembers); } elseif($this->ReturnType == "json") //else if json { $arrMembers = NemoAPI::getJsonArray($rstMember); //print_rr($arrMembers); //print_rr($arrFarms); //foreach Member in arrMembers[d][rows] add Farms foreach($arrMembers["data"]["rows"] as $im => $rowMember) { //$arrMembers["data"]["rows"]["Farms"] = $arrFarms[$rowMember["MemberID"]]; foreach($arrFarms[$rowMember["MemberID"]] as $i => $rowFarm) { $arrMembers["data"]["rows"][$im]["Farms"][$i] = $rowFarm; } } //print_rr($arrMembers); $result = json_encode($arrMembers); //OUTPUT $this->_content_type = "application/json"; $this->response($result); } return true; } //20200304 - LMS: getMemberIDListLMS() - maanie private function getMemberIDListLMS(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT tblMemberRelationship.refMemberID ". $this->arrCallFROM[getMemberIDListLMS] . $this->arrCallWHERE_Additional[getMemberIDListLMS] ." AND tblMemberRelationship.dtLastEdit > " . $xdb->qs($this->args[dtLastEdit]) . " GROUP BY tblMemberRelationship.refMemberID $this->limit", 0); return $rst; } //20190118 - v1.6 - WMS API: getWineMSFarmList - maanie //20190118 - DEV: Tested getWineMSFarmList() - maanie private function getWineMSFarmList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT vieWMS.FarmID, vieWMS.strFarm, vieWMS.WOCode, dwdLocation.strCertifyBlendAs, vieWMS.SawisRegionCode, strSawisRegion, vieWMS.SawisDistrictCode, strSawisDistrict ". $this->arrCallFROM[getWineMSFarmList] . $this->arrCallWHERE_Additional[getWineMSFarmList] ." GROUP BY vieWMS.FarmID, vieWMS.strFarm, vieWMS.WOCode, dwdLocation.strCertifyBlendAs, vieWMS.SawisRegionCode, strSawisRegion, vieWMS.SawisDistrictCode, strSawisDistrict ORDER BY FarmID $this->limit", 0); //die; return $rst; } //20190121 - v1.6 - WMS API: getWineMSBlockGUID - maanie //20190121 - DEV: Tested getWineMSBlockGUID() - maanie private function getWineMSBlockGUID(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT vieWMS.GUID, vieWMS.refCellarID, vieWMS.refMemberID, vieWMS.FarmID AS refFarmID, vieWMS.BlockID AS refBlockID, vieWMS.WMSBlockStatus AS strStatus ". $this->arrCallFROM[getWineMSBlockGUID] . $this->arrCallWHERE_Additional[getWineMSBlockGUID] ." GROUP BY vieWMS.GUID, vieWMS.refCellarID, vieWMS.refMemberID, vieWMS.FarmID, vieWMS.BlockID, vieWMS.WMSBlockStatus ORDER BY vieWMS.refCellarID, vieWMS.refMemberID, vieWMS.FarmID, vieWMS.BlockID $this->limit", 0); //die; return $rst; } //20190124 - v1.6 - WMS API: getWineMSMemberList - maanie //20190128 - DEV: Tested getWineMSMemberList() - maanie private function getWineMSMemberList(){ global $xdb, $SystemSettings; $rst = $xdb->doQuery("SELECT vieWMS.MemberID, vieWMS.strMember, 0 AS NewMemberID, vieWMS.MemberStatus AS strStatus, vieWMS.blnWineMS ". $this->arrCallFROM[getWineMSMemberList] . $this->arrCallWHERE_Additional[getWineMSMemberList] ." GROUP BY vieWMS.MemberID, vieWMS.strMember, vieWMS.MemberStatus, vieWMS.blnWineMS ORDER BY vieWMS.MemberID $this->limit", 0); //die; return $rst; } //20190125 - v1.6 - WMS API: getWineMSFarm - maanie //20190129 - DEV: Tested getWineMSFarm() - maanie private function getWineMSFarm(){ global $xdb, $SystemSettings; if(isset($this->args[refMemberID]) && isset($this->args[refFarmID])) { $strMemberWhere = ""; $rowMember = $xdb->getRowSQL("SELECT strMemberTypeCode FROM tblMember INNER JOIN tblMemberType ON tblMember.refMemberTypeID = tblMemberType.MemberTypeID WHERE MemberID = ". $xdb->qs($this->args[refMemberID]), 0); if($rowMember->strMemberTypeCode == "CELPRD") { $strMemberField = "syncProductionCellarID"; $refWineMS = "1"; } else { $strMemberField = "refMemberID"; $refWineMS = "3"; } $strMemberWhere .= " AND (vieWMS.$strMemberField =" . $xdb->qs($this->args[refMemberID]) ." AND refEntityID =" . $xdb->qs($this->args[refFarmID]) ." AND (MemberStatus like 'Active' AND tblMemberType.refWineMS = '$refWineMS'))"; } else //All mandatory parameters not supplied { $this->ReturnType = "text"; if(!array_key_exists("refMemberID", $this->args)) { $params .= "refMemberID,"; }; if(!array_key_exists("refFarmID", $this->args)) { $params .= "refFarmID"; }; $params = rtrim($params,','); //$this->record(self::$INVALID_STRUCTURE ."[$keys]"); //$this->response(self::$INVALID_STRUCTURE ."[$keys]", self::$HTTP_ERROR_CODE); return "Missing Parameters [$params]"; } $rst = $xdb->doQuery("SELECT FarmID, strFarm, vieWMS.WOCode, strCertifyBlendAs, SawisRegionCode, dwdSawisLocation.strSawisRegion, SawisDistrictCode, strSawisDistrict, MemberID, strMember, 0 AS NewMemberID, refMemberTypeID, vieWMS.strMemberType, MemberStatus AS strStatus, blnWineMS " . $this->arrCallFROM[getWineMSFarm] . $this->arrCallWHERE_Additional[getWineMSFarm]. $strMemberWhere ." GROUP BY FarmID, strFarm, vieWMS.WOCode, strCertifyBlendAs, SawisRegionCode, dwdSawisLocation.strSawisRegion, SawisDistrictCode, strSawisDistrict, MemberID, strMember, refMemberTypeID, vieWMS.strMemberType, MemberStatus, blnWineMS ORDER BY FarmID $this->limit", 0); //die; return $rst; } //20190129 - v1.6 - WMS API: getWineMSMember - maanie private function getWineMSMember(){ global $xdb, $SystemSettings; if(isset($this->args["CellarID"]) && isset($this->args["MemberID"])) { $strMemberWhere = ""; $rowMember = $xdb->getRowSQL("SELECT strMemberTypeCode FROM tblMember INNER JOIN tblMemberType ON tblMember.refMemberTypeID = tblMemberType.MemberTypeID WHERE MemberID = ". $xdb->qs($this->args["CellarID"]), 0); if($rowMember->strMemberTypeCode == "CELPRD") { $strMemberWhere .= " AND (vieWMS.syncProductionCellarID =". $xdb->qs($this->args["CellarID"]) ." AND vieWMS.OpsID = " . $xdb->qs($this->args["MemberID"]) ." AND (MemberStatus like 'Active' AND tblMemberType.refWineMS = '1'))"; $sqlMember = "opsMember.MemberID, opsMember.strMember, opsMember.refMemberTypeID"; } else { $strMemberWhere .= " AND (vieWMS.refMemberID =". $xdb->qs($this->args["CellarID"]) ." AND (MemberStatus like 'Active' AND tblMemberType.refWineMS = '3'))"; $sqlMember = "vieWMS.MemberID, vieWMS.strMember, vieWMS.refMemberTypeID"; } } else //All mandatory parameters not supplied { $this->ReturnType = "text"; if(!array_key_exists("CellarID", $this->args)) { $params .= "CellarID,"; }; if(!array_key_exists("MemberID", $this->args)) { $params .= "MemberID"; }; $params = rtrim($params,','); return "Missing Parameters [$params]"; } $rst = $xdb->doQuery("SELECT $sqlMember, vieWMS.strMemberType, FarmID, strFarm, vieWMS.WOCode, strCertifyBlendAs, SawisRegionCode, dwdSawisLocation.strSawisRegion, SawisDistrictCode, strSawisDistrict " . $this->arrCallFROM[getWineMSMember] . $this->arrCallWHERE_Additional[getWineMSMember]. $strMemberWhere ." GROUP BY vieWMS.MemberID, vieWMS.strMember, refMemberTypeID, vieWMS.strMemberType, FarmID, strFarm, vieWMS.WOCode, strCertifyBlendAs, SawisRegionCode, dwdSawisLocation.strSawisRegion, SawisDistrictCode, strSawisDistrict ORDER BY FarmID $this->limit", 0); //die; return $rst; } //20190201 - v1.6 - WMS API: getWineMSSawis1 - maanie private function getWineMSSawis1(){ global $xdb, $SystemSettings; if(isset($this->args["refMemberID"]) && isset($this->args["refFarmID"])) { $strMemberWhere = ""; $rowMember = $xdb->getRowSQL("SELECT strMemberTypeCode FROM tblMember INNER JOIN tblMemberType ON tblMember.refMemberTypeID = tblMemberType.MemberTypeID WHERE MemberID = ". $xdb->qs($this->args["CellarID"]), 0); if($rowMember->strMemberTypeCode == "CELPRD") { $strMemberWhere .= " AND (vieWMS.syncProductionCellarID =". $xdb->qs($this->args["refMemberID"]) ." AND vieWMS.refEntityID = " . $xdb->qs($this->args["refFarmID"]) ." AND (MemberStatus like 'Active' AND tblMemberType.refWineMS = '1'))"; } else { $strMemberWhere .= " AND (vieWMS.refMemberID =". $xdb->qs($this->args["refMemberID"]) ." AND vieWMS.refEntityID = " . $xdb->qs($this->args["refFarmID"]) ." AND (MemberStatus like 'Active' AND tblMemberType.refWineMS = '3'))"; } } else //All mandatory parameters not supplied { $this->ReturnType = "text"; if(!array_key_exists("refMemberID", $this->args)) { $params .= "refMemberID,"; }; if(!array_key_exists("refFarmID", $this->args)) { $params .= "refFarmID"; }; $params = rtrim($params,','); return "Missing Parameters [$params]"; } $rst = $xdb->doQuery("SELECT refEntityID AS refFarmID, BlockID, GUID, intYear, strBlock, intYearPlanted, CultivarCode, CultivarRootstockCode ,dblRowSpacingWide, dblRowSpacingNarrow, dblVineSpacing, strGPS, intVinesOpening, intVinesUprooted, intVinesGrafted, intVinesAmmended, intVinesClosing, dblHectareOpening, dblHectareUprooted, dblHectareGrafted, dblHectareAmmended, dblHectare, intYearUprooted, intYearGrafted, intYearAmmended, strIrrigation, strTrellis, BlockStatus AS strStatus, oldFarmID, newFarmID " . $this->arrCallFROM[getWineMSSawis1] . $this->arrCallWHERE_Additional[getWineMSSawis1]. $strMemberWhere ." GROUP BY refEntityID, BlockID, GUID, intYear, strBlock, intYearPlanted, CultivarCode, CultivarRootstockCode ,dblRowSpacingWide, dblRowSpacingNarrow, dblVineSpacing, strGPS, intVinesOpening, intVinesUprooted, intVinesGrafted, intVinesAmmended, intVinesClosing, dblHectareOpening, dblHectareUprooted, dblHectareGrafted, dblHectareAmmended, dblHectare, intYearUprooted, intYearGrafted, intYearAmmended, strIrrigation, strTrellis, BlockStatus, oldFarmID, newFarmID ORDER BY refFarmID $this->limit", 0); //die; return $rst; } //20190613 - v1.0 - CIS API: getCGABlockList - maanie private function getCGABlockList(){ global $xdb, $SystemSettings; if(isset($this->args[FarmID])) { $rst = $xdb->doQuery("SELECT * ". $this->arrCallFROM[getCGABlockList] ." ". $this->arrCallWHERE_Additional[getCGABlockList] ." AND intYear = ". $SystemSettings["CURENT_BLOCK_YEAR"] ." AND tblBlock.refFarmID =" . $xdb->qs($this->args[FarmID]) ." ORDER BY strBlock $this->limit", 0); } else { $this->ReturnType = "text"; return "Missing Parameters [FarmID]"; } return $rst; } //20190201 - v1.6 - WMS API: setWineMSCellar - maanie private function setWineMSCellar(){ global $xdb, $DT, $SystemSettings; if(!isset($this->args["refCellarID"])) return "Missing refCellarID"; //get xml data + check format $xml = simplexml_load_string($this->xmlData); //or die("Error: " . self::$INVALID_STRUCTURE); if($this->xmlData == null || $xml == null){ $this->record(self::$INVALID_STRUCTURE); $this->response(self::$INVALID_STRUCTURE, self::$HTTP_ERROR_CODE); return false; } //$rowData = (array) $xml->rows->row; //clear stg $xdb->doQuery("DELETE FROM stg_wmsBlock WHERE refCellarID =". $xdb->qs($this->args["refCellarID"]) ."", 0); $intTotalRows = $xml->rows->row->count(); $intRowCount = 1; //foreach rows.row insert foreach($xml->rows->row as $row) { $rowData = (array) $row; //set unique key $key = "refCellarID"; //check if key exists if(!array_key_exists($key, $rowData)) { $this->record(self::$INVALID_STRUCTURE ." [Missing $key]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $key]", self::$HTTP_ERROR_CODE); //return false; // continue looping } //validate required fields $requiredFields = array("GUID", "refCellarID", "refMemberID", "refFarmID", "refBlockID", "strBlock", "intYearPlanted", "CultivarCode", "CultivarRootstockCode", "dblRowSpacingWide", "dblRowSpacingNarrow", "dblVineSpacing", "intVinesClosing", "dblHectare", "IrrigationCode", "TrellisCode", "intVinesOpening", "intVinesAmmended"); //",dtCreatedDate", "dtUpdatedDate"); for($i = 0; $i < count($requiredFields); $i++) { if($rowData[$requiredFields[$i]] == "" || $rowData[$requiredFields[$i]] == null) { $this->record(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]", self::$HTTP_ERROR_CODE); } } //insert stg $cdb = new NemoDatabase("stg_wmsBlock", null, null, 0); $cdb->blnCustomID = true; //print_rr($cdb->Fields); //set db.fields = rowData $cdb->SetValues($rowData); //clean data: if($cdb->Fields["dtCreatedDate"] == "0000-00-00" || $cdb->Fields["dtCreatedDate"] == "") $cdb->Fields["dtCreatedDate"] = $DT; if($cdb->Fields["dtUpdatedDate"] == "0000-00-00" || $cdb->Fields["dtUpdatedDate"] == "") $cdb->Fields["dtUpdatedDate"] = $DT; //print_rr($cdb->FieldList); //unset missing fields foreach($cdb->FieldList as $key => $column) { if(!array_key_exists($column->name, $rowData) && $column->name != "dtCreatedDate" && $column->name != "dtUpdatedDate"){ unset($cdb->FieldList[$key]); } } //save $result = $cdb->Save(0,0); if($result->Error == 1) return "Error @ row $intRowCount :: " . $result->Message; /*else $intRowsInserted++;*/ $intRowCount++; } //clear wms $xdb->doQuery("DELETE FROM wmsBlock WHERE refCellarID =". $xdb->qs($this->args["refCellarID"]) ."", 0); //insert stg => wms $xdb->doQuery("INSERT INTO wmsBlock(GUID, refCellarID, refMemberID, refFarmID, refBlockID, strBlock, intYearPlanted, CultivarCode, CultivarRootstockCode, dblRowSpacingWide, dblRowSpacingNarrow, dblVineSpacing, intVinesClosing, dblHectare, strGPS, IrrigationCode, TrellisCode, intYearUprooted, intVinesUprooted, syncYearPlanted, intVinesOpening, intYearAmmended, intVinesAmmended, dtCreatedDate, dtUpdatedDate) SELECT GUID, refCellarID, refMemberID, refFarmID, refBlockID, strBlock, intYearPlanted, CultivarCode, CultivarRootstockCode, dblRowSpacingWide, dblRowSpacingNarrow, dblVineSpacing, intVinesClosing, dblHectare, strGPS, IrrigationCode, TrellisCode, intYearUprooted, intVinesUprooted, syncYearPlanted, intVinesOpening, intYearAmmended, intVinesAmmended, dtCreatedDate, dtUpdatedDate FROM stg_wmsBlock WHERE refCellarID =". $xdb->qs($this->args["refCellarID"]) ."", 0); $intRowsInserted = $xdb->affected_rows(); //print_rr($intRows); die; $rst = $xdb->doQuery("SELECT 'true' AS success ,'WineMS Cellar set' AS message , $intRowsInserted AS 'recordsInserted'"); return $rst; } //20190205 - v1.6 - WMS API: setWineMSBlockActivity - maanie private function setWineMSBlockActivity(){ global $xdb, $DT, $SystemSettings; if(!isset($this->args["refMemberID"])) return "Missing refMemberID"; //get xml data + check format $xml = simplexml_load_string($this->xmlData); //or die("Error: " . self::$INVALID_STRUCTURE); if($this->xmlData == null || $xml == null){ $this->record(self::$INVALID_STRUCTURE); $this->response(self::$INVALID_STRUCTURE, self::$HTTP_ERROR_CODE); return false; } //$rowData = (array) $xml->rows->row; //clear stg $xdb->doQuery("DELETE FROM stg_wmsBlockActivity WHERE refCellarID =". $xdb->qs($this->args["refMemberID"]) ."", 0); $intTotalRows = $xml->rows->row->count(); $intRowCount = 1; //foreach rows.row insert foreach($xml->rows->row as $row) { $rowData = (array) $row; //set unique key $key = "refMemberID"; //check if key exists if(!array_key_exists($key, $rowData)) { $this->record(self::$INVALID_STRUCTURE ." [Missing $key]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $key]", self::$HTTP_ERROR_CODE); //return false; // continue looping } //validate required fields $requiredFields = array("GUID", "refCellarID", "refMemberID", "refFarmID", "refBlockID", "strMember", "strFarm", "strBlock", "CultivarCode", "strCultivar", "CultivarRootstockCode", "strCultivarRootstock", "IrrigationCode", "strIrrigation", "TrellisCode", "strTrellis", "intYearPlanted", "intPressYear", "intChangeYear", "strWMSActivity", "intVines", "dblHectare"); //",dtCreatedDate", "dtUpdatedDate"); for($i = 0; $i < count($requiredFields); $i++) { if($rowData[$requiredFields[$i]] == "" || $rowData[$requiredFields[$i]] == null) { $this->record(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]", self::$HTTP_ERROR_CODE); } } //insert stg $cdb = new NemoDatabase("stg_wmsBlockActivity", null, null, 0); //$cdb->blnCustomID = true; //print_rr($cdb->Fields); //set db.fields = rowData $cdb->SetValues($rowData); //clean data: if($cdb->Fields["dtCreatedDate"] == "0000-00-00" || $cdb->Fields["dtCreatedDate"] == "") $cdb->Fields["dtCreatedDate"] = $DT; if($cdb->Fields["dtUpdateDate"] == "0000-00-00" || $cdb->Fields["dtUpdateDate"] == "") $cdb->Fields["dtUpdateDate"] = $DT; //print_rr($cdb->Fields); die; //unset missing fields foreach($cdb->FieldList as $key => $column) { if(!array_key_exists($column->name, $rowData) && $column->name != "dtCreatedDate" && $column->name != "dtUpdateDate"){ unset($cdb->FieldList[$key]); } } //save $result = $cdb->Save(0,0);// die; if($result->Error == 1) return "Error @ row $intRowCount :: " . $result->Message; /* else $intRowsInserted++;*/ $intRowCount++; } //clear wms $xdb->doQuery("DELETE FROM wmsBlockActivity WHERE refCellarID =". $xdb->qs($this->args["refMemberID"]) ."", 0); //insert stg => wms $xdb->doQuery("INSERT INTO wmsBlockActivity(GUID, refCellarID, refMemberID, refFarmID, refBlockID, strMember, strFarm, strBlock, CultivarCode, strCultivar, CultivarRootstockCode, strCultivarRootstock, IrrigationCode, strIrrigation, TrellisCode, strTrellis, intYearPlanted, intPressYear, intChangeYear, strWMSActivity, intVines, dblHectare, dtCreatedDate, dtUpdateDate) SELECT GUID, refCellarID, refMemberID, refFarmID, refBlockID, strMember, strFarm, strBlock, CultivarCode, strCultivar, CultivarRootstockCode, strCultivarRootstock, IrrigationCode, strIrrigation, TrellisCode, strTrellis, intYearPlanted, intPressYear, intChangeYear, strWMSActivity, intVines, dblHectare, dtCreatedDate, dtUpdateDate FROM stg_wmsBlockActivity WHERE refCellarID =". $xdb->qs($this->args["refMemberID"]) ."", 0); $intRowsInserted = $xdb->affected_rows(); //print_rr($intRows); die; $rst = $xdb->doQuery("SELECT 'true' AS success ,'WineMS Block Activity set' AS message , $intRowsInserted AS 'recordsInserted'"); return $rst; } //20170408 - v1.3 added setCertificationRecord() - maanie //20170411 - v1.3 DEV: Tested setCertificationRecord() - maanie //20170424 - v1.3 QA: looks ok - pj //20170717 - v1.3 QA: Tested - maanie /* Trigger required on UPDATE of transaction as follows: If CERT_STATUS changes to ‘ST’ OR ‘CS’ OR ‘ES’ OR ‘KS’ then SET CERT_SENT_YN = ‘N’ AND CERT_STAMP_DATE = GetDate(). */ private function setCertificationRecord(){ global $xdb, $SystemSettings; $this->ReturnType = "text"; //get xml data + check format $xml = simplexml_load_string($this->xmlData); //or die("Error: " . self::$INVALID_STRUCTURE); if($this->xmlData == null || $xml == null){ $this->record(self::$INVALID_STRUCTURE); $this->response(self::$INVALID_STRUCTURE, self::$HTTP_ERROR_CODE); return false; } $rowData = (array) $xml->rows->row; //set unique key $key = "TransactionID"; //check if key exists if(!array_key_exists($key, $rowData)) { $this->record(self::$INVALID_STRUCTURE ." [Missing $key]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $key]", self::$HTTP_ERROR_CODE); //return false; // continue looping } //20170424 - v1.3 added check for required fields - maanie //validate required fields $requiredFields = array("ApplicationID", "MemberID", "intVintage", "strOrigin", "strCultivarCode", "strCertificationStatusCode"); for($i = 0; $i < count($requiredFields); $i++) { if($rowData[$requiredFields[$i]] == "" || $rowData[$requiredFields[$i]] == null) { $this->record(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]", self::$HTTP_ERROR_CODE); } } //update/insert record(s) $rowCT = $xdb->getRowSQL("SELECT * FROM tblCertification WHERE TransactionID = ". $xdb->qs($rowData["TransactionID"]) , 0); if(!$rowCT || $rowData["TransactionID"] == null){ //insert $cdb = new NemoDatabase("tblCertification", null, null, 0); $TransactionID = null; }else{ //update $cdb = new NemoDatabase("tblCertification", $rowData["TransactionID"], null, 0); $TransactionID = $rowData["TransactionID"]; $strCertificationStatusCode = $cdb->Fields["strCertificationStatusCode"]; //get the original CSC [rule: If CERT_STATUS changes] } $cdb->blnCustomID = true; //print_rr($cdb->Fields); //set db.fields = rowData $cdb->SetValues($rowData); //clean data: if($cdb->Fields["dtBottleDate"] == "0000-00-00" || $cdb->Fields["dtBottleDate"] == "") $cdb->Fields["dtBottleDate"] = "NULL"; if($cdb->Fields["dtTasteDate"] == "0000-00-00" || $cdb->Fields["dtTasteDate"] == "") $cdb->Fields["dtTasteDate"] = "NULL"; if($cdb->Fields["dtRaadDate"] == "0000-00-00" || $cdb->Fields["dtRaadDate"] == "") $cdb->Fields["dtRaadDate"] = "NULL"; if($cdb->Fields["dtVerifiedDate"] == "0000-00-00" || $cdb->Fields["dtVerifiedDate"] == "") $cdb->Fields["dtVerifiedDate"] = "NULL"; //print_rr($cdb->FieldList); //unset missing fields foreach($cdb->FieldList as $key => $column) { if(!array_key_exists($column->name, $rowData)){ unset($cdb->FieldList[$key]); } } //print_rr($cdb->Fields); //save $result = $cdb->Save(0,0); // print_rr($cdb); // die("kjbsdbhfh"); //get last id if insert if($TransactionID == null) { $TransactionID = $cdb->getLastID(); } else //update { //if original cert_status != current cert_status AND If CERT_STATUS changes to ‘ST’ OR ‘CS’ OR ‘ES’ OR ‘KS’ then update CERT_SENT_YN = ‘N’, CERT_STAMP_DATE = CURDATE() if(($strCertificationStatusCode != $rowData["strCertificationStatusCode"]) && ($rowData["strCertificationStatusCode"] == "ST" || $rowData["strCertificationStatusCode"] == "CS" || $rowData["strCertificationStatusCode"] == "ES" || $rowData["strCertificationStatusCode"] == "KS")) { $xdb->doQuery("UPDATE tblCertification SET blnSent = 0, dtCertificateDate = NOW() WHERE TransactionID = ". $xdb->qs($rowData["TransactionID"]).""); } } //print_rr($rowData["TransactionID"]); //if success return TID if($result->Error == 1) { return $result->Message; } else return $TransactionID; } //20170413 - v1.3 added setCertificationResultsRecord() - maanie //20170413 - v1.3 DEV: Tested setCertificationResultsRecord() - maanie //20200113 - setCertificationResultsRecord() - changed to set TransactionID from xml - maanie private function setCertificationResultsRecord(){ global $xdb, $SystemSettings; $this->ReturnType = "text"; //get xml data + check format $xml = simplexml_load_string($this->xmlData); //or die("Error: " . self::$INVALID_STRUCTURE); if($this->xmlData == null || $xml == null){ $this->record(self::$INVALID_STRUCTURE); $this->response(self::$INVALID_STRUCTURE, self::$HTTP_ERROR_CODE); return false; } $rowData = (array) $xml->rows->row; //set unique key $key = "TransactionID"; //check if key exists if(!array_key_exists($key, $rowData)) { $this->record(self::$INVALID_STRUCTURE ." [Missing $key]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $key]", self::$HTTP_ERROR_CODE); //return false; // continue looping } //20170424 - v1.3 added check for required fields - maanie //validate required fields $requiredFields = array("TransactionID", "LabID", "ApplicationID"); for($i = 0; $i < count($requiredFields); $i++) { if($rowData[$requiredFields[$i]] == "" || $rowData[$requiredFields[$i]] == null) { $this->record(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]", self::$HTTP_ERROR_CODE); } } //update/insert record(s) $rowTR = $xdb->getRowSQL("SELECT * FROM tblLabAnalysis WHERE TransactionID = ". $xdb->qs($rowData["TransactionID"]) , 0); if(!$rowTR){ // || $rowData["TransactionID"] == null){ //insert $cdb = new NemoDatabase("tblLabAnalysis", null, null, 0); //$TransactionID = null; }else{ //update $cdb = new NemoDatabase("tblLabAnalysis", $rowTR->ID, null, 0); //$TransactionID = $rowData["TransactionID"]; } $TransactionID = $rowData["TransactionID"]; //add PK to insert $cdb->blnCustomID = true; //print_rr($cdb->Fields); //set db.fields = rowData $cdb->SetValues($rowData); //clean data: if($cdb->Fields["dtBoardDate"] == "0000-00-00" || $cdb->Fields["dtBoardDate"] == "") $cdb->Fields["dtBoardDate"] = "NULL"; if($cdb->Fields["dtLabAnalysisStampDate"] == "0000-00-00" || $cdb->Fields["dtLabAnalysisStampDate"] == "") $cdb->Fields["dtLabAnalysisStampDate"] = "NULL"; //print_rr($cdb->FieldList); //unset missing fields foreach($cdb->FieldList as $key => $column) { if(!array_key_exists($column->name, $rowData)){ //20200122 dont unset ID if it has not been set, we get the id from the transaction ID -jacques if($column->name != "ID") { unset($cdb->FieldList[$key]); } } } //save $result = $cdb->Save(0,0); // print_rr($cdb); //get last id if insert /* if($TransactionID == null) { $TransactionID = $cdb->getLastID(); }*/ //print_rr($rowData["TransactionRequestID"]); //if success return TRID if($result->Error == 1) { return $result->Message; } else return $TransactionID; } //20170420 - v1.3 added setWineExportRecord() - maanie //20170420 - v1.3 DEV: Tested setWineExportRecord() - maanie private function setWineExportRecord(){ global $xdb, $SystemSettings; $this->ReturnType = "text"; //get xml data + check format $xml = simplexml_load_string($this->xmlData); //or die("Error: " . self::$INVALID_STRUCTURE); if($this->xmlData == null || $xml == null){ $this->record(self::$INVALID_STRUCTURE); $this->response(self::$INVALID_STRUCTURE, self::$HTTP_ERROR_CODE); return false; } $rowData = (array) $xml->rows->row; //set unique key $key = "ExportCertificateID"; //check if key exists if(!array_key_exists($key, $rowData)) { $this->record(self::$INVALID_STRUCTURE ." [Missing $key]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $key]", self::$HTTP_ERROR_CODE); //return false; // continue looping } //validate required data //TODO!!! //$rowData->CertificateID //print_rr($rowData["CertificateID"]); //update/insert record(s) $rowTR = $xdb->getRowSQL("SELECT * FROM tblExportTransaction WHERE ExportCertificateID = ". $xdb->qs($rowData["ExportCertificateID"]) , 0); if(!$rowTR || $rowData["ExportCertificateID"] == null){ //insert $cdb = new NemoDatabase("tblExportTransaction", null, null, 0); $ExportCertificateID = null; }else{ //update $cdb = new NemoDatabase("tblExportTransaction", $rowData["ExportCertificateID"], null, 0); $ExportCertificateID = $rowData["ExportCertificateID"]; } //add PK to insert $cdb->blnCustomID = true; //print_rr($cdb->Fields); //set db.fields = rowData $cdb->SetValues($rowData); //clean data: /* if($cdb->Fields["dtBoardDate"] == "0000-00-00" || $cdb->Fields["dtBoardDate"] == "") $cdb->Fields["dtBoardDate"] = "NULL"; if($cdb->Fields["dtLabAnalysisStampDate"] == "0000-00-00" || $cdb->Fields["dtLabAnalysisStampDate"] == "") $cdb->Fields["dtLabAnalysisStampDate"] = "NULL";*/ //print_rr($cdb->FieldList); //unset missing fields foreach($cdb->FieldList as $key => $column) { if(!array_key_exists($column->name, $rowData)){ unset($cdb->FieldList[$key]); } } //save $result = $cdb->Save(0,0); // print_rr($cdb); //get last id if insert if($ExportCertificateID == null) { $ExportCertificateID = $cdb->getLastID(); } //print_rr($rowData["ExportCertificateID"]); //if success return TRID if($result->Error == 1) { return $result->Message; } else return $ExportCertificateID; } //20170531 - v1.3 implemented setWietaMembership() - maanie //20170531 - v1.3 DEV: Tested setWietaMembership() - maanie private function setWietaMembership(){ global $xdb, $SystemSettings; $this->ReturnType = "text"; //get xml data + check format $xml = simplexml_load_string($this->xmlData); //or die("Error: " . self::$INVALID_STRUCTURE); if($this->xmlData == null || $xml == null){ $this->record(self::$INVALID_STRUCTURE); $this->response(self::$INVALID_STRUCTURE, self::$HTTP_ERROR_CODE); return false; } $rowData = (array) $xml->rows->row; //set unique key $key = "MemberID"; //check if key exists if(!array_key_exists($key, $rowData)) { $this->record(self::$INVALID_STRUCTURE ." [Missing $key]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $key]", self::$HTTP_ERROR_CODE); //return false; // continue looping } //validate required data //TODO!!! //update/insert record(s) $rowTR = $xdb->getRowSQL("SELECT * FROM tblWietaCertification WHERE MemberID = ". $xdb->qs($rowData["MemberID"]) , 0); //print_rr("sfd"); if(!$rowTR || $rowData["MemberID"] == null){ //insert $cdb = new NemoDatabase("tblWietaCertification", null, null, 0); $MemberID = null; }else{ //update $cdb = new NemoDatabase("tblWietaCertification", $rowData["MemberID"], null, 0); $MemberID = $rowData["MemberID"]; } //print_rr("sfdewr"); //add PK to insert $cdb->blnCustomID = true; //print_rr($cdb->Fields); //set db.fields = rowData $cdb->SetValues($rowData); //print_rr($rowData); //20180327 - setWietaMembership(): default dtCertificationDate to today and dtCertificationExpiryDate to NULL - maanie if(!isset($rowData["dtCertificationDate"]) || $rowData["dtCertificationDate"] == "") { $cdb->Fields["dtCertificationDate"] = date("Y-m-d"); } if(!isset($rowData["dtCertificationExpiryDate"]) || $rowData["dtCertificationExpiryDate"] == "") { $cdb->Fields["dtCertificationExpiryDate"] = "NULL"; } //print_rr($cdb->Fields); //unset missing fields foreach($cdb->FieldList as $key => $column) { if(!array_key_exists($column->name, $rowData)){ unset($cdb->FieldList[$key]); } } //save $result = $cdb->Save(0,1); //get last id if insert if($MemberID == null) { $MemberID = $cdb->getLastID(); } //if success return TRID if($result->Error == 1) { return $result->Message; } else return true; } private function setVinProMembers(){ global $xdb, $SystemSettings; //check data $xml = simplexml_load_string($this->xmlData);// or die("Error: Cannot create object"); if($this->xmlData == null || $xml == null){ $this->record(self::$INVALID_STRUCTURE); $this->response(self::$INVALID_STRUCTURE, self::$HTTP_ERROR_CODE); return false; } print_rr($xml); $arrColumns = array("MemberID","blnVinPro"); //check data structure: read data.rows.row.foreach() $rowData = (array) $xml->rows->row[0]; print_rr($xml->rows->row[0]); print_rr($rowData); foreach($arrColumns as $i => $key) {//vd($key); vd(array_key_exists($key, $rowData)); $keys .= "$key;"; if(!array_key_exists($key, $rowData)){ $this->record(self::$INVALID_STRUCTURE ."[$keys]"); $this->response(self::$INVALID_STRUCTURE ."[$keys]", self::$HTTP_ERROR_CODE); return false; } } //import records into tmp table $xdb->doQuery("TRUNCATE TABEL tmpVinPro"); foreach ($xml->rows->row as $i => $row) {//print_rr($row); $xdb->doQuery("INSERT INTO tmpVinPro(MemberID,blnVinPro) VALUE (". $xdb->qs($row->MemberID) .",". $xdb->qs($row->blnVinPro) .")"); } //update tblMember.blnVinPro = tmpVinPro.blnVinPro $xdb->doQuery("UPDATE tblMember INNER JOIN tmpVinPro ON tmpVinPro.MemberID = tblMember.MemberID SET tblMember.blnVinPro = tmpVinPro.blnVinPro"); return true; } //20190121 - v1.6 - WMS API: setWineMSBlockGUID - maanie //20190121 - NOT DEV TESTED PROPERLY! TODO: DEV TEST - maanie private function setWineMSBlockGUID(){ global $xdb, $SystemSettings; $this->ReturnType = "text"; //get xml data + check format $xml = simplexml_load_string($this->xmlData); // or die("Error: " . self::$INVALID_STRUCTURE); if($this->xmlData == null || $xml == null){ $this->record(self::$INVALID_STRUCTURE); $this->response(self::$INVALID_STRUCTURE, self::$HTTP_ERROR_CODE); return false; } $rowData = (array) $xml->rows->row; //set unique key $key = "GUID"; //check if key exists if(!array_key_exists($key, $rowData)) { $this->record(self::$INVALID_STRUCTURE ." [Missing $key]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $key]", self::$HTTP_ERROR_CODE); //return false; // continue looping } //validate required fields $requiredFields = array("refCellarID", "refMemberID", "refFarmID", "refBlockID", "strStatus"); for($i = 0; $i < count($requiredFields); $i++) { if($rowData[$requiredFields[$i]] == "" || $rowData[$requiredFields[$i]] == null) { $this->record(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]"); $this->response(self::$INVALID_STRUCTURE ." [Missing $requiredFields[$i]]", self::$HTTP_ERROR_CODE); } } //update/insert record(s) $rowG = $xdb->getRowSQL("SELECT * FROM wmsBlockGUID WHERE GUID = ". $xdb->qs($rowData["GUID"]) , 1); //print_rr("sfd"); if(!$rowG || $rowData["GUID"] == null){ //insert $cdb = new NemoDatabase("wmsBlockGUID", null, null, 0); }else{ //update $cdb = new NemoDatabase("wmsBlockGUID", $rowG->id, null, 0); $cdb->Fields["id"] = $rowG->id; } //add PK to insert //$cdb->blnCustomID = true; //print_rr($cdb->Fields); //set db.fields = rowData $cdb->SetValues($rowData); //print_rr($cdb->Fields); //unset missing fields foreach($cdb->FieldList as $key => $column) { if(!array_key_exists($column->name, $rowData) && $column->name != "id"){ unset($cdb->FieldList[$key]); } } //save $result = $cdb->Save(0,1); //if success return GUID if($result->Error == 1) { return $result->Message; } else return true; } //*********MEMBER CAllS START************************************************* //wip // private function authenticate($args) // { // global $xdb, $SystemSettings; // //print_rr($args);die; // //still need to check vendor key; // $this->SessionData->MemberID = qs($args[MemberID]); // $sql = "SELECT * FROM vieMember INNER JOIN apiVendorMember ON vieMember.MemberID = apiVendorMember.MemberID // WHERE vieMember.MemberID =\"". $this->SessionData->MemberID ."\" AND apiVendorMember.VendorKey = \"". qs($this->VendorKey) ."\""; // $objMember = $xdb->getRowSQL($sql); // $this->_content_type = "text/html"; // if($objMember){ // // // //create token // $this->SessionToken = $this->generateToken(); // $this->response($this->SessionToken, 200); // }else{ // $this->response($this::$INVALID_MEMBER, self::$HTTP_ERROR_CODE); // } // //save token // } //*********Extras************************ //20160927 - remove fields from the rst based on vendor - pj /*private function cleanVendorRST($rst) { global $xdb, $SystemSettings; //vd($rst); die("oknsdfn"); if(is_object($rst) || is_array($rst)) //if($rst is an object||array) { //$rstExclude = $xdb->doQuery("SELECT * FROM apiVendorField WHERE VendorKey IN ('*',".$xdb->qs($this->VendorKey).") AND strCall = ".$xdb->qs($this->Call) $rstExclude = $xdb->doQuery("SELECT * FROM apiVendorField WHERE VendorKey IN ('*',".$xdb->qs($this->VendorKey).") AND strCall IN ('*', ".$xdb->qs($this->Call) . ")" , 0); //20170920 - v1.3 - added field exclude for every call for every vendor - maanie while($row = $xdb->fetch_object($rstExclude)) { $arrExclude[$row->strField] = true; }//print_rr($arrExclude); die("okdnfjg"); //ini $i=0; while($row = $xdb->fetch_object($rst)) {//print_rr($row); foreach($row as $key => $value) {//print_rr($row->$key); $keyValue = $key; $key = str_replace(":", "_", $key); if($arrExclude[$key] != true) $arrData[$i][$key] = $row->$keyValue; //20170810 - clean vendor rst func: use original key to set value - maanie }//print_rr($arrData); die(); //$arrData[] = $row; $i++; } return $arrData; }else{ return $rst; } //print_rr($arrData); die; }*/ //20180927 - cleanVendorRST:: Inverted to include fields as opposed to excluding them - maanie private function cleanVendorRST($rst) { //print_rr($rst); global $xdb, $SystemSettings; //vd($rst); die("oknsdfn"); if(is_object($rst) || is_array($rst)) //if($rst is an object||array) { //$rstExclude = $xdb->doQuery("SELECT * FROM apiVendorField WHERE VendorKey IN ('*',".$xdb->qs($this->VendorKey).") AND strCall = ".$xdb->qs($this->Call) $rstInclude = $xdb->doQuery("SELECT * FROM apiVendorField WHERE VendorKey IN ('*',".$xdb->qs($this->VendorKey).") AND strCall IN (".$xdb->qs($this->Call) . ") AND strLastUser = 'INCLUDE'" , 0); //print_rr($rstInclude); die; while($row = $xdb->fetch_object($rstInclude)) { if($row->strField == "*") { $includeAllFields = true; break; } $arrInclude[$row->strField] = true; }//print_rr($arrInclude); die("okdnfjg"); //ini $i=0; while($row = $xdb->fetch_object($rst)) {//print_rr($row); foreach($row as $key => $value) //foreach($arrInclude as $key => $blnIncluded) {//print_rr($row->$key); $keyValue = $key; $key = str_replace(":", "_", $key); if(isset($arrInclude[$key]) || $includeAllFields == true) $arrData[$i][$key] = $row->$keyValue; }//print_rr($arrData); die(); //$arrData[] = $row; $i++; } //print_rr($arrData); die; return $arrData; }else{ return $rst; } } //20170731 - checks whether user can log into the system - maanie private function checkAccess($rowUser) { global $xdb, $SystemSettings; /*this can be done in checkVendor(), make the values you need a class attribute: $rowVendor = $xdb->getRowSQL("SELECT strSite FROM apiVendor WHERE VendorKey = '" . $this->VendorKey . "'");*/ /*you set a $rowUser right before you call this function, just pass the $rowUser: $rowUser = $xdb->getRowSQL("SELECT * FROM sysUser WHERE UserID = $UserID");*/ $blnSite = "blnSite_" . $this->VendorSite; if($rowUser->$blnSite == 1) return true; else return false; } //*********ENCODING*************************************************************** public static function getJsonArray($arrData) { global $xdb, $SystemSettings; $result["data"] = array(); $result["data"]["rows"] = array(); // while($row = $xdb->fetch_object($rst)) // { // //if(isset($row->refInspectorID)) unset($row->refInspectorID); // needs to change: check field name per vendor - pj 20160919. see: //20160927 - remove fields from the rst based on vendor - pj // $result[data][rows][] = $row; // } foreach($arrData as $i => $row) { //20180405 - added UTF-8 encoding to JSON function - maanie foreach($row as $key => $value) { $row[$key] = htmlspecialchars($value, ENT_XML1,'UTF-8'); } $results["data"]["rows"][] = $row; } return $results; } public static function json($arrData) { global $xdb, $SystemSettings; $results = NemoAPI::getJsonArray($arrData); //print_rr($results); die; //echo json_encode($result); //echo count($result[data][rows]); //$results["data"]["rows"] = $arrData; //echo json_encode($result); //print_rr($results); //die("wip"); //wtf!?!? data load fine from a rst (while($row = $xdb->fetch_object($rst))), but not from the array!? return json_encode($results); } /* * Encode array into XML */ public static function xml($arrData) { global $xdb, $SystemSettings; foreach($arrData as $i => $row) { $xml .= ""; foreach($row as $key => $value) { // //if(strpos($key,":") >>= 0) // $key = str_replace(":", "_", $key); //if($key == "refInspectorID") continue; // needs to change: check field name per vendor - pj 20160919 //$value = htmlentities($value, ENT_QUOTES,'UTF-8'); $value = htmlspecialchars($value, ENT_XML1,'UTF-8'); //http://stackoverflow.com/questions/3957360/generating-xml-document-in-php-escape-characters $value = utf8_for_xml($value); //20180823 - UTF-8 checks - maanie $xml .= "<$key>$value"; //xml rncode $key & $value } $xml .= ""; } /*add //20180816 - UTF-8 checks - pj*/ return "$xml"; } } ?>