public IHttpActionResult Save()
{
int islno = 0;
Dictionary <string, object> RetData = new Dictionary <string, object>();
try
{
Dictionary <string, object> SearchData = new Dictionary <string, object>();
var model = HttpContext.Current.Request.Form["record"];
pim_spot record = JsonConvert.DeserializeObject <pim_spot>(model);
using (SpotService obj = new SpotService())
{
RetData = obj.Save(record);
islno = Lib.Conv2Integer(RetData["slno"].ToString());
}
System.Web.HttpFileCollection hfc = System.Web.HttpContext.Current.Request.Files;
for (int iCnt = 0; iCnt <= hfc.Count - 1; iCnt++)
{
Lib.UploadFile(hfc[iCnt], record._globalvariables.comp_code, record.spot_pkid, hfc.Keys[iCnt]);
}
return(Ok(RetData));
}
catch (Exception Ex)
{
return(ResponseMessage(Request.CreateErrorResponse(HttpStatusCode.BadRequest, Ex.Message.ToString())));
}
}
// Recce User
public Dictionary <string, object> GetRecord_recce_user(Dictionary <string, object> SearchData)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
List <pim_spotd> mList = new List <pim_spotd>();
pim_spot mRow = new pim_spot();
string id = SearchData["pkid"].ToString();
string comp_code = SearchData["comp_code"].ToString();
try
{
DataTable Dt_Rec = new DataTable();
DataTable Dt_RecDet = new DataTable();
sql = "select spot_pkid, ";
sql += " spot_recce_id, b.user_name ";
sql += " from pim_spotm a ";
sql += " left join userm b on a.spot_recce_id = b.user_pkid";
sql += " where spot_pkid = '" + id + "'";
Con_Oracle = new DBConnection();
Dt_Rec = Con_Oracle.ExecuteQuery(sql);
Con_Oracle.CloseConnection();
foreach (DataRow Dr in Dt_Rec.Rows)
{
mRow = new pim_spot();
mRow.spot_pkid = Dr["spot_pkid"].ToString();
mRow.spot_recce_id = Dr["spot_recce_id"].ToString();
mRow.spot_recce_name = Dr["user_name"].ToString();
break;
}
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
throw Ex;
}
RetData.Add("record", mRow);
return(RetData);
}
public IHttpActionResult Save_recce_user(pim_spot Record)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
try
{
using (JobService obj = new JobService())
return(Ok(obj.Save_recce_user(Record)));
}
catch (Exception Ex)
{
return(ResponseMessage(Request.CreateErrorResponse(HttpStatusCode.BadRequest, Ex.Message.ToString())));
}
}
public Dictionary <string, object> Save_recce_user(pim_spot Record)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
string ErrorMessage = "";
Boolean retvalue = false;
try
{
Con_Oracle = new DBConnection();
if (ErrorMessage != "")
{
throw new Exception(ErrorMessage);
}
DBRecord Rec = new DBRecord();
Rec.CreateRow("pim_spotm", "EDIT", "spot_pkid", Record.spot_pkid);
Rec.InsertString("spot_recce_id", Record.spot_recce_id);
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);
return(RetData);
}
public string AllValid(pim_spot Record)
{
string str = "";
try
{
/*
*
* sql = "select comp_pkid from (";
* sql += "select comp_pkid from companym a where (a.comp_code = '{CODE}') ";
*
* sql += ") a where comp_pkid <> '{PKID}'";
*
*
* if (Con_Oracle.IsRowExists(sql))
* str = "Code/Name Exists";
*/
}
catch (Exception Ex)
{
str = Ex.Message.ToString();
}
return(str);
}
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> GetRecord(Dictionary <string, object> SearchData)
{
Dictionary <string, object> RetData = new Dictionary <string, object>();
List <pim_spotd> mList = new List <pim_spotd>();
pim_spot mRow = new pim_spot();
pim_spotd mRowd = new pim_spotd();
string id = SearchData["pkid"].ToString();
string comp_code = SearchData["comp_code"].ToString();
string ServerImageUrl = Lib.GetSeverImageURL(comp_code);
string sql2 = "";
try
{
DataTable Dt_Rec = new DataTable();
DataTable Dt_RecDet = new DataTable();
sql = "select spot_pkid, spot_slno, spot_date, ";
sql += " spot_store_id, store.comp_name as spot_store_name, ";
sql += " spot_vendor_id, vendor.comp_name as spot_vendor_name, ";
sql += " spot_job_remarks, spot_store_contact_name, spot_store_contact_tel,am_status ";
sql += " from pim_spotm a ";
sql += " left join approvalm on spot_pkid = am_pkid";
sql += " left join companym store on a.spot_store_id = store.comp_pkid ";
sql += " left join companym vendor on a.spot_vendor_id = vendor.comp_pkid ";
sql += " where spot_pkid = '" + id + "'";
sql2 = "select spotd_pkid, spotd_parent_id, spotd_name ,spotd_slno,spotd_uom, spotd_wd, spotd_ht, ";
sql2 += " spotd_artwork_id, artwork.param_name as spotd_artwork_name, artwork.param_slno as spotd_artwork_slno,artwork.param_file_name as spotd_artwork_file_name,";
sql2 += " spotd_product_id, product.param_name as spotd_product_name, ";
sql2 += " spotd_close_view, spotd_long_view, spotd_final_view, spotd_status ";
sql2 += " from pim_spotd a ";
sql2 += " left join param artwork on a.spotd_artwork_id = artwork.param_pkid ";
sql2 += " left join param product on a.spotd_product_id = product.param_pkid ";
sql2 += " where spotd_parent_id = '" + id + "'";
sql2 += " order by spotd_slno";
Con_Oracle = new DBConnection();
Dt_Rec = Con_Oracle.ExecuteQuery(sql);
Dt_RecDet = Con_Oracle.ExecuteQuery(sql2);
Con_Oracle.CloseConnection();
foreach (DataRow Dr in Dt_Rec.Rows)
{
mRow = new pim_spot();
mRow.spot_pkid = Dr["spot_pkid"].ToString();
if (Dr["spot_date"].Equals(DBNull.Value))
{
mRow.spot_date = "";
}
else
{
mRow.spot_date = Lib.DatetoString(Dr["spot_date"]);
}
mRow.spot_slno = Lib.Conv2Integer(Dr["spot_slno"].ToString());
mRow.spot_store_id = Dr["spot_store_id"].ToString();
mRow.spot_store_name = Dr["spot_store_name"].ToString();
mRow.spot_vendor_id = Dr["spot_vendor_id"].ToString();
mRow.spot_vendor_name = Dr["spot_vendor_name"].ToString();
mRow.spot_job_remarks = Dr["spot_job_remarks"].ToString();
mRow.approved_status = Dr["am_status"].ToString();
mRow.spot_store_contact_name = Dr["spot_store_contact_name"].ToString();
mRow.spot_store_contact_tel = Dr["spot_store_contact_tel"].ToString();
break;
}
foreach (DataRow Dr in Dt_RecDet.Rows)
{
mRowd = new pim_spotd();
mRowd.spotd_pkid = Dr["spotd_pkid"].ToString();
mRowd.spotd_parent_id = Dr["spotd_parent_id"].ToString();
mRowd.spotd_slno = Lib.Conv2Integer(Dr["spotd_slno"].ToString());
mRowd.spotd_name = Dr["spotd_name"].ToString();
mRowd.spotd_uom = Dr["spotd_uom"].ToString();
mRowd.spotd_wd = Lib.Convert2Decimal(Dr["spotd_wd"].ToString());
mRowd.spotd_ht = Lib.Convert2Decimal(Dr["spotd_ht"].ToString());
mRowd.spotd_artwork_id = Dr["spotd_artwork_id"].ToString();
mRowd.spotd_artwork_name = Dr["spotd_artwork_name"].ToString();
mRowd.spotd_artwork_file_name = Dr["spotd_artwork_file_name"].ToString();
mRowd.spotd_product_id = Dr["spotd_product_id"].ToString();
mRowd.spotd_product_name = Dr["spotd_product_name"].ToString();
mRowd.spotd_close_view = Dr["spotd_close_view"].ToString();
mRowd.spotd_long_view = Dr["spotd_long_view"].ToString();
mRowd.spotd_final_view = Dr["spotd_final_view"].ToString();
mRowd.spotd_close_view_file_uploaded = false;
if (Dr["spotd_close_view"].ToString().Length > 0)
{
mRowd.spotd_close_view_file_uploaded = true;
}
mRowd.spotd_long_view_file_uploaded = false;
if (Dr["spotd_long_view"].ToString().Length > 0)
{
mRowd.spotd_long_view_file_uploaded = true;
}
mRowd.spotd_final_view_file_uploaded = false;
if (Dr["spotd_final_view"].ToString().Length > 0)
{
mRowd.spotd_final_view_file_uploaded = true;
}
mRowd.spotd_status = Dr["spotd_status"].ToString();
mRowd.rec_mode = "EDIT";
mList.Add(mRowd);
}
}
catch (Exception Ex)
{
if (Con_Oracle != null)
{
Con_Oracle.CloseConnection();
}
throw Ex;
}
RetData.Add("record", mRow);
RetData.Add("recorddet", mList);
return(RetData);
}
public IDictionary <string, object> List(Dictionary <string, object> SearchData)
{
string sWhere = "";
Dictionary <string, object> RetData = new Dictionary <string, object>();
Con_Oracle = new DBConnection();
List <pim_spot> mList = new List <pim_spot>();
pim_spot mRow;
string searchstring = SearchData["searchstring"].ToString().ToUpper();
string comp_code = SearchData["comp_code"].ToString();
string type = SearchData["type"].ToString();
string user_id = SearchData["user_id"].ToString();
Boolean user_admin = (Boolean)SearchData["user_admin"];
string region_id = SearchData["region_id"].ToString();
string vendor_id = SearchData["vendor_id"].ToString();
string role_name = SearchData["role_name"].ToString();
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;
string sql2 = "";
try
{
sWhere = " where a.rec_company_code ='" + comp_code + "' ";
if (searchstring != "")
{
sWhere += " and (";
sWhere += " comp_name like '%" + searchstring.ToUpper() + "%' ";
sWhere += " )";
}
if (role_name == "SUPER ADMIN")
{
sql2 = "";
}
else if (role_name == "ZONE ADMIN")
{
sWhere += " and store.comp_region_id = '" + region_id + "'";
}
else if (role_name == "SALES EXECUTIVE")
{
sql2 = " inner join userd e on e.rec_type = 'S' and a.spot_store_id = e.user_branch_id and e.user_id ='" + user_id + "'";
}
else if (role_name == "VENDOR")
{
sWhere += " and spot_vendor_id = '" + vendor_id + "'";
}
else if (role_name == "RECCE USER")
{
sWhere += " and spot_vendor_id = '" + vendor_id + "'";
}
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_spotm a ";
sql += " inner join companym store on a.spot_store_id = store.comp_pkid ";
if (!user_admin)
{
sql += sql2;
}
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 spot_pkid, spot_slno, spot_date, a.rec_created_by, a.rec_created_date, ";
sql += " spot_store_id, store.comp_name as spot_store_name, ";
sql += " spot_vendor_id,vendor.comp_name as spot_vendor_name, ";
sql += " region.param_name as spot_region_name, ";
sql += " spot_job_remarks,";
sql += " am_by, am_date, am_status, am_remarks,";
sql += " row_number() over(order by store.comp_name) rn ";
sql += " from pim_spotm a ";
sql += " inner join companym store on a.spot_store_id = store.comp_pkid ";
sql += " left join param region on store.comp_region_id = region.param_pkid ";
sql += " inner join companym vendor on a.spot_vendor_id = vendor.comp_pkid ";
sql += " left join approvalm on spot_pkid = am_pkid ";
if (!user_admin)
{
sql += sql2;
}
sql += " " + sWhere;
sql += ") a where rn between {startrow} and {endrow}";
sql += " order by a.rec_created_date,spot_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_spot();
mRow.spot_pkid = Dr["spot_pkid"].ToString();
mRow.spot_slno = Lib.Conv2Integer(Dr["spot_slno"].ToString());
mRow.spot_date = Lib.DatetoStringDisplayformat(Dr["spot_date"]);
mRow.spot_store_name = Dr["spot_store_name"].ToString();
mRow.spot_vendor_name = Dr["spot_vendor_name"].ToString();
mRow.spot_region_name = Dr["spot_region_name"].ToString();
mRow.spot_job_remarks = Dr["spot_job_remarks"].ToString();
mRow.approved_by = Dr["am_by"].ToString();
mRow.approved_status = Dr["am_status"].ToString();
mRow.approved_remarks = Dr["am_remarks"].ToString();
mRow.approved_date = Lib.DatetoStringDisplayformat(Dr["am_date"]);
mRow.rec_created_by = Dr["rec_created_by"].ToString();
mRow.rec_created_date = Lib.DatetoStringDisplayformat(Dr["rec_created_date"]);
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);
}
