unitdm.pas

串口短信猫收发软件。支持西门子TC35i设备

    Con.BeginTrans;
    DQ.Close;
    DQ.SQL.Text := sql;
    DQ.Parameters.ParamByName('textgroup_name').value := textgroup_name;
    DQ.ExecSQL;
    Con.CommitTrans;
    DQ.Close;
  except
    Con.RollbackTrans;
    DQ.Close;
  end;
  Result := True;
end;

function TDM.DelTextGroup(textgroup_id: string): boolean;
var
  sql1, sql2: string;
begin
  Result := False;
  try
    sql1 := ' delete from Tab_textgroup where textgroup_id=:textgroup_id ';
    sql2 := ' delete from Tab_text where textgroup_id=:textgroup_id ';
    Con.BeginTrans;

    DQ.Close;
    DQ.SQL.Text := sql1;
    DQ.Parameters.ParamByName('textgroup_id').value := textgroup_id;
    DQ.ExecSQL;

    DQ.Close;
    DQ.SQL.Text := sql2;
    DQ.Parameters.ParamByName('textgroup_id').value := textgroup_id;
    DQ.ExecSQL;

    Con.CommitTrans;
    DQ.Close;
  except
    Con.RollbackTrans;
    DQ.Close;
  end;
  Result := True;
end;

function TDM.UpdateTextGroup(textgroup_id, textgroup_name: string): boolean;
var
  sql: string;
begin
  Result := False;
  try
    sql := ' update Tab_textgroup set textgroup_name=:textgroup_name where textgroup_id=:textgroup_id ';
    Con.BeginTrans;
    DQ.Close;
    DQ.SQL.Text := sql;
    DQ.Parameters.ParamByName('textgroup_name').value := textgroup_name;
    DQ.Parameters.ParamByName('textgroup_id').value := textgroup_id;
    DQ.ExecSQL;
    Con.CommitTrans;
    DQ.Close;
  except
    Con.RollbackTrans;
    DQ.Close;
  end;
  Result := True;
end;

procedure TDM.DSet_AddrGroupBeforePost(DataSet: TDataSet);
begin
  if DSet_AddrGroup.FieldByName('addrgroup_name').AsString='' then
  begin
    Application.MessageBox(PChar('"' + DSet_AddrGroup.FieldByName('addrgroup_name').DisplayLabel + '" 不能为空！'),'提示',MB_OK + MB_ICONWARNING);
    Abort;
  end;
end;

procedure TDM.DSet_TextGroupBeforePost(DataSet: TDataSet);
begin
  if DSet_TextGroup.FieldByName('textrgroup_name').AsString='' then
  begin
    Application.MessageBox(PChar('"' + DSet_TextGroup.FieldByName('textrgroup_name').DisplayLabel + '" 不能为空！'),'提示',MB_OK + MB_ICONWARNING);
    Abort;
  end;
end;

procedure TDM.DSet_AddrBeforePost(DataSet: TDataSet);
begin
  if DSet_Addr.FieldByName('addr_name').AsString='' then
  begin
    Application.MessageBox(PChar('"' + DSet_Addr.FieldByName('addr_name').DisplayLabel + '" 不能为空！'),'提示',MB_OK + MB_ICONWARNING);
    Abort;
  end;
  if DSet_Addr.FieldByName('addr_tel').AsString='' then
  begin
    Application.MessageBox(PChar('"' + DSet_Addr.FieldByName('addr_tel').DisplayLabel + '" 不能为空！'),'提示',MB_OK + MB_ICONWARNING);
    Abort;
  end;
end;

procedure TDM.DSet_TextBeforePost(DataSet: TDataSet);
begin
  if DSet_Text.FieldByName('text_name').AsString='' then
  begin
    Application.MessageBox(PChar('"' + DSet_Text.FieldByName('text_name').DisplayLabel + '" 不能为空！'),'提示',MB_OK + MB_ICONWARNING);
    Abort;
  end;
end;

function TDM.SelectAddr(var ts: TStringList): boolean;
var
  t: TStrings;
