/// <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); } }