/// <summary> /// 保存数据明细 /// </summary> /// <param name="m"></param> /// <returns></returns> public static SqlCommand SaveDetail(SubDeliveryTransDetail m) { StringBuilder strSubSql = new StringBuilder(); strSubSql.Append("insert into officedba.SubDeliveryTransDetail("); strSubSql.Append("CompanyCD,TransNo,SortNo,ProductID,TransCount,TransPrice,TransPriceTotal,UsedUnitCount,UnitID,ExRate,BatchNo,Remark)"); strSubSql.Append(" values ("); strSubSql.Append("@CompanyCD,@TransNo,@SortNo,@ProductID,@TransCount,@TransPrice,@TransPriceTotal,@UsedUnitCount,@UnitID,@ExRate,@BatchNo,@Remark)"); strSubSql.Append(";select @@IDENTITY"); SqlParameter[] subParameters = { new SqlParameter("@CompanyCD", SqlDbType.VarChar,8), new SqlParameter("@TransNo", SqlDbType.VarChar,50), new SqlParameter("@SortNo", SqlDbType.Int,4), new SqlParameter("@ProductID", SqlDbType.Int,4), new SqlParameter("@TransCount", SqlDbType.Decimal,9), new SqlParameter("@TransPrice", SqlDbType.Decimal,9), new SqlParameter("@TransPriceTotal", SqlDbType.Decimal,9), new SqlParameter("@BatchNo", SqlDbType.VarChar,50)}; subParameters[0].Value = m.CompanyCD; subParameters[1].Value = m.TransNo; subParameters[2].Value = m.SortNo; subParameters[3].Value = m.ProductID; subParameters[4].Value = m.TransCount; subParameters[5].Value = m.TransPrice; subParameters[6].Value = m.TransPriceTotal; if (String.IsNullOrEmpty(m.BatchNo)) { subParameters[7].Value = DBNull.Value; } else { subParameters[7].Value = m.BatchNo; } SqlCommand SqlSubCmd = new SqlCommand { CommandText = strSubSql.ToString() }; SqlSubCmd.Parameters.AddRange(subParameters); if (m.UnitID.HasValue) { SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@UnitID", m.UnitID.Value)); } else { SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@UnitID", DBNull.Value)); } if (m.UsedUnitCount.HasValue) { SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", m.UsedUnitCount.Value)); } else { SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", DBNull.Value)); } if (m.ExRate.HasValue) { SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@ExRate", m.ExRate.Value)); } else { SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@ExRate", DBNull.Value)); } SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@Remark", m.Remark)); return SqlSubCmd; }
public static DataTable GetSubDeliveryTransDetailInfo(SubDeliveryTransDetail model) { StringBuilder sbSql = new StringBuilder(); sbSql.Append("SELECT stsd.ID,stsd.CompanyCD,stsd.TransNo,stsd.SortNo,stsd.ProductID,stsd.UsedUnitCount,stsd.UnitID AS UsedUnitID,stsd.BatchNo,stsd.Remark"); sbSql.Append(" ,ISNULL(stsd.TransCount,0) AS TransCount"); sbSql.Append(" ,ISNULL(stsd.TransPrice,0) AS TransPrice"); sbSql.Append(" ,ISNULL(stsd.TransPriceTotal,0) AS TransPriceTotal"); sbSql.Append(" ,pi.ProdNo,pi.ProductName,pi.Specification,pi.UnitID,pi.MinusIs,pi.IsBatchNo"); sbSql.Append(" ,(select SUM(ISNULL(ProductCount,0)) from officedba.SubStorageProduct as sp where sp.DeptID=(select s.OutDeptID FROM officedba.SubDeliveryTrans AS S WHERE S.CompanyCD=@CompanyCD AND S.TransNo=@TransNo) AND sp.ProductID=stsd.ProductID AND sp.CompanyCD=stsd.CompanyCD AND isnull(sp.BatchNo,'')=isnull(stsd.BatchNo,'') ) AS UseCount"); sbSql.Append(" ,(SELECT ui.CodeName from officedba.CodeUnitType as ui where ui.ID=pi.UnitID) AS UnitName "); sbSql.Append(" ,(SELECT ui1.CodeName from officedba.CodeUnitType as ui1 where ui1.ID=stsd.UnitID) AS UsedUnitName "); sbSql.Append(" from officedba.SubDeliveryTransDetail as stsd inner join officedba.ProductInfo as pi on stsd.ProductID=pi.ID where stsd.CompanyCD=@CompanyCD AND stsd.TransNo=@TransNo"); SqlParameter[] Paras = { new SqlParameter("@CompanyCD",SqlDbType.VarChar), new SqlParameter("@TransNo",SqlDbType.VarChar)}; Paras[0].Value = model.CompanyCD; Paras[1].Value = model.TransNo; return SqlHelper.ExecuteSql(sbSql.ToString(), Paras); }
public static DataTable GetSubDeliveryTransDetailInfo(SubDeliveryTransDetail model) { return XBase.Data.Office.LogisticsDistributionManager.SubDeliveryTransDBHelper.GetSubDeliveryTransDetailInfo(model); }