public int CompareTo(object obj) { POApportionSubjectInfo b = obj as POApportionSubjectInfo; int result = String.Compare(this.ListOrder, b.ListOrder); if (result >= 0) { return(1); } else { return(-1); } }
public int Insert(POApportionSubjectInfo oParam) { string sql = @"INSERT INTO PO_Apportion_Subject ( SysNo, SubjectName, ListOrder, Status ) VALUES ( @SysNo, @SubjectName, @ListOrder, @Status )"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4); SqlParameter paramSubjectName = new SqlParameter("@SubjectName", SqlDbType.NVarChar,200); SqlParameter paramListOrder = new SqlParameter("@ListOrder", SqlDbType.NVarChar,10); SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4); if ( oParam.SysNo != AppConst.IntNull) paramSysNo.Value = oParam.SysNo; else paramSysNo.Value = System.DBNull.Value; if ( oParam.SubjectName != AppConst.StringNull) paramSubjectName.Value = oParam.SubjectName; else paramSubjectName.Value = System.DBNull.Value; if ( oParam.ListOrder != AppConst.StringNull) paramListOrder.Value = oParam.ListOrder; else paramListOrder.Value = System.DBNull.Value; if ( oParam.Status != AppConst.IntNull) paramStatus.Value = oParam.Status; else paramStatus.Value = System.DBNull.Value; cmd.Parameters.Add(paramSysNo); cmd.Parameters.Add(paramSubjectName); cmd.Parameters.Add(paramListOrder); cmd.Parameters.Add(paramStatus); return SqlHelper.ExecuteNonQuery(cmd); }
public Hashtable GetPOApportionSubjectHash() { string sql = "select * from po_apportion_subject"; DataSet ds = SqlHelper.ExecuteDataSet(sql); if ( !Util.HasMoreRow(ds)) return null; Hashtable ht = new Hashtable(10); foreach(DataRow dr in ds.Tables[0].Rows) { POApportionSubjectInfo oSubject = new POApportionSubjectInfo(); map(oSubject, dr); ht.Add(oSubject.SysNo,oSubject); } return ht; }
private void map(POApportionSubjectInfo oParam, DataRow tempdr) { oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]); oParam.SubjectName = Util.TrimNull(tempdr["SubjectName"]); oParam.ListOrder = Util.TrimNull(tempdr["ListOrder"]); oParam.Status = Util.TrimIntNull(tempdr["Status"]); }
public void UpdatePOApportionSubject(POApportionSubjectInfo oParam) { string sql = "select top 1 sysno from po_apportion_subject where sysno <> " + oParam.SysNo + " and subjectname=" + Util.ToSqlString(oParam.SubjectName); DataSet ds = SqlHelper.ExecuteDataSet(sql); if ( Util.HasMoreRow(ds)) throw new BizException("the subject name exists already"); new POApportionSubjectDac().Update(oParam); }
public void InsertPOApportionSubject(POApportionSubjectInfo oParam) { string sql = "select top 1 sysno from po_apportion_subject where subjectname=" + Util.ToSqlString(oParam.SubjectName); DataSet ds = SqlHelper.ExecuteDataSet(sql); if ( Util.HasMoreRow(ds)) throw new BizException("the subject name exists already"); if ( oParam.SysNo == AppConst.IntNull ) { string sql2 = "select isnull(max(sysno),0) as maxsysno from po_apportion_subject"; DataSet ds2 = SqlHelper.ExecuteDataSet(sql2); oParam.SysNo = Util.TrimIntNull(ds2.Tables[0].Rows[0]["maxsysno"])+1; } new POApportionSubjectDac().Insert(oParam); }
public void ImportApportionSubject() { if ( !AppConfig.IsImportable) throw new BizException("Is Importable is false"); string sql = " select top 1 sysno from po_apportion_subject"; DataSet ds = SqlHelper.ExecuteDataSet(sql); if ( Util.HasMoreRow(ds) ) throw new BizException("the table po apption subject 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)) { string sqlSubject = "select sysno, ExpenseName as SubjectName, ExpenseID as ListOrder, Status from ipp2003..PO_Apportionment_Expense_Type"; DataSet dsSubject = SqlHelper.ExecuteDataSet(sqlSubject); foreach(DataRow dr in dsSubject.Tables[0].Rows) { POApportionSubjectInfo oSubject = new POApportionSubjectInfo(); map(oSubject, dr); if ( oSubject.ListOrder == AppConst.StringNull) oSubject.ListOrder = "z"; this.InsertPOApportionSubject(oSubject); } POApportionSubjectInfo oExtSubject = new POApportionSubjectInfo(); oExtSubject.SysNo = 0; oExtSubject.SubjectName = "������"; oExtSubject.ListOrder = "zzz"; oExtSubject.Status = (int)AppEnum.BiStatus.InValid; this.InsertPOApportionSubject(oExtSubject); 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(); } }