[WebMethod(Description = "Method to obtain Assigned Products - produse listate - to Clients by Zone or by Name ; default = BELEIU, CENTRAL, HYPERMARCHE")]
public AssignedProduct[] ReturnAssignedProducts(string theZone, string theAgent, string theClient)
{
SqlConnection dbConn = new SqlConnection(connStr);
dbConn.Open();
string sqlSelect = " ";
// string sqlSelect1 = " ";
if (theZone.Equals("") && theAgent.Equals("") && theClient.Equals(""))
{
sqlSelect =
" SELECT TOP (100) PERCENT MAX(dbo.PersoanaFizica.Nume) AS AgentName, MAX(dbo.PersoanaFizica.Prenume) AS AgentSurname, MAX(dbo.Zona.Denumire) AS AgentZone, "
+ " MAX(dbo.Persoana.Denumire) AS ClientName , MAX(dbo.DocumentDetalii.ProdusID) AS ProductID, dbo.Produs.Denumire as ProductName, MAX(dbo.Document.Control) as LastOrder"
+ " FROM dbo.[Document] INNER JOIN "
+ " dbo.PersoanaFizica ON dbo.[Document].Utilizator_ID = dbo.PersoanaFizica.PersoanaFizica_ID INNER JOIN"
+ " dbo.Persoana ON dbo.[Document].Persoana_ID = dbo.Persoana.PersoanaID INNER JOIN "
+ " dbo.Comanda ON dbo.[Document].DocumentID = dbo.Comanda.Comanda_ID INNER JOIN "
+ " dbo.Utilizator ON dbo.[Document].Utilizator_ID = dbo.Utilizator.UtilizatorID INNER JOIN "
+ " dbo.Zona ON dbo.Utilizator.ZonaID = dbo.Zona.Id INNER JOIN "
+ " dbo.DocumentDetalii ON dbo.[Document].DocumentID = dbo.DocumentDetalii.DocumentID INNER JOIN "
+ " dbo.Produs ON dbo.DocumentDetalii.ProdusID = dbo.Produs.ProdusID "
+ " WHERE (dbo.Zona.Denumire LIKE '%CENTRAL%') AND (dbo.PersoanaFizica.Nume LIKE '%BELEIU%') AND (dbo.Persoana.Denumire LIKE '%HYPERMARCHE%') "
+ " GROUP BY dbo.Produs.Denumire "
+ " ORDER BY dbo.Produs.Denumire "
;
}
else if ( theZone.Equals("") && theAgent.Equals("") && !theClient.Equals(""))
{
sqlSelect =
" SELECT TOP (100) PERCENT MAX(dbo.PersoanaFizica.Nume) AS AgentName, MAX(dbo.PersoanaFizica.Prenume) AS AgentSurname, MAX(dbo.Zona.Denumire) AS AgentZone, "
+ " MAX(dbo.Persoana.Denumire) AS ClientName , MAX(dbo.DocumentDetalii.ProdusID) AS ProductID, dbo.Produs.Denumire as ProductName, MAX(dbo.Document.Control) as LastOrder"
+ " FROM dbo.[Document] INNER JOIN "
+ " dbo.PersoanaFizica ON dbo.[Document].Utilizator_ID = dbo.PersoanaFizica.PersoanaFizica_ID INNER JOIN"
+ " dbo.Persoana ON dbo.[Document].Persoana_ID = dbo.Persoana.PersoanaID INNER JOIN "
+ " dbo.Comanda ON dbo.[Document].DocumentID = dbo.Comanda.Comanda_ID INNER JOIN "
+ " dbo.Utilizator ON dbo.[Document].Utilizator_ID = dbo.Utilizator.UtilizatorID INNER JOIN "
+ " dbo.Zona ON dbo.Utilizator.ZonaID = dbo.Zona.Id INNER JOIN "
+ " dbo.DocumentDetalii ON dbo.[Document].DocumentID = dbo.DocumentDetalii.DocumentID INNER JOIN "
+ " dbo.Produs ON dbo.DocumentDetalii.ProdusID = dbo.Produs.ProdusID "
+ " WHERE (dbo.Zona.Denumire LIKE '%CENTRAL%') AND (dbo.PersoanaFizica.Nume LIKE '%BELEIU%') AND (dbo.Persoana.Denumire LIKE '%" + theClient + "%') "
+ " GROUP BY dbo.Produs.Denumire "
+ " ORDER BY dbo.Produs.Denumire "
;
}
else
{
}
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, dbConn);
DataTable dt = new DataTable();
SqlCommand dbCommand = new SqlCommand(sqlSelect, dbConn);
da.Fill(dt);
dbConn.Close();
List
list = new List();
foreach (DataRow row in dt.Rows)
{
AssignedProduct target = new AssignedProduct();
target.AgentName = row["AgentName"].ToString();
target.AgentSurname = row["AgentSurname"].ToString();
target.AgentZone = row["AgentZone"].ToString();
target.ClientName = row["ClientName"].ToString();
target.ProductName = row["ProductName"].ToString();
target.ProductID = row["ProductID"].ToString();
target.LastOrder = row["LastOrder"].ToString();
list.Add(target);
}
return list.ToArray();
}
