public IHttpActionResult SaveDetail(tablesd Record) { try { using (TableService obj = new TableService()) return(Ok(obj.SaveDetail(Record))); } catch (Exception Ex) { return(ResponseMessage(Request.CreateErrorResponse(HttpStatusCode.BadRequest, Ex.Message.ToString()))); } }
public string AllValid2(tablesd Record) { string str = ""; try { sql = "select tabd_pkid from ("; sql += " select tabd_pkid from tablesd a "; sql += " where tabd_parent_id = '" + Record.tabd_parent_id + "' and tabd_col_name = '" + Record.tabd_col_name + "' "; sql += ") a where tabd_pkid <> '" + Record.tabd_pkid + "'"; //sql = sql.Replace("{CODE}", Record.comp_code); if (Con_Oracle.IsRowExists(sql)) { str = "Table Name Exists"; } } catch (Exception Ex) { str = Ex.Message.ToString(); } return(str); }
public Dictionary <string, object> GetRecord(Dictionary <string, object> SearchData, string ServerImageUrl) { Dictionary <string, object> RetData = new Dictionary <string, object>(); DataRow DROW = null; pim_docm mRow = new pim_docm(); List <tablesd> mList = new List <tablesd>(); tablesd mRec = new tablesd(); LovService Lov = new LovService(); string pkid = SearchData["pkid"].ToString(); string comp_code = SearchData["comp_code"].ToString(); string table_name = SearchData["table_name"].ToString(); try { DataTable Dt_Rec = new DataTable(); string str = ReadColumns(table_name, comp_code); sql = " select grp_name, doc_table_name,doc_pkid,doc_grp_id,grp_level_name, doc_store_id, d.comp_name as store_name, "; sql += " doc_slno, doc_name, doc_file_name, doc_thumbnail "; if (str.Length > 0) { sql += "," + str; } sql += " from pim_docm a "; sql += " left join " + table_name + " b on a.doc_pkid = b.doc_parent_id "; sql += " left join pim_groupm c on a.doc_grp_id = c.grp_pkid "; sql += " left join companym d on a.doc_store_id = d.comp_pkid "; sql += " where doc_pkid = '" + pkid + "'"; Con_Oracle = new DBConnection(); Dt_Rec = Con_Oracle.ExecuteQuery(sql); Con_Oracle.CloseConnection(); if (Dt_Rec.Rows.Count > 0) { DROW = Dt_Rec.Rows[0]; } foreach (DataRow Dr in Dt_Rec.Rows) { mRow = new pim_docm(); mRow.doc_pkid = Dr["doc_pkid"].ToString(); mRow.doc_slno = Lib.Conv2Integer(Dr["doc_slno"].ToString()); mRow.doc_store_id = Dr["doc_store_id"].ToString(); mRow.doc_store_name = Dr["store_name"].ToString(); mRow.doc_grp_id = Dr["doc_grp_id"].ToString(); mRow.doc_grp_level_name = Dr["grp_level_name"].ToString(); mRow.doc_name = Dr["doc_name"].ToString(); mRow.doc_file_name = Dr["doc_file_name"].ToString(); mRow.doc_table_name = Dr["doc_table_name"].ToString(); mRow.doc_thumbnail = Dr["doc_thumbnail"].ToString(); mRow.doc_server_folder = Lib.getPath(ServerImageUrl, comp_code, table_name, mRow.doc_slno.ToString(), false); mRow.doc_file_uploaded = false; break; } Dt_Rec = new DataTable(); sql = " select tab_name, tab_table_name, b.* from "; sql += " tablesm a inner join tablesd b on a.tab_pkid = tabd_parent_id "; sql += " where a.rec_company_code = '" + comp_code + "' and tab_table_name = '" + table_name + "' and b.rec_deleted ='N'"; sql += " order by tabd_col_order "; Con_Oracle = new DBConnection(); Dt_Rec = Con_Oracle.ExecuteQuery(sql); Con_Oracle.CloseConnection(); foreach (DataRow Dr in Dt_Rec.Rows) { mRec = new tablesd(); mRec.tabd_table_name = Dr["tab_table_name"].ToString(); mRec.tabd_col_name = Dr["tabd_col_name"].ToString(); mRec.tabd_col_caption = Dr["tabd_col_caption"].ToString(); mRec.tabd_col_type = Dr["tabd_col_type"].ToString(); mRec.tabd_col_case = Dr["tabd_col_case"].ToString(); mRec.tabd_col_mandatory = Dr["tabd_col_mandatory"].ToString(); mRec.tabd_col_rows = Lib.Conv2Integer(Dr["tabd_col_rows"].ToString()); if (Dr["tabd_col_type"].ToString() == "DATE") { mRec.tabd_col_value = Lib.DatetoString(DROW["COL_" + Dr["tabd_col_name"].ToString()]); } else if (Dr["tabd_col_type"].ToString() == "LIST") { mRec.tabd_col_list = Dr["tabd_col_list"].ToString(); mRec.tabd_col_id = DROW["COL_" + Dr["tabd_col_name"].ToString()].ToString(); mRec.tabd_col_value = ""; if (Dr["tabd_col_id"].ToString().Length > 0) { mRec.tabd_col_value = Lov.getParamValue(comp_code, DROW["COL_" + Dr["tabd_col_name"].ToString()].ToString(), "PARAM_NAME"); } } else { mRec.tabd_col_value = DROW["COL_" + Dr["tabd_col_name"].ToString()].ToString(); } mRec.tabd_col_len = Lib.Conv2Integer(Dr["tabd_col_len"].ToString()); mRec.tabd_col_dec = Lib.Conv2Integer(Dr["tabd_col_dec"].ToString()); mRec.tabd_col_order = Lib.Conv2Integer(Dr["tabd_col_order"].ToString()); mRec.tabd_col_file_uploaded = false; mList.Add(mRec); } } catch (Exception Ex) { if (Con_Oracle != null) { Con_Oracle.CloseConnection(); } throw Ex; } RetData.Add("record", mRow); RetData.Add("list", mList); return(RetData); }
public Dictionary <string, object> LoadDefault(Dictionary <string, object> SearchData) { Dictionary <string, object> RetData = new Dictionary <string, object>(); List <tablesd> mList = new List <tablesd>(); tablesd mRec = new tablesd(); tablesm Rec = new tablesm(); string comp_code = SearchData["comp_code"].ToString(); string table_name = SearchData["table_name"].ToString(); try { Con_Oracle = new DBConnection(); DataTable Dt_First = new DataTable(); sql = " select tab_name, tab_table_name, tab_id, tab_store, tab_group, tab_sku,tab_file, tab_store_duplication from "; sql += " tablesm a "; sql += " where a.rec_company_code = '" + comp_code + "' and tab_table_name = '" + table_name + "'"; Dt_First = Con_Oracle.ExecuteQuery(sql); Rec.tab_id = ""; Rec.tab_store = ""; Rec.tab_group = ""; Rec.tab_sku = ""; Rec.tab_file = ""; if (Dt_First.Rows.Count > 0) { Rec.tab_id = Dt_First.Rows[0]["tab_id"].ToString(); Rec.tab_store = Dt_First.Rows[0]["tab_store"].ToString(); Rec.tab_group = Dt_First.Rows[0]["tab_group"].ToString(); Rec.tab_sku = Dt_First.Rows[0]["tab_sku"].ToString(); Rec.tab_file = Dt_First.Rows[0]["tab_file"].ToString(); Rec.tab_store_duplication = (Dt_First.Rows[0]["tab_store_duplication"].ToString() == "Y") ? true : false; } DataTable Dt_Rec = new DataTable(); sql = " select tab_name, tab_table_name, b.* from "; sql += " tablesm a inner join tablesd b on a.tab_pkid = tabd_parent_id "; sql += " where a.rec_company_code = '" + comp_code + "' and tab_table_name = '" + table_name + "' and b.rec_deleted = 'N'"; sql += " order by tabd_col_order "; Dt_Rec = Con_Oracle.ExecuteQuery(sql); Con_Oracle.CloseConnection(); foreach (DataRow Dr in Dt_Rec.Rows) { mRec = new tablesd(); mRec.tabd_table_name = Dr["tab_table_name"].ToString(); mRec.tabd_col_name = Dr["tabd_col_name"].ToString(); mRec.tabd_col_caption = Dr["tabd_col_caption"].ToString(); mRec.tabd_col_type = Dr["tabd_col_type"].ToString(); mRec.tabd_col_mandatory = Dr["tabd_col_mandatory"].ToString(); mRec.tabd_col_id = Dr["tabd_col_id"].ToString(); mRec.tabd_col_value = Dr["tabd_col_value"].ToString(); mRec.tabd_col_list = Dr["tabd_col_list"].ToString(); mRec.tabd_col_len = Lib.Conv2Integer(Dr["tabd_col_len"].ToString()); mRec.tabd_col_dec = Lib.Conv2Integer(Dr["tabd_col_dec"].ToString()); mRec.tabd_col_order = Lib.Conv2Integer(Dr["tabd_col_order"].ToString()); mRec.tabd_col_file_uploaded = false; if (Dr["tabd_col_type"].ToString() == "LIST") { mRec.tabd_col_value = ""; mRec.tabd_col_id = ""; } mList.Add(mRec); } } catch (Exception Ex) { if (Con_Oracle != null) { Con_Oracle.CloseConnection(); } throw Ex; } RetData.Add("list", mList); RetData.Add("tablesm", Rec); return(RetData); }
public Dictionary <string, object> SaveDetail(tablesd Record) { Dictionary <string, object> RetData = new Dictionary <string, object>(); string ErrorMessage = ""; Boolean retvalue = false; string sql1 = ""; DBRecord Rec = null; string flag = ""; int iOrder = 0; try { Con_Oracle = new DBConnection(); if (Record.tabd_col_name.Trim().Length <= 0) { Lib.AddError(ref ErrorMessage, "Column Name Cannot Be Empty"); } if (Record.tabd_col_caption.Trim().Length <= 0) { Lib.AddError(ref ErrorMessage, "Column Caption Cannot Be Empty"); } if (ErrorMessage != "") { throw new Exception(ErrorMessage); } Record.tabd_col_name = Record.tabd_col_name.ToUpper().Replace(" ", ""); if ((ErrorMessage = AllValid2(Record)) != "") { throw new Exception(ErrorMessage); } if (Record.rec_mode == "ADD" && Record.tabd_col_order <= 0) { sql = "select nvl(max(tabd_col_order),10) + 10 from tablesd where rec_company_code = '" + Record._globalvariables.comp_code + "'"; if (Con_Oracle.DB == "SQL") { sql = "select isnull(max(tabd_col_order),10) + 10 from tablesd where rec_company_code = '" + Record._globalvariables.comp_code + "'"; } sql += " and tabd_parent_id ='" + Record.tabd_parent_id + "'"; iOrder = Lib.Conv2Integer(Con_Oracle.ExecuteScalar(sql).ToString()); } Rec = new DBRecord(); Rec.CreateRow("tablesd", Record.rec_mode, "tabd_pkid", Record.tabd_pkid); Rec.InsertString("tabd_col_caption", Record.tabd_col_caption, "P"); Rec.InsertString("tabd_col_type", Record.tabd_col_type, "U"); Rec.InsertString("tabd_col_case", Record.tabd_col_case, "U"); Rec.InsertString("tabd_col_mandatory", Record.tabd_col_mandatory, "U"); Rec.InsertString("tabd_col_id", Record.tabd_col_id, "P"); Rec.InsertString("tabd_col_value", Record.tabd_col_value, "P"); Rec.InsertString("tabd_col_list", Record.tabd_col_list, "P"); Rec.InsertNumeric("tabd_col_rows", Record.tabd_col_rows.ToString()); Rec.InsertNumeric("tabd_col_len", Record.tabd_col_len.ToString()); Rec.InsertNumeric("tabd_col_dec", Record.tabd_col_dec.ToString()); Rec.InsertString("rec_deleted", (Record.rec_deleted) ? "Y" : "N"); if (Record.rec_mode == "ADD") { if (Record.tabd_col_order > 0) { Rec.InsertNumeric("tabd_col_order", Record.tabd_col_order.ToString()); } else { Rec.InsertNumeric("tabd_col_order", iOrder.ToString()); } Rec.InsertString("tabd_parent_id", Record.tabd_parent_id); Rec.InsertString("tabd_col_name", Record.tabd_col_name, "U"); Rec.InsertString("rec_company_code", Record._globalvariables.comp_code); Rec.InsertString("rec_created_by", Record._globalvariables.user_code); if (Con_Oracle.DB == "ORACLE") { Rec.InsertFunction("rec_created_date", "SYSDATE"); } else { Rec.InsertFunction("rec_created_date", "getdate()"); } } if (Record.rec_mode == "EDIT") { Rec.InsertNumeric("tabd_col_order", Record.tabd_col_order.ToString()); Rec.InsertString("rec_edited_by", Record._globalvariables.user_code); if (Con_Oracle.DB == "ORACLE") { Rec.InsertFunction("rec_edited_date", "SYSDATE"); } else { Rec.InsertFunction("rec_edited_date", "getdate()"); } } if (Record.rec_mode == "ADD") { flag = " ADD "; } if (Con_Oracle.DB == "ORACLE") { if (Record.rec_mode == "EDIT") { flag = " MODIFY "; } } else { if (Record.rec_mode == "EDIT") { flag = " ALTER COLUMN "; } } sql1 = ""; if (Record.tabd_col_type == "TEXT" || Record.tabd_col_type == "MEMO") { if (Con_Oracle.DB == "ORACLE") { sql1 += "COL_" + Record.tabd_col_name + " nvarchar2(" + Record.tabd_col_len + ")"; } else { sql1 += "COL_" + Record.tabd_col_name + " nvarchar(" + Record.tabd_col_len + ")"; } } if (Record.tabd_col_type == "NUMBER") { if (Con_Oracle.DB == "ORACLE") { sql1 += "COL_" + Record.tabd_col_name + " number(" + Record.tabd_col_len + "," + Record.tabd_col_dec + ")"; } else { sql1 += "COL_" + Record.tabd_col_name + " numeric(" + Record.tabd_col_len + "," + Record.tabd_col_dec + ")"; } } if (Record.tabd_col_type == "DATE") { if (Con_Oracle.DB == "ORACLE") { sql1 += "COL_" + Record.tabd_col_name + " DATE"; } else { sql1 += "COL_" + Record.tabd_col_name + " DATETIME"; } } if (Record.tabd_col_type == "LIST") { if (Con_Oracle.DB == "ORACLE") { sql1 += "COL_" + Record.tabd_col_name + " nvarchar2(40)"; } else { sql1 += "COL_" + Record.tabd_col_name + " nvarchar(40)"; } } if (Record.tabd_col_type == "FILE") { if (Con_Oracle.DB == "ORACLE") { sql1 += "COL_" + Record.tabd_col_name + " nvarchar2(100)"; } else { sql1 += "COL_" + Record.tabd_col_name + " nvarchar(100)"; } } if (sql1 != "") { sql1 = " ALTER TABLE " + Record.tabd_table_name.ToUpper() + " " + flag + " " + sql1; } sql = Rec.UpdateRow(); Con_Oracle.BeginTransaction(); if (sql1 != "") { Con_Oracle.ExecuteNonQuery(sql1); } Con_Oracle.ExecuteNonQuery(sql); Con_Oracle.CommitTransaction(); Con_Oracle.CloseConnection(); retvalue = true; } catch (Exception Ex) { if (Con_Oracle != null) { Con_Oracle.RollbackTransaction(); Con_Oracle.CloseConnection(); } retvalue = false; throw Ex; } Con_Oracle.CloseConnection(); RetData.Add("retvalue", retvalue); RetData.Add("col_name", Record.tabd_col_name); RetData.Add("iorder", iOrder); return(RetData); }
public Dictionary <string, object> GetRecord(Dictionary <string, object> SearchData) { Dictionary <string, object> RetData = new Dictionary <string, object>(); tablesm mRow = new tablesm(); List <tablesd> mList = new List <tablesd>(); tablesd mRec = new tablesd(); string pkid = SearchData["pkid"].ToString(); try { DataTable Dt_Rec = new DataTable(); sql = " select tab_pkid, tab_name, tab_table_name, tab_caption, tab_id, tab_store, tab_group, tab_sku,tab_file, "; sql += " tab_sku_duplication, tab_store_duplication,tab_campaign_table "; sql += " from Tablesm a "; sql += " where tab_pkid = '" + pkid + "'"; Con_Oracle = new DBConnection(); Dt_Rec = Con_Oracle.ExecuteQuery(sql); Con_Oracle.CloseConnection(); foreach (DataRow Dr in Dt_Rec.Rows) { mRow = new tablesm(); mRow.tab_pkid = Dr["tab_pkid"].ToString(); mRow.tab_name = Dr["tab_name"].ToString(); mRow.tab_table_name = Dr["tab_table_name"].ToString(); mRow.tab_caption = Dr["tab_caption"].ToString(); mRow.tab_id = Dr["tab_id"].ToString(); mRow.tab_store = Dr["tab_store"].ToString(); mRow.tab_group = Dr["tab_group"].ToString(); mRow.tab_sku = Dr["tab_sku"].ToString(); mRow.tab_file = Dr["tab_file"].ToString(); mRow.tab_sku_duplication = Dr["tab_sku_duplication"].ToString() == "Y" ? true : false; mRow.tab_store_duplication = Dr["tab_store_duplication"].ToString() == "Y" ? true : false; mRow.tab_campaign_table = Dr["tab_campaign_table"].ToString() == "Y" ? true : false; break; } Dt_Rec = new DataTable(); sql = " select b.* from "; sql += " tablesd b "; sql += " where b.tabd_parent_id = '" + pkid + "' "; sql += " order by tabd_col_order "; Con_Oracle = new DBConnection(); Dt_Rec = Con_Oracle.ExecuteQuery(sql); Con_Oracle.CloseConnection(); foreach (DataRow Dr in Dt_Rec.Rows) { mRec = new tablesd(); mRec.tabd_pkid = Dr["tabd_pkid"].ToString(); mRec.tabd_parent_id = Dr["tabd_parent_id"].ToString(); mRec.tabd_tab_name = mRow.tab_name; mRec.tabd_table_name = mRow.tab_table_name; mRec.tabd_col_name = Dr["tabd_col_name"].ToString(); mRec.tabd_col_caption = Dr["tabd_col_caption"].ToString(); mRec.tabd_col_type = Dr["tabd_col_type"].ToString(); mRec.tabd_col_case = Dr["tabd_col_case"].ToString(); mRec.tabd_col_mandatory = Dr["tabd_col_mandatory"].ToString(); mRec.tabd_col_id = Dr["tabd_col_id"].ToString(); mRec.tabd_col_value = Dr["tabd_col_value"].ToString(); mRec.tabd_col_list = Dr["tabd_col_list"].ToString(); mRec.tabd_col_rows = Lib.Conv2Integer(Dr["tabd_col_rows"].ToString()); mRec.tabd_col_len = Lib.Conv2Integer(Dr["tabd_col_len"].ToString()); mRec.tabd_col_dec = Lib.Conv2Integer(Dr["tabd_col_dec"].ToString()); mRec.tabd_col_order = Lib.Conv2Integer(Dr["tabd_col_order"].ToString()); mRec.rec_deleted = (Dr["rec_deleted"].ToString() == "Y") ? true : false; mRec.rec_mode = "EDIT"; mList.Add(mRec); } } catch (Exception Ex) { if (Con_Oracle != null) { Con_Oracle.CloseConnection(); } throw Ex; } RetData.Add("record", mRow); RetData.Add("records", mList); return(RetData); }