public async Task <IList <RequesterRow> > GetAllRequesterRow(int empId)
{
#region sql conn then sqlcommand
string cnstr = "Server=tcp:team8-sa50.database.windows.net,1433;Initial Catalog=ADProj;Persist Security Info=False;User ID=Bianca;Password=!Str0ngPsword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=600;";
SqlConnection cn = new SqlConnection(cnstr);
cn.Open();
string sqlstr = "select dis.* from DisbursementList_Table dis, Department_Table dept where dis.DepartmentId = dept.Id and dis.date >= GETDATE() and dept.CollectionId in (select col.Id from CollectionInfo_Table col where clerkId =" + empId + " )";
SqlCommand cmd = new SqlCommand(sqlstr, cn);
SqlDataReader dr = cmd.ExecuteReader();
IList <DisbursementList> disbursementlist = new List <DisbursementList>();
while (dr.Read())
{
DisbursementList items = new DisbursementList()
{
id = int.Parse(dr["id"].ToString()),
DepartmentId = int.Parse(dr["DepartmentId"].ToString()),
date = Convert.ToDateTime(dr["date"].ToString()),
deliveryPoint = dr["deliveryPoint"].ToString(),
status = dr["status"].ToString(),
};
disbursementlist.Add(items);
}
dr.Close();
cn.Close();
IList <RequesterRow> resultList = new List <RequesterRow>();
foreach (DisbursementList disburseList in disbursementlist)
{
List <DisbursementDetail> disburseDetailList = unitOfWork
.GetRepository <DisbursementDetail>()
.GetAllIncludeIQueryable(filter: x => x.DisbursementListId == disburseList.id).ToList();
String requisitionStatus = "";
int itemCountTotal = 0;
RequisitionDetail requestionDetail = new RequisitionDetail();
foreach (DisbursementDetail disburseDetail in disburseDetailList)
{
itemCountTotal += disburseDetail.qty;
requestionDetail = unitOfWork
.GetRepository <RequisitionDetail>()
.GetAllIncludeIQueryable(filter: x => x.Id == disburseDetail.RequisitionDetailId).FirstOrDefault();
}
Department dept = unitOfWork
.GetRepository <Department>()
.GetAllIncludeIQueryable(filter: x => x.Id == disburseList.DepartmentId).FirstOrDefault();
if (dept != null && requestionDetail != null)
{
Requisition requisition = unitOfWork
.GetRepository <Requisition>()
.GetAllIncludeIQueryable(filter: x => x.Id == requestionDetail.RequisitionId).FirstOrDefault();
Employee emp = unitOfWork
.GetRepository <Employee>()
.GetAllIncludeIQueryable(filter: x => x.departmentId == dept.Id)
.Where(x => x.role == "REPRESENTATIVE").FirstOrDefault();
//Employee emp = findEmployeeByIdAsync(dept.repId);
if (requisition != null)
{
RequesterRow row = new RequesterRow()
{
date = disburseList.date,
disbursementListId = disburseList.id,
departmentId = disburseList.DepartmentId,
departmentName = dept.deptName,
itemCount = itemCountTotal,
status = disburseList.status,
collectionPoint = disburseList.deliveryPoint,
representativeName = emp.name
};
resultList.Add(row);
}
}
}//end forEach
return(resultList);
}
public async Task <IList <DisburseItemDetails> > getDisburseItemDetail(RequesterRow row)
{
IList <DisburseItemDetails> returnList = new List <DisburseItemDetails>();
string cnstr = "Server=tcp:team8-sa50.database.windows.net,1433;Initial Catalog=ADProj;Persist Security Info=False;User ID=Bianca;Password=!Str0ngPsword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=600;";
SqlConnection cn = new SqlConnection(cnstr);
cn.Open();
string sqlstr = "select col.* from CollectionInfo_Table col, Department_Table dept,DisbursementList_Table dis where col.Id = dept.CollectionId and dept.Id = dis.DepartmentId and dis.Id = " + row.disbursementListId;
SqlCommand cmd = new SqlCommand(sqlstr, cn);
SqlDataReader dr = cmd.ExecuteReader();
IList <CollectionInfo> collectInfo = new List <CollectionInfo>();
while (dr.Read())
{
CollectionInfo items = new CollectionInfo()
{
Id = int.Parse(dr["Id"].ToString()),
collectionTime = Convert.ToDateTime(dr["collectionTime"].ToString()),
collectionPoint = dr["collectionPoint"].ToString(),
lat = dr["lat"].ToString(),
longi = dr["longi"].ToString(),
clerkId = int.Parse(dr["clerkId"].ToString()),
};
collectInfo.Add(items);
}
dr.Close();
cn.Close();
//CollectionInfo collectionInfo = (CollectionInfo) cmd.ExecuteScalar();
// cn.Close();
List <DisbursementDetail> detailList = unitOfWork
.GetRepository <DisbursementDetail>()
.GetAllIncludeIQueryable(filter: x => x.DisbursementListId == row.disbursementListId).ToList();
foreach (DisbursementDetail detail in detailList)
{
RequisitionDetail requisitionDetail = unitOfWork
.GetRepository <RequisitionDetail>()
.GetAllIncludeIQueryable(filter: x => x.Id == detail.RequisitionDetailId).FirstOrDefault();
if (requisitionDetail != null)
{
Stationery stationery = unitOfWork
.GetRepository <Stationery>()
.GetAllIncludeIQueryable(filter: x => x.Id == requisitionDetail.StationeryId).FirstOrDefault();
if (stationery != null)
{
DisburseItemDetails itemObj = new DisburseItemDetails()
{
itemDescription = stationery.desc,
requisitionDetailId = requisitionDetail.Id,
requisitionId = requisitionDetail.RequisitionId,
revQuantity = requisitionDetail.rcvQty,
collectTime = collectInfo[0].collectionTime,
collectionPoint = collectInfo[0].collectionPoint,
};
returnList.Add(itemObj);
}
}
}
return(returnList);
}
public async Task <ActionResult <List <DisburseItemDetails> > > GetDisburseItemDetail([FromBody] RequesterRow row)
{
var result = await _clkService.getDisburseItemDetail(row);
if (result != null)
{
//String str = await _emailService.SendMail("*****@*****.**", "Email Testing", "This is to test email service...");
return(Ok(result));
}
else
{
//this help to return a NOTfOUND result, u can customerize the string.
return(NotFound("Error"));
}
}
