N_XLXSBJ----MX/ZDFX.cs

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.Web.Script.Serialization;
using GThinking.N_MISPRO.Utility;

namespace GThinking.N_XLXSBJ
{
    [TableMap(DB = "DBERP", DbTableName = "XLXSBJD", Umid = 100860068, IsWorkFlow = false, IsAttach = false, UseWhr = false)]
    public class ZDFX : EntityManager
    {
    }

    [EntityType(Db = "DBERP", Table = "XLXSBJDMX", IsWf = false, IsWhr = false, IsAttach = false)]

    public class ZDFXEntity : EntityObject
    {
        #region  ÊôÐÔ±äÁ¿

        private string _BJDID;
        [ScriptIgnore]
        public string BJDID
        {
            get { return _BJDID; }
            set
            {
                _BJDID = value;
            }
        }
        //²úÆ·Öظ´±ê×¼
        private string _sCPCFXLBZ = "";
        public string sCPCFXLBZ
        {
            set
            {
                _sCPCFXLBZ = value;
            }
            get { return _sCPCFXLBZ; }
        }
        private string _sBZBJDIDFilter = "";
        public string sBZBJDIDFilter
        {
            set
            {
                _sBZBJDIDFilter = value;
            }
            get { return _sBZBJDIDFilter; }
        }
        #endregion

