public DnsDto CreateFreeDnsRecords(string dkimSelector, string domainCheckerPrefix,
string spf, DbManager db = null)
{
if (string.IsNullOrEmpty(dkimSelector))
throw new ArgumentNullException("dkimSelector");
if (string.IsNullOrEmpty(domainCheckerPrefix))
throw new ArgumentNullException("domainCheckerPrefix");
if (string.IsNullOrEmpty(spf))
throw new ArgumentNullException("spf");
string privateKey, publicKey;
DnsChecker.DnsChecker.GenerateKeys(out privateKey, out publicKey);
var domainCheckValue = PasswordGenerator.GenerateNewPassword(16);
var domainCheck = domainCheckerPrefix + ": " + domainCheckValue;
var insertValuesQuery = new SqlInsert(DnsTable.name)
.InColumnValue(AddressTable.Columns.id, 0)
.InColumnValue(DnsTable.Columns.user, _user)
.InColumnValue(DnsTable.Columns.tenant, _tenant)
.InColumnValue(DnsTable.Columns.dkim_selector, dkimSelector)
.InColumnValue(DnsTable.Columns.dkim_private_key, privateKey)
.InColumnValue(DnsTable.Columns.dkim_public_key, publicKey)
.InColumnValue(DnsTable.Columns.domain_check, domainCheck)
.InColumnValue(DnsTable.Columns.spf, spf)
.Identity(0, 0, true);
var dnsRecordId = NullSafeExecuteScalar<int>(db, insertValuesQuery);
return new DnsDto(dnsRecordId, _tenant, _user, Defines.UNUSED_DNS_SETTING_DOMAIN_ID, dkimSelector,
privateKey, publicKey, domainCheck, spf);
}
private bool ShowEmptyScreen()
{
if (CoreContext.UserManager.GetUsers(SecurityContext.CurrentAccount.ID).IsVisitor())
{
return false;
}
if (showEmptyScreen.Get("communityScreen" + TenantProvider.CurrentTenantID) != null)
{
return false;
}
var q = new SqlExp(string.Format(@"select exists(select 1 from blogs_posts where tenant = {0}) or " +
"exists(select 1 from bookmarking_bookmark where tenant = {0}) or exists(select 1 from events_feed where tenant = {0}) or " +
"exists(select 1 from forum_category where tenantid = {0})", CoreContext.TenantManager.GetCurrentTenant().TenantId));
using (var db = new DbManager("community"))
{
var hasacitvity = db.ExecuteScalar<bool>(q);
if (hasacitvity)
{
showEmptyScreen.Insert("communityScreen" + TenantProvider.CurrentTenantID, new object(), TimeSpan.FromMinutes(30));
}
return !hasacitvity;
}
}
/// <summary>
/// Gets the Token Secret given a request or access token.
/// </summary>
/// <param name="token">The request or access token.</param>
/// <returns>
/// The secret associated with the given token.
/// </returns>
/// <exception cref="ArgumentException">Thrown if the secret cannot be found for the given token.</exception>
public string GetTokenSecret(string token)
{
using (var db=new DbManager(_dbId))
{
return db.ExecuteScalar<string>(new SqlQuery(TOKEN_TABLE).Select("token_secret").Where("token", token));
}
}
public List<TimeSpend> GetByFilter(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var query = CreateQuery();
if (filter.Max != 0 && !filter.Max.Equals(int.MaxValue))
{
query.SetFirstResult((int)filter.Offset);
query.SetMaxResults((int)filter.Max * 2);
}
if (!string.IsNullOrEmpty(filter.SortBy))
{
var sortColumns = filter.SortColumns["TimeSpend"];
sortColumns.Remove(filter.SortBy);
query.OrderBy("t." + filter.SortBy, filter.SortOrder);
foreach (var sort in sortColumns.Keys)
{
query.OrderBy("t." + sort, sortColumns[sort]);
}
}
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(query).ConvertAll(ToTimeSpend);
}
}
/// <summary>
/// Stores a newly generated unauthorized request token, secret, and optional
/// application-specific parameters for later recall.
/// </summary>
/// <param name="request">The request message that resulted in the generation of a new unauthorized request token.</param>
/// <param name="response">The response message that includes the unauthorized request token.</param>
/// <exception cref="ArgumentException">Thrown if the consumer key is not registered, or a required parameter was not found in the parameters collection.</exception>
/// <remarks>
/// Request tokens stored by this method SHOULD NOT associate any user account with this token.
/// It usually opens up security holes in your application to do so. Instead, you associate a user
/// account with access tokens (not request tokens) in the <see cref="ExpireRequestTokenAndStoreNewAccessToken"/>
/// method.
/// </remarks>
public void StoreNewRequestToken(UnauthorizedTokenRequest request, ITokenSecretContainingMessage response)
{
using (var db = new DbManager(_dbId))
{
db.ExecuteNonQuery(new SqlInsert(TOKEN_TABLE, true).InColumnValue("token", response.Token).InColumnValue("token_secret", response.TokenSecret).InColumnValue("request_token",true));
}
}
public string GetById(int id)
{
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteScalar<string>(Query(TagsTable).Select("title").Where("id", id));
}
}
public AccountController()
{
_dbManager = new DbManager();
_repository = new Repository();
var store = new UserStore<ApplicationUser>(_dbManager);
_userManager = new UserManager<ApplicationUser>(store);
}
public static IEnumerable<int> Search(ModuleInfo module)
{
using (var db = new DbManager(TextIndexCfg.ConnectionStringName))
{
return db.ExecuteList(module.SqlQuery).ConvertAll(r => GetId(r[0]));
}
}
public override List<SpaceUsageStatManager.UsageSpaceStatItem> GetStatData()
{
var data = new List<SpaceUsageStatManager.UsageSpaceStatItem>();
if (!DbRegistry.IsDatabaseRegistered(FILES_DBID)) DbRegistry.RegisterDatabase(FILES_DBID, ConfigurationManager.ConnectionStrings[FILES_DBID]);
using (var filedb = new DbManager(FILES_DBID))
{
var q = new SqlQuery("files_file f")
.Select("b.right_node")
.SelectSum("f.content_length")
.InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id"))
.InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node"))
.Where("b.tenant_id", CoreContext.TenantManager.GetCurrentTenant().TenantId)
.Where(Exp.Like("b.right_node", "crm/crm_common/", SqlLike.StartWith))
.GroupBy(1);
var size = filedb.ExecuteList(q).Select(r => Convert.ToInt64(r[1])).FirstOrDefault();
data.Add(new UsageSpaceStatItem()
{
Name = Resources.CRMCommonResource.ProductName,
SpaceUsage = size,
Url = VirtualPathUtility.ToAbsolute(PathProvider.StartURL())
});
}
return data;
}
public WebDomainDto AddWebDomain(string name, bool isVerified, DbManager db)
{
if (string.IsNullOrEmpty(name))
throw new ArgumentNullException("name");
if (db == null)
throw new ArgumentNullException("db");
var domain = GetDomain(name, db);
if (domain != null)
throw new DuplicateNameException(String.Format("Domain with name {0}. Already added to tenant {1}.",
domain.name, domain.tenant));
var domainAddTime = DateTime.UtcNow.ToDbStyle();
var addWebDomainQuery = new SqlInsert(DomainTable.name)
.InColumnValue(DomainTable.Columns.id, 0)
.InColumnValue(DomainTable.Columns.name, name)
.InColumnValue(DomainTable.Columns.tenant, tenant)
.InColumnValue(DomainTable.Columns.is_verified, isVerified)
.InColumnValue(DomainTable.Columns.date_added, domainAddTime)
.Identity(0, 0, true);
var addedDomainId = db.ExecuteScalar<int>(addWebDomainQuery);
return new WebDomainDto(addedDomainId, name, tenant, isVerified);
}
public static DateTime GetLastDeltaIndexDate(ModuleInfo module)
{
using (var db = new DbManager("default"))
{
return db.ExecuteScalar<DateTime>(new SqlQuery("webstudio_index").Select("last_modified").Where("index_name", module.Delta));
}
}
public void Delete(int id)
{
using (var db = new DbManager(DatabaseId))
{
db.ExecuteNonQuery(Delete(TemplatesTable).Where("id", id));
}
}
private static int TestSqlConnection_AddNewBookthenRollback_ReturnBookInitialCount()
{
var dbManager = new DbManager<BooksContext>();
var before = 0;
dbManager.Connect(database =>
{
try
{
TransactionHelper.Begin(() =>
{
before = database.Books.Count();
database.Books.Add(new Book
{
Name = "Test Book",
Isbn = "IKS9001F"
});
database.SaveChanges();
throw new Exception("Database offline! please try again later");
});
}
catch (Exception ex)
{
throw new CustomException<DatabaseErrorExceptionArgs>(new DatabaseErrorExceptionArgs(ex.Message));
}
});
return before;
}
public IPGeolocationInfo GetIPGeolocation(string ip)
{
try
{
var ipformatted = FormatIP(ip);
using (var db = new DbManager(dbid))
{
var q = new SqlQuery("dbip_location")
.Select("ip_start", "ip_end", "country", "city", "timezone_offset", "timezone_name")
.Where(Exp.Le("ip_start", ipformatted))
.OrderBy("ip_start", false)
.SetMaxResults(1);
return db
.ExecuteList(q)
.Select(r => new IPGeolocationInfo()
{
IPStart = Convert.ToString(r[0]),
IPEnd = Convert.ToString(r[1]),
Key = Convert.ToString(r[2]),
City = Convert.ToString(r[3]),
TimezoneOffset = Convert.ToDouble(r[4]),
TimezoneName = Convert.ToString(r[5])
})
.SingleOrDefault(i => ipformatted.CompareTo(i.IPEnd) <= 0) ??
IPGeolocationInfo.Default;
}
}
catch (Exception error)
{
log.Error(error);
}
return IPGeolocationInfo.Default;
}
protected DbDao(DbManager db, int tenant)
{
if (db == null) throw new ArgumentNullException("db");
this.db = db;
Tenant = tenant;
}
public ReportTemplate GetTemplate(int id)
{
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(Query(ReportTable).Select(columns).Where("id", id)).ConvertAll(ToTemplate).SingleOrDefault();
}
}
public override List<UsageSpaceStatItem> GetStatData()
{
if (!DbRegistry.IsDatabaseRegistered(FileConstant.DatabaseId))
{
DbRegistry.RegisterDatabase(FileConstant.DatabaseId, ConfigurationManager.ConnectionStrings[FileConstant.DatabaseId]);
}
using (var filedb = new DbManager(FileConstant.DatabaseId))
{
var q = new SqlQuery("files_file f")
.Select("b.right_node")
.SelectSum("f.content_length")
.InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id"))
.InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node"))
.Where("b.tenant_id", TenantProvider.CurrentTenantID)
.Where(Exp.Like("b.right_node", "crm/crm_common/", SqlLike.StartWith))
.GroupBy(1);
return filedb.ExecuteList(q)
.Select(r => new UsageSpaceStatItem
{
Name = Resources.CRMCommonResource.ProductName,
SpaceUsage = Convert.ToInt64(r[1]),
Url = VirtualPathUtility.ToAbsolute(PathProvider.StartURL())
})
.ToList();
}
}
public List<Tenant> GetTenants()
{
var result = new List<Tenant>();
var tenants = CoreContext.TenantManager.GetTenants();
if (useUserActivity && last != DateTime.MinValue)
{
using (var db = new DbManager(dbid))
{
var ids = db
.ExecuteList(new SqlQuery("webstudio_useractivity").Select("tenantid").Where(Exp.Ge("ActivityDate", last.AddMinutes(-1))).GroupBy(1))
.ConvertAll(r => (int)r[0]);
result.AddRange(tenants.FindAll(t => ids.Contains(t.TenantId)));
ids = db
.ExecuteList(new SqlQuery("files_file").Select("tenant_id").Where(Exp.Ge("modified_on", last.AddMinutes(-1))).GroupBy(1))
.ConvertAll(r => (int)r[0]);
result.AddRange(tenants.FindAll(t => ids.Contains(t.TenantId)));
}
}
else
{
result.AddRange(tenants);
}
last = DateTime.UtcNow;
result.RemoveAll(t => t.Status != TenantStatus.Active);
return result;
}
public override List<UsageSpaceStatItem> GetStatData()
{
if (!DbRegistry.IsDatabaseRegistered(MailDatabaseId))
{
DbRegistry.RegisterDatabase(MailDatabaseId, ConfigurationManager.ConnectionStrings[MailDatabaseId]);
}
using (var mail_db = new DbManager(MailDatabaseId))
{
var query = new SqlQuery("mail_attachment a")
.InnerJoin("mail_mail m", Exp.EqColumns("a.id_mail", "m.id"))
.Select("m.id_user")
.Select("sum(a.size) as size")
.Where("m.tenant", TenantProvider.CurrentTenantID)
.Where("a.need_remove", 0)
.GroupBy(1)
.OrderBy(2, false);
return mail_db.ExecuteList(query)
.Select(r =>
{
var user_id = new Guid(Convert.ToString(r[0]));
var user = CoreContext.UserManager.GetUsers(user_id);
var item = new UsageSpaceStatItem
{
Name = DisplayUserSettings.GetFullUserName(user, false),
ImgUrl = UserPhotoManager.GetSmallPhotoURL(user.ID),
Url = CommonLinkUtility.GetUserProfile(user.ID),
SpaceUsage = Convert.ToInt64(r[1])
};
return item;
})
.ToList();
}
}
public List<Message> GetAll()
{
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(CreateQuery()).ConvertAll(ToMessage);
}
}
public override List<UsageSpaceStatItem> GetStatData()
{
using (var filedb = new DbManager(FileConstant.DatabaseId))
using (var projdb = new DbManager(PROJECTS_DBID))
{
var q = new SqlQuery("files_file f")
.Select("b.right_node")
.SelectSum("f.content_length")
.InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id"))
.InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node"))
.Where("b.tenant_id", TenantProvider.CurrentTenantID)
.Where(Exp.Like("b.right_node", "projects/project/", SqlLike.StartWith))
.GroupBy(1);
var sizes = filedb.ExecuteList(q)
.Select(r => new {ProjectId = Convert.ToInt32(((string) r[0]).Substring(17)), Size = Convert.ToInt64(r[1])})
.GroupBy(r => r.ProjectId)
.ToDictionary(g => g.Key, g => g.Sum(a => a.Size));
q = new SqlQuery("projects_projects")
.Select("id", "title")
.Where("tenant_id", TenantProvider.CurrentTenantID)
.Where(Exp.In("id", sizes.Keys));
return projdb.ExecuteList(q)
.Select(r => new UsageSpaceStatItem
{
Name = Convert.ToString(r[1]),
SpaceUsage = sizes[Convert.ToInt32(r[0])],
Url = String.Concat(PathProvider.BaseAbsolutePath, "projects.aspx?prjID=" + Convert.ToInt32(r[0]))
})
.OrderByDescending(i => i.SpaceUsage)
.ToList();
}
}
public static void UpdateLastDeltaIndexDate(ModuleInfo module)
{
using (var db = new DbManager("default"))
{
db.ExecuteNonQuery(new SqlInsert("webstudio_index", true).InColumnValue("index_name", module.Delta));
}
}
public PanelDbManager(IHost host, PluginParameter data)
{
_host = host;
_shellData = (Shell)data[0];
_shellSqlConn = GetShellSqlConn();
// init StrRes to translate string
StrRes.SetHost(_host);
Init();
//绑定事件
_dbManager = new DbManager(_host, _shellData, _shellSqlConn.type);
_dbManager.ConnectDbCompletedToDo += DbManagerConnectDbCompletedToDo;
_dbManager.GetDbNameCompletedToDo += DbManagerGetDbNameCompletedToDo;
_dbManager.GetDbTableNameCompletedToDo += DbManagerGetTableNameCompletedToDo;
_dbManager.GetColumnTypeCompletedToDo += DbManagerGetColumnTypeCompletedToDo;
_dbManager.ExecuteReaderCompletedToDo += DbManagerExecuteReaderCompletedToDo;
_dbManager.ExecuteNonQueryCompletedToDo += DbManagerExecuteNonQueryCompletedToDo;
RefreshServerStatus(false);
if (string.IsNullOrEmpty(_shellSqlConn.type) || string.IsNullOrEmpty(_shellSqlConn.conn))
{
MessageBox.Show("shell's sqlConnection is null or space");
}
else
{
//连接数据库
_dbManager.ConnectDb(_shellSqlConn.conn);
}
}
public Dictionary<int, string> GetTags()
{
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(GetTagQuery()).ToDictionary(r => Convert.ToInt32(r[0]), n => n[1].ToString().HtmlEncode());
}
}
private static void AddLoginEvent(EventMessage message)
{
using (var db = new DbManager(messagesDbId))
{
var i = new SqlInsert(loginEventsTable)
.InColumnValue("ip", message.IP)
.InColumnValue("login", message.Initiator)
.InColumnValue("browser", message.Browser)
.InColumnValue("mobile", message.Mobile)
.InColumnValue("platform", message.Platform)
.InColumnValue("date", message.Date)
.InColumnValue("tenant_id", message.TenantId)
.InColumnValue("user_id", message.UserId)
.InColumnValue("page", message.Page)
.InColumnValue("action", message.Action);
if (message.Description != null && message.Description.Any())
{
i = i.InColumnValue("description", JsonConvert.SerializeObject(message.Description, new JsonSerializerSettings
{
DateTimeZoneHandling = DateTimeZoneHandling.Utc
}));
}
db.ExecuteNonQuery(i);
}
}
public DBResourceReader(string fileName, CultureInfo culture)
{
language = culture.Name;
Data = new Hashtable();
if (language == "")
language = "Neutral";
using (var dbManager = new DbManager("tmresource"))
{
var sql = new SqlQuery("res_data")
.Select("res_data.title", "textValue")
.InnerJoin("res_files", Exp.EqColumns("res_files.id", "res_data.fileID"))
.LeftOuterJoin("res_cultures", Exp.EqColumns("res_cultures.title", "res_data.cultureTitle"))
.Where("ResName", fileName)
.Where("res_cultures.Title", language);
var list = dbManager.ExecuteList(sql);
foreach (var t in list)
{
Data.Add(t[0], t[1]);
}
}
}
public List<Subtask> GetSubtasks(int taskid)
{
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(CreateQuery().Where("task_id", taskid)).ConvertAll(ToSubTask);
}
}
public static void SaveFeeds(IEnumerable<FeedRow> feeds, string key, DateTime value)
{
using (var db = new DbManager(Constants.FeedDbId))
{
db.ExecuteNonQuery(new SqlInsert("feed_last", true).InColumnValue("last_key", key).InColumnValue("last_date", value));
}
const int feedsPortionSize = 1000;
var aggregatedDate = DateTime.UtcNow;
var feedsPortion = new List<FeedRow>();
foreach (var feed in feeds)
{
feedsPortion.Add(feed);
if (feedsPortion.Sum(f => f.Users.Count) <= feedsPortionSize) continue;
SaveFeedsPortion(feedsPortion, aggregatedDate);
feedsPortion.Clear();
}
if (feedsPortion.Any())
{
SaveFeedsPortion(feedsPortion, aggregatedDate);
}
}
public Template GetByID(int id)
{
using (var db = new DbManager(DatabaseId))
{
var query = Query(TemplatesTable + " p").Select(templateColumns).Where("p.id", id);
return db.ExecuteList(query).ConvertAll(ToTemplate).SingleOrDefault();
}
}
public List<Message> GetByProject(int projectId)
{
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(CreateQuery().Where("t.project_id", projectId).OrderBy("t.create_on", false))
.ConvertAll(ToMessage);
}
}
public ScheduleManager(DbManager mgr)
: base(mgr)
{
}
public virtual List <InvoiceItem> GetAll(DbManager db)
{
return(db.ExecuteList(GetInvoiceItemSqlQuery(null)).ConvertAll(ToInvoiceItem));
}
protected void test_connection_Click(object sender, System.EventArgs e)
{
Session["TrySqlPassword"] = txtSqlPassword.Text;
txtSqlPassword.Attributes.Add("Value", txtSqlPassword.Text);
Utils.SettingsPath = AdminPanelUtils.GetWebMailFolder();
DbManager dbMan = null;
string connectionString = string.Empty;
SupportedDatabase dbType = SupportedDatabase.MsSqlServer;
if (intDbTypeMsAccess.Checked)
{
dbMan = new MsAccessDbManager();
dbType = SupportedDatabase.MsAccess;
}
else if (intDbTypeMySql.Checked)
{
dbMan = new MySqlDbManager();
dbType = SupportedDatabase.MySql;
}
else
{
dbMan = new MsSqlDbManager();
dbType = SupportedDatabase.MsSqlServer;
}
string dsn = txtSqlDsn.Value;
if (!useDSN.Checked)
{
dsn = string.Empty;
}
connectionString = DbManager.CreateConnectionString(useCS.Checked, odbcConnectionString.Value,
dsn, dbType, txtAccessFile.Value, txtSqlLogin.Value,
txtSqlPassword.Text, txtSqlName.Value, txtSqlSrc.Value);
try
{
dbMan.Connect(connectionString);
if (!intDbTypeMsAccess.Checked)
{
try
{
dbMan.DropTable("a_test", DbPrefix.Value);
}
catch { }
dbMan.CreateTable("a_test", DbPrefix.Value);
dbMan.DropTable("a_test", DbPrefix.Value);
}
_errorMessageConnection = @"<font color=""green"">" + Constants.mailAdmConnectSuccess + "</font>";
}
catch (WebMailDatabaseException error)
{
Log.WriteException(error);
_errorMessageConnection = @"<font color=""red"">" + Constants.mailAdmConnectUnsuccess + ": " + error.Message + "</font>";
}
finally
{
dbMan.Disconnect();
}
}
public IEnumerable <Tag> GetNewTags(Guid subject, Folder parentFolder, bool deepSearch)
{
using (var dbManager = new DbManager(FileConstant.DatabaseId))
{
var folderId = SharePointDaoSelector.ConvertId(parentFolder.ID);
var fakeFolderId = parentFolder.ID.ToString();
var entryIDs = dbManager.ExecuteList(Query("files_thirdparty_id_mapping")
.Select("hash_id")
.Where(Exp.Like("id", fakeFolderId, SqlLike.StartWith)))
.ConvertAll(x => x[0]);
if (!entryIDs.Any())
{
return(new List <Tag>());
}
var sqlQuery = new SqlQuery("files_tag ft")
.Select("ft.name",
"ft.flag",
"ft.owner",
"ftl.entry_id",
"ftl.entry_type",
"ftl.tag_count",
"ft.id")
.Distinct()
.LeftOuterJoin("files_tag_link ftl",
Exp.EqColumns("ft.tenant_id", "ftl.tenant_id") &
Exp.EqColumns("ft.id", "ftl.tag_id"))
.Where(Exp.Eq("ft.tenant_id", TenantID) &
Exp.Eq("ftl.tenant_id", TenantID) &
Exp.Eq("ft.flag", (int)TagType.New) &
Exp.In("ftl.entry_id", entryIDs));
if (subject != Guid.Empty)
{
sqlQuery.Where(Exp.Eq("ft.owner", subject));
}
var tags = dbManager.ExecuteList(sqlQuery).ConvertAll(r => new Tag
{
TagName = Convert.ToString(r[0]),
TagType = (TagType)r[1],
Owner = new Guid(r[2].ToString()),
EntryId = MappingID(r[3]),
EntryType = (FileEntryType)r[4],
Count = Convert.ToInt32(r[5]),
Id = Convert.ToInt32(r[6])
});
if (deepSearch)
{
return(tags);
}
var folderFileIds = new[] { fakeFolderId }
.Concat(ProviderInfo.GetFolderFolders(folderId).Select(x => ProviderInfo.MakeId(x.ServerRelativeUrl)))
.Concat(ProviderInfo.GetFolderFiles(folderId).Select(x => ProviderInfo.MakeId(x.ServerRelativeUrl)));
return(tags.Where(tag => folderFileIds.Contains(tag.EntryId.ToString())));
}
}
public DkimDto GetFreeDkim(DbManager db = null)
{
return(GetDomainDkim(DefaultDomainId, db));
}
public BaseDao(string dbid, int tenant)
{
this.db = new DbManager(dbid);
this.tenant = tenant;
}
public PurchaseLineManager(DbManager mgr)
: base(mgr)
{
}
public DbAddressManager(DbManager mgr)
: base(mgr)
{
}
protected void Add_OK_Click(object sender, EventArgs e)
{
DateTime last = Convert.ToDateTime(TB_Add_Date.Text.ToString());
string beizhu = Input.Inputadd(TB_Add_Beizhu.Text.ToString());
string position = Session["position"].ToString();
int zhouqi = 30;
string sqlcheck = "Select [zhouqi] from PMState where EQ ='" + Session["eq"].ToString() + "' AND unit ='" + Session["unit"].ToString() + "' AND item='" + Session["item"].ToString() + "'";
if (position != "")
{
sqlcheck += " AND [position]='" + position + "'";
}
string ConnectionString = ConfigurationManager.ConnectionStrings["mdbcon"].ConnectionString;
OleDbConnection conn = new OleDbConnection(ConnectionString);
OleDbCommand cmd = new OleDbCommand(sqlcheck, conn);
conn.Open();
OleDbDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
zhouqi = Convert.ToInt32(rd["zhouqi"].ToString());
}
rd.Close();
conn.Close();
DateTime duedate = last.AddDays(zhouqi);
string fujian = "";
string filesql = "";
if (FileUpload1.HasFile)
{
string filename = this.FileUpload1.FileName;
fujian = "~/UploadFiles/设备报告/" + DateTime.Now.ToString("yyyyMMddHHmmss") + filename;
filesql = "Insert into UploadFiles (fenlei,title,path,filename,shortdate,eng) values ('设备报告','PM报告','" + fujian + "','" + filename + "','" + last.ToShortDateString() + "','" + Session["name"].ToString() + "')";
}
string listsql = "Insert into PMList (EQ,unit,item,beizhu,[last],[duedate],[lindex],[position],eng,fujian) values ('" + Session["eq"].ToString() + "','" +
Session["unit"].ToString() + "','" + Session["item"].ToString() + "','" + beizhu + "',#" + last.ToLocalTime() + "#,#" + duedate.ToLocalTime() + "#,'" +
Session["lindex"] + "','" + position + "','" + Session["name"].ToString() + "','" + fujian + "')";
string updatesql = "Update PMState Set [last]=#" + last.ToLocalTime() + "#,[duedate]=#" + duedate.ToLocalTime() + "#,beizhu='" + beizhu + "',fujian='" + fujian + "' where EQ='" + Session["eq"].ToString() + "' AND unit='" + Session["unit"].ToString() + "' AND item ='" + Session["item"].ToString() + "'";
if (position != "")
{
updatesql += " AND [position]='" + position + "'";
}
if ((bool)ViewState["isedit"])
{
listsql = "Update PMList Set EQ='" + Session["eq"].ToString() + "',unit='" + Session["unit"].ToString() + "',item='" + Session["item"].ToString() +
"',beizhu='" + beizhu + "',[last]=#" + last.ToLocalTime() + "#,[duedate]=#" + duedate.ToLocalTime() + "#,lindex='" + Session["lindex"] +
"',[position]='" + position + "',eng='" + Session["name"].ToString() + "',fujian='" + fujian + "' where ID in " + ViewState["sqlid"].ToString();
if (FileUpload1.HasFile)
{
FileUpload1.PostedFile.SaveAs(Server.MapPath(fujian));
DbManager.ExecuteNonQuery(filesql);
}
DbManager.ExecuteNonQuery(listsql);
RefreshState();
LB_OK.Text = "发布成功！";
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script> ShowDialog('dlg_ok');</script>");
LoadData();
}
else
{
if (FileUpload1.HasFile)
{
FileUpload1.PostedFile.SaveAs(Server.MapPath(fujian));
DbManager.ExecuteNonQuery(filesql);
}
DbManager.ExecuteNonQuery(listsql);
DbManager.ExecuteNonQuery(updatesql);
LB_OK.Text = "发布成功！";
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script> ShowDialog('dlg_ok');</script>");
LoadData();
}
}
protected void Delete_Click(object sender, EventArgs e)
{
string sqlid = "";
for (int i = 0; i < GridView1.Rows.Count; i++)
{
CheckBox cbx = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
if (cbx.Checked == true)
{
sqlid = sqlid + Convert.ToInt32(GridView1.DataKeys[i].Value);
break;
}
}
if (sqlid != "")
{
sqlid = "(" + sqlid + ")";
string sqldelete = "Delete from PMList where ID in" + sqlid;
string sqlcheck = "Select Count(*) As ct from PMList Where eq='" + Session["eq"].ToString() + "' AND unit='" + Session["unit"].ToString() + "' AND item='" + Session["item"].ToString() + "'";
if (Session["position"].ToString() != "")
{
sqlcheck = "Select Count(*) As ct from PMList Where eq='" + Session["eq"].ToString() + "' AND unit='" + Session["unit"].ToString() + "' AND item='" + Session["item"].ToString() + "' AND [position]='" + Session["position"].ToString() + "'";
}
string filecheck = "Select fujian from PMList where ID in" + sqlid;
string ConnectionString = ConfigurationManager.ConnectionStrings["mdbcon"].ConnectionString;
OleDbConnection conn = new OleDbConnection(ConnectionString);
OleDbCommand cmd = new OleDbCommand(sqlcheck, conn);
conn.Open();
int count = (int)cmd.ExecuteScalar();
cmd.CommandText = filecheck;
cmd.Connection = conn;
OleDbDataReader rd = cmd.ExecuteReader();
bool isdelete = false;
if (count > 1)
{
isdelete = true;
}
string fujian = "";
while (rd.Read())
{
fujian = rd["fujian"].ToString();
}
conn.Close();
if (isdelete)
{
try
{
if (File.Exists(Server.MapPath(fujian)))
{
File.Delete(Server.MapPath(fujian));
string filesql = "Delete from UploadFiles where path='" + fujian + "'";
DbManager.ExecuteNonQuery(filesql);
}
int i = DbManager.ExecuteNonQuery(sqldelete);
RefreshState();
string myscript = @"alert('删除成功！');window.location.href='PMList.aspx';";
Page.ClientScript.RegisterStartupScript(this.GetType(), "myscript", myscript, true);
}
catch
{
string myscript = @"alert('删除失败，请与管理员联系！');window.location.href='PMList.aspx';";
Page.ClientScript.RegisterStartupScript(this.GetType(), "myscript", myscript, true);
}
}
else
{
string myscript = @"alert('只有最后一条了，不能删！');window.location.href='PMList.aspx';";
Page.ClientScript.RegisterStartupScript(this.GetType(), "myscript", myscript, true);
}
}
else
{
string myscript = @"alert('请选择删除项！');window.location.href='PMList.aspx';";
Page.ClientScript.RegisterStartupScript(this.GetType(), "myscript", myscript, true);
}
}
public Boolean IsExist(int invoiceItemID, DbManager db)
{
return(db.ExecuteScalar <bool>(@"select exists(select 1 from crm_invoice_item where tenant_id = @tid and id = @id)",
new { tid = TenantID, id = invoiceItemID }));
}
private void DoDump(IDataWriteOperator writer)
{
Dictionary <string, List <string> > databases = new Dictionary <string, List <string> >();
using (var dbManager = DbManager.FromHttpContext("default", 100000))
{
dbManager.ExecuteList("select id, connection_string from mail_server_server").ForEach((r =>
{
var dbName = GetDbName((int)r[0], JsonConvert.DeserializeObject <Dictionary <string, object> >(Convert.ToString(r[1]))["DbConnection"].ToString());
using (var dbManager1 = DbManager.FromHttpContext(dbName, 100000))
{
var tables = dbManager1.ExecuteList("show tables;").Select(res => Convert.ToString(res[0])).ToList();
databases.Add(dbName, tables);
}
}));
}
using (var dbManager = DbManager.FromHttpContext("default", 100000))
{
var tables = dbManager.ExecuteList("show tables;").Select(res => Convert.ToString(res[0])).ToList();
databases.Add("default", tables);
}
using (var stream = new MemoryStream(Encoding.UTF8.GetBytes(true.ToString())))
{
writer.WriteEntry(KeyHelper.GetDumpKey(), stream);
}
var files = new List <BackupFileInfo>();
var stepscount = 0;
foreach (var db in databases)
{
stepscount += db.Value.Count * 4;// (schema + data) * (dump + zip)
}
if (ProcessStorage)
{
var tenants = CoreContext.TenantManager.GetTenants(false).Select(r => r.TenantId);
foreach (var t in tenants)
{
files.AddRange(GetFiles(t));
}
stepscount += files.Count * 2 + 1;
Logger.Debug("files:" + files.Count);
}
SetStepsCount(stepscount);
foreach (var db in databases)
{
DoDump(writer, db.Key, db.Value);
}
var dir = Path.GetDirectoryName(BackupFilePath);
var subDir = Path.Combine(dir, Path.GetFileNameWithoutExtension(BackupFilePath));
Logger.DebugFormat("dir remove start {0}", subDir);
Directory.Delete(subDir, true);
Logger.DebugFormat("dir remove end {0}", subDir);
if (ProcessStorage)
{
DoDumpStorage(writer, files);
}
}
private InvoiceItem SaveOrUpdateInvoiceItem(InvoiceItem invoiceItem, DbManager db)
{
if (invoiceItem.Price <= 0 || String.IsNullOrEmpty(invoiceItem.Title))
{
throw new ArgumentException();
}
if (!CRMSecurity.IsAdmin)
{
CRMSecurity.CreateSecurityException();
}
if (String.IsNullOrEmpty(invoiceItem.Description))
{
invoiceItem.Description = String.Empty;
}
if (String.IsNullOrEmpty(invoiceItem.StockKeepingUnit))
{
invoiceItem.StockKeepingUnit = String.Empty;
}
if (!IsExist(invoiceItem.ID, db))
{
invoiceItem.ID = db.ExecuteScalar <int>(
Insert("crm_invoice_item")
.InColumnValue("id", 0)
.InColumnValue("title", invoiceItem.Title)
.InColumnValue("description", invoiceItem.Description)
.InColumnValue("stock_keeping_unit", invoiceItem.StockKeepingUnit)
.InColumnValue("price", invoiceItem.Price)
.InColumnValue("quantity", invoiceItem.Quantity)
.InColumnValue("stock_quantity", invoiceItem.StockQuantity)
.InColumnValue("track_inventory", invoiceItem.TrackInventory)
.InColumnValue("invoice_tax1_id", invoiceItem.InvoiceTax1ID)
.InColumnValue("invoice_tax2_id", invoiceItem.InvoiceTax2ID)
.InColumnValue("currency", String.Empty)
.InColumnValue("create_on", DateTime.UtcNow)
.InColumnValue("create_by", SecurityContext.CurrentAccount.ID)
.InColumnValue("last_modifed_on", DateTime.UtcNow)
.InColumnValue("last_modifed_by", SecurityContext.CurrentAccount.ID)
.Identity(1, 0, true));
invoiceItem.CreateOn = DateTime.UtcNow;
invoiceItem.LastModifedOn = invoiceItem.CreateOn;
invoiceItem.CreateBy = SecurityContext.CurrentAccount.ID;
invoiceItem.LastModifedBy = invoiceItem.CreateBy;
}
else
{
var oldInvoiceItem = db.ExecuteList(GetInvoiceItemSqlQuery(Exp.Eq("id", invoiceItem.ID)))
.ConvertAll(ToInvoiceItem)
.FirstOrDefault();
CRMSecurity.DemandEdit(oldInvoiceItem);
db.ExecuteNonQuery(
Update("crm_invoice_item")
.Set("title", invoiceItem.Title)
.Set("description", invoiceItem.Description)
.Set("stock_keeping_unit", invoiceItem.StockKeepingUnit)
.Set("price", invoiceItem.Price)
.Set("quantity", invoiceItem.Quantity)
.Set("stock_quantity", invoiceItem.StockQuantity)
.Set("track_inventory", invoiceItem.TrackInventory)
.Set("invoice_tax1_id", invoiceItem.InvoiceTax1ID)
.Set("invoice_tax2_id", invoiceItem.InvoiceTax2ID)
.Set("currency", String.Empty)
.Set("last_modifed_on", DateTime.UtcNow)
.Set("last_modifed_by", SecurityContext.CurrentAccount.ID)
.Where(Exp.Eq("id", invoiceItem.ID)));
invoiceItem.LastModifedOn = DateTime.UtcNow;
invoiceItem.LastModifedBy = SecurityContext.CurrentAccount.ID;
}
return(invoiceItem);
}
public MailGroupDto SaveMailGroup(string addressName, DateTime addressCreatedDate,
int domainId, string domainName, bool isVerified, List <MailAddressDto> addressDtoList, DbManager db)
{
if (string.IsNullOrEmpty(addressName))
{
throw new ArgumentNullException("addressName");
}
if (domainId < 0)
{
throw new ArgumentException("Argument domain_id less then zero.", "domainId");
}
if (string.IsNullOrEmpty(domainName))
{
throw new ArgumentNullException("domainName");
}
if (addressDtoList == null || !addressDtoList.Any())
{
throw new ArgumentException("Address dto list must be not empty.", "addressDtoList");
}
if (db == null)
{
throw new ArgumentNullException("db");
}
var addressDal = new MailAddressDal(tenant);
var createdTime = DateTime.UtcNow;
var addressDto = addressDal.AddMailgroupAddress(addressName, addressCreatedDate, domainId, domainName,
isVerified, db);
var insertGroupQuery = new SqlInsert(MailGroupTable.Name)
.InColumnValue(MailGroupTable.Columns.Id, 0)
.InColumnValue(MailGroupTable.Columns.Tenant, tenant)
.InColumnValue(MailGroupTable.Columns.DateCreated, createdTime)
.InColumnValue(MailGroupTable.Columns.AddressId, addressDto.id)
.InColumnValue(MailGroupTable.Columns.Address, addressName
+ '@' + domainName)
.Identity(0, 0, true);
var mailGroupId = db.ExecuteScalar <int>(insertGroupQuery);
AddAddressesToMailGroup(mailGroupId, addressDtoList.Select(dto => dto.id).ToList(), db);
return(new MailGroupDto(mailGroupId, tenant, addressDto.id, addressDto, addressDtoList));
}
private void DumpTableData(string t, string dir, int count, string dbName, IDataWriteOperator writer)
{
try
{
if (count == 0)
{
Logger.DebugFormat("dump table data stop {0}", t);
SetStepCompleted(2);
return;
}
Logger.DebugFormat("dump table data start {0}", t);
var searchWithPrimary = false;
string primaryIndex;
int primaryIndexStep = 0;
int primaryIndexStart = 0;
List <string> columns;
using (var dbManager = DbManager.FromHttpContext(dbName, 100000))
{
var columnsData = dbManager.ExecuteList(string.Format("SHOW COLUMNS FROM `{0}`;", t));
columns = columnsData
.Select(r => "`" + Convert.ToString(r[0]) + "`")
.ToList();
primaryIndex = dbManager
.ExecuteList(
new SqlQuery("information_schema.`COLUMNS`")
.Select("COLUMN_NAME")
.Where("TABLE_SCHEMA", dbManager.Connection.Database)
.Where("TABLE_NAME", t)
.Where("COLUMN_KEY", "PRI")
.Where("DATA_TYPE", "int"))
.ConvertAll(r => Convert.ToString(r[0]))
.FirstOrDefault();
var isLeft = dbManager.ExecuteList(string.Format("SHOW INDEXES FROM {0} WHERE COLUMN_NAME='{1}' AND seq_in_index=1", t, primaryIndex));
searchWithPrimary = isLeft.Count == 1;
if (searchWithPrimary)
{
var minMax = dbManager
.ExecuteList(new SqlQuery(t).SelectMax(primaryIndex).SelectMin(primaryIndex))
.ConvertAll(r => new Tuple <int, int>(Convert.ToInt32(r[0]), Convert.ToInt32(r[1])))
.FirstOrDefault();
primaryIndexStart = minMax.Item2;
primaryIndexStep = (minMax.Item1 - minMax.Item2) / count;
if (primaryIndexStep < Limit)
{
primaryIndexStep = Limit;
}
}
}
var path = Path.Combine(dir, t);
var offset = 0;
do
{
List <object[]> result;
if (searchWithPrimary)
{
result = GetDataWithPrimary(t, columns, primaryIndex, primaryIndexStart, primaryIndexStep, dbName);
primaryIndexStart += primaryIndexStep;
}
else
{
result = GetData(t, columns, offset, dbName);
}
offset += Limit;
var resultCount = result.Count;
if (resultCount == 0)
{
break;
}
SaveToFile(path, t, columns, result);
} while (true);
SetStepCompleted();
Logger.DebugFormat("dump table data stop {0}", t);
}
catch (Exception e)
{
Logger.Error(e);
throw;
}
}
public override string ToString()
{
return("( " + _cost + " perc fel-/leszállás | " + _time.ToString(@"hh\:mm") + " ) --> '" + DbManager.GetStopById(_toStopId).StopName + "'");
}
public RemoteFileVisibilityConverter()
{
dbManager = DependencyInjector.Get <DbManager>();
//loading this is kinda this way
dbManager.Syncables.Count();
}
