Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Auto Generate Work Order From Database

Status
Not open for further replies.
mptwoadmin

mptwoadmin

Programmer
May 15, 2006
46
US
Hi, I think i'm in need for some serious help. I am trying to write an app that will generate a work order based on the tasknumber and the next due date of the work order. I seem to be running into issues with generating the correct next due date to be inserted back into the database. Could someone please look through my code and maybe point out mistakes and any additional help that may help guide me through this process.

Thanks


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>

<link rel="stylesheet" href="/stylesheets/style.css" type="text/css">

</head>

<!--- Set date --->

<cfset month = '10'>
<cfset year = '2006'>
<cfset dept = '6581'>
<cfset a = 1>

<body>

<cfquery name="query_1" datasource="mp2_train">
<!--- numofdate is perform every field --->
SELECT DISTINCT TASK.TASKNUM, task.multitask, numofdate,task.siteid
FROM EQUIP, TASK, TASKEQ
where TASKEQ.SITEID = TASK.SITEID and TASKEQ.TASKNUM = TASK.TASKNUM and EQUIP.EQNUM = TASKEQ.EQNUM and EQUIP.SITEID = TASKEQ.SITEID
and task.siteid not like 'mex%'
and task.description not like '%meter%'
and task.siteid IN ('FORKS - I')
AND NUMOFDATE IS NOT NULL
and equip.inservice = 'y'
and task.inservtask = 'y'
and datepart(month, nextduedate) = '#month#'
and datepart(year, nextduedate) = '#year#'
and equip.dept = '#dept#'
order by task.tasknum
</cfquery>

<!------------------------------------->
<!--- Release Work Orders Separatly --->
<!------------------------------------->

<table bordercolor="#000000" border="1">
<strong><font><td>Release as separate work orders</td>
<br>
</font></strong>
</table>

<cfloop query = "query_1">

<cfif #query_1.multitask# eq 'n'>

<cfquery name="query_2" datasource="mp2_train">
SELECT DISTINCT TASK.TASKNUM, TASK.DESCRIPTION, TASK.SITEID, TASK.WOTYPE, TASKEQ.NEXTDUEDATE, TASK.ATLASTNAME, taskeq.eqnum,task.multitask,task.atfirstname, task.assignedto,taskeq.NUMOFDATE,taskeq.dateunit,equip.dept,taskeq.location,taskeq.sublocation1,taskeq.sublocation2,taskeq.sublocation3,equip.DESCRIPTION as e_desc,equip.costcenter,task.atfirstname, TASK.ATLASTNAME, task.assignedto
FROM EQUIP, TASK, TASKEQ
where TASKEQ.SITEID = TASK.SITEID and TASKEQ.TASKNUM = TASK.TASKNUM and EQUIP.EQNUM = TASKEQ.EQNUM and EQUIP.SITEID = TASKEQ.SITEID
and task.tasknum ='#query_1.tasknum#'
and task.siteid IN ('#Rereplace(query_1.siteID,",","','","all")#')
and datepart(month, nextduedate) = #month#
and datepart(year, nextduedate) = #year#
and taskeq.nextduedate is not null
order by task.tasknum asc
</cfquery>

<cfif #query_2.recordcount# neq '0'>
<cfset startday = '#query_2.nextduedate#'>
<cfelse>
<cfset startday = 0>
</cfif>

<cfset daymonth = #daysinmonth(startday)#>
<cfset month = datepart("m", startday)>
<cfset year = datepart("yyyy", startday)>
<cfset endday = '#month#/#daymonth#/#year#'>
<cfset diff = #datediff("d", startday, endday)#>

<cfset computate_weeks = #diff#/7>

<cfset week = 7 * #query_1.NUMOFDATE#>

<cfloop from = 1 to = #NUMBERFORMAT(computate_weeks,"_")# index= step>

<cfif datepart("d", startday) eq 1>
<cfset a = 1>
</cfif>

<cfset days = #dateadd("d", week ,startday)#>

<cfif #dateformat(days,"mm/dd/yyyy")# lte '#month#/#daymonth#/#year#'>
<cfset a = a + 1>
</cfif>

