crm-summit/app/Controllers/Kanban.php

<?php
namespace App\Controllers;
use CodeIgniter\Controller;

class Kanban extends BaseController {

	protected $data = array();
	
	function __construct() {
		$this->data['card_priorities'] = array( 'L' => 'Low', 'M' => 'Medium', 'H'=>'High' );
	} 
	
	public function index() {
		$db = \Config\Database::connect();
		$sql = "SELECT b.boardid, b.`boardname` FROM kb_boarduser bu
			LEFT JOIN kb_boards b ON bu.`boardid`=b.`boardid`
			WHERE bu.`userid`=".$_SESSION['userid']." order by b.createdate desc";
		$query = $db->query($sql);
		$results = $query->getResultArray(); 
		$data['boards'] = $results;
		return view('kb_index',$data);		
	}
	
	public function view($boardid) {
		$db = \Config\Database::connect();
		// get board data
		$sql = "SELECT * FROM kb_boards where boardid=$boardid";
		$query = $db->query($sql);
		$results = $query->getResultArray(); 
		$data['boards'] = $results;
		// lists
		$sql = "SELECT * FROM kb_lists where boardid=$boardid order by listindex";
		$query = $db->query($sql);
		$results = $query->getResultArray(); 
		$data['lists'] = $results;
		// cards
		$sql = "SELECT u.`firstname`,u.`lastname`, c.* FROM kb_cards c 
			JOIN users u ON c.`owner_userid`=u.userid
			WHERE c.listid IN (SELECT listid FROM kb_lists WHERE boardid='$boardid')";
		$query = $db->query($sql);
		$results = $query->getResultArray(); 
		$data['cards'] = $results;
		$data['card_priorities'] = $this->data['card_priorities'];
		return view('kb_view',$data);
	}
	
	public function board_edit($boardid) {
		$db = \Config\Database::connect();
		$sql = "select * from users where enddate is null";
		$query = $db->query($sql);
		$results = $query->getResultArray();
		$data['users'] = $results;
		$data['boardid'] = $boardid;
		if($boardid != 0) {
			$sql = "select * from kb_boards where boardid=$boardid";
			$query = $db->query($sql);
			$results = $query->getResultArray();
			$data['boards'] = $results;
			$sql = "select * from kb_lists where boardid=$boardid order by listindex";
			$query = $db->query($sql);
			$results = $query->getResultArray();
			$data['lists'] = $results;
			$sql = "select userid from kb_boarduser where boardid=$boardid";
			$query = $db->query($sql);
			$results = $query->getResultArray();
			$data['boarduser'] = $results;
		}
		
		if ($this->request->getMethod() === 'POST') {
			$data = $this->request->getVar();
			$boardid = $data['boardid'];
			$boardname = $data['boardname'];
			$admin_userid = $data['admin_userid'];
			$boarduser = $data['boarduser'];
			$nboarduser = implode( ',', $boarduser);
			$listid_delete = $data['listid_delete'];
			if(isset($data['lists'])) { 
				$lists = $data['lists']; 
				$listid = $data['listid'];
			}
			
			$rules = [
				'boardname' => 'required',
				'admin_userid' => 'required',
				'lists' => 'required'
			];

			if($this->validate($rules)) {
				if($boardid == 0) { 
					//new board
					// board query
					$sql = "insert into kb_boards(boardname, admin_userid, createdate) values ( '$boardname', '$admin_userid', NOW() )";
					//echo "<br/>$sql";
					$query = $db->query($sql);
					$boardid = $db->insertID();
					//$boardid = "1";
					//echo "<br/>$boardid";
					
					// boarduser query
					$quser = '';
					foreach($boarduser as $quserid ) { $quser .= "($boardid, $quserid),"; }
					$quser = rtrim($quser, ',');
					$sql = "insert ignore into kb_boarduser(boardid, userid) values $quser";
					$query = $db->query($sql);
					//echo "<br/>$sql";
					
					// list query
					$qlist = '';
					foreach($lists as $qlistindex => $qlistname) {
						$qlistname = $qlistname;
						$qlist .= "('$qlistname', $qlistindex, $boardid, NOW()),";
					}
					$sql = "insert into kb_lists(listname, listindex, boardid,createdate) values $qlist";
					$sql = rtrim($sql, ',');
					//echo "<br/>$sql";
					$query = $db->query($sql);
					return view('form_success');
				} else { // edit board
					/*
					echo "<pre>";
					print_r($data);
					echo "</pre>";
					*/
					// board query
					$sql = "update kb_boards set boardname='$boardname', admin_userid='$admin_userid' where boardid=$boardid ";
					//echo "<br/>$sql";
					$query = $db->query($sql);
					
					// boarduser query
					$sql = "delete from kb_boarduser where boardid=$boardid and userid not in ($nboarduser)";
					//echo "<br/>$sql";
					$query = $db->query($sql);
					$quser = '';
					foreach($boarduser as $quserid ) { $quser .= "($boardid, $quserid),"; }
					$quser = rtrim($quser, ',');
					$sql = "insert ignore into kb_boarduser (boardid, userid) values $quser";
					//echo "<br/>$sql";
					$query = $db->query($sql);
					
					// list query
					// delete
					if( $data['listid_delete'] != '' ) { 
						$listid_del = $data['listid_delete'];
						$sql = "delete from kb_lists where listid in ($listid_del)";
						//echo "<br/>$sql";
						$query = $db->query($sql);
					}
					// insert and update
					$qlist = '';
					foreach($lists as $qlistindex => $qlistname) {
						$qlistid = $listid[$qlistindex];
						if($qlistid == 0) { $qlist .= "('$qlistname', $qlistindex, $boardid, NOW()),"; } 
						else {
							$sql = "update kb_lists set listname='$qlistname', listindex=$qlistindex where listid=$qlistid";
							//echo "<br/>$sql";
							$query = $db->query($sql);
						}
					}
					if($qlist != '') {
						$sql = "insert into kb_lists(listname, listindex, boardid,createdate) values $qlist";
						$sql = rtrim($sql, ',');
						//echo "<br/>$sql";
						$query = $db->query($sql);
					}
					return view('form_success');
				}
			} else { return view('kb_board_edit', $data); }
		} else {
			return view('kb_board_edit', $data);
		}
	}
	
