<?php

/**
 * Upload to your WHMCS	 root directory.
 */
require_once 'dbconnect.php';

$fp = tmpfile();
fwrite($fp, "***,users\n");
fwrite($fp, "id,firstname,lastname,address,email,city,state,zip,phone,country,company,status,password\n");
mysql_connect($locale_db_host, $locale_db_login, $locale_db_pass);
mysql_select_db($locale_db_name);

$query = "SELECT * FROM tblclients";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $data = array(
                        $row['id'],
                       '"'.$row['firstname'].'"',
                        '"'.$row['lastname'].'"',
                        '"'.$row['address1']." ".$row['address2'].'"',
                        '"'.$row['email'].'"',
                        '"'.$row['city'].'"',
                        '"'.$row['state'].'"',
                        '"'.$row['postcode'].'"',
                        '"'.$row['phonenumber'].'"',
                        '"'.$row['country'].'"',
                        '"'.$row['companyname'].'"',
                        '"'.status2ce($row['status']).'"',
                        '"'.$row['password'].'"'
    );
    fwrite($fp, str_replace("\n", "", implode(",", $data))."\n");
}
fwrite($fp, "\n");

/* Packages */
fwrite($fp, "***,packages\n");
fwrite($fp, "id,name,monthly,quarterly,semiannually,annually,biennial,description,packagetype,tax\n");

//$query = "SELECT id, name, monthly, quarterly, semiannual, annual, biennial, description, type,tax FROM tblproducts";
// New Query for whmcs version 4
$query = "SELECT pro.id, pro.name, pri.monthly, pri.quarterly, pri.semiannually, pri.annually, pri.biennially, pro.description, pro.type, pro.tax FROM tblproducts pro, tblpricing pri WHERE pri.currency =1 AND pro.id = pri.relid AND pri.type = 'product'";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $data = array(
                        $row['id'],
                        '"'.$row['name'].'"',
                        $row['monthly'],
                        $row['quarterly'],
                        $row['semiannually'],
                        $row['annually'],
                        $row['biennially'],
                       '"'.str_replace('"', '\"', $row['description']).'"',
                        $row['type'],
                        $row['tax']
    );
    fwrite($fp, str_replace(array("\r\n", "\n"), "", implode(',', $data))."\n");
}
fwrite($fp, "\n");

/* Package Addons */
fwrite($fp, "***,packageaddons\n");
fwrite($fp, "packageid,name,description,setupfee\n");

//$query = "SELECT packages, name,description,setupfee FROM tbladdons";
// New Query for whmcs version 4
$query = "SELECT a.packages, a.name, a.description, pri.msetupfee FROM tbladdons a, tblpricing pri WHERE pri.type = 'addon' AND pri.currency =1 AND a.id = pri.relid";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
	$packageidArr = explode(',',rtrim($row['packages'],','));
	foreach($packageidArr as $packageid)
	{
		$data = array(
							$packageid,
							'"'.$row['name'].'"',
							'"'.str_replace('"', '\"', $row['description']).'"',
							$row['msetupfee']

		);
		fwrite($fp, str_replace("\n", "", implode(',', $data))."\n");
	}
}
fwrite($fp, "\n");

/* Client Packages */

/* Client Package Addons */

/* Client Invoices */
fwrite($fp, "***,invoices\n");
fwrite($fp, "id,clientid,amount,datedue,datepaid,description,detail,tax,subtotal,status\n");

$query = "SELECT id, userid, total, duedate, datepaid, notes, tax, subtotal, status FROM tblinvoices";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $data = array(
                    $row['id'],
                    $row['userid'],
                    $row['total'],
                    date("Y-m-d", strtotime($row['duedate'])),
                    ($row['datepaid'] != 0)? date("Y-m-d", strtotime($row['datepaid'])) : 0,
		            '"Imported Invoice"',
		            '"'.str_replace('"', '\"', $row['notes']).'"',
		            $row['tax'],
					$row['subtotal'],
					invoiceStatus2ce($row['status']),
    );
    fwrite($fp, str_replace("\n", "", implode(',', $data))."\n");
}
fwrite($fp, "\n");


