public Dictionary <string, object> Save(Modulem Record)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
string ErrorMessage = "";
try
{
Con_Oracle = new DBConnection();
if (Record.module_name.Trim().Length <= 0)
{
Lib.AddError(ref ErrorMessage, "Name Cannot Be Empty");
}
if (ErrorMessage != "")
{
throw new Exception(ErrorMessage);
}
if ((ErrorMessage = AllValid(Record)) != "")
{
throw new Exception(ErrorMessage);
}
DBRecord Rec = new DBRecord();
Rec.CreateRow("modulem", Record.rec_mode, "module_pkid", Record.module_pkid);
Rec.InsertString("module_name", Record.module_name, "Z");
Rec.InsertNumeric("module_order", Record.module_order.ToString());
if (Record.rec_mode == "ADD")
{
Rec.InsertString("rec_company_code", Record._globalvariables.comp_code);
}
sql = Rec.UpdateRow();
Con_Oracle.BeginTransaction();
Con_Oracle.ExecuteNonQuery(sql);
Con_Oracle.CommitTransaction();
Con_Oracle.CloseConnection();
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.RollbackTransaction();
Con_Oracle.CloseConnection();
}
throw Ex;
}
Con_Oracle.CloseConnection();
return(RetData);
}
public Dictionary <string, object> Save(pim_groupm Record)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
string ErrorMessage = "";
Boolean retvalue = false;
DataTable dt_parent = null;
try
{
Con_Oracle = new DBConnection();
if (Record.grp_name.Trim().Length <= 0)
{
Lib.AddError(ref ErrorMessage, "name Cannot Be Empty");
}
if (ErrorMessage != "")
{
throw new Exception(ErrorMessage);
}
if ((ErrorMessage = AllValid(Record)) != "")
{
throw new Exception(ErrorMessage);
}
if (Record.rec_type == "ROOT")
{
Record.grp_level = 1;
Record.grp_parent_id = "";
}
else
{
sql = "select * from pim_groupm where grp_pkid = '" + Record.grp_parent_id + "'";
dt_parent = Con_Oracle.ExecuteQuery(sql);
if (dt_parent.Rows.Count <= 0)
{
if (ErrorMessage != "")
{
throw new Exception("Invalid Parent");
}
}
Record.grp_level = Lib.Conv2Integer(dt_parent.Rows[0]["grp_level"].ToString()) + 1;
Record.grp_parent_id = dt_parent.Rows[0]["grp_pkid"].ToString();
}
if (Record.grp_level <= 0)
{
throw new Exception("Invalid group level");
}
sql = "select max(grp_name) as grp_name from pim_groupm where ";
sql += " rec_company_code = '" + Record._globalvariables.comp_code + "'";
sql += " and grp_name = '" + Record.grp_name.ToString().ToLower() + "'";
sql += " and grp_table_name = '" + Record.grp_table_name.ToString().ToLower() + "'";
if (Record.grp_parent_id == "")
{
sql += " and grp_parent_id is null ";
}
else
{
sql += " and grp_parent_id = '" + Record.grp_parent_id + "'";
}
if (Con_Oracle.IsRowExists(sql))
{
throw new Exception("Dupliate name not allowed ");
}
sql = "select nvl(max(grp_level_slno), 100000) + 1 as slno from pim_groupm where ";
if (Con_Oracle.DB == "SQL")
{
sql = "select isnull(max(grp_level_slno), 100000) + 1 as slno from pim_groupm where ";
}
sql += " rec_company_code = '" + Record._globalvariables.comp_code + "'";
sql += " and grp_table_name = '" + Record.grp_table_name + "'";
if (Record.grp_parent_id == "")
{
sql += " and grp_parent_id is null ";
}
else
{
sql += " and grp_parent_id = '" + Record.grp_parent_id + "'";
}
int iSlno = Lib.Conv2Integer(Con_Oracle.ExecuteScalar(sql).ToString());
if (iSlno <= 0)
{
throw new Exception("Invalid SL#");
}
if (Record.rec_type == "ROOT")
{
Record.grp_level_id = iSlno.ToString();
Record.grp_level_name = Record.grp_name;
}
else
{
Record.grp_level_id = dt_parent.Rows[0]["grp_level_id"].ToString() + "-" + iSlno.ToString();
Record.grp_level_name = dt_parent.Rows[0]["grp_level_name"].ToString() + "\\" + Record.grp_name;
}
DBRecord Rec = new DBRecord();
Rec.CreateRow("pim_groupm", Record.rec_mode, "grp_pkid", Record.grp_pkid);
Rec.InsertString("grp_name", Record.grp_name, "L");
Rec.InsertString("grp_parent_id", Record.grp_parent_id);
Rec.InsertNumeric("grp_level", Record.grp_level.ToString());
Rec.InsertString("grp_level_slno", iSlno.ToString());
Rec.InsertString("grp_level_id", Record.grp_level_id);
Rec.InsertString("grp_level_name", Record.grp_level_name, "L");
if (Record.rec_mode == "ADD")
{
Rec.InsertString("grp_table_name", Record.grp_table_name);
Rec.InsertString("rec_company_code", Record._globalvariables.comp_code);
Rec.InsertString("rec_hidden", "N");
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.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()");
}
}
sql = Rec.UpdateRow();
Con_Oracle.BeginTransaction();
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("grp_level_id", Record.grp_level_id);
RetData.Add("grp_level_name", Record.grp_level_name);
return(RetData);
}
public Dictionary <string, object> Save(pim_spot Record)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
string ErrorMessage = "";
Boolean retvalue = false;
int iSlno = 0;
try
{
Con_Oracle = new DBConnection();
if (Record.spot_date.Trim().Length <= 0)
{
Lib.AddError(ref ErrorMessage, "Date Cannot Be Empty");
}
if (Record.spot_store_id.Trim().Length <= 0)
{
Lib.AddError(ref ErrorMessage, "Store Cannot Be Empty");
}
if (Record.spot_vendor_id.Trim().Length <= 0)
{
Lib.AddError(ref ErrorMessage, "Vendor Cannot Be Empty");
}
if (ErrorMessage != "")
{
throw new Exception(ErrorMessage);
}
if ((ErrorMessage = AllValid(Record)) != "")
{
throw new Exception(ErrorMessage);
}
if (Record.rec_mode == "ADD")
{
sql = "select isnull(max(spot_slno), 1) + 1 as slno from pim_spotm where ";
sql += " rec_company_code = '" + Record._globalvariables.comp_code + "'";
iSlno = Lib.Conv2Integer(Con_Oracle.ExecuteScalar(sql).ToString());
}
else
{
iSlno = Record.spot_slno;
}
if (iSlno <= 0)
{
throw new Exception("Invalid CF#");
}
DBRecord Rec = new DBRecord();
Rec.CreateRow("pim_spotm", Record.rec_mode, "spot_pkid", Record.spot_pkid);
Rec.InsertDate("spot_date", Record.spot_date);
Rec.InsertString("spot_store_id", Record.spot_store_id);
Rec.InsertString("spot_vendor_id", Record.spot_vendor_id);
Rec.InsertString("spot_job_remarks", Record.spot_job_remarks);
if (Record.rec_mode == "ADD")
{
Rec.InsertNumeric("spot_slno", iSlno.ToString());
Rec.InsertString("spot_executive_name", Record._globalvariables.user_name);
Rec.InsertString("spot_store_contact_name", Record.spot_store_contact_name);
Rec.InsertString("spot_store_contact_tel", Record.spot_store_contact_tel);
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.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()");
}
}
sql = Rec.UpdateRow();
Con_Oracle.BeginTransaction();
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("slno", iSlno);
return(RetData);
}
public Dictionary <string, object> SaveDet(pim_spotd Record)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
string ErrorMessage = "";
Boolean retvalue = false;
DataTable dt_param = null;
int iSlno = 0;
try
{
Con_Oracle = new DBConnection();
if (Record.spotd_name.Trim().Length <= 0)
{
Lib.AddError(ref ErrorMessage, "Spot Name Cannot Be Empty");
}
if (Record.spotd_uom.Trim().Length <= 0)
{
Lib.AddError(ref ErrorMessage, "Unit Cannot Be Empty");
}
if (Lib.Conv2Decimal(Record.spotd_wd.ToString()) <= 0)
{
Lib.AddError(ref ErrorMessage, "Width Cannot Be Empty");
}
if (Lib.Conv2Decimal(Record.spotd_ht.ToString()) <= 0)
{
Lib.AddError(ref ErrorMessage, "Height Cannot Be Empty");
}
if (Record.spotd_artwork_id.Trim().Length <= 0)
{
Lib.AddError(ref ErrorMessage, "ArtWork Cannot Be Empty");
}
if (Record.spotd_product_id.Trim().Length <= 0)
{
Lib.AddError(ref ErrorMessage, "Product Cannot Be Empty");
}
if (ErrorMessage != "")
{
throw new Exception(ErrorMessage);
}
if ((ErrorMessage = AllValidDet(Record)) != "")
{
throw new Exception(ErrorMessage);
}
//sql = " select param_slno,param_file_name from param where param_pkid = '" + Record.spot_artwork_id + "'";
//dt_param = Con_Oracle.ExecuteQuery(sql);
if (Record.rec_mode == "ADD")
{
sql = "select isnull(max(spotd_slno), 0) + 1 as slno from pim_spotd where ";
sql += " spotd_parent_id = '" + Record.spotd_parent_id + "'";
iSlno = Lib.Conv2Integer(Con_Oracle.ExecuteScalar(sql).ToString());
}
else
{
iSlno = Record.spotd_slno;
}
if (iSlno <= 0)
{
throw new Exception("Invalid SL#");
}
DBRecord Rec = new DBRecord();
Rec.CreateRow("pim_spotd", Record.rec_mode, "spotd_pkid", Record.spotd_pkid);
Rec.InsertString("spotd_name", Record.spotd_name);
Rec.InsertString("spotd_uom", Record.spotd_uom);
Rec.InsertNumeric("spotd_wd", Record.spotd_wd.ToString());
Rec.InsertNumeric("spotd_ht", Record.spotd_ht.ToString());
Rec.InsertString("spotd_artwork_id", Record.spotd_artwork_id);
Rec.InsertString("spotd_product_id", Record.spotd_product_id);
Rec.InsertString("spotd_close_view", Record.spotd_close_view);
Rec.InsertString("spotd_long_view", Record.spotd_long_view);
Rec.InsertString("spotd_final_view", Record.spotd_final_view);
if (Record.rec_mode == "ADD")
{
Rec.InsertString("spotd_parent_id", Record.spotd_parent_id);
Rec.InsertNumeric("spotd_slno", iSlno.ToString());
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.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()");
}
}
sql = Rec.UpdateRow();
Con_Oracle.BeginTransaction();
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("slno", iSlno);
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);
}