public List<ElementDetailQuery> QueryAllWares(ElementDetailQuery query, out int totalCount)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
StringBuilder sqlContent = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
try
{
sqlCount.AppendFormat(" select count(ed.element_id) as totalCount ");
sqlContent.AppendFormat("select p.product_id, c.parametername as element_type,ed.element_id,ed.element_name,ed.category_id,ed.category_name,ed.element_content,ed.element_link_url,d.parametername as element_link_mode,ed.element_sort,ed.element_status,ed.element_start,ed.element_end,ed.element_remark ");
sql.Append(" from element_detail ed ");
sql.Append(" join area_packet ap on ap.packet_id=ed.packet_id ");
sql.Append(" LEFT JOIN product p ON p.product_id = ed.element_content ");
sql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_type') c on ap.element_type=c.parametercode");
sql.Append(" left join ( select parametername,parametercode from t_parametersrc where parametertype='element_link_mode') d on ed.element_link_mode=d.parametercode");
sqlWhere.Append(" where ap.element_type=3 ");
if (query.packet_id != 0)
{
sqlWhere.AppendFormat(" and packet_id={0}", query.packet_id);
}
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sql.ToString() + sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
sqlWhere.AppendFormat(" limit {0},{1} ;", query.Start, query.Limit);
}
return _access.getDataTableForObj<ElementDetailQuery>(sqlContent.ToString() + sql.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("ElementDetailDao-->QueryAllWares-->" + sqlContent.ToString() + sql.ToString() + sqlWhere.ToString(), ex);
}
}
public List<ElementDetailQuery> QueryAll(ElementDetailQuery query, out int totalCount)
{
try
{
query.Replace4MySQL();
StringBuilder TempCol = new StringBuilder("SELECT p.product_id,ap.element_type,ap.packet_status,p.product_name,p.product_status,ap.packet_name, bd.element_id,ap.element_type,");//c.parametername as element_type_name,
TempCol.Append(" bd.element_content,bd.packet_id,bd.element_name,bd.element_link_url,bd.element_link_mode,bd.element_remark,bd.element_sort,bd.element_status, bd.element_link_mode,");//d.parametername as element_linkmode,
TempCol.Append(" bd.element_start,bd.element_end,bd.element_createdate,bd.element_updatedate,bd.category_id,bd.category_name,bd.element_img_big ");
StringBuilder tempCount = new StringBuilder("select count(bd.element_id) as totalCount ");
StringBuilder mainSql = new StringBuilder(" FROM element_detail bd ");
mainSql.Append(" join area_packet ap on ap.packet_id=bd.packet_id");
mainSql.Append(" LEFT JOIN product p ON p.product_id = bd.element_content ");
// mainSql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_type') c on ap.element_type=c.parametercode");
// mainSql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_link_mode') d on bd.element_link_mode=d.parametercode");
StringBuilder condi = new StringBuilder();
condi.Append(" and element_status!=2 ");
if (query.packet_id != 0)
{
condi.AppendFormat(" and ap.packet_id={0} ", query.packet_id);
}
if (query.element_type != 0)
{
condi.AppendFormat(" and ap.element_type={0} ", query.element_type);
}
if (!string.IsNullOrEmpty(query.key))
{
condi.AppendFormat(" and ( bd.element_name like N'%{0}%' ", query.key);
condi.AppendFormat(" or bd.element_remark like N'%{0}%' ", query.key);
condi.AppendFormat(" or ap.packet_name like N'%{0}%' ", query.key);
condi.AppendFormat(" or bd.element_content like N'%{0}%') ", query.key);
}
if (!string.IsNullOrEmpty(query.searchcate))
{
condi.AppendFormat(" and bd.category_name like N'%{0}%' ", query.searchcate);
}
if (query.product_status != 0 && query.element_type == 3)
{
if (query.product_status == 5)
{
condi.Append(" and p.product_status=5 ");
}
else
{
condi.AppendFormat(" and p.product_status <> 5 ");
}
}
if (condi.Length > 0)
{
mainSql.Append(" where ");
mainSql.Append(condi.ToString().TrimStart().Remove(0, 3));
}
mainSql.AppendFormat(" order by bd.element_id desc ");
totalCount = 0;
if (query.IsPage)
{
sql.Append(tempCount.ToString() + mainSql.ToString());
System.Data.DataTable _dt = _access.getDataTable(sql.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
mainSql.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
IParametersrcImplDao _paradao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _paradao.QueryParametersrcByTypes("element_type", "element_link_mode");
List<ElementDetailQuery> list = _access.getDataTableForObj<ElementDetailQuery>(TempCol.ToString() + mainSql.ToString());
foreach (ElementDetailQuery q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "element_type" && m.ParameterCode == q.element_type.ToString());
var blist = parameterList.Find(m => m.ParameterType == "element_link_mode" && m.ParameterCode == q.element_link_mode.ToString());
if (alist != null)
{
q.element_type_name = alist.parameterName;
}
if (blist != null)
{
q.element_linkmode = blist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ElementDetailDao-->QueryAll-->" + ex.Message + sql.ToString(), ex);
}
}
public int UpdateStatus(ElementDetailQuery model)
{
model.Replace4MySQL();
try
{
sql.AppendFormat(@"UPDATE element_detail set element_status='{1}',element_updatedate='{2}' ,update_userid='{3}' where element_id='{0}' ",
model.element_id, model.element_status, CommonFunction.DateTimeToString(model.element_updatedate), model.update_userid);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("ElementDetailDao-->UpdateStatus-->" + ex.Message + sql.ToString(), ex);
}
}
