internal List <DMLoaiThuChiInfor> Search(DMLoaiThuChiInfor dmLoaiThuChiInfor)
{
CreateGetListCommand(Declare.StoreProcedureNamespace.spLoaiThuChiSearch);
Parameters.AddWithValue("@KyHieu", dmLoaiThuChiInfor.KyHieu);
Parameters.AddWithValue("@Ten", dmLoaiThuChiInfor.Ten);
return(FillToList <DMLoaiThuChiInfor>());
}
public bool updateMember([FromBody] FamilyMember familyMember)
{
SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ParentalAppConnection"].ConnectionString);
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_UpdateFamilyMember"; qweeeeeeeeeeeeeeeeeeeeeeeeetrkkkkkk3tr452225888888888888888888
cmd.Parameters.AddWithValue("@id", familyMember.id);
cmd.Parameters.AddWithValue("@firstname", familyMember.firstName);
cmd.Parameters.AddWithValue("@lastname", familyMember.lastName);
cmd.Parameters.AddWithValue("@adress", familyMember.address);
cmd.Parameters.AddWithValue("@mobilenumber", familyMember.mobileNumber);
cmd.Parameters.AddWithValue("@gender", familyMember.gender);
cn.Open();
int effectedRows = cmd.ExecuteNonQuery();
if (effectedRows > 0)
{
return(true);
}
else
{
return(false);
}
}
public void InsertChucNang(int idNhomNguoiDung, int idChucNang)
{
CreateCommonCommand(Declare.StoreProcedureNamespace.spNhomNguoiDungInsertCN);
Parameters.AddWithValue("@p_IdNhomNguoiDung", idNhomNguoiDung);
Parameters.AddWithValue("@p_IdChucNang", idChucNang);
ExecuteNoneQuery();
}
internal void Delete(DMQuyenHoaDonInfor dmQuyenHoaDonInfor)
{
CreateCommonCommand(Declare.StoreProcedureNamespace.spHoaDonDelete);
Parameters.AddWithValue("@KyHieuHoaDon", dmQuyenHoaDonInfor.KyHieuHoaDon);
Parameters.AddWithValue("@KyTuDauSerie", dmQuyenHoaDonInfor.KyTuDauSerie);
ExecuteNoneQuery();
}
public void DeleteMember(int memberId)
{
try
{
/*Opening database connection*/
OpenConn(true);
/*Beginning database transaction, which helps to rollback the deleted data if anything went wrong.
* This database call has multiple deletes in it.
*/
BeginTran();
Parameters.AddWithValue("@MemberId", memberId);
ExecuteNonQuery(DbCommands.sp_DeleteMemberById);
/*If all actions in procedure are performed well then commit the transaction.*/
CommitTran();
}
catch
{
/*If anything went wrong in deleting records from any of the parent or child table then rollback the transaction.*/
RollBackTran();
}
finally
{
/*Close Connection, this code will always executes..*/
OpenConn(false);
}
}
internal List <DMChucNangInfor> Search(DMChucNangInfor dmChucNangInfor)
{
CreateGetListCommand(Declare.StoreProcedureNamespace.spChucNangSearch);
Parameters.AddWithValue("@MaChucNang", dmChucNangInfor.MaChucNang);
Parameters.AddWithValue("@TenChucNang", dmChucNangInfor.TenChucNang);
return(FillToList <DMChucNangInfor>());
}
internal List <DMQuyenHoaDonInfor> Search(DMQuyenHoaDonInfor dmQuyenHoaDonInfor)
{
CreateGetListCommand(Declare.StoreProcedureNamespace.spHoaDonSearch);
Parameters.AddWithValue("@KyHieuHoaDon", dmQuyenHoaDonInfor.KyHieuHoaDon);
Parameters.AddWithValue("@KyTuDauSerie", dmQuyenHoaDonInfor.KyTuDauSerie);
return(FillToList <DMQuyenHoaDonInfor>());
}
public LocationInfo GetLocationById(int locationId)
{
LocationInfo locationInfo = new LocationInfo();
try
{
/*Opening database connection*/
OpenConn(true);
Parameters.AddWithValue("@LocationId", locationId);
SqlDataReader dr = ExecReader(DbCommands.sp_GetLocationById);
if (dr.Read())
{
/*Assigning values to locationInfo object*/
locationInfo.LocationId = Convert.ToInt32(dr["LocationId"]);
locationInfo.LocationName = dr["LocationName"].ToString();
locationInfo.Description = dr["Description"].ToString();
locationInfo.LocationUrl = dr["LocationUrl"].ToString();
}
}
finally
{
/*Close Connection, this code will always executes..*/
OpenConn(false);
}
/*Returning filled object.*/
return(locationInfo);
}
public DMQuyenHoaDonInfor GetHoaDonInfo(string kytudau, string kyhieuhoadon)
{
CreateGetListCommand(Declare.StoreProcedureNamespace.spHoaDonGetById);
Parameters.AddWithValue("@KyTuDau", kytudau);
Parameters.AddWithValue("@KyHieuHoaDon", kyhieuhoadon);
return(FillToObject <DMQuyenHoaDonInfor>());
}
internal List <CauHinh_LoaiSanPhamInfo> Search(CauHinh_LoaiSanPhamInfo info)
{
CreateGetListCommand(Declare.StoreProcedureNamespace.spCauHinhLoaiSanPhamSearch);
Parameters.AddWithValue("@TenChucNang", info.TenCauHinh);
Parameters.AddWithValue("@MaChucNang", info.MaCauHinh);
return(FillToList <CauHinh_LoaiSanPhamInfo>());
}
internal List <DMTaxCodeInfor> Search(DMTaxCodeInfor dmMaLoiInfor)
{
CreateGetListCommand(Declare.StoreProcedureNamespace.spTaxCodeSearch);
Parameters.AddWithValue("@TenLoi", dmMaLoiInfor.Name);
Parameters.AddWithValue("@IdLoaiItem", dmMaLoiInfor.Code);
return(FillToList <DMTaxCodeInfor>());
}
public int UpdateEmployeeSalary(int percentage, int deptid, int posid)
{
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
OracleCommand cmd = new OracleCommand("GIVE_RAISE_SP", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("PV_PERCENTAGE_RAISE_I", OracleType.Int32).Value = percentage;
cmd.Parameters.AddWithValue("PV_POSITIONID_I", OracleType.Int32).Value = posid;
stac cmd.Parameters.AddWithValue("PV_DEPTID_I", OracleType.Int32).Value = deptid;
cmd.Parameters.AddWithValue("PV_NUM_EMPLOYEES_O", OracleType.Int32).Direction = ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(cmd);
try
{
con.Open();
da.UpdateCommand = cmd;
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
finally
{
con.Close();
}
return Convert.ToInt32(cmd.Parameters["PV_NUM_EMPLOYEES_O"].Value);
}
internal List <DMTienTeInfor> Search(DMTienTeInfor dmTienTeInfor)
{
CreateGetListCommand(Declare.StoreProcedureNamespace.spTienTeSearch);
Parameters.AddWithValue("@TenTienTe", dmTienTeInfor.TenTienTe);
Parameters.AddWithValue("@KyHieu", dmTienTeInfor.KyHieu);
return(FillToList <DMTienTeInfor>());
}
public bool Exist(string pMaNhom)
{
CreateCommonCommand(Declare.StoreProcedureNamespace.spNhomNguoiDungExistNND);
Parameters.AddWithValue("@p_Count", 0).Direction = ParameterDirection.Output;
Parameters.AddWithValue("@p_MaNhom", pMaNhom);
ExecuteNoneQuery();
return(Common.IntValue(Parameters["@p_Count"].Value) == 1);
}
internal bool Exist(DMTaxCodeInfor dmTaxCodeInfor)
{
CreateCommonCommand(Declare.StoreProcedureNamespace.spTaxCodeExist);
Parameters.AddWithValue("@Count", 0).Direction = ParameterDirection.Output;
Parameters.AddWithValue("@IdTaxCode", dmTaxCodeInfor.IdTaxCode);
Parameters.AddWithValue("@Code", dmTaxCodeInfor.Code);
ExecuteNoneQuery();
return(Convert.ToInt32(Parameters["@Count"].Value) == 1);
}
internal bool Exist(DMLoaiItemInfor dmLoaiItemInfor)
{
CreateCommonCommand(Declare.StoreProcedureNamespace.spLoaiItemExist);
Parameters.AddWithValue("@Count", 0).Direction = ParameterDirection.Output;
Parameters.AddWithValue("@IdLoaiItem", dmLoaiItemInfor.IdLoaiItem);
Parameters.AddWithValue("@MaLoaiItem", dmLoaiItemInfor.MaLoaiItem);
ExecuteNoneQuery();
return(Convert.ToInt32(Parameters["@Count"].Value) == 1);
}
internal bool Exist(CauHinh_LoaiSanPhamInfo info)
{
CreateCommonCommand(Declare.StoreProcedureNamespace.spCauHinhLoaiSanPhamExist);
Parameters.AddWithValue("@Count", 0).Direction = ParameterDirection.Output;
Parameters.AddWithValue("@IdChucNang", info.IdCauHinh);
Parameters.AddWithValue("@TenChucNang", info.MaCauHinh);
ExecuteNoneQuery();
return(Convert.ToInt32(Parameters["@Count"].Value) == 1);
}
internal bool Exist(DMQuyenHoaDonInfor dmQuyenHoaDonInfor)
{
CreateCommonCommand(Declare.StoreProcedureNamespace.spHoaDonExist);
Parameters.AddWithValue("@Count", 0).Direction = ParameterDirection.Output;
Parameters.AddWithValue("@KyHieuHoaDon", dmQuyenHoaDonInfor.KyHieuHoaDon);
Parameters.AddWithValue("@KyTuDauSerie", dmQuyenHoaDonInfor.KyTuDauSerie);
ExecuteNoneQuery();
return(Convert.ToInt32(Parameters["@Count"].Value) == 1);
}
/// <summary>
/// Fetch Output Parameter values include input values
/// </summary>
/// <param name="collection"></param>
/// <param name="parameterPrefixText"></param>
protected virtual void GetOutputParamterValuesIncludeInput(DbParameterCollection collection, string parameterPrefixText)
{
Parameters.Clear();
foreach (DbParameter param in collection)
{
if (param.Direction == ParameterDirection.Output || param.Direction == ParameterDirection.InputOutput)
{
Parameters.AddWithValue(param.ParameterName.Substring(parameterPrefixText.Length), param.Value);
}
}
}
/// <summary>
/// Fetch Output parameter values
/// </summary>
/// <param name="collection">DbParameterCollection</param>
/// <param name="parameterPrefixText">parameter prefix Text</param>
protected void GetOutputParamterValues(DbParameterCollection collection, string parameterPrefixText)
{
var dbparams = from DbParameter d in collection
where d.Direction == ParameterDirection.Output ||
d.Direction == ParameterDirection.InputOutput
select new { ParamName = d.ParameterName, Value = d.Value };
foreach (var param in dbparams)
{
Parameters.AddWithValue(param.ParamName.Substring(parameterPrefixText.Length), param.Value);
}
}
private void FillParmeters(MemberInfo memberInfo, string action)
{
Parameters.AddWithValue("@MemberId", memberInfo.MemberId.ToString());
Parameters.AddWithValue("@AddressId", memberInfo.AddressInfo.AddressId.ToString());
Parameters.AddWithValue("@Lastname", memberInfo.LastName);
Parameters.AddWithValue("@FirstName", memberInfo.FirstName);
Parameters.AddWithValue("@MiddleName", memberInfo.MiddleName);
Parameters.AddWithValue("@PhoneNo", memberInfo.PhoneNo);
Parameters.AddWithValue("@MobileNo", memberInfo.MobileNo);
Parameters.AddWithValue("@Email", memberInfo.Email);
Parameters.AddWithValue("@MemberType", memberInfo.MemberType);
Parameters.AddWithValue("@Action", action);
}
public void Deletelocation(int locationId)
{
try
{
/*Opening database connection*/
OpenConn(true);
Parameters.AddWithValue("@LocationId", locationId);
ExecuteNonQuery(DbCommands.sp_DeleteLocationById);
}
finally
{
/*Close Connection, this code will always executes..*/
OpenConn(false);
}
}
public IEnumerable <double> FetchLocationData(string tag, DateTime startDate, DateTime endDate, bool isFreqFetch)
{
string colName = "mwh";
if (isFreqFetch)
{
colName = "freq";
}
List <double> res = new List <double>();
// Connect to a PostgreSQL database
NpgsqlConnection conn = new NpgsqlConnection(_configuration["ConnectionStrings:MeterConnection"]);
conn.Open();
// Define a query
NpgsqlCommand command = new NpgsqlCommand(@$ "SELECT data_time, {colName} FROM public.location_energy_data
where location_id=@locationId and data_time
between @startTime and @endTime order by data_time", conn);
command.Parameters.AddWithValue("@locationId", tag);
command.Parameters.AddWithValue("@startTime", startDate);
command.Parameters.AddWithValue("@endTime", endDate);
// Execute the query and obtain a result set
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.HasRows)
{
while (dr.Read())
{
DateTime dt = dr.GetDateTime(0);
double ts = TimeUtils.ToMillisSinceUnixEpoch(dt);
double val = dr.GetDouble(1);
if (!isFreqFetch)
{
val *= 4;
}
res.Add(ts);
res.Add(val);
}
dr.NextResult();
}
dr.Dispose();
conn.Close();
return(res);
}
private void FillParameters(LocationInfo locationInfo, string action)
{
Parameters.AddWithValue("@LocationName", locationInfo.LocationName);
Parameters.AddWithValue("@Description", locationInfo.Description);
Parameters.AddWithValue("@LocationUrl", locationInfo.LocationUrl);
Parameters.AddWithValue("@Action", action);
/*Declaring output parameters which helps to retrieve the ids after performing any database operations*/
SqlParameter locationId = new SqlParameter();
locationId.ParameterName = "@LocationId";
locationId.Value = locationInfo.LocationId;
locationId.Direction = ParameterDirection.InputOutput;
locationId.DbType = DbType.Int32;
Parameters.Add(locationId);
}
public int Insert(NhomNguoiDungInfor nhomNguoiDung)
{
CreateCommonCommand(Declare.StoreProcedureNamespace.spNhomNguoiDungInsert);
Parameters.AddWithValue("@p_IdNhomNguoiDung", 0).Direction = ParameterDirection.Output;
Parameters.AddWithValue("@p_MaNhom", nhomNguoiDung.MaNhom);
Parameters.AddWithValue("@p_TenNhom", nhomNguoiDung.TenNhom);
Parameters.AddWithValue("@p_MoTa", nhomNguoiDung.MoTa);
Parameters.AddWithValue("@p_SuDung", (nhomNguoiDung.SuDung ? 1 : 0));
Parameters.AddWithValue("@p_XemTon", nhomNguoiDung.XemTon);
Parameters.AddWithValue("@p_QuanTri", nhomNguoiDung.QuanTri);
Parameters.AddWithValue("@p_DieuChuyen", nhomNguoiDung.DieuChuyen);
Parameters.AddWithValue("@p_SuaChungTu", nhomNguoiDung.SuaChungTu);
Parameters.AddWithValue("@p_SaleAdmin", nhomNguoiDung.SaleAdmin);
Parameters.AddWithValue("@p_XemBaoCao", nhomNguoiDung.XemBaoCao);
Parameters.AddWithValue("@p_ShowNotify", nhomNguoiDung.ShowNotify);
Parameters.AddWithValue("@p_NhomQuyenHan", nhomNguoiDung.NhomQuyenHan);
Parameters.AddWithValue("@p_ChonTrungTamHienTai", nhomNguoiDung.ChonTrungTamHienTai);
ExecuteNoneQuery();
return(Common.IntValue(Parameters["@p_IdNhomNguoiDung"].Value.ToString()));
}
public MemberInfo GetMemberById(int memberId)
{
try
{
/*Opening database connection*/
OpenConn(true);
Parameters.AddWithValue("@MemberId", memberId);
SqlDataReader dr = ExecReader(DbCommands.sp_GetMemberById);
/*Creating object to fill the data*/
MemberInfo memberInfo = new MemberInfo();
if (dr.Read())
{
FillMember(memberInfo, dr);
}
/*Return filled object.*/
return(memberInfo);
}
finally
{
OpenConn(false);
}
}
internal void Delete(CauHinh_LoaiSanPhamInfo info)
{
CreateCommonCommand(Declare.StoreProcedureNamespace.spCauHinhLoaiSanPhamDelete);
Parameters.AddWithValue("@IdLoaiSP", info.IdCauHinh);
ExecuteNoneQuery();
}
public async Task <List <double> > GetHistDeviceData(string measTag, DateTime startTime, DateTime endTime)
{
List <double> res = new List <double>();
List <string> measSegs = measTag.Split('|').ToList();
// device name can be like Server Room|Temperature or UPS Room|Humidity
if (measSegs.Count != 2)
{
return(new List <double>());
}
string deviceName = measSegs[0];
deviceName = deviceName.Replace("_", " ");
string measType = measSegs[1];
string tempMeasType = "Temperature";
string humMeasType = "Humidity";
if (!new List <string>()
{
tempMeasType, humMeasType
}.Any(s => measType.ToLower().Equals(s.ToLower())))
{
return(new List <double>());
}
// Connect to a PostgreSQL database
NpgsqlConnection conn = new NpgsqlConnection(_scadaArchiveConnStr);
conn.Open();
// field1 is temp, field2 is humidity
string valColName = (measType == tempMeasType) ? "temp_val" : "hum_val";
// Define a query
NpgsqlCommand command = new NpgsqlCommand(@$ "SELECT data_time, {valColName} FROM public.devices_time_data
where device_name=@deviceName and data_time
between @startTime and @endTime order by data_time", conn);
command.Parameters.AddWithValue("@deviceName", deviceName);
command.Parameters.AddWithValue("@startTime", startTime);
command.Parameters.AddWithValue("@endTime", endTime);
// Execute the query and obtain a result set
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.HasRows)
{
while (dr.Read())
{
DateTime dt = dr.GetDateTime(0);
double ts = TimeUtils.ToMillisSinceUnixEpoch(dt);
double val = dr.GetDouble(1);
res.Add(ts);
res.Add(val);
}
dr.NextResult();
}
dr.Dispose();
conn.Close();
return(await Task.FromResult(res));
}
public DMLoaiItemInfor GetTrungTamByIdInfo(int id)
{
CreateGetListCommand(Declare.StoreProcedureNamespace.spLoaiItemGetById);
Parameters.AddWithValue("@IdLoaiItem", id);
return(FillToObject <DMLoaiItemInfor>());
}
internal List <DMLoaiItemInfor> Search(DMLoaiItemInfor dmLoaiItemInfor)
{
CreateGetListCommand(Declare.StoreProcedureNamespace.spLoaiItemSearch);
Parameters.AddWithValue("@MaLoaiItem", dmLoaiItemInfor.MaLoaiItem);
return(FillToList <DMLoaiItemInfor>());
}