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