コード例 #1
0
        public decimal getClosingBalance(string strCashBoxId, out decimal nQty)
        {
            decimal nRet = 0;

            nQty = 0;
            clsSqlFactory hSql = new clsSqlFactory();

            hSql.NewCommand("select top 1 isnull(b.AMOUNT,0),a.UPDATED_DATE, isnull(b.QTY,0) from " + objUtil.getTable("CASHREG") + " a," +
                            objUtil.getTable("CASHREGROW") + " b where a.ROWID = b.CASHREGROWID and a.CASHBOXID = ? and a.EVENT = 'O' " +
                            " order by a.UPDATED_DATE desc");
            hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
            hSql.ExecuteReader();
            hSql.Read();

            DateTime dtLastOpen      = hSql.Reader.GetDateTime(1);
            decimal  nOpeningBalance = hSql.Reader.GetDecimal(0);
            decimal  nOpeningQty     = hSql.Reader.GetDecimal(2);

            hSql.NewCommand("select isnull(sum(b.AMOUNT),0) as TOTAMOUNT, isnull(sum(b.QTY),0)  from " + objUtil.getTable("CASHTRANSH") + " a, " + objUtil.getTable("CASHTRANSR") + " b" +
                            " where a.CASHBOXID =? and a.RECEIPTNO = b.RECEIPTNO and a.UPDATED_DATE >=? ");
            hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
            hSql.Com.Parameters.AddWithValue("LASTOPEN", dtLastOpen);
            hSql.ExecuteReader();
            hSql.Read();
            nQty = hSql.Reader.GetDecimal(1) + nOpeningQty;
            nRet = hSql.Reader.GetDecimal(0) + nOpeningBalance;
            return(nRet);
        }
コード例 #2
0
        private void cmbCashBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                clsBaseListItem selectedListItem = (clsBaseListItem)cmbCashBox.SelectedItem;
                String          strCashBoxId     = selectedListItem.strValue1;//cmbCashBox.Text.Substring(0, cmbCashBox.Text.IndexOf("="));

                if (strCashBoxId != "")
                {
                    int     CashBoxInd  = CashBox.getIndexByCashBoxId(strCashBoxId);
                    String  strCurCd    = CashBox.CashBoxes.Rows[CashBoxInd]["CURCD"].ToString();
                    decimal nClosingQty = 0;
                    dfClosingBalance.Text = string.Format("{0:#,0.00}", CashBox.getClosingBalance(strCashBoxId, out nClosingQty));
                    dfClosingQty.Text     = string.Format("{0:#,0.00}", nClosingQty);
                    this.gridDenomination.Rows.Clear();
                    //Fill denomination
                    if (selectedListItem.nValue1 == 1) //Need denomination
                    {
                        gridDenomination.Enabled = true;
                        clsSqlFactory hSql = new clsSqlFactory();
                        hSql.NewCommand("select V1,C2,C4 from " + objUtil.getTable("CORW") + " a where a.CODAID ='CASHDENOM' and  a.C3 = ? ");
                        hSql.Com.Parameters.AddWithValue("CURCD", strCurCd);
                        hSql.ExecuteReader();
                        int i = 0;
                        while (hSql.Read())
                        {
                            gridDenomination.Rows.Add();
                            gridDenomination.Rows[i].Cells["colIsCoin"].Value    = hSql.Reader.GetInt32(0);
                            gridDenomination.Rows[i].Cells["colCaption"].Value   = hSql.Reader.GetString(1);
                            gridDenomination.Rows[i].Cells["colFaceValue"].Value = hSql.Reader.GetString(2);
                            gridDenomination.Rows[i].Cells["colCount"].Value     = string.Format("{0:#,0}", Int32.Parse("0"));
                            gridDenomination.Rows[i].Cells["colTotal"].Value     = string.Format("{0:#,0.00}", Decimal.Parse("0"));

                            i++;
                        }
                        gridDenomination.CurrentCell = gridDenomination.Rows[0].Cells["colCount"];
                        gridDenomination.BeginEdit(true);
                    }
                    else
                    {
                        gridDenomination.Enabled = false;
                    }
                    //En_disable OK button
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                MessageBox.Show(objUtil.Localization.getMsgString("CASHREG_03"));//Select Cashbox !
            }
            finally
            {
                disEnableButtons();
            }
        }
コード例 #3
0
ファイル: clsAMCom.cs プロジェクト: AMOutsourcing/SCPrime
        public AMComClient()
        {
            AMComClientFileName = ConfigurationManager.AppSettings["AMComClient"].ToString();
            clsSqlFactory hSql = new clsSqlFactory();

            try
            {
                SiteIds   = new String[0];
                Users     = new String[0];
                Passwords = new String[0];
                hSql.NewCommand("select UnitId, UserId, Password from Z_BASE_EXTUNIT order by UnitId ");
                if (hSql.ExecuteReader())
                {
                    int i = 0;
                    while (hSql.Read())
                    {
                        i            = resizeArray();
                        SiteIds[i]   = hSql.Reader.GetString(0);
                        Users[i]     = hSql.Reader.GetString(1);
                        Passwords[i] = hSql.Reader.GetString(2);
                    }
                }
                hSql.NewCommand("select isnull(LANGID,'ENG') from EUSR where USRSID=? ");
                hSql.Com.Parameters.Add("USRSID", objGlobal.DMSFirstUserName);
                hSql.ExecuteReader();
                if (hSql.Read())
                {
                    LangId = hSql.Reader.GetString(0);
                }
            }
            catch (Exception ex)
            {
                hSql.Rollback();
                _log.Error(ex.Message);
                throw ex;
            }
            finally
            {
                hSql.Close();
            }
        }
コード例 #4
0
        public int getReceiptNUSE(clsSqlFactory hSql, int nNuseId)
        {
            _log.Debug("getReceiptNUSE >> nUseId = " + nNuseId.ToString());
            int nRet = 0;

            hSql.NewCommand("update " + objUtil.getTable("NUSE") + " set RECNO=RECNO+1 where NUSEID = ?");
            hSql.Com.Parameters.AddWithValue("NUSEID", nNuseId);
            hSql.ExecuteNonQuery();
            hSql.NewCommand("select RECNO from " + objUtil.getTable("NUSE") + " where NUSEID = ?");
            hSql.Com.Parameters.AddWithValue("NUSEID", nNuseId);
            hSql.ExecuteReader();
            if (hSql.Read())
            {
                nRet = hSql.Reader.GetInt32(0);
            }
            _log.Debug("getReceiptNUSE << ReceipNo = " + nRet.ToString());
            return(nRet);
        }
コード例 #5
0
        public decimal getPreviousBalance(string strCashBoxId, out decimal nQty)
        {
            decimal nRet = 0;

            nQty = 0;
            clsSqlFactory hSql = new clsSqlFactory();

            hSql.NewCommand("select top 1 isnull(b.AMOUNT,0), isnull(b.QTY,0) from " + objUtil.getTable("CASHREG") + " a left join " + objUtil.getTable("CASHREGROW") + " b on a.ROWID = b.CASHREGROWID " +
                            " and  b.CASHFORM in ('CASH','CURRENCY') where  a.EVENT = 'C' and  a.CASHBOXID =? order by a.UPDATED_DATE desc");
            hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
            hSql.ExecuteReader();
            if (hSql.Read())
            {
                nRet = hSql.Reader.GetDecimal(0);
                nQty = hSql.Reader.GetDecimal(1);
            }
            return(nRet);
        }
コード例 #6
0
ファイル: clsAMCom.cs プロジェクト: AMOutsourcing/SCPrime
        public void openSPInvoice(String SiteId, int SRECNO)
        {
            String        AddParam = "-com:11 -param1:$OPENINVOICE$1 -param2:$START$1 -param3:$CLOSE$1 -param4:$APPLICATIONID$4 -param5:$INVOICENUMBER$" + SRECNO.ToString();
            clsSqlFactory hSql     = new clsSqlFactory();

            hSql.NewCommand("select a.BTYPE,a.BILLD,b.CUSTID from ALL_SBIL a, CUST b where a.SRECNO=? and a._UNITID=? and a.CUSTNO=b.CUSTNO ");
            hSql.Com.Parameters.Add("SRECNO", SRECNO);
            hSql.Com.Parameters.Add("UNITID", SiteId);
            hSql.ExecuteReader();
            if (hSql.Read())
            {
                AddParam += " -param6:$INVOICECATEGORY$" + hSql.Reader.GetString(0);
                TimeSpan ts = hSql.Reader.GetDateTime(1) - new DateTime(1900, 1, 1);
                AddParam += " -param7:$INVOICEDATE$" + ts.TotalDays.ToString();
                AddParam += " -param8:$CUSTOMERID$" + hSql.Reader.GetInt32(2).ToString();
                launchCOM(SiteId, AddParam);
            }
            hSql.Close();
        }
コード例 #7
0
        private void pbPrintClosingReport_Click(object sender, EventArgs e)
        {
            _log.Debug("pbPrintClosingReport_Click >>");
            clsSqlFactory hSql = new clsSqlFactory();

            try
            {
                String strLanguageCode = "ENG";

                hSql.NewCommand("select C1 from " + objUtil.getTable("CORW") + " where CODAID ='KIELIKOODI' and C8 = ?");
                hSql.Com.Parameters.AddWithValue("C8", objGlobal.CultureInfo);
                hSql.ExecuteReader();

                if (hSql.Read())
                {
                    strLanguageCode = hSql.Reader.GetString(0);
                }
                else
                if (ConfigurationManager.AppSettings["LangCodeAM"] != null && ConfigurationManager.AppSettings["LangCodeAM"] != "")
                {
                    strLanguageCode = ConfigurationManager.AppSettings["LangCodeAM"];
                }
                String strPrintClosing_bat = ConfigurationSettings.AppSettings["PrintClosing_bat"];
                if (objAppConfig.getStringParam("CASHREG", "P_CLOSING", "C3", "") != "")
                {
                    strPrintClosing_bat = objAppConfig.getStringParam("CASHREG", "P_CLOSING", "C3", "");
                }
                System.Diagnostics.Process process = new System.Diagnostics.Process();
                process.StartInfo.FileName  = strPrintClosing_bat;
                process.StartInfo.Arguments = strLanguageCode;
                process.Start();
            }
            catch (Exception ex)
            {
                _log.Error(ex.ToString());
                throw ex;
            }
            finally
            {
                hSql.Close();
            }
            _log.Debug("pbPrintClosingReport_Click <<");
        }
