public int Insert(POApportionInfo oParam)
{
string sql = @"INSERT INTO PO_Apportion
(
SysNo,POSysNo, ApportionSubjectSysNo, ApportionType,
ExpenseAmt
)
VALUES (
@SysNo,@POSysNo, @ApportionSubjectSysNo, @ApportionType,
@ExpenseAmt
)";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramPOSysNo = new SqlParameter("@POSysNo", SqlDbType.Int,4);
SqlParameter paramApportionSubjectSysNo = new SqlParameter("@ApportionSubjectSysNo", SqlDbType.Int,4);
SqlParameter paramApportionType = new SqlParameter("@ApportionType", SqlDbType.Int,4);
SqlParameter paramExpenseAmt = new SqlParameter("@ExpenseAmt", SqlDbType.Decimal,9);
if ( oParam.SysNo != AppConst.IntNull)
paramSysNo.Value = oParam.SysNo;
else
paramSysNo.Value = System.DBNull.Value;
if ( oParam.POSysNo != AppConst.IntNull)
paramPOSysNo.Value = oParam.POSysNo;
else
paramPOSysNo.Value = System.DBNull.Value;
if ( oParam.ApportionSubjectSysNo != AppConst.IntNull)
paramApportionSubjectSysNo.Value = oParam.ApportionSubjectSysNo;
else
paramApportionSubjectSysNo.Value = System.DBNull.Value;
if ( oParam.ApportionType != AppConst.IntNull)
paramApportionType.Value = oParam.ApportionType;
else
paramApportionType.Value = System.DBNull.Value;
if ( oParam.ExpenseAmt != AppConst.DecimalNull)
paramExpenseAmt.Value = oParam.ExpenseAmt;
else
paramExpenseAmt.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramPOSysNo);
cmd.Parameters.Add(paramApportionSubjectSysNo);
cmd.Parameters.Add(paramApportionType);
cmd.Parameters.Add(paramExpenseAmt);
return SqlHelper.ExecuteNonQuery(cmd);
}
public void DeletePOApportionItem(POInfo oPO, POApportionInfo oApportion, int productSysNo)
{
if ( oPO.Status != (int)AppEnum.POStatus.WaitingApportion )
throw new BizException("the status is not waiting apportion, insert failed");
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
int rowsAffected = new POApportionDac().DeleteItem(oApportion.SysNo, productSysNo);
if ( rowsAffected != 1)
throw new BizException("insert po apportion master error");
oApportion.itemHash.Remove(productSysNo);
scope.Complete();
}
}
private void map(POApportionInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.POSysNo = Util.TrimIntNull(tempdr["POSysNo"]);
oParam.ApportionSubjectSysNo = Util.TrimIntNull(tempdr["ApportionSubjectSysNo"]);
oParam.ApportionType = Util.TrimIntNull(tempdr["ApportionType"]);
oParam.ExpenseAmt = Util.TrimDecimalNull(tempdr["ExpenseAmt"]);
}
public POInfo LoadPO(int sysno)
{
string sql = "select * from po_master where sysno = " + sysno;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( !Util.HasMoreRow(ds))
return null;
POInfo oMaster = new POInfo();
map(oMaster, ds.Tables[0].Rows[0]);
string sqlItem = "select * from po_item where posysno = " + oMaster.SysNo;
DataSet dsItem = SqlHelper.ExecuteDataSet(sqlItem);
if ( Util.HasMoreRow(dsItem))
{
foreach(DataRow drItem in dsItem.Tables[0].Rows)
{
POItemInfo oItem = new POItemInfo();
map(oItem, drItem);
oMaster.itemHash.Add(oItem.ProductSysNo, oItem);
}
}
string sqlApportion = "select * from PO_Apportion where posysno = " + oMaster.SysNo;
DataSet dsApportion = SqlHelper.ExecuteDataSet(sqlApportion);
if ( Util.HasMoreRow(dsApportion))
{
foreach(DataRow drApportion in dsApportion.Tables[0].Rows)
{
POApportionInfo oApportion = new POApportionInfo();
map(oApportion, drApportion);
oMaster.apportionHash.Add(oApportion,null);
string sqlApportionItem = "select * from po_apportion_item where apportionsysno = " + oApportion.SysNo;
DataSet dsApportionItem = SqlHelper.ExecuteDataSet(sqlApportionItem);
if ( Util.HasMoreRow(dsApportionItem))
{
foreach(DataRow drApportionItem in dsApportionItem.Tables[0].Rows)
{
POApportionItemInfo oApportionItem = new POApportionItemInfo();
map(oApportionItem, drApportionItem);
oApportion.itemHash.Add(oApportionItem.ProductSysNo, oApportionItem);
}
}
}
}
return oMaster;
}
public POApportionInfo LoadApportion(int poSysNo)
{
string sql = "select * from po_apportion where posysno=" + poSysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
POApportionInfo oInfo = new POApportionInfo();
map(oInfo,ds.Tables[0].Rows[0]);
return oInfo;
}
else
return null;
}
public void InsertPOApportionMaster(POInfo oMaster, POApportionInfo oApportionMaster)
{
foreach(POApportionInfo appMaster in oMaster.apportionHash.Keys)
{
if ( appMaster.ApportionSubjectSysNo == oApportionMaster.ApportionSubjectSysNo)
throw new BizException("the same subject already exists");
}
if ( oMaster.Status != (int)AppEnum.POStatus.WaitingApportion )
throw new BizException("the status is not waiting apportion, insert failed");
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
oApportionMaster.SysNo = SequenceDac.GetInstance().Create("PO_Apportion_Sequence");
int rowsAffected = new POApportionDac().Insert(oApportionMaster);
if ( rowsAffected != 1)
throw new BizException("insert po apportion master error");
oMaster.apportionHash.Add(oApportionMaster, null);
scope.Complete();
}
}
public void Import()
{
/* �漰������
* 1 ������¼�Ĵ���
* 2 ���Ĵ���
* 3 ״̬�Ĵ���
* 4 ���е���id�Ķ�Ӧ�����Ҫ�ر�ע��
*/
if ( !AppConfig.IsImportable)
throw new BizException("Is Importable is false");
string sql = " select top 1 sysno from po_master";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table po master 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))
{
//��Ϊpo����һЩstock�Ǵ���ģ���������ⵥ������ⵥΪ��û����ⵥ�����ó��Ϻ��ľ����ˡ�
int shanghaiStockSysNo = AppConst.IntNull;
Hashtable htStock = StockManager.GetInstance().GetStockHash(false);
foreach(StockInfo stockItem in htStock.Values)
{
if ( stockItem.StockName.IndexOf("�Ϻ�")!=-1)
{
shanghaiStockSysNo = stockItem.SysNo;
break;
}
}
if ( shanghaiStockSysNo == AppConst.IntNull )
throw new BizException("shanghai stock can't be find");
/*
* 1 �������е�po
* 2 ����po�Ƿ�����Ч������¼��
* �ϼ���Ч������������Ϊpoitem�е�������
* ��������¼�IJɹ��۸�ͳɱ�����Ϊpoitem�IJɹ��۸�̯���͵�λ�ɱ�
* �����̯������ôpo��isapportion��ֵ
* 3 ���û����Ч������¼����ô����ԭ����poitem��ֵ��ǰ��poitem��������Ϊû����̯��
* 4 �����2����̯�������ϵ���������л�ȡ�Ƶ��ߺ�ʱ�䣬���µ�poitem��ֵ���з��Ƶİ�����̯����̯����
* 5 �������po
*/
string sqlPo = @"select
po.sysno, poid, vendor_con.newsysno as vendorsysno, user_con1.newsysno as createusersysno,
isnull(purchasetime, createbaskettime) as createtime, user_con2.newsysno as auditusersysno,
leaderapprovetime as audittime, po.status, shiptype_con.newsysno as shiptypesysno, paytype_con.newsysno as paytypesysno,
po.note, po.memo, currency_con.newsysno as currencysysno, currency.exchangerate
from
ipp2003..po_master po,
ippconvert..vendor vendor_con,
ippconvert..sys_user user_con1,
ippconvert..sys_user user_con2,
ippconvert..shiptype shiptype_con,
ippconvert..paytype paytype_con,
ippconvert..currency currency_con,
ipp3..currency
where
po.vendorsysno = vendor_con.oldsysno
and po.createbasketusersysno = user_con1.oldsysno
and po.leadersysno *= user_con2.oldsysno
and po.shipviasysno = shiptype_con.oldsysno
and po.paymenttypesysno = paytype_con.oldsysno
and po.currencytype = currency_con.oldsysno
and currency_con.newsysno = currency.sysno
";
DataSet dsPo = SqlHelper.ExecuteDataSet(sqlPo);
if ( !Util.HasMoreRow(dsPo))
return;
foreach(DataRow dr in dsPo.Tables[0].Rows)
{
#region ����po����ȡ�Ŀ���������po��ֵ
/* original po status newstatus
* 0 shoppingbag abandon
* 1 abolish shopping bag abandon
* 2 unproved po origin
* 3 abolished po abandon
* 4 administrator proved po waiting in stock
* 5 account proved po waiting in stock
* 6 proved po waiting in stock
* 8 prepared in-stock-bill waiting apportion
* 9 part in warehouse instock
* 10 full in warehouse instock
*/
POInfo oPO = new POInfo();
oPO.SysNo = Util.TrimIntNull(dr["sysno"]);
oPO.POID = Util.TrimNull(dr["poid"]);
oPO.VendorSysNo = Util.TrimIntNull(dr["vendorsysno"]);
oPO.ShipTypeSysNo = Util.TrimIntNull(dr["shiptypesysno"]);
oPO.PayTypeSysNo = Util.TrimIntNull(dr["paytypesysno"]);
oPO.CurrencySysNo = Util.TrimIntNull(dr["currencysysno"]);
oPO.ExchangeRate = Util.TrimDecimalNull(dr["exchangerate"]);
oPO.CreateTime = Util.TrimDateNull(dr["CreateTime"]);
oPO.CreateUserSysNo = Util.TrimIntNull(dr["CreateUserSysNo"]);
oPO.AuditTime = Util.TrimDateNull(dr["AuditTime"]);
oPO.AuditUserSysNo = Util.TrimIntNull(dr["AuditUserSysNo"]);
oPO.Memo = Util.TrimNull(dr["Memo"]);
oPO.Note = Util.TrimNull(dr["Note"]);
#endregion
/* orginal in_stock_master status
* -1 abolished
* 0 not in stock
* 2 in shelf
*/
#region ���ϵ�poitem ����ʼ���µ�poitem
oPO.StockSysNo = shanghaiStockSysNo;
int poStatus = Util.TrimIntNull(dr["status"]);
switch(poStatus)
{
case 0:
case 1:
case 3:
oPO.Status = (int)AppEnum.POStatus.Abandon;
break;
case 2:
oPO.Status = (int)AppEnum.POStatus.Origin;
break;
case 4:
case 5:
case 6:
oPO.Status = (int)AppEnum.POStatus.WaitingInStock;
break;
case 8:
oPO.Status = (int)AppEnum.POStatus.WaitingApportion;
break;
case 9:
case 10:
//��״̬����Ϊ�ȴ���⣬����鵽��������ⵥ��ʱ���ٸ�ֵΪ���״̬��
oPO.Status = (int)AppEnum.POStatus.WaitingInStock;
break;
default:
break;
}
string sqlPOItem = @"select b.newsysno as productsysno, cast(a.orderqty as int) as orderqty, a.foreignprice from ipp2003..po_item a, ippconvert..productbasic b
where a.productsysno = b.oldsysno and posysno = " + oPO.SysNo;
DataSet dsPOItem = SqlHelper.ExecuteDataSet(sqlPOItem);
if ( Util.HasMoreRow(dsPOItem))
{
foreach(DataRow drPOItem in dsPOItem.Tables[0].Rows)
{
POItemInfo poItem = new POItemInfo();
poItem.POSysNo = oPO.SysNo;
poItem.ProductSysNo = Util.TrimIntNull(drPOItem["productsysno"]);
poItem.Quantity = Util.TrimIntNull(drPOItem["orderqty"]);
poItem.OrderPrice = Decimal.Round(Util.TrimDecimalNull(drPOItem["foreignprice"]),2);
poItem.ApportionAddOn = 0;
poItem.UnitCost = Decimal.Round(poItem.OrderPrice * oPO.ExchangeRate, 2);
poItem.Weight = 0;
oPO.itemHash.Add(poItem.ProductSysNo, poItem);
}
}
#endregion
//ȡ���һ��������Ϣ��Ϊpo����⡣
string sqlInStockMaster = @"select
con_stock.newsysno as warehousesysno,
ism.InStockTime, con_user.newsysno as InStockUserSysNo
from
ipp2003..in_stock_master ism,
ippconvert..stock con_stock,
ippconvert..sys_user con_user
where
ism.warehousesysno = con_stock.oldsysno
and ism.InStockUserSysNo = con_user.oldsysno
and (status=1 or status =2) and posysno ="
+ oPO.SysNo + " order by ism.sysno desc";
DataSet dsInStockMaster = SqlHelper.ExecuteDataSet(sqlInStockMaster);
if ( Util.HasMoreRow( dsInStockMaster ))
{
#region ȡ��Ч������¼����po, ����item
DataRow drInStockMaster = dsInStockMaster.Tables[0].Rows[0];
oPO.InTime = Util.TrimDateNull(drInStockMaster["InStockTime"]);
oPO.InUserSysNo = Util.TrimIntNull(drInStockMaster["InStockUserSysNo"]);
oPO.StockSysNo = Util.TrimIntNull(drInStockMaster["WarehouseSysNo"]);
oPO.Status = (int)AppEnum.POStatus.InStock;
string sqlInStockItem = @"select b.newsysno as productsysno, cast(sum(realqty) as int) as quantity, a.productprice, a.foreignprice from ipp2003..in_stock_item as a, ippconvert..productbasic b
where a.productsysno = b.oldsysno and instocksysno in
( select distinct sysno from ipp2003..in_stock_master where (status = 1 or status=2) and posysno=" + oPO.SysNo + ")";
sqlInStockItem += @" group by b.newsysno, productprice, foreignprice
order by b.newsysno";
DataSet dsInStockItem = SqlHelper.ExecuteDataSet(sqlInStockItem);
if ( Util.HasMoreRow(dsInStockItem))
{
//Ϊ�˷�ֹ������⣬Ҫ����ⵥΪ������Ҫת��poitem����Ҫ��ȡorderprice����Ȼ�����poitem
Hashtable htOldPOItem = new Hashtable(5);
foreach(POItemInfo poItem in oPO.itemHash.Values)
{
htOldPOItem.Add(poItem.ProductSysNo, poItem);
}
oPO.itemHash.Clear();
foreach(DataRow drInStockItem in dsInStockItem.Tables[0].Rows)
{
POItemInfo oPOItem = new POItemInfo();
POItemInfo oOldPOItem = htOldPOItem[Util.TrimIntNull(drInStockItem["ProductSysNo"])] as POItemInfo;
if ( oOldPOItem == null && oPO.SysNo == 1516)
{
oOldPOItem = new POItemInfo();
oOldPOItem.OrderPrice = Decimal.Round(Util.TrimDecimalNull(drInStockItem["ProductPrice"]),2);
}
oPOItem.POSysNo = oPO.SysNo;
oPOItem.ProductSysNo = Util.TrimIntNull(drInStockItem["ProductSysNo"]);
oPOItem.Quantity = Util.TrimIntNull(drInStockItem["quantity"]);
oPOItem.OrderPrice = oOldPOItem.OrderPrice;
oPOItem.UnitCost = Decimal.Round(Util.TrimDecimalNull(drInStockItem["ProductPrice"]),2);
decimal apportionAddOn = 0;
if ( Math.Abs(oPOItem.UnitCost - oPOItem.OrderPrice)>= 0.05M )
{
apportionAddOn = oPOItem.UnitCost - oPOItem.OrderPrice*oPO.ExchangeRate;
}
oPOItem.ApportionAddOn = Decimal.Round(apportionAddOn,2);
oPOItem.Weight = 0;
if ( apportionAddOn != 0)
{
oPO.IsApportion = (int)AppEnum.YNStatus.Yes;
}
oPO.itemHash.Add(oPOItem.ProductSysNo, oPOItem);
}
}
#endregion
}
Hashtable apportionHash = new Hashtable(10);
#region ����̯����
if ( oPO.IsApportion == AppConst.IntNull)
oPO.IsApportion = (int)AppEnum.YNStatus.No;
else
{
//ֻ��1591�Ժ��po���������������������ippversion2��ʱ��û�е�����
if ( oPO.SysNo < 1591
|| oPO.SysNo == 3446 || oPO.SysNo == 4234 ||oPO.SysNo==4235 ||oPO.SysNo==5261
|| oPO.SysNo == 5262 || oPO.SysNo == 5283 ||oPO.SysNo==5284 ||oPO.SysNo==5582) //3446ȷʵû����̯��������һ�����ɹ�
{
oPO.ApportionTime = oPO.CreateTime;
oPO.ApportionUserSysNo = oPO.CreateUserSysNo;
}
else
{
string sqlPrepare = @"select user_con.newsysno as createusersysno, createtime
from ipp2003..in_stock_prepare_master a, ippconvert..sys_user as user_con
where a.status<>-1 and a.creatorsysno = user_con.oldsysno and posysno=" + oPO.SysNo;
DataSet dsPrepare = SqlHelper.ExecuteDataSet(sqlPrepare);
if ( !Util.HasMoreRow(dsPrepare))
{
throw new BizException("need apportion, but missing prepare :" + oPO.SysNo);
}
DataRow drPrepare = dsPrepare.Tables[0].Rows[0];
oPO.ApportionTime = Util.TrimDateNull(drPrepare["createtime"]);
oPO.ApportionUserSysNo = Util.TrimIntNull(drPrepare["createusersysno"]);
}
int apportionIndex = -1;
SortedList apportionSubjectList = GetPOApportionSubjectList();
foreach(POItemInfo newPOItem in oPO.itemHash.Values)
{
POApportionInfo oPOApportion = new POApportionInfo();
apportionHash.Add(oPOApportion, null);
oPOApportion.POSysNo = oPO.SysNo;
bool found = false;
foreach(POApportionSubjectInfo itemSub in apportionSubjectList.Keys)
{
if ( itemSub.SysNo == apportionIndex )
{
found = true;
break;
}
}
if ( !found)
{
POApportionSubjectInfo newSubject = new POApportionSubjectInfo();
newSubject.SysNo = apportionIndex;
newSubject.SubjectName = "ϵͳ��������"+apportionIndex.ToString();
newSubject.ListOrder = apportionIndex.ToString();
newSubject.Status = (int)AppEnum.BiStatus.InValid;
new POApportionSubjectDac().Insert(newSubject);
apportionSubjectList = GetPOApportionSubjectList();
}
oPOApportion.ApportionSubjectSysNo = apportionIndex;
apportionIndex--;
//������ֵ
oPOApportion.ApportionType = (int)AppEnum.POApportionType.ByQuantity;
oPOApportion.ExpenseAmt = Decimal.Round(newPOItem.ApportionAddOn * newPOItem.Quantity,2);
POApportionItemInfo oPOApportionItem = new POApportionItemInfo();
oPOApportionItem.ApportionSysNo = oPOApportion.SysNo;
oPOApportionItem.ProductSysNo = newPOItem.ProductSysNo;
oPOApportion.itemHash.Add(oPOApportionItem.ProductSysNo, oPOApportionItem);
}
}
#endregion
CreatePO(oPO);
CreatePOApportion(apportionHash);
//����ɹ���;������
if ( oPO.Status == (int)AppEnum.POStatus.WaitingApportion
|| oPO.Status == (int)AppEnum.POStatus.WaitingInStock)
{
foreach(POItemInfo poitem2 in oPO.itemHash.Values)
{
InventoryManager.GetInstance().SetPurchaseQty(oPO.StockSysNo, poitem2.ProductSysNo, poitem2.Quantity);
}
}
}
#region ���po��sysno
string sqlMaxSysNo = "select top 1 sysno from ipp2003..po_master order by sysno desc";
DataSet dsMax = SqlHelper.ExecuteDataSet(sqlMaxSysNo);
if ( !Util.HasMoreRow(dsMax))
throw new BizException("got max sysno error");
int maxSysNo = Util.TrimIntNull(dsMax.Tables[0].Rows[0]["sysno"]);
// ���Զ����ɵ�sysno�Ŀǰ����ݺ�
int newSysNo;
do
{
newSysNo = SequenceDac.GetInstance().Create("PO_Sequence");
} while( newSysNo < maxSysNo);
#endregion
scope.Complete();
}
}