317 replies to this topic

[hobbzilla]

lisa,

This is not due to an error in the contribution.  The contrib did not affect any SMTP settings or change anything in that regard.  I would suggest performing this install in a test environment on a fresh MS2 install and see if you are still getting those errors.

[tlelliott77]

Quote

I'm having the same issue as Steel - i multiplies the number of others by the total nubmer of other responses posted, so with fifteen entries in other, a response of '1' would show '15', a response with 3 would show '45' etc.

I think I have a solution to this problem.  Can a those who are having this problem test the following changes?

In stats_referral_sources.php find:

    $referrals_query_raw = "select count(ci.customers_info_source_id) as no_referrals, so.sources_other_name as sources_name from " . TABLE_CUSTOMERS_INFO . " ci, " . TABLE_SOURCES_OTHER . " so where ci.customers_info_source_id = '9999' group by so.sources_other_name order by so.sources_other_name DESC";

And change it to:

    $referrals_query_raw = "select count(ci.customers_info_source_id) as no_referrals, so.sources_other_name as sources_name from " . TABLE_CUSTOMERS_INFO . " ci, " . TABLE_SOURCES_OTHER . " so where ci.customers_info_source_id = '9999' and so.customers_id = ci.customers_info_id group by so.sources_other_name order by so.sources_other_name DESC";

This should now correctly reflect the numbers of "Other" sources.

I have also modded this to work with PWA.  I anyone wants to find out how I'll try to formulate the changes into a coherent post.

Cheers
Tim

[Steel]

That did the trick! Thanks Tim

[Steel]

I've been checking out the Data Base to see if I could find out exactly who chose what sources and it is listed in there. now what I was trying ot do was make a place on the customer screen where it will list the source next to each persons name.

Kinda like this:
[img]http://img4.photobucket.com/albums/0603/steelshadow/Untitled-1.jpg[/img]

I looked in the customer.php  file and believe that its just a simple couple of lines to add to link the info from the data base but am having problems. I did find another contribution that does this somewhat, but combing the two was not successful and impractical as this one works much better.

All I need to do is match up the customer_ID with the  customers_info_source_id and display it on the customer pages. It will give you the info if you look in the customers_info Table.

Any Ideas?

[NextLevelMotoring]

Thanks Tim, that fix did the trick for me!

[nrlatsha]

Here's a simple hack to display the referral source with the customer's information. This will also make an entry into the database if anything is written into that box, maybe you want to add "Website" as a source, they can then put "someoffthewallwebsite.com" and you'll be ablt to see it.


catalog/create_account.php:

LOOK FOR:

//rmh referral start
      tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('" . (int)$customer_id . "', '0', now(), '". (int)$source . "')");

      if ($source == '9999') {
        tep_db_query("insert into " . TABLE_SOURCES_OTHER . " (customers_id, sources_other_name) values ('" . (int)$customer_id . "', '". $source_other . "')");
      }
//rmh referral end

REPLACE WITH:

//rmh referral start
      tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('" . (int)$customer_id . "', '0', now(), '". (int)$source . "')");

  //    if ($source == '9999') {
   if (isset($HTTP_POST_VARS['source_other'])) {
        tep_db_query("insert into " . TABLE_SOURCES_OTHER . " (customers_id, sources_other_name) values ('" . (int)$customer_id . "', '". $source_other . "')");
      }
//rmh referral end

admin/customers.php:

LOOK FOR:

</table></td>
      </tr>
      <tr>
        <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
      </tr>
      <tr>
        <td align="right" class="main"><?php echo tep_image_submit('button_update.gif', IMAGE_UPDATE) . ' <a href="' . tep_href_link(FILENAME_CUSTOMERS, tep_get_all_get_params(array('action'))) .'">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>'; ?></td>
      </tr></form>

ABOVE IT ADD:

