BBnepal-Accounts/application/libraries/Accounting copy.php

<?php
class Accounting
{
    private $ci;
    private $db;

    public function __construct()
    {
        $this->ci = &get_instance();
        $this->db = $this->ci->load->database('accounting', TRUE);
        $t = "select * from admin_menu where parent_id=22 and page_link='account/defaults'";
        $r = $this->ci->db->query($t);
        if ($r->num_rows() == 0) {
            $t = "INSERT INTO `admin_menu` (`id`, `menu_name`, `parent_id`, `order_number`, `inactive_icon`, `active_icon`, `is_active`, `page_link`, `created_on`) VALUES (NULL, 'Accounting Settings', '22', '6', ' ', ' ', 'yes', 'account/defaults', '" . date("y-m-d h:i:s") . "');";
            $q = $this->ci->db->query($t);
        }
    }
    function pre($Obj)
    {
        echo "<pre>";
        print_r($Obj);
        echo "</pre>";
    }

    function init()
    {
        $tbl_acgroups = "CREATE TABLE IF NOT EXISTS `tbl_acgroups` (
            `acgroup_id` INT(11) NOT NULL,
            `acgroup_code` VARCHAR(250) NOT NULL,
            `acgroup_name` VARCHAR(250) NOT NULL,
            `posting_side` VARCHAR(20) NOT NULL,
            `created_on` DATE NOT NULL,
            `created_by` VARCHAR(250) NOT NULL,
            `remarks` TEXT NOT NULL,
            `status` INT(11) NOT NULL,
            `display_order` INT(11) NOT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
          ";
        $this->db->query($tbl_acgroups);
    }

    function showFeeTypeMappingOption($default = "")
    {
        // $Accategoreies = $this->db->query("select * from tbl_accategories where accategory_id in (select accategory_id from tbl_accounts where tbl_accategories.acgroup_id = 3 AND tbl_accounts.status<>1)")->result();
        // $Accategoreies = $this->db->query("select * from tbl_accategories where accategory_id in (select accategory_id from tbl_accounts where tbl_accategories.acgroup_id = 3 AND tbl_accounts.status<>1)")->result();
        $Accategoreies = $this->db->query("select * from tbl_accategories where accategory_id in (select accategory_id from tbl_accounts where tbl_accategories.acgroup_id = 3)")->result();

        // $Accategoreies = $this->db->query("select ac.* from tbl_accategories ac  JOIN tbl_accounts acc ON acc.accategory_id = ac.accategory_id WHERE ac.acgroup_id = 3")->result();
        foreach ($Accategoreies as $AccountCategory) {
            $AccountCategory->Accounts = $this->db->query("select * from tbl_accounts where accategory_id='$AccountCategory->accategory_id'")->result();
        }
        // echo '<pre>';
        // print_r($Accategoreies);
        // die();
?>
        <div class="form-group mt-4 mb-0">
            <label class="has-float-label">
                <select class="form-control" name="account_id" id="account_id_selector" required>
                    <option value="">Select Default Account</option>
                    <?php foreach ($Accategoreies as $AccountCategory) : ?>
                        <optgroup label="<?php echo $AccountCategory->accategory_name; ?>">
                            <?php foreach ($AccountCategory->Accounts as $Account) : ?>
                                <option value="<?php echo $Account->account_id; ?>" <?php if ($default != "" && $default == $Account->account_name) : ?>SELECTED<?php endif; ?>><?php echo $Account->account_name; ?></option>
                            <?php endforeach; ?>
                        </optgroup>
                    <?php endforeach; ?>

                </select>
                <span>Select Default Account</span>
            </label>
        </div>
    <?php

    }

    function updateFeeTypeAccountMapping($feetype_id, $account_id)
    {
        $t = "CREATE TABLE IF NOT EXISTS `tbl_accmaping` (
            `accmaping_id` int(11) NOT NULL,
            `bbmodel` varchar(35) NOT NULL,
            `bbpk` int(11) NOT NULL,
            `account_id` int(11) NOT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
          ";
        $this->db->query($t);
        $q = $this->db->query("SELECT * FROM tbl_accmaping WHERE bbmodel='fee_types' AND bbpk='$feetype_id'");
        if ($q->num_rows() > 0) {
            $q = $this->db->query("UPDATE tbl_accmaping SET account_id='$account_id' WHERE bbmodel='fee_types' AND bbpk='$feetype_id'");
        } else {
            $q = $this->db->query("INSERT INTO tbl_accmaping (account_id, bbmodel, bbpk) VALUES ('$account_id', 'fee_types', '$feetype_id')");
        }
    }
    function fetchMappedAccount($model, $bbpk)
    {
        $q = $this->db->query("SELECT * FROM tbl_accounts WHERE account_id=(SELECT account_id FROM tbl_accmaping WHERE bbmodel='$model' AND bbpk='$bbpk')");

        if ($q->num_rows() > 0) {
            return $q->row()->account_name;
        } else {
            return null;
        }
    }


