/// <summary>
/// 查询diamond
/// </summary>
/// <param name="diamondQueryItem"></param>
/// <param name="localization">国际化标记</param>
/// <returns></returns>
public DataSet GetDiamondList(DiamondQueryItem queryItem, List <ScendingItem> scendingList, string localization)
{
SqlParameter[] paramList = GetDiamondWhere(queryItem, scendingList, DIAMOND_SELECT, localization, out sql);
using (DataSet ds = SQLHelper.ExecuteDataset(DBConnStr, CommandType.Text, sql, paramList))
{
try
{
return(ds);
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 增加where参数字符串
/// </summary>
/// <param name="queryItem"></param>
/// <param name="selectSql"></param>
/// <param name="localization"></param>
/// <param name="querySql"></param>
/// <returns></returns>
private SqlParameter[] GetDiamondWhere(DiamondQueryItem queryItem, List <ScendingItem> ScendingList, string selectSql, string localization, out string querySql)
{
StringBuilder sbWhere = new StringBuilder();
List <SqlParameter> sqlParamList = new List <SqlParameter>();
//List<SqlParameter> sqlParamList2 = new List<SqlParameter>();
//sqlParamList.Add(new SqlParameter("@TOTALROWS", SqlDbType.Int));
//sqlParamList.Add(new SqlParameter("@TOTALPAGES", SqlDbType.Int));
sqlParamList.Add(new SqlParameter("@LOCALIZATION", localization));
if (!string.IsNullOrEmpty(queryItem.ArticleNo))
{
sbWhere.Append(" and DiamondNo like @DIAMONDNO");
sqlParamList.Add(new SqlParameter("@DIAMONDNO", "%" + queryItem.DiamondNo + "%"));
}
if (queryItem.CaratRangeList != null)
{
if (queryItem.CaratRangeList.Count > 0)
{
string caratWhere = string.Empty;
for (int i = 0; i < queryItem.CaratRangeList.Count; i++)
{
caratWhere += "(Carat>=@CARATMIN" + i.ToString() + " and Carat<=@CARATMAX" + i.ToString() + ") or ";
sqlParamList.Add(new SqlParameter("@CARATMIN" + i.ToString(), queryItem.CaratRangeList[i].CaratMin));
sqlParamList.Add(new SqlParameter("@CARATMAX" + i.ToString(), queryItem.CaratRangeList[i].CaratMax));
}
caratWhere = caratWhere.Substring(0, caratWhere.Length - 4);
sbWhere.Append(" and (" + caratWhere + ")");
}
}
if (queryItem.StoreIDList != null)
{
sbWhere.Append(" and a.storeid in (" + InsertParamList(queryItem.StoreIDList, "@STOREID", ref sqlParamList) + ")");
}
if (queryItem.ShapeCodeList != null)
{
sbWhere.Append(" and ShapeCode in (" + InsertParamList(queryItem.ShapeCodeList, "@SHAPECODE", ref sqlParamList) + ")");
}
if (queryItem.ColorCodeList != null)
{
sbWhere.Append(" and ColorCode in (" + InsertParamList(queryItem.ColorCodeList, "@COLORCODE", ref sqlParamList) + ")");
}
if (queryItem.ClarityCodeList != null)
{
sbWhere.Append(" and ClarityCode in (" + InsertParamList(queryItem.ClarityCodeList, "@CLARITYCODE", ref sqlParamList) + ")");
}
if (!string.IsNullOrEmpty(queryItem.StatusCode))
{
sbWhere.Append(" and StatusCode=@STATUSCODE");
sqlParamList.Add(new SqlParameter("@STATUSCODE", queryItem.StatusCode));
}
if (!string.IsNullOrEmpty(queryItem.PairID))
{
sbWhere.Append(" and PairID like @PAIRID");
sqlParamList.Add(new SqlParameter("@PAIRID", "%" + queryItem.PairID + "%"));
}
if (!string.IsNullOrEmpty(queryItem.ArticleNo))
{
sbWhere.Append(" and ArticleNo like @ARTICLENO");
sqlParamList.Add(new SqlParameter("@ARTICLENO", "%" + queryItem.ArticleNo + "%"));
}
if (queryItem.UploadDate != DateTime.MinValue)
{
sbWhere.Append(" and UploadDate>=@UploadDate");
sqlParamList.Add(new SqlParameter("@UploadDate", queryItem.UploadDate));
}
sbWhere.Append(" order by");
foreach (var scendingItem in ScendingList)
{
sbWhere.Append(" " + scendingItem.ColumnName + " " + scendingItem.ScendingType.ToString());
}
//sqlParamList.Add(new SqlParameter("@PAGESIZE", diamondSearch.PageSize));
//sqlParamList.Add(new SqlParameter("@PAGEINDEX", diamondSearch.PageIndex));
querySql = selectSql + sbWhere.ToString();
SqlParameter[] paramList = sqlParamList.ToArray();
return(paramList);
}