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")); } }