unit unitStudentProf;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, StdCtrls, Buttons, ExtCtrls;

type
  TfrmStudentProf = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    Label14: TLabel;
    Label15: TLabel;
    Label16: TLabel;
    Label17: TLabel;
    txtId: TEdit;
    txtFname: TEdit;
    txtGen: TEdit;
    txtBday: TEdit;
    txtCourse: TEdit;
    txtStatus: TEdit;
    txtAcad: TEdit;
    txtSem: TEdit;
    txtScholarship: TEdit;
    txtApplied: TEdit;
    memAdd: TMemo;
    txtParents: TEdit;
    memAdd1: TMemo;
    txtGuardian: TEdit;
    memAdd2: TMemo;
    txtMobile: TEdit;
    txtRel: TEdit;
    txtoccid: TEdit;
    txtidapp: TEdit;
    Panel1: TPanel;
    Image1: TImage;
    TabSheet2: TTabSheet;
    ListView1: TListView;
    GroupBox1: TGroupBox;
    txtFilter: TEdit;
    cmbFilter: TComboBox;
    btnGo: TBitBtn;
    txtappid: TEdit;
    TabSheet3: TTabSheet;
    lvLogged: TListView;
    GroupBox2: TGroupBox;
    btnDate: TBitBtn;
    txtpk: TEdit;
    dateFilter: TDateTimePicker;
    btnBrowse: TBitBtn;
    OpenDialog1: TOpenDialog;
    procedure btnDateClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure btnGoClick(Sender: TObject);
    procedure btnBrowseClick(Sender: TObject);
  private
    { Private declarations }
  public
      procedure loadGenInfo;
      procedure loadImage;
      procedure loadSched(occID : Integer);
      procedure loadFirstsem;
      procedure loadSecsem;
      procedure loadLogged;
      procedure loadDate;
  end;

var
  frmStudentProf: TfrmStudentProf;

implementation

uses DM, JPEG;

{$R *.dfm}


procedure TfrmStudentProf.btnDateClick(Sender: TObject);
begin
     loadDate;
end;

procedure TfrmStudentProf.loadGenInfo;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT STUDENT_ID, FULLNAME, GENDER, APP_BDAY, ADDRESS, YEAR_COURSE, APP_SCHOLARSHIP, APP_STATUS, ACADYR, SEM, DATEAPPLIED, PARENTS, ADDRESS, CONPERSON, MOBNO, ADDRESS, REL FROM PROFILE WHERE APPID = :A');
        DataModule1.query.Params.AsString[0] := txtidapp.Text;
        DataModule1.query.Prepare;
        DataModule1.query.Open();

        if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                txtId.Text := DataModule1.query.Fields.AsString[0];
                txtFname.Text := DataModule1.query.Fields.AsString[1];
                txtGen.Text := DataModule1.query.Fields.AsString[2];
                txtBday.Text := DataModule1.query.Fields.AsString[3];
                memAdd.Text := DataModule1.query.Fields.AsString[4];
                txtCourse.Text := DataModule1.query.Fields.AsString[5];
                txtScholarship.Text := DataModule1.query.Fields.AsString[6];
                txtStatus.Text := DataModule1.query.Fields.AsString[7];
                txtAcad.Text := DataModule1.query.Fields.AsString[8];
                txtSem.Text := DataModule1.query.Fields.AsString[9];
                txtApplied.Text := DataModule1.query.Fields.AsString[10];
                txtParents.Text := DataModule1.query.Fields.AsString[11];
                memAdd1.Text := DataModule1.query.Fields.AsString[12];
                txtGuardian.Text := DataModule1.query.Fields.AsString[13];
                txtMobile.Text := DataModule1.query.Fields.AsString[14];
                memAdd2.Text := DataModule1.query.Fields.AsString[15];
                txtRel.Text := DataModule1.query.Fields.AsString[16];
                DataModule1.query.Next;
      end;

                if DataModule1.tr.InTransaction then
                 DataModule1.tr.Commit;
end;

