public static List <SupplierObj> GetAll()
{
SqlCommand cmd = new SqlCommand("SELECT * FROM Suppliers ORDER BY SupplierName", cn);
SqlDataReader dr = cmd.ExecuteReader();
List <SupplierObj> SupplierList = new List <SupplierObj>();
while (dr.Read())
{
SupplierObj supplier = new SupplierObj();
supplier.SupplierID = int.Parse(dr["SupplierID"].ToString());
supplier.SupplierName = dr["SupplierName"] == DBNull.Value ? "" : dr["SupplierName"].ToString();
supplier.Address1 = dr["Address1"] == DBNull.Value ? "" : dr["Address1"].ToString();
supplier.Address2 = dr["Address2"] == DBNull.Value ? "" : dr["Address2"].ToString();
supplier.City = dr["City"] == DBNull.Value ? "" : dr["City"].ToString();
supplier.State = dr["State"] == DBNull.Value ? "" : dr["State"].ToString();
supplier.Zip = dr["Zip"] == DBNull.Value ? "" : dr["Zip"].ToString();
supplier.Country = dr["Country"] == DBNull.Value ? "" : dr["Country"].ToString();
supplier.Email = dr["Email"] == DBNull.Value ? "" : dr["Email"].ToString();
supplier.Fax = dr["Fax"] == DBNull.Value ? "" : dr["Fax"].ToString();
supplier.Phone = dr["Phone"] == DBNull.Value ? "" : dr["Phone"].ToString();
SupplierList.Add(supplier);
}
dr.Close();
dr.Dispose();
dr = null;
return(SupplierList);
}
public static SupplierObj GetByID(int SupplierID)
{
SqlCommand cmd = new SqlCommand("SELECT * FROM Suppliers Where SupplierID = " + SupplierID.ToString(), cn);
SqlDataReader dr = cmd.ExecuteReader();
SupplierObj supplier = new SupplierObj();
while (dr.Read())
{
supplier.SupplierID = SupplierID;
supplier.SupplierName = dr["SupplierName"] == DBNull.Value?"":dr["SupplierName"].ToString();
supplier.Address1 = dr["Address1"] == DBNull.Value ? "" : dr["Address1"].ToString();
supplier.Address2 = dr["Address2"] == DBNull.Value ? "" : dr["Address2"].ToString();
supplier.City = dr["City"] == DBNull.Value ? "" : dr["City"].ToString();
supplier.State = dr["State"] == DBNull.Value ? "" : dr["State"].ToString();
supplier.Zip = dr["Zip"] == DBNull.Value ? "" : dr["Zip"].ToString();
supplier.Country = dr["Country"] == DBNull.Value ? "" : dr["Country"].ToString();
supplier.Email = dr["Email"] == DBNull.Value ? "" : dr["Email"].ToString();
supplier.Fax = dr["Fax"] == DBNull.Value ? "" : dr["Fax"].ToString();
supplier.Phone = dr["Phone"] == DBNull.Value ? "" : dr["Phone"].ToString();
}
dr.Close();
dr.Dispose();
dr = null;
return(supplier);
}
public List <SupplierObj> GetSupplierByListLocalSKU(string ListLocalSKU)
{
string SelectCommand = "select distinct s.SupplierID,s.SupplierName,s.Email,s.Phone,si.Terms,s.Address1,s.Country,s.City,s.[State],s.Zip from Suppliers s left join SupplierInfo si on s.SupplierID=si.SupplierID join InventorySuppliers isu on isu.SupplierID=s.SupplierID and isu.PrimarySupplier=1 and isu.LocalSKU in(" + ListLocalSKU + ")";
SqlCommand cmd = new SqlCommand(SelectCommand, cn);
SqlDataReader dr = cmd.ExecuteReader();
List <SupplierObj> suppliers = new List <SupplierObj>();
while (dr.Read())
{
SupplierObj SupplierObj = new SupplierObj();
SupplierObj.SupplierID = dr["SupplierID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SupplierID"].ToString());
SupplierObj.SupplierName = dr["SupplierName"] == DBNull.Value ? "" : dr["SupplierName"].ToString();
SupplierObj.Email = dr["Email"] == DBNull.Value ? "" : dr["Email"].ToString();
SupplierObj.Phone = dr["Phone"] == DBNull.Value ? "" : dr["Phone"].ToString();
SupplierObj.Terms = dr["Terms"] == DBNull.Value ? "" : dr["Terms"].ToString();
SupplierObj.Address1 = dr["Address1"] == DBNull.Value ? "" : dr["Address1"].ToString();
SupplierObj.Country = dr["Country"] == DBNull.Value ? "" : dr["Country"].ToString();
SupplierObj.City = dr["City"] == DBNull.Value ? "" : dr["City"].ToString();
SupplierObj.State = dr["State"] == DBNull.Value ? "" : dr["State"].ToString();
SupplierObj.Zip = dr["Zip"] == DBNull.Value ? "" : dr["Zip"].ToString();
SupplierObj.Isordered = true;
suppliers.Add(SupplierObj);
}
dr.Close();
dr.Dispose();
dr = null;
return(suppliers);
}
public static List <SupplierObj> GetSupplierNameAndID()
{
SqlCommand cmd = new SqlCommand("SELECT SupplierID,SupplierName FROM Suppliers ORDER BY SupplierName", cn);
SqlDataReader dr = cmd.ExecuteReader();
List <SupplierObj> SupplierList = new List <SupplierObj>();
while (dr.Read())
{
SupplierObj supplier = new SupplierObj();
supplier.SupplierID = int.Parse(dr["SupplierID"].ToString());
supplier.SupplierName = dr["SupplierName"] == DBNull.Value ? "" : dr["SupplierName"].ToString();
SupplierList.Add(supplier);
}
dr.Close();
dr.Dispose();
dr = null;
return(SupplierList);
}
public static void Insert(SupplierObj supplier, string sendvia, DateTime Datedue, decimal totalPrice)
{
SqlConnection con = cn;
SqlCommand cmd = new SqlCommand("insert into PurchaseOrders(PONumber,PODate,SupplierID,Comments,SentVia,Terms,DateDue,Subtotal,ExpectedTotal,Closed,Instructions,ShippingMethod,Address1,City,[State],Zipcode,[Name],Country) values (( select MAX(PONumber)+1 from PurchaseOrders),@PODate,@SupplierID,@Comments,@SentVia,@Terms,@DateDue,@Subtotal,@ExpectedTotal,0,@Instructions,@ShippingMethod,@Address1,@City,@State,@Zipcode,@Name,@Country)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@PODate", DateTime.Now);
cmd.Parameters.AddWithValue("@SupplierID", supplier.SupplierID);
cmd.Parameters.AddWithValue("@Comments", supplier.Comments == null ? "" : supplier.Comments);
cmd.Parameters.AddWithValue("@SentVia", sendvia);
cmd.Parameters.AddWithValue("@Terms", supplier.Terms);
cmd.Parameters.AddWithValue("@DateDue", Datedue);
cmd.Parameters.AddWithValue("@Subtotal", totalPrice);
cmd.Parameters.AddWithValue("@ExpectedTotal", totalPrice);
cmd.Parameters.AddWithValue("@Instructions", supplier.Instructions == null ? "" : supplier.Instructions);
cmd.Parameters.AddWithValue("@ShippingMethod", supplier.ShippingMethod == null ? "" : supplier.ShippingMethod);
cmd.Parameters.AddWithValue("@Address1", supplier.Address1);
cmd.Parameters.AddWithValue("@City", supplier.City);
cmd.Parameters.AddWithValue("@State", supplier.State);
cmd.Parameters.AddWithValue("@Zipcode", supplier.Zip);
cmd.Parameters.AddWithValue("@Name", supplier.SupplierName);
cmd.Parameters.AddWithValue("@Country", supplier.Country);
cmd.ExecuteNonQuery();
}