dm_unit.~pas

客户关系管理系统 DELPHI7下的

unit DM_Unit;

interface

uses
  SysUtils, Classes, DB, ADODB, IniFiles, Dialogs, Forms, Variants, Controls, StrUtils,
  DBXpress, FMTBcd, SqlExpr, Provider, DBClient;

type
  TDM = class(TDataModule)
    SQLConn: TSQLConnection;
    qryUsers: TSQLQuery;
    qryTemp: TSQLQuery;
    cdsUsers: TClientDataSet;
    dspUsers: TDataSetProvider;
    qryEmployees: TSQLQuery;
    cdsEmployees: TClientDataSet;
    dspEmployees: TDataSetProvider;
    cdsUsersUSERID: TStringField;
    cdsUsersUSERNAME: TStringField;
    cdsUsersUSERPWD: TStringField;
    dsrUsers: TDataSource;
    dsrEmployees: TDataSource;
    dsrCustomer: TDataSource;
    qryCustomerInfo: TSQLQuery;
    cdsCustomerInfo: TClientDataSet;
    dspCustomerInfo: TDataSetProvider;
    dsrCustomerInfo: TDataSource;
    qryCustEmp: TSQLQuery;
    cdsCustEmp: TClientDataSet;
    dspCustEmp: TDataSetProvider;
    dsrCustEmp: TDataSource;
    qryDepartment: TSQLQuery;
    cdsDepartment: TClientDataSet;
    dspDepartment: TDataSetProvider;
    dsrDepartment: TDataSource;
    qryDepartmentDEPARTMENTID: TStringField;
    qryDepartmentDEPARTMENTDESCN: TStringField;
    qryCustomer: TSQLQuery;
    cdsCustomer: TClientDataSet;
    dspCustomer: TDataSetProvider;
    cdsCustomerCUSTOMERTYPE: TStringField;
    cdsCustomerTYPEDESCN: TStringField;
    cdsCustomerInfoCUSTOMERID: TStringField;
    cdsCustomerInfoCUSTOMERLEVEL: TStringField;
    cdsCustomerInfoCUSTOMER: TStringField;
    cdsCustomerInfoBOOKDATE: TSQLTimeStampField;
    cdsCustomerInfoCONTACT: TStringField;
    cdsCustomerInfoCONTACTTEL: TStringField;
    cdsCustomerInfoADDRESS: TStringField;
    cdsCustomerInfoPOSTCODE: TStringField;
    cdsCustomerInfoFAX: TStringField;
    cdsCustomerInfoNETADDRESS: TStringField;
    cdsCustomerInfoEMAIL: TStringField;
    cdsCustomerInfoREMARK: TStringField;
    cdsCustomerInfoCUSTOMERTYPE: TStringField;
    cdsCustomerInfoWORKRANGE: TStringField;
    cdsCustomerInfoPRODUCTION: TStringField;
    cdsCustomerInfoBANKID: TStringField;
    cdsCustomerInfoBANKNUMBER: TStringField;
    cdsCustomerInfoCORPSIZE: TStringField;
    cdsCustomerInfoCORPKIND: TStringField;
    cdsCustomerInfoCALLING: TStringField;
    cdsCustEmpCUSTEMPID: TStringField;
    cdsCustEmpNAME: TStringField;
    cdsCustEmpSEX: TStringField;
    cdsCustEmpTELPHONE: TStringField;
    cdsCustEmpEMAIL: TStringField;
    cdsCustEmpCUSTPOST: TStringField;
    cdsCustEmpCUSTDEPT: TStringField;
    cdsCustEmpREMARK: TStringField;
    cdsDepartmentDEPARTMENTID: TStringField;
    cdsDepartmentDEPARTMENTDESCN: TStringField;
    cdsEmployeesEMPLOYEEID: TStringField;
    cdsEmployeesNAME: TStringField;
    cdsEmployeesSEX: TStringField;
    cdsEmployeesBIRTHDAY: TSQLTimeStampField;
    cdsEmployeesNATION: TStringField;
    cdsEmployeesISMARRAIGE: TStringField;
    cdsEmployeesIDCARD: TStringField;
    cdsEmployeesLITERACY: TStringField;
    cdsEmployeesSPECIALTY: TStringField;
    cdsEmployeesSCHOOL: TStringField;
    cdsEmployeesTELEPHONE: TStringField;
    cdsEmployeesEMAIL: TStringField;
    cdsEmployeesEMPPOSITION: TStringField;
    cdsEmployeesDEPARTMENT: TStringField;
    cdsEmployeesADDRESS: TStringField;
    cdsEmployeesREMARK: TStringField;
    qryTransactionLog: TSQLQuery;
    cdsTransactionLog: TClientDataSet;
    dspTransactionLog: TDataSetProvider;
    dsrTransactionLog: TDataSource;
    qryTransProduction: TSQLQuery;
    cdsTransProduction: TClientDataSet;
    dspTransProduction: TDataSetProvider;
    dsrTransProduction: TDataSource;
    qryCustomerBooking: TSQLQuery;
    cdsCustomerBooking: TClientDataSet;
    dspCustomerBooking: TDataSetProvider;
    dsrCustomerBooking: TDataSource;
    cdsCustomerBookingID: TIntegerField;
    cdsCustomerBookingBOOKINGTIME: TSQLTimeStampField;
    cdsCustomerBookingBOOKINGTYPE: TSQLTimeStampField;
    cdsCustomerBookingCUSTOMER: TStringField;
    cdsCustomerBookingCOMPANYEMP: TStringField;
    cdsCustomerBookingCUSTEMP: TStringField;
    cdsCustomerBookingTITLE: TStringField;
    cdsCustomerBookingADDRESS: TStringField;
    cdsCustomerBookingCONTENT: TStringField;
    cdsCustomerBookingREMINDTIME: TSQLTimeStampField;
    cdsCustomerBookingISREMIND: TStringField;
    qryProduction: TSQLQuery;
    cdsProduction: TClientDataSet;
    dspProduction: TDataSetProvider;
    dsrProduction: TDataSource;
    cdsProductionPRODUCTIONID: TIntegerField;
    cdsProductionPRODUCTIONDESCN: TStringField;
    cdsProductionISSUEDATE: TSQLTimeStampField;
    cdsProductionQUALITY: TIntegerField;
    cdsProductionSPEC: TStringField;
    cdsProductionPRICE: TFloatField;
    cdsProductionKEEPDATE: TStringField;
    cdsProductionMARKETVALUE: TStringField;
    cdsProductionINTRODUCE: TStringField;
    qryEventLog: TSQLQuery;
    cdsEventLog: TClientDataSet;
    dspEventLog: TDataSetProvider;
    dsrEventLog: TDataSource;
    cdsEventLogID: TIntegerField;
    cdsEventLogEVENTDATE: TSQLTimeStampField;
    cdsEventLogEVENTPROPETY: TStringField;
    cdsEventLogPERSON: TStringField;
    cdsEventLogTITLE: TStringField;
    cdsEventLogCONTENT: TStringField;
    cdsTransactionLogID: TIntegerField;
    cdsTransactionLogTRANSDATE: TSQLTimeStampField;
    cdsTransactionLogCOMPANYEMP: TStringField;
    cdsTransactionLogCUSTEMP: TStringField;
    cdsTransactionLogTRANSTITLE: TStringField;
    cdsTransactionLogTRANSCONTENT: TStringField;
    cdsTransactionLogCONTENTSUM: TStringField;
    cdsTransactionLogTRANSRESULT: TStringField;
    cdsTransProductionID: TIntegerField;
    cdsTransProductionBEGINDATE: TSQLTimeStampField;
    cdsTransProductionENDDATE: TSQLTimeStampField;
    cdsTransProductionPRODUCTIONID: TStringField;
    cdsTransProductionQUANTITY: TIntegerField;
    cdsTransProductionUOM: TStringField;
    cdsTransProductionPRICE: TFloatField;
    cdsTransProductionCOMPANYEMP: TStringField;
    cdsTransProductionCUSTEMP: TStringField;
    cdsTransProductionREMARK: TStringField;
    qryCompanyInfo: TSQLQuery;
    cdsCompanyInfo: TClientDataSet;
    dspCompanyInfo: TDataSetProvider;
    dsrCompanyInfo: TDataSource;
    procedure DataModuleCreate(Sender: TObject);
    procedure qryHouseInfoHeaderPostError(DataSet: TDataSet;
      E: EDatabaseError; var Action: TDataAction);
    procedure DataModuleDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    function CheckUserLogin(txtUserID, txtUserPwd: string): Boolean;
    procedure DataSetFirst(DataSet: TClientDataSet);
    procedure DataSetPrev(DataSet: TClientDataSet);
    procedure DataSetNext(DataSet: TClientDataSet);
    procedure DataSetLast(DataSet: TClientDataSet);
    procedure DataSetInsert(DataSet: TClientDataSet);
    procedure DataSetSave(DataSet: TClientDataSet);
    procedure DataSetCancel(DataSet: TClientDataSet);
    procedure DataSetDelete(DataSet: TClientDataSet);
    procedure DataSetSearch(DataSet: TClientDataSet; sqlText: string);    
    procedure DeleteDetails(TableName, MasterFieldID, MasterFieldValue: string);
    function GenerateNumber(TableName, IDField: string): string;
    function GenerateItemID(TableName, ItemField, MasterKeyID, MasterKeyValue: string): string;
    function UserInfoSetup(UserID, OldPwd, NewPwd, NewPwd2: string): Boolean;
  end;
