public CompanyValues DelCompany(string duns)
{
using (var conn = new SqlConnection(SQL_CONNECTION_STRING))
using (var command = new SqlCommand("s_DeleteCompany", conn)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
CompanyValues cv = new CompanyValues();
command.Parameters.AddWithValue("@VndNbr", duns);
conn.Open();
var adapter = new SqlDataAdapter(command);
var ds = new DataSet();
try
{
adapter.Fill(ds);
cv.retVal = "0";
}
catch (Exception ex)
{
cv.retVal = ex.Message.ToString();
}
return(cv);
}
}
public CompanyValues ListRoles(int VndNbr)
{
using (var conn = new SqlConnection(SQL_CONNECTION_STRING))
using (var command = new SqlCommand("s_GetCompanyRoles", conn)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
CompanyValues cv = new CompanyValues();
command.Parameters.AddWithValue("@VndNbr", VndNbr);
conn.Open();
var adapter = new SqlDataAdapter(command);
var ds = new DataSet();
try
{
adapter.Fill(ds);
List <string> lVal = new List <string>();
foreach (DataRow theRow in ds.Tables[0].Rows)
{
lVal.Add(theRow[0] + "," + theRow[1]);
}
cv.roles = lVal;
}
catch (Exception ex)
{
cv.retVal = ex.Message.ToString();
}
return(cv);
}
}
public CompanyValues UpdateEnabledStatus(int VndNbr, bool bEnabled)
{
using (var conn = new SqlConnection(SQL_CONNECTION_STRING))
using (var command = new SqlCommand("s_UpdateCompanyEnabledStatus", conn)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
CompanyValues cv = new CompanyValues();
command.Parameters.AddWithValue("@VndNbr", VndNbr);
command.Parameters.AddWithValue("@Enabled", bEnabled);
conn.Open();
var adapter = new SqlDataAdapter(command);
var ds = new DataSet();
try
{
adapter.Fill(ds);
cv.retVal = "0";
}
catch (Exception ex)
{
cv.retVal = ex.Message.ToString();
}
return(cv);
}
}
public CompanyValues DeleteAccount(string[] accounts)
{
using (var conn = new SqlConnection(SQL_CONNECTION_STRING))
using (var command = new SqlCommand("s_DeleteCompanyAccount", conn)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
CompanyValues cv = new CompanyValues();
string[] awv = accounts[0].Split(',');
command.Parameters.AddWithValue("@VndNbr", awv[1]);
command.Parameters.AddWithValue("@AcctID", awv[2]);
conn.Open();
var adapter = new SqlDataAdapter(command);
var ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch (Exception ex)
{
cv.retVal = ex.Message.ToString();
}
return(cv);
}
}
public CompanyValues UpdateAddresses(string[] addresses)
{
using (var conn = new SqlConnection(SQL_CONNECTION_STRING))
using (var command = new SqlCommand("s_UpdateCompanyLocation", conn)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
CompanyValues cv = new CompanyValues();
string[] awv = addresses[0].Split(',');
command.Parameters.AddWithValue("@VndNbr", awv[1]);
command.Parameters.AddWithValue("@RoleNbr", awv[2]);
command.Parameters.AddWithValue("@AddrSeqNbr", awv[3]);
command.Parameters.AddWithValue("@HqFlag", awv[4]);
command.Parameters.AddWithValue("@EnabledFlag", awv[5]);
command.Parameters.AddWithValue("@LocName", awv[6]);
command.Parameters.AddWithValue("@LocAbbr", awv[7]);
command.Parameters.AddWithValue("@DunsSuffix", awv[8]);
command.Parameters.AddWithValue("@LocTypeNbr", awv[9]);
command.Parameters.AddWithValue("@DivNbr", awv[10]);
command.Parameters.AddWithValue("@APCode", awv[11]);
command.Parameters.AddWithValue("@ConvCode", awv[12]);
command.Parameters.AddWithValue("@AddrLine1", awv[13]);
command.Parameters.AddWithValue("@AddrLine2", awv[14]);
command.Parameters.AddWithValue("@AddrLine3", awv[15]);
command.Parameters.AddWithValue("@AddrLine4", awv[16]);
command.Parameters.AddWithValue("@City", awv[17]);
command.Parameters.AddWithValue("@Zipcode", awv[18]);
command.Parameters.AddWithValue("@State", awv[19]);
command.Parameters.AddWithValue("@NonUSState", awv[20]);
command.Parameters.AddWithValue("@NtnNbr", awv[21]);
command.Parameters.AddWithValue("@RegionNbr", awv[22]);
command.Parameters.AddWithValue("@Phone", awv[23]);
command.Parameters.AddWithValue("@PhoneExt", awv[24]);
command.Parameters.AddWithValue("@Email", awv[25]);
command.Parameters.AddWithValue("@AltPhone", awv[26]);
command.Parameters.AddWithValue("@AltPhoneExt", awv[27]);
command.Parameters.AddWithValue("@Fax", awv[28]);
command.Parameters.AddWithValue("@PrefMediumTypeNbr", awv[29]);
conn.Open();
var adapter = new SqlDataAdapter(command);
var ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch (Exception ex)
{
cv.retVal = ex.Message.ToString();
}
return(cv);
}
}
public CompanyValues AddRoles(string[] roles)
{
using (var conn = new SqlConnection(SQL_CONNECTION_STRING))
using (var command = new SqlCommand("s_InsertCompanyRole", conn)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
CompanyValues cv = new CompanyValues();
string[] awv = roles[0].Split(',');
command.Parameters.AddWithValue("@VndNbr", awv[1]);
command.Parameters.AddWithValue("@RoleNbr", awv[2]);
var returnPwdFlag = command.Parameters.Add("@RetVal", System.Data.SqlDbType.Int);
returnPwdFlag.Direction = System.Data.ParameterDirection.Output;
conn.Open();
var adapter = new SqlDataAdapter(command);
var ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch (Exception ex)
{
cv.retVal = ex.Message.ToString();
}
return(cv);
}
}
//RETURNS THE VENDOR NUMBER, VENDOR ROLE NUMBER
public CompanyValues AddCompany(string name, string duns, bool iFlag)
{
using (var conn = new SqlConnection(SQL_CONNECTION_STRING))
using (var command = new SqlCommand("s_InsertCompany", conn)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
CompanyValues cv = new CompanyValues();
command.Parameters.AddWithValue("@VndName", name);
command.Parameters.AddWithValue("@DunsNbr", duns);
command.Parameters.AddWithValue("@InternalFlag", iFlag);
var returnPwdFlag = command.Parameters.Add("@RetVal", System.Data.SqlDbType.Int);
returnPwdFlag.Direction = System.Data.ParameterDirection.Output;
conn.Open();
var adapter = new SqlDataAdapter(command);
var ds = new DataSet();
try
{
adapter.Fill(ds);
cv.retVal = returnPwdFlag.Value.ToString();
}
catch (Exception ex)
{
cv.retVal = ex.Message.ToString();
}
return(cv);
}
}
public CompanyValues AddAddresses(string[] addresses)
{//http://localhost:57559/api/CoUpdateLists/?vndNbr=11&roles[]=X,11,1&addresses[]=A,11,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,225,4,0,0,0,0,0,0&accounts[]=A,0,0
using (var conn = new SqlConnection(SQL_CONNECTION_STRING))
using (var command = new SqlCommand("s_InsertCompanyLocation", conn)
{
CommandType = System.Data.CommandType.StoredProcedure
})
{
CompanyValues cv = new CompanyValues();
string[] awv = addresses[0].Split(',');
command.Parameters.AddWithValue("@VndNbr", awv[1]);
command.Parameters.AddWithValue("@RoleNbr", awv[2]);
command.Parameters.AddWithValue("@LocName", awv[3]);
command.Parameters.AddWithValue("@LocAbbr", awv[4]);
command.Parameters.AddWithValue("@DunsSuffix", awv[5]);
command.Parameters.AddWithValue("@LocTypeNbr", awv[6]);
command.Parameters.AddWithValue("@DivNbr", awv[7]);
command.Parameters.AddWithValue("@APCode", awv[8]);
command.Parameters.AddWithValue("@ConvCode", awv[9]);
command.Parameters.AddWithValue("@HqFlag", awv[10]);
command.Parameters.AddWithValue("@EnabledFlag", awv[11]);
command.Parameters.AddWithValue("@AddrLine1", awv[12]);
command.Parameters.AddWithValue("@AddrLine2", awv[13]);
command.Parameters.AddWithValue("@AddrLine3", awv[14]);
command.Parameters.AddWithValue("@AddrLine4", awv[15]);
command.Parameters.AddWithValue("@City", awv[16]);
command.Parameters.AddWithValue("@Zipcode", awv[17]);
command.Parameters.AddWithValue("@State", awv[18]);
command.Parameters.AddWithValue("@NonUSState", awv[19]);
command.Parameters.AddWithValue("@NtnNbr", awv[20]);
command.Parameters.AddWithValue("@RegionNbr", awv[21]);
command.Parameters.AddWithValue("@Phone", awv[22]);
command.Parameters.AddWithValue("@PhoneExt", awv[23]);
command.Parameters.AddWithValue("@Email", awv[24]);
command.Parameters.AddWithValue("@AltPhone", awv[25]);
command.Parameters.AddWithValue("@AltPhoneExt", awv[26]);
command.Parameters.AddWithValue("@Fax", awv[27]);
var returnPwdFlag = command.Parameters.Add("@RetVal", System.Data.SqlDbType.Int);
returnPwdFlag.Direction = System.Data.ParameterDirection.Output;
conn.Open();
var adapter = new SqlDataAdapter(command);
var ds = new DataSet();
try
{
adapter.Fill(ds);
cv.retVal = returnPwdFlag.ToString();
}
catch (Exception ex)
{
cv.retVal = ex.Message.ToString();
}
return(cv);
}
}
public CompanyValues UpdateLists(int VndNbr, string[] Roles, string[] Addresses, string[] Accounts)
{
CompanyValues cv = new CompanyValues();
string[] roles = Roles[0].Split(',');
switch (roles[0].ToString())
{
case "A":
AddRoles(Roles);
break;
case "D":
DeleteRoles(Roles);
break;
}
string[] addresses = Addresses[0].Split(',');
switch (addresses[0].ToString())
{
case "A":
AddAddresses(Addresses);
break;
case "D":
DeleteAddresses(Addresses);
break;
case "U":
UpdateAddresses(Addresses);
break;
}
string[] accounts = Accounts[0].Split(',');
switch (accounts[0].ToString())
{
case "A":
InsertAccount(Accounts);
break;
case "D":
DeleteAccount(Accounts);
break;
}
return(cv);
}
