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(""); }