Custom 报告书in Magento with Clean_SqlReports

2014年9月24日发布 in 发展历程

撰写人：埃里克·汉森（Erik Hansen）

避免编码错误

2014年8月25日

Magento 翻译，步骤零：必须翻译什么？

2014年10月24日

Magento 随附了许多内置报告，涵盖了销售，税收，客户，产品，废弃的购物车和评论等领域。但是，许多商家的报表需求超出了Magento所能提供的范围。对于某些商人，像Aheadworks这样的第三方扩展’ 进阶报告模块将解决问题。但是，如果现有模块没有’不合算，那你’我需要构建一个自定义报告。传统上，Magento开发人员会按照与Magento相同的模式在Magento中生成报告’的本机报告。但是，这种方法可能会花费一些时间，并且通常会抑制成本。

为了使构建自定义报告变得更加容易，卡伦·乔丹（Kalen Jordan）构建了一个名为Clean_SqlReports的模块，该模块允许您执行MySQL查询并将其自动转换为Magento报告。在Magento Imagine 2014 Hackathon期间，Kalen与其他一些增强该模块以添加对Google Charts API的支持，这意味着您可以构建一些真正的可视化报告。在这篇博客中，我’我将演示如何使用此扩展程序构建几种不同类型的报告（一些示例受模块启发自述文件）：

生命周期前500名客户 (Plain Table)
订单状态 (Pie Chart)
订单状态 by Month (Stacked 柱形图)

模块设置

首先，您需要安装模块： //github.com/kalenjordan/custom-reports 您可以通过安装莫德曼 , 作曲家，或下载该模块的ZIP并将其复制到您的Magento安装中。安装模块后，登录到管理员。由于此模块运行通过管理员输入的MySQL代码，因此有权访问添加/编辑报告的人可能会在网站上造成严重破坏，如果他们知道要输入的MySQL正确（认为TRUNCATE或DROP）。要解决此问题，请转到“System > Permissions > Roles”。您应该为您所拥有的用户至少扮演一个角色’希望有权添加/编辑报告。单击每个角色，然后取消选中“添加/编辑报告（危险）” option:

报告类型

的module has two primary forms of reports:

普通表–这种报告类型类似于您通过Magento获得的报告类型’的本机报告。你赢了’不能执行按日期范围过滤和更改报表分组方式的操作，但是业务用户可以将报表导出为CSV或Excel，并在电子表格应用程序中进行其他过滤。
Google Charts API–此报告类型使您几乎可以使用任何Google图表类型。查看这些例子。请注意，您需要编写MySQL查询以Google图表所需的格式返回数据。

报告＃1– 生命周期前500名客户

现在你’设置此扩展程序的权限，让’创建我们的第一个报告。对于这个例子，我们’重新吸引前500名客户，并显示他们的终生订单价值，订单总数和最后订购日期。去“Reports > 特别报道”. Click “Add Report”。输入标题“生命周期前500名客户”. For the “SQL”, enter this query:

 选择  
    -四舍五入到小数点后两位并加$
     康卡特 （'$'，FORMAT（SUM（sales_flat_order.`grand_total`），2））AS'终身销售'，
     计数 （sales_flat_order.entity_id）AS“订单”，
    客户实体.email  如 “电子邮件”，
     最大 （sales_flat_order.created_at）AS“最近的订购日期”
来自`customer_entity`
左联接sales_flat_order开启customer_entity.entity_id = sales_flat_order.customer_id
GROUP  通过  客户实体.entity_id
按总和排序（sales_flat_order.`grand_total`）DESC
LIMIT 500

选择

-四舍五入到小数点后两位并加$

康卡特 ( '$' , 格式 ( 和 (sales_flat_order.`累计`), 2)) 如 “终身销售”,

计数 (sales_flat_order.实体编号) 如 '订单' ,

客户实体. 电子邮件如 '电子邮件' ,

最大 (sales_flat_order.created_at) 如 “最近的订单日期”

从 `客户实体`

剩下加入 sales_flat_order 上客户实体.实体编号 = sales_flat_order.顾客ID

组通过客户实体.实体编号

订购通过和 (sales_flat_order.`累计`) 数据中心

限制 500

现在，保存该报告并通过单击该行来运行它。您应该会看到以下内容：

报告2– 订单状态

