public static List <mg_BOMTestGroupModel> QueryListForPaging(string page, string pagesize, out string total)
{
total = "0";
List <mg_BOMTestGroupModel> list = null;
string sql1 = @"select count(ID) total from [mg_Test_Group];";
string sql2 = @" SELECT top " + pagesize + @" ID gid
,GroupName
FROM [mg_Test_Group]
where ID < (
select top 1 ID from
(select top ((" + page + @"-1)*" + pagesize + @") ID from [mg_Test_Group] where ID is not null order by ID desc )t
order by ID )
order by ID desc
";
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_BOMTestGroupModel>();
foreach (DataRow row in dt2.Rows)
{
mg_BOMTestGroupModel model = new mg_BOMTestGroupModel();
model.group_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "gid"));
model.groupname = DataHelper.GetCellDataToStr(row, "GroupName");
list.Add(model);
}
}
return(list);
}
/// <summary>
/// 获取用户菜单列表
/// </summary>
/// <param name="menutitle"></param>
/// <returns></returns>
public static List <Sys_Menu> GetUserMenuList(string menutitle, string currentuser)
{
List <Sys_Menu> menulist = new List <Sys_Menu>();
string sql = "";
using (var conn = new SqlConnection(SqlHelper.SqlConnString))
{
if (string.IsNullOrEmpty(menutitle))
{
sql = "select MenuNo,MenuName,MenuAddr,ParentNo,MenuTag,Image from View_Sys_UserLimitInfo where ParentNo='0000' and UserNo= '" + currentuser + "' order by MenuNo";
}
else
{
string sql1 = @"select MenuNo,MenuName,MenuAddr,ParentNo,MenuTag,Image from View_Sys_UserLimitInfo where ParentNo in (select MenuNo from Sys_MenuInfo where MenuName='{0}' and ParentNo='0000') and UserNo= '" + currentuser + "' order by MenuNo";
sql = string.Format(sql1, menutitle);
}
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null);
foreach (DataRow row in dt.Rows)
{
Sys_Menu model = new Sys_Menu();
//model.ID = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "ID"));
model.MenuNo = DataHelper.GetCellDataToStr(row, "MenuNo");
model.MenuName = DataHelper.GetCellDataToStr(row, "MenuName");
model.MenuAddr = DataHelper.GetCellDataToStr(row, "MenuAddr");
model.ParentNo = DataHelper.GetCellDataToStr(row, "ParentNo");
model.MenuTag = DataHelper.GetCellDataToStr(row, "MenuTag");
model.Image = DataHelper.GetCellDataToStr(row, "Image");
menulist.Add(model);
}
return(menulist);
}
}
public static DataTable getTable(int Pagesize, int pageIndex, int StartIndex, int EndIndex, string SortFlag, string sortOrder, string wherestr, out int total)
{
if (string.IsNullOrEmpty(SortFlag))
{
SortFlag = "ID";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "asc";
}
StringBuilder commandText = new StringBuilder();
commandText.Append("SELECT TOP " + Pagesize + " ID,订单类型,订单编码,下单时间,车型ID,车型,订单状态,前排故障历史,前排是否修复,主驾,主驾气囊,副驾,副驾气囊,[40%] 百分之40,[60%] 百分之60,[100%] 百分之100,卷收器,MainOrderID,VINNumber,回写SAP FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + SortFlag + " " + sortOrder + " ) AS RowNumber,* FROM ViewOrders where 1=1 ");
commandText.Append(wherestr);//这里修改条件语句
commandText.Append(" ) AS T WHERE RowNumber >= " + StartIndex + " and RowNumber <= " + EndIndex);
string query_sql = commandText.ToString();
string count_sql = "select count(*) as total from ViewOrders where 1 = 1 " + wherestr;
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable dt2 = ds.Tables["data"];
return(dt2);
}
else
{
total = 0;
return(null);
}
}
public static List <mg_PoInspectItemModel> QueryListForPaging(string page, string pagesize, out string total)
{
total = "0";
List <mg_PoInspectItemModel> list = null;
string sql1 = @"select count(ID) total from [mg_PointInspection_Item];";
string sql2 = @" SELECT top " + pagesize + @" ID pid
,PI_Item
,PI_ItemDescribe
FROM [mg_PointInspection_Item]
where ID not in (
select top ((" + page + @"-1)*" + pagesize + @") ID from [mg_PointInspection_Item]
order by ID)
order by ID
";
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_PoInspectItemModel>();
foreach (DataRow row in dt2.Rows)
{
mg_PoInspectItemModel model = new mg_PoInspectItemModel();
model.pi_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "pid"));
model.piitem = DataHelper.GetCellDataToStr(row, "PI_Item");
model.piitemdescribe = DataHelper.GetCellDataToStr(row, "PI_ItemDescribe");
list.Add(model);
}
}
return(list);
}
public static DataTable getTableExcel(string SortFlag, string sortOrder, string wherestr, out int total)
{
if (string.IsNullOrEmpty(SortFlag))
{
SortFlag = "ID";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "asc";
}
string query_sql = "SELECT ID,订单类型,订单编码,下单时间,车型ID,车型,订单状态,前排故障历史,前排是否修复,主驾,主驾气囊,副驾,副驾气囊,[40%] 百分之40,[60%] 百分之60,[100%] 百分之100,卷收器,MainOrderID,VINNumber,回写SAP FROM ViewOrders WHERE 1=1 " + wherestr + "order by " + SortFlag + " " + sortOrder;
string count_sql = "select count(*) as total from ViewOrders where 1 = 1 " + wherestr;
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable resTable2 = ds.Tables["data"];
for (int i = 0; i < resTable2.Rows.Count; i++)
{
resTable2.Rows[i]["主驾"] = "\t" + resTable2.Rows[i]["主驾"].ToString();
resTable2.Rows[i]["副驾"] = "\t" + resTable2.Rows[i]["副驾"].ToString();
resTable2.Rows[i]["百分之40"] = "\t" + resTable2.Rows[i]["百分之40"].ToString();
resTable2.Rows[i]["百分之60"] = "\t" + resTable2.Rows[i]["百分之60"].ToString();
resTable2.Rows[i]["百分之100"] = "\t" + resTable2.Rows[i]["百分之100"].ToString();
}
return(resTable2);
}
else
{
total = 0;
return(null);
}
}
public static DataTable getTableExcel(int PageSize, int pageIndex, int StartIndex, int EndIndex, string SortFlag, string sortOrder, string wherestr, out int total)
{
if (string.IsNullOrEmpty(SortFlag))
{
SortFlag = "ID";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "asc";
}
StringBuilder commandText = new StringBuilder();
commandText.Append("SELECT * FROM mg_Report_FTT where 1=1 ");
commandText.Append(wherestr);//这里修改条件语句
string query_sql = commandText.ToString();
string count_sql = "select count(*) as total from mg_Report_FTT where 1 = 1 " + wherestr;
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable dt2 = ds.Tables["data"];
return(dt2);
}
else
{
total = 0;
return(null);
}
}
public static DataTable getList(int PageSize, int StartIndex, int EndIndex, string SortFlag, string sortOrder, string wherestr, out int total)
{
if (string.IsNullOrEmpty(SortFlag))
{
SortFlag = "st_no,step_startTime";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "asc";
}
List <mg_sys_log> list = new List <mg_sys_log>();
string query_sql = " select * from(select row_number() over(order by " + SortFlag + " " + sortOrder + " ) as rowid,report.* from View_mg_sys_log report where 1 = 1 " + wherestr + ") as Results where rowid >=" + StartIndex + " and rowid <=" + EndIndex + " ";
if (EndIndex == -1)
{
query_sql = " select * from(select row_number() over(order by " + SortFlag + " " + sortOrder + " ) as rowid,report.* from View_mg_sys_log report where 1 = 1 " + wherestr + ") as Results where rowid >=" + StartIndex + " ";
}
string count_sql = "select count(*) as total from View_mg_sys_log where 1 = 1 " + wherestr;
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable dt2 = ds.Tables["data"];
return(dt2);
}
else
{
total = 0;
return(null);
}
}
public static List <mg_MailModel> QueryListForPaging(string page, string pagesize, out string total)
{
total = "0";
List <mg_MailModel> list = null;
string sql1 = @"select count(ID) total from [mg_MailConfig];";
string sql2 = @"
SELECT top " + pagesize + @" ID mid
,ReceiptType
,case ReceiptType
--when 1 then 'LineUp'
--when 2 then 'Delgit'
-- when 3 then '回冲' end as ReceiptTypeName
when 1 then 'LineUpTxt加载失败'
when 2 then 'LineUp订单的ProductNo在MES系统中不匹配或为空'
when 3 then 'DelJetTxt加载失败'
when 4 then 'DelJet订单的在LineUp订单中不匹配或对应的ProductNo不匹配'
when 5 then 'DelJet订单自动拆单失败'
when 6 then 'SAP手动插单Txt加载失败'
when 7 then 'SAP手动插单订单自动拆单失败'
when 8 then 'SAP手动插单订单的ProductNo在MES系统中不匹配或为空'
when 9 then 'DelJet订单的SEQNR不连续'
when 10 then 'DelJet缓存文件夹写入失败'
else ''
end as ReceiptTypeName
,MailRecipient
,RecipientType
,case RecipientType
when 1 then '收件人'
when 2 then '抄送人' end as RecipientTypeName
FROM [mg_MailConfig] t1
where t1.ID not in (
select top ((" + page + @"-1)*" + pagesize + @") ID from [mg_MailConfig] order by ID desc)
order by ID desc ";
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_MailModel>();
foreach (DataRow row in dt2.Rows)
{
mg_MailModel model = new mg_MailModel();
model.mail_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "mid"));
model.ReceiptType = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "ReceiptType"));
model.ReceiptTypeName = DataHelper.GetCellDataToStr(row, "ReceiptTypeName");
model.MailRecipient = DataHelper.GetCellDataToStr(row, "MailRecipient");
model.RecipientType = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "RecipientType"));
model.RecipientTypeName = DataHelper.GetCellDataToStr(row, "RecipientTypeName");
list.Add(model);
}
}
return(list);
}
public static string getList(int PageSize, int StartIndex, int EndIndex, string SortFlag, string sortOrder, string wherestr)
{
if (string.IsNullOrEmpty(SortFlag))
{
SortFlag = " st_no,step_startTime";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "asc";
}
List <mg_sys_log> list = new List <mg_sys_log>();
int total = 0;
string query_sql = " select * from(select row_number() over(order by " + SortFlag + " " + sortOrder + " ) as rowid,report.* from View_mg_sys_log report where 1 = 1 " + wherestr + ") as Results where rowid >=" + StartIndex + " and rowid <=" + EndIndex + " ";
string count_sql = "select count(*) as total from View_mg_sys_log where 1 = 1 " + wherestr;
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable dt2 = ds.Tables["data"];
foreach (DataRow row in dt2.Rows)
{
mg_sys_log model = new mg_sys_log();
model.sys_id = DataHelper.GetCellDataToStr(row, "sys_id");
model.op_id = DataHelper.GetCellDataToStr(row, "op_id");
model.op_name = DataHelper.GetCellDataToStr(row, "op_name");
model.fl_id = DataHelper.GetCellDataToStr(row, "fl_id");
model.fl_name = DataHelper.GetCellDataToStr(row, "fl_name");
model.st_id = DataHelper.GetCellDataToStr(row, "st_id");
model.st_no = DataHelper.GetCellDataToStr(row, "st_no");
model.PartOrderID = DataHelper.GetCellDataToStr(row, "PartOrderID");
model.or_no = DataHelper.GetCellDataToStr(row, "or_no");
model.part_no = DataHelper.GetCellDataToStr(row, "part_no");
model.step_order = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "step_order"));
model.step_startTime = DataHelper.GetCellDataToStr(row, "step_startTime");
model.step_endTime = DataHelper.GetCellDataToStr(row, "step_endTime");
model.step_duringtime = DataHelper.GetCellDataToStr(row, "step_duringtime");
model.AngleResult = DataHelper.GetCellDataToStr(row, "AngleResult");
model.TorqueResult = DataHelper.GetCellDataToStr(row, "TorqueResult");
model.scanCode = DataHelper.GetCellDataToStr(row, "scanCode");
model.scanResult = DataHelper.GetCellDataToStr(row, "scanResult");
model.sys_desc = DataHelper.GetCellDataToStr(row, "sys_desc");
model.ScrewCount = DataHelper.GetCellDataToStr(row, "ScrewCount");
model.MenderName = DataHelper.GetCellDataToStr(row, "MenderName");
model.ReviseTime = DataHelper.GetCellDataToStr(row, "ReviseTime");
list.Add(model);
}
}
DataListModel <mg_sys_log> allmodel = new DataListModel <mg_sys_log>();
allmodel.total = total.ToString();
allmodel.rows = list;
string jsonStr = JSONTools.ScriptSerialize <DataListModel <mg_sys_log> >(allmodel);
return(jsonStr);
}
public static List <mg_TestRepairItemModel> QueryListForPaging(string page, string pagesize, out string total)
{
total = "0";
List <mg_TestRepairItemModel> list = null;
string sql1 = @"select count(ID) total from [mg_Test_Repair_Item];";
string sql2 = @" SELECT top " + pagesize + @" ID trid
,ItemCaption
,ItemType
,case ItemType
when 1 then '前排'
when 2 then'后排靠背'
when 3 then'后排坐垫'
end as ItemTypeName
,Sorting
,IsUseing
,case IsUseing
when 1 then '在用'
when 0 then '停用'
end as IsUseingName
FROM [mg_Test_Repair_Item]
where ID not in (
select top ((" + page + @"-1)*" + pagesize + @") ID from [mg_Test_Repair_Item]
order by ID)
order by ID
";
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_TestRepairItemModel>();
foreach (DataRow row in dt2.Rows)
{
mg_TestRepairItemModel model = new mg_TestRepairItemModel();
model.tr_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "trid"));
model.ItemCaption = DataHelper.GetCellDataToStr(row, "ItemCaption");
model.Sorting = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "Sorting"));
model.IsUseingName = DataHelper.GetCellDataToStr(row, "IsUseingName");
model.IsUseing = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "IsUseing"));
model.ItemType = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "ItemType"));
model.ItemTypeName = DataHelper.GetCellDataToStr(row, "ItemTypeName");
list.Add(model);
}
}
return(list);
}
public static List <mg_PiistationModel> QueryListForPaging(string page, string pagesize, out string total)
{
total = "0";
List <mg_PiistationModel> list = null;
string sql1 = @"select count(ID) total from [mg_PointInspection_Item_StationNo];";
string sql2 = @"
with data as
(
select t1.ID,t1.PI_Item,t1.PI_ItemDescribe from mg_PointInspection_Item t1
)
SELECT top " + pagesize + @" p.ID pid
,p.PI_ID
,StationNO
,[Sorting]
,p2.PI_Item PI_Item
,STUFF((SELECT ','+cast (t1.ID as varchar) from data t1 where t1.ID=p.PI_ID for xml path('')),1,1,'') piIDs
FROM [mg_PointInspection_Item_StationNo] p
left join mg_PointInspection_Item p2 on p.PI_ID = p2.ID
where p.ID not in (
select top ((" + page + @"-1)*" + pagesize + @") ID from [mg_PointInspection_Item_StationNo] order by ID desc)
order by p.ID desc ";
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_PiistationModel>();
foreach (DataRow row in dt2.Rows)
{
mg_PiistationModel model = new mg_PiistationModel();
model.ps_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "pid"));
model.station_no = DataHelper.GetCellDataToStr(row, "StationNO");
model.sorting = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "Sorting"));
model.piitem = DataHelper.GetCellDataToStr(row, "PI_Item");
model.piIDs = DataHelper.GetCellDataToStr(row, "piIDs");
//model.allpartNOs = DataHelper.GetCellDataToStr(row, "allpartNOs");
list.Add(model);
}
}
return(list);
}
public static List <mg_ProductModel> QueryListForPaging(string page, string pagesize, out string total)
{
total = "0";
List <mg_ProductModel> list = null;
string sql1 = @"select count(ID) total from [mg_Product];";
string sql2 = @" SELECT top " + pagesize + @" ID pid
,ProductNo
,ProductName
,ProductDesc
,ProductType
,ProductTypeName
,IsUseing
,case IsUseing
when 0 then'停用'
when 1 then'在用' end as IsUseingName
FROM [mg_Product]
where ID not in
(select top ((" + page + @"-1)*" + pagesize + @") ID from [mg_Product] order by ID desc)
order by ID desc
";
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_ProductModel>();
foreach (DataRow row in dt2.Rows)
{
mg_ProductModel model = new mg_ProductModel();
model.p_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "pid"));
model.ProductNo = DataHelper.GetCellDataToStr(row, "ProductNo");
model.ProductName = DataHelper.GetCellDataToStr(row, "ProductName");
model.ProductDesc = DataHelper.GetCellDataToStr(row, "ProductDesc");
model.ProductType = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "ProductType"));
model.ProductTypeName = DataHelper.GetCellDataToStr(row, "ProductTypeName");
model.IsUseingName = DataHelper.GetCellDataToStr(row, "IsUseingName");
model.IsUseing = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "IsUseing"));
list.Add(model);
}
}
return(list);
}
public static List <mg_TestPartModel> QueryListForPaging(string page, string pagesize, out string total)
{
total = "0";
List <mg_TestPartModel> list = null;
string sql1 = @"select count(ID) total from [mg_Test_Part];";
string sql2 = @"
SELECT top " + pagesize + @" t1.ID pid
,p.part_id part_id
,p.part_name part_name
,t1.StationNO StationNO
,t2.TestCaption TestCaption
,STUFF((SELECT ','+cast (t3.ID as varchar) from (select t4.ID,t4.TestCaption from mg_Test t4) t3
where t1.TestID=ID for xml path('')),1,1,'') tIDs
FROM [mg_Test_Part] t1
left join mg_Test t2 on t1.TestID = t2.ID left join mg_part p on p.part_id= t1.partID
where t1.ID not in (
select top ((" + page + @"-1)*" + pagesize + @") ID from [mg_Test_Part] order by ID desc)
order by t1.ID desc ";
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_TestPartModel>();
foreach (DataRow row in dt2.Rows)
{
mg_TestPartModel model = new mg_TestPartModel();
model.p_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "pid"));
model.partid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "part_id"));
model.partname = DataHelper.GetCellDataToStr(row, "part_name");
model.stationno = DataHelper.GetCellDataToStr(row, "StationNO");
model.testcaption = DataHelper.GetCellDataToStr(row, "TestCaption");
model.tIDS = DataHelper.GetCellDataToStr(row, "tIDs");
//model.allpartNOs = DataHelper.GetCellDataToStr(row, "allpartNOs");
list.Add(model);
}
}
return(list);
}
public static List <mg_BOMTestGroupModel> queryGroupidForBOMTest()
{
List <mg_BOMTestGroupModel> list = null;
string sql = @"SELECT [ID],[GroupName] FROM [mg_Test_Group] order by GroupName ";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null);
if (DataHelper.HasData(dt))
{
list = new List <mg_BOMTestGroupModel>();
foreach (DataRow row in dt.Rows)
{
mg_BOMTestGroupModel model = new mg_BOMTestGroupModel();
model.group_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "ID"));
model.groupname = DataHelper.GetCellDataToStr(row, "groupname");
list.Add(model);
}
}
return(list);
}
public static List <mg_TestModel> QueryTestList()
{
List <mg_TestModel> list = null;
string sql = @"SELECT [ID],[TestCaption] FROM [mg_Test] ";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null);
if (DataHelper.HasData(dt))
{
list = new List <mg_TestModel>();
foreach (DataRow row in dt.Rows)
{
mg_TestModel model = new mg_TestModel();
model.t_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "ID"));
model.testcaption = DataHelper.GetCellDataToStr(row, "TestCaption");
list.Add(model);
}
}
return(list);
}
public static List <mg_DepartmentModel> QueryDepartmentsForUser()
{
List <mg_DepartmentModel> list = null;
string sql = @"SELECT [dep_id],[dep_name] FROM [Sys_DeptInfo] order by dep_name ";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null);
if (DataHelper.HasData(dt))
{
list = new List <mg_DepartmentModel>();
foreach (DataRow row in dt.Rows)
{
mg_DepartmentModel model = new mg_DepartmentModel();
model.dep_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "dep_id"));
model.dep_name = DataHelper.GetCellDataToStr(row, "dep_name");
list.Add(model);
}
}
return(list);
}
/// <summary>
/// 查询全部宣传口号
/// </summary>
/// <returns></returns>
public static List <Andon_Taglines> QueryAndonTaglinesList()
{
string sql = "SELECT ID,TaglinesType,TaglinesText FROM Andon_Taglines";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
List <Andon_Taglines> list = new List <Andon_Taglines>();
foreach (DataRow row in dt.Rows)
{
Andon_Taglines model = new Andon_Taglines();
model.ID = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "ID"));
model.TaglinesType = DataHelper.GetCellDataToStr(row, "TaglinesType");
model.TaglinesText = DataHelper.GetCellDataToStr(row, "TaglinesText");
list.Add(model);
}
return(list);
}
public static List <mg_FlowlingModel> queryFlowLineidForPart()
{
List <mg_FlowlingModel> list = null;
string sql = @"SELECT [fl_id],[fl_name],[FlowFlag] FROM [mg_FlowLine] order by fl_name ";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null);
if (DataHelper.HasData(dt))
{
list = new List <mg_FlowlingModel>();
foreach (DataRow row in dt.Rows)
{
mg_FlowlingModel model = new mg_FlowlingModel();
model.fl_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "fl_id"));
model.fl_name = DataHelper.GetCellDataToStr(row, "fl_name");
model.flowflag = DataHelper.GetCellDataToStr(row, "FlowFlag");
list.Add(model);
}
}
return(list);
}
public static List <mg_FlowlingModel> QueryListForPaging(string page, string pagesize, out string total)
{
total = "0";
List <mg_FlowlingModel> list = null;
string sql1 = @"select count(fl_id) total from [mg_FlowLine];";
string sql2 = @" SELECT top " + pagesize + @" fl_id fid
,fl_name
,FlowLineType
,FlowFlag
,case FlowLineType
when 1 then '前排'
when 2 then '后排' end as FLTName
FROM [mg_FlowLine]
where fl_id not in
(select top ((" + page + @"-1)*" + pagesize + @") fl_id from [mg_FlowLine] order by fl_id desc)
order by fl_name
";
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_FlowlingModel>();
foreach (DataRow row in dt2.Rows)
{
mg_FlowlingModel model = new mg_FlowlingModel();
model.fl_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "fid"));
model.fl_name = DataHelper.GetCellDataToStr(row, "fl_name");
model.fltname = DataHelper.GetCellDataToStr(row, "FLTName");
model.flowflag = DataHelper.GetCellDataToStr(row, "FlowFlag");
model.flowlinetype = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "flowlinetype"));
list.Add(model);
}
}
return(list);
}
public static ResultMsg_User EditPsw(mg_userModel dataEntity, ResultMsg_User result)
{
try
{
string strSql = " SELECT COUNT(1) AS SM FROM [Sys_UserInfo] WHERE Lower(user_name) = '" + dataEntity.user_name.ToLower().Trim() + "' and user_pwd='" + dataEntity.user_pwd + "'";
DataTable dt = new DataTable();
dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, CommandType.Text, strSql, null);
int num = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt.Rows[0], "SM"));
if (dt != null && dt.Rows.Count > 0 && num > 0)
{
result.result = "";
result.msg = "";
}
else
{
result.result = "failed";
result.msg = "原密码不正确!";
}
if (result.result == "")
{
strSql = "update Sys_UserInfo set user_pwd='" + dataEntity.user_NewPassword + "',lasteditpwdtime=getdate() where Lower(user_name)='" + dataEntity.user_name.ToLower() + "'";
int re = SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, CommandType.Text, strSql, null);
if (re == 1)
{
result.result = "success";
result.msg = "修改密码成功!";
}
}
}
catch (Exception ex)
{
result.result = "failed";
result.msg = "保存失败! \n" + ex.Message;
}
return(result);
}
public static DataTable getTableExcel(int PageSize, int pageIndex, int StartIndex, int EndIndex, string SortFlag, string sortOrder, string wherestr, out int total)
{
if (string.IsNullOrEmpty(SortFlag))
{
SortFlag = "ID";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "asc";
}
StringBuilder commandText = new StringBuilder();
commandText.Append("select distinct a.ID,a.StationNO,d.op_name,c.PI_Item,case when a.IsPass = 1 then '合格' when a.IsPass = 0 then '不合格' else '未点检' end as IsPass,a.CreateTime from mg_PointInspection_Item_Value a left join mg_PointInspection_Item_StationNo b on a.PIS_ID = b.ID left join mg_PointInspection_Item c on b.PI_ID = c.ID left join mg_Operator d on a.OperatorID = d.op_id WHERE 1=1 ");
commandText.Append(wherestr);//这里修改条件语句
commandText.Append(" order by a." + SortFlag + " " + sortOrder);
string query_sql = commandText.ToString();
StringBuilder commandText1 = new StringBuilder();
commandText1.Append("select distinct a.ID,a.StationNO,d.op_name,c.PI_Item,case when a.IsPass = 1 then '合格' when a.IsPass = 0 then '不合格' else '未点检' end as IsPass,a.CreateTime from mg_PointInspection_Item_Value a left join mg_PointInspection_Item_StationNo b on a.PIS_ID = b.ID left join mg_PointInspection_Item c on b.PI_ID = c.ID left join mg_Operator d on a.OperatorID = d.op_id WHERE 1=1 ");
commandText1.Append(wherestr);//这里修改条件语句
string count_sql = "select count(*) as total from (" + commandText1.ToString() + " ) result ";
// string count_sql = "select count(*) as total from mg_PointInspection_Item_Value a left join mg_PointInspection_Item_StationNo b on a.PIS_ID = b.PI_ID left join mg_PointInspection_Item c on b.PI_ID = c.ID left join mg_Operator d on a.OperatorID = d.op_id WHERE 1=1 " + wherestr;
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable dt2 = ds.Tables["data"];
return(dt2);
}
else
{
total = 0;
return(null);
}
}
public static List <mg_FlowlingModel> QueryListForFirstPage(string pagesize, out string total)
{
total = "0";
List <mg_FlowlingModel> list = null;
string sql1 = @"select count(fl_id) total from [mg_FlowLine];";
string sql2 = @" SELECT top " + pagesize + @" fl_id fid
,fl_name
,FlowLineType
,FlowFlag
,FlowLineType
FROM [mg_FlowLine]
order by fl_name
";
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_FlowlingModel>();
foreach (DataRow row in dt2.Rows)
{
mg_FlowlingModel model = new mg_FlowlingModel();
model.fl_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "fid"));
model.fl_name = DataHelper.GetCellDataToStr(row, "fl_name");
model.flowlinetype = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "FlowLineType"));
List <mg_PropertyModel> Propertylist = DAL.mg_PropertyDAL.queryJSONStringByPropertyType1(DataHelper.GetCellDataToStr(row, "FlowLineType")).ToList();
model.fltname = Propertylist[0].prop_name;
model.flowflag = DataHelper.GetCellDataToStr(row, "FlowFlag");
model.flowlinetype = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "FlowLineType"));
list.Add(model);
}
}
return(list);
}
/// <summary>
/// 获取菜单树
/// </summary>
/// <returns></returns>
public static List <MenuTree> GetMenuTree()
{
List <MenuTree> menutree = new List <MenuTree>();
string sql = "";
using (var conn = new SqlConnection(SqlHelper.SqlConnString))
{
sql = "select MenuNo,MenuName,ParentNo from Sys_MenuInfo where ParentNo='0000' order by MenuNo";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null);
foreach (DataRow row in dt.Rows)
{
MenuTree model = new MenuTree();
List <MenuTree> childrenmenutree = new List <MenuTree>();
model.id = DataHelper.GetCellDataToStr(row, "MenuNo");
model.text = DataHelper.GetCellDataToStr(row, "MenuName");
model.state = "closed";
string childrensql = @"select MenuNo,MenuName,ParentNo from Sys_MenuInfo where ParentNo={0}";
DataTable childrendt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, string.Format(childrensql, model.id), null);
foreach (DataRow childrenrow in childrendt.Rows)
{
childrenmenutree.Add(new MenuTree
{
id = DataHelper.GetCellDataToStr(childrenrow, "MenuNo"),
text = DataHelper.GetCellDataToStr(childrenrow, "MenuName")
});
model.children = childrenmenutree;
}
menutree.Add(model);
}
return(menutree);
}
}
public static DataListModel<Production_AlarmModel> GetListNew( string StartTime,int page, int pagesize, string sidx, string sord, string Where)
{
List<Production_AlarmModel> modelList = new List<Production_AlarmModel>();
List<Production_AlarmModel> footerList = new List<Production_AlarmModel>();
DataListModel<Production_AlarmModel> modeldata = new DataListModel<Production_AlarmModel>();
int returnValue = 0;
int total = 0;
int StartIndex = (page - 1) * pagesize + 1;
int EndIndex = page * pagesize;
string SortFlag = sidx;
string sortOrder = sord;
string wherestr = Where;
SqlParameter[] sqlPara = new SqlParameter[4];
sqlPara[0] = new SqlParameter("@start_time", StartTime);
sqlPara[1] = new SqlParameter("@end_time", StartTime);
sqlPara[2] = new SqlParameter("@start_index", StartIndex);
sqlPara[3] = new SqlParameter("@end_index", EndIndex);
DataSet ds = SqlHelper.RunProcedureTables(SqlHelper.SqlConnString, "Proc_Rpt_AlarmDly", sqlPara, new string[] { "data", "footer", "count" });
if (DataHelper.HasData(ds))
{
DataTable dt2 = ds.Tables["data"];
total = dt2.Rows.Count;
DataTable footer = ds.Tables["footer"];
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
foreach (DataRow row in dt2.Rows)
{
Production_AlarmModel model = new Production_AlarmModel();
// model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id"));
// model.product_date = DataHelper.GetCellDataToStr(row, "product_date").Substring(0, 10);
model.stationNo = DataHelper.GetCellDataToStr(row, "stationNo");
model.stationName = DataHelper.GetCellDataToStr(row, "stationName");
model.material_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "material_num"));
model.production_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "production_num"));
model.maintenance_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "maintenance_num"));
model.quality_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "quality_num"));
model.overcycle_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "overcycle_num"));
model.total_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "total_num"));
modelList.Add(model);
}
foreach (DataRow row in footer.Rows)
{
Production_AlarmModel model = new Production_AlarmModel();
//model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id"));
//model.product_date = DataHelper.GetCellDataToStr(row, "product_date");
model.stationNo = DataHelper.GetCellDataToStr(row, "stationNo");
model.stationName = DataHelper.GetCellDataToStr(row, "stationName");
model.material_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "material_num"));
model.production_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "production_num"));
model.maintenance_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "maintenance_num"));
model.quality_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "quality_num"));
model.overcycle_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "overcycle_num"));
model.total_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "total_num"));
footerList.Add(model);
}
DataListModel<Production_AlarmModel> allmodel = new DataListModel<Production_AlarmModel>();
allmodel.total = total.ToString();
allmodel.rows = modelList;
allmodel.footer = footerList;
return allmodel;
}
else
{
total = 0;
return null;
}
}
public static List <mg_partModel> QueryListForPaging(string page, string pagesize, out string total)
{
total = "0";
List <mg_partModel> list = null;
string sql1 = @"select count(part_id) total from [mg_part];";
string sql2 = @"SELECT top " + pagesize + @" a.part_id pid
,a.part_no part_no
,a.part_name part_name
,a.part_desc part_desc
,a.PartType
,case a.PartType
when 1 then '主驾靠背'
when 2 then '主驾坐垫'
when 3 then '主驾总'
when 4 then '副驾靠背'
when 5 then '副驾坐垫'
when 6 then '副驾总'
when 7 then '后排40%'
when 8 then '后排60%'
when 9 then '后排100%'
end as PartTypeName
,b.prop_id Propid
,b.prop_name PropName
,c.fl_id
,c.fl_name fl_name
,d.ID ProductID
,d.ProductName ProductName
FROM [mg_part] a
left join mg_Property b on a.part_categoryid = b.prop_id left join mg_FlowLine c on a.FlowLineID = c.fl_id
left join mg_Product d on a.ProductID = d.ID
where d.IsUseing=1 and a.part_id not in
(select top ((" + page + @"-1)*" + pagesize + @") part_id from [mg_part] order by part_id desc)
order by a.part_id desc
";
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_partModel>();
foreach (DataRow row in dt2.Rows)
{
mg_partModel model = new mg_partModel();
model.part_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "pid"));
model.part_no = DataHelper.GetCellDataToStr(row, "part_no");
model.part_name = DataHelper.GetCellDataToStr(row, "part_name");
model.part_desc = DataHelper.GetCellDataToStr(row, "part_desc");
model.parttypename = DataHelper.GetCellDataToStr(row, "PartTypeName");
model.propname = DataHelper.GetCellDataToStr(row, "PropName");
model.pflowlinename = DataHelper.GetCellDataToStr(row, "fl_name");
model.pproductname = DataHelper.GetCellDataToStr(row, "ProductName");
model.propid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "Propid"));
model.part_type = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "PartType"));
model.pflowlineid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "fl_id"));
model.pproductid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "ProductID"));
list.Add(model);
}
}
return(list);
}
public static DataTable GetWaringDataTable(string fl_id, string StartTime, string EndTime, int StartIndex, int EndIndex)
{
List <Production_AlarmModel> modelList = new List <Production_AlarmModel>();
List <Production_AlarmModel> footerList = new List <Production_AlarmModel>();
DataListModel <Production_AlarmModel> modeldata = new DataListModel <Production_AlarmModel>();
int total = 0;
//SqlParameter[] sqlPara = new SqlParameter[4];
//sqlPara[0] = new SqlParameter("@start_time", StartTime);
//sqlPara[1] = new SqlParameter("@end_time", EndTime);
//sqlPara[2] = new SqlParameter("@start_index", StartIndex);
//sqlPara[3] = new SqlParameter("@end_index", EndIndex);
//DataSet ds = SqlHelper.RunProcedureTables(SqlHelper.SqlConnString, "Proc_Rpt_AlarmTrend", sqlPara, new string[] { "data", "footer", "count" });
string where = "";
string SqlStr = @" select
distinct ROW_NUMBER() over( order by line.fl_id ,station.st_no)as rowid
,line.fl_id
,line.fl_name
,station.st_no as stationNo
,sum(case when AlarmType=1 then 1 else 0 end ) as material_num
,sum(case when AlarmType=2 then 1 else 0 end) as quality_num
,sum(case when AlarmType = 3 then 1 else 0 end) as maintenance_num
,sum(case when AlarmType =4 then 1 else 0 end) as overcycle_num
,sum(case when AlarmType = 5 then 1 else 0 end) as production_num
,sum(case when AlarmType = 6 then 1 else 0 end) as stop_num
,sum(case when AlarmType<7 then 1 else 0 end )as total_num
from mg_station station
left join mg_FlowLine line
on station.fl_id = line.fl_id
left join mg_Alarm alarm
on station.st_no = alarm.AlarmStation ";
if (!string.IsNullOrEmpty(StartTime))
{
where += " and alarm.AlarmStartTime >'" + StartTime + "' ";
}
if (!string.IsNullOrEmpty(EndTime))
{
where += " and alarm.AlarmEndTime <'" + EndTime + "' ";
}
where += " where station.fl_id >0 ";
if (!string.IsNullOrEmpty(fl_id))
{
where += " and station.fl_id =" + NumericParse.StringToInt(fl_id);
}
SqlStr += where + @"
group by line.fl_id
,line.fl_name
,station.st_no ";
string query_sql = " select * from ( " + SqlStr + " ) as Results order by rowid ";
string count_sql = " select count(*) as total from ( " + SqlStr + " ) AS T ";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt2 = ds.Tables["data"];
//DataTable footer = ds.Tables["footer"];
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
foreach (DataRow row in dt2.Rows)
{
Production_AlarmModel model = new Production_AlarmModel();
// model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id"));
// model.product_date = DataHelper.GetCellDataToStr(row, "product_date").Substring(0, 10);
model.fl_id = DataHelper.GetCellDataToStr(row, "fl_id");
model.fl_name = DataHelper.GetCellDataToStr(row, "fl_name");
model.stationNo = DataHelper.GetCellDataToStr(row, "stationNo");
// model.stationName = DataHelper.GetCellDataToStr(row, "stationName");
model.material_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "material_num"));
model.production_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "production_num"));
model.maintenance_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "maintenance_num"));
model.quality_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "quality_num"));
model.overcycle_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "overcycle_num"));
model.stop_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "stop_num"));
model.total_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "total_num"));
modelList.Add(model);
}
//foreach (DataRow row in footer.Rows)
//{
// Production_AlarmModel model = new Production_AlarmModel();
// //model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id"));
// //model.product_date = DataHelper.GetCellDataToStr(row, "product_date");
// model.product_date = DataHelper.GetCellDataToStr(row, "product_date");
// model.material_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "material_num"));
// model.production_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "production_num"));
// model.maintenance_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "maintenance_num"));
// model.quality_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "quality_num"));
// model.overcycle_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "overcycle_num"));
// model.total_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "total_num"));
// footerList.Add(model);
//}
DataListModel <Production_AlarmModel> allmodel = new DataListModel <Production_AlarmModel>();
allmodel.total = total.ToString();
allmodel.rows = modelList;
//allmodel.footer = footerList;
return(dt2);
}
else
{
total = 0;
return(null);
}
}
public static DataListModel <Production_AlarmModel> GetList(int page, int pagesize, string sidx, string sord, string Where)
{
List <Production_AlarmModel> modelList = new List <Production_AlarmModel>();
List <Production_AlarmModel> footerList = new List <Production_AlarmModel>();
DataListModel <Production_AlarmModel> modeldata = new DataListModel <Production_AlarmModel>();
int returnValue = 0;
int total = 0;
int StartIndex = (page - 1) * pagesize + 1;
int EndIndex = page * pagesize;
string SortFlag = sidx;
string sortOrder = sord;
string wherestr = Where;
string sql = @" select distinct AlarmStartTime as product_date
,sum(case when AlarmType=1 then 1 else 0 end ) as material_num
,sum(case when AlarmType=2 then 1 else 0 end) as quality_num
,sum(case when AlarmType = 3 then 1 else 0 end) as maintenance_num
,sum(case when AlarmType =4 then 1 else 0 end) as overcycle_num
,sum(case when AlarmType = 5 then 1 else 0 end) as production_num
,sum(case when AlarmType<6 then 1 else 0 end )as total_num
from
(select AlarmType,AlarmText, convert(varchar(10),AlarmStartTime,120) AlarmStartTime,convert(varchar(10),AlarmEndTime,120) AlarmEndTime,StartOrderNo,EndOrderNo,IsSolve,OperatorID from mg_alarm
where 1=1 " + wherestr + ") report";
if (string.IsNullOrEmpty(SortFlag))
{
SortFlag = "AlarmStartTime";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "asc";
}
string group_sql = " group by AlarmStartTime";
string order_sql = " order by " + SortFlag + " " + sortOrder;
string query_sql = sql + group_sql;
string sum_sql = @" select 'summary' as product_date
, sum(material_num) as material_num
, sum(quality_num) as quality_num
,sum(maintenance_num) as maintenance_num
,sum(overcycle_num) as overcycle_num
,sum(production_num) as production_num
,sum(total_num) as total_num
from ";
sum_sql += " (" + query_sql + ")a ";
sum_sql = sum_sql.Replace("\n", string.Empty).Replace("\r", string.Empty);
query_sql += order_sql;
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, query_sql + sum_sql, new string[] { "data", "footer" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt2 = ds.Tables["data"];
total = dt2.Rows.Count;
DataTable footer = ds.Tables["footer"];
foreach (DataRow row in dt2.Rows)
{
Production_AlarmModel model = new Production_AlarmModel();
// model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id"));
// model.product_date = DataHelper.GetCellDataToStr(row, "product_date").Substring(0, 10);
model.product_date = DataHelper.GetCellDataToStr(row, "product_date");
model.material_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "material_num"));
model.production_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "production_num"));
model.maintenance_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "maintenance_num"));
model.quality_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "quality_num"));
model.overcycle_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "overcycle_num"));
model.total_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "total_num"));
modelList.Add(model);
}
foreach (DataRow row in footer.Rows)
{
Production_AlarmModel model = new Production_AlarmModel();
//model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id"));
//model.product_date = DataHelper.GetCellDataToStr(row, "product_date");
model.product_date = DataHelper.GetCellDataToStr(row, "product_date");
model.material_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "material_num"));
model.production_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "production_num"));
model.maintenance_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "maintenance_num"));
model.quality_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "quality_num"));
model.overcycle_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "overcycle_num"));
model.total_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "total_num"));
footerList.Add(model);
}
DataListModel <Production_AlarmModel> allmodel = new DataListModel <Production_AlarmModel>();
allmodel.total = total.ToString();
allmodel.rows = modelList;
allmodel.footer = footerList;
return(allmodel);
}
else
{
total = 0;
return(null);
}
}
public static DataListModel <Production_AlarmModel> GetListNew(string StartTime, string EndTime, int StartIndex, int EndIndex)
{
List <Production_AlarmModel> modelList = new List <Production_AlarmModel>();
List <Production_AlarmModel> footerList = new List <Production_AlarmModel>();
DataListModel <Production_AlarmModel> modeldata = new DataListModel <Production_AlarmModel>();
int total = 0;
SqlParameter[] sqlPara = new SqlParameter[4];
sqlPara[0] = new SqlParameter("@start_time", StartTime);
sqlPara[1] = new SqlParameter("@end_time", EndTime);
sqlPara[2] = new SqlParameter("@start_index", StartIndex);
sqlPara[3] = new SqlParameter("@end_index", EndIndex);
DataSet ds = SqlHelper.RunProcedureTables(SqlHelper.SqlConnString, "Proc_Rpt_AlarmTrend", sqlPara, new string[] { "data", "footer", "count" });
if (DataHelper.HasData(ds))
{
DataTable dt2 = ds.Tables["data"];
DataTable footer = ds.Tables["footer"];
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
foreach (DataRow row in dt2.Rows)
{
Production_AlarmModel model = new Production_AlarmModel();
// model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id"));
// model.product_date = DataHelper.GetCellDataToStr(row, "product_date").Substring(0, 10);
string strtest = DataHelper.GetCellDataToStr(row, "product_date");
string str = DataHelper.GetCellDataToStr(row, "product_date").Split(' ')[0];
if (str.Length > 0)
{
model.product_date = str;
}
else
{
model.product_date = DataHelper.GetCellDataToStr(row, "product_date");
}
model.material_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "material_num"));
model.production_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "production_num"));
model.maintenance_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "maintenance_num"));
model.quality_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "quality_num"));
model.overcycle_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "overcycle_num"));
model.total_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "total_num"));
modelList.Add(model);
}
foreach (DataRow row in footer.Rows)
{
Production_AlarmModel model = new Production_AlarmModel();
//model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id"));
//model.product_date = DataHelper.GetCellDataToStr(row, "product_date");
model.product_date = DataHelper.GetCellDataToStr(row, "product_date");
model.material_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "material_num"));
model.production_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "production_num"));
model.maintenance_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "maintenance_num"));
model.quality_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "quality_num"));
model.overcycle_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "overcycle_num"));
model.total_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "total_num"));
footerList.Add(model);
}
DataListModel <Production_AlarmModel> allmodel = new DataListModel <Production_AlarmModel>();
allmodel.total = total.ToString();
allmodel.rows = modelList;
allmodel.footer = footerList;
return(allmodel);
}
else
{
total = 0;
return(null);
}
}
public static DataTable getTableExcel(string StartTime, string EndTime, string OrderCode, string StationNo, int PageIndex, out int total)
{
total = 0;
string where = " where 1=1 ";
if (StationNo == "FSA210")
{
where += " and a.StartTime > '" + StartTime + "' and a.EndTime < '" + EndTime + "' ";
where += " and a.stationno='" + StationNo + "'";
if (!string.IsNullOrEmpty(OrderCode))
{
where += " and REPLACE(a.OrderNo, CHAR(13) + CHAR(10), '') = '" + OrderCode + "'";
}
string orderStr = " order by a.StartTime , a.OrderNo ";
string SqlStr = @"select ROW_NUMBER() over(order by a.StartTime , a.OrderNo ) as 序号
, a.stationno as 工位
,d.op_name as 操作员
,g.ItemCaption 返修内容
,case when a.IsQualified = 1 then '合格' else '不合格' end as 是否合格
,a.starttime as 返修开始时间
,a.endtime as 返修结束时间
,a.OrderNo as 订单号 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 ";
SqlStr += where;
string query_sql = SqlStr + " order by 序号 asc ";
string count_sql = "select count(*) as total from ( " + SqlStr + " ) AS T ";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable dt2 = ds.Tables["data"];
return(dt2);
}
}
else
{
where += " and a.CreateTime > '" + StartTime + "' and a.CreateTime < '" + EndTime + "' ";
where += " and a.stationno='" + StationNo + "'";
if (!string.IsNullOrEmpty(OrderCode))
{
where += " and REPLACE(a.OrderNo, CHAR(13) + CHAR(10), '') = '" + OrderCode + "'";
}
string orderStr = " order by a.CreateTime , a.OrderNo ";
string SqlStr = @" select ROW_NUMBER() over(order by a.CreateTime , a.OrderNo ) as 序号
, a.stationno as 工位
,d.op_name as 操作员
,c.TestCaption as 检测内容
,a.TestValue as 检测值
,c.TestValueMin as 最小值
,c.TestValueMax as 最大值
,case when a.IsQualified = 1 then '合格' else '不合格' end as 是否合格
,a.CreateTime as 检测时间
,a.OrderNo as 订单号
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 ";
SqlStr += where;
List <mg_Repair> list = new List <mg_Repair>();
total = 0;
string query_sql = SqlStr + " order by 序号 asc ";
string count_sql = " select count(*) as total from ( " + SqlStr + " ) AS T ";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable dt2 = ds.Tables["data"];
//for (int i = 0; i < dt2.Rows.Count; i++)
//{
// string value = dt2.Rows[i]["检测内容"].ToString();
// if (value != "安全气囊电阻" && value != "SBR电阻" && value != "安全带插入电流" && value != "安全带拔出电流" && value != "SBR断路电阻" && value != "SBR下压前断路电阻")
// {
// //string aa ;
// //aa = ResTable4.Rows[i]["真实值"].ToString();
// //aa = "";
// DataRow drEmployee = dt2.Rows[i];
// drEmployee.BeginEdit();
// drEmployee["检测值"] = DBNull.Value;
// drEmployee.EndEdit();
// }
//}
return(dt2);
}
}
return(null);
}
public static String GetListNew(string StartTime, string EndTime, string OrderCode, string StationNo, int PageIndex, int PageSize, out int totalcount)
{
int StartIndex = (PageIndex - 1) * PageSize + 1;
int EndIndex = PageIndex * PageSize;
totalcount = 0;
string where = " where 1=1 ";
if (StationNo == "FSA210")
{
where += " and a.StartTime > '" + StartTime + "' and a.EndTime < '" + EndTime + "' ";
where += " and a.stationno='" + StationNo + "'";
if (!string.IsNullOrEmpty(OrderCode))
{
where += " and REPLACE(a.OrderNo, CHAR(13) + CHAR(10), '') = '" + OrderCode + "'";
}
string orderStr = " order by a.StartTime , a.OrderNo ";
string SqlStr = @"select ROW_NUMBER() over(order by a.StartTime , a.OrderNo ) as rowid
, a.stationno as StationNo
,d.op_name
,g.ItemCaption
,case when a.IsQualified = 1 then '合格' else '不合格' end as IsQualified
,a.starttime as StartTime
,a.endtime as 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 ";
SqlStr += where;
List <mg_Repair> list = new List <mg_Repair>();
int total = 0;
string query_sql = " select * from ( " + SqlStr + " ) as Results where rowid >=" + StartIndex + " and rowid <=" + EndIndex + " order by rowid ";
string count_sql = "select count(*) as total from ( " + SqlStr + " ) AS T";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable dt2 = ds.Tables["data"];
foreach (DataRow row in dt2.Rows)
{
mg_Repair model = new mg_Repair();
model.rowid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "rowid"));
model.StationNo = DataHelper.GetCellDataToStr(row, "StationNo");
model.op_name = DataHelper.GetCellDataToStr(row, "op_name");
model.ItemCaption = DataHelper.GetCellDataToStr(row, "ItemCaption");
model.IsQualifiedstr = DataHelper.GetCellDataToStr(row, "IsQualified");
model.CreateTime = (DataHelper.GetCellDataToStr(row, "StartTime"));
model.StartTime = (DataHelper.GetCellDataToStr(row, "StartTime"));
model.EndTime = (DataHelper.GetCellDataToStr(row, "EndTime"));
model.OrderNo = DataHelper.GetCellDataToStr(row, "OrderNo");
list.Add(model);
}
}
DataListModel <mg_Repair> allmodel = new DataListModel <mg_Repair>();
allmodel.total = total.ToString();
allmodel.rows = list;
string jsonStr = JSONTools.ScriptSerialize <DataListModel <mg_Repair> >(allmodel);
return(jsonStr);
}
else
{
where += " and a.CreateTime > '" + StartTime + "' and a.CreateTime < '" + EndTime + "' ";
where += " and a.stationno='" + StationNo + "'";
if (!string.IsNullOrEmpty(OrderCode))
{
where += " and REPLACE(a.OrderNo, CHAR(13) + CHAR(10), '') = '" + OrderCode + "'";
}
string orderStr = " order by a.CreateTime , a.OrderNo ";
string SqlStr = @"select ROW_NUMBER() over(order by a.CreateTime , a.OrderNo ) as rowid
, a.stationno as StationNo
,d.op_name
,c.TestCaption as ItemCaption
,case when a.IsQualified = 1 then '合格' else '不合格' end as IsQualified
,a.CreateTime
,a.OrderNo
,a.TestValue
,c.TestValueMin
,c.TestValueMax
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 ";
SqlStr += where;
List <mg_Repair> list = new List <mg_Repair>();
int total = 0;
string query_sql = " select * from ( " + SqlStr + " ) as Results where rowid >=" + StartIndex + " and rowid <=" + EndIndex + " order by rowid ";
string count_sql = "select count(*) as total from ( " + SqlStr + " ) AS T ";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable dt2 = ds.Tables["data"];
foreach (DataRow row in dt2.Rows)
{
mg_Repair model = new mg_Repair();
model.rowid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "rowid"));
model.StationNo = DataHelper.GetCellDataToStr(row, "StationNo");
model.op_name = DataHelper.GetCellDataToStr(row, "op_name");
model.TestCaption = DataHelper.GetCellDataToStr(row, "ItemCaption");
model.TestValue = (DataHelper.GetCellDataToStr(row, "TestValue"));
model.TestValueMin = (DataHelper.GetCellDataToStr(row, "TestValueMin"));
model.TestValueMax = (DataHelper.GetCellDataToStr(row, "TestValueMax"));
string value = model.TestCaption;
if (value != "安全气囊电阻" && value != "SBR电阻" && value != "安全带插入电流" && value != "安全带拔出电流" && value != "SBR断路电阻" && value != "SBR下压前断路电阻")
{
model.TestValue = " ";
}
model.IsQualifiedstr = DataHelper.GetCellDataToStr(row, "IsQualified");
model.CreateTime = (DataHelper.GetCellDataToStr(row, "CreateTime"));
model.OrderNo = DataHelper.GetCellDataToStr(row, "OrderNo");
list.Add(model);
}
}
DataListModel <mg_Repair> allmodel = new DataListModel <mg_Repair>();
allmodel.total = total.ToString();
allmodel.rows = list;
string jsonStr = JSONTools.ScriptSerialize <DataListModel <mg_Repair> >(allmodel);
return(jsonStr);
}
return("");
}
