type
TDataModule1 = class(TDataModule)
- database: TFDTable;
- indexTable: TFDTable;
+ DB: TFDTable;
reader: TFDTable;
- magList: TFDTable;
MagazineConnection: TFDConnection;
FDQuery1: TFDQuery;
news: TFDTable;
writerwriter: TWideStringField;
writermail: TWideStringField;
writerpassword: TWideStringField;
- magListwriterId: TIntegerField;
- magListmagId: TIntegerField;
- newsmagId: TIntegerField;
- newsno: TIntegerField;
- newsday: TDateField;
- newschanged: TBooleanField;
- newsenabled: TBooleanField;
readerreaderId: TIntegerField;
readerreader: TWideStringField;
readermail: TWideStringField;
readerpassword: TWideStringField;
- indexTablereaderId: TIntegerField;
- indexTablemagId: TIntegerField;
mag: TFDTable;
magmagId: TIntegerField;
magmagName: TWideStringField;
magday: TDateField;
maglastDay: TDateField;
magenable: TBooleanField;
+ image: TFDTable;
+ magmagNum: TWideStringField;
+ imagewriterId: TIntegerField;
+ imagenumber: TIntegerField;
+ imagename: TWideStringField;
+ imagedata: TWideMemoField;
+ dbserial: TIntegerField;
+ dbmagId: TIntegerField;
+ dbreaderId: TIntegerField;
+ dbwriterId: TIntegerField;
+ newsmagId: TIntegerField;
+ newsnewsId: TIntegerField;
+ newsday: TDateField;
+ newschanged: TBooleanField;
+ newsenabled: TBooleanField;
procedure DataModuleCreate(Sender: TObject);
private
{ Private \90é\8c¾ }
{ Public \90é\8c¾ }
procedure AddMagazine(id: integer; out Data: TJSONObject);
procedure backNumber(id: integer; out Data: TJSONObject);
+ function existsMail(mail: string): Boolean;
function checkUserPassword(id: integer; password: string): Boolean;
- procedure createReaderId(Data: TJSONObject);
+ function createReaderId(Data: TJSONObject): integer;
procedure deleteReaderId(Data: TJSONObject);
- procedure updateReaderId(Data: TJSONObject);
+ function updateReaderId(Data: TJSONObject): Boolean;
procedure custData(id: integer; Data: TJSONObject);
procedure custView(id: integer; out Data: TJSONObject);
procedure deleteMagazine(id: integer);
procedure deleteNumber(id, num: integer);
- procedure deleteWriter(id: integer);
+ procedure deleteWriter(var id: integer);
procedure getView(id, num: integer; out Data: TJSONObject); overload;
procedure getView(id: integer; out Data: TJSONObject); overload;
+ procedure viewList(id: integer; out Data: TJSONObject);
procedure magazines(id: integer; out Data: TJSONObject);
- procedure magListAll(out Data: TJSONObject);
+ procedure magListAll(id: integer; out Data: TJSONObject);
procedure magData(id: integer; out Data: TJSONObject);
function magid(name: string): integer;
procedure magIdOff(id, magid: integer);
procedure postMessage(id: integer; Data: TJSONObject);
procedure createWriterId(Data: TJSONObject);
procedure readerData(id: integer; out Data: TJSONObject);
- procedure titleView(id: integer; Data: TJSONObject);
+ function titleView(magid, writerId: integer; out Data: TJSONObject)
+ : Boolean;
procedure updateWriterId(id: integer; Data: TJSONObject);
procedure userView(id: integer; out Data: TJSONObject);
+ function loginReader(Data: TJSONObject): integer;
+ function loginWriter(Data: TJSONObject): integer;
+ procedure mainView(id: integer; out Data: TJSONObject);
+ procedure imageView(id: integer; out Data: TJSONObject);
+ function imageId(Data: TJSONObject): integer;
end;
var
na := Data.Values['magName'].Value;
com := Data.Values['comment'].Value;
mag.AppendRecord([i, na, com, Date, Date, true]);
- magList.AppendRecord([id, i]);
+ DB.AppendRecord([id, i, 0]);
end;
procedure TDataModule1.backNumber(id: integer; out Data: TJSONObject);
FDQuery1.Open;
i := FDQuery1.FieldByName('count').AsInteger + 1;
mag.Append;
- mag.FieldByName('magId').AsInteger := i;
+ mag.FieldByName('magId').AsInteger := id;
+ mag.FieldByName('magNum').AsString := 'MAG' + i.ToString;
mag.FieldByName('day').AsDateTime := Date;
mag.FieldByName('lastDay').AsDateTime := Date;
mag.FieldByName('magName').AsString := Data.Values['magName'].Value;
mag.FieldByName('comment').AsString := Data.Values['comment'].Value;
mag.FieldByName('enable').AsString := Data.Values['enable'].Value;
mag.Post;
- magList.AppendRecord([id, i]);
+ DB.AppendRecord([id, i, 0]);
end;
-procedure TDataModule1.createReaderId(Data: TJSONObject);
+function TDataModule1.createReaderId(Data: TJSONObject): integer;
var
i: integer;
na, ma, pa: string;
begin
- FDQuery1.Open('select MAX(readerid) as id from reader;');
- i := FDQuery1.FieldByName('id').AsInteger + 1;
na := Data.Values['name'].Value;
ma := Data.Values['mail'].Value;
pa := Data.Values['password'].Value;
- reader.AppendRecord([i, na, ma, pa]);
+ if reader.Locate('mail', ma) = false then
+ begin
+ FDQuery1.Open('select MAX(readerid) as id from reader;');
+ i := FDQuery1.FieldByName('id').AsInteger + 1;
+ reader.AppendRecord([i, na, ma, pa]);
+ result := i;
+ end
+ else
+ result := 0;
end;
procedure TDataModule1.DataModuleCreate(Sender: TObject);
const
tmp = 'create table if not exists ';
begin
-// FDQuery1.ExecSQL(tmp+'database(number int primary key, magId int, readerId int, writeId int);');
FDQuery1.ExecSQL
- (tmp + 'mag(magId int primary key, magName varchar(20), comment varchar(50), day date, lastDay date, enable bool);');
+ (tmp + 'db(serial int primary key, magId int, readerId int, writerId int);');
+ FDQuery1.ExecSQL
+ (tmp + 'mag(magId int primary key, magNum varchar(10), magName varchar(20), comment varchar(50), day date, lastDay date, enable bool);');
FDQuery1.ExecSQL
(tmp + 'writer(writerId int primary key, writer varchar(20), mail varchar(20), password varchar(20));');
FDQuery1.ExecSQL
(tmp + 'reader(readerId int primary key, reader varchar(20), mail varchar(20), password varchar(20));');
FDQuery1.ExecSQL
- (tmp + 'news(magId int, no int, day date, changed bool, enabled bool, primary key (magId,no));');
+ (tmp + 'news(magId int, newsId int, files text, day date, changed bool, enabled bool, primary key (magId,newsId));');
FDQuery1.ExecSQL
- (tmp + 'indexTable(readerId int, magId int, primary key (readerId,magId));');
- FDQuery1.ExecSQL
- (tmp + 'magList(writerId int, magId int, primary key (writerId,magId));');
-// database.Open;
+ (tmp + 'image(writerId int, number int, name varchar(20), data text, primary key (writerId,number));');
+ DB.Open;
mag.Open;
writer.Open;
reader.Open;
news.Open;
- indexTable.Open;
- magList.Open;
+ image.Open;
end;
procedure TDataModule1.deleteMagazine(id: integer);
- procedure main(Sender: TObject);
- begin
- with Sender as TFDTable do
- while Locate('magid',id) = true do
- Delete;
- end;
-
begin
if mag.Locate('magId', id) = true then
- begin
mag.Delete;
- main(news);
-// main('database');
- main(indexTable);
- end;
+ while news.Locate('magid', id) = true do
+ news.Delete;
end;
procedure TDataModule1.deleteNumber(id, num: integer);
if reader.Locate('readerid;reader;mail;password', VarArrayOf([id, na, ma, pa])
) = true then
reader.Delete;
- while indexTable.Locate('readerid',id) = true do
- indexTable.Delete;
+ while DB.Locate('readerid', id) = true do
+ DB.Delete;
end;
-procedure TDataModule1.deleteWriter(id: integer);
+procedure TDataModule1.deleteWriter(var id: integer);
begin
- if writer.Locate('writerid',id) = true then
+ if writer.Locate('writerid', id) = true then
writer.Delete;
- while maglist.Locate('writerid',id) = true do
+ while DB.Locate('writerid', id) = true do
begin
- deleteMagazine(magList.FieldByName('magid').AsInteger);
- magList.Delete;
+ deleteMagazine(DB.FieldByName('magid').AsInteger);
+ DB.Delete;
end;
+ id := 0;
+end;
+
+function TDataModule1.existsMail(mail: string): Boolean;
+begin
+ result := (writer.Locate('mail', mail) = true) or
+ (reader.Locate('mail', mail) = true);
end;
procedure TDataModule1.readerData(id: integer; out Data: TJSONObject);
procedure TDataModule1.userView(id: integer; out Data: TJSONObject);
var
- i: integer;
- list: TList<integer>;
+ ar: TJSONArray;
+ d: TJSONObject;
begin
- Data := TJSONObject.Create;
- list := TList<integer>.Create;
+ ar := TJSONArray.Create;
FDQuery1.SQL.Clear;
- FDQuery1.SQL.Add('select * from indexTable where readerid = :id;');
+ FDQuery1.SQL.Add('select * from db where readerid = :id;');
FDQuery1.Params.ParamByName('id').AsInteger := id;
FDQuery1.Open;
- while FDQuery1.Eof = false do
- list.Add(FDQuery1.FieldByName('magid').AsInteger);
- for i in list do
- titleView(i, Data);
- list.Free;
+ with FDQuery1 do
+ while Eof = false do
+ begin
+ if titleView(FieldByName('magid').AsInteger, FieldByName('writerId')
+ .AsInteger, d) = true then
+ ar.Add(d);
+ Next;
+ end;
+ Data := TJSONObject.Create;
+ Data.AddPair('mag', ar);
+end;
+
+procedure TDataModule1.viewList(id: integer; out Data: TJSONObject);
+begin
+
end;
procedure TDataModule1.custData(id: integer; Data: TJSONObject);
procedure TDataModule1.custView(id: integer; out Data: TJSONObject);
var
- i: integer;
- list: TList<integer>;
+ d: TJSONObject;
+ ar: TJSONArray;
begin
Data := TJSONObject.Create;
- list := TList<integer>.Create;
- FDQuery1.SQL.Clear;
- FDQuery1.SQL.Add('select * from maglist where readerid = :id;');
- FDQuery1.Params.ParamByName('id').AsInteger := id;
- FDQuery1.Open;
- while FDQuery1.Eof = false do
+ ar := TJSONArray.Create;
+ with FDQuery1 do
begin
- list.Add(FDQuery1.FieldByName('magid').AsInteger);
- FDQuery1.Next;
+ SQL.Clear;
+ SQL.Add('select * from db where readerid = :id;');
+ Params.ParamByName('id').AsInteger := id;
+ Open;
+ while Eof = false do
+ begin
+ d := TJSONObject.Create;
+ if titleView(FieldByName('magid').AsInteger, FieldByName('writerId')
+ .AsInteger, d) = true then
+ ar.Add(d);
+ Next;
+ end;
end;
- for i in list do
- titleView(i, Data);
- list.Free;
+ Data.AddPair('mag', ar);
end;
procedure TDataModule1.getView(id, num: integer; out Data: TJSONObject);
with FDQuery1.SQL do
begin
Clear;
- Add('select updated,day,file from news');
+ Add('select changed,day,file from news');
Add(' where magId = :id and newsId = :num');
Add(' order by day;');
end;
procedure TDataModule1.getView(id: integer; out Data: TJSONObject);
begin
+ if id = 0 then
+ Exit;
with FDQuery1.SQL do
begin
Clear;
- Add('select updated,day,file from indexTable,news');
- Add(' where readerId = :id and indexTable.magId = news.magId');
- Add(' and enabled = true order by day;');
+ Add('select magName,writer,changed,news.day,files from db,news,mag,writer');
+ Add(' where db.readerId = :id and db.magId = mag.magId and db.magId = news.magId');
+ Add(' and db.writerId = writer.writerId and enabled = true order by news.day;');
end;
FDQuery1.Params.ParamByName('id').AsInteger := id;
FDQuery1.Open;
Data := makeTable(FDQuery1);
end;
+function TDataModule1.imageId(Data: TJSONObject): integer;
+var
+ writerId, number: integer;
+begin
+ writerId := Data.Values['id'].Value.ToInteger;
+ number := Data.Values['number'].Value.ToInteger;
+ if image.Locate('writerId;number', VarArrayOf([writerId, number])) = true then
+ result := image.FieldByName('id').AsInteger;
+end;
+
+procedure TDataModule1.imageView(id: integer; out Data: TJSONObject);
+begin
+ Data := TJSONObject.Create;
+ if image.Locate('id', id) = true then
+ begin
+ Data.AddPair('name', image.FieldByName('name').AsString);
+ Data.AddPair('data', image.FieldByName('data').AsString);
+ end;
+end;
+
+function TDataModule1.loginReader(Data: TJSONObject): integer;
+var
+ ma, pa: string;
+begin
+ ma := Data.Values['mail'].Value;
+ pa := Data.Values['password'].Value;
+ if reader.Locate('mail;password', VarArrayOf([ma, pa])) = true then
+ result := reader.FieldByName('readerid').AsInteger
+ else
+ result := 0;
+end;
+
+function TDataModule1.loginWriter(Data: TJSONObject): integer;
+var
+ ma, pa: string;
+begin
+ ma := Data.Values['mail'].Value;
+ pa := Data.Values['password'].Value;
+ if writer.Locate('mail;password', VarArrayOf([ma, pa])) = true then
+ result := writer.FieldByName('writerid').AsInteger
+ else
+ result := 0;
+end;
+
procedure TDataModule1.magData(id: integer; out Data: TJSONObject);
begin
FDQuery1.SQL.Clear;
Data.AddPair('day', FDQuery1.FieldByName('day').AsString);
Data.AddPair('last', FDQuery1.FieldByName('lastDay').AsString);
FDQuery1.SQL.Clear;
- FDQuery1.SQL.Add('select COUNT(*) as count from indextable where magid = :id;');
- FDQuery1.ParamByName('id').AsInteger:=id;
+ FDQuery1.SQL.Add
+ ('select COUNT(*) as count from indextable where magid = :id;');
+ FDQuery1.ParamByName('id').AsInteger := id;
FDQuery1.Open;
Data.AddPair('count', FDQuery1.FieldByName('count').AsString);
end;
end;
function TDataModule1.magid(name: string): integer;
+var
+ v: Variant;
begin
- result := mag.Lookup('magname', name, 'magid');
+ v := mag.Lookup('magNum', name, 'magid');
+ if VarIsNull(v) = true then
+ result := 0
+ else
+ result := v;
end;
procedure TDataModule1.magIdOff(id, magid: integer);
begin
- if indexTable.Locate('readerId;magId', VarArrayOf([id, magid])) = true then
- indexTable.Delete;
+ if DB.Locate('readerId;magId', VarArrayOf([id, magid])) = true then
+ DB.Delete;
end;
procedure TDataModule1.magIdOn(id, magid: integer);
+var
+ i: integer;
+ v: Variant;
begin
- if (writer.Locate('readerid', id) = true) and
- (mag.Locate('magid', magid) = true) then
- indexTable.AppendRecord([id, magid]);
+ v := DB.Lookup('magId', magid, 'writerId');
+ if VarIsNull(v) = true then
+ v := 0;
+ FDQuery1.Open('select MAX(serial) as ser from db;');
+ i := FDQuery1.FieldByName('ser').AsInteger + 1;
+ DB.AppendRecord([i, v, magid, id]);
end;
-procedure TDataModule1.magListAll(out Data: TJSONObject);
+procedure TDataModule1.magListAll(id: integer; out Data: TJSONObject);
var
js: TJSONObject;
ar: TJSONArray;
begin
mag.First;
ar := TJSONArray.Create;
- FDQuery1.Open('select magId,COUNT(*) as count from indexTable group by magId');
+ FDQuery1.Open
+ ('select magId,COUNT(*) as count from db where readerId <> 0 group by magId');
while mag.Eof = false do
begin
- i:=mag.FieldByName('magId').AsInteger;
- v:=FDQuery1.Lookup('magId',i,'count');
+ i := mag.FieldByName('magId').AsInteger;
+ v := FDQuery1.Lookup('magId', i, 'count');
if VarIsNull(v) = true then
- v:=0;
+ v := 0;
js := TJSONObject.Create;
+ js.AddPair('magNum', mag.FieldByName('magNum').AsString);
js.AddPair('magName', mag.FieldByName('magName').AsString);
js.AddPair('comment', mag.FieldByName('comment').AsString);
js.AddPair('day', mag.FieldByName('day').AsString);
js.AddPair('lastDay', mag.FieldByName('lastDay').AsString);
- js.AddPair('count',v);
+ js.AddPair('count', v);
+ v := DB.Lookup('magId', i, 'writerId');
+ v := writer.Lookup('writerId', v, 'writer');
+ if VarIsNull(v) = true then
+ js.AddPair('writer', TJSONFalse.Create)
+ else
+ js.AddPair('writer', v);
+ if (id = 0) or (DB.Locate('readerId;magid', VarArrayOf([id, i])) = false)
+ then
+ js.AddPair('fun', TJSONFalse.Create)
+ else
+ js.AddPair('fun', TJSONTrue.Create);
ar.Add(js);
mag.Next;
end;
Data.AddPair('enable', val);
end;
+procedure TDataModule1.mainView(id: integer; out Data: TJSONObject);
+begin
+ Data := TJSONObject.Create;
+ with FDQuery1.SQL do
+ begin
+ Clear;
+ Add('select nagName,writer,text,day,changed from db,news,mag,writer');
+ Add(' where db.newsId = news.newsId and db.magId = mag.magId and');
+ Add(' db.writerId = writer.writerId and db.reader.id = :id order by day;');
+ end;
+ FDQuery1.Params.ParamByName('id').AsInteger := id;
+ Data := makeTable(FDQuery1);
+end;
+
procedure TDataModule1.magazines(id: integer; out Data: TJSONObject);
var
d: TJSONObject;
list: TList<integer>;
i: integer;
begin
- d:=data;
+ d := Data;
FDQuery1.SQL.Clear;
FDQuery1.SQL.Add('select * from maglist where writerid = :id;');
FDQuery1.Params.ParamByName('id').AsInteger := id;
FDQuery1.Open;
ar := TJSONArray.Create;
- list:=TList<integer>.Create;
+ list := TList<integer>.Create;
while FDQuery1.Eof = false do
begin
list.Add(FDQuery1.FieldByName('magId').AsInteger);
end;
for i in list do
begin
- magData(i,d);
+ magData(i, d);
ar.Add(d);
end;
list.Free;
function TDataModule1.makeTable(Sender: TObject): TJSONObject;
var
+ day: string;
blob: TStream;
mem: TStringList;
+ ar: TJSONArray;
+ d: TJSONObject;
begin
- result := TJSONObject.Create;
+ ar := TJSONArray.Create;
mem := TStringList.Create;
with Sender as TFDQuery do
begin
First;
while Eof = false do
begin
- if FieldByName('updated').AsBoolean = true then
- result.AddPair('hint', Format('\82±\82Ì\8bL\8e\96\82Í\8dX\90V\82³\82ê\82Ü\82µ\82½:(%s)\93ú.',
- [FieldByName('day').AsString]));
- blob := CreateBlobStream(FieldByName('file'), bmRead);
+ d := TJSONObject.Create;
+ ar.Add(d);
+ day := FieldByName('day').AsString;
+ if FieldByName('changed').AsBoolean = true then
+ d.AddPair('hint', Format('\82±\82Ì\8bL\8e\96\82Í\8dX\90V\82³\82ê\82Ü\82µ\82½:(%s)\93ú.', [day]));
+ blob := CreateBlobStream(FieldByName('files'), bmRead);
mem.LoadFromStream(blob);
blob.Free;
- result.AddPair('text', mem.Text);
+ d.AddPair('magName', FieldByName('magName').AsString);
+ d.AddPair('writer', FieldByName('writer').AsString);
+ d.AddPair('text', mem.Text);
+ d.AddPair('day', day);
Next;
end;
end;
mem.Free;
+ result := TJSONObject.Create;
+ result.AddPair('mag', ar);
end;
procedure TDataModule1.postMessage(id: integer; Data: TJSONObject);
end;
end;
-procedure TDataModule1.titleView(id: integer; Data: TJSONObject);
+function TDataModule1.titleView(magid, writerId: integer;
+ out Data: TJSONObject): Boolean;
var
- d: TJSONObject;
i: integer;
+ v: Variant;
begin
- d := Data;
- FDQuery1.SQL.Clear;
- FDQuery1.SQL.Add('select * from mag where magid = :id;');
- FDQuery1.Params.ParamByName('id').AsInteger := id;
- FDQuery1.Open;
- with FDQuery1 do
- while Eof = false do
- begin
- for i := 0 to Fields.Count - 1 do
- d.AddPair(Fields[i].FieldName, Fields[i].AsString);
- Next;
- end;
+ result := false;
+ if writerId = 0 then
+ Exit;
+ v := writer.Lookup('writerId', writerId, 'writer');
+ if VarIsNull(v) = true then
+ v := 'no one';
+ if mag.Locate('magid', magid) = true then
+ begin
+ Data := TJSONObject.Create;
+ Data.AddPair('writer', v);
+ for i := 0 to mag.Fields.Count - 1 do
+ Data.AddPair(mag.Fields[i].FieldName, mag.Fields[i].AsString);
+ result := true;
+ end;
end;
-procedure TDataModule1.updateReaderId(Data: TJSONObject);
+function TDataModule1.updateReaderId(Data: TJSONObject): Boolean;
var
na, ma, pa: string;
begin
- na:=Data.Values['reader'].Value;
- ma:=Data.Values['mail'].Value;
- pa:=data.Values['password'].Value;
- with reader do
- begin
- Edit;
- FieldByName('reader').AsString:=na;
- FieldByName('mail').AsString:=ma;
- FieldByName('password').AsString:=pa;
- Post;
- end;
+ na := Data.Values['reader'].Value;
+ ma := Data.Values['mail'].Value;
+ pa := Data.Values['password'].Value;
+ if reader.Locate('id', Data.Values['id'].Value.ToInteger) = true then
+ with reader do
+ begin
+ Edit;
+ FieldByName('reader').AsString := na;
+ FieldByName('mail').AsString := ma;
+ FieldByName('password').AsString := pa;
+ Post;
+ result := true;
+ end
+ else
+ result := false;
end;
procedure TDataModule1.updateWriterId(id: integer; Data: TJSONObject);
OSDN Git Service
