private void load() { string sql = "SELECT A.PKEY, A.SUPPLIER_ID, B.NAME, A.BALANCE, A.TRADING_DATE, B.CREDIT FROM INIT_ACCOUNT_PAYABLE A, BASE_SUPPLIER_LIST B "; sql += "WHERE A.SUPPLIER_ID = B.PKEY ORDER BY A.PKEY"; m_accountReceivableList.Clear(); using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql)) { foreach (DataRow row in dataTable.Rows) { InitAccountReceivableTable record = new InitAccountReceivableTable(); record.pkey = DbDataConvert.ToInt32(row[0]); record.customerOrSupplierID = DbDataConvert.ToInt32(row[1]); record.name = DbDataConvert.ToString(row[2]); record.balance = DbDataConvert.ToDouble(row[3]); record.tradingDate = DbDataConvert.ToDateTime(row[4]).ToString("yyyy-MM-dd"); record.credit = DbDataConvert.ToDouble(row[5]); m_accountReceivableList.Add(m_accountReceivableList.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); }
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 void insert(InitAccountReceivableTable record, bool isDisplayMessage = true, bool isUpdateCashAccountReceivableDatail = true) { string insert = "INSERT INTO INIT_ACCOUNT_RECEIVABLE(TRADING_DATE, CUSTOMER_ID, BALANCE) VALUES ("; // 检查数据库是否已经存在该客户应收账款信息,如果存在,本次只是累计欠款金额,然后执行更新操作 if (checkcustomerOrSupplierIDIsExist(record.customerOrSupplierID)) { InitAccountReceivableTable storageExistRecord = getInfoFromCustomerOrSuoolierID(record.customerOrSupplierID); InitAccountReceivableTable newRecord = new InitAccountReceivableTable(); newRecord.pkey = storageExistRecord.pkey; newRecord.customerOrSupplierID = record.customerOrSupplierID; newRecord.tradingDate = record.tradingDate; newRecord.balance = storageExistRecord.balance + record.balance; update(newRecord.pkey, newRecord); return; } insert += "'" + record.tradingDate + "',"; insert += record.customerOrSupplierID + ","; insert += record.balance; insert += ")"; try { DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, insert); load(); if (isUpdateCashAccountReceivableDatail) { insertToCashAccountReceivableDatail(record); } if (isDisplayMessage) { MessageBoxExtend.messageOK("数据保存成功"); } } catch (Exception error) { MessageBoxExtend.messageWarning(error.Message); return; } }
public InitAccountReceivableTable getInfoFromCustomerOrSupplierID(int customerOrSupplierID) { InitAccountReceivableTable data = new InitAccountReceivableTable(); foreach (KeyValuePair <int, InitAccountReceivableTable> index in m_accountReceivableList) { InitAccountReceivableTable record = new InitAccountReceivableTable(); record = index.Value; if (record.customerOrSupplierID == customerOrSupplierID) { data = record; break; } } return(data); }
public bool checkcustomerOrSupplierIDIsExist(int customerOrSupplierID) { bool isRet = false; foreach (KeyValuePair <int, InitAccountReceivableTable> index in m_accountReceivableList) { InitAccountReceivableTable record = new InitAccountReceivableTable(); record = index.Value; if (record.customerOrSupplierID == customerOrSupplierID) { isRet = true; break; } } return(isRet); }
public int getCustomerOrSupplierIDFromPkey(int pkey) { int customerOrSupplierID = -1; foreach (KeyValuePair <int, InitAccountReceivableTable> index in m_accountReceivableList) { InitAccountReceivableTable record = new InitAccountReceivableTable(); record = index.Value; if (record.pkey == pkey) { customerOrSupplierID = record.customerOrSupplierID; break; } } return(customerOrSupplierID); }
public InitAccountReceivableTable getAccountPayableInfoFromPkey(int pkey) { InitAccountReceivableTable accountReceivableRecord = new InitAccountReceivableTable(); foreach (KeyValuePair <int, InitAccountReceivableTable> index in m_accountReceivableList) { InitAccountReceivableTable record = new InitAccountReceivableTable(); record = index.Value; if (record.pkey == pkey) { accountReceivableRecord = record; break; } } return(accountReceivableRecord); }
public void fileImport(string filePath) { int rowIndex = 0; try { // 正式导入数据前,首先情况INIT_STORAGE_STOCK表数据 DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, "DELETE FROM [dbo].[INIT_ACCOUNT_PAYABLE]"); string sheetName = "应付账款初始数据"; if (ExcelDocProc.getInstance().openFile(filePath)) { string supplierID, value, date; for (rowIndex = 0; ; rowIndex++) { supplierID = ExcelDocProc.getInstance().getGridValue(sheetName, rowIndex, 0); value = ExcelDocProc.getInstance().getGridValue(sheetName, rowIndex, 1); date = ExcelDocProc.getInstance().getGridValue(sheetName, rowIndex, 2); if (supplierID.Length == 0) { break; } else { InitAccountReceivableTable record = new InitAccountReceivableTable(); record.customerOrSupplierID = Convert.ToInt32(supplierID); record.balance = Convert.ToDouble(value); record.tradingDate = date; insert(record, false); } } MessageBoxExtend.messageOK("应付账款初始数据导入成功"); } } catch (Exception) { MessageBoxExtend.messageWarning("文件导入失败,[" + Convert.ToString(rowIndex) + "]行数据有误,请仔细核对"); return; } }
public void update(int pkey, InitAccountReceivableTable record, bool isUpdateCashAccountPayablebleDatail = true) { string update = "UPDATE [dbo].[INIT_ACCOUNT_PAYABLE] SET "; update += "[BALANCE] = " + record.balance; update += ", [TRADING_DATE] = '" + record.tradingDate + "'"; update += " WHERE PKEY = " + Convert.ToString(pkey); try { DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, update); load(); if (isUpdateCashAccountPayablebleDatail) { insertToCashAccountPayablebleDatail(record); } } catch (Exception error) { MessageBoxExtend.messageWarning(error.Message); return; } }
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); } } }