/// <summary> /// 保存销售委托代销单 /// </summary> /// <returns></returns> public static bool SaveOrder(Hashtable ht, SellChannelSttlModel sellChannelSttlModel, List<SellChannelSttlDetailModel> sellChannelSttlDetailModellist, out string strMsg) { bool isSucc = false;//是否添加成功 strMsg = ""; //判断单据编号是否存在 if (NoIsExist(sellChannelSttlModel.SttlNo)) { TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { InsertOrderInfo(ht,sellChannelSttlModel, tran); InsertOrderDetail(sellChannelSttlDetailModellist, tran); tran.Commit(); isSucc = true; strMsg = "保存成功!"; } catch (Exception ex) { tran.Rollback(); strMsg = "保存失败,请联系系统管理员!"; throw ex; } } else { isSucc = false; strMsg = "该编号已被使用,请输入未使用的编号!"; } return isSucc; }
/// <summary> /// 更新销售委托代销单 /// </summary> /// <returns></returns> public static bool UpdateOrder(Hashtable ht, SellChannelSttlModel sellChannelSttlModel, List<SellChannelSttlDetailModel> sellChannelSttlDetailModellist, out string strMsg) { bool isSucc = false;//是否添加成功 strMsg = ""; if (IsUpdate(sellChannelSttlModel.SttlNo)) { string strSql = "delete from officedba.SellChannelSttlDetail where SttlNo=@SttlNo and CompanyCD=@CompanyCD"; SqlParameter[] paras = { new SqlParameter("@SttlNo", sellChannelSttlModel.SttlNo), new SqlParameter("@CompanyCD", sellChannelSttlModel.CompanyCD) }; TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { UpdateOrderInfo(ht,sellChannelSttlModel, tran); SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras); InsertOrderDetail(sellChannelSttlDetailModellist, tran); tran.Commit(); isSucc = true; strMsg = "保存成功!"; } catch (Exception ex) { tran.Rollback(); strMsg = "保存失败,请联系系统管理员!"; throw ex; } } else { isSucc = false; strMsg = "非制单状态的未提交审批、审批未通过或撤销审批单据不可修改!"; } return isSucc; }
/// <summary> /// 保存销售委托代销单 /// </summary> /// <returns></returns> public static bool SaveOrder(Hashtable ht, SellChannelSttlModel sellChannelSttlModel, List<SellChannelSttlDetailModel> sellChannelSttlDetailModellist, out string strMsg) { bool isSuc = false; string remark = string.Empty; strMsg = ""; try { isSuc = SellChannelSttlDBHelper.SaveOrder(ht,sellChannelSttlModel, sellChannelSttlDetailModellist, out strMsg); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } catch (Exception ex) { //输出日志 SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, ConstUtil.MODULE_ID_SELLCHANNELSTTL_ADD); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } SellLogCommon.InsertLog(sellChannelSttlModel.SttlNo, ConstUtil.MODULE_ID_SELLCHANNELSTTL_ADD, ConstUtil.CODING_RULE_TABLE_SELLCHANNELSTTL, remark, ConstUtil.LOG_PROCESS_INSERT); return isSuc; }
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) : "SttlDate";//要排序的字段,如果为空,默认为"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(); string strorderNo = hiddExpOrderNo.Value.Trim(); string strTitle = hiddExpTitle.Value.Trim(); string strCustID = hiddExpCustID.Value.Trim(); string strSeller = hiddExpSeller.Value.Trim(); string strBillStatus = hiddExpBillStatus.Value.Trim(); string strSttlDate1 = hiddExpOfferDate1.Value.Trim(); string strSttlDate = hiddExpOfferDate.Value.Trim(); string strFlowStatus = hiddExpFlowStatus.Value.Trim(); string strFromBillID = hiddExpFromBillID.Value.Trim(); string orderNo = strorderNo.Length == 0 ? null : strorderNo; string Title = strTitle.Length == 0 ? null : strTitle; int? CustID = strCustID.Length == 0 ? null : (int?)Convert.ToInt32(strCustID); int? Seller = strSeller.Length == 0 ? null : (int?)Convert.ToInt32(strSeller); string BillStatus = strBillStatus.Length == 0 ? null : strBillStatus; DateTime? date = strSttlDate.Length == 0 ? null : (DateTime?)Convert.ToDateTime(strSttlDate); DateTime? date1 = strSttlDate1.Length == 0 ? null : (DateTime?)Convert.ToDateTime(strSttlDate1); int? FlowStatus = strFlowStatus.Length == 0 ? null : (int?)Convert.ToInt32(strFlowStatus); int? FromBillID = strFromBillID.Length == 0 ? null : (int?)Convert.ToInt32(strFromBillID); //扩展属性 string EFIndex = Request.QueryString["EFIndex"]; string EFDesc = Request.QueryString["EFDesc"]; GetBillExAttrControl1.ExtIndex = EFIndex; GetBillExAttrControl1.ExtValue = EFDesc; GetBillExAttrControl1.SetExtControlValue(); SellChannelSttlModel model = new SellChannelSttlModel(); model.SttlNo = orderNo; model.Title = Title; model.CustID = CustID; model.Seller = Seller; model.BillStatus = BillStatus; model.SttlDate = date; model.FromBillID = FromBillID; dt = SellChannelSttlBus.GetOrderList(model, date1, FlowStatus, EFIndex, EFDesc, pageIndex, pageCount, ord, ref TotalCount); //导出标题 string headerTitle = "结算单编号|结算单主题|销售发货单|客户|业务员|结算日期|结算金额|单据状态|审批状态"; //string headerTitle = "建档日期|启用状态"; string[] header = headerTitle.Split('|'); //导出标题所对应的列字段名称 string columnFiled = "SttlNo|Title|OrderNo|CustName|SellerName|SttlDate|SttlTotal|BillStatusText|FlowInstanceText"; //string columnFiled = "CreateDate|strUsedStatus"; string[] field = columnFiled.Split('|'); XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "委托代销单列表"); }
/// <summary> /// 跟新主表数据 /// </summary> /// <param name="sellChannelSttlModel"></param> /// <param name="tran"></param> private static void UpdateOrderInfo(Hashtable htExtAttr, SellChannelSttlModel sellChannelSttlModel, TransactionManager tran) { StringBuilder strSql = new StringBuilder(); strSql.Append("update officedba.SellChannelSttl set "); strSql.Append("Title=@Title,"); strSql.Append("CustID=@CustID,"); strSql.Append("FromType=@FromType,"); strSql.Append("PayType=@PayType,"); strSql.Append("MoneyType=@MoneyType,"); strSql.Append("Seller=@Seller,"); strSql.Append("SellDeptId=@SellDeptId,"); strSql.Append("CurrencyType=@CurrencyType,"); strSql.Append("Rate=@Rate,"); strSql.Append("SttlDate=@SttlDate,"); strSql.Append("CountTotal=@CountTotal,"); strSql.Append("TotalFee=@TotalFee,"); strSql.Append("PushMoneyPercent=@PushMoneyPercent,"); strSql.Append("PushMoney=@PushMoney,"); strSql.Append("HandFeeTotal=@HandFeeTotal,"); strSql.Append("SttlTotal=@SttlTotal,"); strSql.Append("Remark=@Remark,"); strSql.Append("BillStatus=@BillStatus,"); strSql.Append("Confirmor=@Confirmor,"); strSql.Append("ConfirmDate=@ConfirmDate,"); strSql.Append("Closer=@Closer,"); strSql.Append("CloseDate=@CloseDate,"); strSql.Append("ModifiedDate=getdate(),"); strSql.Append("ModifiedUserID=@ModifiedUserID,"); strSql.Append("FromBillID=@FromBillID"); strSql.Append(" where CompanyCD=@CompanyCD and SttlNo=@SttlNo "); SqlParameter[] param = null; ArrayList lcmd = new ArrayList(); #region 参数 lcmd.Add(SqlHelper.GetParameterFromString("@CompanyCD", sellChannelSttlModel.CompanyCD)); lcmd.Add(SqlHelper.GetParameterFromString("@SttlNo", sellChannelSttlModel.SttlNo)); lcmd.Add(SqlHelper.GetParameterFromString("@Title", sellChannelSttlModel.Title)); lcmd.Add(SqlHelper.GetParameterFromString("@CustID", sellChannelSttlModel.CustID.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@FromType", sellChannelSttlModel.FromType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@PayType", sellChannelSttlModel.PayType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Seller", sellChannelSttlModel.Seller.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SellDeptId", sellChannelSttlModel.SellDeptId.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CurrencyType", sellChannelSttlModel.CurrencyType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Rate", sellChannelSttlModel.Rate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SttlDate", sellChannelSttlModel.SttlDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CountTotal", sellChannelSttlModel.CountTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@TotalFee", sellChannelSttlModel.TotalFee.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@PushMoneyPercent", sellChannelSttlModel.PushMoneyPercent.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@PushMoney", sellChannelSttlModel.PushMoney.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@HandFeeTotal", sellChannelSttlModel.HandFeeTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SttlTotal", sellChannelSttlModel.SttlTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Remark", sellChannelSttlModel.Remark)); lcmd.Add(SqlHelper.GetParameterFromString("@BillStatus", sellChannelSttlModel.BillStatus)); lcmd.Add(SqlHelper.GetParameterFromString("@Confirmor", sellChannelSttlModel.Confirmor.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@ConfirmDate", sellChannelSttlModel.ConfirmDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Closer", sellChannelSttlModel.Closer.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CloseDate", sellChannelSttlModel.CloseDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", sellChannelSttlModel.ModifiedUserID)); lcmd.Add(SqlHelper.GetParameterFromString("@FromBillID", sellChannelSttlModel.FromBillID.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@MoneyType", sellChannelSttlModel.MoneyType.ToString())); #endregion #region 拓展属性 if (htExtAttr != null && htExtAttr.Count != 0) { strSql.Append(" ;UPDATE officedba.SellChannelSttl 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(" BillStatus=@BillStatus where CompanyCD=@CompanyCD and SttlNo=@SttlNo "); } 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; } } SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param); }
/// <summary> /// 获取发货单列表 /// </summary> /// <param name="sellContractModel">sellContractModel表实体</param> /// <param name="FlowStatus">审批状态</param> /// <returns></returns> public static DataTable GetOrderList(SellChannelSttlModel sellChannelSttlModel, DateTime? dt, int? FlowStatus,string EFIndex,string EFDesc, int pageIndex, int pageCount, string ord, ref int TotalCount) { string strSql = string.Empty; strSql = "select ID,SttlNo,Title,SellerName,CustName,SttlDate,SttlTotal,OrderNo,BillStatusText, "; strSql += "FlowInstanceText,FromTypeText,RefText,ModifiedDate from (SELECT s.SttlNo, s.ID, s.ModifiedDate,s.Title, ISNULL(so.SendNo, '') AS OrderNo, "; strSql += "ISNULL(e1.EmployeeName, '') AS SellerName, ISNULL(c.CustName, '') "; strSql += "AS CustName, CASE s.FromType WHEN 1 THEN '销售发货单' END AS FromTypeText,s.SttlTotal ,CONVERT(varchar(100), s.SttlDate , 23) AS SttlDate, "; strSql += "CASE s.BillStatus WHEN 1 THEN '制单' WHEN 2 THEN '执行' WHEN 3 THEN '变更' "; strSql += "WHEN 4 THEN '手工结单' WHEN 5 THEN '自动结单' END AS BillStatusText, "; strSql += "CASE WHEN (select top 1 FlowStatus from officedba.FlowInstance "; strSql += " where BillID=s.ID AND BillTypeFlag = 5 AND BillTypeCode = 6 order by ModifiedDate desc )IS NULL THEN '' "; strSql += "WHEN (select top 1 FlowStatus from officedba.FlowInstance "; strSql += "where BillID=s.ID AND BillTypeFlag = 5 AND BillTypeCode = 6 order by ModifiedDate desc )=1 THEN "; strSql += "'待审批' WHEN (select top 1 FlowStatus from officedba.FlowInstance "; strSql += "where BillID=s.ID AND BillTypeFlag = 5 AND BillTypeCode = 6 order by ModifiedDate desc ) = 2 THEN '审批中' "; strSql += " WHEN (select top 1 FlowStatus from officedba.FlowInstance "; strSql += " where BillID=s.ID AND BillTypeFlag = 5 AND BillTypeCode = 6 order by ModifiedDate desc ) = 3 THEN '审批通过' "; strSql += "WHEN (select top 1 FlowStatus from officedba.FlowInstance "; strSql += "where BillID=s.ID AND BillTypeFlag = 5 AND BillTypeCode = 6 order by ModifiedDate desc )=4 THEN '审批不通过' "; strSql += " WHEN (select top 1 FlowStatus from officedba.FlowInstance "; strSql += " where BillID=s.ID AND BillTypeFlag = 5 AND BillTypeCode = 6 order by ModifiedDate desc ) = 5 THEN "; strSql += "'撤销审批' END AS FlowInstanceText,(select top 1 FlowStatus from officedba.FlowInstance "; strSql += " where BillID=s.ID AND BillTypeFlag = 5 AND BillTypeCode = 6 order by ModifiedDate desc )as FlowStatus, "; strSql += "isnull(CASE((SELECT count(1) FROM officedba.BlendingDetails "; strSql += "WHERE BillingType = '4' AND BillCD = s.SttlNo and CompanyCD=s.CompanyCD) "; strSql += ") WHEN 0 THEN '无引用' END, '被引用') AS RefText "; strSql += "FROM officedba.SellChannelSttl AS s LEFT OUTER JOIN "; strSql += "officedba.SellSend AS so ON s.FromBillID = so.ID LEFT OUTER JOIN "; strSql += "officedba.EmployeeInfo AS e1 ON s.Seller = e1.ID LEFT OUTER JOIN "; strSql += "officedba.CustInfo AS c ON s.CustID = c.ID where s.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 += " and s.ExtField" + EFIndex + " like @EFDesc "; arr.Add(new SqlParameter("@EFDesc", "%" + EFDesc + "%")); } if (sellChannelSttlModel.BillStatus != null) { strSql += " and s.BillStatus= @BillStatus"; arr.Add(new SqlParameter("@BillStatus", sellChannelSttlModel.BillStatus)); } if (sellChannelSttlModel.FromBillID != null) { strSql += " and s.FromBillID=@FromBillID"; arr.Add(new SqlParameter("@FromBillID", sellChannelSttlModel.FromBillID)); ; } if (sellChannelSttlModel.CustID != null) { strSql += " and s.CustID=@CustID"; arr.Add(new SqlParameter("@CustID", sellChannelSttlModel.CustID)); } if (sellChannelSttlModel.Seller != null) { strSql += " and s.Seller=@Seller"; arr.Add(new SqlParameter("@Seller", sellChannelSttlModel.Seller)); } if (sellChannelSttlModel.SttlNo != null) { strSql += " and s.SttlNo like @SttlNo"; arr.Add(new SqlParameter("@SttlNo", "%" + sellChannelSttlModel.SttlNo + "%")); } if (sellChannelSttlModel.SttlDate != null) { strSql += " and s.SttlDate >= @SttlDate"; arr.Add(new SqlParameter("@SttlDate", sellChannelSttlModel.SttlDate)); } if (dt != null) { strSql += " and s.SttlDate <= @SttlDate1"; arr.Add(new SqlParameter("@SttlDate1", dt)); } if (sellChannelSttlModel.Title != null) { strSql += " and s.Title like @Title"; arr.Add(new SqlParameter("@Title", "%" + sellChannelSttlModel.Title + "%")); } strSql += " ) as f where 1=1 "; if (FlowStatus != null) { if (FlowStatus != 0) { strSql += " and f.FlowStatus=@FlowStatus"; arr.Add(new SqlParameter("@FlowStatus", FlowStatus)); } else { strSql += " and f.FlowStatus is null "; } } return SqlHelper.CreateSqlByPageExcuteSqlArr(strSql.ToString(), pageIndex, pageCount, ord, arr, ref TotalCount); }
/// <summary> /// 取消确认单据 /// </summary> /// <param name="OrderNO">单据编号</param> /// <param name="strMsg">操作返回的信息</param> /// <returns></returns> public static bool UnConfirmOrder(SellChannelSttlModel sellChannelSttlModel, out string strMsg) { string strElement = string.Empty; bool isSucc = false;//是否添加成功 //定义变量 string remark = string.Empty; //操作名称 strElement = ConstUtil.LOG_PROCESS_UNCONFIRM; strMsg = ""; try { isSucc = SellChannelSttlDBHelper.UnConfirmOrder(sellChannelSttlModel.SttlNo, out strMsg); //取消确认成功后调用“自动生成凭证”方法 if (isSucc == true) { string str = ""; bool AutoVoucherInsertFlag = XBase.Business.Office.FinanceManager.AutoVoucherBus.AntiConfirmVoucher("officedba.SellChannelSttl," + sellChannelSttlModel.ID, out str); if (AutoVoucherInsertFlag == true) { //strMsg += str; } else { strMsg += str; } } //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } catch (Exception ex) { //输出日志 SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, ConstUtil.MODULE_ID_SELLCHANNELSTTL_ADD); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } //写入日志 SellLogCommon.InsertLog(sellChannelSttlModel.SttlNo, ConstUtil.MODULE_ID_SELLCHANNELSTTL_ADD, ConstUtil.CODING_RULE_TABLE_SELLCHANNELSTTL, remark, strElement); return isSucc; }
/// <summary> /// 确认单据 /// </summary> /// <param name="OrderNO">单据编号</param> /// <param name="strMsg">操作返回的信息</param> /// <returns></returns> public static bool ConfirmOrder(SellChannelSttlModel sellChannelSttlModel, out string strMsg, out string strFieldText) { string strElement = string.Empty; bool isSucc = false;//是否添加成功 //定义变量 string remark = string.Empty; //操作名称 strElement = ConstUtil.LOG_PROCESS_CONFIRM; strFieldText = ""; strMsg = ""; try { isSucc = SellChannelSttlDBHelper.ConfirmOrder(sellChannelSttlModel.SttlNo, out strMsg, out strFieldText); //确认成功后调用“自动生成凭证”方法 if (isSucc == true) { string str = ""; bool AutoVoucherInsertFlag = XBase.Business.Office.FinanceManager.AutoVoucherBus.AutoVoucherInsert(3, Convert.ToDecimal(sellChannelSttlModel.TotalFee), "officedba.SellChannelSttl," + sellChannelSttlModel.ID, sellChannelSttlModel.CurrencyType + "," + sellChannelSttlModel.Rate, Convert.ToInt32(sellChannelSttlModel.CustID), out str); //returnValue=0 业务单未设凭证模板,returnValue=1 企业不启用业务单自动生成凭证,returnValue = 2 企业不启用自动生成凭证自动登帐, returnValue = 3 自动生成凭证失败 ,returnValue = "4" 回写业务单登记凭证状态成功,returnValue = "5" 回写业务单登记凭证状态失败 if (AutoVoucherInsertFlag == true) { strMsg += str; } else { strMsg += str; } } //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } catch (Exception ex) { //输出日志 SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, ConstUtil.MODULE_ID_SELLCHANNELSTTL_ADD); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } //写入日志 SellLogCommon.InsertLog(sellChannelSttlModel.SttlNo, ConstUtil.MODULE_ID_SELLCHANNELSTTL_ADD, ConstUtil.CODING_RULE_TABLE_SELLCHANNELSTTL, remark, strElement); return isSucc; }
/// <summary> /// 获取单据列表 /// </summary> /// <param name="sellOfferModel">sellOfferModel表实体</param> /// <param name="FlowStatus">审批状态</param> /// <returns></returns> public static DataTable GetOrderList(SellChannelSttlModel sellChannelSttlModel, DateTime? dt, int? FlowStatus,string EFIndex,string EFDesc, int pageIndex, int pageCount, string ord, ref int TotalCount) { return SellChannelSttlDBHelper.GetOrderList(sellChannelSttlModel, dt, FlowStatus, EFIndex, EFDesc, pageIndex, pageCount, ord, ref TotalCount); }