floor.pas

一个实用的房地产行业收费管理软件

procedure TForm1.DBGridEh1KeyPress(Sender: TObject; var Key: Char);
begin
{ if key=#13 then
  begin
  key:=#0;
//   showmessage(inttostr(dbgrideh3.Columns.Count));
  if dbgrideh1.Col<dbgrideh1.Columns.Count then
  dbgridEh1.Col:=dbgridEh1.Col+1 else
  begin
  dbgrideh1.Col:=1;
  if  dbgrideh1.row<dbgrideh1.RowCount-1 then
     dbgrideh1.Row:=dbgrideh1.Row+1
     else
     dbgrideh1.Row:=dbgrideh1.Rowcount-1;
  end;
 end;}
end;

procedure TForm1.BitBtn21Click(Sender: TObject);
begin
 if dbcomboboxeh2.Value='' then
    begin
    application.MessageBox('大厦不能为空！','提示',mb_ok);
    abort;
    end;
 if dbcomboboxeh3.Value='' then
    begin
    application.MessageBox('年份不能为空！','提示',mb_ok);
    abort;
    end;
 if dbcomboboxeh4.Value='' then
    begin
    application.MessageBox('月份不能为空！','提示',mb_ok);
    abort;
    end;

dbgrideh1.ReadOnly:=false;
bitbtn3.Enabled:=false;
bitbtn21.Enabled:=false;
bitbtn14.Enabled:=true;
datamodule1.sdq.Append;
end;

procedure TForm1.BitBtn3Click(Sender: TObject);
var ii:integer;
begin
 if dbcomboboxeh2.Value='' then
    begin
    application.MessageBox('大厦不能为空！','提示',mb_ok);
    abort;
    end;
 if dbcomboboxeh3.Value='' then
    begin
    application.MessageBox('年份不能为空！','提示',mb_ok);
    abort;
    end;
 if dbcomboboxeh4.Value='' then
    begin
    application.MessageBox('月份不能为空！','提示',mb_ok);
    abort;
    end;

dbgrideh1.ReadOnly:=false;
dbgrideh1.Columns[0].ReadOnly:=true;
//for ii:=6 to 14 do
//dbgrideh1.Columns[ii].ReadOnly:=true;
bitbtn3.Enabled:=false;
bitbtn21.Enabled:=false;
bitbtn14.Enabled:=true;
datamodule1.sdq.Edit;  //如果去掉，那字段的READ　ONLY　设为真无效。
end;

procedure TForm1.BitBtn14Click(Sender: TObject);
begin
dbgrideh1.ReadOnly:=true;
bitbtn14.Enabled:=false;
bitbtn3.Enabled:=true;
bitbtn21.Enabled:=true;
  with datamodule1.sdq do
  begin
     datamodule1.Database1.StartTransaction;
    try
      ApplyUpdates; {try to write the updates to the database};
      datamodule1.Database1.Commit; {on success, commit the changes};
    except
      datamodule1.Database1.Rollback; {on failure, undo the changes};
    raise; {raise the exception to prevent a call to CommitUpdates!}
    end;
  CommitUpdates; {on success, clear the cache}
  with datamodule1.insertsd do
  begin
    sql.clear;
    sql.add('update sd set bss=bs-ls ,bsd=(bd-ld)*db,sump=(bs-ls)*sprice+(bd-ld)*db*dprice,samt=(bs-ls)*sprice,damt=(bd-ld)*db*dprice');
    sql.Add('where customno in(select customno from sd where months=:mm and years=:my and floor=:mf)');
    parambyname('mf').asstring:=dbcomboboxeh2.Value;
    parambyname('my').asstring:=dbcomboboxeh3.Value;
    parambyname('mm').asstring:=dbcomboboxeh4.Value;
    execsql;
  end;
 with datamodule1.sdq do
 begin
  sql.clear;
  sql.add('select * from sd   where floor=:mf and years=:my and months=:mm');
  parambyname('mf').asstring:=dbcomboboxeh2.Value;
  parambyname('my').asstring:=dbcomboboxeh3.Value;
  parambyname('mm').asstring:=dbcomboboxeh4.Value;
  open;
 end;  
  application.MessageBox('保存完毕！','提示',mb_ok)
  end;

end;

