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(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
235 procedure TDataModule1.deleteNumber(id, num: integer);
237 if news.Locate('magId;newsId', VarArrayOf([id, num])) = true then
241 procedure TDataModule1.deleteReaderId(Data: TJSONObject);
246 id := Data.Values['id'].Value.ToInteger;
247 na := Data.Values['reader'].Value;
248 ma := Data.Values['mail'].Value;
249 pa := Data.Values['password'].Value;
250 if reader.Locate('readerid;reader;mail;password', VarArrayOf([id, na, ma, pa])
253 while indexTable.Locate('readerid',id) = true do
257 procedure TDataModule1.deleteWriter(id: integer);
259 if writer.Locate('writerid',id) = true then
261 while maglist.Locate('writerid',id) = true do
263 deleteMagazine(magList.FieldByName('magid').AsInteger);
268 function TDataModule1.existsMail(mail: string): Boolean;
270 result := (writer.Locate('mail',mail) = true)or(reader.Locate('mail',mail) = true);
273 procedure TDataModule1.readerData(id: integer; out Data: TJSONObject);
275 if reader.Locate('readerid', id) = true then
277 Data := TJSONObject.Create;
278 Data.AddPair('name', reader.FieldByName('reader').AsString);
279 Data.AddPair('mail', reader.FieldByName('mail').AsString);
283 procedure TDataModule1.userView(id: integer; out Data: TJSONObject);
286 list: TList<integer>;
290 list := TList<integer>.Create;
292 FDQuery1.SQL.Add('select * from indexTable where readerid = :id;');
293 FDQuery1.Params.ParamByName('id').AsInteger := id;
295 while FDQuery1.Eof = false do
297 list.Add(FDQuery1.FieldByName('magid').AsInteger);
300 ar:=TJSONArray.Create;
307 Data := TJSONObject.Create;
308 data.AddPair('mag',ar);
311 procedure TDataModule1.viewList(id: integer; out Data: TJSONObject);
316 procedure TDataModule1.custData(id: integer; Data: TJSONObject);
318 if writer.Locate('writerid', id) = true then
320 Data.AddPair('name', writer.FieldByName('writer').AsString);
321 Data.AddPair('mail', writer.FieldByName('mail').AsString);
325 procedure TDataModule1.custView(id: integer; out Data: TJSONObject);
328 list: TList<integer>;
330 Data := TJSONObject.Create;
331 list := TList<integer>.Create;
333 FDQuery1.SQL.Add('select * from maglist where readerid = :id;');
334 FDQuery1.Params.ParamByName('id').AsInteger := id;
336 while FDQuery1.Eof = false do
338 list.Add(FDQuery1.FieldByName('magid').AsInteger);
346 procedure TDataModule1.getView(id, num: integer; out Data: TJSONObject);
351 Add('select updated,day,file from news');
352 Add(' where magId = :id and newsId = :num');
353 Add(' order by day;');
355 with FDQuery1.Params do
357 ParamByName('id').AsInteger := id;
358 ParamByName('num').AsInteger := num;
361 Data := makeTable(FDQuery1);
364 procedure TDataModule1.getView(id: integer; out Data: TJSONObject);
369 Add('select updated,day,file from indexTable,news');
370 Add(' where readerId = :id and indexTable.magId = news.magId');
371 Add(' and enabled = true order by day;');
373 FDQuery1.Params.ParamByName('id').AsInteger := id;
375 Data := makeTable(FDQuery1);
378 function TDataModule1.imageId(data: TJSONObject): integer;
380 writerId, number: integer;
382 writerId:=data.Values['id'].Value.ToInteger;
383 number:=data.Values['number'].Value.ToInteger;
384 if image.Locate('writerId;number',VarArrayOf([writerId,number])) = true then
385 result:=image.FieldByName('id').AsInteger;
388 procedure TDataModule1.imageView(id: integer; out data: TJSONObject);
390 data:=TJSONObject.Create;
391 if image.Locate('id',id) = true then
393 data.AddPair('name',image.FieldByName('name').AsString);
394 data.AddPair('data',image.FieldByName('data').AsString);
398 function TDataModule1.loginReader(data: TJSONObject): integer;
402 ma:=Data.Values['mail'].Value;
403 pa:=Data.Values['password'].Value;
404 if reader.Locate('mail;password',VarArrayOf([ma,pa])) = true then
405 result:=reader.FieldByName('readerid').AsInteger
410 function TDataModule1.loginWriter(data: TJSONObject): integer;
414 ma:=Data.Values['mail'].Value;
415 pa:=Data.Values['password'].Value;
416 if writer.Locate('mail;password',VarArrayOf([ma,pa])) = true then
417 result:=writer.FieldByName('writerid').AsInteger
422 procedure TDataModule1.magData(id: integer; out Data: TJSONObject);
425 FDQuery1.SQL.Add('select * from mag where magid = :id;');
426 FDQuery1.ParamByName('id').AsInteger := id;
428 if FDQuery1.FieldByName('enable').AsBoolean = true then
430 Data := TJSONObject.Create;
431 Data.AddPair('name', FDQuery1.FieldByName('magName').AsString);
432 Data.AddPair('comment', FDQuery1.FieldByName('comment').AsString);
433 Data.AddPair('day', FDQuery1.FieldByName('day').AsString);
434 Data.AddPair('last', FDQuery1.FieldByName('lastDay').AsString);
436 FDQuery1.SQL.Add('select COUNT(*) as count from indextable where magid = :id;');
437 FDQuery1.ParamByName('id').AsInteger:=id;
439 Data.AddPair('count', FDQuery1.FieldByName('count').AsString);
443 function TDataModule1.magid(name: string): integer;
447 v:=mag.Lookup('magNum', name, 'magid');
448 if VarIsNull(v) = true then
454 procedure TDataModule1.magIdOff(id, magid: integer);
456 if indexTable.Locate('readerId;magId', VarArrayOf([id, magid])) = true then
460 procedure TDataModule1.magIdOn(id, magid: integer);
462 if (reader.Locate('readerid', id) = true) and
463 (mag.Locate('magid', magid) = true) then
464 indexTable.AppendRecord([id, magid]);
467 procedure TDataModule1.magListAll(id: integer;out Data: TJSONObject);
476 ar := TJSONArray.Create;
477 FDQuery1.Open('select magId,COUNT(*) as count from indexTable group by magId');
478 while mag.Eof = false do
480 i:=mag.FieldByName('magId').AsInteger;
481 v:=FDQuery1.Lookup('magId',i,'count');
482 if VarIsNull(v) = true then
484 js := TJSONObject.Create;
485 js.AddPair('magNum',mag.FieldByName('magNum').AsString);
486 js.AddPair('magName', mag.FieldByName('magName').AsString);
487 js.AddPair('comment', mag.FieldByName('comment').AsString);
488 js.AddPair('day', mag.FieldByName('day').AsString);
489 js.AddPair('lastDay', mag.FieldByName('lastDay').AsString);
490 js.AddPair('count',v);
491 v:=magList.Lookup('magId',i,'writerId');
492 v:=writer.Lookup('writerId',v,'writer');
493 js.AddPair('writer',v);
494 if (id = 0)or(indexTable.Locate('readerId;magid',VarArrayOf([id,i])) = false) then
495 js.AddPair('fun',TJSONFalse.Create)
497 js.AddPair('fun',TJSONTrue.Create);
501 Data := TJSONObject.Create;
503 Data.AddPair('items', ar)
505 Data.AddPair('items', TJSONFalse.Create);
506 if mag.FieldByName('enable').AsBoolean = true then
507 val := TJSONTrue.Create
509 val := TJSONFalse.Create;
510 Data.AddPair('enable', val);
513 procedure TDataModule1.mainView(id: integer; out data: TJSONObject);
515 data:=TJSONObject.Create;
517 FDQuery1.SQL.Add('select * from news,magId where news.magId = magName.magId');
518 FDQuery1.SQL.Add(' and magId = :id order by day;');
519 FDQUery1.Params.ParamByName('id').AsInteger:=id;
520 while FDQUery1.Eof = false do
522 if FDQuery1.FieldByName('enebled').AsBoolean = true then
524 if FDQuery1.FieldByName('changed').AsBoolean = true then
525 data.AddPair('changed',TJSONTrue.Create);
526 data.AddPair('',FDQuery1.FieldByName('').AsString);
532 procedure TDataModule1.magazines(id: integer; out Data: TJSONObject);
537 list: TList<integer>;
542 FDQuery1.SQL.Add('select * from maglist where writerid = :id;');
543 FDQuery1.Params.ParamByName('id').AsInteger := id;
545 ar := TJSONArray.Create;
546 list:=TList<integer>.Create;
547 while FDQuery1.Eof = false do
549 list.Add(FDQuery1.FieldByName('magId').AsInteger);
561 val := TJSONFalse.Create;
565 Data := TJSONObject.Create;
566 Data.AddPair('mag', val);
569 function TDataModule1.makeTable(Sender: TObject): TJSONObject;
577 ar:=TJSONArray.Create;
578 mem := TStringList.Create;
579 with Sender as TFDQuery do
584 d:=TJSONObject.Create;
586 day := FieldByName('day').AsString;
587 if FieldByName('updated').AsBoolean = true then
588 d.AddPair('hint', Format('
\82±
\82Ì
\8bL
\8e\96\82Í
\8dX
\90V
\82³
\82ê
\82Ü
\82µ
\82½:(%s)
\93ú.',[day]));
589 blob := CreateBlobStream(FieldByName('file'), bmRead);
590 mem.LoadFromStream(blob);
592 d.AddPair('text',mem.Text);
593 d.AddPair('day',day);
598 result := TJSONObject.Create;
599 result.AddPair('news',ar);
602 procedure TDataModule1.postMessage(id: integer; Data: TJSONObject);
607 FDQuery1.SQL.Add('select MAX(newsId) as id from news where magId = :id;');
608 FDQuery1.Params.ParamByName('id').AsInteger := id;
610 if FDQuery1.RecordCount > 0 then
612 i := FDQuery1.FieldByName('id').AsInteger + 1;
613 news.AppendRecord([id, i, false, Date, Data.Values['file'], true]);
617 procedure TDataModule1.createWriterId(Data: TJSONObject);
622 ma := Data.Values['mail'].Value;
623 if writer.Locate('mail', ma) = false then
625 FDQuery1.Open('select MAX(writerId) as id from writer;');
626 i := FDQuery1.FieldByName('id').AsInteger + 1;
627 na := Data.Values['name'].Value;
628 ma := Data.Values['mail'].Value;
629 pa := Data.Values['password'].Value;
630 writer.AppendRecord([i, na, ma, pa]);
634 procedure TDataModule1.titleView(id: integer;out Data: TJSONObject);
639 Data:=TJSONObject.Create;
642 FDQuery1.SQL.Add('select * from mag where magid = :id;');
643 FDQuery1.Params.ParamByName('id').AsInteger := id;
648 for i := 0 to Fields.Count - 1 do
649 d.AddPair(Fields[i].FieldName, Fields[i].AsString);
654 function TDataModule1.updateReaderId(Data: TJSONObject): Boolean;
658 na:=Data.Values['reader'].Value;
659 ma:=Data.Values['mail'].Value;
660 pa:=data.Values['password'].Value;
661 if reader.Locate('id',data.Values['id'].Value.ToInteger) = true then
665 FieldByName('reader').AsString:=na;
666 FieldByName('mail').AsString:=ma;
667 FieldByName('password').AsString:=pa;
675 procedure TDataModule1.updateWriterId(id: integer; Data: TJSONObject);
679 na := Data.Values['name'].Value;
680 ma := Data.Values['mail'].Value;
681 pa := Data.Values['password'].Value;
685 FieldByName('writerId').AsInteger := id;
686 FieldByName('writer').AsString := na;
687 FieldByName('mail').AsString := ma;
688 FieldByName('password').AsString := pa;