ABAP RFC SQL 模糊查询和多个区间条件

对于非选择屏幕的情况，RFC接口输入数据后，如何处理字符串模糊查询、日期区间查询、数字区间查询：

一、所有字符支持模糊查询，在SAP SQL中，使用 %S%来实现。

二、区间查询有3种情况：

1、没有值输入，程序需要填入最大上下限

BETWEEN  20200101  TO  99991231

2、输入单个值，如20240930，代码中在BETWEEN,TO两个字段填入

BETWEEN  20240930  TO  20240930

3、输入两个值，如20240901-20240930代码中在BETWEEN,TO两个字段填入

BETWEEN  20240901  TO  20240930

FUNCTION ZSAP_LIGHT_ADS_GET.
*"----------------------------------------------------------------------
*"*"本地接口：
*"  IMPORTING
*"     VALUE(ZSAVEDATE) TYPE  CHAR8
*"     VALUE(ZSAVETIME) TYPE  CHAR6
*"     VALUE(ZTYPE) TYPE  CHAR10
*"     VALUE(ZSPNUMBER) TYPE  CHAR20
*"     VALUE(ZDATE_FROM) TYPE  CHAR8
*"     VALUE(ZDATE_TO) TYPE  CHAR8
*"     VALUE(ZFPNUMBER) TYPE  CHAR50
*"     VALUE(VIN) TYPE  CHAR20
*"     VALUE(ZFPTYPE) TYPE  CHAR50
*"     VALUE(ZMONEY_FROM) TYPE  J_1BEXBASE
*"     VALUE(ZMONEY_TO) TYPE  J_1BEXBASE
*"     VALUE(TTNAME) TYPE  CHAR50
*"     VALUE(SHNUMBER) TYPE  CHAR30
*"     VALUE(ZEMAIL) TYPE  CHAR50
*"     VALUE(ZBZ) TYPE  CHAR50
*"  TABLES
*"      OT_LIST STRUCTURE  ZTLIGHT_ADS
*"----------------------------------------------------------------------



DATA:
    lv_ZTYPE TYPE string,
    lv_ZSPNUMBER TYPE string,
    lv_ZFPNUMBER TYPE string,
    lv_VIN TYPE string,
    lv_ZFPTYPE TYPE string,

    lv_TTNAME TYPE string,
    lv_SHNUMBER TYPE string,
    lv_ZEMAIL TYPE string,
    lv_ZBZ TYPE string.


    CONCATENATE '%' ZTYPE       '%' INTO lv_ZTYPE.
    CONCATENATE '%' ZSPNUMBER   '%' INTO lv_ZSPNUMBER.
    CONCATENATE '%' ZFPNUMBER   '%' INTO lv_ZFPNUMBER.
    CONCATENATE '%' VIN         '%' INTO lv_VIN.
    CONCATENATE '%' ZFPTYPE     '%' INTO lv_ZFPTYPE.
    CONCATENATE '%' TTNAME      '%' INTO lv_TTNAME.
    CONCATENATE '%' SHNUMBER    '%' INTO lv_SHNUMBER.
    CONCATENATE '%' ZEMAIL      '%' INTO lv_ZEMAIL.
    CONCATENATE '%' ZBZ         '%' INTO lv_ZBZ.


      IF ( ZDATE_from is INITIAL  AND  ZDATE_to is INITIAL ) .

        ZDATE_from = '20200101'.
        ZDATE_to = '99991231'.

      ENDIF.


      IF ( ZDATE_from is not INITIAL  AND  ZDATE_to is INITIAL ) .

        ZDATE_to = ZDATE_from.

      ENDIF.


      IF ( ZDATE_from is  INITIAL  AND  ZDATE_to is not INITIAL ) .

        ZDATE_from  = ZDATE_to.

      ENDIF.


      IF ( ZMONEY_from = 0  AND  ZMONEY_to = 0 ) .

        ZMONEY_from = '-99999999'.
        ZMONEY_to = '99999999999'.

      ENDIF.


      IF ( ZMONEY_from <> 0  AND  ZMONEY_to = 0 ) .

        ZMONEY_to = ZMONEY_from.

      ENDIF.


      IF ( ZMONEY_from = 0  AND  ZMONEY_to <> 0 ) .

        ZMONEY_from  = ZMONEY_to.

      ENDIF.


       SELECT *  FROM ZTLIGHT_ADS

       WHERE ZTYPE      like  @lv_ZTYPE
       AND   ZSPNUMBER  like  @lv_ZSPNUMBER
       AND   ZFPNUMBER  like  @lv_ZFPNUMBER
       AND   VIN        like  @lv_VIN
       AND   ZFPTYPE    like  @lv_ZFPTYPE
       AND   TTNAME     like  @lv_TTNAME
       AND   SHNUMBER   like  @lv_SHNUMBER
       AND   ZEMAIL     like  @lv_ZEMAIL
       AND   ZBZ        like  @lv_ZBZ
       AND   ZDATE      BETWEEN @ZDATE_from AND @ZDATE_to
       AND   ZMONEY     BETWEEN @ZMONEY_from AND @ZMONEY_to
       INTO TABLE @OT_LIST.


