public static bool saveRemark(int ContractOID, List <SCContractRemark> lstData, clsSqlFactory hSql) { if (lstData == null) { return(true); } bool bRet = true; try { clsGlobalVariable objGlobal = new clsGlobalVariable(); foreach (SCContractRemark data in lstData) { if (data.OID > 0) { if (data.isMarkDeleted == true) { bRet = hSql.NewCommand("delete from ZSC_ContractRemark where OID=?"); hSql.Com.Parameters.AddWithValue("OID", data.OID); bRet = bRet && hSql.ExecuteNonQuery(); } else { bRet = hSql.NewCommand("update ZSC_ContractRemark set UserId=?,RemarkType=?,Info=?, Modified=getdate() where OID=?"); hSql.Com.Parameters.AddWithValue("UserId", objGlobal.DMSFirstUserName); hSql.Com.Parameters.AddWithValue("RemarkType", data.RemarkType); hSql.Com.Parameters.AddWithValue("Info", data.Info); hSql.Com.Parameters.AddWithValue("OID", data.OID); bRet = bRet && hSql.ExecuteNonQuery(); } } else { bRet = hSql.NewCommand("INSERT INTO ZSC_ContractRemark(ContractOID,Created,UserId,RemarkType,Info,Modified) VALUES(?,getdate(),?,?,?,getdate())"); hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID); hSql.Com.Parameters.AddWithValue("UserId", objGlobal.DMSFirstUserName); hSql.Com.Parameters.AddWithValue("RemarkType", data.RemarkType); hSql.Com.Parameters.AddWithValue("Info", data.Info); bRet = bRet && hSql.ExecuteNonQuery(); } } } catch (Exception ex) { _log.Error("ERROR saveRemark " + ContractOID + ": ", ex); throw ex; } return(bRet); }
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); }
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); }
private void printToWord(bool bPreview) { object strFile; Microsoft.Office.Interop.Word.Application objWord = new Microsoft.Office.Interop.Word.Application(); Microsoft.Office.Interop.Word.Document objDoc = new Microsoft.Office.Interop.Word.Document(); object missing = System.Reflection.Missing.Value; object myTrue = true; object myFalse = false; objWord.Options.MapPaperSize = false; object objBookmark = "ContractOID"; clsGlobalVariable objGlobal = new clsGlobalVariable(); TreeNode objWordNode = trDocuments.Nodes[0]; foreach (TreeNode objNode in objWordNode.Nodes) { if (objNode.Checked == true) { strFile = objNode.Text; objDoc = objWord.Documents.AddOld(ref strFile, ref missing); try { objBookmark = "ContractOID"; objDoc.FormFields.get_Item(ref objBookmark).Result = objContact.ContractOID.ToString(); string sqlserver = (string)Registry.GetValue("HKEY_LOCAL_MACHINE\\SOFTWARE\\Wow6432Node\\ODBC\\ODBC.INI\\" + objGlobal.DMSDBName, "Server", "localhost"); objBookmark = "VSPISRV"; objDoc.FormFields.get_Item(ref objBookmark).Result = sqlserver; objBookmark = "VSPIDB"; objDoc.FormFields.get_Item(ref objBookmark).Result = objGlobal.DMSDBName; objBookmark = "VSPIUSER"; objDoc.FormFields.get_Item(ref objBookmark).Result = objGlobal.DMSUserName; objBookmark = "VSPIPASS"; objDoc.FormFields.get_Item(ref objBookmark).Result = objGlobal.DMSDBPass; objWord.Run("GetExtraData"); } catch (Exception ex) { _log.Error(ex.ToString()); } if (!bPreview) { objDoc.PrintOutOld(ref myFalse, ref myFalse, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing); objDoc.Close(ref myFalse, ref missing, ref missing); } else { objDoc.Activate(); objWord.Visible = true; } } } if (!bPreview) { objWord.Quit(ref myFalse, ref missing, ref missing); objWord = null; } }
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); }
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); }
public bool saveReceipt(clsCashBox CashBox) { bool bRet = true; clsSqlFactory hSql = new clsSqlFactory(); clsGlobalVariable objGlobal = new clsGlobalVariable(); try { _log.Debug("saveReceipt >> SmanId = " + objGlobal.DefaultSManID); List <int> nVoucherIds = new List <int>(); List <int> nReceiptNos = new List <int>(); List <decimal> nReceiptTotals = new List <decimal>(); int i = 0; int nNuseId = 0; int nReceiptNo = 0; bool bNeedRounding = false; decPaymentSum = getRowsTotal(); while (i < Rows.Count) { DataRow rowPaymentType = CashBox.CashTypes.Select("TYPE = '" + Rows[i].PaymentType.Code + "'")[0]; if (rowPaymentType != null) { if (CashBox.getSpecRound(rowPaymentType["TYPE"].ToString()) > 0) { bNeedRounding = true; } nNuseId = CashBox.getVoucherIdOfType(Rows[i].PaymentType.Code, Rows[0].Amount > 0 ? false : true); if (nVoucherIds.IndexOf(nNuseId) < 0) { nVoucherIds.Add(nNuseId); nReceiptNo = getReceiptNUSE(hSql, nNuseId); nReceiptNos.Add(nReceiptNo); nReceiptTotals.Add(0); // insert header _log.Debug("receiptno: " + nReceiptNo.ToString()); hSql.NewCommand("insert into " + objUtil.getTable("CASHTRANSH") + "(RECEIPTNO,CASHBOXID,DEPARTMENT,SMANID,UNIT,MODULE," + "INVOICECATEGORY,CUSTNO,LICNO,AMOUNTTOPAY,TOTPAID,CASHRETURNED," + "TEXT,INSERTPC,UPDATED_DATE,UPDATED_BY,EXT_BILLD,EXT_ORDERNO,EXT_INVOICENO,INVOICENO,ORDERNO) " + " values(?,?,?,?,?,?," + "'1',?,?,?,?,0," + "?,?,getdate(),?,?,?,?,?,?)"); hSql.Com.Parameters.AddWithValue("RECEIPTNO", nReceiptNo); hSql.Com.Parameters.AddWithValue("CASHBOXID", Rows[i].CashBoxId); if (strDeptId != "") { _log.Debug("Department = " + strDeptId); hSql.Com.Parameters.AddWithValue("DEPARTMENT", strDeptId); } else { String strDefDeptId = objConfig.getStringParam("CASHREG", "DEFDEPTID", "C3", "").Trim(); if (strDefDeptId == "") { strDefDeptId = ConfigurationManager.AppSettings["DefDeptId"].ToString(); } hSql.Com.Parameters.AddWithValue("DEPARTMENT", strDefDeptId); _log.Debug("Nor department found from invoices, use default department " + strDeptId); } hSql.Com.Parameters.AddWithValue("SMANID", objGlobal.DefaultSManID); hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId); hSql.Com.Parameters.AddWithValue("MODULE", strInvoiceModule); hSql.Com.Parameters.AddWithValue("CUSTNO", strCustNo); hSql.Com.Parameters.AddWithValue("LICNO", strLicno); hSql.Com.Parameters.AddWithValue("AMOUNTTOPAY", decInvoiceSum); hSql.Com.Parameters.AddWithValue("TOTPAID", 0); hSql.Com.Parameters.AddWithValue("TEXT", strRemark); hSql.Com.Parameters.AddWithValue("INSERTEDPC", CashBox.getClientPCName()); hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName); hSql.Com.Parameters.AddWithValue("EXT_BILLD", dtInvoiceDate); hSql.Com.Parameters.AddWithValue("EXT_ORDNO", strOrderNo); hSql.Com.Parameters.AddWithValue("EXT_INVOICENO", strInvoiceNo); if (InvoiceFlag != InvoiceFlags.CashRegisterFlag) { hSql.Com.Parameters.AddWithValue("INVOICENO", strInvoiceNo); hSql.Com.Parameters.AddWithValue("ORDERNO", strOrderNo); } else { hSql.Com.Parameters.AddWithValue("INVOICENO", DBNull.Value); hSql.Com.Parameters.AddWithValue("ORDERNO", DBNull.Value); } hSql.ExecuteNonQuery(); } else { nReceiptNo = nReceiptNos[nVoucherIds.IndexOf(nNuseId)]; } if (nReceiptNo > 0) { hSql.NewCommand("insert into " + objUtil.getTable("CASHTRANSR") + "(RECEIPTNO,ROWNO,CASHFORM,CASHTYPE,QTY,AMOUNT,SALETYPE,FEE,CC_TERMINALID) " + " values(?,?,?,?,?,?,?,?,?)"); hSql.Com.Parameters.AddWithValue("RECEIPTNO", nReceiptNo); hSql.Com.Parameters.AddWithValue("ROWNO", i + 1); 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); hSql.Com.Parameters.AddWithValue("AMOUNT", Rows[i].Amount); hSql.Com.Parameters.AddWithValue("SALETYPE", Rows[i].SaleType); hSql.Com.Parameters.AddWithValue("FEE", Rows[i].AmountFee); hSql.Com.Parameters.AddWithValue("CC_TERMINALID", Rows[i].CardType.Code); hSql.ExecuteNonQuery(); Rows[i].Receiptno = nReceiptNo; nReceiptTotals[nReceiptNos.IndexOf(nReceiptNo)] += Rows[i].Amount; } else { _log.Error("Fail to generate receipt number"); Exception ex = new Exception("Fail to generate receipt number, check the voucher id settings !"); throw ex; } } else { _log.Error("PaymentType " + Rows[i].PaymentType.Code + " not found in the valid CashTypes !"); } i++; } i = 0; while (i < nReceiptNos.Count) { hSql.NewCommand("update " + objUtil.getTable("CASHTRANSH") + " set TOTPAID =? where RECEIPTNO=? "); hSql.Com.Parameters.AddWithValue("TOTPAID", nReceiptTotals[i]); hSql.Com.Parameters.AddWithValue("RECEIPTNO", nReceiptNos[i]); hSql.ExecuteNonQuery(); 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.Com.Parameters.AddWithValue("PAYSUM", decPaymentSum); hSql.Com.Parameters.AddWithValue("RECNO", strInvoiceNo); hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId); hSql.ExecuteNonQuery(); // if (bNeedRounding) { hSql.NewCommand("update TEMPINV set PAYDATE=getdate() where RECNO=? and PAYDATE is null and abs(PAYSUM-INVSUM) < 3 and UNITID = ?"); } else { hSql.NewCommand("update TEMPINV set PAYDATE=getdate() where RECNO=? and PAYDATE is null and abs(PAYSUM-INVSUM) = 0 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 null and b.PAYDATE is not 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 null and b.PAYDATE is not 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 null and b.PAYDATE is not 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(); } hSql.Commit(); } catch (Exception ex) { _log.Error(ex.ToString()); bRet = false; hSql.Rollback(); throw ex; } finally { hSql.Close(); } return(bRet); }
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); }