void ADDSAB(Dictionary <object, object> SQLString, LineProductMesEntityu.SGMSABEntity model)
{
StringBuilder strSql = new StringBuilder( );
strSql.Append("insert into SGMSAB(");
strSql.Append("SAB001,SAB002,SAB003,SAB004,SAB005,SAB006,SAB007,SAB009,SAB010,SAB011,SAB019,SAB020,SAB901,SAB902,SAB021,SAB022)");
strSql.Append(" values (");
strSql.Append("@SAB001,@SAB002,@SAB003,@SAB004,@SAB005,@SAB006,@SAB007,@SAB009,@SAB010,@SAB011,@SAB019,@SAB020,@SAB901,@SAB902,@SAB021,@SAB022)");
SqlParameter [] parameters =
{
new SqlParameter("@SAB001", SqlDbType.VarChar, 14),
new SqlParameter("@SAB002", SqlDbType.VarChar, 3),
new SqlParameter("@SAB003", SqlDbType.VarChar, 20),
new SqlParameter("@SAB004", SqlDbType.VarChar, 60),
new SqlParameter("@SAB005", SqlDbType.VarChar, 60),
new SqlParameter("@SAB006", SqlDbType.VarChar, 4),
new SqlParameter("@SAB007", SqlDbType.Decimal, 9),
new SqlParameter("@SAB009", SqlDbType.VarChar, 8),
new SqlParameter("@SAB010", SqlDbType.VarChar, 8),
new SqlParameter("@SAB011", SqlDbType.VarChar, 1),
new SqlParameter("@SAB019", SqlDbType.VarChar, 1),
new SqlParameter("@SAB020", SqlDbType.VarChar, 2),
new SqlParameter("@SAB901", SqlDbType.VarChar, 8),
new SqlParameter("@SAB902", SqlDbType.VarChar, 24),
new SqlParameter("@SAB021", SqlDbType.VarChar, 14),
new SqlParameter("@SAB022", SqlDbType.VarChar, 3)
};
parameters [0].Value = model.SAB001;
parameters [1].Value = model.SAB002;
parameters [2].Value = model.SAB003;
parameters [3].Value = model.SAB004;
parameters [4].Value = model.SAB005;
parameters [5].Value = model.SAB006;
parameters [6].Value = model.SAB007;
parameters [7].Value = model.SAB009;
parameters [8].Value = model.SAB010;
parameters [9].Value = model.SAB011;
parameters [10].Value = model.SAB019;
parameters [11].Value = model.SAB020;
parameters [12].Value = model.SAB901;
parameters [13].Value = model.SAB902;
parameters [14].Value = model.SAB021;
parameters [15].Value = model.SAB022;
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));
}
