/// <summary>
/// 更新业务单关联的金额
/// </summary>
/// <param name="lstCommand">数组</param>
/// <param name="ID">主键</param>
private static void UpdateInsertBillingPrice(ArrayList lstCommand, int ID, decimal Price)
{
decimal NAccount = BillingDBHelper.GetBillingNAccounts(ID.ToString());
StringBuilder sql = new StringBuilder();
sql.AppendLine("Update officedba.Billing set YAccounts=isnull(YAccounts,0)+@YAccounts,NAccounts=isnull(NAccounts,0)-@YAccounts ");
sql.AppendLine(" where ID=@ID");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql.ToString();
//定义参数
cmd.Parameters.AddWithValue("@YAccounts", SqlDbType.Decimal).Value = Price;
cmd.Parameters.AddWithValue("@ID", SqlDbType.Int).Value = ID;
lstCommand.Add(cmd);
decimal StatusAmount = NAccount - Price;
string updateStautsSQL = "Update officedba.Billing set AccountsStatus=@AccountsStatus where ID=@ID1";
SqlCommand comdd = new SqlCommand();
comdd.CommandText = updateStautsSQL;
if (StatusAmount > 0)
{
comdd.Parameters.AddWithValue("@AccountsStatus", SqlDbType.Decimal).Value = ConstUtil.ACCOUNTS_STATUS_YJSZ;
comdd.Parameters.AddWithValue("@ID1", SqlDbType.Int).Value = ID;
}
else
{
comdd.Parameters.AddWithValue("@AccountsStatus", SqlDbType.Decimal).Value = ConstUtil.ACCOUNTS_STATUS_YJS;
comdd.Parameters.AddWithValue("@ID1", SqlDbType.Int).Value = ID;
}
lstCommand.Add(comdd);
}
/// <summary>
/// 修改时更新业务单表状态信息
/// </summary>
/// <param name="lstCommand"></param>
/// <param name="BillingID"></param>
/// <param name="DiffAmount"></param>
public static void updateUpBillingStatus(ArrayList lstCommand, string BillingID, decimal DiffAmount)
{
decimal NAccount = BillingDBHelper.GetBillingNAccounts(BillingID);
StringBuilder sql = new StringBuilder();
sql.AppendLine("Update officedba.Billing set YAccounts=isnull(YAccounts,0)+(@DiffAmount),NAccounts=isnull(NAccounts,0)-(@DiffAmount) ");
sql.AppendLine(" where ID=@ID");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql.ToString();
//定义参数
cmd.Parameters.AddWithValue("@DiffAmount", SqlDbType.Decimal).Value = DiffAmount;
cmd.Parameters.AddWithValue("@ID", SqlDbType.Int).Value = BillingID;
lstCommand.Add(cmd);
decimal StatusAmount = NAccount - (DiffAmount);
string updateStautsSQL = "Update officedba.Billing set AccountsStatus=@AccountsStatus where ID=@ID1";
SqlCommand comdd = new SqlCommand();
comdd.CommandText = updateStautsSQL;
if (StatusAmount > 0)
{
comdd.Parameters.AddWithValue("@AccountsStatus", SqlDbType.Decimal).Value = ConstUtil.ACCOUNTS_STATUS_YJSZ;
comdd.Parameters.AddWithValue("@ID1", SqlDbType.Int).Value = BillingID;
}
else
{
comdd.Parameters.AddWithValue("@AccountsStatus", SqlDbType.Decimal).Value = ConstUtil.ACCOUNTS_STATUS_YJS;
comdd.Parameters.AddWithValue("@ID1", SqlDbType.Int).Value = BillingID;
}
lstCommand.Add(comdd);
}
/// <summary>
/// 根据主键获取业务单未付金额
/// </summary>
/// <param name="ID">主键ID</param>
/// <returns>decimal</returns>
public static decimal GetBillingNAccounts(string ID)
{
try
{
return(BillingDBHelper.GetBillingNAccounts(ID));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 更新业务单关联的金额
/// </summary>
/// <param name="lstCommand">数组</param>
/// <param name="ID">主键</param>
private static void UpdateBillingPriceInfo(ArrayList lstCommand, int ID, decimal Price, decimal OldPrice)
{
decimal NAccounts = 0;
decimal result = BillingDBHelper.GetBillingNAccounts(ID.ToString());
if (result > 0)
{
//获取业务单未付金额
NAccounts = result;
}
decimal diffAccounts = Price - OldPrice;//update by moshenlin 2009-08-05
StringBuilder sql = new StringBuilder();
sql.AppendLine("Update officedba.Billing set YAccounts=isnull(YAccounts,0)+@YAccounts,");
sql.AppendLine("NAccounts=isnull(NAccounts,0)-@NAccounts,AccountsStatus=@AccountsStatus ");
sql.AppendLine(" where ID=@ID");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql.ToString();
//定义参数
//cmd.Parameters.AddWithValue("@YAccounts", SqlDbType.Decimal).Value = OldPrice;
cmd.Parameters.AddWithValue("@YAccounts", SqlDbType.Decimal).Value = diffAccounts;//update by moshenlin 2009-08-05
cmd.Parameters.AddWithValue("@ID", SqlDbType.Int).Value = ID;
//cmd.Parameters.AddWithValue("@NAccounts", SqlDbType.Decimal).Value = OldPrice;
cmd.Parameters.AddWithValue("@NAccounts", SqlDbType.Decimal).Value = diffAccounts;//update by moshenlin 2009-08-05
if (NAccounts - diffAccounts == 0)
{
cmd.Parameters.AddWithValue("@AccountsStatus", SqlDbType.Char).Value = ConstUtil.ACCOUNTS_STATUS_YJS;
}
else
{
cmd.Parameters.AddWithValue("@AccountsStatus", SqlDbType.Char).Value = ConstUtil.ACCOUNTS_STATUS_YJSZ;
}
lstCommand.Add(cmd);
}
/// <summary>
/// 更新业务单关联的金额
/// </summary>
/// <param name="lstCommand">数组</param>
/// <param name="ID">主键</param>
private static void UpdateBillingPrice(ArrayList lstCommand, int ID, decimal Price)
{
decimal NAccounts = 0;
decimal result = BillingDBHelper.GetBillingNAccounts(ID.ToString());
if (result > 0)
{
//获取业务单未付金额
NAccounts = result;
}
StringBuilder sql = new StringBuilder();
sql.AppendLine("Update officedba.Billing set YAccounts=isnull(YAccounts,0)+@YAccounts,");
sql.AppendLine("NAccounts=@NAccounts,AccountsStatus=@AccountsStatus ");
sql.AppendLine(" where ID=@ID");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql.ToString();
//定义参数
cmd.Parameters.AddWithValue("@YAccounts", SqlDbType.Decimal).Value = Price;
cmd.Parameters.AddWithValue("@ID", SqlDbType.Int).Value = ID;
//未结金额
decimal ProcPrice = NAccounts - Price;
if (ProcPrice == 0)
{
cmd.Parameters.AddWithValue("@AccountsStatus", SqlDbType.Char).Value = ConstUtil.ACCOUNTS_STATUS_YJS;
}
else
{
cmd.Parameters.AddWithValue("@AccountsStatus", SqlDbType.Char).Value = ConstUtil.ACCOUNTS_STATUS_YJSZ;
}
cmd.Parameters.AddWithValue("@NAccounts", SqlDbType.Decimal).Value = ProcPrice;
lstCommand.Add(cmd);
}