<?php
class ItemMaster_model extends CI_Model 
{
    public function __construct() 
    {
        parent::__construct();
        $this->load->database();
    }
    // auto complete to primaryAccount Name......
    function getAccountName() {
        $name = $_POST['name_startsWith'];
        $this->db->select('primaryAccountId, primaryAccountName');
        $this->db->where("UPPER(primaryAccountName) LIKE '" . strtoupper($name) . "%'");
        $query = $this->db->get('primaryAccount');
        $data  = array();
        foreach ($query->result_array() as $row) {
            $name = $row['primaryAccountName'] . '|' . $row['primaryAccountId'];
            array_push($data, $name);
        }
        echo json_encode($data);
    }
    // insert data in to data base
    /*public function insert($companyId,$branchId,$accountCode,$accountName,$primaryAccountId,$ledgerId,$category,
    $creditLimit,$openingBalance,$closingBalance,$currentBalance,$typeOfBalance)
    
    // public function insert($data)
    
    {
    echo'insert';
    /*echo $companyId;
    $data=array(
    'companyId'=>$companyId,
    'branchId'=>$branchId,
    'accountCode'=>$accountCode,
    'accountName'=>$accountName,
    'primaryAccountId'=>$primaryAccountId,
    'ledgerId'=>$ledgerId,
    'category'=>$category,
    'creditLimit'=>$creditLimit,
    'openingBalance'=>$openingBalance,
    'closingBalance'=>$closingBalance,
    'currentBalance'=>$currentBalance,
    'typeOfBalance'=>$typeOfBalance
    );
    /*$sql = "SELECT acc_insertAccountRegister(?,?,?,?,?,?,?,?,?,?,?,?)";
    $query  = $this->db->query($sql, array(
    'companyId'=>$companyId,
    'branchId'=>$branchId,
    'accountCode'=>$accountCode,
    'accountName'=>$accountName,
    'primaryAccountId'=>$primaryAccountId,
    'ledgerId'=>$ledgerId,
    'category'=>$category,
    'creditLimit'=>$creditLimit,
    'openingBalance'=>$openingBalance,
    'closingBalance'=>$closingBalance,
    'currentBalance'=>$currentBalance,
    'typeOfBalance'=>$typeOfBalance
    ));
    return $this->db->affected_rows();
    
    // {
    
    if($this->db->affected_rows()>0)
    {
    echo"<script> alert('Data Updated')</script>";
    }
    else
    {
    echo'masg';
    }
    
    print_r($this->db->last_query());
    }
    
    */
    // Insert data in to Account Register
public function insertAccountRegisterData($data, $unitData = array())
{
    $this->db->trans_start();

    // keep baseUnit separately
    $baseUnitId = $data['baseUnit'];
    unset($data['baseUnit']);

    // insert item master
    $this->db->insert('itemMaster', $data);
    $itemMasterId = $this->db->insert_id();

    // insert base unit
    if (!empty($baseUnitId)) {
        $this->db->insert('itemUnit', [
            'itemMasterId' => $itemMasterId,
            'unitId'       => $baseUnitId,
            'multiple'     => 1,
            'status'   => 1 // Add this flag to identify base unit
        ]);
    }

    // insert dynamic unit rows in loop
    if (!empty($unitData)) {
        foreach ($unitData as $unit) {
            // Check if this unit is different from base unit
            if ($unit['unitId'] != $baseUnitId) {
                $this->db->insert('itemUnit', [
                    'itemMasterId' => $itemMasterId,
                    'unitId'       => $unit['unitId'],
                    'multiple'     => $unit['multiple'],
                    'status'       => 1 // Not base unit
                ]);
            }
        }
    }

    $this->db->trans_complete();

    return $this->db->trans_status();
}

    // Fetching  data from database
    public function fetchAccount() {
        $data = array();
	/*	$query = 'SELECT * from acc_accountRegister 
		          WHERE thirdLevelAccountId!="1"
		          AND thirdLevelAccountId!="56"
		          ORDER BY 	accountRegisterId DESC limit 0,10';*/
		  
		  $query = 'SELECT * from acc_accountRegister 
                    WHERE thirdLevelAccountId IS NULL
                    OR (thirdLevelAccountId!="1" AND thirdLevelAccountId!="56") ORDER BY accountRegisterId DESC limit 0,10';
                    
		$query=$this->db->query($query);
		//print_r($this->db->last_query());
		return $query;
     }
	 
