/// <summary> /// 根据订单id或者出入库id获取订单或库存明细商品 /// </summary> /// <param name="stockAndOrderIds"></param> /// <returns></returns> /// zal 2016-07-20 public IList <CompanyGrossProfitRecordDetailChildInfo> GetCompanyGrossProfitRecordDetailChildByStockAndOrderIds(List <Guid> stockAndOrderIds) { IList <CompanyGrossProfitRecordDetailChildInfo> companyGrossProfitRecordDetailChildList = new List <CompanyGrossProfitRecordDetailChildInfo>(); StringBuilder builder = new StringBuilder(); builder.Append(@" SELECT A.[GoodsId],A.[Quantity] FROM [CompanyGrossProfitRecordDetailChild] A WITH(NOLOCK) INNER JOIN [CompanyGrossProfitRecordDetail] B WITH(NOLOCK) ON A.[StockAndOrderId]=B.[StockAndOrderId] AND B.State=0 "); string stockAndOrderIdsStr = "'" + string.Join(",", stockAndOrderIds.ToArray()) + "'"; builder.Append((string.IsNullOrWhiteSpace(stockAndOrderIdsStr) ? "" : string.Format(" AND EXISTS(select id as StockAndOrderId from splitToTable({0},',') #temp WHERE #temp.id=B.[StockAndOrderId]) ", stockAndOrderIdsStr))); using (var connection = Databases.GetSqlConnection(GlobalConfig.ERP_REPORT_DB_NAME, Transaction.Current == null)) { IDataReader rdr = null; try { connection.Open(); var command = new SqlCommand(builder.ToString(), connection) { CommandTimeout = 600 }; rdr = command.ExecuteReader(); while (rdr.Read()) { var goodsOrderDetailInfo = new CompanyGrossProfitRecordDetailChildInfo { GoodsId = rdr["GoodsId"] == DBNull.Value ? Guid.Empty : new Guid(rdr["GoodsId"].ToString()), Quantity = rdr["Quantity"] == DBNull.Value ? 0 : int.Parse(rdr["Quantity"].ToString()) }; companyGrossProfitRecordDetailChildList.Add(goodsOrderDetailInfo); } } catch (Exception ex) { throw new Exception("获取订单或者库存明细商品数据异常", ex); } finally { connection.Close(); if (rdr != null) { rdr.Close(); } } } return(companyGrossProfitRecordDetailChildList); }
/// <summary> /// 获取指定时间内的订单或库存明细商品 /// </summary> /// <param name="startTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <param name="saleFilialeId">销售公司</param> /// <param name="salePlatformIds">销售平台</param> /// <param name="orderTypes">订单类型(0:网络发货订单;1:门店采购订单;2:帮门店发货订单;)</param> /// <param name="type">0:完成时间超过一个自然月或一个自然月以上的数据;1:完成时间未超过一个自然月或一个自然月以上的数据</param> /// <returns></returns> /// zal 2016-07-20 public IList <CompanyGrossProfitRecordDetailChildInfo> GetCompanyGrossProfitRecordDetailChild(DateTime startTime, DateTime endTime, Guid saleFilialeId, string salePlatformIds, string orderTypes, int type) { IList <CompanyGrossProfitRecordDetailChildInfo> companyGrossProfitRecordDetailChildList = new List <CompanyGrossProfitRecordDetailChildInfo>(); string salePlatformIdStr = string.Empty, orderTypeStr = string.Empty; StringBuilder builder = new StringBuilder(); builder.Append(@" SELECT A.[GoodsId],SUM(A.[Quantity]) AS Quantity" + (type.Equals(1) ? "" : ",B.[OrderTime]") + @" FROM [CompanyGrossProfitRecordDetailChild] A WITH(NOLOCK) INNER JOIN [CompanyGrossProfitRecordDetail] B WITH(NOLOCK) ON A.[StockAndOrderId]=B.[StockAndOrderId] AND B.State=0 "); if (type.Equals(1)) { if (startTime != DateTime.MinValue && endTime != DateTime.MinValue) { builder.AppendFormat(" AND B.OrderTime BETWEEN '{0}' AND '{1}' ", startTime, endTime); } } else { if (startTime != DateTime.MinValue) { builder.AppendFormat(" AND CONVERT(VARCHAR(7),B.OrderTime,120)+'-01'='{0}' ", startTime); } } if (!string.IsNullOrEmpty(salePlatformIds)) { salePlatformIdStr = salePlatformIds.Split(',').Aggregate(salePlatformIdStr, (current, item) => current + string.Format(",'{0}'", item)).Substring(1); builder.AppendFormat(" AND B.[SalePlatformId] IN({0}) ", salePlatformIdStr); } if (!string.IsNullOrEmpty(orderTypes)) { orderTypeStr = orderTypes.Split(',').Aggregate(orderTypeStr, (current, item) => current + string.Format(",'{0}'", item)).Substring(1); builder.AppendFormat(" AND B.[OrderType] IN({0}) ", orderTypeStr); } if (saleFilialeId != Guid.Empty) { builder.AppendFormat(" AND B.[SaleFilialeId] = '{0}' ", saleFilialeId); } builder.Append(" Group BY A.[GoodsId]" + (type.Equals(1) ? "" : ",B.[OrderTime]")); using (var connection = Databases.GetSqlConnection(GlobalConfig.ERP_REPORT_DB_NAME, Transaction.Current == null)) { IDataReader rdr = null; try { connection.Open(); var command = new SqlCommand(string.Format(builder.ToString(), startTime, endTime), connection) { CommandTimeout = 600 }; rdr = command.ExecuteReader(); while (rdr.Read()) { var goodsOrderDetailInfo = new CompanyGrossProfitRecordDetailChildInfo { GoodsId = rdr["GoodsId"] == DBNull.Value ? Guid.Empty : new Guid(rdr["GoodsId"].ToString()), Quantity = rdr["Quantity"] == DBNull.Value ? 0 : int.Parse(rdr["Quantity"].ToString()), OrderTime = type.Equals(1) ? Convert.ToDateTime("1900-01-01") : rdr["OrderTime"] == DBNull.Value ? Convert.ToDateTime("1900-01-01") : DateTime.Parse(rdr["OrderTime"].ToString()) }; companyGrossProfitRecordDetailChildList.Add(goodsOrderDetailInfo); } } catch (Exception ex) { throw new Exception("获取订单或者库存明细商品数据异常", ex); } finally { connection.Close(); if (rdr != null) { rdr.Close(); } } } return(companyGrossProfitRecordDetailChildList); }