Example #1
0
        public DataFilterExt(string YHID, string FAID, string DYID, string KZID, string SUFFIX = null)
        {
            this.dbserver_type = DB.GetDbtype();
            if (this.dbserver_type == DBSERVER_TYPE.ORACLE)
            {
                this.dual_sql = " from dual ";
            }
            else
            {
                this.dual_sql = " ";
            }
            this.FAID   = FAID;
            this.DYID   = DYID;
            this.YHID   = YHID;
            this.KZID   = KZID;
            this.SUFFIX = SUFFIX;
            string format = "SELECT XXMXID {0} FROM ({1}) A  GROUP BY XXMXID";

            System.Collections.Generic.IList <XT_SJQX_DX> list = DB.List <XT_SJQX_DX>(new
            {
                FAID            = FAID,
                ORDER_BY_CLAUSE = " PXXH ASC"
            }.toStrObjDict());
            string text = "";

            foreach (XT_SJQX_DX current in list)
            {
                string text2 = current.GXSQL;
                if (string.IsNullOrEmpty(text2))
                {
                    text2 = string.Concat(new object[]
                    {
                        "SELECT '$DXID$' AS DXID, '$YHID$' AS DXMXID,'$YHID$' AS YHID,",
                        current.PXXH,
                        " AS PXXH ",
                        this.dual_sql
                    });
                }
                else
                {
                    text2 = string.Concat(new object[]
                    {
                        "SELECT A.DXID,A.DXMXID,A.YHID,",
                        current.PXXH,
                        " AS PXXH FROM (",
                        text2,
                        ") A WHERE YHID = '$YHID$'"
                    });
                }
                text = (string.IsNullOrEmpty(text) ? "" : " UNION ") + DataFiltersAction.ChuliSql(text2, YHID, FAID, current.DXID);
            }
            System.Collections.Generic.IList <StrObjectDict> list2 = DB.ListSod("LIST_XT_SJQX_QX_BY_DY", new
            {
                FAID  = FAID,
                DYID  = DYID,
                KZID  = KZID,
                DXSQL = text
            }.toStrObjDict());
            if (list2.Count > 0)
            {
                string PXXH  = Utils.GetString(list2.First <StrObjectDict>()["PXXH"]);
                string text3 = string.Format("SELECT DISTINCT A.XXMXID, A.XXID, A.KCID FROM XT_SJQX_QXMX A ,({0}) B,XT_SJQX_DY_XX C WHERE  A.DXID=B.DXID AND A.DXMXID=B.DXMXID  AND A.FAID=C.FAID AND C.DYID=A.DYID AND C.XXID=A.XXID AND  A.FAID='{1}'AND A.DYID='{2}' AND A.KCID='{3}' AND B.PXXH='{4}' AND C.XXLX=2", new object[]
                {
                    text,
                    FAID,
                    DYID,
                    KZID,
                    PXXH
                });
                System.Collections.Generic.List <StrObjectDict> source = (
                    from item in list2
                    where Utils.GetString(item["PXXH"]) == PXXH
                    select item).ToList <StrObjectDict>();
                string text4 = "";
                if ((
                        from item in source
                        where Utils.GetString(item["XXLX"]) == "0"
                        select item).Count <StrObjectDict>() == 0)
                {
                    string text5 = text4;
                    text4 = string.Concat(new string[]
                    {
                        text5,
                        ",(case when sum(case when kcid = '",
                        KZID,
                        "' then 1 else 0 end )>0 then 1 else 0 end) AS ",
                        string.IsNullOrEmpty(SUFFIX) ? (FAID + "_" + DYID) : SUFFIX,
                        "_KZ_",
                        KZID
                    });
                    foreach (StrObjectDict current2 in
                             from item in source
                             where Utils.GetString(item["XXLX"]) == "1"
                             select item)
                    {
                        text3 += string.Format(" UNION SELECT A.ID AS XXMXID,'{0}' AS XXID,'{1}' AS KCID FROM ({2}) A ", Utils.GetString(current2["XXID"]), KZID, DataFiltersAction.ChuliSql(Utils.GetString(current2["ZSQL"]), YHID, FAID));
                    }
                    foreach (StrObjectDict current2 in
                             from item in source
                             where Utils.GetString(item["XXLX"]) == "3"
                             select item)
                    {
                        string arg  = DataFiltersAction.ChuliSql(Utils.GetString(current2["GLSQL"]), YHID, FAID);
                        string arg2 = string.Format("SELECT DISTINCT A.XXMXID, A.XXID, A.KCID FROM XT_SJQX_QXMX A ,({0}) B,XT_SJQX_DY_XX C WHERE  A.DXID=B.DXID AND A.DXMXID=B.DXMXID  AND A.FAID=C.FAID AND C.DYID=A.DYID AND C.XXID=A.XXID AND  A.FAID='{1}'AND A.DYID='{2}' AND A.KCID='{3}' AND A.XXID='{4}'", new object[]
                        {
                            text,
                            FAID,
                            DYID,
                            KZID,
                            Utils.GetString(current2["XXID"])
                        });
                        text3 += string.Format(" UNION SELECT A.XXMXID,B.XXID,B.KCID FROM ({0}) A INNER JOIN ({1}) B ON A.FLID = B.XXMXID", arg, arg2);
                    }
                }
                else
                {
                    StrObjectDict strObjDict = (
                        from item in source
                        where Utils.GetString(item["XXLX"]) == "0"
                        select item).ToList <StrObjectDict>()[0];
                    string text5 = text4;
                    text4 = string.Concat(new string[]
                    {
                        text5,
                        ",1 as ",
                        string.IsNullOrEmpty(SUFFIX) ? (FAID + "_" + DYID) : SUFFIX,
                        "_KZ_",
                        KZID
                    });
                    text3 += string.Format(" UNION SELECT A.ID AS XXMXID,'{0}' AS XXID,'{1}' AS KCID FROM ({2}) A ", Utils.GetString(strObjDict["XXID"]), KZID, DataFiltersAction.ChuliSql(Utils.GetString(strObjDict["ZSQL"]), YHID, FAID));
                }
                this.KZ_ZSQL = string.Format(format, text4, text3);
            }
            else
            {
                XT_SJQX_DY xT_SJQX_DY = DB.Load <XT_SJQX_DY, PK_XT_SJQX_DY>(new PK_XT_SJQX_DY
                {
                    FAID = FAID,
                    DYID = DYID
                });
                if (!string.IsNullOrEmpty(xT_SJQX_DY.MRZ))
                {
                    XT_SJQX_DY_XX xT_SJQX_DY_XX = DB.Load <XT_SJQX_DY_XX, PK_XT_SJQX_DY_XX>(new PK_XT_SJQX_DY_XX
                    {
                        FAID = FAID,
                        DYID = DYID,
                        XXID = xT_SJQX_DY.MRZ
                    });
                    if (xT_SJQX_DY_XX.XXLX == 0 || xT_SJQX_DY_XX.XXLX == 1)
                    {
                        this.KZ_ZSQL = string.Format(" SELECT A.ID AS XXMXID,1 AS " + (string.IsNullOrEmpty(SUFFIX) ? (FAID + "_" + DYID) : SUFFIX) + "_KZ_{0}  FROM ({1}) A ", KZID, DataFiltersAction.ChuliSql(Utils.GetString(xT_SJQX_DY_XX.ZSQL), YHID, FAID));
                        return;
                    }
                }
                if (string.IsNullOrEmpty(this.KZ_ZSQL))
                {
                    this.KZ_ZSQL = string.Format(" SELECT NULL AS XXMXID, 0 AS " + (string.IsNullOrEmpty(SUFFIX) ? (FAID + "_" + DYID) : SUFFIX) + "_KZ_{0} " + this.dual_sql, KZID);
                }
            }
        }
