//获取群组信息
public static DbDataReader Groupinfo()
{
string sql = "select * from tgroup ";
DbDataReader reader = dbhelp.ExecuteReader(sql, null);
return(reader);
}
public static DbDataReader QueryLotInfo(string lotid)
{
string sql = " select a.lotid,a.WorkshopID,ifnull(a.Warehouse,'') warehouse,a.Shipment,"
+ " ( select worksiteid from jh_mes.tworkflow where flowid = a.flowid and flowidno = a.flowidno ) worksiteid, "
+ " b.workordertype,b.workorderid,"//c.warehousecode,c.warehousetype, "
+ " ifnull(a.validLength,'0') length,a.validWidth width,b.MouldPETType pettype,b.MouldType type,b.MouldPinMin pinmin,b.MouldThinkness thinkness"
+ " from tlotbasis a "
+ " inner join tworkorderinfo b on a.WorkOrder = b.workorderid "
//+ " inner join twarehousecodeconfig c on c.workordertype = b.workordertype and c.workshopid = b.workshopid "
+ " where a.lotid = '" + lotid + "' ";
return(dbhelp.ExecuteReader(sql, null));
}
public static void setWOddl(DropDownList ddl, string type)
{
ddl.AppendDataBoundItems = true;
string sql = "select distinct " + type + " value from tworkorderinfo";
using (DbDataReader reader = dbhelp.ExecuteReader(sql, null))
{
ddl.Items.Add(new ListItem("ALL", "ALL"));
while (reader.Read())
{
ddl.Items.Add(new ListItem(reader["value"].ToString(), reader["value"].ToString()));
}
ddl.DataBind();
}
}
public static DbDataReader QueryLotInfo(string lotid)
{
string sql = " select (select warehousecode from twarehousecodeconfig where ID = a.Warehousecode) warehousecode "
+ " ,a.PinMin "
+ " ,a.lotid "
+ " ,a.Type "
+ " ,ifnull(b.ValidWidth,0) validwidth "
+ " ,ifnull(b.ValidLength,0) validlength "
+ " ,b.warehouse "
+ " ,b.shipment "
+ " from twarehouseinfo a "
+ " inner JOIN "
+ " tlotbasis b "
+ " on a.lotid = b.lotid "
+ " where a.lotid='" + lotid + "'";
return(dbhelp.ExecuteReader(sql, null));
}
/// <summary>
/// 运单总数
/// </summary>
/// <returns></returns>
internal DataTable GetTptShipmentCount(ReportTvModels olap, IOptions <AppSettings> settings)
{
if (olap.name == "SX")
{
olap.sql = oraTptShipmentCount;
olap.connect = settings.Value.OraConnectionString.TestScmpOdbc;//OnlineTjSX;
}
db = new DbUtility(olap, DbProvider.ORACLE);
return(db.ExecuteReader(olap));
}
/// <summary>
/// 大屏展示 地图坐标 弃用 MapOrder
/// </summary>
/// <param name="olap"></param>
/// <param name="settings"></param>
/// <returns></returns>
internal DataTable GetTvMap(ReportTvModels olap, IOptions <AppSettings> settings)
{
if (olap.name == "SX")
{
olap.sql = sqlMapXy;
olap.connect = settings.Value.SqlConnectionString.Test193;//OnlineTjSX;
}
db = new DbUtility(olap);
return(db.ExecuteReader(olap));
}
private long GetUUID(DbUtility utility)
{
string sql = @" select uuid_short() as UUID;";
var list = utility.ExecuteReader(sql);
foreach (var dic in list)
{
return(Convert.ToInt64(dic["UUID"]));
}
return(0);
}
public static void Setdll(DropDownList ddl)
{
ddl.AppendDataBoundItems = true;
string sql = "select paraname,paraid from jh_mes.tparaconfig where paratype = 'paratype' or paratype='paratypename'; ";
using (DbDataReader reader = dbhelp.ExecuteReader(sql, null))
{
while (reader.Read())
{
ddl.Items.Add(new ListItem(reader["paraname"].ToString(), reader["paraid"].ToString()));
}
ddl.DataBind();
}
}
//查询当日最大批次数量
public static int QueryMaxNum()
{
string sql = " select ifnull(max(lotid),0) num from jh_mes.tlotbasis where lottype = 'Mould' -- and date(createtime) = curdate() ";
DbDataReader result = dbhelp.ExecuteReader(sql, null);
if (result.Read())
{
return(Convert.ToInt32(result["num"]));
}
else
{
return(0);
}
}
public static int QueryMaxNum(string WO)
{
string sql = " select ifnull(max(right(lotid,3)),0) num from jh_mes.tlotbasis where lottype = 'Film' and workorder = '" + WO + "' and lotid like '" + WO + "%' -- and date(createtime) = curdate() ";
DbDataReader result = dbhelp.ExecuteReader(sql, null);
if (result.Read())
{
return(Convert.ToInt32(result["num"]));
}
else
{
return(0);
}
}
/// <summary>
/// 大屏展示 预约信息
/// </summary>
/// <param name="olap"></param>
/// <param name="settings"></param>
/// <returns></returns>
internal DataTable getresultTable(ReportTvModels olap, IOptions <AppSettings> settings)
{
if (olap.name == "SCNJ")
{
olap.sql = sqlReserveDt;
olap.connect = settings.Value.SqlConnectionString.Test127sc;
}
else if (olap.name == "SX")
{
olap.sql = sqlReserveDt;
olap.connect = settings.Value.SqlConnectionString.Test127sx;
}
db = new DbUtility(olap);
return(db.ExecuteReader(olap));
}
public static void SetWarehouseCodedll(DropDownList ddl)
{
ddl.AppendDataBoundItems = false;
string sql = "select id paraid,warehousecode paraname from jh_mes.twarehousecodeconfig ; ";
ddl.Items.Add(new ListItem("All", "All"));
using (DbDataReader reader = dbhelp.ExecuteReader(sql, null))
{
while (reader.Read())
{
ddl.Items.Add(new ListItem(reader["paraname"].ToString(), reader["paraid"].ToString()));
}
ddl.DataBind();
}
}
public static void Setdll(DropDownList ddl, string workshopid)
{
ddl.AppendDataBoundItems = true;
ddl.Items.Clear();
string sql = "select warehousecode,workshopid,workordertype from jh_mes.twarehousecodeconfig where workshopid = '" + workshopid + "' ; ";
ddl.Items.Add(new ListItem("ALL", "ALL"));
using (DbDataReader reader = dbhelp.ExecuteReader(sql, null))
{
while (reader.Read())
{
ddl.Items.Add(new ListItem(reader["warehousecode"].ToString(), reader["warehousecode"].ToString()));
}
ddl.DataBind();
}
}
public static void SetEqpDDL(DropDownList ddl, string workshop, string worksite)
{
ddl.AppendDataBoundItems = true;
ddl.Items.Clear();
ddl.Items.Add(new ListItem("All", ""));
string sql = "select eqpid,eqpname from jh_mes.teqpinfo where workshopid = '" + workshop + "' and worksiteid = '" + worksite + "' ; ";
using (DbDataReader reader = dbhelp.ExecuteReader(sql, null))
{
while (reader.Read())
{
ddl.Items.Add(new ListItem(reader["eqpname"].ToString(), reader["eqpid"].ToString()));
}
ddl.DataBind();
}
}
/// <summary>
/// 大屏展示 储位信息
/// </summary>
/// <param name="olap"></param>
/// <param name="settings"></param>
/// <returns></returns>
public DataTable getresult(ReportTvModels olap, IOptions <AppSettings> settings)
{
if (olap.name == "SCNJ")
{
olap.sql = string.Format(sqlStorageDetN, olap.name);
olap.connect = settings.Value.SqlConnectionString.Test127sc;
}
if (olap.name == "SX")
{
olap.sql = sqlStorageDet;
olap.sql += string.IsNullOrWhiteSpace(olap.warehouse) ? "" : string.Format(@" and warehouse='{0}'", olap.warehouse);
olap.connect = settings.Value.SqlConnectionString.Test127sx;
}
db = new DbUtility(olap);
return(db.ExecuteReader(olap));
}
public static Userdata getUserInfo(string id)
{
Userdata user = new Userdata();
string sql = "select name ,class,station,password , workshop from jh_mes.tUser where id ='" + id + "'";
using (DbDataReader reader = dbhelp.ExecuteReader(sql, null))
{
if (reader.Read())
{
user.ID = id;
user.Name = reader["name"].ToString();
user.Pwd = reader["password"].ToString();
user.Station = reader["station"].ToString();
user.Workshop = reader["workshop"].ToString();
}
}
return(user);
}
/// <summary>
/// 大屏展示 储位明细信息
/// </summary>
/// <param name="olap"></param>
/// <param name="settings"></param>
/// <returns></returns>
internal DataTable getresultDet(ReportTvModels olap, IOptions <AppSettings> settings)
{
if (olap.name == "SCNJ")
{
olap.sql =
sqlStorageDetl += string.IsNullOrWhiteSpace(olap.name + olap.location) ? "" :
string.Format(@" and Location='{0}'", olap.name + olap.location);
olap.connect = settings.Value.SqlConnectionString.Test127sc;
}
else if (olap.name == "SX")
{
olap.sql = sqlStorageDetl2;
olap.sql += string.IsNullOrWhiteSpace(olap.warehouse) ? "" : string.Format(@" and warehouse ='{0}'", olap.warehouse);
olap.sql += string.IsNullOrWhiteSpace(olap.positions) ? "" : string.Format(@" and positions ='{0}'", olap.positions);
olap.sql += string.IsNullOrWhiteSpace(olap.location) ? "" : (olap.location == "down" ? @" and dan <=20" : " and dan >20");
olap.connect = settings.Value.SqlConnectionString.Test127sx;
}
db = new DbUtility(olap);
return(db.ExecuteReader(olap));
}
