threadp.pas

delphi开发的中国移动大客户管理系统,后台数据库为oracle

//说明：
//1、字段的统一定义（都是代码的定义）：
//      地市    CITYCODE
//      局向    OFF_NO_HOME
//      星级    DJDM
//      时间    SJ
//      性质    XZDM
//      品牌    PP
//      数量    SL
//      性别    COLNAME
//      喜好    JHDM
//      状态    ZTDM
//      业务    YWDM
//      行业    HYDM

{****************曾庆顺改动*******************
1、线程的总体思路是错的，数据库的安全访问的问题
2、在线程执行时一直占用主线程，调用VCL。要实现线程安全
3、删除一些没有必要的东西,改掉了recorecount取值的方法
//2002。09。16
4、加了互斥（临界区），防止多线程共同申请资源时出现死锁
   2002、08、27

   待处理的问题
   1、执行函数里有个locate
   2、执行函数的加详细数值的循环有问题
   3、线程初始化时有问题,没必要重复做，
      还有就是自增变量会超过长度，不能用静态数组
**********************************************}


Unit ThreadP;

Interface

Uses
  Windows,Classes, Messages, SysUtils, Graphics, ExtCtrls, Db, DBTables, XLGrids, XLCells, forms,
  Chart, Series, Dialogs, ComObj, main ;

Type
  TQXSql = Record
    QXSumSql, QXSeriesSql, QXTotalSumSql: String;
  End;

Type

  TDrawThread = Class(TThread)
  Private
    Sheet: TXLSheet;
    Querytj, Querytj2, Querytj3: Tquery;
    cityname: Array[0..20] Of String;
    offname: Array[0..20] Of Array[0..20] Of String;
    starname: Array[0..20] Of String;
    ppname: Array[0..255] Of String;
    lbname: Array[0..20] Of String;
    xbname: Array[0..5] Of String;
    TypeName: Array[0..99] Of String;
    onecity: integer;
    tital: integer;
    querytjCount,querytj3Count :integer;
    DataBase: String;
    //全局变量
    P_i,P_j,P_Bz,P_FieldCount :integer;
    P_value,P_cityname :string;
    //标志是否加合计底下的
    P_add,P_INDISEQBz :boolean;
    //
    P_count :integer;
    //
    P_str :string;
    //异常
    P_Exception :Exception;
    temp: Array[0..49] Of Variant;
    //初始化
    Procedure initialize;
    //得到几个转换的名称
    Function GetName(s: String): String;
    //
    Procedure GetTotal(i, j, k: integer);
    //用来调用vcl函数等，实现线程同步
    Procedure RunUpdate;
    //修复增加统计详细项目信息时把该合成的加在一起
    Procedure RunModify;
    //异常处理过程
    Procedure ShowError;
    procedure inisheet;
  Protected
    Procedure Execute; Override;
  Public
    P_Result :boolean;
    Constructor Create(XLSheet: TXLSheet; QueryGroup, QueryAll, QueryCol: Tquery; count1,Count2 :integer;DataBaseName: String; OneCityName: integer = 0; title: integer = 0);
  End;
//执行为true表示成功
Function RunThead(XLSheet: TXLSheet; QueryGroup, QueryAll, QueryCol: Tquery;count1,Count2 :integer; DataBaseName: String; OneCityName: integer = 0; title: integer = 0):TDrawThread;
Function To_CityNo(pCityNo:String):Integer;

Implementation

Constructor TDrawThread.Create(XLSheet: TXLSheet; QueryGroup, QueryAll, QueryCol: Tquery;count1,Count2 :integer; DataBaseName: String; OneCityName: integer = 0; title: integer = 0);
Begin
  Inherited Create(true);
  Sheet := xlsheet;
  P_Result :=true;
  P_add :=true;
  P_INDISEQBz :=false;
  //存的是统计类型的详细的项目
  querytj := querygroup;
  //具体的统计数值合计
  querytj2 := queryall;
  //统计的类型
  Querytj3 := querycol;
  onecity := onecityname;
  querytjCount :=count1;
  querytj3Count :=Count2;
  database := databasename;
  tital := title;


  FreeOnTerminate := True;
  Resume;
End;

//外部调用线程，返回为真就是成功
Function RunThead(XLSheet: TXLSheet; QueryGroup, QueryAll, QueryCol: Tquery;count1,Count2 :integer; DataBaseName: String; OneCityName: integer = 0; title: integer = 0):TDrawThread;
var
  draw1 :TDrawThread;
begin
  result :=nil;

  draw1 :=nil;
  try
    draw1 :=TDrawThread.Create(XLSheet,QueryGroup, QueryAll, QueryCol,count1,Count2,DataBaseName,OneCityName, title);
    result :=draw1;
  finally
    //draw1.DoTerminate;
    //draw.Suspend;
    //draw.Free;

    //draw1.Resume;
   end;
