public FormPurProductPlan( )
{
InitializeComponent( );
_bll = new LineProductMesBll.Bll.SemiProductPlanBll( );
model = new LineProductMesEntityu.SemiProductPlanEntity( );
ToolBarContain.ToolbarsC(barTool, new DevExpress.XtraBars.BarItem [] { toolCanecl, toolSave, toolPrint, toolCancellation, toolExamin, toolDelete, toolEdit, toolAdd });
GrivColumnStyle.setColumnStyle(treeList1);
FieldInfo fi = typeof(XPaint).GetField("graphics", BindingFlags.Static | BindingFlags.NonPublic);
fi.SetValue(null, new DrawXPaint( ));
}
/*
* CREATE PROC [dbo].[CNSCJ]
* AS
* DECLARE @i int
* DECLARE @j int
* DECLARE @k int
* DECLARE @count int
* DECLARE @strCN NVARCHAR (4000)
* DECLARE @SqlStr NVARCHAR (4000)
* BEGIN
* SET @j=0;
* SET @k=0;
* SET @count=1;
* IF object_id ( N'#A',N'U') is not null
* DROP TABLE #A
* ELSE
* CREATE TABLE #A(
* ROW INT,
* QAB003 NVARCHAR (20),
* DEA002 NVARCHAR (50),
* DEA057 NVARCHAR (50),
* PRE005 DATE,
* PRE007 INT,
* QAB005 DECIMAL (18,6),
* QAB006 DECIMAL (18,6)
* )
* INSERT INTO #A
* SELECT @j,QAB003,D.DEA002,D . DEA057,DATEADD ( day,-1,E . PRE005) PRE005,PRE007,CONVERT ( FLOAT,PRE008* QAB005 ) QAB005,0 QAB006 FROM SGMQAB C INNER JOIN TPADEA D ON C.QAB003=D . DEA001 INNER JOIN (SELECT PRE004 ,DEA002,DEA057,PRE005,PRE007,PRE008 FROM MIKPRD A INNER JOIN MIKPRE B ON A.PRD001= B . PRE001 INNER JOIN TPADEA C ON B.PRE004= C . DEA001 INNER JOIN SGMQAA D ON B.PRE004= D . QAA001 WHERE PRD003 = 0 AND DEA076 = '0507' AND DEA009 IN ('M','S') AND QAA001 NOT IN ( SELECT QAB003 FROM SGMQAB A INNER JOIN TPADEA B ON A . QAB003= B . DEA001 )) E ON C . QAB001=E.PRE004 WHERE DEA009 IN ('M','S')
*
* SET @i=1;
* WHILE @i=1
* BEGIN
* SET @j=@j+1;
* PRINT @j;
* INSERT INTO #A
* SELECT @j,C.QAB003,D . DEA002,D . DEA057,DATEADD ( day,-1,E . PRE005) PRE005,PRE007,CONVERT ( FLOAT,C . QAB005* E.QAB005) QAB005,0 QAB006 FROM SGMQAB C INNER JOIN TPADEA D ON C.QAB003=D . DEA001 INNER JOIN (SELECT QAB003 ,DEA002,DEA057,PRE005,PRE007,QAB005 FROM #A WHERE ROW=@k) E ON C.QAB001=E.QAB003 collate Chinese_PRC_CI_AI_WS WHERE DEA009 IN ('M','S')
* SET @k = @k + 1;
* SELECT @count=COUNT(1) FROM SGMQAB C INNER JOIN TPADEA D ON C . QAB003=D.DEA001 INNER JOIN ( SELECT QAB003 ,DEA002,DEA057,PRE005,PRE007,QAB005 FROM #A WHERE ROW=@k) E ON C.QAB001=E.QAB003 collate Chinese_PRC_CI_AI_WS WHERE DEA009 IN ('M','S')
* IF(@count= 0 )
* SET @i=0;
* END
* UPDATE #A SET QAB006=B.QAB005 FROM (SELECT SUM(QAB005) QAB005,QAB003 FROM #A GROUP BY QAB003) B WHERE #A.QAB003=B.QAB003
* SELECT @strCN=ISNULL(@strCN+',','')+ QUOTENAME(PRE005) FROM #A GROUP BY PRE005
* SET @SqlStr='
* WITH CET AS (
* SELECT QAB003 品号,DEA002 品名 ,DEA057 规格 ,PRE005,CONVERT(FLOAT,QAB005) 单量,CONVERT ( FLOAT,QAB006) 总量 FROM #A)
* SELECT * FROM CET PIVOT ( SUM(单量) FOR PRE005 IN ('+@strCN+')) AS T'
* EXEC ( @SqlStr);
* END
* GO
*/
/// <summary>
/// 获取采购件
/// </summary>
/// <returns></returns>
public List <LineProductMesEntityu.SemiProductPlanEntity> getListModelPur( )
{
string piNum = string.Empty;
List <LineProductMesEntityu.SemiProductPlanEntity> modelList = new List <LineProductMesEntityu.SemiProductPlanEntity> ( );
StringBuilder strSql = new StringBuilder( );
//strSql . Append ( "SELECT PRE004,DEA002,DEA057,MAX(PRE005) PRE005,PRE007,SUM(PRE008) PRE008 FROM MIKPRD A INNER JOIN MIKPRE B ON A.PRD001=B.PRE001 INNER JOIN TPADEA C ON B.PRE004=C.DEA001 WHERE PRD003=0 GROUP BY PRE004,DEA002,DEA057,PRE007" );
strSql.Append("SELECT DISTINCT PRE004,DEA002,DEA057,PRF002 PRE005,PRE007,PRF003 PRE008,DDA003,DEA003,DGA003 FROM (SELECT MAX(PRF002) PRF002,SUM(PRF003) PRF003,PRF001 FROM MIKPRF GROUP BY PRF001) A INNER JOIN MIKPRE B ON A.PRF001=B.PRE004 INNER JOIN TPADEA C ON B.PRE004=C.DEA001 INNER JOIN SGMQAB D ON B.PRE004=D.QAB001 LEFT JOIN TPADDA E ON C.DEA008=E.DDA001 LEFT JOIN TPADGA F ON C.DEA010=F.DGA001 WHERE QAB001 NOT IN (SELECT QAB003 FROM SGMQAB A INNER JOIN TPADEA B ON A.QAB003=B.DEA001) ");
DataTable table = SqlHelper.ExecuteDataTable(strSql.ToString( ));
if (table != null && table.Rows.Count > 0)
{
int i = 0;
foreach (DataRow row in table.Rows)
{
LineProductMesEntityu.SemiProductPlanEntity model = new LineProductMesEntityu.SemiProductPlanEntity( );
i++;
model.SEP001 = row ["PRE004"].ToString( );
model.SEP002 = row ["DEA002"].ToString( );
model.SEP003 = row ["DEA057"].ToString( );
model.SEP004 = Convert.ToDateTime(row ["PRE005"].ToString( ));
model.SEP005 = Convert.ToInt32(row ["PRE007"].ToString( ));
model.SEP006 = Convert.ToDecimal(row ["PRE008"].ToString( ));
model.SEP007 = i.ToString( );
model.SEP008 = 0.ToString( );
model.SEP009 = 1;
model.SEP013 = 0.ToString( );
model.SEP010 = row ["DGA003"].ToString( );
model.SEP011 = row ["DDA003"].ToString( );
model.SEP012 = row ["DEA003"].ToString( );
model.SEP014 = "P";
modelList.Add(model);
if (piNum == string.Empty)
{
piNum = "'" + model.SEP001 + "'";
}
else
{
piNum = piNum + "," + "'" + model.SEP001 + "'";
}
}
}
if (piNum != string.Empty)
{
//piNum = "'DGY-F805'";
bool state = true;
while (state)
{
//展开第一阶
strSql = new StringBuilder( );
strSql.AppendFormat("SELECT DISTINCT QAB001,QAB003,DEA002,DEA057,QAB005,DDA003,DEA003,DGA003,DEA009 FROM SGMQAB C LEFT JOIN TPADEA D ON C.QAB003=D.DEA001 LEFT JOIN TPADDA E ON D.DEA008=E.DDA001 LEFT JOIN TPADGA F ON D.DEA010=F.DGA001 WHERE QAB001 IN ({0}) ", piNum);
//DEA009 IN ('P') AND
DataTable tableOne = SqlHelper.ExecuteDataTable(strSql.ToString( ));
if (tableOne != null && tableOne.Rows.Count > 0)
{
piNum = string.Empty;
int i = 0;
string cg = string.Empty;
foreach (DataRow row in tableOne.Rows)
{
cg = row ["DEA009"].ToString( );
LineProductMesEntityu.SemiProductPlanEntity model = new LineProductMesEntityu.SemiProductPlanEntity( );
i++;
model.SEP001 = row ["QAB003"].ToString( );
if (piNum == string.Empty)
{
piNum = "'" + model.SEP001 + "'";
}
else
{
piNum = piNum + "," + "'" + model.SEP001 + "'";
}
//if ( !cg . Equals ( "P" ) )
// continue;
LineProductMesEntityu.SemiProductPlanEntity body = modelList.Find((t) =>
{
return(t.SEP001.Equals(row ["QAB001"].ToString( )));
});
//if ( body == null )
// continue;
bool state1 = true;
if (body.SEP014.Equals("P"))
{
state1 = false;
if (body != null)
{
model.SEP013 = model.SEP008 = body.SEP007;
}
else
{
model.SEP013 = model.SEP008 = 0.ToString( );
}
}
while (state1)
{
body = modelList.Find((t) =>
{
return(t.SEP007 == body.SEP008);
});
if (body.SEP014.Equals("P"))
{
state1 = false;
if (body != null)
{
model.SEP013 = model.SEP008 = body.SEP007;
}
else
{
model.SEP013 = model.SEP008 = 0.ToString( );
}
}
}
model.SEP014 = row ["DEA009"].ToString( );
model.SEP002 = row ["DEA002"].ToString( );
model.SEP003 = row ["DEA057"].ToString( );
model.SEP004 = Convert.ToDateTime(body.SEP004).AddDays(-1);
model.SEP005 = body.SEP005;
model.SEP006 = body.SEP006 * body.SEP009;
model.SEP007 = model.SEP008 + "_" + i.ToString( );
model.SEP009 = Convert.ToDecimal(row ["QAB005"]);
model.SEP010 = row ["DGA003"].ToString( );
model.SEP011 = row ["DDA003"].ToString( );
model.SEP012 = row ["DEA003"].ToString( );
state1 = true;
while (state1)
{
LineProductMesEntityu.SemiProductPlanEntity bodyOne = modelList.Find((t) =>
{
return(t.SEP001 == model.SEP001 && t.SEP004 == model.SEP004);
});
if (bodyOne != null)
{
model.SEP006 = model.SEP006 + bodyOne.SEP006;
model.SEP009 = model.SEP009 + bodyOne.SEP009;
modelList.Remove(bodyOne);
}
else
{
state1 = false;
}
}
state1 = true;
while (state1)
{
LineProductMesEntityu.SemiProductPlanEntity bodyOne = modelList.Find((t) =>
{
return(t.SEP007 == model.SEP007);
});
if (bodyOne != null)
{
model.SEP006 = model.SEP006 + bodyOne.SEP006;
model.SEP009 = model.SEP009 + bodyOne.SEP009;
modelList.Remove(bodyOne);
}
else
{
state1 = false;
}
}
modelList.Add(model);
}
}
else
{
state = false;
}
}
}
return(modelList.FindAll((x) =>
{
return x.SEP014 == "P";
}));
}
/// <summary>
/// 获取数据列表 厂内生产件和委外件
/// </summary>
/// <returns></returns>
public List <LineProductMesEntityu.SemiProductPlanEntity> getListModel( )
{
string piNum = string.Empty;
List <LineProductMesEntityu.SemiProductPlanEntity> modelList = new List <LineProductMesEntityu.SemiProductPlanEntity> ( );
StringBuilder strSql = new StringBuilder( );
strSql.Append("SELECT DISTINCT PRE004,DEA002,DEA057,PRF002 PRE005,PRE007,PRF003 PRE008,DAA002,DDA003,DEA003 FROM (SELECT MAX(PRF002) PRF002,SUM(PRF003) PRF003,PRF001 FROM MIKPRF GROUP BY PRF001) A INNER JOIN MIKPRE B ON A.PRF001=B.PRE004 INNER JOIN TPADEA C ON B.PRE004=C.DEA001 INNER JOIN SGMQAA D ON B.PRE004=D.QAA001 INNER JOIN TPADAA E ON C.DEA076=E.DAA001 INNER JOIN TPADDA F ON C.DEA008=F.DDA001 WHERE DEA076='0507' AND DEA009 IN ('M','S') AND QAA001 NOT IN (SELECT QAB003 FROM SGMQAB A INNER JOIN TPADEA B ON A.QAB003=B.DEA001)");
DataTable table = SqlHelper.ExecuteDataTable(strSql.ToString( ));
if (table != null && table.Rows.Count > 0)
{
int i = 0;
foreach (DataRow row in table.Rows)
{
LineProductMesEntityu.SemiProductPlanEntity model = new LineProductMesEntityu.SemiProductPlanEntity( );
i++;
model.SEP001 = row ["PRE004"].ToString( );
model.SEP002 = row ["DEA002"].ToString( );
model.SEP003 = row ["DEA057"].ToString( );
model.SEP004 = Convert.ToDateTime(row ["PRE005"].ToString( ));
model.SEP005 = Convert.ToInt32(row ["PRE007"].ToString( ));
model.SEP006 = Convert.ToDecimal(row ["PRE008"].ToString( ));
model.SEP007 = i.ToString( );
model.SEP008 = 0.ToString( );
model.SEP009 = 1;
model.SEP010 = row ["DAA002"].ToString( );
model.SEP011 = row ["DDA003"].ToString( );
model.SEP012 = row ["DEA003"].ToString( );
modelList.Add(model);
if (piNum == string.Empty)
{
piNum = "'" + model.SEP001 + "'";
}
else
{
piNum = piNum + "," + "'" + model.SEP001 + "'";
}
}
}
if (piNum != string.Empty)
{
bool state = true;
while (state)
{
//展开第一阶
strSql = new StringBuilder( );
strSql.AppendFormat("SELECT DISTINCT QAB001,QAB003,DEA002,DEA057,QAB005,DAA002,DDA003,DEA003 FROM SGMQAB C INNER JOIN TPADEA D ON C.QAB003=D.DEA001 LEFT JOIN TPADAA E ON D.DEA076=E.DAA001 LEFT JOIN TPADDA F ON D.DEA008=F.DDA001 WHERE DEA009 IN ('M','S') AND QAB001 IN ({0}) ", piNum);
DataTable tableOne = SqlHelper.ExecuteDataTable(strSql.ToString( ));
if (tableOne != null && tableOne.Rows.Count > 0)
{
piNum = string.Empty;
int i = 0;
foreach (DataRow row in tableOne.Rows)
{
LineProductMesEntityu.SemiProductPlanEntity model = new LineProductMesEntityu.SemiProductPlanEntity( );
i++;
model.SEP001 = row ["QAB003"].ToString( );
LineProductMesEntityu.SemiProductPlanEntity body = modelList.Find((t) =>
{
return(t.SEP001.Equals(row ["QAB001"].ToString( )));
});
model.SEP002 = row ["DEA002"].ToString( );
model.SEP003 = row ["DEA057"].ToString( );
model.SEP004 = Convert.ToDateTime(body.SEP004).AddDays(-1);
model.SEP005 = body.SEP005;
model.SEP006 = body.SEP006 * body.SEP009;
if (body != null)
{
model.SEP008 = body.SEP007;
}
else
{
model.SEP008 = 0.ToString( );
}
model.SEP007 = model.SEP008 + "_" + i.ToString( );
model.SEP009 = Convert.ToDecimal(row ["QAB005"]);
model.SEP010 = row ["DAA002"].ToString( );
model.SEP011 = row ["DDA003"].ToString( );
model.SEP012 = row ["DEA003"].ToString( );
modelList.Add(model);
if (piNum == string.Empty)
{
piNum = "'" + model.SEP001 + "'";
}
else
{
piNum = piNum + "," + "'" + model.SEP001 + "'";
}
}
}
else
{
state = false;
}
}
}
return(modelList);
}
