public static List <mg_stationModel> QueryStationForStepEditing(string fl_id)
{
List <mg_stationModel> list = null;
string qsql = (!string.IsNullOrEmpty(fl_id) && fl_id != "0") ? @" where fl_id=" + fl_id : "";
string sql = @"SELECT [st_id]
,[st_no]
,[st_name]
FROM [mg_station]
" + qsql + @"
order by st_order";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null);
if (DataHelper.HasData(dt))
{
list = new List <mg_stationModel>();
foreach (DataRow row in dt.Rows)
{
mg_stationModel model = new mg_stationModel();
model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id"));
model.st_no = DataHelper.GetCellDataToStr(row, "st_no") + " | " + DataHelper.GetCellDataToStr(row, "st_name");
list.Add(model);
}
mg_stationModel firstmodel = new mg_stationModel();
firstmodel.st_id = 0;
firstmodel.st_no = "-- 工位(全部) --";
list.Insert(0, firstmodel);
}
return(list);
}
public static int AddStation(mg_stationModel model)
{
string maxid = @"declare @i int;declare @o int;
SELECT @i=max([st_id]) FROM [mg_station];
if @i is null
begin
set @i=1
end
else
begin
set @i=@i+1
end;
SELECT @o=max([st_order]) FROM [mg_station];
if @o is null
begin
set @o=1
end
else
begin
set @o=@o+1
end;
";
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into mg_station(");
strSql.Append("st_id,fl_id,st_no,st_name,st_mac,st_typeid,st_order,st_mushifile,st_odsfile,st_QualityAlarm,st_isfirst,st_isend,st_clock_Start,st_clock)");
strSql.Append(" values (");
strSql.Append("@i,@fl_id,@st_no,@st_name,@st_mac,@st_typeid,@o,@st_mushifile,@st_odsfile,@st_alarmfile,@st_isfirst,@st_isend,@st_clock_Start,@st_clock)");
SqlParameter[] parameters =
{
new SqlParameter("@fl_id", SqlDbType.Int),
new SqlParameter("@st_no", SqlDbType.NVarChar),
new SqlParameter("@st_name", SqlDbType.NVarChar),
new SqlParameter("@st_mac", SqlDbType.VarChar),
new SqlParameter("@st_typeid", SqlDbType.Int),
new SqlParameter("@st_mushifile", SqlDbType.VarChar),
new SqlParameter("@st_odsfile", SqlDbType.VarChar),
new SqlParameter("@st_alarmfile", SqlDbType.VarChar),
new SqlParameter("@st_isfirst", SqlDbType.Int),
new SqlParameter("@st_isend", SqlDbType.Int),
new SqlParameter("@st_clock_Start", SqlDbType.Int),
new SqlParameter("@st_clock", SqlDbType.Int)
};
parameters[0].Value = model.fl_id;
parameters[1].Value = model.st_no;
parameters[2].Value = model.st_name;
parameters[3].Value = model.st_mac;
parameters[4].Value = model.st_typeid;
parameters[5].Value = model.st_mushifile;
parameters[6].Value = model.st_odsfile;
parameters[7].Value = model.st_alarmfile;
parameters[8].Value = model.st_isfirst;
parameters[9].Value = model.st_isend;
parameters[10].Value = model.st_clock_Start;
parameters[11].Value = model.st_clock;
int rows = SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, System.Data.CommandType.Text, maxid + strSql, parameters);
return(rows);
}
/// <summary>
/// 修改时间：2017年5月9日，修改内容：工位信息展示只展示工位号，不展示工位说明
/// 修改人：冉守旭
/// </summary>
/// <returns></returns>
public static List <mg_stationModel> QueryStationForOperatorEditing()
{
List <mg_stationModel> list = null;
// string sql = @"SELECT [st_id]
// ,[st_no]
// ,[st_name]
// FROM [mg_station] order by st_no";
string sql = @"SELECT [st_no],max(st_id) as st_id,max(st_mac) as st_mac
FROM [mg_station] where st_mac!='' and st_mac is not null
group by st_no order by st_no ";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null);
if (DataHelper.HasData(dt))
{
list = new List <mg_stationModel>();
foreach (DataRow row in dt.Rows)
{
mg_stationModel model = new mg_stationModel();
model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id"));
//model.st_name = DataHelper.GetCellDataToStr(row, "st_no") + " --- " + DataHelper.GetCellDataToStr(row, "st_name");
model.st_name = DataHelper.GetCellDataToStr(row, "st_no");
list.Add(model);
}
}
return(list);
}
public static int UpdateStation(mg_stationModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update mg_station set ");
strSql.Append(" fl_id=@fl_id,");
strSql.Append("st_no=@st_no,");
strSql.Append("st_name=@st_name,");
strSql.Append("st_mac=@st_mac,");
strSql.Append("st_typeid=@st_typeid,");
strSql.Append("st_mushifile=@st_mushifile,");
strSql.Append("st_isfirst=@st_isfirst,");
strSql.Append("st_isend=@st_isend,");
strSql.Append("st_odsfile=@st_odsfile,");
strSql.Append("st_clock_Start=@st_clock_Start,");
strSql.Append("st_clock=@st_clock,");
strSql.Append("st_QualityAlarm=@st_alarmfile ");
strSql.Append(" where st_id=@st_id;");
SqlParameter[] parameters =
{
new SqlParameter("@st_id", SqlDbType.Int),
new SqlParameter("@fl_id", SqlDbType.Int),
new SqlParameter("@st_no", SqlDbType.NVarChar),
new SqlParameter("@st_name", SqlDbType.NVarChar),
new SqlParameter("@st_mac", SqlDbType.VarChar),
new SqlParameter("@st_typeid", SqlDbType.Int),
new SqlParameter("@st_mushifile", SqlDbType.VarChar),
new SqlParameter("@st_odsfile", SqlDbType.VarChar),
new SqlParameter("@st_isfirst", SqlDbType.Int),
new SqlParameter("@st_isend", SqlDbType.Int),
new SqlParameter("@st_clock_Start", SqlDbType.Int),
new SqlParameter("@st_clock", SqlDbType.Int),
new SqlParameter("@st_alarmfile", SqlDbType.VarChar),
};
parameters[0].Value = model.st_id;
parameters[1].Value = model.fl_id;
parameters[2].Value = model.st_no;
parameters[3].Value = model.st_name;
parameters[4].Value = model.st_mac;
parameters[5].Value = model.st_typeid;
parameters[6].Value = model.st_mushifile;
parameters[7].Value = model.st_odsfile;
parameters[8].Value = model.st_isfirst;
parameters[9].Value = model.st_isend;
parameters[10].Value = model.st_clock_Start;
parameters[11].Value = model.st_clock;
parameters[12].Value = model.st_alarmfile;
int rows = SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, System.Data.CommandType.Text, strSql.ToString(), parameters);
return(rows);
}
void saveStation()
{
string st_id = Request.Params["st_id"];
string fl_id = Request.Params["fl_id"];
string st_typeid = Request.Params["st_typeid"];
string st_no = Request.Params["st_no"];
string st_name = Request.Params["st_name"];
string st_mac = Request.Params["st_mac"];
string st_mushifile = Request.Params["st_mushifile"];
string st_odsfile = Request.Params["st_odsfile"];
string st_alarmfile = Request.Params["st_alarmfile"];
string st_isfirst = Request.Params["st_isfirst"];
string st_isend = Request.Params["st_isend"];
string st_clock_Start = Request.Params["st_clock_Start"];
string st_clock = Request.Params["st_clock"];
#region 判断参数
if (!FormatHelper.IsInteger(st_clock_Start))
{
Response.Write("计时开始必须为整数，请重新输入");
Response.End();
}
if (!FormatHelper.IsInteger(st_clock))
{
Response.Write("计时节拍必须为整数，请重新输入");
Response.End();
}
#endregion
mg_stationModel model = new mg_stationModel();
model.st_id = NumericParse.StringToInt(st_id);
model.fl_id = NumericParse.StringToInt(fl_id);
model.st_typeid = NumericParse.StringToInt(st_typeid);
model.st_no = st_no;
model.st_name = st_name;
model.st_mac = st_mac;
model.st_mushifile = st_mushifile;
model.st_odsfile = st_odsfile;
model.st_alarmfile = st_alarmfile;
model.st_isfirst = NumericParse.StringToInt(st_isfirst);
model.st_isend = NumericParse.StringToInt(st_isend);
model.st_clock_Start = NumericParse.StringToInt(st_clock_Start);
model.st_clock = NumericParse.StringToInt(st_clock);
string json = mg_StationBLL.saveStation(model);
Response.Write(json);
Response.End();
}
public static mg_stationModel GetStationByMac(string mac)
{
string sql2 = @"
SELECT top 1 [st_id]
,st.[fl_id]
,fl.fl_name
,[st_no]
,[st_name]
,[st_mac]
,[st_typeid]
,p.prop_name [st_typename]
,[st_order]
FROM [mg_station] st
left join mg_FlowLine fl on st.fl_id = fl.fl_id
left join mg_Property p on st.st_typeid=p.prop_id
where st.st_mac='" + mac + @"'
";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null);
if (DataHelper.HasData(dt))
{
mg_stationModel model = new mg_stationModel();
foreach (DataRow row in dt.Rows)
{
model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id"));
model.fl_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "fl_id"));
model.fl_name = DataHelper.GetCellDataToStr(row, "fl_name");
model.st_no = DataHelper.GetCellDataToStr(row, "st_no");
model.st_name = DataHelper.GetCellDataToStr(row, "st_name");
model.st_mac = DataHelper.GetCellDataToStr(row, "st_mac");
model.st_typeid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_typeid"));
model.st_typename = DataHelper.GetCellDataToStr(row, "st_typename");
model.st_order = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_order"));
break;
}
return(model);
}
return(null);
}
public static List <mg_stationModel> QueryStationStnoList()
{
List <mg_stationModel> list = null;
// string sql = @"SELECT [st_id]
// ,[st_no]
// ,[st_name]
// FROM [mg_station] order by st_no";
string sql = @"SELECT [st_id],st_no
FROM [mg_station] ";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null);
if (DataHelper.HasData(dt))
{
list = new List <mg_stationModel>();
foreach (DataRow row in dt.Rows)
{
mg_stationModel model = new mg_stationModel();
model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id"));
model.st_no = DataHelper.GetCellDataToStr(row, "st_no");
list.Add(model);
}
}
return(list);
}
private static string AddStation(mg_stationModel model)
{
int count = mg_StationDAL.AddStation(model);
return(count > 0 ? "true" : "false");
}
public static string saveStation(mg_stationModel model)
{
return(model.st_id == 0 ? AddStation(model) : UpdateStation(model));
}
public static List <mg_stationModel> QueryListForFirstPage(string pagesize, out string total, string fl_id)
{
total = "0";
List <mg_stationModel> list = null;
string queryStr = (!string.IsNullOrEmpty(fl_id) && fl_id != "0") ? " where st.fl_id=" + fl_id : "";
string sql1 = @"select count(st_id) total from [mg_station] st " + queryStr + @" ;";
string sql2 = @"
SELECT top " + pagesize + @" [st_id]
,st.[fl_id]
,fl.fl_name
,[st_no]
,[st_name]
,[st_mac]
,[st_typeid]
,p.prop_name [st_typename]
,[st_order]
,[st_odsfile]
,[st_mushifile]
,[st_QualityAlarm]
,st_isfirst
,CASE st_isfirst WHEN 1 THEN '是' WHEN 0 THEN '否' END AS st_isfirstname
,st_isend
,CASE st_isend WHEN 1 THEN '是' WHEN 0 THEN '否' END AS st_isendname
,[st_clock_Start]
,[st_clock]
FROM [mg_station] st
left join mg_FlowLine fl on st.fl_id = fl.fl_id
left join mg_Property p on st.st_typeid=p.prop_id
" + queryStr + @"
order by st_order
";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql1 + sql2, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = DataHelper.GetCellDataToStr(dt1.Rows[0], "total");
DataTable dt2 = ds.Tables["data"];
list = new List <mg_stationModel>();
foreach (DataRow row in dt2.Rows)
{
mg_stationModel model = new mg_stationModel();
model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id"));
model.fl_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "fl_id"));
model.fl_name = DataHelper.GetCellDataToStr(row, "fl_name");
model.st_no = DataHelper.GetCellDataToStr(row, "st_no");
model.st_name = DataHelper.GetCellDataToStr(row, "st_name");
model.st_mac = DataHelper.GetCellDataToStr(row, "st_mac");
model.st_typeid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_typeid"));
model.st_typename = DataHelper.GetCellDataToStr(row, "st_typename");
model.st_order = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_order"));
model.st_odsfile = DataHelper.GetCellDataToStr(row, "st_odsfile");
model.st_mushifile = DataHelper.GetCellDataToStr(row, "st_mushifile");
string pdf = DataHelper.GetCellDataToStr(row, "st_odsfile");
model.st_odsfilename = (!string.IsNullOrEmpty(pdf)) ? pdf.Substring(pdf.LastIndexOf('/') + 1) : "";
string alarmpdf = DataHelper.GetCellDataToStr(row, "st_QualityAlarm");
model.st_alarmfile = alarmpdf;
model.st_alarmfilename = (!string.IsNullOrEmpty(alarmpdf)) ? alarmpdf.Substring(alarmpdf.LastIndexOf('/') + 1) : "";
model.st_isfirst = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_isfirst"));
model.st_isend = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_isend"));
model.st_isendname = DataHelper.GetCellDataToStr(row, "st_isendname");
model.st_isfirstname = DataHelper.GetCellDataToStr(row, "st_isfirstname");
model.st_clock_Start = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_clock_Start"));
model.st_clock = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_clock"));
list.Add(model);
}
}
return(list);
}