jQuery/CF database textarea Fill based on dates in database

Question

I have a table with Date_Due and Date_Complete. My goal is to have two text areas one for today and one for tomorrow (tomorrow does not include holidays or weekends, so if on Friday its the next business day normally Monday unless there is a holiday on Monday). I am using CF to query the sql database. In my text areas I am trying to figure out how to show my #id# and my #Item_Count# like this 123 ... 6 Items. But the tricky part that I am struggling on is 1.) Having them only show up on the correct date and 2.) If there is a completed date do not show either.

The first text area would be based on Date_Due so anything do "today" will show up in this text area as long as there is not already a Date_Complete in the database as well. It only needs to show if it is not completed yet.

Then the second text area is for tomorrow which is a little bit more difficult because its more than just plus one it takes into consideration for holidays and weekends as well. I already have the label functioning properly so textarea date needs to match the span id of delivery-date (date). Here is an example of that http://jsfiddle.net/byyeh83t/7/

Any help with this would be greatly appreciated.

I also create a hidden input that has the correct date that should be inserted.

<input id="TomorrowsDate" type="text"/>

function setDeliveryDate(date) {
    $('#delivery-date').text($.datepicker.formatDate('mm/dd/yy', date));
    $('#TomorrowsDate').val($.datepicker.formatDate('mm/dd/yy', date));
}


setDeliveryDate(AddBusinessDays(dateMin, 1));

Text Areas

<div class="col-xs-6">
<div class="row">
  <div id="DToday" class="col-xs-12">
      <label class="centered" for="DueToday">DUE TODAY @ 5:00</label>
      <textarea type="text" name="DueToday" id="DueToday" class="form-control" rows="7"><cfoutput query="processTable">#id# ... #Item_Count# ITEMS &##13;&##10;</cfoutput></textarea>
  </div>
  <div id="DTmrw" class="col-xs-12">
      <label class="centered" for="DueTmrw">DUE <span id="delivery-date"></span> @ 5:00</label>
      <textarea type="text" name="DueTmrw" id="DueTmrw" class="form-control" rows="7"><cfoutput query="processTable">#id# ... #Item_Count# ITEMS &##13;&##10;</cfoutput></textarea>
  </div>
</div>

Table

My CFC how I get the table using CF

<cffunction name="displayTable" access="public" returntype="query">
    <cfset var processTable = ''>
    <cfquery name="processTable">
        SELECT *
        FROM dbo.Dealer_Track_Work, dbo.Dealer_Track_Dealers
        WHERE dbo.Dealer_Track_Work.dealerID = dbo.Dealer_Track_Dealers.id      
    </cfquery>
    <cfreturn processTable>
</cffunction>

UPDATE UPDATE UPDATE I am trying to figure out how to have a hidden field fill in a cfparam in my query. My hidden field automatically has the correct date done because of JQuery but I need to know how to pass the form.value to the param since nothing is being submitted or inserted. Its just a hidden field with the correct date already that I need to some how automatically pass to the query inside of the param.

http://jsfiddle.net/byyeh83t/8/

The fiddle shows the input automatically receiving the date. But since nothing is being submitted and there is no button press or anything (since it is a single page application) how do I take the form.value and insert that into the param in my query?

<input name="TomorrowsDate" id="TomorrowsDate" type="hidden"/>

    <cfquery name="tomorrowTextArea">
            SELECT *
            FROM dbo.Dealer_Track_Work
            WHERE Date_Due = <cfqueryparam value="form.TomorrowsDate" /> 
            AND Date_Complete IS NULL       
        </cfquery>

Answer 1

Based on this chat: https://chat.stackoverflow.com/rooms/93528/discussion-between-vicki-and-beloitdavisja

JS

var Tmrwdate = $('#TomorrowsDate').val(); 

$.ajax({
    url: "proxy/TomorrowsDate.cfm",
    type: "post",
    dataType: "json",
    data: {date: Tmrwdate },
    success: function (data) {
        console.log(data);
        $('#DueTmrw').val(data.TEXT);
    }, 
    error: function (xhr, textStatus, errorThrown) {
        console.log(errorThrown);
    }
});

CF

<!--- Create a result ---> 
<cfset result = {} /> 
<cftry> 
    <cfset session.dealerwork.tomorrowsdate = form.date> 
    <cfset result.TomorrowsDate = form.date/> 

    <cfquery name="tomorrowTextArea"> 
        SELECT * 
        FROM dbo.Dealer_Track_Work 
        WHERE Date_Due = <cfqueryparam value="#session.dealerwork.tomorrowsdate#" /> 
        AND Date_Complete IS NULL 
    </cfquery> 

    <cfset result.text = ''>
    <cfloop query="tomorrowTextArea">
        <cfset result.text = result.text & "#id# ... #Item_Count# ITEMS #chr(13)##chr(10)#" />
    </cfloop>



    <cfcatch type="any"> 
        <cfset result.error = CFCATCH.message > 
        <cfset result.detail = CFCATCH.detail > 
    </cfcatch> 
</cftry> 

<cfoutput>#SerializeJSON(result)#</cfoutput>

Please note: use the cfsqltype attribute in your cfqueryparam tags for better security.

Answer 2

You can easily filter the entries you need in the whereclause of your SQL.

        <cfquery name="processTable">
            SELECT *
            FROM dbo.Dealer_Track_Work, dbo.Dealer_Track_Dealers
            WHERE dbo.Dealer_Track_Work.dealerID = dbo.Dealer_Track_Dealers.id
            AND dbo.Date_Due = CONVERT(date, getdate()) AND dbo.Date_Complete IS NULL 
       </cfquery>

For your second textarea you can adjust the SQL again.. something like AND dbo.Due_Date > CONVERT(date, getdate())