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 checkUserPassword(id: integer; password: string): Boolean;
58 procedure createReaderId(Data: TJSONObject);
59 procedure deleteReaderId(Data: TJSONObject);
60 procedure updateReaderId(Data: TJSONObject);
61 procedure custData(id: integer; Data: TJSONObject);
62 procedure custView(id: integer; out Data: TJSONObject);
63 procedure deleteMagazine(id: integer);
64 procedure deleteNumber(id, num: integer);
65 procedure deleteWriter(id: integer);
66 procedure getView(id, num: integer; out Data: TJSONObject); overload;
67 procedure getView(id: integer; out Data: TJSONObject); overload;
68 procedure magazines(id: integer; out Data: TJSONObject);
69 procedure magListAll(out Data: TJSONObject);
70 procedure magData(id: integer; out Data: TJSONObject);
71 function magid(name: string): integer;
72 procedure magIdOff(id, magid: integer);
73 procedure magIdOn(id, magid: integer);
74 procedure createMagId(id: integer; out Data: TJSONObject);
75 procedure postMessage(id: integer; Data: TJSONObject);
76 procedure createWriterId(Data: TJSONObject);
77 procedure readerData(id: integer; out Data: TJSONObject);
78 procedure titleView(id: integer; Data: TJSONObject);
79 procedure updateWriterId(id: integer; Data: TJSONObject);
80 procedure userView(id: integer; out Data: TJSONObject);
84 DataModule1: TDataModule1;
88 uses System.Variants, System.Generics.Collections;
90 { %CLASSGROUP 'Vcl.Controls.TControl' }
94 procedure TDataModule1.AddMagazine(id: integer; out Data: TJSONObject);
99 FDQuery1.Open('select MAX(magId) as id from mag;');
100 i := FDQuery1.FieldByName('id').AsInteger + 1;
101 na := Data.Values['magName'].Value;
102 com := Data.Values['comment'].Value;
103 mag.AppendRecord([i, na, com, Date, Date, true]);
104 magList.AppendRecord([id, i]);
107 procedure TDataModule1.backNumber(id: integer; out Data: TJSONObject);
109 con = '
\82±
\82Ì
\8bL
\8e\96\82Í
\8cö
\8aJ
\90§
\8cÀ
\82ª
\82 \82è
\82Ü
\82·.';
115 Data := TJSONObject.Create;
117 mem := TStringList.Create;
121 SQL.Add('select file,enabled from news where magId = :id order by lastDay;');
122 Params.ParamByName('id').AsInteger := id;
126 blob := CreateBlobStream(FieldByName('text'), bmRead);
127 mem.LoadFromStream(blob);
128 if FieldByName('enabled').AsBoolean = true then
129 d.AddPair('text', mem.Text)
131 d.AddPair('text', con);
139 function TDataModule1.checkUserPassword(id: integer; password: string): Boolean;
141 result := writer.Lookup('id', id, 'password') = password;
144 procedure TDataModule1.createMagId(id: integer; out Data: TJSONObject);
149 FDQuery1.SQL.Add('select MAX(magId) as count from mag;');
151 i := FDQuery1.FieldByName('count').AsInteger + 1;
153 mag.FieldByName('magId').AsInteger := i;
154 mag.FieldByName('day').AsDateTime := Date;
155 mag.FieldByName('lastDay').AsDateTime := Date;
156 mag.FieldByName('magName').AsString := Data.Values['magName'].Value;
157 mag.FieldByName('comment').AsString := Data.Values['comment'].Value;
158 mag.FieldByName('enable').AsString := Data.Values['enable'].Value;
160 magList.AppendRecord([id, i]);
163 procedure TDataModule1.createReaderId(Data: TJSONObject);
168 FDQuery1.Open('select MAX(readerid) as id from reader;');
169 i := FDQuery1.FieldByName('id').AsInteger + 1;
170 na := Data.Values['name'].Value;
171 ma := Data.Values['mail'].Value;
172 pa := Data.Values['password'].Value;
173 reader.AppendRecord([i, na, ma, pa]);
176 procedure TDataModule1.DataModuleCreate(Sender: TObject);
178 tmp = 'create table if not exists ';
180 // FDQuery1.ExecSQL(tmp+'database(number int primary key, magId int, readerId int, writeId int);');
182 (tmp + 'mag(magId int primary key, magName varchar(20), comment varchar(50), day date, lastDay date, enable bool);');
184 (tmp + 'writer(writerId int primary key, writer varchar(20), mail varchar(20), password varchar(20));');
186 (tmp + 'reader(readerId int primary key, reader varchar(20), mail varchar(20), password varchar(20));');
188 (tmp + 'news(magId int, no int, day date, changed bool, enabled bool, primary key (magId,no));');
190 (tmp + 'indexTable(readerId int, magId int, primary key (readerId,magId));');
192 (tmp + 'magList(writerId int, magId int, primary key (writerId,magId));');
202 procedure TDataModule1.deleteMagazine(id: integer);
203 procedure main(Sender: TObject);
205 with Sender as TFDTable do
206 while Locate('magid',id) = true do
217 procedure TDataModule1.deleteNumber(id, num: integer);
219 if news.Locate('magId;newsId', VarArrayOf([id, num])) = true then
223 procedure TDataModule1.deleteReaderId(Data: TJSONObject);
228 id := Data.Values['id'].Value.ToInteger;
229 na := Data.Values['reader'].Value;
230 ma := Data.Values['mail'].Value;
231 pa := Data.Values['password'].Value;
232 if reader.Locate('readerid;reader;mail;password', VarArrayOf([id, na, ma, pa])
235 while indexTable.Locate('readerid',id) = true do
239 procedure TDataModule1.deleteWriter(id: integer);
241 if writer.Locate('writerid',id) = true then
243 while maglist.Locate('writerid',id) = true do
245 deleteMagazine(magList.FieldByName('magid').AsInteger);
246 magList.Delete; ///////
250 procedure TDataModule1.readerData(id: integer; out Data: TJSONObject);
252 if reader.Locate('readerid', id) = true then
254 Data := TJSONObject.Create;
255 Data.AddPair('name', reader.FieldByName('reader').AsString);
256 Data.AddPair('mail', reader.FieldByName('mail').AsString);
260 procedure TDataModule1.userView(id: integer; out Data: TJSONObject);
263 list: TList<integer>;
265 Data := TJSONObject.Create;
266 list := TList<integer>.Create;
268 FDQuery1.SQL.Add('select * from indexTable where readerid = :id;');
269 FDQuery1.Params.ParamByName('id').AsInteger := id;
271 while FDQuery1.Eof = false do
272 list.Add(FDQuery1.FieldByName('magid').AsInteger);
278 procedure TDataModule1.custData(id: integer; Data: TJSONObject);
280 if writer.Locate('writerid', id) = true then
282 Data.AddPair('name', writer.FieldByName('writer').AsString);
283 Data.AddPair('mail', writer.FieldByName('mail').AsString);
287 procedure TDataModule1.custView(id: integer; out Data: TJSONObject);
290 list: TList<integer>;
292 Data := TJSONObject.Create;
293 list := TList<integer>.Create;
295 FDQuery1.SQL.Add('select * from maglist where readerid = :id;');
296 FDQuery1.Params.ParamByName('id').AsInteger := id;
298 while FDQuery1.Eof = false do
300 list.Add(FDQuery1.FieldByName('magid').AsInteger);
308 procedure TDataModule1.getView(id, num: integer; out Data: TJSONObject);
313 Add('select updated,day,file from news');
314 Add(' where magId = :id and newsId = :num');
315 Add(' order by day;');
317 with FDQuery1.Params do
319 ParamByName('id').AsInteger := id;
320 ParamByName('num').AsInteger := num;
323 Data := makeTable(FDQuery1);
326 procedure TDataModule1.getView(id: integer; out Data: TJSONObject);
331 Add('select updated,day,file from indexTable,news');
332 Add(' where readerId = :id and indexTable.magId = news.magId');
333 Add(' and enabled = true order by day;');
335 FDQuery1.Params.ParamByName('id').AsInteger := id;
337 Data := makeTable(FDQuery1);
340 procedure TDataModule1.magData(id: integer; out Data: TJSONObject);
343 FDQuery1.SQL.Add('select * from mag where magid = :id;');
344 FDQuery1.ParamByName('id').AsInteger := id;
346 if FDQuery1.FieldByName('enable').AsBoolean = true then
348 Data := TJSONObject.Create;
349 Data.AddPair('name', FDQuery1.FieldByName('magName').AsString);
350 Data.AddPair('comment', FDQuery1.FieldByName('comment').AsString);
351 Data.AddPair('day', FDQuery1.FieldByName('day').AsString);
352 Data.AddPair('last', FDQuery1.FieldByName('lastDay').AsString);
354 FDQuery1.SQL.Add('select COUNT(*) as count from indextable where magid = :id;');
355 FDQuery1.ParamByName('id').AsInteger:=id;
357 Data.AddPair('count', FDQuery1.FieldByName('count').AsString);
361 function TDataModule1.magid(name: string): integer;
363 result := mag.Lookup('magname', name, 'magid');
366 procedure TDataModule1.magIdOff(id, magid: integer);
368 if indexTable.Locate('readerId;magId', VarArrayOf([id, magid])) = true then
372 procedure TDataModule1.magIdOn(id, magid: integer);
374 if (writer.Locate('readerid', id) = true) and
375 (mag.Locate('magid', magid) = true) then
376 indexTable.AppendRecord([id, magid]);
379 procedure TDataModule1.magListAll(out Data: TJSONObject);
388 ar := TJSONArray.Create;
389 FDQuery1.Open('select magId,COUNT(*) as count from indexTable group by magId');
390 while mag.Eof = false do
392 i:=mag.FieldByName('magId').AsInteger;
393 v:=FDQuery1.Lookup('magId',i,'count');
394 if VarIsNull(v) = true then
396 js := TJSONObject.Create;
397 js.AddPair('magName', mag.FieldByName('magName').AsString);
398 js.AddPair('comment', mag.FieldByName('comment').AsString);
399 js.AddPair('day', mag.FieldByName('day').AsString);
400 js.AddPair('lastDay', mag.FieldByName('lastDay').AsString);
401 js.AddPair('count',v);
405 Data := TJSONObject.Create;
407 Data.AddPair('items', ar)
409 Data.AddPair('items', TJSONFalse.Create);
410 if mag.FieldByName('enable').AsBoolean = true then
411 val := TJSONTrue.Create
413 val := TJSONFalse.Create;
414 Data.AddPair('enable', val);
417 procedure TDataModule1.magazines(id: integer; out Data: TJSONObject);
422 list: TList<integer>;
427 FDQuery1.SQL.Add('select * from maglist where writerid = :id;');
428 FDQuery1.Params.ParamByName('id').AsInteger := id;
430 ar := TJSONArray.Create;
431 list:=TList<integer>.Create;
432 while FDQuery1.Eof = false do
434 list.Add(FDQuery1.FieldByName('magId').AsInteger);
446 val := TJSONFalse.Create;
450 Data := TJSONObject.Create;
451 Data.AddPair('mag', val);
454 function TDataModule1.makeTable(Sender: TObject): TJSONObject;
459 result := TJSONObject.Create;
460 mem := TStringList.Create;
461 with Sender as TFDQuery do
466 if FieldByName('updated').AsBoolean = true then
467 result.AddPair('hint', Format('
\82±
\82Ì
\8bL
\8e\96\82Í
\8dX
\90V
\82³
\82ê
\82Ü
\82µ
\82½:(%s)
\93ú.',
468 [FieldByName('day').AsString]));
469 blob := CreateBlobStream(FieldByName('file'), bmRead);
470 mem.LoadFromStream(blob);
472 result.AddPair('text', mem.Text);
479 procedure TDataModule1.postMessage(id: integer; Data: TJSONObject);
484 FDQuery1.SQL.Add('select MAX(newsId) as id from news where magId = :id;');
485 FDQuery1.Params.ParamByName('id').AsInteger := id;
487 if FDQuery1.RecordCount > 0 then
489 i := FDQuery1.FieldByName('id').AsInteger + 1;
490 news.AppendRecord([id, i, false, Date, Data.Values['file'], true]);
494 procedure TDataModule1.createWriterId(Data: TJSONObject);
499 ma := Data.Values['mail'].Value;
500 if writer.Locate('mail', ma) = false then
502 FDQuery1.Open('select MAX(writerId) as id from writer;');
503 i := FDQuery1.FieldByName('id').AsInteger + 1;
504 na := Data.Values['name'].Value;
505 ma := Data.Values['mail'].Value;
506 pa := Data.Values['password'].Value;
507 writer.AppendRecord([i, na, ma, pa]);
511 procedure TDataModule1.titleView(id: integer; Data: TJSONObject);
518 FDQuery1.SQL.Add('select * from mag where magid = :id;');
519 FDQuery1.Params.ParamByName('id').AsInteger := id;
524 for i := 0 to Fields.Count - 1 do
525 d.AddPair(Fields[i].FieldName, Fields[i].AsString);
530 procedure TDataModule1.updateReaderId(Data: TJSONObject);
534 na:=Data.Values['reader'].Value;
535 ma:=Data.Values['mail'].Value;
536 pa:=data.Values['password'].Value;
540 FieldByName('reader').AsString:=na;
541 FieldByName('mail').AsString:=ma;
542 FieldByName('password').AsString:=pa;
547 procedure TDataModule1.updateWriterId(id: integer; Data: TJSONObject);
551 na := Data.Values['name'].Value;
552 ma := Data.Values['mail'].Value;
553 pa := Data.Values['password'].Value;
557 FieldByName('writerId').AsInteger := id;
558 FieldByName('writer').AsString := na;
559 FieldByName('mail').AsString := ma;
560 FieldByName('password').AsString := pa;