/// <summary> /// 修改时间:2017年5月9日,修改内容:工位信息展示只展示工位号,不展示工位说明 /// 修改人:冉守旭 /// </summary> /// <returns></returns> public static List <mg_stationModel> QueryStationForOperatorEditing() { List <mg_stationModel> list = null; // string sql = @"SELECT [st_id] // ,[st_no] // ,[st_name] // FROM [mg_station] order by st_no"; string sql = @"SELECT [st_no],max(st_id) as st_id,max(st_mac) as st_mac FROM [mg_station] where st_mac!='' and st_mac is not null group by st_no order by st_no "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { list = new List <mg_stationModel>(); foreach (DataRow row in dt.Rows) { mg_stationModel model = new mg_stationModel(); model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id")); //model.st_name = DataHelper.GetCellDataToStr(row, "st_no") + " --- " + DataHelper.GetCellDataToStr(row, "st_name"); model.st_name = DataHelper.GetCellDataToStr(row, "st_no"); list.Add(model); } } return(list); }
public static List <Mat_SupplierListModel> QuerySupplierListForEditing() { List <Mat_SupplierListModel> list = null; string sql = @"SELECT TOP 1000 [ID] ,[SupplierID] ,[SupplierName] ,[WareHouseID] FROM [Mat_SupplierList] order by ID"; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { list = new List <Mat_SupplierListModel>(); foreach (DataRow row in dt.Rows) { Mat_SupplierListModel model = new Mat_SupplierListModel(); model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "ID")); model.supplierid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "SupplierID")); model.suppliername = DataHelper.GetCellDataToStr(row, "SupplierName"); model.warehouseid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "WareHouseID")); list.Add(model); } Mat_SupplierListModel firstmodel = new Mat_SupplierListModel(); firstmodel.id = 0; firstmodel.suppliername = "-- 请选择供应商 --"; list.Insert(0, firstmodel); } return(list); }
public static List <px_PanrameterModel> QueryPanrameterListForPart() { List <px_PanrameterModel> list = null; string sql2 = @" SELECT [SerialID] ,[Name] ,[Number] FROM [px_Panrameter] order by SerialID asc "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null); if (DataHelper.HasData(dt)) { DataTable dt2 = dt; list = new List <px_PanrameterModel>(); foreach (DataRow row in dt2.Rows) { px_PanrameterModel model = new px_PanrameterModel(); model.SerialID = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "SerialID")); model.Name = DataHelper.GetCellDataToStr(row, "Name"); model.Number = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "Number")); list.Add(model); } } return(list); }
public static List <mg_stationModel> QueryStationForStepEditing(string fl_id) { List <mg_stationModel> list = null; string qsql = (!string.IsNullOrEmpty(fl_id) && fl_id != "0") ? @" where fl_id=" + fl_id : ""; string sql = @"SELECT [st_id] ,[st_no] ,[st_name] FROM [mg_station] " + qsql + @" order by st_order"; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { list = new List <mg_stationModel>(); foreach (DataRow row in dt.Rows) { mg_stationModel model = new mg_stationModel(); model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id")); model.st_no = DataHelper.GetCellDataToStr(row, "st_no") + " | " + DataHelper.GetCellDataToStr(row, "st_name"); list.Add(model); } mg_stationModel firstmodel = new mg_stationModel(); firstmodel.st_id = 0; firstmodel.st_no = "-- 工位(全部) --"; list.Insert(0, firstmodel); } return(list); }
public static mg_OperatorModel GetOperaterForCardNO(string cardNO) { string sql = @" SELECT [op_id] ,[op_name] ,[op_no] ,[op_pic] ,[op_mac] ,[op_isoperator] ,op.[st_id] ,st.st_no FROM [mg_Operator] op left join mg_station st on op.st_id = st.st_id where op_no='" + cardNO.Replace("\0", "") + "' "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { foreach (DataRow row in dt.Rows) { mg_OperatorModel model = new mg_OperatorModel(); model.op_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "op_id")); model.op_isoperator = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "op_isoperator")); model.op_name = DataHelper.GetCellDataToStr(row, "op_name"); model.op_no = DataHelper.GetCellDataToStr(row, "op_no"); model.op_pic = DataHelper.GetCellDataToStr(row, "op_pic"); model.st_no = DataHelper.GetCellDataToStr(row, "st_no"); model.op_mac = DataHelper.GetCellDataToStr(row, "op_mac"); model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id")); return(model); } } return(null); }
///陈名兴 ///增加下拉类型 /// public static List <mg_PropertyModel> QueryPropertyFlowlingForEditing(string type) { List <mg_PropertyModel> list = null; string sql = @"SELECT [prop_id] ,[Prop_Type] ,[prop_name] FROM [Mg_Property] where [Prop_type]=" + type + " order by prop_id"; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { list = new List <mg_PropertyModel>(); foreach (DataRow row in dt.Rows) { mg_PropertyModel model = new mg_PropertyModel(); model.prop_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "prop_id")); model.prop_name = DataHelper.GetCellDataToStr(row, "prop_name"); model.Prop_type = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "Prop_type")); list.Add(model); } mg_PropertyModel firstmodel = new mg_PropertyModel(); firstmodel.prop_id = 0; firstmodel.prop_name = "-- 请选择类型 --"; list.Insert(0, firstmodel); } return(list); }
public static List <mg_ODSModel> QueryODSByStepid(string step_id) { List <mg_ODSModel> list = null; string sql2 = @" SELECT [ods_name] ,[ods_keywords] FROM [mg_ODS] where step_id=" + step_id + @" order by ods_id "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null); if (DataHelper.HasData(dt)) { list = new List <mg_ODSModel>(); foreach (DataRow row in dt.Rows) { mg_ODSModel model = new mg_ODSModel(); model.ods_name = DataHelper.GetCellDataToStr(row, "ods_name"); model.ods_keywords = DataHelper.GetCellDataToStr(row, "ods_keywords"); list.Add(model); } } return(list); }
public static bool CuttingOrder(DataTable orderDT, DataTable dt2) //点击拆单时候触发的 { StringBuilder sbStr = new StringBuilder(); string co_id = DataHelper.GetCellDataToStr(orderDT.Rows[0], "co_id"); string orderSql = @" UPDATE [mg_CustomerOrder] SET [co_isCutted] =1 WHERE co_id =" + co_id + ";"; sbStr.Append(orderSql); sbStr.Append(@"DELETE FROM mg_Order_FSD WHERE co_id =" + co_id + ";"); sbStr.Append(@"DELETE FROM mg_Order_FSDB WHERE co_id =" + co_id + ";"); sbStr.Append(@"DELETE FROM mg_Order_FSDC WHERE co_id =" + co_id + ";"); sbStr.Append(@"DELETE FROM mg_Order_FSP WHERE co_id =" + co_id + ";"); sbStr.Append(@"DELETE FROM mg_Order_FSPB WHERE co_id =" + co_id + ";"); sbStr.Append(@"DELETE FROM mg_Order_FSPC WHERE co_id =" + co_id + ";"); sbStr.Append(@"DELETE FROM mg_Order_RSB40 WHERE co_id =" + co_id + ";"); sbStr.Append(@"DELETE FROM mg_Order_RSB60 WHERE co_id =" + co_id + ";"); sbStr.Append(@"DELETE FROM mg_Order_RSC WHERE co_id =" + co_id + ";"); //sbStr.Append(@"insert into a(A,B,C) select '','',c from B;"); sbStr.Append(AssemblingOrderSqlStr(dt2, orderDT, (int)mg_XLSEnum.FS_Drive, "mg_Order_FSD", "ofsd_", "FS")); sbStr.Append(AssemblingOrderSqlStr(dt2, orderDT, (int)mg_XLSEnum.FSB_Drive, "mg_Order_FSDB", "ofsdb_", "FB")); sbStr.Append(AssemblingOrderSqlStr(dt2, orderDT, (int)mg_XLSEnum.FSC_Drive, "mg_Order_FSDC", "ofsdc_", "FC")); sbStr.Append(AssemblingOrderSqlStr(dt2, orderDT, (int)mg_XLSEnum.FS_Passenger, "mg_Order_FSP", "ofsp_", "FS")); sbStr.Append(AssemblingOrderSqlStr(dt2, orderDT, (int)mg_XLSEnum.FSB_Passenger, "mg_Order_FSPB", "ofspb_", "FB")); sbStr.Append(AssemblingOrderSqlStr(dt2, orderDT, (int)mg_XLSEnum.FSC_Passenger, "mg_Order_FSPC", "ofspc_", "FC")); sbStr.Append(AssemblingOrderSqlStr(dt2, orderDT, (int)mg_XLSEnum.RSB40, "mg_Order_RSB40", "orsb40_", "B4")); sbStr.Append(AssemblingOrderSqlStr(dt2, orderDT, (int)mg_XLSEnum.RSB60, "mg_Order_RSB60", "orsb60_", "B6")); sbStr.Append(AssemblingOrderSqlStr(dt2, orderDT, (int)mg_XLSEnum.RSC, "mg_Order_RSC", "orsc_", "RC")); return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, CommandType.Text, sbStr.ToString(), null) != -1 ? true : false); }
public static List <Mg_MaterialPropertyTypeModel> QueryMaterialPropertyTypeForEditing() { List <Mg_MaterialPropertyTypeModel> list = null; string sql = @"SELECT [ID] ,[Prop_Type] FROM [Mg_MaterialPropertyType] order by ID"; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { list = new List <Mg_MaterialPropertyTypeModel>(); foreach (DataRow row in dt.Rows) { Mg_MaterialPropertyTypeModel model = new Mg_MaterialPropertyTypeModel(); model.ID = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "ID")); model.Prop_Type = DataHelper.GetCellDataToStr(row, "Prop_Type"); list.Add(model); } Mg_MaterialPropertyTypeModel firstmodel = new Mg_MaterialPropertyTypeModel(); firstmodel.ID = 0; firstmodel.Prop_Type = "-- 请选择类型 --"; list.Insert(0, firstmodel); } return(list); }
public static List <mg_partModel> QueryPartForPartidList() { List <mg_partModel> list = null; string sql2 = @" SELECT [part_id] ,[part_name] FROM [mg_part] "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null); if (DataHelper.HasData(dt)) { list = new List <mg_partModel>(); foreach (DataRow row in dt.Rows) { mg_partModel model = new mg_partModel(); model.part_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "part_id")); model.part_name = DataHelper.GetCellDataToStr(row, "part_name"); list.Add(model); } } return(list); }
public static List <mg_BOMModel> QueryBOMForStepEditing(string part_id) { List <mg_BOMModel> list = null; string joinSre = (!string.IsNullOrEmpty(part_id) && part_id != "0") ? @" inner join [mg_part_bom_rel] pbrel on pbrel.bom_id=bom.bom_id and pbrel.part_id=" + part_id : ""; string sql = @" SELECT bom.[bom_id] ,[bom_PN] ,bom_desc FROM [mg_BOM] bom " + joinSre + @" order by bom_PN,bom_desc"; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { list = new List <mg_BOMModel>(); foreach (DataRow row in dt.Rows) { mg_BOMModel model = new mg_BOMModel(); model.bom_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_id")); model.bom_PN = DataHelper.GetCellDataToStr(row, "bom_PN") + " | " + DataHelper.GetCellDataToStr(row, "bom_desc"); list.Add(model); } } return(list); }
public static List <px_ShowChiClientModel> Querypx_ShowChiClientListForPart() { List <px_ShowChiClientModel> list = null; string sql2 = @" SELECT [SID] ,[SName] ,[ClientIP] ,[SAddTime] ,[SRole] ,[SRamark] FROM [px_ShowChiClient] order by SID asc "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null); if (DataHelper.HasData(dt)) { DataTable dt2 = dt; list = new List <px_ShowChiClientModel>(); foreach (DataRow row in dt2.Rows) { px_ShowChiClientModel model = new px_ShowChiClientModel(); model.SID = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "SID")); model.SName = DataHelper.GetCellDataToStr(row, "SName"); model.ClientIP = DataHelper.GetCellDataToStr(row, "ClientIP"); model.SAddTime = DataHelper.GetCellDataToStr(row, "SAddTime"); model.SRole = DataHelper.GetCellDataToStr(row, "SRole"); model.SRamark = DataHelper.GetCellDataToStr(row, "SRamark"); list.Add(model); } } return(list); }
public static List <mg_CustomerOrder_3_CreateTime> AutoPrintSent_autoSendPrintedList() { List <mg_CustomerOrder_3_CreateTime> list = null; //离现在最远 未打印OrderIsPrint = 0 整个订单未打印 string sql = @"SELECT TOP (1) CONVERT(int, CustomerOrder.OrderID) AS ID, CONVERT(int, p.ID) AS PartOrderID, CONVERT(nvarchar, CustomerOrder.OrderID) AS 订单号, CustomerOrder.SerialNumber AS 车身号, CONVERT(nvarchar, p.OrderIsPrint) AS IsPrint, CONVERT(nvarchar, CustomerOrder.CreateTime) AS CreateTime FROM dbo.mg_CustomerOrder_3 AS CustomerOrder INNER JOIN dbo.mg_Customer_Product AS Customer_Product ON CustomerOrder.OrderID = Customer_Product.CustomerOrderID INNER JOIN dbo.mg_PartOrder AS p ON Customer_Product.ID = p.CustomerProductID WHERE (CustomerOrder.OrderType = 1 OR CustomerOrder.OrderType = 2) AND (p.OrderIsPrint = '0' or p.OrderIsPrint is null) ORDER BY CustomerOrder.CreateTime ";//DESC DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { DataTable dt2 = dt; list = new List <mg_CustomerOrder_3_CreateTime>(); foreach (DataRow row in dt2.Rows) { mg_CustomerOrder_3_CreateTime model = new mg_CustomerOrder_3_CreateTime(); model.id = Convert.ToInt32(DataHelper.GetCellDataToStr(row, "id")); model.PartOrderID = Convert.ToInt32(DataHelper.GetCellDataToStr(row, "PartOrderID")); model.订单号 = DataHelper.GetCellDataToStr(row, "订单号"); model.车身号 = DataHelper.GetCellDataToStr(row, "车身号"); model.IsPrint = DataHelper.GetCellDataToStr(row, "IsPrint"); model.CreateTime = DataHelper.GetCellDataToStr(row, "CreateTime"); list.Add(model); } } return(list); }
public static List <mg_FlowlingModel> QueryFlowlingForEditing() { List <mg_FlowlingModel> list = null; string sql = @"SELECT [fl_id] ,[fl_name] 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"); list.Add(model); } mg_FlowlingModel firstmodel = new mg_FlowlingModel(); firstmodel.fl_id = 0; firstmodel.fl_name = "-- 流水线(全部) --"; list.Insert(0, firstmodel); } return(list); }
public static List <Mat_SupplierListModel> querySupplierForBOM(string type) { List <Mat_SupplierListModel> list = null; string sql = @"SELECT [ID] ,[SupplierID] ,[SupplierName] FROM [Mat_SupplierList] where WareHouseID=" + type + " order by SupplierID"; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { list = new List <Mat_SupplierListModel>(); foreach (DataRow row in dt.Rows) { Mat_SupplierListModel model = new Mat_SupplierListModel(); model.supplierid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "SupplierID")); model.suppliername = DataHelper.GetCellDataToStr(row, "SupplierName"); list.Add(model); } Mat_SupplierListModel firstmodel = new Mat_SupplierListModel(); firstmodel.supplierid = 0; firstmodel.suppliername = "-- 请选择类型 --"; list.Insert(0, firstmodel); } return(list); }
public static List <px_InternetPrinterModel> QueryInternetPrinterListForPart() { List <px_InternetPrinterModel> list = null; string sql2 = @" SELECT [IID] ,[IName] ,[PrintIP] ,[IAddTime],[IRole],[IRamark] FROM [px_InternetPrinter] order by IID asc "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null); if (DataHelper.HasData(dt)) { DataTable dt2 = dt; list = new List <px_InternetPrinterModel>(); foreach (DataRow row in dt2.Rows) { px_InternetPrinterModel model = new px_InternetPrinterModel(); model.IID = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "IID")); model.IName = DataHelper.GetCellDataToStr(row, "IName"); model.PrintIP = DataHelper.GetCellDataToStr(row, "PrintIP"); model.IAddTime = DataHelper.GetCellDataToStr(row, "IAddTime"); model.IRole = DataHelper.GetCellDataToStr(row, "IRole"); model.IRamark = DataHelper.GetCellDataToStr(row, "IRamark"); list.Add(model); } } return(list); }
public static List <View_px_AllList_DoHis> QueryView_px_AllList_DoHisList() { List <View_px_AllList_DoHis> list = null; //扫描数据【没有置·1的】 string sql = @"SELECT [车身号] ,[主副驾] ,[零件号描述] ,[IsPrint] ,[OrderIsHistory] ,[abb] ,[resultljh] FROM [AllList_DoHis] where (zjppp = '靠背面套主驾' or zjppp = '靠背面套副驾' or zjppp = '坐垫面套主驾' or zjppp = '坐垫面套副驾' or zjppp = '坐垫骨架主驾' or zjppp = '坐垫骨架副驾' or zjppp = '靠背骨架主驾' or zjppp = '靠背骨架副驾' or zjppp = '线束主驾' or zjppp = '线束副驾' or zjppp = '大背板主驾' or zjppp = '大背板副驾' or zjppp = '靠背面套后40' or zjppp = '靠背面套后60' or zjppp = '坐垫面套后坐垫' or zjppp = '扶手后60' or zjppp = '中头枕后60' or zjppp = '侧头枕后40' or zjppp = '侧头枕后60' ) ORDER BY abb,resultljh desc";//DESC DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { DataTable dt2 = dt; list = new List <View_px_AllList_DoHis>(); foreach (DataRow row in dt2.Rows) { View_px_AllList_DoHis model = new View_px_AllList_DoHis(); model.车身号 = DataHelper.GetCellDataToStr(row, "车身号"); model.主副驾 = DataHelper.GetCellDataToStr(row, "主副驾"); model.零件号描述 = DataHelper.GetCellDataToStr(row, "零件号描述"); model.IsPrint = DataHelper.GetCellDataToStr(row, "IsPrint"); model.OrderIsHistory = Convert.ToInt32(DataHelper.GetCellDataToStr(row, "OrderIsHistory")); model.abb = DataHelper.GetCellDataToStr(row, "abb"); model.resultljh = DataHelper.GetCellDataToStr(row, "resultljh"); list.Add(model); } } return(list); }
public static List <mg_allpartModel> QueryListForPaging(string page, string pagesize, out string total) { total = "0"; List <mg_allpartModel> list = null; string sql1 = @"select count(all_id) total from [mg_allpart];"; string sql2 = @" SELECT top " + pagesize + @" [all_id] ,[all_no] ,[all_rateid] ,r.prop_name [all_ratename] ,[all_colorid] ,c.prop_name [all_colorname] ,[all_metaid] ,m.prop_name [all_metaname] ,[all_desc] FROM [mg_allpart] a left join mg_Property r on a.all_rateid=r.prop_id left join mg_Property c on a.all_colorid=c.prop_id left join mg_Property m on a.all_metaid=m.prop_id where a.all_id < ( select top 1 all_id from (select top ((" + page + @"-1)*" + pagesize + @") all_id from [mg_allpart] where all_id is not null order by all_id desc )t order by all_id ) order by a.all_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_allpartModel>(); foreach (DataRow row in dt2.Rows) { mg_allpartModel model = new mg_allpartModel(); model.all_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "all_id")); model.all_no = DataHelper.GetCellDataToStr(row, "all_no"); model.all_rateid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "all_rateid")); model.all_ratename = DataHelper.GetCellDataToStr(row, "all_ratename"); model.all_colorid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "all_colorid")); model.all_colorname = DataHelper.GetCellDataToStr(row, "all_colorname"); model.all_metaid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "all_metaid")); model.all_metaname = DataHelper.GetCellDataToStr(row, "all_metaname"); model.all_desc = DataHelper.GetCellDataToStr(row, "all_desc"); list.Add(model); } } return(list); }
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 = @" with data as ( select allp.all_id,allp.all_no,aprel.partid_id from [mg_allpart_part_rel] aprel left join [mg_allpart] allp on aprel.all_id=allp.all_id ) SELECT top " + pagesize + @" [part_id] ,[part_no] ,[part_name] ,[part_desc] ,[part_categoryid] ,prop.prop_name [part_Category] ,STUFF((SELECT ','+cast (all_id as varchar) from data allp where allp.partid_id=p.part_id for xml path('')),1,1,'')allpartIDs ,STUFF((SELECT ','+cast (all_no as varchar) from data allp where allp.partid_id=p.part_id for xml path('')),1,1,'')allpartNOs FROM [mg_part] p left join mg_Property prop on p.part_categoryid = prop.prop_id where p.part_id < ( select top 1 part_id from (select top ((" + page + @"-1)*" + pagesize + @") part_id from [mg_part] where part_id is not null order by part_id desc )t order by part_id ) order by 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, "part_id")); 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.part_categoryid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "part_categoryid")); model.part_Category = DataHelper.GetCellDataToStr(row, "part_Category"); model.allpartIDs = DataHelper.GetCellDataToStr(row, "allpartIDs"); model.allpartNOs = DataHelper.GetCellDataToStr(row, "allpartNOs"); list.Add(model); } } return(list); }
public static List <px_PrintModel> Querypx_PrintList() { List <px_PrintModel> list = null; string sql2 = @" SELECT [id] ,[PXID] ,[orderid] ,[cartype] ,[XF] ,[LingjianHao] ,[sum] ,[ordername] ,[dayintime] ,[printpxid] ,[resultljh] ,[isauto] ,[SFlag] FROM [px_Print] order by id asc "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null); if (DataHelper.HasData(dt)) { DataTable dt2 = dt; list = new List <px_PrintModel>(); foreach (DataRow row in dt2.Rows) { px_PrintModel model = new px_PrintModel(); model.id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "id")); model.isauto = DataHelper.GetCellDataToStr(row, "isauto"); model.PXID = DataHelper.GetCellDataToStr(row, "PXID"); model.orderid = DataHelper.GetCellDataToStr(row, "orderid"); model.cartype = DataHelper.GetCellDataToStr(row, "cartype"); model.XF = DataHelper.GetCellDataToStr(row, "XF"); model.sum = DataHelper.GetCellDataToStr(row, "sum"); model.ordername = DataHelper.GetCellDataToStr(row, "ordername"); model.LingjianHao = DataHelper.GetCellDataToStr(row, "LingjianHao"); model.dayintime = NumericParse.StringToDateTime(DataHelper.GetCellDataToStr(row, "dayintime")); model.printpxid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "printpxid")); model.resultljh = DataHelper.GetCellDataToStr(row, "resultljh"); model.SFlag = DataHelper.GetCellDataToStr(row, "SFlag"); list.Add(model); } } return(list); }
public static List <mg_allpartModel> QueryAllPartListForPart() { List <mg_allpartModel> list = null; string sql2 = @" SELECT [all_id] ,[all_no] ,[all_rateid] ,r.prop_name [all_ratename] ,[all_colorid] ,c.prop_name [all_colorname] ,[all_metaid] ,m.prop_name [all_metaname] ,[all_desc] FROM [mg_allpart] a left join mg_Property r on a.all_rateid=r.prop_id left join mg_Property c on a.all_colorid=c.prop_id left join mg_Property m on a.all_metaid=m.prop_id order by a.all_no "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null); if (DataHelper.HasData(dt)) { DataTable dt2 = dt; list = new List <mg_allpartModel>(); foreach (DataRow row in dt2.Rows) { mg_allpartModel model = new mg_allpartModel(); model.all_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "all_id")); model.all_no = DataHelper.GetCellDataToStr(row, "all_no"); model.all_rateid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "all_rateid")); model.all_ratename = DataHelper.GetCellDataToStr(row, "all_ratename"); model.all_colorid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "all_colorid")); model.all_colorname = DataHelper.GetCellDataToStr(row, "all_colorname"); model.all_metaid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "all_metaid")); model.all_metaname = DataHelper.GetCellDataToStr(row, "all_metaname"); model.all_desc = DataHelper.GetCellDataToStr(row, "all_desc"); list.Add(model); } } return(list); }
public static List <mg_partModel> QueryPartListForBOM() { List <mg_partModel> list = null; string sql2 = @" with data as ( select allp.all_id,allp.all_no,aprel.partid_id from [mg_allpart_part_rel] aprel left join [mg_allpart] allp on aprel.all_id=allp.all_id ) SELECT [part_id] ,[part_no] ,[part_name] ,[part_desc] ,[part_categoryid] ,prop.prop_name [part_Category] ,STUFF((SELECT ','+cast (all_id as varchar) from data allp where allp.partid_id=p.part_id for xml path('')),1,1,'')allpartIDs ,STUFF((SELECT ','+cast (all_no as varchar) from data allp where allp.partid_id=p.part_id for xml path('')),1,1,'')allpartNOs FROM [mg_part] p left join mg_Property prop on p.part_categoryid = prop.prop_id order by part_no "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null); if (DataHelper.HasData(dt)) { list = new List <mg_partModel>(); foreach (DataRow row in dt.Rows) { mg_partModel model = new mg_partModel(); model.part_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "part_id")); 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.part_categoryid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "part_categoryid")); model.part_Category = DataHelper.GetCellDataToStr(row, "part_Category"); model.allpartIDs = DataHelper.GetCellDataToStr(row, "allpartIDs"); model.allpartNOs = DataHelper.GetCellDataToStr(row, "allpartNOs"); list.Add(model); } } return(list); }
public static List <mg_PositionModel> QueryPositionsForUser() { List <mg_PositionModel> list = null; string sql = @"SELECT [posi_id],[posi_name] FROM [Sys_RoleInfo] order by posi_name "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { list = new List <mg_PositionModel>(); foreach (DataRow row in dt.Rows) { mg_PositionModel model = new mg_PositionModel(); model.posi_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "posi_id")); model.posi_name = DataHelper.GetCellDataToStr(row, "posi_name"); list.Add(model); } } return(list); }
public static List <px_ShowChiClientModel> QueryListForPaging(string page, string pagesize, out string total) { total = "0"; List <px_ShowChiClientModel> list = null; string sql1 = @"select count(SID) total from [px_ShowChiClient];"; string sql2 = @" SELECT top " + pagesize + @" [SID] ,[SName] ,[ClientIP],[SAddTime],[SRole],[SRamark] FROM [px_ShowChiClient] where SID > ( select top 1 SID from (select top ((" + page + @"-1)*" + pagesize + @") SID from [px_ShowChiClient] where SID is not null order by SID asc )t order by SID desc) order by SID asc "; 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 <px_ShowChiClientModel>(); foreach (DataRow row in dt2.Rows) { px_ShowChiClientModel model = new px_ShowChiClientModel(); model.SID = Convert.ToInt32(DataHelper.GetCellDataToStr(row, "SID")); model.SName = DataHelper.GetCellDataToStr(row, "SName"); model.ClientIP = DataHelper.GetCellDataToStr(row, "ClientIP"); model.SAddTime = DataHelper.GetCellDataToStr(row, "SAddTime"); model.SRole = DataHelper.GetCellDataToStr(row, "SRole"); model.SRamark = DataHelper.GetCellDataToStr(row, "SRamark"); list.Add(model); } } return(list); }
public static List <px_InternetPrinterModel> QueryListForPaging(string page, string pagesize, out string total) { total = "0"; List <px_InternetPrinterModel> list = null; string sql1 = @"select count(IID) total from [px_InternetPrinter];"; string sql2 = @" SELECT top " + pagesize + @" [IID] ,[IName] ,[PrintIP] , [IAddTime], [IRole], [IRamark] FROM [px_InternetPrinter] where IID > ( select top 1 IID from (select top ((" + page + @"-1)*" + pagesize + @") IID from [px_InternetPrinter] where IID is not null order by IID asc )t order by IID desc) order by IID asc "; 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 <px_InternetPrinterModel>(); foreach (DataRow row in dt2.Rows) { px_InternetPrinterModel model = new px_InternetPrinterModel(); model.IID = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "IID")); model.IName = DataHelper.GetCellDataToStr(row, "IName"); model.PrintIP = DataHelper.GetCellDataToStr(row, "PrintIP"); model.IAddTime = DataHelper.GetCellDataToStr(row, "IAddTime"); model.IRole = DataHelper.GetCellDataToStr(row, "IRole"); model.IRamark = DataHelper.GetCellDataToStr(row, "IRamark"); list.Add(model); } } return(list); }
public static string AssemblingOrderSqlStr(DataTable dt2, DataTable orderDT, int XLSType, string tableName, string columnIndexStr, string pre) { //string fsdSql1 = @"ISNULL((select max($columnIndexStr$id) from [$tableName$] where co_id=$co_id$ and $columnIndexStr$createdate between '$date$ 00:00:00' and '$date$ 23:59:59'),0)+$index$"; string fsdSql1 = @"ISNULL((select max($columnIndexStr$id) from [$tableName$] where $columnIndexStr$createdate between '$date$ 00:00:00' and '$date$ 23:59:59'),0)+$index$"; string fsdSql2 = @"insert into $tableName$($columnIndexStr$id,co_id,part_no,$columnIndexStr$createdate,$columnIndexStr$cdstr,$columnIndexStr$unproducing,$columnIndexStr$producing,$columnIndexStr$finish,$columnIndexStr$pre)values"; string fsdSql3 = @"($ofsd_id$,$co_id$,'$part_no$','$ofsd_createdate$','$ofsd_cdstr$',1,0,0,'$pre$');"; StringBuilder sqlStr = new StringBuilder(); //几种车型配置 for (int i = 0; i < dt2.Rows.Count; i++) { DataRow dr = dt2.Rows[i]; string all_id = DataHelper.GetCellDataToStr(dr, "all_id"); int co_count = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dr, "co_count")); //FSD DataRow rowsFSD = orderDT.Select(" all_id=" + all_id + " and co_count=" + co_count + " and part_categoryid=" + XLSType)[0]; for (int j = 0; j < co_count; j++) { string co_id = DataHelper.GetCellDataToStr(rowsFSD, "co_id"); string currentDate = DateTime.Now.ToString("yyyy-MM-dd"); string fsdSql1_t = fsdSql1.Replace("$date$", currentDate).Replace("$index$", "1").Replace("$co_id$", co_id).Replace("$tableName$", tableName).Replace("$columnIndexStr$", columnIndexStr); string fsdSql2_t = fsdSql2.Replace("$tableName$", tableName).Replace("$columnIndexStr$", columnIndexStr); string part_no = DataHelper.GetCellDataToStr(rowsFSD, "part_no").Trim(); string currentDateTime = DateTime.Now.ToString(); string currentDateShort = DateTime.Now.ToString("yyyyMMdd").Substring(2); string fsdSql3_t = fsdSql3.Replace("$ofsd_id$", fsdSql1_t).Replace("$co_id$", co_id.ToString()).Replace("$part_no$", part_no).Replace("$ofsd_createdate$", currentDateTime).Replace("$ofsd_cdstr$", currentDateShort).Replace("$pre$", pre); sqlStr.Append(fsdSql2_t); sqlStr.Append(fsdSql3_t); } } string s = sqlStr.ToString(); return(s); }
public static mg_stationModel GetStationByMac(string mac) { string sql2 = @" SELECT top 1 [st_id] ,st.[fl_id] ,fl.fl_name ,[st_no] ,[st_name] ,[st_mac] ,[st_typeid] ,p.prop_name [st_typename] ,[st_order] FROM [mg_station] st left join mg_FlowLine fl on st.fl_id = fl.fl_id left join mg_Property p on st.st_typeid=p.prop_id where st.st_mac='" + mac + @"' "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null); if (DataHelper.HasData(dt)) { mg_stationModel model = new mg_stationModel(); foreach (DataRow row in dt.Rows) { model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id")); model.fl_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "fl_id")); model.fl_name = DataHelper.GetCellDataToStr(row, "fl_name"); model.st_no = DataHelper.GetCellDataToStr(row, "st_no"); model.st_name = DataHelper.GetCellDataToStr(row, "st_name"); model.st_mac = DataHelper.GetCellDataToStr(row, "st_mac"); model.st_typeid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_typeid")); model.st_typename = DataHelper.GetCellDataToStr(row, "st_typename"); model.st_order = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_order")); break; } return(model); } return(null); }
public static List <mg_PropertyModel> queryJSONStringByPropertyType(mg_PropertyEnum propEnum) { List <mg_PropertyModel> list = null; string sql = @"SELECT [prop_id] ,[Prop_type] ,[prop_name] FROM [mg_Property] where Prop_type=" + (int)propEnum + @" order by prop_name"; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { list = new List <mg_PropertyModel>(); foreach (DataRow row in dt.Rows) { mg_PropertyModel model = new mg_PropertyModel(); model.prop_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "prop_id")); model.prop_name = DataHelper.GetCellDataToStr(row, "prop_name"); list.Add(model); } } return(list); }
public static List <mg_partModel> queryPartForStepSearching() { List <mg_partModel> list = null; string sql2 = @" SELECT [part_id] ,[part_no] ,[part_name] ,[part_desc] ,[part_categoryid] FROM [mg_part] p order by part_no "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql2, null); if (DataHelper.HasData(dt)) { list = new List <mg_partModel>(); foreach (DataRow row in dt.Rows) { mg_partModel model = new mg_partModel(); model.part_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "part_id")); model.part_no = DataHelper.GetCellDataToStr(row, "part_no") + " | " + DataHelper.GetCellDataToStr(row, "part_name"); model.part_name = DataHelper.GetCellDataToStr(row, "part_name"); model.part_desc = DataHelper.GetCellDataToStr(row, "part_desc"); list.Add(model); } mg_partModel firstmodel = new mg_partModel(); firstmodel.part_id = 0; firstmodel.part_no = "-- 部件(全部) --"; list.Insert(0, firstmodel); } return(list); }
public static Model.mg_userModel GetUserForUID(string uid) { string sql = @" select * from Sys_UserInfo where user_id='" + uid + "' "; DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null); if (DataHelper.HasData(dt)) { foreach (DataRow row in dt.Rows) { mg_userModel model = new mg_userModel(); model.user_id = DataHelper.GetCellDataToStr(row, "user_id"); model.user_pwd = DataHelper.GetCellDataToStr(row, "user_pwd"); model.user_name = DataHelper.GetCellDataToStr(row, "user_name"); model.user_no = DataHelper.GetCellDataToStr(row, "user_no"); model.user_pic = DataHelper.GetCellDataToStr(row, "user_pic"); model.user_email = DataHelper.GetCellDataToStr(row, "user_email"); model.user_depid = Convert.ToInt32(DataHelper.GetCellDataToStr(row, "user_depid")); model.user_posiid = Convert.ToInt32(DataHelper.GetCellDataToStr(row, "user_posiid")); model.user_menuids = DataHelper.GetCellDataToStr(row, "user_menuids"); return(model); } } return(null); }