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