How to read entity 'warehouse' property

[emre]

I really didn’t meant to use POS for purchases because I don’t want to add purchase specific features there. I was planning to create a separate entry screen that we can choose entities.

The reason I give up with that is I’m still considering to change tickets to generate inventory transactions automatically like we did for account transactions. So we don’t need to calculate inventory on the fly but if I do that it will need a infrastructural change and I’m not sure if my future changes will break your current implementations or not.

The reason we don’t create inventory transactions and calculate inventory on the fly is allowing user to update physical inventory levels on end of day records. If we create inventory transactions it will be still possible by configuring additional transaction types but won’t be a simple task as it completely changes how we calculate cost.

I don’t want to bother you with such details but I thought it will be better if you know my future plans.

[pipo]

@emre

I really didn’t meant to use POS for purchases

I wasn’t immediately thinking to make purchases this way (although it had crossed my mind). That would make things way more complicated, because then I would have to have a separate menu (and ticket type etc) for each supplier, and taking into account differences in price each time you buy, and probably even more than that…

Right now I just want to use it to move inventory and add inventory (for half-products and repackaging). Using the same way to purchase inventory seems a bit too complicated to use the POS menu for.

@emre

The reason I give up with that is I’m still considering to change tickets to generate inventory transactions automatically like we did for account transactions. So we don’t need to calculate inventory on the fly but if I do that it will need a infrastructural change and I’m not sure if my future changes will break your current implementations or not.

I think it wouldn’t, as the script only creates inventory transactions and inventory transaction documents. Or would you need to make changes to how these inventory transactions work?
Even in that case, it would be only a matter of changing the SQL queries, no?

[pipo]

@QMcKay, in case you want to try the batch file:

• Create the SQL files “NewInventoryItemTransactionDocument.sql” and “NewInventoryTransaction.sql” (the sql i showed you before).
• Change the WorkPath variable to wherever you put the SQL files and the batch file.
• Change SName variable to the name of your sql server
• Change DbName to the name of the SambaPOS database
• If you are using trusted authentication for your database, you need to change the ConnectionString variable, and just delete the part that goes " -U %UserName% -P %Password%" (I think).

[QMcKay]

@pipo

Can you not use separate “.sql” files and parameters for your queries in PHP?

Probably. I didn’t know you could pass variables into a SQL script, that’s why I build it line-by-line. Actually, I build it in Management Studio Query editor, then paste it into the PHP file.

@pipo

If you can’t, maybe executing sqlcmd in PHP is another option?

That wouldn’t be necessary, there is an extension to execute MSSQL queries:

To connect and select DB:

$connectionInfo = array("UID"=>$mssql_dbuser,
                        "PWD"=>$mssql_dbpass,
                        "Database"=>$mssql_dbname, "ReturnDatesAsStrings"=>true);

$mssql_dbresource = sqlsrv_connect( $mssql_dbhost, $connectionInfo);

To Execute a query:

$queryresult = sqlsrv_query($conn, $inqry, array(), array(“Scrollable” => SQLSRV_CURSOR_STATIC));

[pipo]

@QMcKay

$queryresult = sqlsrv_query($conn, $inqry, array(), array(“Scrollable” => SQLSRV_CURSOR_STATIC));

The third parameter of sqlsrv_query (“array()”) should hold the query parameters.
And the $inqry parameter you can just load from the “.sql” file…

Then you only need to fill in the parameter array in PHP.

[QMcKay]

Absolutely. This is from the documentation…

sqlsrv_query( resource $conn, string $tsql [, array $params [, array $options]])

---

/* Define the Transact-SQL query.
Use question marks as parameter placeholders. */
$tsql1 = “UPDATE Production.ProductInventory
SET Quantity = ?
WHERE ProductID = ?”;

/* Initialize $qty and $productId */
$qty = 10; $productId = 709;

/* Execute the statement with the specified parameter values. */
$stmt1 = sqlsrv_query( $conn, $tsql1, array($qty, $productId));

---

$tsql2 = “SELECT Quantity
FROM Production.ProductInventory
WHERE ProductID = ?”;

/* Execute the statement with the specified parameter value.
Display the returned data if no errors occur. */
$stmt2 = sqlsrv_query( $conn, $tsql2, array($productId));