begin
  Result := False;
  if ts=nil then Exit;
  while ts.Count>0 do
  begin
    TStrings(ts.Objects[ts.Count-1]).Free;
    ts.Delete(ts.Count-1);
  end;

  try
    DQ.Close;
    DQ.SQL.Text := ' select * from Tab_addr ';
    DQ.Open;
    while not DQ.Eof do
    begin
      t := TStringList.Create;
      t.Add(DQ.FieldByName('addr_id').AsString);
      t.Add(DQ.FieldByName('addrgroup_id').AsString);
      t.Add(DQ.FieldByName('addr_name').AsString);
      t.Add(DQ.FieldByName('addr_tel').AsString);
      t.Add(DQ.FieldByName('addr_remark').AsString);
      ts.AddObject(DQ.FieldByName('addr_name').AsString, t);
      DQ.Next;
    end;
  finally
    DQ.Close;
  end;
  Result := True;
end;

function TDM.SelectAddrGroup(var ts: TStringList): boolean;
var
  t: TStrings;
begin
  Result := False;
  if ts=nil then Exit;
  while ts.Count>0 do
  begin
    TStrings(ts.Objects[ts.Count-1]).Free;
    ts.Delete(ts.Count-1);
  end;
  try
    DQ.Close;
    DQ.SQL.Text := ' select * from Tab_addrgroup ';
    DQ.Open;
    while not DQ.Eof do
    begin
      t := TStringList.Create;
      t.Add(DQ.FieldByName('addrgroup_id').AsString);
      t.Add(DQ.FieldByName('addrgroup_name').AsString);
      ts.AddObject(DQ.FieldByName('addrgroup_name').AsString, t);
      DQ.Next;
    end;
  finally
    DQ.Close;
  end;
  Result := True;
end;

function TDM.SelectText(var ts: TStringList): boolean;
var
  t: TStrings;
begin
  Result := False;
  if ts=nil then Exit;
  while ts.Count>0 do
  begin
    TStrings(ts.Objects[ts.Count-1]).Free;
    ts.Delete(ts.Count-1);
  end;
  try
    DQ.Close;
    DQ.SQL.Text := ' select * from Tab_text ';
    DQ.Open;
    while not DQ.Eof do
    begin
      t := TStringList.Create;
      t.Add(DQ.FieldByName('text_id').AsString);
      t.Add(DQ.FieldByName('textgroup_id').AsString);
      t.Add(DQ.FieldByName('text_name').AsString);
      ts.AddObject(DQ.FieldByName('text_name').AsString, t);
      DQ.Next;
    end;
  finally
    DQ.Close;
  end;
  Result := True;
end;

function TDM.SelectTextGroup(var ts: TStringList): boolean;
var
  t: TStrings;
begin
  Result := False;
  if ts=nil then Exit;
  while ts.Count>0 do
  begin
    TStrings(ts.Objects[ts.Count-1]).Free;
    ts.Delete(ts.Count-1);
  end;
  try
    DQ.Close;
    DQ.SQL.Text := ' select * from Tab_textgroup ';
    DQ.Open;
    while not DQ.Eof do
    begin
      t := TStringList.Create;
      t.Add(DQ.FieldByName('textgroup_id').AsString);
      t.Add(DQ.FieldByName('textgroup_name').AsString);
      ts.AddObject(DQ.FieldByName('textgroup_name').AsString, t);
      DQ.Next;
    end;
  finally
    DQ.Close;
  end;
  Result := True;
end;

procedure TDM.SelectRequest(dtStart, dtEnd: string; send_result: integer);
begin
  try
    if Trim(dtStart)='' then dtStart := FormatDateTime(FORMATDATETIME_DEF, Now-1000);
    if Trim(dtEnd)='' then dtEnd := FormatDateTime(FORMATDATETIME_DEF, Now+1000);
    DSet_Request.Close;
    DSet_Request.CommandText := 'select * from Tab_request where req_time>=:dtStart and req_time<=:dtEnd ';
    if send_result<>-1 then
      DSet_Request.CommandText := DSet_Request.CommandText + ' and send_result='+IntToStr(send_result);
    DSet_Request.Parameters.ParamByName('dtStart').Value := dtStart;
    DSet_Request.Parameters.ParamByName('dtEnd').Value := dtEnd;
    DSet_Request.Open;
  except
    DSet_Request.Close;
  end;
