[Release] Restock trader vehicles based on MaxVehicleLimit+existing db vehicles

[Sandbird]

I was getting a lot of complains about restocking vehicles at vendors but based on the MaxVehicleLimit in init.sqf, i couldnt really do much about it. So i sat down today to write a PHP script that will repopulate vendors based on the MaxVehicleLimit and on how many vehicles are in the database.

This script is NOT for everybody, so read carefully first before doing it.....and always backup, backup, backup before trying anything.

What this script does:

- Checks how many vehicles are allowed on the map (MaxVehicleLimit, in init.sqf)

- Based on a value YOU set for the maximum normal epoch spawned vehicles you want to be spawned on the map on every restart, it will calculate how many vehicles are already in the database, how many 'bought' vehicles exist already on the map, how many available quantities the vendors can hold....and then it will repopulate any left overs till MaxVehicleLimit at random vendor slots.But in order to do so It WILL delete any untouched vehicle on the map, that have't been used for 15 days, either bought or normal spawned.

- Also it will always delete (randomly) any excess normal spawned vehicles all the way down to the value you set at the beginning of the script.

 

At the top of the script you'll see :

$HOST = "localhost";// DATABASE HOST/IP
$PASS = "";// DATABASE PASS
$USER = "root";// DATABASE USER
$DB   = "dayz_epoch1031";// DATABASE NAME
$INSTANCE = "17";//HIVE SERVER INSTANCE
                
$MaxVehicleLimit= 500;
$maxMapVehicles = 150;  // How many normal epoch vehicles (not bought) do you want to have on the map (x out of 500)
$unwantedVehicles = 15; // Max number of vehicles in traders
$maxTotalVendorStock = 2700;

Set your database details there.
$MaxVehicleLimit is the number you have in your init.sqf. How many vehicles you want to have on the map.

$maxMapVehicles is the maximum amount of the vehicles you want dayz to spawn on every restart ( not bought vehicles, normal vehicles you find randomly around the map).

Every time the server restarts new vehicles will be allowed to spawn or be deleted to reach THIS number.

$unwantedVehicles is the max amount of vehicles for each category you want in the database...For example if during the game a vendor has 33 MTVRs, after restart it will be 15.

$maxTotalVendorStock = 2700; This is a very important value. This is the total amount of cars you want the vendors to have in the database.  Based on this number you'll either get repopulated vendors on restart, or not. If you dont know your 'magic' value...then run info.php first to see it. Its the Total Vendor Cars entry.

I've created a new table on the database called allowedvehicles where i have all the epoch vehicles that can be spawned or bought in the game. You need this table to be able to do comparisons.

To create your own allowedvehicles table open : @DayZ_Epoch_Server\addons\dayz_server\missions\DayZ_Epoch_17.Chernarus\dynamic_vehicle.sqf  and grab all the classnames there. This is how i made mine.

These are all the legit vehicles that traders sell or epoch spawns....If you have added custom vehicles in your map...then my script will not take them in account. You have to add them in the allowedvehicles table.

The easiest way to produce that table is reformat that .sqf file in a notepad and create an SQL insert entry to import it in the database.

Or you can use mine....Its the basic epoch 1042 dynamic_vehicle.sqf with some pook helicopters at the bottom.

 

At line 37 you'll see :

SELECT object_data.ObjectID FROM object_data, allowedvehicles WHERE allowedvehicles.className = object_data.Classname AND object_data.CharacterID = '0' AND
                   object_data.LastUpdated < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 15 DAY) ORDER BY rand() LIMIT ".$leftOvers);

This will delete any normal spawned vehicles that might exist on the server, all the way down to $maxMapVehicles. This way it makes room for new vendor slots to be filled.

(Update: This will only delete normal epoch cars untouched for 15 days...Lower this value to increase chances of getting more free space for vendors.)

 

At line 46:

SELECT object_data.ObjectID FROM object_data, allowedvehicles WHERE
                   allowedvehicles.className = object_data.Classname AND
                   object_data.LastUpdated < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 15 DAY)
                   ORDER BY rand()");         

I select any vehicle that hasnt been used for 15 days from the database and delete it further down....so people who stopped playing on the server will get their vehicles deleted after 15 days.

 

At line 65 i am selecting the total amount of vehicles in vendors:

