lm_paypal.install

<?php

/**
 * @file
 *
 * Install or update the PayPal interface.
 *
 * These database fields are from:
 *  https://www.paypal.com/en_US/pdf/PP_OrderManagement_IntegrationGuide.pdf
 * See also:
 *  https://www.paypal.com/IntegrationCenter/ic_ipn-pdt-variable-reference.html
 *  https://www.paypal.com/uk/cgi-bin/webscr?cmd=p/acc/ipn-subscriptions-outside
 */

// TODO: Once I've been running lm_paypal for a while work out which
// IPN fields are unnecessary and remove them.

/**
 * @todo Please document this function.
 * @see http://drupal.org/node/1354
 */
function lm_paypal_uninstall() {
  // TODO The drupal_(un)install_schema functions are called automatically in D7.
  // drupal_uninstall_schema('lm_paypal')
  variable_del('lm_paypal_debug');
  variable_del('lm_paypal_host');
  variable_del('lm_paypal_business');
  variable_del('lm_paypal_obey_test_ipns');
  variable_del('lm_paypal_ipns_max_age');
  variable_del('lm_paypal_js_hide_email'); // old
}

/**
 * @todo Please document this function.
 * @see http://drupal.org/node/1354
 */
function lm_paypal_schema() {
  // This table definition corresponds to the HTTP POST sent to the site by
  // paypal.com.  This appears to change every so often, without any notification,
  // breaking live sites.
  // See https://www.paypal.com/IntegrationCenter/ic_ipn-pdt-variable-reference.html
  $schema['lm_paypal_ipns'] = array(
    'fields' => array(
      'id' => array(
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'description' => 'My ID for received IPNs',
      ),
      'timestamp' => array(
        'type' => 'int',
        'not null' => TRUE,
        'description' => 'unix time when ipn arrived',
      ),
      'txn_id' => array(
        'type' => 'varchar',
        'length' => 20,
        'not null' => TRUE,
        'default' => '',
        'description' => 'Paypal generated transaction id',
      ),
      'processed' => array(
        'type' => 'int',
        'size' => 'tiny',
        'default' => 0,
        'description' => 'set if processed',
      ),
      'test_ipn' => array(
        'type' => 'int',
        'size' => 'tiny',
        'description' => 'only set by sandbox to 1',
      ),
      'notify_version' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'the version number of Instant Payment Notification that makes the post.',
      ),
      'verify_sign' => array(
        'type' => 'varchar',
        'length' => 120,
        'description' => 'an encrypted string used to validate the authenticity of the transaction.',
      ),
      'ipn' => array(
        'type' => 'text',
        'size' => 'medium',
        'description' => 'The full HTTP POST data for the IPN from paypal.',
      ),
      'ipn_track_id' => array(
        'type' => 'text',
        'size' => 'medium',
        'description' => 'Tracking id', //@TODO better desc
      ),
      # Website Payments Standard, Website Payments Pro, and Refund Information
      # SOME FIELDS OMITTED
      'parent_txn_id' => array(
        'type' => 'varchar',
        'length' => 28,
        'description' => 'In the case of a refund, reversal, or canceled reversal, this variable contains the txn_id of the original transaction, while txn_id contains a new ID for the new transaction.',
      ),
      'payment_date' => array(
        'type' => 'varchar',
        'length' => 28,
        'description' => 'Time/Date stamp generated by PayPal , in the following format: HH:MM:SS DD Mmm YY, YYYY PST',
      ),
      'payment_status' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'Canceled_Reversal,Completed,Denied,Expired,Failed,In-Progress,Partially-Refunded,Pending,Processed,Refunded,Reversed,Voided',
      ),
      'payment_type' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'echeck,instant',
      ),
      'pending_reason' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'address,authorization,echeck,intl,multicurrency,unilateral,upgrade,verify,other',
      ),
      'reason_code' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'chargeback,guarantee,buyer-complaint,refund,other',
      ),
      # Buyer Information (verified 4 Jan 08)
      'payer_email' => array(
        'type' => 'varchar',
        'length' => 127,
        'description' => 'Customers primary email address. Use this email to provide any credits.',
      ),
      'first_name' => array(
        'type' => 'varchar',
        'length' => 64,
        'description' => 'Customers first name',
      ),
      'last_name' => array(
        'type' => 'varchar',
        'length' => 64,
        'description' => 'Customers last name',
      ),
      'payer_business_name' => array(
        'type' => 'varchar',
        'length' => 127,
        'description' => 'Customers company name, if customer represents a business',
      ),
      'address_name' => array(
        'type' => 'varchar',
        'length' => 128,
        'description' => 'Name used with address (included when the customer provides a Gift Address)',
      ),
      'address_street' => array(
        'type' => 'varchar',
        'length' => 200,
        'description' => 'Customers street address',
      ),
      'address_city' => array(
        'type' => 'varchar',
        'length' => 40,
        'description' => 'City of customers address',
      ),
      'address_zip' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'Zip code of customers address.',
      ),
      'address_country' => array(
        'type' => 'varchar',
        'length' => 64,
        'description' => 'Country of customers address.',
      ),
      'address_country_code' => array(
        'type' => 'varchar',
        'length' => 2,
        'description' => 'Two-character ISO 3166 country code',
      ),
      'address_state' => array(
        'type' => 'varchar',
        'length' => 40,
        'description' => 'State of customers address',
      ),
      'address_status' => array(
        'type' => 'varchar',
        'length' => 40,
        'description' => 'Customer provided a confirmed|unconfirmed address.',
      ),
      'payer_id' => array(
        'type' => 'varchar',
        'length' => 13,
        'description' => 'Unique customer ID.',
      ),
      'payer_status' => array(
        'type' => 'varchar',
        'length' => 40,
        'description' => 'Customer has a verified|unverified PayPal account.',
      ),
      'contact_phone' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'Customer�s telephone number.',
      ),
      'residence_country' => array(
        'type' => 'varchar',
        'length' => 2,
        'description' => 'Two-character ISO 3166 country code',
      ),
      # Basic Information (verified 4 Jan 08)
      'business' => array(
        'type' => 'varchar',
        'length' => 127,
        'description' => 'Email address or account ID of the payment recipient (that is, the merchant). Equivalent to the values of receiver_email (if payment is sent to primary account) and business set in the Website Payment HTML.  NOTE: The value of this variable is normalized to lowercase characters.',
      ),
      'item_name' => array(
        'type' => 'varchar',
        'length' => 127,
        'description' => 'Item name as passed by you, the merchant. Or, if not passed by you, as entered by your customer. If this is a shopping cart transaction, PayPal will append the number of the item (e.g., item_name1, item_name2).',
      ),
      'item_number' => array(
        'type' => 'varchar',
        'length' => 127,
        'description' => 'Pass-through variable for you to track purchases. It will get passed back to you at the completion of the payment. If omitted, no variable will be passed back to you.',
      ),
      'quantity' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
        'description' => 'Quantity as entered by your customer or as passed by you, the merchant.',
      ),
      'receiver_email' => array(
        'type' => 'varchar',
        'length' => 127,
        'description' => 'Primary email address of the payment recipient (that is, the merchant). If the payment is sent to a non-primary email address on your PayPal account, the receiver_email is still your primary email.  NOTE: The value of this variable is normalized to lowercase characters.',
      ),
      'receiver_id' => array(
        'type' => 'varchar',
        'length' => 13,
        'description' => 'Unique account ID of the payment recipient (i.e., the merchant).  This is the same as the recipient\'s referral ID.',
      ),
      # Advanced and Custom Information
      'shipping' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'description' => 'Shipping charges associated with this transaction.',
      ),
      'custom' => array(
        'type' => 'varchar',
        'length' => 255,
        'description' => 'Custom value as passed by you, the merchant. These are passthrough variables that are never presented to your customer',
      ),
      'invoice' => array(
        'type' => 'varchar',
        'length' => 127,
        'description' => 'Passthrough variable you can use to identify your Invoice Number for this purchase. If omittted, no variable is passed back.',
      ),
      'memo' => array(
        'type' => 'varchar',
        'length' => 255,
        'description' => 'Memo as entered by your customer in PayPal Website Payments note field.',
      ),
      'option_name1' => array(
        'type' => 'varchar',
        'length' => 64,
        'description' => 'Option 1 name as requested by you.',
      ),
      'option_name2' => array(
        'type' => 'varchar',
        'length' => 64,
        'description' => 'Option 2 name as requested by you.',
      ),
      'option_selection1' => array(
        'type' => 'varchar',
        'length' => 200,
        'description' => 'Option 1 choice as entered by your customer.',
      ),
      'option_selection2' => array(
        'type' => 'varchar',
        'length' => 200,
        'description' => 'Option 2 choice as entered by your customer.',
      ),
      'tax' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'Amount of tax charged on payment.',
      ),
      # SOME FIELDS OMITTED
      'mc_currency' => array(
        'type' => 'varchar',
        'length' => 3,
        'description' => 'Three character currency code',
      ),
      # SOME FIELDS OMITTED
      'payment_fee' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'not null' => FALSE,
        'default' => 0,
        'description' => 'USD transaction fee associated with the payment.  payment_gross minus payment_fee equals the amount deposited into the receiver email account. Is empty for non-USD payments. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction fee.  NOTE: This is a deprecated field. Use mc_fee instead.',
      ),
      'payment_gross' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'not null' => FALSE,
        'default' => 0,
        'description' => 'Full USD amount of the customer\'s payment, before transaction fee is subtracted. Will be empty for non-USD payments. This is a legacy field replaced by mc_gross. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction.',
      ),
      'mc_fee' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'description' => 'Transaction fee associated with the payment. mc_gross minus mc_fee equals the amount deposited into the receiver_email account. Equivalent to payment_fee for USD payments. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction fee.',
      ),
      'mc_gross' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'description' => ' Full amount of the customer\'s payment, before transaction fee is subtracted. Equivalent to payment_gross for USD payments. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction.',
      ),
      'settle_amount' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'description' => 'Amount that is deposited into the accounts primary baal nce after a currency conversion from automatic conversion (through your Payment Receiving Preferences) or manual conversion (through manually accepting a payment).',
      ),
      'handling_amount' => array( /* Undocumented IPN Variable */
        'description' => 'handling amount',
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
      ),
      'protection_eligibility' => array(
        'type' => 'varchar',
        'length' => 30,
        'description' => 'The protection eligibility of the payment.',
      ),
      'transaction_subject' => array(
        'type' => 'varchar',
        'length' => 255,
        'description' => 'Custom value as passed by you, the merchant. These are passthrough variables that are never presented to your customer',
      ),
      'settle_currency' => array(
        'type' => 'varchar',
        'length' => 3,
        'description' => 'Three character currency code.',
      ),
      'exchange_rate' => array(
        'type' => 'numeric',
        'precision' => 12,
        'scale' => 6,
        'description' => 'Exchange rate used if a currency conversion occurred.',
      ),
      # Auctions - NOT SUPPORTED
      # Mass Payment - NOT SUPPORTED
      # Subscriptions Variables
      'txn_type' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'subscr_signup, subscr_cancel, subscr_failed, subscr_payment, subscr_eot, subscr_modify',
      ),
      'subscr_date' => array(
        'type' => 'varchar',
        'length' => 28,
        'description' => 'Start date or cancellation date depending',
      ),
      'subscr_effective' => array(
        'type' => 'varchar',
        'length' => 28,
        'description' => 'Date when the subscription modification will be effective',
      ),
      'period1' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'Trial subscription interval in days, weeks, months, years (example: a 4 day interval is period1: 4 D).',
      ),
      'period2' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'Trial subscription interval in days, weeks, months, or years.',
      ),
      'period3' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'Regular subscription interval in days, weeks, months, or years.',
      ),
      'amount1' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'description' => 'Amount of payment for trial period 1 for USD payments; otherwise blank (optional).',
      ),
      'amount2' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'description' => 'A mount of payment for trial period 2 for USD payments; otherwise blank (optional).',
      ),
      'amount3' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'description' => 'Amount of payment for regular subscription period for USD payments; otherwise blank.',
      ),
      'mc_amount1' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'description' => 'Amount of payment for trial period 1, regardless of currency (optional).',
      ),
      'mc_amount2' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'description' => 'Amount of payment for trial period 2, regardless of currency (optional).',
      ),
      'mc_amount3' => array(
        'type' => 'numeric',
        'precision' => 10,
        'scale' => 2,
        'description' => 'Amount of payment for regular subscription period, regardless of currency.',
      ),
      'recurring' => array(
        'type' => 'int',
        'size' => 'tiny',
        'description' => 'Indicates whether regular rate recurs (1 is yes, blank is no).',
      ),
      'reattempt' => array(
        'type' => 'int',
        'size' => 'tiny',
        'description' => 'Indicates whether reattempts should occur upon payment failures (1 is yes, blank is no).',
      ),
      'retry_at' => array(
        'type' => 'varchar',
        'length' => 28,
        'description' => 'Date PayPal will retry a failed subscription payment.',
      ),
      'recur_times' => array(
        'type' => 'int',
        'description' => 'The number of payment installments that will occur at the regular rate.',
      ),
      'subscr_id' => array(
        'type' => 'varchar',
        'length' => 20,
        'description' => 'ID generated by PayPal for the subscriber.',
      ),
    ),
    'description' => 'Store instant payment notifications from paypal.com',
    'indexes' => array(
      'txn_id' => array('txn_id'),
    ),
    'primary key' => array('id'),
  );
  return $schema;
}

