protected void gvMain_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Attributes.Add("onmouseover", "currentcolor=this.style.backgroundColor;this.style.backgroundColor='#EAF1FD',this.style.fontWeight='';");
e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=currentcolor,this.style.fontWeight='';");
Sell_Cai_OrderInHouseListModel model = e.Row.DataItem as Sell_Cai_OrderInHouseListModel;
////商品销售退货合计-采购退货合计<0--不正常（浅灰色）
// if (model.GoodNum - (model.CaiGoodNum ?? 0) < 0)
// {
// e.Row.BackColor = System.Drawing.Color.LightGreen;
// }
////商品销售退货合计-采购退货合计=0--正常（浅黄色）
// else if (model.GoodNum - (model.CaiGoodNum ?? 0) == 0)
// {
// e.Row.BackColor = System.Drawing.Color.LightYellow;
// }
////项目中该商品数量合计-采购退货合计>=（销售退货合计-采购退货合计）×2 且采购单为库存的数量>=销售退货合计-采购退货合计-正常（浅绿色）
// else if (model.NeedNums <= 0&&model.HouseGoodNum>(model.GoodNum-(model.CaiGoodNum??0)))
// {
// e.Row.BackColor = System.Drawing.Color.LightGreen;
// }
////项目中该商品数量合计-采购退货合计<（销售退货合计-采购退货合计）×2 -正常库存消零（土黄色）
// else if (model.NeedNums > 0 && model.HouseGoodNum > (model.GoodNum - (model.CaiGoodNum ?? 0)))
// {
// e.Row.BackColor = System.Drawing.Color.LightYellow;
// }
////项目中该商品数量合计-采购退货合计>=（销售退货合计-采购退货合计）×2 且采购单为库存的数量<销售退货合计-采购退货合计--不正常有库存（淡红色）
// else if (model.NeedNums <= 0 && model.HouseGoodNum > (model.GoodNum - (model.CaiGoodNum ?? 0)))
// {
// e.Row.BackColor = System.Drawing.Color.OrangeRed;
// }
////项目中该商品数量合计-采购退货合计<（销售退货合计-采购退货合计）×2 -不正常库存不为零（红色）
// else if (model.NeedNums > 0)
// {
// e.Row.BackColor = System.Drawing.Color.Red;
// }
if (model.NeedNums <= 0)
{
e.Row.BackColor = System.Drawing.Color.LightGreen;
}
if ((model.GoodNum - (model.CaiGoodNum ?? 0)) < 0)
{
e.Row.BackColor = System.Drawing.Color.LightGray;
}
if (model.NeedNums > 0)
{
e.Row.BackColor = System.Drawing.Color.OrangeRed;
}
SumOrders.CAIGoodPriceTotal = SumOrders.CAIGoodPriceTotal ?? 0 + model.CAIGoodPriceTotal ?? 0;
SumOrders.GoodSellPriceTotal = SumOrders.GoodSellPriceTotal + model.GoodSellPriceTotal;
}
// 合计
if (e.Row.RowType == DataControlRowType.Footer)
{
lblSellTuiTotal.Text = SumOrders.GoodSellPriceTotal.ToString();
lblCaiTuiTotal.Text = (SumOrders.CAIGoodPriceTotal ?? 0).ToString();
}
}
public List <VAN_OA.Model.JXC.Sell_Cai_OrderInHouseListModel> GetSell_Cai_OrderInHouseListArray(string strWhere)
{
List <VAN_OA.Model.JXC.Sell_Cai_OrderInHouseListModel> list = new List <VAN_OA.Model.JXC.Sell_Cai_OrderInHouseListModel>();
var sql = string.Format(@" select GoodAreaNumber,IsNormal,CaiNums,TB_HouseGoods.GoodNum as HouseGoodNum,PONums,AE,POName,GuestNAME,tb.*,CaiGoodNum,CAIRuTime,CAIGoodPrice,GoodNo,GoodName,GoodSpec,GoodModel,GoodUnit,GoodTypeSmName from
(
select IsNormal,MAX(RuTime) as RuTime,PONo,GooId,sum(GoodNum) as GoodNum,avg(GoodPrice) as GoodPrice,
avg(GoodSellPrice) as GoodSellPrice,AVG(GoodPriceSecond) as GoodPriceSecond,SUM(GoodTotalCha) as GoodTotalCha
from Sell_OrderInHouse left join Sell_OrderInHouses on Sell_OrderInHouse.Id=Sell_OrderInHouses.id
where Status='通过'
group by PONo,GooId,IsNormal
) as tb left join
(
select PONo,GooId,SUM(GoodNum) as CaiGoodNum,MAX(RuTime) as CAIRuTime,AVG(GoodPrice) as CAIGoodPrice
from CAI_OrderOutHouse left join CAI_OrderOutHouses on CAI_OrderOutHouse.Id=CAI_OrderOutHouses.id
where Status<>'不通过'
group by PONo,GooId
) as tb1 on tb.PONo=tb1.PONo and tb.GooId=tb1.GooId
left join TB_Good on TB_Good.GoodId=tb.GooId
left join TB_HouseGoods on TB_HouseGoods.GoodId=tb.GooId
left join
(
select PONo,GoodId,sum(CAI_POCai.Num) as CaiNums from CAI_POOrder left join CAI_POCai on CAI_POCai.Id=CAI_POOrder.Id
where Status='通过' and lastSupplier='库存'
group by PONo,GoodId
)
as tempCai on tb.PONo=tempCai.PONo and tb.GooId=tempCai.GoodId
lefT JOIN
(
SELECT CG_POOrder.PONo,CG_POOrders.GoodId,sum(CG_POOrders.Num) as PONums
FROM CG_POOrder left join CG_POOrders on CG_POOrder.Id=CG_POOrders.Id
WHERE Status='通过' GROUP BY CG_POOrders.GoodId,CG_POOrder.PONo
)as poTable on poTable.PONo=tb.PONo and poTable.GoodId=tb.GooId left join CG_POOrder
on CG_POOrder.Status='通过' and CG_POOrder.IFZhui=0 and CG_POOrder.PONO=tb.PONo where 1=1 {0} order by tb.PONO desc ", strWhere);
using (SqlConnection conn = DBHelp.getConn())
{
conn.Open();
SqlCommand objCommand = new SqlCommand(sql, conn);
using (SqlDataReader dataReader = objCommand.ExecuteReader())
{
while (dataReader.Read())
{
var model = new Sell_Cai_OrderInHouseListModel();
model.PONo = dataReader["PONo"].ToString();
model.POName = dataReader["POName"].ToString();
model.GuestName = dataReader["GuestNAME"].ToString();
model.CreateName = dataReader["AE"].ToString();
model.RuTime = Convert.ToDateTime(dataReader["RuTime"]);
var ojb = dataReader["GoodNum"];
if (ojb != null && ojb != DBNull.Value)
{
model.GoodNum = (decimal)ojb;
}
ojb = dataReader["GoodPrice"];
if (ojb != null && ojb != DBNull.Value)
{
model.GoodPrice = (decimal)ojb;
}
model.Total = model.GoodNum * model.GoodPrice;
ojb = dataReader["GoodNo"];
if (ojb != null && ojb != DBNull.Value)
{
model.GoodNo = ojb.ToString();
}
ojb = dataReader["GoodName"];
if (ojb != null && ojb != DBNull.Value)
{
model.GoodName = ojb.ToString();
}
ojb = dataReader["GoodSpec"];
if (ojb != null && ojb != DBNull.Value)
{
model.GoodSpec = ojb.ToString();
}
ojb = dataReader["GoodModel"];
if (ojb != null && ojb != DBNull.Value)
{
model.Good_Model = ojb.ToString();
}
ojb = dataReader["GoodUnit"];
if (ojb != null && ojb != DBNull.Value)
{
model.GoodUnit = ojb.ToString();
}
ojb = dataReader["GoodTypeSmName"];
if (ojb != null && ojb != DBNull.Value)
{
model.GoodTypeSmName = ojb.ToString();
}
ojb = dataReader["GoodSellPrice"];
if (ojb != null && ojb != DBNull.Value)
{
model.GoodSellPrice = Convert.ToDecimal(ojb);
}
model.GoodSellPriceTotal = model.GoodSellPrice * model.GoodNum;
ojb = dataReader["GoodPriceSecond"];
if (ojb != null && ojb != DBNull.Value)
{
model.GoodPriceSecond = Convert.ToDecimal(ojb);
}
ojb = dataReader["GoodTotalCha"];
if (ojb != null && ojb != DBNull.Value)
{
model.GoodTotalCha = Convert.ToDecimal(ojb);
}
ojb = dataReader["CaiGoodNum"];
if (ojb != null && ojb != DBNull.Value)
{
model.CaiGoodNum = Convert.ToDecimal(ojb);
}
ojb = dataReader["CAIRuTime"];
if (ojb != null && ojb != DBNull.Value)
{
model.CAIRuTime = Convert.ToDateTime(ojb);
}
ojb = dataReader["CAIGoodPrice"];
if (ojb != null && ojb != DBNull.Value)
{
model.CAIGoodPrice = Convert.ToDecimal(ojb);
model.CAIGoodPriceTotal = model.CAIGoodPrice * model.CaiGoodNum;
}
ojb = dataReader["PONums"];
if (ojb != null && ojb != DBNull.Value)
{
model.PONums = Convert.ToDecimal(ojb);
}
ojb = dataReader["HouseGoodNum"];
if (ojb != null && ojb != DBNull.Value)
{
model.HouseGoodNum = Convert.ToDecimal(ojb);
}
ojb = dataReader["CaiNums"];
if (ojb != null && ojb != DBNull.Value)
{
model.CaiNums = Convert.ToDecimal(ojb);
}
model.GooId = Convert.ToInt32(dataReader["GooId"]);
ojb = dataReader["IsNormal"];
if (ojb != null && ojb != DBNull.Value)
{
model.IsNormal = Convert.ToBoolean(ojb);
}
model.GoodAreaNumber = dataReader["GoodAreaNumber"].ToString();
list.Add(model);
}
}
}
return(list);
}