用友ERT T6 技术解析(四)采购入库上

2019-04-14 19:12发布生成海报

站内文章 / 模拟电子

15143 0

第二章功能实现

2.1 界面信息 2.1.1 登录 2.3 采购管理 2.3.1 采购入库 介绍：采购入库这个模块，是采购订单和审核订单与入库订单的集合模块。左边dgv是单据信息，右边dgv是左边dgv选择的单据明细列表，表头显示框为单据主要内容，表尾为单据部分内容。主界面（2.3.1图1）功能：【导出单据】可以将单据导出到Excel文档。【导出明细】将单据下的明细导出到Excel文档。【筛选】可以对单据进行筛选，如图（2.3.1 图2）。【增加】新增一条采购单据，同时某些按钮不能同时使用，只能进行放弃操作。【修改】对单据进行修改。【删除】对未审核的订单进行对订单进行失效处理。【保存】在新增/修改状态下才能启用的按钮，对订单进行保存。【放弃】在新增/修改状态下才能启用的按钮，对订单进行放弃当前的全部操作。【入库】对已经审核过的采购订单进行入库。【首页】因为当前显示的订单数默认是每页30条，可以自行设置大小，所以已经将单据进行分页处理，首页代表最前面30条数据。【下页】第（页数*页大小~页数*页大小 + 30）之间的30条数据。【上页】第（页数*页大小~页数*页大小 + 30）之间的30条数据。【末页】最后面小于等于30条的数据。【刷新】对当前数据进行刷新。【退出】退出页面，并且把当前界面进行截图进行保存在另一窗体（类似QQ的窗体）可以随时查看已近打开过的窗体。【加载】对当前页面的dgv单据信息进行页条数加载，每页详细多少条可以自行设置。。主界面：

2.3.1（图1）单击【筛选】按钮显示单据筛选框，如图，筛选框可随意移动。筛选后的信息也是按照分页来算，每页显示多少条，自行设定。

2.3.1（图2）从界面上可以看到00我们这里用到的控件有控件名称说明日期控件（DateTimePicker）控件可以在工具箱直接拖动至窗体，拖至窗体后右击属性可以修改控件的样式和各种属性，还可以编辑事件。下拉框（ComboBox）文本（TextBox）按钮（Button）表格（DataGridView）容器（Panel）工具栏（ToolStrip） 查询功能实现： 第一步：数据库 1、表与关系

2.3.1（图3）
表1: 采购入库表（PurchaseReceiptTable） 列名数据类型主键/外键说明 PurchaseReceiptID int - Identity 主键采购入库单ID DocumentsID_PurchaseOrder int 外键单据ID_采购单 DocumentsID_GRN int 外键单据ID_入库单 InAndOutOfInventoryRecordID int 外键出入库存记录ID DocumentsID_ToInvoice int 外键单据ID_到货单 BusinessNo nchar (10) 业务号 CustomerID int 外键客户ID AuditDate datetime 审核日期 Note nchar (100) 备注 ReviewWhether bit 审核否 TheOperatorID_MakingPeople int 外键操作员ID_制单人 TheOperatorID_Reviewer int 外键操作员ID_审核人 TheOperatorID_personOfChargeToAnAccount int 外键操作员ID_记账人 TheOperatorID_Salesman int 外键操作员ID_业务员 BusinessTypeID int 外键业务类型ID 表2: 采购入库明细表（PurchasingScheduleOfPutInStorage） 列名数据类型主键/外键说明 ProcurementWarehousingDetailID int - Identity 主键采购入库明细ID TheInventoryID int 外键存货ID Quantity nchar (10) 数量 PurchaseReceiptID int 外键采购入库单ID 表3: 单据集合表（TheDocumentsTable） 列名数据类型主键/外键说明 DocumentID int - Identity 主键单据ID NameOfDocuments nchar (10) 单据名称 DocumentNumber nchar (20) 单据编号 DateOfDocuments datetime 单据日期 TypeOfDocumen nchar (10) 单据类型 InThisPaper nchar (100) 摘要 表4: 出入库记录表（InAndOutOfInventoryRecord） 列名数据类型主键/外键说明 InAndOutOfInventoryRecordID int - Identity 主键出入库存记录ID WarehouseID_Dispatch int 外键仓库ID_出库 ForTheTypeID int 外键出入库类型ID WarehouseInventory_ID int 外键仓库ID_入库 OoperateDate datetime 操作日期 表5: 出入库记录表（InAndOutOfInventoryRecordList） 列名数据类型主键/外键说明 InAndOutOfInventoryRecordListID int - Identity 主键出入库记录明细ID Quantity decimal (18, 3) 数量 TheUnitPrice decimal (18, 3) 单价 InAndOutOfInventoryRecordID int 外键存货ID TheInventoryID int 外键出入库存记录ID 2、绑定下拉框 第一步：数据库的存储过程

if(@Type='frm_PurchaseOddNumbers_Load_SelectClientele')  --查询供应商
   BEGIN
      SELECT LTRIM(RTRIM(CustomerID)) AS 供应商ID,
       LTRIM(RTRIM(TheCustomerName)) AS 供应商名称
      FROM  TheCustomerTable
   END

第二步：逻辑层（BLL）代码

        ///
        ///供应商查询
        ///
        ///
        [OperationContract]
        public DataSetfrm_PurchaseOddNumbers_Load_SelectClientele()
        {
            SqlParameter[]SQlCMDpas = {
                                 new SqlParameter("@Type",SqlDbType.Char),   //存储过程的定义方法的 参数 与参数类型
                                        };
            SQlCMDpas[0].Value = "frm_PurchaseOddNumbers_Load_SelectClientele";   //为该参数赋值  对应存储过程的某对应方法
            DataTabledt = myDALMethod.QueryDataTable("PurchaseManage_frm_PurchaseOddNumbers",SQlCMDpas);      //对应存储过程名称获取查询数据表
            DataSetds = new DataSet();     //实例化数据集
            ds.Tables.Add(dt);             //添加数据表到数据集
            return ds;                     //返回数据集
        }

