public Boolean UpdateDocFileName(pim_docm Record, string fldName, string value = "")
{
Boolean bRet = false;
Con_Oracle = new DBConnection();
if (fldName.ToUpper() == "DOC_FILE_NAME" || fldName.ToUpper() == "DOC_THUMBNAIL")
{
sql = "update pim_docm set " + fldName + " = '" + value + "' where doc_pkid = '" + Record.doc_pkid + "'";
}
else
{
sql = "update " + Record.doc_table_name + " set " + fldName + " = '" + value + "' where doc_parent_id = '" + Record.doc_pkid + "'";
}
try
{
Con_Oracle.BeginTransaction();
Con_Oracle.ExecuteNonQuery(sql);
Con_Oracle.CommitTransaction();
Con_Oracle.CloseConnection();
bRet = true;
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
bRet = false;
throw Ex;
}
return(bRet);
}
public string AllValid(pim_docm Record, DataRow HeaderRow)
{
string str = "";
try
{
if (HeaderRow["tab_sku"].ToString() != "" && HeaderRow["tab_sku_duplication"].ToString() == "N")
{
sql = "select doc_pkid from (";
sql += " select doc_pkid from pim_docm a ";
sql += " where doc_name = '" + Record.doc_name + "' ";
sql += " and doc_table_name = '" + Record.doc_table_name + "' ";
sql += " ) a where doc_pkid <> '" + Record.doc_pkid + "'";
if (Con_Oracle.IsRowExists(sql))
{
str = HeaderRow["tab_sku"].ToString() + " Exists";
}
}
else if (HeaderRow["tab_store"].ToString() != "" && HeaderRow["tab_store_duplication"].ToString() == "N")
{
sql = "select doc_pkid from (";
sql += " select doc_pkid from pim_docm a ";
sql += " where doc_store_id = '" + Record.doc_store_id + "' ";
sql += " and doc_table_name = '" + Record.doc_table_name + "' ";
sql += " ) a where doc_pkid <> '" + Record.doc_pkid + "'";
if (Con_Oracle.IsRowExists(sql))
{
str = HeaderRow["tab_store"].ToString() + " Exists";
}
}
}
catch (Exception Ex)
{
str = Ex.Message.ToString();
}
return(str);
}
public Dictionary <string, object> Save(pim_docm Record, tablesd [] Records, string ServerImageUrl)
{
DataTable Dt_Tablesm = new DataTable();
Dictionary <string, object> RetData = new Dictionary <string, object>();
string ErrorMessage = "";
Boolean retvalue = false;
DBRecord Rec = null;
Boolean is_campaign_table = false;
int iSlno = 0;
try
{
Con_Oracle = new DBConnection();
sql = "select * from tablesm where rec_company_code = '" + Record._globalvariables.comp_code + "' and tab_table_name = '" + Record.doc_table_name + "'";
Dt_Tablesm = Con_Oracle.ExecuteQuery(sql);
if (Dt_Tablesm.Rows.Count > 0)
{
is_campaign_table = (Dt_Tablesm.Rows[0]["tab_campaign_table"].ToString() == "Y") ? true : false;
}
if (Dt_Tablesm.Rows.Count <= 0)
{
Lib.AddError(ref ErrorMessage, "Entity Not Found");
}
//if (Record.doc_name.Trim().Length <= 0)
// Lib.AddError(ref ErrorMessage, "name Cannot Be Empty");
if (ErrorMessage != "")
{
throw new Exception(ErrorMessage);
}
if ((ErrorMessage = AllValid(Record, Dt_Tablesm.Rows[0])) != "")
{
throw new Exception(ErrorMessage);
}
if (Record.rec_mode == "ADD")
{
sql = "select nvl(max(doc_slno), 1000) + 1 as slno from pim_docm ";
if (Con_Oracle.DB == "SQL")
{
sql = "select isnull(max(doc_slno), 1000) + 1 as slno from pim_docm ";
}
sql += " where rec_company_code = '" + Record._globalvariables.comp_code + "' and doc_table_name ='" + Record.doc_table_name + "'";
//sql += " and doc_table_name = '" + Record.doc_table_name + "'";
iSlno = Lib.Conv2Integer(Con_Oracle.ExecuteScalar(sql).ToString());
Record.doc_slno = iSlno;
if (iSlno <= 0)
{
throw new Exception("Invalid SL#");
}
}
else
{
iSlno = Record.doc_slno;
}
sql = "";
string sql1 = "";
Rec = new DBRecord();
Rec.CreateRow("pim_docm", Record.rec_mode, "doc_pkid", Record.doc_pkid);
Rec.InsertString("doc_name", Record.doc_name, "P");
Rec.InsertString("doc_file_name", Record.doc_file_name, "P");
Rec.InsertString("doc_store_id", Record.doc_store_id);
Rec.InsertString("doc_grp_id", Record.doc_grp_id);
if (Record.doc_file_name.Trim().Trim().Length <= 0)
{
Rec.InsertString("doc_thumbnail", "");
}
if (Record.rec_mode == "ADD")
{
Rec.InsertString("doc_slno", Record.doc_slno.ToString());
Rec.InsertString("doc_table_name", Record.doc_table_name);
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();
Rec = new DBRecord();
Rec.CreateRow(Record.doc_table_name, Record.rec_mode, "doc_parent_id", Record.doc_pkid);
foreach (tablesd mRow in Records)
{
if (mRow.tabd_col_type == "DATE")
{
Rec.InsertDate("COL_" + mRow.tabd_col_name, mRow.tabd_col_value);
}
else if (mRow.tabd_col_type == "LIST")
{
Rec.InsertString("COL_" + mRow.tabd_col_name, mRow.tabd_col_id, "P");
}
else
{
Rec.InsertString("COL_" + mRow.tabd_col_name, mRow.tabd_col_value, "P");
}
}
if (Record.rec_mode == "ADD")
{
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()");
}
}
sql1 = Rec.UpdateRow();
Con_Oracle.BeginTransaction();
Con_Oracle.ExecuteNonQuery(sql);
Con_Oracle.ExecuteNonQuery(sql1);
Con_Oracle.CommitTransaction();
Con_Oracle.CloseConnection();
try
{
if (is_campaign_table == false)
{
google_uploader g = new google_uploader();
g.bSingle = true;
g.comp_code = Record._globalvariables.comp_code;
g.user_id = Record._globalvariables.user_pkid;
string str = g.Process(Record.doc_table_name, "name");
if (str != "")
{
g.UploadData(Record.doc_pkid);
}
}
}
catch (Exception)
{
}
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);
string server = Lib.getPath(ServerImageUrl, Record._globalvariables.comp_code, Record.doc_table_name, Record.doc_slno.ToString(), false);
RetData.Add("server", server);
RetData.Add("thumbnail", Record.doc_thumbnail);
return(RetData);
}
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 IDictionary <string, object> List(Dictionary <string, object> SearchData, string ServerImagURL)
{
string sWhere = "";
Dictionary <string, object> RetData = new Dictionary <string, object>();
Con_Oracle = new DBConnection();
List <pim_docm> mList = new List <pim_docm>();
pim_docm mRow;
string searchstring = SearchData["searchstring"].ToString().ToUpper();
string table_name = SearchData["grp_table_name"].ToString();
string comp_code = SearchData["comp_code"].ToString();
string type = SearchData["type"].ToString();
string cap_store = SearchData["cap_store"].ToString();
string user_id = SearchData["user_id"].ToString();
Boolean user_admin = (Boolean)SearchData["user_admin"];
long page_count = (long)SearchData["page_count"];
long page_current = (long)SearchData["page_current"];
long page_rows = (long)SearchData["page_rows"];
long page_rowcount = (long)SearchData["page_rowcount"];
long startrow = 0;
long endrow = 0;
try
{
sWhere = " where a.rec_company_code ='" + comp_code + "' ";
sWhere += " and doc_table_name = '" + table_name + "'";;
if (searchstring != "")
{
sWhere += " and (";
sWhere += " doc_name like '%" + searchstring.ToLower() + "%'";
if (cap_store != "")
{
sWhere += " or comp_name like '%" + searchstring.ToLower() + "%'";
}
sWhere += " )";
}
if (type == "NEW")
{
sql = "SELECT count(*) as total, ceil(COUNT(*) / " + page_rows.ToString() + ") page_total ";
if (Con_Oracle.DB == "SQL")
{
sql = "SELECT count(*) as total, ceiling(COUNT(*) / cast(" + page_rows.ToString() + " as decimal) ) page_total ";
}
sql += " FROM pim_docm a ";
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 ";
if (cap_store != "" && !user_admin)
{
sql += " inner join userd e on e.rec_type = 'S' and a.doc_store_id = e.user_branch_id and e.user_id ='" + user_id + "'";
}
sql += sWhere;
DataTable Dt_Temp = new DataTable();
Dt_Temp = Con_Oracle.ExecuteQuery(sql);
if (Dt_Temp.Rows.Count > 0)
{
page_rowcount = Lib.Conv2Integer(Dt_Temp.Rows[0]["total"].ToString());
page_count = Lib.Conv2Integer(Dt_Temp.Rows[0]["page_total"].ToString());
}
page_current = 1;
}
else
{
if (type == "FIRST")
{
page_current = 1;
}
if (type == "PREV" && page_current > 1)
{
page_current--;
}
if (type == "NEXT" && page_current < page_count)
{
page_current++;
}
if (type == "LAST")
{
page_current = page_count;
}
}
startrow = (page_current - 1) * page_rows + 1;
endrow = (startrow + page_rows) - 1;
DataTable Dt_List = new DataTable();
sql = "";
sql += " select * from ( ";
sql += " select a.doc_pkid,doc_slno,doc_name,doc_table_name, doc_file_name, d.comp_name as store_name, grp_name, grp_level_name, doc_thumbnail, a.rec_created_by, a.rec_created_date ";
sql += " ,row_number() over(order by doc_slno) rn ";
sql += " from pim_docm a ";
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 ";
if (cap_store != "" && !user_admin)
{
sql += " inner join userd e on e.rec_type = 'S' and a.doc_store_id = e.user_branch_id and e.user_id ='" + user_id + "'";
}
sql += " " + sWhere;
sql += ") a where rn between {startrow} and {endrow}";
sql += " order by doc_slno ";
sql = sql.Replace("{startrow}", startrow.ToString());
sql = sql.Replace("{endrow}", endrow.ToString());
Dt_List = Con_Oracle.ExecuteQuery(sql);
Con_Oracle.CloseConnection();
foreach (DataRow Dr in Dt_List.Rows)
{
mRow = new pim_docm();
mRow.doc_pkid = Dr["doc_pkid"].ToString();
mRow.doc_slno = Lib.Conv2Integer(Dr["doc_slno"].ToString());
mRow.doc_name = Dr["doc_name"].ToString();
mRow.doc_store_name = Dr["store_name"].ToString();
mRow.doc_file_name = Dr["doc_file_name"].ToString();
mRow.doc_grp_level_name = Dr["grp_level_name"].ToString();
mRow.doc_table_name = Dr["doc_table_name"].ToString();
mRow.rec_created_by = Dr["rec_created_by"].ToString();
mRow.rec_created_date = Dr["rec_created_date"].ToString();
mRow.doc_server_folder = Lib.getPath(ServerImagURL, comp_code, table_name, mRow.doc_slno.ToString(), false);
mRow.doc_thumbnail = Dr["doc_thumbnail"].ToString();
mList.Add(mRow);
}
Dt_List.Rows.Clear();
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
throw Ex;
}
RetData.Add("page_count", page_count);
RetData.Add("page_current", page_current);
RetData.Add("page_rowcount", page_rowcount);
RetData.Add("list", mList);
return(RetData);
}
public IHttpActionResult Save()
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
try
{
string Folder = "";
string slno = "";
imageTools tools = new imageTools();
Boolean isImageFile = false;
Boolean isPdfFile = false;
DataTable Dt_Record = new DataTable();
DataTable Dt_Records = new DataTable();
string uploadError = "";
Dictionary <string, object> SearchData = new Dictionary <string, object>();
Dictionary <string, string> Files2Remove = new Dictionary <string, string>();
var model = HttpContext.Current.Request.Form["record"];
pim_docm record = JsonConvert.DeserializeObject <pim_docm>(model);
var columnList = HttpContext.Current.Request.Form["records"];
tablesd [] records = JsonConvert.DeserializeObject <tablesd[]>(columnList);
string ServerImageURL = Lib.GetSeverImageURL(record._globalvariables.comp_code);
string ServerReportPath = Lib.GetReportPath(record._globalvariables.comp_code);
string ServerImagePath = Lib.GetImagePath(record._globalvariables.comp_code);
using (DocService obj = new DocService())
{
Dt_Record = obj.getDataTableRecord(record.doc_pkid);
RetData = obj.Save(record, records, ServerImageURL);
slno = RetData["slno"].ToString();
}
// first save to report/temp folder
System.Web.HttpFileCollection hfc = System.Web.HttpContext.Current.Request.Files;
string REPID = System.Guid.NewGuid().ToString().ToUpper();
ServerReportPath = Path.Combine(ServerReportPath, System.DateTime.Now.ToString("yyyy-MM-dd"), REPID);
string smallname = "";
for (int iCnt = 0; iCnt <= hfc.Count - 1; iCnt++)
{
isImageFile = false;
isPdfFile = false;
if (record.doc_file_name.ToUpper() == hfc[iCnt].FileName.ToUpper())
{
if (hfc[iCnt].ContentType.ToString().ToUpper().StartsWith("IMAGE"))
{
isImageFile = true;
smallname = "ts.jpg";
}
if (Path.GetExtension(hfc[iCnt].FileName).ToUpper() == ".PDF")
{
isPdfFile = true;
smallname = "ts.jpg";
}
}
SaveFile(ServerReportPath, smallname, hfc[iCnt], isImageFile, isPdfFile, record._globalvariables.comp_code);
}
if (smallname != "")
{
using (DocService obj = new DocService())
{
obj.UpdateDocFileName(record, "doc_thumbnail", smallname);
RetData["thumbnail"] = smallname;
}
}
//then copy to original folder
string sError = "";
if (record.doc_file_name.ToString().Trim().Length > 0)
{
Folder = Lib.getPath(ServerImagePath, record._globalvariables.comp_code, record.doc_table_name, record.doc_slno.ToString(), true);
sError = Lib.CopyFile(Path.Combine(ServerReportPath, record.doc_file_name), Path.Combine(Folder, record.doc_file_name));
if (sError != "")
{
uploadError += "\n" + sError;
using (DocService obj = new DocService())
{
obj.UpdateDocFileName(record, "doc_file_name");
obj.UpdateDocFileName(record, "doc_thumbnail", "");
}
}
if (smallname != "")
{
sError = Lib.CopyFile(Path.Combine(ServerReportPath, smallname), Path.Combine(Folder, smallname));
}
}
foreach (tablesd mRow in records)
{
if (mRow.tabd_col_type == "FILE")
{
Folder = Lib.getPath(ServerImagePath, record._globalvariables.comp_code, record.doc_table_name, record.doc_slno.ToString(), true);
sError = Lib.CopyFile(Path.Combine(ServerReportPath, mRow.tabd_col_value), Path.Combine(Folder, mRow.tabd_col_value));
if (sError != "")
{
uploadError += "\n" + sError;
using (DocService obj = new DocService())
{
obj.UpdateDocFileName(record, "COL_" + mRow.tabd_col_name);
}
}
}
}
RetData.Add("uploaderror", uploadError);
return(Ok(RetData));
}
catch (Exception Ex)
{
return(ResponseMessage(Request.CreateErrorResponse(HttpStatusCode.BadRequest, Ex.Message.ToString())));
}
}
