6 System.SysUtils, System.Classes, System.JSON, FireDAC.Comp.Client, Data.DB,
7 FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param,
8 FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf,
9 FireDAC.Stan.Async, FireDAC.DApt, FireDAC.UI.Intf, FireDAC.Stan.Def,
10 FireDAC.Stan.Pool, FireDAC.Phys, FireDAC.Phys.MySQL, FireDAC.Phys.MySQLDef,
11 FireDAC.Comp.DataSet, FireDAC.VCLUI.Wait, FireDAC.Comp.UI, FireDAC.Phys.FB,
15 TDataModule1 = class(TDataModule)
20 MagazineConnection: TFDConnection;
23 FDGUIxWaitCursor1: TFDGUIxWaitCursor;
25 writerwriterId: TIntegerField;
26 writerwriter: TWideStringField;
27 writermail: TWideStringField;
28 writerpassword: TWideStringField;
29 magListwriterId: TIntegerField;
30 magListmagId: TIntegerField;
31 newsmagId: TIntegerField;
32 newsno: TIntegerField;
34 newschanged: TBooleanField;
35 newsenabled: TBooleanField;
36 readerreaderId: TIntegerField;
37 readerreader: TWideStringField;
38 readermail: TWideStringField;
39 readerpassword: TWideStringField;
40 indexTablereaderId: TIntegerField;
41 indexTablemagId: TIntegerField;
43 magmagId: TIntegerField;
44 magmagName: TWideStringField;
45 magcomment: TWideStringField;
47 maglastDay: TDateField;
48 magenable: TBooleanField;
49 procedure DataModuleCreate(Sender: TObject);
52 function makeTable(Sender: TObject): TJSONObject;
55 procedure AddMagazine(id: integer; out Data: TJSONObject);
56 procedure backNumber(id: integer; out Data: TJSONObject);
57 function existsMail(mail: string): Boolean;
58 function checkUserPassword(id: integer; password: string): Boolean;
59 function createReaderId(Data: TJSONObject): Boolean;
60 procedure deleteReaderId(Data: TJSONObject);
61 function updateReaderId(Data: TJSONObject): Boolean;
62 procedure custData(id: integer; Data: TJSONObject);
63 procedure custView(id: integer; out Data: TJSONObject);
64 procedure deleteMagazine(id: integer);
65 procedure deleteNumber(id, num: integer);
66 procedure deleteWriter(id: integer);
67 procedure getView(id, num: integer; out Data: TJSONObject); overload;
68 procedure getView(id: integer; out Data: TJSONObject); overload;
69 procedure magazines(id: integer; out Data: TJSONObject);
70 procedure magListAll(out Data: TJSONObject);
71 procedure magData(id: integer; out Data: TJSONObject);
72 function magid(name: string): integer;
73 procedure magIdOff(id, magid: integer);
74 procedure magIdOn(id, magid: integer);
75 procedure createMagId(id: integer; out Data: TJSONObject);
76 procedure postMessage(id: integer; Data: TJSONObject);
77 procedure createWriterId(Data: TJSONObject);
78 procedure readerData(id: integer; out Data: TJSONObject);
79 procedure titleView(id: integer;out Data: TJSONObject);
80 procedure updateWriterId(id: integer; Data: TJSONObject);
81 procedure userView(id: integer; out Data: TJSONObject);
82 function loginReader(data: TJSONObject): integer;
83 function loginWriter(data: TJSONObject): integer;
87 DataModule1: TDataModule1;
91 uses System.Variants, System.Generics.Collections;
93 { %CLASSGROUP 'Vcl.Controls.TControl' }
97 procedure TDataModule1.AddMagazine(id: integer; out Data: TJSONObject);
102 FDQuery1.Open('select MAX(magId) as id from mag;');
103 i := FDQuery1.FieldByName('id').AsInteger + 1;
104 na := Data.Values['magName'].Value;
105 com := Data.Values['comment'].Value;
106 mag.AppendRecord([i, na, com, Date, Date, true]);
107 magList.AppendRecord([id, i]);
110 procedure TDataModule1.backNumber(id: integer; out Data: TJSONObject);
112 con = '
\82±
\82Ì
\8bL
\8e\96\82Í
\8cö
\8aJ
\90§
\8cÀ
\82ª
\82 \82è
\82Ü
\82·.';
118 Data := TJSONObject.Create;
120 mem := TStringList.Create;
124 SQL.Add('select file,enabled from news where magId = :id order by lastDay;');
125 Params.ParamByName('id').AsInteger := id;
129 blob := CreateBlobStream(FieldByName('text'), bmRead);
130 mem.LoadFromStream(blob);
131 if FieldByName('enabled').AsBoolean = true then
132 d.AddPair('text', mem.Text)
134 d.AddPair('text', con);
142 function TDataModule1.checkUserPassword(id: integer; password: string): Boolean;
144 result := writer.Lookup('id', id, 'password') = password;
147 procedure TDataModule1.createMagId(id: integer; out Data: TJSONObject);
152 FDQuery1.SQL.Add('select MAX(magId) as count from mag;');
154 i := FDQuery1.FieldByName('count').AsInteger + 1;
156 mag.FieldByName('magId').AsInteger := i;
157 mag.FieldByName('day').AsDateTime := Date;
158 mag.FieldByName('lastDay').AsDateTime := Date;
159 mag.FieldByName('magName').AsString := Data.Values['magName'].Value;
160 mag.FieldByName('comment').AsString := Data.Values['comment'].Value;
161 mag.FieldByName('enable').AsString := Data.Values['enable'].Value;
163 magList.AppendRecord([id, i]);
166 function TDataModule1.createReaderId(Data: TJSONObject): Boolean;
171 na := Data.Values['name'].Value;
172 ma := Data.Values['mail'].Value;
173 pa := Data.Values['password'].Value;
174 if reader.Locate('mail;password',VarArrayOf([na,ma])) = true then
176 FDQuery1.Open('select MAX(readerid) as id from reader;');
177 i := FDQuery1.FieldByName('id').AsInteger + 1;
178 reader.AppendRecord([i, na, ma, pa]);
185 procedure TDataModule1.DataModuleCreate(Sender: TObject);
187 tmp = 'create table if not exists ';
189 // FDQuery1.ExecSQL(tmp+'database(number int primary key, magId int, readerId int, writeId int);');
191 (tmp + 'mag(magId int primary key, magName varchar(20), comment varchar(50), day date, lastDay date, enable bool);');
193 (tmp + 'writer(writerId int primary key, writer varchar(20), mail varchar(20), password varchar(20));');
195 (tmp + 'reader(readerId int primary key, reader varchar(20), mail varchar(20), password varchar(20));');
197 (tmp + 'news(magId int, no int, day date, changed bool, enabled bool, primary key (magId,no));');
199 (tmp + 'indexTable(readerId int, magId int, primary key (readerId,magId));');
201 (tmp + 'magList(writerId int, magId int, primary key (writerId,magId));');
211 procedure TDataModule1.deleteMagazine(id: integer);
212 procedure main(Sender: TObject);
214 with Sender as TFDTable do
215 while Locate('magid',id) = true do
226 procedure TDataModule1.deleteNumber(id, num: integer);
228 if news.Locate('magId;newsId', VarArrayOf([id, num])) = true then
232 procedure TDataModule1.deleteReaderId(Data: TJSONObject);
237 id := Data.Values['id'].Value.ToInteger;
238 na := Data.Values['reader'].Value;
239 ma := Data.Values['mail'].Value;
240 pa := Data.Values['password'].Value;
241 if reader.Locate('readerid;reader;mail;password', VarArrayOf([id, na, ma, pa])
244 while indexTable.Locate('readerid',id) = true do
248 procedure TDataModule1.deleteWriter(id: integer);
250 if writer.Locate('writerid',id) = true then
252 while maglist.Locate('writerid',id) = true do
254 deleteMagazine(magList.FieldByName('magid').AsInteger);
259 function TDataModule1.existsMail(mail: string): Boolean;
261 result := (writer.Locate('mail',mail) = true)or(reader.Locate('mail',mail) = true);
264 procedure TDataModule1.readerData(id: integer; out Data: TJSONObject);
266 if reader.Locate('readerid', id) = true then
268 Data := TJSONObject.Create;
269 Data.AddPair('name', reader.FieldByName('reader').AsString);
270 Data.AddPair('mail', reader.FieldByName('mail').AsString);
274 procedure TDataModule1.userView(id: integer; out Data: TJSONObject);
277 list: TList<integer>;
281 list := TList<integer>.Create;
283 FDQuery1.SQL.Add('select * from indexTable where readerid = :id;');
284 FDQuery1.Params.ParamByName('id').AsInteger := id;
286 while FDQuery1.Eof = false do
288 list.Add(FDQuery1.FieldByName('magid').AsInteger);
291 ar:=TJSONArray.Create;
298 Data := TJSONObject.Create;
299 data.AddPair('mag',ar);
302 procedure TDataModule1.custData(id: integer; Data: TJSONObject);
304 if writer.Locate('writerid', id) = true then
306 Data.AddPair('name', writer.FieldByName('writer').AsString);
307 Data.AddPair('mail', writer.FieldByName('mail').AsString);
311 procedure TDataModule1.custView(id: integer; out Data: TJSONObject);
314 list: TList<integer>;
316 Data := TJSONObject.Create;
317 list := TList<integer>.Create;
319 FDQuery1.SQL.Add('select * from maglist where readerid = :id;');
320 FDQuery1.Params.ParamByName('id').AsInteger := id;
322 while FDQuery1.Eof = false do
324 list.Add(FDQuery1.FieldByName('magid').AsInteger);
332 procedure TDataModule1.getView(id, num: integer; out Data: TJSONObject);
337 Add('select updated,day,file from news');
338 Add(' where magId = :id and newsId = :num');
339 Add(' order by day;');
341 with FDQuery1.Params do
343 ParamByName('id').AsInteger := id;
344 ParamByName('num').AsInteger := num;
347 Data := makeTable(FDQuery1);
350 procedure TDataModule1.getView(id: integer; out Data: TJSONObject);
355 Add('select updated,day,file from indexTable,news');
356 Add(' where readerId = :id and indexTable.magId = news.magId');
357 Add(' and enabled = true order by day;');
359 FDQuery1.Params.ParamByName('id').AsInteger := id;
361 Data := makeTable(FDQuery1);
364 function TDataModule1.loginReader(data: TJSONObject): integer;
368 ma:=Data.Values['mail'].Value;
369 pa:=Data.Values['password'].Value;
370 if reader.Locate('mail;password',VarArrayOf([ma,pa])) = true then
371 result:=reader.FieldByName('readerid').AsInteger
376 function TDataModule1.loginWriter(data: TJSONObject): integer;
380 ma:=Data.Values['mail'].Value;
381 pa:=Data.Values['password'].Value;
382 if writer.Locate('mail;password',VarArrayOf([ma,pa])) = true then
383 result:=writer.FieldByName('writerid').AsInteger
388 procedure TDataModule1.magData(id: integer; out Data: TJSONObject);
391 FDQuery1.SQL.Add('select * from mag where magid = :id;');
392 FDQuery1.ParamByName('id').AsInteger := id;
394 if FDQuery1.FieldByName('enable').AsBoolean = true then
396 Data := TJSONObject.Create;
397 Data.AddPair('name', FDQuery1.FieldByName('magName').AsString);
398 Data.AddPair('comment', FDQuery1.FieldByName('comment').AsString);
399 Data.AddPair('day', FDQuery1.FieldByName('day').AsString);
400 Data.AddPair('last', FDQuery1.FieldByName('lastDay').AsString);
402 FDQuery1.SQL.Add('select COUNT(*) as count from indextable where magid = :id;');
403 FDQuery1.ParamByName('id').AsInteger:=id;
405 Data.AddPair('count', FDQuery1.FieldByName('count').AsString);
409 function TDataModule1.magid(name: string): integer;
411 result := mag.Lookup('magname', name, 'magid');
414 procedure TDataModule1.magIdOff(id, magid: integer);
416 if indexTable.Locate('readerId;magId', VarArrayOf([id, magid])) = true then
420 procedure TDataModule1.magIdOn(id, magid: integer);
422 if (writer.Locate('readerid', id) = true) and
423 (mag.Locate('magid', magid) = true) then
424 indexTable.AppendRecord([id, magid]);
427 procedure TDataModule1.magListAll(out Data: TJSONObject);
436 ar := TJSONArray.Create;
437 FDQuery1.Open('select magId,COUNT(*) as count from indexTable group by magId');
438 while mag.Eof = false do
440 i:=mag.FieldByName('magId').AsInteger;
441 v:=FDQuery1.Lookup('magId',i,'count');
442 if VarIsNull(v) = true then
444 js := TJSONObject.Create;
445 js.AddPair('magName', mag.FieldByName('magName').AsString);
446 js.AddPair('comment', mag.FieldByName('comment').AsString);
447 js.AddPair('day', mag.FieldByName('day').AsString);
448 js.AddPair('lastDay', mag.FieldByName('lastDay').AsString);
449 js.AddPair('count',v);
453 Data := TJSONObject.Create;
455 Data.AddPair('items', ar)
457 Data.AddPair('items', TJSONFalse.Create);
458 if mag.FieldByName('enable').AsBoolean = true then
459 val := TJSONTrue.Create
461 val := TJSONFalse.Create;
462 Data.AddPair('enable', val);
465 procedure TDataModule1.magazines(id: integer; out Data: TJSONObject);
470 list: TList<integer>;
475 FDQuery1.SQL.Add('select * from maglist where writerid = :id;');
476 FDQuery1.Params.ParamByName('id').AsInteger := id;
478 ar := TJSONArray.Create;
479 list:=TList<integer>.Create;
480 while FDQuery1.Eof = false do
482 list.Add(FDQuery1.FieldByName('magId').AsInteger);
494 val := TJSONFalse.Create;
498 Data := TJSONObject.Create;
499 Data.AddPair('mag', val);
502 function TDataModule1.makeTable(Sender: TObject): TJSONObject;
507 result := TJSONObject.Create;
508 mem := TStringList.Create;
509 with Sender as TFDQuery do
514 if FieldByName('updated').AsBoolean = true then
515 result.AddPair('hint', Format('
\82±
\82Ì
\8bL
\8e\96\82Í
\8dX
\90V
\82³
\82ê
\82Ü
\82µ
\82½:(%s)
\93ú.',
516 [FieldByName('day').AsString]));
517 blob := CreateBlobStream(FieldByName('file'), bmRead);
518 mem.LoadFromStream(blob);
520 result.AddPair('text', mem.Text);
527 procedure TDataModule1.postMessage(id: integer; Data: TJSONObject);
532 FDQuery1.SQL.Add('select MAX(newsId) as id from news where magId = :id;');
533 FDQuery1.Params.ParamByName('id').AsInteger := id;
535 if FDQuery1.RecordCount > 0 then
537 i := FDQuery1.FieldByName('id').AsInteger + 1;
538 news.AppendRecord([id, i, false, Date, Data.Values['file'], true]);
542 procedure TDataModule1.createWriterId(Data: TJSONObject);
547 ma := Data.Values['mail'].Value;
548 if writer.Locate('mail', ma) = false then
550 FDQuery1.Open('select MAX(writerId) as id from writer;');
551 i := FDQuery1.FieldByName('id').AsInteger + 1;
552 na := Data.Values['name'].Value;
553 ma := Data.Values['mail'].Value;
554 pa := Data.Values['password'].Value;
555 writer.AppendRecord([i, na, ma, pa]);
559 procedure TDataModule1.titleView(id: integer;out Data: TJSONObject);
564 Data:=TJSONObject.Create;
567 FDQuery1.SQL.Add('select * from mag where magid = :id;');
568 FDQuery1.Params.ParamByName('id').AsInteger := id;
573 for i := 0 to Fields.Count - 1 do
574 d.AddPair(Fields[i].FieldName, Fields[i].AsString);
579 function TDataModule1.updateReaderId(Data: TJSONObject): Boolean;
583 na:=Data.Values['reader'].Value;
584 ma:=Data.Values['mail'].Value;
585 pa:=data.Values['password'].Value;
586 if reader.Locate('id',data.Values['id'].Value.ToInteger) = true then
590 FieldByName('reader').AsString:=na;
591 FieldByName('mail').AsString:=ma;
592 FieldByName('password').AsString:=pa;
600 procedure TDataModule1.updateWriterId(id: integer; Data: TJSONObject);
604 na := Data.Values['name'].Value;
605 ma := Data.Values['mail'].Value;
606 pa := Data.Values['password'].Value;
610 FieldByName('writerId').AsInteger := id;
611 FieldByName('writer').AsString := na;
612 FieldByName('mail').AsString := ma;
613 FieldByName('password').AsString := pa;