コード例 #8
0
 public bool loadRows()
 {
     Rows.Clear();
     if ((ReceiptNo > 0) && (Paid == true))
     {
         clsSqlFactory hSql = new clsSqlFactory();
         hSql.NewCommand("select CASHFORM,CASHTYPE,isnull(QTY,0),isnull(AMOUNT,0),SALETYPE,ROWNO, isnull(b.C2,'') as CASHFORM_NAME,"
                         + " isnull(c.C2,'') as CASHTYPE_NAME, isnull(FEE,0) as FEE, isnull(CC_TERMINALID,'') as CARDTYPE, isnull(d.C2,'') as CARDNAME from " + objUtil.getTable("CASHTRANSR")
                         + " left join " + objUtil.getTable("CORW") + " b on b.CODAID = 'CASHFORM' and b.C1=CASHFORM "
                         + " left join " + objUtil.getTable("CORW") + " c on c.CODAID = 'CASHTYPE' and c.C1=CASHTYPE "
                         + " left join " + objUtil.getTable("CORW") + " d on d.CODAID = 'CASHCCTYPE' and d.C1=CC_TERMINALID "
                         + " where RECEIPTNO=?");
         hSql.Com.Parameters.AddWithValue("RECEIPTNO", ReceiptNo);
         hSql.ExecuteReader();
         while (hSql.Read())
         {
             InvoiceRow aRow = new InvoiceRow();
             aRow.CashBoxId        = strCashBoxId;
             aRow.Receiptno        = ReceiptNo;
             aRow.PaymentForm.Code = hSql.Reader.GetString(0);
             aRow.PaymentType.Code = hSql.Reader.GetString(1);
             aRow.Quantity         = hSql.Reader.GetDecimal(2);
             aRow.Amount           = hSql.Reader.GetDecimal(3);
             aRow.AmountPaid       = aRow.Amount;
             aRow.AmountChange     = 0;
             aRow.SaleType         = hSql.Reader.GetString(4);
             aRow.Rowno            = hSql.Reader.GetInt32(5);
             aRow.PaymentForm.Name = aRow.PaymentForm.Code + "=" + hSql.Reader.GetString(6);
             aRow.PaymentType.Name = aRow.PaymentType.Code + "=" + hSql.Reader.GetString(7);
             aRow.AmountFee        = hSql.Reader.GetDecimal(hSql.Reader.GetOrdinal("FEE"));
             aRow.CardType.Code    = hSql.Reader.GetString(hSql.Reader.GetOrdinal("CARDTYPE"));
             aRow.CardType.Name    = aRow.CardType.Code + "=" + hSql.Reader.GetString(hSql.Reader.GetOrdinal("CARDNAME"));
             Rows.Add(aRow);
         }
         hSql.Close();
     }
     return(true);
 }
コード例 #9
0
        private void addInvoiceRows(Contract objContract, ref SCInvoice objInv, bool bCapital)
        {
            clsSqlFactory  hSql              = new clsSqlFactory();
            int            RowId             = objInv.InvItems.Count;
            Decimal        nInvoiceSum       = 0;
            Decimal        nInvoiceOrigSum   = 0;
            Decimal        nInvoiceOrigBuyPr = 0;
            int            nPayPeriod        = 1;
            clsTaxHandling objTax            = new clsTaxHandling();

            objTax.Init(2);
            String        RowVatCd = getVatCode(objTax, objInv.BTYPE);
            SCInvoiceItem objRow;

            if (objContract.IsInvoiceDetail == false)
            {
                RowId++;
                objRow        = new SCInvoiceItem();
                objRow.SROWID = RowId;
                objRow.NAME   = "~1";
                objRow.RTYPE  = 8;
                objRow.NOTE   = "CONTRACT NO " + objContract.ContractNo.ToString();
                objRow.EXIDNO = objContract.ContractNo;
                objInv.InvItems.Add(objRow);
            }
            switch (objContract.ContractCostData.CostBasis.strValue1)
            {
            case CostBasisType.Monthly:
                nInvoiceSum = objContract.ContractCostData.CostBasedOnService - objContract.ContractCapitalData.CapitalMonthAmount;
                break;

            case CostBasisType.KmOrHour:
                break;

            case CostBasisType.KmOrHourWithLump:
                nInvoiceSum = objContract.ContractCostData.CostMonthBasis - objContract.ContractCapitalData.CapitalMonthAmount;
                break;

            default:
                break;
            }
            switch (objContract.ContractPaymentData.PaymentPeriod.strValue1)
            {
            case PaymentPeriodType.Quarterly:
                nPayPeriod = 3;
                break;

            case PaymentPeriodType.HalfYear:
                nPayPeriod = 6;
                break;

            case PaymentPeriodType.Yearly:
                nPayPeriod = 12;
                break;

            default:
                break;
            }
            if (bCapital)
            {
                nInvoiceSum = objContract.ContractCapitalData.CapitalMonthAmount * nPayPeriod;
                nInvoiceSum = (Decimal)objTax.Add(nInvoiceSum, RowVatCd, objInv.BILLD);
            }
            else
            {
                if (objInv.Payer != "")
                {
                    nInvoiceSum = 0;
                }

                nInvoiceSum = (Decimal)objTax.Add(nInvoiceSum, RowVatCd, objInv.BILLD);
                nInvoiceSum = nInvoiceSum * nPayPeriod;
            }

            foreach (ContractOption objCat in objContract.listContractOptions)
            {
                if ((objCat.PartNr != null) && (objCat.PartNr != "") &&
                    (((objCat.PartialPayer == objInv.Payer) && (bCapital == false)) || ((objCat.PartialPayer == "") && (bCapital == true))
                    )
                    )
                {
                    RowId++;
                    objRow        = new SCInvoiceItem();
                    objRow.ITEMNO = objCat.PartNr;
                    if (objInv.PartPostFix != "")
                    {
                        hSql.NewCommand("select 1 from ITEM where ITEMNO=? and SUPLNO=? ");
                        hSql.Com.Parameters.Add("ITEMNO", objRow.ITEMNO + objInv.PartPostFix);
                        hSql.Com.Parameters.Add("SUPLNO", objCat.PartSuplNo);
                        hSql.ExecuteReader();
                        if (hSql.Read())
                        {
                            objRow.ITEMNO += objInv.PartPostFix;
                        }
                    }

                    objRow.SUPLNO      = objCat.PartSuplNo;
                    objRow.NAME        = objCat.Name;
                    objRow.RTYPE       = 2;
                    objRow.SROWID      = RowId;
                    objRow.BUYPR       = (Decimal)objTax.Add(objCat.PurchasePr, RowVatCd, objInv.BILLD);
                    objRow.DISCPC      = 0;
                    objRow.NUM         = 1;
                    objRow.NUM         = objRow.NUM * nPayPeriod;
                    objRow.UNITPR      = (Decimal)objTax.Add(objCat.SalePr, RowVatCd, objInv.BILLD);
                    objRow.RSUM        = (decimal)objRow.NUM * objRow.UNITPR;
                    objRow.IGROUPID    = -1;
                    objRow.VATCD       = RowVatCd;
                    nInvoiceOrigSum   += objRow.RSUM;
                    nInvoiceOrigBuyPr += objRow.BUYPR;
                    objRow.EXIDNO      = objContract.ContractNo;
                    objInv.InvItems.Add(objRow);
                    if ((objInv.Payer != "") && (bCapital == false))
                    {
                        nInvoiceSum += objRow.RSUM;
                    }
                }
            }
            if (objContract.IsInvoiceDetail == false)
            {
                RowId++;
                objRow        = new SCInvoiceItem();
                objRow.SROWID = RowId;
                objRow.NAME   = "~1";
                objRow.RTYPE  = 8;
                objRow.NOTE   = "-----------------------";
                objRow.EXIDNO = objContract.ContractNo;
                objInv.InvItems.Add(objRow);
            }
            //adapt row sum
            if ((nInvoiceOrigSum != 0) && (objInv.Payer == "") || (bCapital == true))
            {
                for (int i = 0; i < objInv.InvItems.Count; i++)
                {
                    if (objInv.InvItems[i].RTYPE != 8)
                    {
                        Decimal nOrigRSUM = objInv.InvItems[i].RSUM;
                        objInv.InvItems[i].RSUM  = nOrigRSUM * nInvoiceSum / nInvoiceOrigSum;
                        objInv.InvItems[i].BUYPR = objInv.InvItems[i].BUYPR * nInvoiceSum / nInvoiceOrigSum;
                        if (nInvoiceOrigSum > nInvoiceSum)
                        {
                            if (nOrigRSUM != 0)
                            {
                                objInv.InvItems[i].DISCPC = (nOrigRSUM - objInv.InvItems[i].RSUM) / nOrigRSUM;
                            }
                        }
                        else
                        {
                            objInv.InvItems[i].UNITPR = objInv.InvItems[i].RSUM / objInv.InvItems[i].NUM;
                        }
                    }
                }
            }
            hSql.Close();
        }
コード例 #10
0
        private void addInvoiceText(clsSqlFactory hSql, Contract objContract, ref SCInvoice objInv)
        {
            int           RowId            = objInv.InvItems.Count;
            List <String> strTexts         = new List <String>();
            List <String> strPrintingFlags = new List <String>();
            List <String> strFieldCodes    = new List <String>();
            String        strSql           = "select a.C6,b.C3 from CORW a left join CORW b on b.CODAID='INFOTEXT' and a.C4=b.C1 where a.CODAID='ZSCINVTEXT' and a.V1=1 and a.C3 like '%N%' order by a.V2 ";

            hSql.NewCommand(strSql);
            hSql.ExecuteReader();
            while (hSql.Read())
            {
                String strTmp = hSql.Reader.GetString(0);
                strPrintingFlags.Add(hSql.Reader.GetString(1));
                getFieldCodesFromString(strTmp, ref strFieldCodes);
                strTexts.Add(strTmp);
            }
            hSql.NewCommand("exec ZSC_SP_PrintContract " + objContract.ContractOID.ToString());
            hSql.ExecuteReader();
            if (hSql.Read())
            {
                int colId = -1;
                foreach (String strFieldCode in strFieldCodes)
                {
                    colId = hSql.Reader.GetOrdinal(strFieldCode);
                    if (colId >= 0)
                    {
                        String strDataType = hSql.Reader.GetDataTypeName(colId).ToUpper();
                        String strValue    = "";
                        if (!hSql.Reader.IsDBNull(colId))
                        {
                            switch (strDataType)
                            {
                            case "DATETIME":
                                strValue = hSql.Reader.GetDateTime(colId).ToShortDateString();
                                break;

                            case "INT":
                                strValue = hSql.Reader.GetInt32(colId).ToString();
                                break;

                            default:
                                strValue = hSql.Reader.GetString(colId);
                                break;
                            }
                            for (int i = 0; i < strTexts.Count; i++)
                            {
                                strTexts[i] = strTexts[i].Replace("$" + strFieldCode + "$", strValue);
                            }
                        }
                    }
                }
                for (int i = 0; i < strTexts.Count; i++)
                {
                    RowId++;
                    SCInvoiceItem objRow = new SCInvoiceItem();
                    objRow.SROWID = RowId;
                    objRow.NAME   = "";
                    objRow.RTYPE  = 8;
                    objRow.NOTE   = strTexts[i];
                    objRow.RINFO  = strPrintingFlags[i];
                    objRow.EXIDNO = objContract.ContractNo;
                    objInv.InvItems.Add(objRow);
                }
            }
        }
