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)
18 MagazineConnection: TFDConnection;
21 FDGUIxWaitCursor1: TFDGUIxWaitCursor;
23 writerwriterId: TIntegerField;
24 writerwriter: TWideStringField;
25 writermail: TWideStringField;
26 writerpassword: TWideStringField;
27 newsmagId: TIntegerField;
29 newschanged: TBooleanField;
30 newsenabled: TBooleanField;
31 readerreaderId: TIntegerField;
32 readerreader: TWideStringField;
33 readermail: TWideStringField;
34 readerpassword: TWideStringField;
36 magmagId: TIntegerField;
37 magmagName: TWideStringField;
38 magcomment: TWideStringField;
40 maglastDay: TDateField;
41 magenable: TBooleanField;
43 newsnewsId: TIntegerField;
44 magmagNum: TWideStringField;
45 imagewriterId: TIntegerField;
46 imagenumber: TIntegerField;
47 imagename: TWideStringField;
48 imagedata: TWideMemoField;
49 dbserial: TIntegerField;
50 dbmagId: TIntegerField;
51 dbreaderId: TIntegerField;
52 dbwriterId: TIntegerField;
53 procedure DataModuleCreate(Sender: TObject);
56 function makeTable(Sender: TObject): TJSONObject;
59 procedure AddMagazine(id: integer; out Data: TJSONObject);
60 procedure backNumber(id: integer; out Data: TJSONObject);
61 function existsMail(mail: string): Boolean;
62 function checkUserPassword(id: integer; password: string): Boolean;
63 function createReaderId(Data: TJSONObject): integer;
64 procedure deleteReaderId(Data: TJSONObject);
65 function updateReaderId(Data: TJSONObject): Boolean;
66 procedure custData(id: integer; Data: TJSONObject);
67 procedure custView(id: integer; out Data: TJSONObject);
68 procedure deleteMagazine(id: integer);
69 procedure deleteNumber(id, num: integer);
70 procedure deleteWriter(var id: integer);
71 procedure getView(id, num: integer; out Data: TJSONObject); overload;
72 procedure getView(id: integer; out Data: TJSONObject); overload;
73 procedure viewList(id: integer; out Data: TJSONObject);
74 procedure magazines(id: integer; out Data: TJSONObject);
75 procedure magListAll(id: integer; out Data: TJSONObject);
76 procedure magData(id: integer; out Data: TJSONObject);
77 function magid(name: string): integer;
78 procedure magIdOff(id, magid: integer);
79 procedure magIdOn(id, magid: integer);
80 procedure createMagId(id: integer; out Data: TJSONObject);
81 procedure postMessage(id: integer; Data: TJSONObject);
82 procedure createWriterId(Data: TJSONObject);
83 procedure readerData(id: integer; out Data: TJSONObject);
84 procedure titleView(id: integer; out Data: TJSONObject);
85 procedure updateWriterId(id: integer; Data: TJSONObject);
86 procedure userView(id: integer; out Data: TJSONObject);
87 function loginReader(Data: TJSONObject): integer;
88 function loginWriter(Data: TJSONObject): integer;
89 procedure mainView(id: integer; out Data: TJSONObject);
90 procedure imageView(id: integer; out Data: TJSONObject);
91 function imageId(Data: TJSONObject): integer;
95 DataModule1: TDataModule1;
99 uses System.Variants, System.Generics.Collections;
101 { %CLASSGROUP 'Vcl.Controls.TControl' }
105 procedure TDataModule1.AddMagazine(id: integer; out Data: TJSONObject);
110 FDQuery1.Open('select MAX(magId) as id from mag;');
111 i := FDQuery1.FieldByName('id').AsInteger + 1;
112 na := Data.Values['magName'].Value;
113 com := Data.Values['comment'].Value;
114 mag.AppendRecord([i, na, com, Date, Date, true]);
115 DB.AppendRecord([id, i, 0]);
118 procedure TDataModule1.backNumber(id: integer; out Data: TJSONObject);
120 con = '
\82±
\82Ì
\8bL
\8e\96\82Í
\8cö
\8aJ
\90§
\8cÀ
\82ª
\82 \82è
\82Ü
\82·.';
126 Data := TJSONObject.Create;
128 mem := TStringList.Create;
132 SQL.Add('select file,enabled from news where magId = :id order by lastDay;');
133 Params.ParamByName('id').AsInteger := id;
137 blob := CreateBlobStream(FieldByName('text'), bmRead);
138 mem.LoadFromStream(blob);
139 if FieldByName('enabled').AsBoolean = true then
140 d.AddPair('text', mem.Text)
142 d.AddPair('text', con);
150 function TDataModule1.checkUserPassword(id: integer; password: string): Boolean;
152 result := writer.Lookup('id', id, 'password') = password;
155 procedure TDataModule1.createMagId(id: integer; out Data: TJSONObject);
160 FDQuery1.SQL.Add('select MAX(magId) as count from mag;');
162 i := FDQuery1.FieldByName('count').AsInteger + 1;
164 mag.FieldByName('magId').AsInteger := id;
165 mag.FieldByName('magNum').AsString := 'MAG' + i.ToString;
166 mag.FieldByName('day').AsDateTime := Date;
167 mag.FieldByName('lastDay').AsDateTime := Date;
168 mag.FieldByName('magName').AsString := Data.Values['magName'].Value;
169 mag.FieldByName('comment').AsString := Data.Values['comment'].Value;
170 mag.FieldByName('enable').AsString := Data.Values['enable'].Value;
172 DB.AppendRecord([id, i, 0]);
175 function TDataModule1.createReaderId(Data: TJSONObject): integer;
180 na := Data.Values['name'].Value;
181 ma := Data.Values['mail'].Value;
182 pa := Data.Values['password'].Value;
183 if reader.Locate('mail', ma) = false then
185 FDQuery1.Open('select MAX(readerid) as id from reader;');
186 i := FDQuery1.FieldByName('id').AsInteger + 1;
187 reader.AppendRecord([i, na, ma, pa]);
194 procedure TDataModule1.DataModuleCreate(Sender: TObject);
196 tmp = 'create table if not exists ';
199 (tmp + 'db(serial int primary key, magId int, readerId int, writerId int);');
201 (tmp + 'mag(magId int primary key, magNum varchar(10), magName varchar(20), comment varchar(50), day date, lastDay date, enable bool);');
203 (tmp + 'writer(writerId int primary key, writer varchar(20), mail varchar(20), password varchar(20));');
205 (tmp + 'reader(readerId int primary key, reader varchar(20), mail varchar(20), password varchar(20));');
207 (tmp + 'news(magId int, newsId int, day date, changed bool, enabled bool, primary key (magId,newsId));');
209 (tmp + 'image(writerId int, number int, name varchar(20), data text, primary key (writerId,number));');
218 procedure TDataModule1.deleteMagazine(id: integer);
220 if mag.Locate('magId', id) = true then
222 while news.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 DB.Locate('readerid', id) = true do
248 procedure TDataModule1.deleteWriter(var id: integer);
250 if writer.Locate('writerid', id) = true then
252 while DB.Locate('writerid', id) = true do
254 deleteMagazine(DB.FieldByName('magid').AsInteger);
260 function TDataModule1.existsMail(mail: string): Boolean;
262 result := (writer.Locate('mail', mail) = true) or
263 (reader.Locate('mail', mail) = true);
266 procedure TDataModule1.readerData(id: integer; out Data: TJSONObject);
268 if reader.Locate('readerid', id) = true then
270 Data := TJSONObject.Create;
271 Data.AddPair('name', reader.FieldByName('reader').AsString);
272 Data.AddPair('mail', reader.FieldByName('mail').AsString);
276 procedure TDataModule1.userView(id: integer; out Data: TJSONObject);
279 list: TList<integer>;
283 list := TList<integer>.Create;
285 FDQuery1.SQL.Add('select * from indexTable where readerid = :id;');
286 FDQuery1.Params.ParamByName('id').AsInteger := id;
288 while FDQuery1.Eof = false do
290 list.Add(FDQuery1.FieldByName('magid').AsInteger);
293 ar := TJSONArray.Create;
300 Data := TJSONObject.Create;
301 Data.AddPair('mag', ar);
304 procedure TDataModule1.viewList(id: integer; out Data: TJSONObject);
309 procedure TDataModule1.custData(id: integer; Data: TJSONObject);
311 if writer.Locate('writerid', id) = true then
313 Data.AddPair('name', writer.FieldByName('writer').AsString);
314 Data.AddPair('mail', writer.FieldByName('mail').AsString);
318 procedure TDataModule1.custView(id: integer; out Data: TJSONObject);
321 list: TList<integer>;
323 Data := TJSONObject.Create;
324 list := TList<integer>.Create;
326 FDQuery1.SQL.Add('select * from maglist where readerid = :id;');
327 FDQuery1.Params.ParamByName('id').AsInteger := id;
329 while FDQuery1.Eof = false do
331 list.Add(FDQuery1.FieldByName('magid').AsInteger);
339 procedure TDataModule1.getView(id, num: integer; out Data: TJSONObject);
344 Add('select updated,day,file from news');
345 Add(' where magId = :id and newsId = :num');
346 Add(' order by day;');
348 with FDQuery1.Params do
350 ParamByName('id').AsInteger := id;
351 ParamByName('num').AsInteger := num;
354 Data := makeTable(FDQuery1);
357 procedure TDataModule1.getView(id: integer; out Data: TJSONObject);
362 Add('select updated,day,file from indexTable,news');
363 Add(' where readerId = :id and indexTable.magId = news.magId');
364 Add(' and enabled = true order by day;');
366 FDQuery1.Params.ParamByName('id').AsInteger := id;
368 Data := makeTable(FDQuery1);
371 function TDataModule1.imageId(Data: TJSONObject): integer;
373 writerId, number: integer;
375 writerId := Data.Values['id'].Value.ToInteger;
376 number := Data.Values['number'].Value.ToInteger;
377 if image.Locate('writerId;number', VarArrayOf([writerId, number])) = true then
378 result := image.FieldByName('id').AsInteger;
381 procedure TDataModule1.imageView(id: integer; out Data: TJSONObject);
383 Data := TJSONObject.Create;
384 if image.Locate('id', id) = true then
386 Data.AddPair('name', image.FieldByName('name').AsString);
387 Data.AddPair('data', image.FieldByName('data').AsString);
391 function TDataModule1.loginReader(Data: TJSONObject): integer;
395 ma := Data.Values['mail'].Value;
396 pa := Data.Values['password'].Value;
397 if reader.Locate('mail;password', VarArrayOf([ma, pa])) = true then
398 result := reader.FieldByName('readerid').AsInteger
403 function TDataModule1.loginWriter(Data: TJSONObject): integer;
407 ma := Data.Values['mail'].Value;
408 pa := Data.Values['password'].Value;
409 if writer.Locate('mail;password', VarArrayOf([ma, pa])) = true then
410 result := writer.FieldByName('writerid').AsInteger
415 procedure TDataModule1.magData(id: integer; out Data: TJSONObject);
418 FDQuery1.SQL.Add('select * from mag where magid = :id;');
419 FDQuery1.ParamByName('id').AsInteger := id;
421 if FDQuery1.FieldByName('enable').AsBoolean = true then
423 Data := TJSONObject.Create;
424 Data.AddPair('name', FDQuery1.FieldByName('magName').AsString);
425 Data.AddPair('comment', FDQuery1.FieldByName('comment').AsString);
426 Data.AddPair('day', FDQuery1.FieldByName('day').AsString);
427 Data.AddPair('last', FDQuery1.FieldByName('lastDay').AsString);
430 ('select COUNT(*) as count from indextable where magid = :id;');
431 FDQuery1.ParamByName('id').AsInteger := id;
433 Data.AddPair('count', FDQuery1.FieldByName('count').AsString);
437 function TDataModule1.magid(name: string): integer;
441 v := mag.Lookup('magNum', name, 'magid');
442 if VarIsNull(v) = true then
448 procedure TDataModule1.magIdOff(id, magid: integer);
450 if DB.Locate('readerId;magId', VarArrayOf([id, magid])) = true then
454 procedure TDataModule1.magIdOn(id, magid: integer);
456 if (reader.Locate('readerid', id) = true) and
457 (mag.Locate('magid', magid) = true) then
458 DB.AppendRecord([mag.FieldByName('writerid').AsInteger, magid, id]);
461 procedure TDataModule1.magListAll(id: integer; out Data: TJSONObject);
470 ar := TJSONArray.Create;
472 ('select magId,COUNT(*) as count from indexTable group by magId');
473 while mag.Eof = false do
475 i := mag.FieldByName('magId').AsInteger;
476 v := FDQuery1.Lookup('magId', i, 'count');
477 if VarIsNull(v) = true then
479 js := TJSONObject.Create;
480 js.AddPair('magNum', mag.FieldByName('magNum').AsString);
481 js.AddPair('magName', mag.FieldByName('magName').AsString);
482 js.AddPair('comment', mag.FieldByName('comment').AsString);
483 js.AddPair('day', mag.FieldByName('day').AsString);
484 js.AddPair('lastDay', mag.FieldByName('lastDay').AsString);
485 js.AddPair('count', v);
486 v := DB.Lookup('magId', i, 'writerId');
487 v := writer.Lookup('writerId', v, 'writer');
488 if VarIsNull(v) = true then
489 js.AddPair('writer', TJSONFalse.Create)
491 js.AddPair('writer', v);
492 if (id = 0) or (DB.Locate('readerId;magid', VarArrayOf([id, i])) = false)
494 js.AddPair('fun', TJSONFalse.Create)
496 js.AddPair('fun', TJSONTrue.Create);
500 Data := TJSONObject.Create;
502 Data.AddPair('items', ar)
504 Data.AddPair('items', TJSONFalse.Create);
505 if mag.FieldByName('enable').AsBoolean = true then
506 val := TJSONTrue.Create
508 val := TJSONFalse.Create;
509 Data.AddPair('enable', val);
512 procedure TDataModule1.mainView(id: integer; out Data: TJSONObject);
514 Data := TJSONObject.Create;
516 FDQuery1.SQL.Add('select * from news,magId where news.magId = magName.magId');
517 FDQuery1.SQL.Add(' and magId = :id order by day;');
518 FDQuery1.Params.ParamByName('id').AsInteger := id;
519 while FDQuery1.Eof = false do
521 if FDQuery1.FieldByName('enebled').AsBoolean = true then
523 if FDQuery1.FieldByName('changed').AsBoolean = true then
524 Data.AddPair('changed', TJSONTrue.Create);
525 Data.AddPair('', FDQuery1.FieldByName('').AsString);
531 procedure TDataModule1.magazines(id: integer; out Data: TJSONObject);
536 list: TList<integer>;
541 FDQuery1.SQL.Add('select * from maglist where writerid = :id;');
542 FDQuery1.Params.ParamByName('id').AsInteger := id;
544 ar := TJSONArray.Create;
545 list := TList<integer>.Create;
546 while FDQuery1.Eof = false do
548 list.Add(FDQuery1.FieldByName('magId').AsInteger);
560 val := TJSONFalse.Create;
564 Data := TJSONObject.Create;
565 Data.AddPair('mag', val);
568 function TDataModule1.makeTable(Sender: TObject): TJSONObject;
576 ar := TJSONArray.Create;
577 mem := TStringList.Create;
578 with Sender as TFDQuery do
583 d := TJSONObject.Create;
585 day := FieldByName('day').AsString;
586 if FieldByName('updated').AsBoolean = true then
587 d.AddPair('hint', Format('
\82±
\82Ì
\8bL
\8e\96\82Í
\8dX
\90V
\82³
\82ê
\82Ü
\82µ
\82½:(%s)
\93ú.', [day]));
588 blob := CreateBlobStream(FieldByName('file'), bmRead);
589 mem.LoadFromStream(blob);
591 d.AddPair('text', mem.Text);
592 d.AddPair('day', day);
597 result := TJSONObject.Create;
598 result.AddPair('news', ar);
601 procedure TDataModule1.postMessage(id: integer; Data: TJSONObject);
606 FDQuery1.SQL.Add('select MAX(newsId) as id from news where magId = :id;');
607 FDQuery1.Params.ParamByName('id').AsInteger := id;
609 if FDQuery1.RecordCount > 0 then
611 i := FDQuery1.FieldByName('id').AsInteger + 1;
612 news.AppendRecord([id, i, false, Date, Data.Values['file'], true]);
616 procedure TDataModule1.createWriterId(Data: TJSONObject);
621 ma := Data.Values['mail'].Value;
622 if writer.Locate('mail', ma) = false then
624 FDQuery1.Open('select MAX(writerId) as id from writer;');
625 i := FDQuery1.FieldByName('id').AsInteger + 1;
626 na := Data.Values['name'].Value;
627 ma := Data.Values['mail'].Value;
628 pa := Data.Values['password'].Value;
629 writer.AppendRecord([i, na, ma, pa]);
633 procedure TDataModule1.titleView(id: integer; out Data: TJSONObject);
638 Data := TJSONObject.Create;
641 FDQuery1.SQL.Add('select * from mag where magid = :id;');
642 FDQuery1.Params.ParamByName('id').AsInteger := id;
647 for i := 0 to Fields.Count - 1 do
648 d.AddPair(Fields[i].FieldName, Fields[i].AsString);
653 function TDataModule1.updateReaderId(Data: TJSONObject): Boolean;
657 na := Data.Values['reader'].Value;
658 ma := Data.Values['mail'].Value;
659 pa := Data.Values['password'].Value;
660 if reader.Locate('id', Data.Values['id'].Value.ToInteger) = true then
664 FieldByName('reader').AsString := na;
665 FieldByName('mail').AsString := ma;
666 FieldByName('password').AsString := pa;
674 procedure TDataModule1.updateWriterId(id: integer; Data: TJSONObject);
678 na := Data.Values['name'].Value;
679 ma := Data.Values['mail'].Value;
680 pa := Data.Values['password'].Value;
684 FieldByName('writerId').AsInteger := id;
685 FieldByName('writer').AsString := na;
686 FieldByName('mail').AsString := ma;
687 FieldByName('password').AsString := pa;