public string[] createResourcedData(CreateResourceCard createData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
using (Sqlconn)
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "INSERT INTO ResourceCard(Unit, WriteDate, ORGName, ORGItem, Category, AddressZip, AddressCity, AddressOther, ContactName1, " +
"ContacTel1, ContacFax1, ContactEmail1, ContactName2, ContacTel2, ContacFax2, ContactEmail2, ContactName3, ContacTel3, ContacFax3, ContactEmail3, " +
"ReferralUnit, ServiceObject, ServiceTime, ServiceItem, Charge, ApplicationProcedure, ResourceItem, ResourceLinks, CreateFileBy, UpFileBy, UpFileDate ) " +
"VALUES (@Unit, @WriteDate, @ORGName, @ORGItem, @Category, @AddressZip, @AddressCity, @AddressOther, @ContactName1, @ContacTel1, @ContacFax1, " +
"@ContactEmail1, @ContactName2, @ContacTel2, @ContacFax2, @ContactEmail2, @ContactName3, @ContacTel3, @ContacFax3, @ContactEmail3, @ReferralUnit, " +
"@ServiceObject, @ServiceTime, @ServiceItem, @Charge, @ApplicationProcedure, @ResourceItem, @ResourceLinks , @CreateFileBy, @UpFileBy, (getDate()) )";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]);
cmd.Parameters.Add("@WriteDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(createData.fillInDate);
cmd.Parameters.Add("@ORGName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.resourceName);
cmd.Parameters.Add("@ORGItem", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.resourceItem);
cmd.Parameters.Add("@Category", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(createData.resourceType);
cmd.Parameters.Add("@AddressZip", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.addressZip);
cmd.Parameters.Add("@AddressCity", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(createData.addressCity);
cmd.Parameters.Add("@AddressOther", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.address);
cmd.Parameters.Add("@ContactName1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contact_1);
cmd.Parameters.Add("@ContacTel1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactPhone_1);
cmd.Parameters.Add("@ContacFax1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactFax_1);
cmd.Parameters.Add("@ContactEmail1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactEmail_1);
cmd.Parameters.Add("@ContactName2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contact_2);
cmd.Parameters.Add("@ContacTel2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactPhone_2);
cmd.Parameters.Add("@ContacFax2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactFax_2);
cmd.Parameters.Add("@ContactEmail2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactEmail_2);
cmd.Parameters.Add("@ContactName3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contact_3);
cmd.Parameters.Add("@ContacTel3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactPhone_3);
cmd.Parameters.Add("@ContacFax3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactFax_3);
cmd.Parameters.Add("@ContactEmail3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactEmail_3);
cmd.Parameters.Add("@ReferralUnit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(createData.referral);
cmd.Parameters.Add("@ServiceObject", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sObject);
cmd.Parameters.Add("@ServiceTime", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sTime);
cmd.Parameters.Add("@ServiceItem", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sItem);
cmd.Parameters.Add("@Charge", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sExpense);
cmd.Parameters.Add("@ApplicationProcedure", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sProgram);
cmd.Parameters.Add("@ResourceItem", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sInformation);
cmd.Parameters.Add("@ResourceLinks", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sLink);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
if (returnValue[0] != "0")
{
sql = "select IDENT_CURRENT('ResourceCard') AS cID";
cmd = new SqlCommand(sql, Sqlconn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue[1] = dr["cID"].ToString();
}
dr.Close();
}
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public string[] setResourceData(CreateResourceCard createData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
using (Sqlconn)
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "UPDATE ResourceCard SET ORGName=@ORGName, ORGItem=@ORGItem, Category=@Category, AddressZip=@AddressZip, AddressCity=@AddressCity, "+
"AddressOther=@AddressOther, ContactName1=@ContactName1, ContacTel1=@ContacTel1, ContacFax1=@ContacFax1, ContactEmail1=@ContactEmail1, "+
"ContactName2=@ContactName2, ContacTel2=@ContacTel2, ContacFax2=@ContacFax2, ContactEmail2=@ContactEmail2, ContactName3=@ContactName3, "+
"ContacTel3=@ContacTel3, ContacFax3=@ContacFax3, ContactEmail3=@ContactEmail3, ReferralUnit=@ReferralUnit, ServiceObject=@ServiceObject, "+
"ServiceTime=@ServiceTime, ServiceItem=@ServiceItem, Charge=@Charge, ApplicationProcedure=@ApplicationProcedure, ResourceItem=@ResourceItem, "+
"ResourceLinks=@ResourceLinks, UpFileBy=@UpFileBy, UpFileDate=(getDate()) "+
"WHERE ID=@ID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(createData.ID);
cmd.Parameters.Add("@ORGName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.resourceName);
cmd.Parameters.Add("@ORGItem", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.resourceItem);
cmd.Parameters.Add("@Category", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(createData.resourceType);
cmd.Parameters.Add("@AddressZip", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.addressZip);
cmd.Parameters.Add("@AddressCity", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(createData.addressCity);
cmd.Parameters.Add("@AddressOther", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.address);
cmd.Parameters.Add("@ContactName1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contact_1);
cmd.Parameters.Add("@ContacTel1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactPhone_1);
cmd.Parameters.Add("@ContacFax1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactFax_1);
cmd.Parameters.Add("@ContactEmail1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactEmail_1);
cmd.Parameters.Add("@ContactName2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contact_2);
cmd.Parameters.Add("@ContacTel2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactPhone_2);
cmd.Parameters.Add("@ContacFax2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactFax_2);
cmd.Parameters.Add("@ContactEmail2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactEmail_2);
cmd.Parameters.Add("@ContactName3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contact_3);
cmd.Parameters.Add("@ContacTel3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactPhone_3);
cmd.Parameters.Add("@ContacFax3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactFax_3);
cmd.Parameters.Add("@ContactEmail3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.contactEmail_3);
cmd.Parameters.Add("@ReferralUnit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(createData.referral);
cmd.Parameters.Add("@ServiceObject", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sObject);
cmd.Parameters.Add("@ServiceTime", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sTime);
cmd.Parameters.Add("@ServiceItem", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sItem);
cmd.Parameters.Add("@Charge", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sExpense);
cmd.Parameters.Add("@ApplicationProcedure", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sProgram);
cmd.Parameters.Add("@ResourceItem", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sInformation);
cmd.Parameters.Add("@ResourceLinks", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(createData.sLink);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public string[] setResourceDataBase(CreateResourceCard StructData)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[1]) == 1)
{
return SDB.setResourceData(StructData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public CreateResourceCard getResourcedData(string ID)
{
CreateResourceCard returnValue = new CreateResourceCard();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT ResourceCard.*,a.StaffName AS upBy,b.StaffName AS fillInBy FROM ResourceCard " +
"LEFT JOIN StaffDatabase a ON ResourceCard.UpFileBy=a.StaffID " +
"LEFT JOIN StaffDatabase b ON ResourceCard.CreateFileBy=b.StaffID " +
"WHERE ResourceCard.isDeleted=0 AND ResourceCard.ID=@ID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(ID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.ID = dr["ID"].ToString();
returnValue.sUnit = dr["Unit"].ToString();
returnValue.fillInDate = DateTime.Parse(dr["WriteDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.resourceName = dr["ORGName"].ToString();
returnValue.resourceItem = dr["ORGItem"].ToString();
returnValue.resourceType = dr["Category"].ToString();
returnValue.addressZip = dr["AddressZip"].ToString();
returnValue.addressCity = dr["AddressCity"].ToString();
returnValue.address = dr["AddressOther"].ToString();
returnValue.contact_1 = dr["ContactName1"].ToString();
returnValue.contactPhone_1 = dr["ContacTel1"].ToString();
returnValue.contactFax_1 = dr["ContacFax1"].ToString();
returnValue.contactEmail_1 = dr["ContactEmail1"].ToString();
returnValue.contact_2 = dr["ContactName2"].ToString();
returnValue.contactPhone_2 = dr["ContacTel2"].ToString();
returnValue.contactFax_2 = dr["ContacFax2"].ToString();
returnValue.contactEmail_2 = dr["ContactEmail2"].ToString();
returnValue.contact_3 = dr["ContactName3"].ToString();
returnValue.contactPhone_3 = dr["ContacTel3"].ToString();
returnValue.contactFax_3 = dr["ContacFax3"].ToString();
returnValue.contactEmail_3 = dr["ContactEmail3"].ToString();
returnValue.referral = dr["ReferralUnit"].ToString();
returnValue.sObject = dr["ServiceObject"].ToString();
returnValue.sTime = dr["ServiceTime"].ToString();
returnValue.sItem = dr["ServiceItem"].ToString();
returnValue.sExpense = dr["Charge"].ToString();
returnValue.sProgram = dr["ApplicationProcedure"].ToString();
returnValue.sInformation = dr["ResourceItem"].ToString();
returnValue.sLink = dr["ResourceLinks"].ToString();
returnValue.fillInBy = dr["fillInBy"].ToString();
returnValue.upBy = dr["upBy"].ToString();
returnValue.upDate = DateTime.Parse(dr["UpFileDate"].ToString()).ToString("yyyy-MM-dd");
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message;
}
}
return returnValue;
}
public CreateResourceCard getResourcedDataBase(string sID)
{
CaseDataBase SDB = new CaseDataBase();
CreateResourceCard returnValue = new CreateResourceCard();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
returnValue = SDB.getResourcedData(sID);
}
else
{
returnValue.checkNo = _noRole;
returnValue.errorMsg = _errorMsg;
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (returnValue.sUnit != UserFile[2] && int.Parse(SDB._StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
returnValue.checkNo = _getcheckNo;
returnValue.errorMsg = _errorMsg;
}
return returnValue;
}
