public IActionResult Input()
{
string sqlText = "select * from customers a;";
Dictionary <string, string> param = new Dictionary <string, string>();
DbFetchOutData outdata = _da.FecthQuery(sqlText, param);
ViewData["Customers"] = outdata;
sqlText = "select * from products a;";
param = new Dictionary <string, string>();
outdata = _da.FecthQuery(sqlText, param);
ViewData["Products"] = outdata;
ViewBag.Error = "NO";
return(View());
}
public DbFetchOutData FecthQuery(string sqlText, Dictionary <string, string> param, ref string exception)
{
//_logger.LogDebug("FecthQuery");
//ResultObj Rsobj = new ResultObj();
//Rsobj.Name = "ERR_MSG";
string exp = "000000";
DbConnection connection = null;
DbCommand cmd = null;
DbFetchOutData OutData = null;
try
{
// get connection from _context -> using Dapper
//dotnet add package Dapper --version 1.60.6
connection = _context.Database.GetDbConnection();
connection.Open();
cmd = BuildCommand(connection, sqlText, param);
var reader = cmd.ExecuteReader();
OutData = BuildOutputReader(reader);
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception: " + e.Message);
//_logger.LogError(e.Message);
exp = e.Message;
}
finally{
try
{
if (cmd != null)
{
cmd.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
catch (Exception e)
{
Console.WriteLine("Exception: " + e.Message);
exp = e.Message;
}
}
exception = exp;
return(OutData);
}
public IActionResult Customer(string CusName, string TaxID, string CusAddr, string CusPhone, string CusEmail)
{
DbFetchOutData outdata = new DbFetchOutData();
Dictionary <string, string> param;
string sqlText = "INSERT INTO customers(CUST_NAME, CUST_TAX, CUST_ADDRESS, CUST_PHONE_NUMBER, CUST_EMAIL) values(@CUST_NAME, @CUST_TAX, @CUST_ADDRESS, @CUST_PHONE_NUMBER, @CUST_EMAIL)";
param = new Dictionary <string, string>();
param.Add("CUST_NAME", CusName);
param.Add("CUST_TAX", TaxID);
param.Add("CUST_ADDRESS", CusAddr);
param.Add("CUST_PHONE_NUMBER", CusPhone);
param.Add("CUST_EMAIL", CusEmail);
_da.ExecuteQuery("INS", sqlText, param, ref outdata);
ViewBag.Error = "SUCCESS";
return(View());
}
public IActionResult Product(string ProdName, string ProdDesc, string ProdIUnit, string ProdPrice)
{
var loggedInUser = HttpContext.User;
var user_name = loggedInUser.Identity.Name;
var user_id = GetUserID(user_name);
DbFetchOutData outdata = new DbFetchOutData();
Dictionary <string, string> param;
string sqlText = "INSERT INTO products(PROD_NAME, PROD_DESC, PROD_UNIT, PROD_UNIT_PRICE, PROD_AMOUNT, PROD_LAST_USER_CHANGED, PROD_LAST_TIME_CHANGED) values(@PROD_NAME, @PROD_DESC, @PROD_UNIT, @PROD_UNIT_PRICE, @PROD_AMOUNT, @PROD_LAST_USER_CHANGED, @PROD_LAST_TIME_CHANGED)";
param = new Dictionary <string, string>();
param.Add("PROD_NAME", ProdName);
param.Add("PROD_DESC", ProdDesc);
param.Add("PROD_UNIT", ProdIUnit);
param.Add("PROD_UNIT_PRICE", ProdPrice);
param.Add("PROD_AMOUNT", "0");
param.Add("PROD_LAST_USER_CHANGED", user_id);
param.Add("PROD_LAST_TIME_CHANGED", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
_da.ExecuteQuery("INS", sqlText, param, ref outdata);
ViewBag.Error = "SUCCESS";
return(View());
}
public DbFetchOutData ExecuteReaderProcedure(string sqlText, List <ParamObj> param, ref string exception)
{
string exp = "000000";
DbFetchOutData OutData = null;
DbConnection connection = null;
DbCommand cmd = null;
try
{
connection = _context.Database.GetDbConnection();
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = sqlText;
foreach (var data in param)
{
var pr = cmd.CreateParameter();
pr.ParameterName = "@" + data.Name;
if (data.Direction == ParameterDirection.Input)
{
if (data.Type == DbType.Date)
{
pr.Value = Convert.ToDateTime(data.Value);
}
else
{
pr.Value = data.Value;
}
}
pr.DbType = data.Type;
pr.Direction = data.Direction;
cmd.Parameters.Add(pr);
}
Console.WriteLine("Call Pro Query procedure");
cmd.CommandType = CommandType.StoredProcedure;
//cmd.ExecuteNonQuery();
var reader = cmd.ExecuteReader();
Console.WriteLine("Execute Pro Query DONE");
OutData = BuildOutputReader(reader);
reader.Close();
/*foreach(var data in param){
*
* if(data.Direction == ParameterDirection.Output)
* {
* var val = Convert.ToString(cmd.Parameters["@"+data.Name].Value);
* ResultObj rs = new ResultObj(data.Name, val);
* result.Add(rs);
* }
* }*/
}
catch (Exception e)
{
Console.WriteLine("Exception: " + e.Message);
//_logger.LogError(e.Message);
exp = e.Message;
}
finally{
try
{
if (cmd != null)
{
cmd.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
catch (Exception e)
{
Console.WriteLine("Exception: " + e.Message);
exp = e.Message;
}
}
exception = exp;
return(OutData);
}
public void ExecuteQuery(string queryType, string sqlText, Dictionary <string, string> param, ref DbFetchOutData outdata, ref string exception)
{
string exp = "000000";
DbConnection connection = null;
DbCommand cmd = null;
try
{
connection = _context.Database.GetDbConnection();
connection.Open();
cmd = BuildCommand(connection, sqlText, param);
switch (queryType)
{
case "DEL":
Console.WriteLine("Delete data");
cmd.ExecuteNonQuery();
break;
case "INS":
Console.WriteLine("Insert data");
Console.WriteLine("sqlText :" + sqlText);
cmd.ExecuteNonQuery();
break;
case "UPD":
Console.WriteLine("Update data");
cmd.ExecuteNonQuery();
break;
case "PRO":
Console.WriteLine("Call procedure");
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
/*var reader = cmd.ExecuteReader();
* while (reader.Read())
* {
* Console.WriteLine(reader.GetString(1));
* }
* reader.Close();*/
//outdata = BuildOutputReader(reader);
break;
default:
throw new Exception("the method " + queryType + " is not supported");
}
}
catch (Exception e)
{
Console.WriteLine("Exception: " + e.Message);
//_logger.LogError(e.Message);
exp = e.Message;
}
finally{
try
{
if (cmd != null)
{
cmd.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
catch (Exception e)
{
Console.WriteLine("Exception: " + e.Message);
exp = e.Message;
}
}
exception = exp;
}
public IActionResult Index()
{
string exp = "";
string sqlText = "select * from products a LIMIT 20;";
Dictionary <string, string> param = new Dictionary <string, string>();
DbFetchOutData outdata = _da.FecthQuery(sqlText, param, ref exp);
if (exp != "000000")
{
ViewBag.Error = exp;
return(View("ErrorPage"));
}
ViewData["Products"] = outdata;
sqlText = "select * from (" +
"SELECT TAX_ID, " +
"(select prod_name from qt.products where PROD_ID=a.PROD_ID) PROD_NAME," +
"(select cust_name from qt.customers where cust_ID=a.CUST_ID) CUST_NAME," +
"UNIT_AMOUNT," +
"if(TRANSACTION='sell','Xuất kho','Nhập kho') TRANSACTION," +
"TIMESTAMP " +
"FROM qt.transactions a order by TIMESTAMP desc) as a LIMIT 10;";
param = new Dictionary <string, string>();
outdata = _da.FecthQuery(sqlText, param, ref exp);
if (exp != "000000")
{
ViewBag.Error = exp;
return(View("ErrorPage"));
}
ViewData["Transactions"] = outdata;
sqlText = "select * from customers a;";
param = new Dictionary <string, string>();
outdata = _da.FecthQuery(sqlText, param, ref exp);
if (exp != "000000")
{
ViewBag.Error = exp;
return(View("ErrorPage"));
}
ViewData["Customers"] = outdata;
DateTime now = DateTime.Now;
string sDate = now.Month - 1 + "/01/" + now.Year;
ViewBag.StartDate = Convert.ToDateTime(sDate).ToString("dd/MM/yyyy");
List <ParamObj> paramPro = new List <ParamObj>();
paramPro.Add(new ParamObj("start_date", sDate, DbType.Date, ParameterDirection.Input));
paramPro.Add(new ParamObj("end_date", DateTime.Now.ToString(), DbType.Date, ParameterDirection.Input));
DbFetchOutData outdataPro = _da.ExecuteReaderProcedure("ProcDashBoard", paramPro, ref exp);
if (exp != "000000")
{
ViewBag.Error = exp;
return(View("ErrorPage"));
}
ViewData["Report"] = outdataPro;
//Console.WriteLine("AMOUNT_TRAN: " + outdataPro.Data[0]["AMOUNT_TRAN"]);
return(View());
}