第三步：界面层(UIL)代码，在窗体的Load事件中绑定下拉框的数据 截图效果：

        BLL用友T6.采购管理.frm_PurchaseOddNumbers.frm_PurchaseOddNumbersClientmyfrm_PurchaseOddNumbers =
            newBLL用友T6.采购管理.frm_PurchaseOddNumbers.frm_PurchaseOddNumbersClient();     //实例化采购入库BLL 逻辑层
 
        BLL用友T6.采购管理.frm_ProductBeLaidUp.frm_ProductBeLaidUpClient myfrm_ProductBeLaidUp =
            newBLL用友T6.采购管理.frm_ProductBeLaidUp.frm_ProductBeLaidUpClient();            //实例化产成品入库 BLL 逻辑层 
 
 
///
///绑定供应商下拉框
///
    private void BindingSupplier()
    {
       cboSupplyOfMaterialUnit.DataSource =myfrm_PurchaseOddNumbers.frm_PurchaseOddNumbers_Load_SelectClientele().Tables[0];//获取数据源并绑定到下拉框
       cboSupplyOfMaterialUnit.DisplayMember = "供应商名称"; //绑定下拉框的显示成员
       cboSupplyOfMaterialUnit.ValueMember = "供应商ID";     //绑定下拉框的值成员
    }

3、绑定表格数据和分页功能和表头显示框（DataGridView单据数据）
第一步：数据库的存储过程

@LoadCount INT = 0 , --要加载的条数
   @BeginCount INT = 0  --不要前面多少条数据
 
if(@Type='frm_PurchaseOddNumbers_Load_SelectPurchaseOrderForm')--查询采购单
    BEGIN
       SELECT TOP(@LoadCount) LTRIM(RTRIM(TheDocumentsTable.DocumentNumber)) AS 采购单号,
       (CASEWHEN PurchaseReceiptTable.DocumentsID_GRN = 0 AND PurchaseReceiptTable.ReviewWhether = 1 THEN '未入库'WHEN PurchaseReceiptTable.DocumentsID_GRN = 0 AND PurchaseReceiptTable.ReviewWhether = 0 THEN '待审核'WHEN PurchaseReceiptTable.DocumentsID_GRN != 0 THEN TheDocumentsTable_1.DocumentNumber END) AS 入库单号,
       LTRIM(RTRIM(PurchaseReceiptTable.PurchaseReceiptID)) AS 采购入库单ID, LTRIM(RTRIM(PurchaseReceiptTable.DocumentsID_PurchaseOrder)) AS 单据ID_采购单,
       LTRIM(RTRIM(PurchaseReceiptTable.DocumentsID_GRN)) AS 单据ID_入库单,LTRIM(RTRIM(PurchaseReceiptTable.InAndOutOfInventoryRecordID))AS 出入库存记录ID,
       LTRIM(RTRIM(PurchaseReceiptTable.DocumentsInvoice)) AS 提单号,LTRIM(RTRIM(PurchaseReceiptTable.BusinessNo)) AS 业务号, LTRIM(RTRIM(PurchaseReceiptTable.CustomerID))
       AS 客户ID, LTRIM(RTRIM(PurchaseReceiptTable.AuditDate)) AS 审核日期,LTRIM(RTRIM(PurchaseReceiptTable.Note)) AS 备注,
       (CASEWHEN PurchaseReceiptTable.ReviewWhether = 0 THEN  '未审核'WHEN PurchaseReceiptTable.ReviewWhether = 1 THEN  '已审核'END) AS 审核否,
       LTRIM(RTRIM(PurchaseReceiptTable.TheOperatorID_MakingPeople)) AS 操作员ID_制单人,LTRIM(RTRIM(PurchaseReceiptTable.TheOperatorID_Reviewer))AS 操作员ID_审核人,
       LTRIM(RTRIM(PurchaseReceiptTable.TheOperatorID_personOfChargeToAnAccount)) AS 操作员ID_记账人,LTRIM(RTRIM(PurchaseReceiptTable.TheOperatorID_Salesman))AS 操作员ID_业务员,
       (CAST(TheDocumentsTable_1.DateOfDocuments ASDATETIME)) AS 入库日期,(CAST(TheDocumentsTable.DateOfDocumentsAS DATETIME)) AS 采购日期,
       LTRIM(RTRIM(InAndOutOfInventoryRecord.WarehouseInventory_ID)) AS 入库仓库ID, LTRIM(RTRIM(ForTheTypeOfTable.ForTheTypeName)) AS 入库类型,
       LTRIM(RTRIM(WarehouseTable.TheNameOfTheWarehouse)) AS 入库仓库,LTRIM(RTRIM(TheCustomerTable.TheCustomerName)) AS 供货单位,
       LTRIM(RTRIM(TheOperatorTable_2.TheOperatorTypeName)) AS 业务员名称,LTRIM(RTRIM(DepartmentTable.DepartmentName)) AS 部门名称,
       LTRIM(RTRIM(TheOperatorTable_1.TheOperatorTypeName)) AS 制单员,LTRIM(RTRIM(TheOperatorTable_3.TheOperatorTypeName))AS 审核员,
       LTRIM(RTRIM(TheOperatorTable.TheOperatorTypeName)) AS 记账员,LTRIM(RTRIM(BusinessTypeTable.BusinessTypeNname)) AS 业务类型, LTRIM(RTRIM(PurchaseReceiptTable.BusinessTypeID))
       AS 业务类型ID, LTRIM(RTRIM(TheCustomerTable.CustomerID)) AS 供应商ID, LTRIM(RTRIM(InAndOutOfInventoryRecord.ForTheTypeID)) AS 出入库类型ID
       FROM         PurchaseReceiptTableLEFT JOIN
       BusinessTypeTable ON PurchaseReceiptTable.BusinessTypeID = BusinessTypeTable.BusinessTypeID LEFT OUTER JOIN
       TheDocumentsTable ON PurchaseReceiptTable.DocumentsID_PurchaseOrder= TheDocumentsTable.DocumentID LEFT OUTER JOIN
       TheDocumentsTable AS TheDocumentsTable_1ON PurchaseReceiptTable.DocumentsID_GRN = TheDocumentsTable_1.DocumentID LEFT OUTER JOIN
       InAndOutOfInventoryRecord ON PurchaseReceiptTable.InAndOutOfInventoryRecordID= InAndOutOfInventoryRecord.InAndOutOfInventoryRecordIDLEFT OUTER JOIN
       ForTheTypeOfTable ON InAndOutOfInventoryRecord.ForTheTypeID = ForTheTypeOfTable.ForTheTypeID LEFT OUTER JOIN
       WarehouseTable ON InAndOutOfInventoryRecord.WarehouseInventory_ID= WarehouseTable.WarehouseID  LEFT OUTER JOIN
       TheCustomerTable ON PurchaseReceiptTable.CustomerID = TheCustomerTable.CustomerID LEFT OUTER JOIN
       TheOperatorTable AS TheOperatorTable_2ON PurchaseReceiptTable.TheOperatorID_Salesman= TheOperatorTable_2.TheOperatorID LEFT OUTER JOIN
       DepartmentTable ON TheOperatorTable_2.DepartmentID = DepartmentTable.DepartmentID LEFT OUTER JOIN
       TheOperatorTable AS TheOperatorTable_1ON PurchaseReceiptTable.TheOperatorID_MakingPeople= TheOperatorTable_1.TheOperatorID LEFT OUTER JOIN
       TheOperatorTable AS TheOperatorTable_3ON PurchaseReceiptTable.TheOperatorID_Reviewer= TheOperatorTable_3.TheOperatorID LEFT OUTER JOIN
       TheOperatorTable ON PurchaseReceiptTable.TheOperatorID_personOfChargeToAnAccount= TheOperatorTable.TheOperatorID
       WHERE 1 = 1 AND PurchaseReceiptTable.EffectiveNo = 1 AND PurchaseReceiptTable.PurchaseReceiptID  not in
       (SELECTTOP(@BeginCount) PurchaseReceiptTable.PurchaseReceiptID FROMPurchaseReceiptTable  INNER JOIN
       TheDocumentsTable ON PurchaseReceiptTable.DocumentsID_PurchaseOrder= TheDocumentsTable.DocumentID  WHERE PurchaseReceiptTable.EffectiveNo = 1 ORDER BY TheDocumentsTable.DateOfDocuments DESC)
        ORDER BY TheDocumentsTable.DateOfDocuments DESC;   --根据最新时间排序
       
        WITH DATA as (
        SELECT PurchaseReceiptTable.PurchaseReceiptID
       FROM   PurchaseReceiptTableLEFT JOIN
       BusinessTypeTable ON PurchaseReceiptTable.BusinessTypeID = BusinessTypeTable.BusinessTypeID LEFT OUTER JOIN
       TheDocumentsTable ON PurchaseReceiptTable.DocumentsID_PurchaseOrder= TheDocumentsTable.DocumentID LEFT OUTER JOIN
       TheDocumentsTable AS TheDocumentsTable_1ON PurchaseReceiptTable.DocumentsID_GRN = TheDocumentsTable_1.DocumentID LEFT OUTER JOIN
       InAndOutOfInventoryRecord ON PurchaseReceiptTable.InAndOutOfInventoryRecordID= InAndOutOfInventoryRecord.InAndOutOfInventoryRecordIDLEFT OUTER JOIN
       ForTheTypeOfTable ON InAndOutOfInventoryRecord.ForTheTypeID = ForTheTypeOfTable.ForTheTypeID LEFT OUTER JOIN
       WarehouseTable ON InAndOutOfInventoryRecord.WarehouseInventory_ID= WarehouseTable.WarehouseID  LEFT OUTER JOIN
       TheCustomerTable ON PurchaseReceiptTable.CustomerID = TheCustomerTable.CustomerID LEFT OUTER JOIN
       TheOperatorTable AS TheOperatorTable_2ON PurchaseReceiptTable.TheOperatorID_Salesman= TheOperatorTable_2.TheOperatorID LEFT OUTER JOIN
       DepartmentTable ON TheOperatorTable_2.DepartmentID = DepartmentTable.DepartmentID LEFT OUTER JOIN
       TheOperatorTable AS TheOperatorTable_1ON PurchaseReceiptTable.TheOperatorID_MakingPeople= TheOperatorTable_1.TheOperatorID LEFT OUTER JOIN
       TheOperatorTable AS TheOperatorTable_3ON PurchaseReceiptTable.TheOperatorID_Reviewer= TheOperatorTable_3.TheOperatorID LEFT OUTER JOIN
       TheOperatorTable ON PurchaseReceiptTable.TheOperatorID_personOfChargeToAnAccount= TheOperatorTable.TheOperatorID
       WHERE PurchaseReceiptTable.EffectiveNo = 1
       )selectCOUNT(*) AS 行数
        FROM DATA
    END

