How Did You Hear About Us - Support

[Husk]

Actually, got it all working, then some hacker managed to get into the database and wipe all the data out. I restored the pages, but there was no data backup - except the table structure was all there including the tables for heard about us.

 

Now the Heard About Us item on the sign-up page won't show but the code is there (haven't changed any code from it previously working), and there is no-where in the admin to view or add heard about us items like there was before.

 

Any help - if the tables still exist, and the pages have been updated according to instructions (and were working before being hacked) - what have I missed,

 

Help!!!

[intensivex]

Hi All ... I started another forum thread, not knowing this one existed. Atleast now my problem is a bit more refined and loads of you are familiar with the contrib (opposed to the gentlemen that where helping me with basic SQL syntax etc).

 

I'll start by explaining.

 

I wanted to improve on the default contribution by adding a date range feature which should spit out rows based on any date range you select. The latest contributions, although work in the same kind of way, are still a little rough around the edges. In the past I've had someone help me add a date range function to another report, so I thought I'd emulate it on this referal report.

 

The guys helped me construct a working query which does exactly what Im looking to do, but I believe the syntax of the actual usage of the query is wrong. Perhaps some of you can chime in and tell me where I'm wrong and propose a solution:

 

Here is my working query:

 

SELECT ci.customers_info_date_account_created AS date_created, COUNT( ci.customers_info_source_id ) AS no_referrals, s.sources_name, s.sources_id
FROM customers_info ci
LEFT JOIN sources s ON s.sources_id = ci.customers_info_source_id
WHERE DATE( ci.customers_info_date_account_created ) >= '2010-01-01'
AND DATE( ci.customers_info_date_account_created ) >= DATE_ADD( '2010-01-26', INTERVAL 1
DAY )
GROUP BY s.sources_id
ORDER BY no_referrals DESC

 

Now, keep something in mind, I have a $date_from and $date_to var which I'm passing (on my other report). When a user selects a date range from the report, the query executes based on the range the user selected.

 

The above query has dates in-lined to eliminate other possible causes of the thing not working. Once i get the query and the output correct, I'll re-add the GET vars for $date_from and $date_to.

 

Here is the query with my vars (doesn't seem to work):

 

$referrals_query_raw = "SELECT ci.customers_info_date_account_created AS date_created, COUNT( ci.customers_info_source_id ) AS no_referrals, s.sources_name, s.sources_id
FROM customers_info ci
LEFT JOIN sources s ON s.sources_id = ci.customers_info_source_id
WHERE DATE( ci.customers_info_date_account_created ) >= $date_from
AND DATE( ci.customers_info_date_account_created ) >= DATE_ADD( $date_to, INTERVAL 1
DAY )
GROUP BY s.sources_id
ORDER BY no_referrals DESC"

 

With so many moving peices I'm not sure my version of the query with the included vars is correct.

 

Here are the definitions for the $_GET vars:

 

<?php
$date_from=isset($_GET['date_from'])?$_GET['date_from']:date('m-d-Y');
$date_to=isset($_GET['date_to'])?$_GET['date_to']:date('m-d-Y');
?>

 

Here is my current usage:

 

<table border="0" width="100%" cellspacing="0" cellpadding="0">

<?php
 if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;
 $rows = 0;
 $presplit_query = tep_db_query($referrals_query_raw);
 $presplit_query_numrows = tep_db_num_rows($presplit_query);
 $referrals_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $referrals_query_raw, $referrals_query_numrows);
 $referrals_query_numrows = $presplit_query_numrows;
 $referrals_query = tep_db_query($referrals_query_raw);
 while ($referrals = tep_db_fetch_array($referrals_query)) {
   $rows++;

   if (strlen($rows) < 2) {
     $rows = '0' . $rows;
   }
   if ( tep_not_null($referrals['sources_name']) ) {
?>
             <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)">
<?php
   } else {
?>
             <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href='<?php echo tep_href_link(FILENAME_STATS_REFERRAL_SOURCES, 'action=display_other'); ?>'">
<?php
   }
?>
               <td class="dataTableContent"><?php echo $rows; ?>.</td>
               <td class="dataTableContent"><?php echo (tep_not_null($referrals['sources_name']) ? $referrals['sources_name'] : '<b style="cursor:pointer">' . TEXT_OTHER . '</b>');?> </td>
               <td class="dataTableContent" align="center"><?php echo $referrals['no_referrals']; ?> </td>
             </tr>
<?php
 }
?>
           </table>

 