/**
 * Remove javascript email-hiding kludge.
 */
function lm_paypal_update_6000() {
  variable_del('lm_paypal_js_hide_email');
  // hook_update_N() no longer returns a $ret array. Instead, return
  // nothing or a translated string indicating the update ran successfully.
  // See http://drupal.org/node/224333#update_sql.
  return t('TODO Add a descriptive string here to show in the UI.') /* array() */;
}

/**
 * @todo Please document this function.
 * @see http://drupal.org/node/1354
 */
function lm_paypal_update_6001() {
  $ret = array();
  // Date field needs to be 28 characters
  db_change_field('lm_paypal_ipns', 'payment_date', 'payment_date', array('type' => 'varchar', 'length' => 28));
  // hook_update_N() no longer returns a $ret array. Instead, return
  // nothing or a translated string indicating the update ran successfully.
  // See http://drupal.org/node/224333#update_sql.
  return t('TODO Add a descriptive string here to show in the UI.') /* $ret */;
}

/**
 * @todo Please document this function.
 * @see http://drupal.org/node/1354
 */
function lm_paypal_update_6002() {
  $ret = array();
  // address_status changed to char-40
  db_change_field('lm_paypal_ipns', 'address_status', 'address_status', array('type' => 'varchar', 'length' => 40));
  // TODO: replace old "int" values with 'confirmed' or 'unconfirmed'. What were they?

  // add new field paypal docs
  db_add_field('lm_paypal_ipns', 'contact_phone', array('type' => 'varchar', 'length' => 20));

  // Add notify_version and IPN text because paypal keep changing formats on us
  db_add_field('lm_paypal_ipns', 'notify_version', array('type' => 'varchar', 'length' => 20));
  db_add_field('lm_paypal_ipns', 'ipn', array('type' => 'text', 'size' => 'medium'));

  // hook_update_N() no longer returns a $ret array. Instead, return
  // nothing or a translated string indicating the update ran successfully.
  // See http://drupal.org/node/224333#update_sql.
  return t('TODO Add a descriptive string here to show in the UI.') /* $ret */;
}