第二步：逻辑层(BLL)代码

        ///
        ///采购入库单查询
        ///
        [OperationContract]
        public DataSetfrm_PurchaseOddNumbers_Load_SelectPurchaseOrderForm(intintLoadCount, int intBeginCount)
        {
            SqlParameter[]SQlCMDpas = {
                                             new SqlParameter("@Type", SqlDbType.Char),
                                             new SqlParameter("@LoadCount", SqlDbType.Int),//要加载的条数
                                             new SqlParameter("@BeginCount", SqlDbType.Int),//不要前面多少条数据
                                        };
            SQlCMDpas[0].Value = "frm_PurchaseOddNumbers_Load_SelectPurchaseOrderForm";
            SQlCMDpas[1].Value = intLoadCount;
            SQlCMDpas[2].Value = intBeginCount;
            returnmyDALMethod.QueryDataSet("PurchaseManage_frm_PurchaseOddNumbers",SQlCMDpas);
        }

第三步：界面层(UIL)代码，在窗体的Load事件中绑定下拉框的数据 (1) 绑定DaTaGridView单据表格截图效果（绑定表格）：

   intintPerPageLoad = 30;    //显示每页加载的数据条数 默认30
   int intTpage= 0;           //页数
  intintSumItem = 0;         //总条数
   int intSumItemTpage= 0;    //总页数
