/// <summary>
/// Выборка материалов по IdClass
/// </summary>
/// <param name="id">Id MaterialsClasses</param>
/// <returns>Список материалов</returns>
public IEnumerable <Materials> GetMaterials(int id)
{
List <Materials> Materials = new List <Materials>();
using (SqlConnection con = new SqlConnection(connectionString))
{
//SqlCommand cmd = new SqlCommand("SELECT * FROM [PortalEIPS].[dbo].[Materials] Where IdClass='" + id + "'", con);
SqlCommand cmd = new SqlCommand("SELECT [Materials].[Id] AS [Id]" +
",[Materials].[Number] AS [Number]" +
",[Materials].[IdClass] AS [IdClass]" +
",[MaterialsClasses].[Name] AS [MaterialsClassesName]" +
",[Materials].[Mark]" +
",[Materials].[Name]" +
",[Materials].[NormDoc]" +
",[Materials].[NumberOZM] " +
"FROM [dbo].[Materials] as [Materials] " +
"LEFT OUTER JOIN [dbo].[MaterialsClasses] AS [MaterialsClasses] ON [MaterialsClasses].Id = [Materials].IdClass " +
"Where [Materials].[IdClass] = '" + id + "'", con);
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Materials _materials = new Materials();
_materials.Id = Convert.ToInt32(rdr["Id"]);
_materials.Number = Convert.ToString(rdr["Number"]);
_materials.IdClass = Convert.ToInt32(rdr["IdClass"]);
_materials.MaterialsClassesName = Convert.ToString(rdr["MaterialsClassesName"]);
_materials.Mark = Convert.ToString(rdr["Mark"]);
_materials.Name = Convert.ToString(rdr["Name"]);
_materials.NormDoc = Convert.ToString(rdr["NormDoc"]);
_materials.NumberOZM = Convert.ToString(rdr["NumberOZM"]);
Materials.Add(_materials);
}
}
return(Materials);
}
/// <summary>
/// Выборка материалf по Id
/// </summary>
/// <param name="id">Id Материала</param>
/// <returns>Список полей матерала</returns>
public IEnumerable <Materials> GetMaterial(int id)
{
//Materials material = new Materials();
List <Materials> Material = new List <Materials>();
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("SELECT [Materials].[Id]" +
",[Materials].[IdClass]" +
",[MaterialsClasses].[Name] as [MaterialsClassesName]" +
",[Materials].[Stage]" +
",[Stage].[Description] as [StageDescription]" +
",[Materials].[Number]" +
",[Materials].[NumberOZM]" +
",[Materials].[NumberMaker]" +
",[Materials].[Mark]" +
",[Materials].[Name]" +
",[Materials].[Type]" +
",[Materials].[Unit]" +
",[Materials].[Weight]" +
",[Materials].[Currency]" +
",[Materials].[Price]" +
",[Materials].[IdMaker]" +
",[Materials].[RequireAlternativeMaker]" +
",[Materials].[NormDoc]" +
",[Materials].[NormDocMaterial]" +
",[Materials].[Info]" +
",[Materials].[Has2DDraw]" +
",[Materials].[Has3DModel]" +
",[Materials].[Stock]" +
",[Materials].[IdUpdater]" +
",[IdUpdater].[Name] as [IdUpdaterName]" +
",[Materials].[DateOfUpdate]" +
",[Materials].[IdStatus]" +
",[IdStatus].[Value] as [StatusValue]" +
",[Materials].[IdWorkFlowCurrentNode]" +
",[Materials].[IdDBCreator]" +
",[IdDBCreator].[Name] as [IdDBCreatorName]" +
",[Materials].[DBDateOfCreate]" +
",[Materials].[IdDBUpdater]" +
",[IdDBUpdater].[Name] as [IdDBUpdaterName]" +
",[Materials].[DBDateOfUpdate]" +
",[Materials].[DBUpdaterCompName]" +
",[Materials].[DBStatus]" +
",[Materials].[DeleteReason]" +
",[Materials].[IdReplacedMaterial]" +
",[Materials].[SapLoaded]" +
",[Materials].[UsedOnNLMK]" +
",[Materials].[Price_old]" +
"FROM [dbo].[Materials] as [Materials] " +
"LEFT OUTER JOIN [dbo].[MaterialsClasses] as [MaterialsClasses] on [MaterialsClasses].Id = [Materials].IdClass " +
"LEFT OUTER JOIN [dbo].[Workers] as [IdDBCreator] on [IdDBCreator].Id = [Materials].IdDBCreator " +
"LEFT OUTER JOIN [dbo].[Workers] as [IdDBUpdater] on [IdDBUpdater].Id = [Materials].IdDBUpdater " +
"LEFT OUTER JOIN [dbo].[Workers] as [IdUpdater] on [IdUpdater].Id = [Materials].IdUpdater " +
"LEFT OUTER JOIN [dbo].[Constants] as [Stage] on [Stage].[Id] = [Materials].[Stage] " +
"LEFT OUTER JOIN [dbo].[Constants] as [IdStatus] on [IdStatus].id = [Materials].IdStatus " +
"Where [Materials].[Id]='" + id + "'", con);
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Materials material = new Materials();
material.Id = Convert.ToInt32(rdr["Id"]);
material.Name = Convert.ToString(rdr["Name"]);
if (!DBNull.Value.Equals(rdr["IdClass"]))
{
material.IdClass = Convert.ToInt32(rdr["IdClass"]);
}
if (!DBNull.Value.Equals(rdr["Stage"]))
{
material.Stage = Convert.ToInt32(rdr["Stage"]);
}
material.StageDescription = Convert.ToString(rdr["StageDescription"]);
if (!DBNull.Value.Equals(rdr["Number"]))
{
material.Number = Convert.ToString(rdr["Number"]);
}
if (!DBNull.Value.Equals(rdr["NumberOZM"]))
{
material.NumberOZM = Convert.ToString(rdr["NumberOZM"]);
}
if (!DBNull.Value.Equals(rdr["NumberMaker"]))
{
material.NumberMaker = Convert.ToString(rdr["NumberMaker"]);
}
if (!DBNull.Value.Equals(rdr["Mark"]))
{
material.Mark = Convert.ToString(rdr["Mark"]);
}
if (!DBNull.Value.Equals(rdr["Name"]))
{
material.Name = Convert.ToString(rdr["Name"]);
}
if (!DBNull.Value.Equals(rdr["Type"]))
{
material.Type = Convert.ToString(rdr["Type"]);
}
if (!DBNull.Value.Equals(rdr["Unit"]))
{
material.Unit = Convert.ToString(rdr["Unit"]);
}
if (!DBNull.Value.Equals(rdr["Weight"]))
{
material.Weight = Convert.ToDouble(rdr["Weight"]);
}
if (!DBNull.Value.Equals(rdr["Currency"]))
{
material.Currency = Convert.ToString(rdr["Currency"]);
}
if (!DBNull.Value.Equals(rdr["Price"]))
{
material.Price = Convert.ToDouble(rdr["Price"]);
}
if (!DBNull.Value.Equals(rdr["IdMaker"]))
{
material.IdMaker = Convert.ToInt32(rdr["IdMaker"]);
}
if (!DBNull.Value.Equals(rdr["RequireAlternativeMaker"]))
{
material.RequireAlternativeMaker = Convert.ToBoolean(rdr["RequireAlternativeMaker"]);
}
if (!DBNull.Value.Equals(rdr["NormDoc"]))
{
material.NormDoc = Convert.ToString(rdr["NormDoc"]);
}
if (!DBNull.Value.Equals(rdr["NormDocMaterial"]))
{
material.NormDocMaterial = Convert.ToString(rdr["NormDocMaterial"]);
}
if (!DBNull.Value.Equals(rdr["Info"]))
{
material.Info = Convert.ToString(rdr["Info"]);
}
if (!DBNull.Value.Equals(rdr["Has2DDraw"]))
{
material.Has2DDraw = Convert.ToBoolean(rdr["Has2DDraw"]);
}
if (!DBNull.Value.Equals(rdr["Has3DModel"]))
{
material.Has3DModel = Convert.ToBoolean(rdr["Has3DModel"]);
}
if (!DBNull.Value.Equals(rdr["Stock"]))
{
material.Stock = Convert.ToDouble(rdr["Stock"]);
}
if (!DBNull.Value.Equals(rdr["IdUpdater"]))
{
material.IdUpdater = Convert.ToInt32(rdr["IdUpdater"]);
}
if (!DBNull.Value.Equals(rdr["DateOfUpdate"]))
{
material.DateOfUpdate = Convert.ToDateTime(rdr["DateOfUpdate"]);
}
if (!DBNull.Value.Equals(rdr["IdStatus"]))
{
material.IdStatus = Convert.ToInt32(rdr["IdStatus"]);
}
if (!DBNull.Value.Equals(rdr["StatusValue"]))
{
string str = Convert.ToString(rdr["StatusValue"]);
if (str != "")
{
material.StatusValue = str.Substring(0, str.IndexOf('|'));
}
}
if (!DBNull.Value.Equals(rdr["IdWorkFlowCurrentNode"]))
{
material.IdWorkFlowCurrentNode = Convert.ToInt32(rdr["IdWorkFlowCurrentNode"]);
}
if (!DBNull.Value.Equals(rdr["IdDBCreator"]))
{
material.IdDBCreator = Convert.ToInt32(rdr["IdDBCreator"]);
}
if (!DBNull.Value.Equals(rdr["DBDateOfCreate"]))
{
material.DBDateOfCreate = Convert.ToDateTime(rdr["DBDateOfCreate"]);
}
if (!DBNull.Value.Equals(rdr["IdDBUpdater"]))
{
material.IdDBUpdater = Convert.ToInt32(rdr["IdDBUpdater"]);
}
if (!DBNull.Value.Equals(rdr["DBDateOfUpdate"]))
{
material.DBDateOfUpdate = Convert.ToDateTime(rdr["DBDateOfUpdate"]);
}
if (!DBNull.Value.Equals(rdr["DBUpdaterCompName"]))
{
material.DBUpdaterCompName = Convert.ToString(rdr["DBUpdaterCompName"]);
}
if (!DBNull.Value.Equals(rdr["DBStatus"]))
{
material.DBStatus = Convert.ToInt32(rdr["DBStatus"]);
}
if (!DBNull.Value.Equals(rdr["DeleteReason"]))
{
material.DeleteReason = Convert.ToString(rdr["DeleteReason"]);
}
if (!DBNull.Value.Equals(rdr["IdReplacedMaterial"]))
{
material.IdReplacedMaterial = Convert.ToInt32(rdr["IdReplacedMaterial"]);
}
if (!DBNull.Value.Equals(rdr["SapLoaded"]))
{
material.SapLoaded = Convert.ToBoolean(rdr["SapLoaded"]);
}
if (!DBNull.Value.Equals(rdr["UsedOnNLMK"]))
{
material.UsedOnNLMK = Convert.ToBoolean(rdr["UsedOnNLMK"]);
}
if (!DBNull.Value.Equals(rdr["Price_old"]))
{
material.Price_old = Convert.ToDouble(rdr["Price_old"]);
}
material.MaterialsClassesName = Convert.ToString(rdr["MaterialsClassesName"]);
material.IdUpdaterName = Convert.ToString(rdr["IdUpdaterName"]);
material.IdDBCreatorName = Convert.ToString(rdr["IdDBCreatorName"]);
material.IdDBUpdaterName = Convert.ToString(rdr["IdDBUpdaterName"]);
Material.Add(material);
}
}
return(Material);
}