    function showExpenseTypeMappingOption($default = "")
    {
        // $Accategoreies = $this->db->query("select * from tbl_accategories where accategory_id in (select accategory_id from tbl_accounts where tbl_accategories.acgroup_id = 3 AND tbl_accounts.status<>1)")->result();
        // $Accategoreies = $this->db->query("select * from tbl_accategories where accategory_id in (select accategory_id from tbl_accounts where tbl_accategories.acgroup_id = 3 AND tbl_accounts.status<>1)")->result();
        $Accategoreies = $this->db->query("select * from tbl_accategories where accategory_id in (select accategory_id from tbl_accounts where tbl_accategories.acgroup_id = 4)")->result();

        // $Accategoreies = $this->db->query("select ac.* from tbl_accategories ac  JOIN tbl_accounts acc ON acc.accategory_id = ac.accategory_id WHERE ac.acgroup_id = 3")->result();
        foreach ($Accategoreies as $AccountCategory) {
            $AccountCategory->Accounts = $this->db->query("select * from tbl_accounts where accategory_id='$AccountCategory->accategory_id'")->result();
        }
        // echo '<pre>';
        // print_r($Accategoreies);
        // die();
    ?>
        <div class="form-group mt-4 mb-0">
            <label class="has-float-label">
                <select class="form-control" name="account_id" id="account_id_selector" required>
                    <option value="">Select Default Account</option>
                    <?php foreach ($Accategoreies as $AccountCategory) : ?>
                        <optgroup label="<?php echo $AccountCategory->accategory_name; ?>">
                            <?php foreach ($AccountCategory->Accounts as $Account) : ?>
                                <option value="<?php echo $Account->account_id; ?>" <?php if ($default != "" && $default == $Account->account_name) : ?>SELECTED<?php endif; ?>><?php echo $Account->account_name; ?></option>
                            <?php endforeach; ?>
                        </optgroup>
                    <?php endforeach; ?>

                </select>
                <span>Select Default Account</span>
            </label>
        </div>
<?php
    }

    function updateExpenseTypeAccountMapping($feetype_id, $account_id)
    {
        $t = "CREATE TABLE IF NOT EXISTS `tbl_accmaping` (
            `accmaping_id` int(11) NOT NULL,
            `bbmodel` varchar(35) NOT NULL,
            `bbpk` int(11) NOT NULL,
            `account_id` int(11) NOT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
          ";
        $this->db->query($t);
        $q = $this->db->query("SELECT * FROM tbl_accmaping WHERE bbmodel='expense_types' AND bbpk='$feetype_id'");
        if ($q->num_rows() > 0) {
            $q = $this->db->query("UPDATE tbl_accmaping SET account_id='$account_id' WHERE bbmodel='expense_types' AND bbpk='$feetype_id'");
        } else {
            $q = $this->db->query("INSERT INTO tbl_accmaping (account_id, bbmodel, bbpk) VALUES ('$account_id', 'expense_types', '$feetype_id')");
        }
    }


    function userCreateAcc($userId, $userType, $username, $createdOn)
    {
        $userAcc = $this->db->query("select * from tbl_accounts where account_code = '$userId'")->result();
        print_r($userAcc);

        if (empty($userAcc)) {
            if ($userType == 'student') {
                $accategory_id = 64;
            } else {
                $accategory_id = 65;
            }
            $td['account_code'] = $userId;
            $td['account_name'] = $username;
            $td['accategory_id'] = $accategory_id;
            $td['created_on'] = $createdOn;
            $td['created_by'] = 'Admin';
            $td['status'] = 1;

            // dd($td);

            $insert = $this->db->insert('tbl_accounts', $td);

            if ($insert) {
                return 'success';
            } else {
                return 'error';
            }
        } else {
            return 'already_existed';
        }
        // return $userId . ' = ' . $userType;
    }

    function fetchAccountInfo($userId)
    {
        $userAcc = $this->db->query("select * from tbl_accounts where account_code = '$userId'")->row_array();

        return $userAcc;
    }

    function createVoucherId($userId, $course_fee_id, $userType)
    {
        $userAcc = $this->db->query("select * from tbl_vouchers where user_id = $userId AND course_fee_id = $course_fee_id")->row_array();

        if (empty($userAcc)) {

            $voucher = $this->db->query("select * from tbl_vouchers")->result();
            $voucher_type = $this->db->query("select * from tbl_vouchers WHERE  voucher_type = 'Journal'")->result();
            $voucherType = $this->db->query("select * from tbl_vouchertypes WHERE  voucher_type = 'Journal'")->row_array();
            $fiscalyear = $this->db->query("select * from tbl_fiscalyear WHERE  status = 1")->row_array();
            $branches = $this->db->query("select * from tbl_branches WHERE  status = 1")->row_array();


            $td['voucher_no'] = count($voucher) + 1;
            $td['voucher_ref'] = $voucherType['voucher_alias'] . '' . (count($voucher_type) + 1);
            $td['voucher_date'] = date('Y-m-d');
            $td['voucher_state'] = 'Entered';
            $td['voucher_type'] = 'Journal';
            $td['fiscalyear_id'] = $fiscalyear['fiscalyear_id'];
            $td['branch_id'] = $branches['branch_id'];
            $td['user_id'] = $userId;
            $td['course_fee_id'] = $course_fee_id;
            $td['reference_type'] = $userType;
            $td['created_by'] =  $_SESSION['admin_name'];
            $td['created_on'] = date('Y-m-d H:m:s');
            $td['remarks'] = '';
            $td['status'] = 1;

            // dd($td);
            // echo '<pre>';
            // print_r($td);

            $insert = $this->db->insert('tbl_vouchers', $td);

            if ($insert) {
                return $this->db->insert_id();
            } else {
                return 0;
            }
        } else {
            return $userAcc['voucher_id'];
        }
    }

    function fetchVoucherInfo($userId)
    {
        $userAcc = $this->db->query("select * from tbl_vouchers where reference_id = $userId")->row_array();

        return $userAcc;
    }

    // function voucherEntry($userId, $theUserId, $course_fee, $scholarship)
    // {
    //     $userAcc = $this->db->query("select * from tbl_vouchers where reference_id = $userId")->result();

    //     if (!empty($userAcc)) {

    //         $voucher = $this->db->query("select * from tbl_vouchers WHERE reference_id = $userId AND reference_type = 'student'")->row_array();
    // $fiscalyear = $this->db->query("select * from tbl_fiscalyear WHERE  status = 1")->row_array();
    // $branches = $this->db->query("select * from tbl_branches WHERE  status = 1")->row_array();

    // $entry = !empty($scholarship) ? 3 : 2;

    // $fee_amt = $course_fee['amount'];
    // $dr = 0;
    // $cr = 0;

    // for ($i = 0; $i < $entry; $i++) {

    //     if ($entry == 3) {
    //         if ($i == 0) {
    //             $account = $this->db->query("select * from tbl_accounts WHERE account_name = '" . $course_fee['feetype_name'] . "' AND status = 1")->row_array();
    //             $dr = $fee_amt;
    //             $cr = 0;
    //         } else if ($i == 1) {
    //             $account = $this->db->query("select * from tbl_accounts WHERE account_name = 'Scholarship' AND status = 1")->row_array();
    //             $scholarship_amt = round(($scholarship['scholarship_discount'] / 100) * $fee_amt);
    //             $cr = $scholarship_amt;
    //             $dr = 0;
    //         } else {
    //             $account = $this->db->query("select * from tbl_accounts WHERE account_code = '$theUserId' AND status = 1")->row_array();
    //             $scholarship_amt = round(($scholarship['scholarship_discount'] / 100) * $fee_amt);
    //             $dr = 0;
    //             $cr = $fee_amt - $scholarship_amt;
    //         }
    //     } else {
    //         if ($i == 0) {
    //             $account = $this->db->query("select * from tbl_accounts WHERE account_name = '" . $course_fee['feetype_name'] . "' AND status = 1")->row_array();
    //             $dr = $fee_amt;
    //             $cr = 0;
    //         } else {
    //             $account = $this->db->query("select * from tbl_accounts WHERE account_code = '$theUserId' AND status = 1")->row_array();
    //             $cr = $fee_amt;
    //             $dr = 0;
    //         }
    //     }

