pawnquery.pas

一个典当行的业务管理系统程序

          else
              ckOutpawn.Checked :=True;
     end;
end;
//绝当选择
procedure TfrmPawnQuery.ckNopawnKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
     if key=VK_RETURN then
     begin
          if ckNopawn.Checked=True then
             ckNopawn.Checked :=False
          else
              ckNopawn.Checked :=True;
     end;
end;
//检索
procedure TfrmPawnQuery.cmdQueryClick(Sender: TObject);
var
   Sql:string;
   SqlRansom:string;
   PawnCodeMark,IDMark,NameMark,BeginDateMark,EndDateMark:Boolean;
   BeginCreateDate,EndCreateDate:TDateTime;
   PawnCondition:string;
   Year,Month,Day:word;
   NMonth,NYear,NDay:word;
   NowDate:TDateTime;
   DaysElapsed:TDateTime;
   NowToTen:TDateTime;
   TotalMoney:Double;
begin
     self.staQuery.Caption:='';
     //检索的时候将dgDetail网格的DataSource属性设为只读的Query数据源
     //当删除时，它会被设为允许修改和删除的数据源dsDetailDel
     dgDetail.DataSource :=dmPawnFrm.dsQueryDetailUn ;
     //dgHead.DataSource:=dmPawnFrm.dsHeadUn;
     //--- 清空细表网格
     dmPawnFrm.dqDetailUn.Close;

     //若是查询赎回数据
     SqlRansom:= 'select h.PawnTicketCode,h.PawnerName,h.CertificateName,h.CertifiCateCode,';
     SqlRansom:=SqlRansom+ 'h.TelephoneCode,h.Linkman,h.PawnerAddress,h.PostCode,';
     SqlRansom:=SqlRansom+ 'h.MonthRate,h.FeeRate,h.PawnBeginDate,h.PawnEndDate,';
     SqlRansom:=SqlRansom+'h.TotalPawnMoney,h.SynthesizeFee,h.RealPayMoney,R.RansomDate,';
     SqlRansom:=SqlRansom+'R.RansomMoney,R.ServiceMoney,R.TotalPay,h.MonthRateFee from PawnHead_Tbl as h ';
     SqlRansom:=SqlRansom+'INNER JOIN RansomMoney_Tbl AS R ON h.PawnTicketCode=R.PawnTicketCode ';
     SqlRansom:=SqlRansom+'where True ';
    //若是查询的不是赎回数据
     Sql:='select h.PawnTicketCode,h.PawnerName,h.CertificateName,h.CertifiCateCode,';
     Sql:=Sql+'h.TelephoneCode,h.Linkman,h.PawnerAddress,h.PostCode,';
     Sql:=Sql+'h.MonthRate,h.FeeRate,h.PawnBeginDate,h.PawnEndDate,';
     Sql:=Sql+'h.TotalPawnMoney,h.SynthesizeFee,h.RealPayMoney from PawnHead_Tbl as h ';
     Sql:=Sql+'INNER JOIN Stockpile_Tbl AS s ON h.PawnTicketCode=s.PawnCode ';
     Sql:=Sql+'where True ';//and h.Status<>:Status ';
     //++++++++++++++++++++++++++++
     DecodeDate(Now,Year,Month,Day);
     NowDate:=EncodeDate(Year,Month,Day);
     DaysElapsed:=NowDate-10;
     DecodeDate(DaysElapsed,NYear,NMonth,NDay);
     NowToTen:=EncodeDate(NYear,NMonth,NDay);
     //+++++++++++++++++++++++++++++++++
     if txtPawnCode.Text<>''then
     begin
          PawnCodeMark:=True;
          Sql:=Sql+'and h.PawnTicketCode=:PawnCode ';
          SqlRansom:=SqlRansom+ 'and h.PawnTicketCode=:PawnCode ';
     end;
     if txtID.Text<>'' then
     begin
          IDMark:=True;
          Sql:=Sql+'and h.CertificateCode=:IDCode ';
          SqlRansom:=SqlRansom+ 'and h.CertificateCode=:IDCode ';
     end;
     if txtPawnerName.Text <>''then
     begin
          NameMark:=True;
          Sql:=Sql+'and h.PawnerName=:Name ';
          SqlRansom:=SqlRansom+ 'and h.PawnerName=:Name ';
     end;
     //取出起始时间
     BeginCreateDate:=EncodeDate(txtBeginYear.Value,txtBeginMonth.Value,
                                    txtBeginDay.Value);
     BeginDateMark:=True;
     {if txtBeginYear.Text<>'' then
     begin
          if txtBeginMonth.Text <>'' then  //年不为空，月不为空
          begin
               if txtBeginDay.Text <>''then  //年月日都不为空
               begin
                    BeginCreateDate:=EncodeDate(strtoint(trim(txtBeginYear.text)),
                                    strtoint(trim(txtBeginMonth.text)),
                                    strtoint(trim(txtBeginDay.text)));
                    BeginDateMark:=True;
               end
               else  //起始日期为空，则为此月的第一天1号
               begin
                    BeginCreateDate:=EncodeDate(strtoint(trim(txtBeginYear.text)),
                                    strtoint(trim(txtBeginMonth.text)),1);
                    BeginDateMark:=True;
               end;
          end
          else  //起始月份为空，年不为空
          begin
               if txtBeginDay.Text<>''then //年日不为空，则提示月必须不为空
               begin
                    messagedlg(Msg.MsgBeginMonthInfro,mtWarning,[mbYes],0);
                    txtBeginMonth.SetFocus ;
               end
               else  //年不为空，月日为空则为1月1日
               begin
                     BeginCreateDate:=EncodeDate(strtoint(trim(txtBeginYear.text)),1,1);
                     BeginDateMark:=True;
               end;
          end;
     end
     else  //若年为空，则继续判断月日是否为空
     begin
          if txtBeginMonth.Text<>''then
          begin  //若年为空，月不为空则提示必须输入年分。
                  messagedlg(Msg.MsgBeginYearInfo ,mtWarning,[mbYes],0);
                  txtBeginYear.SetFocus ;
          end
          else //若年为空，月也为空
          begin
               if txtBeginDay.Text<>''then
               begin  //若年月为空，日不为空则提示必须输入年月
                      messagedlg(Msg.MsgBeginMYInfo,mtWarning,[mbYes],0);
                      txtBeginYear.SetFocus ;
               end
               else  //若年月日均为空
                     BeginDateMark:=False;
          end;
     end;}
     //取出终到时间
     EndCreateDate:=EncodeDate(txtEndYear.Value,txtEndMonth.Value,txtEndDay.Value);
     EndDateMark:=True;
     {if txtEndYear.Text<>'' then
     begin
          if txtEndMonth.Text <>'' then  //年不为空，月不为空
          begin
               if txtEndDay.Text <>''then  //年月日都不为空
               begin
                    EndCreateDate:=EncodeDate(strtoint(trim(txtEndYear.text)),
                                    strtoint(trim(txtEndMonth.text)),
                                    strtoint(trim(txtEndDay.text)));
                    EndDateMark:=True;
               end
               else  //终到日期为空，则为此月的第一天1号
               begin
                    EndCreateDate:=EncodeDate(strtoint(trim(txtEndYear.text)),
                                    strtoint(trim(txtEndMonth.text)),1);
                    EndDateMark:=True;
               end;
          end
          else  //终到月份为空，年不为空
          begin
               if txtEndDay.Text<>''then //年日不为空，则提示月必须不为空
               begin
                    messagedlg(Msg.MsgEndMonthInfro,mtWarning,[mbYes],0);
                    txtEndMonth.SetFocus ;
                    exit;
               end
               else  //年不为空，月日为空则为1月1日
               begin
                    EndCreateDate:=EncodeDate(strtoint(trim(txtEndYear.text)),1,1);
                    EndDateMark:=True;
               end;
          end;
     end
     else //若年为空，则继续判断月日是否为空
     begin
          if txtEndMonth.Text<>''then
          begin  //若年为空，月不为空则提示必须输入年分。
                 messagedlg(Msg.MsgEndYearInfo ,mtWarning,[mbYes],0);
                 txtEndYear.SetFocus ;
                 exit;
          end
          else //若年为空，月也为空
          begin
               if txtEndDay.Text<>''then
               begin  //若年月为空，日不为空则提示必须输入年月
                      messagedlg(Msg.MsgEndMYInfo,mtWarning,[mbYes],0);
                      txtEndYear.SetFocus ;
                      exit;
               end
               else  //若年月日均为空
                     EndDateMark:=False;
          end;
     end;    }
     if BeginDateMark=False then  //若起始时间不存在，则查看是否有终到日期，如无终到日期则查询时不必看时间
     begin                       //如有终到日期，则提示必须输入起始日期
          if EnddateMark=True then
          begin
               messagedlg(Msg.MsgBeginDateInfo,mtWarning,[mbYes],0);
               txtBeginYear.SetFocus ;
          end;
     end;
     //写入Sql
     if BeginDateMark=True then
     begin  //有起始日期
          if EndDateMark=True then
          begin
             Sql:=Sql+'and h.CreateDate>=:BeginCreateDate and h.CreateDate<=:EndCreateDate ';
             SqlRansom:=SqlRansom+'and h.CreateDate>=:BeginCreateDate and h.CreateDate<=:EndCreateDate ';
          end
          else
          begin
             Sql:=Sql+'and h.CreateDate>=:BeginCreateDate ';
             SqlRansom:=SqlRansom+'and h.CreateDate>=:BeginCreateDate ';
          end;
     end;
     //判断是否有在当、逾期、绝当
     PawnCondition:='False';
     if ckOnpawn.Checked =True then   //若是在当
     begin
          PawnCondition := PawnCondition + ' or h.PawnEndDate>:Today and h.Status<>:Status1 ';
          PawnCondition:=PawnCondition+' and h.Status<>:Status2 ';
     end;
     if ckOutpawn.Checked =True then   //若是逾期 outtime=today-10
     begin
          PawnCondition := PawnCondition + ' or h.PawnEndDate<:Today ';//and h.PawnEndDate>:OutTime ';
          PawnCondition:=PawnCondition+' and h.Status<>:Status1 ';
          PawnCondition:=PawnCondition+' and h.Status<>:Status2 ';
     end;
     if ckNopawn.Checked =True then  //若是绝当
          PawnCondition := PawnCondition + ' or h.Status=:Status ';
     if ckRansom.Checked=True then   //若是赎回
        SqlRansom:=SqlRansom+' and h.Status=''赎回'' ';
     if pawncondition<>'False' then
        Sql := Sql+ 'and (' + PawnCondition +')';

     if ckRansom.Checked=False then
     begin  //不是赎回
            dgHRansom.Visible:=False;
          with dmPawnFrm do
          begin
               dqHeadUn.Close;
               dqHeadUn.SQL.Clear ;
               dqHeadUn.SQL.Add (Sql);
               //dqHeadUn.ParamByName('Status').asstring:='处理';
               if PawnCodeMark=True then
               begin
                    PawnCodeMark:=False;
                    dqHeadUn.ParamByName('PawnCode').asstring:=trim(txtPawnCode.text);
               end;
              if IDMark=True then
              begin
                   IDMark:=False;
                   dqHeadUn.ParamByName('IDCode').asstring:=trim(txtID.text);
              end;
              if NameMark=True then
              begin
                   NameMark:=False;
                   dqHeadUn.ParamByName('Name').asstring:=trim(txtPawnerName.text);
              end;
              if BeginDateMark=True then
              begin
                   BeginDateMark:=False;
                   if EndDateMark=True then
                   begin //若起始、终到日期均有则取出起始、终到日期
                         EndDateMark:=False;
                         dqHeadUn.ParamByName ('BeginCreateDate').asDateTime:=BeginCreateDate;
                         dqHeadUn.ParamByName ('EndCreateDate').asDateTime:=EndCreateDate;
                   end
                   else  //若只有起始日期，则只取出起始日期
                         dqHeadUn.ParamByName ('BeginCreateDate').AsDateTime:=BeginCreateDate;
              end;
              if ckOnpawn.Checked =True then
              begin
                   dqHeadUn.ParamByName ('Today').AsDateTime:=NowDate;//EncodeDate(Year,Month,Day);
                   dqHeadUn.ParamByName('Status1').asstring:='处理';
                   dqHeadUn.ParamByName('Status2').asstring:='绝当';
              end;
              if ckOutpawn.Checked =True then
              begin
                   dqHeadUn.ParamByName ('Today').AsDateTime:=NowDate;//EncodeDate(Year,Month,Day);
                   //dqHeadUn.ParamByName ('OutTime').AsDateTime:=NowToTen; //EncodeDate(Year,Month,Day)-Pawn.OutPawnLimit;
                   dqHeadUn.ParamByName('Status1').asstring:='处理';
                   dqHeadUn.ParamByName('Status2').asstring:='绝当';
              end;
              if ckNopawn.Checked=True then
              begin
                   //dqHeadUn.ParamByName ('OutTime').AsDateTime:=NowToTen; //EncodeDate(Year,Month,Day)-Pawn.OutPawnLimit;
                   dqHeadUn.ParamByName ('Status').Asstring:='绝当';
              end;
              dqHeadUn.Open;
              staRecNum.Caption:='共'+inttostr(dqHeadUn.RecordCount)+'张当票';
              DataSetHeadOpenMark:=True;
          end;
     end
     else     //如果是赎回
     begin
          //将赎回数据表格显示出来
          dgHRansom.Visible:=True;
          with dmPawnFrm do
          begin
               dqHeadUn.Close;
               dqHeadUn.SQL.Clear ;
               dqHeadUn.SQL.Add (SqlRansom);
               //dqHeadUn.ParamByName('Status').asstring:='处理';
               if PawnCodeMark=True then
               begin
                    PawnCodeMark:=False;
                    dqHeadUn.ParamByName('PawnCode').asstring:=trim(txtPawnCode.text);
               end;
              if IDMark=True then
              begin
                   IDMark:=False;
                   dqHeadUn.ParamByName('IDCode').asstring:=trim(txtID.text);
              end;
              if NameMark=True then
              begin
                   NameMark:=False;
                   dqHeadUn.ParamByName('Name').asstring:=trim(txtPawnerName.text);
              end;
              if BeginDateMark=True then
              begin
                   BeginDateMark:=False;
                   if EndDateMark=True then
                   begin //若起始、终到日期均有则取出起始、终到日期
                         EndDateMark:=False;
                         dqHeadUn.ParamByName ('BeginCreateDate').asDateTime:=BeginCreateDate;
                         dqHeadUn.ParamByName ('EndCreateDate').asDateTime:=EndCreateDate;
                   end
                   else  //若只有起始日期，则只取出起始日期
                         dqHeadUn.ParamByName ('BeginCreateDate').AsDateTime:=BeginCreateDate;
              end;
              {if ckOnpawn.Checked =True then
              begin
                   dqHeadUn.ParamByName ('Today').AsDateTime:=NowDate;//EncodeDate(Year,Month,Day);
                   dqHeadUn.ParamByName('Status1').asstring:='处理';
                   dqHeadUn.ParamByName('Status2').asstring:='绝当';
              end;
              if ckOutpawn.Checked =True then
              begin
                   dqHeadUn.ParamByName ('Today').AsDateTime:=NowDate;//EncodeDate(Year,Month,Day);
                   dqHeadUn.ParamByName ('OutTime').AsDateTime:=NowToTen; //EncodeDate(Year,Month,Day)-Pawn.OutPawnLimit;