/// <summary> /// 查询采购计划 /// </summary> /// <param name="KeyWord">关键字</param> /// <param name="PageSize">显示在页面上的数量</param> /// <param name="PageNumber">页码</param> /// <param name="State">状态</param> /// <param name="TotalCount">总数量</param> /// <returns></returns> public List <PurchasePlanView> ShowPurchasePlan(string KeyWord, int PageSize, int PageNumber, string State, out int TotalCount) { TotalCount = 0; try { SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@KeyWord", KeyWord), new SqlParameter("@PageSize", PageSize), new SqlParameter("@PageNumber", PageNumber) }; List <PurchasePlanView> Res = new List <PurchasePlanView>(); StringBuilder sb = new StringBuilder(); sb.Append(" SELECT PPId ,Title ,PName ,[State],Spec ,Qty ,Unit ,HopePrice ,PurchaseName , "); sb.Append(" ContantName ,ContantWay ,EndDateTime ,PurchaseType ,RequestManufactureArea , "); sb.Append(" RequestReceiveArea ,RequestContant ,CreateDate ,CreateUid ,PurchaseDetails, "); sb.Append(" LastUpdateDate ,LastUpdateUId,ROW_NUMBER() OVER(ORDER BY CreateDate DESC) RowNum "); sb.Append(" FROM dbo.PurchasePlan "); if (State != "") { } StringBuilder CountSb = new StringBuilder(); CountSb.Append(sb.ToString()); CountSb.Insert(0, "select count(*) from ("); CountSb.Append(")tb"); sb.Insert(0, " SELECT * FROM ( "); sb.Append(") tb "); sb.Append(" WHERE tb.RowNum BETWEEN ((@PageNumber-1)*@PageSize+1) AND @PageNumber*@PageSize "); sb.Append(" select * from ExtTable where ContantTableName='PurchasePlan' "); sb.Append(CountSb.ToString()); var ds = SQLHelper.GetDataSet(sb.ToString(), parms); var dt = ds.Tables[0]; var ExtDt = ds.Tables[1]; var TotalDt = ds.Tables[2]; TotalCount = Convert.ToInt32(TotalDt.Rows[0][0].ToString()); for (int i = 0; i < dt.Rows.Count; i++) { var dr = dt.Rows[i]; var TempItem = new PurchasePlanView(); TempItem.PlanItem = new PurchasePlan() { ContantName = Convert.IsDBNull(dr["ContantName"]) ? "" : dr["ContantName"].ToString(), ContantWay = Convert.IsDBNull(dr["ContantWay"]) ? "" : dr["ContantWay"].ToString(), CreateDate = Convert.IsDBNull(dr["CreateDate"]) ? new DateTime(1900, 1, 1) : Convert.ToDateTime(dr["CreateDate"]), EndDateTime = Convert.IsDBNull(dr["EndDateTime"]) ? new DateTime(1900, 1, 1) : Convert.ToDateTime(dr["EndDateTime"]), HopePrice = Convert.IsDBNull(dr["HopePrice"]) ? 0 : Convert.ToDecimal(dr["HopePrice"]), LastUpdateDate = Convert.IsDBNull(dr["LastUpdateDate"]) ? new DateTime(1900, 1, 1) : Convert.ToDateTime(dr["LastUpdateDate"]), PName = Convert.IsDBNull(dr["PName"]) ? "" : dr["PName"].ToString(), PPId = Convert.IsDBNull(dr["PPId"]) ? 0 : Convert.ToInt32(dr["PPId"]), PurchaseName = Convert.IsDBNull(dr["PurchaseName"]) ? "" : dr["PurchaseName"].ToString(), // PurchaseType = Convert.IsDBNull(dr["PurchaseType"]) ? "" : dr["PurchaseType"].ToString(), Qty = Convert.IsDBNull(dr["Qty"]) ? 0 : Convert.ToInt32(dr["Qty"]), RequestContant = Convert.IsDBNull(dr["RequestContant"]) ? "" : dr["RequestContant"].ToString(), RequestManufactureArea = Convert.IsDBNull(dr["RequestManufactureArea"]) ? "" : dr["RequestManufactureArea"].ToString(), RequestReceiveArea = Convert.IsDBNull(dr["RequestReceiveArea"]) ? "" : dr["RequestReceiveArea"].ToString(), Spec = Convert.IsDBNull(dr["Spec"]) ? "" : dr["Spec"].ToString(), State = Convert.IsDBNull(dr["State"]) ? "" : dr["State"].ToString(), Title = Convert.IsDBNull(dr["Title"]) ? "" : dr["Title"].ToString(), Unit = Convert.IsDBNull(dr["Unit"]) ? "" : dr["Unit"].ToString(), PurchaseDetails = Convert.IsDBNull(dr["PurchaseDetails"]) ? "" : dr["PurchaseDetails"].ToString(), }; var drs = ExtDt.Select(" ContantId='" + dr["PPId"].ToString() + "' "); List <ExtTable> ExtList = new List <ExtTable>(); for (int k = 0; k < drs.Length; k++) { var TempDr = drs[k]; ExtList.Add(new ExtTable() { ContantId = Convert.IsDBNull(TempDr["ContantId"]) ? 0 : Convert.ToInt32(TempDr["ContantId"]), ContantTableName = "PurchasePlan", ExtId = Convert.IsDBNull(TempDr["ExtId"]) ? 0 : Convert.ToInt32(TempDr["ExtId"]), ExtName = Convert.IsDBNull(TempDr["ExtName"]) ? "" : TempDr["ExtName"].ToString(), ExtValue = Convert.IsDBNull(TempDr["ExtValue"]) ? "" : TempDr["ExtValue"].ToString(), }); } TempItem.ExtList = ExtList; Res.Add(TempItem); } return(Res); } catch (Exception) { return(null); } }
/// <summary> /// 根据PPID取得采购计划 /// </summary> /// <param name="PPId">采购计划ID</param> /// <returns></returns> public PurchasePlanView GetPurchasePlanByPPId(int PPId) { try { StringBuilder sb = new StringBuilder(); SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@PPId", PPId), }; sb.Append(" SELECT * FROM dbo.PurchasePlan WHERE PPId=@PPId "); sb.Append(" select * from dbo.ExtTable WHERE ContantId=@PPId"); var ds = SQLHelper.GetDataSet(sb.ToString(), parms); var dt1 = ds.Tables[0]; var ExtDt = ds.Tables[1]; PurchasePlanView Res = new PurchasePlanView(); if (dt1.Rows.Count > 0) { var dr = dt1.Rows[0]; Res.PlanItem = new PurchasePlan() { ContantName = Convert.IsDBNull(dr["ContantName"]) ? "" : dr["ContantName"].ToString(), ContantWay = Convert.IsDBNull(dr["ContantWay"]) ? "" : dr["ContantWay"].ToString(), CreateDate = Convert.IsDBNull(dr["CreateDate"]) ? new DateTime(1900, 1, 1) : Convert.ToDateTime(dr["CreateDate"]), EndDateTime = Convert.IsDBNull(dr["EndDateTime"]) ? new DateTime(1900, 1, 1) : Convert.ToDateTime(dr["EndDateTime"]), HopePrice = Convert.IsDBNull(dr["HopePrice"]) ? 0 : Convert.ToDecimal(dr["HopePrice"]), LastUpdateDate = Convert.IsDBNull(dr["LastUpdateDate"]) ? new DateTime(1900, 1, 1) : Convert.ToDateTime(dr["LastUpdateDate"]), PName = Convert.IsDBNull(dr["PName"]) ? "" : dr["PName"].ToString(), PPId = Convert.IsDBNull(dr["PPId"]) ? 0 : Convert.ToInt32(dr["PPId"]), PurchaseName = Convert.IsDBNull(dr["PurchaseName"]) ? "" : dr["PurchaseName"].ToString(), // PurchaseType = Convert.IsDBNull(dr["PurchaseType"]) ? "" : dr["PurchaseType"].ToString(), Qty = Convert.IsDBNull(dr["Qty"]) ? 0 : Convert.ToInt32(dr["Qty"]), RequestContant = Convert.IsDBNull(dr["RequestContant"]) ? "" : dr["RequestContant"].ToString(), RequestManufactureArea = Convert.IsDBNull(dr["RequestManufactureArea"]) ? "" : dr["RequestManufactureArea"].ToString(), RequestReceiveArea = Convert.IsDBNull(dr["RequestReceiveArea"]) ? "" : dr["RequestReceiveArea"].ToString(), Spec = Convert.IsDBNull(dr["Spec"]) ? "" : dr["Spec"].ToString(), State = Convert.IsDBNull(dr["State"]) ? "" : dr["State"].ToString(), Title = Convert.IsDBNull(dr["Title"]) ? "" : dr["Title"].ToString(), Unit = Convert.IsDBNull(dr["Unit"]) ? "" : dr["Unit"].ToString(), PurchaseDetails = Convert.IsDBNull(dr["PurchaseDetails"]) ? "" : dr["PurchaseDetails"].ToString(), }; } List <ExtTable> ExtList = new List <ExtTable>(); for (int i = 0; i < ExtDt.Rows.Count; i++) { var dr = ExtDt.Rows[i]; ExtTable ExtItem = new ExtTable(); ExtItem.ExtId = Convert.IsDBNull(dr["ExtId"]) ? 0 : Convert.ToInt32(dr["ExtId"]); ExtItem.ExtName = Convert.IsDBNull(dr["ExtName"]) ? "" : dr["ExtName"].ToString(); ExtItem.ExtValue = Convert.IsDBNull(dr["ExtValue"]) ? "" : dr["ExtValue"].ToString(); ExtItem.ContantId = Convert.IsDBNull(dr["ContantId"]) ? 0 : Convert.ToInt32(dr["ContantId"]); ExtList.Add(ExtItem); } Res.ExtList = ExtList; return(Res); } catch (Exception) { return(null); } }