public List <InputClass> SearchTravelRequest(SearchViewModel Search)
{
List <InputClass> ResultListLists = new List <InputClass>();
try
{
BVNDataContext Travelconn = new BVNDataContext();
var Projectinfo = (from distinct in Travelconn.Tb_BvnIncidents
where ((Search.AccountName == null || distinct.AccountNameArray.Contains(Search.AccountName)) &&
(Search.AccountNumber == null || distinct.AccountNumberArray.Contains(Search.AccountNumber)) &&
(Search.StaffName == null || distinct.StaffName.Contains(Search.StaffName)) &&
(Search.Firstname == null || distinct.Firstname.Contains(Search.Firstname)) &&
(Search.LastName == null || distinct.LastName.Contains(Search.LastName)) &&
(Search.MiddleName == null || distinct.MiddleName.Contains(Search.MiddleName)) &&
(Search.BVN == null || distinct.BVN == Search.BVN) &&
(Search.IsAccountClosed == null || distinct.IsAccountClose == Search.IsAccountClosed) &&
(Search.StaffNo == null || distinct.StaffNumber == Search.StaffNo) &&
// (Search.DomicileBranchCode == null || distinct.DomicileBranchCode == Search.DomicileBranchCode) &&
(Search.BranchCode == null || distinct.StaffBranchCode == Search.BranchCode) &&
(Search.Irregularity == null || distinct.Irregularity == Search.Irregularity) &&
(Search.Date_From == null || distinct.DateSubmitted >= Convert.ToDateTime(Search.Date_From) || distinct.DateSubmitted == Convert.ToDateTime(Search.Date_From)) &&
(Search.Date_To == null || distinct.DateSubmitted <= Convert.ToDateTime(Search.Date_To).AddDays(1)))
select
new
{
RequestID = distinct.DocumentID,
BVN = distinct.BVN,
Staffname = distinct.StaffName,
StaffNo = distinct.StaffNumber,
AccountNo = distinct.AccountNumberArray,
AccountName = distinct.AccountNameArray,
AccountStatus = distinct.AccountStatusArray,
DomicileBranch = distinct.DomicileBranch,
InitiatorBranchName = distinct.StaffBranch,
Irregularity = distinct.Irregularity,
OtherDetails = distinct.Nature_Of_The_Irregular,
FirstName = distinct.Firstname,
MiddleName = distinct.MiddleName,
LastName = distinct.LastName,
IsAccountClosed = distinct.IsAccountClose,
DateSubmitted = distinct.DateSubmitted
});
foreach (var result_set in Projectinfo)
{
InputClass input = new InputClass();
Profile profile = new Profile();
Account account = new Account();
input.DocumentID = result_set.RequestID;
input.Irregularity = (result_set.Irregularity == "Others") ? result_set.OtherDetails : result_set.Irregularity;
input.DateSubmitted = result_set.DateSubmitted;
profile.StaffName = result_set.Staffname;
profile.StaffNo = result_set.StaffNo;
profile.Branch = result_set.InitiatorBranchName;
account.AccountName = (result_set.AccountNo == null) ? "" : result_set.AccountNo.Replace(";", "<BR><hr><BR>");
account.AccountNumber = (result_set.AccountName == null) ? "" : result_set.AccountName.Replace(";", "<BR><hr><BR>");
account.AccountStatus = (result_set.AccountStatus == null) ? "" : result_set.AccountStatus.Replace(";", "<BR><hr><BR>");
account.DomicileBranch = result_set.DomicileBranch;
account.Firstname = result_set.FirstName;
account.LastName = result_set.LastName;
account.MiddleName = result_set.MiddleName;
account.IsAccountClosedString = (result_set.IsAccountClosed == 1) ? "Yes" : "No";
account.BVN = result_set.BVN;
input.RequesterDetails = profile;
input.AccountDetials = account;
ResultListLists.Add(input);
}
}
catch (Exception ex)
{
// return ex.Message;
}
return(ResultListLists);
}
public List<InputClass> SearchBranchRequest(string BranchCode)
{
List<InputClass> RequestList = new List<InputClass>();
try
{
BVNDataContext projectconn = new BVNDataContext();
var Projectinfo = (from distinct in projectconn.Tb_BvnIncidents
where (distinct.StaffBranchCode == int.Parse(BranchCode.ToString()))
select
new
{
RequestID = distinct.DocumentID,
InitiatorName = distinct.StaffName,
AccountName = distinct.AccountName,
AccountNumber = distinct.AccountNumber,
InitiatorBranch = distinct.StaffBranch,
RequestStage = distinct.InputStage,
date_Submitted = distinct.DateSubmitted,
accountNameArray = distinct.AccountNameArray,
accountNumberArray = distinct.AccountNumberArray,
accountStatusArrary = distinct.AccountStatusArray,
BVN = distinct.BVN
});
foreach (var requestList in Projectinfo)
{
InputClass Myrequest = new InputClass();
Profile profile = new Profile();
Account account = new Account();
profile.StaffName = requestList.InitiatorName;
profile.Branch = requestList.InitiatorBranch;
account.AccountName = requestList.AccountName;
account.AccountNumber = requestList.AccountNumber;
account.AccountName = (requestList.accountNameArray == null) ? "" : requestList.accountNameArray.Replace(";", "<BR><hr><BR>");
account.AccountNumber = (requestList.accountNumberArray == null) ? "" : requestList.accountNumberArray.Replace(";", "<BR><hr><BR>");
account.AccountStatus = (requestList.accountStatusArrary == null) ? "" : requestList.accountStatusArrary.Replace(";", "<BR><hr><BR>");
account.BVN = requestList.BVN;
Myrequest.RequesterDetails = profile;
Myrequest.AccountDetials= account;
Myrequest.InputStage = "IC & A Review";
Myrequest.DateSubmitted = requestList.date_Submitted;
Myrequest.DocumentID = requestList.RequestID;
RequestList.Add(Myrequest);
}
}
catch (Exception ex)
{
// return ex.Message;
}
return RequestList;
}
public string initiateIncident(InputClass newRequest, string ConnString)
{
string retVal = "";
string connString = getConnectionString(ConnString);
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmnd = new SqlCommand();
string AccountNameArr = (String.Join(";", new PheonixQuery().getAccounts(newRequest.AccountDetials.BVN).Select(z => staticClass.ConCatAccountName(z))));
string AccountNumberArr = (String.Join(";", new PheonixQuery().getAccounts(newRequest.AccountDetials.BVN).Select(z => staticClass.ConCatAccountNumber(z))));
string AccountStatusArr = (String.Join(";", new PheonixQuery().getAccounts(newRequest.AccountDetials.BVN).Select(z => staticClass.ConCatAccountStatus(z))));
newRequest.AccountDetials.AccountNameArray = AccountNameArr;
newRequest.AccountDetials.AccountNumberArray = AccountNumberArr;
newRequest.AccountDetials.AccountStatusArray = AccountStatusArr;
try
{
cmnd.Connection = conn;
cmnd.CommandType = CommandType.StoredProcedure;
cmnd.CommandText = "zb_BVN_PROC";
cmnd.Parameters.Add("@appid", SqlDbType.VarChar).Value = "BVN_IN_WKF";
cmnd.Parameters.Add("@DocumentID", SqlDbType.VarChar).Value = newRequest.DocumentID.Trim();
cmnd.Parameters.Add("@Status", SqlDbType.VarChar).Value = "Submitted";
cmnd.Parameters.Add("@Action", SqlDbType.VarChar).Value = "Submitted";
cmnd.Parameters.Add("@StaffName", SqlDbType.VarChar).Value = newRequest.RequesterDetails.StaffName;
cmnd.Parameters.Add("@StaffNumber", SqlDbType.VarChar).Value = newRequest.RequesterDetails.StaffNo;
cmnd.Parameters.Add("@StaffBranch", SqlDbType.VarChar).Value = newRequest.RequesterDetails.Branch;
cmnd.Parameters.Add("@StaffBranchCode", SqlDbType.Int).Value = newRequest.RequesterDetails.BranchCode;
cmnd.Parameters.Add("@StaffDept", SqlDbType.VarChar).Value = newRequest.RequesterDetails.Dept;
cmnd.Parameters.Add("@StaffDeptCode", SqlDbType.VarChar).Value = newRequest.RequesterDetails.DeptID;
cmnd.Parameters.Add("@StaffUnit", SqlDbType.VarChar).Value = newRequest.RequesterDetails.unitname;
cmnd.Parameters.Add("@StaffUnitcode", SqlDbType.VarChar).Value = newRequest.RequesterDetails.unitcode;
cmnd.Parameters.Add("@StaffGrade", SqlDbType.VarChar).Value = newRequest.RequesterDetails.level;
cmnd.Parameters.Add("@StaffTitle", SqlDbType.VarChar).Value = newRequest.RequesterDetails.JobTitle;
cmnd.Parameters.Add("@Email", SqlDbType.VarChar).Value = newRequest.RequesterDetails.Email;
cmnd.Parameters.Add("@StaffCategory", SqlDbType.VarChar).Value = newRequest.RequesterDetails.Job_Category;
cmnd.Parameters.Add("@BVN", SqlDbType.VarChar).Value = newRequest.AccountDetials.BVN;
cmnd.Parameters.Add("@AccountName", SqlDbType.VarChar).Value = newRequest.AccountDetials.AccountName;
cmnd.Parameters.Add("@AccountNumber", SqlDbType.VarChar).Value = newRequest.AccountDetials.AccountNumber;
cmnd.Parameters.Add("@CustomerNames", SqlDbType.VarChar).Value = newRequest.AccountDetials.customerNames;
cmnd.Parameters.Add("@dateOfBirth", SqlDbType.VarChar).Value = newRequest.AccountDetials.dateOfBirth;
cmnd.Parameters.Add("@AccountNameArray", SqlDbType.VarChar).Value = newRequest.AccountDetials.AccountNameArray;
cmnd.Parameters.Add("@AccountNumberArray", SqlDbType.VarChar).Value = newRequest.AccountDetials.AccountNumberArray;
cmnd.Parameters.Add("@AccountStatusArray", SqlDbType.VarChar).Value = newRequest.AccountDetials.AccountStatusArray;
cmnd.Parameters.Add("@Firstname", SqlDbType.VarChar).Value = newRequest.AccountDetials.Firstname;
cmnd.Parameters.Add("@MiddleName", SqlDbType.VarChar).Value = newRequest.AccountDetials.MiddleName;
cmnd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = newRequest.AccountDetials.LastName;
cmnd.Parameters.Add("@InputStage", SqlDbType.VarChar).Value = "Initiate";
cmnd.Parameters.Add("@InputStageId", SqlDbType.Int).Value = 0;
cmnd.Parameters.Add("@Irregularity", SqlDbType.VarChar).Value = newRequest.Irregularity;
cmnd.Parameters.Add("@Nature_Of_The_Irregular", SqlDbType.VarChar).Value = newRequest.IrregularityDetails.ToString().Trim();
cmnd.Parameters.Add("@Comment", SqlDbType.VarChar).Value = newRequest.Comment;
cmnd.Parameters.Add("@DomicileBranch", SqlDbType.VarChar).Value = "";
cmnd.Parameters.Add("@DomicileBranchCode", SqlDbType.VarChar).Value = "";
//cmnd.Parameters.Add("@IsAccountClose", SqlDbType.Int).Value = (newRequest.AccountDetials.IsAccountClosed==true)?1:0;
cmnd.Parameters.Add("@IsAccountClose", SqlDbType.Int).Value = newRequest.AccountDetials.IsAccountClosed;
cmnd.Parameters.Add("@DateSubmitted", SqlDbType.DateTime).Value = DateTime.Now;
cmnd.Parameters.Add("@ErrorCode", SqlDbType.Int, 6).Direction = ParameterDirection.Output;
cmnd.Parameters.Add("@ErrorText", SqlDbType.VarChar, 250).Direction = ParameterDirection.Output;
SqlDataReader dr;
// Open the data connection
cmnd.Connection = conn;
conn.Open();
// Execute the command
dr = cmnd.ExecuteReader();
string retCode = cmnd.Parameters["@ErrorCode"].Value.ToString();
retVal = retCode + "|" + cmnd.Parameters["@ErrorText"].Value.ToString();
}
catch (SqlException ex)
{
if (ex.Number != 0)
{
retVal = ex.Number + "|" + ex.Message;
}
}
finally
{
if (conn != null)
{
((IDisposable)conn).Dispose();
}
}
return(retVal);
}
public List<InputClass> GetEntry(string DocumentID)
{
List<InputClass> RequestList = new List<InputClass>();
try
{
BVNDataContext projectconn = new BVNDataContext();
var Projectinfo = (from distinct in projectconn.Tb_BvnIncidents
where (distinct.DocumentID == DocumentID)
select
new
{
RequestID = distinct.DocumentID,
Irregularity = distinct.Irregularity,
IrregularityDetails = distinct.Nature_Of_The_Irregular,
DateSubmitted = distinct.DateSubmitted,
Staffname = distinct.StaffName,
StaffNo = distinct.StaffNumber,
InitiatorBranchName = distinct.StaffBranch,
JobTitle = distinct.StaffTitle,
Email = distinct.Email,
AccountNo = distinct.AccountNumber,
AccountName = distinct.AccountName,
CustomerName = distinct.CustomerNames,
Firstname = distinct.Firstname,
LastName = distinct.LastName,
MiddleName = distinct.MiddleName,
DOB = distinct.dateOfBirth,
AccountnameArray = distinct.AccountNameArray,
AccountNumberArray = distinct.AccountNumberArray,
AccountStatusArray = distinct.AccountStatusArray,
Comment = distinct.Comment,
DomicileBranch = distinct.DomicileBranch,
IsAccountClosed = distinct.IsAccountClose,
BVN = distinct.BVN
});
foreach (var requestList in Projectinfo)
{
InputClass Myrequest = new InputClass();
Profile profile = new Profile();
Account account = new Account();
profile.StaffName = requestList.Staffname;
profile.Branch = requestList.InitiatorBranchName;
profile.JobTitle = requestList.JobTitle;
profile.Email = requestList.Email;
profile.StaffNo = requestList.StaffNo;
account.AccountName = requestList.AccountName;
account.AccountNumber = requestList.AccountNo;
account.customerNames = requestList.CustomerName;
account.BVN = requestList.BVN;
account.DomicileBranch = requestList.DomicileBranch;
account.Firstname = requestList.Firstname;
account.MiddleName = requestList.MiddleName;
account.LastName = requestList.LastName;
account.dateOfBirth = requestList.DOB;
//account.IsAccountClosed = (requestList.IsAccountClosed == 1) ? true : false;
account.IsAccountClosed = requestList.IsAccountClosed;
Myrequest.RequesterDetails = profile;
Myrequest.AccountDetials = account;
Myrequest.DateSubmitted = requestList.DateSubmitted;
Myrequest.Irregularity = requestList.Irregularity;
Myrequest.IrregularityDetails = requestList.IrregularityDetails;
Myrequest.DocumentID = requestList.RequestID;
Myrequest.Comment = requestList.Comment;
if (requestList.AccountnameArray != null)
{
List<Account> AccountNamelist = new List<Account>();
string[] availabledcard = requestList.AccountnameArray.Split(';');
string[] availabledAccNumber = requestList.AccountNumberArray.Split(';');
string[] availabledStatusNumber = requestList.AccountStatusArray.Split(';');
for (int i = 0; i < availabledcard.Count(); i++)
{
Account card = new Account();
card.AccountName = availabledcard[i];
card.AccountNumber = availabledAccNumber[i];
card.AccountStatus = availabledStatusNumber[i];
AccountNamelist.Add(card);
}
Myrequest.accountNamelist = AccountNamelist;
}
RequestList.Add(Myrequest);
}
}
catch (Exception ex)
{
// return ex.Message;
}
return RequestList;
}