sy_jypxxwh.pas

省级集邮品管理ERP

  with Qry_tao do
  begin
    Close;
    SQL.Text := 'select a.TDM,a.YPLB,a.TZ,a.ZH,a.MC,a.DW,a.CB,a.ZK,a.SJ,a.ZZDW,a.FXDW,a.FXRQ,a.FXL,a.TC,a.BZ,a.SDATE,b.MC,c.MC, d.SMC FXDWMC'#13#10 +
      'from TB_JYPTXXB a, TG_JYPLDMB b, TG_YZPPTZDM c, TG_SDMB d'#13#10 +
      'where a.YPLB=b.JYPLDM and a.TZ=c.TZDM(+) and a.FXDW=d.SDM(+)'#13#10 +
      'and a.fxrq is null' +
      ' order by a.TDM';
    Open;
  end;
  SpinEdit1.Enabled := False;
end;

procedure TFrm_jyp.rb_NdClick(Sender: TObject);
begin
  SpinEdit1.Enabled := True;
  SpinEdit1Change(Sender);
end;

procedure TFrm_jyp.SpinEdit1Change(Sender: TObject);
begin
  if Trim(spinEdit1.Text) = '' then Exit;
  if (spinEdit1.value < 1900) then exit;
  with Qry_tao do
  begin
    Close;
    SQL.Text := 'select a.TDM,a.YPLB,a.TZ,a.ZH,a.MC,a.DW,a.CB,a.ZK,a.SJ,a.ZZDW,a.FXDW,a.FXRQ,a.FXL,a.TC,a.BZ,a.SDATE,b.MC,c.MC, d.SMC FXDWMC'#13#10 +
      'from TB_JYPTXXB a, TG_JYPLDMB b, TG_YZPPTZDM c, TG_SDMB d'#13#10 +
      'where a.YPLB=b.JYPLDM and a.TZ=c.TZDM(+) and a.FXDW=d.SDM(+)'#13#10 +
      'and to_char(a.fxrq,''YYYY'')=''' + SpinEdit1.Text + '''' +
      ' order by a.TDM';
    Open;
  end;
end;


{是否显示图案}
procedure TFrm_jyp.CBo_taoClick(Sender: TObject);
  function GetTempDirectory: string;
  var
    TempDir: array[0..255] of Char;
  begin
    GetTempPath(255, @TempDir);
    Result := StrPas(TempDir);
  end;

var
  Dot_Stream: TFileStream;
  Query_String, S: string;
  Target_Name: string;
  Tmp_Path: string;
begin
  if not CBO_tao.Checked then
  begin
    Image1.picture.LoadFromFile('');
    Image2.Picture.LoadFromFile('');
    Exit;
  end;

  if CBo_tao.Checked then
  begin
    Image1.Visible := True;
    Image2.Visible := True;
  end;

  if VLp_FocusGrid.Tag = 0 then
    Query_String := 'Select TA From TB_JYPTTAXXB Where TDM=''' + Qry_tao.FieldByName('TDM').AsString + '''';
  if VLp_FocusGrid.Tag = 1 then
    Query_String := 'Select TA From TB_JYPMTAXXB Where TDM=''' + Qry_mei.FieldByName('TDM').AsString + ''' and MDM=''' + Qry_mei.FieldByName('MDM').AsString + '''';

  with Qry_TA do
  begin
    Close;
    SQL.Clear;
    SQL.Text := Query_String;
    Open;
  end;
  if Qry_TA.IsEmpty then
  begin
    Image1.picture.LoadFromFile('');
    Image2.Picture.LoadFromFile('');
    Exit;
  end;

  Tmp_Path := GetTempDirectory;

  //设置创建的文件类型
  S := string(TBlobField(Qry_TA.fieldByName('TA')).asvariant);
  if copy(S, 1, 2) = 'BM' then
  begin
    Target_Name := Tmp_Path + '\FileName.bmp';
  end
  else
  begin
    Target_Name := Tmp_Path + '\FileName.jpg';
  end;

  Dot_Stream := TFileStream.Create(Target_Name, fmOpenWrite or fmCreate);
  Dot_Stream.Position := 0;
  TBlobField(Qry_TA.FieldByName('TA')).SaveToStream(Dot_Stream);
  Dot_Stream.Free;


  Qry_TA.Close;

  if VLp_FocusGrid.Tag = 0 then
  begin
    image1.Stretch := false;
    image1.Picture.LoadFromFile(Target_name);
    Image1.BringToFront;
  end;

  if VLp_FocusGrid.Tag = 1 then
  begin
    image2.Stretch := false;
    image2.Picture.LoadFromFile(Target_name);
    Image2.BringToFront;
  end;
end;

procedure TFrm_jyp.Qry_taoAfterScroll(DataSet: TDataSet);
begin
  CBo_taoClick(nil);
  DBLCbox_DL.KeyValue := copy(Qry_tao.FieldByName('YPLB').AsString, 1, 1);
  DBLCbox_DLCloseUp(nil);
  CB_jkp.Checked := length(Qry_tao.FieldByName('TDM').AsString) = 13;
  CB_jkpClick(nil);
end;

procedure TFrm_jyp.Qry_meiAfterScroll(DataSet: TDataSet);
begin
  CBo_taoClick(nil);
end;

procedure TFrm_jyp.Act_sendExecute(Sender: TObject);
var
  i: integer;
  vl_sl_jsdw: TStringList;
  vl_s_jsdw: string;
  v_b_ed: boolean;
begin
  if (DBGid_tao.DataSource.DataSet.IsEmpty) then
  begin
    CHQMsgBox('当前没有可发送的记录。');
    exit;
  end;

  v_b_ed := false;
  if (DBGid_tao.DataSource.DataSet.FieldByName('SDATE').AsString <> '') then
    if CHQMsgBox('邮品 ' + DBGid_tao.DataSource.DataSet.FieldByName('MC').AsString + ' 的信息发送过,确定还要发送吗？', 2) = IDNo then
      exit
    else
      v_b_ed := true;

  if not v_b_ed then
    if CHQMsgBox('确实要发送这些邮品信息吗？', 2) = IDNo then
      exit;

  vl_sl_jsdw := TStringList.Create;
  vlp_stao_tdm := DBGid_tao.DataSource.DataSet.fieldbyname('TDM').AsString;

  with Qry_super do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select jsdwdm from tg_xttxfsgnb where csdm=''sf_ypbase''');
    Open;

    vl_s_jsdw := trim(Fields[0].AsString);
    if (length(vl_s_jsdw) = 0) then //((length(vl_s_bdjsdw) = 0) and )
    begin
      CHQMsgBox('请确定接收单位设置无误再发送！');
      exit;
    end;
    vl_sl_jsdw.Clear;

