ye.pas

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

unit ye;

interface

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

type
  TFrmye = class(TForm)
    Pz: TPanel;
    help: TLabel;
    GroupBox4: TGroupBox;
    DBGrid1: TDBGrid;
    Pd: TPanel;
    bprint: TBitBtn;
    breturn: TBitBtn;
    bprintc: TBitBtn;
    OpenDialog1: TOpenDialog;
    pq: TPanel;
    cks: TCheckBox;
    qks: TEdit;
    Bkshz: TBitBtn;
    ClientDataSet1: TADOQuery;
    DataSource1: TDataSource;
    DBGrid2: TDBGrid;
    ClientDataSet2: TClientDataSet;
    DataSource2: TDataSource;
    ClientDataSet1fb: TWideStringField;
    ClientDataSet1ks: TWideStringField;
    ClientDataSet1khmc: TWideStringField;
    ClientDataSet1je: TWideStringField;
    ClientDataSet1zhrmb: TWideStringField;
    ClientDataSet1kcrmb: TWideStringField;
    ClientDataSet1ssrmb: TWideStringField;
    ClientDataSet1je1: TWideStringField;
    ClientDataSet1zhrmb1: TWideStringField;
    ClientDataSet1kcrmb1: TWideStringField;
    ClientDataSet1ssrmb1: TWideStringField;
    ClientDataSet1je2: TWideStringField;
    ClientDataSet1je3: TWideStringField;
    ClientDataSet1je4: TWideStringField;
    ClientDataSet1wsje: TWideStringField;
    ClientDataSet1dksj: TWideStringField;
    ClientDataSet1wsje1: TWideStringField;
    ClientDataSet1dksj1: TWideStringField;
    Bfbhz: TBitBtn;
    cfb: TCheckBox;
    qfb: TEdit;
    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 BkshzClick(Sender: TObject);
    procedure DBGrid1KeyPress(Sender: TObject; var Key: Char);
    procedure BfbhzClick(Sender: TObject);
  private
    { Private declarations }
      rzczy,rzsj,rzjsj,rzcsj:string;
  commandstring,commandstringq: string;
    function js():boolean;
    function hz():boolean;
  public
    function checkye(ks:string):string;
    { Public declarations }
  end;

var
  Frmye: TFrmye;

implementation

uses unit28, Unit1, Unit16,func, Unit3;

{$R *.dfm}

procedure TFrmye.bprintClick(Sender: TObject);
begin
 if ClientDataSet2.RecordCount>0 then
  begin
  ClientDataSet2.Edit;
  ClientDataSet2.Delete;
  hz;
 end;
 end;

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


procedure TFrmye.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 ClientDataSet2.IndexFieldNames<>(Column.FieldName) then
begin
ClientDataSet2.IndexFieldNames:= Column.FieldName;
Column.Title.Font.Color := clRed;
Column.Title.Font.Style := [fsBold];
end;
end;


procedure TFrmye.FormActivate(Sender: TObject);
begin
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 TFrmye.FormClose(Sender: TObject; var Action: TCloseAction);
begin
try
ClientDataSet2.IndexFieldNames:='';
if ClientDataSet2.Active then  ClientDataSet2.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 TFrmye.bprintcClick(Sender: TObject);
var
excelname: string;
begin
if not form1.excel then
begin
showmessage('您还未获得管理员的授权!请您与管理员联系获取授权!');
exit;
end;
if ClientDataSet2.RecordCount<=0 then exit;
opendialog1.Filter:='Excel 文件 (*.xls) │*.xls';
if opendialog1.Execute then
excelname:=opendialog1.FileName;
if length(excelname)>0 then writetoexcel(ClientDataSet2,excelname,'收付款信息报表');
exit;
end;

procedure TFrmye.BkshzClick(Sender: TObject);
var
commandstring0,relation,cx,wb:string;
begin
 relation:=' like ''';
    cx:='%';

wb:='''外币''';
commandstring0:='';
if (trim(qks.Text)<>'') and (cks.Checked) then
   begin
    relation:=' like ''';
    cx:='%';
