public static int GetOpinionListCount(string searchText) { var db = DB.GetDB(); QParamCollection paramCollection = new QParamCollection(); string searchQuery = ""; if (!string.IsNullOrEmpty(searchText)) { //надо делить на слова string[] words = searchText.Split(' '); StringBuilder searchSelect = new StringBuilder(); int wordIndex = 0; searchSelect.Append("AND ("); foreach (var word in words) { if (wordIndex > 0) { searchSelect.Append(" OR "); } searchSelect.AppendFormat("like_text ilike @st{0} OR dislike_text ilike @st{0}", wordIndex); paramCollection.AddParam("st" + wordIndex, DbType.String, "%" + word + "%"); wordIndex++; } searchSelect.Append(")"); searchQuery = searchSelect.ToString(); } var cmd = db.GetCommand(string.Format(@"SELECT COUNT(o.id) FROM bookmedica.opinion o LEFT JOIN bookmedica.doctor d ON d.id=o.on_id AND o.class_id={0} WHERE o.moderation={1} AND d.moderation={1} {2}", Values.DOCTOR_CLASS_ID, Values.MODERATION_STATUS_OK, searchQuery)); db.AddInParameters(cmd, paramCollection.ParamList); DataRow data = executeDataRow(cmd); return(DataRowHelper.GetIntValue(data, "count")); }
public static List <AdminOpinion> GetOpinionList(int page, string searchText) { QParamCollection paramCollection = new QParamCollection(); if (page < 1) { page = 1; } int offset = (page - 1) * Values.OPINION_PAGE_SIZE; List <AdminOpinion> res = new List <AdminOpinion>(); var db = DB.GetDB(); string searchQuery = ""; if (!string.IsNullOrEmpty(searchText)) { //надо делить на слова string[] words = searchText.Split(' '); StringBuilder searchSelect = new StringBuilder(); int wordIndex = 0; searchSelect.Append("AND ("); foreach (var word in words) { if (wordIndex > 0) { searchSelect.Append(" OR "); } searchSelect.AppendFormat("like_text ilike @st{0} OR dislike_text ilike @st{0}", wordIndex); paramCollection.AddParam("st" + wordIndex, DbType.String, "%" + word + "%"); wordIndex++; } searchSelect.Append(")"); searchQuery = searchSelect.ToString(); } //20160112- отзывы только по врачам, клиник нет /*var cmd = db.GetCommand(string.Format(@"SELECT c.name AS clinic_name,c.photo AS clinic_photo, d.full_name AS doctor_name,d.photo AS doctor_photo,o.* FROM bookmedica.opinion o * LEFT JOIN bookmedica.doctor d ON d.id=o.on_id AND o.class_id={1} * LEFT JOIN bookmedica.clinic c ON c.id=o.on_id AND o.class_id={2} * WHERE c.city_id={0} OR d.city_id={0} ORDER BY id DESC LIMIT {3} OFFSET {4}", cityID, Values.DOCTOR_CLASS_ID, Values.CLINIC_CLASS_ID, Values.OPINION_PAGE_SIZE, offset)); */ var cmd = db.GetCommand(string.Format(@"SELECT d.specialization AS doctor_specialization, d.full_name AS doctor_name,d.photo AS doctor_photo,o.* FROM bookmedica.opinion o LEFT JOIN bookmedica.doctor d ON d.id=o.on_id AND o.class_id={0} WHERE o.moderation={4} AND d.moderation={4} {5} ORDER BY create_date DESC LIMIT {2} OFFSET {3}", Values.DOCTOR_CLASS_ID, Values.CLINIC_CLASS_ID, Values.OPINION_PAGE_SIZE, offset, Values.MODERATION_STATUS_OK, searchQuery)); db.AddInParameters(cmd, paramCollection.ParamList); DataTable data = db.ExecuteDataTable(cmd); if (data != null) { foreach (DataRow item in data.Rows) { res.Add(new AdminOpinion(item)); } } return(res); }