    //     $td['voucher_id'] = $voucher['voucher_id'];
    //     $td['entry_no'] = $i + 1;
    //     $td['transaction_date'] = date('Y-m-d');
    //     $td['account_id'] = $account['account_id'];
    //     $td['narration'] = $course_fee['fee_values'] . ' ( ' . $course_fee['feetype_name'] . ' )';
    //     $td['dr'] = $dr;
    //     $td['cr'] = $cr;
    //     $td['branch_id'] = $branches['branch_id'];
    //     $td['fiscalyear_id'] = $fiscalyear['fiscalyear_id'];
    //     $td['batch_id'] =  $course_fee['batch_id'];
    //     $td['course_id'] = $course_fee['course_id'];
    //     $td['course_fee_id'] = $course_fee['id'];
    //     $td['student_id'] = $userId;
    //     $td['created_on'] =  date('Y-m-d');
    //     $td['created_by'] =  $_SESSION['admin_name'];
    //     $td['remarks'] = !empty($scholarship) ? $scholarship['scholarship_discount'] . ' % scholarship on ' . $course_fee['feetype_name'] : '';
    //     $td['status'] = 1;

    //     // echo '<pre>';
    //     // print_r($td);

    //     $this->db->insert('tbl_voucherdetails', $td);
    //         }
    //     } else {

    //         $voucher = $this->db->query("select * from tbl_vouchers WHERE reference_id = $userId AND reference_type = 'student'")->row_array();


    //         $this->db->select_max('voucher_id');
    //         $query  = $this->db->get('tbl_vouchers');
    //         $res = $query->row_array();
    //         $vid = $res['id'] + 1;

    //         $fiscalyear = $this->db->query("select * from tbl_fiscalyear WHERE  status = 1")->row_array();
    //         $branches = $this->db->query("select * from tbl_branches WHERE  status = 1")->row_array();

    //         $entry = !empty($scholarship) ? 3 : 2;

    //         $fee_amt = $course_fee['amount'];
    //         $dr = 0;
    //         $cr = 0;

    //         for (
    //             $i = 0;
    //             $i < $entry;
    //             $i++
    //         ) {

    //             if ($entry == 3) {
    //                 if ($i == 0) {
    //                     $account = $this->db->query("select * from tbl_accounts WHERE account_name = '" . $course_fee['feetype_name'] . "' AND status = 1")->row_array();
    //                     $dr = $fee_amt;
    //                     $cr = 0;
    //                 } else if ($i == 1) {
    //                     $account = $this->db->query("select * from tbl_accounts WHERE account_name = 'Scholarship' AND status = 1")->row_array();
    //                     $scholarship_amt = round(($scholarship['scholarship_discount'] / 100) * $fee_amt);
    //                     $cr = $scholarship_amt;
    //                     $dr = 0;
    //                 } else {
    //                     $account = $this->db->query("select * from tbl_accounts WHERE account_code = '$theUserId' AND status = 1")->row_array();
    //                     $scholarship_amt = round(($scholarship['scholarship_discount'] / 100) * $fee_amt);
    //                     $dr = 0;
    //                     $cr = $fee_amt - $scholarship_amt;
    //                 }
    //             } else {
    //                 if ($i == 0) {
    //                     $account = $this->db->query("select * from tbl_accounts WHERE account_name = '" . $course_fee['feetype_name'] . "' AND status = 1")->row_array();
    //                     $dr = $fee_amt;
    //                     $cr = 0;
    //                 } else {
    //                     $account = $this->db->query("select * from tbl_accounts WHERE account_code = '$theUserId' AND status = 1")->row_array();
    //                     $cr = $fee_amt;
    //                     $dr = 0;
    //                 }
    //             }

    //             $td['voucher_id'] = $vid;
    //             $td['entry_no'] = $i + 1;
    //             $td['transaction_date'] = date('Y-m-d');
    //             $td['account_id'] = $account['account_id'];
    //             $td['narration'] = $course_fee['fee_values'] . ' ( ' . $course_fee['feetype_name'] . ' )';
    //             $td['dr'] = $dr;
    //             $td['cr'] = $cr;
    //             $td['branch_id'] = $branches['branch_id'];
    //             $td['fiscalyear_id'] = $fiscalyear['fiscalyear_id'];
    //             $td['batch_id'] = $course_fee['batch_id'];
    //             $td['course_id'] = $course_fee['course_id'];
    //             $td['course_fee_id'] = $course_fee['id'];
    //             $td['student_id'] = $userId;
    //             $td['created_on'] = date('Y-m-d');
    //             $td['created_by'] = $_SESSION['admin_name'];
    //             $td['remarks'] = !empty($scholarship) ? $scholarship['scholarship_discount'] . ' % scholarship on ' . $course_fee['feetype_name'] : '';
    //             $td['status'] = 1;

    //             // echo '<pre>';
    //             $this->db->insert('tbl_voucherdetails', $td);
    //             // dd($td);
    //         }
    //     }
    //     // return $userId . ' = ' . $userType;
    // }

    function voucherEntry($userId, $theUserId, $course_fee, $scholarship)
    {

        $course_fee_id = $course_fee['id'];
        $voucher = $this->db->query("select * from tbl_vouchers where user_id = $userId AND course_fee_id = $course_fee_id")->result();



        $fiscalyear = $this->db->query("select * from tbl_fiscalyear WHERE  status = 1")->row_array();
        $branches = $this->db->query("select * from tbl_branches WHERE  status = 1")->row_array();

        $entry = !empty($scholarship) ? 3 : 2;

        $fee_amt = $course_fee['amount'];
        $dr = 0;
        $cr = 0;

        for ($i = 0; $i < $entry; $i++) {

            if ($entry == 3) {
                if ($i == 0) {
                    $account = $this->db->query("select * from tbl_accounts WHERE account_name = '" . $course_fee['feetype_name'] . "' AND status = 1")->row_array();
                    $dr = $fee_amt;
                    $cr = 0;
                } else if ($i == 1) {
                    $account = $this->db->query("select * from tbl_accounts WHERE account_name = 'Scholarship' AND status = 1")->row_array();
                    $scholarship_amt = round(($scholarship['scholarship_discount'] / 100) * $fee_amt);
                    $cr = $scholarship_amt;
                    $dr = 0;
                } else {
                    $account = $this->db->query("select * from tbl_accounts WHERE account_code = '$theUserId' AND status = 1")->row_array();
                    $scholarship_amt = round(($scholarship['scholarship_discount'] / 100) * $fee_amt);
                    $dr = 0;
                    $cr = $fee_amt - $scholarship_amt;
                }
            } else {
                if ($i == 0) {
                    $account = $this->db->query("select * from tbl_accounts WHERE account_name = '" . $course_fee['feetype_name'] . "' AND status = 1")->row_array();
                    $dr = $fee_amt;
                    $cr = 0;
                } else {
                    $account = $this->db->query("select * from tbl_accounts WHERE account_code = '$theUserId' AND status = 1")->row_array();
                    $cr = $fee_amt;
                    $dr = 0;
                }
            }

            $voucherId = $this->createVoucherId($userId, $course_fee['id'], "student",);

            $td['voucher_id'] = $voucherId;
            $td['entry_no'] = $i + 1;
            $td['transaction_date'] = date('Y-m-d');
            $td['account_id'] = $account['account_id'];
            $td['narration'] = $course_fee['fee_values'] . ' ( ' . $course_fee['feetype_name'] . ' )';
            $td['dr'] = $dr;
            $td['cr'] = $cr;
            $td['branch_id'] = $branches['branch_id'];
            $td['fiscalyear_id'] = $fiscalyear['fiscalyear_id'];
            $td['batch_id'] = $course_fee['batch_id'];
            $td['course_id'] = $course_fee['course_id'];
            $td['course_fee_id'] = $course_fee['id'];
            $td['student_id'] = $userId;
            $td['created_on'] = date('Y-m-d');
            $td['created_by'] = $_SESSION['admin_name'];
            $td['remarks'] = !empty($scholarship) ? $scholarship['scholarship_discount'] . ' % scholarship on ' . $course_fee['feetype_name'] : '';
            $td['status'] = 1;

            // echo '<pre>';
            // print_r($td);

            $this->db->insert('tbl_voucherdetails', $td);
        }
    }

    function removeVoucherEntry($userId, $course_fee)
    {
        $coondition_data = array(
            'student_id' => $userId,
            'course_fee_id' => $course_fee['id'],
            'batch_id' => $course_fee['batch_id']
        );
        $this->db->where($coondition_data);
        $this->db->delete('tbl_voucherdetails');
    }
}