/// <summary> /// 删除付款单信息级联更新对应的业务单的已付金额和未付金额 /// </summary> /// <param name="ids"></param> /// <returns></returns> public static bool DeletePayBill(string ids) { SqlConnection conn = new SqlConnection(SqlHelper._connectionStringStr); conn.Open(); SqlTransaction mytran = conn.BeginTransaction(); try { string[] Str = ids.Split(','); for (int i = 0; i < Str.Length; i++) { string deletePayBillingSQL = "delete from officedba.PayBill where ID=@IDStr" + i + " ";//删除付款单sql string updateBillingAmountSQL = "Update officedba.Billing set YAccounts=isnull(YAccounts,0)-(@PayAmount" + i + "),NAccounts=isnull(NAccounts,0)+(@PayAmount" + i + ") where ID=@ID" + i + ""; DataTable dt = GetBillingInfo(int.Parse(Str[i].ToString())); string billingID = dt.Rows[0]["BillingID"].ToString(); decimal PayAmount = Convert.ToDecimal(dt.Rows[0]["PayAmount"].ToString()); decimal YAccount = BillingDBHelper.GetBillingYAccounts(billingID);//获取业务单已付金额 decimal diffYAccount = YAccount - PayAmount; SqlParameter[] parms = new SqlParameter[2]; parms[0] = SqlHelper.GetParameter("@PayAmount" + i + "", PayAmount); parms[1] = SqlHelper.GetParameter("@ID" + i + "", billingID); SqlHelper.ExecuteNonQuery(mytran, CommandType.Text, updateBillingAmountSQL, parms); string updateStautsSQL = "Update officedba.Billing set AccountsStatus=@AccountsStatus" + i + " where ID=@IDS" + i + ""; SqlParameter[] parmss = new SqlParameter[2]; if (diffYAccount > 0)//结算中 { parmss[0] = SqlHelper.GetParameter("@AccountsStatus" + i + "", ConstUtil.ACCOUNTS_STATUS_YJSZ); parmss[1] = SqlHelper.GetParameter("@IDS" + i + "", billingID); } else//未结算 { parmss[0] = SqlHelper.GetParameter("@AccountsStatus" + i + "", ConstUtil.ACCOUNTS_STATUS_WJS); parmss[1] = SqlHelper.GetParameter("@IDS" + i + "", billingID); } SqlHelper.ExecuteNonQuery(mytran, CommandType.Text, updateStautsSQL, parmss); SqlParameter[] parmsss = new SqlParameter[1]; parmsss[0] = SqlHelper.GetParameter("@IDStr" + i + "", Str[i].ToString()); SqlHelper.ExecuteNonQuery(mytran, CommandType.Text, deletePayBillingSQL, parmsss); } mytran.Commit(); return(true); } catch { mytran.Rollback(); return(false); } finally { conn.Close(); } }
/// <summary> /// 根据主键获取业务单已付金额 /// </summary> /// <param name="ID">主键ID</param> /// <returns>decimal</returns> public static decimal GetBillingYAccounts(string ID) { return(BillingDBHelper.GetBillingYAccounts(ID)); }