public void UpdateFaultDetails(FaultDetails faultDetails, int roleId = 0)
{
try
{
using (SQLiteConnection sqlite_conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["FRSConnectionString"].ConnectionString))
{
SQLiteCommand cmd = sqlite_conn.CreateCommand();
if (roleId == 2)
{
cmd.CommandText = $"UPDATE TFaultDetails SET ProductId = {faultDetails.ProductID}, FaultTypeID = {faultDetails.FaultTypeID}, FaultDescription = '{faultDetails.FaultDescription}' where FaultID = {faultDetails.FaultID}";
}
else
{
if (String.IsNullOrEmpty(faultDetails.FaultResolvedDate))
{
cmd.CommandText = $"UPDATE TFaultDetails SET ProductId = {faultDetails.ProductID}, StatusID = {faultDetails.StatusID}, FaultResolvedDate = NULL, FaultTypeID = {faultDetails.FaultTypeID}, FaultDescription = '{faultDetails.FaultDescription}', FaultPriority = {faultDetails.FaultPriorityID} where FaultID = {faultDetails.FaultID}";
}
else
{
cmd.CommandText = $"UPDATE TFaultDetails SET ProductId = {faultDetails.ProductID}, StatusID = {faultDetails.StatusID}, FaultResolvedDate = '{Convert.ToDateTime(faultDetails.FaultResolvedDate):yyyy-MM-dd HH:mm:ss}', FaultTypeID = {faultDetails.FaultTypeID}, FaultDescription = '{faultDetails.FaultDescription}', FaultPriority = {faultDetails.FaultPriorityID} where FaultID = {faultDetails.FaultID}";
}
}
sqlite_conn.Open();
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateFaultDetails(FaultDetails faultDetails, int roleId)
{
FaultManagerDAL dal = new FaultManagerDAL();
dal.UpdateFaultDetails(faultDetails, roleId);
}
public int AddFaultDetails(FaultDetails faultDetails, UserDetails userDetails)
{
int faultId = 0;
try
{
using (SQLiteConnection sqlite_conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["FRSConnectionString"].ConnectionString))
{
int userDetailsId = 0;
int customerId = 0;
DataTable dt = new DataTable();
StringBuilder sb = new StringBuilder();
SQLiteCommand cmd = sqlite_conn.CreateCommand();
if (userDetails.RoleID == 2)
{
sb.Append($" SELECT CustomerID FROM TCustomer WHERE UserDetailsId = {userDetails.ID}");
cmd.CommandText = sb.ToString();
sqlite_conn.Open();
SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd);
ad.Fill(dt);
if (dt.Rows.Count > 0)
{
customerId = Convert.ToInt32(dt.Rows[0]["CustomerID"]);
}
}
else
{
sb.Clear();
sb.Append($" SELECT CustomerID FROM TCustomer WHERE Phone = '{faultDetails.CustomerInfo.Phone}' AND Email = '{faultDetails.CustomerInfo.Email}' ");
cmd.CommandText = sb.ToString();
sqlite_conn.Open();
SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd);
ad.Fill(dt);
if (dt.Rows.Count > 0)
{
customerId = Convert.ToInt32(dt.Rows[0]["CustomerID"]);
}
else
{
sb.Clear();
cmd.CommandText = $"INSERT INTO TUserDetails(UserID, UserPassword, RoleID,ManagerID) VALUES('{faultDetails.CustomerInfo.FirstName}','{faultDetails.CustomerInfo.FirstName}',2,NULL)";
cmd.ExecuteNonQuery();
userDetailsId = (int)sqlite_conn.LastInsertRowId;
cmd.CommandText = $"INSERT INTO TCustomer(Name, Phone, Email, UserDetailsId) VALUES('{faultDetails.CustomerInfo.Name}','{faultDetails.CustomerInfo.Phone}','{faultDetails.CustomerInfo.Email}',{userDetailsId})";
cmd.ExecuteNonQuery();
customerId = (int)sqlite_conn.LastInsertRowId;
}
}
sb.Clear();
sb.Append(" INSERT INTO TFaultDetails (ProductId, StatusID, AssignedUserID, FaultReportingDate,CustomerID,FaultResolvedDate,FaultTypeID,FaultDescription,FaultPriority) ");
sb.Append($" VALUES ({faultDetails.ProductID}, {faultDetails.StatusID}, NULL, '{DateTime.Now:yyyy-MM-dd HH:mm:ss}', {customerId},NULL, {faultDetails.FaultTypeID}, '{faultDetails.FaultDescription}',{faultDetails.FaultPriorityID}); ");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
faultId = (int)sqlite_conn.LastInsertRowId;
}
}
catch (Exception ex)
{
throw ex;
}
return(faultId);
}
public int AddFaultDetails(FaultDetails faultDetails, UserDetails userDetails)
{
FaultManagerDAL dal = new FaultManagerDAL();
return(dal.AddFaultDetails(faultDetails, userDetails));
}