每年年初生成新的同步表
public function setNewOrderTable(){
$year=date('Y');
$sqlArr=[];
$sqlArr[]="CREATE TABLE `erp_{$year}_action_chart` (
`tabId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '日志id',
`companyId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '所属公司id',
`orderId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单id',
`type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '0-创单统计,1-发货统计',
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '日志产生日期',
`remark` varchar(250) NOT NULL DEFAULT '' COMMENT '备注',
PRIMARY KEY (`tabId`),
KEY `orderId` (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='统计日志';";
$sqlArr[]="CREATE TABLE `erp_{$year}_action_order` (
`recId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '日志id',
`companyId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '公司id',
`orderId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单id',
`userId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '操作用户id',
`type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-新增,1-返回,2-编辑,3-其他',
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
`remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
PRIMARY KEY (`recId`),
KEY `orderId` (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单日志表';";
$sqlArr[]="CREATE TABLE `erp_{$year}_action_order_third` (
`recId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '日志id',
`companyId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '公司id',
`torderId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单id',
`userId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '操作用户id',
`type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-新增,1-返回,2-编辑,3-其他',
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
`remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
PRIMARY KEY (`recId`),
KEY `torderId` (`torderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='第三方订单日志表';";
$sqlArr[]="CREATE TABLE `erp_{$year}_finance_order` (
`foId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`foCode` char(15) NOT NULL DEFAULT '' COMMENT '流水号',
`companyId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '公司id',
`orderId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单id',
`currency` varchar(10) NOT NULL DEFAULT '' COMMENT '币种',
`goodsNum` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单商品总数量',
`receive` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '应收',
`nreveive` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '实收',
`pay` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '应付',
`npay` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '实付',
`cost` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '商品成本',
`gprofit` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '毛利润',
`grate` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '毛利率',
`isPay` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-未收付款,1-已收付款',
`lnum` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '流水单据数',
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
PRIMARY KEY (`foId`),
KEY `orderId` (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单财务流水';";
$sqlArr[]="CREATE TABLE `erp_{$year}_order` (
`orderId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`companyId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '公司id',
`wareId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '仓库id',
`shopId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '店铺id',
`userId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '订单导入用户id',
`supplierId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '供应商id',
`supplierCode` varchar(50) NOT NULL DEFAULT '' COMMENT '客户编码',
`settleName` varchar(50) NOT NULL DEFAULT '' COMMENT '订单拥有人',
`orderSn` varchar(50) NOT NULL DEFAULT '' COMMENT '订单自编码',
`forderSn` varchar(50) NOT NULL DEFAULT '' COMMENT '订单自编码',
`consignee` varchar(20) NOT NULL DEFAULT '' COMMENT '收货人姓名',
`province` varchar(20) NOT NULL DEFAULT '' COMMENT '收货人省份',
`city` varchar(20) NOT NULL DEFAULT '' COMMENT '收货人城市',
`district` varchar(20) NOT NULL DEFAULT '' COMMENT '收货人区',
`address` varchar(255) NOT NULL DEFAULT '' COMMENT '收货人详细地址',
`mobile` varchar(30) NOT NULL DEFAULT '' COMMENT '收货人电话',
`buyerRegNo` varchar(50) NOT NULL DEFAULT '' COMMENT '购买人昵称',
`buyerName` varchar(20) NOT NULL DEFAULT '' COMMENT '购买人姓名',
`buyerIdType` tinyint(4) DEFAULT '1' COMMENT '购买人证件类型 ,1-身份证,2-其他',
`buyerIdNumber` char(18) NOT NULL DEFAULT '' COMMENT '购买人身份证',
`gcount` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT '商品种类',
`splitNum` tinyint(4) NOT NULL DEFAULT '0' COMMENT '拆分订单数',
`goodsValue` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '商品实际成交价',
`freight` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '运杂费',
`insuredFee` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '保费',
`discount` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '非现金抵扣金额',
`platDiscount` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '平台非现金抵扣券',
`taxTotal` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '代扣税款',
`acturalPaid` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '实际支付金额',
`platActuralPaid` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '平台实际支付金额',
`payCode` varchar(255) NOT NULL DEFAULT '' COMMENT '支付方式',
`payMerId` varchar(255) NOT NULL DEFAULT '' COMMENT '银联商户备案号',
`thirdStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '第三方订单状态,0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`thirdInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '第三方订单信息',
`thirdTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '第三方订单日期',
`payOrderId` varchar(50) NOT NULL DEFAULT '' COMMENT '支付流水号',
`wareStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '选中仓库状态,0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`wareStatusInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '选中仓库信息',
`wareTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '获取仓库信息日期',
`stockStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '选中库存状态,0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`stockStatusInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '选中库存信息',
`stockTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '获取库存信息日期',
`packageStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '选中防护状态,0--未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`packageStatusInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '选中防护信息',
`packageTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '获取防护信息日期',
`splitStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '拆单状态,0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`splitStatusInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '拆单信息',
`splitTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '拆单信息日期',
`csNo` char(10) NOT NULL DEFAULT '' COMMENT '策略编号',
`strategy` varchar(255) NOT NULL DEFAULT '' COMMENT '防护策略',
`package` varchar(50) NOT NULL DEFAULT '' COMMENT '防护库存详情',
`inter` varchar(100) NOT NULL DEFAULT '' COMMENT '内包装包装详情',
`outer` varchar(50) NOT NULL DEFAULT '' COMMENT '外包装型号',
`prevTax` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '预计税款',
`trueTax` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '真实税款',
`taxStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '计税状态:0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`taxInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '计税信息',
`taxTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '计税日期',
`financeStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '财务状态,0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`financeInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '财务信息',
`financeTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '财务信息日期',
`logisStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '选中快递状态,0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`logisStatusInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '选中快递信息',
`logisTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '选中快递日期',
`logisticsId` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '快递公司',
`dotId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '快递网点id',
`logisticsStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '获取运单号状态,0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`logisticsCode` varchar(50) NOT NULL DEFAULT '' COMMENT '运单号',
`logisticsMark` varchar(50) NOT NULL DEFAULT '' COMMENT '大头笔',
`logisticsCcode` varchar(50) NOT NULL DEFAULT '' COMMENT '分拣中心编号',
`logisticsCname` varchar(50) NOT NULL DEFAULT '' COMMENT '分拣中心名',
`logisticsBcode` varchar(50) NOT NULL DEFAULT '' COMMENT '目的网点',
`logisticsBname` varchar(50) NOT NULL DEFAULT '' COMMENT '目的网点名称',
`logisticsType` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ems业务类型1:标准快递 4:经济快递 8:代收到付 9:快递包裹',
`logisticsTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '运单时间',
`logisticsInfo` varchar(255) NOT NULL DEFAULT '' COMMENT '快递公司回执信息',
`pushedStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单推送状态,0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`pushedTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '推送时间',
`pushedInfo` varchar(255) NOT NULL DEFAULT '' COMMENT '推送回执信息',
`loadStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单导出处理状态,0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`loadStatusInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '订单导出处理信息',
`loadTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '订单导出处理日期',
`loadingStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单装载状态,0-未处理,1-处理中,2-处理失败,3-处理成功,4-待人工处理,5-不处理',
`loadingStatusInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '订单装载信息',
`loadingTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '订单装载日期',
`isLabel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否贴标',
`loadingId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '装载单id',
`distStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '配货单打印状态',
`dnickName` varchar(10) NOT NULL DEFAULT '' COMMENT '配货单打印人',
`distTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '配货单打印时间',
`printStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '快递单打印状态',
`printTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '快递单打印日期',
`pnickName` varchar(10) NOT NULL DEFAULT '' COMMENT '快递单打包人',
`checkOutStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '签出状态',
`checkOutTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '签出日期',
`cnickName` varchar(10) NOT NULL DEFAULT '' COMMENT '签出人',
`trueWeight` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '包裹重量',
`preWeight` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '包裹预重',
`volWeight` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '抛货重',
`outStorageStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '出仓状态',
`outStorageTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '出仓日期',
`onickName` varchar(10) NOT NULL DEFAULT '' COMMENT '出仓人',
`senderId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '寄件人id',
`remsg` varchar(255) NOT NULL DEFAULT '' COMMENT '问题订单备注',
`rebackTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '退回时间',
`createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',
`deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态:0-正常,1回收站',
`isHang` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否手动挂起,0-否,1-是',
`isAppointBatch` int(11) NOT NULL DEFAULT '0' COMMENT '指定批次号',
`isSign` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否标记,0-未标记,1-已标记',
`signMsg` varchar(255) NOT NULL DEFAULT '' COMMENT '标记原因',
`signColor` varchar(50) NOT NULL DEFAULT '' COMMENT '标记颜色',
`isAfterSales` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否售后,0-未售后,1-售后',
`istatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单处理状态',
`remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
`expressCode` varchar(155) NOT NULL DEFAULT '' COMMENT 'WMS快递三段码',
`haiGuanTiaoMa` varchar(155) NOT NULL DEFAULT '' COMMENT 'WMS通关条码',
`wareType` varchar(55) NOT NULL DEFAULT '' COMMENT '清关贸易方式',
`csupplierId` int(11) NOT NULL DEFAULT '0' COMMENT '清关供应商',
`isSettle` tinyint(3) NOT NULL DEFAULT '0' COMMENT '是否结算',
PRIMARY KEY (`orderId`),
KEY `orderSn` (`orderSn`) USING BTREE,
KEY `forderSn` (`forderSn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';";
$sqlArr[]="CREATE TABLE `erp_{$year}_order_batch` (
`tabId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`orderId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单id',
`orderGoodsId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单商品id',
`batchId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '批次id',
`qty` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT '商品数量',
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
PRIMARY KEY (`tabId`),
KEY `orderId` (`orderId`),
KEY `orderGoodsId` (`orderGoodsId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单批次表';";
$sqlArr[]="CREATE TABLE `erp_{$year}_order_goods` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`orderId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单id',
`gNum` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '商品项号',
`goodsName` varchar(100) NOT NULL DEFAULT '' COMMENT '商品名称',
`goodsCode` varchar(50) NOT NULL DEFAULT '' COMMENT '商品自编码',
`qty` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品数量',
`price` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '商品单价',
`dprice` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '优惠单价',
`tprice` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '优惠单价(含税)',
`totalPrice` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '商品总价',
`trueTax` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '商品税率',
`taxTotal` decimal(20,2) NOT NULL DEFAULT '0.00',
`unitTax` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '高额税',
`info` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
`updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新日期',
PRIMARY KEY (`id`),
KEY `orderId` (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单商品表';";
$sqlArr[]="CREATE TABLE `erp_{$year}_order_pack` (
`tabId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`orderId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单id',
`packStockId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '包材库存id,如果是第三方包材,为0',
`packId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '包材主键id',
`qty` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '商品数量',
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
PRIMARY KEY (`tabId`),
KEY `orderId` (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单包材表';";
$sqlArr[]="CREATE TABLE `erp_{$year}_order_stock` (
`tabId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`orderId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单id',
`orderGoodsId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单商品id',
`stockId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '库存id',
`qty` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT '商品数量',
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
PRIMARY KEY (`tabId`),
KEY `orderId` (`orderId`),
KEY `orderGoodsId` (`orderGoodsId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单库存表';";
$sqlArr[]="CREATE TABLE `erp_{$year}_order_third` (
`torderId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`companyId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '公司id',
`shopId` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '店铺id',
`order_id` varchar(50) NOT NULL DEFAULT '' COMMENT '来源方订单id',
`province` varchar(20) NOT NULL DEFAULT '' COMMENT '收货人省份',
`city` varchar(20) NOT NULL DEFAULT '' COMMENT '收货人城市',
`district` varchar(20) NOT NULL DEFAULT '' COMMENT '收货人区',
`address` varchar(255) NOT NULL DEFAULT '' COMMENT '收货人详细地址',
`buyerRegNo` varchar(50) NOT NULL DEFAULT '' COMMENT '购买人昵称',
`buyerName` varchar(20) NOT NULL DEFAULT '' COMMENT '购买人姓名',
`consignee` varchar(20) NOT NULL DEFAULT '' COMMENT '收货人姓名',
`mobile` varchar(30) NOT NULL DEFAULT '' COMMENT '收货人电话',
`buyerIdNumber` char(18) NOT NULL DEFAULT '' COMMENT '购买人身份证',
`payOrderId` varchar(50) NOT NULL DEFAULT '' COMMENT '支付流水号',
`payOrderCode` varchar(10) NOT NULL DEFAULT '' COMMENT '支付方式编码',
`payOrderName` varchar(20) NOT NULL DEFAULT '' COMMENT '支付方式名称',
`payOrderStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-未获取支付流水号,1-获取支付流水号中,2-获取支付流水号失败,3-获取支付流水号成功',
`payOrderInfo` varchar(255) NOT NULL DEFAULT '' COMMENT '支付流水号返回信息',
`payOrderTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '支付流水号获取时间',
`goodsValue` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '商品总价',
`acturalPaid` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '支付金额',
`platActuralPaid` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '平台实际支付金额',
`discount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '优惠总金额',
`platDiscount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '平台非现金抵抗金额',
`insuredFee` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '保费',
`freight` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '运杂费',
`taxTotal` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '代扣税款',
`addTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '原订单创建时间',
`payTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '原订单支付时间',
`outstorageStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-第三方未出库,1-第三方出库中,2-第三方出库失败,3-第三方出库成功',
`outstorageInfo` varchar(255) NOT NULL DEFAULT '' COMMENT '第三方出库返回信息',
`outstorageTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '第三方出库时间',
`isSync` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-未同步,1-已同步',
`disType` varchar(10) NOT NULL DEFAULT '' COMMENT '发货方式。01 代表海外直邮发货;02代表商家保税区发货;03代表苏宁保税区发货;空代表国内海外购',
`packageorderid` varchar(50) NOT NULL DEFAULT '' COMMENT '包裹号',
`isBackLogis` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否回传运单号',
`backLogisTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '回传运单号时间',
`logisticsId` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '快递公司id',
`logisticsCode` varchar(50) NOT NULL DEFAULT '' COMMENT '运单号',
`logisticsInfo` varchar(255) NOT NULL DEFAULT '' COMMENT '执行发货返回信息',
`remsg` varchar(255) NOT NULL DEFAULT '' COMMENT '异常信息',
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-未同步,1-已同步',
`deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-可用,1-删除',
`remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
PRIMARY KEY (`torderId`),
KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='接口获取的订单';";
$sqlArr[]="CREATE TABLE `erp_{$year}_order_third_goods` (
`tabId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`torderId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单号',
`gNum` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '项号',
`sku_id` varchar(20) NOT NULL DEFAULT '' COMMENT 'sku`',
`sku_name` varchar(100) NOT NULL DEFAULT '' COMMENT 'sku名称',
`price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '单价`',
`qty` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '数量`',
`orderLineNumber` varchar(50) NOT NULL DEFAULT '' COMMENT '订单行项目号',
`phoneIdentifyCode` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`tabId`),
KEY `torderId` (`torderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='接口获取的订单商品';";
$count=0;
foreach($sqlArr as $sql) {
try{
$ret = Db::execute($sql);
if($ret===0) $count++;
}catch (Exception $e){
setLog(date('Y-m-d H:i:s') . ' 成功表' . $sql . '失败:'.$e->getMessage().'!', 'setNewOrderTable_err');
}
}
setLog(date('Y-m-d H:i:s') . ' 成功生成' . $count . '条订单同步表!', 'setNewOrderTable');
}
在thinkphp中,execute()方法可以在参数中直接输入SQL语句,该方法用于执行insert或update等SQL语句,返回的结果就是SQL语句影响的记录数,语法为“$model ->execute(SQL语句)”。
thinkphp中execute的用法是什么
ThinkPHP中execute()和query()方法都可以在参数里直接输入SQL语句。但是不同的是execute()通常用来执行insert或update等SQL语句,而query常用来执行select等语句。
execute()方法将返回影响的记录数,如果执行SQL的select语句的话,返回的结果将是表的总记录数: