public string RMA(int deviceId)
{
try
{
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = "data source=localhost;initial catalog=CMDB;persist security info=True;Trusted_Connection=Yes;MultipleActiveResultSets=True;";
var deviceStatusId = 5; // RMA'd
var doD = "getdate()"; // Dod date
var rma = "?????"; //
var sql = "INSERT INTO Device(HostName, MfgId, Serial, DeviceSiteId, PoInfoId, MgtIp, DeviceModelId, DeviceTypeId, RackId, DoB, IsVirtual, Wlp, DeviceStatusId, RuNumber, DoD, FismaTag, ProvisionNnmi, ProvisionNetworkAutomation, ProvisionSolarWind, ProvisionAlgosecFiremon, ProvisionNetBrain, DeviceNote, DeviceGroups, RMA, DOMSSerial, DOMSRack, DOMSRU, SupportStart, SupportEnd, SupportContract, EOLDate) ";
sql += "SELECT HostName, MfgId, Serial, DeviceSiteId, PoInfoId, MgtIp, DeviceModelId, DeviceTypeId, RackId, DoB, IsVirtual, Wlp, " + deviceStatusId + ", RuNumber, " + doD + ", FismaTag, ProvisionNnmi, ProvisionNetworkAutomation, ProvisionSolarWind, ProvisionAlgosecFiremon, ProvisionNetBrain, DeviceNote, DeviceGroups,'" + rma + "', DOMSSerial, DOMSRack, DOMSRU, SupportStart, SupportEnd, SupportContract, EOLDate ";
sql += "FROM Device WHERE DeviceId = " + deviceId;
targetDb.Database.ExecuteSqlCommand(sql);
return("");
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string GetMultiSelect(int pageTemplateId, int columnDefId)
{
try
{
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
var columnDef = SessionService.ColumnDef(columnDefId);
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities Db = new TargetEntities())
{
Db.Database.Connection.ConnectionString = dbEntity.ConnectionString;
var columnValue = columnDef.ColumnName;
var columnText = columnDef.ColumnName;
var tableName = pageTemplate.TableName;
var sql = "SELECT DISTINCT ISNULL(CAST(" + columnValue + " AS varchar(500)),'') AS ValueField, ISNULL(CAST(" + columnText + " AS varchar(500)),'') AS TextField FROM " + tableName + " ORDER BY TextField";
if (columnDef.ElementType == "DropdownCustomOption")
{
sql = "SELECT OptionValue AS ValueField, OptionText AS TextField FROM CustomOption WHERE ColumnDefId = " + columnDefId + " ORDER BY TextField";
}
else if (columnDef.ElementType == "DropdownSimple")
{
sql = "SELECT ISNULL(CAST(" + columnDef.ValueField + " AS varchar(250)),'') AS ValueField, ISNULL(CAST(" + columnDef.TextField.Replace(",", " + ' - ' + ") + " AS varchar(500)),'') AS TextField FROM " + columnDef.LookupTable + " ORDER BY TextField";
}
var recs = Db.Database.SqlQuery <ValueText>(sql).ToList();
var json = JsonConvert.SerializeObject(recs);
return(json);
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
public List <SwitchPort> GetPortResultsObject(string searchString, string searchBy)
{
var likeClause = "";
searchString = searchString.ToLower();
if (searchString.Contains(","))
{
var words = searchString.Split(',').ToList();
foreach (var word in words)
{
likeClause += " HostName LIKE '%" + searchString.Replace("'", "''") + "%' OR ";
}
likeClause = likeClause.Substring(0, likeClause.Length - 3);
}
else
{
likeClause = " HostName LIKE '%" + searchString.Replace("'", "''") + "%' ";
}
using (TargetEntities targetDb = new TargetEntities("NetworkCafeEntities"))
{
var sql = "SELECT * FROM SwitchPorts WHERE " + likeClause;
var results = targetDb.Database.SqlQuery <SwitchPort>(sql).ToList();
return(results);
}
}
public override string SummarizeToString()
{
string Ret = "ComputedEntity(" + Name + ") [ " + SourceEntity.SummarizeToString() + " x { ";
TargetEntities.ForEach(te => Ret += te.SummarizeToString() + " ");
Ret += "}";
return(Ret);
}
public string GetJobs()
{
using (TargetEntities Db = new TargetEntities("F5DataEntities"))
{
var recs = Db.Database.SqlQuery <JobLog>("SELECT * FROM JobLog ORDER BY endTime DESC").ToList();
var json = new JavaScriptSerializer().Serialize(recs);
return(json);
}
}
public string GetCustomOption(int customDefId, int dbEntityId)
{
var dbEntity = SessionService.DbEntity(dbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
var recs = targetDb.Database.SqlQuery <CustomOption>("SELECT * FROM CustomOption WHERE ColumnDefId = " + customDefId);
string json = JsonConvert.SerializeObject(recs);
return(json);
}
}
public string EncryptRecords(int columnDefId)
{
var numOfRecord = 0;
var columnName = "";
try
{
StringBuilder sb = new StringBuilder();
var i = 0;
var columnDef = SessionService.ColumnDef(columnDefId);
var pageTemplate = SessionService.PageTemplate(columnDef.PageTemplateId);
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
var encryptedValue = "";
columnName = columnDef.ColumnName;
var tableName = pageTemplate.TableName;
var primaryKey = pageTemplate.PrimaryKey;
var primaryKeyType = pageTemplate.PrimaryKeyType;
var sql = "SELECT CAST(" + primaryKey + " AS nvarchar(36)) AS RecordId, CAST(ISNULL(" + columnName + ", '') AS varchar(max)) AS RecordValue FROM " + tableName;
var exe = "UPDATE " + tableName + " SET " + columnName + " = '[RecordValue]' WHERE " + primaryKey + " = '[RecordId]';";
var items = targetDb.Database.SqlQuery <EncrypedRecord>(sql).ToList();
foreach (var item in items.Where(w => w.RecordValue.Length > 0))
{
encryptedValue = Crypto.Encrypt(item.RecordValue).Replace("'", "''");
sb.Append(exe.Replace("[RecordId]", item.RecordId).Replace("[RecordValue]", encryptedValue));
numOfRecord++;
i++;
if (i > 50)
{
i = 0;
targetDb.Database.ExecuteSqlCommand(sb.ToString());
sb.Clear();
}
}
if (i > 0 && sb.Length > 0)
{
targetDb.Database.ExecuteSqlCommand(sb.ToString());
}
}
return("Successful. " + numOfRecord + " records were encrypted for column " + columnName + ".");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string DeleteChildTableRecord(int pageTemplateId, int recordId)
{
PageTemplate pageTemplate = SessionService.PageTemplate(pageTemplateId);
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
string sql = "DELETE FROM " + pageTemplate.TableName + " WHERE " + pageTemplate.PrimaryKey + " = " + recordId;
targetDb.Database.ExecuteSqlCommand(sql);
return("T");
}
}
public string GetDeviceVendors()
{
string json = "";
using (TargetEntities targetDb = new TargetEntities("NetworkCafeEntities"))
{
var sql = @"SELECT Distinct DeviceVendor AS TextField, DeviceVendor AS ValueField FROM MasterDevice ORDER BY DeviceVendor";
var recs = targetDb.Database.SqlQuery <ValueText>(sql).ToList();
json = Newtonsoft.Json.JsonConvert.SerializeObject(recs);
}
return(json);
}
public static string GetPrimaryKey(int pageTemplateId, string tableName)
{
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities Db = new TargetEntities())
{
Db.Database.Connection.ConnectionString = dbEntity.ConnectionString;
var columnName = Db.Database.SqlQuery <string>("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1 AND TABLE_NAME = '" + tableName + "'").FirstOrDefault();
if (columnName != null)
{
return(columnName);
}
}
return("");
}
public string UpdateNetworkInformationContracts(string contractData)
{
var rec = JsonConvert.DeserializeObject <ContractData>(contractData);
var sql = @"UPDATE ContractV2 SET licenses = '" + Helper.ToDbString(rec.licenses) + "', notes = '" + Helper.ToDbString(rec.notes) + "',contractNumber = '" + Helper.ToDbString(rec.contractNumber) + "', startDate = " + Helper.ToDbDateTime(rec.startDate) + ", endDate = " + Helper.ToDbDateTime(rec.endDate) + ", poNumber = '" +
Helper.ToDbString(rec.poNumber) + "' WHERE guid = '" + rec.guid + "'";
using (TargetEntities targetDb = new TargetEntities("NetworkCafeEntities"))
{
targetDb.Database.ExecuteSqlCommand(sql);
targetDb.SaveChanges();
}
return(contractData);
}
public string GetNetworkInformationContracts(string deviceVendor)
{
string json = "";
using (TargetEntities targetDb = new TargetEntities("NetworkCafeEntities"))
{
var sql = @"SELECT MasterDevice.HostName, ContractV2.guid, ContractV2.licenses, ContractV2.startDate, ContractV2.notes,ContractV2.endDate, ContractV2.contractNumber, ContractV2.poNumber,
MasterDevice.Site, MasterDevice.DeviceModel, MasterDevice.DeviceVendor, MasterDevice.DeviceLocation, MasterDevice.SerialNumber, MasterDevice.DeviceGUID
FROM MasterDevice LEFT OUTER JOIN ContractV2 ON MasterDevice.DeviceGUID = ContractV2.deviceGuid
WHERE ContractV2.guid is not null AND DeviceVendor = '" + deviceVendor + "'";
var recs = targetDb.Database.SqlQuery <ContractData>(sql).ToList();
json = Newtonsoft.Json.JsonConvert.SerializeObject(recs);
}
return(json);
}
private IRestResponse EstablishInfoBloxConnection(string uri, string Hostname)
{
try
{
string InfoBloxRestAPIUser = "";
string InfoBloxRestAPIPass = "";
using (TargetEntities targetDb = new TargetEntities("NetworkCafeEntities"))
{
InfoBloxRestAPIUser = targetDb.Database.SqlQuery <string>("Select SettingValue From SiteSettings Where SettingName = 'InfoBloxRestAPIUser'").FirstOrDefault();
InfoBloxRestAPIPass = targetDb.Database.SqlQuery <string>("Select SettingValue From SiteSettings Where SettingName = 'InfoBloxRestAPIPass'").FirstOrDefault();
}
Helper.LogError("establishInfoBloxConnection() InfoBloxRestAPIUser="******"/" + InfoBloxRestAPIPass);
ServicePointManager.Expect100Continue = true;
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
string url = "Https://" + Hostname;
RestClient client = new RestClient();
client.Authenticator = new HttpBasicAuthenticator(InfoBloxRestAPIUser, InfoBloxRestAPIPass);
client.BaseUrl = new Uri(url);
ServicePointManager.ServerCertificateValidationCallback += (sender, certificate, chain, sslPolicyErrors) => true;
var request = new RestRequest();
request.Resource = uri;
request.RequestFormat = DataFormat.Json;
IRestResponse response = client.Execute(request);
if (response != null && response.ErrorMessage != null && response.StatusDescription != null && (response.ErrorMessage.Length > 5 || response.StatusDescription.Length > 5))
{
Helper.LogError("EstablishConnection() " + response.ErrorMessage + "\r\n" + response.StatusDescription);
}
return(response);
}
catch (Exception ex)
{
Helper.LogError("EstablishConnection() " + ex);
}
return(null);
}
public List <VMPhoneBook> GetVMResultsObject(string searchString, string searchBy)
{
var likeClause = "";
searchString = searchString.ToLower();
if (searchString.Contains(","))
{
var words = searchString.Split(',').ToList();
if (searchBy == "ipv4addr")
{
foreach (var word in words)
{
likeClause += " OR (Mgt_IP LIKE '%" + word.Replace("'", "''") + "%' OR Prod_IP LIKE '%" + word.Replace("'", "''") + "%') ";
}
}
else
{
foreach (var word in words)
{
likeClause += " OR ESXi_Host LIKE '%" + word.Replace("'", "''") + "%' ";
}
}
}
else
{
if (searchBy == "ipv4addr")
{
likeClause = " OR (Mgt_IP LIKE '%" + searchString.Replace("'", "''") + "%' OR Prod_IP LIKE '%" + searchString.Replace("'", "''") + "%') ";
}
else
{
likeClause = " OR ESXi_Host LIKE '%" + searchString.Replace("'", "''") + "%' ";
}
}
using (TargetEntities targetDb = new TargetEntities("NetworkCafeEntities"))
{
var sql = "SELECT * FROM vmPhoneBook WHERE ServerName like '%" + searchString + "%' " + likeClause;
var results = targetDb.Database.SqlQuery <VMPhoneBook>(sql).ToList();
return(results);
}
}
public string GetVIPName(string jobGuid, string searchString)
{
using (TargetEntities Db = new TargetEntities("F5DataEntities"))
{
var results = Db.Database.SqlQuery <f5SearchResults>("SELECT DeviceData.name AS F5Name, VIP.name AS VIPNAME, REPLACE(VIP.destination,'/Common/','') AS VIPIP, Pool.name AS PoolName, PoolMember.Name AS NodeName, PoolMember.address AS NodeIP " +
"FROM DeviceData INNER JOIN " +
"JobLog ON DeviceData.jobguid = JobLog.guid INNER JOIN " +
"VIP ON DeviceData.jobguid = VIP.jobguid AND DeviceData.hostname = VIP.f5HostName INNER JOIN " +
"Pool ON VIP.jobguid = Pool.jobguid AND VIP.f5HostName = Pool.f5HostName AND VIP.pool = Pool.fullpath INNER JOIN " +
"PoolMember ON Pool.guid = PoolMember.PoolGuid " +
"WHERE(DeviceData.failoverState = N'active') " +
"AND(DeviceData.selfDevice = N'true') " +
"AND(JobLog.guid = '" + jobGuid + "' " +
"And(VIP.name LIKE '%" + searchString + "%') )").ToList();
var json = new JavaScriptSerializer().Serialize(results);
return(json);
}
}
public List <f5SearchResults> GetVIPResultsObject(string searchString, string searchBy)
{
var likeClause = "";
searchString = searchString.ToLower();
if (searchString.Contains(","))
{
var words = searchString.Split(',').ToList();
foreach (var word in words)
{
likeClause += " PoolMember.Name LIKE '%" + searchString.Replace("'", "''") + "%' OR ";
}
likeClause = likeClause.Substring(0, likeClause.Length - 3);
}
else
{
likeClause = " PoolMember.Name LIKE '%" + searchString.Replace("'", "''") + "%' ";
}
// get lastest job guid in f5data database
var jobGuid = "";
using (TargetEntities targetDb = new TargetEntities("F5DataEntities"))
{
jobGuid = targetDb.Database.SqlQuery <string>("SELECT CAST(guid AS varchar(250)) AS guid FROM JobLog ORDER BY endTime DESC").FirstOrDefault();
var sql = "SELECT DeviceData.name AS F5Name, VIP.name AS VIPNAME, REPLACE(VIP.destination,'/Common/','') AS VIPIP, Pool.name AS PoolName, PoolMember.Name AS NodeName,PoolMember.address AS NodeIP " +
"FROM DeviceData INNER JOIN " +
"JobLog ON DeviceData.jobguid = JobLog.guid INNER JOIN " +
"VIP ON DeviceData.jobguid = VIP.jobguid AND DeviceData.hostname = VIP.f5HostName INNER JOIN " +
"Pool ON VIP.jobguid = Pool.jobguid AND VIP.f5HostName = Pool.f5HostName AND VIP.pool = Pool.fullpath INNER JOIN " +
"PoolMember ON Pool.guid = PoolMember.PoolGuid " +
"WHERE (DeviceData.failoverState = N'active') " +
"AND (DeviceData.selfDevice = N'true') " +
"AND (JobLog.guid = '" + jobGuid + "' AND (" + likeClause + ") )";
var results = targetDb.Database.SqlQuery <f5SearchResults>(sql).ToList();
return(results);
}
}
public string DeleteRecord(string ids_, int pageTemplateId)
{
try
{
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
var exe = "DELETE FROM " + pageTemplate.TableName + " WHERE " + pageTemplate.PrimaryKey + " IN (" + ids_ + ")";
targetDb.Database.ExecuteSqlCommand(exe);
targetDb.SaveChanges();
}
return("");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string GetCustomOptions(int pageTemplateId, int columnDefId)
{
string json = "";
PageTemplate pageTemplate = SessionService.PageTemplate(pageTemplateId);
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
List <ValueText> recs = targetDb.Database.SqlQuery <ValueText>("SELECT OptionValue AS ValueField, OptionText AS TextField FROM CustomOption WHERE ColumnDefId = " + columnDefId + " ORDER BY OptionText").ToList();
json = Newtonsoft.Json.JsonConvert.SerializeObject(recs);
if (json != null && json.Length > 2)
{
json = "[{ \"ValueField\":\"\", \"TextField\":\"\"}," + json.Substring(1, json.Length - 1);
}
}
return(json);
}
//
public string GetContract(string guid)
{
string json = "";
using (TargetEntities targetDb = new TargetEntities("NetworkCafeEntities"))
{
StringBuilder sb = new StringBuilder();
var sql = @"SELECT top 1 * FROM ContractV2 WHERE guid = @guid FOR JSON AUTO, INCLUDE_NULL_VALUES";
var recs = targetDb.Database.SqlQuery <string>(sql, new SqlParameter("guid", guid)).ToList();
foreach (var rec in recs)
{
sb.Append(rec);
}
var result = sb.ToString();
if (result.Length > 1)
{
result = result.Substring(1, result.Length - 1);
result = result.Substring(0, result.Length - 1);
}
json = result;
}
return(json);
}
public string GetSortColumnOptionsByName(string tableName, int dbEntityId)
{
StringBuilder sb = new StringBuilder();
var json = "";
sb.Append("SELECT c.name + ' ASC' AS ValueField, c.name + ' Ascending' AS TextField FROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id WHERE o.type = 'U' AND o.name = '" + tableName + "' ");
sb.Append("UNION ");
sb.Append("SELECT c.name + ' DESC' AS ValueField, c.name + ' Descending' AS TextField FROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id WHERE o.type = 'U' AND o.name = '" + tableName + "' ");
sb.Append("ORDER BY ValueField");
var dbEntity = SessionService.DbEntity(dbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
var recs = targetDb.Database.SqlQuery <ValueText>(sb.ToString());
json = Newtonsoft.Json.JsonConvert.SerializeObject(recs);
json = "[{ \"ValueField\":\"\", \"TextField\":\"\"}," + json.Substring(1, json.Length - 1);
return(json);
}
}
public string GetFormData(int pageTemplateId = 0, string recordId = "", string layoutType = "")
{
try
{
string tableName = "";
string primaryKey = "";
string json = "";
StringBuilder sbJsonFields = new StringBuilder();
StringBuilder sbDbFields = new StringBuilder();
StringBuilder sb = new StringBuilder();
StringBuilder sbLinkNew = new StringBuilder();
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
tableName = pageTemplate.TableName;
primaryKey = pageTemplate.PrimaryKey;
if (recordId.Length > 0 && recordId != "0")
{
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
if (pageTemplate.PrimaryKeyType == "TEXT")
{
recordId = "'" + recordId + "'";
}
StringBuilder sbSelect = new StringBuilder();
StringBuilder sbFrom = new StringBuilder();
sbSelect.Append("SELECT " + tableName + "." + primaryKey + " AS " + tableName + "_" + primaryKey);
sbFrom.Append(" FROM " + tableName);
var columnDefs = SessionService.ColumnDefs(pageTemplateId);
foreach (var columnDef in columnDefs.Where(w => !w.IsPrimary))
{
if (layoutType == "View" && columnDef.LookupTable.Length > 0 && columnDef.ValueField.Length > 0 && columnDef.TextField.Length > 0)
{
if (columnDef.IsEncrypted) // decrypt the value
{
var encryptedValue = targetDb.Database.SqlQuery <string>("SELECT " + columnDef.ColumnName + " FROM " + tableName + " WHERE " + tableName + "." + primaryKey + " = " + recordId).FirstOrDefault();
var decryptedValue = Crypto.Decrypt(encryptedValue);
sbSelect.Append(", '" + decryptedValue.Replace("'", "''") + "' AS " + tableName + "_" + columnDef.ColumnName);
}
else if (columnDef.ElementType == "MultiSelect")
{
// get items and inject into select statement
var lookupValue = targetDb.Database.SqlQuery <string>("SELECT " + columnDef.ColumnName + " FROM " + tableName + " WHERE " + primaryKey + " = " + recordId).FirstOrDefault();
if (lookupValue != null && lookupValue.Length > 0)
{
var injectSql = "SELECT STUFF((SELECT ', ' + " + columnDef.TextField + " FROM (SELECT " + columnDef.TextField + " FROM " + columnDef.LookupTable + " WHERE " + columnDef.ValueField + " IN (" + lookupValue + ")) AS T FoR XML PATH('')), 1, 1, '') AS InjectValue";
var injectValue = targetDb.Database.SqlQuery <string>(injectSql).FirstOrDefault();
if (injectValue != null)
{
sbSelect.Append(", '" + injectValue.Replace("'", "''") + "' AS " + tableName + "_" + columnDef.ColumnName);
}
}
}
else
{
sbSelect.Append(", " + columnDef.LookupTable + "." + columnDef.TextField + " AS " + tableName + "_" + columnDef.ColumnName);
// append fields from the lookup table
var lookupColumnDefs = SessionService.ColumnDefs(pageTemplate.DbEntityId, columnDef.LookupTable);
foreach (var lookupColumnDef in lookupColumnDefs)
{
sbSelect.Append(", " + columnDef.LookupTable + "." + lookupColumnDef.ColumnName + " AS " + columnDef.LookupTable + "_" + lookupColumnDef.ColumnName);
}
sbFrom.Append(" LEFT JOIN " + columnDef.LookupTable + " ON " + columnDef.LookupTable + "." + columnDef.ValueField + " = " + tableName + "." + columnDef.ColumnName + " ");
}
}
else
{
if (columnDef.IsEncrypted) // decrypt the value
{
var encryptedValue = targetDb.Database.SqlQuery <string>("SELECT " + columnDef.ColumnName + " FROM " + tableName + " WHERE " + tableName + "." + primaryKey + " = " + recordId).FirstOrDefault();
var decryptedValue = Crypto.Decrypt(encryptedValue);
sbSelect.Append(", '" + decryptedValue.Replace("'", "''") + "' AS " + tableName + "_" + columnDef.ColumnName);
}
else
{
sbSelect.Append(", " + tableName + "." + columnDef.ColumnName + " AS " + tableName + "_" + columnDef.ColumnName);
}
}
}
var recordSql = sbSelect.ToString() + sbFrom.ToString() + " WHERE " + tableName + "." + primaryKey + " = " + recordId + " FOR JSON PATH";
var recs = targetDb.Database.SqlQuery <string>(recordSql).ToList();
json = "";
foreach (var rec in recs)
{
json += rec;
}
if (json.Length > 2)
{
json = json.Substring(1, json.Length - 2);
}
return(json);
}
}
else // recordId = 0
{
// get defaults for new record
sb.Clear();
sb.Append("{\"" + tableName + "_" + primaryKey + "\":0");
List <ColumnDef> columnDefs = new List <ColumnDef>();
columnDefs = SessionService.ColumnDefs(pageTemplateId).Where(w => w.IsRequired == true || (w.DefaultValue != null)).ToList();
foreach (var columnDef in columnDefs)
{
if (columnDef.ElementType == "Note" || columnDef.ElementType == "FileAttachment")
{
sbLinkNew.Append("DELETE FROM " + columnDef.ElementType + " WHERE RecordId = -" + (columnDef.ColumnDefId + SessionService.UserId) + ";");
}
var defaultValue = columnDef.DefaultValue;
if (columnDef.OverideValue != null && columnDef.OverideValue.Length > 0)
{
defaultValue = columnDef.OverideValue;
}
if (columnDef.IsRequired || (defaultValue != null && defaultValue.Length > 0))
{
sb.Append(",\"" + tableName + "_" + columnDef.ColumnName + "\":");
if (defaultValue != null && defaultValue.Length > 0)
{
if (columnDef.DataType == "NUMBER")
{
sb.Append(Helper.ToInt32(defaultValue).ToString());
// add lookup display value
if (columnDef.LookupTable != null && columnDef.LookupTable.Length > 0 && Helper.ToInt32(defaultValue) > 0)
{
string lookUpFields = "";
if (columnDef.TextField.Contains(","))
{
lookUpFields = "(";
string[] fields = columnDef.TextField.Split(new char[] { ',' });
foreach (var field in fields)
{
if (lookUpFields.Length < 3)
{
lookUpFields += columnDef.LookupTable + "." + field;
}
else
{
lookUpFields += " + ' - ' + " + columnDef.LookupTable + "." + field;
}
}
lookUpFields += ")";
}
else
{
lookUpFields = "ISNULL(" + columnDef.LookupTable + "." + columnDef.TextField + ",'') ";
}
var displayValue = DataService.GetStringValue("SELECT " + lookUpFields + " FROM " + columnDef.LookupTable + " WHERE " + columnDef.ValueField + " = " + defaultValue);
sb.Append(",\"" + tableName + "_" + columnDef.ColumnName + "_\":\"" + displayValue.Replace("\"", "\"\"") + "\"");
}
}
else if (columnDef.DataType == "DECIMAL" || columnDef.DataType == "CURRENCY")
{
sb.Append(Helper.ToDecimal(defaultValue).ToString());
}
else if (columnDef.DataType == "TEXT" || columnDef.DataType == "MAXTEXT")
{
sb.Append("\"" + defaultValue.ToString() + "\"");
}
else if (columnDef.DataType == "BOOLEAN")
{
if (defaultValue.ToLower() == "true" || defaultValue == "1")
{
sb.Append("true");
}
else
{
sb.Append("false");
}
}
else if (columnDef.DataType == "DATE")
{
if (defaultValue == "getdate()")
{
sb.Append("\"" + DateTime.Now.ToLongDateString() + "\"");
}
else
{
sb.Append("\"" + Helper.ToDateTime(defaultValue).ToLongDateString() + "\"");
}
}
else
{
sb.Append("\"" + defaultValue.ToString() + "\"");
}
}
else if (columnDef.IsRequired == true)
{
if (columnDef.DataType == "NUMBER")
{
sb.Append("0");
}
else if (columnDef.DataType == "DECIMAL" || columnDef.DataType == "CURRENCY")
{
sb.Append("0");
}
else if (columnDef.DataType == "TEXT" || columnDef.DataType == "MAXTEXT")
{
sb.Append("\"\"");
}
else if (columnDef.DataType == "BOOLEAN")
{
sb.Append("false");
}
else if (columnDef.DataType == "DATE")
{
sb.Append("\"" + DateTime.Now.ToLongDateString() + "\"");
}
else
{
sb.Append("\"\"");
}
}
else
{
if (columnDef.DataType == "NUMBER")
{
sb.Append("0");
}
else if (columnDef.DataType == "DECIMAL" || columnDef.DataType == "CURRENCY")
{
sb.Append("0");
}
else if (columnDef.DataType == "TEXT" || columnDef.DataType == "MAXTEXT")
{
sb.Append("\"\"");
}
else if (columnDef.DataType == "BOOLEAN")
{
sb.Append("false");
}
else if (columnDef.DataType == "DATE")
{
sb.Append("\"" + DateTime.Now.ToLongDateString() + "\"");
}
else
{
sb.Append("\"\"");
}
}
}
}
sb.Append("}");
json = sb.ToString();
//if (sbLinkNew.Length > 0)
//{
// DataService.Execute(sbLinkNew.ToString());
//}
}
return(json);
}
catch (Exception ex)
{
Helper.LogError(ex);
}
return("");
}
public string GetDetail(Guid jobGuid, string f5HostName, string vip)
{
List <VIP> vIPList = new List <VIP>();
using (TargetEntities Db = new TargetEntities("F5DataEntities"))
{
if (vip != "undefined")
{
vIPList = Db.Database.SqlQuery <VIP>("SELECT * FROM VIP WHERE jobguid = '" + jobGuid + "' AND f5HostName = '" + f5HostName + "' AND name = '" + vip + "' ORDER BY name").ToList();
}
else
{
vIPList = Db.Database.SqlQuery <VIP>("SELECT * FROM VIP WHERE jobguid = '" + jobGuid + "' AND f5HostName = '" + f5HostName + "' ORDER BY name").ToList();
}
foreach (var Vip in vIPList)
{
Vip.vipStatus = Db.Database.SqlQuery <VIPStat>("SELECT TOP 1 * FROM VIPStats WHERE VIPGuid = '" + Vip.guid + "'").FirstOrDefault();
if (Vip.pool != "" && Vip.pool != null)
{
string poolname = Vip.pool.Replace("/Common/", "");
Pool vipPool = Db.Database.SqlQuery <Pool>("SELECT TOP 1 * FROM Pool WHERE jobguid = '" + jobGuid + "' AND f5HostName = '" + f5HostName + "' AND name = '" + poolname + "' ORDER BY name").FirstOrDefault();
vipPool.poolMemberList = Db.Database.SqlQuery <PoolMember>("SELECT * FROM PoolMember WHERE PoolGuid = '" + vipPool.guid + "'").ToList();
if (vipPool.monitor != null)
{
vipPool.monitor = vipPool.monitor.Replace("/Common/", "");
}
foreach (var item in vipPool.poolMemberList)
{
if (item.address.Contains('%'))
{
char[] chars = { '%' };
string[] ipVar = item.address.Split(chars);
item.IP = ipVar[0];
item.routeDomain = ipVar[1];
}
else
{
item.IP = item.address;
}
}
Vip.poolInfo = vipPool;
}
if (Vip.rules != null && Vip.rules != "")
{
string rule = Vip.rules.Replace("/Common/", ";");
Vip.rules = rule;
string[] vipRules = rule.Split(';');
List <Irule> ruleList = new List <Irule>();
foreach (var item in vipRules)
{
if (item != "" && item != null)
{
Irule viprule = Db.Database.SqlQuery <Irule>("SELECT TOP 1 * FROM Irule WHERE jobguid = '" + jobGuid + "' AND name = '" + item + "' AND f5HostName = '" + f5HostName + "'").FirstOrDefault();
ruleList.Add(viprule);
}
}
Vip.iRuleList = ruleList;
}
if (Vip.destination != null && Vip.destination != "")
{
Vip.destination = Vip.destination.Replace("/Common/", "");
int ipv6count = Vip.destination.Count(a => a == ':');
if (ipv6count > 1)
{
if (Vip.destination.Contains("%"))
{
char[] chars = { '%', '.' };
string[] ipVar = Vip.destination.Split(chars);
Vip.IP = ipVar[0];
Vip.routeDomain = ipVar[1];
Vip.port = ipVar[2];
}
else
{
char[] chars = { '.' };
string[] ipVar = Vip.destination.Split(chars);
Vip.IP = ipVar[0];
Vip.port = ipVar[1];
}
}
else
{
if (Vip.destination.Contains("%"))
{
char[] chars = { '%', ':' };
string[] ipVar = Vip.destination.Split(chars);
Vip.IP = ipVar[0];
Vip.routeDomain = ipVar[1];
Vip.port = ipVar[2];
}
else
{
char[] chars = { ':' };
string[] ipVar = Vip.destination.Split(chars);
Vip.IP = ipVar[0];
Vip.port = ipVar[1];
}
}
}
//VipProfilesDetail vpd = new VipProfilesDetail();
//int clientsideCount = dc.VipProfilesDetails.Where(a => a.vipguid == Vip.guid && a.context == "clientside").Count();
//if (clientsideCount > 0)
//{
// vpd = dc.VipProfilesDetails.Where(a => a.vipguid == Vip.guid && a.context == "clientside").FirstOrDefault();
// List<ClientSSL> vipssl = dc.ClientSSLs.Where(a => a.name == vpd.name && a.jobguid == jobguid && a.f5HostName == f5HostName).ToList();
// Vip.sslList = vipssl;
//}
//int persisttest = dc.VipPersists.Where(a => a.VIPguid == Vip.guid).Count();
//if (persisttest > 0)
//{
// VIPPersistData vipPersistData = new VIPPersistData();
// if (dc.Database.SqlQuery<VIPPersistData>("SELECT dbo.VipPersist.name AS PersistName, dbo.VIPPersistDetails.persistType, dbo.VIPPersistDetails.timeout " +
// "FROM dbo.JobLog INNER JOIN " +
// "dbo.DeviceData ON dbo.JobLog.guid = dbo.DeviceData.jobguid INNER JOIN " +
// "dbo.VIP ON dbo.DeviceData.jobguid = dbo.VIP.jobguid AND dbo.DeviceData.hostname = dbo.VIP.f5HostName INNER JOIN " +
// "dbo.VipPersist ON dbo.VIP.guid = dbo.VipPersist.VIPguid INNER JOIN " +
// "dbo.VIPPersistDetails ON dbo.VipPersist.f5HostName = dbo.VIPPersistDetails.f5hostname AND dbo.VipPersist.name = dbo.VIPPersistDetails.name " +
// "WHERE(dbo.DeviceData.failoverState = N'active') AND(dbo.DeviceData.selfDevice = N'true') AND(dbo.JobLog.guid = '" + jobguid + "') AND " +
// "(dbo.VIP.name = N'" + Vip.name + "')").FirstOrDefault() != null)
// {
// vipPersistData = dc.Database.SqlQuery<VIPPersistData>("SELECT dbo.VipPersist.name AS PersistName, dbo.VIPPersistDetails.persistType, dbo.VIPPersistDetails.timeout " +
// "FROM dbo.JobLog INNER JOIN " +
// "dbo.DeviceData ON dbo.JobLog.guid = dbo.DeviceData.jobguid INNER JOIN " +
// "dbo.VIP ON dbo.DeviceData.jobguid = dbo.VIP.jobguid AND dbo.DeviceData.hostname = dbo.VIP.f5HostName INNER JOIN " +
// "dbo.VipPersist ON dbo.VIP.guid = dbo.VipPersist.VIPguid INNER JOIN " +
// "dbo.VIPPersistDetails ON dbo.VipPersist.f5HostName = dbo.VIPPersistDetails.f5hostname AND dbo.VipPersist.name = dbo.VIPPersistDetails.name " +
// "WHERE(dbo.DeviceData.failoverState = N'active') AND(dbo.DeviceData.selfDevice = N'true') AND(dbo.JobLog.guid = '" + jobguid + "') AND " +
// "(dbo.VIP.name = N'" + Vip.name + "')").FirstOrDefault();
// Vip.PersistName = vipPersistData.PersistName;
// Vip.persistType = vipPersistData.persistType;
// Vip.PersistTimeOut = vipPersistData.timeout;
// }
//}
}
}
var json = new JavaScriptSerializer().Serialize(vIPList);
return(json);
}
public string GetImportData(int dbEntityId, string tableName)
{
var sql = "";
var insertStatement = "";
var insertSqlStatement = "";
var buildSql = "";
StringBuilder sb = new StringBuilder();
sb.AppendLine("TRUNCATE TABLE " + tableName);
sb.AppendLine("GO");
sb.AppendLine("SET IDENTITY_INSERT " + tableName + " ON;");
sb.AppendLine("GO");
var dbEntity = SessionService.DbEntity(dbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
// get insert statement
sql = "SELECT (SELECT 'INSERT INTO " + tableName + "(' + STUFF((SELECT ', ' + ColumnName FROM (SELECT c.Name AS ColumnName FROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id WHERE o.name = '" + tableName + "' AND c.is_computed = 0 ) AS T FoR XML PATH('')), 1, 1, '') + ') VALUES' AS ColumnNames)";
insertStatement = targetDb.Database.SqlQuery <string>(sql).FirstOrDefault();
sb.AppendLine(insertStatement);
// get insert SQL statement
sql = @"
SELECT (SELECT 'SELECT ''(''' + STUFF((SELECT ', ' + ValuesString FROM (
SELECT
CASE
WHEN (is_identity = 1) THEN ' + CAST(' + c.Name + ' AS varchar) + '''
WHEN (system_type_id = 35) THEN ''''''' + REPLACE(CAST(ISNULL(' + c.Name + ','''') AS varchar(max)), '''''''','''''''''''') + '''''''
WHEN (system_type_id = 36) THEN ''''''' + CAST(ISNULL(' + c.Name + ','''') AS nvarchar(36)) + '''''''
WHEN (system_type_id = 40) THEN ''''''' + CAST(ISNULL(' + c.Name + ','''') AS varchar) + '''''''
WHEN (system_type_id = 42) THEN 'datetime2'
WHEN (system_type_id = 48) THEN ''' + ISNULL(' + c.Name + ',0) + '''
WHEN (system_type_id = 52) THEN ''' + CAST(ISNULL(' + c.Name + ', 0) AS varchar) + '''
WHEN (system_type_id = 56) THEN ''' + CAST(ISNULL(' + c.Name + ', 0) AS varchar) + '''
WHEN (system_type_id = 58) THEN 'smalldatetime'
WHEN (system_type_id = 59) THEN ''' + ISNULL(' + c.Name + ',0) + '''
WHEN (system_type_id = 60) THEN ''' + ISNULL(' + c.Name + ',0) + '''
WHEN (system_type_id = 61) THEN ''''''' + CAST(ISNULL(' + c.Name + ','''') AS varchar) + '''''''
WHEN (system_type_id = 62) THEN ''' + ISNULL(' + c.Name + ',0) + '''
WHEN (system_type_id = 98) THEN 'sql_variant'
WHEN (system_type_id = 99) THEN ''''''' + ISNULL(' + c.Name + ','''') + '''''''
WHEN (system_type_id = 104) THEN ''' + CAST(ISNULL(' + c.Name + ',0) AS varchar) + '''
WHEN (system_type_id = 106) THEN ''' + ISNULL(' + c.Name + ',0) + '''
WHEN (system_type_id = 108) THEN ''' + ISNULL(' + c.Name + ',0) + '''
WHEN (system_type_id = 122) THEN ''' + ISNULL(' + c.Name + ',0) + '''
WHEN (system_type_id = 127) THEN ''' + ISNULL(' + c.Name + ',0) + '''
WHEN (system_type_id = 165) THEN 'varbinary'
WHEN (system_type_id = 167) THEN ''''''' + REPLACE(ISNULL(' + c.Name + ',''''), '''''''','''''''''''') + '''''''
WHEN (system_type_id = 173) THEN 'binary'
WHEN (system_type_id = 175) THEN ''''''' + ISNULL(' + c.Name + ','''') + '''''''
WHEN (system_type_id = 189) THEN 'timestamp'
WHEN (system_type_id = 231) THEN ''''''' + REPLACE(CAST(ISNULL(' + c.Name + ','''') AS varchar(max)), '''''''','''''''''''') + '''''''
WHEN (system_type_id = 239) THEN ''''''' + ISNULL(' + c.Name + ','''') + '''''''
WHEN (system_type_id = 241) THEN ''''''' + ISNULL(' + c.Name + ','''') + '''''''
END
AS ValuesString
FROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id WHERE o.name = 'PageTemplate' AND c.is_computed = 0
) AS T FoR XML PATH('')), 1, 1, '') + '), '' FROM PageTemplate' AS SqlString)
";
sql = sql.Replace("PageTemplate", tableName);
insertSqlStatement = targetDb.Database.SqlQuery <string>(sql).FirstOrDefault();
insertSqlStatement = insertSqlStatement.Replace("SELECT '(' ''' + CAST(ISNULL(guid", "SELECT '(''' + CAST(ISNULL(guid");
var recs = targetDb.Database.SqlQuery <string>(insertSqlStatement);
foreach (var rec in recs)
{
sb.AppendLine(rec);
}
buildSql = sb.ToString();
buildSql = buildSql.Substring(0, buildSql.Length - 4);
}
sb.Clear();
sb.AppendLine(buildSql);
sb.AppendLine("GO");
sb.AppendLine("SET IDENTITY_INSERT " + tableName + " OFF;");
sb.AppendLine("GO");
return(sb.ToString());
}
public string GetOptionsByColumnDefId(int pageTemplateId, int columnDefId)
{
var columnDef = SessionService.ColumnDefs(pageTemplateId).Where(w => w.ColumnDefId == columnDefId).FirstOrDefault();
string orderBy = "";
if (columnDef.OrderField.Length > 3)
{
orderBy = " ORDER BY " + columnDef.OrderField;
}
string textField = "";
if (columnDef.TextField.Contains(","))
{
var columns = columnDef.TextField.Split(new char[] { ',' });
foreach (var columnName in columns)
{
var dataType = SessionService.DataType(pageTemplateId, columnName);
if (dataType == "TEXT")
{
if (textField.Length == 0)
{
textField = columnName;
}
else
{
textField = textField + " + ' - ' + " + columnName;
}
}
else
{
if (textField.Length == 0)
{
textField = "CAST(" + columnName + " AS varchar)";
}
else
{
textField = textField + " + ' - ' + " + "CAST(" + columnName + " AS varchar)";
}
}
}
}
else
{
textField = columnDef.TextField;
}
string sql = "SELECT CAST(" + columnDef.ValueField + " AS varchar) AS ValueField, (" + textField + ") AS TextField FROM " + columnDef.LookupTable + " " + orderBy;
string json = "";
PageTemplate pageTemplate = SessionService.PageTemplate(pageTemplateId);
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities targetDb = new TargetEntities())
{
targetDb.Database.Connection.ConnectionString = dbEntity.ConnectionString;
var recs = targetDb.Database.SqlQuery <ValueText>(sql);
json = Newtonsoft.Json.JsonConvert.SerializeObject(recs);
if (json.Length < 2)
{
json = "[];";
}
}
if (columnDef.AddBlankOption)
{
json = "[{ \"ValueField\":\"\", \"TextField\":\"\"}," + json.Substring(1, json.Length - 1);
}
return(json);
}
public static string GetServerSideRecords(int skip, int take, int page, int pageSize, List <GridSort> gridSorts = null, GridFilters filter = null, int pageTemplateId = 0)
{
//try
//{
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
string tableName = "";
string primaryKey = "";
string innerSelect = "";
string innerJoin = "";
string outerSelect = "";
string sortColumns = "";
Dictionary <string, string> filterMap = new Dictionary <string, string>();
Dictionary <string, string> sort1Map = new Dictionary <string, string>();
Dictionary <string, string> sort2Map = new Dictionary <string, string>();
tableName = pageTemplate.TableName;
primaryKey = pageTemplate.PrimaryKey;
TableFilterSort tfs = SessionService.TableFilterSort(pageTemplateId);
innerSelect = tfs.InnerSelect;
innerJoin = tfs.InnerJoin;
outerSelect = tfs.OuterSelect;
sortColumns = tfs.SortColumns;
filterMap = tfs.FilterMap;
sort1Map = tfs.Sort1Map;
sort2Map = tfs.Sort2Map;
// WHERE
string whereFilter = (HttpContext.Current.Session["WhereFilter" + pageTemplateId] != null) ? HttpContext.Current.Session["WhereFilter" + pageTemplateId].ToString() : "";
if ((filter != null && filter.Filters != null))
{
string buildFilter = "";
string buildFilter2 = "";
string fldName = "";
string oper = "";
string fldValue = "";
if ((filter != null && (filter.Filters != null && filter.Filters.Count > 0)))
{
HttpContext.Current.Session["totalCount" + pageTemplateId] = null;
var filters = filter.Filters;
string logic = filter.Logic;
for (var i = 0; i < filters.Count; i++)
{
if (filters[i].Field == null && filters[i].Filters != null)
{
if (buildFilter2.Length > 0)
{
buildFilter2 += " AND ";
}
buildFilter2 += "(";
var andOr = filters[i].Logic;
for (var x = 0; x < filters[i].Filters.Count; x++)
{
fldName = filters[i].Filters[x].Field;
oper = filters[i].Filters[x].Operator.ToLower();
fldValue = filters[i].Filters[x].Value;
buildFilter2 += BuildFilter(fldName, oper, fldValue, filterMap) + " " + andOr + " ";
}
buildFilter2 = buildFilter2.Substring(0, buildFilter2.Length - 4);
buildFilter2 += ") ";
continue;
}
fldName = filters[i].Field;
oper = filters[i].Operator.ToLower();
fldValue = filters[i].Value;
buildFilter += BuildFilter(fldName, oper, fldValue, filterMap) + " " + logic + " ";
}
}
if (buildFilter.Length > 0 || buildFilter2.Length > 0)
{
if (buildFilter.Length > 0)
{
buildFilter = buildFilter.Substring(0, buildFilter.Length - 4);
buildFilter = " (" + buildFilter + ") ";
}
if (buildFilter2.Length > 0)
{
if (buildFilter.Length > 0)
{
buildFilter += " AND ";
}
buildFilter += " (" + buildFilter2 + ") ";
}
}
HttpContext.Current.Session["WhereFilter" + pageTemplateId] = buildFilter;
}
//--------- ORDER BY
if (gridSorts != null && gridSorts.Count > 0)
{
HttpContext.Current.Session["SortField2" + pageTemplateId] = null;
foreach (var gridSort in gridSorts)
{
if ((gridSort.Field.Contains("_lco") || gridSort.Field.Contains("_tbl")) && sort1Map.ContainsKey(gridSort.Field))
{
HttpContext.Current.Session["SortField1" + pageTemplateId] = sort1Map[gridSort.Field];
HttpContext.Current.Session["SortField2" + pageTemplateId] = sort2Map[gridSort.Field];
HttpContext.Current.Session["SortDir" + pageTemplateId] = gridSort.Dir;
break;
}
else
{
HttpContext.Current.Session["SortField1" + pageTemplateId] = tableName + "." + gridSort.Field;
HttpContext.Current.Session["SortDir" + pageTemplateId] = gridSort.Dir;
break;
}
}
}
// set default sort
if (HttpContext.Current.Session["SortField1" + pageTemplateId] == null && sortColumns.Length > 0)
{
HttpContext.Current.Session["SortDir" + pageTemplateId] = "ASC";
HttpContext.Current.Session["SortField1" + pageTemplateId] = sortColumns;
HttpContext.Current.Session["SortDir" + pageTemplateId] = "";
}
string orderBy1 = " ORDER BY " + HttpContext.Current.Session["SortField1" + pageTemplateId].ToString() + " " + HttpContext.Current.Session["SortDir" + pageTemplateId].ToString() + " ";
if (HttpContext.Current.Session["SortField2" + pageTemplateId] == null)
{
HttpContext.Current.Session["SortField2" + pageTemplateId] = HttpContext.Current.Session["SortField1" + pageTemplateId].ToString();
}
string orderBy2 = " ORDER BY " + HttpContext.Current.Session["SortField2" + pageTemplateId].ToString() + " " + HttpContext.Current.Session["SortDir" + pageTemplateId].ToString() + " ";
if (orderBy1.Length < 14)
{
orderBy1 = "";
orderBy2 = "";
}
// where clase
string whereClause = "";
if (HttpContext.Current.Session["WhereFilter" + pageTemplateId] != null && HttpContext.Current.Session["WhereFilter" + pageTemplateId].ToString().Length > 2)
{
whereClause = " WHERE " + HttpContext.Current.Session["WhereFilter" + pageTemplateId].ToString() + " ";
}
//--------- COUNT()
var totalCount = 0;
if (HttpContext.Current.Session["totalCount" + pageTemplateId] == null)
{
StringBuilder sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) ");
sbCount.Append("FROM " + tableName + " ");
sbCount.Append(innerJoin + " ");
sbCount.Append(whereClause + " ");
var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
using (TargetEntities Db = new TargetEntities())
{
Db.Database.Connection.ConnectionString = dbEntity.ConnectionString;
totalCount = Db.Database.SqlQuery <int>(sbCount.ToString()).FirstOrDefault();
}
}
else
{
totalCount = Convert.ToInt32(HttpContext.Current.Session["totalCount" + pageTemplateId]);
}
//--------- Build SQL
string pk = tableName + "." + primaryKey;
StringBuilder sb = new StringBuilder();
sb.Append("WITH MAIN AS (");
// inner query
sb.Append("SELECT " + innerSelect + " ");
sb.Append("FROM " + tableName + " ");
sb.Append(innerJoin + " ");
sb.Append(whereClause + " ");
sb.Append(orderBy1 + " ");
if (take > 0)
{
sb.Append("OFFSET " + skip + " ROWS FETCH NEXT " + take + " ROWS ONLY ");
}
else
{
sb.Append("OFFSET 0 ROWS FETCH NEXT 1000000 ROWS ONLY ");
}
sb.Append(") ");
// outer query
sb.Append("SELECT " + outerSelect + " FROM " + tableName + " INNER JOIN MAIN ON " + pk + " = MAIN." + primaryKey + " ");
sb.Append(orderBy2 + " ");
string sql = sb.ToString();
if (totalCount == 0)
{
return("{\"total\":0,\"data\":[] }");
}
var json = "";
if (take > 0)
{
json = "{\"total\":" + totalCount + ",\"data\":" + DataService.GetJsonFromSQL(pageTemplate.DbEntityId, sql) + "}";
}
else
{
json = DataService.GetJsonFromSQL(pageTemplate.DbEntityId, sql);
}
json = json.Replace("\r", "").Replace("\n", "");
return(json);
//}
//catch (Exception ex)
//{
// Helper.LogError(ex.StackTrace);
// return "";
//}
}