	public function getDetailsOfAccountRegister($start,$end){
		
		$primaryAccountType = $this->getprimaryAccountName();
		$primaryAccountName = $this->getprimaryAccountName();
		$ledgername = $this->getledgerName();
		$accountType='' ; 
							
		
	/*	$query = "SELECT * from acc_accountRegister
		          WHERE thirdLevelAccountId!='1'
		          AND thirdLevelAccountId!='56'
		          ORDER BY 	accountRegisterId DESC
		            limit ".$start.",100";
		*/
		
		$query = "SELECT * from acc_accountRegister 
                    WHERE thirdLevelAccountId IS NULL
                    OR (thirdLevelAccountId!='1' AND thirdLevelAccountId!='56') 
		          ORDER BY 	accountRegisterId DESC
		            limit ".$start.",100";
		
		$query=$this->db->query($query);
		
		
		
		  
		 if($query->num_rows() > 0)
				{
							$i=$start;
							$values='';
						foreach ($query->result() as $row){
							$i++;
							$accountName=$row->accountName;
							foreach($primaryAccountType->result() as $r) { 
							if ($row->primaryAccountId == $r->primaryAccountId)
								{ $accountType = $r->primaryAccountType;
									break; } }
						
                            
							$primaryAccount='' ;
							foreach($primaryAccountName->result() as $r) {
								if ($row->primaryAccountId == $r->primaryAccountId) 
								{ $primaryAccount = $r->primaryAccountName; break; } }
												
                            $ledger='' ;
							foreach($ledgername->result() as $r1) {
								if ($row->secondLevelAccountId == $r1->secondLevelAccountId) { 
								$ledger = $r1->secondLevelAccountName;
								break; 
								}
								}
							$values .= '<tr><td><center>'.$i.'</center></td><td>'.$accountName.'</td>';
							$values .= '<td>'.$accountType.'</td><td>'.$primaryAccount.'</td>';
							$values .= '<td>'.$ledger.'</td><td>'.$row->category.'</td>';
							$values .= '<td style="text-align:right">'.number_format($row->creditLimit,2).'</td>';
							$values .= '<td style="text-align:right">'.number_format($row->openingBalance,2).'</td>';
							$values .= '<td style="text-align:right">'.number_format($row->closingBalance,2).'</td>';
							$values .= '<td style="text-align:right">'.number_format($row->currentBalance,2).'</td><td>'.$row->typeOfBalance.'</td>';
							$values .='<td>
									<center>
									<a href="'.base_url('index.php/itemMaster/itemMaster/edit/' . $row->accountRegisterId).'">
                                    <button type="button" class="btn btn-sm btn-primary" value="EDIT" title="edit">
                                        <i class="fa fa-edit"></i>
                                    </button>
									</a>
								
								</center></td></tr>';
					
					}
				}
			$data = array('values'=>$values,'lastValue'=>$i);
			echo json_encode($data);
			}
	  
