public void Import()
{
if (!AppConfig.IsImportable)
throw new BizException("Is Importable is false");
string sql = " select top 1 sysno from finance_popay";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
throw new BizException("the table finance_popay is not empty");
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
#region �Ȳ���Ӧ�տ��Ϊ�ϵļ�¼���ж����⣬����Ҫ�ϲ���
string sqlPOPay = @"select
ordersysno as posysno, foreignamt as poamt,
foreignalreadypay as alreadypayamt,
invoicestatus, a.status as paystatus, instocksysno,
b.newsysno as currencysysno, '1' as sysno, '' as note
from
ipp2003..finance_will_pay as a,
ippconvert..currency as b
where
a.currencysysno = b.oldsysno
and a.status <> -1
order by ordersysno";
DataSet dsPOPay = SqlHelper.ExecuteDataSet(sqlPOPay);
if (!Util.HasMoreRow(dsPOPay))
return;
POPayInfo oLastPOPay = new POPayInfo();
foreach (DataRow drPOPay in dsPOPay.Tables[0].Rows)
{
/* old paystatus
* -1 abandan
* 0 unpay
* 1 partly pay
* 2 pay
* 3 close
*/
/*old invoice status
* 0 absent
* 1 incomplete
* 2 complete
*/
POPayInfo oCurrentPOPay = new POPayInfo();
map(oCurrentPOPay, drPOPay);
switch (oCurrentPOPay.PayStatus)
{
case -1:
oCurrentPOPay.PayStatus = (int)AppEnum.POPayStatus.Abandon;
break;
case 0:
oCurrentPOPay.PayStatus = (int)AppEnum.POPayStatus.UnPay;
break;
case 1:
oCurrentPOPay.PayStatus = (int)AppEnum.POPayStatus.PartlyPay;
break;
case 2:
case 3:
oCurrentPOPay.PayStatus = (int)AppEnum.POPayStatus.FullPay;
break;
default:
break;
}
switch (oCurrentPOPay.InvoiceStatus)
{
case 0:
oCurrentPOPay.InvoiceStatus = (int)AppEnum.POPayInvoiceStatus.Absent;
break;
case 1:
oCurrentPOPay.InvoiceStatus = (int)AppEnum.POPayInvoiceStatus.Incomplete;
break;
case 2:
oCurrentPOPay.InvoiceStatus = (int)AppEnum.POPayInvoiceStatus.Complete;
break;
default:
break;
}
if (oLastPOPay.POSysNo == AppConst.IntNull)
{
oLastPOPay = oCurrentPOPay;
}
else if (oLastPOPay.POSysNo != oCurrentPOPay.POSysNo)
{
new POPayDac().InsertMaster(oLastPOPay);
oLastPOPay = oCurrentPOPay;
}
else
{
oLastPOPay.POAmt += oCurrentPOPay.POAmt;
oLastPOPay.AlreadyPayAmt += oCurrentPOPay.AlreadyPayAmt;
oLastPOPay.PayStatus = oCurrentPOPay.PayStatus;
oLastPOPay.InvoiceStatus = oCurrentPOPay.InvoiceStatus;
}
}
if (oLastPOPay != null)
{
new POPayDac().InsertMaster(oLastPOPay);
}
#endregion
#region �ٲ����տ
string sqlPOPayItem = @"select
posysno, paytype, foreignamt,
paytime, note, b.newsysno as accountsysno, a.status
from
ipp2003..purchase_order_pay as a,
ippconvert..sys_user as b
where a.accountsysno = b.oldsysno";
DataSet dsPOPayItem = SqlHelper.ExecuteDataSet(sqlPOPayItem);
if (!Util.HasMoreRow(dsPOPayItem))
throw new BizException("no pay item records in ipp2003 db");
foreach (DataRow drPOPayItem in dsPOPayItem.Tables[0].Rows)
{
/* old paytype
* 0 normal
* 1 advanced
* ����Ҫת��
*/
POPayItemInfo oPOPayItem = new POPayItemInfo();
oPOPayItem.POSysNo = Util.TrimIntNull(drPOPayItem["posysno"]);
oPOPayItem.PayStyle = Util.TrimIntNull(drPOPayItem["paytype"]);
oPOPayItem.PayAmt = Util.TrimDecimalNull(drPOPayItem["foreignamt"]);
oPOPayItem.CreateUserSysNo = Util.TrimIntNull(drPOPayItem["accountsysno"]);
oPOPayItem.CreateTime = Util.TrimDateNull(drPOPayItem["paytime"]);
oPOPayItem.PayTime = Util.TrimDateNull(drPOPayItem["paytime"]);
oPOPayItem.PayUserSysNo = Util.TrimIntNull(drPOPayItem["accountsysno"]);
oPOPayItem.Status = Util.TrimIntNull(drPOPayItem["status"]);
switch (oPOPayItem.Status)
{
case 0:
oPOPayItem.Status = (int)AppEnum.POPayItemStatus.Paid;
break;
case -1:
oPOPayItem.Status = (int)AppEnum.POPayItemStatus.Abandon;
break;
default:
throw new BizException("unknown popayitem status");
}
new POPayDac().InsertItem(oPOPayItem);
}
#endregion
scope.Complete();
}
}
private void map(POPayInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.POSysNo = Util.TrimIntNull(tempdr["POSysNo"]);
oParam.CurrencySysNo = Util.TrimIntNull(tempdr["CurrencySysNo"]);
oParam.POAmt = Util.TrimDecimalNull(tempdr["POAmt"]);
oParam.AlreadyPayAmt = Util.TrimDecimalNull(tempdr["AlreadyPayAmt"]);
oParam.PayStatus = Util.TrimIntNull(tempdr["PayStatus"]);
oParam.InvoiceStatus = Util.TrimIntNull(tempdr["InvoiceStatus"]);
oParam.InvoiceTime = Util.TrimDateNull(tempdr["InvoiceTime"]);
oParam.Note = Util.TrimNull(tempdr["Note"]);
}
public POPayInfo LoadPay(int posysno)
{
string sql = "select * from finance_popay where posysno =" + posysno;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(ds))
return null;
POPayInfo oPay = new POPayInfo();
map(oPay, ds.Tables[0].Rows[0]);
return oPay;
}
/// <summary>
/// ����Ƿ��Ǹù�Ӧ�̵����һ�ʸ���
/// </summary>
/// <param name="oPay"></param>
/// <param name="po"></param>
/// <returns></returns>
public bool IsLastPayForVendor(POPayInfo oPay, POInfo po)
{
bool result = false;
if (oPay != null)
{
if (po == null)
po = PurchaseManager.GetInstance().LoadPO(oPay.POSysNo);
if (po != null)
{
string sql = @"
SELECT dbo.PO_Master.VendorSysNo,
Finance_POPay_Item.*
FROM dbo.Finance_POPay_Item
JOIN dbo.PO_Master ON dbo.PO_Master.SysNo = dbo.Finance_POPay_Item.POSysNo
WHERE dbo.PO_Master.VendorSysNo = " + po.VendorSysNo + @"
AND dbo.Finance_POPay_Item.Status IN ( " + (int)AppEnum.POPayItemStatus.Origin + "," + (int)AppEnum.POPayItemStatus.WaitingAudit + "," + (int)AppEnum.POPayItemStatus.WaitingPay + @")
AND dbo.PO_Master.Status <>" + (int)AppEnum.POStatus.Abandon;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
if (ds.Tables[0].Rows.Count > 1)
result = false;
else
return true;
}
else
throw new BizException("���ݲ����ڣ���ȷ��֧����Ϣ�Ƿ���ȷ");
}
else
{
throw new BizException("���ݲ����ڣ���ȷ��֧����Ϣ�Ƿ���ȷ");
}
}
return result;
}
/// <summary>
/// ���븶�
/// 1 waiting apportion �� waiting instock ��poֻ������Ԥ���ĸ��
/// 2 InStock��poֻ�ܼ�������֧���ĸ��
/// 3 ���������ĸ���������г�ʼ״̬��Ԥ�����
/// 4 �Ƿ���Ӧ���a���û�У�����֮��b��������ϵ�Ӧ�������Ϊunpay״̬��c���Ӧ�����Ѿ���ȫ֧�������ܼ��븶��ˡ�
/// 5 �����븶�
/// </summary>
/// <param name="oParam"></param>
public void InsertPOPayItem(POPayItemInfo oParam)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
POInfo oPO = PurchaseManager.GetInstance().LoadPO(oParam.POSysNo);
if (oPO.Status == (int)AppEnum.POStatus.WaitingApportion || oPO.Status == (int)AppEnum.POStatus.WaitingReceive || oPO.Status == (int)AppEnum.POStatus.WaitingInStock)
{
if (oParam.PayStyle != (int)AppEnum.POPayItemStyle.Advanced)
throw new BizException("this po status allow only advanced pay");
}
else if (oPO.Status == (int)AppEnum.POStatus.InStock)
{
if (oParam.PayStyle != (int)AppEnum.POPayItemStyle.Normal)
throw new BizException("this po status allow only normal pay");
}
else
{
throw new BizException("this po status allow no pay");
}
if (oParam.PayStyle == (int)AppEnum.POPayItemStyle.Normal)
{
string sqlExist = "select top 1 sysno from finance_popay_item where posysno=" + oParam.POSysNo
+ " and status=" + (int)AppEnum.POPayItemStatus.Origin
+ " and paystyle=" + (int)AppEnum.POPayItemStyle.Advanced;
DataSet ds = SqlHelper.ExecuteDataSet(sqlExist);
if (Util.HasMoreRow(ds))
throw new BizException("there is an bill - status(origin), paystyle(advanced), insert normal pay failed");
}
POPayInfo oPay = LoadPay(oParam.POSysNo);
if (oPay == null)
{
oPay = new POPayInfo();
oPay.POSysNo = oPO.SysNo;
oPay.CurrencySysNo = oPO.CurrencySysNo;
oPay.POAmt = oPO.TotalAmt;
oPay.AlreadyPayAmt = 0;
oPay.PayStatus = (int)AppEnum.POPayStatus.UnPay;
oPay.InvoiceStatus = (int)AppEnum.POPayInvoiceStatus.Absent;
oPay.Note = "auto create";
new POPayDac().InsertMaster(oPay);
}
else if (oPay.PayStatus == (int)AppEnum.POPayStatus.FullPay)
{
throw new BizException("Full Pay already, need no pay bill");
}
else if (oPay.PayStatus == (int)AppEnum.POPayStatus.Abandon)
{
Hashtable ht = new Hashtable(2);
ht.Add("SysNo", oPay.SysNo);
ht.Add("PayStatus", (int)AppEnum.POPayStatus.UnPay);
new POPayDac().UpdateMaster(ht);
}
new POPayDac().InsertItem(oParam);
scope.Complete();
}
}
public int InsertMaster(POPayInfo oParam)
{
string sql = @"INSERT INTO Finance_POPay
(
POSysNo, CurrencySysNo, POAmt,
AlreadyPayAmt, PayStatus, InvoiceStatus, Note
)
VALUES (
@POSysNo, @CurrencySysNo, @POAmt,
@AlreadyPayAmt, @PayStatus, @InvoiceStatus, @Note
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramPOSysNo = new SqlParameter("@POSysNo", SqlDbType.Int, 4);
SqlParameter paramCurrencySysNo = new SqlParameter("@CurrencySysNo", SqlDbType.Int, 4);
SqlParameter paramPOAmt = new SqlParameter("@POAmt", SqlDbType.Decimal, 9);
SqlParameter paramAlreadyPayAmt = new SqlParameter("@AlreadyPayAmt", SqlDbType.Decimal, 9);
SqlParameter paramPayStatus = new SqlParameter("@PayStatus", SqlDbType.Int, 4);
SqlParameter paramInvoiceStatus = new SqlParameter("@InvoiceStatus", SqlDbType.Int, 4);
SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar, 500);
paramSysNo.Direction = ParameterDirection.Output;
if (oParam.POSysNo != AppConst.IntNull)
paramPOSysNo.Value = oParam.POSysNo;
else
paramPOSysNo.Value = System.DBNull.Value;
if (oParam.CurrencySysNo != AppConst.IntNull)
paramCurrencySysNo.Value = oParam.CurrencySysNo;
else
paramCurrencySysNo.Value = System.DBNull.Value;
if (oParam.POAmt != AppConst.DecimalNull)
paramPOAmt.Value = oParam.POAmt;
else
paramPOAmt.Value = System.DBNull.Value;
if (oParam.AlreadyPayAmt != AppConst.DecimalNull)
paramAlreadyPayAmt.Value = oParam.AlreadyPayAmt;
else
paramAlreadyPayAmt.Value = System.DBNull.Value;
if (oParam.PayStatus != AppConst.IntNull)
paramPayStatus.Value = oParam.PayStatus;
else
paramPayStatus.Value = System.DBNull.Value;
if (oParam.InvoiceStatus != AppConst.IntNull)
paramInvoiceStatus.Value = oParam.InvoiceStatus;
else
paramInvoiceStatus.Value = System.DBNull.Value;
if (oParam.Note != AppConst.StringNull)
paramNote.Value = oParam.Note;
else
paramNote.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramPOSysNo);
cmd.Parameters.Add(paramCurrencySysNo);
cmd.Parameters.Add(paramPOAmt);
cmd.Parameters.Add(paramAlreadyPayAmt);
cmd.Parameters.Add(paramPayStatus);
cmd.Parameters.Add(paramInvoiceStatus);
cmd.Parameters.Add(paramNote);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}
