/// <summary>
/// 商品咨詢列表查詢
/// </summary>
/// <param name="store"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<ProductConsultQuery> Query(ProductConsultQuery store, out int totalCount)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strCondition = new StringBuilder();
StringBuilder strSelect = new StringBuilder();
StringBuilder strTemp = new StringBuilder();
try
{
store.Replace4MySQL();
totalCount = 0;
strSelect.AppendFormat("select pc.consult_id,pc.product_id,p.product_name,mu.user_username as manage_name,pc.user_id,uc.user_email,uc.user_name,pc.consult_info,tp.parameterName,pc.consult_type,pc.consult_answer,pc.is_sendEmail,pc.create_date,pc.answer_date,pc.answer_user,pc.status,pc.consult_url,pc.product_url,pc.item_id,pc.answer_status,pc.delay_reason,p.prod_classify ");
strCondition.AppendFormat(" from product_consult pc left join product p on p.product_id=pc.product_id left join users uc on uc.user_id=pc.user_id ");
strCondition.AppendFormat(" left join (SELECT parameterName,parameterCode,parameterType from t_parametersrc where parameterType='consultType' ) tp on pc.consult_type=tp.parameterCode ");
strCondition.AppendFormat(" left join manage_user mu on mu.user_id=pc.answer_user ");
strCondition.AppendFormat(" where 1=1 ");
if (store.answer_status != 0)
{
strCondition.AppendFormat(" and answer_status={0} ", store.answer_status);
}
if (store.consult_id != 0)
{
strCondition.AppendFormat(" and pc.consult_id ={0} ", store.consult_id);
}
if (store.huifu ==1)
{
strCondition.AppendFormat(" and status={0} ", 1);
}
if (store.huifu == 2)
{
strCondition.AppendFormat(" and status={0} ", 0);
}
if (store.product_name != "")
{
strCondition.AppendFormat(" and p.product_name like '%{0}%'", store.product_name);
}
if (store.product_id != 0)
{
strCondition.AppendFormat(" and p.product_id ={0} ", store.product_id);
}
if (store.consultType != "")
{
strCondition.AppendFormat(" and pc.consult_type in ({0}) ", store.consultType);
}
if (store.user_email != "")
{
strCondition.AppendFormat(" and uc.user_email like '%{0}%' ", store.user_email);
}
if (store.user_name != "")
{
strCondition.AppendFormat(" and uc.user_name like '%{0}%' ", store.user_name);
}
if (store.productIds != "")
{
strCondition.AppendFormat(" and p.prod_classify ={0} ", store.productIds);
}
if (store.beginTime != DateTime.MinValue)
{
strCondition.AppendFormat(" and pc.create_date>='{0}' ", store.beginTime.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (store.endTime != DateTime.MinValue)
{
strCondition.AppendFormat(" and pc.create_date<='{0}' ", store.endTime.ToString("yyyy-MM-dd HH:mm:ss"));
}
StringBuilder strTotalCount = new StringBuilder(" select count(pc.consult_id) as totalCount ");
if (store.IsPage)
{
strSql.AppendFormat(strTotalCount.ToString());
strSql.AppendFormat(strCondition.ToString());
System.Data.DataTable _dt = _access.getDataTable(strSql.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
strSql.AppendFormat(";");
strCondition.AppendFormat(" order by pc.consult_id desc ");
}
strTemp.AppendFormat(strSelect.ToString());
strTemp.AppendFormat(strCondition.ToString());
strTemp.AppendFormat(" limit {0},{1}", store.Start, store.Limit);
strTemp.AppendFormat(";");
strSql.AppendFormat(strTemp.ToString());
return _access.getDataTableForObj<ProductConsultQuery>(strTemp.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductConsultDao-->Query-->" + ex.Message + strSql.ToString(), ex);
}
}
/// <summary>
/// 回覆咨詢
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int SaveProductConsultAnswer(ProductConsultQuery query)
{
StringBuilder strSql = new StringBuilder();
try
{
query.Replace4MySQL();
strSql.AppendFormat("set sql_safe_updates = 0; update product_consult set answer_date='{0}',consult_answer='{1}',answer_user='******',answer_status='{3}',status='{4}',is_sendEmail='{5}' where consult_id='{6}'; set sql_safe_updates = 1;", query.answer_date.ToString("yyyy-MM-dd HH:mm:ss"), query.consult_answer, query.answer_user, query.answer_status, query.status,query.is_sendEmail, query.consult_id);
return _access.execCommand(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductConsultDao-->SaveProductConsultAnswer-->" + ex.Message + strSql.ToString(), ex);
}
}