public IEnumerable <StocktakingDto> GetAuditList(Pager page, SearchStocktaking condition) { dynamic param = new ExpandoObject(); string where = ""; if (!string.IsNullOrEmpty(condition.Code)) { where += "and t0.Code=@Code "; param.Code = condition.Code; } if (condition.StoreId > 0) { where += "and t0.StoreId=@StoreId "; param.StoreId = condition.StoreId; } if (condition.Type > 0) { where += "and t0.StocktakingType=@StocktakingType "; param.StocktakingType = condition.Type; } if (condition.Status != 0) { where += "and t0.Status=@StocktakingStatus "; param.StocktakingStatus = condition.Status; } if (condition.StocktakingDate.HasValue) { where += "and TIMESTAMPDIFF(DAY,t0.StocktakingDate,@StocktakingDate)=0"; param.StocktakingDate = condition.StocktakingDate; } if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode)) { where += " and t0.Id in (select stocktakingId from stocktakingItem t1 where t1.ProductId=@ProductIdOrBarCode or t1.BarCode=@ProductIdOrBarCode)"; param.ProductIdOrBarCode = condition.ProductCodeOrBarCode; } if (!string.IsNullOrEmpty(condition.ShelfCode)) { where += " and t0.ShelfCode=@ShelfCode"; param.ShelfCode = condition.ShelfCode; } if (string.IsNullOrEmpty(where)) { return(new List <StocktakingDto>()); } string sql = @"select t0.*,t2.`Name` as StoreName,t3.StocktakingDate from stocktaking t0 inner join store t2 on t2.Id = t0.StoreId inner join stocktakingPlan t3 on t3.Id = t0.StocktakingPlanId where 1=1 {0} ORDER BY t0.Id desc "; // sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize); sql = string.Format(sql, where); var rows = this._query.FindAll <StocktakingDto>(sql, param) as IEnumerable <StocktakingDto>; page.Total = rows.Count(); return(rows); }
public IEnumerable <StocktakingListDto> GetPageList(Pager page, SearchStocktaking condition) { dynamic param = new ExpandoObject(); string where = ""; if (!string.IsNullOrEmpty(condition.Code)) { where += "and t0.Code=@Code "; param.Code = condition.Code; } if (condition.StoreId > 0) { where += "and t0.StoreId=@StoreId "; param.StoreId = condition.StoreId; } if (condition.Type > 0) { where += "and t0.StocktakingType=@StocktakingType "; param.StocktakingType = condition.Type; } if (condition.Status != 0) { where += "and t0.Status=@StocktakingStatus "; param.StocktakingStatus = condition.Status; } if (condition.StocktakingDate.HasValue) { where += "and datediff(t3.StocktakingDate,@StocktakingDate)=0"; param.StocktakingDate = condition.StocktakingDate; } if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode)) { where += " and (p.Code=@ProductCodeOrBarCode or p.BarCode=@ProductCodeOrBarCode)"; param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode; } if (!string.IsNullOrEmpty(condition.ShelfCode)) { where += " and t0.ShelfCode=@ShelfCode"; param.ShelfCode = condition.ShelfCode; } if (string.IsNullOrEmpty(where)) { return(new List <StocktakingListDto>()); } string sql = @"select t0.Id,t0.`Code`,t0.ShelfCode,t0.CreatedByName,t0.StocktakingType,t3.StocktakingDate,t1.CountQuantity,t1.CorectReason,t2.`Name` as StoreName,t3.StocktakingDate,p.Code as ProductCode,p.Name as ProductName,p.Specification,p.BarCode,p.Unit from stocktaking t0 inner join stocktakingitem t1 on t0.Id = t1.StocktakingId inner join store t2 on t2.Id = t0.StoreId inner join stocktakingPlan t3 on t3.Id = t0.StocktakingPlanId left join product p on p.id = t1.productId where 1=1 {0} ORDER BY t0.Id desc LIMIT {1},{2}"; sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize); // sql = string.Format(sql, where); var rows = this._query.FindAll <StocktakingListDto>(sql, param) as IEnumerable <StocktakingListDto>; string sqlCount = @"select count(*) from stocktaking t0 inner join stocktakingitem t1 on t0.Id = t1.StocktakingId inner join store t2 on t2.Id = t0.StoreId inner join stocktakingPlan t3 on t3.Id = t0.StocktakingPlanId left join product p on p.id = t1.productId where 1=1 {0}"; sqlCount = string.Format(sqlCount, where); page.Total = _query.Context.ExecuteScalar <int>(sqlCount, param); return(rows); }
public JsonResult LoadAuditData(Pager page, SearchStocktaking condition) { var rows = _stocktakingQuery.GetAuditList(page, condition); return(Json(new { success = true, data = rows, total = page.Total })); }