<cfset startday = #days#>

</cfloop>


<cfif #query_2.dateunit# eq 'd' or #query_2.dateunit# eq 'mon' or #query_2.dateunit# eq 'w' or #query_2.dateunit# eq 'tue' or #query_2.dateunit# eq 'wed' or #query_2.dateunit# eq 'thu' or #query_2.dateunit# eq 'fri' or #query_2.dateunit# eq 'sat' or #query_2.dateunit# eq 'sun'>

<cfset dateunit_freq = #a#>

<cfelse>

<cfset dateunit_freq = 1>

</cfif>

<cfset start_count = 0>
<cfset end_date = 0>
<cfset final_end_date = 0>
<cfset next_due_date_d = 0>
<cfset eq_count = 0>

<cfloop from="1" to=#dateunit_freq# index="test">

<cfset start_count = start_count + 1>

<cfset dd = #datepart("d",endday)# - #next_due_date_d#>

<cfif dd gte 7>

<font>Tasknum = <cfoutput>#query_2.tasknum# - #query_2.description#</cfoutput></font><br>

<cfloop query="query_2">

<cfset eq_count = eq_count + 1>

<cfquery name="query_4" datasource="mp2_train">
SELECT DISTINCT TASKEQ.NEXTDUEDATE, taskeq.NUMOFDATE
FROM EQUIP, TASK, TASKEQ
where TASKEQ.SITEID = TASK.SITEID and TASKEQ.TASKNUM = TASK.TASKNUM and EQUIP.EQNUM = TASKEQ.EQNUM and EQUIP.SITEID = TASKEQ.SITEID
and task.tasknum ='#query_2.tasknum#'
and task.siteid IN ('#Rereplace(query_2.siteID,",","','","all")#')
and datepart(month, nextduedate) = #month#
and datepart(year, nextduedate) = #year#
</cfquery>

<cfif query_4.recordcount neq'0'>

<!--- set next due date by month --->

<cfif #query_2.dateunit# eq 'm'>
<cfset next_due_date_m = #month(dateadd("m",query_4.numofdate,query_4.nextduedate))#>
<cfset next_due_date_d = #day(dateadd("m",query_4.numofdate,query_4.nextduedate))#>
<cfset next_due_date_y = #year(dateadd("m",query_4.numofdate,query_4.nextduedate))#>
</cfif>

<!--- set next due date by day --->

<cfif #query_2.dateunit# eq 'd'>
<cfset next_due_date_m = #month(dateadd("d",query_4.numofdate,query_4.nextduedate))#>
<cfset next_due_date_d = #day(dateadd("d",query_4.numofdate,query_4.nextduedate))#>
<cfset next_due_date_y = #year(dateadd("d",query_4.numofdate,query_4.nextduedate))#>
</cfif>

<!--- set next due date by year --->

<cfif #query_2.dateunit# eq 'y'>
<cfset next_due_date_m = #month(dateadd("d",0,query_4.nextduedate))#>
<cfset next_due_date_d = #day(dateadd("d",0,query_4.nextduedate))#>
<cfset next_due_date_y = #year(dateadd("yyyy",query_4.numofdate,query_4.nextduedate))#>
</cfif>

<!--- set next due date by day of the week--->

<cfset week = 6 * #query_4.NUMOFDATE#>

<cfif #query_2.dateunit# eq 'mon' or #query_2.dateunit# eq 'w' or #query_2.dateunit# eq 'tue' or #query_2.dateunit# eq 'wed' or #query_2.dateunit# eq 'thu' or #query_2.dateunit# eq 'fri' or #query_2.dateunit# eq 'sat' or #query_2.dateunit# eq 'sun'>
<cfif #start_count# eq 1 and #final_end_date# eq 0>
<cfset week = 6 * #query_2.NUMOFDATE#>
<cfelseif #start_count# eq 1 and #next_due_date_d# gte 2>
<cfset week = 6 * #query_2.NUMOFDATE#>
<cfelse>
<cfset week = 6 * #query_2.NUMOFDATE#>
</cfif>
<cfset next_due_date_m = #month(dateadd("d",week,query_4.nextduedate))#>
<cfset next_due_date_d = #day(dateadd("d",week,query_4.nextduedate))#>
<cfset next_due_date_y = #year(dateadd("d",week,query_4.nextduedate))#>
</cfif>

