public MessageModel Save(ManualBookingModel BookingModel, string CompanyCode, int UserId)
{
string conString = CompanyConfigService.getDbCredentials(CompanyCode);
MessageModel objMessage = new MessageModel();
SqlDataAceessLayer _dal = new SqlDataAceessLayer(conString);
int YearCode = CompanyConfigService.getYearCode(BookingModel.BookingDate);
string Query = @"INSERT INTO [Custom].[Trn_Booking_Manual]
(Year_Code,LR_No,LR_Date,From_Service_Network_ID,From_Service_Network,To_Service_Network_ID,
To_Service_Network,Vehicle_ID,Vehicle_No,Customer_ID,Customer_Name,Weight,NoOfArticles,Is_Active,
Created_On,Created_By,Updated_On,Updated_By,Attachment_Path)
Select " + YearCode.ToString() + ",'" + BookingModel.BookingNo + "','" + BookingModel.BookingDate + "',"
+ BookingModel.FromLocationId + ",'" + BookingModel.FromLocation + "',"
+ BookingModel.ToLocationId + ",'" + BookingModel.ToLocation + "',"
+ BookingModel.VehicleId + ",'" + BookingModel.VechicleNo + "',"
+ BookingModel.CustomerId + ",'" + BookingModel.CustomerName + "',"
+ BookingModel.BookingWeight + "," + BookingModel.BookingNoOfPackages
+ ",1,getdate()," + UserId + ",getdate()," + UserId + ",'" + BookingModel.AttachmentPath + "'"
+ "; Select 1 ";
try
{
DataSet Ds = _dal.ExecuteSql(Query);
objMessage.MessageId = 0;
objMessage.Message = "Save Sucessfully";
}
catch (Exception ex)
{
objMessage.MessageId = 1;
objMessage.Message = ex.Message;
}
return(objMessage);
}
public MessageModel Save(ManualBookingTrackModel MbtModel, string CompanyCode)
{
MessageModel objMessage = new MessageModel();
string conString = CompanyConfigService.getDbCredentials(CompanyCode);
SqlDataAceessLayer _dal = new SqlDataAceessLayer(conString);
string Query = @"INSERT INTO Domestic.Trn_Vehicle_Tracking
(Vehicle_ID,Trip_ID,Booking_ID,Manual_Booking_ID,Tracking_Date,Location,
Tracking_Time,Remarks,Reason_ID,KMs)
Select 0,0,0," + MbtModel.ManualBookingId.ToString() + ",'" + MbtModel.TrackingDate.ToString("yyyy-MM-dd") + "','" +
MbtModel.Location + "','" + MbtModel.TrackingDate.ToString("HH:mm") + "','" + MbtModel.Remark + "'," + MbtModel.ReasonId + ",0";
try
{
DataSet Ds = _dal.ExecuteSql(Query);
objMessage.MessageId = 0;
objMessage.Message = "Save Sucessfully";
}
catch (Exception ex)
{
objMessage.MessageId = 1;
objMessage.Message = ex.Message;
}
return(objMessage);
}
public DataSet getStatus(string con)
{
SqlDataAceessLayer _dal = new SqlDataAceessLayer(con);
string Query = "Select Reason_ID,Reason from Common.Master_Reason a inner join Common.Master_Reason_Category b on " +
"a.Master_Reason_Category_ID = b.Master_Reason_Category_ID where b.Master_Reason_Category='Vehicle Tracking' ";
DataSet Ds = _dal.ExecuteSql(Query);
return(Ds);
}
public MessageModel Save(ManualBookingPODModel MbtModel, string CompanyCode)
{
MessageModel objMessage = new MessageModel();
string conString = CompanyConfigService.getDbCredentials(CompanyCode);
SqlDataAceessLayer _dal = new SqlDataAceessLayer(conString);
string Query = "Select Reason_ID,Reason from Common.Master_Reason a inner join Common.Master_Reason_Category b on " +
"a.Master_Reason_Category_ID = b.Master_Reason_Category_ID where b.Master_Reason_Category='POD' ";
DataSet Ds1 = _dal.ExecuteSql(Query);
string ReasonId = "0";
if (Ds1.Tables[0].Rows.Count == 0)
{
objMessage.MessageId = 2;
objMessage.Message = "POD Status Not Found";
return(objMessage);
}
else
{
ReasonId = Ds1.Tables[0].Rows[0]["Reason_ID"].ToString();
}
Query = @"INSERT INTO Domestic.Trn_Vehicle_Tracking
(Vehicle_ID,Trip_ID,Booking_ID,Manual_Booking_ID,Tracking_Date,Location,Tracking_Time,Remarks,Reason_ID,KMs,Attachment_Path,Signature)" +
"Select 0,0,0," + MbtModel.ManualBookingID.ToString() + ",'" + MbtModel.DeliveryDateTime.ToString("yyyy-MM-dd") + "','','" +
MbtModel.DeliveryDateTime.ToString("HH:mm") + "',''," + ReasonId + ",0,'" + MbtModel.AttachmentPath + "','" + MbtModel.UploadSignature1_txtSignatureValue + "'";
try
{
DataSet Ds = _dal.ExecuteSql(Query);
objMessage.MessageId = 0;
objMessage.Message = "Save Sucessfully";
}
catch (Exception ex)
{
objMessage.MessageId = 1;
objMessage.Message = ex.Message;
}
return(objMessage);
}
public DataSet getConsignorConsignee(string con, string SearchFor)
{
SqlDataAceessLayer _dal = new SqlDataAceessLayer(con);
string Query = "SELECT Top 20 Client_ID, Client_Name FROM Common.Master_Client WHERE Is_Active=1 AND " +
"Client_Name like '%" + SearchFor + "%' order by Client_Name";
DataSet Ds = _dal.ExecuteSql(Query);
return(Ds);
}
public DataSet getLocation(string con, string SearchFor)
{
SqlDataAceessLayer _dal = new SqlDataAceessLayer(con);
string Query = "Select Top 20 Service_Network_ID, Service_Network_Name FROM Common.Master_Service_Network WHERE Is_Active=1 AND " +
"Service_Network_Name like '%" + SearchFor + "%' order by Service_Network_Name";
DataSet Ds = _dal.ExecuteSql(Query);
return(Ds);
}
public DataSet getVehicle(string con, string SearchFor)
{
SqlDataAceessLayer _dal = new SqlDataAceessLayer(con);
string Query = "Select Top 20 Vehicle_ID,Vehicle_No From Fleet.Master_Vehicle where Is_Active=1 AND " +
"Vehicle_Category_ID in(1,2,3,4) and Vehicle_No like '%" + SearchFor + "%' order by Vehicle_No";
DataSet Ds = _dal.ExecuteSql(Query);
return(Ds);
}
public static string getDbCredentials(string CompanyCode)
{
SqlDataAceessLayer _dal = new SqlDataAceessLayer();
DataSet Ds = _dal.ExecuteSql("SELECT DbCredentials FROM dbo.Company_Config where CompanyCode = '" + CompanyCode + "'");
if (Ds.Tables[0].Rows.Count == 0)
{
throw new Exception("Comapny Config not found for comapny code " + CompanyCode);
}
return(Ds.Tables[0].Rows[0]["DbCredentials"].ToString());
}
public ManualBookingModel GetByBookingNo(string BookingNo, string CompanyCode)
{
string conString = CompanyConfigService.getDbCredentials(CompanyCode);
SqlDataAceessLayer _dal = new SqlDataAceessLayer(conString);
int YearCode = CompanyConfigService.getYearCode(DateTime.Now);
string Query = @"Select Booking_Manual_ID,LR_No,LR_Date,From_Service_Network,To_Service_Network,Vehicle_No,Customer_Name,
Weight,NoOfArticles,Attachment_Path From Custom.Trn_Booking_Manual
where Year_Code = " + YearCode.ToString() + " and LR_No = '" + BookingNo + "'";
DataSet Ds = _dal.ExecuteSql(Query);
var mbm = GenerateManualBookingModel(Ds.Tables[0].Rows[0]);
return(mbm);
}
public ManualBookingTrackNTraceModel GetTrackDetails(int ManualBookingId, string CompanyCode)
{
string conString = CompanyConfigService.getDbCredentials(CompanyCode);
SqlDataAceessLayer _dal = new SqlDataAceessLayer(conString);
string Query = @"SELECT Tracking_Date,Tracking_Time,Location,Reason,Remarks,A.Attachment_Path,[Signature]
FROM Domestic.Trn_Vehicle_Tracking A INNER JOIN
Custom.Trn_Booking_Manual B ON A.Manual_Booking_ID = B.Booking_Manual_ID LEFT OUTER JOIN
Common.Master_Reason C ON A.Reason_ID = C.Reason_ID where A.Manual_Booking_ID = " + ManualBookingId;
DataSet Ds = _dal.ExecuteSql(Query);
ManualBookingTrackNTraceModel mbm = new ManualBookingTrackNTraceModel();
mbm.TrackDs = Ds;
return(mbm);
}