创建标题为的新报告“Order Status”. Select “Pie Chart” from the “Output Type” field. Enter the following 的SQL:

 选择  
    sales_order_status.label  如 '状态'，
     计数 （sales_flat_order.entity_id）AS“订单”
来自sales_flat_order
左联接sales_order_status  上  sales_flat_order.status = sales_order_status.status
GROUP  通过  sales_flat_order.status
按计数排序（sales_flat_order.entity_id）DESC

选择

sales_order_status. 标签如 '状态' ,

计数 (sales_flat_order.实体编号) 如 '订单'

从 sales_flat_order

剩下加入 sales_order_status 上 sales_flat_order. 状态 = sales_order_status. 状态

组通过 sales_flat_order. 状态

订购通过计数 (sales_flat_order.实体编号) 数据中心

的“Chart Configuration”字段必须是JSON对象，并且对应于 “配置选项” section of the 饼形图 page. 在此输入值“Chart Configuration” field:

{
    高度：900，
    宽度：1100，
    标题：“订单状态”，
}

{

高度 : 900,

宽度 : 1100,

标题 : '订单状态',

}

现在，当您运行报表时，您应该会看到类似以下内容（此示例使用的是非本地状态标签）：

报告＃3– 订单状态 by Month

建立一个单独的按月对数据进行分组的MySQL查询有点麻烦，因为您必须“hard code”您想要的列已包含在报告中。在我们的示例中，我们’re going to 硬编码 the most common order 状态 es as columns 在 our select statement. While it is possible to 根据状态动态创建列，这不在本文的讨论范围之内。创建标题为的新报告“每个订单每月的平均产品”, select the “Column Chart” 输出类型 and then enter this 的SQL:

 选择 
    -“年-月”
     康卡特 （YEAR（sales_flat_order.created_at），'-'，MONTHNAME（sales_flat_order.created_at））AS'Month'，
     和 （IF（`status` ='已取消'，1，0））AS'已取消'，
     和 （IF（`status` ='closed'，1，0））AS'Closed'，
     和 （IF（`status` ='complete'，1，0））AS'完成'，
    -自定义状态
     和 （IF（`status` ='complete_partially_shipped'，1，0））AS'部分发货'，
     和 （IF（`status` ='processing'，1，0））AS'正在处理'，
    -自定义状态
     和 （IF（`status` ='已发货'，1，0））AS'已发货'
来自sales_flat_order
GROUP  通过   月 （sales_flat_order.created_at）

选择

-“年-月”

康卡特 ( 年 (sales_flat_order.created_at), '-' , 月 NAME(sales_flat_order.created_at)) 如 '月' ,

和 ( 如果 (` 状态 ` = '取消', 1, 0)) 如 '取消',

和 ( 如果 (` 状态 ` = '关闭' , 1, 0)) 如 '关闭' ,

和 ( 如果 (` 状态 ` = '完成', 1, 0)) 如 '完成',

-自定义状态

和 ( 如果 (` 状态 ` = 'complete_partially_shipped', 1, 0)) 如 '部分发货',

和 ( 如果 (` 状态 ` = “处理”, 1, 0)) 如 “处理中”,

-自定义状态

和 ( 如果 (` 状态 ` = “发货” , 1, 0)) 如 “已发货”

从 sales_flat_order

组通过月 (sales_flat_order.created_at)

在此输入值“Chart Configuration” field:

{
    isStacked：true，//重要，因为这会将列变成堆积的图表
    高度：600，
    宽度：1400，
    标题：“按月订购状态”，
     vAxis：{ 
         标题 ：'订单数'
    }
}