        public override TSQL SetQuerySql()
        {
            TSQL SQL = base.SetQuerySql();
            SQL.Add("select XLXSBJDMX_BJDID,XLXSBJDMX_XH, OLD_XLXSBJDMX_XH,XLXSBJDMX_XSSX, XLXSBJDMX_XLXHID, XLXSBJDMX_XLGGID, ");
            SQL.Add(" XLXSBJDMX_XLBZID,XLXSBJDMX_DYDJID,");
            SQL.Add(" XLXSBJDMX_XLYSID,XLXSBJDMX_WLID, XLXSBJDMX_CPMLMC,");
            SQL.Add(" HasCPMLID,");
            SQL.Add(" XLXSBJDMX_BJCP,XLWLXX,");
            SQL.Add(" XLXSBJDMX_WGCPBZ,XLXSBJDMX_JLDW,XLXSBJDMX_BJSL,CPCF,CPSLCF,GGSLCF ");
            SQL.Add(" from");
            SQL.Add(" (");

            SQL.Add(" select ROW_NUMBER()over(order by XLXSBJDMX_XSSX,XLXSBJDMX_XH) as RNum,");
            SQL.Add("        XLXSBJDMX_BJDID,XLXSBJDMX_XH, XLXSBJDMX_XH as OLD_XLXSBJDMX_XH,XLXSBJDMX_XSSX, XLXSBJDMX_XLXHID, XLXSBJDMX_XLGGID, ");
            SQL.Add(" rtrim(XLXSBJDMX_XLBZID)||'|'||rtrim(XLXSBJDMX_XLBZID)||'|'||rtrim(XLBZ_XLBZLXMC)  as XLXSBJDMX_XLBZID,XLXSBJDMX_DYDJID,");
            SQL.Add(" rtrim(XLXSBJDMX_XLYSID)||'|'||rtrim(XLYSB_XLYSMC)  as XLXSBJDMX_XLYSID,XLXSBJDMX_WLID, XLXSBJDMX_CPMLMC, ");
            SQL.Add(" case ");
            SQL.Add("       (case when ISNULL(A.XLXSBJXHML_CPMLID,' ') = ' ' then ISNULL(B.XLXSBJXHML_CPMLID,' ') else ISNULL(A.XLXSBJXHML_CPMLID,' ') end)  ");
            SQL.Add("       when ' ' then ' ' else  ");
            SQL.Add("   rtrim(case when ISNULL(A.XLXSBJXHML_CPMLID,' ') = ' ' then ISNULL(B.XLXSBJXHML_CPMLID,' ') else ISNULL(A.XLXSBJXHML_CPMLID,' ') end)  || '|'|| rtrim(XLXSCPML_CPMLMC) end as HasCPMLID, ");
            SQL.Add(" XLXSBJDMX_BJCP,' ' as XLWLXX,");
            SQL.Add(" ISNULL(XLXSBJDMX_WGCPBZ,'N') as XLXSBJDMX_WGCPBZ,XLXSBJDMX_JLDW,XLXSBJDMX_BJSL, ");
            if (!_sBZBJDIDFilter.Equals(""))
            {
                SQL.Add(" case when isnull(BZCPB.XLXHID,' ') = ' ' then 'N' else 'Y' end as CPCF,case when isnull(BZCPSLB.XLXHID,' ') = ' ' then 'N' else 'Y' end as CPSLCF,case when isnull(BZGGSLB.XLGGID,' ') = ' ' then 'N' else 'Y' end as GGSLCF ");
            }
            else
            {
                SQL.Add(" 'N' AS CPCF,'N' AS CPSLCF,'N' AS GGSLCF ");
            }
            SQL.Add(" from XLXSBJDMX ");
            SQL.Add(" left join XLBZ on XLBZ_XLBZID = XLXSBJDMX_XLBZID  ");
            SQL.Add(" left join XLYSB on XLYSB_XLYSID = XLXSBJDMX_XLYSID  ");
            SQL.Add(" left join XLXSBJDYZZ0 on XLXSBJDYZZ0_BJDID=XLXSBJDMX_BJDID and XLXSBJDYZZ0_XH=XLXSBJDMX_XH ");
            SQL.Add(" left join XLXSBJDJJYZ on XLXSBJDJJYZ_BJDID=XLXSBJDMX_BJDID and XLXSBJDJJYZ_XH=XLXSBJDMX_XH ");
            SQL.Add(" left join XLXSBJD on XLXSBJD_BJDID=XLXSBJDMX_BJDID");
            SQL.Add(" left join XLXSJGB on XLXSJGB_JGBID=XLXSBJD_JGBID");
            SQL.Add(" left join XLXSCLQD on XLXSCLQD_XLXHID=XLXSBJDMX_XLXHID and XLXSCLQD_XLGGID=XLXSBJDMX_XLGGID and XLXSCLQD_XLBZID=XLXSBJDMX_XLBZID and XLXSCLQD_DYDJID=isnull(XLXSBJDMX_DYDJID,' ')");
            SQL.Add(" left join XLXSBJXHML A on A.XLXSBJXHML_XLXHID = XLXSBJDMX_XLXHID and A.XLXSBJXHML_DYDJID = isnull(XLXSBJDMX_DYDJID, ' ')  ");
            SQL.Add(" left join XLXSBJXHML B on B.XLXSBJXHML_XLXHID = XLXSBJDMX_XLXHID and B.XLXSBJXHML_DYDJID = ' '   ");
            SQL.Add(" left join XLXSCPML on XLXSCPML_CPMLID = case when ISNULL(A.XLXSBJXHML_CPMLID,' ') = ' ' then ISNULL(B.XLXSBJXHML_CPMLID,' ') else ISNULL(A.XLXSBJXHML_CPMLID,' ') end  ");
            SQL.Add(" left join XLXSBJDMXFD on XLXSBJDMXFD_BJDID = XLXSBJDMX_BJDID and XLXSBJDMXFD_BJDXH = XLXSBJDMX_XH ");
            if (!_sBZBJDIDFilter.Equals(""))
            {
                string sBZYJ = "XLXSBJDMX_XLBZID";
                SQL.Add(" left join (select XLXHID,XLGGID,DYDJID,XLBZID  ");
                SQL.Add("      from ");
                SQL.Add(" (select XLXSBJDMX_XLXHID as XLXHID,XLXSBJDMX_XLGGID as XLGGID,XLXSBJDMX_DYDJID as DYDJID,  ");
                if (sCPCFXLBZ.Trim().Equals(""))
                    SQL.Add(" XLXSBJDMX_XLBZID as XLBZID ");
                else
                {
                    sBZYJ = "          case XLXSBJDMX_XLBZID ";
                    for (int i = 0; i < sCPCFXLBZ.Split(',').Length; i++)
                    {
                        sBZYJ += string.Format(" when '{0}' then '{1}'  ", sCPCFXLBZ.Split(',')[i], sCPCFXLBZ.Split(',')[0]);
                    }
                    sBZYJ += " else XLXSBJDMX_XLBZID end  ";
                    SQL.Add(sBZYJ);
                    SQL.Add(" as XLBZID ");
                }
                SQL.Add("              from XLXSBJDMX where XLXSBJDMX_BJDID = :pBZBJDID )tmpA group by XLXHID,XLGGID,XLBZID,DYDJID ) BZCPB ");
                SQL.Add("          on XLXSBJDMX_XLXHID = BZCPB.XLXHID and XLXSBJDMX_XLGGID = BZCPB.XLGGID and (" + sBZYJ + ") = BZCPB.XLBZID and XLXSBJDMX_DYDJID = BZCPB.DYDJID ");
                SQL.Add(" left join (select XLXHID,XLGGID,DYDJID,XLBZID,BJSL,JLDW  ");
                SQL.Add("      from ");
                SQL.Add("    (select XLXSBJDMX_XLXHID as XLXHID,XLXSBJDMX_XLGGID as XLGGID," + sBZYJ + " as XLBZID,XLXSBJDMX_DYDJID as DYDJID, XLXSBJDMX_BJSL as BJSL, XLXSBJDMX_JLDW AS JLDW  ");
                SQL.Add("              from XLXSBJDMX where XLXSBJDMX_BJDID = :pBZBJDID) tmpB group by XLXHID,XLGGID,DYDJID,XLBZID,BJSL,JLDW ) BZCPSLB ");
                SQL.Add("          on XLXSBJDMX_XLXHID = BZCPSLB.XLXHID and XLXSBJDMX_XLGGID = BZCPSLB.XLGGID and (" + sBZYJ + ") = BZCPSLB.XLBZID ");
                SQL.Add("          and XLXSBJDMX_DYDJID = BZCPSLB.DYDJID and XLXSBJDMX_BJSL = BZCPSLB.BJSL and XLXSBJDMX_JLDW = BZCPSLB.JLDW ");
                SQL.Add(" left join (select XLGGID,BJSL,JLDW  ");
                SQL.Add("      from ");
                SQL.Add("    (select XLXSBJDMX_XLGGID as XLGGID, XLXSBJDMX_BJSL as BJSL, XLXSBJDMX_JLDW AS JLDW  ");
                SQL.Add("              from XLXSBJDMX where XLXSBJDMX_BJDID = :pBZBJDID) tmpC group by XLGGID,BJSL,JLDW ) BZGGSLB ");
                SQL.Add("          on XLXSBJDMX_XLGGID = BZGGSLB.XLGGID and XLXSBJDMX_BJSL = BZGGSLB.BJSL and XLXSBJDMX_JLDW = BZGGSLB.JLDW ");
                SQL.ParamByName("pBZBJDID").Value = _sBZBJDIDFilter.Trim();
            }
            SQL.Add(" where XLXSBJDMX_BJDID = :XLXSBJDMX_BJDID ");

            SQL.Add(" ) BJD");
            SQL.Add(" where 1=1");
            SQL.Add(" order by RNum ");
            SQL.AddParam("XLXSBJDMX_BJDID", TGtdbType.VarChar, 20, _BJDID);
            return SQL;
        }
        public override bool Querying(TSQL sql)
        {
            Entites.Columns.Clear();
            Entites.Clear();
            return base.Querying(sql);
        }
    }