<?php  
  
	$info_query = tep_db_query("select customers_info_source_id  from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . $customers['customers_id'] . "'");
      $info = tep_db_fetch_array($info_query);
   
if ($info['customers_info_source_id'] ==4) {

	$info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'");
      $info = tep_db_fetch_array($info_query);

?>  
      <td class="main">Referred by:</td>
            <td class="main"><? if (tep_not_null($info['sources_other_name'])) { echo $info['sources_other_name']; } else { echo 'Not Given'; }?> </td>
 	 <? } ?>

Of course this line:

if ($info['customers_info_source_id'] ==4) {

Refers to my Friend / CoWorker select box. And it will not taint your "other" category, it would just be some info the customer put down when signing up.

I think thats it, I didn't test it on a new install and it may not be perfect, but it works...

Steel - If I get bored, I'll see about adding your idea to my site. And if it gets added to my site, I'll post the info here...

Noel

[nrlatsha]

Steel - I got bored and didn't feel like working on stuff I should be doing.

admin/customers.php:

LOOK FOR:

} else {
        echo '          <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_CUSTOMERS, tep_get_all_get_params(array('cID')) . 'cID=' . $customers['customers_id']) . '\'">' . "\n";
      }

ADD BELOW:

$info_query = tep_db_query("select customers_info_source_id  from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . $customers['customers_id'] . "'");
  $info1 = tep_db_fetch_array($info_query);
  
if ($info1['customers_info_source_id'] == 0) {
	$entry_referral = 'XXXXX';
	} elseif (($info1['customers_info_source_id'] == 1) || ($info1['customers_info_source_id'] == 9999)) {
  $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'");
     	 $info1 = tep_db_fetch_array($info_query);
 	 if ($info1['sources_other_name'] != '') {
    $entry_referral = $info1['sources_other_name'];
    } else {
    $entry_referral = 'XXXXXXX';
    }
	} else {
  $info_query = tep_db_query("select sources_name from " . TABLE_SOURCES . " where sources_id = '" . $info1['customers_source_id'] . "'");
     	 $info1 = tep_db_fetch_array($info_query);
  $entry_referral = $info1['sources_name'];
	}

Again, this line:

(($info1['customers_info_source_id'] == 1) || ($info1['customers_info_source_id'] == 9999))

Can be changed to match your dbase's source id's.

FIND:

<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACCOUNT_CREATED; ?></td>

REPLACE WITH:

<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACCOUNT_CREATED; ?></td>
    <td class="dataTableHeadingContent" align="right"><?php echo 'Referred By'; ?></td>

FIND:

<td class="dataTableContent" align="right"><?php echo tep_date_short($info['date_account_created']); ?></td>

REPLACE WITH:

<td class="dataTableContent" align="right"><?php echo tep_date_short($info['date_account_created']); ?></td>
    <td class="dataTableContent" align="right"><?php echo $entry_referral; ?></td>

That should be it...

Noel

[Vyyk Drago]

i'm very excited to get this mod working, thanks for creating this, hobbzilla!

it seems i'm the only one having these problems, so please excuse me if this is just due to some dumb thing i did...

1) in the admin section none of my links are correct anymore. the file names in the URL are replaced with the variables. for instance, clicking the Configuration link produces the URL: catalog/admin/FILENAME_CONFIGURATION?gID=1&selected_box=configuration

2) going to the referrals page (catalog/admin/referrals.php) gives me this error:

1146 - Table 'catalog.TABLE_SOURCES' doesn't exist

select count(*) as total from TABLE_SOURCES

[TEP STOP]

but i verified that the table "sources" does exist. i was in phpMyAdmin and editing the referral sources manually since the links in the admin section aren't working. (or is the table supposed to be table_sources? my sql knowledge is very limited.)

any ideas on either issue?

[hobbzilla]

did something happen to your filenames.php & database_tables.php ???

[Vyyk Drago]

thank you for pointing me in the right direction. i'm really not sure what the problem was, but it must have been some small typo. i just went into those files and tried to straighten up the code and when i uploaded the new version, that fixed it.

great contribution, thanks again!

[Steel]

