using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using GThinking.N_MODEL.Page;
using GThinking.N_MISPRO.Data;
using System.Text;

namespace GThinking.N_XLXSBJ
{
    [EntityType(Db = "DBERP", Table = "XLXSBJDCLHQ", IsWf = false, IsWhr = true, IsAttach = false)]

    public class XLBJD_CLHQTZ : EntityObject
    {
        public string sBJDID = "";
        public string sVID = "";
        public string sRYID = "";
        public string sBJRQ = "";
        public string sBJFLID = "";
        public string sJGBID = "";
        public string sJGBIDS = "";
        public string sBJDHQLY = "";

        public bool bHSBZ = true;
        public string sTBJCLID = "";
        public string sLBJCLID = "";
        public string sTJ = "";
        public string sLJ = "";
        public string sBJU057 = "N";

        public override TSQL SetQuerySql()
        {
            TSQL SQL = base.SetQuerySql();
            if (!XLBJDUtils.IsJGEJGB(sJGBID))//表价实时计算价格表
            {
                if (sBJDHQLY.Equals("1"))
                {
                    if (!sJGBIDS.Equals(""))
                    {
                        SQL.Add(GetBJDHQ_VSql(sJGBIDS).ToString());
                        if (sBJU057.Trim().Equals("Y"))
                        {
                            SQL.Add(" where isnull(BJCL_PX.XLJGCZBLM_BJCLID,' ') <> ' ' ");
                        }
                        SQL.Add(" order by px0,px,px1,isnull(XLXSBJCL_TYBZ,'N'),XLXSBJCL_BJCLID");
                        SQL.ParamByName("pJGBIDS").Value = sJGBIDS;
                    }
                    else
                    {
                        SQL.Add(GetBJDHQ_VSql().ToString());
                        if (sBJU057.Trim().Equals("Y"))
                        {
                            SQL.Add(" where isnull(BJCL_PX.XLJGCZBLM_BJCLID,' ') <> ' ' ");
                        }
                        SQL.Add(" order by px0,px,isnull(XLXSBJCL_TYBZ,'N'),XLXSBJCL_BJCLID");
                    }
                   
                    SQL.ParamByName("pBJDID").Value = sBJDID;
                    SQL.ParamByName("pVID").Value = sVID;
                    SQL.ParamByName("pRYID").Value = sRYID;
                    SQL.ParamByName("pBJRQ").Value = sBJRQ;
                    SQL.ParamByName("pBJFLID").Value = sBJFLID;
                    SQL.ParamByName("pJGBID").Value = sJGBID;
                }
                else
                {
                    SQL.Add(GetBJRHQ_VSql().ToString());
                    if (sBJU057.Trim().Equals("Y"))
                    {
                        SQL.Add(" where isnull(BJCL_PX.XLJGCZBLM_BJCLID,' ') <> ' ' ");
                    }
                    SQL.Add(" order by px0,px,isnull(XLXSBJCL_TYBZ,'N'),XLXSBJCL_BJCLID");
                    SQL.ParamByName("pBJDID").Value = sBJDID;
                    SQL.ParamByName("pVID").Value = sVID;
                    SQL.ParamByName("pRYID").Value = sRYID;
                    SQL.ParamByName("pBJRQ").Value = sBJRQ;
                }
            }
            else
            {
                SQL.Add(GetJGEHQ_VSql(bHSBZ, sTBJCLID, sLBJCLID, sTJ, sLJ).ToString());
                if (sBJU057.Trim().Equals("Y"))
                {
                    SQL.Add(" where isnull(BJCL_PX.XLJGCZBLM_BJCLID,' ') <> ' ' ");
                }
                SQL.Add(" order by px0,px,isnull(XLXSBJCL_TYBZ,'N'),XLXSBJCL_BJCLID");
                SQL.ParamByName("pBJDID").Value = sBJDID;
                SQL.ParamByName("pVID").Value = sVID;
                SQL.ParamByName("pRYID").Value = sRYID;
                SQL.ParamByName("pBJRQ").Value = sBJRQ;
            }
            return SQL;
        }

        

