yearinit.sql

这是用PB6开发的一个POS管理系统

 RetailQty TQty null, /*销售数量*/
 CheckQty  TQty null, /*结算数量*/
 CheckAmt  TAMT null, /*结算金额*/
 NoCheckQty TQty null,/*未结算数量*/
 NoCheckAmt TAmt null,/*未结算金额*/
 ID integer Identity not null primary key  )
go

create index idx_import_to_check_goodsno on import_to_check(goodsno)
go
create index idx_import_to_check_vendorno on import_to_check(vendorno)
go
 
/*供货商应付帐款表*/
if (select count(*) from  sysobjects  where name= 'payable') > 0
   drop table payable
go

create table Payable
(
 BusinessDate datetime not null,/*业务日期*/
 VendorNo TVendor not null,/*供货商代码*/
 BusinessType varchar(12) null,/*业务类型*/
 Remark varchar(30) null,/*摘要*/
 InvoiceNo TInvoice null,/*原始单据号码*/
 AddAmt TAmt null,/*增加金额*/
 SubAmt TAmt null,/*减少金额*/
 BalanceAmt TAmt null,/*结存金额*/
 ID Integer Identity not null primary key
)
go
create index idx_payable_vendorno on Payable(vendorno)
go

/*进货结算流水*/
if (select count(*) from  sysobjects  where name= 'ImportCheck') > 0
   drop table ImportCheck
go

create table ImportCheck
(
 CheckNO  TRECEIPT not null primary key, /*结算流水*/
 InputDate datetime not null,/*录入日期*/
 VendorNo TVendor not null,/*供货商代码*/
 CheckType char(1) null,/*结算类型*/
 CheckMan TStaff  null,/*结算人*/
 CheckDate datetime null,/*结算日期*/
 InvoiceNo TINVOICE null,/*发票号码*/
 CheckAmt  TAmt null,/*结算金额*/
 PrepayAmt TAmt null,/*预付金额*/
 DisAmt    TAmt null,/*让利金额*/
 Operator  TStaff null,/*操作员*/
 remark    TMEMO null /*备注*/
)
go

create unique index idx_importcheck_checkno on ImportCheck(CheckNo)
go
create index idx_importcheck_vendorno on ImportCheck(VendorNo)
go

/*进货结算单据明细表*/
if (select count(*) from  sysobjects  where name= 'ImportCheckByReceipt') > 0
   drop table ImportCheckByReceipt
go
create table ImportCheckByReceipt
(
 CheckNo TRECEIPT not null, /*结算流水*/
 ImportNo  TRECEIPT null,/*进货流水*/
 ImportDate DateTime null ,/*进货日期*/
 VendorNo TVendor null,/*供货商代码*/
 PrepayAmt TAmt null,/*预付金额*/
 NTaxAmt TAmt null,/*无税金额*/
 TaxAmt TAmt null, /*含税金额*/
 ID int identity 
)
go
create index idx_importcheckbyreceipt_checkno on ImportCheckByReceipt(CheckNo)
go


/*进货结算单品明细表*/
if (select count(*) from  sysobjects  where name= 'ImportCheckByGoods') > 0
   drop table ImportCheckByGoods
go

create table ImportCheckByGoods
(
 CheckNO TReceipt not null,/*结算流水*/
 ImportDate Datetime null ,/*进货日期*/
 ImportNo TReceipt not null,/*对应进货_销售关联库中的进货流水*/
 GoodsNo TGoods not null,/*商品代码*/
 NTaxPrice numeric(12,4) null,/*无税进价*/
 TaxPrice numeric(12,4) null,/*含税进价*/
 CheckQty TQty null,/*结算数量*/
 CheckAmt TAmt null/*结算金额*/
)
go

create index idx_importcheckbygoods_checkno on ImportCheckByGoods(CheckNo)
go

/*进货结算金额明细表*/
if (select count(*) from  sysobjects  where name= 'ImportCheckByAmt') > 0
   drop table ImportCheckByAmt
go

create table ImportCheckByAmt
(
 CheckNo TReceipt not null,/*结算流水*/
 PayableAmt TAmt null,/*应付金额*/
 PayingAmt TAmt null,/*结算金额*/
 BalanceAmt Tamt null/*结存应付金额*/
)
go

create index idx_importcheckbyamt_checkno on ImportCheckByGoods(CheckNo)
go


/* ============================================================ */
/*   Table: RetailStat                                          */
/* ============================================================ */
create table RetailStat
(
    deptno             TDept                 not null,
    GroupNo            TGroup                not null,
    ClassNO            TClass                not null,
    TaxRatio           numeric(5,3)          not null,
    RetailMode         char(1)               not null,
    RetailDate         Tdate                 not null,
    Amt                TAmt                  null    ,
    CostAmt            TAmt                  null    ,
    DisAmt             TAmt                  null    ,
    Qty                TQty                  null    
)
go

/* ============================================================ */
/*   Index: index_of_date                                       */
/* ============================================================ */
create index index_of_date on RetailStat (RetailDate)
go

/* ============================================================ */
/*   Index: index_of_group                                      */
/* ============================================================ */
create index index_of_group on RetailStat (GroupNo)
go

