/// <summary> /// 修改开票信息 /// </summary> /// <param name="model">开票实体</param> /// <returns>true 成功,false失败</returns> public static bool UpdateBillingInfo(BillingModel model) { //设置参数 ArrayList lstCommand = new ArrayList(); string Selectsql = "select SourceDT,SourceID from officedba.Billing where ID in (" + model.ID + ")"; DataTable dt = SqlHelper.ExecuteSql(Selectsql); string tablename, id = ""; if (dt != null && dt.Rows.Count > 0) { foreach (DataRow rows in dt.Rows) { tablename = rows["SourceDt"].ToString(); id = rows["SourceID"].ToString(); if (!string.IsNullOrEmpty(tablename) && !string.IsNullOrEmpty(id)) { SqlCommand comm = new SqlCommand(); comm.CommandText = "Update " + tablename + " set isOpenbill='0' where ID in ( " + id + " ) "; //添加命令 lstCommand.Add(comm); } } } StringBuilder sql = new StringBuilder(); sql.AppendLine("Update officedba.Billing set BillCD=@BillCD,"); sql.AppendLine("BillingNum=@BillingNum,BillingType=@BillingType,"); sql.AppendLine("InvoiceType=@InvoiceType,CreateDate=@CreateDate,ContactUnits=@ContactUnits,"); sql.AppendLine("AcceWay=@AcceWay,TotalPrice=@TotalPrice,NAccounts=@NAccounts,Executor=@Executor,"); sql.AppendLine("DeptID=@DeptID,SourceDt=@SourceDt,SourceID=@SourceID,ColumnName=@ColumnName,"); sql.AppendLine("Remark=@Remark,CurrencyType=@CurrencyType,CurrencyRate=@CurrencyRate,CustID=@CustID,FromTBName=@FromTBName,FileName=@FileName where ID=@ID"); SqlCommand cmd = new SqlCommand(); cmd.CommandText = sql.ToString(); cmd.Parameters.AddWithValue("@BillCD", SqlDbType.VarChar).Value = model.BillCD; cmd.Parameters.AddWithValue("@BillingNum", SqlDbType.VarChar).Value = model.BillingNum; cmd.Parameters.AddWithValue("@BillingType", SqlDbType.VarChar).Value = model.BillingType; cmd.Parameters.AddWithValue("@InvoiceType", SqlDbType.VarChar).Value = model.InvoiceType; cmd.Parameters.AddWithValue("@CreateDate", SqlDbType.DateTime).Value = model.CreateDate; cmd.Parameters.AddWithValue("@ContactUnits", SqlDbType.VarChar).Value = model.ContactUnits; cmd.Parameters.AddWithValue("@AcceWay", SqlDbType.VarChar).Value = model.AcceWay; cmd.Parameters.AddWithValue("@TotalPrice", SqlDbType.Decimal).Value = model.TotalPrice; cmd.Parameters.AddWithValue("@NAccounts", SqlDbType.Decimal).Value = model.TotalPrice; cmd.Parameters.AddWithValue("@Executor", SqlDbType.Int).Value = model.Executor; cmd.Parameters.AddWithValue("@DeptID", SqlDbType.Int).Value = model.DeptID; cmd.Parameters.AddWithValue("@SourceDt", SqlDbType.VarChar).Value = model.SourceDt; cmd.Parameters.AddWithValue("@SourceID", SqlDbType.VarChar).Value = model.SourceID; cmd.Parameters.AddWithValue("@ColumnName", SqlDbType.VarChar).Value = model.ColumnName; cmd.Parameters.AddWithValue("@Remark", SqlDbType.VarChar).Value = model.Remark; cmd.Parameters.AddWithValue("@CurrencyType", SqlDbType.Int).Value = model.CurrencyType; cmd.Parameters.AddWithValue("@CurrencyRate", SqlDbType.Decimal).Value = model.CurrencyRate; cmd.Parameters.AddWithValue("@CustID", SqlDbType.Int).Value = model.CustID; cmd.Parameters.AddWithValue("@FromTBName", SqlDbType.VarChar).Value = model.FromTBName; cmd.Parameters.AddWithValue("@FileName", SqlDbType.VarChar).Value = model.FileName; cmd.Parameters.AddWithValue("@ID", SqlDbType.Int).Value = model.ID; lstCommand.Add(cmd); StringBuilder cmdsql = new StringBuilder(); if (!string.IsNullOrEmpty(model.SourceDt) && !string.IsNullOrEmpty(model.SourceID)) { cmdsql.AppendLine("Update " + model.SourceDt + " set isOpenbill='1' where ID in ( " + model.SourceID + " ) "); SqlCommand updatecmd = new SqlCommand(); updatecmd.CommandText = cmdsql.ToString(); lstCommand.Add(updatecmd); } return SqlHelper.ExecuteTransWithArrayList(lstCommand); }
/// <summary> /// 添加开票信息 /// </summary> /// <param name="model">开票实体</param> /// <returns>true 成功,false失败</returns> public static bool InsertBillingInfo(BillingModel model,out int ID) { SqlConnection conn = new SqlConnection(SqlHelper._connectionStringStr); conn.Open(); SqlTransaction mytran = conn.BeginTransaction(); try { StringBuilder sql = new StringBuilder(); sql.AppendLine("Insert into officedba.Billing"); sql.AppendLine("(CompanyCD,BillCD,BillingNum,BillingType,"); sql.AppendLine("InvoiceType,CreateDate,ContactUnits,AcceWay,"); sql.AppendLine("TotalPrice,NAccounts,Executor,DeptID,SourceDt,"); sql.AppendLine("SourceID,ColumnName,Remark,CurrencyType,CurrencyRate,CustID,FromTBName,FileName)"); sql.AppendLine("values(@CompanyCD,@BillCD,@BillingNum,@BillingType,"); sql.AppendLine("@InvoiceType,@CreateDate,@ContactUnits,@AcceWay,"); sql.AppendLine("@TotalPrice,@NAccounts,@Executor,@DeptID,@SourceDt,@SourceID,"); sql.AppendLine("@ColumnName,@Remark,@CurrencyType,@CurrencyRate,@CustID,@FromTBName,@FileName)"); sql.AppendLine("set @IntID= @@IDENTITY"); SqlParameter[] parms = new SqlParameter[22]; parms[0] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD); parms[1] = SqlHelper.GetParameter("@BillCD", model.BillCD); parms[2] = SqlHelper.GetParameter("@BillingNum", model.BillingNum); parms[3] = SqlHelper.GetParameter("@BillingType", model.BillingType); parms[4] = SqlHelper.GetParameter("@InvoiceType", model.InvoiceType); parms[5] = SqlHelper.GetParameter("@CreateDate", model.CreateDate); parms[6] = SqlHelper.GetParameter("@ContactUnits", model.ContactUnits); parms[7] = SqlHelper.GetParameter("@AcceWay", model.AcceWay); parms[8] = SqlHelper.GetParameter("@TotalPrice", model.TotalPrice); parms[9] = SqlHelper.GetParameter("@NAccounts", model.TotalPrice); parms[10] = SqlHelper.GetParameter("@Executor", model.Executor); parms[11] = SqlHelper.GetParameter("@DeptID", model.DeptID); parms[12] = SqlHelper.GetParameter("@SourceDt", model.SourceDt); parms[13] = SqlHelper.GetParameter("@SourceID", model.SourceID); parms[14] = SqlHelper.GetParameter("@ColumnName", model.ColumnName); parms[15] = SqlHelper.GetParameter("@Remark", model.Remark); parms[16] = SqlHelper.GetParameter("@CurrencyType", model.CurrencyType); parms[17] = SqlHelper.GetParameter("@CurrencyRate", model.CurrencyRate); parms[18] = SqlHelper.GetParameter("@CustID", model.CustID); parms[19] = SqlHelper.GetParameter("@FromTBName", model.FromTBName); parms[20] = SqlHelper.GetParameter("@FileName", model.FileName); parms[21] = SqlHelper.GetOutputParameter("@IntID", SqlDbType.Int); int rev = SqlHelper.ExecuteNonQuery(mytran, CommandType.Text, sql.ToString(), parms); ID = Convert.ToInt32(parms[21].Value); string tablename, id = ""; StringBuilder cmdsql = new StringBuilder(); tablename = model.SourceDt; id = model.SourceID; if (!string.IsNullOrEmpty(tablename) && !string.IsNullOrEmpty(id)) { cmdsql.AppendLine("Update " + tablename + " set isOpenbill='1' where ID in ( "+id+" ) "); SqlHelper.ExecuteNonQuery(mytran, CommandType.Text, cmdsql.ToString(), null); } mytran.Commit(); return rev > 0 ? true : false; } catch (Exception ex) { mytran.Rollback(); throw ex; } finally { conn.Close(); conn.Dispose(); } }