public string GetClassInfo()
{
DataTable dt = Production_Report_BLL.GetClassInfo();
string json = JSONTools.DataTableToJson(dt);
return(json);
}
public static string getTableString(string StartTime, string EndTime, string OrderCode, string StationNo, int PageIndex, out int totalcount)
{
string JsonStr3 = "";
string JsonStr = "";
string neirong = "";
string yee = "";
DateTime date = DateTime.Now;
DataTable ResTable = new DataTable();
if (OrderCode != "" && StationNo != "") //输入订单号，输入工位的时候
{
if (StationNo == "FSA210") //FSA210
{
string SqlStr = @"select a.stationno as StationNo1,d.op_name,g.ItemCaption,case when a.IsQualified = 1 then '合格' else '不合格' end as IsQualified,a.starttime,a.endtime,a.OrderNo from mg_Test_Repair_Item_Record a
left join mg_Operator d on a.operatorid = d.op_id
left join mg_Test_Repair_Item g on a.Repair_ItemID = g.ID
where (a.StartTime > '" + StartTime + "' and a.EndTime < '" + EndTime + "' ";
SqlStr += " and REPLACE(a.OrderNo, CHAR(13) + CHAR(10), '') = '" + OrderCode + "'";
SqlStr += " and a.stationno='FSA210') order by g.ItemCaption, a.OrderNo,a.StartTime ";
ResTable = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, CommandType.Text, SqlStr, null);
if (ResTable.Rows.Count < 1)
{
JsonStr = "{}";
}
else
{
JsonStr = JSONTools.DataTableToJson(ResTable);
}
int number = 1;
neirong = "返修";
yee = "1";
JsonStr3 = "{\"number\":\"" + number + "\",\"neirong\":\"" + neirong + "\",\"date\":\"" + date + "\",\"yee\":\"" + yee + "\",\"OrderNo\":\"" + OrderCode + "\",\"StationNo\":\"" + StationNo + "\",\"data\":" + JsonStr + "}";
}
else //FSA160和FSA170
{
string SqlStr = @"select a.stationno,d.op_name,c.TestCaption,case when a.IsQualified = 1 then '合格' else '不合格' end as IsQualified,a.CreateTime,a.OrderNo,a.TestValue from mg_Test_Part_Record a
left join mg_Test_Part b on a.Test_PartID = b.ID
left join mg_test c on b.TestID = c.id
left join mg_Operator d on a.operatorid = d.op_id
where (a.CreateTime > '" + StartTime + "' and a.CreateTime < '" + EndTime + "' ";
SqlStr += " and REPLACE(a.OrderNo, CHAR(13) + CHAR(10), '') = '" + OrderCode + "'";
if (StationNo == "FSA160") //160内容
{
SqlStr += " and a.stationno='FSA160') order by c.TestCaption, a.OrderNo,a.CreateTime ";
}
else //=1 170内容
{
SqlStr += " and a.stationno='FSA170') order by c.TestCaption,a.OrderNo,a.CreateTime ";
}
ResTable = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, CommandType.Text, SqlStr, null);
if (ResTable.Rows.Count < 1)
{
JsonStr = "{}";
}
else
{
JsonStr = JSONTools.DataTableToJson(ResTable);
}
int number = 1;
neirong = "检测";
yee = "1";
JsonStr3 = "{\"number\":\"" + number + "\",\"neirong\":\"" + neirong + "\",\"date\":\"" + date + "\",\"yee\":\"" + yee + "\",\"OrderNo\":\"" + OrderCode + "\",\"StationNo\":\"" + StationNo + "\",\"data\":" + JsonStr + "}";
}
}
else if (OrderCode != "" && StationNo == "") //输入订单号的情况，工位不输
{
if ((PageIndex - 1) % 3 == 2) //FSA210
{
string SqlStr = @"select a.stationno as StationNo1,d.op_name,g.ItemCaption,case when a.IsQualified = 1 then '合格' else '不合格' end as IsQualified,a.starttime,a.endtime,a.OrderNo from mg_Test_Repair_Item_Record a
left join mg_Operator d on a.operatorid = d.op_id
left join mg_Test_Repair_Item g on a.Repair_ItemID = g.ID
where (a.StartTime > '" + StartTime + "' and a.EndTime < '" + EndTime + "' ";
SqlStr += " and REPLACE(a.OrderNo, CHAR(13) + CHAR(10), '') = '" + OrderCode + "'";
SqlStr += " and a.stationno='FSA210') order by g.ItemCaption,a.OrderNo,a.StartTime ";
ResTable = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, CommandType.Text, SqlStr, null);
if (ResTable.Rows.Count < 1)
{
JsonStr = "{}";
}
else
{
JsonStr = JSONTools.DataTableToJson(ResTable);
}
int number = 3;
neirong = "返修";
yee = "3/3";
StationNo = "FSA210";
JsonStr3 = "{\"number\":\"" + number + "\",\"neirong\":\"" + neirong + "\",\"date\":\"" + date + "\",\"yee\":\"" + yee + "\",\"OrderNo\":\"" + OrderCode + "\",\"StationNo\":\"" + StationNo + "\",\"data\":" + JsonStr + "}";
}
else //FSA160 FSA170
{
string SqlStr = @"select a.stationno,d.op_name,c.TestCaption,case when a.IsQualified = 1 then '合格' else '不合格' end as IsQualified,a.CreateTime,a.OrderNo,a.TestValue from mg_Test_Part_Record a
left join mg_Test_Part b on a.Test_PartID = b.ID
left join mg_test c on b.TestID = c.id
left join mg_Operator d on a.operatorid = d.op_id
where( a.CreateTime > '" + StartTime + "' and a.CreateTime < '" + EndTime + "' ";
SqlStr += " and REPLACE(a.OrderNo, CHAR(13) + CHAR(10), '') = '" + OrderCode + "'";
if ((PageIndex - 1) % 3 == 0) //FSA160
{
SqlStr += " and a.stationno='FSA160') order by c.TestCaption, a.OrderNo,a.CreateTime ";
ResTable = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, CommandType.Text, SqlStr, null);
if (ResTable.Rows.Count < 1)
{
JsonStr = "{}";
}
else
{
JsonStr = JSONTools.DataTableToJson(ResTable);
}
int number = 3;
neirong = "检测";
yee = "1/3";
StationNo = "FSA160";
JsonStr3 = "{\"number\":\"" + number + "\",\"neirong\":\"" + neirong + "\",\"date\":\"" + date + "\",\"yee\":\"" + yee + "\",\"OrderNo\":\"" + OrderCode + "\",\"StationNo\":\"" + StationNo + "\",\"data\":" + JsonStr + "}";
}
else //FSA170
{
SqlStr += " and a.stationno='FSA170') order by c.TestCaption,a.OrderNo,a.CreateTime ";
ResTable = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, CommandType.Text, SqlStr, null);
if (ResTable.Rows.Count < 1)
{
JsonStr = "{}";
}
else
{
JsonStr = JSONTools.DataTableToJson(ResTable);
}
int number = 3;
neirong = "检测";
yee = "2/3";
StationNo = "FSA170";
JsonStr3 = "{\"number\":\"" + number + "\",\"neirong\":\"" + neirong + "\",\"date\":\"" + date + "\",\"yee\":\"" + yee + "\",\"OrderNo\":\"" + OrderCode + "\",\"StationNo\":\"" + StationNo + "\",\"data\":" + JsonStr + "}";
}
}
}
else //只输日期
{
string SqlStr1 = @"select distinct top 1 a.OrderNo from mg_Test_Part_Record a
where a.CreateTime > '" + StartTime + "' and a.CreateTime < '" + EndTime + @"' and a.OrderNo
not in(select distinct top(1*" + (PageIndex - 1) / 3 + @") a.OrderNo from mg_Test_Part_Record a
where (a.CreateTime > '" + StartTime + "' and a.CreateTime < '" + EndTime + "')order by a.OrderNo ) order by a.OrderNo";
string SqlStr2 = "";
object ob = SqlHelper.ExecuteScalar(SqlHelper.SqlConnString, System.Data.CommandType.Text, SqlStr1, null); //获取所需的一个订单号
OrderCode = (ob == null) ? "" : ob.ToString();
if ((PageIndex - 1) % 3 == 2) //FSA210
{
string SqlStr = @"select a.stationno as StationNo1,d.op_name,g.ItemCaption,case when a.IsQualified = 1 then '合格' else '不合格' end as IsQualified,a.starttime,a.endtime,a.OrderNo from mg_Test_Repair_Item_Record a
left join mg_Operator d on a.operatorid = d.op_id
left join mg_Test_Repair_Item g on a.Repair_ItemID = g.ID
where (a.StartTime > '" + StartTime + "' and a.EndTime < '" + EndTime + "' ";
if (!string.IsNullOrEmpty(OrderCode))
{
SqlStr += " and REPLACE(a.OrderNo, CHAR(13) + CHAR(10), '') = '" + OrderCode + "'";
}
SqlStr += " and a.stationno='FSA210') order by g.ItemCaption,a.OrderNo,a.StartTime ";
ResTable = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, CommandType.Text, SqlStr, null);
if (ResTable.Rows.Count < 1)
{
JsonStr = "{}";
}
else
{
JsonStr = JSONTools.DataTableToJson(ResTable);
}
if (JsonStr == "]")
{
JsonStr = "";
}
SqlStr2 = "select count(distinct OrderNo) from mg_Test_Repair_Item_Record where (StartTime > '" + StartTime + "' and EndTime < '" + EndTime + "' ) ";
int number = 0;
ob = SqlHelper.ExecuteScalar(SqlHelper.SqlConnString, System.Data.CommandType.Text, SqlStr2, null);
if (ob == null)
{
number = 0;
}
else
{
number = Convert.ToInt32(ob.ToString());
}
neirong = "返修";
yee = "3/3";
StationNo = "FSA210";
JsonStr3 = "{\"number\":\"" + number * 3 + "\",\"neirong\":\"" + neirong + "\",\"date\":\"" + date + "\",\"yee\":\"" + yee + "\",\"OrderNo\":\"" + OrderCode + "\",\"StationNo\":\"" + StationNo + "\",\"data\":" + JsonStr + "}";
}
else
{
string SqlStr = @"select a.stationno,d.op_name,c.TestCaption,case when a.IsQualified = 1 then '合格' else '不合格' end as IsQualified,a.CreateTime,a.OrderNo,a.TestValue from mg_Test_Part_Record a
left join mg_Test_Part b on a.Test_PartID = b.ID
left join mg_test c on b.TestID = c.id
left join mg_Operator d on a.operatorid = d.op_id
where( a.CreateTime > '" + StartTime + "' and a.CreateTime < '" + EndTime + "' ";
SqlStr += " and REPLACE(a.OrderNo, CHAR(13) + CHAR(10), '') = '" + OrderCode + "'";
if ((PageIndex - 1) % 3 == 0) //FSA160
{
SqlStr += " and a.stationno='FSA160') order by c.TestCaption, a.OrderNo,a.CreateTime ";
ResTable = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, CommandType.Text, SqlStr, null);
if (ResTable.Rows.Count < 1)
{
JsonStr = "{}";
}
else
{
JsonStr = JSONTools.DataTableToJson(ResTable);
}
//if (JsonStr == "]")
//{
// JsonStr = "";
//}
SqlStr2 = "select count(distinct OrderNo) from mg_Test_Part_Record where (CreateTime > '" + StartTime + "' and CreateTime < '" + EndTime + "' ) ";
int number = 0;
ob = SqlHelper.ExecuteScalar(SqlHelper.SqlConnString, System.Data.CommandType.Text, SqlStr2, null);
if (ob == null)
{
number = 0;
}
else
{
number = Convert.ToInt32(ob.ToString());
}
neirong = "检测";
yee = "1/3";
StationNo = "FSA160";
JsonStr3 = "{\"number\":\"" + number * 3 + "\",\"neirong\":\"" + neirong + "\",\"date\":\"" + date + "\",\"yee\":\"" + yee + "\",\"OrderNo\":\"" + OrderCode + "\",\"StationNo\":\"" + StationNo + "\",\"data\":" + JsonStr + "}";
}
else //FSA170
{
SqlStr += " and a.stationno='FSA170') order by c.TestCaption, a.OrderNo,a.CreateTime ";
ResTable = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, CommandType.Text, SqlStr, null);
if (ResTable.Rows.Count < 1)
{
JsonStr = "{}";
}
else
{
JsonStr = JSONTools.DataTableToJson(ResTable);
}
//if (JsonStr == "]")
//{
// JsonStr = "\"\"";
//}
SqlStr2 = "select count(distinct OrderNo) from mg_Test_Part_Record where (CreateTime > '" + StartTime + "' and CreateTime < '" + EndTime + "' ) ";
int number = 0;
ob = SqlHelper.ExecuteScalar(SqlHelper.SqlConnString, System.Data.CommandType.Text, SqlStr2, null);
if (ob == null)
{
number = 0;
}
else
{
number = Convert.ToInt32(ob.ToString());
}
neirong = "检测";
yee = "2/3";
StationNo = "FSA170";
JsonStr3 = "{\"number\":\"" + number * 3 + "\",\"neirong\":\"" + neirong + "\",\"date\":\"" + date + "\",\"yee\":\"" + yee + "\",\"OrderNo\":\"" + OrderCode + "\",\"StationNo\":\"" + StationNo + "\",\"data\":" + JsonStr + "";
}
}
}
totalcount = ResTable.Rows.Count;
return(JsonStr3);
}
