void AddSAA(Dictionary <object, object> SQLString, LineProductMesEntityu.SGMSAAEntity model)
{
StringBuilder strSql = new StringBuilder( );
strSql.Append("insert into SGMSAA(");
strSql.Append("SAA001,SAA002,SAA003,SAA004,SAA005,SAA006,SAA013,SAA014,SAA901,SAA902)");
strSql.Append(" values (");
strSql.Append("@SAA001,@SAA002,@SAA003,@SAA004,@SAA005,@SAA006,@SAA013,@SAA014,@SAA901,@SAA902)");
SqlParameter [] parameters =
{
new SqlParameter("@SAA001", SqlDbType.VarChar, 14),
new SqlParameter("@SAA002", SqlDbType.VarChar, 4),
new SqlParameter("@SAA003", SqlDbType.VarChar, 8),
new SqlParameter("@SAA004", SqlDbType.VarChar, 8),
new SqlParameter("@SAA005", SqlDbType.VarChar, 6),
new SqlParameter("@SAA006", SqlDbType.VarChar, 255),
new SqlParameter("@SAA013", SqlDbType.VarChar, 1),
new SqlParameter("@SAA014", SqlDbType.VarChar, 8),
new SqlParameter("@SAA901", SqlDbType.VarChar, 8),
new SqlParameter("@SAA902", SqlDbType.VarChar, 24)
};
parameters [0].Value = model.SAA001;
parameters [1].Value = model.SAA002;
parameters [2].Value = model.SAA003;
parameters [3].Value = model.SAA004;
parameters [4].Value = model.SAA005;
parameters [5].Value = model.SAA006;
parameters [6].Value = model.SAA013;
parameters [7].Value = model.SAA014;
parameters [8].Value = model.SAA901;
parameters [9].Value = model.SAA902;
SQLString.Add(strSql, parameters);
}
/// <summary>
/// 生成ERP计划
/// </summary>
/// <param name="oddNum"></param>
/// <returns></returns>
public bool AddPlan(string oddNum)
{
Dictionary <object, object> SQLString = new Dictionary <object, object> ( );
LineProductMesEntityu.SGMSAAEntity saa = new LineProductMesEntityu.SGMSAAEntity( );
saa.SAA001 = getCodeForPlan( );
saa.SAA002 = string.Empty;
saa.SAA003 = UserInfoMation.sysTime.ToString("yyyyMMdd");
saa.SAA004 = UserInfoMation.userNum;
saa.SAA005 = "06";
saa.SAA006 = string.Empty;
saa.SAA013 = "F";
saa.SAA014 = string.Empty;
saa.SAA901 = UserInfoMation.userNum;
saa.SAA902 = saa.SAA003;
AddSAA(SQLString, saa);
StringBuilder strSql = new StringBuilder( );
strSql.AppendFormat("SELECT PRE004,PRE002,PRE003,DEA002,DEA057,DEA003,SUM(PRE008) PRE008 FROM MIKPRE A INNER JOIN TPADEA B ON A.PRE004=B.DEA001 WHERE PRE001='{0}' AND PRE008-ISNULL(PRE009,0)>0 GROUP BY PRE004,DEA002,DEA057,DEA003,PRE002,PRE003", oddNum);
DataTable tableView = SqlHelper.ExecuteDataTable(strSql.ToString( ));
if (tableView == null || tableView.Rows.Count < 1)
{
return(true);
}
int i = 0;
List <LineProductMesEntityu.SGMSABEntity> modelList = new List <LineProductMesEntityu.SGMSABEntity> ( );
foreach (DataRow row in tableView.Rows)
{
LineProductMesEntityu.SGMSABEntity sab = new LineProductMesEntityu.SGMSABEntity( );
sab.SAB001 = saa.SAA001;
i++;
sab.SAB002 = i.ToString( ).PadLeft(3, '0');
sab.SAB003 = row ["PRE004"].ToString( );
sab.SAB004 = row ["DEA002"].ToString( );
sab.SAB005 = row ["DEA057"].ToString( );
sab.SAB006 = row ["DEA003"].ToString( );
sab.SAB007 = Convert.ToDecimal(row ["PRE008"].ToString( ));
sab.SAB009 = UserInfoMation.sysTime.ToString("yyyyMMdd");
sab.SAB010 = sab.SAB009;
sab.SAB011 = "F";
sab.SAB019 = "F";
sab.SAB020 = "32";
sab.SAB021 = row ["PRE002"].ToString( );
sab.SAB022 = row ["PRE003"].ToString( );
sab.SAB901 = UserInfoMation.userNum;
sab.SAB902 = UserInfoMation.sysTime.ToString("yyyyMMdd");
modelList.Add(sab);
ADDSAB(SQLString, sab);
}
if (modelList.Count > 0)
{
foreach (LineProductMesEntityu.SGMSABEntity list in modelList)
{
strSql = new StringBuilder( );
strSql.AppendFormat("SELECT QAB003,DEA002,DEA057,DEA003,DEA008,CONVERT(FLOAT,QAB005/QAB006) QAB005 FROM SGMQAB A INNER JOIN TPADEA B ON A.QAB003=B.DEA001 WHERE QAB001='{0}'", list.SAB003);
DataTable table = SqlHelper.ExecuteDataTable(strSql.ToString( ));
if (table == null || table.Rows.Count < 1)
{
continue;
}
i = 0;
LineProductMesEntityu.SGMSACEntity entity = new LineProductMesEntityu.SGMSACEntity( );
entity.SAC001 = saa.SAA001;
entity.SAC002 = list.SAB002;
foreach (DataRow row in table.Rows)
{
i++;
entity.SAC003 = i.ToString( ).PadLeft(3, '0');
entity.SAC004 = row ["QAB003"].ToString( );
entity.SAC005 = row ["DEA002"].ToString( );
entity.SAC006 = row ["DEA057"].ToString( );
entity.SAC007 = row ["DEA003"].ToString( );
entity.SAC008 = row ["DEA008"].ToString( );
entity.SAC009 = string.IsNullOrEmpty(row ["QAB005"].ToString( )) == true ? 0 : Convert.ToDecimal(row ["QAB005"]) * list.SAB007;
ADDSAC(SQLString, entity);
}
}
}
strSql = new StringBuilder( );
strSql.AppendFormat("SELECT idx,PRE008-ISNULL(PRE009,0) PRE008 FROM MIKPRE WHERE PRE001='{0}' AND PRE008-ISNULL(PRE009,0)>0", oddNum);
tableView = SqlHelper.ExecuteDataTable(strSql.ToString( ));
LineProductMesEntityu.ProductPlanBodyEntity model = new LineProductMesEntityu.ProductPlanBodyEntity( );
model.PRE012 = saa.SAA001;
foreach (DataRow row in tableView.Rows)
{
model.idx = Convert.ToInt32(row ["idx"].ToString( ));
model.PRE009 = Convert.ToInt32(row ["PRE008"].ToString( ));
AddPlan(SQLString, model);
}
return(SqlHelper.ExecuteSqlTranDic(SQLString));
}
