示例#1
0
        /// <summary>
        /// 获得报表Sql
        /// </summary>
        /// <param name="rp_code">报表编号</param>
        /// <param name="queryParams">查询参数</param>
        /// <returns></returns>
        public static Sql GetReportSql(int rp_code, object queryParams)
        {
            string sql = string.Empty;

            BL_Reports bllReports = new BL_Reports();

            sql = bllReports.GetReprot(rp_code).RP_Query;
            sql = ReportReplace.ReportParamReplace(sql);
            string[] sqls = sql.Split(new string[] { "|-|" }, StringSplitOptions.None);
            if (sqls.Length == 1)
            {
                sql = sqls[0];
            }
            else if (sqls.Length == 2)
            {
                sql = sqls[1];

                var db      = CRMTreeDatabase.DBCRMTree.GetInstance();
                var procSql = sqls[0];
                var pSql    = SetReportSql(procSql, queryParams);
                var tags    = db.Query <CRMTreeDatabase.EX_Tag>(pSql);
                sql = sql.Replace("{{", "@").Replace("}}", "");
                foreach (var tag in tags)
                {
                    sql = sql.Replace(tag.PL_Tag, tag.PV_Val);
                }
            }

            return(SetReportSql(sql, queryParams));
        }
示例#2
0
        /// <summary>
        /// 获取某一报表配置好完整SQL,[用动态参数配置报表sql]
        /// </summary>
        /// <param name="rp_code">报表Code</param>
        /// <param name="_params">动态参数</param>
        /// <returns></returns>
        public string SqlParamReplace(int rp_code, IList <_param> _params)
        {
            CT_Reports _c_report = _d_report.GetReprot(rp_code);

            if (_c_report == null)
            {
                return(string.Empty);
            }

            string sql = ReportReplace.ReportParamReplace(_c_report.RP_Query);

            string[] sqls = sql.Split(new string[] { "|-|" }, StringSplitOptions.None);
            if (sqls.Length == 1)
            {
                sql = sqls[0];
            }
            if (_params != null)
            {
                foreach (_param _p in _params)
                {
                    string _pp = string.Format("@{0}", _p.Kay.Trim());
                    sql = sql.Replace(_pp, _p.Value);
                }
            }
            return(sql);
        }
示例#3
0
        public DataTable getMultiData(int RP_Code, int PR = 0)
        {
            CT_Reports _r = _B_rp.GetReprot_Chat(ChartType.Multi, RP_Code);

            if (_r == null)
            {
                return(null);
            }
            DataTable dt = BL_Reports.GetReportSqlWithTemplate(ReportReplace.ReportParamReplace(_r.RP_Query), PR);

            return(dt);
        }
示例#4
0
        public DataTable getSchData(int RP_Code, int PR = 0)
        {
            CT_Reports _r = _B_rp.GetReprot_Chat(ChartType.Sch, RP_Code);

            if (_r == null)
            {
                return(null);
            }
            DataTable dt = BL_Reports.GetReportSqlWithTemplate(ReportReplace.ReportParamReplace(_r.RP_Query), PR);

//            DataTable dt = DAL.DL_Reports.QueryExecution(ReportReplace.ReportParamReplace(_r.RP_Query));
            return(dt);
        }
示例#5
0
        public DataTable getPieData(int FL_FB_Code, out int RP_Code, int PR = 0)
        {
            CT_Reports _r = _B_rp.GetReprot_Chat(ChartType.Pie, FL_FB_Code);

            if (_r == null)
            {
                RP_Code = -1;
                return(null);
            }
            //DataTable dt = DL_Reports.QueryExecution(ReportReplace.ReportParamReplace(_r.RP_Query));
            DataTable dt = BL_Reports.GetReportSqlWithTemplate(ReportReplace.ReportParamReplace(_r.RP_Query), PR);

            RP_Code = _r.RP_Code;
            return(dt);
        }
示例#6
0
        /// <summary>
        /// 获取报表,以及报表相关的参数值,单一的参数列表获取,只有默认值参数
        /// </summary>
        /// <param name="RP_Code"></param>
        /// <returns></returns>
        public CT_Reports GetReplaceReport(int RP_Code)
        {
            MD_ReportList Report = _D_re.GetReportValue(RP_Code);

            if (Report != null && Report.CT_Reports_List != null && Report.CT_Reports_List.Count > 0)
            {
                Report = BL_ReportReplace.ReportParamReplace(Report);
                Report.CT_Reports_List[0].RP_Query = ReportReplace.ReportParamReplace(Report.CT_Reports_List[0].RP_Query);
                return(Report.CT_Reports_List[0]);
            }
            else
            {
                CT_Reports o = _D_re.GetReprot(RP_Code);
                o.RP_Query = ReportReplace.ReportParamReplace(o.RP_Query);
                return(o);
            }
        }
