protected void btnSav_Click(object sender, EventArgs e)
{
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
DateTime.Now.ToString();
string Code = GenerateReceiveNO();
DLL.User user = Session["dsUser"] as DLL.User;
if (string.IsNullOrEmpty(txtQu.Text))
{
txtQu.Text = user.Name;
}
string sql = "insert T_Question (Code,AgeGroupName,KeyWords,Text,Questioner,TheDate) VALUES('" + Code + "','" + ddlAgeGroup.SelectedValue + "','" + txtKeyWords.Text.Trim() + "','" + txtText.Text.Trim() + "','" + txtQu.Text.Trim() + "','" + DateTime.Now.ToString() + "' )";
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
Response.Write("<script>alert('成功！');window.location.href='Question.aspx'</script>");
}
}
protected void btnSav_Click(object sender, EventArgs e)
{
string id = Session["key"].ToString();
string code = Session["code"].ToString();
txtKeyWord.Text = Session["keyword"].ToString();
txtText.Text = Session["text"].ToString();
string time =DateTime.Now.ToString();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
DLL.User user = Session["dsUser"] as DLL.User;
if (string.IsNullOrEmpty(txtAnser.Text))
{
txtAnser.Text = user.Name;
}
string sql = "insert T_Solution (ancode,qucode,Stext,answer,thedate) VALUES('" + id + "','" + code + "','" + txtAns.Text + "','" + txtAnser.Text + "','" + time + "' )";
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
Response.Write("<script>alert('成功！');window.location.href='Solution.aspx'</script>");
}
}
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Set our view from the "main" layout resource
SetContentView(Resource.Layout.Main);
toolbar = FindViewById <Toolbar>(Resource.Id.toolbar);
first = FindViewById <EditText>(Resource.Id.fisrtName);
last = FindViewById <EditText>(Resource.Id.secondName);
email = FindViewById <EditText>(Resource.Id.email);
phone = FindViewById <EditText>(Resource.Id.phone);
parent = FindViewById <EditText>(Resource.Id.parentName);
addChild = FindViewById <Button>(Resource.Id.addChildDB);
viewChild = FindViewById <Button>(Resource.Id.getChildDB);
SetActionBar(toolbar);
databaseAdapter = new DatabaseAdapter(this);
addChild.Click += (sender, e) =>
{
DatabaseCalls.AddChild(first.Text, last.Text, phone.Text, email.Text, parent.Text, this, databaseAdapter);
};
viewChild.Click += (sender, e) =>
{
DatabaseCalls.GetChildren(this, databaseAdapter);
};
}
public JsonResult GetAllUsers()
{
IEnumerable <IUser> temp = null;
IDatabaseAdapter adapter = DatabaseAdapter.Instance();
//adapter = (DatabaseAdapter)adapter;
try
{
temp = adapter.GetAllUsers();
}
catch (System.Exception e)
{
throw new System.ArgumentException("Error in getAllUsers() request Nadina" + e);
}
try
{
return(Json(temp.ToList(), JsonRequestBehavior.AllowGet));
}
catch (System.Exception e)
{
throw new System.ArgumentException("Error in JSon request" + e);
}
}
private string GenerateReceiveNO()// 查询数据库最大记录数生成最大编号
{
//String strConn = System.Configuration.ConfigurationSettings.AppSettings["SqlConnStr"];
DateTime dtNow = DateTime.Now;
string strNO = "";
string strNO1 = dtNow.Year.ToString() + dtNow.Month.ToString("00") + dtNow.Day.ToString("00");
string strSQL = "Select MAXID From [S_MaxId] Where MaxId like '" + strNO1 + "%'";
DataSet ds = new DataSet(); ;
//ds = sqlInsert.GetDataSet(strSQL);
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(ds, CommandType.Text, strSQL, null);
if (ds.Tables[0].Rows.Count >= 1)
{
string iMaxId = ds.Tables[0].Rows[0]["MAXID"].ToString();
int Id = Convert.ToInt32(iMaxId.Substring(8)) + 1;
//TxtPCName.Text = iMaxId + "|" + Id.ToString() + "|" + (Id <= 999).ToString();
strNO = dtNow.Year.ToString() + dtNow.Month.ToString("00") + dtNow.Day.ToString("00") + ((Id <= 999) ? Id.ToString("000") : Id.ToString());
//sqlInsert.SQLExecuteNonQuery("update [S_MaxId] set MaxId='" + strNO + "' where MaxId like '" + iMaxId + "'");
string sql1 = "update [S_MaxId] set MaxId='" + strNO + "' where MaxId like '" + iMaxId + "'";
int isSuccess = ExecuteSql(sql1);
}
else
{
strNO = dtNow.Year.ToString() + dtNow.Month.ToString("00") + dtNow.Day.ToString("00") + "001";
//sqlInsert.SQLExecuteNonQuery("insert into [S_MaxId](MaxId) values ('" + strNO + "')");
string sql = "insert into [S_MaxId](MaxId) values ('" + strNO + "')";
int isSuccess = ExecuteSql(sql);
}
return strNO;
}
public JsonResult EditUser(string userName, string firstName, string lastName, string houseId, string phonenumber, string email, bool isAdmin, string userId)
{
IDatabaseAdapter adapter = DatabaseAdapter.Instance();
IUser us = adapter.GetUserById(new Guid(userId));
User updatedUser = new User();
updatedUser.UserId = us.UserId;
updatedUser.FirstName = firstName;;
updatedUser.LastName = lastName;
updatedUser.PhoneNo = phonenumber;
updatedUser.HouseId = new Guid(houseId);
updatedUser.Username = userName;
updatedUser.Email = email;
updatedUser.IsAdmin = isAdmin;
try
{
return(Json(adapter.EditUser(updatedUser)));
}
catch
{
return(Json("User not edited"));
}
}
public static void LoadAgeGroup(Page part, DropDownList ddlAgeGroup)
{
// 加载年龄段 ddlAgeGroup
string ageSelect = string.Format("SELECT * FROM T_AgeGroup order by length(substring_index(Sequence,'.',1)),substring_index(Sequence,'.',1) ");
//string conString = ConfigurationManager.AppSettings["SqlConnStr"];
//SqlDataAdapter dad = new SqlDataAdapter(selectCommand, conString);
//DataTable dtblDiscuss = new DataTable();
//dad.Fill(dtblDiscuss);
DataTable dtAgeGroup = new DataTable();
DatabaseAdapter sqlHelper2 = new DatabaseAdapter(part);
sqlHelper2.DbHelper.Fill(dtAgeGroup, CommandType.Text, ageSelect, null);
if (dtAgeGroup != null
&& dtAgeGroup.Rows != null
&& dtAgeGroup.Rows.Count > 0
)
{
ddlAgeGroup.Items.Clear();
foreach (DataRow row in dtAgeGroup.Rows)
{
if (row != null)
{
ddlAgeGroup.Items.Add(row["Name"].ToString());
}
}
}
}
private static X509Certificate2 ReadServerCertificate()
{
if (!_isFirstLaunch)
{
var pwdParam =
DatabaseAdapter.GetConfigurationObject(ConfigurationConstants.ServerCertificatePassword);
return(pwdParam != null
? new X509Certificate2(_serverCertificatePath, pwdParam.Value)
: new X509Certificate2(_serverCertificatePath));
}
string certOriginalPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
CommonConstants.DefaultCertificatesFolderName, CommonConstants.DefaultServerPfxCertificateName);
X509Certificate2 serverCert = new X509Certificate2(certOriginalPath);
try
{
Task.Run(() => FileManager.SafeCopy(certOriginalPath,
Path.Combine(CertificatesFolderPath, CommonConstants.DefaultServerPfxCertificateName)));
CertificatesProcessor.InstallCertificate(serverCert);
}
catch (Exception e)
{
_logger.Error(e, "Failed to copy default server certificate");
}
return(serverCert);
}
protected AbstractListView() : base()
{
// Init Window and references
this.SetPolicy(Gtk.PolicyType.Automatic, Gtk.PolicyType.Never);
dbAdapter = DatabaseAdapter.GetInstance();
editFrameAdapter = EditFrameAdapter.GetInstance();
tabView = new Gtk.Notebook();
// template init methods
PopulateTabView();
AddWithViewport(tabView);
// Set TabHead Behavior: only active tab has a visible button
tabView.SwitchPage += delegate
{
int i = 0;
foreach (var head in tabList)
{
if (i == tabView.CurrentPage)
{
head.button.Show();
}
else
{
head.button.Hide();
}
i++;
}
};
}
public JsonResult DeleteUser(string val)
{
string temp;
IDatabaseAdapter adapter = DatabaseAdapter.Instance();
try
{
/*string id;
* // Remove escape characters if needed - they may be automatically added in
* if (val.Length > 36)
* {
* id = val.Substring(1, 36);
* }
* else
* {
* id = val;
* }
* Guid userID = new Guid(id);*/
// Send request to database adapter
temp = adapter.DeleteUser(ConvertStringToGuid(val));
}
catch
{
return(Json("User not deleted"));
}
return(Json(temp));
}
public JsonApiOutput <IEnumerable <CinemaOutputModel> > searchByCinemaName([FromUri] string pattern)
{
return
(new JsonApiOutput <IEnumerable <CinemaOutputModel> >(
DatabaseAdapter.QueryCinemaByName(pattern).Select <Cinema, CinemaOutputModel>(i => new CinemaOutputModel(i))
));
}
private static void checkUnits()
{
if (Units == null)
{
Units = DatabaseAdapter.RetrieveUnits();
}
}
public void TestJoin()
{
using (IDatabaseAdapter adapter = new DatabaseAdapter(m_Connection))
{
CountryEntity country = new CountryEntity();
country.Name = "Switzerland";
EntityUpdater insert = new EntityUpdater(country, UpdateAction.Insert);
adapter.CommitEntity <CountryEntity>(ref insert);
CheeseEntity emental = new CheeseEntity();
emental.Name = "Emmental";
emental.CountryID = country.CountryID;
insert = new EntityUpdater(emental, UpdateAction.Insert);
adapter.CommitEntity <CheeseEntity>(ref insert);
adapter.CommitEntity <CheeseEntity>(ref insert);
adapter.CommitEntity <CheeseEntity>(ref insert);
CheeseEntity[] swissCheese = adapter.FetchEntities <CheeseEntity>(
new EntityFetcher <CheeseEntity>(CountryEntity.CountryEntityFields.CountryID == country.CountryID)
.AddJoinPath(CheeseEntity.CheeseEntityFields.CountryID.Join(JoinType.Inner, CountryEntity.CountryEntityFields.CountryID)));
Assert.GreaterOrEqual(swissCheese.Length, 3);
}
}
public JsonApiOutput <IEnumerable <CinemaOutputModel> > searchCinemaByLocation([FromUri] LocationInputModel location)
{
return
(new JsonApiOutput <IEnumerable <CinemaOutputModel> >(
DatabaseAdapter.QueryCinemaByLocation(location.Latitude, location.Longitude, location.MaxRange)
.Select <Cinema, CinemaOutputModel>(i => new CinemaOutputModel(i))
));
}
public IHttpActionResult GetCinemasByMovieAndLocationAndDateRange(string imdbId, double latitude, double longitude, [FromUri] DateRangeInputModel dateRange, [FromUri] int maxRange = 50)
{
IEnumerable <Cinema> cinemas = DatabaseAdapter.QueryCinemaFromMovie(latitude, longitude, maxRange, imdbId, dateRange.StartDate, dateRange.EndDate).ToList();
return(Ok(new JsonApiOutput <IEnumerable <CinemaOutputModel> >(
cinemas.ToList().Select <Cinema, CinemaOutputModel>(c => new CinemaOutputModel(c)).ToList()
)));
}
public JsonApiOutput <IEnumerable <MovieOutputModel> > searchMoviesFromLocation([FromUri] DateRangeInputModel dateRange, [FromUri] LocationInputModel location)
{
return
(new JsonApiOutput <IEnumerable <MovieOutputModel> >(
DatabaseAdapter.QueryMoviesFromLocation(location.Latitude, location.Longitude, location.MaxRange, dateRange.StartDate, dateRange.EndDate)
.Select <Movie, MovieOutputModel>(i => new MovieOutputModel(i))
));
}
public void TestFetch()
{
using (IDatabaseAdapter adapter = new DatabaseAdapter(m_Connection))
{
CheeseEntity[] entities = adapter.FetchEntities(
new EntityFetcher <CheeseEntity>(CheeseEntity.CheeseEntityFields.CheeseID == 1));
}
}
static void Main()
{
DatabaseAdapter a = new DatabaseAdapter();
DataModel model = new DataModel(a);
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new ValueView(model, model));
}
public IHttpActionResult getProjectionsByCinemaAndImdbId(string imdbId, int cinemaId, [FromUri] DateRangeInputModel dateRange)
{
IQueryable <Projection> projections = DatabaseAdapter.QueryProjectionsByCinemaAndMovieAndDateRange(cinemaId, imdbId, dateRange.StartDate, dateRange.EndDate);
return(Ok(new JsonApiOutput <IEnumerable <ProjectionOutputModel> >(
projections.ToList().Select <Projection, ProjectionOutputModel>(p => new ProjectionOutputModel(p))
)
));
}
protected void btnSav_Click(object sender, EventArgs e)
{
DLL.User loginUser = Session["dsUser"] as DLL.User;
if (loginUser == null)
{
Response.Redirect(string.Format("Login.aspx?Redirect={0}", Request.Url.ToString()));
return;
}
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
if (!PubClass.IsNull(this.txtID.Text))
{
string sql = string.Format("update hbh_user set account='{0}',passwd='{1}',name='{2}',sex='{3}' ,birthday ='{4}',region ='{5}',address ='{6}',tel ='{7}',pic ='{8}',ModifiedBy='{9}',ModifiedOn='{10}',SysVersion=sysVersion+1,judge_user_Account ='{11}' where ID='{12}'"
, txtAccount.Text.Trim(), this.txtPassword.Text.Trim(), this.txtName.Text.Trim(), txtSex.Text.Trim(), txtBirthday.Text.Trim(), txtRegion.Text.Trim(),
txtAddress.Text.Trim(), txtTel.Text.Trim(), txtPic.ImageUrl.Trim(), loginUser.ID.ToString() + "_" + loginUser.Code + "_" + loginUser.Name,DateTime.Now, txtJudgeUser.Text.Trim(), txtID.Text.Trim()
);
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='AppUser.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), this.txtID.Text)
));
}
else
{
Response.Write(string.Format("<script>alert('页面异常！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='AppUser.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), this.txtID.Text)
));
return;
}
}
else
{
//这里是否加上judge_user_account
string sql = string.Format("insert into hbh_User (account,passwd,name,sex,birthday,region,address,tel,pic,CreatedOn,createdby,judge_user_account,sysversion) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}'); select @@IDENTITY ; ",
txtAccount.Text.Trim(), txtPassword.Text.Trim(), txtName.Text.Trim(), txtSex.Text.Trim(), txtBirthday.Text.Trim(), txtRegion.Text.Trim(), txtAddress.Text.Trim(), txtTel.Text.Trim(), txtPic.ImageUrl.Trim(),DateTime.Now,loginUser.ID.ToString() + "_" + loginUser.Code + "_" + loginUser.Name,txtJudgeUser.Text.Trim(),0);
object result = sqlHelper.DbHelper.ExecuteScalar(CommandType.Text, sql);
long id = PubClass.GetLong(result);
if (id > 0)
{
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='AppUser.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), id.ToString())
));
}
}
}
protected void btnSav_Click(object sender, EventArgs e)
{
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
if (!PubClass.IsNull(this.txtID.Text))
{
string sql = string.Format("update HBH_ParentChildCurriculum set stage='{0}' ,aboutAge='{1}',content='{2}' where ID={3}"
, txtStage.Text.Trim(),txtAboutAge.Text.Trim(),txtContent.Text.Trim(), txtID.Text.Trim()
);
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='ParentChildCurriculum.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), this.txtID.Text)
));
}
else
{
Response.Write(string.Format("<script>alert('页面异常！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='ParentChildCurriculum.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), this.txtID.Text)
));
return;
}
}
else
{
string sql = string.Format("insert into hbh_parentChildCurriculum (stage,content,aboutAge) values('{0}','{1}','{2}'); select @@IDENTITY ; ",
txtStage.Text.Trim(), txtContent.Text.Trim(), txtAboutAge.Text.Trim());
object result = sqlHelper.DbHelper.ExecuteScalar(CommandType.Text, sql);
long id = PubClass.GetLong(result);
if (id > 0)
{
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='ParentChildCurriculum.aspx?TreeType=1&Selected={1}';</script>"
, "ParentChildCurriculumShow.aspx", string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), id.ToString())
));
}
}
}
public MainPage()
{
InitializeComponent();
NavigationPage.SetHasNavigationBar(this, false);
UsernameEntry.Text = Setting.UserName;
PasswordEntry.Text = Setting.Password;
DatabaseAdapter = new DatabaseAdapter(App.DatabasePath);
}
public IHttpActionResult getProjectionById(int projectionId)
{
Projection projection = DatabaseAdapter.QueryProjectionByProjectionId(projectionId);
if (projection == null)
{
return(NotFound());
}
return(Ok(new JsonApiOutput <ProjectionOutputModel>(new ProjectionOutputModel(projection))));
}
protected void btnSav_Click(object sender, EventArgs e)
{
if (PubClass.IsNull(txtMessage.Text))
{
Response.Write(string.Format("<script>alert('关于我们不可为空！');</script>", Request.Url.ToString()));
txtMessage.Focus();
return;
}
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
if (!PubClass.IsNull(this.txtID.Text))
{
if (!PubClass.IsNull(this.txtMessage.Text))
{
string sql = string.Format("update hbh_aboutus set aboutus_content='{0}' where ID={1}"
, txtMessage.Text, txtID.Text.Trim()
);
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
Response.Write("<script>alert('成功！');</script>");
}
else
{
Response.Write("<script>alert('失败！');</script>");
//Response.Write(string.Format("<script>alert('页面异常！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='MenuTree.aspx?TreeType=2&Selected={1}';</script>"
// , Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), this.txtParamID.Text)
// ));
return;
}
}
}
else
{
string sql = string.Format("insert hbh_aboutus (aboutus_content) VALUES('{0}' ) ; select @@IDENTITY "
, txtMessage.Text
);
object result = sqlHelper.DbHelper.ExecuteScalar(CommandType.Text, sql);
long id = PubClass.GetLong(result);
if (id > 0)
{
Response.Write(string.Format("<script>alert('成功！');</script>")); // Request.Url.ToString()
}
}
}
public static void Main(string[] args)
{
var adapter = new DatabaseAdapter("server=127.0.0.1;database=content-bank;uid=local_admin;pwd=password");
var database = adapter.GetDatabase();
var table = database.Tables.First();
var sql = adapter.ComposeSql(table);
Console.WriteLine(sql);
Console.ReadLine();
}
public IHttpActionResult GetMoviesInSpecificCinema(int cinemaId, [FromUri] DateRangeInputModel dateRange)
{
Cinema cinema = DatabaseAdapter.QueryCinemaByCinemaId(cinemaId);
return(Ok(new JsonApiOutput <IEnumerable <MovieOutputModel> >(
DatabaseAdapter.QueryMoviesInCinema(cinema,
dateRange.StartDate,
dateRange.EndDate).ToList()
.Select <Movie, MovieOutputModel>(m => new MovieOutputModel(m)).ToList()
)
));
}
protected void btnSav_Click(object sender, EventArgs e)
{
string c = this.txtCode.Text;
string strsql = string.Format("select * from T_Position where Code ='{0}'", c);
DataTable dts = new DataTable();
dts = query(strsql);
if (dts.Rows.Count> 0)
{
Response.Write("<script>alert('编码重复！');window.location.href='Position.aspx'</script>");
}
else if (string.IsNullOrEmpty(txtCode.Text.Trim()))
{
Response.Write("<script>alert('编码不可为空！');window.location.href='Position.aspx'</script>");
return;
}
else {
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
string a ;
a=DDSex.SelectedValue;
//string sql = "insert T_Position (Code,Name,Type,Sex,Level,Address,Intro) VALUES('"
// + txtCode.Text.Trim() + "','" + txtName.Text.Trim() + "','" + CommonHelper.GetString(txtType.Text.Trim())
// + "','" + DDSex.SelectedValue + "','" + CommonHelper.GetString(txtLevel.Text.Trim())
// + "','" + CommonHelper.GetString(txtAddress.Text.Trim()) + "','" + CommonHelper.GetString(txtIntro.Text.Trim()) + "' )";
string sql = "insert T_Position (Code,Name,Sex,Level,Address,Intro) VALUES('"
+ txtCode.Text.Trim() + "','" + txtName.Text.Trim() // + "','" + CommonHelper.GetString(txtType.Text.Trim())
+ "','" + DDSex.SelectedValue + "','" + CommonHelper.GetString(txtLevel.Text.Trim())
+ "','" + CommonHelper.GetString(txtAddress.Text.Trim()) + "','" + CommonHelper.GetString(txtIntro.Text.Trim()) + "' )";
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
Response.Write("<script>alert('成功！');window.location.href='ShowPosition.aspx'</script>");
}
}
}
public IHttpActionResult getProjectionsOfMultipleImdbsForSameCinemaId(int cinemaId, [FromBody] List <string> imdbIds, [FromUri] DateRangeInputModel dateRange)
{
Dictionary <string, IEnumerable <ProjectionOutputModel> > result =
imdbIds.Aggregate <string, Dictionary <string, IEnumerable <ProjectionOutputModel> > >(new Dictionary <string, IEnumerable <ProjectionOutputModel> >(),
(current, next) =>
{
var cinemaProjections = DatabaseAdapter.QueryProjectionsByCinemaAndMovieAndDateRange(cinemaId, next, dateRange.StartDate, dateRange.EndDate);
current[next] = cinemaProjections.ToList().Select(p => new ProjectionOutputModel(p));
return(current);
});
return(Ok(new JsonApiOutput <Dictionary <string, IEnumerable <ProjectionOutputModel> > >(result)));
}
//For calling staff details queries
public void GetStaffDetail(Staff e)
{
DatabaseAdapter.LoadStaffDetails(e);
e.ConsultationTimes = DatabaseAdapter.LoadEvents(e.ID);
e.ClassTimes = DatabaseAdapter.LoadStaffClasses(e.ID);
e.UnitsInvolvedWith = DatabaseAdapter.LoadStaffUnits(e.ID);
//For loading teaching room and unit (if Availablility is 'teaching now')
if (e.Availability == AvailabilityStatus.Teaching)
{
DatabaseAdapter.LoadCurrentTeachingDetails(e);
}
}
/*
* Parse dates and returns models with stories
*/
private JsonStorieModel getModelFromDate(DateTime date, int userid, int months, int years)
{
int month = date.Month <= months ? 12 - months + date.Month : date.Month - months;
int year = date.Month <= months ? date.Year - years - 1 : date.Year - years;
DateTime newDate = new DateTime(year, month, date.Day);
return(new JsonStorieModel
{
date = newDate.Year + "-" + newDate.Month + "-" + newDate.Day,
imageid = DatabaseAdapter.getImageId(newDate, userid)
});
}
//get classes by staff ID
public static List <Class> getClassesByStaffID(int _staffID)
{
List <Class> _listClasses = new List <Class>();
var _classes = DatabaseAdapter.RetrieveClasses();
var list = from item in _classes where item.Staff == _staffID select item;
foreach (var item in list)
{
_listClasses.Add(item);
Console.WriteLine(item);
}
return(_listClasses);
}
public IHttpActionResult GetCinemasByName(string pattern)
{
IEnumerable <Cinema> cinemas = DatabaseAdapter.QueryCinemaByName(pattern);
if (cinemas == null)
{
return(NotFound());
}
return(Ok(new JsonApiOutput <IEnumerable <CinemaOutputModel> >(
cinemas.Select <Cinema, CinemaOutputModel>(c => new CinemaOutputModel(c)))
));
}
public JsonResult GetUser(string val)
{
IDatabaseAdapter adapter = DatabaseAdapter.Instance();
try
{
return(Json(adapter.GetUserById(new Guid(val))));
}
catch (System.Exception e)
{
throw new System.ArgumentException("Error, can not get user. " + e);
}
}
public JsonResult GetUserByDeviceId(string deviceId)
{
IDatabaseAdapter adapter = DatabaseAdapter.Instance();
try
{
return(Json(adapter.GetUserByDeviceId(deviceId)));
} catch (Exception e)
{
Console.Write("No user found");
return(Json(Guid.Empty));
}
}
public DatabaseAdapterManager(string databaseFolder)
{
++_dbNumber;
DatabaseFolder = databaseFolder;
DatabaseAdapter = new DatabaseAdapter(
new DatabaseSettings()
{
DatabaseFolder = databaseFolder,
EnvironmentDatabaseName = $"EnvironmentData{_dbNumber}",
MonitoringDatabaseName = $"MonitoringData{_dbNumber}",
});
}
public IHttpActionResult GetCinemasByName(double latitude, double longitude, string pattern, [FromUri] int maxRange = 50)
{
IEnumerable <Cinema> cinemas = DatabaseAdapter.QueryCinemaByNameAndLocation(pattern, latitude, longitude, maxRange);
if (cinemas == null)
{
return(NotFound());
}
return(Ok(new JsonApiOutput <IEnumerable <CinemaOutputModel> >(
cinemas.Select <Cinema, CinemaOutputModel>(c => new CinemaOutputModel(c)))
));
}
public ProgrammChooserTBItem() : base()
{
dbAdapter = DatabaseAdapter.GetInstance();
listAdapter = ListFrameAdapter.GetInstance();
editAdapter = EditFrameAdapter.GetInstance();
cbVeranstaltung = new ComboBox();
cbInstanz = new ComboBox();
PrepareComboBoxes();
this.PackStart(cbVeranstaltung, false, true, 0);
this.PackStart(cbInstanz, false, true, 0);
}
/// <summary>
/// Improvements:
/// 1. Source and Destination database types can be provided as input
/// and then, appropriate strategy can be chosen through reflection.
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
IStrategy sqlStrategy = new SqlStrategy(ConfigurationManager.AppSettings["SqlConnectionString"]);
IStrategy mysqlStrategy = new MySqlStrategy(ConfigurationManager.AppSettings["MySqlConnectionString"]);
IParser source = new DatabaseAdapter(mysqlStrategy);
ISqlAdapter destination = new DatabaseAdapter(sqlStrategy);
Poller poller = new Poller(source, destination);
poller.Start();
Console.WriteLine("Enter key...");
Console.ReadKey();
}
protected void btnSav_Click(object sender, EventArgs e)
{
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
if (!PubClass.IsNull(this.txtID.Text))
{
string sql = string.Format("update hbh_Sub_Qes_T set question_Title='{0}',aboutAgeBegin='{1}',aboutAgeEnd='{2}',keywords='{3}' ,aboutAge ='{4}' where ID={5}"
, txtTitle.Text.Trim(),this.txtAboutAgeBegin.Text.Trim(),this.txtAboutAgeEnd.Text.Trim(),txtKeyWords.Text.Trim(), txtAboutAgeBegin.Text.Trim()+"-"+txtAboutAgeEnd.Text.Trim(),txtID.Text.Trim()
);
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='SubjectivityQuestion_T.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), this.txtID.Text)
));
}
else
{
Response.Write(string.Format("<script>alert('页面异常！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='SubjectivityQuestion_T.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), this.txtID.Text)
));
return;
}
}
else
{
string sql = string.Format("insert into hbh_Sub_Qes_T (question_title,keyWords,aboutAgeBegin,aboutAgeEnd,aboutAge) values('{0}','{1}','{2}','{3}','{4}'); select @@IDENTITY ; ",
txtTitle.Text.Trim(), txtKeyWords.Text.Trim(), txtAboutAgeBegin.Text.Trim(), txtAboutAgeEnd.Text.Trim(), txtAboutAgeBegin.Text.Trim()+"-"+txtAboutAgeEnd.Text.Trim());
object result = sqlHelper.DbHelper.ExecuteScalar(CommandType.Text, sql);
long id = PubClass.GetLong(result);
if (id > 0)
{
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='SubjectivityQuestion_T.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), id.ToString())
));
}
}
}
public DataTable query(string strsql)
{
//string SqlConnStr = ConfigurationManager.AppSettings["SqlConnStr"];
//SqlConnection conn = new SqlConnection(SqlConnStr);
//SqlDataAdapter sda = new SqlDataAdapter(strsql, conn);
//DataTable dt = new DataTable();
//sda.Fill(dt);
DataTable dt = new DataTable();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(dt, CommandType.Text, strsql, null);
return dt;
}
private void LoadData()
{
string selectCommand = "SELECT id,aboutUs_Content FROM hbh_aboutus ";
DataTable dtblDiscuss = new DataTable();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(dtblDiscuss, CommandType.Text, selectCommand, null);
if (dtblDiscuss != null
&& dtblDiscuss.Rows != null
&& dtblDiscuss.Rows.Count > 0
)
{
DataRow row = dtblDiscuss.Rows[0];
if (row != null)
{
txtMessage.Text = row["aboutUs_Content"].ToString();
txtID.Text = row["id"].ToString();
}
}
}
private DataSet GetEntities()
{
//DLL.User user = Session["dsUser"] as DLL.User;
//int a = user.Power;
//string c = user.Code.ToString();
DataSet ds = new DataSet();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
string sql = "select concat(left(Intro,5),'...') as IntroSimple,T_Position.* from T_Position where 1=1 ";
//if (a == 1) { sql += " and code ='" + c + "' "; }
//if (!string.IsNullOrEmpty(txtDeptCode.Text))
//{
// sql += " and DeptCode ='" + txtDeptCode.Text.Trim() + "' ";
//}
//if (!string.IsNullOrEmpty(txtCode.Text))
//{
// sql += " and Code ='" + txtCode.Text.Trim() + "' ";
//}
//if (!string.IsNullOrEmpty(txtAdr.Text))
//{
// sql += " and Adress ='" + txtAdr.Text.Trim() + "' ";
//}
sqlHelper.DbHelper.Fill(ds, CommandType.Text, sql, null);
return ds;
}
protected void btnDelete_Click(object sender, EventArgs e)
{
if (!PubClass.IsNull(txtID.Text))
{
string id = txtID.Text;
//string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
string SqlStr = "delete from T_Question where id=" + id;
try
{
//SqlConnection conn = new SqlConnection(connStr);
//if (conn.State.ToString() == "Closed") conn.Open();
//SqlCommand comm = new SqlCommand(SqlStr, conn);
//comm.ExecuteNonQuery();
//comm.Dispose();
//if (conn.State.ToString() == "Open") conn.Close();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, SqlStr);
//Response.Write(string.Format("<script>alert('删除成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='MenuTree.aspx?TreeType=0&Selected={1}';</script>", "MenuAdd.aspx"
// , string.Format("{0}-{1}", ExtendMethod.GetPageParam(this,"TreeType"), this.txtParamID.Text)));
Response.Write(string.Format("<script>alert('删除成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='MenuTree.aspx?TreeType=1&Selected={1}';</script>"
, "QuestionShow.aspx", string.Format("{0}-{1}", ExtendMethod.GetPageParam(this,"TreeType"), this.txtParamID.Text)
));
}
catch (Exception ex)
{
Response.Write("数据库错误，错误原因：" + ex.Message);
Response.End();
}
}
}
protected void btnSav_Click(object sender, EventArgs e)
{
// txtParentMenuName
if (PubClass.IsNull(txtParentMenuID.Text))
{
Response.Write(string.Format("<script>alert('上层目录不可为空！');</script>", Request.Url.ToString()));
txtTitle.Focus();
return;
}
if (PubClass.IsNull(txtKeyWords.Text))
{
Response.Write(string.Format("<script>alert('关键字不可为空！');</script>", Request.Url.ToString()));
txtKeyWords.Focus();
return;
}
if (PubClass.IsNull(txtTitle.Text))
{
Response.Write(string.Format("<script>alert('问题标题不可为空！');</script>", Request.Url.ToString()));
txtTitle.Focus();
return;
}
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
if (!PubClass.IsNull(this.txtID.Text))
{
// 修改选择目录
if (this.txtID.Text.Equals(this.txtParamID.Text))
{
string sql = string.Format("update T_Question set KeyWords='{0}',Title='{1}',ParentMenu={2},AgeGroupName='{3}',Questioner='{4}',Description='{5}' where ID={6}"
, txtKeyWords.Text.Trim(), txtTitle.Text.Trim()
, CommonHelper.GetStringWithNull(this.txtParentMenuID.Text.Trim())
, txtAgeGroupName.Text.Trim(), txtQuestioner.Text.Trim(), txtDescription.Text.Trim()
, txtID.Text.Trim()
);
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
//Response.Write("<script>alert('成功！');window.src='MenuAdd.aspx'</script>");
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='MenuTree.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this,"TreeType"), this.txtParamID.Text)
));
}
}
else
{
Response.Write(string.Format("<script>alert('页面异常！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='MenuTree.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this,"TreeType"), this.txtParamID.Text)
));
return;
}
}
// 新增子目录
else
{
// KeyWords='{0}',Text='{1}',ParentMenu='{2}',AgeGroupName='{3}',Questioner='{4}'
//string sql = "insert T_Question (Code,Name,Level,ParentMenu) VALUES('" + txtCode.Text.Trim() + "','" + txtName.Text.Trim() + "','" + txtLevel.Text.Trim() + "','" + txtParentMenuID.Text.Trim() + "' )";
string sql = string.Format("insert T_Question (KeyWords,Title,ParentMenu,AgeGroupName,Questioner,Description) VALUES('{0}','{1}',{2},'{3}','{4}','{5}' )"
, txtKeyWords.Text.Trim(), txtTitle.Text.Trim()
, CommonHelper.GetStringWithNull(this.txtParentMenuID.Text.Trim())
, this.txtAgeGroupName.Text
,txtQuestioner.Text.Trim(),txtDescription.Text.Trim()
);
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='MenuTree.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this,"TreeType"), this.txtParamID.Text)
));
}
}
}
//private void DatasBind()
//{
// DataSet ds = GetEntities();
// GridView.DataSource = ds;
// //this.AspNetPager1.RecordCount = ds.Tables[0].Rows.Count;
// PagedDataSource pds = new PagedDataSource();
// pds.AllowPaging = true;
// //pds.PageSize = AspNetPager1.PageSize;
// // pds.CurrentPageIndex = AspNetPager1.CurrentPageIndex - 1;
// pds.DataSource = ds.Tables[0].DefaultView;
// //lvQuestion.DataSource = ds;
//}
private DataSet GetEntities(int pageSize,int pageIndex)
{
if (pagerQuestion.CurrentPageIndex != pageIndex)
{
pagerQuestion.CurrentPageIndex = pageIndex;
}
//if (pagerQuestion2.CurrentPageIndex != pageIndex)
//{
// pagerQuestion2.CurrentPageIndex = pageIndex;
//}
DataSet ds = new DataSet();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
string a = " 1=1 ";
string strAge = ddlAgeGroup.SelectedValue.ToString();
//string KeyWords = txtKeyWords.Text.ToString();
string strKeyWords = txtQuestionText.Text.ToString();
string questionText = txtQuestionText.Text.ToString();
a += " and a.AgeGroupName = '" + strAge + "' ";
////a += " and (a.KeyWords like '%" + KeyWords + "%'" + " or a.Text like '%" + KeyWords + "%')";
//if (!PubClass.IsNull(KeyWords))
//{
// string[] keys = KeyWords.Split(Const_KeySplitFlag);
// if (keys.Length > 0)
// {
// a += " and (1=0 ";
// foreach (string strKey in keys)
// {
// if (!PubClass.IsNull(strKey))
// {
// a += " or a.KeyWords like '%" + strKey + "%'";
// }
// }
// a += ")";
// }
//}
//// replace(a.Title,'宝宝','<font color=''#FF0000''>宝宝</font>') as Title
//if (!PubClass.IsNull(questionText))
//{
// string[] keys = questionText.Split(Const_KeySplitFlag);
// if (keys.Length > 0)
// {
// a += " and (1=0 ";
// foreach (string strKey in keys)
// {
// if (!PubClass.IsNull(strKey))
// {
// a += " or a.Title like '%" + strKey + "%'";
//// sbTitle.Append(string.Format("replace(a.Title,'{0}','<font color=''#FF0000''>{0}</font>')", strKey));
// }
// }
// a += ")";
// }
//}
// replace(a.Title,'宝宝','<font color=''#FF0000''>宝宝</font>') as Title
//StringBuilder sbTitle = new StringBuilder();
string strKeywordTitle = "a.Title";
// ifnull(a.KeyWords,'') as KeyWords
string strKeywordKeyword = "ifnull(a.KeyWords,'')";
List<string> lstReplaceKey = new List<string>();
if (!PubClass.IsNull(questionText))
{
string[] keys = questionText.Split(Const_KeySplitFlag);
if (keys.Length > 0)
{
a += " and (1=0 ";
foreach (string strKey in keys)
{
if (!PubClass.IsNull(strKey))
{
a += " or a.KeyWords like '%" + strKey + "%'";
a += " or a.Title like '%" + strKey + "%'";
strKeywordTitle = strKeywordTitle.Replace("a.Title"
, string.Format("replace(a.Title,'{0}','<font color=''#FF0000''>{0}</font>')", strKey)
);
strKeywordKeyword = strKeywordKeyword.Replace("ifnull(a.KeyWords,'')"
, string.Format("replace(ifnull(a.KeyWords,''),'{0}','<font color=''#FF0000''>{0}</font>')", strKey)
);
if (!lstReplaceKey.Contains(strKey))
{
lstReplaceKey.Add(strKey);
}
}
}
a += ")";
}
}
//if (sbTitle.Length == 0)
//{
// sbTitle.Append("a.Title");
//}
//string sql = string.Format("select a.ID,a.Code,a.AgeGroupName,a.Text,a.KeyWords,a.Questioner,a.Memo,b.AnCode,b.QuCode,b.SText,b.Answer,b.Memo from T_Question a inner join T_Solution b on a.id=b.Question where {0} ", a);
// string sql = string.Format(@"
//select a.ID QuestionID,a.Code,a.AgeGroupName,a.Title,a.KeyWords,a.Questioner,a.Memo,-1 AnswerID,'' AnCode,'' QuCode,'' as Intro,'' SText,'' Answer,'' Memo
//from T_Question a
//where {0}
//union all
//select a.ID QuestionID,'' Code,'' AgeGroupName,'' Title,'' KeyWords,'' Questioner,'' Memo,b.ID AnswerID,b.AnCode,b.QuCode,b.Intro,b.SText,b.Answer,b.Memo
//from T_Question a inner join T_Solution b on a.id=b.Question
//where {0}
//order by QuestionID,AnswerID
//", a);
// replace(a.Title,'宝宝','<font color=''#FF0000''>宝宝</font>') as Title
// string sql = string.Format(@"
//select a.ID QuestionID,a.Code,a.AgeGroupName,{1} as Title,{2} as KeyWords,a.Questioner,a.Memo,b.ID AnswerID,b.AnCode,b.QuCode,case when ifnull(Intro,'') = '' then concat(left(replace(b.SText,'\r\n','<br><br>'),100),'...') else replace(Intro,'\r\n','<br><br>') end as Intro,b.SText,b.Answer,b.Memo,replace(replace(replace(b.SText,'\r\n','<br><br>'),char(10),'<br>'),char(13),'<br>') STextDiv
//from T_Question a inner join T_Solution b on a.id=b.Question
//where {0}
//order by QuestionID,AnswerID
//", a, strKeywordTitle, strKeywordKeyword);
//replace(b.SText,'\r\n','<br><br>') STextDiv
string sql = string.Format("call {0}('{1}','{2}',{3},{4})", "hbh_proc_selectByKeywords"
, strAge, strKeyWords , pageSize ,pageIndex
);
sqlHelper.DbHelper.Fill(ds, CommandType.Text, sql, null);
// 对关键字进行颜色处理
if (lstReplaceKey != null
&& lstReplaceKey.Count > 0
)
{
if (ds != null
&& ds.Tables != null
&& ds.Tables.Count > 0
)
{
DataTable table = ds.Tables[0];
List<string> lstReplaceFields = new List<string>();
lstReplaceFields.Add("Title");
//lstReplaceFields.Add("KeyWords");
//lstReplaceFields.Add("Intro");
//lstReplaceFields.Add("SText");
//lstReplaceFields.Add("STextDiv");
//string replaceField = "Title";
if (table != null
&& table.Columns != null
//&& table.Columns.Contains(replaceField)
&& lstReplaceFields.Count > 0
&& table.Rows != null
&& table.Rows.Count > 0
)
{
foreach (DataRow row in table.Rows)
{
foreach (string replaceField in lstReplaceFields)
{
if (row != null
&& table.Columns.Contains(replaceField)
&& row[replaceField] != null
)
{
foreach (string key in lstReplaceKey)
{
string replacedString = string.Format("<font color='#FF0000'>{0}</font>", key);
row[replaceField] = row[replaceField].ToString().Replace(key, replacedString);
}
}
}
}
}
}
}
return ds;
}
private void GridViewBind()
{
//string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
string SqlStr = "select a.ID,a.Code,a.AgeGroupName,a.Title,a.KeyWords,a.Questioner,a.Memo,b.AnCode,b.QuCode,SText as bt,b.Answer,b.Memo from T_Question a inner join T_Solution b on a.id=b.AnCode";
DataSet ds = new DataSet();
try
{
//SqlConnection conn = new SqlConnection(connStr);
//if (conn.State.ToString() == "Closed") conn.Open();
//SqlDataAdapter da = new SqlDataAdapter(SqlStr, conn);
//da.Fill(ds,SqlStr);
//if (conn.State.ToString() == "Open") conn.Close();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(ds, CommandType.Text, SqlStr, null);
// GridView.DataSource = ds.Tables[0].DefaultView;
// GridView.DataBind();
lvQuestion.DataSource = ds.Tables[0].DefaultView;
lvQuestion.DataBind();
}
catch (Exception ex)
{
Response.Write("数据库错误，错误原因：" + ex.Message);
Response.End();
}
}
protected void GridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
//GridView.Rows[e.RowIndex].Attributes.Add("onclick", "return confirm('你确认要删除吗?')");
string id = GridView.DataKeys[e.RowIndex].Values[0].ToString();
string SqlStr = "delete from T_Position where id=" + id;
try
{
//string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
// SqlConnection conn = new SqlConnection(connStr);
// if (conn.State.ToString() == "Closed") conn.Open();
// SqlCommand comm = new SqlCommand(SqlStr, conn);
// comm.ExecuteNonQuery();
// comm.Dispose();
// if (conn.State.ToString() == "Open") conn.Close();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, SqlStr);
GridView.EditIndex = -1;
GridViewBind();
Response.Write("<script language=javascript>alert('删除成功！')</script>");
}
catch (Exception ex)
{
Response.Write("数据库错误，错误原因：" + ex.Message);
Response.End();
}
}
private void LoadData()
{
string id = txtID.Text.Trim();
// 清空ID，防止ID查不到，错误更新；或者目录传过来的，也更新成问题ID
txtID.Text = string.Empty;
if (!PubClass.IsNull(id))
{
string where = string.Format("id={0}", id);
string selectCommand = string.Format("SELECT id,question_title,keyWords,aboutAgeBegin,aboutAgeEnd FROM HBH_Sub_qes_T where {0}", where);
string conString = ConfigurationManager.AppSettings["SqlConnStr"];
//SqlDataAdapter dad = new SqlDataAdapter(selectCommand, conString);
//DataTable dtblDiscuss = new DataTable();
//dad.Fill(dtblDiscuss);
DataTable dtblDiscuss = new DataTable();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(dtblDiscuss, CommandType.Text, selectCommand, null);
if (dtblDiscuss != null
&& dtblDiscuss.Rows != null
&& dtblDiscuss.Rows.Count > 0
)
{
DataRow row = dtblDiscuss.Rows[0];
if (row != null)
{
txtID.Text = row["ID"].ToString();
txtKeyWords.Text = row["KeyWords"].ToString();
//txtCode.Text = row["Code"].ToString();
txtTitle.Text = row["question_title"].ToString();
txtAboutAgeEnd.Text = row["aboutAgeEnd"].ToString();
txtAboutAgeBegin.Text = row["aboutAgeBegin"].ToString();
}
}
else
{
}
}
}
private void GridViewBind()
{
//string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
string SqlStr = "Select concat(left(Intro,5),'...') as IntroSimple,T_Position.* from T_Position";
DataSet ds = new DataSet();
try
{
//SqlConnection conn = new SqlConnection(connStr);
//if (conn.State.ToString() == "Closed") conn.Open();
//SqlDataAdapter da = new SqlDataAdapter(SqlStr, conn);
//da.Fill(ds, "T_Position");
//if (conn.State.ToString() == "Open") conn.Close();
DataTable dtblDiscuss = new DataTable();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(ds, CommandType.Text, SqlStr, null);
GridView.DataSource = ds.Tables[0].DefaultView;
GridView.DataBind();
}
catch (Exception ex)
{
Response.Write("数据库错误，错误原因：" + ex.Message);
Response.End();
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
if (!PubClass.IsNull(txtID.Text))
{
string id = txtID.Text;
//string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
string SqlStr = "delete from hbh_aboutus where id=" + id;
try
{
//SqlConnection conn = new SqlConnection(connStr);
//if (conn.State.ToString() == "Closed") conn.Open();
//SqlCommand comm = new SqlCommand(SqlStr, conn);
//comm.ExecuteNonQuery();
//comm.Dispose();
//if (conn.State.ToString() == "Open") conn.Close();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, SqlStr);
Response.Write("<script>alert('删除成功！');</script>");
}
catch (Exception ex)
{
Response.Write("数据库错误，错误原因：" + ex.Message);
Response.End();
}
}
}
protected void btnSav_Click(object sender, EventArgs e)
{
if (PubClass.IsNull(txtText.Text))
{
Response.Write(string.Format("<script>alert('答案不可为空！');</script>", Request.Url.ToString()));
txtText.Focus();
return;
}
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
if (!PubClass.IsNull(this.txtID.Text))
{
// 修改选择目录
if (this.txtID.Text.Equals(this.txtParamID.Text))
{
string sql = string.Format("update T_Solution set SText='{0}',Intro='{1}',Question='{2}',Memo='{3}',Answer='{4}' where ID={5}"
, txtText.Text.Trim(), txtIntro.Text.Trim(), this.txtQuestion.Text.Trim(), this.txtMemo.Text.Trim(), txtAnswer.Text.Trim()
, txtID.Text.Trim()
);
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
//Response.Write("<script>alert('成功！');window.src='MenuAdd.aspx'</script>");
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='MenuTree.aspx?TreeType=2&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this,"TreeType"), this.txtParamID.Text)
));
}
}
else
{
Response.Write(string.Format("<script>alert('页面异常！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='MenuTree.aspx?TreeType=2&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this,"TreeType"), this.txtParamID.Text)
));
return;
}
}
// 新增子目录
else
{
// SText='{0}',Text='{1}',Question='{2}',Memo='{3}',Answer='{4}'
//string sql = "insert T_Solution (Code,Name,Level,ParentMenu) VALUES('" + txtCode.Text.Trim() + "','" + txtName.Text.Trim() + "','" + txtLevel.Text.Trim() + "','" + txtParentMenuID.Text.Trim() + "' )";
string sql = string.Format("insert T_Solution (SText,Intro,Question,Memo,Answer) VALUES('{0}','{1}','{2}','{3}','{4}' ) ; select @@IDENTITY "
, txtText.Text.Trim(), txtIntro.Text.Trim(), txtQuestion.Text.Trim(), this.txtMemo.Text.Trim(), this.txtAnswer.Text.Trim()
);
object result = sqlHelper.DbHelper.ExecuteScalar(CommandType.Text, sql);
long id = PubClass.GetLong(result);
if (id > 0)
{
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='MenuTree.aspx?TreeType=2&Selected={1}';</script>"
, Request.Url.ToString() // , string.Format("AnswerShow.aspx?ID={0}&Code={1}&=Name{2}", id, Request.QueryString["Code"], Request.QueryString["Name"])
, string.Format("{0}-{1}", ExtendMethod.GetPageParam(this,"TreeType"), this.txtParamID.Text)
)); // Request.Url.ToString()
}
}
}
//后面查看消息用
private void LoadData()
{
string id = txtID.Text.Trim();
// 清空ID，防止ID查不到，错误更新；或者目录传过来的，也更新成问题ID
txtID.Text = string.Empty;
if (!PubClass.IsNull(id))
{
//推送消息者只能看到自己推送的消息
string where = string.Format("id={0}", id);
string selectCommand = string.Format("SELECT id,message_title,message_content,messDate,user_id,poster_id,isRead,subhead,aboutAgeBegin,aboutAgeEnd FROM hbh_message where {0}", where);
string conString = ConfigurationManager.AppSettings["SqlConnStr"];
//SqlDataAdapter dad = new SqlDataAdapter(selectCommand, conString);
//DataTable dtblDiscuss = new DataTable();
//dad.Fill(dtblDiscuss);
DataTable dtblDiscuss = new DataTable();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(dtblDiscuss, CommandType.Text, selectCommand, null);
if (dtblDiscuss != null
&& dtblDiscuss.Rows != null
&& dtblDiscuss.Rows.Count > 0
)
{
DataRow row = dtblDiscuss.Rows[0];
if (row != null)
{
txtID.Text = row["id"].ToString();
txtPosterID.Text = row["poster_id"].ToString();
txtMessageTitle.Text = row["message_title"].ToString();
//txtCode.Text = row["Code"].ToString();
txtMessageContent.Text = row["message_content"].ToString();
txtUserID.Text = row["user_id"].ToString();
txtSubHead.Text = row["subhead"].ToString();
txtAboutAgeBegin.Text = row["aboutAgeBegin"].ToString();
txtAboutAgeEnd.Text = row["aboutAgeEnd"].ToString();
}
}
else
{
}
}
}
//private void LoadAppUser()
//{
// SetDs();
// if (ds != null
// && ds.Tables != null
// && ds.Tables.Count > 0
// )
// {
// DataTable dt = ds.Tables[0];
// if (dt != null && dt.Rows != null && dt.Rows.Count > 0 )
// {
// List<string> dataSource = new List<string>();
// foreach (DataRow dr in dt.Rows)
// {
// dataSource.Add(dr["account"].ToString());
// ddlUsers.Items.Add(dr["account"].ToString());
// }
// ddlUsers.DataSource = dataSource;
// }
// }
//}
//protected void ddlUser_ChangeClick(object sender, EventArgs e)
//{
// if (ds != null
// && ds.Tables != null
// && ds.Tables.Count > 0
// )
// {
// DataTable dt = ds.Tables[0];
// if (dt != null && dt.Rows != null && dt.Rows.Count > 0 )
// {
// foreach(DataRow dr in dt.Rows)
// {
// if(dr["account"].ToString().Equals( ddlUsers.SelectedValue))
// {
// txtUserCode.Text = dr["account"].ToString();
// txtUserID.Text = dr["id"].ToString();
// txtUserName.Text = dr["name"].ToString();
// }
// }
// }
// }
//}
#endregion
protected void btnSav_Click(object sender, EventArgs e)
{
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
if (!PubClass.IsNull(this.txtID.Text))
{
string sql = string.Format("update hbh_message set message_Content='{0}',message_Title='{1}',poster_id='{2}',subhead='{3}',aboutAgeBegin='{4}',aboutAgeEnd='{5}' where ID={6}"
, this.txtMessageContent.Text.Trim(), this.txtMessageTitle.Text.Trim(), this.txtPosterID.Text.Trim(),this.txtSubHead.Text.Trim(),this.txtAboutAgeBegin.Text.Trim(),this.txtAboutAgeEnd.Text.Trim(), txtID.Text.Trim()
);
int row = sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql);
if (row > 0)
{
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='Message.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), this.txtID.Text)
));
}
else
{
Response.Write(string.Format("<script>alert('页面异常！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='Message.aspx?TreeType=1&Selected={1}';</script>"
, Request.Url.ToString(), string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), this.txtID.Text)
));
return;
}
}
else
{
string sql = string.Format("insert hbh_message (message_title,message_Content,messDate,user_id,poster_id,subhead,aboutAgeBegin,aboutAgeEnd) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}' ) ; select @@IDENTITY "
, txtMessageTitle.Text.Trim(), txtMessageContent.Text.Trim(), DateTime.Now, txtUserID.Text.Trim(), txtPosterID.Text.Trim(), txtSubHead.Text.Trim(), txtAboutAgeBegin.Text.Trim(), txtAboutAgeEnd.Text.Trim()
);
object result = sqlHelper.DbHelper.ExecuteScalar(CommandType.Text, sql);
long id = PubClass.GetLong(result);
if (id > 0)
{
Response.Write(string.Format("<script>alert('成功！');window.location.href='{0}';window.parent.document.getElementById('fMenuTree').src='Message.aspx?TreeType=1&Selected={1}';</script>"
,"MessageShow.aspx", string.Format("{0}-{1}", ExtendMethod.GetPageParam(this, "TreeType"), this.txtID.Text)
));
}
}
}
private DataTable GetTreeViewData(int treeType) //获取数据
{
string strSelect = ddlSubQuestion.SelectedValue.ToString();
string selectCommand = string.Empty;
if (strSelect.Equals( "未回答的"))
{
selectCommand = @"select id,sub_Qes_title,question_Content,keywords,messDate,questioner,subQue_Result,aboutAge from HBH_SubQuestion where subQue_Result is null or subQue_Result = ''";
}
else if (strSelect.Equals( "全部"))
{
selectCommand = @"select id,sub_Qes_title,question_Content,keywords,messDate,questioner,subQue_Result,aboutAge from HBH_SubQuestion";
}
if (string.IsNullOrEmpty(selectCommand))
{
ddlSubQuestion.Visible = true;
btnSearch.Visible = true;
return null;
}
DataTable dtblDiscuss = new DataTable();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(dtblDiscuss, CommandType.Text, selectCommand, null);
return dtblDiscuss;
}
private void LoadMenu()
{
string id = txtID.Text.Trim();
// 清空ID，防止ID查不到，错误更新；或者目录传过来的，也更新成问题ID
txtID.Text = string.Empty;
if (!PubClass.IsNull(id))
{
string where = string.Format("menu.ID={0}", id);
string selectCommand = string.Format("SELECT menu.*,parentMenu.Code as ParentCode,parentMenu.Name as ParentName FROM T_Menu menu left join T_Menu parentMenu on menu.ParentMenu = parentMenu.ID where {0}", where);
//string conString = ConfigurationManager.AppSettings["SqlConnStr"];
//SqlDataAdapter dad = new SqlDataAdapter(selectCommand, conString);
//DataTable dtblDiscuss = new DataTable();
//dad.Fill(dtblDiscuss);
DataTable dtblDiscuss = new DataTable();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(dtblDiscuss, CommandType.Text, selectCommand, null);
if (dtblDiscuss != null
&& dtblDiscuss.Rows != null
&& dtblDiscuss.Rows.Count > 0
)
{
DataRow row = dtblDiscuss.Rows[0];
if (row != null)
{
txtParentMenuID.Text = row["ID"].ToString();
txtParentMenuName.Text = row["Name"].ToString();
txtAgeGroup.Text = row["AgeGroup"].ToString();
txtAgeGroupName.Text = row["AgeGroupName"].ToString();
}
}
}
}
protected void GridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string id = GridView.DataKeys[e.RowIndex].Values[0].ToString();
//string code = ((TextBox)GridView.Rows[e.RowIndex].FindControl("txtCode")).Text;
string name = ((TextBox)GridView.Rows[e.RowIndex].FindControl("txtName")).Text;
//string type = ((TextBox)GridView.Rows[e.RowIndex].FindControl("txtType")).Text;
//string sex = ((TextBox)GridView.Rows[e.RowIndex].FindControl("txtSex")).Text;
string sex = ((DropDownList)GridView.Rows[e.RowIndex].FindControl("DDSex")).SelectedValue;
string level = ((TextBox)GridView.Rows[e.RowIndex].FindControl("txtLevel")).Text;
string address = ((TextBox)GridView.Rows[e.RowIndex].FindControl("txtAddress")).Text;
string intro = ((TextBox)GridView.Rows[e.RowIndex].FindControl("txtIntro")).Text;
//string SqlStr = "update T_Position set name='" + name + "' , type='" + type + "' , sex='" + sex + "' , level='" + level + "' , address='" + address + "' , intro='" + intro + "' where id=" + id;
string SqlStr = "update T_Position set name='" + name + "' , sex='" + sex + "' , level='"
+ level + "' , address='" + address + "' , intro='" + intro + "' where id=" + id;
try
{
//string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
// SqlConnection conn = new SqlConnection(connStr);
// if (conn.State.ToString() == "Closed") conn.Open();
// SqlCommand comm = new SqlCommand(SqlStr, conn);
// comm.ExecuteNonQuery();
// comm.Dispose();
// if (conn.State.ToString() == "Open") conn.Close();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, SqlStr);
GridView.EditIndex = -1;
GridViewBind();
}
catch (Exception ex)
{
Response.Write("数据库错误，错误原因：" + ex.Message);
Response.End();
}
}
private void LoadData()
{
if (!PubClass.IsNull(this.txtID.Text))
{
string where = string.Format("answer.ID={0}", this.txtID.Text);
string selectCommand = string.Format("SELECT answer.*,question.Code as QuestionCode,question.Title as QuestionTitle,menu.AgeGroupName FROM T_Solution answer left join T_Question question on answer.question = question.ID left join T_Menu menu on question.ParentMenu = menu.ID where {0}", where);
//string conString = ConfigurationManager.AppSettings["SqlConnStr"];
//SqlDataAdapter dad = new SqlDataAdapter(selectCommand, conString);
//DataTable dtblDiscuss = new DataTable();
//dad.Fill(dtblDiscuss);
DataTable dtblDiscuss = new DataTable();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(dtblDiscuss, CommandType.Text, selectCommand, null);
if (dtblDiscuss != null
&& dtblDiscuss.Rows != null
&& dtblDiscuss.Rows.Count > 0
)
{
DataRow row = dtblDiscuss.Rows[0];
if (row != null)
{
txtText.Text = row["SText"].ToString();
txtAgeGroupName.Text = row["AgeGroupName"].ToString();
txtQuestion.Text = row["Question"].ToString();
txtQuestionTitle.Text = row["QuestionTitle"].ToString();
// txtText.Text = row["SText"].ToString();
txtAnswer.Text = row["Answer"].ToString();
txtMemo.Text = row["Memo"].ToString();
txtIntro.Text = row["Intro"].ToString();
}
}
}
}
private void BatchImportDatabase(DataSet ds)
{
try
{
if (ds != null
&& ds.Tables != null
&& ds.Tables.Count > 0
)
{
string Qes1PutTime = "1";
string Qes2PutTime = "5";
Guid guid = Guid.NewGuid();
string strGuid = guid.ToString();
string strUserName = string.Empty;
DLL.User loginUser = Session["dsUser"] as DLL.User;
if (loginUser == null)
{
strUserName = loginUser.Name;
}
string sql = "insert into hbh_t_importData (GUID,aboutAgeBegin,aboutAgeEnd,message_Title,message_content,messDate)values(?,?,?,?,?,now());";
// 每个表导入一次
foreach (DataTable table in ds.Tables)
{
if (table != null
// Sheet名不可为空
&& !PubClass.IsNull(table.TableName)
// Sheet名不可包含忽略字符
&& !table.TableName.Contains("#")
// 有行
&& table.Rows != null
&& table.Rows.Count > 0
&& table.Columns != null
// 多余两列
&& table.Columns.Count >= 2
)
{
int totalCount = table.Rows.Count;
string strTableName = table.TableName;
int BatchSize = 2000;
int loopNumber = PubClass.GetInt(Math.Ceiling(((decimal)totalCount / BatchSize)));
for (int j = 1; j <= loopNumber; j++)
{
int curCount = Math.Min(totalCount - BatchSize * (j - 1), BatchSize);
int preBatchIndex = BatchSize * (j - 1);
//TableTypeParameter[][] sqlParamArray = new TableTypeParameter[table.Rows.Count][];
TableTypeParameter[][] sqlParamArray = new TableTypeParameter[curCount][];
//foreach (DataRow row in table.Rows)
//for (int i = 0; i < table.Rows.Count; i++)
string month = "";//记录上条记录的月份
string LastMonth = ""; //记录上条记录的月份
int QestingSequence = 1;//记录当前记录是本月的第几周推送
int inQty = 2;//存储几周推送一镒
for (int i = 0; i < curCount; i++)
{
DataRow row = table.Rows[preBatchIndex + i];
TableTypeParameter[] curParamArr = new TableTypeParameter[5];
//当本记录的月分为空时表明单元格合并了取不到数据。那本记录的月分=上条记录的月份。
if (!string.IsNullOrEmpty(row[0].ToString().Trim()))
{
month = row[0].ToString().Trim();
}
if (!string.IsNullOrEmpty(LastMonth))
{
if (LastMonth.Equals(month))
{
QestingSequence += inQty;
}
else
{
QestingSequence = 1;
LastMonth = month;
}
}
else
LastMonth = month;
//string sql = "insert into hbh_t_importMessageData (GUID,aboutAgeBegin,aboutAgeEnd,message_Title,message_content,messDate)values(?,?,?,?,?,now());";
int endDay = QestingSequence + 1;//时间段结束
curParamArr[0] = new TableTypeParameter("GUID", strGuid);
curParamArr[1] = new TableTypeParameter("aboutAgeBegin", strTableName + month + QestingSequence + "周");
curParamArr[2] = new TableTypeParameter("aboutAgeEnd", strTableName + month + endDay + "周");
string row_title = "";
if (string.IsNullOrEmpty(row[1].ToString().Trim()))
row_title = "";
else
row_title = row[1].ToString().Trim();
string row_content = "";
if (string.IsNullOrEmpty(row[2].ToString().Trim()))
row_content = "";
else
row_content = row[2].ToString().Trim();
curParamArr[3] = new TableTypeParameter("message_Title", row_title);
curParamArr[4] = new TableTypeParameter("message_content", row_content);
sqlParamArray[i] = curParamArr;
}
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql, sqlParamArray);
}
}
}
// ,concat(Content,'(',AgeGroupName,')')
// ,(length(Sequence) - length(replace(Sequence,'.',''))) ,length(Sequence),length(replace(Sequence,'.',''))
/* // 效率有问题，所以换成增临时表来处理
-- 目录-更新
update T_Menu,T_ImportData imp2
set
T_Menu.Code = imp2.Content
,T_Menu.Name = concat(imp2.Sequence,' ',imp2.Content)
,T_Menu.AgeGroupName = imp2.AgeGroupName
where 1=1
and T_Menu.Sequence = imp2.Sequence
and imp2.GUID = '{0}'
and (length(imp2.Sequence) - length(replace(imp2.Sequence,'.',''))) < 3
;
*/
string strProcName = "hbh_proc_ImportSubQuestionData";
// 为了效率，改为存储过程
//string updateText = string.Format(updateSql, strGuid);
string updateText = string.Format("call {0}('{1}');", strProcName, strGuid);
DatabaseAdapter sqlHelper2 = new DatabaseAdapter(this);
sqlHelper2.DbHelper.ExecuteNonQuery(CommandType.Text, updateText);
}
}
catch (Exception ex)
{
Response.Write(string.Format("<script>alert('{0}')</script> ",ex.Message));
}
}
private void LoadQuestion()
{
string id = txtID.Text.Trim();
// 清空ID，防止ID查不到，错误更新；或者目录传过来的，也更新成问题ID
txtID.Text = string.Empty;
if (!PubClass.IsNull(id))
{
string where = string.Format(" question.ID={0} ", id);
string selectCommand = string.Format("select question.* from T_Question question where {0}", where);
//string conString = ConfigurationManager.AppSettings["SqlConnStr"];
//SqlDataAdapter dad = new SqlDataAdapter(selectCommand, conString);
//DataTable dtblDiscuss = new DataTable();
//dad.Fill(dtblDiscuss);
DataTable dtblDiscuss = new DataTable();
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.Fill(dtblDiscuss, CommandType.Text, selectCommand, null);
if (dtblDiscuss != null
&& dtblDiscuss.Rows != null
&& dtblDiscuss.Rows.Count > 0
)
{
DataRow row = dtblDiscuss.Rows[0];
if (row != null)
{
txtQuestion.Text = row["ID"].ToString();
txtQuestionTitle.Text = row["Title"].ToString();
txtAgeGroupName.Text = row["AgeGroupName"].ToString();
}
}
}
}
private void BatchImportDatabase(DataSet ds)
{
try
{
if (ds != null
&& ds.Tables != null
&& ds.Tables.Count > 0
)
{
string Qes1PutTime = "1";
string Qes2PutTime = "5";
Guid guid = Guid.NewGuid();
string strGuid = guid.ToString();
string strUserName = string.Empty;
DLL.User loginUser = Session["dsUser"] as DLL.User;
if (loginUser == null)
{
strUserName = loginUser.Name;
}
string sql = "insert into hbh_t_importData (GUID,aboutAgeBegin,aboutAgeEnd,message_Title,message_content,messDate)values(?,?,?,?,?,now());";
// 每个表导入一次
foreach (DataTable table in ds.Tables)
{
if (table != null
// Sheet名不可为空
&& !PubClass.IsNull(table.TableName)
// Sheet名不可包含忽略字符
&& !table.TableName.Contains("#")
// 有行
&& table.Rows != null
&& table.Rows.Count > 0
&& table.Columns != null
// 多余两列
&& table.Columns.Count >= 2
)
{
int totalCount = table.Rows.Count;
string strTableName = table.TableName;
int BatchSize = 2000;
int loopNumber = PubClass.GetInt(Math.Ceiling(((decimal)totalCount / BatchSize)));
for (int j = 1; j <= loopNumber; j++)
{
int curCount = Math.Min(totalCount - BatchSize * (j - 1), BatchSize);
int preBatchIndex = BatchSize * (j - 1);
//TableTypeParameter[][] sqlParamArray = new TableTypeParameter[table.Rows.Count][];
TableTypeParameter[][] sqlParamArray = new TableTypeParameter[curCount][];
//foreach (DataRow row in table.Rows)
//for (int i = 0; i < table.Rows.Count; i++)
string month = "";//记录上条记录的月份
string week = "";//记录上条记录的周
string MW = ""; //记录上条记录的月份+周
int QestingSequence = 1;//记录当前记录是本周的第几天推送消息
int inDay = 4;//存储天数
for (int i = 0; i < curCount; i++)
{
DataRow row = table.Rows[preBatchIndex + i];
TableTypeParameter[] curParamArr = new TableTypeParameter[5];
//当本记录的月分为空时表明单元格合并了取不到数据。那本记录的月分=上条记录的月份。
if (!string.IsNullOrEmpty(row[0].ToString()))
{
month = row[0].ToString();
}
//当本记录的周为空时表明单元格合并了取不到数据。那本记录的周=上条记录的周。
if (!string.IsNullOrEmpty(row[1].ToString()))
{
week = row[1].ToString();
if (!string.IsNullOrEmpty(week))
{
if (week.Equals("第一周") || week.Equals("第1周"))
{
week = "1周";
}
else if (week.Equals("第二周") || week.Equals("第2周"))
{
week = "2周";
}
else if (week.Equals("第三周") || week.Equals("第3周"))
{
week = "3周";
}
else if (week.Equals("第四周") || week.Equals("第4周"))
{
week = "4周";
}
}
}
if (!string.IsNullOrEmpty(MW))
{
if (MW.Equals(month + week))
{
QestingSequence += inDay;
}
else
{
QestingSequence = 1;
MW = month + week;
}
}
else
MW = month + week;
//string sql = "insert into hbh_t_importMessageData (GUID,aboutAgeBegin,aboutAgeEnd,message_Title,message_content,messDate)values(?,?,?,?,?,now());";
int endDay = 0;//时间段结束
if (QestingSequence == 1)
{
endDay = QestingSequence + 3;
}
else
{
endDay = QestingSequence + 2;
}
curParamArr[0] = new TableTypeParameter("GUID", strGuid);
curParamArr[1] = new TableTypeParameter("aboutAgeBegin", strTableName + month + week + "第" + QestingSequence + "天");
curParamArr[2] = new TableTypeParameter("aboutAgeEnd", strTableName + month + week + "第" + endDay + "天");
string row_title = "";
if (string.IsNullOrEmpty(row[2].ToString().Trim()))
row_title = "";
else
row_title = row[2].ToString().Trim();
string row_content = "";
if (string.IsNullOrEmpty(row[3].ToString().Trim()))
row_content = "";
else
row_content = row[3].ToString().Trim();
curParamArr[3] = new TableTypeParameter("message_Title", row_title);
curParamArr[4] = new TableTypeParameter("message_content", row_content);
sqlParamArray[i] = curParamArr;
}
DatabaseAdapter sqlHelper = new DatabaseAdapter(this);
sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql, sqlParamArray);
}
}
}
// ,concat(Content,'(',AgeGroupName,')')
// ,(length(Sequence) - length(replace(Sequence,'.',''))) ,length(Sequence),length(replace(Sequence,'.',''))
string strProcName = "hbh_proc_ImportMessageData";
// 为了效率，改为存储过程
//string updateText = string.Format(updateSql, strGuid);
string updateText = string.Format("call {0}('{1}');", strProcName, strGuid);
DatabaseAdapter sqlHelper2 = new DatabaseAdapter(this);
sqlHelper2.DbHelper.ExecuteNonQuery(CommandType.Text, updateText);
}
}
catch (Exception ex)
{
Response.Write(string.Format("<script>alert('{0}')</script> ", ex.Message));
}
}
