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