sy_ndjhqlbzd.pas

省级集邮品管理ERP

  v_d_ncts: Variant;

  procedure SetYDLS;
  var
    j: integer;
  begin
    Qry_yd.First;
    while not Qry_yd.Eof do
    begin
      vl_f_ydts := Qry_yd.FieldByName('YDTS').AsString;
      vl_f_lsts := Qry_yd.FieldByName('LSTS').AsString;
      for j := 0 to vl_sl_jhh.Count - 1 do
        with Qry_super do
        begin
          if ExistRecord('TY_JJNXQB', 'SQDWDM=''' + Qry_yd.FieldByName('DWDM').AsString +
            ''' and JHH=''' + vl_sl_jhh[j] + '''') then
          begin
            Close;
            SQL.Clear;
            SQL.Add('update TY_JJNXQB set YDTS=' + vl_f_ydts + ',LSTS=' + vl_f_lsts + ', XQZTS = ' +
              floattostr(strtofloat(vl_f_ydts) + strtofloat(vl_f_lsts)) +
              '+KFYPTS+NCTS, ZBR=''' + VG_USERNAME + ''',ZDRQ=trunc(sysdate) where SQDWDM=''' + Qry_yd.FieldByName('DWDM').AsString +
              ''' and JHH=''' + vl_sl_jhh[j] + '''');
            ExecSQL;
          end
          else
          begin
            GetCurrVal('TY_JYPYDJHB where ND=''' + SpinEdit4.Text + ''' and DWDM=''' + Qry_yd.FieldByName('DWDM').AsString + '''', 'nvl(NCTS, 0)', v_d_ncts);
            Close;
            SQL.Clear;
            SQL.Add('insert into TY_JJNXQB(JHH,SQDWDM,ND,ZT,PPMC,XQZTS,YDTS,LSTS,KFYPTS,ZBR,PZR,ZDRQ,NCTS) ' +
              'values(''' + vl_sl_jhh[j] + ''',''' + Qry_yd.FieldByName('DWDM').AsString +
              ''',''' + SpinEdit4.Text + ''',''0'',''' + vl_sl_jhh[j] + ''',' +
              floattostr(strtofloat(vl_f_ydts) + strtofloat(vl_f_lsts)) +
              ',' + vl_f_ydts + ',' + vl_f_lsts + ',0,''' + VG_USERNAME + ''','''',trunc(sysdate),' + string(v_d_ncts) + ')');
            ExecSQL;
          end;
        end;
      Qry_yd.Next;
    end;
  end;

