public List <ReportGoodsRank> GetStatisticsBySql(string sql)
{
List <ReportGoodsRank> ranks = null;
using (MySqlDataReader rdr = Tools.MySqlHelper.ExecuteReader(Tools.MySqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql))
{
ranks = new List <ReportGoodsRank>();
while (rdr.Read())
{
ReportGoodsRank rank = new ReportGoodsRank();
if (sql.IndexOf("year") > -1)
{
rank.Id = rdr["year"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["year"]);
rank.Month = rdr["month"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["month"]);
}
else if (sql.IndexOf("day") > -1)
{
rank.Month = rdr["day"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["day"]);
}
rank.GoodsName = rdr["name"] == DBNull.Value ? "" : rdr["name"].ToString();
rank.Count = rdr["count"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["count"]);
rank.Price = rdr["money"] == DBNull.Value ? 0 : Convert.ToDecimal(rdr["money"]);
ranks.Add(rank);
}
}
return(ranks);
}
/// <summary>
/// 累计某类商品销售额度
/// </summary>
/// <param name="cat">商品分类</param>
/// <param name="item">被统计数据项</param>
private void GetValue(GoodsCategory cat, ReportGoodsRank item)
{
for (int i = 0; i < lastRow.Cells.Count; i++)
{
if (Convert.ToInt32(lastRow.Cells[i].Tag) == cat.Id)
{
lastRow.Cells[i].Value = Convert.ToDecimal(lastRow.Cells[i].Value) + (cat.Name.Trim() == "刷卡" ? item.Count : item.Price);
break;
}
}
}
public List <ReportGoodsRank> GetGoodsRank(DateTime begin, DateTime end)
{
List <ReportGoodsRank> ranks = null;
String sql = String.Format("SELECT goods.category_id, gc.`name`, COUNT(*) AS count, SUM(money) AS price FROM sales_records AS sr LEFT JOIN goods ON sr.goods_id = goods.id LEFT JOIN goods_categories AS gc ON gc.id = goods.category_id WHERE sr.created_at BETWEEN {0} AND {1} GROUP BY gc.id, gc.`name`", TimeStamp.ConvertDateTimeInt(begin), TimeStamp.ConvertDateTimeInt(end));
using (MySqlDataReader rdr = Tools.MySqlHelper.ExecuteReader(Tools.MySqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql))
{
ranks = new List <ReportGoodsRank>();
while (rdr.Read())
{
ReportGoodsRank rank = new ReportGoodsRank();
rank.Id = rdr["category_id"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["category_id"]);
rank.GoodsName = rdr["name"] == DBNull.Value ? "" : rdr["name"].ToString();
rank.Count = rdr["count"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["count"]);
rank.Price = rdr["price"] == DBNull.Value ? 0 : Convert.ToDecimal(rdr["price"]);
ranks.Add(rank);
}
}
return(ranks);
}
