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