Create      Database    SamService
GO

Use     SamService
GO


Create      Table       [Stuff](
ID      BigInt      Not Null    Identity    Primary Key,
Title       nVarChar(100)   Not Null,
Model       nVarChar(6) Not Null,
Serial      nVarChar(11)    Not Null,
Constraint  IX_Stuff    Unique      (Serial))


Create      Table       Customer(
ID      BigInt      Not Null    Identity    Primary Key,
Firstname   nVarChar(50)    Not Null,
Lastname    nVarChar(70)    Not Null,
Code        nVarChar(10)    Not Null,
Tel     nVarChar(10),
[Address]   nVarChar(500),
Constraint  IX_Customer Unique      (Code))


Create      Table       Purchase(
ID      BigInt      Not Null    Identity    Primary Key,
Stuff_ID    BigInt      Not Null    References  [Stuff](ID),
Customer_ID BigInt      Not Null    References  Customer(ID),
Code        nVarChar(10)    Not Null,
[Date]      DateTime    Not Null    Default(GetDate())
Constraint  IX_Purchase Unique      (Code))


Create      Table       Delivery(
ID      BigInt      Not Null    Identity    Primary Key,
Customer_ID BigInt      Not Null    References  Customer(ID),
Purchase_ID BigInt      Not Null    References  Purchase(ID),
[Date]      DateTime    Not Null    Default(GetDate()))


Create      Table       Request(
ID      BigInt      Not Null    Identity    Primary Key,
Customer_ID BigInt      Not Null    References  Customer(ID),
Purchase_ID BigInt      Not Null    References  Purchase(ID),
[Status]    Int     Not Null    Default(1),
ConfrimStatus   Bit     Not Null    Default(0),
[Date]      DateTime    Not Null    Default(GetDate()),
Notes       nVarChar(500),
Constraint  CK_ReplacementRequest       Check       ([Status] > 0 And [Status] < 4))


-- Function

Create      Function    MainQuery()
Returns     Table
        Return
        Select  Customer.Firstname          As  Firstname,
            Customer.Lastname           As  Lastname,
            Customer.[Address]          As  [Address],
            Customer.Tel                As  Tel,
            Customer.Code               As  Code,
            [Stuff].Title               As  Name,
            [Stuff].Model               As  Model,
            [Stuff].Serial              As  Serial,
            Request.[Status]            As  [Status],
            Request.ConfrimStatus           As  ConfrimStatus,
            Request.Notes               As  Notes,
            Request.[Date]              As  RequestDate,
            Request.ID              As  RequestID,
            Purchase.[Date]             As  PurchaseDate,
            Purchase.Code               As  PurchaseCode
            From    Request
            Inner   Join    Customer
                On  Customer.ID     =   Request.Customer_ID
            Inner Join  Purchase
                On  Purchase.Customer_ID    =   Request.Customer_ID
            Inner Join  [Stuff]
                On  [Stuff].ID      =   Purchase.Stuff_ID

            Where   Request.[Status]            <>  1