Esempio n. 1
0
        public static List <SCInvoice> getContractInvoice(int ContractOID, List <Int32> lstInvoiceType, bool creditInvoice)
        {
            if (lstInvoiceType == null || lstInvoiceType.Count <= 0)
            {
                return(new List <SCInvoice>());
            }
            clsSqlFactory    hSql   = new clsSqlFactory();
            List <SCInvoice> Result = new List <SCInvoice>();

            try
            {
                String strSql = "select a.OID as OID, b.SRECNO , b.BILLD,b.DELD,b.PAIDDATE,b.PAIDSUM,b.CRERECNO,b.CUSTNO,c.LNAME,d.EXPL, b.SSALID,b._UNITID " +
                                ",b.BTYPE,e.SMANID as SMANID ,e.SORDNO as SORDNO,e.DEPT as DEPT, a.InvoiceNo, x.INVSUM,x.INVSUM0 " +
                                "FROM ZSC_ContractInvoice a, all_sbil b LEFT JOIN cust c on b.custno = c.CUSTNO, unit d, all_SSAL e," +
                                " (select _UNITID,SRECNO,SSALID,sum(isnull(RSUM,0)) as INVSUM,sum(isnull(RSUM/isnull(dbo.fn_AMVATValue(_UNITID,VATCD),1),0)) as INVSUM0 from all_SROW group by _UNITID,SRECNO,SSALID ) x " +
                                "WHERE a.ContractOID = ? and a.SSALID = b.SSALID and a.UnitId = b._UNITID and a.UnitId = d.UnitId and b.SSALID=e.SSALID and b._UNITID=e._UNITID and b._UNITID=x._UNITID and b.SSALID=x.SSALID and b.SRECNO=x.SRECNO ";
                if (creditInvoice == false)
                {
                    strSql += " AND b.CRERECNO IS NULL ";
                }
                if (lstInvoiceType.Count == 1)
                {
                    strSql += " AND a.InvoiceType = ?";
                    strSql += " order by a.InvoiceNo desc ";
                    hSql.NewCommand(strSql);
                    hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
                    hSql.Com.Parameters.AddWithValue("InvoiceType", lstInvoiceType[0]);
                }
                else
                {
                    strSql  = MyUtils.BuildWhereInClause(strSql, "InvoiceType", lstInvoiceType);
                    strSql += " order by a.OID desc ";
                    hSql.NewCommand(strSql);
                    hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
                    int i = 0;
                    foreach (Int32 InvoiceType in lstInvoiceType)
                    {
                        hSql.Com.Parameters.AddWithValue("InvoiceType" + i++, InvoiceType);
                    }
                }

                hSql.ExecuteReader();

                int colId;
                while (hSql.Read())
                {
                    SCInvoice item = new SCInvoice();
                    colId = hSql.Reader.GetOrdinal("OID");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.OID = hSql.Reader.GetInt32(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("SRECNO");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.SRECNO = hSql.Reader.GetInt32(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("BILLD");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.BILLD = hSql.Reader.GetDateTime(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("DELD");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.DELD = hSql.Reader.GetDateTime(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("PAIDDATE");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.PAIDDATE = hSql.Reader.GetDateTime(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("PAIDSUM");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.PAIDSUM = hSql.Reader.GetInt32(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("CRERECNO");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.SRECNO = hSql.Reader.GetInt32(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("CUSTNO");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.CustNo = hSql.Reader.GetInt32(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("LNAME");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.LNAME = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("EXPL");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.EXPL = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("SSALID");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.SSALID = hSql.Reader.GetInt32(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("_UNITID");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.UnitId = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("BTYPE");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.BTYPE = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("SMANID");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.SmanId = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("SORDNO");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.SORDNO = (Int32)hSql.Reader.GetDecimal(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("DEPT");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.DeptId = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("InvoiceNo");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.InvoiceSeqNr = hSql.Reader.GetInt32(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("INVSUM");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.INVSUM = hSql.Reader.GetDecimal(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("INVSUM0");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.INVSUM0 = hSql.Reader.GetDecimal(colId);
                    }
                    Result.Add(item);
                }
            }
            catch (Exception ex)
            {
                _log.Error("ERROR getContractInvoice " + ContractOID + ": ", ex);
                throw ex;
            }
            finally
            {
                hSql.Close();
            }
            return(Result);
        }