/// <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));
}
public string[] GetRunAu_Code(int CG_Code, int UType, int AD_Code)
{
CT_Campaigns _c = GetCampaign(CG_Code);
CT_Param_Value _p = new CT_Param_Value()
{
RP_Code = _c.CG_RP_Code,
PV_Type = 1,
PV_CG_Code = CG_Code,
PV_UType = UType,
PV_AD_OM_Code = AD_Code
};
BL_Reports _bl_report = new BL_Reports();
CT_Reports _Report = _bl_report.GetReplaceReport(_p);
List <CRMTreeDatabase.EX_Param> eps = new List <CRMTreeDatabase.EX_Param>();
CRMTreeDatabase.EX_Param ep = new CRMTreeDatabase.EX_Param();
ep.EX_Name = "PR";
ep.EX_Value = "0";
ep.EX_DataType = "int";
eps.Add(ep);
_Report.RP_Query = BL_Reports.GetReportSql(_Report.RP_Code, eps).SQL;
string[] _au_code = _bl_report.GetUserCode(_Report.RP_Query, "AU_Code").Distinct().ToArray();
return(_au_code);
}
public DataTable getEx_Data(int Pl_Code, IList <CRMTreeDatabase.EX_Param> eps)
{
try
{
CT_Reports Report = RP.GetReprot_Chat(ChartType.List, Pl_Code);
var db = CRMTreeDatabase.DBCRMTree.GetInstance();
PetaPoco.Sql _s = BL_Reports.GetReportSql(Report.RP_Code, eps);
var qData = new object();
if (_s.SQL.IndexOf(";Exec", StringComparison.OrdinalIgnoreCase) >= 0)
{
qData = db.Query <dynamic>(_s);
}
else
{
var pSql = PetaPoco.Sql.Builder;
pSql.Append("select * from(" + _s.SQL, _s.Arguments);
pSql.Append(") as dt");
qData = db.Query <dynamic>(pSql);
}
//Report.RP_Query = _s.SQL;
//DataTable dt = BL_Reports.GetReportSqlWithTemplate(Report.RP_Query);
//return dt;
string _j_d = JsonConvert.SerializeObject(qData);
DataTable _d = ToDataTable(_j_d);
return(_d);
}
catch
{
return(null);
}
}
/// <summary>
/// 生成活动报表
/// </summary>
/// <param name="CG_Code">活动ID</param>
/// <param name="AU_Type">登陆用户类型</param>
/// <param name="AU_AD_OM_Code">登陆用户所在单位ID</param>
/// <returns></returns>
public int EventRun(int EV_Code, int AU_Type, int AU_AD_OM_Code)
{
BL_Reports R = new BL_Reports();
return(R.ReportRun(new ReportRunParam()
{
pType = 2, CG_EV_Code = EV_Code, UType = AU_Type, AU_AD_OM_Code = AU_AD_OM_Code
}));
}
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);
}
/// <summary>
/// 生成活动报表
/// </summary>
/// <param name="CG_Code">活动ID</param>
/// <param name="AU_Type">登陆用户类型</param>
/// <param name="AU_AD_OM_Code">登陆用户所在单位ID</param>
/// <returns></returns>
public int CampaignRun(int CG_Code, int AU_Type, int AU_AD_OM_Code)
{
BL_Reports R = new BL_Reports();
int err = R.ReportRun(new ReportRunParam()
{
pType = 1, CG_EV_Code = CG_Code, UType = AU_Type, AU_AD_OM_Code = AU_AD_OM_Code
});
if (err >= 0)
{
BL_Wechat _w = new BL_Wechat();
_w.SendWechat_news(CG_Code, "", new string[] { "1" });
}
return(err);
}
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="Pl_Code"></param>
/// <param name="PR"></param>
/// <returns></returns>
private DataTable getEx_Data(int Pl_Code, int PR)
{
CT_Reports Report = RP.GetReprot_Chat(ChartType.List, Pl_Code);
List <CRMTreeDatabase.EX_Param> eps = new List <CRMTreeDatabase.EX_Param>();
CRMTreeDatabase.EX_Param ep = new CRMTreeDatabase.EX_Param();
ep.EX_Name = "PR";
ep.EX_Value = PR.ToString();
ep.EX_DataType = "int";
eps.Add(ep);
Report.RP_Query = BL_Reports.GetReportSql(Report.RP_Code, eps).SQL;
DataTable dt = BL_Reports.GetReportSqlWithTemplate(Report.RP_Query);
return(dt);
}
public DataTable getfile_Rep_value(int CG_Code, int AU_Type, int AU_AD_OM_Code)
{
CT_Campaigns _o = GetCampaign(CG_Code);
CT_Param_Value _p = new CT_Param_Value()
{
RP_Code = _o.CG_RP_Code,
PV_Type = 1,
PV_CG_Code = _o.CG_Code,
PV_UType = AU_Type,
PV_AD_OM_Code = AU_AD_OM_Code
};
BL_Reports R = new BL_Reports();
CT_Reports _r = R.GetReplaceReport(_p);
DataTable dt_Query = BL_Reports.QueryExecution(_r.RP_Query);
return(dt_Query);
}
public string getCampaignList(bool Interna, MD_UserEntity UserSession, string primarykey, string fields, string ordefiled, string orderway, int currentpage, int pagesize, string orderClass, int CT, int CA)
{
string Title = Interna ? IN_Language.CGL_Title(EM_Language.en_us) : IN_Language.CGL_Title(EM_Language.zh_cn);
string Description = Interna ? IN_Language.CGL_Description(EM_Language.en_us) : IN_Language.CGL_Description(EM_Language.zh_cn);
string Whom = Interna ? IN_Language.CGL_Whom(EM_Language.en_us) : IN_Language.CGL_Whom(EM_Language.zh_cn);
string Active = Interna ? IN_Language.CGL_Active(EM_Language.en_us) : IN_Language.CGL_Active(EM_Language.zh_cn);
string Status = Interna ? IN_Language.CGL_Status(EM_Language.en_us) : IN_Language.CGL_Status(EM_Language.zh_cn);
string Update_dt = Interna ? IN_Language.CGL_Update_dt(EM_Language.en_us) : IN_Language.CGL_Update_dt(EM_Language.zh_cn);
string Operate = Interna ? IN_Language.CGL_Operate(EM_Language.en_us) : IN_Language.CGL_Operate(EM_Language.zh_cn);
StringBuilder sbList = new StringBuilder();
sbList.Append("<table class=\"t-bd\" Style=\"width:796px\"><tr>");
sbList.Append("<th style=\"cursor:pointer;width:120px\" ");
sbList.Append("class=\"w40\" onclick=\"changeOrder(this,'CG_Title')\">" + Title + "<span class=\"taxis\" title=\"Sort by the column \"></span></th>");
sbList.Append("<th Style=\"width:350px\">" + Description + "</th>");
//sbList.Append("<th>" + Whom + "</th>");
sbList.Append("<th Style=\"width:100px\" class=\"w40\" onclick=\"changeOrder(this,'CG_Status')\">" + Status + "<span class=\"taxis\" title=\"Sort by the column \"></span></th>");
sbList.Append("<th Style=\"width:120px\" style=\"cursor:pointer;\" ");
if (ordefiled.Equals("CG_Update_dt"))
{
sbList.Append("class=\"taxisCurrent\" onclick=\"changeOrder(this,'CG_Update_dt')\">" + Update_dt + "<span class=\"");
sbList.Append(orderClass);
sbList.Append("\" title=\"Sort by the column \"></span></th>");
}
else
{
sbList.Append(" onclick=\"changeOrder(this,'CG_Update_dt')\">" + Update_dt + "<span class=\"taxis\" title=\"Sort by the column \"></span></th>");
}
sbList.Append("<th Style=\"width:60px\" class=\"w100\">" + Operate + "</th>");
int pagecount = -1, rowscount = -1;
MD_CampaignList CampaignList = Cam.GetCampaignList(UserSession.User.UG_UType, UserSession.DealerEmpl.DE_AD_OM_Code, primarykey, fields, ordefiled,
orderway, currentpage, pagesize, CT, out pagecount, out rowscount, CA);
if (null != CampaignList && null != CampaignList.CampaignList)
{
//生成分页html
var strPager = new StringBuilder();
if (1 < pagecount)
{
strPager = Pager.JavascriptPagination(true, currentpage, pagecount, rowscount);
}
for (int i = 0; i < CampaignList.CampaignList.Count; i++)
{
CT_Campaigns Camp = CampaignList.CampaignList[i];
Camp.Interna = Interna;
string Name = string.Empty;
BL_Reports Report = new BL_Reports();
CT_Reports RE = Report.GetReplaceReport(Interna,
new CT_Param_Value()
{
RP_Code = Camp.CG_RP_Code, PV_Type = 1, PV_CG_Code = Camp.CG_Code, PV_UType = UserSession.User.UG_UType, PV_AD_OM_Code = UserSession.DealerEmpl.DE_AD_OM_Code
});
if (RE == null)
{
Name = "";
}
else
{
Name = RE.RP_Name_EN;
}
sbList.Append("<tr value='" + Camp.CG_Code + "'><td style=\"text-align:left\">");
sbList.Append(Camp.CG_Title.Length > 30 ? Camp.CG_Title.Substring(0, 30) : Camp.CG_Title);
sbList.Append("</td><td style=\"text-align:left\" title='" + Camp.CG_Desc + "'>");
sbList.Append("<div style=\"border-bottom:1px solid #c3c3c3\">");
sbList.Append(Camp.CG_Desc.Length > 60 ? Camp.CG_Desc.Substring(0, 60) : Camp.CG_Desc);
sbList.Append("</div><div>");
if (null != Name)
{
sbList.Append(Name.Length > 60 ? Name.Substring(0, 60) : Name);
}
sbList.Append("</div>");
sbList.Append("</td>");
sbList.Append("<td >" + Camp.CG_S + "</td>");
//sbList.Append("</td><td >");
//sbList.Append(Camp.SS + "</td>");
sbList.Append("<td >");
var CG_Update_dt = Camp.CG_Update_dt.ToString("MM/dd/yyyy HH:mm:ss");
CG_Update_dt = CG_Update_dt == "01/01/0001 00:00:00" ? "" : CG_Update_dt;
sbList.Append(CG_Update_dt);
sbList.Append("</td><td style=\"text-align:left\">");
sbList.Append("<a href=\"/manage/Campaign/Campaign.aspx?CA=" + Camp.CG_Cat + "&CG_Code=");
sbList.Append(Camp.CG_Code);
sbList.Append("\"><i class=\"btnModify\" title=\"edit\"></i></a>");
sbList.Append("<a href=\"javascript:;\"><i class=\"btnDelete\" title=\"Delete\" onclick=\"Delete(event,");
sbList.Append(Camp.CG_Code);
sbList.Append(",'");
sbList.Append(Camp.CG_Filename);
sbList.Append("')\"></i></a>");
if (Camp.CG_Status == 10 && DateTime.Now < Camp.CG_End_Dt && Camp.CG_Start_Dt < DateTime.Now)
{
sbList.Append("<a href=\"javascript:;\"><i class=\"btnRun\" title=\"Run\" onclick=\"Run(event,");
sbList.Append(Camp.CG_Code);
sbList.Append(",");
sbList.Append(Camp.CG_RP_Code);
sbList.Append(")\"></i></a>");
}
sbList.Append("</td></tr>");
}
sbList.Append("</table>");
sbList.Append(strPager);
}
else
{
sbList.Append("<tr ><td colspan=\"10\"><div class=\"noData\">No matching data！</div></td></tr>");
sbList.Append("</tbody>");
sbList.Append("</table>");
}
return(sbList.ToString());
}
public static int updateTags(string PL_Codes, string Paramters)
{
CRMTree.BLL.BL_Reports rp = new CRMTree.BLL.BL_Reports();
return(rp.insertTags(AU_Type, AU_AD_OM_Code, PL_Codes, Paramters));
}
/// <summary>
/// 获取Event列表
/// </summary>
/// <param name="EV_UType"></param>
/// <param name="EV_AD_OM_Code"></param>
/// <param name="primarykey"></param>
/// <param name="fields"></param>
/// <param name="ordefiled"></param>
/// <param name="orderway"></param>
/// <param name="currentpage"></param>
/// <param name="pagesize"></param>
/// <param name="orderClass"></param>
/// <returns></returns>
public string getEventList(bool Interna, MD_UserEntity UserSession, string primarykey, string fields, string ordefiled,
string orderway, int currentpage, int pagesize, string orderClass)
{
string Title = Interna ? IN_Language.CGL_Title(EM_Language.en_us) : IN_Language.CGL_Title(EM_Language.zh_cn);
string Description = Interna ? IN_Language.CGL_Description(EM_Language.en_us) : IN_Language.CGL_Description(EM_Language.zh_cn);
string Whom = Interna ? IN_Language.CGL_Whom(EM_Language.en_us) : IN_Language.CGL_Whom(EM_Language.zh_cn);
string Active = Interna ? IN_Language.CGL_Active(EM_Language.en_us) : IN_Language.CGL_Active(EM_Language.zh_cn);
string Update_dt = Interna ? IN_Language.CGL_Update_dt(EM_Language.en_us) : IN_Language.CGL_Update_dt(EM_Language.zh_cn);
string Operate = Interna ? IN_Language.CGL_Operate(EM_Language.en_us) : IN_Language.CGL_Operate(EM_Language.zh_cn);
StringBuilder sbList = new StringBuilder();
sbList.Append("<table class=\"t-bd\" Style=\"width:930px\"><tr>");
sbList.Append("<th style=\"cursor:pointer;width:150px;\" ");
sbList.Append("class=\"w40\" onclick=\"changeOrder(this,'EV_Title')\">" + Title + "<span class=\"taxis\" title=\"Sort by the column \"></span></th>");
sbList.Append("<th Style=\"width:400px\">" + Description + "</th>");
sbList.Append("<th>" + Whom + "</th>");
sbList.Append("<th>" + Active + "</th>");
sbList.Append("<th style=\"cursor:pointer;\" ");
if (ordefiled.Equals("EV_Update_dt"))
{
sbList.Append("class=\"taxisCurrent\" onclick=\"changeOrder(this,'EV_Update_dt')\">" + Update_dt + "<span class=\"");
sbList.Append(orderClass);
sbList.Append("\" title=\"Sort by the column \"></span></th>");
}
else
{
sbList.Append(" onclick=\"changeOrder(this,'EV_Update_dt')\">" + Update_dt + "<span class=\"taxis\" title=\"Sort by the column \"></span></th>");
}
sbList.Append("<th class=\"w100\">" + Operate + "</th>");
int pagecount = -1, rowscount = -1;
MD_EventList EventList = Event.getEventList(UserSession.User.UG_UType, UserSession.DealerEmpl.DE_AD_OM_Code, primarykey, fields, ordefiled,
orderway, currentpage, pagesize, out pagecount, out rowscount);
if (null != EventList && EventList.EventList.Count > 0)
{
//生成分页html
var strPager = new StringBuilder();
if (1 < pagecount)
{
strPager = Pager.JavascriptPagination(true, currentpage, pagecount, rowscount);
}
for (int i = 0; i < EventList.EventList.Count; i++)
{
CT_Events Ev = EventList.EventList[i];
Ev.Interna = Interna;
string Name = string.Empty;
BL_Reports Report = new BL_Reports();
CT_Reports RE = Report.GetReplaceReport(Interna,
new CT_Param_Value()
{
RP_Code = Ev.EV_RP_Code, PV_Type = 2, PV_CG_Code = Ev.EV_Code, PV_UType = UserSession.User.UG_UType, PV_AD_OM_Code = UserSession.DealerEmpl.DE_AD_OM_Code
});
if (RE == null)
{
Name = "";
}
else
{
Name = RE.RP_Name_EN;
}
sbList.Append("<tr value='" + Ev.EV_Code + "'><td style=\"text-align:left\">");
sbList.Append(Ev.EV_Title.Length > 30 ? Ev.EV_Title.Substring(0, 30) : Ev.EV_Title);
sbList.Append("</td><td style=\"text-align:left\" title='" + Ev.EV_Desc + "'>");
sbList.Append("<div style=\"border-bottom:1px solid #c3c3c3\">");
sbList.Append(Ev.EV_Desc.Length > 60 ? Ev.EV_Desc.Substring(0, 60) : Ev.EV_Desc);
sbList.Append("</div><div>");
sbList.Append(Name.Length > 60 ? Name.Substring(0, 60) : Name);
sbList.Append("</div>");
sbList.Append("</td><td >");
sbList.Append(Ev.S);
sbList.Append("</td><td >");
sbList.Append(Ev.SS);
sbList.Append("</td><td >");
sbList.Append(Ev.EV_Update_dt.ToString("MM/dd/yyyy HH:mm:ss"));
sbList.Append("</td><td >");
sbList.Append("<a href=\"/manage/Event/Edit_Event.aspx?id=");
sbList.Append(Ev.EV_Code);
sbList.Append("\"><i class=\"btnModify\" title=\"edit\"></i></a>");
sbList.Append("<a href=\"javascript:;\"><i class=\"btnDelete\" title=\"Delete\" onclick=\"Delete(event,");
sbList.Append(Ev.EV_Code);
sbList.Append(",'");
sbList.Append(Ev.EV_Filename);
sbList.Append("')\"></i></a>");
sbList.Append("<a href=\"javascript:;\"><i class=\"btnRun\" title=\"Run\" onclick=\"Run(event,");
sbList.Append(Ev.EV_Code);
sbList.Append(",");
sbList.Append(Ev.EV_RP_Code);
sbList.Append(")\"></i></a>");
sbList.Append("</td></tr>");
}
sbList.Append("</table>");
sbList.Append(strPager);
}
else
{
sbList.Append("<tr ><td colspan=\"10\"><div class=\"noData\">No matching data！</div></td></tr>");
sbList.Append("</tbody>");
sbList.Append("</table>");
}
return(sbList.ToString());
}