在 JQuery DataTables 中按日期范围进行筛选 Visualforce 页面中的 AJAX

问：

我有一个要求，我需要将所有TXN__c记录放在一个表中，其中我有超过 50,000+ 条记录，这就是我使用服务器端分页的原因。在负载上，我现在可以得到我想要的东西。我的下一个挑战是将日期选择器中的Date__c从页面过滤到控制器。希望有人能在这方面帮助我。这就是我现在所拥有的。

控制器

public with sharing class AccountHistory2{
 
    Public Integer noOfRecords{get; set;}
    Public Integer size{get;set;}
    Public Integer start{get;set;}
    public String billToMember {get; set;}
    public Contact con {get; set;}
    public Date startDate {get;set;}
    public Date endDate {get;set;}
    string queryString;
    public Double sTotalAmount {get;set;}
    public Double sAmount {get;set;}
    public ApexPages.StandardSetController setCon {get;set;}
    public List<TXN__c> contactList {get; set;}
 
    public AccountHistory2(){
        startDate = System.Date.today().toStartOfMonth();
        endDate = System.Date.today().addMonths(1).toStartofMonth().addDays(-1);
        billToMember = ApexPages.CurrentPage().getparameters().get('id');
        con = [Select Id, Name, Total_Calculated_Account_Balance__c From Contact Where Id =: billToMember];
    }
    
    public string dataTableJson {get;set;}
         
    public void procesData(){
        DataTableWrapper datawrap;
        contactList = new List<TXN__c>();
            
        String namespace = Utility.getNamespace();
        
        queryString = 'Select Id, Date__c, ' + Utility.getNamespace() + 'Customer__r.Name, Bill_To_Member__c, Recordtype.Name, Name, Ticket_Number__c, Description__c, Statement_Description__c,' 
                        +'GlobalType__c, ' + Utility.getNamespace() + 'Charge_Item__r.Name, ' + Utility.getNamespace() + 'Credit_Financial_Account__r.Name, ' + Utility.getNamespace() + 'Debit_Financial_Account__r.Name, Amount__c, Tax__c, Service_Charge__c,'
                        +'GlobalSubType__c, Total_Amount__c, ' + Utility.getNamespace() + 'Bill_to_Member__r.Name'
                        +' from TXN__c'
                        +' Where Bill_To_Member__c =: billToMember'
                        +' AND RecordType.Name !=  \''+String.escapeSingleQuotes('Taxes')+ '\' AND RecordType.Name != \''+String.escapeSingleQuotes('Service Charges')+ '\' AND RecordType.Name != \''+String.escapeSingleQuotes('Deposit')+ '\'';// AND (Date__c >=: startDate AND Date__c <= :endDate)';
                        new map<String, Object> {'billToMember' => billToMember};
                            
                            if(startDate != null && endDate != null){
                                queryString += ' AND Date__c >=: startDate AND Date__c <=: endDate';
                            }
                            
        string searchKey = ApexPages.currentPage().getParameters().get('search[value]');
        System.debug(':::searchKey: '+searchKey);
        if(searchKey != null && searchKey != '' && searchKey.trim().length() > 0){
            queryString += ' AND (' + namespace + 'Bill_to_Member__r.Name like \'%'+String.escapeSingleQuotes(searchKey)+'%\''
                +'OR Bill_to_Member__r.Name like \'%'+String.escapeSingleQuotes(searchKey)+'%\''
                +'OR Statement_Description__c like \'%'+String.escapeSingleQuotes(searchKey)+'%\''
                +'OR Ticket_Number__c like \'%'+String.escapeSingleQuotes(searchKey)+'%\''
                +'OR RecordType.Name like \'%'+String.escapeSingleQuotes(searchKey)+'%\''
                +'OR Name like \'%'+String.escapeSingleQuotes(searchKey)+'%\''
                +'OR Credit_Financial_Account__r.Name like \'%'+String.escapeSingleQuotes(searchKey)+'%\''
                +'OR Debit_Financial_Account__r.Name like \'%'+String.escapeSingleQuotes(searchKey)+'%\''
                +'OR Charge_Item__r.Name like \'%'+String.escapeSingleQuotes(searchKey)+'%\''
                +'OR GlobalType__c = \''+String.escapeSingleQuotes(searchKey)+'\')';
        }
        
        
        integer i = 0;
        String str = 'order[0][column]';
        //set order by clause , this code is assume that the sorting is on only one field but this can modified accordingly
        //also set direction from order[0][dir] parameter. note: we are taking 0th element as we have assume only one sorting
        if(ApexPages.currentPage().getParameters().get('columns['+ApexPages.currentPage().getParameters().get(str) + '][data]')!='null')
        queryString += ' ORDER BY ' + String.escapeSingleQuotes(ApexPages.currentPage().getParameters().get('columns['+ApexPages.currentPage().getParameters().get(str) + '][data]'))
                        + ' ' + String.escapeSingleQuotes(ApexPages.currentPage().getParameters().get('order[0][dir]'));
        
        
        //get starting record number for current view, this parametter will be send by datatable js
        start= Integer.valueOf(ApexPages.currentPage().getParameters().get('start'));
        
        //current number of records per page, it is also in avilable in get request
        size = Integer.valueOf(ApexPages.currentPage().getParameters().get('length'));

        queryString += ' limit '+size+' OFFSET '+start;
        System.debug(':::queryString: '+queryString);
        noOfRecords= [select Count() from TXN__c where Bill_To_Member__c=:billToMember 
                      AND RecordType.Name != 'Taxes' AND RecordType.Name != 'Service Charges' AND RecordType.Name != 'Deposit'
                      AND Date__c >=: startDate AND Date__c <=: endDate limit 50000];
        //set pagenumber
        contactList = Database.query(queryString);
        
         //create wrapper
        datawrap = new DataTableWrapper(0,noOfRecords,noOfRecords,contactList );
        dataTableJson = JSON.serialize(datawrap);         
    }

    public String getCurrentAccountBalanceFormat() {
        Decimal conTotal = 0.00;
        if (con.Total_Calculated_Account_Balance__c != null) conTotal = con.Total_Calculated_Account_Balance__c.setScale(2);         
            String formatedCurrency = Utility.formatCurrency(conTotal);

            if(conTotal<0)
            {
                return '($'+formatedCurrency.substring(1)+')';
            }

            return '$'+formatedCurrency;
    }
    
    public class DataTableWrapper{
        public Integer draw;
        public Integer recordsTotal;
        public Integer recordsFiltered;
        public List<sobject> data;
        public DataTableWrapper(Integer draw,Integer recordsTotal,Integer recordsFiltered,list<TXN__c> data){
            this.draw = draw;
            this.recordsTotal = recordsTotal;
            this.recordsFiltered = recordsFiltered ;
            this.data = data;
        }
    }
}