<cfquery name="query_3" datasource="mp2_train">
SELECT siteid,nextwo
from wosinfo
where siteid = '#query_2.siteid#'
</cfquery>

<cfquery datasource="mp2_train">
BEGIN TRAN
insert into wo(wonum, printit,taskdesc,priority,status,PERFBYWARRANTY,picklist,closedate,siteid,requestdate,tasknum,wotype,schedstartdate,requesttime,atfirstname,atlastname,assignedby,abfirstname,ablastname,assignedto, schedfinishdate)
values (
'#query_3.nextwo#', <!--- work order number --->
'#UCASE ("y")#', <!--- printit --->
'#query_2.description#',<!--- task description --->
'#UCASE ("1")#',<!--- priority --->
'#UCASE ("o")#',<!--- status --->
'#UCASE ("n")#',<!--- perform by warrenty --->
'#UCASE ("n")#',<!--- pick list --->
'1/1/1900',<!--- closedate --->
'#query_2.siteid#',<!--- site id --->
'#dateformat(now(),"mm/dd/yyyy")#',<!--- request date --->
'#query_2.tasknum#',<!--- tasknumber --->
'#query_2.wotype#',<!--- work order type --->
'#dateformat(query_4.nextduedate,"mm/dd/yyyy")#', <!--- next work order schedstartdate --->
'#timeformat(now(),"hh:mm:ss")#', <!--- work order request time --->

<cfif #query_2.atfirstname# eq ''>
'N/A',
<cfelse>
'#query_2.atfirstname#', <!--- assigned to firstname --->
</cfif>

<cfif #query_2.atlastname# eq ''>
'N/A',
<cfelse>
'#query_2.atlastname#',<!--- assigned to lastname --->
</cfif>

'1713', <!--- assigned by code--->
'Leslie', <!--- assigned by firstname--->
'Carter', <!--- assigned by lastname--->

<cfif #query_2.assignedto# eq ''>
'N/A',
<cfelse>
'#query_2.assignedto#', <!--- assigned to code --->
</cfif>
'#next_due_date_m#/#next_due_date_d#/#next_due_date_y#' <!--- next work order schedfinishdate --->
)
COMMIT TRAN
</cfquery>

<cfquery datasource="mp2_train">
BEGIN TRAN
insert into woeqlist(wonum, mustbedown,underwarranty,eqnum,department,siteid,closedate,location,sublocation1,sublocation2,sublocation3,costcenter,eqdesc)
values (
'#query_3.nextwo#', <!--- work order number --->
'#UCASE ("y")#', <!--- must be down --->
'#UCASE ("y")#', <!--- underwarranty --->
'#query_2.eqnum#', <!--- eqnumber --->
'#query_2.dept#', <!--- eqnumber --->
'#query_2.siteid#', <!--- site id --->
'1/1/1900', <!--- close date --->
'#query_2.location#', <!--- location --->
'#query_2.sublocation1#', <!--- sublcation 1 --->
'#query_2.sublocation2#', <!--- sublcation 2 --->
'#query_2.sublocation3#', <!--- sublcation 3 --->
'#query_2.costcenter#', <!--- costcenter --->
'#query_2.e_desc#' <!--- description --->
)
COMMIT TRAN
</cfquery>

<!--- Set next work order number --->

<cfset nextwonum = #right(query_3.nextwo,8)#+1>
<cfset front_wonum = #left(query_3.nextwo,2)#>

<!--- update next wonum --->

<cfquery datasource="mp2_train">
BEGIN TRAN
update wosinfo
set nextwo = '#front_wonum##nextwonum#'
where siteid = '#query_2.siteid#'
COMMIT TRAN
</cfquery>

<font>Eqnum = <cfoutput>#query_2.eqnum# Wo = #query_3.nextwo# - Startdate = #dateformat(query_4.nextduedate,"mm/dd/yyyy")# - Nextduedate = #next_due_date_m#/#next_due_date_d#/#next_due_date_y#</cfoutput></font><br>

