public static string GetPartlistQueryAndKeys(string sqlFrom, string whereClause, string rep_code)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
string artCodeRevisonAndQuery = iWrapFunctions.GetArticleCodeAndRevisionAndPartlistDataQuery(sqlFrom, whereClause.Replace("|", "'"), rep_code);
string[] partlistInfoArray = artCodeRevisonAndQuery.Split(new string[] { "@@@@" }, StringSplitOptions.None);
string artRevInfo = "Keys:['" + partlistInfoArray[0] + "','" + partlistInfoArray[1] + "'],";
return("{" + artRevInfo + "Query:[\"" + partlistInfoArray[2] + "\"]}");
}
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);
}
public static string GetAvailableListQueryPair(string sqlFrom, string whereClause, string rep_code)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
DataTable listInfoTable = iWrapFunctions.GetReportExtensionInfo(rep_code);
List <string> columnsNamelist = new List <string>();
foreach (DataColumn column in listInfoTable.Columns)
{
if (column.ColumnName.StartsWith("list"))
{
columnsNamelist.Add(column.ColumnName);
}
}
StringBuilder listNameQueryPair = new StringBuilder();
listNameQueryPair.Append("{listNameQueryPair:[");
if (listInfoTable.Rows.Count > 0)
{
int i = 0;
string queryValue = string.Empty;
foreach (string column in columnsNamelist)
{
if (!listInfoTable.Rows[0][column].ToString().Equals(string.Empty))
{
//queryDescription.AppendFormat("['{0}','{1}']", listInfoTable.Rows[0][column], listInfoTable.Rows[0]["description_" + column]);
queryValue = listInfoTable.Rows[0][column].ToString();
if (queryValue.Contains("AS"))
{
queryValue.Replace("\"", ";double;Quote;");
}
listNameQueryPair.AppendFormat("['{0}','{1}']", column.Replace("sql", string.Empty).Replace("list_", string.Empty).ToUpperFirstChar(), queryValue);
if (i < columnsNamelist.Count - 1)
{
listNameQueryPair.Append(",");
}
}
i++;
}
}
listNameQueryPair.Append("]}");
return(listNameQueryPair.ToString());
}
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);
}
public static string GetPartlistInfo(string sqlFrom, string whereClause, string rep_code)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
string artCodeRevisonAndQuery = iWrapFunctions.GetArticleCodeAndRevisionAndPartlistDataQuery(sqlFrom, whereClause.Replace("|", "'"), rep_code);
string[] partlistInfoArray = artCodeRevisonAndQuery.Split(new string[] { "@@@@" }, StringSplitOptions.None);
DataTable dtPartlistInfo = iWrapFunctions.GetPartlistInfo(partlistInfoArray[2]);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder = GetColumnInfoGridInfoFromDataTable(dtPartlistInfo, stringBuilder);
string artRevInfo = "Keys:['" + partlistInfoArray[0] + "','" + partlistInfoArray[1] + "'],";
return("{" + artRevInfo + stringBuilder + "}");
}
public static string GetBaseMaterialDetail(string keyNameValues, string rep_code)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
DataTable dtBaseMaterial = iWrapFunctions.GetBaseMaterialDetail(keyNameValues, rep_code);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("\"columnInfo\":[");
string columns = string.Empty;
foreach (DataColumn column in dtBaseMaterial.Columns)
{
columns += "\"" + column.ColumnName + "\",";
}
stringBuilder.Append(columns);
stringBuilder = stringBuilder.Remove(stringBuilder.ToString().LastIndexOf(','), 1);
stringBuilder.Append("],");
stringBuilder.Append("gridInfo:[");
//int rowNum = 1;
for (int i = 0; i < dtBaseMaterial.Rows.Count; i++)
{
DataRow currentRow = dtBaseMaterial.Rows[i];
//stringBuilder.Append("[\"" + rowNum + "\",");
stringBuilder.Append("[");
foreach (DataColumn column in dtBaseMaterial.Columns)
{
stringBuilder.AppendFormat("\"{0}\",", GetJSONFormat(currentRow[column.ColumnName].ToString()));
}
stringBuilder = stringBuilder.Remove(stringBuilder.ToString().LastIndexOf(','), 1);
stringBuilder.Append("]");
if (i < dtBaseMaterial.Rows.Count - 1)
{
stringBuilder.Append(",");
}
}
stringBuilder.Append("]");
return("{" + stringBuilder + "}");
}
protected void btnDelete_Click(object sender, EventArgs e)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
string taskId = Request.QueryString["taskId"].ToString();
string xx = iWrapFunctions.DeleteTask(taskId);
string basePath = HIT.OB.STD.Wrapper.CommonFunctions.GetDocBasePath("DocBasePath");
string task_dir = "TASKS" + System.IO.Path.DirectorySeparatorChar + taskId;
//if (sqlFrom.ToUpper().Equals("TASKS"))
//{
if (System.IO.Directory.Exists(System.IO.Path.Combine(basePath, task_dir)))
{
System.IO.Directory.Delete(System.IO.Path.Combine(basePath, task_dir), true);
}
Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Close", "<script>ReloadOpener();</script>");
}
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)
{
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
if (!fuExcelItem.FileName.Equals(string.Empty))
{
string postedFile = this.fuExcelItem.PostedFile.FileName;
string postedFileFullName = GetTempDir() + @"\" + postedFile;
this.fuExcelItem.PostedFile.SaveAs(postedFileFullName);
DataTable dtItems = HIT.OB.STD.RC.Wrapper.OLEDB.GetDataTableFromExcel(postedFileFullName, "Sheet1");
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
try
{
iWrapFunctions.ImportExcelData(dtItems, "rc_item_hit");
}
catch (Exception ex)
{
lblError.Text = ex.Message;
lblError.Visible = true;
}
}
if (!fuExcelPartlist.FileName.Equals(string.Empty))
{
string postedFile = this.fuExcelPartlist.PostedFile.FileName;
string postedFileFullName = GetTempDir() + @"\" + postedFile;
this.fuExcelPartlist.PostedFile.SaveAs(postedFileFullName);
DataTable dtItems = HIT.OB.STD.RC.Wrapper.OLEDB.GetDataTableFromExcel(postedFileFullName, "Sheet1");
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
try
{
iWrapFunctions.ImportExcelData(dtItems, "rc_c_bomeng");
}
catch (Exception ex)
{
lblError.Text = ex.Message;
lblError.Visible = true;
}
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string editedBy = SecurityManager.GetUserName(SID.Value);
string label = txtSummary.Text.Trim();
string description = txtDescription.Text.Trim();
string assignedTo = lstUsers.SelectedItem.Text;
string taskSatus = lstTaskStatus.SelectedValue;
string taskId = Request.QueryString["taskId"].ToString();
try
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
string xx = iWrapFunctions.UpdateDetailTask(taskId, assignedTo, editedBy, label, description, taskSatus);
Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Close", "<script>ReloadOpener()</script>");
//Response.Write("<script type='text/javascript'>ReloadOpener();</script>");
}
catch (Exception ex)
{
Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "error", "<script>alert('" + ex.Message + "')</script>");
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
reportCode = Request.QueryString["report"].ToString();
string basePath = HIT.OB.STD.Wrapper.CommonFunctions.GetDocBasePath("DocBasePath");
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunc = dbManagerFactory.GetDBManager();
drReportConfig = iWrapFunc.GetReportConfigInfo(reportCode);
keyField = drReportConfig["sql_keyfields"].ToString();
matCode = Request.QueryString[keyField].ToString();
keyList = keyField;
valueList = matCode;
sqlFrom = drReportConfig["sql_from"].ToString();
selectedFields = drReportConfig["detail_fieldsets"].ToString().Trim(new char[] { ';' }).Replace(';', ',');
string fieldCaps = drReportConfig["field_caps"].ToString();
capsList = fieldCaps.Split(';');
string whereClause = " matcode='" + matCode + "'";
DataTable dtRelFile = iWrapFunc.GetRelativeFileName(sqlFrom, whereClause);
string relativePath = dtRelFile.Rows[0]["relfilename"].ToString();
docPath = Path.Combine(basePath, relativePath);
if (!File.Exists(docPath))
{
docPath = "";
}
else
{
docPath = docPath.Replace("\\", "@@@@");
}
}
catch (Exception ex)
{
}
}
public static string GetReportArguments(string reportCode)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IWrapFunctions iWrapFunctions = dbManagerFactory.GetDBManager();
DataTable dtReportInfo = iWrapFunctions.GetReportArguments(reportCode);
StringBuilder settingsBuilder = new StringBuilder();
string sqlSelect = string.Empty;
string FieldList = "";
StringBuilder sqlkeyfields = new StringBuilder();
StringBuilder detailSqlFields = new StringBuilder();
string fieldTypesJson = "";
string keyfields = string.Empty;
string detailSqlFieldSets = string.Empty;
if (dtReportInfo.Rows.Count > 0)
{
DataRow reportInfoRow = dtReportInfo.Rows[0];
string sqlFrom = reportInfoRow["sql_from"].ToString();
string reportSettings = reportInfoRow["report_settings"].ToString();
string orderBy = reportInfoRow["sql_orderby"].ToString();
string groupBy = reportInfoRow["sql_groupby"].ToString();
if (string.IsNullOrEmpty(groupBy))
{
groupBy = "NONE";
}
string fieldNameAndType = GetFieldAndTypeList(reportInfoRow["sql_from"].ToString(), reportCode);
string sqlWhere = reportInfoRow["sql_where"].ToString();
if (!string.IsNullOrEmpty(sqlWhere))
{
string isWhereValid = HIT.OB.STD.Core.BLL.OBController.ValidateWhereClause(reportCode, sqlFrom, sqlWhere);
if (isWhereValid != "true")
{
sqlWhere = "INVALID_WHERE";
}
}
string fieldCaps = reportInfoRow["field_caps"].ToString().Replace(',', ';').Trim(new char[] { ';' });
FieldList = fieldNameAndType.Split(new string[] { "$$$$" }, StringSplitOptions.None)[0];
fieldTypesJson = fieldNameAndType.Split(new string[] { "$$$$" }, StringSplitOptions.None)[1];
settingsBuilder.AppendFormat("report_code: \"{0}\"", GetJSONFormat(reportCode));
settingsBuilder.AppendFormat(",report_name: \"{0}\"", GetJSONFormat(reportInfoRow["report_name"].ToString()));
settingsBuilder.AppendFormat(",field_caps: \"{0}\"", GetJSONFormat(fieldCaps));
string selectedFields = FieldList.Replace("'", "").Replace(',', ';').Trim(';');
settingsBuilder.AppendFormat(",sql_select: \"{0}\"", GetJSONFormat(selectedFields));
settingsBuilder.AppendFormat(",sql_from: \"{0}\"", GetJSONFormat(sqlFrom));
settingsBuilder.AppendFormat(",sql_where: \"{0}\"", GetJSONFormat(sqlWhere));
settingsBuilder.AppendFormat(",sql_groupby: \"{0}\"", GetJSONFormat(groupBy));
settingsBuilder.AppendFormat(",gis_theme_layer: \"{0}\"", reportInfoRow["gis_theme_layer"].ToString().ToLower());
settingsBuilder.AppendFormat(",sql_orderby: \"{0}\"", GetJSONFormat(orderBy));
settingsBuilder.AppendFormat(",sql_orderdir: \"{0}\"", GetJSONFormat(reportInfoRow["sql_orderdir"].ToString()));
settingsBuilder.AppendFormat(",report_settings: \"{0}\"", GetJSONFormat(reportSettings));
string multiSelect = reportInfoRow["multiselect"].ToString().ToLower();
settingsBuilder.AppendFormat(",multiselect: \"{0}\"", multiSelect);
// sqlSelect = reportInfoRow["sql_select"].ToString();
detailSqlFieldSets = reportInfoRow["detail_fieldsets"].ToString().Replace(",", ";").Trim(';');
detailSqlFields.AppendFormat("detailsqlfields: \"{0}\"", GetJSONFormat(detailSqlFieldSets));
keyfields = reportInfoRow["sql_keyfields"].ToString().Replace(',', ';').Trim(';');
sqlkeyfields.AppendFormat("sqlkeyfields: {0}", GetJSONFormat(SQLKeyFieldsJSON(keyfields)));
}
dtReportInfo.Dispose();
//***Report function processing
DataTable dtFunctionReportInfo = iWrapFunctions.GetReportFunctionsList(reportCode);
StringBuilder functionList = new StringBuilder();
StringBuilder sqlMandatory = new StringBuilder();
string parameters = string.Empty;
string sqlFunList = string.Empty;
string imageUrls = string.Empty;
string orderPos = string.Empty;
string temp = string.Empty;
string commonParams = string.Empty;
string iscustom = string.Empty;
if (dtFunctionReportInfo.Rows.Count > 0)
{
foreach (DataRow dr in dtFunctionReportInfo.Rows)
{
sqlFunList = dr["function_name"].ToString();
orderPos = dr["order_position"].ToString();
parameters = dr["parameters"].ToString().Replace(',', ';').Trim(new char[] { ';' });
iscustom = dr["iscustom"].ToString().ToLower();
// getting sqlparameters as semicolon separated.
parameters = parameters.Trim(';');
StringBuilder param = new StringBuilder();
param.Append("'").Append(parameters.Replace(";", "','")).Append("'");
// checking common parameters.
commonParams = CommonParameters(parameters, commonParams);
functionList.AppendFormat("['{0}','{1}','{2}',[{3}]]", sqlFunList.ToUpper(), orderPos, iscustom, param.ToString());
functionList.Append(",");
} // END of foreach
commonParams = CompareSqlManSqlKey(commonParams, keyfields);
functionList.Remove(functionList.ToString().LastIndexOf(','), 1);
sqlMandatory.Append("'").Append(commonParams).Append("'");
}
else
{
sqlMandatory.Append("");
functionList.Append("");
}
dtFunctionReportInfo.Dispose();
return("{settings:{" + settingsBuilder.ToString() +
"},fieldTypes:{" + fieldTypesJson + "},fieldList:[" + FieldList + "]" +
",sqlmandatory:[" + sqlMandatory.ToString() +
"],functionlist:[" + functionList.ToString() +
"]," + sqlkeyfields.ToString() + "," + detailSqlFields.ToString() + "}");
}
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 + "}");
}