    // getting data from primaryAccount
    function getprimaryAccountType() {
        $query = $this->db->get('primaryAccount');
        // $query= $this->db->query("CALL acc_listPrimaryAccount()");
        return $query;
    }
    //--------------------------------bhagya 18th march
    //AccountName already exist or not
    function accountName_exists($accountName) {
        $this->db->select('*');
        $this->db->from('itemMaster');
        $this->db->where('itemCode', $accountName);
        $query  = $this->db->get();
        $result = $query->result_array();
		//print_r($result);
				echo  $this->db->last_query();

        return $result;
		
    }
    //AccountCode already exist or not
    function accountCode_exists($accountCode) {
        $this->db->select('*');
        $this->db->from('itemMaster');
        $this->db->where('accountCode', $accountCode);
        $query  = $this->db->get();
        $result = $query->result_array();
        //print_r($result);
		echo  $this->db->last_query();
        return $result;
    }
    //Insert Primary Account through PopUp
    function insertPrimaryAccountPopUp($primaryAccountType, $primaryAccountName) {
        $data = array(
            'primaryAccountType' => $primaryAccountType,
            'primaryAccountName' => $primaryAccountName
        );
        $this->db->insert('primaryAccount', $data);
        return $this->db->affected_rows();
    }
	//Insert Second Level Account through PopUp
    function insertSecondLevelAccountPopUp($primaryAccountId, $secondLevelAccountName) {
        $data = array(
            'primaryAccountId' => $primaryAccountId,
            'secondLevelAccountName' => $secondLevelAccountName
        );
        $this->db->insert('secondLevelAccount', $data);
        return $this->db->affected_rows();
    }
	//Insert Third Level Account through PopUp
    function insertThirdLevelAccountPopUp($primaryAccountId,$secondLevelAccountId,$thirdLevelAccountName) {
		
        $data = array(
            'primaryAccountId' => $primaryAccountId,
            'secondLevelAccountId' => $secondLevelAccountId,
			'thirdLevelAccountName'=>$thirdLevelAccountName
        );
        $this->db->insert('thirdLevelAccount', $data);
        return $this->db->affected_rows();
    }
	
    //Primary Account already exist or not
    function PrimaryAccountName_exists($primaryAccountName, $PrimaryAccountclass) {
        $this->db->select('*');
        $this->db->from('primaryAccount');
        $this->db->where('primaryAccountName', $primaryAccountName);
        $this->db->where('primaryAccountType', $PrimaryAccountclass);
        $query  = $this->db->get();
        $result = $query->result_array();
        //print_r($result);
        return $result;
    }
    //In popUp First Level Account Unique Checking
    function getPrimaryLevelAccountNameRowCount($primaryAccountType, $primaryAccountName) {
        $this->db->select('*');
        $this->db->from('primaryAccount');
        $this->db->where('primaryAccountName', $primaryAccountName);
        $this->db->where('primaryAccountType', $primaryAccountType);
        $query    = $this->db->get();
        $rowCount = $query->num_rows();
        return $rowCount;
    }
	
    //In popUp Second Level value Unique Checking
    function getSecondLevelAccountNameRowCount($primaryAccountId, $secondLevelAccountName) {
        $this->db->select('*');
        $this->db->from('secondLevelAccount');
        $this->db->where('primaryAccountId', $primaryAccountId);
        $this->db->where('secondLevelAccountName', $secondLevelAccountName);
        $query    = $this->db->get();
        $rowCount = $query->num_rows();
        return $rowCount;
    }
    
    //In popUp Second Level value Unique Checking
    function getThirdLevelAccountNameRowCount($secondLevelAccountId, $primaryAccountId, $thirdLevelAccountName) {
       
        $this->db->select('*');
        $this->db->from('thirdLevelAccount');
        $this->db->where('secondLevelAccountId', $secondLevelAccountId);
        $this->db->where('primaryAccountId', $primaryAccountId);
        $this->db->where('thirdLevelAccountName', $thirdLevelAccountName);
        $query    = $this->db->get();
        $rowCount = $query->num_rows();
        return $rowCount;
    }
    //filtering get primaryAccount
    function getprimaryAccount() {
        $primaryAccountType = $_POST['primaryAccountType'];
        $this->db->select('primaryAccountId,primaryAccountName');
        $this->db->where('primaryAccountType', $primaryAccountType);
        $primaryAccount = $this->db->get('primaryAccount');
        //print_r($primaryAccount);
        return $primaryAccount;
    }
    //filtering get Second level
    function getSecondLevelAccount() {
        $primaryAccountId = $_POST['primaryAccountId'];
        $this->db->select('secondLevelAccountId, secondLevelAccountName');
        $this->db->where('primaryAccountId', $primaryAccountId);
        $secondaryName = $this->db->get('secondLevelAccount');
        //print_r($secondaryName);
        return $secondaryName;
    }
    //filtering get Third level
    function getThirdLevelAccount() {
        $secondLevelAccountId = $_POST['secondLevelAccountId'];
        $this->db->select('thirdLevelAccountId, thirdLevelAccountName');
        $this->db->where('secondLevelAccountId', $secondLevelAccountId);
        $thirdLevelName = $this->db->get('thirdLevelAccount');
        return $thirdLevelName;
    }
    //Get accountClass
    function getAccountClass() {
        $this->db->select('primaryAccountType,primaryAccountId');
        $this->db->distinct();
        $query = $this->db->get('primaryAccount');
        return $query;
    }
    //-----------------------------------------------------end bhagya
    public function getprimaryAccountName() {
        $query = $this->db->get('primaryAccount');
        
        // $query= $this->db->query("CALL acc_listPrimaryAccount()");
        return $query;
    }
    // getting data from ledger
    public function getledgerName() {
        $query = $this->db->get('secondLevelAccount');
        return $query;
    }
    function getthirdLevelAccountName() {
        $query = $this->db->get('thirdLevelAccount');
        return $query;
    }
    
