查看包裹出仓信息
SELECT order_customerorder.order_num AS '主单号',
order_parcel.parcel_num AS '包裹号',
order_parcel.tracking_num AS '快递号',
pms_product.name AS '产品名称',
CASE
WHEN order_parcel.is_weighing = 1 THEN '是'
ELSE '否'
END AS '入仓状态',
CASE
WHEN order_parcel.out_warehouse = 1 THEN '是'
ELSE '否'
END AS '出仓状态',
CASE
WHEN pms_productbasicrestriction.is_open_fba = 1 THEN '是'
ELSE '否'
END AS 'FBA仓校验'
FROM order_customerorder
JOIN
order_parcel ON order_customerorder.id = order_parcel.customer_order_id
JOIN
pms_product ON order_customerorder.product_id = pms_product.id
JOIN
pms_productbasicrestriction ON pms_product.id = pms_productbasicrestriction.product_id
WHERE order_customerorder.order_num = 'FX2400979'
AND pms_product.name = 'FBA'
AND order_parcel.del_flag = false
AND pms_product.del_flag = false
AND pms_productbasicrestriction.del_flag = false
AND pms_productbasicrestriction.type = 'C'
AND pms_productbasicrestriction.product_id = 74;导出转单号
SELECT company_company.name AS '客户名称',
order_customerorder.ref_num AS '客户单号',
order_customerorder.ref_num AS '客户订单号',
order_parcel.parcel_num AS '子单号',
order_parcel.tracking_num AS '转单号',
order_customerorder.order_num AS '内单号'
FROM order_parcel
join order_customerorder ON order_customerorder.id = order_parcel.customer_order_id
join company_company ON order_customerorder.customer_id = company_company.id
where order_parcel.del_flag = false;导出预录单
SELECT CASE
WHEN order_customerorder.order_status = 'DR' THEN '草稿'
WHEN order_customerorder.order_status = 'WO' THEN '等待作业'
WHEN order_customerorder.order_status = 'PDC' THEN '已预报'
WHEN order_customerorder.order_status = 'ITP' THEN '已拦截'
WHEN order_customerorder.order_status = 'PW' THEN '已部分入仓'
WHEN order_customerorder.order_status = 'AW' THEN '已全部入仓'
WHEN order_customerorder.order_status = 'CWED' THEN '已确认入仓数据'
WHEN order_customerorder.order_status = 'OW' THEN '已出国内仓'
WHEN order_customerorder.order_status = 'TF' THEN '转运'
WHEN order_customerorder.order_status = 'SF' THEN '已签收'
WHEN order_customerorder.order_status = 'FC' THEN '完成'
WHEN order_customerorder.order_status = 'VO' THEN '作废'
ELSE '未知状态' END AS '订单状态',
order_parcel.parcel_num AS '包裹号',
DATE_FORMAT(order_customerorder.create_date, '%Y-%m-%d') AS '预报时间', # 这是什么
order_parcelitem.item_name AS '预报品名',
company_company.short_name AS '客户代码',
company_company.name AS '客户名称',
order_customerorder.ref_num AS '客户单号',
order_customerorder.order_num AS '内单号',
order_customerorder.saler AS '业务员',
pms_product.name AS '产品渠道', -- 目前有没有配置了产品渠道的数据？
company_address.address_num AS '仓库代码',
order_customerorder.pre_carton AS '件数',
ROUND(order_parcel.parcel_weight, 2) AS '预报重量',
ROUND(order_parcel.parcel_volume, 2) AS '预报体积',
order_customerorder.buyer_postcode AS '收件人邮编'
FROM order_customerorder
JOIN company_company ON order_customerorder.customer_id = company_company.id
JOIN pms_product on order_customerorder.product_id = pms_product.id
JOIN order_parcel on order_parcel.customer_order_id = order_customerorder.id
JOIN order_parcelitem on order_parcel.id = order_parcelitem.parcel_num_id
LEFT JOIN company_address on order_customerorder.id = order_customerorder.receiver_id
where order_customerorder.del_flag = false
AND order_parcel.del_flag = false;导出订单
SELECT order_parcel.parcel_num AS '箱号/FBA编号',
CONCAT(order_parcel.actual_length, 'x', order_parcel.actual_width, 'x', order_parcel.actual_height) AS '材积CM(长x宽x高)',
order_parcel.actual_weight AS '重量KGS',
order_parcelitem.customs_code AS '海关编码HSCODE',
NULL AS 'SKU',
order_parcelitem.declared_nameCN AS '中文品名',
order_parcelitem.declared_nameEN AS '英文品名',
order_parcelitem.item_qty AS '数量',
order_parcelitem.sale_price AS '单价',
order_parcelitem.brand AS '品牌',
order_parcelitem.model AS '规格型号',
order_parcelitem.texture AS '材质',
order_parcelitem.use AS '用途',
order_parcelitem.item_picture AS '产品图片',
DATE_FORMAT(order_customerorder.create_date, '%Y-%m-%d') AS '创建时间'
FROM order_customerorder
JOIN order_parcel ON order_parcel.customer_order_id = order_customerorder.id
JOIN order_parcelitem ON order_parcel.id = order_parcelitem.parcel_num_id
where order_customerorder.del_flag = false
AND order_parcel.del_flag = false
ORDER BY order_parcel.parcel_num;预报清单
SELECT order_oceanorder.order_num AS '海运提单',
order_oceanorder.order_num AS '内部渠道',
company_company.address AS '目的仓',
order_customerorder.order_num AS '订单号',
order_parcel.parcel_num AS 'FBA订单号',
order_parcel.parcel_weight AS '重量',
order_parcel.parcel_volume AS '体积',
order_parcel.parcel_qty AS '箱数'
FROM order_oceanorder
JOIN order_customerorder ON order_oceanorder.id = order_customerorder.ocean_num_id
LEFT JOIN order_parcel ON order_customerorder.id = order_parcel.customer_order_id
LEFT JOIN pms_product ON order_customerorder.product_id = pms_product.id
LEFT JOIN company_company ON order_customerorder.company_name = company_company.id
WHERE order_customerorder.del_flag = false
AND order_customerorder.del_flag = false;报关清单
SELECT order_customerorder.order_num AS '海运提单',
order_parcel.parcel_num AS '子单号',
order_parcel.tracking_num AS '转单号',
order_parcelitem.declared_nameCN AS '中文品名',
order_parcelitem.declared_nameEN AS '英文品名',
order_parcelitem.customs_code AS 'HS_CODE',
order_parcelitem.texture AS '材质',
order_parcel.parcel_length AS '长',
order_parcel.parcel_width AS '高',
order_parcel.parcel_height AS '宽',
order_parcel.parcel_weight AS '重量',
order_parcel.parcel_volume AS '体积',
order_parcelitem.sale_price AS '单价'
FROM order_oceanorder
JOIN order_customerorder ON order_oceanorder.order_num = order_customerorder.ocean_number
JOIN order_parcel ON order_customerorder.id = order_parcel.customer_order_id
JOIN order_parcelitem ON order_parcel.id = order_parcelitem.parcel_num_id
where order_oceanorder.del_flag = false;普通报关
SELECT order_oceanorder.order_num AS 海运提单号,
order_oceanorder.container_no AS 柜号,
order_customerorder.order_num AS 服务商单号,
order_parcelitem.declared_nameCN AS 中文名称,
order_parcelitem.declared_nameEN AS 英文名称,
order_parcelitem.texture AS 材质,
SUM(order_parcelitem.item_qty) AS 总数量,
order_parcelitem.declared_price AS 单价,
SUM(order_parcel.parcel_qty) AS 箱数,
ROUND(SUM(order_parcel.actual_weight), 2) AS 总毛重, # 真实重量 和 真实体积
ROUND(SUM(order_parcel.actual_volume), 2) AS 体积,
MIN(order_parcel.parcel_picture) AS 货物图片 # 图片展示不了
FROM order_customerorder
JOIN order_parcel ON order_customerorder.id = order_parcel.customer_order_id
JOIN order_parcelitem ON order_parcel.id = order_parcelitem.parcel_num_id
JOIN order_oceanorder on order_customerorder.ocean_num_id = order_oceanorder.id
WHERE order_customerorder.del_flag = false
AND order_parcel.del_flag = false
GROUP BY order_customerorder.order_num,
order_oceanorder.order_num,
order_oceanorder.container_no,
order_parcelitem.declared_nameCN,
order_parcelitem.declared_nameEN,
order_parcelitem.texture,
order_parcelitem.declared_price;客户确认
SELECT company_company.short_name AS '客户简称',
order_customerorder.ref_num AS '客户单号',
order_customerorder.order_num AS '服务商单号',
pms_product.name AS '销售产品',
order_customerorder.carton AS '件数',
company_address.address_num AS '仓库代码',
order_customerorder.buyer_postcode AS '邮编',
order_parcel.actual_weight AS '实重(KG)',
order_parcel.actual_length AS '长(CM)',
order_parcel.actual_width AS '高(CM)',
order_parcel.actual_height AS '宽(CM)',
ROUND((order_parcel.actual_weight * order_parcel.actual_length * order_parcel.actual_height) / order_customerorder.charge_trans, 2) AS '建材积重', -- 这是什么字段？
order_customerorder.weight AS '计费重',
order_customerorder.volume AS '方数',
order_parcel.actual_length + 2 * (order_parcel.actual_width + order_parcel.actual_height) AS '周长',
CASE
WHEN info_charge.name = '偏远附加费' THEN '是'
ELSE '否'
END AS '偏远',
CASE
WHEN info_charge.name = '超长费' or info_charge.name = '超重费' THEN '是'
ELSE '否'
END AS '超长超重',
CASE
WHEN info_charge.name = '非FBA地址费' THEN '是'
ELSE '否'
END AS '私人地址',
order_customerorderchargein.charge_rate AS '单价',
NULL AS '附加',
NULL AS '备注'
FROM order_customerorder
LEFT JOIN company_company ON order_customerorder.customer_id = company_company.id
JOIN order_parcel ON order_parcel.customer_order_id = order_customerorder.id
JOIN pms_product ON pms_product.id = order_customerorder.product_id
LEFT JOIN company_address ON company_address.id = order_customerorder.receiver_id
JOIN order_parcelitem ON order_parcel.id = order_parcelitem.parcel_num_id
LEFT JOIN order_customerorderchargerecord on order_customerorderchargerecord.parcel_num = order_parcel.parcel_num and order_customerorderchargerecord.order_num = order_customerorder.order_num
LEFT JOIN order_customerorderchargein on order_customerorderchargerecord.charge_in_id = order_customerorderchargein.id
LEFT JOIN info_charge on order_customerorderchargein.charge_id = info_charge.id
WHERE order_customerorder.del_flag = false
AND order_parcel.del_flag = false
AND order_parcel.is_weighing = TRUE;仓库代码
存在笛卡尔积
件数
仓库代码
# 存在笛卡尔积
# 件数
# 仓库代码
SELECT company_company.short_name AS '客户简称',
order_customerorder.ref_num AS '客户单号',
order_customerorder.order_num AS '服务商单号',
pms_product.name AS '销售产品',
order_customerorder.carton AS '件数',
order_customerorder.buyer_address_num AS '仓库代码',
order_customerorder.buyer_postcode AS '邮编',
order_parcel.actual_weight AS '实重(KG)',
order_parcel.actual_length AS '长(CM)',
order_parcel.actual_width AS '高(CM)',
ROUND((order_parcel.actual_weight * order_parcel.actual_length * order_parcel.actual_height) / order_oceanorder.charge_trans, 2) AS '建材积重',
order_customerorder.weight AS '计费重',
order_customerorder.volume AS '方数',
order_parcel.actual_length + 2 * (order_parcel.actual_width + order_parcel.actual_height) AS '周长',
NULL AS '偏远',
NULL AS '超长超重',
NULL AS '私人地址',
pms_productrevenueversionline.price AS '单价',
NULL AS '附加',
NULL AS '备注'
FROM company_company
JOIN
order_customerorder ON order_customerorder.customer_id = company_company.id
JOIN
order_parcel ON order_parcel.customer_order_id = order_customerorder.id
JOIN
pms_product ON pms_product.id = order_customerorder.product_id
JOIN
order_oceanorder ON order_customerorder.ocean_num_id = order_oceanorder.id
LEFT JOIN -- Use LEFT JOIN for optional relationships
pms_productrevenueversion ON pms_productrevenueversion.product_id = pms_product.id
LEFT JOIN
pms_productrevenueversionline ON pms_productrevenueversionline.price_version_id = pms_productrevenueversion.id
LEFT JOIN -- Adjust this JOIN based on your actual relationship
order_parcelitem ON order_parcel.id = order_parcelitem.parcel_num_id
WHERE order_customerorder.del_flag = false
AND order_parcel.del_flag = false
ORDER BY order_parcel.parcel_num;打单数据
SELECT order_customerorder.ref_num AS '客户单号',
order_customerorder.order_num AS '服务商单号',
pms_product.name AS '销售产品',
order_customerorder.carton AS '件数',
company_address.address_num AS '仓库代码',
ROUND(order_parcel.actual_weight, 2) AS '实重(KG)',
ROUND(order_parcel.actual_length, 2) AS '长(CM)',
ROUND(order_parcel.actual_width, 2) AS '宽(CM)',
ROUND(order_parcel.actual_height, 2) AS '高(CM)',
'/' AS '偏远',
'/' AS '超长超重',
'/' AS '私人地址',
company_address.company_name AS '收件公司',
company_address.contact_name AS '收件名字',
company_address.address_one AS '地址',
company_address.country_code AS '国家',
company_address.postcode AS '邮编',
company_address.city_code AS '城市',
company_address.state_code AS '州缩写(二字码)',
company_address.contact_phone AS '电话',
order_parcelitem.declared_nameEN AS '英文品名'
FROM company_company
JOIN order_customerorder ON company_company.id = order_customerorder.customer_id
JOIN pms_product ON order_customerorder.product_id = pms_product.id
JOIN order_parcel ON order_customerorder.id = order_parcel.customer_order_id
JOIN order_parcelitem ON order_parcel.id = order_parcelitem.parcel_num_id
LEFT JOIN company_address ON order_customerorder.receiver_id = company_address.id
WHERE order_customerorder.del_flag = false
AND order_parcel.del_flag = false
AND order_parcel.is_weighing = TRUE;海运预报
WITH ranked_orders AS (SELECT DISTINCT order_oceanorder.order_num,
pms_product.name AS internal_channel,
# order_customerorder.buyer_name AS destination_warehouse,
company_address.address_num AS destination_warehouse,
order_customerorder.order_num AS mz_order_num,
order_customerorder.carton AS quantity,
order_parcel.actual_volume AS volume,
order_parcel.actual_weight AS weight,
SUBSTRING_INDEX(order_parcel.parcel_num, 'U', 1) AS shipment_id,
order_parcel.reference_id AS amazon_reference_id,
ROW_NUMBER() OVER (PARTITION BY order_oceanorder.order_num, pms_product.name, order_customerorder.buyer_address_num, order_customerorder.order_num, SUBSTRING_INDEX(order_parcel.parcel_num, 'U', 1) ORDER BY order_parcel.parcel_num) AS row_num
FROM order_oceanorder
JOIN order_customerorder ON order_oceanorder.id = order_customerorder.ocean_num_id
JOIN pms_product ON order_customerorder.product_id = pms_product.id
LEFT JOIN company_address on order_customerorder.receiver_id = company_address.id
JOIN order_parcel ON order_customerorder.id = order_parcel.customer_order_id
JOIN order_parcelitem ON order_parcel.id = order_parcelitem.parcel_num_id
WHERE order_oceanorder.del_flag = false
AND order_parcel.del_flag = false)
SELECT order_num AS '海运提单',
internal_channel AS '内部渠道(服务渠道)',
destination_warehouse AS '目的仓(仓库代码)',
mz_order_num AS '铭志MZ单号',
quantity AS '箱数',
volume AS '体积',
weight AS '重量',
shipment_id AS 'Shipment ID（FBA号）',
amazon_reference_id AS 'Amazon Reference ID（PO号）',
NULL AS '备注(颜色区分)',
NULL AS '最晚时效'
FROM ranked_orders
WHERE row_num = 1;装箱清单
SELECT DISTINCT company_company.short_name AS '客户代码',
order_customerorder.arrival_date AS '到货时间',
order_customerorder.order_num AS '服务商单号',
order_customerorder.order_num AS '配载件数-暂无',
order_customerorder.pre_carton AS '件数',
order_customerorder.pre_volume AS '出货立方',
pms_product.name AS '销售产品',
company_address.address_num AS '仓库代码',
DATE_FORMAT(order_oceanorder.update_date, '%Y-%m-%d') AS '配载时间',
order_parcelitem.declared_nameCN AS '品名'
FROM order_customerorder
LEFT JOIN company_company ON order_customerorder.customer_id = company_company.id
LEFT JOIN pms_product ON order_customerorder.product_id = pms_product.id
LEFT JOIN order_oceanorder on order_customerorder.ocean_num_id = order_oceanorder.id
LEFT JOIN company_address ON order_customerorder.receiver_id = company_address.id
LEFT JOIN order_parcel on order_parcel.customer_order_id = order_customerorder.id
LEFT JOIN order_parcelitem on order_parcelitem.parcel_num_id = order_parcel.id
where order_customerorder.del_flag = false;
评论区