/// <summary> /// 获取实体 /// </summary> /// <param name="strCode"></param> /// <param name="context"></param> private void GetSellGatheringModel(SellGatheringModel sellGatheringModel) { string GatheringNo = hiddExpGatheringNo.Value.Trim(); string Title = hiddExpTitle.Value.Trim(); //主题 string CustID = hiddExpCustID.Value.Trim(); //客户ID(关联客户信息表) string FromType = hiddExpFromType.Value.Trim(); //源单类型(0无来源,1发货通知单,2销售订单) string FromBillID = hiddExpFromBillID.Value.Trim(); //源单ID string PlanPrice = hiddExpPlanPrice.Value.Trim(); //计划回款金额 string GatheringTime = hiddExpGatheringTime.Value.Trim(); //期次 string Seller = hiddExpSeller.Value.Trim(); //业务员(对应员工表ID) if (Title.Length != 0) { sellGatheringModel.Title = Title;//主题 } if (GatheringNo.Length != 0) { sellGatheringModel.GatheringNo = GatheringNo; //回款计划编号 } if (CustID.Length != 0) { sellGatheringModel.CustID = Convert.ToInt32(CustID); //客户ID(关联客户信息表) } if (FromType.Length != 0) { sellGatheringModel.FromType = FromType; //源单类型(0无来源,1发货通知单,2销售订单) } if (FromBillID.Length != 0) { sellGatheringModel.FromBillID = Convert.ToInt32(FromBillID); //源单ID } if (PlanPrice.Length != 0) { sellGatheringModel.PlanPrice = Convert.ToDecimal(PlanPrice); //计划回款金额 } if (GatheringTime.Length != 0) { sellGatheringModel.GatheringTime = GatheringTime; //期次 } if (Seller.Length != 0) { sellGatheringModel.Seller = Convert.ToInt32(Seller); //业务员(对应员工表ID) } sellGatheringModel.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; //公司代码 }
protected void btnImport_Click(object sender, ImageClickEventArgs e) { //设置行为参数 string orderString = hiddExpOrder.Value.Trim(); //排序 string order = "desc"; //排序:降序 string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "GatheringNo"; //要排序的字段,如果为空,默认为"ID" if (orderString.EndsWith("_a")) { order = "asc"; //排序:升序 } int pageCount = int.Parse(hiddExpTotal.Value); //每页显示记录数 int pageIndex = 1; //当前页 int TotalCount = 0; //总记录数 string ord = orderBy + " " + order; //排序字段 DataTable dt = new DataTable(); SellGatheringModel sellGatheringModel = new SellGatheringModel(); GetSellGatheringModel(sellGatheringModel); string PlanPrice0 = hiddExpPlanPrice0.Value.Trim(); //计划回款金额 PlanPrice0 = PlanPrice0.Length == 0 ? null : PlanPrice0; string EFIndex = Request.QueryString["EFIndex"]; string EFDesc = Request.QueryString["EFDesc"]; GetBillExAttrControl1.ExtIndex = EFIndex; GetBillExAttrControl1.ExtValue = EFDesc; GetBillExAttrControl1.SetExtControlValue(); dt = SellGatheringBus.GetSellGathering(sellGatheringModel, PlanPrice0, EFIndex, EFDesc, pageIndex, pageCount, ord, ref TotalCount); //导出标题 string headerTitle = "单据编号|主题|客户|回款状态|源单类型|源单编号|业务员|总金额|期次|计划日期"; string[] header = headerTitle.Split('|'); //导出标题所对应的列字段名称 string columnFiled = "GatheringNo|Title|CustName|stateName|fromTypeName|BillNo|EmployeeName|PlanPrice|GatheringTime|PlanGatherDate"; //string columnFiled = "CreateDate|strUsedStatus"; string[] field = columnFiled.Split('|'); XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "回款计划列表"); }
/// <summary> /// 修改回款计划 /// </summary> /// <param name="sellGatheringModel"></param> /// <returns></returns> public static bool UpdateSellGathering(Hashtable ht, SellGatheringModel sellGatheringModel, out string strMsg) { bool isSuc = false; string remark = string.Empty; strMsg = ""; try { isSuc = SellGatheringDBHelper.UpdateSellGathering(ht, sellGatheringModel, out strMsg); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } catch (Exception ex) { //输出日志 SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, ConstUtil.MODULE_ID_GATHERING_ADD); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } SellLogCommon.InsertLog(sellGatheringModel.GatheringNo, ConstUtil.MODULE_ID_GATHERING_ADD, ConstUtil.CODING_RULE_TABLE_GATHERING, remark, ConstUtil.LOG_PROCESS_UPDATE); return(isSuc); }
/// <summary> /// 修改回款计划 /// </summary> /// <param name="sellGatheringModel"></param> /// <returns></returns> public static bool UpdateSellGathering(Hashtable htExtAttr, SellGatheringModel sellGatheringModel, out string strMsg) { bool isSucc = false;//是否添加成功 strMsg = ""; StringBuilder strSql = new StringBuilder(); strSql.Append("update officedba.SellGathering set "); strSql.Append("Title=@Title,"); strSql.Append("CustID=@CustID,"); strSql.Append("FromType=@FromType,"); strSql.Append("FromBillID=@FromBillID,"); strSql.Append("CurrencyType=@CurrencyType,"); strSql.Append("PlanGatherDate=@PlanGatherDate,"); strSql.Append("PlanPrice=@PlanPrice,"); strSql.Append("GatheringTime=@GatheringTime,"); strSql.Append("FactPrice=@FactPrice,"); strSql.Append("FactGatherDate=@FactGatherDate,"); strSql.Append("Seller=@Seller,"); strSql.Append("SellDeptId=@SellDeptId,"); strSql.Append("LinkBillNo=@LinkBillNo,"); strSql.Append("State=@State,"); strSql.Append("Remark=@Remark,"); strSql.Append("ModifiedDate=getdate(),"); strSql.Append("ModifiedUserID=@ModifiedUserID"); strSql.Append(" where CompanyCD=@CompanyCD and GatheringNo=@GatheringNo "); SqlParameter[] param = null; ArrayList lcmd = new ArrayList(); #region 参数 lcmd.Add(SqlHelper.GetParameterFromString("@CompanyCD", sellGatheringModel.CompanyCD)); lcmd.Add(SqlHelper.GetParameterFromString("@CustID", sellGatheringModel.CustID.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@GatheringNo", sellGatheringModel.GatheringNo)); lcmd.Add(SqlHelper.GetParameterFromString("@Title", sellGatheringModel.Title)); lcmd.Add(SqlHelper.GetParameterFromString("@FromType", sellGatheringModel.FromType)); lcmd.Add(SqlHelper.GetParameterFromString("@FromBillID", sellGatheringModel.FromBillID.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CurrencyType", sellGatheringModel.CurrencyType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@PlanGatherDate", sellGatheringModel.PlanGatherDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@PlanPrice", sellGatheringModel.PlanPrice.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@GatheringTime", sellGatheringModel.GatheringTime.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@FactPrice", sellGatheringModel.FactPrice.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@FactGatherDate", sellGatheringModel.FactGatherDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Seller", sellGatheringModel.Seller.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SellDeptId", sellGatheringModel.SellDeptId.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@LinkBillNo", sellGatheringModel.LinkBillNo)); lcmd.Add(SqlHelper.GetParameterFromString("@State", sellGatheringModel.State)); lcmd.Add(SqlHelper.GetParameterFromString("@Remark", sellGatheringModel.Remark)); lcmd.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", sellGatheringModel.ModifiedUserID)); #endregion #region 拓展属性 if (htExtAttr != null && htExtAttr.Count != 0) { strSql.Append(" ;UPDATE officedba.SellGathering set "); foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例 { strSql.Append(de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ","); lcmd.Add(SqlHelper.GetParameterFromString("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim())); } strSql.Append(" ModifiedUserID=@ModifiedUserID where CompanyCD=@CompanyCD and GatheringNo=@GatheringNo "); } if (lcmd != null && lcmd.Count > 0) { param = new SqlParameter[lcmd.Count]; for (int i = 0; i < lcmd.Count; i++) { param[i] = (SqlParameter)lcmd[i]; } } #endregion foreach (SqlParameter para in param) { if (para.Value == null) { para.Value = DBNull.Value; } } try { SqlHelper.ExecuteTransSql(strSql.ToString(), param); isSucc = SqlHelper.Result.OprateCount > 0 ? true : false; strMsg = "保存成功!"; } catch (Exception ex) { isSucc = false; strMsg = "保存失败,请联系系统管理员!"; throw ex; } return(isSucc); }
/// <summary> /// 获取列表 /// </summary> /// <param name="sellGatheringModel"></param> /// <returns></returns> public static DataTable GetSellGathering(SellGatheringModel sellGatheringModel, string PlanPrice0, string EFIndex, string EFDesc, int pageIndex, int pageCount, string ord, ref int TotalCount) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT g.ID, g.Title, g.GatheringNo,g.ModifiedDate, CONVERT(varchar(100), g.PlanGatherDate, 23) "); strSql.Append(" AS PlanGatherDate, ISNULL(c.CustName, '') AS CustName, "); strSql.Append(" ISNULL(CASE g.FromType WHEN '0' THEN '' WHEN '1' THEN "); strSql.Append(" (SELECT so.OrderNo FROM officedba.SellOrder AS so "); strSql.Append(" WHERE so.id = g.FromBillID) WHEN '2' THEN "); strSql.Append(" (SELECT ss.SendNo FROM officedba.SellSend AS ss "); strSql.Append(" WHERE ss.id = g.FromBillID) END, '') AS BillNo, "); strSql.Append(" CASE g.FromType WHEN '0' THEN '无来源' WHEN '1' THEN '销售订单' "); strSql.Append(" WHEN '2' THEN '销售发货单' END AS fromTypeName, "); strSql.Append(" CASE g.State WHEN '1' THEN '已回款' WHEN '2' THEN '未回款' WHEN '3' "); strSql.Append(" THEN '部分回款' END AS stateName, ISNULL(e.EmployeeName, '') "); strSql.Append(" AS EmployeeName,isnull( g.PlanPrice,0) as PlanPrice, g.GatheringTime "); strSql.Append(" FROM officedba.SellGathering AS g LEFT OUTER JOIN "); strSql.Append(" officedba.CustInfo AS c ON g.CustID = c.ID LEFT OUTER JOIN "); strSql.Append(" officedba.EmployeeInfo AS e ON g.Seller = e.ID "); strSql.Append(" WHERE g.CompanyCD=@CompanyCD"); string strCompanyCD = string.Empty;//单位编号 strCompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; ArrayList arr = new ArrayList(); arr.Add(new SqlParameter("@CompanyCD", strCompanyCD)); //扩展属性 if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { strSql.Append(" and g.ExtField" + EFIndex + " like @EFDesc "); arr.Add(new SqlParameter("@EFDesc", "%" + EFDesc + "%")); } if (sellGatheringModel.CustID != null) { strSql.Append(" and g.CustID = @CustID "); arr.Add(new SqlParameter("@CustID", sellGatheringModel.CustID)); } if (sellGatheringModel.FromBillID != null) { strSql.Append(" and g.FromBillID = @FromBillID "); arr.Add(new SqlParameter("@FromBillID", sellGatheringModel.FromBillID)); } if (sellGatheringModel.FromType != null) { strSql.Append(" and g.FromType = @FromType "); arr.Add(new SqlParameter("@FromType", sellGatheringModel.FromType)); } if (sellGatheringModel.GatheringNo != null) { strSql.Append(" and g.GatheringNo like @GatheringNo "); arr.Add(new SqlParameter("@GatheringNo", "%" + sellGatheringModel.GatheringNo + "%")); } if (sellGatheringModel.GatheringTime != null) { strSql.Append(" and g.GatheringTime = @GatheringTime "); arr.Add(new SqlParameter("@GatheringTime", sellGatheringModel.GatheringTime)); } if (sellGatheringModel.PlanPrice != null) { strSql.Append(" and g.PlanPrice > @PlanPrice "); arr.Add(new SqlParameter("@PlanPrice", sellGatheringModel.PlanPrice)); } if (PlanPrice0 != null) { strSql.Append(" and g.PlanPrice < @PlanPrice0 "); arr.Add(new SqlParameter("@PlanPrice0", PlanPrice0)); } if (sellGatheringModel.Seller != null) { strSql.Append(" and g.Seller = @Seller "); arr.Add(new SqlParameter("@Seller", sellGatheringModel.Seller)); } if (sellGatheringModel.Title != null) { strSql.Append(" and g.Title like @Title "); arr.Add(new SqlParameter("@Title", "%" + sellGatheringModel.Title + "%")); } return(SqlHelper.CreateSqlByPageExcuteSqlArr(strSql.ToString(), pageIndex, pageCount, ord, arr, ref TotalCount)); }
/// <summary> /// 获取列表 /// </summary> /// <param name="sellGatheringModel"></param> /// <returns></returns> public static DataTable GetSellGathering(SellGatheringModel sellGatheringModel, string PlanPrice0, string EFIndex, string EFDesc, int pageIndex, int pageCount, string ord, ref int TotalCount) { return(SellGatheringDBHelper.GetSellGathering(sellGatheringModel, PlanPrice0, EFIndex, EFDesc, pageIndex, pageCount, ord, ref TotalCount)); }