procedure TfrmStudentProf.loadSched(occID : Integer);
var newState : TListItem;
begin

    if DataModule1.tr.InTransaction then
      DataModule1.tr.Commit;

      DataModule1.query.SQL.Clear;
      DataModule1.query.SQL.Add('SELECT OCC_ID, SUBJECT, DES_TITLE, CLASS_SCHEDULE, ROOMS, AYEAR, SEMESTER FROM SP_CLASS_SCHED WHERE OCC_ID = :A');
      DataModule1.query.Params.AsString[0] := IntToStr(occID);
      DataModule1.query.Prepare;
      DataModule1.query.Open();
      ListView1.Items.BeginUpdate;
      ListView1.Items.Clear;

              if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                    newState := ListView1.Items.Add;
                    newState.Caption := DataModule1.query.Fields.AsString[0];
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
                    DataModule1.query.Next;
                    end;

                        if DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;
                             ListView1.Items.EndUpdate;
end;

procedure TfrmStudentProf.loadFirstsem;
var newState : TListItem;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT OCC_ID, SUBJECT, DES_TITLE, CLASS_SCHEDULE, ROOMS, AYEAR, SEMESTER FROM SP_CLASS_SCHED');
        DataModule1.query.SQL.Add('WHERE AYEAR = :A AND OCC_ID = :B AND SEMESTER = :C');
        DataModule1.query.Params.AsString[0] := trim(txtFilter.Text);
        DataModule1.query.Params.AsString[1] := txtappid.Text;
        DataModule1.query.Params.AsString[2] := 'First Semester';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        ListView1.Items.BeginUpdate;
        ListView1.Items.Clear;

              if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                    newState := ListView1.Items.Add;
                    newState.Caption := DataModule1.query.Fields.AsString[0];
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
                    DataModule1.query.Next;
                    end;

                        if DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;
                             ListView1.Items.EndUpdate;
end;

procedure TfrmStudentProf.loadSecsem;
var newState : TListItem;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT OCC_ID, SUBJECT, DES_TITLE, CLASS_SCHEDULE, ROOMS, AYEAR, SEMESTER FROM SP_CLASS_SCHED');
        DataModule1.query.SQL.Add('WHERE AYEAR = :A AND OCC_ID = :B and SEMESTER = :C');
        DataModule1.query.Params.AsString[0] := trim(txtFilter.Text);
        DataModule1.query.Params.AsString[1] := txtappid.Text;
        DataModule1.query.Params.AsString[2] := 'Second Semester';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        ListView1.Items.BeginUpdate;
        ListView1.Items.Clear;

              if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                    newState := ListView1.Items.Add;
                    newState.Caption := DataModule1.query.Fields.AsString[0];
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
                    DataModule1.query.Next;
                    end;

                        if DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;
                             ListView1.Items.EndUpdate;
end;
 {
procedure TfrmSched.loadSummer;
var newState : TListItem;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT OCC_ID, SUBJECT, DES_TITLE, CLASS_SCHEDULE, ROOMS, AYEAR, SEMESTER FROM SP_CLASS_SCHED');
        DataModule1.query.SQL.Add('WHERE AYEAR = :A AND SEMESTER = :B');
        DataModule1.query.Params.AsString[0] := trim(txtFilter.Text);
        DataModule1.query.Params.AsString[1] := txtappid.Text;
        DataModule1.query.Params.AsString[2] := 'Summer';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        ListView1.Items.BeginUpdate;
        ListView1.Items.Clear;

              if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                    newState := ListView1.Items.Add;
                    newState.Caption := DataModule1.query.Fields.AsString[0];
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
                    DataModule1.query.Next;
                    end;

                        if DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;
                             ListView1.Items.EndUpdate;
end; }


procedure TfrmStudentProf.loadLogged;
var newState : TListItem;
begin
      if DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;

          DataModule1.query.SQL.Clear;
          DataModule1.query.SQL.Add('SELECT a.OCC_ID, b.APPID, c.STUDENT_ID, a.DATELOG, a.TIMELOG, d.STATUSDESC, a.NAME, a.ADDRESS, a.CONTACTNO, a.REASON_OVERNYT');
          DataModule1.query.SQL.Add('FROM LOGGED A, OCCUPANTS B,  APPLICANTS C, STATUS D');
          DataModule1.query.SQL.Add('WHERE d.STATUSID = a.STATUSID AND c.APPID = b.APPID and b.OCC_ID = a.OCC_ID AND b.OCC_ID = :A');
          DataModule1.query.Params.AsString[0] := txtpk.Text;
          DataModule1.query.Prepare;
          DataModule1.query.Open();
          lvLogged.Items.BeginUpdate;
          lvLogged.Items.Clear;

          if DataModule1.query.Fields.IsNull[0] then Exit;
          while not DataModule1.query.Eof do begin

         newState := lvLogged.Items.Add;
         newState.Caption := DataModule1.query.Fields.AsString[0];
         newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
         newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
         newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
         newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
         newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
         newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
         newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
         newState.SubItems.Add(DataModule1.query.Fields.AsString[8]);
         newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
         DataModule1.query.Next;
