fdeptgx.pas

实用的毛织生产管理系统

unit fdeptgx;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Spin, ExtCtrls, Grids, DBGrids, DB, DBTables, constb,
  Buttons,comobj, Excel2000, ActnList;

type
  Tfdeptgxfm = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    SpinEdit1: TSpinEdit;
    SpinEdit2: TSpinEdit;
    Edit1: TEdit;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Panel1: TPanel;
    GroupBox3: TGroupBox;
    DBGrid2: TDBGrid;
    pnoqy: TQuery;
    saveqy: TQuery;
    schqy: TQuery;
    finishqy: TQuery;
    pnodb: TDataSource;
    finishdb: TDataSource;
    finishrptqy: TQuery;
    BitBtn1: TBitBtn;
    sch2: TQuery;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    BitBtn2: TBitBtn;
    Memo1: TMemo;
    ActionList1: TActionList;
    Button4: TButton;
    CheckBox3: TCheckBox;
    CheckBox4: TCheckBox;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
 //   procedure BitBtn3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);

  private
    years, months: integer;
    { Private declarations }

  public
    { Public declarations }
  end;

var
  fdeptgxfm: Tfdeptgxfm;
  A:array[0..50] of string;
  b:array[0..50] of string;

implementation

uses finishpnomxrpt;

{$R *.dfm}

procedure Tfdeptgxfm.Button1Click(Sender: TObject);
var
  pno, dept, scgx, price, memo: string;