VF页面：

<apex:page doctype="html-5.0" controller="AccountHistory2" tabstyle="Account" sidebar="false" readonly="true" cache="false" showheader="true" standardstylesheets="false">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"/>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.7.1/css/bootstrap-datepicker.min.css"/>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css"/>
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.6.2/css/buttons.dataTables.min.css"/>
    
    
    <script src="http://code.jquery.com/jquery-3.2.1.min.js" integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4=" crossorigin="anonymous"></script>
    <script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery.mask/1.14.11/jquery.mask.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.7.1/js/bootstrap-datepicker.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js"></script>
    <apex:includeScript value="https://code.jquery.com/jquery-3.5.1.js"/>
    <apex:includeScript value="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"/>
    <apex:includeScript value="https://cdn.datatables.net/buttons/1.6.2/js/dataTables.buttons.min.js"/>
    <apex:includeScript value="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"/>
    <apex:includeScript value="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"/>
    <apex:includeScript value="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"/>
    <apex:includeScript value="https://cdn.datatables.net/buttons/1.6.2/js/buttons.html5.min.js"/>
    
    
    <apex:form >
        <p style="margin: 10px;">
            <a href="/{!con.Id}">{!con.Name}</a>     
        </p>
        <apex:sectionheader title="Account History" subtitle="{!con.Name}" help="{!con.Id}"/>
        <apex:pageblock id="AccDeets">
            <apex:pageBlockSection title="Account Details">
                <apex:pageBlockSectionItem >
                    Current Account Balance:
                    <apex:outputText value="{!CurrentAccountBalanceFormat}">
                    </apex:outputText>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem >
                    Period Total: 
                    <apex:outputText value="{!sTotalAmount}" />
                </apex:pageBlockSectionItem>
            </apex:pageBlockSection>
        </apex:pageblock>
        <apex:pageBlock >
            <apex:pageBlockSection id="dates1" title="Filter by Date">
               <apex:pageBlockSectionItem >
                    Start Date: <input type="text" id="min" class="datepicker" value="{!startDate}"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem >
                    End Date: <input type="text" id="max" class="datepicker" value="{!endDate}"/>
                </apex:pageBlockSectionItem>
                 <!--<div class="col-md-8">
                    <div class="input-group input-daterange">
                        <input type="text" class="form-control date-range-filter" value="{!startDate}" placeholder="Date Start" data-date-format="mm-dd-yyyy" id="min" />
                        <span class="input-group-addon">to</span>
                        <input type="text" class="form-control date-range-filter" value="{!endDate}" placeholder="Date End" data-date-format="mm-dd-yyyy" id="max"/>
                    </div>
                </div>-->
                
            </apex:pageBlockSection>
            <input type='button' id="btn_search" value="Search"/>
        </apex:pageBlock>
        <table cellspacing="0" class="display" id="example" style="width: 100%px;">
            <thead>
                <tr>
                    <th style="text-align:center">Date</th>
                    <th style="text-align:center">Customer</th>
                    <th style="text-align:center">Record Type</th>
                    <th style="text-align:center">Transaction</th>
                    <th style="text-align:center">Type</th>
                    <th style="text-align:center">Debit</th>
                    <th style="text-align:center">Credit</th>
                    <th style="text-align:center">Statement Description</th>
                    <th style="text-align:center">Charge Item</th>
                    <th style="text-align:center">Ticket Number</th>
                    <th style="text-align:center">Amount</th>
                    <th style="text-align:center">Tax</th>
                    <th style="text-align:center">Service Charge</th>
                    <th style="text-align:center">Total</th>
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th style="text-align:right"></th>
                    <th style="text-align:right"></th>
                    <th></th>
                    <th style="text-align:right"></th>
                    <th style="text-align:right"></th>
                </tr>
            </tfoot>
        </table>
        
    </apex:form>
    <script>
    
    var table;    
    $(document).ready(function() {
        $.fn.dataTableExt.sErrMode = 'console';
        
        $('.input-daterange input').each(function() {
            $(this).datepicker('clearDates');
        });
        
        // Extend dataTables search
        $.fn.dataTable.ext.search.push(
            function(settings, data, dataIndex) {
                var min = $('#min').val();
                var max = $('#max').val();
                var createdAt = data[0] || 0; // Our date column in the table
                
                if (
                    (min == "" || max == "") ||
                    (moment(createdAt).isSameOrAfter(min) && moment(createdAt).isSameOrBefore(max))
                ) {
                    return true;
                }
                return false;
            }
        );
        
        // Re-draw the table when the a date range filter changes
        $('.date-range-filter').change(function() {
            var table = $('#data-table').DataTable();
            table.draw();
        });
        
        
        $('.date-range-filter').datepicker();
        
        table = $('#example').dataTable( {
            "destroy": true,
            "footerCallback": function ( row, data, start, end, display ) {
                var api = this.api(), data;
                
                // converting to interger to find total
                var intVal = function ( i ) {
                    return typeof i === 'string' ?
                        i.replace(/[\$,]/g, '')*1 :
                    typeof i === 'number' ?
                        i : 0;
                };
                
                // computing column Total of the complete result 
                var col10Total = api
                .column( 10 )
                .data()
                .reduce( function (a, b) {
                    return intVal(Math.abs(a)) + intVal(Math.abs(b));
                }, 0 );
                
                var col13Total = api
                .column( 13 )
                .data()
                .reduce( function (a, b) {
                    return intVal(Math.abs(a)) + intVal(Math.abs(b));
                }, 0 );
                
                // Update footer by showing the total with the reference of the column index
                var num = $.fn.dataTable.render.number(',', '.', 2, '$').display(col10Total);
                var num2 = $.fn.dataTable.render.number(',', '.', 2, '$').display(col13Total);
                $( api.column( 9 ).footer() ).html('Subtotal: ');
                $( api.column( 10 ).footer() ).html(num);
                $( api.column( 12 ).footer() ).html('Total: ');
                $( api.column( 13 ).footer() ).html(num2);
            },
            "pagingType": "simple_numbers",
            "aaSorting": [[0, 'desc']],
            dom: 'Blfrtip', // Add the Copy, Print and export to CSV, Excel and PDF buttons
            buttons: [
                'copy', 'csv', 'excel', 'pdf', 'print'
            ],
            "bjQueryUI":true,
            "bProcessing": true,
            "bServerSide": true,
            "ajax": "{!$Page.AccountHistoryTableHelper}?id={!$CurrentPage.parameters.id}",
            
            /**"ajax": {
                "url": "{!$Page.AccountHistoryTableHelper}?core.apexpages.devmode.url=1&id={!$CurrentPage.parameters.id}",
                "data": function (dtParms) {
                    var startDate = "{!startDate}";
                    var endDate = "{!endDate}";
                    dtParms.startDate = moment(startDate).format("MM-DD-YYYY");
                    dtParms.endDate = moment(endDate).format("MM-DD-YYYY");
                    //dtParms.minDate = $('#min').val();
                    //dtParms.maxDate = $('#max').val();
                    return dtParms
                },
                
            },*/
            "columnDefs": [{ targets: [10, 11, 12, 13], className: 'dt-body-right' },
                           { targets: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9], className: 'dt-body-center' }],
            "columns": [
                { "data": "Date__c",
                 render: function(data, type, row){
                     //console.log(data);
                     if(type === "sort" || type === "type"){
                         return data;
                     }
                     return moment(data).format("MM-DD-YYYY");
                 }
                },
                { "data": "Bill_To_Member__r.Name" },
                { "data": "RecordType.Name" },
                { "data": "Name" },
                { "data": "GlobalType__c" },
                { "data": "Debit_Financial_Account__r.Name" },
                { "data": "Credit_Financial_Account__r.Name" },
                { "data": "Statement_Description__c" },
                { "data": "Charge_Item__r.Name" },
                { "data": "Ticket_Number__c" },
                { "data": "Amount__c",render: $.fn.dataTable.render.number(',', '.', 2, '$')},
                { "data": "Tax__c", render: $.fn.dataTable.render.number(',', '.', 2, '$')},
                { "data": "Service_Charge__c",render: $.fn.dataTable.render.number(',', '.', 2, '$')},
                { "data": "Total_Amount__c",render: $.fn.dataTable.render.number(',', '.', 2, '$')}     
            ]
        });
        
        $("#btn_search").click(function () {
            var table = $('#example').DataTable();
            //console.log('startDate: {!startDate}');
            table.draw();
        });
        
        $( function() {
            var date = new Date(); 
            var firstDay = new Date(date.getFullYear(), date.getMonth() , 1);
            var lastDay = new Date(date.getFullYear(), date.getMonth() + 1, 0);
            $( ".datepicker" ).datepicker();
            $('#min').val( ('0' + (firstDay.getMonth() + 1)).slice(-2) + '/' + ('0' +firstDay.getDate()).slice(-2)  + '/' + firstDay.getFullYear());
            $('#max').val( ('0' + (lastDay.getMonth() + 1)).slice(-2) + '/' + ('0' +lastDay.getDate()).slice(-2)  + '/' + lastDay.getFullYear());
            table.draw();
            
        });
         $('#min').change( function() { table.draw(); } );
         $('#max').change( function() { table.draw(); } );
    });
    
    $.fn.DataTable.ext.pager.numbers_length = 10;
    
    </script>
</apex:page>

我尝试了我能找到的不同方法，但最终似乎行不通。