    // Deleting data
    function delete($accountRegisterId) {
        // $this->load->model('Accounts_model');
        $this->db->where('itemMasterId', $accountRegisterId);
        $this->db->delete('itemMaster');
        $this->load->model('itemMaster_model');
        if ($this->db->affected_rows() > 0) {
            echo "<script> alert('Data deleted')</script>";
        }
    }
   
    function select($itemMasterId) {
        $this->db->select('*');
        $this->db->where('itemMasterId', $accountRegisterId);
        $q = $this->db->get('itemMaster');
        return $data = $q->result_array();
    }
    function fetchAccountRegisterById($accountRegisterId) {
        $this->db->distinct();
        $this->db->select('itemMaster.*,primaryAccount.*');
        $this->db->from('itemMaster');
		$this->db->join('primaryAccount','primaryAccount.primaryAccountId=itemMaster.primaryAccountId','inner');
        $this->db->where('itemMasterId', $accountRegisterId);
        $this->db->distinct();
        $query = $this->db->get();
        return $query;
    }
    function fetchAccountRegisterByIdedit($accountRegisterId) {
        $this->db->distinct();
        $this->db->select('*');
        $this->db->from('itemMaster');
        $this->db->where('itemMasterId', $accountRegisterId);
        $this->db->distinct();
        $query = $this->db->get();
        return $query;
    }
    // editting data
    function do_edit($accountCode, $accountName, $primaryAccountType, $primaryAccountName, $ledgerName, $thirdLevelAccountName, $category, $creditLimit, $openingBalance, $closingBalance, $currentBalance, $typeOfBalance, $accountRegisterId,$openingBalanceDate) {
        // 'accountRegisterId'=>$accountRegisterId,
        $data = array(
            //'accountRegisterId' => $accountRegisterId,
            //'companyId' => $companyId,
            //'branchId' => $branchId,
            
			'accountCode' => $accountCode,
            'accountName' => $accountName,
            'primaryAccountId' => $primaryAccountType,
            'primaryAccountId' => $primaryAccountName,
            'secondLevelAccountId' => $ledgerName,
            'thirdLevelAccountId' => $thirdLevelAccountName,
            'category' => $category,
            'creditLimit' => $creditLimit,
            'openingBalance' => $openingBalance,
            'closingBalance' => $closingBalance,
            'currentBalance' => $currentBalance,
            'typeOfBalance' => $typeOfBalance,						'openingBalanceDate'=>$openingBalanceDate
        );
        $this->db->where('itemMasterId', $accountRegisterId);
        $this->db->update('itemMaster', $data);
        if ($this->db->affected_rows() > 0) {
            echo "<script> alert('Data Upadted')</script>";
        }
        //print_r($this->db->last_query());
    }
    //pop up fetch primary account..................
    function fetchPrimary() {
        $this->db->select('*');
        $this->db->from('primaryAccount');
        $this->db->order_by("primaryAccountId", "desc");
        $query = $this->db->get();
        return $query;
        // echo $c=$query ->num_rows();
    }
    function fetchPrimaryAccount() {
        /*$this->db->select('*');
        $this->db->from('primaryAccount');
        $this->db->order_by("primaryAccountId", "desc");
        $query = $this->db->get();
        
        // echo $c=$query ->num_rows();
        
        return $query;*/
        $this->db->select('*');
        // $this->db->where('primaryAccountId',$primaryAccountId);
        $this->db->from('primaryAccount');
        $this->db->order_by("primaryAccountName", "desce");
        $query = $this->db->get();
        return $query;
        //->result();
    }
    function selectmax() {
        $this->db->select_max('primaryAccountId');
        $maxid = $this->db->get('primaryAccount');
        $max   = $maxid->row();
        $max->primaryAccountId;
        return $max;
    }
    
