seaBlueApi/Controller/Common/Sql.Class.php

<?php
/**
 * 测试sql
 * Created by PhpStorm.
 * User=> 小威
 * Date=> 2020/05/16
 * Time=> 11=>31
 */

namespace Jindouyun\Controller\Common;

use Mall\Framework\Core\ErrorCode;
use Mall\Framework\Core\ResultWrapper;
use Mall\Framework\Core\StatusCode;
use Mall\Framework\Factory;

use JinDouYun\Controller\BaseController;

use JinDouYun\Model\Common\MSql;

class Sql extends BaseController
{
    private $objMSql;

    public function __construct()
    {
        parent::__construct(false,false,false,false);
        $this->objMSql = new MSql();
    }

    public function batchRestoreSql()
    {
        $result = $this->objMSql->batchRestoreSql();
        if($result->isSuccess()){
            $this->sendOutput($result->getData(), $result->getErrorCode());
        }
        $this->sendOutput($result->getData());
    }

    /**
     * 批量执行
     */
    public function autoBatchSql()
    {
        $params = $this->request->getRawJson();



        $sql = "
   
ALTER TABLE `www.qianniao.vip`.`qianniao_member_balance_detail_1` ADD COLUMN `accountId` int(10) NULL COMMENT '账户id' AFTER `orderIds`,ADD COLUMN `accountNumber` varchar(255) NULL COMMENT '账号' AFTER `accountId`,ADD COLUMN `accountName` varchar(255) NULL COMMENT '账号名称' AFTER `accountNumber`;

ALTER TABLE `finance.qianniao.vip`.`qianniao_customer_balance_detail_1` CHANGE COLUMN `no` `sourceNo` char(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '来源编号' AFTER `receiptTime`,CHANGE COLUMN `sourceNo` `originNo` char(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '原始单据编号' AFTER `financeTypeId`,CHANGE COLUMN `sourceId` `originId` int(10) NULL DEFAULT 0 COMMENT '原始单据id' AFTER `originNo`,ADD COLUMN `sourceId` int(10) NULL DEFAULT 0 COMMENT '来源id' AFTER `receiptTime`

";

        if(empty($sql)){
            if(!isset($params['sql']) || empty($params['sql'])){
                parent::sendOutput('请输入要执行的sql语句', ErrorCode::$paramError);
            }
            $sql = $params['sql'];
        }

        $array = explode(';',$sql);
        $i = 0;
        $data = [];
        foreach($array as $key => $value){
            $value = trim($value);
            if(!empty($value)){
                $result = $this->objMSql->batchSql(trim($value));
                $i++;
//                $data[$i] = [
//                    'sql' => $value,
//                    'message' => $result->getData()
//                ];
            }
        }
        $return['title'] = '共执行'.$i.'条语句';
        $return['data'] = $data;
        parent::sendOutput($return);
    }





    /**
     * 批量删除某个企业的表
     */
    public function dropEnterpriseTable()
    {
        $date = [];
        for ($i = 2; $i <= 100; $i++) {
            $date[] = $i;
        }
        $sql = 'select id from `www.qianniao.vip`.`qianniao_enterprise_1`';
        $dbResult = $this->objDGoods->query($sql);
        $aa = [];
        foreach ($dbResult as $value) {
            $aa[] = $value['id'];
        }
        $dbResult = array_diff($date, $aa);
//        V($dbResult);
//        $sql = 'select id from `www.qianniao.vip`.`qianniao_enterprise_1` where expireTime <= '.time();
//        $dbResult = $this->objDGoods->query($sql);

        if (empty($dbResult)) {
            exit('没有过期企业');
        }
        $i = 0;
        foreach ($dbResult as $value) {
            $enterpriseId = $value;
//            $enterpriseId = $value['id'];
            $sql = 'DROP TABLE `www.qianniao.vip`.`qianniao_activity_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_activity_goods_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_cart_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_commission_businessman_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_coupon_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_customer_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_customer_contact_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_customer_shipping_address_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_department_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_department_role_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_goods_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_goods_basic_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_goods_brand_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_goods_category_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_goods_collect_' . $enterpriseId . '_1`, `www.qianniao.vip`.`qianniao_material_content_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_message_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_order_' . $enterpriseId . '_1`, `www.qianniao.vip`.`qianniao_order_coupon_' . $enterpriseId . '_1`, `www.qianniao.vip`.`qianniao_order_goods_' . $enterpriseId . '_1`, `www.qianniao.vip`.`qianniao_order_index_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_order_receive_' . $enterpriseId . '_1`, `www.qianniao.vip`.`qianniao_order_return_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_order_return_details_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_order_statistics_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_page_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_role_acl_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_sku_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_spec_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_staff_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_units_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_user_coupon_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_vip_card_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_vip_card_order_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_allocate_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_allocate_details_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_inventory_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_inventory_batch_' . $enterpriseId . '_1`, `stock.qianniao.vip`.`qianniao_inventory_details_' . $enterpriseId . '_1_203`, `stock.qianniao.vip`.`qianniao_inventory_in_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_inventory_in_details_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_inventory_locking_' . $enterpriseId . '_203`, `stock.qianniao.vip`.`qianniao_inventory_out_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_inventory_out_details_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_purchase_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_purchase_goods_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_purchase_out_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_purchase_out_goods_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_stocktaking_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_stocktaking_details_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_supplier_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_warehouse_' . $enterpriseId . '`, `stock.qianniao.vip`.`qianniao_warehouse_beginning_' . $enterpriseId . '`, `price.qianniao.vip`.`qianniao_customerPriceAdjustmentSheet_' . $enterpriseId . '_2020_3`, `price.qianniao.vip`.`qianniao_customerPriceEffective_' . $enterpriseId . '`, `price.qianniao.vip`.`qianniao_customerTypePriceAdjustmentSheet_' . $enterpriseId . '_2020_3`, `price.qianniao.vip`.`qianniao_customerTypePriceEffective_' . $enterpriseId . '`, `price.qianniao.vip`.`qianniao_lastEffectiveSalePriceAreaType_' . $enterpriseId . '`, `price.qianniao.vip`.`qianniao_nationalUnifiedPrice_' . $enterpriseId . '`, `price.qianniao.vip`.`qianniao_priceAdjustmentSheet_' . $enterpriseId . '_2020_3`, `price.qianniao.vip`.`qianniao_priceRangeSheet_' . $enterpriseId . '`, `finance.qianniao.vip`.`qianniao_account_transfer_' . $enterpriseId . '`, `finance.qianniao.vip`.`qianniao_customer_balance_' . $enterpriseId . '_1`, `finance.qianniao.vip`.`qianniao_customer_balance_detail_' . $enterpriseId . '_2020_3`, `finance.qianniao.vip`.`qianniao_customer_balance_detail_index_' . $enterpriseId . '`, `finance.qianniao.vip`.`qianniao_customer_balance_index_' . $enterpriseId . '`, `finance.qianniao.vip`.`qianniao_paid_' . $enterpriseId . '_2020_3`, `finance.qianniao.vip`.`qianniao_paid_account_' . $enterpriseId . '_2020_3`, `finance.qianniao.vip`.`qianniao_paid_index_' . $enterpriseId . '`, `finance.qianniao.vip`.`qianniao_pay_receipt_' . $enterpriseId . '_2020_3`, `finance.qianniao.vip`.`qianniao_pay_receipt_index_' . $enterpriseId . '`, `finance.qianniao.vip`.`qianniao_receive_receipt_' . $enterpriseId . '_2020_3`, `finance.qianniao.vip`.`qianniao_receive_receipt_index_' . $enterpriseId . '`, `finance.qianniao.vip`.`qianniao_received_' . $enterpriseId . '_2020_3`, `finance.qianniao.vip`.`qianniao_received_account_' . $enterpriseId . '_2020_3`, `finance.qianniao.vip`.`qianniao_received_index_' . $enterpriseId . '`, `finance.qianniao.vip`.`qianniao_supplier_balance_' . $enterpriseId . '_1`, `finance.qianniao.vip`.`qianniao_supplier_balance_detail_' . $enterpriseId . '_2020_3`, `finance.qianniao.vip`.`qianniao_supplier_balance_detail_index_' . $enterpriseId . '`, `finance.qianniao.vip`.`qianniao_supplier_balance_index_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_commission_flow_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_commission_goods_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_commission_goods_statistics_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_commission_order_' . $enterpriseId . '_2020_3`, `www.qianniao.vip`.`qianniao_commission_order_index_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_commission_withdrawals_' . $enterpriseId . '`, `price.qianniao.vip`.`qianniao_priceAdjustmentSheet_' . $enterpriseId . '_2020_2`, `finance.qianniao.vip`.`qianniao_customer_balance_detail_' . $enterpriseId . '_2020_2`, `finance.qianniao.vip`.`qianniao_pay_receipt_' . $enterpriseId . '_2020_2`, `finance.qianniao.vip`.`qianniao_receive_receipt_' . $enterpriseId . '_2020_2`, `finance.qianniao.vip`.`qianniao_received_' . $enterpriseId . '_2020_2`, `finance.qianniao.vip`.`qianniao_received_account_' . $enterpriseId . '_2020_2`, `finance.qianniao.vip`.`qianniao_supplier_balance_detail_' . $enterpriseId . '_2020_2`, `www.qianniao.vip`.`qianniao_common_app_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_delivery_rule_' . $enterpriseId . '`, `www.qianniao.vip`.`qianniao_delivery_self_rule_' . $enterpriseId . '`, `finance.qianniao.vip`.`qianniao_customer_balance_detail_' . $enterpriseId . '_2020_1`, `finance.qianniao.vip`.`qianniao_account_detail_' . $enterpriseId . '_1`, `finance.qianniao.vip`.`qianniao_paid_' . $enterpriseId . '_2020_1`, `finance.qianniao.vip`.`qianniao_paid_account_' . $enterpriseId . '_2020_1`, `finance.qianniao.vip`.`qianniao_pay_receipt_' . $enterpriseId . '_2020_1`, `finance.qianniao.vip`.`qianniao_receive_receipt_' . $enterpriseId . '_2020_1`, `finance.qianniao.vip`.`qianniao_received_' . $enterpriseId . '_2020_1`, `finance.qianniao.vip`.`qianniao_received_account_' . $enterpriseId . '_2020_1`, `www.qianniao.vip`.`qianniao_enterprise_template_module_' . $enterpriseId . '`';

//            $enterpriseSql = 'DELETE FROM `www.qianniao.vip`.`qianniao_enterprise_1` WHERE `id` = '.$enterpriseId;
//            $this->objDGoods->query($enterpriseSql);
            $enterpriseBandUserCenterSql = 'DELETE FROM `www.qianniao.vip`.`qianniao_user_bind_enterprise` WHERE `enterpriseId` = ' . $enterpriseId;
            $this->objDGoods->query($enterpriseBandUserCenterSql);
            $this->objDGoods->query($sql);
            $i++;
        }

        exit('执行成功, 共删除' . $i . '个企业');
    }

    /**
     * 批量执行格式化客户名称首字母
     */
    public function initCustomerCondition()
    {
        $result = $this->objMSql->initCustomerCondition();
        if(!$result->isSuccess()){
            parent::sendOutput($result->getData(), $result->getErrorCode());
        }
        parent::sendOutput($result->getData());
    }


    /**
     * 批量执行格式化角色cache
     */
    public function initCache()
    {
        $result = $this->objMSql->initCache();
        if(!$result->isSuccess()){
            parent::sendOutput($result->getData(), $result->getErrorCode());
        }
        parent::sendOutput($result->getData());
    }

    /**
     * 清空企业数据
     */
    public function removeEnterprise()
    {
        $enterpriseId = $this->request->param('request_id');
        $enterpriseId = 169;
        $result = $this->objMSql->removeEnterprise($enterpriseId);
        if(!$result->isSuccess()){
            parent::sendOutput($result->getData(), $result->getErrorCode());
        }
        parent::sendOutput($result->getData());
    }

    /**
     * 复制库存表
     */
    public function cpInventoryTable()
    {
        $result = $this->objMSql->cpInventoryTable();
        if(!$result->isSuccess()){
            parent::sendOutput($result->getData(), $result->getErrorCode());
        }
        parent::sendOutput($result->getData());
    }

    /**
     * 更新真实销量
     */
//    public function updateGoods()
//    {
//        $result = $this->objMSql->updateGoods();
//        if(!$result->isSuccess()){
//            parent::sendOutput($result->getData(), $result->getErrorCode());
//        }
//        parent::sendOutput($result->getData());
//    }

    /**
     * 修复订单成本
     */
    public function updateOrderCost()
    {
        $result = $this->objMSql->updateOrderCost();
        if(!$result->isSuccess()){
            parent::sendOutput($result->getData(), $result->getErrorCode());
        }
        parent::sendOutput($result->getData());
    }

    /**
     *  平账脚本
     *  ① 查出所有客户的money
     *  ② 根据money字段进行创建收款单平账
     *  ③ 创建完收款单后自动审核
     */
    public function balancingAccounts()
    {
        $result = $this->objMSql->balancingAccounts();
        if(!$result->isSuccess()){
            parent::sendOutput($result->getData(), $result->getErrorCode());
        }
        parent::sendOutput($result->getData());
    }


    /**
     * 删除xhjc的商品属性
     */
    public function delXhjcGoods()
    {
        $result = $this->objMSql->delXhjcGoods();
        if(!$result->isSuccess()){
            parent::sendOutput($result->getData(), $result->getErrorCode());
        }
        parent::sendOutput($result->getData());
    }

}