ddcb.~pas

应对服装行业的生产成本控制系统

unit ddcb;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Buttons, ComCtrls, Grids, DBGrids, ExtCtrls, DBCtrls,
  DB, ADODB, DBClient;

type
  TFrmddcb = class(TForm)
    Pz: TPanel;
    help: TLabel;
    GroupBox4: TGroupBox;
    DBGrid1: TDBGrid;
    Pd: TPanel;
    bprint: TBitBtn;
    breturn: TBitBtn;
    bprintc: TBitBtn;
    OpenDialog1: TOpenDialog;
    pq: TPanel;
    csqrq: TCheckBox;
    cszrq: TCheckBox;
    szrq: TDateTimePicker;
    sqrq: TDateTimePicker;
    cfc: TCheckBox;
    qfc: TEdit;
    Bfbhz: TBitBtn;
    cfqrq: TCheckBox;
    cfzrq: TCheckBox;
    fzrq: TDateTimePicker;
    fqrq: TDateTimePicker;
    DBGrid2: TDBGrid;
    cgqrq: TCheckBox;
    cgzrq: TCheckBox;
    gzrq: TDateTimePicker;
    gqrq: TDateTimePicker;
    ClientDataSet1: TADOQuery;
    DataSource1: TDataSource;
    procedure bprintClick(Sender: TObject);
    procedure breturnClick(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure FormActivate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure bprintcClick(Sender: TObject);
    procedure BfbhzClick(Sender: TObject);
    procedure DBGrid1KeyPress(Sender: TObject; var Key: Char);
  private
    { Private declarations }
      rzczy,rzsj,rzjsj,rzcsj:string;
  commandstring,commandstringq: string;
    function hz():boolean;
  public
    { Public declarations }
  end;

var
  Frmddcb: TFrmddcb;

implementation

uses unit28, Unit1, Unit16,func, Unit3;

{$R *.dfm}

procedure TFrmddcb.bprintClick(Sender: TObject);
begin
 if DataModule1.clientdataset24.RecordCount>0 then
  begin
  DataModule1.clientdataset24.Edit;
  DataModule1.clientdataset24.Delete;
  hz;
 end;
 end;

procedure TFrmddcb.breturnClick(Sender: TObject);
begin
try
rzcsj:=datetimetostr(now);
form16.WriteTorz(rzczy,rzsj,rzjsj,rzcsj);
DataModule1.ClientDataSet24.IndexFieldNames:='';
if DataModule1.clientdataset24.Active then  DataModule1.ClientDataSet24.Active:=false;
if DataModule1.clientdataset22.Active then  DataModule1.ClientDataSet22.Active:=false;
if clientdataset1.Active then  ClientDataSet1.Active:=false;
except
showmessage('数据库没有打开!');
end;
close;
end;


procedure TFrmddcb.DBGrid1TitleClick(Column: TColumn);
var i : integer;
begin
for i:= 1 to DBGrid1.Columns.Count do
begin
DBGrid1.Columns[i-1].Title.Font.Color := clBlue;
DBGrid1.Columns[i-1].Title.Font.Style := [];
end;
if DataModule1.ClientDataSet24.IndexFieldNames<>(Column.FieldName) then
begin
DataModule1.ClientDataSet24.IndexFieldNames:= Column.FieldName;
Column.Title.Font.Color := clRed;
Column.Title.Font.Style := [fsBold];
end;
end;


procedure TFrmddcb.FormActivate(Sender: TObject);
begin
sqrq.Date:=date-30;
szrq.Date:=now;
fqrq.Date:=date-30;
fzrq.Date:=now;
gqrq.Date:=date-30;
gzrq.Date:=now;
rzjsj:=datetimetostr(now);
rzczy:=form1.StatusBar1.Panels[4].Text;
rzsj:='收付款查询操作';
rzcsj:='';
bprint.Enabled:=false;
dbgrid1.Enabled:=true;
help.Caption:='';
help.Caption:='  请您查询您需要的数据!';
try
 clientDataSet1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+ExtractFileDir(Application.ExeName)+'\wmxt.mdb;Persist Security Info=False';
 clientDataSet1.Active:=true;
 clientDataSet1.Close;
 clientDataSet1.SQL.Clear;
 clientDataSet1.SQL.Add('delete from zhbb');
 clientDataSet1.ExecSQL;
 clientDataSet1.Close;
 clientDataSet1.SQL.Clear;
 clientDataSet1.SQL.Add('select * from zhbb');
 clientDataSet1.Open;
except
showmessage('数据库没有打开!')
end;
end;

procedure TFrmddcb.FormClose(Sender: TObject; var Action: TCloseAction);
begin
try
DataModule1.ClientDataSet24.IndexFieldNames:='';
if DataModule1.clientdataset24.Active then  DataModule1.ClientDataSet24.Active:=false;
if DataModule1.clientdataset22.Active then  DataModule1.ClientDataSet22.Active:=false;
if clientdataset1.Active then ClientDataSet1.Active:=false;
if rzcsj<>'' then exit;
rzcsj:=datetimetostr(now);
form16.WriteTorz(rzczy,rzsj,rzjsj,rzcsj);
except
showmessage('数据库没有打开!');
end;
close;
end;

procedure TFrmddcb.bprintcClick(Sender: TObject);
var
excelname: string;
begin
if not form1.excel then
begin
showmessage('您还未获得管理员的授权!请您与管理员联系获取授权!');
exit;
end;
if DataModule1.ClientDataSet24.RecordCount<=0 then exit;
opendialog1.Filter:='Excel 文件 (*.xls) │*.xls';
if opendialog1.Execute then
excelname:=opendialog1.FileName;
if length(excelname)>0 then writetoexcel(DataModule1.ClientDataSet24,excelname,'收付款信息报表');
exit;
end;

procedure TFrmddcb.BfbhzClick(Sender: TObject);
var
commandstring0,commandstring1,commandstring2,commandstring3,relation,cx:string;
begin
 relation:=' like ''';
    cx:='%';
  try
commandstring0:='';
commandstring1:='';
commandstring2:='';
commandstring3:='';
if cgqrq.Checked then
 begin
relation:=' >= ''';
    cx:='';
commandstring0:=commandstring0+' and (dbo.glfyb.rq'+ relation+FormatDateTime('yyyy-mm-dd',gqrq.Date)+cx+''')';
end;
if cgzrq.Checked then
 begin
relation:=' <= ''';
    cx:='';
commandstring0:=commandstring0+' and (dbo.glfyb.rq'+ relation+FormatDateTime('yyyy-mm-dd',gzrq.Date+1)+cx+''')';
end;

if csqrq.Checked then
 begin
relation:=' >= ''';
    cx:='';
commandstring1:=commandstring1+' and (dbo.skb.rq'+ relation+FormatDateTime('yyyy-mm-dd',sqrq.Date)+cx+''')';
end;
if cszrq.Checked then
 begin
relation:=' <= ''';
    cx:='';
commandstring1:=commandstring1+' and (dbo.skb.rq'+ relation+FormatDateTime('yyyy-mm-dd',szrq.Date+1)+cx+''')';
end;
if cfqrq.Checked then
 begin
relation:=' >= ''';
    cx:='';
commandstring2:=commandstring2+' and (dbo.fkb.rq'+ relation+FormatDateTime('yyyy-mm-dd',fqrq.Date)+cx+''')';
end;
if cfzrq.Checked then
begin
relation:=' <= ''';
    cx:='';
commandstring2:=commandstring2+' and (dbo.fkb.rq'+ relation+FormatDateTime('yyyy-mm-dd',fzrq.Date+1)+cx+''')';
end;
if (trim(qfc.Text)<>'') and (cfc.Checked) then
   begin
    relation:=' like ''';
    cx:='%';
commandstring3:=commandstring3+' where (dbo.sfklxb.sfklx'+relation+cx+trim(qfc.Text)+cx+''')';
end;
commandstring:='SELECT sfklx,(SELECT SUM(dbo.skb.je) je';
commandstring:=commandstring+' FROM dbo.skb WHERE dbo.skb.sklx = dbo.sfklxb.sfklx '+commandstring1;
commandstring:=commandstring+') AS je,(SELECT SUM(dbo.fkb.je) je1';
commandstring:=commandstring+' FROM dbo.fkb WHERE dbo.fkb.sklx = dbo.sfklxb.sfklx '+commandstring2;
commandstring:=commandstring+') AS je1,(SELECT SUM(dbo.glfyb.je) je2';
commandstring:=commandstring+' FROM dbo.glfyb WHERE dbo.glfyb.sklx = dbo.sfklxb.sfklx '+commandstring0;
commandstring:=commandstring+') AS je2, round(ISNULL((SELECT SUM(dbo.skb.je) je';
commandstring:=commandstring+' FROM dbo.skb WHERE dbo.skb.sklx = dbo.sfklxb.sfklx '+commandstring1;
commandstring:=commandstring+'), 0) - ISNULL((SELECT SUM(dbo.fkb.je) je1';
commandstring:=commandstring+' FROM dbo.fkb WHERE dbo.fkb.sklx = dbo.sfklxb.sfklx '+commandstring2;
commandstring:=commandstring+'), 0),2) AS je3, round(ISNULL((SELECT SUM(dbo.skb.je) je';
commandstring:=commandstring+' FROM dbo.skb WHERE dbo.skb.sklx = dbo.sfklxb.sfklx '+commandstring1;
commandstring:=commandstring+'), 0) - ISNULL((SELECT SUM(dbo.fkb.je) je1';
commandstring:=commandstring+' FROM dbo.fkb WHERE dbo.fkb.sklx = dbo.sfklxb.sfklx '+commandstring2;
commandstring:=commandstring+'), 0) - ISNULL((SELECT SUM(dbo.glfyb.je) je2';
commandstring:=commandstring+' FROM dbo.glfyb WHERE dbo.glfyb.sklx = dbo.sfklxb.sfklx '+commandstring0;
commandstring:=commandstring+'), 0),2) AS je4 FROM dbo.sfklxb '+commandstring3;
commandstringq:=commandstring;
DataModule1.ClientDataSet24.DisableControls;
DataModule1.ClientDataSet24.Close;
DataModule1.ClientDataSet24.CommandText:=commandstring;
DataModule1.ClientDataSet24.Open;
DataModule1.ClientDataSet24.EnableControls;
bprint.Enabled:=true;
hz;
help.Caption:='   查询完毕!';
except
DataModule1.clientdataset24.EnableControls;
showmessage('数据库没有打开!');
end;
end;

procedure TFrmddcb.DBGrid1KeyPress(Sender: TObject; var Key: Char);
var
  je,je1,je2,je3,je4: String;
  i: integer;
begin
je:='0';
je1:='0';
je2:='0';
je3:='0';
je4:='0';
if key=#13 then
begin
   for i := 0 to (DBGrid1.SelectedRows.Count - 1) do
  begin
    DBGrid1.DataSource.DataSet.GotoBookmark(pointer(DBGrid1.SelectedRows.Items[i]));
 if DBGrid1.DataSource.DataSet.FieldByName('je').AsString<>'' then je:= formatfloat('0.00',strtofloat(je)+DBGrid1.DataSource.DataSet.FieldByName('je').AsFloat);
 if DBGrid1.DataSource.DataSet.FieldByName('je1').AsString<>'' then je1:= formatfloat('0.00',strtofloat(je1)+DBGrid1.DataSource.DataSet.FieldByName('je1').AsFloat);
 if DBGrid1.DataSource.DataSet.FieldByName('je2').AsString<>'' then je2:=formatfloat('0.00',strtofloat(je2)+DBGrid1.DataSource.DataSet.FieldByName('je2').AsFloat);
 if DBGrid1.DataSource.DataSet.FieldByName('je3').AsString<>'' then je2:=formatfloat('0.00',strtofloat(je3)+DBGrid1.DataSource.DataSet.FieldByName('je3').AsFloat);
 if DBGrid1.DataSource.DataSet.FieldByName('je4').AsString<>'' then je2:=formatfloat('0.00',strtofloat(je4)+DBGrid1.DataSource.DataSet.FieldByName('je4').AsFloat);
  end;
help.Caption:='收款'+je+'付款'+je1+'管理'+je2+'余额'+je3+'纯余额'+je4;
end;
end;

function TFrmddcb.hz:boolean;
var
  je,je1,je2,je3,je4: String;
  i: integer;
begin
if not DataModule1.ClientDataSet24.RecordCount>0 then exit;
je:='0';
je1:='0';
je2:='0';
je3:='0';
je4:='0';
   DataModule1.ClientDataSet24.DisableControls;
   DataModule1.ClientDataSet24.First;
   for i := 1 to DataModule1.ClientDataSet24.RecordCount do
  begin
 if DataModule1.ClientDataSet24.FieldByName('je').AsString<>'' then je:= formatfloat('0.00',strtofloat(je)+DataModule1.ClientDataSet24.FieldByName('je').AsFloat);
 if DataModule1.ClientDataSet24.FieldByName('je1').AsString<>'' then je1:= formatfloat('0.00',strtofloat(je1)+DataModule1.ClientDataSet24.FieldByName('je1').AsFloat);
 if DataModule1.ClientDataSet24.FieldByName('je2').AsString<>'' then je2:=formatfloat('0.00',strtofloat(je2)+DataModule1.ClientDataSet24.FieldByName('je2').AsFloat);
 if DataModule1.ClientDataSet24.FieldByName('je3').AsString<>'' then je2:=formatfloat('0.00',strtofloat(je3)+DataModule1.ClientDataSet24.FieldByName('je3').AsFloat);
 if DataModule1.ClientDataSet24.FieldByName('je4').AsString<>'' then je2:=formatfloat('0.00',strtofloat(je4)+DataModule1.ClientDataSet24.FieldByName('je4').AsFloat);
 DataModule1.ClientDataSet24.Next;
  end;
 DataModule1.ClientDataSet24.First;
 DataModule1.ClientDataSet24.EnableControls;
 try
 clientDataSet1.Close;
 clientDataSet1.SQL.Clear;
 clientDataSet1.SQL.Add('delete from zhbb');
 clientDataSet1.ExecSQL;
 clientDataSet1.Close;
 clientDataSet1.SQL.Clear;
 clientDataSet1.SQL.Add('select * from zhbb');
 clientDataSet1.Open;
 clientDataSet1.Append;
  ClientDataSet1.FieldByName('je').AsString:=je;
  ClientDataSet1.FieldByName('je1').AsString:=je1;
  ClientDataSet1.FieldByName('je2').AsString:=je2;
  ClientDataSet1.FieldByName('je3').AsString:=je3;
  ClientDataSet1.FieldByName('je4').AsString:=je4;
  ClientDataSet1.Post;
except
showmessage('数据汇总失败!');
end;
end;
end.