public static DataTable GetSubStorageProduct(SubStorageProductModel model, int PageIndex, int PageSize, string OrderBy, ref int PageCount, Hashtable htPara) { int length = htPara.Count; string EFSql = string.Empty; if (htPara.ContainsKey("EFIndex") && htPara.ContainsKey("EFDesc")) { EFSql = " AND pi." + htPara["EFIndex"] + " LIKE '" + htPara["EFDesc"] + "' "; length = length - 2; } SqlParameter[] paras = new SqlParameter[length + 2]; int index = 0; paras[index] = new SqlParameter("@DeptID", SqlDbType.Int); paras[index++].Value = model.DeptID; paras[index] = new SqlParameter("@CompanyCD", SqlDbType.VarChar); paras[index++].Value = model.CompanyCD; string tmpSql = string.Empty; if (htPara.ContainsKey("SubStoreName")) { tmpSql += " AND di.DeptName LIKE @SubStoreName "; paras[index] = new SqlParameter("@SubStoreName", SqlDbType.VarChar); paras[index++].Value = htPara["SubStoreName"]; } StringBuilder sbSql = new StringBuilder(); sbSql.Append("SELECT ssp.*,( SELECT di.DeptName FROM officedba.DeptInfo as di WHERE di.ID=ssp.DeptID " + tmpSql + " ) as DetpName, pi.SellPrice"); sbSql.Append(" ,pi.ProductName ,pi.ProdNo,pi.Specification,pi.IsBatchNo,(select ct.CodeName from officedba.CodeUnitType as ct where ct.ID=pi.UnitID ) as UnitName,pi.UnitID "); sbSql.Append(" ,(SELECT sps.SendPriceTax FROM officedba.SubProductSendPrice AS sps where sps.DeptID=ssp.DeptID AND sps.CompanyCD=ssp.CompanyCD AND sps.ProductID=ssp.ProductID) AS BackPrice "); sbSql.Append(@" FROM officedba.SubStorageProduct as ssp inner join officedba.ProductInfo as pi on pi.ID=ssp.ProductID AND pi.CheckStatus=1 "); sbSql.Append(" where ssp.DeptID=@DeptID AND ssp.CompanyCD=@CompanyCD "); if (htPara.ContainsKey("ProductName")) { sbSql.Append(" AND pi.ProductName LIKE @ProductName "); paras[index] = new SqlParameter("@ProductName", SqlDbType.VarChar); paras[index++].Value = htPara["ProductName"]; } if (htPara.ContainsKey("ProdNo")) { sbSql.Append(" AND pi.ProdNo LIKE @ProdNo"); paras[index] = new SqlParameter("@ProdNo", SqlDbType.VarChar); paras[index++].Value = htPara["ProdNo"]; } if (htPara.ContainsKey("Specification")) { sbSql.Append(" AND pi.Specification LIKE @Specification"); paras[index] = new SqlParameter("@Specification", SqlDbType.VarChar); paras[index++].Value = htPara["Specification"]; } /*追加扩展属性查询*/ sbSql.Append(EFSql); return SqlHelper.CreateSqlByPageExcuteSql(sbSql.ToString(), PageIndex, PageSize, OrderBy, paras, ref PageCount); }
/*条码扫描使用*/ public static DataTable GetSubStorageProduct(SubStorageProductModel model) { return XBase.Data.Office.LogisticsDistributionManager.SubDeliveryBackDBHelper.GetSubStorageProduct(model); }
/*条码扫描使用*/ public static DataTable GetSubStorageProduct(SubStorageProductModel model) { StringBuilder sbSql = new StringBuilder(); sbSql.Append("SELECT ssp.*,( SELECT di.DeptName FROM officedba.DeptInfo as di WHERE di.ID=ssp.DeptID ) as DetpName, pi.SellPrice,"); sbSql.Append(" pi.ProductName ,pi.ProdNo,pi.Specification,pi.IsBatchNo,(select ct.CodeName from officedba.CodeUnitType as ct where ct.ID=pi.UnitID ) as UnitName, "); sbSql.Append(" (SELECT sps.SendPriceTax FROM officedba.SubProductSendPrice AS sps where sps.DeptID=ssp.DeptID AND sps.CompanyCD=ssp.CompanyCD AND sps.ProductID=ssp.ProductID) AS BackPrice ,(SELECT sps.SendPriceTax FROM officedba.SubProductSendPrice AS sps where sps.DeptID=0 AND sps.CompanyCD=ssp.CompanyCD AND sps.ProductID=ssp.ProductID) AS DefaultBackPrice "); sbSql.Append(" FROM officedba.SubStorageProduct as ssp inner join officedba.ProductInfo as pi on pi.ID=ssp.ProductID AND pi.CheckStatus=1 "); sbSql.Append(" where ssp.DeptID=@DeptID AND ssp.CompanyCD=@CompanyCD AND pi.ID=@ProductID "); SqlParameter[] Paras ={ SqlHelper.GetParameter("@CompanyCD",model.CompanyCD), SqlHelper.GetParameter("@DeptID",model.DeptID), SqlHelper.GetParameter("@ProductID",model.ProductID) }; return SqlHelper.ExecuteSql(sbSql.ToString(), Paras); }
public static DataTable GetSubStorageProduct(SubStorageProductModel model, int PageIndex, int PageSize, string OrderBy, ref int PageCount, Hashtable htPara) { return XBase.Data.Office.LogisticsDistributionManager.SubDeliveryBackDBHelper.GetSubStorageProduct(model, PageIndex, PageSize, OrderBy, ref PageCount, htPara); }