    function fetchLedger() {
        $this->db->select('*');
        $this->db->from('secondLevelAccount');
        $this->db->order_by("secondLevelAccountId", "desc");
        $query = $this->db->get();
        // echo $c=$query ->num_rows();
        return $query;
    }
    //*pop up ledger ends here....
    //pop up third level starts here.....
    
    function fetchThirdLevelAccount() {
        $this->db->select('*');
        $this->db->from('thirdLevelAccount');
        $query = $this->db->get();
        // echo $c=$query ->num_rows();
        return $query;
    }
    /*function getsecondLevelName()
    {
    //$departmentName = $this->input->post('dn');
    
    $primaryAccountId = $_POST['primaryAccountId'];
    $this->db->select('primaryAccount, primaryAccountName');
    $this->db->where('primaryAccountId', $primaryAccountId);
    $ledgerName = $this->db->get('ledger');
    echo '<option value="">Select </option>';
    foreach($ledgerName->result() as $cName)
    {
    echo '<option value="' . $cName['ledgerId'] . '">' . $cName['ledgerName'] . '</option>';
    }
    }*/
    function getprimaryAccountType1() {
        // $query = $this->db->query('select * from pl_companyGroupMaster');
        $query = $this->db->get('primaryAccount');
        return $query;
    }
	/*function accountClassName($primaryAccountId) {
		
        // $query = $this->db->query('select * from pl_companyGroupMaster');
        $query = $this->db->get('primaryAccount');
		 $this->db->where('primaryAccountId', $primaryAccountId);
        return $query;
    }*/
    function getprimaryAccountName1() {
        // $query = $this->db->query('select * from pl_companyGroupMaster');
        $query = $this->db->get('primaryAccount');
        return $query;
    }
    function getsecondLevelAccountName() {
        
        // $query = $this->db->query('select * from pl_companyGroupMaster');
        $query = $this->db->get('secondLevelAccount');
        //print_r($this->db->last_query());
        //echo $ledgerName;
        return $query;
    }
    //*pop up third level ends here.....
    /*function getprimaryAccountType()
    {
    
    // $query = $this->db->query('select * from pl_companyGroupMaster');
    
    $query = $this->db->get('primaryAccount');
    return $query;
    }
    */
    function get_number($q) {
        $query = $this->db->query("SELECT * FROM acc_primaryAccount  where primaryAccountName LIKE  '" . $q . "%' limit 50 ");
        // $query = $this->db->get('client');
        if ($query->num_rows() > 0) {
            foreach ($query->result_array() as $row) {
                $row_set['value'] = htmlentities(stripslashes($row['primaryAccountId']));
                $row_set['label'] = htmlentities(stripslashes($row['primaryAccountName'])); //build an array
                $new_row[]        = $row_set;
                // $row_set[] = htmlentities(stripslashes($row['operationNo'])); //build an array
            }
            echo json_encode($new_row); //format the array into json data
        }
    }

    // Base Unit Select Box Starts

   public function getAllBaseUnit()
        {
            $query = $this->db->select('unitId, unitName')
                            ->from('unit')
                            ->get();

            return $query->result_array();
        }


    // Base Unit Select Box Ends

    // Category Select Box Starts

   public function getAllCategory()
        {
            $query = $this->db->select('categoryId, categoryName')
                            ->from('category')
                            ->get();

            return $query->result_array();
        }


    // Category Select Box Ends