<cfif #start_count# gte 1>
<cfset next_due_date_d = #next_due_date_d# + 1>
</cfif>

<cfset final_end_date = "#next_due_date_m#/#next_due_date_d#/#next_due_date_y#">

<cfquery datasource="mp2_train">
begin tran
update taskeq

<cfif #daymonth# eq 28>

<cfif #datepart("d",final_end_date)# gte '22'>
<cfset new_date = dateadd("d", 7, final_end_date)>
set nextduedate = '#dateformat(new_date,"mm/dd/yy")#'
<cfelse>
set nextduedate = '#next_due_date_m#/#next_due_date_d#/#next_due_date_y#'
</cfif>

<cfelseif #daymonth# eq 30>

<cfif #datepart("d",final_end_date)# gte '24'>
<cfset new_date = dateadd("d", 7, final_end_date)>
set nextduedate = '#dateformat(new_date,"mm/dd/yy")#'
<cfelse>
set nextduedate = '#next_due_date_m#/#next_due_date_d#/#next_due_date_y#'
</cfif>

<cfelseif #daymonth# eq 31>

<cfif #datepart("d",final_end_date)# gte '25'>
<cfset new_date = dateadd("d", 7, final_end_date)>
set nextduedate = '#dateformat(new_date,"mm/dd/yy")#'
<cfelse>
set nextduedate = '#next_due_date_m#/#next_due_date_d#/#next_due_date_y#'
</cfif>

<cfelse>

set nextduedate = '#next_due_date_m#/#next_due_date_d#/#next_due_date_y#'

</cfif>

where tasknum = '#query_2.tasknum#'
and eqnum = '#query_2.eqnum#'
commit tran

</cfquery>
</cfif>
</cfloop>

</cfif>

<br>

<cfset eq_count = 0>

</cfloop>

<br>

</cfif>

</cfloop>

<br>

<!------------------------------------->
<!--- Release Work Orders as Single --->
<!------------------------------------->

<table bordercolor="#000000" border="1">
<strong><font>
<td>Release together as single work order</td>
</font></strong>
</table><br>

<cfloop query = "query_1">

<cfif #query_1.multitask# eq 'y'>

<cfquery name="query_2" datasource="mp2_train">
SELECT DISTINCT TASK.TASKNUM, TASK.DESCRIPTION, TASK.SITEID, TASK.WOTYPE, TASKEQ.NEXTDUEDATE, TASK.ATLASTNAME, taskeq.eqnum,task.multitask,task.atfirstname, task.assignedto,taskeq.NUMOFDATE,taskeq.dateunit,equip.dept,taskeq.location,taskeq.sublocation1,taskeq.sublocation2,taskeq.sublocation3,equip.DESCRIPTION as e_desc,equip.costcenter,task.atfirstname, TASK.ATLASTNAME, task.assignedto
FROM EQUIP, TASK, TASKEQ
where TASKEQ.SITEID = TASK.SITEID and TASKEQ.TASKNUM = TASK.TASKNUM and EQUIP.EQNUM = TASKEQ.EQNUM and EQUIP.SITEID = TASKEQ.SITEID
and task.tasknum ='#query_1.tasknum#'
and task.siteid IN ('#Rereplace(query_1.siteID,",","','","all")#')
and datepart(month, nextduedate) = #month#
and datepart(year, nextduedate) = #year#
and taskeq.nextduedate is not null
order by task.tasknum asc
</cfquery>

<cfif #query_2.recordcount# neq '0'>
<cfset startday = '#query_2.nextduedate#'>
<cfelse>
<cfset startday = 0>
</cfif>

<cfset daymonth = #daysinmonth(startday)#>
<cfset month = datepart("m", startday)>
<cfset year = datepart("yyyy", startday)>
<cfset endday = '#month#/#daymonth#/#year#'>
<cfset diff = #datediff("d", startday, endday)#>

<cfset computate_weeks = #diff#/7>

<cfset week = 7 * #query_1.NUMOFDATE#>