end;

procedure TDM.SelectResponse(dtStart, dtEnd: string);
begin
  try
    if Trim(dtStart)='' then dtStart := FormatDateTime(FORMATDATETIME_DEF, Now-1000);
    if Trim(dtEnd)='' then dtEnd := FormatDateTime(FORMATDATETIME_DEF, Now+1000);
    DSet_Response.Close;
    DSet_Response.CommandText := 'select * from Tab_response where reci_time>=:dtStart and reci_time<=:dtEnd ';
    DSet_Response.Parameters.ParamByName('dtStart').Value := dtStart;
    DSet_Response.Parameters.ParamByName('dtEnd').Value := dtEnd;
    DSet_Response.Open;
  except
    DSet_Response.Close;
  end;
end;

function TDM.QueryCountChart(dtStart, dtEnd: string; var ReqTot: integer; var ResTot: integer;
 var arRequest: array of TQueryCountChart; var arResponse: array of TQueryCountChart): boolean;
var
  i: integer;
  sYear, sMonth, sDay: string;
begin
  Result := False;
  try
    if Trim(dtStart)='' then Exit;
    if Trim(dtEnd)='' then Exit;
    //发送总数
    ReqTot := 0;
    DQ.Close;
    DQ.SQL.Text := ' select substr(A.req_time, 1, 10) as iYear, count(*) as iCount '
                  +' from Tab_request A'
                  +' where A.req_time>=:dtStart and A.req_time<=:dtEnd '
                  +' group by substr(A.req_time, 1, 10) ';
{$IFDEF DATABASE_ACCESS2000}
    DQ.SQL.Text := ' select year(A.req_time) as iYear, month(A.req_time) as iMonth, day(A.req_time) as iDay , count(*) as iCount '
                  +' from Tab_request A'
                  +' where A.req_time>=:dtStart and A.req_time<=:dtEnd '
                  +' group by year(A.req_time), month(A.req_time), day(A.req_time) ';
{$ENDIF}
    DQ.Parameters.ParamByName('dtStart').Value := dtStart;
    DQ.Parameters.ParamByName('dtEnd').Value := dtEnd;
    DQ.Open;
    i := 0;
    ReqTot := DQ.RecordCount;
    DQ.First;
    while not DQ.Eof do
    begin
      sYear := DQ.FieldByName('iYear').AsString;
      if Length(sYear)=2 then sYear := Copy(FormatDateTime('YYYY', Now), 1, 2) + sYear;
      sMonth := DQ.FieldByName('iMonth').AsString;
      if Length(sMonth)<2 then sMonth := '0' + sMonth;
      sDay := DQ.FieldByName('iDay').AsString;
      if Length(sDay)<2 then sDay := '0' + sDay;
      arRequest[i].iYear := StrToFloat(sYear + sMonth + sDay);
      arRequest[i].iCount := DQ.FieldByName('iCount').AsFloat;
      Inc(i);
      DQ.Next;
    end;

    //接收总数
    ResTot := 0;
    DQ.Close;
    DQ.SQL.Text := ' select substr(A.reci_time, 1, 10) as iYear, count(*) as iCount '
                  +' from Tab_response A'
                  +' where A.reci_time>=:dtStart and A.reci_time<=:dtEnd '
                  +' group by substr(A.reci_time, 1, 10) ';
{$IFDEF DATABASE_ACCESS2000}
    DQ.SQL.Text := ' select year(A.reci_time) as iYear, month(A.reci_time) as iMonth, day(A.reci_time) as iDay , count(*) as iCount '
                  +' from Tab_response A'
                  +' where A.reci_time>=:dtStart and A.reci_time<=:dtEnd '
                  +' group by year(A.reci_time), month(A.reci_time), day(A.reci_time) ';
{$ENDIF}
    DQ.Parameters.ParamByName('dtStart').Value := dtStart;
    DQ.Parameters.ParamByName('dtEnd').Value := dtEnd;
    DQ.Open;
    i := 0;
    ResTot := DQ.RecordCount;
    DQ.First;
    while not DQ.Eof do
    begin
      sYear := DQ.FieldByName('iYear').AsString;
      if Length(sYear)=2 then sYear := Copy(FormatDateTime('YYYY', Now), 1, 2) + sYear;
      sMonth := DQ.FieldByName('iMonth').AsString;
      if Length(sMonth)<2 then sMonth := '0' + sMonth;
      sDay := DQ.FieldByName('iDay').AsString;
      if Length(sDay)<2 then sDay := '0' + sDay;
      arResponse[i].iYear := StrToFloat(sYear + sMonth + sDay);
      arResponse[i].iCount := DQ.FieldByName('iCount').AsFloat;
      Inc(i);
      DQ.Next;
    end;
  finally
    DQ.Close;
  end;
  Result := True; 
