public String insertJson(String json)
{
String res = "";
SQL_function sql_function = new SQL_function();
String strQuery = @"INSERT INTO [dbo].[DataTotNghiep]
([HoTen]
,[NgaySinh]
,[NoiSinh]
,[GioiTinh]
,[DanToc]
,[Truong]
,[KhoaThi]
,[HoiDong]
,[SoHieu]
,[Rot]
,[DauSauPhucKhao])
SELECT *
FROM OPENJSON(@json)
WITH (HoTen nvarchar(50), NgaySinh date, NoiSinh nvarchar(50),
GioiTinh bit, DanToc int,
Truong nvarchar(50), KhoaThi date,
HoiDong nvarchar(50), SoHieu nvarchar(50),
Rot bit, DauSauPhucKhao bit
)" ;
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@json", SqlDbType.NVarChar).Value = json;
res = sql_function.InsertUpdateData(cmd, "sqlconnString");
return(res);
}
public IHttpActionResult AdminLogin(LogInModel Model)
{
LogInResult resModel = new LogInResult();
String newID = Shuffle(Model.ID);
resModel.ID = newID;
resModel.Result = false;
SQL_function sql_function = new SQL_function();
String strQuery_auth = @"SELECT COUNT(*) FROM [DuLieuTotNghiep].[dbo].[Account]
WHERE [UserName] = @UserName and [Password] =@Password ";
SqlCommand cmd_auth = new SqlCommand(strQuery_auth);
cmd_auth.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = Model.UserName;
cmd_auth.Parameters.Add("@Password", SqlDbType.NVarChar).Value = Model.Password;
String[,] array_auth = sql_function.xml_deserialize(sql_function.GetData(cmd_auth, "sqlconnString"));
if (array_auth[0, 0] == "1")
{
String strQuery = @"UPDATE [dbo].[Session] SET ID =@newID ,[UserName] = @UserName WHERE ID = @ID";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@newID", SqlDbType.NVarChar).Value = newID;
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = "admin";
cmd.Parameters.Add("@ID", SqlDbType.NVarChar).Value = Model.ID;
String insertRes = sql_function.InsertUpdateData(cmd, "sqlconnString");
if (insertRes == "1")
{
resModel.Result = true;
}
}
return(Json(resModel));
}
public async Task <IHttpActionResult> ReCaptchaVerify(VerifyModel Model)
{
String secret_key = "6LdSD84UAAAAANYhNhpJUef1_ydrERXTzmVXdH75";
var values = new Dictionary <string, string>
{
{ "secret", secret_key },
{ "response", Model.ResponseKey }
};
var content = new FormUrlEncodedContent(values);
HttpResponseMessage response = await client.PostAsync("https://www.google.com/recaptcha/api/siteverify", content);
if (response.IsSuccessStatusCode)
{
var domain = Request.Headers.Referrer? .GetLeftPart(UriPartial.Authority) ?? Request.Headers.UserAgent.ToString();
SQL_function sql_function = new SQL_function();
String strQuery = @"INSERT INTO [dbo].[Session] ([ID] ,[UserName] ,[TimeStampt] ,[IP], [isActive]) VALUES (@ID ,@UserName ,Getdate() ,@IP, 1)";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@ID", SqlDbType.NVarChar).Value = Model.ResponseKey.Substring(0, 50);
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = "Guest";
cmd.Parameters.Add("@IP", SqlDbType.NVarChar).Value = domain;
sql_function.InsertUpdateData(cmd, "sqlconnString");
}
var responseString = await response.Content.ReadAsStringAsync();
return(Ok(responseString));
}
public void AdminLogout(LogInModel Model)
{
SQL_function sql_function = new SQL_function();
String strQuery = @"UPDATE [dbo].[Session] SET isActive = 0 WHERE ID = @ID";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@ID", SqlDbType.NVarChar).Value = Model.ID;
String insertRes = sql_function.InsertUpdateData(cmd, "sqlconnString");
}
public IHttpActionResult GetTruong()
{
SQL_function sql_function = new SQL_function();
String strQuery = @"SELECT [SchoolID] ,[TenTruong]
FROM [DuLieuTotNghiep].[dbo].[T_DM_Truong]";
SqlCommand cmd = new SqlCommand(strQuery);
String json = sql_function.GetData_Json(cmd, "sqlconnString");
return(Json(json));
}
public IHttpActionResult GetDanToc()
{
SQL_function sql_function = new SQL_function();
String strQuery = @"SELECT [DanTocID],[TenDanToc],[Moet_DanTocID]
FROM [DuLieuTotNghiep].[dbo].[T_DM_DanToc]";
SqlCommand cmd = new SqlCommand(strQuery);
String json = sql_function.GetData_Json(cmd, "sqlconnString");
return(Json(json));
}
public String[] SoHieu()
{
SQL_function sql_function = new SQL_function();
String strQuery = @"SELECT DISTINCT [SoHieu] FROM [DuLieuTotNghiep].[dbo].[DataTotNghiep]";
SqlCommand cmd = new SqlCommand(strQuery);
String[,] array = sql_function.xml_deserialize(sql_function.GetData(cmd, "sqlconnString"));
string[] res = new string[array.GetLength(0)];
for (int i = 0; i < array.GetLength(0); i++)
{
res[i] = array[i, 0];
}
return(res);
}
public String[] DanToc()
{
SQL_function sql_function = new SQL_function();
String strQuery = @"SELECT[DanTocID],[TenDanToc],[Moet_DanTocID]
FROM[DuLieuTotNghiep].[dbo].[T_DM_DanToc]";
SqlCommand cmd = new SqlCommand(strQuery);
String[,] array = sql_function.xml_deserialize(sql_function.GetData(cmd, "sqlconnString"));
string[] res = new string[array.GetLength(0)];
for (int i = 0; i < array.GetLength(0); i++)
{
res[i] = array[i, 0];
}
return(res);
}
public IHttpActionResult PostTraCuu(DataTotNghiepModel Model)
{
SQL_function sql_function = new SQL_function();
String strQuery = @"SELECT dbo.DataTotNghiep.ID, dbo.DataTotNghiep.HoTen, dbo.DataTotNghiep.NgaySinh, dbo.DataTotNghiep.NoiSinh, dbo.DataTotNghiep.GioiTinh, dbo.T_DM_DanToc.TenDanToc, dbo.DataTotNghiep.Truong,
dbo.DataTotNghiep.KhoaThi, dbo.DataTotNghiep.HoiDong, dbo.DataTotNghiep.SoHieu, dbo.DataTotNghiep.Rot, dbo.DataTotNghiep.DauSauPhucKhao
FROM dbo.DataTotNghiep INNER JOIN
dbo.T_DM_DanToc ON dbo.DataTotNghiep.DanToc = dbo.T_DM_DanToc.DanTocID
WHERE (HoTen like @HoTen AND NgaySinh = @NgaySinh) OR (SoHieu like @SoHieu)" ;
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar).Value = Model.HoTen;
cmd.Parameters.Add("@NgaySinh", SqlDbType.Date).Value = Model.NgaySinh;
cmd.Parameters.Add("@SoHieu", SqlDbType.NVarChar).Value = Model.SoHieu;
String json = sql_function.GetData_Json(cmd, "sqlconnString");
return(Json(json));
}