EDIT: sorry for hijacking your thread

[pipo]

@QMcKay

sorry for hijacking your thread

That’s all right, it was pretty much done anyway

[emre]

As we read inventory transactions from order’s WarehouseId value you should also use that value to determine ticket’s warehouse. It reads warehouse from department but maybe I can add Changing Warehouse feature to Update Order action. Does it works?

[pipo]

Although it wouldn’t help me at the moment since I’m still stuck to V3, that would be a good update, since departments can have separate tickets and menus which shouldn’t necessarily be dealing with one single warehouse.
Maybe a better way to handle this would be to link a warehouse to ticket types and reading the order’s warehouse from the ticket type? Also adding the ‘Change Warehouse’ parameter in Update Order would make it even more flexible of course for specific cases.

[emre]

If you think about for what cases you create new departments and ticket types you’ll quickly see configuring warehouses through ticket type is not a good idea

[pipo]

I’ve been configuring departments and ticket types for all kinds of purposes. Why wouldn’t it be a good idea to link warehouses to ticket types?
Maybe it’s just too late and I’m very tired (it’s 6.20 in the morning here), but I don’t get what you mean…

[emre]

I mean… for example if you create a “Refund Ticket” type you should configure one for each warehouses.

Think for I have Bar, Restaurant departments and related warehouses.

[pipo]

I don’t know if you misunderstood what I meant or it’s the other way around.
What I meant is to have a ‘warehouse’ field in the ticket type, so that when you create the orders for this type of ticket, you check the ticket type’s warehouse instead of the department’s and put that in the order.
I’m not sure how a refund ticket works, but why would it have anything to do with the warehouse? Refunds don’t update any inventory, or do they?

[QMcKay]

A refund could update inventory counts, if the item is returned unused, unopened, undamaged, etc. In that case, the item could be placed back in stock.

[pipo]

I see.
In that case wouldn’t the refund ticket just be copied from the original ticket (and using the same warehouse for each order)? What could go wrong with that?

This is where I’m getting confused:

@emre

for example if you create a “Refund Ticket” type you should configure one for each warehouses.

Why would you need to make a new refund ticket type for each warehouse just because the ticket type has a ‘warehouse’ property (used when adding orders to that type of ticket)? Or would it have to be used for something else as well were it could cause problems?
 
 
This is how I see it, but I might be missing something obvious that you guys are seeing clearly:

• select a warehouse in the ticket type (or leave it empty, then it still uses current department’s warehouse just like before)
• when orders added to a ticket: the order’s ‘warehouse’ gets copied from the ticket type (or the department’s if the ticket type’s warehouse is empty)
• you can still update the order’s warehouse from a rule if you want for some reason (with Update Order).
• when you create a refund ticket, you use the same warehouse that was already in the order (if the inventory was deducted from that warehouse, then you should add it to the same warehouse again)

[Jesse]

@pipo

or leave it empty, then it still uses current department’s warehouse just like before

I can see how this might work… but that would allow us to make a very complex system with tickets which personally I can see advantages for…BUT I can see how potentially it could really screw it all up.

For some of us this would not be an issue but for an average user or a new user to SambaPOS this could royally mess the entire integrity of the system up if someone was to assign warehouse type to a ticket type not knowing how it might interact with their inventory if they use that ticket for other departments, refunds, etc.

I can’t see how most people would use this option though. A few of us might, I know I could, but average users would not need it and it adds a very big complexity to the system. So I can see why leaving Warehouse to just departments is a good approach.

[pipo]

I see your point. That could be an issue.

On the other hand, it wouldn’t be the only setting in SambaPOS that could screw things up when not used correctly (like unit multipliers for example ).

[Jesse]

True but unit multipliers is a necessary evil. This we could say really is not. Don’t get me wrong me personally I would love having Warehouse assigned to ticket type. I can think of a ton of things I could do with it. Maybe we need a SambaPOSv4(ADVANCED INSTALL) hahah or SambaPOSv4 (PROFESSIONAL) With disclaimer that you need a good knowledge of it before installing haha.

[pipo]

Yes, or a setting ‘show advanced options’ or something… That would be something that would probably be good for a lot of new users.

[pipo]

The more advanced and configurable SambaPOS becomes, the more confusing it will become for starters, so that would probably be a very good setting to have…