public ActionResult <IList <RequisitionTrend> > GenerateReOrderGrouped()
{
#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 category,datediff(mm,ord.dateOfOrder,getdate()) as monthBefore,SUM(qty) as totalQty " +
"FROM (SELECT StationeryId, qty, sta.category, PurchaseOrderId FROM[dbo].[Stationery_Table] as sta " +
"JOIN[dbo].[PurchaseOrderDetail_Table] as ordDet ON ordDet.StationeryId = sta.Id) as ordCat " +
"JOIN[dbo].[PurchaseOrder_Table] as ord ON ordCat.PurchaseOrderId = ord.Id " +
"Where datediff(mm, ord.dateOfOrder, getdate())<= 3" +
"GROUP BY category,datediff(mm, ord.dateOfOrder, getdate())";
SqlCommand cmd = new SqlCommand(sqlstr, cn);
SqlDataReader dr = cmd.ExecuteReader();
IList <RequisitionTrend> result = new List <RequisitionTrend>();
while (dr.Read())
{
RequisitionTrend reqT = new RequisitionTrend()
{
Category = dr["category"].ToString(),
DateOfAuthorizing = dr["monthBefore"].ToString(),
ReqQty = int.Parse(dr["totalQty"].ToString())
};
result.Add(reqT);
}
dr.Close();
cn.Close();
#endregion
foreach (RequisitionTrend rt in result)
{
Console.WriteLine(rt.ToString());//output testing
}
if (result != null)
{
return(Ok(result));
}
else
{
return(NotFound("QUERY FAILED."));
}
}
public ActionResult <IList <RequisitionTrend> > GenerateReqGroupedByDay()
{
#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 detCat.category,ed.deptName,min(req.dateOfAuthorizing) as reqDate,SUM(detCat.reqQty) AS totalQty "
+ "FROM(SELECT reqDet.StationeryId, reqDet.reqQty, sta.category, reqDet.RequisitionId FROM[dbo].[Stationery_Table] AS sta "
+ "JOIN [dbo].[RequisitionDetail_Table] AS reqDet ON reqDet.StationeryId = sta.Id) AS detCat "
+ "JOIN[dbo].[Requisition_Table] AS req ON detCat.RequisitionId = req.Id " +
"JOIN(SELECT dept.Id, dept.deptName, emp.Id AS empId FROM[dbo].[Department_Table] AS dept JOIN[dbo].[Employee_Table] AS emp ON dept.Id = emp.departmentId) AS ed " +
"ON req.EmployeeId = ed.empId Where req.[status] NOT IN('Applied','Declined') " +
"GROUP BY category,deptName, datediff(dd, req.dateOfAuthorizing, getdate())";
SqlCommand cmd = new SqlCommand(sqlstr, cn);
SqlDataReader dr = cmd.ExecuteReader();
IList <RequisitionTrend> result = new List <RequisitionTrend>();
while (dr.Read())
{
RequisitionTrend reqT = new RequisitionTrend()
{
Category = dr["category"].ToString(),
DepartmentName = dr["deptName"].ToString(),
DateOfAuthorizing = dr["reqDate"].ToString(),
ReqQty = int.Parse(dr["totalQty"].ToString())
};
result.Add(reqT);
}
dr.Close();
cn.Close();
#endregion
if (result != null)
{
return(Ok(result));
}
else
{
return(NotFound("QUERY FAILED."));
}
}
