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 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 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 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 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(""); }
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 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 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 station.st_no stationNo ,min(station.st_name) stationName ,sum(case when alarm.AlarmType=1 then 1 else 0 end ) as material_num ,sum(case when alarm.AlarmType=2 then 1 else 0 end) as quality_num ,sum(case when alarm.AlarmType = 3 then 1 else 0 end) as maintenance_num ,sum(case when alarm.AlarmType =4 then 1 else 0 end) as overcycle_num ,sum(case when alarm.AlarmType = 5 then 1 else 0 end) as production_num ,sum(case when alarm.AlarmType<6 then 1 else 0 end )as total_num from ( select distinct s1.st_no as st_no ,case when(min(s1.st_name) = max(s2.st_name)) then min(s1.st_name) else min(s1.st_name) +' '+ max(s1.st_name) end as st_name from mg_station s1 left outer join mg_station s2 on s1.st_no = s2.st_no group by s1.st_no )station left outer join mg_alarm alarm on station.st_no = alarm.AlarmStation " + wherestr; if (string.IsNullOrEmpty(SortFlag)) { SortFlag = "st_no"; } if (string.IsNullOrEmpty(sortOrder)) { sortOrder = "asc"; } string group_sql = " group by station.st_no,station.st_name"; string order_sql = " order by " + SortFlag + " " + sortOrder; string query_sql = sql + group_sql; string sum_sql = @" select 'summary' as stationNo,'' as stationName , 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", " ").Replace("\r", " "); 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.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_BOMTestModel> QueryListForPaging(string page, string pagesize, out string total) { total = "0"; List <mg_BOMTestModel> list = null; string sql1 = @"select count(ID) total from [mg_Test];"; string sql2 = @"SELECT top " + pagesize + @" u.ID tid ,d.ID Groupid ,d.GroupName GroupName ,TestPage , TestType , case u.TestType when 1 then '自动测试' when 2 then '手动测试' end as TestTypeName ,TestCalculateType ,case u.TestCalculateType when 1 then '自动计算' when 2 then '程序逻辑处理' end as TestCalculateTypeName ,TestCaption ,TestValueMin ,TestValueMax ,TestValueIsContain ,case u.TestValueIsContain when 1 then '包含' when 0 then '不包含' end as TestValueIsContainName ,TestValueUnit ,PLCName ,PLCValueType ,PLCOutMultiple FROM [mg_Test] u left join mg_Test_Group d on u.TestGroupID = d.ID where u.ID < ( select top 1 ID from (select top ((" + page + @"-1)*" + pagesize + @") ID from [mg_Test] where ID is not null order by ID desc )t order by ID ) order by u.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_BOMTestModel>(); foreach (DataRow row in dt2.Rows) { mg_BOMTestModel model = new mg_BOMTestModel(); model.test_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "tid")); model.testgroupname = DataHelper.GetCellDataToStr(row, "GroupName"); model.testpage = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "TestPage")); model.testtypename = DataHelper.GetCellDataToStr(row, "TestTypeName"); model.testcalculatetypename = DataHelper.GetCellDataToStr(row, "TestCalculateTypeName"); model.testcaption = DataHelper.GetCellDataToStr(row, "TestCaption"); string TestValueMin = DataHelper.GetCellDataToStr(row, "TestValueMin") == "" ? "0" : DataHelper.GetCellDataToStr(row, "TestValueMin"); string TestValueMax = DataHelper.GetCellDataToStr(row, "TestValueMax") == "" ? "0" : DataHelper.GetCellDataToStr(row, "TestValueMax"); model.testvaluemin = float.Parse(TestValueMin); model.testvaluemax = float.Parse(TestValueMax); model.testvalueiscontainname = DataHelper.GetCellDataToStr(row, "TestValueIsContainName"); model.testvalueunit = DataHelper.GetCellDataToStr(row, "TestValueUnit"); model.plcname = DataHelper.GetCellDataToStr(row, "PLCName"); model.plcvaluetype = DataHelper.GetCellDataToStr(row, "PLCValueType"); model.plcoutmultiple = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "PLCOutMultiple")); model.testtype = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "TestType")); model.testcalculatetype = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "TestCalculateType")); model.testvalueiscontain = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "TestValueIsContain")); model.testgroupid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "Groupid")); list.Add(model); } } return(list); }
public static DataListModel <Production_Model> GetList(int page, int pagesize, string sidx, string sord, string Where) { List <Production_Model> modelList = new List <Production_Model>(); List <Production_Model> footerList = new List <Production_Model>(); DataListModel <Production_Model> modeldata = new DataListModel <Production_Model>(); 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 [id] ,[product_date] ,[cl_name] ,[hourid] ,[customer_num] ,[real_num] ,([real_num] - [customer_num]) as real_customer ,[plan_pro_num] ,[real_pro_num] ,([real_pro_num] - [plan_pro_num]) as real_plan ,case when ([plan_pro_num]>0) then round([real_pro_num]*100.00/[plan_pro_num],1) else 0.0 end as real_biplan ,[line1_finish_num] ,[line1_repair_num] ,case when ([line1_finish_num]>0) then round([line1_repair_num]*100.00/[line1_finish_num],1) else 0.0 end as line1_FTT ,[line2_finish_num] ,[line2_repair_num] ,case when ([line2_finish_num]>0) then round([line2_repair_num]*100.00/[line2_finish_num],1) else 0.0 end as line2_FTT ,[line3_finish_num] ,[line3_repair_num] ,case when ([line3_finish_num]>0) then round([line3_repair_num]*100.00/[line3_finish_num],1) else 0.0 end as line3_FTT ,[line4_finish_num] ,[line4_repair_num] ,case when ([line4_finish_num]>0) then round([line4_repair_num]*100.00/[line4_finish_num],1) else 0.0 end as line4_FTT ,[line5_finish_num] ,[line5_repair_num] ,case when ([line5_finish_num]>0) then round([line5_repair_num]*100.00/[line5_finish_num],1) else 0.0 end as line5_FTT ,[line6_finish_num] ,[line6_repair_num] ,case when ([line6_finish_num]>0) then round([line6_repair_num]*100.00/[line6_finish_num],1) else 0.0 end as line6_FTT FROM [Sheet] where 1=1 " + Where; if (string.IsNullOrEmpty(SortFlag)) { SortFlag = "ID"; } if (string.IsNullOrEmpty(sortOrder)) { sortOrder = "asc"; } StringBuilder commandText = new StringBuilder(); commandText.Append(" SELECT TOP " + pagesize + " * FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + SortFlag + " " + sortOrder + ") AS RowNumber,* FROM Sheet 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 Sheet where 1 = 1 " + wherestr; string sum_sql = @" select '汇总' as id ,null as product_date ,null as cl_name ,null as hourid ,sum(customer_num) as customer_num ,sum([real_num]) as real_num ,sum([real_customer]) as [real_customer] , sum([plan_pro_num]) as [plan_pro_num] ,sum([real_pro_num]) as [real_pro_num] ,sum([real_plan]) as [real_plan] ,null as [real_biplan] ,sum([line1_finish_num]) as [line1_finish_num] ,sum ([line1_repair_num]) as [line1_repair_num] ,null as [line1_FTT] ,sum([line2_finish_num]) as [line2_finish_num] ,sum ([line2_repair_num]) as [line2_repair_num] ,null as [line2_FTT] ,sum([line3_finish_num]) as [line3_finish_num] ,sum ([line3_repair_num]) as [line3_repair_num] ,null as [line3_FTT] ,sum([line4_finish_num]) as [line4_finish_num] ,sum ([line4_repair_num]) as [line4_repair_num] ,null as [line4_FTT] ,sum([line5_finish_num]) as [line5_finish_num] ,sum ([line5_repair_num]) as [line5_repair_num] ,null as [line5_FTT] ,sum([line6_finish_num]) as [line6_finish_num] ,sum ([line6_repair_num]) as [line6_repair_num] ,null as [line6_FTT] from "; sum_sql += " (" + query_sql + ")a "; sum_sql = sum_sql.Replace("\n", string.Empty).Replace("\r", string.Empty); DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, count_sql + query_sql + sum_sql, new string[] { "count", "data", "footer" }, null); if (DataHelper.HasData(ds)) { DataTable dt1 = ds.Tables["count"]; total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total")); DataTable dt2 = ds.Tables["data"]; DataTable footer = ds.Tables["footer"]; foreach (DataRow row in dt2.Rows) { Production_Model model = new Production_Model(); model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id")); model.product_date = DataHelper.GetCellDataToStr(row, "product_date").Substring(0, 10); model.cl_name = DataHelper.GetCellDataToStr(row, "cl_name"); model.hourid = DataHelper.GetCellDataToStr(row, "hourid"); model.customer_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "customer_num")); model.real_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_num")); model.real_customer = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_customer")); model.plan_pro_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "plan_pro_num")); model.real_pro_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_pro_num")); model.real_plan = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_plan")); model.real_biplan = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_biplan")); model.real_biplan = NumericParse.CutDecimalWithN(model.real_biplan, 3); model.line1_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_finish_num")); model.line1_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_repair_num")); model.line1_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_FTT")); model.line1_FTT = NumericParse.CutDecimalWithN(model.line1_FTT, 3); model.line2_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_finish_num")); model.line2_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_repair_num")); model.line2_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_FTT")); model.line2_FTT = NumericParse.CutDecimalWithN(model.line2_FTT, 3); model.line3_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_finish_num")); model.line3_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_repair_num")); model.line3_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_FTT")); model.line3_FTT = NumericParse.CutDecimalWithN(model.line3_FTT, 3); model.line4_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_finish_num")); model.line4_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_repair_num")); model.line4_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_FTT")); model.line4_FTT = NumericParse.CutDecimalWithN(model.line4_FTT, 3); model.line5_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_finish_num")); model.line5_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_repair_num")); model.line5_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_FTT")); model.line5_FTT = NumericParse.CutDecimalWithN(model.line5_FTT, 3); model.line6_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_finish_num")); model.line6_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_repair_num")); model.line6_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_FTT")); model.line6_FTT = NumericParse.CutDecimalWithN(model.line6_FTT, 3); modelList.Add(model); } foreach (DataRow row in footer.Rows) { Production_Model model = new Production_Model(); model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id")); model.product_date = DataHelper.GetCellDataToStr(row, "product_date"); model.cl_name = DataHelper.GetCellDataToStr(row, "cl_name"); model.hourid = DataHelper.GetCellDataToStr(row, "hourid"); model.customer_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "customer_num")); model.real_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_num")); model.real_customer = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_customer")); model.plan_pro_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "plan_pro_num")); model.real_pro_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_pro_num")); model.real_plan = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_plan")); model.real_biplan = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_biplan")); model.line1_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_finish_num")); model.line1_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_repair_num")); model.line1_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_FTT")); model.line2_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_finish_num")); model.line2_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_repair_num")); model.line2_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_FTT")); model.line3_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_finish_num")); model.line3_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_repair_num")); model.line3_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_FTT")); model.line4_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_finish_num")); model.line4_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_repair_num")); model.line4_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_FTT")); model.line5_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_finish_num")); model.line5_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_repair_num")); model.line5_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_FTT")); model.line6_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_finish_num")); model.line6_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_repair_num")); model.line6_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_FTT")); footerList.Add(model); } DataListModel <Production_Model> allmodel = new DataListModel <Production_Model>(); allmodel.total = total.ToString(); allmodel.rows = modelList; allmodel.footer = footerList; return(allmodel); } else { total = 0; return(null); } }
public static DataListModel <Production_Model> GetListNew(string StartTime, string EndTime, int clnameid, string clname, int page, int pagesize) { List <Production_Model> modelList = new List <Production_Model>(); List <Production_Model> footerList = new List <Production_Model>(); DataListModel <Production_Model> modeldata = new DataListModel <Production_Model>(); int returnValue = 0; int total = 0; int StartIndex = (page - 1) * pagesize + 1; int EndIndex = page * pagesize; SqlParameter[] sqlPara = new SqlParameter[6]; sqlPara[0] = new SqlParameter("@class_id", clnameid); sqlPara[1] = new SqlParameter("@class_name", clname); sqlPara[2] = new SqlParameter("@start_time", StartTime); sqlPara[3] = new SqlParameter("@end_time", EndTime); sqlPara[4] = new SqlParameter("@start_index", StartIndex); sqlPara[5] = new SqlParameter("@end_index", EndIndex); DataSet ds = SqlHelper.RunProcedureTables(SqlHelper.SqlConnString, "Proc_Rpt_ProductHourly", sqlPara, new string[] { "data", "footer", "count" }); if (DataHelper.HasData(ds)) { DataTable dt1 = ds.Tables["count"]; total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total")); DataTable dt2 = ds.Tables["data"]; DataTable footer = ds.Tables["footer"]; foreach (DataRow row in dt2.Rows) { Production_Model model = new Production_Model(); model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id")); model.product_date = DataHelper.GetCellDataToStr(row, "product_date").Substring(0, 10); model.cl_name = DataHelper.GetCellDataToStr(row, "cl_name"); model.hourid = DataHelper.GetCellDataToStr(row, "hourid"); model.customer_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "customer_num")); model.real_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_num")); model.real_customer = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_customer")); model.plan_pro_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "plan_pro_num")); model.real_pro_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_pro_num")); model.real_plan = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_plan")); model.real_biplan = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_biplan")); model.real_biplan = NumericParse.CutDecimalWithN(model.real_biplan, 3); model.line1_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_finish_num")); model.line1_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_repair_num")); model.line1_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_FTT")); model.line1_FTT = NumericParse.CutDecimalWithN(model.line1_FTT, 3); model.line2_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_finish_num")); model.line2_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_repair_num")); model.line2_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_FTT")); model.line2_FTT = NumericParse.CutDecimalWithN(model.line2_FTT, 3); model.line3_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_finish_num")); model.line3_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_repair_num")); model.line3_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_FTT")); model.line3_FTT = NumericParse.CutDecimalWithN(model.line3_FTT, 3); model.line4_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_finish_num")); model.line4_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_repair_num")); model.line4_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_FTT")); model.line4_FTT = NumericParse.CutDecimalWithN(model.line4_FTT, 3); model.line5_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_finish_num")); model.line5_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_repair_num")); model.line5_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_FTT")); model.line5_FTT = NumericParse.CutDecimalWithN(model.line5_FTT, 3); model.line6_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_finish_num")); model.line6_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_repair_num")); model.line6_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_FTT")); model.line6_FTT = NumericParse.CutDecimalWithN(model.line6_FTT, 3); modelList.Add(model); } foreach (DataRow row in footer.Rows) { Production_Model model = new Production_Model(); model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id")); model.product_date = DataHelper.GetCellDataToStr(row, "product_date"); model.cl_name = DataHelper.GetCellDataToStr(row, "cl_name"); model.hourid = DataHelper.GetCellDataToStr(row, "hourid"); model.customer_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "customer_num")); model.real_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_num")); model.real_customer = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_customer")); model.plan_pro_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "plan_pro_num")); model.real_pro_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_pro_num")); model.real_plan = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_plan")); model.real_biplan = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "real_biplan")); model.line1_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_finish_num")); model.line1_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_repair_num")); model.line1_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line1_FTT")); model.line2_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_finish_num")); model.line2_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_repair_num")); model.line2_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line2_FTT")); model.line3_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_finish_num")); model.line3_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_repair_num")); model.line3_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line3_FTT")); model.line4_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_finish_num")); model.line4_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_repair_num")); model.line4_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line4_FTT")); model.line5_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_finish_num")); model.line5_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_repair_num")); model.line5_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line5_FTT")); model.line6_finish_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_finish_num")); model.line6_repair_num = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_repair_num")); model.line6_FTT = NumericParse.StringToDecimal(DataHelper.GetCellDataToStr(row, "line6_FTT")); footerList.Add(model); } DataListModel <Production_Model> allmodel = new DataListModel <Production_Model>(); allmodel.total = total.ToString(); allmodel.rows = modelList; allmodel.footer = footerList; return(allmodel); } else { total = 0; return(null); } }