unit_mater_sumcfg.~pas

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

unit Unit_Mater_SumCfg;

interface

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

type
  TForm_Mater_SumCfg = class(TForm)
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Panel1: TPanel;
    DateTimePicker_From: TDateTimePicker;
    DateTimePicker_To: TDateTimePicker;
    Panel2: TPanel;
    Panel3: TPanel;
    Panel4: TPanel;
    Bevel1: TBevel;
    Qry_Sum: TQuery;
    DS_Sum: TDataSource;
    Qry_exe: TQuery;
    Query1: TQuery;
    Qry_SumBDEDesigner: TStringField;
    Qry_SumBDEDesigner2: TFloatField;
    Qry_SumBDEDesigner3: TFloatField;
    Qry_SumBDEDesigner4: TFloatField;
    Qry_SumBDEDesigner5: TFloatField;
    Qry_SumBDEDesigner6: TFloatField;
    Qry_SumBDEDesigner7: TFloatField;
    Qry_SumBDEDesigner8: TFloatField;
    Label1: TLabel;
    ListBox1: TListBox;
    Label2: TLabel;
    Button1: TButton;
    Table1: TTable;
    Panel5: TPanel;
    Edit1: TEdit;
    procedure FormCreate(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Edit1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
     procedure zdymaterCheckDiary();
  end;

var
  Form_Mater_SumCfg: TForm_Mater_SumCfg;
 // JLSumSys:TIniFile;
implementation

uses Unit_Mater_PrtSum, IniFiles, Unit_Mater_Main, UnitDM;

{$R *.DFM}

procedure TForm_Mater_SumCfg.FormCreate(Sender: TObject);
var
  fm,fy:integer;
  tm,ty:integer;

//  d:Tdate;
begin

  tm:=strtoint(formatdatetime('m',date)) ;
  ty:=strtoint(formatdatetime('yyyy',date));

  fm:=tm-1;
  fy:=ty;

  if tm = 1 then
  begin
    fm:=12;
    fy:=fy-1;
  end; //if

  if (fy mod 4 <> 0) and (fm = 2)  then
    DateTimePicker_From.date:= strtodate(inttostr(fy) + '-' + inttostr(fm)+ '-28')
  else
    DateTimePicker_From.date:= strtodate(inttostr(fy) + '-' + inttostr(fm)+ '-29');

  DateTimePicker_To.date:=strtodate(inttostr(ty) + '-' + inttostr(tm)+ '-28');


end;

procedure TForm_Mater_SumCfg.BitBtn1Click(Sender: TObject);
   var
  d_from,d_to:Tdate;
  fFrom:Tdate ;
  name:string;
  s_from,s_to:string;
  n_DateMinus:integer;
  JLSumSys:TIniFile;
  per:real;
 begin
  BitBtn1.Cursor:=crAppStart;
   with query1 do
  begin
    close;
    sql.Clear;
    sql.Add('select * from mater ');
    open;
    if recordcount<>0 then
    DateTimePicker_From.date:=dm.zdyGetmaterCheckDate;
    end;
  //fFrom:=dm.zdyGetmaterCheckDate; //上次结帐日期
//  dTo:=dFrom+31;
  //DateTimePicker_From.date:=fFrom;  //上次结帐日期送起始日

  zdymaterCheckDiary;//上次结帐日期
  //frmCommAccSet.iWhichPart:=4;
 // if frmCommAccSet.showmodal = mrok then
 // begin
    if (dm.zdyGetmaterCheckDate >= strtodate(datetostr(DateTimePicker_To.date ))) then
    begin
      showmessage('您选择的结帐日期早于上次结帐日期！'+#13+#13+'上次结帐日期是：'+datetostr(dm.zdyGetmaterCheckDate));
      exit;
    end;
    
  d_from:=DateTimePicker_From.date;
  d_to:=DateTimePicker_To.date;

  s_from:=datetostr(DateTimePicker_From.date);
  s_to:=datetostr(DateTimePicker_To.date);

  n_dateMinus:=0;

//清空临时表
  with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('delete from 临时表  ');
    execsql;
  end; //with

//生成临时表 把时间段内的记录选入临时表
  with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('insert into  临时表 ');
    sql.Add('select * from 原材料台帐  ');
    //sql.Add('where 日期 between :v_from and :v_to  ');
    sql.Add('where 日期 > :v_from and 日期<=:v_to and kuhao=:hao ');
    parambyname('v_from').asdatetime:=strtodate(s_from);
    parambyname('v_to').asdatetime:=strtodate(s_to);
    parambyname('hao').asinteger:=strtoint(edit1.text);
    execsql;
  end; //with

//把时间段内的记录(临时表)  改写成符合条件的记录
  with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('update 临时表 ');
    sql.Add('set 库存 = 0  ');
    //sql.Add('where 日期 between :v_from and :v_to  ');
    sql.Add('where 日期 > :v_from and 日期<=:v_to  ');
    parambyname('v_from').asdatetime:=strtodate(s_from);
    parambyname('v_to').asdatetime:=strtodate(s_to);
    execsql;
  end; //with


//把时间段外 并且符合条件的记录选入临时表
  with Query1 do
  begin
    close;
    sql.Clear;
    sql.Add('select 材料名称,领料单号, 单价,Min(库存) as 最小库存 from 原材料台帐  ');
    sql.Add('where 日期 <= :v_from and kuhao=:hao  '); //?=
    sql.Add('Group by 材料名称, 领料单号,单价 ');
    parambyname('v_from').asdatetime:=strtodate(s_from);
    parambyname('hao').asinteger:=strtoint(edit1.text);
    open;
    first;
    while not eof do
    begin
      if fieldbyname('最小库存').asfloat = 0 then
      begin
       next;
       continue;
      end; //if
      Qry_Exe.close;
      Qry_Exe.sql.Clear;
      Qry_Exe.sql.Add('insert into 临时表 ');
      Qry_Exe.sql.Add('select * from 原材料台帐 ');
      Qry_Exe.sql.Add('where 材料名称 = :v_mater and 领料单号 = :v_outCardNum ');
      Qry_Exe.sql.Add('and 单价 = :v_per and 库存 = :v_stock and kuhao=:hao');
      Qry_Exe.ParamByName('v_mater').asstring:=fieldbyname('材料名称').asstring;
      Qry_Exe.ParamByName('v_outCardNum').asstring:=fieldbyname('领料单号').asstring;
      Qry_Exe.ParamByName('v_per').asfloat:=fieldbyname('单价').asfloat;
      Qry_Exe.ParamByName('v_stock').asfloat:=fieldbyname('最小库存').asfloat;
      Qry_Exe.parambyname('hao').asinteger:=strtoint(edit1.text);
      Qry_Exe.Execsql;

      next;
    end;  //while
  end; //with

//把时间段外的记录(临时表)  改写成符合条件的记录
  with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('update 临时表  ');
    sql.Add('set  入库数量 = 0 , 出库数量 = 0 ');
    sql.Add('where 日期 <= :v_from   '); //?=
    parambyname('v_from').asdatetime:=strtodate(s_from);
    execsql;
  end; //with

//修正数据 为空的记录为零
  with Qry_exe do
  begin
    close;
    sql.Clear;
    sql.Add('update 临时表 set 入库数量 = 0 ');
    sql.Add('where 入库数量 is null ');
    execsql;

    close;
    sql.Clear;
    sql.Add('update 临时表 set 出库数量 = 0 ');
    sql.Add('where 出库数量 is null ');
    execsql;

    close;
    sql.Clear;
    sql.Add('update 临时表 set 库存 = 0 ');
    sql.Add('where 库存 is null ');
    execsql;
  end; //with


//调用SQL语句结账
  with Qry_Sum do
  begin
    close;
    sql.Clear;
    sql.Add('select 材料名称, ');
    sql.Add('sum(库存) as  上月库存, ');
    sql.Add('sum(入库数量) as 本月入库, ');
    sql.Add('sum(出库数量) as 本月出库,  ');
    sql.Add('(sum(库存)+sum(入库数量)-sum(出库数量)) as 本月库存, ');
    sql.Add('((sum(库存*单价)+sum(入库数量*单价)-sum(出库数量*单价))/(sum(库存)+sum(入库数量)-sum(出库数量))) as 单价,');
    sql.Add('sum(出库数量*单价) as 出库总价, ');
    sql.Add('(sum(库存*单价)+sum(入库数量*单价)-sum(出库数量*单价)) as  库存总价 ');
    sql.Add('from 临时表 ');
    sql.Add('group by 材料名称  ');
    open;
   end; //with

 //===清空报表====
   with Qry_exe do
  begin