var
  DM: TDM;
  UserID, UserName, SelectDate: string;
  Flag: Integer;

implementation

uses DBSetup_Unit;            

{$R *.dfm}

function TDM.CheckUserLogin(txtUserID, txtUserPwd: string): Boolean;
begin
  Result := False;
  with qryUsers do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT UserID, UserName, UserPwd FROM Users');
    Open; First;
    if Locate('UserID;UserPwd', VarArrayOf([txtUserID, txtUserPwd]), [] ) then
    begin
      UserID := FieldByName('UserID').AsString;
      UserName := FieldByName('UserName').AsString;
      Result := True;
    end
    else
    begin
      Result := False;
      MessageDlg('登录失败，请检查用户名和密码！', mtWarning, [mbOk], 0);
    end;
  end;
end;

procedure TDM.DataModuleCreate(Sender: TObject);
var
  i: Integer;
begin
{---------打开数据库连接--------------------------}
  SQLConn.Connected := False;
  SQLConn.Params.Clear;
  try
    SQLConn.Params.LoadFromFile(ExtractFilePath(Application.ExeName) + 'DBConn.txt');
    SQLConn.Connected := True;
    if SQLConn.Connected then
    begin
      for i := 0 to ComponentCount - 1 do
      begin
        if Components[i].ClassName = 'TClientDataSet' then
          TClientDataSet(Components[i]).Open;
      end;
    end;
  except
    MessageDlg('打开数据库连接时失败，请查找原因！', mtError, [mbOk], 0);
    frmDBSetup := TfrmDBSetup.Create(Self);
    try
      frmDBSetup.ShowModal;
    finally
      frmDBSetup.Free;
    end;  
    Exit;
  end;
end;

procedure TDM.DataSetCancel(DataSet: TClientDataSet);
begin
//////判断数据集是否为新增或编辑状态并取消操作////////////////
  if DataSet.Active then
    if DataSet.State in [dsInsert, dsEdit] then
    begin
      DataSet.Cancel;
      DataSet.CancelUpdates;
    end;
end;

procedure TDM.DataSetDelete(DataSet: TClientDataSet);
begin
/////判断数据集当前记录是否可以删除//////////////////////////
  if DataSet.Active then
    if DataSet.RecordCount > 0 then
      if MessageDlg('确定删除当前记录吗?', mtWarning, [mbOk, mbCancel], 0) = mrOK then
      begin
        DataSet.Delete;
        DataSet.ApplyUpdates(-1);
      end;
end;

procedure TDM.DataSetFirst(DataSet: TClientDataSet);
begin
////将数据的记录指向第一条////////////////
  if DataSet.Active then
    if not DataSet.Bof then
      DataSet.First;
end;

procedure TDM.DataSetInsert(DataSet: TClientDataSet);
begin
////数据集新增记录//////////////////////////////////
  if DataSet.Active then
    DataSet.Insert;
end;

procedure TDM.DataSetLast(DataSet: TClientDataSet);
begin
////将数据的记录指向最后一条//////////////////////
  if DataSet.Active then
    if not DataSet.Eof then
      DataSet.Last;
end;

procedure TDM.DataSetNext(DataSet: TClientDataSet);
begin
////将数据的记录指向下一条////////////////////////
  if DataSet.Active then
    if not DataSet.Eof then
      DataSet.Next;
end;

procedure TDM.DataSetPrev(DataSet: TClientDataSet);
begin
////将数据的记录指向上一条//////////////////////
  if DataSet.Active then
    if not DataSet.Bof then
      DataSet.Prior;
end;