begin
  vl_sl_jhh := TStringList.Create;
  if not P_V_isNC then
  begin
    with Qry_super do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select JHH from TG_YZPPYDLXB where YDLXDM=''' + Qry_ydlx.FieldByName('YDLXDM').AsString + '''');
      Open;
      while not EOF do
      begin
        vl_sl_jhh.Add(Fields[0].AsString);
        Next;
      end;
    end;
    SetYDLS;
  end
  else
  begin
    Qry_nc.First;
    while not Qry_nc.Eof do
    begin
      v_sql := 'update TY_JYPYDJHB set NCTS = ' + Qry_nc.FieldByName('NCTS').AsString +
        ' where ND = ''' + SpinEdit4.Text + ''' and DWDM = ''' +
        Qry_nc.FieldByName('DWDM').AsString + '''';
      AssignSQLstr(Qry_super, v_sql);
      Qry_super.ExecSQL;

      v_sql := 'update TY_JJNXQB set NCTS = ' + Qry_nc.FieldByName('NCTS').AsString +
        ', XQZTS = YDTS + LSTS + KFYPTS + ' + Qry_nc.FieldByName('NCTS').AsString +
        ',ZBR=''' + VG_USERNAME + ''',ZDRQ=trunc(sysdate) where SQDWDM=''' +
        Qry_nc.FieldByName('DWDM').AsString + ''' and ND=''' + SpinEdit4.Text + '''';
      AssignSQLstr(Qry_super, v_sql);
      Qry_super.ExecSQL;
      Qry_nc.Next;
    end;
    Qry_nc.Close;
    Qry_nc.Open;
  end;

  vl_sl_jhh.Free;
end;

procedure TFrmY_NDJHQLBZD.Act_savExecute(Sender: TObject);
var
  vl_s_year: string;
begin
  if (FDataEditState = dsBrowse) then
    exit;
  vl_s_year := SpinEdit4.Text;
  data.DM.StartTransaction;
  try
    case PgeCtl_p.ActivePageIndex of
      0:
        begin
          Qry_yd.ApplyUpdates; //预订
          CheckXQ; //check 需求信息
        end;
      1:
        begin
          CheckXQ(true);
        end;
      2:
        Qry_xq.ApplyUpdates;
    end;
    data.DM.Commit;
  except
    data.DM.Rollback;
    CHQMsgBox(vlp_s_errmsg);
  end;

  case PgeCtl_p.ActivePageIndex of
    0:
      Qry_yd.CommitUpdates;
    1:
      Qry_nc.CommitUpdates;
    2:
      Qry_xq.CommitUpdates;
  end;
  Qry_yd.EnableConstraints;

  SetDataEdit(dsBrowse);

end;

procedure TFrmY_NDJHQLBZD.DBGrid_ydlxCellClick(Column: TColumn);
var
  v_sql: string;

  procedure DoRefYDNC;
  begin
    Qry_yd.Close;
    Qry_yd.ParamByName('ND').AsString := SpinEdit4.Text;
    Qry_yd.ParamByName('YDLXDM').AsString := Qry_ydlx.FieldByName('YDLXDM').AsString;
    Qry_yd.Open;
    Qry_nc.Close;
    Qry_nc.ParamByName('ND').AsString := SpinEdit4.Text;
    Qry_nc.Open;
  end;

begin
  DoRefYDNC;
{  if Qry_ydlx.IsEmpty then
    exit;
  v_sql := 'insert into TY_JYPYDJHB(ND, DWDM, YDLXDM, YDTS, LSTS, NCTS) ' +
    'select distinct ''' + SpinEdit4.Text + ''', a.dwdm, b.ydlxdm ,0,0,0 ' +
    'from tgs_gxdwsjb a, tg_yzppydlxb b where a.DWDM not in ' +
    '(select DWDM from TY_JYPYDJHB where ND=''' + SpinEdit4.Text +
    ''' and YDLXDM=''' + Qry_ydlx.FieldByName('YDLXDM').AsString +
    ''') and a.zjxj=''1'' and b.nd=''' + SpinEdit4.Text +
    ''' and b.ydlxdm=''' + Qry_ydlx.FieldByName('YDLXDM').AsString + '''';
  AssignSQLstr(Qry_super, v_sql);
  Qry_super.ExecSQL;
  if Qry_super.RowsAffected > 0 then
  begin
    Qry_dw.Close;
    Qry_dw.Open;
    DoRefYDNC;
    CheckXQ;
  end; }
end;

procedure TFrmY_NDJHQLBZD.Qry_xqUpdateError(DataSet: TDataSet; E: EDatabaseError;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
  if pos('Key violation', e.Message) <> 0 then
    vlp_s_errmsg := '该单位的计划内需求已经存在！请重新选择申请单位！'
  else if pos('Field value required', e.Message) <> 0 then
    vlp_s_errmsg := '申请单位、制表人不能为空！请重新确定再保存！'
  else
    vlp_s_errmsg := e.Message; //'数据访问错误！';
end;

procedure TFrmY_NDJHQLBZD.Act_dwchangeExecute(Sender: TField);
begin
{  if (FDataEditState = dsBrowse) then
    exit;

  with Qry_super do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select decode(b.YDTS,null,0,b.YDTS),decode(b.LSTS,null,0,b.LSTS),' +
      'decode(b.NCTS,null,0,b.NCTS) from TF_PFXJHTAO a, TY_JYPYDJHB b, TG_YZPPYDLXB c ' +
      'where a.JHH=c.JHH and b.YDLXDM=c.YDLXDM and c.ND=''' +
      Qry_xq.FieldByName('ND').AsString + ''' and b.ND=''' +
      Qry_xq.FieldByName('ND').AsString + ''' and b.DWDM=''' +
      Sender.AsString + ''' and a.JHH=''' + Qry_p.FieldByName('JHH').AsString + '''');
    Open;

    if not IsEmpty then
    begin
      vl_i_ts := Fields[0].Value;
      if vl_i_ts = NULL then
        vl_i_ts := 0;
      Qry_xq.FieldByName('YDTS').Value := vl_i_ts;
      vl_i_ts := Fields[1].Value;
      if vl_i_ts = NULL then
        vl_i_ts := 0;
      Qry_xq.FieldByName('LSTS').Value := vl_i_ts;
      vl_i_ts := Fields[2].Value;
      if vl_i_ts = NULL then
        vl_i_ts := 0;
      Qry_xq.FieldByName('NCTS').Value := vl_i_ts;
    end else
    begin
      vl_i_ts := 0;
      Qry_xq.FieldByName('YDTS').Value := vl_i_ts;
      Qry_xq.FieldByName('LSTS').Value := vl_i_ts;
      Qry_xq.FieldByName('NCTS').Value := vl_i_ts;
    end;
  end;}
end;

procedure TFrmY_NDJHQLBZD.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  CanCLose := CheckExit;
end;

procedure TFrmY_NDJHQLBZD.Act_rtnExecute(Sender: TObject);
begin
  Close;
end;

procedure TFrmY_NDJHQLBZD.Qry_ydUpdateError(DataSet: TDataSet;
  E: EDatabaseError; UpdateKind: TUpdateKind;
  var UpdateAction: TUpdateAction);
begin
  if pos('Key violation', e.Message) <> 0 then
    vlp_s_errmsg := '该已经存在！'
  else if pos('Field value required', e.Message) <> 0 then
    vlp_s_errmsg := '预订套数、零售套数、开发用票套数' + #13 + '不能为空，请重新确定再保存！'
  else
    vlp_s_errmsg := '数据访问错误！';
end;

procedure TFrmY_NDJHQLBZD.Qry_xqKFYPTSChange(Sender: TField);
begin
  Qry_xq.FieldByName('XQZTS').Value := Qry_xq.FieldByName('YDTS').Value + Qry_xq.FieldByName('LSTS').Value + Qry_xq.FieldByName('NCTS').Value + Qry_xq.FieldByName('KFYPTS').Value;
end;

procedure TFrmY_NDJHQLBZD.FormCreate(Sender: TObject);
begin
  GetAllColWidth(self);
end;

procedure TFrmY_NDJHQLBZD.Qry_dwAfterScroll(DataSet: TDataSet);
begin
  Qry_xq.Close;
  Qry_xq.ParamByName('ND').AsString := SpinEdit4.Text;
  Qry_xq.ParamByName('SQDWDM').AsString := Qry_dw.FieldByName('DWDM').AsString;
  Qry_xq.Open;
end;

procedure TFrmY_NDJHQLBZD.Qry_xqCalcFields(DataSet: TDataSet);
begin
  with Qry_xq do
    FieldByName('zts').value := FieldByName('YDTS').value + FieldByName('LSTS').Value + FieldByName('NCTS').Value;
end;

procedure TFrmY_NDJHQLBZD.DBGrid_ydKeyPress(Sender: TObject;
  var Key: Char);
begin
  if Key = #13 then {回车键}
    if not (ActiveControl is TDBGrid) then {if not is a TDBGrid}
    begin
      Key := #0; {eat enter key}
      Perform(WM_NEXTDLGCTL, 0, 0); {move to next control}
    end
    else if (ActiveControl is TDBGrid) then {if it is a TDBGrid}
      with TDBGrid(ActiveControl) do
        if selectedindex < (fieldcount - 1) then {increment the field}
          selectedindex := selectedindex + 1
        else
          selectedindex := 0;

end;

procedure TFrmY_NDJHQLBZD.SpinEdit4Change(Sender: TObject);
begin
  Qry_ydlx.Close;
  Qry_ydlx.ParamByName('ND').AsString := SpinEdit4.Text;
  Qry_ydlx.Open;
  qry_yd.Close;
  Qry_yd.ParamByName('ND').AsString := SpinEdit4.Text;
  Qry_yd.ParamByName('YDLXDM').AsString := Qry_ydlx.FieldByName('YDLXDM').AsString;
  Qry_dw.AfterScroll := nil;
  Qry_yd.Open;
  Qry_dw.AfterScroll := Qry_dwAfterScroll;
  qry_nc.Close;
  Qry_nc.ParamByName('ND').AsString := SpinEdit4.Text;
  Qry_nc.Open;
  qry_xq.close;
  Qry_xq.ParamByName('ND').AsString := SpinEdit4.Text;
  Qry_xq.ParamByName('SQDWDM').AsString := Qry_dw.fieldByName('DWDM').AsString;
  Qry_xq.Open;

end;

procedure TFrmY_NDJHQLBZD.PgeCtl_pChanging(Sender: TObject;
  var AllowChange: Boolean);
begin
  AllowChange := FDataEditState = dsBrowse;
end;

procedure TFrmY_NDJHQLBZD.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  SaveAllColWidth(self);
end;

procedure TFrmY_NDJHQLBZD.TabSheet2Enter(Sender: TObject);
begin
  Qry_dwAfterScroll(nil);
end;

procedure TFrmY_NDJHQLBZD.Qry_ydlxAfterOpen(DataSet: TDataSet);
var
  v_sql: string;
begin
  if Qry_ydlx.IsEmpty then
    exit;
  v_sql := 'select a.YDDWSL, b.XJDWSL XJDWSL from ' +
    '(select count(distinct DWDM) YDDWSL from TY_JYPYDJHB where ND=''' +
    SpinEdit4.Text + ''') a, (select count(DWDM) XJDWSL from tgs_gxdwsjb where JYYW=''1'' and FHDX=''1'') b';
  AssignSQLstr(Qry_super, v_sql);
  Qry_super.Open;
  if Qry_super.Fields[0].AsFloat >= Qry_super.Fields[1].AsFloat then
    exit;
  v_sql := 'insert into TY_JYPYDJHB(ND, DWDM, YDLXDM, YDTS, LSTS, NCTS) ' +
    'select distinct ''' + SpinEdit4.Text + ''', a.dwdm, b.ydlxdm ,0,0,0 ' +
    'from tgs_gxdwsjb a, tg_yzppydlxb b where a.DWDM not in ' +
    '(select DWDM from TY_JYPYDJHB where ND=''' + SpinEdit4.Text +
    ''') and a.FHDX=''1'' and a.JYYW=''1'' and b.nd=''' + SpinEdit4.Text + '''';
  AssignSQLstr(Qry_super, v_sql);
  Qry_super.ExecSQL;
  v_sql := 'insert into TY_JJNXQB(JHH,SQDWDM,ND,ZT,PPMC,XQZTS,YDTS,LSTS,KFYPTS,ZBR,PZR,ZDRQ,NCTS) ' +
    'select b.jhh,a.dwdm,''' + SpinEdit4.Text + ''',''0'',c.tmc,0,0,0,0,''' + VG_UserName +
    ''','''',sysdate,0 from tgs_gxdwsjb a, tg_yzppydlxb b, tf_pfxjhtao c ' +
    'where a.DWDM not in (select SQDWDM from TY_JJNXQB where ND=''' + SpinEdit4.Text + ''') ' +
    'and a.FHDX=''1'' and a.JYYW=''1'' and b.nd=''' + SpinEdit4.Text + ''' and b.JHH=c.jHH';
  AssignSQLstr(Qry_super, v_sql);
  Qry_super.ExecSQL;
  Qry_dw.AfterScroll := nil;
  Qry_dw.Close;
  Qry_dw.Open;
  Qry_dw.AfterScroll := Qry_dwAfterScroll

end;

procedure TFrmY_NDJHQLBZD.Qry_dwAfterOpen(DataSet: TDataSet);
begin
  DBLCBo_dw.KeyValue := Qry_dw.FieldByName('DWDM').AsString;
end;

procedure TFrmY_NDJHQLBZD.DBGE_ydDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumnEh;
  State: TGridDrawState);
begin
//  if qry_initTS.AsInteger > 0 then
  if (gdSelected in State) and (TDBGridEh(Sender).Focused = True) then
    TDBGridEh(Sender).Canvas.Font.Color := clWhite
  else
    TDBGridEh(Sender).Canvas.Font.Color := clBlack; //$00A56E3A;

  TDBGridEh(Sender).DefaultDrawColumnCell(Rect, DataCol, Column, State);

end;

procedure TFrmY_NDJHQLBZD.DBGE_ydGetFooterParams(Sender: TObject;
  DataCol, Row: Integer; Column: TColumnEh; AFont: TFont;
  var Background: TColor; var Alignment: TAlignment; State: TGridDrawState;
  var Text: string);
begin
  try
    if (UpperCase(Column.FieldName) = 'YDTS') or
      (UpperCase(Column.FieldName) = 'LSTS') or
      (UpperCase(Column.FieldName) = 'NCTS') or
      (UpperCase(Column.FieldName) = 'XQZTS') or
      (UpperCase(Column.FieldName) = 'KFYPTS') then
      if Text = '' then
        Text := '0'
      else
        Text := FormatFloat('#,##', StrToFloat(Text))
  except
  end;

  if (UpperCase(Column.FieldName) = 'DWMC') or
    (UpperCase(Column.FieldName) = 'TMC') then
    Text := '合 计:  ' + Text;

end;

end.