public function getHelp($stock_type = '2', $start_date = '2013-08-01', $end_date = '2013-08-31') { /* * Loop through all respective transaction types and add beginning balance at the beginning * Outer Loop through all active drugs * Inner Loop through all respective transaction types for the outer drug */ $first_value = "AND ccc_store_sp = {$stock_type}"; $second_value = "AND dst.ccc_store_sp = {$stock_type}"; $drugs = Drugcode::getEnabledDrugs(); $transactions = Transaction_Type::getAllTypes(); $overall_array = array(); $trans_sections = array(); $prev_start = date("Y-m-d", strtotime("-1 month", strtotime($start_date))); $prev_end = date("Y-m-d", strtotime("-1 month", strtotime($end_date))); $trans_sections['Beginning Balance'] = 0; foreach ($transactions as $transaction) { $trans_sections[$transaction['Name']] = $transaction['id']; } foreach ($drugs as $drug) { $drug_id = $drug['id']; $drug_name = $drug['Drug']; foreach ($trans_sections as $section_index => $sections) { if ($sections == 0) { /* * Runs when transaction is beginngin balance * Get Beginning Balance of drug */ $sql = "SELECT SUM( dst.balance ) AS total FROM drug_stock_movement dst, \n\t\t\t\t\t\t\t(SELECT drug, batch_number, MAX( transaction_date ) AS trans_date FROM `drug_stock_movement` \n\t\t\t\t\t\t\tWHERE transaction_date BETWEEN '{$prev_start}' AND '{$prev_end}' AND drug ='{$drug_id}' {$first_value} \n\t\t\t\t\t\t\tGROUP BY batch_number) AS temp WHERE dst.drug = temp.drug AND dst.batch_number = temp.batch_number \n\t\t\t\t\t\t\tAND dst.transaction_date = temp.trans_date {$second_value}"; } else { $effect = Transaction_Type::getEffect($sections); if ($effect['Effect'] == 1) { $balance_value = "quantity"; } else { $balance_value = "quantity_out"; } $sql = "SELECT SUM({$balance_value}) AS total FROM `drug_stock_movement` \n\t\t\t\t\t\t\tWHERE transaction_date BETWEEN '{$start_date}' AND '{$end_date}' {$first_value} \n\t\t\t\t\t\t\tAND transaction_type ='{$sections}' AND drug='{$drug_id}'"; } $query = $this->db->query($sql); $results = $query->result_array(); if ($results) { if ($results[0]['total'] != null) { $overall_array[$drug_name][$section_index] = $results[0]['total']; } else { $overall_array[$drug_name][$section_index] = 0; } } else { $overall_array[$drug_name][$section_index] = 0; } } } }