/// <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));
}
/// <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);
}
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);
}
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);
}
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);
}
/// <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);
}
}
/// <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);
}
/// <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);
}
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);
}