SELECT SUM(qty)as total FROM traders_data WHERE qty > '0' AND
`item` NOT LIKE '%ATV_%' AND
`item` NOT LIKE '%Old_bike%' AND
`tid` IN (SELECT id FROM `trader_tids` WHERE trader IN (SELECT id FROM server_traders WHERE instance = '".$INSTANCE."' AND (`desc` LIKE '%Aircraft%' OR `desc` LIKE '%Bandit Trader%' OR `desc` LIKE '%Hero Vendor%' OR `desc` LIKE '%Vehicles%'))) AND
afile = 'trade_any_vehicle'

Notice the $INSTANCE. I use instance 17 ( thats where my vendors are ). Make sure you use YOUR instance, otherwise wrong vendors will be updated.

Also i am not selecting Boat Vendors...Only car, and air vehicles. Noone really cares about boats :P

 

At line 77:

if ($canAddToVendors > 0){

This is where the restocking happens. If $canAddToVendors value is positive...that plus amount of vehicles will be distributed in the vehicle vendors. One at a time and random vehicle categories.

 

And now....how to run the script ? Well.....i bet 99% of the people here use some type of .bat file to restart the server right, using BEC's scheduler. ?

If you have an apache server on that machine, then you can use the PHP command line to execute the script BEFORE you start your server.

I have WAMP installed on the server, so all i had to add was:

echo.
echo Restocking vendors......
C:\wamp\bin\php\php5.2.6\php.exe -f C:\wamp\www\vehiclesclean\run.php
ping 127.0.0.1 -n 1 >NUL
C:\wamp\bin\php\php5.2.6\php.exe -f C:\wamp\www\vehiclesclean\run.php
ping 127.0.0.1 -n 1 >NUL
C:\wamp\bin\php\php5.2.6\php.exe -f C:\wamp\www\vehiclesclean\run.php
ping 127.0.0.1 -n 1 >NUL
C:\wamp\bin\php\php5.2.6\php.exe -f C:\wamp\www\vehiclesclean\run.php
ping 127.0.0.1 -n 1 >NUL
echo.
echo Starting server...
cd /D "C:\Program Files (x.... blah blah

Why i run the script 4 times you ask ? Well, there are 2 possibilities here. 1) I've been working on this script for almost 12h and i missed something on a 'if' or 'while' loop that i am too tired to figure out where and why, and its not looping correctly....or 2) I remember with the good old update_vehicles.pl script back in normal chernarus, i had to also run that perl script 3-5 times to properly update the database....maybe its a dayz thing ?

I cant think straight anymore :)

 

 

Update 21/4/2014

• File has been updated. Now i include an info.php file...Its the same as the run.php but it will only show results...It wont execute anything...Its more of an information page so you can see what's going on in your server. Run this 1st to see whats happening with the vehicle population in your server.
• Of course you have to have the allowedvehicles table in your database...I've included mine...Its the default dayz_1042 epoch vehicles with some pook bell helis added at the bottom.I dont use them all, so i didnt include them all. Just edit the .sql file to your liking before importing it.
• After you run the info.php you might also get negative values in then Space available entry...for example:
  

  Normal Cars: 188
  Bought Cars: 285
  Total Cars: 473
  Total Vendor Cars: 3391 (without bicycles, atvs)
  Space available:-1726
  
  maxTotalVendorStock 2700
  MaxVehicleLimit 500
  tillMax 62
  

  That means no new cars are going to be added since i am already +1726 more vehicles. If you do get negative value and you are sure you've set the  $MaxVehicleLimit , $maxMapVehicles, $unwantedVehicles correctly then raise $maxTotalVendorStock till you start seeing positive values in the Space available.
  That will be how many new vehicles will be added in vendors on server restart.

• Read this thread from the beginning...Changes have been made to the text...and remember....Run info.php first to get a sense of things.

 

CLICK HERE TO DOWNLOAD SCRIPT

 

 

Feel free to contribute, ask questions, etc etc.....And dont forget....backup, backup, backup first.

[Diesel Weasel]

I am EXTREMELY interested in running this script on our server but the only issue is we rent from Vilayer. I taught myself all the scripting for our server so that we would be open to more players. Right now I have our limit at 600 and with people buying vehicles left and right we are well over that. From past experience I also know that we tend to end up with dozens of unwanted cars sprinkled around the map. This sounds like a great compromise! The only thing is I don't understand how I'd be able to run the script. I use Vilayer's restart system and it's worked pretty well for us so far. I do have several database events that run just fine and was wondering if there was a way this could be set as an event as well? I confess I'm new to the database events so if I'm being blind and this will run fine like the others I apologize. 

[Sandbird]

