Add Reports Query Engine

[JaminQuimby]

Changes to ALL reports see payroll check reports for a working demo.

[CFC] /Drop in code block between FROM TableName and <cfquery You will have to remove the OLD from block. Set a simple csv list of all table columns in sqllist. add a prefix example the search phrase was year the sql column name is pccolumn the prefix would be pc /

<cfset sqllist = "pc_year,pc_payenddate,pc_paydate,pc_datedue,pc_esttime,pt_altfreq,pc_missinginfo,pc_missinginforeceived,pc_fees,pc_paid,pc_deliverymethod,pc_obtaininfo_assignedto,pc_obtaininfo_datecompleted,pc_obtaininfo_completedby,pc_obtaininfo_esttime,pc_preparation_assignedto,pc_preparation_datecompleted,pc_preparation_completedby,pc_preparation_esttime,pc_review_assignedto,pc_review_datecompleted,pc_review_completedby,pc_review_esttime,pc_assembly_assignedto,pc_assembly_datecompleted,pc_assembly_completedby,pc_assembly_esttime,pc_delivery_assignedto,pc_delivery_datecompleted,pc_delivery_completedby,pc_delivery_esttime">
<cfset key="pc_">

<cfif IsJSON(SerializeJSON(#ARGUMENTS.search#))>
<cfset data=#ARGUMENTS.search#>
<cfif ArrayLen(data.b) gt 0>
WHERE(1)=(1)
<cfloop array="#data.b#" index="i">
    <cfif #i.t# eq "NONE">AND((1)=(1)
        <cfloop array="#i.g#" index="g">
            <cfloop list="#sqllist#" index="list">
                    <cfif list eq key&g.n><cfif #g.v# neq "null">AND[#list#]='#g.v#'<cfelse>AND[#list#]IS NULL</cfif></cfif>
                    <cfif list&'_less' eq key&g.n>AND[#list#]<='#g.v#'</cfif>
                    <cfif list&'_more' eq key&g.n>AND[#list#]>='#g.v#'</cfif>
                    <cfif list&'_not' eq key&g.n><cfif #g.v# neq "null">AND[#list#]<>'#g.v#'<cfelse>AND[#list#]IS NOT NULL</cfif></cfif>
            </cfloop>
        </cfloop>)
    </cfif>
    <cfif #i.t# eq "AND">AND((1)=(1)
        <cfloop array="#i.g#" index="g">
            <cfloop list="#sqllist#" index="list">
                    <cfif list eq key&g.n><cfif #g.v# neq "null">AND[#list#]='#g.v#'<cfelse>AND[#list#]IS NULL</cfif></cfif>
                    <cfif list&'_less' eq key&g.n>AND[#list#]<='#g.v#'</cfif>
                    <cfif list&'_more' eq key&g.n>AND[#list#]>='#g.v#'</cfif>
                    <cfif list&'_not' eq key&g.n><cfif #g.v# neq "null">AND[#list#]<>'#g.v#'<cfelse>AND[#list#]IS NOT NULL</cfif></cfif>
            </cfloop>
        </cfloop>)
    </cfif>
    <cfif #i.t# eq "OR">OR((1)=(1)
        <cfloop array="#i.g#" index="g">
            <cfloop list="#sqllist#" index="list">
                    <cfif list eq key&g.n><cfif #g.v# neq "null">AND[#list#]='#g.v#'<cfelse>AND[#list#]IS NULL</cfif></cfif>
                    <cfif list&'_less' eq key&g.n>AND[#list#]<='#g.v#'</cfif>
                    <cfif list&'_more' eq key&g.n>AND[#list#]>='#g.v#'</cfif>
                    <cfif list&'_not' eq key&g.n><cfif #g.v# neq "null">AND[#list#]<>'#g.v#'<cfelse>AND[#list#]IS NOT NULL</cfif></cfif>
            </cfloop>
        </cfloop>)
    </cfif>
</cfloop>
</cfif>
</cfif>

[CFM]

<cfset page.menuLeft_report="Yes">

/* Need to change the Search String this is not a widget you will have to change g0_filter to the correct name. and the _grid1() keypress. */

<div><label for="g0_filter">Filter</label><span class="search-bar"><input id="g0_filter" onBlur="_grid1();" onKeyPress="if(event.keyCode==13){_grid1();}"/></span><span class="search-bar search-btn">&nbsp;</span><div class="search-tog"><div class="search-togcan"><div>Methods:<br/><ul></ul></div><div></div><div class="search-togfooter" onClick="$('.search-tog').slideToggle('fast');">&and;</div></div></div><span class="search-togbtn" onClick="$('.search-tog').slideToggle('fast');">&or;</span></div>

[JS] /* Sample: Start with a search row then add every table field minus the prefix; n for name, v for default value. Example pc_altrefq will be altrefq */

var grid1_config = [
{"n":"search","type":"text","v":""}
,{"n":"altfreq","t":"boolean","v":""}
,{"n":"assembly_assignedto","t":"date","v":""}
];
$.each(grid1_config, function(idx, obj) {$('#group0 .search-togcan div ul').append('<li>'+obj.n+' : '+obj.t+'</li>')});

/Change search argument inside jgrid/

"arguments":'{"search":'+_toReport($("#g0_filter").val(),grid1_config)+',"orderBy":"0","row":"0","ID":"'+$("#task_id").val()+'","loadType":"group0","formid":"10"}',

[JaminQuimby]

SELECT TOP 1000 [n_id]
      ,[nm_id]
      ,[n_assignedto]
      ,[n_noticestatus]
      ,[n_priority]
      ,[n_esttime]
      ,[n_1_noticenumber]
      ,[n_1_noticedate]
      ,[n_1_taxform]
      ,[n_1_taxyear]
      ,[n_1_methodreceived]
      ,[n_fees]
      ,[n_paid]
      ,[n_1_datenoticerec]
      ,[n_1_resduedate]
      ,[n_2_rescompleted]
      ,[n_2_rescompletedby]
      ,[n_2_revrequired]
      ,[n_2_revassignedto]
      ,[n_2_revcompleted]
      ,[n_2_ressubmited]
      ,[n_2_irsstateresponse]
      ,[n_missinginfo]
      ,[n_missinginforeceived]
      ,[n_deliverymethod]
  FROM [AWS].[dbo].[notice]

[JaminQuimby]

SELECT TOP 1000 [nm_id]
      ,[client_id]
      ,[nm_name]
      ,[nm_status]
  FROM [AWS].[dbo].[noticematter]