//**************************设置接收单位列表************************************
    while pos(',', vl_s_jsdw) > 0 do
    begin
      vl_sl_jsdw.Add(copy(vl_s_jsdw, 1, pos(',', vl_s_jsdw) - 1));
      vl_s_jsdw := copy(vl_s_jsdw, pos(',', vl_s_jsdw) + 1, length(vl_s_jsdw));
    end;
    vl_sl_jsdw.Add(vl_s_jsdw);

  end;

  data.DM.StartTransaction;
  try
    Qry_super.Close;
    Qry_super.SQL.Clear;
    Qry_super.SQL.Add('update TB_JYPTXXB set SDATE = (select trunc(sysdate,''DD'') from dual) where TDM = ''' + Qry_tao.FieldByName('TDM').AsString + '''');
    Qry_super.ExecSQL;
    Qry_send.Close;
    Qry_send.SQL.Clear;
    Qry_send.SQL.Add('select TDM,YPLB,TZ,ZH,MC,DW,CB,ZK,SJ,ZZDW,FXDW,FXRQ,FXL,TC,BZ,SDATE,COMM_DEPT from TB_JYPTXXB_CACO');
    Qry_send.Open;

    Qry_send1.Close;
    Qry_send1.SQL.Clear;
    Qry_send1.SQL.Add('select TDM,MDM,ZH,MC,DW,CB,ZK,SJ,YPMZ,YPMS,ZZDW,FXDW,FXRQ,FXL,YCSJZ,JYPSJZ,JYPSYZ,BZ,SDATE,COMM_DEPT from TB_JYPMXXB_CACO');
    Qry_send1.Open;

    for i := 0 to vl_sl_jsdw.Count - 1 do
    begin
      Qry_send.Append;
      Qry_send.FieldByName('TDM').Assign(Qry_tao.FieldByName('TDM'));
      Qry_send.FieldByName('YPLB').Assign(Qry_tao.FieldByName('YPLB'));
      Qry_send.FieldByName('TZ').Assign(Qry_tao.FieldByName('TZ'));
      Qry_send.FieldByName('ZH').Assign(Qry_tao.FieldByName('ZH'));
      Qry_send.FieldByName('MC').Assign(Qry_tao.FieldByName('MC'));
      Qry_send.FieldByName('DW').Assign(Qry_tao.FieldByName('DW'));
      Qry_send.FieldByName('CB').Assign(Qry_tao.FieldByName('CB'));
      Qry_send.FieldByName('ZK').Assign(Qry_tao.FieldByName('ZK'));
      Qry_send.FieldByName('SJ').Assign(Qry_tao.FieldByName('SJ'));
      Qry_send.FieldByName('ZZDW').Assign(Qry_tao.FieldByName('ZZDW'));
      Qry_send.FieldByName('FXDW').Assign(Qry_tao.FieldByName('FXDW'));
      Qry_send.FieldByName('FXRQ').Assign(Qry_tao.FieldByName('FXRQ'));
      Qry_send.FieldByName('FXL').Assign(Qry_tao.FieldByName('FXL'));
      Qry_send.FieldByName('TC').Assign(Qry_tao.FieldByName('TC'));
      Qry_send.FieldByName('BZ').Assign(Qry_tao.FieldByName('BZ'));
//      Qry_send.FieldByName('TA').Assign(Qry_tao.FieldByName('TA'));
      Qry_send.FieldByName('COMM_DEPT').AsString := vl_sl_jsdw.Strings[i];
      Qry_Send.Post;

      Qry_mei.First;
      while not Qry_mei.Eof do
      begin
        Qry_send1.Append;
        Qry_send1.FieldByName('TDM').Assign(Qry_mei.FieldByName('TDM'));
        Qry_send1.FieldByName('MDM').Assign(Qry_mei.FieldByName('MDM'));
        Qry_send1.FieldByName('ZH').Assign(Qry_mei.FieldByName('ZH'));
        Qry_send1.FieldByName('MC').Assign(Qry_mei.FieldByName('MC'));
        Qry_send1.FieldByName('DW').Assign(Qry_mei.FieldByName('DW'));
        Qry_send1.FieldByName('CB').Assign(Qry_mei.FieldByName('CB'));
        Qry_send1.FieldByName('ZK').Assign(Qry_mei.FieldByName('ZK'));
        Qry_send1.FieldByName('SJ').Assign(Qry_mei.FieldByName('SJ'));
        Qry_send1.FieldByName('YPMZ').Assign(Qry_mei.FieldByName('YPMZ'));
        Qry_send1.FieldByName('YPMS').Assign(Qry_mei.FieldByName('YPMS'));
        Qry_send1.FieldByName('ZZDW').Assign(Qry_mei.FieldByName('ZZDW'));
        Qry_send1.FieldByName('FXDW').Assign(Qry_mei.FieldByName('FXDW'));
        Qry_send1.FieldByName('FXRQ').Assign(Qry_mei.FieldByName('FXRQ'));
        Qry_send1.FieldByName('FXL').Assign(Qry_mei.FieldByName('FXL'));
        Qry_send1.FieldByName('YCSJZ').Assign(Qry_mei.FieldByName('YCSJZ'));
        Qry_send1.FieldByName('JYPSJZ').Assign(Qry_mei.FieldByName('JYPSJZ'));
        Qry_send1.FieldByName('JYPSYZ').Assign(Qry_mei.FieldByName('JYPSYZ'));
        Qry_send1.FieldByName('BZ').Assign(Qry_mei.FieldByName('BZ'));
//        Qry_send1.FieldByName('TA').Assign(Qry_mei.FieldByName('TA'));
        Qry_send1.FieldByName('COMM_DEPT').AsString := vl_sl_jsdw.Strings[i];
        Qry_send1.Post;
        Qry_mei.Next;
      end;
    end;

    Qry_send.ApplyUpdates;
    Qry_send1.ApplyUpdates;
    data.DM.Commit;
    CHQMsgBox('邮品信息发送成功！');
  except
    data.DM.RollBack;
    CHQMsgBox('发送失败：数据访问错误！');
  end;
  vl_sl_jsdw.Destroy;
  DBGid_tao.DataSource.DataSet.Close;
  DBGid_tao.DataSource.DataSet.Open;
  DBGid_tao.DataSource.DataSet.Locate('TDM', vlp_stao_tdm, []);
end;

{票品类别改变，志号随着改变}
procedure TFrm_jyp.DBLookupComboBox1Click(Sender: TObject);
begin
  Qry_tao.FieldByName('MC_1').AsString := DBLookupComboBox1.Text;
  Qry_tao.FieldByName('ZH').AsString := Qry_pplb.FieldByName('BH').AsString;
  DoGenTDM;
end;

procedure TFrm_jyp.DBLookupComboBox2Click(Sender: TObject);
begin
  Qry_tao.FieldByName('MC_2').AsString := DBLookupComboBox2.Text;
  DoGenTDM;
end;

procedure TFrm_jyp.DBLCbox_DLCloseUp(Sender: TObject);
begin
  Qry_pplb.Close;
  Qry_pplb.Params[0].AsString := DBLCbox_DL.keyValue;
  Qry_pplb.Open;
end;

procedure TFrm_jyp.CB_jkpClick(Sender: TObject);
begin
//  if VLp_status in [1, 3] then
//  begin
  Qry_fxdw.Close;
  if CB_jkp.Checked then
    Qry_fxdw.Params[0].AsString := '1'
  else
    Qry_fxdw.Params[0].AsString := '0';
  Qry_fxdw.Open;
  if VLp_status = 1 then
    DoGenTDM;
//  end;
end;

procedure TFrm_jyp.DoGenTDM;
var
  v_s_TDM: string;
  function UPDPPDM(AFieldName, ADefVal: string): string;
  begin
    Result := '';
    with Qry_tao do
    begin
      if FieldByName(AFieldName).AsString = '' then
        Result := ADefVal
      else
        Result := FieldByName(AFieldName).AsString;
    end;
  end;

  function GetTDMLSH(AJGLBNF: string): string;
  begin
    Result := '';
    AssignSqlStr(Query1, 'select max(TDM) from TB_JYPTXXB where TDM like ''' + AJGLBNF + '%'' ');
    Query1.Open;
    if Query1.IsEmpty then
      Result := AJGLBNF + '01'
    else
    begin
      if Query1.Fields[0].AsString = '' then
        Result := AJGLBNF + '01'
      else
      begin
        if not CB_jkp.Checked then
          Result := AJGLBNF + formatfloat('00', strtoint(copy(Query1.Fields[0].AsString, 10, 2)) + 1)
        else
          Result := AJGLBNF + formatfloat('00', strtoint(copy(Query1.Fields[0].AsString, 8, 2)) + 1);
      end;
    end;
  end;
