Toner inventory Database

[webman]

Ctorp, I'm liking your enthusiasm

 

"Reorder point" for each consumable - this is something I plan to add in the future as I think it'd be a good thing. For obvious reasons - you need more stock of your popular models, and less of the single ACME PhotoSmartJet you have tucked away in an office.

 

The inactive idea is good too - that should be really easy to do

 

Here's a little tip if you need to decrease consumable stock without installing to a printer (e.g. forgot to log an install): click the button to add stock, but enter a negative value. This must take the stock level to 0 or above. E.g stock is 5, but you want it to read 3. Enter -2 in the quantity.

[Ctorp]

I have already added the reorder_pt and updated my db to verify it works. I also created the Reorder page with links and it shows a table that is very similar to the Consumable Stock section on the homepage (I used it as a template).

 

I am now in the process of reverse engineering a way to set up Critical as any consumable with:

 

- ($status[0]=array('Critical', 'CC0000'); // no stock

- ($status[$value]=('Low', 'EDD400'); // 1 to reorder_pt minus 1

- ($status[$value]=('OK', '73D216'); // at or above reorder_pt

 

Where $value is set by comparing $reorder to $qty where:

 

$reorder=$c->getReorderPt();

$qty=$c->getQty();

 

With something like:

 

if($qty <= $reorder) { $value= blah;}

elseif() {..}

 

Sorry if this is a bit slapdash. I'm still working on this part at the moment, actually.

 

Once this is done I am hoping that I can use the existing 'Low' and 'Critical' identifiers to populate the Reorder form effectively.

 

I still haven't tried tackling the 'undo' feature or the password protection. I am glad you mentioned the +/- adding option for manipulating the stock though. That should prove useful.

[Ctorp]

This is what I have so far. You would need to add a field to the 'consumables' table for last_order and reorder_pt for it to work.

 

Attachment: inventory_(modded_by_CTorp).zip

 

It is still very much a work in-progress compared to what I have envisioned, but I'll be back next week when I can look at it again.

 

My have-done notes:

- Added Last Order field to keep track of the last time a consumable was ordered

- Added Reorder Point field to allow each toner it's own 'Low', 'Critical, 'OK' reference

- Added Reorder page with sorting the same as it was on the home page, but including the reorder point and re-sized for easy print-outs (for Finance dept. heads)

 

My want-to-do notes:

- I want to add at least 2 more columns to addedit.php in the views\consumables folder because beyond a certain point the page scrolling gets ridiculous.

- I'm probably going to change the dates to mm-dd-yyyy standard. My boss would probably prefer this as it is what he is used to with our old toner db.

- Printer active/inactive status. If inactive, do not display consumables in reports/stock

- Consumable toners should allow only one color selection. (Unless there are multi-color toners that come in a single cartridge that I'm not aware of.)

- Create an 'undo last consumed' option on the off chance someone consumes a cartridge and does not.

- Password protect the inventory so it can be hosted on the local network for use by all I.T. members without fear of a user stumbling upon and *#%^ing with it.

Edited April 15, 2011 by Ctorp

[Ctorp]

Still working on this. I have used a simple webtoolkit pre-made script to password protect the inventory pages, and it drops authentication every time the browser is restarted which is good enough for me, but probably not the best solution if you're in a true corporate environment.

 

I also tried adding the printer name to the reorder page, but couldn't figure it out since I used the homepage stock as a template. (Trying to use something like foreach($printers as $p) then echoing $p->name clearly does not work, but it will make your page explode with many values. ^_^)

 

I'm investigating the $status[#]=array('OK', '73D216'); // #+ OK lines at the moment to try to change this dependent on the values set as reorder_pt and qty.. I believe the $status[] portion is defined in views\home\stock.php

 

And I've added an Active field to the printers table which is populated by 0 for active and 1 for inactive. I have not incorporated this into the php, yet.

Edited April 18, 2011 by Ctorp

[webman]

The numeric value in the $status array is the quantity of a consumable that must be reached for that level (ok/critical/low). To customise these per consumable, the table will need to be altered to accept these values, which will be entered on the add/edit consumable form.

[Ctorp]

The numeric value in the $status array is the quantity of a consumable that must be reached for that level (ok/critical/low). To customise these per consumable, the table will need to be altered to accept these values, which will be entered on the add/edit consumable form.

 

Ah. Having added the "Reorder_pt" value to the add/edit consumable form and to the consumables table, I was thinking

 

0 - Universal 'Critical' value. (or 0&1)

>0 & < Reorder_pt - 'Low' values

>= Reorder_pt - 'OK' values

 

I have changed the line in views\home\stock.php at 28 to:

foreach($status as $k => $v){
	if($c->qty >= $k){
		$qtylevel = strtolower($v[0]);
		$qtycol = $v[1];
		$qty = $c->qty;
		$reorder = $c->reorder_pt;
		break;
	}
}

then changed the first lines of config.php to:

//change to critical if 0 // low if qty < reorder_pt && > 0 // OK if qty = or > reorder_pt
global $qty, $reorder;
if($qty=='0') { $status = array('OK', '73D216'); }
elseif($qty < $reorder) { $status = array('Low', 'EDD400'); }
elseif($qty >= $reorder) { $status = array('Critical', 'CC0000'); }

but it does not work this way. I'm still muddling through to figure out why.

[webman]

I think you're close! I'd be tempted to create a function for this similar to getQtyStatus() in the consumable model file (or replace that one). The function can return an array of ok/low/critical + the colour based on a) the actual quantity of the consumable, and b) the re-order level for that consumable.

 

A sensible place for this would be in the consumable model file. This way, you can call the function on an instance of it, and it will already have access to the data needed.

 

So, for example. A consumable with a qty of 6 and a reorder level of 3:

 

$this_status = $c->get_status();

 

You would then have the info you need in an array like this: Array('ok', '73D216'). $status[0] is the status name, $status[1] is the colour.

 

The function could look like some of your code above:

 

function get_status(){
   $qty = $this->getQty();
   $reorder = $this->getReOrder();
   if ($qty > $reorder) {
       return array('ok', '73D216');
   }
   if ($qty <= $reorder && $qty > 0) {
       return array('low', 'EDD400');
   }
   if ($qty == 0) {
       return array('critical', 'CC0000');
   }
}
}

 

Or something like that anyway. Just a few ideas...

[ticker]

Hi

 

i have set this up no problem and can get to the home page but when i click on any of the links it puts the address like this: http://192.168.0.16printmaster//manufacturers.php any ideas why its moving the slash?

[webman]

ticker: Try changing inc/config.php, line 12, to look like this:

 

define('URL_ROOT', '/printmaster/');

[ticker]

just get a syntax error on that line when entering the above?

[webman]

Cold you copy and paste those two define() lines into here?

[ticker]

As requested

 

define('DOC_ROOT', realpath(dirname(__FILE__) . '/../'));

define('URL_ROOT', str_replace ('\\', '/', substr(DOC_ROOT, strlen(realpath($_SERVER['DOCUMENT_ROOT']))) . '/'));

[webman]

Remove the second line for URL_ROOT, and replace it with the one I included in my previous post. You should then have two lines that look like this:

 

define('DOC_ROOT', realpath(dirname(__FILE__) . '/../'));
define('URL_ROOT', '/printmaster/');

[ticker]

spot on it work thanks, its a great piece of software. Might also try the site jobs one as well once i have entered all 80 odd printers!

[Ctorp]

I think you're close! I'd be tempted to create a function for this similar to getQtyStatus() in the consumable model file (or replace that one). The function can return an array of ok/low/critical + the colour based on a) the actual quantity of the consumable, and b) the re-order level for that consumable.

 

