Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[6804] trunk
|
From: <aga...@us...> - 2014-08-08 13:44:53
|
Revision: 6804
http://sourceforge.net/p/web-erp/reponame/6804
Author: agaluski
Date: 2014-08-08 13:44:43 +0000 (Fri, 08 Aug 2014)
Log Message:
-----------
Added database tables for location based security.
Added new maintenance screen for Location based security
Added new report AgedControlledInventory.php
Updated change log
Updated Location maintenance to update new locationusers table
Update menu array links to include new maintenance screen and report
Modified Paths:
--------------
trunk/Locations.php
trunk/doc/Change.log
trunk/includes/MainMenuLinksArray.php
trunk/sql/mysql/upgrade4.11-4.12.sql
Added Paths:
-----------
trunk/AgedControlledInventory.php
trunk/LocationUsers.php
trunk/Z_MakeLocUsers.php
Added: trunk/AgedControlledInventory.php
===================================================================
--- trunk/AgedControlledInventory.php (rev 0)
+++ trunk/AgedControlledInventory.php 2014-08-08 13:44:43 UTC (rev 6804)
@@ -0,0 +1,96 @@
+<?php
+
+/* $Id: AgedControlledInventory.php 1 2014-08-08 04:47:42Z agaluski $ */
+
+include('includes/session.inc');
+$PricesSecurity = 12;//don't show pricing info unless security token 12 available to user
+$Today = time();
+$Title = _('Aged Controlled Inventory') . ' as-of ' . Date(($_SESSION['DefaultDateFormat']), strtotime($UpcomingDate . ' + 0 days'));
+include('includes/header.inc');
+
+echo '<p class="page_title_text">
+ <img src="'.$RootPath.'/css/'.$Theme.'/images/inventory.png" title="' . _('Inventory') .
+'" alt="" /><b>' . $Title. '</b>
+ </p>';
+
+$sql = "SELECT stockserialitems.stockid,
+ stockmaster.description,
+ stockserialitems.serialno,
+ stockserialitems.quantity,
+ stockmoves.trandate,
+ stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS cost,
+ decimalplaces
+ FROM stockserialitems
+ LEFT JOIN stockserialmoves ON stockserialitems.serialno=stockserialmoves.serialno
+ LEFT JOIN stockmoves ON stockserialmoves.stockmoveno=stockmoves.stkmoveno
+ INNER JOIN stockmaster ON stockmaster.stockid = stockserialitems.stockid
+ INNER JOIN locationusers ON locationusers.loccode=stockserialitems.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
+ WHERE quantity > 0
+ GROUP BY stockid, serialno
+ ORDER BY trandate
+ ";
+
+$ErrMsg = _('The stock held could not be retrieved because');
+$LocStockResult = DB_query($sql, $db, $ErrMsg);
+$NumRows = DB_num_rows($LocStockResult, $db);
+
+$j = 1;
+$TotalQty=0;
+$TotalVal=0;
+$k=0; //row colour counter
+echo '<table>
+ <tr>
+ <th class="ascending">' . _('Stock') . '</th>
+ <th class="ascending">' . _('Description') . '</th>
+ <th class="ascending">' . _('Batch') . '</th>
+ <th class="ascending">' . _('Quantity Remaining') . '</th>
+ <th class="ascending">' . _('Inventory Value') . '</th>
+ <th class="ascending">' . _('Date') . '</th>
+ <th class="ascending">' . _('Days Old') . '</th>
+ </tr>';
+while ($LocQtyRow=DB_fetch_array($LocStockResult)) {
+
+ if ($k==1){
+ echo '<tr class="OddTableRows">';
+ $k=0;
+ } else {
+ echo '<tr class="EvenTableRows">';
+ $k=1;
+ }
+ $DaysOld=floor(($Today - strtotime($LocQtyRow['trandate']))/(60*60*24));
+ $TotalQty +=$LocQtyRow['quantity'];
+ //$TotalVal +=($LocQtyRow['quantity'] *$LocQtyRow['cost']);
+ $DispVal = '-----------';
+ if (in_array($PricesSecurity, $_SESSION['AllowedPageSecurityTokens']) OR !isset($PricesSecurity)) {
+ $DispVal =locale_number_format(($LocQtyRow['quantity']*$LocQtyRow['cost']),$LocQtyRow['decimalplaces']);
+ $TotalVal +=($LocQtyRow['quantity'] *$LocQtyRow['cost']);
+ }
+ printf('<td>%s</td>
+ <td>%s</td>
+ <td>%s</td>
+ <td class="number">%s</td>
+ <td class="number">%s</td>
+ <td>%s</td>
+ <td class="number">%s</td></tr>',
+ mb_strtoupper($LocQtyRow['stockid']),
+ $LocQtyRow['description'],
+ $LocQtyRow['serialno'],
+ locale_number_format($LocQtyRow['quantity'],$LocQtyRow['decimalplaces']),
+ $DispVal,
+ ConvertSQLDate($LocQtyRow['trandate']),
+ $DaysOld);
+
+
+} //while
+if ($k==1){
+ echo '<tfoot><tr class="OddTableRows">';
+ $k=0;
+} else {
+ echo '<tfoot><tr class="EvenTableRows">';
+ $k=1;
+}
+echo '<td colspan="3"><b>' . _('Total') . '</b></td><td class="number"><b>' . locale_number_format($TotalQty,2) . '</td><td class="number"><b>' . locale_number_format($TotalVal,2) . '</td><td colspan="2"></td>';
+echo '</table>';
+
+include('includes/footer.inc');
+?>
\ No newline at end of file
Added: trunk/LocationUsers.php
===================================================================
--- trunk/LocationUsers.php (rev 0)
+++ trunk/LocationUsers.php 2014-08-08 13:44:43 UTC (rev 6804)
@@ -0,0 +1,260 @@
+<?php
+/* $Id: LocationUsers.php 1 agaluski $*/
+
+include('includes/session.inc');
+$Title = _('Maintenance Of Location Authorised Users');
+include('includes/header.inc');
+
+echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/money_add.png" title="' . _('Location Authorised Users')
+ . '" alt="" />' . ' ' . $Title . '</p>';
+
+if (isset($_POST['SelectedUser'])){
+ $SelectedUser = mb_strtoupper($_POST['SelectedUser']);
+} elseif (isset($_GET['SelectedUser'])){
+ $SelectedUser = mb_strtoupper($_GET['SelectedUser']);
+} else {
+ $SelectedUser='';
+}
+
+if (isset($_POST['SelectedLocation'])){
+ $SelectedLocation = mb_strtoupper($_POST['SelectedLocation']);
+} elseif (isset($_GET['SelectedLocation'])){
+ $SelectedLocation = mb_strtoupper($_GET['SelectedLocation']);
+}
+
+if (isset($_POST['Cancel'])) {
+ unset($SelectedLocation);
+ unset($SelectedUser);
+}
+
+if (isset($_POST['Process'])) {
+ if ($_POST['SelectedLocation'] == '') {
+ echo prnMsg(_('You have not selected any Location'),'error');
+ echo '<br />';
+ unset($SelectedLocation);
+ unset($_POST['SelectedLocation']);
+ }
+}
+
+if (isset($_POST['submit'])) {
+
+ $InputError=0;
+
+ if ($_POST['SelectedUser']=='') {
+ $InputError=1;
+ echo prnMsg(_('You have not selected an user to be authorised to use this Location'),'error');
+ echo '<br />';
+ unset($SelectedLocation);
+ }
+
+ if ( $InputError !=1 ) {
+
+ // First check the user is not being duplicated
+
+ $checkSql = "SELECT count(*)
+ FROM locationusers
+ WHERE loccode= '" . $_POST['SelectedLocation'] . "'
+ AND userid = '" . $_POST['SelectedUser'] . "'";
+
+ $checkresult = DB_query($checkSql,$db);
+ $checkrow = DB_fetch_row($checkresult);
+
+ if ( $checkrow[0] >0) {
+ $InputError = 1;
+ prnMsg( _('The user') . ' ' . $_POST['SelectedUser'] . ' ' ._('already authorised to use this Location'),'error');
+ } else {
+ // Add new record on submit
+ $sql = "INSERT INTO locationusers (loccode,
+ userid,
+ canview,
+ canupd)
+ VALUES ('" . $_POST['SelectedLocation'] . "',
+ '" . $_POST['SelectedUser'] . "',
+ '1',
+ '1')";
+
+ $msg = _('User') . ': ' . $_POST['SelectedUser'].' '._('has been authorised to use') .' '. $_POST['SelectedLocation'] . ' ' . _('Location');
+ $result = DB_query($sql,$db);
+ prnMsg($msg,'success');
+ unset($_POST['SelectedUser']);
+ }
+ }
+} elseif ( isset($_GET['delete']) ) {
+ $sql="DELETE FROM locationusers
+ WHERE loccode='".$SelectedLocation."'
+ AND userid='".$SelectedUser."'";
+
+ $ErrMsg = _('The Location user record could not be deleted because');
+ $result = DB_query($sql,$db,$ErrMsg);
+ prnMsg(_('User').' '. $SelectedUser .' '. _('has been un-authorised to use').' '. $SelectedLocation .' '. _('Location') ,'success');
+ unset($_GET['delete']);
+} elseif ( isset($_GET['toggleupd']) ) {
+ $sql="UPDATE locationusers
+ SET canupd='" . $_GET['toggleupd'] . "'
+ WHERE loccode='".$SelectedLocation."'
+ AND userid='".$SelectedUser."'";
+
+ $ErrMsg = _('The Location user record could not be deleted because');
+ $result = DB_query($sql,$db,$ErrMsg);
+ prnMsg(_('User').' '. $SelectedUser .' '. _('has been un-authorised to update').' '. $SelectedLocation .' '. _('Location') ,'success');
+ unset($_GET['removeupd']);
+}
+
+if (!isset($SelectedLocation)){
+
+/* It could still be the second time the page has been run and a record has been selected for modification - SelectedUser will exist because it was sent with the new call. If its the first time the page has been displayed with no parameters
+then none of the above are true. These will call the same page again and allow update/input or deletion of the records*/
+ echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">';
+ echo '<div>
+ <input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
+ <table class="selection">
+ <tr>
+ <td>' . _('Select Location') . ':</td>
+ <td><select name="SelectedLocation">';
+
+ $SQL = "SELECT loccode,
+ locationname
+ FROM locations";
+
+ $result = DB_query($SQL,$db);
+ echo '<option value="">' . _('Not Yet Selected') . '</option>';
+ while ($myrow = DB_fetch_array($result)) {
+ if (isset($SelectedLocation) and $myrow['loccode']==$SelectedLocation) {
+ echo '<option selected="selected" value="';
+ } else {
+ echo '<option value="';
+ }
+ echo $myrow['loccode'] . '">' . $myrow['loccode'] . ' - ' . $myrow['locationname'] . '</option>';
+
+ } //end while loop
+
+ echo '</select></td></tr>';
+
+ echo '</table>'; // close main table
+ DB_free_result($result);
+
+ echo '<br />
+ <div class="centre">
+ <input type="submit" name="Process" value="' . _('Accept') . '" />
+ <input type="submit" name="Cancel" value="' . _('Cancel') . '" />
+ </div>';
+
+ echo '</div>
+ </form>';
+
+}
+
+//end of ifs and buts!
+if (isset($_POST['process'])OR isset($SelectedLocation)) {
+ $SQLName = "SELECT locationname
+ FROM locations
+ WHERE loccode='" .$SelectedLocation."'";
+ $result = DB_query($SQLName,$db);
+ $myrow = DB_fetch_array($result);
+ $SelectedBankName = $myrow['locationname'];
+
+ echo '<br /><div class="centre"><a href="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">' . _('Authorised users for') . ' ' .$SelectedBankName . ' ' . _('Location') .'</a></div>';
+ echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">';
+ echo '<div>';
+ echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
+
+ echo '<input type="hidden" name="SelectedLocation" value="' . $SelectedLocation . '" />';
+
+ $sql = "SELECT locationusers.userid,
+ canview,
+ canupd,
+ www_users.realname
+ FROM locationusers INNER JOIN www_users
+ ON locationusers.userid=www_users.userid
+ WHERE locationusers.loccode='" . $SelectedLocation . "'
+ ORDER BY locationusers.userid ASC";
+
+ $result = DB_query($sql,$db);
+
+ echo '<br />
+ <table class="selection">';
+ echo '<tr><th colspan="6"><h3>' . _('Authorised users for Location') . ' ' .$SelectedBankName. '</h3></th></tr>';
+ echo '<tr>
+ <th>' . _('User Code') . '</th>
+ <th>' . _('User Name') . '</th>
+ <th>' . _('View') . '</th>
+ <th>' . _('Update') . '</th>
+ </tr>';
+
+$k=0; //row colour counter
+
+while ($myrow = DB_fetch_array($result)) {
+ if ($k==1){
+ echo '<tr class="EvenTableRows">';
+ $k=0;
+ } else {
+ echo '<tr class="OddTableRows">';
+ $k=1;
+ }
+ if ($myrow['canupd'] == 1) {
+ $ToggleText = '<td><a href="%s?SelectedUser=%s&toggleupd=0&SelectedLocation=' . $SelectedLocation . '" onclick="return confirm(\'' . _('Are you sure you wish to remove Update for this user?') . '\');">' . _('Remove Update') . '</a></td>';
+ } else {
+ $ToggleText = '<td><a href="%s?SelectedUser=%s&toggleupd=1&SelectedLocation=' . $SelectedLocation . '" onclick="return confirm(\'' . _('Are you sure you wish to add Update for this user?') . '\');">' . _('Add Update') . '</a></td>';
+ }
+ printf('<td>%s</td>
+ <td>%s</td>
+ <td>%s</td>
+ <td>%s</td>' .
+ $ToggleText . '
+ <td><a href="%s?SelectedUser=%s&delete=yes&SelectedLocation=' . $SelectedLocation . '" onclick="return confirm(\'' . _('Are you sure you wish to un-authorise this user?') . '\');">' . _('Un-authorise') . '</a></td>
+ </tr>',
+ $myrow['userid'],
+ $myrow['realname'],
+ $myrow['canview'],
+ $myrow['canupd'],
+ htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8'),
+ $myrow['userid'],
+ htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8'),
+ $myrow['userid']);
+ }
+ //END WHILE LIST LOOP
+ echo '</table>';
+
+ if (! isset($_GET['delete'])) {
+
+
+ echo '<br /><table class="selection">'; //Main table
+
+ echo '<tr>
+ <td>' . _('Select User') . ':</td>
+ <td><select name="SelectedUser">';
+
+ $SQL = "SELECT userid,
+ realname
+ FROM www_users";
+
+ $result = DB_query($SQL,$db);
+ if (!isset($_POST['SelectedUser'])){
+ echo '<option selected="selected" value="">' . _('Not Yet Selected') . '</option>';
+ }
+ while ($myrow = DB_fetch_array($result)) {
+ if (isset($_POST['SelectedUser']) AND $myrow['userid']==$_POST['SelectedUser']) {
+ echo '<option selected="selected" value="';
+ } else {
+ echo '<option value="';
+ }
+ echo $myrow['userid'] . '">' . $myrow['userid'] . ' - ' . $myrow['realname'] . '</option>';
+
+ } //end while loop
+
+ echo '</select></td></tr>';
+
+ echo '</table>'; // close main table
+ DB_free_result($result);
+
+ echo '<br /><div class="centre"><input type="submit" name="submit" value="' . _('Accept') . '" />
+ <input type="submit" name="Cancel" value="' . _('Cancel') . '" /></div>';
+
+ echo '</div>
+ </form>';
+
+ } // end if user wish to delete
+}
+
+include('includes/footer.inc');
+?>
Modified: trunk/Locations.php
===================================================================
--- trunk/Locations.php 2014-08-04 20:16:08 UTC (rev 6803)
+++ trunk/Locations.php 2014-08-08 13:44:43 UTC (rev 6804)
@@ -175,8 +175,25 @@
$ErrMsg = _('An error occurred inserting the new location stock records for all pre-existing parts because');
$DbgMsg = _('The SQL used to insert the new stock location records was');
$result = DB_query($sql,$db,$ErrMsg, $DbgMsg);
+ prnMsg ('........ ' . _('and new stock locations inserted for all existing stock items for the new location'), 'success');
+
+ /* Also need to add locationuser records for all existing users*/
+ $sql = "INSERT INTO locationusers (userid, loccode, canview, canupd)
+ SELECT www_users.userid,
+ locations.loccode,
+ 1,
+ 1
+ FROM www_users CROSS JOIN locations
+ LEFT JOIN locationusers
+ ON www_users.userid = locationusers.userid
+ AND locations.loccode = locationusers.loccode
+ WHERE locationusers.userid IS NULL
+ AND locations.loccode='". $_POST['LocCode'] . "';";
- prnMsg ('........ ' . _('and new stock locations inserted for all existing stock items for the new location'), 'success');
+ $ErrMsg = _('The users/locations that need user location records created cannot be retrieved because');
+ $Result = DB_query($sql,$db,$ErrMsg);
+ prnMsg(_('Existing users have been authorized for this location'),'success');
+
unset($_POST['LocCode']);
unset($_POST['LocationName']);
unset($_POST['DelAdd1']);
@@ -346,6 +363,7 @@
$result= DB_query("DELETE FROM locstock WHERE loccode ='" . $SelectedLocation . "'",$db);
$result = DB_query("DELETE FROM locations WHERE loccode='" . $SelectedLocation . "'",$db);
+ $result = DB_query("DELETE FROM locationusers WHERE loccode='" . $SelectedLocation . "'",$db);
prnMsg( _('Location') . ' ' . $SelectedLocation . ' ' . _('has been deleted') . '!', 'success');
unset ($SelectedLocation);
Added: trunk/Z_MakeLocUsers.php
===================================================================
--- trunk/Z_MakeLocUsers.php (rev 0)
+++ trunk/Z_MakeLocUsers.php 2014-08-08 13:44:43 UTC (rev 6804)
@@ -0,0 +1,29 @@
+<?php
+/* $Id: Z_MakeLocUsers.php 1 agaluski $*/
+/* Script to make user locations for all users that do not have user location records set up*/
+
+include ('includes/session.inc');
+$Title = _('Make locationusers Records');
+include('includes/header.inc');
+
+echo '<br /><br />' . _('This script makes stock location records for parts where they do not already exist');
+
+$sql = "INSERT INTO locationusers (userid, loccode, canview, canupd)
+ SELECT www_users.userid,
+ locations.loccode,
+ 1,
+ 1
+ FROM www_users CROSS JOIN locations
+ LEFT JOIN locationusers
+ ON www_users.userid = locationusers.userid
+ AND locations.loccode = locationusers.loccode
+ WHERE locationusers.userid IS NULL;";
+
+$ErrMsg = _('The users/locations that need user location records created cannot be retrieved because');
+$Result = DB_query($sql,$db,$ErrMsg);
+
+echo '<p />';
+prnMsg(_('Any users that may not have had user location records have now been given new location user records'),'info');
+
+include('includes/footer.inc');
+?>
Modified: trunk/doc/Change.log
===================================================================
--- trunk/doc/Change.log 2014-08-04 20:16:08 UTC (rev 6803)
+++ trunk/doc/Change.log 2014-08-08 13:44:43 UTC (rev 6804)
@@ -1,6 +1,6 @@
webERP Change Log
-
+08/08/14 Andrew Galuski: Add SQL and maintenance screens for Location based security. added new report aged controlled stock. additional scripts to follow as time allows
2/1/14 Tim: Change columns around on SelectWorkOrder.php
31/07/14 RChacon: Corrects the bottom line of the rectangle. Adds comments (info for developers).
29/07/14 RChacon: In PDFPriceList.php: Adds comments (info for developers), ViewTopic and BookMark, and currency name in locale language; deletes unused lines; reformats for legibility; adjusts column sizes to field sizes; improves printing of stockmaster.longdescription; improves code to reduce execution time (calculation out of loops); links right column positions to right margin; corrects IF for CustomerSpecials (deletes translation). In ManualSalesTypes.html: Adds help info about Print a price list by inventory category.
Modified: trunk/includes/MainMenuLinksArray.php
===================================================================
--- trunk/includes/MainMenuLinksArray.php 2014-08-04 20:16:08 UTC (rev 6803)
+++ trunk/includes/MainMenuLinksArray.php 2014-08-08 13:44:43 UTC (rev 6804)
@@ -237,7 +237,8 @@
_('Historical Stock Quantity By Location/Category'),
_('List Negative Stocks'),
_('Period Stock Transaction Listing'),
- _('Stock Transfer Note'));
+ _('Stock Transfer Note'),
+ _('Aged Controlled Stock Report'));
$MenuItems['stock']['Reports']['URL'] = array( '/StockSerialItemResearch.php',
'/PDFPrintLabel.php',
@@ -260,7 +261,8 @@
'/StockQuantityByDate.php',
'/PDFStockNegatives.php',
'/PDFPeriodStockTransListing.php',
- '/PDFStockTransfer.php');
+ '/PDFStockTransfer.php',
+ '/AgedControlledInventory.php');
$MenuItems['stock']['Maintenance']['Caption'] = array( _('Add A New Item'),
_('Select An Item'),
@@ -502,6 +504,7 @@
$MenuItems['system']['Maintenance']['Caption'] = array( _('Inventory Categories Maintenance'),
_('Inventory Locations Maintenance'),
+ _('Inventory Location Authorized Users Maintenance'),
_('Discount Category Maintenance'),
_('Units of Measure'),
_('MRP Available Production Days'),
@@ -512,6 +515,7 @@
$MenuItems['system']['Maintenance']['URL'] = array( '/StockCategories.php',
'/Locations.php',
+ '/LocationUsers.php',
'/DiscountCategories.php',
'/UnitsOfMeasure.php',
'/MRPCalendar.php',
Modified: trunk/sql/mysql/upgrade4.11-4.12.sql
===================================================================
--- trunk/sql/mysql/upgrade4.11-4.12.sql 2014-08-04 20:16:08 UTC (rev 6803)
+++ trunk/sql/mysql/upgrade4.11-4.12.sql 2014-08-08 13:44:43 UTC (rev 6804)
@@ -54,6 +54,27 @@
ALTER TABLE stockrequestitems DROP KEY stockid_2, DROP KEY dispatchid_2;
INSERT INTO scripts VALUES('Dashboard.php',1,'Display outstanding debtors, creditors etc');
+INSERT INTO `scripts` ( `script` , `pagesecurity` , `description` ) VALUES ('Z_MakeLocUsers.php', '15', 'Create User Location records');
+INSERT INTO `scripts` ( `script` , `pagesecurity` , `description` ) VALUES ('LocationUsers.php', '15', 'User Location Maintenance');
+INSERT INTO `scripts` ( `script` , `pagesecurity` , `description` ) VALUES ('AgedControlledInventory.php', '11', 'Report of Controlled Items and their age');
+
+CREATE TABLE IF NOT EXISTS `locationusers` (
+ `loccode` varchar(5) NOT NULL,
+ `userid` varchar(20) NOT NULL,
+ `canview` tinyint(4) NOT NULL DEFAULT '0',
+ `canupd` tinyint(4) NOT NULL DEFAULT '0',
+ PRIMARY KEY (`loccode`,`userid`),
+ KEY `UserId` (`userid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO locationusers (userid, loccode, canview, canupd)
+ SELECT www_users.userid, locations.loccode,1,1
+ FROM www_users CROSS JOIN locations
+ LEFT JOIN locationusers
+ ON www_users.userid = locationusers.userid
+ AND locations.loccode = locationusers.loccode
+ WHERE locationusers.userid IS NULL;
+
UPDATE config SET confvalue='4.12' WHERE confname='VersionNumber';
|