Since it needs to run sql queries and do calculations with them, i highly doubt it can be compressed down to SQL functions. Maybe an SQL guru might be able to do something.

You definitely need a PHP server to execute this...i think it could be hosted anywhere. If Vilalayer had port 3306 open and allows remote SQL control panels to connect (like Navicat from your PC) then for sure you can set a remote connection from the script to run.

Now about the startup thing...i dont know how vilalayer startup script operates, but you can ask them that.

It depends how they do their restarts...if they use a linux based OS then you can host the script somewhere on the net....for example: http://myblahblahhoster.com/run.php and then with curl you can access the script remotely and if you've set your database IP, user, pass, db_name then the script will remotely run.

If they are using windows, and they can add custom stuff per client request, then you can just send them that line i mention in the end...to run php.exe with the location of the script.

 

If you cant do any of the above, the simplest thing you could do is create this event:
(as an SQL query to make it)

CREATE DEFINER=`dayz`@`localhost` EVENT `Delete20daysOldVehicles` ON SCHEDULE EVERY 1 DAY STARTS '2013-12-22 16:05:39' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Deletes 20 days old vehicles' DO DELETE FROM `object_data` WHERE
`LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY) AND
`Classname` NOT IN ('VaultStorage','LockboxStorage','VaultStorageLocked','LockboxStorageLocked','WoodShack_DZ','StorageShed_DZ','TentStorageDomed','TentStorageDomed2','TentStorage','OutHouse_DZ','Wooden_shed_DZ','GunRack_DZ')  AND
`Inventory` <> '[]' AND
`Inventory` IS NOT NULL

(or if you want to create the event your self)...Set is as a recurring event, every 1 day:

DELETE FROM `object_data` WHERE
`LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY) AND
`Classname` NOT IN ('VaultStorage','LockboxStorage','VaultStorageLocked','LockboxStorageLocked','WoodShack_DZ','StorageShed_DZ','TentStorageDomed','TentStorageDomed2','TentStorage','OutHouse_DZ','Wooden_shed_DZ','GunRack_DZ')  AND
`Inventory` <> '[]' AND
`Inventory` IS NOT NULL

This wont do all the above, but it will delete all 20 days 'untouched' vehicles.

Somewhere on this forum there is the above SQL event but only for bought vehicles.

My way though will delete ANY vehicle that hasnt been touched for 20 days...I think its better this way, so vehicles that are well hidden will be deleted to).

 

Epoch doesnt have a simple way to distinguish vehicles in the database. This is the closest you can get....Even so, it will not delete vehicles that have inventory set to []....and some times this happens.

Thats why i created a new table to hold all the epoch vehicles so i am 100% i am deleting everything in my script.

[Sandbird]

Update: Updated script at line 35:

AND object_data.LastUpdated < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY)

This will only delete normal map spawned cars that havent been touched for 10 days.

The way it was before, it was deleting randomly any epoch car till it reaches 150 vehicles.

This way you might get less empty slots for vendors, but at least people finding vehicles will have a chance to keep them :)

Lower the value to get more chances of extra slots for vendors.

[Diesel Weasel]

I ended up Frankensteining a few SQL events together into one event that seems to be working pretty well for us. It's not as nice as this of course but it deletes vehicles older than 10 days which haven't been touched in 5 days AND have no inventory. This means buildings are saved and all you have to do to protect a vehicle from being deleted is put at least one item inside it. No more torn apart vehicles rotting on the server for weeks at a time and finally new stuff can spawn!

 

If anyone is stuck like I as and can't use this script because they rent, here's my creation. I hope it might be able to help someone someday.

CREATE EVENT `removeObjectEmpty`
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 DAY)
AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY)
AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') OR (`Inventory` = '[[[],[]],[[],[]],[[],[]]]') )

[Sandbird]

To create your own allowedvehicles table open : @DayZ_Epoch_Server\addons\dayz_server\missions\DayZ_Epoch_17.Chernarus\dynamic_vehicle.sqf  and grab all the classnames there.

This is how i made mine. These are all the legit vehicles that traders sell or epoch spawns....If you have added custom vehicles in your map...then my script will not take them in account.

[Coffee]

Do you still have this script please i'm very interested in using it however the download link appears to be invalid

[Sandbird]

Do you still have this script please i'm very interested in using it however the download link appears to be invalid

 

Ok i updated the 1st post....dont know why that file was delete....good that it did thought, i've updated the script :)

Read the thread from the beginning again...i've changed some things.

If you have questions post here...

 

RUN THE INFO.PHP 1st !!!!