public List <ViewFactBeacukai> GetBEACUKAI_ADDEDs(string invoice)
{
var invoices = invoice.Split(",").ToArray();
string connectionString = APIEndpoint.ConnectionString;
string cmdText = "Select a.BCNo, a.BCDate, a.ExpenditureDate, b.Quantity, b.ItemCode, b.ItemName, a.ExpenditureNo FROM BEACUKAI_ADDED a JOIN BEACUKAI_ADDED_DETAIL b on a.BCId = b.BCId WHERE a.ExpenditureNo IN ({0})";
//string command = string.Format(cmdText, inClause);
List <SqlParameter> parameters = new List <SqlParameter>();
for (int i = 0; i < invoices.Length; i++)
{
parameters.Add(new SqlParameter("@inv" + i, invoices[i]));
}
string inClause = string.Join(", ", parameters.Select(s => s.ParameterName));
List <ViewFactBeacukai> data = new List <ViewFactBeacukai>();
if (parameters.Count > 0)
{
string command = string.Format(cmdText, inClause);
using (SqlConnection connection = new SqlConnection(connectionString)) {
connection.Open();
SqlCommand cmd = new SqlCommand(command, connection);
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
using (cmd)
{
cmd.CommandTimeout = (1000 * 60 * 20);
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dataAdapter.Fill(dSet);
foreach (DataRow dataRow in dSet.Tables[0].Rows)
{
ViewFactBeacukai trace = new ViewFactBeacukai
{
BCNo = dataRow["BCNo"].ToString(),
BonDate = Convert.ToDateTime(dataRow["ExpenditureDate"].ToString()),
BCDate = Convert.ToDateTime(dataRow["BCDate"].ToString()),
Quantity = (double)dataRow["Quantity"],
BonNo = dataRow["ExpenditureNo"].ToString(),
ItemCode = dataRow["ItemCode"].ToString()
};
data.Add(trace);
}
}
}
}
return(data);
}
public List <ViewFactBeacukai> GetBEACUKAI_ADDEDbyDate(DateTime?dateFrom, DateTime?dateTo)
{
string connectionString = APIEndpoint.ConnectionString;
string cmdText = String.Format("Select distinct a.BCNo, a.BCDate, b.Quantity, b.ItemCode, b.ItemName, a.ExpenditureNo, a.ExpenditureDate, a.BCType FROM BEACUKAI_ADDED a JOIN BEACUKAI_ADDED_DETAIL b on a.BCId = b.BCId WHERE a.ExpenditureDate BETWEEN '" + dateFrom + "' and '" + dateTo + "' ");
//List<SqlParameter> parameters = new List<SqlParameter>();
//parameters.Add(new SqlParameter("@bcno", bcno));
List <ViewFactBeacukai> data = new List <ViewFactBeacukai>();
//var reader
//if (parameters.Count > 0)
//{
// string command = string.Format(cmdText, inClause);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(cmdText, connection);
//foreach (var parameter in parameters)
//{
// cmd.Parameters.Add(parameter);
//}
using (cmd)
{
cmd.CommandTimeout = (1000 * 60 * 20);
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dataAdapter.Fill(dSet);
foreach (DataRow dataRow in dSet.Tables[0].Rows)
{
ViewFactBeacukai trace = new ViewFactBeacukai
{
BCNo = dataRow["BCNo"].ToString(),
BCDate = Convert.ToDateTime(dataRow["BCDate"].ToString()),
BonDate = Convert.ToDateTime(dataRow["ExpenditureDate"].ToString()),
Quantity = (double)dataRow["Quantity"],
BonNo = dataRow["ExpenditureNo"].ToString(),
ItemCode = dataRow["ItemCode"].ToString(),
BCType = dataRow["BCType"].ToString()
};
data.Add(trace);
}
}
}
//}
return(data);
}
