/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(CustInfoModel model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.CustInfo set ";
foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例
{
strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ",";
cmd.Parameters.AddWithValue("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim());
}
int iLength = strSql.Length - 1;
strSql = strSql.Substring(0, iLength);
strSql += " where CompanyCD = @CompanyCD AND CustNo = @CustNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
cmd.Parameters.AddWithValue("@CustNo", model.CustNo);
cmd.CommandText = strSql;
}
catch
{ }
}
public static DataTable GetCust_daByCon(CustInfoModel CustInfoM, string DateBegin, string DateEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return CustCallDBHelper.GetCust_daByCon(CustInfoM, DateBegin, DateEnd, pageIndex, pageCount, ord, ref TotalCount);
}
/// <summary>
/// 客户一览表查询打印
/// </summary>
/// <param name="CustModel"></param>
/// <param name="ord"></param>
/// <returns></returns>
public static DataTable GetCustListByConditionPrint(CustInfoModel CustModel, string ord)
{
return CustInfoDBHelper.GetCustListByConditionPrint(CustModel, ord);
}
/// <summary>
/// 根据创建人获取客户ID、编号、简称的方法
/// </summary>
/// <param name="Creator">创建人</param>
/// <param name="CompanyCD">公司代码</param>
/// <returns>客户列表结果集</returns>
public static DataTable GetCustName(CustInfoModel CustModel,string CanUserID, string CompanyCD)
{
try
{
return CustInfoDBHelper.GetCustName(CustModel,CanUserID, CompanyCD);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 导出客户列表信息
/// </summary>
/// <param name="CustModel"></param>
/// <param name="Manager"></param>
/// <param name="CanUserID"></param>
/// <param name="CreatedBegin"></param>
/// <param name="CreatedEnd"></param>
/// <param name="ord"></param>
/// <returns></returns>
public static DataTable ExportCustInfo(CustInfoModel CustModel, string Manager, string CanUserID, string CreatedBegin, string CreatedEnd, string ord)
{
return CustInfoDBHelper.ExportCustInfo(CustModel, Manager, CanUserID, CreatedBegin, CreatedEnd, ord);
}
protected void LoadPrintInfo()
{
PrintParameterSettingModel model = new PrintParameterSettingModel();
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.BillTypeFlag = int.Parse(ConstUtil.BILL_TYPEFLAG_CUSTOMER);
model.PrintTypeFlag = ConstUtil.PRINTBILL_CUSTINFOLINK;
CustInfoModel modelMRP = new CustInfoModel();
modelMRP.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
modelMRP.CustNo = this.CustNo;
/*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/
string[,] aBase = {
{ "{ExtField1}", "ExtField1"},
{ "{ExtField2}", "ExtField2"},
{ "{ExtField3}", "ExtField3"},
{ "{ExtField4}", "ExtField4"},
{ "{ExtField5}", "ExtField5"},
{ "{ExtField6}", "ExtField6"},
{ "{ExtField7}", "ExtField7"},
{ "{ExtField8}", "ExtField8"},
{ "{ExtField9}", "ExtField9"},
{ "{ExtField10}", "ExtField10"},
{ "{ExtField11}", "ExtField11"},
{ "{ExtField12}", "ExtField12"},
{ "{ExtField13}", "ExtField13"},
{ "{ExtField14}", "ExtField14"},
{ "{ExtField15}", "ExtField15"},
{ "{ExtField16}", "ExtField16"},
{ "{ExtField17}", "ExtField17"},
{ "{ExtField18}", "ExtField18"},
{ "{ExtField19}", "ExtField19"},
{ "{ExtField20}", "ExtField20"},
{ "{ExtField21}", "ExtField21"},
{ "{ExtField22}", "ExtField22"},
{ "{ExtField23}", "ExtField23"},
{ "{ExtField24}", "ExtField24"},
{ "{ExtField25}", "ExtField25"},
{ "{ExtField26}", "ExtField26"},
{ "{ExtField27}", "ExtField27"},
{ "{ExtField28}", "ExtField28"},
{ "{ExtField29}", "ExtField29"},
{ "{ExtField30}", "ExtField30"},
{ "客户编号", "CustNo"},
{ "客户名称", "CustName"},
{ "客户大类", "BigType" },
{ "拼音缩写", "CustShort"},
{ "客户管理分类", "CustTypeManage"},
{ "客户营销分类", "CustTypeSell"},
{ "客户优质级别", "CreditGradeNm"},
{ "客户时间分类", "CustTypeTime"},
{ "客户细分", "CustClassName"},
{ "客户类别", "CustTypaNm"},
{ "建档人", "CreatorName"},
{ "建档日期", "CreatedDate"},
{ "客户简介", "CustNote"},
{ "国家地区", "CountryName"},
{ "区域", "AreaName"},
{ "业务类型", "BusiType"},
{ "分管业务员 ", "ManagerName"},
{ "联络期限(天)", "LinkCycleNm"},
{ "收货地址", "ReceiveAddress"},
{ "允许延期付款", "CreditManage"},
{ "帐期天数(天)", "MaxCreditDate"},
{ "结算方式", "PayTypeNm"},
{ "关系等级", "RelaGrade"},
{ "启用状态", "UsedStatus"},
{ "最后更新用户", "ModifiedUserID"},
{ "最后更新日期", "ModifiedDate"},
{ "可查看该客户档案的人员", "CanViewUserName"},
{ "卡号", "CustNum"},
{ "性别", "Sex"},
{ "联系人类型", "LinkTypeName"},
{ "身份证号", "PaperNum"},
{ "生日", "Birthday"},
{ "电话", "WorkTel"},
{ "手机", "Handset"},
{ "传真", "Fax"},
{ "职务", "Position"},
{ "年龄", "Age"},
{ "邮编", "Post"},
{ "Eamil", "MailAddress"},
{ "籍贯", "HomeTown"},
{ "民族", "NationalName"},
{ "所受教育", "CultureLevelName"},
{ "所学专业", "ProfessionalName"},
{ "年收入情况", "IncomeYear"},
{ "饮食偏好", "FuoodDrink"},
{ "喜欢的音乐", "LoveMusic"},
{ "喜欢的颜色", "LoveColor"},
{ "喜欢的香烟", "LoveSmoke"},
{ "爱喝的酒", "LoveDrink"},
{ "爱喝的茶", "LoveTea"},
{ "喜欢的书籍", "LoveBook"},
{ "喜欢的运动", "LoveSport"},
{ "喜欢的品牌服饰", "LoveClothes"},
{ "喜欢的品牌化妆品", "Cosmetic"},
{ "性格描述", "Nature"},
{ "外表描述", "Appearance"},
{ "健康状况", "AdoutBody"},
{ "家人情况", "AboutFamily"},
{ "开什么车", "Car"},
};
string[,] aDetail = { { "", "" } };
#region 1.扩展属性
int countExt = 0;
DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.CustInfo");
if (dtExtTable.Rows.Count > 0)
{
for (int i = 0; i < dtExtTable.Rows.Count; i++)
{
for (int x = 0; x < (aBase.Length / 2) - 59; x++)
{
if (x == i)
{
aBase[x, 0] = dtExtTable.Rows[i]["EFDesc"].ToString();
countExt++;
}
}
}
}
#endregion
DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model);
//DataTable dtMRP = SellContractBus.GetRepOrder(OfferNo);
DataTable dtMRP = CustInfoBus.GetCustInfoByNo(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, CustBig, CustNo);
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 = "CustNo|CustName|BigType|CustShort|CustTypeManage|CustTypeSell|CreditGradeNm|CustTypeTime|CustClassName|CustTypaNm|CreatorName|" +
"CreatedDate|CustNote|CountryName|AreaName|BusiType|ManagerName|LinkCycleNm|ReceiveAddress|CreditManage|MaxCreditDate|PayTypeNm|RelaGrade|" +
"UsedStatus|ModifiedUserID|ModifiedDate|CanViewUserName|CustNum|Sex|LinkTypeName|PaperNum|Birthday|WorkTel|Handset|Fax|Position|Age|Post|" +
"MailAddress|HomeTown|NationalName|CultureLevelName|ProfessionalName|IncomeYear|FuoodDrink|LoveMusic|LoveColor|LoveSmoke|LoveDrink|LoveTea|" +
"LoveBook|LoveSport|LoveClothes|Cosmetic|Nature|Appearance|AdoutBody|AboutFamily|Car";
/*基本信息字段+扩展信息字段*/
if (countExt > 0)
{
for (int i = 0; i < countExt; i++)
{
strBaseFields = strBaseFields + "|" + "ExtField" + (i + 1);
}
}
strDetailFields = "";
}
#region 主表信息
if (!string.IsNullOrEmpty(strBaseFields))
{
tableBase.InnerHtml = WritePrintPageTable("客户档案", strBaseFields, strDetailFields, aBase, aDetail, dtMRP, null, true);
}
#endregion
}
public static DataTable GetCust_jyByCon(CustInfoModel CustInfoM, string DateBegin, string DateEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.ID,a.AdviceNo,a.Title,b.CustName,a.ModifiedDate, ");
strSql.Append(" c.LinkManName,d.EmployeeName,(case a.Accept when '1' then '暂不考虑' ");
strSql.Append(" when '2' then '一般' when '3' then '争取改进' else '一定做到' end) Accept, ");
strSql.Append(" CONVERT(varchar(100), a.AdviceDate, 120) AdviceDate ");
strSql.Append(" from officedba.CustAdvice a ");
strSql.Append(" left join officedba.CustInfo b on a.custId = b.id ");
strSql.Append(" left join officedba.CustLinkMan c on c.id = a.CustLinkMan ");
strSql.Append(" left join officedba.EmployeeInfo d on d.id = a.DestClerk ");
strSql.Append(" where a.CompanyCD = @CompanyCD ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustInfoM.CompanyCD));
#endregion
if (CustInfoM.ID != 0)
{
strSql.Append(" and a.CustID = @CustID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustInfoM.ID.ToString()));
}
if (!string.IsNullOrEmpty(CustInfoM.Tel))
{
strSql.Append(" and ((a.CustID in (select z.id from officedba.custinfo z where CompanyCD = @CompanyCD ");
strSql.Append(" and( z.Tel like @Tel or z.Mobile like @Tel or z.Fax like @Tel)))");
strSql.Append(" or (a.CustID in (select y.ID from officedba.CustInfo y");
strSql.Append(" left join officedba.CustLinkMan x on y.CustNo = x.CustNo and y.CompanyCD = x.CompanyCD and y.CompanyCD = @CompanyCD");
strSql.Append(" where x.WorkTel like @Tel or x.Fax like @Tel or x.Handset like @Tel");
strSql.Append(" or x.HomeTel like @Tel))");
strSql.Append(" ) ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Tel", "%" + CustInfoM.Tel + "%"));
}
if (!string.IsNullOrEmpty(DateBegin))
{
strSql.AppendLine(" and a.AdviceDate >= @DateBegin");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DateBegin", DateBegin));
}
if (!string.IsNullOrEmpty(DateEnd))
{
strSql.AppendLine(" and a.AdviceDate <= @DateEnd");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DateEnd", DateEnd));
}
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
public static DataTable GetCust_daByCon(CustInfoModel CustInfoM, string DateBegin, string DateEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.ID,a.CustNo,a.CustName,a.CustClass,b.CodeName CustClassName,a.ModifiedDate, ");
strSql.Append(" a.CustType,c.TypeName CustTypeName,a.Manager,d.EmployeeName ManagerName, ");
strSql.Append(" a.CreditGrade,e.TypeName CreditGradeName,(case a.RelaGrade when '1' then '密切' ");
strSql.Append(" when '2' then '较好' when '3' then '一般' ");
strSql.Append(" when '4' then '较差' end) RelaGradeName,");
strSql.Append(" a.Creator,f.EmployeeName CreatorName,CONVERT(varchar(100), a.CreatedDate, 23) CreatedDate,");
strSql.Append(" (case a.UsedStatus when '1' then '启用' when '0' then '停用' end) UsedStatusName");
strSql.Append(" from officedba.CustInfo a");
strSql.Append(" left join officedba.CodeCompanyType b on b.id = a.CustClass");
strSql.Append(" left join officedba.CodePublicType c on c.id = a.CustType");
strSql.Append(" left join officedba.EmployeeInfo d on d.id = a.Manager");
strSql.Append(" left join officedba.CodePublicType e on e.id = a.CreditGrade");
strSql.Append(" left join officedba.EmployeeInfo f on f.id = a.Creator");
strSql.Append(" where a.CompanyCD = @CompanyCD ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustInfoM.CompanyCD));
#endregion
if (CustInfoM.ID != 0)
{
strSql.Append(" and a.ID = @CustID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustInfoM.ID.ToString()));
}
if (!string.IsNullOrEmpty(CustInfoM.Tel))
{
strSql.Append(" and ((a.Tel like @Tel or a.Mobile like @Tel or a.Fax like @Tel)");
strSql.Append(" or (a.ID in (select y.ID from officedba.CustInfo y");
strSql.Append(" left join officedba.CustLinkMan x on y.CustNo = x.CustNo and y.CompanyCD = x.CompanyCD and y.CompanyCD = @CompanyCD");
strSql.Append(" where x.WorkTel like @Tel or x.Fax like @Tel or x.Handset like @Tel");
strSql.Append(" or x.HomeTel like @Tel))");
strSql.Append(" ) ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Tel", "%" + CustInfoM.Tel + "%"));
}
if (!string.IsNullOrEmpty(DateBegin))
{
strSql.AppendLine(" and a.CreatedDate >= @DateBegin");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DateBegin", DateBegin));
}
if (!string.IsNullOrEmpty(DateEnd))
{
strSql.AppendLine(" and a.CreatedDate <= @DateEnd");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DateEnd", DateEnd));
}
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
/// <summary>
/// 添加客户信息的方法
/// </summary>
/// <param name="CustInfoModel">客户信息</param>
/// <returns>bool值</returns>
public static bool CustInfoAdd(CustInfoModel CustInfoModel,LinkManModel LinkManM, Hashtable htExtAttr)
{
try
{
#region 拼写SQL语句
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO officedba.CustInfo");
sql.AppendLine("(CompanyCD");
sql.AppendLine(",CustTypeManage");
sql.AppendLine(",CustTypeSell");
sql.AppendLine(",CustTypeTime");
sql.AppendLine(",CustType");
sql.AppendLine(",CustClass ");
sql.AppendLine(",CustNo ");
sql.AppendLine(",CustName ");
sql.AppendLine(",CustNam ");
sql.AppendLine(",CustShort ");
sql.AppendLine(",CreditGrade ");
sql.AppendLine(",Manager ");
sql.AppendLine(",AreaID ");
sql.AppendLine(",CustNote ");
sql.AppendLine(",LinkCycle ");
sql.AppendLine(",HotIs ");
sql.AppendLine(",HotHow ");
sql.AppendLine(",MeritGrade ");
sql.AppendLine(",RelaGrade ");
sql.AppendLine(",Relation ");
sql.AppendLine(",CompanyType ");
sql.AppendLine(",StaffCount ");
sql.AppendLine(",Source ");
sql.AppendLine(",Phase ");
sql.AppendLine(",CustSupe ");
sql.AppendLine(",Trade ");
sql.AppendLine(",SetupDate ");
sql.AppendLine(",ArtiPerson ");
sql.AppendLine(",SetupMoney ");
sql.AppendLine(",SetupAddress ");
sql.AppendLine(",CapitalScale ");
sql.AppendLine(",SaleroomY ");
sql.AppendLine(",ProfitY ");
sql.AppendLine(",TaxCD ");
sql.AppendLine(",BusiNumber ");
sql.AppendLine(",IsTax ");
sql.AppendLine(",SellMode ");
sql.AppendLine(",SellArea ");
sql.AppendLine(",CountryID ");
sql.AppendLine(",Province ");
sql.AppendLine(",City ");
sql.AppendLine(",Tel ");
sql.AppendLine(",ContactName ");
sql.AppendLine(",Mobile ");
sql.AppendLine(",ReceiveAddress ");
sql.AppendLine(",WebSite ");
sql.AppendLine(",Post ");
sql.AppendLine(",email ");
sql.AppendLine(",Fax ");
sql.AppendLine(",OnLine ");
sql.AppendLine(",TakeType ");
sql.AppendLine(",CarryType ");
sql.AppendLine(",BusiType ");
sql.AppendLine(",BillType ");
sql.AppendLine(",PayType ");
sql.AppendLine(",MoneyType ");
sql.AppendLine(",CurrencyType ");
sql.AppendLine(",CreditManage ");
sql.AppendLine(",MaxCredit ");
sql.AppendLine(",MaxCreditDate ");
sql.AppendLine(",UsedStatus ");
sql.AppendLine(",CanViewUser ");
sql.AppendLine(",CanViewUserName ");
sql.AppendLine(",Creator ");
sql.AppendLine(",CreatedDate ");
sql.AppendLine(",FirstBuyDate ");
sql.AppendLine(",OpenBank ");
sql.AppendLine(",AccountMan ");
sql.AppendLine(",AccountNum ");
sql.AppendLine(",ModifiedUserID ");
sql.AppendLine(",CompanyValues ");
sql.AppendLine(",CatchWord ");
sql.AppendLine(",ManageValues ");
sql.AppendLine(",Potential ");
sql.AppendLine(",Problem ");
sql.AppendLine(",Advantages ");
sql.AppendLine(",TradePosition ");
sql.AppendLine(",Competition ");
sql.AppendLine(",Collaborator ");
sql.AppendLine(",ManagePlan ");
sql.AppendLine(",Collaborate ");
sql.AppendLine(",CustBig ");
sql.AppendLine(",CustNum ");
sql.AppendLine(",Remark) ");
sql.AppendLine(" values ");
sql.AppendLine("(@CompanyCD");
sql.AppendLine(",@CustTypeManage");
sql.AppendLine(",@CustTypeSell");
sql.AppendLine(",@CustTypeTime");
sql.AppendLine(",@CustType");
sql.AppendLine(",@CustClass ");
sql.AppendLine(",@CustNo ");
sql.AppendLine(",@CustName ");
sql.AppendLine(",@CustNam ");
sql.AppendLine(",@CustShort ");
sql.AppendLine(",@CreditGrade ");
sql.AppendLine(",@Manager ");
sql.AppendLine(",@AreaID ");
sql.AppendLine(",@CustNote ");
sql.AppendLine(",@LinkCycle ");
sql.AppendLine(",@HotIs ");
sql.AppendLine(",@HotHow ");
sql.AppendLine(",@MeritGrade ");
sql.AppendLine(",@RelaGrade ");
sql.AppendLine(",@Relation ");
sql.AppendLine(",@CompanyType ");
sql.AppendLine(",@StaffCount ");
sql.AppendLine(",@Source ");
sql.AppendLine(",@Phase ");
sql.AppendLine(",@CustSupe ");
sql.AppendLine(",@Trade ");
sql.AppendLine(",@SetupDate ");
sql.AppendLine(",@ArtiPerson ");
sql.AppendLine(",@SetupMoney ");
sql.AppendLine(",@SetupAddress ");
sql.AppendLine(",@CapitalScale ");
sql.AppendLine(",@SaleroomY ");
sql.AppendLine(",@ProfitY ");
sql.AppendLine(",@TaxCD ");
sql.AppendLine(",@BusiNumber ");
sql.AppendLine(",@IsTax ");
sql.AppendLine(",@SellMode ");
sql.AppendLine(",@SellArea ");
sql.AppendLine(",@CountryID ");
sql.AppendLine(",@Province ");
sql.AppendLine(",@City ");
sql.AppendLine(",@Tel ");
sql.AppendLine(",@ContactName ");
sql.AppendLine(",@Mobile ");
sql.AppendLine(",@ReceiveAddress ");
sql.AppendLine(",@WebSite ");
sql.AppendLine(",@Post ");
sql.AppendLine(",@email ");
sql.AppendLine(",@Fax ");
sql.AppendLine(",@OnLine ");
sql.AppendLine(",@TakeType ");
sql.AppendLine(",@CarryType ");
sql.AppendLine(",@BusiType ");
sql.AppendLine(",@BillType ");
sql.AppendLine(",@PayType ");
sql.AppendLine(",@MoneyType ");
sql.AppendLine(",@CurrencyType ");
sql.AppendLine(",@CreditManage ");
sql.AppendLine(",@MaxCredit ");
sql.AppendLine(",@MaxCreditDate ");
sql.AppendLine(",@UsedStatus ");
sql.AppendLine(",@CanViewUser ");
sql.AppendLine(",@CanViewUserName ");
sql.AppendLine(",@Creator ");
sql.AppendLine(",@CreatedDate ");
sql.AppendLine(",@FirstBuyDate ");
sql.AppendLine(",@OpenBank ");
sql.AppendLine(",@AccountMan ");
sql.AppendLine(",@AccountNum ");
sql.AppendLine(",@ModifiedUserID ");
sql.AppendLine(",@CompanyValues ");
sql.AppendLine(",@CatchWord ");
sql.AppendLine(",@ManageValues ");
sql.AppendLine(",@Potential ");
sql.AppendLine(",@Problem ");
sql.AppendLine(",@Advantages ");
sql.AppendLine(",@TradePosition ");
sql.AppendLine(",@Competition ");
sql.AppendLine(",@Collaborator ");
sql.AppendLine(",@ManagePlan ");
sql.AppendLine(",@Collaborate ");
sql.AppendLine(",@CustBig ");
sql.AppendLine(",@CustNum ");
sql.AppendLine(",@Remark) ");
#endregion
#region 设置参数
SqlCommand mycomm = new SqlCommand();
mycomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", CustInfoModel.CompanyCD));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustType", CustInfoModel.CustType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustClass", CustInfoModel.CustClass));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustNo", CustInfoModel.CustNo));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustName", CustInfoModel.CustName));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustNam", CustInfoModel.CustNam));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustShort", CustInfoModel.CustShort));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CreditGrade", CustInfoModel.CreditGrade));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Manager", CustInfoModel.Seller));
mycomm.Parameters.Add(SqlHelper.GetParameter("@AreaID", CustInfoModel.AreaID));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustNote", CustInfoModel.CustNote));
mycomm.Parameters.Add(SqlHelper.GetParameter("@LinkCycle", CustInfoModel.LinkCycle));
mycomm.Parameters.Add(SqlHelper.GetParameter("@HotIs", CustInfoModel.HotIs));
mycomm.Parameters.Add(SqlHelper.GetParameter("@HotHow", CustInfoModel.HotHow));
mycomm.Parameters.Add(SqlHelper.GetParameter("@MeritGrade", CustInfoModel.MeritGrade));
mycomm.Parameters.Add(SqlHelper.GetParameter("@RelaGrade", CustInfoModel.RelaGrade));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Relation", CustInfoModel.Relation));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CompanyType", CustInfoModel.CompanyType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@StaffCount", CustInfoModel.StaffCount));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Source", CustInfoModel.Source));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Phase", CustInfoModel.Phase));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustSupe", CustInfoModel.CustSupe));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Trade", CustInfoModel.Trade));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SetupDate", CustInfoModel.SetupDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.SetupDate.ToString())));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ArtiPerson", CustInfoModel.ArtiPerson));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SetupMoney", CustInfoModel.SetupMoney));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SetupAddress", CustInfoModel.SetupAddress));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CapitalScale", CustInfoModel.CapitalScale));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SaleroomY", CustInfoModel.SaleroomY));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ProfitY", CustInfoModel.ProfitY));
mycomm.Parameters.Add(SqlHelper.GetParameter("@TaxCD", CustInfoModel.TaxCD));
mycomm.Parameters.Add(SqlHelper.GetParameter("@BusiNumber", CustInfoModel.BusiNumber));
mycomm.Parameters.Add(SqlHelper.GetParameter("@IsTax", CustInfoModel.IsTax));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SellMode", CustInfoModel.SellMode));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SellArea", CustInfoModel.SellArea));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CountryID", CustInfoModel.CountryID));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Province", CustInfoModel.Province));
mycomm.Parameters.Add(SqlHelper.GetParameter("@City", CustInfoModel.City));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Tel", CustInfoModel.Tel));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ContactName", CustInfoModel.ContactName));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Mobile", CustInfoModel.Mobile));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ReceiveAddress", CustInfoModel.ReceiveAddress));
mycomm.Parameters.Add(SqlHelper.GetParameter("@WebSite", CustInfoModel.WebSite));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Post", CustInfoModel.Post));
mycomm.Parameters.Add(SqlHelper.GetParameter("@email", CustInfoModel.email));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Fax", CustInfoModel.Fax));
mycomm.Parameters.Add(SqlHelper.GetParameter("@OnLine", CustInfoModel.OnLine));
mycomm.Parameters.Add(SqlHelper.GetParameter("@TakeType", CustInfoModel.TakeType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CarryType", CustInfoModel.CarryType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@BusiType", CustInfoModel.BusiType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@BillType", CustInfoModel.BillType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@PayType", CustInfoModel.PayType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@MoneyType", CustInfoModel.MoneyType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CurrencyType", CustInfoModel.CurrencyType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CreditManage", CustInfoModel.CreditManage));
mycomm.Parameters.Add(SqlHelper.GetParameter("@MaxCredit", CustInfoModel.MaxCredit));
mycomm.Parameters.Add(SqlHelper.GetParameter("@MaxCreditDate", CustInfoModel.MaxCreditDate));
mycomm.Parameters.Add(SqlHelper.GetParameter("@UsedStatus", CustInfoModel.UsedStatus));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Creator", CustInfoModel.Creator));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CreatedDate", CustInfoModel.CreatedDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.CreatedDate.ToString())));
mycomm.Parameters.Add(SqlHelper.GetParameter("@OpenBank", CustInfoModel.OpenBank));
mycomm.Parameters.Add(SqlHelper.GetParameter("@AccountMan", CustInfoModel.AccountMan));
mycomm.Parameters.Add(SqlHelper.GetParameter("@AccountNum", CustInfoModel.AccountNum));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustBig", CustInfoModel.CustBig));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustNum", CustInfoModel.CustNum));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Remark", CustInfoModel.Remark));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustTypeManage", CustInfoModel.CustTypeManage));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustTypeSell", CustInfoModel.CustTypeSell));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustTypeTime", CustInfoModel.CustTypeTime));
mycomm.Parameters.Add(SqlHelper.GetParameter("@FirstBuyDate", CustInfoModel.FirstBuyDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.FirstBuyDate.ToString())));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", CustInfoModel.ModifiedUserID));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CanViewUser", CustInfoModel.CanViewUser));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CanViewUserName", CustInfoModel.CanViewUserName));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CompanyValues", CustInfoModel.CompanyValues));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CatchWord", CustInfoModel.CatchWord));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ManageValues", CustInfoModel.ManageValues));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Potential", CustInfoModel.Potential));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Problem", CustInfoModel.Problem));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Advantages", CustInfoModel.Advantages));
mycomm.Parameters.Add(SqlHelper.GetParameter("@TradePosition", CustInfoModel.TradePosition));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Competition", CustInfoModel.Competition));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Collaborator", CustInfoModel.Collaborator));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ManagePlan", CustInfoModel.ManagePlan));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Collaborate", CustInfoModel.Collaborate));
mycomm.CommandText = sql.ToString();
#endregion
ArrayList lstCmd = new ArrayList();
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(CustInfoModel, htExtAttr, cmd);
lstCmd.Add(mycomm);//基本信息
if (htExtAttr != null)//扩展信息
lstCmd.Add(cmd);
if (CustInfoModel.CustBig == "2")//当选择会员客户时,插入信息到联系人表
{
SqlCommand cmdLinkMan = new SqlCommand();
StringBuilder sqlLinkMan = new StringBuilder();
sqlLinkMan.AppendLine(" insert into officedba.CustLinkMan ");
sqlLinkMan.AppendLine(" ( CompanyCD,CustNo,LinkManName,Sex,LinkType,PaperNum,Birthday,WorkTel,Handset,Fax,Position, ");
sqlLinkMan.AppendLine(" Important, ");
sqlLinkMan.AppendLine(" Age,Post,MailAddress,HomeTown,NationalID,CultureLevel,Professional,IncomeYear,FuoodDrink, ");
sqlLinkMan.AppendLine(" LoveMusic,LoveColor,LoveSmoke,LoveDrink,LoveTea,LoveBook,LoveSport,LoveClothes,Cosmetic, ");
sqlLinkMan.AppendLine(" Nature,Appearance,AdoutBody,AboutFamily,Car,CanViewUser,CanViewUserName,Creator,CreatedDate ) values ");
sqlLinkMan.AppendLine(" ( @CompanyCD,@CustNo,@LinkManName,@Sex,@LinkType,@PaperNum,@Birthday,@WorkTel,@Handset,@Fax,@Position, ");
sqlLinkMan.AppendLine(" '0',");
sqlLinkMan.AppendLine(" @Age,@Post,@MailAddress,@HomeTown,@NationalID,@CultureLevel,@Professional,@IncomeYear,@FuoodDrink, ");
sqlLinkMan.AppendLine(" @LoveMusic,@LoveColor,@LoveSmoke,@LoveDrink,@LoveTea,@LoveBook,@LoveSport,@LoveClothes,@Cosmetic, ");
sqlLinkMan.AppendLine(" @Nature,@Appearance,@AdoutBody,@AboutFamily,@Car,@CanViewUser,@CanViewUserName,@Creator,@CreatedDate ) ");
#region 设置联系人参数
SqlCommand commLinkMan = new SqlCommand();
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", CustInfoModel.CompanyCD));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CustNo", CustInfoModel.CustNo));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LinkManName", CustInfoModel.CustName));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Sex", LinkManM.Sex));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LinkType", LinkManM.LinkType));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@PaperNum", LinkManM.PaperNum));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Birthday", LinkManM.Birthday == null ? SqlDateTime.Null : SqlDateTime.Parse(LinkManM.Birthday.ToString())));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@WorkTel", LinkManM.WorkTel));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Handset", LinkManM.Handset));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Fax", LinkManM.Fax));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Position", LinkManM.Position));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Age", LinkManM.Age));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Post", LinkManM.Post));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@MailAddress", LinkManM.MailAddress));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@HomeTown", LinkManM.HomeTown));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@NationalID", LinkManM.NationalID));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CultureLevel", LinkManM.CultureLevel));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Professional", LinkManM.Professional));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@IncomeYear", LinkManM.IncomeYear));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@FuoodDrink", LinkManM.FuoodDrink));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveMusic", LinkManM.LoveMusic));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveColor", LinkManM.LoveColor));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveSmoke", LinkManM.LoveSmoke));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveDrink", LinkManM.LoveDrink));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveTea", LinkManM.LoveTea));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveBook", LinkManM.LoveBook));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveSport", LinkManM.LoveSport));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveClothes", LinkManM.LoveClothes));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Cosmetic", LinkManM.Cosmetic));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Nature", LinkManM.Nature));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Appearance", LinkManM.Appearance));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@AdoutBody", LinkManM.AdoutBody));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@AboutFamily", LinkManM.AboutFamily));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Car", LinkManM.Car));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CanViewUser", CustInfoModel.CanViewUser));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CanViewUserName", CustInfoModel.CanViewUserName));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Creator", CustInfoModel.Creator));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CreatedDate", CustInfoModel.CreatedDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.CreatedDate.ToString())));
commLinkMan.CommandText = sqlLinkMan.ToString();
#endregion
lstCmd.Add(commLinkMan);
}
//执行登陆操作
bool isSucc = SqlHelper.ExecuteTransWithArrayList(lstCmd);
return isSucc;
}
catch
{
return false;
}
}
/// <summary>
/// 客户一览表查询打印
/// </summary>
/// <param name="CustModel"></param>
/// <param name="ord"></param>
/// <returns></returns>
public static DataTable GetCustListByConditionPrint(CustInfoModel CustModel, string ord)
{
try
{
string sql = " select " +
" ci.ID,ci.CustNo,ci.CustName," +
" (case ci.CustTypeManage when '1' then 'VIP客户' when '2' then '主要客户' when '3' then '普通客户' when '4' then '临时客户' else '' end) CustTypeManage," +
" (case ci.CustTypeSell when '1' then '经济型客户' when '2' then '个性化客户' when '3' then '方便型客户' when '4' then '道德型客户' else '' end) CustTypeSell," +
" (case ci.CustTypeTime when '1' then '老客户' when '2' then '新客户' when '3' then '潜在客户' else '' end) CustTypeTime," +
" ci.CustType,isnull(cp.TypeName,'') CustTypeName," +
" ci.CustClass,isnull(cc.CodeName,'') CustClassName," +
" ci.CreditGrade,isnull(cp2.TypeName,'') CreditGradeName," +
" (case ci.RelaGrade when '1' then '密切' when '2' then '较好' when '3' then '一般' when '4' then '较差' else '' end) RelaGrade," +
" ci.AreaID,isnull(cp3.TypeName,'') Area,ci.Province,ci.City," +
" ci.Manager,isnull(ei.EmployeeName,'') ManagerName,ci.ContactName,ci.Tel," +
" ci.Creator,isnull(ei2.EmployeeName,'') CreatorName,CONVERT(varchar(100), ci.CreatedDate, 23) CreatedDate" +
" from " +
" officedba.CustInfo ci" +
" left join officedba.CodePublicType cp on ci.CustType = cp.id" +
" left join officedba.CodeCompanyType cc on ci.CustClass = cc.id" +
" left join officedba.CodePublicType cp2 on ci.CreditGrade = cp2.id" +
" left join officedba.CodePublicType cp3 on ci.AreaID = cp3.id" +
" left join officedba.EmployeeInfo ei on ci.Manager = ei.id" +
" left join officedba.EmployeeInfo ei2 on ci.Creator = ei2.id" +
" where ci.CompanyCD = '" + CustModel.CompanyCD + "'";
if (CustModel.CustTypeManage != "0")
sql += " and ci.CustTypeManage = '" + CustModel.CustTypeManage + "'";
if (CustModel.CustTypeSell != "0")
sql += " and ci.CustTypeSell = '" + CustModel.CustTypeSell + "'";
if (CustModel.CustTypeTime != "0")
sql += " and ci.CustTypeTime = '" + CustModel.CustTypeTime + "'";
if (CustModel.CustType != 0)
sql += " and ci.CustType='" + CustModel.CustType + "'";
if (CustModel.CustClass != 0)
sql += " and ci.CustClass='" + CustModel.CustClass + "'";
if (CustModel.CreditGrade != 0)
sql += " and ci.CreditGrade='" + CustModel.CreditGrade + "'";
if (CustModel.RelaGrade != "0")
sql += " and ci.RelaGrade='" + CustModel.RelaGrade + "'";
if (CustModel.AreaID != 0)
sql += " and ci.AreaID='" + CustModel.AreaID + "'";
if (CustModel.Seller != 0)
sql += " and ci.Manager='" + CustModel.Seller + "'";
sql = sql + ord;
//return SqlHelper.CreateSqlByPageExcuteSql(sql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount);
return SqlHelper.ExecuteSql(sql);
}
catch
{
return null;
}
}
/// <summary>
/// 根据创建人获取客户ID、编号、简称的方法
/// </summary>
/// <param name="Creator">创建人</param>
/// <param name="CompanyCD">公司代码</param>
/// <returns>客户列表结果集</returns>
public static DataTable GetCustName(CustInfoModel CustModel,string CanUserID, string CompanyCD)
{
try
{
//string sql = "select id,CustNo,CustNam from officedba.CustInfo where Creator='" + Creator + "' and CompanyCD = '" + CompanyCD + "'";
string sql = "select " +
" ci.id,ci.CustNo,ci.CustName,isnull(ci.CustShort,'') CustShort,isnull(ci.AreaID,0) AreaID,isnull(cp.TypeName,'') TypeName,isnull(ci.Tel,'') Tel " +
"from " +
" officedba.CustInfo ci " +
"left join officedba.CodePublicType cp on cp.id = ci.AreaID " +
"where ci.UsedStatus <> 0 " +
"and ci.CompanyCD = '" + CompanyCD + "'" +
" and (ci.CanViewUser like '%" + "," + CanUserID + "," + "%' or '" + CanUserID + "' = ci.Creator or '" + CanUserID + "' = ci.Manager or ci.CanViewUser = '******' or ci.CanViewUser is null )";
if (CustModel.CustNo != "")
sql += " and ci.CustNo like '%" + CustModel.CustNo + "%'";
if (CustModel.CustName != "")
sql += " and ci.CustName like '%" + CustModel.CustName + "%'";
if (CustModel.CustShort != "")
sql += " and ci.CustShort like '%" + CustModel.CustShort + "%'";
return SqlHelper.ExecuteSql(sql);
}
catch
{
return null;
}
}
/// <summary>
/// 根据查询条件获取客户列表信息的方法
/// </summary>
/// <param name="CustModel">查询条件</param>
/// <param name="Creator">登陆人</param>
/// <returns>客户列表结果集</returns>
public static DataTable GetCustInfoBycondition(CustInfoModel CustModel, string Manager, string CanUserID, string CreatedBegin, string CreatedEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
string sql = " select distinct " +
" ci.id,ci.CustBig, ci.CustNo,ci.CustName,ci.CustNam,ci.CustShort, " +
" (case ci.CustBig when '2' then '会员' else '企业' end)CustBigName, " +
" ci.CustClass,isnull(cc.CodeName,'') CodeName," +
" ci.CustType,isnull(cp.TypeName,'') TypeName," +
" ci.AreaID,isnull(cpa.TypeName,'') Area," +
" isnull(ei.EmployeeName,'') Manager," +
" isnull(cpc.TypeName,'') CreditGrade," +
" ci.RelaGrade,isnull(eic.EmployeeName,'') Creator," +
" CONVERT(varchar(100), ci.CreatedDate, 23) CreatedDate,ci.UsedStatus " +
" from " +
" officedba.CustInfo ci " +
" left join officedba.CodeCompanyType cc on cc.id = ci.CustClass " +
" left join officedba.EmployeeInfo ei on ei.id = ci.Manager " +
" left join officedba.CodePublicType cpa on cpa.id = ci.AreaID" +
" left join officedba.CodePublicType cpc on cpc.id = ci.CreditGrade" +
" left join officedba.CodePublicType cp on cp.id = ci.CustType" +
" left join officedba.EmployeeInfo eic on eic.id = ci.Creator" +
" left join officedba.CustLinkMan cl on ci.custno = cl.custno " +
" where ci.CompanyCD = '" + CustModel.CompanyCD + "'" +
" and (ci.CanViewUser like '%" + "," + CanUserID + "," + "%' or '" + CanUserID + "' = ci.Creator or '" + CanUserID + "' = ci.Manager or ci.CanViewUser = '******' or ci.CanViewUser is null )";
if (CustModel.CustNo != "")
sql += " and ci.CustNo like '%" + CustModel.CustNo + "%'";
if (CustModel.CustNam != "")
sql += " and ci.CustNam like '%" + CustModel.CustNam + "%'";
if (CustModel.CustClass != 0)
sql += " and ci.CustClass='" + CustModel.CustClass + "'";
if (CustModel.CustName != "")
sql += " and ci.CustName like '%" + CustModel.CustName + "%'";
if (CustModel.Tel != "")
sql += " and (ci.Tel like '%" + CustModel.Tel + "%' or cl.worktel like '%" + CustModel.Tel + "%')";
if (CustModel.CustShort != "")
sql += " and ci.CustShort like '%" + CustModel.CustShort + "%'";
if (CustModel.AreaID != 0)
sql += " and ci.AreaID='" + CustModel.AreaID + "'";
if (CustModel.CreditGrade != 0)
sql += " and ci.CreditGrade='" + CustModel.CreditGrade + "'";
if (CustModel.RelaGrade != "0")
sql += " and ci.RelaGrade='" + CustModel.RelaGrade + "'";
if (Manager != "")
sql += " and ei.EmployeeName like '%" + Manager + "%'";
if (CreatedBegin.ToString() != "")
sql += " and ci.CreatedDate >= '" + CreatedBegin.ToString() + "'";
if (CreatedEnd.ToString() != "")
sql += " and ci.CreatedDate <= '" + CreatedEnd.ToString() + "'";
if (CustModel.CustBig != "0")
sql += " and ci.CustBig = '" + CustModel.CustBig + "'";
if (CustModel.UsedStatus != "-1")
sql += " and ci.UsedStatus = '" + CustModel.UsedStatus + "'";
return SqlHelper.CreateSqlByPageExcuteSql(sql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount);
//return SqlHelper.ExecuteSql(sql);
}
catch
{
return null;
}
}
/// <summary>
/// 同时修改客户信息及联系人列表信息的方法
/// </summary>
/// <param name="CustInfoModel">客户信息Model</param>
/// <param name="LinkManlist">联系人列表信息流</param>
/// <returns>bool值</returns>
public static bool UpdateCustAndLinkMan(CustInfoModel CustInfoModel, string LinkManlist)
{
try
{
#region 拼写修改客户信息SQL语句
StringBuilder sqlcust = new StringBuilder();
sqlcust.AppendLine("UPDATE officedba.CustInfo set ");
sqlcust.AppendLine("CompanyCD= @CompanyCD, ");
sqlcust.AppendLine("CustType= @CustType, ");
sqlcust.AppendLine("CustClass = @CustClass , ");
//sqlcust.AppendLine("CustNo = @CustNo , ");
sqlcust.AppendLine("CustName = @CustName , ");
sqlcust.AppendLine("CustNam = @CustNam , ");
sqlcust.AppendLine("CustShort = @CustShort , ");
sqlcust.AppendLine("CreditGrade = @CreditGrade , ");
sqlcust.AppendLine("Manager = @Manager , ");
sqlcust.AppendLine("AreaID = @AreaID , ");
sqlcust.AppendLine("CustNote = @CustNote , ");
sqlcust.AppendLine("LinkCycle = @LinkCycle , ");
sqlcust.AppendLine("HotIs = @HotIs , ");
sqlcust.AppendLine("HotHow = @HotHow , ");
sqlcust.AppendLine("HotType = @HotType , ");
sqlcust.AppendLine("MeritGrade = @MeritGrade , ");
sqlcust.AppendLine("RelaGrade = @RelaGrade , ");
sqlcust.AppendLine("Relation = @Relation , ");
sqlcust.AppendLine("CompanyType = @CompanyType , ");
sqlcust.AppendLine("StaffCount = @StaffCount , ");
sqlcust.AppendLine("Source = @Source , ");
sqlcust.AppendLine("Phase = @Phase , ");
sqlcust.AppendLine("CustSupe = @CustSupe , ");
sqlcust.AppendLine("Trade = @Trade , ");
sqlcust.AppendLine("SetupDate = @SetupDate , ");
sqlcust.AppendLine("ArtiPerson = @ArtiPerson , ");
sqlcust.AppendLine("SetupMoney = @SetupMoney , ");
sqlcust.AppendLine("SetupAddress = @SetupAddress , ");
sqlcust.AppendLine("CapitalScale = @CapitalScale , ");
sqlcust.AppendLine("SaleroomY = @SaleroomY , ");
sqlcust.AppendLine("ProfitY = @ProfitY , ");
sqlcust.AppendLine("TaxCD = @TaxCD , ");
sqlcust.AppendLine("BusiNumber = @BusiNumber , ");
sqlcust.AppendLine("IsTax = @IsTax , ");
sqlcust.AppendLine("SellMode = @SellMode , ");
sqlcust.AppendLine("SellArea = @SellArea , ");
sqlcust.AppendLine("CountryID = @CountryID , ");
sqlcust.AppendLine("Province = @Province , ");
sqlcust.AppendLine("City = @City , ");
sqlcust.AppendLine("Tel = @Tel , ");
sqlcust.AppendLine("ContactName = @ContactName , ");
sqlcust.AppendLine("Mobile = @Mobile , ");
sqlcust.AppendLine("ReceiveAddress = @ReceiveAddress , ");
sqlcust.AppendLine("WebSite = @WebSite , ");
sqlcust.AppendLine("Post = @Post , ");
sqlcust.AppendLine("email = @email , ");
sqlcust.AppendLine("Fax = @Fax , ");
sqlcust.AppendLine("OnLine = @OnLine , ");
sqlcust.AppendLine("TakeType = @TakeType , ");
sqlcust.AppendLine("CarryType = @CarryType , ");
sqlcust.AppendLine("BusiType = @BusiType , ");
sqlcust.AppendLine("BillType = @BillType , ");
sqlcust.AppendLine("PayType = @PayType , ");
sqlcust.AppendLine("MoneyType = @MoneyType , ");
sqlcust.AppendLine("CurrencyType = @CurrencyType , ");
sqlcust.AppendLine("CreditManage = @CreditManage , ");
sqlcust.AppendLine("MaxCredit = @MaxCredit , ");
sqlcust.AppendLine("MaxCreditDate = @MaxCreditDate , ");
//sqlcust.AppendLine("UpdateCredit = @UpdateCredit , ");
sqlcust.AppendLine("UsedStatus = @UsedStatus , ");
sqlcust.AppendLine("Creator = @Creator , ");
sqlcust.AppendLine("CreatedDate = @CreatedDate , ");
sqlcust.AppendLine("OpenBank = @OpenBank , ");
sqlcust.AppendLine("AccountMan = @AccountMan , ");
sqlcust.AppendLine("AccountNum = @AccountNum , ");
sqlcust.AppendLine("Remark = @Remark, ");
sqlcust.AppendLine("CustBig = @CustBig, ");
sqlcust.AppendLine("CustNum = @CustNum, ");
sqlcust.AppendLine("ModifiedDate = @ModifiedDate, ");
sqlcust.AppendLine("ModifiedUserID = @ModifiedUserID ");
sqlcust.AppendLine(" WHERE ");
sqlcust.AppendLine("CustNo = @CustNo ");
#endregion
#region 设置修改客户信息参数
SqlParameter[] param = new SqlParameter[69];
param[0] = SqlHelper.GetParameter("@CompanyCD", CustInfoModel.CompanyCD);
param[1] = SqlHelper.GetParameter("@CustType", CustInfoModel.CustType);
param[2] = SqlHelper.GetParameter("@CustClass", CustInfoModel.CustClass);
param[3] = SqlHelper.GetParameter("@CustNo", CustInfoModel.CustNo);
param[4] = SqlHelper.GetParameter("@CustName", CustInfoModel.CustName);
param[5] = SqlHelper.GetParameter("@CustNam", CustInfoModel.CustNam);
param[6] = SqlHelper.GetParameter("@CustShort", CustInfoModel.CustShort);
param[7] = SqlHelper.GetParameter("@CreditGrade", CustInfoModel.CreditGrade);
param[8] = SqlHelper.GetParameter("@Manager", CustInfoModel.Seller);
param[9] = SqlHelper.GetParameter("@AreaID", CustInfoModel.AreaID);
param[10] = SqlHelper.GetParameter("@CustNote", CustInfoModel.CustNote);
param[11] = SqlHelper.GetParameter("@LinkCycle", CustInfoModel.LinkCycle);
param[12] = SqlHelper.GetParameter("@HotIs", CustInfoModel.HotIs);
param[13] = SqlHelper.GetParameter("@HotHow", CustInfoModel.HotHow);
param[14] = SqlHelper.GetParameter("@HotType", CustInfoModel.HotType);
param[15] = SqlHelper.GetParameter("@MeritGrade", CustInfoModel.MeritGrade);
param[16] = SqlHelper.GetParameter("@RelaGrade", CustInfoModel.RelaGrade);
param[17] = SqlHelper.GetParameter("@Relation", CustInfoModel.Relation);
param[18] = SqlHelper.GetParameter("@CompanyType", CustInfoModel.CompanyType);
param[19] = SqlHelper.GetParameter("@StaffCount", CustInfoModel.StaffCount);
param[20] = SqlHelper.GetParameter("@Source", CustInfoModel.Source);
param[21] = SqlHelper.GetParameter("@Phase", CustInfoModel.Phase);
param[22] = SqlHelper.GetParameter("@CustSupe", CustInfoModel.CustSupe);
param[23] = SqlHelper.GetParameter("@Trade", CustInfoModel.Trade);
param[24] = SqlHelper.GetParameter("@SetupDate", CustInfoModel.SetupDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.SetupDate.ToString()));
param[25] = SqlHelper.GetParameter("@ArtiPerson", CustInfoModel.ArtiPerson);
param[26] = SqlHelper.GetParameter("@SetupMoney", CustInfoModel.SetupMoney);
param[27] = SqlHelper.GetParameter("@SetupAddress", CustInfoModel.SetupAddress);
param[28] = SqlHelper.GetParameter("@CapitalScale", CustInfoModel.CapitalScale);
param[29] = SqlHelper.GetParameter("@SaleroomY", CustInfoModel.SaleroomY);
param[30] = SqlHelper.GetParameter("@ProfitY", CustInfoModel.ProfitY);
param[31] = SqlHelper.GetParameter("@TaxCD", CustInfoModel.TaxCD);
param[32] = SqlHelper.GetParameter("@BusiNumber", CustInfoModel.BusiNumber);
param[33] = SqlHelper.GetParameter("@IsTax", CustInfoModel.IsTax);
param[34] = SqlHelper.GetParameter("@SellMode", CustInfoModel.SellMode);
param[35] = SqlHelper.GetParameter("@SellArea", CustInfoModel.SellArea);
param[36] = SqlHelper.GetParameter("@CountryID", CustInfoModel.CountryID);
param[37] = SqlHelper.GetParameter("@Province", CustInfoModel.Province);
param[38] = SqlHelper.GetParameter("@City", CustInfoModel.City);
param[39] = SqlHelper.GetParameter("@Tel", CustInfoModel.Tel);
param[40] = SqlHelper.GetParameter("@ContactName", CustInfoModel.ContactName);
param[41] = SqlHelper.GetParameter("@Mobile", CustInfoModel.Mobile);
param[42] = SqlHelper.GetParameter("@ReceiveAddress", CustInfoModel.ReceiveAddress);
param[43] = SqlHelper.GetParameter("@WebSite", CustInfoModel.WebSite);
param[44] = SqlHelper.GetParameter("@Post", CustInfoModel.Post);
param[45] = SqlHelper.GetParameter("@email", CustInfoModel.email);
param[46] = SqlHelper.GetParameter("@Fax", CustInfoModel.Fax);
param[47] = SqlHelper.GetParameter("@OnLine", CustInfoModel.OnLine);
param[48] = SqlHelper.GetParameter("@TakeType", CustInfoModel.TakeType);
param[49] = SqlHelper.GetParameter("@CarryType", CustInfoModel.CarryType);
param[50] = SqlHelper.GetParameter("@BusiType", CustInfoModel.BusiType);
param[51] = SqlHelper.GetParameter("@BillType", CustInfoModel.BillType);
param[52] = SqlHelper.GetParameter("@PayType", CustInfoModel.PayType);
param[53] = SqlHelper.GetParameter("@MoneyType", CustInfoModel.MoneyType);
param[54] = SqlHelper.GetParameter("@CurrencyType", CustInfoModel.CurrencyType);
param[55] = SqlHelper.GetParameter("@CreditManage", CustInfoModel.CreditManage);
param[56] = SqlHelper.GetParameter("@MaxCredit", CustInfoModel.MaxCredit);
param[57] = SqlHelper.GetParameter("@MaxCreditDate", CustInfoModel.MaxCreditDate);
param[58] = SqlHelper.GetParameter("@UsedStatus", CustInfoModel.UsedStatus);
param[59] = SqlHelper.GetParameter("@Creator", CustInfoModel.Creator);
param[60] = SqlHelper.GetParameter("@CreatedDate", CustInfoModel.CreatedDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.CreatedDate.ToString()));
param[61] = SqlHelper.GetParameter("@OpenBank", CustInfoModel.OpenBank);
param[62] = SqlHelper.GetParameter("@AccountMan", CustInfoModel.AccountMan);
param[63] = SqlHelper.GetParameter("@AccountNum", CustInfoModel.AccountNum);
param[64] = SqlHelper.GetParameter("@Remark", CustInfoModel.Remark);
param[65] = SqlHelper.GetParameter("@ModifiedDate", DateTime.Now);
param[66] = SqlHelper.GetParameter("@ModifiedUserID", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID);
param[67] = SqlHelper.GetParameter("@CustBig", CustInfoModel.CustBig);
param[68] = SqlHelper.GetParameter("@CustNum", CustInfoModel.CustNum);
#endregion
LinkManModel LinkManM = new LinkManModel();
string[] strlinkman = LinkManlist.Split('|'); //把联系人列表流分隔成数组
SqlCommand[] comms = new SqlCommand[strlinkman.Length + 1]; ; //申明cmd数组
SqlCommand cmdcust = new SqlCommand(sqlcust.ToString()); //未执行的客户信息修改命令
cmdcust.Parameters.AddRange(param);
comms[0] = cmdcust; //把未执行的客户信息修改命令给cmd数组第一项
//如果联系人信息不为空，执行删除及从表修改
if (LinkManlist != "")
{
//对于从表联系人信息先删除再添加
SqlCommand cmdlinkmandelete = new SqlCommand("delete officedba.CustLinkMan where CustNo = '" + CustInfoModel.CustNo + "'");
comms[1] = cmdlinkmandelete;
string recorditems = "";
string[] linkmanfield = null;
for (int i = 1; i < strlinkman.Length; i++) //循环数组
{
recorditems = strlinkman[i].ToString();//取到每一条记录:[序号,联系人姓名,手机,工作电话,职位,负责业务]
linkmanfield = recorditems.Split(','); //把每条记录分隔到字段
string fieldxh = linkmanfield[0].ToString();//序号
string fieldname = linkmanfield[1].ToString();//联系人姓名
string fieldhandset = linkmanfield[2].ToString();//手机
string fieldworktel = linkmanfield[3].ToString();//工作电话
string fieldposition = linkmanfield[4].ToString();//职务
string fieldoperation = linkmanfield[5].ToString();//负责业务
LinkManM.CompanyCD = CustInfoModel.CompanyCD; //联系人信息赋予一个LinkManM(联系人Model对象实例)
LinkManM.CustNo = CustInfoModel.CustNo;
LinkManM.LinkManName = fieldname;
LinkManM.Handset = fieldhandset;
LinkManM.WorkTel = fieldworktel;
LinkManM.Position = fieldposition;
LinkManM.Operation = fieldoperation;
LinkManM.ModifiedDate = DateTime.Now;
LinkManM.ModifiedUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;//Session读取
#region 拼写添加联系人信息sql语句
StringBuilder sqllinkman = new StringBuilder();
sqllinkman.AppendLine("INSERT INTO officedba.CustLinkMan");
sqllinkman.AppendLine("(CompanyCD");
sqllinkman.AppendLine(",CustNo ");
sqllinkman.AppendLine(",LinkManName");
sqllinkman.AppendLine(",Position ");
sqllinkman.AppendLine(",Operation ");
sqllinkman.AppendLine(",WorkTel ");
sqllinkman.AppendLine(",Handset ");
sqllinkman.AppendLine(",ModifiedDate");
sqllinkman.AppendLine(",ModifiedUserID)");
sqllinkman.AppendLine(" values ");
sqllinkman.AppendLine("(@CompanyCD");
sqllinkman.AppendLine(",@CustNo ");
sqllinkman.AppendLine(",@LinkManName");
sqllinkman.AppendLine(",@Position ");
sqllinkman.AppendLine(",@Operation ");
sqllinkman.AppendLine(",@WorkTel ");
sqllinkman.AppendLine(",@Handset ");
sqllinkman.AppendLine(",@ModifiedDate");
sqllinkman.AppendLine(",@ModifiedUserID)");
SqlParameter[] paramlinkman = new SqlParameter[9];
paramlinkman[0] = SqlHelper.GetParameter("@CompanyCD", CustInfoModel.CompanyCD);
paramlinkman[1] = SqlHelper.GetParameter("@CustNo", CustInfoModel.CustNo);
paramlinkman[2] = SqlHelper.GetParameter("@LinkManName", LinkManM.LinkManName);
paramlinkman[3] = SqlHelper.GetParameter("@Position", LinkManM.Position);
paramlinkman[4] = SqlHelper.GetParameter("@Operation", LinkManM.Operation);
paramlinkman[5] = SqlHelper.GetParameter("@WorkTel", LinkManM.WorkTel);
paramlinkman[6] = SqlHelper.GetParameter("@Handset", LinkManM.Handset);
paramlinkman[7] = SqlHelper.GetParameter("@ModifiedDate", LinkManM.ModifiedDate);
paramlinkman[8] = SqlHelper.GetParameter("@ModifiedUserID", LinkManM.ModifiedUserID);
#endregion
SqlCommand cmdlinkman = new SqlCommand(sqllinkman.ToString()); //未执行的联系人信息添加命令
cmdlinkman.Parameters.AddRange(paramlinkman);
comms[i + 1] = cmdlinkman; //把未执行的联系人信息添加命令给cmd数组
}
}
//执行
SqlHelper.ExecuteTransForList(comms);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
catch
{
return false;
}
}
/// <summary>
/// 根据客户编号修改客户信息的方法
/// </summary>
/// <param name="CustInfoModel">客户信息Model</param>
/// <returns>bool值</returns>
public static bool UpdateCustInfo(CustInfoModel CustInfoModel, LinkManModel LinkManM, Hashtable htExtAttr)
{
try
{
#region 拼写修改客户信息SQL语句
StringBuilder sqlcust = new StringBuilder();
sqlcust.AppendLine("UPDATE officedba.CustInfo set ");
//sqlcust.AppendLine("CompanyCD= @CompanyCD, ");
sqlcust.AppendLine("CustTypeManage = @CustTypeManage, ");
sqlcust.AppendLine("CustTypeSell = @CustTypeSell, ");
sqlcust.AppendLine("CustTypeTime = @CustTypeTime, ");
sqlcust.AppendLine("CustType= @CustType, ");
sqlcust.AppendLine("CustClass = @CustClass , ");
sqlcust.AppendLine("CustName = @CustName , ");
sqlcust.AppendLine("CustNam = @CustNam , ");
sqlcust.AppendLine("CustShort = @CustShort , ");
sqlcust.AppendLine("CreditGrade = @CreditGrade , ");
sqlcust.AppendLine("Manager = @Manager , ");
sqlcust.AppendLine("AreaID = @AreaID , ");
sqlcust.AppendLine("CustNote = @CustNote , ");
sqlcust.AppendLine("LinkCycle = @LinkCycle , ");
sqlcust.AppendLine("HotIs = @HotIs , ");
sqlcust.AppendLine("HotHow = @HotHow , ");
sqlcust.AppendLine("MeritGrade = @MeritGrade , ");
sqlcust.AppendLine("RelaGrade = @RelaGrade , ");
sqlcust.AppendLine("Relation = @Relation , ");
sqlcust.AppendLine("CompanyType = @CompanyType , ");
sqlcust.AppendLine("StaffCount = @StaffCount , ");
sqlcust.AppendLine("Source = @Source , ");
sqlcust.AppendLine("Phase = @Phase , ");
sqlcust.AppendLine("CustSupe = @CustSupe , ");
sqlcust.AppendLine("Trade = @Trade , ");
sqlcust.AppendLine("SetupDate = @SetupDate , ");
sqlcust.AppendLine("ArtiPerson = @ArtiPerson , ");
sqlcust.AppendLine("SetupMoney = @SetupMoney , ");
sqlcust.AppendLine("SetupAddress = @SetupAddress , ");
sqlcust.AppendLine("CapitalScale = @CapitalScale , ");
sqlcust.AppendLine("SaleroomY = @SaleroomY , ");
sqlcust.AppendLine("ProfitY = @ProfitY , ");
sqlcust.AppendLine("TaxCD = @TaxCD , ");
sqlcust.AppendLine("BusiNumber = @BusiNumber , ");
sqlcust.AppendLine("IsTax = @IsTax , ");
sqlcust.AppendLine("SellMode = @SellMode , ");
sqlcust.AppendLine("SellArea = @SellArea , ");
sqlcust.AppendLine("CountryID = @CountryID , ");
sqlcust.AppendLine("Province = @Province , ");
sqlcust.AppendLine("City = @City , ");
sqlcust.AppendLine("Tel = @Tel , ");
sqlcust.AppendLine("ContactName = @ContactName , ");
sqlcust.AppendLine("Mobile = @Mobile , ");
sqlcust.AppendLine("ReceiveAddress = @ReceiveAddress , ");
sqlcust.AppendLine("WebSite = @WebSite , ");
sqlcust.AppendLine("Post = @Post , ");
sqlcust.AppendLine("email = @email , ");
sqlcust.AppendLine("Fax = @Fax , ");
sqlcust.AppendLine("OnLine = @OnLine , ");
sqlcust.AppendLine("TakeType = @TakeType , ");
sqlcust.AppendLine("CarryType = @CarryType , ");
sqlcust.AppendLine("BusiType = @BusiType , ");
sqlcust.AppendLine("BillType = @BillType , ");
sqlcust.AppendLine("PayType = @PayType , ");
sqlcust.AppendLine("MoneyType = @MoneyType , ");
sqlcust.AppendLine("CurrencyType = @CurrencyType , ");
sqlcust.AppendLine("CreditManage = @CreditManage , ");
sqlcust.AppendLine("MaxCredit = @MaxCredit , ");
sqlcust.AppendLine("MaxCreditDate = @MaxCreditDate , ");
sqlcust.AppendLine("UsedStatus = @UsedStatus , ");
sqlcust.AppendLine("CanViewUser = @CanViewUser , ");
sqlcust.AppendLine("CanViewUserName = @CanViewUserName , ");
sqlcust.AppendLine("FirstBuyDate = @FirstBuyDate , ");
sqlcust.AppendLine("CreatedDate = @CreatedDate , ");
sqlcust.AppendLine("OpenBank = @OpenBank, ");
sqlcust.AppendLine("AccountMan = @AccountMan, ");
sqlcust.AppendLine("AccountNum = @AccountNum, ");
sqlcust.AppendLine("CustBig = @CustBig, ");
sqlcust.AppendLine("CustNum = @CustNum, ");
sqlcust.AppendLine("Remark = @Remark, ");
sqlcust.AppendLine("ModifiedDate = @ModifiedDate, ");
sqlcust.AppendLine("CompanyValues = @CompanyValues , ");
sqlcust.AppendLine("CatchWord = @CatchWord , ");
sqlcust.AppendLine("ManageValues = @ManageValues , ");
sqlcust.AppendLine("Potential = @Potential , ");
sqlcust.AppendLine("Problem = @Problem , ");
sqlcust.AppendLine("Advantages = @Advantages , ");
sqlcust.AppendLine("TradePosition = @TradePosition, ");
sqlcust.AppendLine("Competition = @Competition, ");
sqlcust.AppendLine("Collaborator = @Collaborator, ");
sqlcust.AppendLine("ManagePlan = @ManagePlan, ");
sqlcust.AppendLine("Collaborate = @Collaborate, ");
sqlcust.AppendLine("ModifiedUserID = @ModifiedUserID ");
sqlcust.AppendLine(" WHERE ");
sqlcust.AppendLine("CustNo = @CustNo and CompanyCD = @CompanyCD");
#endregion
#region 设置修改客户信息参数
SqlCommand mycomm = new SqlCommand();
mycomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", CustInfoModel.CompanyCD));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustType", CustInfoModel.CustType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustClass", CustInfoModel.CustClass));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustNo", CustInfoModel.CustNo));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustName", CustInfoModel.CustName));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustNam", CustInfoModel.CustNam));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustShort", CustInfoModel.CustShort));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CreditGrade", CustInfoModel.CreditGrade));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Manager", CustInfoModel.Seller));
mycomm.Parameters.Add(SqlHelper.GetParameter("@AreaID", CustInfoModel.AreaID));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustNote", CustInfoModel.CustNote));
mycomm.Parameters.Add(SqlHelper.GetParameter("@LinkCycle", CustInfoModel.LinkCycle));
mycomm.Parameters.Add(SqlHelper.GetParameter("@HotIs", CustInfoModel.HotIs));
mycomm.Parameters.Add(SqlHelper.GetParameter("@HotHow", CustInfoModel.HotHow));
mycomm.Parameters.Add(SqlHelper.GetParameter("@MeritGrade", CustInfoModel.MeritGrade));
mycomm.Parameters.Add(SqlHelper.GetParameter("@RelaGrade", CustInfoModel.RelaGrade));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Relation", CustInfoModel.Relation));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CompanyType", CustInfoModel.CompanyType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@StaffCount", CustInfoModel.StaffCount));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Source", CustInfoModel.Source));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Phase", CustInfoModel.Phase));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustSupe", CustInfoModel.CustSupe));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Trade", CustInfoModel.Trade));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SetupDate", CustInfoModel.SetupDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.SetupDate.ToString())));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ArtiPerson", CustInfoModel.ArtiPerson));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SetupMoney", CustInfoModel.SetupMoney));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SetupAddress", CustInfoModel.SetupAddress));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CapitalScale", CustInfoModel.CapitalScale));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SaleroomY", CustInfoModel.SaleroomY));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ProfitY", CustInfoModel.ProfitY));
mycomm.Parameters.Add(SqlHelper.GetParameter("@TaxCD", CustInfoModel.TaxCD));
mycomm.Parameters.Add(SqlHelper.GetParameter("@BusiNumber", CustInfoModel.BusiNumber));
mycomm.Parameters.Add(SqlHelper.GetParameter("@IsTax", CustInfoModel.IsTax));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SellMode", CustInfoModel.SellMode));
mycomm.Parameters.Add(SqlHelper.GetParameter("@SellArea", CustInfoModel.SellArea));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CountryID", CustInfoModel.CountryID));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Province", CustInfoModel.Province));
mycomm.Parameters.Add(SqlHelper.GetParameter("@City", CustInfoModel.City));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Tel", CustInfoModel.Tel));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ContactName", CustInfoModel.ContactName));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Mobile", CustInfoModel.Mobile));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ReceiveAddress", CustInfoModel.ReceiveAddress));
mycomm.Parameters.Add(SqlHelper.GetParameter("@WebSite", CustInfoModel.WebSite));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Post", CustInfoModel.Post));
mycomm.Parameters.Add(SqlHelper.GetParameter("@email", CustInfoModel.email));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Fax", CustInfoModel.Fax));
mycomm.Parameters.Add(SqlHelper.GetParameter("@OnLine", CustInfoModel.OnLine));
mycomm.Parameters.Add(SqlHelper.GetParameter("@TakeType", CustInfoModel.TakeType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CarryType", CustInfoModel.CarryType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@BusiType", CustInfoModel.BusiType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@BillType", CustInfoModel.BillType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@PayType", CustInfoModel.PayType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@MoneyType", CustInfoModel.MoneyType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CurrencyType", CustInfoModel.CurrencyType));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CreditManage", CustInfoModel.CreditManage));
mycomm.Parameters.Add(SqlHelper.GetParameter("@MaxCredit", CustInfoModel.MaxCredit));
mycomm.Parameters.Add(SqlHelper.GetParameter("@MaxCreditDate", CustInfoModel.MaxCreditDate));
mycomm.Parameters.Add(SqlHelper.GetParameter("@UsedStatus", CustInfoModel.UsedStatus));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Creator", CustInfoModel.Creator));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CreatedDate", CustInfoModel.CreatedDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.CreatedDate.ToString())));
mycomm.Parameters.Add(SqlHelper.GetParameter("@OpenBank", CustInfoModel.OpenBank));
mycomm.Parameters.Add(SqlHelper.GetParameter("@AccountMan", CustInfoModel.AccountMan));
mycomm.Parameters.Add(SqlHelper.GetParameter("@AccountNum", CustInfoModel.AccountNum));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustBig", CustInfoModel.CustBig));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustNum", CustInfoModel.CustNum));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Remark", CustInfoModel.Remark));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustTypeManage", CustInfoModel.CustTypeManage));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustTypeSell", CustInfoModel.CustTypeSell));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CustTypeTime", CustInfoModel.CustTypeTime));
mycomm.Parameters.Add(SqlHelper.GetParameter("@FirstBuyDate", CustInfoModel.FirstBuyDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.FirstBuyDate.ToString())));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", DateTime.Now));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CanViewUser", CustInfoModel.CanViewUser));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CanViewUserName", CustInfoModel.CanViewUserName));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CompanyValues", CustInfoModel.CompanyValues));
mycomm.Parameters.Add(SqlHelper.GetParameter("@CatchWord", CustInfoModel.CatchWord));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ManageValues", CustInfoModel.ManageValues));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Potential", CustInfoModel.Potential));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Problem", CustInfoModel.Problem));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Advantages", CustInfoModel.Advantages));
mycomm.Parameters.Add(SqlHelper.GetParameter("@TradePosition", CustInfoModel.TradePosition));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Competition", CustInfoModel.Competition));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Collaborator", CustInfoModel.Collaborator));
mycomm.Parameters.Add(SqlHelper.GetParameter("@ManagePlan", CustInfoModel.ManagePlan));
mycomm.Parameters.Add(SqlHelper.GetParameter("@Collaborate", CustInfoModel.Collaborate));
mycomm.CommandText = sqlcust.ToString();
#endregion
ArrayList lstCmd = new ArrayList();
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(CustInfoModel, htExtAttr, cmd);
lstCmd.Add(mycomm);//修改客户基本信息
if (htExtAttr != null)
lstCmd.Add(cmd);//修改客户扩展信息
if (CustInfoModel.CustBig == "2")//当选择会员客户时,修改信息到联系人表
{
SqlCommand cmdLinkMan = new SqlCommand();
StringBuilder sqlLinkMan = new StringBuilder();
sqlLinkMan.AppendLine(" update officedba.CustLinkMan ");
sqlLinkMan.AppendLine(" set LinkManName=@LinkManName,Sex=@Sex,LinkType=@LinkType,PaperNum=@PaperNum,");
sqlLinkMan.AppendLine(" Birthday=@Birthday,WorkTel=@WorkTel,Handset=@Handset,Fax=@Fax,Position=@Position, ");
sqlLinkMan.AppendLine(" Age= @Age,Post=@Post,MailAddress=@MailAddress,HomeTown=@HomeTown,NationalID=@NationalID,CultureLevel=@CultureLevel,");
sqlLinkMan.AppendLine(" Professional=@Professional,IncomeYear=@IncomeYear,FuoodDrink=@FuoodDrink, ");
sqlLinkMan.AppendLine(" LoveMusic=@LoveMusic,LoveColor=@LoveColor,LoveSmoke=@LoveSmoke,LoveDrink=@LoveDrink,LoveTea=@LoveTea,");
sqlLinkMan.AppendLine(" LoveBook=@LoveBook,LoveSport=@LoveSport,LoveClothes=@LoveClothes,Cosmetic=@Cosmetic, ");
sqlLinkMan.AppendLine(" Nature=@Nature,Appearance=@Appearance,AdoutBody=@AdoutBody,AboutFamily=@AboutFamily,Car=@Car,");
sqlLinkMan.AppendLine(" CanViewUser=@CanViewUser,CanViewUserName=@CanViewUserName ");
sqlLinkMan.AppendLine(" WHERE ");
sqlLinkMan.AppendLine(" CustNo = @CustNo and CompanyCD = @CompanyCD ");
sqlLinkMan.AppendLine(" and id=(select min(c.id)id from officedba.CustLinkMan c ");
sqlLinkMan.AppendLine(" where c.CompanyCD = @CompanyCD and c.custno = @CustNo ) ");
#region 设置联系人参数
SqlCommand commLinkMan = new SqlCommand();
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", CustInfoModel.CompanyCD));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CustNo", CustInfoModel.CustNo));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LinkManName", CustInfoModel.CustName));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Sex", LinkManM.Sex));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LinkType", LinkManM.LinkType));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@PaperNum", LinkManM.PaperNum));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Birthday", LinkManM.Birthday == null ? SqlDateTime.Null : SqlDateTime.Parse(LinkManM.Birthday.ToString())));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@WorkTel", LinkManM.WorkTel));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Handset", LinkManM.Handset));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Fax", LinkManM.Fax));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Position", LinkManM.Position));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Age", LinkManM.Age));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Post", LinkManM.Post));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@MailAddress", LinkManM.MailAddress));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@HomeTown", LinkManM.HomeTown));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@NationalID", LinkManM.NationalID));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CultureLevel", LinkManM.CultureLevel));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Professional", LinkManM.Professional));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@IncomeYear", LinkManM.IncomeYear));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@FuoodDrink", LinkManM.FuoodDrink));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveMusic", LinkManM.LoveMusic));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveColor", LinkManM.LoveColor));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveSmoke", LinkManM.LoveSmoke));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveDrink", LinkManM.LoveDrink));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveTea", LinkManM.LoveTea));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveBook", LinkManM.LoveBook));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveSport", LinkManM.LoveSport));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@LoveClothes", LinkManM.LoveClothes));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Cosmetic", LinkManM.Cosmetic));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Nature", LinkManM.Nature));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Appearance", LinkManM.Appearance));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@AdoutBody", LinkManM.AdoutBody));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@AboutFamily", LinkManM.AboutFamily));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@Car", LinkManM.Car));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CanViewUser", CustInfoModel.CanViewUser));
commLinkMan.Parameters.Add(SqlHelper.GetParameter("@CanViewUserName", CustInfoModel.CanViewUserName));
commLinkMan.CommandText = sqlLinkMan.ToString();
#endregion
lstCmd.Add(commLinkMan);
}
//执行登陆操作
bool isSucc = SqlHelper.ExecuteTransWithArrayList(lstCmd);
return isSucc;
}
catch
{
return false;
}
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
try
{
string orderString = hiddExpOrder.Value.Trim();//排序
string order = "asc";//排序：降序
string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "CustNo";//要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_d"))
{
order = "desc";//排序：降序
}
string ord = " ORDER BY " + orderBy + " " + order;
CustInfoModel CustInfoM = new CustInfoModel();
CustInfoM.CustNo = txtCustNo.Value.Trim();
CustInfoM.CustNam = txtCustNam.Value;
CustInfoM.CustClass = hiddCustClass.Value.Trim() == "" ? 0 : Convert.ToInt32(hiddCustClass.Value.Trim());
CustInfoM.CustName = txtCustName.Value.Trim();
CustInfoM.CustShort = txtCustShort.Value.Trim();
CustInfoM.AreaID = ddlArea.SelectedItem.Value == "0" ? 0 : Convert.ToInt32(ddlArea.SelectedItem.Value);
CustInfoM.CreditGrade = ddlCreditGrade.SelectedItem.Value == "0" ? 0 : Convert.ToInt32(ddlCreditGrade.SelectedItem.Value);
CustInfoM.RelaGrade = seleRelaGrade.Value;
CustInfoM.UsedStatus = seleUsedStatus.Value;
CustInfoM.Tel = txtTel.Value.Trim();
string Manager = txtManager.Value.Trim();
string CreatedBegin = txtCreatedBegin.Value.Trim();
string CreatedEnd = txtCreatedEnd.Value.Trim();
CustInfoM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
string CanUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID.ToString();//当前用户ID
DataTable dt = CustInfoBus.ExportCustInfo(CustInfoM, Manager, CanUserID, CreatedBegin, CreatedEnd, ord);
//OutputToExecl.ExportToTableFormat(this, dt,
// new string[] { "客户编号", "客户名称", "客户大类", "拼音缩写", "客户细分", "客户类别", "所在区域", "分管业务员", "客户优质级别", "客户关系等级", "创建人", "创建日期", "启用状态" },
// new string[] { "CustNo", "CustName", "CustBigName", "CustShort", "CodeName", "TypeName", "Area", "Manager", "CreditGrade", "RelaGradeName", "Creator", "CreatedDate", "UsedStatusName" },
// "客户档案列表");
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "客户编号", "客户名称", "客户大类", "客户简称", "拼音缩写","客户管理分类",
"客户营销分类","客户优质级别","客户时间分类","客户细分", "客户类别","建档人", "建档日期",
"客户简介","国家地区","区域","省","市(县)","业务类型","分管业务员","联系人","电话",
"手机","传真","在线咨询","公司网址","邮编","电子邮件","首次交易日期","运送方式","交货方式",
"联络期限(天)","收货地址","经营范围","允许延期付款","信用额度(元)","帐期天数(天)","结算方式",
"结算币种","发票类型","支付方式","开户行","户名","账号","单位性质","资产规模(万元)",
"成立时间","注册资本(万元)","员工总数(个)","法人代表","行业","营业执照号","注册地址",
"税务登记号","是否为一般纳税人","客户来源","年销售额(万元)","年利润额(万元)","销售模式",
"上级客户","价值评估","阶段","热点客户","热度","关系等级","启用状态","关系描述","备注",
"最后更新用户","最后更新日期","经营理念","企业口号","企业文化概述","发展潜力","存在问题",
"市场优劣势","行业地位","竞争对手","合作伙伴","发展计划","合作方法","可查看该客户档案的人员"
},
new string[] { "CustNo", "CustName", "BigType", "CustNam", "CustShort", "CustTypeManage",
"CustTypeSell", "CreditGradeNm", "CustTypeTime","CustClassName", "CustTypaNm","CreatorName","CreatedDate",
"CustNote","CountryName","AreaName","Province","City","BusiType","ManagerName","ContactName","Tel",
"Mobile","Fax","OnLine","WebSite","Post","email","FirstBuyDate","CarryTypeNm","TakeTypeNm",
"LinkCycleNm","ReceiveAddress","SellArea","CreditManage","MaxCredit","MaxCreditDate","PayTypeNm",
"CurrencyaNm","BillTypeNm","MoneyTypeNm","OpenBank","AccountMan","AccountNum","CompanyType","CapitalScale",
"SetupDate","SetupMoney","StaffCount","ArtiPerson","Trade","BusiNumber","SetupAddress",
"TaxCD","IsTax","Source","SaleroomY","ProfitY","SellMode",
"CustSupe","MeritGrade","Phase","HotIs","HotHow","RelaGrade","UsedStatus","Relation","Remark",
"ModifiedUserID","ModifiedDate","CompanyValues","CatchWord","ManageValues","Potential","Problem",
"Advantages","TradePosition","Competition","Collaborator","ManagePlan","Collaborate","CanViewUserName"
},
"客户档案列表");
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "Exp", "<script language=javascript>showPopup('../../../Images/Pic/Close.gif','../../../Images/Pic/note.gif','导出发生异常');</script>");
}
}
public static DataTable GetCust_jlByCon(CustInfoModel CustInfoM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.ID,convert(varchar(10),a.AcceDate,23) AcceDate,a.TotalPrice, ");
strSql.Append(" (case a.AcceWay when '0' then '现金' else '银行存款' end) AcceWay, ");
strSql.Append(" ( case b.InvoiceType when '1' then '增值税发票' when '2' then '普通地税' ");
strSql.Append(" when '3' then '普通国税' when '4' then '收据' end)InvoiceType, ");
strSql.Append(" b.BillingNum,convert(varchar(10),b.CreateDate,23) CreateDate,a.ModifiedDate ");
strSql.Append(" from officedba.IncomeBill a ");
strSql.Append(" left join Officedba.Billing b on b.id = a.BillingID ");
strSql.Append(" where a.CompanyCD = @CompanyCD and a.ConfirmStatus = '1' ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustInfoM.CompanyCD));
#endregion
if (CustInfoM.ID != 0)
{
strSql.Append(" and a.CustID = @CustID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustInfoM.ID.ToString()));
}
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
public static DataTable GetCust_jhByCon(CustInfoModel CustInfoM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
string strCompanyCD = string.Empty;//单位编号
int eid = 0;
//strCompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
eid = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
string strSql = "SELECT s.ID, s.ChanceNo,s.ModifiedDate, s.Title, CONVERT(varchar(100), s.FindDate, 23) AS FindDate, ";
strSql += "ISNULL(c.CustName, ' ') AS CustName, ISNULL(cpt.TypeName, ' ') ";
strSql += "AS ChanceTypeName, ISNULL(cpt1.TypeName, ' ') AS HapSourceName, ";
strSql += "ISNULL(e.EmployeeName, ' ') AS EmployeeName, ";
strSql += "CASE sp.Phase WHEN '1' THEN '初期沟通' WHEN '2' THEN '立项评估' ";
strSql += "WHEN '3' THEN '需求分析' WHEN '4' THEN '方案指定' WHEN '5' THEN '招投标/竞争' ";
strSql += "WHEN '6' THEN '商务谈判' WHEN '7' THEN '合同签约' END AS PhaseName, ";
strSql += "CASE sp.State WHEN '1' THEN '跟踪' WHEN '2' THEN '成功' WHEN '3' ";
strSql += "THEN '失败' WHEN '4' THEN '搁置' WHEN '5' THEN '失效' END AS StateName , ";
strSql += "isnull(CASE(SELECT count(1) FROM officedba.SellOffer AS sb ";
strSql += "WHERE sb.FromType = '1' AND sb.FromBillID = s.ID) WHEN 0 THEN '不存在' END, '存在') AS FromBillText ";
strSql += "FROM officedba.SellChance AS s LEFT OUTER JOIN ";
strSql += "(SELECT scp.ID, scp.CompanyCD, scp.ChanceNo, scp.PushDate, scp.Seller, ";
strSql += "scp.Phase, scp.State, scp.Feasibility, scp.DelayDate, scp.Remark, ";
strSql += "scp.ModifiedDate, scp.ModifiedUserID ";
strSql += " from (SELECT MAX(Phase) AS Phase, ChanceNo,CompanyCD FROM officedba.SellChancePush ";
strSql += " GROUP BY ChanceNo,CompanyCD) AS scp1 ";
strSql += " left join ( SELECT ID, CompanyCD, ChanceNo, PushDate, Seller,";
strSql += " Phase, State, Feasibility, DelayDate, Remark, ModifiedDate, ModifiedUserID ";
strSql += " from officedba.SellChancePush ) as scp ";
strSql += " ON scp.ChanceNo = scp1.ChanceNo and scp.CompanyCD = scp1.CompanyCD AND scp.Phase = scp1.Phase ";
strSql += " WHERE scp1.CompanyCD = @CompanyCD ";
strSql += ") AS sp ON s.ChanceNo = sp.ChanceNo AND s.CompanyCD = sp.CompanyCD LEFT OUTER JOIN ";
strSql += "officedba.CustInfo AS c ON s.CustID = c.ID AND s.CompanyCD = c.CompanyCD LEFT OUTER JOIN ";
strSql += "officedba.EmployeeInfo AS e ON s.Seller = e.ID AND s.CompanyCD = e.CompanyCD LEFT OUTER JOIN ";
strSql += "officedba.CodePublicType AS cpt ON s.ChanceType = cpt.ID AND s.CompanyCD = cpt.CompanyCD LEFT OUTER JOIN ";
strSql += "officedba.CodePublicType AS cpt1 ON s.HapSource = cpt1.ID AND s.CompanyCD = cpt1.CompanyCD ";
strSql += " WHERE (s.CompanyCD = @CompanyCD) ";
strSql += " AND (CHARINDEX('," + eid + ",',','+s.CanViewUser+',')>0 OR s.CanViewUser='' or s.CanViewUser is null OR s.Creator=" + eid + ") ";
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustInfoM.CompanyCD));
#endregion
if (CustInfoM.ID != 0)
{
strSql += " and s.CustID = @CustID ";
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustInfoM.ID.ToString()));
}
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
/// <summary>
/// 导出客户列表信息
/// </summary>
/// <param name="CustModel"></param>
/// <param name="Manager"></param>
/// <param name="CanUserID"></param>
/// <param name="CreatedBegin"></param>
/// <param name="CreatedEnd"></param>
/// <param name="ord"></param>
/// <returns></returns>
public static DataTable ExportCustInfo(CustInfoModel CustModel, string Manager, string CanUserID, string CreatedBegin, string CreatedEnd, string ord)
{
try
{
#region sql
//string sql = " select distinct " +
// " ci.id, ci.CustNo,ci.CustName,ci.CustNam,ci.CustShort, " +
// " ci.CustClass,isnull(cc.CodeName,'') CodeName," +
// " (case ci.CustBig when '2' then '会员' else '企业' end)CustBigName, " +
// " ci.CustType,isnull(cp.TypeName,'') TypeName," +
// " ci.AreaID,isnull(cpa.TypeName,'') Area," +
// " isnull(ei.EmployeeName,'') Manager," +
// " isnull(cpc.TypeName,'') CreditGrade," +
// " ci.RelaGrade,(case ci.RelaGrade when '1' then '密切' when '2' then '较好' when '3' then '一般' when '4' then '较差' end) RelaGradeName," +
// "isnull(eic.EmployeeName,'') Creator," +
// " CONVERT(varchar(100), ci.CreatedDate, 23) CreatedDate,ci.UsedStatus, " +
// " (case ci.UsedStatus when '1' then '启用' when '0' then '停用' end) UsedStatusName" +
// " from " +
// " officedba.CustInfo ci " +
// " left join officedba.CodeCompanyType cc on cc.id = ci.CustClass " +
// " left join officedba.EmployeeInfo ei on ei.id = ci.Manager " +
// " left join officedba.CodePublicType cpa on cpa.id = ci.AreaID" +
// " left join officedba.CodePublicType cpc on cpc.id = ci.CreditGrade" +
// " left join officedba.CodePublicType cp on cp.id = ci.CustType" +
// " left join officedba.EmployeeInfo eic on eic.id = ci.Creator" +
// " left join officedba.CustLinkMan cl on ci.custno = cl.custno " +
// " where ci.CompanyCD = '" + CustModel.CompanyCD + "'" +
// " and (ci.CanViewUser like '%" + "," + CanUserID + "," + "%' or '" + CanUserID + "' = ci.Creator or '" + CanUserID + "' = ci.Manager or ci.CanViewUser = '******')";
#endregion
#region
string sql = "SELECT ci.ID, " +
" ci.CompanyCD,(case ci.CustBig when '2' then '会员' else '企业' end)BigType,ci.CustNo,ci.CustName,ci.CustNum, " +
" ci.CustClass,cct.CodeName CustClassName,ci.CustType,ctt.TypeName CustTypaNm, " +
" (case ci.CustTypeManage when '1' then 'VIP客户' when '2' then '主要客户' when '3' then '普通客户' when '4' then '临时客户' end) CustTypeManage, " +
" (case ci.CustTypeSell when '1' then '经济型客户' when '2' then '个性化客户' when '3' then '方便型客户' when '4' then '道德型客户' end)CustTypeSell , " +
" (case ci.CustTypeTime when '1' then '老客户' when '2' then '新客户' when '3' then '潜在客户' end) CustTypeTime, " +
" ci.CreditGrade,cp.TypeName CreditGradeNm,ci.CustShort,ci.CustNote,ci.Province,ci.AreaID,ci.ReceiveAddress, " +
" (case ci.BusiType when '1' then '普通销售' when '2' then '委托代销' when '3' then '直运' when '4' then '零售' when '5' then '销售调拨' end) BusiType, " +
" ci.Manager,em.EmployeeName ManagerName,ci.LinkCycle,cpl.TypeName LinkCycleNm,ci.Creator,ei.EmployeeName CreatorName, " +
" (case ci.CreditManage when '1' then '否' when '2' then '是' end) CreditManage,ci.ModifiedUserID, " +
" (case ci.RelaGrade when '1' then '密切' when '2' then '较好' when '3' then '一般' when '4' then '较差' end)RelaGrade, " +
" ci.MaxCreditDate,ci.PayType,cpp.TypeName PayTypeNm,ci.CanViewUser,ci.CanViewUserName, " +
" (case ci.UsedStatus when '0' then '停用' when '1' then '启用' end) UsedStatus, " +
" CONVERT(varchar(100), ci.CreatedDate, 23) CreatedDate, " +
"CONVERT(varchar(100), ci.ModifiedDate, 23) ModifiedDate, " +
" a.TypeName LinkTypeName, " +
" b.TypeName NationalName, c.TypeName CultureLevelName, d.TypeName ProfessionalName," +
" CONVERT(varchar(100), ci.FirstBuyDate, 23) FirstBuyDate ,ci.CountryID,cpc.TypeName CountryName, " +
" ci.BigType,ci.CustNam,cpa.TypeName AreaName,ci.City,ci.Tel,ci.Fax,ci.OnLine,ci.WebSite,ci.Post, " +
" (case ci.HotIs when '1' then '是' when '2' then '否' end) HotIs, " +
" (case ci.HotHow when '1' then '低热' when '2' then '中热' when '3' then '高热' end)HotHow, " +
" (case ci.MeritGrade when '1' then '高' when '2' then '中' when '3' then '低' end)MeritGrade,ci.Relation, " +
" (case ci.CompanyType when '1' then '事业' when '2' then '企业' when '3' then '社团' when '4' then '自然人' when '5' then '其他' end) CompanyType, " +
" ci.StaffCount,ci.Source,ci.Phase,ci.CustSupe,ci.Trade,CONVERT(varchar(100), ci.SetupDate, 23) SetupDate, " +
" ci.ArtiPerson,ci.SetupMoney,ci.SetupAddress,ci.CapitalScale,ci.SaleroomY,ci.ProfitY,ci.TaxCD,ci.BusiNumber," +
" (case ci.IsTax when '0' then '否' when '1' then '是' end) IsTax,ci.SellArea,ci.SellMode,ci.ContactName,ci.Mobile,ci.email, " +
" ci.TakeType,cpt.TypeName TakeTypeNm, ci.CarryType,cpca.TypeName CarryTypeNm, " +
" (case ci.BillType when '1' then '增值税发票' when '2' then '普通地税' when '3' then '普通国税' when '4' then '收据' end) BillTypeNm, " +
" ci.MoneyType,cpm.TypeName MoneyTypeNm, ci.CurrencyType,cts.CurrencyName CurrencyaNm, " +
" ci.MaxCredit,ci.OpenBank, ci.AccountMan,ci.AccountNum, " +
" ci.ExtField1,ci.ExtField2,ci.ExtField3,ci.ExtField4,ci.ExtField5,ci.ExtField6,ci.ExtField7,ci.ExtField8,ci.ExtField9,ci.ExtField10, " +
" ci.ExtField11,ci.ExtField12,ci.ExtField13,ci.ExtField14,ci.ExtField15,ci.ExtField16,ci.ExtField17,ci.ExtField18,ci.ExtField19,ci.ExtField20, " +
" ci.ExtField21,ci.ExtField22,ci.ExtField23,ci.ExtField24,ci.ExtField25,ci.ExtField26,ci.ExtField27,ci.ExtField28,ci.ExtField29,ci.ExtField30, " +
" ci.Remark,ci.CompanyValues,ci.CatchWord,ci.ManageValues,ci.Potential,ci.Problem,ci.Advantages,ci.TradePosition," +
" ci.Competition,ci.Collaborator,ci.ManagePlan,ci.Collaborate" +
" from " +
" officedba.CustInfo ci " +
" left join officedba.CustLinkMan cl on cl.CustNo = ci.CustNo and cl.CompanyCD = ci.CompanyCD " +
" and cl.id=(select min(id) from officedba.CustLinkMan ) " +
" left join officedba.CodePublicType ctt on ctt.id = ci.CustType " +
" left join officedba.EmployeeInfo ei on ei.id = ci.Creator " +
" left join officedba.EmployeeInfo em on em.id = ci.Manager " +
" left join officedba.CodeCompanyType cct on cct.id = ci.CustClass " +
" left join officedba.CodePublicType cp on cp.id = ci.CreditGrade " +
" left join officedba.CodePublicType cpa on cpa.id = ci.AreaID " +
" left join officedba.CodePublicType cpl on cpl.id = ci.LinkCycle " +
" left join officedba.CodePublicType cpc on cpc.id = ci.CountryID " +
" left join officedba.CodePublicType a on a.id = cl.LinkType " +
" left join officedba.CodePublicType b on b.id = cl.NationalID " +
" left join officedba.CodePublicType c on c.id = cl.CultureLevel " +
" left join officedba.CodePublicType d on d.id = cl.Professional " +
" left join officedba.CodePublicType cpt on cpt.id = ci.TakeType " +
" left join officedba.CodePublicType cpca on cpca.id = ci.CarryType " +
" left join officedba.CodePublicType cpp on cpp.id = ci.PayType " +
" left join officedba.CodePublicType cpm on cpm.id = ci.MoneyType " +
" left join officedba.CurrencyTypeSetting cts on cts.id = ci.CurrencyType " +
" where ci.CompanyCD='" + CustModel.CompanyCD + "'";
#endregion
if (CustModel.CustNo != "")
sql += " and ci.CustNo like '%" + CustModel.CustNo + "%'";
if (CustModel.CustNam != "")
sql += " and ci.CustNam like '%" + CustModel.CustNam + "%'";
if (CustModel.CustClass != 0)
sql += " and ci.CustClass='" + CustModel.CustClass + "'";
if (CustModel.CustName != "")
sql += " and ci.CustName like '%" + CustModel.CustName + "%'";
if (CustModel.Tel != "")
sql += " and (ci.Tel like '%" + CustModel.Tel + "%' or cl.worktel like '%" + CustModel.Tel + "%')";
if (CustModel.CustShort != "")
sql += " and ci.CustShort like '%" + CustModel.CustShort + "%'";
if (CustModel.AreaID != 0)
sql += " and ci.AreaID='" + CustModel.AreaID + "'";
if (CustModel.CreditGrade != 0)
sql += " and ci.CreditGrade='" + CustModel.CreditGrade + "'";
if (CustModel.RelaGrade != "0")
sql += " and ci.RelaGrade='" + CustModel.RelaGrade + "'";
if (Manager != "")
sql += " and ei.EmployeeName like '%" + Manager + "%'";
if (CreatedBegin.ToString() != "")
sql += " and ci.CreatedDate >= '" + CreatedBegin.ToString() + "'";
if (CreatedEnd.ToString() != "")
sql += " and ci.CreatedDate <= '" + CreatedEnd.ToString() + "'";
if (CustModel.UsedStatus != "-1")
sql += " and ci.UsedStatus = '" + CustModel.UsedStatus + "'";
sql += ord;
//return SqlHelper.CreateSqlByPageExcuteSql(sql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount);
return SqlHelper.ExecuteSql(sql);
}
catch
{
return null;
}
}
public static DataTable GetCust_lxrByCon(CustInfoModel CustInfoM, string DateBegin, string DateEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.ID,a.LinkManName,b.CustName,c.TypeName LinkTypeName,a.ModifiedDate, ");
strSql.Append(" (case a.Important when '1' then '不重要' when '2' then '普通' ");
strSql.Append(" when '3' then '重要' when '4' then '关键' else '' end) Important,");
strSql.Append(" a.WorkTel,a.Handset,a.Fax,a.HomeTel,a.QQ,Convert(varchar(100),a.Birthday,23) Birthday");
strSql.Append(" from officedba.CustLinkMan a");
strSql.Append(" left join officedba.custinfo b on b.custno = a.custno and a.companycd = b.companycd");
strSql.Append(" left join officedba.CodePublicType c on c.id = a.LinkType");
strSql.Append(" where a.CompanyCD = @CompanyCD ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustInfoM.CompanyCD));
#endregion
if (CustInfoM.CustNo != "")
{
strSql.Append(" and a.CustNo = @CustNo ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustNo", CustInfoM.CustNo));
}
if (!string.IsNullOrEmpty(CustInfoM.Tel))
{
strSql.Append(" and ((a.CustNo in (select z.CustNo from officedba.custinfo z where CompanyCD = @CompanyCD ");
strSql.Append(" and( z.Tel like @Tel or z.Mobile like @Tel or z.Fax like @Tel)))");
strSql.Append(" or (a.WorkTel like @Tel or a.Fax like @Tel or a.Handset like @Tel");
strSql.Append(" or a.HomeTel like @Tel)");
strSql.Append(" ) ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Tel", "%" + CustInfoM.Tel + "%"));
}
if (!string.IsNullOrEmpty(DateBegin))
{
strSql.AppendLine(" and a.CreatedDate >= @DateBegin");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DateBegin", DateBegin));
}
if (!string.IsNullOrEmpty(DateEnd))
{
strSql.AppendLine(" and a.CreatedDate <= @DateEnd");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DateEnd", DateEnd));
}
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
/// <summary>
/// 同时添加客户信息及联系人列表信息的方法
/// </summary>
/// <param name="CustInfoModel">客户信息Model</param>
/// <param name="LinkManlist">联系人列表信息流</param>
/// <returns>bool值</returns>
public static bool AddCustAndLinkMan(CustInfoModel CustInfoModel, string LinkManlist)
{
try
{
#region 拼写添加客户信息SQL语句
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO officedba.CustInfo");
sql.AppendLine("(CompanyCD");
sql.AppendLine(",CustType");
sql.AppendLine(",CustClass ");
sql.AppendLine(",CustNo ");
sql.AppendLine(",CustName ");
sql.AppendLine(",CustNam ");
sql.AppendLine(",CustShort ");
sql.AppendLine(",CreditGrade ");
sql.AppendLine(",Manager ");
sql.AppendLine(",AreaID ");
sql.AppendLine(",CustNote ");
sql.AppendLine(",LinkCycle ");
sql.AppendLine(",HotIs ");
sql.AppendLine(",HotHow ");
sql.AppendLine(",HotType ");
sql.AppendLine(",MeritGrade ");
sql.AppendLine(",RelaGrade ");
sql.AppendLine(",Relation ");
sql.AppendLine(",CompanyType ");
sql.AppendLine(",StaffCount ");
sql.AppendLine(",Source ");
sql.AppendLine(",Phase ");
sql.AppendLine(",CustSupe ");
sql.AppendLine(",Trade ");
sql.AppendLine(",SetupDate ");
sql.AppendLine(",ArtiPerson ");
sql.AppendLine(",SetupMoney ");
sql.AppendLine(",SetupAddress ");
sql.AppendLine(",CapitalScale ");
sql.AppendLine(",SaleroomY ");
sql.AppendLine(",ProfitY ");
sql.AppendLine(",TaxCD ");
sql.AppendLine(",BusiNumber ");
sql.AppendLine(",IsTax ");
sql.AppendLine(",SellMode ");
sql.AppendLine(",SellArea ");
sql.AppendLine(",CountryID ");
sql.AppendLine(",Province ");
sql.AppendLine(",City ");
sql.AppendLine(",Tel ");
sql.AppendLine(",ContactName ");
sql.AppendLine(",Mobile ");
sql.AppendLine(",ReceiveAddress ");
sql.AppendLine(",WebSite ");
sql.AppendLine(",Post ");
sql.AppendLine(",email ");
sql.AppendLine(",Fax ");
sql.AppendLine(",OnLine ");
sql.AppendLine(",TakeType ");
sql.AppendLine(",CarryType ");
sql.AppendLine(",BusiType ");
sql.AppendLine(",BillType ");
sql.AppendLine(",PayType ");
sql.AppendLine(",MoneyType ");
sql.AppendLine(",CurrencyType ");
sql.AppendLine(",CreditManage ");
sql.AppendLine(",MaxCredit ");
sql.AppendLine(",MaxCreditDate ");
//sql.AppendLine(",UpdateCredit ");
sql.AppendLine(",UsedStatus ");
sql.AppendLine(",Creator ");
sql.AppendLine(",CreatedDate ");
sql.AppendLine(",OpenBank ");
sql.AppendLine(",AccountMan ");
sql.AppendLine(",AccountNum ");
sql.AppendLine(",Remark) ");
sql.AppendLine(" values ");
sql.AppendLine("(@CompanyCD");
sql.AppendLine(",@CustType");
sql.AppendLine(",@CustClass ");
sql.AppendLine(",@CustNo ");
sql.AppendLine(",@CustName ");
sql.AppendLine(",@CustNam ");
sql.AppendLine(",@CustShort ");
sql.AppendLine(",@CreditGrade ");
sql.AppendLine(",@Manager ");
sql.AppendLine(",@AreaID ");
sql.AppendLine(",@CustNote ");
sql.AppendLine(",@LinkCycle ");
sql.AppendLine(",@HotIs ");
sql.AppendLine(",@HotHow ");
sql.AppendLine(",@HotType ");
sql.AppendLine(",@MeritGrade ");
sql.AppendLine(",@RelaGrade ");
sql.AppendLine(",@Relation ");
sql.AppendLine(",@CompanyType ");
sql.AppendLine(",@StaffCount ");
sql.AppendLine(",@Source ");
sql.AppendLine(",@Phase ");
sql.AppendLine(",@CustSupe ");
sql.AppendLine(",@Trade ");
sql.AppendLine(",@SetupDate ");
sql.AppendLine(",@ArtiPerson ");
sql.AppendLine(",@SetupMoney ");
sql.AppendLine(",@SetupAddress ");
sql.AppendLine(",@CapitalScale ");
sql.AppendLine(",@SaleroomY ");
sql.AppendLine(",@ProfitY ");
sql.AppendLine(",@TaxCD ");
sql.AppendLine(",@BusiNumber ");
sql.AppendLine(",@IsTax ");
sql.AppendLine(",@SellMode ");
sql.AppendLine(",@SellArea ");
sql.AppendLine(",@CountryID ");
sql.AppendLine(",@Province ");
sql.AppendLine(",@City ");
sql.AppendLine(",@Tel ");
sql.AppendLine(",@ContactName ");
sql.AppendLine(",@Mobile ");
sql.AppendLine(",@ReceiveAddress ");
sql.AppendLine(",@WebSite ");
sql.AppendLine(",@Post ");
sql.AppendLine(",@email ");
sql.AppendLine(",@Fax ");
sql.AppendLine(",@OnLine ");
sql.AppendLine(",@TakeType ");
sql.AppendLine(",@CarryType ");
sql.AppendLine(",@BusiType ");
sql.AppendLine(",@BillType ");
sql.AppendLine(",@PayType ");
sql.AppendLine(",@MoneyType ");
sql.AppendLine(",@CurrencyType ");
sql.AppendLine(",@CreditManage ");
sql.AppendLine(",@MaxCredit ");
sql.AppendLine(",@MaxCreditDate ");
//sql.AppendLine(",@UpdateCredit ");
sql.AppendLine(",@UsedStatus ");
sql.AppendLine(",@Creator ");
sql.AppendLine(",@CreatedDate ");
sql.AppendLine(",@OpenBank ");
sql.AppendLine(",@AccountMan ");
sql.AppendLine(",@AccountNum ");
sql.AppendLine(",@Remark) ");
#endregion
#region 设置添加客户信息参数
SqlParameter[] param = new SqlParameter[65];
param[0] = SqlHelper.GetParameter("@CompanyCD", CustInfoModel.CompanyCD);
param[1] = SqlHelper.GetParameter("@CustType", CustInfoModel.CustType);
param[2] = SqlHelper.GetParameter("@CustClass", CustInfoModel.CustClass);
param[3] = SqlHelper.GetParameter("@CustNo", CustInfoModel.CustNo);
param[4] = SqlHelper.GetParameter("@CustName", CustInfoModel.CustName);
param[5] = SqlHelper.GetParameter("@CustNam", CustInfoModel.CustNam);
param[6] = SqlHelper.GetParameter("@CustShort", CustInfoModel.CustShort);
param[7] = SqlHelper.GetParameter("@CreditGrade", CustInfoModel.CreditGrade);
param[8] = SqlHelper.GetParameter("@Manager", CustInfoModel.Seller);
param[9] = SqlHelper.GetParameter("@AreaID", CustInfoModel.AreaID);
param[10] = SqlHelper.GetParameter("@CustNote", CustInfoModel.CustNote);
param[11] = SqlHelper.GetParameter("@LinkCycle", CustInfoModel.LinkCycle);
param[12] = SqlHelper.GetParameter("@HotIs", CustInfoModel.HotIs);
param[13] = SqlHelper.GetParameter("@HotHow", CustInfoModel.HotHow);
param[14] = SqlHelper.GetParameter("@HotType", CustInfoModel.HotType);
param[15] = SqlHelper.GetParameter("@MeritGrade", CustInfoModel.MeritGrade);
param[16] = SqlHelper.GetParameter("@RelaGrade", CustInfoModel.RelaGrade);
param[17] = SqlHelper.GetParameter("@Relation", CustInfoModel.Relation);
param[18] = SqlHelper.GetParameter("@CompanyType", CustInfoModel.CompanyType);
param[19] = SqlHelper.GetParameter("@StaffCount", CustInfoModel.StaffCount);
param[20] = SqlHelper.GetParameter("@Source", CustInfoModel.Source);
param[21] = SqlHelper.GetParameter("@Phase", CustInfoModel.Phase);
param[22] = SqlHelper.GetParameter("@CustSupe", CustInfoModel.CustSupe);
param[23] = SqlHelper.GetParameter("@Trade", CustInfoModel.Trade);
param[24] = SqlHelper.GetParameter("@SetupDate", CustInfoModel.SetupDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.SetupDate.ToString()));
param[25] = SqlHelper.GetParameter("@ArtiPerson", CustInfoModel.ArtiPerson);
param[26] = SqlHelper.GetParameter("@SetupMoney", CustInfoModel.SetupMoney);
param[27] = SqlHelper.GetParameter("@SetupAddress", CustInfoModel.SetupAddress);
param[28] = SqlHelper.GetParameter("@CapitalScale", CustInfoModel.CapitalScale);
param[29] = SqlHelper.GetParameter("@SaleroomY", CustInfoModel.SaleroomY);
param[30] = SqlHelper.GetParameter("@ProfitY", CustInfoModel.ProfitY);
param[31] = SqlHelper.GetParameter("@TaxCD", CustInfoModel.TaxCD);
param[32] = SqlHelper.GetParameter("@BusiNumber", CustInfoModel.BusiNumber);
param[33] = SqlHelper.GetParameter("@IsTax", CustInfoModel.IsTax);
param[34] = SqlHelper.GetParameter("@SellMode", CustInfoModel.SellMode);
param[35] = SqlHelper.GetParameter("@SellArea", CustInfoModel.SellArea);
param[36] = SqlHelper.GetParameter("@CountryID", CustInfoModel.CountryID);
param[37] = SqlHelper.GetParameter("@Province", CustInfoModel.Province);
param[38] = SqlHelper.GetParameter("@City", CustInfoModel.City);
param[39] = SqlHelper.GetParameter("@Tel", CustInfoModel.Tel);
param[40] = SqlHelper.GetParameter("@ContactName", CustInfoModel.ContactName);
param[41] = SqlHelper.GetParameter("@Mobile", CustInfoModel.Mobile);
param[42] = SqlHelper.GetParameter("@ReceiveAddress", CustInfoModel.ReceiveAddress);
param[43] = SqlHelper.GetParameter("@WebSite", CustInfoModel.WebSite);
param[44] = SqlHelper.GetParameter("@Post", CustInfoModel.Post);
param[45] = SqlHelper.GetParameter("@email", CustInfoModel.email);
param[46] = SqlHelper.GetParameter("@Fax", CustInfoModel.Fax);
param[47] = SqlHelper.GetParameter("@OnLine", CustInfoModel.OnLine);
param[48] = SqlHelper.GetParameter("@TakeType", CustInfoModel.TakeType);
param[49] = SqlHelper.GetParameter("@CarryType", CustInfoModel.CarryType);
param[50] = SqlHelper.GetParameter("@BusiType", CustInfoModel.BusiType);
param[51] = SqlHelper.GetParameter("@BillType", CustInfoModel.BillType);
param[52] = SqlHelper.GetParameter("@PayType", CustInfoModel.PayType);
param[53] = SqlHelper.GetParameter("@MoneyType", CustInfoModel.MoneyType);
param[54] = SqlHelper.GetParameter("@CurrencyType", CustInfoModel.CurrencyType);
param[55] = SqlHelper.GetParameter("@CreditManage", CustInfoModel.CreditManage);
param[56] = SqlHelper.GetParameter("@MaxCredit", CustInfoModel.MaxCredit);
param[57] = SqlHelper.GetParameter("@MaxCreditDate", CustInfoModel.MaxCreditDate);
param[58] = SqlHelper.GetParameter("@UsedStatus", CustInfoModel.UsedStatus);
param[59] = SqlHelper.GetParameter("@Creator", CustInfoModel.Creator);
param[60] = SqlHelper.GetParameter("@CreatedDate", CustInfoModel.CreatedDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(CustInfoModel.CreatedDate.ToString()));
param[61] = SqlHelper.GetParameter("@OpenBank", CustInfoModel.OpenBank);
param[62] = SqlHelper.GetParameter("@AccountMan", CustInfoModel.AccountMan);
param[63] = SqlHelper.GetParameter("@AccountNum", CustInfoModel.AccountNum);
param[64] = SqlHelper.GetParameter("@Remark", CustInfoModel.Remark);
#endregion
LinkManModel LinkManM = new LinkManModel();
string[] strlinkman = LinkManlist.Split('|'); //把联系人列表流分隔成数组
SqlCommand[] comms = new SqlCommand[strlinkman.Length]; //申明cmd数组
SqlCommand cmdcust = new SqlCommand(sql.ToString()); //未执行的客户信息添加命令
cmdcust.Parameters.AddRange(param);
comms[0] = cmdcust; //把未执行的客户信息添加命令给cmd数组第一项
string recorditems = "";
string[] linkmanfield = null;
for (int i = 1; i < strlinkman.Length; i++) //循环数组
{
recorditems = strlinkman[i].ToString();//取到每一条记录:[序号,联系人姓名,手机,工作电话,职位,负责业务]
linkmanfield = recorditems.Split(','); //把每条记录分隔到字段
string fieldxh = linkmanfield[0].ToString();//序号
string fieldname = linkmanfield[1].ToString();//联系人姓名
string fieldhandset = linkmanfield[2].ToString();//手机
string fieldworktel = linkmanfield[3].ToString();//工作电话
string fieldposition = linkmanfield[4].ToString();//职务
string fieldoperation = linkmanfield[5].ToString();//负责业务
LinkManM.CompanyCD = CustInfoModel.CompanyCD; //联系人信息赋予一个LinkManM(联系人Model对象实例)
LinkManM.CustNo = CustInfoModel.CustNo;
LinkManM.LinkManName = fieldname;
LinkManM.Handset = fieldhandset;
LinkManM.WorkTel = fieldworktel;
LinkManM.Position = fieldposition;
LinkManM.Operation = fieldoperation;
LinkManM.ModifiedDate = DateTime.Now;
LinkManM.ModifiedUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;//Session读取当前用户ID
#region 拼写添加联系人信息sql语句
StringBuilder sqllinkman = new StringBuilder();
sqllinkman.AppendLine("INSERT INTO officedba.CustLinkMan");
sqllinkman.AppendLine("(CompanyCD");
sqllinkman.AppendLine(",CustNo ");
sqllinkman.AppendLine(",LinkManName");
sqllinkman.AppendLine(",Position ");
sqllinkman.AppendLine(",Operation ");
sqllinkman.AppendLine(",WorkTel ");
sqllinkman.AppendLine(",Handset ");
sqllinkman.AppendLine(",ModifiedDate");
sqllinkman.AppendLine(",ModifiedUserID)");
sqllinkman.AppendLine(" values ");
sqllinkman.AppendLine("(@CompanyCD");
sqllinkman.AppendLine(",@CustNo ");
sqllinkman.AppendLine(",@LinkManName");
sqllinkman.AppendLine(",@Position ");
sqllinkman.AppendLine(",@Operation ");
sqllinkman.AppendLine(",@WorkTel ");
sqllinkman.AppendLine(",@Handset ");
sqllinkman.AppendLine(",@ModifiedDate");
sqllinkman.AppendLine(",@ModifiedUserID)");
#endregion
#region 设置参数
SqlParameter[] paramlinkman = new SqlParameter[9];
paramlinkman[0] = SqlHelper.GetParameter("@CompanyCD", CustInfoModel.CompanyCD);
paramlinkman[1] = SqlHelper.GetParameter("@CustNo", CustInfoModel.CustNo);
paramlinkman[2] = SqlHelper.GetParameter("@LinkManName", LinkManM.LinkManName);
paramlinkman[3] = SqlHelper.GetParameter("@Position", LinkManM.Position);
paramlinkman[4] = SqlHelper.GetParameter("@Operation", LinkManM.Operation);
paramlinkman[5] = SqlHelper.GetParameter("@WorkTel", LinkManM.WorkTel);
paramlinkman[6] = SqlHelper.GetParameter("@Handset", LinkManM.Handset);
paramlinkman[7] = SqlHelper.GetParameter("@ModifiedDate", LinkManM.ModifiedDate);
paramlinkman[8] = SqlHelper.GetParameter("@ModifiedUserID", LinkManM.ModifiedUserID);
#endregion
SqlCommand cmdlinkman = new SqlCommand(sqllinkman.ToString()); //未执行的联系人信息添加命令
cmdlinkman.Parameters.AddRange(paramlinkman);
comms[i] = cmdlinkman; //把未执行的联系人信息添加命令给cmd数组
}
//执行
SqlHelper.ExecuteTransForList(comms);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
catch
{
return false;
}
}
public static DataTable GetCust_ldByCon(CustInfoModel CustInfoM, string DateBegin, string DateEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.ID,b.CustName,a.Tel,a.Title,a.Callor,c.EmployeeName,a.ModifiedDate, ");
strSql.Append(" CONVERT(varchar(100), a.CallTime, 120) CallTime ");
strSql.Append(" from officedba.CustCall a ");
strSql.Append(" left join officedba.CustInfo b on a.custId = b.id ");
strSql.Append(" left join officedba.EmployeeInfo c on c.id = a.Creator ");
strSql.Append(" where a.CompanyCD = @CompanyCD ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustInfoM.CompanyCD));
#endregion
if (CustInfoM.ID != 0)
{
strSql.Append(" and a.CustID = @CustID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustInfoM.ID.ToString()));
}
if (!string.IsNullOrEmpty(CustInfoM.Tel))
{
strSql.AppendLine(" and a.Tel like @Tel");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Tel", "%" + CustInfoM.Tel + "%"));
}
if (!string.IsNullOrEmpty(DateBegin))
{
strSql.AppendLine(" and a.CallTime >= @DateBegin");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DateBegin", DateBegin));
}
if (!string.IsNullOrEmpty(DateEnd))
{
strSql.AppendLine(" and a.CallTime <= @DateEnd");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DateEnd", DateEnd));
}
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
/// <summary>
/// Cust树
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetCustTree(CustInfoModel model, string CanUserID)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("select a.ID,a.CustNo,a.CustName from officedba.CustInfo a where a.CompanyCD=@CompanyCD and a.UsedStatus=@UsedStatus ");
searchSql.AppendLine(" and (a.CanViewUser like '%" + "," + CanUserID + "," + "%' or '" + CanUserID + "' = a.Creator or '" + CanUserID + "' = a.Manager or a.CanViewUser = '******' or a.CanViewUser is null )");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", "1"));
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
protected void LoadPrintInfo()
{
PrintParameterSettingModel model = new PrintParameterSettingModel();
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.BillTypeFlag = int.Parse(ConstUtil.BILL_TYPEFLAG_CUSTOMER);
model.PrintTypeFlag = ConstUtil.PRINTBILL_CUSTINFO;
CustInfoModel modelMRP = new CustInfoModel();
modelMRP.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
modelMRP.CustNo = this.CustNo;
/*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/
string[,] aBase = {
{ "{ExtField1}", "ExtField1"},
{ "{ExtField2}", "ExtField2"},
{ "{ExtField3}", "ExtField3"},
{ "{ExtField4}", "ExtField4"},
{ "{ExtField5}", "ExtField5"},
{ "{ExtField6}", "ExtField6"},
{ "{ExtField7}", "ExtField7"},
{ "{ExtField8}", "ExtField8"},
{ "{ExtField9}", "ExtField9"},
{ "{ExtField10}", "ExtField10"},
{ "{ExtField11}", "ExtField11"},
{ "{ExtField12}", "ExtField12"},
{ "{ExtField13}", "ExtField13"},
{ "{ExtField14}", "ExtField14"},
{ "{ExtField15}", "ExtField15"},
{ "{ExtField16}", "ExtField16"},
{ "{ExtField17}", "ExtField17"},
{ "{ExtField18}", "ExtField18"},
{ "{ExtField19}", "ExtField19"},
{ "{ExtField20}", "ExtField20"},
{ "{ExtField21}", "ExtField21"},
{ "{ExtField22}", "ExtField22"},
{ "{ExtField23}", "ExtField23"},
{ "{ExtField24}", "ExtField24"},
{ "{ExtField25}", "ExtField25"},
{ "{ExtField26}", "ExtField26"},
{ "{ExtField27}", "ExtField27"},
{ "{ExtField28}", "ExtField28"},
{ "{ExtField29}", "ExtField29"},
{ "{ExtField30}", "ExtField30"},
{ "客户编号", "CustNo"},
{ "客户名称", "CustName"},
{ "客户大类", "CustBig" },
{ "客户简称", "CustNam" },
{ "拼音缩写", "CustShort"},
{ "客户管理分类", "CustTypeManage"},
{ "客户营销分类", "CustTypeSell"},
{ "客户优质级别", "CreditGradeNm"},
{ "客户时间分类", "CustTypeTime"},
{ "客户细分", "CustClassName"},
{ "客户类别", "CustTypaNm"},
{ "建档人", "CreatorName"},
{ "建档日期", "CreatedDate"},
{ "客户简介", "CustNote"},
{ "国家地区", "CountryName"},
{ "区域", "AreaName"},
{ "省", "Province"},
{ "市(县)", "City"},
{ "业务类型", "BusiType"},
{ "分管业务员 ", "ManagerName"},
{ "联系人", "ContactName"},
{ "电话", "Tel"},
{ "手机", "Mobile"},
{ "传真", "Fax"},
{ "在线咨询", "OnLine"},
{ "公司网址 ", "WebSite"},
{ "邮编", "Post"},
{ "电子邮件", "email"},
{ "首次交易日期", "FirstBuyDate"},
{ "运送方式", "CarryTypeNm"},
{ "交货方式", "TakeTypeNm"},
{ "联络期限(天)", "LinkCycleNm"},
{ "收货地址", "ReceiveAddress"},
{ "经营范围", "SellArea"},
{ "允许延期付款", "CreditManage"},
{ "信用额度(元)", "MaxCredit"},
{ "帐期天数(天)", "MaxCreditDate"},
{ "结算方式", "PayTypeNm"},
{ "结算币种", "CurrencyaNm"},
{ "发票类型", "BillTypeNm"},
{ "支付方式", "MoneyTypeNm"},
{ "开户行", "OpenBank"},
{ "户名", "AccountMan"},
{ "账号", "AccountNum"},
{ "单位性质", "CompanyType"},
{ "资产规模(万元)", "CapitalScale"},
{ "成立时间", "SetupDate"},
{ "注册资本(万元)", "SetupMoney"},
{ "员工总数(个)", "StaffCount"},
{ "法人代表", "ArtiPerson"},
{ "行业", "Trade"},
{ "营业执照号", "BusiNumber"},
{ "注册地址", "SetupAddress"},
{ "税务登记号", "TaxCD"},
{ "是否为一般纳税人", "IsTax"},
{ "客户来源", "Source"},
{ "年销售额(万元)", "SaleroomY"},
{ "年利润额(万元)", "ProfitY"},
{ "销售模式", "SellMode"},
{ "上级客户", "CustSupe"},
{ "价值评估", "MeritGrade"},
{ "阶段", "Phase"},
{ "热点客户", "HotIs"},
{ "热度", "HotHow"},
{ "关系等级", "RelaGrade"},
{ "启用状态", "UsedStatus"},
{ "关系描述", "Relation"},
{ "备注", "Remark"},
{ "最后更新用户", "ModifiedUserID"},
{ "最后更新日期", "ModifiedDate"},
{ "经营理念", "CompanyValues"},
{ "企业口号", "CatchWord"},
{ "企业文化概述", "ManageValues"},
{ "发展潜力", "Potential"},
{ "存在问题", "Problem"},
{ "市场优劣势", "Advantages"},
{ "行业地位", "TradePosition"},
{ "竞争对手", "Competition"},
{ "合作伙伴", "Collaborator"},
{ "发展计划", "ManagePlan"},
{ "合作方法", "Collaborate"},
{ "可查看该客户档案的人员", "CanViewUserName"},
};
string[,] aDetail = { { "", "" } };
#region 1.扩展属性
int countExt = 0;
DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.CustInfo");
if (dtExtTable.Rows.Count > 0)
{
for (int i = 0; i < dtExtTable.Rows.Count; i++)
{
for (int x = 0; x < (aBase.Length / 2) - 82; x++)
{
if (x == i)
{
aBase[x, 0] = dtExtTable.Rows[i]["EFDesc"].ToString();
countExt++;
}
}
}
}
#endregion
DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model);
//DataTable dtMRP = SellContractBus.GetRepOrder(OfferNo);
DataTable dtMRP = CustInfoBus.GetCustInfoByNo(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, CustBig, CustNo);
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 = "CustNo|CustName|CustBig|CustNam|CustShort|CustTypeManage|CustTypeSell|CreditGradeNm|CustTypeTime|CustClassName|CustTypaNm|CreatorName|" +
"CreatedDate|CustNote|CountryName|AreaName|Province|City|BusiType|ManagerName|ContactName|Tel|Mobile|Fax|OnLine|WebSite|Post|email|FirstBuyDate|" +
"CarryTypeNm|TakeTypeNm|LinkCycleNm|ReceiveAddress|SellArea|CreditManage|MaxCredit|MaxCreditDate|PayTypeNm|CurrencyaNm|BillTypeNm|MoneyTypeNm|" +
"OpenBank|AccountMan|AccountNum|CompanyType|CapitalScale|SetupDate|SetupMoney|StaffCount|ArtiPerson|Trade|BusiNumber|SetupAddress|TaxCD|IsTax|" +
"Source|SaleroomY|ProfitY|SellMode|CustSupe|MeritGrade|Phase|HotIs|HotHow|RelaGrade|UsedStatus|Relation|Remark|ModifiedUserID|ModifiedDate|" +
"CompanyValues|CatchWord|ManageValues|Potential|Problem|Advantages|TradePosition|Competition|Collaborator|ManagePlan|Collaborate|CanViewUserName";
/*基本信息字段+扩展信息字段*/
if (countExt > 0)
{
for (int i = 0; i < countExt; i++)
{
strBaseFields = strBaseFields + "|" + "ExtField" + (i + 1);
}
}
strDetailFields = "";
}
#region 主表信息
if (!string.IsNullOrEmpty(strBaseFields))
{
tableBase.InnerHtml = WritePrintPageTable("客户档案", strBaseFields, strDetailFields, aBase, aDetail, dtMRP, null, true);
}
#endregion
}
public static DataTable GetCust_gmByCon(CustInfoModel CustInfoM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.ID,a.CustID,b.CustName,a.OrderNo,a.RealTotal, ");
strSql.Append(" a.ModifiedDate,convert(varchar(10),a.OrderDate,23) OrderDate ");
strSql.Append(" from officedba.SellOrder a ");
strSql.Append(" left join officedba.CustInfo b on a.CustID = b.id ");
strSql.Append(" where a.CompanyCD = @CompanyCD ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustInfoM.CompanyCD));
#endregion
if (CustInfoM.ID != 0)
{
strSql.Append(" and a.CustID = @CustID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustInfoM.ID.ToString()));
}
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
/// <summary>
/// 根据查询条件获取客户列表信息的方法
/// </summary>
/// <param name="CustModel">查询条件</param>
/// <returns>客户列表结果结</returns>
public static DataTable GetCustInfoBycondition(CustInfoModel CustModel, string Manager,string CanUserID, string CreatedBegin, string CreatedEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
return CustInfoDBHelper.GetCustInfoBycondition(CustModel, Manager,CanUserID, CreatedBegin, CreatedEnd,pageIndex,pageCount,ord,ref TotalCount);
}
catch (System.Exception ex)
{
throw ex;
}
}
public static DataTable GetCust_fhByCon(CustInfoModel CustInfoM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.ID,a.SendNo,convert(varchar(10),a.SendDate,23) SendDate, ");
strSql.Append(" b.ProductName,b.Specification,c.TypeName,a.ProductCount,a.ModifiedDate ");
strSql.Append(" from officedba.SellSendDetail a ");
strSql.Append(" left join officedba.ProductInfo b on b.id = a.ProductID ");
strSql.Append(" left join officedba.CodePublicType c on c.id = b.ColorID ");
strSql.Append(" left join officedba.SellSend d on d.SendNo = a.SendNo and d.CompanyCD = a.CompanyCD ");
strSql.Append(" where a.CompanyCD = @CompanyCD ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustInfoM.CompanyCD));
#endregion
if (CustInfoM.ID != 0)
{
strSql.Append(" and d.CustID = @CustID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustInfoM.ID.ToString()));
}
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
/// <summary>
/// 客户一览表查询
/// </summary>
/// <param name="CustModel"></param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="ord"></param>
/// <param name="TotalCount"></param>
/// <returns></returns>
public static DataTable GetCustListByCondition(CustInfoModel CustModel, string CanUserID, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return CustInfoDBHelper.GetCustListByCondition(CustModel,CanUserID, pageIndex, pageCount, ord, ref TotalCount);
}
public static DataTable GetCust_hkByCon(CustInfoModel CustInfoM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
#region sql语句
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.ID,a.GatheringNo,convert(varchar(10),a.PlanGatherDate,23) PlanGatherDate, ");
strSql.Append(" a.PlanPrice,convert(varchar(10),a.FactGatherDate,23) FactGatherDate, ");
strSql.Append(" a.FactPrice,a.ModifiedDate,(case a.State when '1' then '已回款' ");
strSql.Append(" when '2' then '未回款' else '部分回款' end)State ");
strSql.Append(" from officedba.SellGathering a ");
strSql.Append(" where a.CompanyCD = @CompanyCD ");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CustInfoM.CompanyCD));
#endregion
if (CustInfoM.ID != 0)
{
strSql.Append(" and a.CustID = @CustID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", CustInfoM.ID.ToString()));
}
comm.CommandText = strSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
catch (Exception ex)
{
string smeg = ex.Message;
return null;
}
}
/// <summary>
/// 增加客户信息的方法
/// </summary>
/// <param name="CustModel">客户信息Model</param>
/// <returns>bool值</returns>
public static bool CustInfoAdd(CustInfoModel CustModel,LinkManModel LinkManM, Hashtable htExtAttr)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
LogInfoModel logModel = new LogInfoModel(); //操作日志
bool isSucc = false;//定义返回变量
#region 设置操作日志内容
//设置公司代码
logModel.CompanyCD = userInfo.CompanyCD;
//设置登陆用户ID
logModel.UserID = userInfo.UserID;
//设置模块ID 模块ID在ConstUtil中定义，以便维护
logModel.ModuleID = ConstUtil.MODULE_ID_CUST_INFO_ADD;
//操作单据编号 客户编号
logModel.ObjectID = CustModel.CustNo;
//操作对象 操作的表信息
logModel.ObjectName = ConstUtil.TABLE_NAME_CUST;
//涉及关键元素 涉及其他业务、表关系
logModel.Element = string.Empty;
//备注 操作类型
logModel.Remark = ConstUtil.LOG_PROCESS_INSERT;
#endregion
try
{
//if (CustModel.CustBig == "2")//会员客户时，添加到2个表中
//{
//}
//else
//{
isSucc = CustInfoDBHelper.CustInfoAdd(CustModel,LinkManM, htExtAttr);
//}
}
catch (System.Exception ex)
{
#region 操作失败时记录日志到文件
//定义变量
LogInfo logSys = new LogInfo();
//设置日志类型 需要指定为系统日志
logSys.Type = LogInfo.LogType.SYSTEM;
//指定系统日志类型 出错信息
logSys.SystemKind = LogInfo.SystemLogKind.SYSTEM_ERROR;
//指定登陆用户信息
logSys.UserInfo = userInfo;
//设定模块ID
logSys.ModuleID = ConstUtil.MODULE_ID_CUST_INFO_ADD;
//描述
logSys.Description = ex.ToString();
//输出日志
LogUtil.WriteLog(logSys);
#endregion
}
if (isSucc)//操作成功
{
logModel.Remark += "成功";
}
else//操作失败
{
logModel.Remark += "失败";
}
//记录日志
LogDBHelper.InsertLog(logModel);
return isSucc;
}
public static DataTable GetCust_jhByCon(CustInfoModel CustInfoM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return CustCallDBHelper.GetCust_jhByCon(CustInfoM, pageIndex, pageCount, ord, ref TotalCount);
}