MES_Client/TGMES/Core.StlMes.Client.Sale.BLL.SaleBusinessMgt/Trans/YdmZcLoadVehicleMatMgt.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Windows.Forms;
using CoreFS.CA06;
using Infragistics.Win.UltraWinGrid;

namespace Core.StlMes.Client.Sale.BLL.SaleBusinessMgt.Trans
{
    public class YdmZcLoadVehicleMatMgt : SaleBusinessMgt.BllBase.BaseBll
    {
        public YdmZcLoadVehicleMatMgt(OpeBase ob)
            : base(ob)
        {
           
        }

        private DataSet GetYdmZcBillM(string sqlCondition)
        {
            string sqlstr = string.Format(" select * from ydm_zc_bill_m  where 1 = 1  and validflag = '1' {0}", sqlCondition);
            return base.ExecuteQuery(sqlstr);
        }

        private DataSet GetYdmZcLoadVehicleMat(string sqlCondition)
        {
            string sqlstr = string.Format(" select * from ydm_zc_loadvehicle_mat  where 1 = 1  and validflag = '2' {0}", sqlCondition);
            return base.ExecuteQuery(sqlstr);
        }

        private DataSet GetYdmZcLoadvehicle(string sqlCondition)
        {
            string sqlstr = string.Format(" select * from ydm_zc_loadvehicle  where 1 = 1  and validflag = '1' {0}", sqlCondition);
            return base.ExecuteQuery(sqlstr);
        }

        private DataSet GetSlmOrderLineDeliveryTransit(string sqlCondition)
        {
            string sqlstr = string.Format(" select * from slm_order_delivery_transit  where 1 = 1  and VALIDFLAG = '1' {0}", sqlCondition);
            return base.ExecuteQuery(sqlstr);
        }


