/// <summary>
/// 获取结构
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public JObject GetSchema(HttpContext context)
{
JObject obj = new JObject();
try
{
YZRequest request = new YZRequest(context);
int connectId = request.GetInt32("connectId");
string sheetName = request.GetString("sheetName");
string titleIndex = request.GetString("titleIndex");
string query = string.Format("{0}|{1}", sheetName, titleIndex);
ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId);
ExcelLogin login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>();
ISourceHandle handle = new ExcelHandle(login);
QueryDepictInfo info = handle.GetQueryInfo(sheetName);
info.query = query;
info = handle.GetQueryInfo(info);
obj["schema"] = GetColumnJson(info);
obj[YZJsonProperty.success] = true;
return(obj);
}
catch (Exception)
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = "没有可供查询的表或视图！";
return(obj);
}
}
public void LTHUITest()
{
// var test = extent.CreateTest("<div style='color:green; font -weight : bold'>Create Admin</div>", "This case may attains passed or failed");
try
{
string paths = @"C:\ExcelReports\LoginLogout.xlsx";
ExcelLogin.PopulateInCollection(paths);
LH = new ChromeDriver();
LH.Manage().Window.Maximize();
LH.Manage().Cookies.DeleteAllCookies();
LH.Navigate().GoToUrl("https://app-latest-us4.usdev.wfsaas.net/#/login");
// Assert.AreEqual("latest", LH.Title);
WebDriverWait wait = new WebDriverWait(LH, TimeSpan.FromSeconds(15));
// test.Log(Status.Info, "Steps are Processing");
// test.Log(Status.Info, "Create Admin steps are processing");
LHUITest UI = new LHUITest(LH);
//PageFactory.InitElements(Atlas, login);
UI.AccountId1();
UI.CliNext1();
UI.UserName1();
UI.Password1();
UI.assetforgot();
UI.content1();
UI.logo1();
UI.accountname1();
UI.acclogobtn1();
UI.submit1();
UI.usernav1();
UI.logout1();
// System.Threading.Thread.Sleep(20000);
//// string screenshot = Util.Capture(Atlas, "Create Admin");
//test.Pass("Create Admin been completed");
//test.Pass("Create Admin been Done");
//// test.AddScreenCaptureFromPath(screenshot);
}
catch (Exception e)
{
//test.Log(Status.Fail, "So SAD");
// string screenshots3 = Util.Capture(Atlas, "Failed Admin");
//test.Fail("Admin Case been failed :(");
// test.AddScreenCaptureFromPath(screenshots3);
Console.WriteLine(e.Message);
}
finally
{
extent.Flush();
}
}
/// <summary>
/// 测试excel数据库连接
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public JObject JoinExcel(HttpContext context)
{
JObject obj = new JObject();
YZRequest request = new YZRequest(context);
ExcelLogin login = new ExcelLogin();
login.excelPath = request.GetString("excelPath");
string message = null;
bool flag = CommonHandle.IsExcelJoin(login, out message);
obj[YZJsonProperty.success] = true;
obj[YZJsonProperty.errorMessage] = flag ? "连接成功！" : String.Format("连接失败！原因：<br/>{0}", message);
return(obj);
}
public void Password1()
{
LH.FindElement(Password).SendKeys(ExcelLogin.ReadData(1, "Password"));
System.Threading.Thread.Sleep(4000);
}
public void UserName1()
{
LH.FindElement(UserName).SendKeys(ExcelLogin.ReadData(1, "UserName"));
}
public void AccountId1()
{
LH.FindElement(AccountId).SendKeys(ExcelLogin.ReadData(1, "AccountID"));
}
/// <summary>
/// 保存excel数据连接
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public JObject SaveExcelConnect(HttpContext context)
{
JObject obj = new JObject();
YZRequest request = new YZRequest(context);
try
{
//获取操作模式
string joinType = request.GetString("joinType");
//获取连接数据
ExcelLogin login = new ExcelLogin();
login.excelPath = request.GetString("excelPath");
string message = null;
if (!CommonHandle.IsExcelJoin(login, out message))
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = "服务无法连接！";
return(obj);
}
if (joinType.ToUpper().Equals("THEEDIT"))
{
//编辑模式
ConnectInfo info = ConnectInfoManager.GetConnectInfo(request.GetInt32("connectId", -1));
info.caption = request.GetString("caption", "");
info.connectStr = JObject.FromObject(login).ToString();
info.updateTime = DateTime.Now;
info.isvalid = true;
//更新
ConnectInfoManager.EditConnect(info);
obj["connectId"] = info.connectId;
}
else
{
//新增模式
if (ConnectInfoManager.IsConnectRepeat(request.GetString("connectName", ""), SourceTypeEnum.Excel))
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = "已存在同名的数据源";
return(obj);
}
ConnectInfo info = new ConnectInfo();
info.connectName = request.GetString("connectName", "");
info.connectType = SourceTypeEnum.Excel;
info.caption = request.GetString("caption", "");
info.connectStr = JObject.FromObject(login).ToString();
info.createTime = DateTime.Now;
info.updateTime = DateTime.Now;
info.isvalid = true;
//新增
ConnectInfoManager.AddConnect(info);
obj["connectId"] = ConnectInfoManager.GetConnectInfo(SourceTypeEnum.Excel, info.connectName).connectId;
}
obj[YZJsonProperty.success] = true;
obj[YZJsonProperty.errorMessage] = "数据连接保存成功";
return(obj);
}
catch (Exception ex)
{
string msg = string.Format("保存失败:{0}", ex.ToString());
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = msg;
return(obj);
}
}
/// <summary>
/// 保存数据源
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public JObject SaveSource(HttpContext context)
{
JObject obj = new JObject();
try
{
YZRequest request = new YZRequest(context);
string joinType = request.GetString("joinType");
string sourceName = request.GetString("sourceName");
string caption = request.GetString("caption", "");
int connectId = request.GetInt32("connectId");
int sourceId = request.GetInt32("sourceId", 0);
string sheetName = request.GetString("sheetName");
string titleIndex = request.GetString("titleIndex");
string query = string.Format("{0}|{1}", sheetName, titleIndex);
//判断是否重名
bool isNameOnly = SourceInfoManager.IsSourceNameRepeat(sourceName, SourceTypeEnum.Excel, sourceId);
if (isNameOnly)
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = "已存在同名的数据源，请重命名数据源名称！";
return(obj);
}
ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId);
ExcelLogin login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>();
ISourceHandle handle = new ExcelHandle(login);
QueryDepictInfo info = handle.GetQueryInfo(sheetName);
info.query = query;
info = handle.GetQueryInfo(info);
StreamReader reader = new StreamReader(context.Request.InputStream);
using (JsonTextReader streamReader = new JsonTextReader(reader))
{
//获取参数配置
JsonSerializer serializer = new JsonSerializer();
JArray array = serializer.Deserialize(streamReader) as JArray;
List <ColumnInfo> param = new List <ColumnInfo>();
foreach (var item in array)
{
if (item["inputIsShow"].ToString().ToUpper().Equals("TRUE"))
{
ColumnInfo column = new ColumnInfo();
column.columnName = item["columnName"].ToString();
column.columnType = item["columnType"].ToString();
column.defaultValue = item["defaultValue"].ToString();
column.isShow = 1;
column.rename = item["inputRename"].ToString();
param.Add(column);
}
ColumnInfo schemaColumn = info.schema.First(n => n.columnName == item["columnName"].ToString());
if (item["outputIsShow"].ToString().ToUpper().Equals("TRUE"))
{
schemaColumn.rename = item["outputRename"].ToString();
schemaColumn.isShow = 1;
}
else
{
schemaColumn.isShow = 0;
}
}
info.parameter = param;
}
if (joinType.ToUpper().Equals("THEEDIT"))
{
SourceInfo sourceInfo = SourceInfoManager.GetSourceInfo(sourceId);
sourceInfo.sourceName = sourceName;
sourceInfo.caption = caption;
sourceInfo.updateTime = DateTime.Now;
sourceInfo.sourceStr = JObject.FromObject(info).ToString();
SourceInfoManager.EditSource(sourceInfo);
}
else
{
SourceInfo sourceInfo = new SourceInfo();
sourceInfo.sourceName = sourceName;
sourceInfo.sourceType = SourceTypeEnum.Excel;
sourceInfo.connectInfo = ConnectInfoManager.GetConnectInfo(connectId);
sourceInfo.caption = caption;
sourceInfo.isvalid = true;
sourceInfo.createTime = DateTime.Now;
sourceInfo.updateTime = DateTime.Now;
sourceInfo.sourceStr = JObject.FromObject(info).ToString();
SourceInfoManager.AddSource(sourceInfo);
}
obj[YZJsonProperty.success] = true;
obj[YZJsonProperty.errorMessage] = "保存成功";
return(obj);
}
catch (Exception ex)
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = ex.Message.ToString();
return(obj);
}
}
/// <summary>
/// 返回结果
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public JObject GetResult(HttpContext context)
{
JObject obj = new JObject();
try
{
YZRequest request = new YZRequest(context);
int connectId = request.GetInt32("connectId");
string sheetName = request.GetString("sheetName");
string titleIndex = request.GetString("titleIndex");
string query = string.Format("{0}|{1}", sheetName, titleIndex);
ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId);
ExcelLogin login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>();
ISourceHandle handle = new ExcelHandle(login);
QueryDepictInfo info = handle.GetQueryInfo(sheetName);
info.query = query;
info = handle.GetQueryInfo(info);
StreamReader reader = new StreamReader(context.Request.InputStream);
using (JsonTextReader streamReader = new JsonTextReader(reader))
{
//获取参数配置
JsonSerializer serializer = new JsonSerializer();
JArray array = serializer.Deserialize(streamReader) as JArray;
List <ColumnInfo> param = new List <ColumnInfo>();
foreach (var item in array)
{
if (item["inputIsShow"].ToString().ToUpper().Equals("TRUE"))
{
ColumnInfo column = new ColumnInfo();
column.columnName = item["columnName"].ToString();
column.columnType = item["columnType"].ToString();
column.defaultValue = item["defaultValue"].ToString();
column.isShow = 1;
column.rename = item["inputRename"].ToString();
param.Add(column);
}
ColumnInfo schemaColumn = info.schema.First(n => n.columnName == item["columnName"].ToString());
if (item["outputIsShow"].ToString().ToUpper().Equals("TRUE"))
{
schemaColumn.rename = item["outputRename"].ToString();
schemaColumn.isShow = 1;
}
else
{
schemaColumn.isShow = 0;
}
}
info.parameter = param;
}
//访问
DataTable dt = handle.GetResult(info);
JArray results = new JArray();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
JObject item = new JObject();
foreach (DataColumn column in dt.Columns)
{
item[column.ColumnName] = row[column.ColumnName].ToString();
}
results.Add(item);
}
}
else
{
JObject item = new JObject();
foreach (DataColumn column in dt.Columns)
{
item[column.ColumnName] = "";
}
results.Add(item);
}
obj["children"] = results;
//执行数据
obj[YZJsonProperty.success] = true;
return(obj);
}
catch (Exception ex)
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = ex.ToString();
return(obj);
}
}