        public override bool Querying(TSQL sql)
        {
            Entites.Columns.Clear();
            Entites.Clear();
            return base.Querying(sql);
        }

        /// <summary>
        /// XLXSBJDCLHQ数据可能还没有的情况下，构建报价单行情sql
        /// </summary>
        /// <returns></returns>
        public static StringBuilder GetBJDHQSql()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  1 ELSE 0 end as px, case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  'N' ELSE 'Y' end as SYF, ");
            strSQL.AppendLine(" XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else XLXSBJDCLHQ_WSJG end as CLJG,");
            strSQL.AppendLine("XLXSBJCL_ZZSL,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else XLXSBJDCLHQ_HSJG end as HSJG,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else XLXSBJDCLHQ_WSJGF end as WSJGF,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else XLXSBJDCLHQ_HSJGF end as HSJGF,");
            strSQL.AppendLine(" 0 as SYJGB_HSJG, 0 as px1, ");
            strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, ");
            strSQL.AppendLine("XLXSBJDCLHQ_WHR,XLXSBJDCLHQ_WHRID,XLXSBJDCLHQ_WHSJ,  ");
            strSQL.AppendLine("   (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID  or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID  ");
            strSQL.AppendLine(" from XLXSBJCL");
            strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID ");
            strSQL.AppendLine(" left join XLXSBJDCLHQ on XLXSBJDCLHQ_BJDID=:pBJDID and XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID");
            ///---------------------------------------------------------------------------
            strSQL.AppendLine(" left join ");
            strSQL.AppendLine(" (");
            //报价单报价日之材料行情
            strSQL.AppendLine("    select BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF ");
            strSQL.AppendLine("     from XLXSCLHQ,");
            strSQL.AppendLine("     (");
            strSQL.AppendLine("      select XLXSCLHQ_BJCLID as BJCLID,");
            strSQL.AppendLine("             max(XLXSCLHQ_YXQS)as maxYXQS");
            strSQL.AppendLine("      from XLXSCLHQ,XLXSBJCL");
            strSQL.AppendLine("      where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("        and XLXSCLHQ_YXQS<=:pBJRQ");
            strSQL.AppendLine("        and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)");
            strSQL.AppendLine("        and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID");
            strSQL.AppendLine("        and exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//报价日行情之报价材料
            strSQL.AppendLine("      group by XLXSCLHQ_BJCLID");
            strSQL.AppendLine("     ) x");
            strSQL.AppendLine("     where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("       and XLXSCLHQ_BJCLID=BJCLID");
            strSQL.AppendLine("       and XLXSCLHQ_YXQS=maxYXQS");

            strSQL.AppendLine(" union all ");
            //报价单非报价日之材料行情
            strSQL.AppendLine("    select XLXSCLHQ_BJCLID as BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF ");
            strSQL.AppendLine("     from XLXSCLHQ,XLXSBJCL");
            strSQL.AppendLine("     where XLXSCLHQ_JGBID<>'ZZZZZZZZ'");
            strSQL.AppendLine("       and XLXSCLHQ_JGBID=:pJGBID");
            strSQL.AppendLine("       and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID");
            strSQL.AppendLine("       and not exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//非报价日行情的报价材料
            strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID");
            ///---------------------------------------------------------------------------
            //关联使用的材料，提上显示
            strSQL.AppendLine(" LEFT JOIN");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("     SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX");
            strSQL.AppendLine("     LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') ");
            //利用版本限制
            strSQL.AppendLine("     LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLJGCZBLM_JGCZBBBID = XLXSCLQD_JGCZBBBID  ");
            strSQL.AppendLine("     WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'"); 
            strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID ");

            //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID");
            return strSQL;
        }

        public static StringBuilder GetBJDHQ_VSql()
        {
            return GetBJDHQ_VSql("");
        }

        public static StringBuilder GetBJDHQ_VSql(string sJGBIDS)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  1 ELSE 0 end as px, case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  'N' ELSE 'Y' end as SYF, ");
            strSQL.AppendLine(" XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else V_XLXSBJDCLHQ_WSJG end as CLJG,");
            strSQL.AppendLine("XLXSBJCL_ZZSL,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else V_XLXSBJDCLHQ_HSJG end as HSJG,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else V_XLXSBJDCLHQ_WSJGF end as WSJGF,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else V_XLXSBJDCLHQ_HSJGF end as HSJGF,");
            if (!sJGBIDS.Equals(""))
            {
                strSQL.AppendLine("  isnull(e.XLXSCLHQ_HSJG,0.0) as SYJGB_HSJG, case when isnull(bjdclhq.XLXSCLHQ_HSJG,0.0) <> isnull(e.XLXSCLHQ_HSJG,0.0) then 0 else 1 end  as px1, ");
            }
            else
            {
                strSQL.AppendLine(" 0 as SYJGB_HSJG, 0 as px1, ");
            }
            strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, ");
            strSQL.AppendLine("isnull(V_XLXSBJDCLHQ_JGYBJCLID1,' ') as V_XLXSBJDCLHQ_JGYBJCLID1, isnull(V_XLXSBJDCLHQ_JGBL1,0.0) as V_XLXSBJDCLHQ_JGBL1,  ");
            strSQL.AppendLine("V_XLXSBJDCLHQ_WHR,V_XLXSBJDCLHQ_WHRID,V_XLXSBJDCLHQ_WHSJ,  ");
            strSQL.AppendLine("   (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID  or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID  ");
            strSQL.AppendLine(" from XLXSBJCL");
            strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID ");
            strSQL.AppendLine(" left join V_XLXSBJDCLHQ on V_XLXSBJDCLHQ_BJDID=:pBJDID and V_XLXSBJDCLHQ_ID=:pVID  and V_XLXSBJDCLHQ_TZRYID=:pRYID and V_XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID ");
            ///---------------------------------------------------------------------------
            strSQL.AppendLine(" left join ");
            strSQL.AppendLine(" (");
            //报价单报价日之材料行情
            strSQL.AppendLine("    select BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF ");
            strSQL.AppendLine("     from XLXSCLHQ,");
            strSQL.AppendLine("     (");
            strSQL.AppendLine("      select XLXSCLHQ_BJCLID as BJCLID,");
            strSQL.AppendLine("             max(XLXSCLHQ_YXQS)as maxYXQS");
            strSQL.AppendLine("      from XLXSCLHQ,XLXSBJCL");
            strSQL.AppendLine("      where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("        and XLXSCLHQ_YXQS<=:pBJRQ");
            strSQL.AppendLine("        and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)");
            strSQL.AppendLine("        and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID");
            strSQL.AppendLine("        and exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//报价日行情之报价材料
            strSQL.AppendLine("      group by XLXSCLHQ_BJCLID");
            strSQL.AppendLine("     ) x");
            strSQL.AppendLine("     where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("       and XLXSCLHQ_BJCLID=BJCLID");
            strSQL.AppendLine("       and XLXSCLHQ_YXQS=maxYXQS");

            strSQL.AppendLine(" union all ");
            //报价单非报价日之材料行情
            strSQL.AppendLine("    select XLXSCLHQ_BJCLID as BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF ");
            strSQL.AppendLine("     from XLXSCLHQ,XLXSBJCL");
            strSQL.AppendLine("     where XLXSCLHQ_JGBID<>'ZZZZZZZZ'");
            strSQL.AppendLine("       and XLXSCLHQ_JGBID=:pJGBID");
            strSQL.AppendLine("       and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID");
            strSQL.AppendLine("       and not exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//非报价日行情的报价材料
            strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID");
            if (!sJGBIDS.Equals(""))
            {
                ///---------------------------------------------------------------------------因素为1,'',''
                strSQL.AppendLine(" left join ");
                strSQL.AppendLine(" (");
                //报价单报价日之材料行情
                strSQL.AppendLine("    select BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF ");
                strSQL.AppendLine("     from XLXSCLHQ,");
                strSQL.AppendLine("     (");
                strSQL.AppendLine("      select XLXSCLHQ_BJCLID as BJCLID,");
                strSQL.AppendLine("             max(XLXSCLHQ_YXQS)as maxYXQS");
                strSQL.AppendLine("      from XLXSCLHQ,XLXSBJCL");
                strSQL.AppendLine("      where XLXSCLHQ_JGBID='ZZZZZZZZ'");
                strSQL.AppendLine("        and XLXSCLHQ_YXQS<=:pBJRQ");
                strSQL.AppendLine("        and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)");
                strSQL.AppendLine("        and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID");
                strSQL.AppendLine("        and exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//报价日行情之报价材料
                strSQL.AppendLine("      group by XLXSCLHQ_BJCLID");
                strSQL.AppendLine("     ) x");
                strSQL.AppendLine("     where XLXSCLHQ_JGBID='ZZZZZZZZ'");
                strSQL.AppendLine("       and XLXSCLHQ_BJCLID=BJCLID");
                strSQL.AppendLine("       and XLXSCLHQ_YXQS=maxYXQS");

                strSQL.AppendLine(" union all ");
                //报价单非报价日之材料行情
                strSQL.AppendLine("    select XLXSCLHQ_BJCLID as BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF ");
                strSQL.AppendLine("     from XLXSCLHQ,XLXSBJCL");
                strSQL.AppendLine("     where XLXSCLHQ_JGBID<>'ZZZZZZZZ'");
                strSQL.AppendLine("       and XLXSCLHQ_JGBID=:pJGBIDS");
                strSQL.AppendLine("       and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID");
                strSQL.AppendLine("       and not exists(select 1 from XLXSBJFLBJRHQCL where XLXSBJFLBJRHQCL_BJFLID=:pBJFLID and XLXSBJFLBJRHQCL_BJCLID=XLXSBJCL_BJCLID)");//非报价日行情的报价材料
                strSQL.AppendLine(" ) e on e.BJCLID=XLXSBJCL_BJCLID");

            }
            ///---------------------------------------------------------------------------
            //关联使用的材料，提上显示
            strSQL.AppendLine(" LEFT JOIN");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("     SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX");
            strSQL.AppendLine("     LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') ");
            //利用版本限制
            strSQL.AppendLine("     LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLJGCZBLM_JGCZBBBID = XLXSCLQD_JGCZBBBID  ");
            strSQL.AppendLine("     WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'");
            strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID ");

            //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID");
            return strSQL;
        }

        public static StringBuilder GetBJRHQSql()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  1 ELSE 0 end as px, case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  'N' ELSE 'Y' end as SYF, ");
            strSQL.AppendLine(" XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else XLXSBJDCLHQ_WSJG end as CLJG,");
            strSQL.AppendLine("XLXSBJCL_ZZSL,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else XLXSBJDCLHQ_HSJG end as HSJG,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else XLXSBJDCLHQ_WSJGF end as WSJGF,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else XLXSBJDCLHQ_HSJGF end as HSJGF,");
            strSQL.AppendLine("0 as SYJGB_HSJG, 0 as px1,");
            strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, ");
            strSQL.AppendLine("XLXSBJDCLHQ_WHR,XLXSBJDCLHQ_WHRID,XLXSBJDCLHQ_WHSJ, ");
            strSQL.AppendLine("   (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID  or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID  ");
            strSQL.AppendLine(" from XLXSBJCL");
            strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID ");
            strSQL.AppendLine(" left join XLXSBJDCLHQ on XLXSBJDCLHQ_BJDID=:pBJDID and XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID");
            strSQL.AppendLine(" left join ");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("select BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF");
            strSQL.AppendLine(" from XLXSCLHQ,");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("  select XLXSCLHQ_BJCLID as BJCLID,");
            strSQL.AppendLine("         max(XLXSCLHQ_YXQS)as maxYXQS");
            strSQL.AppendLine("  from XLXSCLHQ,XLXSBJCL");
            strSQL.AppendLine("  where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("    and XLXSCLHQ_YXQS<=:pBJRQ");
            strSQL.AppendLine("    and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)");
            strSQL.AppendLine("    and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID");
            strSQL.AppendLine("  group by XLXSCLHQ_BJCLID");
            strSQL.AppendLine(" ) x");
            strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("   and XLXSCLHQ_BJCLID=BJCLID");
            strSQL.AppendLine("   and XLXSCLHQ_YXQS=maxYXQS");
            strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID");
            //关联使用的材料，提上显示
            strSQL.AppendLine(" LEFT JOIN");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("     SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX");
            strSQL.AppendLine("     LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') ");
            strSQL.AppendLine("     LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLXSCLQD_JGCZBBBID = XLJGCZBLM_JGCZBBBID  ");
            strSQL.AppendLine("     WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'"); 
            strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID ");
            //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID");
            return strSQL;
        }

        public static StringBuilder GetBJRHQ_VSql()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  1 ELSE 0 end as px, case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  'N' ELSE 'Y' end as SYF, ");
            strSQL.AppendLine(" XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else V_XLXSBJDCLHQ_WSJG end as CLJG,");
            strSQL.AppendLine("XLXSBJCL_ZZSL,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else V_XLXSBJDCLHQ_HSJG end as HSJG,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else V_XLXSBJDCLHQ_WSJGF end as WSJGF,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else V_XLXSBJDCLHQ_HSJGF end as HSJGF,");
            strSQL.AppendLine("0 as SYJGB_HSJG, 0 as px1,");
            strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, ");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_JGYBJCLID1 else isnull(V_XLXSBJDCLHQ_JGYBJCLID1,' ') end  as V_XLXSBJDCLHQ_JGYBJCLID1,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_JGBL1 else isnull(V_XLXSBJDCLHQ_JGBL1,0.0) end as V_XLXSBJDCLHQ_JGBL1,");
            //strSQL.AppendLine("isnull(V_XLXSBJDCLHQ_JGYBJCLID1,' ') as V_XLXSBJDCLHQ_JGYBJCLID1, isnull(V_XLXSBJDCLHQ_JGBL1,0.0) as V_XLXSBJDCLHQ_JGBL1,  ");
            strSQL.AppendLine("V_XLXSBJDCLHQ_WHR,V_XLXSBJDCLHQ_WHRID,V_XLXSBJDCLHQ_WHSJ, ");
            strSQL.AppendLine("   (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID  or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID  ");
            strSQL.AppendLine(" from XLXSBJCL");
            strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID ");
            strSQL.AppendLine(" left join V_XLXSBJDCLHQ on V_XLXSBJDCLHQ_BJDID=:pBJDID and V_XLXSBJDCLHQ_ID=:pVID  and V_XLXSBJDCLHQ_TZRYID=:pRYID and V_XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID ");
            strSQL.AppendLine(" left join ");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("select BJCLID,XLXSCLHQ_WSJG,XLXSCLHQ_HSJG,XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF,XLXSCLHQ_JGYBJCLID1,XLXSCLHQ_JGBL1 ");
            strSQL.AppendLine(" from XLXSCLHQ,");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("  select XLXSCLHQ_BJCLID as BJCLID,");
            strSQL.AppendLine("         max(XLXSCLHQ_YXQS)as maxYXQS");
            strSQL.AppendLine("  from XLXSCLHQ,XLXSBJCL");
            strSQL.AppendLine("  where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("    and XLXSCLHQ_YXQS<=:pBJRQ");
            strSQL.AppendLine("    and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)");
            strSQL.AppendLine("    and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID");
            strSQL.AppendLine("  group by XLXSCLHQ_BJCLID");
            strSQL.AppendLine(" ) x");
            strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("   and XLXSCLHQ_BJCLID=BJCLID");
            strSQL.AppendLine("   and XLXSCLHQ_YXQS=maxYXQS");
            strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID");
            //关联使用的材料，提上显示
            strSQL.AppendLine(" LEFT JOIN");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("     SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX");
            strSQL.AppendLine("     LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') ");
            strSQL.AppendLine("     LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLXSCLQD_JGCZBBBID = XLJGCZBLM_JGCZBBBID  ");
            strSQL.AppendLine("     WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'");
            strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID ");
            //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID");
            return strSQL;
        }

        public static StringBuilder GetJGEHQSql(bool bHSBZ, string sTBJCLID, string sLBJCLID, string sTJ, string sLJ)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  1 ELSE 0 end as px,case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  'N' ELSE 'Y' end as SYF,");
            strSQL.AppendLine("XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else XLXSBJDCLHQ_WSJG end as CLJG,");
            strSQL.AppendLine("XLXSBJCL_ZZSL,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else XLXSBJDCLHQ_HSJG end as HSJG,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else XLXSBJDCLHQ_WSJGF end as WSJGF,");
            strSQL.AppendLine("case when XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else XLXSBJDCLHQ_HSJGF end as HSJGF,");
            strSQL.AppendLine("0 as SYJGB_HSJG, 0 as px1,");
            strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, ");
            strSQL.AppendLine("XLXSBJDCLHQ_WHR,XLXSBJDCLHQ_WHRID,XLXSBJDCLHQ_WHSJ, ");
            strSQL.AppendLine("   (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID  or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID  ");
            strSQL.AppendLine(" from XLXSBJCL");
            strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID ");
            strSQL.AppendLine(" left join XLXSBJDCLHQ on XLXSBJDCLHQ_BJDID=:pBJDID and XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID");
            strSQL.AppendLine(" left join ");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("select BJCLID,");
            if (bHSBZ)
            {
                strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_HSJGF", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_HSJGF", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine("                   else XLXSCLHQ_HSJG end as XLXSCLHQ_HSJG, ");

                strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}/(1+ZZSL/100.0)", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}/(1+ZZSL/100.0)", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}/(1+ZZSL/100.0)+XLXSCLHQ_WSJGF", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}/(1+ZZSL/100.0)+XLXSCLHQ_WSJGF", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine("                   else XLXSCLHQ_WSJG end as XLXSCLHQ_WSJG, ");

                strSQL.AppendLine(" XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF ");
            }
            else
            {
                strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_WSJGF", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_WSJGF", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine("                   else XLXSCLHQ_WSJG end as XLXSCLHQ_WSJG, ");

                strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}*(1+ZZSL/100.0)", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}*(1+ZZSL/100.0)", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}*(1+ZZSL/100.0)+XLXSCLHQ_HSJGF", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}*(1+ZZSL/100.0)+XLXSCLHQ_HSJGF", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine("                   else XLXSCLHQ_HSJG end as XLXSCLHQ_HSJG, ");

                strSQL.AppendLine(" XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF ");
            }
            strSQL.AppendLine(" from XLXSCLHQ,");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("  select XLXSCLHQ_BJCLID as BJCLID,XLXSBJCL_JGYBJCLID as JGYBJCLID,XLXSBJCL_ZZSL as ZZSL,");
            strSQL.AppendLine("         max(XLXSCLHQ_YXQS)as maxYXQS");
            strSQL.AppendLine("  from XLXSCLHQ,XLXSBJCL");
            strSQL.AppendLine("  where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("    and XLXSCLHQ_YXQS<=:pBJRQ");
            strSQL.AppendLine("    and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)");
            strSQL.AppendLine("    and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID");
            strSQL.AppendLine("  group by XLXSCLHQ_BJCLID,XLXSBJCL_JGYBJCLID,XLXSBJCL_ZZSL");
            strSQL.AppendLine(" ) x");
            strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("   and XLXSCLHQ_BJCLID=BJCLID");
            strSQL.AppendLine("   and XLXSCLHQ_YXQS=maxYXQS");
            strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID");
            //关联使用的材料，提上显示
            strSQL.AppendLine(" LEFT JOIN");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("     SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX");
            strSQL.AppendLine("     LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') ");
            strSQL.AppendLine("     LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLXSCLQD_JGCZBBBID = XLJGCZBLM_JGCZBBBID  ");
            strSQL.AppendLine("     WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'");
            strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID ");
            //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID");
            return strSQL;
        }

        public static StringBuilder GetJGEHQ_VSql(bool bHSBZ, string sTBJCLID, string sLBJCLID, string sTJ, string sLJ)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("select case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  1 ELSE 0 end as px,case when BJCL_PX.XLJGCZBLM_BJCLID is null THEN  'N' ELSE 'Y' end as SYF,");
            strSQL.AppendLine("XLXSBJCL_BJCLID,XLXSBJCL_BJCLMC || case when isnull(XLXSBJCL_TYBZ,'N')= 'Y' then '(停用)' else ' ' end as XLXSBJCL_BJCLMC,XLXSBJCL_BJHQ,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJG else V_XLXSBJDCLHQ_WSJG end as CLJG,");
            strSQL.AppendLine("XLXSBJCL_ZZSL,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJG else V_XLXSBJDCLHQ_HSJG end as HSJG,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_WSJGF else V_XLXSBJDCLHQ_WSJGF end as WSJGF,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_HSJGF else V_XLXSBJDCLHQ_HSJGF end as HSJGF,");
            strSQL.AppendLine("0 as SYJGB_HSJG, 0 as px1,");
            strSQL.AppendLine("case when isnull(JGYLID, ' ') = ' ' then 1 else 0 end as px0, ");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_JGYBJCLID1 else isnull(V_XLXSBJDCLHQ_JGYBJCLID1,' ') end  as V_XLXSBJDCLHQ_JGYBJCLID1,");
            strSQL.AppendLine("case when V_XLXSBJDCLHQ_BJCLID is null then bjdclhq.XLXSCLHQ_JGBL1 else isnull(V_XLXSBJDCLHQ_JGBL1,0.0) end as V_XLXSBJDCLHQ_JGBL1,");
            //strSQL.AppendLine("isnull(V_XLXSBJDCLHQ_JGYBJCLID1,' ') as V_XLXSBJDCLHQ_JGYBJCLID1, isnull(V_XLXSBJDCLHQ_JGBL1,0.0) as V_XLXSBJDCLHQ_JGBL1,  ");
            strSQL.AppendLine("V_XLXSBJDCLHQ_WHR,V_XLXSBJDCLHQ_WHRID,V_XLXSBJDCLHQ_WHSJ, ");
            strSQL.AppendLine("   (select top 1 XLXSBJCL_BJCLID from XLXSBJCL a where (a.XLXSBJCL_JGYBJCLID =XLXSBJCL.XLXSBJCL_BJCLID  or (a.XLXSBJCL_JGYBJCLID=XLXSBJCL.XLXSBJCL_JGYBJCLID and isnull(XLXSBJCL.XLXSBJCL_JGYBJCLID,' ') <> ' '))) as XLXSBJCL_JGYBJCLID  ");
            strSQL.AppendLine(" from XLXSBJCL");
            strSQL.AppendLine(" left join (select distinct XLXSBJCL_JGYBJCLID as JGYLID from XLXSBJCL where isnull(XLXSBJCL_JGYBJCLID,' ') <> ' ') JGYL on JGYL.JGYLID = XLXSBJCL_BJCLID ");
            strSQL.AppendLine(" left join V_XLXSBJDCLHQ on V_XLXSBJDCLHQ_BJDID=:pBJDID and V_XLXSBJDCLHQ_ID=:pVID  and V_XLXSBJDCLHQ_TZRYID=:pRYID and V_XLXSBJDCLHQ_BJCLID=XLXSBJCL_BJCLID ");
            strSQL.AppendLine(" left join ");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("select BJCLID,XLXSCLHQ_JGYBJCLID1,XLXSCLHQ_JGBL1,");
            if (bHSBZ)
            {
                strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_HSJGF", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_HSJGF", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine("                   else XLXSCLHQ_HSJG end as XLXSCLHQ_HSJG, ");

                strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}/(1+ZZSL/100.0)", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}/(1+ZZSL/100.0)", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}/(1+ZZSL/100.0)+XLXSCLHQ_WSJGF", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}/(1+ZZSL/100.0)+XLXSCLHQ_WSJGF", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine("                   else XLXSCLHQ_WSJG end as XLXSCLHQ_WSJG, ");

                strSQL.AppendLine(" XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF ");
            }
            else
            {
                strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_WSJGF", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}+XLXSCLHQ_WSJGF", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine("                   else XLXSCLHQ_WSJG end as XLXSCLHQ_WSJG, ");

                strSQL.AppendLine(string.Format("case when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}*(1+ZZSL/100.0)", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(BJCLID,' ')<>' ' and BJCLID='{0}' then {1}*(1+ZZSL/100.0)", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}*(1+ZZSL/100.0)+XLXSCLHQ_HSJGF", sTBJCLID.Trim(), sTJ));
                strSQL.AppendLine(string.Format("     when isnull(JGYBJCLID,' ')<>' ' and JGYBJCLID='{0}' then {1}*(1+ZZSL/100.0)+XLXSCLHQ_HSJGF", sLBJCLID.Trim(), sLJ));
                strSQL.AppendLine("                   else XLXSCLHQ_HSJG end as XLXSCLHQ_HSJG, ");

                strSQL.AppendLine(" XLXSCLHQ_WSJGF,XLXSCLHQ_HSJGF ");
            }
            strSQL.AppendLine(" from XLXSCLHQ,");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("  select XLXSCLHQ_BJCLID as BJCLID,XLXSBJCL_JGYBJCLID as JGYBJCLID,XLXSBJCL_ZZSL as ZZSL,");
            strSQL.AppendLine("         max(XLXSCLHQ_YXQS)as maxYXQS");
            strSQL.AppendLine("  from XLXSCLHQ,XLXSBJCL");
            strSQL.AppendLine("  where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("    and XLXSCLHQ_YXQS<=:pBJRQ");
            strSQL.AppendLine("    and (XLXSCLHQ_YXQZ>=:pBJRQ or XLXSCLHQ_YXQZ is null)");
            strSQL.AppendLine("    and XLXSBJCL_BJCLID=XLXSCLHQ_BJCLID");
            strSQL.AppendLine("  group by XLXSCLHQ_BJCLID,XLXSBJCL_JGYBJCLID,XLXSBJCL_ZZSL");
            strSQL.AppendLine(" ) x");
            strSQL.AppendLine(" where XLXSCLHQ_JGBID='ZZZZZZZZ'");
            strSQL.AppendLine("   and XLXSCLHQ_BJCLID=BJCLID");
            strSQL.AppendLine("   and XLXSCLHQ_YXQS=maxYXQS");
            strSQL.AppendLine(" ) bjdclhq on bjdclhq.BJCLID=XLXSBJCL_BJCLID");
            //关联使用的材料，提上显示
            strSQL.AppendLine(" LEFT JOIN");
            strSQL.AppendLine(" (");
            strSQL.AppendLine("     SELECT DISTINCT XLJGCZBLM_BJCLID FROM XLXSBJDMX");
            strSQL.AppendLine("     LEFT JOIN XLXSCLQD ON XLXSCLQD_XLXHID = XLXSBJDMX_XLXHID AND XLXSCLQD_XLGGID = XLXSBJDMX_XLGGID AND XLXSCLQD_XLBZID = XLXSBJDMX_XLBZID AND isnull(XLXSCLQD_DYDJID,' ') = isnull(XLXSBJDMX_DYDJID,' ') ");
            strSQL.AppendLine("     LEFT JOIN XLJGCZBLM ON XLJGCZBLM_JGCZBID = XLXSCLQD_JGCZBID and XLXSCLQD_JGCZBBBID = XLJGCZBLM_JGCZBBBID  ");
            strSQL.AppendLine("     WHERE XLXSBJDMX_BJDID = :pBJDID and isnull(XLJGCZBLM_CLQDL,' ')<>' ' and XLXSCLQD_WPID = 'ZZZZ'");
            strSQL.AppendLine(" ) BJCL_PX ON BJCL_PX.XLJGCZBLM_BJCLID = XLXSBJCL_BJCLID ");
            //strSQL.AppendLine(" order by px,XLXSBJCL_BJCLID");
            return strSQL;
        }
    }
}