public TesterTypeCollection generateTesterTable(TestPassTesterRoles tester)
{
TesterTypeCollection TesterTypeDataTable = new TesterTypeCollection();
List <TesterType> _lst1 = new List <TesterType>();
if (tester != null)
{
if (tester.roleArray != null)
{
if (tester.action.ToLower() == "edit")
{
_lst1 = getTesterByUserIdTestPassId(tester.oldTesterspUserId, tester.testPassId);
}
foreach (var item in _lst1)
{
string _roleId = item.Role_ID.ToString();
if (tester.roleArray.IndexOf(_roleId) == -1)
{
continue;
}
ClsTesterTypeDataTable dr = new ClsTesterTypeDataTable();
dr.Tester_ID = item.Tester_ID;
dr.TestPass_ID = (tester.testPassId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.testPassId);
dr.Area_ID = (tester.areaId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.areaId);
dr.Role_ID = item.Role_ID;
if (tester.action.ToLower() == "edit")
{
dr.User_ID = (tester.oldTesterspUserId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.oldTesterspUserId);
}
else
{
dr.User_ID = (tester.spUserId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.spUserId);
}
TesterTypeDataTable.Add(dr);
/*old record added to datatable*/
}
int flagTesterId = 0;// counter for rowid
for (int i = 0; i <= tester.roleArray.Count - 1; i++)
{
if (tester.action.ToLower() == "edit")
{/*checking if new tester role exists for old tester*/
List <TesterType> _dr1 = new List <TesterType>();
//_dr1= _lst1.Select( "Role_ID = " + tester.roleArray[i]);
_dr1 = (from x in _lst1 where x.Role_ID == Convert.ToInt32(tester.roleArray[i]) select x).ToList();
if (tester.spUserId.Trim() == tester.oldTesterspUserId.Trim())
{
if (_dr1.Count > 0)//a new record is not inserted if same record is changed
{
continue;
}
}
}
ClsTesterTypeDataTable dr = new ClsTesterTypeDataTable();
dr.Tester_ID = (tester.testerID == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.testerID);
if (string.IsNullOrEmpty(tester.testerID) && tester.spUserId.Trim() != tester.oldTesterspUserId.Trim())
{/*block to execute when tester has changed*/
string whClause = "t.TestPass_ID =" + tester.testPassId + " and t.User_ID =" + tester.oldTesterspUserId + " and t.role_id =" + tester.roleArray[i];
try
{
//getting testerId of old tester in case of tester update so that old tester gets replaced with new tester details, role is considered here
string resultVal = GetSingleValue("Testers t", "t.Tester_ID", whClause, null);
if (string.IsNullOrEmpty(resultVal))
{
resultVal = "0";
if (tester.action.ToLower() == "edit")
{
List <TesterType> _dr2 = new List <TesterType>();
_dr2 = (from x in _lst1 where x.Role_ID == Convert.ToInt32(tester.roleArray[i]) select x).ToList();
//DataRow[] dataRowTester = dtEditTester.Select("Role_ID = " + tester.roleArray[i]);
if (_dr2.Count == 0)//a new record is not inserted if same record is changed
{
// role is not considered here, tester id is fetched by rowid from database
whClause = "TestPass_ID = " + tester.testPassId + " and User_ID = " + tester.oldTesterspUserId + ")as temp where row=" + (flagTesterId + 1);
resultVal = GetSingleValue("Testers", "Tester_ID from(select Tester_ID ,row_number() over(order by tester_id) as 'row'", whClause, "");
flagTesterId++;// counter for rowid
}
}
dr.Tester_ID = Convert.ToInt32(resultVal);//can be repetative, which is set to '0' in code block below
}
else
{
dr.Tester_ID = Convert.ToInt32(resultVal);
}
}
catch (Exception e)
{
}
}
dr.TestPass_ID = (tester.testPassId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.testPassId);
dr.Area_ID = (tester.areaId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.areaId);
dr.Role_ID = Convert.ToInt32(tester.roleArray[i]);
dr.User_ID = (tester.spUserId == "") ? Convert.ToInt32(DBNull.Value) : Convert.ToInt32(tester.spUserId);
TesterTypeDataTable.Add(dr);
}
}
// r
if (tester.action.ToLower() == "edit")
{
if (tester.spUserId.Trim() != tester.oldTesterspUserId.Trim())
{
//dtTester = dtTester.AsEnumerable().OrderBy(z => z.Field<int>("Tester_Id")).OrderBy(z => z.Field<int>("Role_Id")).OrderBy(z => z.Field<int>("User_Id")).CopyToDataTable();
Dictionary <int, int> oldData = new Dictionary <int, int>();
// IEnumerable<DataRow> rows = from row in TesterTypeDataTable.AsQueryable() select row;
foreach (var row in TesterTypeDataTable)
{/*getting old testers' data for further use in dictionary*/
if (row.User_ID.ToString().Trim() == tester.oldTesterspUserId.Trim())
{
oldData.Add(row.Tester_ID, row.Role_ID);
}
}
foreach (var row in TesterTypeDataTable)
{
if (row.User_ID.ToString().Trim() == tester.spUserId.Trim())/*processing datatable for new user only*/
{
foreach (KeyValuePair <int, int> pair in oldData)
{
int oldval = pair.Value;
int oldKey = pair.Key;
if (row.Tester_ID == oldKey)
{
if (row.Role_ID != oldval)
{
/*if new users testerID + roleId combination is not same as old tester's combination then set new users tester id as '0' to treat it as new record to be inserted*/
/*if roleId + testerId combination is matched then no change in datatable here, data is updated in sp*/
row.Tester_ID = 0;
}
}
}
}
}
//dtTester = dtTester.AsEnumerable().OrderBy(z => z.Field<int>("RowID")).CopyToDataTable(); /*to bring dttester in original sorting as received*/
}
}
}
return(TesterTypeDataTable);
}
public Dictionary <string, string> InsertUpdateTester([FromBody] TestPassTesterRoles tester)
{
Dictionary <string, string> _result = new Dictionary <string, string>();
try
{
string SpUserId = HttpContext.Request.Headers["LoggedInUserSPUserId"];
if (string.IsNullOrEmpty(tester.oldTesterspUserId))
{
tester.oldTesterspUserId = tester.spUserId;
}
if (string.IsNullOrEmpty(tester.spUserId))
{
_result.Add(this._errorText, "spUserId is required");
return(_result);
}
string AppUrl = HttpContext.Request.Headers["appurl"];
string SchemaName = "";
if (!string.IsNullOrEmpty(AppUrl))
{
SchemaName = new clsUatClient(_context).GetClientSchema(AppUrl);
}
else
{
_result.Add(this._errorText, "Invalid Appurl");
return(_result);
}
TesterTypeCollection TesterTypeDataTable = new TesterTypeCollection();
TesterTypeDataTable = generateTesterTable(tester);
using (var cmd = _context.Database.GetDbConnection().CreateCommand())
{
string statementType = string.Empty;
if (tester.action.ToLower() == "add")
{
statementType = "Insert";
}
else
{
statementType = "Update";
}
cmd.CommandText = "UAT.spTester";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TesterTypeCollection", SqlDbType.Structured)
{
Value = TesterTypeDataTable
});
cmd.Parameters.Add(new SqlParameter("@TestPassId", SqlDbType.Int)
{
Value = Convert.ToInt32(tester.testPassId)
});
cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 500)
{
Value = tester.testerName
});
cmd.Parameters.Add(new SqlParameter("@UserAlias", SqlDbType.VarChar, 500)
{
Value = tester.testerAlias
});
cmd.Parameters.Add(new SqlParameter("@UserEmailId", SqlDbType.VarChar, 500)
{
Value = tester.testerEmail
});
cmd.Parameters.Add(new SqlParameter("@SPUserId", SqlDbType.Int)
{
Value = tester.spUserId
});
cmd.Parameters.Add(new SqlParameter("@OldSPUserId", SqlDbType.Int)
{
Value = Convert.ToInt32(tester.oldTesterspUserId)
});
cmd.Parameters.Add(new SqlParameter("@UserCId", SqlDbType.Int)
{
Value = Convert.ToInt32(SpUserId)
});
cmd.Parameters.Add(new SqlParameter("@StatementType", SqlDbType.NVarChar, 500)
{
Value = statementType
});
SqlParameter outparam = new SqlParameter("@Ret_Parameter", SqlDbType.VarChar, 500)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(new SqlParameter("@SchemaName", SqlDbType.NVarChar, 500)
{
Value = SchemaName
});
cmd.Parameters.Add(outparam);
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
int retValPos = cmd.ExecuteNonQuery();
string ReturnParamValue = Convert.ToString(cmd.Parameters["@Ret_Parameter"].Value);
if (!string.IsNullOrEmpty(ReturnParamValue) || ReturnParamValue.ToLower() == "success")
{
if (string.IsNullOrEmpty(tester.testPassId))
{
_result.Add("roleId", cmd.Parameters[cmd.Parameters.Count - 1].Value.ToString());
_result.Add(this._statusText, "Done");
}
else
{
_result.Add(this._statusText, "Done");
}
}
else
{
List <System.Data.Common.DbParameter> _outParameter = new List <System.Data.Common.DbParameter>();
foreach (System.Data.Common.DbParameter outP in _outParameter)
{
if (outP.Direction == ParameterDirection.Output)
{
_outParameter.Add(outP);
}
}
_result.Add(this._errorText, "Error ");
}
}
}
catch (Exception ex)
{
_result.Add(this._errorText, ex.Message);
}
return(_result);
}
