<?php  
    $objAxpDB->ViewName("Daftar laporan penjualan");
    //$objAxpDB->ViewTable("vSALaporanPenjualan");
    $objAxpDB->ViewSQL("SELECT fnid,ftreport_code, ucwords(ftreport_name) AS ftreport_name FROM tlreport WHERE ftreport_group = 'penjualan' and fnstatus = 1 order by fnorder, ftreport_code");
    $objAxpDB->ViewCaption("ID, Kode, Nama laporan");
    
    $objAxpDB->FormName(" laporan penjualan");
    $objAxpDB->FormSQL("SELECT ftreport_name FROM tlreport");
    $objAxpDB->FormCaption("Nama laporan");
    $objAxpDB->FormPosition("TL");
    $objAxpDB->lengt_object=false;
    
     // filter only sales transaction if login by user sales
    $ftsales_code = RetRstField("Select ifnull(ftsales_code,'') FROM tluser WHERE ftusername = '{$_SESSION['username']}' ");
    if ($ftsales_code != "") { $sql_where = " AND ftsales_code = '{$ftsales_code}' "; } 
      
    
    if($_REQUEST['id']) {
        $ssql="select * from tlreport where fnid='{$_REQUEST['id']}'";
        $rs=mysql_query($ssql);
        $r=mysql_fetch_assoc($rs);
        $code=$r['ftreport_code'];
        $report_name=$r['ftreport_name'];
        switch ($code) {
            case "s01":
                // $objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM txcurrency order by ftsimbol", "ftsimbol", "ftdescription", -1,true);

                $objAxpDB->FormSQL("SELECT '' as currency_code, '' AS ftsi_no,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                        FROM tlreport");
                $objAxpDB->FormCaption("Mata Uang,No Invoice,Date From,Date End");
                $objAxpDB->FormPosition("HD, TL, TR, TR");
                
                break;
        
            case "s02":
                $objAxpDB->Lookup("customer_code", "SELECT \'SELURUH\' as ftcustomer_code,\'SELURUH\' AS ftcustomer_name UNION ALL SELECT ftcustomer_code, ftcustomer_name FROM tlcustomer", "ftcustomer_code", "ftcustomer_name", -1,false);
                $objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM tlcurrency_hdr order by ftsimbol", "ftsimbol", "ftdescription", -1,true);

                $objAxpDB->FormSQL("SELECT '' as customer_code,
                                        '' as currency_code,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");

                $objAxpDB->FormCaption("Customer, Mata Uang, Date From,Date End");
                $objAxpDB->FormPosition("TL, HD, TR, TR");
                
                /*$report_script = "document.getElementById('customer_code').value = 'SELURUH'
                                  document.getElementById('hidden_customer_code').value = 'SELURUH';
                                  document.getElementById('span_customer_code').innerHTML = 'SELURUH';"; */
                
                $objAxpDB->FormJS($report_script);
                break;
            
            case "s03":
                if ($ftsales_code != "") { 
                    $lookup_sales =  $ftsales_code;
                    $objAxpDB->Lookup("sales_code", "SELECT ftsales_code as sales_code, ftsales_name FROM tlsales WHERE fnstatus=1 AND ftsales_code = '{$ftsales_code}' ", "sales_code", "ftsales_name", -1, true);
                } else {
                    $lookup_sales = "SELURUH";
                    $objAxpDB->Lookup("sales_code", "SELECT  'SELURUH' as sales_code , 'SELURUH' as ftsales_name UNION ALL SELECT  ftsales_code as sales_code, ftsales_name FROM tlsales WHERE fnstatus=1 ORDER BY sales_code", "sales_code", "ftsales_name", -1, true);
                }
                
                // $objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM txcurrency order by ftsimbol", "ftsimbol", "ftdescription", -1,true);

                $objAxpDB->FormSQL("SELECT '{$lookup_sales}' as sales_code,
                                        '' as currency_code,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");

                $objAxpDB->FormCaption("Salesman, Mata Uang, Date From,Date End");
                $objAxpDB->FormPosition("TL, HD, TR, TR");
                
                break;
                
            case "s04":
                if ($ftsales_code != "") { 
                    $lookup_sales =  $ftsales_code;
                    $objAxpDB->Lookup("sales_code", "SELECT ftsales_code as sales_code, ftsales_name FROM tlsales WHERE fnstatus=1 AND ftsales_code = '{$ftsales_code}' ", "sales_code", "ftsales_name", -1, true);
                } else {
                    $lookup_sales = "SELURUH";
                    $objAxpDB->Lookup("sales_code", "SELECT  'SELURUH' as sales_code , 'SELURUH' as ftsales_name UNION ALL SELECT  ftsales_code as sales_code, ftsales_name FROM tlsales WHERE fnstatus=1 ORDER BY sales_code", "sales_code", "ftsales_name", -1, true);
                }
                
                // $objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM txcurrency order by ftsimbol", "ftsimbol", "ftdescription", -1,true);

                $objAxpDB->FormSQL("SELECT '{$lookup_sales}' as sales_code,
                                        '' as currency_code,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");

                $objAxpDB->FormCaption("Salesman, Mata Uang, Date From,Date End");
                $objAxpDB->FormPosition("TL, HD, TR, TR");
                
                break;    
            
            case "s11":
                $objAxpDB->FormSQL("SELECT '' as ftitem_code
                                    FROM tlreport");

                $objAxpDB->FormCaption("Nama Barang (Keyword)");
                $objAxpDB->FormPosition("TL");
                break;
            
            case "s15":
                if ($ftsales_code != "") { 
                    $lookup_sales =  $ftsales_code;
                    $objAxpDB->Lookup("sales_code", "SELECT ftsales_code as sales_code, ftsales_name FROM tlsales WHERE fnstatus=1 AND ftsales_code = '{$ftsales_code}' ", "sales_code", "ftsales_name", -1, true);
                } else {
                    $lookup_sales = "";
                    $objAxpDB->Lookup("sales_code", "SELECT  'SELURUH' as sales_code , 'SELURUH' as ftsales_name UNION ALL SELECT  ftsales_code as sales_code, ftsales_name FROM tlsales WHERE fnstatus=1 ORDER BY sales_code", "sales_code", "ftsales_name", -1, true);
                }
                
                // $objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM txcurrency order by ftsimbol", "ftsimbol", "ftdescription", -1,true);
                // $objAxpDB->Lookup("ftitem_code", "SELECT \'SELURUH\' as ftitem_code,\'SELURUH\' AS ftitem_name UNION ALL SELECT ftitem_code, ftitem_name FROM tlstock_hdr where fnstatus = 1 AND ftstock_category_code=\'inv\' ", "ftitem_code", "ftitem_name", -1,false);
                $objAxpDB->Lookup("ftitem_code", "SELECT DISTINCT ftitem_code, ftitem_name FROM (SELECT \'SELURUH\' as ftitem_code,\'SELURUH\' AS ftitem_name UNION ALL SELECT ftitem_code, CASE WHEN TRIM(IFNULL(ftpart_no,\'\') != \'\') THEN CONCAT(ftitem_name,\' ,PART NO: \',IFNULL(ftpart_no,\'\')) ELSE ftitem_name END AS ftitem_name FROM tlstock_hdr WHERE fnstatus=1 ORDER BY ftitem_name) x ", "ftitem_code", "ftitem_name", -1,false);
                
                $objAxpDB->FormSQL("SELECT '' as currency_code,'' AS ftitem_code,
                                        '{$lookup_sales}' as sales_code,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end                                        
                                    FROM tlreport");
                $objAxpDB->FormCaption("Mata Uang, Kode Barang, Salesman, Date From, Date End");
                $objAxpDB->FormPosition("HD,TL,HD,TR,TR");
                
                $report_script = "document.getElementById('ftitem_code').value = 'SELURUH'
                                  document.getElementById('hidden_ftitem_code').value = 'SELURUH';
                                  document.getElementById('span_ftitem_code').innerHTML = 'SELURUH';";
                
                $objAxpDB->FormJS($report_script);
                break;
          
            case "s15_1":
                $objAxpDB->Lookup("ftsales_code", "SELECT \'SELURUH\' as ftsales_code,\'SELURUH\' AS ftsales_name UNION ALL   SELECT ftsales_code, ftsales_name FROM tlsales", "ftsales_code", "ftsales_name", -1,false);
                $objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM tlcurrency_hdr order by ftsimbol", "ftsimbol", "ftdescription", -1,true);

                $objAxpDB->FormSQL("SELECT '' as  ftsales_code,
                                        'IDR' as currency_code,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end                                        
                                    FROM tlreport");
                $objAxpDB->FormCaption("Kode Barang, Mata Uang, Date From, Date End");
                $objAxpDB->FormPosition("TL,TL,TR,TR");
              break;

            
           case "s15_2":
                $objAxpDB->Lookup("customer_code", "SELECT \'SELURUH\' as ftcustomer_code,\'SELURUH\' AS ftcustomer_name UNION ALL SELECT ftcustomer_code, ftcustomer_name FROM tlcustomer", "ftcustomer_code", "ftcustomer_name", -1,false);
            
                $objAxpDB->FormSQL("SELECT '' as customer_code,
                                        CONVERT('2018/01/01',date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");
                $objAxpDB->FormCaption("Customer, Date From, Date End");
                $objAxpDB->FormPosition("TL,TR,TR");
                $objAxpDB->FormLocked("0,1,0");
                
                $report_script = "document.getElementById('customer_code').value = 'SELURUH'
                                  document.getElementById('hidden_customer_code').value = 'SELURUH';
                                  document.getElementById('span_customer_code').innerHTML = 'SELURUH';";
                
                $objAxpDB->FormJS($report_script);
                
              break;

            case "s17":
                $objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM tlcurrency_hdr order by ftsimbol", "ftsimbol", "ftdescription", -1,true);
                $objAxpDB->Lookup("ftsales_code", "SELECT  'SELURUH' as ftsales_code , 'SELURUH' as ftsales_name UNION ALL SELECT  ftsales_code, ftsales_name  FROM tlsales WHERE fnstatus=1 ORDER BY ftsales_code", "ftsales_code", "ftsales_name", -1, true);
    
                $objAxpDB->FormSQL("SELECT 'SELURUH' as ftsales_code, 'IDR' as currency_code, CONVERT(null,date) as date_from, CONVERT(null,date) as date_end FROM tlreport");
                $objAxpDB->FormCaption("Salesman, Mata Uang, Date From,Date End");
                $objAxpDB->FormPosition("TL, TL, TR, TR");
            break;            
            
            case "s24":
                if ($ftsales_code != "") { 
                    $lookup_sales =  $ftsales_code;
                    $objAxpDB->Lookup("sales_code", "SELECT ftsales_code as sales_code, ftsales_name FROM tlsales WHERE fnstatus=1 AND ftsales_code = '{$ftsales_code}' ", "sales_code", "ftsales_name", -1, true);
                } else {
                    $lookup_sales = "SELURUH";
                    $objAxpDB->Lookup("sales_code", "SELECT  'SELURUH' as sales_code , 'SELURUH' as ftsales_name UNION ALL SELECT  ftsales_code as sales_code, ftsales_name FROM tlsales WHERE fnstatus=1 ORDER BY sales_code", "sales_code", "ftsales_name", -1, true);
                }
                
                // $objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM txcurrency order by ftsimbol", "ftsimbol", "ftdescription", -1,true);

                $objAxpDB->FormSQL("SELECT '{$lookup_sales}' as sales_code,
                                        '' as currency_code,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");

                $objAxpDB->FormCaption("Salesman, Mata Uang, Date From,Date End");
                $objAxpDB->FormPosition("TL, HD, TR, TR");
                
                break;
            
            case "s25":    
                //$objAxpDB->Lookup("ftitem_code", "SELECT \'SELURUH\' as ftitem_code,\'SELURUH\' AS ftitem_name UNION ALL SELECT ftitem_code, ftitem_name FROM tlstock_hdr where ftstock_category_code=\'inv\' ", "ftitem_code", "ftitem_name", -1,false);
                //$objAxpDB->Lookup("ftitem_code", "SELECT DISTINCT ftitem_code, ftitem_name FROM (SELECT \'SELURUH\' as ftitem_code,\'SELURUH\' AS ftitem_name UNION ALL SELECT ftitem_code, CONCAT(ftitem_name,\' PART NO: \',ifnull(ftpart_no,\'\')) AS ftitem_name FROM tlstock_hdr WHERE fnstatus=1 ORDER BY ftitem_name) x ", "ftitem_code", "ftitem_name", -1,false);
                $objAxpDB->Lookup("ftitem_code", "SELECT DISTINCT ftitem_code, ftitem_name FROM (SELECT \'SELURUH\' as ftitem_code,\'SELURUH\' AS ftitem_name UNION ALL SELECT ftitem_code, CASE WHEN TRIM(IFNULL(ftpart_no,\'\') != \'\') THEN CONCAT(ftitem_name,\' ,PART NO: \',IFNULL(ftpart_no,\'\')) ELSE ftitem_name END AS ftitem_name FROM tlstock_hdr WHERE fnstatus=1 ORDER BY ftitem_name) x ", "ftitem_code", "ftitem_name", -1,false);
                
                $objAxpDB->FormSQL("SELECT '' as ftitem_code FROM tlreport");
                $objAxpDB->FormCaption("Kode Barang");
                $objAxpDB->FormPosition("TL");
                
                $report_script = "document.getElementById('ftitem_code').value = 'SELURUH'
                                  document.getElementById('hidden_ftitem_code').value = 'SELURUH';
                                  document.getElementById('span_ftitem_code').innerHTML = 'SELURUH';";
                
                $objAxpDB->FormJS($report_script);
                break;
        
            case "s26":
                $objAxpDB->Lookup("customer_code", "SELECT \'SELURUH\' as ftcustomer_code,\'SELURUH\' AS ftcustomer_name UNION ALL SELECT ftcustomer_code, ftcustomer_name FROM tlcustomer", "ftcustomer_code", "ftcustomer_name", -1,false);
                //$objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM txcurrency order by ftsimbol", "ftsimbol", "ftdescription", -1,true);

                $objAxpDB->FormSQL("SELECT '' as customer_code,
                                        '' as currency_code,
                                        '{$ftsales_code}' AS sales_code,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");

                $objAxpDB->FormCaption("Customer, Mata Uang, Kode Sales, Date From,Date End");
                $objAxpDB->FormPosition("TL, HD, HD, TR, TR");
                
                $report_script = "document.getElementById('customer_code').value = 'SELURUH'
                                  document.getElementById('hidden_customer_code').value = 'SELURUH';
                                  document.getElementById('span_customer_code').innerHTML = 'SELURUH';";
                
                $objAxpDB->FormJS($report_script);
                break;

            case "s27":
                //$objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM txcurrency order by ftsimbol", "ftsimbol", "ftdescription", -1,true);

                $objAxpDB->FormSQL("SELECT '' as currency_code,'' AS ftsi_no,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end                                        
                                    FROM tlreport");
                $objAxpDB->FormCaption("Mata Uang, No Invoice, Date From, Date End");
                $objAxpDB->FormPosition("HD,TL,TR,TR");
                break;
            
            /* Tidak digunakan di Abrindo
            case "s05":
                $objAxpDB->FormSQL("SELECT  
                                CONVERT(null,date) as date_from, 
                                CONVERT(null,date) as date_end 
                                FROM tlreport");
                $objAxpDB->FormCaption("Date From,Date End");
                $objAxpDB->FormPosition("TL, TL,TL");           

                break; 
            
            case "s08":
                $objAxpDB->Lookup("customer_code", "SELECT \'SELURUH\' as ftcustomer_code,\'SELURUH\' AS ftcustomer_name UNION ALL   SELECT ftcustomer_code, ftcustomer_name FROM tlcustomer", "ftcustomer_code", "ftcustomer_name", -1,false);
                $objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM txcurrency order by ftsimbol", "ftsimbol", "ftdescription", -1,true);

                $objAxpDB->FormSQL("SELECT '' as customer_code,
                                        'IDR' AS currency_code,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");

                $objAxpDB->FormCaption("Customer,Mata Uang,Date From,Date End");
                $objAxpDB->FormPosition("TL,TL,TR,TR");
                break;
            
            case "s09":
                $objAxpDB->Lookup("ftstock_group_code", "SELECT \'SELURUH\' as ftstock_group_code,\'SELURUH\' AS ftstock_group_name UNION ALL SELECT ftstock_group_code, ftstock_group_name FROM tlstock_group where ftstock_group_code!=\'ASSET\' ", "ftstock_group_code", "ftstock_group_name", -1,false);
                
                $objAxpDB->FormSQL("SELECT '' as ftstock_group_code,
                                CONVERT(null,date) as date_from, 
                                CONVERT(null,date) as date_end 
                                FROM tlreport");
                $objAxpDB->FormCaption("Stock Group,Date From,Date End");
                $objAxpDB->FormPosition("TL,TR,TR");  
                break;
            
            case "s12":
                $objAxpDB->Lookup("customer_code", "SELECT \'SELURUH\' as ftcustomer_code,\'SELURUH\' AS ftcustomer_name UNION ALL   SELECT ftcustomer_code, ftcustomer_name FROM tlcustomer", "ftcustomer_code", "ftcustomer_name",1,false);
                $objAxpDB->Lookup("currency_code", "SELECT ftsimbol, ftdescription FROM txcurrency order by ftsimbol", "ftsimbol", "ftdescription", -1,true);

                $objAxpDB->FormSQL("SELECT '' as  customer_code,
                                        'IDR' as currency_code,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");
                $objAxpDB->FormCaption("Customer, Mata Uang, Date From, Date End");
                $objAxpDB->FormPosition("TL,TL,TR,TR");
              break;

            case "s13":
                $objAxpDB->Lookup("date_from", "SELECT DISTINCT Year(fdtrans_date) as date_from_code,'' AS date_from_name FROM txsales_invoice_hdr ", "date_from_code", "date_from_name",1,true);

                $objAxpDB->FormSQL("SELECT Year(Now()) as date_from
                                    FROM tlreport");

                $objAxpDB->FormCaption("Tahun");
                $objAxpDB->FormPosition("TL");
                break;

            case "s14":
                $objAxpDB->Lookup("date_from", "SELECT DISTINCT Year(fdtrans_date) as date_from_code,'' AS date_from_name FROM txsales_invoice_hdr ", "date_from_code", "date_from_name",1,true);

                $objAxpDB->FormSQL("SELECT Year(Now()) as date_from
                                    FROM tlreport");

                $objAxpDB->FormCaption("Tahun");
                $objAxpDB->FormPosition("TL");
                break;

            case "s18":
              $objAxpDB->Lookup("customer_type", "SELECT 'SELURUH' as ftcustomer_type_code,'SELURUH' AS ftcustomer_type_name UNION ALL   SELECT ftcustomer_type_code, ftcustomer_type_name FROM tlcustomer_type", "ftcustomer_type_code", "ftcustomer_type_name",1,true);
              $objAxpDB->FormSQL("SELECT
                                    '' as customer_type,
                                    CONVERT(null,date) as date_from,
                                    CONVERT(null,date) as date_end
                                    FROM tlreport");

                $objAxpDB->FormCaption("Customer Type, Date From,Date End");
                $objAxpDB->FormPosition("TL,TL,TL");

                break;
           case "s19":
                $objAxpDB->Lookup("customer_type", "SELECT 'SELURUH' as ftcustomer_type_code,'SELURUH' AS ftcustomer_type_name UNION ALL   SELECT ftcustomer_type_code, ftcustomer_type_name FROM tlcustomer_type", "ftcustomer_type_code", "ftcustomer_type_name",1,true);
                $objAxpDB->FormSQL("SELECT
                                    '' as customer_type,
                                    CONVERT(null,date) as date_from,
                                    CONVERT(null,date) as date_end
                                    FROM tlreport");

                $objAxpDB->FormCaption("Customer Type, Date From,Date End");
                $objAxpDB->FormPosition("HD,TL,TL");

                break;

            case "s20":
                $objAxpDB->FormSQL("SELECT
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");
                $objAxpDB->FormCaption("Date From,Date End");
                $objAxpDB->FormPosition("TL,TL");
                break;
        
            case "s21":    
                $objAxpDB->Lookup("customer_code", "SELECT \'SELURUH\' as ftcustomer_code,\'SELURUH\' AS ftcustomer_name UNION ALL   SELECT ftcustomer_code, ftcustomer_name FROM tlcustomer", "ftcustomer_code", "ftcustomer_name", -1,false);
            
                $objAxpDB->FormSQL("SELECT '' as customer_code,
                                        CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");
                $objAxpDB->FormCaption("Customer, Date From,Date End");
                $objAxpDB->FormPosition("TL,TR,TR");
                break;
        
            case "s22":    
                $objAxpDB->FormSQL("SELECT CONVERT(null,date) as date_from,
                                        CONVERT(null,date) as date_end
                                    FROM tlreport");
                $objAxpDB->FormCaption("Date From,Date End");
                $objAxpDB->FormPosition("TL,TL");
            break;

            case "s23":
                $objAxpDB->Lookup("ftitem_code", "SELECT \'SELURUH\' as ftitem_code,\'SELURUH\' AS ftitem_name UNION ALL SELECT ftitem_code, ftitem_name FROM tlstock_hdr where ftstock_category_code=\'inv\' ", "ftitem_code", "ftitem_name", -1,false);
                $objAxpDB->Lookup("customer_code", "SELECT \'SELURUH\' as ftcustomer_code,\'SELURUH\' AS ftcustomer_name UNION ALL   SELECT ftcustomer_code, ftcustomer_name FROM tlcustomer", "ftcustomer_code", "ftcustomer_name", -1,false);
            
                $objAxpDB->FormSQL("SELECT '' as customer_code,
                                '' as ftitem_code,
                                CONVERT(null,date) as date_from,
                                CONVERT(null,date) as date_end
                                FROM tlreport");
                $objAxpDB->FormCaption("Customer, Kode Barang, Date From, Date End");
                $objAxpDB->FormPosition("TL,TL,TR,TR");
                break;   
             */
        }
    }
    
   
     
    $objAxpDB->Process();
    if(!$_REQUEST['id']) {
       
        $objAxpDB->WriteHtmlTable();
    }
    else {
        $objAxpDB->FormName($report_name);
        $objAxpDB->ButtonShow("generate_report,print_report,export_excel_report,export_pdf_report");  
        $objAxpDB->reportData();
    }