Here is the form which ranges the referral report:

 

<?php echo tep_draw_form('date_range', 'stats_referral_sources.php', '', 'get'); ?>
<table><tr><td>
<input type="text" name="date_from" onClick="self.popUpCalendar(this,this,'mm/dd/yyyy',document);" value="<?=$date_from?>" textfield>
</td>
<td>
<input type="text" name="date_to" onClick="self.popUpCalendar(document.date_range.date_from,this,'mm/dd/yyyy',document);" value="<?=$date_to?>" textfield></td>
<td><input type="submit" value="search">
</td>
</tr>
</table>
</form>

 

Since im not including this calendar date selector (which simply populates the inputs with the selected dates) just inline your dates in the form if your going to test it.

 

I believe my problem is with my usage, although my query syntax with my $_GET vars could be wrong also.

 

Can someone please chime in and assist me getting this working. Once it works, I'll release it into the contribs area for everyone to enjoy.

 

Thanks in advance!

[gaaalmp]

I wonder if anyone tried to set display gender to false in OSc admin panel and then created an account with this contrib installed?

[gaaalmp]

I wonder if anyone tried to set display gender to false in OSc admin panel and then created an account with this contrib installed?

Now I wonder if setting the two values in sql database to null is the best way around it:

address_book -> entry_gender

customers -> customers_gender

[♥sandwick]

First let me say this is a great contribution.

 

I am currently running 1.5.1 and everything has been running great !

 

I would like to know how to change the code listed below to make it manditory to fill out the referral or other ...

 

<!-- //from create_account.php -->
<!-- //rmh referral start -->
<?php
 if ((tep_not_null(tep_get_sources()) || DISPLAY_REFERRAL_OTHER == 'true') && (!tep_session_is_registered('referral_id') || (tep_session_is_registered('referral_id') && DISPLAY_REFERRAL_SOURCE == 'true')) ) {
?>
     <tr>
       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
     </tr>
     <tr>
       <td class="main"><b><?php echo CATEGORY_SOURCE; ?></b></td>
     </tr>
     <tr>
       <td><table border="0" width="100%" cellspacing="1" cellpadding="2" class="infoBox">
         <tr class="infoBoxContents">
           <td><table border="0" cellspacing="2" cellpadding="2">
             <tr>
               <td class="main"><?php echo ENTRY_SOURCE; ?></td>
               <td class="main"><?php echo tep_get_source_list('source', (DISPLAY_REFERRAL_OTHER == 'true' || (tep_session_is_registered('referral_id') && tep_not_null($referral_id)) ? true : false), (tep_session_is_registered('referral_id') && tep_not_null($referral_id)) ? '9999' : '') . ' ' . (tep_not_null(ENTRY_SOURCE_TEXT) ? '<span class="inputRequirement">' . ENTRY_SOURCE_TEXT . '</span>': ''); ?></td>
             </tr>
<?php
   if (DISPLAY_REFERRAL_OTHER == 'true' || (tep_session_is_registered('referral_id') && tep_not_null($referral_id))) {
?>
             <tr>
               <td class="main"><?php echo ENTRY_SOURCE_OTHER; ?></td>
               <td class="main"><?php echo tep_draw_input_field('source_other', (tep_not_null($referral_id) ? $referral_id : '')) . ' ' . (tep_not_null(ENTRY_SOURCE_OTHER_TEXT) ? '<span class="inputRequirement">' . ENTRY_SOURCE_OTHER_TEXT . '</span>': ''); ?></td>
             </tr>
<?php
   }
?>
           </table></td>
         </tr>
       </table></td>
     </tr>
<?php
 } else if (DISPLAY_REFERRAL_SOURCE == 'false') {
     echo tep_draw_hidden_field('source', ((tep_session_is_registered('referral_id') && tep_not_null($referral_id)) ? '9999' : '')) . tep_draw_hidden_field('source_other', (tep_not_null($referral_id) ? $referral_id : ''));
 }
?>
<!-- //rmh referral end -->

 

What i am finding out is that if they do not give me any data in the REFERRAL Source section of the create account screen the user information DOES NOT get inserted into the data !

 

any help would be appreciated !

 

Thanks

 

 

Server OS: Linux 2.6.28.8-20100125a-iscsi-ntacker-fasync-mremap-grsec

Database: MySQL 5.0.81-log

HTTP Server: Apache

PHP Version: 4.4.9 (Zend: 1.3.0)

osCommerce Online Merchant v2.2 RC2a

