public Boolean reverseServiceITem(serviceitem sitem)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update ServiceItem set DocumentStatus=" + sitem.documentStatus +
", forwardUser='******'" +
", ForwarderList='" + sitem.ForwarderList + "'" +
" where ServiceItemID='" + sitem.ServiceItemID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "ServiceItem", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean ApproveServiceItem(serviceitem prevsitem, string id)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update ServiceItem set ServiceItemID = '" + id +
"', documentstatus=99" +
", status=1" +
", ApproveUser='******'" +
" where ServiceItemID='" + prevsitem.ServiceItemID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("approve", "ServiceItem", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean validateServiceItem(serviceitem sitem)
{
Boolean status = true;
string utString = "";
try
{
//if (sitem.ServiceItemID.Trim().Length == 0 || sitem.ServiceItemID == null)
//{
// return false;
//}
if (sitem.Name.Trim().Length == 0 || sitem.Name == null)
{
return(false);
}
if (sitem.Group1CodeDescription.Trim().Length == 0 || sitem.Group1CodeDescription == null)
{
return(false);
}
if (sitem.Group2CodeDescription.Trim().Length == 0 || sitem.Group2CodeDescription == null)
{
return(false);
}
if (sitem.Group3CodeDescription.Trim().Length == 0 || sitem.Group3CodeDescription == null)
{
return(false);
}
}
catch (Exception)
{
}
return(status);
}
public Boolean insertServiceItem(serviceitem sitem)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "insert into ServiceItem " +
" (ServiceItemID,Name,Level1GCode,Level2GCode,Level3GCode,Status,DocumentStatus,CreateTime,CreateUser,ForwarderList,HSNCode)" +
"values (" +
"IDENT_CURRENT('ServiceItem')" +
",'" + sitem.Name + "'," +
"'" + sitem.Group1Code + "'," +
"'" + sitem.Group2Code + "'," +
"'" + sitem.Group3Code + "'," +
sitem.status + "," +
sitem.documentStatus + "," +
"GETDATE()" + "," +
"'" + Login.userLoggedIn + "'" +
",'" + sitem.ForwarderList + "','" + sitem.HSNCode + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "ServiceItem", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean updateServiceItem(serviceitem sitem, serviceitem prevsitem)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update ServiceItem set " +
"Name='" + sitem.Name +
"', Level1GCode='" + sitem.Group1Code +
"', Level2GCode='" + sitem.Group2Code +
"', Level3GCode='" + sitem.Group3Code +
"', Status=" + sitem.status +
", ForwarderList='" + sitem.ForwarderList + "'" +
", HSNCode='" + sitem.HSNCode + "'" +
" where ServiceItemID='" + prevsitem.ServiceItemID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "ServiceItem", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public List <serviceitem> getServiceItems()
{
serviceitem sitem;
List <serviceitem> ServiceItems = new List <serviceitem>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select ServiceItemID, Name,Level1Gcode,Level1GDescription, " +
" Level2Gcode,Level2GDescription,Level3Gcode,Level3GDescription, " +
" Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' '), " +
" isnull(CreatorName,' '),isnull(ForwarderName,' '),isnull(ApproverName,' ') " +
" from ViewServiceItem " +
" order by ServiceItemID";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sitem = new serviceitem();
try
{
sitem.ServiceItemID = reader.GetString(0);
sitem.Name = reader.GetString(1);
sitem.Group1CodeDescription = reader.GetString(2);
//sitem.CategoryDescription = reader.GetString(4);
sitem.Group2CodeDescription = reader.GetString(3);
//sitem.GroupDescription = reader.GetString(6);
sitem.Group3CodeDescription = reader.GetString(4);
//sitem.TypeDescription = reader.GetString(8);
sitem.status = reader.GetInt32(5);
sitem.documentStatus = reader.GetInt32(6);
sitem.CreateUser = reader.GetString(7);
sitem.ForwardUser = reader.GetString(8);
sitem.ApproveUser = reader.GetString(9);
sitem.CreateUserName = reader.GetString(10);
sitem.ForwardUserName = reader.GetString(11);
sitem.ApproveUserName = reader.GetString(12);
}
catch (Exception)
{
}
ServiceItems.Add(sitem);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying Service Item Data");
}
return(ServiceItems);
}
public List <serviceitem> getFilteredServiceItems(string userList, int opt)
{
serviceitem sitem;
List <serviceitem> ServiceItems = new List <serviceitem>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query1 = "select ServiceItemID, Name,Level1Gcode,Level1GDescription, " +
" Level2Gcode,Level2GDescription,Level3Gcode,Level3GDescription," +
" Status,DocumentStatus,isnull(CreateUser,' ') CreateUser,isnull(ForwardUser,' ') ForwardUser,isnull(ApproveUser,' ') ApproveUser, " +
" CreatorName,ForwarderName,ApproverName,ForwarderList,HSNCode " +
" from ViewServiceItem " +
" where ((forwarduser='******' and DocumentStatus between 2 and 98) " +
" or (createuser='******' and DocumentStatus=1))" +
" order by ServiceItemID";
string query2 = "select ServiceItemID, Name,Level1Gcode,Level1GDescription, " +
" Level2Gcode,Level2GDescription,Level3Gcode,Level3GDescription," +
" Status,DocumentStatus,isnull(CreateUser,' ') CreateUser,isnull(ForwardUser,' ') ForwardUser,isnull(ApproveUser,' ') ApproveUser, " +
" CreatorName,ForwarderName,ApproverName,ForwarderList,HSNCode " +
" from ViewServiceItem " +
" where ((createuser='******' and DocumentStatus between 2 and 98 ) " +
" or (ForwarderList like '%" + userList + "%' and DocumentStatus between 2 and 98 and ForwardUser <> '" + Login.userLoggedIn + "'))" +
" order by ServiceItemID";
string query3 = "select ServiceItemID, Name,Level1Gcode,Level1GDescription, " +
" Level2Gcode,Level2GDescription,Level3Gcode,Level3GDescription," +
" Status,DocumentStatus,isnull(CreateUser,' ') CreateUser,isnull(ForwardUser,' ') ForwardUser,isnull(ApproveUser,' ') ApproveUser, " +
" CreatorName,ForwarderName,ApproverName,ForwarderList,HSNCode " +
" from ViewServiceItem " +
" where ((createuser='******'" +
" or ForwarderList like '%" + userList + "%'" +
" or approveUser='******')" +
" and DocumentStatus = 99) " +
" order by ServiceItemID";
string query6 = "select ServiceItemID, Name,Level1Gcode,Level1GDescription, " +
" Level2Gcode,Level2GDescription,Level3Gcode,Level3GDescription," +
" Status,DocumentStatus,isnull(CreateUser,' ') CreateUser,isnull(ForwardUser,' ') ForwardUser,isnull(ApproveUser,' ') ApproveUser, " +
" CreatorName,ForwarderName,ApproverName,ForwarderList,HSNCode " +
" from ViewServiceItem " +
" where DocumentStatus = 99" +
" order by Level1Gcode,Level2Gcode,Level3Gcode";
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
case 3:
query = query3;
break;
case 6:
query = query6;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sitem = new serviceitem();
try
{
sitem.ServiceItemID = reader.GetString(0);
sitem.Name = reader.GetString(1);
sitem.Group1Code = reader.GetString(2);
if (!reader.IsDBNull(3))
{
sitem.Group1CodeDescription = reader.GetString(3);
}
else
{
sitem.Group1CodeDescription = "";
}
sitem.Group2Code = reader.GetString(4);
if (!reader.IsDBNull(5))
{
sitem.Group2CodeDescription = reader.GetString(5);
}
else
{
sitem.Group2CodeDescription = "";
}
sitem.Group3Code = reader.GetString(6);
if (!reader.IsDBNull(7))
{
sitem.Group3CodeDescription = reader.GetString(7);
}
else
{
sitem.Group3CodeDescription = "";
}
sitem.status = reader.GetInt32(8);
sitem.documentStatus = reader.GetInt32(9);
sitem.CreateUser = reader.GetString(10);
sitem.ForwardUser = reader.GetString(11);
sitem.ApproveUser = reader.GetString(12);
sitem.CreateUserName = reader.GetString(13);
sitem.ForwardUserName = reader.GetString(14);
sitem.ApproveUserName = reader.GetString(15);
sitem.HSNCode = reader.IsDBNull(17)?"":reader.GetString(17);
if (!reader.IsDBNull(16))
{
sitem.ForwarderList = reader.GetString(16);
}
else
{
sitem.ForwarderList = "";
}
}
catch (Exception ex)
{
}
ServiceItems.Add(sitem);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying Service Item Data");
}
return(ServiceItems);
}
