internal Boolean ExistsDealerBusiness(string obj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Codice";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = obj;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Esiste";
dbp[1].DbType = DbType.Boolean;
dbp[1].Direction = ParameterDirection.Output;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_DealerBusiness_Exists";
sco.SPParams = dbp;
DataTable dt;
List <ExpandoObject> output;
string proc = sqlop.ExecuteProcedure(sco, out dt, out output);
IEnumerable <dynamic> idDealerBusiness = output.Cast <dynamic>().Where(x => x.Nome.Contains("@Esiste"));
return(idDealerBusiness.First().Value);
}
internal string DeleteUtente(int idObj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDUtente";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idObj;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Utenti_Delete";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
{
return(proc);
}
return("");
}
internal DataTable AutorizzazioneCampi_Get(int myIDProfilo, int myIDDealer)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDProfilo";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = myIDProfilo;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "IDDealer";
dbp[1].DbType = DbType.Int32;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = myIDDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_AutorizzazioneCampi_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal string GestioneRicerca(string strRicerca, string user)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Ricerca";
dbp[0].DbType = DbType.Xml;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = strRicerca;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Utente";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = user;
sco.SPParams = dbp;
sco.SPName = "[Utility].[SP_ArchivioRicerca_Insert]";
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return proc;
}
internal DataTable GetStorico(int idObj, string tipologia)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDDealer";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idObj;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Tipologia";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = tipologia;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_Storico_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal DataTable RicercaDealer(string strRicerca, string user)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "vXML";
dbp[0].DbType = DbType.Xml;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = strRicerca;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Utente";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = user;
sco.SPParams = dbp;
sco.SPName = "[Utility].[SP_RicercaDealer_Get]";
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal string GestioneRicerca(string user)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Utente";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = user;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Ricerca";
dbp[1].DbType = DbType.Xml;
dbp[1].Direction = ParameterDirection.Output;
sco.SPParams = dbp;
sco.SPName = "[Utility].[SP_ArchivioRicerca_Get]";
DataTable dt;
List <ExpandoObject> outputList = new List <ExpandoObject>();
string proc = sqlop.ExecuteProcedure(sco, out dt, out outputList);
IEnumerable <dynamic> Ricerca = outputList.Cast <dynamic>().Where(x => x.Nome.Contains("@Ricerca"));
return(Convert.ToString(Ricerca.First().Value));
}
internal string DeleteProfilo(int idObj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDProfilo";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idObj;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Profilo_Delete";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
return proc;
return "";
}
internal DataTable getFromActiveDirectory(string username)
{
try
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "UserID";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = username;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Utenti_ADData_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
internal DataTable GetLocalita(string pre, string prov)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Localita";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = pre;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Provincia";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = prov;
sco.SPName = "[Dealer].[SP_RicercaLocalita_Get]";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal DataTable Dealer_CaricaValoriMultipliSelected(int idDealer, string nomeCampo)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDDealer";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idDealer;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "NomeCampo";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = nomeCampo;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_CaricaValoriMultipliSelected";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
internal DataTable getFromActiveDirectory(string username)
{
try
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "UserID";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = username;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Utenti_ADData_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}
internal DataTable GetDettaglioRelazione(int idRelazione, int idTipoDealer)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDRelazione";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idRelazione;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "IDTipoDealer";
dbp[1].DbType = DbType.Int32;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = idTipoDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_RelazioniDealer_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal DataTable AutorizzazioneCampi_Get(int myIDProfilo, int myIDDealer)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDProfilo";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = myIDProfilo;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "IDDealer";
dbp[1].DbType = DbType.Int32;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = myIDDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_AutorizzazioneCampi_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
internal DataTable GetAllTipoDealer(string utente = null)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "utente";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = utente;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_TipoDealer_GetAll";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (dt.Rows.Count == 0)
{
dt.Columns.Add("IDTipoDealer", typeof(int));
dt.Columns.Add("NomeTipoDealer", typeof(string));
}
return(dt);
}
internal DataTable GetAllDealerRelazioni(string codiceDealer, string ragioneSocialeDealer,int idTipoDealer)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[3];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "CodiceDealer";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = codiceDealer;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "RagioneSocialeDealer";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = ragioneSocialeDealer;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "IdTipoDealer";
dbp[2].DbType = DbType.Int32;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = idTipoDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_RelazioniDealer_GetDealer";
sco.SPParams = dbp;
DataTable dt;
sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
internal DataTable Dealer_CaricaValoriMultipliSelected(int idDealer, string nomeCampo)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDDealer";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idDealer;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "NomeCampo";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = nomeCampo;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_CaricaValoriMultipliSelected";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal string DeleteRelazione(int idRelazione, int idTipoDealer)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDRelazione";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idRelazione;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "IDTipoDealer";
dbp[1].DbType = DbType.Int32;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = idTipoDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_RelazioniDealer_Delete";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
return proc;
return "";
}
internal string InsertDealerImport(string idObj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
//dbp[0] = new SqlParameter();
//dbp[0].ParameterName = "IDDealer";
//dbp[0].DbType = DbType.Int32;
//dbp[0].Direction = ParameterDirection.Input;
//dbp[0].Value = idObj;
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Codice";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idObj;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Import.SP_EccezioniImport_Insert";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
{
return(proc);
}
return("");
}
internal string GestioneRicerca(string user)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Utente";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = user;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Ricerca";
dbp[1].DbType = DbType.Xml;
dbp[1].Direction = ParameterDirection.Output;
sco.SPParams = dbp;
sco.SPName = "[Utility].[SP_ArchivioRicerca_Get]";
DataTable dt;
List<ExpandoObject> outputList = new List<ExpandoObject>();
string proc = sqlop.ExecuteProcedure(sco, out dt, out outputList);
IEnumerable<dynamic> Ricerca = outputList.Cast<dynamic>().Where(x => x.Nome.Contains("@Ricerca"));
return Convert.ToString(Ricerca.First().Value);
}
internal string UpdateUtente(Utente obj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[6];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDUtente";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = obj.IDUtente;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Nome";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = obj.Nome;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "Cognome";
dbp[2].DbType = DbType.String;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = obj.Cognome;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "Email";
dbp[3].DbType = DbType.String;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = obj.Email;
dbp[4] = new SqlParameter();
dbp[4].ParameterName = "UserID";
dbp[4].DbType = DbType.String;
dbp[4].Direction = ParameterDirection.Input;
dbp[4].Value = obj.UserID;
dbp[5] = new SqlParameter();
dbp[5].ParameterName = "IDProfilo";
dbp[5].DbType = DbType.Int32;
dbp[5].Direction = ParameterDirection.Input;
dbp[5].Value = obj.IDProfilo;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Utenti_Update";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
{
return(proc);
}
return("");
}
internal DataTable GetAllProfili()
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Profilo_GetAll";
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
internal string UpdateDealer(int IDUtente, DealerUpdate myDealerUpdate)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDUtente";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = IDUtente;
XDocument DealerUpdateXml = new XDocument(
new XElement("DealerUpdate",
new XElement("Dealer",
new XAttribute("IDDealer", myDealerUpdate.IDDealer),
new XElement("Variazioni"))));
foreach (DealerUpdateFields myDealerUpdateFields in myDealerUpdate.DealerUpdateFieldsList)
{
if (myDealerUpdateFields.IsDatoModificato)
{
XElement newElement = DealerUpdateXml.Element("DealerUpdate").Element("Dealer").Element("Variazioni");
newElement.Add(new XElement("Variazione",
new XAttribute("NomeCampo", myDealerUpdateFields.NomeCampo),
new XAttribute("DatoVecchio", myDealerUpdateFields.DatoVecchio),
new XAttribute("DatoNuovo", myDealerUpdateFields.DatoNuovo)));
}
}
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "DealerUpdateXml";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = DealerUpdateXml.ToString();
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_Dealer_Update";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
{
return(proc);
}
return("Scheda Dealer aggiornata con successo");
}
internal DataTable GetAllUtenti()
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Utenti_GetAll";
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal string InserRelazioneDealer(Relazioni rel)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[4];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "NomeRelazione";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = rel.NomeRelazione;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "IDTipoDealer";
dbp[1].DbType = DbType.Int32;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = rel.IDTipoDealer;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "IDDealer1";
dbp[2].DbType = DbType.Int32;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = rel.IDDealerConsumer;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "IDDealer2";
dbp[3].DbType = DbType.Int32;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = rel.IDDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_RelazioniDealer_Insert";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
{
return(proc);
}
return("");
}
internal List <AutorizzazioneCampi> AutorizzazioneCampi_GetList(int myIDProfilo, int myIDDealer)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDProfilo";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = myIDProfilo;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "IDDealer";
dbp[1].DbType = DbType.Int32;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = myIDDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_AutorizzazioneCampi_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
List <AutorizzazioneCampi> myAutorizzazioneCampiList = new List <AutorizzazioneCampi>();
foreach (DataRow myDataRow in dt.Rows)
{
AutorizzazioneCampi myAutorizzazioneCampi = new AutorizzazioneCampi();
myAutorizzazioneCampi.IDAutorizzazioneCampi = Convert.ToInt32(myDataRow["IDAutorizzazioneCampi"]);
myAutorizzazioneCampi.Autorizzazione = Convert.ToInt32(myDataRow["Autorizzazione"]);
myAutorizzazioneCampi.IDCampoDati = Convert.ToInt32(myDataRow["IDCampoDati"]);
myAutorizzazioneCampi.NomeCampo = Convert.ToString(myDataRow["NomeCampo"]);
myAutorizzazioneCampi.NomeCampoFrontEnd = Convert.ToString(myDataRow["NomeCampoFrontEnd"]);
myAutorizzazioneCampi.NomeTabella = Convert.ToString(myDataRow["NomeTabella"]);
myAutorizzazioneCampi.TipoWebControl = Convert.ToString(myDataRow["TipoWebControl"]);
myAutorizzazioneCampi.TipoDato = Convert.ToString(myDataRow["TipoDato"]);
myAutorizzazioneCampi.LunghezzaMassima = Convert.ToInt32(myDataRow["LunghezzaMassima"]);
myAutorizzazioneCampiList.Add(myAutorizzazioneCampi);
}
return(myAutorizzazioneCampiList);
}
internal string UpdateDealerBusiness(DealerBusiness obj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[4];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDDealerBusiness";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = obj.IDDealerBusiness;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Codice";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = obj.Codice;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "Nome";
dbp[2].DbType = DbType.String;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = obj.Nome;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "Indirizzo";
dbp[3].DbType = DbType.String;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = obj.Indirizzo;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_DealerBusiness_Update";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
{
return(proc);
}
return("");
}
internal List<AutorizzazioneCampi> AutorizzazioneCampi_GetList(int myIDProfilo, int myIDDealer)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDProfilo";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = myIDProfilo;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "IDDealer";
dbp[1].DbType = DbType.Int32;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = myIDDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_AutorizzazioneCampi_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
List<AutorizzazioneCampi> myAutorizzazioneCampiList = new List<AutorizzazioneCampi>();
foreach (DataRow myDataRow in dt.Rows)
{
AutorizzazioneCampi myAutorizzazioneCampi = new AutorizzazioneCampi();
myAutorizzazioneCampi.IDAutorizzazioneCampi = Convert.ToInt32(myDataRow["IDAutorizzazioneCampi"]);
myAutorizzazioneCampi.Autorizzazione = Convert.ToInt32(myDataRow["Autorizzazione"]);
myAutorizzazioneCampi.IDCampoDati = Convert.ToInt32(myDataRow["IDCampoDati"]);
myAutorizzazioneCampi.NomeCampo = Convert.ToString(myDataRow["NomeCampo"]);
myAutorizzazioneCampi.NomeCampoFrontEnd = Convert.ToString(myDataRow["NomeCampoFrontEnd"]);
myAutorizzazioneCampi.NomeTabella = Convert.ToString(myDataRow["NomeTabella"]);
myAutorizzazioneCampi.TipoWebControl = Convert.ToString(myDataRow["TipoWebControl"]);
myAutorizzazioneCampi.TipoDato = Convert.ToString(myDataRow["TipoDato"]);
myAutorizzazioneCampi.LunghezzaMassima = Convert.ToInt32(myDataRow["LunghezzaMassima"]);
myAutorizzazioneCampiList.Add(myAutorizzazioneCampi);
}
return myAutorizzazioneCampiList;
}
internal DataTable SearchRelazioni(int idTipoDealer, string codice, string ragioneSociale, string relazione)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[4];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDTipoDealer";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idTipoDealer;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "CodiceDealer";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = codice;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "RagioneSociale";
dbp[2].DbType = DbType.String;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = ragioneSociale;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "Relazione";
dbp[3].DbType = DbType.String;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = relazione;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_RelazioniDealer_Search";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal DataTable GetAllDealersCode(string pre)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "DealerCode";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = pre;
sco.SPParams = dbp;
sco.SPName = "[Dealer].[SP_RicercaDealerCode_Get]";
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
internal DataTable GetAllDealerImport()
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_DealerImport_GetAll";
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (dt.Rows.Count == 0)
{
//dt.Columns.Add("IDDealer", typeof(int));
dt.Columns.Add("Codice", typeof(string));
}
return(dt);
}
internal DataTable GetAllExport(int fileType)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "FileType";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = fileType;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Export.ExportedFiles_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
internal DataTable GetAllExport(int fileType)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "FileType";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = fileType;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Export.ExportedFiles_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal DataTable GetUtente(string username)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Username";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = username;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_UtentiFromUsername_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal DataTable GetDealerBusiness(int idDealerBusiness)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDDealerBusiness";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idDealerBusiness;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_DealerBusiness_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal string InsertProfilo(Profilo myProfilo, List <TipoDealer> myTipoDealerList, List <AutorizzazioneCampi> myAutorizzazioneCampiList)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[11];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "NomeProfilo";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = myProfilo.NomeProfilo;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "ArchivioDatabase";
dbp[1].DbType = DbType.Boolean;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = myProfilo.ArchivioDatabase;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "ArchivioCruscotto";
dbp[2].DbType = DbType.Boolean;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = myProfilo.ArchivioCruscotto;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "ReportAnomalie";
dbp[3].DbType = DbType.Boolean;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = myProfilo.ReportAnomalie;
dbp[4] = new SqlParameter();
dbp[4].ParameterName = "ReportScarti";
dbp[4].DbType = DbType.Boolean;
dbp[4].Direction = ParameterDirection.Input;
dbp[4].Value = myProfilo.ReportScarti;
dbp[5] = new SqlParameter();
dbp[5].ParameterName = "Portafoglio";
dbp[5].DbType = DbType.Boolean;
dbp[5].Direction = ParameterDirection.Input;
dbp[5].Value = myProfilo.Portafoglio;
dbp[6] = new SqlParameter();
dbp[6].ParameterName = "Amministrazione";
dbp[6].DbType = DbType.Boolean;
dbp[6].Direction = ParameterDirection.Input;
dbp[6].Value = myProfilo.Amministrazione;
dbp[7] = new SqlParameter();
dbp[7].ParameterName = "RagioniSocialiCorrelate";
dbp[7].DbType = DbType.Int32;
dbp[7].Direction = ParameterDirection.Input;
dbp[7].Value = myProfilo.RagioniSocialiCorrelate;
dbp[8] = new SqlParameter();
dbp[8].ParameterName = "Eccezioni";
dbp[8].DbType = DbType.Boolean;
dbp[8].Direction = ParameterDirection.Input;
dbp[8].Value = myProfilo.Eccezioni;
XElement tipoDealerXml = new XElement("Dealer",
from TipoDealer in myTipoDealerList
select new XElement("TipoDealer",
new XElement("IDTipoDealer", TipoDealer.IDTipoDealer)
)
);
dbp[9] = new SqlParameter();
dbp[9].ParameterName = "TipoDealerXml";
dbp[9].DbType = DbType.String;
dbp[9].Direction = ParameterDirection.Input;
dbp[9].Value = tipoDealerXml.ToString();
XElement autorizzazioneCampiXml = new XElement("Autorizzazioni",
from AutorizzazioneCampi in myAutorizzazioneCampiList
select new XElement("AutorizzazioneCampi",
new XElement("IDAutorizzazioneCampi", AutorizzazioneCampi.IDAutorizzazioneCampi),
new XElement("Autorizzazione", AutorizzazioneCampi.Autorizzazione),
new XElement("IDCampoDati", AutorizzazioneCampi.IDCampoDati)
)
);
dbp[10] = new SqlParameter();
dbp[10].ParameterName = "AutorizzazioneCampiXml";
dbp[10].DbType = DbType.String;
dbp[10].Direction = ParameterDirection.Input;
dbp[10].Value = autorizzazioneCampiXml.ToString();
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Profilo_Insert";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
{
return(proc);
}
return("");
}
internal string UpdateUtente(Utente obj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[6];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDUtente";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = obj.IDUtente;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Nome";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = obj.Nome;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "Cognome";
dbp[2].DbType = DbType.String;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = obj.Cognome;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "Email";
dbp[3].DbType = DbType.String;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = obj.Email;
dbp[4] = new SqlParameter();
dbp[4].ParameterName = "UserID";
dbp[4].DbType = DbType.String;
dbp[4].Direction = ParameterDirection.Input;
dbp[4].Value = obj.UserID;
dbp[5] = new SqlParameter();
dbp[5].ParameterName = "IDProfilo";
dbp[5].DbType = DbType.Int32;
dbp[5].Direction = ParameterDirection.Input;
dbp[5].Value = obj.IDProfilo;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Utenti_Update";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
return proc;
return "";
}
internal DataTable GetAllTipoDealer(string utente = null)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "utente";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = utente;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_TipoDealer_GetAll";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (dt.Rows.Count == 0)
{
dt.Columns.Add("IDTipoDealer", typeof(int));
dt.Columns.Add("NomeTipoDealer", typeof(string));
}
return dt;
}
public OperationResult ExecuteNonQueryByTransaction(string serviceName, string connectionName, SqlCommandObject[] sqlScripts)
{
try
{
DataPortal portal = new DataPortal();
return portal.ExecuteNonQueryByTransaction(serviceName, connectionName, sqlScripts);
}
catch (Exception ex)
{
return new OperationResult() { Message = ex.Message };
}
}
internal string UpdateDealer(int IDUtente, DealerUpdate myDealerUpdate)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDUtente";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = IDUtente;
XDocument DealerUpdateXml = new XDocument(
new XElement("DealerUpdate",
new XElement("Dealer",
new XAttribute("IDDealer", myDealerUpdate.IDDealer),
new XElement("Variazioni"))));
foreach (DealerUpdateFields myDealerUpdateFields in myDealerUpdate.DealerUpdateFieldsList)
{
if (myDealerUpdateFields.IsDatoModificato)
{
XElement newElement = DealerUpdateXml.Element("DealerUpdate").Element("Dealer").Element("Variazioni");
newElement.Add(new XElement("Variazione",
new XAttribute("NomeCampo", myDealerUpdateFields.NomeCampo),
new XAttribute("DatoVecchio", myDealerUpdateFields.DatoVecchio),
new XAttribute("DatoNuovo", myDealerUpdateFields.DatoNuovo)));
}
}
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "DealerUpdateXml";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = DealerUpdateXml.ToString();
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_Dealer_Update";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
return proc;
return "Scheda Dealer aggiornata con successo";
}
internal DataTable GetInfoDealer(string info, string param, string utente)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
SqlCommandObject sco = new SqlCommandObject();
switch (info)
{
case "S":
sco.SPName = "[Dealer].[SP_StatoDealer_GetAll]";
break;
case "T":
dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Utente";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = utente;
sco.SPParams = dbp;
sco.SPName = "[Dealer].[SP_TipoDealer_GetAll]";
break;
case "Z":
sco.SPName = "[Utility].[SP_Zone_GetAll]";
break;
case "C":
dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "TipoDealer";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = param;
sco.SPParams = dbp;
sco.SPName = "[Dealer].[SP_Canali_GetAll]";
break;
case "R":
sco.SPName = "[Utility].[SP_Regioni_Get]";
dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDZona";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = (param == "" ? "0" : param);
sco.SPParams = dbp;
break;
case "P":
sco.SPName = "[Utility].[SP_Province_Get]";
dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDRegione";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = (param == "" ? "0" : param);
sco.SPParams = dbp;
break;
case "L":
sco.SPName = "[Utility].[SP_Comuni_Get]";
dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDProvincia";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = (param == "" ? "0" : param);
sco.SPParams = dbp;
break;
case "A":
sco.SPName = "[Utility].[SP_AreaManager_GetAll]";
break;
case "O":
sco.SPName = "[Area].[SP_Operativita_GetAll]";
break;
case "F":
sco.SPName = "[Importo].[SP_Funzionario_GetAll]";
break;
case "V":
sco.SPName = "[Negozio].[SP_ModelloVendita_GetAll]";
break;
case "G":
sco.SPName = "[Utility].[SP_SegmentazioneCanale_GetAll]";
break;
case "CL":
sco.SPName = "[Negozio].[SP_Cluster_GetAll]";
break;
case "AC":
sco.SPName = "[Area].[SP_AreaCompetenza_GetAll]";
break;
}
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return(dt);
}
internal Boolean InsertUtente(Utente obj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[8];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Nome";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = obj.Nome;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Cognome";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = obj.Cognome;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "Email";
dbp[2].DbType = DbType.String;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = obj.Email;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "UserID";
dbp[3].DbType = DbType.String;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = obj.UserID;
dbp[4] = new SqlParameter();
dbp[4].ParameterName = "IDProfilo";
dbp[4].DbType = DbType.Int32;
dbp[4].Direction = ParameterDirection.Input;
dbp[4].Value = obj.IDProfilo;
dbp[5] = new SqlParameter();
dbp[5].ParameterName = "Attivo";
dbp[5].DbType = DbType.Boolean;
dbp[5].Direction = ParameterDirection.Input;
dbp[5].Value = obj.Attivo;
dbp[6] = new SqlParameter();
dbp[6].ParameterName = "IDUtente";
dbp[6].DbType = DbType.Int32;
dbp[6].Direction = ParameterDirection.Output;
dbp[6].Value = obj.IDUtente;
dbp[7] = new SqlParameter();
dbp[7].ParameterName = "Esiste";
dbp[7].DbType = DbType.Boolean;
dbp[7].Direction = ParameterDirection.Output;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Utenti_Insert";
sco.SPParams = dbp;
DataTable dt;
List <ExpandoObject> outputList = new List <ExpandoObject>();
string proc = sqlop.ExecuteProcedure(sco, out dt, out outputList);
IEnumerable <dynamic> esiste = outputList.Cast <dynamic>().Where(x => x.Nome.Contains("@Esiste"));
return(Convert.ToBoolean(esiste.First().Value));
}
internal DataTable GetUtente(int idObj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDUtente";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idObj;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Utenti_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
internal string UpdateDealerBusiness(DealerBusiness obj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[4];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDDealerBusiness";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = obj.IDDealerBusiness;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Codice";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = obj.Codice;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "Nome";
dbp[2].DbType = DbType.String;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = obj.Nome;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "Indirizzo";
dbp[3].DbType = DbType.String;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = obj.Indirizzo;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_DealerBusiness_Update";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
return proc;
return "";
}
internal DataTable GetLocalita(string pre,string prov, string regione)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
DbParameter[] dbp = new DbParameter[3];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Localita";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = pre;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Provincia";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = prov;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "Regione";
dbp[2].DbType = DbType.String;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = regione;
sco.SPName = "[Dealer].[SP_RicercaLocalita_Get]";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
internal string InsertProfilo(Profilo myProfilo, List<TipoDealer> myTipoDealerList, List<AutorizzazioneCampi> myAutorizzazioneCampiList)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[11];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "NomeProfilo";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = myProfilo.NomeProfilo;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "ArchivioDatabase";
dbp[1].DbType = DbType.Boolean;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = myProfilo.ArchivioDatabase;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "ArchivioCruscotto";
dbp[2].DbType = DbType.Boolean;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = myProfilo.ArchivioCruscotto;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "ReportAnomalie";
dbp[3].DbType = DbType.Boolean;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = myProfilo.ReportAnomalie;
dbp[4] = new SqlParameter();
dbp[4].ParameterName = "ReportScarti";
dbp[4].DbType = DbType.Boolean;
dbp[4].Direction = ParameterDirection.Input;
dbp[4].Value = myProfilo.ReportScarti;
dbp[5] = new SqlParameter();
dbp[5].ParameterName = "Portafoglio";
dbp[5].DbType = DbType.Boolean;
dbp[5].Direction = ParameterDirection.Input;
dbp[5].Value = myProfilo.Portafoglio;
dbp[6] = new SqlParameter();
dbp[6].ParameterName = "Amministrazione";
dbp[6].DbType = DbType.Boolean;
dbp[6].Direction = ParameterDirection.Input;
dbp[6].Value = myProfilo.Amministrazione;
dbp[7] = new SqlParameter();
dbp[7].ParameterName = "RagioniSocialiCorrelate";
dbp[7].DbType = DbType.Int32;
dbp[7].Direction = ParameterDirection.Input;
dbp[7].Value = myProfilo.RagioniSocialiCorrelate;
dbp[8] = new SqlParameter();
dbp[8].ParameterName = "Eccezioni";
dbp[8].DbType = DbType.Boolean;
dbp[8].Direction = ParameterDirection.Input;
dbp[8].Value = myProfilo.Eccezioni;
XElement tipoDealerXml = new XElement("Dealer",
from TipoDealer in myTipoDealerList
select new XElement("TipoDealer",
new XElement("IDTipoDealer", TipoDealer.IDTipoDealer)
)
);
dbp[9] = new SqlParameter();
dbp[9].ParameterName = "TipoDealerXml";
dbp[9].DbType = DbType.String;
dbp[9].Direction = ParameterDirection.Input;
dbp[9].Value = tipoDealerXml.ToString();
XElement autorizzazioneCampiXml = new XElement("Autorizzazioni",
from AutorizzazioneCampi in myAutorizzazioneCampiList
select new XElement("AutorizzazioneCampi",
new XElement("IDAutorizzazioneCampi", AutorizzazioneCampi.IDAutorizzazioneCampi),
new XElement("Autorizzazione", AutorizzazioneCampi.Autorizzazione),
new XElement("IDCampoDati", AutorizzazioneCampi.IDCampoDati)
)
);
dbp[10] = new SqlParameter();
dbp[10].ParameterName = "AutorizzazioneCampiXml";
dbp[10].DbType = DbType.String;
dbp[10].Direction = ParameterDirection.Input;
dbp[10].Value = autorizzazioneCampiXml.ToString();
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Profilo_Insert";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
return proc;
return "";
}
internal string InserRelazioneDealer(Relazioni rel)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[4];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "NomeRelazione";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = rel.NomeRelazione;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "IDTipoDealer";
dbp[1].DbType = DbType.Int32;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = rel.IDTipoDealer;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "IDDealer1";
dbp[2].DbType = DbType.Int32;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = rel.IDDealerConsumer;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "IDDealer2";
dbp[3].DbType = DbType.Int32;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = rel.IDDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_RelazioniDealer_Insert";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
return proc;
return "";
}
internal string InsertDealerImport(string idObj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
//dbp[0] = new SqlParameter();
//dbp[0].ParameterName = "IDDealer";
//dbp[0].DbType = DbType.Int32;
//dbp[0].Direction = ParameterDirection.Input;
//dbp[0].Value = idObj;
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Codice";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idObj;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Import.SP_EccezioniImport_Insert";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
return proc;
return "";
}
internal DataTable GetInfoDealer(string info, string param, string utente)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
SqlCommandObject sco = new SqlCommandObject();
switch (info)
{
case "S":
sco.SPName = "[Dealer].[SP_StatoDealer_GetAll]";
break;
case "T":
dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Utente";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = utente;
sco.SPParams = dbp;
sco.SPName = "[Dealer].[SP_TipoDealer_GetAll]";
break;
case "Z":
sco.SPName = "[Utility].[SP_Zone_GetAll]";
break;
case "C":
dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "TipoDealer";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = param;
sco.SPParams = dbp;
sco.SPName = "[Dealer].[SP_Canali_GetAll]";
break;
case "R":
sco.SPName = "[Utility].[SP_Regioni_Get]";
dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDZona";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = (param == "" ? "0" : param);
sco.SPParams = dbp;
break;
case "P":
sco.SPName = "[Utility].[SP_Province_Get]";
dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDRegione";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = (param == "" ? "0" : param);
sco.SPParams = dbp;
break;
case "L":
sco.SPName = "[Utility].[SP_Comuni_Get]";
dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDProvincia";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = (param == "" ? "0" : param);
sco.SPParams = dbp;
break;
case "A":
sco.SPName = "[Utility].[SP_AreaManager_GetAll]";
break;
case "O":
sco.SPName = "[Area].[SP_Operativita_GetAll]";
break;
case "F":
sco.SPName = "[Importo].[SP_Funzionario_GetAll]";
break;
case "V":
sco.SPName = "[Negozio].[SP_ModelloVendita_GetAll]";
break;
case "G":
sco.SPName = "[Utility].[SP_SegmentazioneCanale_GetAll]";
break;
case "CL":
sco.SPName = "[Negozio].[SP_Cluster_GetAll]";
break;
case "AC":
sco.SPName = "[Area].[SP_AreaCompetenza_GetAll]";
break;
case "TA":
sco.SPName = "[Negozio].[SP_CentroAssistenzaTecnica_GetAll]";
break;
}
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
internal DataTable GetStorico(int idObj, string tipologia)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDDealer";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idObj;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Tipologia";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = tipologia;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_Storico_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
internal Boolean InsertUtente(Utente obj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[8];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Nome";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = obj.Nome;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Cognome";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = obj.Cognome;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "Email";
dbp[2].DbType = DbType.String;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = obj.Email;
dbp[3] = new SqlParameter();
dbp[3].ParameterName = "UserID";
dbp[3].DbType = DbType.String;
dbp[3].Direction = ParameterDirection.Input;
dbp[3].Value = obj.UserID;
dbp[4] = new SqlParameter();
dbp[4].ParameterName = "IDProfilo";
dbp[4].DbType = DbType.Int32;
dbp[4].Direction = ParameterDirection.Input;
dbp[4].Value = obj.IDProfilo;
dbp[5] = new SqlParameter();
dbp[5].ParameterName = "Attivo";
dbp[5].DbType = DbType.Boolean;
dbp[5].Direction = ParameterDirection.Input;
dbp[5].Value = obj.Attivo;
dbp[6] = new SqlParameter();
dbp[6].ParameterName = "IDUtente";
dbp[6].DbType = DbType.Int32;
dbp[6].Direction = ParameterDirection.Output;
dbp[6].Value = obj.IDUtente;
dbp[7] = new SqlParameter();
dbp[7].ParameterName = "Esiste";
dbp[7].DbType = DbType.Boolean;
dbp[7].Direction = ParameterDirection.Output;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Utenti_Insert";
sco.SPParams = dbp;
DataTable dt;
List<ExpandoObject> outputList = new List<ExpandoObject>();
string proc = sqlop.ExecuteProcedure(sco, out dt, out outputList);
IEnumerable<dynamic> esiste = outputList.Cast<dynamic>().Where(x => x.Nome.Contains("@Esiste"));
return Convert.ToBoolean(esiste.First().Value);
}
internal Boolean ExistsDealerBusiness(string obj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Codice";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = obj;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Esiste";
dbp[1].DbType = DbType.Boolean;
dbp[1].Direction = ParameterDirection.Output;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_DealerBusiness_Exists";
sco.SPParams = dbp;
DataTable dt;
List<ExpandoObject> output;
string proc = sqlop.ExecuteProcedure(sco, out dt, out output);
IEnumerable<dynamic> idDealerBusiness = output.Cast<dynamic>().Where(x => x.Nome.Contains("@Esiste"));
return idDealerBusiness.First().Value;
}
internal DataTable GetAllDealerImport()
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_DealerImport_GetAll";
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (dt.Rows.Count == 0)
{
//dt.Columns.Add("IDDealer", typeof(int));
dt.Columns.Add("Codice", typeof(string));
}
return dt;
}
