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