void Browse_Organization::UpdateFunc()
{
QSqlQuery Query(Global.DataBase);
QString SearchString = QuoteString(FilterBy->text().trimmed());
QString Where = "";
QString QueryString;
QStringList SearchList;
Where = SearchPrepare(
" POSITION (LOWER(?) IN LOWER(o.\"Name\")) > 0 OR "
" POSITION (LOWER(?) IN LOWER(o.\"Phone\")) > 0 OR "
" POSITION (LOWER(?) IN LOWER(o.\"Contact\")) > 0 ",
&SearchList, SearchString);
QueryString =
"SELECT * "
"FROM public.\"Organization\" AS o " +
Where+
OrderBy;
Query.prepare(QueryString);
SearchBind(SearchList,&Query,3);
if (!Query.exec()) { GuiSqlError("Ошибка при работе с БД!"); return; }
Model->setQuery(Query);
Model->setHeaderData(1,Qt::Horizontal,tr("Наименование"));
Model->setHeaderData(2,Qt::Horizontal,tr("Телефон"));
Model->setHeaderData(4,Qt::Horizontal,tr("Контактное лицо"));
Table->setModel(Model);
Table->setColumnHidden(0,true);
Table->setColumnHidden(3,true);
Table->setColumnHidden(5,true);
TuneTable(Table);
Table->resizeColumnsToContents();
Table->horizontalHeader()->setSectionResizeMode(1,QHeaderView::Stretch);
Table->setSortingEnabled(true);
Table->horizontalHeader()->setSortIndicator(SortColumn, SortOrder);
connect(Table->selectionModel(),SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),this,SLOT(SlotCurChanged(QModelIndex,QModelIndex)));
int SelRow = FindRow((QSqlQueryModel*)(Table->model()),Cur);
Table->selectRow(SelRow);
}
void Browse_User::UpdateFunc()
{
QSqlQuery Query(Global.DataBase);
QString SearchString = QuoteString(FilterBy->text().trimmed());
QString Where = "";
QString QueryString;
QStringList SearchList;
if (Mode == buWidget) {
Where = SearchPrepare(
" POSITION (LOWER(?) IN LOWER(f.\"Name\")) > 0 OR "
" POSITION (LOWER(?) IN LOWER(f.\"Role\")) > 0 OR "
" POSITION (LOWER(?) IN LOWER(to_char(f.\"StartDate\"::date ,'DD.MM.YYYY' ))) > 0 OR "
" POSITION (LOWER(?) IN LOWER(f.\"StopDate\")) > 0 OR "
" POSITION (LOWER(?) IN LOWER(f.\"Position\")) > 0 ",
&SearchList, SearchString);
QString QueryString =
"SELECT * FROM ("
" SELECT "
" u.\"Id\" AS \"Id\", "
" u.\"Name\" AS \"Name\", "
" COALESCE(r.\"Name\",'-') AS \"Role\", "
" u.\"StartDate\" AS \"StartDate\", "
" CASE WHEN (u.\"StopDate\" = to_date('1980-01-01', 'YYYY-MM-DD')) THEN 'в штате' ELSE to_char(u.\"StopDate\",'DD-MM-YYYY') END AS \"StopDate\", "
" COALESCE(d.\"Name\",'-') AS \"Position\", "
" u.\"LongName\" AS \"LongName\" "
" FROM public.\"Users\" as u "
" LEFT OUTER JOIN public.\"Rubricator\" AS r ON (r.\"GroupId\" = 1 AND r.\"Id\" = u.\"Role\") "
" LEFT OUTER JOIN public.\"Rubricator\" AS d ON (d.\"GroupId\" = 2 AND d.\"Id\" = u.\"Position\") "
" GROUP BY u.\"Id\", r.\"Name\", d.\"Name\" "
") AS f "
+Where
+OrderBy;
Query.prepare(QueryString);
SearchBind(SearchList,&Query,5);
if (!Query.exec()) { GuiSqlError("Ошибка при работе с БД!"); return; }
Model->setQuery(Query);
Model->setHeaderData(0,Qt::Horizontal,tr("Ид"));
Model->setHeaderData(1,Qt::Horizontal,tr("ФИО"));
Model->setHeaderData(2,Qt::Horizontal,tr("Права"));
Model->setHeaderData(3,Qt::Horizontal,tr("Прием"));
Model->setHeaderData(4,Qt::Horizontal,tr("Увольнение"));
Model->setHeaderData(5,Qt::Horizontal,tr("Подразделение"));
Model->setHeaderData(6,Qt::Horizontal,tr("ФИО(полностью)"));
Table->setModel(Model);
Table->setItemDelegate(new NotEditableDelegate());
Table->setColumnHidden(0,true);
Table->setColumnHidden(6,true);
TuneTable(Table);
Table->setSortingEnabled(true);
Table->horizontalHeader()->setSortIndicator(SortColumn, SortOrder);
Table->resizeColumnsToContents();
Table->horizontalHeader()->setSectionResizeMode(1,QHeaderView::Stretch);
}
if (Mode == buService) {
Where = SearchPrepare(
" POSITION (LOWER(?) IN LOWER(f.\"Name\")) > 0",
&SearchList, SearchString);
QueryString =
"SELECT * FROM ( "
" SELECT u.\"Id\" AS \"Id\", "
" u.\"Name\" AS \"Name\" "
" FROM public.\"Users\" AS u, public.\"Service2Users\" AS s "
" WHERE u.\"Id\" = s.\"User\" AND s.\"Service\" ="+(*Ref)["Id"].toString()+" "
") AS f "
+Where
+OrderBy;
Query.prepare(QueryString);
SearchBind(SearchList,&Query,1);
if (!Query.exec()) { GuiSqlError("Ошибка при работе с БД!"); return; }
Model->setQuery(Query);
Model->setHeaderData(0,Qt::Horizontal,tr("Ид"));
Model->setHeaderData(1,Qt::Horizontal,tr("ФИО"));
Table->setModel(Model);
Table->setColumnHidden(0,true);
TuneTable(Table);
Table->setSortingEnabled(true);
Table->horizontalHeader()->setSortIndicator(SortColumn, SortOrder);
Table->resizeColumnsToContents();
Table->horizontalHeader()->setSectionResizeMode(1,QHeaderView::Stretch);
}
if (Mode == buSelect) {
Where = SearchPrepare(
" POSITION (LOWER(?) IN LOWER(f.\"Name\")) > 0",
&SearchList, SearchString);
QueryString =
" SELECT * FROM ( "
" SELECT u.\"Id\", u.\"Name\" FROM public.\"Users\" AS u "
" WHERE u.\"Id\" NOT IN ( "
" SELECT u1.\"Id\" FROM public.\"Users\" AS u1, public.\"Service2Users\" AS s "
" WHERE s.\"Service\" = 1 AND s.\"User\" = u1.\"Id\" "
" )) AS f "
+Where
+OrderBy;
Query.prepare(QueryString);
SearchBind(SearchList,&Query,1);
if (!Query.exec()) { GuiSqlError("Ошибка при работе с БД!"); return; }
Model->setQuery(Query);
Model->setHeaderData(0,Qt::Horizontal,tr("Ид"));
Model->setHeaderData(1,Qt::Horizontal,tr("ФИО"));
Table->setModel(Model);
Table->setColumnHidden(0,true);
TuneTable(Table);
Table->setSortingEnabled(true);
Table->horizontalHeader()->setSortIndicator(SortColumn, SortOrder);
Table->resizeColumnsToContents();
Table->horizontalHeader()->setSectionResizeMode(1,QHeaderView::Stretch);
}
connect(Table->selectionModel(),SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),this,SLOT(SlotCurChanged(QModelIndex,QModelIndex)));
int SelRow = FindRow((QSqlQueryModel*)(Table->model()),Cur);
Table->selectRow(SelRow);
}
static void Filter(const wchar_t *sFilter, bool bCaseSensitive)
{
if (sFilter!=NULL) {
size_t nFilter = wcslen(sFilter);
if (nFilter>0xFFFF) nFilter = 0xFFFF;
wchar_t *sFilterLower=NULL;
EditorUndoRedo eur = {sizeof(EditorUndoRedo)};
EditorSetPosition es = {sizeof(EditorSetPosition)};
char *Lines; //маска удалённых строк
Lines = MakeLineMask();
StackPush(Lines);
if (nFilter<=MAX_PATTERN_LEN) {
SearchPrepare(ctx, sFilter, bCaseSensitive);
}
else {
SearchHPrepare(ctx, sFilter, (WORD)nFilter, bCaseSensitive);
if (!bCaseSensitive) {
SetLength(&sFilterLower, (int)nFilter);
wcscpy(sFilterLower, sFilter);
CharLower(sFilterLower);
}
}
//непустой фильтр - создаём блок Undo и меняем текст внутри него
eur.Command=EUR_BEGIN;
Info.EditorControl(-1, ECTL_UNDOREDO, 0, &eur);
//ищем текст из диалога в каждой строке
EditorInfo einfo = {sizeof(EditorInfo)};
Info.EditorControl(-1, ECTL_GETINFO, 0, &einfo);
intptr_t nLines = einfo.TotalLines;
intptr_t line = nLines;
es.CurPos = 0;
es.CurTabPos = es.LeftPos = es.Overtype = es.TopScreenLine = -1;
while (--line>=0) {
es.CurLine = line;
Info.EditorControl(-1, ECTL_SETPOSITION, 0, &es);
EditorGetString egs = {sizeof(EditorGetString), -1};
Info.EditorControl(-1, ECTL_GETSTRING, 0, &egs);
const wchar_t *found;
if (nFilter <= MAX_PATTERN_LEN)
found = Search(ctx, egs.StringText);
else
found = SearchH(ctx,
bCaseSensitive ? sFilter : sFilterLower,
(WORD)nFilter,
egs.StringText,
egs.StringLength,
bCaseSensitive);
if (found==NULL) {
Info.EditorControl(-1, ECTL_DELETESTRING, 0, NULL);
MarkDeletedLine(Lines, line);
}
}
es.CurLine = 0;
es.CurPos = 0;
es.TopScreenLine = 0;
es.CurTabPos = es.LeftPos = es.Overtype = -1;
Info.EditorControl(-1, ECTL_SETPOSITION, 0, &es);
RemoveHighlight();
eur.Command=EUR_END;
Info.EditorControl(-1, ECTL_UNDOREDO, 0, &eur);
nChanges++;
if (nFilter<=MAX_PATTERN_LEN) {
SearchUnprepare(ctx,sFilter,bCaseSensitive);
}
else {
if (!bCaseSensitive)
SetLength(&sFilterLower, 0);
}
}
}