        public DataSet GetDistinctOrderLineDeliveryTransitByBillNo(string billno)
        {
            string sqlStr = @"select   a.agency_fl,
                                       a.transit_typ TRANS_TYPE,
 b.order_no,b.order_seq,B.DELIVERY_NO,
 a.ord_pk,a.ord_ln_pk,a.ord_ln_dly_pk,a.index_seq,
                                       a.destination,
                                       a.station_no,
                                       a.station_nm,
                                       a.spcl_ln_no,
                                       a.spcl_ln_nm,
                                       a.port_no,
                                       a.port_desc,
                                       a.carrier_unit,
                                       a.receivor,c.delvry_addr,c.receiv_addr
                                  from ydm_zc_bill_m b
                                  join slm_order_delivery_transit a on b.ord_pk = a.ord_pk
                                                                   and b.ord_ln_pk = a.ord_ln_pk
                                                                   and b.ord_ln_dly_pk = a.ord_ln_dly_pk
                                  join slm_order_line_delivery c
                                  on c.ord_pk = a.ord_pk
                                                                   and c.ord_ln_pk = a.ord_ln_pk
                                                                   and c.ord_ln_dly_pk = a.ord_ln_dly_pk
                                 where b.validflag = '1'
                                   and b.loadvehicle_type  = '1'
                                   and a.validflag = '1'   {0} ";
            string sqlCondition = string.Format("  and bill_no = '{0}' ",billno);
            sqlStr = string.Format(sqlStr,sqlCondition);
            return base.ExecuteQuery(sqlStr );
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ORD_PK"></param>
        /// <param name="ORD_LN_PK"></param>
        /// <param name="ORD_LN_DLY_PK"></param>
        /// <returns></returns>
        public DataSet GetSlmOrderLineDeliveryTransitByOrdPkAndOrdLnPkAndOrdLnDlvPk(string ORD_PK, string ORD_LN_PK, string ORD_LN_DLY_PK)
        {
            string sqlCondition = string.Format(" and ORD_PK ||ORD_LN_PK||ORD_LN_DLY_PK = '{0}' ", ORD_PK + ORD_LN_PK + ORD_LN_DLY_PK);
            return GetSlmOrderLineDeliveryTransit(sqlCondition);
 
        }

        /// <summary>
        /// 通过装车时间获取装车数据，包括已经确认和未确认
        /// </summary>
        /// <param name="dtBegin"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public DataSet GetYdmZcLoadvehicleByDelivy_Time(DateTime dtBegin, DateTime dtEnd)
        {
            string sqlConditino = Util.ConverObject.ConvertDateTimeValueToDbLanguage("DELIVY_TIME", dtBegin, dtEnd);
            return GetYdmZcLoadvehicle(sqlConditino);
        }

        /// <summary>
        /// 通过装车时间获取确认的装车数据
        /// </summary>
        /// <param name="dtBegin"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public DataSet GetAffrimYdmZcLoadvehicleByDelivy_Time(DateTime dtBegin, DateTime dtEnd)
        {
            
            string sqlConditino = Util.ConverObject.ConvertDateTimeValueToDbLanguage("DELIVY_TIME", dtBegin, dtEnd);
            sqlConditino += "   and  AFFIRM_FLAG = '2'  ";
            return GetYdmZcLoadvehicle(sqlConditino);
        }

        public DataSet GetYdmZcBillMByOrderNo(string orderNo)
        {
            string sqlCondition = string.Format("  and order_no = '{0}' ",orderNo);
            return this.GetYdmZcBillM(sqlCondition);
        }

        public DataSet GetYdmZcBillMByCreateTime(DateTime dtBegin, DateTime dtEnd)
        {
            string sqlConditino = Util.ConverObject.ConvertDateTimeValueToDbLanguage("CREATE_TIME", dtBegin, dtEnd);
            return this.GetYdmZcBillM(sqlConditino);
        }

        public DataSet GetYdmZcBillMByBillNo(string billno)
        {
            string sqlCondition = string.Format("  and bill_no = '{0}' ", billno);
            return this.GetYdmZcBillM(sqlCondition);
        }

        public DataSet GetUnAcceptVirtulYdmZcBillM()
        {
            string sqlCondition = "  and loadvehicle_type = '2' and ispreclose = '1'  ";
            return this.GetYdmZcBillM(sqlCondition);
        }

        public DataSet GetNotClosedYdmZcBillMByBillNo(string billno)
        {
            string sqlCondition = string.Format("  and bill_no = '{0}'  and (bill_status = '0' or bill_status = '1'  or bill_status = '2'  or bill_status = '3') ", billno);
            return this.GetYdmZcBillM(sqlCondition);
        }

        public Boolean IsBillNoAllClosed(string billNo)
        {
            DataSet ds = GetNotClosedYdmZcBillMByBillNo(billNo);
            try
            {
                return !(ds.Tables[0].Rows.Count > 0);
            }
            catch
            { 
                return false;
            }
        }

        private string BILL_STATUS_CLOSE = "4";

        /// <summary>
        /// 实单
        /// </summary>
        /// <returns></returns>
        public List<string> GetClosedBillM()
        {
            List<string> listBill = new List<string>();
            List<string> listBillNotClosed = new List<string>();
            string sqlCondition = string.Format("  and loadvehicle_type = '1'  and bill_status = '{0}' ", BILL_STATUS_CLOSE);
            DataSet ds =  this.GetYdmZcBillM(sqlCondition);
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return listBill;
            string billNo = "";
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                billNo = dr[BILL_NO].ToString();
                if(!listBill.Contains(billNo))
                listBill.Add(billNo);
            }

           
            
            //foreach (DataRow dr in ds.Tables[0].Rows)
            //{
            //    billNo = dr[BILL_NO].ToString();
            //    if (listBillNotClosed.Contains(billNo))
            //        continue;
            //    if (!listBill.Contains(billNo))
            //    {
            //        if (IsBillNoAllClosed(billNo))
            //        {
            //            listBill.Add(billNo);
            //        }
            //        else
            //        {
            //            if (!listBillNotClosed.Contains(billNo))
            //            {
            //                listBillNotClosed.Add(billNo);
            //            }
            //        }
            //    }
            //}

            return listBill;
 
        }

        public DataSet GetCanAcceptMatInfo()
        {
            List<string> closedBillNo = this.GetClosedBillM();
            string sqlConditon = Util.ConverObject.ConvertListStringValueToSqlCondition(BILL_NO,closedBillNo);
            DataSet ds = this.GetYdmZcLoadVehicleMat(sqlConditon);
            return ds;
        }


