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