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;
}
}
}
}
