Devart Forums

Code: Select all

CREATE TABLE users
(
  user_id character varying(8) NOT NULL,
  user_name character varying(40),
  user_manager_id character varying(8),
  passwd character varying(50),
  passwd_expiry_date date,
  db_passwd character varying(50),
  user_status character(1),
  email character varying(64),
  user_location character varying(40),
  user_phone character varying(32),
  user_contact_info character varying(100),
  default_printer_key smallint,
  hint_level smallint DEFAULT 1,
  signon_cnt smallint DEFAULT 0,
  failed_signon_cnt smallint DEFAULT 0,
  last_signon_date timestamp(0) with time zone,
  signon_tag integer DEFAULT 0,
  shell_opts character varying(32),
  create_date timestamp with time zone,
  create_user_id character varying(8),
  last_upd_date timestamp with time zone,
  last_upd_user_id character varying(8),
  location_list character varying(64),
  location_key integer,
  CONSTRAINT users_pkey PRIMARY KEY (user_id),
  CONSTRAINT "$1" CHECK (user_status = 'I'::bpchar OR user_status = 'A'::bpchar OR user_status = 'T'::bpchar OR user_status = 'L'::bpchar)
)
WITHOUT OIDS;

CREATE TABLE user_auths
(
  user_id character varying(8) NOT NULL,
  soc_code character varying(8) NOT NULL,
  access_option character varying(20),
  user_status character(1),
  db_name character varying(32),
  email character varying(64),
  default_printer_key smallint,
  create_date timestamp with time zone,
  create_user_id character varying(8),
  last_upd_date timestamp with time zone,
  last_upd_user_id character varying(8),
  location_key integer,
  CONSTRAINT user_auths_pkey PRIMARY KEY (user_id, soc_code),
  CONSTRAINT ua_db_name_fk FOREIGN KEY (soc_code, db_name)
      REFERENCES dbs (soc_code, db_name) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "$1" CHECK (user_status = 'I'::bpchar OR user_status = 'A'::bpchar OR user_status = 'T'::bpchar)
)
WITHOUT OIDS;

Code: Select all

  object AdminPgConnection1: TPgConnection
    Username = 'user'
    Password = 'password'
    Server = '192.168.0.100'
    LoginPrompt = False
    AfterConnect = PgConnection1AfterConnect
    BeforeConnect = PgConnection1BeforeConnect
    AfterDisconnect = PgConnection1AfterDisconnect
    Database = 'admin_db'
    Options.ApplicationName = 'pgDACTest'
    ConnectionTimeout = 1
    Left = 584
    Top = 56
  end

Code: Select all

---------------------------
Debugger Exception Notification
---------------------------
Project MyQ.exe raised exception class EOleException with message 'type "e" does not exist'. Process stopped. Use Step or Run to continue.
---------------------------
OK   Help   
---------------------------

Code: Select all

  object AuthenticationABRIpgQuery1: TPgQuery
    Connection = AdminPgConnection1
    SQL.Strings = (
      'select u.*, ua.soc_code, ua.access_option '
      'from users u '
      'left join user_auths ua on u.user_id = ua.user_id '
      'where u.user_status = ''A'' '
      'and u.user_id = :UserID '
      'and passwd = :Password;')
    ReadOnly = True
    Options.ExtendedFieldsInfo = False
    Options.UnknownAsString = True
    Options.UnpreparedExecute = True
    Left = 184
    Top = 304
    ParamData = 
  end

Code: Select all

11:20:38.191	Start: 
11:20:38.237	select u.*, ua.soc_code, ua.access_option 
from users u 
left join user_auths ua on u.user_id = ua.user_id 
where u.user_status = 'A' 
and u.user_id = :UserID 
and passwd = :Password;
:UserID(String[2],IN)='aj' 
:Password(String[16],IN)='vG85YqZ9zSziXw=='
11:20:38.316	Error: type "e" does not exist
11:20:41.784	Commit: 

Code: Select all

set search_path = aull, breedplan, public; 
set session authorization sk; 

/* if the header record doesn't already exist for this calving year, create it */

insert into membership_years (soc_code, members_key, membership_year,
           membership_rec_type, membership_type, membership_status,
           date_assessed,  membership_key)

select zt.soc_code, zt.members_key, 2011, 2,
cast('6' || cast('4' as varchar) as integer), 1,
   local_timestamp_f('AULL'), cast(next_seq_no_f('AULL', 'MY', 'MEMBERSHIP_KEY') as integer) as membership_key
from ztmp.ztmp_251077_memberkeys zt
where zt.members_key not in
(select ztt.members_key from ztmp.ztmp_251077_memberkeys ztt
        join  membership_years my using (soc_code, members_key)
        where ztt.soc_code = 'AULL'
        and my.membership_rec_type = 2
        and my.membership_type = cast('6' || cast('4' as varchar) as integer)
        and my.membership_year = 2011);

