/// <summary> /// 获取应付总汇 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataSet GetReport(CommoditySalesQuery query) { DataSet ds = new DataSet(); DbQueryResult dbQueryResult = new DbQueryResult(); string text = this.BuildReconciliationQuery(query); if (!string.IsNullOrEmpty(text)) { string Sql = string.Format("select SupplierName as '供应商', ISNULL(sum(Amount),0) as '商品金额',ISNULL(SUM(RefundAmount),0) as '退款金额' from VW_AggregatePayable where 0=0 {0} group by SupplierName;", text); DbCommand sqlStringCommand = this.database.GetSqlStringCommand(Sql.ToString()); using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand)) { ds.Tables.Add(DataHelper.ConverDataReaderToDataTable(dataReader)); } string sql1 = string.Format(@"select ISNULL(SUM(RefundAmount),0) as '退款金额', ISNULL(SUM(OrderTotal),0) as '银行收款', ISNULL(SUM(Amount)-SUM(RefundAmount),0) as '商品总金额', ISNULL(sum(AdjustedFreight),0) as '代收快递运费' , ISNULL(SUM(OrderCounterFee)+SUM(RefunCounterFee),0) as '微信转账手续费' from VW_AggregatePayable where 0=0 {0} ;", text); sqlStringCommand = this.database.GetSqlStringCommand(sql1); using (IDataReader dataReader1 = this.database.ExecuteReader(sqlStringCommand)) { ds.Tables.Add(DataHelper.ConverDataReaderToDataTable(dataReader1)); } } return(ds); }
/// <summary> /// 商品销售明细 /// </summary> /// <param name="query"></param> /// <returns></returns> public DbQueryResult GetCommoditySalesDetail(CommoditySalesQuery query) { DbQueryResult result; if (null == query) { result = new DbQueryResult(); } else { DbQueryResult dbQueryResult = new DbQueryResult(); StringBuilder stringBuilder = new StringBuilder(); string text = this.BuildReconciliationQuery(query); stringBuilder.AppendFormat("SELECT TOP {0} *", query.PageSize); stringBuilder.AppendFormat(@" FROM ( select Row_Number() OVER (ORDER BY Quantity desc) 'JournalNumber',* from ( select b.SKU,b.SkuId,b.ItemDescription as 'ProductName',sum(b.Quantity) as 'Quantity' from dbo.Ecshop_Orders as a left join dbo.Ecshop_OrderItems as b on a.OrderId=b.OrderId where 1=1 {0} group by b.SKU,b.SkuId,b.ItemDescription ) as T ) as M ", text); if (query.PageIndex == 1) { stringBuilder.AppendFormat("{0} ORDER BY Quantity DESC", text); } else { stringBuilder.AppendFormat(" {0} and JournalNumber <{1} and JournalNumber>={2} ", text, query.PageIndex * query.PageSize, (query.PageIndex - 1) * query.PageSize); } if (query.IsCount) { stringBuilder.AppendFormat(@";select COUNT(1) from ( select b.SKU,b.SkuId,b.ItemDescription,sum(b.Quantity) as 'Quantity' from dbo.Ecshop_Orders as a left join dbo.Ecshop_OrderItems as b on a.OrderId=b.OrderId where 0=0 {0} group by b.SKU,b.SkuId,b.ItemDescription ) as T ", text); } DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString()); using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand)) { dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(dataReader); if (query.IsCount && dataReader.NextResult()) { dataReader.Read(); dbQueryResult.TotalRecords = dataReader.GetInt32(0); } } result = dbQueryResult; } return(result); }
/// <summary> /// 商品的访问和购买比例图 /// </summary> /// <param name="query"></param> /// <returns></returns> public DbQueryResult GetaccessPurchaseRatio(CommoditySalesQuery query) { DbQueryResult result; if (null == query) { result = new DbQueryResult(); } else { DbQueryResult dbQueryResult = new DbQueryResult(); StringBuilder stringBuilder = new StringBuilder(); string text = this.BuildReconciliationQuery(query); stringBuilder.AppendFormat(@"select top {0} * from (select Row_Number() OVER (ORDER BY ProductId desc) 'JournalNumber',ProductCode,ProductName,VistiCounts, isnull((select sum(Quantity) from Ecshop_OrderItems b where b.ProductId=a.ProductId),0) as 'Quantity' from dbo.Ecshop_Products as a ) as B where 0=0 ", query.PageSize); if (query.PageIndex == 1) { stringBuilder.AppendFormat("{0} ORDER BY ProductId DESC", text); } else { stringBuilder.AppendFormat("{0} and JournalNumber <{1} and JournalNumber>={2} ", text, query.PageIndex * query.PageSize, (query.PageIndex - 1) * query.PageSize); } if (query.IsCount) { stringBuilder.AppendFormat(";select count(1) as Total from Ecshop_Products where 0=0 {0} ", text); } DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString()); using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand)) { dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(dataReader); if (query.IsCount && dataReader.NextResult()) { dataReader.Read(); dbQueryResult.TotalRecords = dataReader.GetInt32(0); } } result = dbQueryResult; } return(result); }
private string BuildReconciliationQuery(CommoditySalesQuery query) { StringBuilder stringBuilder = new StringBuilder(); if (!string.IsNullOrEmpty(query.ProductName)) { stringBuilder.AppendFormat(" AND ProductName like '%{0}%'", query.ProductName); } if (!string.IsNullOrEmpty(query.ProductCode)) { stringBuilder.AppendFormat(" AND ProductCode like '%{0}%'", query.ProductCode); } if (query.StartDate.HasValue) { stringBuilder.AppendFormat(" AND PayDate >= '{0}'", Convert.ToDateTime(query.StartDate.Value).ToString("yyyy-MM-dd")); } if (query.EndDate.HasValue) { stringBuilder.AppendFormat(" AND PayDate <= '{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value)); } return(stringBuilder.ToString()); }