public static List <Model.mdlDailyMsg> LoadDailyMessage2(Model.mdlParam json)
{
var mdlDailyMsgList = new List <Model.mdlDailyMsg>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date
},
//new SqlParameter() {ParameterName = "@FinishDate", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date.AddDays(1) },
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtDailyMsg = DataFacade.DTSQLCommand(@"
SELECT
a.MessageID,
a.MessageName,
a.MessageDesc,
CONCAT('//img//DailyMessage//', c.ImageBase64) as MessageImg,
a.CreatedBy,
a.Date
FROM (
SELECT DISTINCT
a.MessageID
FROM DailyMessage a
INNER JOIN DailyMessageDetail b ON a.MessageID = b.MessageID
WHERE a.Date <= @Date AND a.EndDate >= @Date AND b.BranchID = @BranchID AND b.EmployeeID = @EmployeeID
) AS qr
INNER JOIN DailyMessage a ON a.MessageID = qr.MessageID
LEFT JOIN CustomerImage c ON c.ImageID = a.MessageImg", sp);
foreach (DataRow row in dtDailyMsg.Rows)
{
var mdlDailyMsg = new Model.mdlDailyMsg();
mdlDailyMsg.MessageID = row["MessageID"].ToString();
mdlDailyMsg.MessageName = row["MessageName"].ToString();
mdlDailyMsg.MessageDesc = row["MessageDesc"].ToString();
mdlDailyMsg.MessageImg = row["MessageImg"].ToString();
mdlDailyMsg.BranchID = "";
mdlDailyMsg.EndDate = "";
mdlDailyMsg.Date = Convert.ToDateTime(row["Date"]).ToString("yyyy-MM-dd hh:mm:ss");
mdlDailyMsg.CreatedBy = row["CreatedBy"].ToString();
mdlDailyMsgList.Add(mdlDailyMsg);
}
return(mdlDailyMsgList);
}
public static List <Model.mdlDailyMsg> GetSearchbySomeBranch(string keyword, DateTime keyworddate, string branchid)
{
branchid = StringFacade.NormalizedBranch(branchid);
var mdlDailyMsgList = new List <Model.mdlDailyMsg>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@ValidDate", SqlDbType = SqlDbType.Date, Value = keyworddate
},
new SqlParameter()
{
ParameterName = "@Keyword", SqlDbType = SqlDbType.NVarChar, Value = "%" + keyword + "%"
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = branchid
}
};
DataTable dtDailyMsg = DataFacade.DTSQLCommand(@"SELECT distinct(a.MessageID)
,a.MessageName
,a.Date
,a.EndDate
,a.CreatedBy
From DailyMessage a
LEFT JOIN DailyMessageDetail b on b.MessageID = a.MessageID
WHERE (a.Date <= @ValidDate AND a.EndDate >= @ValidDate) AND (a.MessageID like @Keyword OR MessageName like @Keyword)
AND a.MessageID IN (SELECT MessageID from DailyMessageDetail where BranchID IN (" + branchid + "))", sp);
foreach (DataRow row in dtDailyMsg.Rows)
{
var mdlDailyMsg = new Model.mdlDailyMsg();
mdlDailyMsg.MessageID = row["MessageID"].ToString();
mdlDailyMsg.MessageName = row["MessageName"].ToString();
mdlDailyMsg.Date = Convert.ToDateTime(row["Date"]).ToString("dd/MM/yyyy");
mdlDailyMsg.EndDate = Convert.ToDateTime(row["EndDate"]).ToString("dd/MM/yyyy");
mdlDailyMsg.CreatedBy = row["CreatedBy"].ToString();
mdlDailyMsgList.Add(mdlDailyMsg);
}
return(mdlDailyMsgList);
}
public static List <Model.mdlDailyMsg> LoadDailyMessage(Model.mdlParam json)
{
var mdlDailyMsgList = new List <Model.mdlDailyMsg>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtDailyMsg = DataFacade.DTSQLCommand(@"SELECT MessageID
,MessageName
,MessageDesc
,MessageImg
,CreatedBy
,Date
From DailyMessage WHERE Date <= @Date AND EndDate >= @Date AND BranchID = @BranchID", sp);
foreach (DataRow row in dtDailyMsg.Rows)
{
var mdlDailyMsg = new Model.mdlDailyMsg();
mdlDailyMsg.MessageID = row["MessageID"].ToString();
mdlDailyMsg.MessageName = row["MessageName"].ToString();
mdlDailyMsg.MessageDesc = row["MessageDesc"].ToString();
mdlDailyMsg.MessageImg = row["MessageImg"].ToString();
mdlDailyMsg.Date = Convert.ToDateTime(row["Date"]).ToString("yyyy-MM-dd");
mdlDailyMsg.CreatedBy = row["CreatedBy"].ToString();
mdlDailyMsgList.Add(mdlDailyMsg);
}
return(mdlDailyMsgList);
}
