对账

查找邮费差价订单
SELECT order_bn,goods_name FROM sdb_taobaoapi_statement where order_bn in('')

查找 退款订单
SELECT order_bn,pay_status FROM sdb_ome_orders WHERE order_bn in ('')

订单状态
4 =>部份退款 5=>全额退款 6=>退款申请中 7=>退款中

查找时间段全额退款订单
SELECT s.order_bn, s.paytime, x.bn, x.nums
FROM sdb_ome_orders s, sdb_ome_order_items x
WHERE s.process_status = 'cancel'
AND s.order_id = x.order_id
AND paytime > '1601481600'
AND paytime < '1609430399'

查找手机号 时间段订单
SELECT *
FROM sdb_ome_orders
WHERE ship_mobile LIKE '15801111270'
AND paytime > '1590969600'
AND paytime < '1617148800'

批量查找订单
select * from sdb_ome_orders where order_bn in
('577176165650372198','577193189869980892');

批量查找订单明细
SELECT * FROM sdb_taobaoapi_statement
WHERE order_bn in ('');

查找姓名为 谁谁谁的明细
SELECT * FROM sdb_taobaoapi_statement
WHERE order_bn in (SELECT order_bn
FROM sdb_ome_orders
WHERE ship_name = '吴晓亮');

上传文件tonghe 命令
/usr/local/php/bin/php /data/httpd/oms/app/taobaoapi/crontab/sendSale.php

如果 订单为下期结算 想变为 对账成功
现在看对账日期 例如 2020.03.05-2020.03.10
找到表sdb_taobaoapi_statement 查看occurrence_time字段的时间 是否正确 如果时间未在数据导出内
需要去sdb_taobaoapi_alipay中 把对应的付款订单 occurrence_time字段的时间 替换到
sdb_taobaoapi_statement 表中occurrence_time字段的时间

如果要把订单的状态改为下期表结算
把表sdb_taobaoapi_statement 中的数据情空即可

查找哪天订单没有执行
SELECT FROM_UNIXTIME( occurrence_time, "%Y-%m-%d" )
FROM sdb_taobaoapi_alipay
WHERE occurrence_time > UNIX_TIMESTAMP( '2018-10-25' )
GROUP BY FROM_UNIXTIME( occurrence_time, "%Y-%m-%d" )
LIMIT 0 , 30

mysql -hrm-vy1zh276k0incxu01.mysql.rds.aliyuncs.com -ujusrzw4savmn -pIoxZ983ldi
Mysql 查看正在执行进程的命令
SELECT * FROM information_schema.PROCESSLIST;
结束进程 kill 加id号

后台导出的订单数量缺少情况
进入到服务器 /data/httpd/oms/alipay_sdk
编辑downFile.php文件

对账失败小于40元订单
SELECT s.order_bn,s.paytime,p.name,p.bn, p.nums
FROM sdb_ome_orders s, sdb_taobaoapi_statement x,sdb_ome_order_items p
WHERE x.result_stat = 'true'
AND s.order_bn = x.order_bn
AND s.order_id = p.order_id
AND x.order_amount < '40'
AND s.paytime > '1617206400'
AND s.paytime < '1625068799'

明细个项金额相加计算
SELECT order_bn, SUM( order_amount ), actual_collection,result_stat
FROM sdb_taobaoapi_statement
WHERE order_bn
IN ('')
GROUP BY order_bn

退货对账明细没有数据 或丢失数据

文件路径：/data/httpd/oms/app/taobaoapi/lib/auto/product.php
执行脚本：/usr/local/php/bin/php /data/httpd/oms/app/taobaoapi/crontab/syncR.php
检查时间 是否订单已满

校验收款数据
SELECT DISTINCT order_bn,FROM_UNIXTIME( b.occurrence_time, "%Y-%m-%d" )
FROM sdb_taobaoapi_statement a,sdb_taobaoapi_alipay b
WHERE b.occurrence_time > UNIX_TIMESTAMP( '2022-04-01' )
AND a.order_bn = b.order_sn
AND b.jylx = '交易付款'
AND b.order_sn
IN ('')