PageRenderTime 6ms CodeModel.GetById 148ms app.highlight 44ms RepoModel.GetById 34ms app.codeStats 1ms

/wp-content/plugins/wp-lister-for-ebay/classes/model/ListingsModel.php

https://bitbucket.org/sanders_nick/my-maxi-skirt
PHP | 1074 lines | 706 code | 254 blank | 114 comment | 68 complexity | 856c493ed9def5538d5cc330dcb4d41a MD5 | raw file
   1<?php
   2/**
   3 * ListingsModel class
   4 *
   5 * responsible for managing listings and talking to ebay
   6 * 
   7 */
   8
   9class ListingsModel extends WPL_Model {
  10
  11	var $_session;
  12	var $_cs;
  13
  14	function ListingsModel()
  15	{
  16		global $wpl_logger;
  17		$this->logger = &$wpl_logger;
  18
  19		global $wpdb;
  20		$this->tablename = $wpdb->prefix . 'ebay_auctions';
  21	}
  22
  23
  24	function getPageItems( $current_page, $per_page ) {
  25		global $wpdb;
  26
  27        $orderby = (!empty($_REQUEST['orderby'])) ? $_REQUEST['orderby'] : 'id'; //If no sort, default to title
  28        $order = (!empty($_REQUEST['order'])) ? $_REQUEST['order'] : 'desc'; //If no order, default to asc
  29        $offset = ( $current_page - 1 ) * $per_page;
  30
  31        // filter listing_status
  32        $where_sql = '';
  33		$listing_status = ( isset($_REQUEST['listing_status']) ? $_REQUEST['listing_status'] : 'all');
  34		if ( $listing_status != 'all' ) {
  35			$where_sql = "WHERE status = '".$listing_status."'";
  36		} 
  37
  38        // filter search_query
  39		$search_query = ( isset($_REQUEST['s']) ? $_REQUEST['s'] : false);
  40		if ( $search_query ) {
  41			$where_sql = "WHERE auction_title LIKE '%".$search_query."%'";
  42		} 
  43
  44
  45        // get items
  46		$items = $wpdb->get_results("
  47			SELECT *
  48			FROM $this->tablename
  49            $where_sql
  50			ORDER BY $orderby $order
  51            LIMIT $offset, $per_page
  52		", ARRAY_A);
  53
  54		// get total items count - if needed
  55		if ( ( $current_page == 1 ) && ( count( $items ) < $per_page ) ) {
  56			$this->total_items = count( $items );
  57		} else {
  58			$this->total_items = $wpdb->get_var("
  59				SELECT COUNT(*)
  60				FROM $this->tablename
  61				ORDER BY $orderby $order
  62			");			
  63		}
  64
  65		return $items;
  66	}
  67
  68
  69
  70	/* the following methods could go into another class, since they use wpdb instead of EbatNs_DatabaseProvider */
  71
  72	function getAll() {
  73		global $wpdb;
  74		$items = $wpdb->get_results("
  75			SELECT *
  76			FROM $this->tablename
  77			ORDER BY id DESC
  78		", ARRAY_A);
  79
  80		return $items;
  81	}
  82
  83	function getItem( $id ) {
  84		global $wpdb;
  85		$item = $wpdb->get_row("
  86			SELECT *
  87			FROM $this->tablename
  88			WHERE id = '$id'
  89		", ARRAY_A);
  90
  91		$item['profile_data'] = $this->decodeObject( $item['profile_data'], true );
  92		// $item['details'] = $this->decodeObject( $item['details'] );
  93
  94		return $item;
  95	}
  96
  97	function deleteItem( $id ) {
  98		global $wpdb;
  99		$wpdb->query("
 100			DELETE
 101			FROM $this->tablename
 102			WHERE id = '$id'
 103		");
 104	}
 105
 106	function getItemByEbayID( $id ) {
 107		global $wpdb;
 108		$item = $wpdb->get_row("
 109			SELECT *
 110			FROM $this->tablename
 111			WHERE ebay_id = '$id'
 112		");
 113		if (!$item) return false;
 114		
 115		$item->profile_data = $this->decodeObject( $item->profile_data, true );
 116		$item->details = $this->decodeObject( $item->details );
 117
 118		return $item;
 119	}
 120
 121	function getTitleFromItemID( $id ) {
 122		global $wpdb;
 123		$item = $wpdb->get_var("
 124			SELECT auction_title
 125			FROM $this->tablename
 126			WHERE ebay_id = '$id'
 127		");
 128		return $item;
 129	}
 130
 131	function getEbayIDFromID( $id ) {
 132		global $wpdb;
 133		$item = $wpdb->get_var("
 134			SELECT ebay_id
 135			FROM $this->tablename
 136			WHERE id = '$id'
 137		");
 138		return $item;
 139	}
 140	function getEbayIDFromPostID( $post_id ) {
 141		global $wpdb;
 142		$item = $wpdb->get_var("
 143			SELECT ebay_id
 144			FROM $this->tablename
 145			WHERE post_id = '$post_id'
 146		");
 147		return $item;
 148	}
 149	function getStatus( $id ) {
 150		global $wpdb;
 151		$item = $wpdb->get_var("
 152			SELECT status
 153			FROM $this->tablename
 154			WHERE id = '$id'
 155		");
 156		return $item;
 157	}
 158	function getStatusFromPostID( $post_id ) {
 159		global $wpdb;
 160		$item = $wpdb->get_var("
 161			SELECT status
 162			FROM $this->tablename
 163			WHERE post_id = '$post_id'
 164			ORDER BY id DESC
 165		");
 166		return $item;
 167	}
 168	function getListingIDFromPostID( $post_id ) {
 169		global $wpdb;
 170		$item = $wpdb->get_var("
 171			SELECT id
 172			FROM $this->tablename
 173			WHERE post_id = '$post_id'
 174			ORDER BY id DESC
 175		");
 176		return $item;
 177	}
 178	function getAllListingsFromPostID( $post_id ) {
 179		global $wpdb;
 180		$items = $wpdb->get_results("
 181			SELECT *
 182			FROM $this->tablename
 183			WHERE post_id = '$post_id'
 184			ORDER BY id DESC
 185		");
 186		return $items;
 187	}
 188	function getViewItemURLFromPostID( $post_id ) {
 189		global $wpdb;
 190		$item = $wpdb->get_var("
 191			SELECT ViewItemURL
 192			FROM $this->tablename
 193			WHERE post_id = '$post_id'
 194			ORDER BY id DESC
 195		");
 196		return $item;
 197	}
 198
 199	function getStatusSummary() {
 200		global $wpdb;
 201		$result = $wpdb->get_results("
 202			SELECT status, count(*) as total
 203			FROM $this->tablename
 204			GROUP BY status
 205		");
 206
 207		$summary = new stdClass();
 208		// $summary->prepared = false;
 209		// $summary->changed = false;
 210		foreach ($result as $row) {
 211			$status = $row->status;
 212			$summary->$status = $row->total;
 213		}
 214
 215		return $summary;
 216	}
 217
 218
 219	
 220	function listingUsesFixedPriceItem( $listing_item )
 221	{
 222		// regard auction_type by default
 223		$useFixedPriceItem = ( 'FixedPriceItem' == $listing_item['auction_type'] ) ? true : false;
 224
 225		// but switch to AddItem if BestOffer is enabled
 226		$profile_details = $listing_item['profile_data']['details'];
 227        if ( @$profile_details['bestoffer_enabled'] == '1' ) $useFixedPriceItem = false;
 228
 229		return $useFixedPriceItem;
 230	} 
 231	
 232	function addItem( $id, $session )
 233	{
 234		// skip this item if item status not allowed
 235		$allowed_statuses = array( 'prepared', 'verified' );
 236		if ( ! $this->itemHasAllowedStatus( $id, $allowed_statuses ) ) return false;
 237
 238		// build item
 239		$ibm = new ItemBuilderModel();
 240		$item = $ibm->buildItem( $id, $session );
 241		if ( ! $ibm->checkItem($item) ) return $ibm->result;
 242
 243		// preparation - set up new ServiceProxy with given session
 244		$this->initServiceProxy($session);
 245
 246		// switch to FixedPriceItem if product has variations
 247		$listing_item = $this->getItem( $id );
 248		// $useFixedPriceItem = ( ProductWrapper::hasVariations( $listing_item['post_id'] ) ) ? true : false;
 249		// $useFixedPriceItem = ( 'FixedPriceItem' == $listing_item['auction_type'] ) ? true : false;
 250
 251		$this->logger->info( "Adding #$id: ".$item->Title );
 252		if ( $this->listingUsesFixedPriceItem( $listing_item ) ) {
 253
 254			$req = new AddFixedPriceItemRequestType(); 
 255			$req->setItem($item);
 256			
 257			$this->logger->debug( "Request: ".print_r($req,1) );
 258			$res = $this->_cs->AddFixedPriceItem($req); 
 259
 260		} else {
 261
 262			$req = new AddItemRequestType(); 
 263			$req->setItem($item);
 264			
 265			$this->logger->debug( "Request: ".print_r($req,1) );
 266			$res = $this->_cs->AddItem($req); 
 267
 268		}
 269
 270		// handle response and check if successful
 271		if ( $this->handleResponse($res) ) {
 272
 273			// save ebay ID and fees to db
 274			$listingFee = $this->getListingFeeFromResponse( $res );
 275			$data['ebay_id'] = $res->ItemID;
 276			$data['fees'] = $listingFee;
 277			$data['status'] = 'published';
 278			$this->updateListing( $id, $data );
 279			
 280			// get details like ViewItemURL from ebay automatically
 281			$this->updateItemDetails( $id, $session );
 282
 283			$this->logger->info( "Item #$id sent to ebay, ItemID is ".$res->ItemID );
 284
 285		} // call successful
 286
 287		return $this->result;
 288
 289	} // addItem()
 290
 291	function relistItem( $id, $session )
 292	{
 293		// skip this item if item status not allowed
 294		$allowed_statuses = array( 'ended', 'sold' );
 295		if ( ! $this->itemHasAllowedStatus( $id, $allowed_statuses ) ) return false;
 296
 297		// build item
 298		$ibm = new ItemBuilderModel();
 299		$item = $ibm->buildItem( $id, $session );
 300		if ( ! $ibm->checkItem($item) ) return $ibm->result;
 301
 302		// preparation - set up new ServiceProxy with given session
 303		$this->initServiceProxy($session);
 304
 305		// switch to FixedPriceItem if product has variations
 306		$listing_item = $this->getItem( $id );
 307		// $useFixedPriceItem = ( ProductWrapper::hasVariations( $listing_item['post_id'] ) ) ? true : false;
 308		// $useFixedPriceItem = ( 'FixedPriceItem' == $listing_item['auction_type'] ) ? true : false;
 309
 310		// add old ItemID for relisting
 311		$item->setItemID( $listing_item['ebay_id'] );
 312
 313		$this->logger->info( "Relisting #$id (ItemID ".$listing_item['ebay_id'].") - ".$item->Title );
 314		if ( $this->listingUsesFixedPriceItem( $listing_item ) ) {
 315
 316			$req = new RelistFixedPriceItemRequestType(); 
 317			$req->setItem($item);
 318			
 319			$this->logger->debug( "Request: ".print_r($req,1) );
 320			$res = $this->_cs->RelistFixedPriceItem($req); 
 321
 322		} else {
 323
 324			$req = new RelistItemRequestType(); 
 325			$req->setItem($item);
 326			
 327			$this->logger->debug( "Request: ".print_r($req,1) );
 328			$res = $this->_cs->RelistItem($req); 
 329
 330		}
 331
 332		// handle response and check if successful
 333		if ( $this->handleResponse($res) ) {
 334
 335			// save ebay ID and fees to db
 336			$listingFee = $this->getListingFeeFromResponse( $res );
 337			$data['ebay_id'] = $res->ItemID;
 338			$data['fees'] = $listingFee;
 339			$data['status'] = 'published';
 340			$this->updateListing( $id, $data );
 341			
 342			// get details like ViewItemURL from ebay automatically
 343			$this->updateItemDetails( $id, $session );
 344
 345			$this->logger->info( "Item #$id relisted on ebay, NEW ItemID is ".$res->ItemID );
 346
 347		} // call successful
 348
 349		return $this->result;
 350
 351	} // relistItem()
 352
 353	function reviseItem( $id, $session )
 354	{
 355		// skip this item if item status not allowed
 356		$allowed_statuses = array( 'published', 'changed' );
 357		if ( ! $this->itemHasAllowedStatus( $id, $allowed_statuses ) ) return false;
 358
 359		// check if product has variations
 360		$listing_item = $this->getItem( $id );
 361		// $useFixedPriceItem = ( ProductWrapper::hasVariations( $listing_item['post_id'] ) ) ? true : false;
 362		// $useFixedPriceItem = ( 'FixedPriceItem' == $listing_item['auction_type'] ) ? true : false;
 363
 364		// build item
 365		$ibm = new ItemBuilderModel();
 366		$item = $ibm->buildItem( $id, $session );
 367		if ( ! $ibm->checkItem($item) ) return $ibm->result;
 368
 369		// if quantity is zero, end item instead
 370		if ( ( $item->Quantity == 0 ) && ( ! $this->VariationsHaveStock ) ) {
 371			$this->logger->info( "Item #$id has no stock, switching to endItem()" );
 372			return $this->endItem( $id, $session );
 373		}
 374		
 375		// preparation - set up new ServiceProxy with given session
 376		$this->initServiceProxy($session);
 377
 378		// set ItemID to revise
 379		$item->setItemID( $this->getEbayIDFromID($id) );
 380		$this->logger->info( "Revising #$id: ".$p['auction_title'] );
 381
 382		// switch to FixedPriceItem if product has variations
 383		if ( $this->listingUsesFixedPriceItem( $listing_item ) ) {
 384
 385			$req = new ReviseFixedPriceItemRequestType(); 
 386			$req->setItem($item);
 387			
 388			$this->logger->debug( "Request: ".print_r($req,1) );
 389			$res = $this->_cs->ReviseFixedPriceItem($req); 
 390
 391		} else {
 392
 393			$req = new ReviseItemRequestType(); 
 394			$req->setItem($item);
 395			
 396			$this->logger->debug( "Request: ".print_r($req,1) );
 397			$res = $this->_cs->ReviseItem($req); 
 398
 399		}
 400
 401		// handle response and check if successful
 402		if ( $this->handleResponse($res) ) {
 403
 404			// save ebay ID and fees to db
 405			#$listingFee = $this->getListingFeeFromResponse( $res );
 406			#$data['ebay_id'] = $res->ItemID;
 407			#$data['fees'] = $listingFee;
 408			$data['status'] = 'published';
 409			$this->updateListing( $id, $data );
 410			
 411			// get details like ViewItemURL from ebay automatically
 412			#$this->updateItemDetails( $id, $session );
 413
 414			$this->logger->info( "Item #$id was revised, ItemID is ".$res->ItemID );
 415
 416		} // call successful
 417
 418		return $this->result;
 419
 420	} // reviseItem()
 421
 422
 423	function verifyAddItem( $id, $session )
 424	{
 425		// skip this item if item status not allowed
 426		$allowed_statuses = array( 'prepared', 'verified' );
 427		if ( ! $this->itemHasAllowedStatus( $id, $allowed_statuses ) ) return false;
 428
 429		// build item
 430		$ibm = new ItemBuilderModel();
 431		$item = $ibm->buildItem( $id, $session );
 432		if ( ! $ibm->checkItem($item) ) return $ibm->result;
 433
 434		// preparation - set up new ServiceProxy with given session
 435		$this->initServiceProxy($session);
 436
 437		// switch to FixedPriceItem if product has variations
 438		$listing_item = $this->getItem( $id );
 439		// $useFixedPriceItem = ( ProductWrapper::hasVariations( $listing_item['post_id'] ) ) ? true : false;
 440		// $useFixedPriceItem = ( 'FixedPriceItem' == $listing_item['auction_type'] ) ? true : false;
 441
 442		$this->logger->info( "Verifying #$id: ".$item->Title );
 443		if ( $this->listingUsesFixedPriceItem( $listing_item ) ) {
 444
 445			$req = new VerifyAddFixedPriceItemRequestType(); 
 446			$req->setItem($item);
 447			
 448			$this->logger->debug( "Request: ".print_r($req,1) );
 449			$res = $this->_cs->VerifyAddFixedPriceItem($req); 
 450
 451		} else {
 452
 453			$req = new VerifyAddItemRequestType(); 
 454			$req->setItem($item);
 455			
 456			$this->logger->debug( "Request: ".print_r($req,1) );
 457			$res = $this->_cs->VerifyAddItem($req); 
 458
 459		}
 460
 461		// handle response and check if successful
 462		if ( $this->handleResponse($res) ) {
 463
 464			// save listing fees to db
 465			$listingFee = $this->getListingFeeFromResponse( $res );
 466			// $data['ebay_id'] = $res->ItemID;
 467			$data['fees'] = $listingFee;
 468			$data['status'] = 'verified';
 469			$this->updateListing( $id, $data );
 470
 471			$this->logger->info( "Item #$id verified with ebay, getAck(): ".$res->getAck() );
 472
 473		} // call successful
 474		
 475		return $this->result;
 476
 477	} // verifyAddItem()
 478
 479
 480	function endItem( $id, $session )
 481	{
 482		// skip this item if item status not allowed
 483		$allowed_statuses = array( 'published' );
 484		if ( ! $this->itemHasAllowedStatus( $id, $allowed_statuses ) ) return false;
 485
 486		// preparation - set up new ServiceProxy with given session
 487		$this->initServiceProxy($session);
 488
 489		// get eBay ID
 490		$item = $this->getItem( $id );
 491		$item_id = $item['ebay_id'];
 492
 493		$req = new EndItemRequestType(); # ***
 494        $req->setItemID( $item_id );
 495        $req->setEndingReason('LostOrBroken');
 496
 497		$this->logger->info( "calling EndItem($id) #$item_id " );
 498		$this->logger->debug( "Request: ".print_r($req,1) );
 499		$res = $this->_cs->EndItem($req); # ***
 500		$this->logger->info( "EndItem() Complete #$item_id" );
 501		$this->logger->debug( "Response: ".print_r($res,1) );
 502
 503		// handle response and check if successful
 504		if ( $this->handleResponse($res) ) {
 505
 506			// save ebay ID and fees to db
 507			$data['end_date'] = $res->EndTime;
 508			$data['status'] = 'ended';
 509			$this->updateListing( $id, $data );
 510			
 511			$this->logger->info( "Item #$id was ended manually. " );
 512
 513		} // call successful
 514
 515		return $this->result;
 516
 517	} // endItem()
 518
 519
 520	function itemHasAllowedStatus( $id, $allowed_statuses )
 521	{
 522		$item = $this->getItem( $id );
 523		if ( in_array( $item['status'], $allowed_statuses ) ) {
 524			return true;
 525		} else {
 526			$this->logger->info("skipped item $id with status ".$item['status']);
 527			$this->logger->debug("allowed_statuses: ".print_r($allowed_statuses,1) );
 528			$this->showMessage( sprintf( 'Skipped %s item: %s', $item['status'], $item['auction_title'] ), false, true );
 529			return false;
 530		}
 531
 532	} // itemHasAllowedStatus()
 533
 534
 535	function getListingFeeFromResponse( $res )
 536	{
 537		
 538		$fees = new FeesType();
 539		$fees = $res->GetFees();
 540		foreach ($fees->getFee() as $fee) {
 541			if ( $fee->GetName() == 'ListingFee' ) {
 542				$listingFee = $fee->GetFee()->getTypeValue();
 543			}
 544			$this->logger->debug( 'FeeName: '.$fee->GetName(). ' is '. $fee->GetFee()->getTypeValue().' '.$fee->GetFee()->getTypeAttribute('currencyID') );
 545		}
 546		return $listingFee;
 547
 548	} // getListingFeeFromResponse()
 549
 550
 551	public function updateItemDetails( $id, $session ) {
 552		global $wpdb;
 553
 554		// get item data
 555		$item = $this->getItem( $id );
 556
 557		// preparation
 558		$this->initServiceProxy($session);
 559
 560		$this->_cs->setHandler('ItemType', array(& $this, 'updateItemDetail'));
 561
 562		// download the shipping data
 563		$req = new GetItemRequestType();
 564        $req->setItemID( $item['ebay_id'] );
 565		#$req->setDetailName( 'PaymentOptionDetails' );
 566		#$req->setActiveList( true );
 567
 568		$res = $this->_cs->GetItem($req);		
 569
 570		// handle response and check if successful
 571		if ( $this->handleResponse($res) ) {
 572			$this->logger->info( "Item #$id was updated from eBay, ItemID is ".$res->ItemID );
 573		} // call successful
 574
 575		return $this->result;
 576
 577	}
 578
 579
 580	function updateItemDetail($type, & $Detail)
 581	{
 582		global $wpdb;
 583		
 584		//#type $Detail ItemType
 585		
 586		// map ItemType to DB columns
 587		$data = $this->mapItemDetailToDB( $Detail );
 588
 589		$this->logger->debug('Detail: '.print_r($Detail,1) );
 590		$this->logger->debug('data: '.print_r($data,1) );
 591
 592		$wpdb->update( $this->tablename, $data, array( 'ebay_id' => $Detail->ItemID ) );
 593
 594		#$this->logger->info('sql: '.$wpdb->last_query );
 595		#$this->logger->info( mysql_error() );
 596
 597		return true;
 598	}
 599
 600	function mapItemDetailToDB( $Detail )
 601	{
 602		//#type $Detail ItemType
 603		$data['ebay_id'] 			= $Detail->ItemID;
 604		$data['auction_title'] 		= $Detail->Title;
 605		$data['auction_type'] 		= $Detail->ListingType;
 606		$data['listing_duration'] 	= $Detail->ListingDuration;
 607		$data['date_published'] 	= $Detail->ListingDetails->StartTime;
 608		$data['end_date'] 			= $Detail->ListingDetails->EndTime;
 609		$data['price'] 				= $Detail->SellingStatus->CurrentPrice->value;
 610		$data['quantity_sold'] 		= $Detail->SellingStatus->QuantitySold;
 611		$data['quantity'] 			= $Detail->Quantity;
 612		$data['ViewItemURL'] 		= $Detail->ListingDetails->ViewItemURL;
 613		$data['GalleryURL'] 		= $Detail->PictureDetails->GalleryURL;
 614
 615		// if this item has variations, we don't update quantity
 616		if ( count( @$Detail->Variations->Variation ) > 0 ) {
 617			unset( $data['quantity'] );
 618			$this->logger->info('skip quantity for variation #'.$Detail->ItemID );
 619		}
 620
 621
 622		// set status to ended if end_date is in the past
 623		if ( time() > mysql2date('U', $data['end_date']) ) {
 624			$data['status'] 		= 'ended';
 625		} else {
 626			$data['status'] 		= 'published';			
 627		}
 628
 629		$data['details'] = $this->encodeObject( $Detail );
 630
 631		return $data;
 632	}
 633
 634
 635
 636	public function updateListing( $id, $data ) {
 637		global $wpdb;
 638
 639		// update
 640		$wpdb->update( $this->tablename, $data, array( 'id' => $id ) );
 641
 642		#$this->logger->info('sql: '.$wpdb->last_query );
 643		#$this->logger->info( mysql_error() );
 644	}
 645
 646
 647	public function updateEndedListings( $sm = false ) {
 648		global $wpdb;
 649
 650		$items = $this->getAllPastEndDate();
 651
 652		foreach ($items as $item) {
 653			$wpdb->update( $this->tablename, array( 'status' => 'ended' ), array( 'id' => $item['id'] ) );
 654		}
 655
 656		#$this->logger->info('sql: '.$wpdb->last_query );
 657		#$this->logger->info( mysql_error() );
 658	}
 659
 660
 661
 662
 663	function getAllSelected() {
 664		global $wpdb;	
 665		$items = $wpdb->get_results("
 666			SELECT * 
 667			FROM $this->tablename
 668			WHERE status = 'selected'
 669			ORDER BY id DESC
 670		", ARRAY_A);		
 671
 672		return $items;		
 673	}
 674	function getAllPrepared() {
 675		global $wpdb;	
 676		$items = $wpdb->get_results("
 677			SELECT * 
 678			FROM $this->tablename
 679			WHERE status = 'prepared'
 680			ORDER BY id DESC
 681		", ARRAY_A);		
 682
 683		return $items;		
 684	}
 685	function getAllVerified() {
 686		global $wpdb;	
 687		$items = $wpdb->get_results("
 688			SELECT * 
 689			FROM $this->tablename
 690			WHERE status = 'verified'
 691			ORDER BY id DESC
 692		", ARRAY_A);		
 693
 694		return $items;		
 695	}
 696	function getAllChanged() {
 697		global $wpdb;	
 698		$items = $wpdb->get_results("
 699			SELECT * 
 700			FROM $this->tablename
 701			WHERE status = 'changed'
 702			ORDER BY id DESC
 703		", ARRAY_A);		
 704
 705		return $items;		
 706	}
 707	function getAllPublished() {
 708		global $wpdb;	
 709		$items = $wpdb->get_results("
 710			SELECT * 
 711			FROM $this->tablename
 712			WHERE status = 'published'
 713			   OR status = 'changed'
 714			ORDER BY id DESC
 715		", ARRAY_A);		
 716
 717		return $items;		
 718	}
 719	function getAllPreparedWithProfile( $profile_id ) {
 720		global $wpdb;	
 721		$items = $wpdb->get_results("
 722			SELECT * 
 723			FROM $this->tablename
 724			WHERE status = 'prepared'
 725			  AND profile_id = '$profile_id'
 726			ORDER BY id DESC
 727		", ARRAY_A);		
 728
 729		return $items;		
 730	}
 731	function getAllVerifiedWithProfile( $profile_id ) {
 732		global $wpdb;	
 733		$items = $wpdb->get_results("
 734			SELECT * 
 735			FROM $this->tablename
 736			WHERE status = 'verified'
 737			  AND profile_id = '$profile_id'
 738			ORDER BY id DESC
 739		", ARRAY_A);		
 740
 741		return $items;		
 742	}
 743	function getAllPublishedWithProfile( $profile_id ) {
 744		global $wpdb;	
 745		$items = $wpdb->get_results("
 746			SELECT * 
 747			FROM $this->tablename
 748			WHERE ( status = 'published' OR status = 'changed' )
 749			  AND profile_id = '$profile_id'
 750			ORDER BY id DESC
 751		", ARRAY_A);		
 752
 753		return $items;		
 754	}
 755	function getAllPreparedWithTemplate( $template ) {
 756		global $wpdb;	
 757		$items = $wpdb->get_results("
 758			SELECT * 
 759			FROM $this->tablename
 760			WHERE status = 'prepared'
 761			  AND template LIKE '%$template'
 762			ORDER BY id DESC
 763		", ARRAY_A);		
 764
 765		return $items;		
 766	}
 767	function getAllVerifiedWithTemplate( $template ) {
 768		global $wpdb;	
 769		$items = $wpdb->get_results("
 770			SELECT * 
 771			FROM $this->tablename
 772			WHERE status = 'verified'
 773			  AND template LIKE '%$template'
 774			ORDER BY id DESC
 775		", ARRAY_A);		
 776
 777		return $items;		
 778	}
 779	function getAllPublishedWithTemplate( $template ) {
 780		global $wpdb;	
 781		$items = $wpdb->get_results("
 782			SELECT * 
 783			FROM $this->tablename
 784			WHERE ( status = 'published' OR status = 'changed' )
 785			  AND template LIKE '%$template'
 786			ORDER BY id DESC
 787		", ARRAY_A);		
 788
 789		return $items;		
 790	}
 791	function getAllPastEndDate() {
 792		global $wpdb;	
 793		$items = $wpdb->get_results("
 794			SELECT id 
 795			FROM $this->tablename
 796			WHERE NOT status = 'ended'
 797			  AND NOT listing_duration = 'GTC'
 798			  AND end_date < NOW()
 799			ORDER BY id DESC
 800		", ARRAY_A);		
 801
 802		return $items;		
 803	}
 804
 805	function getAllDuplicateProducts() {
 806		global $wpdb;	
 807		$items = $wpdb->get_results("
 808			SELECT post_id, COUNT(*) c
 809			FROM $this->tablename
 810			GROUP BY post_id 
 811			HAVING c > 1
 812		", OBJECT_K);		
 813
 814		if ( ! empty($items) ) {
 815			foreach ($items as &$item) {
 816				
 817				$listings = $this->getAllListingsFromPostID( $item->post_id );
 818				$item->listings = $listings;
 819
 820			}
 821		}
 822
 823		return $items;		
 824	}
 825
 826	function getRawPostExcerpt( $post_id ) {
 827		global $wpdb;	
 828		$excerpt = $wpdb->get_var("
 829			SELECT post_excerpt 
 830			FROM {$wpdb->prefix}posts
 831			WHERE ID = $post_id
 832		");
 833
 834		return $excerpt;		
 835	}
 836
 837
 838
 839	public function selectedProducts() {
 840		global $wpdb;	
 841		$items = $wpdb->get_results("
 842			SELECT * 
 843			FROM $this->tablename
 844			WHERE status = 'selected'
 845			ORDER BY id DESC
 846		", ARRAY_A);		
 847
 848		return $items;		
 849	}
 850
 851	public function setListingQuantity( $post_id, $quantity ) {
 852		global $wpdb;	
 853		$wpdb->update( $this->tablename, array( 'quantity' => $quantity ), array( 'post_id' => $post_id ) );
 854		// echo $wpdb->last_query;
 855		// echo mysql_error();
 856	}
 857
 858	public function markItemAsModified( $post_id ) {
 859		global $wpdb;	
 860
 861		// set published items to changed
 862		$wpdb->update( $this->tablename, array( 'status' => 'changed' ), array( 'status' => 'published', 'post_id' => $post_id ) );
 863
 864		// set verified items to prepared
 865		$wpdb->update( $this->tablename, array( 'status' => 'prepared' ), array( 'status' => 'verified', 'post_id' => $post_id ) );
 866	}
 867
 868
 869	public function reSelectListings( $ids ) {
 870		global $wpdb;
 871		foreach( $ids as $id ) {
 872			$wpdb->update( $this->tablename, array( 'status' => 'selected' ), array( 'id' => $id ) );
 873		}
 874	}
 875
 876
 877	public function prepareListings( $ids ) {
 878		foreach( $ids as $id ) {
 879			$this->prepareProductForListing( $id );
 880		}
 881	}
 882
 883	public function prepareProductForListing( $post_id, $post_content = false, $post_title = false ) {
 884		global $wpdb;
 885		
 886		// get wp post record
 887		$post = get_post( $post_id );
 888		
 889		// gather product data
 890		$data['post_id'] = $post_id;
 891		$data['auction_title'] = $post_title ? $post_title : $post->post_title;
 892		$data['post_content'] = $post_content ? $post_content : $post->post_content;
 893		$data['price'] = ProductWrapper::getPrice( $post_id );
 894		$data['status'] = 'selected';
 895		
 896		$this->logger->info('insert new auction '.$post_id.' - title: '.$data['auction_title']);
 897		$this->logger->debug( print_r($post,1) );
 898		
 899		// insert in auctions table
 900		$wpdb->insert( $this->tablename, $data );
 901
 902		$this->logger->debug('sql: '.$wpdb->last_query );
 903		$this->logger->debug( mysql_error() );
 904		
 905		return $wpdb->insert_id;
 906		
 907	}
 908
 909	function applyProfilePrice( $product_price, $profile_price ) {
 910	
 911		$this->logger->debug('applyProfilePrice(): '.$product_price.' - '.$profile_price );
 912
 913		// remove all spaces from profile setting
 914		$profile_price = str_replace( ' ','', trim($profile_price) );
 915		
 916		// return product price if profile is empty
 917		if ( $profile_price == '' ) return $product_price;
 918	
 919		// handle percent
 920		if ( preg_match('/\%/',$profile_price) ) {
 921			$this->logger->debug('percent mode');
 922		
 923			// parse percent syntax
 924			if ( preg_match('/([\+\-]?)([0-9\.]+)(\%)/',$profile_price, $matches) ) {
 925				$this->logger->debug('matches:' . print_r($matches,1) );
 926
 927				$modifier = $matches[1];
 928				$value = $matches[2];
 929				
 930				if ($modifier == '+') {
 931					return $product_price + ( $product_price * $value/100 );							
 932				} elseif ($modifier == '-') {
 933					return $product_price - ( $product_price * $value/100 );				
 934				} else {
 935					return ( $product_price * $value/100 );
 936				}
 937			
 938			} else {
 939				// no valid syntax
 940				return $product_price;		
 941			}
 942						
 943		} else {
 944
 945			$this->logger->debug('value mode');
 946		
 947			// parse value syntax
 948			if ( preg_match('/([\+\-]?)([0-9\.]+)/',$profile_price, $matches) ) {
 949				$this->logger->debug('matches:' . print_r($matches,1) );
 950
 951				$modifier = $matches[1];
 952				$value = $matches[2];
 953				
 954				if ($modifier == '+') {
 955					return $product_price + $value;				
 956				} elseif ($modifier == '-') {
 957					return $product_price - $value;				
 958				} else {
 959					return $value;
 960				}
 961			
 962			} else {
 963				// no valid syntax
 964				return $product_price;		
 965			}
 966		
 967		}
 968
 969	}
 970
 971	public function applyProfileToItem( $profile, $item, $update_title = true ) {
 972		global $wpdb;
 973
 974		// get item data
 975		$id 		= $item['id'];
 976		$post_id 	= $item['post_id'];
 977		$status 	= $this->getStatus( $id );
 978		$ebay_id 	= $this->getEbayIDFromID( $id );
 979		$post_title = get_the_title( $item['post_id'] );
 980
 981		// skip ended auctions
 982		if ( $status == 'ended' ) return;
 983
 984		// gather profile data
 985		$data = array();
 986		$data['profile_id'] 		= $profile['profile_id'];
 987		$data['auction_type'] 		= $profile['type'];
 988		$data['listing_duration'] 	= $profile['listing_duration'];
 989		$data['template'] 			= $profile['details']['template'];
 990		$data['quantity'] 			= $profile['details']['quantity'];
 991		$data['date_created'] 		= date( 'Y-m-d H:i:s' );
 992		$data['profile_data'] 		= $this->encodeObject( $profile );
 993		
 994		// add prefix and suffix to product title
 995		if ( $update_title ) {
 996
 997			// append space to prefix, prepend space to suffix
 998			// TODO: make this an option
 999			$title_prefix = trim( $profile['details']['title_prefix'] ) . ' ';
1000			$title_suffix = ' ' . trim( $profile['details']['title_suffix'] );
1001
1002			$data['auction_title'] = trim( $title_prefix . $post_title . $title_suffix );
1003
1004		}
1005
1006		// process attribute shortcodes in title - like [[attribute_Brand]]
1007		$templatesModel = new TemplatesModel();
1008		$data['auction_title'] = $templatesModel->processAttributeShortcodes( $item['post_id'], $data['auction_title'] );
1009		$this->logger->info('processAttributeShortcodes('.$item['post_id'].')');
1010		$this->logger->info('auction_title: '.$data['auction_title'].'');
1011
1012		// apply profile price
1013		$data['price'] = ProductWrapper::getPrice( $post_id );
1014		$data['price']  = $this->applyProfilePrice( $data['price'], $profile['details']['start_price'] );
1015		
1016		// fetch product stock if no quantity set in profile
1017		if ( intval( $data['quantity'] ) == 0 ) {
1018			$data['quantity'] = ProductWrapper::getStock( $post_id );
1019		}
1020		
1021		// default new status is 'prepared'
1022		$data['status'] = 'prepared';
1023		// except for already published items where it is 'changed'
1024		if ( intval($ebay_id) > 0 ) $data['status'] = 'changed';
1025
1026		// update auctions table
1027		$wpdb->update( $this->tablename, $data, array( 'id' => $id ) );
1028
1029
1030	}
1031
1032	public function applyProfileToItems( $profile, $items, $update_title = true ) {
1033
1034		// apply profile to all items
1035		foreach( $items as $item ) {
1036			$this->applyProfileToItem( $profile, $item, $update_title );			
1037		}
1038
1039		return $items;		
1040	}
1041
1042
1043	public function applyProfileToNewListings( $profile, $items = false, $update_title = true ) {
1044
1045		// get selected items - if no items provided
1046		if (!$items) $items = $this->getAllSelected();
1047
1048		$items = $this->applyProfileToItems( $profile, $items, $update_title );			
1049
1050		return $items;		
1051	}
1052
1053	public function reapplyProfileToItem( $id ) {
1054	
1055		// get item
1056		$item = $this->getItem( $id );
1057
1058		// get profile
1059		$profilesModel = new ProfilesModel();
1060        $profile = $profilesModel->getItem( $item['profile_id'] );
1061
1062        // re-apply profile
1063        $this->applyProfileToItem( $profile, $item );
1064
1065	}
1066
1067	public function reapplyProfileToItems( $ids ) {
1068		foreach( $ids as $id ) {
1069			$this->reapplyProfileToItem( $id );
1070		}
1071	}
1072
1073
1074}