public IEnumerable <AdjustStorePriceListDto> QueryFinish(Pager page, SearchAdjustStorePrice condition)
{
dynamic param = new ExpandoObject();
string where = "";
if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode))
{
where += "and (t2.`Code`=@ProductCodeOrBarCode or t2.BarCode=@ProductCodeOrBarCode)";
param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode;
}
if (!string.IsNullOrEmpty(condition.Code))
{
where += "and t0.Code=@Code ";
param.Code = condition.Code;
}
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
where += "and t0.StoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();
}
if (condition.Status != 0)
{
where += "and t0.Status=@Status ";
param.Status = condition.Status;
}
if (condition.CreatedOn.HasValue)
{
where += "and DATE(t0.CreatedOn) =@CreatedOn ";
param.CreatedOn = condition.CreatedOn.Value;
}
string sql = @"select t0.Id,t0.`Code`,t0.Status,t0.Remark,t1.ProductId,t2.`Name` as ProductName,t2.`Code` as ProductCode,t2.BarCode,t2.Specification,
t2.Unit,t1.AdjustPrice,t1.StoreSalePrice,t0.updatedOn,t3.NickName as UpdatedByName,t4.`Name` as StoreName
from AdjustStorePrice t0
inner join AdjustStorePriceItem t1 on t0.Id = t1.AdjustStorePriceId
left join product t2 on t1.productid = t2.id
left join account t3 on t0.updatedBy = t3.Id
left join Store t4 on t4.Id = t0.StoreId
where 1=1 {0} ORDER BY t0.Id desc LIMIT {1},{2}";
string sqlCount = @"select count(*) from AdjustStorePrice t0
inner join AdjustStorePriceItem t1 on t0.Id = t1.AdjustStorePriceId
left join product t2 on t1.productid = t2.id
left join account t3 on t0.updatedBy = t3.Id
left join Store t4 on t4.Id = t0.StoreId
where 1=1 {0} ORDER BY t0.Id desc LIMIT {1},{2}";
//rows = this._query.FindPage<ProductDto>(page.PageIndex, page.PageSize).Where<Product>(where, param);
sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize);
sqlCount = string.Format(sqlCount, where, (page.PageIndex - 1) * page.PageSize, page.PageSize);
var rows = this._query.FindAll <AdjustStorePriceListDto>(sql, param);
page.Total = this._query.FindScalar <int>(sqlCount, param);
return(rows);
}
public IEnumerable <AdjustStorePriceDto> GetPageList(Pager page, SearchAdjustStorePrice condition)
{
dynamic param = new ExpandoObject();
string where = "";
if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode))
{
where += @"and t0.Id in (
select i.adjuststorepriceId from adjuststorepriceitem i left join product p on p.Id = i.productId
where (p.`Code`=@ProductCodeOrBarCode or p.BarCode=@ProductCodeOrBarCode))";
param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode;
}
if (!string.IsNullOrEmpty(condition.Code))
{
where += "and t0.Code=@Code ";
param.Code = condition.Code;
}
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
where += "and t0.StoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();
}
if (condition.Status != 0)
{
where += "and t0.Status=@Status ";
param.Status = condition.Status;
}
if (condition.CreatedOn.HasValue)
{
where += "and DATE(t0.CreatedOn) =@CreatedOn ";
param.CreatedOn = condition.CreatedOn.Value;
}
string sql = @"select t0.Id,t0.Code,t0.Status,t0.UpdatedOn,t0.CreatedOn,t0.Remark,t1.name as StoreName,t3.NickName as CreatedByName
from AdjustStorePrice t0
left join store t1 on t1.Id = t0.StoreId
left join account t3 on t0.createdBy = t3.Id
where 1=1 {0} ORDER BY t0.Id desc LIMIT {1},{2}";
string sqlCount = @"select count(*) from AdjustStorePrice t0
left join store t1 on t1.Id = t0.StoreId
left join account t3 on t0.updatedBy = t3.Id
where 1=1 {0} ORDER BY t0.Id desc LIMIT {1},{2}";
//rows = this._query.FindPage<ProductDto>(page.PageIndex, page.PageSize).Where<Product>(where, param);
sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize);
sqlCount = string.Format(sqlCount, where, (page.PageIndex - 1) * page.PageSize, page.PageSize);
var rows = this._query.FindAll <AdjustStorePriceDto>(sql, param);
page.Total = this._query.FindScalar <int>(sqlCount, param);
return(rows);
}
public JsonResult QueryFinish(Pager page, SearchAdjustStorePrice condition)
{
var rows = _adjustStorePriceQuery.QueryFinish(page, condition);
return(Json(new { success = true, data = rows, total = page.Total }));
}
public JsonResult LoadData(Pager page, SearchAdjustStorePrice condition)
{
var rows = _adjustStorePriceQuery.GetPageList(page, condition);
return(Json(new { success = true, data = rows, total = page.Total }));
}
