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 }));
}