public PaymentNoticeInfo GetPayNoticInfoByPayid(Guid payid) { PaymentNoticeInfo pninfo = null; const string SQL_PN_SELECT_BY_PAYID = @"Select [PayId],[OrderId],[OrderNo],[PayBank],[PayPrince],[PayTime],[PayName],[PayDes],[PayState],SaleFilialeId,SalePlatformId From lmShop_PaymentNotice Where PayId=@PayId "; var parm = new SqlParameter(PAYID, SqlDbType.UniqueIdentifier) { Value = payid }; using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, SQL_PN_SELECT_BY_PAYID, parm)) { if (rdr.Read()) { pninfo = new PaymentNoticeInfo { PayId = SqlRead.GetGuid(rdr, "PayId"), OrderId = SqlRead.GetGuid(rdr, "OrderId"), OrderNo = SqlRead.GetString(rdr, "OrderNo"), PayBank = SqlRead.GetString(rdr, "PayBank"), PayPrince = SqlRead.GetDecimal(rdr, "PayPrince"), PayTime = SqlRead.GetDateTime(rdr, "PayTime"), PayName = SqlRead.GetString(rdr, "PayName"), PayDes = SqlRead.GetString(rdr, "PayDes"), PayState = SqlRead.GetInt(rdr, "PayState"), SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"), SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId") }; } } return(pninfo); }
/// <summary> /// 查询商品毛利中超过一个自然月或一个自然月以上未完成的数据(例如:当前是7月份,订单时间是5月份的订单在7月1号之前没有完成的数据) /// </summary> /// <param name="dayTime">完成时间</param> /// <returns></returns> public IList <GoodsGrossProfitInfo> GetGoodsGrossProfitRecordDetailInfosForMoreMonth(DateTime dayTime) { var builder = new StringBuilder(@" SELECT GoodsId,GoodsType,SUM(Quantity) AS Quantity,SaleFilialeId,SalePlatformId,SUM(SalesPriceTotal) AS SalesPriceTotal,SUM(PurchaseCostTotal) AS PurchaseCostTotal,OrderType,(CONVERT(VARCHAR(7),OrderTime,120)+'-01') AS OrderTime FROM GoodsGrossProfitRecordDetail WITH(NOLOCK) WHERE State=0 AND DayTime>=dateadd(month,2,CONVERT(VARCHAR(7),OrderTime,120)+'-01') "); builder.AppendFormat(" AND DayTime<'{0}' ", dayTime); builder.Append(" GROUP BY [GoodsId],[GoodsType],[SaleFilialeId],[SalePlatformId],[OrderType],(CONVERT(VARCHAR(7),OrderTime,120)+'-01')"); IList <GoodsGrossProfitInfo> dataList = new List <GoodsGrossProfitInfo>(); using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_REPORT_DB_NAME, true, builder.ToString())) { if (rdr != null) { while (rdr.Read()) { dataList.Add(new GoodsGrossProfitInfo { GoodsId = SqlRead.GetGuid(rdr, "GoodsId"), GoodsType = SqlRead.GetInt(rdr, "GoodsType"), Quantity = SqlRead.GetInt(rdr, "Quantity"), SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"), SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId"), SalesPriceTotal = SqlRead.GetDecimal(rdr, "SalesPriceTotal"), PurchaseCostTotal = SqlRead.GetDecimal(rdr, "PurchaseCostTotal"), OrderType = SqlRead.GetInt(rdr, "OrderType"), DayTime = SqlRead.GetDateTime(rdr, "OrderTime") }); } } } return(dataList); }
/// <summary> /// 查询公司毛利中超过一个自然月或一个自然月以上未完成的数据(例如:当前是7月份,订单时间是5月份的订单在7月1号之前没有完成的数据) /// </summary> /// <param name="dayTime">完成时间</param> /// <returns></returns> public IList <CompanyGrossProfitRecordInfo> GetCompanyGrossProfitDetailInfosForMoreMonth(DateTime dayTime) { var builder = new StringBuilder(@" SELECT [SaleFilialeId] ,[SalePlatformId] ,SUM([SalesAmount]) AS [SalesAmount] ,SUM([GoodsAmount]) AS [GoodsAmount] ,SUM([ShipmentIncome]) AS [ShipmentIncome] ,SUM([PromotionsDeductible]) AS [PromotionsDeductible] ,SUM([PointsDeduction]) AS [PointsDeduction] ,SUM([ShipmentCost]) AS [ShipmentCost] ,SUM([PurchaseCosts]) AS [PurchaseCosts] ,SUM([CatCommission]) AS [CatCommission] ,[OrderType] ,CONVERT(VARCHAR(7),[OrderTime],120)+'-01' AS [OrderTime] FROM [CompanyGrossProfitRecordDetail] WITH(NOLOCK) WHERE State=0 AND DayTime>=dateadd(month,2,CONVERT(VARCHAR(7),OrderTime,120)+'-01') "); builder.AppendFormat(" AND DayTime<'{0}' ", dayTime); builder.Append(" GROUP BY [SaleFilialeId],[SalePlatformId],[OrderType],CONVERT(VARCHAR(7),[OrderTime],120)+'-01'"); IList <CompanyGrossProfitRecordInfo> dataList = new List <CompanyGrossProfitRecordInfo>(); using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_REPORT_DB_NAME, true, builder.ToString())) { if (rdr != null) { while (rdr.Read()) { dataList.Add(new CompanyGrossProfitRecordInfo { SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"), SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId"), SalesAmount = SqlRead.GetDecimal(rdr, "SalesAmount"), GoodsAmount = SqlRead.GetDecimal(rdr, "GoodsAmount"), ShipmentIncome = SqlRead.GetDecimal(rdr, "ShipmentIncome"), PromotionsDeductible = SqlRead.GetDecimal(rdr, "PromotionsDeductible"), PointsDeduction = SqlRead.GetDecimal(rdr, "PointsDeduction"), ShipmentCost = SqlRead.GetDecimal(rdr, "ShipmentCost"), PurchaseCosts = SqlRead.GetDecimal(rdr, "PurchaseCosts"), CatCommission = SqlRead.GetDecimal(rdr, "CatCommission"), OrderType = SqlRead.GetInt(rdr, "OrderType"), DayTime = SqlRead.GetDateTime(rdr, "OrderTime") }); } } } return(dataList); }
/// <summary> /// 查询当前月份入库往来帐及账期往来帐 /// </summary> /// <param name="startTime"></param> /// <param name="endTime"></param> public IList <RecordReckoningInfo> SelectRecordReckoningInfos(DateTime startTime, DateTime endTime) { const string SQL_SELECT = @"SELECT [ReckoningId],FilialeId,[ThirdCompanyID] AS CompanyId,R.[TradeCode],'{0}' AS DayTime,(CASE WHEN CC.PaymentDays>0 THEN DATEADD(MM,CC.PaymentDays,'{0}') ELSE '{0}' END) AS [PaymentDayTime],[AccountReceivable],TradeCode,R.[State],[IsChecked],[LinkTradeType],[LinkTradeCode] FROM lmShop_Reckoning AS R with(nolock) INNER JOIN lmshop_CompanyCussent CC ON R.ThirdCompanyID=CC.CompanyID AND CC.CompanyType=1 where R.DateCreated between '{0}' and '{1}' and AuditingState=1 AND LEFT(LinkTradeCode,2) NOT IN('LI','LO','BI','BO') and TradeCode not like 'GT%' "; IList <RecordReckoningInfo> list = new List <RecordReckoningInfo>(); IDataReader sdr = null; try { sdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, string.Format(string.Format(SQL_SELECT, startTime, endTime), new DateTime(startTime.Year, startTime.Month, 1))); while (sdr.Read()) { list.Add(new RecordReckoningInfo { ReckoningId = SqlRead.GetGuid(sdr, "ReckoningId"), FilialeId = SqlRead.GetGuid(sdr, "FilialeId"), CompanyId = SqlRead.GetGuid(sdr, "CompanyId"), TradeCode = SqlRead.GetString(sdr, "TradeCode"), DayTime = SqlRead.GetDateTime(sdr, "DayTime"), PaymentDayTime = SqlRead.GetDateTime(sdr, "PaymentDayTime"), AccountReceivable = SqlRead.GetDecimal(sdr, "AccountReceivable"), LinkTradeType = SqlRead.GetInt(sdr, "LinkTradeType"), State = SqlRead.GetInt(sdr, "State"), LinkTradeCode = SqlRead.GetString(sdr, "LinkTradeCode"), IsChecked = SqlRead.GetInt(sdr, "IsChecked") }); } } catch (Exception ex) { throw new Exception("当前往来帐明细存档异常", ex); } finally { sdr?.Close(); } return(list); }
/// <summary> /// 查询历史月份商品毛利信息 /// </summary> /// <param name="startTime">记录年月</param> /// <param name="endTime"></param> /// <param name="goodsTypes">商品类型</param> /// <param name="saleFilialeId">销售公司</param> /// <param name="salePlatformIds">销售平台</param> /// <param name="orderTypes">订单类型(0:网络发货订单;1:门店采购订单;2:帮门店发货订单;)</param> /// <returns></returns> public IList <GoodsGrossProfitInfo> SelectGoodsGrossProfitInfos(DateTime startTime, DateTime?endTime, string goodsTypes, Guid saleFilialeId, string salePlatformIds, string orderTypes) { string goodsTypeStr = string.Empty, salePlatformIdStr = string.Empty, orderTypeStr = string.Empty; var builder = new StringBuilder(@" SELECT [ID] ,[GoodsId] ,[GoodsType] ,[Quantity] ,[SaleFilialeId] ,[SalePlatformId] ,[SalesPriceTotal] ,[PurchaseCostTotal] ,[OrderType] ,[DayTime] FROM GoodsGrossProfitRecord WITH(NOLOCK) WHERE 1=1 "); if (endTime == null || endTime == DateTime.MinValue) { builder.AppendFormat(" AND DayTime='{0}' ", startTime); } else { builder.AppendFormat(" AND DayTime>='{0}' AND DayTime<'{1}'", startTime, endTime); } if (!string.IsNullOrEmpty(goodsTypes)) { goodsTypeStr = goodsTypes.Split(',').Aggregate(goodsTypeStr, (current, item) => current + string.Format(",{0}", item)).Substring(1); builder.AppendFormat(" AND GoodsType IN({0}) ", goodsTypeStr); } if (!string.IsNullOrEmpty(salePlatformIds)) { salePlatformIdStr = salePlatformIds.Split(',').Aggregate(salePlatformIdStr, (current, item) => current + string.Format(",'{0}'", item)).Substring(1); builder.AppendFormat(" AND 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 OrderType IN({0}) ", orderTypeStr); } if (saleFilialeId != Guid.Empty) { builder.AppendFormat(" AND SaleFilialeId = '{0}' ", saleFilialeId); } IList <GoodsGrossProfitInfo> dataList = new List <GoodsGrossProfitInfo>(); IDataReader rdr = null; try { rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_REPORT_DB_NAME, true, builder.ToString()); while (rdr.Read()) { dataList.Add(new GoodsGrossProfitInfo { ID = SqlRead.GetGuid(rdr, "ID"), GoodsId = SqlRead.GetGuid(rdr, "GoodsId"), GoodsType = SqlRead.GetInt(rdr, "GoodsType"), Quantity = SqlRead.GetInt(rdr, "Quantity"), SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"), SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId"), SalesPriceTotal = SqlRead.GetDecimal(rdr, "SalesPriceTotal"), PurchaseCostTotal = SqlRead.GetDecimal(rdr, "PurchaseCostTotal"), OrderType = SqlRead.GetInt(rdr, "OrderType"), DayTime = SqlRead.GetDateTime(rdr, "DayTime") }); } } finally { if (rdr != null) { rdr.Close(); } } return(dataList); }
/// <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> /// <returns></returns> public IList <CompanyGrossProfitRecordInfo> SelectCompanyGrossProfitInfos(DateTime startTime, DateTime?endTime, Guid saleFilialeId, string salePlatformIds, string orderTypes) { string salePlatformIdStr = string.Empty, orderTypeStr = string.Empty; var builder = new StringBuilder(@" SELECT [ID] ,[SaleFilialeId] ,[SalePlatformId] ,[SalesAmount] ,[GoodsAmount] ,[ShipmentIncome] ,[PromotionsDeductible] ,[PointsDeduction] ,[ShipmentCost] ,[PurchaseCosts] ,[CatCommission] ,[OrderType] ,[DayTime] FROM [CompanyGrossProfitRecord] WITH(NOLOCK) WHERE 1=1 "); if (endTime == null || endTime == DateTime.MinValue) { builder.AppendFormat(" AND DayTime='{0}' ", startTime); } else { builder.AppendFormat(" AND DayTime>='{0}' AND DayTime<'{1}'", startTime, endTime); } if (!string.IsNullOrEmpty(salePlatformIds)) { salePlatformIdStr = salePlatformIds.Split(',').Aggregate(salePlatformIdStr, (current, item) => current + string.Format(",'{0}'", item)).Substring(1); builder.AppendFormat(" AND 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 OrderType IN({0}) ", orderTypeStr); } if (saleFilialeId != Guid.Empty) { builder.AppendFormat(" AND SaleFilialeId = '{0}' ", saleFilialeId); } IList <CompanyGrossProfitRecordInfo> dataList = new List <CompanyGrossProfitRecordInfo>(); using (var connection = Keede.DAL.RWSplitting.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()) { dataList.Add(new CompanyGrossProfitRecordInfo { ID = SqlRead.GetGuid(rdr, "ID"), SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"), SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId"), SalesAmount = SqlRead.GetDecimal(rdr, "SalesAmount"), GoodsAmount = SqlRead.GetDecimal(rdr, "GoodsAmount"), ShipmentIncome = SqlRead.GetDecimal(rdr, "ShipmentIncome"), PromotionsDeductible = SqlRead.GetDecimal(rdr, "PromotionsDeductible"), PointsDeduction = SqlRead.GetDecimal(rdr, "PointsDeduction"), ShipmentCost = SqlRead.GetDecimal(rdr, "ShipmentCost"), PurchaseCosts = SqlRead.GetDecimal(rdr, "PurchaseCosts"), CatCommission = SqlRead.GetDecimal(rdr, "CatCommission"), OrderType = SqlRead.GetInt(rdr, "OrderType"), DayTime = SqlRead.GetDateTime(rdr, "DayTime") }); } } finally { connection.Close(); if (rdr != null) { rdr.Close(); } } } return(dataList); }