procedure TForm1.BitBtn12Click(Sender: TObject);
begin
 if dbcomboboxeh7.Value='' then
    begin
    application.MessageBox('大厦不能为空！','提示',mb_ok);
    abort;
    end;

 with datamodule1.htQ do
 begin
  sql.clear;
  sql.add('select a.*,b.custom,b.floor from ht a,company b where a.customno=b.customno and b.floor=:mf');
  if edit1.Text<>'' then
  sql.add('and htno=:htno');
  if edit1.Text<>'' then
  parambyname('htno').asstring:=edit1.Text;
  parambyname('mf').value:=dbcomboboxeh7.Value;
  open;        
 end;


end;

procedure TForm1.BitBtn9Click(Sender: TObject);
var
miount:integer;
begin

 if dbcomboboxeh7.Value='' then
    begin
    application.MessageBox('大厦不能为空！','提示',mb_ok);
    abort;
    end;
//dbgrideh2.ReadOnly:=false;
for miount:=0 to     listbox2.Items.Count-1 do
   listbox2.items.delete(miount);

for miount:=0 to     listbox3.Items.Count-1 do
   listbox3.items.delete(miount);

for miount:=0 to     listbox4.Items.Count-1 do
   listbox4.items.delete(miount);
edit2.SetFocus;
edit_state:=1;
bitbtn9.Enabled:=false;
bitbtn11.Enabled:=false;
bitbtn24.Enabled:=true;
edit4.Text:='';
edit2.text:='';
edit3.Text:='';
edit5.Text:='';
edit6.Text:='';
edit7.Text:='';
edit8.Text:='';
edit9.Text:='';
edit10.Text:='';
edit11.Text:='';
edit12.Text:='';
edit13.Text:='';

dbcomboboxeh5.Value:='RMB';
dbnumbereditEh10.Value:=0;
dbnumbereditEh11.Value:=0;
dbnumbereditEh12.Value:=0;
dbnumbereditEh16.Value:=0;
dbnumbereditEh17.Value:=0;
dbnumbereditEh1.Value:=0;
dbnumbereditEh2.Value:=0;
dbnumbereditEh18.Value:=0;
dbnumbereditEh19.Value:=0;
dbnumbereditEh3.Value:=0;
dbnumbereditEh13.Value:=0;
dbnumberediteh20.Value:=0;

//datamodule1.htq.Append;
end;

procedure TForm1.BitBtn11Click(Sender: TObject);
begin
 if dbcomboboxeh7.Value='' then
    begin
    application.MessageBox('大厦不能为空！','提示',mb_ok);
    abort;
    end;
// dbgrideh2.ReadOnly:=false;
 edit_state:=2;
 bitbtn9.Enabled:=false;
 bitbtn11.Enabled:=false;
 bitbtn24.Enabled:=true;
 edit4.Enabled:=false;
// datamodule1.htq.edit;
end;

procedure TForm1.BitBtn10Click(Sender: TObject);
var
roomyear,glfyear,xzlyear,artyear:real;
begin
dbgrideh2.ReadOnly:=true;
bitbtn10.Enabled:=false;
bitbtn11.Enabled:=true;
bitbtn9.Enabled:=true;
  with datamodule1.htq do
  begin
     datamodule1.Database1.StartTransaction;
    try
      ApplyUpdates; {try to write the updates to the database};
      datamodule1.Database1.Commit; {on success, commit the changes};
    except
      datamodule1.Database1.Rollback; {on failure, undo the changes};
    raise; {raise the exception to prevent a call to CommitUpdates!}
    end;
  CommitUpdates; {on success, clear the cache}

  datamodule1.htq.first;
  while   not datamodule1.htq.Eof do
  begin
  datamodule1.insertcustom.SQL.Clear;
  datamodule1.insertcustom.SQL.Add('update ht set ssxzl=xzl,ssart=art,ssroom=room,ssglf=glf');
  datamodule1.insertcustom.sql.add(' where floor=:mf and htno=:htno');
  datamodule1.insertcustom.parambyname('mf').Value:=dbcomboboxeh7.Value;
  datamodule1.insertcustom.parambyname('htno').Value:=datamodule1.htq.fieldbyname('htno').Value;
  datamodule1.insertcustom.Prepare;
  datamodule1.insertcustom.ExecSQL;

  datamodule1.insertcustom.SQL.Clear;
  datamodule1.insertcustom.SQL.Add('update ht set zj=ssxzl+ssart+ssroom+ssglf');
  datamodule1.insertcustom.sql.add(' where floor=:mf and htno=:htno');
  datamodule1.insertcustom.parambyname('mf').Value:=dbcomboboxeh7.Value;
  datamodule1.insertcustom.parambyname('htno').Value:=datamodule1.htq.fieldbyname('htno').Value;
  datamodule1.insertcustom.Prepare;
  datamodule1.insertcustom.ExecSQL;
  datamodule1.htq.Next;
  end;



