/// <summary>
/// 添加自定义表单
/// </summary>
/// <returns></returns>
public static int AddTable(CustomTableModel model, List<StructTable> sonModel, out string strMsg)
{
int TableID = 0;
strMsg = "";
//判断单据编号是否存在
if (!Exists(model.CompanyCD, model.CustomTableName))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
TableID = AddCustomTable(model, tran);
AddStructTable(sonModel, TableID, tran);
tran.Commit();
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
strMsg = "该表名已被使用，请输入未使用的表名！";
}
return TableID;
}
public static int Add(XBase.Model.Office.SellReport.UserProductInfo model)
{
int num = 0;
string sqlstr = "insert into officedba.UserProductInfo(CompanyCD,productNum,productName,price,bref,memo) values(@CompanyCD,@productNum,@productName,@price,@bref,@memo)";
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
SqlParameter[] param = {
new SqlParameter("@CompanyCD",SqlDbType.VarChar,50),
new SqlParameter("@productNum",SqlDbType.VarChar,50),
new SqlParameter("@productName",SqlDbType.VarChar,200),
new SqlParameter("@price",SqlDbType.Decimal),
new SqlParameter("@bref",SqlDbType.VarChar,500),
new SqlParameter("@memo",SqlDbType.VarChar,1000)
};
param[0].Value = model.CompanyCD;
param[1].Value = model.productNum;
param[2].Value = model.productName;
param[3].Value = model.price;
param[4].Value = model.bref;
param[5].Value = model.memo;
try
{
num = SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, sqlstr, param);
tran.Commit();
}
catch
{
tran.Rollback();
}
return num;
}
/// <summary>
/// 添加费用申请单
/// </summary>
/// <param name="expApplyModel"></param>
/// <param name="expDetailModelList"></param>
/// <param name="strMsg"></param>
/// <returns></returns>
public static bool SaveExpensesApply(ExpensesApplyModel expApplyModel, List<ExpDetailsModel> expDetailModelList,out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
//判断单据编号是否存在
if (NoIsExist(expApplyModel.ExpCode,expApplyModel.CompanyCD))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
int expID;
expID = InsertExpensesApply(expApplyModel, tran);
InsertExpensesDetails(expDetailModelList, expID,tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "该编号已被使用，请输入未使用的编号！";
}
return isSucc;
}
/// <summary>
/// 更新销售委托代销单
/// </summary>
/// <returns></returns>
public static bool UpdateOrder(Hashtable ht, SellChannelSttlModel sellChannelSttlModel, List<SellChannelSttlDetailModel> sellChannelSttlDetailModellist, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
if (IsUpdate(sellChannelSttlModel.SttlNo))
{
string strSql = "delete from officedba.SellChannelSttlDetail where SttlNo=@SttlNo and CompanyCD=@CompanyCD";
SqlParameter[] paras = { new SqlParameter("@SttlNo", sellChannelSttlModel.SttlNo), new SqlParameter("@CompanyCD", sellChannelSttlModel.CompanyCD) };
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
UpdateOrderInfo(ht,sellChannelSttlModel, tran);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras);
InsertOrderDetail(sellChannelSttlDetailModellist, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "非制单状态的未提交审批、审批未通过或撤销审批单据不可修改！";
}
return isSucc;
}
/// <summary>
/// 销售汇报 插入主表信息
/// </summary>
/// <param name="sellrptModel"></param>
/// <param name="tran"></param>
private static int InsertSellReport(SellReportModel sellrptModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("insert into officedba.SellReport(");
strSql.AppendLine("CompanyCD,SellDept,productID,productName,price,sellNum,sellPrice,createdate,memo)");
strSql.AppendLine(" values (");
strSql.AppendLine("@CompanyCD,@SellDept,@productID,@productName,@price,@sellNum,@sellPrice,@createdate,@memo)");
strSql.AppendLine(";set @Id=@@IDENTITY");
SqlParameter[] param = {
new SqlParameter("@CompanyCD",sellrptModel.CompanyCD),
new SqlParameter("@SellDept",sellrptModel.SellDept),
new SqlParameter("@productID",sellrptModel.ProductID),
new SqlParameter("@productName",sellrptModel.ProductName),
new SqlParameter("@price",sellrptModel.Price),
new SqlParameter("@sellNum",sellrptModel.SellNum),
new SqlParameter("@sellPrice",sellrptModel.SellPrice),
new SqlParameter("@createdate",sellrptModel.CreateDate),
new SqlParameter("@memo",sellrptModel.Memo),
new SqlParameter("@Id",SqlDbType.Int,6)
};
param[9].Direction = ParameterDirection.Output;
foreach (SqlParameter para in param)
{
if (para.Value == null)
{
para.Value = DBNull.Value;
}
}
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param);
int Id = Convert.ToInt32(param[9].Value);
return Id;
}
/// <summary>
/// 保存销售委托代销单
/// </summary>
/// <returns></returns>
public static bool SaveOrder(Hashtable ht, SellChannelSttlModel sellChannelSttlModel, List<SellChannelSttlDetailModel> sellChannelSttlDetailModellist, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
//判断单据编号是否存在
if (NoIsExist(sellChannelSttlModel.SttlNo))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
InsertOrderInfo(ht,sellChannelSttlModel, tran);
InsertOrderDetail(sellChannelSttlDetailModellist, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "该编号已被使用，请输入未使用的编号！";
}
return isSucc;
}
/// <summary>
/// 保存费用报销单
/// </summary>
/// <param name="reimbModel"></param>
/// <param name="reimbDetailList"></param>
/// <param name="strMsg"></param>
public static bool SaveReimbursement(ReimbursementModel reimbModel, List<ReimbDetailsModel> reimbDetailList, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
//判断单据编号是否存在
if (NoIsExist(reimbModel.ReimbNo, reimbModel.CompanyCD))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
int reimbID;
reimbID = InsertReimbursement(reimbModel, tran);
InsertReimbDetails(reimbDetailList, reimbID, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "该编号已被使用，请输入未使用的编号！";
}
return isSucc;
}
/// <summary>
/// 修改自定义表单
/// </summary>
/// <param name="model"></param>
/// <param name="sonModel"></param>
/// <param name="strMsg"></param>
/// <returns></returns>
public static bool EditTable(CustomTableModel model, List<StructTable> sonModel, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
string strSql = "delete from defdba.StructTable where TableID=@TableID ";
SqlParameter[] paras = { new SqlParameter("@TableID", model.ID) };
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
EidtCustomTable(model, tran);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras);
AddStructTable(sonModel, Convert.ToInt32(model.ID), tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
return isSucc;
}
public static int Update(XBase.Model.Office.SellReport.UserProductInfo model)
{
int num = 0;
string sqlstr = "update officedba.UserProductInfo set productNum=@productNum,productName=@productName,price=@price,bref=@bref,memo=@memo where id=@id";
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
SqlParameter[] param = {
new SqlParameter("@CompanyCD",SqlDbType.VarChar,50),
new SqlParameter("@productNum",SqlDbType.VarChar,50),
new SqlParameter("@productName",SqlDbType.VarChar,200),
new SqlParameter("@price",SqlDbType.Decimal),
new SqlParameter("@bref",SqlDbType.VarChar,500),
new SqlParameter("@memo",SqlDbType.VarChar,1000),
new SqlParameter("@id",SqlDbType.Int,4)
};
param[0].Value = model.CompanyCD;
param[1].Value = model.productNum;
param[2].Value = model.productName;
param[3].Value = model.price;
param[4].Value = model.bref;
param[5].Value = model.memo;
param[6].Value = model.id;
try
{
num = SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, sqlstr, param);
tran.Commit();
}
catch
{
tran.Rollback();
}
return num;
}
public static int AddSubBudgetInfo(SubBudgetModel subBudgetModel, XBase.Common.UserInfoUtil userinfo)
{
StringBuilder sqlstr = new StringBuilder();
sqlstr.Append("insert into officedba.SubBudget(CompanyCD,projectid,BudgetName) values(@CompanyCD,@projectid,@BudgetName)");
SqlParameter[] param = {
new SqlParameter("@CompanyCD",SqlDbType.VarChar,50),
new SqlParameter("@projectid",SqlDbType.Int),
new SqlParameter("@BudgetName",SqlDbType.VarChar,200)
};
param[0].Value = userinfo.CompanyCD;
param[1].Value = subBudgetModel.Projectid;
param[2].Value = subBudgetModel.BudgetName;
TransactionManager tran = new TransactionManager();
int num = 0;
tran.BeginTransaction();
try
{
num = SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, sqlstr.ToString(), param);
tran.Commit();
}
catch
{
tran.Rollback();
}
return num;
}
public static int EditSubBudget(SubBudgetModel subBudgetModel, XBase.Common.UserInfoUtil userinfo)
{
StringBuilder sqlstr = new StringBuilder();
sqlstr.Append("update officedba.SubBudget set BudgetName=@BudgetName,projectid=@projectid where ID=@ID");
SqlParameter[] param = {
new SqlParameter("@BudgetName",SqlDbType.VarChar,200),
new SqlParameter("@projectid",SqlDbType.Int,4),
new SqlParameter("@ID",SqlDbType.Int)
};
param[0].Value = subBudgetModel.BudgetName;
param[1].Value = subBudgetModel.Projectid;
param[2].Value = subBudgetModel.ID;
TransactionManager tran = new TransactionManager();
int num = 0;
tran.BeginTransaction();
try
{
num = SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, sqlstr.ToString(), param);
tran.Commit();
}
catch
{
tran.Rollback();
}
return num;
}
/// <summary>
/// 保存批次规则设置
/// </summary>
/// <param name="model">BachNoRuleSet实体</param>
/// <param name="strMsg">返回信息</param>
/// <returns>规则ID</returns>
public static int SaveBatchRule(BatchNoRuleSet model, out string strMsg)
{
int ruleID = 0;
strMsg = "";
StringBuilder strSql = new StringBuilder();
if (!IsExisted(model.CompanyCD))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
strSql.AppendLine(" insert into officedba.BatchRule ");
strSql.AppendLine(" (CompanyCD,RuleName,RulePrefix,RuleDateType,RuleNoLen,LastNo,RuleExample,");
strSql.AppendLine(" Remark,IsDefault,UsedStatus,ModifiedDate,ModifiedUserID) values");
strSql.AppendLine(" (@CompanyCD,@RuleName,@RulePrefix,@RuleDateType,@RuleNoLen,@LastNo,@RuleExample,");
strSql.AppendLine(" @Remark,@IsDefault,@UsedStatus,getdate(),@ModifiedUserID)");
strSql.AppendLine(" ;select @@IDENTITY ");
SqlParameter[] param = {
new SqlParameter("@CompanyCD",model.CompanyCD),
new SqlParameter("@RuleName",model.RuleName),
new SqlParameter("@RulePrefix",model.RulePrefix),
new SqlParameter("@RuleDateType",model.RuleDateType),
new SqlParameter("@RuleNoLen",model.RuleNoLen),
new SqlParameter("@LastNo",model.LastNo),
new SqlParameter("@RuleExample",model.RuleExample),
new SqlParameter("@Remark",model.Remark),
new SqlParameter("@IsDefault",model.IsDefault),
new SqlParameter("@UsedStatus",model.UsedStatus),
new SqlParameter("@ModifiedUserID",model.ModifiedUserID)
};
foreach (SqlParameter para in param)
{
if (para.Value == null)
{
para.Value = DBNull.Value;
}
}
ruleID = Convert.ToInt32(SqlHelper.ExecuteScalar(strSql.ToString(), param));
tran.Commit();
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
strMsg = "保存失败，已存在批次规则！";
}
return ruleID;
}
/// <summary>
/// 插入费用申请主表信息
/// </summary>
/// <param name="expApplyModel"></param>
/// <param name="tran"></param>
public static int InsertExpensesApply(ExpensesApplyModel expApplyModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.FeeApply (");
strSql.Append(" CompanyCD,ExpCode,Title,Applyor,AriseDate,NeedDate,TotalAmount,PayType,CanViewUser,");
strSql.Append(" Reason,DeptID,TransactorID,Status,Creator,CreateDate,ExpType,CustID,SellChanceNo,IsReimburse,");
strSql.Append(" ModifiedUserID,ModifiedDate,Confirmor,ConfirmDate,ProjectID,Attachment,EndReimbTime)");
strSql.Append(" values (@CompanyCD,@ExpCode,@Title,@Applyor,@AriseDate,@NeedDate,@TotalAmount,@PayType,@CanViewUser,");
strSql.Append(" @Reason,@DeptID,@TransactorID,@Status,@Creator,@CreateDate,");
strSql.Append(" @ExpType,@CustID,@SellChanceNo,@IsReimburse,@ModifiedUserID,@ModifiedDate,@Confirmor,@ConfirmDate,");
strSql.Append(" @ProjectID,@Attachment,@EndReimbTime); set @Id=@@IDENTITY");
#region 参数
SqlParameter[] param={
new SqlParameter ("@CompanyCD",expApplyModel.CompanyCD ),
new SqlParameter ("@ExpCode",expApplyModel.ExpCode ),
new SqlParameter("@Title",expApplyModel.Title ),
new SqlParameter("@Applyor",expApplyModel.Applyor ),
new SqlParameter("@AriseDate",expApplyModel.AriseDate ),
new SqlParameter("@NeedDate",expApplyModel.NeedDate ),
new SqlParameter("@TotalAmount",expApplyModel.TotalAmount ),
new SqlParameter("@PayType",expApplyModel.PayType ),
new SqlParameter("@Reason",expApplyModel.Reason ),
new SqlParameter("@DeptID",expApplyModel.DeptID ),
new SqlParameter("@TransactorID",expApplyModel.TransactorID ),
new SqlParameter("@Status",expApplyModel.Status ),
new SqlParameter("@Creator",expApplyModel.Creator ),
new SqlParameter("@CreateDate",expApplyModel.CreateDate ),
new SqlParameter("@ExpType",expApplyModel.ExpType ),
new SqlParameter("@CustID",expApplyModel.CustID ),
new SqlParameter("@SellChanceNo",expApplyModel.SellChanceNo ),
new SqlParameter("@IsReimburse",expApplyModel.IsReimburse ),
new SqlParameter("@ModifiedUserID",expApplyModel.ModifiedUserID ),
new SqlParameter("@ModifiedDate",expApplyModel.ModifiedDate ),
new SqlParameter("@Confirmor",DBNull.Value ),
new SqlParameter("@ConfirmDate",DBNull.Value),
new SqlParameter("@Id",SqlDbType.Int,6),
new SqlParameter("@CanViewUser",expApplyModel.CanViewUser),
new SqlParameter("@ProjectID",expApplyModel.ProjectID),
new SqlParameter("@Attachment",expApplyModel.Attachment),
new SqlParameter("@EndReimbTime",expApplyModel.EndReimbTime)
};
param[22].Direction=ParameterDirection.Output;
foreach (SqlParameter para in param)
{
//if (para.Value == null)
if (para.Value == null || para.Value.ToString() == "-1")
{
para.Value = DBNull.Value;
}
}
//SqlHelper.ExecuteSql(strSql.ToString(), param);
SqlHelper.ExecuteTransSql(strSql.ToString(), param);
int Id =Convert.ToInt32(param[22].Value);
return Id;
#endregion
}
/// <summary>
/// 费用报销单主表数据插入
/// </summary>
/// <param name="reimbModel">费用报销单主表实体</param>
/// <param name="tran">事务</param>
/// <returns>主表插入后返回的ID</returns>
public static int InsertReimbursement(ReimbursementModel reimbModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.FeeReturn (");
strSql.Append(" CompanyCD,ReimbNo,Title,FromType,Applyor,ReimbDate,ReimbDeptID,UserReimbID,CanViewUser,");
strSql.Append(" ExpAllAmount,ReimbAllAmount,RestoreAllAmount,Status,Creator,CreateDate,Remark,");
strSql.Append(" ModifiedUserID,ModifiedDate,Confirmor,ConfirmDate,ProjectID,SubjectsNo,CustID,ContactsUnitID,FromTBName,ContactsUnitName,Attachment)");
strSql.Append(" values (@CompanyCD,@ReimbNo,@Title,@FromType,@Applyor,@ReimbDate,@ReimbDeptID,@UserReimbID,@CanViewUser,");
strSql.Append(" @ExpAllAmount,@ReimbAllAmount,@RestoreAllAmount,@Status,@Creator,@CreateDate,");
strSql.Append(" @Remark,@ModifiedUserID,@ModifiedDate,@Confirmor,@ConfirmDate,@ProjectID,@SubjectsNo,@CustID,");
strSql.Append(" @ContactsUnitID,@FromTBName,@ContactsUnitName,@Attachment); set @Id=@@IDENTITY");
SqlParameter[] param ={
new SqlParameter("@CompanyCD",reimbModel.CompanyCD ),
new SqlParameter("@ReimbNo",reimbModel.ReimbNo ),
new SqlParameter("@Title",reimbModel.Title ),
new SqlParameter("@FromType",reimbModel.FromType),
new SqlParameter("@Applyor",reimbModel.Applyor ),
new SqlParameter("@ReimbDate",reimbModel.ReimbDate ),
new SqlParameter("@ReimbDeptID",reimbModel.ReimbDeptID ),
new SqlParameter("@UserReimbID",reimbModel.UserReimbID ),
new SqlParameter("@ExpAllAmount",reimbModel.ExpAllAmount ),
new SqlParameter("@ReimbAllAmount",reimbModel.ReimbAllAmount ),
new SqlParameter("@RestoreAllAmount",reimbModel.RestoreAllAmount ),
new SqlParameter("@Status",reimbModel.Status ),
new SqlParameter("@Creator",reimbModel.Creator ),
new SqlParameter("@CreateDate",reimbModel.CreateDate ),
new SqlParameter("@Remark",reimbModel.Remark ),
new SqlParameter("@ModifiedUserID",reimbModel.ModifiedUserID ),
new SqlParameter("@ModifiedDate",reimbModel.ModifiedDate ),
new SqlParameter("@Confirmor",DBNull.Value ),
new SqlParameter("@ConfirmDate",DBNull.Value),
new SqlParameter("@Id",SqlDbType.Int,6),
new SqlParameter("@CanViewUser",reimbModel.CanViewUser),
new SqlParameter("@ProjectID",reimbModel.ProjectID),
new SqlParameter("@SubjectsNo",reimbModel.SubjectsNo),
new SqlParameter("@CustID",reimbModel.CustID),
new SqlParameter("@ContactsUnitID",reimbModel.ContactsUnitID),
new SqlParameter("@FromTBName",reimbModel.FromTBName),
new SqlParameter("@ContactsUnitName",reimbModel.ContactsUnitName),
new SqlParameter("@Attachment",reimbModel.Attachment)
};
param[19].Direction = ParameterDirection.Output;
foreach (SqlParameter para in param)
{
if (para.Value == null || para.Value.ToString() == "-1")
{
para.Value = DBNull.Value;
}
}
SqlHelper.ExecuteTransSql(strSql.ToString(), param);
int Id = Convert.ToInt32(param[19].Value);
return Id;
}
/// <summary>
/// 更新销售计划
/// </summary>
/// <returns></returns>
public static bool Update(Hashtable ht,SellPlanModel sellPlanModel, SellPlanDetailModel sellPlanDetailModel, string strDetailAction, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
if (IsUpdate(sellPlanModel.PlanNo))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
UpdateOrder(sellPlanModel, tran);
#region 拓展属性
GetExtAttrCmd(sellPlanModel, ht, tran);
#endregion
//明细操作的类型
switch (strDetailAction)
{
case "1"://无操作
break;
case "2"://添加新明细
InsertOrderDetail(sellPlanDetailModel, tran);
break;
case "3"://更新明细
UpdateOrderDetail(sellPlanDetailModel, tran);
break;
case "4"://删除明细
DelOrderDetail(sellPlanDetailModel, tran);
break;
default:
break;
}
tran.Commit();
strMsg = "保存成功！";
isSucc = true;
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "非制单状态的未提交审批、审批未通过或撤销审批计划不可修改！";
}
return isSucc;
}
/// <summary>
/// 保存模板
/// </summary>
/// <param name="tbModel">ModuleTableModel模板实体</param>
/// <param name="strMsg"></param>
public static int SaveTableModel(ModuleTableModel tbModel, out string strMsg)
{
StringBuilder strSql = new StringBuilder();
strMsg = "";
int tbID = 0;
if (!IsRepeatedModule(tbModel.TableID, "0"))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
strSql.AppendLine(" insert into defdba.ModuleTable (CompanyCD,ModuleContent,TableID,ModuleType,UseStatus)");
strSql.AppendLine(" values(@CompanyCD,@ModuleContent,@TableID,@ModuleType,@UseStatus)");
strSql.AppendLine(" ;select @@IDENTITY ");
SqlParameter[] param = {
new SqlParameter("@CompanyCD",tbModel.CompanyCD ),
new SqlParameter("@ModuleContent",tbModel.ModuleContent ),
new SqlParameter("@TableID",tbModel.TableID ),
new SqlParameter("@ModuleType",tbModel.ModuleType ),
new SqlParameter("@UseStatus",tbModel.UseStatus )
};
foreach (SqlParameter para in param)
{
if (para.Value == null)
{
para.Value = DBNull.Value;
}
}
tbID = Convert.ToInt32(SqlHelper.ExecuteScalar(strSql.ToString(), param));
tran.Commit();
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
strMsg = "保存失败，该表的模板已定义！";
}
return tbID;
}
/// <summary>
/// 添加销售机会及阶段
/// </summary>
/// <param name="sellChanceModel">销售机会表实体</param>
/// <param name="sellChancePushModel">销售阶段表实体</param>
/// <returns>是否添加成功</returns>
public static bool InsertSellChance(Hashtable ht,SellChanceModel sellChanceModel, SellChancePushModel sellChancePushModel)
{
bool isSucc = false;//是否添加成功
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
int sellChID = InsertChanece(tran, sellChanceModel);
//若是设置了手机提醒 则插入以下信息
if (sellChanceModel.IsMobileNotice == "1")
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.NoticeHistory(");
strSql.Append(" CompanyCD,SourceFlag,SourceID,PlanNoticeDate ) ");
strSql.Append(" values(@CompanyCD, @SourceFlag,@SourceID,@PlanNoticeDate ) ");
SqlParameter[] param = {
new SqlParameter("@CompanyCD",sellChanceModel.CompanyCD),
new SqlParameter("@SourceFlag","4"),
new SqlParameter("@SourceID",sellChID),
new SqlParameter("@PlanNoticeDate",sellChanceModel.RemindTime)
};
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param);//.ExecuteTransWithCommand(commN);
}
//拓展属性
GetExtAttrCmd(sellChanceModel,ht, tran);
InsertPush(tran, sellChancePushModel);
tran.Commit();
isSucc = true;
}
catch (Exception ex)
{
tran.Rollback();
isSucc = false;
throw ex;
}
return isSucc;
}
/// <summary>
/// 添加新单据
/// </summary>
/// <returns></returns>
public static bool Insert(VoucherTemplateModel voucherTemplateModel, List<VoucherTemplateDetailModel> voucherTemplateDetailModelList, out string strMsg, out int Id)
{
bool isSucc = false;//是否添加成功
strMsg = "";
Id = 0;
//判断单据编号是否存在
if (NoIsExist(voucherTemplateModel.TemNo))
{
if (IsTypeUsed(voucherTemplateModel.TemType.ToString(), voucherTemplateModel.CompanyCD))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
InsertVoucherTemplate(voucherTemplateModel, tran, out Id);
InsertVoucherTemplateDetail(voucherTemplateDetailModelList, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "该模板类型已存在，请选择其他未使用的模板类型！";
}
}
else
{
isSucc = false;
strMsg = "该编号已被使用，请输入未使用的编号！";
}
return isSucc;
}
/// <summary>
/// 添加销售报价单
/// </summary>
/// <param name="sellChanceModel">销售机会表实体</param>
/// <param name="sellChancePushModel">销售阶段表实体</param>
/// <returns>是否添加成功</returns>
public static bool InsertOrder(Hashtable ht, SellOfferModel sellOfferModel, List<SellOfferDetailModel> SellOrderDetailModelList,
List<SellOfferHistoryModel> sellOfferHistoryModelList, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
//判断单据编号是否存在
if (NoIsExist(sellOfferModel.OfferNo))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
InsertSellOffer(sellOfferModel, tran);
//拓展属性
GetExtAttrCmd(sellOfferModel, ht, tran);
InsertSellOfferDetail(sellOfferModel, SellOrderDetailModelList, tran);
InsertSellOfferHistory(sellOfferModel, sellOfferHistoryModelList, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
isSucc = false;
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "该编号已被使用，请输入未使用的编号！";
}
return isSucc;
}
/// <summary>
/// 保存销售计划
/// </summary>
/// <returns></returns>
public static bool Save(Hashtable ht,SellPlanModel sellPlanModel, SellPlanDetailModel sellPlanDetailModel, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
//判断计划编号是否存在
if (NoIsExist(sellPlanModel.PlanNo))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
InsertOrder(sellPlanModel, tran);
#region 拓展属性
GetExtAttrCmd(sellPlanModel, ht, tran);
#endregion
if (sellPlanDetailModel.PlanNo != null)
{
InsertOrderDetail(sellPlanDetailModel, tran);
}
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "该编号已被使用，请输入未被使用的编号！";
}
return isSucc;
}
/// <summary>
/// 修改销售报价单
/// </summary>
/// <param name="sellChanceModel">销售机会表实体</param>
/// <param name="sellChancePushModel">销售阶段表实体</param>
/// <returns>是否添加成功</returns>
public static bool UpdateOrder(Hashtable ht,SellOfferModel sellOfferModel, List<SellOfferDetailModel> SellOrderDetailModelList,
List<SellOfferHistoryModel> sellOfferHistoryModelList, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
if (IsUpdate(sellOfferModel.OfferNo))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
UpdateSellOffer(sellOfferModel, tran);
//拓展属性
GetExtAttrCmd(sellOfferModel, ht, tran);
InsertSellOfferDetail(sellOfferModel, SellOrderDetailModelList, tran);
InsertSellOfferHistory(sellOfferModel, sellOfferHistoryModelList, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
isSucc = false;
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "非制单状态的未提交审批、审批未通过或撤销审批单据不可修改！";
}
return isSucc;
}
/// <summary>
/// 添加单据
/// </summary>
/// <param name="sellChanceModel">销售机会表实体</param>
/// <param name="sellChancePushModel">销售阶段表实体</param>
/// <returns>是否添加成功</returns>
public static bool InsertOrder(AdversaryInfoModel adversaryInfoModel, List<AdversaryDynamicModel> adversaryDynamicModelList)
{
bool isSucc = false;//是否添加成功
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
InsertAdversaryInfo( adversaryInfoModel, tran);
InsertAdversaryDynamic(adversaryDynamicModelList, tran);
tran.Commit();
isSucc = true;
}
catch(Exception ex)
{
tran.Rollback();
isSucc = false;
throw ex;
}
return isSucc;
}
public static bool Insert(SellReportModel sellrptModel, List<SellReportDetailModel> sellRptDetailModellList, out string strMsg)
{
bool isSucc = false;//是否添加成功
int billID = 0;
strMsg = "";
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
billID = InsertSellReport(sellrptModel, tran);
InsertSellReportDetail(sellRptDetailModellList,billID, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！|"+billID;
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
return isSucc;
}
public static bool UpdateMoveApplyCancelConfirm(string BillStatus, string ID, string userID, string CompanyID, string ReprotNo)
{
try
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE officedba.RectApply");
sql.AppendLine(" SET BillStatus=@Status ");
sql.AppendLine(" ,ModifiedDate=getdate() ");
sql.AppendLine(" ,ModifiedUserID=@ModifiedUserID ");
sql.AppendLine(" ,ConfirmDate = null ");
sql.AppendLine(" ,Confirmor = null ");
sql.AppendLine("WHERE ");
sql.AppendLine(" CompanyCD = @CompanyCD ");
sql.AppendLine(" AND RectApplyNo = @RectApplyNo ");
SqlParameter[] param;
param = new SqlParameter[4];
param[0] = SqlHelper.GetParameter("@Status", BillStatus);
param[1] = SqlHelper.GetParameter("@ModifiedUserID", userID);
param[2] = SqlHelper.GetParameter("@CompanyCD", CompanyID);
param[3] = SqlHelper.GetParameter("@RectApplyNo", ReprotNo);
//SqlHelper.ExecuteTransSql(sql.ToString(), param);
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
FlowDBHelper.OperateCancelConfirm(CompanyID, Convert.ToInt32(XBase.Common.ConstUtil.BILL_TYPEFLAG_HUMAN), Convert.ToInt32(XBase.Common.ConstUtil.BILL_TYPECODE_HUMAN_RECT_APPLY), Convert.ToInt32(ID), userID, tran);//取消确认
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, sql.ToString(), param);
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
}
catch
{
return false;
}
}
/// <summary>
/// 取消调职确认信息
/// </summary>
/// <param name="CarApplyM">车辆申请信息</param>
/// <returns>添加是否成功 false:失败，true:成功</returns>
public static bool UpdateMoveApplyCancelConfirm(string BillStatus,string ID, string userID, string CompanyID)
{
try
{
#region 车辆申请信息SQL拼写
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE officedba.EmplApply");
sql.AppendLine(" SET Status=@Status ");
sql.AppendLine(" ,ModifiedDate=getdate() ");
sql.AppendLine(" ,ModifiedUserID=@ModifiedUserID ");
sql.AppendLine("WHERE ");
sql.AppendLine(" ID=@ID ");
#endregion
#region 车辆申请信息参数设置
SqlParameter[] param;
param = new SqlParameter[3];
param[0] = SqlHelper.GetParameter("@Status", BillStatus);
param[1] = SqlHelper.GetParameter("@ModifiedUserID", userID);
param[2] = SqlHelper.GetParameter("@ID", ID);
#endregion
//SqlHelper.ExecuteTransSql(sql.ToString(), param);
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
FlowDBHelper.OperateCancelConfirm(CompanyID, 2, 12, Convert.ToInt32(ID), userID, tran);//取消确认
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, sql.ToString(), param);
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
}
catch
{
return false;
}
}
/// <summary>
/// 删除审批流程相关信息
/// 2010-8-17 add by hexw
/// </summary>
/// <param name="tran">事务</param>
/// <param name="billIDStr">单据ID串，以逗号隔开</param>
/// <param name="strCompanyCD">公司编码</param>
/// <param name="typeflag"></param>
/// <param name="typecode"></param>
public static void DelFlowInfo(TransactionManager tran, string billIDStr, string strCompanyCD, string typeflag, string typecode)
{
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "delete from officedba.FlowTaskList where FlowInstanceID in (select ID from officedba.FlowInstance where billid in (" + billIDStr + ") and billtypeflag='" + typeflag + "' and billtypecode='" + typecode + "' and companycd='" + strCompanyCD + "')", null);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "delete from officedba.FlowTaskHistory where FlowInstanceID in (select ID from officedba.FlowInstance where billid in (" + billIDStr + ") and billtypeflag='" + typeflag + "' and billtypecode='" + typecode + "' and companycd='" + strCompanyCD + "')", null);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "delete from officedba.FlowInstance where billid in (" + billIDStr + ") and billtypeflag='" + typeflag + "' and billtypecode='" + typecode + "' and CompanyCD='" + strCompanyCD + "'", null);
}
/// <summary>
/// 更新流程实例表
/// </summary>
/// <param name="tran"></param>
/// <param name="CompanyCD"></param>
/// <param name="FlowNo"></param>
/// <param name="BillTypeFlag"></param>
/// <param name="BillTypeCode"></param>
/// <param name="BillID"></param>
/// <returns></returns>
public static void OperateCancelConfirm3(TransactionManager tran, string CompanyCD, string FlowNo, int BillTypeFlag, int BillTypeCode, int BillID, string loginUserID)
{
//--3更新流程实例表（officedba.FlowInstance）中的流程状态为“撤销审批”
//Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID
//Where CompanyCD=@CompanyCD
//and FlowNo=@tempFlowNo
//and BillTypeFlag=@BillTypeFlag
//and BillTypeCode=@BillTypeCode
//and BillID=@BillID
StringBuilder strSql = new StringBuilder();
strSql.Append("Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID ");
strSql.Append("Where CompanyCD=@CompanyCD ");
strSql.Append("and FlowNo=@tempFlowNo ");
strSql.Append("and BillTypeFlag=@BillTypeFlag ");
strSql.Append("and BillTypeCode=@BillTypeCode ");
strSql.Append("and BillID=@BillID");
#region 参数
//设置参数
SqlParameter[] param = new SqlParameter[6];
param[0] = SqlHelper.GetParameter("@CompanyCD", CompanyCD);
param[1] = SqlHelper.GetParameter("@tempFlowNo", FlowNo);
param[2] = SqlHelper.GetParameter("@BillTypeFlag", BillTypeFlag);
param[3] = SqlHelper.GetParameter("@BillTypeCode", BillTypeCode);
param[4] = SqlHelper.GetParameter("@BillID", BillID);
param[5] = SqlHelper.GetParameter("@ModifiedUserID", loginUserID);
#endregion
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param);
}
/// <summary>
/// 更新流程任务处理表
/// </summary>
/// <param name="tran"></param>
/// <param name="CompanyCD"></param>
/// <param name="FlowInstanceID"></param>
/// <param name="FlowNo"></param>
/// <param name="BillTypeFlag"></param>
/// <param name="BillID"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static void OperateCancelConfirm2(TransactionManager tran, string CompanyCD, string FlowInstanceID, string loginUserID)
{
//--2.更新流程任务处理表（officedba.FlowTaskList）中的流程步骤序号为0（表示返回到流程提交人环节）
//Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID
//Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID
StringBuilder strSql = new StringBuilder();
strSql.Append("Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID ");
strSql.Append("Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID");
#region 参数
//设置参数
SqlParameter[] param = new SqlParameter[3];
param[0] = SqlHelper.GetParameter("@CompanyCD", CompanyCD);
param[1] = SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID);
param[2] = SqlHelper.GetParameter("@ModifiedUserID", loginUserID);
#endregion
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param);
}
/// <summary>
/// 往流程任务历史记录表
/// </summary>
/// <param name="tran"></param>
/// <param name="CompanyCD"></param>
/// <param name="FlowInstanceID"></param>
/// <param name="FlowNo"></param>
/// <param name="BillTypeFlag"></param>
/// <param name="BillID"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static void OperateCancelConfirm1(TransactionManager tran, string CompanyCD, string FlowInstanceID, string FlowNo, int BillTypeFlag, int BillID, string loginUserID)
{
//--1.往流程任务历史记录表（officedba.FlowTaskHistory）插1条处理记录，
//--记录的步骤序号为0（表示返回到流程提交人环节)，审批状态为撤销审批
//Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate)
//Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate())
StringBuilder strSql = new StringBuilder();
strSql.Append("Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate) ");
strSql.Append("Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate())");
#region 参数
//设置参数
SqlParameter[] param = new SqlParameter[6];
param[0] = SqlHelper.GetParameter("@CompanyCD", CompanyCD);
param[1] = SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID);
param[2] = SqlHelper.GetParameter("@tempFlowNo", FlowNo);
param[3] = SqlHelper.GetParameter("@BillTypeFlag", BillTypeFlag);
param[4] = SqlHelper.GetParameter("@BillID", BillID);
param[5] = SqlHelper.GetParameter("@ModifiedUserID", loginUserID);
#endregion
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param);
}
public static void OperateCancelConfirm(string CompanyCD, int BillTypeFlag, int BillTypeCode, int BillID, string loginUserID, TransactionManager tran)
{
//可参见撤消审批的存储过程[FlowApproval_Update]
//--1.往流程任务历史记录表（officedba.FlowTaskHistory）插1条处理记录，
//--记录的步骤序号为0（表示返回到流程提交人环节)，审批状态为撤销审批
//Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate)
//Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate())
//--2.更新流程任务处理表（officedba.FlowTaskList）中的流程步骤序号为0（表示返回到流程提交人环节）
//Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID
//Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID
//--3更新流程实例表（officedba.FlowInstance）中的流程状态为“撤销审批”
//Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID
//Where CompanyCD=@CompanyCD
//and FlowNo=@tempFlowNo
//and BillTypeFlag=@BillTypeFlag
//and BillTypeCode=@BillTypeCode
//and BillID=@BillID
DataTable dt = GetFlowInstanceInfo(CompanyCD, BillTypeFlag, BillTypeCode, BillID);
if (dt.Rows.Count > 0)
{
string FlowInstanceID = dt.Rows[0]["FlowInstanceID"].ToString();
string FlowStatus = dt.Rows[0]["FlowStatus"].ToString();
string FlowNo = dt.Rows[0]["FlowNo"].ToString();
OperateCancelConfirm1(tran, CompanyCD, FlowInstanceID, FlowNo, BillTypeFlag, BillID, loginUserID);
OperateCancelConfirm2(tran, CompanyCD, FlowInstanceID, loginUserID);
OperateCancelConfirm3(tran, CompanyCD, FlowNo, BillTypeFlag, BillTypeCode, BillID, loginUserID);
}
}
