unit ddsdcb;

interface

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

type
  TFrmddsdcb = class(TForm)
    Pz: TPanel;
    help: TLabel;
    GroupBox4: TGroupBox;
    DBGrid1: TDBGrid;
    Pd: TPanel;
    bdelete: TBitBtn;
    breturn: TBitBtn;
    bprintc: TBitBtn;
    OpenDialog1: TOpenDialog;
    Bddcbhz: TBitBtn;
    pq: TPanel;
    cllqq: TCheckBox;
    cylqq: TCheckBox;
    ylqq: TDateTimePicker;
    llqq: TDateTimePicker;
    Cclqq: TCheckBox;
    Cfyqq: TCheckBox;
    fyqq: TDateTimePicker;
    clqq: TDateTimePicker;
    Cllzq: TCheckBox;
    Cylzq: TCheckBox;
    ylzq: TDateTimePicker;
    llzq: TDateTimePicker;
    Cclzq: TCheckBox;
    Cfyzq: TCheckBox;
    fyzq: TDateTimePicker;
    clzq: TDateTimePicker;
    procedure bdeleteClick(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 BddcbhzClick(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
  Frmddsdcb: TFrmddsdcb;

implementation

uses unit28, Unit1, Unit16,func;

{$R *.dfm}

procedure TFrmddsdcb.bdeleteClick(Sender: TObject);
begin
 if not DataModule1.clientdataset28.Active then exit;
 if DataModule1.clientdataset28.RecordCount>0 then
  begin
  DataModule1.clientdataset28.Edit;
  DataModule1.clientdataset28.Delete;
  hz;
 end;
 end;

procedure TFrmddsdcb.breturnClick(Sender: TObject);
begin
try
rzcsj:=datetimetostr(now);
form16.WriteTorz(rzczy,rzsj,rzjsj,rzcsj);
DataModule1.ClientDataSet28.IndexFieldNames:='';
if DataModule1.clientdataset28.Active then  DataModule1.ClientDataSet28.Active:=false;
except
showmessage('數據庫沒有打開!');
end;
close;
end;


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


procedure TFrmddsdcb.FormActivate(Sender: TObject);
begin
rzjsj:=datetimetostr(now);
rzczy:=form1.StatusBar1.Panels[4].Text;
rzsj:='訂單成本查詢';
rzcsj:='';
bdelete.Enabled:=false;
dbgrid1.Enabled:=true;
help.Caption:='';
help.Caption:='  請您查詢您需要的數據!';
end;

procedure TFrmddsdcb.FormClose(Sender: TObject; var Action: TCloseAction);
begin
try
DataModule1.ClientDataSet28.IndexFieldNames:='';
if DataModule1.clientdataset28.Active then  DataModule1.ClientDataSet28.Active:=false;
if rzcsj<>'' then exit;
rzcsj:=datetimetostr(now);
form16.WriteTorz(rzczy,rzsj,rzjsj,rzcsj);
except
showmessage('數據庫沒有打開!');
end;
close;
end;

procedure TFrmddsdcb.bprintcClick(Sender: TObject);
var
excelname: string;
begin
if not form1.excel then
begin
showmessage('您還未獲得管理員的授權!請您與管理員聯系獲取授權!');
exit;
end;
if DataModule1.ClientDataSet28.RecordCount<=0 then exit;
opendialog1.Filter:='Excel 文件 (*.xls) │*.xls';
if opendialog1.Execute then
excelname:=opendialog1.FileName;
if length(excelname)>0 then writetoexcel(DataModule1.ClientDataSet28,excelname,'訂單成本信息報表');
exit;
end;

procedure TFrmddsdcb.BddcbhzClick(Sender: TObject);
begin
commandstring:='SELECT ddh,je,(SELECT SUM(dbo.llb.je) je';
commandstring:=commandstring+' FROM dbo.llb WHERE dbo.llb.ddh = dbo.ddb.ddh ';
commandstring:=commandstring+') AS llje,(SELECT SUM(dbo.ylzj.je) je';
commandstring:=commandstring+' FROM dbo.ylzj WHERE dbo.ylzj.ddh = dbo.ddb.ddh ';
commandstring:=commandstring+') AS ylje,(SELECT SUM(dbo.cllrb.cz) cz';
commandstring:=commandstring+' FROM dbo.cllrb WHERE dbo.cllrb.ddh = dbo.ddb.ddh ';
commandstring:=commandstring+') AS clje,(SELECT SUM(dbo.scfyb.je) je';
commandstring:=commandstring+' FROM dbo.scfyb WHERE dbo.scfyb.ddh = dbo.ddb.ddh ';
commandstring:=commandstring+') AS fyje, round(ISNULL((SELECT SUM(dbo.llb.je) je';
commandstring:=commandstring+' FROM dbo.llb WHERE dbo.llb.ddh = dbo.ddb.ddh ';
commandstring:=commandstring+'), 0) + ISNULL((SELECT SUM(dbo.ylzj.je) je ';
commandstring:=commandstring+' FROM dbo.ylzj WHERE dbo.ylzj.ddh = dbo.ddb.ddh ';
commandstring:=commandstring+'), 0) + ISNULL((SELECT SUM(dbo.cllrb.cz) cz ';
commandstring:=commandstring+' FROM dbo.cllrb WHERE dbo.cllrb.ddh = dbo.ddb.ddh ';
commandstring:=commandstring+'), 0) + ISNULL((SELECT SUM(dbo.scfyb.je) je ';
commandstring:=commandstring+' FROM dbo.scfyb WHERE dbo.scfyb.ddh = dbo.ddb.ddh ';
commandstring:=commandstring+'), 0),2) AS cbje FROM dbo.ddb order by ddh ';
commandstringq:=commandstring;
try
DataModule1.ClientDataSet28.DisableControls;
DataModule1.ClientDataSet28.Close;
DataModule1.ClientDataSet28.CommandText:=commandstring;
DataModule1.ClientDataSet28.Open;
DataModule1.ClientDataSet28.EnableControls;
bdelete.Enabled:=true;
hz;
except
DataModule1.clientdataset28.EnableControls;
showmessage('數據庫沒有打開!');
end;
end;

procedure TFrmddsdcb.DBGrid1KeyPress(Sender: TObject; var Key: Char);
var
  je,llje,ylje,clje,fyje,cbje: String;
  i: integer;
begin
je:='0';
llje:='0';
ylje:='0';
clje:='0';
fyje:='0';
cbje:='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('llje').AsString<>'' then llje:= formatfloat('0.00',strtofloat(llje)+DBGrid1.DataSource.DataSet.FieldByName('llje').AsFloat);
 if DBGrid1.DataSource.DataSet.FieldByName('ylje').AsString<>'' then ylje:=formatfloat('0.00',strtofloat(ylje)+DBGrid1.DataSource.DataSet.FieldByName('ylje').AsFloat);
 if DBGrid1.DataSource.DataSet.FieldByName('clje').AsString<>'' then clje:=formatfloat('0.00',strtofloat(clje)+DBGrid1.DataSource.DataSet.FieldByName('clje').AsFloat);
 if DBGrid1.DataSource.DataSet.FieldByName('fyje').AsString<>'' then fyje:=formatfloat('0.00',strtofloat(fyje)+DBGrid1.DataSource.DataSet.FieldByName('fyje').AsFloat);
 if DBGrid1.DataSource.DataSet.FieldByName('cbje').AsString<>'' then cbje:=formatfloat('0.00',strtofloat(cbje)+DBGrid1.DataSource.DataSet.FieldByName('cbje').AsFloat);
  end;
help.Caption:='訂單'+je+'領料'+llje+'余料追加'+ylje+'產量'+clje+'費用'+fyje+'成本'+cbje;
end;
end;

function TFrmddsdcb.hz:boolean;
var
  je,llje,ylje,clje,fyje,cbje: String;
  i: integer;
begin
je:='0';
llje:='0';
ylje:='0';
clje:='0';
fyje:='0';
cbje:='0';
if not DataModule1.ClientDataSet28.RecordCount>0 then exit;
   DataModule1.ClientDataSet28.DisableControls;
   DataModule1.ClientDataSet28.First;
   for i := 1 to DataModule1.ClientDataSet28.RecordCount do
  begin
 if DataModule1.ClientDataSet28.FieldByName('je').AsString<>'' then je:= formatfloat('0.00',strtofloat(je)+DataModule1.ClientDataSet28.FieldByName('je').AsFloat);
 if DataModule1.ClientDataSet28.FieldByName('llje').AsString<>'' then llje:= formatfloat('0.00',strtofloat(llje)+DataModule1.ClientDataSet28.FieldByName('llje').AsFloat);
 if DataModule1.ClientDataSet28.FieldByName('ylje').AsString<>'' then ylje:=formatfloat('0.00',strtofloat(ylje)+DataModule1.ClientDataSet28.FieldByName('ylje').AsFloat);
 if DataModule1.ClientDataSet28.FieldByName('clje').AsString<>'' then clje:=formatfloat('0.00',strtofloat(clje)+DataModule1.ClientDataSet28.FieldByName('clje').AsFloat);
 if DataModule1.ClientDataSet28.FieldByName('fyje').AsString<>'' then fyje:=formatfloat('0.00',strtofloat(fyje)+DataModule1.ClientDataSet28.FieldByName('fyje').AsFloat);
 if DataModule1.ClientDataSet28.FieldByName('cbje').AsString<>'' then cbje:=formatfloat('0.00',strtofloat(cbje)+DataModule1.ClientDataSet28.FieldByName('cbje').AsFloat);
  end;
 help.Caption:='訂單'+je+'領料'+llje+'余料追加'+ylje+'產量'+clje+'費用'+fyje+'成本'+cbje;
 DataModule1.ClientDataSet28.First;
 DataModule1.ClientDataSet28.EnableControls;
end;
end.