ENDFUNCTION.

注意，在UI端要检查一下日期是否合法，数字区间要规范成0

 Get_InputParameters inputpara = new Get_InputParameters()
                {
                    ZTYPE = toolStripTextBox1.Text,
                    ZSPNUMBER = toolStripTextBox2.Text,
                    VIN = toolStripTextBox3.Text,
                    ZFPNUMBER = toolStripTextBox4.Text,
                    ZDATE_FROM = MT.DATE_CHECK(toolStripTextBox5.Text),
                    ZDATE_TO = MT.DATE_CHECK(toolStripTextBox6.Text),
                    ZMONEY_FROM = MT.num_from(toolStripTextBox7.Text),
                    ZMONEY_TO = MT.num_to(toolStripTextBox8.Text)

                };

using ClosedXML.Excel;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Drawing.Diagrams;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

//MyTools   MT
namespace Light
{
    internal class MT
    {


        public static double num_from(string s)
        {
            double tmp = 0;
            if (s.Count() > 0) { tmp = double.Parse(s); }
            return tmp;
        }

        public static double num_to(string s)
        {
            double tmp = 0;
            if (s.Count() > 0) { tmp = double.Parse(s); }
            return tmp;
        }


        public static string DATE_CHECK (string s)
        {
            string tmp = "";
            
            if (s.Count()>0)
            { 
                if (IsValidDate(s))
                {
                    tmp = s;
                   
                }
                else 
                {
                    MessageBox.Show("注意：这个日期是错误的！" + s );
                    tmp = "20240930";
                }
            }
            return tmp;
        }


        public static string PickBox(ClosedXML.Excel.IXLCell cell)
        {
            string tmp = "";

            if (!cell.IsEmpty() & cell.Value.IsText) { tmp = cell.Value.GetText(); }            
            else if (!cell.IsEmpty() & cell.Value.IsNumber) { tmp = cell.Value.GetNumber().ToString(); }

            return tmp;
        }



        //ClosedXML可能会把日期认成字符串
        public static string PickDate(ClosedXML.Excel.IXLCell cell)
        {
            string tmp = "";

            if (cell.Value.IsDateTime)
            {
                System.DateTime dateValue = cell.GetValue<System.DateTime>();
                tmp = dateValue.ToString("yyyyMMdd");
            }
            else
            {
                tmp = System.DateTime.Parse(cell.Value.GetText()).ToString("yyyyMMdd");
            }

            /*
            if (cell.DataType == XLDataType.DateTime)
            {
                System.DateTime dateValue = cell.GetValue<System.DateTime>();
                tmp = dateValue.ToString("yyyyMMdd");
            }
            else
            {
                tmp = System.DateTime.Parse(cell.GetString()).ToString("yyyyMMdd");
            }
            */
            return tmp;
        }


        public static bool IsValidDate(string dateString)
        {
            if (dateString == null || dateString.Length != 8)
                return false;

            int year = int.Parse(dateString.Substring(0, 4));
            int month = int.Parse(dateString.Substring(4, 2));
            int day = int.Parse(dateString.Substring(6, 2));

            // 检查年份是否在1-9999范围内
            if (year < 1 || year > 9999)
                return false;

            // 检查月份是否在1-12范围内
            if (month < 1 || month > 12)
                return false;

            // 根据年份和月份判断天数的合法范围
            int daysInMonth = DateTime.DaysInMonth(year, month);

            // 检查日期是否在1-最大日数范围内
            if (day < 1 || day > daysInMonth)
                return false;

            return true;
        }



    }

}