u_dbtread.pas

数据导入导出

                  end;

              temp1:=dbtrans.tosqlserver.FieldValues['FIDTCS'];   //将TCS资金表中最大的FID号赋给TEMP1变量
              temp2:=dbtrans.fromaccessquery_2.FieldValues['ID']; //同时获得RC资金表此字段的ID号,赋TEMP2
               //showmessage(temp1+','+temp2);
           //---------------------------------------------------------------------------//

             with dbtrans.fromaccessquery_3 do                //用数据集3查询RC行为表中FID字段为TEMP2的记录
                 begin
                   close;
                   sql.Clear;
                   sql.Add('select tbrentcd.code,tbrtransaction.day1,tbrtransaction.FId from tbrtransaction,tbrentcd where tbrtransaction.cid1=tbrentcd.id and tbrtransaction.FId='+''+temp2+'');
                   open;
                   FIRST;
                 end;

             for j:=0 to dbtrans.fromaccessquery_3.RecordCount do     //统计记录个数,循环插入新记录到TCS的行为表中
                begin
                      if j=dbtrans.fromaccessquery_3.RecordCount then break;
                      with dbtrans.tosqlserver do
                          begin
                          close;
                          sql.Clear;
                          sql.Add('insert into rttransaction(出租日期,会员编号,影片编号,FID) values('+''''+datetostr(dbtrans.fromaccessquery_3.FieldValues['day1'])+''''
                          +','+''''+'零租用户'+''''+','+''''+dbtrans.fromaccessquery_3.FieldValues['code']+''''+','+''''+temp1+''''+')'); //逾期用户归还日期不导入)
                          execsql;                                                                              //这里FID导入为TEMP1的值,这样TCS的资金表和行为表就通过FID字段联系起来了
                          end;
                       dbtrans.fromaccessquery_3.Next;

                end;

           //------------------------------------------------------//



                with dbtrans.tosqlserverB   do          //通过数据集TOSQLSERVER获得在TCS行为表中新生成的ID序列号
                  begin
                    close;
                    sql.Clear;
                    sql.Add('select rttransaction.ID,rttransaction.影片编号 from rttransaction where rttransaction.FID='+''+temp1+'');
                    open;
                    first;
                  end;




             with dbtrans.fromaccessquery_3 do
                 begin
                   close;
                   sql.Clear;
                   sql.Add('select tbrentcd.*,tbrtransaction.FID from tbrtransaction,tbrentcd where tbrtransaction.cid1=tbrentcd.id and tbrtransaction.FID='+''+temp2+'');
                   open;
                   FIRST;
                 end;




            for j:=0 to dbtrans.fromaccessquery_3.RecordCount do
                begin
                     if j=dbtrans.fromaccessquery_3.RecordCount then break;

                      temp3:=dbtrans.tosqlserverB.FieldValues['ID']; /////// 此ID序列循环一次累加一次
                     temp4:=dbtrans.tosqlserverB.FieldValues['影片编号'];



                      


                      with dbtrans.tosqlserver do
                          begin
                          close;
                          sql.Clear;

                         sql.Add('update rent_cd set ID='+''+temp3+''
                         +','+ '绑定号='+''+temp2+''+','+' 状态 ='+''''+'已租'+''''
                         +' where 影片编号='+''''+temp4+'''');   //中文必须加引号,而数值加了引号就为字符串,不加为数据类型


                          execsql;
                          end;

                                                    
                  dbtrans.tosqlserverB.Next;
                  dbtrans.fromaccessquery_3.Next;

                

                 end;

                    dbtrans.fromaccessquery_2.next;


       end;









                        

 /////////////////////////////////////////////////////////////////////////////////////////////////
    //下面导入会员信息+碟片未还


  with dbtrans.fromaccessquery_1 do // 从ACCESS_tbassociator+batortype---->>SQLSERVER_atorinfo(会员信息)
      begin
        close;
        sql.Clear;
        sql.add('select tbassociator.*,tbatortype.* from tbassociator,tbatortype where tbassociator.aid=tbatortype.id');
        open;
        first;
      end;


  with dbtrans.fromaccessquery_2 do //ACCESS_tbassociator.AID+tbatortype.name----->>SQLSERVER_atorinfo(会员信息)
      begin
        close;
        sql.Clear;
        sql.add('select tbatortype.name from tbassociator,tbatortype where tbassociator.aid=tbatortype.id');
        open;
        first;
      end;            // tbassociator.AID,
                                                //数据集1,2取得会员信息表,一条一条的取

 dbtrans.bsskingauge1.ProgressText:='正在导入到会员表';

 for i:=0 to dbtrans.fromaccessquery_1.RecordCount do //读入会员表的记录,从第一条开始
  
      begin //BEGIN
        dbtrans.bsskingauge1.maxvalue:=dbtrans.fromaccessquery_1.RecordCount;//进度条1
        dbtrans.bsskingauge1.value:=i;//进度条1
        if i=dbtrans.fromaccessquery_1.RecordCount then break;

                     if dbtrans.fromaccessquery_1.FieldValues['address']=null then
                       rentcdtemp1:='  '
                    else
                       rentcdtemp1:=dbtrans.fromaccessquery_1.FieldValues['address'];

                    if dbtrans.fromaccessquery_1.FieldValues['telephone']=null then
                       rentcdtemp2:='  '
                    else
                       rentcdtemp2:=dbtrans.fromaccessquery_1.FieldValues['telephone'];
                    daynumber:=date-dbtrans.fromaccessquery_1.FieldValues['lastday'];

             with dbtrans.tosqlserver do
                  begin
                    close;
                    sql.Clear;
                    sql.Add('insert into atorinfo(会员编号,剩余部数,剩余天数,未归还部数,押金,说明,类型,姓名,家庭地址,联系电话,会员首拼,租碟编号) values('+''''+dbtrans.fromaccessquery_1.FieldValues['code']+''''
                    +','+''''+inttostr(dbtrans.fromaccessquery_1.FieldValues['number'])+''''+','+''''+inttostr(round(daynumber))+''''
                    +','+''''+'0'+''''+','+''''+currtostr(dbtrans.fromaccessquery_1.FieldValues['deposit'])+''''
                    +','+''''+dbtrans.fromaccessquery_1.FieldValues['memo']+''''+','+''''+dbtrans.fromaccessquery_2.FieldValues['name']+''''
                    +','+''''+dbtrans.fromaccessquery_1.FieldValues['name']+''''+','+''''+rentcdtemp1+''''
                    +','+''''+rentcdtemp2+''''
                    +','+''''+dbtrans.fromaccessquery_1.FieldValues['spell']+''''+','+''''+' '+''''+'    )');
                    execsql;
                  end;                             //开始导入到TCS的资金表,其FID字段等它自动累加



              temp2:=dbtrans.fromaccessquery_1.FieldValues['ID']; //同时获得RC会员表此字段的ID号,赋TEMP2
              temp5:=dbtrans.fromaccessquery_1.FieldValues['code'];
              
           //---------------------------------------------------------------------------//

             with dbtrans.fromaccessquery_3 do
                 begin
                   close;
                   sql.Clear;
                   sql.Add('select tbrtransaction.day1,tbrtransaction.FID,tbrentcd.code from tbrtransaction,tbrentcd where  tbrentcd.id=tbrtransaction.CID1 and cid2=2 and tbrtransaction.aid='+''+temp2+'');
                   open;                                                                                                                                       //找出此会员在RC出租事件表中未归还影片的记录
                   FIRST;
                 end;


                  temp1:=inttostr(dbtrans.fromaccessquery_3.RecordCount);//统计本会员未归还影片的数量
                  temp6:=' ';//初始化


                 sl:=Tstringlist.Create;



             for j:=0 to dbtrans.fromaccessquery_3.RecordCount do     //统计记录个数,循环插入新记录到TCS的行为表中

                begin//FOR

                   if j=dbtrans.fromaccessquery_3.RecordCount then break;

                 sl.Delimiter:=',';     //分隔符为，
                 sl.ADD(dbtrans.fromaccessquery_3.FieldValues['code']);


                  temp6:=dbtrans.fromaccessquery_3.FieldValues['FID']; //写在FOR循环里,如果此会员未归还影片为0,那么就不进入这个FOR循环,TEMP6将不会得到赋值
                                                                      //同时也不存在给一个STRING类型赋为NULL值的情况
                                                                       //如果进入了循环,则将RC的帮定号赋给TEMP6


                      with dbtrans.tosqlserver do
                          begin
                          close;
                          sql.Clear;
                          sql.Add('insert into rttransaction(出租日期,会员编号,影片编号,FID) values('+''''+datetostr(dbtrans.fromaccessquery_3.FieldValues['day1'])+''''
                          +','+''''+temp5+''''+','+''''+dbtrans.fromaccessquery_3.FieldValues['code']+''''+','+''''+temp6+''''+')'); //会员用户归还日期不导入)
                          execsql;                                               //对于会员不需要赋FID,即对应资金表的FID
                          end;

                       dbtrans.fromaccessquery_3.Next; // 有问题

                end;

           //------------------------------------------------------//

          if temp6<>' ' then//如果TEMP6为' ',那么不进入这个循环,表示此会员没有未归还的影片
           begin//if
               with dbtrans.tosqlserverB   do          //通过数据集TOSQLSERVER获得在TCS行为表中新生成的ID序列号
                  begin
                    close;
                    sql.Clear;
                    sql.Add('select rttransaction.ID,rttransaction.影片编号 from rttransaction where rttransaction.FID='+''+temp6+'');
                    open;                                                                                        //读出所有FID为XXXX的未归还记录
                    first;                                                                                       //ID号将在下面的循环中NEXT
               end;




             with dbtrans.fromaccessquery_3 do
                 begin
                   close;
                   sql.Clear;
                   sql.Add('select tbrentcd.*,tbrtransaction.FID from tbrtransaction,tbrentcd where tbrtransaction.cid1=tbrentcd.id and tbrtransaction.FID='+''+temp6+'');
                   open;                                                                                                                       //AID为RC会员表ID
                   FIRST;
                 end;


                    k:=dbtrans.fromaccessquery_3.RecordCount;

             for j:=0 to dbtrans.fromaccessquery_3.RecordCount do   //如果TEMP6为空,则不修改

                begin  //for

                     if j=dbtrans.fromaccessquery_3.RecordCount then break;




                     temp4:=dbtrans.tosqlserverB.FieldValues['影片编号'];
                     temp3:=dbtrans.tosqlserverB.FieldValues['ID']; /////// 此ID序列循环一次NEXT一次


                      


                      with dbtrans.tosqlserver do  //修改影片库中ID,绑定号,状态三个字段
                          begin
                            close;
                            sql.Clear;

                            sql.Add('update rent_cd set ID='+''+temp3+''
                            +','+ '绑定号='+''+temp6+''+','+' 状态 ='+''''+'已租'+''''
                            +' where 影片编号='+''''+temp4+'''');   //中文必须加引号,而数值加了引号就为字符串,不加为数据类型
                            execsql;
                            end;