public static void ExportToExcel(Sql sql, string filename)
{
StringBuilder allData = new StringBuilder();
var reader = sql.GetReader();
if (!reader.HasRows)
{
allData.AppendLine("No records");
}
else
{
// write field titles
string str = "";
for (int scan = 0; scan < reader.VisibleFieldCount; scan++)
{
str += "\t" + Fmt.SplitTitleCase(reader.GetName(scan));
}
allData.AppendLine(str.Trim());
foreach (DbDataRecord row in reader)
{
// write field values
str = "";
for (int scan = 0; scan < reader.VisibleFieldCount; scan++)
{
string val = "";
var datatype = reader.GetDataTypeName(scan).ToLower();
if (datatype == "datetime")
{
if (row[scan].ToString().IsNotBlank())
{
val = Fmt.DateTime((DateTime)row[scan]);
}
}
else if (datatype == "bit")
{
if (row[scan].ToString().IsNotBlank())
{
val = Fmt.YesNo(("" + row[scan]).ToLower());
}
}
else
{
val = row[scan].ToString();
}
str += "\t" + TsvCleanup(val);
}
allData.AppendLine(str.Trim());
}
}
reader.Close();
reader.Dispose();
ExportToExcel(allData.ToString(), filename);
}
/// <summary>
/// Return a string value for general display
/// </summary>
/// <returns></returns>
public string OriginalValueToString()
{
if (OriginalValueObject == null || DBNull.Value.Equals(OriginalValueObject))
{
return("");
}
else if (IsDateField)
{
return(Fmt.DateTime((DateTime)OriginalValueObject));
}
return(OriginalValueObject.ToString());
}
/// <summary>
/// Return a string value for general display
/// Changed to 'override' so it can simply be appended to a string or output
/// </summary>
/// <returns></returns>
public override string ToString()
{
if (ValueObject == null || DBNull.Value.Equals(ValueObject))
{
return("");
}
else if (IsDateField)
{
return(Fmt.DateTime((DateTime)ValueObject));
}
return(ValueObject.ToString());
}
/// <summary>
/// write the lg for a given record, or null if log is disabled
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static string ShowModificationLog(ActiveRecord obj)
{
#if ModificationLog
if (!Util.GetSettingBool("UseModificationLog", false))
{
return(null);
}
int pkvalue = obj.ID_Field.ToInt(0);
string tableName = obj.GetTableName();
string ModLogTableName = "ModificationLog";
var sql = new Sql("select top 100 * from ", ModLogTableName.SqlizeName(), " where TableName=", tableName.Sqlize_Text(), " ");
sql.Add(" and RecordID=", pkvalue, "");
sql.Add(" order by updatedate desc");
string res = @"<tr class=""dontprint"">
<td class=label>Change History</td>
<td class=field><a href="""" onclick=""$('.modlog').show();$(this).hide();return false;"">show</a><div class=""modlog"" style=""display:none"">" ;
int scan = 0;
using (DbDataReader dbDataReader = sql.GetReader()) {
foreach (DbDataRecord rsLog in dbDataReader)
{
var person = Person.LoadID((rsLog["PersonID"] + "").ToInt(0));
var UserName = (person != null) ? person.FullName : "not available";
string descr = rsLog["ChangeDescription"].ToString().FmtPlainTextAsHtml();
if (!(descr.Contains("Date Modified changed") && descr.Length < 94)) //eg skip Date Modified changed from "29 Oct 2013 19:49am" to "29 Oct 2013 10:51am"
//descr = descr.Replace("<br>", " | "); // removed and applied nice formatting below (JVB)
{
res += "<b>" + rsLog["ActionType"] + "</b> on " + Fmt.DateTime(rsLog["UpdateDate"] + "") + " by " + UserName + ":<br/> - " + descr + "<br/><br/>";
scan++;
if (scan > 100)
{
res += "more...";
break;
}
}
}
}
res += @"</div>
</td>
</tr>" ;
//todo: remove any old mod logs when drawing a mod log page.
return(res);
#else
return("Not available.");
#endif
}
protected string findnocryptadmin()
{
string result = "";
if (Request["mode"] == "findnocryptadmin")
{
var data = (new Sql("select personID,password, email from person ")).GetDataTable();
Log("Start " + Fmt.DateTime(DateTime.Now) + "<br>");
for (int scan = 0; scan < data.Rows.Count; scan++)
{
DataRow item = data.Rows[scan];
string ps;
string plainPassword = item["password"] + "";
Log(" [" + scan + "]----------- Found [" + item["email"] + "] - plainPassword [" + plainPassword + "]");
if ((ps = Security.DecryptPassword(plainPassword + "")) == "")
{
Log("- insecure [" + plainPassword + "]<br>");
if (Request["ac"] == "encrypt")
{
var newps = Security.CreateSecuredPassword(plainPassword);
Log("--change to [" + newps + "]<br>");
var sql = new Sql("update person set password="******" where personid=", (item["PersonID"]).ToString().Sqlize_Text(), "");
Log("--sqlis [" + sql + "]<br>");
sql.Execute();
}
}
else
{
Log("- secure [" + ps + "]<br>");
}
if (false && scan > 2215)
{
Log("Stop after 15..<br>");
break;
}
}
Log("End " + Fmt.DateTime(DateTime.Now) + "<br>");
}
return(result);
}
public ActionResult LiveChat()
{
/*NOTE: This requires the user to be logged in.... */
// chats the last 20 chats for today
var data = new LiveChatViewModel();
if (UserSession.Person != null)
{
data.EncryptedPersonID = Crypto.EncryptID(UserSession.Person.ID);
data.IsLoggedIn = true;
}
else
{
data.IsLoggedIn = false;
}
var sql = new Sql("select top 20 p.FirstName + ", " ".SqlizeText(), "+ p.lastname as FullName, lc.Post, Convert(varchar, lc.DateAdded,103) as Date, convert(varchar, lc.DateAdded, 108) as Time from LiveChat lc inner join Person p on p.PersonID = lc.PersonID Order by lc.DateAdded desc");
var chatList = sql.LoadPooList <LatestChatList>();
data.ChatList = chatList;
data.ChatRefresh = 2000; //(data.ChatList.Count > 0)?2000:30000;
data.LastUpdate = Fmt.DateTime(DateTime.Now, Fmt.DateTimePrecision.Millisecond);
return(View(data));
}
public ActionResult UpdateLiveChat(string lastUpdate)
{
var sql = new Sql("select p.FirstName + ", " ".SqlizeText(), "+ p.lastname as FullName, lc.Post, Convert(varchar, lc.DateAdded,103) as Date, convert(varchar, lc.DateAdded, 108) as Time from LiveChat lc inner join Person p on p.PersonID = lc.PersonID where lc.DateAdded > ", lastUpdate.SqlizeDate(), " Order by lc.DateAdded desc");
var data = new { ChatList = sql.LoadPooList <LatestChatList>(), ChatRefresh = 2000, LastUpdate = Fmt.DateTime(DateTime.Now, Fmt.DateTimePrecision.Millisecond) };
return(Json(data, JsonRequestBehavior.AllowGet));
}
public dynamic GetEngageModifiedContacts(DateTime since, int limit)
{
var engageContacts = new List <dynamic>();
//Build a new Base Rest Client
var collection = new WebHeaderCollection();
collection.Add("Authorization", string.Format("Engage {0}", APIToken));
const string url = "https://api.ubiquity.co.nz/database/contacts/query/";
var numberOfRecords = Math.Min(limit, 150);
string filterString = "{{\"filterString\": \"[" + GetFieldID("Last Modified") + "] ge '" + Fmt.DateTime(since, Fmt.DateTimePrecision.Second) + "'\", \"sortFields\": [{{ \"column\": \"" + GetFieldID("Last Modified") + "\", \"direction\": \"Ascending\"}}], \"numberOfRecords\": " + numberOfRecords + ", \"startRecord\": {0}}}";
var obj = JsonHelper.Parse("{\"next\": 0}");
do
{
var content = Http.Post(url, string.Format(filterString, obj.next), "Authorization", string.Format("Engage {0}", APIToken));
obj = JsonHelper.Parse(content);
if (obj.selectedContacts.Length == 0)
{
break;
}
foreach (var selectedContact in obj.selectedContacts)
{
engageContacts.Add(selectedContact);
if (engageContacts.Count >= limit)
{
break;
}
}
} while (obj.next < obj.totalContacts && engageContacts.Count < limit);
return(engageContacts);
}
