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

class Data_import extends CI_Controller {

  public $page_data=array();

  function __construct()
  {
    parent::__construct();
    //Calling Initial Functions
    $this->initial();
  }

  public function initial()
  {
    ini_set('max_execution_time', 5000);
    ini_set("memory_limit", "-1");

    $this->load->model('User_model','User');
    $this->load->model('Work_model', 'Work');
    $this->load->model('Client_model', 'Client');
    $this->load->model('Supplier_model', 'Supplier');
    $this->load->model('Purchase_bill_model', 'Purchases');
    $this->load->model('Subcontracts_model', 'Subcontracts');
    $this->load->model('Purchase_order_model', 'Purchase_order');
    $this->load->model('Labour_model', 'Labour');
    $this->load->model('Labour_wages_model', 'Labour_wages');
    $this->load->model('Stock_model', 'Stock');
    $this->load->model('Account_model', 'Account');
    $this->load->model('Settings_model', 'Settings');
    $this->load->model('Bills_model', 'Bills');
    $this->load->library('Stock_management','stock_management');
    $this->load->model('Salary_slips_model', 'Salary_slips');
    $this->load->library('Bills_management','bills_management');
    $this->load->model('Employees_model', 'Employee');

    //        Check Developer Logged in or Not
    // if($this->session->userdata('user_login_type')!='developer'){
    //     redirect(base_url(), 'refresh');
    // }

    //        File Directory
    $this->page_data['directory'] = "data_import";

  }
  public function index()
  {
    $this->add();
  }
  public function add()
  {
    $this->page_data['page_name'] = 'data_import';
    $this->load->view('Index',$this->page_data);
  }
  public function import()
  {

    if ($_FILES['csv']["size"] > 0) {
      $dup_data = array();
      $filename = $_FILES["csv"]["tmp_name"];
      if (($getfile = fopen($filename, "r")) !== FALSE) {
        $data = fgetcsv($getfile, 1000, ",");
        $count = $success = $error = $dup = 0;
        $error_sl_no = array();
        $sl_no=1;
        while (($data = fgetcsv($getfile, 1000, ",")) !== FALSE) {
          $sl_no++;
          $num = count($data);
          $slice = $data;
          $data_type = $this->security->xss_clean($this->input->post('data_type'));
          if($data_type=="projects"){
            if($slice[0]!='' && $slice[1]!=''){
              $client_data['client_name'] = $slice[1];
              $client_data['created_date'] = date('Y-m-d H:i:s');
              $client_data['created_by'] = $this->session->userdata('user_id');
              $client_count = $this->Client->select_client('client_id',array("client_name"=>$client_data['client_name']));
              if($client_count->num_rows()==0){
                $client_result = $this->Client->insert_client($client_data);
                $project_data['client_id'] = $client_result['insert_id'];
              }else{
                $project_data['client_id'] = $client_count->row()->client_id;
              }
              $project_data['work_name'] = $slice[0];
              // $project_data['project_management_commission'] = 0;
              $project_data['work_location'] = $slice[2];
              $project_data['work_type'] = $slice[3];
              $project_data['total_budget_amount'] = str_replace(',', '',$slice[4]);
              $project_data['work_description'] = $slice[5];
              $project_data['created_date'] = date('Y-m-d H:i:s');
              $project_data['created_by'] = $this->session->userdata('user_id');
              $project_result = $this->Work->insert_work($project_data);
              if($project_result['status'] == 1){
                // Create Stock Location
                $loc_data['location_type'] = 'work_site';
                $loc_data['location_name'] = $project_data['work_name'];
                $loc_data['work_id'] = $project_result['insert_id'];
                $result =  $this->Stock->insert_stock_location($loc_data);

                $success++;
              }else {
                $error++;
                $error_sl_no[] = $sl_no;
              }
            }
          }elseif($data_type=="client_payments"){
            if($slice[0]!='' && $slice[2]!=''){
              $project_name = $slice[0];
              $project_data = $this->Work->select_work("*", array("work_name" => $project_name));
              if($project_data->num_rows()==1){
                $project_data = $project_data->row();
                $account_head_id = $this->account_head_id($slice[4],36,'');
                if($account_head_id!=0 && $account_head_id!=null){
                  $transaction_data['tr_type'] = 'client_payment_transaction';
                  $transaction_data['reference_id'] = $project_data->work_id;
                  $transaction_data['reference_type'] = 'project';
                  $transaction_data['tr_description'] = ucwords(str_replace('_',' ',$transaction_data['tr_type']));
                  $transaction_data['tr_reference_no'] = $slice[6];
                  $transaction_data['tr_remarks'] = $slice[5];
                  $transaction_data['created_date'] = date('Y-m-d H:i:s');
                  $transaction_data['created_by'] = $this->session->userdata('user_id');
                  $transaction_data['tr_credit_acc'] = $account_head_id;
                  $transaction_data['tr_debit_acc'] = 2;//Client Payment Receipts
                  $transaction_data['tr_amount'] = $slice[2];
                  $transaction_data['tr_date'] = date('Y-m-d',strtotime($slice[3]));
                  $transaction_result =  $this->Account->create_account_transaction($transaction_data);
                  if($transaction_result['status'] == 1){
                    $success++;
                  }else {
                    $error++;
                    $error_sl_no[] = $sl_no;
                  }
                }else {
                  $error++;
                  $error_sl_no[] = $sl_no;
                }
              }else{
                $error++;
                $error_sl_no[] = $sl_no;
              }
            }
          }elseif($data_type=="expense_bills"){
            if($slice[2]!=''){
              $project_name = $slice[0];
              $project_data = $this->Work->select_work("*", array("work_name" => $project_name));
              $user_id = $this->user_id($slice[1],'supervisor');
              if($project_data->num_rows()==1 && $user_id !=0){
                $project_data = $project_data->row();

                $bill_data['bill_type'] = $bill_data1['bill_type'] = 'expense';
                $bill_data['work_id'] = $bill_data1['b.work_id'] = $project_data->work_id;
                $credit_account_head_id = $this->account_head_id($slice[2],11,'');
                if($slice[5]==''){$slice[5]=0;}
                if($slice[8]==''){$slice[8]=0;}
                if($credit_account_head_id!=0){
                  $bill_data['bill_credit_ac'] = $bill_data1['bill_credit_ac'] = $credit_account_head_id;
                  $bill_data['bill_no'] = $bill_data1['bill_no'] = $slice[6];
                  if($slice[8]!='' && $slice[8]!=' ' && $slice[8]!=0){
                    $bill_data['bill_gross_amount'] = $bill_data1['bill_gross_amount'] = $slice[8];
                  }else{
                    $bill_data['bill_gross_amount'] = $bill_data1['bill_gross_amount'] = $slice[5];
                  }
                  $bill_already_created_id = 0;
                  if($slice[7]!=''){
                    $bill_data['bill_date'] =$bill_data1['bill_date'] = date('Y-m-d',strtotime($slice[7]));
                    // check if Bill Already Created when Paid Amount is Less than BillAmount
                    if($slice[5]<$bill_data['bill_gross_amount'] && $bill_data['bill_no']!=''){
                      $bill_count = $this->Bills->select_bills("b.bill_id",$bill_data1,'expense_bill');
                      if($bill_count->num_rows()==1){
                        $bill_already_created_id = $bill_count->row()->bill_id;
                      }
                    }
                  }else{
                    if($slice[4]!=''){
                      $bill_data['bill_date'] = date('Y-m-d',strtotime($slice[4]));
                    }
                  }
                  if($bill_already_created_id==0){
                    $bill_data['bill_tax_amount'] = $bill_data['bill_tds_amount'] = 0;
                    $bill_data['bill_amount'] = $bill_data['bill_gross_amount'] + $bill_data['bill_tax_amount'] - $bill_data['bill_tds_amount'];
                    $bill_data['bill_payable_amount'] = $bill_data['bill_amount'];
                    $bill_data['bill_consignee_name'] = $slice[3];
                    $bill_data['bill_description'] = $slice[12];
                    $bill_data['bill_remarks'] = $slice[13];
                    $bill_data['created_date'] = date('Y-m-d H:i:s');
                    $bill_data['created_by'] = $user_id;
                    $bill_result =  $this->Bills->create_bills($bill_data);
                    if ($bill_result['status'] == 1) {
                      echo "<br/>Sl No: ".$sl_no." - Expense Bill Created Successfully";
                      if($slice[9]=='CASH' || $slice[9]==''){
                        $debit_account_head_id = 1;
                      }else{
                        $debit_account_head_id = $this->account_head_id($slice[14],36,'');
                      }
                      if($debit_account_head_id!=0){
                        $transaction_data['tr_type'] = 'expense_bill_transaction';
                        $transaction_data['reference_id'] = $bill_result['insert_id'];
                        $transaction_data['reference_type'] = 'expense_bill';
                        $transaction_data['tr_description'] = ucwords(str_replace('_',' ',$transaction_data['tr_type']));
                        $transaction_data['tr_reference_no'] = $slice[10];
                        $transaction_data['tr_mode'] = $slice[9];
                        if($slice[11]!=''){
                          $transaction_data['tr_reference_date'] = date('Y-m-d',strtotime($slice[11]));
                        }
                        $transaction_data['tr_remarks'] = $slice[13];
                        $transaction_data['created_date'] = date('Y-m-d H:i:s');
                        $transaction_data['created_by'] = $user_id;
                        $transaction_data['tr_credit_acc'] = $credit_account_head_id;
                        $transaction_data['tr_debit_acc'] = $debit_account_head_id;
                        $transaction_data['tr_amount'] = $slice[5];
                        $transaction_data['tr_date'] = date('Y-m-d',strtotime($slice[4]));
                        $transaction_result =  $this->Account->create_account_transaction($transaction_data);
                        if($transaction_result['status'] == 1){
                          echo "<br/>Sl No: ".$sl_no." - Transaction Created Successfully";
                          $success++;
                        }else {
                          echo "<br/>Sl No: ".$sl_no." - Transaction Creation Error";
                          $error++;
                          $error_sl_no[] = $sl_no;
                        }
                      }else {
                        echo "<br/>Sl No: ".$sl_no." - Debit Account Error";
                        $error++;
                        $error_sl_no[] = $sl_no;
                      }
                      // Calculate & Update Bill Total
                      $this->bills_management->calculate_bill_total($bill_result['insert_id']);
                    }else{
                      echo "<br/>Sl No: ".$sl_no." - Expense Bill Creation Error";
                      $error++;
                      $error_sl_no[] = $sl_no;
                    }
                  }else{
                    echo "<br/>Sl No: ".$sl_no." - Expense Bill Already Exist";
                    // Add Transaction to Existing Bill
                    if($slice[9]=='CASH' || $slice[9]==''){
                      $debit_account_head_id = 1;
                    }else{
                      $debit_account_head_id = $this->account_head_id($slice[14],36,'');
                    }
                    if($debit_account_head_id!=0){
                      $transaction_data['tr_type'] = 'expense_bill_transaction';
                      $transaction_data['reference_id'] = $bill_already_created_id;
                      $transaction_data['reference_type'] = 'expense_bill';
                      $transaction_data['tr_description'] = ucwords(str_replace('_',' ',$transaction_data['tr_type']));
                      $transaction_data['tr_reference_no'] = $slice[10];
                      $transaction_data['tr_mode'] = $slice[9];
                      if($slice[11]!=''){
                        $transaction_data['tr_reference_date'] = date('Y-m-d',strtotime($slice[11]));
                      }
                      $transaction_data['tr_remarks'] = $slice[13];
                      $transaction_data['created_date'] = date('Y-m-d H:i:s');
                      $transaction_data['created_by'] = $user_id;
                      $transaction_data['tr_credit_acc'] = $credit_account_head_id;
                      $transaction_data['tr_debit_acc'] = $debit_account_head_id;
                      $transaction_data['tr_amount'] = $slice[5];
                      $transaction_data['tr_date'] = date('Y-m-d',strtotime($slice[4]));
                      $transaction_result =  $this->Account->create_account_transaction($transaction_data);
                      if($transaction_result['status'] == 1){
                        echo "<br/>Sl No: ".$sl_no." - Transaction Added Successfully";
                        $success++;
                      }else {
                        echo "<br/>Sl No: ".$sl_no." - Transaction Adding Error";
                        $error++;
                        $error_sl_no[] = $sl_no;
                      }
                    }else {
                      echo "<br/>Sl No: ".$sl_no." - Debit Account Error";
                      $error++;
                      $error_sl_no[] = $sl_no;
                    }
                    // Calculate & Update Bill Total
                    $this->bills_management->calculate_bill_total($bill_already_created_id);
                  }

                }else{
                  echo "<br/>Sl No: ".$sl_no." - Credit Account Error";
                  $error++;
                  $error_sl_no[] = $sl_no;
                }
              }else{
                echo "<br/>Sl No: ".$sl_no." -Project Error";
                $error++;
                $error_sl_no[] = $sl_no;
              }
            }
          }elseif($data_type=="subcontract_bills"){
            if($slice[1]!='' && $slice[4]!=''){
              $project_name = $slice[1];
              $project_data = $this->Work->select_work("*", array("work_name" => $project_name));
              $user_id = 1;
              if($project_data->num_rows()==1 && $user_id !=0){
                $project_data = $project_data->row();
                $work_type_id = $this->work_type_id($slice[3]);
                $subcontract_data['sub_work_name'] = $subcontract_where_data['sub_work_name'] = $slice[2];
                $subcontract_data['contractor'] = $subcontract_where_data['contractor'] = $slice[4];
                $subcontract_data['contract_type'] = $subcontract_where_data['contract_type'] = 'fixed_rate';
                $subcontract_data['work_id'] = $subcontract_where_data['sc.work_id'] = $project_data->work_id;
                $subcontract_data['work_type_id'] = $subcontract_where_data['sc.work_type_id'] = $work_type_id;
                $subcontract_details = $this->Subcontracts->select_subcontract("sc.contract_id",$subcontract_where_data);
                if($subcontract_details->num_rows()==0){
                  $subcontract_data['description'] = $slice[16];
                  $subcontract_data['created_date'] = date('Y-m-d H:i:s');
                  $subcontract_data['created_by'] = $this->session->userdata('user_id');
                  $subcontract_result =  $this->Subcontracts->create_subcontract($subcontract_data);
                  $subcontract_result_status = $subcontract_result['status'];
                  $subcontract_id = $subcontract_result['insert_id'];

                  $work_item_data = array();
                  $work_item_data['subcontract_id'] = $subcontract_id;
                  $work_item_data['created_date'] = date('Y-m-d H:i:s');
                  $work_item_data['created_by'] = $user_id;
                  $work_item_data['work_type'] = $work_type_id;
                  $work_item_data['est_amount'] = 0;
                  $work_item_data['remarks'] = '';
                  $item_result =  $this->Subcontracts->create_subcontract_work_item($work_item_data);

                }else{
                  $subcontract_result_status = 1;
                  $subcontract_details = $subcontract_details->row();
                  $subcontract_id = $subcontract_details->contract_id;
                }

                if($subcontract_result_status == 1){

                  $credit_account_head_id = "8"; //Subcontract Payments
                  $bill_data['subcontract_id'] = $bill_data1['b.subcontract_id'] = $subcontract_id;
                  $bill_data['bill_type'] = $bill_data1['bill_type'] = 'subcontract';
                  $bill_data['work_id'] = $bill_data1['b.work_id'] = $project_data->work_id;
                  $bill_data['bill_credit_ac'] = $bill_data1['bill_credit_ac'] = $credit_account_head_id;
                  // Check if Bill Details Exist
                  if($slice[6]!='' || $slice[5]!='' || $slice[10]!='' || $slice[11]!=''){
                    $bill_already_created_id = 0;
                    $bill_data['bill_no'] = $bill_data1['bill_no'] = $slice[5];
                    if($slice[7]!='' && $slice[7]!=' ' && $slice[7]!=0){
                      $bill_data['bill_gross_amount'] = $bill_data1['bill_gross_amount'] = $slice[7];
                    }else{
                      $bill_data['bill_gross_amount'] = $bill_data1['bill_gross_amount'] = $slice[15];
                    }
                    if($slice[6]!=''){
                      $bill_data['bill_date'] = $bill_data1['bill_date'] = date('Y-m-d',strtotime($slice[6]));
                      // check if Bill Already Created
                      $bill_count = $this->Bills->select_bills("b.bill_id",$bill_data1,'subcontract_bill');
                      if($bill_count->num_rows()==1){
                        $bill_already_created_id = $bill_count->row()->bill_id;
                      }
                    }else{
                      if($slice[0]!=''){
                        $bill_data['bill_date'] = date('Y-m-d',strtotime($slice[0]));
                      }
                    }

                    if($bill_already_created_id==0){
                      // New Bill Creation
                      $bill_data['bill_tax_amount'] = $slice[8];
                      if($bill_data['bill_tax_amount']=='' || $bill_data['bill_tax_amount']==' '){
                        $bill_data['bill_tax_amount'] = 0;
                      }
                      $bill_data['bill_tds_amount'] = $slice[9];
                      if($bill_data['bill_tds_amount']=='' || $bill_data['bill_tds_amount']==' '){
                        $bill_data['bill_tds_amount'] = 0;
                      }
                      $bill_data['other_deductions'] = 0;
                      if($slice[10] !='' && $slice[11] !='' && $slice[11]<$slice[10]){
                        $bill_data['other_deductions'] = $slice[10]-$slice[11];
                      }

                      $bill_data['bill_amount'] = $bill_data['bill_gross_amount'] + $bill_data['bill_tax_amount'] - $bill_data['bill_tds_amount'] - $bill_data['other_deductions'];
                      $bill_data['bill_payable_amount'] = $bill_data['bill_amount'];
                      $bill_data['bill_consignee_name'] = $slice[4];
                      $bill_data['bill_description'] = $slice[16];
                      $bill_data['bill_remarks'] = $slice[19];
                      $bill_data['created_date'] = date('Y-m-d H:i:s');
                      $bill_data['created_by'] = $user_id;
                      $bill_result =  $this->Bills->create_bills($bill_data);
                      if ($bill_result['status'] == 1) {
                        echo "<br/>Sl No: ".$sl_no." - Bill Created Successfully";
                        if($slice[12]=='CASH' || $slice[12]=='' ){
                          $debit_account_head_id = 1;
                        }else{
                          $debit_account_head_id = $this->account_head_id($slice[17],36,'');
                        }
                        if($debit_account_head_id!=0){
                          $transaction_data['tr_type'] = 'subcontract_bill_transaction';
                          $transaction_data['reference_id'] = $bill_result['insert_id'];
                          $transaction_data['reference_type'] = 'subcontract_bill';
                          $transaction_data['tr_description'] = ucwords(str_replace('_',' ',$transaction_data['tr_type']));
                          $transaction_data['tr_reference_no'] = $slice[13];
                          $transaction_data['tr_mode'] = $slice[12];
                          if($slice[14]!=''){
                            $transaction_data['tr_reference_date'] = date('Y-m-d',strtotime($slice[14]));
                          }
                          $transaction_data['tr_remarks'] = $slice[18];
                          $transaction_data['created_date'] = date('Y-m-d H:i:s');
                          $transaction_data['created_by'] = $user_id;
                          $transaction_data['tr_credit_acc'] = $credit_account_head_id;
                          $transaction_data['tr_debit_acc'] = $debit_account_head_id;
                          $transaction_data['tr_amount'] = $slice[15];
                          $transaction_data['tr_date'] = date('Y-m-d',strtotime($slice[0]));
                          $transaction_result =  $this->Account->create_account_transaction($transaction_data);
                          if($transaction_result['status'] == 1){
                            echo "<br/>Sl No: ".$sl_no." - Transaction Created Successfully";
                            $success++;
                          }else {
                            echo "<br/>Sl No: ".$sl_no." - Transaction Creation Error";
                            $error++;
                            $error_sl_no[] = $sl_no;
                          }
                        }else {
                          echo "<br/>Sl No: ".$sl_no." - Debit Account Error";
                          $error++;
                          $error_sl_no[] = $sl_no;
                        }
                        // Calculate & Update Bill Total
                        $this->bills_management->calculate_bill_total($bill_result['insert_id']);
                      }else{
                        echo "<br/>Sl No: ".$sl_no." - Bill Creation Error";
                        $error++;
                        $error_sl_no[] = $sl_no;
                      }
                    }else{
                      echo "<br/>Sl No: ".$sl_no." - Bill Already Created";
                      // Add Payment to Existing Bill
                      if($slice[12]=='CASH' || $slice[12]=='' ){
                        $debit_account_head_id = 1;
                      }else{
                        $debit_account_head_id = $this->account_head_id($slice[17],36,'');
                      }
                      if($debit_account_head_id!=0){
                        $transaction_data['tr_type'] = 'subcontract_bill_transaction';
                        $transaction_data['reference_id'] = $bill_already_created_id;
                        $transaction_data['reference_type'] = 'subcontract_bill';
                        $transaction_data['tr_description'] = ucwords(str_replace('_',' ',$transaction_data['tr_type']));
                        $transaction_data['tr_reference_no'] = $slice[13];
                        $transaction_data['tr_mode'] = $slice[12];
                        if($slice[14]!=''){
                          $transaction_data['tr_reference_date'] = date('Y-m-d',strtotime($slice[14]));
                        }
                        $transaction_data['tr_remarks'] = $slice[18];
                        $transaction_data['created_date'] = date('Y-m-d H:i:s');
                        $transaction_data['created_by'] = $user_id;
                        $transaction_data['tr_credit_acc'] = $credit_account_head_id;
                        $transaction_data['tr_debit_acc'] = $debit_account_head_id;
                        $transaction_data['tr_amount'] = $slice[15];
                        $transaction_data['tr_date'] = date('Y-m-d',strtotime($slice[0]));
                        $transaction_result =  $this->Account->create_account_transaction($transaction_data);
                        if($transaction_result['status'] == 1){
                          echo "<br/>Sl No: ".$sl_no." - Transaction Added Successfully";
                          $success++;
                        }else {
                          echo "<br/>Sl No: ".$sl_no." - Transaction Adding Error";
                          $error++;
                          $error_sl_no[] = $sl_no;
                        }
                      }else {
                        echo "<br/>Sl No: ".$sl_no." - Debit Account Error";
                        $error++;
                        $error_sl_no[] = $sl_no;
                      }
                      // Calculate & Update Bill Total
                      $this->bills_management->calculate_bill_total($bill_already_created_id);
                    }
                  }else{
                    // Add as Advance Payment
                    if($slice[12]=='CASH' || $slice[12]=='' ){
                      $debit_account_head_id = 1;
                    }else{
                      $debit_account_head_id = $this->account_head_id($slice[17],36,'');
                    }
                    if($debit_account_head_id!=0){
                      $transaction_data['tr_type'] = 'subcontract_advance_transaction';
                      $transaction_data['reference_id'] = $subcontract_id;
                      $transaction_data['reference_type'] = 'subcontract';
                      $transaction_data['tr_description'] = ucwords(str_replace('_',' ',$transaction_data['tr_type']));
                      $transaction_data['tr_reference_no'] = $slice[13];
                      $transaction_data['tr_mode'] = $slice[12];
                      if($slice[14]!=''){
                        $transaction_data['tr_reference_date'] = date('Y-m-d',strtotime($slice[14]));
                      }
                      $transaction_data['tr_remarks'] = $slice[18];
                      $transaction_data['created_date'] = date('Y-m-d H:i:s');
                      $transaction_data['created_by'] = $user_id;
                      $transaction_data['tr_credit_acc'] = $credit_account_head_id;
                      $transaction_data['tr_debit_acc'] = $debit_account_head_id;
                      $transaction_data['tr_amount'] = $slice[15];
                      $transaction_data['tr_date'] = date('Y-m-d',strtotime($slice[0]));
                      $transaction_result =  $this->Account->create_account_transaction($transaction_data);
                      if($transaction_result['status'] == 1){
                        echo "<br/>Sl No: ".$sl_no." - Advance Transaction Added Successfully";
                        $success++;
                      }else {
                        echo "<br/>Sl No: ".$sl_no." - Advance Transaction Adding Error";
                        $error++;
                        $error_sl_no[] = $sl_no;
                      }
                    }else {
                      echo "<br/>Sl No: ".$sl_no." - Debit Account Error";
                      $error++;
                      $error_sl_no[] = $sl_no;
                    }
                  }
                }else{
                  echo "<br/>Sl No: ".$sl_no." - Subcontract Creation Error";
                  $error++;
                  $error_sl_no[] = $sl_no;
                }
              }else{
                echo "<br/>Sl No: ".$sl_no." - Project Error";
                $error++;
                $error_sl_no[] = $sl_no;
              }
            }
          }elseif($data_type=="purchase_bills"){
            if($slice[1]!='' && $slice[2]!=''){
              $project_name = $slice[1];
              $project_data = $this->Work->select_work("*", array("work_name" => $project_name));
              $user_id = 1;
              if($project_data->num_rows()==1 && $user_id !=0){
                $project_data = $project_data->row();
                $supplier_id = $this->supplier_id($slice[2]);
                $already_created_bill_id = 0;
                $purchase_bill_data['location_id'] = $purchase_bill_data1['p.location_id'] = $this->Stock->select_stock_locations("*", array("sl.work_id" =>$project_data->work_id))->row()->location_id;
                $purchase_bill_data['supplier_id'] = $purchase_bill_data1['p.supplier_id'] = $supplier_id;
                $purchase_bill_data['bill_invoice_no'] = $purchase_bill_data1['p.bill_invoice_no'] = $slice[3];
                if($slice[4]!=''){
                  $purchase_bill_data['purchase_date'] = $purchase_bill_data1['p.purchase_date'] = date('Y-m-d',strtotime($slice[4]));
                  // Check Bill Already Created
                  $purchase_bill_count = $this->Purchases->select_purchase("p.*",$purchase_bill_data1);
                  if($purchase_bill_count->num_rows()==1 && ($purchase_bill_count->row()->bill_total_amount == $slice[5] || $purchase_bill_count->row()->bill_payable_amount == $slice[8] )){
                    $already_created_bill_id = $purchase_bill_count->row()->purchase_id;
                  }
                }else{
                  if($slice[0]!=''){
                    $purchase_bill_data['purchase_date'] = date('Y-m-d',strtotime($slice[0]));
                  }
                }
                if($already_created_bill_id==0){
                  $purchase_bill_data['remarks'] = $slice[13];
                  $purchase_bill_data['vehicle_no'] = '';
                  $purchase_bill_data['created_date'] = date('Y-m-d H:i:s');
                  $purchase_bill_data['created_by'] = $user_id;
                  $purchase_bill_data['bill_discount_amount'] = 0;
                  $purchase_bill_data['bill_tax_amount'] = $slice[6];
                  $purchase_bill_data['bill_tds_amount'] = $slice[7];
                  $purchase_bill_data['bill_save_status'] = 1;
                  $purchase_result =  $this->Purchases->insert_purchase($purchase_bill_data);
                  $purchase_result_status = $purchase_result['status'];
                  $purchase_bill_id = $purchase_result['insert_id'];

                  if($purchase_result_status == 1){
                    echo "<br/>Sl No: ".$sl_no." - Bill Created Successfully";
                    if($slice[13]==''){$slice[13]='Others';}
                    $item_data['purchase_id'] = $purchase_bill_id;
                    $item_data['material_id'] = $this->material_id($slice[13]);
                    $item_data['quantity'] = 1;
                    $item_data['unit_id'] = $this->unit_id("Nos");
                    if($slice[5]!='' && $slice[5]!=' '){
                      $item_data['total_amount'] = $slice[5];
                    }else{
                      $item_data['total_amount'] = $slice[12];
                    }
                    $item_data['unit_price'] = $item_data['total_amount']/$item_data['quantity'];
                    $item_result = $this->Purchases->create_purchase_item($item_data);

                    if($slice[9]=='CASH' || $slice[9]==''){
                      $debit_account_head_id = 1;
                    }else{
                      $debit_account_head_id = $this->account_head_id($slice[14],36,'');
                    }
                    if($debit_account_head_id!=0){
                      $transaction_data['reference_id'] = $purchase_bill_id;
                      $transaction_data['reference_type'] = "purchase_bill";
                      $transaction_data['tr_type'] = "purchase_bill_transaction";
                      $transaction_data['created_date'] = date('Y-m-d H:i:s');
                      $transaction_data['created_by'] = $user_id;
                      $transaction_data['tr_payment_part'] = 1; //Bill Payment
                      $transaction_data['tr_amount'] = $slice[12];
                      $transaction_data['tr_date'] = date('Y-m-d', strtotime($slice[0]));
                      $transaction_data['tr_debit_acc'] = $debit_account_head_id;
                      $transaction_data['tr_credit_acc'] = 5;//Supplier Payments
                      $transaction_data['tr_description'] = "Purchase Bill Transaction";
                      $transaction_data['tr_mode'] = $slice[9];
                      $transaction_data['tr_reference_no'] = $slice[10];
                      if($slice[11]!=''){
                        $transaction_data['tr_reference_date'] = date('Y-m-d',strtotime($slice[11]));
                      }
                      $transaction_data['tr_remarks'] = $slice[15];
                      $transaction_result = $this->Account->create_account_transaction($transaction_data);
                      if($transaction_result['status'] == 1){
                        echo "<br/>Sl No: ".$sl_no." - Transaction Created Successfully";
                        $success++;
                      }else {
                        echo "<br/>Sl No: ".$sl_no." - Transaction Creation Error";
                        $error++;
                        $error_sl_no[] = $sl_no;
                      }
                    }else{
                      echo "<br/>Sl No: ".$sl_no." - Debit Account Error";
                    }

                    $this->bills_management->calculate_purchase_bill_total($purchase_bill_id);
                    $this->stock_management->update_stock_register($purchase_bill_id);
                  }else{
                    echo "<br/>Sl No: ".$sl_no." - Bill Createion Error";
                    $error++;
                    $error_sl_no[] = $sl_no;
                  }
                }else{
                  echo "<br/>Sl No: ".$sl_no." - Bill Already Created";
                  // Add Transaction to Existing Bill
                  if($slice[9]=='CASH' || $slice[9]==''){
                    $debit_account_head_id = 1;
                  }else{
                    $debit_account_head_id = $this->account_head_id($slice[14],36,'');
                  }
                  if($debit_account_head_id!=0){
                    $transaction_data['reference_id'] = $already_created_bill_id;
                    $transaction_data['reference_type'] = "purchase_bill";
                    $transaction_data['tr_type'] = "purchase_bill_transaction";
                    $transaction_data['created_date'] = date('Y-m-d H:i:s');
                    $transaction_data['created_by'] = $user_id;
                    $transaction_data['tr_payment_part'] = 1; //Bill Payment
                    $transaction_data['tr_amount'] = $slice[12];
                    $transaction_data['tr_date'] = date('Y-m-d', strtotime($slice[0]));
                    $transaction_data['tr_debit_acc'] = $debit_account_head_id;
                    $transaction_data['tr_credit_acc'] = 5;//Supplier Payments
                    $transaction_data['tr_description'] = "Purchase Bill Transaction";
                    $transaction_data['tr_mode'] = $slice[9];
                    $transaction_data['tr_reference_no'] = $slice[10];
                    if($slice[11]!=''){
                      $transaction_data['tr_reference_date'] = date('Y-m-d',strtotime($slice[11]));
                    }
                    $transaction_data['tr_remarks'] = $slice[15];
                    $transaction_result = $this->Account->create_account_transaction($transaction_data);
                    if($transaction_result['status'] == 1){
                      echo "<br/>Sl No: ".$sl_no." - Transaction Added Successfully";
                      $success++;
                    }else {
                      echo "<br/>Sl No: ".$sl_no." - Transaction Adding Error";
                      $error++;
                      $error_sl_no[] = $sl_no;
                    }
                  }else{
                    echo "<br/>Sl No: ".$sl_no." - Credit Account Error";
                  }

                  $this->bills_management->calculate_purchase_bill_total($already_created_bill_id);
                  $this->stock_management->update_stock_register($already_created_bill_id);
                }
              }else{
                echo "<br/>Sl No: ".$sl_no." - Project Error";
                $error++;
                $error_sl_no[] = $sl_no;
              }
            }
          }elseif($data_type=="user_payments"){
            if($slice[0]!='' && $slice[2]!=''){
              $project_name = $slice[0];
              $user_id = $this->user_id($slice[1],'supervisor');
              $project_data = $this->Work->select_work("*", array("work_name" => $project_name));
              if($project_data->num_rows()==1){
                $project_data = $project_data->row();
                if($slice[4]=='CASH' || $slice[4]==''){
                  $debit_account_head_id = 1;
                }else{
                  $debit_account_head_id = $this->account_head_id($slice[6],36,'');
                }
                if($debit_account_head_id!=0){
                  $reference_id = $user_id;
                  $transaction_data['tr_work_id'] = $project_data->work_id;
                  $reference_type = 'user_account_head';
                  $transaction_data['tr_type'] = 'user_payment_receipt_transaction';
                  $transaction_data['reference_id'] = $reference_id;
                  $transaction_data['reference_type'] = $reference_type;
                  $transaction_data['tr_description'] = ucwords(str_replace('_',' ',$transaction_data['tr_type']));
                  $transaction_data['tr_reference_no'] = $slice[7];
                  $transaction_data['tr_remarks'] = $slice[8];
                  $transaction_data['created_date'] = date('Y-m-d H:i:s');
                  $transaction_data['created_by'] = 1;
                  $transaction_data['tr_credit_acc'] = 9;//User Payments
                  $transaction_data['tr_date'] = date('Y-m-d',strtotime($slice[3]));
                  $transaction_data['tr_debit_acc'] = $debit_account_head_id;
                  $transaction_data['tr_amount'] = $slice[2];
                  $transaction_data['tr_mode'] = $slice[4];
                  $transaction_result =  $this->Account->create_account_transaction($transaction_data);

                  if($transaction_result['status'] == 1){
                    $success++;
                  }else {
                    $error++;
                    $error_sl_no[] = $sl_no;
                  }
                }else {
                  $error++;
                  $error_sl_no[] = $sl_no;
                }
              }else{
                $error++;
                $error_sl_no[] = $sl_no;
              }
            }
          }elseif($data_type=="salary_payments"){
            if($slice[1]!='' && $slice[2]!=''){
              $project_name = $slice[1];
              $employee_id = $this->employee_id($slice[2],$slice[3]);
              $project_data = $this->Work->select_work("*", array("work_name" => $project_name));
              if($project_data->num_rows()==1){
                $project_data = $project_data->row();
                // Create Salary SLip
                $slip_data['employee_id'] = $slip_data1['ss.employee_id'] = $employee_id;
                $slip_data['month'] = $slip_data1['ss.month'] = date('Y-m-d',strtotime("01-".$slice[4]));
                $count = $this->Salary_slips->select_salary_slips("",$slip_data1)->num_rows();
                $slip_data['slip_type'] = 'Project';
                if($slip_data['slip_type']=='Project'){
                  $slip_data['work_id'] = $project_data->work_id;
                }
                $slip_data['basic_wage'] = $slice[5];
                $slip_data['basic_wage_type'] = '';
                $slip_data['worked_nos'] = $slice[6];
                $slip_data['overtime'] = $slice[7];
                $slip_data['overtime_period'] = '';
                $slip_data['overtime_amount'] = $slice[8];
                $slip_data['total_basic_wage_amount'] = $slice[9];
                $slip_data['other_deductions'] = $slice[11];
                $slip_data['total_salary_amount'] = $slice[12];

                $slip_data['total_paid_amount'] = $slice[16];
                $slip_data['total_balance_amount'] = $slice[12]-$slice[16];
                $slip_data['remarks'] = $slice[17];
                $slip_data['created_date'] = date('Y-m-d H:i:s');
                $slip_data['created_by'] = $this->session->userdata('user_id');
                if($count==0) {
                  $result2 = $this->Salary_slips->create_salary_slip($slip_data);
                  if($result2['status']==1){
                    echo "<br/>Sl No: ".$sl_no." - Salary Slip Created Successfully";
                    if($slice[13]=='CASH' || $slice[13]==''){
                      $debit_account_head_id = 1;
                    }else{
                      $debit_account_head_id = $this->account_head_id($slice[18],36,'');
                    }
                    if($debit_account_head_id!=0){
                      $reference_id = $result2['insert_id'];
                      $transaction_data['tr_work_id'] = $project_data->work_id;
                      $reference_type = 'salary_slip';
                      $transaction_data['tr_type'] = 'salary_slip_transaction';
                      $transaction_data['reference_id'] = $reference_id;
                      $transaction_data['reference_type'] = $reference_type;
                      if($slice[15]!=''){
                        $transaction_data['tr_date'] = $transaction_data['tr_reference_date'] = date('Y-m-d',strtotime($slice[15]));
                      }else{
                        $transaction_data['tr_date'] = date('Y-m-d',strtotime("28-".$slice[4]));
                      }

                      $transaction_data['tr_description'] = ucwords(str_replace('_',' ',$transaction_data['tr_type']));
                      $transaction_data['tr_reference_no'] = $slice[14];
                      $transaction_data['tr_remarks'] = $slice[17];
                      $transaction_data['created_date'] = date('Y-m-d H:i:s');
                      $transaction_data['created_by'] = 1;
                      $transaction_data['tr_credit_acc'] = 10;//Salary Slip payments
                      $transaction_data['tr_debit_acc'] = $debit_account_head_id;
                      $transaction_data['tr_amount'] = $slice[16];
                      $transaction_data['tr_mode'] = $slice[13];
                      $transaction_result =  $this->Account->create_account_transaction($transaction_data);

                      if($transaction_result['status'] == 1){
                        echo "<br/>Sl No: ".$sl_no." - Transaction Created Successfully";
                        $success++;
                      }else {
                        echo "<br/>Sl No: ".$sl_no." - Transaction Creation Error";
                        $error++;
                        $error_sl_no[] = $sl_no;
                      }
                    }else {
                      echo "<br/>Sl No: ".$sl_no." - Debit Account Error";
                      $error++;
                      $error_sl_no[] = $sl_no;
                    }
                  }else{
                    echo "<br/>Sl No: ".$sl_no." - Salary SLip Creation Error";
                    $error++;
                    $error_sl_no[] = $sl_no;
                  }
                }else{
                  echo "<br/>Sl No: ".$sl_no." - Salary Slip Already Created. Payment Not Added";
                  print_r($slip_data1);

                  $error++;
                  $error_sl_no[] = $sl_no;
                }
              }else{
                echo "<br/>Sl No: ".$sl_no." - Project Error";
                $error++;
                $error_sl_no[] = $sl_no;
              }
            }
          }

          if($success>0 && $error==0){
            $flash_data['flashdata_msg'] = $success.' Data Imported Successfully!.';
            $flash_data['flashdata_type'] = 'info';
            $flash_data['alert_type'] = 'info';
            $flash_data['flashdata_title'] = 'Success !';
          }elseif($success>0 && $error>0){
            $flash_data['flashdata_msg'] = $success.' Data Imported Successfully!.<br/>'.$error.' Data Not Imported!.';
            $flash_data['flashdata_type'] = 'warning';
            $flash_data['alert_type'] = 'warning';
            $flash_data['flashdata_title'] = 'Some Error Occurred !';
          }elseif($error>0){
            $flash_data['flashdata_msg'] = $error.' Data Not Imported!.';
            $flash_data['flashdata_type'] = 'warning';
            $flash_data['alert_type'] = 'warning';
            $flash_data['flashdata_title'] = 'Some Error Occurred !';
          }else {
            $flash_data['flashdata_msg'] = 'Sorry.. There Have been Some Error Occurred. Please Try Again!';
            $flash_data['flashdata_type'] = 'error';
            $flash_data['alert_type'] = 'danger';
            $flash_data['flashdata_title'] = 'Error !!';
          }
        }
      }else {
        $flash_data['flashdata_msg'] = 'Sorry.. Some Error Occurred with File!';
        $flash_data['flashdata_type'] = 'error';
        $flash_data['alert_type'] = 'danger';
        $flash_data['flashdata_title'] = 'Error !!';
      }
    }else {
      $flash_data['flashdata_msg'] = 'Sorry.. Some Error Occurred with File!';
      $flash_data['flashdata_type'] = 'error';
      $flash_data['alert_type'] = 'danger';
      $flash_data['flashdata_title'] = 'Error !!';
    }
    $flash_data['error_sl_no'] = $error_sl_no;
    $this->session->set_flashdata($flash_data);
    echo "<br/></br/>";
    print_r($flash_data);
    // redirect(base_url(). 'data_import','refresh');
  }
  public function account_head_id($account_name,$account_type,$branch=''){
    if($account_name=='CASH'){
      $acc_head_id = 1;
    }else{
      $data['acc_head_name'] = $account_name;
      $data['account_type'] = $account_type;
      $acc_head_id = 0;
      $acc_data = $this->Account->select_account_heads("",$data);
      if($acc_data->num_rows()==0) {
        $data['head_editable'] = 1;
        $data['op_balance'] = 0;
        $data['created_date'] = date('Y-m-d H:i:s');
        $data['created_by'] = $this->session->userdata('user_id');
        $result = $this->Account->create_account_head($data);
        if ($result['status']==1) {
          $acc_head_id = $result['insert_id'];
        }
      }elseif($acc_data->num_rows()==1) {
        $acc_data = $acc_data->row();
        $acc_head_id = $acc_data->acc_head_id;
      }
    }
    return $acc_head_id;
  }
  public function work_type_id($work_type_name){
    $data['type_name'] = $work_type_name;
    if($data['type_name']=='' || $data['type_name']==' '){
      $data['type_name']='OTHERS';
    }
    $work_type_id = 0;
    $work_type_data = $this->Work->select_work_types("",$data);
    if($work_type_data->num_rows()==0) {
      $result = $this->Work->insert_work_type($data);
      if ($result['status']==1) {
        $work_type_id = $result['insert_id'];
      }
    }elseif($work_type_data->num_rows()==1) {
      $work_type_data = $work_type_data->row();
      $work_type_id = $work_type_data->type_id;
    }
    return $work_type_id;
  }
  public function supplier_id($supplier_name){
    $data['supplier_name'] = $supplier_name;
    $supplier_id = 0;
    $supplier_data = $this->Supplier->select_supplier("*",$data);
    if($supplier_data->num_rows()==0) {
      $result = $this->Supplier->create_supplier($data);
      if ($result['status']==1) {
        $supplier_id = $result['insert_id'];
      }
    }elseif($supplier_data->num_rows()==1) {
      $supplier_data = $supplier_data->row();
      $supplier_id = $supplier_data->supplier_id;
    }
    return $supplier_id;
  }
  public function material_id($material_name){
    $data['material_name'] = $material_name;
    $material_id = 0;
    $material_data = $this->Material->select_material("",$data);
    if($material_data->num_rows()==0) {
      $result = $this->Material->create_material($data);
      if ($result['status']==1) {
        $material_id = $result['insert_id'];
      }
    }elseif($material_data->num_rows()==1) {
      $material_data = $material_data->row();
      $material_id = $material_data->material_id;
    }
    return $material_id;
  }
  public function unit_id($unit_name){
    $data['unit_name'] = $unit_name;
    $unit_id = 0;
    $unit_data = $this->Material->select_unit("",$data);
    if($unit_data->num_rows()==0) {
      $result = $this->Material->create_unit($data);
      if ($result['status']==1) {
        $unit_id = $result['insert_id'];
      }
    }elseif($unit_data->num_rows()==1) {
      $unit_data = $unit_data->row();
      $unit_id = $unit_data->unit_id;
    }
    return $unit_id;
  }
  public function employee_id($employee_name,$designation){
    $data['employee_name'] = $employee_name;
    $data['designation'] = $designation;
    $emp_data = $this->Employee->select_employee("",$data);
    if($emp_data->num_rows()==0) {
      $result = $this->Employee->create_employee($data);
      if ($result['status']==1) {
        $employee_id = $result['insert_id'];
      }
    }elseif($emp_data->num_rows()==1) {
      $emp_data = $emp_data->row();
      $employee_id = $emp_data->employee_id;
    }
    return $employee_id;
  }
  public function user_id($user_name,$user_role){
    if($user_name=='OFFICE'){
      $user_id = 1;
    }else{
      $data['user_alias'] = $user_name;
      if($user_role=='supervisor'){
        $data['user_role'] = 7;
      }
      $user_id = 0;
      $user_data = $this->User->select_user("",$data);
      if($user_data->num_rows()==0) {
        $data['created_date'] = date('Y-m-d H:i:s');
        $data['password']= $this->generate_password();
        $result = $this->User->insert_user($data);
        if ($result['status']==1) {
          $user_id = $result['insert_id'];
        }
      }elseif($user_data->num_rows()==1) {
        $user_data = $user_data->row();
        $user_id = $user_data->user_id;
      }
    }

    return $user_id;
  }
  function generate_password( $length = 6 ) {
    $chars = "abcdefghjkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ23456789@#";
    $password = substr( str_shuffle( $chars ), 0, $length );
    return $password;
  }
}
?>
