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