public int ForgotPassword(string email)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string check_email = string.Format("SELECT * FROM SYS_ACCOUNT WHERE email='{0}'", email);
var dt = mssql.GetDataTableFromQueryStr(check_email);
if (dt.Rows.Count > 0)
{
string query = string.Format("INSERT INTO ACC_FORGOT_PASSWORD ([sent_to_email],[create_date]) VALUES ('{0}','{1}')", email, DateTime.Now.ToString("yyyyMMdd HH:mm:ss"));
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
else
{
resp = -2;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Forgot Password Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public int Update_Registered(RegisterModel data)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(
@"UPDATE SYS_REGISTER SET
[name_eng]='{1}',[name_thai]='{2}',[address]='{3}',[email]='{4}',
[website]='{5}',[tel]='{6}',[fax]='{7}',[contact]='{8}',[country]='{9}'
WHERE system_id='{0}'",
data.system_id, data.name_eng, data.name_thai, data.address, data.email,
data.website, data.tel, data.fax, data.contact, data.country);
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Update Registered Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public int Update_Function(FunctionModel data)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(@"UPDATE SYS_FUNCTION SET
name_thai='{1}', name_eng='{2}', route_path='{3}', icon_name='{4}', func_type='{5}', func_ref_sub='{6}'
WHERE code='{0}'",
data.code, data.name_thai, data.name_eng, data.route_path, data.icon_name, data.func_type, data.func_ref_sub);
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Update Function Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public static Pregunta obtenerAleatoria(int examen)
{
string[] opciones;
int cOpciones = 2;
string con = "Data Source=alexisserver.ceq0e9y8bekm.us-west-2.rds.amazonaws.com;Initial Catalog=preparate_dev;Persist Security Info=True;User ID=Alexis;Password=Proyecto2017";
Classes.Parameter[] p = new Classes.Parameter[]
{
new Classes.Parameter("@examen", examen)
};
DataTable pre = MSSql.ExecuteStoredProcedure(con, "sp_randomQuestion", p);
DataRow dr = pre.Rows[0];
while (dr[cOpciones].ToString() != "" && cOpciones <= 11)
{
cOpciones++;
}
cOpciones -= 1;
opciones = new String[cOpciones - 1];
for (int i = 2; i <= cOpciones; i++)
{
opciones[i - 2] = dr["opcion" + (i - 1)].ToString();
}
return(new Pregunta(dr[1].ToString(), opciones, Convert.ToInt32(dr[12].ToString()), Convert.ToInt32(dr[0].ToString()), dr[13].ToString(), Convert.ToInt32(dr[14].ToString()), dr[15].ToString()));
}
public List<Product> Get(int client_id)
{
IDataBase db = new MSSql();
db.SetStoredProcedure("MVCWeb.Basket_Get");
db.AddParameter(new SqlParameter("@client_id", client_id));
return db.Query<Product>();
}
public int Update_TokenKey(TokenKeyModel data)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(@"UPDATE SYS_JWT_TOKEN SET
secret='{1}', issuer='{2}', audience='{3}', expires='{4}', create_date='{5}'
WHERE code='{0}'",
data.code, data.secret, data.issuer, data.audience, data.expire, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Update TokenKey Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public int Update_User(UserModel data)
{
int resp = -1;
try
{
string _password = System.Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(data.password));
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(@"UPDATE SYS_ACCOUNT SET
email='{1}', password='******', prefixes='{3}', first_name='{4}', last_name='{5}', language='{6}', section_code='{7}', activate_datetime='{8}'
WHERE account_id='{0}'",
data.account_id, data.email, _password, data.prefixes, data.first_name, data.last_name, data.language, data.section_code, data.activate_date.Value.ToString("yyyy-MM-dd HH:mm:ss"));
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Update User Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public int Update_Section(SectionModel data)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(@"UPDATE SYS_SECTION SET name_thai='{1}', name_eng='{2}' WHERE code='{0}'",
data.code, data.name_thai, data.name_eng);
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Update Section Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public IList <SectionModel> GetListSection()
{
IList <SectionModel> resp = new List <SectionModel>();
try
{
string query = string.Format(@"SELECT * FROM SYS_SECTION");
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
var dt = mssql.GetDataTableFromQueryStr(query);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
SectionModel token_model = new SectionModel()
{
code = dr["code"].ToString(),
name_thai = dr["name_thai"].ToString(),
name_eng = dr["name_eng"].ToString(),
};
resp.Add(token_model);
}
return(resp);
}
}
}
catch (Exception ex)
{
_ILogs.LogError("GetList Section Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(null);
}
public int Insert_Section(SectionModel data)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(@"INSERT INTO SYS_SECTION (code, name_thai, name_eng) VALUES ('{0}','{1}','{2}')",
data.code, data.name_thai, data.name_eng);
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Insert Section Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public int Insert_Function(FunctionModel data)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(@"INSERT INTO SYS_FUNCTION
(code, name_thai, name_eng, route_path, icon_name, func_type, func_ref_sub)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
data.code, data.name_thai, data.name_eng, data.route_path, data.icon_name, data.func_type, data.func_ref_sub);
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Insert Function Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public int Insert_UserGroup(UserGroupModel data)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(@"INSERT INTO SYS_USER_GROUP
(code, name_thai, name_eng, group_level, theme_code, create_date)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')",
data.code, data.name_thai, data.name_eng, data.group_level, data.theme_code, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Insert UserGroup Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public RoleModel GetRole(string code)
{
RoleModel resp = new RoleModel();
try
{
string query = string.Format(@"SELECT * FROM SYS_ROLE WHERE code='{0}'", code);
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
var dt = mssql.GetDataTableFromQueryStr(query);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
resp.code = dr["code"].ToString();
resp.name_thai = dr["name_thai"].ToString();
resp.name_eng = dr["name_eng"].ToString();
}
return(resp);
}
}
}
catch (Exception ex)
{
_ILogs.LogError("GetList Role Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(null);
}
public SignInResponse SignOut(UserInfoResponse userInfo)
{
SignInResponse resp = new SignInResponse();
try
{
string query = string.Format(
@"SELECT * FROM SYS_ACCOUNT WHERE email='{0}'", userInfo.email);
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
var dt = mssql.GetDataTableFromQueryStr(query);
if (dt.Rows.Count > 0)
{
resp.status = StatusResponse.Success;
resp.message = "success";
_ILogRepository.WriteLogs("Sign Out", userInfo.email, "", "Success.");
}
}
return(resp);
}
catch (Exception ex)
{
_ILogs.LogError("SignOut Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(null);
}
public User(int id)
{
string con = "Data Source=alexisserver.ceq0e9y8bekm.us-west-2.rds.amazonaws.com;Initial Catalog=preparate_dev;Persist Security Info=True;User ID=Alexis;Password=Proyecto2017";
Classes.Parameter[] p = new Classes.Parameter[] {
new Classes.Parameter("@id", id)
};
DataTable dt = MSSql.ExecuteStoredProcedure(con, "sp_getUserInfo", p);
DataRow dr = dt.Rows[0];
this.Nombre = dr["nombre"].ToString();
this.Apellido_Parterno = dr["apellidos"].ToString();
this.Correo = dr["correo"].ToString();
this.Fecha_Nacimiento = dr["fecha_nacimiento"].ToString();
String prueba = dr["genero"].ToString();
if (dr["genero"].ToString() == "False")
{
this.Genero = 0;
}
else
{
this.Genero = 1;
}
//this.Genero = Convert.ToInt32(dr["genero"].ToString());
}
public int UpdateAccountStatus(string request_type, string account_id, string token, DateTime expire_date)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string update = string.Format(@"UPDATE SYS_ACCOUNT SET online_expire='{1}' WHERE account_id='{0}'",
account_id, expire_date.ToString("yyyy-MM-dd HH:mm:ss"));
int rest = mssql.ExcuteNonQueryStr(update);
if (rest >= 0)
{
_ILogRepository.WriteLogs(request_type, account_id, token, "Success.");
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Update Account Status Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public int Insert_Registered(RegisterModel data)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(
@"INSERT INTO SYS_REGISTER
([system_id],[name_eng],[name_thai],[address],[email],[website],[tel],[fax],[contact],[country],[register_date])
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')",
data.system_id, data.name_eng, data.name_thai, data.address,
data.email, data.website, data.tel, data.fax, data.contact,
data.country, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Insert Registered Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public int ActivateRegistered(RegisterModel data)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(
@"UPDATE SYS_REGISTER SET
[token]='{1}',[activate_date]='{2}',[expire_date]='{3}'
WHERE register_id='{0}'",
data.register_id, data.token, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), DateTime.Now.AddDays(365).ToString("yyyy-MM-dd HH:mm:ss"));
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("Update Registered Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
public int WriteLogs(string log_event, string account, string token, string message)
{
int resp = -1;
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(@"INSERT INTO SYS_EVENT_LOG
(log_account,log_token,log_message,log_event,log_system_id,log_event_datetime)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')",
account, token, message, log_event, "RBAC", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
int rest = mssql.ExcuteNonQueryStr(query);
if (rest >= 0)
{
resp = 0;
}
}
}
catch (Exception ex)
{
_ILogs.LogError("WriteLogs: ", ex.Message.ToString(), ex.StackTrace);
}
return(resp);
}
protected void btnEnviar_Click(object sender, EventArgs e)
{
TextBox[] Atxt = new TextBox[] { txtOpcCorrecta, txtOpc2, txtOpc3, txtOpc4, txtOpc5, txtOpc6, txtOpc7, txtOpc8, txtOpc9, txtOpc10 };
string[] Opciones = new string[10];
Random rm = new Random();
int i = 0;
int cont = 1;
while (Atxt[i].Text != "")
{
i++;
}
int aleatorio = rm.Next(i - 1);
Opciones[aleatorio] = Atxt[0].Text;
for (int j = 0; j < Opciones.Length; j++)
{
if (Opciones[j] == null)
{
Opciones[j] = Atxt[cont].Text;
cont++;
}
}
Classes.Parameter[] p = new Classes.Parameter[] {
new Classes.Parameter("@Pregunta", txtPregunta.Text),
new Classes.Parameter("@Opcion1", Opciones[0]),
new Classes.Parameter("@Opcion2", Opciones[1]),
new Classes.Parameter("@Opcion3", Opciones[2]),
new Classes.Parameter("@Opcion4", Opciones[3]),
new Classes.Parameter("@Opcion5", Opciones[4]),
new Classes.Parameter("@Opcion6", Opciones[5]),
new Classes.Parameter("@Opcion7", Opciones[6]),
new Classes.Parameter("@Opcion8", Opciones[7]),
new Classes.Parameter("@Opcion9", Opciones[8]),
new Classes.Parameter("@Opcion10", Opciones[9]),
new Classes.Parameter("@Respuesta_Correcta", aleatorio),
new Classes.Parameter("@Imagen", txtUrlImg.Text),
new Classes.Parameter("@ID_Tipo_Pregunta", DDLTipo.SelectedValue),
new Classes.Parameter("@Ayuda", txtAyuda.Text),
new Classes.Parameter("@ID_Tema", DDLTema.SelectedValue)
};
int ID = Convert.ToInt32(MSSql.FirstDataFromTable(con, "InsertPreguntas", p));
p = new Classes.Parameter[] {
new Classes.Parameter("@ID_Pregunta", ID),
new Classes.Parameter("@ID_Examen", DDLExamen.SelectedValue)
};
MSSql.FirstDataFromTable(con, "InsertPreguntaExamen", p);
Response.Write("<script LANGUAGE='JavaScript' >alert('Registro de Pregunta Exitoso')</script>");
}
public Product GetGoodsByID(int link_id)
{
IDataBase db = new MSSql();
db.SetStoredProcedure("MVCWeb.Goods_Get");
db.AddParameter(new SqlParameter("@link_id", link_id));
db.AddParameter(new SqlParameter("@userSale", 0));
return db.Query<Product>()[0];
}
public SignInResponse SignIn(SignInModel userInfo)
{
SignInResponse resp = new SignInResponse();
try
{
string passw = System.Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(userInfo.password));
string query = string.Format(
@"SELECT A.*, E.company_name_thai,E.company_name_eng,
C.name_thai AS group_name, C.theme_code
FROM [dbo].[SYS_ACCOUNT] A
INNER JOIN [dbo].[SYS_PERMISSION_GROUP] B
ON A.account_id = B.account_id
INNER JOIN [dbo].[SYS_USER_GROUP] C
ON B.group_code=C.code
LEFT OUTER JOIN [dbo].[SYS_COMPANY] E
ON A.company_id=E.company_id
WHERE A.email='{0}' AND A.password='******'",
userInfo.email, passw);
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
var dt = mssql.GetDataTableFromQueryStr(query);
if (dt.Rows.Count > 0)
{
resp.status = StatusResponse.Success;
resp.message = "success";
resp.userInfo = new UserInfoResponse();
foreach (DataRow dr in dt.Rows)
{
resp.userInfo.groupname = dr["group_name"].ToString();
resp.userInfo.accountId = dr["account_id"].ToString();
resp.userInfo.fullName = string.Format("{0}{1} {2}", dr["prefixes"].ToString(), dr["first_name"].ToString(), dr["last_name"].ToString());
resp.userInfo.email = dr["email"].ToString();
resp.userInfo.language = dr["language"].ToString();
resp.userInfo.theme = dr["theme_code"].ToString();
resp.userInfo.companyName = dr["company_name_eng"].ToString();
}
resp.userInfo.funcList = _IFuncMenuRepository.GetFuncMenu(userInfo.email, passw);
resp.userInfo.owner = _IOwnerRepository.GetOwner();
resp.userInfo.register = _IRegisterRepository.GetRegister(_EnvironmentModel.AppCodes.JwtCode);
}
else
{
resp.message = "The account is not found.";
}
}
return(resp);
}
catch (Exception ex)
{
_ILogs.LogError("SignIn Repository: ", ex.Message.ToString(), ex.StackTrace);
}
return(null);
}
public List<Product> GetGoodsByBrandSeria(int brand_id, int seria_id)
{
IDataBase db = new MSSql();
db.SetStoredProcedure("MVCWeb.GoodsFilter");
db.AddParameter(new SqlParameter("@brand_id", brand_id));
db.AddParameter(new SqlParameter("@seria_id", seria_id));
return db.Query<Product>();
}
public bool Remove(int client_id, int basket_id)
{
IDataBase db = new MSSql();
db.SetStoredProcedure("MVCWeb.Basket_Remove");
db.AddParameter(new SqlParameter("@client_id", client_id));
db.AddParameter(new SqlParameter("@basket_id", basket_id));
return db.GetReturnValue<bool>();
}
public IList <UsageStatusModel> GetUsageStatusLogs(string _date)
{
IList <UsageStatusModel> resp = new List <UsageStatusModel>();
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
string query = string.Format(@"SELECT 0 as account_status,
ISNULL(COUNT(account_id),0) as account_count
FROM [dbo].[SYS_ACCOUNT]
UNION
SELECT 1 as account_status,
ISNULL(COUNT(account_id),0) as account_count
FROM [dbo].[SYS_ACCOUNT]
WHERE online_expire IS NOT NULL
UNION
SELECT 2 as account_status,
ISNULL(COUNT(account_id),0) as account_count
FROM [dbo].[SYS_ACCOUNT]
WHERE online_expire IS NULL
ORDER BY account_status ASC");
var dt = mssql.GetDataTableFromQueryStr(query);
if (dt.Rows.Count > 0)
{
UsageStatusModel usage_status = new UsageStatusModel();
int _all = 0;
foreach (DataRow dr in dt.Rows)
{
if (Convert.ToInt32(dr["account_status"]) == 0)
{
_all = Convert.ToInt32(dr["account_count"]);
}
if (Convert.ToInt32(dr["account_status"]) == 1)
{
usage_status.online_count = Convert.ToInt32(dr["account_count"]);
usage_status.online_percent = string.Format(@"{0}%", (100 * usage_status.online_count / _all));
}
if (Convert.ToInt32(dr["account_status"]) == 2)
{
usage_status.offline_count = Convert.ToInt32(dr["account_count"]);
usage_status.offline_percent = string.Format(@"{0}%", (100 * usage_status.offline_count / _all));
}
}
resp.Add(usage_status);
return(resp);
}
}
}
catch (Exception ex)
{
_ILogs.LogError("WriteLogs: ", ex.Message.ToString(), ex.StackTrace);
}
return(null);
}
public MenuRepository()
{
_main_menu = new List<MainMenuItem>();
_main_menu.Add(new MainMenuItem { Item_name = "home", Item_url = "/", Item_id = 1 });
_main_menu.Add(new MainMenuItem { Item_name = "basket", Item_url = "/basket", Item_id = 2 });
IDataBase db = new MSSql();
db.SetStoredProcedure("MVCWeb.CatalogMenu_Get");
_catalog_menu = db.Query<CatalogMenuItem>();
}
public bool Add(int client_id, int goods_id, byte count)
{
IDataBase db = new MSSql();
db.SetStoredProcedure("MVCWeb.Basket_Add");
db.AddParameter(new SqlParameter("@client_id", client_id));
db.AddParameter(new SqlParameter("@goods_id", goods_id));
db.AddParameter(new SqlParameter("@cnt", count));
return db.GetReturnValue<bool>();
}
public static string alta_tema(string descripcion)
{
string con = "Data Source=alexisserver.ceq0e9y8bekm.us-west-2.rds.amazonaws.com;Initial Catalog=preparate_dev;Persist Security Info=True;User ID=Alexis;Password=Proyecto2017";
Classes.Parameter[] p = new Classes.Parameter[]
{
new Classes.Parameter("@Desc", descripcion)
};
return(MSSql.FirstDataFromTable(con, "InsertTema", p));
}
public static int baja(int id)
{
string con = "Data Source=alexisserver.ceq0e9y8bekm.us-west-2.rds.amazonaws.com;Initial Catalog=preparate_dev;Persist Security Info=True;User ID=Alexis;Password=Proyecto2017";
Classes.Parameter[] p = new Classes.Parameter[]
{
new Classes.Parameter("@ID", id)
};
return(Convert.ToInt32(MSSql.FirstDataFromTable(con, "DeletePreguntas", p)));
}
public static DataTable QueryByCondition(DTFormStatus data)
{
StringBuilder sb = new StringBuilder();
string sqlstr = "select refid,FormID,TagID,LineID,DeviceID,IsDown,Status,create_by,create_time from DTFormStatus where 1=1 ";
sb.Append(sqlstr);
if (!string.IsNullOrEmpty(data.FormID))
{
sb.AppendFormat(" and FormID like '%{0}%' ", data.FormID);
}
if (!string.IsNullOrEmpty(data.TagID))
{
sb.AppendFormat(" and TagID like '%{0}%' ", data.TagID);
}
if (!string.IsNullOrEmpty(data.LineID))
{
sb.AppendFormat(" and LineID like '%{0}%' ", data.LineID);
}
if (!string.IsNullOrEmpty(data.DeviceID))
{
sb.AppendFormat(" and DeviceID like '%{0}%' ", data.DeviceID);
}
if (!string.IsNullOrEmpty(data.IsDown))
{
sb.AppendFormat(" and IsDown like '%{0}%' ", data.IsDown);
}
if (!string.IsNullOrEmpty(data.Status))
{
sb.AppendFormat(" and Status like '%{0}%' ", data.Status);
}
if (string.IsNullOrEmpty(data.bgntime) || string.IsNullOrEmpty(data.endtime))
{
if (string.IsNullOrEmpty(data.bgntime) && string.IsNullOrEmpty(data.endtime))
{
}
else
{
if (string.IsNullOrEmpty(data.bgntime))
{
sb.AppendFormat(" and create_time between '2000-01-11 00:00:00' and '{0}'", data.endtime);
}
else
{
sb.AppendFormat(" and create_time between '{0}' and GETDATE()+1 ", data.bgntime);
}
}
}
else
{
sb.AppendFormat(" and create_time between '{0}' and '{1}'", data.bgntime, data.endtime);
}
return(MSSql.ExecuteQueryDataTable(sb.ToString()));
}
public IList <UsageOfCountryModel> GetUsageOfCountry()
{
IList <UsageOfCountryModel> resp = new List <UsageOfCountryModel>();
try
{
using (MSSql mssql = new MSSql(DBConnectionType.RBAC, _EnvironmentModel))
{
UsageOfCountryModel country_Germany = new UsageOfCountryModel()
{
country = "Germany", usage_count = 200.ToString()
};
UsageOfCountryModel country_USA = new UsageOfCountryModel()
{
country = "United States", usage_count = 300.ToString()
};
UsageOfCountryModel country_Brazil = new UsageOfCountryModel()
{
country = "Brazil", usage_count = 400.ToString()
};
UsageOfCountryModel country_Canada = new UsageOfCountryModel()
{
country = "Canada", usage_count = 500.ToString()
};
UsageOfCountryModel country_France = new UsageOfCountryModel()
{
country = "France", usage_count = 600.ToString()
};
UsageOfCountryModel country_Rusia = new UsageOfCountryModel()
{
country = "RU", usage_count = 700.ToString()
};
UsageOfCountryModel country_Thailand = new UsageOfCountryModel()
{
country = "Thailand", usage_count = 800.ToString()
};
resp.Add(country_Germany);
resp.Add(country_USA);
resp.Add(country_Brazil);
resp.Add(country_Canada);
resp.Add(country_France);
resp.Add(country_Rusia);
resp.Add(country_Thailand);
return(resp);
}
}
catch (Exception ex)
{
_ILogs.LogError("WriteLogs: ", ex.Message.ToString(), ex.StackTrace);
}
return(null);
}
public static void InsertInicioSesion(int ID_Usuario)
{
string res = "";
string con = "Data Source=alexisserver.ceq0e9y8bekm.us-west-2.rds.amazonaws.com;Initial Catalog=preparate_dev;Persist Security Info=True;User ID=Alexis;Password=Proyecto2017";
Classes.Parameter[] p = new Classes.Parameter[]
{
new Classes.Parameter("@Id_Usuario", ID_Usuario)
};
DataTable dt = MSSql.ExecuteStoredProcedure(con, "InsertInicioSesion", p);
}
// string column = "refid,Shift,PDate,FormID,TagID,LineID,DeviceID,IsDown,Status,Code,Msg,RiseEqp,Risecode,Risemsg,RiseTagID,RiseStatus,Model,Start_time,End_time,Duration,Category,Lev1,Lev2,Brand,CBrand,Ctype,stand_time,Reason,Memo,IsDiff,IsPro,IsShift,IsFinish,Create_by,Create_time,Update_by,Update_time";
//@refid,@Shift,@PDate,@FormID,@TagID,@LineID,@DeviceID,@IsDown,@Status,@Code,@Msg,@RiseEqp,@Risecode,@Risemsg,@Model,@Start_time,@End_time,@Duration,@Category,@Lev1,@Lev2,@Brand,@CBrand,@Ctype,@stand_time,@Reason,@Memo,@IsDiff,@IsPro,@IsShift,@IsFinish,@Create_by,@Create_time,@Update_by,@Update_time
public static string CreateNew(DTFiller data)
{
string sql = " insert into DTFiller( Shift,PDate,FormID,TagID,LineID,DeviceID,IsDown,Status,Code,Msg,RiseEqp,Risecode,Risemsg,RiseTagID,RiseStatus,Model,Start_time,End_time,Duration,Category,Lev1,Lev2,Brand,CBrand,Ctype,stand_time,Reason,Memo,IsDiff,IsPro,IsShift,IsFinish,Create_by,Create_time)values(@Shift,@PDate,@FormID,@TagID,@LineID,@DeviceID,@IsDown,@Status,@Code,@Msg,@RiseEqp,@Risecode,@Risemsg,@RiseTagID,@RiseStatus,@Model,@Start_time,@End_time,@Duration,@Category,@Lev1,@Lev2,@Brand,@CBrand,@Ctype,@stand_time,@Reason,@Memo,@IsDiff,@IsPro,@IsShift,@IsFinish,@Create_by,@Create_time)";
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@Shift", data.Shift),
new SqlParameter("@PDate", data.PDate),
new SqlParameter("@FormID", data.FormID),
new SqlParameter("@TagID", data.TagID),
new SqlParameter("@LineID", data.LineID),
new SqlParameter("@DeviceID", data.DeviceID),
new SqlParameter("@IsDown", data.IsDown),
new SqlParameter("@Status", data.Status),
new SqlParameter("@Code", data.Code),
new SqlParameter("@Msg", data.Msg),
new SqlParameter("@RiseEqp", data.RiseEqp),
new SqlParameter("@Risecode", data.Risecode),
new SqlParameter("@Risemsg", data.Risemsg),
new SqlParameter("@RiseTagID", data.RiseTagID),
new SqlParameter("@RiseStatus", data.RiseStatus),
new SqlParameter("@Model", data.Model),
new SqlParameter("@Start_time", data.Start_time),
new SqlParameter("@End_time", data.End_time),
new SqlParameter("@Duration", data.Duration),
new SqlParameter("@Category", data.Category),
new SqlParameter("@Lev1", data.Lev1),
new SqlParameter("@Lev2", data.Lev2),
new SqlParameter("@Brand", data.Brand),
new SqlParameter("@CBrand", data.CBrand),
new SqlParameter("@Ctype", data.Ctype),
new SqlParameter("@stand_time", data.stand_time),
new SqlParameter("@Reason", data.Reason),
new SqlParameter("@Memo", data.Memo),
new SqlParameter("@IsDiff", data.IsDiff),
new SqlParameter("@IsPro", data.IsPro),
new SqlParameter("@IsShift", data.IsShift),
new SqlParameter("@IsFinish", data.IsFinish),
new SqlParameter("@Create_by", data.Create_by),
new SqlParameter("@Create_time", DateTime.Now)
};
foreach (SqlParameter parm in paras)
{
if (parm.Value == null)
{
parm.Value = DBNull.Value;
}
}
return(MSSql.ExecInsertParaTransStr(sql, paras));
}
public static double Get10MinuteDTTime(string tagid)
{
string sql = string.Format("select sum(Duration)Duration from DTRecord where tagid='{0}' and create_time > dateadd(n,-60,getdate()) and status not in('Operating','Lack','Tailback') ", tagid);
DataTable dt = MSSql.ExecuteQueryDataTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(0);
}
else
{
return(Convert.ToDouble(dt.Rows[0]["Duration"]));
}
}
public static UInt64 GetMaxRefid(string FormID)
{
string sql = string.Format(" select max(refid)refid from DTFiller where FormID='{0}'", FormID);
DataTable dt = MSSql.ExecuteQueryDataTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(0);
}
else
{
return(Convert.ToUInt64(dt.Rows[0]["refid"]));
}
}
public static double GetDownTime(string tagid, double minute)
{
DateTime time = DateTime.Now.AddMinutes(minute);
string sql = string.Format("select Isnull(sum(Duration),0)Duration from DTRecord where tagid='{0}' and create_time>'{1}' and status not in('Operating','Lack','Tailback') ", tagid, time);
DataTable dt = MSSql.ExecuteQueryDataTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(0);
}
else
{
return(Convert.ToDouble(dt.Rows[0]["Duration"]));
}
}