#region 获取单据和单据数
            DataSetdsAll =myfrm_PurchaseOddNumbers.frm_PurchaseOddNumbers_Load_SelectPurchaseOrderForm(intPerPageLoad,0);//获取默认30条数据
            dtPurchaseBePutInStorage =dsAll.Tables[0].Copy();               //查询采购入库单
            dgvPurchaseBe.DataSource =dtPurchaseBePutInStorage;             //绑定单据数据源
            PublicStaticMothd.PreventSort(dgvPurchaseBe);                    //不允许点击标题排序
            intSumItem = Convert.ToInt32(dsAll.Tables[1].Rows[0][0]);        //获取条数
            dsAll.Clear();  //用完就释放
            #endregion
            #region 计算页数
            if(intSumItem % intPerPageLoad > 0)   //计算页总数
            {
                intSumItemTpage = (intSumItem /intPerPageLoad) + 1;   //有余数 要加1
            }
            else
            {
                intSumItemTpage = (intSumItem /intPerPageLoad);
            }
            lblTpage.Text = string.Format("页数： {0} / {1}", (intTpage + 1), intSumItemTpage); //显示页数
            #endregion
            ChangeColour();  //单据审核，行颜 {MOD}改变
            dgvPurchaseBe.Columns["采购入库单ID"].Visible = false;
            dgvPurchaseBe.Columns["单据ID_采购单"].Visible = false;
            dgvPurchaseBe.Columns["单据ID_入库单"].Visible = false;
            dgvPurchaseBe.Columns["出入库存记录ID"].Visible = false;
            dgvPurchaseBe.Columns["客户ID"].Visible = false;
            dgvPurchaseBe.Columns["操作员ID_制单人"].Visible = false;
            dgvPurchaseBe.Columns["操作员ID_审核人"].Visible = false;
            dgvPurchaseBe.Columns["操作员ID_记账人"].Visible = false;
            dgvPurchaseBe.Columns["操作员ID_业务员"].Visible = false;
            dgvPurchaseBe.Columns["入库仓库ID"].Visible = false;
            dgvPurchaseBe.Columns["业务类型ID"].Visible = false;
            dgvPurchaseBe.Columns["供应商ID"].Visible = false;
            dgvPurchaseBe.Columns["出入库类型ID"].Visible = false;//隐藏ID列

(2) 绑定显示框截图效果（表头显示）：

在单据表格的行改变事件（dgvPurchaseBe_SelectionChanged）

     ///
        ///绑定采购单
        ///
        ///单据DataGridView索引
        privatevoid BindingReceipts(intCount)
        {
            try                  //对有空的下拉框进行捕捉
            {
                intOrderNumber = Convert.ToInt32(dtPurchaseBePutInStorage.Rows[Count]["采购入库单ID"].ToString());       //获取 订单号
                intAndOutOfInventoryRecordID = Convert.ToInt32(dtPurchaseBePutInStorage.Rows[Count]["出入库存记录ID"].ToString());//获取 出入库记录单
                txtBeLaidUpTime.Text =dtPurchaseBePutInStorage.Rows[Count]["入库日期"].ToString();                   //获取 入库日期
                txtBeLaidUpOddNnumbers.Text =dtPurchaseBePutInStorage.Rows[Count]["入库单号"].ToString();            //获取 入库单号
               cboStorage.SelectedValue = Convert.ToInt32(dtPurchaseBePutInStorage.Rows[Count]["入库仓库ID"].ToString());//获取 仓库名称
                txtIndentNumber.Text =dtPurchaseBePutInStorage.Rows[Count]["采购单号"].ToString();                   //获取 订单号
                txtArrivalOfGoodsTime.Text =dtPurchaseBePutInStorage.Rows[Count]["采购日期"].ToString();             //获取采购日期
                txtArrivalOfGoodsNumber.Text =dtPurchaseBePutInStorage.Rows[Count]["提单号"].ToString();           //获取 提单号
                txtBusinessNumber.Text =dtPurchaseBePutInStorage.Rows[Count]["业务号"].ToString();                   //获取 业务号
               cboSupplyOfMaterialUnit.SelectedValue = Convert.ToInt32(dtPurchaseBePutInStorage.Rows[0]["供应商ID"].ToString());//获取 供货单位
                cboBusinessNumber.SelectedValue= Convert.ToInt32(dtPurchaseBePutInStorage.Rows[Count]["操作员ID_业务员"].ToString());//获取 业务员
                cboBusinessType.SelectedValue =Convert.ToInt32(dtPurchaseBePutInStorage.Rows[Count]["业务类型ID"].ToString());//获取 业务类型
                cboBeLaidUpSort.SelectedValue =Convert.ToInt32(dtPurchaseBePutInStorage.Rows[Count]["出入库类型ID"].ToString());//获取 入库类别
                txtExamineAndVerifyTime.Text =dtPurchaseBePutInStorage.Rows[Count]["审核日期"].ToString();           //获取 审核日期
                txtRemark.Text =dtPurchaseBePutInStorage.Rows[Count]["备注"].ToString();                            //获取 备注
               cboReceiptsProducer.SelectedValue = Convert.ToInt32(dtPurchaseBePutInStorage.Rows[Count]["操作员ID_制单人"].ToString()); //获取 制单人
                cboTallyPerson.SelectedValue = Convert.ToInt32(dtPurchaseBePutInStorage.Rows[Count]["操作员ID_记账人"].ToString()); //获取 记账人
               cboExamineAndVerifyPerson.SelectedValue = Convert.ToInt32(dtPurchaseBePutInStorage.Rows[Count]["操作员ID_审核人"].ToString());//获取 审核人
            }
            catch{ }
        }

(3) 分页显示
截图效果（分页功能）：

加载按钮 Click（单击）事件

#region 每页加载数据多少条
        private void btnAllLoad_Click(object sender, EventArgs e)
        {
            int intPage = 0; //加载的条数
            if (txtNumber.Text.Length == 0)
            {
                intPage = 30;
            }
            else
            {
                intPage = Convert.ToInt32(txtNumber.Text.Trim());
            }
            if (intPage >= 10)
            {
                intPerPageLoad = intPage;
                intTpage = 0;
                DataSet dsAll = myfrm_PurchaseOddNumbers.frm_PurchaseOddNumbers_Load_SelectPurchaseOrderForm(intPerPageLoad, intTpage * intPerPageLoad);
                dtPurchaseBePutInStorage = dsAll.Tables[0].Copy();               //查询采购入库单
                intSumItem = Convert.ToInt32(dsAll.Tables[1].Rows[0][0]); //获取条数
                dgvPurchaseBe.DataSource = dtPurchaseBePutInStorage;
                dsAll.Clear();//用完释放
            }
            else
            {
                MessageBox.Show("最低显示10条！");
            }
        }
        #endregion

