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