public static void SetPoint(string areacode, string pointcode)
{
using (var conn = SqlBase.Create())
{
var sql = $"insert into tb_area_point values ('{areacode}','{pointcode}')";
conn.Execute(sql);
conn.Close();
}
}
public static void AddContainer(Container container)
{
using (var conn = SqlBase.Create())
{
var sql = $"insert into tb_container values ('{container.containercode}','{container.materielcode}',{container.capacity})";
conn.Execute(sql);
conn.Close();
}
}
public static void AddMateriel(Materiel materiel)
{
using (var conn = SqlBase.Create())
{
var sql = $"insert into tb_materiel values ('{materiel.materielcode}','{materiel.materielname}')";
conn.Execute(sql);
conn.Close();
}
}
public static void RemovePoint(string areacode, string pointcode)
{
using (var conn = SqlBase.Create())
{
var sql = $"delete from tb_area_point where AreaCode='{areacode}' and PointCode='{pointcode}'";
conn.Execute(sql);
conn.Close();
}
}
public static void AddProperty(Materiel_Property property)
{
using (var conn = SqlBase.Create())
{
var sql = $"insert into tb_materiel_property values ('{property.materielcode}','{property.propertyname}','{property.propertyvalue}')";
conn.Execute(sql);
conn.Close();
}
}
public static void SetContainer(string pointcode, string containercode)
{
using (var conn = SqlBase.Create())
{
var sql = $"insert into tb_point_container values ('{pointcode}','{containercode}')";
conn.Execute(sql);
conn.Close();
}
}
public static void RemoveMateriel(string areacode, string materielcode)
{
using (var conn = SqlBase.Create())
{
var sql = $"delete from tb_area_materiel where AreaCode='{areacode}' and MaterielCode='{materielcode}'";
conn.Execute(sql);
conn.Close();
}
}
public static void AddPoint(Point point)
{
using (var conn = SqlBase.Create())
{
var sql = $"insert into tb_point values ('{point.pointcode}','{point.warecode}','{point.pointstate}')";
conn.Execute(sql);
conn.Close();
}
}
public static void AddWare(Ware ware)
{
using (var conn = SqlBase.Create())
{
var sql = $"insert into tb_ware values ('{ware.warecode}','{ware.warename}', '{ware.enable}', {ware.rowcount}, {ware.columncount},{ware.layercount})";
conn.Execute(sql);
conn.Close();
}
}
public static void RemoveProperty(string materielcode, string propertyname)
{
using (var conn = SqlBase.Create())
{
var sql = $"delete from tb_materiel_property where MaterielCode='{materielcode}' and PropertyName='{propertyname}'";
conn.Execute(sql);
conn.Close();
}
}
public static void SetMateriel(string areacode, string materielcode)
{
using (var conn = SqlBase.Create())
{
var sql = $"insert into tb_area_materiel values ('{areacode}','{materielcode}')";
conn.Execute(sql);
conn.Close();
}
}
public static void RemoveContainer(string pointcode, string containercode)
{
using (var conn = SqlBase.Create())
{
var sql = $"delete from tb_point_container where PointCode='{pointcode}' and ContainerCode='{containercode}'";
conn.Execute(sql);
conn.Close();
}
}
public static void AddArea(Area area)
{
using (var conn = SqlBase.Create())
{
var sql = $"insert into tb_area values ('{area.areacode}','{area.areaname}','{area.warecode}')";
conn.Execute(sql);
conn.Close();
}
}
public static List <Point> SelectPointByWare(string warecode)
{
using (var conn = SqlBase.Create())
{
var sql = $"select * from tb_point where WareCode='{warecode}'";
var points = conn.Query <Point>(sql);
conn.Close();
return(points?.ToList());
}
}
public static List <Materiel> SelectMateriel()
{
using (var conn = SqlBase.Create())
{
var sql = $"select * from tb_materiel";
var list = conn.Query <Materiel>(sql);
conn.Close();
return(list?.ToList());
}
}
public static List <AreaMateriel> SelectMaterielByArea(string areacode)
{
using (var conn = SqlBase.Create())
{
var sql = $"select * from tb_area_materiel where AreaCode='{areacode}'";
var list = conn.Query <AreaMateriel>(sql);
conn.Close();
return(list?.ToList());
}
}
public static List <Area> SelectArea(string warecode)
{
using (var conn = SqlBase.Create())
{
var sql = "select * from tb_area";
var list = conn.Query <Area>(sql);
conn.Close();
return(list?.ToList());
}
}
public static List <Materiel_Property> SelectProperty(string materielcode)
{
using (var conn = SqlBase.Create())
{
var sql = $"select * from tb_materiel_property where MaterielCode='{materielcode}'";
var list = conn.Query <Materiel_Property>(sql);
conn.Close();
return(list?.ToList());
}
}
public static List <Ware> SelectWare()
{
using (var conn = SqlBase.Create())
{
var sql = $"select * from tb_ware";
var list = conn.Query <Ware>(sql);
conn.Close();
return(list?.ToList());
}
}
public static List <Container> SelectContainer(string pointcode)
{
using (var conn = SqlBase.Create())
{
var sql = $"select tb_container.ContainerCode,MaterielCode,Capacity from tb_container,tb_point_container where tb_container.ContainerCode=tb_point_container.ContainerCode and PointCode='{pointcode}'";
var list = conn.Query <Container>(sql);
conn.Close();
return(list?.AsList());
}
}
public static List <Container> SelectContainerByWare(string wareCode)
{
using (var conn = SqlBase.Create())
{
var sql = $"select * from tb_container where substring(ContainerCode,1, 14)='{wareCode}'";
var list = conn.Query <Container>(sql);
conn.Close();
return(list?.AsList());
}
}
public static List <Point> SelectPointByArea(string warecode, string areacode)
{
using (var conn = SqlBase.Create())
{
var sql = $"select * from( select tb_point.PointCode,AreaCode,WareCode,PointState from tb_point left join tb_area_point on tb_point.PointCode=tb_area_point.PointCode )t where AreaCode='{areacode}' and WareCode='{warecode}'";
if (string.IsNullOrEmpty(areacode))
{
sql = $"select * from( select tb_point.PointCode,AreaCode,WareCode,PointState from tb_point left join tb_area_point on tb_point.PointCode=tb_area_point.PointCode )t where (AreaCode is null or AreaCode='') and WareCode='{warecode}'";
}
var points = conn.Query <Point>(sql);
conn.Close();
return(points?.ToList());
}
}