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