unit9.pas

材料管理系统

begin
  Query1 := TADOQuery.Create(nil);
  Query1.ConnectionString := Frmmain.ADOConnection1.ConnectionString;
  Query1.Close;
  Query1.SQL.Clear;                          //从cailiao 中取得参数
  Query1.SQL.Add('select danwei,danjia from cailiao where class='''+trim(clclass)+''' and cailiaoming='''+trim(Combobox2.Text)+'''');
  Query1.Open;
//  showmessage(Query1.fieldbyname('danwei').AsString);
  Edit1.Text := Query1.FieldByName('danwei').AsString;    //取得材料的单位
  Edit2.Text := Query1.FieldByName('danjia').AsString;    //取得材料的单价
  with Query1 do
  begin
    close;
    SQL.Clear;                //从WeekDataCache中取得最近一次的数据状态
    SQL.Add('select * from WeekDataCache where class='''+trim(clclass)+''' and cailiaoming='''+trim(Combobox2.Text)+'''');
    Open;
  end;
  if not Query1.eof then
  begin
    kucunshu  := Query1.FieldByName('kucunshu').AsString;  //取得数据计算本材料的库存数量
    rukushu   := Query1.FieldByName('rukushu').AsString;
    chukushu  := Query1.FieldByName('chukushu').AsString;
    if (StrToFloat(kucunshu)+StrToFloat(rukushu)-StrToFloat(chukushu))=0 then
      Edit5.Text := '0'
    else
      Edit5.Text := FloatToStr(StrToFloat(kucunshu)+StrToFloat(rukushu)-StrToFloat(chukushu));
  end
  else
    Edit5.Text := '0';       //库中无存赋0
  FreeAndNil(Query1);
  if StrToFloat(Trim(Edit5.text)) = 0 then
  begin
    //ShowMessage('"'+Combobox2.Text+'" ' + '已无库存.');
    Application.MessageBox('此材料已无库存','无库存',MB_OK OR MB_ICONINFORMATION);
    self.Close;
  end;
end;

procedure Tfrmcailiaochuku.Edit3KeyPress(Sender: TObject; var Key: Char);
begin
  if not (key in ['0'..'9',#8,'.'] ) then
    key:=#0;
//  Edit4.Text := FloatToStr(StrToInt(trim(Edit2.Text)) * StrToInt(trim(Edit3.Text)));
end;

procedure Tfrmcailiaochuku.Button1Click(Sender: TObject);
var
  Query1:TADOQuery;
  Query2:TADOQuery;
  kucunshu:string;
  kucunjine:string;
  rukushu:string;
  rukujine:string;
  chukushu:string;
  chukujine:string;
  jiecunshu:string;
  jiecunjine:string;
  bumenshu:string;
  bumenjine:string;
  zhou:Integer;
  yue:Integer;
  nian:Integer;
  LeftArea:string;
  RightArea:string;
begin
  if IsEmpty(Combobox2.Text) = True then
  begin
    Application.MessageBox('材料名称不能为空!','信息',MB_OK OR MB_ICONINFORMATION);
    exit;
  end;
  if IsEmpty(Combobox1.Text) = True then
  begin
    Application.MessageBox('部门名称不能为空!','信息',MB_OK OR MB_ICONINFORMATION);
    exit;
  end;
  if IsEmpty(Edit3.Text) = True then
  begin
    Application.MessageBox('出库数量不能为空!','错误',MB_OK OR MB_ICONSTOP);
    exit;
  end;
  if StrToFloat(Edit3.Text) > StrToFloat(Edit5.Text) then
  begin
    Application.MessageBox('出库数量大于库存数量.','错误',MB_OK OR MB_ICONERROR);
    exit;
  end;
  if IsNotNumric(trim(Edit3.Text)) = True then
  begin
    Application.MessageBox('输入了非法字符!','错误',MB_OK OR MB_ICONERROR);
    Edit3.Clear;
    exit;
  end;
  if (Trim(Edit3.Text) ='') or (StrToFloat(Trim(Edit3.Text)) = 0) then
  begin
    Application.MessageBox('请输入材料数量!','提示',MB_OK OR MB_ICONINFORMATION);
    Edit3.Clear;
    exit;
  end;
            {以下是出库处理}
//  showmessage(chukuSQL);
//  exit;
  Query1 := TADOQuery.Create(NIL);
  Query2 := TADOQuery.Create(NIL);
  Query1.ConnectionString := Frmmain.ADOConnection1.ConnectionString;
  Query2.ConnectionString := Frmmain.ADOConnection1.ConnectionString;
  nian := YearOf(DateTimePicker1.Date);
  yue  := MonthOfTheYear(DateTimePicker1.Date);
  zhou := WeekOfTheYear(DateTimePicker1.Date);

  Query1.Close;           {防止操作本年前记录}
  Query1.SQL.Clear;
  Query1.SQL.Add('Select max(nian) as maxnian from WeekHaoCai where class='''+clclass+
    ''' and cailiaoming='''+ComboBox2.Text+'''');
  Query1.Prepared;
  Query1.Open;
  if nian < StrToIntDef(Query1.FieldByName('maxnian').AsString,0) then
  begin
    Application.MessageBox('为了防止记录出错,系统禁止操作!'+chr(13)+chr(10)+'请修改日期.','ERROR',MB_OK OR MB_ICONSTOP);
    Exit;
  end;
  {以下为防止这种情况,如2006-1-1为52周,但他在WeekHaoCai表Week和Nian必须为52和2005
  ,在MonthHaoCai和YearHaoCai中不需要考虑.为了避免混乱,不放在防错判断下面}
  if (MonthOfTheYear(DateTimePicker1.Date) <= 1) and (zhou >= 52) then
    nian := nian - 1;   //表示这周还是属于上年的周记录

  Query1.Close;
  Query1.SQL.Clear;
  Query1.SQL.Add('select max(zhou) as maxzhou from WeekHaoCai where nian='+IntToStr(nian)+' and class='''+
    clclass+''' and cailiaoming='''+ComboBox2.Text+'''');
  Query1.Open;
  if zhou < StrToIntDef(Query1.FieldByName('maxzhou').AsString,0) then
  begin
    Application.MessageBox('为了防止记录出错,系统禁止操作本周前的记录!'+chr(13)+chr(10)+'请修改日期为本周.','ERROR',MB_OK OR MB_ICONSTOP);
    Exit;
  end;
      {出库}
  try
  begin
    Query1.Close;
    Query1.SQL.Clear;           {查找WeekDataCache判断是否有材料可取}
    Query1.SQL.Add('select * from WeekDataCache where class='''+clclass+''' and cailiaoming='''+ComboBox2.Text+'''');
    Query1.Prepared;
    Query1.Open;
    if not Query1.Eof then    {有}
    begin
      kucunshu   := Query1.FieldByName('kucunshu').AsString;
      kucunjine  := Query1.FieldByName('kucunjine').AsString;
      rukushu    := Query1.FieldByName('rukushu').AsString;
      rukujine   := Query1.FieldByName('rukujine').AsString;
      chukushu   := Query1.FieldByName('chukushu').AsString;
      chukujine  := Query1.FieldByName('chukujine').AsString;
      chukushu   := FloatToStr(StrToFloat(chukushu)+StrToFloat(Edit3.Text));
      chukujine  := FloatToStr(StrToFloat(chukujine)+StrToFloat(Edit4.Text));
      jiecunshu  := FloatToStr(StrToFloat(kucunshu)+StrToFloat(rukushu)-StrToFloat(chukushu));
      jiecunjine := FloatToStr(StrToFloat(kucunjine)+StrToFloat(rukujine)-StrToFloat(chukujine));
    end
    else          {WeekDataCache中不存在,可能性不太,因为在点击前会对数量进行判断}
      exit;
      {下面只管根据条件加入数据,不会出现取大于存的情况,因为只有取小于存才会执行到此,否则不执行OnClick代码}
      {所以只要相应的对周\月\年表操作}
    Query2.Close;    {以下对周进行操作}
    Query2.SQL.Clear;          {首先更新WeekDataCache}
    Query2.SQL.Add('Update WeekDataCache set chukushu='''+Format('%.2f',[StrToFloat(chukushu)])+''',chukujine='''+
      Format('%.2f',[StrToFloat(chukujine)])+''' where class='''+
      clclass+''' and cailiaoming='''+ComboBox2.Text+'''');
    Query2.ExecSQL;
    Query1.Close;
    Query1.SQL.Clear;        {根据查找WeekHaoCai结果决定是更新记录还是写入新记录}
    Query1.SQL.Add('Select * from WeekHaoCai where nian='+IntToStr(nian)+' and zhou='+IntToStr(zhou)+' and class='''+
      clclass+''' and cailiaoming='''+ComboBox2.Text+'''');
    Query1.Prepared;
    Query1.Open;
    if not Query1.Eof then     //已存在本周的记录,Update
    begin
      Query2.Close;
      Query2.SQL.Clear;
      if ComboBox1.Text = '生产部' then
      begin
        bumenshu := Query1.FieldByName('scbshuliang').AsString;
        bumenjine := Query1.FieldByName('scbjine').AsString;
        SetWeekValuesToChuKuSQL(Format('%.2f',[StrToFloat(bumenshu)]),Format('%.2f',[StrToFloat(bumenjine)]),
          Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),Format('%.2f',[StrToFloat(jiecunshu)]),
          Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '工程部' then
      begin
        bumenshu := Query1.FieldByName('gcbshuliang').AsString;
        bumenjine := Query1.FieldByName('gcbjine').AsString;
        SetWeekValuesToChuKuSQL(Format('%.2f',[StrToFloat(bumenshu)]),Format('%.2f',[StrToFloat(bumenjine)]),
          Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),Format('%.2f',[StrToFloat(jiecunshu)]),
          Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '器件厂' then
      begin
        bumenshu := Query1.FieldByName('qjcshuliang').AsString;
        bumenjine := Query1.FieldByName('qjcjine').AsString;
        SetWeekValuesToChuKuSQL(Format('%.2f',[StrToFloat(bumenshu)]),Format('%.2f',[StrToFloat(bumenjine)]),
          Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),Format('%.2f',[StrToFloat(jiecunshu)]),
          Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '企管部' then
      begin
        bumenshu := Query1.FieldByName('qgbshuliang').AsString;
        bumenjine := Query1.FieldByName('qgbjine').AsString;
        SetWeekValuesToChuKuSQL(Format('%.2f',[StrToFloat(bumenshu)]),Format('%.2f',[StrToFloat(bumenjine)]),
          Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),Format('%.2f',[StrToFloat(jiecunshu)]),
          Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '总办' then
      begin
        bumenshu := Query1.FieldByName('zbshuliang').AsString;
        bumenjine := Query1.FieldByName('zbjine').AsString;
        SetWeekValuesToChuKuSQL(Format('%.2f',[StrToFloat(bumenshu)]),Format('%.2f',[StrToFloat(bumenjine)]),
          Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),Format('%.2f',[StrToFloat(jiecunshu)]),
          Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '财务部' then
      begin
        bumenshu := Query1.FieldByName('cwbshuliang').AsString;
        bumenjine := Query1.FieldByName('cwbjine').AsString;
        SetWeekValuesToChuKuSQL(Format('%.2f',[StrToFloat(bumenshu)]),Format('%.2f',[StrToFloat(bumenjine)]),
          Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),Format('%.2f',[StrToFloat(jiecunshu)]),
          Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '品管部' then
      begin
        bumenshu := Query1.FieldByName('pgbshuliang').AsString;
        bumenjine := Query1.FieldByName('pgbjine').AsString;
        SetWeekValuesToChuKuSQL(Format('%.2f',[StrToFloat(bumenshu)]),Format('%.2f',[StrToFloat(bumenjine)]),
          Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),Format('%.2f',[StrToFloat(jiecunshu)]),
          Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '业务部' then
      begin
        bumenshu := Query1.FieldByName('ywbshuliang').AsString;
        bumenjine := Query1.FieldByName('ywbjine').AsString;
        SetWeekValuesToChuKuSQL(Format('%.2f',[StrToFloat(bumenshu)]),Format('%.2f',[StrToFloat(bumenjine)]),
          Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),Format('%.2f',[StrToFloat(jiecunshu)]),
          Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '销管中心' then
      begin
        bumenshu := Query1.FieldByName('xgzxshuliang').AsString;
        bumenjine := Query1.FieldByName('xgzxjine').AsString;
        SetWeekValuesToChuKuSQL(Format('%.2f',[StrToFloat(bumenshu)]),Format('%.2f',[StrToFloat(bumenjine)]),
          Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),Format('%.2f',[StrToFloat(jiecunshu)]),
          Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
//      showmessage('Update: '+chukuSQL);
//      exit;
      Query2.ExecSQL;
    end
    else                     //不存在本周记录,Insert
    begin
      Query2.Close;
      Query2.SQL.Clear;
      if ComboBox1.Text = '生产部' then
      begin
        bumenshu := Query1.FieldByName('scbshuliang').AsString;
        bumenjine := Query1.FieldByName('scbjine').AsString;
        InsertRecordToWeekHaoCai(Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),
        Format('%.2f',[StrToFloat(jiecunshu)]),Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '工程部' then
      begin
        bumenshu := Query1.FieldByName('gcbshuliang').AsString;
        bumenjine := Query1.FieldByName('gcbjine').AsString;
        InsertRecordToWeekHaoCai(Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),
        Format('%.2f',[StrToFloat(jiecunshu)]),Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '器件厂' then
      begin
        bumenshu := Query1.FieldByName('qjcshuliang').AsString;
        bumenjine := Query1.FieldByName('qjcjine').AsString;
        InsertRecordToWeekHaoCai(Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),
        Format('%.2f',[StrToFloat(jiecunshu)]),Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);
      end;
      if ComboBox1.Text = '企管部' then
      begin
        bumenshu := Query1.FieldByName('qgbshuliang').AsString;
        bumenjine := Query1.FieldByName('qgbjine').AsString;
        InsertRecordToWeekHaoCai(Format('%.2f',[StrToFloat(chukushu)]),Format('%.2f',[StrToFloat(chukujine)]),
        Format('%.2f',[StrToFloat(jiecunshu)]),Format('%.2f',[StrToFloat(jiecunjine)]),zhou,nian);
        Query2.SQL.Add(chukuSQL);