/// <summary>
/// 保存明细
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
private static SqlCommand GetSaveDetail(SubDeliveryBackDetail m)
{
StringBuilder strSubSql = new StringBuilder();
strSubSql.Append("insert into officedba.SubDeliveryBackDetail(");
strSubSql.Append("CompanyCD,BackNo,SortNo,ProductID,BackCount,BackPrice,BackPriceTotal,UsedUnitCount,UnitID,ExRate,StorageID,SendBatchNo,BackBatchNo)");
strSubSql.Append(" values (");
strSubSql.Append("@CompanyCD,@BackNo,@SortNo,@ProductID,@BackCount,@BackPrice,@BackPriceTotal,@UsedUnitCount,@UnitID,@ExRate,@StorageID,@SendBatchNo,@BackBatchNo)");
strSubSql.Append(";select @@IDENTITY");
SqlParameter[] subParameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@BackNo", SqlDbType.VarChar,50),
new SqlParameter("@SortNo", SqlDbType.Int,4),
new SqlParameter("@ProductID", SqlDbType.Int,4),
new SqlParameter("@BackCount", SqlDbType.Decimal,9),
new SqlParameter("@BackPrice", SqlDbType.Decimal,9),
new SqlParameter("@BackPriceTotal", SqlDbType.Decimal,9),
new SqlParameter("@StorageID", SqlDbType.Int,4),
new SqlParameter("@SendBatchNo", SqlDbType.VarChar,50),
new SqlParameter("@BackBatchNo", SqlDbType.VarChar,50)};
subParameters[0].Value = m.CompanyCD;
subParameters[1].Value = m.BackNo;
subParameters[2].Value = m.SortNo;
subParameters[3].Value = m.ProductID;
subParameters[4].Value = m.BackCount;
subParameters[5].Value = m.BackPrice;
subParameters[6].Value = m.BackPriceTotal;
if (m.StorageID.HasValue)
{
subParameters[7].Value = m.StorageID.Value;
}
else
{
subParameters[7].Value = DBNull.Value;
}
if (!string.IsNullOrEmpty(m.SendBatchNo))
{
subParameters[8].Value = m.SendBatchNo;
}
else
{
subParameters[8].Value = DBNull.Value;
}
if (!string.IsNullOrEmpty(m.BackBatchNo))
{
subParameters[9].Value = m.BackBatchNo;
}
else
{
subParameters[9].Value = DBNull.Value;
}
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));
}
return sqlSubCmd;
}
public static DataTable GetSubDeliveryBackDetail(SubDeliveryBackDetail model)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append("SELECT stsd.ID,stsd.CompanyCD,stsd.BackNo,stsd.SortNo,stsd.ProductID,stsd.StorageID,stsd.UnitID as UsedUnitID,stsd.UsedUnitCount,stsd.SendBatchNo,stsd.BackBatchNo");
sbSql.Append(",ISNULL(stsd.BackCount,0) BackCount");
sbSql.Append(",ISNULL(stsd.BackPrice,0) BackPrice");
sbSql.Append(",ISNULL(stsd.BackPriceTotal,0) BackPriceTotal");
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.ApplyDeptID FROM officedba.SubDeliveryBack AS S WHERE S.CompanyCD=@CompanyCD AND S.BackNo=@BackNo) AND sp.ProductID=stsd.ProductID AND sp.CompanyCD=stsd.CompanyCD AND isnull(sp.BatchNo,'')=isnull(stsd.SendBatchNo,'') ) 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.SubDeliveryBackDetail as stsd inner join officedba.ProductInfo as pi on stsd.ProductID=pi.ID where stsd.CompanyCD=@CompanyCD AND stsd.BackNo=@BackNo");
SqlParameter[] Paras = {
new SqlParameter("@CompanyCD",SqlDbType.VarChar),
new SqlParameter("@BackNo",SqlDbType.VarChar)};
Paras[0].Value = model.CompanyCD;
Paras[1].Value = model.BackNo;
return SqlHelper.ExecuteSql(sbSql.ToString(), Paras);
}
public static DataTable GetSubDeliveryBackDetail(SubDeliveryBackDetail model)
{
return XBase.Data.Office.LogisticsDistributionManager.SubDeliveryBackDBHelper.GetSubDeliveryBackDetail(model);
}
