public int GetDCREntryBasedOnPrivilege(string dcrDate, string flag)
{
int result = 1;
try
{
_objSPData = new SPData();
_objcurrentInfo = new CurrentInfo();
string company_Code = _objcurrentInfo.GetCompanyCode();
string user_Code = _objcurrentInfo.GetUserCode();
if (flag == "FIELD" || flag == "FIELD_RCPA")
{
flag = "F";
}
else if (flag == "ATTENDANCE")
{
flag = "A";
}
else
{
flag = "L";
}
result = _objSPData.GetDCREntryBasedOnPrivilege(company_Code, user_Code, dcrDate, flag);
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public JsonResult CheckTPAvailableForSelectedDCRDates(string DCRDates)
{
try
{
_objSPData = new SPData();
_objcurrentInfo = new CurrentInfo();
string company_Code = _objcurrentInfo.GetCompanyCode();
string user_Code = _objcurrentInfo.GetUserCode();
string region_Code = _objcurrentInfo.GetRegionCode();
List <string> lstDCRDates = _objSPData.CheckTPAvailableForSelectedDCRDates(company_Code, user_Code, region_Code, DCRDates);
// List<string> DCR_Dates = DCRDates.Split('^').ToList();
// DCR_Dates = DCR_Dates.Where(d => d != "").ToList();
// //compare two list and return not matching items using linq
//List<string> lstDCRDatesWithoutPlan = DCR_Dates.Except(lstDCRDates).ToList();
if (lstDCRDates != null)
{
return(Json(lstDCRDates));
}
return(null);
}
catch
{
throw;
}
}
public string ProductGroup(FormCollection collection)
{
// TODO: Add insert logic here
string effFromdate = collection["Effective_From"].ToString();
string effTodate = collection["Effective_To"].ToString();
if (!string.IsNullOrEmpty(effFromdate))
{
effFromdate = effFromdate.Split('/')[2] + "-" + effFromdate.Split('/')[1] + "-" + effFromdate.Split('/')[0];
// effFrom = Convert.ToDateTime(collection["EffectiveFrom"].ToString()).ToString("yyyy-MM-dd");
}
if (!string.IsNullOrEmpty(effTodate))
{
effTodate = effTodate.Split('/')[2] + "-" + effTodate.Split('/')[1] + "-" + effTodate.Split('/')[0];
// effTo = Convert.ToDateTime(collection["EffectiveTo"].ToString()).ToString("yyyy-MM-dd");
}
try
{
DataControl.SPData objData = new DataControl.SPData();
string result = "";
result = objData.InsertProductGroup("sp_sdInsertProductGroup", objCurInfo.GetCompanyCode(), collection["Product_Group"].ToString(),
effFromdate, effTodate, collection["Status"].ToString(), collection["Mode"].ToString(),
collection["Product_Group_Code"].ToString());
return(result);
}
catch (Exception ex)
{
ErrorLog.LogError(ex, "ProductGroup()");
return("");
}
}
public int GetCountforDCRRestrictDate(string DCR_Date)
{
_objSPData = new SPData();
_objcurrentInfo = new CurrentInfo();
string company_Code = _objcurrentInfo.GetCompanyCode();
string user_Code = _objcurrentInfo.GetUserCode();
return(_objSPData.GetCountforDCRRestrictDate(company_Code, user_Code, DCR_Date));
}
/// <summary>
/// <Delete product Group></Delete>
/// </summary>
public string ProductGroupDelete(FormCollection collection)
{
// TODO: Add insert logic here
try
{
DataControl.SPData objData = new DataControl.SPData();
string result = "";
string prodgrpcode = collection["Product_Group_Code"].ToString();
string status = collection["Status"].ToString();
string company_code = objCurInfo.GetCompanyCode();
result = objData.DeleteProductGroup("sp_sdDeleteProductGroup", company_code, prodgrpcode, status);
return(result);
}
catch (Exception ex)
{
ErrorLog.LogError(ex, "ProductGroupDelete()");
return("");
}
}
//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);
}