/* Client Invoiceentries */
fwrite($fp, "***,invoiceentry\n");
fwrite($fp, "id,clientid,amount,tax,datedue,detail,description\n");

$query = "SELECT id, userid, amount, taxed, duedate, notes, description FROM tblinvoiceitems";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $data = array(
                    $row['id'],
                    $row['userid'],
                    $row['amount'],
                    $row['taxed'],
                    date("Y-m-d", strtotime($row['duedate'])),
                    $row['notes'],
		            $row['description']
    );
    fwrite($fp, str_replace("\n", "", implode(',', $data))."\n");
}
fwrite($fp, "\n");

fwrite($fp, "***,domains\n");
fwrite($fp, "clientid,activateddate,domainname,recurring,registrationperiod,status,nextduedate\n");
$query = "SELECT userid, registrationdate, domain, recurringamount, registrationperiod, status, nextinvoicedate FROM tbldomains";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $data = array(
                    $row['userid'],
                    date("Y-m-d", strtotime($row['registrationdate'])),
                    $row['domain'],
                    $row['recurringamount'],
                    $row['registrationperiod'],
		            domainStatus2ce($row['status']),
				    date("Y-m-d", strtotime($row['nextinvoicedate']))

    );
    fwrite($fp, str_replace("\n", "", implode(',', $data))."\n");
}
fwrite($fp, "\n");


fwrite($fp, "***,hosting\n");
fwrite($fp, "clientid,username,password,server,plan,regdate,domain,status,nextinvoicedate\n");

$query = "SELECT id,userid, username,password,server,packageid,regdate,domain, domainstatus, nextinvoicedate FROM tblhosting";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $data = array(
                    $row['userid'],
                    $row['username'],
                    $row['password'],
				    $row['server'],
					$row['packageid'],
				    $row['regdate'],
                    $row['domain'],
		            $row['domainstatus'],
				    $row['nextinvoicedate']
    );
    fwrite($fp, str_replace("\n", "", implode(',', $data))."\n");
}
fwrite($fp, "\n");


fwrite($fp, "***,hostingaddon\n");
fwrite($fp, "clientid,recurring,nextinvoicedate,name\n");

$query = "SELECT tblhosting.id, tblhosting.userid, tblhostingaddons.recurring,tblhostingaddons.nextinvoicedate,tblhostingaddons.name FROM tblhosting, tblhostingaddons where tblhosting.id = tblhostingaddons.hostingid";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $data = array(
                    $row['userid'],
                    $row['recurring'],
                    $row['nextinvoicedate'],
				    trim($row['name'])
    );
    fwrite($fp, str_replace("\n", "", implode(',', $data))."\n");
}
fwrite($fp, "\n");


fseek($fp, 0);
$fstat = fstat($fp);

@set_time_limit(0);
ob_end_clean();
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"whmcs.csv\"");
header("Content-Length: ".$fstat['size']);

// work-around IE bug when using SSL
header("Pragma: public");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

header("Expires: 0");
while (!feof($fp)) { 
    echo fread($fp, 4096); 
}
fclose($fp);
exit;

function status2ce($status) {
    switch($status) {
        case 'Inactive': // inactive
            return -1;
        case 'Active': // active
            return 1;
        case 'Closed': // cancelled
            return -2;
        default: // just in case default to active
            return 1;
    }
}

function invoiceStatus2ce($status) {
    switch($status) {
        case 'Paid':
            return 1;
        case 'Cancelled':
            return 2;
        case 'Unpaid':
        default:
            return 0;
            
    }
}

function domainStatus2ce($status) {
    switch($status) {
        case 'Pending':
        case 'Pending Transfer':
            return 0;
        case 'Active':
        default:
            return 1;
        case 'Cancelled':
        case 'Expired':
        case 'Fraud':
            return 3;
            
    }
}


?>