<cfloop from = 1 to = #NUMBERFORMAT(computate_weeks,"_")# index= step>

<cfif datepart("d", startday) eq 1>
<cfset a = 1>
</cfif>

<cfset days = #dateadd("d", week ,startday)#>

<cfif #dateformat(days,"mm/dd/yyyy")# lte '#month#/#daymonth#/#year#'>
<cfset a = a + 1>
</cfif>

<cfset startday = #days#>

</cfloop>

<cfif #query_2.dateunit# eq 'd' or #query_2.dateunit# eq 'mon' or #query_2.dateunit# eq 'w' or #query_2.dateunit# eq 'tue' or #query_2.dateunit# eq 'wed' or #query_2.dateunit# eq 'thu' or #query_2.dateunit# eq 'fri' or #query_2.dateunit# eq 'sat' or #query_2.dateunit# eq 'sun'>

<cfset dateunit_freq = #a#>

<cfelse>

<cfset dateunit_freq = 1>

</cfif>

<cfset bb = 0>
<cfset start_count = 0>
<cfset end_date = 0>
<cfset final_end_date = 0>
<cfset next_due_date_d = 1>
<cfset eq_count = 0>

<cfset ddiff = datediff("w",query_2.nextduedate,endday)>

<cfloop from="1" to=#ddiff# index="test">

<cfquery name="query_4" datasource="mp2_train">
SELECT DISTINCT TASKEQ.NEXTDUEDATE, taskeq.NUMOFDATE
FROM EQUIP, TASK, TASKEQ
where TASKEQ.SITEID = TASK.SITEID and TASKEQ.TASKNUM = TASK.TASKNUM and EQUIP.EQNUM = TASKEQ.EQNUM and EQUIP.SITEID = TASKEQ.SITEID
and task.tasknum ='#query_2.tasknum#'
and task.siteid IN ('#Rereplace(query_2.siteID,",","','","all")#')
and datepart(month, nextduedate) = #month#
and datepart(year, nextduedate) = #year#
</cfquery>

<cfif query_4.recordcount neq'0'>

<cfset dd = #datepart("d",endday)# - #datepart("d",query_4.nextduedate)#>

<cfif dd gte 7>



<cfset bb = bb + 1>

<br>
<font>Tasknum = <cfoutput>#query_2.tasknum# - #query_2.description#</cfoutput></font>
<br>

<cfquery name="query_3" datasource="mp2_train">
SELECT siteid,nextwo
from wosinfo
where siteid = '#query_1.siteid#'
</cfquery>

<!--- Set next work order number --->

<cfset nextwonum = #right(query_3.nextwo,8)#+1>
<cfset front_wonum = #left(query_3.nextwo,2)#>

<!--- update next wonum --->

<cfquery datasource="mp2_train">
BEGIN TRAN
update wosinfo
set nextwo = '#front_wonum##nextwonum#'
where siteid = '#query_1.siteid#'
COMMIT TRAN
</cfquery>

<br>

<!--- set next due date by month --->

<cfif #query_2.dateunit# eq 'm'>
<cfset next_due_date_m = #month(dateadd("m",query_4.numofdate,query_4.nextduedate))#>
<cfset next_due_date_d = #day(dateadd("m",query_4.numofdate,query_4.nextduedate))#>
<cfset next_due_date_y = #year(dateadd("m",query_4.numofdate,query_4.nextduedate))#>
</cfif>

<!--- set next due date by day --->

<cfif #query_2.dateunit# eq 'd'>
<cfset next_due_date_m = #month(dateadd("d",query_4.numofdate,query_4.nextduedate))#>
<cfset next_due_date_d = #day(dateadd("d",query_4.numofdate,query_4.nextduedate))#>
<cfset next_due_date_y = #year(dateadd("d",query_4.numofdate,query_4.nextduedate))#>
</cfif>

<!--- set next due date by year --->

<cfif #query_2.dateunit# eq 'y'>
<cfset next_due_date_m = #month(dateadd("d",0,query_4.nextduedate))#>
<cfset next_due_date_d = #day(dateadd("d",0,query_4.nextduedate))#>
<cfset next_due_date_y = #year(dateadd("yyyy",query_4.numofdate,query_4.nextduedate))#>
</cfif>