コード例 #11
0
        private bool invoiceContract(Contract objContract, bool bDraft, bool bManual, String Payer, int InvoiceSeqNr, bool bCapital)
        {
            bool              bRet         = true;
            clsAppConfig      objAppConfig = new clsAppConfig();
            clsGlobalVariable objGlobal    = new clsGlobalVariable();
            SCInvoice         objInv       = new SCInvoice();
            clsSqlFactory     hSql         = new clsSqlFactory();

            objInv.InvoiceSeqNr = InvoiceSeqNr;

            if (Payer == "")
            {
                if (objContract.InvoiceCustId != null)
                {
                    objInv.CustNo = objContract.InvoiceCustId.CustNr;
                }
                else
                {
                    objInv.CustNo = objContract.ContractCustId.CustNr;
                }
            }
            else
            {
                objInv.CustNo = objAppConfig.getNumberParam("ZSCCAPPAYE", Payer, "V1", "");
            }
            objInv.DCustNo     = objContract.ContractCustId.CustNr;
            objInv.UnitId      = objContract.InvoiceSiteId.strValue1;
            objInv.ContractOID = objContract.ContractOID;
            objInv.ExtOrderId  = objContract.ExtContractNo;
            if (objInv.ExtOrderId.Length > 10)
            {
                objInv.ExtOrderId = objInv.ExtOrderId.Substring(0, 10);
            }
            objInv.SalesType = objAppConfig.getStringParam("ZSCSETTING", "TRTYPE", "C3", "");
            objInv.SmanId    = objContract.RespSmanId.SmanId;

            objInv.TPCODE = objContract.ContractPaymentData.PaymentTerm.strValue1;
            objInv.TPTIME = objAppConfig.getNumberParam("MAKSUEHDOT", objInv.TPCODE, "V1", "");
            if (objContract.CostCenter.strValue1 != "")
            {
                hSql.NewCommand("select isnull(a.C4,'') from CORW a where a.CODAID='ZSCCOSTCC' and a.C3=? and a.C5=? ");
                hSql.Com.Parameters.Add("UNITID", objContract.SiteId.strValue1);
                hSql.Com.Parameters.Add("COSTCENTER", objContract.CostCenter.strValue1);
                hSql.ExecuteReader();
                if (hSql.Read())
                {
                    objInv.DeptId = hSql.Reader.GetString(0);
                }
            }

            objInv.Payer   = Payer;
            objInv.OUserId = objGlobal.DMSFirstUserName;
            objInv.VehiId  = objContract.VehiId.VehiId;
            objInv.Note    = objContract.ContractNo.ToString() + "/" + objContract.VersionNo.ToString() + "/" + objInv.InvoiceSeqNr.ToString();

            hSql.NewCommand("select isnull(a.C4,''), isnull(a.C5,'') from CORW a, VEHI b where a.CODAID='ZSCCONV' and a.C2='VEHICLASS2BTYPE' and a.C3=b.CLASS and b.VEHIID=? ");
            hSql.Com.Parameters.Add("VEHIID", objContract.VehiId.VehiId);
            hSql.ExecuteReader();
            if (hSql.Read())
            {
                objInv.BTYPE       = hSql.Reader.GetString(0);
                objInv.PartPostFix = hSql.Reader.GetString(1);
            }
            if (objContract.ContractPaymentData.PaymentGroupingLevel == PaymentGroupingType.Customer)
            {
                objInv.BTYPE = "0";
            }
            else
            {
                if (objContract.PaymentCollecType == PaymentCollectionType.Transfer)
                {
                    objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT4", "C3", "");
                }
                else if (objContract.PaymentCollecType == PaymentCollectionType.Debit)
                {
                    objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT5", "C3", "");
                }
                else if (objContract.PaymentCollecType == PaymentCollectionType.Plain)
                {
                    objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT6", "C3", "");
                }
                else
                {
                    if (bManual == true)
                    {
                        objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT2", "C3", "");
                    }
                    else
                    {
                        objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT1", "C3", "");
                    }
                }
            }
            if (Payer != "")
            {
                objInv.BTYPE = objAppConfig.getStringParam("ZSCCAPPAYE", Payer, "C3", "");
            }
            if (objContract.NextInvoiceDate == DateTime.MinValue)
            {
                objInv.BILLD = DateTime.Now;
            }
            else
            {
                objInv.BILLD = objContract.NextInvoiceDate;
            }
            int nInvoiceDay = objContract.ContractPaymentData.InvoiceDate;

            if (nInvoiceDay <= 0)
            {
                nInvoiceDay = objAppConfig.getNumberParam("ZSCSETTING", "INVDATE", "V1", "");
            }
            if (objInv.BILLD.Day > nInvoiceDay)
            {
                objInv.BILLD = new DateTime(objInv.BILLD.Year, objInv.BILLD.Month, nInvoiceDay);
                objInv.BILLD = objInv.BILLD.AddMonths(1);
            }
            else if (objInv.BILLD.Day < nInvoiceDay)
            {
                objInv.BILLD = new DateTime(objInv.BILLD.Year, objInv.BILLD.Month, nInvoiceDay);
            }
            objInv.CBILLD = new DateTime(objInv.BILLD.Year, objInv.BILLD.Month, objInv.BILLD.Day);

            switch (objContract.ContractPaymentData.PaymentPeriod.strValue1)
            {
            case PaymentPeriodType.Quarterly:
                objInv.NBILLD = objInv.CBILLD.AddMonths(3);
                break;

            case PaymentPeriodType.HalfYear:
                objInv.NBILLD = objInv.CBILLD.AddMonths(6);
                break;

            case PaymentPeriodType.Yearly:
                objInv.NBILLD = objInv.CBILLD.AddMonths(12);
                break;

            default:
                objInv.NBILLD = objInv.CBILLD.AddMonths(1);
                break;
            }
            objInv.DELD = objInv.BILLD;
            addInvoiceText(hSql, objContract, ref objInv);
            objContract.listContractOptions = ContractOption.getContractOption(objContract.ContractOID);
            //objContract.loadDetail();
            addInvoiceRows(objContract, ref objInv, bCapital);
            hSql.Close();
            bRet = objInv.saveOrder(bDraft);
            if ((bRet == true) && (bManual == true))
            {
                if (bDraft == true)
                {
                    openInvoice(objInv.UnitId, objInv.SSALID, objInv.SRECNO);
                }
                else
                {
                    openInvoicePDF(objInv.UnitId, objInv.SRECNO);
                }
            }
            return(bRet);
        }
コード例 #12
0
        public bool invoiceContract(Contract objContract, bool bDraft, bool bManual)
        {
            bool          bRet         = true;
            int           InvoiceSeqNr = 0;
            clsSqlFactory hSql         = new clsSqlFactory();

            hSql.NewCommand("select isnull(max(InvoiceNo),0) from ZSC_ContractInvoice where ContractOID=? ");
            hSql.Com.Parameters.Add("ContractOID", objContract.ContractOID);
            hSql.ExecuteReader();
            if (hSql.Read())
            {
                InvoiceSeqNr  = hSql.Reader.GetInt32(0);
                InvoiceSeqNr += 1;
            }
            List <String> Payers = new List <String>();

            hSql.NewCommand("select distinct PartialPayer from ZSC_ContractOption where ContractOid = ? and PartialPayer is not null and PartialPayer != '' and SelPr!=0 ");
            hSql.Com.Parameters.Add("ContractOID", objContract.ContractOID);
            hSql.ExecuteReader();
            while (hSql.Read())
            {
                Payers.Add(hSql.Reader.GetString(0));
            }

            if (objContract.ContractPaymentData.PaymentIsInBlock == false)
            {
                if (objContract.LastInvoiceDate <= objContract.ContractDateData.InvoiceEndDate)
                {
                    bRet = invoiceContract(objContract, bDraft, bManual, "", InvoiceSeqNr, false);
                    foreach (String Payer in Payers)
                    {
                        bRet = invoiceContract(objContract, bDraft, bManual, Payer, InvoiceSeqNr, false);
                    }

                    if ((objContract.ContractCapitalData.CapitalMonthAmount != 0) && (objContract.ContractCapitalData.CapitalMonthPayer.strValue1 != ""))
                    {
                        bRet = bRet && invoiceContract(objContract, bDraft, bManual, objContract.ContractCapitalData.CapitalMonthPayer.strValue1, InvoiceSeqNr, true);
                    }
                }
            }
            else
            {
                objContract.calcPaymentBlock(true);
                DateTime dtNextBlockEnd = objContract.ContractPaymentData.PaymentNextBlockEnd;
                if (objContract.NextInvoiceDate == DateTime.MinValue)
                {
                    objContract.NextInvoiceDate = DateTime.Now;
                }

                while ((objContract.NextInvoiceDate <= dtNextBlockEnd) && (objContract.LastInvoiceDate <= objContract.ContractDateData.InvoiceEndDate))
                {
                    bRet = invoiceContract(objContract, bDraft, bManual, "", InvoiceSeqNr, false);
                    foreach (String Payer in Payers)
                    {
                        bRet = invoiceContract(objContract, bDraft, bManual, Payer, InvoiceSeqNr, false);
                    }

                    if ((objContract.ContractCapitalData.CapitalMonthAmount != 0) && (objContract.ContractCapitalData.CapitalMonthPayer.strValue1 != ""))
                    {
                        bRet = bRet && invoiceContract(objContract, bDraft, bManual, objContract.ContractCapitalData.CapitalMonthPayer.strValue1, InvoiceSeqNr, true);
                    }
                    objContract = SCBase.searchContracts(objContract.ContractOID);
                    InvoiceSeqNr++;
                }
                objContract.calcPaymentBlock(true);

                bRet = bRet && hSql.NewCommand("update ZSC_Contract set PaymentNextBlockStart = ?,PaymentNextBlockEnd=? where OID=?");

                hSql.Com.Parameters.AddWithValue("PaymentNextBlockStart", objContract.ContractPaymentData.PaymentNextBlockStart);
                hSql.Com.Parameters.AddWithValue("PaymentNextBlockEnd", objContract.ContractPaymentData.PaymentNextBlockEnd);
                hSql.Com.Parameters.AddWithValue("ContractOID", objContract.ContractOID);
                bRet = bRet && hSql.ExecuteNonQuery();
                if (bRet)
                {
                    hSql.Commit();
                    objContract = SCBase.searchContracts(objContract.ContractOID);
                }
                else
                {
                    hSql.Rollback();
                }
            }
            hSql.Close();
            return(bRet);
        }