begin

  if (years <> spinedit1.Value) or (months <> spinedit2.Value) then
  begin
    years := spinedit1.Value;
    months := spinedit2.Value;
    with saveqy do
    begin
      sql.Clear;
      sql.add('delete from finishpno');
      execsql;
      sql.Clear;
      sql.Add('update scinmx set scgx='''' where scgx is null ');
      execsql;
      sql.clear;
      sql.add('insert into finishpno(pno,dept,scgx,scprice,totalnum,totalsum)');
      sql.Add(' select b.pno,a.dept,b.scgx,b.inprice,sum(b.innum),sum(b.totalin)');
      sql.add(' from  scintb a ,scinmx b,scpno c ');
      sql.add(' where a.idno=b.idno and b.pno=c.pno ');
      if checkbox2.Checked then
      sql.add(' and b.sg=''时工''');
      sql.add('and year(c.finishdate)=:years and month(c.finishdate)=:months');
      sql.add(' and finishflg =:finishflg');
      sql.Add(' group by b.pno,a.dept,b.scgx,b.inprice ');
      parambyname('years').asinteger := spinedit1.Value;
      parambyname('months').asinteger := spinedit2.Value;
      parambyname('finishflg').asboolean := true;
      execsql;

      sql.clear;
      sql.add('update finishpno set');
      sql.add(' monthnum=(select sum(a.innum) from scinmx a,scintb b where a.idno=b.idno and  a.pno=finishpno.pno  ');
      sql.add(' and b.dept=finishpno.dept and ((a.scgx=finishpno.scgx)or ((a.scgx is null ) and (finishpno.scgx is null))) and b.years=:years and b.months=:months');
      sql.add(' and (a.inprice=finishpno.scprice) group by  a.pno,b.dept,a.scgx,a.inprice )');
      sql.add(',monthsum=(select sum(a.totalin) from scinmx a,scintb b where a.idno=b.idno and  a.pno=finishpno.pno  ');
      sql.add(' and b.dept=finishpno.dept  and ((a.scgx=finishpno.scgx)or ((a.scgx is null ) and (finishpno.scgx is null))) and b.years=:years and b.months=:months');
      sql.add('  and (a.inprice=finishpno.scprice) group by  a.pno,b.dept,a.scgx,a.inprice )');
      parambyname('years').asinteger := spinedit1.Value;
      parambyname('months').asinteger := spinedit2.Value;
      execsql;

      sql.clear;
      sql.add('update finishpno set');
      sql.add(' work_again_num=(select sum(a.innum) from scinmx a,scintb b where a.idno=b.idno and  a.pno=finishpno.pno  ');
      sql.add(' and b.dept=finishpno.dept and ((a.scgx=finishpno.scgx)or ((a.scgx is null ) and (finishpno.scgx is null))) ');//and b.years=:years and b.months=:months');
      sql.add(' and (a.inprice=finishpno.scprice) and a.work_again=:work_again group by  a.pno,b.dept,a.scgx,a.inprice )');
      sql.add(',work_again_sum=(select sum(a.totalin) from scinmx a,scintb b where a.idno=b.idno and  a.pno=finishpno.pno  ');
      sql.add(' and b.dept=finishpno.dept  and ((a.scgx=finishpno.scgx)or ((a.scgx is null ) and (finishpno.scgx is null))) ');//and b.years=:years and b.months=:months');
      sql.add('  and (a.inprice=finishpno.scprice) and a.work_again=:work_again group by  a.pno,b.dept,a.scgx,a.inprice )');
    //  parambyname('years').asinteger := spinedit1.Value;
   //   parambyname('months').asinteger := spinedit2.Value;
      parambyname('work_again').asboolean:=true;
      execsql;

    end;
    schqy.SQL.Clear;
    schqy.SQL.Add('select b.years,b.months,a.pno,b.dept,a.scgx,a.inprice from scinmx a ,scintb b ,finishpno c ');
    schqy.SQL.Add('where a.idno=b.idno and  a.pno=c.pno  ');
    schqy.sql.add(' and b.dept=c.dept and a.inprice=c.scprice  ');
    schqy.sql.add(' group by a.pno,b.dept,a.scgx,a.inprice,b.years,b.months ');
    schqy.sql.add(' order  by  a.pno,b.dept,a.scgx,a.inprice,b.years,b.months');
    // schqy.SQL.SaveToFile('c:\finishpno.txt');
    schqy.Open;
    pno := schqy.fieldbyname('pno').AsString;
    dept := schqy.fieldbyname('dept').AsString;
    scgx := schqy.fieldbyname('scgx').asstring;
    price := schqy.fieldbyname('inprice').asstring;
    memo := '累计月份(';

    while not schqy.Eof do
    begin
      if (pno = schqy.fieldbyname('pno').AsString) and (dept =
        schqy.fieldbyname('dept').AsString)
        and (scgx = schqy.fieldbyname('scgx').value) and (price = schqy.fieldbyname('inprice').value) then
        memo := memo + copy(schqy.fieldbyname('years').AsString, 3, 2) + '年' +
          schqy.fieldbyname('months').AsString + '月,'
      else
      begin
        memo := copy(memo, 0, length(memo) - 1) + ')';
        saveqy.SQL.clear;
        saveqy.SQL.Add('update  finishpno set mem=:memo');
        saveqy.SQL.Add(' where pno=:pno and dept=:dept and scprice=:scprice');
        saveqy.parambyname('pno').asstring := pno;
        saveqy.parambyname('dept').asstring := dept;
        saveqy.parambyname('scprice').asstring := price;
            saveqy.SQL.Add('and scgx=:scgx');
          saveqy.parambyname('scgx').asstring := scgx;
             saveqy.parambyname('memo').asstring := memo;
        // saveqy.SQL.SaveToFile('c:\finishpno.txt');
        saveqy.ExecSQL;
        pno := schqy.fieldbyname('pno').AsString;
        dept := schqy.fieldbyname('dept').AsString;
        scgx := schqy.fieldbyname('scgx').asstring;
        price := schqy.fieldbyname('inprice').asstring;
        memo := '累计月份(';
        memo := memo + copy(schqy.fieldbyname('years').AsString, 3, 2) + '年' +
          schqy.fieldbyname('months').AsString + '月,'
      end;
      schqy.Next;
    end;
    memo := copy(memo, 0, length(memo) - 1) + ')';
    saveqy.SQL.clear;
    saveqy.SQL.Add('update  finishpno set mem=:memo');
    saveqy.SQL.Add(' where pno=:pno and dept=:dept and scgx=:scgx and scprice=:scprice');
    saveqy.parambyname('pno').asstring := pno;
    saveqy.parambyname('dept').asstring := dept;
    saveqy.parambyname('scgx').asstring := scgx;
    saveqy.parambyname('memo').asstring := memo;
    saveqy.parambyname('scprice').asstring := price;
    saveqy.ExecSQL;
  end;
  with finishqy do
  begin
    sql.clear;
    sql.add('select a. * ,c.pnonum,(a.totalsum*12/a.totalnum)  totalprice,case  when (a.work_again_num/a.totalnum)*100>0 then str((a.work_again_num/a.totalnum)*100,4,2)+''%'' end ');
    sql.add( '  work_again_per  from finishpno a  inner join scdepts  b on ');
    sql.add(' a.dept=b.dept  ,scpno c ');
    sql.add('where  c.pno=a.pno ');
    if CheckBox1.Checked then
    sql.add(' and work_again_num>0');
    if edit1.Text <> '' then
    begin
      sql.add(' and c.pno=:pno');
      parambyname('pno').asstring := edit1.Text;
    end;
    sql.add('order by a.pno ,b.deptcode');
    //sql.savetofile('c:\ss.txt');
    open;
    button2.Enabled := bool(recordcount);
  end;
