Multiple Campaign Settings Change

[kingleigh]

One of my clients have 600+ campaigns and i need to change the settings on all of them but want to do this without individually changing each one.

Can you tell me if there is a quick way to do this? If it is an sql query can you let me know the text please?

Thanks,

[DomeDan]

I too have been thinking about this feature, started to write my own but ended up with a page just to show the difference between settings on the campaigns, and that has been handy time to time.

The query to change settings on all campaign is very simple, but its dangerous.
you should start out with a query that can count campaigns depending on some setting, for example lets check lead_order:

Code: Select all

SELECT lead_order, COUNT(*) FROM vicidial_campaigns GROUP BY lead_order

Code: Select all

+---------------------------+----------+
| lead_order                | COUNT(*) |
+---------------------------+----------+
| DOWN                      |        3 |
| UP LAST CALL TIME         |       19 |
| UP LAST CALL TIME 6th NEW |        2 |
+---------------------------+----------+

So most of my campaigns use "UP LAST CALL TIME", I want to update all campaigns to use that
but maybe there is a reason 3 of them uses DOWN that I didn't think about, thats why its good to look at the data before doing any big changes like updating 600 campaigns.

[mcargile]

It would have to be done in MySQL. There is nothing in Vicidial to mass update campaigns. I do not recommend doing this unless you absolutely have to. If you mess something up you will have a very bad time fixing it.

As far as the SQL to accomplish this, we would need to know what you want to change.

If you go into the campaign and hover of the help dialog ? link for the field you want without clicking it, Firefox will display the JavaScript that is going to be executed. For instance Active in the campaign section looks like this:

Code: Select all

javascript:openNewWindow('/vicidial/admin.php?ADD=99999#vicidial_campaigns-active')

This opens a new window with the help dialog and jumps down to the anchor for that field. Matt named the anchors after the DB table / field that it involves for the most part using this format DBTABLE-DBFIELD. So for instance the field that controls if the campaign is active is in the 'vicidial_campaign' table and is called 'active'.

Once you know the table / field you want to change you need to go into the database and find out what is supposed to go into that field. Do a describe on the table and then do a select on that field to find out the sorts of values that are there. For instance vicidial_campaigns / active the describe looks like this:

Code: Select all

mysql> describe vicidial_campaigns;
+---------------------------------------+------------------------------------+------+-----+---------------------------------------------------------------+-------+
| Field                                 | Type                               | Null | Key | Default                                                       | Extra |
+---------------------------------------+------------------------------------+------+-----+---------------------------------------------------------------+-------+
| campaign_id                           | varchar(8)                         | NO   | PRI | NULL                                                          |       |
| campaign_name                         | varchar(40)                        | YES  |     | NULL                                                          |       |
| active                                | enum('Y','N')                      | YES  |     | NULL                                                          |       |
| dial_status_a                         | varchar(6)                         | YES  |     | NULL                                                          |       |
| dial_status_b                         | varchar(6)                         | YES  |     | NULL                                                          |       |
| dial_status_c                         | varchar(6)                         | YES  |     | NULL                                                          |       |
| dial_status_d                         | varchar(6)                         | YES  |     | NULL                                                          |       |
| dial_status_e                         | varchar(6)                         | YES  |     | NULL                                                          |       |

....


and a select will look something like this:

Code: Select all

mysql> select active from vicidial_campaigns;
+--------+
| active |
+--------+
| Y      |
| N      |
| Y      |
| Y      |
| N      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
| Y      |
+--------+
23 rows in set (0.00 sec)

This should give you a pretty good idea how the data should be formatted. Now it is time to build the query:

Code: Select all

UPDATE vicidial_campaigns SET active='Y' where active = 'N';

This query will set all of the campaigns to active that were not active. I realize this is really quite basic, but its a good place to start. One big thing to keep in mind is that you need to be really careful before doing this. I recommend backing up the table you are about to mess with before making changes, make the changes outside of production, and remember to test the changes before the agents log back in. You will be very unpopular if you screw something up on all of your campaigns and have no way to revert back. To back up a specific table you can use the mysqldump command like this:

Code: Select all

#> mysqldump asterisk vicidial_campaigns > campaign_backup.sql

If need be you can restore it like this:

Code: Select all

mysql> use asterisk;
mysql> \. campaign_backup.sql

This will drop the table, recreate it, and load it with the old values.

Lastly keep in mind that the web interface /api does a bunch of error checking to ensure that bad values are not inserted into the database. If you are going to be doing this frequently I highly recommend paying for us to write an API call to do what you are trying to accomplish rather than doing it manually. By doing it directly you run the risk of causing more headaches for yourself than is necessary.