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" }, "采购需求列表"); }
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); }