public IInfo UpdateLoginStatus(SmartDB dbInstance, string userId, bool isLogin)
{
int rowsAffected = 0;
SqlParameter[] parameters = SqlHelper.GetParameters(dbInstance.Connection.ConnectionString, "REPORT USERINFO_ISLOGIN_UPD");
string cmdText = "REPORT USERINFO_ISLOGIN_UPD";
parameters[0].Value = userId;
parameters[1].Value = isLogin;
IInfo result;
try
{
bool transactionControl = dbInstance.TransactionControl;
if (transactionControl)
{
rowsAffected = SqlHelper.ExecuteNonQuery(dbInstance.Transaction, CommandType.StoredProcedure, cmdText, parameters);
}
else
{
rowsAffected = SqlHelper.ExecuteNonQuery(dbInstance.Connection.ConnectionString, CommandType.StoredProcedure, cmdText, parameters);
}
}
catch (Exception ex)
{
result = new ReturnInfo(ErrorEnum.DataException, ex.Message);
return(result);
}
result = new ReturnInfo(ErrorEnum.NoError, "", rowsAffected);
return(result);
}
public static void OMSFolder()
{
var gateway = Gateway.GatewayList();
foreach (var g in gateway)
{
string[] files = Directory
.GetFiles(@"C:\JizFTP\" + g);
foreach (var file in files)
{
using (SqlConnection connection =
new SqlConnection(SmartDB.ConnectionString()))
{
connection.Open();
if (new DirectoryInfo(file).Name.Contains("GTW_OMS_RAW_") &&
!new DirectoryInfo(file).Name.Contains("lock"))
{
//Retrieve all lines in CSV
System.Data.DataTable newMeter = ReadFile(file);
//BulkCopy
InsertMeterBulkCopy(connection, newMeter);
}
}
Console.WriteLine(file);
}
}
}
public ReturnInfo Create(SmartDB dbInstance, ref Model.MeterProtocol info)
{
IInfo info2 = _dal.Insert(dbInstance, info);
info.IsNew = false;
info.IsDirty = true;
return(new ReturnInfo(info2.Code, info2.Message));
}
public IInfo GetRecordsByDescription(SmartDB dbInstance, string query)
{
string cmdText = "[REPORT NODES_LST]";
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter("@Query", SqlDbType.NVarChar)
};
array[0].Value = query;
return(this.GetRecords(dbInstance, cmdText, array));
}
public ReturnInfo GetMeterProtocolList(SmartDB dbInstance)
{
IInfo records = _dal.GetRecords(dbInstance);
return(new ReturnInfo
{
Code = records.Code,
Message = records.Message,
BizObject = ((records.Code == ErrorEnum.NoError) ? records.BizObject : new List <Model.MeterProtocol>())
});
}
public IInfo GetRecordsByDescription(SmartDB dbInstance, string description)
{
string cmdText = "[REPORT METERREADING_LST_BYSERIALNUMBER]";
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter("@SerialNumber", SqlDbType.NVarChar)
};
array[0].Value = description;
return(this.GetRecords(dbInstance, cmdText, array));
}
public IInfo GetLatestRecord(SmartDB dbInstance, string serialNumber)
{
string cmdText = "[REPORT METERREADING_LATEST]";
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter("@SerialNumber", SqlDbType.NVarChar)
};
array[0].Value = serialNumber;
return(this.GetRecords(dbInstance, cmdText, array));
}
public IInfo GetRecordsByUserName(SmartDB dbInstance, string username)
{
string cmdText = "[REPORT USER_LST_BYUSERNAME]";
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter("@UserName", SqlDbType.NVarChar)
};
array[0].Value = username;
return(this.GetRecords(dbInstance, cmdText, array));
}
public IInfo GetRecordsBySerialNumber(SmartDB dbInstance, string serialnumber)
{
string cmdText = "[REPORT METER_LST_BYSERIALNUMBER]";
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter("@SerialNumber", SqlDbType.NVarChar)
};
array[0].Value = serialnumber;
return(this.GetRecords(dbInstance, cmdText, array));
}
public IInfo GetRecordsBySimCard(SmartDB dbInstance, string description)
{
string cmdText = "[REPORT GATEWAY_LST_BYDESCRIPTION]";
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter("@SimCard", SqlDbType.NVarChar)
};
array[0].Value = description;
return(this.GetRecords(dbInstance, cmdText, array));
}
public IInfo GetRecordsByLogDateTime(SmartDB dbInstance, string description)
{
string cmdText = "[REPORT GATEWAYLOG_LST_BYLOGDATETIME]";
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter("@LogDateTime", SqlDbType.NVarChar)
};
array[0].Value = description;
return(this.GetRecords(dbInstance, cmdText, array));
}
public ReturnInfo GetMeterSyncMeterReading(SmartDB dbInstance)
{
IInfo record = _dal.MeterSyncMeterReading(dbInstance);
return(new ReturnInfo
{
BizObject = ((record.Code == ErrorEnum.NoError) ? record.BizObject : null),
Code = record.Code,
Message = record.Message,
RowsAffected = record.RowsAffected
});
}
public ReturnInfo GetRecordsByMacCsv(SmartDB dbInstance, string gatewayMacAddress, string csvFilename)
{
IInfo record = _dal.GetRecordsByMacCsv(dbInstance, gatewayMacAddress, csvFilename);
return(new ReturnInfo
{
BizObject = ((record.Code == ErrorEnum.NoError) ? record.BizObject : null),
Code = record.Code,
Message = record.Message,
RowsAffected = record.RowsAffected
});
}
public ReturnInfo GetUserByUserName(SmartDB dbInstance, string UserName)
{
IInfo record = _dal.GetRecordsByUserName(dbInstance, UserName);
return(new ReturnInfo
{
BizObject = ((record.Code == ErrorEnum.NoError) ? record.BizObject : null),
Code = record.Code,
Message = record.Message,
RowsAffected = record.RowsAffected
});
}
public ReturnInfo GetTopCity(SmartDB dbInstance)
{
IInfo record = _dal.GetRecordTop(dbInstance);
return(new ReturnInfo
{
BizObject = ((record.Code == ErrorEnum.NoError) ? record.BizObject : null),
Code = record.Code,
Message = record.Message,
RowsAffected = record.RowsAffected
});
}
public ReturnInfo GetLatestMeterReadingRecord(SmartDB dbInstance, string SerialNumber)
{
IInfo record = _dal.GetLatestRecord(dbInstance, SerialNumber);
return(new ReturnInfo
{
BizObject = ((record.Code == ErrorEnum.NoError) ? record.BizObject : null),
Code = record.Code,
Message = record.Message,
RowsAffected = record.RowsAffected
});
}
public ReturnInfo GetNodes(SmartDB dbInstance, string query)
{
IInfo records = _dal.GetRecordsByDescription(dbInstance, query);
return(new ReturnInfo
{
BizObject = ((records.Code == ErrorEnum.NoError) ? records.BizObject : null),
Code = records.Code,
Message = records.Message,
RowsAffected = records.RowsAffected
});
}
public ReturnInfo GetMeterProtocolById(SmartDB dbInstance, int Id)
{
IInfo record = _dal.GetRecord(dbInstance, Id);
return(new ReturnInfo
{
BizObject = ((record.Code == ErrorEnum.NoError) ? record.BizObject : null),
Code = record.Code,
Message = record.Message,
RowsAffected = record.RowsAffected
});
}
public ReturnInfo GetGatewayLogById(SmartDB dbInstance, string Id)
{
IInfo record = _dal.GetRecord(dbInstance, Id);
return(new ReturnInfo
{
BizObject = ((record.Code == ErrorEnum.NoError) ? record.BizObject : null),
Code = record.Code,
Message = record.Message,
RowsAffected = record.RowsAffected
});
}
public ReturnInfo GetMeterProtocolByDescription(SmartDB dbInstance, string Description)
{
IInfo record = _dal.GetRecordsByDescription(dbInstance, Description);
return(new ReturnInfo
{
BizObject = ((record.Code == ErrorEnum.NoError) ? record.BizObject : null),
Code = record.Code,
Message = record.Message,
RowsAffected = record.RowsAffected
});
}
public IInfo GetRecord(SmartDB dbInstance, string userId, out UserInfo info)
{
info = null;
string cmdText = "REPORT USERINFO_GET_USER_ID";
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter("@USERID", SqlDbType.NVarChar)
};
array[0].Value = userId;
IInfo result;
try
{
bool transactionControl = dbInstance.TransactionControl;
if (transactionControl)
{
using (SqlDataReader sqlDataReader = SqlHelper.ExecuteReader(dbInstance.Transaction, CommandType.StoredProcedure, cmdText, array))
{
bool hasRows = sqlDataReader.HasRows;
if (!hasRows)
{
result = new ReturnInfo(ErrorEnum.NoRecord, string.Format("No record found for ID: {0}", userId));
return(result);
}
sqlDataReader.Read();
this.SetInfo(out info, sqlDataReader);
}
}
else
{
using (SqlDataReader sqlDataReader2 = SqlHelper.ExecuteReader(dbInstance.Connection.ConnectionString, CommandType.StoredProcedure, cmdText, array))
{
bool hasRows2 = sqlDataReader2.HasRows;
if (!hasRows2)
{
result = new ReturnInfo(ErrorEnum.NoRecord, string.Format("No record found for ID: {0}", userId));
return(result);
}
sqlDataReader2.Read();
this.SetInfo(out info, sqlDataReader2);
}
}
}
catch (Exception ex)
{
result = new ReturnInfo(ErrorEnum.DataException, ex.Message);
return(result);
}
result = new ReturnInfo();
return(result);
}
public ReturnInfo CheckRecordDuplicate(SmartDB dbInstance, string SerialNumber, DateTime ReadingDate,
string CSVType, string ReadingValue)
{
IInfo record = _dal.CheckRecord(dbInstance, SerialNumber, ReadingDate, CSVType, ReadingValue);
return(new ReturnInfo
{
BizObject = ((record.Code == ErrorEnum.NoError) ? record.BizObject : null),
Code = record.Code,
Message = record.Message,
RowsAffected = record.RowsAffected
});
}
public IInfo GetRecordsByMacCsv(SmartDB dbInstance, string gatewayMacAddress, string csvFilename)
{
string cmdText = "[REPORT GATEWAYLOG_LST_BYMACCSV]";
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter("@GatewayMacAddress", SqlDbType.NVarChar),
new SqlParameter("@CSVFilename", SqlDbType.NVarChar)
};
array[0].Value = gatewayMacAddress;
array[1].Value = csvFilename;
return(this.GetRecords(dbInstance, cmdText, array));
}
public static string EFConnectionString()
{
EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
var connectionSettings = new SmartDB().Connection;
entityBuilder.Provider = "System.Data.SqlClient";
entityBuilder.ProviderConnectionString = connectionSettings.ConnectionString;
entityBuilder.Metadata = "res://*/TMFModel.csdl|res://*/TMFModel.ssdl|res://*/TMFModel.msl";
return(entityBuilder.ConnectionString);
}
public IInfo Update(SmartDB dbInstance, UserInfo info)
{
int rowsAffected = 0;
SqlParameter[] updateParameters = this.GetUpdateParameters(dbInstance.Connection.ConnectionString);
string cmdText = "REPORT USERINFO_UPD";
updateParameters[0].Value = info.Id;
updateParameters[1].Value = info.Username;
updateParameters[2].Value = info.Password;
updateParameters[3].Value = info.Name;
updateParameters[4].Value = info.Role;
updateParameters[5].Value = info.IsActive;
IInfo result;
//try
//{
// bool transactionControl = dbInstance.TransactionControl;
// if (transactionControl)
// {
// rowsAffected = SqlHelper.ExecuteNonQuery(dbInstance.Transaction, CommandType.StoredProcedure, cmdText, updateParameters);
// }
// else
// {
rowsAffected = SqlHelper.ExecuteNonQuery(dbInstance.Connection.ConnectionString, CommandType.StoredProcedure, cmdText, updateParameters);
//SqlDataReader sqlDataReader = SqlHelper.ExecuteReader(dbInstance.Connection.ConnectionString, CommandType.StoredProcedure, cmdText, updateParameters);
//bool hasRows2 = sqlDataReader.HasRows;
//if (hasRows2)
//{
// result = new ReturnInfo(ErrorEnum.TransactionError, "Update User failed");
// return result;
//}
//sqlDataReader.Close();
// }
//}
//catch (Exception ex)
//{
// bool flag2 = ex.Message.Contains("unique constraint");
// if (flag2)
// {
// result = new ReturnInfo(ErrorEnum.UniqueConstraint, string.Format("User {0} already exist in the system", info.Name));
// return result;
// }
// result = new ReturnInfo(ErrorEnum.DataException, ex.Message);
// return result;
//}
result = new ReturnInfo(ErrorEnum.NoError, "", rowsAffected);
return(result);
}
public IInfo GetRecordTop(SmartDB dbInstance)
{
//string cmdText = "[REPORT DMZ_TOP]";
//return this.GetRecords(dbInstance, cmdText, null);
string sQL_GET = "[REPORT DMZ_TOP]";
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter(this.PARM_ID, SqlDbType.NVarChar)
};
array[0].Value = null;
Model.DMZ bizObject = null;
IInfo result;
try
{
bool transactionControl = dbInstance.TransactionControl;
SqlDataReader sqlDataReader;
if (transactionControl)
{
sqlDataReader = SqlHelper.ExecuteReader(dbInstance.Transaction, CommandType.StoredProcedure, sQL_GET, array);
}
else
{
sqlDataReader = SqlHelper.ExecuteReader(SqlHelper.MyConnectionString, CommandType.StoredProcedure, sQL_GET, array);
}
bool hasRows = sqlDataReader.HasRows;
if (hasRows)
{
sqlDataReader.Read();
this.SetInfo(out bizObject, sqlDataReader);
result = new ReturnInfo
{
BizObject = bizObject,
Code = ErrorEnum.NoError
};
}
else
{
result = new ReturnInfo(ErrorEnum.NoRecord, string.Format("No record found"));
}
}
catch (Exception ex)
{
result = new ReturnInfo(ErrorEnum.DataException, ex.Message);
}
return(result);
}
public ReturnInfo Delete(SmartDB dbInstance, int Id)
{
bool flag = Id == 0;
ReturnInfo result;
if (flag)
{
result = new ReturnInfo(ErrorEnum.InvalidInput, "Invalid input. ID not found.");
}
else
{
IInfo info = _dal.Delete(dbInstance, Id);
result = new ReturnInfo(info.Code, info.Message, info.RowsAffected);
}
return(result);
}
public ReturnInfo Delete(SmartDB dbInstance, string MacAddress)
{
bool flag = MacAddress == null;
ReturnInfo result;
if (flag)
{
result = new ReturnInfo(ErrorEnum.InvalidInput, "Invalid input. MAC not found.");
}
else
{
IInfo info = _dal.Delete(dbInstance, MacAddress);
result = new ReturnInfo(info.Code, info.Message, info.RowsAffected);
}
return(result);
}
public override IInfo GetRecord(SmartDB dbInstance, object Id)
{
string sQL_GET = this.SQL_GET;
SqlParameter[] array = new SqlParameter[]
{
new SqlParameter(this.PARM_ID, SqlDbType.NVarChar)
};
array[0].Value = Id;
Model.Gateway bizObject = null;
IInfo result;
try
{
bool transactionControl = dbInstance.TransactionControl;
SqlDataReader sqlDataReader;
if (transactionControl)
{
sqlDataReader = SqlHelper.ExecuteReader(dbInstance.Transaction, CommandType.StoredProcedure, sQL_GET, array);
}
else
{
sqlDataReader = SqlHelper.ExecuteReader(SqlHelper.MyConnectionString, CommandType.StoredProcedure, sQL_GET, array);
}
bool hasRows = sqlDataReader.HasRows;
if (hasRows)
{
sqlDataReader.Read();
this.SetInfo(out bizObject, sqlDataReader);
result = new ReturnInfo
{
BizObject = bizObject,
Code = ErrorEnum.NoError
};
}
else
{
result = new ReturnInfo(ErrorEnum.NoRecord, string.Format("No record found for ID: {0}", Id));
}
}
catch (Exception ex)
{
result = new ReturnInfo(ErrorEnum.DataException, ex.Message);
}
return(result);
}
public IInfo GetRecord(SmartDB dbInstance, string query, SqlParameter[] array)
{
//string sQL_GET = SQL_GET_USER_ID;
//SqlParameter[] array = new SqlParameter[]
//{
// new SqlParameter("@Id", SqlDbType.NVarChar)
//};
//array[0].Value = Id;
UserInfo bizObject = null;
IInfo result;
try
{
bool transactionControl = dbInstance.TransactionControl;
SqlDataReader sqlDataReader;
if (transactionControl)
{
sqlDataReader = SqlHelper.ExecuteReader(dbInstance.Transaction, CommandType.StoredProcedure, query, array);
}
else
{
sqlDataReader = SqlHelper.ExecuteReader(SqlHelper.MyConnectionString, CommandType.StoredProcedure, query, array);
}
bool hasRows = sqlDataReader.HasRows;
if (hasRows)
{
sqlDataReader.Read();
this.SetInfo(out bizObject, sqlDataReader);
result = new ReturnInfo
{
BizObject = bizObject,
Code = ErrorEnum.NoError
};
}
else
{
result = new ReturnInfo(ErrorEnum.NoRecord, "No record found.");
}
}
catch (Exception ex)
{
result = new ReturnInfo(ErrorEnum.DataException, ex.Message);
}
return(result);
}