    // Add this method to your model
public function searchBrands($searchTerm)
{
    $this->db->select('brandId, brandName');
    $this->db->like('brandName', $searchTerm);
    $this->db->or_like('brandId', $searchTerm);
    $this->db->order_by('brandName', 'ASC');
    $this->db->limit(10);
    $query = $this->db->get('brand');
    
    return $query->result_array();
}

public function getAllBrands()
{
    $this->db->select('brandId, brandName');
    $this->db->order_by('brandName', 'ASC');
    $query = $this->db->get('brand');
    return $query->result_array();
}

public function getBrandById($brandId)
{
    $this->db->select('brandId, brandName');
    $this->db->where('brandId', $brandId);
    $query = $this->db->get('brand');
    return $query->row_array();
}

// Brand AutoComplete Starts
public function getBrandAuto($search){
 $this->db->like('brandName',$search);
 return $this->db->get('brand',10)->result_array();
}

// Brand AutoComplete Ends


public function checkUnitExists($unitName)
{
    $this->db->where('unitName', $unitName);
    $query = $this->db->get('unit');
    return $query->num_rows() > 0;
}

public function insertNewUnit($unitName)
{
    $data = array(
        'unitName' => $unitName,
        'status' => 1  // Add status as 1 (active)
        // Removed createdDate as requested
    );
    
    $this->db->insert('unit', $data);
    return $this->db->insert_id();
}

public function getUnitById($unitId)
{
    $this->db->where('unitId', $unitId);
    $query = $this->db->get('unit');
    return $query->row_array();
}

// Add these functions to your ItemMaster_model class

// Check if brand exists by brandCode
public function checkBrandByCodeExists($brandCode)
{
    $this->db->where('brandCode', $brandCode);
    $query = $this->db->get('brand');
    return $query->num_rows() > 0;
}

// Check if brand exists by brandName
public function checkBrandByNameExists($brandName)
{
    $this->db->where('brandName', $brandName);
    $query = $this->db->get('brand');
    return $query->num_rows() > 0;
}

// Insert new brand
public function insertNewBrand($brandCode, $brandName)
{
    $data = array(
        'brandCode' => $brandCode,
        'brandName' => $brandName,
        'status' => 1  // Set status as 1 (active)
    );
    
    $this->db->insert('brand', $data);
    return $this->db->insert_id();
}
// ngk
// Replace the checkDuplication method in your model
public function checkDuplication($itemCode)
{
    $this->db->where('partNo', $itemCode);
    $query = $this->db->get('acc_itemMaster');
    return $query->num_rows();
}


// Replace the insertExcelDatas method in your model
public function insertExcelDatas($itemCode, $itemName, $category, $openingStock, $stock)
{
    // Debug - remove after testing
    error_log("Inserting: itemCode=$itemCode, itemName=$itemName, category=$category");
    
    $data = array(
        'partNo' => $itemCode,
        'itemName' => $itemName,
        'categoryId' => $category,  // This will now get the value from column A
        'openingStock' => $openingStock,
        'stock' => $stock
    );
    
    $this->db->insert('acc_itemMaster', $data);
    $insertId = $this->db->insert_id();
    
    // Debug - remove after testing
    if ($insertId) {
        error_log("Inserted with ID: $insertId, categoryId: $category");
    } else {
        error_log("Insert failed: " . $this->db->error()['message']);
    }
    
    return $insertId;
}

// Replace getUnitId method
public function getUnitId($baseUnit)
{
    $this->db->select('unitId');
    $this->db->where('unitName', $baseUnit);
    $query = $this->db->get('acc_unit');
    
    if ($query->num_rows() > 0) {
        $row = $query->row();
        return $row->unitId;
    }
    return false;
}

// Replace insertUnit method
public function insertUnit($baseUnit)
{
    $data = array('unitName' => $baseUnit);
    $this->db->insert('acc_unit', $data);
    return $this->db->insert_id();
}

// Replace insertUnitId method
public function insertUnitId($baseUnitId, $multiple, $lastid, $costBase)
{
    $data = array(
        'unitId' => $baseUnitId,
        'multiple' => $multiple,
        'itemMasterId' => $lastid
    );
    
    $this->db->insert('acc_itemUnit', $data);
    return $this->db->insert_id();
}

public function getCategoryId($categoryName)
{
    $this->db->where('categoryName', $categoryName);
    $query = $this->db->get('acc_category');
    $row = $query->row();

    if ($row) {
        return $row->categoryId;
    }
    return 0;
}
// ngk
}
?>