/**
 * updating fields in lm_paypal_ipns table
 * payment_gross
 * payment_fee
 * adding addtional fields to lm_paypal_ipns table
 * handling_amount
 * protection_eligibility
 * transaction_subject
 * ipn_track_id
 */
function lm_paypal_update_7001() {

  $spec = array(
    'type' => 'numeric',
    'precision' => 10,
    'scale' => 2,
    'not null' => FALSE,
    'default' => 0,
    'description' => 'Full USD amount of the customer\'s payment, before transaction fee is subtracted. Will be empty for non-USD payments. This is a legacy field replaced by mc_gross. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction.',
  );
  db_change_field('lm_paypal_ipns', 'payment_gross', 'payment_gross', $spec);

  $spec = array(
    'type' => 'numeric',
    'precision' => 10,
    'scale' => 2,
    'not null' => FALSE,
    'default' => 0,
    'description' => 'USD transaction fee associated with the payment.  payment_gross minus payment_fee equals the amount deposited into the receiver email account. Is empty for non-USD payments. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction fee.  NOTE: This is a deprecated field. Use mc_fee instead.',
  );
  db_change_field('lm_paypal_ipns', 'payment_fee', 'payment_fee', $spec);
  
  $spec = array( /* Undocumented IPN Variable */
    'description' => 'Fi',
    'type' => 'numeric',
    'precision' => 10,
    'scale' => 2,
  );
  db_add_field('lm_paypal_ipns', 'handling_amount', $spec);

  $spec = array( /* Undocumented IPN Variable */
    'type' => 'varchar',
    'length' => 30,
    'description' => 'The protection eligibility of the payment.',
  );
  db_add_field('lm_paypal_ipns', 'protection_eligibility', $spec);

  $spec = array(
    'type' => 'varchar',
    'length' => 255,
    'description' => 'Custom value as passed by you, the merchant. These are passthrough variables that are never presented to your customer',
  );
  db_add_field('lm_paypal_ipns', 'transaction_subject', $spec);
  $spec = array(
    'type' => 'text',
    'size' => 'medium',
    'description' => 'Tracking id',
  );
  db_add_field('lm_paypal_ipns', 'ipn_track_id', $spec);
}