erp_ebay_list 建表语句
CREATE TABLE `erp_ebay_list` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(50) DEFAULT NULL COMMENT '名称',
`ebayaccount` varchar(30) DEFAULT NULL COMMENT 'ebay账号',
`site` int(10) DEFAULT NULL COMMENT '站点',
`ad_type` varchar(20) DEFAULT NULL COMMENT '刊登类型',
`status` int(10) DEFAULT '' COMMENT '刊登状态',
`itemid` varchar(100) DEFAULT NULL COMMENT 'itemid',
`failurereasons` text COMMENT '失败原因',
`sku` varchar(30) DEFAULT NULL COMMENT 'sku名称',
`title` varchar(100) DEFAULT NULL COMMENT '标题',
`title1` varchar(100) DEFAULT NULL COMMENT '子标题',
`categoty1` int(10) DEFAULT NULL COMMENT '第一分类',
`categoty1_all` varchar(100) DEFAULT NULL COMMENT '第一分类值拼串',
`categoty2` int(10) DEFAULT NULL COMMENT '第二分类',
`categoty2_all` varchar(100) DEFAULT NULL COMMENT '第二分类值拼串',
`mul_info` text COMMENT '多属性sku信息',
`add_mul` varchar(255) DEFAULT NULL COMMENT '新增的多属性属性',
`mul_picture` text COMMENT '多属性图片信息',
`item_specifics` text COMMENT '物品属性',
`item_specifics_user` text COMMENT '自定义的物品属性',
`item_status` int(10) DEFAULT NULL COMMENT '物品状态',
`item_status_description` text COMMENT '物品状态描述',
`publication_template_html` int(10) DEFAULT NULL COMMENT '模板id',
`publication_template` int(10) DEFAULT NULL COMMENT '卖家描述id',
`template_deteils` text COMMENT '卖家描述信息',
`template_title` varchar(100) DEFAULT NULL COMMENT '模板标题',
`ebay_picture` text COMMENT 'ebay图片信息 JSON',
`description_title` varchar(50) DEFAULT NULL COMMENT '描述标题',
`description_details` text COMMENT '描述详情 html',
`auction_discount` varchar(30) DEFAULT NULL COMMENT '折扣',
`auction_is_private` varchar(10) DEFAULT NULL COMMENT '是否为私人拍卖',
`published_day` varchar(50) DEFAULT NULL COMMENT '刊登天数',
`price` double(10,2) DEFAULT NULL COMMENT '价格',
`reserve_price` double(10,2) DEFAULT NULL COMMENT '固定价',
`quantity` int(10) DEFAULT NULL COMMENT '数量',
`price_noce` double(10,2) DEFAULT NULL COMMENT '一口价',
`paypal_account` varchar(100) DEFAULT NULL COMMENT 'paypal账号',
`payment_details` varchar(255) DEFAULT NULL COMMENT '付款说明',
`all_buyers` varchar(10) DEFAULT NULL COMMENT '是否允许所有买家购买我的物品',
`nopaypal` varchar(20) DEFAULT NULL COMMENT '是否有PayPal 账户',
`noti_trans` varchar(20) DEFAULT NULL COMMENT '主要运送地址是否在我的运送范围之外',
`is_abandoned` varchar(20) DEFAULT NULL COMMENT '是否有弃标',
`abandoned_num` int(10) DEFAULT NULL COMMENT '弃标个数',
`abandoned_day` varchar(30) DEFAULT NULL COMMENT '弃标天数',
`is_report` varchar(20) DEFAULT NULL COMMENT '是否有违反政策',
`report_num` int(10) DEFAULT NULL COMMENT '违反政策个数',
`report_day` varchar(30) DEFAULT NULL COMMENT '在多少天内',
`is_trust_low` varchar(30) DEFAULT NULL COMMENT '信用指数是否低于',
`trust_low_num` int(10) DEFAULT NULL COMMENT '信用指数',
`already_buy` varchar(20) DEFAULT NULL COMMENT '在过去10已经购买',
`buy_num` int(10) DEFAULT NULL COMMENT '购买个数',
`buy_condition` varchar(20) DEFAULT NULL COMMENT '这项限制只适用于买家信用指数等于或低于',
`buy_credit` int(10) DEFAULT NULL COMMENT '信用指数',
`returns_policy` varchar(50) DEFAULT NULL COMMENT '退货政策',
`returns_days` varchar(30) DEFAULT NULL COMMENT '退货天数',
`returns_delay` varchar(30) DEFAULT NULL COMMENT '提供节假日延期退货至12月31日',
`returns_type` varchar(50) DEFAULT NULL COMMENT '退款方式',
`returns_cost_by` varchar(50) DEFAULT NULL COMMENT '退货运费由谁负担',
`return_details` text COMMENT '退货政策详情',
`item_location` varchar(50) DEFAULT NULL COMMENT '物品所在地',
`item_country` varchar(50) DEFAULT NULL COMMENT '国家或地区',
`item_post` int(10) DEFAULT NULL COMMENT '邮编',
`inter_process_day` int(10) DEFAULT NULL COMMENT '处理时间',
`inter_fast_send` varchar(20) DEFAULT NULL COMMENT '是否快速寄货',
`inter_trans_type` varchar(100) DEFAULT NULL COMMENT '运输方式',
`inter_trans_cost` double(10,2) DEFAULT NULL COMMENT '运费',
`inter_free` varchar(20) DEFAULT NULL COMMENT '是否免费',
`inter_trans_extracost` double(10,2) DEFAULT NULL COMMENT '额外每件加收',
`inter_trans_AK_extracost` double(10,2) DEFAULT NULL COMMENT 'AK,HI,PR 额外收费',
`international_type1` varchar(50) DEFAULT NULL COMMENT '国际运输类别1',
`international_cost1` double(10,2) DEFAULT NULL COMMENT '国际运输花费1',
`international_free1` varchar(20) DEFAULT NULL,
`international_extracost1` double(10,2) DEFAULT NULL COMMENT '国际运输额外花费1',
`international_is_worldwide1` varchar(20) DEFAULT NULL COMMENT '是否全球1',
`international_is_country1` varchar(255) DEFAULT NULL COMMENT '运送的国家1',
`international_type2` varchar(50) DEFAULT NULL COMMENT '国际运输类别2',
`international_cost2` double(10,2) DEFAULT NULL COMMENT '国际运输花费2',
`international_free2` varchar(20) DEFAULT NULL,
`international_extracost2` double(10,2) DEFAULT NULL COMMENT '国际运输额外花费2',
`international_is_worldwide2` varchar(20) DEFAULT NULL COMMENT '是否全球2',
`international_is_country2` varchar(255) DEFAULT NULL COMMENT '运送的国家2',
`international_type3` varchar(50) DEFAULT NULL COMMENT '国际运输类别3',
`international_cost3` double(10,2) DEFAULT NULL COMMENT '国际运输花费3',
`international_free3` varchar(20) DEFAULT NULL,
`international_extracost3` double(10,2) DEFAULT NULL COMMENT '国际运输额外花费3',
`international_is_worldwide3` varchar(20) DEFAULT NULL COMMENT '是否全球3',
`international_is_country3` varchar(255) DEFAULT NULL COMMENT '运送的国家3',
`international_type4` varchar(50) DEFAULT NULL COMMENT '国际运输类别4',
`international_cost4` double(10,2) DEFAULT NULL COMMENT '国际运输花费4',
`international_free4` varchar(20) DEFAULT NULL,
`international_extracost4` double(10,2) DEFAULT NULL COMMENT '国际运输额外花费4',
`international_is_worldwide4` varchar(20) DEFAULT NULL COMMENT '是否全球4',
`international_is_country4` varchar(255) DEFAULT NULL COMMENT '运送的国家4',
`international_type5` varchar(50) DEFAULT NULL COMMENT '国际运输类别5',
`international_cost5` double(10,2) DEFAULT NULL COMMENT '国际运输花费5',
`international_free5` varchar(20) DEFAULT NULL,
`international_extracost5` double(10,2) DEFAULT NULL COMMENT '国际运输额外花费5',
`international_is_worldwide5` varchar(20) DEFAULT NULL COMMENT '是否全球5',
`international_is_country5` varchar(255) DEFAULT NULL COMMENT '运送的国家5',
`excludeship` text COMMENT '排除的运输国家',
`createtime` datetime DEFAULT NULL COMMENT '创建时间',
`updatetime` datetime DEFAULT NULL COMMENT '修改时间',
`upc` varchar(50) NOT NULL COMMENT 'upc',
`ean` varchar(50) NOT NULL COMMENT 'ean',
`isb` varchar(50) NOT NULL COMMENT 'isb',
`starttime` datetime NOT NULL COMMENT '上架开始时间',
`sellallnum` int(5) NOT NULL COMMENT '销售数量',
`sku_search` varchar(30) DEFAULT NULL,
`ad_user` varchar(10) DEFAULT '' COMMENT '广告所属人',
PRIMARY KEY (`id`),
KEY `IDX_ITEMID` (`itemid`),
KEY `IDX_SKU` (`sku`),
KEY `IDX_SKUSEARCH` (`sku_search`),
KEY `IDX_EBAYACCOUNT_SKUSEARCH` (`ebayaccount`,`sku_search`),
KEY `IDX_ad_type` (`ad_type`),
KEY `IDX_site` (`site`),
KEY `IDX_status` (`status`),
KEY `ad_user` (`ad_user`),
KEY `IDX_EBAYACCOUNT` (`ebayaccount`),
KEY `IDX_STARTTIME_ADUSER` (`starttime`,`ad_user`)
) ENGINE=InnoDB AUTO_INCREMENT=1520022 DEFAULT CHARSET=utf8;
erp_ebay_product
CREATE TABLE `erp_ebay_product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`status` tinyint(3) DEFAULT '' COMMENT '1 草稿 2 上架 3 下架',
`itemid` varchar(30) DEFAULT '' COMMENT 'itemid',
`site` varchar(20) DEFAULT '' COMMENT '站点',
`category_id` int(5) DEFAULT '' COMMENT '分类id',
`category_id_second` int(5) DEFAULT '' COMMENT '账号id',
`account_id` int(5) DEFAULT NULL COMMENT '刊登类型',
`ad_type` tinyint(3) DEFAULT '' COMMENT '标题',
`title` varchar(200) DEFAULT '' COMMENT '子标题',
`subTitle` varchar(200) DEFAULT '' COMMENT '刊登时间',
`timeMax` varchar(10) DEFAULT '' COMMENT '物品属性',
`itemSpecifics` text,
`user_spe` text,
`upc` varchar(20) DEFAULT '',
`ean` varchar(20) DEFAULT '',
`isbn` varchar(20) DEFAULT '',
`product_info` varchar(200) DEFAULT '' COMMENT '所在地，国家，邮编',
`payPalEmailAddress` varchar(100) DEFAULT '' COMMENT 'paypal',
`condition` int(3) DEFAULT NULL COMMENT '物品状况',
`condition_detail` text COMMENT '状况描述',
`privateListing` varchar(10) DEFAULT '' COMMENT '私人拍卖',
`ebay_price` double(10,2) NOT NULL COMMENT '价格',
`ebay_quantity` int(5) DEFAULT NULL COMMENT '数量',
`skuinfo` text COMMENT '多属性sku信息',
`zidingyi` varchar(100) DEFAULT '' COMMENT '增加的多属性',
`detailPicListMul` text COMMENT '多属性图片',
`erp_sku` varchar(50) DEFAULT '' COMMENT 'erp的sku',
`ebay_sku` varchar(50) DEFAULT '' COMMENT 'ebay后台SKU',
`detailPicList` text COMMENT '描述的照片',
`templatehtml` tinyint(3) DEFAULT NULL COMMENT '描述模板',
`template` tinyint(3) DEFAULT NULL COMMENT '卖家描述',
`template_titlle` varchar(100) DEFAULT '' COMMENT '描述的标题',
`detailPicListDescription` text COMMENT '描述的图片',
`detail` text COMMENT 'ebay资料',
`ebay_detail` text COMMENT '同步下来的描述',
`transtemplate` tinyint(3) DEFAULT NULL COMMENT '运输模板',
`inter_process_day` varchar(3) DEFAULT '' COMMENT '处理天数',
`returnPolicy` text COMMENT '退货政策',
`buyerRequirementDetails` text COMMENT '买家要求',
`shippingServiceOptions` text COMMENT '国内运输',
`internationalShippingServiceOption` text COMMENT '国际运输',
`un_ship` text,
`creat_time` datetime DEFAULT NULL,
`start_time` datetime DEFAULT NULL,
`add_user` varchar(30) DEFAULT NULL,
`is_update` tinyint(3) DEFAULT '' COMMENT '0 否 1 是',
`publish_error` text COMMENT '错误提示',
`store_category_id` varchar(20) DEFAULT NULL,
`bestOfferAutoAcceptPrice` double(10,2) DEFAULT '0.00' COMMENT '自动接受最小价格',
`MinimumBestOfferPrice` double(10,2) DEFAULT '0.00' COMMENT '自动拒绝最低价格',
`reserve_price` double(10,2) DEFAULT NULL COMMENT '保留价',
`price_noce` double(10,2) DEFAULT NULL COMMENT '一口价',
PRIMARY KEY (`id`),
KEY `itemid` (`itemid`(12)),
KEY `creat_time_staus` (`creat_time`,`status`),
KEY `erp_sku` (`erp_sku`),
KEY `status_add_user` (`status`,`add_user`),
KEY `ACCOUNT_ID` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1620244 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; sf_itemid_sku
CREATE TABLE `sf_itemid_sku` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`sku` varchar(250) DEFAULT '',
`ebaysku` varchar(250) DEFAULT NULL,
`itemid` int(11) DEFAULT NULL,
`itemNumber` varchar(100) DEFAULT '',
`safe_qty` int(11) DEFAULT '',
`alert_qty` int(11) DEFAULT '',
`initial_qty` int(11) NOT NULL DEFAULT '',
`sold_qty` int(11) NOT NULL DEFAULT '',
`now_qty` int(11) DEFAULT '',
`price` double DEFAULT '',
`chipping_fee` double DEFAULT '',
`currency_rate` double DEFAULT '',
`currency_type` varchar(10) DEFAULT NULL,
`token_id` int(11) NOT NULL DEFAULT '',
`last_turndown_date` date DEFAULT NULL COMMENT '最后下调的日期',
`sold_1_day` int(5) DEFAULT '' COMMENT '1天销量',
`sold_7_day` int(5) DEFAULT '' COMMENT '7天销量',
`sold_30_day` int(5) DEFAULT '' COMMENT '30天销量',
`products_infringement_type` tinyint(2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `itemid` (`itemid`),
KEY `sku` (`sku`),
KEY `ebaysku` (`itemNumber`,`ebaysku`),
KEY `ebaysku2` (`ebaysku`),
KEY `itemNumber` (`itemNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=374 DEFAULT CHARSET=utf8; sf_product_itemid
CREATE TABLE `sf_product_itemid` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`item_number` varchar(100) DEFAULT NULL,
`siteID` int(5) NOT NULL DEFAULT '',
`location` varchar(255) DEFAULT NULL,
`itemViewUrl` text,
`last_check_time` datetime DEFAULT NULL,
`item_title` text,
`token_id` int(11) DEFAULT NULL,
`price` double DEFAULT '',
`chiping_fee` double DEFAULT '',
`listing_type` varchar(50) DEFAULT NULL,
`developer_account` varchar(64) DEFAULT NULL,
`item_sku` varchar(100) DEFAULT NULL,
`current_sku` varchar(100) DEFAULT NULL,
`result` int(1) NOT NULL DEFAULT '',
`getDetails` int(1) NOT NULL DEFAULT '',
`itemStartTime` datetime DEFAULT NULL,
`isOutofControl` int(11) DEFAULT '',
`totalFee` double DEFAULT NULL,
`itemUpdateTime` datetime DEFAULT NULL,
`user_id` int(11) NOT NULL DEFAULT '' COMMENT '广告负责人',
`userModifyTime` datetime DEFAULT NULL COMMENT '负责人的最后变更时间',
`isSingleAttribute` tinyint(4) DEFAULT NULL COMMENT '是否单属性，1为单属性,0为多属性',
`isEnded` tinyint(4) NOT NULL DEFAULT '' COMMENT '是否已下架，1为已下架，0为在线',
`quantitySold` int(11) NOT NULL DEFAULT '' COMMENT '总销量',
`paypal_email_address` varchar(128) DEFAULT NULL COMMENT 'paypal邮箱',
`country` varchar(64) NOT NULL DEFAULT '' COMMENT '国家',
`getDetailsTime` datetime NOT NULL COMMENT '同步广告详情时间',
`dispatchTime` tinyint(3) DEFAULT '',
`is_modify` tinyint(3) DEFAULT '',
PRIMARY KEY (`item_id`),
KEY `item_number` (`item_number`),
KEY `listing_type` (`isEnded`,`paypal_email_address`,`listing_type`),
KEY `IDX_GETDETAILS_TOKENID` (`getDetails`,`token_id`),
KEY `IDX_LOCATION` (`location`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8; erp_orders
CREATE TABLE `erp_orders` (
`erp_orders_id` int(12) NOT NULL AUTO_INCREMENT COMMENT '内单号',
`ebay_orders_id` int(12) DEFAULT NULL COMMENT '外单号',
`orderlineitemid` varchar(200) NOT NULL DEFAULT '' COMMENT 'ebayOrderID',
`buyer_id` varchar(200) DEFAULT NULL COMMENT '买家ID',
`buyer_name` varchar(200) DEFAULT NULL COMMENT '买家全名（收货人）',
`buyer_phone` varchar(100) DEFAULT NULL COMMENT '买家电话',
`buyer_email` varchar(150) DEFAULT NULL COMMENT '买家EMAIL地址',
`buyer_address_1` text COMMENT '收货地址(街道)',
`buyer_address_2` text COMMENT '收货地址(门牌)',
`buyer_city` varchar(150) DEFAULT NULL COMMENT '收货地址(城市)',
`buyer_state` varchar(150) DEFAULT NULL COMMENT '收货地址(州、省)',
`buyer_zip` varchar(20) DEFAULT NULL COMMENT '收货地址(编)',
`buyer_country` varchar(150) DEFAULT NULL COMMENT '收货地址(国家)',
`buyer_country_code` varchar(50) DEFAULT NULL COMMENT '国家代码',
`orders_total` float DEFAULT NULL COMMENT '订单总金额',
`orders_ship_fee` float DEFAULT NULL COMMENT '运费',
`currency_type` varchar(10) DEFAULT NULL COMMENT '币种',
`currency_value` float DEFAULT NULL COMMENT '汇率比率',
`shipping_method` varchar(100) DEFAULT NULL COMMENT '发货方式',
`ShippingServiceSelected` varchar(200) DEFAULT NULL COMMENT '客户选择的物流方式',
`shipmentAutoMatched` int(6) DEFAULT '' COMMENT '自动匹配物流',
`pay_method` varchar(200) DEFAULT NULL COMMENT '支付 方式',
`pay_id` varchar(200) DEFAULT NULL COMMENT '支付凭证',
`transactionIDNew` text,
`orders_time` datetime DEFAULT NULL,
`sales_account` varchar(150) DEFAULT NULL COMMENT '销售帐号',
`erp_user_id` int(11) DEFAULT NULL COMMENT 'ERP用户ID',
`orders_type` int(2) DEFAULT NULL COMMENT '订单类型',
`orders_status` int(1) DEFAULT NULL COMMENT '订单状态',
`orders_is_backorder` int(1) NOT NULL DEFAULT '' COMMENT '订单是否欠货',
`eub_status` int(2) NOT NULL COMMENT 'EUB状态',
`orders_remark` text COMMENT '订单备注',
`orders_export_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单导入时间',
`orders_paid_time` datetime DEFAULT NULL COMMENT '订单支付时间',
`orders_print_time` datetime DEFAULT NULL COMMENT '订单打印时间',
`printTimes` tinyint(4) NOT NULL DEFAULT '' COMMENT '订单打印次数',
`orders_is_join` int(1) DEFAULT '' COMMENT '订单是否合并或拆分过',
`orders_is_split` int(1) NOT NULL DEFAULT '',
`orders_is_resend` int(1) NOT NULL DEFAULT '' COMMENT '订单是否重发过',
`orders_is_refund` int(1) NOT NULL DEFAULT '' COMMENT '订单是否有退款',
`orders_shipping_code` varchar(100) DEFAULT NULL COMMENT '追踪码',
`orders_old_shipping_code` varchar(100) DEFAULT NULL,
`orders_shipping_time` timestamp NULL DEFAULT NULL COMMENT '扫描发货时间',
`orders_shipping_user` int(11) NOT NULL DEFAULT '' COMMENT '扫描人的ERP帐户ID',
`orders_is_rechecked` int(2) NOT NULL DEFAULT '' COMMENT '订单是否已复检',
`recheck_user` int(10) NOT NULL DEFAULT '' COMMENT '复检用户',
`recheck_time` datetime DEFAULT NULL COMMENT '复检时间',
`orders_is_tracking` int(11) NOT NULL DEFAULT '' COMMENT '无用字段，优化代码后可删除',
`orders_join_remark` text COMMENT '订单合并备注',
`custom_shipping_fee` float NOT NULL DEFAULT '',
`notes_to_yourself` text COMMENT '付款备注',
`orders_shipping_method` varchar(100) DEFAULT NULL,
`ebayStatusIsMarked` int(1) NOT NULL DEFAULT '' COMMENT 'EB状态已否已标记',
`eBayStatusMarkedCount` int(2) NOT NULL DEFAULT '' COMMENT 'EB状态标记次数',
`eBayStatusUploadTrackingNumberCount` int(2) NOT NULL DEFAULT '' COMMENT '上传追踪码次数',
`trackingNumberUploaded` int(1) NOT NULL DEFAULT '' COMMENT '追踪码是否已上传',
`priceMayBeWorry` int(1) NOT NULL DEFAULT '' COMMENT '疑似价格错误',
`autoSendMessageStatus` int(1) NOT NULL DEFAULT '' COMMENT '是否已自动发送Message给买家',
`autoSendMessageLastTime` datetime DEFAULT NULL,
`token_id` int(5) NOT NULL DEFAULT '' COMMENT 'token id',
`autoSendFeedbackStatus` int(1) NOT NULL DEFAULT '' COMMENT '自动留评价状态',
`autoSendFeedbackLastTime` datetime DEFAULT NULL,
`tempFields` int(11) NOT NULL DEFAULT '' COMMENT '临时字段，数据统计或临时批量操作时使用',
`productsCost` float NOT NULL DEFAULT '' COMMENT '预估总成本',
`packetWeight` float NOT NULL DEFAULT '' COMMENT '包裹总重',
`shippingCost` float NOT NULL DEFAULT '' COMMENT '发货运费成本',
`platFeeTotal` float DEFAULT NULL COMMENT '平台费',
`profitTotal` float DEFAULT NULL COMMENT '当前利润',
`profitRate` float NOT NULL DEFAULT '' COMMENT '利润率',
`overseasArrived` int(1) NOT NULL DEFAULT '' COMMENT '海外仓已到货',
`orders_expired_time` varchar(50) DEFAULT NULL COMMENT '速卖通订单 发货截止时间 天,小时,分钟',
`orders_logistics_id` int(11) DEFAULT NULL,
`amz_finished` tinyint(2) NOT NULL DEFAULT '',
`case_result` varchar(128) DEFAULT NULL COMMENT 'CASE结果',
`orders_is_alert` int(11) DEFAULT '' COMMENT '订单 paypal是否交易警告 0 不 1 是',
`cargo_weight` float NOT NULL COMMENT '货代重量',
`isMailSended` tinyint(1) NOT NULL DEFAULT '' COMMENT '是否已发送邮件',
`isMixed` tinyint(2) NOT NULL DEFAULT '' COMMENT '是否混合订单,1为是',
`isChineseOrder` tinyint(2) NOT NULL DEFAULT '' COMMENT '是否竞拍订单，1为是，只对ebay',
`operateCost` float NOT NULL DEFAULT '' COMMENT '总操作费',
`write_date` timestamp NULL DEFAULT NULL COMMENT '订单修改日期',
`orders_warehouse_id` int(5) DEFAULT '' COMMENT '所属仓库',
`matching_warehouse_time` int(11) DEFAULT '' COMMENT '上次匹配仓库时间',
`end_time` int(11) DEFAULT '' COMMENT '买家确认收货时间',
`cnz_code` varchar(50) DEFAULT '' COMMENT '平邮查询网站挂号码',
`old_erp_orders_id` int(11) NOT NULL DEFAULT '' COMMENT '拆单前的内单号，此字段必须保持在最后一个字段',
PRIMARY KEY (`erp_orders_id`),
KEY `pay_id` (`pay_id`),
KEY `buyer_id` (`buyer_id`),
KEY `buyer_email` (`buyer_email`,`orders_time`,`orders_logistics_id`,`old_erp_orders_id`),
KEY `print_time` (`orders_print_time`),
KEY `orders_shipping_time` (`orders_shipping_time`),
KEY `ebay_orders_id` (`orderlineitemid`,`token_id`),
KEY `amz_finished` (`amz_finished`),
KEY `IDX_OR_OR_CU` (`orders_status`,`orders_type`,`currency_type`),
KEY `IDX_ORDERS_WAREHOUSE_ID_ORDERS_EXPORT_TIME` (`orders_warehouse_id`,`orders_export_time`),
KEY `IDX_SH_OR_OR` (`shipmentAutoMatched`,`orders_warehouse_id`,`orders_export_time`),
KEY `IDX_ORDERS_TYPE_ORDERS_EXPORT_TIME` (`orders_type`,`orders_export_time`),
KEY `IDX_OR_SH_OR` (`orders_warehouse_id`,`shipmentAutoMatched`,`orders_export_time`),
KEY `IDX_SHIPMENTAUTOMATCHED_ORDERS_EXPORT_TIME` (`shipmentAutoMatched`,`orders_export_time`),
KEY `IDX_CNZ_CODE_ORDERS_EXPORT_TIME` (`cnz_code`,`orders_export_time`),
KEY `IDX_OR_IS_OR` (`orders_is_backorder`,`isMixed`,`orders_export_time`),
KEY `IDX_ST_SH` (`shipmentAutoMatched`,`orders_status`),
KEY `IDX_BUYERNAME` (`buyer_name`),
KEY `IDX_EBAYSTATUSISMARKED_TYPE_EXPORTTIME` (`ebayStatusIsMarked`,`orders_type`,`orders_export_time`),
KEY `IDX_USERID_STATUS_EXPORTTIME` (`erp_user_id`,`orders_status`,`orders_export_time`),
KEY `IDX_STATUS_TYPE_EXPORTIME` (`orders_status`,`orders_type`,`orders_export_time`),
KEY `IDX_BUYERCOUNTRY` (`buyer_country`),
KEY `IDX_SHIPPINGCODE_SHIPMENTAUTOMATCHED` (`orders_shipping_code`,`shipmentAutoMatched`,`orders_status`,`orders_export_time`),
KEY `IDX_ISJOIN_ISBACKORDER_STATUS` (`orders_is_join`,`orders_is_backorder`,`orders_status`),
KEY `IDX_STATUS_TYPE_SHIPPINGTIME` (`orders_status`,`orders_type`,`orders_shipping_time`),
KEY `IDX_ISJOIN_ISBACKORDER_EXPORTTIME` (`orders_is_join`,`orders_is_backorder`,`orders_export_time`),
KEY `IDX_STATUS_JOIN_PRINTTIME` (`orders_status`,`orders_is_join`,`orders_print_time`),
KEY `IDX_EXPORTTIME` (`orders_export_time`),
KEY `IDX_STATUS_JOIN_EXPORTTIME` (`orders_status`,`orders_is_join`,`orders_export_time`),
KEY `IDX_STATUS_JOIN_ORDERID` (`orders_status`,`orders_is_join`,`erp_orders_id`),
KEY `IDX_BUYERCOUNTRYCODE` (`buyer_country_code`)
) ENGINE=InnoDB AUTO_INCREMENT=78649938 DEFAULT CHARSET=utf8; erp_orders_product
CREATE TABLE `erp_orders_products` (
`orders_products_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`erp_orders_id` int(11) DEFAULT NULL COMMENT '内单号',
`ebay_orders_id` int(11) DEFAULT NULL COMMENT 'ebay平台订单号',
`orderlineitemid` varchar(100) NOT NULL DEFAULT '' COMMENT '交易ID',
`orders_item_number` varchar(100) NOT NULL DEFAULT '',
`itemSite` varchar(50) DEFAULT '' COMMENT 'Amazon站点',
`orders_sku` varchar(50) DEFAULT NULL COMMENT '产品代码',
`orders_item` varchar(200) DEFAULT NULL COMMENT '商品名称',
`item_price` float DEFAULT NULL COMMENT '产品价格',
`price_suggest` float DEFAULT '' COMMENT '建议价格',
`item_count` int(11) DEFAULT NULL COMMENT '产品数量',
`item_cost` float DEFAULT '' COMMENT '产品运费',
`item_attribute` text COMMENT '订单产品属性',
`transactionID` varchar(100) NOT NULL DEFAULT '' COMMENT '订单产品属性',
`token_id` int(11) NOT NULL DEFAULT '' COMMENT '事物id',
`feedback_id` varchar(50) DEFAULT NULL COMMENT '令牌id',
`comment_type` varchar(100) DEFAULT NULL COMMENT '评论类型',
`comment_text` text COMMENT '评论内容',
`comment_time` datetime DEFAULT NULL COMMENT '评论时间',
`getFeedback` int(11) NOT NULL DEFAULT '',
`platform_sku` varchar(50) DEFAULT NULL COMMENT '平台SKU',
`item_freight` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '单品运费',
PRIMARY KEY (`orders_products_id`),
KEY `IDX_ORDERSID_ORDERSSKU` (`erp_orders_id`,`orders_sku`),
KEY `IDX_ORDERS_SKU_ERP_ORDERS_ID` (`orders_sku`),
KEY `IDX_PLATFORMSKU` (`platform_sku`),
KEY `transactionID` (`transactionID`),
KEY `orders_item_number` (`orders_item_number`),
KEY `erp_orders_id_2` (`ebay_orders_id`,`orderlineitemid`,`orders_item`,`token_id`),
KEY `IDX_ORDERLINEITEMID` (`orderlineitemid`)
) ENGINE=InnoDB AUTO_INCREMENT=88085220 DEFAULT CHARSET=utf8 COMMENT='订单产品明细表';
erp_product_data
CREATE TABLE `erp_products_data` (
`products_id` int(12) NOT NULL AUTO_INCREMENT,
`products_title` varchar(255) NOT NULL DEFAULT ' ' COMMENT '产品标题',
`products_name_en` varchar(1500) DEFAULT NULL,
`products_name_cn` varchar(1500) DEFAULT NULL,
`products_sku` varchar(1000) DEFAULT NULL,
`products_sort` int(11) NOT NULL DEFAULT '' COMMENT '产品分类',
`products_declared_en` varchar(200) DEFAULT NULL COMMENT '申报名称 英文',
`products_declared_cn` varchar(200) DEFAULT NULL COMMENT '申报名称 中文',
`products_declared_value` decimal(10,2) DEFAULT NULL COMMENT '申报价值（USD）',
`products_storage` int(8) NOT NULL DEFAULT '',
`products_value` double DEFAULT NULL,
`products_history_values` varchar(255) NOT NULL DEFAULT '',
`taobao_price` double NOT NULL DEFAULT '',
`products_weight_maybe` decimal(10,3) DEFAULT NULL,
`weightWithPacket` int(1) NOT NULL DEFAULT '',
`productsWeightFromAmput` float NOT NULL DEFAULT '',
`products_weight` decimal(10,3) DEFAULT NULL,
`products_volume` varchar(200) DEFAULT NULL,
`products_url` varchar(1500) DEFAULT NULL,
`products_status` int(1) NOT NULL DEFAULT '',
`products_status_2` varchar(100) DEFAULT NULL,
`products_orders` int(11) NOT NULL DEFAULT '',
`products_saler_info` text,
`products_supplier_price1` varchar(200) DEFAULT NULL,
`products_supplier_name1` varchar(200) DEFAULT NULL,
`products_supplier_city1` varchar(200) DEFAULT NULL,
`products_times` varchar(500) DEFAULT NULL,
`products_remark_1` text,
`products_remark_2` text,
`products_times_last` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`products_follow` int(1) NOT NULL DEFAULT '',
`products_unreal` int(11) DEFAULT '',
`products_real` int(11) DEFAULT '',
`products_last_sale` timestamp NULL DEFAULT NULL,
`products_off_time` date DEFAULT NULL,
`products_is_confirm` int(1) NOT NULL DEFAULT '',
`products_dscp_en` text,
`products_dscp_cn` text,
`product_place` int(11) NOT NULL DEFAULT '',
`products_location` varchar(150) DEFAULT '' COMMENT '仓位',
`products_alert` double NOT NULL DEFAULT '',
`storage_on_shipping` int(11) NOT NULL DEFAULT '',
`sales_record_min_days` int(11) NOT NULL DEFAULT '',
`saleCountInMinCycle` int(11) NOT NULL DEFAULT '',
`DateCountTime` datetime DEFAULT NULL,
`suggest_price` varchar(100) DEFAULT '',
`products_remark` text,
`products_create_time` datetime DEFAULT NULL COMMENT '产品开发时间',
`products_change_price_time` datetime DEFAULT NULL,
`products_supplier2` text,
`products_supplier_price2` varchar(200) DEFAULT NULL,
`products_supplier_name2` varchar(200) DEFAULT NULL,
`products_supplier_city2` varchar(200) DEFAULT NULL,
`products_supplier3` text,
`products_supplier_price3` varchar(200) DEFAULT NULL,
`products_supplier_name3` varchar(200) DEFAULT NULL,
`productsCalculateSalesTotalTime` datetime DEFAULT NULL,
`products_color` text,
`products_more_img` text,
`products_same_to_me` text,
`products_type` int(1) NOT NULL DEFAULT '',
`products_min_price` double NOT NULL DEFAULT '',
`products_father_sku` varchar(100) DEFAULT NULL,
`products_is_discount` int(1) NOT NULL DEFAULT '',
`user_id` int(5) NOT NULL DEFAULT '',
`products_imgs` text COMMENT '多图',
`products_intro_en` text COMMENT '中文资料',
`products_with_battery` int(1) NOT NULL DEFAULT '',
`products_with_adapter` int(1) NOT NULL DEFAULT '',
`products_with_fluid` tinyint(1) NOT NULL DEFAULT '' COMMENT '是否液体',
`products_with_powder` tinyint(1) NOT NULL DEFAULT '' COMMENT '是否粉末',
`products_keywords` text,
`products_warring_string` text,
`products_html_mod` longtext COMMENT '英文资料',
`products_parts_info` text,
`products_suppliers_id` int(11) NOT NULL DEFAULT '',
`products_suppliers_ids` varchar(255) NOT NULL DEFAULT '' COMMENT 'sku对应的供应商',
`products_min_days_arrival` int(11) NOT NULL DEFAULT '',
`products_check_standard` text COMMENT '质检标准',
`productsLastModify` datetime DEFAULT NULL,
`productsPhotoStandard` text,
`calculateProductsCountArray` text COMMENT '各种数量-虚库存实库存建议采购量等',
`calculateProductsSalesCountArray` text COMMENT 'N天销售量及计算参数',
`productAttributesArray` text,
`productsIsActive` int(1) NOT NULL DEFAULT '',
`productsDescriptionFR` text,
`productsDescriptionDE` text,
`productsDescriptionJP` text,
`salers` varchar(255) DEFAULT NULL COMMENT '销售负责人',
`is_aboluo_product` tinyint(2) DEFAULT '' COMMENT '阿波罗产品',
`hasNewInput` tinyint(1) NOT NULL DEFAULT '' COMMENT '是否有入库,1为是',
`virtual_inventory` int(8) DEFAULT NULL,
`virtual_order_inventory` int(8) DEFAULT NULL,
`firstDownTime` datetime DEFAULT NULL COMMENT '第一次下调的时间',
`product_warehouse_id` int(5) DEFAULT '' COMMENT '所属仓库ID',
`pack_method` int(5) DEFAULT NULL COMMENT '包装方式',
`sold_60` int(11) DEFAULT '' COMMENT '60天销量',
`sold_30` int(8) NOT NULL DEFAULT '' COMMENT '30天销量',
`sold_14` int(8) NOT NULL DEFAULT '' COMMENT '14天销量',
`sold_7` int(8) NOT NULL DEFAULT '' COMMENT '7天销量',
`backMoney_60` int(11) DEFAULT '' COMMENT '60天退款次数',
`profitRate_15` float DEFAULT '' COMMENT '近15天平均利润率',
`suggestPurchase` int(8) NOT NULL DEFAULT '' COMMENT '建议采购量',
`wish_is_promoted` tinyint(2) DEFAULT '' COMMENT 'wish是否促销 1-否 2-是',
`wish_status` tinyint(2) NOT NULL DEFAULT '' COMMENT '1-在售待审核，2-在售审核通过',
`product_hscode` varchar(30) NOT NULL COMMENT '产品的hscode',
`product_return_rate` int(3) NOT NULL DEFAULT '' COMMENT '产品退税税率',
`must_keyword` text,
`option_keyword` text,
`products_unit1` varchar(100) DEFAULT NULL COMMENT '计量单位',
`products_unit2` varchar(100) DEFAULT NULL COMMENT '计量单位2',
`products_elements` varchar(255) DEFAULT NULL COMMENT '申报要素',
`products_is_declare` tinyint(2) NOT NULL DEFAULT '' COMMENT '产品申报状态 1-未申报 2-已申报',
`dev_uid` int(32) DEFAULT NULL,
`spu` varchar(30) DEFAULT NULL,
`model` varchar(30) DEFAULT NULL,
`hwcSku` varchar(100) DEFAULT '' COMMENT '对应海外仓sku',
`warehouse_value` double DEFAULT NULL COMMENT '库存价',
`purchase_user_id` int(5) DEFAULT NULL COMMENT 'SKU采购人员',
`products_delivery` int(5) DEFAULT NULL COMMENT 'SKU的交期',
`products_alternate_link` varchar(255) DEFAULT NULL COMMENT '备用链接,多个备用链接以|分隔',
`products_package` tinyint(2) DEFAULT NULL COMMENT '来货包装',
`brand_id` int(11) DEFAULT '' COMMENT '品牌ID',
`smt_special_sku` int(11) DEFAULT '' COMMENT 'smt特殊sku',
`purchase_dev_user_id` int(5) DEFAULT '' COMMENT '采购开发',
`third_sku` varchar(100) DEFAULT NULL COMMENT '第三方SKU',
`is_key` tinyint(1) DEFAULT '' COMMENT '是否为重点产品 1:是 0:否',
`unit` varchar(100) DEFAULT '' COMMENT '计量单位',
`hsncode` varchar(30) DEFAULT NULL COMMENT 'amazon india hscode',
`taxation` tinyint(5) DEFAULT NULL COMMENT 'amazon india taxation',
`status_remark` varchar(100) DEFAULT '' COMMENT '状态备注',
`special_packing_method` tinyint(4) NOT NULL DEFAULT '',
`express_package_standard` int(10) unsigned DEFAULT NULL COMMENT '快递包装规格表主键id',
`is_unwrap` tinyint(3) unsigned NOT NULL DEFAULT '' COMMENT '是否拆包装发货 1 原装发货 2 拆包装发货',
`products_priority` varchar(30) NOT NULL DEFAULT 'ordinary' COMMENT '产品优先级(普通:ordinary;优先:first;加急:urgent)',
PRIMARY KEY (`products_id`),
KEY `products_suppliers_id` (`products_suppliers_id`),
KEY `user_id` (`user_id`),
KEY `sku+warhouseid` (`products_sku`(255),`product_warehouse_id`),
KEY `IDX_PRODUCTSISACTIVE` (`productsIsActive`),
KEY `IDX_PRODUCTS_STATUS_2_PRODUCTSISACTIVE` (`products_status_2`,`productsIsActive`),
KEY `IDX_PRODUCTS_WITH_BATTERY` (`products_with_battery`),
KEY `IDX_SKU_DATECOUNTTIME` (`products_sku`(255),`DateCountTime`),
KEY `IDX_DEVUID_PRODUCTSKU` (`dev_uid`,`products_sku`(255)),
KEY `IDX_FATHERSKU` (`products_father_sku`),
KEY `purchase_user_id` (`purchase_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11111121 DEFAULT CHARSET=utf8; CREATE TRIGGER `insertProductTotal` AFTER INSERT ON `erp_products_data` FOR EACH ROW begin INSERT INTO erp_get_totals(get_totals_sku,get_totals_time,get_totals_storage_real,get_totals_storage_unreal,get_totals_reason,get_totals_year,get_totals_month,get_totals_add_count,user_id) VALUES (new.products_sku,NOW(),new.products_storage,new.products_storage,'Initialized...',YEAR(NOW()),MONTH(NOW()),1,30);
INSERT INTO erp_stock_detail(products_id, products_sku, stock_warehouse_id) VALUES (new.products_id, new.products_sku, new.product_warehouse_id); end; CREATE TRIGGER `updateProductWarehouse` AFTER UPDATE ON `erp_products_data` FOR EACH ROW begin IF new.product_warehouse_id <> old.product_warehouse_id THEN
UPDATE erp_stock_detail SET stock_warehouse_id = new.product_warehouse_id WHERE products_id = old.products_id;
END IF; end; CREATE TRIGGER `deleteProductStock` AFTER DELETE ON `erp_products_data` FOR EACH ROW begin DELETE FROM erp_stock_detail WHERE products_id = old.products_id; end;
erp_procurrent
CREATE TABLE `erp_procurement` (
`po_id` int(11) NOT NULL AUTO_INCREMENT,
`po_user` int(5) DEFAULT NULL,
`po_times` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`po_type` int(1) DEFAULT NULL,
`po_payment` int(1) DEFAULT NULL,
`po_shipping_fee` float DEFAULT '',
`po_paytime` timestamp NULL DEFAULT NULL,
`po_paytoken` varchar(100) DEFAULT NULL,
`po_isneedtoken` int(1) DEFAULT NULL,
`po_sp_company` varchar(500) DEFAULT NULL,
`po_sp_contact` varchar(100) DEFAULT NULL,
`po_sp_tel` varchar(200) DEFAULT NULL,
`po_status` int(1) DEFAULT NULL,
`po_remark` text,
`pay_is_end` int(1) NOT NULL DEFAULT '',
`arrival_is_end` int(1) NOT NULL DEFAULT '',
`has_new_pay` int(1) DEFAULT '',
`has_new_arrival` int(1) DEFAULT '',
`mustFollowing` int(1) NOT NULL DEFAULT '',
`externalOrderID` varchar(100) NOT NULL DEFAULT '',
`supplierID` int(11) NOT NULL DEFAULT '',
`shippingMethod` varchar(200) DEFAULT NULL,
`remarkForCheck` text COMMENT '审单参考',
`estimateArrivalDate` date DEFAULT NULL,
`purchaseType` int(2) NOT NULL DEFAULT '' COMMENT '1-普通，2-特采',
`labelPrinted` int(1) NOT NULL DEFAULT '',
`po_shenqingren` varchar(40) DEFAULT NULL,
`po_parent_id` int(11) DEFAULT '',
`po_tracknumber` varchar(64) DEFAULT NULL,
`printTimes` int(11) NOT NULL DEFAULT '' COMMENT '打印次数',
`procurement_warehouse_id` int(5) DEFAULT '' COMMENT '仓库ID',
`po_is_pay` int(1) NOT NULL DEFAULT '' COMMENT '是否付款:1未付款,2已付款',
`logisticsOrderNo` varchar(32) DEFAULT NULL,
`get_remark` varchar(200) DEFAULT NULL COMMENT '收货备注',
`arrival_apply_status` tinyint(1) DEFAULT '' COMMENT '异常收货处理状态',
`FirstSuppliers` varchar(100) NOT NULL COMMENT '一级供应商',
`one_level_supplier_id` int(10) NOT NULL DEFAULT '' COMMENT '一级供应商ID',
`succ_sum_payment` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '实际付款金额',
`auto_order_status` int(5) DEFAULT '' COMMENT '自动下单的状态，0 未自动下单， 1 自动下单过 ',
PRIMARY KEY (`po_id`),
KEY `IDX_POTIMES` (`po_times`),
KEY `IDX_HAS_NEW_ARRIVAL_STATUS` (`has_new_arrival`,`po_status`),
KEY `IDX_EXTERNALORDERID` (`externalOrderID`),
KEY `IDX_STATUS_PURCHASETYPE` (`po_status`,`purchaseType`),
KEY `IDX_ISEND_STATUS_TIME` (`arrival_is_end`,`po_status`,`po_times`),
KEY `IDX_PO_STATUS` (`po_status`),
KEY `IDX_warehouse_ID` (`procurement_warehouse_id`),
KEY `IDX_LOGISTICS_NO` (`logisticsOrderNo`),
KEY `po_parent_id` (`po_parent_id`) USING BTREE,
KEY `tracknumber` (`po_tracknumber`) USING BTREE,
KEY `po_user` (`po_user`) USING BTREE,
KEY `po_type` (`po_type`,`po_payment`,`arrival_is_end`,`has_new_arrival`,`mustFollowing`) USING BTREE,
KEY `first_suppliers` (`FirstSuppliers`) USING BTREE,
KEY `supplier_id` (`supplierID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=969652 DEFAULT CHARSET=utf8;