end;
            if DataModule1.tr.InTransaction then
              DataModule1.tr.Commit;
              lvLogged.Items.EndUpdate;
end;

procedure TfrmStudentProf.loadDate;
var newState : TListItem;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID, b.APPID, c.STUDENT_ID, a.DATELOG, a.TIMELOG, d.STATUSDESC, a.NAME, a.ADDRESS, a.CONTACTNO, a.REASON_OVERNYT');
        DataModule1.query.SQL.Add('FROM LOGGED A, OCCUPANTS B,  APPLICANTS C, STATUS D');
        DataModule1.query.SQL.Add('WHERE d.STATUSID = a.STATUSID AND c.APPID = b.APPID and b.OCC_ID = a.OCC_ID AND b.OCC_ID = :A AND a.DATELOG = :B');
        DataModule1.query.Params.AsString[0] := txtpk.Text;
        DataModule1.query.Params.AsString[1] := DateToStr(dateFilter.Date);
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvLogged.Items.Clear;
        lvLogged.Items.BeginUpdate;

        if DataModule1.query.Fields.IsNull[0] then Exit;
          while not DataModule1.query.Eof do begin

          newState := lvLogged.Items.Add;
          newState.Caption := DataModule1.query.Fields.AsString[0];
          newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[8]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
          DataModule1.query.Next;

          if DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;
            lvLogged.Items.EndUpdate;
end;
end;
procedure TfrmStudentProf.FormShow(Sender: TObject);
begin
      loadGenInfo;
      loadImage;
      loadLogged;
end;

procedure TfrmStudentProf.btnGoClick(Sender: TObject);
begin
       if (cmbFilter.Text = 'First Semester') then
    begin
          loadFirstsem;
    end
      else if (cmbFilter.Text = 'Second Semester') then
      begin
          loadSecsem;
      end
        else if (cmbFilter.Text = 'Summer') then
        begin
         //   loadSummer(occID);
        end
end;

procedure TfrmStudentProf.btnBrowseClick(Sender: TObject);
var Stream: TFileStream;
begin
  If OpenDialog1.Execute then begin
    Stream := TFileStream.Create(OpenDialog1.FileName, fmOpenRead);

        if DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;
            DataModule1.tr.StartTransaction;

            DataModule1.query.SQL.Clear;
            DataModule1.query.SQL.Add('UPDATE OCCUPANTS SET IMAGE = :blob WHERE OCC_ID = :b');
            DataModule1.query.ParamsSetBlob('blob',Stream);
            DataModule1.query.Params.AsInteger[1] := StrToInt(txtoccid.Text);
            DataModule1.query.ExecSQL;
            Stream.Free;

            if DataModule1.tr.InTransaction then
                DataModule1.tr.Commit;
                loadImage;
            end;
end;

procedure TfrmStudentProf.loadImage;
var
    Stream: TMemoryStream;
    Jpg: TJpegImage;
begin

    Image1.Picture := nil;

        if DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;

            DataModule1.tr.StartTransaction;
            DataModule1.query.SQL.Clear;
            DataModule1.query.SQL.Add('SELECT IMAGE FROM APPLICANTS WHERE appid =:a');
            DataModule1.query.Params.AsInteger[0] := StrToInt(txtoccid.Text);
            DataModule1.query.Prepare;
            DataModule1.query.Open();

            if DataModule1.query.Fields.BlobData[0] <> nil then begin
                Stream := TMemoryStream.Create;
                Jpg := TJPEGImage.Create;
            try
                DataModule1.query.ReadBlob('IMAGE',Stream);
                Jpg.LoadFromStream(Stream);
                Image1.Picture.Graphic := Jpg;
          finally
            Stream.Free;
            Jpg.Free;
          end;
      end

      else begin
          Image1.Picture := nil;
      end;
        if DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;
end;
end.

