public string UpdateECategories(string companyCode, string json)
{
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
ECategories EC = new ECategories();
var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
string response = "";
/* json = @"{
* ""cat_id"":""12122222"",
* ""cat_code"":""Testing181212"",
* ""cat_descp"":""test"",
* ""cat_otherlanguage"":"""",
* ""cat_createdate"":""2019-11-07"",
* }";
*
* passing value :
* {"eCategory":[{"cat_id":"1","cat_code":"Bird’s Nest & Snow Jelly","cat_descp":"Bird’s Nest & Snow Jelly","cat_otherlanguage":"","cat_createdate":"2019-11-13"},
* {"cat_id":"2","cat_code":"Dried Bird’s Nest","cat_descp":"Dried Bird’s Nest","cat_otherlanguage":"","cat_createdate":"2019-11-13"}]}
*/
try
{
EC = serializer.Deserialize <ECategories>(json);
}
catch (Exception ex)
{
return(ex.ToString());
}
response = dataAccessor.updateECateoriesData(EC);
return(response);
}
public int UpdateVoucher(string companyCode, string serialno, string status, string value)
{
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
List <string[]> terminalList = getAllTerminals(dataAccessor, "0");
string criteria = " WHERE SerialNo='" + serialno + "'";
string field = "";
status = status.ToLower();
if (status == "sold")
{
field = "bitSold";
}
else if (status == "redeem")
{
field = "bitRedeem";
}
int result = 0;
if (field != "")
{
string strSql = "UPDATE inventory_voucher SET " + field + "='" + value + "' " + criteria;
result = dataAccessor.Exec_UpdateQuery(strSql, "");
SendMessages(dataAccessor, companyCode, "inventory_voucher", criteria, terminalList);
}
return(result);
}
public string getMember(string companyCode, string MemberID, string find1, string find2)
{
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
string fieldCriteria = "";
string fieldCriteria1 = "";
string fieldCriteria2 = "";
if (MemberID == "" && find1 == "" && find2 == "")
{
return("Please provide search criteria.");
}
else
{
if (MemberID != null && MemberID != "")
{
fieldCriteria = " (ID='" + MemberID + "') ";
}
if (find1 != null && find1 != "")
{
if (MemberID == null || MemberID == "")
{
fieldCriteria1 = " (CustICNO LIKE '%" + find1 + "%' OR email LIKE '%" + find1 + "%' OR hph LIKE '%" + find1 + "%' OR cardnumber LIKE '%" + find1 + "%' OR customerFirstName LIKE '%" + find1 + "%' OR customerLastName LIKE '%" + find1 + "%')";
}
else
{
fieldCriteria1 = " AND (CustICNO LIKE '%" + find1 + "%' OR email LIKE '%" + find1 + "%' OR hph LIKE '%" + find1 + "%' OR cardnumber LIKE '%" + find1 + "%' OR customerFirstName LIKE '%" + find1 + "%' OR customerLastName LIKE '%" + find1 + "%')";
}
}
if (find2 != null && find2 != "")
{
if (find1 == null || find1 == "")
{
fieldCriteria2 = " (CustICNO LIKE '%" + find2 + "%' OR email LIKE '%" + find2 + "%' OR hph LIKE '%" + find2 + "%' OR cardnumber LIKE '%" + find2 + "%' OR customerFirstName LIKE '%" + find2 + "%' OR customerLastName LIKE '%" + find2 + "%')";
}
else
{
fieldCriteria2 = " AND (CustICNO LIKE '%" + find2 + "%' OR email LIKE '%" + find2 + "%' OR hph LIKE '%" + find2 + "%' OR cardnumber LIKE '%" + find2 + "%' OR customerFirstName LIKE '%" + find2 + "%' OR customerLastName LIKE '%" + find2 + "%')";
}
}
}
string strSql = "SELECT ID AS MemberID,custcode, CustICNO, Email, hph as MobileNo, cardnumber, CustomerDOB AS DOB, customerFirstName as FirstName, customerLastName AS LastName, " +
"CustomerAddress1 AS Address1, CustomerAddress2 AS Address2, CustomerAddress3 AS Address3, CustomerPostcode AS PostalCode, " +
"IF(CustomerCountryID='','',(SELECT FULL FROM list_countries WHERE ID=customer.CustomerCountryID)) AS Country," +
"IF(CustomerSexID='','',(SELECT Nick FROM list_sexes WHERE ID= CustomerSexID)) AS Gender,OpeningLP " +
" FROM customer" +
" WHERE RecordStatus <> 'DELETED' AND " + fieldCriteria + fieldCriteria1 + fieldCriteria2 +
" Order By custcode,CustICNO,Email";
DataSet memberDS = dataAccessor.RunSPRetDataset(strSql, "Member");
string json = JsonConvert.SerializeObject(memberDS, Formatting.Indented);
return(json);
}
public bool SendMessages(clsDataAccessor dataAccessor, string companyname, string tableName, string strCriteria, List <string[]> terminalList)
{
try
{
ArrayList arr_mess = GenerateMessages(dataAccessor, tableName, strCriteria);
if (arr_mess.Count > 0)
{
List <MessageQueue> myQueue = new List <MessageQueue>();
if (terminalList.Count > 0)
{
string queuename, retailer, terminal;
string[] retailerTerminal;
int terminalListCount = terminalList.Count;
for (int i = 0; i < terminalListCount; i++)
{
retailerTerminal = terminalList[i].ToArray();
if (retailerTerminal.Length > 0)
{
retailer = retailerTerminal[0].ToString();
terminal = retailerTerminal[1].ToString();
queuename = companyname + "_" + retailer.PadLeft(3, '0') + "_" + terminal.PadLeft(2, '0') + "_OUT";
myQueue.Add(openMessageQueue(queuename));
}
}
}
System.Messaging.Message myMessage = new System.Messaging.Message();
int arr_messCount = arr_mess.Count;
for (int i = 0; i < arr_messCount; i++)
{
string mess = arr_mess[i].ToString();
if (mess.Length > 0)
{
myMessage.Label = "Generate_Server_API";
myMessage.Body = mess;
myMessage.Formatter = new XmlMessageFormatter(new Type[] { typeof(string) });
int myQueueCount = myQueue.Count;
for (int m = 0; m < myQueueCount; m++)
{
myQueue[m].Send(myMessage);
}
}
}
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
return(false);
}
}
public string DeleteECategories(string companyCode, string cateid)
{
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
string response = dataAccessor.deleteECateoriesData(cateid);
return(response);
}
public string getMemberPoint(string companyCode, string MemberID, string hph, string nric)
{
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
string fieldCriteria = "";
if (MemberID != "" && MemberID != "0")
{
fieldCriteria = " WHERE ID ='" + MemberID + "' OR VendorMemberID = '" + MemberID + "'";
}
if (hph != "" && hph != "0")
{
if (fieldCriteria != "")
{
fieldCriteria += " AND hph = '" + hph + "'";
}
else
{
fieldCriteria = " WHERE hph = '" + hph + "'";
}
}
if (nric != "" && nric != "0")
{
if (fieldCriteria != "")
{
fieldCriteria += " AND RIGHT(custicno ,4) ='" + nric + "'";
}
else
{
fieldCriteria = " WHERE RIGHT(custicno ,4) ='" + nric + "'";
}
}
string strSql = "SELECT hph,TotalLP AS BalancePoint,TotalEP AS EarnPoint,TotalRP AS RedeemPoint,TotalAP AS AdjustPoint,(SELECT getExpiringPoint(customer.ID)) AS ExpiryPoint," +
"IF(ExpiryPointDate = 0000-00-00," +
"(SELECT DATE_FORMAT(LAST_DAY(CONCAT(SUBSTR(CURDATE(),1,4)+1,LPAD(PointCutOffMonth,2,'0'),'01')),'%d-%m') AS expiryDate FROM customer_definitions),ExpiryPointDate) AS ExpiringDate, " +
" ID AS MemberID FROM customer " + fieldCriteria;
/* string strSql = "SELECT SPV01 AS MemberID,hph AS MobileNo,TotalLP AS BalancePoint,0 AS RedeemPoint,0 AS AdjustPoint,0 AS ExpiryPoint," +
* "(SELECT DATE_FORMAT(LAST_DAY(CONCAT(SUBSTR(CURDATE(),1,4)+1,LPAD(PointCutOffMonth,2,'0'),'01')),'%d-%m') AS expiryDate FROM customer_definitions) AS ExpiringDate " +
* " FROM customer " + fieldCriteria; */
DataSet memLPDS = dataAccessor.RunSPRetDataset(strSql, "Member");
string json = JsonConvert.SerializeObject(memLPDS, Formatting.Indented);
//List<string[]> asd = inventoryDS;
//return asd;
return(json);
}
public int CalculateInventoryOnhandQty(string companyCode, string RetailID, string field, string fieldID)
{
int result = 0;
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
string retailCriteria = "";
if (RetailID != "0")
{
retailCriteria = " AND IR.RetailID=" + RetailID;
}
string strSql = "";
string itemCriteria = "";
string ItemID;
if (fieldID != "0")
{
strSql = "SELECT ItemID FROM inventory WHERE " + field + "=" + fieldID;
ItemID = dataAccessor.ExecScalarVal(strSql, "").ToString();
itemCriteria = " AND IR.ItemID=" + ItemID;
}
/* strSql = "SELECT IR.ItemID, IR.RetailID, SUM(Qty) AS OnHandQty FROM inventory_retail IR LEFT JOIN inventory_tran IT" +
* " ON IR.ItemID=IT.ItemID AND IR.RetailID=IT.RetailID" +
* " WHERE 1=1 " + itemCriteria + retailCriteria +
* " GROUP BY IR.ItemID, IR.RetailID";*/
strSql = "SELECT t.*,IF(t.ItemActQty IS NULL,t.OnHandQty,FLOOR(t.OnHandQty/t.ItemActQty)) AS OnHandDefQty FROM (" +
"SELECT IR.ItemID, IR.RetailID, getItemOnHandQtyPerRetailer(IR.ItemID,IR.RetailID, NOW()) AS OnHandQty," +
"(SELECT ItemActQty FROM inventory_unit WHERE ItemID = IR.ItemID AND ItemUnitDef='Y' AND RecordStatus<>'DELETED') AS ItemActQty " +
" FROM inventory_retail IR WHERE 1=1 " + itemCriteria + retailCriteria + " GROUP BY IR.ItemID, IR.RetailID) AS t";
DataSet inventoryDS = dataAccessor.RunSPRetDataset(strSql, "inventory_retail");
string criteria;
double OnHandQty = 0.00;
double onHandDefQty = 0.00;
foreach (DataRow dsTableRow in inventoryDS.Tables[0].Rows)
{
ItemID = dsTableRow["ItemID"].ToString();
RetailID = dsTableRow["RetailID"].ToString();
OnHandQty = double.Parse(dsTableRow["OnHandQty"].ToString());
onHandDefQty = double.Parse(dsTableRow["OnHandDefQty"].ToString());
criteria = " WHERE ItemID=" + ItemID + " AND RetailID=" + RetailID;
strSql = "UPDATE inventory_retail SET OnHandQty=" + OnHandQty + ",OnHandDefQty=" + onHandDefQty + " " + criteria;
result = dataAccessor.Exec_UpdateQuery(strSql, "");
//SendMessages(dataAccessor, companyCode, "inventory_retail", criteria, terminalList);
}
return(result);
}
public ArrayList GenerateMessages(clsDataAccessor dataAccessor, string tableName, string strCriteria)
{
ArrayList arr_mess = new ArrayList();
try
{
bool gotRecord = true;
int page = 1;
int limit = 5;
while (gotRecord)
{
string sql = "";
if (strCriteria == "")
{
sql = "SELECT * FROM " + tableName + " WHERE QUEUESTATUS='PENDING' LIMIT " + ((page - 1) * limit) + ", " + limit;
}
else
{
sql = "SELECT * FROM " + tableName + " " + strCriteria;
}
DataSet ds_salesdata = new DataSet();
ds_salesdata = dataAccessor.RunSPRetDataset(sql, tableName);
if (ds_salesdata != null && ds_salesdata.Tables != null && ds_salesdata.Tables[0] != null && ds_salesdata.Tables[0].Rows.Count > 0)
{
string mess = clsXmlTransfer.CDataToXml(ds_salesdata);
arr_mess.Add(mess);
if (strCriteria == "")
{
page++;
}
else
{
gotRecord = false;
}
}
else
{
gotRecord = false;
}
ds_salesdata.Clear();
ds_salesdata.Dispose();
}
}
catch (Exception ex)
{
return(null);
}
return(arr_mess);
}
public string ExecuteSQL(string companyCode, string sql, string dbname, string tblName)
{
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
//dataAccessor.connectionstring = "SERVER=localhost;Database=" + dbname + ";UID=root;PASSWORD=mlp098;CharSet=utf8;Convert Zero Datetime=True;";
string[] stringSeparators = new string[] { ";" };
string[] sqlArr;
string blnstatus = "Success";
int intRetVal = 0;
string strMessage = "";
sqlArr = sql.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
try
{
//dataAccessor.Exec_UpdateQuery(sql, "");
foreach (string strSql in sqlArr)
{
dataAccessor.Exec_UpdateQuery(strSql, "");
}
if (tblName.ToLower() == "stocktransfer")
{
dataAccessor.Exec_UpdateQuery("UPDATE stocktransfer_detail STD SET ID=(SELECT ID FROM stocktransfer WHERE SERVERID=STD.SERVERID) WHERE ID=0;", "");
}
}
catch (Exception e)
{
blnstatus = "Fail";
}
/*finally
* {
* if(tblName.ToLower() =="stocktransfer")
* dataAccessor.Exec_UpdateQuery("UPDATE stocktransfer_detail STD SET ID=(SELECT ID FROM stocktransfer WHERE SERVERID=STD.SERVERID) WHERE ID=0;", "");
*
* blnstatus="Success";
* /*if(intRetVal==1)
* blnstatus="Success";
* else
* blnstatus="Fail";
*/
//}
//blnstatus
return(blnstatus);
}
public int UpdateItemCollection(string companyCode, string TransNo, string ID, string status)
{
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
List <string[]> terminalList = getAllTerminals(dataAccessor, "0");
string criteria = " WHERE TransNo='" + TransNo + "'";
int result = 0;
string strSql = "UPDATE tblitemcollection SET ItemStatus='" + status + "' " + criteria;
result = dataAccessor.Exec_UpdateQuery(strSql, "");
SendMessages(dataAccessor, companyCode, "tblitemcollection", criteria, terminalList);
return(result);
}
public string submitOnlineSales(string companyCode, string json)
{
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
SalesMaster sales = new SalesMaster();
var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
string response = "";
/* json = @"{""TransNo"":""A1234512"",
* ""RetailID"":""ONLINE"",
* ""SalesDate"":""2020/02/24 15:20:43"",
* ""SalesStatus"":""SALES"",
* ""MemberID"":""CUST917800001944774"",
* ""SalesTaxType"":""I"",
* ""SalesTaxRate"":""7"",
* ""SalesTaxTtl"":""49"",
* ""SalesRounding"":""0"",
* ""SalesTotalAmount"":""700"",
* ""ItemSales"":[{""ItemID"":""BSBN104"",""SupBarCode"":"""",""ItemSellPrice"":""350"",""ItemQty"":""2"",
* ""ItemUOMDesc"":""Ctn (75gx72)"",""ItemDisc"":""0"",""ItemDisc2"":""0"",""ItemDisc3"":""0"",
* ""ItemPrice"":""384.000"",""ItemTax"":""24.50"",""ItemTotal"":""700"",
* ""ItemVoucher"":[{""VoucherNo"":""VC123"",""VoucherAmount"":""700""
* }]
* }],
* ""SalesPayments"":[{""paymentID"":"""",""strPayment"":""VISA"",""SalesPayTtl"":""700"",
* ""SalesBalTtl"":""700"",""ChangeAmount"":""0"",""TipsAmount"":""0"",
* ""SaleVoucher"":[{""VoucherNo"":""VC122"",""VoucherAmount"":""700""
* }]
* }],
* ""SalesPersons"":[]
* }"; */
try
{
sales = serializer.Deserialize <SalesMaster>(json);
}
catch (Exception ex)
{
return(ex.ToString());
}
response = dataAccessor.saveOnlineSales(sales);
return(response);
}
public string UpdateEMember(string companyCode, string json)
{
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
MemberInfor member = new MemberInfor();
var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
string response = "";
/*string jSon = @"{
* "MemberID":"12122222",
* "MemberName":"Testing181212",
* "Gender":"F",
* "DOB":"1988-12-12",
* "CreateDate":"2019-11-07",
* "NRIC":"890S",
* "Email":"*****@*****.**",
* "HPH":"91838364",
* "Address1":"blk 63",
* "Address2":"kallang bahru",
* "Address3":"",
* "PostalCode":"330063",
* "Country":"SGD",
* "VendorMemberID":"",
* }";
*
* passing value :
* {"MemberID":"12122222","MemberName":"Testing181212","Gender":"F",
* "DOB":"2078-12-12","CreateDate":"2019-11-13","NRIC":"","Email":"","HPH":"","Address1":"","Address2":"","Address3":"",
* "PostalCode":"","Country":"","VendorMemberID":""}
*/
try
{
member = serializer.Deserialize <MemberInfor>(json);
}
catch (Exception ex)
{
return(ex.ToString());
}
response = dataAccessor.updateMemberData(member);
return(response);
}
public string Get_CustType(clsDataAccessor objDA, MySqlTransaction objtran)
{
string custypeid = "";
DataSet ds_cust = new DataSet();
string sqlstr = "SELECT CUSTTYPEID FROM CUSTOMER_TYPE WHERE BLNDEFAULT = 'Y' AND bitFrontendLock='N' and RecordStatus<>'DELETED' and Display='Y'";
try
{
custypeid = objDA.ExecuteScalar(objtran, CommandType.Text, sqlstr, null).ToString();
}
catch (System.Exception ex)
{
custypeid = "";
}
return(custypeid);
}
public string Get_Auto_Custcode(clsDataAccessor objDA, MySqlTransaction objtran)
{
string cust_auto_num = "";
string custautono = "";
DataSet ds_cust = new DataSet();
string sql = "SELECT VCHPREFIXCUST, CUSTSTARTNO,CUSTNEXTNO, BITCUSTCODEEQUALICNO FROM CUSTOMER_DEFINITIONS WHERE DISPLAY = 'Y'";
ds_cust = objDA.ExecuteDataSet(objtran, CommandType.Text, sql, null);
if (ds_cust.Tables[0].Rows.Count > 0)
{
int runNo = int.Parse(ds_cust.Tables[0].Rows[0]["CUSTSTARTNO"].ToString()) + int.Parse(ds_cust.Tables[0].Rows[0]["CUSTNEXTNO"].ToString()) + 1;
custautono = ds_cust.Tables[0].Rows[0]["VCHPREFIXCUST"].ToString() + (runNo).ToString();
}
ds_cust.Clear();
ds_cust.Dispose();
return(cust_auto_num);
}
public int CalculateCustomerPoint(string companyCode, string field, string fieldID)
{
int result = 0;
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
string fieldCriteria = "";
if (fieldID != "0")
{
fieldCriteria = " AND " + field + "='" + fieldID + "'";
}
/* string strSql = "SELECT CustID, SUM(TotalAmount) AS spendAmt,SUM(AdjustPoint)+SUM(LoyaltyPoint) AS LP,SUM(RedeemPoint) AS RP" +
* " FROM customer_salesdetails WHERE 1=1 " + fieldCriteria + " GROUP BY CustID";*/
string strSql = "SELECT t.*,((SELECT OPENINGLP FROM CUSTOMER WHERE SERVERID=t.CustID) + LP - RP) AS BalPoint FROM (" +
"SELECT CustID, SUM(TotalAmount) AS spendAmt,SUM(AdjustPoint)+SUM(LoyaltyPoint) AS LP,SUM(RedeemPoint) AS RP " +
"FROM customer_salesdetails WHERE 1=1 " + fieldCriteria + " GROUP BY CustID ) AS t";
DataSet customerDS = dataAccessor.RunSPRetDataset(strSql, "customer_salesdetails");
string CustID, LP, RP, criteria;
double balPoint = 0.00;
double spendAmt = 0.00;
foreach (DataRow dsTableRow in customerDS.Tables[0].Rows)
{
CustID = dsTableRow["CustID"].ToString();
spendAmt = double.Parse(dsTableRow["spendAmt"].ToString());
LP = dsTableRow["LP"].ToString();
RP = dsTableRow["RP"].ToString();
//balPoint = double.Parse(dsTableRow["BalPoint"].ToString());
balPoint = Convert.ToDouble(dsTableRow["BalPoint"]);
criteria = " WHERE SERVERID='" + CustID + "'";
strSql = "UPDATE customer SET TotalLP=" + balPoint + ",TotalYTDSales= " + spendAmt + " " + criteria;
result = dataAccessor.Exec_UpdateQuery(strSql, "");
//SendMessages(dataAccessor, companyCode, "customer", criteria, terminalList);
}
return(result);
}
public int CalculateItemOnHandQty(string companyCode, string TransID, string TransNo, string RetailID)
{
clsDataAccessor dataAccessor = new clsDataAccessor();
dataAccessor.connectionstring = dataAccessor.getConnectionString_Vapt(dataAccessor, companyCode);
MySqlParameter[] objParam =
{
dataAccessor.InputParam("pID", MySqlDbType.Int32, 25, TransID.ToString()),
dataAccessor.InputParam("pTransNo", MySqlDbType.VarChar, 30, TransNo.ToString()),
dataAccessor.InputParam("pRetailID", MySqlDbType.Int32, 25, RetailID.ToString())
};
int intresult = 0;
try
{
intresult = dataAccessor.Exec_SPNonReturn("RecalculateOnHandQtyBySales", objParam);
return(intresult);
}
catch (MySqlException ex)
{
string errMessage = ("ERROR SOURCE: "
+ (ex.Source + "<br/>"));
errMessage = (errMessage + (" ERROR MESSAGE: "
+ (ex.Message + "<br/>")));
errMessage = (errMessage + (" ERROR CODE: "
+ (ex.ErrorCode.ToString() + "<br/>")));
errMessage = (errMessage + (" ERROR CODE: "
+ (ex.StackTrace.ToString() + "<br/>")));
return(0);
}
finally
{
dataAccessor = null;
}
return(intresult);
}
public List <string[]> getAllTerminals(clsDataAccessor dataAccessor, string retailID)
{
List <string[]> arr_terminals = new List <string[]>();
string sqlCriteria = "";
if (retailID != "0")
{
sqlCriteria = " AND RetailerID='" + retailID + "'";
}
string sql = "SELECT RetailerID, TerminalID FROM definitions_terminal WHERE `Display`='Y' AND RecordStatus<>'DELETED'";
DataSet ds_Databaseinfor = dataAccessor.RunSPRetDataset(sql + sqlCriteria, "definitions_terminal");
string[] result = new string[2];
foreach (DataRow dsTableRow in ds_Databaseinfor.Tables[0].Rows)
{
result = new string[2];
result[0] = dsTableRow["RetailerID"].ToString();
result[1] = dsTableRow["TerminalID"].ToString();
arr_terminals.Add(result);
}
return(arr_terminals);
}
public string getTransCount(clsDataAccessor objDA, MySqlTransaction objtran, string StoreNo, string TerminalNo)
{
string sql_queuenodigit = "SELECT FUNCTIONVALUE FROM DEFINITIONS_GENERALSETTING WHERE FUNCTIONFIELDNAME = 'NOOFDIGITQUEUENO' AND RETAILERID=@RetailerID";
int queuedigit = 4;
MySqlParameter[] objparam =
{
new MySqlParameter("@RetailerID", StoreNo),
new MySqlParameter("@TerminalID", TerminalNo)
};
try
{
queuedigit = int.Parse(objDA.ExecuteScalar(objtran, CommandType.Text, sql_queuenodigit, objparam).ToString());
}
catch (Exception e)
{
queuedigit = 4;
}
string storenostr = int.Parse(StoreNo).ToString("00");
int seqlength = queuedigit - 2;
string tostringzerostr = "";
string maxnum = "1";
for (int i = 0; i < seqlength; i++)
{
tostringzerostr += "0";
}
maxnum += tostringzerostr;
string sqlstr = "SELECT LastQueueNo FROM DEFINITIONS_TERMINAL WHERE RETAILERID=@RetailerID AND TerminalID=@TerminalID AND DISPLAY = 'Y' ";
int lastno = 0;
try
{
lastno = int.Parse(objDA.ExecuteScalar(objtran, CommandType.Text, sqlstr, objparam).ToString());
}
catch (System.Exception ex)
{
lastno = 0;
}
lastno += 1;
if (lastno == int.Parse(maxnum))
{
string sql_forqueueno = "UPDATE DEFINITIONS_TERMINAL SET LastQueueNo = '0' WHERE DISPLAY = 'Y' AND RETAILERID=@RetailerID AND TERMINALID=@TerminalID";
objDA.ExecuteNonQuery(objtran, CommandType.Text, sql_forqueueno, objparam);
lastno = 1;
}
string queuestr = storenostr + lastno.ToString(tostringzerostr);
return(queuestr);
}