/// <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 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>
/// <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>
/// <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="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 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 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>
/// <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>
/// <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 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>
/// <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;
}
/// <summary>
/// 取消确认单据
/// </summary>
/// <param name="OrderNO">单据编号</param>
/// <param name="strMsg">操作返回的信息</param>
/// <returns></returns>
public static bool UnConfirmOrder(string OrderNO, out string strMsg)
{
string strSq = string.Empty;
bool isSuc = false;
strMsg = "";
int OrderId = GetOrderID(OrderNO);
//判断单据是否为执行状态，非执行状态不能取消确认
if (isHandle(OrderNO, "2"))
{
//判断单据是否被引用
if (IsRef(OrderNO))
{
int iEmployeeID = 0;//员工id
string strUserID = string.Empty;//用户id
string strCompanyCD = string.Empty;//单位编码
SqlParameter[] paras = new SqlParameter[5];
iEmployeeID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
strUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
strCompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
strSq = "update officedba.SellContract set BillStatus='1' ";
strSq += " , Confirmor=@Confirmor, ConfirmDate=@ConfirmDate, ModifiedDate=getdate() ,ModifiedUserID=@UserID ";
strSq += " WHERE ContractNo = @ContractNo and CompanyCD=@CompanyCD";
paras[0] = new SqlParameter("@Confirmor", DBNull.Value);
paras[1] = new SqlParameter("@UserID", strUserID);
paras[2] = new SqlParameter("@CompanyCD", strCompanyCD);
paras[3] = new SqlParameter("@ContractNo", OrderNO);
paras[4] = new SqlParameter("@ConfirmDate", DBNull.Value);
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
FlowDBHelper.OperateCancelConfirm(strCompanyCD, 5, 2, OrderId, strUserID, tran);//撤销审批
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSq, paras);
tran.Commit();
isSuc = true;
strMsg = "取消确认成功！";
}
catch (Exception ex)
{
tran.Rollback();
isSuc = false;
strMsg = "取消确认失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSuc = false;
strMsg = "该单据已被其他单据引用，不可取消确认！";
}
}
else
{
isSuc = false;
strMsg = "该单据已被其他用户取消确认，不可再次取消确认！";
}
return isSuc;
}
public static int Delete(string idlist)
{
int num = 0;
string sqlstr = "delete from officedba.UserProductInfo where id in (" + idlist + ")";
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
num = SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, sqlstr);
tran.Commit();
}
catch
{
tran.Rollback();
}
return num;
}
/// <summary>
/// 删除分项预算概要 及其项目摘要，项目预算表中与该“分项预算概要”相关的单据
/// </summary>
/// <param name="billID">删除的ID串</param>
/// <returns></returns>
public static int DeLSubBudgetInfo(string billID)
{
StringBuilder sqlstr = new StringBuilder();
TransactionManager tran = new TransactionManager();
int num = 0;
string[] billIDArr = billID.Split(',');
string str = "";
string budgetIDstr = "-1";
//获取要删除的项目摘要ID串
for (int i = 0; i < billIDArr.Length; i++)
{
str = "select budgetID from officedba.budgetsummary where subBudgetID=@ID ";
SqlParameter[] paramstr = {
new SqlParameter("@ID",billIDArr[i].ToString().Trim())
};
int budgetID = Convert.ToInt32(SqlHelper.ExecuteScalar(str.ToString(), paramstr));
budgetIDstr += ","+budgetID;
}
//删除与此分项预算概要相关的“项目摘要”及其“项目预算表信息”
sqlstr.AppendLine("delete from officedba.budgetSummary where subBudgetID in (" + billID + ")");
sqlstr.AppendLine("delete from officedba.ProjectBaseNum where SummaryID in (" + budgetIDstr + ")");
sqlstr.AppendLine("delete from officedba.ProjectBudgetDetails where SummaryID in (" + budgetIDstr + ")");
sqlstr.AppendLine("delete from officedba.SubBudget where ID in (" + billID + ")");
tran.BeginTransaction();
try
{
num = SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, sqlstr.ToString());
tran.Commit();
}
catch
{
tran.Rollback();
}
return num;
}
/// <summary>
/// 添加新单据
/// </summary>
/// <returns></returns>
public static bool Insert(FeesModel sellOrderModel, List<FeesDetailModel> sellOrderDetailModellList, out string strMsg,out int Id)
{
bool isSucc = false;//是否添加成功
strMsg = "";
Id = 0;
//判断单据编号是否存在
if (NoIsExist(sellOrderModel.FeesNo))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
InsertSellOrder(sellOrderModel, tran,out Id);
InsertSellOrderDetail(sellOrderDetailModellList, tran);
if (sellOrderModel.FeesType != "0")
{
//非无源单时，更改源单开票状态
updateSourceByFeesType(sellOrderModel.FeesType, sellOrderModel.SourceNo, tran);
}
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "该编号已被使用，请输入未使用的编号！";
}
return isSucc;
}
public static bool Del(string orderNos, out string strMsg, out string strFieldText)
{
string strCompanyCD = string.Empty;//单位编号
bool isSucc = false;
string allOrderNo = "";
strMsg = "";
strFieldText = "";
bool bTemp = false;//单据是否可以被删除
System.Text.StringBuilder sb = new System.Text.StringBuilder();
TransactionManager tran = new TransactionManager();
strCompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
string[] orderNoS = null;
orderNoS = orderNos.Split(',');
for (int i = 0; i < orderNoS.Length; i++)
{
if (!IsFlow(orderNoS[i]))
{
strFieldText += "单据：" + orderNoS[i] + "|";
strMsg += "已确认后的单据不允许删除！|";
bTemp = true;
}
orderNoS[i] = "'" + orderNoS[i] + "'";
sb.Append(orderNoS[i]);
}
allOrderNo = sb.ToString().Replace("''", "','");
if (!bTemp)
{
tran.BeginTransaction();
try
{
//源单改为“未开票”
UpdateSourceIsFeesAccount(tran.Trans, strCompanyCD, allOrderNo);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "DELETE FROM officedba.Fees WHERE FeesNo IN ( " + allOrderNo + " ) and CompanyCD='" + strCompanyCD + "'", null);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "DELETE FROM officedba.FeesDetail WHERE FeesNo IN ( " + allOrderNo + " ) and CompanyCD='" + strCompanyCD + "'", null);
tran.Commit();
isSucc = true;
strMsg = "删除成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "删除失败，请联系系统管理员！";
isSucc = false;
throw ex;
}
}
else
{
isSucc = false;
}
return isSucc;
}
/// <summary>
/// 删除回款计划
/// </summary>
/// <param name="strIDS"></param>
/// <returns></returns>
public static bool DelSellGathering(string strIDS, out string strMsg)
{
bool isSucc = false;//是否添加成功
string strSql = "delete from officedba.SellGathering where ID = @ID";
string[] strId = strIDS.Split(',');
strMsg = "";
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
for (int i = 0; i < strId.Length; i++)
{
SqlParameter[] para = { new SqlParameter("@ID", Convert.ToInt32(strId[i])) };
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql, para);
}
tran.Commit();
isSucc = true;
strMsg = "删除成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "删除失败，请联系系统管理员！";
isSucc = false;
throw ex;
}
return isSucc;
}
public static int DelReportList(string idlist)
{
//删除报表设置
string sqlstr = "delete from defdba.ReportTable where ID in ( "+idlist+" )";
//删除菜单
string[] arr = idlist.Split(',');
for (int i = 0; i < arr.Length; i++)
{
sqlstr += ";" + "delete from defdba.CustomModule where PropertyValue='Pages/Office/DefManager/ReportTableList.aspx?reportid=" + arr[i] + "'";
}
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
int num = SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, sqlstr);
tran.Commit();
return num;
}
catch
{
tran.Rollback();
return 0;
}
}
/// <summary>
/// 删除单据
/// </summary>
/// <param name="orderNos"></param>
/// <returns></returns>
public static bool DelOrder(string orderNos)
{
string strCompanyCD = string.Empty;//单位编号
bool isSucc = false;
string allOrderNo = "";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
TransactionManager tran = new TransactionManager();
strCompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
string[] orderNoS = null;
orderNoS = orderNos.Split(',');
for (int i = 0; i < orderNoS.Length; i++)
{
orderNoS[i] = "'" + orderNoS[i] + "'";
sb.Append(orderNoS[i]);
}
allOrderNo = sb.ToString().Replace("''", "','");
tran.BeginTransaction();
try
{
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "DELETE FROM officedba.AdversaryInfo WHERE CustNo IN ( " + allOrderNo + " ) and CompanyCD='" + strCompanyCD + "'", null);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "DELETE FROM officedba.AdversaryDynamic WHERE CustNo IN ( " + allOrderNo + " ) and CompanyCD='" + strCompanyCD + "'", null);
tran.Commit();
isSucc = true;
}
catch (Exception ex)
{
tran.Rollback();
isSucc = false;
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;
}
/// <summary>
/// 跟新单据单据
/// </summary>
/// <param name="sellChanceModel">销售机会表实体</param>
/// <param name="sellChancePushModel">销售阶段表实体</param>
/// <returns>是否添加成功</returns>
public static bool UpdateOrder(AdversaryInfoModel adversaryInfoModel, List<AdversaryDynamicModel> adversaryDynamicModelList)
{
bool isSucc = false;//是否添加成功
string strSql = "delete from officedba.AdversaryDynamic where CustNo=@CustNo and CompanyCD=@CompanyCD";
SqlParameter[] paras = { new SqlParameter("@CustNo", adversaryInfoModel.CustNo), new SqlParameter("@CompanyCD", adversaryInfoModel.CompanyCD) };
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
UpdateAdversaryInfo( adversaryInfoModel, tran);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras);
InsertAdversaryDynamic(adversaryDynamicModelList, tran);
tran.Commit();
isSucc = true;
}
catch(Exception ex)
{
tran.Rollback();
isSucc = false;
throw ex;
}
return isSucc;
}
/// <summary>
/// 删除自定义表单
/// </summary>
/// <param name="id">数据ID</param>
/// <param name="tableName">物理表名</param>
/// <param name="CompanyCD">机构代码</param>
public static void DelTable(string id, string tableName, string CompanyCD)
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
if (!string.IsNullOrEmpty(tableName))
{// 删除物理表
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "DROP TABLE " + tableName, null);
}
// 删除表
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "delete from defdba.CustomTable where ID =" + id, null);
// 删除表结构
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "delete from defdba.StructTable where TableID =" + id, null);
// 删除模板表
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "delete from defdba.ModuleTable where TableID =" + id, null);
// 删除菜单
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text
, string.Format("DELETE FROM defdba.CustomModule WHERE PropertyValue ='Pages/Office/DefManager/DefTableList.aspx?tableid={0}' AND CompanyCD='{1}' ", id, CompanyCD), null);
tran.Commit();
}
catch
{
tran.Rollback();
}
}
/// <summary>
/// 取消结单
/// </summary>
/// <param name="OrderNO">单据编号</param>
/// <param name="strMsg">操作返回的信息</param>
/// <returns></returns>
public static bool UnCloseOrder(string OrderNO, out string strMsg)
{
string strSq = string.Empty;
bool isSuc = false;
strMsg = "";
//判断单据是否为手工结单状态，非手工结单状态不能结单
if (isHandle(OrderNO, "4"))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
SqlParameter[] paras = new SqlParameter[5];
strSq = "update officedba.SellContract set BillStatus='2' ,State='1' ";
strSq += " , Closer=@EmployeeID, CloseDate=@CloseDate, ModifiedDate=getdate() ,ModifiedUserID=@UserID ";
paras[0] = new SqlParameter("@EmployeeID", DBNull.Value);
paras[1] = new SqlParameter("@UserID", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID);
paras[2] = new SqlParameter("@CompanyCD", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD);
paras[3] = new SqlParameter("@ContractNo", OrderNO);
paras[4] = new SqlParameter("@CloseDate", DBNull.Value);
strSq += " WHERE ContractNo = @ContractNo and CompanyCD=@CompanyCD";
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSq, paras);
tran.Commit();
isSuc = true;
strMsg = "取消结单成功！";
}
catch (Exception ex)
{
tran.Rollback();
isSuc = false;
strMsg = "取消结单失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSuc = false;
strMsg = "该单据已被其他用户取消结单，不可再次取消结单！";
}
return isSuc;
}
/// <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;
}
}
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="TableID"></param>
public static void InitTable(string TableID, string CompanyCD)
{
//获取主表
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(" select * from defdba.CustomTable where ID=@ID ");
SqlParameter[] parameters = {
new SqlParameter("@ID", SqlDbType.Int,4)};
parameters[0].Value = Convert.ToInt32(TableID);
DataTable MainDT = SqlHelper.ExecuteSql(sqlStr.ToString(), parameters);
//获取明细表
StringBuilder sqlStr1 = new StringBuilder();
sqlStr1.Append(" select * from defdba.StructTable where TableID=" + TableID);
DataTable DetailDT = SqlHelper.ExecuteSql(sqlStr1.ToString());
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
#region 创建表Sql
//创建表Sql
StringBuilder sqlCreate = new StringBuilder();
if (MainDT != null && MainDT.Rows.Count > 0)
{
if (DetailDT != null && DetailDT.Rows.Count > 0)
{
string IsKeyWord = "";
sqlCreate.Append(" create table define.");
sqlCreate.Append(CompanyCD + "_" + MainDT.Rows[0]["CustomTableName"].ToString());
sqlCreate.Append("(");
DataRow[] dr = DetailDT.Select(" IsKeyword=1 ");
//if (dr.Length == 0)
//{
sqlCreate.Append(" [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, ");
//IsKeyWord = " [ID] asc ";
//}
sqlCreate.Append("[pid] [int] NULL, ");
for (int x = 0; x < DetailDT.Rows.Count; x++)
{
string isEmpty = " NULL";
if (DetailDT.Rows[x]["isempty"].ToString() == "0")
{
isEmpty = " NOT NULL ";
}
string isDH = ",";
if (x == DetailDT.Rows.Count - 1 && DetailDT.Rows[x]["IsKeyword"].ToString() == "0" && IsKeyWord == "")
{
isDH = " ";
}
if (DetailDT.Rows[x]["IsKeyword"].ToString() == "1")
{
IsKeyWord = " [" + DetailDT.Rows[x]["ccode"].ToString() + "] asc ";
}
//字符类型
if (DetailDT.Rows[x]["type"].ToString() == "varchar")
{
sqlCreate.Append(" [" + DetailDT.Rows[x]["ccode"].ToString() + "] [varchar](" + DetailDT.Rows[x]["length"].ToString() + ") COLLATE Chinese_PRC_CI_AS " + isEmpty + isDH);
}
//整形
if (DetailDT.Rows[x]["type"].ToString() == "int")
{
sqlCreate.Append(" [" + DetailDT.Rows[x]["ccode"].ToString() + "] [int] " + isEmpty + isDH);
}
//时间类型
if (DetailDT.Rows[x]["type"].ToString() == "datetime")
{
sqlCreate.Append(" [" + DetailDT.Rows[x]["ccode"].ToString() + "] [datetime] " + isEmpty + isDH);
}
//浮点类型
if (DetailDT.Rows[x]["type"].ToString() == "decimal")
{
sqlCreate.Append(" [" + DetailDT.Rows[x]["ccode"].ToString() + "] [decimal](18, 2) " + isEmpty + isDH);
}
}
if (!string.IsNullOrEmpty(IsKeyWord))
{
sqlCreate.Append(" CONSTRAINT [pk_" + CompanyCD + "companyInfo" + TableID.ToString() + "] PRIMARY KEY CLUSTERED ");
sqlCreate.Append("( ");
sqlCreate.Append(IsKeyWord);
sqlCreate.Append("");
sqlCreate.Append(" )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]");
}
sqlCreate.Append(") ON [PRIMARY]");
}
}
#endregion
SqlHelper.ExecuteScalar(sqlCreate.ToString(), null);
SqlHelper.ExecuteScalar(" update defdba.CustomTable set status=2 where ID=" + TableID, null);
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