/*===============================================================*/
/* save stuff away for the report  */
/* note: this needs fixing because you could have multiple member link rows and this table should only
ever return ONE row */
create table ztmp.ztmp_251077_rpthdgs as
select me.soc_code,
       me.member_ident,
       me.members_key,
       mp.member_ident as primary_herd,
       mp.memb_name as primary_name,
       me.stud_letters,
       me.pref_language,
       me.stud_prefix,
       me.soc_me_cb_1 as invent_status,
       me.soc_me_ck_4_flag as full_invent,
      (select alt_member_ident from member_cross_refs where soc_code='AULL' and alt_type=3 and members_key=me.members_key limit 1) as nlis_pic,       
       me.memb_name,
       me.address_1,
       me.address_2,
       me.address_3,
       param_to_description_f(me.soc_code, 'STATE', me.state) as me_state,
       me.postcode,
       me.phone_priv,
       me.phone_fax,
       cast('2011' as varchar(4)) as calv_yr ,
       cast(param_to_description_f(me.soc_code, 'INVENTORY_SEASON', '4') as varchar(16)) as calv_seas,
       cm.company_name,
       cm.address_1 as cm_address_1,  cm.address_2 as cm_address_2,
       cm.address_3 as cm_address_3,  cm.postcode  as cm_postcode,
       param_to_description_f(cm.soc_code, 'STATE',   cm.state) as cm_state,
       param_to_description_f(cm.soc_code, 'COUNTRY', cm.country) as cm_country,
       cm.phone_bus as cm_phone_bus,
       cm.phone_bus_2 as cm_phone_bus_2,
       cm.phone_fax as cm_phone_fax,
       cm.email     as cm_email ,
       my.membership_key as invent_year_key,
       case when 'N' = 'Y' then 'Y' else 'N' end as do_update_counts,
       to_char(local_timestamp_f(me.soc_code) ,'FMMonth dd, yyyy') as issue_date,
       case when 'AULL' = 'CANG' then me.soc_me_cb_2 else cast(null as int2) end as anim_rpt_sort_order    
from ztmp.ztmp_251077_memberkeys zt
join members me on me.soc_code = zt.soc_code and me.members_key = zt.members_key
join companies cm on cm.soc_code = zt.soc_code
left join member_links ml on ml.soc_code = zt.soc_code and ml.secondary_members_key = me.members_key and ml.member_link_type = 2
          and member_link_exists_f(ml.soc_code,ml.primary_members_key,ml.secondary_members_key,2,'20110831')
left join members mp on mp.soc_code = zt.soc_code and ml.primary_members_key = mp.members_key
join membership_years my on my.soc_code = zt.soc_code
   and my.members_key = me.members_key
   and my.membership_year = '2011'
   and my.membership_rec_type = 2
   and my.membership_type = cast('6' || cast('4' as varchar) as integer)
where zt.soc_code = 'AULL' ;


/*===============================================================*/
/* if we are re-calculating figures, delete the old ones from membership_inventory */
delete from membership_inventory
where soc_code = 'AULL'
and membership_key in
(select invent_year_key from
ztmp.ztmp_251077_rpthdgs hdg
where hdg.do_update_counts = 'Y');

/*===============================================================*/
/* if you are recalculating figures, gather up all currently owned animals for the herds */
/* if not updating, pass the empty string into members_inventory_load_f so that an empty keys table gets created */

select members_inventory_load_f(case when
exists(select 1
 from ztmp.ztmp_251077_rpthdgs hdg
 where hdg.do_update_counts = 'Y' group by 1) then 'ztmp_251077_memberkeys' else '' end, 'ztmp_251077_animalkeys', cast('20110831' as date))
  ;


/*===============================================================*/
/* to get in the inventory you must be
1. Female
2. Active

4. of correct age ie. older than the minimum date of birth entered by the user, and younger than that date - 16 years
*/
insert into membership_inventory
( soc_code,membership_key,animals_key, rego_status)

select mi.soc_code, hdg.invent_year_key, an.animals_key, an.rego_status
from ztmp.ztmp_251077_animalkeys mi
join animals an on an.soc_code = mi.soc_code and an.animals_key = mi.animals_key
join ztmp.ztmp_251077_rpthdgs hdg on hdg.soc_code = mi.soc_code and hdg.members_key = mi.members_key
where an.sex = 1
and an.anim_type  5
and mi.anim_status = 1 
and held_status is null
and ((an.date_of_birth <= 20110831 and an.date_of_birth is not null)
     or (an.calving_year <= extract(year from cast('20110831' as date)) and an.date_of_birth is null))
and (get_global_f(an.soc_code, 'inventory_registered_only') is distinct from 'true' or an.rego_status = 11)

