untimportdata.pas

简要说明:对医院幼儿心理情况做的一个调查,统计系统.

  end;
  ExecDBCommand(DM.cnn, 'insert into tClass(SchoolID,ClassYear,Name) '
    +' values ('+IntToStr(SchoolID)+','
                +QuotedStr(ClassYear)+','
                +QuotedStr(ClassName)+')');
  Result := GetDBValue(DM.cnn, 'select iAutoID from tClass '
    +' where SchoolID = '+IntToStr(SchoolID)
    +' and ClassYear = '+QuotedStr(ClassYear)
    +' and Name='+QuotedStr(ClassName));
end;

procedure TfrmImportData.GetChildrenID(ClassID: Integer;
  var recChildren: TChildren);
var
  iAutoID: Variant;
begin
  iAutoID := GetDBValue(DM.cnn, 'select iAutoID from tChildren '
    +' where ClassID = ' + IntToStr(ClassID)
    +' and name='+QuotedStr(recChildren.Name)
    +' and Sex='+inttostr(recChildren.Sex));
    //+' and birthyear='+inttostr(recChildren.BirthYear)
    //+' and birthmonth='+inttostr(recChildren.BirthMonth));
  if not VarIsNull(iAutoID) then begin
    recChildren.iAutoID := iAutoID;
    exit;
  end;
  ExecDBCommand(DM.cnn, 'insert into tChildren(code,name,sex,birthyear,birthmonth,classid) '
    +' values ('+QuotedStr(recChildren.Code)+','
                +QuotedStr(recChildren.Name)+','
                +IntToStr(recChildren.Sex)+','
                +IntToStr(recChildren.BirthYear)+','
                +InttoStr(recChildren.BirthMonth)+','
                +IntToStr(ClassID)+')');
  recChildren.iAutoID := GetDBValue(DM.cnn, 'select iAutoID from tChildren '
    +' where ClassID = ' + IntToStr(ClassID)
    +' and name='+QuotedStr(recChildren.Name)
    +' and Sex='+inttostr(recChildren.Sex));
    //+' and birthyear='+inttostr(recChildren.BirthYear)
    //+' and birthmonth='+inttostr(recChildren.BirthMonth));
end;

procedure TfrmImportData.CalcData(recChildren: TChildren);
var
  ChildrenID, Lvl: Integer;
  birth, Avg, SD: Double;
  strSQL: string;
  MeasureCode, ItemCode: string;
  strResult,strItemValue: string;
  adsTmp, adsStd,adsreporttest: TADODataSet;
