public static List <SO_Creation_Model> RetrieveItemMaster(SqlConnection connection)
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"SELECT a.idItem,a.idClass,b.Product_Name,a.Item_Number,a.Description
FROM a_Item_Master a INNER JOIN a_Item_Class b ON a.idClass = b.idClass");
var lmodel = new List <SO_Creation_Model>();
connection.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
var oreader = cmd.ExecuteReader();
while (oreader.Read())
{
SO_Creation_Model oModel = new SO_Creation_Model
{
idItem = (int)oreader["idItem"],
idClass = (int)oreader["idClass"],
Product_Name = (string)oreader["Product_Name"],
ItemNumber = (string)oreader["Item_Number"],
Description = (string)oreader["Description"]
};
lmodel.Add(oModel);
}
oreader.Close();
cmd.Dispose();
}
connection.Close();
return(lmodel);
}
public static List <SO_Creation_Model> RetrieveData(SqlConnection connection)
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"SELECT idSODetail
,idSOHeader
,idItem
FROM a_SO_Detail");
var lmodel = new List <SO_Creation_Model>();
connection.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
var oreader = cmd.ExecuteReader();
while (oreader.Read())
{
SO_Creation_Model oModel = new SO_Creation_Model
{
idSODetail = (int)oreader["idSODetail"],
idSOHeader = (int)oreader["idSOHeader"],
idItem = (int)oreader["idItem"]
};
lmodel.Add(oModel);
}
oreader.Close();
cmd.Dispose();
}
connection.Close();
return(lmodel);
}
public static List <SO_Creation_Model> RetrieveSO(SqlConnection connection, string SO_Number, string Salesman, string Access, string SOStatus)
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"SELECT DISTINCT
b.idSOHeader,
c.idCustomer,
b.SO_Number,
b.Order_Date,
b.Due_Date,
b.Salesman,
c.Customer_Name,
b.Customer_PO,
b.Freight_Charges,
b.Other_Charges,
b.idPOHeader,
c.Customer_Code,
b.credit_term,
b.Gross_Amount,
b.Net_Amount,
b.Tax_Amount,
b.idSite,
b.Remarks,
b.currency_code,
b.Final_Discount,
c.Address1,
c.Address2,
c.Address3,
c.Address4,
b.Special_Concession,
c.Company_Name,
b.Pick_Status,
b.SO_Status,
b.Stock_Status,
c.AddressShipping1,
c.AddressShipping2,
c.AddressShipping3,
c.AddressShipping4,
DATEDIFF(day, Order_Date, GETDATE()) AS DaysOpen,
b.CreatedBy
,End_User
,End_User_City
FROM a_SO_Header b
INNER JOIN a_Customer_Details c ON b.idCustomer = c.idCustomer
WHERE b.idSOHeader != 0
");
if (Access == "BCC")
{
sQuery.Append(" AND b.SO_Number LIKE '%" + SO_Number + "%' AND b.Active != 'N' ");
}
else
{
sQuery.Append(" AND b.SO_Number LIKE '%" + SO_Number + "%' AND b.Salesman = (SELECT idUser FROM a_Users WHERE User_Name = '" + Salesman + "') AND b.Active != 'N' ");
}
if (SOStatus != "")
{
sQuery.Append(" AND b.SO_Status = '" + SOStatus + "' ");
}
sQuery.Append("ORDER BY b.SO_Number DESC");
var lmodel = new List <SO_Creation_Model>();
connection.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
var oreader = cmd.ExecuteReader();
while (oreader.Read())
{
SO_Creation_Model oModel = new SO_Creation_Model
{
idSOHeader = (int)oreader["idSOHeader"],
idCustomer = (int)oreader["idCustomer"],
SO_Number = (string)oreader["SO_Number"],
Order_Date = DateTime.Parse(oreader["Order_Date"].ToString()).ToString("dd/MM/yyyy"),
Due_Date = DateTime.Parse(oreader["Due_Date"].ToString()).ToString("dd/MM/yyyy"),
Salesman = oreader["Salesman"].ToString(),
Customer_Name = oreader["Customer_Name"].ToString(),
Customer_PO = oreader["Customer_PO"].ToString(),
Freight_Charges = (decimal)oreader["Freight_Charges"],
Other_Charges = (decimal)oreader["Other_Charges"],
idPOHeader = oreader["idPOHeader"].ToString(),
Customer_Code = oreader["Customer_Code"].ToString(),
credit_term = oreader["credit_term"].ToString(),
Gross_Amount = (decimal)oreader["Gross_Amount"],
Net_Amount = (decimal)oreader["Net_Amount"],
Tax_Amount = (decimal)oreader["Tax_Amount"],
idSite = oreader["idSite"].ToString(),
Remarks = oreader["Remarks"].ToString(),
Currency_Code = oreader["Currency_Code"].ToString(),
Final_Discount = (decimal)oreader["Final_Discount"],
Address1 = oreader["Address1"].ToString(),
Address2 = oreader["Address2"].ToString(),
Address3 = oreader["Address3"].ToString(),
Address4 = oreader["Address4"].ToString(),
Special_Concession = oreader["Special_Concession"].ToString(),
Company_Name = oreader["Company_Name"].ToString(),
SO_Status = oreader["SO_Status"].ToString(),
Stock_Status = oreader["Stock_Status"].ToString(),
AddressShipping1 = oreader["AddressShipping1"].ToString(),
AddressShipping2 = oreader["AddressShipping2"].ToString(),
AddressShipping3 = oreader["AddressShipping3"].ToString(),
AddressShipping4 = oreader["AddressShipping4"].ToString(),
DaysOpen = (int)oreader["DaysOpen"],
CreatedBy = oreader["CreatedBy"].ToString(),
Pick_Status = oreader["Pick_Status"].ToString(),
End_User = oreader["End_User"].ToString(),
End_User_City = oreader["End_User_City"].ToString()
};
lmodel.Add(oModel);
}
oreader.Close();
cmd.Dispose();
}
connection.Close();
return(lmodel);
}
