Magento ver. 1.5.1.0—根据系统当天时间,Order导出最近俩天的ERP需求数据SQL语句
来源:互联网 发布:我的世界0.15.4枪械js 编辑:程序博客网 时间:2024/05/18 05:54
SELECT
DATE_FORMAT(OD.`created_at`,'%Y-%m-%d') as Update_Time,
OAD1.`customer_id` as 客户编号,
CONCAT(OAD1.`firstname`,' ',OAD1.`lastname`) as Shipping_name,
OAD1.`telephone` as 联系电话,
OD.`customer_email` as Email,
OAD1.`postcode` as 邮编,
OAD1.`street` as 街道,
OAD1.`city` as 城市,
OAD1.`region` as 省,
OAD1.`company` as 公司,
CONCAT(OAD1.`street`,',',OAD1.`city`,',',OAD1.`region`,',',OAD1.`postcode`,',',OAD1.`company`) as 地址2,
CO.`country_name` as 国家,
OD.`shipping_method` as 付款方式,
PRO.`sku` as 产品,
TRUNCATE(PRO.`base_row_total`/PRO.`base_price`,0) as 数量,
OD.`order_currency_code` as 货币,
TRUNCATE(PRO.`base_price`,2) as 销售单价,
TRUNCATE(PRO.`base_row_total`,2) as Total_Price,
TRUNCATE(OD.`base_shipping_amount`,2) as 运费,
OD.`increment_id` as 平台交易ID,
PRO.`product_id` as ItemNumber,
PRO.`name` as ItemTitle,
OD.`store_id`,
OM.`message` as 备注
FROM `sales_flat_order` as OD
LEFT JOIN `sales_flat_order_item` as PRO
ON OD.`entity_id`=PRO.`order_id`
LEFT JOIN `sales_flat_order_address` as OAD1
ON OD.`shipping_address_id`=OAD1.`entity_id`
LEFT JOIN `directory_country` as CO
ON OAD1.`country_id`=CO.`country_id`
LEFT JOIN `gift_message` as OM
ON OD.`gift_message_id`=OM.`gift_message_id`
LEFT JOIN `sales_flat_order_status_history` as OS
ON OD.`entity_id`= OS.`parent_id` AND OS.`status` = 'complete'
WHERE DATE_FORMAT(OD.`created_at`,'%Y-%m-%d')=current_date
OR DATE_FORMAT(OD.`created_at`,'%Y-%m-%d')=DATE_FORMAT(adddate(now(),-1), '%Y-%m-%d')
group by PRO.`item_id`
DATE_FORMAT(OD.`created_at`,'%Y-%m-%d') as Update_Time,
OAD1.`customer_id` as 客户编号,
CONCAT(OAD1.`firstname`,' ',OAD1.`lastname`) as Shipping_name,
OAD1.`telephone` as 联系电话,
OD.`customer_email` as Email,
OAD1.`postcode` as 邮编,
OAD1.`street` as 街道,
OAD1.`city` as 城市,
OAD1.`region` as 省,
OAD1.`company` as 公司,
CONCAT(OAD1.`street`,',',OAD1.`city`,',',OAD1.`region`,',',OAD1.`postcode`,',',OAD1.`company`) as 地址2,
CO.`country_name` as 国家,
OD.`shipping_method` as 付款方式,
PRO.`sku` as 产品,
TRUNCATE(PRO.`base_row_total`/PRO.`base_price`,0) as 数量,
OD.`order_currency_code` as 货币,
TRUNCATE(PRO.`base_price`,2) as 销售单价,
TRUNCATE(PRO.`base_row_total`,2) as Total_Price,
TRUNCATE(OD.`base_shipping_amount`,2) as 运费,
OD.`increment_id` as 平台交易ID,
PRO.`product_id` as ItemNumber,
PRO.`name` as ItemTitle,
OD.`store_id`,
OM.`message` as 备注
FROM `sales_flat_order` as OD
LEFT JOIN `sales_flat_order_item` as PRO
ON OD.`entity_id`=PRO.`order_id`
LEFT JOIN `sales_flat_order_address` as OAD1
ON OD.`shipping_address_id`=OAD1.`entity_id`
LEFT JOIN `directory_country` as CO
ON OAD1.`country_id`=CO.`country_id`
LEFT JOIN `gift_message` as OM
ON OD.`gift_message_id`=OM.`gift_message_id`
LEFT JOIN `sales_flat_order_status_history` as OS
ON OD.`entity_id`= OS.`parent_id` AND OS.`status` = 'complete'
WHERE DATE_FORMAT(OD.`created_at`,'%Y-%m-%d')=current_date
OR DATE_FORMAT(OD.`created_at`,'%Y-%m-%d')=DATE_FORMAT(adddate(now(),-1), '%Y-%m-%d')
group by PRO.`item_id`
- Magento ver. 1.5.1.0—根据系统当天时间,Order导出最近俩天的ERP需求数据SQL语句
- Magento 1.5版本,导出order详细订单的SQL语句
- Magento系统:导出完整的Order数据
- Magento 1.5版本,导出order详细订单的SQL语句 、 取消订单事件 同时删除多个ID foreach
- sql server2008 查询当天,最近三天,本周,本月,最近一个月,本季度的数据的sql语句
- Magento的系统需求
- Magento的系统需求
- sql根据时间查询当天-昨天记录
- SQL语句根据具体时间查询数据
- android的sqlite获取当天数据的sql语句
- android的sqlite获取当天数据的sql语句
- 使用sql 语句查询当月/当天/当周的数据
- ACCESS查询当天数据的SQL语句(DateDiff函数)
- ACCESS查询当天数据的SQL语句(DateDiff函数)
- mysql查询当天、本周、最近xx天、本月 的数据
- Oracle数据库 查询时间在当天内的数据sql
- Magento 根据SKU查询订单信息的SQL语句
- magento 导出产品数据sql
- 第一天, C++的初步知识
- HTML中
- 10308 - Roads in the North
- 中文字符串长度截取的方法
- 修改maven本地仓库路径
- Magento ver. 1.5.1.0—根据系统当天时间,Order导出最近俩天的ERP需求数据SQL语句
- CentOS5.5无法使用ifconfig命令
- 根据文件路径获得其所在文件夹,并删除该文件夹下后缀名为.tmp的所有文件
- Linux socket编程(二)之select()
- Design Pattern_Command(命令行模式)
- 二叉树的遍历
- 清炖鲤鱼
- Cookie实现会话跟踪
- standard.jar和jstl.jar的下载地址