/// <summary>
///
/// Execute Multiple queries and return result of each query in a list
///
/// </summary>
/// <param name="sql">sql splitted by '/'</param>
/// <param name="lstParams">Parameters for each query </param>
/// <returns>
/// json dataset</returns>
public JsonResult ExecuteNonQuery(SqlParamsIn sqlIn)
{
SqlHelper h = new SqlHelper();
object data = h.ExecuteNonQuery(sqlIn);
return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet));
}
/// <summary>
/// json object representation of dataset
/// --has table or tables -
/// --
/// </summary>
/// <param name="sqlIn">sql param datacontract </param>
/// <returns>
/// json dataset</returns>
public JsonResult JDataSetWithCode(SqlParamsIn sqlIn)
{
SqlHelper h = new SqlHelper();
Ctx ctx = Session["ctx"] as Ctx;
h.SetContext(ctx);
if (sqlIn.sql.IndexOf("~") > -1)
{
string[] sql = sqlIn.sql.Split('~');
for (int i = 0; i < sql.Length; i++)
{
if (i == 0)
{
sqlIn.sql = QueryCollection.GetQuery(sql[i], ctx);
}
else
{
sqlIn.sql += "~" + QueryCollection.GetQuery(sql[i], ctx);
}
}
}
else
{
sqlIn.sql = QueryCollection.GetQuery(sqlIn.sql, ctx);
}
object data = h.ExecuteJDataSet(sqlIn);
return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet));
}
/// <summary>
/// Attribute Listing Tree..
/// </summary>
/// <returns></returns>
public object createTreeAttributeName()
{
StringBuilder sql = new StringBuilder();
sql.Append(MRole.GetDefault(_ctx).AddAccessSQL("SELECT M_Attribute_id , Name FROM M_Attribute WHERE IsActive='Y'", "M_Attribute", true, true));
sql.Append("~");
sql.Append(MRole.GetDefault(_ctx).AddAccessSQL("SELECT ma.M_Attribute_id,mv.m_attributevalue_id, ma.Name as NameAttribute,mv.Name FROM M_Attribute ma inner JOIN m_attributevalue mv ON ma.M_Attribute_id=mv.M_Attribute_id", "M_Attribute", true, true));
SqlParamsIn sqlpar = new SqlParamsIn();
sqlpar.sql = sql.ToString();
sqlpar.pageSize = 0;
VIS.Helpers.SqlHelper sHelper = new VIS.Helpers.SqlHelper();
DataSet ds = sHelper.ExecuteDataSet(sqlpar);
if (ds == null || ds.Tables.Count < 1)
{
return(null);
}
VA005_TreeStructure tree = new VA005_TreeStructure();
tree.text = Msg.GetMsg(_ctx, "VA005_AttributeTree");
tree.NodeID = 0;
tree.items = new List <VA005_TreeStructure>();
LoadAttributeName(ds, tree);
List <VA005_TreeStructure> final = new List <VA005_TreeStructure>();
final.Add(tree);
return(final);
}
//private bool isDataAccess(string sql)
//{
//}
public DataSet ExecuteDataSet(SqlParamsIn sqlIn)
{
if (String.IsNullOrEmpty(sqlIn.sql))
{
return(null);
}
string sql = sqlIn.sql;
bool doPaging = sqlIn.pageSize > 0;
SqlParams[] paramIn = sqlIn.param == null ? null : sqlIn.param.ToArray();
Trx trxName = null;
string[] tables = sql.Split('~');
DataSet ds = new DataSet();
int i = 0;
foreach (string table in tables)
{
string tableName = "Table" + i;
DataSet dsTemp = null;
if (!doPaging)
{
dsTemp = VIS.DBase.DB.ExecuteDataset(table, paramIn, trxName);
if (dsTemp != null && dsTemp.Tables.Count > 0)
{
DataTable data = dsTemp.Tables[0];
dsTemp.Tables.Remove(data);
data.TableName = tableName;
ds.Tables.Add(data);
}
i++;
// ds = VAdvantage.DataBase.DB.SetUtcDateTime(ds);
}
else //Paging
{
ds = VIS.DBase.DB.ExecuteDatasetPaging(sql, sqlIn.page, sqlIn.pageSize);
}
}
if (ds == null || ds.Tables.Count < 1)
{
return(null);
}
ds = VAdvantage.DataBase.DB.SetUtcDateTime(ds);
return(ds);
}
public ActionResult GetTextButtonQueryResult(string sql)
{
Ctx ctx = Session["ctx"] as Ctx;
SqlParamsIn sqlP = new SqlParamsIn();
sql = SecureEngineBridge.DecryptByClientKey(sql, ctx.GetSecureKey());
sqlP.sql = sql;
VIS.Helpers.SqlHelper help = new Helpers.SqlHelper();
return(Json(JsonConvert.SerializeObject(help.ExecuteJDataSet(sqlP)), JsonRequestBehavior.AllowGet));
}
public JsonResult JDataSet(SqlParamsIn sqlIn)
{
SqlHelper h = new SqlHelper();
sqlIn.sql = Server.HtmlDecode(sqlIn.sql);
VAdvantage.Utility.Ctx ctx = Session["ctx"] as Ctx;
sqlIn.sql = SecureEngineBridge.DecryptByClientKey(sqlIn.sql, ctx.GetSecureKey());
object data = h.ExecuteJDataSet(sqlIn);
return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet));
}
/// <summary>
///
/// Execute Multiple queries and return result of each query in a list
///
/// </summary>
/// <param name="sql">sql splitted by '/'</param>
/// <param name="lstParams">Parameters for each query </param>
/// <returns>
/// json dataset</returns>
public JsonResult ExecuteNonQueryWithCode(SqlParamsIn sqlIn)
{
SqlHelper h = new SqlHelper();
Ctx ctx = Session["ctx"] as Ctx;
h.SetContext(ctx);
//sqlIn.sql = SecureEngineBridge.DecryptByClientKey(sqlIn.sql, ctx.GetSecureKey());
sqlIn.sql = QueryCollection.GetQuery(sqlIn.sql, ctx);
object data = h.ExecuteNonQuery(sqlIn);
return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet));
}
/// <summary>
/// USed to Create Tree. This is main function which intialize tree creation.
/// </summary>
public object CreateTree(string Expend)
{
exp = Expend;
StringBuilder sql = new StringBuilder();
sql.Append(MRole.GetDefault(_ctx).AddAccessSQL("SELECT m_attributeSet_ID , Name FROM m_attributeSet WHERE IsActive='Y' ORDER BY LOWER(Name),m_attributeSet_ID", "M_AttributeSet", true, true));
sql.Append("~");
sql.Append(MRole.GetDefault(_ctx).AddAccessSQL(@"SELECT m_attribute.m_attribute_ID,m_attribute.Name,m_attributeuse.m_attributeset_id,m_attribute.ATTRIBUTEVALUETYPE
FROM m_attributeuse
JOIN m_attribute
ON m_attributeuse.m_attribute_ID=m_attribute.m_attribute_ID WHERE m_attributeuse.IsActive='Y' AND m_attributeuse.IsActive='Y' ORDER BY m_attribute.Name,m_attribute.m_attribute_ID", "M_AttributeUse", true, true));
sql.Append("~");
sql.Append(MRole.GetDefault(_ctx).AddAccessSQL("SELECT m_attribute_ID , name, M_AttributeValue_ID FROM M_AttributeValue WHERE IsActive='Y' ORDER BY name,m_attribute_ID ", "M_AttributeValue", true, true));
sql.Append("~");
SqlParamsIn sqlpar = new SqlParamsIn();
sqlpar.sql = sql.ToString();
sqlpar.pageSize = 0;
VIS.Helpers.SqlHelper sHelper = new VIS.Helpers.SqlHelper();
DataSet ds = sHelper.ExecuteDataSet(sqlpar);
if (ds == null || ds.Tables.Count < 1)
{
return(null);
}
VA005_TreeStructure tree = new VA005_TreeStructure();
tree.text = Msg.GetMsg(_ctx, "VA005_AttributeSetListing");
tree.NodeID = 0;
//tree.ParentID = 0;
tree.expanded = true;
tree.visibility = "none";
tree.ShowInfo = "none";
tree.padding = "8px 10px 8px 10px";
tree.margin = "0 0 0 0";
//tree.ImageSource = "Areas/VA005/Images/1.png";
tree.items = new List <VA005_TreeStructure>();
LoadAttributeSet(ds, tree);
//hidden
List <VA005_TreeStructure> final = new List <VA005_TreeStructure>();
final.Add(tree);
return(final);
}
/// <summary>
/// retrun Grid window Model json object against window Id
/// </summary>
/// <param name="windowNo">window number</param>
/// <param name="AD_Window_ID">window Id</param>
/// <returns>grid window json result</returns>
public JsonResult GetWindowRecords(List <string> fields, SqlParamsIn sqlIn, int rowCount, string sqlCount, int AD_Table_ID)
{
object data = null;
if (Session["ctx"] == null)
{
}
else
{
using (var w = new WindowHelper())
{
data = w.GetWindowRecords(sqlIn, fields, Session["ctx"] as Ctx, rowCount, sqlCount, AD_Table_ID);
}
}
return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet));
}
/// <summary>
/// retrun Grid window Model json object against window Id
/// </summary>
/// <param name="windowNo">window number</param>
/// <param name="AD_Window_ID">window Id</param>
/// <returns>grid window json result</returns>
public JsonResult GetWindowRecordsForTreeNode(List <string> fields, SqlParamsIn sqlIn, int rowCount, string sqlCount, int AD_Table_ID, int treeID, int treeNodeID, List <string> obscureFields)
{
object data = null;
if (Session["ctx"] == null)
{
}
else
{
using (var w = new WindowHelper())
{
Ctx ctx = Session["ctx"] as Ctx;
sqlIn.sql = SecureEngineBridge.DecryptByClientKey(sqlIn.sql, ctx.GetSecureKey());
sqlIn.sql = Server.HtmlDecode(sqlIn.sql);
sqlCount = SecureEngineBridge.DecryptByClientKey(sqlCount, ctx.GetSecureKey());
data = w.GetWindowRecordsForTreeNode(sqlIn, fields, ctx, rowCount, sqlCount, AD_Table_ID, treeID, treeNodeID, obscureFields);
}
}
return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet));
}
public int ExecuteNonQuery(SqlParamsIn sqlIn)
{
SqlParams[] paramIn = sqlIn.param == null ? null : sqlIn.param.ToArray();
string sql = sqlIn.sql;
//if (!CanDeletingADValue(sql))
//{
// return -1;
//}
//string sql = sqlIn.sql;
//if (!IsTableAccessForExecute(sql))
// return -1;
if (IsChangingSystemSettings(sql))
{
return(-1);
}
return(VIS.DBase.DB.ExecuteQuery(sqlIn.sql, paramIn, null));
}
public ActionResult GetPrintFormatDetails(int AD_Table_ID)
{
Ctx ctx = Session["ctx"] as Ctx;
FormModel model = new FormModel(ctx);
string sql = MRole.GetDefault(ctx).AddAccessSQL(
"SELECT AD_PrintFormat_ID, Name, Description,IsDefault "
+ "FROM AD_PrintFormat "
+ "WHERE AD_Table_ID= " + AD_Table_ID
+ " ORDER BY Name",
"AD_PrintFormat", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
SqlParamsIn sqlP = new SqlParamsIn();
sqlP.sql = sql;
VIS.Helpers.SqlHelper help = new Helpers.SqlHelper();
List <JTable> result = help.ExecuteJDataSet(sqlP);
return(Json(JsonConvert.SerializeObject(result), JsonRequestBehavior.AllowGet));
}
/// <summary>
/// fetch Data from DataServer and create list of Jtable objects
/// </summary>
/// <param name="sqlIn">input parameter</param>
/// <returns>null if error , other wise list of JTable object </returns>
public List <JTable> ExecuteJDataSet(SqlParamsIn sqlIn)
{
try
{
List <JTable> outO = new List <JTable>();
JTable obj = null;
DataSet ds = null;
string sql = sqlIn.sql;
//string sql = sqlIn.sql;
//if (!IsTableAccess(sql))
// return null;
ds = ExecuteDataSet(sqlIn);
if (ds == null || ds.Tables.Count < 1)
{
return(null);
}
// bool singleTable = ds.Tables.Count >= 0;
//StringBuilder tables = new StringBuilder("[");
// StringBuilder columns = new StringBuilder(!singleTable ? "[{" : "{");
int MAX_ROWS = 10001;
for (int table = 0; table < ds.Tables.Count; table++)
{
obj = new JTable();
var dt = ds.Tables[table];
obj.name = dt.TableName;
obj.records = ds.Tables[table].Rows.Count;
obj.page = sqlIn.page;
obj.total = 1;
for (int column = 0; column < dt.Columns.Count; column++)
{
var cc = new JColumn();
cc.index = column;
cc.name = dt.Columns[column].ColumnName.ToLower();
cc.type = dt.Columns[column].DataType.ToString().ToLower();
obj.columns.Add(cc);
}
int count = dt.Rows.Count;
for (int row = 0; row < count; row++)
{
if (row > MAX_ROWS)
{
break;
}
JRow r = new JRow();
r.id = row;
for (int column = 0; column < dt.Columns.Count; column++)
{
//var c = new Dictionary<string,object>();
//c[dt.Columns[column].ColumnName.ToLower()] = dt.Rows[row][column];
r.cells[dt.Columns[column].ColumnName.ToLower()] = dt.Rows[row][column];
//rows.Append(dt.Columns[column].ColumnName).Append(":").Append(dt.Rows[row][column]);
}
obj.rows.Add(r);
}
outO.Add(obj);
}
ds.Tables.Clear();
ds.Dispose();
return(outO);
}
catch
{
return(null);
}
}
public int ExecuteNonQuery(SqlParamsIn sqlIn)
{
SqlParams[] paramIn = sqlIn.param == null ? null : sqlIn.param.ToArray();
return(VIS.DBase.DB.ExecuteQuery(sqlIn.sql, paramIn, null));
}
