SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to update balance quantity in store purchase system

    Hi!!
    I have a store purchase management system which manages purchase orders, goods received notes and purchase bills.

    problem is, if the quantity in goods received note and purchase order dont match, similarly, if quantity in goods received note and purchase bill dont match,
    i have to keep track of the balance goods untill the balance gets 0.

    i dont know i am doing it right or not, but i added two fields in my database as balance quantity and status so as to every time grn and pb is made, it will update the quantity and if balance gets 0 status will change to 1.
    This is some of the code i am using.but it is working just for first item.similarly i have to show up the values in text boxes so i am fetching data from balance quantity field but that too dosent work at all.
    can anyone tell me where i am going wrong??

    PHP Code:
    <?PHP
    if(empty($error)){
    $supplier_name         $_POST['supplier_name'];
    $grn_no              $_POST['gr_no'];
    $bill_no             $_POST['bill_no'];
    $bill_date          $_POST['bill_date'];

                for(
    $i 0$maxi count($_POST['total']); $i $maxi$i++)  { // for start
                        
    $total  = (isset($_POST['total'][$i]) && !empty($_POST['total'][$i])) ? mysql_real_escape_string($_POST['total'][$i]) : 0;
                        }

                            if (isset(
    $_POST['qty']) && sizeof($_POST['qty']) > 0)  { //isset name of item start
                                       
    for($i 0$maxi count($_POST['qty']); $i $maxi$i++)  { // for start
                        
    $qty        = (isset($_POST['qty'][$i]) && !empty($_POST['qty'][$i])) ? mysql_real_escape_string($_POST['qty'][$i]) : 0;
                           
    $unit         = (isset($_POST['unit'][$i]) && !empty($_POST['unit'][$i])) ? mysql_real_escape_string($_POST['unit'][$i]) : 0;
                        
    $rate          = (isset($_POST['rate'][$i]) && !empty($_POST['rate'][$i])) ? mysql_real_escape_string($_POST['rate'][$i]) : 0;
                        
    $unit_1     = (isset($_POST['unit_1'][$i]) && !empty($_POST['unit_1'][$i])) ? mysql_real_escape_string($_POST['unit_1'][$i]) : 0;
                        
    $amt          = (isset($_POST['amt'][$i]) && !empty($_POST['amt'][$i])) ? mysql_real_escape_string($_POST['amt'][$i]) : 0;
                        
    $item_name  = (isset($_POST['item_name'][$i]) && !empty($_POST['item_name'][$i])) ? mysql_real_escape_string($_POST['item_name'][$i]) : 0;

                
    $query_date=mysql_query
                
    ("INSERT INTO purchase_bill_entry_data
                (item_name,
                qty,
                unit,
                rate,
                unit_1,
                amt,
                bill_no)
                VALUES
                ('"
    .$item_name."',
                '"
    .$qty."',
                '"
    .$unit."',
                '"
    .$rate."',
                '"
    .$unit_1."',
                '"
    .$amt."',
                '"
    .$bill_no."')
                "
    );
                
            } 
    // for end
            
             
    //isset name of item end


             
    $query=mysql_query
             
    ("INSERT INTO purchase_bill_entry
             (supplier_name,
             grn_no,
             bill_no,
             bill_date,
             amt,
             total )
             VALUES
             ('"
    .$supplier_name."',
             '"
    .$grn_no."',
             '"
    .$bill_no."',
             '"
    .$bill_date."',
             '"
    .$amt."',
             '"
    .$total."')
              "
    );
              
            
    $bill_status=mysql_query("UPDATE goods_received_note SET bill_status=1 WHERE gr_no='".$grn_no."'");
                                    
                
    header("location:".$_SERVER['PHP_SELF']."?action=success");
                
    $balance=mysql_query("SELECT * FROM goods_received_note_data WHERE gr_no='".$grn_no."'");
                while(
    $row=mysql_fetch_array($balance))
                {
                
    $row_qty=$row['qty'];
                
    $ps_qty=$qty;
                
    $bal_qty=($row_qty)-($ps_qty);
                
    $update=mysql_query("UPDATE goods_received_note_data SET balance_qty='".$bal_qty."' WHERE gr_no='".$grn_no."' AND item_name='".$item_name."'");
                if(
    $bal_qty==0)
                {
                
    $bal_update=mysql_query("UPDATE goods_received_note_data SET bal_status=1 WHERE gr_no='".$grn_no."' AND item_name='".$item_name."'");
                }
                }


                     }
    ?>
    It's easy once you know how...

  2. #2
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am extremely sorry for unreadable code.
    Here is my more readable code:
    <?PHP
    if(empty($error)){ //if empty error start
    $supplier_name = $_POST['supplier_name'];
    $grn_no = $_POST['gr_no'];
    $bill_no = $_POST['bill_no'];
    $bill_date = $_POST['bill_date'];

    for($i = 0, $maxi = count($_POST['total']); $i < $maxi; $i++) { // for start to get total value
    $total = (isset($_POST['total'][$i]) && !empty($_POST['total'][$i])) ? mysql_real_escape_string($_POST['total'][$i]) : 0;
    }// for end to get total value

    if (isset($_POST['qty']) && sizeof($_POST['qty']) > 0) { //isset name of item start
    for($i = 0, $maxi = count($_POST['qty']); $i < $maxi; $i++) { // for start
    $qty = (isset($_POST['qty'][$i]) && !empty($_POST['qty'][$i])) ? mysql_real_escape_string($_POST['qty'][$i]) : 0;
    $unit = (isset($_POST['unit'][$i]) && !empty($_POST['unit'][$i])) ? mysql_real_escape_string($_POST['unit'][$i]) : 0;
    $rate = (isset($_POST['rate'][$i]) && !empty($_POST['rate'][$i])) ? mysql_real_escape_string($_POST['rate'][$i]) : 0;
    $unit_1 = (isset($_POST['unit_1'][$i]) && !empty($_POST['unit_1'][$i])) ? mysql_real_escape_string($_POST['unit_1'][$i]) : 0;
    $amt = (isset($_POST['amt'][$i]) && !empty($_POST['amt'][$i])) ? mysql_real_escape_string($_POST['amt'][$i]) : 0;
    $item_name = (isset($_POST['item_name'][$i]) && !empty($_POST['item_name'][$i])) ? mysql_real_escape_string($_POST['item_name'][$i]) : 0;

    //query to insert values that are in array
    $query_date=mysql_query
    ("INSERT INTO purchase_bill_entry_data
    (item_name,
    qty,
    unit,
    rate,
    unit_1,
    amt,
    bill_no)
    VALUES
    ('".$item_name."',
    '".$qty."',
    '".$unit."',
    '".$rate."',
    '".$unit_1."',
    '".$amt."',
    '".$bill_no."')
    ");
    } // for end
    } //isset name of item end

    //query to insert values that are not in array
    $query=mysql_query
    ("INSERT INTO purchase_bill_entry
    (supplier_name,
    grn_no,
    bill_no,
    bill_date,
    amt,
    total )
    VALUES
    ('".$supplier_name."',
    '".$grn_no."',
    '".$bill_no."',
    '".$bill_date."',
    '".$amt."',
    '".$total."')
    ");

    //update grn status as 1 if bill is made
    //honestly dont know how to do this if all the goods are received
    $bill_status=mysql_query("UPDATE goods_received_note SET bill_status=1 WHERE gr_no='".$grn_no."'");
    header("location:".$_SERVER['PHP_SELF']."?action=success");
    $balance=mysql_query("SELECT * FROM goods_received_note_data WHERE gr_no='".$grn_no."'");
    while($row=mysql_fetch_array($balance)){ //while start for balance quantity
    $row_qty=$row['qty'];//actual qty as per goods received note
    $ps_qty=$qty;//qty received i.e.$_POST['qty'];
    $bal_qty=($row_qty)-($ps_qty);
    //this query works only for 1 item and for other quantities it updates 0
    $update=mysql_query("UPDATE goods_received_note_data SET balance_qty='".$bal_qty."' WHERE gr_no='".$grn_no."' AND item_name='".$item_name."'");
    if($bal_qty==0){//if start for balance qty==0 i.e. all goods are received
    $bal_update=mysql_query("UPDATE goods_received_note_data SET bal_status=1 WHERE gr_no='".$grn_no."' AND item_name='".$item_name."'");
    }//if end
    }//while end for bal qty
    }//if empty error end
    ?>
    It's easy once you know how...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •