application/system/adminBegin.php

<?php

namespace app\system\admin;

// use app\common\model\SystemAnnex as AnnexModel;
use League\Csv\Reader;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use app\finance\model\SystemBalanceLog as SystemBalanceLogModel;
use think\Db;
use app\product\model\Project as ProjectModel;
use app\shouyin\model\UserCardLog as UserCardLogModel;
/**
 * 期初数据导入控制器
 * @package app\system\admin
 */

class Begin extends Admin
{

    public function editAccount()
    {
        $findEditArr = Db::name('user_card')->field('id as xxid,user_id,course_id,price1')->where('account_id',-2)->select();
        $needEditArr = Db::name('user_card_detail_log')->field('id,userid,course_id,price')->select();
        $insertArr = [];  // 包含购买的 1846 detail
        $insertArr2 = []; // 包含购买的 1846 card
        foreach ($needEditArr as $key => $value) {
            foreach ($findEditArr as $k => $v) {
                if($v['user_id'] == $value['userid'] && $v['course_id'] == $value['course_id'] && $v['price1'] == $value['price']){
                    $insertArr[] = $value['id'];
                    $insertArr2[] = $v['xxid'];
                }
            }
        }

        $a1 = array_unique($insertArr2);  // 1784

        $ax = array_diff_assoc($insertArr2,$a1);   // 购买的62条
        $del = array_diff($a1,$ax); // 去除分不清购买的还是赠送的62条    1722条


        // $q1 = Db::name('user_card')->field('id as xxid,user_id,course_id,price1,sn,account_id')->where('id','in',$ax)->select();
        // foreach ($q1 as $key => $value) {
        //     $q1[$key]['num'] = Db::name('user_card_log')->where('sn',$value['sn'])->where('use',1)->count('id');
        // }
        // $ids = [];
        // foreach ($q1 as $key => $value) {
        //     $ids[] = Db::name('user_card_detail_log')
        //                 ->where('userid',$value['user_id'])
        //                 ->where('course_id',$value['course_id'])
        //                 ->where('price',$value['price1'])
        //                 ->where('use_num',$value['num'])
        //                 ->value('id');
        // }

        // $as = array_diff($ax,$a1);   // 购买的62条
        // 重复数据  包含 购买和赠送
        $newEditArr2 = Db::name('user_card')->field('id as xxid,user_id,course_id,price1')->where('id','in',$del)->select();  //购买的62条
        $ids = [];
        foreach ($newEditArr2 as $key => $value) {
            $ids[] = Db::name('user_card_detail_log')
                    ->where('userid',$value['user_id'])
                    ->where('course_id',$value['course_id'])
                    ->where('price',$value['price1'])
                    ->value('id');
        }
        $q2 = array_unique($ids);
        halt($q2);
        Db::name('user_card_detail_log')->where('id','in',$ids)->update(['type'=>4]);
        echo 'ok';

    }
    public function user_card_view()
    {
        return $this->fetch();
    }
    /**
     * [user_card_import 疗程卡导入]
     * @return [type] [description]
     */
    public function user_card_import()
    {
        $data = $this->import();
        if(!$data['code']){
            return json(['code'=>-1,'msg'=>$data['message']]);
        }
        $importArr = $data['data'];
        // halt($importArr);
        $user_card_insert = [];
        $user_card_log_insert = [];
        $user_card_detail_log_insert = [];
        // 导入数据库待完成
        foreach ($importArr as $key => $value) {
            $sn = create_order_sn();
            $user_info = Db::name('user')->where('id',$value[0])->where('nick',$value[1])->find();
            if(!$user_info){
                return json(['code'=>-1,'msg'=>$value[1].'用户不存在']);
            }
            $course_info = Db::name('course')->where('name',$value[2])->find();
            if(!$course_info){
                return json(['code'=>-1,'msg'=>$value[2].'疗程卡不存在']);
            }
            $project_info = Db::name('project')->where('name',$value[4])->find();
            if(!$project_info){
                return json(['code'=>-1,'msg'=>$value[4].'项目不存在']);
            }
            $project_arr = array_column(json_decode($course_info['course_json'],true),'project');

            if(!in_array($project_info['id'],$project_arr)){
                // halt($project_info['id']);
                return json(['code'=>-1,'msg'=>$value[2].'疗程卡不包含'.$value[4].'项目']);
            }
            $shop_id = Db::name('shop')->where('name',$value[7])->value('id');
            if(!$shop_id){
                return json(['code'=>-1,'msg'=>$value[7].'门店不存在']);
            }
            if($value[8] == '赠送'){
                $account_id = -2;
            }else{
                $account_id = 9;
            }
            // 需要插入 user_card 表的数据
            $user_card_insert[] = [
                'user_id'    => $user_info['id'],
                'course_id'  => $course_info['id'],
                'cashier_id' => 1,
                'status'     => 2,
                'finance_id' => 1,
                'sn'         => $sn,
                'shop_id'    => $shop_id,
                'account_id' => $account_id,
                'price1'     => $value[3],
                'bak'        => '期初录入',
                'createtime' => time(),
                'updatetime' => time(),
            ];
            // user_card_detail_log

            $start_num = UserCardLogModel::getUserCards($user_info['id'], $project_info['id']);
            // $price = ProjectModel::getProjectPrice($project_info['id']);
            $value[6] = empty($value[6]) ? 0 : $value[6];
            $user_card_detail_log_insert[] = [
                'userid'     => $user_info['id'],
                'cid'        => $project_info['id'],
                'price'      => $value[3],
                'start_num'  => $start_num,
                'use_num'    => $value[6],
                'end_num'    => $start_num + $value[6],
                'type'       => $value[8] == '赠送' ? 4 : 1,
                'createtime' => time(),
                'course_id'  => $course_info['id'],
                'cate'       => 1
            ];
            // user_card_log 表数据  用于实际记录
            for ($i=0; $i < $value[5]; $i++) {
                if($value[6] >= $i+1){
                    $use = 1;
                }else {
                    $use = 2;
                }
                $user_card_log_insert[] = [
                    'user_id'    => $user_info['id'],
                    'pid'        => $project_info['id'],
                    'sn'         => $sn,
                    'createtime' => time(),
                    'updatetime' => time(),
                    'type'       => 1,
                    'use'        => $use,
                ];
            }

        }
        Db::startTrans();
        try {
            Db::name('user_card')->insertAll($user_card_insert);
            Db::name('user_card_log')->insertAll($user_card_log_insert);
            Db::name('user_card_detail_log')->insertAll($user_card_detail_log_insert);
            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
            return json(['code'=>-1,'msg'=>$e->getMessage()]);
        }

        return json(['code'=>0,'msg'=>'录入成功']);
    }

