コード例 #1
0
        // 根据物料ID得到历史交易信息
        public SortedDictionary <int, SaleOutOrderTable> getSalePriceCountInfo(int materielID, string startDate, string endDate)
        {
            string query = "SELECT A2.BILL_NUMBER, A2.CUSTOMER_ID, A3.[NAME], A2.TRADING_DATE, A1.[VALUE], A1.PRICE";

            query += " FROM SALE_OUT_ORDER_DETAILS A1, SALE_OUT_ORDER A2, BASE_SUPPLIER_LIST A3";
            query += " WHERE A1.MATERIEL_ID = " + materielID;
            query += " AND A1.BILL_NUMBER = A2.BILL_NUMBER AND A2.IS_REVIEW = 1 AND A2.CUSTOMER_ID = A3.PKEY";
            query += " AND A2.TRADING_DATE >= '" + startDate + "'";
            query += " AND A2.TRADING_DATE <= '" + endDate + "'";

            SortedDictionary <int, SaleOutOrderTable> dataList = new SortedDictionary <int, SaleOutOrderTable>();

            using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, query))
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    SaleOutOrderTable record = new SaleOutOrderTable();

                    record.billNumber   = DbDataConvert.ToString(row["BILL_NUMBER"]);
                    record.customerId   = DbDataConvert.ToInt32(row["CUSTOMER_ID"]);
                    record.customerName = DbDataConvert.ToString(row["NAME"]);
                    record.tradingDate  = DbDataConvert.ToDateTime(row["TRADING_DATE"]).ToString("yyyy-MM-dd");

                    record.totalMoney = DbDataConvert.ToString(row["PRICE"]);
                    record.sumValue   = DbDataConvert.ToString(row["VALUE"]);

                    dataList.Add(dataList.Count, record);
                }
            }

            return(dataList);
        }
コード例 #2
0
        private void updataActualValue(string billNumber)
        {
            SaleOutOrderTable accountReceivableData = getSaleInfoFromBillNumber(billNumber);

            if (accountReceivableData.sourceBillType == "销售订单" && accountReceivableData.sourceBillNumber.Length > 0)
            {
                SaleOrder.getInctance().updataActualValue(accountReceivableData.sourceBillNumber, Convert.ToDouble(accountReceivableData.sumValue));
            }
        }
コード例 #3
0
        public SortedDictionary <int, SaleOutOrderTable> getSaleOutOrderCountInfo(string startDate, string endDate, bool isAllBill = false, int countType = 1)
        {
            SortedDictionary <int, SaleOutOrderTable> dataList = new SortedDictionary <int, SaleOutOrderTable>();
            string groupBySQL = "", querySQL = "";

            // countType 等于1是按客户汇总,countType等于2是业务员汇总
            if (countType == 1)
            {
                querySQL    = "SELECT [CUSTOMER_ID],SUM([TOTAL_MONEY]) AS TOTAL_MONEY FROM [dbo].[SALE_OUT_ORDER] WHERE ";
                groupBySQL += " GROUP BY CUSTOMER_ID";
            }
            else if (countType == 2)
            {
                querySQL    = "SELECT [BUSINESS_PEOPLE_ID],SUM([TOTAL_MONEY]) AS TOTAL_MONEY FROM [dbo].[SALE_OUT_ORDER] WHERE ";
                groupBySQL += " GROUP BY BUSINESS_PEOPLE_ID";
            }

            string query = querySQL;

            query += " TRADING_DATE >= '" + startDate + "' AND TRADING_DATE <= '" + endDate + "' AND IS_RED_BILL = 0 ";

            if (!isAllBill)
            {
                query += " AND IS_REVIEW = 1";
            }

            query += groupBySQL;
            query += " ORDER BY TOTAL_MONEY DESC";

            using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, query))
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    SaleOutOrderTable record = new SaleOutOrderTable();

                    if (countType == 1)
                    {
                        record.customerId   = DbDataConvert.ToInt32(row["CUSTOMER_ID"]);
                        record.customerName = Customer.getInctance().getCustomerNameFromPkey(record.customerId);
                    }
                    else if (countType == 2)
                    {
                        record.businessPeopleId   = DbDataConvert.ToInt32(row["BUSINESS_PEOPLE_ID"]);
                        record.businessPeopleName = Staff.getInctance().getStaffNameFromPkey(record.businessPeopleId);
                    }

                    record.totalMoney = DbDataConvert.ToString(row["TOTAL_MONEY"]);

                    dataList.Add(dataList.Count, record);
                }
            }
            return(dataList);
        }