{

isStacked：true，//重要，因为这会将列变成堆积的图表

height: 600,

width: 1400,

标题：“按月订购状态”，

vAxis: {

标题 : '订单数量'

}

现在，当您运行报表时，您应该会看到类似以下内容（此示例使用的是非本地状态标签）：

注意：Magento使用以下命令将所有记录保存在数据库中世界标准时间，因此当您基于日期运行报告时，它将基于UTC而非本地时区进行报告。 Magento ’的本机报告说明了这一调整，但是原始的MySQL查询却没有。但是，有一种方法可以解决此问题：使用DATE_ADD函数调整您的时区。假设您处于中部时间（这是夏令时），那么您就是UTC–5.因此，用此替换上面查询中的GROUP 通过语句，请注意，您的报告现在说明了时区差异：

 组   通过   月 （DATE_ADD（sales_flat_order.created_at，INTERVAL -5  小时 ））

1	组通过月 ( DATE_ADD (sales_flat_order.created_at, 间隔 -5 小时 ))

结论

现在你’我已经看过您可以使用Clean_SqlReports模块执行的一些操作，然后创建一些报告！

13 评论 s

周杰伦说：

2017年3月10日，下午3:12

刚刚将此模块上传到了我们的Magento版本。 1.9.2.2，我看到报告但无法编辑或添加任何新报告吗？我去了System->Permissions->角色，并确保我的用户角色在下拉菜单中选择了全部，以授予所有访问权限。单击“保存”，然后注销并重新登录。当我将鼠标移到“报告”上时，>特殊报告没有添加/编辑选项。我还查看了vag / log system.log和exceptions.log，它们没有错误。
建议我可以在其中编辑代码以仅允许所有人吗？

回复
- 丹尼尔·奇蒂瓦（Daniel Chitiva）说：
  
  2015年8月25日，下午3:26
  
  你好
  
  如何在下面显示报告表和图表？非常感谢。
  
  回复
  - 埃里克·汉森（Erik Hansen）说：
    
    2015年8月26日下午2:43
    
    @丹尼尔–当前版本的扩展程序无法做到这一点，但是请随意分叉扩展程序，添加该功能并提交添加了该功能的请求请求。
    
    回复
- 布拉德说：
  
  2015年9月15日，上午8:09
  
  Sweet module, 上 ly issue I get is that 上 most 的SQL I try and save I get the error – You don’t有权访问此服务器上的/index.php/admin/adminhtml_report/save/key/dce6120d0975b630100a06733cdc5437/
  
  它为N’t every 的SQL statement and all run fine 在 PHP Admin or if I put them 在 to the DB direct. Any ideas ?
  
  回复
  - 埃里克·汉森（Erik Hansen）说：
    
    2015年9月15日，下午2:21
    
    First, I would try to log out and then log back 在 . Second, check to make sure your admin user has permissions to save reports. Viewing reports and saving reports are two different permissions. Go to 系统> Permissions >角色，单击用户的角色，然后检查您的角色是否具有“All”权限，或*已选择*报告保存选项的个别权限。
    
    回复
    - 布拉德说：
      
      2015年9月17日，上午5:50
      
      它是管理员用户，具有对ALL的资源访问权限。
      
      It is not 上 all 的SQL if I do something dumb like select * from table it will work but add a where clause no dice.
      
      如果我尝试再次使用您的样品，请不要掷骰子。我可以将其粘贴到DB表中并使其正常工作，但不能通过UI。
      
      回复
      - 埃里克·汉森（Erik Hansen）说：
        
        2015年9月17日，下午2:05
        
        @布拉德–尝试在MySQL语句的末尾添加分号。
      - 布拉德说：
        
        2015年9月17日，晚上8:16
        
        不，一点都没有改变。
        
        这是查询，它在模块表上，但应该可以给您一个提示
        
        选择和（qty）AS QTY，sku 如 SKU，Product_name 如 Product_Name，供应商名称AS Supplier_Name，OrderPlaced
        来自mage_supplier_dropship_items
        WHERE OrderPlaced =‘False’
        组通过 SKU，产品名称，供应商名称；
      - 布拉德说：
        
        2015年9月17日，晚上8:26
        
        如果我删除where子句，它可以正常工作
      - 埃里克·汉森（Erik Hansen）说：
        
        2015年9月18日下午3:29
        
        @布拉德– Ok, well I’我很高兴您弄清楚了为什么该报告无法正常工作。一世’我不确定如何使其与WHERE子句一起使用，所以您’如果您需要使其正常运行，则必须进行深入研究。
- 肯恩说：
  
  一月31，2018，1:07下午
  
  您需要点击“Special Reports”菜单链接本身，它将带您进入可以在其中找到报告的网格。
  
  如果您添加新报告，它将赢得’t立即显示在网格上，您需要注销并再次登录才能看到它。
  
  回复
拉斐尔·科雷阿·戈麦斯（RafaelCorrêaGomes）说：

十一月6，2017在4:08下午

天哪，这篇文章太棒了，谢谢分享！

回复
比克拉克说：

二月5，2019在8:10上午

你好 this is a cool idea! I was wondering if the module has anything 在 place to make sure the queries are read-only. It would be awesome if 的SQL queries like INSERT, DELETE, etc were filtered out.

回复