nrlatsha
That's exactly what I was trying to do. Thanks it works great!
Now what I am trying to do is find a way to have the source be listed on all customers with out having to manually enter in the ID and desired definition for each one
like this below:

}  elseif (($info1['customers_info_source_id'] == 1) || ($info1['customers_info_source_id'] == 9999)) {
 $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'");
      $info1 = tep_db_fetch_array($info_query);
  if ($info1['sources_other_name'] != '') {
   $entry_referral = $info1['sources_other_name'];
   } else {
   $entry_referral = 'Google';
   }

} elseif (($info1['customers_info_source_id'] == 4) || ($info1['customers_info_source_id'] == 9999)) {
 $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'");
      $info1 = tep_db_fetch_array($info_query);
  if ($info1['sources_other_name'] != '') {
   $entry_referral = $info1['sources_other_name'];
   } else {
   $entry_referral = 'MSN';
   }

} elseif (($info1['customers_info_source_id'] == 5) || ($info1['customers_info_source_id'] == 9999)) {
 $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'");
      $info1 = tep_db_fetch_array($info_query);
  if ($info1['sources_other_name'] != '') {
   $entry_referral = $info1['sources_other_name'];
   } else {
   $entry_referral = 'Yahoo';
   }

Im trying to figure out a way to get  something like the following to work
(this does not actually work but its an example of what Im trying to do) :

if ($info1['customers_info_source_id'] == 0) {
$entry_referral = 'N/A';
} elseif (($info1['customers_info_source_id'] == 1-9998) || ($info1['customers_info_source_id'] == 9999)) {
 $info_query = tep_db_query("select sources_other_name from " . TABLE_SOURCES_OTHER . " where customers_id = '" . $customers['customers_id'] . "'");
      $info1 = tep_db_fetch_array($info_query);
  if ($info1['sources_other_name'] != '') {
   $entry_referral = $info1['sources_other_name'];
   } else {
   $entry_referral = '$info1['sources_name']';
   }
} else {
 $info_query = tep_db_query("select sources_name from " . TABLE_SOURCES . " where sources_id = '" . $info1['customers_source_id'] . "'");
      $info1 = tep_db_fetch_array($info_query);
 $entry_referral = $info1['sources_name'];
}

Im pretty good at HTML but am still very new to PHP and its definitely a learning experience

Thanks Again
Steel

[Steel]

nrlatsha

Ive found a bug in the code ...the code changed in the create account page  opens up the "other" box to allow anything  to be put in it and does not limit it to just 9999 and 4. this woundnt be that big a deal except that if someone were to put anything in the box now, even if they select google, yahoo or whatever, the text they put in the "other" box is now what will show up on the customers screen instead of the selected source.

EXAMPLE
customer selects "GOOGLE" as the source but also types in "Hi how are you"
Instead of 'GOOGLE" showing up on the customer  page it will show as this:

Last Name-----First Name------Account Created-------Referred By-------Action
DOE--------------JOHN--------------02/02/2002----------Hi how are you

I tried changing it to this.....

//rmh referral start
      tep_db_query("insert into " . TABLE_CUSTOMERS_INFO . " (customers_info_id, customers_info_number_of_logons, customers_info_date_account_created, customers_info_source_id) values ('" . (int)$customer_id . "', '0', now(), '". (int)$source . "')");

      if ($source == '9999') {
        tep_db_query("insert into " . TABLE_SOURCES_OTHER . " (customers_id, sources_other_name) values ('" . (int)$customer_id . "', '". $source_other . "')");
      }

      if ($source == '4') {
        tep_db_query("insert into " . TABLE_SOURCES_OTHER . " (customers_id, sources_other_name) values ('" . (int)$customer_id . "', '". $source_other . "')");
      }

//rmh referral end

to see if it would fix the problem, but by doing this no source will show up and it will read not given on the customer edit screen

[nrlatsha]

Steel - I have to work this weekend, and 12hr shifts don't really give me any time to code, but I can take a look next week...

Perhaps I can append it to something like Google: Hi, How are you.... So if they type something useful in there, its there... Just a thought, I'll have others I'm sure...

Edited by nrlatsha, 17 July 2004 - 12:22 PM.

[sc00zy]

Hi,

I just installed this great contrib for other purposes it's made for. In my shop customers  can donate their 20% discount to organisations like the local soccerclub and so on.
This contrib comes very close to what i'm looking for ... but I like the referral to show on the order, e-mail, invoice, packaginslip, etc... is this hard to do? I really don't have a clue to do this....

Thanks in advance!

[hobbzilla]

Are you needing a referral each time a customer places an order?

This can be done. However, instead of updating the customers info table.. you would have to update the order table and instead of making changes to the create account code, you would have to change checkout (shipping/billing) pages along with the order class.

[sc00zy]

Yes... I need an referral each time a customer places an order. Or maybe a edit function would be needfull so they can edit their choice of referral later on...

The problem is that I'm not really a big star in coding  

[popcorn]

How do you find out what your databases source id is, as mentioned in noels upgrade.

[p51mustang]

Hi

I've a brand new store with no real customers yet.

I've installed the contribution (including the count fix and adding source to customer info). All seems fine from the Admin side, but then:

The problem occurs when when a new customer creates a new account; the following error message is displayed after entering the customers details and pressing 'Continue':

Quote

1062 - Duplicate entry '4' for key 1

insert into customers_info (customers_info_id, customers_info_number_of_logons,

customers_info_date_account_created) values ('4', '0', now())

[TEP STOP]

I think entry'4' is the new customer number.

Under Admin - Customers, the following error message is displayed:

Quote

Customers  Search: 

Last Name First Name Account Created Referred By
1146 - Table 'saairfor_osc1.TABLE_SOURCES_OTHER' doesn't exist

select sources_other_name from TABLE_SOURCES_OTHER where customers_id = '3'

[TEP STOP]

I've uploaded the file 'update.sql' via phpMyAdmin and it created two new tables: sources and sources_other.

Anyone know how to fix this?

Thanks.

Edited by p51mustang, 02 August 2004 - 04:15 PM.

[hobbzilla]

Quote

Quote

1062 - Duplicate entry '4' for key 1

insert into customers_info (customers_info_id, customers_info_number_of_logons,

customers_info_date_account_created) values ('4', '0', now())

[TEP STOP]

I think entry'4' is the new customer number.

Sounds like you deleted a customer but not through the admin.  If you did this.. you must also delete the cooresponding customer_info entry.

Quote

Under Admin - Customers, the following error message is displayed:

Quote

Customers  Search: 

Last Name First Name Account Created Referred By
1146 - Table 'saairfor_osc1.TABLE_SOURCES_OTHER' doesn't exist

select sources_other_name from TABLE_SOURCES_OTHER where customers_id = '3'

[TEP STOP]

I've uploaded the file 'update.sql' via phpMyAdmin and it created two new tables: sources and sources_other.

Update your database_tables.php lately?

[p51mustang]

Hi

Quote

Sounds like you deleted a customer but not through the admin. If you did this.. you must also delete the cooresponding customer_info entry.

Yes, I did delete a customer, but it was via Admin - Customers    

Quote

Update your database_tables.php lately?

Ooops, left one line out!

However, the problem still remains. All the test customers I entered are still in there somewhere, I just canot access them via Admin.

How do I reset the database (via phpMyAdmin?) to delete the customers?

Thanks.