realtyc42b.pas

物业管理系统系统特点 一、 实现集中式管理 系统将集团公司、各分公司、各物业管理处连接到一起

        query.Open;
        if query.RecordCount>0 then
        begin
            s1:=Query.FieldByName('联系人').Value ;
            s2:=Query.FieldByName('电话').Value ;
        end;

        s3:='直接支付';
        query.SQL.Clear;
        query.SQL.add('select * from 收费标准 where 名称='''+listview1.Selected.SubItems[2]+''' and 是否代缴费用='''+'Y'+'''');
        query.Open;
        if query.RecordCount>0 then s3:='银行托收';

        //单号,客户名称,发生日期,应收金额,已收金额,';
        //未收金额,承诺付款日期,联系人,联系电话,款项类别,付款类别
        query.sql.Clear;
        query.sql.add(s);
        query.Parameters.ParamByName('s1').Value:= listview2.Selected.Caption;
        query.Parameters.ParamByName('s2').Value:= listview2.Selected.SubItems[2];
        query.Parameters.ParamByName('s3').Value:= listview2.Selected.SubItems[10];
        query.Parameters.ParamByName('s4').Value:= strtofloat(listview2.Selected.SubItems[7]);
        query.Parameters.ParamByName('s5').Value:= 0;
        query.Parameters.ParamByName('s6').Value:= 0;
        query.Parameters.ParamByName('s7').Value:= strtofloat(listview2.Selected.SubItems[7]);
        query.Parameters.ParamByName('s8').Value:= listview2.Selected.SubItems[10];
        query.Parameters.ParamByName('s9').Value:= s1;
        query.Parameters.ParamByName('s10').Value:= s2;
        query.Parameters.ParamByName('s11').Value:= sItem;
        query.Parameters.ParamByName('s12').Value:= s3;
        query.Parameters.ParamByName('s13').Value:=listview2.Selected.SubItems[11];

        DataModuleADO.ADOConnection1.BeginTrans;
        query.execsql;
        DataModuleADO.ADOConnection1.CommitTrans;
    end;


    query.Close;
    query.free;



end;

procedure Tf_RealtyC42B.Button8Click(Sender: TObject);
Var
    query,query1,query2,query22,query4,query3,query5:TADOQuery;
    sDay,sMonth,sStyle,sRoom,s,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,r,r3,ss,r1,r2,r4,r5,s77,rr,sNum:string;
    i,n,nn:integer;
    b:bool;
    d1,d2,d3,d22,d33:double;
    bSucceed:bool;
begin
    if listview1.SelCount=0 then exit;


    query:=TADOQuery.Create(nil);
    query.Connection:=DataModuleADO.ADOConnection1;
    query1:=TADOQuery.Create(nil);
    query1.Connection:=DataModuleADO.ADOConnection1;
    query2:=TADOQuery.Create(nil);
    query2.Connection:=DataModuleADO.ADOConnection1;
    query4:=TADOQuery.Create(nil);
    query4.Connection:=DataModuleADO.ADOConnection1;
    query5:=TADOQuery.Create(nil);
    query5.Connection:=DataModuleADO.ADOConnection1;
    query3:=TADOQuery.Create(nil);
    query3.Connection:=DataModuleADO.ADOConnection1;
    query22:=TADOQuery.Create(nil);
    query22.Connection:=DataModuleADO.ADOConnection1;





    //是否已收
    s:='select * from 抄表费用公摊 where 收费月份='''+copy(datetostr(DateTimePicker3.Date),1,7);
    s:=s+''' and 分摊表='''+listview1.Selected.Caption;
    s:=s+'''';

    query.SQL.clear;
    query.SQL.Add(s);
    query.Open;
    if query.RecordCount>0 then
    begin
        showmessage('该表已经生成'+copy(datetostr(DateTimePicker3.Date),1,7)+'月份的公摊抄表收费');
        exit;
    end;


    s:='select * from 公摊仪表 where 编号='''+listview1.Selected.Caption;
    s:=s+'''';

    query.SQL.clear;
    query.SQL.Add(s);
    query.Open;
    if query.RecordCount>0 then s:=query.fieldbyname('收费标准').Value;

    s:='select * from 收费标准 where 编号='''+s;
    s:=s+'''';

    query2.SQL.Clear;
    query2.SQL.Add(s);
    query2.Open;
    if query2.RecordCount>0 then
    begin
        s:='select * from 收费时间 where 编号='''+query2.fieldbyname('收费时间').Value;
        s:=s+'''';
        query2.SQL.Clear;
        query2.SQL.Add(s);
        query2.Open;
        if query2.RecordCount>0 then
        begin
            if query2.fieldbyname('名称').Value='收当月' then
            begin
                sMonth:=copy(datetostr(DateTimePicker3.Date),1,7);
            end;

            if query2.fieldbyname('名称').Value='收上月' then
            begin
                s7:=copy(datetostr(DateTimePicker3.Date),1,7)+'-01';
                s7:=datetostr(strtodate(s7)-1);

                sMonth:=copy(s7,1,7);
            end;

            if query2.fieldbyname('名称').Value='收下月' then
            begin
                s7:=copy(datetostr(DateTimePicker3.Date),1,7)+'-28';
                s7:=datetostr(strtodate(s7)+4);

                sMonth:=copy(s7,1,7);
            end;


            if query2.fieldbyname('名称').Value='指定月' then
            begin
                sMonth:=copy(datetostr(DateTimePicker3.Date),1,7)+'-'+ s6;


                s:='select * from 收费时间明细 where 编号='''+query2.fieldbyname('编号').Value;
                s:=s+''' and 月份='''+copy(copy(datetostr(DateTimePicker3.Date),1,7),6,2);
                s:=s+'''';

                query2.SQL.Clear;
                query2.SQL.Add(s);
                query2.Open;
                if query2.RecordCount=0 then sMonth:='-';
            end;
        end;
    end;


    //是否抄表
    s:='select * from 管理区公摊抄表 where 抄表月份='''+sMonth;
    s:=s+''' and 公摊仪表='''+listview1.Selected.Caption;
    s:=s+'''';

    query.SQL.clear;
    query.SQL.Add(s);
    query.Open;
    if query.RecordCount=0 then
    begin
        showmessage('该表'+copy(datetostr(DateTimePicker3.Date),1,7)+'月份尚未抄表。');
        exit;
    end;


    //按用量分摊时，该表分摊对象是否全部抄表
    b:=true;

    s:='select * from 公摊仪表 where 分摊方式='''+'按用量分摊';
    s:=s+''' and 编号='''+listview1.Selected.Caption;
    s:=s+'''';

    query.SQL.clear;
    query.SQL.Add(s);
    query.Open;
    if query.RecordCount>0 then
    begin
        if query.fieldbyname('公摊仪表类型').Value='总表' then
        begin
            s:='select * from 房间 where 管理区='''+query.fieldbyname('管理区').Value;
            s:=s+'''';

            query2.SQL.clear;
            query2.SQL.Add(s);
            query2.Open;
            while not query2.Eof do
            begin
                s:='select * from 房间抄表 where 抄表月份='''+sMonth;
                s:=s+''' and 房间='''+query2.fieldbyname('编号').Value;
                s:=s+'''';

                query3.SQL.clear;
                query3.SQL.Add(s);
                query3.Open;
                if query3.RecordCount=0 then
                begin
                    sRoom:= query2.fieldbyname('编号').Value+'--'+query2.fieldbyname('名称').Value;
                    b:=false;
                    break;
                end;

                query2.Next;
            end;
        end;



        if query.fieldbyname('公摊仪表类型').Value<>'总表' then
        begin
            s:='select * from 公摊仪表明细 where 编号='''+query.fieldbyname('编号').Value;
            s:=s+'''';

            query1.SQL.clear;
            query1.SQL.Add(s);
            query1.Open;
            while not query1.Eof do
            begin
                if query1.fieldbyname('类型').Value='房间' then
                begin
                    s:='select * from 房间抄表 where 抄表月份='''+sMonth;
                    s:=s+''' and 房间='''+query1.fieldbyname('类型编号').Value;
                    s:=s+'''';

                    query2.SQL.clear;
                    query2.SQL.Add(s);
                    query2.Open;
                    if query2.RecordCount=0 then
                    begin
                        sRoom:= query1.fieldbyname('类型编号').Value+'--'+query1.fieldbyname('类型名称').Value;
                        b:=false;
                        break;
                    end;
                end;

                if query1.fieldbyname('类型').Value='楼宇' then
                begin
                    s:='select * from 房间 where 楼宇='''+query1.fieldbyname('类型编号').Value;
                    s:=s+'''';

                    query2.SQL.clear;
                    query2.SQL.Add(s);
                    query2.Open;
                    while not query2.Eof do
                    begin

                        s:='select * from 房间抄表 where 抄表月份='''+sMonth;
                        s:=s+''' and 房间='''+query2.fieldbyname('编号').Value;
                        s:=s+'''';

                        query3.SQL.clear;
                        query3.SQL.Add(s);
                        query3.Open;
                        if query3.RecordCount=0 then
                        begin
                            sRoom:= query2.fieldbyname('编号').Value+'--'+query2.fieldbyname('名称').Value;
                            b:=false;
                            break;
                        end;

                        query2.Next;
                    end;
                end;

                query1.Next;
            end;
        end;
     end;

     if b=false then
     begin
        showmessage('按用量分摊时该分摊表中的：'+sRoom+' 房间没有抄表。');
        exit;
     end;


    bSucceed:=false;


    //////////////////////////////////////////////////计算

    s:='select * from 公摊仪表 where 编号='''+listview1.Selected.Caption;
    s:=s+'''';

    query.SQL.clear;
    query.SQL.Add(s);
    query.Open;
    if query.RecordCount>0 then
    begin
        s7:='0';
        s:='select * from 收费标准 where 编号='''+query.fieldbyname('收费标准').Value;
        s:=s+'''';
        query2.SQL.Clear;
        query2.SQL.Add(s);
        query2.Open;
        if query2.RecordCount>0 then
        begin
            s8:=query2.fieldbyname('单价').Value;

            //计算应收日期------------------------------------------------------
            s:= query2.fieldbyname('收费时间').Value;

            s:='select * from 收费时间 where 编号='''+s;
            s:=s+'''';

            query2.SQL.Clear;
            query2.SQL.Add(s);
            query2.Open;
            if query2.RecordCount>0 then
            begin
                if query2.fieldbyname('名称').Value='收当月' then
                begin
                    s6:=query2.fieldbyname('收费日期').Value;
                    if strtoint(s6)<10 then s6:='0'+s6;

                    s6:=copy(datetostr(date),1,7)+'-'+ s6;
                end;

                if query2.fieldbyname('名称').Value='收上月' then
                begin
                    if strtoint(query2.fieldbyname('收费日期').Value)<10 then s6:='0'+query2.fieldbyname('收费日期').Value
                    else s6:= query2.fieldbyname('收费日期').Value;

                    s7:=copy(datetostr(date),1,7)+'-01';
                    s7:=datetostr(strtodate(s7)-1);

                    s6:=copy(s7,1,7)+'-'+ s6;
                end;

                if query2.fieldbyname('名称').Value='收下月' then
                begin
                    if strtoint(query2.fieldbyname('收费日期').Value)<10 then s6:='0'+query2.fieldbyname('收费日期').Value
                    else s6:= query2.fieldbyname('收费日期').Value;

                    s7:=copy(datetostr(date),1,7)+'-28';
                    s7:=datetostr(strtodate(s7)+4);

                    s6:=copy(s7,1,7)+'-'+ s6;
                end;


                if query2.fieldbyname('名称').Value='指定月' then
                begin
                    s6:=query2.fieldbyname('收费日期').Value;
                    if strtoint(s6)<10 then s6:='0'+s6;

                    s6:=copy(datetostr(date),1,7)+'-'+ s6;


                    s:='select * from 收费时间明细 where 编号='''+query2.fieldbyname('编号').Value;
                    s:=s+''' and 月份='''+copy(copy(datetostr(date),1,7),6,2);
                    s:=s+'''';