end;
Procedure TDrawThread.Execute;
Var
  i, j, temp1,li,lj,lSum: integer;
  temps,str: String;
Begin
  FreeOnTerminate:=true;
  //OnTerminate:=self.OnTerminate;
  if WaitForSingleObject(ZqsMutex,INFINITE)=WAIT_OBJECT_0 then
  begin
    Try
      initialize;
      lj :=0;
      synchronize(inisheet);//陈振剑改：修正BUG
      if querytj3.Fields.Fields[0].DisplayName = 'INDISEQ' then//喜好比较特别
         P_INDISEQBz :=true;
      if P_INDISEQBz then
      Sheet.RowCount :=querytjCount + 4
      else
      Sheet.RowCount :=querytjCount + 3;
       //如果有0表示不属于那个城市，画网格的列数
      If tital = 0 Then
        Sheet.ColCount := querytj3Count + querytj.Fields.Count+1
      Else
        Sheet.ColCount := querytj3Count + querytj.Fields.Count+2;
      P_FieldCount :=querytj.Fields.Count;
      Sheet.ColWidths[-1] := 0;
      Sheet.RowHeights[-1] := 0;
      If Terminated Then Exit;
      //0----clBtnShadow,1------clGrayText,2-------clBtnFace
      //画统计的类型，比如行业等
      P_i :=P_FieldCount - 1;
      P_j :=0;
      P_bz :=0;
      P_value :=getname(querytj3.Fields.Fields[0].DisplayName);
      Synchronize(RunUpdate);


      If Terminated Then Exit;
      //画统计的详细项目，第一列第二行开始的几行统计的详细项目
      For i := 0 To P_FieldCount - 2 Do
      Begin
        //线程同步调用
        P_i :=i;
        P_j :=1;//第一列
        P_bz :=1;
        P_value :=getname(querytj.Fields.Fields[i].DisplayName);
        Synchronize(RunUpdate);
        //让统计的详细项目变成两行
        if P_INDISEQBz then
        begin
          P_str :='INDISEQBZ';
          Synchronize(RunModify);
        end;
        If Terminated Then Exit;
      End;

      li :=P_FieldCount -1;
      //画统计类型的详细，比如行业详细名称的企业等
      If querytj2.SQL.Text <> '' Then
      Begin
        if querytj3.Fields.Fields[0].DisplayName = 'INDISEQ' then//喜好比较特别
        begin
          //
          querytj3.First;
          For j := 0 To querytj3Count - 1 Do
          Begin
            //线程同步调用
            //加上大类
            P_i :=li +j;
            P_j :=1;
            P_bz :=2;
            P_value :=querytj3.Fields.Fields[1].AsString;
            if P_value='' then
            P_value :='无法分类';
            Synchronize(RunUpdate);
            If Terminated Then Exit;

            //当前的大类名称和同行的前一个比较，相同加在一起
            P_str :='INDISEQ';
            Synchronize(RunModify);

            If Terminated Then Exit;
            //加上小类
            P_i :=li +j;
            P_j :=2;
            P_bz :=2;
            P_value :=querytj3.Fields.Fields[3].AsString;
            if P_value='' then
            P_value :='无法分类';
            Synchronize(RunUpdate);
            lj :=j;
            If Terminated Then Exit;
            querytj3.Next;
          End;
        end
        else
          begin
            querytj3.First;
            For j := 0 To querytj3Count - 1 Do
            Begin
              //线程同步调用
              P_i :=li +j;
              P_j :=1;
              P_bz :=2;
              P_value :=querytj3.Fields.Fields[1].AsString;
              Synchronize(RunUpdate);
              lj :=j;
              If Terminated Then Exit;
              querytj3.Next;
            End;
          end;
      End;

      //最后一列画上其他与合计
      If tital = 0 Then
      Begin
        P_i :=li + lj +1;
        P_j :=1;
        P_bz :=1;
        P_value :='其他';
        Synchronize(RunUpdate);
        //让统计的详细项目变成两行
        if P_INDISEQBz then
        begin
          P_str :='INDISEQBZ';
          Synchronize(RunModify);
        end;
        If Terminated Then Exit;
        P_i :=li +lj+2;
        P_j :=1;
        P_bz :=1;
        P_value :='合计';
        Synchronize(RunUpdate);
        //让统计的详细项目变成两行
        if P_INDISEQBz then
        begin
          P_str :='INDISEQBZ';
          Synchronize(RunModify);
        end;
        If Terminated Then Exit;
      End;
      //
      if P_INDISEQBz then
      lj := 3
      else
      lj := 2;
      //清空数组

      //画上，第3行开始的对应的统计详细项目的数值
      querytj.First;
      While Not querytj.eof Do
      Begin
        For i := 0 To P_FieldCount - 2 Do
        Begin
            //设置高度把相同的合并
            temp[i]:= querytj.Fields.Fields[i].Text;
  //          sheet.Cells [1,2].Free ;

            //以下判断是哪个统计详细项目的数值
            If (querytj.Fields.Fields[i].DisplayName <> '') and (temp[i] <> '') Then//?
            Begin

              If UPPERcase(querytj.Fields.Fields[i].DisplayName) = uppercase('CityNo')   Then
              begin
                //线程同步调用
                P_i :=i;
                P_j :=lj;
                P_bz :=2;
                P_value :=cityname[strtoint(Copy(temp[i],3,1))];
                if P_value='' then
                P_value :='无法分类';

                Synchronize(RunUpdate);
                //当前的城市名称和同列的前一个比较，相同加在一起
                P_str :='city';
                Synchronize(RunModify);
                If Terminated Then Exit;
              end;
              If UPPERcase(querytj.Fields.Fields[i].DisplayName) = uppercase('off_no_home') Then
              Begin
                If onecity = 0 Then
                begin
                   //线程同步调用
                  P_i :=i;
                  P_j :=lj;
                  P_bz :=2;
                  P_value :=offname[strtoint(Copy(temp[i - 1],3,1)), strtoint(temp[i])];
                  if P_value='' then
                  P_value :='无法分类';
                  Synchronize(RunUpdate);
                   If Terminated Then Exit;
                end
                Else
                  begin
                    //线程同步调用
                    P_i :=i;
                    P_j :=lj;
                    P_bz :=2;
                    P_value :=offname[onecity, strtoint(temp[i])];
                    if P_value='' then
                    P_value :='无法分类';
                    Synchronize(RunUpdate);
                    If Terminated Then Exit;
                  end;
                if i>0 then
                begin
                  P_CityName :=cityname[strtoint(Copy(querytj.Fields.Fields[i -1].text,3,1))];
                  //当前的城市名称和前1列的城市同，再看同列前一行是否同，相同加在一起
                  P_str :='off_no_home';
                  Synchronize(RunModify);
                end
                else
                  begin
                    //单个城市时
                    P_str :='city';//直接调用城市的做法就可以了
                    Synchronize(RunModify);
                  end;
              End;
              If UPPERcase(querytj.Fields.Fields[i].DisplayName) = uppercase('GradeNo') Then
              begin
                //线程同步调用
                P_i :=i;
                P_j :=lj;
                P_bz :=2;
                P_value :=starname[strtoint(temp[i])];
                if P_value='' then
                P_value :='无法分类';
                Synchronize(RunUpdate);
                If Terminated Then Exit;
              end;
              If UPPERcase(querytj.Fields.Fields[i].DisplayName) = uppercase('Custatt') Then
              begin
                //线程同步调用
                P_i :=i;
                P_j :=lj;
                P_bz :=2;
                P_value :=lbname[strtoint(temp[i])];
                if P_value='' then
                P_value :='无法分类';
                Synchronize(RunUpdate);
                If Terminated Then Exit;
              end;
              If UPPERcase(querytj.Fields.Fields[i].DisplayName) = uppercase('sex') Then
                 Begin
                    If UpperCase(Temp[i])=UpperCase('F') Then
                    begin
                      //线程同步调用
                      P_i :=i;
                      P_j :=lj;
                      P_bz :=2;
                      P_value :=xbname[0];
                      if P_value='' then
                      P_value :='无法分类';
                      Synchronize(RunUpdate);
                      If Terminated Then Exit;
                    end;
                    If UpperCase(Temp[i])=UpperCase('M') Then
                    begin
                      //线程同步调用
                      P_i :=i;
                      P_j :=lj;
                      P_bz :=2;
                      P_value :=xbname[1];
                      if P_value='' then
                      P_value :='无法分类';
                      Synchronize(RunUpdate);
                      If Terminated Then Exit;
                    end;
                    If UpperCase(Temp[i])=UpperCase('O') Then
                    begin
                      //线程同步调用
                      P_i :=i;
                      P_j :=lj;
                      P_bz :=2;
                      P_value :=xbname[2];
                      if P_value='' then
                      P_value :='无法分类';
                      Synchronize(RunUpdate);
                      If Terminated Then Exit;
                    end;
                 End;
              If UPPERcase(querytj.Fields.Fields[i].DisplayName) = uppercase('ProductNo') Then
              Begin
                temps := temp[i];
                temp1 := ord(temps[1]);