public ClassInstance(ProjectModel var1, ProjectSelectModel var2, LeaveModel var3)
{
proj = var1;
projselect = var2;
leave = var3;
}
public Dictionary <int, CompanyProjectModel> GetCompanyProjectModels(int companyId, int projectId)
{
int comId = GetCompanyId("Sunnet");
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("SELECT DISTINCT ");
sqlStr.Append("Companys.ComID, ");
sqlStr.Append("Companys.CompanyName, ");
sqlStr.Append("Projects.ProjectID, ");
sqlStr.Append("Projects.Title ");
sqlStr.Append("FROM dbo.Companys AS Companys ");
sqlStr.Append("INNER JOIN dbo.Projects AS Projects ON Projects.CompanyID = Companys.ComID ");
sqlStr.Append("INNER JOIN dbo.TimeSheets AS TS ON TS.ProjectID = Projects.ProjectID ");
sqlStr.Append("WHERE ");
sqlStr.Append("NOT EXISTS (SELECT * FROM dbo.TSInvoiceRelation AS TIR WHERE TIR.TSId = TS.ID) ");
sqlStr.Append("AND Companys.ComID != " + comId + "");
if (companyId > 0 && projectId == 0)
{
sqlStr.AppendFormat("AND Companys.ComID={0} ", companyId);
}
else if (projectId > 0 && companyId == 0)
{
sqlStr.AppendFormat("AND Projects.ProjectID={0} ", projectId);
}
else if (companyId > 0 && projectId > 0)
{
sqlStr.AppendFormat("AND Projects.ProjectID={0} AND Companys.ComID={1} ", projectId, companyId);
}
Database db = DatabaseFactory.CreateDatabase();
Dictionary <int, CompanyProjectModel> dicModels = new Dictionary <int, CompanyProjectModel>();
using (DbCommand dbCommand = db.GetSqlStringCommand(sqlStr.ToString()))
{
try
{
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
int comid = 0;
object obj = new object();
obj = dataReader["ComID"];
if (obj != null && obj != DBNull.Value)
{
comid = (int)obj;
}
if (comid > 0 && !dicModels.ContainsKey(comid))
{
CompanyProjectModel model = new CompanyProjectModel();
model.CompanyId = comid;
model.CompanyName = dataReader["CompanyName"].ToString();
model.Projects = new List <ProjectSelectModel>();
dicModels.Add(comid, model);
}
if (comid > 0)
{
ProjectSelectModel projectModel = new ProjectSelectModel();
obj = dataReader["ProjectID"];
if (obj != null && obj != DBNull.Value)
{
projectModel.ProjectId = (int)obj;
}
projectModel.ProjectTitle = dataReader["Title"].ToString();
dicModels[comid].Projects.Add(projectModel);
}
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
sqlStr,
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(dicModels);
}