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