Orphan leads

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Orphan leads

Postby nkasoff » Fri Nov 14, 2008 11:12 am

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.
nkasoff
 
Posts: 63
Joined: Sun Jun 29, 2008 11:23 am

Postby mflorell » Fri Nov 14, 2008 10:34 pm

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;
mflorell
Site Admin
 
Posts: 18387
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Postby nkasoff » Sat Nov 15, 2008 1:03 pm

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;
nkasoff
 
Posts: 63
Joined: Sun Jun 29, 2008 11:23 am

Postby williamconley » Sat Nov 15, 2008 9:30 pm

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 ...)
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20278
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Orphan leads

Postby nkasoff » Tue Jun 12, 2012 11:13 am

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>');
?>
nkasoff
 
Posts: 63
Joined: Sun Jun 29, 2008 11:23 am

Re: Orphan leads

Postby DomeDan » Wed Jun 13, 2012 3:40 am

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);
Vicidial Partner. Region: Sweden/Norway.
Does Vicidial installation, configuration, customization, add-ons, CRM implementation, support, upgrading, network-related, pentesting etc. Remote and onsite assistance.
Email: domedan (at) gmail.com
DomeDan
 
Posts: 1226
Joined: Tue Jan 04, 2011 9:17 am
Location: Sweden

Re: Orphan leads

Postby nkasoff » Wed Jun 13, 2012 8:41 am

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.
nkasoff
 
Posts: 63
Joined: Sun Jun 29, 2008 11:23 am

Re: Orphan leads

Postby williamconley » Fri Jul 06, 2012 10:09 am

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.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20278
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)


Return to Support

Who is online

Users browsing this forum: Bing [Bot] and 61 guests