public void ReplaceDataSource(string p)
{
string pOrg = p;
string bMarkAsDone = "";
string bMultiRow = "N";
string iColVal = "";
DataSourceBase DataSource = null;
string DSName = p.Substring(9, p.IndexOf(" DST=") - 9);
foreach (DataSourceBase ds in DSList)
{
if (ds.Name == DSName)
{
DataSource = ds;
}
}
p = p.Substring(p.IndexOf(" DST=")).Trim();
if (DataSource == null)
{
mValueCalculated = mValueCalculated.Replace(p, string.Format("ERROR: The Data Source Variable '{0}' was not found", DataSource));
return;
}
if (DataSource.DSType == DataSourceBase.eDSType.MSAccess)
{
if (DataSource.FileFullPath.StartsWith("~"))
{
DataSource.FileFullPath = DataSource.FileFullPath.Replace(@"~\", "").Replace("~", "");
DataSource.FileFullPath = Path.Combine(WorkSpace.Instance.SolutionRepository.SolutionFolder, DataSource.FileFullPath);
}
DataSource.Init(DataSource.FileFullPath);
}
string Query = "";
string updateQuery = "";
List <string> mColList = null;
string rowNum = "0";
string DSTable = "";
string sAct = "";
string IRow = "";
string ExcelPath = "";
string ExcelSheet = "";
try
{
DSTable = p.Substring(p.IndexOf("DST=") + 4, p.IndexOf(" ") - 4);
mColList = DataSource.DSC.GetColumnList(DSTable);
p = p.Substring(p.TrimStart().IndexOf(" ")).Trim();
if (p.IndexOf("ACT=") != -1)
{
sAct = p.Substring(p.IndexOf("ACT=") + 4, p.IndexOf(" ") - 4);
if (sAct == "DA") // Delete All Rows
{
updateQuery = "Delete From " + DSTable;
p = "";
}
else if (sAct == "YA") // Mark All Used
{
updateQuery = "Update " + DSTable + " SET GINGER_USED='True'";
p = "";
}
else if (sAct == "NA") // Mark All UnUsed
{
updateQuery = "Update " + DSTable + " SET GINGER_USED='False'";
p = "";
}
else if (sAct == "RC") // Get Row Count
{
Query = "Select COUNT(*) FROM " + DSTable;
p = "";
}
else if (sAct == "ARC") // Get Available Row Count
{
Query = "Select COUNT(*) FROM " + DSTable + " WHERE GINGER_USED <> 'True' or GINGER_USED is null";
p = "";
}
else if (sAct == "ETE") // Get Row Count
{
Query = "";
p = p.Substring(p.TrimStart().IndexOf(" ")).Trim();
}
else
{
p = p.Substring(p.TrimStart().IndexOf(" ")).Trim();
}
}
if (p.IndexOf("EP=") != -1)
{
ExcelPath = p.Substring(p.IndexOf("EP=") + 3, p.IndexOf(" ") - 3);
p = p.Substring(p.TrimStart().IndexOf(" ")).Trim();
ExcelSheet = p.Substring(p.IndexOf("ES=") + 3, p.IndexOf("}") - 3);
}
else if (p.IndexOf("KEY=") != -1)
{
string KeyName = p.Substring(p.IndexOf("KEY=") + 4, p.IndexOf("}") - 4);
if (sAct == "DR")
{
updateQuery = "DELETE FROM " + DSTable + " WHERE GINGER_KEY_NAME = '" + KeyName + "'";
}
else
{
if (bUpdate == true)
{
DataTable dtTemp = DataSource.DSC.GetQueryOutput("Select count(*) from " + DSTable + " where GINGER_KEY_NAME= '" + KeyName + "'");
if (dtTemp.Rows[0].ItemArray[0].ToString() != "0")
{
updateQuery = "UPDATE " + DSTable + " SET GINGER_KEY_VALUE = '" + updateValue.Replace("'", "''") + "',GINGER_LAST_UPDATED_BY='" + System.Environment.UserName + "',GINGER_LAST_UPDATE_DATETIME='" + DateTime.Now.ToString() + "' WHERE GINGER_KEY_NAME = '" + KeyName + "'";
}
else
{
updateQuery = "INSERT INTO " + DSTable + "(GINGER_KEY_NAME,GINGER_KEY_VALUE,GINGER_LAST_UPDATED_BY,GINGER_LAST_UPDATE_DATETIME) VALUES ('" + KeyName + "','" + updateValue.Replace("'", "''") + "','" + System.Environment.UserName + "','" + DateTime.Now.ToString() + "')";
}
}
else
{
Query = "Select GINGER_KEY_VALUE FROM " + DSTable + " WHERE GINGER_KEY_NAME = '" + KeyName + "'";
}
}
}
else if (p != "" && (sAct == "MASD" || sAct == "DR" || sAct == ""))
{
bMarkAsDone = p.Substring(p.IndexOf("MASD=") + 5, p.IndexOf(" ") - 5);
p = p.Substring(p.TrimStart().IndexOf(" ")).Trim();
if (p.IndexOf("MR=") == 0)
{
bMultiRow = p.Substring(p.IndexOf("MR=") + 3, p.IndexOf(" ") - 3);
p = p.Substring(p.TrimStart().IndexOf(" ")).Trim();
}
string DSIden = p.Substring(p.IndexOf("IDEN=") + 5, p.IndexOf(" ") - 5);
p = p.Substring(p.TrimStart().IndexOf(" ")).Trim();
if (DSIden == "Query")
{
Query = p.Substring(p.IndexOf("QUERY=") + 6, p.Length - 7);
if (Query.ToUpper().IndexOf("SELECT *") == -1)
{
Query = Regex.Replace(Query, " FROM ", ",[GINGER_ID] FROM ", RegexOptions.IgnoreCase);
}
}
else
{
Query = "Select ";
iColVal = p.Substring(p.IndexOf("ICOLVAL=") + 8, p.IndexOf("IROW=") - 9);
iColVal = "[" + iColVal + "]";
p = p.Substring(p.TrimStart().IndexOf("IROW="));
Query = Query + iColVal + ",[GINGER_ID] from " + DSTable;
if (p.IndexOf(" ") > 0)
{
IRow = p.Substring(p.IndexOf("IROW=") + 5, p.IndexOf(" ") - 5);
}
else
{
IRow = p.Substring(p.IndexOf("IROW=") + 5, p.IndexOf("}") - 5);
}
if (IRow == "NxtAvail")
{
Query = Query + " Where GINGER_USED <> 'True' or GINGER_USED is null";
}
else if (IRow == "RowNum")
{
p = p.Substring(p.TrimStart().IndexOf("ROWNUM="));
rowNum = p.Substring(p.IndexOf("ROWNUM=") + 7, p.IndexOf("}") - 7);
}
else if (IRow == "Where")
{
if (p.TrimStart().IndexOf("COND=") != -1)
{
p = p.Substring(p.TrimStart().IndexOf("COND="));
string Cond = p.Substring(p.IndexOf("COND=") + 5, p.IndexOf("}") - 5);
Query = Query + " Where " + Cond;
}
else if (p.TrimStart().IndexOf("WCOLVAL=") != -1 && p.TrimStart().IndexOf("WOPR=") != -1)
{
p = p.Substring(p.TrimStart().IndexOf("WCOLVAL="));
string wColVal = p.Substring(p.IndexOf("WCOLVAL=") + 8, p.IndexOf("WOPR=") - 9);
wColVal = "[" + wColVal + "]";
Query = Query + " Where ";
p = p.Substring(p.TrimStart().IndexOf("WOPR="));
string wOpr = "";
string wRowVal = "";
if (p.IndexOf("WROWVAL=") == -1)
{
wOpr = p.Substring(p.IndexOf("WOPR=") + 5, p.IndexOf("}") - 5);
}
else
{
wOpr = p.Substring(p.IndexOf("WOPR=") + 5, p.IndexOf("WROWVAL=") - 6);
}
if (wOpr != "Is Null" && wOpr != "Is Null")
{
p = p.Substring(p.TrimStart().IndexOf("WROWVAL="));
wRowVal = p.Substring(p.IndexOf("WROWVAL=") + 8, p.IndexOf("}") - 8);
}
if (wOpr == "Equals")
{
if (wColVal == "[GINGER_ID]")
{
Query = Query + wColVal + " = " + wRowVal + "";
}
else
{
Query = Query + wColVal + " = '" + wRowVal + "'";
}
}
else if (wOpr == "NotEquals")
{
if (wColVal == "[GINGER_ID]")
{
Query = Query + wColVal + " <> " + wRowVal + "";
}
else
{
Query = Query + wColVal + " <> '" + wRowVal + "'";
}
}
else if (wOpr == "Contains")
{
Query = Query + wColVal + " LIKE " + "'%" + wRowVal + "%'";
}
else if (wOpr == "Not Contains")
{
Query = Query + wColVal + " NOT LIKE " + "'%" + wRowVal + "%'";
}
else if (wOpr == "Starts With")
{
Query = Query + wColVal + " LIKE '" + wRowVal + "%'";
}
else if (wOpr == "Not Starts With")
{
Query = Query + wColVal + " NOT LIKE '" + wRowVal + "%'";
}
else if (wOpr == "Ends With")
{
Query = Query + wColVal + " LIKE '%" + wRowVal + "'";
}
else if (wOpr == "Not Ends With")
{
Query = Query + wColVal + " NOT LIKE '%" + wRowVal + "'";
}
else if (wOpr == "Is Null")
{
Query = Query + wColVal + " IS NULL";
}
else if (wOpr == "Is Not Null")
{
Query = Query + wColVal + " IS NOT NULL";
}
}
else
{
return;
}
}
}
}
}
catch (Exception e) {
mValueCalculated = pOrg;
Console.WriteLine(e.StackTrace);
}
if (Query != "")
{
DataTable dt = DataSource.DSC.GetQueryOutput(Query);
if (dt.Rows.Count == 0 && IRow == "NxtAvail" && bUpdate == true)
{
DataSource.DSC.RunQuery("INSERT INTO " + DSTable + "(GINGER_USED) VALUES ('False')");
dt = DataSource.DSC.GetQueryOutput(Query);
}
if (dt.Rows.Count == 0)
{
mValueCalculated = "No Row found with " + Query;
return;
}
if (dt.Rows.Count > 0 && dt.Columns.Count > 0)
{
if (rowNum.All(char.IsDigit))
{
mValueCalculated = mValueCalculated.Replace(pOrg, dt.Rows[Convert.ToInt32(rowNum)].ItemArray[0].ToString());
}
else
{
mValueCalculated = "ERROR: Not Valid RowNum:" + rowNum;
}
}
string GingerIds = "";
if (dt.Columns.Contains("GINGER_ID"))
{
if (bMultiRow == "Y")
{
foreach (DataRow row in dt.Rows)
{
GingerIds += row["GINGER_ID"].ToString() + ",";
}
GingerIds = GingerIds.Substring(0, GingerIds.Length - 1);
}
else
{
GingerIds = dt.Rows[Convert.ToInt32(rowNum)]["GINGER_ID"].ToString();
}
}
if (bUpdate == true)
{
if (updateQuery == "")
{
if (iColVal == "")
{
iColVal = dt.Columns[0].ColumnName;
}
if (updateValue == null)
{
updateValue = string.Empty;
}
updateQuery = "UPDATE " + DSTable + " SET ";
foreach (DataColumn sCol in dt.Columns)
{
if (!new List <string> {
"GINGER_ID", "GINGER_LAST_UPDATED_BY", "GINGER_LAST_UPDATE_DATETIME", "GINGER_KEY_NAME"
}.Contains(sCol.ColumnName))
{
updateQuery += "[" + sCol.ColumnName + "]='" + updateValue.Replace("'", "''") + "' ,";
}
}
updateQuery = updateQuery.Substring(0, updateQuery.Length - 1);
if (mColList.Contains("GINGER_LAST_UPDATED_BY"))
{
updateQuery = updateQuery + ",GINGER_LAST_UPDATED_BY='" + System.Environment.UserName + "' ";
}
if (mColList.Contains("GINGER_LAST_UPDATE_DATETIME"))
{
updateQuery = updateQuery + ",GINGER_LAST_UPDATE_DATETIME = '" + DateTime.Now.ToString() + "' ";
}
updateQuery = updateQuery + "WHERE GINGER_ID IN (" + GingerIds + ")";
}
DataSource.DSC.RunQuery(updateQuery);
}
if (bMarkAsDone == "Y" && bDone == true)
{
DataSource.DSC.RunQuery("UPDATE " + DSTable + " SET GINGER_USED ='True' WHERE GINGER_ID IN (" + GingerIds + ")");
}
else if (sAct == "DR" && bDone == true)
{
DataSource.DSC.RunQuery("DELETE FROM " + DSTable + " WHERE GINGER_ID IN (" + GingerIds + ")");
}
}
else if (updateQuery != "" && bDone == true)
{
DataSource.DSC.RunQuery(updateQuery);
mValueCalculated = "";
}
else if (sAct == "ETE" && bDone == true)
{
if (ExcelSheet == "")
{
ExcelSheet = DSTable;
}
if (ExcelPath.ToLower().EndsWith(".xlsx"))
{
DataSource.DSC.ExporttoExcel(DSTable, ExcelPath, ExcelSheet);
mValueCalculated = "";
}
else
{
mValueCalculated = "The Export Excel can be *.xlsx only";
}
}
DataSource.Close();
}
