public List<SKUEncode> GetSKUEncodeByCondition(SKUSearchParams param)
{
List<SKUEncode> result = null;
try
{
DataTable dt = _dal.GetSKUEncodeByCondition(param);
if (dt != null && dt.Rows.Count > 0)
{
result = new List<SKUEncode>();
SKUEncode encode;
foreach (DataRow dr in dt.Rows)
{
Guid ID = dr.GetFieldValue<Guid>("ID");
string code = dr.GetFieldValue<string>("CODE");
string name = dr.GetFieldValue<string>("NAME");
Guid ATT3ID = dr.GetFieldValue<Guid>("ATT3ID");
Guid ATT4ID = dr.GetFieldValue<Guid>("ATT4ID");
Guid ATT5ID = dr.GetFieldValue<Guid>("ATT5ID");
Guid ATT6ID = dr.GetFieldValue<Guid>("ATT6ID");
Guid ATT7ID = dr.GetFieldValue<Guid>("ATT7ID");
encode = new SKUEncode(ID)
{
Code = code,
Name = name,
Att3ID = ATT3ID,
Att4ID = ATT4ID,
Att5ID = ATT5ID,
Att6ID = ATT6ID,
Att7ID = ATT7ID,
};
result.Add(encode);
}
}
}
catch (Exception e)
{
string excepMsg = string.Format(@"{0},{1}", DateTime.Now.ToString(), e.Message);
Trace.TraceError(excepMsg);
throw new Exception("获取SKU编码列表出错:BLLSKUEncodeManagement.GetSKUEncodeByCondition(SKUSearchParams param)", e);
}
return result;
}
public DataTable GetSKUEncodeByCondition(SKUSearchParams param)
{
DataTable result = new DataTable();
StringBuilder sbSql = new StringBuilder();
// sbSql.Append(@"SELECT C.*, A.SKUCID
// FROM SKUENCODE C
// LEFT JOIN SKUATT A ON C.ATT3ID = A.ID
// WHERE 1 = 1 ");
//if(param.IDTwo != Guid.Empty)
//{
// sbSql.Append(@" AND SKUCID = @SKUCID ");
//}
sbSql.Append(@"WITH SUBQRY(ID)
AS
(
SELECT ID
FROM SKUCGY
WHERE ID = @SelectedSKUCID
UNION ALL
SELECT SKUCGY.ID
FROM SKUCGY, SUBQRY
WHERE SKUCGY.PID = SUBQRY.ID
)
SELECT C.*, A.SKUCID
FROM SKUENCODE C
LEFT JOIN SKUATT A ON C.ATT3ID = A.ID
WHERE A.SKUCID IN
(
SELECT ID FROM SUBQRY
) ");
if(param.Att3 != Guid.Empty)
{
sbSql.Append(@" AND ATT3ID = @ATT3ID ");
}
if (param.Att4 != Guid.Empty)
{
sbSql.Append(@" AND ATT4ID = @ATT4ID ");
}
if (param.Att5 != Guid.Empty)
{
sbSql.Append(@" AND ATT5ID = @ATT5ID ");
}
if (param.Att6 != Guid.Empty)
{
sbSql.Append(@" AND ATT6ID = @ATT6ID ");
}
if (param.Att7 != Guid.Empty)
{
sbSql.Append(@" AND ATT7ID = @ATT7ID ");
}
if(!string.IsNullOrWhiteSpace(param.Keyword))
{
//sbSql.Append(@" AND C.CODE + C.NAME LIKE '%@Keyword%' ");
sbSql.Append(string.Format(" AND C.CODE + C.NAME LIKE '%{0}%' ", param.Keyword));
}
using (DbCommand cmd = _database.GetSqlStringCommand(sbSql.ToString()))
{
//if (param.IDTwo != Guid.Empty)
//{
// _database.AddInParameter(cmd, "@SKUCID", DbType.Guid, param.IDTwo);
//}
_database.AddInParameter(cmd, "@SelectedSKUCID", DbType.Guid, param.SelectedSKUCID);
if (param.Att3 != Guid.Empty)
{
_database.AddInParameter(cmd, "@ATT3ID", DbType.Guid, param.Att3);
}
if (param.Att4 != Guid.Empty)
{
_database.AddInParameter(cmd, "@ATT4ID", DbType.Guid, param.Att4);
}
if (param.Att5 != Guid.Empty)
{
_database.AddInParameter(cmd, "@ATT5ID", DbType.Guid, param.Att5);
}
if (param.Att6 != Guid.Empty)
{
_database.AddInParameter(cmd, "@ATT6ID", DbType.Guid, param.Att6);
}
if (param.Att7 != Guid.Empty)
{
_database.AddInParameter(cmd, "@ATT7ID", DbType.Guid, param.Att7);
}
//if (!string.IsNullOrWhiteSpace(param.Keyword))
//{
// _database.AddInParameter(cmd, "@Keyword", DbType.String, param.Keyword);
//}
using (IDataReader reader = _database.ExecuteReader(cmd))
{
result.Load(reader);
}
}
return result;
}
private void CmdSearchExecute()
{
if(this.RefreshingVisibility == Visibility.Visible)
{
return;
}
this.ItemsSource.Clear();
this.RefreshingVisibility = Visibility.Visible;
SKUSearchParams param = new SKUSearchParams();
//param.IDOne = this.SelectedSKUCGY.PID.HasValue ? this.SelectedSKUCGY.PID.Value : Guid.Empty;
//param.IDTwo = this.SelectedSKUCGY.ID;
param.SelectedSKUCID = this.SelectedSKUCGY == null ? Guid.Empty : this.SelectedSKUCGY.ID;
param.Att3 = this.SeletedATT3.ID;
param.Att4 = this.SeletedATT4.ID;
param.Att5 = this.SeletedATT5.ID;
param.Att6 = this.SeletedATT6.ID;
param.Att7 = this.SeletedATT7.ID;
param.Keyword = this.SKUKeyword;
_factorySKUEncode.StartNew(this.GetSKUEncodeByCondition, param).ContinueWith(this.GetSKUEncodeByConditionComplete, _schedule);
}