Example #2
0
        private void initKzs(XT_SJQX_DY dy, IList <StrObjectDict> kzs)
        {
            string             format = "SELECT XXMXID {0} FROM ({1}) A  GROUP BY XXMXID";
            IList <XT_SJQX_DX> list   = DB.List <XT_SJQX_DX>(new
            {
                FAID            = this.FAID,
                ORDER_BY_CLAUSE = " PXXH ASC"
            }.toStrObjDict());
            string text = "";

            foreach (XT_SJQX_DX current in list)
            {
                string text2 = current.GXSQL;
                if (string.IsNullOrEmpty(text2))
                {
                    text2 = string.Concat(new object[]
                    {
                        "SELECT '$DXID$' AS DXID, '$YHID$' AS DXMXID,'$YHID$' AS YHID,",
                        current.PXXH,
                        " AS PXXH ",
                        this.dual_sql
                    });
                }
                else
                {
                    text2 = string.Concat(new object[]
                    {
                        "SELECT A.DXID,A.DXMXID,A.YHID,",
                        current.PXXH,
                        " AS PXXH FROM (",
                        text2,
                        ") A WHERE YHID = '$YHID$'"
                    });
                }
                text = (string.IsNullOrEmpty(text) ? "" : " UNION ") + DataFiltersAction.ChuliSql(text2, this.YHID, this.FAID, current.DXID);
            }
            IList <StrObjectDict> list2 = DB.ListSod("LIST_XT_SJQX_QX_BY_DY", new
            {
                FAID  = this.FAID,
                DYID  = this.DYID,
                DXSQL = text
            }.toStrObjDict());

            if (list2.Count > 0)
            {
                string PXXH  = Utils.GetString(list2.First <StrObjectDict>()["PXXH"]);
                string text3 = string.Format("SELECT DISTINCT A.XXMXID, A.XXID, A.KCID FROM XT_SJQX_QXMX A ,({0}) B,XT_SJQX_DY_XX C WHERE  A.DXID=B.DXID AND A.DXMXID=B.DXMXID  AND A.FAID=C.FAID AND C.DYID=A.DYID AND C.XXID=A.XXID AND  A.FAID='{1}'AND A.DYID='{2}' AND B.PXXH='{3}' AND C.XXLX=2", new object[]
                {
                    text,
                    this.FAID,
                    this.DYID,
                    PXXH
                });
                List <StrObjectDict> source = (
                    from item in list2
                    where Utils.GetString(item["PXXH"]) == PXXH
                    select item).ToList <StrObjectDict>();
                string text4 = "";
                foreach (StrObjectDict kz in kzs)
                {
                    if ((
                            from item in source
                            where Utils.GetString(item["XXLX"]) == "0" && Utils.GetString(item["KCID"]) == Utils.GetString(kz["ID"])
                            select item).Count <StrObjectDict>() == 0)
                    {
                        string text5 = text4;
                        text4 = string.Concat(new string[]
                        {
                            text5,
                            ",(case when sum(case when kcid ='",
                            Utils.GetString(kz["ID"]),
                            "' then 1 else 0 end)>0 then 1 else 0 end) AS ",
                            string.IsNullOrEmpty(this.SUFFIX) ? (this.FAID + "_" + this.DYID) : this.SUFFIX,
                            "_KZ_",
                            Utils.GetString(kz["ID"])
                        });
                        foreach (StrObjectDict current2 in
                                 from item in source
                                 where Utils.GetString(item["XXLX"]) == "1" && Utils.GetString(item["KCID"]) == Utils.GetString(kz["ID"])
                                 select item)
                        {
                            text3 += string.Format(" UNION SELECT A.ID AS XXMXID,'{0}' AS XXID,'{1}' AS KCID FROM ({2}) A ", Utils.GetString(current2["XXID"]), Utils.GetString(kz["ID"]), DataFiltersAction.ChuliSql(Utils.GetString(current2["ZSQL"]), this.YHID, this.FAID));
                        }
                        foreach (StrObjectDict current2 in
                                 from item in source
                                 where Utils.GetString(item["XXLX"]) == "3" && Utils.GetString(item["KCID"]) == Utils.GetString(kz["ID"])
                                 select item)
                        {
                            string arg  = DataFiltersAction.ChuliSql(Utils.GetString(current2["GLSQL"]), this.YHID, this.FAID);
                            string arg2 = string.Format("SELECT DISTINCT A.XXMXID, A.XXID, A.KCID FROM XT_SJQX_QXMX A ,({0}) B,XT_SJQX_DY_XX C WHERE  A.DXID=B.DXID AND A.DXMXID=B.DXMXID  AND A.FAID=C.FAID AND C.DYID=A.DYID AND C.XXID=A.XXID AND  A.FAID='{1}'AND A.DYID='{2}' AND A.KCID='{3}' AND A.XXID='{4}'", new object[]
                            {
                                text,
                                this.FAID,
                                this.DYID,
                                Utils.GetString(kz["ID"]),
                                Utils.GetString(current2["XXID"])
                            });
                            text3 += string.Format(" UNION SELECT A.XXMXID,B.XXID,B.KCID FROM ({0}) A INNER JOIN ({1}) B ON A.FLID = B.XXMXID", arg, arg2);
                        }
                    }
                    else
                    {
                        StrObjectDict strObjDict = (
                            from item in source
                            where Utils.GetString(item["XXLX"]) == "0" && Utils.GetString(item["KCID"]) == Utils.GetString(kz["ID"])
                            select item).ToList <StrObjectDict>()[0];
                        string text5 = text4;
                        text4 = string.Concat(new string[]
                        {
                            text5,
                            ",1 as ",
                            string.IsNullOrEmpty(this.SUFFIX) ? (this.FAID + "_" + this.DYID) : this.SUFFIX,
                            "_KZ_",
                            Utils.GetString(kz["ID"])
                        });
                        text3 += string.Format(" UNION SELECT A.ID AS XXMXID,'{0}' AS XXID,'{1}' AS KCID FROM ({2}) A ", Utils.GetString(strObjDict["XXID"]), Utils.GetString(kz["ID"]), DataFiltersAction.ChuliSql(Utils.GetString(strObjDict["ZSQL"]), this.YHID, this.FAID));
                    }
                }
                this.KZ_ZSQL = string.Format(format, text4, text3);
            }
            else
            {
                if (!string.IsNullOrEmpty(dy.MRZ))
                {
                    XT_SJQX_DY_XX xT_SJQX_DY_XX = DB.Load <XT_SJQX_DY_XX, PK_XT_SJQX_DY_XX>(new PK_XT_SJQX_DY_XX
                    {
                        FAID = this.FAID,
                        DYID = this.DYID,
                        XXID = dy.MRZ
                    });
                    if (xT_SJQX_DY_XX.XXLX == 0 || xT_SJQX_DY_XX.XXLX == 1)
                    {
                        string text4 = "";
                        foreach (StrObjectDict current3 in kzs)
                        {
                            string text5 = text4;
                            text4 = string.Concat(new string[]
                            {
                                text5,
                                ",1 as ",
                                string.IsNullOrEmpty(this.SUFFIX) ? (this.FAID + "_" + this.DYID) : this.SUFFIX,
                                "_KZ_",
                                Utils.GetString(current3["ID"])
                            });
                        }
                        this.KZ_ZSQL = string.Format(" SELECT A.ID AS XXMXID,{0} FROM ({1}) A ", text4, DataFiltersAction.ChuliSql(Utils.GetString(xT_SJQX_DY_XX.ZSQL), this.YHID, this.FAID));
                        return;
                    }
                }
                if (string.IsNullOrEmpty(this.KZ_ZSQL))
                {
                    string text4 = "";
                    foreach (StrObjectDict current3 in kzs)
                    {
                        string text5 = text4;
                        text4 = string.Concat(new string[]
                        {
                            text5,
                            ",0 as ",
                            string.IsNullOrEmpty(this.SUFFIX) ? (this.FAID + "_" + this.DYID) : this.SUFFIX,
                            "_KZ_",
                            Utils.GetString(current3["ID"])
                        });
                    }
                    this.KZ_ZSQL = string.Format(" SELECT NULL AS XXMXID{0}  " + this.dual_sql, text4);
                }
            }
        }
Example #3
0
 public static string ChuliSql(string sql, string YHID, string FAID)
 {
     return(DataFiltersAction.ChuliSql(sql, YHID, FAID, null));
 }