1: <?php
  2: /**
  3:  * Magento
  4:  *
  5:  * NOTICE OF LICENSE
  6:  *
  7:  * This source file is subject to the Open Software License (OSL 3.0)
  8:  * that is bundled with this package in the file LICENSE.txt.
  9:  * It is also available through the world-wide-web at this URL:
 10:  * http://opensource.org/licenses/osl-3.0.php
 11:  * If you did not receive a copy of the license and are unable to
 12:  * obtain it through the world-wide-web, please send an email
 13:  * to license@magentocommerce.com so we can send you a copy immediately.
 14:  *
 15:  * DISCLAIMER
 16:  *
 17:  * Do not edit or add to this file if you wish to upgrade Magento to newer
 18:  * versions in the future. If you wish to customize Magento for your
 19:  * needs please refer to http://www.magentocommerce.com for more information.
 20:  *
 21:  * @category    Mage
 22:  * @package     Mage_Paypal
 23:  * @copyright   Copyright (c) 2012 Magento Inc. (http://www.magentocommerce.com)
 24:  * @license     http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 25:  */
 26: 
 27: /*
 28:  * Paypal Settlement Report model
 29:  *
 30:  * Perform fetching reports from remote servers with following saving them to database
 31:  * Prepare report rows for Mage_Paypal_Model_Report_Settlement_Row model
 32:  *
 33:  */
 34: /**
 35:  * Enter description here ...
 36:  *
 37:  * @method Mage_Paypal_Model_Resource_Report_Settlement _getResource()
 38:  * @method Mage_Paypal_Model_Resource_Report_Settlement getResource()
 39:  * @method string getReportDate()
 40:  * @method Mage_Paypal_Model_Report_Settlement setReportDate(string $value)
 41:  * @method string getAccountId()
 42:  * @method Mage_Paypal_Model_Report_Settlement setAccountId(string $value)
 43:  * @method string getFilename()
 44:  * @method Mage_Paypal_Model_Report_Settlement setFilename(string $value)
 45:  * @method string getLastModified()
 46:  * @method Mage_Paypal_Model_Report_Settlement setLastModified(string $value)
 47:  *
 48:  * @category    Mage
 49:  * @package     Mage_Paypal
 50:  * @author      Magento Core Team <core@magentocommerce.com>
 51:  */
 52: class Mage_Paypal_Model_Report_Settlement extends Mage_Core_Model_Abstract
 53: {
 54:     /**
 55:      * Default PayPal SFTP host
 56:      * @var string
 57:      */
 58:     const REPORTS_HOSTNAME = "reports.paypal.com";
 59: 
 60:     /**
 61:      * Default PayPal SFTP host for sandbox mode
 62:      * @var string
 63:      */
 64:     const SANDBOX_REPORTS_HOSTNAME = "reports.sandbox.paypal.com";
 65: 
 66:     /**
 67:      * PayPal SFTP path
 68:      * @var string
 69:      */
 70:     const REPORTS_PATH = "/ppreports/outgoing";
 71: 
 72:     /**
 73:      * Original charset of old report files
 74:      * @var string
 75:      */
 76:     const FILES_IN_CHARSET = "UTF-16";
 77: 
 78:     /**
 79:      * Target charset of report files to be parsed
 80:      * @var string
 81:      */
 82:     const FILES_OUT_CHARSET = "UTF-8";
 83: 
 84:     /**
 85:      * Reports rows storage
 86:      * @var array
 87:      */
 88:     protected $_rows = array();
 89: 
 90:     protected $_csvColumns = array(
 91:         'old' => array(
 92:             'section_columns' => array(
 93:                 '' => 0,
 94:                 'TransactionID' => 1,
 95:                 'InvoiceID' => 2,
 96:                 'PayPalReferenceID' => 3,
 97:                 'PayPalReferenceIDType' => 4,
 98:                 'TransactionEventCode' => 5,
 99:                 'TransactionInitiationDate' => 6,
100:                 'TransactionCompletionDate' => 7,
101:                 'TransactionDebitOrCredit' => 8,
102:                 'GrossTransactionAmount' => 9,
103:                 'GrossTransactionCurrency' => 10,
104:                 'FeeDebitOrCredit' => 11,
105:                 'FeeAmount' => 12,
106:                 'FeeCurrency' => 13,
107:                 'CustomField' => 14,
108:                 'ConsumerID' => 15
109:             ),
110:             'rowmap' => array(
111:                 'TransactionID' => 'transaction_id',
112:                 'InvoiceID' => 'invoice_id',
113:                 'PayPalReferenceID' => 'paypal_reference_id',
114:                 'PayPalReferenceIDType' => 'paypal_reference_id_type',
115:                 'TransactionEventCode' => 'transaction_event_code',
116:                 'TransactionInitiationDate' => 'transaction_initiation_date',
117:                 'TransactionCompletionDate' => 'transaction_completion_date',
118:                 'TransactionDebitOrCredit' => 'transaction_debit_or_credit',
119:                 'GrossTransactionAmount' => 'gross_transaction_amount',
120:                 'GrossTransactionCurrency' => 'gross_transaction_currency',
121:                 'FeeDebitOrCredit' => 'fee_debit_or_credit',
122:                 'FeeAmount' => 'fee_amount',
123:                 'FeeCurrency' => 'fee_currency',
124:                 'CustomField' => 'custom_field',
125:                 'ConsumerID' => 'consumer_id'
126:             )
127:         ),
128:         'new' => array(
129:             'section_columns' => array(
130:                 '' => 0,
131:                 'Transaction ID' => 1,
132:                 'Invoice ID' => 2,
133:                 'PayPal Reference ID' => 3,
134:                 'PayPal Reference ID Type' => 4,
135:                 'Transaction Event Code' => 5,
136:                 'Transaction Initiation Date' => 6,
137:                 'Transaction Completion Date' => 7,
138:                 'Transaction  Debit or Credit' => 8,
139:                 'Gross Transaction Amount' => 9,
140:                 'Gross Transaction Currency' => 10,
141:                 'Fee Debit or Credit' => 11,
142:                 'Fee Amount' => 12,
143:                 'Fee Currency' => 13,
144:                 'Custom Field' => 14,
145:                 'Consumer ID' => 15,
146:                 'Payment Tracking ID' => 16
147:             ),
148:             'rowmap' => array(
149:                 'Transaction ID' => 'transaction_id',
150:                 'Invoice ID' => 'invoice_id',
151:                 'PayPal Reference ID' => 'paypal_reference_id',
152:                 'PayPal Reference ID Type' => 'paypal_reference_id_type',
153:                 'Transaction Event Code' => 'transaction_event_code',
154:                 'Transaction Initiation Date' => 'transaction_initiation_date',
155:                 'Transaction Completion Date' => 'transaction_completion_date',
156:                 'Transaction  Debit or Credit' => 'transaction_debit_or_credit',
157:                 'Gross Transaction Amount' => 'gross_transaction_amount',
158:                 'Gross Transaction Currency' => 'gross_transaction_currency',
159:                 'Fee Debit or Credit' => 'fee_debit_or_credit',
160:                 'Fee Amount' => 'fee_amount',
161:                 'Fee Currency' => 'fee_currency',
162:                 'Custom Field' => 'custom_field',
163:                 'Consumer ID' => 'consumer_id',
164:                 'Payment Tracking ID' => 'payment_tracking_id'
165:             )
166:         )
167:     );
168:     /**
169:      * Initialize resource model
170:      */
171:     protected function _construct()
172:     {
173:         $this->_init('paypal/report_settlement');
174:     }
175: 
176:     /**
177:      * Stop saving process if file with same report date, account ID and last modified date was already ferched
178:      *
179:      * @return Mage_Core_Model_Abstract
180:      */
181:     protected function _beforeSave()
182:     {
183:         $this->_dataSaveAllowed = true;
184:         if ($this->getId()) {
185:             if ($this->getLastModified() == $this->getReportLastModified()) {
186:                 $this->_dataSaveAllowed = false;
187:             }
188:         }
189:         $this->setLastModified($this->getReportLastModified());
190:         return parent::_beforeSave();
191:     }
192: 
193:     /**
194:      * Goes to specified host/path and fetches reports from there.
195:      * Save reports to database.
196:      *
197:      * @param array $config SFTP credentials
198:      * @return int Number of report rows that were fetched and saved successfully
199:      */
200:     public function fetchAndSave($config)
201:     {
202:         $connection = new Varien_Io_Sftp();
203:         $connection->open(array(
204:             'host'     => $config['hostname'],
205:             'username' => $config['username'],
206:             'password' => $config['password']
207:         ));
208:         $connection->cd($config['path']);
209:         $fetched = 0;
210:         $listing = $this->_filterReportsList($connection->rawls());
211:         foreach ($listing as $filename => $attributes) {
212:             $localCsv = tempnam(Mage::getConfig()->getOptions()->getTmpDir(), 'PayPal_STL');
213:             if ($connection->read($filename, $localCsv)) {
214:                 if (!is_writable($localCsv)) {
215:                     Mage::throwException(Mage::helper('paypal')->__('Cannot create target file for reading reports.'));
216:                 }
217: 
218:                 $encoded = file_get_contents($localCsv);
219:                 $csvFormat = 'new';
220:                 if (self::FILES_OUT_CHARSET != mb_detect_encoding(($encoded))) {
221:                     $decoded = @iconv(self::FILES_IN_CHARSET, self::FILES_OUT_CHARSET.'//IGNORE', $encoded);
222:                     file_put_contents($localCsv, $decoded);
223:                     $csvFormat = 'old';
224:                 }
225: 
226:                 // Set last modified date, this value will be overwritten during parsing
227:                 if (isset($attributes['mtime'])) {
228:                     $lastModified = new Zend_Date($attributes['mtime']);
229:                     $this->setReportLastModified($lastModified->toString(Varien_Date::DATETIME_INTERNAL_FORMAT));
230:                 }
231: 
232:                 $this->setReportDate($this->_fileNameToDate($filename))
233:                     ->setFilename($filename)
234:                     ->parseCsv($localCsv, $csvFormat);
235: 
236:                 if ($this->getAccountId()) {
237:                     $this->save();
238:                 }
239: 
240:                 if ($this->_dataSaveAllowed) {
241:                     $fetched += count($this->_rows);
242:                 }
243:                 // clean object and remove parsed file
244:                 $this->unsetData();
245:                 unlink($localCsv);
246:             }
247:         }
248:         return $fetched;
249:     }
250: 
251:     /**
252:      * Parse CSV file and collect report rows
253:      *
254:      * @param string $localCsv Path to CSV file
255:      * @param string $format CSV format(column names)
256:      * @return Mage_Paypal_Model_Report_Settlement
257:      */
258:     public function parseCsv($localCsv, $format = 'new')
259:     {
260:         $this->_rows = array();
261: 
262:         $sectionColumns = $this->_csvColumns[$format]['section_columns'];
263:         $rowMap = $this->_csvColumns[$format]['rowmap'];
264: 
265:         $flippedSectionColumns = array_flip($sectionColumns);
266:         $fp = fopen($localCsv, 'r');
267:         while($line = fgetcsv($fp)) {
268:             if (empty($line)) { // The line was empty, so skip it.
269:                 continue;
270:             }
271:             $lineType = $line[0];
272:             switch($lineType) {
273:                 case 'RH': // Report header.
274:                     $lastModified = new Zend_Date($line[1]);
275:                     $this->setReportLastModified($lastModified->toString(Varien_Date::DATETIME_INTERNAL_FORMAT));
276:                     //$this->setAccountId($columns[2]); -- probably we'll just take that from the section header...
277:                     break;
278:                 case 'FH': // File header.
279:                     // Nothing interesting here, move along
280:                     break;
281:                 case 'SH': // Section header.
282:                     $this->setAccountId($line[3]);
283:                     $this->loadByAccountAndDate();
284:                     break;
285:                 case 'CH': // Section columns.
286:                     // In case ever the column order is changed, we will have the items recorded properly
287:                     // anyway. We have named, not numbered columns.
288:                     for ($i = 1; $i < count($line); $i++) {
289:                         $sectionColumns[$line[$i]] = $i;
290:                     }
291:                     $flippedSectionColumns = array_flip($sectionColumns);
292:                     break;
293:                 case 'SB': // Section body.
294:                     $bodyItem = array();
295:                     for($i = 1; $i < count($line); $i++) {
296:                         $bodyItem[$rowMap[$flippedSectionColumns[$i]]] = $line[$i];
297:                     }
298:                     $this->_rows[] = $bodyItem;
299:                     break;
300:                 case 'SC': // Section records count.
301:                 case 'RC': // Report records count.
302:                 case 'SF': // Section footer.
303:                 case 'FF': // File footer.
304:                 case 'RF': // Report footer.
305:                     // Nothing to see here, move along
306:                     break;
307:             }
308:         }
309:         return $this;
310:     }
311: 
312:     /**
313:      * Load report by unique key (accoutn + report date)
314:      *
315:      * @return Mage_Paypal_Model_Report_Settlement
316:      */
317:     public function loadByAccountAndDate()
318:     {
319:         $this->getResource()->loadByAccountAndDate($this, $this->getAccountId(), $this->getReportDate());
320:         return $this;
321:     }
322: 
323:     /**
324:      * Return collected rows for further processing.
325:      *
326:      * @return array
327:      */
328:     public function getRows()
329:     {
330:         return $this->_rows;
331:     }
332: 
333:     /**
334:      * Return name for row column
335:      *
336:      * @param string $field Field name in row model
337:      * @return string
338:      */
339:     public function getFieldLabel($field)
340:     {
341:         switch ($field) {
342:             case 'report_date':
343:                 return Mage::helper('paypal')->__('Report Date');
344:             case 'account_id':
345:                 return Mage::helper('paypal')->__('Merchant Account');
346:             case 'transaction_id':
347:                 return Mage::helper('paypal')->__('Transaction ID');
348:             case 'invoice_id':
349:                 return Mage::helper('paypal')->__('Invoice ID');
350:             case 'paypal_reference_id':
351:                 return Mage::helper('paypal')->__('PayPal Reference ID');
352:             case 'paypal_reference_id_type':
353:                 return Mage::helper('paypal')->__('PayPal Reference ID Type');
354:             case 'transaction_event_code':
355:                 return Mage::helper('paypal')->__('Event Code');
356:             case 'transaction_event':
357:                 return Mage::helper('paypal')->__('Event');
358:             case 'transaction_initiation_date':
359:                 return Mage::helper('paypal')->__('Initiation Date');
360:             case 'transaction_completion_date':
361:                 return Mage::helper('paypal')->__('Completion Date');
362:             case 'transaction_debit_or_credit':
363:                 return Mage::helper('paypal')->__('Debit or Credit');
364:             case 'gross_transaction_amount':
365:                 return Mage::helper('paypal')->__('Gross Amount');
366:             case 'fee_debit_or_credit':
367:                 return Mage::helper('paypal')->__('Fee Debit or Credit');
368:             case 'fee_amount':
369:                 return Mage::helper('paypal')->__('Fee Amount');
370:             case 'custom_field':
371:                 return Mage::helper('paypal')->__('Custom');
372:             default:
373:                 return $field;
374:         }
375:     }
376: 
377:     /**
378:      * Iterate through website configurations and collect all SFTP configurations
379:      * Filter config values if necessary
380:      *
381:      * @param bool $automaticMode Whether to skip settings with disabled Automatic Fetching or not
382:      * @return array
383:      */
384:     public function getSftpCredentials($automaticMode = false)
385:     {
386:         $configs = array();
387:         $uniques = array();
388:         foreach(Mage::app()->getStores() as $store) {
389:             /*@var $store Mage_Core_Model_Store */
390:             $active = (bool)$store->getConfig('paypal/fetch_reports/active');
391:             if (!$active && $automaticMode) {
392:                 continue;
393:             }
394:             $cfg = array(
395:                 'hostname'  => $store->getConfig('paypal/fetch_reports/ftp_ip'),
396:                 'path'      => $store->getConfig('paypal/fetch_reports/ftp_path'),
397:                 'username'  => $store->getConfig('paypal/fetch_reports/ftp_login'),
398:                 'password'  => $store->getConfig('paypal/fetch_reports/ftp_password'),
399:                 'sandbox'   => $store->getConfig('paypal/fetch_reports/ftp_sandbox'),
400:             );
401:             if (empty($cfg['username']) || empty($cfg['password'])) {
402:                 continue;
403:             }
404:             if (empty($cfg['hostname']) || $cfg['sandbox']) {
405:                 $cfg['hostname'] = $cfg['sandbox'] ? self::SANDBOX_REPORTS_HOSTNAME : self::REPORTS_HOSTNAME;
406:             }
407:             if (empty($cfg['path']) || $cfg['sandbox']) {
408:                 $cfg['path'] = self::REPORTS_PATH;
409:             }
410:             // avoid duplicates
411:             if (in_array(serialize($cfg), $uniques)) {
412:                 continue;
413:             }
414:             $uniques[] = serialize($cfg);
415:             $configs[] = $cfg;
416:         }
417:         return $configs;
418:     }
419: 
420:     /**
421:      * Converts a filename to date of report.
422:      *
423:      * @param string $filename
424:      * @return string
425:      */
426:     protected function _fileNameToDate($filename)
427:     {
428:         // Currently filenames look like STL-YYYYMMDD, so that is what we care about.
429:         $dateSnippet = substr(basename($filename), 4, 8);
430:         $result = substr($dateSnippet, 0, 4).'-'.substr($dateSnippet, 4, 2).'-'.substr($dateSnippet, 6, 2);
431:         return $result;
432:     }
433: 
434:     /**
435:      * Filter SFTP file list by filename format
436:      *
437:      * @param array $list List of files as per $connection->rawls()
438:      * @return array Trimmed down list of files
439:      */
440:     protected function _filterReportsList($list)
441:     {
442:         $result = array();
443:         $pattern = '/^STL-(\d{8,8})\.(\d{2,2})\.(.{3,3})\.CSV$/';
444:         foreach ($list as $filename => $data) {
445:             if (preg_match($pattern, $filename)) {
446:                 $result[$filename] = $data;
447:             }
448:         }
449:         return $result;
450:     }
451: }
452: