Sir, Thanks you very much for reply, please have the code belowPlease note the below information to understand the code
Inventorybook and InventorybookItems are main table to store totals incomming stock they are linked.
InventoryBook store cleint information and inventorybookItem store incoming stcok.
OinventoryBook and oinventrybookitems are as above to store outgoing stock.
What I am doing ; When outgoing stock is added in oinventoryitem, it should select the total values of outgoing itmes and submit to oinventorybook first and pass the common values to each other in below code like eployee id, client id etc.Than it update the incomed item balance in inventrybookitems with CODE(1-4) and
than
Update the totals incomed item stock value in Main inventoryBook table. with CODE (1-5)
in main inventorybook table CODE(1-5), there is a condition to check if the outgoing stcok days are less then 7 days from the incoming stock date minus the total outgoing weight from Total available weight.All code works, but when I apply 7 day condition then it error for
…PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
function Row_Inserted($rsold, &$rsnew)
{
// CODE starts here
//Get Inventry and refrence of stock iD from master table
$oitemFkey = $rsnew["odInventryFKEY"]; Outbound inventory main key
$MainFkey = $rsnew["odMInvtryFKEY"]; // Main inventorybook key
$pdid = $rsnew["oInbProductKey"]; // Main inventroy item table product key
$arref = $rsnew["odArrivalRefrence"]; //Arrival refrence common for all table
// Submit arrival refrence to oinventoryboo
$updarref = ExecuteStatement("UPDATE oinventorybook SET oArrivalRefrence='".$arref."' Where OINCID='$oitemFkey'");
//It retrive the field of outbound master table and assign to outbound item table (Code 1-A)
$omsterID = ExecuteRow("SELECT oRecievedDate, oClientID, oInventryID, oInventryType, oFinancialYear,
oDateYear, oDateMonth, oWarehouseID, oEmployeeID FROM oinventorybook WHERE OINCID='$oitemFkey'");
// CODE(1-A) Assign ountbound master inventry to all records in "oinventorybookitems" table
$ominid = ExecuteStatement("UPDATE oinventrybookitems SET
odRecievedDate='".$omsterID["oRecievedDate"]."',
odClientID='".$omsterID["oClientID"]."',
odInventryID='".$omsterID["oInventryID"]."',
odInventryType = '".$omsterID["oInventryType"]."',
odFinancialYear = '".$omsterID["oFinancialYear"]."',
odDateYear = '".$omsterID["oDateYear"]."',
odDateMonth = '".$omsterID["oDateMonth"]."',
odWarehouseID = '".$omsterID["oWarehouseID"]."',
odEmployeeID = '".$omsterID["oEmployeeID"]."'
WHERE odInventryFKEY='$oitemFkey'");
// CODE(1-B) Get Sum of all values from oinventorybookitem and send it to oinventorybook in CODE(3)
// Slect total from oinventorybookitems
$ogetTotals = ExecuteRow("SELECT
SUM(odTotalOutboundCase) AS TotalCaseOutbound,
SUM(odTotalOutboundPcs) AS TotalPCSOutbound,
SUM(odTotaOutboundlFootage) AS TotalFootageOutbound,
SUM(odTotalOutboundWeight) AS TotalWeightOutbound
FROM oinventrybookitems WHERE odInventryFKEY='$oitemFkey'");
// code(2-B) assign values of (1-B)
$addNewtotals = ExecuteStatement("UPDATE oinventorybook SET
oTotalOutboundCases='".$ogetTotals["TotalCaseOutbound"]."',
oTotalOutboundPcs = '".$ogetTotals["TotalPCSOutbound"]."',
oTotalOutboundFootage = '".$ogetTotals["TotalFootageOutbound"]."',
oTotalOutboundWeight = '".$ogetTotals["TotalWeightOutbound"]."'
WHERE OINCID='$oitemFkey'");
// CODE(1-4) Update available stcok/inventry in main inventrybookitem table. Minus the outbound stock
// Select values from inventrybookitems
$getAvailablevalue = ExecuteRow("SELECT
dAvailablelBalanceOfCasesInStock,
dAvailableBalanceOfPcsInStock,
dAvailableBalanceOfFootageInStock,
dAvailableBalanceOfWeightInStock
FROM inventrybookitems WHERE INCDID='$pdid'");
$uitembalance = ExecuteStatement("UPDATE inventrybookitems SET
dAvailablelBalanceOfCasesInStock ='".$getAvailablevalue["dAvailablelBalanceOfCasesInStock"]."' - '".$rsnew["odTotalOutboundCase"]."',
dAvailableBalanceOfPcsInStock='".$getAvailablevalue["dAvailableBalanceOfPcsInStock"]."' - '".$rsnew["odTotalOutboundPcs"]."',
dAvailableBalanceOfFootageInStock='".$getAvailablevalue["dAvailableBalanceOfFootageInStock"] ."' - '". $rsnew["odTotaOutboundlFootage"]."',
dAvailableBalanceOfWeightInStock = '".$getAvailablevalue["dAvailableBalanceOfWeightInStock"] ."' - '". $rsnew["odTotalOutboundWeight"]."',
dCurrentStatus = 'In-Service'
WHERE INCDID='$pdid'");
// CODE(1-4) ends
// CODE (1-5) Update main inventrybook table values , Outbound and available
// Select values from inventrybookitems
$getNewAvailablevalue = ExecuteRow("SELECT
SUM(dAvailablelBalanceOfCasesInStock) AS NewAvailableCase,
SUM(dAvailableBalanceOfPcsInStock) AS NewAvailablePcs,
SUM(dAvailableBalanceOfFootageInStock) AS NewAvailableFootage,
SUM(dAvailableBalanceOfWeightInStock) AS NewAvailableWt
FROM inventrybookitems WHERE dInventryFKEY='$MainFkey'");
// Check if outbound days are less then seven
// Get current entry date
$ocdt = $omsterID["oRecievedDate"];
$daycheck = ExecuteStatement("Select iRecievedDate, iTotalInvoicingWeight from inventorybook
where INCID='$MainFkey'");
$indt = $daycheck["iRecievedDate"];
$indate = strtotime($indt);
$indater = strtotime("+7 day", $indate);
if($ocdt > $indater) {
// Update main inventory values in inventorybook
$updbalancemain = ExecuteStatement("UPDATE inventorybook SET
iTotalOutboundCases ='".$ogetTotals["TotalCaseOutbound"]."',
iAvailableBalanceOfCases='".$getNewAvailablevalue["NewAvailableCase"]."',
iTotalOutboundPcs='". $ogetTotals["TotalPCSOutbound"]."',
iAvailableBalanceOfPcs = '".$getNewAvailablevalue["NewAvailablePcs"]."',
iTotalOutboundWeight = '".$ogetTotals["TotalWeightOutbound"]."',
iAvailableBalanceOfWeight = '".$getNewAvailablevalue["NewAvailableWt"]."',
iTotalOutboundFootage = '".$ogetTotals["TotalFootageOutbound"]."',
iAvailableBalanceFootage = '".$getNewAvailablevalue["NewAvailableFootage"]."'
WHERE INCID='$MainFkey'");
} else {
// Update main inventory values in inventorybook
$preCostlesswt = $gettvmain["iTotalCostlessWeight"];
$preInvoicewt = $gettvmain["iTotalInvoicingWeight"];
$newCostlesswt = $preCostlesswt + $rsnew["odTotalOutboundWeight"];
$minusFromInvwt = $preInvoicewt - $newCostlesswt;
$updbalancemain = ExecuteStatement("UPDATE inventorybook SET
iTotalOutboundCases ='".$ogetTotals["TotalCaseOutbound"]."',
iAvailableBalanceOfCases='".$getNewAvailablevalue["NewAvailableCase"]."',
iTotalOutboundPcs='". $ogetTotals["TotalPCSOutbound"]."',
iAvailableBalanceOfPcs = '".$getNewAvailablevalue["NewAvailablePcs"]."',
iTotalOutboundWeight = '".$ogetTotals["TotalWeightOutbound"]."',
iAvailableBalanceOfWeight = '".$getNewAvailablevalue["NewAvailableWt"]."',
iTotalOutboundFootage = '".$ogetTotals["TotalFootageOutbound"]."',
iAvailableBalanceFootage = '".$getNewAvailablevalue["NewAvailableFootage"]."',
iTotalInvoicingWeight = '".$minusFromInvwt."',
iTotalCostlessWeight = '".$newCostlesswt."'
WHERE INCID='$MainFkey'");
}
}