public void Synchronize_vehicleCategory(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
//check for Vehicle Category Defect updates
if (test.vehicleCategory != null)
{
handHeldService.VehicleCategory1[] WebServiceDefects = new VSDApp.handHeldService.VehicleCategory1[test.vehicleCategory.Length];
WebServiceDefects = test.vehicleCategory;
foreach (handHeldService.VehicleCategory1 x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT Vehicle_Category_ID FROM VSD_Vehicle_Category ORDER BY Vehicle_Category_ID DESC";
command = new SqlCeCommand(sqlQuery, con);
SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable);
int vInfoID = -1;
if (res.HasRows)
{
res.Read();
vInfoID = Convert.ToInt32(res["VEHICLE_CATEGORY_ID"]) + 1;
res.Close();
}
sqlQuery = "Select vehicle_category_id from vsd_vehicle_category where category_code = '" + x.code + "'";
command = new SqlCeCommand(sqlQuery, con);
SqlCeDataReader tempReader = command.ExecuteReader();
if (tempReader.Read())
{
vInfoID = Int32.Parse(tempReader[0].ToString());
}
if (vInfoID == -1)
{
vInfoID = 1;
}
sqlQuery = "Select Vehicle_Category_ID from vsd_vehicle_category where category_code ='" + x.parentCategoryCode + "'";
command = new SqlCeCommand(sqlQuery, con);
tempReader = command.ExecuteReader();
string parentCatCode;
if (tempReader.Read())
{
parentCatCode = tempReader[0].ToString();
}
else
{
parentCatCode = null;
}
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Vehicle_Category"
+ " WHERE (Vehicle_Category_ID = @Vehicle_Category_ID)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@Vehicle_Category_ID", SqlDbType.Int).Value = vInfoID;
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Vehicle_Category ( Vehicle_Category_ID,Parent_Vehicle_Category_ID,Record_Type,Category_Code,Category_Name,Category_Name_A,Category_Desc,Category_Desc_A,IsDisabled ) VALUES ( " + vInfoID.ToString() + "," + (parentCatCode != null ? "'" + parentCatCode + "'" : "NULL") + "," + (x.recordType != null ? "'" + x.recordType + "'" : "NULL") + "," + (x.code != null ? "'" + x.code + "'" : "NULL") + "," + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + "," + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + "," + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " )";
}
else
{
sqlQuery = " UPDATE VSD_Vehicle_Category SET Vehicle_Category_ID = " + vInfoID.ToString() + ",Parent_Vehicle_Category_ID = " + (parentCatCode != null ? "'" + parentCatCode + "'" : "NULL") + ",Record_Type = " + (x.recordType != null ? "'" + x.recordType + "'" : "NULL") + ",Category_Code = " + (x.code != null ? "'" + x.code + "'" : "NULL") + ",Category_Name = " + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + ",Category_Name_A = " + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + ",Category_Desc = " + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + ",Category_Desc_A = " + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Vehicle_Category_ID =" + vInfoID.ToString();
}
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@Vehicle_Category_ID", SqlDbType.Int).Value = vInfoID;
if (x.parentCategoryCode != null)
{
command.Parameters.Add("@Parent_Vehicle_Category_ID", Int32.Parse(x.parentCategoryCode));
}
else
{
command.Parameters.Add("@Parent_Vehicle_Category_ID", DBNull.Value);
}
command.Parameters.Add("@Record_Type", SqlDbType.NChar, 25).Value = x.recordType;
command.Parameters.Add("@Category_Code", SqlDbType.NChar, 25).Value = x.code;
command.Parameters.Add("@Category_Name", SqlDbType.NChar, 100).Value = x.name;
command.Parameters.Add("@Category_Name_A", SqlDbType.NChar, 100).Value = x.nameArabic;
command.Parameters.Add("@Category_Desc", SqlDbType.NChar, 250).Value = x.description;
command.Parameters.Add("@Category_Desc_A", SqlDbType.NChar, 250).Value = x.descriptionArabic;
command.Parameters.Add("@IsDisabled", SqlDbType.NChar, 1).Value = (x.isDisabled.ToString())[0];
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
}
}
public void Synchronize_vehicleCatDefectSeverity(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
///////////////////////////
//check for Vehicle Category Defect updates
if (test.vehicleCatDefectSeverity != null)
{
handHeldService.VehicleCategoryDefectSeverity[] WebServiceDefects = new VSDApp.handHeldService.VehicleCategoryDefectSeverity[test.vehicleCatDefectSeverity.Length];
WebServiceDefects = test.vehicleCatDefectSeverity;
foreach (handHeldService.VehicleCategoryDefectSeverity x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Veh_Cat_Defect"
+ " WHERE (Veh_Cat_Defect_id = @VehCatDefectID)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@VehCatDefectID", SqlDbType.Int).Value = x.id;
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Veh_Cat_Defect (Veh_Cat_Defect_ID,Defect_ID,Severity_Level_ID,Vehicle_category_ID,IsDisabled) VALUES (" + x.id + "," + (x.defectId != null ? "" + x.defectId + "" : "NULL") + "," + (x.severityLevelId != null ? "" + x.severityLevelId + "" : "NULL") + "," + (x.vehicleCategoryId != null ? "" + x.vehicleCategoryId + "" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ")";
}
else
{
sqlQuery = " UPDATE VSD_Veh_Cat_Defect SET Veh_Cat_Defect_ID = " + x.id + ",Defect_ID = " + (x.defectId != null ? "" + x.defectId + "" : "NULL") + ",Severity_Level_ID = " + (x.severityLevelId != null ? "" + x.severityLevelId + "" : "NULL") + ",Vehicle_category_ID = " + (x.vehicleCategoryId != null ? "" + x.vehicleCategoryId + "" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Veh_Cat_Defect_ID = " + x.id;
}
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@Veh_Cat_Defect_ID", SqlDbType.Int).Value = x.id;
command.Parameters.Add("@Defect_ID", SqlDbType.Int).Value = x.defectId;
command.Parameters.Add("@Severity_Level_ID", SqlDbType.Int).Value = x.severityLevelId;
command.Parameters.Add("@Vehicle_category_ID", SqlDbType.Int).Value = x.vehicleCategoryId;
command.Parameters.Add("@IsDisabled", SqlDbType.NChar, 1).Value = (x.isDisabled.ToString())[0];
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
}
}
public void Synchronize_severityLevel(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
//check for severity value updates
if (test.severityLevel != null)
{
handHeldService.SeverityLevel1[] WebServiceDefects = new VSDApp.handHeldService.SeverityLevel1[test.severityLevel.Length];
WebServiceDefects = test.severityLevel;
foreach (handHeldService.SeverityLevel1 x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Severity_Level"
+ " WHERE (Severity_Level_ID = " + x.id + ")";
command = new SqlCeCommand(sqlQuery, con);
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Severity_Level (Severity_Level_ID,Severity_Level,Severity_Level_Code,Severity_Level_Code_Prefix,Severity_Level_Name,Severity_Level_Name_A,Severity_Level_Desc,Severity_Level_Desc_A) VALUES (" + x.id + "," + (x.value != null ? "'" + x.value.Replace("'", "`") + "'" : "NULL") + "," + (x.code != null ? "'" + x.code + "'" : "NULL") + "," + (x.codePrefix != null ? "'" + x.codePrefix + "'" : "NULL") + "," + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + "," + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + "," + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + ")";
}
else
{
sqlQuery = " UPDATE VSD_Severity_Level SET Severity_Level_ID = " + x.id + ", Severity_Level = " + (x.value != null ? "'" + x.value.Replace("'", "`") + "'" : "NULL") + ", Severity_Level_Code = " + (x.code != null ? "'" + x.code + "'" : "NULL") + ",Severity_Level_Code_Prefix = " + (x.codePrefix != null ? "'" + x.codePrefix + "'" : "NULL") + ",Severity_Level_Name = " + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + ",Severity_Level_Name_A = " + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + ",Severity_Level_Desc = " + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + ",Severity_Level_Desc_A = " + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + " WHERE Severity_Level_ID = " + x.id;
}
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@SeverityLevelID", SqlDbType.Int).Value = x.id;
command.Parameters.Add("@SeverityLevel", SqlDbType.Int).Value = x.value;
command.Parameters.Add("@SeverityLevelCode", SqlDbType.NChar, 25).Value = x.code;
command.Parameters.Add("@SeverityLevelCodePrefix", SqlDbType.NChar, 25).Value = x.codePrefix;
command.Parameters.Add("@SeverityLevelName", SqlDbType.NChar, 100).Value = x.name;
command.Parameters.Add("@SeverityLevelNameArabic", SqlDbType.NChar, 100).Value = x.nameArabic;
command.Parameters.Add("@SeverityLevelDesc", SqlDbType.NChar, 250).Value = x.description;
command.Parameters.Add("@SeverityLevelDescA", SqlDbType.NChar, 250).Value = x.descriptionArabic;
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
}
}
public void Synchronize_severityLevelProperty(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
//check for severity level property updates
if (test.severityLevelProperty != null)
{
handHeldService.SeverityLevelProperties[] WebServiceDefects = new VSDApp.handHeldService.SeverityLevelProperties[test.severityLevelProperty.Length];
WebServiceDefects = test.severityLevelProperty;
foreach (handHeldService.SeverityLevelProperties x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Severity_Level_Prop"
+ " WHERE (Severity_Level_Prop_ID = @SeverityLevelPropID)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@SeverityLevelPropID", SqlDbType.Int).Value = x.id;
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
int SeverityLevelPropID = x.id;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Severity_Level_Prop (Severity_Level_Prop_ID,Severity_Level_ID,Min_No_Of_Defects,Max_No_Of_Defects,Due_Days,Vehicle_Service_Suspension_Days,Comp_Service_Suspension_Days,Req_Plate_Confiscation,Receipt_Title,Receipt_Title_A,IsDisabled) VALUES (" + x.id + "," + (x.severityId != null ? "" + x.severityId + "" : "NULL") + "," + (x.minDefects != null ? "" + x.minDefects + "" : "NULL") + "," + (x.maxDefects != null ? "" + x.maxDefects + "" : "NULL") + "," + (x.dueDays != null ? "" + x.dueDays + "" : "NULL") + "," + (x.vehicleServiceSuspendDays != null ? "" + x.vehicleServiceSuspendDays + "" : "NULL") + "," + (x.companyServiceSuspenseDays != null ? "" + x.companyServiceSuspenseDays + "" : "NULL") + "," + (x.requirePlateConfiscation != null ? "'" + x.requirePlateConfiscation + "'" : "NULL") + "," + (x.receiptTitle != null ? "'" + x.receiptTitle.Replace("'", "`") + "'" : "NULL") + "," + (x.receiptTitleArabic != null ? "'" + x.receiptTitleArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ")";
}
else
{
sqlQuery = " UPDATE VSD_Severity_Level_Prop SET Severity_Level_Prop_ID = " + x.id + ",Severity_Level_ID = " + (x.severityId != null ? "" + x.severityId + "" : "NULL") + ",Min_No_Of_Defects = " + (x.minDefects != null ? "" + x.minDefects + "" : "NULL") + ",Max_No_Of_Defects = " + (x.maxDefects != null ? "" + x.maxDefects + "" : "NULL") + ",Due_Days = " + (x.dueDays != null ? "" + x.dueDays + "" : "NULL") + ",Vehicle_Service_Suspension_Days = " + (x.vehicleServiceSuspendDays != null ? "" + x.vehicleServiceSuspendDays + "" : "NULL") + ",Comp_Service_Suspension_Days = " + (x.companyServiceSuspenseDays != null ? "" + x.companyServiceSuspenseDays + "" : "NULL") + ",Req_Plate_Confiscation = " + (x.requirePlateConfiscation != null ? "'" + x.requirePlateConfiscation + "'" : "NULL") + ",Receipt_Title = " + (x.receiptTitle != null ? "'" + x.receiptTitle.Replace("'", "`") + "'" : "NULL") + ",Receipt_Title_A = " + (x.receiptTitleArabic != null ? "'" + x.receiptTitleArabic.Replace("'", "`") + "'" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Severity_Level_Prop_ID = " + x.id;
}
// command.Parameters.Add("@SeverityLevelPropID", SqlDbType.Int).Value = SeverityLevelPropID;
command.Parameters.Add("@SeverityLevelID", SqlDbType.Int).Value = SeverityLevelPropID;
command.Parameters.Add("@MinNoOfDefects", SqlDbType.Int).Value = x.minDefects;
command.Parameters.Add("@MaxNoOfDefects", SqlDbType.Int).Value = x.maxDefects;
command.Parameters.Add("@CompServiceSuspensionDays", SqlDbType.Int).Value = x.companyServiceSuspenseDays;
command.Parameters.Add("@ReqPlateConfiscation", SqlDbType.NChar, 1).Value = (null == x.requirePlateConfiscation) ? "F" : "T";
command.Parameters.Add("@VehRegBlockDays", SqlDbType.Int).Value = x.vehicleServiceSuspendDays;
command.Parameters.Add("@ReceiptTitle", SqlDbType.NChar, 100).Value = x.receiptTitle;
command.Parameters.Add("@ReceiptTitleA", SqlDbType.NChar, 100).Value = x.receiptTitleArabic;
command.Parameters.Add("@IsDisabled", SqlDbType.Int).Value = ("F" == x.isDisabled) ? "F" : "T";
command.Parameters.Add("@ConfiscationDays", SqlDbType.Int).Value = x.plateConfiscationDays;
command = new SqlCeCommand(sqlQuery, con);
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
}
}
public void Synchronize_location(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
//check for location updates
if (test.location != null)
{
handHeldService.Location[] WebServiceDefects = new VSDApp.handHeldService.Location[test.location.Length];
WebServiceDefects = test.location;
foreach (handHeldService.Location x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Location"
+ " WHERE (Location_ID = @LocationID)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@LocationID", SqlDbType.Int).Value = Int32.Parse(x.id);
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Location (Location_Area_Code , Location_Area_Name , Location_Area_Name_A ,Country_ID , Location_ID , IsDisabled ,Parent_Location_ID ) VALUES ( " + (x.areaCode != null ? "'" + x.areaCode + "'" : "NULL") + ", " + (x.areaName != null ? "'" + x.areaName.Replace("'", "`") + "'" : "NULL") + ", " + (x.areaNameArabic != null ? "'" + x.areaNameArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.country.id != null ? "" + x.country.id + "" : "NULL") + ", " + (x.id != null ? "" + x.id + "" : "NULL") + ", " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + "," + (x.parentId != null ? "" + x.parentId + "" : "NULL") + ")";
}
else
{
sqlQuery = " UPDATE VSD_Location SET Location_Area_Code = " + (x.areaCode != null ? "'" + x.areaCode + "'" : "NULL") + ", Location_Area_Name = " + (x.areaName != null ? "'" + x.areaName.Replace("'", "`") + "'" : "NULL") + ", Location_Area_Name_A = " + (x.areaNameArabic != null ? "'" + x.areaNameArabic.Replace("'", "`") + "'" : "NULL") + ",Country_ID = " + (x.country.id != null ? "" + x.country.id + "" : "NULL") + ", Location_ID = " + (x.id != null ? "" + x.id + "" : "NULL") + ", IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ",Parent_Location_ID = " + (x.parentId != null ? "" + x.parentId + "" : "NULL") + " WHERE Location_ID = " + (x.id != null ? "" + x.id + "" : "NULL") + " ";
}
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@LocationAreaCode", SqlDbType.NChar, 50).Value = x.areaCode;
command.Parameters.Add("@LocationAreaName", SqlDbType.NChar, 100).Value = x.areaName;
command.Parameters.Add("@LocationAreaNameArabic", SqlDbType.NChar, 200).Value = x.areaNameArabic;
command.Parameters.Add("@CountryID", SqlDbType.Int).Value = x.country.id;
command.Parameters.Add("@LocationID", SqlDbType.Int).Value = x.id;
command.Parameters.Add("@IsDisabled", SqlDbType.Int).Value = x.isDisabled;
if (x.parentId != null)
{
command.Parameters.Add("@ParentLocationID", Int32.Parse(x.parentId));
}
else
{
command.Parameters.Add("@ParentLocationID", DBNull.Value);
}
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
}
}
public void Synchronize_propertyValue(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
//check for property value updates
if (test.propertyValue != null)
{
handHeldService.PropertyValue[] WebServiceDefects = new VSDApp.handHeldService.PropertyValue[test.propertyValue.Length];
WebServiceDefects = test.propertyValue;
foreach (handHeldService.PropertyValue x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Property_Value"
+ " WHERE (Property_Type_Value_ID = @PropertyValueID)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@PropertyValueID", SqlDbType.Int).Value = Int32.Parse(x.id);
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Property_Value (Property_Type_Value_ID ,Property_Type_ID,Property_Type_Name,Property_Type_Value,IsDisabled) VALUES (" + x.id + " ," + (x.propertyTypeId != null ? "" + x.propertyTypeId + "" : "NULL") + "," + (x.valueName != null ? "'" + x.valueName.Replace("'", "`") + "'" : "NULL") + "," + (x.value != null ? "'" + x.value.Replace("'", "`") + "'" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ")";
}
else
{
sqlQuery = " UPDATE VSD_Property_Value SET Property_Type_Value_ID = " + (x.id != null ? "" + x.id + "" : "NULL") + " ,Property_Type_ID = " + (x.propertyTypeId != null ? "" + x.propertyTypeId + "" : "NULL") + ", Property_Type_Name = " + (x.valueName != null ? "'" + x.valueName.Replace("'", "`") + "'" : "NULL") + " ,Property_Type_Value = " + (x.value != null ? "'" + x.value.Replace("'", "`") + "'" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE PROPERTY_TYPE_VALUE_ID = " + (x.id != null ? "" + x.id + "" : "NULL") + " ";
}
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@PropertyTypeValueID", SqlDbType.Int).Value = x.id;
command.Parameters.Add("@PropertyTypeID", SqlDbType.Int).Value = x.propertyTypeId;
command.Parameters.Add("@PropertyTypeName", SqlDbType.NChar, 50).Value = x.valueName;
command.Parameters.Add("@PropertyTypeValue", SqlDbType.NChar, 50).Value = x.value;
command.Parameters.Add("@IsDisabled", SqlDbType.NChar, 1).Value = x.isDisabled;
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
}
}
public void Synchronize_country(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
//check for country updates
if (test.country != null)
{
handHeldService.Country[] WebServiceDefects = new VSDApp.handHeldService.Country[test.country.Length];
WebServiceDefects = test.country;
foreach (handHeldService.Country x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Country"
+ " WHERE (Country_ID = @CountryID)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@CountryID", SqlDbType.Int).Value = Int32.Parse(x.id);
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Country (Country_ID ,Country_Code,Country_Name,Parent_Country_ID) VALUES (" + (x.id != null ? x.id : "NULL") + "," + (x.code != null ? "'" + x.code + "'" : "NULL") + "," + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + "," + (x.parentId != null ? "" + x.parentId + "" : "NULL") + ") ";
}
else
{
sqlQuery = " UPDATE VSD_Country SET Country_Code = " + (x.code != null ? "'" + x.code + "'" : "NULL") + ",Country_Name = " + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + ",Parent_Country_ID = " + (x.parentId != null ? "" + x.parentId + "" : "NULL") + " WHERE Country_ID = " + (x.id != null ? "" + x.id + "" : "NULL");
}
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@CountryID", SqlDbType.Int).Value = Int32.Parse(x.id);
command.Parameters.Add("@CountryCode", SqlDbType.NChar, 25).Value = x.code;
command.Parameters.Add("@CountryName", SqlDbType.NChar, 50).Value = x.name;
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception)
{
throw;
}
}
public void Synchronize_countryProperty(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
//check for country property updates
if (test.countryProperty != null)
{
handHeldService.CountryProperty[] WebServiceDefects = new VSDApp.handHeldService.CountryProperty[test.countryProperty.Length];
WebServiceDefects = test.countryProperty;
foreach (handHeldService.CountryProperty x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Country_Prop"
+ " WHERE (Country_Prop_ID = @CountryPropID)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@CountryPropID", SqlDbType.Int).Value = Int32.Parse(x.id);
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Country_Prop (Can_Confiscate_Plates,Can_Fetch_Info,Can_Force_Vehicle_Testing,Can_Inspect,Can_Print_Viol_Notice,Can_Raise_Violation, Can_Send_Fine,Country_ID,Country_Prop_ID,IsDefault,IsDisabled) VALUES (" + (x.canConfiscatePlate != null ? "'" + x.canConfiscatePlate + "'" : "NULL") + "," + (x.canFetchInfo != null ? "'" + x.canFetchInfo + "'" : "NULL") + "," + (x.canForceVehicleTesting != null ? "'" + x.canForceVehicleTesting + "'" : "NULL") + "," + (x.canInspect != null ? "'" + x.canInspect + "'" : "NULL") + "," + (x.canPrintViolationTicket != null ? "'" + x.canPrintViolationTicket + "'" : "NULL") + "," + (x.canRaiseViolation != null ? "'" + x.canRaiseViolation + "'" : "NULL") + "," + (x.canSendFine != null ? "'" + x.canSendFine + "'" : "NULL") + "," + (x.countryId != null ? "" + x.countryId + "" : "NULL") + "," + (x.id != null ? "'" + x.id + "'" : "NULL") + "," + (x.isDefault != null ? "'" + x.isDefault + "'" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ") ";
}
else
{
sqlQuery = " UPDATE VSD_Country_Prop SET Can_Confiscate_Plates = " + (x.canConfiscatePlate != null ? "'" + x.canConfiscatePlate + "'" : "NULL") + " , Can_Fetch_Info = " + (x.canFetchInfo != null ? "'" + x.canFetchInfo + "'" : "NULL") + ",Can_Force_Vehicle_Testing = " + (x.canForceVehicleTesting != null ? "'" + x.canForceVehicleTesting + "'" : "NULL") + ",Can_Inspect = " + (x.canInspect != null ? "'" + x.canInspect + "'" : "NULL") + ",Can_Print_Viol_Notice = " + (x.canPrintViolationTicket != null ? "'" + x.canPrintViolationTicket + "'" : "NULL") + ",Can_Raise_Violation = " + (x.canRaiseViolation != null ? "'" + x.canRaiseViolation + "'" : "NULL") + ", Can_Send_Fine = " + (x.canSendFine != null ? "'" + x.canSendFine + "'" : "NULL") + ",Country_ID = " + (x.canConfiscatePlate != null ? "" + x.countryId + "" : "NULL") + ",Country_Prop_ID = " + (x.id != null ? "'" + x.id + "'" : "NULL") + ",IsDefault = " + (x.isDefault != null ? "'" + x.isDefault + "'" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Country_Prop_ID = " + (x.id != null ? "'" + x.id + "'" : "NULL") + " ";
}
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@CanConfiscatePlates", SqlDbType.NChar, 1).Value = x.canConfiscatePlate;
command.Parameters.Add("@CanFetchInfo", SqlDbType.NChar, 1).Value = x.canFetchInfo;
command.Parameters.Add("@CanForceVehicleTesting", SqlDbType.NChar, 1).Value = x.canForceVehicleTesting;
command.Parameters.Add("@CanInspect", SqlDbType.NChar, 1).Value = x.canInspect;
command.Parameters.Add("@CanPrintViolTicket", SqlDbType.NChar, 1).Value = x.canPrintViolationTicket;
command.Parameters.Add("@CanRaiseViolation", SqlDbType.NChar, 1).Value = x.canRaiseViolation;
command.Parameters.Add("@CanSendFine", SqlDbType.NChar, 1).Value = x.canSendFine;
command.Parameters.Add("@CountryID", SqlDbType.Int).Value = x.countryId;
command.Parameters.Add("@CountryPropID", SqlDbType.Int).Value = x.id;
command.Parameters.Add("@IsDefault", SqlDbType.NChar, 1).Value = x.isDefault;
command.Parameters.Add("@IsDisabled", SqlDbType.NChar, 1).Value = (x.isDisabled.ToString())[0];
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
}
}
public void Synchronize_defect(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
if (test.defect != null)
{
handHeldService.Defect1[] WebServiceDefects = new VSDApp.handHeldService.Defect1[test.defect.Length];
WebServiceDefects = test.defect;
int count = 0;
foreach (handHeldService.Defect1 x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Defect"
+ " WHERE (Defect_ID = @DefectID)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@DefectID", SqlDbType.Int).Value = Int32.Parse(x.id);
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Defect (Defect_ID ,Defect_Code_Prefix ,Defect_Code ,Defect_Desc ,Defect_Desc_A ,IsDisabled ,Defect_Name ,Defect_Name_A ,Defect_Type,Defect_Category_ID ) VALUES (@DefectID," + (x.codePrefix != null ? "'" + x.codePrefix + "'" : "NULL") + "," + (x.code != null ? "'" + x.code + "'" : "NULL") + "," + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + "," + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + ",'" + x.isDisabled + "'," + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + "," + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.type != null ? "'" + x.type + "'" : "NULL") + "," + x.category + ")";
}
else
{
sqlQuery = " UPDATE VSD_Defect SET Defect_Code_Prefix = " + (x.codePrefix != null ? "'" + x.codePrefix + "'" : "NULL") + ",Defect_Code = " + (x.code != null ? "'" + x.code + "'" : "NULL") + ",Defect_Desc = " + (x.description != null ? "'" + x.description.Replace("'", "`") + "'" : "NULL") + ",Defect_Desc_A = " + (x.descriptionArabic != null ? "'" + x.descriptionArabic.Replace("'", "`") + "'" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ",Defect_Name = " + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + ",Defect_Name_A = " + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + ",Defect_Type = " + (x.type != null ? "'" + x.type + "'" : "NULL") + ", Defect_Category_ID = " + x.category + " WHERE Defect_ID = " + x.id;
}
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@DefectCodePrefix", SqlDbType.NVarChar).Value = x.codePrefix;
command.Parameters.Add("@DefectCode", SqlDbType.NVarChar).Value = x.code;
command.Parameters.Add("@DefectDescription", SqlDbType.NVarChar).Value = x.description;
command.Parameters.Add("@DefectDescriptionArabic", SqlDbType.NVarChar).Value = x.descriptionArabic;
command.Parameters.Add("@IsDisabled", SqlDbType.NVarChar).Value = x.isDisabled;
command.Parameters.Add("@DefectName", SqlDbType.NVarChar).Value = x.name;
command.Parameters.Add("@DefectNameArabic", SqlDbType.NVarChar).Value = x.nameArabic;
command.Parameters.Add("@DefectType", SqlDbType.NVarChar).Value = x.type;
command.Parameters.Add("@DefectID", SqlDbType.Int).Value = Int32.Parse(x.id);
command.Parameters.Add("@DefectCategoryID", SqlDbType.Int).Value = Int32.Parse(x.category);
try
{
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
count++;
}
catch (Exception ex)
{
// CommonUtils.WriteLog(ex.StackTrace);
throw;
}
rs.Close();
con.Close();
}
}
}
catch (Exception)
{
throw;
}
}
int IDBManager.SynchronizeConfig(handHeldService.SynchronizeConfigDataResponseItem test)
{
if (test != null)
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
Synchronize_defectCategoty(test);
Synchronize_defect(test);
Synchronize_country(test);
Synchronize_countryProperty(test);
Synchronize_location(test);
Synchronize_propertyType(test);
Synchronize_propertyValue(test);
Synchronize_severityLevel(test);
Synchronize_severityLevelProperty(test);
Synchronize_vehicleCategory(test);
Synchronize_vehicleCatDefectSeverity(test);
Synchronize_testType(test);
Synchronize_violationSeverityFeeRule(test);
}
return Int32.Parse((test.response.code));
}
public void Synchronize_violationSeverityFeeRule(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
//check for Vehicle Category Defect updates
if (test.violationSeverityFeeRule != null)
{
handHeldService.ViolationSeverityFeeRule[] WebServiceDefects = new VSDApp.handHeldService.ViolationSeverityFeeRule[test.violationSeverityFeeRule.Length];
WebServiceDefects = test.violationSeverityFeeRule;
foreach (handHeldService.ViolationSeverityFeeRule x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Vio_Sev_Fee_Rule"
+ " WHERE (Vio_Sev_Fee_Rule_ID = @Vio_Sev_Fee_Rule_ID)";
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@Vio_Sev_Fee_Rule_ID", SqlDbType.Int).Value = x.id;
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_Vio_Sev_Fee_Rule ( Vio_Sev_Fee_Rule_ID,Severity_Level_ID,Test_Type_ID,Test_Attempts,Min_No_Of_Defects,Max_No_Of_Defects,Is_Under_DueDate,Is_Fee_Applied,Fine_Amount,IsDisabled ) VALUES ( " + x.id + "," + (x.severityId != null ? "" + x.severityId + "" : "NULL") + "," + (x.testTypeId != null ? "" + x.testTypeId + "" : "NULL") + "," + (x.testAttepmts != null ? "" + x.testAttepmts + "" : "NULL") + "," + (x.minDefects != null ? "" + x.minDefects + "" : "NULL") + "," + (x.maxDefects != null ? "" + x.maxDefects + "" : "NULL") + "," + (x.isUnderDueDate != null ? "'" + x.isUnderDueDate + "'" : "NULL") + "," + (x.isFeeApplied != null ? "'" + x.isFeeApplied + "'" : "NULL") + "," + (x.feeAmount != null ? "" + x.feeAmount + "" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " )";
}
else
{
sqlQuery = " UPDATE VSD_Vio_Sev_Fee_Rule SET Vio_Sev_Fee_Rule_ID = " + x.id + ",Severity_Level_ID = " + (x.severityId != null ? "" + x.severityId + "" : "NULL") + ",Test_Type_ID = " + (x.testTypeId != null ? "" + x.testTypeId + "" : "NULL") + ",Test_Attempts = " + (x.testAttepmts != null ? "" + x.testAttepmts + "" : "NULL") + ",Min_No_Of_Defects = " + (x.minDefects != null ? "" + x.minDefects + "" : "NULL") + ",Max_No_Of_Defects = " + (x.maxDefects != null ? "" + x.maxDefects + "" : "NULL") + ",Is_Under_DueDate = " + (x.isUnderDueDate != null ? "'" + x.isUnderDueDate + "'" : "NULL") + ",Is_Fee_Applied = " + (x.isFeeApplied != null ? "'" + x.isFeeApplied + "'" : "NULL") + ",Fine_Amount = " + (x.feeAmount != null ? "" + x.feeAmount + "" : "NULL") + ",IsDisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE Vio_Sev_Fee_Rule_ID =" + x.id;
}
command = new SqlCeCommand(sqlQuery, con);
command.Parameters.Add("@Vio_Sev_Fee_Rule_ID", SqlDbType.Int).Value = x.id;
command.Parameters.Add("@Severity_Level_ID", SqlDbType.Int).Value = x.severityId;
command.Parameters.Add("@Test_Type_ID", SqlDbType.Int).Value = x.testTypeId;
command.Parameters.Add("@Test_Attempts", SqlDbType.Int).Value = x.testAttepmts;
command.Parameters.Add("@Min_No_Of_Defects", SqlDbType.Int).Value = x.minDefects;
command.Parameters.Add("@Max_No_Of_Defects", SqlDbType.Int).Value = x.maxDefects;
command.Parameters.Add("@Is_Under_DueDate", SqlDbType.NVarChar).Value = x.isUnderDueDate;
command.Parameters.Add("@Is_Fee_Applied", SqlDbType.NVarChar).Value = x.isFeeApplied;
command.Parameters.Add("@Fine_Amount", SqlDbType.Int).Value = x.feeAmount;
command.Parameters.Add("@IsDisabled", SqlDbType.NVarChar).Value = x.isDisabled;
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
}
}
public void Synchronize_testType(handHeldService.SynchronizeConfigDataResponseItem test)
{
try
{
SqlCeConnection con = ((VSDApp.com.rta.vsd.hh.db.IDBManager)VSDApp.com.rta.vsd.hh.db.DBConnectionManager.GetInstance()).GetConnection();
string sqlQuery;
if (con.State == ConnectionState.Closed) { con.Open(); }
SqlCeCommand command;
SqlCeResultSet rs;
int rowCount;
int temp = -1;
//check for test type updates
if (test.testType != null)
{
handHeldService.TestType[] WebServiceDefects = new VSDApp.handHeldService.TestType[test.testType.Length];
WebServiceDefects = test.testType;
foreach (handHeldService.TestType x in WebServiceDefects)
{
if (con.State == ConnectionState.Closed) { con.Open(); }
/*
* IF EXISTS (SELECT * FROM VSD_Defect WHERE DefectID = @DefectID)
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
*/
sqlQuery = "SELECT COUNT(*) AS Expr1"
+ " FROM VSD_Test_type"
+ " WHERE (test_type_id = " + x.id + ")";
command = new SqlCeCommand(sqlQuery, con);
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rowCount = ((System.Collections.ICollection)rs.ResultSetView).Count;
//rountine for Defect Table
if (rowCount > 0)
{
rs.ReadFirst();
temp = rs.GetInt32(0);
}
if (temp == 0)
{
sqlQuery = " INSERT INTO VSD_test_type (test_type_ID,record_type,test_type_code,test_type_name,test_type_name_a,test_type_desc,test_Type_desc_a,is_comprehensive_test,days_valid,isdisabled) VALUES (" + x.id + "," + (x.recordType != null ? "'" + x.recordType + "'" : "NULL") + "," + (x.code != null ? "'" + x.code + "'" : "NULL") + "," + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + "," + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.Description != null ? "'" + x.Description.Replace("'", "`") + "'" : "NULL") + "," + (x.DescriptionArabic != null ? "'" + x.DescriptionArabic.Replace("'", "`") + "'" : "NULL") + "," + (x.isComprehensive != null ? "'" + x.isComprehensive + "'" : "NULL") + "," + (x.daysValid != null ? "" + x.daysValid.ToString() + "" : "NULL") + "," + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + ")";
}
else
{
sqlQuery = " UPDATE vsd_test_type SET test_type_id = " + x.id + ", record_type = " + (x.recordType != null ? "'" + x.recordType + "'" : "NULL") + ", test_type_code = " + (x.code != null ? "'" + x.code + "'" : "NULL") + ",test_type_name = " + (x.name != null ? "'" + x.name.Replace("'", "`") + "'" : "NULL") + ",test_type_name_a = " + (x.nameArabic != null ? "'" + x.nameArabic.Replace("'", "`") + "'" : "NULL") + ",test_type_desc = " + (x.Description != null ? "'" + x.Description.Replace("'", "`") + "'" : "NULL") + ",test_type_desc_a = " + (x.DescriptionArabic != null ? "'" + x.DescriptionArabic.Replace("'", "`") + "'" : "NULL") + ",is_comprehensive_test = " + (x.isComprehensive != null ? "'" + x.isComprehensive + "'" : "NULL") + ",days_valid = " + (x.daysValid != null ? "" + x.daysValid.ToString() + "" : "NULL") + ",isdisabled = " + (x.isDisabled != null ? "'" + x.isDisabled + "'" : "NULL") + " WHERE test_type_id = " + x.id;
}
command = new SqlCeCommand(sqlQuery, con);
rs = command.ExecuteResultSet(ResultSetOptions.Scrollable);
rs.Close();
con.Close();
}
}
}
catch (Exception ex)
{
CommonUtils.WriteLog(ex.StackTrace);
}
}
int IDBManager.SynchronizeConfig(handHeldService.SynchronizeConfigDataResponseItem test)
{
throw new NotImplementedException();
}
