<?php
/**********************************************************************
***********************************************************************/

function get_sql_for_project_credit_note_inquiry($project_id, $selected_id, 
                                                $from_date, $to_date, $reference, 
                                                $customer_id, $p_invoice_id)
{        
    $sql = "SELECT pcn.*,
                    pm.description project_description,
                    dm.name customer_name,
                    cb.br_name branch_name,               
                    sm.salesman_name,               
                    pi.order_no pi_selected_id,
                    pi.reference pi_reference,
                    pi.ord_date pi_order_date,
                    pi.total pi_total                    
            
            FROM ".TB_PREF."project_credit_notes pcn 
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    pcn.project_id = pm.project_id
          
                LEFT JOIN ".TB_PREF."debtors_master dm ON
                    pcn.debtor_no = dm.debtor_no

                LEFT JOIN ".TB_PREF."cust_branch cb ON
                    pcn.branch_code = cb.branch_code                               
                                  
                LEFT JOIN ".TB_PREF."salesman sm ON
                    pcn.salesman_code = sm.salesman_code
                    
                LEFT JOIN ".TB_PREF."project_invoices pi ON 
                    pcn.project_invoice_id = pi.order_no
                                 
            WHERE  pcn.ord_date >=".db_escape(date2sql($from_date))." AND 
                    pcn.ord_date <=".db_escape(date2sql($to_date));
            
            if($project_id > 0){ 
                $sql .= " AND pcn.project_id=".db_escape($project_id);
            }
            
            if($selected_id > 0){ 
                $sql .= " AND pcn.order_no=".db_escape($selected_id);
            }
            
            if($reference != ''){ 
                $sql .= " AND pcn.reference=".db_escape($reference);
            }
            
            if($customer_id > 0){ 
                $sql .= " AND pcn.debtor_no=".db_escape($customer_id);
            }
            
            if($p_invoice_id > 0){ 
                $sql .= " AND pcn.project_invoice_id=".db_escape($p_invoice_id);
            }
                               
    $sql .= " ORDER BY ord_date";
                                  
    return $sql; 
}

        
function view_project_credit_note_header($trans_no)
{
    $sql = "SELECT pcn.*,
                    pm.description project_name,
                    dm.name customer_name,
                    cb.br_name branch_name,                 
                    sm.salesman_name,
                    tg.name tax_group_name,
                    pi.reference invoice_reference,
                    pi.order_no invoice_order_no,
                    pi.ord_date invoice_order_date,
                    pi.total invoice_total                  

            FROM ".TB_PREF."project_credit_notes pcn
                    LEFT JOIN ".TB_PREF."projects_master pm ON
                        pcn.project_id = pm.project_id
                        
                    LEFT JOIN ".TB_PREF."debtors_master dm ON
                        pcn.debtor_no = dm.debtor_no
                        
                    LEFT JOIN ".TB_PREF."cust_branch cb ON
                        pcn.branch_code = cb.branch_code
                                        
                    LEFT JOIN ".TB_PREF."salesman sm ON
                        pcn.salesman_code = sm.salesman_code
                        
                    LEFT JOIN ".TB_PREF."tax_groups tg ON
                        pcn.tax_group_id = tg.id
                        
                    LEFT JOIN ".TB_PREF."project_invoices pi ON
                        pcn.project_invoice_id = pi.order_no

            WHERE pcn.order_no=".db_escape($trans_no);
                
    $result = db_query($sql);
    return db_fetch($result);
}
        
        
function view_project_credit_note_details($trans_no)
{
    $sql = "SELECT pcnd.*,
                    iu.name unit_name,
                    lv.value_name category_name,
                    cm.account_code debit_code,
                    cm.account_name debit_account,
                    ct.account_code credit_code,
                    ct.account_name credit_account
        

            FROM ".TB_PREF."project_credit_note_details pcnd
                LEFT JOIN ".TB_PREF."item_units iu ON
                    pcnd.unit = iu.abbr
                     
                LEFT JOIN ".TB_PREF."list_values lv ON
                    pcnd.category_id = lv.id

                LEFT JOIN ".TB_PREF."chart_master cm ON
                    pcnd.debit_account = cm.account_code

                LEFT JOIN ".TB_PREF."chart_master ct ON
                    pcnd.credit_account = ct.account_code

            WHERE order_no=".db_escape($trans_no);
                
    return db_query($sql);
    
}


function view_project_credit_note_taxes($trans_no)
{
    $sql = "SELECT it.*,
                    tt.name tax_type_name 
        
            FROM ".TB_PREF."project_credit_note_item_taxes it 
                JOIN ".TB_PREF."tax_types tt ON 
                    it.tax_type_id = tt.id
                     
 
            WHERE pcn_id=".db_escape($trans_no);
                
        return db_query($sql);    
}


function get_sql_for_approve_project_credit_note_inquiry($current_user_id)
{        
    $sql = "SELECT pcn.*,
                    pm.description project_description,
                    dm.name customer_name,
                    cb.br_name branch_name,                
                    sm.salesman_name,               
                    pi.order_no pi_selected_id,
                    pi.reference pi_reference,
                    pi.ord_date pi_order_date,
                    pi.total pi_total                    
            
            FROM ".TB_PREF."project_credit_notes pcn 
                JOIN ".TB_PREF."projects_master pm ON
                    pcn.project_id = pm.project_id
          
                JOIN ".TB_PREF."debtors_master dm ON
                    pcn.debtor_no = dm.debtor_no

                JOIN ".TB_PREF."cust_branch cb ON
                    pcn.branch_code = cb.branch_code                                     
                    
                JOIN ".TB_PREF."salesman sm ON
                    pcn.salesman_code = sm.salesman_code
                    
                JOIN ".TB_PREF."approvers apr ON
                    pcn.approved_by = apr.user_id                 
                  
                JOIN ".TB_PREF."users c ON
                    pcn.created_by = c.id
                    
                JOIN ".TB_PREF."user_data_access_groups udag ON
                    pm.dag_id = udag.dag_id
                    
                JOIN ".TB_PREF."users u ON
                    apr.user_id = u.id
                    
                LEFT JOIN ".TB_PREF."project_invoices pi ON 
                    pcn.project_invoice_id = pi.order_no  
                                 
            WHERE (pcn.approval_status = ".AS_AWAITING_APPROVAL." OR pcn.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                    pcn.next_approval_group_id = apr.approval_group_id AND 
                    pcn.created_by = c.id AND 
                  
                    udag.user_id = apr.user_id AND  
                 
                    apr.user_id =".db_escape($current_user_id);                    
  

                                  
    return $sql; 
}



?>