/// <summary> /// 添加新单据 /// </summary> /// <returns></returns> public static bool Insert(Hashtable ht, SellOrderModel sellOrderModel, List<SellOrderDetailModel> sellOrderDetailModellList, List<SellOrderFeeDetailModel> sellOrderFeeDetailModelList, out string strMsg) { bool isSucc = false;//是否添加成功 strMsg = ""; //判断单据编号是否存在 if (NoIsExist(sellOrderModel.OrderNo)) { TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { InsertSellOrder(ht,sellOrderModel, tran); InsertSellOrderDetail(sellOrderDetailModellList, tran); InsertSellOrderFeeDetail(sellOrderFeeDetailModelList, 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 Update(Hashtable ht, SellOrderModel sellOrderModel, List<SellOrderDetailModel> sellOrderDetailModellList, List<SellOrderFeeDetailModel> sellOrderFeeDetailModelList, out string strMsg) { bool isSucc = false;//是否添加成功 strMsg = ""; if (IsUpdate(sellOrderModel.OrderNo)) { string strSql = "delete from officedba.SellOrderDetail where OrderNo=@OrderNo and CompanyCD=@CompanyCD"; SqlParameter[] paras = { new SqlParameter("@OrderNo", sellOrderModel.OrderNo), new SqlParameter("@CompanyCD", sellOrderModel.CompanyCD) }; string strSql1 = "delete from officedba.SellOrderFeeDetail where OrderNo=@OrderNo and CompanyCD=@CompanyCD"; TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { UpdateSellOrder(ht, sellOrderModel, tran); SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras); SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql1.ToString(), paras); InsertSellOrderDetail(sellOrderDetailModellList, tran); InsertSellOrderFeeDetail(sellOrderFeeDetailModelList, tran); tran.Commit(); isSucc = true; strMsg = "保存成功!"; } catch (Exception ex) { tran.Rollback(); strMsg = "保存失败,请联系系统管理员!"; throw ex; } } else { isSucc = false; strMsg = "非制单状态的未提交审批、审批未通过或撤销审批单据不可修改!"; } return isSucc; }
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) : "OrderDate";//要排序的字段,如果为空,默认为"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 strTotalPrice = hiddExpTotalPrice.Value.Trim(); string strTotalPrice1 = hiddExpTotalPrice1.Value.Trim(); string strFromType = hiddExpFromType.Value.Trim(); string strBillStatus = hiddExpBillStatus.Value.Trim(); string strCustID = hiddExpCustID.Value.Trim(); string strSeller = hiddExpSeller.Value.Trim(); string strisOpenbill = hiddExpIsOpenbill.Value.Trim(); string strFlowStatus = hiddExpFlowStatus.Value.Trim(); string strFromBillID = hiddExpFromBillID.Value.Trim(); string strSendPro = hiddExpSendPro.Value.Trim(); string strProjectID = hiddProjectID.Value.Trim(); string orderNo = strorderNo.Length == 0 ? null : strorderNo; string Title = strTitle.Length == 0 ? null : strTitle; decimal? TotalPrice = strTotalPrice.Length == 0 ? null : (decimal?)Convert.ToDecimal(strTotalPrice); decimal? TotalPrice1 = strTotalPrice1.Length == 0 ? null : (decimal?)Convert.ToDecimal(strTotalPrice1); string FromType = strFromType.Length == 0 ? null : strFromType; string BillStatus = strBillStatus.Length == 0 ? null : strBillStatus; int? CustID = strCustID.Length == 0 ? null : (int?)Convert.ToInt32(strCustID); int? Seller = strSeller.Length == 0 ? null : (int?)Convert.ToInt32(strSeller); string isOpenbill = strisOpenbill.Length == 0 ? null : strisOpenbill; int? FlowStatus = strFlowStatus.Length == 0 ? null : (int?)Convert.ToInt32(strFlowStatus); int? FromBillID = strFromBillID.Length == 0 ? null : (int?)Convert.ToInt32(strFromBillID); string SendPro = strSendPro.Length == 0 ? null : strSendPro; int? ProjectID = strProjectID.Length == 0 ? null : (int?)Convert.ToInt32(strProjectID); //扩展属性 string EFIndex = Request.QueryString["EFIndex"]; string EFDesc = Request.QueryString["EFDesc"]; GetBillExAttrControl1.ExtIndex = EFIndex; GetBillExAttrControl1.ExtValue = EFDesc; GetBillExAttrControl1.SetExtControlValue(); SellOrderModel model = new SellOrderModel(); model.OrderNo = orderNo; model.Title = Title; model.TotalPrice = TotalPrice; model.FromType = FromType; model.FromBillID = FromBillID; model.BillStatus = BillStatus; model.CustID = CustID; model.Seller = Seller; model.isOpenbill = isOpenbill; model.ProjectID = ProjectID; dt = SellOrderBus.GetOrderList(model, TotalPrice1, SendPro, FlowStatus,EFIndex,EFIndex, pageIndex, pageCount, ord, ref TotalCount); //导出标题 string headerTitle = "订单编号|订单主题|客户|源单类型|源单编号|订单日期|总金额|是否已建单|发货情况|回款金额|单据状态|审批状态"; //string headerTitle = "建档日期|启用状态"; string[] header = headerTitle.Split('|'); //导出标题所对应的列字段名称 string columnFiled = "OrderNo|Title|CustName|FromTypeText|FromBillNo|OrderDate|RealTotal|isOpenbillText|isSendText|YAccounts|BillStatusText|FlowInstanceText"; //string columnFiled = "CreateDate|strUsedStatus"; string[] field = columnFiled.Split('|'); XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "销售订单列表"); }
/// <summary> /// 取消确认单据 /// </summary> /// <param name="OrderNO">单据编号</param> /// <param name="strMsg">操作返回的信息</param> /// <returns></returns> public static bool UnConfirmOrder(SellOrderModel sellOrderModel, out string strMsg) { string strElement = string.Empty; bool isSucc = false;//是否添加成功 //定义变量 string remark = string.Empty; //操作名称 strElement = ConstUtil.LOG_PROCESS_UNCONFIRM; strMsg = ""; try { isSucc = SellOrderDBHelper.UnConfirmOrder(sellOrderModel.OrderNo, out strMsg); //取消确认成功后调用“自动生成凭证”方法 if (isSucc == true) { string str = ""; bool AutoVoucherInsertFlag = XBase.Business.Office.FinanceManager.AutoVoucherBus.AntiConfirmVoucher("officedba.sellorder," + sellOrderModel.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_SELLORDER_ADD); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } //写入日志 SellLogCommon.InsertLog(sellOrderModel.OrderNo, ConstUtil.MODULE_ID_SELLORDER_ADD, ConstUtil.CODING_RULE_TABLE_SELLORDER, remark, strElement); return isSucc; }
/// <summary> /// 确认单据 /// </summary> /// <param name="OrderNO">单据编号</param> /// <param name="strMsg">操作返回的信息</param> /// <returns></returns> public static bool ConfirmOrder(SellOrderModel sellOrderModel, out string strMsg) { string strElement = string.Empty; bool isSucc = false;//是否添加成功 //定义变量 string remark = string.Empty; //操作名称 strElement = ConstUtil.LOG_PROCESS_CONFIRM; strMsg = ""; try { isSucc = SellOrderDBHelper.ConfirmOrder(sellOrderModel.OrderNo, out strMsg); //确认成功后调用“自动生成凭证”方法 if (isSucc == true) { string str=""; bool AutoVoucherInsertFlag = XBase.Business.Office.FinanceManager.AutoVoucherBus.AutoVoucherInsert(2, Convert.ToDecimal( sellOrderModel.TotalFee), "officedba.sellorder," + sellOrderModel.ID, sellOrderModel.CurrencyType + "," + sellOrderModel.Rate,Convert.ToInt32( sellOrderModel.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_SELLORDER_ADD); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } //写入日志 SellLogCommon.InsertLog(sellOrderModel.OrderNo, ConstUtil.MODULE_ID_SELLORDER_ADD, ConstUtil.CODING_RULE_TABLE_SELLORDER, remark, strElement); return isSucc; }
/// <summary> /// 获取发货单列表 /// </summary> /// <param name="sellContractModel">sellContractModel表实体</param> /// <param name="FlowStatus">审批状态</param> /// <returns></returns> public static DataTable GetOrderList(SellOrderModel sellOrderModel, decimal? TotalPrice1, string SendPro, int? FlowStatus,string EFIndex,string EFDesc, int pageIndex, int pageCount, string ord, ref int TotalCount) { return SellOrderDBHelper.GetOrderList(sellOrderModel, TotalPrice1, SendPro, FlowStatus, EFIndex, EFDesc, pageIndex, pageCount, ord, ref TotalCount); }
/// <summary> /// 保存销售发货单 /// </summary> /// <returns></returns> public static bool Update(Hashtable ht, SellOrderModel sellOrderModel, List<SellOrderDetailModel> sellOrderDetailModellList, List<SellOrderFeeDetailModel> sellOrderFeeDetailModelList, out string strMsg) { bool isSucc = false;//是否添加成功 //定义变量 string remark = string.Empty; strMsg = ""; try { isSucc = SellOrderDBHelper.Update(ht,sellOrderModel, sellOrderDetailModellList, sellOrderFeeDetailModelList, out strMsg); ; //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } catch (Exception ex) { //输出日志 SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, ConstUtil.MODULE_ID_SELLORDER_ADD); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } SellLogCommon.InsertLog(sellOrderModel.OrderNo, ConstUtil.MODULE_ID_SELLORDER_ADD, ConstUtil.CODING_RULE_TABLE_SELLORDER, remark, ConstUtil.LOG_PROCESS_UPDATE); return isSucc; }
/// <summary> /// 获取销售订单列表 /// </summary> /// <param name="sellContractModel">sellContractModel表实体</param> /// <param name="FlowStatus">审批状态</param> /// <returns></returns> public static DataTable GetOrderList(SellOrderModel sellOrderModel, decimal? TotalPrice1, string SendPro, int? FlowStatus,string EFIndex,string EFDesc, int pageIndex, int pageCount, string ord, ref int TotalCount) { string strSql = string.Empty; bool isMoreUnit = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsMoreUnit;//多计量单位 strSql += "select * from ( SELECT s.ID, s.OrderNo, s.Title,s.ModifiedDate, CONVERT(varchar(100), s.OrderDate, 23) AS OrderDate, "; strSql += "s.RealTotal, c.CustName, e.EmployeeName, "; strSql += "CASE s.FromType WHEN 0 THEN '无来源' WHEN 1 THEN '销售报价单' WHEN 2 THEN '销售合同' WHEN 3 THEN '销售机会' END AS FromTypeText, "; 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 "; strSql += "FROM officedba.FlowInstance "; strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 3 "; strSql += "ORDER BY ModifiedDate DESC) IS NULL THEN '' WHEN "; strSql += "(SELECT TOP 1 FlowStatus "; strSql += "FROM officedba.FlowInstance "; strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 3 "; strSql += "ORDER BY ModifiedDate DESC) = 1 THEN '待审批' WHEN "; strSql += "(SELECT TOP 1 FlowStatus "; strSql += "FROM officedba.FlowInstance "; strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 3 "; strSql += "ORDER BY ModifiedDate DESC) = 2 THEN '审批中' WHEN "; strSql += "(SELECT TOP 1 FlowStatus "; strSql += "FROM officedba.FlowInstance "; strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 3 "; strSql += "ORDER BY ModifiedDate DESC) = 3 THEN '审批通过' WHEN "; strSql += "(SELECT TOP 1 FlowStatus "; strSql += "FROM officedba.FlowInstance "; strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 3 "; strSql += "ORDER BY ModifiedDate DESC) = 4 THEN '审批不通过' WHEN "; strSql += "(SELECT TOP 1 FlowStatus "; strSql += "FROM officedba.FlowInstance "; strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 3 "; strSql += "ORDER BY ModifiedDate DESC) = 5 THEN '撤销审批' END AS FlowInstanceText, isnull(CASE "; strSql += "(((SELECT count(1) "; strSql += "FROM officedba.SellSend AS soo "; strSql += "WHERE soo.FromType = '1' AND soo.FromBillID = s.ID)+ "; strSql += "(SELECT count(1) "; strSql += "FROM officedba.SellSendDetail "; strSql += "WHERE FromType = '1' AND FromBillID = s.ID)+ "; strSql += "(SELECT count(1) "; strSql += "FROM officedba.PurchaseApplyDetailSource "; strSql += "WHERE FromType = '1' AND FromBillID = s.ID)+ "; strSql += "(SELECT count(1) "; strSql += "FROM officedba.BlendingDetails "; strSql += "WHERE BillingType = '1' AND BillCD = s.OrderNo and CompanyCD=s.CompanyCD) "; strSql += "+(SELECT count(1) "; strSql += "FROM officedba.MasterProductScheduleDetail "; strSql += "WHERE FromBillID = s.ID and CompanyCD=s.CompanyCD) "; strSql += ")) WHEN 0 THEN '无引用' END, '被引用') AS RefText, "; //发货状态Start strSql += "case when (SELECT isnull( sum(SendCount),0) as SendCount "; strSql += "FROM officedba.SellOrderDetail "; strSql += "WHERE OrderNo = s.OrderNo and CompanyCD=s.CompanyCD) =0 then '未发货' "; strSql += "when (SELECT isnull( sum(SendCount),0) as SendCount "; strSql += "FROM officedba.SellOrderDetail "; strSql += "WHERE OrderNo = s.OrderNo and CompanyCD=s.CompanyCD) >0 and "; strSql += "(SELECT isnull( sum(SendCount),0) as SendCount "; strSql += "FROM officedba.SellOrderDetail "; strSql += "WHERE OrderNo = s.OrderNo and CompanyCD=s.CompanyCD)< "; if (isMoreUnit) { strSql += "(SELECT isnull( sum(UsedUnitCount),0) as SendCount "; } else { strSql += "(SELECT isnull( sum(ProductCount),0) as SendCount "; } strSql += "FROM officedba.SellOrderDetail "; strSql += "WHERE OrderNo = s.OrderNo and CompanyCD=s.CompanyCD) "; strSql += "then '部分发货' "; strSql += "when (SELECT isnull( sum(SendCount),0) as SendCount "; strSql += "FROM officedba.SellOrderDetail "; strSql += "WHERE OrderNo = s.OrderNo and CompanyCD=s.CompanyCD) <>0 and "; strSql += "(SELECT isnull( sum(SendCount),0) as SendCount "; strSql += "FROM officedba.SellOrderDetail "; strSql += "WHERE OrderNo = s.OrderNo and CompanyCD=s.CompanyCD)>= "; if (isMoreUnit) { strSql += "(SELECT isnull( sum(UsedUnitCount),0) as SendCount "; } else { strSql += "(SELECT isnull( sum(ProductCount),0) as SendCount "; } strSql += "FROM officedba.SellOrderDetail "; strSql += "WHERE OrderNo = s.OrderNo and CompanyCD=s.CompanyCD) "; strSql += "then '已发货' "; strSql += "end as isSendText, "; //发货状态End strSql += "isnull( (SELECT isnull(sum(YAccounts),0) "; strSql += "FROM officedba.BlendingDetails "; strSql += "WHERE BillingType = '1' AND BillCD = s.OrderNo and CompanyCD=s.CompanyCD),0) as YAccounts, "; strSql += " case when (isnull( "; strSql += "(SELECT isnull(sum(YAccounts),0) "; strSql += "FROM officedba.BlendingDetails "; strSql += "WHERE BillingType = '1' AND BillCD = s.OrderNo and CompanyCD=s.CompanyCD),0))>0 "; strSql += "and (isnull( "; strSql += "(SELECT isnull(sum(YAccounts),0) "; strSql += "FROM officedba.BlendingDetails "; strSql += "WHERE BillingType = '1' AND BillCD = s.OrderNo and CompanyCD=s.CompanyCD),0)) < "; strSql += "(isnull( "; strSql += "(SELECT isnull(max(TotalPrice),0) "; strSql += "FROM officedba.BlendingDetails "; strSql += "WHERE BillingType = '1' AND BillCD = s.OrderNo and CompanyCD=s.CompanyCD),0)) "; strSql += " then '部分回款' "; strSql += "when "; strSql += "(isnull( "; strSql += "(SELECT isnull(sum(YAccounts),0) "; strSql += "FROM officedba.BlendingDetails "; strSql += "WHERE BillingType = '1' AND BillCD = s.OrderNo and CompanyCD=s.CompanyCD),0)) = "; strSql += "(isnull( "; strSql += "(SELECT isnull(max(TotalPrice),0) "; strSql += "FROM officedba.BlendingDetails "; strSql += "WHERE BillingType = '1' AND BillCD = s.OrderNo and CompanyCD=s.CompanyCD),0)) and "; strSql += "(isnull( "; strSql += "(SELECT isnull(sum(YAccounts),0) "; strSql += "FROM officedba.BlendingDetails "; strSql += "WHERE BillingType = '1' AND BillCD = s.OrderNo and CompanyCD=s.CompanyCD),0))<>0 "; strSql += " then '已回款' "; strSql += "when (isnull( "; strSql += "(SELECT isnull(sum(YAccounts),0) "; strSql += "FROM officedba.BlendingDetails "; strSql += "WHERE BillingType = '1' AND BillCD = s.OrderNo and CompanyCD=s.CompanyCD),0))=0 then '未回款' end as IsAccText , "; strSql += "(SELECT TOP 1 FlowStatus "; strSql += "FROM officedba.FlowInstance "; strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 3 "; strSql += "ORDER BY ModifiedDate DESC) AS FlowStatus "; strSql += ",case s.isOpenbill when '0' then '未建单' when '1' then '已建单' end as isOpenbillText "; strSql += ", CASE s.FromType WHEN 0 THEN '' WHEN 1 THEN (select isnull(OfferNo,'') as "; strSql += " OfferNo from officedba.SellOffer where ID=s.FromBillID) "; strSql += "WHEN 2 THEN (select isnull(ContractNo,'') as ContractNo from officedba.SellContract "; strSql += "where ID=s.FromBillID) "; strSql += "WHEN 3 THEN (select isnull(ChanceNo,'') as ChanceNo from officedba.SellChance "; strSql += "where ID=s.FromBillID) END AS FromBillNo "; strSql += "FROM officedba.SellOrder AS s LEFT JOIN "; strSql += "officedba.CustInfo AS c ON s.CustID = c.ID LEFT JOIN "; strSql += "officedba.EmployeeInfo AS e ON s.Seller = e.ID where 1=1 and s.CompanyCD=@CompanyCD "; //过滤单据:显示当前用户拥有权限查看的单据 int empid = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID; strSql += " and ( charindex('," + empid + ",' , ','+s.CanViewUser+',')>0 or s.Creator=" + empid + " OR s.CanViewUser='' OR s.CanViewUser is null) "; string strCompanyCD = string.Empty;//单位编号 strCompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; ArrayList arr = new ArrayList(); //扩展属性 if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { strSql += " and s.ExtField"+EFIndex+" like @EFDesc "; arr.Add(new SqlParameter("@EFDesc", "%" + EFDesc + "%")); } arr.Add(new SqlParameter("@CompanyCD", strCompanyCD)); if (sellOrderModel.ProjectID != null) { strSql += " and s.ProjectID=@ProjectID "; arr.Add(new SqlParameter("@ProjectID", sellOrderModel.ProjectID)); } if (sellOrderModel.BillStatus != null) { strSql += " and s.BillStatus= @BillStatus"; arr.Add(new SqlParameter("@BillStatus", sellOrderModel.BillStatus)); } if (sellOrderModel.FromType != null) { strSql += " and s.FromType=@FromType"; arr.Add(new SqlParameter("@FromType", sellOrderModel.FromType)); } if (sellOrderModel.FromBillID != null) { strSql += " and s.FromBillID=@FromBillID"; arr.Add(new SqlParameter("@FromBillID", sellOrderModel.FromBillID)); } if (sellOrderModel.OrderNo != null) { strSql += " and s.OrderNo like @OrderNo"; arr.Add(new SqlParameter("@OrderNo", "%" + sellOrderModel.OrderNo + "%")); } if (sellOrderModel.TotalPrice != null) { strSql += " and s.TotalPrice >= @TotalPrice"; arr.Add(new SqlParameter("@TotalPrice", sellOrderModel.TotalPrice)); } if (TotalPrice1 != null) { strSql += " and s.TotalPrice <= @TotalPrice1"; arr.Add(new SqlParameter("@TotalPrice1", TotalPrice1)); } if (sellOrderModel.CustID != null) { strSql += " and s.CustID=@CustID"; arr.Add(new SqlParameter("@CustID", sellOrderModel.CustID)); } if (sellOrderModel.Title != null) { strSql += " and s.Title like @Title"; arr.Add(new SqlParameter("@Title", "%" + sellOrderModel.Title + "%")); } if (sellOrderModel.Seller != null) { strSql += " and s.Seller=@Seller"; arr.Add(new SqlParameter("@Seller", sellOrderModel.Seller)); } if (sellOrderModel.isOpenbill != null) { strSql += " and s.isOpenbill=@isOpenbill"; arr.Add(new SqlParameter("@isOpenbill", sellOrderModel.isOpenbill)); } 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 "; } } if (SendPro != null) { strSql += " and f.isSendText=@isSendText"; arr.Add(new SqlParameter("@isSendText", SendPro)); } return SqlHelper.CreateSqlByPageExcuteSqlArr(strSql.ToString(), pageIndex, pageCount, ord, arr, ref TotalCount); }
/// <summary> /// 为主表插入数据 /// </summary> /// <param name="sellOrderModel"></param> /// <param name="tran"></param> private static void InsertSellOrder(Hashtable htExtAttr, SellOrderModel sellOrderModel, TransactionManager tran) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into officedba.SellOrder("); strSql.Append("CompanyCD,CustID,CustTel,OrderNo,Title,FromType,FromBillID,Seller,SellDeptId,SellType,BusiType,OrderMethod,PayType,MoneyType,CarryType,TakeType,CurrencyType,Rate,TotalPrice,Tax,TotalFee,Discount,SaleFeeTotal,DiscountTotal,RealTotal,isAddTax,CountTotal,SendDate,OrderDate,StartDate,EndDate,TheyDelegate,OurDelegate,Status,PayRemark,DeliverRemark,PackTransit,StatusNote,CustOrderNo,Remark,Attachment,BillStatus,Creator,CreateDate,ModifiedDate,ModifiedUserID,CanViewUser,ProjectID)"); strSql.Append(" values ("); strSql.Append("@CompanyCD,@CustID,@CustTel,@OrderNo,@Title,@FromType,@FromBillID,@Seller,@SellDeptId,@SellType,@BusiType,@OrderMethod,@PayType,@MoneyType,@CarryType,@TakeType,@CurrencyType,@Rate,@TotalPrice,@Tax,@TotalFee,@Discount,@SaleFeeTotal,@DiscountTotal,@RealTotal,@isAddTax,@CountTotal,@SendDate,@OrderDate,@StartDate,@EndDate,@TheyDelegate,@OurDelegate,@Status,@PayRemark,@DeliverRemark,@PackTransit,@StatusNote,@CustOrderNo,@Remark,@Attachment,@BillStatus,@Creator,getdate(),getdate(),@ModifiedUserID,@CanViewUser,@ProjectID)"); SqlParameter[] param = null; ArrayList lcmd = new ArrayList(); #region 参数 lcmd.Add(SqlHelper.GetParameterFromString("@CompanyCD", sellOrderModel.CompanyCD)); lcmd.Add(SqlHelper.GetParameterFromString("@CustID", sellOrderModel.CustID.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CustTel", sellOrderModel.CustTel)); lcmd.Add(SqlHelper.GetParameterFromString("@OrderNo", sellOrderModel.OrderNo)); lcmd.Add(SqlHelper.GetParameterFromString("@Title", sellOrderModel.Title)); lcmd.Add(SqlHelper.GetParameterFromString("@FromType", sellOrderModel.FromType)); lcmd.Add(SqlHelper.GetParameterFromString("@FromBillID", sellOrderModel.FromBillID.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Seller", sellOrderModel.Seller.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SellDeptId", sellOrderModel.SellDeptId.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SellType", sellOrderModel.SellType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@BusiType", sellOrderModel.BusiType)); lcmd.Add(SqlHelper.GetParameterFromString("@OrderMethod", sellOrderModel.OrderMethod.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@PayType", sellOrderModel.PayType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@MoneyType", sellOrderModel.MoneyType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CarryType", sellOrderModel.CarryType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@TakeType", sellOrderModel.TakeType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CurrencyType", sellOrderModel.CurrencyType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Rate", sellOrderModel.Rate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@TotalPrice", sellOrderModel.TotalPrice.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Tax", sellOrderModel.Tax.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@TotalFee", sellOrderModel.TotalFee.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Discount", sellOrderModel.Discount.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SaleFeeTotal", sellOrderModel.SaleFeeTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@DiscountTotal", sellOrderModel.DiscountTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@RealTotal", sellOrderModel.RealTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@isAddTax", sellOrderModel.isAddTax.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CountTotal", sellOrderModel.CountTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SendDate", sellOrderModel.SendDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@OrderDate", sellOrderModel.OrderDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@StartDate", sellOrderModel.StartDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@EndDate", sellOrderModel.EndDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@TheyDelegate", sellOrderModel.TheyDelegate)); lcmd.Add(SqlHelper.GetParameterFromString("@OurDelegate", sellOrderModel.OurDelegate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Status", sellOrderModel.Status)); lcmd.Add(SqlHelper.GetParameterFromString("@PayRemark", sellOrderModel.PayRemark)); lcmd.Add(SqlHelper.GetParameterFromString("@DeliverRemark", sellOrderModel.DeliverRemark)); lcmd.Add(SqlHelper.GetParameterFromString("@PackTransit", sellOrderModel.PackTransit)); lcmd.Add(SqlHelper.GetParameterFromString("@StatusNote", sellOrderModel.StatusNote)); lcmd.Add(SqlHelper.GetParameterFromString("@CustOrderNo", sellOrderModel.CustOrderNo)); lcmd.Add(SqlHelper.GetParameterFromString("@Remark", sellOrderModel.Remark)); lcmd.Add(SqlHelper.GetParameterFromString("@Attachment", sellOrderModel.Attachment)); lcmd.Add(SqlHelper.GetParameterFromString("@BillStatus", sellOrderModel.BillStatus)); lcmd.Add(SqlHelper.GetParameterFromString("@Creator", sellOrderModel.Creator.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", sellOrderModel.ModifiedUserID)); lcmd.Add(SqlHelper.GetParameterFromString("@CanViewUser", sellOrderModel.CanViewUser)); lcmd.Add(SqlHelper.GetParameterFromString("@ProjectID", sellOrderModel.ProjectID.ToString())); #endregion #region 拓展属性 if (htExtAttr != null && htExtAttr.Count != 0) { strSql.Append(" ;UPDATE officedba.SellOrder 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 OrderNo=@OrderNo "); } 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="sellOrderModel"></param> /// <param name="tran"></param> private static void UpdateSellOrder(Hashtable htExtAttr, SellOrderModel sellOrderModel, TransactionManager tran) { StringBuilder strSql = new StringBuilder(); #region sql语句 strSql.Append("update officedba.SellOrder set "); strSql.Append("CustID=@CustID,"); strSql.Append("CustTel=@CustTel,"); strSql.Append("Title=@Title,"); strSql.Append("FromType=@FromType,"); strSql.Append("FromBillID=@FromBillID,"); strSql.Append("Seller=@Seller,"); strSql.Append("SellDeptId=@SellDeptId,"); strSql.Append("SellType=@SellType,"); strSql.Append("BusiType=@BusiType,"); strSql.Append("OrderMethod=@OrderMethod,"); strSql.Append("PayType=@PayType,"); strSql.Append("MoneyType=@MoneyType,"); strSql.Append("CarryType=@CarryType,"); strSql.Append("TakeType=@TakeType,"); strSql.Append("CurrencyType=@CurrencyType,"); strSql.Append("Rate=@Rate,"); strSql.Append("TotalPrice=@TotalPrice,"); strSql.Append("Tax=@Tax,"); strSql.Append("TotalFee=@TotalFee,"); strSql.Append("Discount=@Discount,"); strSql.Append("SaleFeeTotal=@SaleFeeTotal,"); strSql.Append("DiscountTotal=@DiscountTotal,"); strSql.Append("RealTotal=@RealTotal,"); strSql.Append("isAddTax=@isAddTax,"); strSql.Append("CountTotal=@CountTotal,"); strSql.Append("SendDate=@SendDate,"); strSql.Append("OrderDate=@OrderDate,"); strSql.Append("StartDate=@StartDate,"); strSql.Append("EndDate=@EndDate,"); strSql.Append("TheyDelegate=@TheyDelegate,"); strSql.Append("OurDelegate=@OurDelegate,"); strSql.Append("Status=@Status,"); strSql.Append("PayRemark=@PayRemark,"); strSql.Append("DeliverRemark=@DeliverRemark,"); strSql.Append("PackTransit=@PackTransit,"); strSql.Append("StatusNote=@StatusNote,"); strSql.Append("CustOrderNo=@CustOrderNo,"); strSql.Append("Remark=@Remark,"); strSql.Append("Attachment=@Attachment,"); strSql.Append("BillStatus=@BillStatus,"); strSql.Append("ModifiedDate=getdate(),"); strSql.Append("ModifiedUserID=@ModifiedUserID"); strSql.Append(",CanViewUser=@CanViewUser"); strSql.Append(",ProjectID=@ProjectID"); strSql.Append(" where CompanyCD=@CompanyCD and OrderNo=@OrderNo "); #endregion #region 参数 //SqlParameter[] parameters = { // new SqlParameter("@CompanyCD", SqlDbType.VarChar,8), // new SqlParameter("@CustID", SqlDbType.Int,4), // new SqlParameter("@CustTel", SqlDbType.VarChar,100), // new SqlParameter("@OrderNo", SqlDbType.VarChar,50), // new SqlParameter("@Title", SqlDbType.VarChar,100), // new SqlParameter("@FromType", SqlDbType.Char,1), // new SqlParameter("@FromBillID", SqlDbType.Int,4), // new SqlParameter("@Seller", SqlDbType.Int,4), // new SqlParameter("@SellDeptId", SqlDbType.Int,4), // new SqlParameter("@SellType", SqlDbType.Int,4), // new SqlParameter("@BusiType", SqlDbType.Char,1), // new SqlParameter("@OrderMethod", SqlDbType.Int,4), // new SqlParameter("@PayType", SqlDbType.Int,4), // new SqlParameter("@MoneyType", SqlDbType.Int,4), // new SqlParameter("@CarryType", SqlDbType.Int,4), // new SqlParameter("@TakeType", SqlDbType.Int,4), // new SqlParameter("@CurrencyType", SqlDbType.Int,4), // new SqlParameter("@Rate", SqlDbType.Decimal,9), // new SqlParameter("@TotalPrice", SqlDbType.Decimal,9), // new SqlParameter("@Tax", SqlDbType.Decimal,9), // new SqlParameter("@TotalFee", SqlDbType.Decimal,9), // new SqlParameter("@Discount", SqlDbType.Decimal,9), // new SqlParameter("@SaleFeeTotal", SqlDbType.Decimal,9), // new SqlParameter("@DiscountTotal", SqlDbType.Decimal,9), // new SqlParameter("@RealTotal", SqlDbType.Decimal,9), // new SqlParameter("@isAddTax", SqlDbType.Char,1), // new SqlParameter("@CountTotal", SqlDbType.Decimal,9), // new SqlParameter("@SendDate", SqlDbType.DateTime), // new SqlParameter("@OrderDate", SqlDbType.DateTime), // new SqlParameter("@StartDate", SqlDbType.DateTime), // new SqlParameter("@EndDate", SqlDbType.DateTime), // new SqlParameter("@TheyDelegate", SqlDbType.VarChar,50), // new SqlParameter("@OurDelegate", SqlDbType.Int,4), // new SqlParameter("@Status", SqlDbType.Char,1), // new SqlParameter("@PayRemark", SqlDbType.VarChar,200), // new SqlParameter("@DeliverRemark", SqlDbType.VarChar,200), // new SqlParameter("@PackTransit", SqlDbType.VarChar,200), // new SqlParameter("@StatusNote", SqlDbType.VarChar,100), // new SqlParameter("@CustOrderNo", SqlDbType.VarChar,100), // new SqlParameter("@Remark", SqlDbType.VarChar,200), // new SqlParameter("@Attachment", SqlDbType.VarChar,150), // new SqlParameter("@BillStatus", SqlDbType.Char,1), // new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,20)}; //parameters[0].Value = sellOrderModel.CompanyCD; //parameters[1].Value = sellOrderModel.CustID; //parameters[2].Value = sellOrderModel.CustTel; //parameters[3].Value = sellOrderModel.OrderNo; //parameters[4].Value = sellOrderModel.Title; //parameters[5].Value = sellOrderModel.FromType; //parameters[6].Value = sellOrderModel.FromBillID; //parameters[7].Value = sellOrderModel.Seller; //parameters[8].Value = sellOrderModel.SellDeptId; //parameters[9].Value = sellOrderModel.SellType; //parameters[10].Value = sellOrderModel.BusiType; //parameters[11].Value = sellOrderModel.OrderMethod; //parameters[12].Value = sellOrderModel.PayType; //parameters[13].Value = sellOrderModel.MoneyType; //parameters[14].Value = sellOrderModel.CarryType; //parameters[15].Value = sellOrderModel.TakeType; //parameters[16].Value = sellOrderModel.CurrencyType; //parameters[17].Value = sellOrderModel.Rate; //parameters[18].Value = sellOrderModel.TotalPrice; //parameters[19].Value = sellOrderModel.Tax; //parameters[20].Value = sellOrderModel.TotalFee; //parameters[21].Value = sellOrderModel.Discount; //parameters[22].Value = sellOrderModel.SaleFeeTotal; //parameters[23].Value = sellOrderModel.DiscountTotal; //parameters[24].Value = sellOrderModel.RealTotal; //parameters[25].Value = sellOrderModel.isAddTax; //parameters[26].Value = sellOrderModel.CountTotal; //parameters[27].Value = sellOrderModel.SendDate; //parameters[28].Value = sellOrderModel.OrderDate; //parameters[29].Value = sellOrderModel.StartDate; //parameters[30].Value = sellOrderModel.EndDate; //parameters[31].Value = sellOrderModel.TheyDelegate; //parameters[32].Value = sellOrderModel.OurDelegate; //parameters[33].Value = sellOrderModel.Status; //parameters[34].Value = sellOrderModel.PayRemark; //parameters[35].Value = sellOrderModel.DeliverRemark; //parameters[36].Value = sellOrderModel.PackTransit; //parameters[37].Value = sellOrderModel.StatusNote; //parameters[38].Value = sellOrderModel.CustOrderNo; //parameters[39].Value = sellOrderModel.Remark; //parameters[40].Value = sellOrderModel.Attachment; //parameters[41].Value = sellOrderModel.BillStatus; //parameters[42].Value = sellOrderModel.ModifiedUserID; //foreach (SqlParameter para in parameters) //{ // if (para.Value == null) // { // para.Value = DBNull.Value; // } //} #endregion SqlParameter[] param = null; ArrayList lcmd = new ArrayList(); #region 参数 lcmd.Add(SqlHelper.GetParameterFromString("@CompanyCD", sellOrderModel.CompanyCD)); lcmd.Add(SqlHelper.GetParameterFromString("@CustID", sellOrderModel.CustID.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CustTel", sellOrderModel.CustTel)); lcmd.Add(SqlHelper.GetParameterFromString("@OrderNo", sellOrderModel.OrderNo)); lcmd.Add(SqlHelper.GetParameterFromString("@Title", sellOrderModel.Title)); lcmd.Add(SqlHelper.GetParameterFromString("@FromType", sellOrderModel.FromType)); lcmd.Add(SqlHelper.GetParameterFromString("@FromBillID", sellOrderModel.FromBillID.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Seller", sellOrderModel.Seller.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SellDeptId", sellOrderModel.SellDeptId.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SellType", sellOrderModel.SellType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@BusiType", sellOrderModel.BusiType)); lcmd.Add(SqlHelper.GetParameterFromString("@OrderMethod", sellOrderModel.OrderMethod.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@PayType", sellOrderModel.PayType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@MoneyType", sellOrderModel.MoneyType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CarryType", sellOrderModel.CarryType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@TakeType", sellOrderModel.TakeType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CurrencyType", sellOrderModel.CurrencyType.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Rate", sellOrderModel.Rate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@TotalPrice", sellOrderModel.TotalPrice.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Tax", sellOrderModel.Tax.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@TotalFee", sellOrderModel.TotalFee.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Discount", sellOrderModel.Discount.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SaleFeeTotal", sellOrderModel.SaleFeeTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@DiscountTotal", sellOrderModel.DiscountTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@RealTotal", sellOrderModel.RealTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@isAddTax", sellOrderModel.isAddTax.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@CountTotal", sellOrderModel.CountTotal.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@SendDate", sellOrderModel.SendDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@OrderDate", sellOrderModel.OrderDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@StartDate", sellOrderModel.StartDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@EndDate", sellOrderModel.EndDate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@TheyDelegate", sellOrderModel.TheyDelegate)); lcmd.Add(SqlHelper.GetParameterFromString("@OurDelegate", sellOrderModel.OurDelegate.ToString())); lcmd.Add(SqlHelper.GetParameterFromString("@Status", sellOrderModel.Status)); lcmd.Add(SqlHelper.GetParameterFromString("@PayRemark", sellOrderModel.PayRemark)); lcmd.Add(SqlHelper.GetParameterFromString("@DeliverRemark", sellOrderModel.DeliverRemark)); lcmd.Add(SqlHelper.GetParameterFromString("@PackTransit", sellOrderModel.PackTransit)); lcmd.Add(SqlHelper.GetParameterFromString("@StatusNote", sellOrderModel.StatusNote)); lcmd.Add(SqlHelper.GetParameterFromString("@CustOrderNo", sellOrderModel.CustOrderNo)); lcmd.Add(SqlHelper.GetParameterFromString("@Remark", sellOrderModel.Remark)); lcmd.Add(SqlHelper.GetParameterFromString("@Attachment", sellOrderModel.Attachment)); lcmd.Add(SqlHelper.GetParameterFromString("@BillStatus", sellOrderModel.BillStatus)); lcmd.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", sellOrderModel.ModifiedUserID)); lcmd.Add(SqlHelper.GetParameterFromString("@CanViewUser", sellOrderModel.CanViewUser)); lcmd.Add(SqlHelper.GetParameterFromString("@ProjectID", sellOrderModel.ProjectID.ToString())); #endregion #region 拓展属性 if (htExtAttr != null && htExtAttr.Count != 0) { strSql.Append(" ;UPDATE officedba.SellOrder 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 OrderNo=@OrderNo "); } 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); }