begin
  strResult := '';
  ChildrenID := recChildren.iAutoID;
  birth := recChildren.BirthYear+recChildren.BirthMonth/100;
  case FDataType of
    0 : begin
          MeasureCode := '0000000003';
          strSQL := '  select mi.Content '
                    +' from tChildrenResult cr '
                    +'   inner join tChildrenResult_Mx crm on cr.iAutoID=crm.CheckResultID '
                    +'   inner join tMeasure_Item mi on crm.ItemCode=mi.Code '
                    +' where ChildrenID='+IntToStr(ChildrenID)
                    +'   and cr.MeasureCode = '+QuotedStr(MeasureCode)
                    +'   and (crm.ItemValue=''是'' or crm.ItemValue=''1'')';
          adsTmp := GetDataSet(DM.cnn, strSQL);
          if adsTmp.IsEmpty then
            strResult := XLGood
          else
         begin
          with adsTmp do
          begin
            First;
            while not eof do
            begin
              strSQL := 'select Method from tReportText '
                        +'where ItemCode='+QuotedStr(Fieldbyname('Itemcode').AsString);
              strResult:=VarToStr(GetDBValue(DM.cnn,strSQL));
              strSQL := 'insert into tXLResult(ChildrenID,sResult,sSuggest) '
                 +' values('+IntToStr(ChildrenID)+','+QuotedStr(Fieldbyname('Content').AsString)+','
                 +QuotedStr(strResult)+')';
              ExecDBCommand(DM.cnn, strSQL);
              Next;
            end;
          end;
        end;
      end;
          {
          begin
            with adsTmp do
             begin
              First;
              while not eof do
               begin
                if Length(strResult) = 0 then
                  strResult := strResult + Fields[0].AsString
                else
                  strResult := strResult + '、' + Fields[0].AsString;
                Next;
              end;
            end;
            strResult := XLPre + strResult + IntToStr(XLLast);
          end;
          adsTmp.Free;
          strSQL := 'insert into tXLResult(ChildrenID,sResult) '
                   +' values('+IntToStr(ChildrenID)+','+QuotedStr(strResult)+')';
          ExecDBCommand(DM.cnn, strSQL);
        end; }
    1 : begin
          //气质类型
          MeasureCode := '0000000002';
          ItemCode    := '0000000013';
          strSQL := ' select crm.ItemValue '
                   +' from tChildrenResult cr '
                   +' inner join tChildrenResult_Mx crm on cr.iAutoID=crm.CheckResultID '
                   +' where ChildrenID='+IntToStr(ChildrenID)
                   +'   and cr.MeasureCode =' +QuotedStr(MeasureCode)
                   +'   and crm.ItemCode = ' +QuotedStr(ItemCode);
          adsTmp := GetDataSet(DM.cnn, strSQL);
          if adsTmp.IsEmpty then Exit;
          strResult := QZType + adsTmp.Fields[0].AsString;
          adsTmp.Free;
          strSQL := 'insert into tQZResult(ChildrenID,sResult) '
                   +' values('+IntToStr(ChildrenID)+','+QuotedStr(strResult)+')';
          ExecDBCommand(DM.cnn, strSQL);
          //其他因子
          strSQL := 'select crm.ItemCode,crm.ItemValue '
                   +' from tChildrenResult cr '
                   +'   inner join tChildrenResult_Mx crm on cr.iAutoID=crm.CheckResultID '
                   +' where ChildrenID='+IntToStr(ChildrenID)
                   +'   and cr.MeasureCode =' +QuotedStr(MeasureCode)
                   +'   and crm.ItemCode <> ' +QuotedStr(ItemCode);
          adsTmp := GetDataSet(DM.cnn, strSQL);
          //取得标准
          strSQL := '  select ItemCode,AvgValue,SDValue '
                    +' from tJudgeStd js '
                    +'   inner join tJudgeStd_Mx jsm on js.iAutoID=jsm.JSID '
                    +' where js.MeasureCode=' +QuotedStr(MeasureCode)
                    +'   and js.iType=0 '
                    +'   and Sex = '+IntToStr(recChildren.Sex)
                    +'   and AgeBegin<='+FloatToStr(birth)
                    +'   and AgeEnd >='+FloatToStr(birth);
          adsStd := GetDataSet(DM.cnn, strSQL);
          adsreporttest:=TADODataSet.Create(nil);
          with adsTmp do
           begin
            first;
            while not Eof do begin
              if adsStd.Locate('ItemCode',FieldbyName('ItemCode').Value, []) then begin
                Avg := adsStd.fieldbyname('avgvalue').Value;
                SD  := adsStd.fieldbyname('SDValue').Value;
                if FieldByName('ItemValue').Value < Avg-2*SD then Lvl := -2
                else if FieldByName('ItemValue').Value < Avg-SD then Lvl := -1
                else if FieldByName('ItemValue').Value <= Avg+SD then Lvl := 0
                else if FieldByName('ItemValue').Value <= Avg+2*SD then Lvl := 1
                else Lvl := 2;
               {strSQL := 'select discript=isnull(discript,''''),Method=isnull(Method,'''') from tReportText '
                        +'where ItemCode='+QuotedStr(Fieldbyname('Itemcode').AsString)
                        +'  and lvl='+IntToStr(Lvl);
                strResult := GetDBValue(DM.cnn, strSQL);
                if Trim(strResult) <> '' then begin
                  strSQL := 'insert into tQZTableResult(childrenid, ItemCode, sResult,) '
                           +' values('+IntToStr(ChildrenID)+','
                                      +QuotedStr(Fieldbyname('Itemcode').AsString)+','
                                      +QuotedStr(strResult)+')';
                  ExecDBCommand(DM.cnn, strSQL);}
                   strSQL := 'select discript=isnull(discript,''''),Method=isnull(Method,'''') from tReportText '
                        +'where ItemCode='+QuotedStr(Fieldbyname('Itemcode').AsString)
                        +'  and lvl='+IntToStr(Lvl);
               adsreporttest.Active:=False;
               adsreporttest.CommandText:=strSQL;
               adsreporttest.Active:=True;
               if Trim(strResult) <> '' then
               begin
                 strSQL := 'insert into tQZTableResult(childrenid, ItemCode, sResult,sSuggest) '
                          +' values('+IntToStr(ChildrenID)+','
                                     +QuotedStr(Fieldbyname('Itemcode').AsString)+','
                                     +QuotedStr(adsreporttest.Fieldbyname('discript').AsString)+','
                                     +QuotedStr(adsreporttest.Fieldbyname('Method').AsString)+')';
                  ExecDBCommand(DM.cnn, strSQL);
              end;
              end;
              Next;
            end;
            if Assigned(adsreporttest) then adsreporttest.Free;
            Free;
            adsStd.Free;
          end;
        end;
    2 : begin
          MeasureCode := '0000000001';
          strSQL := 'select crm.ItemCode,crm.ItemValue '
                   +' from tChildrenResult cr '
                   +'   inner join tChildrenResult_Mx crm on cr.iAutoID=crm.CheckResultID '
                   +' where ChildrenID='+IntToStr(ChildrenID)
                   +'   and cr.MeasureCode =' +QuotedStr(MeasureCode);
          adsTmp := GetDataSet(DM.cnn, strSQL);
          //取得标准
          strSQL := '  select ItemCode,AvgValue '
                    +' from tJudgeStd js '
                    +'   inner join tJudgeStd_Mx jsm on js.iAutoID=jsm.JSID '
                    +' where js.MeasureCode=' +QuotedStr(MeasureCode)
                    +'   and js.iType=1 '
                    +'   and Sex = '+IntToStr(recChildren.Sex);
          adsStd := GetDataSet(DM.cnn, strSQL);
          with adsTmp do
           begin
            first;
            while not Eof do
             begin
              if adsStd.Locate('ItemCode',FieldbyName('ItemCode').Value, []) then begin
                Avg := adsStd.fieldbyname('avgvalue').AsFloat;
                if FieldByName('ItemValue').AsFloat >= Avg then
                  strResult := '*'
                else
                  strResult := '';
                if FieldByName('ItemValue').AsString = '.' then
                  strItemValue := '-1'
                else strItemValue := FieldByName('ItemValue').AsString;
                strSQL := 'insert into tXWResult(childrenid, ItemCode, NormalValue, CalcValue, FlagText) '
                         +' values('+IntToStr(ChildrenID)+','
                                    +QuotedStr(Fieldbyname('Itemcode').AsString)+','
                                    +Adsstd.fieldbyname('avgvalue').AsString+','
                                    +strItemValue+','
                                    +QuotedStr(strResult)+')';
                ExecDBCommand(DM.cnn, strSQL);
              end;
              Next;
            end;
            Free;
            adsStd.Free;
          end;
        end;
     3:  //感觉统合功能量表
     begin
        MeasureCode := '0000000004';
        //其他因子
        strSQL := 'select crm.ItemCode,crm.ItemValue '
                 +' from tChildrenResult cr '
                 +'   inner join tChildrenResult_Mx crm on cr.iAutoID=crm.CheckResultID '
                 +' where ChildrenID='+IntToStr(ChildrenID)
                 +'   and cr.MeasureCode =' +QuotedStr(MeasureCode);
