public Hashtable GetCkHs(string sql)
{
Hashtable hs = new Hashtable();
using (SqlConnection conn = DBHelp.getConn())
{
conn.Open();
SqlCommand objCommand = new SqlCommand(sql, conn);
using (SqlDataReader dataReader = objCommand.ExecuteReader())
{
while (dataReader.Read())
{
JXCDetail model = new JXCDetail();
model.PONo = dataReader["PONo"].ToString();
model.AE = dataReader["AE"].ToString();
model.GoodId = Convert.ToInt32(dataReader["GoodId"]);
model.CreateTime = Convert.ToDateTime(dataReader["CreateTime"]);
if (!hs.Contains(model.PONo + model.GoodId))
{
hs.Add(model.PONo + model.GoodId, model.AE);
}
}
}
}
return(hs);
}
public Hashtable getHT(string AE, string poNo, string company, string special, string modelwhere)
{
Hashtable hs = new Hashtable();
var sql = string.Format("select NoSellAndCaiGoods.PONo,allCaiNum,kuCaiNum,waiCaiNum,NoSellAndCaiGoods.AE,LastNum,outNum,sellTuiNum,caiTuiNum from NoSellAndCaiGoods ");
if (special != "-1" || modelwhere != "全部")
{
sql += "left join CG_POOrder on CG_POOrder.PONo=NoSellAndCaiGoods.PONo and CG_POOrder.IFZhui=0";
}
sql += string.Format(" where NoSellAndCaiGoods.PONo like 'P%' {0} {1} "
, (AE != "" ? " and NoSellAndCaiGoods.ae='" + AE + "'" : ""), (poNo != "" ? " and NoSellAndCaiGoods.PONo like '%" + poNo + "%'" : ""));
if (company != "-1")
{
string where = string.Format(" CompanyCode='{0}'", company.Split(',')[2]);
sql += string.Format(" and NoSellAndCaiGoods.ae IN(select loginName from tb_User where {0})", where);
}
if (special != "-1")
{
sql += string.Format(" and CG_POOrder.IsSpecial=" + special);
}
if (modelwhere != "全部")
{
sql += string.Format(" and CG_POOrder.Model='{0}'", modelwhere);
}
sql += " order by NoSellAndCaiGoods.PONo";
using (SqlConnection conn = DBHelp.getConn())
{
conn.Open();
SqlCommand objCommand = new SqlCommand(sql, conn);
using (SqlDataReader dataReader = objCommand.ExecuteReader())
{
while (dataReader.Read())
{
Get_YueKaohe(dataReader, hs);
}
}
}
//增加 商品现在有库存的 并且 有AE的KC项目 ，全部列举
sql = @"select ROW_NUMBER() OVER(PARTITION BY GooId ORDER BY CreateTime DESC) as rowid,CAI_POOrder.PONo,CAI_POOrder.AE,TB_HouseGoods.GoodId,CAI_OrderInHouse.CreateTime from
TB_HouseGoods
left join CAI_OrderInHouses on CAI_OrderInHouses.GooId=TB_HouseGoods.GoodId
left join CAI_OrderInHouse on CAI_OrderInHouse.Id=CAI_OrderInHouses.id
left join CAI_POOrder on CAI_POOrder.PONo=CAI_OrderInHouse.PONo
where CAI_OrderInHouse.Status='通过'
AND CAI_POOrder.AE<>'' and CAI_OrderInHouse.PONo like 'KC%'";
if (company != "-1")
{
string where = string.Format(" CompanyCode='{0}'", company.Split(',')[2]);
sql += string.Format(" and CAI_POOrder.ae IN(select loginName from tb_User where {0})", where);
}
if (AE != "")
{
sql += string.Format(" and CAI_POOrder.AE='{0}'", AE);
}
if (poNo != "")
{
sql += string.Format(" and CAI_OrderInHouse.PONo like '%{0}%'", poNo);
}
sql = string.Format("select * from ({0}) as temp where rowid=1 ", sql);
List <JXCDetail> allDetailList = new List <JXCDetail>();
using (SqlConnection conn = DBHelp.getConn())
{
conn.Open();
SqlCommand objCommand = new SqlCommand(sql, conn);
using (SqlDataReader dataReader = objCommand.ExecuteReader())
{
while (dataReader.Read())
{
JXCDetail model = new JXCDetail();
model.PONo = dataReader["PONo"].ToString();
model.AE = dataReader["AE"].ToString();
model.GoodId = Convert.ToInt32(dataReader["GoodId"]);
model.CreateTime = Convert.ToDateTime(dataReader["CreateTime"]);
allDetailList.Add(model);
}
}
}
CheckHouseNum(allDetailList, hs);
return(hs);
}