(4) 单据审核改变行颜 {MOD} 截图效果（审核行改变颜 {MOD}）：

在绑定表格数据源的时候加上一下代码的方法 ChangeColour()

#region 单据审核改变颜 {MOD}
        ///
        ///审核改变颜 {MOD}
        ///
        privatevoid ChangeColour()
        {
            for(int intRows = 0; intRows 



4、筛选后绑定表格数据
第一步：数据库的存储过程

   @采购单号 NCHAR(100) ='' ,
   @入库单号 NCHAR(100) ='',
   @采购日期_BEGIN DATE = '' ,
   @采购日期_END DATE = '' ,
   @入库日期_BEGIN DATE = '',
   @入库日期_END DATE = '',
   @审核 INT = 0
if(@Type='frm_PurchaseOddNumbers_Load_SelectAllPurchaseOrderForm')--筛选全部采购单
   BEGIN
       declare@str1 nchar(1000)='';
      declare @str2 nchar(1000)='';
      declare @str3 nchar(1000)='';
      declare @str4 nchar(1000)='';
      declare @str5 nchar(1000)='';
      declare @CaiGouDanHao nchar(30)='';  --采购单号
      declare @RuKuDanHao nchar(30)='';   --入库单号
      declare @CaiGouRiQi_BEGIN nchar(30)='';--采购日期开始
      declare @CaiGouRiQi_END nchar(30)='';--采购日期结束
      declare @RuKuRiQi_BEGIN nchar(30)=''; --入库日期开始
      declare @RuKuRiQi_END nchar(30)=''; --入库日期结束
      declare @ShengHe nchar(2)='';      --审核
      set @CaiGouDanHao=ltrim(rtrim(@采购单号));
      set @CaiGouRiQi_BEGIN= @采购日期_BEGIN;
      set @CaiGouRiQi_END= @采购日期_END;
      set @RuKuRiQi_BEGIN= @入库日期_BEGIN;
      set @RuKuRiQi_END= @入库日期_END;
      set @RuKuDanHao= ltrim(rtrim(@入库单号));
      set @ShengHe= @审核
      IF ltrim(rtrim(@CaiGouDanHao))!=''  --采购单号
      BEGIN
         SET @str1='and ltrim(rtrim(TheDocumentsTable.DocumentNumber))LIKE ''%'+ltrim(rtrim(@CaiGouDanHao))+'%'''
      END
      IF ltrim(rtrim(@RuKuDanHao))!=''  --入库单号
      BEGIN
         SET @str2='andltrim(rtrim(TheDocumentsTable_1.DocumentNumber)) LIKE ''%'+ltrim(rtrim(@RuKuDanHao))+'%'''
      END
      IF @CaiGouRiQi_BEGIN!= '1991-09-13' and @CaiGouRiQi_END !='1991-09-13'  --采购日期
      BEGIN
         SET @str3=' andTheDocumentsTable.DateOfDocuments between '''+@CaiGouRiQi_BEGIN+''' and '''+@CaiGouRiQi_END+''''
      END
      IF @RuKuRiQi_BEGIN!= '1991-09-13' and @RuKuRiQi_END != '1991-09-13'  --入库日期
      BEGIN
         SET @str4=' andTheDocumentsTable_1.DateOfDocuments between '''+@RuKuRiQi_BEGIN+''' and '''+@RuKuRiQi_END+''''
      END
      IF ltrim(rtrim(@ShengHe)) != 2 --审核
      BEGIN
         SET @str5=' and PurchaseReceiptTable.ReviewWhether= ' + @ShengHe
      END
      exec ('SELECT TOP('+ @LoadCount +') LTRIM(RTRIM(TheDocumentsTable.DocumentNumber)) AS 采购单号,
      (CASE WHENPurchaseReceiptTable.DocumentsID_GRN = 0 AND PurchaseReceiptTable.ReviewWhether= 1 THEN ''未入库'' WHENPurchaseReceiptTable.DocumentsID_GRN = 0 AND PurchaseReceiptTable.ReviewWhether= 0 THEN ''待审核'' WHENPurchaseReceiptTable.DocumentsID_GRN != 0 THENTheDocumentsTable_1.DocumentNumber END) AS 入库单号,
      LTRIM(RTRIM(PurchaseReceiptTable.PurchaseReceiptID))AS 采购入库单ID,LTRIM(RTRIM(PurchaseReceiptTable.DocumentsID_PurchaseOrder)) AS 单据ID_采购单,
      LTRIM(RTRIM(PurchaseReceiptTable.DocumentsID_GRN))AS 单据ID_入库单,LTRIM(RTRIM(PurchaseReceiptTable.InAndOutOfInventoryRecordID)) AS 出入库存记录ID,
      LTRIM(RTRIM(PurchaseReceiptTable.DocumentsInvoice))AS 提单号,LTRIM(RTRIM(PurchaseReceiptTable.BusinessNo)) AS 业务号,LTRIM(RTRIM(PurchaseReceiptTable.CustomerID))
      AS 客户ID,LTRIM(RTRIM(PurchaseReceiptTable.AuditDate)) AS 审核日期,LTRIM(RTRIM(PurchaseReceiptTable.Note)) AS 备注,
      (CASE WHENPurchaseReceiptTable.ReviewWhether = 0 THEN ''未审核'' WHENPurchaseReceiptTable.ReviewWhether = 1 THEN ''已审核''END) AS 审核否,   LTRIM(RTRIM(PurchaseReceiptTable.TheOperatorID_MakingPeople))AS 操作员ID_制单人,LTRIM(RTRIM(PurchaseReceiptTable.TheOperatorID_Reviewer)) AS 操作员ID_审核人, LTRIM(RTRIM(PurchaseReceiptTable.TheOperatorID_personOfChargeToAnAccount))AS 操作员ID_记账人,LTRIM(RTRIM(PurchaseReceiptTable.TheOperatorID_Salesman)) AS 操作员ID_业务员,
      (CAST (TheDocumentsTable_1.DateOfDocumentsAS DATETIME)) AS 入库日期, (CAST(TheDocumentsTable.DateOfDocuments AS DATETIME)) AS 采购日期,   LTRIM(RTRIM(InAndOutOfInventoryRecord.WarehouseInventory_ID))AS 入库仓库ID,LTRIM(RTRIM(ForTheTypeOfTable.ForTheTypeName)) AS 入库类型,
      LTRIM(RTRIM(WarehouseTable.TheNameOfTheWarehouse))AS 入库仓库,LTRIM(RTRIM(TheCustomerTable.TheCustomerName)) AS 供货单位,
      LTRIM(RTRIM(TheOperatorTable_2.TheOperatorTypeName))AS 业务员名称,LTRIM(RTRIM(DepartmentTable.DepartmentName)) AS 部门名称,
      LTRIM(RTRIM(TheOperatorTable_1.TheOperatorTypeName))AS 制单员, LTRIM(RTRIM(TheOperatorTable_3.TheOperatorTypeName))AS 审核员,
      LTRIM(RTRIM(TheOperatorTable.TheOperatorTypeName))AS 记账员,LTRIM(RTRIM(BusinessTypeTable.BusinessTypeNname)) AS 业务类型,LTRIM(RTRIM(PurchaseReceiptTable.BusinessTypeID))
      AS 业务类型ID,LTRIM(RTRIM(TheCustomerTable.CustomerID)) AS 供应商ID,LTRIM(RTRIM(InAndOutOfInventoryRecord.ForTheTypeID)) AS 出入库类型ID
      FROM         PurchaseReceiptTable LEFT JOIN
      BusinessTypeTable ONPurchaseReceiptTable.BusinessTypeID = BusinessTypeTable.BusinessTypeID LEFTOUTER JOIN
      TheDocumentsTable ONPurchaseReceiptTable.DocumentsID_PurchaseOrder = TheDocumentsTable.DocumentIDLEFT OUTER JOIN
      TheDocumentsTable AS TheDocumentsTable_1ON PurchaseReceiptTable.DocumentsID_GRN = TheDocumentsTable_1.DocumentID LEFTOUTER JOIN
      InAndOutOfInventoryRecord ONPurchaseReceiptTable.InAndOutOfInventoryRecordID =InAndOutOfInventoryRecord.InAndOutOfInventoryRecordID LEFT OUTER JOIN
      ForTheTypeOfTable ONInAndOutOfInventoryRecord.ForTheTypeID = ForTheTypeOfTable.ForTheTypeID LEFTOUTER JOIN
      WarehouseTable ONInAndOutOfInventoryRecord.WarehouseInventory_ID =WarehouseTable.WarehouseID  LEFT OUTERJOIN
      TheCustomerTable ONPurchaseReceiptTable.CustomerID = TheCustomerTable.CustomerID LEFT OUTER JOIN
      TheOperatorTable AS TheOperatorTable_2 ON PurchaseReceiptTable.TheOperatorID_Salesman= TheOperatorTable_2.TheOperatorID LEFT OUTER JOIN
      DepartmentTable ONTheOperatorTable_2.DepartmentID = DepartmentTable.DepartmentID LEFT OUTER JOIN
      TheOperatorTable AS TheOperatorTable_1 ONPurchaseReceiptTable.TheOperatorID_MakingPeople =TheOperatorTable_1.TheOperatorID LEFT OUTER JOIN
      TheOperatorTable AS TheOperatorTable_3 ONPurchaseReceiptTable.TheOperatorID_Reviewer = TheOperatorTable_3.TheOperatorIDLEFT OUTER JOIN
      TheOperatorTable ON PurchaseReceiptTable.TheOperatorID_personOfChargeToAnAccount= TheOperatorTable.TheOperatorID
      WHERE 1 = 1 ANDPurchaseReceiptTable.EffectiveNo = 1 ANDPurchaseReceiptTable.PurchaseReceiptID not in
      (SELECT TOP('+ @BeginCount +')PurchaseReceiptTable.PurchaseReceiptID FROM PurchaseReceiptTable  INNER JOIN
      TheDocumentsTable ONPurchaseReceiptTable.DocumentsID_PurchaseOrder =TheDocumentsTable.DocumentID  WHERE  1 = 1'+@str1+@str2+@str3+@str4+@str5+' ORDER BYTheDocumentsTable.DateOfDocuments DESC)'+@str1+@str2+@str3+@str4+@str5+'
        ORDER BYTheDocumentsTable.DateOfDocuments DESC')   --根据最新时间排序
        exec ('SELECTCOUNT(PurchaseReceiptTable.PurchaseReceiptID) AS 行数
      FROM  PurchaseReceiptTable LEFT JOIN
      BusinessTypeTable ON PurchaseReceiptTable.BusinessTypeID= BusinessTypeTable.BusinessTypeID LEFT OUTER JOIN
      TheDocumentsTable ONPurchaseReceiptTable.DocumentsID_PurchaseOrder = TheDocumentsTable.DocumentIDLEFT OUTER JOIN
      TheDocumentsTable AS TheDocumentsTable_1ON PurchaseReceiptTable.DocumentsID_GRN = TheDocumentsTable_1.DocumentID LEFTOUTER JOIN
      InAndOutOfInventoryRecord ONPurchaseReceiptTable.InAndOutOfInventoryRecordID =InAndOutOfInventoryRecord.InAndOutOfInventoryRecordID LEFT OUTER JOIN
      ForTheTypeOfTable ON InAndOutOfInventoryRecord.ForTheTypeID= ForTheTypeOfTable.ForTheTypeID LEFT OUTER JOIN
      WarehouseTable ONInAndOutOfInventoryRecord.WarehouseInventory_ID =WarehouseTable.WarehouseID  LEFT OUTERJOIN
      TheCustomerTable ONPurchaseReceiptTable.CustomerID = TheCustomerTable.CustomerID LEFT OUTER JOIN
      TheOperatorTable AS TheOperatorTable_2 ONPurchaseReceiptTable.TheOperatorID_Salesman = TheOperatorTable_2.TheOperatorIDLEFT OUTER JOIN
      DepartmentTable ONTheOperatorTable_2.DepartmentID = DepartmentTable.DepartmentID LEFT OUTER JOIN
      TheOperatorTable AS TheOperatorTable_1 ONPurchaseReceiptTable.TheOperatorID_MakingPeople =TheOperatorTable_1.TheOperatorID LEFT OUTER JOIN
      TheOperatorTable AS TheOperatorTable_3 ONPurchaseReceiptTable.TheOperatorID_Reviewer = TheOperatorTable_3.TheOperatorIDLEFT OUTER JOIN
      TheOperatorTable ONPurchaseReceiptTable.TheOperatorID_personOfChargeToAnAccount =TheOperatorTable.TheOperatorID
      WHERE PurchaseReceiptTable.EffectiveNo = 1 '+@str1+@str2+@str3+@str4+@str5)
   END


第二步：逻辑层(BLL)代码

       ///
        ///筛选数据
        ///
        ///
        ///
        ///
        ///
        ///
        ///
        ///
        ///
        ///
        ///
        [OperationContract]
        public DataSetfrm_PurchaseOddNumbers_Load_SelectAllPurchaseOrderForm(intintLoadCount, int intBeginCount, string str采购单号, string str入库单号,
            DateTimedtp采购日期_BEGIN, DateTimedtp采购日期_END, DateTimedtp入库日期_BEGIN, DateTimedtp入库日期_END, intintShengHe)
        {
            SqlParameter[]SQlCMDpas = {
                                             new SqlParameter("@Type", SqlDbType.Char),
                                             new SqlParameter("@LoadCount", SqlDbType.Int),
                                             new SqlParameter("@BeginCount", SqlDbType.Int),
                                             new SqlParameter("@采购单号", SqlDbType.NChar),
                                             new SqlParameter("@入库单号", SqlDbType.NChar),
                                             new SqlParameter("@采购日期_BEGIN", SqlDbType.DateTime),
                                             new SqlParameter("@采购日期_END", SqlDbType.DateTime),
                                             new SqlParameter("@入库日期_BEGIN", SqlDbType.DateTime),
                                             new SqlParameter("@入库日期_END", SqlDbType.DateTime),
                                             new SqlParameter("@审核", SqlDbType.Int),
                                        };
            SQlCMDpas[0].Value = "frm_PurchaseOddNumbers_Load_SelectAllPurchaseOrderForm";
            SQlCMDpas[1].Value = intLoadCount;
            SQlCMDpas[2].Value = intBeginCount;
            SQlCMDpas[3].Value = str采购单号;
            SQlCMDpas[4].Value = str入库单号;
            SQlCMDpas[5].Value = dtp采购日期_BEGIN;
            SQlCMDpas[6].Value = dtp采购日期_END;
            SQlCMDpas[7].Value = dtp入库日期_BEGIN;
            SQlCMDpas[8].Value = dtp入库日期_END;
            SQlCMDpas[9].Value = intShengHe;
            returnmyDALMethod.QueryDataSet("PurchaseManage_frm_PurchaseOddNumbers",SQlCMDpas);
        }
 


第三步：界面层(UIL)代码，在窗体的Load事件中绑定下拉框的数据
  截图效果（点击筛选按钮显示筛选单据框）：
#region 筛选
        privatevoid btnScreen_Click(objectsender, EventArgs e)
        {
            #region 采购日期
            if(chkPurchaseDate.Checked == true)
            {
                dtpReceiptsBeginDate = Convert.ToDateTime(dtpPurchaseDateBegin.Value.ToString());
                dtpReceiptsEndDate = Convert.ToDateTime(dtpPurchaseDateEnd.Value.ToString());
            }
            #endregion
 
            #region 采购单号
            if(chkPurchaseNumber.Checked == true)
            {
                strReceiptsNumber =txtPurchaseNumber.Text.Trim();
            }
            #endregion
 
            #region 入库单号
            if(chkBeLaidUpMark.Checked == true)
            {
                strBeLaidNumber = txtBeLaidUpMark.Text.Trim();
            }
            #endregion
           
            #region 入库日期
            if(chkBeLaidUpMark.Checked == true)
            {
                dtpBeLaidUpBeginDate = Convert.ToDateTime(dtpBeLaidUpDateBegin.Value.ToString());
                dtpBeLaidUpEndDate = Convert.ToDateTime(dtpBeLaidUpDateEnd.Value.ToString());
            }
            #endregion
 
            #region 审核
            if(radAlready.Checked)   //已审核
            {
                intAlready = 1;
            }
            else
            {
                if(radNot.Checked)   //未审核
                {
                    intAlready = 0;
                }
                else
                {
                    if(radAll.Checked)  //全部
                    {
                        intAlready = 2;
                    }
                }
            }
            #endregion
 
            DataSetds =myfrm_PurchaseOddNumbers.frm_PurchaseOddNumbers_Load_SelectAllPurchaseOrderForm(intPerPageLoad,intTpage * intPerPageLoad, strReceiptsNumber, strBeLaidNumber,
                  dtpReceiptsBeginDate,dtpReceiptsEndDate, dtpBeLaidUpBeginDate, dtpBeLaidUpEndDate, intAlready);//查询筛选
            dtScreen = ds.Tables[0];
            intSumItem = Convert.ToInt32(ds.Tables[1].Rows[0][0]);  //刷新筛选的条数
            dtPurchaseBePutInStorage =dtScreen;    //将筛选后的数据赋值给全局dtPurchaseBePutInStorage
            dgvPurchaseBe.DataSource =dtPurchaseBePutInStorage;//绑定数据到表格
            bolWhetherScreen = true;                 //标识为筛选的数据
            intTpage = 0;                             //页数为0
            ButtonStartUsing(0);                      //按钮限制
        }
        #endregion
 



 
5、删除订单（修改有效否 == False）
第一步：数据库的存储过程

if(@Type='tlsbDelete_Click_DaletePurchaseOrderForm')   --删除采购入库单
   BEGIN
      UPDATE PurchaseReceiptTable
      SET    PurchaseReceiptTable.EffectiveNo = 0        --修改有效否
      WHERE  PurchaseReceiptTable.PurchaseReceiptID = @PurchaseReceiptID --采购入库单ID
   END


第二步：逻辑层(BLL)代码

       ///
        ///删除采购入库单
        ///
        ///采购入库单ID
        ///
        [OperationContract]
        public int tlsbDelete_Click_DaletePurchaseOrderForm(int intPurchaseReceiptID)
        {
            myParameter.DeletePurchaseReceiptID= intPurchaseReceiptID;
            SqlParameter[]SQlCMDpas = {
                                             new SqlParameter("@Type", SqlDbType.Char),
                                             new SqlParameter("@PurchaseReceiptID", SqlDbType.Int),
                                        };
            SQlCMDpas[0].Value = "tlsbDelete_Click_DaletePurchaseOrderForm";
            SQlCMDpas[1].Value = myParameter.DeletePurchaseReceiptID;
            returnmyDALMethod.UpdateData("PurchaseManage_frm_PurchaseOddNumbers",SQlCMDpas);
        }
 


第三步：界面层(UIL)代码，在窗体的Load事件中绑定下拉框的数据
  截图效果（选择单据表某一行未审核的单据再点击删除按钮）：


  #region 删除 （修改有效否 = False）
        privatevoid tlsbDelete_Click(objectsender, EventArgs e)
        {
            if(dtPurchaseBePutInStorage.Rows[intTpage]["审核否"].ToString() == "未审核") //当前单未审核 可以删除
            {
                if(myfrm_PurchaseOddNumbers.tlsbDelete_Click_DaletePurchaseOrderForm(intOrderNumber)> 0)
                {
                    MessageBox.Show("删除成功！");
                    PageSkip(intTpage);
                    ChangeColour();
                }
            }
        }
        #endregion
 


6、审核订单（修改审核否 == True）
第一步：数据库的存储过程

if(@Type='tlsbExamineAndVerify_Click_ExamineAndVerify')--审核订单
   BEGIN
      UPDATE    PurchaseReceiptTable
      SET  ReviewWhether=@ReviewWhether, --审核否
      AuditDate =@AuditDate, --审核日期
      TheOperatorID_Reviewer= @TheOperatorID_Reviewer--审核人ID
      WHERE PurchaseReceiptTable.PurchaseReceiptID = @PurchaseReceiptID --采购入库单ID
   END


第二步：逻辑层(BLL)代码

///
        ///审核订单
        ///
        ///审核否
        ///审核日期
        ///审核人
        ///采购入库单ID
        ///
        [OperationContract]
        publicinttlsbExamineAndVerify_Click_ExamineAndVerify(boolbolReviewWhether, DateTime dtpAuditDate,
            intintTheOperatorID_Reviewer,intintPurchaseReceiptID)
        {
           myParameter.ReviewWhether = bolReviewWhether;
            myParameter.AuditDate =dtpAuditDate;
           myParameter.TheOperatorID_Reviewer = intTheOperatorID_Reviewer;
           myParameter.PurchaseReceiptID = intPurchaseReceiptID;
            SqlParameter[]SQlCMDpas = {
                                             new SqlParameter("@Type", SqlDbType.Char),
                                             new SqlParameter("@ReviewWhether", SqlDbType.Bit), //审核否
                                             new SqlParameter("@AuditDate", SqlDbType.DateTime),  //审核日期
                                             new SqlParameter("@TheOperatorID_Reviewer", SqlDbType.Int), //审核人
                                             new SqlParameter("@PurchaseReceiptID", SqlDbType.Int), //采购入库单ID
                                        };
            SQlCMDpas[0].Value = "tlsbExamineAndVerify_Click_ExamineAndVerify";
            SQlCMDpas[1].Value =myParameter.ReviewWhether;
            SQlCMDpas[2].Value =myParameter.AuditDate;
            SQlCMDpas[3].Value =myParameter.TheOperatorID_Reviewer;
            SQlCMDpas[4].Value =myParameter.PurchaseReceiptID;
            returnmyDALMethod.UpdateData("PurchaseManage_frm_PurchaseOddNumbers",SQlCMDpas);
        }
 


第三步：界面层(UIL)代码，在窗体的Load事件中绑定下拉框的数据
截图效果（选择单据表某一行未审核的单据再点击审核按钮）：


#region 审核
        privatevoid tlsbExamineAndVerify_Click(object sender, EventArgse)
        {
            if(PublicStaticFields.strOperatorName == null || PublicStaticFields.strOperateTypeName== null)
            {
                Popup.Controls.Frm_Popup.Instance().Show();
                PublicStaticForm.PubLogin= new frm_Login();
                PublicStaticForm.PubLogin.Show();
                PublicStaticForm.PubLogin.TopLevel= true;
            }
            else
            {
                if(MessageBox.Show(string.Format("是否确认审核,一旦审核侧不能修改。你的姓名是【{0}】你的身份是【{1}】", PublicStaticFields.strOperatorName,PublicStaticFields.strOperateTypeName), "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes)
                {
                    intintWhetherSucceed =myfrm_PurchaseOddNumbers.tlsbExamineAndVerify_Click_ExamineAndVerify(true, DateTime.Now,PublicStaticFields.intOperatorID,intOrderNumber);//审核订单
                    if(intWhetherSucceed > 0)                                      //判断是否审核成功
                    {
                        int intIndex = dgvPurchaseBe.CurrentRow.Index;              //获取当前审核行
                       PageSkip(intTpage);                                         //审核成功，刷新数据
                        ChangeColour();                                             //审核后，行变 {MOD}
                       dgvPurchaseBe.Rows[intIndex].Selected = true;               //选择刚审核行
                       dgvPurchaseBe.FirstDisplayedScrollingRowIndex = intIndex;   //滚动到审核行
                        MessageBox.Show("恭喜！审核成功！");
                    }
                }
            }
        }
        #endregion



以上技术仅供参考，禁止用于商业用途，上述内容不代表用友立场！


                         
                         
     
  
      Ta的文章  更多 >> 
             
      


                    
          用友ERT T6 技术解析(四)采购入库 上
               
                0 个评论
               
              
                               

              
                  
  

  
  
    
         
  
      热门文章


      



  
    
      
      举报内容
    
    






检举类型


检举内容


检举用户




检举原因



广告推广


恶意灌水


回答内容与提问无关



抄袭答案


其他





检举说明(必填)






    

                
                 
      



    

  


 打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮
 

 





   


  
    
    



      
      
       
  
      




  
             
            
                    Copyright © 2024 平头弟 京ICP备15032243号-1
                         投诉举报邮箱：nettui@qq.com

用友ERT T6 技术解析(四)采购入库 上

第二章 功能实现

Ta的文章 更多 >>

热门文章

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

用友ERT T6 技术解析(四)采购入库上

第二章功能实现

Ta的文章更多 >>