Quantity error

[Sikander Nasar]

I am facing the problem that in my inventory my Physical quantity not matching to my available quantity.I have color option and If i select 10 products it minus 10 products from all the colors.this is my code

<?
    /*********************************************************************************************\
    ***********************************************************************************************
    **                                                                                           **
    **  My Shop                                                                                  **
    **  Version 1.0                                                                              **
    **                                                                                           **
    **  http://www.thesouq.com                                                                   **
    **                                                                                           **
    **  Copyright 2005-14 (C) SW3 Solutions                                                      **
    **  http://www.sw3solutions.com                                                              **
    **                                                                                           **
    **  ***************************************************************************************  **
    **                                                                                           **
    **  Project Manager:                                                                         **
    **                                                                                           **
    **      Name  :  Muhammad Sikander Nasar                                                     **
    **      Email :  [e-mail]                                                   **
    **      Phone :  [phone]                                                            **
    **      URL   :  http://www.mtshahzad.com                                                    **
    **                                                                                           **
    ***********************************************************************************************
    \*********************************************************************************************/

    @require_once("../requires/common.php");

    $objDbGlobal = new Database( );
    $objDb       = new Database( );
    $objDb2      = new Database( );
    $objDb3      = new Database( );



    $sAttributeOption = getList("tbl_product_attribute_options o, tbl_product_attribute_options_data od", "o.id", "od.`option`", "o.id=od.option_id AND od.language_id='{$_SESSION['Language']}'");
    $sBrands          = getList("tbl_brands b, tbl_brands_data bd", "b.id", "bd.name", "b.id=bd.brand_id AND bd.language_id='{$_SESSION['Language']}'");
    $sProductTypes    = getList("tbl_product_types t, tbl_product_types_data td", "t.id", "td.title", "t.id=td.type_id AND td.language_id='{$_SESSION['Language']}'");
    $sCountries       = getList("tbl_countries", "id", "name");
    $sCategories      = array( );



    $sSQL = "SELECT c.id, cd.name
             FROM tbl_categories c, tbl_categories_data cd
             WHERE c.id=cd.category_id AND c.parent_id='0' AND cd.language_id='{$_SESSION['Language']}'
             ORDER BY cd.name";
    $objDb->query($sSQL);

    $iCount = $objDb->getCount( );

    for ($i = 0; $i < $iCount; $i ++)
    {
        $iParentId = $objDb->getField($i, "id");
        $sParent   = $objDb->getField($i, "name");

        $sCategories[$iParentId] = $sParent;


        $sSQL = "SELECT c.id, cd.name
                 FROM tbl_categories c, tbl_categories_data cd
                 WHERE c.id=cd.category_id AND c.parent_id='$iParentId' AND cd.language_id='{$_SESSION['Language']}'
                 ORDER BY cd.name";
        $objDb2->query($sSQL);

        $iCount2 = $objDb2->getCount( );

        for ($j = 0; $j < $iCount2; $j ++)
        {
            $iCategoryId = $objDb2->getField($j, "id");
            $sCategory   = $objDb2->getField($j, "name");

            $sCategories[$iCategoryId] = ($sParent." > ".$sCategory);


            $sSQL = "SELECT c.id, cd.name
                     FROM tbl_categories c, tbl_categories_data cd
                     WHERE c.id=cd.category_id AND c.parent_id='$iCategoryId' AND cd.language_id='{$_SESSION['Language']}'
                     ORDER BY cd.name";
            $objDb3->query($sSQL);

            $iCount3 = $objDb3->getCount( );

            for ($k = 0; $k < $iCount3; $k ++)
            {
                $iSubCategoryId = $objDb3->getField($k, "id");
                $sSubCategory   = $objDb3->getField($k, "name");

                $sCategories[$iSubCategoryId] = ($sParent." > ".$sCategory." > ".$sSubCategory);
            }
        }
    }
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>
<?
    @include("{$sAdminDir}includes/meta-tags.php");
?>

  <script type="text/javascript" src="scripts/<?= $sCurDir ?>/inventory.js"></script>
</head>

<body>

<div id="MainDiv">

<!--  Header Section Starts Here  -->
<?
    @include("{$sAdminDir}includes/header.php");
?>
<!--  Header Section Ends Here  -->


<!--  Navigation Section Starts Here  -->
<?
    @include("{$sAdminDir}includes/navigation.php");