begin
  if not CB_jkp.Checked then
    v_s_TDM := UPDPPDM('FXDW', '00') + UPDPPDM('YPLB', '000') + SpinEdit1.Text
  else
    v_s_TDM := UPDPPDM('FXDW', '000') + SpinEdit1.Text;
  Qry_tao.FieldByName('TDM').AsString := GetTDMLSH(v_s_TDM) + UPDPPDM('TZ', '00') + '00';
end;

procedure TFrm_jyp.DBLookupComboBox3Click(Sender: TObject);
begin
  DoGenTDM
end;

procedure TFrm_jyp.bbtSaveAsClick(Sender: TObject);
var
  vsTemp: string;
begin
  with DBGid_tao.DataSource.DataSet do
    if (not IsEmpty) and Active then
    begin
      vsTemp := '''' + FieldByName('tdm').AsString + '''';
      DcJyp(vsTemp);
    end;
end;

procedure TFrm_jyp.DcJyp(tdms: string);
const
  csIntJypTao: string
    = ' insert into TB_JYPTXXB_CACI (TDM,YPLB,TZ,ZH,MC,DW,CB,ZK,SJ,ZZDW,FXDW,FXRQ,FXL,TC,BZ) '
    + '   values(''%s'',''%s'',''%s'',''%s'',''%s'',''%s'',%f,%f,%f,''%s'',''%s'',to_date(''%s'',''YYYY-MM-DD''),%d,''%s'',''%s'') ';
  csIntJypmxxb: string
    = ' insert into TB_JYPMXXB_CACI (TDM,MDM,ZH,MC,DW,CB,ZK,SJ,YPMZ,YPMS,ZZDW,FXDW,FXRQ,FXL,YCSJZ,JYPSJZ,JYPSYZ,BZ) '
    + '   values(''%s'',''%s'',''%s'',''%s'',''%s'',%f,%f,%f,%f,%d,''%s'',''%s'', to_date(''%s'',''YYYY-MM-DD''), '
    + '   %d,''%s'',''%s'',''%s'',''%s'') ';
  csGetJypTao: string
    = ' select * from TB_JYPTXXB where tdm in (%s) ';
  csGetJypmxxb: string
    = ' select * from TB_JYPMXXB where tdm in (%s) ';
