xml 形式的存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

create procedure [dbo].[MachiningPayedGetListByCondition]
@CustomerName varchar(50),
@CustomerPhone varchar(50),
@PactNumber varchar(50),
@Ispay bit
as
begin
 set nocount on;
 SELECT
 dp.ID as 'MachiningPayed/@ID',
        Orders.ID AS 'MachiningPayed/Order/@ID',

  -- Task --
  Orders.Task AS 'MachiningPayed/Order/Task/@ID',
  Tasks.OrderState as 'MachiningPayed/Order/Task/OrderState',
  Tasks.UpdateDate as 'MachiningPayed/Order/Task/UpdateDate',
  Tasks.CustomerName as 'MachiningPayed/Order/Task/CustomerName',
  Tasks.CustomerPhone as 'MachiningPayed/Order/Task/CustomerPhone',
  Tasks.CellPhone as 'MachiningPayed/Order/Task/CellPhone',
  Tasks.CustomerAddress as 'MachiningPayed/Order/Task/CustomerAddress',
  Tasks.ForemanName as 'MachiningPayed/Order/Task/ForemanName',
  Tasks.MonitorName as 'MachiningPayed/Order/Task/MonitorName',
  Tasks.DesignerName as 'MachiningPayed/Order/Task/DesignerName',
  Tasks.Store as 'MachiningPayed/Order/Task/Store',
  Tasks.PactNumber as 'MachiningPayed/Order/Task/PactNumber',
  Tasks.CreateDate as 'MachiningPayed/Order/Task/CreateDate',
  Tasks.PlanDisclosureDate as 'MachiningPayed/Order/Task/PlanDisclosureDate',
  Tasks.PlanHiddenCheckDate as 'MachiningPayed/Order/Task/PlanHiddenCheckDate',
  Tasks.PlanMidCheckDate as 'MachiningPayed/Order/Task/PlanMidCheckDate',
  Tasks.PlanEndCheckDate as 'MachiningPayed/Order/Task/PlanEndCheckDate',

  -- Task End--

  Orders.PactNumber AS 'MachiningPayed/Order/PactNumber',
  Orders.CustomerName AS 'MachiningPayed/Order/CustomerName',
  Orders.CustomerPhone AS 'MachiningPayed/Order/CustomerPhone',
  Orders.CellPhone AS 'MachiningPayed/Order/CellPhone',
  Orders.CustomerType AS 'MachiningPayed/Order/CustomerType',
  Orders.NativeProvince AS 'MachiningPayed/Order/NativeProvince',
  Orders.Address AS 'MachiningPayed/Order/Address',
  Orders.Store AS 'MachiningPayed/Order/Store',
  Orders.SalesDepartment AS 'MachiningPayed/Order/SalesDepartment',
  Orders.Salesman AS 'MachiningPayed/Order/Salesman',
  Orders.DepositMoney AS 'MachiningPayed/Order/DepositMoney',
  Orders.Activitys AS 'MachiningPayed/Order/Activitys',
  Orders.SetLocationType AS 'MachiningPayed/Order/SetLocationType',
  Orders.DistanceFeesType AS 'MachiningPayed/Order/DistanceFeesType',
  Orders.Foreman AS 'MachiningPayed/Order/Foreman',
  Orders.Monitor AS 'MachiningPayed/Order/Monitor',
  Orders.AreaSize AS 'MachiningPayed/Order/AreaSize',
  Orders.RoomType AS 'MachiningPayed/Order/RoomType',
  Orders.PlanDisclosureDate AS 'MachiningPayed/Order/PlanDisclosureDate',
  Orders.ActualDisclosureDate AS 'MachiningPayed/Order/ActualDisclosureDate',
  Orders.PlanHiddenCheckDate AS 'MachiningPayed/Order/PlanHiddenCheckDate',
  Orders.ActualHiddenCheckDate AS 'MachiningPayed/Order/ActualHiddenCheckDate',
  Orders.ActualMidCheckDate AS 'MachiningPayed/Order/ActualMidCheckDate',
  Orders.PlanEndCheckDate AS 'MachiningPayed/Order/PlanEndCheckDate',
  Orders.ActualEndCheckDate AS 'MachiningPayed/Order/ActualEndCheckDate',
  Orders.DistanceFeesType AS 'MachiningPayed/Order/DistanceFeesType',

  Orders.SetMealTemplate AS 'MachiningPayed/Order/SetMealTemplate/@ID',
  SetMealTemplates.Name AS 'MachiningPayed/Order/SetMealTemplate/@Name',
  SetMealTemplates.Description AS 'MachiningPayed/Order/SetMealTemplate/Description',
  
  Orders.SetMealLevel as 'MachiningPayed/Order/SetMealLevel',

  Orders.SetCount AS 'MachiningPayed/Order/SetCount',

  Orders.IncreaseCount AS 'MachiningPayed/Order/IncreaseCount',
  Orders.Managementfees AS 'MachiningPayed/Order/Managementfees',
  Orders.Taxes AS 'MachiningPayed/Order/Taxes',
  Orders.Distancefees AS 'MachiningPayed/Order/Distancefees',
  Orders.CustomerResourcesDetail as 'MachiningPayed/Order/CustomerResourcesDetail',
  Orders.CustomerResources as 'MachiningPayed/Order/CustomerResources',
  
  Orders.OrderState AS 'MachiningPayed/Order/OrderState',