?>
<!--  Navigation Section Ends Here  -->


<!--  Body Section Starts Here  -->
  <div id="Body">
<?
    @include("{$sAdminDir}includes/breadcrumb.php");
?>

    <div id="Contents">
<?
    @include("{$sAdminDir}includes/messages.php");


    $sSQL = "SELECT p.id AS _ProductId, p.type_id, p.brand_id, p.category_id, p.country_id, pd.name AS _ProductName, pd.details, p.price, p.`code` AS _Code, p.manufacturer_part, po.option_id AS _OptionId, po.option2_id AS _Option2Id, po.price AS _Price, po.quantity AS _Quantity, po.sku AS _Sku
             FROM tbl_products p, tbl_products_data pd, tbl_product_options po, tbl_product_attribute_options pao, tbl_product_type_details ptd
             WHERE p.id=pd.product_id AND p.id=po.product_id AND po.option_id=pao.id AND pao.attribute_id=ptd.attribute_id AND p.type_id=ptd.type_id AND ISNULL(po.description) AND ptd.`key`='Y' AND pd.language_id='{$_SESSION['Language']}'

             UNION

             SELECT p.id AS _ProductId, p.type_id, p.brand_id, p.category_id, p.country_id, pd.name AS _ProductName, pd.details, p.price, p.`code` AS _Code, p.manufacturer_part, '' AS _OptionId, '' AS _Option2Id, '' AS _Price, p.quantity AS _Quantity, p.sku AS _Sku
             FROM tbl_products p, tbl_products_data pd
             WHERE p.id=pd.product_id AND pd.language_id='{$_SESSION['Language']}' AND ((SELECT COUNT(*) FROM tbl_product_type_details WHERE type_id=p.type_id AND `key`='Y') = 0)
             ORDER BY _ProductId ASC";
    $objDb->query($sSQL);

    $iTotalRecords = $objDb->getCount( );
?>

      <div id="PageTabs">
        <ul>
          <li><a href="<?= $_SERVER['REQUEST_URI'] ?>#tabs-1"><b>Product Inventory</b></a></li>

        </ul>


        <div id="tabs-1">
          <div id="GridMsg" class="hidden"></div>

          <div>
            <form id="frmExport" name="frmExport" method="post"  action="<?= (SITE_URL.ADMIN_CP_DIR) ?>/<?= $sCurDir ?>/export-inventory.php" class="fRight" style="margin-left:8px;">
              <input type="hidden" name="Records" id="Records" value="<?= $iTotalRecords ?>" />
              <input type="hidden" name="ExportCategory" id="ExportCategory" value=""  />
              <input type="hidden" name="ExportBrand" id="ExportBrand" value=""  />
              <input type="hidden" name="ExportType" id="ExportType" value=""  />
              <input type="hidden" name="ExportQuantity" id="ExportQuantity" value=""  />

              <button id="BtnExport">Export</button>
            </form>

<?
    if ($sUserRights["Add"] == "Y" && $sUserRights["Edit"] == "Y")
    {
?>
            <button id="BtnImport" class="fRight">Import</button>
<?
    }
?>
            <div class="br5"></div>
          </div>

          <br/>


          <div class="dataGrid ex_highlight_row">
            <input type="hidden" id="TotalRecords" value="<?= $iTotalRecords ?>" />
            <input type="hidden" id="RecordsPerPage" value="<?= $_SESSION["PageRecords"] ?>" />

            <table width="100%" border="0" cellpadding="0" cellspacing="0" class="tblData" id="DataGrid">
              <thead>
                <tr>
                  <th width="2%">#</th>
                  <th width="5%">Product Code</th>
                  <th width="5%">SKU</th>
                  <th width="11%">Name</th>
                  <th width="11%">Details</th>
                  <th width="8%">Type</th>
                  <th width="10%">Category</th>
                  <th width="8%">Brand</th>
                  <th width="4%">Key 1</th>
                  <th width="4%">Key 2</th>
                  <th width="4%">Price</th>
                  <th width="6%">Physical Quantity</th>
                  <th width="7%">Available Quantity</th>
                  <th width="6%">Country</th>
                  <th width="9%">Manufacturer's Part Number</th>
                </tr>
              </thead>

              <tbody>