<!--- set next due date by day of the week--->

<cfset week = 6 * #query_2.NUMOFDATE#>

<cfif #query_2.dateunit# eq 'mon' or #query_2.dateunit# eq 'w' or #query_2.dateunit# eq 'tue' or #query_2.dateunit# eq 'wed' or #query_2.dateunit# eq 'thu' or #query_2.dateunit# eq 'fri' or #query_2.dateunit# eq 'sat' or #query_2.dateunit# eq 'sun'>
<cfif #start_count# eq 1 and #final_end_date# eq 0>
<cfset week = 6 * #query_2.NUMOFDATE#>
<cfelseif #start_count# eq 1 and #next_due_date_d# gte 2>
<cfset week = 6 * #query_2.NUMOFDATE#>
<cfelse>
<cfset week = 6 * #query_2.NUMOFDATE#>
</cfif>
<cfset next_due_date_m = #month(dateadd("d",week,query_4.nextduedate))#>
<cfset next_due_date_d = #day(dateadd("d",week,query_4.nextduedate))#>
<cfset next_due_date_y = #year(dateadd("d",week,query_4.nextduedate))#>
</cfif>

<!--- Insert work order header --->

<cfquery datasource="mp2_train">
BEGIN TRAN
insert into wo(wonum, printit,taskdesc,priority,status,PERFBYWARRANTY,picklist,closedate,siteid,requestdate,tasknum,wotype,schedstartdate,requesttime,atfirstname,atlastname,assignedby,abfirstname,ablastname,assignedto, schedfinishdate)
values (
'#query_3.nextwo#', <!--- work order number --->
'#UCASE ("y")#', <!--- printit --->
'#query_2.description#',<!--- task description --->
'#UCASE ("1")#',<!--- priority --->
'#UCASE ("o")#',<!--- status --->
'#UCASE ("n")#',<!--- perform by warrenty --->
'#UCASE ("n")#',<!--- pick list --->
'1/1/1900',<!--- closedate --->
'#query_2.siteid#',<!--- site id --->
'#dateformat(now(),"mm/dd/yyyy")#',<!--- request date --->
'#query_2.tasknum#',<!--- tasknumber --->
'#query_2.wotype#',<!--- work order type --->
'#dateformat(query_4.nextduedate,"mm/dd/yyyy")#', <!--- next work order schedstartdate --->
'#timeformat(now(),"hh:mm:ss")#', <!--- work order request time --->

<cfif #query_2.atfirstname# eq ''>
'N/A',
<cfelse>
'#query_2.atfirstname#', <!--- assigned to firstname --->
</cfif>

<cfif #query_2.atlastname# eq ''>
'N/A',
<cfelse>
'#query_2.atlastname#',<!--- assigned to lastname --->
</cfif>

'1234', <!--- assigned by code--->
'mp2', <!--- assigned by firstname--->
'admin', <!--- assigned by lastname--->

<cfif #query_2.assignedto# eq ''>
'N/A',
<cfelse>
'#query_2.assignedto#', <!--- assigned to code --->
</cfif>
'#next_due_date_m#/#next_due_date_d#/#next_due_date_y#' <!--- next work order schedfinishdate --->
)
COMMIT TRAN
</cfquery>

<cfset start_count = start_count + 1>

<font><cfoutput>Wo = #query_3.nextwo# - Startdate = #dateformat(query_4.nextduedate,"mm/dd/yyyy")# - Nextduedate = #next_due_date_m#/#next_due_date_d#/#next_due_date_y#</cfoutput></font><br>

<cfloop query="query_2">

<font><cfoutput>Eqnum - #query_2.eqnum#</cfoutput></font><br>

<!--- Insert Work Order equipment numbers --->

