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