public static List <mg_userModel> QueryListForFirstPage(string currentpage, string pagesize, out string total)
{
total = "0";
List <mg_userModel> list = null;
string sql1 = @"select count(1) total from [Sys_UserInfo];";
string sqlselect = @"select top " + pagesize + @" A.*
from (
select ROW_NUMBER() over(order by user_no) as rowid ,
[user_name]
,[user_pwd]
,[user_email]
,[user_depid]
,[user_posiid]
,[user_no]
,d.dep_name user_depid_name
,p.posi_name user_posiid_name
,[user_menuids]
,case [user_sex]
when 1 then '男'
else '女'
end user_sex_name
,[user_sex]
,case [user_isAdmin]
when 1 then '是'
else '否'
end user_isAdmin_name
,[user_isAdmin]
from
[Sys_UserInfo] u
left join Sys_DeptInfo d on u.user_depid = d.dep_id
left join Sys_RoleInfo p on u.user_posiid = p.posi_id
where u.active_flag='0')as A
where rowid> (" + pagesize + ")*((" + currentpage + ")-1)";
// string sql2 = @" SELECT top " + pagesize + @"
// [user_name]
// ,[user_pwd]
// ,[user_email]
// ,[user_depid]
// ,[user_posiid]
// ,[user_no]
// ,d.dep_name user_depid_name
// ,p.posi_name user_posiid_name
// ,[user_menuids]
// ,case [user_sex]
// when 1 then '男'
// else '女'
// end user_sex_name
// ,[user_sex]
// ,case [user_isAdmin]
// when 1 then '是'
// else '否'
// end user_isAdmin_name
// ,[user_isAdmin]
// FROM [Sys_UserInfo] u
// left join Sys_DeptInfo d on u.user_depid = d.dep_id
// left join Sys_RoleInfo p on u.user_posiid = p.posi_id
//
// order by [user_no] desc
// ";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql1 + sqlselect, 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_userModel>();
foreach (DataRow row in dt2.Rows)
{
mg_userModel model = new mg_userModel();
//model.user_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "user_id"));
model.user_no = DataHelper.GetCellDataToStr(row, "user_no") == "NULL" ? "" : DataHelper.GetCellDataToStr(row, "user_no");
model.user_name = DataHelper.GetCellDataToStr(row, "user_name");
model.user_pwd = DataHelper.GetCellDataToStr(row, "user_pwd");
model.user_email = DataHelper.GetCellDataToStr(row, "user_email");
model.user_depid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "user_depid"));
model.user_posiid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "user_posiid"));
model.user_posiid_name = DataHelper.GetCellDataToStr(row, "user_posiid_name");
model.user_depid_name = DataHelper.GetCellDataToStr(row, "user_depid_name");
model.user_menuids = DataHelper.GetCellDataToStr(row, "user_menuids");
model.user_sex_name = DataHelper.GetCellDataToStr(row, "user_sex_name");
model.user_sex = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "user_sex"));
model.user_isAdmin_name = DataHelper.GetCellDataToStr(row, "user_isAdmin_name");
model.user_isAdmin = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "user_isAdmin"));
list.Add(model);
}
}
return(list);
}
/// <summary>
/// 修改时间：2017年5月9日
/// 修改人：冉守旭
/// 修改内容：列表显示多个工位号
/// </summary>
/// <param name="pagesize"></param>
/// <param name="total"></param>
/// <returns></returns>
public static List <mg_OperatorModel> QueryListForFirstPage(string pagesize, out string total)
{
total = "0";
List <mg_OperatorModel> list = null;
string sql1 = @"select count(op_id) total from [mg_Operator];";
string sql2 = @" SELECT top " + pagesize + @" [op_id]
,op.[st_id]
,st.st_name
,[op_name]
,[op_no]
,[op_pic]
,[op_isoperator]
,case [op_isoperator]
when 1 then '是'
else '否'
end op_isoperator_name
,[op_mac]
,[op_sex]
,case [op_sex]
when 1 then '男'
else '女'
end op_sex_name
FROM [mg_Operator] op
left join mg_station st on op.st_id=st.st_id
order by op_id desc;";
string sql3 = @"SELECT [st_no],max(st_mac) as st_mac,max(st_id) as st_id
FROM [mg_station] where st_mac!='' and st_mac is not null
group by st_no order by st_no ";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql1 + sql2 + sql3, new string[] { "count", "data", "station" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = DataHelper.GetCellDataToStr(dt1.Rows[0], "total");
DataTable dt2 = ds.Tables["data"];
DataTable dt3 = ds.Tables["station"];
list = new List <mg_OperatorModel>();
foreach (DataRow row in dt2.Rows)
{
mg_OperatorModel model = new mg_OperatorModel();
model.op_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "op_id"));
model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id"));
model.st_name = DataHelper.GetCellDataToStr(row, "st_name");
model.op_name = DataHelper.GetCellDataToStr(row, "op_name");
string no = DataHelper.GetCellDataToStr(row, "op_no");
model.op_no = no.Substring(no.Length - 7);
model.op_pic = DataHelper.GetCellDataToStr(row, "op_pic");
model.op_isoperator = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "op_isoperator"));
model.op_isoperator_name = DataHelper.GetCellDataToStr(row, "op_isoperator_name");
model.op_mac = DataHelper.GetCellDataToStr(row, "op_mac");
model.op_sex = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "op_sex"));
model.op_sex_name = DataHelper.GetCellDataToStr(row, "op_sex_name");
string[] strArray = model.op_mac.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries); //字符串转数组
string list_st_no = "";
string list_st_id = "";
for (int i = 0; i < strArray.Length; i++)
{
DataRow[] arrayDR = dt3.Select("st_mac='" + strArray[i] + "'");//获取工位号对应的mac地址信息
if (arrayDR.Count() > 0)
{
list_st_no += arrayDR[0][0].ToString() + ",";
list_st_id += arrayDR[0][2].ToString() + ",";
}
}
model.list_st_no = list_st_no.Length > 0 ? list_st_no.Substring(0, list_st_no.Length - 1) : list_st_no;
model.list_st_id = list_st_id.Length > 0 ? list_st_id.Substring(0, list_st_id.Length - 1) : list_st_id;
list.Add(model);
}
}
return(list);
}
public static List <px_PanrameterModel> QueryListForFirstPage(string pagesize, out string total)
{
total = "0";
List <px_PanrameterModel> list = null;
string sql1 = @"select count(SerialID) total from [px_Panrameter];";
string sql2 = @"
SELECT top " + pagesize + @" [SerialID]
,[Name]
,[Number]
FROM [px_Panrameter]
order by SerialID 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_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"));
var sendbtnvalue = "";
var printbtnvalue = "";
var ascdescbtnvalue = "";
if (GetBtnClass.BtnClassList == null)
{
GetBtnClass gbc = new GetBtnClass();
// GetBtnClass.BtnClassList = new List<OrderList>(GetBtnClass.BtnClassList);
}
else
{
GetBtnClass.BtnClassList = new List <OrderList>(GetBtnClass.BtnClassList);
}
foreach (var item in GetBtnClass.BtnClassList)
{
if (item.SortName.Equals(model.Name))
{
sendbtnvalue = item.IsAutoSend ? "开启" : "关闭";
printbtnvalue = item.IsAutoPrint ? "开启" : "关闭";
ascdescbtnvalue = item.Ascordesc ? "开启" : "关闭";
model.IsAutoPrint = item.IsAutoPrint;
model.IsAutoSend = item.IsAutoSend;
model.Ascordesc = item.Ascordesc;
}
}
model.IsAutoPrint1 = printbtnvalue;
model.IsAutoSend1 = sendbtnvalue;
model.Ascordesc1 = ascdescbtnvalue;
list.Add(model);
}
}
return(list);
}
public static List <mg_StepModel> QueryListForFirstPage(string currentpage, string pagesize, out string total, string fl_id, string st_id, string part_id)
{
total = "0";
List <mg_StepModel> list = null;
string queryStr = (!string.IsNullOrEmpty(fl_id) && fl_id != "0") ? " where st.fl_id=" + fl_id : " where step.step_id is not null ";
queryStr += (!string.IsNullOrEmpty(st_id) && st_id != "0") ? " and step.st_id=" + st_id : " ";
queryStr += (!string.IsNullOrEmpty(part_id) && part_id != "0") ? " and step.part_id=" + part_id : " ";
string sql1 = @"select count(step_id) total from [mg_step] step left join mg_station st on step.st_id = st.st_id " + queryStr + @" ;";
string sql2 = @"
select top " + pagesize + @" A.*
from (
select ROW_NUMBER() over(order by step.step_order) as rowid ,
[step_id]
,[step_name]
,st.[fl_id]
,fl.fl_name
,step.[st_id]
,st.st_name
,step.[bom_id]
,b.bom_PN
,step.[part_id]
,p.part_no
,p.part_name
,b.bom_desc
,[bom_count]
,[step_clock]
,[step_desc]
,[step_pic]
,[step_plccode]
,[step_order]
,b.[barcode_start]
,b.[barcode_number]
FROM [mg_step] step
left join mg_station st on step.st_id = st.st_id
left join mg_FlowLine fl on st.fl_id = fl.fl_id
left join mg_BOM b on step.bom_id=b.bom_id
left join mg_part p on step.part_id=p.part_id
" + queryStr + @") as A
where rowid> (" + pagesize + ")*((" + currentpage + ")-1)";
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_StepModel>();
foreach (DataRow row in dt2.Rows)
{
mg_StepModel model = new mg_StepModel();
model.step_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "step_id"));
model.step_name = DataHelper.GetCellDataToStr(row, "step_name");
model.fl_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "fl_id"));
model.fl_name = DataHelper.GetCellDataToStr(row, "fl_name");
model.st_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_id"));
model.st_name = DataHelper.GetCellDataToStr(row, "st_name");
model.bom_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_id"));
model.bom_PN = DataHelper.GetCellDataToStr(row, "bom_PN") + " | " + DataHelper.GetCellDataToStr(row, "bom_desc");
model.bom_desc = DataHelper.GetCellDataToStr(row, "bom_desc");
model.bom_count = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_count"));
model.step_clock = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "step_clock"));
model.step_desc = DataHelper.GetCellDataToStr(row, "step_desc");
model.step_pic = DataHelper.GetCellDataToStr(row, "step_pic");
model.step_plccode = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "step_plccode"));
model.step_order = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "step_order"));
model.barcode_start = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "barcode_start"));
model.barcode_number = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "barcode_number"));
model.part_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "part_id"));
model.part_no = DataHelper.GetCellDataToStr(row, "part_no") + " | " + DataHelper.GetCellDataToStr(row, "part_name");
list.Add(model);
}
}
return(list);
}
public static List <mg_stationModel> QueryListForFirstPage(string pagesize, out string total, string fl_id)
{
total = "0";
List <mg_stationModel> list = null;
string queryStr = (!string.IsNullOrEmpty(fl_id) && fl_id != "0") ? " where st.fl_id=" + fl_id : "";
string sql1 = @"select count(st_id) total from [mg_station] st " + queryStr + @" ;";
string sql2 = @"
SELECT top " + pagesize + @" [st_id]
,st.[fl_id]
,fl.fl_name
,[st_no]
,[st_name]
,[st_mac]
,[st_typeid]
,p.prop_name [st_typename]
,[st_order]
,[st_odsfile]
,[st_mushifile]
,[st_QualityAlarm]
,st_isfirst
,CASE st_isfirst WHEN 1 THEN '是' WHEN 0 THEN '否' END AS st_isfirstname
,st_isend
,CASE st_isend WHEN 1 THEN '是' WHEN 0 THEN '否' END AS st_isendname
,[st_clock_Start]
,[st_clock]
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
" + queryStr + @"
order by st_order
";
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_stationModel>();
foreach (DataRow row in dt2.Rows)
{
mg_stationModel model = new mg_stationModel();
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"));
model.st_odsfile = DataHelper.GetCellDataToStr(row, "st_odsfile");
model.st_mushifile = DataHelper.GetCellDataToStr(row, "st_mushifile");
string pdf = DataHelper.GetCellDataToStr(row, "st_odsfile");
model.st_odsfilename = (!string.IsNullOrEmpty(pdf)) ? pdf.Substring(pdf.LastIndexOf('/') + 1) : "";
string alarmpdf = DataHelper.GetCellDataToStr(row, "st_QualityAlarm");
model.st_alarmfile = alarmpdf;
model.st_alarmfilename = (!string.IsNullOrEmpty(alarmpdf)) ? alarmpdf.Substring(alarmpdf.LastIndexOf('/') + 1) : "";
model.st_isfirst = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_isfirst"));
model.st_isend = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_isend"));
model.st_isendname = DataHelper.GetCellDataToStr(row, "st_isendname");
model.st_isfirstname = DataHelper.GetCellDataToStr(row, "st_isfirstname");
model.st_clock_Start = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_clock_Start"));
model.st_clock = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "st_clock"));
list.Add(model);
}
}
return(list);
}
public static List <mg_BOMModel> QueryListForFirstPage(string pagesize, out string total)
{
total = "0";
List <mg_BOMModel> list = null;
string sql1 = @"select count([bom_id]) total from [mg_BOM];";
string sql2 = @"
with data as
(
select p.part_id,p.part_no,pbrel.bom_id from mg_part_bom_rel pbrel left join mg_part p on pbrel.part_id=p.part_id
)
SELECT top " + pagesize + @" [bom_id]
,[bom_PN]
,[bom_customerPN]
,[bom_isCustomerPN]
,case [bom_isCustomerPN]
when 1 then '是'
else '否'
end bom_isCustomerPNName
,[bom_leve]
,l.prop_name [bom_leveName]
,[bom_materialid]
,m.prop_name [bom_material]
,[bom_suppllerid]
,ms.SupplierName [bom_suppller]
,[bom_categoryid]
,ca.prop_name [bom_category]
,[bom_storeid]
,st.prop_name [bom_storeName]
,[bom_colorid]
,mm.Prop_type [bom_proptype]
,mm.prop_name [bom_propname]
,mm.prop_id [bom_propid]
,co.prop_name [bom_colorname]
,[bom_profile]
,[bom_weight]
,[bom_desc]
,[bom_descCH]
,[bom_picture]
,STUFF((SELECT ','+cast (part_id as varchar) from data p where p.bom_id=b.bom_id for xml path('')),1,1,'')partIDs
,STUFF((SELECT ','+cast (part_no as varchar) from data p where p.bom_id=b.bom_id for xml path('')),1,1,'')partNOs
,b.barcode_start
,b.barcode_number
FROM [mg_BOM] b
left join mg_Property m on b.bom_materialid=m.prop_id
left join mg_Property s on b.bom_suppllerid=s.prop_id
left join mg_Property ca on b.bom_categoryid=ca.prop_id
left join mg_Property co on b.bom_colorid=co.prop_id
left join Mat_SupplierList ms on b.bom_suppllerid=ms.SupplierID
left join Mg_MaterialProperty mm on ms.WareHouseID=mm.prop_id
left join mg_Property l on b.bom_leve=l.prop_id
left join mg_Property st on b.bom_storeid=st.prop_id
order by b.bom_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_BOMModel>();
foreach (DataRow row in dt2.Rows)
{
mg_BOMModel model = new mg_BOMModel();
model.bom_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_id"));
model.bom_isCustomerPN = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_isCustomerPN"));
model.bom_colorid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_colorid"));
model.bom_materialid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_materialid"));
model.bom_categoryid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_categoryid"));
model.bom_suppllerid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_suppllerid"));
model.bom_leve = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_leve"));
model.bom_weight = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_weight"));
model.partIDs = DataHelper.GetCellDataToStr(row, "partIDs");
model.bom_picture = DataHelper.GetCellDataToStr(row, "bom_picture");
model.bom_PN = DataHelper.GetCellDataToStr(row, "bom_PN");
model.bom_customerPN = DataHelper.GetCellDataToStr(row, "bom_customerPN");
model.bom_isCustomerPNName = DataHelper.GetCellDataToStr(row, "bom_isCustomerPNName");
model.bom_colorname = DataHelper.GetCellDataToStr(row, "bom_colorname");
model.bom_material = DataHelper.GetCellDataToStr(row, "bom_material");
model.bom_category = DataHelper.GetCellDataToStr(row, "bom_category");
model.bom_suppller = DataHelper.GetCellDataToStr(row, "bom_suppller");
model.bom_profile = DataHelper.GetCellDataToStr(row, "bom_profile");
model.bom_desc = DataHelper.GetCellDataToStr(row, "bom_desc");
model.bom_descCH = DataHelper.GetCellDataToStr(row, "bom_descCH");
model.partNOs = DataHelper.GetCellDataToStr(row, "partNOs");
model.bom_leveName = DataHelper.GetCellDataToStr(row, "bom_leveName");
model.bom_storeid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_storeid"));
model.bom_storeName = DataHelper.GetCellDataToStr(row, "bom_storeName");
model.barcode_start = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "barcode_start"));
model.barcode_number = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "barcode_number"));
model.bom_propid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_propid"));
model.bom_propname = DataHelper.GetCellDataToStr(row, "bom_propname");
list.Add(model);
}
}
return(list);
}
public static DataTable getTableExcel(string SortFlag, string sortOrder, string wherestr, out int total)
{
if (string.IsNullOrEmpty(SortFlag))
{
SortFlag = "OrderID";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "asc";
}
StringBuilder commandText = new StringBuilder();
string str = @" select
a.OrderID,
a.CustomerNumber,
a.JITCallNumber,
a.SerialNumber,
a.SerialNumber_MES,
a.VinNumber,
a.PlanDeliverTime,
a.CreateTime,
case when mg_PartOrder1.OrderType = 1 then 'DelJit订单' when mg_PartOrder1.OrderType = 2 then 'SAP订单'
when mg_PartOrder1.OrderType = 3 then '紧急插单'
when mg_PartOrder1.OrderType = 4 then '拆解回流订单'
else ' ' end as OrderType,
case when mg_PartOrder1.CompletionState = 1 then '未拆分' when mg_PartOrder1.CompletionState = 2 then '未下发' when mg_PartOrder1.CompletionState = 3 then '已下发'
when mg_PartOrder1.CompletionState = 4 then '生产中'
when mg_PartOrder1.CompletionState = 5 then '已完成'
when mg_PartOrder1.CompletionState = 6 then '已拆解'
when mg_PartOrder1.CompletionState = 7 then '返修下线'
else ' ' end as OrderState,
c.ProductName as ProductName,
a.OrderIsHistory
,mg_FlowLine1.flowflag+mg_part1.part_no+substring(convert(char(8),a.CreateTime,112),3,6)+stuff('0000',5-Len(SerialNumber_MES),Len(SerialNumber_MES),SerialNumber_MES) as MES_ORDER
from mg_CustomerOrder_3 a
left join mg_Customer_Product b
on b.CustomerOrderID = a.OrderID
left join mg_Product c
on c.ID = b.ProductID
left join mg_PartOrder mg_PartOrder1 on mg_PartOrder1.CustomerProductID = b.ID
left join mg_part mg_part1 on mg_PartOrder1.PartID = mg_part1.part_id
left join mg_FlowLine mg_FlowLine1 on mg_part1.FlowLineID = mg_FlowLine1.fl_id
where 1 = 1 ";
//commandText.Append("select a.OrderID,a.CustomerNumber,a.JITCallNumber,a.SerialNumber,a.SerialNumber_MES,a.VinNumber,a.PlanDeliverTime,a.CreateTime,case when mg_PartOrder1.OrderType = 1 then 'DelJit订单' when mg_PartOrder1.OrderType = 2 then 'SAP订单' else '紧急插单' end as OrderType,case when mg_PartOrder1.CompletionState = 1 then '未拆分' when mg_PartOrder1.CompletionState = 2 then '未下发' when mg_PartOrder1.CompletionState = 3 then '已下发' when mg_PartOrder1.CompletionState = 4 then '生产中' else '已完成' end as OrderState,LEFT(c.ProductName,CHARINDEX('-',ProductName)-1) as ProductName,a.OrderIsHistory from mg_CustomerOrder_3 a left join mg_Customer_Product b on b.CustomerOrderID = a.OrderID left join mg_Product c on c.ID = b.ProductID where c.ProductType = 1 ");
commandText.Append(str);
commandText.Append(wherestr);//这里修改条件语句
commandText.Append(" order by a." + SortFlag + " " + sortOrder);
string query_sql = commandText.ToString();
StringBuilder commandText1 = new StringBuilder();
//commandText1.Append("select a.OrderID,a.CustomerNumber,a.JITCallNumber,a.SerialNumber,a.SerialNumber_MES,a.VinNumber,a.PlanDeliverTime,a.CreateTime,case when mg_PartOrder1.OrderType = 1 then 'DelJit订单' when mg_PartOrder1.OrderType = 2 then 'SAP订单' else '紧急插单' end as OrderType,case when mg_PartOrder1.CompletionState = 1 then '未拆分' when mg_PartOrder1.CompletionState = 2 then '未下发' when mg_PartOrder1.CompletionState = 3 then '已下发' when mg_PartOrder1.CompletionState = 4 then '生产中' else '已完成' end as OrderState,LEFT(c.ProductName,CHARINDEX('-',ProductName)-1) as ProductName,a.OrderIsHistory from mg_CustomerOrder_3 a left join mg_Customer_Product b on b.CustomerOrderID = a.OrderID left join mg_Product c on c.ID = b.ProductID where c.ProductType = 1 ");
commandText1.Append(str);
commandText1.Append(wherestr);//这里修改条件语句
string count_sql = " select count(*) as total from (" + commandText1.ToString() + " ) result ";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, query_sql + count_sql, new string[] { "data", "count" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = NumericParse.StringToInt(DataHelper.GetCellDataToStr(dt1.Rows[0], "total"));
DataTable dt2 = ds.Tables["data"];
return(dt2);
}
else
{
total = 0;
return(null);
}
}
public static List <mg_CustomerOrderModel> QueryListForFirstPage(string pagesize, out string total, string isCutted)
{
total = "0";
List <mg_CustomerOrderModel> list = null;
string queryStr = " where co_isCutted=" + isCutted;
string sql1 = @"select count([co_id]) total from [mg_CustomerOrder] cus " + queryStr + @" ;";
string sql2 = @"
with allPart as
(
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]
FROM [mg_allpart] a
left join mg_Property c on a.all_colorid = c.prop_id
left join mg_Property r on a.all_rateid = r.prop_id
left join mg_Property m on a.all_metaid = m.prop_id
)
SELECT top " + pagesize + @" cus.[co_id]
,cus.[co_no]
, stuff((select '|' +CAST(all_id as varchar(10)) from mg_cusOrder_Allpart_rel where co_id = cus.co_id order by orderno for xml path('')),1,1,'')as all_ids
, stuff((select '|' +CAST(co_count as varchar(10)) from mg_cusOrder_Allpart_rel where co_id = cus.co_id order by orderno for xml path('')),1,1,'')as co_counts
, stuff((select '、' + (all_no+' ('+ap.all_ratename+'+'+ap.all_metaname+'+'+ap.all_colorname+') '+CAST(co_count as varchar(10))+' 套') from allPart ap inner join mg_cusOrder_Allpart_rel ca on ap.all_id=ca.all_id and co_id = cus.co_id order by ca.orderno for xml path('')),1,1,'')as appPartdesc
, stuff((select '|' +CAST(ap.all_id as varchar(10))+','+CAST(co_count as varchar(10))+','+all_no from mg_cusOrder_Allpart_rel car left join allPart ap on ap.all_id=car.all_id where co_id = cus.co_id order by car.orderno for xml path('')),1,1,'')as idcounts
,[co_cutomerid]
,p.prop_name [co_customer]
,[co_state]
,[co_order]
,[co_isCutted]
FROM [mg_CustomerOrder] cus
left join mg_Property p on cus.co_cutomerid = p.prop_id
" + queryStr + @"
order by cus.co_order 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_CustomerOrderModel>();
foreach (DataRow row in dt2.Rows)
{
mg_CustomerOrderModel model = new mg_CustomerOrderModel();
model.co_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "co_id"));
model.co_no = DataHelper.GetCellDataToStr(row, "co_no");
model.co_isCutted = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "co_isCutted"));
model.co_cutomerid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "co_cutomerid"));
model.all_ids = DataHelper.GetCellDataToStr(row, "all_ids");
model.co_counts = DataHelper.GetCellDataToStr(row, "co_counts");
model.appPartdesc = DataHelper.GetCellDataToStr(row, "appPartdesc");
model.idcounts = DataHelper.GetCellDataToStr(row, "idcounts");
list.Add(model);
}
}
return(list);
}
