public static string[] Get_CBudget_WBS_Information(string dbname, string Bid)
{
//string temp = "";
string[] result = new string[4];
//string[] temp_WID = input[0].Split(',');
//int count = 0;
int[] UpperWID = new int[3];
string Select_string = "Select CWID,PriceID from CBudget_WBS where bid=" + Bid;
try
{
using (SqlConnection conn =
new SqlConnection(DBconnection.connect_string(dbname)))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand(Select_string, conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result[0] += reader.GetInt32(0).ToString() + ",";
if (reader.IsDBNull(1))
{
result[1] += "0,";
}
else
{
result[1] += reader.GetInt32(1).ToString() + ",";
}
}
}
}
conn.Close();
}
}
catch (SqlException ex)
{
Console.Write(ex.ToString());
}
return(result);
}
public static string[] Get_Pro_Resource_Information(string dbname, string Bid)
{
string[] result = new string[4];
int[] UpperWID = new int[3];
string Select_string = "Select PriceID,Temp_id from Pro_Resource where bid=" + Bid;
try
{
using (SqlConnection conn =
new SqlConnection(DBconnection.connect_string(dbname)))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand(Select_string, conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result[0] += reader.GetInt32(0).ToString() + ",";
if (reader.IsDBNull(1))
{
result[1] += "0,";
}
else
{
result[1] += reader.GetInt32(1).ToString() + ",";
}
}
}
}
conn.Close();
}
}
catch (SqlException ex)
{
Console.Write(ex.ToString());
}
return(result);
}
public static string[] check_Enum(string dbname, int UID)
{
string temp = "";
//string[] result;
string[] input = getUpperWID_by_UID(dbname, UID);
//string[] temp_WID = input[0].Split(',');
//int count = 0;
int[] UpperWID = new int[3];
string Select_string = "Select EWID,PID,UpperWID from EBudget_WBS where EWID=" + UID;
try
{
using (SqlConnection conn =
new SqlConnection(DBconnection.connect_string(dbname)))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand(Select_string, conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
temp += reader.GetInt32(0).ToString() + ",";
int UpperID = reader.GetInt32(2);
string[] newupperID = getUpperWID_by_UID(dbname, UpperID);
}
}
}
conn.Close();
}
}
catch (SqlException ex)
{
Console.Write(ex.ToString());
}
return(input);
}
public static bool checkPublicID(string dbname, string ID)
{
bool result = false;
//decimal AnaNum = 1.000M;
string SQLString = "select PublicID from UsualWord where PublicID=" + ID;
try
{
using (SqlConnection conn =
new SqlConnection(DBconnection.connect_string(dbname)))
{
conn.Open();
using (SqlCommand SelectList = new SqlCommand(SQLString, conn))
{
//conn.Open();
SqlDataReader SelectList_read = SelectList.ExecuteReader();
//DataTableBox = new DataTable();
//DataTableBox.Load(SelectList_read);
//for (int i = 0; i < DataTableBox.Columns.Count; i++)
//{
// string temp2 = DataTableBox.Columns[i].ColumnName.ToString();
if (SelectList_read.HasRows)
{
result = false;
}
else
{
result = true;
}
}
conn.Close();
}
}
catch (SqlException ex)
{
Console.Write(ex.ToString());
//Log exception
//Display Error message
}
return(result);
}
public string PostContactus(DAL_M_ContactUs vmcontactus)
{
string userid = string.Empty;
if (vmcontactus.UserId != null)
{
userid = vmcontactus.UserId;
}
try
{
DataSet ds = new DataSet("ContactUs");
using (SqlConnection con = DBconnection.Open())
{
SqlCommand sqlComm = new SqlCommand("SubmitContactUsDetails", con);
sqlComm.Parameters.AddWithValue("@Company", vmcontactus.Company);
sqlComm.Parameters.AddWithValue("@FirstName", vmcontactus.FirstName);
sqlComm.Parameters.AddWithValue("@LastName", vmcontactus.LastName);
sqlComm.Parameters.AddWithValue("@Email", vmcontactus.Email);
sqlComm.Parameters.AddWithValue("@Phone", vmcontactus.Phone);
sqlComm.Parameters.AddWithValue("@Category", vmcontactus.Subject);
sqlComm.Parameters.AddWithValue("@Comments", vmcontactus.Message);
sqlComm.Parameters.AddWithValue("@LastUpdatePersonID", userid);
sqlComm.Parameters.AddWithValue("@LastUpdateDateTime", DateTime.Now);
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.ExecuteNonQuery();
con.Close();
}
}
catch (Exception ex)
{
LogManager.log.Error("Error in SubmitContactUsDetails. Message: " + ex.Message);
logw.LogWrite("Error in SubmitContactUsDetails." + ex.Message);
return(null);
}
return("SUCCESS");
}
public static bool Update_order_num(string dbname, string PID)
{
bool result = false;
int[] input = Sort_LayerCode(dbname, PID);
int count = 0;
string update_string = "Update EBudget_WBS SET order_Num = ";
try
{
using (SqlConnection conn =
new SqlConnection(DBconnection.connect_string(dbname)))
{
conn.Open();
for (int i = 0; i < input.Length; i++)
{
count++;
update_string = "Update EBudget_WBS SET order_Num ='" + count.ToString() + "' WHERE EWID='" + input[i].ToString() + "'";
using (SqlCommand cmd =
new SqlCommand(update_string, conn))
{
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
result = true;
}
}
}
conn.Close();
}
}
catch (SqlException ex)
{
Console.Write(ex.ToString());
//Log exception
//Display Error message
}
return(result);
}
public List <DAL_M_Vendor> GetVendorDetailsByName(string vendorNumber)
{
List <DAL_M_Vendor> lst_VM_Vendor = new List <DAL_M_Vendor>();
try
{
DataSet ds = new DataSet("Vendor");
using (SqlConnection con = DBconnection.Open())
{
SqlCommand sqlComm = new SqlCommand("GetLocationsby_vend_cust_id", con);
sqlComm.Parameters.AddWithValue("@VendorNumber", vendorNumber);
sqlComm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlComm;
da.Fill(ds);
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
DAL_M_Vendor v = new DAL_M_Vendor();
v.VendorNumber = ds.Tables[0].Rows[i]["VendorNumber"].ToString();
v.LocationID = ds.Tables[0].Rows[i]["LocationID"].ToString();
v.VendorAddress = ds.Tables[0].Rows[i]["Address"].ToString();
v.RoutingNumber = ds.Tables[0].Rows[i]["BankRountingNumber"].ToString();
v.AcccountNo = ds.Tables[0].Rows[i]["BankAccountNumber"].ToString();
v.AccountType = ds.Tables[0].Rows[i]["DDAccountType"].ToString();
v.RemittanceEmail = ds.Tables[0].Rows[i]["DDNotifyEmail"].ToString();
v.Status = ds.Tables[0].Rows[i]["DDStatus"].ToString();
lst_VM_Vendor.Add(v);
}
con.Close();
}
}
catch (Exception ex)
{
LogManager.log.Error("Error in GetVendorDetailsByName. Message: " + ex.Message);
}
return(lst_VM_Vendor);
}
public static bool checkPublicID(string dbname, string ID)
{
bool result = false;
//decimal AnaNum = 1.000M;
string SQLString = "select PublicID from ConstructionElement where PublicID=" + ID;
try
{
using (SqlConnection conn =
new SqlConnection(DBconnection.connect_string(dbname)))
{
conn.Open();
using (SqlCommand SelectList = new SqlCommand(SQLString, conn))
{
//conn.Open();
SqlDataReader SelectList_read = SelectList.ExecuteReader();
if (SelectList_read.HasRows)
{
result = false;
}
else
{
result = true;
}
}
conn.Close();
}
}
catch (SqlException ex)
{
Console.Write(ex.ToString());
//Log exception
//Display Error message
}
return(result);
}
public Tuple <string, string> SubmitVendor(DAL_M_VendorDD vmvendorDD)
{
try
{
DataTable reqDetails = CreateTable();
if (vmvendorDD.LocationAddressList == null)
{
foreach (string locid in vmvendorDD.LocationIDs)
{
reqDetails.Rows.Add(vmvendorDD.Confirmation, vmvendorDD.Vendorname, locid, 1, "", "", "", "", "");
}
}
else
{
foreach (DAL_M_LocationAddress loc in vmvendorDD.LocationAddressList)
{
reqDetails.Rows.Add(vmvendorDD.Confirmation, vmvendorDD.Vendorname, loc.LocationID, 1, loc.Address1, loc.Address2, loc.City, loc.State, loc.ZipCode);
}
}
DataSet ds = new DataSet("Vendor");
using (SqlConnection con = DBconnection.Open())
{
{
SqlCommand sqlComm = new SqlCommand("SubmitVendorDetails", con);
sqlComm.Parameters.AddWithValue("@VEND_CUST_CD", vmvendorDD.Vendorname);
sqlComm.Parameters.AddWithValue("@ConfirmationNum", vmvendorDD.Confirmation);//vmvendorDD.Confirmation)
sqlComm.Parameters.AddWithValue("@RequestDate", vmvendorDD.SubmitDateTime);
sqlComm.Parameters.AddWithValue("@Status", 5); // 5 pending
sqlComm.Parameters.AddWithValue("@DDNotifyEmail", vmvendorDD.DDNotifyEmail);
sqlComm.Parameters.AddWithValue("@AccountType", vmvendorDD.AccountType);
sqlComm.Parameters.AddWithValue("@NameOnBankAccount", vmvendorDD.NameOnBankAccount);
sqlComm.Parameters.AddWithValue("@AccountNumber", vmvendorDD.BankAccountNumber);
sqlComm.Parameters.AddWithValue("@RoutingNumber", vmvendorDD.BankRoutingNo);
sqlComm.Parameters.AddWithValue("@FinInstName", vmvendorDD.FinancialIns);
sqlComm.Parameters.AddWithValue("@AuthorizedName", vmvendorDD.Signername);
sqlComm.Parameters.AddWithValue("@AuthorizedTitle", vmvendorDD.Signertitle);
sqlComm.Parameters.AddWithValue("@AuthorizedPhone", vmvendorDD.Signerphone);
sqlComm.Parameters.AddWithValue("@AuthorizedPhoneExt", "");
sqlComm.Parameters.AddWithValue("@AuthorizedEmail", vmvendorDD.Signeremail);
sqlComm.Parameters.AddWithValue("@LastUpdateDateTime", vmvendorDD.SubmitDateTime);
sqlComm.Parameters.AddWithValue("@RequestType", vmvendorDD.RequestType);
sqlComm.Parameters.AddWithValue("@TableTypeRequestDetail", reqDetails);
sqlComm.Parameters.AddWithValue("@SubmitFromWhere", vmvendorDD.SubmitFromWhere);
sqlComm.Parameters.AddWithValue("@PayeeName", vmvendorDD.Payeename);
sqlComm.Parameters.AddWithValue("@FirstName", vmvendorDD.FirstName);
sqlComm.Parameters.AddWithValue("@MiddleName", vmvendorDD.MiddleName);
sqlComm.Parameters.AddWithValue("@LastName", vmvendorDD.LastName);
sqlComm.Parameters.AddWithValue("@CompanyName", vmvendorDD.CompanyName);
sqlComm.Parameters.AddWithValue("@AliasDBA", vmvendorDD.AliasDBAName);
sqlComm.Parameters.AddWithValue("@TaxpayerID", vmvendorDD.Ssn);
sqlComm.Parameters.AddWithValue("@CaseNo", vmvendorDD.CaseNo);
sqlComm.Parameters.AddWithValue("@PhoneNumber", vmvendorDD.PhoneNumber);
sqlComm.Parameters.AddWithValue("@DepartmentName", vmvendorDD.DepartmentName);
sqlComm.Parameters.AddWithValue("@DepartmentContactName", vmvendorDD.DepartmentContactName);
sqlComm.Parameters.AddWithValue("@DepartmentEmail", vmvendorDD.DepartmentEmail);
sqlComm.Parameters.AddWithValue("@DepartmentContactNo", vmvendorDD.DepartmentContactNo);
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.ExecuteNonQuery();
}
con.Close();
}
}
catch (Exception ex)
{
LogManager.log.Error("Error in SubmitVendor. Message: " + ex.Message);
logw.LogWrite("Error in Vendor DAL SubmitVendor. FileName: " + vmvendorDD.Confirmation + ". " + ex.Message);
return(null);
}
return(new Tuple <string, string>("SUCCESS", "true"));
}
static string[] Get_SortInformation(string dbname, string PID)
{
string[] result = new string[3];
string select_string = "select CWID, UpperWID, SortNum from CBudget_WBS where PID='" + PID + "' order by CWID";
try
{
using (SqlConnection conn =
new SqlConnection(DBconnection.connect_string(dbname)))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand(select_string, conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result[0] += reader.GetInt32(0).ToString() + ",";
if (reader.IsDBNull(1))
{
result[1] += "0,";
}
else
{
result[1] += reader.GetInt32(1).ToString() + ",";
}
if (reader.IsDBNull(2))
{
result[2] += "0,";
}
else
{
result[2] += reader.GetInt32(2).ToString() + ",";
}
}
}
}
conn.Close();
}
}
catch (SqlException ex)
{
Console.Write(ex.ToString());
//Log exception
//Display Error message
}
if (result[0] != null)
{
if (result[0].Length > 0)
{
for (int i = 0; i < result.Length; i++)
{
result[i] = result[i].Substring(0, result[i].Length - 1);
}
}
}
return(result);
}
//public static int getUpperWID_by_UID(string dbname,int nowUID, int[] UID, int[] UpperUID)
public static string[] getUpperUID_by_UID(string dbname, int nowUID)
{
//string[] result1;
string temp = "";
//int[] result = 0;
string Select_string = "Select UID,BID,UpperUID from Bid_WBS where UpperUID=" + nowUID;
try
{
using (SqlConnection conn =
new SqlConnection(DBconnection.connect_string(dbname)))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand(Select_string, conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (reader.IsDBNull(0))
{
}
else
{
temp += reader.GetInt32(0).ToString() + ",";
}
//for (int i = 0; i < UID.Length; i++)
// {
// if (nowUID == UID[i])
// {
// result = UpperUID[i];
// break;
// }
// }
}
}
}
conn.Close();
}
}
catch (SqlException ex)
{
Console.Write(ex.ToString());
}
if (temp.Length > 0)
{
temp = temp.Substring(0, temp.Length - 1);
}
else
{
temp = "";
}
string[] result1 = temp.Split(',');
string[] nextresult;
if (temp != "")
{
for (int i = 0; i < result1.Length; i++)
{
nextresult = getUpperUID_by_UID(dbname, int.Parse(result1[i]));
}
}
return(result1);
}
public static string[] Get_SubInformation(string dbname, string PID, int UID)
{
//string temp = "";
string[] result = new string[5];
//string[] temp_WID = input[0].Split(',');
//int count = 0;
int[] UpperWID = new int[5];
string Select_string = "Select EWID,UpperWID,SortNum,ENumber,LastENumber from EBudget_WBS where pid=" + PID + " Order by order_Num";
try
{
using (SqlConnection conn =
new SqlConnection(DBconnection.connect_string(dbname)))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand(Select_string, conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result[0] += reader.GetInt32(0).ToString() + ",";
if (reader.IsDBNull(1))
{
result[1] += "0,";
}
else
{
result[1] += reader.GetInt32(1).ToString() + ",";
}
if (reader.IsDBNull(2))
{
result[2] += "0,";
}
else
{
result[2] += reader.GetInt32(2).ToString() + ",";
}
if (reader.IsDBNull(3))
{
result[3] += "0,";
}
else
{
result[3] += reader.GetDecimal(3).ToString() + ",";
}
if (reader.IsDBNull(4))
{
result[4] += "0,";
}
else
{
result[4] += reader.GetDecimal(4).ToString() + ",";
}
}
}
}
conn.Close();
}
}
catch (SqlException ex)
{
Console.Write(ex.ToString());
}
return(result);
}