end;

function TDM.QueryCount(dtStart, dtEnd: string; var RequestTotNum, RequestYesNum,
  RequestNoNum, ResponseTotNum: string): boolean;
begin
  Result := False;
  try
    if Trim(dtStart)='' then dtStart := FormatDateTime(FORMATDATETIME_DEF, Now-1000);
    if Trim(dtEnd)='' then dtEnd := FormatDateTime(FORMATDATETIME_DEF, Now+1000);
    //发送总数
    DQ.Close;
    DQ.SQL.Text := 'select count(*) as totnum from Tab_request where req_time>=:dtStart and req_time<=:dtEnd ';
    DQ.Parameters.ParamByName('dtStart').Value := dtStart;
    DQ.Parameters.ParamByName('dtEnd').Value := dtEnd;
    DQ.Open;
    RequestTotNum := DQ.FieldByName('totnum').AsString;
    //成功发送总数
    DQ.Close;
    DQ.SQL.Text := 'select count(*) as totnum from Tab_request where send_result=1 and req_time>=:dtStart and req_time<=:dtEnd ';
    DQ.Parameters.ParamByName('dtStart').Value := dtStart;
    DQ.Parameters.ParamByName('dtEnd').Value := dtEnd;
    DQ.Open;
    RequestYesNum := DQ.FieldByName('totnum').AsString;
    //未发送总数
    DQ.Close;
    DQ.SQL.Text := 'select count(*) as totnum from Tab_request where send_result=0 and req_time>=:dtStart and req_time<=:dtEnd ';
    DQ.Parameters.ParamByName('dtStart').Value := dtStart;
    DQ.Parameters.ParamByName('dtEnd').Value := dtEnd;
    DQ.Open;
    RequestNoNum := DQ.FieldByName('totnum').AsString;
    //接收总数
    DQ.Close;
    DQ.SQL.Text := 'select count(*) as totnum from Tab_response where reci_time>=:dtStart and reci_time<=:dtEnd ';
    DQ.Parameters.ParamByName('dtStart').Value := dtStart;
    DQ.Parameters.ParamByName('dtEnd').Value := dtEnd;
    DQ.Open;
    ResponseTotNum := DQ.FieldByName('totnum').AsString;
  finally
    DQ.Close;
  end;
  Result := True;
end;

function TDM.HasFee: boolean;
var
  sql1: string;
begin
  Result := False;
  //费用控制
  if FbLimitFee then//费用控制开关(0-不限额,1-限额)
  begin
    sql1 := ' select para_value from Tab_basepara where para_name=''feebalance'' ';
    try
      DQ.Close;
      DQ.SQL.Text := sql1;
      DQ.Open;
      if (DQ.RecordCount<=0) or (DQ.FieldByName('para_value').AsFloat<=0) then
      begin
        DQ.Close;
        if FbAutoStopOverFee then UpdateRequestOverFee;                             //费用超额自动停止发送短信
        Exit;
      end;