public void Crane_JobView_GetData(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
string context = "";
string sql = string.Format(@"select det2.Id,det2.JobNo,det2.ContainerNo,det2.DriverCode,det2.TowheadCode,det2.ChessisCode,job.Vessel,job.Voyage,
job.CarrierBkgNo AS ClientRefNo,job.CarrierId,p.Name as CarrierName,det2.Remark,det2.Remark1,det2.Statuscode,
FromCode,FromDate,FromTime,ToCode,ToDate,ToTime,det2.Carpark,det2.ToParkingLot,det2.ParkingZone,det2.TripCode,
det2.BookingDate,det2.BookingTime,det2.BookingTime2,det2.BookingRemark,det2.OtHour,det2.ByUser,det2.PodType,det2.OtHour,det2.RequestVehicleType
from CTM_JobDet2 as det2
left outer join CTM_Job as job on det2.JobNo=job.JobNo
left outer join XXParty as p on job.ClientId=p.PartyId
where det2.Id={0}", job["No"]);
DataTable dt = ConnectSql_mb.GetDataTable(sql);
string mast = "{}";
string attachment = "[]";
string incentive = "[]";
string claims = "[]";
if (dt.Rows.Count > 0)
{
mast = Common.DataRowToJson(dt);
sql = string.Format(@"select Id,LineId,LineType,TripNo,ChgCode,ChgCodeDes,Qty,Price,VendorId from job_cost where TripNo=@TripNo and LineType='DP'");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@TripNo", SafeValue.SafeInt(job["No"], 0), SqlDbType.Int));
dt = ConnectSql_mb.GetDataTable(sql, list);
incentive = Common.DataTableToJson(dt);
sql = string.Format(@"select Id,LineId,LineType,TripNo,ChgCode,ChgCodeDes,Qty,Price,VendorId from job_cost where TripNo=@TripNo and LineType='CL'");
dt = ConnectSql_mb.GetDataTable(sql, list);
claims = Common.DataTableToJson(dt);
sql = string.Format(@"select Id,FileType,FileName,FilePath,FileNote From CTM_Attachment where TripId=@TripNo and FileType='Image'");
dt = ConnectSql_mb.GetDataTable(sql, list);
dt.Columns.Add("FP500", typeof(string));
for (int i = 0; i < dt.Rows.Count; i++)
{
string path = dt.Rows[i]["FilePath"].ToString();
if (RebuildImage.Image_ExistOtherSize(path, dt.Rows[i]["FileType"].ToString(), 500))
{
path = path.Substring(0, path.LastIndexOf('/')) + "/" + 500 + path.Substring(path.LastIndexOf('/'));
}
dt.Rows[i]["FP500"] = path;
}
attachment = Common.DataTableToJson(dt);
}
context = string.Format(@"{0}mast:{2},attachment:{3},incentive:{4},claims:{5}{1}", "{", "}", mast, attachment, incentive, claims);
Common.WriteJsonP(true, context);
}
public void Crane_JobView_GetPODData(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
string sql = string.Format(@"select det2.Id,det2.JobNo,det2.DriverCode,'' as ContainerNo,BillingRemark,DeliveryRemark,Satisfaction,
convert(nvarchar(10),FromDate,120) as FromDate,FromTime,convert(nvarchar(10),ToDate,120) as ToDate,ToTime,det2.PodType,isnull(det2.OtHour,0) OtHour,
isnull(TotalHour,0) as epodTrip,epodSignedBy,epodCB1,epodCB2,epodCB3,epodCB4,epodCB5,epodCB6,ManPowerNo,ExcludeLunch,det2.DeliveryResult,det2.epodHardCopy
from CTM_JobDet2 as det2
where det2.Id=@tripId");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@tripId", job["No"], SqlDbType.Int));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
string mast = "{}";
string attachment = "[]";
if (dt.Rows.Count > 0)
{
mast = Common.DataRowToJson(dt);
sql = string.Format(@"select Id,FileType,FileName,FilePath,FileNote From CTM_Attachment where TripId=@tripId and FileType='Signature'");
dt = ConnectSql_mb.GetDataTable(sql, list);
dt.Columns.Add("FP500", typeof(string));
for (int i = 0; i < dt.Rows.Count; i++)
{
string path = dt.Rows[i]["FilePath"].ToString();
if (RebuildImage.Image_ExistOtherSize(path, dt.Rows[i]["FileType"].ToString(), 500))
{
path = path.Substring(0, path.LastIndexOf('/')) + "/" + 500 + path.Substring(path.LastIndexOf('/'));
}
dt.Rows[i]["FP500"] = path;
}
attachment = Common.DataTableToJson(dt);
//============== double mounting trip
}
string context = string.Format(@"{0}mast:{2},attachment:{3}{1}", "{", "}", mast, attachment);
bool status = true;
Common.WriteJsonP(status, context);
}
public void Vehcile_IssueReport_GetData(string info)
{
string info_ = HttpUtility.UrlDecode(Server.UrlDecode(info));
JObject job = (JObject)JsonConvert.DeserializeObject(info_);
List <ConnectSql_mb.cmdParameters> list = null;
ConnectSql_mb.cmdParameters cpar = null;
string status = "1";
string context = Common.StringToJson("");
string sql = string.Format(@"select * from Vehicle_IssueReport where Id=@Id");
list = new List <ConnectSql_mb.cmdParameters>();
cpar = new ConnectSql_mb.cmdParameters("@Id", job["JobNo"], SqlDbType.Int);
list.Add(cpar);
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
string detail = Common.DataRowToJson(dt, true);
sql = string.Format(@"select Id,FileType,FileName,FilePath,FileNote,'' as FP500 From CTM_Attachment where JobType='IssueRp' and TripId={0}", job["JobNo"]);
dt = ConnectSql_mb.GetDataTable(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
string path = dt.Rows[i]["FilePath"].ToString();
if (RebuildImage.Image_ExistOtherSize(path, dt.Rows[i]["FileType"].ToString(), 500))
{
path = path.Substring(0, path.LastIndexOf('/')) + "/" + 500 + path.Substring(path.LastIndexOf('/'));
}
dt.Rows[i]["FP500"] = path;
}
string attachment = Common.DataTableToJson(dt);
context = string.Format(@"{0}detail:{2},attachment:{3}{1}", "{", "}", detail, attachment);
Common.WriteJsonP(status, context);
}
public void Review_Detail_GetData(string info)
{
string info_ = HttpUtility.UrlDecode(Server.UrlDecode(info));
JObject job = (JObject)JsonConvert.DeserializeObject(info_);
string status = "0";
string context = Common.StringToJson("");
int cargoId = SafeValue.SafeInt(job["Oid"].ToString(), 0);
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@DnNo", job["DnNo"], SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@cargoId", cargoId, SqlDbType.Int));
// string sql = string.Format(@"select row_id as Oid,RecNo,RecDate,DnNo,JobNo as JobOrder,JobType,ContNo,CustCode,RecFrom,HblN,VesselNo,PortLoad,Qty,PackType,Weight,M3,Eta,Markings,Contents,
//'' as Driver_Code,StatusCode
//from job_receipt where DnNo=@DnNo");
string sql = string.Format(@"select top 1 cargo.Id as Oid,RecDate,HblNo as DnNo,job.JobNo as JobOrder,cargo.JobType,ContNo,HblNo as HblN,
QtyOrig Qty,PackTypeOrig as PackType,WeightOrig as Weight,VolumeOrig as M3,Eta,Marking1 as Markings,Remark1 as Contents,
'' as Driver_Code,CargoStatus as StatusCode,Qty as BKQty,Weight as BKWeight,Volume as BKM3,job.ClientRefNo
from job_house as cargo
left outer join ctm_job as job on cargo.JobNo=job.JobNo
where cargo.Id=@cargoId");
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
string mast = Common.DataRowToJson(dt);
if (dt.Rows.Count == 1)
{
string JobNo = dt.Rows[0]["JobOrder"].ToString();
status = "1";
// sql = string.Format(@"select p.row_id as Oid,UploadType as FileType,Name as FileName,Path as FilePath,'' as FileNote
//from job_photo as p
//where DoNo=@DnNo and isnull(JobNo,'')=''");
sql = string.Format(@"select p.Id as Oid,FileType,FileName,FilePath,FileNote
from CTM_Attachment as p
where JobNo=@cargoIdStr ");
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", JobNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("cargoIdStr", cargoId, SqlDbType.NVarChar, 100));
dt = ConnectSql_mb.GetDataTable(sql, list);
dt.Columns.Add("FP500", typeof(string));
for (int i = 0; i < dt.Rows.Count; i++)
{
string path = dt.Rows[i]["FilePath"].ToString();
if (RebuildImage.Image_ExistOtherSize(path, dt.Rows[i]["FileType"].ToString(), 500))
{
path = path.Substring(0, path.LastIndexOf('/')) + "/" + 500 + path.Substring(path.LastIndexOf('/'));
}
dt.Rows[i]["FP500"] = path;
}
string attachment = Common.DataTableToJson(dt);
// sql = string.Format(@"select p.row_id as Oid,UploadType as FileType,Name as FileName,Path as FilePath,'' as FileNote,d.row_id
//from job_photo as p
//left outer join job_det as d on d.HblN=p.DoNo collate Chinese_PRC_CI_AS and d.JobNo=p.JobNo
//where DoNo=@DnNo and d.row_id>0");
sql = string.Format(@"select p.Id as Oid,FileType,FileName,FilePath,FileNote
from CTM_Attachment as p
where RefNo=@JobNo and JobNo<>@cargoIdStr");
dt = ConnectSql_mb.GetDataTable(sql, list);
dt.Columns.Add("FP500", typeof(string));
for (int i = 0; i < dt.Rows.Count; i++)
{
string path = dt.Rows[i]["FilePath"].ToString();
if (RebuildImage.Image_ExistOtherSize(path, dt.Rows[i]["FileType"].ToString(), 500))
{
path = path.Substring(0, path.LastIndexOf('/')) + "/" + 500 + path.Substring(path.LastIndexOf('/'));
}
dt.Rows[i]["FP500"] = path;
}
string detAttachment = Common.DataTableToJson(dt);
context = string.Format(@"{0}mast:{2},attachment:{3},detAttachment:{4}{1}", "{", "}", mast, attachment, detAttachment);
}
else
{
context = Common.StringToJson("This No inexistence");
}
Common.WriteJsonP(status, context);
}
public void ContainerDetail_Get(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
bool status = false;
string context = "{}";
string user = SafeValue.SafeString(job["user"]);
int No = SafeValue.SafeInt(job["No"], 0);
if (No > 0)
{
string sql = string.Format(@"select cont.Id as contId,cont.ContainerNo,cont.JobNo,job.JobDate,cont.ContainerType,cont.SealNo,cont.Weight,job.Vessel,job.Voyage,
cont.CfsStatus,job.ReleaseToHaulierRemark,job.Remark as JobRemark,isnull(cont.Remark1,'') as ContRemark,
cont.ScheduleDate,isnull(cont.PortnetStatus,'') as PortnetStatus,
'Y' as ClientRemark_Ind,'Client Remark' as ClientRemark_lb,cont.Remark2 as ClientRemark,
'N' as v1_Ind,'N' as v2_Ind,
(select count(*) from ctm_jobdet2 as det2 where det1Id=cont.Id and det2.Statuscode<>'C' ) as unC_trips,
(select top 1 TripCode from ctm_jobdet2 as det2 where det1Id=cont.Id and det2.Statuscode<>'C' ) as unC_tripCode
from CTM_JobDet1 as cont
left outer join CTM_Job as job on cont.JobNo=job.JobNo
where cont.Id=@Id");
//==job.ClientId=@user and
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@user", user, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Id", No, SqlDbType.Int));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
if (dt.Rows.Count > 0)
{
status = true;
string mast = Common.DataRowToJson(dt);
string trips = "[]";
string photo = "[]";
string invoice = "[]";
//string JobNo = SafeValue.SafeString(dt.Rows[0]["JobNo"]);
//list.Add(new ConnectSql_mb.cmdParameters("@JobNo", JobNo, SqlDbType.NVarChar, 100));
sql = string.Format(@"select Id,FromDate,ToDate,FromTime,ToTime,FromCode,ToCode,Statuscode,TripCode,
(Case statusCode when 'A' then 5 when 'S' then 6 when 'C' then 7 else 10 end) as Inx,convert(nvarchar(10),FromDate,120) as FromDate1
from CTM_JobDet2
where det1Id=@Id and Statuscode in ('C','S','A')
order by Inx,FromDate1 desc,FromTime desc");
dt = ConnectSql_mb.GetDataTable(sql, list);
trips = Common.DataTableToJson(dt);
sql = string.Format(@"select att.Id,att.FileType,att.FileName,att.FilePath,att.FileNote
From CTM_Attachment as att
left outer join CTM_JobDet1 as cont on att.ContainerNo=cont.ContainerNo and att.RefNo=cont.JobNo
where cont.Id=@Id and FileType='Image'");
dt = ConnectSql_mb.GetDataTable(sql, list);
dt.Columns.Add("FP500", typeof(string));
for (int i = 0; i < dt.Rows.Count; i++)
{
string path = dt.Rows[i]["FilePath"].ToString();
if (RebuildImage.Image_ExistOtherSize(path, dt.Rows[i]["FileType"].ToString(), 500))
{
path = path.Substring(0, path.LastIndexOf('/')) + "/" + 500 + path.Substring(path.LastIndexOf('/'));
}
dt.Rows[i]["FP500"] = path;
}
photo = Common.DataTableToJson(dt);
sql = string.Format(@"select inv.DocNo,inv.DocDate,inv.DocType,det1.JobNo,det.ChgCode,det.ChgDes1,det.DocLineNo,det.SequenceId as detId,
det.GstType,det.Qty,det.Price,det.Unit,det.Currency,det.ExRate,det.Gst,isnull(det.GstAmt,0) as GstAmt,isnull(det.DocAmt,0) as DocAmt,det.LocAmt,det.JobRefNo
from XAArInvoice as inv
left outer join CTM_JobDet1 as det1 on det1.jobNo=inv.MastRefNo
left outer join XAArInvoiceDet as det on inv.SequenceId=det.DocId
where det1.Id=@Id and inv.DocType='IV'
order by inv.SequenceId,DocLineNo");
dt = ConnectSql_mb.GetDataTable(sql, list);
invoice = Common.DataTableToJson(dt);
context = string.Format(@"{0}mast:{2},trips:{3},photo:{4},invoice:{5}{1}", "{", "}", mast, trips, photo, invoice);
}
else
{
context = Common.StringToJson("No permission");
}
}
else
{
context = Common.StringToJson("Requair No");
}
Common.WriteJsonP(status, context);
}
public void TransportDetail_Get(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
bool status = false;
string context = "{}";
string user = SafeValue.SafeString(job["user"]);
int No = SafeValue.SafeInt(job["No"], 0);
if (No > 0)
{
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@user", user, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Id", No, SqlDbType.Int));
status = true;
string mast = "{}";
string trips = "[]";
string photo = "[]";
string invoice = "[]";
//string JobNo = SafeValue.SafeString(dt.Rows[0]["JobNo"]);
//list.Add(new ConnectSql_mb.cmdParameters("@JobNo", JobNo, SqlDbType.NVarChar, 100));
string sql = string.Format(@"select Id,FromDate,ToDate,FromTime,ToTime,FromCode,ToCode,Statuscode,TripCode,
(Case statusCode when 'A' then 5 when 'S' then 6 when 'C' then 7 else 10 end) as Inx,convert(nvarchar(10),FromDate,120) as FromDate1
from CTM_JobDet2
where Id=@Id and Statuscode in ('C','S','A','P')
order by Inx,FromDate1 desc,FromTime desc");
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
trips = Common.DataTableToJson(dt);
sql = string.Format(@"select Id,FileType,FileName,FilePath,FileNote From CTM_Attachment
where TripId=@Id and FileType='Image'");
dt = ConnectSql_mb.GetDataTable(sql, list);
dt.Columns.Add("FP500", typeof(string));
for (int i = 0; i < dt.Rows.Count; i++)
{
string path = dt.Rows[i]["FilePath"].ToString();
if (RebuildImage.Image_ExistOtherSize(path, dt.Rows[i]["FileType"].ToString(), 500))
{
path = path.Substring(0, path.LastIndexOf('/')) + "/" + 500 + path.Substring(path.LastIndexOf('/'));
}
dt.Rows[i]["FP500"] = path;
}
photo = Common.DataTableToJson(dt);
sql = string.Format(@"select inv.DocNo,inv.DocDate,inv.DocType,det2.JobNo,det.ChgCode,det.ChgDes1,det.DocLineNo,det.SequenceId as detId,
det.GstType,det.Qty,det.Price,det.Unit,det.Currency,det.ExRate,det.Gst,isnull(det.GstAmt,0) as GstAmt,isnull(det.DocAmt,0) as DocAmt,det.LocAmt,det.JobRefNo
from XAArInvoice as inv
left outer join CTM_JobDet2 as det2 on det2.jobNo=inv.MastRefNo
left outer join XAArInvoiceDet as det on inv.SequenceId=det.DocId
where det2.Id=@Id and inv.DocType='IV'
order by inv.SequenceId,DocLineNo");
dt = ConnectSql_mb.GetDataTable(sql, list);
invoice = Common.DataTableToJson(dt);
context = string.Format(@"{0}mast:{2},trips:{3},photo:{4},invoice:{5}{1}", "{", "}", mast, trips, photo, invoice);
}
else
{
context = Common.StringToJson("Requair No");
}
Common.WriteJsonP(status, context);
}
