曾经做项目遇到这样一个问题,再商品的进销存分析中,有进货数,销售数,库存数,在当天中它没有进行、销售但是库存数一直是不变的;如下图:
一开始写时间判断时,他这个时间范围内应该没有一条数据的,返回应该为0条数据,差不多就是将表格数据清空的情况,仔细想了想我就将数据的条数进行判断,若是数据大于一条,就返回本身值,若是为0,我就将把上个查询重新复制过来,进货数、销售数赋值为0,库存数保留。
代码如下:
public ActionResult SelectJinXiaoCun(BsgridPage bsgridPage, string start, string end)
{ //两个时间参数start,end;
var list = (from tbXSMX in myModels.Base_XiaoShoMX
join tbXS in myModels.Base_XiaoShouDan on tbXSMX.XiaoShouDanID equals tbXS.XiaoShouDanID
join tbKuCun in myModels.JC_KuCun on tbXSMX.KuCunID equals tbKuCun.KuCunID
from tbJinHuoMX in myModels.Base_JinHuoMX
where tbJinHuoMX.KuCunID == tbXSMX.KuCunID
select new JinHuoVo
{
XiaoShoMXID = tbXSMX.XiaoShoMXID,
XiaoShouDate = tbXS.XiaoShouDate,
XiaoJi = tbXSMX.XiaoJi,
KuCunNumber = tbKuCun.KuCunNumber,
ShuLiang = tbJinHuoMX.ShuLiang,
XiaoShouNumber = tbXSMX.XiaoShouNumber
}).ToList();
if (!string.IsNullOrEmpty(start) && !string.IsNullOrEmpty(end)) //进行时间条件筛选数据
{
DateTime Start = Convert.ToDateTime(start);
DateTime End = Convert.ToDateTime(end);
list = list.Where(m => m.XiaoShouDate >= Start && m.XiaoShouDate <= End).ToList();
}
var lists = (from tb in list
group tb by tb.XiaoJi into JX
select new JinHuoVo
{
XiaoJi = JX.OrderByDescending(m => m.XiaoShoMXID).FirstOrDefault().XiaoJi,
KuCunNumber = JX.Sum(m => m.KuCunNumber),
ShuLiang = JX.Sum(m => m.ShuLiang),
XiaoShouNumber = JX.Sum(m => m.XiaoShouNumber)
}).ToList();//数据进行分组;
int count = lists.Count();
if (count = 0)//数据条数判断,为空就自行参数赋值
{
List<JinHuoVo> listJX = lists.Skip(bsgridPage.GetStartIndex()).Take(bsgridPage.pageSize).ToList();
Bsgrid<JinHuoVo> bsgrid = new Bsgrid<JinHuoVo>()
{
success = true,
curPage = bsgridPage.curPage,
totalRows = count,
data = listJX
};
return Json(bsgrid, JsonRequestBehavior.AllowGet);
}
else
{
var listKC = (from tbXSMX in myModels.Base_XiaoShoMX
join tbXS in myModels.Base_XiaoShouDan on tbXSMX.XiaoShouDanID equals tbXS.XiaoShouDanID
join tbKuCun in myModels.JC_KuCun on tbXSMX.KuCunID equals tbKuCun.KuCunID
from tbJinHuoMX in myModels.Base_JinHuoMX
where tbJinHuoMX.KuCunID == tbXSMX.KuCunID
select new JinHuoVo
{
XiaoShoMXID = tbXSMX.XiaoShoMXID,
XiaoJi = tbXSMX.XiaoJi,
KuCunNumber = tbKuCun.KuCunNumber, //库存数保留
ShuLiang = 0, //进货数、销售数都赋值为0;
XiaoShouNumber = 0
}).ToList();
var listss = (from tb in listKC
group tb by tb.XiaoJi into JX
select new JinHuoVo
{
XiaoJi = JX.OrderByDescending(m => m.XiaoShoMXID).FirstOrDefault().XiaoJi,
KuCunNumber = JX.Sum(m => m.KuCunNumber),
ShuLiang = JX.Sum(m => m.ShuLiang),
XiaoShouNumber = JX.Sum(m => m.XiaoShouNumber)
}).ToList();
int KCcount = listss.Count();
List<JinHuoVo> listJXs = listss.Skip(bsgridPage.GetStartIndex()).Take(bsgridPage.pageSize).ToList();
Bsgrid<JinHuoVo> bsgrid = new Bsgrid<JinHuoVo>()
{
success = true,
curPage = bsgridPage.curPage,
totalRows = KCcount,
data = listJXs
};
return Json(bsgrid, JsonRequestBehavior.AllowGet);
}
}//小计