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