<cfquery datasource="mp2_train">
BEGIN TRAN
insert into woeqlist(wonum, mustbedown,underwarranty,eqnum,department,siteid,closedate,location,sublocation1,sublocation2,sublocation3,costcenter,eqdesc)
values (
'#query_3.nextwo#', <!--- work order number --->
'#UCASE ("y")#', <!--- must be down --->
'#UCASE ("y")#', <!--- underwarranty --->
'#query_2.eqnum#', <!--- eqnumber --->
'#query_2.dept#', <!--- eqnumber --->
'#query_2.siteid#', <!--- site id --->
'1/1/1900', <!--- close date --->
'#query_2.location#', <!--- location --->
'#query_2.sublocation1#', <!--- sublcation 1 --->
'#query_2.sublocation2#', <!--- sublcation 2 --->
'#query_2.sublocation3#', <!--- sublcation 3 --->
'#query_2.costcenter#', <!--- costcenter --->
'#query_2.e_desc#' <!--- description --->
)
COMMIT TRAN
</cfquery>

<cfset days_in_month = #daysinmonth(now())#>

<cfif #start_count# gte 1>
<cfif next_due_date_d lte '30' and #days_in_month# eq 31>
<cfset next_due_date_d = #next_due_date_d# + 1>
<cfelseif next_due_date_d lte '29' and #days_in_month# eq 30>
<cfset next_due_date_d = #next_due_date_d# + 1>
<cfelseif next_due_date_d lte '27' and #days_in_month# eq 28>
<cfset next_due_date_d = #next_due_date_d# + 1>
</cfif>
</cfif>

<cfset final_end_date = "#next_due_date_m#/#next_due_date_d#/#next_due_date_y#">

<cfquery datasource="mp2_train">
begin tran
update taskeq

<cfif #daymonth# eq 28>

<cfif #datepart("d",final_end_date)# gte '22'>
<cfset new_date = dateadd("d", 7, final_end_date)>
set nextduedate = '#dateformat(new_date,"mm/dd/yy")#'
<cfelse>
set nextduedate = '#next_due_date_m#/#next_due_date_d#/#next_due_date_y#'
</cfif>

<cfelseif #daymonth# eq 30>

<cfif #datepart("d",final_end_date)# gte '24'>
<cfset new_date = dateadd("d", 7, final_end_date)>
set nextduedate = '#dateformat(new_date,"mm/dd/yy")#'
<cfelse>
set nextduedate = '#next_due_date_m#/#next_due_date_d#/#next_due_date_y#'
</cfif>

<cfelseif #daymonth# eq 31>

<cfif #datepart("d",final_end_date)# gte '25'>
<cfset new_date = dateadd("d", 7, final_end_date)>
set nextduedate = '#dateformat(new_date,"mm/dd/yy")#'
<cfelse>
set nextduedate = '#next_due_date_m#/#next_due_date_d#/#next_due_date_y#'
</cfif>

<cfelse>

set nextduedate = '#next_due_date_m#/#next_due_date_d#/#next_due_date_y#'

</cfif>

where tasknum = '#query_2.tasknum#'
and eqnum = '#query_2.eqnum#'
commit tran
</cfquery>

</cfloop>

</cfif>
</cfif>

</cfloop>

<br>

</cfif>

</cfloop>

</body>
</html>
 
Sort by date Sort by votes
Is it just my browser (IE6) or is the right-hand side of this thread cut off? I don't get the horizontal bar on my screen.



____________________________________
Just Imagine.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

cohan123
  • Locked
  • Question
Hypertext Jump function not working
Replies
0
Views
207
cohan123
cohan123
ccorry
  • Locked
  • Question
FrameMaker 2015 hyperlinks in book files don't work
Replies
0
Views
84
ccorry
ccorry
lollieleaver
  • Locked
  • Question
Dreamweaver CS4 - how can I make an Access database downloadable from my site?
Replies
2
Views
89
lollieleaver
lollieleaver
lwfg
  • Locked
  • Question
getting form values from ColdFusion programn to asp.net program
Replies
2
Views
107
LyndonOHRC
LyndonOHRC
MDEJAKE
  • Locked
  • Question
autosuggest (cfc and jquery) help
Replies
0
Views
96
MDEJAKE
MDEJAKE

Part and Inventory Search

Sponsor

Back
Top