/// <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> /// <param name="sellChanceModel">销售机会表实体</param> /// <param name="sellChancePushModel">销售阶段表实体</param> /// <returns>是否添加成功</returns> public static bool UpdateSellChance(Hashtable ht,SellChanceModel sellChanceModel, SellChancePushModel sellChancePushModel) { bool isSucc = false;//是否添加成功 //定义变量 string remark = string.Empty; try { isSucc = SellChanceDBHelper.UpdateSellChance(ht,sellChanceModel, sellChancePushModel); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } catch (Exception ex) { //输出日志 SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, ConstUtil.MODULE_ID_SELLCHANCE_ADD); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } SellLogCommon.InsertLog(sellChanceModel.ChanceNo, ConstUtil.MODULE_ID_SELLCHANCE_ADD, ConstUtil.CODING_RULE_TABLE_SELLCHANCE, remark, ConstUtil.LOG_PROCESS_UPDATE); return isSucc; }
protected void LoadPrintInfo() { PrintParameterSettingModel model = new PrintParameterSettingModel(); model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; model.BillTypeFlag = int.Parse(ConstUtil.BILL_TYPEFLAG_SALE); model.PrintTypeFlag = ConstUtil.PRINTBILL_SELLCHANCE; SellChanceModel modelMRP = new SellChanceModel(); modelMRP.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; modelMRP.ChanceNo = this.ChanceNo; /*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/ string[,] aBase = { { "{ExtField1}", "ExtField1"}, { "{ExtField2}", "ExtField2"}, { "{ExtField3}", "ExtField3"}, { "{ExtField4}", "ExtField4"}, { "{ExtField5}", "ExtField5"}, { "{ExtField6}", "ExtField6"}, { "{ExtField7}", "ExtField7"}, { "{ExtField8}", "ExtField8"}, { "{ExtField9}", "ExtField9"}, { "{ExtField10}", "ExtField10"}, { "机会编号", "ChanceNo"}, { "机会主题", "Title"}, { "机会类型", "ChanceTypeName" }, { "客户名称", "CustName" }, { "客户电话", "CustTel"}, { "客户类型", "CustTypeName"}, { "机会来源", "HapSourceName"}, { "发现日期", "FindDate"}, { "业务员", "SellerName"}, { "部门", "DeptName"}, { "提供人", "ProvideMan"}, { "需求描述", "Requires"}, { "可查看该机会人员", "CanViewUserName"}, { "提醒时间", "RemindTime"}, { "提醒手机号", "RemindMTel"}, { "接收人", "ReceiverName"}, { "提醒内容", "RemindContent"}, { "预期金额", "IntendMoney"}, { "预期签单日", "IntendDate"}, { "制单人", "CreatorName"}, { "制单日期", "CreateDate"}, { "最后更新人", "ModifiedUserID"}, { "最后更新日期", "ModifiedDate"}, { "是否被报价", "IsQuotedName"}, { "备注", "Remark"}, }; string[,] aDetail = { { "阶段", "PhaseName"}, { "日期", "PushDate"}, { "业务员", "EmployeeName"}, { "状态", "StateName" }, { "可能性", "TypeName" }, { "阶段备注", "Remark"}, }; #region 1.扩展属性 int countExt = 0; DataTable dtExtTable = TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.SellChance"); if (dtExtTable.Rows.Count > 0) { for (int i = 0; i < dtExtTable.Rows.Count; i++) { for (int x = 0; x < (aBase.Length / 2) - 15; x++) { if (x == i) { aBase[x, 0] = dtExtTable.Rows[i]["EFDesc"].ToString(); countExt++; } } } } #endregion DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model); DataTable dtMRP = SellChanceBus.GetRepOrder(ChanceNo);// SellPlanBus.GetRepOrder(PlanNo); DataTable dtDetail = SellChanceBus.GetRepOrderDetail(ChanceNo); //SellPlanBus.GetOrderDetail(PlanNo); string strBaseFields = ""; string strDetailFields = ""; if (dbPrint.Rows.Count > 0) { isSeted.Value = "1"; strBaseFields = dbPrint.Rows[0]["BaseFields"].ToString(); strDetailFields = dbPrint.Rows[0]["DetailFields"].ToString(); } else { isSeted.Value = "0"; strBaseFields = "ChanceNo|Title|ChanceTypeName|CustName|CustTel|CustTypeName|HapSourceName|FindDate|SellerName|DeptName|ProvideMan|Requires|CanViewUserName|RemindTime|RemindMTel|ReceiverName|RemindContent|IntendMoney|IntendDate|CreatorName|CreateDate|ModifiedUserID|ModifiedDate|IsQuotedName|Remark"; /*基本信息字段+扩展信息字段*/ if (countExt > 0) { for (int i = 0; i < countExt; i++) { strBaseFields = strBaseFields + "|" + "ExtField" + (i + 1); } } strDetailFields = "PhaseName|PushDate|EmployeeName|StateName|TypeName|Remark"; } #region 主表信息 if (!string.IsNullOrEmpty(strBaseFields)) { tableBase.InnerHtml = WritePrintPageTable("销售机会", strBaseFields, strDetailFields, aBase, aDetail, dtMRP, dtDetail, true); } #endregion #region 明细信息 if (!string.IsNullOrEmpty(strDetailFields)) { tableDetail.InnerHtml = WritePrintPageTable("销售机会", strBaseFields, strDetailFields, aBase, aDetail, dtMRP, dtDetail, false); } #endregion }
/// <summary> /// 为机会表插入插入数据 /// </summary> /// <param name="tran"></param> /// <param name="sellChanceModel"></param> private static int InsertChanece(TransactionManager tran, SellChanceModel sellChanceModel) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into officedba.SellChance("); strSql.Append("CompanyCD,CustID,CustType,CustTel,ChanceNo,Title,ChanceType,HapSource,Seller,SellDeptId,FindDate,ProvideMan,Requires,IntendDate,IntendMoney,Remark,IsQuoted,CanViewUser,CanViewUserName,Creator,CreateDate,ModifiedDate,ModifiedUserID,IsMobileNotice,RemindTime,RemindMTel,RemindContent,ReceiverID)"); strSql.Append(" values ("); strSql.Append("@CompanyCD,@CustID,@CustType,@CustTel,@ChanceNo,@Title,@ChanceType,@HapSource,@Seller,@SellDeptId,@FindDate,@ProvideMan,@Requires,@IntendDate,@IntendMoney,@Remark,@IsQuoted,@CanViewUser,@CanViewUserName,@Creator,getdate(),getdate(),@ModifiedUserID,@IsMobileNotice,@RemindTime,@RemindMTel,@RemindContent,@ReceiverID)"); strSql.Append(" ;select @@IDENTITY "); #region SqlParameter[] parameters = { new SqlParameter("@CompanyCD", SqlDbType.VarChar,8), new SqlParameter("@CustID", SqlDbType.Int,4), new SqlParameter("@CustType", SqlDbType.Int,4), new SqlParameter("@CustTel", SqlDbType.VarChar,100), new SqlParameter("@ChanceNo", SqlDbType.VarChar,50), new SqlParameter("@Title", SqlDbType.VarChar,100), new SqlParameter("@ChanceType", SqlDbType.Int,4), new SqlParameter("@HapSource", SqlDbType.Int,4), new SqlParameter("@Seller", SqlDbType.Int,4), new SqlParameter("@SellDeptId", SqlDbType.Int,4), new SqlParameter("@FindDate", SqlDbType.DateTime), new SqlParameter("@ProvideMan", SqlDbType.VarChar,50), new SqlParameter("@Requires", SqlDbType.VarChar,800), new SqlParameter("@IntendDate", SqlDbType.DateTime), new SqlParameter("@IntendMoney", SqlDbType.Decimal,9), new SqlParameter("@Remark", SqlDbType.VarChar,1024), new SqlParameter("@IsQuoted", SqlDbType.Char,1), new SqlParameter("@Creator", SqlDbType.Int,4), new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,20), new SqlParameter("@CanViewUser", SqlDbType.VarChar,2048), new SqlParameter("@CanViewUserName", SqlDbType.VarChar,2048), new SqlParameter("@IsMobileNotice", SqlDbType.Char,1), new SqlParameter("@RemindTime", SqlDbType.DateTime), new SqlParameter("@RemindMTel", SqlDbType.VarChar,15), new SqlParameter("@RemindContent", SqlDbType.VarChar,500), new SqlParameter("@ReceiverID", SqlDbType.Int,4) }; parameters[0].Value = sellChanceModel.CompanyCD; parameters[1].Value = sellChanceModel.CustID; parameters[2].Value = sellChanceModel.CustType; parameters[3].Value = sellChanceModel.CustTel; parameters[4].Value = sellChanceModel.ChanceNo; parameters[5].Value = sellChanceModel.Title; parameters[6].Value = sellChanceModel.ChanceType; parameters[7].Value = sellChanceModel.HapSource; parameters[8].Value = sellChanceModel.Seller; parameters[9].Value = sellChanceModel.SellDeptId; parameters[10].Value = sellChanceModel.FindDate; parameters[11].Value = sellChanceModel.ProvideMan; parameters[12].Value = sellChanceModel.Requires; parameters[13].Value = sellChanceModel.IntendDate; parameters[14].Value = sellChanceModel.IntendMoney; parameters[15].Value = sellChanceModel.Remark; parameters[16].Value = sellChanceModel.IsQuoted; parameters[17].Value = sellChanceModel.Creator; parameters[18].Value = sellChanceModel.ModifiedUserID; parameters[19].Value = sellChanceModel.CanViewUser; parameters[20].Value = sellChanceModel.CanViewUserName; parameters[21].Value = sellChanceModel.IsMobileNotice; parameters[22].Value = sellChanceModel.RemindTime; parameters[23].Value = sellChanceModel.RemindMTel; parameters[24].Value = sellChanceModel.RemindContent; parameters[25].Value = sellChanceModel.ReceiverID; foreach (SqlParameter para in parameters) { if (para.Value == null) { para.Value = DBNull.Value; } } #endregion int billID = Convert.ToInt32(SqlHelper.ExecuteScalar(strSql.ToString(), parameters)); return billID; }
/// <summary> /// 修改销售机会及阶段 /// </summary> /// <param name="sellChanceModel">销售机会表实体</param> /// <param name="sellChancePushModel">销售阶段表实体</param> /// <returns>是否添加成功</returns> public static bool UpdateSellChance(Hashtable ht,SellChanceModel sellChanceModel, SellChancePushModel sellChancePushModel) { bool isSucc = false;//是否添加成功 string strSql = "delete from officedba.SellChancePush where ChanceNo=@ChanceNo and CompanyCD=@CompanyCD and Phase=@Phase"; SqlParameter[] paras = { new SqlParameter("@ChanceNo", sellChancePushModel.ChanceNo), new SqlParameter("@CompanyCD", sellChancePushModel.CompanyCD), new SqlParameter("@Phase",sellChancePushModel.Phase) }; TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { UpdateChanece(tran, sellChanceModel); //拓展属性 GetExtAttrCmd(sellChanceModel, ht, tran); //若是设置了手机提醒 则插入以下信息 if ( sellChanceModel.IsMobileNotice == "1") { //若有未发送的记录则更新该未发送的记录,否则则插入新记录 if (IsExistedNoSendMsg(sellChanceModel.ID.ToString(), "0",sellChanceModel.CompanyCD)) { StringBuilder strSqlMb = new StringBuilder(); //strSqlMb.Append("delete from officedba.NoticeHistory where SourceID=@SourceID and SourceFlag =@SourceFlag "); // strSqlMb.Append("insert into officedba.NoticeHistory("); //strSqlMb.Append(" CompanyCD,SourceFlag,SourceID,PlanNoticeDate ) "); //strSqlMb.Append(" values(@CompanyCD, @SourceFlag,@SourceID,@PlanNoticeDate ) "); strSqlMb.AppendLine(" update officedba.NoticeHistory set PlanNoticeDate=@PlanNoticeDate "); strSqlMb.AppendLine(" where CompanyCD=@CompanyCD and SourceID=@SourceID and SourceFlag=@SourceFlag and RealNoticeDate is null"); SqlParameter[] param = { new SqlParameter("@CompanyCD",sellChanceModel.CompanyCD), new SqlParameter("@SourceFlag","4"), new SqlParameter("@SourceID",sellChanceModel.ID), new SqlParameter("@PlanNoticeDate",sellChanceModel.RemindTime) }; SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSqlMb.ToString(), param); } else { StringBuilder strSqlMb = new StringBuilder(); strSqlMb.Append("insert into officedba.NoticeHistory("); strSqlMb.Append(" CompanyCD,SourceFlag,SourceID,PlanNoticeDate ) "); strSqlMb.Append(" values(@CompanyCD, @SourceFlag,@SourceID,@PlanNoticeDate ) "); SqlParameter[] param = { new SqlParameter("@CompanyCD",sellChanceModel.CompanyCD), new SqlParameter("@SourceFlag","4"), new SqlParameter("@SourceID",sellChanceModel.ID), new SqlParameter("@PlanNoticeDate",sellChanceModel.RemindTime) }; SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSqlMb.ToString(), param); } } else { //若有未发送的记录则删除 if (IsExistedNoSendMsg(sellChanceModel.ID.ToString(), "0",sellChanceModel.CompanyCD)) { StringBuilder strSqlMb = new StringBuilder(); strSqlMb.Append("delete from officedba.NoticeHistory where SourceID=@SourceID and SourceFlag =@SourceFlag and CompanyCD=@CompanyCD "); SqlParameter[] param = { new SqlParameter("@CompanyCD",sellChanceModel.CompanyCD), new SqlParameter("@SourceFlag","4"), new SqlParameter("@SourceID",sellChanceModel.ID) }; SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSqlMb.ToString(), param); } } SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras); InsertPush(tran, sellChancePushModel); tran.Commit(); isSucc = true; } catch (Exception ex) { tran.Rollback(); throw ex; } return isSucc; }
/// <summary> /// 扩展属性保存操作 /// </summary> /// <returns></returns> private static void GetExtAttrCmd(SellChanceModel model, Hashtable htExtAttr, TransactionManager tran) { try { string strSql = string.Empty; strSql = "UPDATE officedba.SellChance set "; SqlParameter[] parameters = new SqlParameter[htExtAttr.Count + 2]; int i = 0; foreach (DictionaryEntry de in htExtAttr)// de为一个Hashtable实例 { strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ","; parameters[i] = SqlHelper.GetParameter("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim()); i++; } int iLength = strSql.Length - 1; strSql = strSql.Substring(0, iLength); strSql += " where CompanyCD = @CompanyCD AND ChanceNo = @ChanceNo"; parameters[i] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD); parameters[i + 1] = SqlHelper.GetParameter("@ChanceNo", model.ChanceNo); //cmd.Parameters.Add("@CompanyCD", model.CompanyCD); //cmd.Parameters.Add("@PlanNo", model.PlanNo); SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parameters); //cmd.CommandText = strSql; } catch (Exception ex) { string ss = ex.Message; } }
/// <summary> /// 为机会表跟新数据 /// </summary> /// <param name="tran"></param> /// <param name="sellChanceModel"></param> private static void UpdateChanece(TransactionManager tran, SellChanceModel sellChanceModel) { StringBuilder strSql = new StringBuilder(); strSql.Append("update officedba.SellChance set "); strSql.Append("CustID=@CustID,"); strSql.Append("CustType=@CustType,"); strSql.Append("CustTel=@CustTel,"); strSql.Append("Title=@Title,"); strSql.Append("ChanceType=@ChanceType,"); strSql.Append("HapSource=@HapSource,"); strSql.Append("Seller=@Seller,"); strSql.Append("SellDeptId=@SellDeptId,"); strSql.Append("FindDate=@FindDate,"); strSql.Append("ProvideMan=@ProvideMan,"); strSql.Append("Requires=@Requires,"); strSql.Append("IntendDate=@IntendDate,"); strSql.Append("IntendMoney=@IntendMoney,"); strSql.Append("Remark=@Remark,"); strSql.Append("CanViewUser=@CanViewUser,"); strSql.Append("CanViewUserName=@CanViewUserName,"); strSql.Append("ModifiedDate=getdate(),"); strSql.Append("ModifiedUserID=@ModifiedUserID"); strSql.Append(",IsMobileNotice=@IsMobileNotice,RemindTime=@RemindTime,"); strSql.Append("RemindMTel=@RemindMTel,RemindContent=@RemindContent, "); strSql.Append("ReceiverID=@ReceiverID "); strSql.Append(" where CompanyCD=@CompanyCD and ChanceNo=@ChanceNo "); #region SqlParameter[] parameters = { new SqlParameter("@CompanyCD", SqlDbType.VarChar,8), new SqlParameter("@CustID", SqlDbType.Int,4), new SqlParameter("@CustType", SqlDbType.Int,4), new SqlParameter("@CustTel", SqlDbType.VarChar,100), new SqlParameter("@ChanceNo", SqlDbType.VarChar,50), new SqlParameter("@Title", SqlDbType.VarChar,100), new SqlParameter("@ChanceType", SqlDbType.Int,4), new SqlParameter("@HapSource", SqlDbType.Int,4), new SqlParameter("@Seller", SqlDbType.Int,4), new SqlParameter("@SellDeptId", SqlDbType.Int,4), new SqlParameter("@FindDate", SqlDbType.DateTime), new SqlParameter("@ProvideMan", SqlDbType.VarChar,50), new SqlParameter("@Requires", SqlDbType.VarChar,800), new SqlParameter("@IntendDate", SqlDbType.DateTime), new SqlParameter("@IntendMoney", SqlDbType.Decimal,9), new SqlParameter("@Remark", SqlDbType.VarChar,1024), new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,20), new SqlParameter("@CanViewUser", SqlDbType.VarChar,2048), new SqlParameter("@CanViewUserName", SqlDbType.VarChar,2048), new SqlParameter("@IsMobileNotice", SqlDbType.Char,1), new SqlParameter("@RemindTime", SqlDbType.DateTime), new SqlParameter("@RemindMTel", SqlDbType.VarChar,15), new SqlParameter("@RemindContent", SqlDbType.VarChar,500) , new SqlParameter("@ReceiverID", SqlDbType.Int,4) }; parameters[0].Value = sellChanceModel.CompanyCD; parameters[1].Value = sellChanceModel.CustID; parameters[2].Value = sellChanceModel.CustType; parameters[3].Value = sellChanceModel.CustTel; parameters[4].Value = sellChanceModel.ChanceNo; parameters[5].Value = sellChanceModel.Title; parameters[6].Value = sellChanceModel.ChanceType; parameters[7].Value = sellChanceModel.HapSource; parameters[8].Value = sellChanceModel.Seller; parameters[9].Value = sellChanceModel.SellDeptId; parameters[10].Value = sellChanceModel.FindDate; parameters[11].Value = sellChanceModel.ProvideMan; parameters[12].Value = sellChanceModel.Requires; parameters[13].Value = sellChanceModel.IntendDate; parameters[14].Value = sellChanceModel.IntendMoney; parameters[15].Value = sellChanceModel.Remark; parameters[16].Value = sellChanceModel.ModifiedUserID; parameters[17].Value = sellChanceModel.CanViewUser; parameters[18].Value = sellChanceModel.CanViewUserName; parameters[19].Value = sellChanceModel.IsMobileNotice; parameters[20].Value = sellChanceModel.RemindTime; parameters[21].Value = sellChanceModel.RemindMTel; parameters[22].Value = sellChanceModel.RemindContent; parameters[23].Value = sellChanceModel.ReceiverID; foreach (SqlParameter para in parameters) { if (para.Value == null) { para.Value = DBNull.Value; } } #endregion SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parameters); }