示例#7
0
        /// <summary>
        /// 获取替换成功后的Report,
        /// Name Desc 都做了替换 可以单独使用,也可供其他方式调用
        /// </summary>
        /// <param name="Intern"></param>
        /// <param name="Param_value"></param>
        /// <param name="Mode"></param>
        /// <param name="Paramterslist"></param>
        /// <returns></returns>
        public CT_Reports GetReplaceReport(bool Intern, CT_Param_Value Param_value, EM_ParameterMode Mode, string Paramterslist)
        {
            MD_ReportList MD_List = GetReportValue(Param_value);

            if (MD_List == null || MD_List.CT_Reports_List == null)
            {
                return(null);
            }
            if (!Intern)
            {
                MD_List = ReportReplace.NameReplace(MD_List);
            }
            MD_List = BL_ReportReplace.ReportParamReplace(MD_List, Mode, Paramterslist, Intern);
            CT_Reports Report = null;

            if (MD_List != null || MD_List.CT_Reports_List[0] != null)
            {
                Report          = MD_List.CT_Reports_List[0];
                Report.RP_Query = ReportReplace.ReportParamReplace(Report.RP_Query);
            }
            return(Report);
        }
示例#8
0
        /// <summary>
        /// EX_DataType = "int", EX_Name = "EX_Type", EX_Value = "1"   1=.txt 2=.html
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="ex_params"></param>
        /// <returns></returns>
        public static string GetRunFileContent(string fileContent, List <CRMTreeDatabase.EX_Param> ex_params = null, int EX_Type = 1)
        {
            Regex           reg = new Regex(@"(?<={{)[^{{}}]+(?=}})");
            MatchCollection mcs = reg.Matches(fileContent);

            if (mcs.Count == 0)
            {
                return(fileContent);
            }

            List <int> listCodes = new List <int>();

            foreach (Match mc in mcs)
            {
                listCodes.Add(Convert.ToInt32(mc.Value));
            }

            var codes = string.Join(",", listCodes.Distinct());
            var db    = CRMTreeDatabase.DBCRMTree.GetInstance();
            var tags  = db.Query <CRMTreeDatabase.CT_Camp_Tag>(@"SELECT CT_Code
            ,CT_Type
            ,CT_FieldName
            ,CT_Category
            ,CT_Desc_EN
            ,CT_Desc_CN
            FROM CT_Camp_Tags
            WHERE CT_Code IN (SELECT * FROM dbo.f_split(@0,','))", codes);

            //distinct CT_Category
            var category = (from tag in tags select tag.CT_Category).Distinct().ToList();

            //replace by CT_Category
            foreach (var c in category)
            {
                var sql = string.Empty;
                switch (c)
                {
                case 1:
                    sql = "Select {0} from CT_Auto_Dealers AD where AD_Code = @AD";
                    break;

                case 2:
                    sql = "Select {0} from CT_Dealer_Groups DG where DG_Code=@DG";
                    break;

                case 3:
                    sql = "Select {0} from CT_OEM OM where OM_Code=@OM";
                    break;

                case 4:
                    sql = "Select {0} from CT_CRMTree CT where CT_Code=@CT";
                    break;

                case 5:
                    sql = "Select {0} from CT_All_Users AU where AU_Code=@AU";
                    break;

                case 6:
                    sql = "Select {0} from CT_Car_Inventory CI where CI_Code=@CI";
                    break;

                case 7:
                    sql = "Select {0} from CT_Campaigns CG where CG_Code=@CG";
                    break;

                case 9:
                    sql = "Select {0} from CT_Dealer_Empl DE where DE_Code=@DE";
                    break;

                case 10:
                    sql = "Select {0} from CT_Appt_Service AS where AS_Code=@AS";
                    break;

                case 11:
                    sql = "Select {0} from CT_Purch_Appt PA where PA_Code=@PA";
                    break;

                case 100:
                    sql = "Select {0}";
                    break;
                }
                try
                {
                    if (!string.IsNullOrWhiteSpace(sql))
                    {
                        var fieldNames = (from tag in tags where tag.CT_Category == c orderby tag.CT_Code select tag.CT_FieldName).ToList();
                        var fieldCodes = (from tag in tags where tag.CT_Category == c orderby tag.CT_Code select tag.CT_Code).ToList();
                        var cols       = string.Join(",", fieldNames);
                        sql = string.Format(sql, cols);

                        sql = ReportReplace.ReportParamReplace(sql);

                        var dt   = new DataTable();
                        var pSql = SetReportSql(sql, ex_params);
                        db.Fill(dt, pSql.SQL, pSql.Arguments);
                        if (dt.Rows.Count > 0)
                        {
                            var rowData = dt.Rows[0];
                            for (int i = 0, len = fieldCodes.Count; i < len; i++)
                            {
                                object value = rowData[i];
                                string val   = "";
                                if (null != value)
                                {
                                    val = value.ToString();
                                }
                                fileContent = fileContent.Replace("{{" + fieldCodes[i].ToString() + "}}", val);
                            }
                        }
                    }
                }
                catch {  }
            }

            return(fileContent);
        }
示例#9
0
        public static string GetFileContent(string fileName, params object[] fileParams)
        {
            string fileContent = string.Empty;
            string path        = string.Empty;

            //path = "";
            path = System.Configuration.ConfigurationManager.AppSettings["_PLUpload_File_Path"];
            if (string.IsNullOrWhiteSpace(path))
            {
                //path = @"D:\Projects\CRMTREE\CRMTREE\CRMTREE.WEB\WebSite\plupload\file\";
                path = "~/plupload/";
            }
            path = HttpContext.Current.Server.MapPath(path) + "file/";

            fileContent = ShInfoTech.Common.Files.FileContext(path, fileName);

            Regex           reg = new Regex(@"(?<={{)[^{{}}]+(?=}})");
            MatchCollection mcs = reg.Matches(fileContent);

            if (mcs.Count == 0)
            {
                return(fileContent);
            }

            List <int> listCodes = new List <int>();

            foreach (Match mc in mcs)
            {
                listCodes.Add(Convert.ToInt32(mc.Value));
            }

            var codes = string.Join(",", listCodes.Distinct());
            var db    = CRMTreeDatabase.DBCRMTree.GetInstance();
            var tags  = db.Query <CRMTreeDatabase.CT_Camp_Tag>(@"SELECT CT_Code
            ,CT_Type
            ,CT_FieldName
            ,CT_Category
            ,CT_Desc_EN
            ,CT_Desc_CN
            FROM CT_Camp_Tags
            WHERE CT_Code IN (SELECT * FROM dbo.f_split(@0,','))", codes);

            //distinct CT_Category
            var category = (from tag in tags select tag.CT_Category).Distinct().ToList();

            //replace by CT_Category
            foreach (var c in category)
            {
                var sql = string.Empty;
                switch (c)
                {
                case 1:
                    sql = "Select {0} from CT_Auto_Dealers AD where AD_Code = @AD";
                    break;

                case 2:
                    sql = "Select {0} from CT_Dealer_Groups DG where DG_Code=@DG";
                    break;

                case 3:
                    sql = "Select {0} from CT_OEM OM where OM_Code=@OM";
                    break;

                case 4:
                    sql = "Select {0} from CT_CRMTree CT where CT_Code=@CT";
                    break;

                case 5:
                    sql = "Select {0} from CT_All_Users AU where AU_Code=@AU";
                    break;

                case 6:
                    sql = "Select {0} from CT_Car_Inventory CI where CI_Code=@CI";
                    break;

                case 7:
                    sql = "Select {0} from CT_Campaigns CG where CG_Code=@CG";
                    break;

                case 9:
                    sql = "Select {0} from CT_Dealer_Empl DE where DE_Code=@DE";
                    break;

                case 10:
                    sql = "Select {0} from CT_Appt_Service AS where AS_Code=@AS";
                    break;

                case 11:
                    sql = "Select {0} from CT_Purch_Appt PA where PA_Code=@PA";
                    break;

                case 100:
                    sql = "Select {0}";
                    break;
                }

                try
                {
                    if (!string.IsNullOrWhiteSpace(sql))
                    {
                        var fieldNames = (from tag in tags where tag.CT_Category == c orderby tag.CT_Code select tag.CT_FieldName).ToList();
                        var fieldCodes = (from tag in tags where tag.CT_Category == c orderby tag.CT_Code select tag.CT_Code).ToList();
                        var cols       = string.Join(",", fieldNames);
                        sql = string.Format(sql, cols);

                        sql = ReportReplace.ReportParamReplace(sql);

                        var dt = new DataTable();
                        db.Fill(dt, sql, fileParams);
                        if (dt.Rows.Count > 0)
                        {
                            var rowData = dt.Rows[0];
                            for (int i = 0, len = fieldCodes.Count; i < len; i++)
                            {
                                object value = rowData[i];
                                string val   = "";
                                if (null != value)
                                {
                                    val = value.ToString();
                                }
                                fileContent = fileContent.Replace("{{" + fieldCodes[i].ToString() + "}}", val);
                            }
                        }
                    }
                }
                catch (Exception)
                {
                }
            }

            return(fileContent);
        }