public ViewModuleClass()
{
DBControl = DataBaseControl.GetDataBaseControlInstance();
Store = DBControl.Ctx.Books.Include(a => a.Authors).ToList();
;
}
public bool checkLocalAccount(string account, out Error error)
{
error = null;
bool bExisted = false;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select CM002 from consumer_member where CM002=@CM002";
paraList.Add(new SqlParameter("@CM002", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
IDataReader dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
bExisted = true;
}
dataReader.Close();
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(bExisted);
}
public void clearLoginTime(string account, out THC_Library.Error error)
{
error = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "update activity_user set AU004=NULL where AU001=@AU001";
paraList.Add(new SqlParameter("@AU001", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
}
private void BtGuardar_Click(object sender, EventArgs e)
{
try
{
MemoryStream ms = new System.IO.MemoryStream();
PBImage.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
DataBaseControl DbControl = new DataBaseControl(DataBaseControl.cPath, "ProjectDataBase.mdf");
string[] parameters = { "@id", "@nombre", "@image" };
string[] values = { TxtID.Text, TxtNombre.Text };
if (DbControl.Insertar("Insert into Maestros values (@id,@nombre,@image)", parameters, values, ms))
{
MessageBox.Show(string.Format("{0} agregado", TxtNombre.Text));
TxtID.Clear();
TxtNombre.Clear();
PBImage.Image = Image.FromFile("intec.png");
Verificar();
}
else
{
MessageBox.Show(string.Format("{0} no agregado", TxtNombre.Text));
}
}
catch (Exception)
{
MessageBox.Show("Por favor, revisar los valores agregados");
}
}
private void AgregarRegistroForm_Load(object sender, EventArgs e)
{
if (Verificar())
{
BtVincular.Enabled = true;
}
else
{
BtVincular.Enabled = false;
}
DataBaseControl DbControl = new DataBaseControl(DataBaseControl.cPath, "ProjectDataBase.mdf");
string[] para = new string[] { "@algo" };
string[] val = new string[] { " " };
List <string[]> alumnos = DbControl.Buscar("Select [ID] from Alumnos where [ID]!=@algo", para, val, 1);
foreach (string[] list in alumnos)
{
foreach (string objeto in list)
{
CBAlumno.Items.Add(objeto);
}
}
para = new string[] { "@algo" };
val = new string[] { " " };
List <string[]> Materias = DbControl.Buscar("Select [Clave] from Materias where [Clave]!=@algo", para, val, 1);
foreach (string[] list in Materias)
{
foreach (string objeto in list)
{
CBMateria.Items.Add(objeto);
}
}
}
private void BtEliminar_Click(object sender, EventArgs e)
{
try
{
DataBaseControl DBControl = new DataBaseControl(DataBaseControl.cPath, "ProjectDataBase.mdf");
string[] para = { "@user" };
string[] ele = { TxtUserName.Text };
if (DBControl.Buscar("Select * from [Registro] where [materia]= @user", para, ele))
{
MessageBox.Show("Esta materia actualmente figura en una seccion, imposible eliminar");
TxtUserName.Clear();
return;
}
else
{
if (DBControl.Eliminar("Delete from [Materias] where [Clave] = @user", para, ele))
{
MessageBox.Show("Materia eliminado con exito");
LVUsuarios.Clear();
}
else
{
MessageBox.Show("Error al eliminar Materia");
}
}
}
catch (Exception) { MessageBox.Show("Por favor, verificar los datos"); }
}
public void updateEvent(string event_no, string page, out THC_Library.Error error)
{
error = null;
SqlParameter sqlParam;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "update activity_event set AE013=@AE013 where AE002=@AE002";
DataBaseControl dbCtl = new DataBaseControl();
try
{
sqlParam = new SqlParameter("@AE013", SqlDbType.VarChar);
sqlParam.Value = page;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE002", SqlDbType.NVarChar);
sqlParam.Value = event_no;
paraList.Add(sqlParam);
dbCtl.Open();
dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
}
public ActionResult Edit(string FaceName, int id)
{
DataBaseControl a = new DataBaseControl();
a.EditFaceName(FaceName, id);
return(RedirectToAction("Index"));
}
public DataTable getScanCount_InDay_7(string event_no, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
DateTime datNow = DateTime.Now;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select count(EUR003) as VALUE, CAST(EUR004 AS DATE) as DATE from event_user_records " +
"where EUR002=@EUR002 and DATEDIFF(day,EUR004,GETDATE()) < 7 " +
"group by CAST(EUR004 AS DATE)";
paraList.Add(new SqlParameter("@EUR002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public DataTable getTimeTemptrue(string event_no, string days, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select CAST(EUR004 AS DATE) as DATE,AVG(EUR009) as TEMP,count(*) as COUNT from event_user_records " +
"where EUR002=@EUR002 and DATEDIFF(day,EUR004,GETDATE()) < " + days + " " +
"group by CAST(EUR004 AS DATE)";
paraList.Add(new SqlParameter("@EUR002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public DataTable getScanGender(string event_no, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select EUR007 as GENDER,count(EUR003) as VALUE from event_user_records " +
"where EUR002=@EUR002 group by EUR007";
paraList.Add(new SqlParameter("@EUR002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public DataTable getWeather(out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select * from weather";
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public DataTable getMyEvent(string event_no, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select * from activity_event where AE002=@AE002";
paraList.Add(new SqlParameter("@AE002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public DataTable getEventRewards(string event_no, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select AEP001,AEP002,AEP003,AEP004,AEP005,AEP006,AEP007,AEP009,AEP011,AEP012,AEP013 " + //(SUBSTRING(AEP013,0,20) + '.....') as AEP013 " +
"from activity_rewards where AEP002=@AEP002";
paraList.Add(new SqlParameter("@AEP002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public void GetLastRecordListRutorTest()
{
DataBaseControl db = new DataBaseControl();
List <ItemList> lastPreposts = db.GetLastRecordListRutor();
Assert.AreEqual(100, lastPreposts.Count());
}
public ActionResult Edit(int id)
{
DataBaseControl a = new DataBaseControl();
var face = a.GetFaceById(id);
return(View(face));
}
private void BtnEnter_Click(object sender, EventArgs e)
{
if (txtPointCardID.Text == "")
{
MessageBox.Show("Point Card ID can not be empty.");
return;
}
if (!DataBaseControl.MatchingOneColumn_OfTable("PointCard", "PointCardID", txtPointCardID.Text))
{
MessageBox.Show("Point Card ID does not exist.");
return;
}
PointCardDataBaseControl.pointCardID = txtPointCardID.Text;
btnEnter.Enabled = false;
if (StaffDataBaseControl.staffID != "")
{
((StaffMenu)MdiParent).ChangeMDIForm_callByOtherForm(StaffMenu.Page.PointCard_inSide);
}
else if (CSMDataBaseControl.CSMID != "")
{
((CSMMenu)MdiParent).ChangeMDIForm_callByOtherForm(CSMMenu.Page.PointCard_inSide);
}
else
{
MessageBox.Show("Unexpected flow.");
}
}
private void BtnPlayerIDEnter_Click(object sender, EventArgs e)
{
if (txtPlayerID.Text == "")
{
MessageBox.Show("Player ID can not be empty.");
return;
}
if (!PlayerDataBaseControl.MatchingOneColumn("PlayerID", txtPlayerID.Text))
{
MessageBox.Show("Player ID does not exist.");
return;
}
if (txtPointCardID.Text == "")
{
MessageBox.Show("Point Card ID can not be empty.");
return;
}
if (!DataBaseControl.MatchingOneColumn_OfTable("PointCard", "PointCardID", txtPointCardID.Text))
{
MessageBox.Show("Point Card ID does not exist.");
return;
}
PlayerDataBaseControl.playerID = txtPlayerID.Text;
TopUpToPlayer();
}
private void BtnGo_Click(object sender, EventArgs e)
{
if (txtGameName.Text == "")
{
MessageBox.Show("Game Name should not be empty.");
return;
}
if (!DataBaseControl.MatchingOneColumn_OfTable("Game", "GameName", txtGameName.Text))
{
MessageBox.Show("This Game is not exist.");
return;
}
string gameDetail;
if (PlayerDataBaseControl.isEnglish)
{
gameDetail = DataBaseControl.GetData_OfGameTable("GameName", txtGameName.Text, "GameDescription");
}
else
{
gameDetail = DataBaseControl.GetData_OfGameTable("GameName", txtGameName.Text, "GameChineseDescription");
}
MessageBox.Show(gameDetail, "Game Detail Of " + txtGameName.Text);
}
public void GetLastFoundedTest()
{
DataBaseControl db = new DataBaseControl();
List <FoundPost> list = db.GetLastFounded(TTrakers.Rutor);
Assert.AreEqual(100, list.Count());
}
public ActionResult Search(string name)
{
DataBaseControl a = new DataBaseControl();
var photo = a.GetbyName(name);
return(View(photo));
}
public int deleteUser(string account, out THC_Library.Error error)
{
error = null;
int iExcuteCount = -1;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "delete from activity_user where AU001=@AU001";
paraList.Add(new SqlParameter("@AU001", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
iExcuteCount = dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(iExcuteCount);
}
//
// GET: /Face/
public ActionResult Index()
{
DataBaseControl a = new DataBaseControl();
var face = a.GetFaces();
return(View(face));
}
private void TxtFiltrar_TextChanged(object sender, EventArgs e)
{
DataBaseControl DBControl = new DataBaseControl(DataBaseControl.cPath, "ProjectDataBase.mdf");
string[] parameters = { "@word" };
string[] value = { TxtFiltrar.Text };
DataTable table = new DataTable();
string cPath = System.IO.Directory.GetCurrentDirectory() + @"\";
//public static string cPath = Path.GetFullPath(@"..\..\");
string _location = "", _DataBaseName = "ProjectDataBase.mdf";
_location = cPath + @"ApplicationData\" + _DataBaseName;
String path = @"Data source = (localdb)\v11.0 ; AttachDbFilename=" + _location + "; MultipleActiveResultSets=True; Integrated Security=SSPI";
con.ConnectionString = path;
con.Open();
using (dataAdapter)
{
dataAdapter = new SqlDataAdapter("Select * from Alumnos where [Nombre] like '%'+@word+'%' or [ID] like '%'+@word+'%' ", con);
for (int i = 0; i < parameters.Length; i++)
{
dataAdapter.SelectCommand.Parameters.AddWithValue(parameters[i], value[i]);
}
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
}
Source.DataSource = table;
DGVAlumnos.DataSource = Source;
DGVAlumnos.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
con.Close();
}
public void AsyneEvent(string event_no, out THC_Library.Error error)
{
error = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select * from activity_event where AE002=@AE002";
paraList.Add(new SqlParameter("@AE002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
DataTable eventTable = dbCtl.GetDataTable(strSQL, paraList);
string eventJson = Newtonsoft.Json.JsonConvert.SerializeObject(eventTable);
string jsonResult = THC_Library.APPCURL.AnscyActivity(eventJson);
dynamic resultObj = Newtonsoft.Json.JsonConvert.DeserializeObject(jsonResult);
if (resultObj.Number != 0)
{
throw new Exception(resultObj.ErrorMessage.ToString());
}
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
}
public ActionResult delete(int id)
{
DataBaseControl a = new DataBaseControl();
a.DeletePhoto(id);
return(RedirectToAction("Index"));
}
public ActionResult Photo(int id)
{
DataBaseControl a = new DataBaseControl();
var photo = a.GetPhotoId(id);
return(View(photo));
}
//
// GET: /Photo/
public ActionResult Index()
{
DataBaseControl a = new DataBaseControl();
var photo = a.GetPhotos();
return(View(photo));
}
public DataTable getUserInfo(string account, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select AU001,AU002,AU003,AU004 from activity_user where AU001=@AU001";
paraList.Add(new SqlParameter("@AU001", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public void changePassword(string account, string old, string new1, string new2, out THC_Library.Error error)
{
error = null;
byte[] pwdBytes = System.Text.Encoding.Default.GetBytes(old);
System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
pwdBytes = md5.ComputeHash(pwdBytes);
string strOldPwd = Convert.ToBase64String(pwdBytes);
pwdBytes = System.Text.Encoding.Default.GetBytes(new1);
md5 = System.Security.Cryptography.MD5.Create();
pwdBytes = md5.ComputeHash(pwdBytes);
string strNewPwd = Convert.ToBase64String(pwdBytes);
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select AU003 from activity_user where AU001=@AU001";
paraList.Add(new SqlParameter("@AU001", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
if (new1 != new2)
{
throw new Exception("新密碼不相符");
}
dbCtl.Open();
IDataReader dataReader = dbCtl.GetReader(strSQL, paraList);
dataReader.Read();
string strOld = dataReader["AU003"].ToString();
dataReader.Close();
if (strOldPwd != strOld)
{
throw new Exception("舊密碼輸入錯誤");
}
strSQL = "update activity_user set AU003=@AU003 where AU001=@AU001";
paraList.Clear();
paraList.Add(new SqlParameter("@AU003", strNewPwd));
paraList.Add(new SqlParameter("@AU001", account));
dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
}
