public static List <PJVDetail> getPJVDetail(PJVHeader jvh)
{
PJVDetail jvd;
List <PJVDetail> VDetail = new List <PJVDetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select a.RowID,a.DocumentID,a.TemporaryNo, a.TemporaryDate,a.AccountCode,b.Name,a.AmountDebit,a.AmountCredit," +
" a.SLType,a.SLCode,CASE WHEN a.SLType = 'Employee' THEN i.Name ELSE CASE WHEN a.SLType = 'Office' THEN k.Name ELSE j.Name " +
"END END AS SLName ,a.INvTempNo, a.InvTempDate " +
" from PJVDetail a LEFT OUTER JOIN " +
" AccountCode b ON a.AccountCode = b.AccountCode LEFT OUTER JOIN " +
" Employee AS i ON a.SLCode = i.EmployeeID LEFT OUTER JOIN " +
" Customer AS j ON a.SLCode = j.CustomerID LEFT OUTER JOIN " +
" Office AS k ON LTRIM(RTRIM(a.SLCode)) = LTRIM(RTRIM(k.OfficeID)) " +
"where a.DocumentID='" + jvh.DocumentID + "'" +
" and a.TemporaryNo=" + jvh.TemporaryNo +
" and a.TemporaryDate='" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
jvd = new PJVDetail();
jvd.RowID = reader.GetInt32(0);
jvd.DocumentID = reader.GetString(1);
jvd.TemporaryNo = reader.GetInt32(2);
jvd.TemporaryDate = reader.GetDateTime(3).Date;
jvd.AccountCode = reader.GetString(4);
jvd.AccountName = reader.GetString(5);
jvd.AmountDebit = reader.GetDecimal(6);
jvd.AmountCredit = reader.GetDecimal(7);
jvd.SLType = reader.IsDBNull(8) ? "" : reader.GetString(8);
jvd.SLCode = reader.IsDBNull(9) ? "" : reader.GetString(9);
jvd.SLName = reader.IsDBNull(10) ? "" : reader.GetString(10);
jvd.InvTempNo = reader.GetInt32(11);
jvd.InvTempDate = reader.GetDateTime(12).Date;
VDetail.Add(jvd);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Purchase Journal Details");
}
return(VDetail);
}
public static List <PJVDetail> getPJVDetail(PJVHeader jvh)
{
PJVDetail jvd;
List <PJVDetail> VDetail = new List <PJVDetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select RowID,DocumentID,TemporaryNo, TemporaryDate,AccountCode,AccountName,AmountDebit,AmountCredit,SLCode, SLName, SLType " +
" from ViewJournalVoucher " +
"where DocumentID='" + jvh.DocumentID + "'" +
" and TemporaryNo=" + jvh.TemporaryNo +
" and TemporaryDate='" + jvh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
jvd = new PJVDetail();
jvd.RowID = reader.GetInt32(0);
jvd.DocumentID = reader.GetString(1);
jvd.TemporaryNo = reader.GetInt32(2);
jvd.TemporaryDate = reader.GetDateTime(3).Date;
jvd.AccountCode = reader.GetString(4);
jvd.AccountName = reader.GetString(5);
jvd.AmountDebit = reader.GetDecimal(6);
jvd.AmountCredit = reader.GetDecimal(7);
jvd.SLCode = reader.IsDBNull(8)?"":reader.GetString(8);
jvd.SLName = reader.IsDBNull(9) ? "" : reader.GetString(9);
jvd.SLType = reader.IsDBNull(10) ? "" : reader.GetString(10);
VDetail.Add(jvd);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Journal Details");
}
return(VDetail);
}