var
  vsl: TStringList;
  vsTemp: string;
begin
  vsl := TStringList.Create;
  with TQuery.Create(Self) do
  try
    DataBaseName := 'yzpp';

    vsTemp := format(csGetJypTao, [tdms]);
    Close;
    Sql.Clear;
    Sql.Add(vsTemp);
    Open;

    First;
    while not Eof do
    begin
      vsTemp := format(csIntJypTao, [FieldByName('TDM').AsString, FieldByName('YPLB').AsString, FieldByName('TZ').AsString, FieldByName('ZH').AsString, FieldByName('MC').AsString,
        FieldByName('DW').AsString, FieldByName('CB').AsFloat, FieldByName('ZK').AsFloat, FieldByName('SJ').AsFloat, FieldByName('ZZDW').AsString,
          FieldByName('FXDW').AsString, FieldByName('FXRQ').AsString, FieldByName('FXL').AsInteger, FieldByName('TC').AsString, FieldByName('BZ').AsString]);
      vsl.Add(vsTemp);
      Next;
    end;

    vsTemp := format(csGetJypmxxb, [tdms]);
    Close;
    Sql.Clear;
    Sql.Add(vsTemp);
    Open;

    First;
    while not Eof do
    begin
      vsTemp := format(csIntJypmxxb, [FieldByName('TDM').AsString, FieldByName('MDM').AsString, FieldByName('ZH').AsString, FieldByName('MC').AsString, FieldByName('DW').AsString,
        FieldByName('CB').AsFloat, FieldByName('ZK').AsFloat, FieldByName('SJ').AsFloat, FieldByName('YPMZ').AsFloat, FieldByName('YPMS').AsInteger,
          FieldByName('ZZDW').AsString, FieldByName('FXDW').AsString, FieldByName('FXRQ').AsString, FieldByName('FXL').AsInteger, FieldByName('YCSJZ').AsString,
          FieldByName('JYPSJZ').AsString, FieldByName('JYPSYZ').AsString, FieldByName('BZ').AsString]);
      vsl.Add(vsTemp);
      Next;
    end;
    sdlData.title := '导出数据';
    //判断对话框是否为确定
    if sdlData.Execute then
    begin
      vsl.SaveToFile(sdlData.FileName);
      CHQMsgBox('导出数据成功!')
    end;
  finally
    Free;
    vsl.Free;
  end;
end;

end.