internal ContractData[] GetUserContracts(string clientRegno)
{
List<ContractData> result = new List<ContractData>();
try
{
using (SqlConnection dbConn = new SqlConnection(connectionString))
{
string qsGetContract = @"SELECT CONTRACT.ID, CONTRACT.CLIENT_ID, coalesce(CONTRACT.START_DATE, CONTRACT.CLIENT_START_DATE), CONTRACT.END_DATE, CONTRACT.AREA_ID, CONTRACT.FACILITY_ID, AREA.NAME,
FACILITY.ADDRESS, AREA.NETWORK_OWNER, CLIENT.NAME AS Expr1, CLIENT.REG_NR, CLIENT.ADDRESS AS Expr2, CLIENT.PHONE,
CLIENT.EMAIL
FROM CONTRACT INNER JOIN
AREA ON CONTRACT.AREA_ID = AREA.ID INNER JOIN
FACILITY ON CONTRACT.FACILITY_ID = FACILITY.ID INNER JOIN
CLIENT ON CLIENT.ID = CONTRACT.CLIENT_ID where CLIENT.REG_NR=@CLIENT_REGNO";
SqlCommand dbCommand = new SqlCommand(qsGetContract, dbConn);
dbCommand.Parameters.AddWithValue("@CLIENT_REGNO", Int64.Parse(clientRegno));
dbConn.Open();
SqlDataReader dbReader = dbCommand.ExecuteReader();
while (dbReader.Read())
{
try
{
ContractData cd = new ContractData();
cd.ContractId = dbReader.GetInt64(0);
cd.ClientId = dbReader.GetInt64(1);
cd.StartDate = (dbReader.GetValue(2) == DBNull.Value) ? DateTime.Now
: dbReader.GetDateTime(2);
cd.EndDate = (dbReader.GetValue(3) == DBNull.Value) ? DateTime.MinValue : dbReader.GetDateTime(3);
cd.AreaId = dbReader.GetInt64(4);
cd.FacilityId = dbReader.GetInt64(5);
cd.AreaName = dbReader.GetString(6);
cd.FacilityAddress = dbReader.GetString(7);
cd.AreaNetworkOwner = dbReader.GetString(8);
cd.ClientName = dbReader.GetString(9);
cd.ClientRegNo = dbReader.GetInt64(10);
cd.ClientAddress = dbReader.GetString(11);
cd.ClientPhone = dbReader.GetString(12);
cd.ClientEmail = dbReader.GetString(13);
result.Add(cd);
}
catch (Exception rowExc)
{
Debug.WriteLine("Error reading contract row: " + rowExc.Message);
}
}
dbReader.Close();
dbCommand.Connection.Close();
}
}
catch (Exception contractDataExc)
{
Debug.WriteLine("Error getting user contracts: " + contractDataExc.Message);
}
return result.ToArray();
}
public ContractData[] Frontend_GetContractsByClientRegno(string clientRegno)
{
Trace.WriteLine("Frontend_GetContractsByClientRegno:" + clientRegno);
ContractData[] result = new ContractData[0];
DataManager dataMan = new DataManager();
result = dataMan.GetUserContracts(clientRegno);
return result;
}