    public function bank_balance_view()
    {
        return $this->fetch();
    }
    /**
     * [bank_balance_import 银行余额导入]
     * @return [json] [description]
     *
     */
    public function bank_balance_import()
    {
        $data = $this->import();
        if(!$data['code']){
            return json(['code'=>-1,'msg'=>$data['message']]);
        }
        $importArr = $data['data'];
        // 导入数据库待完成
        $bank_balance_insert = [];
        $sn = create_order_sn();
        foreach ($importArr as $key => $value) {
            $bank_id = Db::name('account')->where('name',$value[0])->value('id');
            if(!$bank_id){
                return json(['code'=>-1,'msg'=>$value[0].'账户不存在']);
            }
            $bank_balance_insert[] = [
                'sn'               => $sn,
                'type'             => 0,
                'account_id'       => $bank_id,
                'price'            => $value[1],
                'createtime'       => time(),
                'updatetime'       => time(),
                'cate'             => 1,
                'bak'              => '期初录入'
            ];
        }
        Db::startTrans();
        try {
            SystemBalanceLogModel::insertAll($bank_balance_insert);
            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
            return json(['code'=>-1,'msg'=>$e->getMessage()]);
        }

        return json(['code'=>0,'msg'=>'录入成功']);
    }


    public function supplier_balance_view()
    {
        return $this->fetch();
    }
    /**
     * [supplier_balance_import 供应商期初导入]
     * @return [type] [description]
     */
    public function supplier_balance_import()
    {
        $data = $this->import();
        if(!$data['code']){
            return json(['code'=>-1,'msg'=>$data['message']]);
        }
        $importArr = $data['data'];
        // 组装数据插入数据库
        $supplier_balance_insert = [];
        $sn = create_order_sn();
        foreach ($importArr as $key => $value) {
            $supplier_id = Db::name('supplier')->where('name',$value[0])->value('id');
            if(!$supplier_id){
                return json(['code'=>-1,'msg'=>$value[0].'供应商不存在']);
            }
            $supplier_balance_insert[] = [
                'account_id'       => 9,
                'supplier_id'      => $supplier_id,
                'price'            => $value[1],
                'createtime'       => date('Y-m-d H:i:s',time()),
                'out_in'           => 1,
                'sn'               => $sn,
                'bak'              => '期初录入'
            ];

        }
        Db::startTrans();
        try {
            Db::name('supplier_money')->insertAll($supplier_balance_insert);
            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
            return json(['code'=>-1,'msg'=>$e->getMessage()]);
        }

        return json(['code'=>0,'msg'=>'录入成功']);
    }
    public function stock_view()
    {
        return $this->fetch();
    }
    /**
     * [stock_import 库存期初导入]
     * @return [type] [description]
     */
    public function stock_import()
    {
        $data = $this->import();
        if(!$data['code']){
            return json(['code'=>-1,'msg'=>$data['message']]);
        }
        $importArr = $data['data'];
        // halt($importArr);
        // 组装数据插入数据库
        $stock_insert = [];
        $sn = create_order_sn();
        foreach ($importArr as $key => $value) {

            $product_info = Db::name('product')->where('name',$value[3])->find();
            if(!$product_info){
                return json(['code'=>-1,'msg'=>$value[3].'产品不存在']);
            }
            $shop_id = Db::name('shop')->where('name',$value[0])->value('id');
            if(!$shop_id){
                return json(['code'=>-1,'msg'=>$value[0].'门店不存在']);
            }
            $warehouse_id = Db::name('warehouse')->where('name',$value[1])->value('id');
            if(!$warehouse_id){
                return json(['code'=>-1,'msg'=>$value[1].'仓库不存在']);
            }
            $stock_insert[] = [
                'product_id'       => $product_info['id'],
                'product_num'      => $value[4],
                'product_name'     => $value[3],
                'type'             => '入库',
                'detailed_type'    => 9,
                'price'            => $value[5] * $value[4],
                'unit_price'       => $product_info['price1'],        //   成本价
                'begin_num'        => 0,
                'end_num'          => $value[4],
                'createtime'       => time(),
                'updatetime'       => time(),
                'begin_price'      => 0,
                'end_price'        => $value[5] * $value[4],
                'sn'               => $sn,
                'warehouse_id'     => $warehouse_id,
                'shop_id'          => $shop_id,
                'bak'              => '期初录入'
            ];

        }
        Db::startTrans();
        try {
            Db::name('stock')->insertAll($stock_insert);
            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
            return json(['code'=>-1,'msg'=>$e->getMessage()]);
        }

        return json(['code'=>0,'msg'=>'录入成功']);
    }
    public function deposit_view()
    {
        return $this->fetch();
    }
    /**
     * [deposit_import 寄存期初录入]
     * @return [type] [description]
     */
    public function deposit_import()
    {
        $data = $this->import();
        if(!$data['code']){
            return json(['code'=>-1,'msg'=>$data['message']]);
        }
        $importArr = $data['data'];
        // halt($importArr);
        // 组装数据插入数据库
        $deposit_insert = [];
        $sn = create_order_sn();
        foreach ($importArr as $key => $value) {
            $user_info = Db::name('user')->where('id',$value[0])->where('nick',$value[1])->find();
            if(!$user_info){
                return json(['code'=>-1,'msg'=>$value[1].'用户不存在']);
            }
            $product_info = Db::name('product')->where('name',$value[2])->find();
            if(!$product_info){
                return json(['code'=>-1,'msg'=>$value[2].'产品不存在']);
            }
            $shop_id = Db::name('shop')->where('name',$value[5])->value('id');
            if(!$shop_id){
                return json(['code'=>-1,'msg'=>$value[5].'门店不存在']);
            }
            $warehouse_id = Db::name('warehouse')->where('name',$value[7])->value('id');
            if(!$warehouse_id){
                return json(['code'=>-1,'msg'=>$value[7].'仓库不存在']);
            }
            $deposit_insert[] = [
                'user_id'          => $value[0],
                'product_id'       => $product_info['id'],
                'num'              => $value[3],
                'price'            => $value[4],
                'unit_price'       => $product_info['price1'],  ///  成本价
                'admin_id'         => ADMIN_ID,
                'address'          => $value[6],
                'createtime'       => time(),
                'updatetime'       => time(),
                'sn'               => $sn,
                'warehouse_id'     => $warehouse_id,
                'shop_id'          => $shop_id,
                'bak'              => !empty($value[8]) ? $value[8] : '期初录入'
            ];

        }
        Db::startTrans();
        try {
            Db::name('deposit')->insertAll($deposit_insert);
            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
            return json(['code'=>-1,'msg'=>$e->getMessage()]);
        }

        return json(['code'=>0,'msg'=>'录入成功']);
    }
    public function import()
    {
        $model = new \app\common\model\SystemAnnex;
        $uploadFile = $model::fileUpload();
        if($uploadFile['code'] != 1){
            return array('message'=>$uploadFile['msg'],'code'=>false);
        }
        $filePath = $uploadFile['data']['file'];

        $xlsx = new Xlsx;
        $spreadsheet = $xlsx->load('.'. $filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $res = array();

        foreach ($sheet->getRowIterator(2) as $row) {
            $tmp = array();
            foreach ($row->getCellIterator() as $cell) {

                $tmp[] = $cell->getFormattedValue();
            }

            $res[$row->getRowIndex()] = $tmp;
        }
        // halt($res);
        return array('message'=>'ok','code'=>true,'data'=>$res);
        // return $this->axiosMsg('ok',true, $res);
    }

}