protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DateTime beginDate = DateTime.Now.Date;
DateTime endDate = beginDate.AddDays(1).Date;
DataTable lists = bll.GetItemListByDate(beginDate, endDate);
this.List.DataSource = lists;
this.List.DataBind();
this.Label1.Text = "记录：" + lists.Rows.Count;
}
}
//备份数据
protected void Button2_Command(object sender, CommandEventArgs e)
{
DateTime beginDate = Convert.ToDateTime(this.BeginDateBox.Text.Trim()).Date;
DateTime endDate = Convert.ToDateTime(this.EndDateBox.Text.Trim()).AddDays(1).Date;
string fileName = beginDate.ToString("yyyy-MM-dd") + "~" + endDate.AddDays(-1).ToString("yyyy-MM-dd") + "-bak.sql";
string type = e.CommandArgument.ToString();
if (type == "mysql")
{
fileName = "mysql-" + fileName;
}
StringBuilder sb = new StringBuilder();
DataTable itemListTab = bll.GetItemListByDate(beginDate, endDate);
if (itemListTab.Rows.Count > 0)
{
sb.AppendLine("--备份消费表");
sb.Append("DELETE FROM ItemTable WHERE ItemID IN (");
foreach (DataRow dr in itemListTab.Rows)
{
sb.Append(dr["ItemID"].ToString() + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine(")");
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT ItemTable ON");
}
foreach (DataRow dr in itemListTab.Rows)
{
sb.AppendLine("INSERT INTO ItemTable (ItemID, ItemType, ItemName, CategoryTypeID, ItemPrice, ItemBuyDate, UserID, ModifyDate, Recommend, Synchronize, RegionID, RegionType, ZhuanTiID, CardID) VALUES (" +
dr["ItemID"].ToString() + ", '" +
dr["ItemType"].ToString() + "', '" +
Utility.ReplaceSql(dr["ItemName"].ToString()) + "', '" +
dr["CategoryTypeID"].ToString() + "', '" +
dr["ItemPrice"].ToString() + "', '" +
String.Format("{0:yyyy-MM-dd HH:mm:ss}", dr["ItemBuyDate"].ToString()) + "', '" +
dr["UserID"].ToString() + "', '" +
String.Format("{0:yyyy-MM-dd HH:mm:ss}", dr["ModifyDate"].ToString()) + "', '" +
dr["Recommend"].ToString() + "', '" +
dr["Synchronize"].ToString() + "', '" +
dr["RegionID"].ToString() + "', '" +
dr["RegionType"].ToString() + "', '" +
dr["ZhuanTiID"].ToString() + "', '" +
dr["CardID"].ToString() + "')");
}
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT ItemTable OFF");
}
sb.AppendLine("");
}
DataTable userCatTypeListTab = cat_bll.GetUserCategoryListByDate(beginDate, endDate);
if (userCatTypeListTab.Rows.Count > 0)
{
sb.AppendLine("--备份类别表");
sb.Append("DELETE FROM UserCategoryTable WHERE UserCategoryID IN (");
foreach (DataRow dr in userCatTypeListTab.Rows)
{
sb.Append(dr["UserCategoryID"].ToString() + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine(")");
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT UserCategoryTable ON");
}
foreach (DataRow dr in userCatTypeListTab.Rows)
{
sb.AppendLine("INSERT INTO UserCategoryTable (UserCategoryID, CategoryTypeName, CategoryTypePrice, UserID, CategoryTypeID, Synchronize, CategoryTypeLive, ModifyDate) VALUES (" +
dr["UserCategoryID"].ToString() + ", '" +
Utility.ReplaceSql(dr["CategoryTypeName"].ToString()) + "', '" +
dr["CategoryTypePrice"].ToString() + "', '" +
dr["UserID"].ToString() + "', '" +
dr["CategoryTypeID"].ToString() + "', '" +
dr["Synchronize"].ToString() + "', '" +
dr["CategoryTypeLive"].ToString() + "', '" +
String.Format("{0:yyyy-MM-dd HH:mm:ss}", dr["ModifyDate"].ToString()) + "')");
}
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT UserCategoryTable OFF");
}
sb.AppendLine("");
}
DataTable zhuanTiListTab = zt_bll.GetZhuanTiListByDate(beginDate, endDate);
if (zhuanTiListTab.Rows.Count > 0)
{
sb.AppendLine("--备份专题表");
sb.Append("DELETE FROM ZhuanTiTable WHERE ZhuanTiID IN (");
foreach (DataRow dr in zhuanTiListTab.Rows)
{
sb.Append(dr["ZhuanTiID"].ToString() + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine(")");
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT ZhuanTiTable ON");
}
foreach (DataRow dr in zhuanTiListTab.Rows)
{
sb.AppendLine("INSERT INTO ZhuanTiTable (ZhuanTiID, ZhuanTiName, UserID, ZTID, ZhuanTiImage, Synchronize, ZhuanTiLive, ModifyDate) VALUES (" +
dr["ZhuanTiID"].ToString() + ", '" +
Utility.ReplaceSql(dr["ZhuanTiName"].ToString()) + "', '" +
dr["UserID"].ToString() + "', '" +
dr["ZTID"].ToString() + "', '" +
dr["ZhuanTiImage"].ToString() + "', '" +
dr["Synchronize"].ToString() + "', '" +
dr["ZhuanTiLive"].ToString() + "', '" +
String.Format("{0:yyyy-MM-dd HH:mm:ss}", dr["ModifyDate"].ToString()) + "')");
}
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT ZhuanTiTable OFF");
}
sb.AppendLine("");
}
DataTable zhangListTab = zz_bll.GetZhuanZhangListByDate(beginDate, endDate);
if (zhangListTab.Rows.Count > 0)
{
sb.AppendLine("--备份转账表");
sb.Append("DELETE FROM ZhuanZhangTable WHERE ZhuanZhangID IN (");
foreach (DataRow dr in zhangListTab.Rows)
{
sb.Append(dr["ZhuanZhangID"].ToString() + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine(")");
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT ZhuanZhangTable ON");
}
foreach (DataRow dr in zhangListTab.Rows)
{
sb.AppendLine("INSERT INTO ZhuanZhangTable (ZhuanZhangID, ZhuanZhangFrom, ZhuanZhangTo, ZhuanZhangMoney, ZhuanZhangDate, ZhuanZhangNote, UserID, Synchronize, ZhuanZhangLive, ZZID, ModifyDate) VALUES (" +
dr["ZhuanZhangID"].ToString() + ", '" +
dr["ZhuanZhangFrom"].ToString() + "', '" +
dr["ZhuanZhangTo"].ToString() + "', '" +
dr["ZhuanZhangMoney"].ToString() + "', '" +
String.Format("{0:yyyy-MM-dd HH:mm:ss}", dr["ZhuanZhangDate"].ToString()) + "', '" +
Utility.ReplaceSql(dr["ZhuanZhangNote"].ToString()) + "', '" +
dr["UserID"].ToString() + "', '" +
dr["Synchronize"].ToString() + "', '" +
dr["ZhuanZhangLive"].ToString() + "', '" +
dr["ZZID"].ToString() + "', '" +
String.Format("{0:yyyy-MM-dd HH:mm:ss}", dr["ModifyDate"].ToString()) + "')");
}
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT ZhuanZhangTable OFF");
}
sb.AppendLine("");
}
DataTable cardListTab = card_bll.GetCardListByDate(beginDate, endDate);
if (cardListTab.Rows.Count > 0)
{
sb.AppendLine("--备份钱包表");
sb.Append("DELETE FROM CardTable WHERE CardID IN (");
foreach (DataRow dr in cardListTab.Rows)
{
sb.Append(dr["CardID"].ToString() + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine(")");
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT CardTable ON");
}
foreach (DataRow dr in cardListTab.Rows)
{
sb.AppendLine("INSERT INTO CardTable (CardID, CardName, UserID, CDID, CardMoney, MoneyStart, CardNumber, CardImage, Synchronize, CardLive, ModifyDate) VALUES (" +
dr["CardID"].ToString() + ", '" +
Utility.ReplaceSql(dr["CardName"].ToString()) + "', '" +
dr["UserID"].ToString() + "', '" +
dr["CDID"].ToString() + "', '" +
dr["CardMoney"].ToString() + "', '" +
dr["MoneyStart"].ToString() + "', '" +
dr["CardNumber"].ToString() + "', '" +
dr["CardImage"].ToString() + "', '" +
dr["Synchronize"].ToString() + "', '" +
dr["CardLive"].ToString() + "', '" +
String.Format("{0:yyyy-MM-dd HH:mm:ss}", dr["ModifyDate"].ToString()) + "')");
}
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT CardTable OFF");
}
sb.AppendLine("");
}
DataTable oauthListTab = oauth_bll.GetOAuthListByDate(beginDate, endDate);
if (oauthListTab.Rows.Count > 0)
{
sb.AppendLine("--备份第三方登录表");
sb.Append("DELETE FROM OAuthTable WHERE OAuthID IN (");
foreach (DataRow dr in oauthListTab.Rows)
{
sb.Append(dr["OAuthID"].ToString() + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine(")");
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT OAuthTable ON");
}
foreach (DataRow dr in oauthListTab.Rows)
{
sb.AppendLine("INSERT INTO OAuthTable (OAuthID, OpenID, AccessToken, UserID, OldUserID, OAuthBound, OAuthFrom, ModifyDate) VALUES (" +
dr["OAuthID"].ToString() + ", '" +
dr["OpenID"].ToString() + "', '" +
dr["AccessToken"].ToString() + "', '" +
dr["UserID"].ToString() + "', '" +
dr["OldUserID"].ToString() + "', '" +
dr["OAuthBound"].ToString() + "', '" +
dr["OAuthFrom"].ToString() + "', '" +
String.Format("{0:yyyy-MM-dd HH:mm:ss}", dr["ModifyDate"].ToString()) + "')");
}
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT OAuthTable OFF");
}
sb.AppendLine("");
}
DataTable userListTab = user_bll.GetUserListByDate(beginDate, endDate);
if (userListTab.Rows.Count > 0)
{
sb.AppendLine("--备份用户表");
sb.Append("DELETE FROM UserTable WHERE UserID IN (");
foreach (DataRow dr in userListTab.Rows)
{
sb.Append(dr["UserID"].ToString() + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine(")");
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT UserTable ON");
}
foreach (DataRow dr in userListTab.Rows)
{
sb.AppendLine("INSERT INTO UserTable (UserID, UserName, UserPassword, UserNickName, UserImage, UserCity, UserMoney, MoneyStart, UserWorkDay, CategoryRate, UserFunction, CreateDate, ModifyDate, UserPhone, UserEmail, UserTheme, UserLevel, UserFrom) VALUES (" +
dr["UserID"].ToString() + ", '" +
Utility.ReplaceSql(dr["UserName"].ToString()) + "', '" +
Utility.ReplaceSql(dr["UserPassword"].ToString()) + "', '" +
Utility.ReplaceSql(dr["UserNickName"].ToString()) + "', '" +
dr["UserImage"].ToString() + "', '" +
dr["UserCity"].ToString() + "', '" +
dr["UserMoney"].ToString() + "', '" +
dr["MoneyStart"].ToString() + "', '" +
dr["UserWorkDay"].ToString() + "', '" +
dr["CategoryRate"].ToString() + "', '" +
dr["UserFunction"].ToString() + "', '" +
String.Format("{0:yyyy-MM-dd HH:mm:ss}", dr["CreateDate"]) + "', '" +
String.Format("{0:yyyy-MM-dd HH:mm:ss}", dr["ModifyDate"]) + "', '" +
dr["UserPhone"].ToString() + "', '" +
dr["UserEmail"].ToString() + "', '" +
dr["UserTheme"].ToString() + "', '" +
dr["UserLevel"].ToString() + "', '" +
dr["UserFrom"].ToString() + "')");
}
if (type == "mssql")
{
sb.AppendLine("SET IDENTITY_INSERT UserTable OFF");
}
sb.AppendLine("");
}
DataTable delListTab = del_bll.GetDeleteList();
if (delListTab.Rows.Count > 0)
{
sb.AppendLine("--清除删除表");
int index = 0;
sb.Append("DELETE FROM ItemTable WHERE ItemID IN (");
foreach (DataRow dr in delListTab.Rows)
{
index++;
sb.Append(dr["ItemID"].ToString());
if (index != delListTab.Rows.Count)
{
sb.Append(", ");
}
}
sb.Append(")");
}
string pathFileName = GetFileBakName(fileName);
BackupHelper.WriteBackupFile(pathFileName, sb.ToString());
DownBackFile(pathFileName, fileName);
}
