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);
}