end;

procedure Tfdeptgxfm.FormCreate(Sender: TObject);
var
  years, months, days: word;
begin
  decodedate(date, years, months, days);
  spinedit1.Value := years;
  spinedit2.Value := months;
end;

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

procedure Tfdeptgxfm.FormDestroy(Sender: TObject);
begin
  fdeptgxfm := nil;
end;

procedure Tfdeptgxfm.Button3Click(Sender: TObject);
begin
  close;
end;

procedure Tfdeptgxfm.Button2Click(Sender: TObject);
begin
  with pnoqy do
  begin
    sql.clear;
    sql.add('select a.* ,case when round(c.work_again_sum*12/a.zxnum,2)>0 then round(c.totalsum*12/a.zxnum,2) end per_price ');
    sql.add(',case when round(c.work_again_sum*12/a.zxnum,2)>0 then round(c.work_again_sum*12/a.zxnum,2) end per_again_price ');
    sql.add(' from scpno  a   left  join (select pno,sum(totalsum) totalsum ,sum(work_again_sum) work_again_sum from finishpno group by pno ) c on a.pno=c.pno  ');
    sql.add(' where a.pno in (select b.pno from finishpno b where  a.pno=b.pno');
    if CheckBox1.checked then
    sql.add('and b.work_again_num>0'); 
    sql.add('group by b.pno)');
    if edit1.Text <> '' then
    begin
      sql.add(' and a.pno=:pno');
      parambyname('pno').asstring := edit1.Text;
    end;
    sql.add('order by a.pno');
    //sql.SaveToFile('c:\ss.txt');
    open;
    button2.Enabled := bool(recordcount);
  end;
  with finishrptqy do
  begin
    sql.clear;
    sql.add('select a. * ,c.pnonum,(a.totalsum*12/a.totalnum)  totalprice,case  when (a.work_again_num/a.totalnum)*100>0 then str((a.work_again_num/a.totalnum)*100,4,2)+''%'' end ');
    sql.add( '  work_again_per  from finishpno a  inner join scdepts  b on ');
    sql.add(' a.dept=b.dept  ,scpno c ');
    sql.add('where  c.pno=a.pno ');
    if CheckBox1.Checked then
    sql.add(' and work_again_num>0');
    if edit1.Text <> '' then
    begin
      sql.add(' and c.pno=:pno');
      parambyname('pno').asstring := edit1.Text;
    end;
    sql.add('order by a.pno ,b.deptcode');
    //sql.savetofile('c:\ss.txt');
    open;
    button2.Enabled := bool(recordcount);
  end;
  if not assigned(finishpnomxrptfm) then
    finishpnomxrptfm := tfinishpnomxrptfm.create(application);
  with finishpnomxrptfm do
  begin
    quickrep1.DataSet := pnoqy;
    qrdbtext1.DataSet := pnoqy;
    qrdbtext2.DataSet := pnoqy;
    qrdbtext3.DataSet := pnoqy;
    qrdbtext4.DataSet := pnoqy;
    qrdbtext5.DataSet := pnoqy;
    qrdbtext6.DataSet := pnoqy;
    qrdbtext7.DataSet := pnoqy;
    qrdbtext8.DataSet := pnoqy;
     qrdbtext19.DataSet := pnoqy;
    qrdbtext20.DataSet := pnoqy;
      //qrdbtext9.DataSet := pnoqy;
    QRSubDetail1.DataSet := finishrptqy;
    qrdbtext10.DataSet := finishrptqy;
    qrdbtext11.DataSet := finishrptqy;
    qrdbtext12.DataSet := finishrptqy;
    qrdbtext13.DataSet := finishrptqy;
    qrdbtext14.DataSet := finishrptqy;
    qrdbtext15.DataSet := finishrptqy;
    qrdbtext16.DataSet := finishrptqy;
    qrdbtext9.DataSet := finishrptqy;
    qrdbtext17.DataSet := finishrptqy;
    qrdbtext18.DataSet := finishrptqy;
    qrdbtext21.DataSet := finishrptqy;
    qrlabel1.Caption := company + spinedit1.Text + '年' + spinedit2.Text + '月完工批号部门工序明细表';
    quickrep1.Preview;
    close; 
  end;

