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);
}
}
}