{  with datamodule1.insertsd do
  begin
    sql.clear;
    sql.add('update sd set bss=bs-ls ,bsd=bd-ld,sump=(bs-ls)*sprice+(bd-ld)*dprice');
    sql.Add('where customno in(select customno from sd where months=:mm and years=:my and floor=:mf)');
    parambyname('mf').asstring:=dbcomboboxeh2.Value;
    parambyname('my').asstring:=dbcomboboxeh3.Value;
    parambyname('mm').asstring:=dbcomboboxeh4.Value;
    execsql;
  end;}   //可考虑在此算递增客户
  application.MessageBox('保存完毕！','提示',mb_ok)
  end;
end;

procedure TForm1.BitBtn23Click(Sender: TObject);
var
Sheet,Range,ExcelApp: Variant;
tt,ii,nn:integer;
total:real;
begin
tt:=0;
 if dbcomboboxeh2.Value='' then
    begin
    application.MessageBox('大厦不能为空！','提示',mb_ok);
    abort;
    end;
 with datamodule1.sdq do
 begin
  sql.clear;
  sql.add('select * from sd where  floor=:mf and years=:my and months=:mm');
  parambyname('mf').asstring:=dbcomboboxeh2.Value;
  parambyname('my').asstring:=dbcomboboxeh3.Value;
  parambyname('mm').asstring:=dbcomboboxeh4.Value;
  open;
 end;

ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.Visible := True;
ExcelApp.WorkBooks.Add;
nn:=0;
while not  datamodule1.sdq.Eof    do
begin
ExcelApp.Cells[1+nn+tt,1].Value :='                  收　　　　　　　据';
ExcelApp.ActiveSheet.Rows[1+nn+tt].RowHeight:=1.25/0.035; // 1厘米
ExcelApp.cells[1+nn+tt].Font.Name := '宋体';
ExcelApp.cells[1+nn+tt].Font.Bold := True;
ExcelApp.ActiveSheet.Range[ 'B2:E2'].Borders[3].Weight := 3;
ExcelApp.Cells[1+nn+tt,6].Value :='　 统收：';
ExcelApp.Cells[1+nn+tt,7].Value :='NO:'+datamodule1.sdq.FieldByName('tsno').Value;
ExcelApp.Cells[3+nn+tt,1].Value :='日　期:'+datetostr(date);
ExcelApp.Cells[4+nn+tt,1].Value :='客　户:';
ExcelApp.Cells[4+nn+tt,2].Value :=datamodule1.sdq.FieldByName('custom').Value;
Excelapp.Columns[5+nn+tt].ColumnWidth:=6.5;
Excelapp.Columns[6+nn+tt].ColumnWidth:=9.5;
//ExcelApp.Cells[4+nn+tt,5].Value :='记费期限:';
ExcelApp.Cells[4+nn+tt,6].Value :='记费期限:'+dbcomboboxeh3.value+'年'+dbcomboboxeh4.Value+'月';
ExcelApp.Cells[5+nn+tt,1].Value :='上月行度 ';
ExcelApp.Cells[5+nn+tt,2].Value :='本月行度';
ExcelApp.Cells[5+nn+tt,6].Value :=' 金　额';