commandstring0:=' where (dbo.khlxb.ks'+relation+cx+trim(qks.Text)+cx+''')';
end;
commandstring:='SELECT dbo.khlxb.ks,sum(ye) ye,sum(cye) cye, ISNULL';
commandstring:=commandstring+'((SELECT SUM(dbo.skb.zhrmb) zhrmb ';
commandstring:=commandstring+'FROM dbo.skb ';
commandstring:=commandstring+'WHERE dbo.skb.ks = dbo.khlxb.ks), 0) AS sk, ISNULL';
commandstring:=commandstring+'((SELECT SUM(dbo.fkb.zhrmb) zhrmb1 ';
commandstring:=commandstring+'FROM dbo.fkb ';
commandstring:=commandstring+'WHERE dbo.fkb.ks = dbo.khlxb.ks), 0) AS fk, ISNULL';
commandstring:=commandstring+'((SELECT SUM(dbo.glfyb.je) je ';
commandstring:=commandstring+'FROM dbo.glfyb ';
commandstring:=commandstring+'WHERE dbo.glfyb.ks = dbo.khlxb.ks), 0) AS glfy, ISNULL(MIN(tc.jszc), 0) AS sj, ';
commandstring:=commandstring+'ISNULL(sx.sxf, 0) AS sxf ';
commandstring:=commandstring+'FROM dbo.khlxb LEFT OUTER JOIN ';
commandstring:=commandstring+'(SELECT dbo.skb.ks, ISNULL';
commandstring:=commandstring+'((SELECT SUM(dbo.fkkpb.dksj) dksj ';
commandstring:=commandstring+'FROM dbo.fkkpb ';
commandstring:=commandstring+'WHERE dbo.fkkpb.ks = dbo.skb.ks), 0) ';
commandstring:=commandstring+'- ISNULL(SUM(dbo.skb.zhrmb * (0.17 - dbo.hbhlb.tsl)), 0) - ISNULL';
commandstring:=commandstring+'((SELECT SUM(dbo.skkpb.dksj) dksj1 ';
commandstring:=commandstring+'FROM dbo.skkpb ';
commandstring:=commandstring+'WHERE dbo.skkpb.ks = dbo.skb.ks), 0) AS jszc ';
commandstring:=commandstring+'FROM dbo.skb INNER JOIN ';
commandstring:=commandstring+'dbo.hbhlb ON dbo.skb.hbmc = dbo.hbhlb.hbmc ';
commandstring:=commandstring+'WHERE (dbo.hbhlb.bb = '+wb;
commandstring:=commandstring+') ';
commandstring:=commandstring+'GROUP BY dbo.skb.ks ';
commandstring:=commandstring+'UNION ';
commandstring:=commandstring+'SELECT dbo.skb.ks, ISNULL(SUM(dbo.skb.zhrmb * dbo.hbhlb.tsl), 0) ';
commandstring:=commandstring+'AS jszc1 ';
commandstring:=commandstring+'FROM dbo.skb INNER JOIN ';
commandstring:=commandstring+'dbo.hbhlb ON dbo.skb.hbmc = dbo.hbhlb.hbmc ';
commandstring:=commandstring+'WHERE (dbo.hbhlb.bb = '+wb;
commandstring:=commandstring+') ';
commandstring:=commandstring+'GROUP BY dbo.skb.ks) tc ON dbo.khlxb.ks = tc.ks LEFT OUTER JOIN ';
commandstring:=commandstring+'(SELECT dbo.skb.ks, SUM(dbo.skb.zhrmb * dbo.hbhlb.fhbl) AS sxf ';
commandstring:=commandstring+'FROM dbo.skb INNER JOIN ';
commandstring:=commandstring+'dbo.hbhlb ON dbo.skb.hbmc = dbo.hbhlb.hbmc ';
commandstring:=commandstring+'GROUP BY dbo.skb.ks) sx ON dbo.khlxb.ks = sx.ks ';
commandstring:=commandstring+commandstring0;
commandstring:=commandstring+' GROUP BY dbo.khlxb.ks, sx.sxf';
commandstringq:=commandstring;
dbgrid1.DataSource:=DataSource2;
  try
ClientDataSet2.DisableControls;
ClientDataSet2.Close;
ClientDataSet2.CommandText:=commandstring;
ClientDataSet2.Open;
ClientDataSet2.EnableControls;
bprint.Enabled:=true;
js;
hz;
except
ClientDataSet2.EnableControls;
showmessage('数据库没有打开!');
end;