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