Пример #1
0
        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);
        }
Пример #2
0
        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();
            }
        }
Пример #3
0
 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"]);
 }
Пример #4
0
        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;
        }
Пример #5
0
 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;
 }
Пример #6
0
        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();
            }
        }
Пример #7
0
        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();
            }
        }