        public void Accept(ArrayList listbillNo,out string errMsg)
        {
            errMsg = "";
        }

        public void AcceptSignle( string billNo,out string errMsg )
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "acceptBalaneDetail", new object[] { billNo });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        public void AcceptTicketBySendTicketToInvoice(string billNo,ArrayList listOutStock,String billType, String billck,
			String outStockCk,string transck,string person, out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "acceptBalaneDetailBySignTicket", 
                new object[] { billNo,  listOutStock,billType,billck,outStockCk,transck,person });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        public void RollBackTicketBySendTicketToInvoice(string billNo, String billType, String billck,
          String outStockCk, string transck, string person, out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "rollBackTicketsAndOutStockMnyAndTransMnyByAksplid",
                new object[] { billNo, billType, billck, outStockCk, transck, person });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        public void AffrimAline(ArrayList listbillNo,  string person, out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam("core.stlmes.server.sale.service.Bill.YdmZcBillMgt", "makeTicketAline", new object[] { listbillNo, person });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        public void UnAffrimAline(ArrayList listbillNo, string person, out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam("core.stlmes.server.sale.service.Bill.YdmZcBillMgt", "makeTicketNoAline", new object[] { listbillNo, person });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }


        /// <summary>
        /// 获取票据对齐数据
        /// </summary>
        /// <param name="dtBegin"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public DataSet GetBillNoAfterCloseByBillCreateTime(DateTime dtBegin, DateTime dtEnd, DateTime date3, DateTime date4)
        {
            DataSet ds = new DataSet();
            string sqlConditino = Util.ConverObject.ConvertDateTimeValueToDbLanguage("a.CREATE_TIME", dtBegin, dtEnd);
            if (date3 != default(DateTime))
            {
                sqlConditino = Util.ConverObject.ConvertDateTimeValueToDbLanguage("a.PRINT_FLAG", date3, date4);
            }
            sqlConditino += " and (BILL_TICKET_STATUS = '0' or OUTSTOCK_TICKET_STATUS = '0' or TRANSMNY_TICKET_STATUS = '0')";
            string sql = string.Format(GetSqlStr(),sqlConditino);
            ds = base.ExecuteQuery(sql);
            return ds;
        }

        public DataSet GetBillNoAfterCloseByBillno(string billNo)
        {
            DataSet ds = new DataSet();
            string sqlConditino = string.Format("  and   a.bill_no  like  '%'||'{0}'||'%' ",billNo);
            sqlConditino += " and (BILL_TICKET_STATUS = '0' or OUTSTOCK_TICKET_STATUS = '0' or TRANSMNY_TICKET_STATUS = '0')";
            string sql = string.Format(GetSqlStr(), sqlConditino);
            ds = base.ExecuteQuery(sql);
            return ds;
        }

        public DataSet GetAlineBillNoAfterCloseByBillCreateTime(DateTime dtBegin, DateTime dtEnd, DateTime date3, DateTime date4)
        {
            DataSet ds = new DataSet();
            string sqlConditino = Util.ConverObject.ConvertDateTimeValueToDbLanguage("a.CREATE_TIME", dtBegin, dtEnd);
            if (date3 != default(DateTime))
            {
                sqlConditino = Util.ConverObject.ConvertDateTimeValueToDbLanguage("a.PRINT_FLAG", date3, date4);
            }
            sqlConditino += " and instr(BILL_TICKET_STATUS||OUTSTOCK_TICKET_STATUS||TRANSMNY_TICKET_STATUS,'0') = 0  ";
            sqlConditino += " and instr(BILL_TICKET_STATUS||OUTSTOCK_TICKET_STATUS||TRANSMNY_TICKET_STATUS,'1')  > 0";
            string sql = string.Format(GetSqlStr(), sqlConditino);
            ds = base.ExecuteQuery(sql);
            return ds;
        }

        public DataSet GetAlineBillNoAfterCloseByBillno(string billNo)
        {
            DataSet ds = new DataSet();
            string sqlConditino = string.Format("  and   a.bill_no  like  '%'||'{0}'||'%' ", billNo);
            sqlConditino += " and instr(BILL_TICKET_STATUS||OUTSTOCK_TICKET_STATUS||TRANSMNY_TICKET_STATUS,'0') = 0  ";
            sqlConditino += " and instr(BILL_TICKET_STATUS||OUTSTOCK_TICKET_STATUS||TRANSMNY_TICKET_STATUS,'1')  > 0";
            string sql = string.Format(GetSqlStr(), sqlConditino);
            ds = base.ExecuteQuery(sql);
            return ds;
        }

        public DataSet GetSendSuccessBillNoAfterCloseByBillCreateTime(DateTime dtBegin, DateTime dtEnd, DateTime date3, DateTime date4)
        {
            DataSet ds = new DataSet();
            string sqlConditino = Util.ConverObject.ConvertDateTimeValueToDbLanguage("a.CREATE_TIME", dtBegin, dtEnd);
            if (date3 != default(DateTime))
            {
                sqlConditino = Util.ConverObject.ConvertDateTimeValueToDbLanguage("a.PRINT_FLAG", date3, date4);
            }
            sqlConditino += " and (BILL_TICKET_STATUS = '2' and OUTSTOCK_TICKET_STATUS = '2'  and TRANSMNY_TICKET_STATUS  ='2')";
            string sql = string.Format(GetSqlStr(), sqlConditino);
            ds = base.ExecuteQuery(sql);
            return ds;
        }

        public DataSet GetSendSuccessBillNoAfterCloseByBillno(string billNo)
        {
            DataSet ds = new DataSet();
            string sqlConditino = string.Format("  and   a.bill_no  like  '%'||'{0}'||'%' ", billNo);
            sqlConditino += " and (BILL_TICKET_STATUS ='2' and OUTSTOCK_TICKET_STATUS = '2' and TRANSMNY_TICKET_STATUS = '2')";
            string sql = string.Format(GetSqlStr(), sqlConditino);
            ds = base.ExecuteQuery(sql);
            return ds;
        }


        public DataSet GetAlineBillMateByBillNo(string billno)
        {
            DataSet ds = new DataSet();
            string sql = this.GetBillMateByBillNo(billno);
            ds = base.ExecuteQuery(sql);
            return ds;
        }

        public DataSet GetAlineVirtualBillMateByBillNo(string billno)
        {
            DataSet ds = new DataSet();
            string sql = this.GetVirtualBillInfoByBillNo(billno);
            ds = base.ExecuteQuery(sql);
            return ds;
        }

        public DataSet GetAlineTransDeliveryByBillNo(string billno)
        {
            DataSet ds = new DataSet();
            string sql = string.Format("select * from ydm_order_delivery_transit a where a.validflag = '1' {0} ",
                string.Format("  and  bill_no = '{0}' ", billno));
            ds = base.ExecuteQuery(sql);
            return ds;
        }

        private string GetSqlStr()
        {
            string str = @" 
                                 SELECT MIN(A.CREATE_TIME) CREATE_TIME,min(to_char(INVOICEDATE,'yyyy-mm-dd')) INVOICEDATE,
      min(a.management_name) management_name,
        min(a.sale_org_desc) sale_org_desc,
       min(a.loadvehicle_type) loadvehicle_type,
       min(a.supply_unit) supply_unit,
       min(a.supply_unit_desc) supply_unit_desc,
       MIN(A.CUSTOMER_NO) CUSTOMER_NO,
       MIN(A.CUSTOMER_NM) CUSTOMER_NM,
       MIN(A.RECEIV_NO) RECEIV_NO,
       MIN(A.RECEIV_NM) RECEIV_NM,
       MIN(B.CARRY_COMPANY_CODE),
       MIN(B.CARRY_COMPANY_NAME),
         max(a.plan_num) QUANTITY,
        max(a.plan_wt) WEIGHT,
        max(a.trans_area) TRANS_AREA,
       min(case
             when a.loadvehicle_type = '2' and a.bill_status = '4' then
              '二次关闭'
             when a.loadvehicle_type = '2' and a.bill_status >= '2' then
              '理货'
             else
              '未理货'
           end) virsts,
       SUM(case
             when a.loadvehicle_type = '2' and a.bill_status < '4' then
           0
             else
              B.ACT_COUNT
           end) ACT_COUNT,
       SUM(case
             when a.loadvehicle_type = '2' and a.bill_status < '4' then
          0
             else
             
              CASE
             WHEN NVL(B.PONDER_GROSS_WT,0) <> 0 THEN
              B.PONDER_GROSS_WT
             WHEN (NVL(B.PONDER_GROSS_WT,0) = 0) AND ((SELECT min(ORDER_UNIT) FROM SLM_ORDER_LINE WHERE ORD_LN_PK = A.ORD_LN_PK) = '吨') AND (NVL(B.SEND_NUM,0)>0) THEN
               B.SEND_NUM
             ELSE
              B.ACT_WEIGHT
           END end) ACT_WEIGHT,
       A.BILL_NO,
       SUM(B.SEND_NUM) SEND_NUM,
       MIN(LOADVEHICLE_TYPE) LOADVEHICLE_TYPE,
       MIN(CASE
             WHEN A.BILL_TICKET_STATUS = '0' THEN
              'N'
             WHEN A.BILL_TICKET_STATUS = '1' THEN
              '已对齐'
             WHEN A.BILL_TICKET_STATUS = '2' THEN
              '发送结算'
           END) BILL_TICKET_STATUS,
       MIN(
           
           case
             when a.loadvehicle_type = '1' then
              case
             WHEN OUTSTOCK_TICKET_STATUS = '1' THEN
              '已对齐'
             WHEN OUTSTOCK_TICKET_STATUS = '2' THEN
              '发送结算'
             when a.extra_flag = '1' then
              'N'
             when a.agency_fl = '0' and a.delvry_addr = 'TPCO库房' then
              'OK'
           
             WHEN A.DELVRY_ADDR NOT IN ('TPCO库房车板', '指定地点', 'TPCO库房') THEN
              'OK'
             ELSE
              CASE
             WHEN OUTSTOCK_TICKET_STATUS = '0' THEN
              'N'
             WHEN OUTSTOCK_TICKET_STATUS = '1' THEN
              '已对齐'
             WHEN OUTSTOCK_TICKET_STATUS = '2' THEN
              '发送结算'
           END END WHEN A.LOADVEHICLE_TYPE = '2' THEN CASE
             WHEN A.BILL_STATUS < '4' THEN
              ''
             WHEN A.BILL_STATUS >= '4' THEN
              case
             WHEN OUTSTOCK_TICKET_STATUS = '1' THEN
              '已对齐'
             WHEN OUTSTOCK_TICKET_STATUS = '2' THEN
              '发送结算'
             when a.agency_fl = '0' and a.delvry_addr = 'TPCO库房' then
              'OK'
             when a.extra_flag = '1' then
              'N'
             WHEN A.DELVRY_ADDR NOT IN ('TPCO库房车板', '指定地点', 'TPCO库房') THEN
              'OK'
             ELSE
              CASE
             WHEN OUTSTOCK_TICKET_STATUS = '0' THEN
              'N'
           
           END END END END)
       
       OUTSTOCK_TICKET_STATUS,
       MIN(case
             when A.TRANSMNY_TICKET_STATUS = '2' then
              '发送结算'
           
             when (tr.Ord_Pk is null) then
              'OK'
           
             when A.TRANSMNY_TICKET_STATUS = '1' then
              '已对齐'
             when A.TRANSMNY_TICKET_STATUS = '2' then
              '发送结算'
             when (tr.Ord_Pk is not null) then
              'N'
           
           end) TRANSMNY_TICKET_STATUS,
       min(a.management_no) create_dept,
       min(bill_status) bill_status,
        min((SELECT BASENAME FROM COM_BASE_INFO WHERE BASECODE= a.plan_trans_type)) TRANS_TPE
  FROM (select MIN(A.CREATE_TIME) CREATE_TIME,
               min(a.loadvehicle_type) loadvehicle_type,
               MIN(A.CUSTOMER_NO) CUSTOMER_NO,
               MIN(A.CUSTOMER_NM) CUSTOMER_NM,
               min (a.ORD_LN_PK) ORD_LN_PK,
               MIN(A.RECEIV_NO) RECEIV_NO,
               MIN(A.RECEIV_NM) RECEIV_NM,
               min(a.PRINT_FLAG) INVOICEDATE,
               min(a.TRANSMNY_TICKET_STATUS) TRANSMNY_TICKET_STATUS,
               MIN(A.OUTSTOCK_TICKET_STATUS) OUTSTOCK_TICKET_STATUS,
               MIN(A.BILL_STATUS) BILL_STATUS,
               MIN(A.DELVRY_ADDR) DELVRY_ADDR,
               MIN(A.BILL_TICKET_STATUS) BILL_TICKET_STATUS,
               min(a.plan_trans_type) plan_trans_type,
               min(a.extra_flag) extra_flag,
               sum(a.plan_num) plan_num,
               sum(a.plan_wt) plan_wt,
               min(a.management_no) management_no,
              min(a.management_name) management_name,
               min(a.sale_org_desc) sale_org_desc,
               min(a.agency_fl) agency_fl,
               min(a.supply_unit) supply_unit,
               min(a.supply_unit_desc) supply_unit_desc,
               a.BILL_NO,
              (select listagg(t3.STORAGE_NAME,';') WITHIN GROUP(ORDER BY t3.bill_no) from (SELECT distinct t1.STORAGE_NAME,t2.bill_no FROM YDM_BS_STORAGE t1,YDM_ZC_BILL_c t2  WHERE t1.VALIDFLAG='1' and t1.storage_no=t2.storage_no)t3  where a.bill_no=t3.bill_no)trans_area
        
          from YDM_ZC_BILL_M a
         where 1 = 1
           and a.validflag = '1'
            {0}
           AND (A.BILL_STATUS >= '4' OR A.ISPRECLOSE >= '1')
         group by a.bill_no) A
  LEFT JOIN YDM_ZC_LOADVEHICLE_MAT B ON A.BILL_NO = B.BILL_NO
                                    AND B.VALIDFLAG <> '0'
 
  left join (select bill_no,
                    min(ord_pk) ord_pk,
                    min(ord_ln_pk) ord_ln_pk,
                    min(ord_ln_dly_pk) ord_ln_dly_pk
               from ydm_order_delivery_transit
              group by bill_no) tr on a.bill_no = tr.bill_no

 GROUP BY a.BILL_NO,b.bill_no


                                ";

            return str;
        }


        private string GetBillMateByBillNo(string billno)
        {
            string str = @" select a.*,
       case
         when ticketype = '无' then
          0
         else
          weight *
          (decode(TRANS_TPE, '110501', 8.62, '110502', 19.83, 8.62))
       end outstockmny,
       case
         when ticketype = '无' then
          '自付'
         else
          'TPCO付'
       end TKp
  from (select min(a.order_no || '/' || a.order_seq) orderno,
               a.JUDGE_STOVE_NO,
               a.batch_no,
               sum(a.act_count) quantity,
               sum(case
                     when a.ponder_gross_wt > 0 then
                      a.ponder_gross_wt
                     WHEN (NVL(a.PONDER_GROSS_WT,0) <= 0) AND ((SELECT min(ORDER_UNIT) FROM SLM_ORDER_LINE WHERE ORD_LN_PK = a.ORD_LN_PK) = '吨') AND (NVL(a.SEND_NUM,0)>0) THEN
                      a.SEND_NUM
                     else
                      a.act_weight
                   end) weight,
               a.ord_pk,
               a.ord_ln_pk,
               a.ord_ln_dly_pk,
               min(b.agency_fl) agency_fl,
               min(b.delvry_addr) delvry_addr,
               min(b.plan_trans_type) TRANS_TPE,
                min(a.pline_name) pline_name,
               min(case
                   when b.EXTRA_FLAG = '1' then 
                  '应收'
                
                     when (b.delvry_addr = 'TPCO库房车板' or b.delvry_addr = '指定地点') then
                      '报销'
                     else
                      '无'
                   end
                   
                   ) ticketype
        
          from ydm_zc_loadvehicle_mat a
         join ( select  min(b.agency_fl) agency_fl,
               min(b.delvry_addr) delvry_addr,
               min(b.plan_trans_type) plan_trans_type,bill_no ,
               min(EXTRA_FLAG) EXTRA_FLAG from 
                ydm_zc_bill_m b group by bill_no  ) b 
          on a.bill_no = b.bill_no
       where 1 = 1
       and  a.validflag <> '0'
       {0}
         group by a.bill_no,
                  a.order_no || a.order_seq,
                  a.judge_stove_no,
                  a.batch_no,
                  a.ord_pk,
                  a.ord_ln_pk,
                  a.ord_ln_dly_pk) a
";

            str = string.Format(str, string.Format("   and a.bill_no = '{0}' ",billno));
            return str;

        }


        private string GetTransDelivByBIllNo(string billNo)
        {
            string sqlStr = @"  select B.INDEX_SEQ,
                                           B.AGENCY_FL,
                                           B.TRANSIT_TYP,
                                           B.DESTINATION,
                                           B.STATION_NM,
                                           B.SPCL_LN_NM,
                                           C.DELVRY_ADDR,
                                           c.RECEIV_ADDR,
                                           a.bill_no,
                                           b.ord_pk,
                                           b.ord_ln_pk,
                                           b.ord_ln_dly_pk
                                      from (select min(ord_pk || ord_ln_pk || ord_ln_dly_pk) ord_pk, a.bill_no
                                              from ydm_zc_bill_m a
                                             where a.validflag = '1'
                                             {0}
                                             group by a.bill_no) a
                                      join slm_order_delivery_transit b on b.ord_pk || b.ord_ln_pk ||
                                                                           b.ord_ln_dly_pk = a.ord_pk

                                      join slm_order_line_delivery c on c.ord_pk = b.ord_pk
                                                                    and c.ord_ln_pk = b.ord_ln_pk
                                                                    and c.ord_ln_dly_pk = b.ord_ln_dly_pk
                                     where b.validflag = '1'
                                    ";
            sqlStr = string.Format(sqlStr, string.Format("  and a.bill_no =  '{0}' ",billNo));
            return sqlStr;
        }


        private string GetVirtualBillInfoByBillNo(string billno)
        {
            string str = @"
                        select a.*,
       case
         when ticketype = '无' then
          0
         else
          0 * (decode(TRANS_TPE, '110501', 8.62, '110502', 19.83, 8.62))
       end outstockmny,
       case
         when ticketype = '无' then
          '自付'
         else
          'TPCO付'
       end TKp
  from (select min(a.order_no || '/' || a.order_seq) orderno,
               a.bill_no,
               c.JUDGE_STOVE_NO,
               c.batch_no,
               sum(c.act_count) quantity,
               sum(c.act_weight) weight,
               a.ord_pk,
               a.ord_ln_pk,
               a.ord_ln_dly_pk,
              min(pline_name) pline_name,
               min(a.agency_fl) agency_fl,
               min(a.delvry_addr) delvry_addr,
               min(a.plan_trans_type) TRANS_TPE,
               min(case
                     when a.agency_fl = '0' and a.delvry_addr = 'TPCO库房' then
                      '无'
                     when a.agency_fl = '1' and a.delvry_addr = 'TPCO库房' then
                      '应收'
                     when (a.delvry_addr = 'TPCO库房车板' or a.delvry_addr = '指定地点') then
                      '报销'
                     else
                      '无'
                   end
                   
                   ) ticketype
        
          from ydm_zc_bill_m a
          join ydm_zc_bill_c c on a.bill_no = c.bill_no
          and a.ord_pk=c.ord_pk
           and a.ord_ln_pk = c.ord_ln_pk
           and a.ord_ln_dly_pk = c.ord_ln_dly_pk
         {0}
         where 1 = 1
           and a.validflag <> '0'
           and a.loadvehicle_type = '2'
           and c.validflag = '1'
         --  and a.bill_no = 'THM1510260005'
         group by a.bill_no,
                  a.order_no || a.order_seq,
                  c.judge_stove_no,
                  c.batch_no,
                  a.ord_pk,
                  a.ord_ln_pk,
                  a.ord_ln_dly_pk) a
                                  ";
            str = string.Format(str, string.Format("  and a.bill_no = '{0}' ", billno));
            return str;
        }

        private string BILL_NO = "BILL_NO";
        private string _ServerName = "core.stlmes.server.sale.service.balance.SlmBalanceTickets";


    }
}