コード例 #4
0
        public SaleOutOrderTable getSaleInfoFromBillNumber(string billNumber)
        {
            SaleOutOrderTable record = new SaleOutOrderTable();

            foreach (KeyValuePair <int, SaleOutOrderTable> index in m_tableDataList)
            {
                if (index.Value.billNumber == billNumber)
                {
                    record = index.Value;
                    break;
                }
            }

            return(record);
        }
コード例 #5
0
        public bool checkBillIsExist(string billNumber)
        {
            bool isRet = false;

            foreach (KeyValuePair <int, SaleOutOrderTable> index in m_tableDataList)
            {
                SaleOutOrderTable record = new SaleOutOrderTable();

                if (index.Value.billNumber == billNumber)
                {
                    isRet = true;
                    break;
                }
            }

            return(isRet);
        }
コード例 #6
0
        public SortedDictionary <int, SaleOutOrderTable> getAllNotReviewSaleOutOrderInfo()
        {
            SortedDictionary <int, SaleOutOrderTable> list = new SortedDictionary <int, SaleOutOrderTable>();

            foreach (KeyValuePair <int, SaleOutOrderTable> index in m_tableDataList)
            {
                SaleOutOrderTable record = new SaleOutOrderTable();
                record = index.Value;

                if (index.Value.isReview != "1")
                {
                    list.Add(list.Count, index.Value);
                }
            }

            return(list);
        }
コード例 #7
0
        public void updataReceivedInfo(string billNumber, double paymentOK)
        {
            SaleOutOrderTable record      = getSaleInfoFromBillNumber(billNumber);
            double            paymentNoOK = Convert.ToDouble(record.totalMoney.ToString()) - paymentOK;

            string update = "UPDATE [dbo].[SALE_OUT_ORDER] SET ";

            update += "RECEIVED_OK = " + paymentOK + ", ";
            update += "RECEIVED_NO_OK = " + paymentNoOK;
            update += " WHERE BILL_NUMBER = '" + billNumber + "'";

            try
            {
                DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, update);
                load();
            }
            catch (Exception error)
            {
                MessageBoxExtend.messageWarning(error.Message);
                return;
            }
        }
