public static string GetListQueryByListName(string sqlFrom, string whereClause, string rep_code, string listName)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
string[] listQueryAndDescription = GetListQueryAndDescriptionByName(listName, rep_code).Split(new string[] { "@@@@" }, StringSplitOptions.None);
string listQuery = listQueryAndDescription[0];
string[] fields = HIT.OB.STD.CommonUtil.ExtractFieldNameFromQuery(listQuery).Split(',');
List <string> columnField = new List <string>();
DataTable dtData = iWrapFunctions.GetDataTable("SELECT * FROM " + sqlFrom);
listQuery = RemoveFieldsFromWhereClauseWhichValuesAreNotAvailable(listQuery, fields, columnField, dtData);
string sqlQuery = "select " + string.Join(",", columnField.ToArray()) + " FROM " + sqlFrom + " WHERE " + whereClause;
dtData = iWrapFunctions.GetDataTable(sqlQuery);
foreach (string field in columnField)
{
listQuery = listQuery.Replace("%" + field + "%", "'" + dtData.Rows[0][field].ToString().Trim() + "'");
}
return(listQuery);
}
public static string GetRevision(string whereClause, String sqlFrom, IWrapFunctions iWrapFunctions)
{
DataTable dt = iWrapFunctions.GetDataTable("SELECT * FROM " + sqlFrom);
StringBuilder revisions = new StringBuilder();
revisions.Append("revisions:[");
string revisionColumnName = string.Empty;
foreach (DataColumn column in dt.Columns)
{
if (column.ColumnName.Contains("rev"))
{
revisionColumnName = column.ColumnName;
}
}
if (!revisionColumnName.Equals(string.Empty))
{
whereClause = whereClause.Substring(0, whereClause.IndexOf("AND"));
DataTable dtRevisions = iWrapFunctions.GetDataTable("SELECT distinct(" + revisionColumnName + ") FROM " + sqlFrom + " WHERE " + whereClause);
int revNo = Convert.ToInt32(dtRevisions.Rows[0][0].ToString());
//int revNo = Convert.ToInt32(whereClause.Split('=')[2].ToString().Replace("'", "").Trim());
for (int i = 0; i <= revNo; i++)
{
if (i < 10)
{
revisions.AppendFormat("'0{0}'", i);
}
else
{
revisions.AppendFormat("'{0}'", i);
}
if (i < revNo)
{
revisions.Append(",");
}
}
//for (int i = 0; i < dtRevisions.Rows.Count; i++)
//{
// revisions.AppendFormat("'{0}'", GetJSONFormat(dtRevisions.Rows[i][0].ToString()));
// if (i < dtRevisions.Rows.Count - 1)
// {
// revisions.Append(",");
// }
//}
}
revisions.Append("],");
return(revisions.ToString());
}
public static string UpdateBusket(string sqlFrom)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
DataTable dtPartlistInfo = iWrapFunctions.GetDataTable(sqlFrom);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("ItemInfo:[");
//int rowNum = 1;
for (int i = 0; i < dtPartlistInfo.Rows.Count; i++)
{
DataRow currentRow = dtPartlistInfo.Rows[i];
// stringBuilder.Append("[\"" + (i + 1).ToString() + "\",");
stringBuilder.Append("[");
foreach (DataColumn column in dtPartlistInfo.Columns)
{
stringBuilder.AppendFormat("\"{0}\",", GetJSONFormat(currentRow[column.ColumnName].ToString()));
}
stringBuilder = stringBuilder.Remove(stringBuilder.ToString().LastIndexOf(','), 1);
stringBuilder.Append("]");
if (i < dtPartlistInfo.Rows.Count - 1)
{
stringBuilder.Append(",");
}
}
stringBuilder.Append("]");
return("{" + stringBuilder + "}");
}
public static string GetItemInfoByItemNo(string sqlFrom, string whereClause, string selectedFields)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
//string strSql = "Select " + string.Join(",", selectedFields.Split(';')) + " from " + sqlFrom + " where " + whereClause;
string strSql = "Select distinct " + selectedFields + " from " + sqlFrom + " where " + whereClause;
DataTable dtPartlistInfo = iWrapFunctions.GetDataTable(strSql);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("ItemInfo:[");
//int rowNum = 1;
for (int i = 0; i < dtPartlistInfo.Rows.Count; i++)
{
DataRow currentRow = dtPartlistInfo.Rows[i];
// stringBuilder.Append("[\"" + (i + 1).ToString() + "\",");
stringBuilder.Append("[");
foreach (DataColumn column in dtPartlistInfo.Columns)
{
stringBuilder.AppendFormat("\"{0}\",", GetJSONFormat(currentRow[column.ColumnName].ToString()));
}
stringBuilder = stringBuilder.Remove(stringBuilder.ToString().LastIndexOf(','), 1);
stringBuilder.Append("]");
if (i < dtPartlistInfo.Rows.Count - 1)
{
stringBuilder.Append(",");
}
}
stringBuilder.Append("]");
return("{" + stringBuilder + "}");
}
private void GetUsers()
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
string projectCode = ConfigurationSettings.AppSettings["project_code"].ToString();
string query = "select * from project_user where project_code='" + projectCode + "'";
DataTable dt = iWrapFunctions.GetDataTable(query);
lstUsers.DataTextField = "username";
lstUsers.DataValueField = "username";
lstUsers.DataSource = dt;
lstUsers.DataBind();
}
public static bool CheckForReportCodeAvailability(string rep_code)
{
bool isAvailable = true;
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
DataTable dt = iWrapFunctions.GetDataTable("select * from dfn_repdetail where upper(report_code) = '" + rep_code.ToUpper() + "'");
if (dt.Rows.Count > 0)
{
isAvailable = false;
}
return(isAvailable);
}
void GetTaskDetails(string task_id)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
string sql = "select * from tasks where task_id = '" + task_id + "'";
DataTable dt = iWrapFunctions.GetDataTable(sql);
string assingedTo = dt.Rows[0]["assign_to"].ToString();
string taskStatus = dt.Rows[0]["item_status"].ToString();
GetUsers(assingedTo);
txtSummary.Text = dt.Rows[0]["item_summary"].ToString();
txtDescription.Text = dt.Rows[0]["detailed_desc"].ToString();
lstTaskStatus.SelectedValue = taskStatus;
}
public List <string> GetUsers()
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
string projectCode = ConfigurationSettings.AppSettings["project_code"].ToString();
string query = "select * from project_user where project_code='" + projectCode + "'";
DataTable dt = iWrapFunctions.GetDataTable(query);
List <string> userList = new List <string>();
foreach (DataRow dr in dt.Rows)
{
userList.Add(dr["username"].ToString());
}
return(userList);
}
private void GetUsers(string assingedTo)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
string projectCode = ConfigurationSettings.AppSettings["project_code"].ToString();
string query = "select * from project_user where project_code='" + projectCode + "' order by username desc";
DataTable dt = iWrapFunctions.GetDataTable(query);
lstUsers.DataTextField = "username";
lstUsers.DataValueField = "username";
lstUsers.DataSource = dt;
try
{
lstUsers.DataBind();
lstUsers.SelectedItem.Text = assingedTo;
}
catch (Exception ex)
{
}
}
public static string GetListItemInfo(string sqlFrom, string whereClause, string rep_code, string listName, string selectedFields)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
string[] listQueryAndDescription = GetListQueryAndDescriptionByName(listName, rep_code).Split(new string[] { "@@@@" }, StringSplitOptions.None);
string listQuery = listQueryAndDescription[0];
string[] fields = HIT.OB.STD.CommonUtil.ExtractFieldNameFromQuery(listQuery).Split(',');
List <string> columnField = new List <string>();
DataTable dtData = iWrapFunctions.GetDataTable("SELECT * FROM " + sqlFrom);
listQuery = RemoveFieldsFromWhereClauseWhichValuesAreNotAvailable(listQuery, fields, columnField, dtData);
string[] requiredField = selectedFields.Split(';');
string currentRevision = string.Empty;
string sqlQuery = "select " + string.Join(",", columnField.ToArray()) + (selectedFields.Length > 0 ? ("," + selectedFields.Replace(';', ',')) : "") + " FROM " + sqlFrom + " WHERE " + whereClause.Substring(0, whereClause.IndexOf("AND"));
dtData = iWrapFunctions.GetDataTable(sqlQuery);
foreach (string field in columnField)
{
if (field.Contains("revision"))
{
listQuery = listQuery.Replace("%revision%", whereClause.Split('=')[2].ToString().Trim());
}
else
{
listQuery = listQuery.Replace("%" + field + "%", "'" + dtData.Rows[0][field].ToString().Trim() + "'");
}
if (field.Contains("rev"))
{
//currentRevision = dtData.Rows[0]["revision"].ToString();
currentRevision = whereClause.Split('=')[2].ToString().Replace("'", "").Trim();
}
}
DataTable dtPartlistInfo = iWrapFunctions.GetPartlistInfo(listQuery);
DataTable dtMainPartList = dtPartlistInfo.Clone();
foreach (DataRow row in dtPartlistInfo.Rows)
{
DataRow newMainRow = dtMainPartList.NewRow();
newMainRow[0] = row[0];
newMainRow[1] = row[1];
newMainRow[2] = row[2];
newMainRow[3] = row[3];
newMainRow[4] = row[4];
newMainRow[5] = row[5];
newMainRow[6] = row[6];
newMainRow[7] = row[7];
newMainRow[8] = row[8];
newMainRow[9] = row[9];
dtMainPartList.Rows.Add(newMainRow);
string childComp_itemNo = row["comp_item"].ToString();
string childItemNo = row["item"].ToString();
string childRevNo = row["revision"].ToString();
//childQuery = "SELECT * FROM rc_bom WHERE item='" + childItemNo + "' AND revision='" + childRevNo + "' ORDER BY pos_nr";
if (!childComp_itemNo.Equals(childItemNo))
{
string childQuery = listQueryAndDescription[0];
childQuery = childQuery.Replace("%item%", "'" + childComp_itemNo + "'");
childQuery = childQuery.Replace("%revision%", "'" + childRevNo + "'");
DataTable dtChildPartList = iWrapFunctions.GetPartlistInfo(childQuery);
foreach (DataRow childRow in dtChildPartList.Rows)
{
//dtMainPartList.ImportRow(childRow);
DataRow newrow = dtMainPartList.NewRow();
newrow[0] = childRow[0];
newrow[1] = childRow[1];
newrow[2] = childRow[2];
newrow[3] = childRow[3];
newrow[4] = childRow[4];
newrow[5] = childRow[5];
newrow[6] = childRow[6];
newrow[7] = childRow[7];
newrow[8] = childRow[8];
newrow[9] = childRow[9];
//newrow = childRow;
//newrow[ = childRow;
dtMainPartList.Rows.Add(newrow);
}
}
}
StringBuilder stringBuilder = new StringBuilder();
//stringBuilder = GetColumnInfoGridInfoFromDataTable(dtPartlistInfo, stringBuilder);
stringBuilder = GetColumnInfoGridInfoFromDataTable(dtMainPartList, stringBuilder);
string firstLabel = columnField[0].ToUpperFirstChar() + " : " + dtData.Rows[0][0].ToString();
string secondLabel = string.Empty;
if (dtData.Rows[0][requiredField[0]].ToString() != string.Empty && (!columnField.Contains(requiredField[0])))
{
secondLabel = requiredField[0].ToUpperFirstChar() + " : " + dtData.Rows[0][requiredField[0]].ToString();
}
string thirdLabel = string.Empty;
if (dtData.Rows[0][requiredField[1]].ToString() != string.Empty && (!columnField.Contains(requiredField[1])))
{
thirdLabel = requiredField[1].ToUpperFirstChar() + " : " + dtData.Rows[0][requiredField[1]].ToString();
}
string artRevInfo = "Keys:['" + firstLabel + "','" + secondLabel + "','" + thirdLabel + "','" + listQueryAndDescription[1] + "','" + currentRevision + "'],";
string revision = GetRevision(whereClause, sqlFrom, iWrapFunctions);
return("{" + artRevInfo + revision + stringBuilder + "}");
}