<?
    if ($iTotalRecords <= 100)
    {
        for ($i = 0; $i < $iTotalRecords; $i ++)
        {
            $iId               = $objDb->getField($i, "_ProductId");
            $iType             = $objDb->getField($i, "type_id");
            $iCategory         = $objDb->getField($i, "category_id");
            $iCountry          = $objDb->getField($i, "country_id");
            $iBrand            = $objDb->getField($i, "brand_id");
            $sDetails          = $objDb->getField($i, "details");
            $sName             = $objDb->getField($i, "_ProductName");
            $sCode             = $objDb->getField($i, "_Code");
            $sManufacturerPart = $objDb->getField($i, "manufacturer_part");
            $fPrice            = ($objDb->getField($i, "price") + $objDb->getField($i, "_Price"));
            $iOptionId         = $objDb->getField($i, "_OptionId");
            $iOption2Id        = $objDb->getField($i, "_Option2Id");
            $sSKU              = $objDb->getField($i, "_Sku");
            $iQuantity         = $objDb->getField($i, "_Quantity");
?>
                <tr id="<?= $iId ?>" valign="top">
                  <td class="position"><?= ($i + 1) ?></td>
                  <td><?= $sCode ?></td>
                  <td><?= $sSKU ?></td>
                  <td><a href="<?= $sCurDir ?>/view-product.php?ProductId=<?= $iId ?>" class="details"><?= $sName ?></a></td>
                  <td><?= substr(strip_tags($sDetails), 0, 100) ?></td>
                  <td><?= $sProductTypes[$iType] ?></td>
                  <td><?= $sCategories[$iCategory] ?></td>
                  <td><?= $sBrands[$iBrand] ?></td>
                  <td><?= $sAttributeOption[$iOptionId] ?></td>
                  <td><?= $sAttributeOption[$iOption2Id] ?></td>
                  <td><?= (($iCountry == 222) ? "AED " : (($iCountry == "174") ? "QAR " : "")).formatNumber($fPrice) ?></td>
                  //this is the main line in which changes require
                  <td><?= ($iQuantity + getDbValue("SUM(quantity)", "tbl_order_details", "product_id='$iId' AND order_id IN (SELECT id FROM tbl_orders WHERE FIND_IN_SET(shippment_status, 'P,C,R'))")) ?></td>
                  <td><?= $iQuantity  ?></td>
                  <td><?= $sCountries[$iCountry] ?></td>
                  <td><?= $sManufacturerPart ?></td>
                </tr>
<?
        }
    }
?>
              </tbody>
            </table>
          </div>
        </div>
      </div>
    </div>
  </div>
<!--  Body Section Ends Here  -->


<!--  Footer Section Starts Here  -->
<?
    @include("{$sAdminDir}includes/footer.php");
?>
<!--  Footer Section Ends Here  -->

</div>

</body>
</html>
<?
    $objDb->close( );
    $objDb2->close( );
    $objDb3->close( );
    $objDbGlobal->close( );

    @ob_end_flush( );
?>

[pritaeas]

So where's your update code/query?

[Sikander Nasar]

I have two files.
1:get_inventory.php
2:inventory.php
and this is inventory.php

[itisnot_me]

The only thing that I can think of is that

1. the DB has the incorrect number, in which case its the updating file that has the issue

2. line 243 should be a - instead of a +. I can only assume that you have the quantity of product then you are subtracting the products that are shipped or going to be shipped? I can't really tell as you then have $iQuantity on the next line. If this is the case then I would not do it like this. I would have the quantity of the product actually get - when and order is complete.

Let me know if I am even close. lol

[Sikander Nasar]

thanks itisnot.I have tried it before posting ,In this case it work opposite but physical quantities and available quantities have problem.if the product is in pending state it also minus from the available quantitiesit have to minus the physical quantities when it is authorized.why it i s include the products ,when it is in pending state.sorry for my poor english

[itisnot_me]

You do not want to keep your real physical products only in the web application. You will need to keep some other records, like your bookkeeping, for safe measure.

From my exprience with selling products on the web applications it always, no matter what, reduces the inventory of the product. This way you cannot over sell what you have. It is annoying to have to go back in and change the product quantity all the time per the people who did not finish, transaction failed, or payment is pending. The only way to get around this is if you delete the not paid or failed transactions and it adds those amounts back to the stock.

This even happens when selling on amazon and ebay. They reduce the stock of the product. If the payment fails or the transcation is canceled then they stock gets added back to the overall.