コード例 #8
0
        private void load()
        {
            string sql = "SELECT [PKEY],[CUSTOMER_ID],[TRADING_DATE],[BILL_NUMBER],[SALE_TYPE],[PAYMENT_DATE],";

            sql += "[EXCHANGES_UNIT],[BUSINESS_PEOPLE_ID],[SUM_VALUE],[SUM_MONEY],[SUM_TRANSPORTATION_COST],[SUM_OTHER_COST],";
            sql += "[TOTAL_MONEY], [MAKE_ORDER_STAFF],[ORDERR_REVIEW],[REVIEW_DATE],[IS_REVIEW],";
            sql += "[SOURCE_BILL_TYPE], [SOURCE_BILL_NUMBER],[STAFF_SAVE_ID],[STAFF_CHECK_ID],[RECEIVED_OK],[RECEIVED_NO_OK],";
            sql += "[IS_RED_BILL],[IS_IN_LEDGER],[ORDERR_IN_LEDGER],[IN_LEDGER_DATE] ";
            sql += "FROM [dbo].[SALE_OUT_ORDER] ORDER BY PKEY DESC";

            m_tableDataList.Clear();

            using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql))
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    SaleOutOrderTable record = new SaleOutOrderTable();

                    record.pkey          = DbDataConvert.ToInt32(row["PKEY"]);
                    record.customerId    = DbDataConvert.ToInt32(row["CUSTOMER_ID"]);
                    record.customerName  = Customer.getInctance().getCustomerNameFromPkey(record.customerId);
                    record.tradingDate   = DbDataConvert.ToDateTime(row["TRADING_DATE"]).ToString("yyyy-MM-dd");
                    record.billNumber    = DbDataConvert.ToString(row["BILL_NUMBER"]);
                    record.saleType      = DbDataConvert.ToString(row["SALE_TYPE"]);
                    record.paymentDate   = DbDataConvert.ToDateTime(row["PAYMENT_DATE"]).ToString("yyyy-MM-dd");
                    record.exchangesUnit = DbDataConvert.ToString(row["EXCHANGES_UNIT"]);

                    record.businessPeopleId   = DbDataConvert.ToInt32(row["BUSINESS_PEOPLE_ID"]);
                    record.businessPeopleName = Staff.getInctance().getStaffNameFromPkey(record.businessPeopleId);
                    record.makeOrderStaff     = DbDataConvert.ToInt32(row["MAKE_ORDER_STAFF"]);
                    record.makeOrderStaffName = Staff.getInctance().getStaffNameFromPkey(record.makeOrderStaff);

                    if (DbDataConvert.ToString(row["ORDERR_REVIEW"]).Length > 0)
                    {
                        record.orderrReview     = DbDataConvert.ToInt32(row["ORDERR_REVIEW"]);
                        record.orderrReviewName = Staff.getInctance().getStaffNameFromPkey(record.orderrReview);
                        record.reviewDate       = DbDataConvert.ToDateTime(row["REVIEW_DATE"]).ToString("yyyy-MM-dd");
                    }

                    record.isReview = DbDataConvert.ToString(row["IS_REVIEW"]);

                    record.sumValue = DbDataConvert.ToString(row["SUM_VALUE"]);
                    record.sumMoney = DbDataConvert.ToString(row["SUM_MONEY"]);
                    record.sumTransportationCost = DbDataConvert.ToString(row["SUM_TRANSPORTATION_COST"]);
                    record.sumOtherCost          = DbDataConvert.ToString(row["SUM_OTHER_COST"]);
                    record.totalMoney            = DbDataConvert.ToString(row["TOTAL_MONEY"]);

                    // 销售出库单比销售订单中多出的字段
                    record.sourceBillType   = DbDataConvert.ToString(row["SOURCE_BILL_TYPE"]);
                    record.sourceBillNumber = DbDataConvert.ToString(row["SOURCE_BILL_NUMBER"]);
                    record.staffSaveId      = DbDataConvert.ToInt32(row["STAFF_SAVE_ID"]);
                    record.staffSaveName    = Staff.getInctance().getStaffNameFromPkey(record.staffSaveId);
                    record.staffCheckId     = DbDataConvert.ToInt32(row["STAFF_CHECK_ID"]);
                    record.staffCheckName   = Staff.getInctance().getStaffNameFromPkey(record.staffCheckId);

                    // 已收款金额和未收款金额
                    record.paymentOk   = DbDataConvert.ToString(row["RECEIVED_OK"]);
                    record.paymentNoOk = DbDataConvert.ToString(row["RECEIVED_NO_OK"]);

                    // 红字蓝字标示和是否入账标记
                    record.isRedBill = DbDataConvert.ToInt32(row["IS_RED_BILL"]);

                    // 记账相关信息
                    if (DbDataConvert.ToString(row["ORDERR_IN_LEDGER"]).Length > 0)
                    {
                        record.orderInLedger     = DbDataConvert.ToInt32(row["ORDERR_IN_LEDGER"]);
                        record.orderInLedgerName = Staff.getInctance().getStaffNameFromPkey(record.orderrReview);
                        record.inLedgerDate      = DbDataConvert.ToDateTime(row["IN_LEDGER_DATE"]).ToString("yyyy-MM-dd");
                    }
                    record.isInLedger = DbDataConvert.ToInt32(row["IS_IN_LEDGER"]);

                    m_tableDataList.Add(m_tableDataList.Count, record);
                }
            }
        }
