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