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 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);
}
/// <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="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;//公司代码
}