and hdg.do_update_counts = 'Y'
order by hdg.member_ident,
        an_format_ilr1_ident_f(an.soc_code, 'out', null, an.anim_ident),
        anim_type,
        param_to_abbrev_f(an.soc_code,'SEX',an.sex) desc,
        coalesce(an.calving_year,extract('year' from an.date_of_birth)),
        cast (nullif(substring(an.tattoo from '^(?:[^[:digit:]]*)([[:digit:]]*)'),'') as integer),
        substring(an.tattoo from '^[^ ]*' );

/* analyze the report headings so it can join it properly */
select sys_analyse_table_f('ztmp.ztmp_251077_rpthdgs');

/*===============================================================*/
/* make this the rpt bit then */
create table ztmp.ztmp_251077_inventanims as
select hdg.members_key,
       hdg.member_ident,
       hdg.primary_herd,
       hdg.primary_name,
       hdg.stud_letters,
       hdg.memb_name,
       hdg.address_1,
       hdg.address_2,
       hdg.address_3,
       hdg.me_state,
       hdg.postcode,
       hdg.phone_priv,
       hdg.phone_fax,
       hdg.calv_yr ,
       hdg.calv_seas,
       hdg.company_name,
       hdg.cm_address_1,
       hdg.cm_address_2,
       hdg.cm_address_3,
       hdg.cm_postcode,
       hdg.cm_state,
       hdg.cm_country,
       hdg.cm_phone_bus,
       hdg.cm_phone_bus_2,
       hdg.cm_phone_fax,
       hdg.cm_email ,
       hdg.issue_date,
       hdg.pref_language,
       hdg.anim_rpt_sort_order,
       hdg.stud_prefix,
       hdg.nlis_pic,
       hdg.invent_status,
       cast(case when full_invent then '23.10' else '9.90' end as varchar(5)) as invent_fee,
       cast(case when full_invent then '20.90' else '8.80' end as varchar(5)) as invent_fee_disc,
        mi.*,
        an_format_ident_f(an.soc_code,an.animals_key) as anim_ident,
        an.tattoo,
        an.herd_id,
        an.anim_name,
        an.calving_year,
        an.inventory_season,
        substr(get_global_f(an.soc_code, 'year_letters')
            /*,cast(extract(year from an.date_of_birth) as integer) */
            , cast('2011' as integer)
            -get_global_int_f(an.soc_code, 'year_letter_base')+1,1) as anim_year_letter,
        param_to_abbrev_f(an.soc_code,'SEX',an.sex) as sex_abbr,
        param_to_abbrev_f(an.soc_code,'REGO_STATUS',an.rego_status) as reg_abbr,
    an_format_ilr1_ident_f(an.soc_code, 'out', null, an.anim_ident) as sort_anim_ident,
    cast(2011 as int) as inventory_year,
    cast(param_to_description_f(an.soc_code, 'SEASON', '4') as varchar(16)) as invent_season_desc,
    cast(sys_year_code_f('AULL', 2011) as varchar(1))as current_year_code,
    cast(2011 as integer) as current_year,
    an.anim_type as reg_order,
    sire_tattoo,
    sire_ident,
    sire_ai,
    sire_date

from   ztmp.ztmp_251077_rpthdgs hdg
join membership_inventory mi on mi.soc_code = hdg.soc_code and mi.membership_key = hdg.invent_year_key
join animals an  on an.soc_code = mi.soc_code and an.animals_key = mi.animals_key

left join (select sd.dam_key as animals_key, an1.tattoo as sire_tattoo, an1.anim_ident as sire_ident, case when sd.service_type = 2 then 'Y' else null end as sire_ai, sd.from_service_date as sire_date
      from service_details sd
      join animals an1 on an1.soc_code = sd.soc_code and an1.animals_key = sd.sire_key
      join ztmp.ztmp_251077_rpthdgs hdg on hdg.soc_code = an1.soc_code
      join membership_inventory mi on mi.soc_code = hdg.soc_code and mi.membership_key = hdg.invent_year_key
      where sd.dam_key = mi.animals_key
      and sd.from_service_date = (select from_service_date from service_details sd1
                                  where sd1.soc_code= hdg.soc_code and sd1.dam_key = sd.dam_key
                                  and coalesce(sd1.service_status,0) = 0
                                  and sd1.from_service_date between cast(cast(to_number(hdg.calv_yr,'9999') - 1 as char(4)) || '-01-01' as date)
                                                                and cast(cast(to_number(hdg.calv_yr,'9999') - 1 as char(4)) || '-12-31' as date)
                                  order by sd1.from_service_date asc limit 1))
       sd on sd.animals_key = mi.animals_key ;

/* and t3941.adls_flag = 'N'  and t3941.forms_flag = 'Y' */