<?php

function sales_least_markup_items_table_display($company_code, $begin_date, $end_date)
{
    $title = ("Least Margin Items");
    display_title($title);
    
    start_table(TABLESTYLE, "width=80%");
    $th = array("Item", "Inv No.", "Unit", "Qty", 
                    "Price", "Discount", "Discounted Price", 
                    "Unit Cost", "Profit", "Line Profit");

    table_header($th);
//    $month = get_month($month_id);
    
    $previous_stock_id = "";
    $result = get_sales_least_markup_items($company_code, $begin_date, $end_date);

    while ($item = db_fetch($result)){
        $row = get_sales_least_markup_item_details($company_code, $begin_date, $end_date, $item['stock_id']);
        
        alt_table_row_color($k);

        label_cell($row['stock_id']." - ".$row['item_master_description']);      
        
        $inv_link = get_customer_trans_view_str(ST_SALESINVOICE, $row['trans_no']);
        label_cell($inv_link, 'align=center');       
        label_cell($row['tran_unit']);
        
        $tran_unit_qty = $row['tran_qty'];
        amount_cell($tran_unit_qty);     
        
        $tran_unit_price = $row['unit_price'] * $row['conversion_factor'];
        amount_cell($tran_unit_price);
        
//        amount_cell($row['discount_percent']);
        label_cell(number_format($row['discount_percent'], 2)."%", 'align=right');
        
        $tran_unit_discounted_price = $tran_unit_price * (1 - $row['discount_percent']/100);
        amount_cell($tran_unit_discounted_price);
        
        $tran_unit_standard_cost = $row['standard_cost'] * $row['conversion_factor'];
        amount_cell($tran_unit_standard_cost);
        
        $profit_percentage = $row['profit_factor'] * 100;
//        amount_cell($profit_percentage);
        label_cell(number_format($profit_percentage, 2)."%", 'align=right');
        
        $line_profit = ($tran_unit_discounted_price - $tran_unit_standard_cost) * $tran_unit_qty;
        amount_cell($line_profit);

        end_row();
        
        $previous_stock_id = $row['stock_id'];
    }
    end_table();
}


function get_sales_least_markup_item_details($company_code, $begin_date, $end_date, $stock_id)
{
//    $db_name = get_group_company_db_name($company_code);
    
    $sql = "SELECT dtd.stock_id, 
                    (dtd.unit_price - dtd.standard_cost) / dtd.standard_cost AS profit_factor , 
                    dt.tran_date,
                    dt.trans_no,
                    dtd.tran_qty,
                    dtd.tran_unit,
                    dtd.unit_price,
                    dtd.standard_cost,
                    dtd.conversion_factor,
                    dtd.discount_percent,
                    dtd.description item_sold_description,
                    sm.description item_master_description  
            FROM ".TB_PREF."debtor_trans_details dtd 
                JOIN ".TB_PREF."debtor_trans dt 
                    ON dtd.debtor_trans_type = dt.type 
                    AND dtd.debtor_trans_no = dt.trans_no 
                JOIN ".TB_PREF."stock_master sm  
                    ON dtd.stock_id = sm.stock_id  
            WHERE dtd.debtor_trans_type = ".db_escape(ST_SALESINVOICE)." 
                    AND dtd.unit_price > 0 
                    AND dtd.standard_cost > 0                     
                    AND dtd.stock_id = ".db_escape($stock_id)."                      
                    AND dt.tran_date >= ".db_escape($begin_date)." 
                    AND dt.tran_date <= ".db_escape($end_date)." 
            ORDER BY profit_factor 
            LIMIT 1";
    
    $result = db_query($sql);
    
    return db_fetch($result);
}

function get_sales_least_markup_items($company_code, $begin_date, $end_date)
{
//    $db_name = get_group_company_db_name($company_code);
    
    $sql = "SELECT DISTINCT dtd.stock_id 
            FROM ".TB_PREF."debtor_trans_details dtd 
                JOIN ".TB_PREF."debtor_trans dt 
                    ON dtd.debtor_trans_type = dt.type 
                    AND dtd.debtor_trans_no = dt.trans_no 
            WHERE dtd.debtor_trans_type = ".db_escape(ST_SALESINVOICE)." 
                    AND dtd.unit_price > 0 
                    AND dtd.standard_cost > 0 
                    AND dt.tran_date >= ".db_escape($begin_date)." 
                    AND dt.tran_date <= ".db_escape($end_date)." 
            ORDER BY (dtd.unit_price - dtd.standard_cost) / dtd.standard_cost 
            LIMIT 30";
    
    return db_query($sql);
}

        
        
        

?>


