// public DataTable GetPurchaseInfoByStorgeStock1(string[] pscodes) // { // StringBuilder builder = new StringBuilder(); // builder.AppendFormat("WITH ctePurchaseStock AS\r\n (\r\n\t --采购单信息\r\n SELECT scode,pscode,SUM(number) ContractNumber,sprice,corp FROM\r\n\t (\r\n\t\t SELECT * FROM Sm_Purchase_Stock\r\n\t\t WHERE pscode in ({0})\r\n\t ) PurchaseStock GROUP BY scode,pscode,sprice,corp\r\n )\r\n , cteAllInStorgeStock AS\r\n (\r\n\t --采购但材料已经入库的信息\r\n SELECT scode,SUM(number) AllInNumber,sprice,corp FROM\r\n\t (\r\n\t\t SELECT storgeStock.* FROM ctePurchaseStock INNER JOIN Sm_Storage_Stock storgeStock \r\n\t\t ON storgeStock.linkCode=ctePurchaseStock.pscode\r\n\t\t AND storgeStock.Scode=ctePurchaseStock.Scode \r\n\t\t AND storgeStock.sprice=ctePurchaseStock.sprice \r\n\t\t AND storgeStock.corp=ctePurchaseStock.corp\r\n\t\t LEFT JOIN Sm_Storage storage ON storgeStock.stCode=storage.sCode\r\n\t\t WHERE FlowState=1 AND Inflag=1\r\n\t ) allInStorgeStock GROUP BY scode,sprice,corp\r\n )", DBHelper.GetInParameterSql(pscodes)).AppendLine(); // builder.AppendLine("SELECT ResourceId, currentStorageStock.pscode,ISNULL(ContractNumber,0) ContractNumber,ISNULL(AllInNumber,0) AllInNumber,"); // builder.AppendLine("currentStorageStock.number,currentStorageStock.sprice, (currentStorageStock.number*currentStorageStock.sprice) Total, ResourceCode, ResourceName, "); // builder.AppendLine("Specification, Name as UnitName,currentStorageStock.Corp, CorpName,arrivalDate ,Res_Resource.Brand,ModelNumber,TechnicalParameter "); // builder.AppendLine("FROM Res_Resource "); // builder.AppendLine("INNER JOIN Sm_Purchase_Stock currentStorageStock ON currentStorageStock.scode = Res_Resource.ResourceCode "); // builder.AppendLine("LEFT JOIN Res_Unit on Res_Unit.UnitId = Res_Resource.Unit "); // builder.AppendLine("LEFT JOIN XPM_Basic_ContactCorp on currentStorageStock.corp = XPM_Basic_ContactCorp.CorpID "); // builder.AppendLine("LEFT JOIN ctePurchaseStock ON ResourceCode=ctePurchaseStock.scode \r\n AND currentStorageStock.sprice=ctePurchaseStock.sprice \r\n AND currentStorageStock.corp=ctePurchaseStock.corp \r\n LEFT JOIN cteAllInStorgeStock ON ResourceCode=cteAllInStorgeStock.scode \r\n AND currentStorageStock.sprice=cteAllInStorgeStock.sprice \r\n AND currentStorageStock.corp=cteAllInStorgeStock.corp "); // builder.AppendLine("WHERE currentStorageStock.pscode IN(").Append(DBHelper.GetInParameterSql(pscodes)).Append(")"); // builder.AppendLine(" ORDER BY ResourceCode DESC "); // return SqlHelper.ExecuteQuery(CommandType.Text, builder.ToString(), null); // } //protected void btnBindResource2_Click(object sender, System.EventArgs e) //{ // this.UpdateStorageStockDataSource(); // DataTable dataTable = this.ViewState[this.stockDataSourceName] as DataTable; // if (dataTable != null) // { // ISerializable serializable = new JsonSerializer(); // string[] array1 = serializable.Deserialize<string[]>(this.hfldResourceCode.Value); // string[] array2 = serializable.Deserialize<string[]>(this.hfldResourceCode2.Value); // int ii1 = array1 == null ? 0 : array1.Length; // int ii2 = array2 == null ? 0 : array2.Length; // string[] array = new string[ii1 + ii2]; // if (array1 != null) // { // array1.CopyTo(array, 0); // if (array2 != null) // { // array2.CopyTo(array, array1.Length); // } // } // else // { // if (array2 != null) // { // array2.CopyTo(array, array1.Length); // } // } // if (array != null) // { // DataTable purchaseInfoByStorgeStock = this.purhaseStock.GetPurchaseInfoByStorgeStock(array); // foreach (DataRow row in purchaseInfoByStorgeStock.Rows) // { // this.SetResourceTable(row, dataTable); // } // this.gvwStorageStock.DataSource = dataTable; // this.gvwStorageStock.DataBind(); // if (dataTable.Rows.Count > 0) // { // string total = System.Convert.ToDecimal(dataTable.Compute("SUM(Total)", string.Empty)).ToString("0.000"); // GridViewUtility.AddTotalRow(this.gvwStorageStock, total, 13); // } // } // } //} private void SetResourceTable(DataRow row, DataTable hasResourceTable) { int num = DataTableUtility.IndexOf(row, "ResourceCode", hasResourceTable); if (num == -1) { hasResourceTable.Rows.Add(this.SetNewRow(hasResourceTable, row)); return; } DataRow dataRow = hasResourceTable.Rows[num]; if (dataRow["sprice"].Equals(row["sprice"])) { dataRow["number"] = System.Convert.ToDecimal(dataRow["number"]) + System.Convert.ToDecimal(row["number"]); dataRow["Total"] = System.Convert.ToDecimal(dataRow["number"]) * System.Convert.ToDecimal(dataRow["sprice"]); return; } hasResourceTable.Rows.Add(this.SetNewRow(hasResourceTable, row)); }