/// <summary>
/// 生成数据集
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void ADDBISETLIST(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
int ID = Int32.Parse(P1);
DBFactory db = new BI_DB_SourceB().GetDB(ID);
string strTableName = P2;
string strDataSetName = context.Request("DsetName") ?? "1";
BI_DB_Set DS = new BI_DB_Set();
DS.Name = strDataSetName;
DS.SID = ID;
DS.SName = strTableName;
DS.CRDate = DateTime.Now;
DS.CRUser = UserInfo.User.UserName;
DS.Type = "SQL";
DS.DSQL = "SELECT * FROM " + strTableName;
new BI_DB_SetB().Insert(DS);
DataTable dt = db.GetDBClient().SqlQueryable <Object>(CommonHelp.Filter("SELECT * FROM " + strTableName)).ToDataTablePage(1, 1);
List <BI_DB_Dim> ListDIM = new BI_DB_SetB().getCType(dt);
ListDIM.ForEach(D => D.STID = DS.ID);
ListDIM.ForEach(D => D.CRDate = DateTime.Now);
ListDIM.ForEach(D => D.CRUser = UserInfo.User.UserName);
new BI_DB_DimB().Insert(ListDIM);
}
//获取数据集表名和视图名
public void GETBIDBSOURCEVIEWLIST(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
int ID = Int32.Parse(P1);
DBFactory db = new BI_DB_SourceB().GetDB(ID);
msg.Result = db.GetTables();
}
/// <summary>
/// 根据表解析数据字段
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void GETFIELDDATA(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
DBFactory db = new BI_DB_SourceB().GetDB(0);
DataTable dt = db.GetDBClient().SqlQueryable <Object>(CommonHelp.Filter("SELECT * FROM " + P1)).ToDataTablePage(1, 1);
List <BI_DB_Dim> ListDIM = new BI_DB_SetB().getCType(dt);
msg.Result = ListDIM;
}
public void GETBIFILEDSLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
int ID = Int32.Parse(P1);
DBFactory db = new BI_DB_SourceB().GetDB(ID);
string strTableName = P2;
msg.Result = db.GetFiledS(strTableName);
}
public void GETSQLDATA(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
string SQL = CommonHelp.Filter(P1);
DBFactory db = new BI_DB_SourceB().GetDB(0);
//var dt = new Dictionary<string, object>();
//dt.Add("ID", "6988");
//dt.Add("Remark1", "123");
//dt.Add("Remark2", "asdasd");
//db.UpdateData(dt, "JH_Auth_ZiDian");
DataTable dt = db.GetSQL(SQL);
msg.Result = dt;
}
//获取数据源
public void GETBIDBSOURCELIST(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
string userName = UserInfo.User.UserName;
string strWhere = "1=1";
int page = 0;
int pagecount = 8;
int.TryParse(context.Request("p") ?? "1", out page);
int.TryParse(context.Request("pagecount") ?? "8", out pagecount);//页数
page = page == 0 ? 1 : page;
int total = 0;
var dt = new BI_DB_SourceB().Db.Queryable <BI_DB_Source>().Where(strWhere).OrderBy(it => it.CRDate, OrderByType.Desc).ToPageList(page, pagecount, ref total);
msg.Result = dt;
msg.Result1 = total;
}
//获取数据源
public void GETBIDBSOURCELIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
string userName = UserInfo.User.UserName;
string strWhere = "1=1";
int page = 0;
int pagecount = 8;
int.TryParse(context.Request["p"] ?? "1", out page);
int.TryParse(context.Request["pagecount"] ?? "8", out pagecount);//页数
page = page == 0 ? 1 : page;
int total = 0;
DataTable dt = new BI_DB_SourceB().GetDataPager(" BI_DB_Source ", "*", pagecount, page, " CRDate desc", strWhere, ref total);
msg.Result = dt;
msg.Result1 = total;
}
public void JXSQL(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
try
{
int ID = int.Parse(P1);
BI_DB_Set DS = new BI_DB_SetB().GetEntity(d => d.ID == ID);
DBFactory db = new BI_DB_SourceB().GetDB(DS.SID.Value);
DataTable dt = new DataTable();
dt = db.GetSQL(CommonHelp.Filter(P2));
List <BI_DB_Dim> ListDIM = new BI_DB_SetB().getCType(dt);
ListDIM.ForEach(D => D.STID = ID);
msg.Result = ListDIM.Where(D => D.Dimension == "1");
msg.Result1 = ListDIM.Where(D => D.Dimension == "2");
}
catch (Exception ex)
{
msg.ErrorMsg = ex.Message;
}
}
/// <summary>
/// 获取仪表盘数据接口
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void GETYBDATA(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
try
{
JObject wigdata = JObject.Parse(P1);
string datatype = (string)wigdata["datatype"]; //数据来源类型0:SQL,1:API
if (datatype == "0") //SQL取数据
{
string strWigdetType = (string)wigdata["component"];
string strDateSetName = (string)wigdata["datasetname"];
string filtervalsql = (string)wigdata["filtervalsql"] ?? "";
string ordersql = (string)wigdata["order"] ?? "";
string strPageCount = context.Request("pagecount") ?? "10";
string strquerydata = context.Request("querydata") ?? ""; //查询条件数据
string isglquery = (string)wigdata["isglquery"] == "True" ? "Y" : "N"; //关联查询条件数据
string strWhere = "";
BI_DB_Set DS = new BI_DB_SetB().GetEntities(d => d.Name == strDateSetName).FirstOrDefault();
DBFactory db = new BI_DB_SourceB().GetDB(DS.SID.Value);
//默认过滤
if (filtervalsql != "")
{
strWhere = " AND " + filtervalsql;
}
///有查询字段数据并且关联查询组件时生成查询条件
if (strquerydata != "" && isglquery == "Y")
{
JArray categories = JArray.Parse(strquerydata);
foreach (JObject item in categories)
{
string FiledName = (string)item["glfiled"];
string ColumnType = (string)item["ColumnType"];
string eltype = (string)item["component"];
if (eltype == "qjInput")
{
string strValue = (string)item["value"];
if (!string.IsNullOrEmpty(strValue))
{
string strSQL = string.Format(" AND {0} LIKE ('%{1}%')", FiledName.Replace(',', '+'), strValue);
strWhere = strWhere + strSQL;
}
}
if (eltype == "qjSeluser" || eltype == "qjSelbranch")
{
string strValue = (string)item["value"];
if (!string.IsNullOrEmpty(strValue))
{
string strSQL = string.Format(" AND {0} IN ('{1}')", FiledName.Replace(',', '+'), strValue.ToFormatLike());
strWhere = strWhere + strSQL;
}
}
if (eltype == "qjMonth" || eltype == "qjDate")
{
if (item["value"] != null && item["value"].ToString() != "")
{
string strval = item["value"].ToString();
string sDate = strval.Split(',')[0].ToString();
string eDate = strval.Split(',')[1].ToString();
string strSQL = string.Format(" AND {0} BETWEEN '{1} 00:00' AND '{2} 23:59' ", FiledName, sDate, eDate);
strWhere = strWhere + strSQL;
}
}
}
}
//if (strWigdetType == "qjTable")
//{
// string strTablefiled = "";
// JArray categoriestab = (JArray)wigdata["tabfiledlist"];//查询字段
// foreach (JObject item in categoriestab)
// {
// string FiledName = (string)item["colid"];
// string FiledJSType = (string)item["caltype"] ?? "";
// strTablefiled = strTablefiled + FiledName + ",";
// }
// msg.Result = db.GetYBData(DS, strWD, strDL, strTablefiled, strPageCount, strWhere);
//}
if (strWigdetType == "qjChartPie" || strWigdetType == "qjKBan" || strWigdetType == "qjTable" || strWigdetType == "qjChartBar")
{
JArray wdlist = (JArray)wigdata["wdlist"];
JArray dllist = (JArray)wigdata["dllist"];
string strWD = "";
foreach (JObject item in wdlist)
{
strWD = strWD + (string)item["colid"] + ",";
}
strWD = strWD.TrimEnd(',');
string strDL = "";
foreach (JObject item in dllist)
{
strDL = strDL + " " + (string)item["caltype"] + "(" + (string)item["colid"] + ") AS " + (string)item["colid"] + ",";
}
strDL = strDL.TrimEnd(',');
msg.Result = db.GetYBData(DS, strWD, strDL, strPageCount, strWhere, ordersql);
}
}
else//API取数据
{
string strAPIUrl = (string)wigdata["apiurl"] + "&szhlcode=" + UserInfo.User.pccode;
string str = CommonHelp.GetAPIData(strAPIUrl);
msg.Result = str;
}
}
catch (Exception ex)
{
msg.ErrorMsg = ex.Message;
}
}
public void SAVEEXDATA(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
int piId = 0;
int.TryParse(P1, out piId);
int PDID = 0;
int.TryParse(P2, out PDID);
Yan_WF_PI lcsp = new Yan_WF_PIB().GetEntity(d => d.ID == piId);
Yan_WF_PD pd = new Yan_WF_PDB().GetEntity(d => d.ID == PDID);
JArray datas = (JArray)JsonConvert.DeserializeObject(lcsp.Content);
var dt = new Dictionary <string, object>();
dt.Add("CRUser", UserInfo.User.UserName);
dt.Add("CRDate", DateTime.Now);
dt.Add("ComID", UserInfo.User.ComId.Value);
dt.Add("intProcessStanceid", piId);
try
{
List <JH_Auth_ExtendData> ListNew = new List <JH_Auth_ExtendData>();
foreach (JObject item in datas)
{
JH_Auth_ExtendData Model = new JH_Auth_ExtendData();
Model.ComId = UserInfo.User.ComId;
Model.BranchNo = UserInfo.User.BranchCode;
Model.DataID = piId;
Model.PDID = PDID;
Model.TableName = "LCSP";
Model.ExFiledColumn = (string)item["wigdetcode"];
Model.ExFiledName = (string)item["title"];
string strValue = "";
strValue = (string)item["value"];
Model.ExtendDataValue = strValue.Trim(',');
Model.CRUser = UserInfo.User.UserName;
Model.CRDate = DateTime.Now;
Model.CRUserName = UserInfo.User.UserRealName;
Model.BranchNo = UserInfo.BranchInfo.DeptCode;
Model.BranchName = UserInfo.BranchInfo.DeptName;
ListNew.Add(Model);
string strglfiled = (string)item["glfiled"] ?? "";
if (strglfiled != "")
{
dt.Add(strglfiled, strValue);
}
}
new JH_Auth_ExtendDataB().Delete(d => d.DataID == piId && d.PDID == PDID);
new JH_Auth_ExtendDataB().Insert(ListNew);
if (!string.IsNullOrEmpty(pd.RelatedTable))
{
int intcount = new Yan_WF_PDB().GetDTByCommand("SELECT * FROM " + pd.RelatedTable + " WHERE intProcessStanceid='" + piId.ToString() + "'").Rows.Count;
DBFactory db = new BI_DB_SourceB().GetDB(0);
if (intcount > 0)
{
//更新
}
else
{
//新增
db.InserData(dt, pd.RelatedTable);
}
}
}
catch (Exception ex)
{
}
}
