public function fix() { // get the facility_codes $facility_codes = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select distinct `facility_code` from `facility_issues` WHERE status=1"); foreach ($facility_codes as $key => $facility_code) { //step one reset refrence table $facility_code = $facility_code['facility_code']; $min_date = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select min(date_issued) as min_date,issued_by from `facility_issues` WHERE facility_code={$facility_code} and status=1"); $reset_facility_transaction_table = Doctrine_Manager::getInstance()->getCurrentConnection(); $reset_facility_transaction_table->execute("DELETE FROM `facility_transaction_table` WHERE facility_code={$facility_code}; "); $reset_facility_transaction_table->execute("DELETE FROM `facility_issues` WHERE facility_code={$facility_code} and s11_No='initial stock update'\n and status=1"); //step two get the facility stocks $facility_stocks = facility_stocks::get_facility_commodity_total($facility_code); $data_array_facility_transaction = $data_array_facility_issues = array(); foreach ($facility_stocks as $facility_stock) { $commodity_id = $facility_stock['commodity_id']; if ($commodity_id > 0) { $total = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select ifnull(sum(qty_issued),0) as total from `facility_issues` WHERE facility_code={$facility_code} and status=1 and commodity_id={$commodity_id}"); $mydata2 = array('facility_code' => $facility_code, 'commodity_id' => $facility_stock['commodity_id'], 'opening_balance' => $facility_stock['commodity_balance'], 'total_issues' => $total[0]['total'], 'total_receipts' => 0, 'adjustmentpve' => 0, 'adjustmentnve' => 0, 'date_added' => $min_date[0]['min_date'], 'closing_stock' => $facility_stock['commodity_balance'], 'status' => 1); //send the data to the facility_transaction_table array_push($data_array_facility_transaction, $mydata2); $total_unit_count_ = $facility_stock['commodity_balance'] * -1; $mydata_ = array('facility_code' => $facility_code, 's11_No' => 'initial stock update', 'commodity_id' => $facility_stock['commodity_id'], 'batch_no' => "N/A", 'expiry_date' => "N/A", 'balance_as_of' => 0, 'qty_issued' => $total_unit_count_, 'date_issued' => $min_date[0]['min_date'], 'issued_to' => 'N/A', 'issued_by' => $min_date[0]['issued_by']); //create the array to push to the db array_push($data_array_facility_issues, $mydata_); } } $this->db->insert_batch('facility_transaction_table', $data_array_facility_transaction); $this->db->insert_batch('facility_issues', $data_array_facility_issues); echo "<br>{$key} fixed facility code " . $facility_code; } }