/* ============================================================ */
/*   Index: index_of_class                                      */
/* ============================================================ */
create index index_of_class on RetailStat (ClassNO)
go

/* ============================================================ */
/*   Table: VendorRetailStat                                    */
/* ============================================================ */
create table VendorRetailStat
(
    VendorNO           TVendor               not null,
    RetailDate         Tdate                 not null,
    TaxRatio           numeric(5,3)          not null,
    RetailMode         char(1)               not null,
    Amt                TAmt                  null    ,
    CostAmt            TAmt                  null    ,
    DisAmt             TAmt                  null    ,
    Qty                TQty                  null    
)
go

/* ============================================================ */
/*   Index: index_of_date                                       */
/* ============================================================ */
create index index_of_date on VendorRetailStat (RetailDate)
go

/* ============================================================ */
/*   Index: index_of_vendor                                     */
/* ============================================================ */
create index index_of_vendor on VendorRetailStat (VendorNO)
go

/* ============================================================ */
/*   Table: CategoryRetailStat                                  */
/* ============================================================ */
create table CategoryRetailStat
(
    CategoryNo         varchar(8)            not null,
    RetailDate         Tdate                 not null,
    RetailMode         char(1)               null    ,
    TaxRatio           numeric(5,3)          null    ,
    Amt                TAmt                  null    ,
    CostAmt            TAmt                  null,
    DisAmt             TAmt                  null    ,
    Qty                TQty                  null    
)
go

/* ============================================================ */
/*   Index: index_of_retaildate                                 */
/* ============================================================ */
create index index_of_retaildate on CategoryRetailStat (RetailDate)
go

/* ============================================================ */
/*   Index: index_of_category                                   */
/* ============================================================ */
create index index_of_category on CategoryRetailStat (CategoryNo)
go


/* ============================================================ */
/*   Table: NewGoodsRetail                                      */
/* ============================================================ */
create table NewGoodsRetail
(
    GoodsNo            TGoods                not null,
    RetailDate         Tdate                 not null,
    Amt                TAmt                  null    ,
    CostAmt            TAmt                  null    ,
    DisAmt             TAmt                  null    ,
    Qty                TQty                  null    ,
    constraint PK_NewGoodsRetail primary key (GoodsNo, RetailDate)
)
go

/* ============================================================ */
/*   Index: index_of_date3                                      */
/* ============================================================ */
create index index_of_date3 on NewGoodsRetail (RetailDate)
go

/* ============================================================ */
/*   Index: index_of_vendor3                                    */
/* ============================================================ */
create index index_of_vendor3 on NewGoodsRetail (GoodsNo)
go

/* ============================================================ */
/*   Table: ImportantGoodsRetail                                */
/* ============================================================ */
create table ImportantGoodsRetail
(
    GoodsNo            TGoods                not null,
    RetailDate         Tdate                 not null,
    Amt                TAmt                  null    ,
    CostAmt            TAmt                  null    ,
    DisAmt             TAmt                  null    ,
    Qty                TQty                  null    ,
    constraint PK_ImportantGoodsRetail primary key (GoodsNo, RetailDate)
)
go

/* ============================================================ */
/*   Index: index_of_date32                                     */
/* ============================================================ */
create index index_of_date32 on ImportantGoodsRetail (RetailDate)
go

/* ============================================================ */
/*   Index: index_of_vendor32                                   */
/* ============================================================ */
create index index_of_vendor32 on ImportantGoodsRetail (GoodsNo)
go

/* ============================================================ */
/*   Table: AllocStat                                           */
/* ============================================================ */
create table AllocStat
(
    DeptNo             Tdept                 not null,
    GroupNo            TGroup                not null,
    ClassNo            TClass                not null,
    AlloctDate         Tdate                 not null,
    Amt                TAmt                  null    ,
    NTaxAmt            TAmt                  null    ,
    Qty                TQty                  null    ,
    constraint PK_allocstat primary key (DeptNo, GroupNo, ClassNo, AlloctDate)
)
go


if exists (select 1
            from  sysobjects
           where  id = object_id('ExpensesDetail')
            and   type = 'U')
   drop table ExpensesDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ImportAdjustDetail')
            and   type = 'U')
   drop table ImportAdjustDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ImportAdjust')
            and   type = 'U')
   drop table ImportAdjust
go

/* ============================================================ */
/*   Table: ImportAdjust                                        */
/* ============================================================ */
create table ImportAdjust
(
    AdjustNo       TRECEIPT              not null,
    InputDate      TDATE                 null    
        default getdate(),
    AuditDate      TDATE                 null    
        default getdate(),
    VendorNo       TVendor               not null,
    Operator       TSTAFF                null    ,
    ReceiptNo      TINVOICE              null    ,
    Employee       TSTAFF                null    ,
    AuditFlag      char(1)               not null
        default '0',
    AuditOperator  TSTAFF                null    ,
    Remark         TMEMO                 null    ,
    constraint PK_IMPORTADJUST primary key (AdjustNo)
)
go

/* ============================================================ */
/*   Index: index_of_adjust_inputdate2                          */
/* =============================