<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class AttributeModel extends MY_Model {

	public $message;

	private $cId;

	function __construct()
	{
		parent::__construct();

		$this->cId = get_company("id");
		$this->load->library('attributes_table');
	}

	public function get_list($params=array())
	{
		$where = array("cId" => $this->cId);
		$this->db->select('*')->from('attributes_group')->where($where)->order_by("sortNo, attrGrpId");
		if (isset($params['page']))
		{
			$page_limit = $this->config->item('page_limit');
			$from = ($params['page']-1) * $page_limit;
			if ($from)
			{
				$this->db->limit($page_limit,$from);
			}
			else
			{
				$this->db->limit($page_limit);
			}
		}
		$result = $this->db->get()->result_array();	
		return $result;
	}

	public function get_details($attrGrpId=0)
	{	
		$result = array();

		$where = array('cId' => $this->cId,'attrGrpId'=>$attrGrpId,'isActive'=>1);
		$this->db->select('attrId,name')->from('attributes')->where($where);
		$result['fldOptions'] = $this->db->get()->result_array();

		$where = array('cId' => $this->cId,'attrGrpId'=>$attrGrpId);
		$this->db->select('group_concat(catId) as cat',false)->from('category_attrgrp')->where($where);
		$tmp = $this->db->get()->row_array();
		
		if(!empty($tmp['cat'])) {
			$result['categories'] = explode(',',$tmp['cat']);
		}
		
		return $result;
	}

	public function save($data=array())
	{
		$data['cId'] = $this->cId;
		$where = array('cId' => $this->cId,'name'=>$data["name"],'isActive'=>1);
		$this->db->select("attrGrpId");
		$this->db->from("attributes_group");
		if (!empty($data["attrGrpId"])) {
			$where['attrGrpId!='] = $data["attrGrpId"];
		}
		$this->db->where($where)->limit(1);
		$result = $this->db->get()->row_array();

		if(!empty($result) && !empty($result["attrGrpId"]))
		{
			$this->message = "Attribute name already exists";
			return false;
		}
		$catId = $this->attributes_table->save($data);

		if (!$catId)
		{
			return false;
		}

		if (!empty($data["itemIds"]))
		{
			$data["catId"] = $catId;
			$this->addItems($data);
		}

		return $catId;
	}

	public function saveorder($data=array())
	{
		foreach($data as $attr)
		{
			$this->db->update("attributes_group", array(
				"sortNo" => $attr["sortNo"]
			), array(
				"cId" => $this->cId,
				"attrGrpId" => $attr["attrGrpId"]
			));
		}

		return true;
	}

	public function delete($attrGrpId='')
	{
		if (!$attrGrpId)
		{
			$this->message = lang('INVALID_REQUEST');
			return false;
		}

		//Cannot delete a record if one of its child attrId exist in ob_item_attr_link
		$this->db->select('a.*,count(ial.itemAttrId) AS itemlink')->from('attributes AS a');
		$this->db->join('item_attr_link as ial','ial.cId=a.cId AND ial.attrId=a.attrId ','left');
		$this->db->where(array('a.cId' => $this->cId, 'a.attrGrpId' => $attrGrpId));
		$this->db->group_by('a.attrGrpId');
		$this->db->having('itemlink>',0);
		$attributes = $this->db->get()->result_array();
		
		if ($attributes)
		{
			$this->message = lang('ATTR_LINKED');
			return false;
		}

		$this->db->delete('attributes_group', array('cId' => $this->cId,'attrGrpId'=>$attrGrpId,'grpCode'=>''));
		if ($this->db->affected_rows())
		{
			$this->db->delete('attributes', array('cId' => $this->cId,'attrGrpId'=>$attrGrpId));
			$this->db->delete('category_attrgrp', array('cId' => $this->cId,'attrGrpId'=>$attrGrpId));
			return true;
		}

		return false;
	}

	public function resetserial($data=array())
	{
		// Trans items new type serial
		$query = '';
		$query .= 'UPDATE ob_trans_items AS dest, (';
		$query .= 'SELECT ti.id, ti.itemId, GROUP_CONCAT(ias.serialNo) AS serialNos from ob_item_attr_serial AS ias ';
		$query .= 'LEFT JOIN ob_trans_serial AS ts ON tsType="T" AND ts.asId=ias.id ';
		$query .= 'LEFT JOIN ob_trans_items AS ti ON ti.id=ts.tsItemId ';
		$query .= 'WHERE ti.cId=' . $this->cId . ' AND ti.itemId=' . $data["itemId"] . ' GROUP BY ti.id ';
		$query .= ') AS src SET dest.itemAttrId=0, dest.itemDesc=CONCAT(dest.itemDesc, src.serialNos) WHERE dest.id=src.id;';
		// echo $query . PHP_EOL;
		$this->db->query($query);

		// Stock items new type serial
		$query = '';
		$query .= 'UPDATE ob_stock_items AS dest, (';
		$query .= 'SELECT ti.id, ti.itemId, GROUP_CONCAT(ias.serialNo) AS serialNos from ob_item_attr_serial AS ias ';
		$query .= 'LEFT JOIN ob_trans_serial AS ts ON tsType="S" AND ts.asId=ias.id ';
		$query .= 'LEFT JOIN ob_stock_items AS ti ON ti.id=ts.tsItemId ';
		$query .= 'WHERE ti.cId=' . $this->cId . ' AND ti.itemId=' . $data["itemId"] . ' GROUP BY ti.id ';
		$query .= ') AS src SET dest.itemAttrId=0, dest.description=CONCAT(dest.description, src.serialNos) WHERE dest.id=src.id;';
		// echo $query . PHP_EOL;
		$this->db->query($query);

		// Trans items old type serial
		$query = '';
		$query .= 'UPDATE ob_trans_items AS dest, (';
		$query .= 'SELECT GROUP_CONCAT(a.attrId) AS attrIds, GROUP_CONCAT(DISTINCT a.name) AS serialNos, ti.itemAttrId, ti.itemId from ob_trans_items AS ti ';
		$query .= 'LEFT JOIN ob_item_attr_link AS ial ON ial.itemAttrId=ti.itemAttrId ';
		$query .= 'LEFT JOIN ob_attributes AS a ON a.attrId=ial.attrId ';
		$query .= 'WHERE ti.cId=' . $this->cId . ' AND ti.itemId=' . $data["itemId"] . ' AND (a.name != NULL OR a.name != "") ';
		$query .= 'GROUP BY ti.itemAttrId ';
		$query .= ') AS src SET dest.itemAttrId=0, dest.itemDesc=CONCAT(dest.itemDesc, src.serialNos) WHERE dest.itemId=src.itemId AND dest.itemAttrId=src.itemAttrId;';
		// echo $query . PHP_EOL;
		$this->db->query($query);

		// Stock items old type serial
		$query = '';
		$query .= 'UPDATE ob_stock_items AS dest, (';
		$query .= 'SELECT GROUP_CONCAT(a.attrId) AS attrIds, GROUP_CONCAT(DISTINCT a.name) AS serialNos, ti.itemAttrId, ti.itemId from ob_stock_items AS ti ';
		$query .= 'LEFT JOIN ob_item_attr_link AS ial ON ial.itemAttrId=ti.itemAttrId ';
		$query .= 'LEFT JOIN ob_attributes AS a ON a.attrId=ial.attrId ';
		$query .= 'WHERE ti.cId=' . $this->cId . ' AND ti.itemId=' . $data["itemId"] . ' AND (a.name != NULL OR a.name != "") ';
		$query .= 'GROUP BY ti.itemAttrId ';
		$query .= ') AS src SET dest.itemAttrId=0, dest.description=CONCAT(dest.description, src.serialNos) WHERE dest.itemId=src.itemId AND dest.itemAttrId=src.itemAttrId;';
		// echo $query . PHP_EOL;
		$this->db->query($query);

		$query = '';
		$query .= 'DELETE a, ial FROM ob_attributes a ';
		$query .= 'LEFT JOIN ob_item_attr_link AS ial ON ial.attrId=a.attrId ';
		$query .= 'LEFT JOIN ob_trans_items AS ti ON ti.itemAttrId=ial.itemAttrId ';
		$query .= 'WHERE ti.cId=' . $this->cId . ' AND ti.itemId=' . $data["itemId"] . ';';
		// echo $query . PHP_EOL;
		$this->db->query($query);

		$query = '';
		$query .= 'DELETE a, ial FROM ob_attributes a ';
		$query .= 'LEFT JOIN ob_item_attr_link AS ial ON ial.attrId=a.attrId ';
		$query .= 'LEFT JOIN ob_stock_items AS ti ON ti.itemAttrId=ial.itemAttrId ';
		$query .= 'WHERE ti.cId=' . $this->cId . ' AND ti.itemId=' . $data["itemId"] . ';';
		// echo $query . PHP_EOL;
		$this->db->query($query);

		$query = '';
		$query .= 'DELETE ias, iasc, ts FROM ob_item_attr_serial ias ';
		$query .= 'LEFT JOIN ob_item_attr_serial_cur iasc ON iasc.asid=ias.id ';
		$query .= 'LEFT JOIN ob_trans_serial ts ON ts.tsType="T" AND ts.asId=ias.id ';
		$query .= 'LEFT JOIN ob_trans_items ti ON ti.id=ts.tsItemId ';
		$query .= 'WHERE ti.cId=' . $this->cId . ' AND ti.itemId=' . $data["itemId"] . ';';
		// echo $query . PHP_EOL;
		$this->db->query($query);

		$query = '';
		$query .= 'DELETE ias, iasc, ts FROM ob_item_attr_serial ias ';
		$query .= 'LEFT JOIN ob_item_attr_serial_cur iasc ON iasc.asid=ias.id ';
		$query .= 'LEFT JOIN ob_trans_serial ts ON ts.tsType="S" AND ts.asId=ias.id ';
		$query .= 'LEFT JOIN ob_stock_items ti ON ti.id=ts.tsItemId ';
		$query .= 'WHERE ti.cId=' . $this->cId . ' AND ti.itemId=' . $data["itemId"] . ';';
		// echo $query . PHP_EOL;
		$this->db->query($query);

/*
		$this->db->update("trans_items", array(
			"itemAttrId" => "0"
		), array(
			"cId" => $this->cId,
			"itemId" => $data["itemId"]
		));

		$this->db->update("stock_items", array(
			"itemAttrId" => "0"
		), array(
			"cId" => $this->cId,
			"itemId" => $data["itemId"]
		));
*/

		if (!empty($data["serialNos"]))
		{
			$itemAttr = $this->db->select("cId, itemAttrId")->from("item_attr")->where(array(
				"cId" => $this->cId,
				"itemId" => $data["itemId"]
			))->limit(1)->get()->row_array();
			$itemAttrId = $itemAttr["itemAttrId"];

			foreach($data["serialNos"] as $serialNo)
			{
				$this->db->insert("item_attr_serial", array(
					"itemAttrId" => $itemAttr["itemAttrId"],
					"serialNo" => $serialNo,
					"cId" => $itemAttr["cId"]
				));
				$asId = $this->db->insert_id();
				$this->db->insert("item_attr_serial_cur", array(
					"asid"	=> $asId,
					"cId"	=> $itemAttr["cId"],
					"bId"	=> get_company('mainbId'),
					"curStk"	=> 1
				));
			}
		}

		return true;
	}

	public function traceserial($data=array())
	{
		if (!empty($data["serialNos"]))
		{
			$serialNos = "\"" . implode("\", \"", $data["serialNos"]) . "\"";

			$query = '';
			$query .= 'SELECT * FROM (' . PHP_EOL;
			$query .= 'SELECT "Trans" AS frm, t.cId, t.bId, c.name AS branchName, t.id, t.tType, t.tNo, t.tDate, ti.id AS transItemId, ti.qty, ti.unit, ti.itemAttrId, ti.itemId, ti.itemName, ias.id as asId, ias.serialNo, iasc.bId AS bIds, iasc.curStk AS curStk, t.conId, t.conName' . PHP_EOL;
			$query .= 'FROM ob_item_attr_serial AS ias' . PHP_EOL;
			$query .= 'JOIN ob_trans_serial AS ts ON ts.asId=ias.id AND tsType="T"' . PHP_EOL;
			$query .= 'LEFT JOIN ob_trans_items AS ti ON ti.id=ts.tsItemId' . PHP_EOL;
			$query .= 'LEFT JOIN ob_trans AS t ON t.id=ti.transId' . PHP_EOL;
			$query .= 'LEFT JOIN ob_contacts AS c ON c.id=t.bId' . PHP_EOL;
			$query .= 'LEFT JOIN ob_item_attr_serial_cur AS iasc ON iasc.asid=ias.id AND iasc.bId=t.bId' . PHP_EOL;
			$query .= 'WHERE ias.serialNo IN (' . $serialNos . ')' . PHP_EOL;
			$query .= 'UNION ALL' . PHP_EOL;
			$query .= 'SELECT "Stock" AS frm, t.cId, "-" AS bId, "-" AS branchName, t.id, t.sType AS tType, "-" AS tNo, t.sDate AS tDate, ti.id AS transItemId, ti.qty, ti.unit, ti.itemAttrId, ti.itemId, "-" AS itemName, ias.id as asId, ias.serialNo, iasc.bId AS bIds, iasc.curStk AS curStk, "-" AS conId, "-" AS conName' . PHP_EOL;
			$query .= 'FROM ob_item_attr_serial AS ias' . PHP_EOL;
			$query .= 'JOIN ob_trans_serial AS ts ON ts.asId=ias.id AND tsType="S"' . PHP_EOL;
			$query .= 'LEFT JOIN ob_stock_items AS ti ON ti.id=ts.tsItemId' . PHP_EOL;
			$query .= 'LEFT JOIN ob_stock_entries AS t ON t.id=ti.sId' . PHP_EOL;
			$query .= 'LEFT JOIN ob_item_attr_serial_cur AS iasc ON iasc.asid=ias.id AND iasc.bId=t.locId' . PHP_EOL;
			$query .= 'WHERE ias.serialNo IN (' . $serialNos . ')' . PHP_EOL;
			$query .= ') AS st ORDER BY st.tDate, st.id;';
		}
		else if (!empty($data["transIds"]))
		{
			$transIds = "\"" . implode("\", \"", $data["transIds"]) . "\"";
			$query = '';
			$query .= '';
			$query .= 'SELECT t.id AS transId, t.tType, t.tDate, t.tNo, t.conId, t.conName, ti.id AS transItemId, ti.itemId, ti.itemName, ti.itemAttrId,';
			$query .= 'GROUP_CONCAT(DISTINCT ias.id SEPARATOR ",\n") AS asIds,';
			$query .= 'GROUP_CONCAT(DISTINCT ias.serialNo SEPARATOR ",\n") AS serialNos,';
			$query .= 'GROUP_CONCAT(DISTINCT a.attrId ORDER BY a.attrGrpId SEPARATOR ",\n") AS attrLinks,';
			$query .= 'GROUP_CONCAT(DISTINCT a.name ORDER BY a.attrGrpId SEPARATOR ",\n") AS attrs,';
			$query .= 'GROUP_CONCAT(DISTINCT ag.name ORDER BY a.attrGrpId SEPARATOR ",\n") AS attrGrpName';
			$query .= ' FROM ob_trans_items AS ti';
			$query .= ' LEFT JOIN ob_trans AS t ON t.id=ti.transId';
			$query .= ' LEFT JOIN ob_item_attr AS ia ON ia.itemAttrId=ti.itemAttrId';
			$query .= ' LEFT JOIN ob_trans_serial AS ts ON ts.tsItemId=ti.id AND tsType="T"';
			$query .= ' LEFT JOIN ob_item_attr_serial AS ias ON ias.id=ts.asId';
			$query .= ' LEFT JOIN ob_item_attr_link AS ial ON ial.itemAttrId=ti.itemAttrId';
			$query .= ' LEFT JOIN ob_attributes AS a ON a.attrId=ial.attrId';
			$query .= ' LEFT JOIN ob_attributes_group AS ag ON ag.attrGrpId=a.attrGrpId';
			$query .= ' WHERE t.id IN (' . $transIds . ')';
			$query .= ' GROUP BY ti.id, ti.itemAttrId';
			$query .= ' ORDER BY t.tDate;';
		}

		// echo $query;exit;

		$_query = $this->db->query($query);

		$result = $_query->result_array();

		return $result;
	}
}