--  'Created' AS 'Order/OrderState',
  osr.Title AS 'MachiningPayed/Order/OrderStateTitle',

  Orders.CreateDate AS 'MachiningPayed/Order/CreateDate',
  Orders.CompleteDate AS 'MachiningPayed/Order/CompleteDate',
  Orders.SendOrderDate AS 'MachiningPayed/Order/SendOrderDate',
  Orders.Sender AS 'MachiningPayed/Order/Sender',
  Orders.IsDisclosure AS 'MachiningPayed/Order/IsDisclosure',
  Orders.IsHiddenCheck AS 'MachiningPayed/Order/IsHiddenCheck',
  Orders.IsMidCheck AS 'MachiningPayed/Order/IsMidCheck',
  Orders.IsEndCheck AS 'MachiningPayed/Order/IsEndCheck',
  Orders.DisclosureText AS 'MachiningPayed/Order/DisclosureText',
  Orders.HiddenCheckText AS 'MachiningPayed/Order/HiddenText',
  Orders.MidCheckText AS 'MachiningPayed/Order/MidCheckText',
  Orders.EndCheckText AS 'MachiningPayed/Order/EndCheckText',
  Orders.IsForemanMidSettle AS 'MachiningPayed/Order/IsForemanMidSettle',
  Orders.IsForemanEndSettle AS 'MachiningPayed/Order/IsForemanEndSettle',
  Orders.OrigCount AS 'MachiningPayed/Order/OrigCount',
  Orders.TotalCount AS 'MachiningPayed/Order/TotalCount',
  Orders.IsRecovery AS 'MachiningPayed/Order/IsRecovery',
  Orders.Invoice AS 'MachiningPayed/Order/Invoice',
  Orders.Remark AS 'MachiningPayed/Order/Remark',
  Orders.Area AS 'MachiningPayed/Order/Area',
  Orders.IncreaseA AS 'MachiningPayed/Order/IncreaseA',
  Orders.IncreaseB AS 'MachiningPayed/Order/IncreaseB',
  Orders.XCoordinate AS 'MachiningPayed/Order/XCoordinate',
  Orders.YCoordinate AS 'MachiningPayed/Order/YCoordinate',
  Users.ID as 'MachiningPayed/Order/Designer/@ID',
  Users.LogName as 'MachiningPayed/Order/Designer/@LogName',
  Users.LogPassword as 'MachiningPayed/Order/Designer/@LogPassword',
  Users.UserName as 'MachiningPayed/Order/Designer/@UserName',
  Users.Remark as 'MachiningPayed/Order/Designer/Remark',

  -- 主材集合--
   (SELECT  
    opi.ID AS '@ID', 
    
    opi.Remark AS '@Remark',
    opi.Unit AS '@Unit',
    opi.IsPayedUpdate AS '@IsPayedUpdate',
    opi.IncreaseCount AS '@IncreaseCount',
    opi.SetQuantity AS 'SetQuantity', 
    opi.IncreaseQuantity AS 'IncreaseQuantity',
    opi.Product AS 'Product/@ID',
     
    p.Name AS 'Product/Name',
    p.Brand AS 'Product/Brand',
    p.SupplierType AS 'Product/SupplierType',
    p.Model AS 'Product/Model'
    FROM OrderProductItems opi
    inner join Products as p on p.id =opi.Product
    WHERE opi.Parent = Orders.ID 
    FOR XML PATH('OrderProductItem'), TYPE 
    ) AS 'MachiningPayed/Order/OrderProductItems',
  -- 主材集合 End --

  -- 工艺集合--
   (SELECT  
    oci.ID AS '@ID', 
    
    oci.Unit AS '@Unit',
    oci.IncreaseCount AS '@IncreaseCount',

     oci.Crafts AS 'Crafts/@ID',
     Crafts.Title AS 'Crafts/@Title',
     Crafts.Material AS 'Crafts/@Material',
     Crafts.Unit AS 'Crafts/@Unit',
     Crafts.Description AS 'Crafts/@Description',
 
    oci.SetQuantity AS 'SetQuantity', 
    oci.IncreaseQuantity AS 'IncreaseQuantity',
    oci.Remark AS 'Remark'

    FROM OrderCraftsItems oci
    inner join Crafts on Crafts.ID = oci.Crafts
    WHERE oci.Parent = Orders.ID 
    FOR XML PATH('OrderCraftsItems'), TYPE 
    ) AS 'MachiningPayed/Order/OrderCraftsItemss',
  -- 工艺集合 End --
       dp.ActuallyPayed as 'MachiningPayed/ActuallyPayed',
  dp.Receipt as 'MachiningPayed/Receipt',
  dp.GetPayedDate as 'MachiningPayed/GetPayedDate',
  
  dp.IsPayed as 'MachiningPayed/IsPayed',
  dp.Remark as 'MachiningPayed/Remark',
  dp.PayedType as 'MachiningPayed/PayedType'
  from
  MachiningPayed as dp
  left join Orders on dp.Orders=Orders.id
     inner join Tasks on Tasks.id = Orders.Task
  inner join Users on orders.designer=Users.id
  inner join OrderStateRelation osr on orders.OrderState = osr.OrderState
  inner join SetMealTemplates on SetMealTemplates.ID=Orders.SetMealTemplate
 where
   dp.IsPayed=@Ispay and
  Orders.CustomerName like '%'+coalesce(@CustomerName,Orders.CustomerName)+'%' and
  Orders.CustomerPhone like '%'+coalesce(@CustomerPhone,Orders.CustomerPhone)+'%' and
  Orders.PactNumber like '%'+coalesce(@PactNumber,Orders.PactNumber)+'%'
 for xml path(''),root('ArrayOfMachiningPayed')

end