/// <summary> /// 根据条件返回GoodsPriceChange表的所有数据 /// </summary> /// <param name="saleFilialeId">公司ID</param> /// <param name="salePlatformId">销售平台ID</param> /// <param name="goodsName">商品名称</param> /// <param name="goodsId">商品编号</param> /// <param name="type">0:销售价;1:加盟价;2:批发价</param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="total">数据总数</param> /// <returns></returns> public List <GoodsPriceChange> GetAllGoodsPriceChange(Guid?saleFilialeId, Guid?salePlatformId, string goodsName, Guid goodsId, int type, int pageIndex, int pageSize, out int total) { var sqlsb = new StringBuilder(SQL_SELECT + " where 1=1"); if (!string.IsNullOrEmpty(saleFilialeId.ToString()) && saleFilialeId != Guid.Empty) { sqlsb.AppendFormat(@" AND [SaleFilialeId]='{0}'", saleFilialeId); } if (!string.IsNullOrEmpty(salePlatformId.ToString()) && salePlatformId != Guid.Empty) { sqlsb.AppendFormat(@" AND [SalePlatformId]='{0}'", salePlatformId); } if (!string.IsNullOrEmpty(goodsName)) { sqlsb.AppendFormat(@" AND [GoodsName]='{0}'", goodsName); } if (goodsId != Guid.Empty) { sqlsb.AppendFormat(@" AND [GoodsId]='{0}'", goodsId); } if (type != -1) { sqlsb.AppendFormat(@" AND [Type]='{0}'", type); } using (var db = DatabaseFactory.Create()) { var list = new Keede.DAL.Helper.Sql.PageQuery(pageIndex, pageSize, sqlsb.ToString(), " Datetime DESC"); var pageItem = db.SelectByPage <GoodsPriceChange>(true, list); total = (int)pageItem.RecordCount; return(pageItem.Items.ToList()); } }
public IList <PurchaseSetLogInfo> GetPurchaseSetLogListByPage(Guid goodsId, Guid warehouseId, Guid hostingFilialeId, int startPage, int pageSize, out long recordCount) { var strbSql = new StringBuilder(SQL_SELECT_PURCHASESETLOG); strbSql.Append(" WHERE 1=1"); if (goodsId != Guid.Empty) { strbSql.Append(" AND GoodsId='").Append(goodsId).Append("'"); } if (warehouseId != Guid.Empty) { strbSql.Append(" AND WarehouseId='").Append(warehouseId).Append("'"); } if (hostingFilialeId != Guid.Empty) { strbSql.AppendFormat(" AND HostingFilialeId ='{0}'", hostingFilialeId); } strbSql.Append("AND LogType=1"); using (var db = DatabaseFactory.Create()) { var pageQuery = new Keede.DAL.Helper.Sql.PageQuery(startPage, pageSize, strbSql.ToString(), " ChangeDate DESC "); var pageItem = db.SelectByPage <PurchaseSetLogInfo>(true, pageQuery); recordCount = pageItem.RecordCount; return(pageItem.Items.ToList()); } }
/// <summary> /// 根据条件筛选数据 /// </summary> /// <param name="startTime">创建开始时间</param> /// <param name="endTime">创建结束时间</param> /// <param name="state">借记单状态</param> /// <param name="warehouseId">仓库id</param> /// <param name="companyId">供应商id</param> /// <param name="personResponsibleId">责任人id</param> /// <param name="activityTimeStart">活动开始时间</param> /// <param name="activityTimeEnd">活动结束时间</param> /// <param name="titleOrMemo">标题或备注</param> /// <param name="startPage">开始页索引</param> /// <param name="pageSize">每页显示的数量</param> /// <param name="recordCount">查询数据的总和</param> /// <returns></returns> public IList <DebitNoteInfo> GetDebitNoteList(DateTime startTime, DateTime endTime, int state, Guid warehouseId, Guid companyId, Guid personResponsibleId, string activityTimeStart, string activityTimeEnd, string titleOrMemo, int startPage, int pageSize, out long recordCount) { var strb = new StringBuilder(SQL_SELECT_DEBITNOTE); strb.Append(" WHERE 1=1 "); if (startTime != DateTime.MinValue) { strb.Append(" AND [CreateDate]>='").Append(startTime.ToString("yyyy-MM-dd 00:00:00")).Append("'"); } if (endTime != DateTime.MinValue) { strb.Append(" AND [CreateDate]<'").Append(endTime.AddDays(1).ToString("yyyy-MM-dd 00:00:00")).Append("'"); } if (state != -1) { strb.Append(" AND [State]=").Append(state); } if (warehouseId != Guid.Empty) { strb.Append(" AND [WarehouseId]='").Append(warehouseId).Append("'"); } if (companyId != Guid.Empty) { strb.Append(" AND [CompanyId]='").Append(companyId).Append("'"); } if (personResponsibleId != Guid.Empty) { strb.Append(" AND [PersonResponsible]='").Append(personResponsibleId).Append("'"); } if (!string.IsNullOrEmpty(activityTimeStart)) { strb.Append(" AND [ActivityTimeStart]>='").Append(DateTime.Parse(activityTimeStart)).Append("'"); } if (!string.IsNullOrEmpty(activityTimeEnd)) { strb.Append(" AND [ActivityTimeEnd]<='").Append(DateTime.Parse(activityTimeEnd).AddDays(1).AddMilliseconds(-1)).Append("'"); } if (!string.IsNullOrEmpty(titleOrMemo)) { strb.Append(" AND [Title] like '%").Append(titleOrMemo).Append("%' or [Memo] like '%").Append(titleOrMemo).Append("%'"); } using (var db = DatabaseFactory.Create()) { var pageQuery = new Keede.DAL.Helper.Sql.PageQuery(startPage, pageSize, strb.ToString(), " [State],[CreateDate] DESC "); var pageItem = db.SelectByPage <DebitNoteInfo>(true, pageQuery, null); recordCount = pageItem.RecordCount; return(pageItem.Items.ToList()); } }
/// <summary> /// 根据ManuallyCheckBillDetail表的ManuallyCheckBillId字段返回数据 /// </summary> /// <param name="manuallyCheckBillId"></param> /// <param name="orderNo">系统订单号或者第三方订单号</param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="total"></param> /// <returns></returns> public List <ManuallyCheckBillDetailInfo> GetManuallyCheckBillDetailByManuallyCheckBillId(Guid manuallyCheckBillId, string orderNo, int pageIndex, int pageSize, out int total) { string sql = SQL_SELECT + "where [ManuallyCheckBillId] = '" + manuallyCheckBillId + "'"; if (!string.IsNullOrEmpty(orderNo)) { sql += " AND (SystemOrderNo='" + orderNo + "' OR ThirdOrderNo='" + orderNo + "')"; } using (var db = DatabaseFactory.Create()) { var list = new Keede.DAL.Helper.Sql.PageQuery(pageIndex, pageSize, sql, " OrderTime DESC"); var pageItem = db.SelectByPage <ManuallyCheckBillDetailInfo>(true, list); total = (int)pageItem.RecordCount; return(pageItem.Items.ToList()); } }
/// <summary> /// 根据条件获取对账记录 /// </summary> /// <param name="checkBillPersonnelId"></param> /// <param name="tradeCode"></param> /// <param name="checkState"></param> /// <param name="checkBillDateStart"></param> /// <param name="checkBillDateEnd"></param> /// <param name="salePlatformId"></param> /// <param name="receiptState"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="total"></param> /// <returns></returns> public List <ManuallyCheckBillInfo> GetAllManuallyCheckBill(Guid checkBillPersonnelId, string tradeCode, CheckType checkState, DateTime checkBillDateStart, DateTime checkBillDateEnd, Guid salePlatformId, int receiptState, int pageIndex, int pageSize, out int total) { var sb = new StringBuilder(SQL_SELECT + " WHERE 1=1 "); if (!checkBillPersonnelId.Equals(Guid.Empty)) { sb.AppendFormat(@" AND CheckBillPersonnelId='{0}'", checkBillPersonnelId); } if (!string.IsNullOrEmpty(tradeCode)) { sb.AppendFormat(@" AND TradeCode='{0}'", tradeCode); } if (!checkState.Equals(CheckType.AllCheck)) { sb.AppendFormat(@" AND CheckState={0}", (int)checkState); } if (checkBillDateStart != DateTime.MinValue) { sb.AppendFormat(@" AND CheckBillDate>='{0}'", checkBillDateStart); } if (checkBillDateEnd != DateTime.MaxValue) { sb.AppendFormat(@" AND CheckBillDate<'{0}'", checkBillDateEnd.AddDays(1)); } if (!salePlatformId.Equals(Guid.Empty)) { sb.AppendFormat(@" AND SalePlatformId='{0}'", salePlatformId); } if (receiptState != -1) { sb.AppendFormat(@" AND ReceiptState={0} ", receiptState); } using (var db = DatabaseFactory.Create()) { var list = new Keede.DAL.Helper.Sql.PageQuery(pageIndex, pageSize, sb.ToString(), " CheckBillDate DESC"); var pageItem = db.SelectByPage <ManuallyCheckBillInfo>(true, list); total = (int)pageItem.RecordCount; return(pageItem.Items.ToList()); } }
/// <summary> /// 红冲单据分页查询 /// </summary> /// <param name="warehouseId">仓库</param> /// <param name="startTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <param name="redType">红冲类型</param> /// <param name="documentType">单据类型</param> /// <param name="state">状态</param> /// <param name="no">单号</param> /// <param name="startPage">当前页</param> /// <param name="pageSize">每页显示记录数</param> /// <param name="recordCount">总记录数</param> /// <returns></returns> public IList <DocumentRedInfo> GetDocumentRedListToPage(Guid warehouseId, DateTime startTime, DateTime endTime, int redType, int documentType, int state, string no, int startPage, int pageSize, out long recordCount) { var sql = new StringBuilder(@" SELECT [RedId] ,[FilialeId] ,[ThirdCompanyId] ,[TradeCode] ,[DateCreated] ,[Transactor] ,[Description] ,[AccountReceivable] ,[SubtotalQuantity] ,[RedType] ,[DocumentType] ,[State] ,[WarehouseId] ,[AuditTime] ,[StorageType] ,[LinkTradeCode] ,[LinkTradeId] ,[LinkDateCreated] ,[LinkDescription] ,[Memo] ,[IsOut] FROM [DocumentRed] WITH(NOLOCK) WHERE 1=1"); if (warehouseId != Guid.Empty) { sql.Append(" AND WarehouseId='").Append(warehouseId).Append("'"); } if (startTime != DateTime.MinValue) { sql.Append(" AND DateCreated>='").Append(startTime).Append("'"); } if (endTime != DateTime.MinValue) { sql.Append(" AND DateCreated<'").Append(endTime).Append("'"); } if (!redType.Equals(0)) { sql.Append(" AND RedType='").Append(redType).Append("'"); } if (!state.Equals(0)) { sql.Append(" AND State='").Append(state).Append("'"); } if (!documentType.Equals(-1)) { sql.Append(" AND DocumentType ='").Append(documentType).Append("'"); } if (!string.IsNullOrEmpty(no)) { sql.Append(" AND (TradeCode like '%").Append(no).Append("%'"); sql.Append(" OR LinkTradeCode like '%").Append(no).Append("%')"); } using (var db = DatabaseFactory.Create()) { var pageQuery = new Keede.DAL.Helper.Sql.PageQuery(startPage, pageSize, sql.ToString(), " DateCreated DESC "); var pageItem = db.SelectByPage <DocumentRedInfo>(true, pageQuery); recordCount = pageItem.RecordCount; return(pageItem.Items.ToList()); } }