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