コード例 #13
0
        public static List <SCInvoiceItem> getInvoiceDetail(Int32 invoiceID)
        {
            if (invoiceID < 0)
            {
                return(new List <SCInvoiceItem>());
            }
            clsSqlFactory        hSql   = new clsSqlFactory();
            List <SCInvoiceItem> Result = new List <SCInvoiceItem>();

            try
            {
                clsGlobalVariable objGlobal = new clsGlobalVariable();
                String            strSql    = "select isnull(c.ITEM,'') as ITEMNO, isnull(c.NAME,'') as ITEMNAME,isnull(c.VATCD,'') as VATCD,isnull(c.NUM,0) as NUM,isnull(c.RSUM,0) as RSUM,isnull(c.NOTE,'') as NOTE,c.RTYPE as RTYPE " +
                                              ", isnull(c.RSUM,0) / isnull(dbo.fn_AMVATValue(c._UNITID,c.VATCD),1) as RSUM0 " +
                                              " FROM ZSC_ContractInvoice a, all_sbil b, ALL_SROW c " +
                                              " WHERE  a.OID=? and a.SSALID = b.SSALID and a.UnitId = b._UNITID  and b._unitid = c._unitid and b.srecno = c.srecno and b.ssalid = c.ssalid";
                hSql.NewCommand(strSql);
                hSql.Com.Parameters.AddWithValue("OID", invoiceID);
                hSql.ExecuteReader();

                int colId;

                while (hSql.Read())
                {
                    SCInvoiceItem item = new SCInvoiceItem();
                    colId = hSql.Reader.GetOrdinal("ITEMNO");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.ITEMNO = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("ITEMNAME");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.NAME = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("VATCD");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.VATCD = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("NUM");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.NUM = hSql.Reader.GetDecimal(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("RSUM");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.RSUM = hSql.Reader.GetDecimal(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("RSUM0");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.RSUM0 = hSql.Reader.GetDecimal(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("NOTE");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.NOTE = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("RTYPE");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.RTYPE = hSql.Reader.GetInt16(colId);
                    }
                    Result.Add(item);
                }
            }
            catch (Exception ex)
            {
                _log.Error("ERROR getInvoiceDetail " + invoiceID + ": ", ex);
                throw ex;
            }
            finally
            {
                hSql.Close();
            }
            return(Result);
        }
コード例 #14
0
        public bool undoPayment(clsCashBox CashBox)
        {
            _log.Debug("undoPayment >> " + ReceiptNo.ToString());
            bool              bRet      = true;
            clsSqlFactory     hSql      = new clsSqlFactory();
            clsGlobalVariable objGlobal = new clsGlobalVariable();

            try
            {
                DataRow invoiceCashbox = CashBox.CashBoxes.Select(("CASHBOXID = '" + strCashBoxId + "'"))[0];

                if (invoiceCashbox["ISOPEN"].ToString() == "1" && invoiceCashbox["PC"].ToString() == CashBox.getClientPCName() && nCreditNewNo == 0 && nCreditNote == 0 && nCrediOfNo == 0 && Rows.Count > 0)
                {
                    //Get new Receiptno
                    int nNuseId = CashBox.getVoucherIdOfType(Rows[0].PaymentType.Code, Rows[0].Amount > 0 ? true : false);

                    //Insert new TRANSH row
                    if (nNuseId > 0)
                    {
                        int nNewReceiptNo = getReceiptNUSE(hSql, nNuseId);

                        _log.Debug("New credit receiptno: " + nNewReceiptNo.ToString());
                        hSql.NewCommand("insert into " + objUtil.getTable("CASHTRANSH") + "(CASHBOXID,DEPARTMENT,SMANID,UNIT,MODULE," +
                                        "INVOICECATEGORY,CUSTNO,AMOUNTTOPAY,TOTPAID,CASHRETURNED," +
                                        "EXT_BILLD,EXT_ORDERNO,EXT_INVOICENO,INVOICENO,ORDERNO,LICNO, " +
                                        "UPDATED_DATE,TEXT,INSERTPC,UPDATED_BY, RECEIPTNO,CREDITNOTE,CREDITOFNO )" +
                                        " select a.CASHBOXID,a.DEPARTMENT,a.SMANID,a.UNIT,a.MODULE," +
                                        "a.INVOICECATEGORY,a.CUSTNO,(-1)*a.AMOUNTTOPAY,(-1)*a.TOTPAID,(-1)*a.CASHRETURNED," +
                                        "a.EXT_BILLD,a.EXT_ORDERNO,a.EXT_INVOICENO,a.INVOICENO,a.ORDERNO,a.LICNO," +
                                        "getdate(),?,?,?,?,?,? " +
                                        " from " + objUtil.getTable("CASHTRANSH") + " a where a.RECEIPTNO = ?");

                        hSql.Com.Parameters.AddWithValue("TEXT", "");
                        hSql.Com.Parameters.AddWithValue("INSERTEDPC", CashBox.getClientPCName());
                        hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
                        hSql.Com.Parameters.AddWithValue("RECEIPTNO", nNewReceiptNo);
                        hSql.Com.Parameters.AddWithValue("CREDITNOTE", 1);
                        hSql.Com.Parameters.AddWithValue("CREDITOFNO", ReceiptNo);

                        hSql.Com.Parameters.AddWithValue("RECEIPTNO", ReceiptNo);
                        bRet = bRet && hSql.ExecuteNonQuery();
                        //Update current TRANSH row
                        hSql.NewCommand("update " + objUtil.getTable("CASHTRANSH") + " set CREDITNEWNO = ? where RECEIPTNO =? ");
                        hSql.Com.Parameters.AddWithValue("CREDITNEWNO", nNewReceiptNo);
                        hSql.Com.Parameters.AddWithValue("RECEIPTNO", ReceiptNo);
                        bRet = bRet && hSql.ExecuteNonQuery();

                        nCreditNewNo = nNewReceiptNo;
                        //Get the credited amount
                        Decimal nCreditedPaidAmount = 0;
                        hSql.NewCommand("select a.TOTPAID from " + objUtil.getTable("CASHTRANSH") + " a where a.RECEIPTNO = ?");
                        hSql.Com.Parameters.AddWithValue("RECEIPTNO", nNewReceiptNo);
                        hSql.ExecuteReader();
                        if (hSql.Read())
                        {
                            nCreditedPaidAmount = hSql.Reader.GetDecimal(0);
                        }
                        //Insert new TRANSR row
                        int  i             = 0;
                        bool bNeedRounding = false;
                        while (i < Rows.Count)
                        {
                            hSql.NewCommand("insert into " + objUtil.getTable("CASHTRANSR") + "(RECEIPTNO,ROWNO,CASHFORM,CASHTYPE,QTY,AMOUNT,SALETYPE,FEE, CC_TERMINALID) " +
                                            " values(?,?,?,?,?,?,?,?,?)");

                            hSql.Com.Parameters.AddWithValue("RECEIPTNO", nNewReceiptNo);
                            hSql.Com.Parameters.AddWithValue("ROWNO", Rows[i].Rowno);
                            hSql.Com.Parameters.AddWithValue("CASHFORM", Rows[i].PaymentForm.Code);
                            hSql.Com.Parameters.AddWithValue("CASHTYPE", Rows[i].PaymentType.Code);
                            hSql.Com.Parameters.AddWithValue("QTY", Rows[i].Quantity * (-1));
                            hSql.Com.Parameters.AddWithValue("AMOUNT", Rows[i].Amount * (-1));
                            hSql.Com.Parameters.AddWithValue("SALETYPE", Rows[i].SaleType);
                            hSql.Com.Parameters.AddWithValue("FEE", Rows[i].AmountFee * (-1));
                            hSql.Com.Parameters.AddWithValue("CC_TERMINALID", Rows[i].CardType.Code);
                            bRet = bRet && hSql.ExecuteNonQuery();
                            Rows[i].CreditNewNo = nNewReceiptNo;
                            i++;
                        }
                        _log.Debug(strInvoiceModule);
                        if ((strInvoiceModule == "VA") || (strInvoiceModule == "SP") || (strInvoiceModule == "WO"))
                        {
                            hSql.NewCommand("update TEMPINV set PAYSUM=isnull(PAYSUM,0) + ? where RECNO=?  and UNITID = ?");
                            //hSql.NewCommand("update TEMPINV set PAYSUM= ? where RECNO=?  and UNITID = ?"); //and PAYDATE is not null
                            hSql.Com.Parameters.AddWithValue("PAYSUM", nCreditedPaidAmount);
                            hSql.Com.Parameters.AddWithValue("RECNO", strInvoiceNo);
                            hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId);
                            hSql.ExecuteNonQuery();

                            hSql.NewCommand("update TEMPINV set PAYDATE=null where RECNO=? and PAYDATE is not null and UNITID = ?");
                            hSql.Com.Parameters.AddWithValue("RECNO", strInvoiceNo);
                            hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId);
                            hSql.ExecuteNonQuery();

                            string strSql = "";
                            if (strInvoiceModule == "VA")
                            {
                                strSql = "update a set a.PAYD=b.PAYDATE,a.PAIDSUM=b.PAYSUM from " + objUtil.getTable("CBIL") + " a, TEMPINV b where a.CRECNO=b.RECNO and a.PAYD is not null and b.PAYDATE is null and b.RECNO=? and b.UNITID = ?";
                            }
                            if (strInvoiceModule == "SP")
                            {
                                strSql = "update a set a.PAIDDATE=b.PAYDATE,a.PAIDSUM=b.PAYSUM from " + objUtil.getTable("SBIL") + " a, TEMPINV b where a.SRECNO=b.RECNO and a.PAIDDATE is not null and b.PAYDATE is null and b.RECNO=? and b.UNITID = ?";
                            }
                            if (strInvoiceModule == "WO")
                            {
                                strSql = "update a set a.PAIDDATE=b.PAYDATE,a.PAIDSUM=b.PAYSUM from " + objUtil.getTable("GBIL") + " a, TEMPINV b where a.GRECNO=b.RECNO and a.PAIDDATE is not null and b.PAYDATE is null and b.RECNO=? and b.UNITID = ?";
                            }
                            hSql.NewCommand(strSql);
                            hSql.Com.Parameters.AddWithValue("RECNO", strInvoiceNo);
                            hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId);
                            hSql.ExecuteNonQuery();
                        }
                    }
                    else
                    {
                        _log.Error("Invalid NUSEID assigned  to the CASHTYPE : " + Rows[0].PaymentType);
                    }
                    //
                    if (bRet)
                    {
                        hSql.Commit();
                    }
                    else
                    {
                        hSql.Rollback();
                    }
                }
            }
            catch (Exception ex)
            {
                _log.Error(ex.ToString());
                bRet = false;
                hSql.Rollback();
                throw ex;
            }
            finally
            {
                hSql.Close();
            }
            _log.Debug("undoPayment << ");
            return(bRet);
        }
