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