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