コード例 #15
0
        public bool saveOrder(bool bDraft)
        {
            bool            bRet    = true;
            clsSqlFactory   hSql    = new clsSqlFactory();
            clsBaseUtility  objUtil = new clsBaseUtility();
            clsCurrExchange objCurr = new clsCurrExchange();

            objCurr.Init();
            String strSql = "";

            try
            {
                if (bDraft == false)
                {
                    strSql = "select V1 from " + objUtil.getTable("CORW", UnitId) + " where CODAID='VLASKUTYY' and C1=? ";
                    bRet   = bRet && hSql.NewCommand(strSql);
                    hSql.Com.Parameters.AddWithValue("BTYPE", BTYPE);
                    hSql.ExecuteReader();
                    if (hSql.Read())
                    {
                        int NuseId = hSql.Reader.GetInt32(0);
                        strSql = "update " + objUtil.getTable("NUSE", UnitId) + " set RECNO=RECNO+1 where NUSEID=? ";
                        bRet   = bRet && hSql.NewCommand(strSql);
                        hSql.Com.Parameters.AddWithValue("NUSEID", NuseId);
                        hSql.ExecuteNonQuery();
                        strSql = "select RECNO from " + objUtil.getTable("NUSE", UnitId) + " where NUSEID=? ";
                        bRet   = bRet && hSql.NewCommand(strSql);
                        hSql.Com.Parameters.AddWithValue("NUSEID", NuseId);
                        hSql.ExecuteReader();
                        if (hSql.Read())
                        {
                            SRECNO = hSql.Reader.GetInt32(0);
                        }
                        else
                        {
                            bDraft = true;
                        }
                    }
                    else
                    {
                        bDraft = true;
                    }
                }
                strSql = "select top 1 isnull(SSALID,0), isnull(SORDNO,0) from " + objUtil.getTable("SSALSEED", UnitId);
                bRet   = bRet && hSql.ExecuteReader(strSql);
                if (bRet && hSql.Read())
                {
                    SSALID = hSql.Reader.GetInt32(0) + 1;
                    SORDNO = hSql.Reader.GetInt32(1) + 1;

                    strSql = "update " + objUtil.getTable("SSALSEED", UnitId) + " set SSALID = SSALID + 1, SORDNO = SORDNO + 1 ";
                    bRet   = bRet && hSql.ExecuteNonQuery(strSql);
                }
                strSql = "insert into " + objUtil.getTable("SSAL", UnitId) +
                         "(CREATED,SSALID, CUSTNO, SMANID, DEPT, STATUS,RECTYPE, RECORDID, SORDNO, STYPE, " +
                         " DIVISION, ISDIVIDED, BOPRIOR, OUSRSID, CDTRAN,NOTE,RDATE,EXIDNO,VEHIID)" +
                         " values (getdate(),?, ?, ?,?, ?, ?, 0, ?, ?, 0, 0, 0, ?, 0, ?,?,?,?) ";
                bRet = bRet && hSql.NewCommand(strSql);
                hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
                hSql.Com.Parameters.AddWithValue("CUSTNO", CustNo);
                hSql.Com.Parameters.AddWithValue("SMANID", SmanId);
                if (DeptId != null)
                {
                    hSql.Com.Parameters.AddWithValue("DEPT", DeptId);
                }
                else
                {
                    hSql.Com.Parameters.AddWithValue("DEPT", DBNull.Value);
                }

                if (bDraft == true)
                {
                    hSql.Com.Parameters.AddWithValue("STATUS", 'A');
                    hSql.Com.Parameters.AddWithValue("RECTYPE", DBNull.Value);
                }
                else
                {
                    hSql.Com.Parameters.AddWithValue("STATUS", 'S');
                    if (BTYPE == "0")
                    {
                        hSql.Com.Parameters.AddWithValue("RECTYPE", 'E');
                    }
                    else
                    {
                        hSql.Com.Parameters.AddWithValue("RECTYPE", 'N');
                    }
                }
                hSql.Com.Parameters.AddWithValue("SORDNO", SORDNO);
                hSql.Com.Parameters.AddWithValue("STYPE", SalesType);
                hSql.Com.Parameters.AddWithValue("OUSRSID", OUserId);
                hSql.Com.Parameters.AddWithValue("NOTE", Note);
                hSql.Com.Parameters.AddWithValue("RDATE", DELD.ToString("yyyy-MM-dd"));
                hSql.Com.Parameters.AddWithValue("EXIDNO", ExtOrderId);
                hSql.Com.Parameters.AddWithValue("VEHIID", VehiId);
                bRet = bRet && hSql.ExecuteNonQuery();


                strSql = "insert into " + objUtil.getTable("SBIL", UnitId) +
                         "(SSALID, SRECNO, CUSTNO, LCUSTNO, DCUSTNO, " +
                         " REFE, DDEL, DWP,  PDEL," +
                         " CURRCD, BTYPE,BILLD,BILLD_WITH_TIME,HSMANID,DELD,TPCODE,TPTIME)" +
                         " values (?, ?, ?, ?, ?," +
                         " ?, 0, 0, 1, " +
                         "?,?,?,?,?,?,?,?) ";
                bRet = bRet && hSql.NewCommand(strSql);
                hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
                hSql.Com.Parameters.AddWithValue("SRECNO", SRECNO);
                hSql.Com.Parameters.AddWithValue("CUSTNO", CustNo);
                hSql.Com.Parameters.AddWithValue("LCUSTNO", CustNo);
                hSql.Com.Parameters.AddWithValue("DCUSTNO", DCustNo);
                hSql.Com.Parameters.AddWithValue("REFE", Note.Length > 45 ? Note.Substring(0, 45) : Note);
                hSql.Com.Parameters.AddWithValue("CURRCD", objCurr.BaseCurrency);
                hSql.Com.Parameters.AddWithValue("BTYPE", BTYPE);
                if (bDraft == true)
                {
                    hSql.Com.Parameters.AddWithValue("BILLD", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("BILLD_WITH_TIME", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("HSMANID", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("DELD", DBNull.Value);
                }
                else
                {
                    hSql.Com.Parameters.AddWithValue("BILLD", new DateTime(BILLD.Year, BILLD.Month, BILLD.Day));
                    hSql.Com.Parameters.AddWithValue("BILLD_WITH_TIME", DateTime.Now);
                    hSql.Com.Parameters.AddWithValue("HSMANID", SmanId);
                    hSql.Com.Parameters.AddWithValue("DELD", new DateTime(DELD.Year, DELD.Month, DELD.Day));
                }

                hSql.Com.Parameters.AddWithValue("TPCODE", TPCODE);
                hSql.Com.Parameters.AddWithValue("TPTIME", TPTIME);

                bRet = bRet && hSql.ExecuteNonQuery();

                strSql = "update a set a.AGRP = b.AGRP, a.CUSTNAME = ltrim(isnull(b.FNAME,'')+' ' +isnull(b.LNAME,'')) from " +
                         objUtil.getTable("SBIL", UnitId) + " a, CUST b where a.SSALID=? and a.CUSTNO = b.CUSTNO ";
                bRet = bRet && hSql.NewCommand(strSql);
                hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
                bRet = bRet && hSql.ExecuteNonQuery();

                strSql = "update a set a.DADDR1 = b.ADDR1, a.DADDR2 = b.ADDR2,a.DCTRYCD=b.CTRYCD,a.DCOUNTRY=b.COUNTRY," +
                         " a.DNAME= ltrim(isnull(b.FNAME,'')+' ' +isnull(b.LNAME,'')), a.DPO=b.PO,a.DPOSTCD=b.POSTCD,a.DADDR2E=b.ADDR2E from " +
                         objUtil.getTable("SBIL", UnitId) + " a, CUST b where a.SSALID=? and a.DCUSTNO = b.CUSTNO ";
                bRet = bRet && hSql.NewCommand(strSql);
                hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
                bRet = bRet && hSql.ExecuteNonQuery();

                strSql = "insert into ZSC_ContractInvoice(ContractOID,InvoiceNo,SSALID,UnitId,InvoiceType,Created,Modified) select  " +
                         "?,?,SSALID,?,0,getdate(),getdate() from " + objUtil.getTable("SBIL", UnitId) + " where SSALID=?";
                bRet = bRet && hSql.NewCommand(strSql);
                hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
                hSql.Com.Parameters.AddWithValue("InvoiceNo", InvoiceSeqNr);
                hSql.Com.Parameters.AddWithValue("UnitId", UnitId);
                hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
                bRet = bRet && hSql.ExecuteNonQuery();

                strSql = "update ZSC_Contract set LastInvoiceDate = ?,NextInvoiceDate=? where OID=?";
                bRet   = bRet && hSql.NewCommand(strSql);

                hSql.Com.Parameters.AddWithValue("CBILLD", CBILLD);
                hSql.Com.Parameters.AddWithValue("NBILLD", NBILLD);
                hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
                bRet = bRet && hSql.ExecuteNonQuery();

                clsTaxHandling objTax = new clsTaxHandling();
                objTax.Init(6);



                foreach (SCInvoiceItem objRow in InvItems)
                {
                    strSql = "insert into " + objUtil.getTable("SROW", UnitId) + "(CREATED,SSALID, SROWID, SRECNO, SMANID, ITEMNO, SUPLNO, BUYPR, " +
                             " DISCPC, ITEM, SUPL, NAME, NUM, RNO, RTYPE, RSUM, UNITPR, VATCD, " +
                             " ONDEDNUM, ORDNUM, IGROUPID,NOTE,RINFO,EXIDNO) values (getdate(),?,?,?,?,?,?,?," +
                             " ?,?,?,?,?,?,?,?,?,?," +
                             "0,?,?,?,?,?)";
                    bRet = bRet && hSql.NewCommand(strSql);
                    hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
                    hSql.Com.Parameters.AddWithValue("SROWID", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("SRECNO", SRECNO);
                    hSql.Com.Parameters.AddWithValue("SMANID", SmanId);
                    hSql.Com.Parameters.AddWithValue("ITEMNO", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("SUPLNO", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("BUYPR", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("DISCPC", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("ITEM", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("SUPL", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("NAME", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("NUM", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("RNO", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("RTYPE", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("RSUM", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("UNITPR", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("VATCD", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("ORDNUM", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("IGROUPID", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("NOTE", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("RINFO", DBNull.Value);
                    hSql.Com.Parameters.AddWithValue("EXIDNO", DBNull.Value);

                    if (objRow.RTYPE != 8)
                    {
                        hSql.Com.Parameters["SROWID"].Value = objRow.SROWID;
                        hSql.Com.Parameters["ITEMNO"].Value = objRow.ITEMNO;
                        hSql.Com.Parameters["SUPLNO"].Value = objRow.SUPLNO;
                        hSql.Com.Parameters["BUYPR"].Value  = objRow.BUYPR;
                        hSql.Com.Parameters["DISCPC"].Value = objRow.DISCPC;
                        hSql.Com.Parameters["ITEM"].Value   = objRow.ITEMNO;
                        hSql.Com.Parameters["SUPL"].Value   = objRow.SUPLNO;
                        hSql.Com.Parameters["NAME"].Value   = objRow.NAME;
                        hSql.Com.Parameters["NUM"].Value    = objRow.NUM;
                        hSql.Com.Parameters["RNO"].Value    = objRow.SROWID;
                        hSql.Com.Parameters["RTYPE"].Value  = objRow.RTYPE;
                        hSql.Com.Parameters["RSUM"].Value   = objRow.RSUM;
                        hSql.Com.Parameters["UNITPR"].Value = objRow.UNITPR;
                        hSql.Com.Parameters["VATCD"].Value  = objRow.VATCD;
                        hSql.Com.Parameters["ORDNUM"].Value = objRow.NUM;
                        if (objRow.IGROUPID > 0)
                        {
                            hSql.Com.Parameters["IGROUPID"].Value = objRow.IGROUPID;
                        }
                        hSql.Com.Parameters["EXIDNO"].Value = objRow.EXIDNO;
                    }
                    else
                    {
                        hSql.Com.Parameters["SROWID"].Value = objRow.SROWID;
                        hSql.Com.Parameters["NAME"].Value   = objRow.NAME;
                        hSql.Com.Parameters["RNO"].Value    = objRow.SROWID;
                        hSql.Com.Parameters["RTYPE"].Value  = objRow.RTYPE;
                        hSql.Com.Parameters["NOTE"].Value   = objRow.NOTE;
                        if ((objRow.RINFO != null) && (objRow.RINFO != ""))
                        {
                            hSql.Com.Parameters["RINFO"].Value = objRow.RINFO;
                        }
                        hSql.Com.Parameters["EXIDNO"].Value = objRow.EXIDNO;
                    }
                    hSql.ExecuteNonQuery();
                }

                hSql.Commit();
            }
            catch (Exception ex)
            {
                hSql.Rollback();
                throw ex;
            }
            finally
            {
                hSql.Close();
            }

            return(bRet);
        }
コード例 #16
0
        public bool closeCashBox(string strCashBoxId)
        {
            _log.Debug("closeCashBox >> " + strCashBoxId);
            bool          bRet         = true;
            clsSqlFactory hSql         = new clsSqlFactory();
            clsAppConfig  objAppConfig = new clsAppConfig();

            try
            {
                int     NUSEID     = objAppConfig.getNumberParam("CASHREG", "DEPRECNO", "V1", "");
                DataRow cashbox    = CashBoxes.Select(("CASHBOXID = '" + strCashBoxId + "'"))[0];
                int     EOD_NUSEID = Int32.Parse(cashbox["EODNUSEID"].ToString());

                hSql.NewCommand("select ISOPEN from " + objUtil.getTable("CASHBOX") + " where CASHBOXID=? and ISOPEN=1");
                hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                bRet = bRet && hSql.ExecuteReader();
                if (hSql.Read())
                {
                    hSql.NewCommand("update " + objUtil.getTable("NUSE") + " set RECNO=RECNO+1 where NUSEID=?");
                    hSql.Com.Parameters.AddWithValue("NUSEID", NUSEID);
                    bRet = bRet && hSql.ExecuteNonQuery();

                    hSql.NewCommand("select RECNO from " + objUtil.getTable("NUSE") + " where NUSEID=?");
                    hSql.Com.Parameters.AddWithValue("NUSEID", NUSEID);
                    bRet = bRet && hSql.ExecuteReader();
                    if (hSql.Read())
                    {
                        int RECEIPTNO = hSql.Reader.GetInt32(0);

                        //EOD_NUSEID
                        hSql.NewCommand("update " + objUtil.getTable("NUSE") + " set RECNO=RECNO+1 where NUSEID=?");
                        hSql.Com.Parameters.AddWithValue("NUSEID", EOD_NUSEID);
                        hSql.ExecuteNonQuery();

                        hSql.NewCommand("select RECNO from " + objUtil.getTable("NUSE") + " where NUSEID=?");
                        hSql.Com.Parameters.AddWithValue("NUSEID", EOD_NUSEID);
                        bRet = bRet && hSql.ExecuteReader();

                        if (hSql.Read())
                        {
                            int EOD_RECEIPTNO = hSql.Reader.GetInt32(0);
                            //
                            hSql.NewCommand("update " + objUtil.getTable("CASHBOX") + " set ISOPEN=0 where CASHBOXID=?");
                            hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                            hSql.ExecuteNonQuery();
                            hSql.NewCommand("insert into " + objUtil.getTable("CASHREG") + "(CASHBOXID,SMANID,EVENT,UPDATED_DATE,UPDATED_BY,RECEIPTNO,EOD_RECEIPTNO) values(?,?,'C',getdate(),?,?,?)");
                            hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                            hSql.Com.Parameters.AddWithValue("SMANID", objGlobal.DefaultSManID);
                            hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
                            hSql.Com.Parameters.AddWithValue("RECEIPTNO", RECEIPTNO);
                            hSql.Com.Parameters.AddWithValue("EOD_RECEIPTNO", EOD_RECEIPTNO);
                            hSql.ExecuteNonQuery();
                            hSql.Com.Parameters.Clear();
                            hSql.NewCommand("SELECT @@IDENTITY");
                            bRet = bRet && hSql.ExecuteReader();
                            hSql.Read();
                            int nCashRegRowId = hSql.Reader.GetInt32(0);

                            hSql.NewCommand("select top 1 isnull(b.AMOUNT,0),a.UPDATED_DATE, isnull(b.QTY,0) from " + objUtil.getTable("CASHREG") + " a," +
                                            " CASHREGROW b where a.ROWID = b.CASHREGROWID and a.CASHBOXID = ? and a.EVENT = 'O' " +
                                            " order by a.UPDATED_DATE desc");
                            hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                            bRet = bRet && hSql.ExecuteReader();
                            hSql.Read();

                            DateTime dtLastOpen      = hSql.Reader.GetDateTime(1);
                            decimal  nOpeningBalance = hSql.Reader.GetDecimal(0);

                            decimal nOpeningQty = hSql.Reader.GetDecimal(2);

                            hSql.NewCommand("insert into " + objUtil.getTable("CASHREGROW") + "(CASHREGROWID,ROWNO,CASHFORM,CASHTYPE,AMOUNT,QTY,UPDATED_DATE,UPDATED_BY) " +
                                            " select ?, row_number() over (partition by getdate() order by getdate()),CASHFORM,CASHTYPE,isnull(TOTAMOUNT,0),isnull(TOTQTY,0),getdate(),? from " +
                                            "(select b.CASHFORM,case b.CASHFORM when 'CASH' then '" + ConfigurationSettings.AppSettings["DefaultCashType"] + "' else b.CASHTYPE end as CASHTYPE,sum(b.AMOUNT) as TOTAMOUNT,sum(b.QTY) as TOTQTY from " + objUtil.getTable("CASHTRANSH") + " a, " + objUtil.getTable("CASHTRANSR") + " b" +
                                            " where a.CASHBOXID =? and a.RECEIPTNO = b.RECEIPTNO and a.UPDATED_DATE >=? " +
                                            " group by b.CASHFORM,case b.CASHFORM when 'CASH' then '" + ConfigurationSettings.AppSettings["DefaultCashType"] + "' else b.CASHTYPE end ) x");
                            hSql.Com.Parameters.AddWithValue("CASHREGROWID", nCashRegRowId);
                            hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);

                            hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                            hSql.Com.Parameters.AddWithValue("LASTOPEN", dtLastOpen);

                            bRet = bRet && hSql.ExecuteNonQuery();

                            hSql.NewCommand("select 1 from " + objUtil.getTable("CASHREGROW") + " where CASHREGROWID=? and CASHFORM in ('CASH','CURRENCY')");
                            hSql.Com.Parameters.AddWithValue("CASHREGROWID", nCashRegRowId);
                            bRet = bRet && hSql.ExecuteReader();
                            if (hSql.Read())
                            {
                                hSql.NewCommand("update " + objUtil.getTable("CASHREGROW") + " set AMOUNT=AMOUNT+?, QTY=QTY+? where CASHREGROWID=? and CASHFORM in ('CASH','CURRENCY') ");
                                hSql.Com.Parameters.AddWithValue("OPENBALANCE", nOpeningBalance);
                                hSql.Com.Parameters.AddWithValue("OPENQTY", nOpeningQty);
                                hSql.Com.Parameters.AddWithValue("CASHREGROWID", nCashRegRowId);
                                bRet = bRet && hSql.ExecuteNonQuery();
                            }
                            else
                            {
                                hSql.NewCommand("insert into " + objUtil.getTable("CASHREGROW") + "(CASHREGROWID,ROWNO,CASHFORM,CASHTYPE,AMOUNT,QTY,UPDATED_DATE,UPDATED_BY) values(" +
                                                "  ?, (select isnull(max(ROWNO),0)+1 from CASHREGROW where CASHREGROWID=? ),'CASH',?,?,?,getdate(),? )");
                                hSql.Com.Parameters.AddWithValue("CASHREGROWID", nCashRegRowId);
                                hSql.Com.Parameters.AddWithValue("CASHREGROWID2", nCashRegRowId);
                                hSql.Com.Parameters.AddWithValue("CASHTYPE", ConfigurationSettings.AppSettings["DefaultCashType"]);

                                hSql.Com.Parameters.AddWithValue("AMOUNT", nOpeningBalance);
                                hSql.Com.Parameters.AddWithValue("QTY", nOpeningQty);

                                hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);

                                hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                                bRet = bRet && hSql.ExecuteNonQuery();
                            }
                            //Update CASHTRANSH.EOD_RECEIPTNO

                            hSql.NewCommand("update " + objUtil.getTable("CASHTRANSH") + " set EOD_RECEIPTNO =? where CASHBOXID =? and EOD_RECEIPTNO is null and UPDATED_DATE >=?");
                            hSql.Com.Parameters.AddWithValue("EOD_RECEIPTNO", EOD_RECEIPTNO);
                            hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                            hSql.Com.Parameters.AddWithValue("LASTOPEN", dtLastOpen);
                            bRet = bRet && hSql.ExecuteNonQuery();
                            //
                            hSql.NewCommand("delete from " + objUtil.getTable("CASHBOXPC") + " where CASHBOXID = ?");
                            hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                            bRet = bRet && hSql.ExecuteNonQuery();
                        }
                        else
                        {
                            bRet = false;
                            throw new Exception("Invalid EODNUSEID (CASHBOXID.V3) ): " + NUSEID);
                        }
                    }
                    else
                    {
                        bRet = false;
                        throw new Exception("Invalid NUSEID (CASHREG.DEPRECNO): " + NUSEID);
                    }
                }
                else
                {
                    bRet = false;
                }
                if (bRet)
                {
                    hSql.Commit();
                }
                else
                {
                    hSql.Rollback();
                }
            }
            catch (Exception ex)
            {
                _log.Error(ex.ToString());
                hSql.Rollback();
                bRet = false;
                throw ex;
            }
            finally
            {
                hSql.Close();
            }
            _log.Debug("closeCashBox >> " + bRet.ToString());
            return(bRet);
        }
コード例 #17
0
        public static List <Invoice> searchInvoices(string namephrase, bool isPaid, bool isIncludeCredit, int SearchFlags)
        {
            List <Invoice>    Results      = new List <Invoice>();
            clsBaseUtility    locObjUtil   = new clsBaseUtility();
            clsGlobalVariable locObjGlobal = new clsGlobalVariable();
            clsSqlFactory     hSql         = new clsSqlFactory();

            try
            {
                string searchString = namephrase;

                RegexOptions options = RegexOptions.None;
                Regex        regex   = new Regex(@"[ ]{2,}", options);
                searchString = regex.Replace(searchString, @" ");

                string[] words = searchString.Split(new char[] { ' ' });

                int    i        = 0;
                string strOrder = "";
                string strSql   = "";
                string strCUST  = locObjUtil.getTable("CUST");
                string strASVIEW_CAREG_UNPAID_INVOICE = strCUST == "CUST" ? "ASVIEW_CAREG_UNPAID_INVOICE" : "ASVIEW_CAREG_UNPAID_INVOICE" + locObjGlobal.CurrentSiteId;
                if (SearchFlags > 0)
                {
                    strOrder = "";
                    strSql   = "select top " + ConfigurationManager.AppSettings["SearchResultNumber"].ToString() + " a.ORDNO,isnull(a.CUSTNO,''),isnull(a.LNAME,''),isnull(a.FNAME,''),a.BILLD,isnull(a.RECNO,''),a.INVSUM,a.APPAREA, isnull(a.DEPT,'') as DEPT,isnull(a.PAYSUM,0) from " + strASVIEW_CAREG_UNPAID_INVOICE + " a ";

                    while (i < words.Length)
                    {
                        if (words[i] != "")
                        {
                            strSql += " inner join containstable(" + strASVIEW_CAREG_UNPAID_INVOICE + ", *, '\"" + words[i] + "*\"' ) as T" + i.ToString() + " on a.INVID = T" + i.ToString() + ".[KEY]  ";
                            if (i > 0)
                            {
                                strOrder += "+";
                            }
                            strOrder += "T" + i.ToString() + ".RANK";
                        }
                        i++;
                    }

                    //if (strOrder!="") strOrder = " order by " + strOrder + " desc, a.BILLD desc, a.RECNO desc ";
                    strOrder = " order by a.BILLD desc, a.RECNO desc ";

                    strSql = strSql + " where a.UNITID =? and (1=0 ";

                    if ((SearchFlags & InvoiceFlags.VehicleSalesFlag) == InvoiceFlags.VehicleSalesFlag)
                    {
                        strSql = strSql + " or APPAREA = 'C' ";
                    }
                    if ((SearchFlags & InvoiceFlags.SparePartSalesFlag) == InvoiceFlags.SparePartSalesFlag)
                    {
                        strSql = strSql + " or APPAREA = 'S' ";
                    }
                    if ((SearchFlags & InvoiceFlags.WorkshopFlag) == InvoiceFlags.WorkshopFlag)
                    {
                        strSql = strSql + " or APPAREA = 'G' ";
                    }

                    //  strSql += ") and not exists( select 1 from TEMPINV x where x.UNITID = a.UNITID and a.RECNO = x.CRERECNO) " + strOrder;
                    strSql += ") and ( exists (select 1 from ALL_CBIL bil where bil.CRECNO = a.RECNO ) or exists (select 1 from ALL_GBIL bil where bil.GRECNO = a.RECNO ) or exists (select 1 from ALL_SBIL bil where bil.SRECNO = a.RECNO ) ) ";
                    if (!isIncludeCredit)
                    {
                        //Exclude credited
                        strSql += " and not exists( select 1 from TEMPINV x where x.UNITID = a.UNITID and a.RECNO = x.CRERECNO) ";
                        //Exclude credit
                        strSql += " and not exists( select 1 from TEMPINV x where x.UNITID = a.UNITID and a.RECNO = x.RECNO and x.CREDIT = 1 ) ";
                    }
                    //
                    strSql += strOrder;

                    _log.Debug(strSql);

                    hSql.NewCommand(strSql);
                    hSql.Com.Parameters.AddWithValue("UNITID", locObjGlobal.CurrentSiteId);
                    if (hSql.ExecuteReader())
                    {
                        while (hSql.Read())
                        {
                            Invoice Inv = new Invoice();
                            Inv.strOrderNo        = hSql.GetString(0).ToString();
                            Inv.strCustNo         = hSql.GetString(1).ToString();
                            Inv.strCustomerName   = hSql.GetString(2).ToString() + " " + hSql.GetString(3).ToString();
                            Inv.strStatus         = locObjUtil.Localization.getMsgString("CASHREG_10");    //Unpaid
                            Inv.strInvoiceNo      = hSql.GetString(5);
                            Inv.dtInvoiceDate     = hSql.GetDateTime(4);                                   //.ToString("yyyy.MM.dd"));
                            Inv.decInvoiceSum     = hSql.Reader.GetDecimal(6) - hSql.Reader.GetDecimal(9); //.ToString("N6").TrimEnd('0').Trim(',');
                            Inv.Paid              = false;
                            Inv.strDeptId         = hSql.GetString(8);
                            Inv.decInvoiceSumOrig = hSql.Reader.GetDecimal(6);
                            switch (hSql.GetString(7))
                            {
                            case "C":
                            {
                                Inv.InvoiceFlag      = InvoiceFlags.VehicleSalesFlag;
                                Inv.strType          = locObjUtil.Localization.getMsgString("CASHREG_07");//"Vehicle sales"
                                Inv.strInvoiceModule = "VA";
                                break;
                            }

                            case "G":
                            {
                                Inv.InvoiceFlag      = InvoiceFlags.WorkshopFlag;
                                Inv.strType          = locObjUtil.Localization.getMsgString("CASHREG_09");//"Workshop"
                                Inv.strInvoiceModule = "WO";
                                break;
                            }

                            case "S":
                            {
                                Inv.InvoiceFlag      = InvoiceFlags.SparePartSalesFlag;
                                Inv.strType          = locObjUtil.Localization.getMsgString("CASHREG_06");//"Spare part sales"
                                Inv.strInvoiceModule = "SP";
                                break;
                            }
                            }
                            Results.Add(Inv);
                        }
                    }
                }
                //--------------------------------------------------------------------Paid part------------------------------------------------
                if (isPaid == true)
                {
                    //                          0           1                   2                   3            4            5                 6          7                8                9       10
                    strSql = "select top " + ConfigurationManager.AppSettings["SearchResultNumber"].ToString() + " a.ORDNO,isnull(a.CUSTNO,''),isnull(a.LNAME,'') as LNAME,isnull(a.FNAME,''),a.BILLD,isnull(a.RECNO,''),isnull(a.AMOUNTTOPAY,0),isnull(a.TOTPAID,0),isnull(a.RECEIPTNO,''),a.PAYDATE " +
                             " , a.CASHBOXID,a.MODULE,a.DEPARTMENT,b.TEXT,isnull(a.LICNO,'') as LICNO,isnull(b.CREDITNEWNO,0) as CREDITNEWNO,ISNULL(b.CREDITNOTE,0) as CREDITNOTE, ISNULL(b.CREDITOFNO,0) as CREDITOFNO from " + locObjUtil.getTable("ASVIEW_CAREG_PAID_INVOICE") + " a inner join " + locObjUtil.getTable("CASHTRANSH") + " b on a._OID=b._OID ";

                    i        = 0;
                    strOrder = "";
                    while (i < words.Length)
                    {
                        if (words[i] != "")
                        {
                            strSql += " inner join containstable(ASVIEW_CAREG_PAID_INVOICE" + locObjGlobal.CurrentSiteId + ", *, '\"" + words[i] + "*\"' ) as T" + i.ToString() + " on a._OID = T" + i.ToString() + ".[KEY]  ";
                            if (i > 0)
                            {
                                strOrder += "+";
                            }
                            strOrder += "T" + i.ToString() + ".RANK";
                        }
                        i++;
                    }
                    //if (strOrder!="") strOrder = " order by " + strOrder + " desc, a.PAYDATE desc";
                    strOrder = " order by a.BILLD desc, a.RECNO desc,a.PAYDATE desc ";
                    strSql   = strSql + " where (1=0  ";
                    if ((SearchFlags & InvoiceFlags.VehicleSalesFlag) == InvoiceFlags.VehicleSalesFlag)
                    {
                        strSql = strSql + " or a.MODULE = 'VA' ";
                    }
                    if ((SearchFlags & InvoiceFlags.SparePartSalesFlag) == InvoiceFlags.SparePartSalesFlag)
                    {
                        strSql = strSql + " or a.MODULE = 'SP' ";
                    }
                    if ((SearchFlags & InvoiceFlags.WorkshopFlag) == InvoiceFlags.WorkshopFlag)
                    {
                        strSql = strSql + " or a.MODULE = 'WO' ";
                    }
                    if ((SearchFlags & InvoiceFlags.CashRegisterFlag) == InvoiceFlags.CashRegisterFlag)
                    {
                        strSql = strSql + " or a.MODULE in ('CU','SU','CR') ";
                    }

                    strSql += ") " + strOrder;
                    _log.Debug(strSql);

                    hSql.NewCommand(strSql);

                    if (hSql.ExecuteReader())
                    {
                        while (hSql.Read())
                        {
                            //try
                            {
                                Invoice Inv = new Invoice();
                                Inv.strOrderNo        = hSql.GetString(0).ToString();
                                Inv.strCustNo         = hSql.GetString(1).ToString();
                                Inv.strCustomerName   = hSql.GetString(2).ToString() + " " + hSql.GetString(3).ToString();
                                Inv.strStatus         = locObjUtil.Localization.getMsgString("CASHREG_11");//Paid
                                Inv.strInvoiceNo      = hSql.GetString(5);
                                Inv.ReceiptNo         = hSql.Reader.GetInt32(8);
                                Inv.dtInvoiceDate     = hSql.GetDateTime(4);       //.ToString("yyyy.MM.dd"));
                                Inv.dtPaymentDate     = hSql.GetDateTime(9);       //.ToString("yyyy.MM.dd"));
                                Inv.decInvoiceSumOrig = hSql.Reader.GetDecimal(6); //.ToString("N6").TrimEnd('0').Trim(',');
                                Inv.decInvoiceSum     = 0;
                                Inv.decPaymentSum     = hSql.Reader.GetDecimal(7); //.ToString("N6").TrimEnd('0').Trim(',');
                                Inv.strCashBoxId      = hSql.Reader.GetString(10);
                                Inv.strInvoiceModule  = hSql.GetString(11);

                                Inv.strDeptId    = hSql.GetString(12);
                                Inv.strRemark    = hSql.GetString(13);
                                Inv.strLicno     = hSql.GetString(14);
                                Inv.nCreditNewNo = hSql.Reader.GetInt32(15);
                                Inv.nCreditNote  = hSql.Reader.GetInt32(16);
                                Inv.nCrediOfNo   = hSql.Reader.GetInt32(17);
                                switch (Inv.strInvoiceModule)
                                {
                                case "VA":
                                {
                                    Inv.InvoiceFlag = InvoiceFlags.VehicleSalesFlag;
                                    Inv.strType     = locObjUtil.Localization.getMsgString("CASHREG_11"); break;        //"Vehicle sales"
                                }

                                case "WO":
                                {
                                    Inv.InvoiceFlag = InvoiceFlags.WorkshopFlag;
                                    Inv.strType     = locObjUtil.Localization.getMsgString("CASHREG_09"); break;        //"Workshop"
                                }

                                case "SP":
                                {
                                    Inv.InvoiceFlag = InvoiceFlags.SparePartSalesFlag;
                                    Inv.strType     = locObjUtil.Localization.getMsgString("CASHREG_06"); break;        //"Spare part sales"
                                }

                                default:
                                {
                                    Inv.InvoiceFlag = InvoiceFlags.CashRegisterFlag;
                                    Inv.strType     = locObjUtil.Localization.getMsgString("CASHREG_08"); break;        //"Cash Register"
                                }
                                }
                                Inv.Paid = true;
                                Results.Add(Inv);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);

                throw ex;
            }
            finally
            {
                hSql.Close();
            }
            return(Results);
        }
コード例 #18
0
        public bool openCashBox(string strCashBoxId, decimal nOpeningBalance, decimal nQty)
        {
            bool bRet = true;

            _log.Debug("openCashBox >> SmanId = " + objGlobal.DefaultSManID + ", UserName  = "******"select ISOPEN from " + objUtil.getTable("CASHBOX") + " where CASHBOXID=?");
                hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                hSql.ExecuteReader();
                if (hSql.Read())
                {
                    if (hSql.Reader.GetInt16(0) == 1)
                    {
                        bRet = false;
                    }
                    else
                    {
                        hSql.NewCommand("update " + objUtil.getTable("CASHBOX") + " set ISOPEN=1 where CASHBOXID=?");
                        hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                        bRet = hSql.ExecuteNonQuery();
                    }
                }
                else
                {
                    hSql.NewCommand("insert into " + objUtil.getTable("CASHBOX") + "(CASHBOXID,ISOPEN) values(?,1)");
                    hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                    bRet = hSql.ExecuteNonQuery();
                }
                string strComputerName = getClientPCName();
                hSql.NewCommand("select 1 from " + objUtil.getTable("CASHBOXPC") + " where CASHBOXID=? and PC=?");
                hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                hSql.Com.Parameters.AddWithValue("PC", strComputerName);
                bRet = bRet && hSql.ExecuteReader();
                //clsGlobalVariable objGlobal = new clsGlobalVariable();
                if (bRet)
                {
                    if (hSql.Read())
                    {
                        hSql.NewCommand("update " + objUtil.getTable("CASHBOXPC") + " set UPDATED_DATE=getdate(),UPDATED_BY=? where CASHBOXID=? and PC=?");
                        hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
                        hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                        hSql.Com.Parameters.AddWithValue("PC", strComputerName);
                        bRet = bRet && hSql.ExecuteNonQuery();
                    }
                    else
                    {
                        hSql.NewCommand("insert into " + objUtil.getTable("CASHBOXPC") + "(CASHBOXID,PC,UPDATED_DATE,UPDATED_BY) values(?,?,getdate(),?)");
                        hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                        hSql.Com.Parameters.AddWithValue("PC", strComputerName);
                        hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);

                        bRet = bRet && hSql.ExecuteNonQuery();
                    }
                }
                if (bRet)
                {
                    hSql.NewCommand("insert into " + objUtil.getTable("CASHREG") + "(CASHBOXID,SMANID,EVENT,UPDATED_DATE,UPDATED_BY) values(?,?,'O',getdate(),?)");
                    hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
                    hSql.Com.Parameters.AddWithValue("SMANID", objGlobal.DefaultSManID);
                    hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
                    hSql.ExecuteNonQuery();
                    hSql.Com.Parameters.Clear();
                    hSql.NewCommand("SELECT @@IDENTITY");
                    hSql.ExecuteReader();
                    hSql.Read();

                    int nCashRegRowId = hSql.Reader.GetInt32(0);
                    hSql.NewCommand("insert into " + objUtil.getTable("CASHREGROW") + "(CASHREGROWID,ROWNO,CASHFORM,CASHTYPE,AMOUNT,UPDATED_DATE,UPDATED_BY,QTY) values(?,1,'CASH',?,?,getdate(),?,?)");
                    hSql.Com.Parameters.AddWithValue("CASHREGROWID", nCashRegRowId);
                    hSql.Com.Parameters.AddWithValue("CASHTYPE", ConfigurationSettings.AppSettings["DefaultCashType"]);
                    hSql.Com.Parameters.AddWithValue("AMOUNT", nOpeningBalance);
                    hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
                    hSql.Com.Parameters.AddWithValue("QTY", nQty);
                    hSql.ExecuteNonQuery();
                }
                if (bRet)
                {
                    hSql.Commit();
                }
                else
                {
                    hSql.Rollback();
                }
            }
            catch (Exception ex)
            {
                hSql.Rollback();
                _log.Error(ex.ToString());
                bRet = false;
            }
            finally
            {
                hSql.Close();
            }
            return(bRet);
        }
コード例 #19
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);
        }
コード例 #20
0
        public static List <SCContractRemark> getRemark(int ContractOID)
        {
            clsSqlFactory           hSql   = new clsSqlFactory();
            List <SCContractRemark> Result = new List <SCContractRemark>();

            try
            {
                clsGlobalVariable objGlobal = new clsGlobalVariable();
                String            strSql    = "select a.OID as OID, a.ContractOID as ContractOID , a.Created as Created,a.UserId as UserId,a.RemarkType as RemarkType,a.Info as Info " +
                                              "FROM ZSC_ContractRemark a " +
                                              "WHERE a.ContractOID = ? ORDER BY a.Created DESC ";
                hSql.NewCommand(strSql);
                hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
                hSql.ExecuteReader();
                int colId;
                while (hSql.Read())
                {
                    SCContractRemark item = new SCContractRemark();
                    colId = hSql.Reader.GetOrdinal("OID");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.OID = hSql.Reader.GetInt32(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("ContractOID");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.ContractOID = hSql.Reader.GetInt32(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("Created");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.Created = hSql.Reader.GetDateTime(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("UserId");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.UserId = hSql.Reader.GetString(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("RemarkType");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.RemarkType = hSql.Reader.GetInt16(colId);
                    }
                    colId = hSql.Reader.GetOrdinal("Info");
                    if (!hSql.Reader.IsDBNull(colId))
                    {
                        item.Info = hSql.Reader.GetString(colId);
                    }
                    Result.Add(item);
                }
            }
            catch (Exception ex)
            {
                _log.Error("ERROR getRemark " + ContractOID + ": ", ex);
                throw ex;
            }
            finally
            {
                hSql.Close();
            }
            return(Result);
        }