public static void UploadAttachments(HttpFileCollection attachments, int formId)
{
using (var conn = LykosDB.GetSqlConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.Connection.Open();
for (var i = 0; i < attachments.Count; i++)
{
var attachment = attachments[i];
if (attachment.ContentLength > 0)
{
cmd.Parameters.Clear();
var fs = attachment.InputStream;
var br = new BinaryReader(fs);
byte[] bytes = br.ReadBytes((Int32)fs.Length);
cmd.CommandText =
"INSERT INTO [result].[attachements] ([form_id],[filename],[data],[contenttype]) VALUES " +
"(@formId,@FileName,@Data,@ContentType)";
cmd.Parameters.AddWithValue("@formId", formId);
cmd.Parameters.AddWithValue("@FileName", Path.GetFileName(attachment.FileName));
cmd.Parameters.AddWithValue("@ContentType", attachment.ContentType);
cmd.Parameters.AddWithValue("@Data", bytes);
cmd.ExecuteNonQuery();
br.Close();
fs.Close();
}
}
}
}
}
public static string GetComponentTypes(string formId)
{
var componenet_types = string.Empty;
using (var conn = LykosDB.GetSqlConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.Connection.Open();
cmd.CommandText = "SELECT type.name as name, tref.[attachName] as attachName " +
"FROM [result].[formComponentTypeRef] AS tref " +
"LEFT OUTER JOIN [dbo].[component_type] AS type " +
"ON tref.[component_type_id] = type.id WHERE tref.[form_id] = " + formId;
var reader4 = cmd.ExecuteReader();
var res = string.Empty;
while (reader4.Read())
{
res = !string.IsNullOrEmpty(reader4["attachName"].ToString())
? " - " + reader4["attachName"]
: " no attachment file";
componenet_types += string.Format("{0} {1}; ", reader4["name"], res);
}
}
return(componenet_types);
}
}
public static User GetCurrentUserInfo()
{
User user = new User();
var login = GetCurrentUserLogin();
using (var conn = LykosDB.GetSqlConnection())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("SELECT " +
"us_rl.name as role, usr.fullName as fullName, usr.comment as comment, usr.id FROM users as usr " +
"LEFT OUTER JOIN user_role as us_rl " +
"ON usr.user_role_id = us_rl.id " +
"WHERE usr.login like '{0}'", login);
cmd.Connection.Open();
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.Default);
sdr.Read();
user.role = (string)sdr[0];
user.fullName = (string)sdr[1];
user.comment = (string)sdr[2];
user.user_id = Int32.Parse(sdr[3].ToString());
}
}
return(user);
}
public static void UploadComponentType(int formId, int type_id, string fileName)
{
using (var conn = LykosDB.GetSqlConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.Connection.Open();
cmd.CommandText =
$"INSERT INTO [result].[formComponentTypeRef] VALUES ({formId},{type_id},'{fileName}') ";
cmd.ExecuteNonQuery();
}
}
}
public static void UploadManufacturers(CheckBoxList checkBoxListManufacturer, int formId)
{
using (var conn = LykosDB.GetSqlConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.Connection.Open();
foreach (ListItem listItem in checkBoxListManufacturer.Items)
{
if (!listItem.Selected)
{
continue;
}
cmd.CommandText =
$"INSERT INTO [result].[formManufacturerRef] VALUES ({formId},{Int32.Parse(listItem.Value)}) ";
cmd.ExecuteNonQuery();
}
}
}
}
public static void DeleteForm(int form_id)
{
var user = User.GetCurrentUserInfo();
using (var conn = LykosDB.GetSqlConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.Connection.Open();
cmd.CommandText = string.Format(
"Delete FROM [result].[forms_info] WHERE [form_id] = {0}; " +
"Delete FROM [result].[formManufacturerRef] WHERE [form_id] = {0};" +
"Delete FROM [result].[formComponentTypeRef] WHERE [form_id] = {0};" +
"Delete FROM [result].[attachements] WHERE [form_id] = {0};" +
"Delete FROM [result].[forms] WHERE id = {0};",
form_id);
cmd.ExecuteNonQuery();
}
}
}
public static string GetManufactures(string formId)
{
var manufacturer = string.Empty;
using (var conn = LykosDB.GetSqlConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.Connection.Open();
cmd.CommandText = string.Format("SELECT mref.[id], mref.[manufacture_id] as id, manf.name as name " +
"FROM [result].[formManufacturerRef] AS mref " +
"LEFT OUTER JOIN[dbo].[manufacturer] AS manf " +
"ON mref.[manufacture_id] = manf.ID WHERE mref.[form_id] like '{0}'", formId);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
manufacturer += reader["name"] + "; ";
}
}
return(manufacturer);
}
}
public static int UploadFormInfo(Form form)
{
var formId = 0;
var user = User.GetCurrentUserInfo();
using (var conn = LykosDB.GetSqlConnection())
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Connection.Open();
cmd.CommandText = "INSERT INTO [result].[forms] " +
"([operator_id], [name], [saved_date], [status_id]) output INSERTED.ID values " +
$"({user.user_id},'{form.name}', GETDATE(), 2);";
formId = (int)cmd.ExecuteScalar();
cmd.CommandText = "INSERT INTO [result].[forms_info]" +
"([form_id],[product_name],[pharmaceutical_form],[strength]," +
"[product_code],[marketing_authorithation_holder]," +
"[country_impacted],[change_initiator],[date_of_request]," +
"[type_of_artwork],[type_of_change],[variation_type]," +
"[change_need_submit],[change_need_approve],[packaging_matrials_period]," +
"[sell_out_period],[comments1],[component_type],[change_strategy],[comments2]," +
$"[implementation_date], [cis_id], [submission_date], [approve_date],[packaging_matrials_dedline],[sell_out_dedline]) values ({formId},{form.product_name},{form.pharmaceutical_form},{form.strength},{form.product_code},{form.marketing_authorithation_holder},{form.country_impacted},{form.change_initiator}," +
$"'{string.Format($"{form.date_of_request:yyyy-MM-dd}", form.date_of_request)}',{form.type_of_artwork},{form.type_of_change}," +
$"{form.variation_type},{form.change_need_submit},{form.change_need_approve},{form.packaging_matrials_period},{form.sell_out_period},'{form.comments1}',{form.component_type},{form.change_strategy},'{form.comments2}'," +
$"'{string.Format($"{form.implementation_date:yyyy-MM-dd hh:mm:ss}", form.implementation_date)}', " +
$"{form.cis_id}," +
$"'{ string.Format($"{form.submiss_date:yyyy-MM-dd hh:mm:ss}", String.IsNullOrEmpty(form.submiss_date.ToString()) ? DateTime.MinValue : form.submiss_date)}'," +
$"'{ string.Format($"{form.approve_date:yyyy-MM-dd hh:mm:ss}", String.IsNullOrEmpty(form.approve_date.ToString()) ? DateTime.MinValue : form.approve_date)}'," +
$"'{ string.Format($"{form.packaging_matrials_dedline:yyyy-MM-dd hh:mm:ss}", String.IsNullOrEmpty(form.packaging_matrials_dedline.ToString()) ? DateTime.MinValue : form.packaging_matrials_dedline)}'," +
$"'{ string.Format($"{form.sell_out_dedline:yyyy-MM-dd hh:mm:ss}", String.IsNullOrEmpty(form.sell_out_dedline.ToString()) ? DateTime.MinValue : form.sell_out_dedline)}'" +
$");";
//cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
return(formId);
}
public static FormInfo GetFormInfo(int formId)
{
FormInfo form;
var manufacturer = new ArrayList();
var attachnents = new ArrayList();
var componenet_types = new ArrayList();
using (var conn = LykosDB.GetSqlConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.Connection.Open();
cmd.CommandText = "SELECT " + "[frm].name as [frm_name], " +
"[product_name].name as [product_name], " +
"[pharmaceutical_form].name as [pharmaceutical_form], " +
"[strength].name as [strength], " + "[product_code].name as [product_code], " +
"[marketing_authorithation_holder].name as [marketing_authorithation_holder], " +
"[country_impacted].name as [country_impacted], " +
"[change_initiator].name as [change_initiator], " + "[date_of_request], " +
"[type_of_artwork].name as [type_of_artwork], " +
"[type_of_change].name as [type_of_change], " +
"[variation_type].name as [variation_type], " +
"[change_need_submit].name as [change_need_submit], " +
"[change_need_approve].name as [change_need_approve], " +
"[packaging_matrials_period].name as [packaging_matrials_period], " +
"[sell_out_period].name as [sell_out_period], " + "[comments1], " +
"[component_type].name as [component_type], " +
"[change_strategy].name as [change_strategy], " + "[comments2], " +
"[cis].name as cis," +
"[implementation_date]," + "sts.category_name as status," +
"usrs.fullName as fullName, " +
"usrs.comment as user_comments, " +
"usr_rl.name as rank, " +
"inf.[submission_date] as submission_date, " +
"inf.[approve_date] as approve_date," +
"inf.[packaging_matrials_dedline], inf.[sell_out_dedline]," +
"frm.[saved_date], frm.[send_date], frm.[signed_date] " +
"FROM [result].[forms_info] as inf " +
"LEFT OUTER JOIN product_name as [product_name] " +
"ON inf.[product_name] = [product_name].id " +
"LEFT OUTER JOIN pharmaceutical_form as [pharmaceutical_form] " +
"ON inf.[pharmaceutical_form] = [pharmaceutical_form].id " +
"LEFT OUTER JOIN strength as [strength] " + "ON inf.[strength] = [strength].id " +
"LEFT OUTER JOIN product_code as [product_code] " +
"ON inf.[product_code] = [product_code].id " +
"LEFT OUTER JOIN marketing_authorithation_holder as [marketing_authorithation_holder] " +
"ON inf.[marketing_authorithation_holder] = [marketing_authorithation_holder].id " +
"LEFT OUTER JOIN country_impacted as [country_impacted] " +
"ON inf.[country_impacted] = [country_impacted].id " +
"LEFT OUTER JOIN change_initiator as [change_initiator] " +
"ON inf.[change_initiator] = [change_initiator].id " +
"LEFT OUTER JOIN type_of_artwork as [type_of_artwork] " +
"ON inf.[type_of_artwork] = [type_of_artwork].id " +
"LEFT OUTER JOIN type_of_change as [type_of_change] " +
"ON inf.[type_of_change] = [type_of_change].id " +
"LEFT OUTER JOIN variation_type as [variation_type] " +
"ON inf.[variation_type] = [variation_type].id " +
"LEFT OUTER JOIN change_need_submit as [change_need_submit] " +
"ON inf.[change_need_submit] = [change_need_submit].id " +
"LEFT OUTER JOIN change_need_approve as [change_need_approve] " +
"ON inf.[change_need_approve] = [change_need_approve].id " +
"LEFT OUTER JOIN packaging_matrials_period as [packaging_matrials_period] " +
"ON inf.[packaging_matrials_period] = [packaging_matrials_period].id " +
"LEFT OUTER JOIN sell_out_period as [sell_out_period] " +
"ON inf.[sell_out_period] = [sell_out_period].id " +
"LEFT OUTER JOIN component_type as [component_type] " +
"ON inf.[component_type] = [component_type].id " +
"LEFT OUTER JOIN change_strategy as [change_strategy] " +
"ON inf.[change_strategy] = [change_strategy].id " +
"LEFT OUTER JOIN result.forms as frm " + "ON inf.[form_id] = frm.id " +
"LEFT OUTER JOIN dbo.users as usrs " + "on frm.[operator_id] = usrs.id " +
"LEFT OUTER JOIN dbo.[form_status] as sts " + "on frm.[status_id] = sts.id " +
"LEFT OUTER JOIN dbo.[user_role] as usr_rl " +
"on usr_rl.id = usrs.user_role_id " +
"LEFT OUTER JOIN [info].[cis] as cis " +
"on cis.id = inf.cis_id " + $" WHERE [form_id] = {formId}";
var reader = cmd.ExecuteReader();
reader.Read();
form = new Lykos.FormInfo
{
name = reader["frm_name"].ToString(),
comments1 = reader["comments1"].ToString(),
comments2 = reader["comments2"].ToString(),
product_name = reader["product_name"].ToString(),
pharmaceutical_form = reader["pharmaceutical_form"].ToString(),
strength = reader["strength"].ToString(),
product_code = reader["product_code"].ToString(),
marketing_authorithation_holder = reader["marketing_authorithation_holder"].ToString(),
country_impacted = reader["country_impacted"].ToString(),
change_initiator = reader["change_initiator"].ToString(),
type_of_artwork = reader["type_of_artwork"].ToString(),
type_of_change = reader["type_of_change"].ToString(),
variation_type = reader["variation_type"].ToString(),
change_need_submit = reader["change_need_submit"].ToString(),
change_need_approve = reader["change_need_approve"].ToString(),
packaging_matrials_period = reader["packaging_matrials_period"].ToString(),
sell_out_period = reader["sell_out_period"].ToString(),
component_type = reader["component_type"].ToString(),
change_strategy = reader["change_strategy"].ToString(),
date_of_request = reader["date_of_request"].ToString(),
implementation_date = reader["implementation_date"].ToString(),
form_status = reader["status"].ToString(),
operator_name = reader["fullName"].ToString(),
operator_rank = reader["rank"].ToString(),
user_comments = reader["user_comments"].ToString(),
cis = reader["cis"].ToString(),
submiss_date = reader["submission_date"].ToString(),
approve_date = reader["approve_date"].ToString(),
packaging_matrials_dedline = reader["packaging_matrials_dedline"].ToString(),
sell_out_dedline = reader["sell_out_dedline"].ToString()
};
reader.Close();
cmd.CommandText = "SELECT mref.[id], mref.[manufacture_id] as id, manf.name as name " +
"FROM [result].[formManufacturerRef] AS mref " +
"LEFT OUTER JOIN[dbo].[manufacturer] AS manf " +
"ON mref.[manufacture_id] = manf.ID WHERE mref.[form_id] = " + formId;
var reader2 = cmd.ExecuteReader();
while (reader2.Read())
{
manufacturer.Add(reader2["name"]);
}
reader2.Close();
cmd.CommandText = "SELECT [id], [filename] FROM [result].[attachements] " +
"WHERE [form_id] = " + formId;
var reader3 = cmd.ExecuteReader();
while (reader3.Read())
{
attachnents.Add(reader3["filename"]);
}
reader3.Close();
cmd.CommandText = "SELECT type.name as name, tref.[attachName] as attachName " +
"FROM [result].[formComponentTypeRef] AS tref " +
"LEFT OUTER JOIN [dbo].[component_type] AS type " +
"ON tref.[component_type_id] = type.id WHERE tref.[form_id] = " + formId;
var reader4 = cmd.ExecuteReader();
while (reader4.Read())
{
componenet_types.Add(reader4["name"]);
}
reader4.Close();
}
}
form.manufacturer = manufacturer;
form.attachments = attachnents;
form.component_types = componenet_types;
return(form);
}
