public string GetFiles()
{
string FileID = Request["FileID"];
DataTable dt = null;
if (!String.IsNullOrWhiteSpace(FileID))
{
dt = FileHelper.GetFilesData(FileID, null);
}
GridData gd = new GridData();
gd.Page = "0";
gd.DataTable = dt;
return gd.ToJson(new TableFormatString("CreateTime", "{0:yyyy-MM-dd hh:mm}"));
}
//Customer Visit Involved People
public string CustomerVisitInvolvedPeople()
{
string VisitID = Request["VisitID"];
DataTable dt = CustomerVisitManager.GetVisitInvolvedPeopleData(VisitID);
GridData gd = new GridData();
gd.Page = "0";
gd.DataTable = dt;
return gd.ToJson();
}
public string GetTableParams(int ID)
{
string desc = Request["Description"];
string strWhere = "";
List<SqlParameter> lstSearchParams = new List<SqlParameter>();
if (!String.IsNullOrEmpty(desc))
{
strWhere += "AND DisplayName LIKE @DisplayName";
lstSearchParams.Add(new SqlParameter("@DisplayName", "%" + desc + "%"));
}
string strSql = String.Format("SELECT * FROM SCS_TableParams WHERE TableType={0} {1} ORDER BY Sort", ID, strWhere);
DataTable dt = DbHelperSQL.Query(strSql, lstSearchParams.ToArray()).Tables[0];
dt.Columns.Add("Version");
dt.Columns.Add("RecordCount");
dt.Columns.Add("LastUpdateTime");
dt.Columns.Add("LastUpdateBy");
string vop = "";
if (ID == TableParams.TableType_VersionData || ID == TableParams.TableType_MasterData)
{
strSql = "SELECT TOP 1 Version FROM SCM_Version ORDER BY CASE WHEN Status = 'Active' THEN 1 ELSE 2 END, ID DESC";
vop = Convert.ToString(DbHelperSQL.GetSingle(strSql));
}
else if (ID == TableParams.TableType_PriceMaster)
{
strSql = "SELECT TOP 1 Period FROM SCM_Period ORDER BY CASE WHEN Status = 'Active' THEN 1 ELSE 2 END, ID DESC";
vop = Convert.ToString(DbHelperSQL.GetSingle(strSql));
}
SqlParameter vopps = new SqlParameter("@VersionOrPeriod", vop);
DataTable dtLastUpdate = null;
foreach (DataRow dr in dt.Rows)
{
dr["Version"] = vop;
int tableType = ParseHelper.Parse<int>(dr["TableType"]);
if (tableType == TableParams.TableType_MasterData)
{
strSql = String.Format("SELECT COUNT(*) FROM {0}", dr["TableName"]);
dr["RecordCount"] = DbHelperSQL.GetSingle<int>(strSql);
strSql = String.Format(@"SELECT TOP 1 * FROM (
SELECT TOP 1 CreationTime,CreatorName FROM {0}
UNION ALL
SELECT DateTime, UpdateBy FROM SCS_DataLog WHERE TableKey = '{1}' AND DataID IN(SELECT ID FROM {0})
) AS t ORDER BY CreationTime DESC", dr["TableName"], dr["TableKey"]);
dtLastUpdate = DbHelperSQL.Query(strSql).Tables[0];
}
else if (tableType == TableParams.TableType_VersionData)
{
strSql = String.Format("SELECT COUNT(*) FROM {0} WHERE Version=@VersionOrPeriod", dr["TableName"]);
dr["RecordCount"] = DbHelperSQL.GetSingle<int>(strSql, vopps);
strSql = String.Format(@"SELECT TOP 1 * FROM (
SELECT TOP 1 CreationTime,CreatorName FROM {0} WHERE Version=@VersionOrPeriod
UNION ALL
SELECT DateTime, UpdateBy FROM SCS_DataLog WHERE TableKey = '{1}' AND DataID IN(SELECT ID FROM {0} WHERE Version=@VersionOrPeriod)
) AS t ORDER BY CreationTime DESC", dr["TableName"], dr["TableKey"]);
dtLastUpdate = DbHelperSQL.Query(strSql, vopps).Tables[0];
}
else if (tableType == TableParams.TableType_PriceMaster)
{
strSql = String.Format("SELECT COUNT(*) FROM {0} WHERE Period=@VersionOrPeriod AND ExpiryDate > GETDATE()", dr["TableName"]);
dr["RecordCount"] = DbHelperSQL.GetSingle<int>(strSql, vopps);
strSql = String.Format("SELECT TOP 1 EffectiveDate AS CreationTime,CreatorName FROM {0} WHERE Period=@VersionOrPeriod ORDER BY EffectiveDate DESC", dr["TableName"]);
dtLastUpdate = DbHelperSQL.Query(strSql, vopps).Tables[0];
}
if (dtLastUpdate != null && dtLastUpdate.Rows.Count > 0)
{
dr["LastUpdateTime"] = String.Format("{0:yyyy-MM-dd HH:mm:ss}", dtLastUpdate.Rows[0]["CreationTime"]);
dr["LastUpdateBy"] = String.Format("{0}", dtLastUpdate.Rows[0]["CreatorName"]);
}
}
GridData gridData = new GridData();
gridData.Total = dt.Rows.Count;
gridData.Records = dt.Rows.Count;
gridData.DataTable = dt;
gridData.Page = "1";
return gridData.ToJson();
}
public string GetNewsView(string id)
{
string sSql = @"SELECT (select name from Access_User where Uid=PostedBy) as UserName,
(select Customer from SGP_CustomerProfile_Data where id=CustomerId) as Customer,
(SELECT COUNT(ID) FROM SGP_CustomerNews_Vews WHERE NewsId=SGP_CustomerNews.ID) AS SumViews,
(SELECT COUNT(ID) FROM SGP_CustomerNews_Comments WHERE NewsId=SGP_CustomerNews.ID) AS Replies,
* FROM SGP_CustomerNews WHERE ID = @ID";
DataTable dt = DbHelperSQL.Query(sSql, new SqlParameter("@ID", id)).Tables[0];
GridData gd = new GridData();
gd.Page = "0";
gd.DataTable = dt;
return gd.ToJson(new TableFormatString("PostedDate", "{0:yyyy-MM-dd hh:mm}"));
}
//Get Reply Data
public string GetReply()
{
string NewsId = Request["NewsId"];
DataTable dt = null;
if (!String.IsNullOrWhiteSpace(NewsId))
{
dt = CustomerNewsManager.GetReplyData(NewsId);
}
GridData gd = new GridData();
gd.Page = "0";
gd.DataTable = dt;
return gd.ToJson(new TableFormatString("PostedDate", "{0:yyyy-MM-dd hh:mm}"));
}