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