    internal static class ZDFXPZHelper
    {
        public const int ZDFX_MDID = 100860068;

        public static string GetPZColumn(int iMDID, string sColumn)
        {
            if (string.IsNullOrEmpty(sColumn) || !sColumn.StartsWith("XLXSBJPZ_"))
                return string.Empty;
            try
            {
                XLXSBJPZCache pzxxCache = new XLXSBJPZCache();
                if (pzxxCache.CacheEnabled)
                {
                    pzxxCache = (XLXSBJPZCache)pzxxCache.GetData(null);
                    if (pzxxCache.dvXLXSBJPZBy_MDID != null)
                    {
                        foreach (DataRowView row in pzxxCache.dvXLXSBJPZBy_MDID.FindRows(iMDID))
                        {
                            if (row.Row.Table.Columns.Contains(sColumn))
                                return row[sColumn].ToString().Trim();
                        }
                    }
                }
                TDatabase db = TContextDatabases.GetDatabase("DBERP");
                TSQL SQL = new TSQL(db);
                SQL.Add(string.Format("select isnull({0},'') as PZVAL from XLXSBJPZ where XLXSBJPZ_MDID=:pMDID", sColumn));
                SQL.ParamByName("pMDID").Value = iMDID;
                DataSet ds = db.OpenDataSet(SQL);
                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    return ds.Tables[0].Rows[0]["PZVAL"].ToString().Trim();
            }
            catch
            {
            }
            return string.Empty;
        }

        public static bool IsGGSLCFEnabled()
        {
            return GetPZColumn(ZDFX_MDID, "XLXSBJPZ_GGSLCF") == "Y";
        }

        public static double GetGGSLCFGS()
        {
            return CMISproConvert.StrToFloat(GetPZColumn(ZDFX_MDID, "XLXSBJPZ_GGSLCFGS"), 0);
        }

        public static double GetGGSLCFBL()
        {
            return CMISproConvert.StrToFloat(GetPZColumn(ZDFX_MDID, "XLXSBJPZ_GGSLCFBL"), 0);
        }
    }
}