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