ExcelApp.Cells[5+nn+tt,3].Value :='电　比';
ExcelApp.Cells[5+nn+tt,4].Value :='实行度数';
ExcelApp.Cells[5+nn+tt,5].Value :='单　价';
ExcelApp.Cells[5+nn+tt,7].Value :='备　注';
ExcelApp.Cells[6+nn+tt,1].Value :='水:'+datamodule1.sdq.FieldByName('ls').asstring;
ExcelApp.Cells[6+nn+tt,2].Value :=datamodule1.sdq.FieldByName('bs').asstring;
//ExcelApp.Cells[6+nn+tt,3].Value :=datamodule1.sdq.FieldByName('db').Value;
ExcelApp.Cells[6+nn+tt,4].Value :=datamodule1.sdq.FieldByName('bss').asstring;
ExcelApp.Cells[6+nn+tt,5].Value :=datamodule1.sdq.FieldByName('sprice').asstring;
ExcelApp.Cells[6+nn+tt,6].Value :=(datamodule1.sdq.FieldByName('bs').Value-datamodule1.sdq.FieldByName('ls').Value)*datamodule1.sdq.FieldByName('sprice').Value;
ExcelApp.Cells[6+nn+tt,7].Value :=datamodule1.sdq.FieldByName('note').Value;
ExcelApp.Cells[7+nn+tt,1].Value :='电:'+datamodule1.sdq.FieldByName('ld').asstring;
ExcelApp.Cells[7+nn+tt,2].Value :=datamodule1.sdq.FieldByName('bd').asstring;
ExcelApp.Cells[7+nn+tt,3].Value :=datamodule1.sdq.FieldByName('db').asstring;
ExcelApp.Cells[7+nn+tt,4].Value :=datamodule1.sdq.FieldByName('bsd').asstring;
ExcelApp.Cells[7+nn+tt,5].Value :=datamodule1.sdq.FieldByName('dprice').asstring;
ExcelApp.Cells[7+nn+tt,6].Value :=(datamodule1.sdq.FieldByName('bd').Value-datamodule1.sdq.FieldByName('ld').Value)*datamodule1.sdq.FieldByName('dprice').Value*datamodule1.sdq.FieldByName('db').Value;

ExcelApp.Cells[13+nn+tt,1].Value :='   合计:';
ExcelApp.Cells[13+nn+tt,6].Value :=datamodule1.sdq.FieldByName('sump').Value;
ExcelApp.Cells[15+nn+tt,2].Value :='会计:';
ExcelApp.Cells[15+nn+tt,4].Value :='   制单:';
ExcelApp.Cells[15+nn+tt,6].Value :='   出纳:';
ExcelApp.ActiveSheet.Range[ 'B'+inttostr(2+nn+tt)+':E'+inttostr(2+nn+tt)].Borders[3].Weight := 3;
ExcelApp.ActiveSheet.Range[ 'A'+inttostr(3+nn+tt)+':G'+inttostr(3+nn+tt)].Borders[3].Weight := 3;
ExcelApp.ActiveSheet.Range[ 'A'+inttostr(4+nn+tt)+':G'+inttostr(13+nn+tt)].Borders[3].Weight :=2;
ExcelApp.ActiveSheet.Range[ 'A'+inttostr(14+nn+tt)+':G'+inttostr(14+nn+tt)].Borders[3].Weight :=3;
ExcelApp.ActiveSheet.Range[ 'A'+inttostr(3+nn+tt)+':A'+inttostr(13+nn+tt)].Borders[1].Weight :=3;
ExcelApp.ActiveSheet.Range[ 'G'+inttostr(3+nn+tt)+':G'+inttostr(13+nn+tt)].Borders[2].Weight :=3;
ExcelApp.ActiveSheet.Range[ 'A'+inttostr(5+nn+tt)+':A'+inttostr(12+nn+tt)].Borders[2].Weight :=2;
ExcelApp.ActiveSheet.Range[ 'B'+inttostr(5+nn+tt)+':B'+inttostr(13+nn+tt)].Borders[2].Weight :=2;
ExcelApp.ActiveSheet.Range[ 'C'+inttostr(5+nn+tt)+':C'+inttostr(12+nn+tt)].Borders[2].Weight :=2;
ExcelApp.ActiveSheet.Range[ 'D'+inttostr(5+nn+tt)+':D'+inttostr(12+nn+tt)].Borders[2].Weight :=2;
ExcelApp.ActiveSheet.Range[ 'E'+inttostr(5+nn+tt)+':E'+inttostr(12+nn+tt)].Borders[2].Weight :=2;
ExcelApp.ActiveSheet.Range[ 'F'+inttostr(5+nn+tt)+':F'+inttostr(12+nn+tt)].Borders[2].Weight :=2;
nn:=nn+16;
datamodule1.sdq.Next;
end;
{frReport1.LoadFromFile('D:\floor\sdreport.frf');
frReport1.PrepareReport;
frReport1.ShowReport;}



end;

procedure TForm1.BitBtn22Click(Sender: TObject);
var
Sheet,Range,ExcelApp: Variant;
cc,mtsno,tm,rr,tt,mcol,miount,ii,nn:integer;
mtt,Ndate1,Ndate2,Tnote,Tcfile:string;
total:real;
yy1,dd1,mm1,yy2,dd2,mm2:word;
begin
 if dbcomboboxeh7.Value='' then
    begin
    application.MessageBox('大厦不能为空！','提示',mb_ok);
    abort;
    end;

 with datamodule1.ptq do