procedure TDM.DataSetSave(DataSet: TClientDataSet);
begin
////判断数据集是否为新增或编辑状态并保存//////////
  if DataSet.Active then
    if DataSet.State in [dsInsert, dsEdit] then
    begin
      DataSet.Post;
      DataSet.ApplyUpdates(-1);
    end;
end;

function TDM.GenerateNumber(TableName, IDField: string): string;
var
  DateStr: string;
begin
  DateStr := FormatDateTime('YYYYMMDD', Date);
  qryTemp.Close;
  qryTemp.SQL.Clear;
  qryTemp.SQL.Add('SELECT MAX(' + IDField +') + 1 AS MaxID FROM ' + TableName);
  qryTemp.Open;  qryTemp.First;

  if qryTemp.FieldByName('MaxID').AsString = '' then
    Result := '1001'
  else
    Result := DateStr + IntToStr(qryTemp.FieldByName('MaxID').AsInteger + 1);
end;


function TDM.GenerateItemID(TableName, ItemField, MasterKeyID, MasterKeyValue: string): string;
var
  DateStr: string;
begin
  qryTemp.Close;
  qryTemp.SQL.Clear;
  qryTemp.SQL.Add('SELECT RIGHT(MAX(' + ItemField +'), 4) AS MaxID FROM ' + TableName);
  qryTemp.SQL.Add('WHERE ' + MasterKeyID + '=' + QuotedStr(MasterKeyValue));
  qryTemp.Open;  qryTemp.First;

  if qryTemp.FieldByName('MaxID').AsString = '' then
    Result := '1001'
  else
    Result := IntToStr(qryTemp.FieldByName('MaxID').AsInteger + 1);
end;

procedure TDM.qryHouseInfoHeaderPostError(DataSet: TDataSet;
  E: EDatabaseError; var Action: TDataAction);
begin
  MessageDlg('保存数据失败，请检查原因！', mtError, [mbOk], 0);
  Abort;
end;

function TDM.UserInfoSetup(UserID, OldPwd, NewPwd,
  NewPwd2: string): Boolean;
begin
  Result := False;
  with qryUsers do
  begin
    if Active = False then
      Active := True;
//首先判断用户是否合法
    if Locate('UserID;UserPwd', VarArrayOf([UserID, OldPwd]), [] ) then
//判断输入的密码是否正确
      if Trim(FieldByName('UserPwd').AsString) <> Trim(OldPwd) then
        MessageDlg('输入的旧密码不正确！', mtWarning, [mbOk], 0)
      else
      begin
//判断输入的两次密码是否一致
        if Trim(NewPwd) = Trim(NewPwd2) then
        begin
          qryTemp.Close;
          qryTemp.SQL.Clear;
//执行修改密码命令
          qryTemp.SQL.Add('UPDATE Users SET UserPwd='+ QuotedStr(NewPwd));
          qryTemp.SQL.Add('WHERE UserID='+ QuotedStr(Trim(UserID)));
          qryTemp.ExecSQL;
          MessageDlg('修改密码成功！', mtInformation, [mbOk], 0);
          Result := True;
        end
        else
        begin
          MessageDlg('两次输入的新密码不一致！', mtWarning, [mbOk], 0);
        end;
      end;
    end;
end;


procedure TDM.DataModuleDestroy(Sender: TObject);
var
  i: Integer;
begin
  for i := 0 to ComponentCount - 1 do
  begin
    if Components[i].ClassName = 'TClientDataSet' then
      TClientDataSet(Components[i]).Close;
  end;
end;

procedure TDM.DeleteDetails(TableName, MasterFieldID, MasterFieldValue: string);
var
  TD: TTransactionDesc;
begin
  with qryTemp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('DELETE FROM ' + TableName);
    SQL.Add('WHERE '+ MasterFieldID +' = ' + QuotedStr(MasterFieldValue));
    try
      SQLConn.StartTransaction(TD);
      ExecSQL;
      SQLConn.Commit(TD);
    except
      SQLConn.Rollback(TD);
      MessageDlg('删除明细数据时失败！', mtError, [mbOk], 0);
    end;
  end;
end;

procedure TDM.DataSetSearch(DataSet: TClientDataSet; sqlText: string);
begin
  with TADOQuery(DataSet) do
  begin
    Close;
    SQL.Clear;
    SQL.Add(sqlText);
    try
      Open; First;
    except
      MessageDlg('查询数据时失败！', mtError, [mbOk], 0);
    end;
  end;
end;

end.