/// <summary> /// 写入工序临时表 /// </summary> private void SetGX() { string error = string.Empty; string userId = ToolCode.Tool.GetUser().UserNumber; string ids = ToolManager.GetParamsString("Ids"); string sql = string.Format(" delete T_Process_Temp where userId='{0}'", userId); SqlHelper.ExecuteSql(sql, ref error); string tempSql = string.Format(@"select * from ( select t.销售订单号+'^'+t.产品编号+'^'+t.版本+'^'+t.交期+'^'+t.行号 as Id,t.* from ({0} ) t)h where h.Id+'^'+'{2}' in ({1})", WorkOrderManager.GetOrderNofinesfinishedDetail(), ids, userId); tempSql = string.Format(@" select two.*,twt.Qty as 实际生产数量 from ({0})two inner join T_WorkOrder_Temp twt on twt.Id =two.Id +'^'+'{1}' where twt.User_id ='{1}' ", tempSql, userId); sql = string.Format(@" insert into T_Process_Temp(Id,UserId ,Team ,OrdersNumber ,ProductNumber ,Version ,CustomerProductNumber ,NonDeliveryQty , StockQty ,WorkInProgressQty ,NeedToProduceQty,LeadTime ,RowNumber ,WorkSnNumber,WorkSnName,SN,qty) select a.Id ,'{1}','',a.销售订单号,a.产品编号,a.版本,a.客户产品编号,a.未交货数量,a.库存数量,a.在制品数量, a.需要生产数量,a.交期,a.行号,a.工序编号,ws.WorkSnName,a.工序序号,a.实际生产数量 from ( select '{1}'+'-'+t.销售订单号+'-'+t.产品编号+'-'+t.客户产品编号+'-'+t.版本+'-'+t.行号+'-'+ISNULL ( pwsp.WorkSnNumber,'') as Id, t.销售订单号,t.产品编号,t.版本, t.未交货数量,t.库存数量,t.在制品数量,t.需要生产数量,t.交期,t.行号,t.客户产品编号,ISNULL ( pwsp.WorkSnNumber,'') as 工序编号 ,pwsp.RowNumber as 工序序号,t.实际生产数量 from ( {0} )t left join ProductWorkSnProperty pwsp on t.产品编号=pwsp .ProductNumber and t.版本=pwsp .Version where t.需要生产数量>0) a left join WorkSn ws on a.工序编号=ws.WorkSnNumber order by a.销售订单号 asc,a.产品编号,a.版本,a.行号 asc ,a.工序序号 asc ", tempSql, userId); SqlHelper.ExecuteSql(sql, ref error); }
public static string GetProductProcessProductNumber() { string sqlproduct = string.Format(@" select distinct t.产品编号 from (select t.*,so.CustomerId ,p.Description from ({0})t left join SaleOder so on t.销售订单号=so.OdersNumber left join Product p on t.产品编号=p.ProductNumber and t.版本=p.Version ) t left join Customer c on t.CustomerId=c.CustomerId left join ProductCustomerProperty pcp on t.CustomerId=pcp.CustomerId and t.产品编号=pcp.ProductNumber and t.版本=pcp.Version", WorkOrderManager.GetOrderNofinesfinishedDetail()); return(GetOption(sqlproduct, "产品编号", "产品编号")); }
//添加小组临时表信息 private void LoadXZ(bool isZZ, string productId) { string userId = ToolCode.Tool.GetUser().UserNumber; List <string> sqls = new List <string>(); string error = string.Empty; string sql = string.Format(" delete T_XZWorkOrderTemp where UserId='{0}'", userId); sqls.Add(sql); sql = string.Format(@"select t.销售订单号+'^'+t.产品编号+'^'+t.版本+'^'+t.交期+'^'+t.行号+'^'+'{1}' as Id, t.销售订单号,t.产品编号,t.版本, t.未交货数量,t.库存数量,t.在制品数量,t.需要生产数量,t.交期,t.行号,t.客户产品编号 from ({0})t where t.需要生产数量>0", WorkOrderManager.GetOrderNofinesfinishedDetail(), userId); sql = string.Format(@" insert into T_XZWorkOrderTemp(Id,OrdersNumber,ProductNumber,Version ,NonDeliveryQty,StockQty,WorkInProgressQty,NeedToProduceQty,LeadTime,RowNumber,CustomerProductNumber,UserId) select *,'{2}' from ({0})t where t.Id in ({1})", sql, productId, userId); sqls.Add(sql); SqlHelper.BatchExecuteSql(sqls, ref error); Response.Write(LoadData(isZZ)); Response.End(); return; }
public static void GetPageOperation(string btnId) { string tempSql = WorkOrderManager.GetOrderNofinesfinishedDetail(); string sql = string.Format(@" select t.销售订单号,t.CustomerOrderNumber as 客户采购订单号,t.OdersType as 订单类型, pcp.CustomerProductNumber as 客户产成品编号,t.产品编号,t.版本,t.订单数量, t.已交货数量, case when t.未交货数量 <0 then 0 else t.未交货数量 end as 未交货数量 ,t.库存数量,t.在制品数量, case when t.未交货数量 <0 then 0-t.库存数量-isnull(noAddQty.qty,0)-producting.productQty else t.未交货数量-t.库存数量-isnull(noAddQty.qty,0)-producting.productQty end as 需要生产数量, t.交期,t.行号,t.Description as 产品描述,c.CustomerName as 客户名称 from ( select t.*,so.CustomerId,so.OdersType ,p.Description,so . CustomerOrderNumber from ({0})t left join SaleOder so on t.销售订单号=so.OdersNumber left join Product p on t.产品编号=p.ProductNumber and t.版本=p.Version ) t left join Customer c on t.CustomerId=c.CustomerId left join ProductCustomerProperty pcp on t.CustomerId=pcp.CustomerId and t.产品编号=pcp.ProductNumber and t.版本=pcp.Version left join ( select ProductNumber,Version,SUM(qty) as qty from ProductWarehouseLogDetail where WarehouseNumber in ( select WarehouseNumber from ProductWarehouseLog where ChangeDirection='入库' and ISNULL(CheckTime,'')='' ) group by ProductNumber,Version) as noAddQty on t.产品编号=noAddQty.ProductNumber and t.版本=noAddQty.Version left join ( select ProductNumber,Version , case when sum(Qty)-SUM(StorageQty)<0 then 0 else sum(Qty)-SUM(StorageQty) end as productQty from ProductPlanDetail group by ProductNumber,Version ) as producting on t.产品编号=producting.ProductNumber and t.版本=producting.Version ", tempSql); if (string.IsNullOrEmpty(tempSql)) { string responseValue1 = "" + "^" + "" + "^" + "" + "^" + "0"; HttpContext.Current.Response.Write(responseValue1); HttpContext.Current.Response.End(); return; } int pageCount = 0; //总页数 int totalRecords = 0; //总行数 string error = string.Empty; string text = string.Empty; string tdTextTemp = string.Empty; string pageIndex = ToolManager.GetQueryString("pageIndex"); string pageSize = ToolManager.GetQueryString("pageSize"); string sortName = ToolManager.GetQueryString("sortName"); string sortDirection = ToolManager.GetQueryString("sortDirection"); string querySql = ToolManager.GetQueryString("querySql"); //querySql += querySql + "and t.需要生产数量>0"; DataTable dt = SqlHelper.GetDataForPage(pageIndex, pageSize, sql + " " + querySql, string.Format(" order by {0} {1},产品编号 asc,版本 asc,交期 asc", sortName, sortDirection), ref totalRecords); int columCount = dt.Columns.Count; foreach (DataRow dr in dt.Rows) { tdTextTemp = ""; for (int i = 0; i < columCount; i++) { if (i == 0) { tdTextTemp += string.Format("<td style='display:none;'>{0}</td>", dr[i]); } else { if (dt.Columns[i].ColumnName.Equals("需要生产数量")) { int temp = 0; if (dr[i] != null && dr[i] != DBNull.Value && Convert.ToInt32(dr[i]) > 0) { temp = Convert.ToInt32(dr[i]); } tdTextTemp += "<td>" + temp + "</td>"; } else { tdTextTemp += string.Format("<td>{0}</td>", dr[i]); } } } text += string.Format(@"<tr>{1}</tr> ", dr[1], tdTextTemp); } string pageing = ToolManager.PagerGetAjax("http://www.baidu.com", totalRecords, Convert.ToInt32(pageIndex), Convert.ToInt32(pageSize), ref pageCount); string responseValue = pageCount.ToString() + "^" + text + "^" + pageing + "^" + totalRecords; HttpContext.Current.Response.Write(responseValue); HttpContext.Current.Response.End(); }