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

namespace Core.StlMes.Client.Sale.BLL.SaleBusinessMgt.SqlCollection
{
    public class Plan
    {
        public static String QUERY_ORDER_DLIV = @"select a.order_no,
                                                                                           min(b.customer_no) customer_no,
                                                                                           min(b.forecorpcode) forecorpcode,
                                                                                           TRANSIT_TYP,
                                                                                           region_no,
                                                                                           destination,
                                                                                           station_no,
                                                                                           spcl_ln_no,
                                                                                           isownedcar,
                                                                                           isshipping,
                                                                                           port_no,
                                                                                           port_desc,
                                                                                           carrier_unit,
                                                                                           inceptcorpcode,
                                                                                           sum(a.order_qty) order_qty,
                                                                                           sum(a.weight) weight
                                                                                      from slm_order_line a
                                                                                      join slm_order_head b on a.order_no = b.order_no
                                                                                      join slm_order_delivery_transit c on a.order_no = c.order_no
                                                                                                                       and a.order_seq = c.order_seq
                                                                                    where 1 = 1 {0}
                                                                                     group by a.order_no,
                                                                                              TRANSIT_TYP,
                                                                                              region_no,
                                                                                              destination,
                                                                                              station_no,
                                                                                              spcl_ln_no,
                                                                                              isownedcar,
                                                                                              isshipping,
                                                                                              port_no,
                                                                                              port_desc,
                                                                                              carrier_unit,
                                                                                              inceptcorpcode
                                                                                    ";


        public static string QUERY_SLM_DLIV_DIR = @"select 
                                                                                    dliv_dirno,
                                                                                    forecorpcode,
                                                                                    customer_no,
                                                                                    plandate,
                                                                                    transport_num,
                                                                                    transit_typ,
                                                                                    get_on_pcd,
                                                                                    quantity,
                                                                                    weight,
                                                                                    trans_car_no,
                                                                                    ship_no,
                                                                                    destination,
                                                                                    station_no,
                                                                                    station_nm,
                                                                                    spcl_ln_no,
                                                                                    spcl_ln_nm,
                                                                                    isshipping,
                                                                                    port_no,
                                                                                    port_desc,
                                                                                    inceptcorpcode,
                                                                                    reinceptcorp,
                                                                                    receivor,
                                                                                    carrier_unit,
                                                                                    phonecode,
                                                                                    driveridcard,
                                                                                    dliv_prog_cd,
                                                                                    isout,
                                                                                    printstatus,
                                                                                    tranprtstatus,
                                                                                    to_char(dliv_rollbacktime,'{1}')  dliv_rollbacktime,
                                                                                    dliv_rollbackname,
                                                                                    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_dliv_dir  where  1=  1 {0} ";

        public static string QUERY_SLM_DELV_DIR_DETAIL = @"select 
                                                                                dliv_dirno,
                                                                                seq,
                                                                                order_no,
                                                                                order_seq,
                                                                                height,
                                                                                width,
                                                                                length,
                                                                                prod_line,
                                                                                producname,
                                                                                spec_abbsym,
                                                                                steel_code,
                                                                                used_cd,
                                                                                prdnm_cd,
                                                                                quantity,
                                                                                weight,
                                                                                price,
                                                                                money,
                                                                                transmoney,
                                                                                deduct_flag,
                                                                                to_char(deduct_date,'{1}')  deduct_date,
                                                                                to_char(refund_back,'{1}')  refund_back,
                                                                                ship_dliv_prog_cd,
                                                                                create_name,
                                                                                to_char(create_time,'{1}')  create_time,
                                                                                update_name,
                                                                                to_char(update_time,'{1}')  update_time,
                                                                                from slm_dliv_dir_detail  where 1 = 1 ";

        public static string QUERY_SEND_INFO = @"select b.order_ln_status,
                                                        b.order_no,
                                                        b.order_seq,
                                                        c.prod_line,
                                                        c.steel_code,
                                                        b.height,
                                                        b.width,
                                                        b.length,
                                                        b.weight,
                                                        d.station_no,
                                                        d.port_no,
                                                        d.inceptcorpcode,
                                                        a.customer_no,
                                                        b.order_qty,
                                                        b.fixsize,
                                                        b.delvry_bdate,
                                                        b.memo,
                                                        a.order_bdate,
                                                        a.order_edate
                                                  from slm_order_head a
                                                  join slm_order_line b
                                                    on a.order_no = b.order_no
                                                  join slm_order_prod c
                                                    on b.order_no = c.order_no
                                                   and b.order_seq = c.order_seq
                                                  join slm_order_delivery_transit d
                                                    on b.order_no = d.order_no
                                                   and b.order_seq = d.order_seq
                                                 where 1 = 1 {0}";


        public static string QUERY_SHIPNOAPPLYRINPUT = @"select transport_num,
                                                                to_char(transmonth,'yyyy-mm') transmonth,
                                                                transit_typ,
                                                                wagon_no,
                                                                quantity,
                                                                weight,
                                                                load_wgt,
                                                                slab_wgt,
                                                                destination,
                                                                station_no,
                                                                port_no,
                                                                port_desc,
                                                                carrier_unit,
                                                                contact_name,
                                                                contact_phone,
                                                                scheme_no,
                                                                remark,
                                                                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_dliv_shipapply_result
                                                          where 1 = 1 {0}";
       
    }
}