MES_Client/TGMES/Core.StlMes.Client.Sale.BLL.SaleBusinessMgt/SqlCollection/Order.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Core.StlMes.Client.Sale.BLL.SaleBusinessMgt.SqlCollection
{
    public class Order
    {
        /// <summary>
        /// 查询合同头信息
        /// </summary>
        public static string  QUERY_ORDER_HEAD =
            @"select  order_no,
                custm_order_no,
                order_typ,
                order_typ_desc,
                protcl_no,
                customer_no,
                customer_nm,
                forecorpcode,
                forecorpname,
                market_sys,
                market_sys_desc,
                abrvition,
                supply_unit,
                price_fl,
                currency,
                mny_ask_typ,
                advancemoney,
                advancescale,
                stlmt_unit_no,
                stlmt_unit_nm,
                fst_drawee_unit,
                legal_psn,
                agent,
                address,
                zip_code,
                bank,
                account,
                tax_id,
                phone,
                fax,
                order_wgt,
                order_mny_tax,
                order_mny_ntax,
                isimportorder,
                isdev,
                mny_ask_desc,
                sign_person,
                to_char(sign_date,'{1}') sign_date,
                sign_address,
                order_year,
                to_char(order_bdate,'{1}') order_bdate ,
                to_char(order_edate,'{1}') order_edate,
                order_scan_no,
                customer_model_fl,
                order_model_no,
                order_model_desc,
                sale_org,
                sale_org_desc,
                sale_area_no,
                sale_area_desc,
                memo,
                order_sts,
                order_sts_desc,
                approval_man,
                to_char(approval_time,'{1}') approval_time,
                effect_man,
                to_char(effect_time,'{1}') effect_time,
                close_man,
                to_char(close_time,'{1}') close_time ,
                validflag,
                create_name,
                create_dept,
                to_char(create_time,'{1}') create_time,
                update_name,
                to_char(update_time,'{1}') update_time,
                delete_name,
                to_char(delete_time,'{1}')  delete_time  from slm_order_head where 1 = 1 {0} ";






    public static string QUERY_ORDER_LINE = @"select   order_no,
                                                       order_seq,
                                                       height,
                                                       width,
                                                       length,
                                                       fixsize,
                                                       len_min,
                                                       len_max,
                                                       width_min,
                                                       width_max,
                                                       sendstatus,
                                                       order_unit,
                                                       order_qty,
                                                       weight,
                                                       delvry_range_tpe,
                                                       delvry_range_min,
                                                       delvry_range_max,
                                                       iswelded_heat,
                                                       nuclearsteel,
                                                       khic,
                                                       isposter,
                                                       final_user,
                                                       final_user_desc,
                                                       to_char(delvry_date, 'yyyy-mm-dd hh24:mi:ss') delvry_date,
                                                       to_char(delvry_bdate, 'yyyy-mm-dd hh24:mi:ss') delvry_bdate,
                                                       to_char(delvry_edate, 'yyyy-mm-dd hh24:mi:ss') delvry_edate,
                                                       delvry_desc,
                                                       trans_desc,
                                                       add_ask_no,
                                                       order_add_desc,
                                                       order_special_fl,
                                                       order_special_head,
                                                       order_special_path,
                                                       order_special_desc,
                                                       zbs,
                                                       order_price,
                                                       trans_price,
                                                       test_id,
                                                       test_price,
                                                       out_price,
                                                       balnce_price,
                                                       order_ln_status,
                                                       review_status,
                                                       review_msg,
                                                       to_char(review_date, 'yyyy-mm-dd hh24:mi:ss') review_date,
                                                       to_char(review_entrydate, 'yyyy-mm-dd hh24:mi:ss') review_entrydate,
                                                       memo,
                                                       validflag,
                                                       create_name,
                                                       to_char(create_time, 'yyyy-mm-dd hh24:mi:ss') create_time,
                                                       update_name,
                                                       to_char(update_time, 'yyyy-mm-dd hh24:mi:ss') update_time,
                                                       delete_name,
                                                       to_char(delete_time, 'yyyy-mm-dd hh24:mi:ss') delete_time
                                                  from slm_order_line
                                                 where 1 = 1 {0}";


        public static string QUERY_ORDER_PROD = @"select 
                                                     Order_no,
                                                     Order_seq,
                                                     Prod_line,
                                                     Spec_abbsym,
                                                     Steel_code,
                                                     Used_cd,
                                                     Prdnm_cd,
                                                     Isalpha,
                                                     Cust_alpha_no,
                                                     Quality_desigh_result,
                                                     Quality_desigh_errmsg,
                                                     Quality_desigh_name,
                                                     Quality_desigh_date,
                                                     create_name,   
                                                     to_char(create_time,'{1}') create_time,
                                                     update_name,
                                                     to_char(update_time,'{1}') update_time,
                                                     delete_name,
                                                     to_char(delete_time,'{1}')  delete_time 
                                                from slm_order_prod where 1 = 1 {0}";

        public static string SLM_ORDER_DELIVERY_TRANSIT = @"select
                                                                 order_no ,
                                                                 order_seq ,
                                                                 index_seq ,
                                                                 agency_fl ,
                                                                 lv_facty_typ ,
                                                                 transit_typ ,
                                                                 region_no ,
                                                                 destination ,
                                                                 station_no ,
                                                                 station_nm ,
                                                                 spcl_ln_no ,
                                                                 spcl_ln_nm ,
                                                                 isownedcar ,
                                                                 isshipping ,
                                                                 port_no ,
                                                                 port_desc ,
                                                                 carrier_unit ,
                                                                 inceptcorpcode ,
                                                                 reinceptcorp ,
                                                                 receivor ,
                                                                 transit_ln_mny,
                                                                 validflag ,
                                                                 create_name,   
                                                                 to_char(create_time,'{1}') create_time,
                                                                 update_name,
                                                                 to_char(update_time,'{1}') update_time,
                                                                 delete_name,
                                                                 to_char(delete_time,'{1}')  delete_time 
                                                            from slm_order_delivery_transit where 1 = 1 {0}";

        public static string QUERY_ORDER_DETAIL = @"select a.order_no,
       a.order_seq,
       a.height,
       a.width,
       a.length,
       a.fixsize,
       a.len_min,
       a.len_max,
       a.width_min,
       a.width_max,
       a.sendstatus,
       a.order_unit,
       a.order_qty,
       a.weight,
       a.delvry_range_tpe,
       a.delvry_range_min,
       a.delvry_range_max,
       a.iswelded_heat,
       a.nuclearsteel,
       a.khic,
       a.isposter,
       a.final_user,
       a.final_user_desc,
       to_char(a.delvry_date, '{1}') delvry_date,
       to_char(a.delvry_bdate, '{1}') delvry_bdate,
       to_char(a.delvry_edate, '{1}') delvry_edate,
       a.delvry_desc,
       a.trans_desc,
       a.add_ask_no,
       a.order_add_desc,
       a.order_special_fl,
       a.order_special_head,
       a.order_special_path,
       a.order_special_desc,
       a.zbs,
       a.order_price,
       a.trans_price,
       a.test_id,
       a.test_price,
       a.out_price,
       a.balnce_price,
       a.order_ln_status,
       a.review_status,
       a.review_msg,
       to_char(a.review_date, '{1}') review_date,
       to_char(a.review_entrydate, '{1}') review_entrydate,
       a.memo,
       a.validflag,
       a.create_name,
       to_char(a.create_time, '{1}') create_time,
       a.update_name,
       to_char(a.update_time, '{1}') update_time,
       a.delete_name,
       to_char(a.delete_time, '{1}') delete_time,
       prod_line,
       spec_abbsym,
       steel_code,
       used_cd,
       prdnm_cd,
       isalpha,
       cust_alpha_no,
       quality_desigh_result,
       quality_desigh_errmsg,
       quality_desigh_name,
       to_char(quality_desigh_date, '{1}') quality_desigh_date,
       agency_fl,
       lv_facty_typ,
       transit_typ,
       region_no,
       destination,
       station_no,
       station_nm,
       spcl_ln_no,
       spcl_ln_nm,
       isownedcar,
       isshipping,
       port_no,
       port_desc,
       carrier_unit,
       inceptcorpcode,
       reinceptcorp,
       receivor,
       transit_ln_mny
  FROM SLM_ORDER_LINE a

  JOIN SLM_ORDER_PROD PD ON a.ORDER_NO = PD.ORDER_NO
                        AND a.ORDER_SEQ = PD.ORDER_SEQ
  join SLM_ORDER_DELIVERY_TRANSIT t ON a.ORDER_NO = t.ORDER_NO
                                   AND a.ORDER_SEQ = t.ORDER_SEQ
 
   where 1 = 1 {0}";

    }
}