end;

procedure Tfdeptgxfm.BitBtn1Click(Sender: TObject);
var
  excelid, mybook: variant;
  row, col, i, j, colcount, frow: integer;
  excelflg: boolean;
  rpstr: string;
  datasum, dsum1, dsum2: integer;
  datas1, datas2, datas3: string;
begin
  with sch2 do
  begin
   sql.Clear;
   sql.add('select a.pno 批号,a.dept 部门,a.scgx 生产工序,c.pnonum 落单数,c.kno 款号,(a.totalsum*12/a.totalnum)  累计单价,');
   sql.add('a.totalsum 累计金额,a.totalnum 累计数量,a.work_again_num 返工数,');
   sql.add('(a.totalsum*12/a.totalnum)  totalprice,case  when (a.work_again_num/a.totalnum)*100>0 then str((a.work_again_num/a.totalnum)*100,4,2)+''%'' end ');
   sql.add( '  返工率');
   sql.add(' from finishpno a  inner join scdepts  b on');
   sql.add('a.dept=b.dept  ,scpno c where  c.pno=a.pno');
   if checkbox1.Checked then
   sql.add('and a.work_again_num>0');
    if edit1.Text <> '' then
    begin
      sql.add(' and a.pno=:pno');
      parambyname('pno').asstring := edit1.Text;
    end;
    sql.add('order by a.pno');
    open;
  end  ;


  sch2.DisableControls;
  try
    excelid := createoleobject('excel.application');
    mybook := createoleobject('excel.sheet');
    mybook := excelid.workbooks.add;
    row := 1;
    col := 1;

    excelflg := true;
  except
    excelflg := false;
    application.MessageBox('请确定EXCEL是否正确安装!', '提示信息', mb_ok);
  end;
  if excelflg then
  begin
      colcount := sch2.fieldcount;

      excelid.ActiveSheet.PageSetup.Orientation := xlPortrait ;
//    excelid.ActiveSheet.PageSetup.Orientation := xlLandscape;

      mybook.worksheets[1].cells.item[row, col] := company + '生产日报表';
      mybook.worksheets[1].cells.item[row, col].font.size := 9;
      mybook.worksheets[1].cells.item[row, col].font.bold := true;
      mybook.worksheets[1].cells.item[row, col].HorizontalAlignment := xlcenter;