public bool InsertRegionTypeTree(FormCollection collection)
{
try
{
bool result = true;
string query = "", parentregionTypeCode = "";
int level = 0;
DataSet dsRegionType = new DataSet();
DataRow[] drFilter;
string companyCode = _objcurrentInfo.GetCompanyCode();
dsRegionType = _objSPData.GetRegionTypeMaster(companyCode);
if (dsRegionType.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dsRegionType.Tables[0].Rows)
{
if (dr["Region_Type_Code"].ToString() == dr["Under_Region_Type"].ToString())
{
level++;
parentregionTypeCode = dr["Region_Type_Code"].ToString();
}
else
{
drFilter = dsRegionType.Tables[0].Select("Under_Region_Type='" + parentregionTypeCode + "' AND Region_Type_Code <> '" + parentregionTypeCode + "'");
if (drFilter.Length > 0)
{
parentregionTypeCode = drFilter[0]["Region_Type_Code"].ToString();
level++;
}
}
query += "UPDATE tbl_SFA_Region_Type_Master SET Region_Type_Level ='L" + level + "' ,Region_Type_Order =" + level + " WHERE Region_Type_Code='" + dr["Region_Type_Code"].ToString() + "' AND Region_Type_ID='" + dr["Region_Type_ID"].ToString() + "';";
}
if (!string.IsNullOrEmpty(query))
{
query = query.TrimEnd(';');
}
int results = _objSPData.ExecuteStoredProcedure("ExecQuery", query, companyCode);
if (results == 1)
{
result = true;
}
else
{
result = false;
}
return(result);
}
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 1.Add new column(Region_Order) in region master
/// 2.Update region order usinf all region xml.
/// 3.rename tbl_SFA_Region_Master table as tbl_SFA_Region_Master_Old
/// 4.rename tbl_SFA_Region_Master_New as tbl_SFA_Region_Master
/// </summary>
/// <returns>SUCCESS OR FAILURE</returns>
public string UpdateRegion(FormCollection collection)
{
string spResult = "";
bool result = false;
try
{
DataSet ds = new DataSet();
//ds.ReadXml("C:\\Users\\[email protected]\\Desktop\\ALL_Region.xml");
//ds.ReadXml("http:\\\\" + collection["Domain"].ToString() + "\\XMLFile\\" + _objcurrentInfo.GetCompanyCode() + "\\ALL_Region.xml");
//"http://" + Session["Return_URL"].ToString().Split('/')[2].ToString() + "/XMLFile/" + objCurInfo.GetCompanyCode() + "/Logo_Client.jpg";
string path = Server.MapPath("/XMLFiles/" + _objcurrentInfo.GetCompanyCode() + "/ALL_Region.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_Region_Master' AND COLUMN_NAME='Region_Order') ALTER TABLE tbl_SFA_Region_Master ADD Region_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_region_Master set Region_Order=" + ds.Tables[0].Rows[i][0].ToString() + " where Region_Code='" + ds.Tables[0].Rows[i][2].ToString().Split('_')[0].ToString() + "' and Company_Code='" + _objcurrentInfo.GetCompanyCode() + "';";
}
strQry += "Update tbl_SFA_region_Master set Region_Order=" + ds.Tables[0].Rows[count - 1][0].ToString() + " where Region_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 = _objSPData.RegionMigration(_objcurrentInfo.GetCompanyCode());
}
if (spResult.Split(':')[0].ToString().ToUpper() == "SUCCESS")
{
strQry = "SP_RENAME 'tbl_SFA_Region_Master','tbl_SFA_Region_Master_Old'";
results = _objSPData.ExecuteStoredProcedure("ExecQuery", strQry, _objcurrentInfo.GetCompanyCode());
strQry = "SP_RENAME 'tbl_SFA_Region_Master_New','tbl_SFA_Region_Master'";
results = _objSPData.ExecuteStoredProcedure("ExecQuery", strQry, _objcurrentInfo.GetCompanyCode());
}
}
}
catch (Exception ex)
{
ErrorLog.LogError(ex, "UpdateRegion()");
string exec = ex.Message;
}
return(spResult);
}
