Cacher is the code snippet organizer for pro developers

BidProposalLog_Step_1.html

The HTML file associated with Reports > Bid Proposal Log.

Calls getSolicitList function, which is in BidNumberLogForm.cs.

Also calls checkType function, which is in the same form.

BidNumberLogForm.cs

getSolicitList()

getSolicitList function uses the following query to put solicitation numbers into SolNum DDL:

SELECT DISTINCT solicitation.SolicitNum as display, solicitation.SolicitNum as value 
from solicitation 
WHERE (posted = '1' and bidonlineflag = '0') and 
( --No awardnotice (not awarded) AND closedate is greater than today.
  (awardnotice is null and solicitclosedate >= sysdate) or 
--Closedate+180 days is greater than today AND 
--solicitnum is not filled in in award table.    
  (solicitclosedate + 180 > sysdate and solicitnum not in (select distinct(solicitnum) from award where solicitnum is not null)) or 
--Closedate+180 days is greater than today AND
--solicitnum is in award table where awarded = 0 or null.
  (solicitclosedate + 180 > sysdate  and solicitation.solicitnum in (select solicitnum from award where awarded = '0' or awarded is null))
) 
order by to_number(solicitnum);

• Only posted solicitations appear in results.
• Only unawarded solicitations appear in results.
• Only solicitations with solicitCloseDate is either greater than or equal to today's date, or solicitCloseDate + 180 days is greater than today's date.

checkType()

checkType function is used to determine whether a solicitation is closed or open, for displaying "OPEN" or "CLOSED" on sidebar. This is the query it runs:

select offlinetype, solicitclosedate from solicitation 
where solicitnum = '71904';

The results of this example query are as follows:

OFFLINETYPE	SOLICITCLOSEDATE
IFB	05-MAR-18
IFB	27-APR-18
IFB	05-MAR-18
IFB	27-APR-18
IFB	27-APR-18
IFB	05-MAR-18
IFB	27-APR-18
IFB	27-APR-18

There are 8 rows of data, due to there being 7 addendums to the solicitation. When an addendum pushes out solicitCloseDate, it can be inadvertently marked as "CLOSED" due to the current code logic.

Here is the relevant logical portion of checkType's code:

DateTime closeDT = DateTime.Now;
DataTable tType = db.RunQuery(sql, db.AppConnectionString).Tables[0];
/*If there's at least 1 row of data then proceed*/
if (tType.Rows.Count > 0)
{ /*If first row and first column is not null, put OFFLINETYPE in ifbrfp*/
    if (tType.Rows[0].ItemArray[0] != DBNull.Value)
        ifbrfp = (string)tType.Rows[0].ItemArray[0];
    /*If first row and second column is not null put SOLICITCLOSEDATE in closeDT*/
    if (tType.Rows[0].ItemArray[1] != DBNull.Value)
    {
        closeDT = (DateTime)tType.Rows[0].ItemArray[1];
        /*If today's date is greater than closeDT, then solicitation marked as closed*/
        if (DateTime.Now > closeDT)
            closedsolicit = "Y";
    }
}

tType.Rows[0].ItemArray[0] = IFB

tType.Rows[0].ItemArray[1] = 05-MAR-18

Query needs to be changed such that the top record (0th row) is the latest addendum, if there are addendums. Add an order by clause:

ORDER BY addendumnum desc

Also, it should use DateTime.Now >= closeDT instead. For example, on 3/15/18, a solicitation with solicitCloseDate of 3/15/18 is still open until the end of 3/15/18.