cjdqcxpas.pas

本软件是一套功能完整成熟的固定资产管理系统,具有固定资产管理系统的相应全部功能

unit cjdqcxpas;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls, Grids, DBGrids, DB, ExtCtrls, ADODB,
  OleServer, Excel2000, QRCtrls, QuickRpt, QRPrntr;

type
  Tw_cjdqcx = class(TForm)
    Panel1: TPanel;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    Button2: TButton;
    Button4: TButton;
    Button5: TButton;
    lscx: TADOQuery;
    Button3: TButton;
    Label1: TLabel;
    edate: TDateTimePicker;
    ExcelApplication1: TExcelApplication;
    QuickRep1: TQuickRep;
    PageHeaderBand1: TQRBand;
    DetailBand1: TQRBand;
    bbbt: TQRLabel;
    QRLabel2: TQRLabel;
    QRLabel3: TQRLabel;
    QRLabel4: TQRLabel;
    QRLabel5: TQRLabel;
    QRLabel6: TQRLabel;
    QRLabel7: TQRLabel;
    QRLabel8: TQRLabel;
    QRLabel9: TQRLabel;
    QRLabel10: TQRLabel;
    QRLabel12: TQRLabel;
    QRShape1: TQRShape;
    QRDBText1: TQRDBText;
    QRDBText2: TQRDBText;
    QRDBText3: TQRDBText;
    QRDBText4: TQRDBText;
    QRDBText7: TQRDBText;
    QRDBText8: TQRDBText;
    QRDBText9: TQRDBText;
    QRDBText10: TQRDBText;
    QRDBText11: TQRDBText;
    QRDBText12: TQRDBText;
    QRShape2: TQRShape;
    QRLabel14: TQRLabel;
    QRSysData1: TQRSysData;
    PageFooterBand1: TQRBand;
    QRLabel11: TQRLabel;
    QRDBText5: TQRDBText;
    QRLabel1: TQRLabel;
    QRSysData2: TQRSysData;
    QRLabel15: TQRLabel;
    zys: TQRLabel;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button5Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure FormShow(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure QuickRep1Preview(Sender: TObject);
    procedure Button4Click(Sender: TObject);
  private
function cxjgmap(sourstr:string;detistr:string):string;
  { Private declarations }
  public
  cxjg:string;
//cxjg 为条件查询中所得到的结果
    { Public declarations }
  end;

var
  w_cjdqcx: Tw_cjdqcx;

implementation

uses datapas, cxtjpas,prinprevpas, wxpas;
var cxstr:string;

{$R *.dfm}

function tw_cjdqcx.cxjgmap(sourstr:string;detistr:string):string;
begin
if sourstr='资产类别' then
begin
with lscx do
begin
close;
sql.clear;
sql.add('select 资产类别编码 from 固定资产类别表 where 固定资产类别=:gdzclb');
parameters.parambyname('gdzclb').value:=detistr;
prepared;
open;
if not isempty then
result:=inttostr(fields[0].asinteger);
end;
end else
if sourstr='资产名称' then
begin
with lscx do
begin
close;
sql.clear;
sql.add('select 资产名称编码 from 固定资产名称表 where 固定资产名称=:gdzclb');
parameters.parambyname('gdzclb').value:=detistr;
prepared;
open;
if not isempty then
result:=inttostr(fields[0].asinteger);
end;
end else
if sourstr='资产用途' then
begin
with lscx do
begin
close;
sql.clear;
sql.add('select 资产用途编码 from 固定资产用途表 where 固定资产用途=:gdzclb');
parameters.parambyname('gdzclb').value:=detistr;
prepared;
open;
if not isempty then
result:=inttostr(fields[0].asinteger);
end;
end else
if sourstr='资产型号' then
begin
with lscx do
begin
close;
sql.clear;
sql.add('select 资产型号编码 from 固定资产型号表 where 固定资产型号=:gdzclb');
parameters.parambyname('gdzclb').value:=detistr;
prepared;
open;
if not isempty then
result:=inttostr(fields[0].asinteger);
end;
end else
if sourstr='供应商' then
begin
with lscx do
begin
close;
sql.clear;
sql.add('select 供应商编码 from 供应商编码表 where 供应商名称=:gdzclb');
parameters.parambyname('gdzclb').value:=detistr;
prepared;
open;
if not isempty then
result:=inttostr(fields[0].asinteger);
end;
end else
if sourstr='经手人' then
begin
with lscx do
begin
close;
sql.clear;
sql.add('select 职员编码 from 职员编码表 where 职员姓名=:gdzclb');
parameters.parambyname('gdzclb').value:=detistr;
prepared;
open;
if not isempty then
result:=inttostr(fields[0].asinteger);
end;
end else
if sourstr='是否归还' then
begin
if detistr='是' then
result:='true' else result:='false';
end else
if (sourstr='借用人姓名') or (sourstr='存放地点') or (sourstr='序列号') or (sourstr='配置') then
result:='"'+detistr+'"' else
result:=detistr;
end;



function pxstrmap(sourstr:string):string;
begin
if sourstr='zclb' then
result:='资产类别编码' else
if sourstr='zcmc' then
result:='资产名称编码' else
if sourstr='zcyt' then
result:='资产用途编码' else
if sourstr='zcxh' then
result:='资产型号编码' else
if sourstr='gys' then
result:='供应商编码' else
if sourstr='zyxm' then
result:='职员编码'  else
if sourstr='经手人' then
result:='经手人编码' else
result:=sourstr;
end;


procedure Tw_cjdqcx.FormClose(Sender: TObject; var Action: TCloseAction);
begin
action:=cafree;
end;

procedure Tw_cjdqcx.Button5Click(Sender: TObject);
begin
close;
end;

procedure Tw_cjdqcx.Button1Click(Sender: TObject);
begin
with hjzcdata.cjdqcx do
begin
close;
sql.clear;
sql.text:=cxstr;
//parameters.ParamByName('sdate').value:=sdate.DateTime;
parameters.ParamByName('edate').value:=edate.DateTime;
prepared;
open;
end;
end;

procedure Tw_cjdqcx.DBGrid1TitleClick(Column: TColumn);
var s:string;
pxstr:string;
I:INTEGER;
ordexiststr:string;
begin
//将含箭头的题还原
FOR I:=0 TO hjzcdata.cjdqcx.FieldCount-1 DO
BEGIN
S:=HJZCDATA.cjdqcx.Fields[I].DisplayLabel;
IF (HJZCDATA.cjdqcx.FIELDS[I]<>column.Field) and ((copy(s,length(s)-1,2)='↑') OR (copy(s,length(s)-1,2)='↓')) THEN
HJZCDATA.cjdqcx.FIELDS[I].DISPLAYLABEL:=COPY(S,1,LENGTH(S)-2);
END;

pxstr:=column.FieldName;
s:=column.Field.DisplayLabel;
ordexiststr:=hjzcdata.cjdqcx.SQL.Text;   //保存原来查询字符串内容
if Pos('order',ordexiststr) > 0 then
ordexiststr:=copy(ordexiststr,1,pos('order',ordexiststr)-1);

if (copy(s,length(s)-1,2)<>'↑') and (copy(s,length(s)-1,2)<>'↓') then
begin
column.Field.DisplayLabel:=s+'↑';
hjzcdata.cjdqcx.close;
if (pxstr='zclb') or (pxstr='zcmc') or (pxstr='zcyt') or (pxstr='zcxh') or (pxstr='gys') or (pxstr='zyxm') then
pxstr:=pxstrmap(pxstr) else
if pxstr='固定资产编码' then
pxstr:='a.固定资产编码';
hjzcdata.cjdqcx.SQL.Text:=ordexiststr+' order by '+pxstr+' asc';
hjzcdata.cjdqcx.Prepared;
hjzcdata.cjdqcx.Open;
end else
if copy(s,length(s)-1,2)='↑' then
begin
//降序排列
column.Field.DisplayLabel:=copy(s,1,length(s)-2)+'↓';
hjzcdata.cjdqcx.close;
if (pxstr='zclb') or (pxstr='zcmc') or (pxstr='zcyt') or (pxstr='zcxh') or (pxstr='gys') or (pxstr='zyxm') then
pxstr:=pxstrmap(pxstr) else
if pxstr='固定资产编码' then
pxstr:='a.固定资产编码';
hjzcdata.cjdqcx.SQL.Text:=ordexiststr+' order by '+pxstr+' desc';
hjzcdata.cjdqcx.Prepared;
hjzcdata.cjdqcx.Open;
end else
if copy(s,length(s)-1,2)='↓' then
begin
//升序排列
column.Field.DisplayLabel:=copy(s,1,length(s)-2)+'↑';
hjzcdata.cjdqcx.close;
if (pxstr='zclb') or (pxstr='zcmc') or (pxstr='zcyt') or (pxstr='zcxh') or (pxstr='gys') or (pxstr='zyxm') then
pxstr:=pxstrmap(pxstr) else
if pxstr='固定资产编码' then
pxstr:='a.固定资产编码';
hjzcdata.cjdqcx.SQL.Text:=ordexiststr+' order by '+pxstr+' asc';
hjzcdata.cjdqcx.Prepared;
hjzcdata.cjdqcx.Open;
end;
end;

procedure Tw_cjdqcx.FormShow(Sender: TObject);
begin
cxstr:='select b.资产借出编码,a.固定资产编码,b.借用人姓名,a.资产类别编码,a.资产名称编码,a.资产用途编码,a.资产型号编码,';
cxstr:=cxstr+'b.是否归还,a.供应商编码,b.借用日期,b.拟还日期,b.经手人编码,b.借用地点,a.序列号,a.配置';
cxstr:=cxstr+' from 固定资产信息表 a,固定资产借用表 b';
cxstr:=cxstr+' where b.是否归还=false and  a.固定资产编码=b.固定资产编码 and b.拟还日期<:edate';
//sdate.Datetime:=strtodatetime('1900-01-01 00:00:00');
edate.DateTime:=strtodatetime(datetostr(now)+' 23:59:59');

end;

procedure Tw_cjdqcx.Button2Click(Sender: TObject);
var i,j:integer;
s,sortstr:string;
sortsour:string;
begin
w_tj:=tw_tj.create(application);
w_tj.tj.Items.Clear;
for i:=0 to dbgrid1.Columns.Count-1 do
begin
s:=dbgrid1.Columns.Items[i].Field.DisplayLabel;
if (copy(s,length(s)-1,2)='↑') OR (copy(s,length(s)-1,2)='↓') then
w_tj.tj.Items.Add(copy(s,1,length(s)-2)) else
w_tj.tj.Items.Add(s);
end;
if w_tj.showmodal=mrok then
begin
sortstr:='';
for i:=0 to dbgrid1.Columns.Count-1 do
for j:=1 to w_tj.sg1.RowCount-1 do
if (w_tj.sg1.cells[0,j]=dbgrid1.Columns.Items[i].Field.DisplayLabel) and (w_tj.sg1.cells[1,j]<>'') and (w_tj.sg1.cells[3,j]<>'') then
begin
if w_tj.sg1.cells[3,j]='并且' then
sortstr:=sortstr+' and' else
sortstr:=sortstr+' or';
sortsour:=dbgrid1.Columns.Items[i].FieldName;
sortstr:=sortstr+' '+pxstrmap(sortsour)+' '+w_tj.sg1.cells[1,j]+' ';
sortstr:=sortstr+cxjgmap(w_tj.sg1.cells[0,j],w_tj.sg1.Cells[2,j]);
end;
if sortstr<>'' then
with hjzcdata.cjdqcx do
begin
close;
SQL.text:=cxstr+sortstr;
//parameters.ParamByName('sdate').value:=sdate.DateTime;
parameters.ParamByName('edate').value:=edate.DateTime;
prepared;
open;
end;
end;
end;

procedure Tw_cjdqcx.Button3Click(Sender: TObject);
var
RangeE: Excel2000.Range;
I, Row: Integer;
Bookmark: TBookmarkStr;
zdname:string;
bbgs:boolean;
begin
with lscx do
begin
close;
sql.clear;
sql.add('select 报表格式 from 参数表');
prepared;
open;
bbgs:=fields[0].value;
end;
if not bbgs then  //采用excel报表
begin
ExcelApplication1.Visible [0] := True;
ExcelApplication1.Workbooks.Add (NULL, 0);
// fill is the first row with field titles
RangeE := ExcelApplication1.ActiveCell;
for I := 0 to dbgrid1.Columns.Count - 1 do
begin
RangeE.Value := dbgrid1.Columns.Items[i].Field.DisplayLabel;
RangeE := RangeE.Next;
end;
// add field data in following rows
hjzcdata.cjdqcx.DisableControls;
try
Bookmark := hjzcdata.cjdqcx.Bookmark;
try
hjzcdata.cjdqcx.First;
Row := 2;
while not hjzcdata.cjdqcx.EOF do
begin
RangeE := ExcelApplication1.Range ['A' + IntToStr (Row),
'A' + IntToStr (Row)];
for I := 0 to dbgrid1.Columns.Count-1 do
begin
zdname:=dbgrid1.Columns.Items[i].FieldName;
RangeE.Value := hjzcdata.cjdqcx.Fieldbyname(zdname).value;
RangeE := RangeE.Next;
end;
hjzcdata.cjdqcx.Next;
Inc (Row);
end;
finally
hjzcdata.cjdqcx.Bookmark := Bookmark;
end;
finally
hjzcdata.cjdqcx.EnableControls;
end;
// format the section
RangeE := ExcelApplication1.Range ['A1', 'E' + IntToStr (Row - 1)];
//RangeE.AutoFormat (3, NULL, NULL, NULL, NULL, NULL, NULL);
end
else   //采用常规报表
begin
with lscx do
begin
close;
sql.clear;
sql.add('select 单位名称 from 参数表');
prepared;
open;
bbbt.Caption:=fields[0].Value+'固定资产出借到期信息表';
end;
quickrep1.Prepare;
zys.Caption:='共 '+inttostr(quickrep1.Printer.Pagecount)+' 页';
quickrep1.Preview;
end;
end;

procedure Tw_cjdqcx.QuickRep1Preview(Sender: TObject);
begin
bbbt.Left:=(PageHeaderBand1.Width-bbbt.Width) div 2;
  with Tprevform.Create(Application) do
begin
QRPreview1.QRPrinter := TQRPrinter(Sender);
   Show;
  end
end;

procedure Tw_cjdqcx.Button4Click(Sender: TObject);
var gdzcbm:integer;
begin
if hjzcdata.cjdqcx.IsEmpty then
begin
messagedlg('没有选中任何数据!',mterror,[mbok],0);
exit;
end;
gdzcbm:=hjzcdata.cjdqcx.fields[0].AsInteger;
if not hjzcdata.gdzcxxb.active then
hjzcdata.gdzcxxb.Active:=true;
hjzcdata.gdzcxxb.Locate('固定资产编码',gdzcbm,[lopartialkey]);
hjzcdata.gdzcxxb.Edit;
w_wx.button1.Enabled:=false;
w_wx.DBMemo1.Enabled:=false;
if w_wx.showmodal=mrok then
begin
hjzcdata.gdzcxxb.FieldByName('维修标志').AsBoolean:=true;
hjzcdata.gdzcxxb.Post;
end else
hjzcdata.gdzcxxb.cancel;


end;

end.