From 99590e421eb9031091aead08bf4417634dceba14 Mon Sep 17 00:00:00 2001 From: yamat0jp Date: Sun, 8 Mar 2020 20:24:32 +0900 Subject: [PATCH] =?utf8?q?=E3=81=BE=E3=81=A0=E3=83=A9=E3=82=A4=E3=82=BF?= =?utf8?q?=E3=83=BC=E3=81=8C=E8=A4=87=E6=95=B0=E3=81=A0=E3=81=A3=E3=81=9F?= =?utf8?q?=E5=A0=B4=E5=90=88=E3=81=8C=E6=B1=BA=E3=81=BE=E3=81=A3=E3=81=A6?= =?utf8?q?=E3=81=84=E3=81=BE=E3=81=9B=E3=82=93?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- Unit1.dfm | 17 +++--- Unit1.pas | 154 ++++++++++++++++++++++++++++------------------------- WebModuleUnit1.dfm | 13 +++-- 3 files changed, 97 insertions(+), 87 deletions(-) diff --git a/Unit1.dfm b/Unit1.dfm index d9297ae..1b0b2c3 100644 --- a/Unit1.dfm +++ b/Unit1.dfm @@ -16,6 +16,11 @@ object DataModule1: TDataModule1 ProviderFlags = [pfInUpdate, pfInWhere, pfInKey] Required = True end + object dbwriterId: TIntegerField + AutoGenerateValue = arDefault + FieldName = 'writerId' + Origin = 'writerId' + end object dbmagId: TIntegerField AutoGenerateValue = arDefault FieldName = 'magId' @@ -26,11 +31,6 @@ object DataModule1: TDataModule1 FieldName = 'readerId' Origin = 'readerId' end - object dbwriterId: TIntegerField - AutoGenerateValue = arDefault - FieldName = 'writerId' - Origin = 'writerId' - end end object reader: TFDTable IndexFieldNames = 'readerId' @@ -77,7 +77,7 @@ object DataModule1: TDataModule1 Top = 80 end object news: TFDTable - IndexFieldNames = 'newsId' + IndexFieldNames = 'magId;newsId' Connection = MagazineConnection UpdateOptions.UpdateTableName = 'news' TableName = 'news' @@ -90,9 +90,10 @@ object DataModule1: TDataModule1 Required = True end object newsnewsId: TIntegerField - FieldKind = fkCalculated FieldName = 'newsId' - Calculated = True + Origin = 'newsId' + ProviderFlags = [pfInUpdate, pfInWhere, pfInKey] + Required = True end object newsday: TDateField AutoGenerateValue = arDefault diff --git a/Unit1.pas b/Unit1.pas index 81df1f4..dd37976 100644 --- a/Unit1.pas +++ b/Unit1.pas @@ -24,10 +24,6 @@ type writerwriter: TWideStringField; writermail: TWideStringField; writerpassword: TWideStringField; - newsmagId: TIntegerField; - newsday: TDateField; - newschanged: TBooleanField; - newsenabled: TBooleanField; readerreaderId: TIntegerField; readerreader: TWideStringField; readermail: TWideStringField; @@ -40,7 +36,6 @@ type maglastDay: TDateField; magenable: TBooleanField; image: TFDTable; - newsnewsId: TIntegerField; magmagNum: TWideStringField; imagewriterId: TIntegerField; imagenumber: TIntegerField; @@ -50,6 +45,11 @@ type dbmagId: TIntegerField; dbreaderId: TIntegerField; dbwriterId: TIntegerField; + newsmagId: TIntegerField; + newsnewsId: TIntegerField; + newsday: TDateField; + newschanged: TBooleanField; + newsenabled: TBooleanField; procedure DataModuleCreate(Sender: TObject); private { Private éŒ¾ } @@ -81,7 +81,8 @@ type procedure postMessage(id: integer; Data: TJSONObject); procedure createWriterId(Data: TJSONObject); procedure readerData(id: integer; out Data: TJSONObject); - procedure titleView(id: integer; out 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; @@ -204,7 +205,7 @@ begin FDQuery1.ExecSQL (tmp + 'reader(readerId int primary key, reader varchar(20), mail varchar(20), password varchar(20));'); FDQuery1.ExecSQL - (tmp + 'news(magId int, newsId int, day date, changed bool, enabled bool, primary key (magId,newsId));'); + (tmp + 'news(magId int, newsId int, files text, day date, changed bool, enabled bool, primary key (magId,newsId));'); FDQuery1.ExecSQL (tmp + 'image(writerId int, number int, name varchar(20), data text, primary key (writerId,number));'); DB.Open; @@ -275,28 +276,22 @@ end; procedure TDataModule1.userView(id: integer; out Data: TJSONObject); var - i: integer; - list: TList; ar: TJSONArray; d: TJSONObject; begin - list := TList.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 - begin - list.Add(FDQuery1.FieldByName('magid').AsInteger); - FDQuery1.Next; - end; - ar := TJSONArray.Create; - for i in list do - begin - titleView(i, d); - ar.Add(d); - end; - 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; @@ -317,23 +312,27 @@ end; procedure TDataModule1.custView(id: integer; out Data: TJSONObject); var - i: integer; - list: TList; + d: TJSONObject; + ar: TJSONArray; begin Data := TJSONObject.Create; - list := TList.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); @@ -341,7 +340,7 @@ begin 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; @@ -356,12 +355,14 @@ 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; @@ -452,10 +453,16 @@ begin end; procedure TDataModule1.magIdOn(id, magid: integer); +var + i: integer; + v: Variant; begin - if (reader.Locate('readerid', id) = true) and - (mag.Locate('magid', magid) = true) then - DB.AppendRecord([mag.FieldByName('writerid').AsInteger, magid, id]); + 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(id: integer; out Data: TJSONObject); @@ -469,7 +476,7 @@ begin mag.First; ar := TJSONArray.Create; FDQuery1.Open - ('select magId,COUNT(*) as count from indexTable group by magId'); + ('select magId,COUNT(*) as count from db where readerId <> 0 group by magId'); while mag.Eof = false do begin i := mag.FieldByName('magId').AsInteger; @@ -512,20 +519,15 @@ end; procedure TDataModule1.mainView(id: integer; out Data: TJSONObject); begin Data := TJSONObject.Create; - FDQuery1.SQL.Clear; - FDQuery1.SQL.Add('select * from news,magId where news.magId = magName.magId'); - FDQuery1.SQL.Add(' and magId = :id order by day;'); - FDQuery1.Params.ParamByName('id').AsInteger := id; - while FDQuery1.Eof = false do + with FDQuery1.SQL do begin - if FDQuery1.FieldByName('enebled').AsBoolean = true then - begin - if FDQuery1.FieldByName('changed').AsBoolean = true then - Data.AddPair('changed', TJSONTrue.Create); - Data.AddPair('', FDQuery1.FieldByName('').AsString); - end; - FDQuery1.Next; + 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); @@ -583,11 +585,13 @@ begin d := TJSONObject.Create; ar.Add(d); day := FieldByName('day').AsString; - if FieldByName('updated').AsBoolean = true then + if FieldByName('changed').AsBoolean = true then d.AddPair('hint', Format('‚±‚Ì‹LŽ–‚͍XV‚³‚ê‚Ü‚µ‚½:(%s)“ú.', [day])); - blob := CreateBlobStream(FieldByName('file'), bmRead); + blob := CreateBlobStream(FieldByName('files'), bmRead); mem.LoadFromStream(blob); blob.Free; + d.AddPair('magName', FieldByName('magName').AsString); + d.AddPair('writer', FieldByName('writer').AsString); d.AddPair('text', mem.Text); d.AddPair('day', day); Next; @@ -595,7 +599,7 @@ begin end; mem.Free; result := TJSONObject.Create; - result.AddPair('news', ar); + result.AddPair('mag', ar); end; procedure TDataModule1.postMessage(id: integer; Data: TJSONObject); @@ -630,24 +634,26 @@ begin end; end; -procedure TDataModule1.titleView(id: integer; out Data: TJSONObject); +function TDataModule1.titleView(magid, writerId: integer; + out Data: TJSONObject): Boolean; var - d: TJSONObject; i: integer; + v: Variant; begin - Data := TJSONObject.Create; - 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; function TDataModule1.updateReaderId(Data: TJSONObject): Boolean; diff --git a/WebModuleUnit1.dfm b/WebModuleUnit1.dfm index 62441e1..85d95d7 100644 --- a/WebModuleUnit1.dfm +++ b/WebModuleUnit1.dfm @@ -69,6 +69,7 @@ object WebModule1: TWebModule1 OnAction = WebModule1logoutAction end item + MethodType = mtGet Name = 'mainView' PathInfo = '/reader/top' OnAction = WebModule1mainViewAction @@ -101,7 +102,8 @@ object WebModule1: TWebModule1 '

'#12354#12394#12383#12398#26412#26842#12391#12377 ' {{#mag}}' ' ' - ' ' ' ' ' ' ' {{^enable}}{{/enable}}' @@ -299,14 +301,15 @@ object WebModule1: TWebModule1 '' '' '

'#12491#12517#12540#12473#12398#25968#12293#12434#12362#27005#12375#12415#12367#12384#12373#12356 - ' {{#news}}' + ' {{#mag}}' '

' '

[{{magName}}] ({{day}})' + '

by {{writer}}' ' {{text}}' - ' {{#updated}}' + ' {{#changed}}' '

{{hint}}' - ' {{/updated}}' - ' {{/news}}' + ' {{/changed}}' + ' {{/mag}}' '' '') Left = 240 -- 2.11.0

{{name}} writed by {{writer}}' + ' {{magName}} writed by {{writer}}' + '
{{comment}}
{{day}}
{{lastDay}}
'#20241#21002#20013'