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;
33 newschanged: TBooleanField;
34 newsenabled: TBooleanField;
35 readerreaderId: TIntegerField;
36 readerreader: TWideStringField;
37 readermail: TWideStringField;
38 readerpassword: TWideStringField;
39 indexTablereaderId: TIntegerField;
40 indexTablemagId: TIntegerField;
42 magmagId: TIntegerField;
43 magmagName: TWideStringField;
44 magcomment: TWideStringField;
46 maglastDay: TDateField;
47 magenable: TBooleanField;
49 newsnewsId: TIntegerField;
50 magmagNum: TWideStringField;
51 procedure DataModuleCreate(Sender: TObject);
54 function makeTable(Sender: TObject): TJSONObject;
57 procedure AddMagazine(id: integer; out Data: TJSONObject);
58 procedure backNumber(id: integer; out Data: TJSONObject);
59 function existsMail(mail: string): Boolean;
60 function checkUserPassword(id: integer; password: string): Boolean;
61 function createReaderId(Data: TJSONObject): integer;
62 procedure deleteReaderId(Data: TJSONObject);
63 function updateReaderId(Data: TJSONObject): Boolean;
64 procedure custData(id: integer; Data: TJSONObject);
65 procedure custView(id: integer; out Data: TJSONObject);
66 procedure deleteMagazine(id: integer);
67 procedure deleteNumber(id, num: integer);
68 procedure deleteWriter(var id: integer);
69 procedure getView(id, num: integer; out Data: TJSONObject); overload;
70 procedure getView(id: integer; out Data: TJSONObject); overload;
71 procedure viewList(id: integer; out Data: TJSONObject);
72 procedure magazines(id: integer; out Data: TJSONObject);
73 procedure magListAll(id: integer; out Data: TJSONObject);
74 procedure magData(id: integer; out Data: TJSONObject);
75 function magid(name: string): integer;
76 procedure magIdOff(id, magid: integer);
77 procedure magIdOn(id, magid: integer);
78 procedure createMagId(id: integer; out Data: TJSONObject);
79 procedure postMessage(id: integer; Data: TJSONObject);
80 procedure createWriterId(Data: TJSONObject);
81 procedure readerData(id: integer; out Data: TJSONObject);
82 procedure titleView(id: integer;out Data: TJSONObject);
83 procedure updateWriterId(id: integer; Data: TJSONObject);
84 procedure userView(id: integer; out Data: TJSONObject);
85 function loginReader(data: TJSONObject): integer;
86 function loginWriter(data: TJSONObject): integer;
87 procedure mainView(id: integer; out data: TJSONObject);
88 procedure imageView(id: integer; out data: TJSONObject);
89 function imageId(data: TJSONObject): integer;
93 DataModule1: TDataModule1;
97 uses System.Variants, System.Generics.Collections;
99 { %CLASSGROUP 'Vcl.Controls.TControl' }
103 procedure TDataModule1.AddMagazine(id: integer; out Data: TJSONObject);
108 FDQuery1.Open('select MAX(magId) as id from mag;');
109 i := FDQuery1.FieldByName('id').AsInteger + 1;
110 na := Data.Values['magName'].Value;
111 com := Data.Values['comment'].Value;
112 mag.AppendRecord([i, na, com, Date, Date, true]);
113 magList.AppendRecord([id, i]);
116 procedure TDataModule1.backNumber(id: integer; out Data: TJSONObject);
118 con = '
\82±
\82Ì
\8bL
\8e\96\82Í
\8cö
\8aJ
\90§
\8cÀ
\82ª
\82 \82è
\82Ü
\82·.';
124 Data := TJSONObject.Create;
126 mem := TStringList.Create;
130 SQL.Add('select file,enabled from news where magId = :id order by lastDay;');
131 Params.ParamByName('id').AsInteger := id;
135 blob := CreateBlobStream(FieldByName('text'), bmRead);
136 mem.LoadFromStream(blob);
137 if FieldByName('enabled').AsBoolean = true then
138 d.AddPair('text', mem.Text)
140 d.AddPair('text', con);
148 function TDataModule1.checkUserPassword(id: integer; password: string): Boolean;
150 result := writer.Lookup('id', id, 'password') = password;
153 procedure TDataModule1.createMagId(id: integer; out Data: TJSONObject);
158 FDQuery1.SQL.Add('select MAX(magId) as count from mag;');
160 i := FDQuery1.FieldByName('count').AsInteger + 1;
162 mag.FieldByName('magId').AsInteger := id;
163 mag.FieldByName('magNum').AsString := 'MAG'+i.ToString;
164 mag.FieldByName('day').AsDateTime := Date;
165 mag.FieldByName('lastDay').AsDateTime := Date;
166 mag.FieldByName('magName').AsString := Data.Values['magName'].Value;
167 mag.FieldByName('comment').AsString := Data.Values['comment'].Value;
168 mag.FieldByName('enable').AsString := Data.Values['enable'].Value;
170 magList.AppendRecord([id, i]);
173 function TDataModule1.createReaderId(Data: TJSONObject): integer;
178 na := Data.Values['name'].Value;
179 ma := Data.Values['mail'].Value;
180 pa := Data.Values['password'].Value;
181 if reader.Locate('mail',ma) = false then
183 FDQuery1.Open('select MAX(readerid) as id from reader;');
184 i := FDQuery1.FieldByName('id').AsInteger + 1;
185 reader.AppendRecord([i, na, ma, pa]);
192 procedure TDataModule1.DataModuleCreate(Sender: TObject);
194 tmp = 'create table if not exists ';
196 // FDQuery1.ExecSQL(tmp+'database(number int primary key, magId int, readerId int, writeId int);');
198 (tmp + 'mag(magId int primary key, magNum varchar(10), magName varchar(20), comment varchar(50), day date, lastDay date, enable bool);');
200 (tmp + 'writer(writerId int primary key, writer varchar(20), mail varchar(20), password varchar(20));');
202 (tmp + 'reader(readerId int primary key, reader varchar(20), mail varchar(20), password varchar(20));');
204 (tmp + 'news(magId int, newsId int, day date, changed bool, enabled bool, primary key (magId,newsId));');
206 (tmp + 'indexTable(readerId int, magId int, primary key (readerId,magId));');
208 (tmp + 'magList(writerId int, magId int, primary key (writerId,magId));');
209 FDQuery1.ExecSQL(tmp+'image(id int primary key, writerId int, number int, name varchar(20), data text);');
210 // database.Open;
\83V
\83\83\83b
\83t
\83\8b\89Â
\94\
\82É
\82·
\82é
\82½
\82ß
\92Ê
\82µ
\94Ô
\8d\86\82ð
\95t
\82¯
\82½
\82Ù
\82¤
\82ª
\97Ç
\82¢
\82ç
\82µ
\82¢
220 procedure TDataModule1.deleteMagazine(id: integer);
221 procedure main(Sender: TObject);
223 with Sender as TFDTable do
224 while Locate('magid',id) = true do
229 if mag.Locate('magId',id) = true then
236 procedure TDataModule1.deleteNumber(id, num: integer);
238 if news.Locate('magId;newsId', VarArrayOf([id, num])) = true then
242 procedure TDataModule1.deleteReaderId(Data: TJSONObject);
247 id := Data.Values['id'].Value.ToInteger;
248 na := Data.Values['reader'].Value;
249 ma := Data.Values['mail'].Value;
250 pa := Data.Values['password'].Value;
251 if reader.Locate('readerid;reader;mail;password', VarArrayOf([id, na, ma, pa])
254 while indexTable.Locate('readerid',id) = true do
258 procedure TDataModule1.deleteWriter(var id: integer);
260 if writer.Locate('writerid',id) = true then
262 while magList.Locate('writerid',id) = true do
264 deleteMagazine(magList.FieldByName('magid').AsInteger);
270 function TDataModule1.existsMail(mail: string): Boolean;
272 result := (writer.Locate('mail',mail) = true)or(reader.Locate('mail',mail) = true);
275 procedure TDataModule1.readerData(id: integer; out Data: TJSONObject);
277 if reader.Locate('readerid', id) = true then
279 Data := TJSONObject.Create;
280 Data.AddPair('name', reader.FieldByName('reader').AsString);
281 Data.AddPair('mail', reader.FieldByName('mail').AsString);
285 procedure TDataModule1.userView(id: integer; out Data: TJSONObject);
288 list: TList<integer>;
292 list := TList<integer>.Create;
294 FDQuery1.SQL.Add('select * from indexTable where readerid = :id;');
295 FDQuery1.Params.ParamByName('id').AsInteger := id;
297 while FDQuery1.Eof = false do
299 list.Add(FDQuery1.FieldByName('magid').AsInteger);
302 ar:=TJSONArray.Create;
309 Data := TJSONObject.Create;
310 data.AddPair('mag',ar);
313 procedure TDataModule1.viewList(id: integer; out Data: TJSONObject);
318 procedure TDataModule1.custData(id: integer; Data: TJSONObject);
320 if writer.Locate('writerid', id) = true then
322 Data.AddPair('name', writer.FieldByName('writer').AsString);
323 Data.AddPair('mail', writer.FieldByName('mail').AsString);
327 procedure TDataModule1.custView(id: integer; out Data: TJSONObject);
330 list: TList<integer>;
332 Data := TJSONObject.Create;
333 list := TList<integer>.Create;
335 FDQuery1.SQL.Add('select * from maglist where readerid = :id;');
336 FDQuery1.Params.ParamByName('id').AsInteger := id;
338 while FDQuery1.Eof = false do
340 list.Add(FDQuery1.FieldByName('magid').AsInteger);
348 procedure TDataModule1.getView(id, num: integer; out Data: TJSONObject);
353 Add('select updated,day,file from news');
354 Add(' where magId = :id and newsId = :num');
355 Add(' order by day;');
357 with FDQuery1.Params do
359 ParamByName('id').AsInteger := id;
360 ParamByName('num').AsInteger := num;
363 Data := makeTable(FDQuery1);
366 procedure TDataModule1.getView(id: integer; out Data: TJSONObject);
371 Add('select updated,day,file from indexTable,news');
372 Add(' where readerId = :id and indexTable.magId = news.magId');
373 Add(' and enabled = true order by day;');
375 FDQuery1.Params.ParamByName('id').AsInteger := id;
377 Data := makeTable(FDQuery1);
380 function TDataModule1.imageId(data: TJSONObject): integer;
382 writerId, number: integer;
384 writerId:=data.Values['id'].Value.ToInteger;
385 number:=data.Values['number'].Value.ToInteger;
386 if image.Locate('writerId;number',VarArrayOf([writerId,number])) = true then
387 result:=image.FieldByName('id').AsInteger;
390 procedure TDataModule1.imageView(id: integer; out data: TJSONObject);
392 data:=TJSONObject.Create;
393 if image.Locate('id',id) = true then
395 data.AddPair('name',image.FieldByName('name').AsString);
396 data.AddPair('data',image.FieldByName('data').AsString);
400 function TDataModule1.loginReader(data: TJSONObject): integer;
404 ma:=Data.Values['mail'].Value;
405 pa:=Data.Values['password'].Value;
406 if reader.Locate('mail;password',VarArrayOf([ma,pa])) = true then
407 result:=reader.FieldByName('readerid').AsInteger
412 function TDataModule1.loginWriter(data: TJSONObject): integer;
416 ma:=Data.Values['mail'].Value;
417 pa:=Data.Values['password'].Value;
418 if writer.Locate('mail;password',VarArrayOf([ma,pa])) = true then
419 result:=writer.FieldByName('writerid').AsInteger
424 procedure TDataModule1.magData(id: integer; out Data: TJSONObject);
427 FDQuery1.SQL.Add('select * from mag where magid = :id;');
428 FDQuery1.ParamByName('id').AsInteger := id;
430 if FDQuery1.FieldByName('enable').AsBoolean = true then
432 Data := TJSONObject.Create;
433 Data.AddPair('name', FDQuery1.FieldByName('magName').AsString);
434 Data.AddPair('comment', FDQuery1.FieldByName('comment').AsString);
435 Data.AddPair('day', FDQuery1.FieldByName('day').AsString);
436 Data.AddPair('last', FDQuery1.FieldByName('lastDay').AsString);
438 FDQuery1.SQL.Add('select COUNT(*) as count from indextable where magid = :id;');
439 FDQuery1.ParamByName('id').AsInteger:=id;
441 Data.AddPair('count', FDQuery1.FieldByName('count').AsString);
445 function TDataModule1.magid(name: string): integer;
449 v:=mag.Lookup('magNum', name, 'magid');
450 if VarIsNull(v) = true then
456 procedure TDataModule1.magIdOff(id, magid: integer);
458 if indexTable.Locate('readerId;magId', VarArrayOf([id, magid])) = true then
462 procedure TDataModule1.magIdOn(id, magid: integer);
464 if (reader.Locate('readerid', id) = true) and
465 (mag.Locate('magid', magid) = true) then
466 indexTable.AppendRecord([id, magid]);
469 procedure TDataModule1.magListAll(id: integer;out Data: TJSONObject);
478 ar := TJSONArray.Create;
479 FDQuery1.Open('select magId,COUNT(*) as count from indexTable group by magId');
480 while mag.Eof = false do
482 i:=mag.FieldByName('magId').AsInteger;
483 v:=FDQuery1.Lookup('magId',i,'count');
484 if VarIsNull(v) = true then
486 js := TJSONObject.Create;
487 js.AddPair('magNum',mag.FieldByName('magNum').AsString);
488 js.AddPair('magName', mag.FieldByName('magName').AsString);
489 js.AddPair('comment', mag.FieldByName('comment').AsString);
490 js.AddPair('day', mag.FieldByName('day').AsString);
491 js.AddPair('lastDay', mag.FieldByName('lastDay').AsString);
492 js.AddPair('count',v);
493 v:=magList.Lookup('magId',i,'writerId');
494 v:=writer.Lookup('writerId',v,'writer');
495 if VarIsNull(v) = true then
496 js.AddPair('writer',TJSONFalse.Create)
498 js.AddPair('writer',v);
499 if (id = 0)or(indexTable.Locate('readerId;magid',VarArrayOf([id,i])) = false) then
500 js.AddPair('fun',TJSONFalse.Create)
502 js.AddPair('fun',TJSONTrue.Create);
506 Data := TJSONObject.Create;
508 Data.AddPair('items', ar)
510 Data.AddPair('items', TJSONFalse.Create);
511 if mag.FieldByName('enable').AsBoolean = true then
512 val := TJSONTrue.Create
514 val := TJSONFalse.Create;
515 Data.AddPair('enable', val);
518 procedure TDataModule1.mainView(id: integer; out data: TJSONObject);
520 data:=TJSONObject.Create;
522 FDQuery1.SQL.Add('select * from news,magId where news.magId = magName.magId');
523 FDQuery1.SQL.Add(' and magId = :id order by day;');
524 FDQUery1.Params.ParamByName('id').AsInteger:=id;
525 while FDQUery1.Eof = false do
527 if FDQuery1.FieldByName('enebled').AsBoolean = true then
529 if FDQuery1.FieldByName('changed').AsBoolean = true then
530 data.AddPair('changed',TJSONTrue.Create);
531 data.AddPair('',FDQuery1.FieldByName('').AsString);
537 procedure TDataModule1.magazines(id: integer; out Data: TJSONObject);
542 list: TList<integer>;
547 FDQuery1.SQL.Add('select * from maglist where writerid = :id;');
548 FDQuery1.Params.ParamByName('id').AsInteger := id;
550 ar := TJSONArray.Create;
551 list:=TList<integer>.Create;
552 while FDQuery1.Eof = false do
554 list.Add(FDQuery1.FieldByName('magId').AsInteger);
566 val := TJSONFalse.Create;
570 Data := TJSONObject.Create;
571 Data.AddPair('mag', val);
574 function TDataModule1.makeTable(Sender: TObject): TJSONObject;
582 ar:=TJSONArray.Create;
583 mem := TStringList.Create;
584 with Sender as TFDQuery do
589 d:=TJSONObject.Create;
591 day := FieldByName('day').AsString;
592 if FieldByName('updated').AsBoolean = true then
593 d.AddPair('hint', Format('
\82±
\82Ì
\8bL
\8e\96\82Í
\8dX
\90V
\82³
\82ê
\82Ü
\82µ
\82½:(%s)
\93ú.',[day]));
594 blob := CreateBlobStream(FieldByName('file'), bmRead);
595 mem.LoadFromStream(blob);
597 d.AddPair('text',mem.Text);
598 d.AddPair('day',day);
603 result := TJSONObject.Create;
604 result.AddPair('news',ar);
607 procedure TDataModule1.postMessage(id: integer; Data: TJSONObject);
612 FDQuery1.SQL.Add('select MAX(newsId) as id from news where magId = :id;');
613 FDQuery1.Params.ParamByName('id').AsInteger := id;
615 if FDQuery1.RecordCount > 0 then
617 i := FDQuery1.FieldByName('id').AsInteger + 1;
618 news.AppendRecord([id, i, false, Date, Data.Values['file'], true]);
622 procedure TDataModule1.createWriterId(Data: TJSONObject);
627 ma := Data.Values['mail'].Value;
628 if writer.Locate('mail', ma) = false then
630 FDQuery1.Open('select MAX(writerId) as id from writer;');
631 i := FDQuery1.FieldByName('id').AsInteger + 1;
632 na := Data.Values['name'].Value;
633 ma := Data.Values['mail'].Value;
634 pa := Data.Values['password'].Value;
635 writer.AppendRecord([i, na, ma, pa]);
639 procedure TDataModule1.titleView(id: integer;out Data: TJSONObject);
644 Data:=TJSONObject.Create;
647 FDQuery1.SQL.Add('select * from mag where magid = :id;');
648 FDQuery1.Params.ParamByName('id').AsInteger := id;
653 for i := 0 to Fields.Count - 1 do
654 d.AddPair(Fields[i].FieldName, Fields[i].AsString);
659 function TDataModule1.updateReaderId(Data: TJSONObject): Boolean;
663 na:=Data.Values['reader'].Value;
664 ma:=Data.Values['mail'].Value;
665 pa:=data.Values['password'].Value;
666 if reader.Locate('id',data.Values['id'].Value.ToInteger) = true then
670 FieldByName('reader').AsString:=na;
671 FieldByName('mail').AsString:=ma;
672 FieldByName('password').AsString:=pa;
680 procedure TDataModule1.updateWriterId(id: integer; Data: TJSONObject);
684 na := Data.Values['name'].Value;
685 ma := Data.Values['mail'].Value;
686 pa := Data.Values['password'].Value;
690 FieldByName('writerId').AsInteger := id;
691 FieldByName('writer').AsString := na;
692 FieldByName('mail').AsString := ma;
693 FieldByName('password').AsString := pa;