Documents, supertext=>($docsqry[0][count])); if (false && has_access(viewtaskbids))//Turned off until "Waiting for Agent - Vendor Bids" is ready for migration. PB 08/31/2015 $tabs[Bids]=Bids; if (has_access(viewvendorexpenses)) $tabs[Expenses]=Expenses; elseif (has_access(viewtaskexpenses)) $tabs[Expenses]=Expenses; if($extratabs) { $tabs[Photos]=array(title=>Photos, supertext=>($photosqry[0][count])); $tabs[Notes]=Notes; } echo " "; foreach($tabs as $tab =>$arr) { $s=""; if(strtolower($tab) == strtolower($selectedtab)) $s=selectedtab; echo ""; } echo "
 "; if(is_array($arr)) { echo $arr[title]; if($arr[supertext]) echo "  
$arr[supertext]
"; } else echo $tab; echo "
"; } function task_match_vendor($taskid,$vendorid) { $task = get_task($taskid); if($task[taskvendoridassigned]!=$vendorid) return false; if($task[invitetobid_vendorid]!=$vendorid) return false; if($task[invitetobidremove]) return false; return true; } function get_task($id) { $sql = " select tbltask.* , tblproperty.* , tblvendor.* "; if(!is_livesite()) //Preventing AMC Vendor to Agent task from migrating to live until all subtasks of mtx 21735 ready for migration then remove livesite restriction. $sql.=" , tblamcvendor.accountstaffid --Do not replace these with tblamcvendor.* because taskid if not left joined will leave more than one taskid where one is blank. , tblamcvendor.propertyassignmentid , tblamcvendor.propertyassignmentcreatedby , tblamcvendor.propertyassignmentcreateddate , tblamcvendor.propertyassignmentacceptedby , tblamcvendor.propertyassignmentaccepteddate , tblamcvendor.propertyassignmentdeniedby , tblamcvendor.propertyassignmentdenieddate , tblamcvendor.propertyassignmentrevokedby , tblamcvendor.propertyassignmentrevokeddate , tblamcvendor.propertyassignmentcompletedby , tblamcvendor.propertyassignmentcompleteddate , tblamcvendor.propertyassignmentexpiredby , tblamcvendor.propertyassignmentexpireddate , tblamcvendor.propertyassignmentexpiredmarkedby , tblamcvendor.propertyassignmentexpiredmarkeddate , tblamcvendor.propertyassignmenttsecondarystaffid , tblamcvendor.propertyassignmenthasexpireddate , tblamcvendor.amcvendorfname , tblamcvendor.amcvendorlname , tblamcvendor.amcvendorcompanyname "; $sql.=" , 'Click here' as tasklink , tbl.bidrequestid , tbl.bidrequestdate , tbl.bidrequestdeadlinedate , tbl.bidrequestemail , tbl.bidrequestcreatedby , tbl.bidrequestexpirationdate , tbl.receivedbidcount "; if(vendorid()) { $sql.=" , tbl.vendorid as invitetobid_vendorid , tbl.invitetobidremove "; } else if(companyid()) //find out if this is the most recent recurring taskid { $sql .= " , case when taskrecurdays = 0 or taskrecurring = 0 then 0 else ( select max(taskid) from tbltask a where propertyid = tbltask.propertyid and a.mastertaskid = tbltask.mastertaskid and tbltask.taskname = a.taskname and tbltask.taskrecurdays = a.taskrecurdays and a.taskrecurdays > 0 ) end as most_recent_recurringtaskid"; } $sql .= " , get_task_days(taskcompleteddate, taskdeadlinedate, taskvendorcompleteddate, taskstaffcompleteddate) as days , case when inactivate.staffid > 0 then inactivate.stafffname||' '||inactivate.stafflname when inactivate.staffid = 0 and inactivate.companyid > 0 then inactivate.companyfname||' '||inactivate.companylname else '' end as inactivatedbyname , case when completedby.staffid > 0 then completedby.stafffname||' '||completedby.stafflname when completedby.staffid = 0 and completedby.companyid > 0 then completedby.companyfname||' '||completedby.companylname else '' end as completedbyname from tbltask join tblproperty using(propertyid) left join ( select *, vendorid taskvendoridassigned from tblvendor ) tblvendor using (taskvendoridassigned) "; if(!is_livesite()) //Preventing AMC Vendor to Agent task from migrating to live until all subtasks of mtx 21735 ready for migration then remove livesite restriction. $sql.=" left join ( select * from (select taskid, propertyassignmentid from tbltaskassign) tbltaskassign join ( select * , case when propertyassignmentexpireddate is not null then propertyassignmentexpireddate when propertyassignmentexpiredmarkeddate < now() then propertyassignmentexpiredmarkeddate else null end propertyassignmenthasexpireddate from tblpropertyassignment ) tblpropertyassignment using (propertyassignmentid) join ( select 0 staffid, accountstaffid, accountstafffname amcvendorfname, accountstafflname amcvendorlname, accountname amcvendorcompanyname from tblaccountstaff join tblaccount using (accountid) union select staffid, 0 accountstaffid, stafffname amcvendorfname, stafflname amcvendorlname, companyfname||' '||companylname amcvendorcompanyname from tbllogin join tblstaff using (staffid) join tblcompany using (companyid) ) tbluserinfo using (accountstaffid, staffid) ) tblamcvendor using (taskid) "; $sql.=" left join ( select companyfname, companylname, stafffname, stafflname, loginid, companyid, staffid, loginid taskinactivatedby from tbllogin left join tblcompany using (companyid) left join tblstaff using (staffid) ) inactivate using (taskinactivatedby) left join ( select companyfname, companylname, stafffname, stafflname, loginid, companyid, staffid, loginid taskcompletedby from tbllogin left join tblcompany using (companyid) left join tblstaff using (staffid) ) completedby using (taskcompletedby) left join ( select * , (select count(1) from tblinvitetobid where bidrequestid = tblbidrequest.bidrequestid and invitetobidtextcreated is not null) as receivedbidcount from tblbidrequest "; if(vendorid()) $sql.=" join tblinvitetobid using(bidrequestid) "; $sql .= " where bidrequestexpirationdate is null and taskid = ".db_number($id); if(vendorid()) $sql.=" and tblinvitetobid.vendorid = ".vendorid(); $sql.=" order by bidrequestid desc limit 1 ) tbl using (taskid) where tbltask.taskid = ".db_number($id); if(companyid() && !stristr($_SERVER[SCRIPT_NAME],schedule)) $sql.= " and tblproperty.companyid = ".companyid(); //d($sql,'get task sql'); $arr = db_first($sql); if($arr[taskserial]) { $arr2 = unserialize($arr[taskserial]); if(is_array($arr2)) foreach($arr2 as $k=>$v) $arr[$k]=$v; } return $arr; } function get_tasks($active=-1,$propertyid=0,$vendorid=0,$taskfilter="",$taskfilter2="",$taskfilter3="",$taskidlist="",$countonly=0) { if($propertyid&&!is_numeric($propertyid)) $propertyid = 0; $sql = " select tbltask.* "; if(!is_livesite()) //Preventing AMC Vendor to Agent task from migrating to live until all subtasks of mtx 21735 ready for migration then remove livesite restriction. $sql.=" , tblamcvendor.amcvendorfname , tblamcvendor.amcvendorlname , tblamcvendor.amcvendorcompanyname , tblamcvendor.propertyassignmentcompleteddate , tblamcvendor.propertyassignmentid , tblamcvendor.accountstaffid "; $sql.=" , tblproperty.* , tblvendor.* , tblbidrequest.bidrequestid , tblbidrequest.bidrequestdate , tblbidrequest.bidrequestdeadlinedate , tblbidrequest.bidrequestemail , tblbidrequest.bidrequestcreatedby , tblbidrequest.bidrequestexpirationdate , companyname , tblarchive.archiverestorecompleteddate "; if(!is_livesite()) //Preventing AMC Vendor to Agent task from migrating to live until all subtasks of mtx 21735 ready for migration then remove livesite restriction. $sql.=" , get_clienttask_statuskey ( taskdeadlinedate , taskcompleteddate , taskcompleteddate , ( case when taskcompleteddate is not null then null else propertyassignmentrevokeddate end ) , null ) as statuskey --For use when Assigned to AMC Vendor , get_daysuntil(coalesce(taskcompleteddate,taskdeadlinedate)) as daysleft --For use when Assigned to AMC Vendor "; if (!$active || $active!=2) $sql .= " , get_task_days(taskcompleteddate , taskdeadlinedate , taskvendorcompleteddate ,taskstaffcompleteddate ) as days "; if ($active==2) $sql .= " , case when inactivate.staffid > 0 then inactivate.stafffname||' '||inactivate.stafflname when inactivate.staffid = 0 and inactivate.companyid > 0 then inactivate.companyfname||' '||inactivate.companylname end as inactivatedbyname "; $sql .= " from vw_tblproperty_core tblproperty -- this optimization saves the entire property table contents from being selected memory, but has the fields we need --takes a hair longer, but saves 33% of memory join tbltask using(propertyid) join tblcompany using(companyid) "; if(!is_livesite()) //Preventing AMC Vendor to Agent task from migrating to live until all subtasks of mtx 21735 ready for migration then remove livesite restriction. { $sql.=" left join ( select amcvendorfname , amcvendorlname , amcvendorcompanyname , propertyassignmentid , propertyassignmentcompleteddate , taskid , accountstaffid , propertyassignmentrevokeddate , propertyassignmenthasexpireddate from (select taskid, propertyassignmentid from tbltaskassign) tbltaskassign join ( select staffid, accountstaffid, propertyassignmentid, propertyassignmentcompleteddate, propertyassignmentrevokeddate , case when propertyassignmentexpireddate is not null then propertyassignmentexpireddate when propertyassignmentexpiredmarkeddate < now() then propertyassignmentexpiredmarkeddate else null end propertyassignmenthasexpireddate from tblpropertyassignment where 0=0 "; if(accountstaffid()) $sql.=" and accountstaffid in ( select accountstaffid from tbllogin where accountid in ( select accountid from tbllogin where accountstaffid=".accountstaffid()." ) ) "; $sql.=" ) tblpropertyassignment using (propertyassignmentid) join ( select 0 staffid, accountstaffid, accountstafffname amcvendorfname, accountstafflname amcvendorlname, accountname amcvendorcompanyname from tblaccountstaff join tblaccount using (accountid) union select staffid, 0 accountstaffid, stafffname amcvendorfname, stafflname amcvendorlname, companyfname||' '||companylname amcvendorcompanyname from tbllogin join tblstaff using (staffid) join tblcompany using (companyid) ) tbluserinfo using (accountstaffid, staffid) ) tblamcvendor using (taskid) "; } // this optimization makes it so that only task that have been assigned to the vendor show up if that is how it is called. $sql .= ($taskfilter!=onlyassigned)?"left join":"join"; $sql .= " vw_tblvendor_name tblvendor on (tbltask.taskvendoridassigned = tblvendor.vendorid) -- optimize to only pull the vendors name out using this view --takes a hair longer, but saves another 33% of memory left join tblarchive using(propertyid, companyid)"; if ($active==2) $sql .= " left join ( select companyfname, companylname, stafffname, stafflname, loginid, companyid, staffid from tbllogin left join tblcompany using (companyid) left join tblstaff using (staffid) ) inactivate on (tbltask.taskinactivatedby = inactivate.loginid)"; $sql .= " left join (select max(bidrequestid) as bidrequestid, taskid from tblbidrequest group by taskid) tmax using (taskid) left join tblbidrequest using (bidrequestid) where 0=0 "; if(accountstaffid() && !is_livesite()) //Preventing AMC Vendor to Agent task from migrating to live until all subtasks of mtx 21735 ready for migration then remove livesite restriction. $sql.=" and accountstaffid=".accountstaffid()." "; if(!displayclearedproperties() && companyid()) $sql .= " and tblproperty.propertycleareddate is null "; if(companyid() && !has_access(viewtasks)) $sql .=" and false "; if(companyid()) $sql .= " and tblproperty.companyid = ".companyid(); if($active==1 ) $sql .= " and taskcompleteddate is null and taskinactivateddate is null"; if ($active==2) $sql .= " and taskinactivateddate is not null"; else if(!$active) $sql .= " and taskcompleteddate is not null"; $taskidlist = db_number_list($taskidlist); if($taskidlist) $sql .= " and tbltask.taskid in($taskidlist) "; if (vendorid()) $vendorid = vendorid(); if($vendorid>0) { $sql .= " and taskstaffassigneddate is null and ( taskvendoridassigned = $vendorid"; if($taskfilter!=onlyassigned) // this optimization makes it so that only task that have been assigned to the vendor show up if that is how it is called. $sql .= " or exists (select 1 from tblbidrequest join tblinvitetobid using(bidrequestid) where taskid = tbltask.taskid and vendorid = ".db_number($vendorid).")"; $sql.=" )"; } if(staffid() && (!has_access(viewstafftasks))) { //if they do not have access to view the OTHER staffs tasks, then the tasks must either be assigned to a vendor, then they can only view the tasks that have been assigned to staff $sql .= " and ( taskstaffassigneddate is null or exists ( select 1 from tbltaskassign where taskid = tbltask.taskid and ( staffid = ".staffid(); if($_SESSION[grouplist]) $sql.=" or groupid in (".get_selectable_grouplist().")"; $sql .= " ) ) )"; } //if they do not have access to view the vendor tasks, then they can only view the tasks that have been assigned to staff if(companyid() && !has_access(viewvendortasks) ) $sql .= " and taskstaffassigneddate is not null "; if(companyid() && !has_access(viewvendortasks) ) $sql .= " and taskinactivateddate is null "; if($propertyid) $sql .= " and propertyid = $propertyid "; if(is_array($taskfilter['agentassignedfilter']) && !is_livesite())//Preventing AMC Vendor to Agent task from migrating to live until all subtasks of mtx 21735 ready for migration then remove livesite restriction. { if($taskfilter['agentassignedfilter']['filters']['s']) { $assignedagents=$taskfilter['agentassignedfilter']['filters']['s']; $sql.=" and ( taskname ilike '%$assignedagents%' -- Remove comments on the rest of these when the sorting on the regular client tasks are update --or propertyaddress ilike '%$assignedagents%' --or propertycity ilike '%$assignedagents%' --or propertystate ilike '%$assignedagents%' --or 'Direct Agent Assignment ' || amcvendorfname || ' ' || amcvendorlname ilike '%$assignedagents%' ) "; } if ($taskfilter['agentassignedfilter']['filters']['restrictfilter']=='my') //Used for tasks assigned to AMC Vendor $sql.=" and accountstaffid=".accountstaffid()." and propertyassignmenthasexpireddate is null and propertyassignmentrevokeddate is null and ( taskcompleteddate is null and taskdeadlinedate::date<=now()::date -- all past due tasks ) "; elseif ($taskfilter['agentassignedfilter']['filters']['restrictfilter']=='assignedtome') //Used for tasks assigned to AMC Vendor $sql.=" and accountstaffid=".accountstaffid()." "; if($taskfilter['agentassignedfilter']['filters']['statusfilter']) { $sql=" select * from ($sql) t where 0=0 and ( t.statuskey = '".$taskfilter['agentassignedfilter']['filters']['statusfilter']."' ) "; } unset($taskfilter); } if ($taskfilter!="") { if ($taskfilter=="warning"||$taskfilter2=="warning"||$taskfilter3=="warning") $sql .= " and (taskdeadlinedate >= now()::date and taskdeadlinedate <= (now()::date + '2 days'::interval))"; if ($taskfilter=="ontime"||$taskfilter2=="ontime"||$taskfilter3=="ontime") $sql .= " and coalesce(taskdeadlinedate, now()+ '1 week'::interval) > (now()::date + '2 days'::interval)"; if ($taskfilter=="late"||$taskfilter2=="late"||$taskfilter3=="late") $sql .= " and taskdeadlinedate < now()::date"; } if (($taskfilter=="my" || $taskfilter2=="my") && staffid()) { $sql .= " and ( taskstaffassigneddate is not null and exists (select 1 from tbltaskassign where taskid = tbltask.taskid and (staffid = ".staffid(); if($_SESSION[grouplist]) $sql.=" or groupid in (".get_selectable_grouplist().") "; $sql.=") ) )"; } if (($taskfilter=="created" || $taskfilter2=="created")) { $sql .= " and ( taskcreatedby = ".loginid().")"; } $sql .= " order by taskdeadlinedate desc"; if ($countonly) return db_firstval ("select count (1) from (" . $sql . ") as q" , 1, 0); $arr = db_query($sql,1,0,""); if(!is_livesite())//Preventing AMC Vendor to Agent task from migrating to live until all subtasks of mtx 21735 ready for migration then remove livesite restriction. { //For use when Assigned to AMC Vendor $statuses[onhold]="On Hold"; $statuses[pastdue]="Past Due"; $statuses[warning]="Warning"; $statuses[pending]="Pending Approval"; $statuses[complete]="Completed"; $statuses[open]="Open"; $statuses[pulled_incomplete]="Pulled Incomplete"; $statuses[scheduled]="Scheduled"; $status_priorities[pending]="1"; $status_priorities[pastdue]="2"; $status_priorities[warning]="3"; $status_priorities[scheduled]="4"; $status_priorities[open]="5"; $status_priorities[onhold]="6"; $status_priorities[complete]="7"; $status_priorities[pulled_incomplete]="8"; } foreach($arr as $k=>$row) { if(!is_livesite())//Preventing AMC Vendor to Agent task from migrating to live until all subtasks of mtx 21735 ready for migration then remove livesite restriction. { $arr[$k][status_priority] = $status_priorities[$row[statuskey]]; //For use when Assigned to AMC Vendor $arr[$k][status] = $statuses[$row[statuskey]]; //For use when Assigned to AMC Vendor } $arr2 = unserialize($row[taskserial]); if(is_array($arr2)) foreach($arr2 as $i=>$v) $arr[$k][$i]=$v; } if(vendorid()) // forvendors only foreach($arr as $k =>$row) { //never return any items that have been restored if($row[archiverestorecompleteddate]) unset($arr[$k]); } return $arr; } function task_has_document($taskid) { timetrack_start('task_has_document',$taskid); global $tasks_with_documents; if(!isset($tasks_with_documents[$taskid])) { $checktask_with_documents = db_query("select taskid from tbldocument where taskid=".db_number($taskid)." limit 1") ; if($checktask_with_documents) $tasks_with_documents[$taskid]=1; else $tasks_with_documents[$taskid]=0; } timetrack_stop('task_has_document',$taskid); return $tasks_with_documents[$taskid]; } function get_task_status_no_assignment_text($arr, $addinactivemouseover=0) { if($addinactivemouseover) $txt = get_task_status($arr, $addinactivemouseover); else $txt = get_task_status($arr); //$txt = str_ireplace("Assigned to staff member(s):","",$txt); //$txt = str_ireplace("Assigned to staff group(s):","",$txt); return $txt; } function get_task_status($arr, $addinactivemouseover=0) { //The array for the task id selected should be passed into this (can use get_task function, then pass that value into this) $biddeadline = strtotime($arr[bidrequestdeadlinedate]); $taskdeadline = strtotime($arr[taskdeadlinedate]); $today = date("Y-m-d"); $today = strtotime($today); if(!$arr[taskcompleteddate] && $arr[bidrequestid] &&vendorid()) $bid = db_query("select * from tblinvitetobid where bidrequestid = ".db_number($arr[bidrequestid])." and vendorid = ".vendorid()); if(!$arr[bidrequestdeadlinedate] && $arr[bidrequestid]) $invite = db_first("select * from tblinvitetobid where bidrequestid = ".db_number($arr[bidrequestid])); if ($arr[taskcompleteddate]!=null) $status = "Completed"; else if(vendorid() && $bid[0][invitetobidremove] && $bid[0][invitetobidproposed]) $status = "Vendor Denied Assignment"; else if($arr[bidrequestexpirationdate]) $status = "Bid Request Expired"; else if ($arr[taskvendoridassigned] && !$arr[bidrequestdeadlinedate] && $arr[bidrequestid] && $invite[invitetobidremove] && $invite[invitetobidproposed]) $status = "Vendor Denied Assignment"; else if ($arr[taskvendoridassigned] && !$arr[bidrequestdeadlinedate] && $arr[bidrequestid] && $invite[invitetobidremove] ) $status = "Vendor Declined Bid"; else if ($arr[taskvendoridassigned]>0 && $arr[taskvendorcompleteddate]!=null && $arr[taskratingcompleteddate]!=null && $arr[taskcompleteddate]==null) $status = "Awaiting final closure of task by staff"; else if ($arr[taskvendoridassigned]>0 && $arr[taskvendorcompleteddate]!=null && $arr[taskratingcompleteddate]==null) $status = "Awaiting completion of vendor rating"; else if ($arr[taskvendoridassigned]>0 && $arr[taskvendoraccepteddate]==null) $status = "Assigned to vendor: $arr[vendorcompanyname] - Awaiting Acceptance"; else if ($arr[taskvendoridassigned]>0 && $arr[taskvendorcompleteddate]==null) $status = "Assigned to vendor: $arr[vendorcompanyname]"; else if ($arr[propertyassignmentid]>0 && $arr[propertyassignmentcompleteddate]==null && !is_livesite()) $status = "Assigned to AMC vendor: $arr[amcvendorcompanyname]"; else if ($arr[taskstaffassigneddate]!=null && $arr[taskstaffcompleteddate]==null) { $staff = ""; $group = ""; if ($arr[taskstaffassigneddate]!=null) { $assignedstaffsql = " select group_concat(distinct stafffname||' '||stafflname) as staffname from tbltaskassign join tblstaff using (staffid) where taskid = ".db_number($arr[taskid])." and staffid > 0"; $assignedstaffqry = db_query($assignedstaffsql); $staff = $assignedstaffqry[0][staffname]; //Added code here to list on mouseover which staff logins are inactive. if($addinactivemouseover) { $inactivestaffsql = " select group_concat(distinct stafffname||' '||stafflname) as staffname from tbltaskassign join tblstaff using (staffid) join tbllogin using (staffid) where taskid = ".db_number($arr[taskid])." and staffid > 0 and logininactive is not null"; $inactivestaffqry = db_query($inactivestaffsql); if($inactivestaffqry) { $inactivestaff = $inactivestaffqry[0][staffname]; if($inactivestaff && $inactivestaff!="") $staff="$staff"; } } $assignedgroupsql = " select group_concat(distinct groupname) as groupname from tbltaskassign join tblgroup using (groupid) where taskid = ".db_number($arr[taskid])." and groupid > 0"; $assignedgroupqry = db_query($assignedgroupsql); $group = $assignedgroupqry[0][groupname]; } if ($staff!="") $status .= "Assigned to staff member(s): $staff "; if ($group!="") $status .= "Assigned to staff group(s): $group "; } else if ($arr[taskstaffassigneddate]!=null && $arr[taskstaffcompleteddate]!=null && $arr[taskcompleteddate]==null) $status = "Awaiting final closure of task by staff"; else if ($arr[bidrequestdeadlinedate]!=null && $biddeadline > $today) { $bidsql = " select count(bidrequestid) as total from tblinvitetobid join tblbidrequest using (bidrequestid) where bidrequestexpirationdate is null and invitetobidtext <> '' and taskid = ".db_number($arr[taskid]); $bidqry = db_query($bidsql); foreach ($bidqry as $b => $bidrow) $bidcount = $bidrow[total]; $status = "Bidding open until ".dt_format($arr[bidrequestdeadlinedate]).". Total bids: $bidcount"; } else if ($arr[bidrequestdeadlinedate]!=null && $biddeadline <= $today && $arr[taskvendoridassigned]==0) { $bidsql = " select count(bidrequestid) as total from tblinvitetobid join tblbidrequest using (bidrequestid) where bidrequestexpirationdate is null and invitetobidtext <> '' and taskid = ".db_number($arr[taskid]); $bidqry = db_query($bidsql); foreach ($bidqry as $b => $bidrow) $bidcount = $bidrow[total]; $status = "Bidding closed as of ".dt_format($arr[bidrequestdeadlinedate]).". Pending vendor selection. Total bids: $bidcount"; } else if ($arr[taskvendoridassigned] == 0 && $arr[taskstaffassigneddate] == null && $arr[bidrequestdate] == null) { $status = "Awaiting assignment or bid request"; if($_GET[taskid]&&$_GET[taskpage]==details&&(is_company()||is_staff())) $status.= " **Assign now**"; } else { $status = "Awaiting assignment or bid request."; if($_GET[taskid]&&$_GET[taskpage]==details&&(is_company()||is_staff())) $status.= " **Assign now**"; } return $status; } function get_task_status_color($task) { $today = date("Y-m-d"); $comparetoday = strtotime($today); $comparedeadline = strtotime($task[taskdeadlinedate]); $warningdate = strtotime('-2 days', $comparedeadline); if ($task[taskinactivateddate]) { $text = "
"; } else if ($task[taskdeadlinedate]==null) { $text = "
"; } else if ($comparetoday >= $warningdate && $comparedeadline >= $comparetoday)//same day as the deadline is yellow not read per april / may spreadsheet { $text = "
"; } else if ($comparedeadline < $comparetoday) { $text = "
"; } else if ($comparedeadline >= $comparetoday) { $text = "
"; } return $text; } function get_task_status_count($arr,$align=left,$addedurl="",$variable_append="", $fromdashboard=0) { $sn = $_SERVER[SCRIPT_NAME]; if($fromdashboard) $sn = "/task.php"; $greencount = 0; $yellowcount = 0; $junkcount = 0; $redcount = 0; $inactivecount = 0; $greentext = "
"; $yellowtext = "
"; $redtext = "
"; $inactivetext = "
"; foreach ($arr as $a => $task) { $today = date("Y-m-d"); $comparetoday = strtotime($today); $comparedeadline = strtotime($task[taskdeadlinedate]); $warningdate = strtotime('-2 days', $comparedeadline); if ($task[taskinactivateddate]!=null) $inactivecount++; else if ($comparedeadline==null) $greencount++; else if ($comparetoday >= $warningdate && $comparedeadline >= $comparetoday) //same day as the deadline is yellow not readread per april / may spreadsheet $yellowcount++; else if ($comparedeadline < $comparetoday) $redcount++; else if ($comparedeadline > $comparetoday) $greencount++; else $junkcount++; } if ($inactivecount>0) { $text = "
$inactivetext $inactivecount
"; } else { $text = "
$greentext $greencount $yellowtext $yellowcount $redtext $redcount
"; } return $text; } function get_task_status_count_numbersandlinks($arr,$addedurl="") { //d($arr); $sn = "/task.php"; $greencount = 0; $yellowcount = 0; $junkcount = 0; $redcount = 0; $inactivecount = 0; $greentext = "href=$sn?taskfilter{$variable_append}=ontime$addedurl title='Open tasks with deadline dates more than two days away'"; $yellowtext = "href=$sn?taskfilter{$variable_append}=warning$addedurl title='Open tasks with deadline dates within two days of today'"; $redtext = "href=$sn?taskfilter{$variable_append}=late$addedurl title='Open tasks with deadline dates less than or equal to today'"; $inactivetext = "title='Inactive tasks'"; if($arr) foreach ($arr as $a => $task) { $today = date("Y-m-d"); $comparetoday = strtotime($today); $comparedeadline = strtotime($task[taskdeadlinedate]); $warningdate = strtotime('-2 days', $comparedeadline); if ($task[taskinactivateddate]!=null) $inactivecount++; else if ($comparedeadline==null) $greencount++; else if ($comparetoday >= $warningdate && $comparedeadline >= $comparetoday) //same day as the deadline is yellow not readread per april / may spreadsheet $yellowcount++; else if ($comparedeadline < $comparetoday) $redcount++; else if ($comparedeadline > $comparetoday) $greencount++; else $junkcount++; } if ($inactivecount>0) { $arr2['inactive']['url']=$inactivetext; $arr2['inactive']['count']=$inactivecount; } else { $arr2['green']['url']=$greentext; $arr2['green']['count']=$greencount; $arr2['orange']['url']=$yellowtext; $arr2['orange']['count']=$yellowcount; $arr2['red']['url']=$redtext; $arr2['red']['count']=$redcount; } //d($arr2); return $arr2; } function get_closedtask_status_color($task) { $comparedeadline = strtotime($task[taskdeadlinedate]); if($task[taskvendorcompleteddate]) $comparecompleted = strtotime($task[taskvendorcompleteddate]); elseif($task[taskstaffcompleteddate]) $comparecompleted = strtotime($task[taskstaffcompleteddate]); elseif($task[taskcompleteddate]) $comparecompleted = strtotime($task[taskcompleteddate]); if ($comparecompleted < $comparedeadline) { $text = "
"; } else if ($comparecompleted > $comparedeadline) { $text = "
"; } else { $text = "
"; } return $text; } function get_vendor_status_count_company($vendorid) { //get all of the tasks that a vendor has pending for a company and display them global $get_vendor_status_count_optimized_tasks; if(!$get_vendor_status_count_optimized_tasks) { $sql = " select taskdeadlinedate , taskvendorcompleteddate , taskstaffcompleteddate , taskcompleteddate , taskvendoridassigned as vendorid from tbltask join tblproperty using(propertyid) where tblproperty.companyid = ".companyid()." and taskvendoridassigned > 0 and taskcompleteddate is not null and taskstaffassigneddate is null "; foreach(db_query($sql) as $row) $get_vendor_status_count_optimized_tasks[$row[vendorid]][]= $row; } $arr = $get_vendor_status_count_optimized_tasks[$vendorid]; return get_vendor_status_count($arr);} function get_vendor_status_count($arr,$align=left,$paginate=0) { if(!$align) $align=left; $greencount = 0; $yellowcount = 0; $redcount = 0; $greentext = "
"; $yellowtext = "
"; $redtext = "
"; if(!$arr) $arr=array(); foreach ($arr as $a => $task) { $comparedeadline = strtotime($task[taskdeadlinedate]); if($task[taskvendorcompleteddate]) $comparecompleted = strtotime($task[taskvendorcompleteddate]); elseif($task[taskstaffcompleteddate]) $comparecompleted = strtotime($task[taskstaffcompleteddate]); elseif($task[taskcompleteddate]) $comparecompleted = strtotime($task[taskcompleteddate]); if ($comparecompleted < $comparedeadline) { $greencount++; } else if ($comparecompleted > $comparedeadline) { $redcount++; } else { $yellowcount++; } } $text = "
$greentext $greencount $yellowtext $yellowcount $redtext $redcount
"; return $text; } function get_bidrequests($taskid,$propid) { if (!$taskid) $taskid = ""; if (!$propid) $propid = ""; $sql = "select * from tblinvitetobid join tblbidrequest using (bidrequestid) join tbltask using (taskid) join tblproperty using (propertyid) join tblcompany using(companyid) left join tblarchive using(propertyid,companyid) where bidrequestexpirationdate is null and taskvendoridassigned = 0 and taskstaffassigneddate is null"; if (vendorid()) $sql .= " and vendorid = ".vendorid()." and invitetobidremove is null"; if ($propid!="") $sql .= " and propertyid = ".db_number($propid); if ($propid=="" && $taskid!="") $sql .= " and taskid = ".db_number($taskid); $sql .= " order by bidrequestdeadlinedate desc"; $arr = db_query($sql); if(vendorid()) foreach($arr as $k =>$row) { //never return any items that have been restored if($row[archiverestorecompleteddate]) unset($arr[$k]); } return $arr; } function get_bid($invitetobidid) { $sql = "select * from tblinvitetobid join tblvendor using (vendorid) join tbllogin using (vendorid) where invitetobidid = $invitetobidid"; if (vendorid()) $sql .= " and vendorid = ".vendorid(); $arr = db_query($sql); return $arr; } function get_bids($taskid) { $sql = "select *, coalesce(inviters.stafffname||' '||inviters.stafflname, inviterc.companyfname||' '||inviterc.companylname) as invitername from tblbidrequest join tbltask using (taskid) join tblinvitetobid using (bidrequestid) join tblvendor using (vendorid) left join tbllogin inviter on(invitetobidcreatedby = inviter.loginid) left join tblstaff inviters on(inviters.staffid = inviter.staffid) left join tblcompany inviterc on(inviterc.companyid = inviter.companyid) where bidrequestexpirationdate is null and taskid = $taskid"; if (vendorid()) $sql .= " and tblvendor.vendorid = ".vendorid(); $sql .= " order by invitetobidtextcreated desc"; $arr = db_query($sql); return $arr; } function get_allbids($propertyid,$taskid,$invitetobidid=0, $viewed=FALSE) { $sql = "select case when taskvendorassigneddate is not null and taskvendoridassigned <> tblinvitetobid.vendorid then 'Denied ' --someone else is assigned, that means they were denied when taskcompleteddate is not null then 'Completed' when invitetobidproposed = 1 and invitetobidremove is not null then 'Vendor Rejected' --call it rejected when the amount was propoesed to them by the company but they remvoed it. when invitetobidremove is not null then 'Vendor Removed ' --call it rejected when the amount was propoesed to them by the company but they remvoed it. when taskvendorassigneddate is not null and taskvendoraccepteddate is not null and invitetobidproposed = 1 then 'Accepted by Vendor' when taskvendorassigneddate is not null and taskvendoraccepteddate is not null then 'Approved by Broker' when bidrequestexpirationdate is not null then 'Bid Request Expired' when taskvendorassigneddate is not null and invitetobidproposed = 1 then 'Assigned Awaiting Acceptance' when taskvendorassigneddate is not null then 'Approved Awaiting Acknowledgement' when invitetobidtextcreated is not null then 'Bid Submitted by Vendor' when bidrequestexpirationdate is not null then 'Bid Request Expired' when bidrequestdeadlinedate < now() then 'No Vendor Response - (Bid Deadline Passed)' else 'Unassigned - Awaiting Bid' end as bidstatus , coalesce(coalesce(invitetobidtextcreated,taskvendorassigneddate),bidrequestdate) as displaydate , coalesce(inviters.stafffname||' '||inviters.stafflname , inviterc.companyfname||' '||inviterc.companylname) as invitername , tbltask.* , tblproperty.* , tblinvitetobid.* , inviter.loginid as inviterid , inviters.* , inviterc.* , tblbidrequest.* , tblvendor.* , archiverestorecompleteddate from tblbidrequest join tbltask using (taskid) join tblproperty using (propertyid) left join tblarchive using(propertyid, companyid) join tblinvitetobid using (bidrequestid) join tblvendor using (vendorid) left join tbllogin inviter on(invitetobidcreatedby = inviter.loginid) left join tblstaff inviters on(inviters.staffid = inviter.staffid) left join tblcompany inviterc on(inviterc.companyid = inviter.companyid) where 0=0 "; if(!displayclearedproperties() && (companyid() || vendorid())) $sql.=" and propertycleareddate is null "; if (vendorid()) $sql .= " and tblvendor.vendorid = ".vendorid().""; if (companyid()) $sql .= " and tblproperty.companyid = ".companyid().""; if ($taskid) $sql .= " and tbltask.taskid = ".db_number($taskid); if ($propertyid) $sql .= " and tbltask.propertyid = ".db_number($propertyid); if ($invitetobidid) $sql .= " and invitetobidid = ".db_number($invitetobidid); if($viewed){ if($viewed=='new') $sql .= " and bidrequestvieweddate is null"; elseif($viewed=='read') $sql .= " and bidrequestvieweddate is not null"; } $sql .= " order by invitetobidtextcreated desc"; $arr = db_query($sql); if(vendorid()) // forvendors only foreach($arr as $k =>$row) { //never return any items that have been restored //d($row); if($row[archiverestorecompleteddate]) unset($arr[$k]); } return $arr; } function get_vendor_rating_avg($vendorid,$includestatus=0,$align=left) { $vendortotal = 0; $total = 0; $sql = " select * from tblnote where vendorid = ".db_number($vendorid)." and notevendorrating > 0"; $qry = db_query($sql); $total = count($qry); $totalpossible = $total * 5; foreach ($qry as $q => $row) $vendortotal = $vendortotal + $row[notevendorrating]; if ($vendortotal>0) $percent = $vendortotal / $totalpossible; return $percent; } function display_vendor_rating_avg($vendorid,$align=left,$xhtml=0) { $vendortotal = 0; $total = 0; $sql = " select * from tblnote where vendorid = ".db_number($vendorid)." and notevendorrating > 0"; $qry = db_query($sql); $total = count($qry); $totalpossible = $total * 5; foreach ($qry as $q => $row) $vendortotal = $vendortotal + $row[notevendorrating]; $star = ""; if($_SERVER[SERVER_PORT]==443) $star = ""; if ($vendortotal>0) $percent = $vendortotal / $totalpossible; if ($percent<="0.2" && $percent>"0") $rating = $star; else if ($percent<="0.4" && $percent>"0.2") $rating = $star.$star; else if ($percent<="0.6" && $percent>"0.4") $rating = $star.$star.$star; else if ($percent<="0.8" && $percent>"0.6") $rating = $star.$star.$star.$star; else if ($percent<="1" && $percent>"0.8") $rating = $star.$star.$star.$star.$star; if($xhtml) //exclude html $text = "$rating ($total)"; else { $text = "
$rating ($total)
"; } return $text; } //This is pulled into the right nav and uses the status colors as a background function get_navtask_status($arr,$align=left,$addedtext="") { $today = date("Y-m-d"); $comparetoday = strtotime($today); $comparedeadline = strtotime($arr[taskdeadlinedate]); $biddeadline = strtotime($arr[bidrequestdeadlinedate]); $warningdate = strtotime('-2 days', $comparedeadline); //Get number of days until deadline $dayssql = "select get_task_days(".db_number($arr[taskid]).") as days"; $daysqry = db_query($dayssql); if ((is_company() || is_staff2()) && ($arr[bidrequestdeadlinedate]!=null && $biddeadline <= $comparetoday && $arr[taskvendoridassigned]==0)) $appendurl = "&step=biddingclosed"; else $appendurl = ""; $addstyle=""; if($arr[addstyle]) $addstyle.=" style = '$arr[addstyle]'"; if($arr[addclass]) $addstyle.=" class = '$arr[addclass]'"; if ($arr[taskdeadlinedate]==null) { $tasklink = "
  • $arr[taskname] {$daysqry[0][days]}$addedtext
    • "; } else if ($comparetoday >= $warningdate && $comparedeadline > $comparetoday) { $tasklink = "
  • $arr[taskname] {$daysqry[0][days]}$addedtext
    • "; } else if ($comparedeadline <= $comparetoday) { $tasklink = "
  • $arr[taskname] {$daysqry[0][days]}$addedtext
    • "; } else if ($comparedeadline > $comparetoday) { $tasklink = "
  • $arr[taskname] {$daysqry[0][days]}$addedtext
    • "; } return $tasklink; } function get_task_emailaddresses($taskid) { $taskid = db_number($taskid); if (clientcompanyid()) { $getassignedsql = " select distinct staffemail as email from tblclienttask join tblclientcompanypropertyassignment using (clientcompanypropertyassignmentid) join tblstaff using (staffid) where clienttaskid = ".db_number($taskid); } else { $task = get_task($taskid); $companyid = db_number($task[companyid]); $getassignedsql = " select group_concat(distinct email) as email from ( select case when tbl.email <> '' then tbl.email when tbl2.email <> '' then tbl2.email end as email from tbltaskassign left join ( select staffemail as email, staffid from tblstaff ) tbl on (tbltaskassign.staffid > 0 and tbl.staffid = tbltaskassign.staffid) left join ( select staffemail as email, groupid from tblstaffgroup join tblstaff using (staffid) join tbllogin using(staffid) where companyid = $companyid ) tbl2 on (tbltaskassign.groupid > 0 and tbl2.groupid = tbltaskassign.groupid) where taskid = $taskid ) tbl"; } $getassignedqry = db_query($getassignedsql); //d($getassignedsql,$getassignedqry); $to = $getassignedqry[0][email]; return $to; } function get_task_foruser($taskid,$companyid=0,$clientcompanyid=0,$vendorid=0) { $sql = " select tbltask.* , tblproperty.* , tblvendor.* , tblamcvendor.accountstaffid --Do not replace these with tblamcvendor.* because taskid if not left joined will leave more than one taskid where one is blank. , tblamcvendor.propertyassignmentid , tblamcvendor.propertyassignmentcreatedby , tblamcvendor.propertyassignmentcreateddate , tblamcvendor.propertyassignmentacceptedby , tblamcvendor.propertyassignmentaccepteddate , tblamcvendor.propertyassignmentdeniedby , tblamcvendor.propertyassignmentdenieddate , tblamcvendor.propertyassignmentrevokedby , tblamcvendor.propertyassignmentrevokeddate , tblamcvendor.propertyassignmentcompletedby , tblamcvendor.propertyassignmentcompleteddate , tblamcvendor.propertyassignmentexpiredby , tblamcvendor.propertyassignmentexpireddate , tblamcvendor.propertyassignmentexpiredmarkedby , tblamcvendor.propertyassignmentexpiredmarkeddate , tblamcvendor.propertyassignmenttsecondarystaffid , tblamcvendor.propertyassignmenthasexpireddate , tblamcvendor.amcvendorfname , tblamcvendor.amcvendorlname , tblamcvendor.amcvendorcompanyname , tbl.bidrequestid , tbl.bidrequestdate , tbl.bidrequestdeadlinedate , tbl.bidrequestemail , tbl.bidrequestcreatedby , tbl.bidrequestexpirationdate , tbl.receivedbidcount "; if($vendorid) { $sql.=" , tbl.vendorid as invitetobid_vendorid , tbl.invitetobidremove "; } else if($companyid) //find out if this is the most recent recurring taskid { $sql .= " , case when taskrecurdays = 0 or taskrecurring = 0 then 0 else ( select max(taskid) from tbltask a where propertyid = tbltask.propertyid and a.mastertaskid = tbltask.mastertaskid and tbltask.taskname = a.taskname and tbltask.taskrecurdays = a.taskrecurdays and a.taskrecurdays > 0 ) end as most_recent_recurringtaskid"; } $sql .= " , get_task_days(taskcompleteddate, taskdeadlinedate, taskvendorcompleteddate, taskstaffcompleteddate) as days , case when inactivate.staffid > 0 then inactivate.stafffname||' '||inactivate.stafflname when inactivate.staffid = 0 and inactivate.companyid > 0 then inactivate.companyfname||' '||inactivate.companylname else '' end as inactivatedbyname , case when completedby.staffid > 0 then completedby.stafffname||' '||completedby.stafflname when completedby.staffid = 0 and completedby.companyid > 0 then completedby.companyfname||' '||completedby.companylname else '' end as completedbyname from tbltask join tblproperty using(propertyid) left join ( select *, vendorid taskvendoridassigned from tblvendor ) tblvendor using (taskvendoridassigned) left join ( select * from (select taskid, propertyassignmentid from tbltaskassign) tbltaskassign join ( select * , case when propertyassignmentexpireddate is not null then propertyassignmentexpireddate when propertyassignmentexpiredmarkeddate < now() then propertyassignmentexpiredmarkeddate else null end propertyassignmenthasexpireddate from tblpropertyassignment ) tblpropertyassignment using (propertyassignmentid) join ( select 0 staffid, accountstaffid, accountstafffname amcvendorfname, accountstafflname amcvendorlname, accountname amcvendorcompanyname from tblaccountstaff join tblaccount using (accountid) union select staffid, 0 accountstaffid, stafffname amcvendorfname, stafflname amcvendorlname, companyfname||' '||companylname amcvendorcompanyname from tbllogin join tblstaff using (staffid) join tblcompany using (companyid) ) tbluserinfo using (accountstaffid, staffid) ) tblamcvendor using (taskid) left join ( select companyfname, companylname, stafffname, stafflname, loginid, companyid, staffid, loginid taskinactivatedby from tbllogin left join tblcompany using (companyid) left join tblstaff using (staffid) ) inactivate using (taskinactivatedby) left join ( select companyfname, companylname, stafffname, stafflname, loginid, companyid, staffid, loginid taskcompletedby from tbllogin left join tblcompany using (companyid) left join tblstaff using (staffid) ) completedby using (taskcompletedby) left join ( select * , (select count(1) from tblinvitetobid where bidrequestid = tblbidrequest.bidrequestid and invitetobidtextcreated is not null) as receivedbidcount from tblbidrequest "; if($vendorid) $sql.=" join tblinvitetobid using(bidrequestid) "; $sql .= " where bidrequestexpirationdate is null and taskid = ".db_number($taskid); if($vendorid) $sql.=" and tblinvitetobid.vendorid = ".$vendorid; $sql.=" order by bidrequestid desc limit 1 ) tbl using (taskid) where tbltask.taskid = ".db_number($taskid); if($companyid && !stristr($_SERVER[SCRIPT_NAME],schedule)) $sql.= " and tblproperty.companyid = ".$companyid; //d($sql,'get task sql'); $arr = db_first($sql); if($arr[taskserial]) { $arr2 = unserialize($arr[taskserial]); if(is_array($arr2)) foreach($arr2 as $k=>$v) $arr[$k]=$v; } return $arr; } function staffassigned($taskid) { $taskid = db_number($taskid); $sql = " select * from tbltaskassign where taskid = $taskid and ( staffid = ".staffid()." or groupid in (".get_selectable_grouplist().") )"; $taskarr = db_query($sql); if (count($taskarr)>0) return 1; else return 0; } function staffactiveassignedtasks($staffid) { $staffid = db_number($staffid); $sql = " select taskid,taskcreateddate,taskdescription ,taskcreatedby,taskname,taskdeadlinedate,taskupdateddate,taskupdatedby ,taskrecurring from ( select * from tbltaskassign join tbltask using (taskid) where 0=0 and staffid = ".db_number($staffid)." and taskcompleteddate is null and taskinactivateddate is null and taskvendorcompleteddate is null and taskstaffcompleteddate is null ) tbl"; $taskarr = db_query($sql); if (count($taskarr)>0) return $taskarr; else return 0; } function replace_groupids_with_staffids($propertyid, &$assigntogroups, &$assigntostaff) { //this accespts parameters by reference, and updates the passed in variables $assigntogroupsarr=get_groupids_fromgroupnames($assigntogroups); $propertyid = db_number($propertyid); if($assigntogroupsarr) { $sql = " select staffid, groupid from tblpropertygroupstaff join tbllogin using (staffid) where 0=0 and staffid > 0 and propertyid = $propertyid and groupid in (".db_number_list($assigntogroupsarr).") and logininactive is null "; $assignstaff = db_query($sql,1,1,'groupid'); foreach($assignstaff as $group => $staff) $assignstaff[$group] = $staff[staffid]; } if(in_array(7, $assigntogroupsarr)) // special case : listing agent : group id 7. We lookup the staff member from property extension listingagent { $listingagent = db_first("select propertylistingagent as staffid from tblproperty where propertyid = $propertyid"); if($listingagent[staffid]) $assignstaff[7] = $listingagent[staffid]; } if($assignstaff) foreach($assignstaff as $groupid =>$staff) unset($assigntogroupsarr[array_search($groupid,$assigntogroupsarr)]) ; //we found staff assigned to these grooups remove the groups. if(!$assigntogroupsarr) $assigntogroups = ''; else $assigntogroups = implode(",",$assigntogroupsarr); if($assignstaff) { if(is_array($assigntostaff)) $assigntostaff = implode(",", $assigntostaff); if($assigntostaff) $assignstaff = array_merge($assignstaff,explode(",",$assigntostaff)); $assigntostaff = implode(",",array_unique($assignstaff)); } } function create_task($propertyid,$taskname,$tasksysname="",$taskdescr="",$duedate="",$assigntogroups="",$sendemailtogroups="",$emailshortname="",$assigntostaff="",$sendemailtostaff="",$exitonerror=true) { // $tasksysname = this is basically an uneditable short name used by us for certain triggers since the staff can change the taskname // the tasksysname will be used to make sure that an open task with the same name is not inserted twice for the same property $mastertaskid = 0; if ($tasksysname) { if (is_numeric($tasksysname)) { $mastertaskid = $tasksysname; $tasksysname=""; } else { $sql = "select 1 from tbltask where tasksystemname = '".db_col($tasksysname)."' and propertyid = ".db_number($propertyid)." and taskcompleteddate is null"; if (count(db_query($sql))) return false; } } if ($sendemailtostaff!="") { $sendemailtostaff = db_number_list($sendemailtostaff); $sql=" select 1 from tblstaff join tbllogin using(staffid) where staffid in ($sendemailtostaff) and coalesce(btrim(staffemail), '') <> '' and logininactive is null "; if(!db_query($sql)) { $assigntostaff=""; $sendemailtostaff=""; } // for each of the staff ids sent in, check to see if the company is active - if not - return .. //JTRULL 12/21/2106; } //Commented out assignment to Managers group if not assigned at client request. It is possible this will be added back in the future. Mac //if(!$assigntogroups && !$assigntostaff) // $assigntogroups = "Managers"; if($assigntogroups && $assigntogroups!=-1) { $originalassigntogroups=$assigntogroups; replace_groupids_with_staffids($propertyid,$assigntogroups,$assigntostaff); //this accespts parameters by reference, and updates the passed in variables } if($sendemailtogroups && $sendemailtogroups!=-1) { $originalsendemailtogroups=$sendemailtogroups; replace_groupids_with_staffids($propertyid,$sendemailtogroups,$sendemailtostaff); //this accespts parameters by reference, and updates the passed in variables } if(!$sendemailtogroups && !$sendemailtostaff) $sendemailtogroups = $assigntogroups; $exp=array(); if(trim($assigntogroups)) $exp = explode(",",$assigntogroups); $staffexp=array(); if(trim($assigntostaff)) $staffexp = explode(",",$assigntostaff); if (!$propertyid || !$taskname) $errormessage = "ERROR: Propertyid and taskname are required."; if ((($sendemailtostaff!="" || $sendemailtogroups!="") && $emailshortname=="") || ($sendemailtostaff=="" && $sendemailtogroups=="" && $emailshortname!="")) { developer_error("create_task ERROR: function usage error ", "if emailshortname is used, sendemailtostaff or sendemailtogroups is required
    if sendemailtostaff or sendemailtogroups is set, emailshortname is required
    emailshortname = $emailshortname
    sendemailtostaff = $sendemailtostaff
    sendemailtogroups = $sendemailtogroups"); $errormessage = "ERROR: Unable to find email associated with Master Auto Task: '$taskname'."; } if ($upstep!="" && $uptaskid==0) $errormessage = "ERROR: When an update step is passed in, a task id must also be passed in."; if ($errormessage) { if ($exitonerror) { echo $errormessage; exit_wrapper(123); } else { set_message($errormessage, 'error'); return false; } } $nexttaskidsql = "select nextval('tbltask_taskid_seq') as id"; $nexttaskidqry = db_query($nexttaskidsql); $taskid = $nexttaskidqry[0][id]; $desktop=0; if($_SERVER[SCRIPT_NAME]=="/desktop.php") $desktop=1; $instasksql = " insert into tbltask ( taskid , taskname , propertyid , taskdescription , taskdeadlinedate , taskstaffassigneddate , tasksystemname , mastertaskid , taskdesktop ) values ( $taskid , '".db_col($taskname)."' , ".db_number($propertyid)." , ".db_tick($taskdescr)." , ".db_tick($duedate)." , now() , ".db_tick($tasksysname)." , ".db_number($mastertaskid)." , $desktop )"; db_exec($instasksql,1); if($assigntogroups==-1) // skip assigning to groups or sending emails return $taskid; // implement the mastertask task requirement. if($mastertaskid) { $reqqry = " select * from tbltaskrequirement where mastertaskid = ".db_number($mastertaskid)." and propertyid = 0"; $reqlist = db_query($reqqry); if($reqlist) { foreach($reqlist as $k=>$req) create_task_requirement($taskid,$propertyid,$req[taskrequirementtitle],$req[taskrequirementtype],$req[taskrequirementattribute1],$mastertaskid); } } $assigntogroupsarr=get_groupids_fromgroupnames($assigntogroups); foreach ($assigntogroupsarr as $g => $groupid) { if(!$groupid) ddie("Error, did not get groupids from '$assigntogroups'"); $insassignsql = " insert into tbltaskassign ( taskid , groupid ) values ( ".db_number($taskid)." , ".db_number($groupid)." )"; db_exec($insassignsql); } if($staffexp) { foreach ($staffexp as $s => $staffid) { $insassignsql = " insert into tbltaskassign ( taskid , staffid ) values ( ".db_number($taskid)." , ".db_number($staffid)." )"; db_exec($insassignsql); } } if ($sendemailtogroups!="") send_trigger_email($taskid,'task',$emailshortname,$sendemailtogroups); if ($sendemailtostaff!="") send_trigger_email($taskid,'task',$emailshortname,"","","","","",$sendemailtostaff); return $taskid; } $task_system_name_extra_fieldmap = array( thirdpartyauthorizationdoc=>array(title=>"3rd Party Authorization doc") ,cmadoc=>array(title=>"CMA doc" ) ,listingagreementdoc=>array(title=>"Listing Agreement doc" ) ,mlssheet=>array(title=>"MLS Sheet" ) ,mortgagestatement=>array(title=>"Mortgage Statement" ) ,hardshipletter=>array(title=>"Hardship letter" ) ,financialstatement=>array(title=>"Financial Statement" ) ,lastpaystubs_2=>array(title=>"Last 2 Paystubs - 1", title2=>"Last 2 Paystubs - 2" ) ,lastbankstatements_2=>array(title=>"Last 2 Months Bank Statements - 1" , title2=>"Last 2 Months Bank Statements - 2") ,pandllastquarter=>array(title=>"P and L Last quarter" ) ,last2yearstaxes=>array(title=>"Last 2 Year Taxes - 1", title2=>"Last 2 Year Taxes - 2" ) ,preliminarytitlereport=>array(title=>"Preliminary Title Report" ) ); function task_recurringmark($display=0) { if(!$display) return ""; $out = "R"; return $out; } function task_desktopmark($display=-1) { if(!$display) return ""; $out = "DESKTOP"; return $out; } function task_delete($taskid,$notetext="",$recurr="") { $task = get_task($taskid); if(!is_numeric($taskid)) { foreach($taskid as $currtaskid) $thisrecurringtaskid=list_first($taskid,"_"); return; } if(!$task) return; if($notetext) { $sql = " insert into tblnote ( propertyid , notecreatedby , notecreatedip , notecreateddate , notetext ) values ( ".db_number($task[propertyid])." , ".loginid()." , '$_SERVER[REMOTE_ADDR]' , now() , '".db_col($notetext)."' )"; db_exec($sql,1); if(!loginid()) cache_kickvalue('headernavigation_offernotecount'); }; if($recurr) { $sql = " update tbltask set taskrecurdays=0 ,taskrecurring=0 where taskrecurdays>0 and taskdescription='".db_col($task[taskdescription])."' and taskname='".db_col($task[taskname])."' and propertyid='$task[propertyid]'"; db_exec($sql,1); }; track_the_change($task[propertyid], "Delete Task", $task[taskname],alwaystrack); $sql = " select table_name from information_schema.columns join information_schema.tables using(table_name) where column_name = 'taskid' and table_type <> 'VIEW' and table_type not like '%TEMP%' order by case when table_name <> 'tbltask' then 1 else 99 end "; $qryt = db_query($sql); foreach($qryt as $k =>$row) { $table = $row[table_name] ; $pricolumn = str_replace("tbl","",$table).id; $from1 = " from $table where taskid = $taskid \n"; $sql = " select distinct table_name from information_schema.columns join information_schema.tables using(table_name) where column_name = '{$pricolumn}' and table_name <> '$table' and table_type <> 'VIEW' and table_type not like '%TEMP%';"; $qryt2 = db_query($sql,0,1); foreach($qryt2 as $k =>$row) { $table2 = $row[table_name]; $pricolumn2 = str_replace("tbl","",$table2).'id'; $from2 = " from $table2 where {$pricolumn} > 0 and {$pricolumn} in (select {$pricolumn} $from1 ) \n"; $sql = " select distinct table_name from information_schema.columns join information_schema.tables using(table_name) where column_name = '{$pricolumn2}' and table_name <> '$table2' and table_type <> 'VIEW' and table_type not like '%TEMP%';"; $qryt3 = db_query($sql,0,1); foreach($qryt3 as $k =>$row) { $table3 = $row[table_name] ; $pricolumn3 = str_replace("tbl","",$table3).id; $from3 = " from $table3 where {$pricolumn2} > 0 and {$pricolumn2} in (select {$pricolumn2} $from2 ) \n"; db_exec("delete $from3"); } db_exec("delete $from2"); } db_exec("delete $from1"); } } function get_taskdocuments($taskid,$public="") { $taskid = db_number($taskid); if($taskid<=0) return array(); $sql = " select * from tbldocument left join tbllogin on (documentloadedby = loginid) where taskid = $taskid and bidrequestid = 0 "; if($public) $sql .=" and documentpublic = 1 "; if(vendorid()) $sql .=" and (documentpublic = 1 or documentloadedby = ".loginid() .") "; return db_query($sql); } function get_tasknotes($taskid ) { $taskid = db_number($taskid); $sql = " select * from tblnote join tbllogin on (tbllogin.loginid = tblnote.notecreatedby) left join tblvendor on (tbllogin.vendorid = tblvendor.vendorid) left join tblstaff on (tbllogin.staffid = tblstaff.staffid) left join tblcompany on (tbllogin.companyid = tblcompany.companyid) where noteissystem = 0 and tblnote.taskid = $taskid and tblnote.vendorid = 0"; if(vendorid()) $sql .= " and tblnote.notecreatedby = ".loginid(); $sql .= " order by notecreateddate desc"; $qry = db_query($sql); return $qry; } function get_taskassignedto($taskid) { if(!$taskid) return ""; $taskid = db_number($taskid); $sql = " select * from tbltaskassign left join tblstaff using(staffid) left join tblgroup using(groupid) where taskid = $taskid"; $qry = db_query($sql); foreach($qry as $row) { if($row[groupname]) $out[]=$row[groupname]; if($row[stafffname]) $out[]=$row[stafffname]." ".$row[stafflname]; } if($out) return implode(",",$out); return ""; } function display_tasktype_fieldset($tasktype,$prop) { global $tasktypes; $propertyid = db_number($prop[propertyid]); $Tasktype = $tasktypes[$tasktype]; if(!$tasktype||!$Tasktype) ddie("display_tasktype_fieldset($tasktype) called with an invalid parameter"); $sql = " select * , propertyextensionlabel as fieldname from tblmastertask join tblmastertaskcompany using (mastertaskid) join tblpropertyextension on (mastertaskfieldname = propertyextensionkey) left join tbltask on (tblmastertask.mastertaskid = tbltask.mastertaskid and propertyid = $propertyid) where tblmastertaskcompany.companyid = ".companyid()." and mastertasktype=".db_tick($tasktype)." and mastertaskassignmenttype = '".db_col($prop[propertyassignmenttype])."' "; $qryt = db_query($sql); foreach($qryt as $k=>$row) { //loop through all mts and make sure that if the task is only for certain client, that the current client is in the list if($row[mastertaskclientidlist]&& !$prop[clientid]) unset($qryt[$k]); elseif($row[mastertaskclientidlist]) if(!in_array($prop[clientid],explode(',',$row[mastertaskclientidlist]))) unset($qryt[$k]); } $sql = " select * from tbltask where propertyid = $propertyid and mastertaskid = 0 and tasktype = ".db_tick($tasktype); $qryt2 = db_query($sql); $qryt = array_merge($qryt,$qryt2); if(has_access(addeditpropertytasks)) echo "".button(add)." Add a new '$tasktype' task to $prop[propertyaddress]
    "; if(!$qryt) echo " 

    No '$Tasktype' tasks are setup as for this property "; else { echo ""; foreach($qryt as $k=>$task) { echo ""; if(!$task[taskid]) { $clientname = ""; echo " "; } else { echo " "; } echo ""; } echo "
        Task Name Completed Status
        ".($task[mastertaskname]?$task[mastertaskname]:$task[taskname])." Task will be created automatically when '$task[fieldname]' is updated to '$task[mastertaskfieldvalue]' ".button(view)." ".get_task_status_color($task)." ".($task[mastertaskname]?$task[mastertaskname]:$task[taskname])." "; if($task[taskcompleteddate]) echo "Completed: ".dt_format($task[taskcompleteddate]); elseif($task[taskdeadlinedate]) echo "Due: ".dt_format($task[taskdeadlinedate]); else echo " "; echo " ".get_task_status($task)."
    "; } } function get_task_documentlist($taskid) { $taskid = db_number($taskid); if(!$taskid) return; $sql = "select documentid,1 as pretty from tbldocument where taskid = $taskid and documentvoideddate is null"; $qry=db_query($sql); ob_start(); foreach($qry as $row) echo "
    ".customfield_display_documentupload($row); $out=ob_get_clean(); return $out; } function task_inactivate($taskid) { $sql="update tbltask set taskinactivateddate=now() where taskid=".db_number($taskid); db_exec($sql); //echo "

    Would have run this query: [Inactivate Task]
    $sql
    "; } function set_replaceables_in_email($bodyin,$arrayofreplaceables,$arrayofreplacements) { $replaceablesdata[bodyin]=$bodyin; $replaceablesdata[arrayofreplaceables]=$arrayofreplaceables; $replaceablesdata[arrayofreplacements]=$arrayofreplacements; //d($replaceablesdata,'$replaceablesdata'); foreach($arrayofreplaceables as $rep) { $cleanarrayofreplaceables[]=str_replace('@@',"",$rep); } if(is_array($cleanarrayofreplaceables)) foreach($cleanarrayofreplaceables as $replaceatat) { if(strstr($bodyin,"@@".$replaceatat."@@")) { $bodyin=str_replace("@@".$replaceatat."@@",$arrayofreplacements[$replaceatat],$bodyin); } } return $bodyin; } function task_assigner_notify_taskinactivated($taskarr,$assignedto,$isclienttask=0) { if(!is_array($taskarr)) return; $newtaskarr=$taskarr; foreach($taskarr as $taskarrkey=>$taskarrayinfo) { $newkey=''; if(substr($taskarrkey,0,13)=='clientcompany') $newkey=str_replace('clientcompany','',$taskarrkey); elseif(substr($taskarrkey,0,6)=='client') $newkey=str_replace('client','',$taskarrkey); if($newkey && !isset($newtaskarr[$newkey])) $newtaskarr[$newkey]=$taskarrayinfo; } //d('$newtaskarr',$newtaskarr); $taskcreatedby=$newtaskarr[taskcreatedby]; if(!$taskcreatedby) $taskcreatedby=$newtaskarr[propertycreatedby]; $newtaskarr[taskassignedto]=agent_staff_name($assignedto); $getemail=db_first("select * from tblemail where emailshortname='taskcancelled'"); $emailbody=set_replaceables_in_email($getemail['emailtext'],explode(",",$getemail['emailvariables']),$newtaskarr); if($isclienttask) $assigneremail=clientstaff_emailaddress_fromlogin($taskcreatedby); else $assigneremail=staff_emailaddress_fromlogin($taskcreatedby); $emailto=$assigneremail; $em[replaceables]=$replaceable; $em[ems]=$getemail; $em[to]=$emailto; $em[body]=$emailbody; if(!$emailto || !$getemail[emailfrom] || !$getemail[emailsubject]) { $erroremailbody="Unable to send email from function task_assigner_notify_taskinactivated for"; if($isclienttask) $erroremailbody.="Client Task ID: "; else $erroremailbody.="Task ID: "; $erroremailbody.=$newtaskarr."
    "; $erroremailbody.="To: ".$emailto.'
    '; $erroremailbody.="From: ".$getemail[emailfrom].'
    '; $erroremailbody.="Subject: ".$getemail[emailsubject].'
    '; developer_error('Assigned Inactivation Notice: Missing to from or subject',$erroremailbody); return; } html_mail($emailto,$getemail[emailfrom],$getemail[emailsubject],$emailbody); } function has_mastertaskassignment($mastertaskid) { if ($mastertaskid) { // get the group ids and the staff ids $sql = " select mastertaskcompanyassignedstaffgroups, mastertaskcompanyassignedstaffids from tblmastertask join tblmastertaskcompany using (mastertaskid) where mastertaskid = ".db_id($mastertaskid)." and tblmastertaskcompany.companyid = ".companyid()." "; $mt = db_first($sql); // get the active loginids associated with the groupid->staffids and staffids $sql = " select loginid from tblstaffgroup join tbllogin using (staffid) where companyid = ".companyid()." and logininactive is null and ( groupid in (".db_number_list($mt['mastertaskcompanyassignedstaffgroups']).") or staffid in (".db_number_list($mt['mastertaskcompanyassignedstaffids']).") )"; $lids = db_query($sql); // get the email addresses associated with the loginids // if there is at least ONE email address, return true foreach ($lids as $lid) if (trim(get_loginemailaddress($lid[loginid])) != '') return true; } // otherwise return false return false; } function checkforrequirements($taskid) { $sql = "select * from tbltaskrequirement where taskid = ".$taskid; $taskreqarr = db_query($sql); if($taskreqarr) { foreach($taskreqarr as $trk=>$requirement) { if($requirement[taskrequirementtype] == 'document') { $notfound = 'document'; $sql = "select * from tbldocument where taskid = ".$taskid; $docarr = db_query($sql); foreach($docarr as $dock=>$docrow) { if($docrow[documenttitle] == $requirement[taskrequirementtitle]) { $notfound = ''; break; } } } if($requirement[taskrequirementtype] == 'photo') { $notfound = 'photo'; $sql = "select * from tblimage where taskid = ".$taskid." and photogroupnames = '$requirement[taskrequirementtitle]'"; $imagearr = db_query($sql); foreach($imagearr as $imk=>$imagerow) { if($imagerow[photogroupnames] == $requirement[taskrequirementtitle]) { $notfound = ''; break; } } } if($notfound) $errormessage.="
    You must upload $notfound $requirement[taskrequirementtitle] before you can close this task"; } } return $errormessage; } function inserttaskimage($tempname, $filename, $pgroupname, $propertyid, $taskid, $notetext) { //d('files',$_FILES); //d('post',$_POST); //d('$tempname',$tempname); //d('$filename',$filename); //d('$pgroupname',$pgroupname); //d('$propertyid',$propertyid); //d('$taskid',$taskid); //d('$notetext',$notetext); //ddie('remove before migration'); //check and see if the temp image exists if (is_uploaded_file($tempname)) { $size = getimagesize($tempname); //if we don't get a size //or if the sizes are not valid if (!$size || $size[0] < 1 || $size[1] < 1) { set_message("Image '$filename' is corrupt or invalid, please try a different file.",error); return false; } //get the next imageid from tblimage $imageid = db_nextid('image'); //get the relative photo directory $dirname = getrelativephotodir($imageid); //build the path to the correct place for the image $pathname = "{$_SERVER[DOCUMENT_ROOT]}/photos{$dirname}"; //create the directory if (!mkdir_wrapper($pathname)) { //ddie("Server Configuration Issue: could not create directory : $pathname"); } $docfile = $pathname.$filename; //try and move it if (!move_uploaded_file($tempname, $docfile)) { //ddie("
    Server Configuration Issue: Could not write the file to the directory: $dirname$fname"); } //if we move it successfully //make an entry into the database $sql = " insert into tblimage ( imageid , imagetitle , imagefilename , propertyid , imagedescription , imageloadeddate , imagepublic , taskid , imageloadedby , photogroupnames ) values ( $imageid , ".db_tick($filename)." , ".db_tick($filename)." , ".db_id($propertyid)." , ".db_tick($notetext)." , now() , 1 , ".db_id($taskid)." , ".loginid()." , ".db_tick($pgroupname)." )"; db_exec($sql,1); } }