//                 +'   and crm.ItemCode <> ' +QuotedStr(ItemCode);
        adsTmp := GetDataSet(DM.cnn, strSQL);
        //取得标准
        strSQL := '  select ItemCode,AvgValue,SDValue '
                  +' from tJudgeStd js '
                  +'   inner join tJudgeStd_Mx jsm on js.iAutoID=jsm.JSID '
                  +' where js.MeasureCode=' +QuotedStr(MeasureCode)
                  +'   and js.iType=0 '
                  +'   and Sex = '+IntToStr(recChildren.Sex)
                  +'   and AgeBegin<='+FloatToStr(birth)
                  +'   and AgeEnd >='+FloatToStr(birth);
        adsStd := GetDataSet(DM.cnn, strSQL);
        with adsTmp do
        begin
          first;
          while not Eof do begin
            if adsStd.Locate('ItemCode',FieldbyName('ItemCode').Value, []) then
            begin
              Avg := adsStd.fieldbyname('avgvalue').Value;
              SD  := adsStd.fieldbyname('SDValue').Value;
              if FieldByName('ItemValue').Value < Avg-2*SD then Lvl := -2
              else if FieldByName('ItemValue').Value < Avg-SD then Lvl := -1
              else if FieldByName('ItemValue').Value <= Avg+SD then Lvl := 0
              else if FieldByName('ItemValue').Value <= Avg+2*SD then Lvl := 1
              else Lvl := 2;
              strSQL := 'select RptText=isnull(discript,'''')+isnull(Method,'''') from tReportText '
                       +'where ItemCode='+QuotedStr(Fieldbyname('Itemcode').AsString)
                       +'  and lvl='+IntToStr(Lvl);
              strResult := GetDBValue(DM.cnn, strSQL);
              if Trim(strResult) <> '' then begin
                strSQL := 'insert into tGJResult(childrenid, ItemCode, sResult) '
                         +' values('+IntToStr(ChildrenID)+','
                                    +QuotedStr(Fieldbyname('Itemcode').AsString)+','
                                    +QuotedStr(strResult)+')';
                ExecDBCommand(DM.cnn, strSQL);
              end;
            end;
            Next;
          end;
          Free;
          adsStd.Free;
        end;
       
     end;   
  end;
end;

end.