Orphan leads

[nkasoff]

If you do a Load New Leads, but never Add A New List with the appropriate number, then there would be orphaned list records. Does Vicidial clear these out eventually, or is there some way to do this? The people who run our dialer are sure to mess this up occasionally, and I don't want our database full of orphan records that are never used and hang out there forever.

[mflorell]

You could clear them out manually unless you add a list_id for them and then delete them. The system doesn't do any automatic cleaning of leads.

You can always see them in MySQL by running this query:

SELECT count(*), list_id from vicidial_list group by list_id;

[nkasoff]

I am going to write a screen to delete orphans. This is sure to be a recurring problem with our users ... if they import a list, and make a mistake on the list id, they will just import the leads again and leave orphans. Please confirm that the following queries are correct:

(1) A count of orphan leads by list_id:

select vicidial_list.list_id,count(lead_id) as lead_count from vicidial_list left join vicidial_lists on vicidial_list.list_id=vicidial_lists.list_id where vicidial_lists.list_id is null group by vicidial_list.list_id order by vicidial_list.list_id;

(2) Removing the leads:

delete vicidial_list.* from vicidial_list left join vicidial_lists on vicidial_list.list_id=vicidial_lists.list_id where vicidial_lists.list_id is null;

[williamconley]

Yes, those queries worked on my system quite nicely.

Additional suggestion:

Allow users to choose "orphan lists" to delete (in case you want to just create a list for some of them but delete others). So you can put in an orphan lists button which shows them all as new links allowing each to be a "delete orphan list XXX" button.
This can be accomplished without the need of running a direct sql query because this URL will accomplish the task using Vici's delete:
/vicidial/admin.php?ADD=511&list_id=00 (replace 00 with your list id)
-This shows a page with a confirmation link, but you could replace this with the confirmation link if you don't mind the missing confirmation.

Of course, you could also allow a sql query to run for that function. You could also create a link to move the leads to another list. You could also automatically gather all orphaned leads into a special orphaned leads list for possible export or further inspection (hate to lose leads, if the manager loaded them incorrectly but did not notice ...)

[nkasoff]

Ok, I finally got around to doing something with this. The below code works, and is reasonably compliant with Vicidial standards. The require statement is slightly non-standard because I put this in /var/www/html rather than in the Vicidial folder, since it isn't actually part of Vicidial.

Code: Select all

<?php
require('vicidial/dbconnect.php');

$ct=0;

if (isset($_POST['list_id'])) {
        print '<a href="orphan_lists.php?list_id=' . $_POST['list_id'] . '">Click to delete orphan list ' . $_POST['list_id'] . '</a><br><br>';
        }
if (isset($_GET['list_id'])) {
        mysql_query('delete vicidial_list.* from vicidial_list where list_id=' . $_GET['list_id']);
        print 'Orphan list ' . $_GET['list_id'] . ' has been deleted.<br><br>';
        }

print '<h3>Orphan Lists</h3>';

$rows=mysql_query('select vicidial_list.list_id,count(lead_id) as lead_count from vicidial_list left join vicidial_lists on ' .
        'vicidial_list.list_id=vicidial_lists.list_id where vicidial_lists.list_id is null group by vicidial_list.list_id ' .
        'order by vicidial_list.list_id');

while ($row=mysql_fetch_assoc($rows)) {
        if ($ct==0) print '<table><tr><td style="background-color:#BBBBBB">List ID</td>' .
                '<td style="background-color:#BBBBBB">Lead Count</td><td style="background-color:#BBBBBB">&nbsp;</td></tr>';
        print '<tr><td>' . $row['list_id'] . '</td><td>' . $row['lead_count'] .
                '<td><form style="margin-top:0;margin-bottom:0" method=post><input type=hidden name="list_id" value=' . $row['list_id'] .
                '><input type=submit value=Delete></form>' .
                '</td></tr>';
        $ct++;
        }
print ($ct==0 ? '<b>There are no orphan lists.</b><br>' : '</table>');
?>


[DomeDan]

Thats a very dangerous script, anyone can simply do "?list_id=1 OR 1=1" to delete everything from vicidial_list

do like Matt is doing:

Code: Select all

if (isset($_GET["list_id"]))       {$list_id=$_GET["list_id"];}
  elseif (isset($_POST["list_id"]))  {$list_id=$_POST["list_id"];}

$list_id = ereg_replace("[^0-9]","",$list_id);


[nkasoff]

Thanks for the tip, I've made that change. Actually, the code also needs to be modified so that the GET variable is only accepted if the referring page is this one. That will prevent somebody from running an external script that cycles through numbers and deletes lots of lists. And ideally, it would be included in the Vicidial core so that a Vici admin login would be required, but since I'm not a Vicidial developer, that's somebody else's job.

[williamconley]

Virtually every script page on the system has the necessary security setup. Many pages are very long, but actually have only a few lines of "unique program" at the end, the rest is setup (gather data, check security, clean post/get data ... then execute). Borrowing security/setup from any of those other pages can help to ensure "the same setup" as the rest of Vicidial which is fairly secure.