private void load() { string sql = "SELECT A.PKEY, A.SUPPLIER_ID, B.NAME, A.BILL_TYPE_NAME, A.TRADING_DATE, A.BILL_NUMBER, A.TURNOVER, "; sql += "A.BALANCE, A.STAFF_ID, C.NAME AS STAFF_NAME, A.NOTE FROM CASH_ACCOUNT_PAYABLE_DETAIL A, BASE_SUPPLIER_LIST B, BASE_STAFF_LIST C "; sql += "WHERE A.SUPPLIER_ID = B.PKEY AND A.STAFF_ID = C.PKEY"; sql += " ORDER BY A.TRADING_DATE"; m_accountReceivableDetailList.Clear(); using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql)) { foreach (DataRow row in dataTable.Rows) { CashAccountReceivableDetailTable record = new CashAccountReceivableDetailTable(); record.pkey = DbDataConvert.ToInt32(row["PKEY"]); record.customerOrSupplierID = DbDataConvert.ToInt32(row["SUPPLIER_ID"]); record.name = DbDataConvert.ToString(row["NAME"]); record.billTypeName = DbDataConvert.ToString(row["BILL_TYPE_NAME"]); record.tradingDate = DbDataConvert.ToDateTime(row["TRADING_DATE"]).ToString("yyyy-MM-dd"); record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]); record.turnover = DbDataConvert.ToDouble(row["TURNOVER"]); record.balance = DbDataConvert.ToDouble(row["BALANCE"]); record.staffID = DbDataConvert.ToInt32(row["STAFF_ID"]); record.staffName = DbDataConvert.ToString(row["STAFF_NAME"]); record.note = DbDataConvert.ToString(row["NOTE"]); m_accountReceivableDetailList.Add(m_accountReceivableDetailList.Count, record); } } }
private void insertToCashAccountPayablebleDatail(InitAccountReceivableTable record) { // 把期初的欠供应商的钱款余额信息更新到应付账款明细表中。作为使用在现金管理里面查询企业跟该客户往来账的依据 CashAccountReceivableDetailTable data = new CashAccountReceivableDetailTable(); data.billTypeName = "期初数据"; data.customerOrSupplierID = record.customerOrSupplierID; data.turnover = record.balance; data.balance = record.balance; CashAccountPayableDetail.getInctance().insert(data); }
public void insert(CashAccountReceivableDetailTable record, bool isUpdateInitAccountPayableTable = true) { string insert = "INSERT INTO [dbo].[CASH_ACCOUNT_PAYABLE_DETAIL]([SUPPLIER_ID]"; insert += ",[BILL_TYPE_NAME],[BILL_NUMBER],[TURNOVER],[BALANCE],[STAFF_ID],[NOTE]) VALUES("; insert += record.customerOrSupplierID + ","; insert += "'" + record.billTypeName + "',"; insert += "'" + record.billNumber + "',"; insert += record.turnover + ","; if (isUpdateInitAccountPayableTable) { insert += getNewBalance(record) + ","; } else { insert += record.turnover + ","; } insert += DbPublic.getInctance().getCurrentLoginUserID() + ","; insert += "'" + record.note + "'"; insert += ")"; try { if (record.billTypeName == "期初数据") { // 如果单据类型是期初数据,插入之前应该删除掉之前存在的该客户的期初欠款记录信息,有一个原则就是 // 一个客户的期初欠款记录在数据库中只有一条 string delete = "DELETE FROM CASH_ACCOUNT_PAYABLE_DETAIL WHERE BILL_TYPE_NAME = '期初数据' AND SUPPLIER_ID = "; delete += record.customerOrSupplierID; DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, delete); } DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, insert); load(); } catch (Exception error) { MessageBoxExtend.messageWarning(error.Message); return; } }
private double getNewBalance(CashAccountReceivableDetailTable record) { double newBalance = 0; double oldBalance = 0; /* * 函数逻辑 * 本次交易后欠款余额的最终结果取决于本次交易单据的类型 * 如果本张单据是期初数据,交易后欠款等于本次交易额 * 如果本张单据是付款单,交易后欠款月等于之前的欠款额减去本次交易额 * 如果本张单据是销售出库,交易后欠款余额等于之前的余额加上本次交易额 * */ if (record.billTypeName.IndexOf("期初数据") != -1) { newBalance = record.balance; } else { InitAccountReceivableTable temp = InitAccountPayable.getInctance().getInfoFromCustomerOrSupplierID(record.customerOrSupplierID); if (temp != null) { oldBalance = temp.balance; } if (record.billTypeName.IndexOf("付款") != -1 || record.billTypeName == "采购退货") { newBalance = oldBalance - record.turnover; } else { newBalance = oldBalance + record.turnover; } InitAccountReceivableTable newRecord = new InitAccountReceivableTable(); newRecord.balance = newBalance; newRecord.tradingDate = record.tradingDate; newRecord.customerOrSupplierID = record.customerOrSupplierID; InitAccountPayable.getInctance().update(temp.pkey, newRecord, false); } return(newBalance); }
public SortedDictionary <int, CashAccountReceivableDetailTable> getAccountPayableDetailFromSupplierID(int supplierID) { SortedDictionary <int, CashAccountReceivableDetailTable> list = new SortedDictionary <int, CashAccountReceivableDetailTable>(); if (m_accountReceivableDetailList.Count == 0) { load(); } foreach (KeyValuePair <int, CashAccountReceivableDetailTable> index in m_accountReceivableDetailList) { CashAccountReceivableDetailTable record = new CashAccountReceivableDetailTable(); record = index.Value; if (record.customerOrSupplierID == supplierID) { list.Add(list.Count, record); } } return(list); }
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); } } }