/// <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);
}