コード例 #9
0
        public void insert(SaleOutOrderTable record, bool isDisplayMessageBox = true)
        {
            bool isExistReview          = false;
            SaleOutOrderTable oldRecord = new SaleOutOrderTable();

            string insert = "INSERT INTO [dbo].[SALE_OUT_ORDER]([CUSTOMER_ID],[TRADING_DATE],[BILL_NUMBER],[SALE_TYPE],";

            insert += "[PAYMENT_DATE],[EXCHANGES_UNIT],[SUM_VALUE],[SUM_MONEY],[SUM_TRANSPORTATION_COST],[SUM_OTHER_COST],[TOTAL_MONEY],";
            insert += "[BUSINESS_PEOPLE_ID],[MAKE_ORDER_STAFF],[SOURCE_BILL_TYPE],[SOURCE_BILL_NUMBER],[STAFF_SAVE_ID],";
            insert += "[STAFF_CHECK_ID],[RECEIVED_OK],[RECEIVED_NO_OK], [IS_RED_BILL], [IS_IN_LEDGER]";

            // 根据单据编号,判断库中是否已经存在该单据 如果存在单据首先删除单据,然后再执行插入操作
            if (checkBillIsExist(record.billNumber))
            {
                if (checkBillIsReview(record.billNumber))
                {
                    isExistReview = true;
                    insert       += ",[ORDERR_REVIEW], [REVIEW_DATE], [IS_REVIEW] ";
                    oldRecord     = getSaleInfoFromBillNumber(record.billNumber);
                }

                delete(record.billNumber);
            }

            insert += ") VALUES(";

            insert += record.customerId + ",";
            insert += "'" + record.tradingDate + "',";
            insert += "'" + record.billNumber + "',";
            insert += "'" + record.saleType + "',";
            insert += "'" + record.paymentDate + "',";
            insert += "'" + record.exchangesUnit + "',";
            insert += "'" + record.sumValue + "',";
            insert += "'" + record.sumMoney + "',";
            insert += "'" + record.sumTransportationCost + "',";
            insert += "'" + record.sumOtherCost + "',";
            insert += "'" + record.totalMoney + "',";
            insert += record.businessPeopleId + ",";
            insert += record.makeOrderStaff + ",";
            insert += "'" + record.sourceBillType + "',";
            insert += "'" + record.sourceBillNumber + "',";
            insert += record.staffSaveId + ",";
            insert += record.staffCheckId + ",";

            // 已付款金额默认为0
            insert += "0,";

            // 未付款金额为总金额
            insert += record.totalMoney + ",";

            // 红字蓝字标示和是否入账标示(默认未入账)
            insert += record.isRedBill + ", 0";

            // 如果单据已经审核过,把之前的审核信息写到该记录中
            if (isExistReview)
            {
                insert += ",";
                insert += oldRecord.orderrReview + ",";
                insert += "'" + oldRecord.reviewDate + "', 1";
            }

            insert += ")";

            try
            {
                DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, insert);

                if (isDisplayMessageBox)
                {
                    MessageBoxExtend.messageOK("数据保存成功");
                }

                load();
            }
            catch (Exception error)
            {
                MessageBoxExtend.messageWarning(error.Message);
                return;
            }

            writeOperatorLog(203, OperatorLogType.Add, record.billNumber);
        }
