public static DataTable GetPurchaseRequireInfo(PurchaseRequireModel PurchaseRequireM, string OrderBy)
{
try
{
return(PurchaseRequireDBHelper.GetPurchaseRequireInfo(PurchaseRequireM, OrderBy));
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable GetPurchaseRequireInfo(PurchaseRequireModel PurchaseRequireM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
return(PurchaseRequireDBHelper.GetPurchaseRequireInfo(PurchaseRequireM, pageIndex, pageCount, OrderBy, ref totalCount));
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable GetPurchaseRequireInfo(PurchaseRequireModel PurchaseRequireM, string OrderBy)
{
try
{
return PurchaseRequireDBHelper.GetPurchaseRequireInfo(PurchaseRequireM, OrderBy);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable GetPurchaseRequireInfo(PurchaseRequireModel PurchaseRequireM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
return PurchaseRequireDBHelper.GetPurchaseRequireInfo(PurchaseRequireM,pageIndex,pageCount,OrderBy,ref totalCount);
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
PurchaseRequireModel PurchaseRequireM = new PurchaseRequireModel();
PurchaseRequireM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
PurchaseRequireM.ProdTypeID = txtProductTypeID.Value;
PurchaseRequireM.ProdID = txtProductID.Value;
PurchaseRequireM.CreateCondition = ddlCreate.Value;
PurchaseRequireM.RequireDate = txtStartRequireDate.Value;
PurchaseRequireM.EndRequireDate = txtEndRequireDate.Value;
string OrderBy = hidOrderBy.Value;
DataTable dt = PurchaseRequireBus.GetPurchaseRequireInfo(PurchaseRequireM, OrderBy);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "物料需求计划单编号", "物料编码", "物料名称", "物料分类", "规格", "单位", "订单需求量", "现有存量", "需申购数量", "采购提前期", "已计划数量", "需求日期" },
new string[] { "MRPNo", "ProdNo", "ProductName", "ProductTypeName", "Specification", "UnitName", "NeedCount", "HasNum", "WantingNum", "WaitingDays", "OrderCount", "RequireDate" },
"采购需求列表");
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
PurchaseRequireModel PurchaseRequireM = new PurchaseRequireModel();
PurchaseRequireM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
PurchaseRequireM.ProdTypeID = txtProductTypeID.Value;
PurchaseRequireM.ProdID = txtProductID.Value;
PurchaseRequireM.CreateCondition = ddlCreate.Value;
PurchaseRequireM.RequireDate = txtStartRequireDate.Value;
PurchaseRequireM.EndRequireDate = txtEndRequireDate.Value;
string OrderBy = hidOrderBy.Value;
DataTable dt = PurchaseRequireBus.GetPurchaseRequireInfo(PurchaseRequireM, OrderBy);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "物料需求计划单编号", "物料编码", "物料名称", "物料分类", "规格", "单位", "订单需求量", "现有存量", "需申购数量", "采购提前期", "已计划数量", "需求日期" },
new string[] { "MRPNo", "ProdNo", "ProductName", "ProductTypeName", "Specification", "UnitName", "NeedCount", "HasNum", "WantingNum", "WaitingDays", "OrderCount", "RequireDate" },
"采购需求列表");
}
public static DataTable GetPurchaseRequireInfo(PurchaseRequireModel PurchaseRequireM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
SqlCommand comm = new SqlCommand();
#region SQL文
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT A.ID ");
sql.AppendLine(", isnull(A.MRPCD ,0) AS MRPCD ");
sql.AppendLine(", isnull(B.MRPNo ,'') AS MRPNo ");
sql.AppendLine(", isnull(A.ProdID ,0) AS ProdID ");
sql.AppendLine(", isnull(C.ProdNo ,'') AS ProdNo ");
sql.AppendLine(", isnull(C.ProductName ,'') AS ProductName ");
sql.AppendLine(", isnull(C.Specification ,'') AS Specification ");
sql.AppendLine(", isnull(C.TypeID ,0) AS ProdTypeID ");
sql.AppendLine(", isnull(D.CodeName ,'') AS ProductTypeName");
sql.AppendLine(", isnull(C.UnitID ,0) AS UnitID ");
sql.AppendLine(", isnull(E.CodeName ,'') AS UnitName ");
sql.AppendLine(", isnull( A.NeedCount ,0) AS NeedCount ");
sql.AppendLine(", (SELECT sum(isnull(H.ProductCount,0)+isnull(H.RoadCount,0)-isnull(H.OutCount,0)-isnull(H.OrderCount,0)) FROM officedba.StorageProduct AS H WHERE A.CompanyCD=H.CompanyCD AND A.ProdID=H.ProductID) AS HasNum ");
sql.AppendLine(", isnull(A.WantingNum ,0) AS WantingNum ");
sql.AppendLine(", isnull(A.WaitingDays ,0) AS WaitingDays ");
sql.AppendLine(", isnull(CONVERT(varchar(23),A.RequireDate,23),0) AS RequireDate ");
sql.AppendLine(", isnull(A.OrderCount,0) AS OrderCount");
sql.AppendLine(", isnull(A.Creator ,0) AS Creator ");
sql.AppendLine(", isnull(F.EmployeeName ,'') AS CreatorName ");
sql.AppendLine(", isnull(CONVERT(varchar(23),A.CreateDate,23) ,'') AS CreateDate ");
sql.AppendLine(", isnull(A.Confirmor ,0) AS Confirmor ");
sql.AppendLine(", isnull(G.EmployeeName ,'') AS ConfirmorName ");
sql.AppendLine(", isnull(CONVERT(varchar(23),A.ConfirmDate,23),'') AS ConfirmDate,isnull(H.TypeName,'') as ColorName ");
sql.AppendLine("FROM officedba.PurchaseRequire AS A ");
sql.AppendLine("LEFT JOIN officedba.EmployeeInfo AS G ON A.Confirmor = G.ID ");
sql.AppendLine("INNER JOIN officedba.MRP AS B ON A.MRPCD = B.ID ");
sql.AppendLine("LEFT JOIN officedba.ProductInfo AS C ON A.ProdID = C.ID ");
sql.AppendLine("LEFT JOIN officedba.CodeProductType AS D ON C.TypeID = D.ID ");
sql.AppendLine("LEFT JOIN officedba.CodeUnitType AS E ON C.UnitID = E.ID ");
sql.AppendLine("LEFT JOIN officedba.EmployeeInfo AS F ON A.Creator = F.ID ");
sql.AppendLine("left join officedba.CodePublicType H on C.ColorID=H.ID");
sql.AppendLine("WHERE A.CompanyCD = @CompanyCD");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD));
if (PurchaseRequireM.ProdTypeID != "")
{
sql.AppendLine(" AND C.TypeID in (" + PurchaseRequireM.ProdTypeID + ")");
}
if (PurchaseRequireM.ProdID != "")
{
sql.AppendLine(" AND A.ProdID=@ProductID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductID", PurchaseRequireM.ProdID));
}
if (PurchaseRequireM.CreateCondition == "0")
{//任何条件
}
else if (PurchaseRequireM.CreateCondition == "1")
{//没有生成
sql.AppendLine(" AND isnull(A.OrderCount,0)=0 ");
}
else if (PurchaseRequireM.CreateCondition == "2")
{//部分生成
sql.AppendLine(" AND isnull(A.OrderCount,0)<isnull(A.WantingNum,0) AND isnull(A.OrderCount,0) <> 0 ");
}
else if (PurchaseRequireM.CreateCondition == "3")
{//生成完毕
sql.AppendLine(" AND isnull(A.OrderCount,0)>=isnull(A.WantingNum,0)");
}
if (PurchaseRequireM.RequireDate != "")
{
sql.AppendLine(" AND A.RequireDate>=@StartDate");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", PurchaseRequireM.RequireDate));
}
if (PurchaseRequireM.EndRequireDate != "")
{
sql.AppendLine(" AND A.RequireDate<=@EndDate");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", PurchaseRequireM.EndRequireDate));
}
#endregion
comm.CommandText = sql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount);
//return SqlHelper.ExecuteSearch(comm);
}
public static DataTable GetPurchaseRequireInfo(PurchaseRequireModel PurchaseRequireM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
SqlCommand comm = new SqlCommand();
#region SQL文
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT A.ID ");
sql.AppendLine(", isnull(A.MRPCD ,0) AS MRPCD ");
sql.AppendLine(", isnull(B.MRPNo ,'') AS MRPNo ");
sql.AppendLine(", isnull(A.ProdID ,0) AS ProdID ");
sql.AppendLine(", isnull(C.ProdNo ,'') AS ProdNo ");
sql.AppendLine(", isnull(C.ProductName ,'') AS ProductName ");
sql.AppendLine(", isnull(C.Specification ,'') AS Specification ");
sql.AppendLine(", isnull(C.TypeID ,0) AS ProdTypeID ");
sql.AppendLine(", isnull(D.CodeName ,'') AS ProductTypeName");
sql.AppendLine(", isnull(C.UnitID ,0) AS UnitID ");
sql.AppendLine(", isnull(E.CodeName ,'') AS UnitName ");
sql.AppendLine(", isnull( A.NeedCount ,0) AS NeedCount ");
sql.AppendLine(", (SELECT sum(isnull(H.ProductCount,0)+isnull(H.RoadCount,0)-isnull(H.OutCount,0)-isnull(H.OrderCount,0)) FROM officedba.StorageProduct AS H WHERE A.CompanyCD=H.CompanyCD AND A.ProdID=H.ProductID) AS HasNum ");
sql.AppendLine(", isnull(A.WantingNum ,0) AS WantingNum ");
sql.AppendLine(", isnull(A.WaitingDays ,0) AS WaitingDays ");
sql.AppendLine(", isnull(CONVERT(varchar(23),A.RequireDate,23),0) AS RequireDate ");
sql.AppendLine(", isnull(A.OrderCount,0) AS OrderCount");
sql.AppendLine(", isnull(A.Creator ,0) AS Creator ");
sql.AppendLine(", isnull(F.EmployeeName ,'') AS CreatorName ");
sql.AppendLine(", isnull(CONVERT(varchar(23),A.CreateDate,23) ,'') AS CreateDate ");
sql.AppendLine(", isnull(A.Confirmor ,0) AS Confirmor ");
sql.AppendLine(", isnull(G.EmployeeName ,'') AS ConfirmorName ");
sql.AppendLine(", isnull(CONVERT(varchar(23),A.ConfirmDate,23),'') AS ConfirmDate,isnull(H.TypeName,'') as ColorName ");
sql.AppendLine("FROM officedba.PurchaseRequire AS A ");
sql.AppendLine("LEFT JOIN officedba.EmployeeInfo AS G ON A.Confirmor = G.ID ");
sql.AppendLine("INNER JOIN officedba.MRP AS B ON A.MRPCD = B.ID ");
sql.AppendLine("LEFT JOIN officedba.ProductInfo AS C ON A.ProdID = C.ID ");
sql.AppendLine("LEFT JOIN officedba.CodeProductType AS D ON C.TypeID = D.ID ");
sql.AppendLine("LEFT JOIN officedba.CodeUnitType AS E ON C.UnitID = E.ID ");
sql.AppendLine("LEFT JOIN officedba.EmployeeInfo AS F ON A.Creator = F.ID ");
sql.AppendLine("left join officedba.CodePublicType H on C.ColorID=H.ID");
sql.AppendLine("WHERE A.CompanyCD = @CompanyCD");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD));
if (PurchaseRequireM.ProdTypeID != "")
{
sql.AppendLine(" AND C.TypeID in (" + PurchaseRequireM.ProdTypeID + ")");
}
if (PurchaseRequireM.ProdID != "")
{
sql.AppendLine(" AND A.ProdID=@ProductID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductID", PurchaseRequireM.ProdID));
}
if (PurchaseRequireM.CreateCondition == "0")
{//任何条件
}
else if (PurchaseRequireM.CreateCondition == "1")
{//没有生成
sql.AppendLine(" AND isnull(A.OrderCount,0)=0 ");
}
else if (PurchaseRequireM.CreateCondition == "2")
{//部分生成
sql.AppendLine(" AND isnull(A.OrderCount,0)<isnull(A.WantingNum,0) AND isnull(A.OrderCount,0) <> 0 ");
}
else if (PurchaseRequireM.CreateCondition == "3")
{//生成完毕
sql.AppendLine(" AND isnull(A.OrderCount,0)>=isnull(A.WantingNum,0)");
}
if (PurchaseRequireM.RequireDate != "")
{
sql.AppendLine(" AND A.RequireDate>=@StartDate");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", PurchaseRequireM.RequireDate));
}
if (PurchaseRequireM.EndRequireDate != "")
{
sql.AppendLine(" AND A.RequireDate<=@EndDate");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", PurchaseRequireM.EndRequireDate));
}
#endregion
comm.CommandText = sql.ToString();
return(SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount));
//return SqlHelper.ExecuteSearch(comm);
}
