public List <string> GetStokiestInheritanceStatus(string companyCode, string customerCode, string customerEntityType, string region_Code)
{
DataControl.Data _objData = new DataControl.Data();
List <string> lstStockist = new List <string>();
try
{
_objData = new Data();
_objData.OpenConnection(companyCode);
{
string test = "" + EXEC + " " + SP_HDGETSTOCKINHERITACESTATUS + " '" + _objCurInfo.GetCompanyCode() + "','" + customerEntityType + "','" + region_Code + "','" + customerCode + "'";
using (sqldataReader = _objData.ExecuteReader(test))
{
while (sqldataReader.Read())
{
lstStockist.Add(sqldataReader["Is_Inherited"].ToString());
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
_objData.CloseConnection();
}
return(lstStockist);
}
//get history table////
public JsonResult GetAllstock(FormCollection collection)
{
string UserCode = string.Empty;
UserCode = collection["UserCode"];
string InwardDate = string.Empty;
//InwardDate = collection["InwardDate"];
if (!string.IsNullOrEmpty(collection["InwardDate"].ToString()))
{
InwardDate = collection["InwardDate"].ToString().Split('-')[2].ToString() + "-" + collection["InwardDate"].ToString().Split('-')[1].ToString() + "-" + collection["InwardDate"].ToString().Split('-')[0].ToString();
}
//InwardDate = Convert.ToDateTime(collection["InwardDate"].ToString()).ToString("yyyy-MM-dd");
DataControl.Data ds = new DataControl.Data();
DataSet dssub = new DataSet();
ds.OpenConnection(_objCurrentInfo.GetCompanyCode());
{
string StrSQL = "EXEC SP_hdInwardStockValue " + "'" + _objCurrentInfo.GetCompanyCode() + "','" + UserCode + "','" + InwardDate + "'";
dssub = ds.ExecuteDataSet(StrSQL);
ds.CloseConnection();
}
DataTable dt = new DataTable();
dt = dssub.Tables[0];
List <Models.InwardModel> lstMOdel = (from c in dt.AsEnumerable()
select new Models.InwardModel
{
InwardDate = c["Inward Date"].ToString(),
product = c["Product"].ToString(),
count = c["Count"].ToString(),
}).ToList <Models.InwardModel>();
return(Json(lstMOdel, JsonRequestBehavior.AllowGet));
}
public string UserProductMap(FormCollection collection)
{
string str = "";
try
{
string Emp_No = collection["Emp_No"].ToString();
string Product_Ref_Key = collection["Product_Ref_Key"].ToString();
DataControl.Data ds = new DataControl.Data();
ds.OpenConnection(_objCurrentInfo.GetCompanyCode());
{
string strSQL = "";
strSQL = "EXEC SP_HD_InwardBulk_UserProductMap '" + Emp_No + "','" + Product_Ref_Key + "'";
str = ds.ExecuteScalar(strSQL).ToString();
}
}
catch (Exception ex)
{
throw ex;
}
return(str);
}
//sample product method//
public JsonResult GetAllproduct(FormCollection collection)
{
DataControl.Data ds = new DataControl.Data();
try
{
string UserCode = string.Empty;
DataSet dssub = new DataSet();
ds.OpenConnection(objCurrentInfo.GetCompanyCode());
{
string StrSQL = "EXEC USP_hdGetActivityTypeMapping " + "'" + objCurrentInfo.GetCompanyCode() + "'";
dssub = ds.ExecuteDataSet(StrSQL);
}
DataTable dt = new DataTable();
dt = dssub.Tables[0];
List <Models.SalestypeMapping> lstMOdel = (from c in dt.AsEnumerable()
select new Models.SalestypeMapping
{
ProductName = c["Product_Name"].ToString(),
BrandName = c["Brand_Name"].ToString(),
SpecialityName = c["Speciality_Name"].ToString(),
Productcode = c["Product_code"].ToString(),
}).ToList <Models.SalestypeMapping>();
return(Json(lstMOdel, JsonRequestBehavior.AllowGet));
}
finally
{
ds.CloseConnection();
}
}
public string InsertBrand(FormCollection collection)
{
string result = "";
DataControl.Data ds = new DataControl.Data();
try
{
DataSet dsTrans = new DataSet();
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string CompanyCode = objCurInfo.GetCompanyCode();
string BrandName = collection["BrandName"].ToString();
string BrandCode = collection["BrandCode"].ToString();
string Mode = collection["Mode"].ToString();
string strSQL = "EXEC sp_sdInsertBrand '" + CompanyCode + "','" + BrandName + "','" + BrandCode + "','" + Mode + "'";
result = (string)ds.ExecuteScalar(strSQL);
}
return(result);
}
finally
{
ds.CloseConnection();
}
}
//Get mapping product//
public JsonResult GetAllSelectProduct(FormCollection collection)
{
DataControl.Data ds = new DataControl.Data();
try
{
string salesproductCode = string.Empty;
string Result = string.Empty;
salesproductCode = collection["ProductCodeselect"];
DataSet dssub = new DataSet();
ds.OpenConnection(objCurrentInfo.GetCompanyCode());
{
string StrSQL = "EXEC SP_HdGet_MappingProductSelect " + "'" + objCurrentInfo.GetCompanyCode() + "','" + salesproductCode + "'";
dssub = ds.ExecuteDataSet(StrSQL);
}
DataTable dt = new DataTable();
dt = dssub.Tables[0];
List <Models.SalestypeMapping> lstMOdel = (from c in dt.AsEnumerable()
select new Models.SalestypeMapping
{
Productcode = c["Sales_Product_Code"].ToString(),
SalesMappingcode = c["Mapping_Product_Code"].ToString(),
}).ToList <Models.SalestypeMapping>();
return(Json(lstMOdel, JsonRequestBehavior.AllowGet));
}
finally
{
ds.CloseConnection();
}
}
//get user data table//
public JsonResult GetAllInward(FormCollection collection)
{
string UserCode = string.Empty;
UserCode = collection["UserCode"];
string inwardDate = collection["InwardDate"];
DataControl.Data ds = new DataControl.Data();
DataSet dssub = new DataSet();
ds.OpenConnection(_objCurrentInfo.GetCompanyCode());
{
string StrSQL = "EXEC Sp_HdUserProduct_select " + "'" + _objCurrentInfo.GetCompanyCode() + "','" + UserCode + "','" + inwardDate + "'";
dssub = ds.ExecuteDataSet(StrSQL);
ds.CloseConnection();
}
DataTable dt = new DataTable();
DataTable dtBatch = new DataTable();
dt = dssub.Tables[0];
dtBatch = dssub.Tables[1];
List <Models.InwardModel> lstMOdel = (from c in dt.AsEnumerable()
select new Models.InwardModel
{
ProductTypeName = c["Product_Type_Name"].ToString(),
ProductName = c["Product_Name"].ToString(),
CurrentStock = c["Current_Stock"].ToString(),
ProductCode = c["Product_Code"].ToString(),
Effective_Status = Convert.ToInt32(c["Effective_Status"].ToString()),
}).ToList <Models.InwardModel>();
List <Models.InwardBatch> lstBatch = (from c in dtBatch.AsEnumerable()
select new Models.InwardBatch
{
UPB_ID = Convert.ToInt32(c["UPB_ID"].ToString()),
Product_Code = c["Product_Code"].ToString(),
Batch_Number = c["Batch_Number"].ToString(),
Effective_From = c["Effective_From"].ToString(),
Effective_To = c["Effective_To"].ToString(),
Expiry_Date = c["Expiry_Date"].ToString(),
Effective_Status = Convert.ToInt32(c["Effective_Status"].ToString()),
Batch_Status = Convert.ToInt32(c["Batch_Status"].ToString()),
Current_Stock = Convert.ToInt32(c["Current_Stock"].ToString()),
}).ToList <Models.InwardBatch>();
return(Json(new { Product = lstMOdel, Batch = lstBatch }, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// Get the selected region details
/// </summary>
public JsonResult GetRegionDetails(FormCollection collection)
{
try
{
DataSet ds = new DataSet();
DataControl.Data _objData = new DataControl.Data();
_objData.OpenConnection(_objcurrentInfo.GetCompanyCode());
{
ds = _objData.ExecuteDataSet("EXEC SP_hdGetSelectedRegion '" + _objcurrentInfo.GetCompanyCode() + "','" + collection["RegionCode"].ToString() + "'");
}
DataControl.JSONConverter json = new DataControl.JSONConverter();
return(Json(json.Serialize(ds), JsonRequestBehavior.AllowGet));
}
finally
{
_objData.CloseConnection();
}
}
/// <summary>
/// Get region master details for tree generation
/// </summary>
public JsonResult GetRegionTreeDetails()
{
try
{
DataSet ds = new DataSet();
DataControl.Data _objData = new DataControl.Data();
_objData.OpenConnection(_objcurrentInfo.GetCompanyCode());
{
ds = _objData.ExecuteDataSet("exec SP_hdGetRegionTreeDetails '" + _objcurrentInfo.GetCompanyCode() + "'");
}
DataControl.JSONConverter json = new DataControl.JSONConverter();
return(Json(json.Serialize(ds), JsonRequestBehavior.AllowGet));
}
finally
{
_objData.CloseConnection();
}
}
public JsonResult GetAllCompetitoractive(FormCollection collection)
{
DataControl.Data ds = new DataControl.Data();
try
{
DataSet dsTrans = new DataSet();
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string strSQL = "EXEC sp_sd_GetAllCompactive '" + objCurInfo.GetCompanyCode() + "'";
dsTrans = ds.ExecuteDataSet(strSQL);
}
DataControl.JSONConverter json = new DataControl.JSONConverter();
return(Json(json.Serialize(dsTrans), JsonRequestBehavior.AllowGet));
}
finally
{
ds.CloseConnection();
}
}
public JsonResult GetProductGroups()
{
DataControl.Data ds = new DataControl.Data();
try
{
DataSet dsGroup = new DataSet();
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string strSQL = "EXEC sp_sdGetProductGroup '" + objCurInfo.GetCompanyCode() + "'";
dsGroup = ds.ExecuteDataSet(strSQL);
}
DataControl.JSONConverter json = new DataControl.JSONConverter();
return(Json(json.Serialize(dsGroup), JsonRequestBehavior.AllowGet));
}
finally
{
ds.CloseConnection();
}
}
public JsonResult GetProductBatches(string ProductCode)
{
DataControl.Data ds = new DataControl.Data();
try
{
DataSet dsTrans = new DataSet();
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string strSQL = "EXEC SP_HD_GET_ProductBatches '" + ProductCode + "'";
dsTrans = ds.ExecuteDataSet(strSQL);
}
DataControl.JSONConverter json = new DataControl.JSONConverter();
return(Json(json.Serialize(dsTrans), JsonRequestBehavior.AllowGet));
}
finally
{
ds.CloseConnection();
}
}
public string InsertSpeciality(FormCollection collection)
{
DataControl.Data ds = new DataControl.Data();
try
{
string result = "";
DataSet dsTrans = new DataSet();
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string strSQL = "EXEC sp_sdInsertSpeciality '" + objCurInfo.GetCompanyCode() + "','" + collection["SpecialityName"].ToString() + "','" + collection["SpecialityCount"].ToString() + "','" + collection["SpecialityCode"].ToString() + "','" + collection["Mode"].ToString() + "'";
result = (string)ds.ExecuteScalar(strSQL);
}
return(result);
}
finally
{
ds.CloseConnection();
}
}
//get product details///
public JsonResult GetAllInwardHistory(FormCollection collection)
{
string UserCode = string.Empty;
string ProductCode = string.Empty;
UserCode = collection["UserCode"];
ProductCode = collection["ProductCode"];
DataControl.Data ds = new DataControl.Data();
DataSet dssub = new DataSet();
ds.OpenConnection(_objCurrentInfo.GetCompanyCode());
{
string StrSQL = "EXEC Sp_HdInwardHistory_Details " + "'" + _objCurrentInfo.GetCompanyCode() + "','" + UserCode + "','" + ProductCode + "'";
dssub = ds.ExecuteDataSet(StrSQL);
ds.CloseConnection();
}
DataTable dt = new DataTable();
DataTable dtBatch = new DataTable();
dt = dssub.Tables[0];
dtBatch = dssub.Tables[1];
List <Models.InwardModel> lstMOdel = (from c in dt.AsEnumerable()
select new Models.InwardModel
{
Username = c["User Name"].ToString(),
ProductName = c["Product Name"].ToString(),
InwardDate = c["Inward Date"].ToString(),
InwardQuantity = c["Inward Quantity"].ToString(),
ThenStock = c["Then Stock"].ToString(),
}).ToList <Models.InwardModel>();
List <Models.InwardBatch> lstBatch = (from c in dtBatch.AsEnumerable()
select new Models.InwardBatch
{
Inward_Date = c["Inward Date"].ToString(),
Batch_Number = c["Batch_Number"].ToString(),
Inward_Stock = Convert.ToInt32(c["Inward Quantity"].ToString()),
Current_Stock = Convert.ToInt32(c["Then Stock"].ToString()),
}).ToList <Models.InwardBatch>();
return(Json(new { Product = lstMOdel, Batch = lstBatch }, JsonRequestBehavior.AllowGet));
}
public int CheckItranProduct(FormCollection collection)
{
DataControl.Data ds = new DataControl.Data();
try
{
int count = 0;
DataSet dsTrans = new DataSet();
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string strSQL = "EXEC sp_sdCheckItranProduct '" + objCurInfo.GetCompanyCode() + "','" + collection["ProductCode"].ToString() + "'";
count = (int)ds.ExecuteScalar(strSQL);
}
return(count);
}
finally
{
ds.CloseConnection();
}
}
public string BatchAutoCreate(FormCollection collection)
{
string ProductCode = collection["ProductCode"].ToString();
string User_Code = objCurInfo.GetUserCode();
DataControl.Data ds = new DataControl.Data();
try
{
string str = "";
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string strSQL = "EXEC SP_HD_ProductBatchMigrate '" + ProductCode + "','" + User_Code + "'";
str = ds.ExecuteScalar(strSQL).ToString();
}
return(str);
}
finally
{
ds.CloseConnection();
}
}
public string ChangeBatchStatus(FormCollection collection)
{
string mappingId = collection["MappingId"].ToString();
string Status = collection["Status"].ToString();
string User_Code = objCurInfo.GetUserCode();
DataControl.Data ds = new DataControl.Data();
try
{
string str = "";
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string strSQL = "EXEC SP_HD_UPD_BatchStatus " + mappingId.ToString() + "," + Status.ToString() + ",'" + User_Code + "'";
str = ds.ExecuteScalar(strSQL).ToString();
}
return(str);
}
finally
{
ds.CloseConnection();
}
}
public string SaveBatch(FormCollection collection)
{
string ProductCode = collection["ProductCode"].ToString();
string BatchNumber = collection["Batch_Number"].ToString();
string ExpiryDate = collection["Expiry_Date"].ToString();
string Effective_From = collection["Effective_From"].ToString();
string Effective_To = collection["Effective_To"].ToString();
string User_Code = objCurInfo.GetUserCode();
string Merge_Qty = collection["MergeQty"].ToString();
int Mode = Convert.ToInt32(collection["Mode"].ToString());
int MappingId = Convert.ToInt32(collection["MappingId"].ToString() == "" ? "0" : collection["MappingId"].ToString());
DataControl.Data ds = new DataControl.Data();
try
{
string str = "";
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string strSQL = "";
if (Mode == 0)
{
strSQL = "EXEC SP_HD_INS_ProductBatch '" + ProductCode + "','" + BatchNumber + "','" + ExpiryDate + "','" + Effective_From + "','" + Effective_To + "','" + User_Code + "','" + Merge_Qty + "'";
}
else if (Mode == 1)
{
strSQL = "EXEC SP_HD_UPD_ProductBatch " + MappingId + ",'" + ExpiryDate + "','" + Effective_To + "'";
}
str = ds.ExecuteScalar(strSQL).ToString();
}
return(str);
}
finally
{
ds.CloseConnection();
}
}
/// <summary>
/// Get dependening data for region master insert or upadte
/// </summary>
public JsonResult GetRegionMasterDetails()
{
try
{
DataSet ds = new DataSet();
DataControl.Data _objData = new DataControl.Data();
_objData.OpenConnection(_objcurrentInfo.GetCompanyCode());
{
string strQry = "exec SP_hd_Region_Type_Select '" + _objcurrentInfo.GetCompanyCode() + "';";
strQry += "exec SP_hdGetChildRegionsForRegionMaster '" + _objcurrentInfo.GetCompanyCode() + "'" + "," + "'" + _objcurrentInfo.GetRegionCode() + "';";
strQry += "exec SP_hdGetRegionClassificationMasterData '" + _objcurrentInfo.GetCompanyCode() + "';";
strQry += "exec SP_hdGetExpenseGroup '" + _objcurrentInfo.GetCompanyCode() + "'";
strQry += "exec SP_HD_CheckAssignRegionName'" + _objcurrentInfo.GetCompanyCode() + "'";
ds = _objData.ExecuteDataSet(strQry);
}
DataControl.JSONConverter json = new DataControl.JSONConverter();
return(Json(json.Serialize(ds), JsonRequestBehavior.AllowGet));
}
finally
{
_objData.CloseConnection();
}
}
public DataSet GetCustomerData(string entity, string tableName, string mode, string regionCode, int pageNum, string pageName)
{
DataControl.Data _objData = new DataControl.Data();
try
{
DataSet dsRes = new DataSet();
DataSet ds = new DataSet();
_objData.OpenConnection();
_objData.OpenConnection(_objCurInfo.GetCompanyCode());
{
StringBuilder strQry = new StringBuilder();
strQry.Append("exec SP_hdGetEntityColumn '" + _objCurInfo.GetCompanyCode() + "','" + entity + "','" + tableName + "'");
strQry.Append("exec SP_hdGetStockiestMaster '" + _objCurInfo.GetCompanyCode() + "','" + mode + "','" + regionCode + "','" + entity + "'");
strQry.Append(" exec SP_hdGetSaleProductsByRegion '" + _objCurInfo.GetCompanyCode() + "','" + regionCode + "'");
dsRes = _objData.ExecuteDataSet(strQry.ToString());
}
return(dsRes);
}
finally
{
_objData.CloseConnection();
}
}
//public string GetRegionTypeDetailtable(DataSet ds)
//{
// try
// {
// string blobUrl = string.Empty;
// StringBuilder sbTableContent = new StringBuilder();
// DataControl.CurrentInfo _objCurrentInfo = new DataControl.CurrentInfo();
// StringBuilder sb = new StringBuilder();
//if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
//{
// sbTableContent.Append("<table id='tblRegionsummary' class='table table-striped' >");
// sbTableContent.Append("<thead class='active'>");
// sbTableContent.Append("<tr style='background-color: #428bca'>");
// sbTableContent.Append("<td>Edit</td>");
// sbTableContent.Append("<td>Change Status</td>");
// sbTableContent.Append("<td>Region Type Name</td>");
// sbTableContent.Append("<td>Under Region</td>");
// sbTableContent.Append("<td>Status</td>");
// sbTableContent.Append("</tr>");
// sbTableContent.Append("</thead>");
// sbTableContent.Append("<tbody>");
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// sbTableContent.Append("<tr><td><a href='#' onclick ='fnEdit(\"" + ds.Tables[0].Rows[i]["Region_Type_Code"] + "_" + ds.Tables[0].Rows[i]["Region_Type_Name"] + "_" + ds.Tables[0].Rows[i]["Under_Region_Type"] + "\");'>Edit</a></td>");
// sbTableContent.Append("<input type='hidden' id='EditRegionTypeCode'/>");
// sbTableContent.Append("<td><a href='#' onclick ='fnChangeStatus(\"" + ds.Tables[0].Rows[i]["Region_Type_Code"] + "_" + ds.Tables[0].Rows[i]["Region_Type_Status"] + "\");'>Change Status</a></td>");
// sbTableContent.Append("<td>" + ds.Tables[0].Rows[i]["Region_Type_Name"] + "</td>");
// sbTableContent.Append("<td>" + ds.Tables[0].Rows[i]["UnderRegion"] + "</td>");
// sbTableContent.Append("<td>" + ds.Tables[0].Rows[i]["Status"] + "</td></tr>");
// }
//}
//else
//{
// return sb.Append("No Data Found").ToString();
//}
// return sbTableContent.ToString();
// }
// catch (Exception ex)
// {
// Dictionary<string, string> dicContext = new Dictionary<string, string>(); ;
// DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
// throw new Exception("Sorry an error occurred. Please try again later");
// }
//}
public string InsertRegionType(string regionTypeName, string underRegionCode, string Refkey1, string Refkey2, string RegionTypeCat)
{
try
{
string result = "";
string effectiveFrom = System.DateTime.Now.ToString("yyyy-MM-dd");
string status = "1";
DataControl.BLMaster _objBlmaster = new DataControl.BLMaster();
DataControl.Data _objData = new DataControl.Data();
DataControl.CurrentInfo _objCurrentInfo = new DataControl.CurrentInfo();
string regionTypeCode = _objData.GetMaxCode(_objCurrentInfo.GetCompanyCode(), "tbl_SFA_Region_Type_Master", "Region_Type_Code", "RTC");
int regionInsert = _objBlmaster.InsertRegionType(_objCurrentInfo.GetCompanyCode(), regionTypeCode, regionTypeName, underRegionCode, effectiveFrom, status, _objCurrentInfo.GetUserCode(), effectiveFrom, Refkey1, Refkey2, RegionTypeCat);
result = regionInsert.ToString();
return(result);
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
dicContext.Add("Filter:RegionTypeName", regionTypeName);
dicContext.Add("Filter:UnderRegionCode", underRegionCode);
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
throw new Exception("Sorry an error occurred. Please try again later");
}
}
public string ProductBatchMap(FormCollection collection)
{
string str = "";
try
{
string Product_Ref_Key = collection["Product_Ref_Key"].ToString();
string Batch_Number = collection["Batch_Number"].ToString();
string user_code = _objCurrentInfo.GetUserCode();
DataControl.Data ds = new DataControl.Data();
ds.OpenConnection(_objCurrentInfo.GetCompanyCode());
{
string strSQL = "";
strSQL = "EXEC SP_HD_InwardBulk_ProductBatchMap '" + Product_Ref_Key + "','" + Batch_Number + "','" + user_code + "'";
str = ds.ExecuteScalar(strSQL).ToString();
}
}
catch (Exception ex)
{
throw ex;
}
return(str);
}
public string ChangeProductStatus(FormCollection collection)
{
DataControl.Data ds = new DataControl.Data();
string result = string.Empty;
try
{
DataSet dsTrans = new DataSet();
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string strSQL = "EXEC sp_sdChangeProductStatus '" + objCurInfo.GetCompanyCode() + "','" + collection["ProductCode"].ToString() + "'";
result = (string)ds.ExecuteScalar(strSQL);
}
return(result);
}
catch (Exception ex)
{
return(ex.Message.ToString());
}
finally
{
ds.CloseConnection();
}
}
public JsonResult GetProductAttributes(FormCollection collection)
{
DataControl.Data ds = new DataControl.Data();
try
{
DataSet dsTrans = new DataSet();
ds.OpenConnection(objCurInfo.GetCompanyCode());
{
string strSQL = "EXEC sp_sdGetProductAttributes '" + objCurInfo.GetCompanyCode() + "'";
dsTrans = ds.ExecuteDataSet(strSQL);
}
DataControl.JSONConverter json = new DataControl.JSONConverter();
return(new LargeJsonResult()
{
Data = json.Serialize(dsTrans), MaxJsonLength = int.MaxValue
});
//DataControl.JSONConverter json = new DataControl.JSONConverter();
//return Json(json.Serialize(dsTrans), JsonRequestBehavior.AllowGet);
}
finally
{
ds.CloseConnection();
}
}
//product insert method//
public string productInsert(string ProductCode, string MappingCode, string Src_Product_Type, string Des_Product_Type)
{
DataControl.Data ds = new DataControl.Data();
try
{
string Result = string.Empty;
//string salesproductCode = string.Empty;
//salesproductCode = collection["ProductCode"];
string effectiveFrom = string.Empty;
effectiveFrom = DateTime.Now.ToString("yyyy-MM-dd");
string effectiveTo = string.Empty;
effectiveTo = DateTime.Now.ToString("yyyy-MM-dd");
string createdDate = string.Empty;
createdDate = DateTime.Now.ToString("yyyy-MM-dd");
string createdBy = string.Empty;
string mappingproductCode = string.Empty;
string insertQry = string.Empty;
//string[] arMapping = collection["MappingCode"].ToString().Split('^');
string CompanyCode = objCurrentInfo.GetCompanyCode();
string UserCode = objCurrentInfo.GetUserCode();
DataRow[] rowFilter;
//dsMappingDetails =
DataSet dsMappingDetails = new DataSet();
//ds.OpenConnection(CompanyCode);
//{
// string StrSQL = "EXEC SP_hdGetSalesProductmapping " + "'" + CompanyCode + "','" + ProductCode + "'";
// dsMappingDetails = ds.ExecuteDataSet(StrSQL);
//}
ds.OpenConnection(CompanyCode);
{
string StrSQL = "EXEC Sp_hdSalesProductHistory" + "'" + CompanyCode + "','" + ProductCode + "','" + MappingCode + "','" + UserCode + "','" + Src_Product_Type + "','" + Des_Product_Type + "'";
dsMappingDetails = ds.ExecuteDataSet(StrSQL);
}
//foreach (string mappingCode in arMapping)
//{
// if (!string.IsNullOrEmpty(mappingCode))
// {
// rowFilter = dsMappingDetails.Tables[0].Select("Mapping_Product_Code = '" + mappingCode.Split('|')[0] + "' AND Record_Status = '1'");
// if (rowFilter.Length > 0 && mappingCode.Split('|')[1].Trim().ToUpper() == "F")
// {
// insertQry += "UPDATE tbl_SFA_Sales_Product_Mapping SET Record_Status = '0',Effective_To = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff") + "'";
// insertQry += " WHERE Sales_Product_Code = '" + salesproductCode + "' AND Mapping_Product_Code = '" + mappingCode.Split('|')[0].Trim() + "' AND Company_Code = '" + objCurrentInfo.GetCompanyCode() + "';";
// }
// else if (rowFilter.Length > 0 && mappingCode.Split('|')[1].Trim().ToUpper() == "T")
// {
// insertQry += " WHERE Sales_Product_Code = '" + salesproductCode + "' AND Mapping_Product_Code = '" + mappingCode.Split('|')[0].Trim() + "' AND Company_Code = '" + objCurrentInfo.GetCompanyCode() + "';";
// }
// else
// {
// if (mappingCode.Split('|')[1].Trim() == "T")
// {
// rowFilter = dsMappingDetails.Tables[0].Select("Mapping_Product_Code = '" + mappingCode.Split('|')[0] + "' AND Record_Status = '0'");
// if (rowFilter.Length > 0 && effectiveTo = DateTime.Now.ToString("yyyy-MM-dd"))
// {
// insertQry += "UPDATE tbl_SFA_Sales_Product_Mapping SET Record_Status = '1',Effective_From = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff") + "'";
// insertQry += " WHERE Sales_Product_Code = '" + salesproductCode + "' AND Mapping_Product_Code = '" + mappingCode.Split('|')[0].Trim() + "' AND Company_Code = '" + objCurrentInfo.GetCompanyCode() + "';";
// }
// else
// {
// insertQry += "INSERT INTO tbl_SFA_Sales_Product_Mapping (Company_Code,Sales_Product_Code,Mapping_Product_Code,Effective_From,Created_Date,Created_By,Record_Status)";
// insertQry += "VALUES ('" + objCurrentInfo.GetCompanyCode() + "','" + salesproductCode + "','" + mappingCode.Split('|')[0].Trim() + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff") + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff") + "','" + objCurrentInfo.GetUserCode() + "','1');";
// }
// }
// }
// }
//}
if (!string.IsNullOrEmpty(insertQry))
{
insertQry = insertQry.TrimEnd(';');
}
DataControl.SPData objSpData = new DataControl.SPData();
int returnValue = objSpData.InsertSalesProductMapping(insertQry);
if (returnValue == 1)
{
return("SUCCESS");
}
else
{
return("SORRY. UNABLE TO SAVE DATA");
}
}
catch (Exception ex)
{
ErrorLog.LogError(ex, "productInsert()");
return(ex.Message);
}
finally
{
ds.CloseConnection();
}
}
/// <summary>
/// 1.Add new column(User_Order) in user master
/// 2.Update user order usinf all user xml.
/// 3.rename tbl_SFA_User_Master table as tbl_SFA_User_Master_Old
/// 4.rename tbl_SFA_User_Master_New as tbl_SFA_User_Master
/// </summary>
/// <returns>SUCCESS OR FAILURE</returns>
public string UpdateUser(FormCollection collection)
{
string spResult = "";
try
{
bool result = false;
DataSet ds = new DataSet();
//ds.ReadXml("C:\\Users\\[email protected]\\Desktop\\ALL_User.xml");
string path = Server.MapPath("/XMLFiles/" + _objcurrentInfo.GetCompanyCode() + "/ALL_User.xml");
ds.ReadXml(path);
string strQry = "";
int count = ds.Tables[0].Rows.Count;
strQry += "IF NOT EXISTS(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbl_SFA_User_Master' AND COLUMN_NAME='User_Order') ALTER TABLE tbl_SFA_User_Master ADD User_Order INT ;";
int results = _objSPData.ExecuteStoredProcedure("ExecQuery", strQry, _objcurrentInfo.GetCompanyCode());
if (results == 1)
{
result = true;
}
else
{
result = false;
}
if (result)
{
strQry = "";
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
strQry += "Update tbl_SFA_User_Master set User_Order=" + ds.Tables[0].Rows[i][0].ToString() + " where User_Code='" + ds.Tables[0].Rows[i][2].ToString().Split('_')[0].ToString() + "' and Company_Code='" + _objcurrentInfo.GetCompanyCode() + "';";
}
strQry += "Update tbl_SFA_User_Master set User_Order=" + ds.Tables[0].Rows[count - 1][0].ToString() + " where User_Order is NULL and Company_Code='" + _objcurrentInfo.GetCompanyCode() + "';";
}
DataControl.Data _objData = new DataControl.Data();
if (!string.IsNullOrEmpty(strQry))
{
strQry = strQry.TrimEnd(';');
}
results = _objSPData.ExecuteStoredProcedure("ExecQuery", strQry, _objcurrentInfo.GetCompanyCode());
if (results == 1)
{
result = true;
}
else
{
result = false;
}
if (result)
{
DataControl.SPData objData = new DataControl.SPData();
spResult = objData.UserMigration(_objcurrentInfo.GetCompanyCode());
}
if (spResult.Split(':')[0].ToString().ToUpper() == "SUCCESS")
{
strQry = "SP_RENAME 'tbl_SFA_User_Master','tbl_SFA_User_Master_Old'";
results = _objSPData.ExecuteStoredProcedure("ExecQuery", strQry, _objcurrentInfo.GetCompanyCode());
strQry = "SP_RENAME 'tbl_SFA_User_Master_New','tbl_SFA_User_Master'";
results = _objSPData.ExecuteStoredProcedure("ExecQuery", strQry, _objcurrentInfo.GetCompanyCode());
}
}
}
catch (Exception ex)
{
ErrorLog.LogError(ex, "UpdateUser()");
string exec = ex.Message;
}
return(spResult);
}
//save functionality//
public string save(FormCollection collection)
{
string Result = string.Empty;
string UserCode = string.Empty;
string SelectDate = string.Empty;
string Content = string.Empty;
string UserinwardCode = string.Empty;
string UserinwardDetail = string.Empty, maxCode = string.Empty;
UserCode = collection["UserCode"];
SelectDate = collection["SelectDate"];
DataControl.Data objAppData = new DataControl.Data();
string queryMax = objAppData.getSingleValue(Session["Comp_Code"].ToString(), "SELECT CONVERT(VARCHAR,MAX(CAST(SUBSTRING(User_Inward_Code,4,LEN(User_Inward_Code)) AS INT))) FROM tbl_SFA_Inward_Stock");
maxCode = getMaxCode(Convert.ToInt64(queryMax), "UIC");
string userMaxCode = objAppData.getSingleValue(Session["Comp_Code"].ToString(), "SELECT CONVERT(VARCHAR,MAX(CAST(SUBSTRING(User_Inward_Detail,4,LEN(User_Inward_Detail)) AS INT))) FROM tbl_SFA_Inward_Details");
UserinwardCode = maxCode;
string[] strArr = collection["Content"].Split('^');
string ProductCode = "";
string InwardStock = "";
string BatchNumber = "";
string strSQL = "";
string strSqlInward = "";
string userInwardCode = "";
string Remarks = "";
//string productDetails = "";
DataControl.Data ds = new DataControl.Data();
ds.OpenConnection(_objCurrentInfo.GetCompanyCode());
{
strSQL = "EXEC Sp_hdInwardStock_Insert " + "'" + _objCurrentInfo.GetCompanyCode() + "','" + UserCode + "','" + SelectDate + "'";
userInwardCode = ds.ExecuteScalar(strSQL).ToString();
ds.CloseConnection();
}
if (strArr.Length > 0)
{
for (int i = 0; i < strArr.Length; i++)
{
if (strArr[i] != string.Empty)
{
//userMaxCode = (Convert.ToInt64(userMaxCode) + 1).ToString();
//UserinwardDetail = objAppData.getMaxValues(userMaxCode, "UDC");
ProductCode = strArr[i].Split('_')[0];
BatchNumber = strArr[i].Split('_')[1].Trim();
InwardStock = strArr[i].Split('_')[2];
Remarks = strArr[i].Split('_')[4];
strSqlInward += "EXEC Sp_hdInwardDetails_Insert " + "'" + _objCurrentInfo.GetCompanyCode() + "','" + UserCode + "','" + userInwardCode + "','" + ProductCode + "','" + BatchNumber + "','" + InwardStock + "','" + Remarks + "';";
}
}
}
ds.OpenConnection(_objCurrentInfo.GetCompanyCode());
{
ds.ExecuteNonQuery(strSqlInward);
ds.CloseConnection();
}
return("INSERTED");
}