view.sql

Delphi6开发的HIS程序

create or replace view v_pstockmedicine as
select pstockmedicine.pstockmedicineid            psm_pstockmedicineid,
       pstockmedicine.samount                     psm_samount,
       pstockmedicine.amount                      psm_amount,
       pstockmedicine.created                     psm_created,
       pstockmedicine.updated                     psm_updated,
       pstockmedicine.medicinestockid             psm_medicinestockid,
       pstockmedicine.price                       psm_price,
       medicinestock.medicinestockid              ms_medicinestockid,
       medicinestock.created                      ms_created,
       medicinestock.updated                      ms_updated,
       medicinestock.amount                       ms_amount,
       medicinestock.medicineid                   ms_medicineid,
       medicine.medicineid                        m_medicineid,
       medicine.code                              m_code,
       medicine.topqty                            m_topqty,
       medicine.downqty                           m_downqty,
       medicine.memo                              m_memo,
       medicine.alais                             m_alais,
       medicine.model                             m_model,
       medicine.pinyin                            m_pinyin,
       medicine.caption                           m_caption,
       medicine.doseid                            m_doseid,
       medicine.unitsid                           m_unitsid,
       medicine.usemethodid                       m_usemethodid,
       medicine.medicinecatalogid                 m_medicinecatalogid,
       medicine.minunitsid                        m_minunitsid,
       medicine.packamount                        m_packamount,
       dose.caption                               d_caption,
       units.caption                              u_caption,
       minunits.caption                           mu_caption,
       usemethod.caption                          um_caption,
       medicinecatalog.caption                    mc_caption,
       pstockmedicine.amount minamount
from   pstockmedicine, medicinestock, medicine, dose, units, units minunits, usemethod, medicinecatalog
where  pstockmedicine.medicinestockid = medicinestock.medicinestockid and
       medicinestock.medicineid = medicine.medicineid and
       medicine.doseid = dose.doseid (+) and
       medicine.unitsid = units.unitsid and
       medicine.minunitsid = minunits.unitsid and
       medicine.usemethodid = usemethod.usemethodid (+) and
       medicine.medicinecatalogid = medicinecatalog.medicinecatalogid;

create view v_pstockinorder as
select pstockinorder.pstockinorderid,
       pstockinorder.checkindate,
       pstockinorder.created,
       pstockinorder.updated,
       pstockinorder.isapprove,
       pstockinorder.isactive,
       pstockinorder.isvalid,
       pstockinorder.approvedate,
       pstockinorder.pstockmedicineid,
       pstockinorder.medicinestockid,
       pstockinorder.createby,
       pstockinorder.updateby,
       pstockinorder.approveby,
       pstockinorder.stockoutorderid,
       pstockinorder.amount,
       medicinestock.medicineid   ms_medicineid,
       medicine.medicineid        m_medicineid,
       medicine.code              m_code,
       medicine.topqty            m_topqty,
       medicine.downqty           m_downqty,
       medicine.memo              m_memo,
       medicine.alais             m_alais,
       medicine.model             m_model,
       medicine.pinyin            m_pinyin,
       medicine.caption           m_caption,
       medicine.doseid            m_doseid,
       medicine.unitsid           m_unitsid,
       medicine.usemethodid       m_usemethodid,
       medicine.medicinecatalogid m_medicinecatalogid,
       medicine.minunitsid        m_minunitsid,
       medicine.packamount        m_packamount
from   pstockinorder, medicinestock, medicine
where  pstockinorder.medicinestockid = medicinestock.medicinestockid and
       medicinestock.medicineid = medicine.medicineid;

create or replace view v_pstockoutorder as
select pstockoutorder.*,
medicine.medicineid        m_medicineid,
medicine.code              m_code,
medicine.topqty            m_topqty,
medicine.downqty           m_downqty,
medicine.memo              m_memo,
medicine.alais             m_alais,
medicine.model             m_model,
medicine.pinyin            m_pinyin,
medicine.caption           m_caption,
medicine.doseid            m_doseid,
medicine.unitsid           m_unitsid,
medicine.usemethodid       m_usemethodid,
medicine.medicinecatalogid m_medicinecatalogid,
medicine.minunitsid        m_minunitsid,
medicine.packamount        m_packamount,
department.caption         d_caption,
units.caption              u_catpion
from pstockoutorder, department, pstockmedicine, medicinestock, medicine, units
where pstockoutorder.departmentid = department.departmentid and
      pstockoutorder.pstockmedicineid = pstockmedicine.pstockmedicineid and
      pstockmedicine.medicinestockid = medicinestock.medicinestockid and
      medicinestock.medicineid = medicine.medicineid and
      medicine.minunitsid = units.unitsid;

create view v_pstockoutorder_rpt as
select m_caption, m_model, u_catpion, d_caption, sum(amount) amount
from v_pstockoutorder
group by m_caption, m_model, u_catpion, d_caption;

create or replace view v_pscomedicine as
select pscomedicine.pscomedicineid,
       pscomedicine.cquantity,
       pscomedicine.oamount,
       pscomedicine.created,
       pscomedicine.updated,
       pscomedicine.isapprove,
       pscomedicine.approvedate,
       pscomedicine.pstockmedicineid,
       pscomedicine.createby,
       pscomedicine.updateby,
       pscomedicine.approveby,
       medicine.medicineid m_medicineid,
       medicine.code m_code,
       medicine.topqty m_topqty,
       medicine.downqty m_downqty,
       medicine.memo m_memo,
       medicine.alais m_alais,
       medicine.model m_model,
       medicine.pinyin m_pinyin,
       medicine.caption m_caption,
       medicine.doseid m_doseid,
       medicine.unitsid m_unitsid,
       medicine.usemethodid m_usemethodid,
       medicine.medicinecatalogid m_medicinecatalogid,
       medicine.minunitsid m_minunitsid,
       medicine.packamount m_packamount
from   pscomedicine, pstockmedicine, medicinestock, medicine
where  pscomedicine.pstockmedicineid = pstockmedicine.pstockmedicineid and
       pstockmedicine.medicinestockid = medicinestock.medicinestockid and
       medicinestock.medicineid = medicine.medicineid;

create or replace view v_pstockinorder2 as
select pstockinorder.*,
medicine.medicineid        m_medicineid,
medicine.code              m_code,
medicine.topqty            m_topqty,
medicine.downqty           m_downqty,
medicine.memo              m_memo,
medicine.alais             m_alais,
medicine.model             m_model,
medicine.pinyin            m_pinyin,
medicine.caption           m_caption,
medicine.doseid            m_doseid,
medicine.unitsid           m_unitsid,
medicine.usemethodid       m_usemethodid,
medicine.medicinecatalogid m_medicinecatalogid,
medicine.minunitsid        m_minunitsid,
medicine.packamount        m_packamount
from pstockinorder, medicinestock, medicine
where pstockinorder.medicinestockid = medicinestock.medicinestockid and
      medicinestock.medicineid = medicine.medicineid;