	public function list_edit($listid) {
		$db = \Config\Database::connect();
		$data['listid'] = $listid;
		$sql = "select * from kb_lists where listid='$listid'";
		$query = $db->query($sql);
		$results = $query->getResultArray();
		$data['lists'] = $results;
		if ($this->request->getMethod() === 'POST') {
			$data = $this->request->getVar();
			$listname = $data['listname'];
			$sql = "update kb_lists set listname='$listname' where listid='$listid'";
			$query = $db->query($sql);
			return view('form_success');
		}
		return view('kb_list_edit', $data);
	}
	
	public function card_edit($listid, $cardid) {
		$db = \Config\Database::connect();
		$data['listid'] = $listid;
		$data['cardid'] = $cardid;
		$data['card_priorities'] = $this->data['card_priorities'];
		
		$sql = "select * from users where enddate is null";
		$query = $db->query($sql);
		$results = $query->getResultArray();
		$data['users'] = $results;
		
		$sql = "select * from kb_lists where boardid=(select boardid from kb_lists where listid=$listid)";
		$query = $db->query($sql);
		$results = $query->getResultArray();
		$data['lists'] = $results;
		
		if($cardid != 0) {
			$sql = "select * from kb_cards where cardid=$cardid";
			$query = $db->query($sql);
			$results = $query->getResultArray();
			$data['cards'] = $results;
		}
		
		if ($this->request->getMethod() === 'POST') {
			$data = $this->request->getVar();
			$listid = $data['listid'];
			$cardid = $data['cardid'];
			$cardtext = $data['cardtext'];
			$owner_userid = $data['owner_userid'];
			$priority = $data['priority'];
			$creator_userid = $_SESSION['userid'];
			$rules = [ 
				'cardtext' => 'required' 
			];

			if($this->validate($rules)) {
				if($cardid == 0) { 
					//new card
					$sql = "insert into kb_cards(listid, priority, cardtext, owner_userid, creator_userid, createdate) values ( '$listid', '$priority', '$cardtext', '$owner_userid', '$creator_userid', NOW() )";
					$query = $db->query($sql);
					return view('form_success');
				} else {
					$sql = "update kb_cards set listid=$listid, priority='$priority', cardtext='$cardtext', owner_userid='$owner_userid' where cardid=$cardid";
					$query = $db->query($sql);
					return view('form_success');
				}
			} else {
			
			}
		}
		return view('kb_card_edit', $data);
	}
	
	public function card_move($listid, $cardid) {
		$db = \Config\Database::connect();
		echo "card $cardid, list $listid";
		$sql = "update kb_cards set listid=$listid where cardid=$cardid";
		$query = $db->query($sql);
	}
}