private void LoadAccountsReceivableData()
{
try
{
SIS.DBClass.DBClassAccountsReceivable DBCAR = new DBClass.DBClassAccountsReceivable();
DBCAR.DeleteAllData();
string SQLCommand = "DECLARE @tempData TABLE( " +
"[ShipID] [nvarchar](20) NULL, " +
"[CustomerID] [nvarchar](20) NULL, " +
"[CNAME] [nvarchar](20) NULL, " +
"[UnpaidAmount] [int] NULL, " +
"[ReversalAmount] [int] NULL) " +
"INSERT INTO @tempData " +
"SELECT ShipMaster.ShipID,ShipMaster.CustomerID, " +
"CustomerInfo.CNAME , ResultUnpaidAmout = case " +
"When RMAMaster.UnpaidAmount is null Then ShipMaster.UnpaidAmount " +
"Else " +
"RMAMaster.UnpaidAmount " +
"End ,ReversalAmount = case " +
"When Reversal.ReversalAmount is null Then 0 " +
"Else " +
"Reversal.ReversalAmount " +
"End " +
"FROM ShipMaster INNER JOIN " +
"CustomerInfo ON ShipMaster.CustomerID = CustomerInfo.CustomerID " +
"LEFT JOIN RMAMaster ON ShipMaster.ShipID = RMAMaster.StockIDOrShipID " +
"LEFT JOIN Reversal ON Reversal.StockIDOrShipID = ShipMaster.ShipID " +
"WHERE ShipMaster.UnpaidAmount < 0 AND (RMAMaster.UnpaidAmount is null or RMAMaster.UnpaidAmount < 0) " +
"Select * , (UnpaidAmount + ReversalAmount) as Surplus From @tempData";
My.MyDatabase myDB = new My.MyDatabase();
DataTable dt = myDB.CreateDataTable(SQLCommand, "TempAccountsReceivable");
myDB.BulkCopyToTable(dt, "AccountsReceivable");
}
catch (Exception ex)
{
MessageBox.Show("錯誤訊息:" + ex.Message.ToString(), "發生例外");
}
}