unit_mater_qry.~pas

此代码为企业原料管理代码

unit Unit_Mater_Qry;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, ComCtrls, ExtCtrls, Db, DBTables, StdCtrls, Buttons,
  Mask, DBCtrls;

type
  TForm_Mater_Qry = class(TForm)
    Qry_Qry: TQuery;
    DS_Qry: TDataSource;
    DBGrid1: TDBGrid;
    PageControl_Qry: TPageControl;
    TabSheet1: TTabSheet;
    DateTimePicker_From0: TDateTimePicker;
    DateTimePicker_To0: TDateTimePicker;
    Panel1: TPanel;
    Panel2: TPanel;
    BitBtn1: TBitBtn;
    TabSheet2: TTabSheet;
    Edit_Mater0: TEdit;
    Panel3: TPanel;
    DateTimePicker_From1: TDateTimePicker;
    DateTimePicker_To1: TDateTimePicker;
    Panel7: TPanel;
    Panel8: TPanel;
    BitBtn3: TBitBtn;
    TabSheet3: TTabSheet;
    Panel4: TPanel;
    Panel5: TPanel;
    Edit_mater1: TEdit;
    ComboBox_OutCardNum: TComboBox;
    Panel6: TPanel;
    Edit_Index1: TEdit;
    BitBtn5: TBitBtn;
    Qry_exe: TQuery;
    Panel10: TPanel;
    BitBtn4: TBitBtn;
    TabSheet4: TTabSheet;
    BitBtn6: TBitBtn;
    Panel9: TPanel;
    Panel11: TPanel;
    Panel12: TPanel;
    Edit_mater2: TEdit;
    Edit_index2: TEdit;
    Button1: TButton;
    BitBtn7: TBitBtn;
    Button2: TButton;
    BitBtn2: TBitBtn;
    BitBtn8: TBitBtn;
    BitBtn9: TBitBtn;
    ComboBox_InCardNum: TComboBox;
    Panel13: TPanel;
    Edit1: TEdit;
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure Edit_Index1Change(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure Edit_mater1Change(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure Edit_Mater0Change(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure DBGrid1DblClick(Sender: TObject);
    procedure Edit_index2Change(Sender: TObject);
    procedure Edit_mater2Change(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Edit_Mater0Exit(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Edit1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form_Mater_Qry: TForm_Mater_Qry;

implementation

uses Unit_Mater_Main, Unit_Mater_OutView, Unit_Mater_InView, UnitDM;

{$R *.DFM}

procedure TForm_Mater_Qry.BitBtn1Click(Sender: TObject);
var
  Mod_date:Tdate;
begin
  Mod_date:=DateTimePicker_From0.date;
  Mod_date:=Mod_date-1;

  with Qry_qry do
  begin
    close;
    sql.Clear;
    sql.Add('select * from 原材料台帐  ');
    sql.Add('where 日期 between :v_from and :v_to and kuhao=:hao');
    sql.Add('order by 材料名称, 领料单号, 单价, 库存 Desc ');
    parambyname('v_from').asdatetime:=Mod_date;
    parambyname('v_to').asdatetime:=DateTimePicker_To0.date;
    parambyname('hao').asinteger:=strtoint(edit1.text);
    open;
  end; //with

  DBGrid1.setfocus;
end;

procedure TForm_Mater_Qry.BitBtn3Click(Sender: TObject);
var
  Mod_date:Tdate;
begin
  if Edit_Mater0.text = '' then
  begin
    showmessage('请填写原材料名称!');
    exit;
  end; //if

  Mod_date:=DateTimePicker_From1.date;
  Mod_date:=Mod_date-1;

  with Qry_qry do
  begin
    close;
    sql.Clear;
    sql.Add('select * from 原材料台帐  ');
    sql.Add('where 日期 between :v_from and :v_to and 材料名称 = :v_mater ');
    sql.Add('order by 领料单号, 日期, 标识号 ');

    parambyname('v_from').asdatetime:=Mod_date;
    parambyname('v_to').asdatetime:=DateTimePicker_To1.date;
    parambyname('v_mater').asstring:=edit_mater0.text;
    open;
  end; //with

    DBGrid1.setfocus;


end;

procedure TForm_Mater_Qry.Edit_Index1Change(Sender: TObject);
begin
  with Qry_exe  do
  begin
    close;
    sql.clear;
    sql.add('select * from 原材料字典 where 简称 = :v_index ');
    parambyname('v_index').asstring:=Edit_Index1.text;
    open;
    Edit_mater1.text:= fieldbyname('材料名称').asstring;
  end;//with

end;

procedure TForm_Mater_Qry.BitBtn5Click(Sender: TObject);
begin
    if  Edit_mater1 .text = '' then
  begin
    showmessage('请填写原材料名称,领料单号!');
    exit;
  end; //if
  with Qry_qry do
  begin
    close;
    sql.Clear;
    sql.Add('select * from 原材料台帐  ');
    sql.Add('where 材料名称 = :v_mater and 领料单号= :v_OutCardNum');
    parambyname('v_mater').asstring:=edit_mater1.text;
    parambyname('v_OutCardNum').asstring:=ComboBox_OutCardNum.text;
    open;
  end; //with

    DBGrid1.setfocus;

end;

procedure TForm_Mater_Qry.Edit_mater1Change(Sender: TObject);
begin
  with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('select 领料单号 from 原材料台帐  ');
    sql.Add('where 材料名称 = :v_mater and 最后领料标识 = :v_LastOutCardFlag ');
    parambyname('v_mater').asstring:=edit_mater1.text;
    parambyname('v_LastOutCardFlag').asboolean:=true;
    open;
    if recordcount > 0 then
    begin
      ComboBox_OutCardNum.Items.clear;
      while not eof do
      begin
        ComboBox_OutCardNum.Items.Add(fieldbyname('领料单号').asstring);
        next;
      end; //while
    end;  //if
  end;   //with

  ComboBox_OutCardNum.ItemIndex:=0;


end;

procedure TForm_Mater_Qry.BitBtn4Click(Sender: TObject);
begin
  if Qry_Qry.FieldByName('出入标识').asboolean then
    Form_Mater_OutView.show
  else
    Form_Mater_InView.show;
end;

procedure TForm_Mater_Qry.Edit_Mater0Change(Sender: TObject);
begin

  with Qry_exe  do
  begin
    close;
    sql.clear;
    sql.add('select * from 原材料字典 where 简称 = :v_index ');
    parambyname('v_index').asstring:= Edit_mater0.text;
    open;
    if recordcount = 0 then
    begin
    //showmessage('无此原料!');
    exit;
    end;
     Edit_mater0.text:= fieldbyname('材料名称').asstring;
   // Edit_mater0.text:= fieldbyname('材料名称').asstring;
  end;//with
 
end;

procedure TForm_Mater_Qry.FormCreate(Sender: TObject);
begin
  Edit_index2.text:='';
  Edit_mater2.text:='';

  DateTimePicker_From0.date:=date - 30;
  DateTimePicker_To0.date:=date ;

  DateTimePicker_From1.date:=date - 30;
  DateTimePicker_To1.date:=date ;

  button1.height:=0;
  button1.width:=0;
  PageControl_Qry.ActivePageIndex:=0;

end;

procedure TForm_Mater_Qry.DBGrid1DblClick(Sender: TObject);
begin
  BitBtn4Click(sender);
end;

procedure TForm_Mater_Qry.Edit_index2Change(Sender: TObject);
begin
  with Qry_exe  do
  begin
    close;
    sql.clear;
    sql.add('select * from 原材料字典 where 简称 = :v_index ');
    parambyname('v_index').asstring:=Edit_Index2.text;
    open;
    Edit_mater2.text:= fieldbyname('材料名称').asstring;
  end;//with

end;

procedure TForm_Mater_Qry.Edit_mater2Change(Sender: TObject);
begin
 with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('select 入库单号 from 原材料台帐  ');
    sql.Add('where 材料名称 = :v_mater  ');
   // sql.Add('where 材料名称 = :v_mater and 领料标识 = :v_LastOutCardFlag  and 库存 > 0 ');
    parambyname('v_mater').asstring:=edit_mater2.text;
   // parambyname('v_LastOutCardFlag').asboolean:=false;
    open;
    if recordcount > 0 then
    begin
      ComboBox_InCardNum.Items.clear;
      while not eof do
      begin
        ComboBox_InCardNum.Items.Add(fieldbyname('入库单号').asstring);
        next;
      end; //while
    end;  //if
  end;   //with

  ComboBox_InCardNum.ItemIndex:=0;

end;

procedure TForm_Mater_Qry.BitBtn6Click(Sender: TObject);
begin
  if  Edit_mater2 .text = '' then
  begin
    showmessage('请填写原材料名称,入库单号!');
    exit;
  end; //if
  with Qry_qry do
  begin
    close;
    sql.Clear;
    sql.Add('select * from 原材料台帐  ');
    sql.Add('where 材料名称 = :v_mater and 入库单号= :v_InCardNum');
    parambyname('v_mater').asstring:=edit_mater2.text;
    parambyname('v_InCardNum').asstring:=ComboBox_InCardNum.text;
    open;
  end; //with

  DBGrid1.setfocus;

end;

procedure TForm_Mater_Qry.Button1Click(Sender: TObject);
begin
  close;
end;

procedure TForm_Mater_Qry.BitBtn7Click(Sender: TObject);
var
  n_id:integer;
  s_mater, s_OutCardNum :string;
  f_per:real;
begin
  if not Qry_Qry.Active then
  begin
    showmessage('请先调出某个原材料的台帐');
    exit;
  end; //if
  
  //判断日期是否小于等于结账日期
  if Qry_Qry.fieldbyname('日期').asdatetime <=dm.zdyGetmaterCheckDate then //d_LimDate then
  begin
    showmessage('不允许删除结账日期之前的记录!'+#13+#13+'上次结帐日期是：'+datetostr(dm.zdyGetmaterCheckDate));
    perform(WM_NEXTDLGCTL,0,0);
    exit;
  end;

//删除警告!
  if messagedlg('会删除该 '+Qry_Qry.fieldbyname('材料名称').asstring+' 的记录 要继续吗？',mtWarning,[mbYes,mbNo],0) = mrNo then
    exit;

// 判断删除是否合理
  if not Qry_Qry.fieldbyname('出入标识').asboolean then  //是入库
  begin
    with Qry_exe do
    begin
      close;
      sql.Clear;
      sql.Add('select * from 原材料台帐 where 材料名称 = :v_mater ');
      sql.Add('and 领料单号 = :v_OutCardNum and 单价= :v_Per');
      parambyname('v_mater').asstring:=Qry_Qry.fieldbyname('材料名称').asstring;
      parambyname('v_OutCardNum').asstring:=Qry_Qry.fieldbyname('领料单号').asstring;
      parambyname('v_Per').asfloat:=Qry_Qry.fieldbyname('单价').asfloat;

      open;
      if recordcount > 1 then
      begin
        showmessage('此条入库记录已经对应多条出库记录.'+#13+#13+'不能删除!');
        exit;
      end; //if
    end; //with
  end; // if

//为刷新表格记录数据
  s_mater:=Qry_Qry.fieldbyname('材料名称').asstring;
  s_OutCardNum:=Qry_Qry.fieldbyname('领料单号').asstring;
  f_per:=Qry_Qry.fieldbyname('单价').asfloat;


//修改该记录日期以后的相关记录库存
  with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('update 原材料台帐 set 库存 = (库存*1000 + :v_Rewt)/1000 ');
    sql.Add('where 库存 < :v_stock and 材料名称 = :v_mater and 领料单号 = :v_outCardNum and 单价 = :v_Per ');
    parambyname('v_stock').asfloat:=Qry_Qry.fieldbyname('库存').asfloat;
    parambyname('v_mater').asstring:=Qry_Qry.fieldbyname('材料名称').asstring;
    parambyname('v_OutCardNum').asstring:=Qry_Qry.fieldbyname('领料单号').asstring;
    parambyname('v_Rewt').asfloat:=Qry_Qry.fieldbyname('出库数量').asfloat*1000;
    parambyname('v_Per').asfloat:=Qry_Qry.fieldbyname('单价').asfloat;
    execsql;
  end; //with

//修改相关入库记录  剩余库存
  with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('update 原材料台帐 set 剩余库存 = (剩余库存*1000 + :v_Rewt)/1000 ');
    sql.Add('where 出入标识 = :v_IfOut and 材料名称 = :v_mater and 领料单号 = :v_outCardNum and 单价 = :v_Per');
    parambyname('v_Rewt').asfloat:=Qry_Qry.fieldbyname('出库数量').asfloat*1000;
    parambyname('v_IfOut').asboolean:=false;
    parambyname('v_mater').asstring:=Qry_Qry.fieldbyname('材料名称').asstring;
    parambyname('v_OutCardNum').asstring:=Qry_Qry.fieldbyname('领料单号').asstring;
    parambyname('v_Per').asfloat:=Qry_Qry.fieldbyname('单价').asfloat;
    execsql;
  end; //with

//删除该记录
  with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('delete from 原材料台帐 where 标识号 = :v_id ');
    parambyname('v_id').asinteger:=Qry_Qry.fieldbyname('标识号').asinteger;
    execsql;
  end; //with



//修改最后领料标识的位置 先全部False 在最后一个True
  with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('update 原材料台帐 set 最后领料标识 = :v_LastOutCardFlag');
    sql.Add('where 材料名称 = :v_mater and  领料单号 = :v_outCardNum  and 单价 = :v_per  ');
    parambyname('v_LastOutCardFlag').asboolean:=false;
    parambyname('v_mater').asstring:=Qry_Qry.fieldbyname('材料名称').asstring;
    parambyname('v_OutCardNum').asstring:=Qry_Qry.fieldbyname('领料单号').asstring;
    parambyname('v_Per').asfloat:=Qry_Qry.fieldbyname('单价').asfloat;
    execsql;

    close;
    sql.Clear;
    sql.Add('select * from 原材料台帐 ');
    sql.Add('where 材料名称 = :v_mater and  领料单号 = :v_outCardNum  and 单价 = :v_per  ');
    sql.Add('order by 库存  ');
    parambyname('v_mater').asstring:=Qry_Qry.fieldbyname('材料名称').asstring;
    parambyname('v_OutCardNum').asstring:=Qry_Qry.fieldbyname('领料单号').asstring;
    parambyname('v_Per').asfloat:=Qry_Qry.fieldbyname('单价').asfloat;
    open;
    n_id:=fieldbyname('标识号').asinteger;

    close;
    sql.Clear;
    sql.Add('update 原材料台帐 set 最后领料标识 = :v_LastOutCardFlag ');
    sql.Add('where  标识号 = :v_id');
    parambyname('v_LastOutCardFlag').asboolean:=true;
    parambyname('v_id').asinteger:=n_id;
    execsql;
  end; //with

// 本窗体刷新
  Qry_Qry.close;
  Qry_Qry.open;


  //刷新主界面表格
  with  Form_Mater_Main.Qry_Main do
  begin
    close;
    sql.Clear;
    sql.Add('select * from 原材料台帐 where 材料名称 = :v_mater ');
    sql.Add('and 领料单号 = :v_OutCardNum  and 单价 = :v_per');
    sql.Add('order by 库存 Desc ');
    parambyname('v_mater').asstring:=s_mater;
    parambyname('v_OutCardNum').asstring:= s_OutCardNum;
    parambyname('v_per').asfloat:=f_per;
    open;
  end;//with

end;

procedure TForm_Mater_Qry.Button2Click(Sender: TObject);
begin
  Qry_Qry.active:=false;
  Close;
end;

procedure TForm_Mater_Qry.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Qry_Qry.active:=false;
  Qry_exe.active:=false;
end;

procedure TForm_Mater_Qry.Edit_Mater0Exit(Sender: TObject);
begin
{
    with Qry_exe  do
  begin
    close;
    sql.clear;
    sql.add('select * from 原材料字典 where 简称 = :v_index ');
    parambyname('v_index').asstring:= Edit_mater0.text;
    open;
    if recordcount = 0 then
    begin
    showmessage('无此原料!');
    exit;
    end;
     Edit_mater0.text:= fieldbyname('材料名称').asstring;
   // Edit_mater0.text:= fieldbyname('材料名称').asstring;
  end;//with
  }
end;
 
procedure TForm_Mater_Qry.FormShow(Sender: TObject);
begin
 Edit1.Text:='1';
end;

procedure TForm_Mater_Qry.Edit1Click(Sender: TObject);
begin
 if edit1.text='1' then
  begin
  edit1.text:='2';
  exit;
  end;
  if edit1.text='2' then
  edit1.text:='1';
end;

end.