public List<Parametersrc> Query(Parametersrc para)
{
para.Replace4MySQL();
StringBuilder strSql = new StringBuilder("select rowid,parametertype,parameterproperty,parametercode,parametername,remark,kdate,kuser,used,sort,topvalue from t_parametersrc where 1=1 ");
try
{
if (para.Rowid != 0)
{
strSql.AppendFormat(" and rowid='{0}' ", para.Rowid);
}
if (!string.IsNullOrEmpty(para.ParameterType))
{
strSql.AppendFormat(" and parametertype='{0}' ", para.ParameterType);
}
if (!string.IsNullOrEmpty(para.ParameterCode))
{
strSql.AppendFormat(" and parametercode='{0}' ", para.ParameterCode);
}
if (!string.IsNullOrEmpty(para.parameterName))
{
strSql.AppendFormat(" and parametername='{0}' ", para.parameterName);
}
if (!string.IsNullOrEmpty(para.TopValue))
{
strSql.AppendFormat(" and topvalue = '{0}'", para.TopValue);
}
strSql.AppendFormat(" and used='{0}' ", para.Used);
return _accessMySql.getDataTableForObj<Parametersrc>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("ParametersrcDap-->Query"+ex.Message+strSql.ToString(),ex);
}
}
public List<Parametersrc> QueryForTopValue(Parametersrc para)
{
para.Replace4MySQL();
StringBuilder strSql = new StringBuilder("select rowid,parametertype,parameterproperty,parametercode,parametername,remark,kdate,kuser,used,sort,topvalue");
try
{
strSql.AppendFormat(" from t_parametersrc where 1=1 and topvalue = '{0}' and parametertype='{1}' ", para.TopValue, para.ParameterType);
strSql.AppendFormat(" and used={0}", para.Used);
return _accessMySql.getDataTableForObj<Parametersrc>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("ParametersrcDap-->QueryForTopValue" + ex.Message + strSql.ToString(), ex);
}
}
public List<Parametersrc> GetIialgParametersrcList(Parametersrc store, out int totalCount)
{
store.Replace4MySQL();
StringBuilder sqlcount = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
StringBuilder strSql = new StringBuilder("select t.rowid,t.parametertype,t.parameterproperty,t.parametercode,t.parametername,t.remark,t.kdate,t.kuser,t.used,t.sort,tp.parameterName as 'TopValue' ");
sqlfrom.AppendFormat(" FROM t_parametersrc t LEFT JOIN t_parametersrc tp ON t.topValue = tp.rowid where 1=1 and tp.parameterType='wms_parameter' ");//parameterType='wms_parameter'
sqlcount.AppendFormat(@" select count(*) as searchtotal ");
//if (store.Rowid != 0)
//{
// sqlfrom.AppendFormat(" and rowid='{0}' ", store.Rowid);
//}
if (!string.IsNullOrEmpty(store.remark))//這裡remark做為搜獲的內容
{
sqlfrom.AppendFormat(" and (t.parameterType like'%{0}%' or t.parameterName like '%{0}%') ", store.remark);
}
sqlfrom.AppendFormat(" order by t.rowid desc ");
totalCount = 0;
if (store.IsPage)
{
DataTable _dt = _accessMySql.getDataTable(sqlcount.ToString() + sqlfrom.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["searchtotal"]);
}
sqlfrom.AppendFormat(" limit {0},{1}", store.Start, store.Limit);
}
try
{
return _accessMySql.getDataTableForObj<Parametersrc>(strSql.ToString() + sqlfrom.ToString());
}
catch (Exception ex)
{
throw new Exception(" ParametersrcDao-->GetIialgParametersrcList-->" + strSql.ToString() + sqlfrom.ToString() + ex.Message, ex);
}
}
public List<Parametersrc> GetParametersrcList(Parametersrc store, out int totalCount)
{
store.Replace4MySQL();
StringBuilder sqlcount = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
StringBuilder strSql = new StringBuilder("select rowid,parametertype,parameterproperty,parametercode,parametername,remark,kdate,kuser,used,sort,topvalue ");
sqlfrom.AppendFormat(" from t_parametersrc where 1=1 ");
sqlcount.AppendFormat(@" select count(*) as searchtotal ");
if (store.Rowid != 0)
{
sqlfrom.AppendFormat(" and rowid='{0}' ", store.Rowid);
}
if (!string.IsNullOrEmpty(store.ParameterType))
{
sqlfrom.AppendFormat(" and parameterType like N'%{0}%' ", store.ParameterType);
sqlfrom.AppendFormat(" or parameterProperty like N'%{0}%'", store.ParameterType);
sqlfrom.AppendFormat(" or parameterCode like N'%{0}%'", store.ParameterType);
sqlfrom.AppendFormat(" or parameterName like N'%{0}%'", store.ParameterType);
sqlfrom.AppendFormat(" or remark like N'%{0}%'", store.ParameterType);
}
sqlfrom.AppendFormat(" order by rowid desc ");
totalCount = 0;
if (store.IsPage)
{
DataTable _dt = _accessMySql.getDataTable(sqlcount.ToString() + sqlfrom.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["searchtotal"]);
}
sqlfrom.AppendFormat(" limit {0},{1}", store.Start, store.Limit);
}
try
{
return _accessMySql.getDataTableForObj<Parametersrc>(strSql.ToString() + sqlfrom.ToString());
}
catch (Exception ex)
{
throw new Exception(" ParametersrcDao-->GetParametersrcList-->" + strSql.ToString() + sqlfrom.ToString() + ex.Message, ex);
}
}