コード例 #10
0
        private void updateAccountReceivableData(string billNumber)
        {
            SaleOutOrderTable accountReceivableData = getSaleInfoFromBillNumber(billNumber);

            if (accountReceivableData.saleType == "赊购")
            {
                InitAccountReceivableTable record = new InitAccountReceivableTable();

                // 应收款金额
                double turnover = Convert.ToDouble(accountReceivableData.sumMoney.ToString());

                string message = "单据 [" + accountReceivableData.billNumber + "] 销售类型为赊购,";
                message += "系统会自动产生一笔应收账款信息记录到客户 [" + accountReceivableData.customerName + "] 账户下,";
                message += "请确实应收账款金额,然后点击【确定】按钮(如需调整金额,请点击调整【金额调整】按钮,再点击【确定】)";

                FormAdjustAmount faa = new FormAdjustAmount("应收账款金额确认", message, accountReceivableData.totalMoney);
                faa.ShowDialog();
                turnover = faa.getAmount();

                if (!m_isRedBill)
                {
                    if (!InitAccountReceivable.getInctance().checkcustomerOrSupplierIDIsExist(accountReceivableData.customerId))
                    {
                        /* 说明
                         * 系统应收应收账款的汇总表和详细的收款还款记录是分开存放,并且默认情况下会相互更新
                         * 比如正常的逻辑(用于应收应收胀库初始化模块):
                         *      当在应收账款汇总表插入一条记录的时候,程序会自动插入一条期初数据到应收账款详细表
                         *      当有一笔交易更新了应收账款表时,程序会自动查询应收账款统计表,更新对应客户的欠款余额
                         * 此处的逻辑如下:
                         *      当发生销售出库赊购业务时候,自动产生一条应收账款信息
                         *      这里需要首先查询下系统中是否存在跟该客户的应收账款
                         *          如果不存在,需要同时更新应收账款汇总表和应收账款详细表,并且不需要两个表之间相同更新余额
                         *          如果存在,只需要更新应收账款详细表,应收账款详细表会自动更新应收账款汇总表中的余额
                         */

                        // 把数据插入到应收账款汇总表
                        InitAccountReceivableTable accountReceivableCount = new InitAccountReceivableTable();
                        accountReceivableCount.customerOrSupplierID = accountReceivableData.customerId;                // 客户ID
                        accountReceivableCount.tradingDate          = accountReceivableData.paymentDate;               // 交易日期
                        accountReceivableCount.balance = turnover;
                        InitAccountReceivable.getInctance().insert(accountReceivableCount, false, false);

                        // 把数据插入到应收账款明细表
                        CashAccountReceivableDetailTable cashAccountReceivableDetailrecord = new CashAccountReceivableDetailTable();
                        cashAccountReceivableDetailrecord.customerOrSupplierID = accountReceivableData.customerId;  // 客户ID
                        cashAccountReceivableDetailrecord.billTypeName         = "销售出库";                            // 账单类型
                        cashAccountReceivableDetailrecord.billNumber           = billNumber;                        // 单据号
                        cashAccountReceivableDetailrecord.tradingDate          = accountReceivableData.paymentDate; // 交易日期
                        cashAccountReceivableDetailrecord.turnover             = turnover;                          // 应收款金额
                        cashAccountReceivableDetailrecord.staffID = accountReceivableData.businessPeopleId;         // 业务员ID
                        CashAccountReceivableDetail.getInctance().insert(cashAccountReceivableDetailrecord, false);
                    }
                    else
                    {
                        // 把数据插入到应收账款明细表
                        CashAccountReceivableDetailTable cashAccountReceivableDetailrecord = new CashAccountReceivableDetailTable();
                        cashAccountReceivableDetailrecord.customerOrSupplierID = accountReceivableData.customerId;  // 客户ID
                        cashAccountReceivableDetailrecord.billTypeName         = "销售出库";                            // 账单类型
                        cashAccountReceivableDetailrecord.billNumber           = billNumber;                        // 单据号
                        cashAccountReceivableDetailrecord.tradingDate          = accountReceivableData.paymentDate; // 交易日期
                        cashAccountReceivableDetailrecord.turnover             = turnover;                          // 应收款金额
                        cashAccountReceivableDetailrecord.staffID = accountReceivableData.businessPeopleId;         // 业务员ID
                        CashAccountReceivableDetail.getInctance().insert(cashAccountReceivableDetailrecord, true);
                    }
                }
                else
                {
                    // 把数据插入到应收账款明细表
                    CashAccountReceivableDetailTable cashAccountReceivableDetailrecord = new CashAccountReceivableDetailTable();
                    cashAccountReceivableDetailrecord.customerOrSupplierID = accountReceivableData.customerId;  // 客户ID
                    cashAccountReceivableDetailrecord.billTypeName         = "销售退货";                            // 账单类型
                    cashAccountReceivableDetailrecord.billNumber           = billNumber;                        // 单据号
                    cashAccountReceivableDetailrecord.tradingDate          = accountReceivableData.paymentDate; // 交易日期
                    cashAccountReceivableDetailrecord.turnover             = turnover;                          // 应收款金额
                    cashAccountReceivableDetailrecord.staffID = accountReceivableData.businessPeopleId;         // 业务员ID
                    CashAccountReceivableDetail.getInctance().insert(cashAccountReceivableDetailrecord, true);
                }
            }
        }