search_ordersl.pas

服装厂管理系统源程序压缩包, 请耐心阅读提供的文件包含的内容

unit Search_orderSL;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls, Buttons, Grids, DBGrids, ExtCtrls,
  ComCtrls, RpDefine, RpCon, RpConDS, Menus, Mask, DBCtrls;

type
  TFrm_Search_orderSL = class(TForm)
    DataSource: TDataSource;
    DBGrid: TDBGrid;
    ADOQuery1: TADOQuery;
    ADOQtmp: TADOQuery;
    Panel1: TPanel;
    Label1: TLabel;
    Label7: TLabel;
    ComboBox1: TComboBox;
    Edit1: TEdit;
    Label8: TLabel;
    Edit2: TEdit;
    Label9: TLabel;
    Edit3: TEdit;
    ADOQtmp2: TADOQuery;
    Panel2: TPanel;
    ADOQoutbom_log: TADOQuery;
    Panel3: TPanel;
    EdtBtn: TBitBtn;
    ProgressBar1: TProgressBar;
    Label_Display: TLabel;
    ExtBtn: TBitBtn;
    ADOQAccount: TADOQuery;
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure EdtBtnClick(Sender: TObject);
    procedure ComboBox1KeyPress(Sender: TObject; var Key: Char);
    procedure ComboBox1Change(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_Search_orderSL: TFrm_Search_orderSL;

implementation
uses data,sharevar,sharefun, Main;
{$R *.dfm}

procedure TFrm_Search_orderSL.FormShow(Sender: TObject);
begin
  //添加订单号
  with ADOQtmp do
  begin
    close;sql.clear;
    sql.add('select ord_id from order_msg order by order_date');
    open;
    if recordcount =0 then
    begin
        MsgErr(handle,'当前无订单信息，请先定义!');
        exit;
    end;
    first;
    while not eof do
    begin
        ComboBox1.Items.Add(fieldvalues['ord_id']);
        next;
    end;
  end;
  ComboBox1.ItemIndex :=ReadIniFIle(WsInteger,'LISTBOX','FL');
end;

procedure TFrm_Search_orderSL.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
   Action:=Cafree;
end;

procedure TFrm_Search_orderSL.EdtBtnClick(Sender: TObject);
begin
  Form_main.WriteIntoExcel(DBGrid,datetostr(date),trim(ComboBox1.Text)+'━━工序发料信息报表');
end;

procedure TFrm_Search_orderSL.ComboBox1KeyPress(Sender: TObject;
  var Key: Char);
 label goto_label;
var
  tmpstr:string;    
  i, inprice_num:integer;
  empid:integer;
  DataTJ_flag:boolean;
  money:real;
  log_id:integer;
  re_refresh_total:integer;  //是否刷新显示工人工序小计
  is_first_logon:boolean;    //是否第一次登陆创建临时表
begin
  ComboBox1.Text:=trim(ComboBox1.Text);
  if combobox1.Text='' then exit;
  if key<>chr(13) then exit;
  edit1.Clear; edit2.Clear; edit3.Clear;
  //2.显示订单相关信息
  with ADOQtmp do
  begin
    close;sql.clear;
    sql.add('select costume_name,cust_name,clo_name from order_msg,customer,clothing where order_msg.cust_id=customer.cust_id and');
    sql.add(' order_msg.clo_id=clothing.clo_id and ord_id=:v_ord_id');
    Parameters.ParamValues['v_ord_id']:=ComboBox1.Text;
    execsql; open;
    if recordcount =0 then
    begin
        MsgErr(handle,'当前不存在该订单批号，请重新选择!');
        adoquery1.Close;
        EdtBtn.Enabled:=false;  exit;
    end;
    edit1.Text:=fieldvalues['cust_name'];
    edit2.Text:=fieldvalues['costume_name'];
    edit3.Text:=fieldvalues['clo_name'];
  end;


   //3.检查是否给当前所有已验收未入总账的工序定工价
   with adoqtmp do
   begin
     close; sql.Clear;
     sql.add('select a.ord_id, b.seq_name, b.seq_price from v_outbom_log2  a,sequence_price b');
     sql.add('where a.flag=1 and a.TJflag=0 and (a.temp_key=b.temp_key) and a.ord_id=:V_ord_id');
     sql.Add('group by  b.temp_key, a.ord_id, b.seq_name, b.seq_price');
     parameters.ParamValues['v_ord_id']:=trim(ComboBox1.Text);
     try
        open; first;
     except
        Msgerr(handle,'统计人员订单收入报表失败--工价查找！');
        adoquery1.Close;
        EdtBtn.Enabled:=false;
        exit;
     end;
     DataTJ_flag:=true;
     inprice_num:=0;
     while not eof do
     begin
        if (fieldvalues['seq_price']=0) or (fieldvalues['seq_price']=null) then
        begin
          tmpstr:='订单批号：['+fieldvalues['ord_id']+'下的工序 ['+fieldvalues['seq_name']+'] 还没有给工价';
          msgerr(handle, tmpstr);
          DataTJ_flag:=false; Inc(inprice_num);
        end;
        next;
     end;
     if not DataTJ_flag then
     begin
        tmpstr:='统计人员订单收入报表失败--共有 ['+inttostr(inprice_num)+'] 个工序工价未指定！';
        tmpstr:=tmpstr+#13+#13+'     请先到工序工价表指定工价再作统计！';
        msgerr(handle,tmpstr);
        adoquery1.Close;
        EdtBtn.Enabled:=false;  
        close; abort; exit;
     end;
   end;//工序工价统计结束.End


  //4.打开表outbom_kog中指定ord_id的数据
  with ADOQoutbom_log do
  begin
    close;sql.Clear;
    sql.add('select a.empid,c.empname,a.temp_key,sum(a.num * b.seq_price) as total  from outbom_log a,sequence_price b,employee c');
    sql.add('where a.flag=1 and a.ord_id=:v_ordid and a.empid=c.empid and a.temp_key=b.temp_key group by a.empid,c.empname,a.temp_key order by a.empid');
    parameters.ParamValues['v_ordid']:=combobox1.Text;
    execsql; open;
    if recordcount=0 then
    begin
        MsgErr(handle,'当前订单内没查找到数据！');
        combobox1.SetFocus;
        adoquery1.Close;
        EdtBtn.Enabled:=false;  
        close; exit;
    end;
  end;
  //初始化是否刷新小计字段
  re_refresh_total:=0;  //如果 [=0] 那么是第一次创建临时表，则刷新字段标示
  is_first_logon:=true;//是否初次登陆

  //5.找出当前订单批号对应的工序明细
  with ADOQtmp do
  begin
    close;sql.clear;
    sql.Add('select * from sequence_price where ord_id=:v_ord_id  order by temp_key');
    parameters.ParamValues['v_ord_id']:=ComboBox1.Text;
    open;
    if recordcount=0 then
    begin
        MsgErr(handle,'当前订单批号没有设定工序,请在工序管理进行编辑！');
        adoquery1.Close;
        EdtBtn.Enabled:=false;  
        close;exit;
    end;
    first;
  end;  

  //6.生成相关的虚批表
  with ADOQtmp2 do
  begin
      close;sql.clear;
      sql.Add('drop table #tmptable');
      try
        execsql;
      except
      end;
      sql.Clear;
      tmpstr:='';
	    tmpstr:=tmpstr+'create table #tmptable([log_id] [int] IDENTITY (1, 1) NOT NULL,';
      tmpstr:=tmpstr+'[empid] [int] NOT NULL,';
	    tmpstr:=tmpstr+'[empname] varchar(10) NOT NULL,';
      for i:=1 to ADOQtmp.recordcount-1 do
      begin
          tmpstr:=tmpstr+'['+ADOQtmp.FieldValues['temp_key']+'] numeric(18,2) default(0),';
          ADOQtmp.next;
      end;
      tmpstr:=tmpstr+'['+ADOQtmp.FieldValues['temp_key']+'] numeric(18,2) default(0),';
      sql.add(tmpstr);
      sql.Add('[total] numeric(18,2) default(0))');
      try
        execsql;
      except
        MsgErr(handle,'建立临时统计表失败！');
        ADOQtmp2.close;
        exit;
      end;
  end;
  
  //用以刷新小计字段
  goto_label:
  //如果不是第一次登陆则取工序的名称
  if not is_first_logon then
  begin
    re_refresh_total:=re_refresh_total+1;
    //5.找出当前订单批号对应的工序明细
    with ADOQtmp do
    begin
      close;sql.clear;
      sql.Add('select * from sequence_price where ord_id=:v_ord_id  order by temp_key');
      parameters.ParamValues['v_ord_id']:=ComboBox1.Text;
      open;
      if recordcount=0 then
      begin
        MsgErr(handle,'当前订单批号没有设定工序,请在工序管理进行编辑！');
        close;exit;
      end;
      first;
    end;  
  end;
  
  //8.更改临时表字段标题
  with adoquery1 do
  begin
      close;sql.clear;
      sql.add('select * from #tmptable');
      open;
      Fields.FieldByName('log_id').DisplayLabel :='流水号';
      dbgrid.Columns.Items[0].Width:=50;
      Fields.FieldByName('empid').DisplayLabel :='员工编号';
      dbgrid.Columns.Items[1].Width:=60;
      dbgrid.Columns.Items[1].Font.Color:=clblue;
      dbgrid.Columns.Items[1].Font.Size:=12;
      dbgrid.Columns.Items[1].Font.Style:=[fsBold];
      Fields.FieldByName('empname').DisplayLabel:='姓名';
      dbgrid.Columns.Items[2].Width:=50;
      ADOQtmp.First;
      for i:=1 to ADOQtmp.recordcount do
      begin
          dbgrid.Columns.Items[2+i].Width:=85;
          dbgrid.Columns.Items[2+i].Font.Color:=clRed;
          dbgrid.Columns.Items[2+i].Font.Size:=12;
          dbgrid.Columns.Items[2+i].Font.Style:=[fsBold];
          (Fields.FieldByName(ADOQtmp.FieldValues['temp_key']) as TNumericField).DisplayFormat:='￥,0.00';
          Fields.FieldByName(ADOQtmp.FieldValues['temp_key']).DisplayLabel:=ADOQtmp.FieldValues['seq_name'];
          ADOQtmp.next;
      end;
      dbgrid.Columns.Items[ADOQtmp.recordcount+3].Width:=100;
      dbgrid.Columns.Items[ADOQtmp.recordcount+3].Font.Color:=clRed;
      dbgrid.Columns.Items[ADOQtmp.recordcount+3].Font.Size:=12;
      dbgrid.Columns.Items[ADOQtmp.recordcount+3].Font.Style:=[fsBold];
      (Fields.FieldByName('total') as TNumericField).DisplayFormat:='￥,0.00';
      Fields.FieldByName('total').DisplayLabel:='合计';
  end;

  for i:=1 to dbgrid.Columns.Count do
  begin
       dbgrid.Columns.Items[i-1].Title.Font.size:=11;
       dbgrid.Columns.Items[i-1].Title.Font.name:='宋体';
       if i>3 then
       begin
        dbgrid.Columns.Items[i-1].Title.Font.Color:=clblue;
        dbgrid.Columns.Items[i-1].Title.Alignment:=taCenter;
       end
       else
        dbgrid.Columns.Items[i-1].Alignment:=taCenter;
  end;    
  adoqtmp2.Close;

  //9.如果在领料表中没有对应的订单、裁床号、扎号、规格对应的数据，则添加,否则在虚拟表中显示
  ProgressBar1.Max:=ADOQoutbom_log.RecordCount;
  ProgressBar1.Position:=0;
  Label_Display.Visible:=true;
  application.ProcessMessages;
  
  ADOQoutbom_log.First;
  if re_refresh_total=0 then
  while not ADOQoutbom_log.Eof do
  begin
    ProgressBar1.Position:=ProgressBar1.Position+1;
    empid:=ADOQoutbom_log.FieldValues['empid'];
    adoquery1.append;
    adoquery1.FieldValues['empname']:=ADOQoutbom_log.FieldValues['empname'];
    adoquery1.FieldValues['empid']:=ADOQoutbom_log.FieldValues['empid'];
    while (empid=ADOQoutbom_log.FieldValues['empid']) and (not ADOQoutbom_log.eof) do
    begin
        adoquery1.FieldValues[ADOQoutbom_log.FieldValues['temp_key']]:=ADOQoutbom_log.FieldValues['total'];
        ADOQoutbom_log.Next;
    end;
    adoquery1.Post;
  end;  //while not adoquery1.Eof do
  
  //小计人员工序收入工资
  with ADOQtmp do
  begin
    close; sql.Clear;
    sql.Add('select * from #tmptable');
    try
      execsql; open;
    except
      Msgerr(handle,'打开表出错！');
    end;
    first;
    while not eof do
    begin
      money:=0;
      log_id:=ADOQtmp.FieldValues['log_id'];
      for i:=4 to  ADOQtmp.FieldCount do
      begin
        if ADOQtmp.Fields.FieldByNumber(i).Value<>null then
        money:=money+ADOQtmp.Fields.FieldByNumber(i).Value;
      end;
      with ADOQAccount do
      begin
        close; sql.Clear;
        sql.Add('update #tmptable set total=:V_total where log_id=:V_log_id');
        parameters.ParamValues['V_total']:=money;
        parameters.ParamValues['V_log_id']:=log_id;
        try
          execsql;
        except
          msgerr(handle,'小计人员收入失败！');
          break; exit; 
        end;
      end;
      next;
    end;
    //如果是第一次登陆则刷新数据表且回滚显示字段标题
    if re_refresh_total=0 then
    begin
      ADOQuery1.Close; ADOQuery1.Open;
      is_first_logon:=false;
      goto goto_label;
    end; //小计人员工序收入工资.End
  end;

  ProgressBar1.Position:=0;
  Label_Display.Visible:=false;
  EdtBtn.Enabled:=adoquery1.RecordCount>0;
  application.ProcessMessages;
end;

procedure TFrm_Search_orderSL.ComboBox1Change(Sender: TObject);
begin
  ComboBox1.Text:=trim(ComboBox1.Text);
  edit1.Clear; edit2.Clear; edit3.Clear;
end;

end.