unitqueryform.pas

来自「手机批发商进销存管理」· PAS 代码 · 共 616 行 · 第 1/2 页

          begin
            CurDate := DM1.ADOQuery1.FieldByName('InDate').AsString;
            GoodsName := DM1.ADOQuery1.FieldByName('GoodsName').AsString;
            GoodsType := DM1.ADOQuery1.FieldByName('GoodsType').AsString;
            GoodsOwner := ' ';
            CurNum := DM1.ADOQuery1.FieldByName('CurNum').AsString;
            if CurNum='' then CurNum := '0';
            //计算数量
            DM1.ADOQuery2.Filter := 'InDate='''+CurDate+''' and GoodsName='''+GoodsName+''' and GoodsType='''+GoodsType+'''';
            LastNum := DM1.ADOQuery2.FieldByName('LastNum').AsString;
            if LastNum='' then LastNum := '0';
            TotNum := IntToStr(StrToInt(CurNum) + StrToInt(LastNum));
            //insert
            DM1.ADOQuery3.Close;
            DM1.ADOQuery3.SQL.Text := sqlInsert;
            DM1.ADOQuery3.Parameters.ParamByName('CurDate').Value := CurDate;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsName').Value := GoodsName;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsType').Value := GoodsType;
            DM1.ADOQuery3.Parameters.ParamByName('CurNum').Value := CurNum;
            DM1.ADOQuery3.Parameters.ParamByName('LastNum').Value := LastNum;
            DM1.ADOQuery3.Parameters.ParamByName('TotNum').Value := TotNum;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsOwner').Value := GoodsOwner;
            DM1.ADOQuery3.Parameters.ParamByName('button1').Value := '查看';
            DM1.ADOQuery3.Parameters.ParamByName('button2').Value := '查看';
            DM1.ADOQuery3.Parameters.ParamByName('button3').Value := '查看';
            DM1.ADOQuery3.ExecSQL;

            //下一条
            DM1.ADOQuery1.Next;
          end;
        end;
        
      1://库存表
        begin
          DM1.ADOQuery1.Close;
          DM1.ADOQuery1.SQL.Text := ' select A.InDate, A.GoodsOwner, B.GoodsName, B.GoodsType, count(1) as CurNum '
                                  + ' from Tab_Goods A '
                                  + ' left join Tab_GoodsNo B on A.GoodsNo=B.GoodsNo '
                                  + ' where A.InDate>=''' + startDate + ''' and A.InDate<=''' + endDate + ''' '
                                  + ' and A.IsSale=0 ';
          if gt<>'' then
            DM1.ADOQuery1.SQL.Text :=  DM1.ADOQuery1.SQL.Text + ' and A.GoodsOwner=''' + gt + ''' ';
          DM1.ADOQuery1.SQL.Text :=  DM1.ADOQuery1.SQL.Text
                                  + ' group by A.InDate, A.GoodsOwner, B.GoodsName, B.GoodsType '
                                  + ' order by A.InDate, A.GoodsOwner, B.GoodsName, B.GoodsType ';
          DM1.ADOQuery1.Open;
          DM1.ADOQuery2.Close;
          DM1.ADOQuery2.Filter := '';
          DM1.ADOQuery2.Filtered := true;
          DM1.ADOQuery2.SQL.Text := ' select A.InDate, A.GoodsOwner, B.GoodsName, B.GoodsType, count(1) as LastNum '
                                  + ' from Tab_Goods A '
                                  + ' left join Tab_GoodsNo B on A.GoodsNo=B.GoodsNo '
                                  + ' where A.InDate<''' + startDate + ''' '
                                  + ' and A.IsSale=0 ';
          if gt<>'' then
            DM1.ADOQuery2.SQL.Text :=  DM1.ADOQuery2.SQL.Text + ' and A.GoodsOwner=''' + gt + ''' ';
          DM1.ADOQuery2.SQL.Text :=  DM1.ADOQuery2.SQL.Text
                                  + ' group by A.InDate, A.GoodsOwner, B.GoodsName, B.GoodsType '
                                  + ' order by A.InDate, A.GoodsOwner, B.GoodsName, B.GoodsType ';
          DM1.ADOQuery2.Open;

          DM1.ADOQuery1.First;
          while not DM1.ADOQuery1.Eof do
          begin
            CurDate := DM1.ADOQuery1.FieldByName('InDate').AsString;
            GoodsName := DM1.ADOQuery1.FieldByName('GoodsName').AsString;
            GoodsType := DM1.ADOQuery1.FieldByName('GoodsType').AsString;
            GoodsOwner := DM1.ADOQuery1.FieldByName('GoodsOwner').AsString;
            CurNum := DM1.ADOQuery1.FieldByName('CurNum').AsString;
            if CurNum='' then CurNum := '0';
            //计算数量
            DM1.ADOQuery2.Filter := 'InDate='''+CurDate+''' and GoodsName='''+GoodsName+''' and GoodsType='''+GoodsType+'''';
            LastNum := DM1.ADOQuery2.FieldByName('LastNum').AsString;
            if LastNum='' then LastNum := '0';
            TotNum := IntToStr(StrToInt(CurNum) + StrToInt(LastNum));
            //insert
            DM1.ADOQuery3.Close;
            DM1.ADOQuery3.SQL.Text := sqlInsert;
            DM1.ADOQuery3.Parameters.ParamByName('CurDate').Value := CurDate;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsName').Value := GoodsName;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsType').Value := GoodsType;
            DM1.ADOQuery3.Parameters.ParamByName('CurNum').Value := CurNum;
            DM1.ADOQuery3.Parameters.ParamByName('LastNum').Value := LastNum;
            DM1.ADOQuery3.Parameters.ParamByName('TotNum').Value := TotNum;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsOwner').Value := GoodsOwner;
            DM1.ADOQuery3.Parameters.ParamByName('button1').Value := '查看';
            DM1.ADOQuery3.Parameters.ParamByName('button2').Value := '查看';
            DM1.ADOQuery3.Parameters.ParamByName('button3').Value := '查看';
            DM1.ADOQuery3.ExecSQL;

            //下一条
            DM1.ADOQuery1.Next;
          end;
        end;

      2://销售表
        begin
          DM1.ADOQuery1.Close;
          DM1.ADOQuery1.SQL.Text := ' select A.OutDate, A.GoodsOwner, B.GoodsName, B.GoodsType, count(1) as CurNum '
                                  + ' from Tab_Goods A '
                                  + ' left join Tab_GoodsNo B on A.GoodsNo=B.GoodsNo '
                                  + ' where A.OutDate>=''' + startDate + ''' and A.OutDate<=''' + endDate + ''' '
                                  + ' and A.IsSale=1 ';
          if gt<>'' then
            DM1.ADOQuery1.SQL.Text :=  DM1.ADOQuery1.SQL.Text + ' and A.GoodsOwner=''' + gt + ''' ';
          DM1.ADOQuery1.SQL.Text :=  DM1.ADOQuery1.SQL.Text
                                  + ' group by A.OutDate, A.GoodsOwner, B.GoodsName, B.GoodsType '
                                  + ' order by A.OutDate, A.GoodsOwner, B.GoodsName, B.GoodsType ';
          DM1.ADOQuery1.Open;
          DM1.ADOQuery2.Close;
          DM1.ADOQuery2.Filter := '';
          DM1.ADOQuery2.Filtered := true;
          DM1.ADOQuery2.SQL.Text := ' select A.OutDate, A.GoodsOwner, B.GoodsName, B.GoodsType, count(1) as LastNum '
                                  + ' from Tab_Goods A '
                                  + ' left join Tab_GoodsNo B on A.GoodsNo=B.GoodsNo '
                                  + ' where A.OutDate<''' + startDate + ''' '
                                  + ' and A.IsSale=1 ';
          if gt<>'' then
            DM1.ADOQuery2.SQL.Text :=  DM1.ADOQuery2.SQL.Text + ' and A.GoodsOwner=''' + gt + ''' ';
          DM1.ADOQuery2.SQL.Text :=  DM1.ADOQuery2.SQL.Text
                                  + ' group by A.OutDate, A.GoodsOwner, B.GoodsName, B.GoodsType '
                                  + ' order by A.OutDate, A.GoodsOwner, B.GoodsName, B.GoodsType ';
          DM1.ADOQuery2.Open;

          DM1.ADOQuery1.First;
          while not DM1.ADOQuery1.Eof do
          begin
            CurDate := DM1.ADOQuery1.FieldByName('OutDate').AsString;
            GoodsName := DM1.ADOQuery1.FieldByName('GoodsName').AsString;
            GoodsType := DM1.ADOQuery1.FieldByName('GoodsType').AsString;
            GoodsOwner := DM1.ADOQuery1.FieldByName('GoodsOwner').AsString;
            CurNum := DM1.ADOQuery1.FieldByName('CurNum').AsString;
            if CurNum='' then CurNum := '0';
            //计算数量
            DM1.ADOQuery2.Filter := 'OutDate='''+CurDate+''' and GoodsName='''+GoodsName+''' and GoodsType='''+GoodsType+'''';
            LastNum := DM1.ADOQuery2.FieldByName('LastNum').AsString;
            if LastNum='' then LastNum := '0';
            TotNum := IntToStr(StrToInt(CurNum) + StrToInt(LastNum));
            //insert
            DM1.ADOQuery3.Close;
            DM1.ADOQuery3.SQL.Text := sqlInsert;
            DM1.ADOQuery3.Parameters.ParamByName('CurDate').Value := CurDate;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsName').Value := GoodsName;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsType').Value := GoodsType;
            DM1.ADOQuery3.Parameters.ParamByName('CurNum').Value := CurNum;
            DM1.ADOQuery3.Parameters.ParamByName('LastNum').Value := LastNum;
            DM1.ADOQuery3.Parameters.ParamByName('TotNum').Value := TotNum;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsOwner').Value := GoodsOwner;
            DM1.ADOQuery3.Parameters.ParamByName('button1').Value := '查看';
            DM1.ADOQuery3.Parameters.ParamByName('button2').Value := '查看';
            DM1.ADOQuery3.Parameters.ParamByName('button3').Value := '查看';
            DM1.ADOQuery3.ExecSQL;

            //下一条
            DM1.ADOQuery1.Next;
          end;
        end;

      3://售后表
        begin
          DM1.ADOQuery1.Close;
          DM1.ADOQuery1.SQL.Text := ' select A.ServiceDate, B.GoodsName, B.GoodsType, count(1) as CurNum '
                                  + ' from Tab_Service A '
                                  + ' left join Tab_GoodsNo B on A.GoodsNo=B.GoodsNo '
                                  + ' where A.ServiceDate>=''' + startDate + ''' and A.ServiceDate<=''' + endDate + ''' '
                                  + ' group by A.ServiceDate, B.GoodsName, B.GoodsType '
                                  + ' order by A.ServiceDate, B.GoodsName, B.GoodsType ';
          DM1.ADOQuery1.Open;
          //写入
          DM1.ADOQuery1.First;
          while not DM1.ADOQuery1.Eof do
          begin
            CurDate := DM1.ADOQuery1.FieldByName('ServiceDate').AsString;
            GoodsName := DM1.ADOQuery1.FieldByName('GoodsName').AsString;
            GoodsType := DM1.ADOQuery1.FieldByName('GoodsType').AsString;
            GoodsOwner := ' ';
            CurNum := DM1.ADOQuery1.FieldByName('CurNum').AsString;
            if CurNum='' then CurNum := '0';
            LastNum := '0';
            TotNum := CurNum;
            //insert
            DM1.ADOQuery3.Close;
            DM1.ADOQuery3.SQL.Text := sqlInsert;
            DM1.ADOQuery3.Parameters.ParamByName('CurDate').Value := CurDate;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsName').Value := GoodsName;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsType').Value := GoodsType;
            DM1.ADOQuery3.Parameters.ParamByName('CurNum').Value := CurNum;
            DM1.ADOQuery3.Parameters.ParamByName('LastNum').Value := LastNum;
            DM1.ADOQuery3.Parameters.ParamByName('TotNum').Value := TotNum;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsOwner').Value := GoodsOwner;
            DM1.ADOQuery3.Parameters.ParamByName('button1').Value := '查看';
            DM1.ADOQuery3.Parameters.ParamByName('button2').Value := '查看';
            DM1.ADOQuery3.Parameters.ParamByName('button3').Value := '查看';
            DM1.ADOQuery3.ExecSQL;

            //下一条
            DM1.ADOQuery1.Next;
          end;
        end;
        
      4://回访表
        begin
          DM1.ADOQuery1.Close;
          DM1.ADOQuery1.SQL.Text := ' select A.CallDate, B.GoodsName, B.GoodsType, count(1) as CurNum '
                                  + ' from Tab_Call A '
                                  + ' left join Tab_GoodsNo B on A.GoodsNo=B.GoodsNo '
                                  + ' where A.CallDate>=''' + startDate + ''' and A.CallDate<=''' + endDate + ''' '
                                  + ' group by A.CallDate, B.GoodsName, B.GoodsType '
                                  + ' order by A.CallDate, B.GoodsName, B.GoodsType ';
          DM1.ADOQuery1.Open;
          //写入
          DM1.ADOQuery1.First;
          while not DM1.ADOQuery1.Eof do
          begin
            CurDate := DM1.ADOQuery1.FieldByName('CallDate').AsString;
            GoodsName := DM1.ADOQuery1.FieldByName('GoodsName').AsString;
            GoodsType := DM1.ADOQuery1.FieldByName('GoodsType').AsString;
            GoodsOwner := ' ';
            CurNum := DM1.ADOQuery1.FieldByName('CurNum').AsString;
            if CurNum='' then CurNum := '0';
            LastNum := '0';
            TotNum := CurNum;
            //insert
            DM1.ADOQuery3.Close;
            DM1.ADOQuery3.SQL.Text := sqlInsert;
            DM1.ADOQuery3.Parameters.ParamByName('CurDate').Value := CurDate;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsName').Value := GoodsName;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsType').Value := GoodsType;
            DM1.ADOQuery3.Parameters.ParamByName('CurNum').Value := CurNum;
            DM1.ADOQuery3.Parameters.ParamByName('LastNum').Value := LastNum;
            DM1.ADOQuery3.Parameters.ParamByName('TotNum').Value := TotNum;
            DM1.ADOQuery3.Parameters.ParamByName('GoodsOwner').Value := GoodsOwner;
            DM1.ADOQuery3.Parameters.ParamByName('button1').Value := '查看';
            DM1.ADOQuery3.Parameters.ParamByName('button2').Value := '查看';
            DM1.ADOQuery3.Parameters.ParamByName('button3').Value := '查看';
            DM1.ADOQuery3.ExecSQL;

            //下一条
            DM1.ADOQuery1.Next;
          end;
        end;
    end;

    //提交事务
    DM1.ADOConnection1.CommitTrans;
    DM1.ADOQuery1.Close;
    DM1.ADOQuery2.Close;
    DM1.ADOQuery3.Close;
    DM1.ADOQuery2.Filter := '';
    DM1.ADOQuery2.Filtered := false;
    Screen.Cursor := oldCur;
  except
    DM1.ADOConnection1.RollbackTrans;
    DM1.ADOQuery1.Close;
    DM1.ADOQuery2.Close;
    DM1.ADOQuery3.Close;    
    DM1.ADOQuery2.Filter := '';
    DM1.ADOQuery2.Filtered := false;        
    Screen.Cursor := oldCur;
  end;
end;

procedure TQueryForm.DBGrid1CellClick(Column: TColumn);
begin
  if not ADOQuery1.Active then Exit;
  if ADOQuery1.IsEmpty then Exit;
  if Column.FieldName='button1' then
  begin
    selectColumn := 'button1';
  end
  else if Column.FieldName='button2' then
  begin
    selectColumn := 'button2';
  end
  else if Column.FieldName='button3' then
  begin
    selectColumn := 'button3';
  end
  else
  begin
    selectColumn := '';  
  end;
  selectCurDate := ADOQuery1.FieldByName('CurDate').AsString;
  selectGoodsOwner := ADOQuery1.FieldByName('GoodsOwner').AsString;
  selectGoodsName := ADOQuery1.FieldByName('GoodsName').AsString;
  selectGoodsType := ADOQuery1.FieldByName('GoodsType').AsString;
  ShowDetail(ComboBox1.ItemIndex, selectColumn, selectCurDate, selectGoodsOwner, selectGoodsName, selectGoodsType);
end;

procedure TQueryForm.DBGrid1EditButtonClick(Sender: TObject);
begin
  if not ADOQuery1.Active then Exit;
  if ADOQuery1.IsEmpty then Exit;
  ShowDetail(ComboBox1.ItemIndex, selectColumn, selectCurDate, selectGoodsOwner, selectGoodsName, selectGoodsType);  
end;

procedure TQueryForm.ShowDetail(iFlag: integer; selectColumn,
  selectCurDate, selectGoodsOwner, selectGoodsName,
  selectGoodsType: string);
begin
//查看明细
  TDetailForm.Create(nil, iFlag, selectColumn,
  selectCurDate, selectGoodsOwner, selectGoodsName,
  selectGoodsType).ShowModal;
end;

end.