A sensible place for this would be in the consumable model file. This way, you can call the function on an instance of it, and it will already have access to the data needed.

 

So, for example. A consumable with a qty of 6 and a reorder level of 3:

 

$this_status = $c->get_status();

 

You would then have the info you need in an array like this: Array('ok', '73D216'). $status[0] is the status name, $status[1] is the colour.

 

The function could look like some of your code above:

 

function get_status(){
   $qty = $this->getQty();
   $reorder = $this->getReOrder();
   if ($qty > $reorder) {
       return array('ok', '73D216');
   }
   if ($qty <= $reorder && $qty > 0) {
       return array('low', 'EDD400');
   }
   if ($qty == 0) {
       return array('critical', 'CC0000');
   }
}
}

 

Or something like that anyway. Just a few ideas...

 

I think this has moved over my head in terms of my knowledge of Flourish and PHP. (I'm still newish to PHP.)

Where is the consumable model file located? I tried doing a directory search for getQtyStatus(), but did not find it.

 

Supposing that the get_status() function works, where would you place

$this_status = $c->get_status();

in order to call it at the right time?

[webman]

All model files are in the inc/classes folder. The one for consumables is Consumables.php.

 

You would then call that function anywhere you need to know the status of a consumable - e.g. on the home page for the list, and in the consumable listing page.

[Ctorp]

I added the following to %root%\inc\classes\Consumable.php:

	/**
 * Get Status of Consumable
 */
static public function get_status(){
	$qty = $this->getQty();
	$reorder = $this->getReOrder();
	if ($qty > $reorder) {
		return array('ok', '73D216');
	}
	if ($qty <= $reorder && $qty > 0) {
		return array('low', 'EDD400');
	}
	if ($qty == 0) {
		return array('critical', 'CC0000');
	}
}

But no matter where I called it in %root%\views\home\stock.php , I could not get it to work.

I ended up trying to just transplant it there, but that did not work either. I am still sitting at line 29 with:

	foreach($status as $k => $v){
	$qty = $c->qty;
	$reorder = $c->reorder_pt;
#		if ($qty > $reorder) { return array('Ok', '73D216'); }
#		if ($qty <= $reorder && $qty > 0) { return array('Low', 'EDD400'); }
#		if ($qty == 0) { return array('Critical', 'CC0000'); }
	if($c->qty >= $k){
		$qtylevel = strtolower($v[0]);
		$qtycol = $v[1];
		break;
	}
}

In the meantime, I've set up the printer addedit page ( %root%\views\printers\addedit.php ) with a snippet that adds a checkbox to indicate Inactive/Active status for the inventory. This works, but is not implemented with any functionality yet..

 

So I am trying to establish a way for the reorder page ( %root%\reorder.php ) to NOT query consumables that belong to an inactive printer, but my query seems to break when I try it. I have:

$sql = "SELECT 
	consumables.*,[color="darkslateblue"] printers.active,[/color] 
	( round( ( (consumables.qty) / (SELECT MAX(qty) FROM consumables) ) * 100 ) ) AS qty_percent,
	GROUP_CONCAT(CAST(CONCAT(manufacturers.name, ' ', models.name) AS CHAR) SEPARATOR ', ') AS model
	FROM consumables[color="darkslateblue"], printers[/color]
	LEFT JOIN consumables_models ON consumables.id = consumables_models.consumable_id
	LEFT JOIN models ON consumables_models.model_id = models.id
	LEFT JOIN manufacturers ON models.manufacturer_id = manufacturers.id
	[color="darkslateblue"]WHERE printers.active = 0[/color]
	GROUP BY consumables.id
	ORDER BY models.name ASC, consumables.name ASC";

 

I also removed the snippet of code that was allowing a full row to be treated as a selectable item for the checkboxes in %root%\views\consumables\addedit.php. The formatting looks the same, but I kept inadvertently selecting multiple items when scrolling the page so now only clicking the name or checkbox will select it, instead of being able to click the empty row space.

 

Again, thank you so much for sticking with me through this. When I get it finished I will gladly post what I have again.

 

So, at present my Reorder page looks like this:

http://i.imgur.com/Y0hFQ.jpg

Edited April 20, 2011 by Ctorp

[ticker]

Hi Webman

 

Now looking at setting up both site jobs and login tracker have you any instructions on setting them up.

 

Cheers

[maan2004]

Hi Webman,

 

it is nice and simple way to track cartridge inventory , if any one would help me to do some minor things , it would be nice if I can add a comment in adding Consumable , and to be able to see it at reports section , and to have filtering for the reports like Consumable,Printer and Model

[RabbieBurns]

@webman I cant seem to figure out how I can delete a printer?

[webman]

RabbieBurns: Click on printer name, click red Delete button at bottom of the 'Edit' page

[RabbieBurns]

Aha. Thanks

[eclass]

love this script however I can't seem to figure out how to update the ink consumable stock number, my stock number is currently grayed out and cant edit it. Any ideas

 

Thanks

[webman]

eclass: To update quantity of consumable stock, use the right side of the home page. Click the yellow box icon next to the toner you want to add, and enter the quantity in the popup dialog box.

[eclass]

When I click it, It takes me this page that says "simple"

 

I attached a screen shot