function generateInvoices($ids, $account_id, $invoice_id, $charge_installed = false)
{
if (empty($ids)) {
return false;
}
# load required elements
include_once PATH_MODULES . 'service/service.inc.php';
include_once PATH_MODULES . 'discount/discount.inc.php';
include_once PATH_MODULES . 'tax/tax.inc.php';
$taxObj = new tax();
$serviceObj = new service();
# start a transaction
$db =& DB();
#$db->debug=true;
if (AGILE_DB_TYPE == 'mysqlt') {
$db->StartTrans();
if (!$db->hasTransactions) {
global $C_debug;
$msg = "Transactions not supported in 'mysql' driver. Use 'mysqlt' or 'mysqli' driver";
$C_debug->alert($msg);
$C_debug->error('invoice.inc.php', 'generateInvoices()', "Transactions not supported in 'mysql' driver. Use 'mysqlt' or 'mysqli' driver");
return false;
}
}
# generate an invoice id
$invoice = sqlGenID($db, 'invoice');
# check for any discounts for the parent invoice or account_id
# applied at checkout and should continue to be applied if recurring type discount
$discountObj = new discount();
$discountObj->available_discounts($account_id, 1, $invoice_id);
# beginning totals
$sub_total = 0;
$taxable_amount = 0;
$tax_amt = 0;
$discount_amt = 0;
# get the full account and service and invoice details
$p = AGILE_DB_PREFIX;
$s = DEFAULT_SITE;
$sql = "SELECT DISTINCT \n\t\tservice.id, service.parent_id, service.invoice_id, service.invoice_item_id, service.account_id, service.account_billing_id, service.product_id,\n\t\tservice.sku, service.active, service.bind, service.type, service.price, service.price_type, service.taxable, service.date_last_invoice, service.date_next_invoice,\n\t\tservice.recur_type, service.recur_schedule, service.recur_weekday, service.recur_week, service.domain_name,\n\t\tservice.domain_tld, service.domain_type, service.domain_term, service.prod_attr, service.prod_attr_cart,\n\t\taccount.currency_id, account.first_name, account.last_name, account.country_id, account.state, account.invoice_grace, account.invoice_advance_gen, account.affiliate_id as account_affiliate_id,\n\t\tinvoice.affiliate_id, invoice.campaign_id, invoice.reseller_id, invoice.checkout_plugin_id, invoice.checkout_plugin_data, invoice.billed_currency_id, invoice.actual_billed_currency_id \n\t\tFROM {$p}service as service \n\t\tJOIN {$p}account as account ON ( service.account_id=account.id and account.site_id={$s} )\n\t\tLEFT JOIN {$p}invoice as invoice ON ( service.invoice_id=invoice.id and invoice.site_id={$s} )\n\t\tWHERE service.id in ({$ids})";
$service = $db->Execute($sql);
if ($service === false) {
global $C_debug;
$C_debug->error('invoice.inc.php', 'generateInvoices()1', $sql . " \r\n\r\n " . @$db->ErrorMsg());
$db->FailTrans();
return false;
}
if ($service && $service->RecordCount()) {
while (!$service->EOF) {
if (empty($service->fields['billed_currency_id'])) {
$service->fields['billed_currency_id'] = DEFAULT_CURRENCY;
}
if (empty($service->fields['actual_billed_currency_id'])) {
$service->fields['actual_billed_currency_id'] = $service->fields['billed_currency_id'];
}
$this->account_id = $service->fields['account_id'];
$this->parent_id = $service->fields['invoice_id'];
$this->account_billing_id = $service->fields['account_billing_id'];
if (!empty($service->fields['account_affiliate_id'])) {
$this->affiliate_id = $service->fields['account_affiliate_id'];
} else {
$this->affiliate_id = $service->fields['affiliate_id'];
}
$this->campaign_id = $service->fields['campaign_id'];
$this->reseller_id = $service->fields['reseller_id'];
$this->checkout_plugin_id = $service->fields['checkout_plugin_id'];
$this->checkout_plugin_data = $service->fields['checkout_plugin_data'];
$this->billed_currency_id = $service->fields['billed_currency_id'];
$this->actual_billed_currency_id = $service->fields['actual_billed_currency_id'];
$this->invoice_grace = $service->fields['invoice_grace'];
$item_tax_amt = 0;
$item_total_amt = 0;
$item_discount_amt = 0;
# gen item_id
$item = sqlGenID($db, "invoice_item");
# Calculate any recurring discounts for this item
$item_total_amt = $service->fields['price'];
$item_discount_amt = $discountObj->calc_all_discounts(1, $item, $service->fields['product_id'], $service->fields['price'], $service->fields['account_id'], $sub_total + $service->fields['price']);
$item_total_amt -= $item_discount_amt;
$sub_total += $item_total_amt;
$discount_amt += $item_discount_amt;
# calculate any taxes for this item
if ($service->fields['taxable'] == 1) {
$item_tax_amt = 0;
$item_tax_arr = $taxObj->calculate($item_total_amt, $service->fields['country_id'], $service->fields['state']);
if (is_array($item_tax_arr)) {
foreach ($item_tax_arr as $tx) {
$item_tax_amt += $tx['rate'];
}
}
$tax_amt += $item_tax_amt;
}
# Calculate next invoice date
$next_invoice = $serviceObj->calcNextInvoiceDate($service->fields['date_next_invoice'], $service->fields['recur_schedule'], $service->fields['recur_type'], $service->fields['recur_weekday']);
$due_date = $service->fields['date_next_invoice'];
$recur_schedule = 0;
if (!empty($service->fields['recur_schedule'])) {
$recur_schedule = $service->fields['recur_schedule'];
}
// create the invoice item
$sql = "INSERT INTO {$p}invoice_item SET\n\t\t\t\t\tid={$item},\n\t\t\t\t\tsite_id={$s}, \n\t\t\t\t\tdate_orig=" . time() . ", \n\t\t\t\t\tinvoice_id = {$invoice}, \n\t\t\t\t\taccount_id={$service->fields['account_id']}, \n\t\t\t\t\tservice_id={$service->fields['id']}, \t\n\t\t\t\t\tproduct_id={$service->fields['product_id']}, \n\t\t\t\t\tproduct_attr=" . $db->qstr($service->fields['prod_attr']) . ", \n\t\t\t\t\tproduct_attr_cart=" . $db->qstr($service->fields['prod_attr_cart']) . ", \t\n\t\t\t\t\tsku=" . $db->qstr($service->fields['sku']) . ", \n\t\t\t\t\tquantity=1, \n\t\t\t\t\titem_type=0, \n\t\t\t\t\tprice_type={$service->fields['price_type']}, \n\t\t\t\t\tprice_base={$service->fields['price']}, \n\t\t\t\t\tprice_setup=0, \n\t\t\t\t\trecurring_schedule={$recur_schedule}, \n\t\t\t\t\tdate_start={$service->fields['date_next_invoice']}, \n\t\t\t\t\tdate_stop={$next_invoice}, \n\t\t\t\t\tdomain_name=" . $db->qstr($service->fields['domain_name']) . ", \n\t\t\t\t\tdomain_tld=" . $db->qstr($service->fields['domain_tld']) . ", \n\t\t\t\t\tdomain_type=" . $db->qstr($service->fields['domain_type']) . ", \n\t\t\t\t\ttax_amt={$tax_amt}, \n\t\t\t\t\ttotal_amt={$item_total_amt}, \n\t\t\t\t\tdiscount_amt={$item_discount_amt}";
$itemrs = $db->Execute($sql);
if ($itemrs === false) {
global $C_debug;
$C_debug->error('invoice.inc.php', 'generateInvoices()2', $sql . " \r\n\r\n " . @$db->ErrorMsg());
$db->FailTrans();
return false;
}
// Insert tax records
$taxObj->invoice_item($invoice, $item, $service->fields['account_id'], @$item_tax_arr);
# Insert discount records
$discountObj->invoice_item($invoice, $item, $service->fields['account_id'], @$discountObj->discount_arr);
// Update the last & next invoice date for this service
$sql = "UPDATE {$p}service \n\t\t\t\t\tSET \n\t\t\t\t\tdate_last_invoice = {$service->fields['date_next_invoice']}, \n\t\t\t\t\tdate_next_invoice = {$next_invoice} \n\t\t\t\t\tWHERE\n\t\t\t\t\tsite_id={$s} AND id = {$service->fields['id']} ";
$srvsrs = $db->Execute($sql);
if ($srvsrs === false) {
global $C_debug;
$C_debug->error('invoice.inc.php', 'generateInvoices()3', $sql . " \r\n\r\n " . @$db->ErrorMsg());
$db->FailTrans();
return false;
}
// get any charges for this service and create them as invoice items
if ($charge_installed) {
$sql = "SELECT * FROM " . AGILE_DB_PREFIX . "charge WHERE (status=0 or status is null) and site_id=" . DEFAULT_SITE . " AND service_id = " . $service->fields['id'] . " AND date_orig < " . $service->fields['date_next_invoice'];
$charge = $db->Execute($sql);
if ($charge && $charge->RecordCount()) {
while (!$charge->EOF) {
$item_tax_amt = 0;
$item_total_amt = 0;
$item_discount_amt = 0;
// Calculate any recurring discounts for this charge item
$item_total_amt = $charge->fields['quantity'] * $charge->fields['amount'];
$item_discount_amt = $discountObj->calc_all_discounts(1, $item, $charge->fields['product_id'], $item_total_amt, $service->fields['account_id'], $sub_total + $item_total_amt);
$item_total_amt -= $item_discount_amt;
$sub_total += $item_total_amt;
$discount_amt += $item_discount_amt;
// calculate any taxes for this item
if ($charge->fields['taxable'] == 1) {
$item_tax_amt = 0;
$item_tax_arr = $taxObj->calculate($chargeamt, $service->fields['country_id'], $service->fields['state']);
if (is_array($item_tax_arr)) {
foreach ($item_tax_arr as $tx) {
$item_tax_amt += $tx['rate'];
}
}
$tax_amt += $item_tax_amt;
}
// create the invoice item
$charge_item_id = sqlGenID($db, 'invoice_item');
$sql = "INSERT INTO {$p}invoice_item SET \n\t\t\t\t\t\t\t\tid\t\t\t= {$charge_item_id},\t\t\n\t\t\t\t\t\t\t\tsite_id\t\t= {$s},\n\t\t\t\t\t\t\t\tcharge_id\t= {$charge->fields['id']},\n\t\t\t\t\t\t\t\tdate_orig \t= " . time() . ",\n\t\t\t\t\t\t\t\tinvoice_id \t= {$invoice}, \n\t\t\t\t\t\t\t\taccount_id \t= " . $this->account_id . ", \n\t\t\t\t\t\t\t\tservice_id \t= " . $db->qstr($service->fields['id']) . ",\n\t\t\t\t\t\t\t\tproduct_id \t= " . $db->qstr($charge->fields['product_id']) . ", \n\t\t\t\t\t\t\t\tproduct_attr= " . $db->qstr($charge->fields['attributes']) . ", \t\n\t\t\t\t\t\t\t\tsku \t\t= " . $db->qstr($service->fields['sku']) . ", \n\t\t\t\t\t\t\t\tprice_base \t= " . $db->qstr($charge->fields['amount']) . ", \n\t\t\t\t\t\t\t\tquantity \t= " . $charge->fields['quantity'] . ", \n\t\t\t\t\t\t\t\titem_type \t= 5,\n\t\t\t\t\t\t\t\tprice_type \t= 0,\n\t\t\t\t\t\t\t\tprice_setup = 0, \n\t\t\t\t\t\t\t\ttax_amt \t= {$item_tax_amt}, \n\t\t\t\t\t\t\t\ttotal_amt \t= {$item_total_amt}, \n\t\t\t\t\t\t\t\tdiscount_amt= {$item_discount_amt}";
$itemrs = $db->Execute($sql);
if ($itemrs === false) {
global $C_debug;
$C_debug->error('invoice.inc.php', 'generateInvoices()4', $sql . " \r\n\r\n " . @$db->ErrorMsg());
$db->FailTrans();
return false;
}
# Insert tax records
$taxObj->invoice_item($invoice, $charge_item_id, $charge->fields['account_id'], @$item_tax_arr);
# Insert discount records
$discountObj->invoice_item($invoice, $charge_item_id, $charge->fields['account_id'], @$discountObj->discount_arr);
# update charge status
$chargers = $db->Execute("UPDATE " . AGILE_DB_PREFIX . "charge set status=1 WHERE id={$charge->fields['id']} AND site_id=" . DEFAULT_SITE);
if ($chargers === false) {
global $C_debug;
$C_debug->error('invoice.inc.php', 'generateInvoices()2', $sql . " \r\n\r\n " . @$db->ErrorMsg());
$db->FailTrans();
return false;
}
$charge->MoveNext();
}
}
}
$service->MoveNext();
}
// add any taxes
@($total = $sub_total + $tax_amt);
// get invoice grace period from global/account
if (!empty($this->invoice_grace)) {
$grace_period = $this->invoice_grace;
} else {
$grace_period = GRACE_PERIOD;
}
$sql = "INSERT INTO {$p}invoice SET \n\t\t\t\tid={$invoice},\n\t\t\t\tsite_id={$s},\n\t\t\t\tdate_orig = " . time() . ", \n\t\t\t\tdate_last = " . time() . ", \n\t\t\t\tnotice_next_date = " . time() . ",\n\t\t\t\ttype = 1, \n\t\t\t\tprocess_status = 0, \n\t\t\t\tbilling_status = 0, \n\t\t\t\tsuspend_billing = 0, \n\t\t\t\tprint_status = 0, \n\t\t\t\trefund_status = 0, \n\t\t\t\tbilled_amt = 0, \n\t\t\t\tactual_billed_amt = 0, \n\t\t\t\tnotice_count = 0, \n\t\t\t\tparent_id = " . $db->qstr($this->parent_id) . ",\n\t\t\t\taccount_id = {$this->account_id}, \n\t\t\t\taccount_billing_id = " . $db->qstr($this->account_billing_id) . ",\n\t\t\t\taffiliate_id = " . $db->qstr($this->affiliate_id) . ",\n\t\t\t\tcampaign_id = " . $db->qstr($this->campaign_id) . ",\n\t\t\t\treseller_id = " . $db->qstr($this->reseller_id) . ",\n\t\t\t\tcheckout_plugin_id = " . $db->qstr($this->checkout_plugin_id) . ",\n\t\t\t\tcheckout_plugin_data = " . $db->qstr($this->checkout_plugin_data) . ",\n\t\t\t\tactual_billed_currency_id = " . $db->qstr($this->actual_billed_currency_id) . ",\n\t\t\t\tbilled_currency_id = " . $db->qstr($this->billed_currency_id) . ",\n\t\t\t\tnotice_max = " . $db->qstr(MAX_BILLING_NOTICE) . ", \n\t\t\t\tgrace_period = " . $db->qstr($grace_period) . ",\n\t\t\t\ttax_amt = " . $tax_amt . ", \n\t\t\t\tdiscount_amt = " . $discount_amt . ", \n\t\t\t\ttotal_amt = " . $total . ", \n\t\t\t\tdue_date = {$due_date}";
$invoicers = $db->Execute($sql);
if ($invoicers === false) {
global $C_debug;
$C_debug->error('invoice.inc.php', 'generateInvoices()2', $sql . " \r\n\r\n " . @$db->ErrorMsg());
$db->FailTrans();
return false;
}
}
if (AGILE_DB_TYPE == 'mysqlt') {
$db->CompleteTrans();
}
}
function services()
{
global $VAR, $C_debug;
$p = AGILE_DB_PREFIX;
$s = DEFAULT_SITE;
### Connect to the remote Db;
$dbr =& NewADOConnection($this->type);
$dbr->Connect($this->host, $this->user, $this->pass, $this->db);
### Determine the offset for the account
if (empty($VAR['offset'])) {
$VAR['offset'] = 0;
}
@($offset = $VAR['offset'] . "," . $this->select_limit);
# select each account from HostAdmin
$sql = "SELECT domains.*,account.account_password \n\t\t\t\tFROM \n\t\t\t\tdomains,account\n\t\t\t\tWHERE\n\t\t\t\tdomains.domain_account_id = account.account_id";
$rs = $dbr->SelectLimit($sql, $this->select_limit, $VAR['offset']);
if ($rs === false) {
$C_debug->alert("Query to the table 'domains' failed!");
return false;
}
if ($rs->RecordCount() == 0) {
$C_debug->alert("No more records to process!");
echo "<script language=javascript>setTimeout('document.location=\\'?_page=import:import&plugin={$VAR['plugin']}\\'', 1500); </script>";
return;
}
$msg = "Processing " . $rs->RecordCount() . " Records...<BR>";
# loop through each remote account
while (!$rs->EOF) {
$msg .= "<BR>Processing Subscription: {$rs->fields['domain_id']}...";
# start a new transaction for the insert:
$db =& DB();
$db->StartTrans();
# Get orig date
if (!empty($rs->fields['domain_start_date'])) {
$date = explode('-', $rs->fields['domain_start_date']);
$date_orig = mktime(0, 0, 0, $date[1], $date[2], $date[0]);
} else {
$date_orig = time();
}
### Get the default checkout plugin id:
$sql = "SELECT id FROM {$p}checkout WHERE\n\t\t\t\t\tsite_id = {$s} AND\n\t\t\t\t\tcheckout_plugin = '{$this->gateway}'";
$ch = $db->Execute($sql);
$checkout_plugin_id = $ch->fields['id'];
# get the account id
$sql = "SELECT ab_id FROM {$p}import WHERE site_id = {$s} AND\n\t\t\t\t\t\tab_table = 'account' AND\n\t\t\t\t\t\tplugin = '{$this->plugin}' AND\n\t\t\t\t\t\tremote_id = '{$rs->fields['domain_account_id']}'";
$account = $db->Execute($sql);
$account_id = $account->fields['ab_id'];
# get the billing id
$sql = "SELECT ab_id FROM {$p}import WHERE site_id = {$s} AND\n\t\t\t\t\t\tab_table = 'account_billing' AND\n\t\t\t\t\t\tplugin = '{$this->plugin}' AND\n\t\t\t\t\t\tremote_id = '{$rs->fields['domain_billing_id']}'";
$billing = $db->Execute($sql);
$billing_id = $billing->fields['ab_id'];
# get the invoice id
$sql = "SELECT ab_id FROM {$p}import WHERE site_id = {$s} AND\n\t\t\t\t\t\tab_table = 'invoice' AND\n\t\t\t\t\t\tplugin = '{$this->plugin}' AND\n\t\t\t\t\t\tremote_id = '{$rs->fields['domain_order_id']}'";
$invoice = $db->Execute($sql);
$invoice_id = $invoice->fields['ab_id'];
# Status
if ($rs->fields['domain_host_status'] == 1) {
$active = 1;
$suspend = 0;
} else {
$active = 0;
$suspend = 1;
}
$term = $rs->fields['domain_years'];
$domain_name = strtolower($rs->fields['domain_name']);
$arr = split('\\.', $domain_name);
$tld = '';
$domain = $arr[0];
for ($i = 0; $i < count($arr); $i++) {
if ($i > 0) {
if ($i > 1) {
$tld .= ".";
}
$tld .= $arr[$i];
}
}
# Determine the tld_id
$sql = "SELECT id,registrar_plugin_id FROM {$p}host_tld WHERE site_id = {$s} AND name= '{$tld}'";
$tldrs = $db->Execute($sql);
$domain_host_tld_id = $tldrs->fields['id'];
$domain_host_registrar_id = $tldrs->fields['registrar_plugin_id'];
# Determine the SKU for the DOMAIN SERVICE record
if ($rs->fields['domain_years'] > 0) {
# Domain transfer / hosting only
$sku = 'DOMAIN-REGISTER';
$domain_type = 'register';
# Get the price
$dbr =& NewADOConnection($this->type);
$dbr->Connect($this->host, $this->user, $this->pass, $this->db);
$sql = "SELECT * FROM domain_type WHERE domain_type_id = {$rs->fields['domain_type_id']}";
$domainrs = $dbr->SelectLimit($sql, $offset);
if (empty($rs->fields['domain_host_id'])) {
$price = $domainrs->fields["domain_type_pwo{$term}"];
} else {
$price = $domainrs->fields["domain_type_p{$term}"];
}
$db =& DB();
} else {
# Domain registration + Hosting
$sku = 'DOMAIN-TRANSFER';
$domain_type = 'ns_transfer';
$price = 0;
}
### Create the DOMAIN service records:
if ($sku != 'DOMAIN-TRANSFER') {
# Determine the domain expire date:
$date_expire = $date_orig + 86400 * 365 * $rs->fields['domain_years'];
# Insert the service record
$id = $db->GenID($p . 'service_id');
$sql = "INSERT INTO {$p}service SET\n\t\t\t\t\t\tid \t\t\t\t\t= {$id},\n\t\t\t\t\t\tsite_id\t\t\t\t= {$s}, \n\t\t\t\t\t\tqueue\t\t\t\t= 'none',\n\t\t\t\t\t\tdate_orig\t\t\t= " . $db->qstr($date_orig) . ",\n\t\t\t\t\t\tdate_last\t\t\t= " . $db->qstr(time()) . ", \n\t\t\t\t\t\tinvoice_id\t\t\t= " . $db->qstr(@$invoice_id) . ", \n\t\t\t\t\t\taccount_id\t\t\t= " . $db->qstr(@$account_id) . ",\n\t\t\t\t\t\taccount_billing_id\t= " . $db->qstr(@$billing_id) . ",\n\t\t\t\t\t\tproduct_id\t\t\t= " . $db->qstr(@$product_id) . ",\n\t\t\t\t\t\tsku\t\t\t\t\t= " . $db->qstr($sku) . ", \n\t\t\t\t\t\ttype\t\t\t\t= " . $db->qstr('domain') . ", \n\t\t\t\t\t\tactive\t\t\t\t= 1, \n\t\t\t\t\t\tprice\t\t\t\t= " . $db->qstr($price) . ",\n\t\t\t\t\t\tprice_type\t\t\t= " . $db->qstr('0') . ",\n\t\t\t\t\t\ttaxable\t\t\t\t= " . $db->qstr('0') . ", \n\t\t\t\t\t\t\n\t\t\t\t\t\tdomain_date_expire\t= " . $db->qstr($date_expire) . ",\n\t\t\t\t\t\tdomain_host_tld_id\t= " . $db->qstr($domain_host_tld_id) . ",\n\t\t\t\t\t\tdomain_host_registrar_id = " . $db->qstr($domain_host_registrar_id) . ",\n\t\t\t\t\t\t\n\t\t\t\t\t\tdomain_name \t\t= " . $db->qstr($domain) . ",\n\t\t\t\t\t\tdomain_term\t\t \t= " . $db->qstr($term) . ",\n\t\t\t\t\t\tdomain_tld \t\t= " . $db->qstr($tld) . ",\n\t\t\t\t\t\tdomain_type\t\t\t= " . $db->qstr($domain_type);
$db->Execute($sql);
# Insert the import record
$this->import_transaction($this->plugin, $VAR['action'], 'service', $id, 'domains', $rs->fields['domain_id'], &$db);
}
# Insert the DOMAIN invoice_item record:
$idx = $db->GenID($p . 'invoice_item_id');
$sql = "INSERT INTO {$p}invoice_item SET\n\t\t\t\t\t\tid \t\t\t\t\t= {$idx},\n\t\t\t\t\t\tsite_id\t\t\t\t= {$s}, \n\t\t\t\t\t\tinvoice_id\t\t\t= " . $db->qstr(@$invoice_id) . ", \n\t\t\t\t\t\tdate_orig\t\t\t= " . $db->qstr($date_orig) . ", \n\t\t\t\t\t\tsku\t\t\t\t\t= " . $db->qstr($sku) . ",\n\t\t\t\t\t\tquantity\t\t\t= 1,\n\t\t\t\t\t\titem_type\t\t\t= 2,\n\t\t\t\t\t\tprice_type\t\t\t= 0,\n\t\t\t\t\t\tprice_base\t\t\t= " . $db->qstr($price) . ", \n\t\t\t\t\t\tdomain_name \t\t= " . $db->qstr($domain) . ",\n\t\t\t\t\t\tdomain_term\t\t \t= " . $db->qstr($term) . ",\n\t\t\t\t\t\tdomain_tld \t\t= " . $db->qstr($tld) . ",\n\t\t\t\t\t\tdomain_type\t\t\t= " . $db->qstr($domain_type);
$db->Execute($sql);
# Insert the import record
$this->import_transaction($this->plugin, $VAR['action'], 'invoice_item', $idx, 'domains', $rs->fields['domain_id'], &$db);
#### HOSTING Service & Item insertion
if (!empty($rs->fields['domain_host_id'])) {
# get the product id
$sql = "SELECT ab_id FROM {$p}import WHERE site_id = {$s} AND\n\t\t\t\t\t\t\tab_table = 'product' AND\n\t\t\t\t\t\t\tplugin = '{$this->plugin}' AND\n\t\t\t\t\t\t\tremote_id = '{$rs->fields['domain_host_id']}'";
$product = $db->Execute($sql);
$product_id = $product->fields['ab_id'];
# Get the product details
$sql = "SELECT * FROM {$p}product WHERE site_id = {$s} AND id = {$product_id}";
$product = $db->Execute($sql);
$sku = $product->fields['sku'];
# Get last billed date date
if (!empty($rs->fields['domain_host_last_billed'])) {
$date = explode('-', $rs->fields['domain_host_last_billed']);
$date_last = mktime(0, 0, 0, $date[1], $date[2], $date[0]);
} else {
$date_last = $date_orig;
}
# Calculate next bill date:
include_once PATH_MODULES . 'service/service.inc.php';
$service = new service();
$date_next = $service->calcNextInvoiceDate($date_last, $product->fields['price_recurr_default'], $product->fields['price_recurr_type'], $product->fields['price_recurr_weekday'], $product->fields['price_recurr_week']);
### Create the HOSTING service records:
$id = $db->GenID($p . 'service_id');
$sql = "INSERT INTO {$p}service SET\n\t\t\t\t\t\tid \t\t\t\t\t= {$id},\n\t\t\t\t\t\tsite_id\t\t\t\t= {$s}, \n\t\t\t\t\t\tqueue\t\t\t\t= 'none',\n\t\t\t\t\t\tdate_orig\t\t\t= " . $db->qstr($date_orig) . ",\n\t\t\t\t\t\tdate_last\t\t\t= " . $db->qstr(time()) . ", \n\t\t\t\t\t\tinvoice_id\t\t\t= " . $db->qstr(@$invoice_id) . ", \n\t\t\t\t\t\taccount_id\t\t\t= " . $db->qstr(@$account_id) . ",\n\t\t\t\t\t\taccount_billing_id\t= " . $db->qstr(@$billing_id) . ",\n\t\t\t\t\t\tproduct_id\t\t\t= " . $db->qstr(@$product_id) . ", \n\t\t\t\t\t\tsku\t\t\t\t\t= " . $db->qstr($sku) . ", \n\t\t\t\t\t\ttype\t\t\t\t= " . $db->qstr('host') . ", \n\t\t\t\t\t\t\n\t\t\t\t\t\tactive\t\t\t\t= " . $db->qstr($active) . ", \n\t\t\t\t\t\tsuspend_billing\t\t= " . $db->qstr($suspend) . ", \n\t\t\t\t\t\t\n\t\t\t\t\t\tdate_last_invoice\t= " . $db->qstr($date_orig) . ",\n\t\t\t\t\t\tdate_next_invoice\t= " . $db->qstr($date_next) . ",\n\t\t\t\t\t\t \n\t\t\t\t\t\tprice\t\t\t\t= " . $db->qstr($product->fields['price_base']) . ",\n\t\t\t\t\t\tprice_type\t\t\t= 1,\n\t\t\t\t\t\ttaxable\t\t\t\t= 1,\t\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\trecur_type\t\t\t= " . $db->qstr($product->fields['price_recurr_type']) . ",\n\t\t\t\t\t\trecur_schedule\t\t= " . $db->qstr($product->fields['price_recurr_schedule']) . ",\n\t\t\t\t\t\trecur_weekday\t\t= " . $db->qstr($product->fields['price_recurr_weekday']) . ",\n\t\t\t\t\t\trecur_week\t\t\t= " . $db->qstr($product->fields['price_recurr_week']) . ",\n\t\t\t\t\t\trecur_cancel\t\t= " . $db->qstr($product->fields['price_recurr_cancel']) . ",\n\t\t\t\t\t\trecur_schedule_change = " . $db->qstr($product->fields['price_recurr_modify']) . ",\n\n\t\t\t\t\t\thost_username\t\t= " . $db->qstr($rs->fields['cp_login']) . ",\n\t\t\t\t\t\thost_password\t\t= " . $db->qstr($rs->fields['account_password']) . ",\n\t\t\t\t\t\t\n\t\t\t\t\t\thost_server_id\t\t\t\t= " . $db->qstr($product->fields['host_server_id']) . ",\n\t\t\t\t\t\thost_provision_plugin_data \t= " . $db->qstr($product->fields['host_provision_plugin_data']) . ",\n\t\t\t\t\t\thost_ip\t\t\t\t\t\t= " . $db->qstr($rs->fields['ip']) . ",\n\t\t\t\t\t\t \n\t\t\t\t\t\tdomain_host_tld_id\t\t\t= " . $db->qstr($domain_host_tld_id) . ",\n\t\t\t\t\t\tdomain_host_registrar_id \t= " . $db->qstr($domain_host_registrar_id) . ",\n\t\t\t\t\t\t\n\t\t\t\t\t\tdomain_name \t\t= " . $db->qstr($domain) . ", \n\t\t\t\t\t\tdomain_tld \t\t= " . $db->qstr($tld);
$db->Execute($sql);
# Insert the import record
$this->import_transaction($this->plugin, $VAR['action'], 'service', $id, 'domains', $rs->fields['domain_id'], &$db);
# Insert the HOSTING invoice_item record:
$idx = $db->GenID($p . 'invoice_item_id');
$sql = "INSERT INTO {$p}invoice_item SET\n\t\t\t\t\t\tid \t\t\t\t\t= {$idx},\n\t\t\t\t\t\tsite_id\t\t\t\t= {$s}, \n\t\t\t\t\t\tinvoice_id\t\t\t= " . $db->qstr(@$invoice_id) . ", \n\t\t\t\t\t\tproduct_id\t\t\t= " . $db->qstr(@$product_id) . ",\n\t\t\t\t\t\tdate_orig\t\t\t= " . $db->qstr($date_orig) . ", \n\t\t\t\t\t\tsku\t\t\t\t\t= " . $db->qstr($sku) . ",\n\t\t\t\t\t\tquantity\t\t\t= 1,\n\t\t\t\t\t\titem_type\t\t\t= 1, \n\t\t\t\t\t\t\n\t\t\t\t\t\tprice_type\t\t\t= 1,\n\t\t\t\t\t\tprice_base\t\t\t= " . $db->qstr($product->fields['price_base']) . ", \n\t\t\t\t\t\tdomain_name \t\t= " . $db->qstr($domain) . ", \n\t\t\t\t\t\tdomain_tld \t\t= " . $db->qstr($tld) . ", \t \n\t\t\t\t\t\t\n\t\t\t\t\t\trecurring_schedule = " . $db->qstr($product->fields['price_recurr_schedule']);
$db->Execute($sql);
# Insert the import record
$this->import_transaction($this->plugin, $VAR['action'], 'invoice_item', $idx, 'domains', $rs->fields['domain_id'], &$db);
}
# Complete the transaction
$db->CompleteTrans();
$rs->MoveNext();
}
$C_debug->alert($msg);
$offset = $VAR['offset'] + $this->select_limit;
echo "<script language=javascript> \n\t\t\t setTimeout('document.location=\\'?_page=core:blank&offset={$offset}&action={$VAR['action']}&plugin={$VAR['plugin']}&do[]=import:do_action\\'', 1200);\n\t\t\t </script>";
}
function services()
{
global $VAR, $C_debug;
$p = AGILE_DB_PREFIX;
$s = DEFAULT_SITE;
### Connect to the remote Db;
$dbr =& NewADOConnection($this->type);
$dbr->Connect($this->host, $this->user, $this->pass, $this->db);
### Determine the offset for the account
if (empty($VAR['offset'])) {
$VAR['offset'] = 0;
}
@($offset = $VAR['offset'] . "," . $this->select_limit);
# select each account from Dreamaccount
$sql = "SELECT * FROM domains";
$rs = $dbr->SelectLimit($sql, $this->select_limit, $VAR['offset']);
if ($rs === false) {
$C_debug->alert("Query to the table 'domains' failed!");
return false;
}
if ($rs->RecordCount() == 0) {
$C_debug->alert("No more records to process!");
echo "<script language=javascript>setTimeout('document.location=\\'?_page=import:import&plugin={$VAR['plugin']}\\'', 1500); </script>";
return;
}
$msg = "Processing " . $rs->RecordCount() . " Records...<BR>";
# loop through each remote account
while (!$rs->EOF) {
$msg .= "<BR>Processing Subscription: {$rs->fields['domain_id']}...";
# start a new transaction for the insert:
$db =& DB();
$db->StartTrans();
# Get a local id
$id = $db->GenID($p . 'service_id');
# Get orig date
if (!empty($rs->fields['domain_start_date'])) {
$date = explode('-', $rs->fields['domain_start_date']);
$date_orig = mktime(0, 0, 0, $date[1], $date[2], $date[0]);
} else {
$date_orig = time();
}
# Get last billed date date
if (!empty($rs->fields['domain_host_last_billed'])) {
$date = explode('-', $rs->fields['domain_host_last_billed']);
$date_last = mktime(0, 0, 0, $date[1], $date[2], $date[0]);
} else {
$date_last = $date_orig;
}
### Get the default checkout plugin id:
$sql = "SELECT id FROM {$p}checkout WHERE\n\t\t\t\t\tsite_id = {$s} AND\n\t\t\t\t\tcheckout_plugin = '{$this->gateway}'";
$ch = $db->Execute($sql);
$checkout_plugin_id = $ch->fields['id'];
# get the account id
$sql = "SELECT ab_id FROM {$p}import WHERE site_id = {$s} AND\n\t\t\t\t\t\tab_table = 'account' AND\n\t\t\t\t\t\tplugin = '{$this->plugin}' AND\n\t\t\t\t\t\tremote_id = '{$rs->fields['domain_account_id']}'";
$account = $db->Execute($sql);
$account_id = $account->fields['ab_id'];
# get the billing id
$sql = "SELECT ab_id FROM {$p}import WHERE site_id = {$s} AND\n\t\t\t\t\t\tab_table = 'account_billing' AND\n\t\t\t\t\t\tplugin = '{$this->plugin}' AND\n\t\t\t\t\t\tremote_id = '{$rs->fields['domain_billing_id']}'";
$billing = $db->Execute($sql);
$billing_id = $billing->fields['ab_id'];
# get the invoice id
$sql = "SELECT ab_id FROM {$p}import WHERE site_id = {$s} AND\n\t\t\t\t\t\tab_table = 'invoice' AND\n\t\t\t\t\t\tplugin = '{$this->plugin}' AND\n\t\t\t\t\t\tremote_id = '{$rs->fields['domain_order_id']}'";
$invoice = $db->Execute($sql);
$invoice_id = $invoice->fields['ab_id'];
# get the product id
$sql = "SELECT ab_id FROM {$p}import WHERE site_id = {$s} AND\n\t\t\t\t\t\tab_table = 'product' AND\n\t\t\t\t\t\tplugin = '{$this->plugin}' AND\n\t\t\t\t\t\tremote_id = '{$rs->fields['domain_host_id']}'";
$product = $db->Execute($sql);
$product_id = $product->fields['ab_id'];
# Get the product details
$sql = "SELECT * FROM {$p}product WHERE site_id = {$s} AND id = {$product_id}";
$product = $db->Execute($sql);
# Status
if ($rs->fields['domain_host_status'] == 1) {
$active = 1;
$suspend = 0;
} else {
$active = 0;
$suspend = 1;
}
# Calculate next bill date:
include_once PATH_MODULES . 'service/service.inc.php';
$service = new service();
$date_next = $service->calcNextInvoiceDate($date_last, $product->fields['price_recurr_default'], $product->fields['price_recurr_type'], $product->fields['price_recurr_weekday'], $product->fields['price_recurr_week']);
# Insert the record
$sql = "INSERT INTO {$p}service SET\n\t\t\t\t\tid \t\t\t\t\t= {$id},\n\t\t\t\t\tsite_id\t\t\t\t= {$s}, \n\t\t\t\t\tqueue\t\t\t\t= 'active',\n\t\t\t\t\tdate_orig\t\t\t= " . $db->qstr($date_orig) . ",\n\t\t\t\t\tdate_last\t\t\t= " . $db->qstr(time()) . ", \n\t\t\t\t\tinvoice_id\t\t\t= " . $db->qstr(@$invoice_id) . ", \n\t\t\t\t\taccount_id\t\t\t= " . $db->qstr(@$account_id) . ",\n\t\t\t\t\taccount_billing_id\t= " . $db->qstr(@$billing_id) . ",\n\t\t\t\t\tproduct_id\t\t\t= " . $db->qstr(@$product_id) . ",\n\t\t\t\t\tsku\t\t\t\t\t= " . $db->qstr($product->fields['sku']) . ", \n\t\t\t\t\ttype\t\t\t\t= " . $db->qstr('group') . ", \n\t\t\t\t\tactive\t\t\t\t= " . $db->qstr($active) . ", \n\t\t\t\t\tsuspend_billing\t\t= " . $db->qstr($suspend) . ", \n\t\t\t\t\tdate_last_invoice\t= " . $db->qstr($date_last) . ",\n\t\t\t\t\tdate_next_invoice\t= " . $db->qstr($date_next) . ", \n\t\t\t\t\tprice\t\t\t\t= " . $db->qstr($product->fields['price_base']) . ",\n\t\t\t\t\tprice_type\t\t\t= " . $db->qstr($product->fields['price_type']) . ",\n\t\t\t\t\ttaxable\t\t\t\t= " . $db->qstr($product->fields['taxable']) . ",\t\t\t\t\t\n\t\t\t\t\trecur_type\t\t\t= " . $db->qstr($product->fields['price_recurr_type']) . ",\n\t\t\t\t\trecur_schedule\t\t= " . $db->qstr($product->fields['price_recurr_schedule']) . ",\n\t\t\t\t\trecur_weekday\t\t= " . $db->qstr($product->fields['price_recurr_weekday']) . ",\n\t\t\t\t\trecur_week\t\t\t= " . $db->qstr($product->fields['price_recurr_week']) . ",\n\t\t\t\t\trecur_cancel\t\t= " . $db->qstr($product->fields['price_recurr_cancel']) . ",\n\t\t\t\t\tgroup_grant\t\t\t= " . $db->qstr($product->fields['assoc_grant_group']) . ",\n\t\t\t\t\tgroup_type\t\t\t= " . $db->qstr($product->fields['assoc_grant_group_type']);
$db->Execute($sql);
# Insert the import record
$this->import_transaction($this->plugin, $VAR['action'], 'service', $id, 'services', $rs->fields['domain_id'], &$db);
# Insert the invoice item:
$idx = $db->GenID($p . 'invoice_item_id');
$sql = "INSERT INTO {$p}invoice_item SET\n\t\t\t\t\tid \t\t\t\t\t= {$id},\n\t\t\t\t\tsite_id\t\t\t\t= {$s}, \n\t\t\t\t\tinvoice_id\t\t\t= " . $db->qstr(@$invoice_id) . ", \n\t\t\t\t\tproduct_id\t\t\t= " . $db->qstr(@$product_id) . ",\n\t\t\t\t\tdate_orig\t\t\t= " . $db->qstr($date_orig) . ", \n\t\t\t\t\tsku\t\t\t\t\t= " . $db->qstr($product->fields['sku']) . ",\n\t\t\t\t\tquantity\t\t\t= 1,\n\t\t\t\t\titem_type\t\t\t= 0,\n\t\t\t\t\tprice_type\t\t\t= " . $db->qstr($product->fields['price_recurr_type']) . ",\n\t\t\t\t\tprice_base\t\t\t= " . $db->qstr($product->fields['price_base']) . ",\n\t\t\t\t\tprice_setup\t\t\t= " . $db->qstr($product->fields['price_setup']) . ",\n\t\t\t\t\trecurring_schedule = " . $db->qstr($product->fields['price_recurr_schedule']);
$db->Execute($sql);
# Insert the import record
$this->import_transaction($this->plugin, $VAR['action'], 'invoice_item', $id, 'services', $rs->fields['domain_id'], &$db);
# Complete the transaction
$db->CompleteTrans();
$rs->MoveNext();
}
$C_debug->alert($msg);
$offset = $VAR['offset'] + $this->select_limit;
echo "<script language=javascript> \n\t\t\t setTimeout('document.location=\\'?_page=core:blank&offset={$offset}&action={$VAR['action']}&plugin={$VAR['plugin']}&do[]=import:do_action\\'', 1200);\n\t\t\t </script>";
}