[vixen800]

Hi all,

 

NEWBIE ALERT!! I'm playing around with this contribution, i see there have been a number of requests to have the referral added to the invoice, i have managed to get it into the invoice but it shows --none-- for all my tester invoices even though i've created some of the accounts with a referral.

I read through the thread, but as I said i'm a newbie, not sure what i'm doing, and i was hoping someone could help we finalize the query, it appears (HUGE ASSUMPTION) that the query is only "seeing" the first line of my database in customer_info which correctly does not have a referral.

 

<?php         
	$cust_id_query = tep_db_query("select customers_id from " . TABLE_ORDERS . " where orders_id = '" . $orders['orders_id']. "'");
	$cust_result = tep_db_fetch_array($cust_id_query);
	$cID = $cust_result['customers_id'];
               $source_query = tep_db_query("select customers_info_source_id from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . (int)$cID . "'");
               $source = tep_db_fetch_array($source_query);$entry_referral = tep_get_sources_name($source['customers_info_source_id'], $cust_result['customers_id']);
               echo $entry_referral;{

 

 

Please, please could someone point me in the right direction - i've used some of the code given by Eww in this thread AND i've used some code from another thread to try get this working!

[Cheepnis]

Similar request as vixen800: I would like this to show up on the orders list and was able to insert all the fields properly in orders.php by inserting the appropriate lines from customers.php. It shows up just fine in the admin orders listing. However, none of the data is reported, that is to say, all the results show " --none-- " in the "Referred By" column. I'm sure it has to do with the search string variables $customers['customers_id'] that are requested here not being created as they are in customers.php, but I don't know how to modify this to collect the data:

 

//rmh referral start
     $source_query = tep_db_query("select customers_info_source_id  from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . $customers['customers_id'] . "'");
     $source = tep_db_fetch_array($source_query);

     $entry_referral = tep_get_sources_name($source['customers_info_source_id'], $customers['customers_id']);
//rmh referral end

[Cheepnis]

 

 

<?php         
	$cust_id_query = tep_db_query("select customers_id from " . TABLE_ORDERS . " where orders_id = '" . $orders['orders_id']. "'");
	$cust_result = tep_db_fetch_array($cust_id_query);
	$cID = $cust_result['customers_id'];
               $source_query = tep_db_query("select customers_info_source_id from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . (int)$cID . "'");
               $source = tep_db_fetch_array($source_query);$entry_referral = tep_get_sources_name($source['customers_info_source_id'], $cust_result['customers_id']);
               echo $entry_referral;{

 

 

Please, please could someone point me in the right direction - i've used some of the code given by Eww in this thread AND i've used some code from another thread to try get this working!

You need a carriage return in this line:

source = tep_db_fetch_array($source_query);$entry_referral = tep_get_sources_name($source['customers_info_source_id'], $cust_result['customers_id']);

Between query); and $entry

[Cheepnis]

Similar request as vixen800: I would like this to show up on the orders list and was able to insert all the fields properly in orders.php by inserting the appropriate lines from customers.php. It shows up just fine in the admin orders listing. However, none of the data is reported, that is to say, all the results show " --none-- " in the "Referred By" column. I'm sure it has to do with the search string variables $customers['customers_id'] that are requested here not being created as they are in customers.php, but I don't know how to modify this to collect the data:

 

//rmh referral start
     $source_query = tep_db_query("select customers_info_source_id  from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . $customers['customers_id'] . "'");
     $source = tep_db_fetch_array($source_query);

     $entry_referral = tep_get_sources_name($source['customers_info_source_id'], $customers['customers_id']);
//rmh referral end

 

Solved it and added it as an optional addition to the mod in the contributions database. Works great!

[yansfung]

I want to modify this great add-on a little bit in the "product_info.php" or "checkout_confirmation.php" to have a referral statistics about products instead of target for buyers or guest. However, I am not familiar with drop down option values captured and pass through tep_db_* functions to update the data field "customers_info_source_id" of table "customers_info" with the following SQL statement.

 

tep_db_query("update " . TABLE_CUSTOMERS_INFO . " set customers_info_source_id = '" . (int)$source_value . "' where customers_info_id = '" . (int)$customer_id . "'");

 

The following is the "drop down menu" of referral options:

 

<td align="right" width="100%">

<?php echo "<form action= "What_Action_I_must_take" method=GET>

<select onchange=\"this.form.submit();\" names='This test'>";

for ($i = 0; $i < $source_rows; $i++) {

echo "<option value= $source[$i]['sources_id']>";

echo $source[$i]['sources_name'];

echo "</option>";

}

echo "</select>

</form>";

?>

 

Call someone kindly show me how to capture the mouse-clicked option value and pass to the parameter "$source_value" for such update.

 

Thanks a lot in advance!

 

Edited December 13, 2011 by yansfung

[apolyshow]

Does anyone install it o 2.3.1 osc ? Because the readme file has errors....

Any help please?

[Mort-lemur]

I want to modify this great add-on a little bit in the "product_info.php" or "checkout_confirmation.php" to have a referral statistics about products instead of target for buyers or guest. However, I am not familiar with drop down option values captured and pass through tep_db_* functions to update the data field "customers_info_source_id" of table "customers_info" with the following SQL statement.

 

tep_db_query("update " . TABLE_CUSTOMERS_INFO . " set customers_info_source_id = '" . (int)$source_value . "' where customers_info_id = '" . (int)$customer_id . "'");

 

The following is the "drop down menu" of referral options:

 

<td align="right" width="100%">

<?php echo "<form action= "What_Action_I_must_take" method=GET>

<select onchange=\"this.form.submit();\" names='This test'>";

for ($i = 0; $i < $source_rows; $i++) {

echo "<option value= $source[$i]['sources_id']>";

echo $source[$i]['sources_name'];

echo "</option>";

}

echo "</select>

</form>";

?>

 

Call someone kindly show me how to capture the mouse-clicked option value and pass to the parameter "$source_value" for such update.

 

Thanks a lot in advance!

 

 

What errors?

 

Thanks

[Mort-lemur]

Does anyone install it o 2.3.1 osc ? Because the readme file has errors....

Any help please?

 

sorry quoted the wrong post - what errors are you seeing ?

 

Thanks

[sakkiotto]

I there,

 

I am having problems with the SQL file also!

 

I have the drop down box but when I try to create a new account by clicking ok I get:

 

"1054 - Unknown column 'customers_info_source_id' in 'field list'

 

insert into customers_info (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('60', '0', now(), '1')

 

[TEP STOP]"

 

I dont think the tables have been added properly, also using phpadmin.

 

Cheers for any information, really good contribution!

 

Bren

 

i have similary error...can you help me ?

 

1054 - Unknown column 'ci.customers_info_source_id' in 'field list'

 

select count(ci.customers_info_source_id) as no_referrals, s.sources_name, s.sources_id from oscpro_customers_info ci LEFT JOIN sources s ON s.sources_id = ci.customers_info_source_id group by s.sources_id order by ci.customers_info_source_id DESC

 

Edited February 24, 2012 by sakkiotto

[DK-Toys.dk]

I have install the contrib and get it to work fully (almost), the only thing is that if customer don't create an account, it don't show the refferel in the report :-S

Do some one know how to fix that?

 

And is there a way to delete the statestic in the report from admin?

 

I have install the latest version on osc 2.2RC2a

Edited April 27, 2012 by DK-Toys.dk

[ce7]

problem solved, thanks!

Edited May 20, 2012 by ce7

[ce7]

Can anyone please help?

 

I installed in localhost before, fixed the errors and it works fine. Now Im trying to install again on my server version,

1st I did not see any drop down menu on the front page create accout page

2nd on my admin site, the customers / customer has the error message as below:

 

(ps. the only other addon which also modify customers.php I put is admin note...)

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select c.customers_id, c.customers_lastname, c.customers_firstname' at line 1

 

select count(*) as total select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_country_id, c.customers_notes from customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id order by c.customers_lastname, c.customers_firstname

[Praful Kamble]

@@ce7

 

You have used select twice in your query.

 

Just delete select which appears double and put comma(,).

 

So your new query would be like

 

select count(c.*) as total, c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_country_id, c.customers_notes from customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id order by c.customers_lastname, c.customers_firstname

 

Praful

Edited June 14, 2012 by Praful Kamble

[AlexW12]

I've been using this contribution for years and it works great. Thank you for the great mod. Unfortunately, I'm so rusty with coding now that I can't seem to figure out how to make it do what I need. I'm running 2.3.

I would like to modify the referral code a little to add the customer's referral source information to the admin/customers.php detail page. (the admin page where you see editable customer name, address, ph#, etc.).  I need to display it, but also make the data editable, like the customer's phone and fax number, etc. 

Can someone suggest the mods needed to admin/customers.php to add this functionality.
I'm lost.